Tuesday, December 5, 2023

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:


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.  


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:

No comments:

Post a Comment

Featured Post

11g to 12c OSB projects migration points

1. Export 11g OSB code and import in 12c Jdeveloper. Steps to import OSB project in Jdeveloper:   File⇾Import⇾Service Bus Resources⇾ Se...