Oracle Apex Collection

How To Create A Complete Form By Oracle Apex Collection

Topic Introduction: In this tutorial, we will discuss what is Oracle Apex Collection. How to build a collection, store data on the collection, Show data on a report by SQL Query, Delete & Update Collection Data and Insert data on the targeted table from the Collection. we will see a complete form by using Oracle Apex Collection.

APEX_COLLECTION is a package that comes with Oracle Apex. It is a most powerful feature of oracle apex that can be used in many ways on oracle apex application building. This act as temporary storage for a user session and it must have a name. A row is added as a member and each member have a sequence number. Each Apex Collection can have 50 varchar2 column (c001 -> c0050), 5 number column (n001->n005), 5 date column (d001->d005), 1 CLOB column (clob001), and 1 BLOB column (blob001). 

 

Step to work with apex collection on Oracle Apex.

1. Create a collection.

2. Check if a collection already exists.

3. Truncate a collection.

4. Populate it with data using PL/SQL.

5. Query a collection.

6. Update Collection Data

7. Insert data to Database Table From the Collection

Creating Collection

Create a Dynamic Action On Page Load

Add Action 1 action type JavaScript 

$('#P1119_MRND_ITM_ITEM_ID').val(null).trigger('change');

$('#P1119_MRND_QTY').val(null);

$('#P1119_MRND_RATE').val(null);

$('#P1119_MRN_VATAMT').val(null);

$('#P1119_MRN_DISAMT').val(null);

$('#P1119_MRN_BASEVAL').val(null);

$('#P1119_MRN_ROUNDAMT').val(0);

$('#P1119_MRN_NETVAL').val(null);

Add Action 2 action type PL/SQL

 IF APEX_COLLECTION.COLLECTION_EXISTS (

        p_collection_name => 'PQMRNM') THEN

APEX_COLLECTION.DELETE_COLLECTION (

    p_collection_name => 'PQMRNM');

    END IF;

IF not APEX_COLLECTION.COLLECTION_EXISTS (p_collection_name => 'PQMRNM') THEN

        APEX_COLLECTION.CREATE_COLLECTION(p_collection_name => 'PQMRNM');

        END IF;

Add Action 3 action type JavaScript 

apex.region("ITEMINFO").refresh();
apex.event.trigger( "#ITEMINFO", "apexrefresh" );

Add Member on Collection

Create a Dynamic Action On Add Button

Add Action 1 action type PL/SQL

DECLARE 

i number;

v_qty number:=1;

V_ITMQTY NUMBER;

BEGIN

begin

SELECT COUNT(c001)

INTO V_ITMQTY

from apex_collections

where collection_name='PQMRNM'

AND c001=:P1119_MRND_ITM_ITEM_ID;

exception when no_data_found then null; when others then null;

end;

IF APEX_COLLECTION.COLLECTION_EXISTS (p_collection_name => 'PQMRNM') THEN

--=================

if :P1119_MRND_ITM_ITEM_ID is null or :P1119_MRND_QTY IS NULL  or :P1119_MRND_RATE IS NULL then     

raise_application_error(-20015,'Please select Item & give Quantity and Rate Value.');

end if;

--==================

IF V_ITMQTY > 0 THEN

raise_application_error(-20015,'You can not add same Item multiple time.');

end if;

--==================

if :P1119_MRND_ITM_ITEM_ID is not null

and :P1119_MRND_QTY is not null

and :P1119_MRND_RATE is not null

then

  for i in 1..v_qty loop

APEX_COLLECTION.ADD_MEMBER(

    p_collection_name =>'PQMRNM',

    p_c001 =>:P1119_MRND_ITM_ITEM_ID,

   

    p_n001      =>i,  

    p_n002      =>:P1119_MRND_RATE,

    p_n003      =>:P1119_MRND_QTY

);

--v_stt:=v_stt+1;

END LOOP;

end if;

END IF;

END;

Add Action 2 action type JavaScript 

apex.region("ITEMINFO").refresh();

apex.event.trigger( "#ITEMINFO", "apexrefresh" );

Add Action 3 action type JavaScript 

$('#P1119_MRND_ITM_ITEM_ID').val(null).trigger('change');

$('#P1119_MRND_QTY').val(null);
$('#P1119_MRND_RATE').val(null);

$('#P1119_MRN_ROUNDAMT').val(0);

