生产物料清单齐套缺料分析SQL

从以下几个角度考虑：  1. 物料清单；2 即时库存数量; 3.是否启用多组织; 4. 是否启用辅助属性。

思路如下：

①拼接生产用料清单与即时库存关系，这里用了outer apply 直接计算清单中各物料在库存数量。(也可以先做即库子查询,G再与之匹配)

②因为启用了多组织与辅助属性，在即时库存表(T_STK_INVENTORY)需要与物料表关联，转换成库存组织的物料内码

③排除不发料的物料

④做一个开窗函数统计按清单fid升序，累计到该行时所需数量，再用库存量减累计数量，得出剩余数量

⑤用剩余数量是否大于0，就可以判断足料还缺料。足料标记为1， 缺料标记为0

⑥统计清单中明细条数与足料数目，条数与足料数相减，0 为齐套， 大于0 为缺料

SELECT
    FBILLNO, FID, FENTRYID, FMATERIALID, FNUMBER, FNAME, FAUXPROPID, FISSUETYPE,
    FREQUIRE_QTY, FSUPPLY_QTY, FSUPPLYTYPE, FSECQTY, REMAINQTY, ENOUGH,
    CASE WHEN COUNT(FID) OVER ( PARTITION BY FID ) - SUM(ENOUGH) OVER (PARTITION BY FID) = 0 THEN '齐套'
         ELSE '缺料'
    END    AS supplying,   --  统计BOM是否齐套
    COUNT(FID) OVER ( PARTITION BY FID ) AS LINENUMBER,  -- 统计bom子项物料数量
    SUM(ENOUGH) OVER (PARTITION BY FID) AS ENOUGHNUMBER   -- 统计bom足料个数
FROM (
         SELECT
             A.FBILLNO, -- 生产用料清单单号
             A.FID, --  生产用料清单主表内码
             B.FENTRYID,  -- 生产用料清单明细内码
             B.FMATERIALID,  --- 物料内码
             M.FNUMBER,  -- 物料编码
             ML.FNAME, --  物料名称
             B.FAUXPROPID,   -- 物料辅助属性
             C.FISSUETYPE,  -- 领料方式
             round(B.FMUSTQTY, 6) AS FREQUIRE_QTY,  -- bom需求量
             nvl(STK.FBASEQTY, 0) AS FSUPPLY_QTY,  -- 即时库存量
             B.FSUPPLYTYPE,  -- 供应类型
             NVL(STK.FSECQTY, 0) AS FSECQTY,  --  即时库存辅助数量
             nvl(STK.FBASEQTY, 0) - sum(round(B.FNEEDQTY, 6))
                                        OVER (PARTITION BY B.FMATERIALID ,B.FAUXPROPID ORDER BY B.FID
                                            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW )
                 AS REMAINQTY,   --  剩余量
             CASE WHEN nvl(STK.FBASEQTY, 0) - sum(round(B.FNEEDQTY, 6))
                 OVER (PARTITION BY B.FMATERIALID ,B.FAUXPROPID ORDER BY B.FID ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) < 0
                      THEN 0
                  ELSE 1
             END AS ENOUGH  -- 0 分配数量不足，1 足够
         FROM T_PRD_PPBOM A
                  INNER JOIN T_PRD_PPBOMENTRY B ON A.FID = B.FID
                  INNER JOIN T_PRD_PPBOMENTRY_C C ON C.FENTRYID = B.FENTRYID
                  INNER JOIN T_BD_MATERIAL M ON M.FMATERIALID = B.FMATERIALID
                  INNER JOIN T_BD_MATERIAL_L ML ON M.FMATERIALID = ML.FMATERIALID AND ML.FLOCALEID = 2052
                  outer apply (SELECT M2.FMATERIALID, A2.FAUXPROPID, A2.FSTOCKORGID, sum(FBASEQTY) AS FBASEQTY, sum(FSECQTY) AS FSECQTY
                             FROM T_STK_INVENTORY A2
                                      INNER JOIN T_BD_MATERIAL M ON M.FMATERIALID = A2.FMATERIALID
                                      INNER JOIN T_BD_MATERIAL M2 ON M2.FMASTERID = M.FMASTERID AND M2.FUSEORGID = A2.FSTOCKORGID
                                      where a2.FMATERIALID = B.FMATERIALID
                                       AND B.FAUXPROPID = a2.FAUXPROPID
                                       AND a2.FSTOCKORGID = A.FPRDORGID
                             GROUP BY M2.FMATERIALID, A2.FAUXPROPID, A2.FSTOCKORGID) STK
         WHERE  C.FISSUETYPE <> '7'
     ) S
ORDER BY S.FBILLNO, S.FENTRYID