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.

 A Comprehensive Guide to Combination Sets in Oracle Cloud ERP

Introduction: The New Era of Validation in Oracle Fusion

Oracle Fusion's latest release has introduced a powerful feature for managing chart of accounts (COA) integrity: Combination Sets. This enhancement simplifies how users control valid segment combinations, offering a faster, more efficient alternative to traditional Cross-Validation Rules (CVRs).

In this blog, we’ll explore:

• What are Combination Sets?
• How they differ from CVRs
• When to use them
• How to configure them
• Real-world examples

What are Combination Sets in Oracle Cloud Fusion?

Combination Sets are a simplified and high-performance way to restrict segment value combinations in your Chart of Accounts (COA). Unlike CVRs, which operate at the rule level, Combination Sets allow predefined valid segment combinations to be stored in a set and then assigned to a ledger or business unit.

At its core, a Combination Set is a new type of account validation rule that allows you to define a list of allowed or disallowed combinations of segment values for your Chart of Accounts. Unlike CVRs, which are rule-based (if X then Y), Combination Sets are list-based. They focus on explicitly enumerating valid or invalid partial account combinations.

You can specify up to five validation segments within a Combination Set. This feature is particularly powerful when dealing with complex business rules that don't follow a simple pattern or when integrating with Master Data Management (MDM) systems that manage such lists.

How Do Combination Sets Work?

Combination Sets operate on a simple yet effective principle:

1. Define the Set: You create a Combination Set and specify its type:

Allow: Only the exact combinations of segment values explicitly listed within this set are valid. Any other combination involving these segments is considered invalid. This is ideal for scenarios where only a few specific combinations are permitted.

Disallow: All combinations are considered valid except for the specific combinations of segment values explicitly listed within this set. This is useful for preventing a few known problematic combinations.

 

2. Specify Validation Segments: You select the Chart of Accounts (COA) segments (up to five) that will be part of this validation set. The order of these segments should    typically mirror your COA structure for clarity.

3. Upload Combinations: You then define the specific segment value combinations      (either allowed or disallowed) within the set. This can be done manually for smaller setsor, more commonly, via File-Based Data Import (FBDI) for larger volumes. Oracle  provides templates for this purpose.

4. Activation: Once defined and combinations are loaded, the Combination Set must be activated. This involves setting its status from 'Draft' to 'Active'. For auditing purposes, you might need to enable auditing for account combination changes.

When a user attempts to create a new account combination (either through manual entry, imports, or integrations), Oracle's validation engine checks it against all active Combination Sets, CVRs, and Related Value Sets. For a combination to be valid, it must satisfy all active validation rules.

Combination Sets vs. Cross-Validation Rules (CVR): A Comparison

While both Combination Sets and CVRs aim to ensure valid account combinations, their underlying approach differs significantly:

Feature	Cross-Validation Rules (CVR)	Combination Sets
Approach	R ule-based (IF-THEN logic): Defines conditions and filters.	List-based (Explicit Lists): Defines allowed or disallowed combinations.
Complexity	Ideal for logical patterns, ranges, or hierarchical relationships. Can become complex with many conditions.	Ideal for non-patterned, discrete lists of combinations. Handles up to 5 segments.
Maintenance	Managed through UI with condition/validation filters. Spreadsheet upload available.	Managed primarily via FBDI upload for bulk combinations. UI for set definition.
Error Messages	Customizable error messages per rule.	Error messages are generic for the Combination Set.
Best Use Case	Rules like "If Company = X, then Department cannot be Y." "If Account is Revenue, then Cost Center must be non-zero."	"Only these specific combinations of Company-Department-Account are allowed." Or "These 3 combinations of Company-Project are explicitly disallowed."
Performance	Can degrade with highly complex or numerous rules that are evaluated.	Generally good for defined lists; performance benefit for disallow rules over complex CVRs.
Integration	Less direct integration with MDM for list management.	Designed to integrate well with MDM solutions managing explicit lists.

 

When Should You Use Combination Sets?

Use Combination Sets when:

• You have a fixed list of valid segment combinations.
• Performance is impacted by a large number of CVRs.
• You want to eliminate the risk of users entering invalid combinations without writing dozens of validation rules.

Avoid them if you need dynamic or conditional validations — for that, CVRs are still better.

How to configure combination Sets: A Step-by-Step Guide

Configuring Combination Sets in Oracle Cloud ERP involves navigating through the Setup and Maintenance work area. Here’s a general outline of the steps:

