金蝶K3供应链初始化存储过程

DESC ,a.FBatchNo,a.FMtoNo,a.FAuxPropID,a.FInterID,a.FSPID,a.FKFDate,a.FKFPeriod,a.FSupplyID --临时表结算余额结转进入ICInvBal INSERT INTO ICInvBal(FBrNo, FYear, FPeriod, FStockID, FStockPlaceID, FItemID, FBatchNo,FMtoNo,

FBegQty, FReceive, FSend, FYtdReceive, FYtdSend, FEndQty, FBegBal, FDebit, FCredit, FYtdDebit, FYtdCredit, FEndBal, FBegDiff, FReceiveDiff, FSendDiff, FYtdReceiveDiff, FYtdSendDiff,

FKFDate, FKFPeriod, FAuxPropID, FSecBegQty, FSecYtdSend, FSecYtdReceive,

FStockInDate,FBillInterID,FSupplyID)

SELECT '0', @CurYear, @Period, FStockID, FSPID, FItemID, FBatchNo, FMtoNo,

FBegQty, FReceive, FSend, FYtdReceive, FYtdSend, FEndQty, FBegBal, FDebit, FCredit, FYtdDebit, FYtdCredit, FEndBal, FBegDiff,FReceiveDiff, FSendDiff, FYtdReceiveDiff, FYtdSendDiff,

FKFDate, FKFPeriod, FAuxPropID, FAuxUnitBegQty, FYtdAuxUnitSend, FYtdAuxUnitReceive,

FStockInDate,FBillInterID,FSupplyID FROM #ICInvBalTemp UPDATE t1 SET FBillInterID=0 FROM icinvbal t1 inner join t_icitem t2 on t1.FItemID=t2.FItemID where t2.FTrack not in (78,79,20308) --从ICinvBal导数据到ICBal，不用包含VMI仓数据

INSERT INTO ICBal(FBrNo,FYear,FPeriod,FItemID,FBatchNo,FStockGroupID, FBegQty,FReceive,FSend,FYtdReceive,FYtdSend,FEndQty, FBegBal,FDebit,FCredit,FYtdDebit,FYtdCredit,FEndBal,

FBegDiff,FReceiveDiff,FSendDiff,FYtdReceiveDiff,FYtdSendDiff,

FBillInterID,FAuxPropID,FSecBegQty,FSecYtdSend,FSecYtdReceive,FStockInDate) SELECT '0',@CurYear,@Period,t1.FItemID,t1.FBatchNo,t2.FGroupID,

SUM(FBegQty),SUM(FReceive),SUM(FSend),SUM(FYtdReceive),SUM(FYtdSend),SUM(FEndQty),

SUM(FBegBal),SUM(FDebit),SUM(FCredit),SUM(FYtdDebit),SUM(FYtdCredit),SUM(FEndBal),

SUM(FBegDiff),SUM(FReceiveDiff),SUM(FSendDiff),SUM(FYtdReceiveDiff),SUM(FYtdSendDiff),

t1.FBillInterID,t1.FAuxPropID,SUM(FSecBegQty),SUM(FSecYtdSend),SUM(FSecYtdReceive),t1.FStockInDate

FROM ICInvBal t1 INNER JOIN t_Stock t2 ON t1.FStockID=t2.FItemID WHERE t2.FTypeID <> 504 GROUP BY t2.FGroupID,t1.FItemID,t1.FBatchNo,t1.FAuxPropID,t1.FStockInDate,t1.FBillInterID --VMI仓库存余额

--非后进先出和先进先出按批号＋入库日期排序重新分配顺序号 INSERT INTO #ICVMIInvBalTemp (FStockID,FSPID,

FItemID,FBatchNo,FMtoNo,FOldBillInterID,

FBegQty,FReceive,FSend,FYtdReceive,FYtdSend,FEndQty, FBegBal,FDebit,FCredit,FYtdDebit,FYtdCredit,FEndBal,

FBegDiff,FReceiveDiff,FSendDiff,FYtdReceiveDiff,FYtdSendDiff, FAuxPropID,FSecBegQty,FSecYtdSend,FSecYtdReceive,FStockInDate,

FKFDate,FKFPeriod,FAuxUnitBegQty,FYtdAuxUnitSend,FYtdAuxUnitReceive,FSupplyID) SELECT a.FStockID, a.FSPID,

a.FItemID,a.FBatchNo,a.FMtoNo,a.FInterID,

a.FBegQty,a.FReceive,a.FSend,a.FYtdReceive,a.FYtdSend,a.FEndQty, a.FBegBal,a.FDebit,a.FCredit,a.FYtdDebit,a.FYtdCredit,a.FEndBal,

a.FBegDiff,a.FReceiveDiff,a.FSendDiff,a.FYtdReceiveDiff,a.FYtdSendDiff,

a.FAuxPropID,a.FSecBegQty,a.FSecYtdSend,a.FSecYtdReceive,a.FStockInDate,

a.FKFDate,a.FKFPeriod,a.FAuxUnitBegQty,a.FYtdAuxUnitSend,a.FYtdAuxUnitReceive,a.FSupplyID FROM (SELECT t1.FStockID, t1.FSPID,

t1.FItemID,t1.FBatchNo,t1.FMtoNo, Sum(t1.FBegQty) AS FBegQty,Sum(t1.FReceive) As Freceive ,Sum(t1.FSend) As FSend,Sum(t1.FYtdReceive) As FYtdReceive, Sum(t1.FYtdSend) As FYtdSend,Sum(t1.FEndQty) As FEndQty,Sum(t1.FBegBal) As FBegBal,Sum(t1.Fdebit) As Fdebit, Sum(t1.Fcredit) As Fcredit,Sum(t1.FYtdDebit) As FYtdDebit,Sum(t1.FYtdCredit) As FYtdCredit,Sum(t1.FEndBal) As FEndBal, Sum(t1.FBegDiff) As FBegDiff,Sum(t1.FReceiveDiff) As FReceiveDiff,Sum(t1.FSendDiff) As FSendDiff,

Sum(t1.FYtdReceiveDiff) As FYtdReceiveDiff,Sum(t1.FYtdSendDiff) As FYtdSendDiff, FAuxPropID,Sum(FAuxUnitBegQty) As FSecBegQty, t1.FBillInterID As FInterID, Sum(FYtdAuxUnitSend) As FSecYtdSend,Sum(FYtdAuxUnitReceive) As FSecYtdReceive,FStockInDate,

t1.FKFDate,t1.FKFPeriod,SUM(t1.FAuxUnitBegQty) As FAuxUnitBegQty, SUM(t1.FYtdAuxUnitSend) As FYtdAuxUnitSend,SUM(t1.FYtdAuxUnitReceive) As FYtdAuxUnitReceive,t1.FSupplyID

FROM ICInvInitial t1 INNER JOIN t_icitem t3 ON t1.FItemID=t3.FItemID

INNER JOIN t_Stock t4 ON t1.FStockID=t4.FItemID

WHERE t1.FPeriod=@Period And t3.FTrack<>79 And t3.FTrack<>78 AND t4.FTypeID = 504

GROUP BY t1.FStockID,t1.FItemID,t1.FBatchNo,t1.FMtoNo,FAuxPropID,FStockInDate,t1.FBillInterID,t1.FSPID,t1.FKFDate,t1.FKFPeriod,t1.FSupplyID) a ORDER BY a.FStockID,a.FItemID,a.FBatchNo,a.FMtoNo,a.FStockInDate,a.FAuxPropID,a.FInterID,a.FSPID,a.FKFDate,a.FKFPeriod,a.FSupplyID --普通仓ICInvInitial数据结转入临时表 --先进先出法按入库日期正排 INSERT INTO #ICVMIInvBalTemp (FStockID,FSPID,

FItemID,FBatchNo,FMtoNo,FOldBillInterID,

FBegQty,FReceive,FSend,FYtdReceive,FYtdSend,FEndQty, FBegBal,FDebit,FCredit,FYtdDebit,FYtdCredit,FEndBal,

FBegDiff,FReceiveDiff,FSendDiff,FYtdReceiveDiff,FYtdSendDiff, FAuxPropID,FSecBegQty,FSecYtdSend,FSecYtdReceive,FStockInDate,

FKFDate,FKFPeriod,FAuxUnitBegQty,FYtdAuxUnitSend,FYtdAuxUnitReceive,FSupplyID) SELECT a.FStockID, a.FSPID,

a.FItemID,a.FBatchNo,a.FMtoNo,a.FInterID,

a.FBegQty,a.FReceive,a.FSend,a.FYtdReceive,a.FYtdSend,a.FEndQty, a.FBegBal,a.FDebit,a.FCredit,a.FYtdDebit,a.FYtdCredit,a.FEndBal,

a.FBegDiff,a.FReceiveDiff,a.FSendDiff,a.FYtdReceiveDiff,a.FYtdSendDiff,

a.FAuxPropID,a.FSecBegQty,a.FSecYtdSend,a.FSecYtdReceive,a.FStockInDate,

