oracle-EBS-PL/sql 工单BOM产品批次追溯, 成品回推追溯到原材料 SQL START WITH CONNECT BY prior

oracle-EBS-PL/sql 工单BOM追溯, 成品追溯原材料回推 SQL START WITH CONNECT BY

prior

本文用到的这个 oracle 语句:具体可以网上搜索它的用法

start with connect by prior  --自顶向下,自下向上口诀:
start with child_id = 10 connect by (prior child_id) = parent_id
prior 和 子列在一起,表示寻找它的子孙,即自顶向下,和父列在一起,表示开始寻找她的爸爸,即自下向上。

经常会从一个工单追溯产生它的另一些工单的来源,比如从出库成品追溯它的入库bom原料。我这里列了一些简单的例子,用的是oracle 的语法

这里用到了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

 

 

作者: 轻烟随风
当前文章地址: https://www.zyxpp.com/oracle-ebs-pl-sql-trace/
来源: 轻烟随风的博客
文章版权归作者所有,欢迎转载
THE END
分享
二维码
< <上一篇
下一篇>>
文章目录
关闭
目 录