OIC - Tracking AP Invoice Import Status Using SQL for BI Publisher and OIC Integration

Use Case:

In Oracle Integration Cloud (OIC), when integrating accounts payable (AP) invoice data into Oracle Fusion ERP using the AP Payment Request Bulk Import process, it’s important to track which invoices have been successfully imported and which failed.

To automate this tracking, a BI Publisher Report is created with a SQL query that pulls invoice status from the AP_PAYMENT_REQUESTS_INT and AP_INTERFACE_REJECTIONS tables. The report is scheduled to output a CSV file, which is then picked up by OIC and sent to the target system or vendor for further processing or reconciliation.

---

Solution Steps:

SQL Query:

SELECT

    aii.invoice_num INVOICE_NUMBER,

    air.REJECTION_MESSAGE REJECTION_MESSAGE,

    CASE

        WHEN aii.STATUS = 'PROCESSED'

        THEN 'SUCCESS'

        ELSE 'ERROR'

    END IMPORT_STATUS

FROM

    AP_PAYMENT_REQUESTS_INT aii,

    AP_INTERFACE_REJECTIONS air

WHERE 1 = 1

AND air.parent_table = 'AP_PAYMENT_REQUESTS_INT'

AND aii.PAYMENT_REQUEST_INTERFACE_ID = air.parent_id

AND aii.LOAD_REQUEST_ID IN (:P_RequestId)

UNION

SELECT

    aii.invoice_num INVOICE_NUMBER,

    null REJECTION_MESSAGE,

    'SUCCESS' IMPORT_STATUS

FROM

    AP_PAYMENT_REQUESTS_INT aii

WHERE 1 = 1

AND aii.PAYMENT_REQUEST_INTERFACE_ID NOT IN (

    SELECT parent_id

    FROM AP_INTERFACE_REJECTIONS

    WHERE parent_table = 'AP_PAYMENT_REQUESTS_INT'

)

AND aii.STATUS = 'PROCESSED'

AND aii.LOAD_REQUEST_ID IN (:P_RequestId)

1. SQL Query Overview

The SQL query checks the status of each invoice request using a UNION of two SELECTs:

• First part: Fetches both successful and rejected records by joining the interface table and rejection table.
• Second part: Captures the remaining successful invoices not found in the rejection table.

Key Components:

• invoice_num → The invoice number submitted.
• REJECTION_MESSAGE → Error message if the record failed.
• IMPORT_STATUS → 'SUCCESS' or 'ERROR' based on processing status or rejection presence.
• :P_RequestId → Bind variable to dynamically filter the results for a specific load request.

---

2. Report Execution in BI Publisher

• The SQL is used as a Data Set in the BI Publisher Data Model.
• It takes the LOAD_REQUEST_ID as a parameter (:P_RequestId) from the OIC process.
• The report is scheduled or triggered from OIC to output a CSV file.

---

3. Integration with OIC

• OIC receives the CSV output file from BI Publisher.
• Parses the invoice import result file.
• Sends the status and details (e.g., rejection reason) back to the source system or vendor.

---

Benefits:

• Automated visibility into which AP invoices were processed and which failed.
• Improved traceability and reconciliation with source systems.
• Reduces manual intervention and error tracking time.