--由于金蝶系统庞大，而且每个企业的设置内容都不一样，单据自定义上也有许多差异，本程序仅提供参考，切勿照搬 begin transaction declare @error int set @error = 0 --定义事务，更新金蝶数据库时，肯定涉及多张表，多个SQL语句，如果其中一个出现错误，就需要全部返回 declare @v varchar(20) set @v='' --记录错误位置，事务只会告诉你有错误，但不会告诉你错在哪里 declare @fbill varchar(20) set @fbill=( select isnull(max(right(FBillNo,6)),0) from POInStock where FBillNo like 'DDH%' ) --生产单据编号，金蝶是从一张单据表上获取新的单据编号，但我的单据编号规则是不一样，所以不管那张单据编号表 declare @int int set @int=CAST(@fbill as int)+1 set @fbill='DDH'+LEFT('000000',6-LEN(@int))+CAST(@int as varchar(10)) declare @fid int set @fid=(select FMaxNum+1 from ICMaxNum where FTableName='POInStock') --生产POInStock表里新的内码 declare @supplier varchar(20) --这一段是生产批号，你们如果没有批号设置，或批号与我这不一样的，可以不用看 set @supplier=(select distinct b.FBillNo from POOrder a inner join t_Supplier b on a.FSupplyID=b.FItemID where a.FInterID=10862) declare @z int set @z=0 declare @batch varchar(20) set @batch='' while(@batch<>'.') begin set @z=@z+1 set @batch=SUBSTRING(@supplier,@z,1) end set @batch=RIGHT(@supplier,LEN(@supplier)-@z) INSERT INTO POInstockEntry ( --插入表体 FInterID,FEntryID,FBrNo,FMapNumber,FMapName,FItemID,FAuxPropID,FBatchNo,FQty,FUnitID,Fauxqty,FSecCoefficient, FSecQty,FDischarged,FCheckMethod,Fauxprice,Famount,Fnote,FKFDate,FKFPeriod,FPeriodDate,FStockID,FDCSPID,FSourceBillNo, FSourceTranType,FSourceInterId,FSourceEntryID,FContractBillNo,FContractInterID,FContractEntryID,FOrderBillNo,FOrderInterID, FOrderEntryID,FPlanMode,FMTONo,FOrderType,FAuxQtyPass,FQtyPass,FSecQtyPass,FAuxConPassQty,FConPassQty,FSecConPassQty, FAuxNotPassQty,FNotPassQty,FSecNotPassQty,FAuxSampleBreakQty,FSampleBreakQty,FSecSampleBreakQty,FScrapQty,FAuxScrapQty, FSecScrapQty,FAuxRelateQty,FRelateQty,FSecRelateQty,FAuxQCheckQty,FQCheckQty,FSecQCheckQty,FAuxBackQty,FBackQty,FSecBackQty, FScrapInCommitQty,FAuxScrapInCommitQty,FSecScrapInCommitQty,FDeliveryNoticeFID,FDeliveryNoticeEntryID,FTime,FSamBillNo,FSamInterID, FSamEntryID,FEntrySelfP0377,FEntrySelfP0378,FEntrySelfP0379,FEntrySelfP0380,FEntrySelfP0381,FPickQty,FAuxPickQty,FSecPickQty,FPrice) select @fid,1,'0','','',b.FItemID,0,@batch,10.00,b.FUnitID,10.00,0,0,1059,b.FCheckMethod,b.FAuxPrice,b.FQty*b.FPrice, b.FNote,Null,0,Null,54069,0,a.FBillNo,71,a.FinterID,b.FEntryID,'',0,0, a.FBillNo,a.FInterId,b.FEntryID,14036,'',71,0,0,0,0,0,0,0,0,0,0,0,0,0,0, 0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,'',0,0,0,0,'',b.FEntrySelfP0268,b.FEntrySelfP0269,0,0,0,b.FPrice from POOrder a inner join POOrderEntry b on a.FInterID=b.FInterID inner join t_ICItem c on b.FItemID=c.FItemID where b.FInterID='10862' and b.FEntryID='1' if (@@ROWCOUNT = 0) begin set @error = @error + 1 set @error = @error + @@error set @v='表单1错误' --如果执行行数是0行，或执行出现错误，则记录错误，以后期回滚 end update POOrderEntry set FCommitQty=z.Fqty ,FAuxCommitQty=z.Fqty ,FAuxReceiptQty=z.Fqty ,FReceiptQty= z.Fqty --采购订单与收料通知单是关联性单据，由采购订单下推的收料通知单，需要回填采购订单上的字段 from ( select SUM(FQty) as Fqty,FSourceInterId,FSourceEntryID from POInStockEntry where FSourceInterId in (10862) and FSourceInterId<>0 group by FSourceInterId,FSourceEntryID ) z inner join POOrderEntry a on a.FInterID=z.FSourceInterId and a.FEntryID=z.FSourceEntryID if (@@ROWCOUNT = 0) begin set @error = @error + 1 set @error = @error + @@error set @v='更新订单数量错误' end update POOrder set FClosed=1,FStatus=3,FCloseUser=16394,FCloseDate=CONVERT(varchar(100), GETDATE(), 20),FCloseCauses='系统自动关闭' --如果采购订单下推完收料通知单，则需要关闭这张采购订单 where FInterID in ( select a.FInterID from POOrder a inner join POOrderEntry b on a.FInterID=b.FInterID where a.FInterID in (10862)group by a.FInterID having (SUM(FQty)-SUM(FAuxCommitQty))=0) if(@@error>0) begin set @error = @error + @@error set @v='更新订单审核关闭错误' end update POOrder set FStatus=2 where FInterID in ( --审核状态 FStatus=1,部分关联 2;关闭是 3 select a.FInterID from POOrder a inner join POOrderEntry b on a.FInterID=b.FInterID where a.FInterID in (10862) group by a.FInterID having (SUM(FQty)-SUM(FAuxCommitQty))<>0) if(@@error>0) begin set @error = @error + @@error set @v='更新订单 关联状态错误' end INSERT INTO POInstock( --插入表头 FInterID,FBillNo,FBrNo,FTranType,FCancellation,FStatus,FUpStockWhenSave,Fdate,FSupplyID,FCheckDate, FFManagerID,FDeptID,FEmpID,FBillerID,FCurrencyID,FBizType,FExchangeRateType,FExchangeRate,FPOStyle,FWWType, FRelateBrID,FMultiCheckDate1,FMultiCheckDate2,FMultiCheckDate3,FMultiCheckDate4,FMultiCheckDate5,FMultiCheckDate6, FSelTranType,FFetchAdd,FExplanation,FAreaPS,FManageType,FPOMode,FPrintCount,FHeadSelfP0337,FHeadSelfP0342) select @fid,@fbill,'0',72,0,0,0,convert(varchar(10),getdate(),120),a.FSupplyID,Null,0,25075,25091,16394,1,12510,1,1, 252,0,0,Null,Null,Null,Null,Null,Null,71,'','',20302,0,36680,0,39464,CONVERT(varchar(100), GETDATE(), 20) from POOrder a inner join POOrderEntry b on a.FInterID=b.FInterID inner join t_ICItem c on b.FItemID=c.FItemID where b.FInterID='10862' and b.FEntryID='1' if (@@ROWCOUNT = 0) begin set @error = @error + 1 set @error = @error + @@error set @v='表体错误' end update ICMaxNum set FMaxNum=@fid where FTableName='POInStock' --更新内码表 if(@@ROWCOUNT=0) begin set @error=@error+1 set @error = @error + @@error set @v='更新内码错误' end update POOrder set FChildren=FChildren+1 where FInterID='10862' --回填采购订单，采购订单的子单据+1 if(@@ROWCOUNT=0) begin set @error=@error+1 set @error = @error + @@error set @v='更新Children错误' end if @error <> 0 --如果出现错误，则事务回滚，本次执行的SQL全部失效 begin rollback transaction end else begin commit transaction end select @error --如果为0，表示执行成功！大于0，表示执行失败 --select @v --用于查询错误位置
联系客服
