FROM hr_operating_units
WHERE organization_id = aia.org_id) operating_unit_name,
(SELECT segment1
FROM ap_suppliers
WHERE vendor_id = aia.vendor_id) vendor_code,
(SELECT vendor_name
FROM ap_suppliers
WHERE vendor_id = aia.vendor_id) vendor_name,
SUM (NVL (ps.gross_amount, 0)) balance,
SUM (NVL (ps.amount_remaining, 0)) unpaid_amount,
COUNT (aia.invoice_num) number_of_invoices
FROM ap_invoices_all aia,
ap_payment_schedules_all ps,
ap_supplier_sites_all vs
WHERE 1 = 1
AND ps.amount_remaining != 0
AND ps.invoice_id = aia.invoice_id
AND NVL (aia.payment_status_flag, 'N') != 'Y'
AND aia.cancelled_date IS NULL
AND vs.vendor_site_id = aia.vendor_site_id
AND NOT ( aia.invoice_type_lookup_code = 'PREPAYMENT'
AND NVL (aia.payment_status_flag, 'N') = 'N'
)
AND aia.org_id = NVL (:p_org_id, aia.org_id)
AND aia.vendor_id = NVL (:p_vendor_id, aia.vendor_id)
AND aia.vendor_site_id = NVL (:p_vendor_site_id, aia.vendor_site_id)
GROUP BY aia.org_id, aia.vendor_id
ORDER BY vendor_code