DECLARE
CURSOR c
IS
SELECT xps.ROWID ROW_ID
, xps.PHX_ORDER_NUMBER
, xps.PHX_SHIPPING_GROUP_ID
, xps.ITEM_ID
, xps.NCMS_ORDER_NUMBER
, xps.STATUS_FLAG
, xps.TRACKING_NUMBER
, xps.PROCESSED_FLAG
FROM xxont_phx_status_t xps, oe_order_headers_all ooh
WHERE 1 = 1
AND xps.ncms_order_number = ooh.order_number
AND ooh.creation_Date >= TO_DATE ('26-JUL-2019 09:00:00', 'DD-MON-YYYY, HH24:MI:SS');
TYPE phx_array_type IS TABLE OF c%ROWTYPE;
phx_array phx_array_type;
BEGIN
OPEN c;
FETCH c BULK COLLECT INTO phx_array;
CLOSE c;
-- Delete
FORALL x IN phx_array.FIRST .. phx_array.LAST
DELETE FROM xxont_phx_status_t pst
WHERE pst.ROWID = phx_array (x).ROW_ID;
-- Insert into base table
FORALL x IN phx_array.FIRST .. phx_array.LAST
INSERT INTO xxont_phx_status_t (PHX_ORDER_NUMBER
, PHX_SHIPPING_GROUP_ID
, ITEM_ID
, NCMS_ORDER_NUMBER
, STATUS_FLAG
, TRACKING_NUMBER
, PROCESSED_FLAG)
VALUES (phx_array (x).PHX_ORDER_NUMBER
, phx_array (x).PHX_SHIPPING_GROUP_ID
, phx_array (x).ITEM_ID
, phx_array (x).NCMS_ORDER_NUMBER
, phx_array (x).STATUS_FLAG
, phx_array (x).TRACKING_NUMBER
, phx_array (x).PROCESSED_FLAG);
COMMIT;
END;
/