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

金蝶K3供应链初始化存储过程 ; set ANSI_NULLS ONset QUOTED_IDENTIFIER ON goALTER PROCEDURE [dbo].[StockINIDataOn] ASSET NOCOUNT ONDECLARE @pPeriodTmpVARCHAR (255)DECLARE @PeriodINT--当前会计期间DECLARE @PtypeVARCHAR (255)DECLARE @CurYearINT--当前年份DECLARE @CalTypeINT--核算方式DECLARE @CurStartDate DATETIME DECLARE @CurEndDateDATETIMEDECLARE @BillNoVARCHAR (255)--单据号码DECLARE @AuditChoiceINT ---核算方式，--数量核算，--数量、金额核算--电子行业：序列号检查--检查SystemINISNDataOn存储过程的存在，存在那么执行IF EXISTS(SELECT 1 FROM sysobjects WHERE name = 'SystemINISNDataOn') EXEC SystemINISNDataOn--电子行业：序列号检查SET @AuditChoice = 0SELECT @CurYear = CONVERT(INT, FValue) FROM t_SystemProfile WHERE FCategory = 'IC' AND FKey = 'CurrentYear'IF @CurYear = NULL BEGINRAISERROR('~$~系统参数: ?IC--CurrentYear》数据被破坏^|^！',16,1) RETURN 0 ENDEXEC GetSysPrm 'IC', 'CurrentPeriod', @pPeriodTmp OUTPUT, @pType OUTPUTSELECT @Period = CONVERT(INT, @pPeriodTmp)SELECT @CalType = CONVERT(INT, Fvalue) FROM t_SystemProfile WHERE FCategory = 'IC' AND FKey = 'CalculateType'If @CalType = Null BEGINRAISERROR('~$~系统参数: ?IC--CalculateType 数据被破坏^|^！',16,1) RETURN 0ENDIF @CalType = 2 BEGINIF EXISTS (SELECT 1 FROM t_Stock WHERE FTypeID IN (500, 20291, 20293) AND FStockGroupID = 0 ) BEGINRAISERROR('~$~系统检测到在分组核算模式下，还有实仓未分组^|^', 16, 1) RETURN 0 ENDUPDATE t_Stock SET FGroupID = FStockGroupID WHERE FTypeID IN (500, 20291, 20293) ENDIF @CalType = 1 BEGINUPDATE t_Stock SET FGroupID = FItemID WHERE FTypeID IN (500, 20291, 20293) ENDIF @CalType = 0 BEGINUPDATE t_Stock SET FGroupID = 0 WHERE FTypeID IN (500, 20291, 20293) END--当前会计期间的起止日期EXEC GetPeriodStartEnd 0, @Period, @CurStartDate OUTPUT, @CurEndDate OUTPUTIF (SELECT COUNT(*) FROM (SELECT DISTINCT FPeriod FROM ICInvInitial) AS t1) = 1BEGINUPDATE ICInvInitial SET FPeriod = @Period UPDATE ICInvInitialAdj SET FPeriod = @PeriodENDELSE IF (SELECT COUNT(*) FROM (SELECT DISTINCT FPeriod FROM ICInvInitial) AS t1) > 1 BEGINRAISERROR('~$~系统提示: 系统初始化表?ICInvInitial》含有多期数据，不合乎启用条件^|^！', 16, 1) RETURN 0 ENDSELECT @BillNo = FBillNoFROM ICStockBill WHERE (FCheckerID IS NULL OR FCheckerID = 0) AND FTranType = 28 AND FDate IF @@ROWCOUNT > 0 BEGIN SET @BillNo = '~$~系统提示: 委外加工出库单--^|^' + @BillNo + '^|^~$~未审核，不合乎启用条件^|^！'RAISERROR(@BillNo, 16, 1) RETURN 0 ENDSELECT @BillNo = FBillNoFROM ICStockBill WHERE (FCheckerID IS NULL OR FCheckerID = 0) AND FTranType = 5 AND FDate 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 BEGINSELECT @BillNo = B.FBillNoFROM ICStockBill BINNER JOIN ICStockBillEntry E ON B.FInterID = E.FInterID AND B.FTranType = 28 AND E.FAmount = 0 WHERE B.FDate IF @@ROWCOUNT > 0 BEGIN SET @BillNo = '~$~系统提示: 委外加工出库单--^|^' + @BillNo + '^|^~$~中存在金额为的分录，不合乎启用条件^|^！' RAISERROR(@BillNo, 16, 1) RETURN 0 ENDSELECT @BillNo = B.FBillNoFROM ICStockBill BINNER JOIN ICStockBillEntry E ON B.FInterID = E.FInterID AND B.FTranType = 5AND (E.FMaterialCost = 0 OR E.FMaterialCostPrice = 0) WHERE B.FDate IF @@ROWCOUNT > 0 BEGIN SET @BillNo = '~$~系统提示: 委外加工入库单--^|^' + @BillNo + '^|^~$~中存在金额为的分录，不合乎启用条件^|^！' RAISERROR(@BillNo, 16, 1) RETURN 0 END END--系统不允许负库存的时候，不允许出现负数IF EXISTS (SELECT top 1 1 FROM ICInvInitial tiINNER JOIN t_Stock ts ON ti.FStockID = ts.FItemID WHERE FBegQty RAISERROR('~$~系统不允许负库存，但是期初结存已经录入负数，不合乎启用条件^|^！',16,1)RETURN 0 ENDTRUNCATE TABLE ICInvBal TRUNCATE TABLE ICBal TRUNCATE TABLE ICVMIInvBalCREATE TABLE #ICInvBalTemp (FStockIDINT NOT NULLDEFAULT (0),FSPIDINT NOT NULLDEFAULT (0),FItemIDINT NOT NULL,FBatchNoVARCHAR (200) NOT NULL DEFAULT (''), FMtoNo NVARCHAR (50) NOT NULL DEFAULT (''), FBegQtyDECIMAL (28, 10) DEFAULT (0), FReceiveDECIMAL (28, 10) DEFAULT (0), FSendDECIMAL (28, 10) DEFAULT (0), FYtdReceiveDECIMAL (28, 10) DEFAULT (0), FYtdSendDECIMAL (28, 10) DEFAULT (0), FEndQtyDECIMAL (28, 10) DEFAULT (0),FBegBalDECIMAL (20,2) NOT NULL DEFAULT (0),FDebit DECIMAL (20,2) NOT NULL DEFAULT (0), FCreditDECIMAL (20,2) NOT NULL DEFAULT (0), FYtdDebitDECIMAL (20,2) NOT NULL DEFAULT (0), FYtdCredit DECIMAL (20,2) NOT NULL DEFAULT (0), FEndBalDECIMAL (20,2) NOT NULL DEFAULT (0), FBegDiffDECIMAL (20,2) NOT NULL DEFAULT (0), FReceiveDiffDECIMAL (20,2) NOT NULL DEFAULT (0), FSendDiffDECIMAL (20,2) NOT NULL DEFAULT (0), FEndDiffDECIMAL (20,2) NOT NULL DEFAULT (0), FBillInterIDINT IDENTITY(1,1),FOldBillInterIDINT NOT NULL DEFAULT (0),FEntryIDINT NOT NULL DEFAULT (0),FYtdReceiveDiffDECIMAL (20,2) NOT NULL DEFAULT (0),FYtdSendDiffDECIMAL (20,2) NOT NULL DEFAULT (0), FSecBegQty DECIMAL (28, 10) NOT NULL DEFAULT (0), FSecReceiveDECIMAL (28, 10) NOT NULL DEFAULT (0), FSecSendDECIMAL (28, 10) NOT NULL DEFAULT (0), FSecYtdReceive DECIMAL (28, 10) NOT NULL DEFAULT (0), FSecYtdSendDECIMAL (28, 10) NOT NULL DEFAULT (0。