存货收发存汇总表数据库取数脚本sql（存储过程）

此脚本加了‘不统计核算组织内调拨单据’和‘不统计库存调整单据’的过滤条件

@orgid 参数为组织名称(汉字)   @benqibegindate 参数为日期

CREATE PROCEDURE [dbo].[INOUTSTOCKSUMMARY]

@orgid varchar(500),

@benqibegindate date

AS

BEGIN

---取组织id

declare @orgiddd int 

select @orgiddd=forgid from  T_ORG_ORGANIZATIONS_L  where fname = @orgid AND FLOCALEID = 2052 

----取日期年月

declare @fperiod int 

declare @fyear int 

set @fyear = year(@benqibegindate)

set @fperiod = month(@benqibegindate)

-----------------------------------------------------------------存货收发存汇总 单价 begin

---查核算期间内码

If OBJECT_ID(N'tempdb.dbo.#hesuanqijian',N'U') Is Not Null

Drop Table #hesuanqijian

SELECT A.FID ,HSTX.FNAME '会计核算体系名称' , KJZC.FNAMe'会计政策名称'  , HSZZ.FNAME '核算组织名称'  , A.FYEAR '年' , A.FPERIOD '期',A.FID '期间内码',HSTX.FACCTSYSTEMID '会计核算体系内码',KJZC.FACCTPOLICYID '会计政策内码',HSZZ.FORGID '核算组织内码'

into #hesuanqijian

FROM T_HS_OUTACCTG  A  INNER JOIN T_HS_CALDIMENSIONS B ON A.FDIMENSIONID =B.FDIMENSIONID

INNER JOIN T_ORG_ACCOUNTSYSTEM_L HSTX  ON B.FACCTSYSTEMID=HSTX.FACCTSYSTEMID   and  HSTX.FLOCALEID =2052  ---T_ORG_ACCOUNTSYSTEM_L 核算体系名称

INNER JOIN T_FA_ACCTPOLICY_L KJZC  ON B.FACCTPOLICYID=KJZC.FACCTPOLICYID   and  KJZC.FLOCALEID =2052   --T_FA_ACCTPOLICY_L  会计政策名称

INNER JOIN T_ORG_ORGANIZATIONS_L HSZZ  ON B.FFINORGID=HSZZ.FORGID   and  HSZZ.FLOCALEID =2052   ---T_ORG_ORGANIZATIONS_L 核算组织名称

WHERE  A.FYEAR =@fyear AND A.FPERIOD = @fperiod and  HSTX.FNAME ='财务会计核算体系' AND KJZC.FNAME='中国准则会计政策' AND HSZZ.FNAME=@orgid  

---核算期间内码

declare @hs_period_id int 

select @hs_period_id=期间内码 from #hesuanqijian

---步骤1：创建临时表#TM_HS_StockInSummaryData

If OBJECT_ID(N'tempdb.dbo.#TM_HS_StockInSummaryData',N'U') Is Not Null

Drop Table #TM_HS_StockInSummaryData

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, FACCTGID INT NOT NULL DEFAULT 0, FMATERIALBASEID INT NOT NULL DEFAULT 0, FMATERIALID NVARCHAR (255) NULL, FMATERIALNAME NVARCHAR (255) NULL, FMATERIALGROUPID INT 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)

---步骤2：按会计核算体系+核算组织+会计政策，查询物料的维度信息，维度信息包含：货主类型、货主、库存组织、库存状态、仓库、仓位、批号、BOM版本、计划跟踪号、辅助属性、存货类别等。可以理解为【存货收发存汇总表】菜单的显示隐藏列里除了数量单价金额的字段

