sql oracle recursive lesson 在庫引当 引当鮮度日特定

まえがき

以前、在庫引当の動きを再帰で漸化的に顕在化することに取り組んでみましたが、今回は伝票ごとに引き当てた在庫の鮮度日が分かるように改良してみました。途中、データ作成用にsqlがいろいろありますが、本記事の目的の実現しているsqlはzaiko_prov.sqlです。検証パターンは前回とだいたい同じです。

参考文献

10-346 再帰with句の再帰項でLeft Join

枝切り(レベル制限)再帰with句での枝切り@枝切り(ノード数の総合計)分析関数の結果を使った枝切り

sql oracle recursive lesson 在庫引当

仕様

前回の仕様と違う点は、売上の引当優先順位を伝票No、伝票行No単位に処理するようにした点です。
伝票Noは顧客ごとに付番され、伝票行Noはその顧客が注文した商品数分、付番しています。

データ作成

伝票行Noに対して複数の鮮度日の在庫が割り当たるように引当指示数prov_inst_qtyと在庫数stock_qtyをいい感じに調節します。検証パターンごとにテストする際も、この2つの値を調節しています。

初期データの作成

create_init_data.sql

DROP TABLE item_mst;
CREATE TABLE item_mst(item CONSTRAINT item_mst_pk PRIMARY KEY ,item_kbn,price,weight,VOLUME,case_irisu,bowl_irisu) AS 
SELECT DISTINCT
    lpad(dbms_random.STRING('X',TRUNC(dbms_random.VALUE * 3) + 2),6,'p_') AS item
    ,' '--dummuyでデフォ値いれとく
    ,10000--dummuyでデフォ値いれとく
    ,10000--dummuyでデフォ値いれとく
    ,10000--dummuyでデフォ値いれとく
    ,100--dummuyでデフォ値いれとく
    ,10--dummuyでデフォ値いれとく
FROM
    dual
CONNECT BY
    LEVEL <= 3
;

MERGE INTO item_mst tar
    USING (
            SELECT
                item
                ,to_char(TRUNC(ABS(dbms_random.VALUE(1,10)),0)) AS item_kbn
                ,TRUNC(ABS(dbms_random.VALUE(100,10000)),0) AS price
                ,TRUNC(ABS(dbms_random.VALUE(1,100)),0) AS weight
                ,TRUNC(ABS(dbms_random.VALUE(1,100)),0) AS VOLUME
                ,TRUNC(ABS(dbms_random.VALUE(1,100)),0) AS case_irisu
                ,TRUNC(ABS(dbms_random.VALUE(1,10)),0) AS bowl_irisu
            FROM item_mst
            ) src
    ON (
        tar.item = src.item
        )
    WHEN MATCHED THEN
    UPDATE
        SET
            tar.item_kbn = src.item_kbn
            ,tar.price = src.price
            ,tar.weight = src.weight
            ,tar.VOLUME = src.VOLUME
            ,tar.case_irisu = src.case_irisu
            ,tar.bowl_irisu = src.bowl_irisu
;

COMMIT;

DELETE FROM item_mst s1
WHERE
    NOT EXISTS(
            WITH tmp_rn AS (
            SELECT
                s2.*
                ,row_number() OVER (ORDER BY s2.item) AS rn
            FROM
                item_mst s2
            )
            SELECT
                1
            FROM
                tmp_rn s3
            WHERE
                s1.item = s3.item
            AND s3.rn <= 3
            )
;

COMMIT;

DROP TABLE cust_mst;
CREATE TABLE cust_mst(cust_id  CONSTRAINT cust_mst_pk PRIMARY KEY ,cust_kbn,basket_no,cart_no) AS 
SELECT DISTINCT
    lpad(dbms_random.STRING('X',TRUNC(dbms_random.VALUE * 3) + 2),6,'c_') AS cust_id
    ,' '--dummuyでデフォ値いれとく
    ,'      '--dummuyでデフォ値いれとく
    ,'        '--dummuyでデフォ値いれとく
FROM
    dual
CONNECT BY
    LEVEL <= 3
;

MERGE INTO cust_mst tar
    USING (
            SELECT
                cust_id
                ,to_char(TRUNC(ABS(dbms_random.VALUE(1,10)),0)) AS cust_kbn
                ,lpad(dbms_random.STRING('X',TRUNC(dbms_random.VALUE * 3) + 2),6,'b_') AS basket_no
                ,lpad(dbms_random.STRING('X',TRUNC(dbms_random.VALUE * 3) + 2),8,'car_') AS cart_no
            FROM cust_mst
            ) src
    ON (
        tar.cust_id = src.cust_id
        )
    WHEN MATCHED THEN
    UPDATE
        SET
            tar.cust_kbn = src.cust_kbn
            ,tar.basket_no = src.basket_no
            ,tar.cart_no = src.cart_no
;

COMMIT;

DELETE FROM cust_mst s1
WHERE
    NOT EXISTS(
            WITH tmp_rn AS (
            SELECT
                s2.*
                ,row_number() OVER (ORDER BY s2.cust_id) AS rn
            FROM
                cust_mst s2
            )
            SELECT
                1
            FROM
                tmp_rn s3
            WHERE
                s1.cust_id = s3.cust_id
            AND s3.rn <= 3
            )
;

COMMIT;


DROP TABLE basket_mst;
CREATE TABLE basket_mst(basket_no  CONSTRAINT basket_mst_pk PRIMARY KEY ,upper_limit_weight,upper_limit_volume) AS 
SELECT DISTINCT
    basket_no
    ,1000
    ,1000
