AP Current Supplier Balances (Oracle Fusion SQL)

Enterprise Resource Planning (ERP) Oracle Account Payables (AP-Fusion-SQL's)
1

This AP Current Supplier Balances query displays the outstanding balances (Amount Remaining) for suppliers across operating units, grouped by vendor and organization.

#sqlquery

SELECT
  -- SQL4Fusion (An Orbit Analytics Project) 
  -- This query displays outstanding invoice balances by supplier and organization
  PSV.VENDOR_NAME SUPPLIER_NAME,
  HAOU.ORGANIZATION_ID,
  HAOU.NAME ORGANIZATION_NAME,
  PSV.VENDOR_TYPE_LOOKUP_CODE,
  SUM (APSA.AMOUNT_REMAINING) AS_OF_DATE_BALANCE_AMOUNT,
  ROW_NUMBER () OVER (
    ORDER BY
      SUM (APSA.AMOUNT_REMAINING) DESC
  ) ROW_SEQ,
  PSV.VENDOR_ID SUPPLIER_ID,
  AIA.INVOICE_CURRENCY_CODE 
FROM
  AP_PAYMENT_SCHEDULES_ALL APSA,
  AP_INVOICES_ALL AIA,
  POZ_SUPPLIERS_V PSV,
  HR_ALL_ORGANIZATION_UNITS HAOU
WHERE
  AIA.INVOICE_ID = APSA.INVOICE_ID
  AND AIA.VENDOR_ID = PSV.VENDOR_ID
  AND HAOU.ORGANIZATION_ID = AIA.ORG_ID
  AND AIA.CANCELLED_DATE IS NULL
  AND (
    NVL (APSA.AMOUNT_REMAINING, 0) * NVL (AIA.EXCHANGE_RATE, 1)
  ) != 0
  AND AIA.PAYMENT_STATUS_FLAG IN ('N', 'P')
GROUP BY
  PSV.VENDOR_NAME,
  PSV.VENDOR_ID,
  PSV.VENDOR_TYPE_LOOKUP_CODE,
  HAOU.NAME,
  HAOU.ORGANIZATION_ID,
  AIA.INVOICE_CURRENCY_CODE