a.FKFDate,a.FKFPeriod,a.FAuxUnitBegQty,a.FYtdAuxUnitSend,a.FYtdAuxUnitReceive,a.FSupplyID FROM (SELECT t1.FStockID, t1.FSPID,

t1.FItemID,t1.FBatchNo,t1.FMtoNo, Sum(t1.FBegQty) AS FBegQty,Sum(t1.FReceive) As Freceive ,Sum(t1.FSend) As FSend,Sum(t1.FYtdReceive) As FYtdReceive, Sum(t1.FYtdSend) As FYtdSend,Sum(t1.FEndQty) As FEndQty,Sum(t1.FBegBal) As FBegBal,Sum(t1.Fdebit) As Fdebit, Sum(t1.Fcredit) As Fcredit,Sum(t1.FYtdDebit) As FYtdDebit,Sum(t1.FYtdCredit) As FYtdCredit,Sum(t1.FEndBal) As FEndBal, Sum(t1.FBegDiff) As FBegDiff,Sum(t1.FReceiveDiff) As FReceiveDiff,Sum(t1.FSendDiff) As FSendDiff,

Sum(t1.FYtdReceiveDiff) As FYtdReceiveDiff,Sum(t1.FYtdSendDiff) As FYtdSendDiff, FAuxPropID,Sum(FAuxUnitBegQty) As FSecBegQty, t1.FBillInterID As FInterID, Sum(FYtdAuxUnitSend) As

FSecYtdSend,Sum(FYtdAuxUnitReceive) As FSecYtdReceive,FStockInDate,

t1.FKFDate,t1.FKFPeriod,Sum(t1.FAuxUnitBegQty) As FAuxUnitBegQty, Sum(t1.FYtdAuxUnitSend) As FYtdAuxUnitSend,Sum(t1.FYtdAuxUnitReceive) As FYtdAuxUnitReceive,t1.FSupplyID

FROM ICInvInitial t1 INNER JOIN t_icitem t3 ON t1.FItemID=t3.FItemID INNER JOIN t_Stock t4 ON t1.FStockID=t4.FItemID

WHERE t1.FPeriod=@Period And t3.FTrack= 78 AND t4.FTypeID = 504 GROUP BY t1.FStockID,t1.FItemID,t1.FBatchNo,t1.FMtoNo,FAuxPropID,FStockInDate,t1.FBillInterID,t1.FSPID,t1.FKFDate,t1.FKFPeriod,t1.FSupplyID) a ORDER BY a.FStockID,a.FItemID,a.FStockInDate ,a.FBatchNo,a.FMtoNo,a.FAuxPropID,a.FInterID,a.FSPID,a.FKFDate,a.FKFPeriod,a.FSupplyID --普通仓ICInvInitial数据结转入临时表 --后进先出法按入库日期倒排 INSERT INTO #ICVMIInvBalTemp (FStockID,FSPID,

FItemID,FBatchNo,FMtoNo,FOldBillInterID,

FBegQty,FReceive,FSend,FYtdReceive,FYtdSend,FEndQty, FBegBal,FDebit,FCredit,FYtdDebit,FYtdCredit,FEndBal,

FBegDiff,FReceiveDiff,FSendDiff,FYtdReceiveDiff,FYtdSendDiff, FAuxPropID,FSecBegQty,FSecYtdSend,FSecYtdReceive,FStockInDate,

FKFDate,FKFPeriod,FAuxUnitBegQty,FYtdAuxUnitSend,FYtdAuxUnitReceive,FSupplyID) SELECT a.FStockID, a.FSPID,

a.FItemID,a.FBatchNo,a.FMtoNo,a.FInterID,

a.FBegQty,a.FReceive,a.FSend,a.FYtdReceive,a.FYtdSend,a.FEndQty, a.FBegBal,a.FDebit,a.FCredit,a.FYtdDebit,a.FYtdCredit,a.FEndBal,

a.FBegDiff,a.FReceiveDiff,a.FSendDiff,a.FYtdReceiveDiff,a.FYtdSendDiff,

a.FAuxPropID,a.FSecBegQty,a.FSecYtdSend,a.FSecYtdReceive,a.FStockInDate,

a.FKFDate,a.FKFPeriod,a.FAuxUnitBegQty,a.FYtdAuxUnitSend,a.FYtdAuxUnitReceive,a.FSupplyID FROM (SELECT t1.FStockID, t1.FSPID,

t1.FItemID,t1.FBatchNo,t1.FMtoNo, Sum(t1.FBegQty) AS FBegQty,Sum(t1.FReceive) As Freceive ,Sum(t1.FSend) As FSend,Sum(t1.FYtdReceive) As FYtdReceive, Sum(t1.FYtdSend) As FYtdSend,Sum(t1.FEndQty) As FEndQty,Sum(t1.FBegBal) As FBegBal,Sum(t1.Fdebit) As Fdebit, Sum(t1.Fcredit) As Fcredit,Sum(t1.FYtdDebit) As FYtdDebit,Sum(t1.FYtdCredit) As FYtdCredit,Sum(t1.FEndBal) As FEndBal, Sum(t1.FBegDiff) As FBegDiff,Sum(t1.FReceiveDiff) As