SELECT * 
FROM   (SELECT vendor_site_code, 
               vendor_id, 
               vendor_site_id, 
               address_line1, 
               address_line2, 
               address_line3, 
               NULL                   invoice_type_lookup_code, 
               NULL                   invoice_num, 
               NULL                   ACCTS_PAY_CODE_COMBINATION_ID, 
               NULL                   account_code, 
               NULL                   invoice_date, 
               NULL                   Vou_num, 
               NULL                   Vou_date, 
               NULL                   description, 
               NULL                   ccid, 
               invoice_currency_code, 
               0                      exchange_rate, 
               0                      dr_val, 
               0                      dr_exchange_val, 
               0                      cr_val, 
               0                      cr_exchange_val, 
               NULL                   payment_num, 
               NULL                   pay_accounting_date, 
               NULL                   acc_status, 
               NULL                   VALIDATION_STATUS, 
               NULL                   check_number, 
               segment1, 
               vendor_name, 
               vendor_type_lookup_code, 
               NULL                   po_distribution_id, 
               NULL                   exchange_rate_type, 
               org_id, 
               NULL                   batch_id, 
               NULL                   exchange_date, 
               NULL                   invoice_id, 
               NULL                   accounting_date, 
               Nvl(dr_val_opening, 0) dr_val_opening, 
               Nvl(cr_val_opening, 0) cr_val_opening 
        FROM  (SELECT vendor_site_code, 
                      vendor_id, 
                      vendor_site_id, 
                      org_id, 
                      address_line1, 
                      address_line2, 
                      address_line3, 
                      segment1, 
                      vendor_name, 
                      vendor_type_lookup_code, 
                      invoice_currency_code, 
                      Nvl(Sum(dr_exchange_val), 0) dr_val_opening, 
                      Nvl(Sum(cr_exchange_val), 0) cr_val_opening 
               FROM   (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.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 
        FROM   ap_invoices_all api, 
        ap_invoice_lines_all apil, 
        ap_invoice_distributions_all apd, 
        po_vendors pov, 
        po_vendor_sites_all povs, 
        (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 < :p_from_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 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 < :p_from_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.invoice_currency_code like nvl(:p_invoice_currency_code,api.invoice_currency_code) 
        -------------&P_WHERE_CURRENCY_TYPE 
        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' ) 
        /*and 
        ( not exists  
                    (Select '1'  
                     from   ap_invoice_payments_all  app,  
                            ap_checks_all apc  
                     where  app.check_id = apc.check_id  
                     and    app.invoice_id = api.invoice_id  
                     and    apc.payment_type_flag = 'R'  
                     )  
        )*/ 
        ) ) 
        UNION ALL 
        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.invoice_date, 
        api.doc_sequence_value                                          Vou_num, 
        api.creation_date                                               Vou_date 
        , 
        apd.description 
        description, 
        app.accts_pay_code_combination_id                               ccid, 
        api.payment_currency_code, 
        Nvl(apc.exchange_rate, 1) 
        exchange_rate, 
        Decode(api.invoice_type_lookup_code, 'CREDIT', 
        Decode(status_lookup_code, 'VOIDED', 0, 
        0), 
        app.amount)                dr_val, 
        Decode(api.invoice_type_lookup_code, 'CREDIT', 
        Decode(status_lookup_code, 'VOIDED', 0, 
        0), 
        app.amount) * 
        Nvl(apc.exchange_rate, 1) 
        dr_val, 
        Decode(api.invoice_type_lookup_code, 'CREDIT', 
        Decode(status_lookup_code, 'VOIDED', app.amount, 
        Abs(app.amount)), 
        0)                         cr_val, 
        Decode(api.invoice_type_lookup_code, 'CREDIT', 
        Decode(status_lookup_code, 'VOIDED', app.amount, 
        Abs(app.amount)), 
        0) * Nvl(apc.exchange_rate, 1) 
        cr_exchane_val, 
        Decode(api.payment_status_flag, 'Y', To_char(apc.doc_sequence_value), 
        'P', To_char(apc.doc_sequence_value), 
        To_char(apc.doc_sequence_value), 'N', 
        NULL) 
        payment_num, 
        Decode(api.payment_status_flag, 'Y', 
        To_char(app.accounting_date, 'dd-MON-yyyy') 
        , 
        'P', 
        To_char(app.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, 
        Decode(api.payment_status_flag, 'Y', To_char(apc.check_number), 
        'P', To_char(apc.check_number)) 
        check_number, 
        pov.segment1, 
        pov.vendor_name, 
        pov.vendor_type_lookup_code, 
        apd.po_distribution_id, 
        apc.exchange_rate_type, 
        api.org_id, 
        api.batch_id, 
        apc.exchange_date, 
        api.invoice_id, 
        app.accounting_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 
        WHERE  api.invoice_id = apd.invoice_id 
        AND apil.invoice_id = api.invoice_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 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 < :p_from_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_type_lookup_code like nvl(:p_invoice_type_lookup_code,api.invoice_type_lookup_code)
        --and  api.payment_currency_code like nvl(:p_invoice_currency_code ,api.payment_currency_code) 
        -------------------------&P_WHERE_CURRENCY_TYPE1 
        AND api.vendor_site_id = Nvl(:p_vendor_site_id, api.vendor_site_id)) 
        GROUP  BY vendor_site_code, 
        vendor_id, 
        vendor_site_id, 
        org_id, 
        address_line1, 
        address_line2, 
        address_line3, 
        segment1, 
        vendor_name, 
        vendor_type_lookup_code, 
        invoice_currency_code) 
        -- for opening 
        UNION ALL 
        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')) 
/*Added by nprashar for bug # 7207441*/ 
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.invoice_currency_code like nvl(:p_invoice_currency_code,api.invoice_currency_code) 
-----------------------&P_WHERE_CURRENCY_TYPE 
--AND api.invoice_currency_code!=:P_CURRENCY 
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' ) 
/* and 
( not exists  
             (Select '1'  
              from   ap_invoice_payments_all  app,  
                     ap_checks_all apc  
              where  app.check_id = apc.check_id  
              and    app.invoice_id = api.invoice_id  
              and    apc.payment_type_flag = 'R'  
              )  
)*/ 
) ) 
UNION ALL 
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, 
app.accts_pay_code_combination_id                               ccid, 
api.payment_currency_code, 
Nvl(apc.exchange_rate, 1) 
exchange_rate, 
Decode(api.invoice_type_lookup_code, 'CREDIT', 
Decode(status_lookup_code, 'VOIDED', 0, 
0), 
app.amount)                dr_val, 
Decode(api.invoice_type_lookup_code, 'CREDIT', 
Decode(status_lookup_code, 'VOIDED', 0, 
0), 
app.amount) * 
Nvl(apc.exchange_rate, 1) 
dr_val, 
Decode(api.invoice_type_lookup_code, 'CREDIT', 
Decode(status_lookup_code, 'VOIDED', app.amount, 
Abs(app.amount)), 
0)                         cr_val, 
Decode(api.invoice_type_lookup_code, 'CREDIT', 
Decode(status_lookup_code, 'VOIDED', app.amount, 
Abs(app.amount)), 
0) * Nvl(apc.exchange_rate, 1) 
cr_exchane_val, 
Decode(api.payment_status_flag, 'Y', To_char(apc.doc_sequence_value), 
'P', To_char(apc.doc_sequence_value), 
To_char(apc.doc_sequence_value), 'N', 
NULL) 
payment_num, 
Decode(api.payment_status_flag, 'Y', 
To_char(app.accounting_date, 'dd-MON-yyyy') 
, 
'P', 
To_char(app.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, 
Decode(api.payment_status_flag, 'Y', To_char(apc.check_number), 
'P', To_char(apc.check_number)) 
check_number, 
pov.segment1, 
pov.vendor_name, 
pov.vendor_type_lookup_code, 
apd.po_distribution_id, 
apc.exchange_rate_type, 
api.org_id, 
api.batch_id, 
apc.exchange_date, 
api.invoice_id, 
app.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, 
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_type_lookup_code LIKE 
Nvl(:p_invoice_type_lookup_code, api.invoice_type_lookup_code) 
--and  api.payment_currency_code like nvl(:p_invoice_currency_code ,api.payment_currency_code) 
-----------------------&P_WHERE_CURRENCY_TYPE1 
--AND api.PAYMENT_currency_code!=:P_CURRENCY 
AND api.vendor_site_id = Nvl(:p_vendor_site_id, api.vendor_site_id)) 
ORDER  BY invoice_id