库龄计算中全部入库单sql原创
金蝶云·星空
金蝶云·星空实施
金蝶云社区-程Long
程Long
12人赞赏了该文章 1,593次浏览 未经作者许可，禁止转载编辑于2021年09月02日 10:00:51
summary-icon摘要由AI智能服务提供

这段文本是一个SQL查询语句，主要目的是从多个数据表中联合查询并汇总不同类型的库存入库信息，包括初始库存、采购入库、生产入库、其他入库、简单生产入库、生产线产品入库、受托加工材料入库、组装入库、拆卸入库和销售退货入库等。查询结果包括物料的基本信息（如物料编码、物料名称）、库存数量、入库日期、库龄天数、库存组织以及入库单据类型等，并将这些信息按库龄天数排序后存储到一个新表T_UG_StockAge中。每个类型的入库信息通过不同的表关联查询得到，查询中涉及了物料表、物料本地化表、各类库存单据头和单据体、仓库表以及组织机构表等。通过UNION ALL语句将所有类型的入库记录合并在一起，以确保查询结果包含所有符合条件的入库记录。

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


图标赞 12
12人点赞
还没有人点赞，快来当第一个点赞的人吧！
图标打赏
0人打赏
还没有人打赏，快来当第一个打赏的人吧！

推荐阅读

生产入库领料查询中本期材料套数如何计算的

【概述】 生产入库领料查询的 [本期材料套数]是指按单据日期在表头[入库日期]之间的领料单、退料单、补料单计算出来的材料套数吗,其中,材料套数是按照时间段以及表 …

    知识

    748

直接SQL账表如何默认查询全部

问题描述： 新增直接SQL账表时，默认过滤条件不录入任何信息，直接点击查询，查询出所有的数据 。 操作步骤： 1、 以下例子是根据录入供应商名称查询对应的采购订 …

    文章

    941

库龄计算

（前言：目前该功能处于灰度发布功能） 一、操作步骤： [图片] [图片] [图片] [图片] [图片] [图片] 二、库龄计算与库存账龄分析表的算法主要差异： …

    文章

    3.1万