Aging distribution by location using receipt transactions

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:

fiddle