Bring AP Invoice Attachment URLs into Fusion Data Intelligence

Use Case

In financial workflows, especially in Accounts Payable (AP), users often need to validate invoices by checking supporting documents like scanned copies, payment proofs, or contracts. However, attachment URLs are not readily available in prebuilt FDI subject areas.

Oracle Fusion Data Intelligence (FDI) enables this through the Data Augmentation and Semantic Model Extension (SME) capabilities. This article demonstrates how to expose attachment URLs for AP invoices, allowing you to access document links directly from dashboards—reducing manual effort and improving traceability.

Prerequisites

• Access to FDI
• Ensure ‘SME Options for Data Augmentation’ is enabled.
• Required roles to access semantic model extension
• Knowledge of the PVOs involved:
  • FndAttachedDocumentExtractPVO
  • FndDocumentExtractPVO
  • FndDocumentTranslationExtractPVO

Steps Summary

• Identify PVOs: Use the Fusion PVOs that expose document details
• Define join logic for AP invoice attachments
• Capture Fusion Domain details
• Create the Invoice Details view
• Augment Data: Use FDI’s Data Augmentation
• Create views using Augmented Tables
• Extend the semantic model – Create Logical Star and Custom subject area
• Publish and validate the subject area

1. Identify required PVOs

With Oracle Fusion Cloud Applications Release 25A, Oracle made the following PVOs available that allow access to document metadata and URLs:

HcmTopModelAnalyticsGlobalAM.HCMExtractAM.CommonBiccExtractAM.FndAttachedDocumentExtractPVO

HcmTopModelAnalyticsGlobalAM.HCMExtractAM.CommonBiccExtractAM.FndDocumentExtractPVO

HcmTopModelAnalyticsGlobalAM.HCMExtractAM.CommonBiccExtractAM.FndDocumentTranslationExtractPVO

These PVOs correspond to Fusion application tables:

PVO	Application Table
FndAttachedDocumentExtractPVO	FND_ATTACHED_DOCUMENTS
FndDocumentExtractPVO	FND_DOCUMENTS
FndDocumentTranslationExtractPVO	FND_DOCUMENTS_TL

 

By joining these three PVOs, you can construct a complete view of the attachments, including the attachments URL.
 

2. Define join logic for AP invoice attachments

To limit the scope to AP invoice attachments, use the following logic:

FndAttachedDocumentExtractPVO.ENTITY_NAME = ‘AP_INVOICES’

FndAttachedDocumentExtractPVO.PK1_VALUE = AP_INVOICES_ALL.INVOICE_ID

Join relationships between the PVOs are:

FndAttachedDocumentExtractPVO.DOCUMENT_ID = FndDocumentExtractPVO.DOCUMENT_ID 

FndDocumentExtractPVO.DOCUMENT_ID = FndDocumentTranslationExtractPVO.DOCUMENT_ID

These relationships map metadata like file name, title, creation date, and most importantly, the document access URL.

3. Capture Fusion Domain details

In Oracle Fusion Cloud Applications, domain information is stored in the internal table fusion.ask_deployed_domains, so custom data capture table is created in the ADW and manually inserted the required domain details into it.

Table name: DW_FA_X_FUSION_DEPLOYED_DOMAINS

This table stores essential domain metadata. This use case specifically uses the following column:

EXTERNAL_VIRTUAL_HOST — used to construct external-facing document links dynamically.

4. Create the Invoice Details View

The following view is created using the predefined ADW tables available in FDI. It joins invoice, supplier site, and party dimension tables to retrieve key attributes such as vendor ID, vendor site ID, and invoice number. This simplifies reporting and analysis related to supplier invoices.

CREATE OR REPLACE VIEW DW_FA_X_AP_INVOICE_VIEW AS

SELECT

    inv.invoice_number     AS invoice_number,

    inv.invoice_id         AS invoice_id,

    site.supplier_id       AS vendor_id,

    site.supplier_site_id  AS vendor_site_id

FROM

    oax$oac.dw_ap_invoice_cf_sec inv

