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

OIC ERP - Send custom Invoice report using Ess Job call and BI bursting report

Usecase: Instead of directly send the custom invoice report using Notification activity, we can also alternatively call ess job and do BI report bursting and send the email notification.

Highlevel steps:

1. Create Data models
2. Add BI bursting with email delivery channel.
3. Create RTF template using msword BI publisher plugin.
4. Upload the RTF template and create BI report
5. Create ESS job and call the BI report as reporting id.
6. From integration, call the submitESSJobRequest of erp cloud adpater erpIntegrationService with load id and source name as input feeds.

Detailed steps:

Step1: Create Data Model:

Invoice Callback Custom BI report SQL query:

WITH details AS

(Select 'Source: AP : ' || (select DISTINCT aii1.source FROM ap_invoices_interface aii1 WHERE aii1.load_request_id = :p_load_request_id) AS source_name,

'Total number of invoices received: ' || (select TO_CHAR(COUNT(invoice_id)) AS invoice_id FROM ap_invoices_interface aii1 WHERE aii1.load_request_id =  :p_load_request_id) AS count_rcv,

'Total dollar amount of invoices received: '|| (select NVL(SUM(invoice_amount),0) AS invoice_amnt FROM ap_invoices_interface aii1 WHERE aii1.load_request_id = :p_load_request_id) AS amount_rcv,

'Total number of invoices loaded from staging table: ' || (select COUNT(invoice_id) FROM ap_invoices_interface aii1 WHERE aii1.load_request_id = :p_load_request_id and aii1.status = 'PROCESSED') AS count_loaded,

'Total dollar amount of invoices loaded from staging table: '|| (select NVL(SUM(invoice_amount),0) FROM ap_invoices_interface aii1 WHERE aii1.load_request_id = :p_load_request_id and aii1.status = 'PROCESSED') AS amount_loaded,

'Total number of invoices not loaded from staging table: ' || (select COUNT(invoice_id)  FROM ap_invoices_interface aii1 WHERE aii1.load_request_id = :p_load_request_id and NVL(aii1.status,'REJECTED')= 'REJECTED') AS count_ignored,

'Total dollar amount of invoices not loaded from staging table: '|| (select NVL(SUM(invoice_amount),0)  FROM ap_invoices_interface aii1 WHERE aii1.load_request_id = :p_load_request_id and NVL(aii1.status,'REJECTED')= 'REJECTED') AS amount_ignored,

'Total number of errored invoices: ' || (select COUNT(invoice_id) FROM ap_invoices_interface aii1 WHERE aii1.load_request_id = :p_load_request_id and aii1.status= 'REJECTED') AS count_rejected,

'Total dollar amount of errored invoices: '|| (select NVL(SUM(invoice_amount),0) FROM ap_invoices_interface aii1 WHERE aii1.load_request_id = :p_load_request_id and aii1.status = 'REJECTED') AS amount_rejected,

'00121' KEY

From DUAL

Where (select distinct aii1.source from ap_invoices_interface aii1 where aii1.load_request_id = :p_load_request_id and aii1.source = :p_source) is not null

 )

Invoice error details SQL query:

Select Distinct aii.invoice_num, aii.invoice_date,aii.vendor_num,aii.invoice_amount,'00121' KEY from ap_invoices_interface aii where aii.load_request_id = :p_load_request_id and aii.source= :p_source and aii.status = ' REJECTED'

Step2 : create email bursting

Bursting query used:

Select

'00121' as "KEY",

'Template' TEMPLATE,

'en-US' LOCALE,

'XLSX' OUTPUT_FORMAT,

'CONTROL_REPORT' || to_char(SYSDATE, 'yyyymmdd') OUTPUT_NAME,

'EMAIL' DEL_CHANNEL,

'ReceiverEmail@gmail.com' PARAMETER1,

NULL PARAMETER2,

'SenderEmail' PARAMETER3,

'Import Invoice Control Report for Source : '|| :p_source || to_char(SYSDATE,'yyyymmdd') PARAMETER4,

'Please find attached Control Report' PARAMETER5,

'true" PARAMETER6,

NULL PARAMETER7

FROM DUAL

Step3: Create RTF template:

https://soalicious.blogspot.com/2024/01/erp-create-rtf-file-based-on-data-model.html

https://soalicious.blogspot.com/2022/12/erp-how-to-create-rtf-template-from.html

Step4: Create BI report.

Step5: create ESS job which will invoke this BI report.

Go to Home > My enterprise > Setup and Maintenance search with Manage Enterprise Schedule > click on Manage Enterprise Schuduler Job Definitions and job sets for financial, suppy chain management and related applicarions > click on create (+) button 

Provide BI report absolute path to reporting id.

We can test/schedule the created ESS job from tools > Scheduled Processes > schedule new process

Search with ESS job name and provide required input feeds and submit.

Step6: From integration, call the submitessjob of erp cloud adpater erpIntegrationService with load id and source name as input feeds.

Jobdifinition: "Test_AP_Email_CTRL_ESS1"

JobPackageName: "oracle/apps/ess/custom/ess"

OIC ERP - Invoice BI Report SQL query to get custom detailed report

Usecase: Here, we will see the BI report SQL query which we can use to generate custom detailed invoice report like below:

• Source name
• Total number of invoices received
• Total amount of invoices received
• Total number of invoices loaded from staging table
• Total amount of invoices loaded from staging table
• Total number of of invoices not loaded from staging table
• Total amount of invoices not loaded from staging table
• Total number of errored invoices
• Total amount of erroed invoices

Invoice Callback Custom BI report SQL query:

WITH details AS

(Select 'Source: AP : ' || (select DISTINCT aii1.source FROM ap_invoices_interface aii1 WHERE aii1.load_request_id = :p_load_request_id) AS source_name,

'Total number of invoices received: ' || (select TO_CHAR(COUNT(invoice_id)) AS invoice_id FROM ap_invoices_interface aii1 WHERE aii1.load_request_id =  :p_load_request_id) AS count_rcv,

'Total dollar amount of invoices received: '|| (select NVL(SUM(invoice_amount),0) AS invoice_amnt FROM ap_invoices_interface aii1 WHERE aii1.load_request_id = :p_load_request_id) AS amount_rcv,

'Total number of invoices loaded from staging table: ' || (select COUNT(invoice_id) FROM ap_invoices_interface aii1 WHERE aii1.load_request_id = :p_load_request_id and aii1.status = 'PROCESSED') AS count_loaded,

'Total dollar amount of invoices loaded from staging table: '|| (select NVL(SUM(invoice_amount),0) FROM ap_invoices_interface aii1 WHERE aii1.load_request_id = :p_load_request_id and aii1.status = 'PROCESSED') AS amount_loaded,

'Total number of invoices not loaded from staging table: ' || (select COUNT(invoice_id)  FROM ap_invoices_interface aii1 WHERE aii1.load_request_id = :p_load_request_id and NVL(aii1.status,'REJECTED')= 'REJECTED') AS count_ignored,

'Total dollar amount of invoices not loaded from staging table: '|| (select NVL(SUM(invoice_amount),0)  FROM ap_invoices_interface aii1 WHERE aii1.load_request_id = :p_load_request_id and NVL(aii1.status,'REJECTED')= 'REJECTED') AS amount_ignored,

'Total number of errored invoices: ' || (select COUNT(invoice_id) FROM ap_invoices_interface aii1 WHERE aii1.load_request_id = :p_load_request_id and aii1.status= 'REJECTED') AS count_rejected,

'Total dollar amount of errored invoices: '|| (select NVL(SUM(invoice_amount),0) FROM ap_invoices_interface aii1 WHERE aii1.load_request_id = :p_load_request_id and aii1.status = 'REJECTED') AS amount_rejected

From DUAL

Where (select distinct aii1.source from ap_invoices_interface aii1 where aii1.load_request_id = :p_load_request_id) is not null

 )

Select source_name column1 from details

Union all

Select count_rcv from details

Union all

Select amount_rcv from details

Union all

Select count_loaded from details

Union all

Select amount_loaded from details

Union all

Select count_ignored from details

Union all

Select amount_ignored from details

Union all

Select count_rejected from details

Union all

Select amount_rejected from details

Create data model and report using edi etext template

Sample template:

https://soalicious.blogspot.com/2023/11/etext-edi-template.html

Similarly, we can do the same custom report for FA using fa_mass_additions , EX for exm_credit_card_trxns , CM using ce_statement_headers_int tables etc.