FROM
    cust_mst
;

MERGE INTO basket_mst tar
    USING (
            SELECT
                basket_no
                ,TRUNC(ABS(dbms_random.VALUE(100,1000)),0) AS upper_limit_weight
                ,TRUNC(ABS(dbms_random.VALUE(100,1000)),0) AS upper_limit_volume
            FROM basket_mst
            ) src
    ON (
        tar.basket_no = src.basket_no
        )
    WHEN MATCHED THEN
    UPDATE
        SET
            tar.upper_limit_weight = src.upper_limit_volume
            ,tar.upper_limit_volume = src.upper_limit_volume
;

DELETE FROM basket_mst s1
WHERE
    NOT EXISTS(
            WITH tmp_rn AS (
            SELECT
                s2.*
                ,row_number() OVER (ORDER BY s2.basket_no) AS rn
            FROM
                basket_mst s2
            )
            SELECT
                1
            FROM
                tmp_rn s3
            WHERE
                s1.basket_no = s3.basket_no
            AND s3.rn <= 3
            )
;

COMMIT;

DROP TABLE cart_mst;
CREATE TABLE cart_mst(cart_no  CONSTRAINT cart_mst_pk PRIMARY KEY ,upper_stack_cnt) AS 
SELECT DISTINCT
    cart_no
    ,10
FROM
    cust_mst
;

MERGE INTO cart_mst tar
    USING (
            SELECT
                cart_no
                ,TRUNC(ABS(dbms_random.VALUE(1,20)),0) AS upper_stack_cnt
            FROM
                cart_mst
            ) src
    ON (
        tar.cart_no = src.cart_no
        )
    WHEN MATCHED THEN
    UPDATE
        SET
            tar.upper_stack_cnt = src.upper_stack_cnt
;

DELETE FROM cart_mst s1
WHERE
    NOT EXISTS(
            WITH tmp_rn AS (
            SELECT
                s2.*
                ,row_number() OVER (ORDER BY s2.cart_no) AS rn
            FROM
                cart_mst s2
            )
            SELECT
                1
            FROM
                tmp_rn s3
            WHERE
                s1.cart_no = s3.cart_no
            AND s3.rn <= 3
            )
;

COMMIT;

DROP SEQUENCE sales_seq;
CREATE SEQUENCE sales_seq  START WITH 1 INCREMENT BY 1;

DROP TABLE sales_trn;
CREATE TABLE sales_trn(seq CONSTRAINT sales_trn_pk PRIMARY KEY ,voucher_clz,correct_clz,slip_no,slip_row_no,item,prov_inst_qty,cust_id,arrive_date,org_slip_no,offset_flg,pick_inst_maked_flg,pick_inst_ref_flg) AS
WITH make_date AS (
    SELECT
        to_char(A.date_from + ROWNUM - 1, 'YYYYMMDD') AS std_date
    FROM
        (
            SELECT
                TO_DATE(to_char(sysdate, 'YYYY/MM/DD'), 'YYYY/MM/DD') AS date_from
                , TO_DATE(to_char(sysdate + 1, 'YYYY/MM/DD'), 'YYYY/MM/DD') AS date_to
            FROM
                dual
        ) A
    CONNECT BY
        LEVEL <= A.date_to - A.date_from + 1
)
SELECT
    sales_seq.NEXTVAL AS seq
    ,'k' as voucher_clz
    ,'1' as correct_clz
    ,9999 as slip_no
    ,999 as slip_row_no
    ,m2.item
    ,TRUNC(ABS(dbms_random.VALUE(400,500)),0) AS prov_inst_qty
    ,m1.cust_id
    ,m3.std_date AS arrive_date
    ,9999 as org_slip_no
    ,'0' as offset_flg
    ,'0' as pick_inst_maked_flg
    ,'0' as pick_inst_ref_flg
FROM
    cust_mst m1
        CROSS JOIN item_mst m2
        CROSS JOIN make_date m3
;

INSERT /*+ APPEND */ INTO sales_trn tar--ダイレクトパスインサート
WITH make_date AS (
    SELECT
        to_char(A.date_from + ROWNUM - 1, 'YYYYMMDD') AS std_date
    FROM
        (
            SELECT
                TO_DATE(to_char(sysdate, 'YYYY/MM/DD'), 'YYYY/MM/DD') AS date_from
                , TO_DATE(to_char(sysdate + 1, 'YYYY/MM/DD'), 'YYYY/MM/DD') AS date_to
            FROM
                dual
        ) A
    CONNECT BY
        LEVEL <= A.date_to - A.date_from + 1
)
SELECT
    sales_seq.NEXTVAL AS seq
    ,'k' as voucher_clz
    ,'1' as correct_clz
    ,9999 as slip_no
    ,999 as slip_row_no
    ,m2.item
    ,TRUNC(ABS(dbms_random.VALUE(400,500)),0) AS prov_inst_qty
    ,m1.cust_id
    ,m3.std_date AS arrive_date
    ,9999 as org_slip_no
    ,'0' as offset_flg
    ,'0' as pick_inst_maked_flg
    ,'0' as pick_inst_ref_flg
FROM
    cust_mst m1
        CROSS JOIN item_mst m2
        CROSS JOIN make_date m3
;

COMMIT;

