Table Structure :
CREATE TABLE MATERIALS
(
ID NUMBER(10),
MATERIALS VARCHAR2(50),
UNIT_TYPE NUMBER(4)
CONSTRAINT PK_MATERIALS_ID PRIMARY KEY (ID);
CREATE TABLE PURCHASE_MATERIALS_M
(
BILL_NO NUMBER(30),
PURCHASE_DATE DATE,
SUPPLIER_ID NUMBER(10),
TOTAL_AMOUNT NUMBER(11,2),
DISCOUNT_PCT NUMBER(11,2),
DISCOUNT_AMOUNT NUMBER(11,2),
VAT_PCT NUMBER(11,2),
VAT_AMOUNT NUMBER(11,2),
NET_AMOUNT NUMBER(11,2),
PAID NUMBER(11,2) DEFAULT 0,
DUE NUMBER(11,2) DEFAULT 0,
ENTRY_BY VARCHAR2(100) DEFAULT USER,
ENTRY_DATE DATE DEFAULT SYSDATE,
UPDATE_BY VARCHAR2(100),
UPDATE_DATE DATE,
CONSTRAINT PK_PURCHASE_MATERIALS_BILL_NO PRIMARY KEY (BILL_NO)
);
CREATE TABLE PURCHASE_MATERIALS_D
(
ID NUMBER(10),
BILL_NO NUMBER(10),
PRODUCT_ID NUMBER(10), ----FK MATERIALS
UNIT_PRICE NUMBER(10),
QTY NUMBER(10),
AMOUNT NUMBER(11,2),
ENTRY_BY VARCHAR2(100) DEFAULT USER,
ENTRY_DATE DATE DEFAULT SYSDATE,
UPDATE_BY VARCHAR2(100),
UPDATE_DATE DATE,
CONSTRAINT PK_PURCHASE_MATERIALS_D PRIMARY KEY (ID),
CONSTRAINT FK_PURCHASE_MATERIALS_D_BILL_NO FOREIGN KEY (BILL_NO)
REFERENCES PURCHASE_MATERIALS_M (BILL_NO)
);
CREATE SEQUENCE SEQ_COLL_ID
start with 1
increment by 1
minvalue 0
maxvalue 100000
cycle;
==================================================
Step-1 :
At first page design on PURCHASE_MATERIALS_M table. (as like picture). My region name : "Purchase Collection".
Step-2 :
Create sub-region (Name : Add Item) and put the 3 new items (as like "P85_PRODUCT", "P85_QTY", "P85_UNIT_PRICE"). This is non database items.
P85_PRODUCT - Type - Select List (List from MATERIALS table)
P85_QTY - Type - Number field
P85_UNIT_PRICE - Type - Number field
Step-3 :
Create Daynamic Action on "P85_UNIT_PRICE".
Daynamic Action Name : Apex_Collection
Event : Change
Select Type : Items (P85_UNIT_PRICE)
Client-side Condition:
Type : Item is not null
For True Action: Execute Server-Side Code
PL/SQL Code:
BEGIN
if not apex_collection.collection_exists('PURCHASE') then
apex_collection.create_collection('PURCHASE');
end if;
apex_collection.add_member
(
p_collection_name => 'PURCHASE',
p_c001 => :P85_PRODUCT,
p_c002 => :P85_QTY,
p_c003 => :P85_UNIT_PRICE
);
END;
*** Item to Submit: You must select Collection items
(Like- P85_PRODUCT, P85_QTY, P85_UNIT_PRICE)
Step-4 :
Create new Region --> Classic Report (Like Name : "ITEM DETAILS")
SQL Query :
SELECT SEQ_ID SL#,C001 Product_name,
C002 Qty,C003 UNIT_PRICE, C002 * C003 TOTAL_UNIT_PRICE,
SEQ_ID ID_DELETE
FROM APEX_COLLECTIONS
WHERE COLLECTION_NAME='PURCHASE';
Step-5 :
Again True Dynamic action on "P85_UNIT_PRICE" for Refresh.
Action: Refresh
Selection Type : Region
Region : "ITEM DETAILS"
Again True Dynamic action on "P85_UNIT_PRICE" for Clear all items ("P85_PRODUCT", "P85_QTY", "P85_UNIT_PRICE") .
Action: Clear
Selection Type : Items
Item : P85_PRODUCT, P85_QTY, P85_UNIT_PRICE
Step-6 : Column wise total Calculation on Classic Report (Like Region Name : "ITEM DETAILS") :-
Go to Report region Attributes --> Report Sum Label: Total
(press ctrl button then select "P85_QTY", "P85_TOTAL_UNIT_PRICE")
And go to Advanced --> Compute Sum: On
Step-7 :
Again True Dynamic action on "P85_UNIT_PRICE" for Calculate "P85_TOTAL_AMOUNT". (In the middle "Refresh" and "Clear" Action)
Event: Change
Selection Type: Item(s)
Item(s): P85_UNIT_PRICE
Client-side Condition:
Type : Item is not null
Item : P85_UNIT_PRICE
True:
Action: Set Value
Settings :
Set Type: SQL Statement
SQL Statement:
SELECT sum(C002 * C003)
FROM APEX_COLLECTIONS
WHERE COLLECTION_NAME ='PURCHASE';
Settings:
Items to Submit: P85_UNIT_PRICE
Affected Elements:
Selection Type: Item(s)
Item(s): P85_TOTAL_AMOUNT
Step-8 : Save your Data Process (Delete by default process)
Process Name :
SAVE_DETAILS -- > PL/SQL Code:
BEGIN
IF SQL%NOTFOUND THEN
SELECT NVL(MAX(BILL_NO),1000)+1 INTO :P85_BILL_NO FROM PURCHASE_MATERIALS_M;
END IF;
if not apex_collection.collection_exists('PURCHASE') then
apex_collection.create_collection('PURCHASE');
end if;
insert into PURCHASE_MATERIALS_D(ID,BILL_NO,PRODUCT_ID,QTY,UNIT_PRICE,AMOUNT)
select SEQ_COLL_ID.NEXTVAL,:P85_BILL_NO,C001,C002,C003, C002 * C003
FROM APEX_COLLECTIONS
WHERE COLLECTION_NAME= 'PURCHASE';
commit;
apex_collection.truncate_collection(p_collection_name => 'PURCHASE');
END;
SAVE_MASTER :
UPDATE PURCHASE_MATERIALS_M SET
PURCHASE_DATE=:P85_PURCHASE_DATE,
SUPPLIER_ID=:P85_SUPPLIER_ID,
TOTAL_AMOUNT=:P85_TOTAL_AMOUNT,
DISCOUNT_PCT=:P85_DISCOUNT_PCT,
DISCOUNT_AMOUNT=:P85_DISCOUNT_AMOUNT,
VAT_PCT=:P85_VAT_PCT,
VAT_AMOUNT=:P85_VAT_AMOUNT,
NET_AMOUNT=:P85_NET_AMOUNT,
PAID=:P85_PAID,
DUE=:P85_DUE,
UPDATE_BY=:APP_USER,
UPDATE_DATE=SYSDATE
WHERE BILL_NO=:P85_BILL_NO;
IF SQL%NOTFOUND THEN
SELECT NVL(MAX(BILL_NO),1000)+1 INTO :P85_BILL_NO FROM PURCHASE_MATERIALS_M;
INSERT INTO PURCHASE_MATERIALS_M (BILL_NO,PURCHASE_DATE,SUPPLIER_ID,DISCOUNT_PCT,DISCOUNT_AMOUNT,TOTAL_AMOUNT,VAT_PCT,VAT_AMOUNT,NET_AMOUNT,PAID,DUE,ENTRY_BY, ENTRY_DATE)
VALUES (:P85_BILL_NO,:P85_PURCHASE_DATE,:P85_SUPPLIER_ID,:P85_DISCOUNT_PC,:P85_DISCOUNT_AMOUNT,:P85_TOTAL_AMOUNT,:P85_VAT_PCT,:P85_VAT_AMOUNT,:P85_NET_AMOUNT, :P85_PAID,:P85_DUE,:APP_USER,SYSDATE);
END IF;
----Process : clear
----type : clear Session sate
Step-9: Delete Collection Row
Create a New Item. (P85_SEQ_ID) And Create Dynamic Action.
Action Name : DELETE_COLLECTION_ROW
Event : Change
Item : P85_SEQ_ID
Action : Execute server-side code
Plsql Code :
APEX_COLLECTION.DELETE_MEMBER (P_COLLECTION_NAME => 'PURCHASE',
P_SEQ => :P85_SEQ_ID);
APEX_COLLECTION.RESEQUENCE_COLLECTION( P_COLLECTION_NAME => 'PURCHASE' );
Again Action : Refresh
Selection Type : Region
Region : Item Details
Select ID_DELETE Column
Type : Link
Target :
Type : URL
URL : javascript:$s("P85_SEQ_ID","#ID_DELETE#")
Link Text : <span class="fa fa-trash" aria-hidden="true"></span>
==============================================
Step-10 : All Calculation
Create Dynamic Action on "P85_DISCOUNT_PCT"
Event : Key Release
Action : Set Value
set type : PL/SQL Function Body
return NVL(:P85_TOTAL_AMOUNT,0)* NVL(:P85_DISCOUNT_PCT,0)/100;
Items to Submit :
P85_TOTAL_AMOUNT,P85_DISCOUNT_PCT
Affected Elements :
Item(s) : P85_DISCOUNT_AMOUNT
----------------------------------------------------
Create Dynamic Action on "P85_VAT_PCT"
Event : Key Release
Action : Set Value
set type : PL/SQL Function Body
return NVL(:P85_TOTAL_AMOUNT,0)* NVL(:P85_VAT_PCT,0)/100;
Items to Submit :
P85_TOTAL_AMOUNT,P85_VAT_PCT
Affected Elements :
Item(s) : P85_VAT_AMOUNT
-----------------------------------------------------
Create Dynamic Action on "P85_VAT_AMOUNT"
Event : Change
Action : Set Value
set type : PL/SQL Function Body
RETURN NVL(:P85_TOTAL_AMOUNT,0)- NVL(:P85_DISCOUNT_AMOUNT,0)+NVL(:P85_VAT_AMOUNT,0);
Items to Submit :
P85_TOTAL_AMOUNT,P85_DISCOUNT_AMOUNT,P85_VAT_AMOUNT
Affected Elements :
Item(s) : P85_NET_AMOUNT
------------------------------------------------------
Create Dynamic Action on "P85_PAID"
Event : Key Release
Action : Set Value
set type : PL/SQL Function Body
RETURN NVL(:P85_NET_AMOUNT,0)-NVL(:P85_PAID,0);
Items to Submit :
P85_NET_AMOUNT,P85_PAID
Affected Elements :
Item(s) : P85_DUE
----------------------------------------------------------
Enjoy Oracle Apex .....