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

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:
1. Call child integration like OIC_FILE_HANDLER and feed the downloaded file reference, move file flag = Y,  source and target file details so that it can move the file from source to target location.https://soalicious.blogspot.com/2023/12/oic-create-resuable-integration-to.html
2. Call child integration like OIC_Notification to create a log file to ftp directory. Also we can create incident to Service now. https://soalicious.blogspot.com/2021/06/oic-send-notification.html

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