INSERT INTO #TM_HS_StockInSummaryData with(tablock)  (FDIMID, FACCTGDIMID, FACCTGSYSTEMID, FACCTGORGID, FACCTPOLICYID, FYEAR, FPERIOD, FACCTGID, FMATERIALBASEID, FMATERIALID, FMATERIALNAME, FMATERIALGROUPID, FMODEL, FLOTNO, FASSIPROPERTYID, FMATERPROPERTY, FMATERTYPE, FBOMNO, FPLANNO, FSEQUENCENO, FPROJECTNO, FOWNERID, FSTOCKORGID, FSTOCKSTATUSID, FSTOCKID, FSTOCKPLACEID, FACCTGRANGEID, FUNITID, FVALUATION, FDETAILBILLFORMID, FISTOTAL) SELECT STOCKDIM.FENTRYID fdimid, 0 facctgdimid, 1 facctgsystemid, @orgiddd facctgorgid, 1 facctpolicyid, @fyear, @fperiod, @hs_period_id, STOCKDIM.FMATERIALID fmaterialbaseid, MAT.FNUMBER fmaterialid, MAT_L.FNAME fmaterialname, MAT.FMATERIALGROUP, 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, '0' fvaluatio, 'HS_INOUTSTOCKDETAILRPT', 0 FROM T_HS_InivStockDimension STOCKDIM INNER JOIN T_HS_StockDimension HSDIM ON HSDIM.FENTRYID = STOCKDIM.FDIMEENTRYID INNER JOIN t_bd_Material MAT ON STOCKDIM.FMATERIALID = MAT.FMATERIALID LEFT OUTER JOIN T_BD_LOTMASTER LOT ON STOCKDIM.FLOT = LOT.FLOTID LEFT OUTER JOIN t_BD_Stock STOCK ON STOCK.FSTOCKID = STOCKDIM.FSTOCKID INNER JOIN T_HS_ACCTGRANGE RAG ON HSDIM.FACCTGRANGEID = RAG.FACCTGRANGEID INNER JOIN T_BD_MATERIALBASE MAT_T ON (MAT_T.FMATERIALID = MAT.FMATERIALID AND MAT.FUSEORGID = MAT_T.FUSEORGID) LEFT OUTER JOIN t_bd_Material_l MAT_L ON ((MAT.FMATERIALID = MAT_L.FMATERIALID AND MAT.FUSEORGID = MAT_L.FUSEORGID) AND MAT_L.FLOCALEID = 2052) WHERE ((((RAG.FACCTGSYSTEMID = 1 AND RAG.FACCTGORGID = @orgiddd) AND RAG.FACCTPOLICYID = 1) ) ) 

-------注释：WHERE条件的RAG.FACCTGSYSTEMID为会计核算体系内码，RAG.FACCTGORGID为核算组织内码，RAG.FACCTPOLICYI为会计政策内码，MAT.FNUMBER 为物料编码。其中，【会计核算体系+核算组织+会计政策是必填】，物料编码选填。

UPDATE STATISTICS #TM_HS_StockInSummaryData 

CREATE CLUSTERED INDEX Ic911c83f538e4eccbdd3bb2085d43 ON #TM_HS_StockInSummaryData (FDIMID, FACCTGID)

----步骤3.1：往临时表插入 期初数量（FINITQTY），数据来源是视图V_HS_INIVBALANCE_RPT，存货库存维度余额表从存货启用到本期的所有数据

MERGE INTO #TM_HS_StockInSummaryData T using(SELECT T1.FDIMEENTRYID fdimeentryid, Sum(T1.FQty) finitqty, T1.FID, max(T1.FACCTGDIMEENTRYID) facctgdimeentryid FROM V_HS_INIVBALANCE_RPT T1 WHERE ((T1.FENDINITKEY = '0' AND T1.FID = @hs_period_id) AND T1.FDIMEENTRYID IN (SELECT DISTINCT FDIMID FROM #TM_HS_StockInSummaryData TDIME)) GROUP BY T1.FDIMEENTRYID, T1.FID) RET ON ((T.FDIMID = RET.FDIMEENTRYID AND T.FACCTGID = RET.FID)) WHEN MATCHED  THEN UPDATE SET T.FINITQTY = RET.FINITQTY, T.FACCTGDIMID = RET.FACCTGDIMEENTRYID;

--注释：WHERE条件里T1.FENDINITKEY = '0'为期初， T1.FID为开始期间的内码， T1.FDIMEENTRYID为步骤2的维度信息。【三个条件都是必填】

---步骤3.2：往临时表插入 期初单价（FINITPRICE）和金额（FINITAMOUNT），期初单价=期初数量除以期初数量，数据来源是视图V_HS_INIVBALANCE_RPT里FENDINITKEY=0

MERGE INTO #TM_HS_StockInSummaryData t using(SELECT T1.FDIMEENTRYID fdimeentryid, CAST(Sum(t1.FAMOUNT) AS NUMERIC(28, 11)) finitamount, T1.FID FROM V_HS_INIVBALANCE_RPT T1 WHERE ((T1.FENDINITKEY = '0' AND T1.FID = @hs_period_id) AND T1.FDIMEENTRYID IN (SELECT DISTINCT FDIMID FROM #TM_HS_StockInSummaryData TDIME)) GROUP BY T1.FDIMEENTRYID, T1.FID) RET ON ((T.FDIMID = RET.FDIMEENTRYID AND T.FACCTGID = RET.FID)) 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;

--注释：WHERE条件同3.1

