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

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON go

ALTER PROCEDURE [dbo].[StockINIDataOn] AS

SET NOCOUNT ON

DECLARE @pPeriodTmp VARCHAR (255)

DECLARE @Period INT --当前会计期间 DECLARE @Ptype VARCHAR (255)

DECLARE @CurYear INT --当前年份 DECLARE @CalType INT --核算方式 DECLARE @CurStartDate DATETIME DECLARE @CurEndDate DATETIME

DECLARE @BillNo VARCHAR (255) --单据号码

DECLARE @AuditChoice INT ---核算方式，--数量核算，--数量、金额核算

--电子行业：序列号检查

--检查SystemINISNDataOn存储过程的存在，存在则执行

IF EXISTS(SELECT 1 FROM sysobjects WHERE name = 'SystemINISNDataOn') EXEC SystemINISNDataOn

--电子行业：序列号检查 SET @AuditChoice = 0

SELECT @CurYear = CONVERT(INT, FValue) FROM t_SystemProfile WHERE FCategory = 'IC' AND FKey = 'CurrentYear'

IF @CurYear = NULL BEGIN

RAISERROR('~$~系统参数: 《IC--CurrentYear》数据被破坏^|^！',16,1) RETURN 0 END

EXEC GetSysPrm 'IC', 'CurrentPeriod', @pPeriodTmp OUTPUT, @pType OUTPUT

SELECT @Period = CONVERT(INT, @pPeriodTmp)

SELECT @CalType = CONVERT(INT, Fvalue) FROM t_SystemProfile WHERE FCategory = 'IC' AND FKey = 'CalculateType'

If @CalType = Null BEGIN

RAISERROR('~$~系统参数: 《IC--CalculateType 数据被破坏^|^！',16,1) RETURN 0

END

IF @CalType = 2 BEGIN

IF EXISTS (SELECT 1

FROM t_Stock

WHERE FTypeID IN (500, 20291, 20293) AND FStockGroupID = 0 ) BEGIN

RAISERROR('~$~系统检测到在分组核算模式下，还有实仓未分组^|^', 16, 1) RETURN 0 END

UPDATE t_Stock SET FGroupID = FStockGroupID WHERE FTypeID IN (500, 20291, 20293) END

IF @CalType = 1 BEGIN

UPDATE t_Stock SET FGroupID = FItemID WHERE FTypeID IN (500, 20291, 20293) END

IF @CalType = 0 BEGIN

UPDATE t_Stock SET FGroupID = 0 WHERE FTypeID IN (500, 20291, 20293) END

--当前会计期间的起止日期

EXEC GetPeriodStartEnd 0, @Period, @CurStartDate OUTPUT, @CurEndDate OUTPUT

IF (SELECT COUNT(*) FROM (SELECT DISTINCT FPeriod FROM ICInvInitial) AS t1) = 1

BEGIN

UPDATE ICInvInitial SET FPeriod = @Period UPDATE ICInvInitialAdj SET FPeriod = @Period END

ELSE IF (SELECT COUNT(*) FROM (SELECT DISTINCT FPeriod FROM ICInvInitial) AS t1) > 1 BEGIN

RAISERROR('~$~系统提示: 系统初始化表《ICInvInitial》含有多期数据，不符合启用条件^|^！', 16, 1) RETURN 0 END

SELECT @BillNo = FBillNo FROM ICStockBill

WHERE (FCheckerID IS NULL OR FCheckerID = 0) AND FTranType = 28 AND FDate < @CurStartDate

IF @@ROWCOUNT > 0 BEGIN

SET @BillNo = '~$~系统提示: 委外加工出库单--^|^' + @BillNo + '^|^~$~未审核，不符合启用条件^|^！'

RAISERROR(@BillNo, 16, 1) RETURN 0 END

SELECT @BillNo = FBillNo FROM ICStockBill

WHERE (FCheckerID IS NULL OR FCheckerID = 0) AND FTranType = 5 AND FDate < @CurStartDate

IF @@ROWCOUNT > 0 BEGIN

SET @BillNo = '~$~系统提示: 委外加工入库单--^|^' + @BillNo + '^|^~$~未审核，不符合启用条件^|^！'

RAISERROR(@BillNo, 16, 1) RETURN 0 END

--数量核算账套不检查金额

SELECT @AuditChoice = FValue FROM t_SystemProfile WHERE FKey = 'AuditChoice' AND FCategory = 'IC'

IF @AuditChoice = 1 BEGIN

SELECT @BillNo = B.FBillNo FROM ICStockBill B

INNER JOIN ICStockBillEntry E ON B.FInterID = E.FInterID AND B.FTranType = 28 AND E.FAmount = 0

WHERE B.FDate < @CurStartDate

IF @@ROWCOUNT > 0 BEGIN

SET @BillNo = '~$~系统提示: 委外加工出库单--^|^' + @BillNo + '^|^~$~中存在金额为的分录，不符合启用条件^|^！' RAISERROR(@BillNo, 16, 1) RETURN 0 END