1. Access Cross-Validation Rules:
• From the Setup and maintenance page, select Manage Cross-Validation Rules.
• On the Manage Cross-Validations page, you'll find a new tab: Combination Sets. Click on this tab.

     2.Create a New Combination Set:

        Click the Create icon (+).

• Name: Provide a unique and descriptive name for your Combination Set.
• Description: Add a detailed description of its purpose.
• Type: Select either Allow or Disallow. Crucially, this cannot be changed after saving.
• Status: The initial status will be Draft. You'll change this to Active once your combinations are ready.
• Validation Segments: Select the segments that will be part of this Combination Set. Remember, a maximum of five segments can be chosen.

3.Save the Combination Set Definition.

4.Define and Upload Combinations:

• While on the Combination Sets tab, select your newly created (or existing) Combination Set.
• Click Download Combinations. This will generate an FBDI template (CrossValidationCombinationsImportTemplate.xlsm) for you.
• Populate the template with your desired valid or invalid segment combinations. Ensure you use the correct segment values and the appropriate "Action" (INSERT, DELETE).
• Upload the completed FBDI template using the Upload Combinations option (or Manage Account Combination Validation Rules process).

• Upload process
• On the Manage Cross-Validations page, click the Combination Sets tab
• On the Actions menu, select Upload.

• Now load the data from the interface table into the application.
• Select Manage from the Actions menu to open the Manage Account Combination Validation Rules process dialog box.

7. Activate the Combination Set:
• Once your combinations are successfully uploaded and you've reviewed them, go back to the Combination Set definition.
• Change the Status from Draft to Active.
• Important: Remember to redeploy your Chart of Accounts Flex field if you've made significant structural changes or are activating new validation types for the first time.

Examples of Allowed vs Disallowed Combinations in Combination Sets

Let's assume our Chart of Accounts has the following segments:

• Company (e.g., 1000 - US Operations, 2000 - EU Operations)
• Department (e.g., 101 - Sales, 102 - Marketing, 201 - HR)
• Account (e.g., 5000 - Salaries, 6000 - Travel, 7000 - Utilities)
• Project (e.g., P001 - New Product Launch, P002 - Q3 Campaign, P000 - No Project)

Example 1: Allow Type Combination Set

Scenario: Your company has very strict controls over capital expenditure projects. Only specific combinations of Company, Department, and Project are allowed for any capital-related accounts (e.g., 15000 - Capital Assets).

Combination Set Definition:

• Name: Capital_Project_Allowance
• Type: Allow
• Validation Segments: Company, Department, Project
• Status: Active

Combinations Loaded (via FBDI):

Company	Department	Project
1000	102	P001
1000	201	P000
2000	101	P002

 

Results:

• Allowed Combination: 1000-102-15000-P001 (This combination is valid because 1000-102-P001 is explicitly allowed in the Capital_Project_Allowance set.)
• Allowed Combination: 1000-201-15000-P000 (Valid, as 1000-201-P000 is allowed.)
• Allowed Combination: 2000-101-15000-P002 (Valid, as 2000-101-P002 is allowed.)
• NOT Allowed Combination: 1000-101-15000-P001 (Invalid. Although Company 1000 and Project P001 are in the set, the Department 101 is not part of any explicitly allowed combination for Company 1000 and Project P001 in this set. This segment combination would be rejected.)
• NOT Allowed Combination: 2000-201-15000-P000 (Invalid. The combination 2000-201-P000 is not explicitly listed as allowed in the Capital_Project_Allowance set.)
• NOT Allowed Combination: 1000-102-15000-P003 (Invalid. Project P003 is not part of any allowed combination in the set.)

 

Example 2: Disallow Type Combination Set

Scenario: Your company wants to prevent specific problematic or erroneous combinations from ever being used in the General Ledger. For example, certain expense types should never be booked to specific departments.

Combination Set Definition:

• Name: Invalid_Expense_Combos
• Type: Disallow
• Validation Segments: Department, Account
• Status: Active

Combinations Loaded (via FBDI):

Department	Account
101	6000
102	5000
201	7000

Results:

• Allowed Combination: 1000-101-5000-P001 (Valid. Department 101 with Account 5000 is not in the disallowed list. The disallowed combination for 101 is 6000.)
• Allowed Combination: 2000-102-6000-P000 (Valid. Department 102 with Account 6000 is not in the disallowed list. The disallowed combination for 102 is 5000.)
• Allowed Combination: 1000-201-6000-P002 (Valid. Department 201 with Account 6000 is not in the disallowed list. The disallowed combination for 201 is 7000.)
• NOT Allowed Combination: 1000-101-6000-P001 (Invalid. This combination uses Department 101 and Account 6000, which is explicitly disallowed in the Invalid_Expense_Combos set.)
• NOT Allowed Combination: 2000-102-5000-P000 (Invalid. This combination uses Department 102 and Account 5000, which is explicitly disallowed.)
• NOT Allowed Combination: 1000-201-7000-P002 (Invalid. This combination uses Department 201 and Account 7000, which is explicitly disallowed.)

