Payables Useful Query

1)list all the invoices and supplier information
supplier_no,supp name,supplier site,invoice no,invoice curr,amount


/* Formatted on 9/4/2014 2:13:06 PM (QP5 v5.115.810.9015) */
SELECT pv.segment1 "Supplier Number",
       pv.vendor_name "Supplier Name",
       pvsa.vendor_site_id "Supplier Site",
       aia.invoice_num "Invoice Number",
       aia.invoice_currency_code "Currency Code",
       aia.invoice_amount
FROM ap_invoices_all aia, po_vendors pv, po_vendor_sites_all pvsa
WHERE     1 = 1
      AND pv.vendor_id = pvsa.vendor_id
      AND aia.vendor_id = pv.vendor_id
      AND aia.vendor_site_id = pvsa.vendor_site_id
      AND aia.org_id = 204
      AND aia.invoice_num = 'HYD01';

2)list all the open invoices showing balances(not paid and partially paid)
invoice no,invoice amount,supplier no,supplier name,date of invoice creation,status(paid or not) and balance


/* Formatted on 9/4/2014 2:13:27 PM (QP5 v5.115.810.9015) */
SELECT aia.invoice_num,
       aia.invoice_amount,
       pv.vendor_id,
       pv.vendor_name,
       aia.invoice_date                                                  --New
                       ,
       alc.displayed_field,
       apsa.amount_remaining
FROM ap_invoices_all aia,
     po_vendors pv,
     ap_payment_schedules_all apsa,
     ap_lookup_codes alc
WHERE     1 = 1
      AND aia.vendor_id = pv.vendor_id
      AND aia.invoice_id = apsa.invoice_id
      AND apsa.payment_status_flag = alc.lookup_code
      AND aia.org_id = 204
      AND alc.lookup_type = 'INVOICE PAYMENT STATUS'
      AND alc.displayed_field NOT IN ('Yes')
      AND aia.invoice_num = 'HYD01';
     
3)list all the invoices on which the payment has been padi
invoice no,do of invoice creation,do of payment of invoice,supplier name,payment document no


/* Formatted on 9/4/2014 2:13:44 PM (QP5 v5.115.810.9015) */
SELECT aia.invoice_num,
       aia.invoice_date,
       aipa.creation_date,
       aca.vendor_name,
       aca.check_number
FROM ap_invoices_all aia, ap_invoice_payments_all aipa, ap_checks_all aca
WHERE     1 = 1
      AND aia.invoice_id = aipa.invoice_id
      AND aipa.check_id = aca.check_id
      AND aia.org_id = 204
      AND aia.invoice_num = 'HYD01';

4)invoice no,do of invoice creation,do of payment of invoice,supplier name,distribution account


/* Formatted on 9/4/2014 2:14:04 PM (QP5 v5.115.810.9015) */
SELECT aia.invoice_num,
       aia.invoice_date,
       aia.creation_date,
       aida.amount,
       aia.invoice_id,
       aia.invoice_amount,
       aipa.creation_date payment_date,
       pv.vendor_name,
          gcc.segment1
       || '.'
       || gcc.segment2
       || '.'
       || gcc.segment3
       || '.'
       || gcc.segment4
       || '.'
       || gcc.segment5
          "Distribution A/C"
FROM ap_invoices_all aia,
     po_vendors pv,
     ap_invoice_distributions_all aida,
     gl_code_combinations gcc,
     ap_invoice_payments_all aipa
WHERE     1 = 1
      AND aia.vendor_id = pv.vendor_id
      AND aia.invoice_id = aida.invoice_id
      AND aida.dist_code_combination_id = gcc.code_combination_id
      AND aipa.invoice_id = aia.invoice_id
      AND aipa.invoice_id = 10250
      AND aia.org_id = 204
--and pv.vendor_id = 1937
ORDER BY aipa.invoice_id;