OIC ERP - Two Methods to receive Callback/Business Events from ERP – OAuth(New) and CISF Keys(Old)

Two Methods to receive Callback/Business Events from ERP

• OAuth(New)
• CISF Keys(Old)

We can check if the Token-Based Authentication Scheme is Now Enabled for Event Integrations as below:

Step1: 

Open in SOAP UI /POSTMan :-

https://<Fusion_Server_host_url>/soa-infra/PublicEvent/subscriptions

Step2:

By default after 22A update OIC will start using Token Based Authentication. This you can check from the About page on your OIC instance. If you need CSF Key based authentication then SR has to be raised.

When TBA (Token Based Authentication) is used.

When CSF Key is used.

Points to remember:

1. The token-based authentication scheme is added in the Oracle ERP Cloud Adapter for business and FBDI event messages originating from Oracle Fusion Applications. For token-based authentication, the EHF-Token must be present in the HTTP headers of the event message. 
2. In the absence of the EHF-Token, Oracle Integration checks for the CSF key credentials for authentication. Token-based authentication removes the dependency on the CSF entry in Oracle SOA Composer.
3. On the current version (as of April 2022): You don't even have to indicate the specific callback integration anymore, as the moment you activate the callback-appdriven integration, SaaS immediately knows that integration as an active subscriber (you can confirm when you call SaaS REST API: <SaaSDomain>/soa-infra/PublicEvent/subscriptions). When you deactivate that integration, it also allows you the option to temporarily 'Delete Event Subscription', which confirms that both OIC - SaaS are really linked.
4. The new callback is forced to use OnEvent subscription, versus the old OnJobCompletion.

Reference:

https://dobbylearns.wordpress.com/2021/05/23/oic-fbdi-callback-related/

https://docs.oracle.com/en/cloud/paas/application-integration/erp-adapter/prerequisites-creating-connection.html

ODI 12c - Buffer too small for CLOB to CHAR or BLOB to RAW conversion

We have an error message refresh variable in which it is trying to get the error message from snp_session table (schema : OD_BI_REPO) where it is converting the clob message to char as varchar2(3000) getting below error:

Caused by: java.sql.SQLException: ORA-22835 : Buffer too small for CLOB to CHAR or BLOB to RAW conversion (actual: 54150, maximum: 32767)

Sql query causing the error:

Select cast(error_message as varchar2(3000)) from (select error_message from snp_session where sess_status ='E' order by sess_beg desc) where rownum <2

Modified query:

Select substr(error_message,1,4000) from (select error_message from snp_session where sess_status ='E' order by sess_beg desc) where rownum <2

OIC - ERP - Detailed implementation steps to import transactions to ERP Fusion accounting hub or ERP FAH sub ledger accounting

Working...13th Feb

Usecase: we have a source who is unable to provide transformed accounting data thats why we can't use FBDI GL journal import so we need to use Fusion Accounting Hub (FAH) -  create the subledger source for the 3rd party system and configure the account transformation rules. Then import the transaction data using the fbdi template. Here, we will see the detailed FAH subledger accounting import implementation steps to ERP.

To know when to use GL or FAH, follow

https://soalicious.blogspot.com/2023/12/oic-when-to-use-fah-vs-gl-import.html

Implementation steps:

1. List the files using SFTP connection from a specified directory
2. Iterate over each file using for each action
3. Take a body scope
1. Download file as binary using SFTP connection to stage location.
2. Assign global variables
3. Write header Rows using stage C to C79 CSV file, file name : XlaTrxH.csv
4. Write Line Header names : XlaTrxL.csv , https://soalicious.blogspot.com/2022/01/oic-add-header-or-line-file-field-names.html
5. Get Open Period dates from ERP using BI report call. See my blog how to call BI report. https://soalicious.blogspot.com/2022/03/oic-how-to-call-and-read-bi-publisher.html
6. Decode BI base64 response to reference : Take a assign and use decodeBase64ToReference() to store in a variable
7. Read the open period dates file using stage, sample csv file, reference variable
8. Read in segments the downloaded source file using stage
9. For each record
1. Update variables
2. If C1 = DHD ( for header records)
1. If record line count =0, assign transactionNumberString as empty
2. Create a unique transaction number , we can take a sequence number or generate using data and time like concat(month-from-date(current-date()),day-from-date(current-date()),$p_currentTime,c8,c4) where c8= event type code and C4= transaction date and p_currentTime =concat(hours-from-dateTime(current-dateTime()),minutes-from-dateTime(current-dateTime())) declared as a global variable
3. For each open period dates row,
1. Check if transaction date within open period start and end dates and system matches, then assign openperiod flag as Y
4. IF open period flag = N, update reject count as below
1. Update closeperiodcount = + 1 and closedperioddates = concat( closedperiod dates , C2 and C4) where C= company code and C4= date
5. If contains(TransactionNumberString, transactionNunber) = "true" then just logger with trasaction number. This step means we are skipping to write duplicate header record having same transaction number
6. Else:
1. update transaction number string.
1. Transaction number string = concat(transaction number string, transaction number)
2. Write header file using stage
1. File name: XlaTrxH.csv
2. Append : yes
3. Mandatory mapped fields:
1. transaction_number : for each unique number , only one header record should be there.
2. event_type_code
3. Ledger_name
4. Transaction_date
5. Transaction_reversal_flag
3. If C1 = DLN ( For Line records)
1. Assign line variables
1. recordLineCount = recordLineCount +1 
2. lineNumber = LineNumber +1
2. Take a switch and check amout for credit and debit validation:
1. If c30 >= 0.0 then assign debit variables like
1.  record debit line count = record debit line count +1
2. record debit line amount = add(record debit line amount, c30). how to add two numbers with fixed decimal points using javascript. check my blog(https://soalicious.blogspot.com/2021/03/oic-use-of-javascript-in-integration.html)
2. else, assign same for credit variables like record credit line count, record credit line amount
4. If C1 = BTR ( For Trailer Records)
1. Assign Trailer records:
1. File line count
2. File debit line count
3. File credit line count
4. File debit line amount
5. File credit line amount
10. IF closePeriodCount >= 1 then throw new fault 
1. Code like closed period trasaction exists
2. Reason like closed period transaction exists in the source file
3. Details like closePeriodDates variable
11. Write Lines part:
1. Read file in segments using stage and the downloaded file reference : provide a sample.csv file
2. For each record
1. If C1 = DLN
1. Assign newLineNumber = newLineNumber + 1 (where newLineNumber was globally assigned as 0)
2. Take a stage and write the lines
1. File name: XlaTrxL.csv
2. Append yes
3. Mandatory mapped fields: 
1. Transaction_numer: we can take a sequence number or generate using data and time like concat(month-from-date(current-date()),day-from-date(current-date()),$p_currentTime,c8,c4) where c8= event type code and C4= transaction date and p_currentTime =concat(hours-from-dateTime(current-dateTime()),minutes-from-dateTime(current-dateTime())) declared as a global variable (Note: the transaction number should match for header to the lines uniquely)
2. Line_Number : assign $newLineNumber generated from
3. Default_Currency
4. Account_L
5. STATE
6. Description
7. Book_Type
8. Company
9. CostCenter
10. InterCompany
11. ExpenseFunction
12. Project
13. Je_Line_Ref
14. Source
15. Default_amount  
1. Write meta data : Create a meta data file using stage with Metadata_File_InterfaceName.txt which will be helpful to track from ERP scheduled process.
1. Metadata version number: 3
2. Application Short Name : SourceName
2. Zip files : Zip the header and line files using a stage.
3. Archive zip files : Archive the zip file to a ftp location for support help.
4. Validation of Trailer record: Take a switch and validate following:
1. File line count = RecordLineCount
2. File debit line count = RecordDebitLineCount
3. File credit line count = RecordCreditLineCount
4. File debit line amount = RecordDebitLineAmount
5. File credit line amount = RecordCreditLineAmount
5. IF validation is success
1. Encrypt and import : https://soalicious.blogspot.com/2021/05/oic-erp-import-technique-1.html
2. Get ess job status for import: https://soalicious.blogspot.com/2021/07/oic-get-ess-job-status.html
3. Capture child process id: https://soalicious.blogspot.com/2022/01/oic-erp-how-to-get-child-process-id.html
4. Get ess job staus from child process : same way like above step2 : https://soalicious.blogspot.com/2021/07/oic-get-ess-job-status.html
5. Fah call back part : https://soalicious.blogspot.com/2022/02/oic-custom-callback-from-erp.html
6. Else:

OIC ERP - How to select a Layout while invoking BI Report | use of attributeTemplate of ExternalReportWSSService

Usecase: Sometimes we have a requirement that we have a BI report with Multiple layouts like RTF, XML, CSV etc and we would like to select specific layout to generate the output.

In the below , we can see we have two layouts 

Solution steps:

Use layout name to attributeTemplate element to select the layout to generate report .

Sample payload:

<soap:Envelope xmlns:soap="http://www.w3.org/2003/05/soap-envelope" xmlns:pub="http://xmlns.oracle.com/oxp/service/PublicReportService"> 

<soap:Header/> 

<soap:Body> 

<pub:runReport>

 <pub:reportRequest> <pub:attributeTemplate>CONTROL</pub:attributeTemplate> <pub:reportAbsolutePath>/Custom/INTEGRATION/AP_Invoice_Import_Summary_Report.xdo</pub:reportAbsolutePath> <pub:sizeOfDataChunkDownload>-1</pub:sizeOfDataChunkDownload> </pub:reportRequest> 

</pub:runReport> 

</soap:Body></soap:Envelope>

If you want to use another layout, you can change the layout name in the payload.

OIC ERP - Create a Reusable Outbound integration | Run outbound interface reports to create outbound files

Usecase: Here, we will create n reusable outbound integration where we will call BI report using with name value paramters or without paramters and also can call ESS job (for daily or delta data fetch) and then create outbound files.

For delta data logic, follow below blog:

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

Implementation steps:

1. Create a schedule orchestration and below schedule parameters with sample values
1. p_encryptFlag : Y or N
2. p_essJobName : "oracle/apps/ess/custom/delta,FIn_Sample_ERP_ESS;p_event=PT:p_destination: full path;p_fileName= filename:p_ext=.xlsx,p_format=xssx"
3. p_fileName: filename.xlsx
4. p_outboundPath: where we will place outbound file
5. p_reportPath: Custom/Integrations/Outbound/GL/FIN_GL_RECON_ERP_BLK_BI.xdo;p_event=pt
2. Take a scope body
3. Assign globals
1. fileName : concat(substring($p_fileName,1.0, index-within-string($p_fileName,".")),"_",year-from-date( current-date()),month-fromdate(current-date()),day-from-date(current-date()),"_", hours-from-dateTime(current-dateTime()), minutes-from-dateTime(current-dateTime()),integer(seconds-from-dateTime(current-dateTime())),substring($p_fileName, index-within-string($p_fileName,".")+1.0,5.0))
2. jobPackage : ""
3. jobDefinition : ""
4. fileReference : ""
5. paramFileName : "Parameters.csv"
6. paramFileDir : "/parameters"
7. essParamFileName : "ESSParameters.csv"
8. essParamFileDir : "/essParameters"
4. Check if p_reportPath contains ";" it means it contains some parameters
1. If yes:  fetch name values paramters and retry 5 time and call BI report to fetch the report.
1. Fetch name values paramters.(https://soalicious.blogspot.com/2021/05/oic-params.html)
2. Read paramter csv file using stage
3. Take assign  
1. retryCount = 0.0
2. fileReference = "NONE"
4. Take a while with condition as $retrycount <=4.0 and $fileReference ="NONE"
1. Take a switch and check if $retrycount >=2.0 then wait 20sec
2. Configure BI report call for runReport operation (we should have a soap connection for ExternalReportWSSService)
3. Map the following:
1. p_reportName as reportAbsolutePath and -1 as sizeOfDataChunkDownload.
2. Map the readParams c1 and c2 to paramterNameValues
4. Take assign fileReference = reportBytes
5. Take assign and update retry count : $retryCount =retrycount +1
5. Switch if fileReference = NONE throw new fault as "Error while dispatching SOAP messagw to the endpoint "
2. If no: retry 5 times and call BI report to fetch the report.
1. Take assign  
1. retryCount = 0.0
2. fileReference = "NONE"
2. Take a while with condition as $retrycount <=4.0 and $fileReference ="NONE"
1. Take a switch and check if $retrycount >=2.0 then wait 20sec
2. Configure BI report call for runReport operation (we should have a soap connection for ExternalReportWSSService)
3. Map the p_reportName as reportAbsolutePath and -1 as sizeOfDataChunkDownload.
4. Take assign fileReference = reportBytes
5. Take assign and update retry count : $retryCount =retrycount +1
3. Switch if fileReference = NONE throw new fault as "Error while dispatching SOAP messagw to the endpoint "
5. Check if p_encryptFlag = Y then encrypt the data fetched from BI call
1. Take assign and deocde base64 to file reference. fileReference = decodeBase64ToReference($fileReference)
2. Take stage and encrypt using Pgp key, fileReference and fileName (for pgp key details: https://soalicious.blogspot.com/2021/08/oic-import-pgp-keys-to-use-in-stage.html)
3. Take assign and encode back to base64. fileReference = encodeReferenceToBase64(fileReference)
6. Check if p_ESSJobName != "" or != "null" then

1. Check if contains($p_essJobName,";") = true then
1. Fetch required params values and call ESS job
1. Fetch only values of paramters and save in a csv file(https://soalicious.blogspot.com/2021/05/oic-params.html)
2. Read the csv file using stage
3. Submit ess job request using erp adapter erpIntegrationService
4. Monitor the ess job status
5. If anything failed for monitor child integration , will throw a new fault.
2. Else,
1. Call the ess job without parameters and monitor thr ess job status
1. Update job params:
1. jobDefinition: substring-after($p_essJobName,",")
2. jobPackage: substring-before($p_essJobName,",")
2. Configure Oracle Cloud adpater erpIntegrationService and submitEssJobRequest operation
3. Map the job definition and job package.
4. Monitor the ess job.(https://soalicious.blogspot.com/2024/01/oic-erp-monitor-progress-of-ess-job-and.html)
1. Move the file to Outbound location: Call child integration and move file to respective folder. https://soalicious.blogspot.com/2023/12/oic-create-resuable-integration-to.html

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