sql oracle recursive lesson 在庫引当

まえがき

再帰クエリで在庫を題材に取り組んでいこうとおもいます。
javaで書かれている内容をsqlでもできたら楽しそうだなーと思ったのがきっかけです。
本記事の目的は在庫引当の動きを再帰で漸化的に顕在化することです。

勢いでやりきりました。。

参考文献

7-54 引き当て処理

在庫の引き当て

在庫引当とは？受注する前には必ず必要な有効在庫の動きを自分で考えてみよう！

新人必修！在庫数の計算問題

基礎から学ぶ、販売管理の仕組み

再帰処理をSQLで記述する Write recursive processing in SQL

仕様

顧客が注文した商品とその購入数を納品日の近い順に、在庫引当処理します。
在庫は賞味期限日（以後、鮮度日）、入庫日の古い順に引当していきます。引き当てる在庫がない場合は鮮度日と入庫日を********とした在庫を作成します（以後、マイナス在庫）。在庫更新数には引当可能在庫数が引当指示数より多い場合は引当指示数を、少ない場合は引当可能在庫数を設定します。また、処理対象の在庫データから引き当てる在庫数がなくなってしまった場合には繰越引当指示数として次鮮度日の在庫データに持ち越すようにします。

データ作成

create_data.sqlを実行します。

create_data.sql

DROP TABLE item_mst;
CREATE TABLE item_mst(item CONSTRAINT item_mst_pk PRIMARY KEY ,item_kbn,price,weight,VOLUME) 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でデフォ値いれとく
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
            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
;

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 ,item,prov_inst_qty,cust_id,arrive_date) 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
    ,m2.item
    ,TRUNC(ABS(dbms_random.VALUE(100,500)),0) AS prov_inst_qty
    ,m1.cust_id
    ,m3.std_date AS arrive_date
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
    ,m2.item
    ,TRUNC(ABS(dbms_random.VALUE(100,500)),0) AS prov_inst_qty
    ,m1.cust_id
    ,m3.std_date AS arrive_date
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(1000, 2000) ), 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;

作成データ確認

conf.sqlを実行し、作成されたテーブルのデータ件数を確認します。

conf.sql

select 'CUST_MST' as tbl,count(*) from CUST_MST group by 'CUST_MST' union all
select 'ITEM_MST' as tbl,count(*) from ITEM_MST group by 'ITEM_MST' union all
select 'BASKET_MST' as tbl,count(*) from BASKET_MST group by 'BASKET_MST' union all
select 'CART_MST' as tbl,count(*) from CART_MST group by 'CART_MST' union all
select 'STOCK' as tbl,count(*) from STOCK group by 'STOCK' union all
select 'SALES_TRN' as tbl,count(*) from SALES_TRN group by 'SALES_TRN'
;

売上の引当優先順位

order byに仕様にあわせた処理順を指定します。

本仕様では納品日の昇順です。

sales.sql

SELECT
    row_number() OVER (ORDER BY item, arrive_date, cust_id, seq) AS rn
    , cust_id
    , item
    , arrive_date
    , seq
    , prov_inst_qty
FROM
    sales_trn
;

在庫の引当優先順位

order byに仕様にあわせた処理順を指定します。残在庫数、引当可能在庫数、次鮮度日の引当可能在庫数を算出しています。また、アイテムごとに複数鮮度日、複数入庫日が存在するので、引き当てる優先順位を明記すべく、priority列を用意しています。

本仕様では鮮度日、入庫日の昇順です。

zaiko.sql

SELECT
    row_number() OVER (ORDER BY item , expiration_date , warehousing_date) AS rn
    ,item
    ,expiration_date
    ,warehousing_date
    ,row_number() OVER (PARTITION BY item ORDER BY expiration_date , warehousing_date) AS PRIORITY
    ,stock_qty
    ,reserved_stock_qty
    ,stock_qty - reserved_stock_qty AS prov_enable_stock_qty
    ,LEAD(stock_qty - reserved_stock_qty,1,0) OVER (PARTITION BY item ORDER BY expiration_date , warehousing_date) AS next_prov_enable_stock_qty
FROM
    stock
;

在庫引当sql

上記のsqlをsubqueryに保持し、その後でグルグルしています。コメントはもりもり書いてみました。

