FREE AI-Enhanced Online Oracle Compiler

Untitled*

You must execute the code again to save those values in the database.

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';

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
);

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
);

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);

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;

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;

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
LCS_ITEM_CODE	LCS_LOCN_COD	ITEM_UOM_COD	STOCK_QTY	SL_TXN_CODE	SL_NO	SL_CONF_DT	SL_QTY	BALANCE
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