Very Important Note

These examples demonstrate how Allow sets restrict usage to only what's explicitly listed, while Disallow sets prevent specific problematic combinations while allowing everything else. This flexibility makes Combination Sets a powerful addition to Oracle's validation capabilities.

Conclusion: Smarter Validations with Less Effort

Combination Sets are a game-changer for Oracle Cloud ERP users seeking performance, simplicity, and better control over COA segment combinations. While CVRs are still relevant for advanced conditions, Combination Sets offer an easier and faster way to enforce fixed combinations.

Looking to optimize your ERP setup? Start using Combination Sets today to reduce errors, improve efficiency, and enhance compliance.

 

IDR vs Document IO Agent in Oracle Fusion: What’s the Difference and Which One Should You Use?

Introduction : IDR vs Document IO Agent in Oracle Fusion

In the era of automation and AI, Oracle Fusion Cloud Applications are constantly evolving to enhance enterprise efficiency. Two of the most talked-about automation features are:

• Intelligent Document Recognition (IDR)
• Document Understanding powered by the IO Agent

Both promise AI-driven document processing, but they are not interchangeable. This post dives deep into how they differ, when to use each, and which is more suitable depending on your specific Oracle Fusion scenario.

What is IDR in Oracle Fusion?

IDR (Intelligent Document Recognition) is a built-in AI/ML-based feature in Oracle Fusion Accounts Payable. It is designed to automatically capture, extract, and process invoice data from supplier documents.

How IDR Works:

• Leverages AI/ML to extract key invoice data (supplier name, invoice number, dates, PO number, etc.).
• Uses trained data models that continuously learn from corrections made by users.
• Integrated with Oracle Content Management (OCM) and AP Invoice Workflow.
• Best used for automating AP invoice entry via email, drag-drop, or scan.

Key Features of IDR:

• Pretrained AI model for invoices.
• Seamless AP automation.
• Confidence scoring and manual validation.
• No additional customization required.
• Auto-learning based on corrections.

What is Document IO Agent (Document Understanding)?

The Document IO Agent is part of Oracle’s Intelligent Automation Platform used within Oracle Integration Cloud (OIC). It is built to handle diverse types of unstructured documents beyond just invoices — including contracts, onboarding documents, claim forms, and more.

How Document IO Agent Works:

• Uses Oracle’s Document Understanding AI engine.
• Supports custom model training, entity extraction, classification, and labeling.
• Ideal for non-AP document automation (HR, Legal, SCM, etc.).
• Integrated with Oracle Integration (OIC) and OCI Vision AI models.

Key Features of Document IO Agent:

• Supports many document types and formats (PDF, image, scanned docs).
• AI-driven classification, key field extraction, sentiment, and entity recognition.
• Custom AI models tailored to business needs.
• Works with BPM and OIC workflows.

IDR vs Document IO Agent – Feature Comparison Table

Feature	IDR (Intelligent Document Recognition)	Document IO Agent (IO/OIC)
Purpose	Invoice Data Extraction	General Document Understanding
Document Types	Supplier Invoices (AP)	Invoices, Contracts, Forms, etc.
AI/ML Support	Pre-trained for invoices	Custom model training supported
User Customization	Minimal	High – Train and deploy models
Oracle Integration	Oracle AP, Content Management	Oracle Integration Cloud (OIC)
Use Case Fit	Finance (Accounts Payable)	HR, Legal, SCM, Claims, etc.
Learning Mechanism	Auto-learns from user validation	Explicit model training
Setup Time	Fast & Prebuilt	Time-intensive but flexible

Which One Should You Use?

• Choose IDR if:
• Your focus is on automating Accounts Payable invoice processing.
• You need a ready-to-use, minimal-setup solution embedded in Oracle Fusion ERP.
• Your documents follow standard invoice formats.
• Choose Document IO Agent if:
• You have a diverse set of document types (contracts, claims, HR docs).
• You need custom AI model capabilities to extract unique business information.
• You're integrating with Oracle Integration Cloud and building complex workflows.

Real-World Business Use Cases

• IDR Example: A retail company receives hundreds of supplier invoices daily. With IDR, 90% of invoices are auto-processed, reducing manual entry time by 60%.
• Document IO Agent Example: A healthcare company uses IO Agent to extract data from handwritten claim forms, classify them, and feed results into a claims system.

