OIC - ESS job run for delta calculation | Fetch daily Incremental data using ESS job and ess history and property tables

Usecase:

Here, we will discuss the steps how we can use ESS job for delta data or incremental data calculation for outbound data send respective to Oracle ERP.

High level steps:

1. Create a BI report with bursting query.
2. In the BI report model, use last_run_date or processstart date (using ess tables fusion.ess_request_history, fusion.ess_request_property) to fetch incremental data.
3. Create a ESS job and invoke the BI report as Reporting id.
4. Call that ess job from OIC.

Navigation to check/create/clone your ESS job:

Settings & Actions -- setup and maintenance -- search tasks -- search with "JOB" --select Manage enterprise scheduler job definition and job sets for financials, supplier chain, management and related application-- search with you ess name like '%FIN%'--select your ess job and edit to see or clone to create new ess job.

Following details are needed to create a dummy ess job for delta calculation:

Path: /delta/

Name: ess job name

Report id: /Custom/Integrations/Outbound/AP/FIN<report_name>_BI.xdo

application: application tool kit

Job application: FscmEss

Job type: BIP job type

Allow multiple pending submission: yes

Enable submission from scheduled process : yes

Note:

While the dummy ess job will be ran from the oic, we can see the job and submission time from erp process monitor.

This submission time will be saved in Fusion.ess_request_history table. This last runtime of ess job we use with last update date  to calculate the delta data wih comparing with the respectibe gl_je_lines or other table creation date and also based on event type like for which interface.

Query to fetch Last run date from ess history table:

Select NVL(MAX(erh.processstart), NULL) last_run_date 

FROM ess_request_history erh, ess_request_property erp1 

where 

erh.executable_status ='SUCCEEDED' 

AND erp1.requestid = erh.requestid 

and erp1.name ='submit.argument1' 

and erp1.VALUE =:p_event 

and erh.submitter ='SVC_INTEGRATION_ERP_ACT'

And (erh.definition ='JobDefinition://oracle/apps/ess/custom/delta/FIN_GL_RECON_ERP_BLK_ESS' or erh.definition' = 'JobDefinition://oracle/apps/ess/custom/delta/FIN_GL_RECON_CSV_ERP_BLK_ESS')

Select requestid,processstart,executable_status,submitter,definition from ess_request_history where definition =JobDefinition://oracle/apps/ess/custom/delta/FIN_GL_RECON_ERP_BLK_ESS' or erh.definition'

Select * from ess_request_property where value = :p_event

Main data model BI query example: 

first we populate the last_run_date, then comparing the gl_je_lins creating datw with last_run_date, we can fetch tbe delta daily imcremental data.

Select

Segment1,

Segement2,

Event_type_code,

Last_run_date,

Creation_date,

'1' super_group

from

(select 

to_char(xal.accounting_date, 'MM/DD/YYYY') as effective_date,

glcc.segment1 as segment1,

Glcc.segment2 as segment2,

xe.event_type_code,

Er.last_run_date,

Glb.posted_date creation_date

from

gl_code_combination glcc,

gl_je_lines glje,

gl_je_headers gljeh,

gl_je_batches glb

gl_je_categories gljec

Xla_event xe,

xla_ae_headers xah,

xla_ae_lines xal,

gl_import_references gir,

(

Select NVL(MAX(erh.processstart), NULL) last_run_date 

FROM ess_request_history erh, ess_request_property erp1 

where 

erh.executable_status ='SUCCEEDED' 

AND erp1.requestid = erh.requestid 

and erp1.name ='submit.argument1' 

and erp1.VALUE =:p_event 

and erh.submitter ='SVC_INTEGRATION_ERP_ACT'

And (erh.definition ='JobDefinition://oracle/apps/ess/custom/delta/FIN_GL_RECON_ERP_BLK_ESS' or erh.definition' = 'JobDefinition://oracle/apps/ess/custom/delta/FIN_GL_RECON_CSV_ERP_BLK_ESS')

Er

Where

glje.code_combination_id = glcc.code_combination_id

And glje.je_header_id = gljeh.je_header_id

And gljeh.je_category = gljec.je_category_name

And glje.creation_date > NVL(:p_test_date,NVL(er.last_run_date, SYSDATE - 1))

And xe.event_id = xah.event_id

And xe.entity_id = xah.entity_id

And xah.ae_header_id = xal.ae_header_id

And xah.ledger_id = xal.ledger_id

.

.

.

Group by 

(xal.accounting_date, glcc.segment1,glcc.segment2,xe.event_type_code,er.last_run_date,glb.posted_date))

Union

Select

'Default_Row' segement1,

' ' segement2,

:p_event event_type_code,

Sysdate last_run_date,

Sysdate creation_date

.

.

.

'1' Super_group

From dual