27,579
社区成员
发帖
与我相关
我的任务
分享
USE [AIS20131025233823]
GO
/****** Object: StoredProcedure [dbo].[yu123] Script Date: 2017/8/17 13:52:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--存储过程,由销售订单生成计划状态的生产任务单
--sp_helptext [yu123]
--drop proc [yu123]
--exec [yu123] 16404,1,'SEORD017062','2017-8-14 00:00:00.000','2017-8-18 00:00:00.000'
ALTER procedure [dbo].[yu123]
@FBillerID int, --制单人
@fentryid int, --分录号
@FBillNo varchar(11), --订单编号
@FPlanCommitDate datetime, --开工日期
@FPlanFinishDate datetime --完工日期
as
set nocount on
--begin transaction
begin
INSERT INTO [dbo].[ICMO]
([FBrNo]
,[FInterID]
,[FBillNo]
,[FTranType]
,[FStatus]
,[FMRP]
,[FType]
,[FWorkShop]
,[FItemID]
,[FQty]
,[FCommitQty]
,[FPlanCommitDate]
,[FPlanFinishDate]
,[FConveyerID]
,[FCommitDate]
,[FCheckerID]
,[FCheckDate]
,[FRequesterID]
,[FBillerID]
,[FSourceEntryID]
,[FClosed]
,[FNote]
,[FUnitID]
,[FAuxCommitQty]
,[FAuxQty]
,[FOrderInterID]
,[FPPOrderInterID]
,[FParentInterID]
,[FCancellation]
,[FSupplyID]
,[FQtyFinish]
,[FQtyScrap]
,[FQtyForItem]
,[FQtyLost]
,[FPlanIssueDate]
,[FRoutingID]
,[FStartDate]
,[FFinishDate]
,[FAuxQtyFinish]
,[FAuxQtyScrap]
,[FAuxQtyForItem]
,[FAuxQtyLost]
,[FMrpClosed]
,[FBomInterID]
,[FQtyPass]
,[FAuxQtyPass]
,[FQtyBack]
,[FAuxQtyBack]
,[FFinishTime]
,[FReadyTIme]
,[FPowerCutTime]
,[FFixTime]
,[FWaitItemTime]
,[FWaitToolTime]
,[FTaskID]
,[FWorkTypeID]
,[FCostObjID]
,[FStockQty]
,[FAuxStockQty]
,[FSuspend]
,[FProjectNO]
,[FProductionLineID]
,[FReleasedQty]
,[FReleasedAuxQty]
,[FUnScheduledQty]
,[FUnScheduledAuxQty]
,[FSubEntryID]
,[FScheduleID]
,[FPlanOrderInterID]
,[FProcessPrice]
,[FProcessFee]
,[FGMPBatchNo]
,[FGMPCollectRate]
,[FGMPItemBalance]
,[FGMPBulkQty]
,[FCustID]
,[FMultiCheckLevel1]
,[FMultiCheckLevel2]
,[FMultiCheckLevel3]
,[FMultiCheckLevel4]
,[FMultiCheckLevel5]
,[FMultiCheckLevel6]
,[FMultiCheckDate1]
,[FMultiCheckDate2]
,[FMultiCheckDate3]
,[FMultiCheckDate4]
,[FMultiCheckDate5]
,[FMultiCheckDate6]
,[FCurCheckLevel]
,[FMRPLockFlag]
,[FHandworkClose]
,[FConfirmerID]
,[FConfirmDate]
,[FInHighLimit]
,[FInHighLimitQty]
,[FAuxInHighLimitQty]
,[FInLowLimit]
,[FInLowLimitQty]
,[FAuxInLowLimitQty]
,[FChangeTimes]
,[FHeadSelfJ0172]
,[FHeadSelfJ0173]
,[FHeadSelfJ0174]
,[FCheckCommitQty]
,[FAuxCheckCommitQty]
,[FCloseDate]
,[FPlanConfirmed]
,[FPlanMode]
,[FMTONo]
,[FPrintCount]
,[FFinClosed]
,[FFinCloseer]
,[FFinClosedate]
,[FStockFlag]
,[FStartFlag]
,[FVchBillNo]
,[FVchInterID]
,[FCardClosed]
,[FHRReadyTime]
,[FPlanCategory]
,[FBomCategory]
,[FSourceTranType]
,[FSourceInterId]
,[FSourceBillNo]
,[FReprocessedAuxQty]
,[FReprocessedQty]
,[FSelDiscardStockInAuxQty]
,[FSelDiscardStockInQty]
,[FDiscardStockInAuxQty]
,[FDiscardStockInQty]
,[FSampleBreakAuxQty]
,[FSampleBreakQty]
,[FResourceID]
,[FAddInterID]
,[FAPSImported]
,[FAPSLastStatus]
,[FAuxPropID]
,[FOrderBOMEntryID])
select top 1
0,(select max(finterid)+1 from icmo),(SELECT FPreLetter+str(fcurno+1,6,0) fcurno FROM IcBillNo WHERE FBILLNAME='生产任务单'),85,0,1052,1054,d.F_101,t1.FItemID,t1.FQty-t1.FCommitQty,0,@FPlanCommitDate,@FPlanFinishDate,NULL,NULL,NULL,GETDATE(),NULL,@FBillerID,t1.FEntryID,0,t1.FNote,t1.FUnitID,0,t1.FQty-t1.FCommitQty,t.FInterID,0,0,0,0,0,0,0,0,NULL,0,NULL,NULL,0,0,0,0,0,b.FInterID,0,0,0,0,0,0,0,0,0,0,0,55,a.FItemID,0,0,0,0,0,0,0,0,0,0,0,0,0,0,'',0,0,0,0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0,0,NULL,NULL,0,t1.FQty-t1.FCommitQty,t1.FQty-t1.FCommitQty,0,t1.FQty-t1.FCommitQty,t1.FQty-t1.FCommitQty,0,'',t1.FEntrySelfS0153,t1.FEntrySelfS0154,0,0,NULL,1,14036,'',0,0,0,NULL,14215,0,'',0,1059,0,t.FPlanCategory,36820,81,t.FInterID,t.FBillNo,0,0,0,0,0,0,0,0,0,0,0,0,0,0
from seorder t join SEOrderEntry t1 on t.FInterID=t1.FInterID and t.FBillNo=@FBillNo and t1.fentryid=@fentryid
join cbCostObj a on a.FStdProductID=t1.FItemID
left join t_Item_3001 d on d.FItemID = t.FHeadSelfS0140
left join (select distinct FNumber,FInterID from vicbom where fusestatus='使用') b on a.FNumber=b.FNumber
order by t.FInterID
update IcBillNo set fcurno=fcurno+1 WHERE FBILLNAME='生产任务单'
end
--commit transaction
if (@@error<>0)
begin
--raiserror 99999 '组织数据出错'
rollback transaction
return (-1)
end
return (0)
USE [AIS20131025233823]
GO
/****** Object: StoredProcedure [dbo].[yu123] Script Date: 2017/8/17 13:52:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--存储过程,由销售订单生成计划状态的生产任务单
--sp_helptext [yu123]
--drop proc [yu123]
--exec [yu123] 16404,1,'SEORD017062','2017-8-14 00:00:00.000','2017-8-18 00:00:00.000'
ALTER procedure [dbo].[yu123]
@FBillerID int, --制单人
@fentryid int, --分录号
@FBillNo varchar(11), --订单编号
@FPlanCommitDate datetime, --开工日期
@FPlanFinishDate datetime --完工日期
AS
BEGIN
SET NOCOUNT ON
DECLARE @errMsg NVARCHAR(MAX)
SET @errMsg=''
BEGIN TRY
BEGIN TRAN
INSERT INTO [dbo].[ICMO]
([FBrNo]
,[FInterID]
,[FBillNo]
,[FTranType]
,[FStatus]
,[FMRP]
,[FType]
,[FWorkShop]
,[FItemID]
,[FQty]
,[FCommitQty]
,[FPlanCommitDate]
,[FPlanFinishDate]
,[FConveyerID]
,[FCommitDate]
,[FCheckerID]
,[FCheckDate]
,[FRequesterID]
,[FBillerID]
,[FSourceEntryID]
,[FClosed]
,[FNote]
,[FUnitID]
,[FAuxCommitQty]
,[FAuxQty]
,[FOrderInterID]
,[FPPOrderInterID]
,[FParentInterID]
,[FCancellation]
,[FSupplyID]
,[FQtyFinish]
,[FQtyScrap]
,[FQtyForItem]
,[FQtyLost]
,[FPlanIssueDate]
,[FRoutingID]
,[FStartDate]
,[FFinishDate]
,[FAuxQtyFinish]
,[FAuxQtyScrap]
,[FAuxQtyForItem]
,[FAuxQtyLost]
,[FMrpClosed]
,[FBomInterID]
,[FQtyPass]
,[FAuxQtyPass]
,[FQtyBack]
,[FAuxQtyBack]
,[FFinishTime]
,[FReadyTIme]
,[FPowerCutTime]
,[FFixTime]
,[FWaitItemTime]
,[FWaitToolTime]
,[FTaskID]
,[FWorkTypeID]
,[FCostObjID]
,[FStockQty]
,[FAuxStockQty]
,[FSuspend]
,[FProjectNO]
,[FProductionLineID]
,[FReleasedQty]
,[FReleasedAuxQty]
,[FUnScheduledQty]
,[FUnScheduledAuxQty]
,[FSubEntryID]
,[FScheduleID]
,[FPlanOrderInterID]
,[FProcessPrice]
,[FProcessFee]
,[FGMPBatchNo]
,[FGMPCollectRate]
,[FGMPItemBalance]
,[FGMPBulkQty]
,[FCustID]
,[FMultiCheckLevel1]
,[FMultiCheckLevel2]
,[FMultiCheckLevel3]
,[FMultiCheckLevel4]
,[FMultiCheckLevel5]
,[FMultiCheckLevel6]
,[FMultiCheckDate1]
,[FMultiCheckDate2]
,[FMultiCheckDate3]
,[FMultiCheckDate4]
,[FMultiCheckDate5]
,[FMultiCheckDate6]
,[FCurCheckLevel]
,[FMRPLockFlag]
,[FHandworkClose]
,[FConfirmerID]
,[FConfirmDate]
,[FInHighLimit]
,[FInHighLimitQty]
,[FAuxInHighLimitQty]
,[FInLowLimit]
,[FInLowLimitQty]
,[FAuxInLowLimitQty]
,[FChangeTimes]
,[FHeadSelfJ0172]
,[FHeadSelfJ0173]
,[FHeadSelfJ0174]
,[FCheckCommitQty]
,[FAuxCheckCommitQty]
,[FCloseDate]
,[FPlanConfirmed]
,[FPlanMode]
,[FMTONo]
,[FPrintCount]
,[FFinClosed]
,[FFinCloseer]
,[FFinClosedate]
,[FStockFlag]
,[FStartFlag]
,[FVchBillNo]
,[FVchInterID]
,[FCardClosed]
,[FHRReadyTime]
,[FPlanCategory]
,[FBomCategory]
,[FSourceTranType]
,[FSourceInterId]
,[FSourceBillNo]
,[FReprocessedAuxQty]
,[FReprocessedQty]
,[FSelDiscardStockInAuxQty]
,[FSelDiscardStockInQty]
,[FDiscardStockInAuxQty]
,[FDiscardStockInQty]
,[FSampleBreakAuxQty]
,[FSampleBreakQty]
,[FResourceID]
,[FAddInterID]
,[FAPSImported]
,[FAPSLastStatus]
,[FAuxPropID]
,[FOrderBOMEntryID])
select top 1
0,(select max(finterid)+1 from icmo),(SELECT FPreLetter+str(fcurno+1,6,0) fcurno FROM IcBillNo WHERE FBILLNAME='生产任务单'),85,0,1052,1054,d.F_101,t1.FItemID,t1.FQty-t1.FCommitQty,0,@FPlanCommitDate,@FPlanFinishDate,NULL,NULL,NULL,GETDATE(),NULL,@FBillerID,t1.FEntryID,0,t1.FNote,t1.FUnitID,0,t1.FQty-t1.FCommitQty,t.FInterID,0,0,0,0,0,0,0,0,NULL,0,NULL,NULL,0,0,0,0,0,b.FInterID,0,0,0,0,0,0,0,0,0,0,0,55,a.FItemID,0,0,0,0,0,0,0,0,0,0,0,0,0,0,'',0,0,0,0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0,0,NULL,NULL,0,t1.FQty-t1.FCommitQty,t1.FQty-t1.FCommitQty,0,t1.FQty-t1.FCommitQty,t1.FQty-t1.FCommitQty,0,'',t1.FEntrySelfS0153,t1.FEntrySelfS0154,0,0,NULL,1,14036,'',0,0,0,NULL,14215,0,'',0,1059,0,t.FPlanCategory,36820,81,t.FInterID,t.FBillNo,0,0,0,0,0,0,0,0,0,0,0,0,0,0
from seorder t join SEOrderEntry t1 on t.FInterID=t1.FInterID and t.FBillNo=@FBillNo and t1.fentryid=@fentryid
join cbCostObj a on a.FStdProductID=t1.FItemID
left join t_Item_3001 d on d.FItemID = t.FHeadSelfS0140
left join (select distinct FNumber,FInterID from vicbom where fusestatus='使用') b on a.FNumber=b.FNumber
order by t.FInterID
update IcBillNo set fcurno=fcurno+1 WHERE FBILLNAME='生产任务单'
COMMIT TRANSACTION
END TRY
BEGIN CATCH
SET @errMsg=ERROR_MESSAGE()
ROLLBACK TRAN
END CATCH
--程序那边用 ExecuteSclar() 来接收,空串表示成功,否则即为失败
SELECT @errMsg
END
GO
USE [AIS20131025233823]
GO
/****** Object: StoredProcedure [dbo].[yu123] Script Date: 2017/8/17 13:52:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--存储过程,由销售订单生成计划状态的生产任务单
--sp_helptext [yu123]
--drop proc [yu123]
--exec [yu123] 16404,1,'SEORD017062','2017-8-14 00:00:00.000','2017-8-18 00:00:00.000'
ALTER PROCEDURE [dbo].[yu123]
@FBillerID INT , --制单人
@fentryid INT , --分录号
@FBillNo VARCHAR(11) , --订单编号
@FPlanCommitDate DATETIME , --开工日期
@FPlanFinishDate DATETIME --完工日期
AS
SET nocount ON
--begin transaction
BEGIN
BEGIN TRY
INSERT INTO [dbo].[ICMO]
( [FBrNo] ,
[FInterID] ,
[FBillNo] ,
[FTranType] ,
[FStatus] ,
[FMRP] ,
[FType] ,
[FWorkShop] ,
[FItemID] ,
[FQty] ,
0 ,
0 ,
0 ,
0 ,
0 ,
0 ,
0
FROM seorder t
JOIN SEOrderEntry t1 ON t.FInterID = t1.FInterID
AND t.FBillNo = @FBillNo
AND t1.fentryid = @fentryid
JOIN cbCostObj a ON a.FStdProductID = t1.FItemID
LEFT JOIN t_Item_3001 d ON d.FItemID = t.FHeadSelfS0140
LEFT JOIN ( SELECT DISTINCT
FNumber ,
FInterID
FROM vicbom
WHERE fusestatus = '使用'
) b ON a.FNumber = b.FNumber
ORDER BY t.FInterID
UPDATE IcBillNo
SET fcurno = fcurno + 1
WHERE FBILLNAME = '生产任务单'
--commit transaction
SELECT 'succeed '
END TRY
BEGIN CATCH
SELECT ERROR_MESSAGE()
IF @@TRANCOUNT>0
ROLLBACK TRANSACTION
END CATCH
END
超过长度了,中间的我删掉了一部分,,你自己补全BEGIN TRY
SELECT 1/0
select 'succeed '
END TRY
BEGIN CATCH
SELECT ERROR_MESSAGE()
END catch
你把这个错误的信息返回到程序里面去,然后在程序里面展示就可以了[/quote]
补充了下,你在try的最后可以返回成功的消息BEGIN TRY
SELECT 1/0
END TRY
BEGIN CATCH
SELECT ERROR_MESSAGE()
END catch
你把这个错误的信息返回到程序里面去,然后在程序里面展示就可以了