DROP TABLE stock;
CREATE TABLE stock AS
WITH make_date AS (
    SELECT
        to_char(A.date_from + ROWNUM - 1, 'YYYYMMDD') AS std_date
    FROM
        (
            SELECT
                TO_DATE(to_char(sysdate, 'YYYY/MM/DD'), 'YYYY/MM/DD') AS date_from
                , TO_DATE(to_char(sysdate + 1, 'YYYY/MM/DD'), 'YYYY/MM/DD') AS date_to
            FROM
                dual
        ) A
    CONNECT BY
        LEVEL <= A.date_to - A.date_from + 1
)
SELECT DISTINCT
    s2.item
    , to_char(TO_DATE(s1.std_date) - INTERVAL '10' DAY,'YYYYMMDD') AS warehousing_date
    , to_char(TO_DATE(s1.std_date) + INTERVAL '30' DAY,'YYYYMMDD')AS expiration_date
    , TRUNC(ABS(dbms_random.VALUE(300, 400) ), 0) AS stock_qty
    , 0 AS reserved_stock_qty
  FROM
    make_date s1
    , item_mst s2
;

ALTER TABLE stock ADD PRIMARY KEY(item,warehousing_date, expiration_date);

MERGE INTO stock tar
USING(
    SELECT
        item
        ,to_char(TO_DATE(warehousing_date) - INTERVAL '7' DAY,'YYYYMMDD') AS warehousing_date
        ,expiration_date
        ,stock_qty
        ,reserved_stock_qty
    FROM
        stock
    ) src
    ON(
        tar.item = src.item
    AND tar.warehousing_date = src.warehousing_date
    AND tar.expiration_date = src.expiration_date
        )
WHEN NOT MATCHED THEN INSERT
VALUES(src.item, to_char(TO_DATE(src.warehousing_date) + INTERVAL '1' DAY,'YYYYMMDD'), src.expiration_date, src.stock_qty, src.reserved_stock_qty)
;
COMMIT;

伝票番号の付番

作成した初期データに対して、伝票No、伝票行No、元伝票Noを付番していきます。

numbering_slip.sql

MERGE INTO sales_trn tar
USING(
        SELECT
            seq
            , voucher_clz
            , correct_clz
            , dense_rank() over (order by cust_id,arrive_date) + decode(slip_no,9999,0,nvl(max(slip_no) over (),0)) AS slip_no
            , row_number() OVER (partition by cust_id,arrive_date order by seq) AS slip_row_no
            , item
            , prov_inst_qty
            , cust_id
            , arrive_date
            , dense_rank() over (order by cust_id,arrive_date) + decode(slip_no,9999,0,nvl(max(slip_no) over (),0)) AS org_slip_no
            , offset_flg
            , pick_inst_maked_flg
            , pick_inst_ref_flg
        FROM
            sales_trn
    ) src
ON(
    tar.seq = src.seq
    )
WHEN MATCHED THEN UPDATE
SET
    tar.slip_no = src.slip_no
    ,tar.slip_row_no = src.slip_row_no
    ,tar.org_slip_no = src.org_slip_no
;
COMMIT;

在庫引当対象データの作成

在庫引当を行うトランデータを作成します。

create_pick_inst.sql

DROP TABLE pick_inst;
CREATE TABLE pick_inst AS
WITH all_slip_src AS (--未処理の全伝票
    SELECT
        seq
        , voucher_clz
        , correct_clz
        , slip_no
        , slip_row_no
        , item
        , prov_inst_qty
        , cust_id
        , arrive_date
        , org_slip_no
        , offset_flg
        , pick_inst_maked_flg
        , pick_inst_ref_flg
        , ROWNUM AS proc_seq
    FROM
        sales_trn
    WHERE
        pick_inst_ref_flg = '0'
),jdg_offset as (
        SELECT
            kuro.seq
            , kuro.voucher_clz
            , kuro.correct_clz
            , kuro.slip_no
            , kuro.slip_row_no
            , kuro.item
            , kuro.prov_inst_qty
            , kuro.cust_id
            , kuro.arrive_date
            , kuro.org_slip_no
            , CASE
                WHEN--相殺後、個数訂正あり
                    EXISTS(
                            SELECT
                                1
                            FROM
                                all_slip_src chk
                            WHERE
                                chk.voucher_clz = 'a'
                            AND kuro.slip_no = chk.org_slip_no
                            AND kuro.item = chk.item
                            AND chk.correct_clz = '2'
                            )
                    AND EXISTS(
                                SELECT
                                    1
                                FROM
                                    all_slip_src chk
                                WHERE
                                    chk.voucher_clz = 'k'
                                AND kuro.slip_no = chk.org_slip_no
                                AND kuro.slip_no <> chk.slip_no
                                AND kuro.item = chk.item
                                AND chk.correct_clz = '3'
                                ) THEN '1'
                WHEN--相殺後、個数訂正なし
                    EXISTS(
                            SELECT
                                1
                            FROM
                                all_slip_src chk
                            WHERE
                                chk.voucher_clz = 'a'
                            AND kuro.slip_no = chk.org_slip_no
                            AND kuro.item = chk.item
                            AND chk.correct_clz = '2'
                            )
                    AND NOT EXISTS(
                                SELECT
                                    1
                                FROM
                                    all_slip_src chk
                                WHERE
                                    chk.voucher_clz = 'k'
                                AND kuro.slip_no = chk.org_slip_no
                                AND kuro.slip_no <> chk.slip_no
                                AND kuro.item = chk.item
                                AND chk.correct_clz = '3'
                                ) THEN '1'
                ELSE '0'--相殺なし
            END AS offset_flg
            , kuro.pick_inst_maked_flg
            , '1' AS pick_inst_ref_flg
            , kuro.proc_seq
        FROM
            all_slip_src kuro
)
SELECT
    seq
    , voucher_clz
    , correct_clz
    , slip_no
    , slip_row_no
    , item
    , prov_inst_qty
    , cust_id
    , arrive_date
    , org_slip_no
    , offset_flg
    , '1' AS pcking_inst_maked_flg
    , pick_inst_ref_flg
