DECLARE @StartYear INT
SET @StartYear=2021
DECLARE @StartPeriod INT
SET @StartPeriod=9
DECLARE @EndYear INT
SET @EndYear=2021
DECLARE @EndPeriod INT
SET @EndPeriod=9
DECLARE @BeginDate NVARCHAR(100)
SET @BeginDate='2021-09-01 00:00:00'
DECLARE @EndDate NVARCHAR(100)
SET @EndDate='2021-10-01 00:00:00'
DECLARE @AcctSysId BIGINT --核算体系编码
SET @AcctSysId=1
DECLARE @AcctOrgId BIGINT --核算组织编码
SET @AcctOrgId=1
DECLARE @AcctPolicyId BIGINT --会计政策编码
SET @AcctPolicyId=1
DECLARE @DimensionId BIGINT
SET @DimensionId=5 --存货核算计算维度
DECLARE @StartMatCode NVARCHAR(100)
SET @StartMatCode=N'10195'
DECLARE @EndMatCode NVARCHAR(100)
SET @EndMatCode=N'10205'
DECLARE @FirstSkuGroup INT
SET @FirstSkuGroup=0
DECLARE @SECONDSKUGROUP INT
SET @SECONDSKUGROUP=0
DECLARE @THIRDSKUGROUP INT
SET @THIRDSKUGROUP=0
--SELECT * FROM T_HS_CALDIMENSIONS WHERE FACCTSYSTEMID=@AcctSysId AND FFINORGID=@AcctOrgId AND FACCTPOLICYID=@AcctPolicyId
--1.------------------------------------------------------------------------------------------------------------------------------------------------
CREATE TABLE #TM_HS_StockInSummaryData (FDIMID INT NULL, FACCTGDIMID INT NULL, FACCTGSYSTEMID INT NULL, FACCTGSYSTEMNAME NVARCHAR (255) NULL, FACCTGORGID INT NULL, FACCTGORGNAME NVARCHAR (255) NULL, FACCTPOLICYID INT NULL, FACCTPOLICYNAME NVARCHAR (255) NULL, FYear INT NULL, FPeriod INT NULL, FMATERIALBASEID INT NOT NULL DEFAULT 0, FMATERIALID NVARCHAR (255) NULL, FMATERIALNAME NVARCHAR (255) NULL, FMODEL NVARCHAR (510) NULL, FLOTNO VARCHAR (1020) NULL, FASSIPROPERTYID NVARCHAR (510) NULL, FMATERPROPERTY NVARCHAR (255) NULL, FMATERTYPE NVARCHAR (255) NULL, FBOMNO VARCHAR (255) NULL, FPLANNO NVARCHAR (255) NULL, FSEQUENCENO VARCHAR (255) NULL, FPROJECTNO VARCHAR (255) NULL, FOWNERID INT NULL, FOWNERNAME NVARCHAR (255) NULL, FSTOCKORGID INT NULL, FSTOCKORGNAME NVARCHAR (100) NULL, FSTOCKID INT NULL, FSTOCKNAME NVARCHAR (255) NULL, FSTOCKPLACEID INT NULL, FSTOCKPLACENAME NVARCHAR (255) NULL, FACCTGRANGEID NVARCHAR (255) NULL, FACCTGRANGENAME NVARCHAR (255) NULL, FEXPENSEID NVARCHAR (255) NULL, FUNITNAME NVARCHAR (255) NULL, FVALUATION NVARCHAR (255) NULL, FINITQTY DECIMAL (28, 10) NULL, FINITPRICE DECIMAL (28, 10) NULL, FINITAMOUNT DECIMAL (28, 10) NULL, FRECEIVEQTY DECIMAL (28, 10) NULL, FRECEIVEPRICE DECIMAL (28, 10) NULL, FRECEIVEAMOUNT DECIMAL (28, 10) NULL, FEXPENSENAME NVARCHAR (255) NULL, FSENDQTY DECIMAL (28, 10) NULL, FSENDPRICE DECIMAL (28, 10) NULL, FSENDAMOUNT DECIMAL (28, 10) NULL, FENDQTY DECIMAL (28, 10) NULL, FENDPRICE DECIMAL (28, 10) NULL, FENDAMOUNT DECIMAL (28, 10) NULL, FDBENDQTY DECIMAL (28, 10) NULL, FDBENDAMOUNT DECIMAL (28, 10) NULL, FDIFFENDQTY DECIMAL (28, 10) NULL, FDIFFENDAMOUNT DECIMAL (28, 10) NULL, FISTOTAL INT NULL, FASSIPROPNAME NVARCHAR (510) NULL, FSTOCKSTATUSID NVARCHAR (255) NULL, FGroupByField NVARCHAR (255) NULL, FDetailBillFormID NVARCHAR (255) NULL, FUnitID INT NULL
,FirstSkuGroup INT NULL,FSECONDSKUGROUP INT NULL,FTHIRDSKUGROUP INT NULL,FENDAcctPRICE DECIMAL (28, 10) NULL )
CREATE CLUSTERED INDEX Ia2f4b650dd6c4e5b997cd6d8b4f36 ON #TM_HS_StockInSummaryData (FDIMID, FACCTGDIMID, FYEAR, FPERIOD)
--2.插入明细-------------------------------------------------------------------------------------------------------------------------------------------------
INSERT INTO #TM_HS_StockInSummaryData with(tablock)
(FDIMID, FACCTGDIMID, FACCTGSYSTEMID, FACCTGORGID, FACCTPOLICYID, FYear, FPeriod, FMATERIALBASEID, FMATERIALID, FMATERIALNAME, FMODEL, FLOTNO, FASSIPROPERTYID, FMATERPROPERTY, FMATERTYPE, FBOMNO, FPLANNO, FSEQUENCENO, FPROJECTNO, FOWNERID, FSTOCKORGID,FSTOCKSTATUSID, FSTOCKID, FSTOCKPLACEID, FACCTGRANGEID, FUNITID, FVALUATION, FDetailBillFormID, FISTOTAL
,FirstSkuGroup,FSECONDSKUGROUP,FTHIRDSKUGROUP)
SELECT STOCKDIM.FENTRYID FDIMID, HSDIM.FENTRYID FACCTGDIMID, CALD.FACCTSYSTEMID facctgsystemid, CALD.FFINORGID facctgorgid, CALD.FACCTPOLICYID facctpolicyid,@StartYear, @StartPeriod, STOCKDIM.FMATERIALID fmaterialbaseid, MAT.FNUMBER fmaterialid, MAT_L.FNAME fmaterialname, MAT_L.FSPECIFICATION fmodel, LOT.FNUMBER flotno, STOCKDIM.FAUXPROPID fassipropertyid, MAT_T.FERPCLSID fmaterproperty, MAT_T.FCATEGORYID fmatertype, STOCKDIM.FBOMID fbomno, STOCKDIM.FMTONO fplanno, N'' fsequenceno, STOCKDIM.FPROJECTNO fprojectno, STOCKDIM.FCargoOwnerId fownerid, STOCKDIM.FStockOrgId fstockorgid, STOCKDIM.FSTOCKSTATUSID fstockstatusid, STOCKDIM.FSTOCKID fstockid, STOCKDIM.FSTOCKLOCID fstockplaceid, HSDIM.FACCTGRANGEID facctgrangeid, MAT_T.FBASEUNITID funitid, HSDIM.FVALUATIONMETHOD fvaluatio, 'HS_INOUTSTOCKDETAILRPT', 0 ,MAT.FirstSkuGroup,MAT.FSECONDSKUGROUP,MAT.FTHIRDSKUGROUP
FROM T_HS_OUTACCTG AG
INNER JOIN V_HS_INIVBALANCE_RPT IV ON AG.FID = IV.FID
INNER JOIN T_HS_CALDIMENSIONS CALD ON (AG.FDIMENSIONID = CALD.FDIMENSIONID AND AG.FDIMENSIONID = @DimensionId)
INNER JOIN T_HS_InivStockDimension STOCKDIM ON IV.FDimeEntryId = STOCKDIM.FENTRYID
LEFT OUTER JOIN T_BD_LOTMASTER LOT ON STOCKDIM.FLOT = LOT.FLOTID
INNER JOIN T_HS_StockDimension HSDIM ON HSDIM.FENTRYID = IV.FACCTGDIMEENTRYID
INNER JOIN t_bd_Material MAT ON STOCKDIM.FMATERIALID = MAT.FMATERIALID
INNER JOIN T_BD_MATERIALBASE MAT_T ON (MAT.FMATERIALID = MAT_T.FMATERIALID AND MAT.FUSEORGID = MAT_T.FUSEORGID)
LEFT OUTER JOIN t_BD_Stock STOCK ON STOCK.FSTOCKID = STOCKDIM.FSTOCKID
LEFT OUTER JOIN t_bd_Material_l MAT_L ON (MAT.FMATERIALID = MAT_L.FMATERIALID AND MAT_L.FLOCALEID = 2052)
WHERE IV.FENDINITKEY = '0' AND AG.FYEAR = @StartYear AND AG.FPERIOD = @StartPeriod AND CAlD.FACCTSYSTEMID = @AcctSysId AND CAlD.FFINORGID = @AcctOrgId AND CAlD.FACCTPOLICYID = @AcctPolicyId
AND CONVERT(NVARCHAR(4),AG.FYEAR)+right('0'+CONVERT(NVARCHAR(2),AG.FPERIOD),2)>=CONVERT(NVARCHAR(4),@StartYear)+right('0'+CONVERT(NVARCHAR(2),@StartPeriod),2)
AND CONVERT(NVARCHAR(4),AG.FYEAR)+right('0'+CONVERT(NVARCHAR(2),AG.FPERIOD),2)<=CONVERT(NVARCHAR(4),@EndYear)+right('0'+CONVERT(NVARCHAR(2),@EndPeriod),2)
AND (@StartMatCode='' OR MAT.FNUMBER >= @StartMatCode) AND (@EndMatCode='' OR MAT.FNUMBER <= @EndMatCode)
AND (@FirstSkuGroup=0 or @FirstSkuGroup=MAT.FirstSkuGroup) and (@SECONDSKUGROUP=0 or @SECONDSKUGROUP=MAT.FSECONDSKUGROUP) and (@THIRDSKUGROUP=0 or @THIRDSKUGROUP=MAT.FTHIRDSKUGROUP)
-----------------
UNION SELECT STOCKDIM.FENTRYID FDIMID, HSDIM.FENTRYID FACCTGDIMID, CALD.FACCTSYSTEMID facctgsystemid, CALD.FFINORGID facctgorgid, CALD.FACCTPOLICYID facctpolicyid, @StartYear, @StartPeriod, STOCKDIM.FMATERIALID fmaterialbaseid, MAT.FNUMBER fmaterialid, MAT_L.FNAME fmaterialname, MAT_L.FSPECIFICATION fmodel, LOT.FNUMBER flotno, STOCKDIM.FAUXPROPID fassipropertyid, MAT_T.FERPCLSID fmaterproperty, MAT_T.FCATEGORYID fmatertype, STOCKDIM.FBOMID fbomno, STOCKDIM.FMTONO fplanno, N'' fsequenceno, STOCKDIM.FPROJECTNO fprojectno, STOCKDIM.FCargoOwnerId fownerid, STOCKDIM.FStockOrgId fstockorgid, STOCKDIM.FSTOCKSTATUSID fstockstatusid, STOCKDIM.FSTOCKID fstockid, STOCKDIM.FSTOCKLOCID fstockplaceid, HSDIM.FACCTGRANGEID facctgrangeid, MAT_T.FBASEUNITID funitid, HSDIM.FVALUATIONMETHOD fvaluatio, 'HS_INOUTSTOCKDETAILRPT', 0 ,MAT.FirstSkuGroup,MAT.FSECONDSKUGROUP,MAT.FTHIRDSKUGROUP
FROM T_HS_OUTACCTG AG
INNER JOIN T_HS_CALDIMENSIONS CALD ON (AG.FDIMENSIONID = CALD.FDIMENSIONID AND AG.FDIMENSIONID = @DimensionId)
INNER JOIN V_HS_OUTINSTOCKSEQ SEQ ON (AG.FID = SEQ.FACCTGID AND SEQ.FDOCUMENTSTATUS = 'C')
INNER JOIN T_HS_InivStockDimension STOCKDIM ON SEQ.FDIMEENTRYID = STOCKDIM.FENTRYID
LEFT OUTER JOIN T_BD_LOTMASTER LOT ON STOCKDIM.FLOT = LOT.FLOTID
INNER JOIN T_HS_StockDimension HSDIM ON HSDIM.FENTRYID = SEQ.FACCTGDIMEENTRYID
INNER JOIN t_bd_Material MAT ON STOCKDIM.FMATERIALID = MAT.FMATERIALID
INNER JOIN T_BD_MATERIALBASE MAT_T ON (MAT.FMATERIALID = MAT_T.FMATERIALID AND MAT.FUSEORGID = MAT_T.FUSEORGID)
LEFT OUTER JOIN t_BD_Stock STOCK ON STOCK.FSTOCKID = STOCKDIM.FSTOCKID
LEFT OUTER JOIN t_bd_Material_l MAT_L ON ((MAT.FMATERIALID = MAT_L.FMATERIALID AND MAT_L.FLOCALEID = 2052) AND MAT.FUSEORGID = MAT_L.FUSEORGID)
WHERE CAlD.FACCTSYSTEMID = @AcctSysId AND CAlD.FFINORGID = @AcctOrgId AND CAlD.FACCTPOLICYID = @AcctPolicyId
AND CONVERT(NVARCHAR(4),AG.FYEAR)+right('0'+CONVERT(NVARCHAR(2),AG.FPERIOD),2)>=CONVERT(NVARCHAR(4),@StartYear)+right('0'+CONVERT(NVARCHAR(2),@StartPeriod),2)
AND CONVERT(NVARCHAR(4),AG.FYEAR)+right('0'+CONVERT(NVARCHAR(2),AG.FPERIOD),2)<=CONVERT(NVARCHAR(4),@EndYear)+right('0'+CONVERT(NVARCHAR(2),@EndPeriod),2)
AND (@StartMatCode='' OR MAT.FNUMBER >= @StartMatCode) AND (@EndMatCode='' OR MAT.FNUMBER <= @EndMatCode) and (@FirstSkuGroup=0 or @FirstSkuGroup=MAT.FirstSkuGroup) and (@SECONDSKUGROUP=0 or @SECONDSKUGROUP=MAT.FSECONDSKUGROUP) and (@THIRDSKUGROUP=0 or @THIRDSKUGROUP=MAT.FTHIRDSKUGROUP)
--------------
UNION SELECT STOCKDIM.FENTRYID FDIMID, HSDIM.FENTRYID FACCTGDIMID, CALD.FACCTGSYSTEMID facctgsystemid, CALD.FACCTORGID facctgorgid, CALD.FACCTPOLICYID facctpolicyid, @StartYear, @StartPeriod, STOCKDIM.FMATERIALID fmaterialbaseid, MAT.FNUMBER fmaterialid, MAT_L.FNAME fmaterialname, MAT_L.FSPECIFICATION fmodel, LOT.FNUMBER flotno, STOCKDIM.FAUXPROPID fassipropertyid, MAT_T.FERPCLSID fmaterproperty, MAT_T.FCATEGORYID fmatertype, STOCKDIM.FBOMID fbomno, STOCKDIM.FMTONO fplanno, N'' fsequenceno, STOCKDIM.FPROJECTNO fprojectno, STOCKDIM.FCargoOwnerId fownerid, STOCKDIM.FStockOrgId fstockorgid, STOCKDIM.FSTOCKSTATUSID fstockstatusid, STOCKDIM.FSTOCKID fstockid, STOCKDIM.FSTOCKLOCID fstockplaceid, HSDIM.FACCTGRANGEID facctgrangeid, MAT_T.FBASEUNITID funitid, HSDIM.FVALUATIONMETHOD fvaluatio, 'HS_INOUTSTOCKDETAILRPT', 0 ,MAT.FirstSkuGroup,MAT.FSECONDSKUGROUP,MAT.FTHIRDSKUGROUP
FROM T_HS_AdjustmentBill CALD
INNER JOIN T_HS_AdjustmentBillEntry ADJENTRY ON CALD.FID = ADJENTRY.FID
INNER JOIN T_HS_InivStockDimension STOCKDIM ON ADJENTRY.FDIMEENTRYID = STOCKDIM.FENTRYID
LEFT OUTER JOIN T_BD_LOTMASTER LOT ON STOCKDIM.FLOT = LOT.FLOTID
INNER JOIN T_HS_StockDimension HSDIM ON HSDIM.FENTRYID = ADJENTRY.FACCTGDIMEENTRYID
INNER JOIN t_bd_Material MAT ON STOCKDIM.FMATERIALID = MAT.FMATERIALID
INNER JOIN T_BD_MATERIALBASE MAT_T ON (MAT.FMATERIALID = MAT_T.FMATERIALID AND MAT.FUSEORGID = MAT_T.FUSEORGID)
INNER JOIN T_BD_ACCOUNTPERIOD AG ON (CALD.FDATE BETWEEN AG.FPERIODSTARTDATE AND AG.FPERIODENDDATE)
LEFT OUTER JOIN t_BD_Stock STOCK ON STOCK.FSTOCKID = STOCKDIM.FSTOCKID
LEFT OUTER JOIN t_bd_Material_l MAT_L ON ((MAT.FMATERIALID = MAT_L.FMATERIALID AND MAT_L.FLOCALEID = 2052) AND MAT.FUSEORGID = MAT_L.FUSEORGID)
WHERE (CALD.FBUSINESSTYPE = '0' OR CALD.FISACCTGGENERATE = '1') AND CALD.FDOCUMENTSTATUS = 'C' AND CALD.FFORBIDSTATUS = 'A' AND CAlD.FACCTGSYSTEMID = @AcctSysId AND CAlD.FACCTORGID = @AcctOrgId AND CAlD.FACCTPOLICYID = @AcctPolicyId
AND CONVERT(NVARCHAR(4),AG.FYEAR)+right('0'+CONVERT(NVARCHAR(2),AG.FPERIOD),2)>=CONVERT(NVARCHAR(4),@StartYear)+right('0'+CONVERT(NVARCHAR(2),@StartPeriod),2)
AND CONVERT(NVARCHAR(4),AG.FYEAR)+right('0'+CONVERT(NVARCHAR(2),AG.FPERIOD),2)<=CONVERT(NVARCHAR(4),@EndYear)+right('0'+CONVERT(NVARCHAR(2),@EndPeriod),2)
AND (@StartMatCode='' OR MAT.FNUMBER >= @StartMatCode) AND (@EndMatCode='' OR MAT.FNUMBER <= @EndMatCode) and (@FirstSkuGroup=0 or @FirstSkuGroup=MAT.FirstSkuGroup) and (@SECONDSKUGROUP=0 or @SECONDSKUGROUP=MAT.FSECONDSKUGROUP) and (@THIRDSKUGROUP=0 or @THIRDSKUGROUP=MAT.FTHIRDSKUGROUP)
--3.库存维度期末 数量、单价、金额-----------------------------------------------------------------------------------------------------------------------------
BEGIN
--3.1----------------------------------------------------------------------------------------------------------------------------------------------------------------
MERGE INTO #TM_HS_StockInSummaryData T
using(
SELECT DIM.FENTRYID fdimeentryid, T1.FACCTGDIMEENTRYID, Sum(T1.FQty) finitqty, T0.FYEAR, T0.FPERIOD
FROM T_HS_OUTACCTG T0
INNER JOIN V_HS_INIVBALANCE_RPT T1 ON T0.FID = T1.FID
INNER JOIN T_HS_CALDIMENSIONS CALD ON T0.FDIMENSIONID = CALD.FDIMENSIONID
INNER JOIN T_HS_InivStockDimension DIM ON T1.FDIMEENTRYID = DIM.FENTRYID
WHERE T1.FENDINITKEY = '0' AND CALD.FACCTSYSTEMID = @AcctSysId AND CALD.FFINORGID = @AcctOrgId AND CALD.FACCTPOLICYID = @AcctPolicyId AND T0.FYEAR = @StartYear AND T0.FPERIOD = @StartPeriod
GROUP BY DIM.FENTRYID, T1.FACCTGDIMEENTRYID, T0.FYEAR, T0.FPERIOD
) RET ON T.FDIMID = RET.FDimeEntryId AND T.FACCTGDIMID = RET.FACCTGDIMEENTRYID AND T.FYEAR = RET.fyear AND T.FPERIOD = RET.fperiod
WHEN MATCHED THEN
UPDATE SET T.FINITQTY = RET.FINITQTY;
--3.2----------------------------------------------------------------------------------------------------------------------------------------------------------------
MERGE INTO #TM_HS_StockInSummaryData t
using(
SELECT DIM.FENTRYID fdimeentryid, T1.FACCTGDIMEENTRYID, CAST(Sum(t1.FAMOUNT) AS NUMERIC(28, 10)) finitamount, T0.FYEAR, T0.FPERIOD
FROM T_HS_OUTACCTG T0
INNER JOIN V_HS_INIVBALANCE_RPT T1 ON T0.FID = T1.FID
INNER JOIN T_HS_CALDIMENSIONS CALD ON T0.FDIMENSIONID = CALD.FDIMENSIONID
INNER JOIN T_HS_InivStockDimension DIM ON T1.FDIMEENTRYID = DIM.FENTRYID
WHERE T1.FENDINITKEY = '0' AND CALD.FACCTSYSTEMID = @AcctSysId AND CALD.FFINORGID = @AcctOrgId AND CALD.FACCTPOLICYID = @AcctPolicyId AND T0.FYEAR = @StartYear AND T0.FPERIOD = @StartPeriod
GROUP BY DIM.FENTRYID, T1.FACCTGDIMEENTRYID, T0.FYEAR, T0.FPERIOD
) RET ON T.FDIMID = RET.FDimeEntryId AND T.FACCTGDIMID = RET.FACCTGDIMEENTRYID AND T.FYEAR = RET.fyear AND T.FPERIOD = RET.fperiod
WHEN MATCHED THEN
UPDATE SET T.FINITPRICE = CASE T.FINITQTY WHEN 0 THEN 0 ELSE ROUND((RET.FINITAMOUNT / T.FINITQTY), 10) END, T.FINITAMOUNT = RET.FINITAMOUNT;
--3.3-----------------------------------------------------------------------------------------------------------------------------------------------------
MERGE INTO #TM_HS_StockInSummaryData T
using(
SELECT T1.FACCTGDIMEENTRYID, DIM.FENTRYID fdimeentryid, Sum(CASE WHEN T1.FINOUTINDEX = '1' THEN T1.FQTY ELSE 0 END) freceiveqty, Sum(CASE WHEN T1.FINOUTINDEX = '0' THEN T1.FQTY ELSE 0 END) fsendqty
FROM T_HS_OUTACCTG T0
INNER JOIN T_HS_CALDIMENSIONS CALD ON T0.FDIMENSIONID = CALD.FDIMENSIONID
INNER JOIN V_HS_OUTINSTOCKSEQ T1 ON T0.FID = T1.FACCTGID AND T1.FDOCUMENTSTATUS = 'C'
INNER JOIN T_HS_InivStockDimension DIM ON T1.FDIMEENTRYID = DIM.FENTRYID
WHERE FACCTSYSTEMID = @AcctSysId AND FFINORGID = @AcctOrgId AND FACCTPOLICYID = @AcctPolicyId
AND CONVERT(NVARCHAR(4),T0.FYEAR)+right('0'+CONVERT(NVARCHAR(2),T0.FPERIOD),2)>=CONVERT(NVARCHAR(4),@StartYear)+right('0'+CONVERT(NVARCHAR(2),@StartPeriod),2)
AND CONVERT(NVARCHAR(4),T0.FYEAR)+right('0'+CONVERT(NVARCHAR(2),T0.FPERIOD),2)<=CONVERT(NVARCHAR(4),@EndYear)+right('0'+CONVERT(NVARCHAR(2),@EndPeriod),2)
GROUP BY T1.FACCTGDIMEENTRYID, DIM.FENTRYID
) RET ON T.FDIMID = RET.FDimeEntryId AND T.FACCTGDIMID = RET.FACCTGDIMEENTRYID
WHEN MATCHED THEN
UPDATE SET T.FRECEIVEQTY = RET.FRECEIVEQTY, T.FSENDQTY = RET.FSENDQTY;
--3.4---------------------------------------------------------------------------------------------------------------------------------------------------------
MERGE INTO #TM_HS_StockInSummaryData T
using(
SELECT CAST(sum(rt2.FRECEIVEAMOUNT) AS NUMERIC(28, 10)) freceiveamount, CAST(sum(rt2.FSENDAMOUNT) AS NUMERIC(28, 10)) fsendamount, FACCTGDIMEENTRYID, FDIMEENTRYID
FROM (
SELECT t0.fyear, t0.fperiod, DIM.FENTRYID fdimeentryid, t1.FACCTGDIMEENTRYID, CASE WHEN
(t1.FInOutIndex = '1' AND (t1.FINDEXENTRYID <> 32)) THEN t1.FAcctgAmount ELSE 0 END freceiveamount, CASE WHEN t1.FInOutIndex = '0' THEN t1.FAcctgAmount WHEN t1.FINDEXENTRYID = 32 THEN (0 - t1.FAcctgAmount) ELSE 0 END fsendamount
FROM T_HS_OUTACCTG t0
INNER JOIN T_HS_CALDIMENSIONS cald ON t0.FDIMENSIONID = cald.FDIMENSIONID
INNER JOIN V_HS_OUTINSTOCKSEQ t1 ON t0.FID = t1.FACCTGID AND t1.FDocumentStatus = 'C'
INNER JOIN T_HS_InivStockDimension DIM ON T1.FDIMEENTRYID = DIM.FENTRYID
WHERE cald.FACCTSYSTEMID = @AcctSysId AND cald.FFINORGID = @AcctOrgId AND cald.FACCTPOLICYID = @AcctPolicyId
AND CONVERT(NVARCHAR(4),T0.FYEAR)+right('0'+CONVERT(NVARCHAR(2),T0.FPERIOD),2)>=CONVERT(NVARCHAR(4),@StartYear)+right('0'+CONVERT(NVARCHAR(2),@StartPeriod),2)
AND CONVERT(NVARCHAR(4),T0.FYEAR)+right('0'+CONVERT(NVARCHAR(2),T0.FPERIOD),2)<=CONVERT(NVARCHAR(4),@EndYear)+right('0'+CONVERT(NVARCHAR(2),@EndPeriod),2)
UNION ALL
SELECT OUTACCT.FYEAR, OUTACCT.FPERIOD, DIM.FENTRYID fdimeentryid, ADJENTRY.FACCTGDIMEENTRYID, CASE WHEN T0.FINOUTINDEX = '1' THEN ADJENTRY.FADJUSTMENTAMOUNT ELSE 0 END freceiveamount, CASE WHEN T0.FINOUTINDEX = '1' THEN 0 ELSE ADJENTRY.FADJUSTMENTAMOUNT END fsendamount
FROM T_HS_AdjustmentBill T0
INNER JOIN T_HS_AdjustmentBillEntry ADJENTRY ON T0.FID = ADJENTRY.FID
INNER JOIN T_HS_OUTACCTG OUTACCT ON OUTACCT.FID = T0.FACCTGID
INNER JOIN T_HS_InivStockDimension DIM ON ADJENTRY.FDIMEENTRYID = DIM.FENTRYID
WHERE T0.FACCTGSYSTEMID = @AcctSysId AND T0.FACCTORGID = @AcctOrgId AND T0.FACCTPOLICYID = @AcctPolicyId AND (T0.FBUSINESSTYPE = '0' OR T0.FISACCTGGENERATE = '1') AND T0.FDATE >= @BeginDate AND T0.FDATE < @EndDate AND T0.FDOCUMENTSTATUS = 'C' AND T0.FFORBIDSTATUS = 'A'
) rt2
GROUP BY FACCTGDIMEENTRYID, FDIMEENTRYID
) ret ON T.FDIMID = RET.FDIMEENTRYID AND T.FACCTGDIMID = RET.FACCTGDIMEENTRYID
WHEN MATCHED THEN
UPDATE SET t.FRECEIVEPRICE = CASE t.FRECEIVEQTY WHEN 0 THEN 0 ELSE ROUND((ret.FRECEIVEAMOUNT / t.FRECEIVEQTY), 10) END, t.FSENDPRICE = CASE t.FSENDQTY WHEN 0 THEN 0 ELSE ROUND((ret.FSENDAMOUNT / t.FSENDQTY), 10) END, t.FRECEIVEAMOUNT = ret.FRECEIVEAMOUNT, t.FSENDAMOUNT = ret.FSENDAMOUNT;
--3.5-------------------------------------------------------------------------------------------------------------------------------------------------------
UPDATE #TM_HS_StockInSummaryData
SET FENDQTY = ((ISNULL(FINITQTY, 0) + ISNULL(FRECEIVEQTY, 0)) - ISNULL(FSENDQTY, 0))
, FENDAMOUNT = ((ISNULL(FINITAMOUNT, 0) + ISNULL(FRECEIVEAMOUNT, 0)) - ISNULL(FSENDAMOUNT, 0))
, FENDPRICE = CASE WHEN ((ISNULL(FINITQTY, 0) + ISNULL(FRECEIVEQTY, 0)) - ISNULL(FSENDQTY, 0)) = 0 THEN 0 ELSE ROUND((((ISNULL(FINITAMOUNT, 0) + ISNULL(FRECEIVEAMOUNT, 0)) - ISNULL(FSENDAMOUNT, 0)) / ((ISNULL(FINITQTY, 0) + ISNULL(FRECEIVEQTY, 0)) - ISNULL(FSENDQTY, 0))), 10) END
END
--.汇总依据---------------------------------------------------------------------------------------------------------------------------------------
INSERT INTO #TM_HS_StockInSummaryData with(tablock)
(FMATERIALID, FMATERIALNAME, FMODEL, FUNITID, FMATERTYPE, FMATERPROPERTY, FMATERIALBASEID, FDIMID, FISTOTAL, FINITQTY, FINITPRICE, FINITAMOUNT, FRECEIVEQTY, FRECEIVEPRICE, FRECEIVEAMOUNT, FSENDQTY, FSENDPRICE, FSENDAMOUNT, FENDQTY, FENDPRICE, FENDAMOUNT)
SELECT FMATERIALID, Min(FMATERIALNAME) fmaterialname, Min(FMODEL) fmodel, min(FUNITID) funitid, min(FMATERTYPE) fmatertype, min(FMATERPROPERTY) fmaterproperty, min(FMATERIALBASEID) fmaterialbaseid, (max(FDIMID) + 1) fdimid, 3 fistotal, sum(FINITQTY) finitqty, CASE WHEN sum(FINITQTY) = 0 THEN 0 ELSE ROUND((CAST(sum(FINITAMOUNT) AS NUMERIC(28, 10)) / sum(FINITQTY)), 10) END finitprice, sum(FINITAMOUNT) finitamount, sum(FRECEIVEQTY) freceiveqty, CASE WHEN sum(FRECEIVEQTY) = 0 THEN 0 ELSE ROUND((CAST(sum(FRECEIVEAMOUNT) AS NUMERIC(28, 10)) / sum(FRECEIVEQTY)), 10) END freceiveprice, sum(FRECEIVEAMOUNT) freceiveamount, sum(FSENDQTY) fsendqty, CASE WHEN sum(FSENDQTY) = 0 THEN 0 ELSE ROUND((CAST(sum(FSENDAMOUNT) AS NUMERIC(28, 10)) / sum(FSENDQTY)), 10) END fsendprice, sum(FSENDAMOUNT) fsendamount, sum(FENDQTY) fendqty, CASE WHEN sum(FENDQTY) = 0 THEN 0 ELSE ROUND((CAST(sum(FENDAMOUNT) AS NUMERIC(28, 10)) / sum(FENDQTY)), 10) END fendprice, sum(FENDAMOUNT) fendamount
FROM #TM_HS_StockInSummaryData t0
GROUP BY FMATERIALID
--.-------------------------------------------------------------------------------------------------------------------------------------------------
DELETE FROM #TM_HS_StockInSummaryData
WHERE (((((ISNULL(FINITQTY, 0) = 0 AND ISNULL(FINITAmount, 0) = 0) AND ISNULL(FRECEIVEQTY, 0) = 0) AND ISNULL(FRECEIVEamount, 0) = 0) AND ISNULL(FSENDQTY, 0) = 0) AND ISNULL(FSENDamount, 0) = 0)
--.长度超出255省略号显示--------------------------------------------------------------------------------------------------------------------------------------
MERGE INTO #TM_HS_StockInSummaryData t0
using(
SELECT T.FID,
CASE
WHEN ISNULL(T.FShowName, N' ') = N' ' THEN CONVERT(VARCHAR(8000), N'')
ELSE LEFT(T.FShowName, (LEN(T.FShowName) - 1))
END fshowname
FROM
(
SELECT
t0.FID fid,
(
CASE
WHEN ISNULL(st01_L.FNAME, N' ') = ' ' THEN CONVERT(VARCHAR(8000), N'')
ELSE CONVERT(NVARCHAR(255), st01_L.FNAME) + CONVERT(VARCHAR(8000), ';') END
+ CASE
WHEN ISNULL(st02_L.FNAME, N' ') = ' ' THEN CONVERT(VARCHAR(8000), N'')
ELSE CONVERT(NVARCHAR(255), st02_L.FNAME) + CONVERT(VARCHAR(8000), ';') END
+ CASE
WHEN ISNULL(st03_L.FNAME, N' ') = ' ' THEN CONVERT(VARCHAR(8000), N'')
ELSE CONVERT(NVARCHAR(255), st03_L.FNAME) + CONVERT(VARCHAR(8000), ';') END
+ CASE
WHEN ISNULL(st04_L.FNAME, N' ') = ' ' THEN CONVERT(VARCHAR(8000), N'')
ELSE CONVERT(NVARCHAR(255), st04_L.FNAME) + CONVERT(VARCHAR(8000), ';') END
+ CASE
WHEN ISNULL(t0.FOPCODE, N' ') = ' ' THEN CONVERT(VARCHAR(8000), N'')
ELSE CONVERT(NVARCHAR(255), t0.FOPCODE) + CONVERT(VARCHAR(8000), ';') END
+ CONVERT(VARCHAR(8000), N'')
) fshowname
FROM T_BAS_FLEXVALUESDETAIL t0
LEFT OUTER JOIN T_BAS_FLEXVALUESENTRY_L st01_L ON t0.FF100004 = st01_L.FEntryID AND st01_L.FLocaleId = 2052
LEFT OUTER JOIN T_BAS_FLEXVALUESENTRY_L st02_L ON t0.FF100016 = st02_L.FEntryID AND st02_L.FLocaleId = 2052
LEFT OUTER JOIN T_BAS_FLEXVALUESENTRY_L st03_L ON t0.FF100191 = st03_L.FEntryID AND st03_L.FLocaleId = 2052
LEFT OUTER JOIN T_BAS_FLEXVALUESENTRY_L st04_L ON t0.FF100192 = st04_L.FEntryID AND st04_L.FLocaleId = 2052
) t
) t1 ON (t1.FID = t0.FSTOCKPLACEID)
WHEN MATCHED THEN
UPDATE SET t0.FSTOCKPLACENAME = CASE
WHEN (LEN(t1.FShowName) >= 255) THEN (LEFT(t1.FShowName, 252) + '...')
ELSE t1.FShowName END;
--结尾----------------------------------------------------------------------------------------------------------------------------------------------------------------------
--.-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SELECT T.FMATERIALBASEID, T.FMATERIALID, T.FMATERIALNAME, GROUPL.FNAME fmaterialgroup, T.FMODEL, T.FLOTNO, T.FASSIPROPERTYID, ENUML.FCAPTION fmaterproperty, MT.FNAME fmatertype, BOM.FNUMBER fbomno, T.FPLANNO, T.FSEQUENCENO, T.FPROJECTNO, T.FOWNERID, OWN.FNAME fownername, T.FSTOCKORGID, SORG.FNAME fstockorgname, ORG.FNUMBER fstockorgCODE,
T.FSTOCKID, STOCK.FNAME fstockname, T.FSTOCKPLACEID, T.FSTOCKPLACENAME, RANG.FNUMBER facctgrangeid, RANGL.FNAME facctgrangename, UNITL.FNAME funitname, T.FUnitID, T.FINITQTY, T.FINITPRICE, T.FINITAMOUNT, T.FRECEIVEQTY, T.FRECEIVEPRICE, T.FRECEIVEAMOUNT, T.FSENDQTY, T.FSENDPRICE, T.FSENDAMOUNT, T.FENDQTY,
T.FENDPRICE, T.FENDAMOUNT, t.FSTOCKSTATUSID, sts.FNAME fstockstatusname, T.FDIMID, T.FACCTGDIMID, T.FASSIPROPNAME, 2 fdigits, 6 fpricedigits, unit.FPRECISION fqtydigits, ENUML1.FCAPTION fvaluation, T.FIsTotal, T.FGroupByFIeld, T.FDetailBillFormId fdetailreportformid ,T.FirstSkuGroup,T.FSECONDSKUGROUP,T.FTHIRDSKUGROUP,T.FENDAcctPRICE, ROW_NUMBER() OVER( ORDER BY T.FMATERIALID ASC, T.FDIMID ASC, T.FACCTGDIMID ASC, T.FISTOTAL DESC) fidentityid
INTO #TMPCA1DD1DF490B11EC930670B5E85
FROM #TM_HS_StockInSummaryData T
LEFT OUTER JOIN T_BD_STOCKSTATUS_L sts ON (t.FSTOCKSTATUSID = sts.FSTOCKSTATUSID AND sts.FLOCALEID = 2052)
LEFT OUTER JOIN T_BD_UNIT unit ON unit.funitid = t.FUnitID
LEFT OUTER JOIN T_HS_ACCTGRANGE RANG ON T.FACCTGRANGEID = RANG.FACCTGRANGEID
LEFT OUTER JOIN T_ORG_ORGANIZATIONS ORG ON T.FSTOCKORGID = ORG.FORGID
LEFT OUTER JOIN T_ORG_ORGANIZATIONS_L SORG ON (T.FSTOCKORGID = SORG.FORGID AND SORG.FLOCALEID = 2052)
LEFT OUTER JOIN T_ORG_ORGANIZATIONS_L OWN ON (T.FOWNERID = OWN.FORGID AND OWN.FLOCALEID = 2052)
LEFT OUTER JOIN t_BD_Stock_L STOCK ON (T.FSTOCKID = STOCK.FSTOCKID AND STOCK.FLOCALEID = 2052)
LEFT OUTER JOIN T_HS_ACCTGRANGE_L RANGL ON (T.FACCTGRANGEID = RANGL.FACCTGRANGEID AND RANGL.FLOCALEID = 2052)
LEFT OUTER JOIN T_BD_UNIT_L UNITL ON (T.FUNITID = UNITL.FUNITID AND UNITL.FLOCALEID = 2052)
LEFT OUTER JOIN T_BD_MATERIALCATEGORY_L MT ON (T.FMATERTYPE = MT.FCATEGORYID AND MT.FLOCALEID = 2052)
LEFT OUTER JOIN t_eng_bom BOM ON BOM.FID = T.FBOMNO
LEFT OUTER JOIN T_META_FORMENUMITEM ENUM ON (ENUM.FID = 'ac14913e-bd72-416d-a50b-2c7432bbff63' AND ENUM.FVALUE = T.FMATERPROPERTY)
LEFT OUTER JOIN T_META_FORMENUMITEM_L ENUML ON (ENUM.FENUMID = ENUML.FENUMID AND ENUML.FLOCALEID = 2052)
LEFT OUTER JOIN T_META_FORMENUMITEM ENUM1 ON (ENUM1.FID = 'eca675f6-d296-4ba9-b9df-170b7b286a73' AND ENUM1.FValue = t.FVALUATION)
LEFT OUTER JOIN T_META_FORMENUMITEM_L ENUML1 ON (ENUM1.FENUMID = ENUML1.FENUMID AND ENUML1.FLOCALEID = 2052)
LEFT OUTER JOIN t_bd_Material MAT ON T.FMATERIALBASEID = MAT.FMATERIALID
LEFT OUTER JOIN T_BD_MATERIALGROUP_L GROUPL ON (MAT.FMATERIALGROUP = GROUPL.FID AND GROUPL.FLOCALEID = 2052)
--.------------------------------------------------------------------------------------------------------------------------------------------------------------------------
UPDATE #TMPCA1DD1DF490B11EC930670B5E85
SET FQtyDigits = (SELECT max(unit.FPRECISION) FROM #TM_HS_StockInSummaryData t LEFT OUTER JOIN T_BD_UNIT unit ON unit.funitid = t.FUnitID WHERE t.FISTOTAL = 0), FMATERIALID = (FMATERIALID + N'-总计') WHERE FISTOTAL = 3;
IF EXISTS (SELECT 1 FROM (SELECT NAME AS TABLE_NAME, XTYPE AS TABLE_XTYPE FROM sysobjects WHERE XTYPE = 'U' OR XTYPE = 'V') AS KSQL_USERTABLES WHERE TABLE_NAME = UPPER('#TMPCA1DD1DF490B11EC930670B5E85'))
BEGIN
CREATE INDEX IDX_TMPCA1DD1DF49 ON #TMPCA1DD1DF490B11EC930670B5E85 (FIDENTITYID)
END
--SELECT SUM(FINITQTY) finitqty, SUM(FINITAMOUNT) finitamount, SUM(FRECEIVEQTY) freceiveqty, SUM(FRECEIVEAMOUNT) freceiveamount, SUM(FSENDQTY) fsendqty, SUM(FSENDAMOUNT) fsendamount, SUM(FENDQTY) fendqty, SUM(FENDAMOUNT) fendamount, CASE Sum(FINITQTY) WHEN 0 THEN 0 ELSE ROUND((CAST(Sum(FINITAMOUNT) AS NUMERIC(28, 10)) / Sum(FINITQTY)), 10) END finitprice, CASE Sum(FRECEIVEQTY) WHEN 0 THEN 0 ELSE ROUND((CAST(Sum(FRECEIVEAMOUNT) AS NUMERIC(28, 10)) / Sum(FRECEIVEQTY)), 10) END freceiveprice, CASE Sum(FSENDQTY) WHEN 0 THEN 0 ELSE ROUND((CAST(Sum(FSENDAMOUNT) AS NUMERIC(28, 10)) / Sum(FSENDQTY)), 10) END fsendprice, CASE Sum(FENDQTY) WHEN 0 THEN 0 ELSE ROUND((CAST(Sum(FENDAMOUNT) AS NUMERIC(28, 10)) / Sum(FENDQTY)), 10) END fendprice FROM #TMPCA1DD1DF490B11EC930670B5E85 WHERE FISTOTAL = 0
SELECT * FROM #TMPCA1DD1DF490B11EC930670B5E85
DROP TABLE #TM_HS_StockInSummaryData
DROP TABLE #TMPCA1DD1DF490B11EC930670B5E85
-----------来自UGreen Jarry and Damomlau
推荐阅读