[OIC] Automating AP Invoice Attachment Downloads: Oracle Fusion, BIP, and OIC

 

Extracting invoice attachments in bulk is a common requirement for auditing and archival. This post details how to automate this using Business Intelligence Publisher (BIP) to identify documents and the Universal Content Management (UCM) SOAP API to retrieve them within Oracle Integration Cloud (OIC).

Step 1: Create a BIP Report for Attachment Metadata

Before downloading, you need the unique identifiers for the files stored in Oracle's UCM.

Below is the query which you can use to get the attachment details:

select /*+ parallel(aia) parallel(fad) parallel(fd) parallel(fdt) */

     aia.invoice_id                             PK1_VALUE

    ,aia.invoice_num                            PK2_VALUE

    ,fad.entity_name                            ENTITY_NAME

    ,fd.document_id                             DOCUMENT_ID

    ,fdt.description                            DESCRIPTION

    ,fdt.title                                  TITLE

    ,fdt.dm_version_number                      DM_VERSION_NUMBER

    ,fdt.dm_document_id                         DM_DOCUMENT_ID

    ,fdt.dm_repository                          DM_REPOSITORY

    ,fdt.file_name                              FILE_NAME

    ,fdt.dm_type                                DM_TYPE

    ,fdt.object_size                            OBJECT_SIZE

    ,fdt.object_content_type                    OBJECT_CONTENT_TYPE

    ,to_char(aia.invoice_date,'MM-dd-yyyy')     INVOICE_DATE

from

     ap_invoices_all aia

    ,fnd_attached_documents fad

    ,fnd_documents fd

    ,fnd_documents_tl fdt

where 1=1

--and aia.invoice_num='CHM_MSI_1782_9000081'

and aia.invoice_id          = fad.pk1_value

and fad.entity_name         ='AP_INVOICES_ALL'

and fad.document_id         = fd.document_id

and fd.document_id          = fdt.document_id

and fdt.object_content_type ='application/pdf'

and invoice_date between :P_FROM_DATE and :P_TO_DATE

Sample Output:

• Now, you can create an OIC Integration which will first run this report and get the data of invoice attachments. Then insert this data in ATP table:

• Loop over these records and then use the Oracle ERP Adapter

• Then use the ERP Cloud Adapter follow these steps:
• Select the Query, Create, Update or Delete Information
• Select Browse By as Services
• Select the Service ErpIntegrationService
• Select operation as getDocumentForDocumentId
• Map DM VERSION NUMBER to the Document Id

• Once document is downloaded we will decode it (as the content will be in base64). We will be using Stage write activity with content structure type using XML Schema (XSD) document using opaque schema.

• Below is the opaque_schema.xsd file:

<?xml version = '1.0' encoding = 'UTF-8'?>  

<schema targetNamespace="http://xmlns.oracle.com/pcbpel/adapter/opaque/"

            xmlns="http://www.w3.org/2001/XMLSchema" >

      <element name="opaqueElement" type="base64Binary" />

     

</schema>

• Map the Content element to opaque element in mapper

• After writing the stage file you can use it like sending as an attachment using the notification activity