How to fetch Attachment content in BI Report in Oracle Fusion

 Hello Everyone,

In this post let's us see how to fetch attachment content in BI Reports from UCM server

Let us consider fetching Attachment details of AP Invoices

Step1: In data model, Click on "New Data Set"

Select "SQL Query"

Step 2: Use the following query and click "ok"

select 

fdv.* 

from

fnd_attached_documents fad,

fnd_documents_vl       fdv,

AP_INVOICES_ALL        ap_invoices

where

             fad.document_id = fdv.document_id 

AND    fad.entity_name         = 'AP_INVOICES_ALL'

AND    fad.pk1_value                            = ap_invoices.invoice_id

The above query fetches attached document related details like id, version..

Step 3: Fetching document data from UCM server

Click on "New Data Set" and select "Content Server"

Step 4: Enter following data:

Name                : Specify data set name

Data Source     :  let it be "FA_UCM_PROVISIONED"

Parent Group   :  Choose the other data set group , for example I have choose G_1 which contains query from step 2

Document ID  :  Choose DM_VERSION_NUMBER

Content Type  : Text

Click "ok"

Step 5: This is how it looks

Goto results and check data, document content can be seen in the column "Document"