Skip to main content

Oracle EBS R12 Payables Module – Practical SQL Queries

 

The Oracle E-Business Suite (EBS) R12, Payables (AP) Module is a core financial application that automates supplier invoicing, payment processing, and liability tracking. It manages the full invoice life cycle such as recording, matching with Purchase Orders (PO's) and paying vendors to ensure accurate, timely, and compliant.

 Below are real-world SQL queries commonly used.

1)  Invoice Details with Supplier Info

    Purpose: This query retrieves invoice details along with supplier name.

     SELECT

    ai.invoice_num,

    ai.invoice_date,

    ai.invoice_amount,

    ai.amount_paid,

    ai.payment_status_flag,

    aps.vendor_name,

    aps.segment1 AS vendor_number

    FROM ap_invoices_all ai

   JOIN ap_suppliers aps

    ON ai.vendor_id = aps.vendor_id

   ORDER BY ai.invoice_date DESC;

 

2)   Invoice with Payment Details

  Purpose: This query shows invoice payments including check number and payment date.

     SELECT

    ai.invoice_num,

    ai.invoice_date,

    ai.invoice_amount,

    ac.check_number,

    ac.check_date,

    ac.amount AS paid_amount

    FROM ap_invoices_all ai

     JOIN ap_invoice_payments_all aip

    ON ai.invoice_id = aip.invoice_id

   JOIN ap_checks_all ac

    ON aip.check_id = ac.check_id

   ORDER BY ac.check_date DESC;

 
3)  Unpaid Invoices Details

     Purpose: This query identifies invoices that are not fully paid.

    SELECT

    ai.invoice_num,

    ai.invoice_date,

    ai.invoice_amount,

    ai.amount_paid,

    (ai.invoice_amount - NVL(ai.amount_paid,0)) AS balance_amount,

    aps.vendor_name

    FROM ap_invoices_all ai

   JOIN ap_suppliers aps

    ON ai.vendor_id = aps.vendor_id

   WHERE ai.payment_status_flag <> 'Y'

  ORDER BY ai.invoice_date;

 

4)  Supplier Site Details

    Purpose: This query retrieves supplier site and address information.

    SELECT

    aps.vendor_name,

    assa.vendor_site_code,

    assa.address_line1,

    assa.city,

    assa.country

    FROM ap_suppliers aps

   JOIN ap_supplier_sites_all assa

    ON aps.vendor_id = assa.vendor_id;

 
5)  Unpaid Invoices against Purchase Orders

    Purpose: This query links invoices with Purchase Orders and shows unpaid amounts.

     SELECT

    ai.invoice_num,

    ai.invoice_date,

    aps.vendor_name,

    ai.invoice_amount,

    ai.amount_paid,

    (ai.invoice_amount - NVL(ai.amount_paid,0)) AS unpaid_amount,

    ai.payment_status_flag,

    poh.segment1 AS po_number,

    poh.creation_date AS po_date,

    aid.amount AS line_amount

    FROM ap_invoices_all ai

   JOIN ap_suppliers aps

    ON ai.vendor_id = aps.vendor_id

   JOIN ap_invoice_distributions_all aid

   ON ai.invoice_id = aid.invoice_id

    JOIN po_distributions_all pod

    ON aid.po_distribution_id = pod.po_distribution_id

   JOIN po_headers_all poh

    ON pod.po_header_id = poh.po_header_id

    WHERE ai.payment_status_flag = 'N'

    AND NVL(ai.cancelled_date, SYSDATE+1) > SYSDATE

    ORDER BY ai.invoice_date;

 

Conclusion:

These queries provide practical insights into invoice processing, payment status, and Purchase Order (PO) matching, helping professionals better analyze and troubleshoot Oracle Payables data.

 

 

Comments

Post a Comment

Popular posts from this blog

Oracle EBS R12 General Ledger (GL) Module – Practical SQL Queries

Introduction Oracle E-Business Suite (EBS) R12 General Ledger (GL) is the core Financial module in Oracle EBS R12. It is used to manage journal entries, accounting periods, balances, and financial reporting. The GL module integrates with Payables (AP), Receivables (AR), Fixed Assets (FA), Purchasing (PO), and Inventory (INV). The main purpose of a general ledger system is to record financial activity of a company and to produce financial and management reports to help the organization make decisions. Below are real-world SQL queries commonly used.   1) View configured Ledgers      Purpose: Fetch Ledger Configuration Details SELECT       ledger_id,      name  AS   ledger_name ,    short_name ,   currency_code FROM     gl_ledgers ORDER BY     ledger_id ;   2) Ledger Information Query Purpose: Fetch Ledger Configuration Details SELECT     gl.name   ...
Post a Comment
Read more

Exploring AI in Oracle APEX 26.1

Artificial Intelligence (AI) is transforming the way organizations and developers work. Businesses and professionals are increasingly adopting AI tools to improve productivity, automate processes, and build smarter applications. Oracle APEX introduces a significant evolution in application development by integrating governed Generative AI capabilities directly into the low-code platform. This enables developers to build intelligent, conversational applications while maintaining enterprise-grade security and control. In this article we explore AI capabilities in Oracle APEX 26.1 What is AI Agents AI Agents can be created to reason over user requests and take actions through approved AI Tools. Each tool exposes a specific application capability the agent is allowed to invoke, such as retrieving data, running server-side PL/SQL, or executing client-side JavaScript. APEX manages the execution flow by preparing context, dispatching tool calls, executing tools, handling results, and...
Post a Comment
Read more

Oracle R12 Inventory Configuration: Key points to Avoid Mistakes

Most Oracle ERP R12 inventory problems are not system failures they are process and configuration failures. The same categories of mistakes surface repeatedly across implementations: Focusing on master data control, disciplined transactions, proper OU setup, and strong internal controls prevents operational and financial discrepancies.   This article walks through the four most critical areas where Oracle R12 inventory implementations go wrong, explains why each mistake is costly, and provides recommendations to prevent them. Each section includes the relevant navigation path and configuration screenshots for hands-on reference   1.       Improper Item Master Configuration in Oracle R12   Oracle E‑Business Suite R12 , the Item Master Configuration serves as the foundation of the Inventory module. Two critical components under this configuration umbrella are: Define Master Item Setup Define Item Status Both setups control how items ...
Post a Comment
Read more