库龄计算中全部入库单sql

select  row_number() over(order by 库龄天数) as id,* into  T_UG_StockAge from (

--初始库存

select  B1.FBILLNO as 单据编号,D1.FNAME as 仓库, B2.FQTY AS 库存数量, A1.FNUMBER AS 物料编码,A2.FNAME AS 物料名称, CONVERT(varchar(100),B1.FDATE, 23) as 入库日期, DATEDIFF(day, B1.FDATE, '2019-12-31') AS 库龄天数,C1.FNAME AS 库存组织,'初始库存' as '入库单据' from T_BD_MATERIAL A1 --物料

inner join T_BD_MATERIAL_L A2 on A1.FMATERIALID=A2.FMATERIALID  AND FLOCALEID='2052' --物料_L表

inner join  T_STK_INVINITDETAIL B2 on A1.FMATERIALID=B2.FMATERIALID --初始库存单据体

inner join T_STK_INVINIT B1 on B2.FID=B1.FID  --初始库存单据头

inner join T_ORG_ORGANIZATIONS_L C1 on C1.FORGID=B1.FStockOrgId AND C1.FLOCALEID='2052'  --组织机构_l

inner join T_ORG_ORGANIZATIONS C2 on  C1.FORGID=C2.FORGID --组织机构

inner join T_BD_STOCK_L D1 on D1.FStockID=B2.FStockID --仓库

    where   B1.FDATE<='2019-12-31' union all -- C2.FNUMBER in('SU')

--采购入库

   select B1.FBILLNO as 单据编号,D1.FNAME as 仓库, B2.FREALQTY AS 库存数量, A1.FNUMBER AS 物料编码,A2.FNAME AS 物料名称, CONVERT(varchar(100),B1.FDATE, 23) as 入库日期, DATEDIFF(day, B1.FDATE, '2019-12-31') AS 库龄天数,C1.FNAME AS 库存组织,'采购入库单' as '入库单据' from T_BD_MATERIAL A1 --物料

inner join T_BD_MATERIAL_L A2 on A1.FMATERIALID=A2.FMATERIALID  AND FLOCALEID='2052' --物料_L表

inner join  T_STK_INSTOCKENTRY B2 on A1.FMATERIALID=B2.FMATERIALID --采购入库单据体

inner join t_STK_InStock B1 on B2.FID=B1.FID  --采购入库单据头

inner join T_ORG_ORGANIZATIONS_L C1 on C1.FORGID=B1.FStockOrgId  AND C1.FLOCALEID='2052' --组织机构_l

inner join T_ORG_ORGANIZATIONS C2 on  C1.FORGID=C2.FORGID --组织机构

inner join T_BD_STOCK_L D1 on D1.FStockID=B2.FStockID --仓库

inner join t_STK_InStockFin E1 ON E1.FID=B2.FID 

    where  B1.FDATE<='2019-12-31' and A1.FNUMBER='10106' and FISGENFORIOS = '0'  union all

--生产入库

select  B1.FBILLNO as 单据编号,D1.FNAME as 仓库, B2.FREALQTY AS 库存数量, A1.FNUMBER AS 物料编码,A2.FNAME AS 物料名称, CONVERT(varchar(100),B1.FDATE, 23) as 入库日期, DATEDIFF(day, B1.FDATE, '2019-12-31') AS 库龄天数,C1.FNAME AS 库存组织,'生产入库' as '入库单据' from T_BD_MATERIAL A1 --物料

inner join T_BD_MATERIAL_L A2 on A1.FMATERIALID=A2.FMATERIALID  AND FLOCALEID='2052' --物料_L表

inner join  T_PRD_INSTOCKENTRY B2 on A1.FMATERIALID=B2.FMATERIALID --采购入库单据体

inner join T_PRD_INSTOCK B1 on B2.FID=B1.FID  --采购入库单据头

inner join T_ORG_ORGANIZATIONS_L C1 on C1.FORGID=B1.FStockOrgId  AND C1.FLOCALEID='2052' --组织机构_l

inner join T_ORG_ORGANIZATIONS C2 on  C1.FORGID=C2.FORGID --组织机构

inner join T_BD_STOCK_L D1 on D1.FStockID=B2.FStockID --仓库

    where   B1.FDATE<='2019-12-31'  union all

--其他入库

select B1.FBILLNO as 单据编号,D1.FNAME as 仓库,B2.FQTY AS 库存数量, A1.FNUMBER AS 物料编码,A2.FNAME AS 物料名称, CONVERT(varchar(100),B1.FDATE, 23) as 入库日期, DATEDIFF(day, B1.FDATE, '2019-12-31') AS 库龄天数,C1.FNAME AS 库存组织,'其他入库' as '入库单据' from T_BD_MATERIAL A1 --物料

inner join T_BD_MATERIAL_L A2 on A1.FMATERIALID=A2.FMATERIALID  AND FLOCALEID='2052' --物料_L表

inner join  T_STK_MISCELLANEOUSENTRY B2 on A1.FMATERIALID=B2.FMATERIALID --其他入库单据体

inner join T_STK_MISCELLANEOUS B1 on B2.FID=B1.FID  --其他入库单据头

inner join T_ORG_ORGANIZATIONS_L C1 on C1.FORGID=B1.FStockOrgId  AND C1.FLOCALEID='2052' --组织机构_l

inner join T_ORG_ORGANIZATIONS C2 on  C1.FORGID=C2.FORGID --组织机构

inner join T_BD_STOCK_L D1 on D1.FStockID=B2.FStockID --仓库

    where  B1.FDATE<='2019-12-31' union all

--简单生产入库

select B1.FBILLNO as 单据编号,D1.FNAME as 仓库,B2.FREALQTY AS 库存数量, A1.FNUMBER AS 物料编码,A2.FNAME AS 物料名称, CONVERT(varchar(100),B1.FDATE, 23) as 入库日期, DATEDIFF(day, B1.FDATE, '2019-12-31') AS 库龄天数,C1.FNAME AS 库存组织,'简单生产入库' as '入库单据' from T_BD_MATERIAL A1 --物料

inner join T_BD_MATERIAL_L A2 on A1.FMATERIALID=A2.FMATERIALID  AND FLOCALEID='2052' --物料_L表

inner join  T_SP_INSTOCKENTRY B2 on A1.FMATERIALID=B2.FMATERIALID --其他入库单据体

inner join T_SP_INSTOCK B1 on B2.FID=B1.FID  --其他入库单据头

inner join T_ORG_ORGANIZATIONS_L C1 on C1.FORGID=B1.FStockOrgId  AND C1.FLOCALEID='2052' --组织机构_l

inner join T_ORG_ORGANIZATIONS C2 on  C1.FORGID=C2.FORGID --组织机构

inner join T_BD_STOCK_L D1 on D1.FStockID=B2.FStockID --仓库

    where  B1.FDATE<='2019-12-31' union all

--生产线产品入库

select B1.FBILLNO as 单据编号,D1.FNAME as 仓库,B2.FREALQTY AS 库存数量, A1.FNUMBER AS 物料编码,A2.FNAME AS 物料名称, CONVERT(varchar(100),B1.FDATE, 23) as 入库日期, DATEDIFF(day, B1.FDATE, '2019-12-31') AS 库龄天数,C1.FNAME AS 库存组织,'生产线产品入库' as '入库单据' from T_BD_MATERIAL A1 --物料

inner join T_BD_MATERIAL_L A2 on A1.FMATERIALID=A2.FMATERIALID  AND FLOCALEID='2052' --物料_L表

inner join  T_REM_INSTOCKENTRY B2 on A1.FMATERIALID=B2.FMATERIALID --其他入库单据体

inner join T_REM_INSTOCK B1 on B2.FID=B1.FID  --其他入库单据头

inner join T_ORG_ORGANIZATIONS_L C1 on C1.FORGID=B1.FStockOrgId  AND C1.FLOCALEID='2052' --组织机构_l

inner join T_ORG_ORGANIZATIONS C2 on  C1.FORGID=C2.FORGID --组织机构

inner join T_BD_STOCK_L D1 on D1.FStockID=B2.FStockID --仓库

    where B1.FDATE<='2019-12-31' union all

--受托加工材料入库单

select B1.FBILLNO as 单据编号,D1.FNAME as 仓库,B2.FQTY AS 库存数量, A1.FNUMBER AS 物料编码,A2.FNAME AS 物料名称, CONVERT(varchar(100),B1.FDATE, 23) as 入库日期, DATEDIFF(day, B1.FDATE, '2019-12-31') AS 库龄天数,C1.FNAME AS 库存组织,'受托加工材料入库单' as '入库单据' from T_BD_MATERIAL A1 --物料

inner join T_BD_MATERIAL_L A2 on A1.FMATERIALID=A2.FMATERIALID  AND FLOCALEID='2052' --物料_L表

inner join  T_STK_OEMINSTOCKENTRY B2 on A1.FMATERIALID=B2.FMATERIALID --其他入库单据体

inner join T_STK_OEMINSTOCK B1 on B2.FID=B1.FID  --其他入库单据头

inner join T_ORG_ORGANIZATIONS_L C1 on C1.FORGID=B1.FStockOrgId  AND C1.FLOCALEID='2052' --组织机构_l

inner join T_ORG_ORGANIZATIONS C2 on  C1.FORGID=C2.FORGID --组织机构

inner join T_BD_STOCK_L D1 on D1.FStockID=B2.FStockID --仓库

    where  B1.FDATE<='2019-12-31' union all

--组装入库

select B1.FBILLNO as 单据编号,D1.FNAME as 仓库, B2.FQty AS 库存数量, A1.FNUMBER AS 物料编码,A2.FNAME AS 物料名称, CONVERT(varchar(100),B1.FDATE, 23) as 入库日期, DATEDIFF(day, B1.FDATE, '2019-12-31') AS 库龄天数,C1.FNAME AS 库存组织,'组装入库' as '入库单据' from T_BD_MATERIAL A1 --物料

                        inner join T_BD_MATERIAL_L A2 on A1.FMATERIALID=A2.FMATERIALID  AND FLOCALEID='2052' --物料_L表

                        inner join  T_STK_ASSEMBLYPRODUCT B2 on A1.FMATERIALID=B2.FMATERIALID --组装入库单据体

                        inner join T_STK_ASSEMBLY B1 on B2.FID=B1.FID  --组装入库单据头

                        inner join T_ORG_ORGANIZATIONS_L C1 on C1.FORGID=B1.FStockOrgId  AND C1.FLOCALEID='2052' --组织机构_l

                        inner join T_ORG_ORGANIZATIONS C2 on  C1.FORGID=C2.FORGID --组织机构

inner join T_BD_STOCK_L D1 on D1.FStockID=B2.FStockID --仓库

                        where  B1. FAffairType='ASSEMBLY'  and B1.FDATE<='2019-12-31' union all 

--拆卸入库

select  B1.FBILLNO as 单据编号,D1.FNAME as 仓库, B2.FQTY AS 库存数量, A1.FNUMBER AS 物料编码,A2.FNAME AS 物料名称, CONVERT(varchar(100),B1.FDATE, 23) as 入库日期, DATEDIFF(day, B1.FDATE, '2019-12-31') AS 库龄天数,C1.FNAME AS 库存组织,'拆卸入库' as '入库单据' from T_BD_MATERIAL A1 --物料

                        inner join T_BD_MATERIAL_L A2 on A1.FMATERIALID=A2.FMATERIALID  AND FLOCALEID='2052' --物料_L表

                        inner join  T_STK_ASSEMBLYSUBITEM B2 on A1.FMATERIALID=B2.FMATERIALID --拆卸入库单据体

                        inner JOIN  T_STK_ASSEMBLYPRODUCT B2_2 ON B2_2.FENTRYID=b2.FENTRYID

                        inner join T_STK_ASSEMBLY B1 on B2_2.FID=B1.FID  --拆卸入库单据头

                        inner join T_ORG_ORGANIZATIONS_L C1 on C1.FORGID=B1.FStockOrgId  AND C1.FLOCALEID='2052' --组织机构_l

                        inner join T_ORG_ORGANIZATIONS C2 on  C1.FORGID=C2.FORGID --组织机构

inner join T_BD_STOCK_L D1 on D1.FStockID=B2.FStockID --仓库

                        where  B1. FAffairType='DASSEMBLY'  and B1.FDATE<='2019-12-31' union all

--销售退货入库

select B1.FBILLNO as 单据编号,D1.FNAME as 仓库, B2.FREALQTY AS 库存数量, A1.FNUMBER AS 物料编码,A2.FNAME AS 物料名称, CONVERT(varchar(100),B1.FDATE, 23) as 入库日期, DATEDIFF(day, B1.FDATE, '2019-12-31') AS 库龄天数,C1.FNAME AS 库存组织,'销售退货入库' as '入库单据' from T_BD_MATERIAL A1 --物料

                        inner join T_BD_MATERIAL_L A2 on A1.FMATERIALID=A2.FMATERIALID  AND FLOCALEID='2052' --物料_L表

                        inner join  T_SAL_RETURNSTOCKENTRY B2 on A1.FMATERIALID=B2.FMATERIALID --销售退货入库单据体

                        inner join T_SAL_RETURNSTOCK B1 on B2.FID=B1.FID  --销售退货入库单据头

                        inner join T_ORG_ORGANIZATIONS_L C1 on C1.FORGID=B1.FStockOrgId   AND C1.FLOCALEID='2052'--组织机构_l

                        inner join T_ORG_ORGANIZATIONS C2 on  C1.FORGID=C2.FORGID --组织机构

inner join T_BD_STOCK_L D1 on D1.FStockID=B2.FStockID --仓库

                        where B1.FDATE<='2019-12-31' union all

--生产退料单

select B1.FBILLNO as 单据编号,D1.FNAME as 仓库, B2.FQTY AS 库存数量, A1.FNUMBER AS 物料编码,A2.FNAME AS 物料名称, CONVERT(varchar(100),B1.FDATE, 23) as 入库日期, DATEDIFF(day, B1.FDATE, '2019-12-31') AS 库龄天数,C1.FNAME AS 库存组织,'生产退料单' as '入库单据' from T_BD_MATERIAL A1 --物料

                        inner join T_BD_MATERIAL_L A2 on A1.FMATERIALID=A2.FMATERIALID  AND FLOCALEID='2052' --物料_L表

                        inner join  T_PRD_RETURNMTRLENTRY B2 on A1.FMATERIALID=B2.FMATERIALID --销售退货入库单据体

                        inner join T_PRD_RETURNMTRL B1 on B2.FID=B1.FID  --销售退货入库单据头

                        inner join T_ORG_ORGANIZATIONS_L C1 on C1.FORGID=B1.FStockOrgId   AND C1.FLOCALEID='2052'--组织机构_l

                        inner join T_ORG_ORGANIZATIONS C2 on  C1.FORGID=C2.FORGID --组织机构

inner join T_BD_STOCK_L D1 on D1.FStockID=B2.FStockID --仓库

                        where  B1.FDATE<='2019-12-31' union all

--生产线退料单

select B1.FBILLNO as 单据编号, D1.FNAME as 仓库, B2.FQTY AS 库存数量, A1.FNUMBER AS 物料编码,A2.FNAME AS 物料名称, CONVERT(varchar(100),B1.FDATE, 23) as 入库日期, DATEDIFF(day, B1.FDATE, '2019-12-31') AS 库龄天数,C1.FNAME AS 库存组织,'生产线退料单' as '入库单据' from T_BD_MATERIAL A1 --物料

                        inner join T_BD_MATERIAL_L A2 on A1.FMATERIALID=A2.FMATERIALID  AND FLOCALEID='2052' --物料_L表

                        inner join  T_REM_RETURNMTRLENTRY B2 on A1.FMATERIALID=B2.FMATERIALID --销售退货入库单据体

                        inner join T_REM_RETURNMTRL B1 on B2.FID=B1.FID  --销售退货入库单据头

                        inner join T_ORG_ORGANIZATIONS_L C1 on C1.FORGID=B1.FStockOrgId   AND C1.FLOCALEID='2052'--组织机构_l

                        inner join T_ORG_ORGANIZATIONS C2 on  C1.FORGID=C2.FORGID --组织机构

inner join T_BD_STOCK_L D1 on D1.FStockID=B2.FStockID --仓库

                        where   B1.FDATE<='2019-12-31' union all

--简单生产退料单

select B1.FBILLNO as 单据编号,D1.FNAME as 仓库, B2.FQTY AS 库存数量, A1.FNUMBER AS 物料编码,A2.FNAME AS 物料名称, CONVERT(varchar(100),B1.FDATE, 23) as 入库日期, DATEDIFF(day, B1.FDATE, '2019-12-31') AS 库龄天数,C1.FNAME AS 库存组织,'简单生产退料单' as '入库单据' from T_BD_MATERIAL A1 --物料

                        inner join T_BD_MATERIAL_L A2 on A1.FMATERIALID=A2.FMATERIALID  AND FLOCALEID='2052' --物料_L表

                        inner join  T_SP_RETURNMTRLENTRY B2 on A1.FMATERIALID=B2.FMATERIALID --销售退货入库单据体

                        inner join T_SP_RETURNMTRL B1 on B2.FID=B1.FID  --销售退货入库单据头

                        inner join T_ORG_ORGANIZATIONS_L C1 on C1.FORGID=B1.FStockOrgId   AND C1.FLOCALEID='2052'--组织机构_l

                        inner join T_ORG_ORGANIZATIONS C2 on  C1.FORGID=C2.FORGID --组织机构

inner join T_BD_STOCK_L D1 on D1.FStockID=B2.FStockID --仓库

                        where  B1.FDATE<='2019-12-31' union all

--委外退料单

select B1.FBILLNO as 单据编号,D1.FNAME as 仓库, B2.FQTY AS 库存数量, A1.FNUMBER AS 物料编码,A2.FNAME AS 物料名称, CONVERT(varchar(100),B1.FDATE, 23) as 入库日期, DATEDIFF(day, B1.FDATE, '2019-12-31') AS 库龄天数,C1.FNAME AS 库存组织,'委外退料单' as '入库单据' from T_BD_MATERIAL A1 --物料

                        inner join T_BD_MATERIAL_L A2 on A1.FMATERIALID=A2.FMATERIALID  AND FLOCALEID='2052' --物料_L表

                        inner join  T_SUB_RETURNMTRLENTRY B2 on A1.FMATERIALID=B2.FMATERIALID --销售退货入库单据体

                        inner join T_SUB_RETURNMTRL B1 on B2.FID=B1.FID  --销售退货入库单据头

                        inner join T_ORG_ORGANIZATIONS_L C1 on C1.FORGID=B1.FStockOrgId   AND C1.FLOCALEID='2052'--组织机构_l

                        inner join T_ORG_ORGANIZATIONS C2 on  C1.FORGID=C2.FORGID --组织机构

inner join T_BD_STOCK_L D1 on D1.FStockID=B2.FStockID --仓库

                        where  B1.FDATE<='2019-12-31' union all

--生产线仓库调拨单

select B1.FBILLNO as 单据编号,D1.FNAME as 仓库, B2.FQTY AS 库存数量, A1.FNUMBER AS 物料编码,A2.FNAME AS 物料名称, CONVERT(varchar(100),B1.FDATE, 23) as 入库日期, DATEDIFF(day, B1.FDATE, '2019-12-31') AS 库龄天数,C1.FNAME AS 库存组织,'生产线仓库调拨单' as '入库单据' from T_BD_MATERIAL A1 --物料

                        inner join T_BD_MATERIAL_L A2 on A1.FMATERIALID=A2.FMATERIALID  AND FLOCALEID='2052' --物料_L表

                        inner join  T_REM_STKTRANSFERINENTRY B2 on A1.FMATERIALID=B2.FMATERIALID --销售退货入库单据体

                        inner join T_REM_STKTRANSFERIN B1 on B2.FID=B1.FID  --销售退货入库单据头

                        inner join T_ORG_ORGANIZATIONS_L C1 on C1.FORGID=B1.FStockOrgId   AND C1.FLOCALEID='2052'--组织机构_l

                        inner join T_ORG_ORGANIZATIONS C2 on  C1.FORGID=C2.FORGID --组织机构

inner join T_BD_STOCK_L D1 on D1.FStockID=B2.FDestStockId --仓库

                        where B1.FDATE<='2019-12-31' union all

--批号调整单

select B1.FBILLNO as 单据编号,D1.FNAME as 仓库, B2.FQTY AS 库存数量, A1.FNUMBER AS 物料编码,A2.FNAME AS 物料名称, CONVERT(varchar(100),B1.FDATE, 23) as 入库日期, DATEDIFF(day, B1.FDATE, '2019-12-31') AS 库龄天数,C1.FNAME AS 库存组织,'批号调整单' as '入库单据' from T_BD_MATERIAL A1 --物料

                        inner join T_BD_MATERIAL_L A2 on A1.FMATERIALID=A2.FMATERIALID  AND FLOCALEID='2052' --物料_L表

                        inner join  T_STK_LOTADJUSTENTRY B2 on A1.FMATERIALID=B2.FMATERIALID --销售退货入库单据体

                        inner join T_STK_LOTADJUST B1 on B2.FID=B1.FID  --销售退货入库单据头

                        inner join T_ORG_ORGANIZATIONS_L C1 on C1.FORGID=B1.FStockOrgId   AND C1.FLOCALEID='2052'--组织机构_l

                        inner join T_ORG_ORGANIZATIONS C2 on  C1.FORGID=C2.FORGID --组织机构

inner join T_BD_STOCK_L D1 on D1.FStockID=B2.FStockID --仓库

                        where   B1.FDATE<='2019-12-31' union all

--库存状态转换

select B1.FBILLNO as 单据编号,D1.FNAME, B2.FCONVERTQTY AS 库存数量, A1.FNUMBER AS 物料编码,A2.FNAME AS 物料名称, CONVERT(varchar(100),B1.FDATE, 23) as 入库日期, DATEDIFF(day, B1.FDATE, '2019-12-31') AS 库龄天数,C1.FNAME AS 库存组织,'库存状态转换' as '入库单据' from T_BD_MATERIAL A1 --物料

                        inner join T_BD_MATERIAL_L A2 on A1.FMATERIALID=A2.FMATERIALID  AND FLOCALEID='2052' --物料_L表

                        inner join  T_STK_STOCKCONVERTENTRY B2 on A1.FMATERIALID=B2.FMATERIALID --销售退货入库单据体

                        inner join T_STK_STOCKCONVERT B1 on B2.FID=B1.FID  --销售退货入库单据头

                        inner join T_ORG_ORGANIZATIONS_L C1 on C1.FORGID=B1.FStockOrgId   AND C1.FLOCALEID='2052'--组织机构_l

                        inner join T_ORG_ORGANIZATIONS C2 on  C1.FORGID=C2.FORGID --组织机构

inner join T_BD_STOCK_L D1 on D1.FStockID=B2.FStockID --仓库

                        where  B1.FDATE<='2019-12-31' union all

--形态转换单

select B1.FBILLNO as 单据编号,D1.FNAME as 仓库, B2.FCONVERTQTY AS 库存数量, A1.FNUMBER AS 物料编码,A2.FNAME AS 物料名称, CONVERT(varchar(100),B1.FDATE, 23) as 入库日期, DATEDIFF(day, B1.FDATE, '2019-12-31') AS 库龄天数,C1.FNAME AS 库存组织,'形态转换单' as '入库单据' from T_BD_MATERIAL A1 --物料

                        inner join T_BD_MATERIAL_L A2 on A1.FMATERIALID=A2.FMATERIALID  AND FLOCALEID='2052' --物料_L表

                        inner join  T_STK_STATUSCONVERTENTRY B2 on A1.FMATERIALID=B2.FMATERIALID --销售退货入库单据体

                        inner join T_STK_STATUSCONVERT B1 on B2.FID=B1.FID  --销售退货入库单据头

                        inner join T_ORG_ORGANIZATIONS_L C1 on C1.FORGID=B1.FStockOrgId   AND C1.FLOCALEID='2052'--组织机构_l

                        inner join T_ORG_ORGANIZATIONS C2 on  C1.FORGID=C2.FORGID --组织机构

inner join T_BD_STOCK_L D1 on D1.FStockID=B2.FStockID --仓库

                        where   B1.FDATE<='2019-12-31' union all

--盘盈入库

select  B1.FBILLNO as 单据编号,D1.FNAME as 仓库, B2.FGainQty AS 库存数量, A1.FNUMBER AS 物料编码,A2.FNAME AS 物料名称, CONVERT(varchar(100),B1.FDATE, 23) as 入库日期, DATEDIFF(day, B1.FDATE, '2019-12-31') AS 库龄天数,C1.FNAME AS 库存组织,'盘盈入库' as '入库单据' from T_BD_MATERIAL A1 --物料

inner join T_BD_MATERIAL_L A2 on A1.FMATERIALID=A2.FMATERIALID  AND FLOCALEID='2052' --物料_L表

inner join  T_STK_STKCOUNTGAINENTRY B2 on A1.FMATERIALID=B2.FMATERIALID --盘盈入库单据体

inner join T_STK_STKCOUNTGAIN B1 on B2.FID=B1.FID  --盘盈入库单据头

inner join T_ORG_ORGANIZATIONS_L C1 on C1.FORGID=B1.FStockOrgId   AND C1.FLOCALEID='2052'--组织机构_l

inner join T_ORG_ORGANIZATIONS C2 on  C1.FORGID=C2.FORGID --组织机构

inner join T_BD_STOCK_L D1 on D1.FStockID=B2.FStockID --仓库

    where  B1.FDATE<='2019-12-31' 

) as d