--由于金蝶系統(tǒng)龐大，而且每個(gè)企業(yè)的設(shè)置內(nèi)容都不一樣，單據(jù)自定義上也有許多差異，本程序僅提供參考，切勿照搬
 
 
 begin transaction declare @error  int set @error = 0            --定義事務(wù)，更新金蝶數(shù)據(jù)庫時(shí)，肯定涉及多張表，多個(gè)SQL語句，如果其中一個(gè)出現(xiàn)錯(cuò)誤，就需要全部返回
 
 declare @v varchar(20) set @v=''                --記錄錯(cuò)誤位置，事務(wù)只會告訴你有錯(cuò)誤，但不會告訴你錯(cuò)在哪里
   
 declare @fbill varchar(20) set @fbill=( select isnull(max(right(FBillNo,6)),0) from POInStock where FBillNo like 'DDH%' )   --生產(chǎn)單據(jù)編號，金蝶是從一張單據(jù)表上獲取新的單據(jù)編號，但我的單據(jù)編號規(guī)則是不一樣，所以不管那張單據(jù)編號表
 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')        --生產(chǎn)POInStock表里新的內(nèi)碼
 
 declare @supplier varchar(20)            --這一段是生產(chǎn)批號，你們?nèi)绻麤]有批號設(shè)置，或批號與我這不一樣的，可以不用看 
 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錯(cuò)誤'        --如果執(zhí)行行數(shù)是0行，或執(zhí)行出現(xiàn)錯(cuò)誤，則記錄錯(cuò)誤，以后期回滾
 end  
 
 update POOrderEntry set FCommitQty=z.Fqty ,FAuxCommitQty=z.Fqty  ,FAuxReceiptQty=z.Fqty  ,FReceiptQty=  z.Fqty   --采購訂單與收料通知單是關(guān)聯(lián)性單據(jù)，由采購訂單下推的收料通知單，需要回填采購訂單上的字段
 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='更新訂單數(shù)量錯(cuò)誤' 
 end   
 
 update POOrder set  FClosed=1,FStatus=3,FCloseUser=16394,FCloseDate=CONVERT(varchar(100), GETDATE(), 20),FCloseCauses='系統(tǒng)自動關(guān)閉'        --如果采購訂單下推完收料通知單，則需要關(guān)閉這張采購訂單
 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='更新訂單審核關(guān)閉錯(cuò)誤' 
 end    
 
 update POOrder set  FStatus=2 where FInterID in (                --審核狀態(tài) FStatus=1,部分關(guān)聯(lián) 2;關(guān)閉是 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='更新訂單 關(guān)聯(lián)狀態(tài)錯(cuò)誤' 
 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='表體錯(cuò)誤' 
 end  
 
 update ICMaxNum set FMaxNum=@fid where FTableName='POInStock'            --更新內(nèi)碼表   
 if(@@ROWCOUNT=0) 
 begin  
 set @error=@error+1 set  @error = @error + @@error set @v='更新內(nèi)碼錯(cuò)誤' 
 end  
 
 update POOrder set FChildren=FChildren+1 where FInterID='10862'        --回填采購訂單，采購訂單的子單據(jù)+1
 if(@@ROWCOUNT=0) 
 begin 
 set @error=@error+1 set   @error = @error + @@error set @v='更新Children錯(cuò)誤' 
 end  
 
 if  @error <> 0                --如果出現(xiàn)錯(cuò)誤，則事務(wù)回滾，本次執(zhí)行的SQL全部失效
 begin 
 rollback  transaction  
 end 
 else 
 begin commit transaction  
 end 
 select @error            --如果為0，表示執(zhí)行成功！大于0，表示執(zhí)行失敗
 --select @v            --用于查詢錯(cuò)誤位置