OIC - End-to-End OIC Integration for AP Invoice Payment Request Processing and Status Callbacks in Oracle Fusion | AP invoice Payment Request FBDI import and callback

🔹 Use Case

A source system sends AP Payable payment request data as REST JSON to Oracle Integration Cloud (OIC). OIC performs the following:

1. Converts the incoming data into FBDI and property files.
2. Zips and uploads them via ERP Fusion Adapter for bulk import.
3. Handles invoice creation callback.
4. On successful import, extracts the load interface ID and calls a BIP report to fetch invoice status.
5. Sends the response back to the source over REST.
6. Also handles callbacks for:
   • Payables Payment Created
   • Payables Payment Voided
7. Uses Fusion REST API to get payment-related invoice info and sends status to the source.
8. If payment is voided, calls an OOTB (out-of-the-box) fusion REST API to cancel the invoice in Fusion.

---

🔹 Solution Steps

1. Receive AP Invoice payment Request from Source

• API: Expose OIC integration as REST endpoint.
• Method: POST
• Request Payload (Sample):

{
  "transactions": [
    {
      "partyCode": "G1235",
      "partyType": "ORGANIZATION",
      "partyCountryCode": "HK",
      "address": "Flat X 3/F,...",
      "city": "Kowloon",
      "bankCountryCode": "HK",
      "bankCurr": "HKD",
      "bankId": "004",
      "bankName": "Citi Bank N.A.",
      "bankAccountNumber": "1234",
      "bankHolderName": "name",
      "refundAmount": "500",
      "currencyCode": "HKD",
      "paymentDate": "2025/07/01",
      "invoiceNumber": "PC-10043534_6",
      "businessUnit": "test LIMITED",
      "source": "PC",
      "paymentMethod": "test",
      "payGroup": "PC_HSBC",
      "distributionCombination": "0100-631-000000-1207060101-0000-0000-000-000",
      "lineNumber": "1",
      "lineDescription": "1000000210+SDU2025R1"
    }
  ]
}

---

2. OIC - Create FBDI File & Properties File

• Step: Map incoming JSON to AP Invoice FBDI template (usually AP_INVOICES_INTERFACE and AP_INVOICE_LINES_INTERFACE).
• Step: Generate the FBDI CSV and properties file.
• Step: Zip the files.

FBDI file format:

Transaction_Identifier,Party Name,Party Original System Reference,Party Type,Location Original System Reference,Country,Address Line1,Address Line2,Address Line3,Address Line4,Phonetic Address Line,Address Element Attribute5,Building,Floor Number,City,State,Province,County,Postal code,Postal Plus 4 code,Addressable Global Location Number,Language,Phone Country Code,Phone Area Code,Phone,Phone Extension,Payee E-mail address,Party Tax Registration Number,Account Country Code,Account Currency Code,Account Number,IBAN,Secondary Account Reference,Account Name,Account Type Code,Bank Name,Bank Branch Name,Bank Branch Number,BIC,Check Digits,Account Description,Account Alternate Name,Business Unit,Source,Ledger,Date,Source Currency,Description,Import Set,Legal Entity,Payment Terms,Payment Method,pay_Group,Liability Combination,Document Category Code,Voucher Number,Payment Reference,Payment Amount,Payment Reason Comments,URL Attachments,Document Category,Requester First Name,Requester Last Name,Requester E-mail,Requester Name,Payment Priority,Payment Message1,Payment Message2,Payment Message3,Attribute_Category,Attribute1,Attribute2,Attribute3,Attribute4,Attribute5,Attribute6,Attribute7,Attribute8,Attribute9,Attribute10,Attribute11,Attribute12,Attribute13,Attribute14,Attribute15,Line Number,Line Requester First Name,Line Requester Last Name,Line Requester E-mail,Line Requester Name,Line Description,Combination,Distribution Set,Line Attribute_Category,Line Attribute1,Line Attribute2,Line Attribute3,Line Attribute4,Line Attribute5,Line Attribute6,Line Attribute7,Line Attribute8,Line Attribute9,Line Attribute10,Line Attribute11,Line Attribute12,Line Attribute13,Line Attribute14,Line Attribute15,Line Attribute1,Line Attribute2,Line Attribute3,Line Attribute4,Line Attribute5,Line Attribute6,Line Attribute7,Line Attribute8,Line Attribute9,Line Attribute10,Line Attribute11,END

FBDI template:

https://docs.oracle.com/en/cloud/saas/financials/24c/oefbf/payablespaymentrequestimport-3114.html

