Oracle Apex Collections (Demo Orders)

COLLECTION SELECT STATEMENT :

SELECT * FROM APEX_COLLECTIONS WHERE COLLECTION_NAME='ORDER';

DYNAMIC ACTION :
:P20_ORDER_TIMESTAMP:=SYSDATE;

SELECT LIST_PRICE INTO :P20_UNIT_PRICE FROM DEMO_PRODUCT_INFO WHERE PRODUCT_ID = :P20_PRODUCT_ID;

PROCESS_COLLECTION_ADD (SUBMIT) :

if not apex_collection.collection_exists ('ORDER') then
apex_collection.create_collection('ORDER');
end if;
apex_collection.add_member (p_collection_name => 'ORDER',
p_c001 => :P20_ORDER_ID,
p_c002 => :P20_PRODUCT_ID,
p_c003 => :P20_UNIT_PRICE,
p_c004 => :P20_QUANTITY,
p_c005 => NVL(:P20_UNIT_PRICE,0) * NVL(:P20_QUANTITY,0)
);

PROCESS_COLLECTION_TRUNCATE :

Begin
APEX_COLLECTION.TRUNCATE_COLLECTION(
p_collection_name => 'ORDER');
End;

PROCESS_ORDERS :

UPDATE DEMO_ORDERS SET
CUSTOMER_ID=:P20_CUSTOMER_ID,
ORDER_TIMESTAMP=:P20_ORDER_TIMESTAMP
WHERE ORDER_ID=:P20_ORDER_ID;
IF SQL%NOTFOUND THEN
SELECT NVL(MAX(ORDER_ID),0)+1 INTO :P20_ORDER_ID FROM DEMO_ORDERS;
INSERT INTO DEMO_ORDERS(ORDER_ID,CUSTOMER_ID,ORDER_TIMESTAMP)
VALUES (:P20_ORDER_ID,:P20_CUSTOMER_ID,:P20_ORDER_TIMESTAMP);
END IF;

PROCESS_ORDER_ITEMS :

FOR r in (select SEQ_ID ORDER_ITEM_ID,C001 ORDER_ID,C002 PRODUCT_ID,C003 UNIT_PRICE,C004 QUANTITY FROM APEX_COLLECTIONS WHERE COLLECTION_NAME='ORDER') LOOP
UPDATE DEMO_ORDER_ITEMS SET
PRODUCT_ID=R.PRODUCT_ID,
UNIT_PRICE=R.UNIT_PRICE,
QUANTITY=R.QUANTITY
WHERE ORDER_ID=:P20_ORDER_ID
AND ORDER_ITEM_ID=R.ORDER_ITEM_ID;
IF SQL%NOTFOUND THEN
INSERT INTO DEMO_ORDER_ITEMS (ORDER_ID,ORDER_ITEM_ID,PRODUCT_ID,UNIT_PRICE,QUANTITY)
VALUES (:P20_ORDER_ID,R.ORDER_ITEM_ID,R.PRODUCT_ID,R.UNIT_PRICE,R.QUANTITY);
END IF;
END LOOP;

PROCESS_TOTAL :

UPDATE DEMO_ORDERS SET ORDER_TOTAL = (SELECT SUM(UNIT_PRICE*QUANTITY) FROM DEMO_ORDER_ITEMS WHERE ORDER_ID=:P20_ORDER_ID) WHERE ORDER_ID=:P20_ORDER_ID;

PROCESS_CLEAR :

CLEAR_SESSION_STATE

Begin
APEX_COLLECTION.TRUNCATE_COLLECTION(
p_collection_name => 'ORDER');
End;