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:

  1. The SELECT clause retrieves specific columns from various joined tables, renaming some of them using aliases for clarity.
  2. 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.
  3. 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.

    ai.invoice_num              invoice_number,
    ai.invoice_type_lookup_code invoice_type_code,
    ai.gl_date                  accounting_date,
    hz.party_name               supplier_name,
    pv.segment1                 supplier_number,
    pvs.vendor_site_code        supplier_site_code,
    ai.amount_paid              paid_amount
         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
        1 = 1
    AND ai.invoice_amount > ai.amount_paid
    AND bu.bu_name = :Business_Unit