存货收发存汇总sql（库存维度）

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