Imaginations are the key of special requirements, specially when are you working with dynamic customers who are always open for broad mind suggestions.
Working with apex collection gives you opportunity to store values in session without save in database until last hit on SAVE or CREATE button of record. Fetch data or create records and store in apex_collections, do manipulations with data as per steps user go through. When user is happy with the record set then only hit Oracle table and save data. For more information on apex_collection click here.
Step 1: Create master page as normal and child page as popup.
Master page creates on Table1 DML process. Child page will create on Interactive Grid based on apex collection.
Step 2: Initialize collection and add members in collection
APEX_COLLECTION.CREATE_OR_TRUNCATE_COLLECTION(
p_collection_name => 'SAMPLE_NAME');
for i in (select ID1, ID2, NUMBER1, DATE1, CHARACTER1 FROM TABLE2 where ID2 = :PX_ITEM) loop
APEX_COLLECTION.ADD_MEMBER(
p_collection_name => 'SAMPLE_NAME',
p_n001 => i.ID1,
p_n002 => i.ID2,
p_n003 => i.NUMBER1,
p_d001 => i.DATE1,
p_c001 => i.CHARACTER1);
end loop;
ID1 is primary key of column TABLE2 table,
ID2 is foreign key of TABLE2,
NUMBER1 is number column in TABLE2 table,
DATE1 is date column in TABLE2 table,
CHARACTER1 is varchar column in TABLE2.
Create collection with name "SAMPLE_NAME", add member by using APEX_COLLECTION.ADD_MEMBER procedure within for loop. So, if data already present in TABLE2 then load data in apex collection.
Step 3: Create Interactive Grid on Child page (popup)
Step 3: Create Interactive Grid on Child page (popup)
By using following query, create interactive grid.
select seq_id, n001, n002, n003, d001, c001 from apex_collections where collection_name = 'SAMPLE_NAME'
Make sure Enable Yes option in Edit section of Attributes of interactive grid
Also enable Primary Key as "Yes" for "SEQ_ID" column
Step 4: Customize Interactive Grid - Automatic Row process (After Submit)
Choose "Target Type" as "PL/SQL Code".
In PL/SQL code, all three operation are required (insert / update / delete)
Note: If primary key columns get populated by a trigger or in the PL/SQL code, it is a requirement that the new values are assigned to the bind variables of the primary key columns, because Application Express uses those values to fetch the row after an insert or update.
Use below code to populate, update and delete records of apex_collection
begin
case :APEX$ROW_STATUS
when 'C' then
:SEQ_ID := APEX_COLLECTION.ADD_MEMBER(
p_collection_name => 'SAMPLE_NAME',
p_n001 => :N001,
p_n002 => :N002,
p_n003 => :N003,
p_d001 => :D001,
p_c001 => :C001);
when 'U' then
APEX_COLLECTION.UPDATE_MEMBER (
p_collection_name => 'SAMPLE_NAME',
p_seq => :SEQ_ID,
p_n001 => :N001,
p_n002 => :N002,
p_n003 => :N003,
p_d001 => :D001,
p_c001 => :C001);
when 'D' then
APEX_COLLECTION.DELETE_MEMBER (
p_collection_name => 'SAMPLE_NAME',
p_seq => :SEQ_ID);
end case;
end;
Here, "I" represents insert, "U" represents update and "D" represent delete DML operations.
Set "Lock Row" to "No" in Settings
Step 5: At master page add PL/SQL process (After Submit => After DML operation of TABLE1 process)
BEGIN
--Delete records which are not present in collection
delete TABLE2 where ID2 = :PX_ID1 --Primary Item of TABLE1
AND ID1 not in
(select n001
from apex_collections
where collection_name = 'SAMPLE_NAME'
AND n001 is not null);
--Insert records which are new in collection
insert into TABLE2 (ID2, DATE1, NUMBER1, CHARACTER1)
(select :PX_ID1, trunc(d001), n005, c001 from apex_collections where
collection_name = 'SAMPLE_NAME' and
n001 is null);
--Update records
for i in ( select fe.ID1, coll.d001, coll.n005, coll.c001 from
(select * from TABLE2 where ID2 = :PX_ID1 ) fe,
(select * from apex_collections where collection_name = 'SAMPLE_NAME' and n002 = :PX_ID1 ) coll
where fe.ID1 = coll.n001
and fe.ID2 = coll.n002
and (trunc(fe.DATE1) <> trunc(coll.d001)
or hours_detail <> coll.n005
or who <> coll.c001)) loop
update TABLE2
set DATE1 = trunc(i.d001),
NUMBER1 = i.n005,
CHARACTER1 = i.c001
where ID1 = i.ID1;
end loop;
END;
Add above code for save data when user hit "Create" or "Save" button.
Author: Lovneesh Kumar
Author: Lovneesh Kumar