SELECT @BillNo = B.FBillNo FROM ICStockBill B

INNER JOIN ICStockBillEntry E ON B.FInterID = E.FInterID AND B.FTranType = 5

AND (E.FMaterialCost = 0 OR E.FMaterialCostPrice = 0)

WHERE B.FDate < @CurStartDate

IF @@ROWCOUNT > 0 BEGIN

SET @BillNo = '~$~系统提示: 委外加工入库单--^|^' + @BillNo + '^|^~$~中存在金额为的分录，不符合启用条件^|^！' RAISERROR(@BillNo, 16, 1) RETURN 0 END END

--系统不允许负库存的时候，不允许出现负数 IF EXISTS (SELECT top 1 1 FROM ICInvInitial ti

INNER JOIN t_Stock ts ON ti.FStockID = ts.FItemID WHERE FBegQty < 0 AND ts.FUnderStock=0) BEGIN

RAISERROR('~$~系统不允许负库存，但是期初结存已经录入负数，不符合启用条件^|^！',16,1)

RETURN 0 END

TRUNCATE TABLE ICInvBal TRUNCATE TABLE ICBal TRUNCATE TABLE ICVMIInvBal

CREATE TABLE #ICInvBalTemp (

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) ) 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)

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 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 DESC ,a.FBatchNo,a.FMtoNo,a.FAuxPropID,a.FInterID,a.FSPID,a.FKFDate,a.FKFPeriod --VMI仓结算余额来自ICInvInitialAdj --非后进先出和先进先出按批号＋入库日期排序重新分配顺序号 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,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 ICInvInitialAdj 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 --VMI仓结算余额来自ICInvInitialAdj --先进先出法按入库日期正排 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,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 ICInvInitialAdj 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 --VMI仓结算余额来自ICInvInitialAdj