Conclusion

Oracle Fusion offers multiple AI-powered document processing tools, but selecting the right one is key. Use IDR for fast, intelligent invoice automation, and Document IO Agent for complex, varied document needs across departments.

Both offer transformative benefits, but understanding their scope and limitations is the secret to successful implementation.

 

Oracle Intelligent Document Recognition (IDR): AI-Powered Invoice Automation in Fusion Cloud

Oracle Fusion IDR (Intelligent Document Recognition): AI-Powered Efficiency for Finance Teams

Introduction - What is Oracle Intelligent Document Recognition (IDR)

In today’s fast-paced digital finance environment, manual invoice processing is outdated, error-prone, and resource-intensive. Oracle Fusion has responded to this challenge with a game-changing feature: Intelligent Document Recognition (IDR).

Powered by Artificial Intelligence (AI) and Machine Learning (ML), IDR in Oracle Fusion enables businesses to automate document ingestion, extract data intelligently, and reduce human intervention, making your finance operations faster, smarter, and more accurate.

What is Oracle IDR?

IDR (Intelligent Document Recognition) is Oracle Fusion’s embedded capability that automates the extraction of key data fields from incoming documents—especially supplier invoices—by applying AI and ML-based pattern recognition.

Key Features of Oracle IDR: AI, ML & Automation

• Automated invoice scanning and data extraction
• AI-based field mapping with self-learning capabilities
• Seamless integration with Oracle Fusion Payables
• Reduced dependency on third-party OCR tools
• Continuous improvement through Machine Learning

How IDR Uses AI and Machine Learning

Oracle IDR stands apart because it uses built-in AI/ML models trained on thousands of invoice formats to auto-learn and improve over time.

AI/ML Functionalities in IDR:

• Pattern Recognition
• Smart Field Extraction
• Supplier Matching
• Confidence Scoring
• Feedback Loop

Solving Business Challenges with Oracle Fusion IDR

Problem	Solution via IDR
Manual data entry	AI auto-extracts invoice data
Delayed invoice processing	Instant ingestion and validation
High error rate	AI-based confidence tagging & validation
Compliance risks	Standardized and audit-ready workflows
Operational cost	Less manpower needed for data entry

 

Implementing Oracle IDR: Configuration & Setup Steps

Prerequisites for IDR Implementation

• Fusion ERP Cloud with Payables module
• IDR enabled through the Oracle Cloud Service Entitlement
• Valid email inbox for invoice ingestion

High-Level Configuration Steps for IDR

1. Enable IDR Profile Options
2. Configure Email Inbound Channel
3. Train the Model (Optional)
4. Review & Approve
5. Integrate with Approval Workflows

Real Business Impact

• 60–80% reduction in invoice processing time
• 40–60% cost savings on manual processing
• 90%+ accuracy on field extraction after initial learning phase
• Significant reduction in payment delays and late fees

Why Choose Oracle IDR Over Traditional OCR?

Traditional OCR	Oracle IDR
Template-dependent	AI/ML-powered dynamic learning
Requires custom scripting	Out-of-the-box in Oracle Fusion
Limited scalability	Scales with invoice volume
Third-party integration needed	Native to Oracle Cloud ERP

 

Conclusion: Transforming AP with Oracle IDR

Oracle Intelligent Document Recognition (IDR) represents a significant leap forward in Accounts Payable (AP) automation within Oracle Fusion Cloud Applications. By seamlessly integrating Artificial Intelligence (AI) and Machine Learning (ML) into the invoice processing workflow, IDR goes far beyond traditional OCR tools.

This intelligent solution empowers organizations to:

• Drastically Reduce Manual Effort: Automating invoice data extraction frees up AP teams from repetitive, manual data entry, allowing them to focus on value-added activities like exception handling and analysis.
• Enhance Data Accuracy: The continuous learning capabilities of IDR lead to higher accuracy over time, minimizing errors that can lead to payment delays or reconciliation issues.
• Accelerate Invoice Processing: A faster AP workflow contributes to quicker payment cycles, improved vendor relationships, and better cash flow management.
• Strengthen Audit & Compliance: Automated processes provide a clear and reliable audit trail, enhancing procurement compliance and overall financial governance.

Implementing Oracle IDR is not just about adopting a new technology; it's about transforming your Accounts Payable operations from a cost center into a strategic function. By embracing these capabilities and adhering to best practices, your organization can achieve unprecedented levels of efficiency, accuracy, and control in invoice management within your Oracle Fusion ERP system.