OIC - Run a Schedule Integration now from oracle integration and retrieve Scheduled Integrations Run Status

Usecase: 

Here, we will see how to invoke a scheduled integration now and then monitor or retrieve scheduled integration run status.

Rest API details:

Run a Scheduled Integration Now:

Method: POST

Path: /ic/api/integration/v1/integrations/{id}/schedule/jobs

Request json Payload:

Without schedule params

{

"action":"NOW"

}

with schedule params if any:

{

"action":"NOW",

"parameters" : [{

"name": "p_encryptFlag",

"value": "Y"

},{

"name": "p_essJobName",

"value": ""

}]

}

Retrieve scheduled integration run status:

Method: POST

Path: /ic/api/integration/v1/integrations/{id}/schedule/jobs/{job_id}

Response json payload:

{

"id" : "111",

"links" : [{

"href" : "https://test.oraclecloud.com:443/icsapis/v2/integrations/schedulejobs/111",

"rel":"self",

},{

href" : "https://test.oraclecloud.com:443/icsapis/v2/integrations/schedulejobs/111",

"rel": "self"

}],

"status": " SUCCEEDED"

}

High level steps:

• Create a dummy scheduled service with scheduled parameters.
• Create a Rest OIC connection with OIC host,user and password details.
• Create another integration and call the rest oic invoke connection and configure with a location standard http header.
• Assign the job request id to a variable.
• Take a while loop construct and repeat until it gets the status
• Configure rest retrieve endpoint and get the status
• Update the status.

Detailed screenshots:

Step 1 : Create a dummy Scheduled integration with some scheduled parameters.

Step 2 : create another integration from where we will configure rest OIC invoke to call that created dummy scheduled service.

 In the standard http header response, we have to add location header field so that we can fetch job request id of the invoked dummy scheduled service.

Step3: Monitor or retrieve the called scheduled service job status.

To convert job status to boolean true or false values:

 https://soalicious.blogspot.com/2021/03/oic-js-return-boolean-status-based-on.html

Oracle Reference:

https://docs.oracle.com/en/cloud/paas/integration-cloud/rest-api/api-integrations-integrations-scheduled-integrations.html

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

OIC - ERP - create an integration to send outbound ERP File using BI bursting FTP delivery channel and ESS job | How to create an erp Outbound flow in Oracle Integration

Usecase: Here, we will see outbound integration which will process ERP outbound files using BI bursting FTP delivery channel and ESS job. 

Steps involved:

1. Create an Integration (app driven or schedule based on requirement) and feed following inputs:
1. essJobName : containes ess job package, jobdefinitionName and appended name value parameters with a ";". 
2. fileName
3. reportPath: BI report absolute path
2. Fetch name and value parameters into a stage csv file which will be used while submitting ess job request. Follow below blog to know how to get the name and value parameters. (https://soalicious.blogspot.com/2021/05/oic-params.html)
3. Submit ESS job with this fetched value parameters> call BI Bursting with FTP Delivery channel (https://soalicious.blogspot.com/2024/01/oic-erp-submit-ess-job-sending-multiple.html)
4. Check getESSjobStatus(https://soalicious.blogspot.com/2021/07/oic-get-ess-job-status.html)
5. If ESSJobStatus not Succeeded then
1. Call BI report: in a loop and retry:  (https://soalicious.blogspot.com/2022/03/oic-how-to-call-and-read-bi-publisher.html)
1. While condition: $p_decodeFileRef ='NULL' and $retryCounter <5.0
2. Call BI report with ExternalReportWSSServive.
3. Update file ref:
1. p_decodeFileRef: decodeBase64ToReference(reportBytes)
4. UPdate retry count  retryCounter = $retryCounter + 1.0
2. Read File : take a stage and read entire file where specify file ref : $p_decodeFileRef and provide a sample csv file (C1 field)
3. Take row counts : take a for each loop and count rows using repeating elements and rowCounter.
4. Check row counts
1. If rowcount >=2.0 then throw new fault as ess job failure
2. Else, call another child integration to have the blank file and process. https://soalicious.blogspot.com/2024/02/oic-erp-create-reusable-outbound.html
3. Monitor the child integration process status. Follow my below blog to call and monitor scheduled integration.(https://soalicious.blogspot.com/2024/01/oic-run-schedule-integration-now-from.html)

We are calling ess job to invoke to BI report to do ftp bursting for a reason. We are doing it to fetch delta data(daily data).

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

OIC - ERP - Monitor Progress of ESS job and return status | Reusable component to monitor the ESS job run status

Usecase: we will create reusable integration which will take the ESS job run request id as input and monitor the ESS job status and return back the status as response to the caller integration.

Detailed logic steps:

1. Create an app driven rest trigger integration with
1. Input: ESSJOBRequestID as template param and queryCount as Query param.
2. Output: returnStatus
2. SetRequestVariables
1. requestStatus = ""
2. loopCounter = 0.0
3. inProgress = "true"
4. MaxLoopCount = $queryCount
3. Take a while loop with condition $I_inProgress = "true"
1. Configure ERP cloud adapter and call erpIntegrationService and operation: getESSJobStatus
2. Update loop counter and status
1. loopCounter = $loopCounter +1
2. requestStatus = result
3. inProgress = returnStatusV2(result)
3. Take a switch
1. If loopCounter = maxLoopCount then Exit loop with below updates
1. inProgress = "false"
2. requestStatus = "Error, exceeded max wait time."
4. Update request status

Response rest json:

{

"returnStatus": "",

"exceptionCode":"",

"exceptionReason":"",

"exceptionDetails":""

}

Detailed steps with Screenshots:

ERP - Create a RTF file based on data model xml file

Usecase: We will see how to create a RTF template based on exported datamodel xml file.

Detailed steps:

Step1: Install BI publisher Desktop softwate so that we can create RTF file on MS word.

https://soalicious.blogspot.com/2022/03/erp-bi-publisher-installation-and.html

Step2: export xml file from data model and import to MS word as sample xml.

Step3: under BI publisher tab, insert the table rows and columns required and uaing field section, we can drag and drop field names to the table.

Step4: using repeating Group, we can add G_1 and G_2 repeating groups

Once RTF template ready > clik on format like pdf or xml etc to preview and save it .rtf.

See the data is showing as required