JOIN

    oax$oac.dw_supplier_site_d site

    ON inv.supplier_site_id = site.supplier_site_id

JOIN

    oax$oac.dw_party_d party

    ON site.party_id = party.party_id;

5. Augment Data: Use FDI’s Data Augmentation

Data Augmentation in FDI allows you to import additional data — beyond the default FDI subject areas — from Oracle Fusion Cloud Applications. This is especially useful when required analytics fields or tables exist in the underlying application but are not included in the pre-packaged model.

To support reporting on document metadata, begin by augmenting three predefined PVOs available in Oracle Fusion Applications.

• FndAttachedDocumentExtractPVO → Augmented as DW_FA_X_URL (used as the fact table)
• FndDocumentExtractPVO → Augmented as DW_FA_X_DOC_URL
• FndDocumentTranslationExtractPVO → Augmented as DW_FA_X_DOC_TL_URL

6. Create the views on augmented tables

A view named DW_FA_X_DOC_URL_DETAILS_V is then created by joining the two augmented dimension tables — DW_FA_X_DOC_URL and DW_FA_X_DOC_TL_URL.

CREATE OR REPLACE VIEW DW_FA_X_DOC_URL_DETAILS_V AS

SELECT

    fdt.documentid,

    fdt.url,

    fdt.uri,

    fdt.title,

    fdt.dmversionnumber,

    REPLACE(

        REPLACE(

            CASE

                WHEN fdt.URL IS NOT NULL THEN fdt.URL

                WHEN fdt.URI IS NOT NULL THEN

                    (SELECT external_virtual_host

                     FROM DW_FA_X_FUSION_DEPLOYED_DOMAINS

                     WHERE deployed_domain_name = ‘FADomain’) || fdt.URI

                WHEN fdt.TITLE IS NOT NULL THEN

                    (SELECT external_virtual_host

                     FROM DW_FA_X_FUSION_DEPLOYED_DOMAINS

                     WHERE deployed_domain_name = ‘FADomain’) ||

                    ‘/cs/idcplg?IdcService=GET_FILE=’ || fdt.DMVERSIONNUMBER

                ELSE NULL

            END,

        ‘<old_domain1>’, ‘<new_host>’

        ),

    ‘<old_domain2>’,

    (SELECT external_virtual_host

     FROM DW_FA_X_FUSION_DEPLOYED_DOMAINS

     WHERE deployed_domain_name = ‘FADomain’)

    ) AS INVOICE_IMAGE_LINK

FROM

    oax$oac.dw_fa_x_doc_url fd,

    oax$oac.dw_fa_x_doc_tl_url fdt

WHERE

    fd.documentid = fdt.documentid;

Together, these components form a data model for reporting on attached documents with relevant supplier and invoice attributes.

7. Extend the Semantic Model

Once invoice attachment data is ingested via data augmentation and views creation, the next task is to expose it through a semantic layer for analysis. This is achieved using the Semantic Model Extensions (SME) capability in FDI.

• Go to Semantic Model Extensions.
• Create a new sandbox and create a new logical star.
  • DW_FA_X_URL as the fact table
  • DW_FA_X_DOC_URL_DETAILS_V as the first dimension for URL details
  • DW_FA_X_AP_INVOICE_VIEW as the second dimension for vendor/invoice details
• Add new subject area; for example, AP Invoice Image URL Details.

 

 

 

After applying and publishing the sandbox created in earlier steps, you can navigate to the catalog and search for the custom subject area (for example, AP Invoices Image URL Details). Open it in the reporting tool and validate by checking:

Conclusion

By combining the Data Augmentation and Semantic Model Extensions capabilities, you can expose the AP Invoice attachment URLs in FDI. This approach is scalable, easy to maintain, and unlocks a new level of transparency and insight into invoice processing.

Call to Action

Refer to these resources:

• Oracle Analytics Lounge
• FDI Documentation
• Customize Oracle Fusion Data Intelligence

Now that you’ve read this article, try it yourself and let us know your results in the Oracle Analytics Community, where you can also ask questions and post ideas. It’s free and easy to join!