Bank Vochur Report

select api.invoice_num
,       to_char(api.invoice_date, 'DD-MON-YYYY')   "INV_DATE"
,      ac.bank_account_name
,      aip.amount * (nvl(aip.EXCHANGE_RATE,1)) LINE_AMOUNT
,      ac.check_number
,      to_char(ac.check_date, 'DD-MON-YYYY')   "CHECK_DATE"
,      ac.amount  AMOUNT_USD
,      ac.CURRENCY_CODE     
,       ac.EXCHANGE_RATE     
,      ac.amount * (nvl(ac.EXCHANGE_RATE,1))  PAY_AMOUNT    
,      ac.vendor_name
,      ac.vendor_site_code
,      ac.DOC_SEQUENCE_VALUE
,      ac.org_id
,      ff.description
,      aba.bank_account_num
,       papf.FULL_NAME             
from   ap_invoices_all api
,      ap_invoice_payments_all  aip
,      ap_checks_all ac
,      gl_code_combinations gcc
,      ap_bank_accounts_all aba
,      fnd_flex_values_vl  ff
,       fnd_user fu
,       per_all_people_f papf       
where  api.invoice_id=aip.invoice_id
and    api.org_id=aip.org_id
and    gcc.segment4=ff.flex_value
and    ff.flex_value_set_id=1009939
and    gcc.code_combination_id=aba.asset_code_combination_id
and    aip.check_id=ac.check_id
and    api.vendor_id<>7
and    ac.bank_account_id=aba.bank_account_id
and    ac.org_id=aba.org_id
and    ac.bank_account_id=nvl(:P_BANK,ac.bank_account_id)
and    ac.check_number=nvl(:P_CHECK_NUM,ac.check_number)
and    ac.org_id=nvl(:P_ORG_ID,ac.org_id)
and    aip.org_id=ac.org_id
and    ac.CREATED_BY=fu.USER_ID           
and    fu.EMPLOYEE_ID=papf.PERSON_ID       
group by api.invoice_num
,      api.invoice_date
,      ac.bank_account_name
,      ac.check_number
,      ac.check_date
,      ac.amount
,       aip.EXCHANGE_RATE
,      ac.DOC_SEQUENCE_VALUE
,      ac.vendor_name
,      ac.vendor_site_code
,      ac.org_id
,      aip.amount
,      ac.CURRENCY_CODE     
,       ac.EXCHANGE_RATE      
,      ff.description
,      aba.bank_account_num
,       papf.FULL_NAME            
union
select api.invoice_num
,      to_char(api.invoice_date, 'DD-MON-YYYY')  "INV_DATE"
,      ac.bank_account_name
,      aip.amount * (nvl(aip.EXCHANGE_RATE,1)) LINE_AMOUNT
,      ac.check_number
,        to_char(ac.check_date, 'DD-MON-YYYY')   "CHECK_DATE"
,      ac.amount
,      ac.CURRENCY_CODE     
,       ac.EXCHANGE_RATE     
,      ac.amount * (nvl(ac.EXCHANGE_RATE,1))  Base_Amount    
,      ac.vendor_name
,      pap.title||' '||pap.first_name||pap.last_name||'(EMP CODE '||pap.employee_number ||')'  "Supplier Site Code"
,      ac.DOC_SEQUENCE_VALUE
,      ac.org_id
,      ff.description
,      aba.bank_account_num
,      papf.FULL_NAME           
from   ap_invoices_all api
,      ap_invoice_payments_all  aip
,      ap_checks_all ac
,      per_all_people_f pap
,      ap_bank_accounts_all aba
,      gl_code_combinations gcc
,      fnd_flex_values_vl  ff
,       fnd_user fu              
,       per_all_people_f papf      
where  api.invoice_id=aip.invoice_id
and    api.org_id=aip.org_id
and    api.vendor_id=7
and    ac.bank_account_id=aba.bank_account_id
and    ac.org_id=aba.org_id
and    gcc.segment4=ff.flex_value
and    ff.flex_value_set_id=1009939
and    gcc.code_combination_id=aba.asset_code_combination_id
and    ac.bank_account_id=nvl(:P_BANK,ac.bank_account_id)
and    ac.check_number=nvl(:P_CHECK_NUM,ac.check_number)
and    ac.org_id=nvl(:P_ORG_ID,ac.org_id)
and    aip.check_id=ac.check_id
and    aip.org_id=ac.org_id
and    ac.vendor_site_code=pap.employee_number
and    ac.CREATED_BY=fu.USER_ID         
and    fu.EMPLOYEE_ID=papf.PERSON_ID     
group by api.invoice_num
,      api.invoice_date
,      ac.bank_account_name
,      ac.check_number
,      ac.check_date
,      ac.amount
,       aip.EXCHANGE_RATE
,      ac.DOC_SEQUENCE_VALUE
,      ac.vendor_name
,      pap.title||' '||pap.first_name||pap.last_name||'(EMP CODE '||pap.employee_number
,      ac.org_id
,      aip.amount
,      ac.CURRENCY_CODE     
,      ff.description
,      aba.bank_account_num
,       papf.FULL_NAME