FROM
    jdg_offset
WHERE
    offset_flg = '0'--未相殺のデータ
AND voucher_clz = 'k'--黒伝
;

在庫引当sql 引当鮮度日特定

コメントもりもり書いてみました。

zaiko_prov.sql

WITH zaiko AS(
    SELECT
        row_number() OVER (ORDER BY s1.item, s1.expiration_date, s1.warehousing_date) AS rn
        ,s1.item
        ,s1.expiration_date
        ,s1.warehousing_date
        ,row_number() OVER (PARTITION BY s1.item ORDER BY s1.expiration_date , s1.warehousing_date) AS PRIORITY
        ,s1.stock_qty
        ,s1.reserved_stock_qty
        ,s1.stock_qty - s1.reserved_stock_qty AS prov_enable_stock_qty
        ,LEAD(s1.stock_qty - s1.reserved_stock_qty,1,0) OVER (PARTITION BY s1.item ORDER BY s1.expiration_date, s1.warehousing_date) AS next_prov_enable_stock_qty
    FROM
        stock s1
    WHERE
        EXISTS(
                SELECT
                    1
                FROM
                    pick_inst s2
                WHERE
                    s1.item = s2.item
                )
),sales AS (
    SELECT
        row_number() OVER (ORDER BY s1.item, s1.arrive_date, s1.cust_id, s1.slip_no,s1.slip_row_no) AS rn
        , s1.cust_id
        , s1.item
        , s3.case_irisu
        , s3.bowl_irisu
        , s1.arrive_date
        , s1.seq
        , s1.slip_no
        , s1.org_slip_no
        , s1.slip_row_no
        , s1.prov_inst_qty
        , s1.offset_flg
    FROM
        pick_inst s1
            LEFT OUTER JOIN item_mst s3
                ON
                    s1.item = s3.item
),rec(
      rn
    , arrive_date
    , seq
    , cust_id
    , item
    , case_irisu
    , bowl_irisu
    , expiration_date
    , warehousing_date
    , PRIORITY
    , prov_enable_stock_qty
    , pre_prov_inst_qty
    , remain_stock_qty
    , next_prov_enable_stock_qty
    , carryover_prov_inst_qty
    , update_qty
    , flg
    , slip_no
    , org_slip_no
    , slip_row_no
    , prov_inst_qty
    , offset_flg
    , remain_prov_inst_qty_flg
    , cnt
)AS(
    SELECT
          s1.rn
        , s1.arrive_date
        , s1.seq
        , s1.cust_id
        , s1.item
        , s1.case_irisu
        , s1.bowl_irisu
        , nvl(s2.expiration_date,'********')--紐付く在庫がない場合、********を設定
        , nvl(s2.warehousing_date,'********')--紐付く在庫がない場合、********を設定
        , CASE
            WHEN(s2.expiration_date IS NULL AND s2.warehousing_date IS NULL) THEN nvl(s2.PRIORITY,0) + 1
            --紐付く在庫がない場合、引当優先順位に1を加算（マイナス在庫の場合でも在庫データをリニアに処理したいため）
            ELSE s2.PRIORITY
            --上記以外は結合条件により紐付く値を設定
        END AS PRIORITY
        , CASE
            WHEN (s2.expiration_date IS NULL AND s2.warehousing_date IS NULL ) THEN 0
            --紐付く在庫がない場合、0を設定
            ELSE s2.prov_enable_stock_qty
            --上記以外の場合、引当可能在庫数を設定
        END AS prov_enable_stock_qty
        , 0 AS pre_prov_inst_qty
        , CASE
            WHEN (s2.expiration_date IS NULL AND s2.warehousing_date IS NULL ) THEN 0
            --紐付く在庫がない場合、0を設定
            WHEN s2.prov_enable_stock_qty - s1.prov_inst_qty <= 0 THEN 0
            --引当可能在庫数から引当指示数を差し引き、0以下となる場合、残在庫数に0を設定
            ELSE s2.prov_enable_stock_qty - s1.prov_inst_qty
            --上記以外は引当可能在庫数から引当指示数を差し引いた値を設定
        END AS remain_stock_qty
        ,nvl(s2.next_prov_enable_stock_qty,0)--次鮮度日の引当可能在庫数がない場合、0を設定
        , CASE
            WHEN (s2.expiration_date IS NULL AND s2.warehousing_date IS NULL ) THEN s1.prov_inst_qty
            --紐付く在庫がない場合、引当指示数を設定
            WHEN s2.prov_enable_stock_qty - s1.prov_inst_qty > 0 THEN 0
            --引当可能在庫数から引当指示数を差し引き、0より多い場合、0を設定
            ELSE ABS(s2.prov_enable_stock_qty - s1.prov_inst_qty)
            --上記以外は引当可能在庫数から引当指示数を差し引いたの絶対数を設定
        END AS carryover_prov_inst_qty
        , CASE
            WHEN (s2.expiration_date IS NULL AND s2.warehousing_date IS NULL ) THEN s1.prov_inst_qty
            --紐付く在庫がない場合、引当指示数を設定
            WHEN s2.prov_enable_stock_qty - s1.prov_inst_qty > 0 THEN s1.prov_inst_qty
            --引当可能在庫数から引当指示数を差し引き、0より多い場合、引当指示数を設定
            ELSE s2.prov_enable_stock_qty
            --上記以外は引当可能在庫数を設定
        END AS update_qty
        , CASE
            WHEN (s2.expiration_date IS NULL AND s2.warehousing_date IS NULL ) THEN -1
            --紐付く在庫がない場合、-1を設定
            WHEN s2.prov_enable_stock_qty - s1.prov_inst_qty < 0 THEN -1
            --引当可能在庫数から引当指示数を差し引き、0より少ない場合、-1を設定
            WHEN s2.prov_enable_stock_qty - s1.prov_inst_qty = 0 THEN 0
            --引当可能在庫数から引当指示数を差し引き、0の場合、0を設定
            WHEN s2.prov_enable_stock_qty - s1.prov_inst_qty > 0 THEN 1
            --引当可能在庫数から引当指示数を差し引き、0より多い場合、1を設定
        END AS flg
        , s1.slip_no
        , s1.org_slip_no
        , s1.slip_row_no
        , s1.prov_inst_qty
        , s1.offset_flg
        , CASE
            WHEN (s2.expiration_date IS NULL AND s2.warehousing_date IS NULL ) THEN 0
            --紐付く在庫がない場合、0を設定
            WHEN s2.prov_enable_stock_qty - s1.prov_inst_qty < 0 THEN 1
            --引当可能在庫数から引当指示数を差し引き、0より少なくなる場合、1を設定
            WHEN s2.prov_enable_stock_qty - s1.prov_inst_qty >= 0 THEN 0
            --引当可能在庫数から引当指示数を差し引き、0以上となる場合、0を設定
        END AS remain_prov_inst_qty_flg
        ,count(*) over() as cnt
    FROM
        sales s1
            LEFT OUTER JOIN zaiko s2--引き当てる在庫データが存在しない場合を考慮
                ON
                    CASE
                        WHEN s2.rn IS NOT NULL THEN s2.rn
                        --紐付く在庫がある場合
                        ELSE s1.rn
                        --紐付く在庫がない場合
                    END = s1.rn
                AND s1.item = s2.item
    WHERE
        s1.rn = 1
    UNION ALL
    SELECT
          s1.rn
        , s1.arrive_date
        , s1.seq
        , s1.cust_id
        , s1.item
        , s1.case_irisu
        , s1.bowl_irisu
        , nvl(s2.expiration_date,'********')--紐付く在庫がない場合、********を設定
        , nvl(s2.warehousing_date,'********')--紐付く在庫がない場合、********を設定
        , CASE
            WHEN tar.item <> s1.item THEN 1
            --前回アイテムと今回アイテムが異なる場合、1を設定
            WHEN(s2.expiration_date IS NULL AND s2.warehousing_date IS NULL) THEN tar.PRIORITY + 1
            --紐付く在庫がない場合、前回の引当優先順位に1を加算（マイナス在庫の場合でも在庫データをリニアに処理したいため）
            ELSE s2.PRIORITY
            --上記以外は結合条件により紐付く値を設定
        END AS PRIORITY
        , CASE
            WHEN tar.item <> s1.item THEN nvl(s2.prov_enable_stock_qty,0)
            --前回アイテムと今回アイテムが異なる場合、今回アイテムの引当可能在庫数を設定
            WHEN tar.remain_stock_qty = 0 THEN tar.next_prov_enable_stock_qty
            --前回の残在庫数が0の場合、前回の次鮮度日の引当可能在庫数を設定
            WHEN (s2.expiration_date IS NULL AND s2.warehousing_date IS NULL ) THEN 0
            --紐付く在庫がない場合、0を設定
            ELSE tar.remain_stock_qty
            --上記以外の場合、前回の残在庫数を設定
        END AS prov_enable_stock_qty
        , CASE
            WHEN tar.carryover_prov_inst_qty > 0 AND (tar.expiration_date <> '********' AND tar.warehousing_date <> '********' ) THEN tar.carryover_prov_inst_qty
            --前回の繰越引当指示数が0より多い場合かつ前回の鮮度日がアスタリスクでない場合は前回の繰越引当指示数を設定
            ELSE 0
            --上記以外の場合、0を設定
        END AS pre_prov_inst_qty
        , CASE
            WHEN tar.item <> s1.item AND (s2.expiration_date IS NULL AND s2.warehousing_date IS NULL ) THEN 0
            --前回アイテムと今回アイテムが異なり、紐付く在庫がない場合、0を設定
            WHEN tar.item <> s1.item AND s2.prov_enable_stock_qty - decode(tar.remain_prov_inst_qty_flg,1,tar.carryover_prov_inst_qty,s1.prov_inst_qty) > 0 THEN s2.prov_enable_stock_qty - decode(tar.remain_prov_inst_qty_flg,1,tar.carryover_prov_inst_qty,s1.prov_inst_qty)
            --前回アイテムと今回アイテムが異なり、かつ今回の引当可能在庫数から前回の繰越引当指示数または今回の引当指示数を差し引き、0より多くなる場合、今回の引当可能在庫数から前回の繰越引当指示数または今回の引当指示数を差し引いた値を設定
            WHEN tar.item <> s1.item AND s2.prov_enable_stock_qty - decode(tar.remain_prov_inst_qty_flg,1,tar.carryover_prov_inst_qty,s1.prov_inst_qty) <= 0 THEN 0
            --前回アイテムと今回アイテムが異なり、かつ今回の引当可能在庫数から前回の繰越引当指示数または今回の引当指示数を差し引き、0以下となる場合、0を設定
            WHEN tar.carryover_prov_inst_qty >= 0 AND decode(tar.remain_stock_qty,0,tar.next_prov_enable_stock_qty,tar.remain_stock_qty) - decode(tar.remain_prov_inst_qty_flg,1,tar.carryover_prov_inst_qty,s1.prov_inst_qty) > 0 THEN decode(tar.remain_stock_qty,0,tar.next_prov_enable_stock_qty,tar.remain_stock_qty) - decode(tar.remain_prov_inst_qty_flg,1,tar.carryover_prov_inst_qty,s1.prov_inst_qty)
            --前回の繰越引当指示数が0以上で、かつ前回の残在庫数または前回の次鮮度日の引当可能在庫数から前回の繰越引当指示数または今回の引当指示数を差し引き、0より多くなる場合、前回の残在庫数または前回の次鮮度日の引当可能在庫数から前回の繰越引当指示数または今回の引当指示数を差し引いた値を設定
            WHEN tar.carryover_prov_inst_qty >= 0 AND decode(tar.remain_stock_qty,0,tar.next_prov_enable_stock_qty,tar.remain_stock_qty) - decode(tar.remain_prov_inst_qty_flg,1,tar.carryover_prov_inst_qty,s1.prov_inst_qty) <= 0 THEN 0
            --前回の繰越引当指示数が0以上で、かつ前回の残在庫数または前回の次鮮度日の引当可能在庫数から前回の繰越引当指示数または今回の引当指示数を差し引き、0以下となる場合、0を設定
            WHEN (s2.expiration_date IS NULL AND s2.warehousing_date IS NULL ) THEN 0
            --紐付く在庫がない場合、0を設定
        END AS remain_stock_qty
        , nvl(s2.next_prov_enable_stock_qty,0)--次鮮度日の引当可能在庫数がない場合、0を設定
        , CASE
            WHEN tar.item <> s1.item AND nvl(s2.prov_enable_stock_qty,0) - decode(tar.remain_prov_inst_qty_flg,1,tar.carryover_prov_inst_qty,s1.prov_inst_qty) >= 0 THEN 0
            --前回アイテムと今回アイテムが異なり、かつ今回の引当可能在庫数から前回の繰越引当指示数または今回の引当指示数を差し引いた値が0以上の場合、0を設定
            WHEN tar.item <> s1.item AND nvl(s2.prov_enable_stock_qty,0) - decode(tar.remain_prov_inst_qty_flg,1,tar.carryover_prov_inst_qty,s1.prov_inst_qty) < 0 THEN ABS(nvl(s2.prov_enable_stock_qty,0) - decode(tar.remain_prov_inst_qty_flg,1,tar.carryover_prov_inst_qty,s1.prov_inst_qty))
            --前回アイテムと今回アイテムが異なり、かつ今回の引当可能在庫数から前回の繰越引当指示数または今回の引当指示数を差し引いた値が0より少なくなる場合、今回の引当可能在庫数から前回の繰越引当指示数または今回の引当指示数を差し引いた値の絶対数を設定
            WHEN decode(tar.remain_stock_qty,0,tar.next_prov_enable_stock_qty,tar.remain_stock_qty) - decode(tar.remain_prov_inst_qty_flg,1,tar.carryover_prov_inst_qty,s1.prov_inst_qty) >= 0 THEN 0
            --前回の残在庫数または前回の次鮮度日の引当可能在庫数から前回の繰越引当指示数または今回の引当指示数を差し引き、0以上場合、0を設定
            WHEN decode(tar.remain_stock_qty,0,tar.next_prov_enable_stock_qty,tar.remain_stock_qty) - decode(tar.remain_prov_inst_qty_flg,1,tar.carryover_prov_inst_qty,s1.prov_inst_qty) < 0 THEN ABS((decode(tar.remain_stock_qty,0,tar.next_prov_enable_stock_qty,tar.remain_stock_qty)) - decode(tar.remain_prov_inst_qty_flg,1,tar.carryover_prov_inst_qty,s1.prov_inst_qty))
            --前回の残在庫数または前回の次鮮度日の引当可能在庫数から前回の繰越引当指示数または今回の引当指示数を差し引き、0より少なくなる場合、前回の残在庫数または前回の次鮮度日の引当可能在庫数から前回の繰越引当指示数または今回の引当指示数を差し引いた値の絶対数を設定
        END AS carryover_prov_inst_qty
        , CASE
            WHEN tar.item <> s1.item AND s2.prov_enable_stock_qty - decode(tar.remain_prov_inst_qty_flg,1,tar.carryover_prov_inst_qty,s1.prov_inst_qty) > 0 THEN decode(tar.remain_prov_inst_qty_flg,1,tar.carryover_prov_inst_qty,s1.prov_inst_qty)
            --前回アイテムと今回アイテムが異なり、かつ今回の引当可能在庫数から前回の繰越引当指示数または今回の引当指示数を差し引いた値が0より多い場合、前回の繰越引当指示数または今回の引当指示数を設定
            WHEN tar.item <> s1.item AND s2.prov_enable_stock_qty - decode(tar.remain_prov_inst_qty_flg,1,tar.carryover_prov_inst_qty,s1.prov_inst_qty) <= 0 THEN s2.prov_enable_stock_qty
            --前回アイテムと今回アイテムが異なり、かつ今回の引当可能在庫数から前回の繰越引当指示数または今回の引当指示数を差し引いた値が0以下の場合、今回の引当可能在庫数を設定
            WHEN (s2.expiration_date IS NULL AND s2.warehousing_date IS NULL ) THEN decode(tar.remain_prov_inst_qty_flg,1,tar.carryover_prov_inst_qty,s1.prov_inst_qty)
            --紐付く在庫がない場合、今回の引当指示数を設定
            WHEN tar.carryover_prov_inst_qty >= 0 AND decode(tar.remain_stock_qty,0,tar.next_prov_enable_stock_qty,tar.remain_stock_qty) - decode(tar.remain_prov_inst_qty_flg,1,tar.carryover_prov_inst_qty,s1.prov_inst_qty) > 0 THEN decode(tar.remain_prov_inst_qty_flg,1,tar.carryover_prov_inst_qty,s1.prov_inst_qty)
            --前回の繰越引当指示数が0以上かつ前回の残在庫数または前回の次鮮度日の引当可能在庫数から前回の繰越引当指示数または今回の引当指示数を差し引き、0より多くなる場合、前回の繰越引当指示数または今回の引当指示数を設定
            WHEN tar.carryover_prov_inst_qty >= 0 AND decode(tar.remain_stock_qty,0,tar.next_prov_enable_stock_qty,tar.remain_stock_qty) - decode(tar.remain_prov_inst_qty_flg,1,tar.carryover_prov_inst_qty,s1.prov_inst_qty) <= 0 THEN decode(tar.remain_stock_qty,0,tar.next_prov_enable_stock_qty,tar.remain_stock_qty)
            --前回の繰越引当指示数が0以上かつ前回の残在庫数または前回の次鮮度日の引当可能在庫数から前回の繰越引当指示数または今回の引当指示数を差し引き、0以下となる場合、前回の次鮮度日の引当可能在庫数または前回の残在庫数を設定
            WHEN decode(tar.remain_stock_qty,0,tar.next_prov_enable_stock_qty,tar.remain_stock_qty) - decode(tar.remain_prov_inst_qty_flg,1,tar.carryover_prov_inst_qty,s1.prov_inst_qty) <= 0 THEN nvl(s2.prov_enable_stock_qty,ABS((tar.next_prov_enable_stock_qty + tar.remain_stock_qty) - decode(tar.remain_prov_inst_qty_flg,1,tar.carryover_prov_inst_qty,s1.prov_inst_qty)))
            --前回の残在庫数または前回の次鮮度日の引当可能在庫数から前回の繰越引当指示数または今回の引当指示数を差し引き、0以下となる場合、今回の引当可能在庫数または前回の残在庫数に前回の次鮮度日の引当可能在庫数を加算した値から前回の繰越引当指示数または今回の引当指示数を差し引いた値の絶対数を設定
        END AS update_qty
        , CASE
            WHEN (tar.item <> s1.item AND s2.prov_enable_stock_qty - decode(tar.remain_prov_inst_qty_flg,1,tar.carryover_prov_inst_qty,s1.prov_inst_qty) < 0) OR (s2.expiration_date IS NULL AND s2.warehousing_date IS NULL) THEN -1
            --前回アイテムと今回アイテムが異なり、かつ今回の引当可能在庫数から前回の繰越引当指示数または今回の引当指示数を差し引いた値が0より少ないまたは紐付く在庫がない場合、-1を設定
            WHEN tar.item <> s1.item AND s2.prov_enable_stock_qty - decode(tar.remain_prov_inst_qty_flg,1,tar.carryover_prov_inst_qty,s1.prov_inst_qty) = 0 THEN 0
            --前回アイテムと今回アイテムが異なり、かつ今回の引当可能在庫数から前回の繰越引当指示数または今回の引当指示数を差し引いた値が0の場合、0を設定
            WHEN tar.item <> s1.item AND s2.prov_enable_stock_qty - decode(tar.remain_prov_inst_qty_flg,1,tar.carryover_prov_inst_qty,s1.prov_inst_qty) > 0 THEN 1
            --前回アイテムと今回アイテムが異なり、かつ今回の引当可能在庫数から今回の引当指示数を差し引いた値が0より多い場合、1を設定
            WHEN decode(tar.remain_stock_qty,0,tar.next_prov_enable_stock_qty,tar.remain_stock_qty) - decode(tar.remain_prov_inst_qty_flg,1,tar.carryover_prov_inst_qty,s1.prov_inst_qty) < 0 THEN -1
            --前回の残在庫数または前回の次鮮度日の引当可能在庫数から前回の繰越引当指示数または今回の引当指示数を差し引き、0より少なくなる場合、-1を設定
            WHEN decode(tar.remain_stock_qty,0,tar.next_prov_enable_stock_qty,tar.remain_stock_qty) - decode(tar.remain_prov_inst_qty_flg,1,tar.carryover_prov_inst_qty,s1.prov_inst_qty) = 0 THEN 0
            --前回の残在庫数または前回の次鮮度日の引当可能在庫数から前回の繰越引当指示数または今回の引当指示数を差し引き、0の場合、0を設定
            WHEN decode(tar.remain_stock_qty,0,tar.next_prov_enable_stock_qty,tar.remain_stock_qty) - decode(tar.remain_prov_inst_qty_flg,1,tar.carryover_prov_inst_qty,s1.prov_inst_qty) > 0 THEN 1
            --前回の残在庫数または前回の次鮮度日の引当可能在庫数から前回の繰越引当指示数または今回の引当指示数を差し引き、0より多くなる場合、1を設定
        END AS flg
        , s1.slip_no
        , s1.org_slip_no
        , s1.slip_row_no
        , s1.prov_inst_qty
        , s1.offset_flg
        , CASE
            WHEN (s2.expiration_date IS NULL AND s2.warehousing_date IS NULL) THEN 0
            --紐付く在庫がない場合、0を設定
            WHEN tar.item <> s1.item AND s2.prov_enable_stock_qty - decode(tar.remain_prov_inst_qty_flg,1,tar.carryover_prov_inst_qty,s1.prov_inst_qty) < 0 THEN 1
            --前回アイテムと今回アイテムが異なり、かつ今回の引当可能在庫数から前回の繰越引当指示数または今回の引当指示数を差し引いた値が0より少なくなる場合、1を設定
            WHEN tar.item <> s1.item AND s2.prov_enable_stock_qty - decode(tar.remain_prov_inst_qty_flg,1,tar.carryover_prov_inst_qty,s1.prov_inst_qty) >= 0 THEN 0
            --前回アイテムと今回アイテムが異なり、かつ今回の引当可能在庫数から前回の繰越引当指示数または今回の引当指示数を差し引いた値が0以上となる場合、0を設定
            WHEN decode(tar.remain_stock_qty,0,tar.next_prov_enable_stock_qty,tar.remain_stock_qty) - decode(tar.remain_prov_inst_qty_flg,1,tar.carryover_prov_inst_qty,s1.prov_inst_qty) < 0 THEN 1
            --前回の残在庫数または前回の次鮮度日の引当可能在庫数から前回の繰越引当指示数または今回の引当指示数を差し引き、0より少なくなる場合、1を設定
            WHEN decode(tar.remain_stock_qty,0,tar.next_prov_enable_stock_qty,tar.remain_stock_qty) - decode(tar.remain_prov_inst_qty_flg,1,tar.carryover_prov_inst_qty,s1.prov_inst_qty) >= 0 THEN 0
            --前回の残在庫数または前回の次鮮度日の引当可能在庫数から前回の繰越引当指示数または今回の引当指示数を差し引き、0以上となる場合、0を設定
        END AS remain_prov_inst_qty_flg
        ,tar.cnt + count(*)over() as cnt
    FROM
        rec tar
            INNER JOIN sales s1
                ON
                    CASE
                        WHEN tar.remain_prov_inst_qty_flg = 1 THEN tar.rn
                        --前回の引当指示数が残っている場合は、同一のトランデータで処理する
                        ELSE tar.rn + 1
                        --上記以外は次のトランデータで処理する
                    END = s1.rn
                AND tar.cnt <= 1000000--ダミー値で終了条件を記載し、パーサをだましにいく（のつもり）。
            LEFT OUTER JOIN zaiko s2
                ON
                    s1.item = s2.item--トランに紐付くアイテムで
                AND CASE
                        WHEN tar.item <> s1.item THEN 1
                        --前回アイテムと今回アイテムが異なる場合、紐付く在庫があるかチェックするため、1を設定
                        WHEN tar.flg > 0 AND tar.item = s1.item THEN tar.PRIORITY
                        --残在庫数が0より大きく、かつ前回アイテムと今回アイテムが同じの場合、次鮮度日の在庫から引当開始しないため、前回アイテムの引当優先順位を設定
                        WHEN tar.flg <= 0 AND tar.item = s1.item THEN tar.PRIORITY + 1
                        --残在庫数が0以下で、かつ前回アイテムと今回アイテムが同じの場合、次鮮度日の在庫から引当開始するため、前回アイテムの引当優先順位に1を加算して設定
                        WHEN tar.flg <= 0 AND tar.next_prov_enable_stock_qty = 0 THEN tar.PRIORITY + 1
                        --残在庫数が0以下で、前回の次鮮度日の引当可能在庫数が0の場合、同一商品在庫データ内の最終レコードとなるので、商品が切り替わるまで前回アイテムの引当優先順位に1を加算して設定
                        ELSE NULL
                    END = s2.PRIORITY
)
SELECT
    item
    , slip_no
    , slip_row_no
    , dense_rank()over(partition by slip_no,slip_row_no order by case when expiration_date <> '********' then 1 else 2 end,expiration_date,case when warehousing_date <> '********' then 1 else 2 end,warehousing_date) as grp--鮮度割れ確認用
    , prov_inst_qty
    , expiration_date
    , warehousing_date
    , PRIORITY
    , update_qty
    , remain_prov_inst_qty_flg
FROM
    rec
;

検証パターン

在庫もりもりある場合の在庫引当sqlの実行結果

在庫もりもりあるので、鮮度割れていませんね。

在庫そこそこない場合の在庫引当sqlの実行結果

ひとつの伝票No、伝票行Noに対して複数の鮮度が割りついているのが確認できますね。

在庫ぶっ飛ばした場合の在庫引当sqlの実行結果

在庫ぶっ飛ばしたので、鮮度割れていませんね。

在庫がない商品をトランに追加投入した場合の在庫引当sqlの実行結果

在庫がある商品をトランに追加投入した場合の在庫引当sqlの実行結果

あとがき

ダミー値でループ終了条件を記載し、パーサをだましにいく（だませているのかはわからない）発想がでてくるのに、時間かかった！参考文献の「枝切り(ノード数の総合計)分析関数の結果を使った枝切り
」でひらめいた！

他にもこんな書き方あるよ！もっとスマートに書けるよ！バグってるよ！みたいなのがあれば、コメントいただけるとうれしいです。

以上、ありがとうございました。