--由于金蝶系统庞大，而且每个企业的设置内容都不一样，单据自定义上也有许多差异，本程序仅提供参考，切勿照搬
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 --用于查询错误位置
浙公网安备 33010602011771号