Multiple Grid Region Collection data SAVE to Another Table

 


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);

Step 5:
=====

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.






Post a Comment

Previous Post Next Post