Insert into the table data from the interactive report (based on two tables)

Dears,

I have an interactive report (initial and illustrative) based on two tables, with a select lists as a filter, which dynamically refresh the report.

Report

SQL query:

select
co.SITE_NAME,
co.SUST_FORM,
sn.DC_VS_S12345,
co.COMMENTS,
co.FILE_NAME as COOMENT_FILE_NAME ,
sn.FILE_NAME as NUMBERS_FILE_NAME
from COMMENTS co
join SITE_NUMBERS sn on co.SITE_NAME = sn.SITE_NAME
where co.FILE_NAME = :P4_COMMENTS and sn.FILE_NAME = :P4_NUMBERS;

What I would like to achieve is to insert those filtered data from this 'initial' interactive report into the final, target table (let's say - FINAL_SUMMARY).

I have tried to create a process that after clicking on the button will add those data to the table, but I don't know how to pass the data from this report...

Could someone explain to me what would be the way to add this filtered, preview data to the target table?

Thanks in advance!

One way to handle this is to use the api APEX_EXEC.OPEN_QUERY_CONTEXT. This will allow you to open a cursor with the exact query that was executed by APEX for that region. I'd suggest reading this blog that describes how to do this for a facet search region, however, you can do this for an interactive report as well.

In that blog a pipelined function is used to re-run the same query for another component. For you solution that is not needed obviously - you just need the query.

Begin
    l_query := 'select
					co.SITE_NAME,
					co.SUST_FORM,
					sn.DC_VS_S12345,
					co.COMMENTS,
					co.FILE_NAME as COOMENT_FILE_NAME ,
					sn.FILE_NAME as NUMBERS_FILE_NAME
					from COMMENTS co
					join SITE_NUMBERS sn on co.SITE_NAME = sn.SITE_NAME
					where co.FILE_NAME = '||:P4_COMMENTS||' and sn.FILE_NAME = '||:P4_NUMBERS||';';
	
	APEX_COLLECTION.CREATE_COLLECTION_FROM_QUERY (
        p_collection_name => 'YOUR_COLLECTION_NAME', 
        p_query => l_query,
        p_generate_md5 => 'YES');
End;

USE the below query in your interactive report

SELECT C001 SITE_NAME, C002 SUST_FORM FROM APEX_COLLECTION WHERE COLLECTION_NAME = 'YOUR_COLLECTION_NAME;

https://docs.oracle.com/cd/E59726_01/doc.50/e39149/apex_collection.htm#AEAPI725

Create additional button (right beside two select list items) (let's call it P4_BTN_INSERT); its action will be "Submit".

Create a process which will insert data into the target table; set process' condition so that it fires when P4_BTN_INSERT button is pressed (if you don't process will fire whenever you set any select list item's value because it - I presume - also submits the page as you said that report is being automatically refreshed).

Process itself is simple; basically, you're reusing interactive report's source query:

insert into target_Table
  (site_name, sust_form, dc_vs_s12345, comments, cooment_File_name, numbers_file_name)
select
co.SITE_NAME,
co.SUST_FORM,
sn.DC_VS_S12345,
co.COMMENTS,
co.FILE_NAME as COOMENT_FILE_NAME ,
sn.FILE_NAME as NUMBERS_FILE_NAME
from COMMENTS co
join SITE_NUMBERS sn on co.SITE_NAME = sn.SITE_NAME
where co.FILE_NAME = :P4_COMMENTS and sn.FILE_NAME = :P4_NUMBERS;