zaiko_prov.sql

WITH zaiko AS(
    SELECT
        row_number() OVER (ORDER BY item , expiration_date , warehousing_date) AS rn
        ,item
        ,expiration_date
        ,warehousing_date
        ,row_number() OVER (PARTITION BY item ORDER BY expiration_date , warehousing_date) AS PRIORITY
        ,stock_qty
        ,reserved_stock_qty
        ,stock_qty - reserved_stock_qty AS prov_enable_stock_qty
        ,LEAD(stock_qty - reserved_stock_qty,1,0) OVER (PARTITION BY item ORDER BY expiration_date , warehousing_date) AS next_prov_enable_stock_qty
    FROM
        stock
),sales AS (
    SELECT
        row_number() OVER (ORDER BY item, arrive_date, cust_id, seq) AS rn
        , cust_id
        , item
        , arrive_date
        , seq
        , prov_inst_qty
        , SUM(prov_inst_qty) OVER (PARTITION BY item) - SUM(prov_inst_qty) OVER (PARTITION BY item ORDER BY item, arrive_date, cust_id, seq) AS remain_stock_qty
    FROM
        sales_trn
),rec(
      rn
    , arrive_date
    , cust_id
    , item
    , prov_inst_qty
    , expiration_date
    , warehousing_date
    , PRIORITY
    , prov_enable_stock_qty
    , remain_stock_qty
    , next_prov_enable_stock_qty
    , carryover_prov_inst_qty
    , update_qty
    , flg
)AS(
    SELECT
          s1.rn
        , s1.arrive_date
        , s1.cust_id
        , s1.item
        , s1.prov_inst_qty
        , 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
        , 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
    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
    WHERE
        s1.rn = 1
    UNION ALL
    SELECT
          s1.rn
        , s1.arrive_date
        , s1.cust_id
        , s1.item
        , s1.prov_inst_qty
        , 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.item <> s1.item AND s2.prov_enable_stock_qty - s1.prov_inst_qty > 0 THEN s2.prov_enable_stock_qty - s1.prov_inst_qty
            --前回アイテムと今回アイテムが異なり、かつ今回の引当可能在庫数から今回の引当指示数を差し引き、0より多くなる場合、今回の引当可能在庫数から今回の引当指示数差し引いた値を設定
            WHEN tar.item <> s1.item AND s2.prov_enable_stock_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) - (s1.prov_inst_qty + tar.carryover_prov_inst_qty) > 0 THEN decode(tar.remain_stock_qty,0,tar.next_prov_enable_stock_qty,tar.remain_stock_qty) - (s1.prov_inst_qty + tar.carryover_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) - (s1.prov_inst_qty + tar.carryover_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) - s1.prov_inst_qty >= 0 THEN 0
            --前回アイテムと今回アイテムが異なり、かつ今回の引当可能在庫数から今回の引当指示数を差し引いた値が0より多い場合、0を設定
            WHEN tar.item <> s1.item AND nvl(s2.prov_enable_stock_qty,0) - s1.prov_inst_qty < 0 THEN ABS(nvl(s2.prov_enable_stock_qty,0) - s1.prov_inst_qty)
            --前回アイテムと今回アイテムが異なり、かつ今回の引当可能在庫数から今回の引当指示数を差し引いた値が0より少なくなる場合、今回の引当可能在庫数から今回の引当指示数を差し引いた値の絶対数を設定
            WHEN decode(tar.remain_stock_qty,0,tar.next_prov_enable_stock_qty,tar.remain_stock_qty) - (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) - (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)) - (s1.prov_inst_qty + tar.carryover_prov_inst_qty))
            --前回の残在庫数または前回の次鮮度日の引当可能在庫数から前回の繰越引当指示数と今回の引当指示数を差し引き、0より少ない場合、前回の残在庫数または前回の次鮮度日の引当可能在庫数から今回の引当指示数と前回の繰越引当指示数を差し引いた値の絶対数を設定
        END AS carryover_prov_inst_qty
        , CASE
            WHEN tar.item <> s1.item AND s2.prov_enable_stock_qty - s1.prov_inst_qty > 0 THEN s1.prov_inst_qty
            --前回アイテムと今回アイテムが異なり、かつ今回の引当可能在庫数から今回の引当指示数を差し引いた値が0より多い場合、今回の引当指示数を設定
            WHEN tar.item <> s1.item AND s2.prov_enable_stock_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 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) - (tar.carryover_prov_inst_qty + s1.prov_inst_qty) > 0 THEN 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) - (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) - s1.prov_inst_qty <= 0 THEN nvl(s2.prov_enable_stock_qty,ABS((tar.next_prov_enable_stock_qty + tar.remain_stock_qty) - (s1.prov_inst_qty + tar.carryover_prov_inst_qty)))
            --前回の残在庫数または前回の次鮮度日の引当可能在庫数から今回の引当指示数を差し引き、0以下となる場合、前回の残在庫数を設定
        END AS update_qty
        , CASE
            WHEN tar.item <> s1.item AND s2.prov_enable_stock_qty - s1.prov_inst_qty < 0 THEN -1
            --前回アイテムと今回アイテムが異なり、かつ今回の引当可能在庫数から今回の引当指示数を差し引いた値が0より少ない場合、-1を設定
            WHEN tar.item <> s1.item AND s2.prov_enable_stock_qty - s1.prov_inst_qty = 0 THEN 0
            --前回アイテムと今回アイテムが異なり、かつ今回の引当可能在庫数から今回の引当指示数を差し引いた値が0の場合、0を設定
            WHEN tar.item <> s1.item AND s2.prov_enable_stock_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) - (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) - (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) - (tar.carryover_prov_inst_qty + s1.prov_inst_qty) > 0 THEN 1
            --前回の残在庫数または前回の次鮮度日の引当可能在庫数から今回の引当指示数と前回の繰越引当指示数を差し引き、0より多い場合、1を設定
        END AS flg
    FROM
        rec tar
            INNER JOIN sales s1
                ON
                    tar.rn + 1 = s1.rn--リニアにトランデータ処理
            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 * FROM rec;

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