Create a region for showing Item Information from the Collection static id is ITEMINFO

SELECT c001 ITEMCD,ITEM_NAME,n002 RATE,n003 QTY,n002*n003 AMT

,SEQ_ID

from apex_collections COL,ITEM  ITM

where collection_name='PQMRNM'

AND COL.c001=ITM.ID

Clear Member from  Apex Collection

Create a Dynamic Action On Add Button

Add Action 1 action type PL/SQL

DECLARE

BEGIN
IF APEX_COLLECTION.COLLECTION_EXISTS(
p_collection_name =>'PQMRNM') THEN
APEX_COLLECTION.TRUNCATE_COLLECTION(
        p_collection_name => 'PQMRNM');
END IF;

END;

Add Action 2 action type JavaScript 

apex.region("ITEMINFO").refresh();

apex.event.trigger( "#ITEMINFO", "apexrefresh" );

Add Action 3 action type JavaScript 

$('#P1119_MRND_ITM_ITEM_ID').val(null).trigger('change');

$('#P1119_MRND_QTY').val(null);
$('#P1119_MRND_RATE').val(null);
$('#P1119_MRN_VATAMT').val(null);
$('#P1119_MRN_DISAMT').val(null);
$('#P1119_MRN_BASEVAL').val(null);
$('#P1119_MRN_ROUNDAMT').val(0);

$('#P1119_MRN_NETVAL').val(null);

Update Member

select c001, -- Item Name, 

        n002, -- Rate, 

        n003, -- Qty

into :P1119_MRND_ITM_ITEM_ID, :P1119_MRND_RATE, :P1119_MRND_QTY

from apex_collections

where collection_name = 'PQMRNM' AND seq_id = to_number(:P1119_MEMBER_SEQ_ID);

APEX_COLLECTION.UPDATE_MEMBER (

        p_collection_name => 'PQMRNM',

        p_seq => :P1119_MEMBER_SEQ_ID,

        p_c001 => :P1119_MRND_ITM_ITEM_ID,

        p_n002 => :P1119_MRND_RATE,

        P_n003 => :P1119_MRND_QTY

);

The process of Insert Data into Database Table

DECLARE

V_CD VARCHAR2(16);

V_SYS_COMPANY_ID NUMBER;

V_SYS_BRANCH_ID NUMBER;

V_MID number;

V_DID number;

V_LOC NUMBER;

V_SL NUMBER;

V_FR VARCHAR2(4):=TO_CHAR(sysdate,'RRRR');

V_DATE DATE:=TO_DATE(to_char(sysdate,'DD-MON-RR HH24:MI:SS'),'DD-MON-RR HH24:MI:SS');

V_TRCD VARCHAR2(4):='MRN';

BEGIN

Select NVL(max(ID),0)+1 into V_MID from PURCH_MASTER;

IF :P1119_TR_SUPPLIER_ID is not null  then 

INSERT INTO PURCH_MASTER(ID,MRN_DATE,TR_SUPPLIER_ID,MRN_REMARKS

,MRN_TR_MODE)

VALUES (V_MID,V_DATE,:P1119_TR_SUPPLIER_ID,:P1119_MRN_REMARKS

,:P1119_MRN_TR_MODE

);

for x in (

   

SELECT c001 ITEMCD,n002 RATE,n003 QTY,n002*n003 AMT

,SEQ_ID

from apex_collections 

where collection_name='PQMRNM' 

    

    )  loop

       insert into PURCH_DETAIL(  

       PURCH_MASTER_ID,

       MRND_DATE,

       MRND_ITM_ITEM_ID,

       MRND_QTY,

       MRND_RATE,

       MRND_LINE_TOTAL

    )        

         values(

       V_MID,

       V_DATE,

       x.ITEMCD,

       x.QTY,

       x.RATE,

       x.AMT

         );

end loop;

COMMIT;

----------------End Save Details Data --------------------------

APEX_APPLICATION.G_PRINT_SUCCESS_MESSAGE :='Success...';

ELSE

APEX_APPLICATION.G_PRINT_SUCCESS_MESSAGE :='Fail.....';

END IF;

EXCEPTION

WHEN OTHERS THEN

RAISE_APPLICATION_ERROR(-20002,'Can not insert!');

APEX_COLLECTION.DELETE_COLLECTION (

    p_collection_name => 'PQMRNM');

COMMIT;

END;