Zip files:

<systemname>_<interfaceid>_Aap_YYYYMMDDhhmmds.zip

APPaymentRequests.csv

APPaymentRequests.properties

Properties files contents:

/oracle/apps/ess/financials/payables/invoices/transactions,APXPRIMPT,PC_INT388.1_AP_20250721021929,

300000005780278,#NULL,1000,PC,#NULL,N,300000005649365,#NULL,1

---

3. Upload to Fusion Using ERP Adapter

• Operation: Import Bulk data into Oracle Erp Cloud - Import Payables Payment Requests
• Adapter: ERP Integration Adapter
• Required Info: UCM Account: fin$/payables$/import$
• Enable callback

---

4. Callback: Invoice Load Completed

• Callback Notification from Fusion: OIC receives callback with Load Interface ID.

Import Payables Payemnt Requests does not have any filter so we need to use ERP Integration Inbound Event. So that we can apply filter and identify for which system we are getting the callback.

Reference:

https://docs.oracle.com/en/cloud/paas/application-integration/erp-adapter/different-multiple-callbacks-same-fbdi-job.html

---

5. Call BI Report to Get Invoice Status

• API: Oracle BI Report Web Service
• Report Path: /Custom/AP/Invoice_Status_Report.xdo
• Request:

<soapenv:Envelope ... >
  <soapenv:Body>
    <runReport>
      <reportRequest>
        <reportAbsolutePath>/Custom/AP/Invoice_Status_Report.xdo</reportAbsolutePath>
        <parameterNameValues>
          <item>
            <name>p_load_interface_id</name>
            <values>
              <item>1234567</item>
            </values>
          </item>
        </parameterNameValues>
      </reportRequest>
    </runReport>
  </soapenv:Body>
</soapenv:Envelope>

• Response: XML/CSV with invoice number, status, rejection reason.

BIP report sql query:

https://soalicious.blogspot.com/2025/07/oic-tracking-ap-invoice-import-status.html?m=1

---

6. Send Invoice Status Back to Source

• Method: POST
• Payload (Sample):

{
  "transactions": [
    {
      "invoiceNumber": "PC-1003423424",
      "status": "Invoice Created",
      "reason": "Bank details are missing",
      "date": "01.07.2025"
    }
  ]
}

---

7. Callback - Payables Payment Created

Since the PPR (Payment Process Request) process in ERP does not emit events for Payables Payment Created, we need to rely on the Payment File Created event instead.

• Trigger: Payment created event in Fusion

Filter used:

<xpathExpr xmlns:ns0="http://xmlns.oracle.com/apps/financials/payments/fundsDisbursement/payments/model/entity/events">

  $eventPayload/ns0:PaymentProcessProfileName/ns0:newValue/@value = 'ABC_HSBC_ACH_HKD_PROF_PC'

</xpathExpr>

Call Fusion Rest API: Get Cheque id

GET /fscmRestApi/resources/11.13.18.05/payablesPayments

• Call Fusion REST API: Get Child Invoices of Payment

• GET /fscmRestApi/resources/11.13.18.05/payablesPayments/{CheckId}/child/relatedInvoices

• Send Status to Source (Sample Payload):

{
  "transactions": [
    {
      "invoiceNumber": "PC-1003423424",
      "status": "Payment Created",
      "reason": "",
      "date": "01.07.2025"
    }
  ]
}

---

8. Callback - Payables Payment Voided

• Trigger: Voided payment notification from Fusion.

Call REST API: Get related invoices for the Voided Payment.

• API: GET /fscmRestApi/resources/11.13.18.05/payablesPayments/{CheckId}/child/relatedInvoices

Call Fusion REST API: Cancel Invoice

• API: POST /fscmRestApi/resources/11.13.18.05/invoices/action/cancelInvoice

Notify Source System

• POST Payload:

{
  "transactions": [
    {
      "invoiceNumber": "PC-1003423424",
      "status": "Payment Voided",
      "reason": "",
      "date": "01.07.2025"
    }
  ]
}

---

🔹 Final Integration Architecture

1. Inbound:
   • Source → OIC (REST API with AP data)
   • OIC → Fusion (FBDI ZIP via ERP Adapter)
2. Outbound:
   • OIC → BI Report → Invoice Status → Source
   • Fusion → OIC (Callbacks for Invoice Load, Payment Created, Payment Voided)
   • OIC → Fusion REST (to fetch/cancel)
   • OIC → Source (REST POST with status updates)

Next pending:

How to add Filters while subscribing events for specific system?