---步骤3.3：往临时表插入 收入数量（FRECEIVEQTY）、发出数量（FSENDQTY），数据来源是视图V_HS_OUTINSTOCKSEQ

MERGE INTO #TM_HS_StockInSummaryData T using(SELECT T1.FDIMEENTRYID 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, max(T1.FACCTGDIMEENTRYID) facctgdimeentryid FROM V_HS_OUTINSTOCKSEQ T1 WHERE (

T1.FACCTGID IN ( @hs_period_id ) AND 

(

(

(

(

--T1.FACCTGDIMEENTRYID IN ( SELECT DISTINCT FDIMID FROM #TM_HS_StockInSummaryData TDIME ) 

T1.FDIMEENTRYID IN ( SELECT DISTINCT FDIMID FROM #TM_HS_StockInSummaryData TDIME )

AND (

T1.FISSETTLE = '1' 

OR ( ( ( T1.FBILLFROMID <> 'STK_TRANSFERIN' ) AND ( T1.FBILLFROMID <> 'STK_TRANSFEROUT' ) ) AND T1.FISGENFORIOS = '0' ) 

) 

) 

AND ( T1.FOUTINSTOCKTYPE <> '12' ) 

) 

AND NOT EXISTS (

SELECT

1 

FROM

T_HS_AdjustmentBill adjTran 

WHERE

(

adjTran.FFROMTYPE = '8' 

AND ( ( adjTran.FACCTGID IN ( @hs_period_id ) AND T1.FBILLFROMID = 'HS_AdjustmentBill' ) AND T1.FBILLID = adjTran.FID ) 

) 

) 

) 

AND T1.FBILLFROMID NOT IN ( 'STK_LOTADJUST', 'STK_StockConvert', 'STK_StatusConvert' ) 

) 

)  GROUP BY T1.FDIMEENTRYID) RET ON (T.FDIMID = RET.FDIMEENTRYID) WHEN MATCHED  THEN UPDATE SET T.FRECEIVEQTY = RET.FRECEIVEQTY, T.FSENDQTY = RET.FSENDQTY, T.FACCTGDIMID = RET.FACCTGDIMEENTRYID;

--注释：WHERE条件里T1.FACCTGID是查询从开始到结束期间的内码，2022-01~2022-05一共5个期间就有5个内码。T1.FDIMEENTRYID为步骤2的维度信息。【2个条件都是必填】

--步骤3.4：往临时表插入 收入单价（FRECEIVEPRICE）、发出单价（FRECEIVEQTY）、收入金额（FRECEIVEAMOUNT）、发出金额（FSENDAMOUNT），数据来源是视图V_HS_OUTINSTOCKSEQ 和 成本调整T_HS_AdjustmentBillEntry

MERGE INTO #TM_HS_StockInSummaryData T using(SELECT CAST(sum(rt2.FRECEIVEAMOUNT) AS NUMERIC(28, 11)) freceiveamount, CAST(sum(rt2.FSENDAMOUNT) AS NUMERIC(28, 11)) fsendamount, rt2.FDIMEENTRYID FROM (SELECT T1.FACCTGID, t1.FDIMEENTRYID fdimeentryid, 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 V_HS_OUTINSTOCKSEQ T1 WHERE (

T1.FACCTGID IN ( @hs_period_id ) AND 