--后进先出法按入库日期倒排 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,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 ICInvInitialAdj 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 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

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

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 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 DESC ,a.FBatchNo,a.FMtoNo,a.FAuxPropID,a.FInterID,a.FSPID,a.FKFDate,a.FKFPeriod,a.FSupplyID --VMI临时表结算库存余额结转进入ICVMIInvBal INSERT INTO ICVMIInvBal(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 #ICVMIInvBalTemp UPDATE t1 SET FBillInterID=0 FROM ICVMIInvBal t1 inner join t_icitem t2 on t1.FItemID=t2.FItemID where t2.FTrack not in (78,79,20308) --即时库存来自库存初始化表ICInvInitial TRUNCATE TABLE ICInventory

INSERT INTO ICInventory (FBrNo, FItemID, FBatchNo,FMtoNo, FStockID, FQty, FBal, FStockPlaceID, FKFDate, FKFPeriod,

FAuxPropID,FSecQty,FSupplyID)

SELECT '0', FItemID, FBatchNo,FMtoNo, FStockID, SUM(FBegQty), SUM(FBegBal), FSPID, FKFDate, FKFPeriod,

FAuxPropID, SUM(FAuxUnitBegQty) ,FSupplyID FROM ICInvInitial

WHERE FPeriod = @Period

GROUP BY FStockID, FItemID, FBatchNo,FMtoNo, FSPID, FKFDate, FKFPeriod, FAuxPropID,FSupplyID --处理初始化余额表

UPDATE ICInvInitial SET FPeriod = 0 UPDATE t1 SET t1.FBillInterID=t2.FBillInterID from ICInvInitial t1

INNER join #ICInvBalTemp t2 on t1.FStockID=t2.FStockID and t1.FSPID=t2.FSPID and t1.FItemID=t2.FItemID and t1.FBatchNo=t2.FBatchNo and t1.FMtoNo=t2.FMtoNo and t1.FKFDate=t2.FKFDate AND t1.FKFPeriod =t2.FKFPeriod AND t2.FItemID=t1.FItemID AND

t1.FAuxPropID=t2.FAuxPropID AND t1.FStockInDate=t2.FStockInDate AND t1.FBillInterID=t2.FOldBillInterID AND t1.FSupplyID = t2.FSupplyID --从ICInvInitial备份普通仓结算余额数据到ICInvInitialAdj用于做期初余额调整 --VMI仓的余额不会变化

DELETE t1 FROM ICInvInitialAdj t1 INNER JOIN t_Stock t2 ON t1.FStockID = t2.FItemID WHERE t2.FTypeID <> 504

UPDATE ICInvInitialAdj SET FPeriod = 0 INSERT INTO ICInvInitialAdj(FBrNo,FPeriod,FStockID,FItemID,FBatchNo,FBegQty,FReceive,FSend,FYtdReceive, FYtdSend,FEndQty,FBegBal,FDebit,FCredit,FYtdDebit,FYtdCredit,FEndBal,FBegDiff,FReceiveDiff,FSendDiff, FBillInterID,FUnitID,FAuxBegQty,FYtdBegQty,FYtdAuxBegQty,FYtdAuxReceive,FYtdAuxSend,FYtdBegBal,

FYtdBegDiff,FYtdReceiveDiff,FYtdSendDiff,FKFDate,FKFPeriod,FSPID,FSecBegQty,FSecReceive,FSecSend, FSecYtdReceive,FSecYtdSend,FSecEndQty,FSecUnitID,FStockInDate,FAuxPropID,FSecCoefficient, FAuxUnitBegQty,FYtdAuxUnitBegQty,FYtdAuxUnitSend,FYtdAuxUnitReceive,FSNListID,FMTONo,FSupplyID) SELECT

'',0,t1.FStockID,t1.FItemID,t1.FBatchNo,t1.FBegQty,t1.FReceive,t1.FSend,t1.FYtdReceive,t1.FYtdSend, t1.FEndQty,t1.FBegBal,t1.FDebit,t1.FCredit,t1.FYtdDebit,t1.FYtdCredit,t1.FEndBal,t1.FBegDiff, t1.FReceiveDiff,t1.FSendDiff,t1.FBillInterID,t1.FUnitID,t1.FAuxBegQty,t1.FYtdBegQty,t1.FYtdAuxBegQty, t1.FYtdAuxReceive,t1.FYtdAuxSend,t1.FYtdBegBal,t1.FYtdBegDiff,t1.FYtdReceiveDiff,t1.FYtdSendDiff, t1.FKFDate,t1.FKFPeriod,t1.FSPID,t1.FSecBegQty,t1.FSecReceive,t1.FSecSend,t1.FSecYtdReceive, t1.FSecYtdSend,t1.FSecEndQty,t1.FSecUnitID,t1.FStockInDate,t1.FAuxPropID,t1.FSecCoefficient, t1.FAuxUnitBegQty,t1.FYtdAuxUnitBegQty,t1.FYtdAuxUnitSend,t1.FYtdAuxUnitReceive,t1.FSNListID, t1.FMTONo,t1.FSupplyID

FROM ICInvInitial t1 INNER JOIN t_Stock t2 ON t1.FStockID = t2.FItemID WHERE t2.FTypeID <> 504

UPDATE t1 SET FBillInterID=0 FROM ICInvInitialAdj t1 inner join t_icitem t2 on t1.FItemID=t2.FItemID where t2.FTrack not in (78,79,20308) UPDATE t1 SET FBillInterID=0 FROM ICInvInitial t1 inner join t_icitem t2 on t1.FItemID=t2.FItemID where t2.FTrack not in (78,79,20308)

DROP TABLE #ICInvBalTemp DROP TABLE #ICVMIInvBalTemp

UPDATE t_SystemProFile SET FValue = '0' WHERE FCategory = 'IC' AND FKey = 'InvDataPeriod'

UPDATE t_SystemProFile SET FValue = '1' WHERE FCategory = 'IC' AND FKey =

'ICClosed'

--处理启用前的单据

--初始化前的委外加工入库单默认为全部核销BT244631 MYQ V11.0 2007-11-08 UPDATE u1

SET u1.FCheckStatus = 2 FROM ICStockBill v1 INNER JOIN ICStockBillEntry u1 ON u1.FInterID=v1.FInterID WHERE v1.FDate < @CurStartDate AND v1.FTranType=5 UPDATE ICStockBill SET FVchInterID = -1

WHERE FDate < @CurStartDate AND FCancellation = 0

DELETE t1

FROM t_MakeVouchTemp t1

JOIN ICStockBill t2 ON t1.FSCBillInterID = t2.FInterID

WHERE t1.FTranType IN (4000, 5) AND t1.FPreVchInterID = 0 AND t1.FGroup = 0 AND t2.FDate < @CurStartDate AND t2.FTranType IN (1, 5) AND t2.FCancellation = 0

INSERT INTO t_MakeVouchTemp (FGroupID,FVchInterID,FPeriod,FYear,FVchNumber,FTranType,FSCBillInterID,FGroup,FPreVchInterID)

SELECT 0, -1, @Period, @CurYear, -1, CASE FTranType WHEN 1 THEN 4000 ELSE FTranType END, FInterID, 0, 0 FROM ICStockBill

WHERE FDate < @CurStartDate AND FTranType IN (1, 5) And FCancellation = 0

DELETE t1

FROM ICSaleVoucher t1

JOIN ICStockBill t2 ON t1.FInterID = t2.FInterID

WHERE t2.FDate < @CurStartDate AND t2.FTranType = 21 AND t2.FSaleStyle IN (102, 103) AND t2.FCancellation = 0

INSERT INTO ICSaleVoucher (FInterID,FVoucherID,FTypeID) SELECT FInterID, -1, 0 FROM ICStockBill

WHERE FDate < @CurStartDate AND FTranType = 21 AND FSaleStyle IN (102, 103) AND FCancellation = 0

RETURN 1

本文来源：https://www.bwwdw.com/article/3ec.html