oracle-EBS-PL/sql 工单BOM产品领料批次追溯, 成品如何倒推追溯到原材料/底层物料 SQL START WITH CONNECT BY prior

经常会从一个工单追溯产生它的前一个工序步骤的工单的来源，比如从出库成品追溯它的入库bom原料。

比如一个组装一个桌子， 桌子的四条桌腿可能分辨是 A B C D 四个工单产生，桌面是 E 工单产生 。 成品桌子是 F工单完成的，那我们想推出 F 是由哪几个工单产生，领了哪些原料，就需要通过F去倒推 ABCDE的领料情况。

这里用到了oracle 数据库 START WITH ，CONNECT BY  这样的语法，很强大，类似递归，查询匹配的数据。

下图，生产产品经过了 磨粉，配粉，包装 依次3个阶段，最终产生成品： E产出。 可以理解为经过多个工单，得到了 E产出。 我们通过E产品，推导E前面用了哪几张工单？

通过这种思路，你可以从一张成品工单，不停的向前追溯，追溯它所用过的多个中间件wip工单、起始工单 甚至是采购单。

注意下方箭头的 负数比如 -10，就是左边工单领用的产品数量。

效果图如下，从图中可以看出， E 是由C和D 两个工单产生的，C 和 D 又是 A,B 两个工单产生的 ，图中只列举产出物的情况。

测试的sql 如下：

建立表：

create table LSG_TEST_EC01(BATCH_NUM varchar2(50),ITEM varchar2(50),LOT varchar2(50),QTY number,TYPE varchar2(50),CAT varchar2(50))

插入测试用例：

INSERT INTO LSG_TEST_EC01( BATCH_NUM,ITEM,LOT,QTY,TYPE,CAT) VALUES('A','A投料','A01','-10','2-磨粉','IN' );
INSERT INTO LSG_TEST_EC01( BATCH_NUM,ITEM,LOT,QTY,TYPE,CAT) VALUES('A','A产出','A_out','10','2-磨粉','OUT' );

INSERT INTO LSG_TEST_EC01( BATCH_NUM,ITEM,LOT,QTY,TYPE,CAT) VALUES('B','B投料','B01','-20','2-磨粉','IN' );
INSERT INTO LSG_TEST_EC01( BATCH_NUM,ITEM,LOT,QTY,TYPE,CAT) VALUES('B','B产出','B_out','20','2-磨粉','OUT' );

INSERT INTO LSG_TEST_EC01( BATCH_NUM,ITEM,LOT,QTY,TYPE,CAT) VALUES('C','A产出','A_out','-3','2-配粉','IN' );
INSERT INTO LSG_TEST_EC01( BATCH_NUM,ITEM,LOT,QTY,TYPE,CAT) VALUES('C','B产出','B_out','-4','2-配粉','IN' );
INSERT INTO LSG_TEST_EC01( BATCH_NUM,ITEM,LOT,QTY,TYPE,CAT) VALUES('C','C产出','C_out','7','2-配粉','OUT' );

INSERT INTO LSG_TEST_EC01( BATCH_NUM,ITEM,LOT,QTY,TYPE,CAT) VALUES('D','A产出','A_out','-7','2-配粉','IN' );
INSERT INTO LSG_TEST_EC01( BATCH_NUM,ITEM,LOT,QTY,TYPE,CAT) VALUES('D','B产出','B_out','-16','2-配粉','IN' );
INSERT INTO LSG_TEST_EC01( BATCH_NUM,ITEM,LOT,QTY,TYPE,CAT) VALUES('D','C产出','D_out','23','2-配粉','OUT' );

INSERT INTO LSG_TEST_EC01( BATCH_NUM,ITEM,LOT,QTY,TYPE,CAT) VALUES('E','C产出','C_out','-7','2-包装','IN' );
INSERT INTO LSG_TEST_EC01( BATCH_NUM,ITEM,LOT,QTY,TYPE,CAT) VALUES('E','C产出','D_out','-23','2-包装','IN' );
INSERT INTO LSG_TEST_EC01( BATCH_NUM,ITEM,LOT,QTY,TYPE,CAT) VALUES('E','E产出','E_out','30','2-包装','OUT' );

实现的PL/sql:

 WITH a AS (
    SELECT
        a.batch_num,
        a.item,
        a.lot,
        a.qty,
        a.type,
        a.cat,
        b.batch_num   batch_num_f,
        b.qty         qty_f,
        b.type        type_f
    FROM
        lsg_test_ec01   a,
        lsg_test_ec01   b
    WHERE
        a.cat = 'OUT'
        AND a.item = b.item
        AND a.lot = b.lot
        AND a.type <> b.type
    UNION ALL
    SELECT a.batch_num,
        a.item,
        a.lot,
        a.qty,
        a.type,
        a.CAT ，null,
        NULL,
        NULL
    FROM
        lsg_test_ec01 a
    WHERE
        a.type = '2-包装'
        AND a.cat = 'OUT'
)  
SELECT
    CAST(lpad(' ', level * 2 - 1, ' ')
         || batch_num AS VARCHAR(200)) batch_num,
          item,
    lot,
    qty,
    type,
    cat,
    batch_num_f,
    level,
    CAST(sys_connect_by_path(batch_num, '/') AS VARCHAR(100)) path
FROM
    a
START WITH
    batch_num = 'E'
CONNECT BY
    PRIOR batch_num = batch_num_f
ORDER SIBLINGS BY
    batch_num_f

或者如下代码：

WITH a AS (
    SELECT
        a.batch_num,
         a.item,
        a.lot,
        a.qty,
        a.type,
        a.cat,
        b.batch_num   batch_num_f,
        b.qty         qty_f,
        b.type        type_f
    FROM
        lsg_test_ec01   a,
        lsg_test_ec01   b
    WHERE
        a.cat = 'OUT'
        AND a.item = b.item
            AND a.lot = b.lot
                AND a.type <> b.type
    UNION ALL
    SELECT
        a.batch_num,
                a.item,
        a.lot,
        a.qty,
        a.type,
        a.cat ，null,
        NULL,
        NULL
    FROM
        lsg_test_ec01 a
    WHERE
        a.type = '2-包装'
        AND a.cat = 'OUT'
)
SELECT
    CAST(lpad(' ', level * 2 - 1, ' ')
         || batch_num AS VARCHAR(20)) batch_num,
             item,
    lot,
    qty,
    type,
    cat,
    batch_num_f,
    level,
    CASE
        WHEN PRIOR batch_num = batch_num_f THEN
            0
        ELSE
            1
    END flag,
    CAST(sys_connect_by_path(batch_num, '/') AS VARCHAR(100)) path
FROM
    a
START WITH
    batch_num = 'E'
CONNECT BY NOCYCLE PRIOR batch_num = batch_num_f
                   OR batch_num = PRIOR batch_num_f

sql server 上的语法请参考这篇文章：

SQL Server CTE 递归查询