OIC - Handling Mixed File Inputs in Oracle Integration (OIC): Smart Zip Check and Transfer to SFTP

Use Case

In real-world integrations, especially with ERP or external systems, files received from source applications may vary in format — some may already be zipped while others may not. To ensure consistency and avoid errors during downstream processing or transfer to systems like SFTP, we need a way to check if a file is zipped and handle it accordingly.

This use case demonstrates an Oracle Integration Cloud (OIC) integration that intelligently detects whether an incoming file is a zipped archive. If the file is already zipped, it is forwarded directly to the target SFTP server. If it's not zipped, the integration compresses it and then transfers it to the target location.

---

Solution Steps

1. Scope: CheckZipFile

   • A scope that encapsulates the logic for file evaluation and error handling.

2. Main Flow:

   • Stage File – UnzipFile:
     Tries to unzip the incoming file.
     • If successful, it means the file was zipped. No further compression is needed.
     • If it fails (i.e., the file isn't a zip), an exception is thrown.

3. Fault Handler: Default Handler

   • Stage File – ZipFile:
     This step is triggered only when UnzipFile fails, meaning the file wasn't zipped. The step compresses the incoming file.
   • Stitch – AssignFileRef:
     Assigns or updates the file reference to point to the newly zipped version for further processing or transfer.

4. Downstream Processing 

   • The processed (either original zipped or newly zipped) file is sent to the target SFTP or other endpoints.

---

Benefits

• Flexibility: Handles both zipped and non-zipped files without requiring format enforcement at the source.
• Error Handling: Robust fallback logic ensures no failure in case of unexpected file formats.
• Automation Ready: Ideal for file-based B2B integrations or scheduled ERP exports.

OIC - Smart File Polling from OCI Object Storage with Dynamic Day-Based Logic and Datadog Logging

Use Case

A business requirement involves polling files from Oracle Cloud Infrastructure (OCI) Object Storage, processing them to a target file system via Agent, and logging any missing files in Datadog. The file polling count dynamically varies based on the day of the week:

• Friday: Expect 3 files
• Saturday: Expect 1 file
• Other days: Can be triggered manually via an adhoc flag

The solution ensures resilience through structured error handling and JSON-driven logic that categorizes files into MissingFiles and ProcessFiles.

Flow diagam

---

Solution Architecture Overview

The solution is designed using 3 OIC integrations and a supporting JSON structure:

{
  "MissingFiles": [
    {
      "FileName": ""
    }
  ],
  "ProcessFiles": [
    {
      "FileName": ""
    }
  ]
}

🔁 Integration 1: Scheduler Integration

• Purpose: Triggers the flow based on the scheduled time or ad-hoc execution.
• Steps:
  • Runs on a schedule (typically daily).
  • Accepts a flag adhocExecution = Y/N to override weekday logic.
  • Calls the Main Integration.

🔧 Integration 2: Main File Polling Integration

• Purpose: List and categorize files from OCI Object Storage.
• Steps:
1. List all files from a configured object storage bucket.
2. Determine required file count based on:
   • Day of the week (Friday = 3, Saturday = 1).
   • adhocExecution = Y allows polling on other days.
3. Compare expected vs actual files.
4. Populate a JSON object:
   • ProcessFiles: Files found and ready to process.
   • MissingFiles: Files not found (expected but missing).
5. For each ProcessFile, invoke the Child Integration.
6. Log MissingFiles to Datadog using REST API/log collector.

Missed file - throw fault

XSlT code for validating process files vs missed files

<xsl:template match="/xml:id_11">

  <ns1grp:write xml:id="id_117">

    <ns31:request-wrapper>

      <xsl:if test="($Var_dayOfWeek = &quot;Friday&quot;)">

        <xsl:if test="not($FileRef_Var/nsmpr0:response-wrapper/nsmpr0:objects[nsmpr0:name='BP_IDENTITY.csv'])">

          <ns31:MissingFiles>

            <ns31:FileName>

              <xsl:value-of select="'BP_IDENTITY.csv'"/>

            </ns31:FileName>

          </ns31:MissingFiles>

        </xsl:if>

        <xsl:if test="not($FileRef_Var/nsmpr0:response-wrapper/nsmpr0:objects[nsmpr0:name='BP_CREDITCARD.csv'])">

          <ns31:MissingFiles>

            <ns31:FileName>

              <xsl:value-of select="'BP_CREDITCARD.csv'"/>

            </ns31:FileName>

          </ns31:MissingFiles>

        </xsl:if>

        <xsl:if test="not($FileRef_Var/nsmpr0:response-wrapper/nsmpr0:objects[nsmpr0:name='DLP_CA_NUM.csv'])">

          <ns31:MissingFiles>

            <ns31:FileName>

              <xsl:value-of select="'DLP_CA_NUM.csv'"/>

            </ns31:FileName>

          </ns31:MissingFiles>

        </xsl:if>

      </xsl:if>

      <xsl:if test="($Var_dayOfWeek = &quot;Saturday&quot;)">

        <xsl:if test="not($FileRef_Var/nsmpr0:response-wrapper/nsmpr0:objects[nsmpr0:name='BP_NAMES.csv'])">

          <ns31:MissingFiles>

            <ns31:FileName>

              <xsl:value-of select="'BP_NAMES.csv'"/>

            </ns31:FileName>

          </ns31:MissingFiles>

        </xsl:if>

      </xsl:if>

      <xsl:if test="($Var_dayOfWeek = &quot;Friday&quot;) or (/nsmpr0:execute/ns17:request-wrapper/ns17:ProcessRequest/ns17:AdhocExecutionFlag = &quot;Y&quot;)">

        <xsl:if test="$FileRef_Var/nsmpr0:response-wrapper/nsmpr0:objects[nsmpr0:name='BP_IDENTITY.csv']">

          <ns31:ProcessFiles>

            <ns31:FileName>

              <xsl:value-of select="'BP_IDENTITY.csv'"/>

            </ns31:FileName>

          </ns31:ProcessFiles>

        </xsl:if>

        <xsl:if test="$FileRef_Var/nsmpr0:response-wrapper/nsmpr0:objects[nsmpr0:name='BP_CREDITCARD.csv']">

          <ns31:ProcessFiles>

            <ns31:FileName>

              <xsl:value-of select="'BP_CREDITCARD.csv'"/>

            </ns31:FileName>

          </ns31:ProcessFiles>

        </xsl:if>

        <xsl:if test="$FileRef_Var/nsmpr0:response-wrapper/nsmpr0:objects[nsmpr0:name='DLP_CA_NUM.csv']">

          <ns31:ProcessFiles>

            <ns31:FileName>

              <xsl:value-of select="'DLP_CA_NUM.csv'"/>

            </ns31:FileName>

          </ns31:ProcessFiles>

        </xsl:if>

      </xsl:if>

      <xsl:if test="($Var_dayOfWeek = &quot;Saturday&quot;) or (/nsmpr0:execute/ns17:request-wrapper/ns17:ProcessRequest/ns17:AdhocExecutionFlag = &quot;Y&quot;)">

        <xsl:if test="$FileRef_Var/nsmpr0:response-wrapper/nsmpr0:objects[nsmpr0:name='BP_NAMES.csv']">

          <ns31:ProcessFiles>

            <ns31:FileName>

              <xsl:value-of select="'BP_NAMES.csv'"/>

            </ns31:FileName>

          </ns31:ProcessFiles>

        </xsl:if>

      </xsl:if>

    </ns31:request-wrapper>

  </ns1grp:write>

</xsl:template>  

Integration 3: Child File Processor

• Purpose: Handles individual file transfer and cleanup.
• Steps:
• Download file from OCI Object Storage.
• Write the file to a local file system via Agent.
• Delete the file from OCI Object Storage post-processing.

---

Key Highlights

• ✅ Dynamic logic using weekday and ad-hoc flags.
• ✅ Robust processing pipeline using JSON mapping and loop controls.
• ✅ Clean-up mechanism ensures files aren't reprocessed.
• ✅ Monitoring integration using Datadog for transparency and alerting.

OIC - Streamlined OIC Scheduled Integration for GL Fuel Cost Data Using ESS Job Callback and Event-Based File Processing Without Timeouts

Use Case

The organization needs to automate the extraction and delivery of GL Fuel Cost data generated from a BI Publisher report in Oracle Fusion ERP. The integration should be efficient, scalable, and must avoid long-running processes and timeouts within Oracle Integration Cloud (OIC).

The requirement includes:

• Scheduled trigger to initiate the process.
• Submission of the BI report using the Export ESS Job Wrapper.
• Callback-based integration to handle ESS job completion.
• Document download, decode, unzip, and CSV parsing.
• Transformation and delivery of the processed data to a target system.

---

Optimized Solution Approach (Without Timeout)

1. Scheduled Integration – Submit ESS Job Only

• Create a Scheduled Orchestration Integration in OIC.
• Trigger the Export ESS Job Wrapper using the ERP Cloud Adapter.
• Service: ErpIntegrationService
• Operation: exportBulkData
• Pass the following information:
•  JobName: "JobPackageName,JobDefinitionName"
• parameterList: concat(param1,param2)
• JobOptions:  EnableEvent= Y to ensure callback is published.
• Do not wait for completion in the same flow — this avoids long runtime and timeouts.

2. Callback Integration – Listen to ERP Event

• Create a separate App-Driven Integration.
• Use ERPIntegrationOutboundEvent as the trigger.
• Apply a filter condition on Job Name or Report Path to restrict to only GL Fuel Cost report completions.

3. Get Report Output via Document ID

• From the event payload, extract the Document ID.
• Call the GetDocumentForDocumentId operation using ERP Cloud Adapter.
• Retrieve the output as a base64-encoded ZIP.

4. Decode and Unzip File

• Use OIC's base64 decode function.
• Use Stage File – Unzip File action to extract files from the archive.
• Use a For-Each loop to iterate through unzipped files.

5. Parse CSV and Transform Data

• For files with .csv extension:
• Use Stage File – Read File to parse contents.
• Transform the data into the required target structure using Mapper.

6. Deliver Data to Target System

• Send the formatted data to the external target (via REST, FTP, etc.).
• Include proper error handling, logging, and retry logic for robustness.

---

Benefits of This Approach

• Avoids Timeout: Processing is split between scheduler and callback flow, preventing long execution.
• Event-Driven: Ensures OIC listens and responds only when the ESS job completes successfully.
• Modular Design: Easier to maintain and enhance.
• Optimized Performance: Resource-light scheduled flow, heavier processing is deferred to callback.

Ess job sample page:

OIC - How to add Filters while subscribing ERP business events in Oracle Integration

To add filters for the “specific” event in Oracle Integration Cloud (OIC), follow the steps below. These filters ensure that only relevant events are processed — e.g., only payments voided for a specific business unit or source system.

---

✅ Steps to Add Filters for “Payables Payment Voided” Event in OIC

Step 1: Create or Edit the Integration

1. Open your OIC integration where you want to subscribe to the event.
2. Use the ERP Cloud Adapter as a trigger.
3. Choose the event:
   Business Event → Payables Payment Voided

---

Step 2: Configure the Event Adapter and Add Filter

After selecting the event:

1. On the "Basic Info" screen, click Next.
2. On the "Event Details" screen, scroll down to the Filter Expression section.
3. You’ll see an “Add Filter Expression” button.

🔍 Example Payload Fields for Payables Payment Voided:

When the event is triggered, the payload might contain fields like:

<env:Body>
  <ns0:ApPaymentVoidedInfo 

xmlns:ns0="http://xmlns.oracle.com/apps/financials/payables/shared/paymentFile/common/publicEvents/ApPaymentVoided">
    <ns0:CheckId>121001</ns0:CheckId>
    <ns0:PaymentTypeFlag>A</ns0:PaymentTypeFlag>
    <ns0:OrgId>300000057802xxx</ns0:OrgId>
    <ns0:VendorName>ABC</ns0:VendorName>
    <ns0:VendorSiteCode/>
    <ns0:PartyId>100000286292xxx</ns0:PartyId>
    <ns0:PartySiteId>1000002862943xxx</ns0:PartySiteId>
    <ns0:CheckNumber>51817</ns0:CheckNumber>
    <ns0:CheckDate>2025-07-07T00:00:00</ns0:CheckDate>
    <ns0:Amount>1000</ns0:Amount>
    <ns0:CurrencyCode>HKD</ns0:CurrencyCode>
    <ns0:PaymentMethod>E_ACH_EPS_BATCH</ns0:PaymentMethod>
    <ns0:PaymentStatus>VOIDED</ns0:PaymentStatus>
    <ns0:BankAccountNum>XXXXXXXXX73303</ns0:BankAccountNum>
    <ns0:VoidDate>2025-07-07T00:00:00</ns0:VoidDate>
  </ns0:ApPaymentVoidedInfo>
</env:Body>

---

Step 3: Add Filter Expression

Run the integration once without filters, log the entire incoming payload using Logger, and confirm which keys you want to filter on. Use the body namespace here.

Sample 1: Check the payment method.

<xpathExpr xmlns:ns0="http://xmlns.oracle.com/apps/financials/payables/shared/paymentFile/common/publicEvents/ApPaymentVoided">

$eventPayload/ns0:PaymentMethod = 'E_ACH_EPS_BATCH_PC'

</xpathExpr>

Sample 2: substring and filter

<xpathExpr xmlns:ns0="http://xmlns.oracle.com/apps/financials/payables/shared/paymentFile/common/publicEvents/ApPaymentVoided">
substring($eventPayload/ns0:PaymentMethod, string-length($eventPayload/ns0:PaymentMethod) - 1) = 'PC'
</xpathExpr>

Sample 3:  for Any Import bulkFBdI use, ERP Integrtaion Inbound Event

<xpathExpr xmlns:ns0="http://xmlns.oracle.com/apps/financials/commonModules/shared">

  starts-with($eventPayload/ns0:JOBS[ns0:JOBNAME='Load Interface File for Import']/ns0:DOCUMENTNAME,

              'PC_test_AP')

</xpathExpr>

---

Why $eventPayload is used in <xpathExpr> in OIC:

In Oracle Integration Cloud (OIC) event subscriptions, when you're writing a filter expression inside <xpathExpr>, Oracle automatically binds the incoming event data (the event payload) to a variable named:

$eventPayload

This variable is your entry point to access the fields from the event payload using XPath expressions.

---

⚠️ Important Notes:

• Fields must match payload field names exactly (case-sensitive).
• Use AND, OR, =, !=, etc., as supported operators.
• If unsure of available fields, log the payload in your integration to inspect its structure.

---

✅ Best Practice:

Run the integration once without filters, log the entire incoming payload using Logger, and confirm which keys you want to filter on.

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?

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.

OIC - Extract RSA Public Key from a Certificate – Two Easy Methods (OpenSSL & Java)

📌 Use Case:

When integrating with third-party platforms (e.g., Oracle Integration Cloud, REST APIs with JWT, or SAML), you often receive a certificate. To validate tokens or signatures, you must extract the RSA public key from an X.509 certificate file (like .cer or .crt).

---

✅ Solution Steps

🔽 Input:

• A .cer or .crt file (Base64-encoded X.509 format)
• Goal: Extract the RSA public key in readable format

---

🔧 Option 1: Using OpenSSL (Command Line)

📥 Steps:

1. Save your certificate as cert.pem (Base64 X.509 format).
2. Run this command:

openssl x509 -in cert.pem -pubkey -noout > public_key.pem

✅ Output:

-----BEGIN PUBLIC KEY-----
MIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKCAQE...
-----END PUBLIC KEY-----

📎 Notes:

• Works on Linux/macOS/WSL/Windows with OpenSSL installed
• Easy to use in automation scripts

---

☕ Option 2: Using Java Code (Without OpenSSL)

📥 Steps:

1. Convert .cer File to Base64 Text:

• Open your .cer file (which is binary) in any Base64 encoder (e.g., PowerShell, online tool, or base64 CLI).
• It should look like this:

-----BEGIN CERTIFICATE-----
MIIDczCCAlugAwIBAgIEXV...<trimmed>...C2s85w==
-----END CERTIFICATE-----

2. Copy Only the Certificate Key Part:

• Copy the middle Base64 key part (remove headers and newlines).
• Store it in a Java string like base64Cert in the code below.

---

✅ Java Code:

import java.io.ByteArrayInputStream;
import java.security.PublicKey;
import java.security.cert.CertificateFactory;
import java.security.cert.X509Certificate;
import java.util.Base64;

public class RSAPublicKeyExtractor {
    public static void main(String[] args) throws Exception {
        // Step 1: Paste your base64-encoded certificate string here
        String base64Cert = 
            "MIIDczCCAlugAwIBAgIEXV...<full cert key here>...C2s85w==";

        // Step 2: Decode the base64 string
        byte[] certBytes = Base64.getDecoder().decode(base64Cert);

        // Step 3: Convert to X.509 certificate
        CertificateFactory certFactory = CertificateFactory.getInstance("X.509");
        X509Certificate cert = (X509Certificate) certFactory
            .generateCertificate(new ByteArrayInputStream(certBytes));

        // Step 4: Extract public key
        PublicKey publicKey = cert.getPublicKey();

        // Step 5: Print the public key details
        System.out.println("Public Key Algorithm : " + publicKey.getAlgorithm());
        System.out.println("Public Key Format    : " + publicKey.getFormat());
        System.out.println("Public Key (Base64)  : ");
        System.out.println(Base64.getEncoder().encodeToString(publicKey.getEncoded()));
    }
}

🧾 Sample Output:

Public Key Algorithm : RSA
Public Key Format    : X.509
Public Key (Base64)  :
MIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKCAQEArpM...