SQL Sample Statement

Find out the invoices in Accounts Payable which aren’t fully paid out for a business unit.

The SQL code sample is a query for retrieving invoice data from Oracle Cloud for a specific business unit where the invoice amount is greater than the amount paid. This is a common scenario for accounts payable reporting. Here’s a breakdown of the SQL query:

Detailed Explanation

The SELECT clause retrieves specific columns from various joined tables, renaming some of them using aliases for clarity.
The FROM clause specifies the tables you are querying, including ap_invoices_all, poz_suppliers, hz_parties, poz_supplier_sites_all_m, and fun_all_business_units_v. These tables are joined using INNER JOIN statements to link the relevant data.
In the WHERE clause, several conditions are applied:
- 1 = 1 is a placeholder condition that doesn’t affect the query’s logic but can be used to easily add more conditions.
- ai.invoice_amount > ai.amount_paid filters the results to include only invoices where the invoice amount is greater than the amount paid, which is a common condition for open invoices.
- bu.bu_name = :Business_Unit filters the results to a specific business unit. :Business_Unit is a placeholder for a parameter that should be provided when executing the query.

This query can be used to retrieve a list of unpaid invoices for a particular business unit in Oracle Cloud. You would need to replace :Business_Unit with the actual business unit name you want to report on when executing the query.

				
					SELECT
    ai.invoice_num              invoice_number,
    ai.invoice_type_lookup_code invoice_type_code,
    ai.invoice_date,
    ai.gl_date                  accounting_date,
    hz.party_name               supplier_name,
    pv.segment1                 supplier_number,
    pvs.vendor_site_code        supplier_site_code,
    ai.invoice_currency_code,
    ai.invoice_amount,
    ai.amount_paid              paid_amount
FROM
         ap_invoices_all ai
    INNER JOIN poz_suppliers            pv ON ai.vendor_id = pv.vendor_id
    INNER JOIN hz_parties               hz ON hz.party_id = pv.party_id
    INNER JOIN poz_supplier_sites_all_m pvs ON ai.vendor_site_id = pvs.vendor_site_id
    INNER JOIN fun_all_business_units_v bu ON ai.org_id = bu.bu_id
WHERE
        1 = 1
    AND ai.invoice_amount > ai.amount_paid
    AND bu.bu_name = :Business_Unit

SQL Sample Statement

Find out the invoices in Accounts Payable which aren’t fully paid out for a business unit.

Getting Started

Support

Get In Touch