SQL Queries to Track FAH Import Accounting Transactions process ID , Create Accounting for Data Set Process ID and Others

Query 1: Link Import Accounting Transactions with Create Accounting for Data Set

select * from FUSION_ORA_ESS.REQUEST_history where requestid in (select requestid from FUSION_ORA_ESS.REQUEST_PROPERTY where name = 'submit.argument23' and value = 'XLA_LOAD_<Import Accounting Transactions request ID>')

Query 2: Link Create Accounting for Data Set with Create Accounting: Subprocess and Post Subledger Journal Entries

select decode(name,'apps.fin.xla.xlafsnrpt.childRequests','Create Accounting Subprocess','apps.fin.xla.xlafsnrpt.gltparent','Post Subledger Journal Entries'),value from ESS_REQUEST_PROPERTY where requestid = <Create Accounting for Data Set request ID from Query 1> and name in ('apps.fin.xla.xlafsnrpt.gltparent','apps.fin.xla.xlafsnrpt.childRequests')

Query 3. Link Post Subledger Journal Entries with Post Subledger Journal Entries: Subprocess

select DECODE(name,'apps.fin.xla.xlagltrn.childRequests','Post Subledger Journal Entries Subprocess') jobname,value from ess_request_property where requestid = <Post Subledger Journal Entries request ID from Query 2> and name ='apps.fin.xla.xlagltrn.childRequests'

Query 4. Link Create Accounting: Subprocess with Update Subledger Accounting Balances

select prop1.requestid XLABABUP, hist1.requestid CASUBPROCESS from fusion_ora_ess.request_history hist1 , fusion_ora_ess.request_property prop1, fusion_ora_ess.request_history hist2 , fusion_ora_ess.request_property prop2 WHERE prop2.name = ('submit.argument8') and prop2.requestid = <a Create Accounting Subprocess request     ID from Query 2> and hist1.requestid = prop2.requestid and hist1.name = 'XLAFSNAPENG' and prop1.name = ('submit.argument3') and prop1.requestid = hist2.requestid and prop1.value = prop2.value and hist2.name = 'XLABABUP'

Query 5. Link Import Accounting Transactions with Create Accounting for Data Set, Post Subledger Journal Entries and Update Subledger Accounting Balances 

select base4.zipfile, substr(base4.loadfile,1,16) loadfile,base4.cadatasetid cadataset,

(select count(1) from fusion.xla_fsn_events_int where parent_request_id <> -1

and report_request_id = cadatasetid) event_count,

(select value from fusion_ora_ess.request_property

where requestid = cadatasetid

and name = 'submit.argument3') application_id,

(select value from fusion_ora_ess.request_property

where requestid = cadatasetid

and name = 'submit.argument4') ledger_id,

substr(replace(base4.casubprocess_ids,',','-'),1,300) casubprocess,

to_number(base4.accounting_batch_id) accountingbatchid,

to_number(base4.gltp) GLTParentProcessId,

to_number(base4.XLABABUP) XLABABUP,

prop5.value gltw ,

hist3.processstart importstarttime,

hist3.processend importendtime,

hist3.processend - hist3.processstart importspendtime,

base4.castart,

base4.caend,

base4.caend -base4.castart,

hist2.processstart xlababup_start_time,

hist2.processend xlababup_end_time,

hist2.processend - hist2.processstart xlababupspendtime,

nvl(hist2.processend,base4.caend) - hist3.processstart total_time_taken

from fusion_ora_ess.request_property prop5,

fusion_ora_ess.request_history hist2,

fusion_ora_ess.request_history hist3,

(select base3.*,prop4.value gltp from fusion_ora_ess.request_property prop4, (SELECT base2.*,

prop3.requestid XLABABUP

FROM fusion.ess_request_property prop3,(

SELECT base.loadfile,

base.zipfile,

base.cadatasetid,

base.castart,

base.caend,

base.process_ids casubprocess_ids,

value accounting_batch_id

FROM fusion.ess_request_property prop2,

(SELECT zipfile,

loadfile,

cadatasetid,

castart,

caend,

process_name,

process_ids

FROM

(SELECT base0.*,

--hist.requestid cadatasetid ,

DECODE(prop.name,'apps.fin.xla.xlafsnrpt.childRequests','Create Accounting

Subprocesses' ) process_name,

prop.value process_ids

FROM fusion.ESS_REQUEST_PROPERTY prop,

fusion_ora_ess.request_history hist,

(SELECT hist0.requestid cadatasetid ,

prop0.value loadfile,

hist0.processstart castart,

prop1.value zipfile,

hist0.processend caend

FROM FUSION_ORA_ESS.REQUEST_history hist0,

FUSION_ORA_ESS.REQUEST_PROPERTY prop0,

FUSION_ORA_ESS.REQUEST_PROPERTY prop1

WHERE hist0.requestid = prop0.requestid

AND prop0.name = 'submit.argument23'

AND prop0.value LIKE 'XLA_LOAD_%'

and prop1.requestid = substr(prop0.value,10,length(prop0.value))

and prop1.name = 'submit.argument2'

AND TRUNC(hist0.processstart)> to_date(<Start Date of the Import Accounting

Transactions request>,'dd-MON-YYYY')

) base0

WHERE prop.requestid = hist.requestid

AND prop.name IN ('apps.fin.xla.xlafsnrpt.childRequests')

AND hist.requestid = base0.cadatasetid

)

) base

WHERE prop2.name IN ('submit.argument8')

AND prop2.requestid = DECODE (instr(base.process_ids,',',1), 0,

base.process_ids,SUBSTR(base.process_ids,0,instr(base.process_ids,',',1)-1))

AND base.process_name = 'Create Accounting Subprocesses') base2

WHERE prop3.value (+)= base2.accounting_batch_id

AND prop3.name (+)= 'submit.argument3') base3

WHERE base3.cadatasetid = prop4.requestid

AND prop4.name = 'apps.fin.xla.xlafsnrpt.gltparent'

) base4

where base4.gltp = prop5.requestid

and prop5.name = 'apps.fin.xla.xlagltrn.childRequests'

and hist2.requestid (+) = to_number(base4.XLABABUP)

and hist3.requestid = to_number(substr(base4.loadfile,10))

ORDER BY cadatasetid