FDebit DECIMAL (20, 2) NOT NULL DEFAULT (0), FCredit DECIMAL (20, 2) NOT NULL DEFAULT (0), FYtdDebit DECIMAL (20, 2) NOT NULL DEFAULT (0), FYtdCredit DECIMAL (20, 2) NOT NULL DEFAULT (0), FEndBal DECIMAL (20, 2) NOT NULL DEFAULT (0), FBegDiff DECIMAL (20, 2) NOT NULL DEFAULT (0), FReceiveDiff DECIMAL (20, 2) NOT NULL DEFAULT (0), FSendDiff DECIMAL (20, 2) NOT NULL DEFAULT (0), FEndDiff DECIMAL (20, 2) NOT NULL DEFAULT (0), FBillInterID INT IDENTITY(1,1),
FOldBillInterID INT NOT NULL DEFAULT (0), FEntryID INT NOT NULL DEFAULT (0),
FYtdReceiveDiff DECIMAL (20, 2) NOT NULL DEFAULT (0),
FYtdSendDiff DECIMAL (20, 2) NOT NULL DEFAULT (0), FSecBegQty DECIMAL (28, 10) NOT NULL DEFAULT (0), FSecReceive DECIMAL (28, 10) NOT NULL DEFAULT (0), FSecSend DECIMAL (28, 10) NOT NULL DEFAULT (0), FSecYtdReceive DECIMAL (28, 10) NOT NULL DEFAULT (0), FSecYtdSend DECIMAL (28, 10) NOT NULL DEFAULT (0), FSecEndQty DECIMAL (28, 10) NOT NULL DEFAULT (0), FStockInDate VARCHAR (20) NOT NULL DEFAULT (''), FAuxPropID INT NOT NULL DEFAULT (0),
FKFDate VARCHAR (20) NOT NULL DEFAULT (''), FKFPeriod INT NOT NULL DEFAULT (0),
FAuxUnitBegQty DECIMAL (28, 10) NOT NULL DEFAULT(0), FYtdAuxUnitSend DECIMAL (28, 10) NOT NULL DEFAULT(0), FYtdAuxUnitReceive DECIMAL (28, 10) NOT NULL DEFAULT(0), FSupplyID INT NOT NULL DEFAULT (0) ) CREATE TABLE #ICVMIInvBalTemp (
FStockID INT NOT NULL DEFAULT (0), FSPID INT NOT NULL DEFAULT (0), FItemID INT NOT NULL,
FBatchNo VARCHAR (200) NOT NULL DEFAULT (''), FMtoNo NVARCHAR (50) NOT NULL DEFAULT (''), FBegQty DECIMAL (28, 10) DEFAULT (0), FReceive DECIMAL (28, 10) DEFAULT (0), FSend DECIMAL (28, 10) DEFAULT (0), FYtdReceive DECIMAL (28, 10) DEFAULT (0), FYtdSend DECIMAL (28, 10) DEFAULT (0), FEndQty DECIMAL (28, 10) DEFAULT (0),
FBegBal DECIMAL (20, 2) NOT NULL DEFAULT (0),
FDebit DECIMAL (20, 2) NOT NULL DEFAULT (0), FCredit DECIMAL (20, 2) NOT NULL DEFAULT (0), FYtdDebit DECIMAL (20, 2) NOT NULL DEFAULT (0), FYtdCredit DECIMAL (20, 2) NOT NULL DEFAULT (0), FEndBal DECIMAL (20, 2) NOT NULL DEFAULT (0), FBegDiff DECIMAL (20, 2) NOT NULL DEFAULT (0), FReceiveDiff DECIMAL (20, 2) NOT NULL DEFAULT (0), FSendDiff DECIMAL (20, 2) NOT NULL DEFAULT (0), FEndDiff DECIMAL (20, 2) NOT NULL DEFAULT (0), FBillInterID INT IDENTITY(1,1),
FOldBillInterID INT NOT NULL DEFAULT (0), FEntryID INT NOT NULL DEFAULT (0),
FYtdReceiveDiff DECIMAL (20, 2) NOT NULL DEFAULT (0),
FYtdSendDiff DECIMAL (20, 2) NOT NULL DEFAULT (0), FSecBegQty DECIMAL (28, 10) NOT NULL DEFAULT (0), FSecReceive DECIMAL (28, 10) NOT NULL DEFAULT (0), FSecSend DECIMAL (28, 10) NOT NULL DEFAULT (0), FSecYtdReceive DECIMAL (28, 10) NOT NULL DEFAULT (0), FSecYtdSend DECIMAL (28, 10) NOT NULL DEFAULT (0), FSecEndQty DECIMAL (28, 10) NOT NULL DEFAULT (0), FStockInDate VARCHAR (20) NOT NULL DEFAULT (''), FAuxPropID INT NOT NULL DEFAULT (0),
FKFDate VARCHAR (20) NOT NULL DEFAULT (''), FKFPeriod INT NOT NULL DEFAULT (0),
FAuxUnitBegQty DECIMAL (28, 10) NOT NULL DEFAULT(0), FYtdAuxUnitSend DECIMAL (28, 10) NOT NULL DEFAULT(0), FYtdAuxUnitReceive DECIMAL (28, 10) NOT NULL DEFAULT(0), FSupplyID INT NOT NULL DEFAULT (0) ) --普通仓ICInvInitial数据结转入临时表
--非后进先出和先进先出按批号＋入库日期排序重新分配顺序号 INSERT INTO #ICInvBalTemp (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) 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 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
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) a ORDER BY a.FStockID,a.FItemID,a.FBatchNo,a.FMtoNo,a.FStockInDate,a.FAuxPropID,a.FInterID,a.FSPID,a.FKFDate,a.FKFPeriod --普通仓ICInvInitial数据结转入临时表 --先进先出法按入库日期正排 INSERT INTO #ICInvBalTemp (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)
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 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
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) a ORDER BY a.FStockID,a.FItemID,a.FStockInDate ,a.FBatchNo,a.FMtoNo,a.FAuxPropID,a.FInterID,a.FSPID,a.FKFDate,a.FKFPeriod --普通仓ICInvInitial数据结转入临时表 --后进先出法按入库日期倒排 INSERT INTO #ICInvBalTemp (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)