11i payment to gl

SELECT gjb.je_batch_id,gjb.name batch_name,h.je_source Batch_Source_Name,
       h.je_category Batch_Category,h.name Header_name,h.JE_HEADER_ID,l.JE_LINE_NUM,
       h.default_effective_date GL_Date,trunc(gjb.posted_date) Date_Posted,--glcc.code_combination_id,
       nvl(l.accounted_dr,0) Debit,nvl(l.accounted_cr,0) Credit,
       l.effective_date gldate,
        l.period_name,
        gcc.concatenated_segments "Account",
        NULL Check_Number,
        NULL  Check_Date,
        NULL  Check_Amount,
        NULL  Payment_Method,
       -- po.segment1      Vendor_Number,
        NULL  Vendor_Name,
        NULL Vendor_Site_Code,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL Bank_Number,
        NULL bank_Account_Number,
        NULL bank_Account_Name,       
        l.reference_1  vendorno,
        ael.reference1 vendorname,
        NVL ( l.reference_2, ael.source_id ) invoice_id,
        NVL ( l.reference_3, ael.reference3 ) Inv_Distribution_line_number,
        NVL ( l.reference_4, ael.reference4 ) ref_4,
        NVL ( l.reference_5, ael.reference5 ) invoice_num,
        NVL ( l.reference_6, ael.reference6 ) sourcetable,
        ael.source_table aelsourcetable,
        NVL ( l.reference_7, ael.reference7 ) ref_7 ,
        NVL ( l.reference_8, ael.reference8 ) ref_8 ,
        NVL ( l.reference_9 ,ael.reference9 ) ref_9 ,
        NVL ( l.reference_10, ael.reference10 ) line_type,
        ael.ae_line_type_code ,
     --   nvl(l.gl_sl_link_id, ir.gl_sl_link_id) gl_sl_link_id,
        h.je_source,
        h.je_category,
        h.status,
      --  h.je_batch_id,
        NVL ( l.subledger_doc_sequence_value, ael.subledger_doc_sequence_value ) vr_no,
       -- h.je_header_id,
       -- l.je_line_num,
        ael.accounted_dr Invoice_Line_amt_debit ,
        ael.accounted_cr Invoice_Line_amt_credit   
from     gl_je_batches gjb,
        apps.gl_je_lines l,
         apps.gl_je_headers h,
         apps.gl_import_references ir,
        apps.gl_code_combinations_kfv gcc,
        apps.ap_ae_lines_all ael
where     l.je_header_id = h.je_header_id
AND     gjb.je_batch_id = H.je_batch_id
and     l.je_header_id = ir.je_header_id
and     l.je_line_num = ir.je_line_num
and     nvl(l.gl_sl_link_table, ir.gl_sl_link_table) = 'APECL'
AND       l.code_combination_id = gcc.code_combination_id
and   gjb.name = nvl(:P_batch_name,gjb.name) --'56720 Payables 32824216: A 16270765'
AND gcc.segment1 BETWEEN NVL (:p_company_from, gcc.segment1)
                           AND NVL (:p_company_to, gcc.segment1)
     AND gcc.segment2 BETWEEN NVL (:p_exp_account_from, gcc.segment2)
                           AND NVL (:p_exp_account_to, gcc.segment2)
     AND gcc.segment3 BETWEEN NVL (:p_costcenter_from,gcc.segment3)
                           AND NVL (:p_costcenter_to, gcc.segment3)
     AND gcc.segment4 BETWEEN NVL (:p_acctqual_from, gcc.segment4)
                           AND NVL (:p_acctqual_to, gcc.segment4)
     AND gcc.segment5 BETWEEN NVL (:p_crew_from, gcc.segment5)
                           AND NVL (:p_crew_to, gcc.segment5)
