OIC ERP - submit ess job - sending multiple parameters | call BI Bursting - sending two reports| FTP delivery channel

Usecase: 

Here, we will see the following:

• Submit ess job with mutiple parameters
• From ess > call BI report
• From BI report > bursting to FTP

Submit ess job with mutiple parameters

You can follow my below blog to get detailed info:

https://soalicious.blogspot.com/2023/04/oic-erp-how-to-submit-ess-job-and-get.html

From ess > call BI report:

Create ESS Job >> calling BI report id

Home Navigation > My enterprise > Setup and Maintenance >Search with Manage Enterprise Schduler > click on Manage Enterprise Scheduler Job Definition  and Jkb Seta for Financial, Supply Chain Management and Related Applications. > click on create plus button

Provide ESS Name, path, Application, job type, job application name, report id etc 

For detailed steps, follow mu below blog:

https://soalicious.blogspot.com/2023/01/create-first-ess-job-on-bi-report-erp.html

BI report Bursting FTP delivery:

Check my below blog for detailed steps:

https://soalicious.blogspot.com/2022/03/oic-erp-bi-bursting-steps.html

Bursting query: In this case we are sending 2 files.

SELECT 1 AS "KEY",

'Report' AS "TEMPLATE",

'en-US' AS "LOCALE",

:p_format AS "OUTPUT_FORMAT",

'FTP' AS "DEL_CHANNEL",

'AmazonS3' AS "PARAMETER1",

:p_destination AS "PARAMETER4",

:p_fileName ||'_' || TO_CHAR(SYSDATE, 'YYYY-MM-DD_HHMISS') || :p_ext

AS "PARAMETER5",

'true' AS "PARAMETER6"

FROM DUAL

Union All

SELECT 1 AS "KEY",

'Control' AS "TEMPLATE",

'en-US' AS "LOCALE",

'Text' AS "OUTPUT_FORMAT",

'FTP' AS "DEL_CHANNEL",

'AmazonS3' AS "PARAMETER1",

:p_destination AS "PARAMETER4",

'Control_'|| :p_fileName ||'_' || TO_CHAR(SYSDATE, 'YYYY-MM-DD_HHMISS') || '.txt'

AS "PARAMETER5",

'true' AS "PARAMETER6"

FROM DUAL

BI report data model query:

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

For more details on daily incremental data fetch, follow my below blog:

https://soalicious.blogspot.com/2021/05/oic-ess-job-run-for-delta-calculation.html