(

(

(

(

--T1.FACCTGDIMEENTRYID IN ( SELECT DISTINCT FDIMID FROM #TM_HS_StockInSummaryData TDIME ) 

T1.FDIMEENTRYID IN ( SELECT DISTINCT FDIMID FROM #TM_HS_StockInSummaryData TDIME )

AND (

T1.FISSETTLE = '1' 

OR ( ( ( T1.FBILLFROMID <> 'STK_TRANSFERIN' ) AND ( T1.FBILLFROMID <> 'STK_TRANSFEROUT' ) ) AND T1.FISGENFORIOS = '0' ) 

) 

) 

AND ( T1.FOUTINSTOCKTYPE <> '12' ) 

) 

AND NOT EXISTS (

SELECT

1 

FROM

T_HS_AdjustmentBill adjTran 

WHERE

(

adjTran.FFROMTYPE = '8' 

AND ( ( adjTran.FACCTGID IN ( @hs_period_id ) AND T1.FBILLFROMID = 'HS_AdjustmentBill' ) AND T1.FBILLID = adjTran.FID ) 

) 

) 

) 

AND T1.FBILLFROMID NOT IN ( 'STK_LOTADJUST', 'STK_StockConvert', 'STK_StatusConvert' ) 

) 

)  UNION ALL SELECT T0.FACCTGID, ADJENTRY.FDIMEENTRYID fdimeentryid, 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 WHERE ((((T0.FACCTGID IN (@hs_period_id) AND (T0.FBUSINESSTYPE = '0' OR T0.FISACCTGGENERATE = '1')) AND T0.FDOCUMENTSTATUS = 'C') AND T0.FFORBIDSTATUS = 'A') AND ADJENTRY.FDIMEENTRYID IN (SELECT DISTINCT FDIMID FROM #TM_HS_StockInSummaryData TDIME))) rt2 GROUP BY FDIMEENTRYID) ret ON (T.FDIMID = RET.FDIMEENTRYID) 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;

--注释：从成本调整单T_HS_AdjustmentBillEntry取数，根据FINOUTINDEX=1时，调整金额（ADJENTRY.FADJUSTMENTAMOUNT）计到收入金额，FINOUTINDEX=0时计到发出金额，WHERE里的T0.FACCTGID 为期间内码，条件都是必填

--步骤3.5：删掉期初、收发、发出的数量金额都为0的，这些不显示

DELETE FROM #TM_HS_StockInSummaryData WHERE (((((ISNULL(FINITQTY, 0) = 0 AND ISNULL(FINITAMOUNT, 0) = 0) AND (FRECEIVEQTY IS NULL)) AND (FRECEIVEAMOUNT IS NULL)) AND (FSENDQTY IS NULL)) AND (FSENDAMOUNT IS NULL))

UPDATE #TM_HS_StockInSummaryData SET FINITQTY = 0 WHERE (FINITQTY IS NULL)

UPDATE #TM_HS_StockInSummaryData SET FINITAMOUNT = 0 WHERE (FINITAMOUNT IS NULL)

UPDATE #TM_HS_StockInSummaryData SET FINITPRICE = 0 WHERE (FINITPRICE IS NULL)

UPDATE #TM_HS_StockInSummaryData SET FRECEIVEQTY = 0 WHERE (FRECEIVEQTY IS NULL)

UPDATE #TM_HS_StockInSummaryData SET FRECEIVEAMOUNT = 0 WHERE (FRECEIVEAMOUNT IS NULL)

UPDATE #TM_HS_StockInSummaryData SET FRECEIVEPRICE = 0 WHERE (FRECEIVEPRICE IS NULL)

UPDATE #TM_HS_StockInSummaryData SET FSENDQTY = 0 WHERE (FSENDQTY IS NULL)

UPDATE #TM_HS_StockInSummaryData SET FSENDAMOUNT = 0 WHERE (FSENDAMOUNT IS NULL)

UPDATE #TM_HS_StockInSummaryData SET FSENDPRICE = 0 WHERE (FSENDPRICE IS NULL)

---步骤3.6：统计结存数量=期初数量+收入数量-发出数量，结存金额=期初金额+收入金额-发出金额，结存单价=结存金额除以结存数量。取数的主要逻辑到此已经完成。

UPDATE #TM_HS_StockInSummaryData SET FENDQTY = ((FINITQTY + FRECEIVEQTY) - FSENDQTY), FENDAMOUNT = ((FINITAMOUNT + FRECEIVEAMOUNT) - FSENDAMOUNT), FENDPRICE = CASE  WHEN ((FINITQTY + FRECEIVEQTY) - FSENDQTY) = 0 THEN 0 ELSE ROUND((CAST(((FINITAMOUNT + FRECEIVEAMOUNT) - FSENDAMOUNT) AS NUMERIC(28, 11)) / ((FINITQTY + FRECEIVEQTY) - FSENDQTY)), 10) END

If OBJECT_ID(N'tempdb.dbo.#hesuandanjia',N'U') Is Not Null

Drop Table #hesuandanjia

select 

FACCTGORGID,FYear,FPeriod,FMATERIALID as FMATERIALFNUMBER,FMATERIALNAME,FMATERIALGROUPID,b.fnumber as stocknumber,

FINITQTY as 期初数量,FINITPRICE as 期初单价,FINITAMOUNT as 期初金额,

FRECEIVEQTY as 收入数量,FRECEIVEPRICE as 收入单价,FRECEIVEAMOUNT as 收入金额, 

FSENDQTY as 发出数量,FSENDPRICE as 发出单价,FSENDAMOUNT as 发出金额,

FENDQTY as 结存数量,FENDPRICE as 结存单价,FENDAMOUNT as 结存金额

into #hesuandanjia

from  #TM_HS_StockInSummaryData a

left  join   T_BD_STOCK  b   on  b.fstockid=a.fstockid

select * from  #hesuandanjia

end