AND l.code_combination_id = ael.code_combination_id
AND nvl(l.gl_sl_link_id, ir.gl_sl_link_id) = ael.gl_sl_link_id
and h.je_category = 'Purchase Invoices'
AND h.status = 'P'
UNION
SELECT  gjb.je_batch_id,gjb.name batch_name,h.je_source Batch_Source_Name,
        h.je_category Batch_Category,h.name Header_name,h.JE_HEADER_ID,l.JE_LINE_NUM,
        h.default_effective_date GL_Date,trunc(gjb.posted_date) Date_Posted,
        nvl(l.accounted_dr,0) Debit,nvl(l.accounted_cr,0) Credit,
        l.effective_date gldate,
        l.period_name,
        gcc.concatenated_segments "Account",  
        aca.check_number Check_Number,
        aca.check_date   Check_Date,
        aca.amount       Check_Amount,
        aca.PAYMENT_METHOD_LOOKUP_CODE Payment_Method,
       -- po.segment1      Vendor_Number,
        aca.vendor_name  Vendor_Name,
        aca.Vendor_site_code Vendor_Site_Code,
        aca.ADDRESS_LINE1,
        aca.ADDRESS_LINE2,
        aca.ADDRESS_LINE3,
        aca.city,
        aca.country,
        aca.bank_num Bank_Number,
        aca.bank_account_num bank_Account_Number,
        aca.Bank_account_name bank_Account_Name,       
        l.reference_1  vendorno,
        ael.reference1 vendorname,
        NVL ( l.reference_2, ael.source_id ) invoice_id,
        NVL ( l.reference_3, ael.reference3 ) Inv_Distribution_line_number,
        NVL ( l.reference_4, ael.reference4 ) ref_4,
        NVL ( l.reference_5, ael.reference5 ) invoice_num,
        NVL ( l.reference_6, ael.reference6 ) sourcetable,
        ael.source_table aelsourcetable,
        NVL ( l.reference_7, ael.reference7 ) ref_7 ,
        NVL ( l.reference_8, ael.reference8 ) ref_8 ,
        NVL ( l.reference_9 ,ael.reference9 ) ref_9 ,
        NVL ( l.reference_10, ael.reference10 ) line_type,
        ael.ae_line_type_code ,
     --   nvl(l.gl_sl_link_id, ir.gl_sl_link_id) gl_sl_link_id,
        h.je_source,
        h.je_category,
        h.status,
      --  h.je_batch_id,
        NVL ( l.subledger_doc_sequence_value, ael.subledger_doc_sequence_value ) vr_no,
       -- h.je_header_id,
       -- l.je_line_num,
        ael.accounted_dr Invoice_Line_amt_debit ,
        ael.accounted_cr Invoice_Line_amt_credit       
from    gl_je_batches gjb,
        apps.gl_je_lines l,
        apps.gl_je_headers h,
        apps.gl_import_references ir,
        apps.gl_code_combinations_kfv gcc,
        apps.ap_ae_lines_all ael,
        apps.ap_checks_all aca,
       -- ap_bank_branches bb,
        apps.ap_bank_accounts_all ba
      --  ap_bank_account_uses_all bau
where     l.je_header_id = h.je_header_id
AND     gjb.je_batch_id = H.je_batch_id
and     l.je_header_id = ir.je_header_id
and     l.je_line_num = ir.je_line_num
and     nvl(l.gl_sl_link_table, ir.gl_sl_link_table) = 'APECL'
AND       l.code_combination_id = gcc.code_combination_id
and   h.je_category = 'Payments'
and   gjb.name = nvl(:P_batch_name,gjb.name) --'56720 Payables 32824216: A 16270765'
AND gcc.segment1 BETWEEN NVL (:p_company_from, gcc.segment1)
                           AND NVL (:p_company_to, gcc.segment1)
AND gcc.segment2 BETWEEN NVL (:p_exp_account_from, gcc.segment2)
                           AND NVL (:p_exp_account_to, gcc.segment2)
     AND gcc.segment3 BETWEEN NVL (:p_costcenter_from,gcc.segment3)
                           AND NVL (:p_costcenter_to, gcc.segment3)
     AND gcc.segment4 BETWEEN NVL (:p_acctqual_from, gcc.segment4)
                           AND NVL (:p_acctqual_to, gcc.segment4)
     AND gcc.segment5 BETWEEN NVL (:p_crew_from, gcc.segment5)
                           AND NVL (:p_crew_to, gcc.segment5)
AND     l.code_combination_id = ael.code_combination_id
AND     nvl(l.gl_sl_link_id, ir.gl_sl_link_id) = ael.gl_sl_link_id
and     ael.source_id = aca.check_id
and     aca.BANK_ACCOUNT_ID = ba.bank_account_id
--and     bb.bank_branch_id = ba.bank_branch_id
--AND        ba.bank_account_id = bau.external_bank_account_id
--and     ael.source_id = 973342
AND     h.status = 'P'
order by 6,7