Prepayment Status Report Custom Query

Prepayment Status Report Custom Query

/* Formatted on 10-01-2015 04:48:39 PM (QP5 v5.163.1008.3004) */

SELECT vendor_name,

       --       ACCOUNT_CODE,

       (SELECT segment4

          FROM gl_code_combinations_kfv

         WHERE code_combination_id = account_code)

          account_code,

       invoice_type_lookup_code,

       invoice_no,

       invoice_id,

       invoice_date,

       exchange_rate,

       invoice_currency_code,

       invoice_amount,

       --       (INVOICE_AMOUNT - PERPAY)/EXCHANGE_RATE,

       ( ( (invoice_amount - perpay) * exchange_rate) / exchange_rate)

          amount_remaining_cy,

       ( (invoice_amount - perpay) * exchange_rate) amount_remaining_aed,

       perpay,

       description

  FROM (  SELECT b.vendor_name vendor_name,

                 --c.vendor_site_id,

                 --c.PREPAY_CODE_COMBINATION_ID ACCOUNT_CODE,

                 d.dist_code_combination_id account_code,

                 a.invoice_type_lookup_code invoice_type_lookup_code,

                 a.invoice_num invoice_no,

                 a.invoice_id invoice_id,

                 a.invoice_date invoice_date,

                 a.invoice_currency_code invoice_currency_code,

                 NVL (a.exchange_rate, 1) exchange_rate,

                 SUM (d.amount) invoice_amount,

                 NVL (

                    (SELECT SUM (prepay_amount_applied)

                       FROM ap_view_prepays_fr_prepay_v

                      WHERE prepay_id = a.invoice_id

                            AND TO_DATE (accounting_date, 'DD-MON-RRRR') <=

                                   TO_DATE (:p_to_date, 'DD-MON-RRRR')),

                    0)

                    perpay,

                 a.description description

            FROM apps.ap_invoices_all a,

                 apps.ap_invoice_distributions_all d,

                 apps.ap_suppliers b,

                 apps.ap_supplier_sites_all c

           WHERE     a.invoice_id = d.invoice_id

                 AND a.vendor_id = b.vendor_id

                 AND b.vendor_id = c.vendor_id

                 AND a.vendor_site_id = c.vendor_site_id

                 AND a.invoice_type_lookup_code = 'PREPAYMENT'

                 AND a.org_id = NVL (:org_id, a.org_id)

                 AND b.vendor_name = NVL (:p_vendor_name, b.vendor_name)

                 --               and  d.DIST_CODE_COMBINATION_ID=2016--(233100)

                 AND TO_DATE (d.accounting_date, 'DD-MON-RRRR') <=

                        TO_DATE (:p_to_date, 'DD-MON-RRRR')

        GROUP BY b.vendor_name,

                 --c.vendor_site_id,

                 d.dist_code_combination_id,

                 a.invoice_type_lookup_code,

                 a.invoice_num,

                 a.invoice_id,

                 a.invoice_date,

                 a.invoice_currency_code,

                 NVL (a.exchange_rate, 1),

                 a.description)

 WHERE (invoice_amount - perpay) <> 0;

--       AND a.invoice_num = 'IPO-107253';