OIC ERP - Get Source Name for invoice callback | Fixed asset callback

Usecase: Here, we will see how to get source name for which the callback event we received once the FBDI import completed to ERP. Here, we will show couple of examples like soure names for invoice or fixed asset callbacks etc.

High level steps:

1. Create a BI report data model based on the below sql query.
2. Create an EDI delimiter based eText BI template and create a BI Report.
3. Create a BI report invoke soap connection in OIC
4. In callback integration - invoke the BI report soap connection and pass the load request id parameter name value and the report path name etc.
5. Decode the base 64 response and remove carriage return or next line using custom Javascript and store it into a variable for further use.

BI report SQL queries:

Select distinct source from ap_invoices_interface where 1 = 1 and load_request_id = :p_load_request_id

Union all

Select distinct aii1.feeder_system_name from fa_mass_additions aii1 where aii1.load_request_id = :p_load_request_id

Union all

Select distinct bank_name from ce_statement_headers_int csh where 1=1 and csh.load_request_id = :p_load_request_id

Union all

Select distinct 'ServiceNowUpdates' from fa_descriptive_details_int where 1=1 and 

load_request_id = :p_load_request_id

Union all

Select distinct 'ServiceNowTransfer' from fa_transfers_t where 1=1 and 

load_request_id = :p_load_request_id

Union all

Select distinct 'ServiceNowRetirements' from fa_retirements_t where 1=1 and 

load_request_id = :p_load_request_id

Detailed with screenshots:

Step1: Create Data model.

Home > Tools > Reports & Analytics >Browse Catalog > create > data model > click + sign > SQL Query > provide the SQL query

Checked the box to create the parameter.

Data model created.

Provide a default value and make it mandatory

See the structure.

Click on data and provide valid load request id and view

Save the sample data

Export the data to see the xml structure.

Save

Step2: Create a BI report. Create a edi (.rtf) template and upload.

Save

View Report.

Sample edi (comma separated template) etext template.

To know how to create eText Delimiter based rtf template , follow my blog:

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

Step3: Create a SOAP connection to call the BI report

Step4: Configure BI report invoke and decode base64 and store it into a variable.

Sometime while we decode the base64 BI report response, it can have a next line or carriage return with the response, in that case , we need to create a custom javascript and remove them. Follow my below blog for the js code:

https://soalicious.blogspot.com/2021/03/oic-javascript-to-remove-carriage-return.html

OIC - ERP - Payables invoice callback - Custom report

Usecase: Here, we will customize the payable invoice callback and get a detailed report from ERP.

Implmentation steps:

1. Subscribe the callback
2. Check if jobname ="Load Interface File for Import" then 
1. save the load request id and load status 
2. Call a BI report to get the Source name based on Load request id (https://soalicious.blogspot.com/2024/01/oic-erp-get-source-name-for-invoice.html)
3. Update path : Fetch FTP or S3 bucket path details from where we need to download the processed zip file or achive the processed file. We will use a simple Lookup like OIC_Interface_Path.dvm and store source name and path  and from integration, fetch the path based on source name (fetched from previous step) using lookup() function.
4. Get the Processed zip file from FTP or S3 Bucket processing directory invoking a child integration. (https://soalicious.blogspot.com/2023/11/oic-get-files-from-aws-s3-rest-service.html)
1. Provide directory name
2. filePattern as $loadRequestId
3. Unzip flag = N
5. Check Load status
1. If Load status != SUCCEEDED then Archive the source zip file calling a child integration. With .Failed appended file name. (https://soalicious.blogspot.com/2023/12/oic-create-resuable-integration-to.html)
2. Otherwise, if Jobname ="Import Payables Invoices" ans status = SUCCEEDED then, 
1. Validate the AP Invoice: Call ESS job and feed inputs parameters like business unit id, source, ledger id etc. JobPackageName: /oracle/apps/ess/financials/payables/invoices/trandactions/ and jobDefinitionName: APXAPRVL
2. Monitor the AP invoice : Call a child integration and pass ess job run request id and get the status (https://soalicious.blogspot.com/2024/01/oic-erp-monitor-progress-of-ess-job-and.html)
3. Initiate invoice approval: Call ESS job and feed inputs parameters like business unit id, source etc. JobPackageName: /oracle/apps/ess/financials/payables/invoices/trandactions/ and jobDefinitionName: APXIAWRE
4. Archive the file.
6. Get detailed BI report file (https://soalicious.blogspot.com/2024/01/oic-erp-invoice-bi-report-sql-query-to.html)
7. Save the report to a ftp or S3 directory for further processing.
8. Send a Notification with the report. Or we can call ESS job and send mail over BI bursting (https://soalicious.blogspot.com/2024/01/oic-erp-send-custom-invoice-report.html)

OIC ERP - How to download Import Payables Invoices Report using ERP cloud Adapter from Oracle Integration

Usecase: 

Here, we will see how we can download the import Payables Invoices report. That is we will download report equivalent .xml file which contains same details like the report.

Why do we need :

Note: By default, when we do import AP standard invoice and enabled callback, using import payable invoice process request id if we download ess job exec details, it will only give a .log file but not actual execution report.

High level steps:

• Create an app driven intregation and configure to subscribe callback event
• If summarystatus and import payables invoice succeeded, store the import payables invoices request id to a variable
• Call cloud ERP adapter and do submitESSJobRequest operation of ErpIntegrationService to run the report and get the request id as response.
• Take a while loop and check getESSJobStatus 
• Once submitESSjob gets success, downloadESSJobExecutionDetails using cloud erp adapter
• Write the zipped file to stage location using opaque schema
• Unzip the files using stage
• Map the xml file(putting a predicate condition as fileType='xml' in xpath) to send as Notification attachment.

Ess report package details:

jobPackageName: "/oracle/apps/ess/financials/payables/invoices/transactions/"

jobDefinitionName: "APXIIMPT_BIP"

Detailed steps with screenshots:

Integration flow:

Subscribe callback event:

Assign global variable for request id, ess job package name, path, loop counter and ess job status etc.

Switch to check summarystatus

Store import payables invoices process request id.

Configure submit ess job request and map the import request id and ess job package definition and name.

While loop to check ess job status

Configure getEssJobStatus and map the request id which the response of the submitEssJobReq.

Update status and counter to loop.

Put a wait time.

Once ess job gets succeeded,  configure downloadEssJobExecutionDetails and map the request id of the submitEssJobReq.

Take a stage to write the zipped file.

Unzip the files.

Take a notification and add the details required and attached the .xml file in the attament area. We need to put a predicate condition fileType ='xml' in the xpath to read the .xml file.

After test:

ODI 12C - How to clean Cache to start ODI studio

Sometimes when you will try to start odi studio from console or terminal, you will observe that its not getting started and in that situation, the simple solution is to clean the cache before starting odi studio.

Following command to clean the cache:

./odi.sh -clean -initialize

Path:

/u01/oracle/mwh/odi/studio

After cleaning the cache, if you run the following command it will start the odi

./odi.sh

OIC ERP - When to use Fusion Accounting Hub (FAH) vs GL Journal integration?

When to use Fusion Accounting Hub (FAH) vs GL Journal integration?

When GL :

For the third party source system (such as Payroll or Treasury , etc.) which provides direct transformed accounting transactions or data, we can use GL Journal Import interface. We can build a custom integration by importing a periodic data file from the source system and insert the data validation and populate the GL journal fbdi template. We can then upload the template and import the GL Journal.

When FAH:

The source systems which are unable to provide transformed accounting data, we can 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.

Once transactions are accounted in FAH sub- ledger, there are 2 ways to transfer these accounted entries to Oracle General Ledger. 

• One is to transfer at once by submitting the individual Journal Import.
• the other one is to submit it as part of the Create Accounting Process itself (Create Accounting => Accounting Program => Journal Import). With the second option, system submits journal import for each of the child accounting programs i.e. concurrency achieved at the journal import level. This can save up to 30% of total time and works within an interface.

OIC - ERP - Import AP Invoice to Cloud ERP using FBDI | Detailed steps to import AP Invoice to Cloud ERP

Usecase: Here, we will demonstrate the detailed implementation steps for AP Invoice FBD Import from polling the file from source >> create FBDI file >> to import to ERP >> to Subscribe Callback.

Source Invoice file structure:

https://soalicious.blogspot.com/2023/11/ap-invoice-source-file.html

Download the FBDI template: 

oracle site https://docs.oracle.com/en/cloud/saas/index.html (ERP >>Financials >>select 23D or latest >>Administer >> Import data >>Get started with file-based data import >>payables >> payables standard Invoice Import >>xslm template section.  

https://soalicious.blogspot.com/2023/03/oic-erp-import-acoount-payable-invoice.html

Implementarion steps:

1. Poll the Invoice file from Source. We can poll from some SFTP server or calling a rest service or others. In this case, we are calling a child integration which will get the files using rest call. (https://soalicious.blogspot.com/2023/11/oic-get-files-from-aws-s3-rest-service.html). Put a switch and check If there is any failure , then throw new fault with exception code, reason and details to scope handler. 
2. Iterate over the polled files using loop action.(below all steps under a body scope)
1. For each file, read file reference using stage,  decodebase64ToReference() and Sample CSV file.
1. Iterate over each record
1. Take a assign to take line and amount counter for validation.
2. If line counter >3 then(in the source file, 1st 3 records are only header fields that we will skip)
1. Check If C1 = Invoice then
1. Get unique Invoice id by calling BI Report. (How to create OTBI report: https://soalicious.blogspot.com/2022/03/cloud-erp-how-to-create-otbi-report.html) and how to call BI report from OIC (https://soalicious.blogspot.com/2021/05/oic-invoke-erp-bi-report.html)
2. Add total invoice amount for each header. varInvoiceAmount = add(varInvoiceAmount,eachHeaderamount). This is required for file validation.Use this custom function to add numbers to have 2 decimal places. https://soalicious.blogspot.com/2021/03/oic-use-of-javascript-in-integration.html
3. Write header file using FBDI Invoice header template. File name: ApInvoicesInterface.csv(Mandatory mapped fields: Invoice id(unique id generated from BI report), Business Unit, Source, Invoice Nunber, Invoice Amount, Invoice Date(YYYY/MM/DD format), supplier Name, Supplier Number, Supplier Site, Invoice Currency, Description, Invoice type, Legel entity, Payment terms, Pay Group, Payment reason code, payament reason comments)
2. Check if C1 = Invoice Line then,
1. Write Line FBDI File using template. File name: ApInvoiceLinesInterface.csv  and Mandatory mapped fields: Invoice id (same BI fetches Unique and sequential Id), Line Number, Line Type like ITEM, Amount, Invoice Quantity, Unit Price, Description, Dustribution Combination, Tax Classification Code.
3. Check if C1 =Invoice Trailer then
1. Take file records count and total file amount which is needed for validation.
2. Create a ZIP manifest file using a stage for invoice header and Line files.
3. Properties file : This file contains import job package name, job definition, zip file prefix and job parameters to be passed. Check my below blog for details. How to get job details and parameters etc.  There are two options: choose any one based on requirements.
1. Generally we create this .property file and zip with the FBDI header and line files if we use import bulk of oracle cloud erp adapter.  (https://soalicious.blogspot.com/2022/05/oic-understand-of-fbdi-properties-file.html)  
2. Map the comma separated job parameters while using CRUD erpIntegrationservice under Oracle Cloud ERP adapter. This case, we used 2nd option: 
1. Mapped job parameters: "#NULL,BusinessUnitID,N,#NULL,#NULL,#NULL,1000,SourceName,#NULL,N,N,LedgerId,#NULL,1"
2. Job name: /oracle/apps/ess/financials/payables/invoices/transactions/,APXIIMPT
1. File Validation: 
1. If Trailer record total lines count = total line counts and total trailer amount = total line amount then its a valid file then
1. Encrypt and import.: there are generally two different methods using which we can import to ERP using oracle cloud ERP adapter: choose any one based on requirements.
1. Using bulk import. (https://soalicious.blogspot.com/2021/05/oic-import-payables-invoices-using-erp.html)
2. Using CRUD Service ErpIntegrationService>> importBulkData operation: Here we will create a reusable child integration to import. This case we used 2nd option (https://soalicious.blogspot.com/2021/05/oic-import-to-erp-technique-2-bulk-data.html)
2. Get import job status using cloud erp adapter and erpIntegrationService (https://soalicious.blogspot.com/2021/07/oic-get-ess-job-status.html)
3. Move source file as zip appedning with import or load ess request id to processing folder for further processing during callback. (Business specific requirement)
2. Otherwise,
1. Write the source file to Archive folder and delete from polling folder. (https://soalicious.blogspot.com/2023/12/oic-create-resuable-integration-to.html)
2. Create logs and create incident and send email Notification (https://soalicious.blogspot.com/2021/06/oic-send-notification.html)
3. Throw new fault to scope handler using tralier fault as code, incident short description as reason and log description as details.
2.  Callback part : We can follow any of the below methods - default or custom callback. This case we have used custom callback.
1. Default callback subscribe:
1. Prerequisite : https://soalicious.blogspot.com/2023/03/erp-register-csf-key.html
2. Follow this blog to import and enable and subscribe callback (https://soalicious.blogspot.com/2021/05/oic-import-payables-invoices-using-erp.html)
3. Download Import Payables Imvoices Report as .xml and send as notification attachemnt - https://soalicious.blogspot.com/2023/12/oic-erp-how-to-get-import-payables.html
2. Custom report callback: Follow this blog: https://soalicious.blogspot.com/2024/01/oic-erp-payables-invoice-callback.html)
3. Erp inbound integration callback subscribe: this below blog will show how to subscribe the events which are raised within the ERP. Will update more details later(?????) (https://soalicious.blogspot.com/2022/11/oic-subscribe-to-business-events-raised.html)
3. Error handling:
1. From body scope , after each invoke, put a switch condition and if exception  exists, throw new fault (customized fault) to scope default fault handler. For example, after invoking get files , import, get ess job status etc.
2. From scope fault handler:
1. If import not failed(checking error code), Move file from source direcory to archive directory.
2. Throw the fault to Global fault
3. From Global fault: 
1. Send notification or email
2. Log fault details to DB or File/FTP location
3. Create an incident ticket.
4. Rethtow fault.

To know how to upload PGP key for encryption and decryption follow my below blog:

https://soalicious.blogspot.com/2021/08/oic-import-pgp-keys-to-use-in-stage.html

OIC - Create a resuable integration to write or move and delete files from AWS S3 using Rest

Usecase: Here, we will create a reusable integration for example say OIC_FILE_Handler which will do move and delete file from a specified directory of AWS S3 bucket using rest feature.

It will perform the following operations:

1. Write file
2. Delete file
3. Move file (mixed option 1 and option2)

REST trigger Request JSON:

{

"base64FileRef":"",

"sourceFileName":"",

"sourceDirectory":"",

"moveFileFlag":"",

"targetDirectory":"",

"targetFileName":"",

"deleteSourceFileFlag";"",

"deleteDirectory":""

}

REST Response JSON:

{

"jobStatus":"SUCCEEDED",

"fileMoved":"Y",

"fileDeleted":"N",

"exceptionCode":"",

"exceptionReason":"",

"exceptionDetails":""

}

Implemetation logic steps:

1. Create a AWS S3 rest connection
2. Create a App driven orchestration pattern and configure rest to accept the base64 encoded file, source path, delete path, source and delete directories and different flags. 
3. Assign globals:
1. varBasePathAndFile : Concat(deleteDirectory  sourceFileName)
2. varMovePathAndFile : concat(targetDirectory, targetFileName)
3. varFileref: ""
4. varMoveComplete : N
5. varDeletecomplete: N
6. varFileStatus : "error"
7. varCounter : 0.0
4. IF base64FileRef != empty, then decode base64 to file reference using decodebase64ToReference().
5. If moveFileFlag = Y, call S3 rest service to place the file to the specified  directory and map the decoded file ref to stream reference.
1. Resource path: /{pathAndFileName}
2. VERB: put
3. Request payload as Binary - application/zip
4. Also, put a retry logic to place the files to S3 3 times
1. Take a scope
2. While conditon: varFileStatus ="error" and varCounter <3.0
3. If success, assign varFileStaus ="success"
4. Under scope > default fault handler > increment counter +1
5. Update that varMoveComplete = Y if it is success
6. IF deleteSourceFileFlag = Y , Call S3 rest service nad configure below:
1. Resource path: /{pathAndFileName}
2. Verb: Delete
3. Map the varBasePathandFileName 
4. If it is success,  assign deleteComplete =Y
7. Update the response of the rest - fileMoved, fileDeleted and jobStatus.
8. Update the exception scope fault status from main scope fault handler

Screenshots: