ERP BI sql query to fetch output based on ESS REQUEST HISTORY Table last run date | Fetch Processstart from ESS_Request_History table

Usecase: Here using ess job tables - ESS_request_history and ESS_request_property, we will fetch the latest succeeded processstart data as last_run_date and then compare this last_run_date with the last_update_date from the xla_trsansaction_headers.

Same BI sql query to fetch the last run date latest data from ERP db tables:

Example1:

WITH ess_requests

AS (select NVL(MAX(erh.processstart), NULL) last_run_date FROM fusion.ess_request_history erh, fusion.ess_request_property erp1

Where erh.executable_status = 'SUCCEEDED'

AND erp1.requestid = erh.requestid

AND erp1.name ='reportID'

AND erp1.VALUE = '/Custom/Integrations/Poc/SampleREP.xdo'

AND erh.submitter = 'SVC_OIC_ERP')

Select '1' super_group

,xth.transaction_number

,xal.sr8 policy number

,gcc.segement1

,gcc.segment2

...........

From xla_ae_lines xal

,xla_ae_headers xah

,xla_events xe

,xla_transaction_entities xte

,xla_transaction_headers xth

 ,gl_code_combinations gcc

,xla_subledgers

,xla_event_types_b xet

,ess_requests er

Where 1=1

And xte.entity_id =xe.entity_id

And xte.application_id = xe_application_id

And xe.event_id =xah.event_id

And xah.ledger_id = xal.ledger_id

...........

And xth.last_update_date > NVL(er.last_run_date, SYSDATE - 2)

Example2:

With jobrun as 

(Select (

Select nvl(MAX(erh.processstart), sysdate-1)

From fusion.ess_request_history erh

Where 1=1

And upper(erh.definition) like '%ESS_Report_Name%'

And erh.executable_status = 'SUCCEEDED'

AND erh.submitter = 'SVC_OIC_ERP') last_run

From dual)

Select distinct cc.segment1

,cc.segment2

,jeh.description

,jeh.status

........

From gl_period_statuses ps

,gl_je_headers jeh

,gl_je_lines jel

,gl_je_sources src

,gl_code_combinations cc

,gl_je_batches jeb

,xla_ae_lines xal

,xcla_ae_headers xah

,jobrun

........

Where 1=1

And ps.application_id =101

,ps.ledger_id = jeh.ledger_id

And jeh_period_name =ps.period_name

.....

And ((jeh.last_update_date) >=jobrun.last_run

Or (jel.last_update_date) >=jobrun.last_run

Or (jeb.last_update_date) >=jobrun.last_run

Or (src.last_update_date) >=jobrun.last_run

Or (xah.last_update_date) >=jobrun.last_run

Or (xal.last_update_date) >=jobrun.last_run)