1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

SQL Query for Oracle Apps Prepayment

Discussion in 'Oracle Apps Technical' started by yknev, Mar 8, 2010.

  1. yknev

    yknev Active Member

    Messages:
    28
    Likes Received:
    0
    Trophy Points:
    100
    Dear All,

    Please give me a query for below mentioned:-

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

    Thanks inadvance
     
  2. apps_expert

    apps_expert Forum Expert

    Messages:
    325
    Likes Received:
    28
    Trophy Points:
    330
    Location:
    Chennai, India
    Re: Prepayment Query

    See if this query helps, it's for 11i

    Code (SQL):
    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
     
  3. yknev

    yknev Active Member

    Messages:
    28
    Likes Received:
    0
    Trophy Points:
    100
    Dear Apps_Expert,

    Thanks for your prompt response.
    Can you give me clarification for following point:-

    When will populate the data in AP_INVOICE_PREPAYS_ALL table.
     
  4. yknev

    yknev Active Member

    Messages:
    28
    Likes Received:
    0
    Trophy Points:
    100
    Dear Apps_Expert,

    Awaiting for the response.


    Regds,

    kynev.
     
  5. apps_expert

    apps_expert Forum Expert

    Messages:
    325
    Likes Received:
    28
    Trophy Points:
    330
    Location:
    Chennai, India
    I am not sure, AP_INVOICE_PREPAYS_ALL is a multi-org table which has data for your current operating unit and ignore data in other operating units. My guess is it should populate when you apply a prepayment. Why don't you try it out.