#まえがき
再帰クエリで在庫を題材に取り組んでいこうとおもいます。
javaで書かれている内容をsqlでもできたら楽しそうだなーと思ったのがきっかけです。
本記事の目的は在庫引当の動きを再帰で漸化的に顕在化することです。
勢いでやりきりました。。
#参考文献
[7-54 引き当て処理]
(http://www.geocities.jp/oraclesqlpuzzle/7-54.html)
[在庫の引き当て]
(http://www.netsuite.co.jp/resource/glossary/zaiko-2.shtml)
[在庫引当とは？受注する前には必ず必要な有効在庫の動きを自分で考えてみよう！]
(https://info.isi-grp.co.jp/blog/grandit/inventory-reservation)
[新人必修！在庫数の計算問題]
(https://www.nogenius.jp/cloud/20120516.htm)
[基礎から学ぶ、販売管理の仕組み]
(http://products.ndis.jp/SKit/function/hk_06.html)
[再帰処理をSQLで記述する Write recursive processing in SQL]
(https://www.youtube.com/watch?v=X73Vi9wjWrI)
#仕様
顧客が注文した商品とその購入数を納品日の近い順に、在庫引当処理します。
在庫は賞味期限日（以後、鮮度日）、入庫日の古い順に引当していきます。引き当てる在庫がない場合は鮮度日と入庫日を********とした在庫を作成します（以後、マイナス在庫）。在庫更新数には引当可能在庫数が引当指示数より多い場合は引当指示数を、少ない場合は引当可能在庫数を設定します。また、処理対象の在庫データから引き当てる在庫数がなくなってしまった場合には繰越引当指示数として次鮮度日の在庫データに持ち越すようにします。
#データ作成
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;