在庫もりもりあるので、マイナス在庫が発生していませんね。

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

引き当てる在庫がなくなったので、途中からマイナス在庫が発生していますね。

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

truncate table stock;をかましてから実行してみます。すべてマイナス在庫ですね。

引当済数がすでにある場合の在庫引当sqlの実行結果

今回分の引当数を算出する手前の状態を求めるsqlでもあるので、前回分までの引当済数を差し引いてから処理を行っていますね。

売上もりもりある場合の在庫引当sqlの実行結果（処理時間）

50minかかった！！

SQL> alter system flush shared_pool;

システムが変更されました。

経過: 00:00:00.55
SQL> alter system flush buffer_cache;

システムが変更されました。

経過: 00:00:00.29
SQL> @zaiko_prov.sql
7320000行が選択されました。

経過: 00:50:31.85

実行計画
----------------------------------------------------------
Plan hash value: 255459794

---------------------------------------------------------------------------------------------------------------
| Id  | Operation                                 | Name      | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                          |           |    18E|    15E|       |    18E  (0)|999:59:59 |
|   1 |  VIEW                                     |           |    18E|    15E|       |    18E  (0)|999:59:59 |
|   2 |   UNION ALL (RECURSIVE WITH) BREADTH FIRST|           |       |       |       |            |          |
|   3 |    NESTED LOOPS OUTER                     |           |     4 |   496 |       | 75694   (1)| 00:00:03 |
|*  4 |     VIEW                                  |           |     1 |    60 |       | 75689   (1)| 00:00:03 |
|*  5 |      WINDOW SORT PUSHED RANK              |           |  7320K|   230M|   336M| 75689   (1)| 00:00:03 |
|   6 |       TABLE ACCESS FULL                   | SALES_TRN |  7320K|   230M|       | 10522   (1)| 00:00:01 |
|*  7 |     VIEW                                  |           |     4 |   256 |       |     5  (20)| 00:00:01 |
|   8 |      WINDOW SORT                          |           |   400 | 12400 |       |     5  (20)| 00:00:01 |
|   9 |       TABLE ACCESS FULL                   | STOCK     |   400 | 12400 |       |     4   (0)| 00:00:01 |
|* 10 |    HASH JOIN RIGHT OUTER                  |           |    18E|    15E|       |    18E  (0)|999:59:59 |
|  11 |     BUFFER SORT (REUSE)                   |           |       |       |       |            |          |
|  12 |      VIEW                                 |           |   400 | 26000 |       |     5  (20)| 00:00:01 |
|  13 |       WINDOW SORT                         |           |   400 | 12400 |       |     5  (20)| 00:00:01 |
|  14 |        TABLE ACCESS FULL                  | STOCK     |   400 | 12400 |       |     4   (0)| 00:00:01 |
|  15 |     MERGE JOIN                            |           |    18E|    15E|       |    18E  (0)|999:59:59 |
|  16 |      SORT JOIN                            |           |    18E|    15E|    15E|    18E  (0)|999:59:59 |
|  17 |       RECURSIVE WITH PUMP                 |           |       |       |       |            |          |
|* 18 |      SORT JOIN (REUSE)                    |           |  7320K|   418M|  1068M|   180K  (1)| 00:00:08 |
|  19 |       VIEW                                |           |  7320K|   418M|       | 75689   (1)| 00:00:03 |
|  20 |        WINDOW SORT                        |           |  7320K|   230M|   336M| 75689   (1)| 00:00:03 |
|  21 |         TABLE ACCESS FULL                 | SALES_TRN |  7320K|   230M|       | 10522   (1)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - filter("S1"."RN"=1)
   5 - filter(ROW_NUMBER() OVER ( ORDER BY "ITEM","ARRIVE_DATE","CUST_ID","SEQ")<=1)
   7 - filter("S1"."RN"=SYS_OP_CSEE(CASE  WHEN "S2"."RN"(+) IS NOT NULL THEN "S2"."RN"(+) ELSE
              "S1"."RN" END ) AND SYS_OP_CSEE(CASE  WHEN "S2"."RN"(+) IS NOT NULL THEN "S2"."RN"(+) ELSE "S1"."RN"
              END )=1)
  10 - access("S1"."ITEM"="S2"."ITEM"(+) AND "S2"."PRIORITY"(+)=CASE  WHEN ("TAR"."ITEM"<>"S1"."ITEM")
              THEN 1 WHEN (("TAR"."FLG">0) AND ("TAR"."ITEM"="S1"."ITEM")) THEN "TAR"."PRIORITY" WHEN
              (("TAR"."FLG"<=0) AND ("TAR"."ITEM"="S1"."ITEM")) THEN SYS_OP_CSEE("TAR"."PRIORITY"+1) WHEN
              (("TAR"."FLG"<=0) AND ("TAR"."NEXT_PROV_ENABLE_STOCK_QTY"=0)) THEN SYS_OP_CSEE("TAR"."PRIORITY"+1)
              ELSE NULL END )
  18 - access("S1"."RN"="TAR"."RN"+1)
       filter("S1"."RN"="TAR"."RN"+1)


