SELECT povs.vendor_site_code, 
               api.vendor_id, 
               api.vendor_site_id, 
               PoVS.address_line1, 
               PoVS.address_line2, 
               PoVS.address_line3, 
               api.invoice_type_lookup_code, 
               api.invoice_num, 
               API.accts_pay_code_combination_id, 
               ( GCC.segment1 
                 || '.' 
                 || gCC.segment2 
                 || '.' 
                 || GCC.segment3 
                 || '.' 
                 || GCC.segment4 
                 || '.' 
                 || GCC.segment5 
                 || '.' 
                 || GCC.segment6 
                 || '.' 
                 || GCC.segment7 ) 
               account_code, 
               api.invoice_date, 
               api.doc_sequence_value 
               Vou_num, 
               api.creation_date 
               Vou_date, 
               apd.description 
               description, 
               apd.dist_code_combination_id 
               ccid, 
               api.invoice_currency_code, 
               Nvl(api.exchange_rate, 1) 
               exchange_rate, 
               Decode(api.invoice_type_lookup_code, 'CREDIT', Abs(z.amt_val) - 
                                                              Abs( 
               Nvl(api.discount_amount_taken, 0)), 
                                                    0) 
               dr_val, 
               Decode(api.invoice_type_lookup_code, 'CREDIT', Abs(z.amt_val) - 
                                                              Abs( 
               Nvl(api.discount_amount_taken, 0)), 
0) * Nvl(api.exchange_rate, 1) 
dr_exchange_val, 
Decode(api.invoice_type_lookup_code, 'CREDIT', 0, 
z.amt_val - 
Nvl(api.discount_amount_taken, 0)) 
cr_val, 
Decode(api.invoice_type_lookup_code, 'CREDIT', 0, 
z.amt_val - 
Nvl(api.discount_amount_taken, 0)) 
* 
Nvl( 
api.exchange_rate, 1) 
cr_exchange_val, 
To_char(api.doc_sequence_value) 
payment_num, 
To_char(apd.accounting_date, 'dd-MON-yyyy') 
pay_accounting_date, 
Nvl(ap_invoice_lines_utility_pkg.Get_posting_status(api.invoice_id, 
apil.line_number), 'N') 
acc_status, 
Nvl(ap_invoice_lines_utility_pkg.Get_approval_status(api.invoice_id, 
apil.line_number), 'NEVER APPROVED') 
VALIDATION_STATUS, 
NULL 
check_number, 
pov.segment1, 
pov.vendor_name, 
pov.vendor_type_lookup_code, 
apd.po_distribution_id, 
api.exchange_rate_type, 
api.org_id, 
api.batch_id, 
api.exchange_date, 
api.invoice_id, 
apd.accounting_date, 
0 
dr_val_opening, 
0 
cr_val_opening 
FROM   ap_invoices_all api, 
ap_invoice_lines_all apil, 
ap_invoice_distributions_all apd, 
po_vendors pov, 
po_vendor_sites_all povs, 
gl_code_combinations GCC, 
(SELECT Nvl(Sum(apd.amount), 0) amt_val, 
api.invoice_id 
FROM   ap_invoices_all api, 
ap_invoice_lines_all apil, 
ap_invoice_distributions_all apd, 
po_vendors pov, 
po_vendor_sites_all povs 
WHERE  api.invoice_id = apd.invoice_id 
AND apil.invoice_id = api.invoice_id 
AND apil.line_number = apd.invoice_line_number 
AND api.vendor_id = pov.vendor_id 
AND api.vendor_id = Nvl(:p_vendor_id, api.vendor_id) 
AND pov.segment1 = Nvl(:p_vendor_no, pov.segment1) 
AND Nvl(pov.vendor_type_lookup_code, 'NULL') = 
Nvl(:P_Vendor_Type_Lookup_Code, 
Nvl(Pov.vendor_type_lookup_code, 
'NULL')) 
AND api.invoice_type_lookup_code <> 'PREPAYMENT' 
AND ( api.org_id = :p_org_id 
OR api.org_id IS NULL ) 
AND api.vendor_site_id = povs.vendor_site_id 
AND api.vendor_site_id = Nvl(:p_vendor_site_id, 
api.vendor_site_id) 
AND apd.accounting_date BETWEEN :p_from_date AND :p_to_date 
AND apd.match_status_flag = 'A' 
AND apil.line_type_lookup_code <> 'PREPAY' 
GROUP  BY api.invoice_id) z 
WHERE  api.invoice_id = z.invoice_id 
AND api.invoice_id = apd.invoice_id 
AND apil.invoice_id = api.invoice_id 
AND GCC.code_combination_id = API.accts_pay_code_combination_id 
AND apil.line_number = apd.invoice_line_number 
AND apd.rowid = (SELECT rowid 
FROM   ap_invoice_distributions_all 
WHERE  rownum = 1 
AND invoice_id = apd.invoice_id 
AND accounting_date BETWEEN 
:p_from_date AND :p_to_date 
AND match_status_flag = 'A') 
AND api.vendor_id = pov.vendor_id 
AND api.vendor_id = Nvl(:p_vendor_id, api.vendor_id) 
AND pov.segment1 = Nvl(:p_vendor_no, pov.segment1) 
AND Nvl(pov.vendor_type_lookup_code, 'NULL') = 
Nvl(:P_Vendor_Type_Lookup_Code, Nvl(Pov.vendor_type_lookup_code, 
'NULL')) 
AND api.invoice_type_lookup_code <> 'PREPAYMENT' 
AND apd.match_status_flag = 'A' 
AND ( api.org_id = :p_org_id 
OR api.org_id IS NULL ) 
AND api.vendor_site_id = povs.vendor_site_id 
AND api.invoice_type_lookup_code LIKE 
Nvl(:p_invoice_type_lookup_code, api.invoice_type_lookup_code) 
AND api.vendor_site_id = Nvl(:p_vendor_site_id, api.vendor_site_id) 
AND ( ( api.invoice_type_lookup_code <> 'DEBIT' ) 
OR (( api.invoice_type_lookup_code = 'DEBIT' ) 
) ) 


