Question:

How to know, how many prepayments are available to particular vendor?

See this query code which is for 11i :

SELECT   pv.vendor_name C_vendor_name,
         pvs.address_line1 C_address_line1,
         pvs.address_line2 C_address_line2,
         pvs.address_line3 C_address_line3,
            DECODE (pvs.city, '', '', pvs.city || ', ')
         || DECODE (pvs.state, '', '', pvs.state || ' ')
         || pvs.zip
            C_city_state_zip,
         pvs.country C_country,
         aipp.last_update_date C_application_date,
         aipp.prepayment_amount_applied C_amount_applied,
         inv.invoice_currency_code C_currency_code,
         pp.invoice_num C_prepay_num,
         inv.invoice_num C_invoice_num,
         NVL (inv.invoice_amount, 0) - NVL (inv.amount_paid, 0)
            C_amt_remaining
  FROM   po_vendors pv,
         po_vendor_sites_all pvs,
         ap_invoices_all inv,
         ap_invoices_all pp,
         ap_invoice_prepays_all aipp
 WHERE       aipp.invoice_id = inv.invoice_id
         AND aipp.prepay_id = pp.invoice_id
         AND inv.vendor_id = pp.vendor_id
         AND inv.vendor_id = pv.vendor_id
         AND pv.vendor_id = pvs.vendor_id
         AND pvs.vendor_site_id = inv.vendor_site_id
         AND NVL (pvs.LANGUAGE, 'AMERICAN') = 'AMERICAN'
         AND aipp.last_update_date >= &InvDate
UNION
SELECT   pv.vendor_name C_vendor_name,
         pvs.address_line1 C_address_line1,
         pvs.address_line2 C_address_line2,
         pvs.address_line3 C_address_line3,
            DECODE (pvs.city, '', '', pvs.city || ', ')
         || DECODE (pvs.state, '', '', pvs.state || ' ')
         || pvs.zip
            C_city_state_zip,
         pvs.country C_country,
         aid2.last_update_date C_application_date,
         NVL (
            ap_invoices_utility_pkg.get_pp_amt_applied_on_date (
               inv.invoice_id,
               pp.invoice_id,
               aid2.last_update_date
            ),
            0
         )
            C_amount_applied,
         inv.invoice_currency_code C_currency_code,
         pp.invoice_num C_prepay_num,
         inv.invoice_num C_invoice_num,
         NVL (inv.invoice_amount, 0)
         - (ap_invoices_pkg.get_prepaid_amount (inv.invoice_id))
            C_amt_remaining
  FROM   po_vendors pv,
         po_vendor_sites_all pvs,
         ap_invoices_all inv,
         ap_invoices_all pp,
         ap_invoice_distributions_all aid1,
         ap_invoice_distributions_all aid2
 WHERE       aid1.invoice_id = inv.invoice_id
         AND aid2.invoice_id = pp.invoice_id
         AND aid2.invoice_distribution_id = aid1.prepay_distribution_id
         AND aid1.line_type_lookup_code = 'PREPAY'
         AND inv.vendor_id = pp.vendor_id
         AND inv.vendor_id = pv.vendor_id
         AND pv.vendor_id = pvs.vendor_id
         AND pvs.vendor_site_id = inv.vendor_site_id
         AND NVL (aid1.reversal_flag, 'N') != 'Y'
         AND NVL (pvs.LANGUAGE, 'AMERICAN') = 'AMERICAN'
         AND inv.invoice_date >= &InvDate

Have a Oracle Question
Do you have an Oracle Question?

Oracle Books
Oracle Certification, Database Administration, SQL, Application, Programming Reference Books

Oracle Application
Oracle Application Hints and Tips

Oracle Home
Oracle Database, SQL, Application, Programming Tips