統計
----------------------------------------------------------
       1396  recursive calls
   76087917  db block gets
      77297  consistent gets
     157878  physical reads
      13972  redo size
  343212818  bytes sent via SQL*Net to client
    5376122  bytes received via SQL*Net from client
     488001  SQL*Net roundtrips to/from client
   14640023  sorts (memory)
          2  sorts (disk)
    7320000  rows processed

あとがき

プログラムの動きを想像しにくい場合、テストデータを適当なさじ加減で作って動きに問題ないか確認するのは楽でいいよなー。（ﾃｷﾄｰ）

引当優先順位にかんしてはマスタ等で管理していれば、動的に組み替えることも可能ですね。次回以降書き換えてみようと思います。

処理時間はトランがどの程度の件数になると急激に増え始めるのかを測定してみるのもおもしろそうです。ハノイの塔とかで先人方が取り組んでいるぽいので。

match_recoganizeやmodelでの書き換えしても練習になりそうです。

引当済数の算出クエリとかは次回以降書こうと思います。

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

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

20181029追記

nvlの対応漏れに気づき、修正しました。

zaiko_prov.sql

WITH zaiko AS(
    SELECT
        row_number() OVER (ORDER BY item , expiration_date , warehousing_date) AS rn
        ,item
        ,expiration_date
        ,warehousing_date
        ,row_number() OVER (PARTITION BY item ORDER BY expiration_date , warehousing_date) AS PRIORITY
        ,stock_qty
        ,reserved_stock_qty
        ,stock_qty - reserved_stock_qty AS prov_enable_stock_qty
        ,LEAD(stock_qty - reserved_stock_qty,1,0) OVER (PARTITION BY item ORDER BY expiration_date , warehousing_date) AS next_prov_enable_stock_qty
    FROM
        stock
),sales AS (
    SELECT
        row_number() OVER (ORDER BY item, arrive_date, cust_id, seq) AS rn
        , cust_id
        , item
        , arrive_date
        , seq
        , prov_inst_qty
        , SUM(prov_inst_qty) OVER (PARTITION BY item) - SUM(prov_inst_qty) OVER (PARTITION BY item ORDER BY item, arrive_date, cust_id, seq) AS remain_stock_qty
    FROM
        sales_trn
),rec(
      rn
    , arrive_date
    , cust_id
    , item
    , prov_inst_qty
    , expiration_date
    , warehousing_date
    , PRIORITY
    , prov_enable_stock_qty
    , remain_stock_qty
    , next_prov_enable_stock_qty
    , carryover_prov_inst_qty
    , update_qty
    , flg
)AS(
    SELECT
          s1.rn
        , s1.arrive_date
        , s1.cust_id
        , s1.item
        , s1.prov_inst_qty
        , 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 nvl(s2.prov_enable_stock_qty,0)
            --上記以外の場合、引当可能在庫数を設定
        END AS prov_enable_stock_qty
        , CASE
            WHEN (s2.expiration_date IS NULL AND s2.warehousing_date IS NULL ) THEN 0
            --紐付く在庫がない場合、0を設定
            WHEN nvl(s2.prov_enable_stock_qty,0) - s1.prov_inst_qty <= 0 THEN 0
            --引当可能在庫数から引当指示数を差し引き、0以下となる場合、残在庫数に0を設定
            ELSE nvl(s2.prov_enable_stock_qty,0) - 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 nvl(s2.prov_enable_stock_qty,0) - s1.prov_inst_qty > 0 THEN 0
            --引当可能在庫数から引当指示数を差し引き、0より多い場合、0を設定
            ELSE ABS(nvl(s2.prov_enable_stock_qty,0) - 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 nvl(s2.prov_enable_stock_qty,0) - s1.prov_inst_qty > 0 THEN s1.prov_inst_qty
            --引当可能在庫数から引当指示数を差し引き、0より多い場合、引当指示数を設定
            ELSE nvl(s2.prov_enable_stock_qty,0)
            --上記以外は引当可能在庫数を設定
        END AS update_qty
        , CASE
            WHEN (s2.expiration_date IS NULL AND s2.warehousing_date IS NULL ) THEN -1
            --紐付く在庫がない場合、-1を設定
            WHEN nvl(s2.prov_enable_stock_qty,0) - s1.prov_inst_qty < 0 THEN -1
            --引当可能在庫数から引当指示数を差し引き、0より少ない場合、-1を設定
            WHEN nvl(s2.prov_enable_stock_qty,0) - s1.prov_inst_qty = 0 THEN 0
            --引当可能在庫数から引当指示数を差し引き、0の場合、0を設定
            WHEN nvl(s2.prov_enable_stock_qty,0) - s1.prov_inst_qty > 0 THEN 1
            --引当可能在庫数から引当指示数を差し引き、0より多い場合、1を設定
        END AS flg
    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
    WHERE
        s1.rn = 1
    UNION ALL
    SELECT
          s1.rn
        , s1.arrive_date
        , s1.cust_id
        , s1.item
        , s1.prov_inst_qty
        , 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.item <> s1.item AND nvl(s2.prov_enable_stock_qty,0) - s1.prov_inst_qty > 0 THEN nvl(s2.prov_enable_stock_qty,0) - s1.prov_inst_qty
            --前回アイテムと今回アイテムが異なり、かつ今回の引当可能在庫数から今回の引当指示数を差し引き、0より多くなる場合、今回の引当可能在庫数から今回の引当指示数差し引いた値を設定
            WHEN tar.item <> s1.item AND nvl(s2.prov_enable_stock_qty,0) - 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) - (s1.prov_inst_qty + tar.carryover_prov_inst_qty) > 0 THEN decode(tar.remain_stock_qty,0,tar.next_prov_enable_stock_qty,tar.remain_stock_qty) - (s1.prov_inst_qty + tar.carryover_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) - (s1.prov_inst_qty + tar.carryover_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) - s1.prov_inst_qty >= 0 THEN 0
            --前回アイテムと今回アイテムが異なり、かつ今回の引当可能在庫数から今回の引当指示数を差し引いた値が0より多い場合、0を設定
            WHEN tar.item <> s1.item AND nvl(s2.prov_enable_stock_qty,0) - s1.prov_inst_qty < 0 THEN ABS(nvl(s2.prov_enable_stock_qty,0) - s1.prov_inst_qty)
            --前回アイテムと今回アイテムが異なり、かつ今回の引当可能在庫数から今回の引当指示数を差し引いた値が0より少なくなる場合、今回の引当可能在庫数から今回の引当指示数を差し引いた値の絶対数を設定
            WHEN decode(tar.remain_stock_qty,0,tar.next_prov_enable_stock_qty,tar.remain_stock_qty) - (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) - (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)) - (s1.prov_inst_qty + tar.carryover_prov_inst_qty))
            --前回の残在庫数または前回の次鮮度日の引当可能在庫数から前回の繰越引当指示数と今回の引当指示数を差し引き、0より少ない場合、前回の残在庫数または前回の次鮮度日の引当可能在庫数から今回の引当指示数と前回の繰越引当指示数を差し引いた値の絶対数を設定
        END AS carryover_prov_inst_qty
        , CASE
            WHEN tar.item <> s1.item AND nvl(s2.prov_enable_stock_qty,0) - s1.prov_inst_qty > 0 THEN s1.prov_inst_qty
            --前回アイテムと今回アイテムが異なり、かつ今回の引当可能在庫数から今回の引当指示数を差し引いた値が0より多い場合、今回の引当指示数を設定
            WHEN tar.item <> s1.item AND nvl(s2.prov_enable_stock_qty,0) - s1.prov_inst_qty <= 0 THEN nvl(s2.prov_enable_stock_qty,s1.prov_inst_qty)
            --前回アイテムと今回アイテムが異なり、かつ今回の引当可能在庫数から今回の引当指示数を差し引いた値が0以下の場合、今回の引当可能在庫数あるいは今回引当指示数を設定
            WHEN (s2.expiration_date IS NULL AND s2.warehousing_date IS NULL ) THEN 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) - (tar.carryover_prov_inst_qty + s1.prov_inst_qty) > 0 THEN 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) - (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) - s1.prov_inst_qty <= 0 THEN nvl(s2.prov_enable_stock_qty,ABS((tar.next_prov_enable_stock_qty + tar.remain_stock_qty) - (s1.prov_inst_qty + tar.carryover_prov_inst_qty)))
            --前回の残在庫数または前回の次鮮度日の引当可能在庫数から今回の引当指示数を差し引き、0以下となる場合、前回の残在庫数を設定
        END AS update_qty
        , CASE
            WHEN tar.item <> s1.item AND nvl(s2.prov_enable_stock_qty,0) - s1.prov_inst_qty < 0 THEN -1
            --前回アイテムと今回アイテムが異なり、かつ今回の引当可能在庫数から今回の引当指示数を差し引いた値が0より少ない場合、-1を設定
            WHEN tar.item <> s1.item AND nvl(s2.prov_enable_stock_qty,0) - s1.prov_inst_qty = 0 THEN 0
            --前回アイテムと今回アイテムが異なり、かつ今回の引当可能在庫数から今回の引当指示数を差し引いた値が0の場合、0を設定
            WHEN tar.item <> s1.item AND nvl(s2.prov_enable_stock_qty,0) - 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) - (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) - (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) - (tar.carryover_prov_inst_qty + s1.prov_inst_qty) > 0 THEN 1
            --前回の残在庫数または前回の次鮮度日の引当可能在庫数から今回の引当指示数と前回の繰越引当指示数を差し引き、0より多い場合、1を設定
        END AS flg
    FROM
        rec tar
            INNER JOIN sales s1
                ON
                    tar.rn + 1 = s1.rn--リニアにトランデータ処理
            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 * FROM rec;