I have a stock query which gives the stock available for a particular item by location:
Another query gives all the receipts by quantity, date and receipt txn and number for the same item in descending order:
We want an output like below using a SQL Query:
I want to add the receipt details (transaction code, number, quantity and date) for each location stock available and distribute it as per the receipt quantity comparing the available stock. The receipt details and quantity goes in loop until the total matches the stock quantity and then it takes the next receipt and loops until all the location quantities are matched with the receipts.
Kindly I need your expertise in solving this issue. Thanks.
Query to create the Stock Table and insert data:
CREATE TABLE STOCK
(
LCS_ITEM_CODE VARCHAR2(20 BYTE) NOT NULL,
LCS_LOCN_CODE VARCHAR2(12 BYTE) NOT NULL,
ITEM_UOM_CODE VARCHAR2(12 BYTE),
STOCK_QTY NUMBER,
STOCKROWNUM NUMBER
);
SET DEFINE OFF;
Insert into STOCK
(LCS_ITEM_CODE, LCS_LOCN_CODE, ITEM_UOM_CODE, STOCK_QTY, STOCKROWNUM)
Values
('AMZ_BSCS', 'A001', 'PCS', 2280, 1);
Insert into STOCK
(LCS_ITEM_CODE, LCS_LOCN_CODE, ITEM_UOM_CODE, STOCK_QTY, STOCKROWNUM)
Values
('AMZ_BSCS', 'A002', 'PCS', 205, 2);
Insert into STOCK
(LCS_ITEM_CODE, LCS_LOCN_CODE, ITEM_UOM_CODE, STOCK_QTY, STOCKROWNUM)
Values
('AMZ_BSCS', 'A003', 'PCS', 188, 3);
Insert into STOCK
(LCS_ITEM_CODE, LCS_LOCN_CODE, ITEM_UOM_CODE, STOCK_QTY, STOCKROWNUM)
Values
('AMZ_BSCS', 'A004', 'PCS', 111, 4);
Insert into STOCK
(LCS_ITEM_CODE, LCS_LOCN_CODE, ITEM_UOM_CODE, STOCK_QTY, STOCKROWNUM)
Values
('AMZ_BSCS', 'A005', 'PCS', 104, 5);
Insert into STOCK
(LCS_ITEM_CODE, LCS_LOCN_CODE, ITEM_UOM_CODE, STOCK_QTY, STOCKROWNUM)
Values
('AMZ_BSCS', 'A006', 'PCS', 99, 6);
Insert into STOCK
(LCS_ITEM_CODE, LCS_LOCN_CODE, ITEM_UOM_CODE, STOCK_QTY, STOCKROWNUM)
Values
('AMZ_BSCS', 'A007', 'PCS', 98, 7);
Insert into STOCK
(LCS_ITEM_CODE, LCS_LOCN_CODE, ITEM_UOM_CODE, STOCK_QTY, STOCKROWNUM)
Values
('AMZ_BSCS', 'A008', 'PCS', 88, 8);
Insert into STOCK
(LCS_ITEM_CODE, LCS_LOCN_CODE, ITEM_UOM_CODE, STOCK_QTY, STOCKROWNUM)
Values
('AMZ_BSCS', 'A009', 'PCS', 71, 9);
Insert into STOCK
(LCS_ITEM_CODE, LCS_LOCN_CODE, ITEM_UOM_CODE, STOCK_QTY, STOCKROWNUM)
Values
('AMZ_BSCS', 'A010', 'PCS', 65, 10);
Insert into STOCK
(LCS_ITEM_CODE, LCS_LOCN_CODE, ITEM_UOM_CODE, STOCK_QTY, STOCKROWNUM)
Values
('AMZ_BSCS', 'A011', 'PCS', 60, 11);
Insert into STOCK
(LCS_ITEM_CODE, LCS_LOCN_CODE, ITEM_UOM_CODE, STOCK_QTY, STOCKROWNUM)
Values
('AMZ_BSCS', 'A012', 'PCS', 56, 12);
Insert into STOCK
(LCS_ITEM_CODE, LCS_LOCN_CODE, ITEM_UOM_CODE, STOCK_QTY, STOCKROWNUM)
Values
('AMZ_BSCS', 'A013', 'PCS', 46, 13);
Insert into STOCK
(LCS_ITEM_CODE, LCS_LOCN_CODE, ITEM_UOM_CODE, STOCK_QTY, STOCKROWNUM)
Values
('AMZ_BSCS', 'A014', 'PCS', 24, 14);
Insert into STOCK
(LCS_ITEM_CODE, LCS_LOCN_CODE, ITEM_UOM_CODE, STOCK_QTY, STOCKROWNUM)
Values
('AMZ_BSCS', 'A015', 'PCS', 13, 15);
Insert into STOCK
(LCS_ITEM_CODE, LCS_LOCN_CODE, ITEM_UOM_CODE, STOCK_QTY, STOCKROWNUM)
Values
('AMZ_BSCS', 'A016', 'PCS', 5, 16);
COMMIT;
Query to create the receipts table and data:
CREATE TABLE RECEIPT
(
SL_ITEM_CODE VARCHAR2(20 BYTE),
ITEM_UOM_CODE VARCHAR2(12 BYTE),
SL_TXN_CODE VARCHAR2(12 BYTE),
SL_NO NUMBER(10),
SL_CONF_DT DATE,
SL_QTY NUMBER,
RECEIPTROWNUM NUMBER
);
SET DEFINE OFF;
Insert into RECEIPT
(SL_ITEM_CODE, ITEM_UOM_CODE, SL_TXN_CODE, SL_NO, SL_CONF_DT,
SL_QTY, RECEIPTROWNUM)
Values
('AMZ_BSCS', 'PCS', 'RECP', 2024000375, TO_DATE('07/30/2020 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
3000, 1);
Insert into RECEIPT
(SL_ITEM_CODE, ITEM_UOM_CODE, SL_TXN_CODE, SL_NO, SL_CONF_DT,
SL_QTY, RECEIPTROWNUM)
Values
('AMZ_BSCS', 'PCS', 'RECP', 2024000200, TO_DATE('04/06/2020 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
4000, 2);
Insert into RECEIPT
(SL_ITEM_CODE, ITEM_UOM_CODE, SL_TXN_CODE, SL_NO, SL_CONF_DT,
SL_QTY, RECEIPTROWNUM)
Values
('AMZ_BSCS', 'PCS', 'RECP', 2022999987, TO_DATE('11/24/2020 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
8000, 3);
Insert into RECEIPT
(SL_ITEM_CODE, ITEM_UOM_CODE, SL_TXN_CODE, SL_NO, SL_CONF_DT,
SL_QTY, RECEIPTROWNUM)
Values
('AMZ_BSCS', 'PCS', 'RECP', 2022000084, TO_DATE('02/09/2020 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
2500, 4);
Insert into RECEIPT
(SL_ITEM_CODE, ITEM_UOM_CODE, SL_TXN_CODE, SL_NO, SL_CONF_DT,
SL_QTY, RECEIPTROWNUM)
Values
('AMZ_BSCS', 'PCS', 'RECP', 2021000389, TO_DATE('09/10/2020 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
2000, 5);
Insert into RECEIPT
(SL_ITEM_CODE, ITEM_UOM_CODE, SL_TXN_CODE, SL_NO, SL_CONF_DT,
SL_QTY, RECEIPTROWNUM)
Values
('AMZ_BSCS', 'PCS', 'RECP', 2021000001, TO_DATE('12/08/2020 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
3500, 6);
Insert into RECEIPT
(SL_ITEM_CODE, ITEM_UOM_CODE, SL_TXN_CODE, SL_NO, SL_CONF_DT,
SL_QTY, RECEIPTROWNUM)
Values
('AMZ_BSCS', 'PCS', 'RECP', 2020000179, TO_DATE('06/23/2020 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
3500, 7);
Insert into RECEIPT
(SL_ITEM_CODE, ITEM_UOM_CODE, SL_TXN_CODE, SL_NO, SL_CONF_DT,
SL_QTY, RECEIPTROWNUM)
Values
('AMZ_BSCS', 'PCS', 'RECP', 2019999995, TO_DATE('11/30/2020 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
3000, 8);
Insert into RECEIPT
(SL_ITEM_CODE, ITEM_UOM_CODE, SL_TXN_CODE, SL_NO, SL_CONF_DT,
SL_QTY, RECEIPTROWNUM)
Values
('AMZ_BSCS', 'PCS', 'RECP', 2018999998, TO_DATE('12/18/2020 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
1000, 9);
Insert into RECEIPT
(SL_ITEM_CODE, ITEM_UOM_CODE, SL_TXN_CODE, SL_NO, SL_CONF_DT,
SL_QTY, RECEIPTROWNUM)
Values
('AMZ_BSCS', 'PCS', 'RECP', 2018000042, TO_DATE('01/12/2020 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
6000, 10);
Insert into RECEIPT
(SL_ITEM_CODE, ITEM_UOM_CODE, SL_TXN_CODE, SL_NO, SL_CONF_DT,
SL_QTY, RECEIPTROWNUM)
Values
('AMZ_BSCS', 'PCS', 'RECP', 2017000171, TO_DATE('07/05/2020 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
4500, 11);
Insert into RECEIPT
(SL_ITEM_CODE, ITEM_UOM_CODE, SL_TXN_CODE, SL_NO, SL_CONF_DT,
SL_QTY, RECEIPTROWNUM)
Values
('AMZ_BSCS', 'PCS', 'RECP', 2017000001, TO_DATE('01/19/2020 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
2000, 12);
Insert into RECEIPT
(SL_ITEM_CODE, ITEM_UOM_CODE, SL_TXN_CODE, SL_NO, SL_CONF_DT,
SL_QTY, RECEIPTROWNUM)
Values
('AMZ_BSCS', 'PCS', 'RECP', 2016000071, TO_DATE('03/30/2020 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
3500, 13);
Insert into RECEIPT
(SL_ITEM_CODE, ITEM_UOM_CODE, SL_TXN_CODE, SL_NO, SL_CONF_DT,
SL_QTY, RECEIPTROWNUM)
Values
('AMZ_BSCS', 'PCS', 'RECP', 2015000177, TO_DATE('07/01/2020 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
1000, 14);
Insert into RECEIPT
(SL_ITEM_CODE, ITEM_UOM_CODE, SL_TXN_CODE, SL_NO, SL_CONF_DT,
SL_QTY, RECEIPTROWNUM)
Values
('AMZ_BSCS', 'PCS', 'RECP', 2015000026, TO_DATE('01/09/2020 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
3000, 15);
Insert into RECEIPT
(SL_ITEM_CODE, ITEM_UOM_CODE, SL_TXN_CODE, SL_NO, SL_CONF_DT,
SL_QTY, RECEIPTROWNUM)
Values
('AMZ_BSCS', 'PCS', 'RECP', 2014000117, TO_DATE('04/22/2020 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
1000, 16);
Insert into RECEIPT
(SL_ITEM_CODE, ITEM_UOM_CODE, SL_TXN_CODE, SL_NO, SL_CONF_DT,
SL_QTY, RECEIPTROWNUM)
Values
('AMZ_BSCS', 'PCS', 'RECP', 2013000174, TO_DATE('08/30/2020 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
2000, 17);
Insert into RECEIPT
(SL_ITEM_CODE, ITEM_UOM_CODE, SL_TXN_CODE, SL_NO, SL_CONF_DT,
SL_QTY, RECEIPTROWNUM)
Values
('AMZ_BSCS', 'PCS', 'RECP', 2013000022, TO_DATE('01/09/2020 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
2000, 18);
Insert into RECEIPT
(SL_ITEM_CODE, ITEM_UOM_CODE, SL_TXN_CODE, SL_NO, SL_CONF_DT,
SL_QTY, RECEIPTROWNUM)
Values
('AMZ_BSCS', 'PCS', 'RECP', 2012000313, TO_DATE('08/16/2020 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
2000, 19);
Insert into RECEIPT
(SL_ITEM_CODE, ITEM_UOM_CODE, SL_TXN_CODE, SL_NO, SL_CONF_DT,
SL_QTY, RECEIPTROWNUM)
Values
('AMZ_BSCS', 'PCS', 'RECP', 2012000089, TO_DATE('03/01/2020 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
2000, 20);
Insert into RECEIPT
(SL_ITEM_CODE, ITEM_UOM_CODE, SL_TXN_CODE, SL_NO, SL_CONF_DT,
SL_QTY, RECEIPTROWNUM)
Values
('AMZ_BSCS', 'PCS', 'RECP', 2012000090, TO_DATE('02/24/2020 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
40, 21);
Insert into RECEIPT
(SL_ITEM_CODE, ITEM_UOM_CODE, SL_TXN_CODE, SL_NO, SL_CONF_DT,
SL_QTY, RECEIPTROWNUM)
Values
('AMZ_BSCS', 'PCS', 'RECP', 2011000243, TO_DATE('08/20/2020 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
1960, 22);
Insert into RECEIPT
(SL_ITEM_CODE, ITEM_UOM_CODE, SL_TXN_CODE, SL_NO, SL_CONF_DT,
SL_QTY, RECEIPTROWNUM)
Values
('AMZ_BSCS', 'PCS', 'RECP', 2011000131, TO_DATE('05/12/2020 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
500, 23);
Insert into RECEIPT
(SL_ITEM_CODE, ITEM_UOM_CODE, SL_TXN_CODE, SL_NO, SL_CONF_DT,
SL_QTY, RECEIPTROWNUM)
Values
('AMZ_BSCS', 'PCS', 'RECP', 2011000049, TO_DATE('02/11/2020 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
2000, 24);
Insert into RECEIPT
(SL_ITEM_CODE, ITEM_UOM_CODE, SL_TXN_CODE, SL_NO, SL_CONF_DT,
SL_QTY, RECEIPTROWNUM)
Values
('AMZ_BSCS', 'PCS', 'RECP', 2010000223, TO_DATE('08/05/2020 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
500, 25);
Insert into RECEIPT
(SL_ITEM_CODE, ITEM_UOM_CODE, SL_TXN_CODE, SL_NO, SL_CONF_DT,
SL_QTY, RECEIPTROWNUM)
Values
('AMZ_BSCS', 'PCS', 'RECP', 2010000135, TO_DATE('05/08/2020 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
500, 26);
Insert into RECEIPT
(SL_ITEM_CODE, ITEM_UOM_CODE, SL_TXN_CODE, SL_NO, SL_CONF_DT,
SL_QTY, RECEIPTROWNUM)
Values
('AMZ_BSCS', 'PCS', 'RECP', 2009000133, TO_DATE('06/11/2020 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
4000, 27);
COMMIT;
The stock query:
SELECT LCS_ITEM_CODE,LCS_LOCN_CODE,ITEM_UOM_CODE,STOCK, STOCKROWNUM FROM STOCK
The receipt query:
Select SL_ITEM_CODE,ITEM_UOM_CODE,SL_TXN_CODE,SL_NO, SL_CONF_DT, SL_QTY,RECEIPTROWNUM FROM RECEIPT;
I have tried using cursors and temporary table , this is how far I was able to go but doesn’t give the desired result:
Query to create temporary table:
CREATE GLOBAL TEMPORARY TABLE temp_results (
LCS_ITEM_CODE VARCHAR2(50),
LCS_LOCN_CODE VARCHAR2(50),
ITEM_UOM_CODE VARCHAR2(50),
STOCK_QTY NUMBER,
SL_TXN_CODE VARCHAR2(50),
SL_NO VARCHAR2(50),
SL_CONF_DT DATE,
SL_QTY NUMBER,
BALANCE NUMBER
) ON COMMIT PRESERVE ROWS;
The cursor query:
DECLARE
TYPE stock_rec_type
IS
RECORD (
LCS_ITEM_CODE os_locn_curr_stk.lcs_item_code%TYPE,
LCS_LOCN_CODE os_locn_curr_stk.lcs_locn_code%TYPE,
ITEM_UOM_CODE om_item_uom.iu_uom_code%TYPE,
STOCK_QTY NUMBER,
STOCKROWNUM NUMBER
);
TYPE receipt_rec_type
IS
RECORD (
SL_ITEM_CODE os_stk_ledger.sl_item_code%TYPE,
ITEM_UOM_CODE om_item_uom.iu_uom_code%TYPE,
SL_TXN_CODE os_stk_ledger.sl_txn_code%TYPE,
SL_NO os_stk_ledger.sl_no%TYPE,
SL_CONF_DT DATE,
SL_QTY NUMBER,
RECEIPTROWNUM NUMBER
);
CURSOR stock_cur
IS
SELECT LCS_ITEM_CODE,LCS_LOCN_CODE,ITEM_UOM_CODE,STOCK, STOCKROWNUM FROM STOCK;
CURSOR receipt_cur
IS
Select SL_ITEM_CODE,ITEM_UOM_CODE,SL_TXN_CODE,SL_NO, SL_CONF_DT, SL_QTY,RECEIPTROWNUM FROM RECEIPT;
stock_rec stock_rec_type;
receipt_rec receipt_rec_type;
balance NUMBER;
temp_balance NUMBER;
BEGIN
balance:=0;
OPEN receipt_cur;
FETCH receipt_cur INTO receipt_rec;
WHILE receipt_cur%FOUND --AND balance<=0--
LOOP --
balance := receipt_rec.SL_QTY-balance;
OPEN stock_cur;
FETCH stock_cur INTO stock_rec;
WHILE stock_cur%FOUND
LOOP
balance := balance-stock_rec.STOCK_QTY;
if balance<0 THEN
FETCH receipt_cur INTO receipt_rec;
ELSE
INSERT INTO temp_results
VALUES (stock_rec.LCS_ITEM_CODE,
stock_rec.LCS_LOCN_CODE,
stock_rec.ITEM_UOM_CODE,
stock_rec.STOCK_QTY,
receipt_rec.SL_TXN_CODE,
receipt_rec.SL_NO,
receipt_rec.SL_CONF_DT,
receipt_rec.SL_QTY,
balance);
END IF;
--if balance<0 THEN
--balance:=99;
-- END IF;
FETCH stock_cur INTO stock_rec;
END LOOP;
CLOSE stock_cur;
FETCH receipt_cur INTO receipt_rec;
END LOOP;
CLOSE receipt_cur;
END;
/
SELECT * FROM temp_results;
Calculate the running totals for the stock and receipt and then join on thse running totals:
WITH total_stock (
LCS_ITEM_CODE,
LCS_LOCN_CODE,
ITEM_UOM_CODE,
STOCK_QTY,
STOCKROWNUM,
TOTAL_STOCK_QTY,
PREV_TOTAL_STOCK_QTY
) AS (
SELECT s.*,
SUM(stock_qty) OVER (PARTITION BY lcs_item_code, item_uom_code ORDER BY stockrownum),
SUM(stock_qty) OVER (PARTITION BY lcs_item_code, item_uom_code ORDER BY stockrownum) - stock_qty
FROM stock s
),
total_receipts (
SL_ITEM_CODE,
ITEM_UOM_CODE,
SL_TXN_CODE,
SL_NO,
SL_CONF_DT,
SL_QTY,
RECEIPTROWNUM,
TOTAL_SL_QTY,
PREV_TOTAL_SL_QTY
) AS (
SELECT r.*,
SUM(sl_qty) OVER (PARTITION BY sl_item_code, item_uom_code ORDER BY receiptrownum),
SUM(sl_qty) OVER (PARTITION BY sl_item_code, item_uom_code ORDER BY receiptrownum) - sl_qty
FROM receipt r
)
SELECT s.lcs_item_code,
s.lcs_locn_code,
s.item_uom_code,
LEAST(s.total_stock_qty, r.total_sl_qty)
- GREATEST(s.prev_total_stock_qty, r.prev_total_sl_qty) AS stock_qty,
r.sl_txn_code,
r.sl_no,
r.sl_conf_dt,
r.sl_qty,
GREATEST(r.total_sl_qty - s.total_stock_qty, 0) AS balance
FROM total_stock s
LEFT OUTER JOIN total_receipts r
ON s.lcs_item_code = r.sl_item_code
AND s.item_uom_code = r.item_uom_code
AND s.total_stock_qty > r.prev_total_sl_qty
AND s.prev_total_stock_qty < r.total_sl_qty
ORDER BY
s.lcs_item_code,
s.item_uom_code,
s.stockrownum;
Which, for the sample data, outputs:
| LCS _ITEM _CODE |
LCS _LOCN _COD |
ITEM _UOM _COD |
STOCK _QTY |
SL _TXN _CODE |
SL_NO | SL_CONF_DT | SL _QTY |
BALANCE |
|---|---|---|---|---|---|---|---|---|
| AMZ_BSCS | A001 | PCS | 2280 | RECP | 2024000375 | 2020-07-30 00:00:00 | 3000 | 720 |
| AMZ_BSCS | A002 | PCS | 205 | RECP | 2024000375 | 2020-07-30 00:00:00 | 3000 | 515 |
| AMZ_BSCS | A003 | PCS | 188 | RECP | 2024000375 | 2020-07-30 00:00:00 | 3000 | 327 |
| AMZ_BSCS | A004 | PCS | 111 | RECP | 2024000375 | 2020-07-30 00:00:00 | 3000 | 216 |
| AMZ_BSCS | A005 | PCS | 104 | RECP | 2024000375 | 2020-07-30 00:00:00 | 3000 | 112 |
| AMZ_BSCS | A006 | PCS | 99 | RECP | 2024000375 | 2020-07-30 00:00:00 | 3000 | 13 |
| AMZ_BSCS | A007 | PCS | 13 | RECP | 2024000375 | 2020-07-30 00:00:00 | 3000 | 0 |
| AMZ_BSCS | A007 | PCS | 85 | RECP | 2024000200 | 2020-04-06 00:00:00 | 4000 | 3915 |
| AMZ_BSCS | A008 | PCS | 88 | RECP | 2024000200 | 2020-04-06 00:00:00 | 4000 | 3827 |
| AMZ_BSCS | A009 | PCS | 71 | RECP | 2024000200 | 2020-04-06 00:00:00 | 4000 | 3756 |
| AMZ_BSCS | A010 | PCS | 65 | RECP | 2024000200 | 2020-04-06 00:00:00 | 4000 | 3691 |
| AMZ_BSCS | A011 | PCS | 60 | RECP | 2024000200 | 2020-04-06 00:00:00 | 4000 | 3631 |
| AMZ_BSCS | A012 | PCS | 56 | RECP | 2024000200 | 2020-04-06 00:00:00 | 4000 | 3575 |
| AMZ_BSCS | A013 | PCS | 46 | RECP | 2024000200 | 2020-04-06 00:00:00 | 4000 | 3529 |
| AMZ_BSCS | A014 | PCS | 24 | RECP | 2024000200 | 2020-04-06 00:00:00 | 4000 | 3505 |
| AMZ_BSCS | A015 | PCS | 13 | RECP | 2024000200 | 2020-04-06 00:00:00 | 4000 | 3492 |
| AMZ_BSCS | A016 | PCS | 5 | RECP | 2024000200 | 2020-04-06 00:00:00 | 4000 | 3487 |
fiddle
7
A first attempt:
with stock(item_code, locn_code, item_uom, stock, rn) as (
select 'AMZ_BSCS', 'A001', 'PCS', 2280, 1 from dual union all
select 'AMZ_BSCS', 'A002', 'PCS', 205, 2 from dual union all
select 'AMZ_BSCS', 'A002', 'PCS', 188, 3 from dual union all
select 'AMZ_BSCS', 'A002', 'PCS', 111, 4 from dual union all
select 'AMZ_BSCS', 'A002', 'PCS', 104, 5 from dual union all
select 'AMZ_BSCS', 'A002', 'PCS', 99, 6 from dual union all
select 'AMZ_BSCS', 'A002', 'PCS', 98, 7 from dual union all
select 'AMZ_BSCS', 'A002', 'PCS', 88, 8 from dual union all
select 'AMZ_BSCS', 'A002', 'PCS', 71, 9 from dual union all
select 'AMZ_BSCS', 'A002', 'PCS', 65, 10 from dual union all
select 'AMZ_BSCS', 'A002', 'PCS', 60, 11 from dual union all
select 'AMZ_BSCS', 'A002', 'PCS', 56, 12 from dual union all
select 'AMZ_BSCS', 'A002', 'PCS', 46, 13 from dual union all
select 'AMZ_BSCS', 'A002', 'PCS', 24, 14 from dual union all
select 'AMZ_BSCS', 'A002', 'PCS', 13, 15 from dual union all
select 'AMZ_BSCS', 'A002', 'PCS', 5, 16 -- from dual union all
),
receipts(item_code, uom_code, txn_code, no, conf_dt, qty, rn) as (
select 'AMZ_BSCS', 'PCS', 'RECP', 2024000375, date '2024-07-30', 3000, 1 from dual union all
select 'AMZ_BSCS', 'PCS', 'RECP', 2024000200, date '2024-04-06', 4000, 2 from dual union all
select 'AMZ_BSCS', 'PCS', 'RECP', 2022999987, date '2024-04-06', 8000, 3 from dual union all
select 'AMZ_BSCS', 'PCS', 'RECP', 2022000084, date '2024-04-06', 2500, 4 from dual union all
select 'AMZ_BSCS', 'PCS', 'RECP', 2021000389, date '2024-04-06', 2000, 5 from dual union all
select 'AMZ_BSCS', 'PCS', 'RECP', 2021000001, date '2024-04-06', 3500, 6 from dual union all
select 'AMZ_BSCS', 'PCS', 'RECP', 2020000179, date '2024-04-06', 3500, 7 from dual union all
select 'AMZ_BSCS', 'PCS', 'RECP', 2019999995, date '2024-04-06', 3000, 8 from dual union all
select 'AMZ_BSCS', 'PCS', 'RECP', 2018999998, date '2024-04-06', 1000, 9 from dual union all
select 'AMZ_BSCS', 'PCS', 'RECP', 2010000042, date '2024-04-06', 6000, 10 from dual union all
select 'AMZ_BSCS', 'PCS', 'RECP', 2017000171, date '2024-04-06', 4500, 11 from dual union all
select 'AMZ_BSCS', 'PCS', 'RECP', 2017000001, date '2024-04-06', 2000, 12 from dual union all
select 'AMZ_BSCS', 'PCS', 'RECP', 2016000071, date '2024-04-06', 3500, 13 from dual union all
select 'AMZ_BSCS', 'PCS', 'RECP', 2015000177, date '2024-04-06', 1000, 14 from dual union all
select 'AMZ_BSCS', 'PCS', 'RECP', 2015000026, date '2024-04-06', 3000, 15 from dual union all
select 'AMZ_BSCS', 'PCS', 'RECP', 2014000117, date '2024-04-06', 1000, 16 from dual union all
select 'AMZ_BSCS', 'PCS', 'RECP', 2013000174, date '2024-04-06', 2000, 17 from dual union all
select 'AMZ_BSCS', 'PCS', 'RECP', 2013000022, date '2024-04-06', 2000, 18 from dual union all
select 'AMZ_BSCS', 'PCS', 'RECP', 2012000313, date '2024-04-06', 2000, 19 from dual union all
select 'AMZ_BSCS', 'PCS', 'RECP', 2012000089, date '2024-04-06', 2000, 20 from dual union all
select 'AMZ_BSCS', 'PCS', 'RECP', 2012000090, date '2024-04-06', 40, 21 from dual union all
select 'AMZ_BSCS', 'PCS', 'RECP', 2011000243, date '2024-04-06', 1960, 22 from dual union all
select 'AMZ_BSCS', 'PCS', 'RECP', 2011000131, date '2024-04-06', 500, 23 from dual union all
select 'AMZ_BSCS', 'PCS', 'RECP', 2011000049, date '2024-04-06', 2000, 24 from dual union all
select 'AMZ_BSCS', 'PCS', 'RECP', 2010000223, date '2024-04-06', 500, 25 from dual union all
select 'AMZ_BSCS', 'PCS', 'RECP', 2010000135, date '2024-04-06', 500, 26 from dual union all
select 'AMZ_BSCS', 'PCS', 'RECP', 2009000133, date '2024-04-06', 4000, 27 -- from dual union all
),
cstock(item_code, locn_code, item_uom, stock, rn, cum_stock) as (
select item_code, locn_code, item_uom, stock, rn,
sum(stock) over(partition by item_code order by rn)
from stock stk
),
creceipts(item_code, uom_code, txn_code, no, conf_dt, qty, rn, cum_qty) as (
select item_code, uom_code, txn_code, no, conf_dt, qty, rn,
sum(qty) over(partition by item_code order by rn)
from receipts
),
cte(
item_code, uom_code, txn_code, locn_code, no, conf_dt, qty, rn, stock, stk_rn,
report, balance,
priority, cnt
) as (
select rcp.item_code, rcp.uom_code, rcp.txn_code, stk.locn_code, rcp.no,
rcp.conf_dt, rcp.qty, rcp.rn,
stk.stock, stk.rn as stk_rn,
least(rcp.qty - stk.stock, 0) as report,
greatest(rcp.qty - stk.stock, 0) as balance,
1, 1
from creceipts rcp
join cstock stk on rcp.item_code = stk.item_code and rcp.rn = stk.rn
where rcp.rn = 1
union all
select
case when r.balance - rcp.stock > 0
then case when rcp.priority = 0 and rcp.cnt > 1 then null else r.item_code end
else case when rcp.priority = 0 and rcp.cnt > 1 then r.item_code else null end
end,
rcp.uom_code, rcp.txn_code, rcp.locn_code,
rcp.no,
rcp.conf_dt, rcp.qty, rcp.rn,
case when least(r.balance - rcp.stock, 0) < 0 then -least(r.balance - rcp.stock, 0) else rcp.stock end,
rcp.stk_rn,
- least(r.balance - rcp.stock, 0),
case when least(r.balance - rcp.stock, 0) < 0
then rcp.qty + least(r.balance - rcp.stock, 0)
else greatest(r.balance - rcp.stock, 0)
end,
rcp.priority, rcp.cnt
from cte r,
lateral (
select
count(distinct no) over() as cnt,
case when rcp.rn = r.rn then 1 else 0 end as priority,
rcp.item_code, rcp.uom_code, rcp.txn_code, stk.locn_code, rcp.no, rcp.conf_dt, rcp.qty, rcp.rn, rcp.cum_qty,
stk.stock, stk.cum_stock, stk.rn as stk_rn
from creceipts rcp
join cstock stk on rcp.item_code = stk.item_code
where r.item_code = rcp.item_code -- and r.balance - stk.stock > 0
and (
(rcp.rn = r.rn or rcp.rn = r.rn + 1)
and r.stk_rn + 1 = stk.rn
)
) rcp
)
cycle item_code, rn, stk_rn set is_loop to 'Y' default 'N'
select item_code, uom_code, txn_code, locn_code, stock, no, conf_dt, qty, balance
from cte
where item_code is not null
;
Note that the line
AMZ_BSCS PCS RECP A002 13 2024000375 30/07/24 3000 0
is not in the result yet, not only because it’s annoying to generate but also because it then takes part to the recursion and a safe way to avoid that has to be defined.
AMZ_BSCS PCS RECP A001 2280 2024000375 30/07/24 3000 720
AMZ_BSCS PCS RECP A002 205 2024000375 30/07/24 3000 515
AMZ_BSCS PCS RECP A002 188 2024000375 30/07/24 3000 327
AMZ_BSCS PCS RECP A002 111 2024000375 30/07/24 3000 216
AMZ_BSCS PCS RECP A002 104 2024000375 30/07/24 3000 112
AMZ_BSCS PCS RECP A002 99 2024000375 30/07/24 3000 13
AMZ_BSCS PCS RECP A002 85 2024000200 06/04/24 4000 3915
AMZ_BSCS PCS RECP A002 88 2024000200 06/04/24 4000 3827
AMZ_BSCS PCS RECP A002 71 2024000200 06/04/24 4000 3756
AMZ_BSCS PCS RECP A002 65 2024000200 06/04/24 4000 3691
AMZ_BSCS PCS RECP A002 60 2024000200 06/04/24 4000 3631
AMZ_BSCS PCS RECP A002 56 2024000200 06/04/24 4000 3575
AMZ_BSCS PCS RECP A002 46 2024000200 06/04/24 4000 3529
AMZ_BSCS PCS RECP A002 24 2024000200 06/04/24 4000 3505
AMZ_BSCS PCS RECP A002 13 2024000200 06/04/24 4000 3492
AMZ_BSCS PCS RECP A002 5 2024000200 06/04/24 4000 3487