Step 1:
=====
I want to show data conditionally, so set my Parameter.
P11_TYPE_MST
P11_TYPE_DTL
P11_TYPE_ORG
P11_ACTION --- this is working G for "Grant" or R for "Revoke" (Insert or Delete)
Step 2:
=====
Insert data into another table from the two table combinations data.
So I want to create two grid regions. Input from the apex collection base data.
1st region:
Name: Type Region
Type: Interactive Grid
SQL:
SELECT
seq_id as XSL,
c001 as ID,
c002 as Type,
c003 as Code,
c004 as Description
FROM apex_collections
WHERE collection_name = 'XCODE_COLLECTION';
-----------------------------
2nd region:
Name: ORG Region
Type: Interactive Grid
SQL:
SELECT
seq_id as SL,
c001 as XBID,
c002 as ZORG_CODE,
c003 as XSHORT,
c004 as ZORG
FROM apex_collections
WHERE collection_name = 'ZBUSINESS_COLLECTION';
Step 3:
=====
Create a Button for the input region data load.
Name: Data_Load
Dynamic action: InsertToCollection
Action: Execute Server-side Code
PLSQL:
declare
cursor c1 is -- 2nd region
select XBID , XATTRIBUTE1 AS ZORG_CODE, ZORG, XSHORT
from zbusiness
where ZACTIVE='1'
AND ZORGTYP=:P11_TYPE_ORG
order by 1;
cursor c2 is -- 1st region
select XID, ACCOUNT_HEAD_NAME AS XTYPE, ACCOUNT_USAGES AS XCODE, NVL(ACCOUNT_SOURCE,'NONE') AS XDESCDET
from FAD_COA
where ZACTIVE='1'
and ACCOUNT_TYPE=:P11_TYPE_MST
and (XID=:P11_TYPE_DTL OR :P11_TYPE_DTL IS NULL )
order by 1;
begin
/**************** Organization Information *************/
if apex_collection.collection_exists('ZBUSINESS_COLLECTION') then
APEX_COLLECTION.DELETE_COLLECTION ('ZBUSINESS_COLLECTION');
apex_collection.create_collection('ZBUSINESS_COLLECTION');
elsif not apex_collection.collection_exists('ZBUSINESS_COLLECTION') then
apex_collection.create_collection('ZBUSINESS_COLLECTION');
end if;
for r1 in c1 loop
apex_collection.add_member(
p_collection_name => 'ZBUSINESS_COLLECTION',
p_c001 => r1.XBID,
p_c002 => r1.ZORG_CODE,
p_c003 => r1.XSHORT,
p_c004 => r1.ZORG
);
end loop;
/**************** Lookup Value *************/
if apex_collection.collection_exists('XCODE_COLLECTION') then
APEX_COLLECTION.DELETE_COLLECTION ('XCODE_COLLECTION');
apex_collection.create_collection('XCODE_COLLECTION');
elsif not apex_collection.collection_exists('XCODE_COLLECTION') then
apex_collection.create_collection('XCODE_COLLECTION');
end if;
for r2 in c2 loop
apex_collection.add_member(
p_collection_name => 'XCODE_COLLECTION',
p_c001 => r2.XID,
p_c002 => r2.XTYPE,
p_c003 => r2.XCODE,
p_c004 => r2.XDESCDET
);
end loop;
exception when others then null;
end;
Items to Submit: P11_TYPE_ORG,P11_TYPE_MST,P11_TYPE_DTL
Again Dynamic Action on Button for Refresh Your two Grid Regions.
Then Click Your "Data_Load" button and see data show.
Step 4:
=====
Select 1st Region (Type Region) and Create Apex Item for selected all PK_ID Store this.
Item Name: P11_ALL_MST_ID
Again select 1st Region then Create Dynamic action.
Action Name: MST_SELECTED_ROW
Action: Execute JavaScript Code
var i, i_empids = ":", i_empid,
model = this.data.model;
for ( i = 0; i < this.data.selectedRecords.length; i++ ) {
i_empid = model.getValue( this.data.selectedRecords[i], "ID"); // PK ID
i_empids += model.getValue( this.data.selectedRecords[i], "ID") + ":"; // PK ID
}
apex.item( "P11_ALL_MST_ID" ).setValue (i_empids);
-----------------------------------------------------------------
Select 2nd Region (ORG Region) and Create Apex Item for selected all PK_ID Store this.
Item Name: P11_ALL_ORG_MST_ID
Again select 2nd Region then Create Dynamic action.
Action Name: ORG_MST_SELECTED_ROW
Action: Execute JavaScript Code
var i, i_empids = ":", i_empid,
model = this.data.model;
for ( i = 0; i < this.data.selectedRecords.length; i++ ) {
i_empid = model.getValue( this.data.selectedRecords[i], "XBID"); // PK ID
i_empids += model.getValue( this.data.selectedRecords[i], "XBID") + ":"; // PK ID
}
apex.item( "P11_ALL_ORG_MST_ID" ).setValue (i_empids);
=====
Create a Button for Saving Collection Data.
Name: Save
Dynamic action: Collection_save
Action: Execute Server-side Code
PLSQL:
declare
vcnt number:=0;
verror varchar2(500);
verrornum varchar2(500);
verrormsg varchar2(500);
vipadr varchar2(500);
cursor c1 is --- 2nd Region
select * from ZBUSINESS
where xbid in
(SELECT t.Column_Value AS XBID
FROM TABLE(Apex_String.Split(RTRIM(LTRIM(:P11_ALL_ORG_MST_ID, ':'), ':'),
':')) t);
cursor c2 is --- 1st region
SELECT XID, ACCOUNT_HEAD_NAME AS XTYPE, ACCOUNT_USAGES AS XCODE, NVL(ACCOUNT_SOURCE,'NONE') AS XDESCDET
from FAD_COA
where xid in
(SELECT t.Column_Value AS XID
FROM TABLE(Apex_String.Split(RTRIM(LTRIM(:P11_ALL_MST_ID, ':'), ':'),
':')) t);
begin
/* ----Grant/Save privilege ---- */
if :P11_ACTION='G' then
for r1 in c1 loop
for r2 in c2 loop
begin
select count(*)
into vcnt
from FAD_COA_ASSIGN
where xentity_id=r1.xbid
and ACCOUNT_HEAD_ID=r2.xid ;
exception when others then null;
end;
if vcnt=0 then ---- 0 that means this data is new, then run insert statements
Insert into FAD_COA_ASSIGN
( xentity_id , xentity_type , ACCOUNT_HEAD_ID , ztime ,zemail )
values (r1.xbid, r1.zorgtyp , r2.xid ,sysdate,:app_user);
end if;
end loop;
end loop;
/* ----Revoke/delete privilege ---- */
elsif :P11_ACTION='R' then ---- if you want delete then setect "Revoke" = R
for r1 in c1 loop
for r2 in c2 loop
delete from FAD_COA_ASSIGN
where xentity_id=r1.xbid and ACCOUNT_HEAD_ID=r2.xid ;
end loop;
end loop;
end if;
exception when others then
verrornum :=sqlcode;
verrormsg :=sqlerrm;
vipadr :=owa_util.get_cgi_env('X-FORWARDED-FOR');
generate_errorlog_prc (:g_zid,:g_xbid,'ERROR','DA: InsertUpdate',verrornum ,verrormsg , :P18046_XVALUE, :APP_USER, :APP_ID, :APP_PAGE_ID, :SESSION, vipadr, null);
end;
Items to Submit:
P11_ALL_ORG_MST_ID,P11_ALL_MST_ID,P11_ACTION,P18046_XVALUE
Step 6:
=====
Create a Classic report for showing Inserting Data.
Region Name: Combination Inserting Data
SQL Query:
select xbid , ZORG , ZORGTYP, XID, XCODE , XDESCDET
from Table_Name
where ACCOUNT_TYPE=:P11_TYPE_MST
and (xid=:P11_TYPE_DTL OR :P11_TYPE_DTL IS NULL )
*** Create a Dynamic Action on "Save" Button For "Combination Inserting Data" Region Refresh.
*** Create a Dynamic Action on "Load_Data" Button For "Combination Inserting Data" Region Refresh. Then show who the data was inserted previously.