-------------------///without prepayment------------------------

(select   api.invoice_type_lookup_code,nvl(apc.exchange_rate,1)--,apc.exchange_Date
		 FROM   ap_invoices_all api, 
       ap_invoice_lines_all apil, 
       ap_invoice_distributions_all apd, 
       po_vendors pov, 
       ap_invoice_payments_all app, 
       ap_checks_all apc, 
       po_vendor_sites_all povs, 
       gl_code_combinations GCC 
WHERE  api.invoice_id = apd.invoice_id 
       AND apil.invoice_id = api.invoice_id 
       AND apil.line_number = apd.invoice_line_number 
       AND GCC.code_combination_id = API.accts_pay_code_combination_id 
       AND apd.rowid = (SELECT rowid 
                        FROM   ap_invoice_distributions_all 
                        WHERE  rownum = 1 
                               AND invoice_id = apd.invoice_id 
                               AND match_status_flag = 'A') 
       AND api.vendor_id = pov.vendor_id 
       AND app.invoice_id = api.invoice_id 
       AND app.check_id = apc.check_id 
       AND api.vendor_id = Nvl(:p_vendor_id, api.vendor_id) 
       AND pov.segment1 = Nvl(:p_vendor_no, pov.segment1) 
       AND app.accounting_date BETWEEN :p_from_date AND :p_to_date 
       AND ( api.org_id = :p_org_id 
              OR api.org_id IS NULL ) 
       AND apc.status_lookup_code IN ( 'CLEARED', 'NEGOTIABLE', 'VOIDED', 
                                       'RECONCILED UNACCOUNTED', 
                                       'RECONCILED', 'CLEARED BUT UNACCOUNTED' ) 
       AND apd.match_status_flag = 'A' 
       AND api.vendor_site_id = povs.vendor_site_id 
	   and  api.invoice_currency_code in  'USD'
       AND api.invoice_type_lookup_code LIKE 
           Nvl(:p_invoice_type_lookup_code, api.invoice_type_lookup_code) 
       AND api.vendor_site_id = Nvl(:p_vendor_site_id, api.vendor_site_id) )--exc_rate