Supplier Attachment Data: SQL query to extract the supplier attachment in oracle cloud

Managing supplier information in Oracle Cloud is critical for efficient procurement and robust auditing. While the standard user interface allows you to view and manage supplier attachments one by one, there's often a need for bulk extraction of attachment details. Whether for audit purposes, data migration, or comprehensive reporting, a direct SQL query can be a game-changer.

This post will guide you through the process of getting all the relevant metadata about attachments linked at the supplier profile level in Oracle Cloud Fusion Applications.

SQL Query to Get Supplier Attachment Metadata in oracle cloud 

To overcome the limitations of the UI, we can directly query the underlying Oracle Fusion Procurement database tables. The key tables involved in storing supplier profile data and attachment details are:

• POZ_SUPPLIERS_V: This view provides core supplier information, including supplier name and number.
• FND_ATTACHED_DOCUMENTS: This crucial table links attachments to various Oracle entities (like suppliers) via ENTITY_NAME and PK1_VALUE (the primary key of the entity, in this case, VENDOR_ID).
• FND_DOCUMENTS_VL: Provides details about the actual document, such as the document ID and data type (File, URL, Text).
• FND_DOCUMENTS_TL: Stores the translatable information about the document, including its title and description.

By joining these Oracle Cloud tables, we can construct a comprehensive query to retrieve all the necessary supplier attachment metadata.

Here is the SQL query to extract attachment details from the supplier profile level:

SELECT 

    ps.VENDOR_ID,

    hp.PARTY_NAME AS Supplier_Name,

    ps.ENABLED_FLAG AS Supplier_Active_Status,

    ps.SEGMENT1 AS Supplier_Number,

    ps.END_DATE_ACTIVE AS Inactive_Date,

    fdt.FILE_NAME AS Attachment_Name,

    fdt.CREATION_DATE AS Attachment_Added_Date

FROM 

    POZ_SUPPLIERS ps

JOIN 

    HZ_PARTIES hp ON ps.PARTY_ID = hp.PARTY_ID

LEFT JOIN 

    HZ_CONTACT_POINTS hcp ON hcp.OWNER_TABLE_ID = hp.PARTY_ID 

    AND hcp.CONTACT_POINT_TYPE IN ('EMAIL', 'PHONE')

-- Changed LEFT JOIN to INNER JOIN below

INNER JOIN 

    FND_ATTACHED_DOCUMENTS fad ON fad.PK1_VALUE = TO_CHAR(ps.VENDOR_ID)

    AND fad.ENTITY_NAME = 'POZ_SUPPLIERS'

INNER JOIN 

    FND_DOCUMENTS_TL fdt ON fad.DOCUMENT_ID = fdt.DOCUMENT_ID

    AND fdt.LANGUAGE = 'US'

Note: The SQL query above is designed to extract metadata about attachments (like name, description, category, and URL if it's a URL type). It does not directly download the file content (BLOBs) in a readable format. For bulk downloading of actual file content from Oracle Cloud's Universal Content Management (UCM), you would typically need to use tools like the External Data Loader Client (EDLC) or integrate with Oracle Integration Cloud (OIC).

How to Use This Query in Oracle Cloud

You can execute this SQL query in various environments within Oracle Cloud:

1. SQL Developer / SQL Plus: If you have direct database access or access through a reporting schema.
2. Oracle BI Publisher (BIP) Data Model: This is a common and recommended approach in Oracle Cloud. You can create a data model using this SQL, then build reports (e.g., Excel, PDF) on top of it to easily export the data.
3. Visual Builder Studio (VBS) or APEX: For more complex applications or integrations where you need to display or process this data.

Remember to have the necessary database privileges (e.g., select privileges on the mentioned tables/views) to run this query successfully.