select to_number(null) cash_receipt_id,
aip.accounting_date gl_date,
to_char(apc.doc_sequence_value) voucher_no,
to_char(apc.check_number) check_number,
apc.vendor_name vendor_name,
api.accts_pay_code_combination_id account_code,
0 receipts_ent,
0 receipts_acc,
sum(round(aip.amount,5)) payments_ent,
sum(round(aip.amount*nvl(aip.exchange_rate,1),5)) payments_acc,
aba.bank_account_name bank_account_name,
aba.bank_account_num bank_account_num,
aip.exchange_rate exchange_rate,
api.payment_currency_code curr,
'ap' flg,
api.vendor_id partyid,
to_char(null) status,
aba.bank_account_id,
'payables' transaction_source
from ap_invoice_payments_all aip,
ap_invoices_all api,
ap_checks_all apc,
ce_bank_accounts aba ,
ce_bank_acct_uses_all cbu
where api.invoice_id = aip.invoice_id
and aip.check_id = apc.check_id
and apc.ce_bank_acct_use_id = cbu.bank_acct_use_id
and aba.bank_account_id=cbu.bank_account_id
and
(
(
apc.status_lookup_code in ('negotiable', 'cleared','voided',
'reconciled unaccounted', 'reconciled', 'cleared but unaccounted' )
and aip.accounting_date between nvl(:p_start_date,aip.accounting_date) and nvl(:p_end_date,aip.accounting_date)
and apc.future_pay_due_date is null
)
or
(
apc.status_lookup_code in ('negotiable')
and apc.future_pay_due_date between nvl(:p_start_date,apc.future_pay_due_date) and nvl(:p_end_date,apc.future_pay_due_date)
)
)
and aba.bank_account_id = :p_bank_account_id
and aip.invoice_payment_type is null
and (api.org_id is null or api.org_id = :p_org_id)
group by aip.accounting_date,
apc.doc_sequence_value,
apc.check_number,
apc.vendor_name,
api.accts_pay_code_combination_id,
aba.bank_account_name,
aba.bank_account_num,
aip.exchange_rate,
api.payment_currency_code,
api.vendor_id,aba.bank_account_id
union all
select to_number(null) cash_receipt_id,
aip.accounting_date gl_date,
to_char(apc.doc_sequence_value) voucher_no,
to_char(apc.check_number) check_number,
apc.vendor_name vendor_name,
api.accts_pay_code_combination_id account_code,
0 receipts_ent,
0 receipts_acc,
sum(round(aip.amount,5)) payments_ent,
sum(round(aip.amount*nvl(aip.exchange_rate,1),5)) payments_acc,
aba.bank_account_name bank_account_name,
aba.bank_account_num bank_account_num,
aip.exchange_rate exchange_rate,
api.payment_currency_code curr,
'ap' flg,
api.vendor_id partyid,
to_char(null) status,
aba.bank_account_id,
'payables' transaction_source
from
ap_invoice_distributions_all apid,
ap_invoice_payments_all aip,
ap_checks_all apc,
ap_invoice_lines_all apla,
ap_invoices_all api,
po_vendors pov,
ce_bank_accounts aba
where api.invoice_id=apid.invoice_id
and apla.invoice_id = apid.invoice_id
and apla.line_number = apid.invoice_line_number
and api.invoice_id=aip.invoice_id
and apc.check_id=aip.check_id
and api.vendor_id = pov.vendor_id
and aba.bank_account_id = :p_bank_account_id
and aba.asset_code_combination_id=apid.dist_code_combination_id
and apid.match_status_flag = 'a'
and nvl(api.payment_status_flag,'n') <>'n'
and apid.accounting_date between nvl(:p_start_date,apid.accounting_date) and nvl(:p_end_date,apid.accounting_date)
and (api.org_id is null or api.org_id = :p_org_id)
group by aip.accounting_date,
apc.doc_sequence_value,
apc.check_number,
apc.vendor_name,
api.accts_pay_code_combination_id,
aba.bank_account_name,
aba.bank_account_num,
aip.exchange_rate,
api.payment_currency_code,
api.vendor_id,aba.bank_account_id
union
select acrh.cash_receipt_id cash_receipt_id,
acrh.gl_date gl_date,
acr.receipt_number voucher_no,
null check_number,
nvl(rc.customer_name, acr.comments) vendor_name,
acrh.account_code_combination_id account_code,
round (acrh.amount, 5) receipts_ent,
round (acrh.amount*nvl(acrh.exchange_rate,1), 5) receipts_acc,
0 payments_ent,
0 payments_acc,
aba.bank_account_name bank_account_name,
aba.bank_account_num bank_account_num,
0 exchange_rate,
acr.currency_code curr,
'ar' flg,
rc.customer_id partyid,
acrh.status status,
aba.bank_account_id,
'receivables' transaction_source
from ar_cash_receipt_history_all acrh,
ar_cash_receipts_all acr,
ar_cash_receipts_v rc,
ce_bank_accounts aba
,ce_bank_acct_uses_all cbaua
where acrh.cash_receipt_id(+) = acr.cash_receipt_id
--and nvl(reversal_created_from,'zzzz') <> 'rate adjustment trigger'
and rc.cash_receipt_id = acr.cash_receipt_id
--and rc.remittance_bank_branch_id=aba.bank_branch_id
and rc.remit_bank_acct_use_id=cbaua.bank_acct_use_id
and aba.bank_account_id=cbaua.bank_account_id
and acrh.status in ('cleared', 'remitted', 'confirmed')
and aba.bank_account_id = nvl(:p_bank_account_id, aba.bank_account_id)
and acr.remit_bank_acct_use_id =(select bank_acct_use_id from ce_bank_acct_uses_all
where bank_account_id=:p_bank_account_id and org_id=:p_org_id)
and trunc(acrh.gl_date ) between (:p_start_date) and (:p_end_date)
and (acr.org_id = :p_org_id or acr.org_id is null)
union all
select acrh.cash_receipt_id cash_receipt_id,
acrh.gl_date gl_date,
acr.receipt_number voucher_no,
null check_number,
nvl(rc.customer_name, acr.comments) vendor_name,
acrh.account_code_combination_id account_code,
round(decode(acrh.status,
'reversed',
(acrh.amount)*-1,acrh.amount ),5) receipts_ent,
round(decode(acrh.status,
'reversed',
(acrh.amount*
nvl(acrh.exchange_rate,1))*-1,
acrh.amount*
nvl(acrh.exchange_rate,1)),5) receipts_acc,
0 payments_ent,
0 payments_acc,
aba.bank_account_name bank_account_name,
aba.bank_account_num bank_account_num,
0 exchange_rate,
acr.currency_code curr,
'arrev' flg,
rc.customer_id partyid,
acrh.status status,
aba.bank_account_id,
'receivables' transaction_source
from ar_cash_receipt_history_all acrh,
ar_cash_receipts_all acr,
ar_cash_receipts_v rc,
ce_bank_accounts aba,
ce_bank_acct_uses_all ca
where acrh.cash_receipt_id = acr.cash_receipt_id
--and nvl(reversal_created_from,'zzzz') <> 'rate adjustment trigger'
and rc.remit_bank_acct_use_id=ca.bank_acct_use_id
and aba.bank_account_id=ca.bank_account_id
and acr.cash_receipt_id = rc.cash_receipt_id
and acrh.status in ('reversed')
and aba.bank_account_id = nvl(:p_bank_account_id, aba.bank_account_id)
and trunc(acrh.gl_date ) between (:p_start_date) and (:p_end_date)
and (acr.org_id = :p_org_id or acr.org_id is null)
union
select gjh.je_header_id cash_receipt_id,
gjh.default_effective_date gl_date,
to_char(gjh.doc_sequence_value) voucher_no,
to_char(gjl.je_line_num) check_number,
null vendor_name,
gjl.code_combination_id account_code,
nvl(gjl.entered_dr,0) receipts_ent,
nvl(gjl.accounted_dr,0) receipts_acc,
nvl(gjl.entered_cr,0) payments_ent,
nvl(gjl.accounted_cr,0) payments_acc,
aba.bank_account_name bank_account_name,
aba.bank_account_num bank_account_num,
nvl(gjh.currency_conversion_rate,1) exchange_rate,
gjh.currency_code curr,
'bank_transfer' flg,
null partyid,
null status,
aba.bank_account_id,
gjc.user_je_category_name transaction_source
from gl_je_headers gjh,
gl_je_lines gjl,
ce_bank_accounts aba,
gl_je_sources gjs,
gl_je_categories gjc,
ce_bank_acct_uses_all ca
where 1=1
and aba.bank_account_id = nvl(:p_bank_account_id, aba.bank_account_id)
and aba.asset_code_combination_id = gjl.code_combination_id
and ca.bank_account_id =aba.bank_account_id
and gjl.je_header_id = gjh.je_header_id
--and gjs.user_je_source_name in ('cash management','manual','receivables')
and gjs.je_source_name = gjh.je_source
and gjc.user_je_category_name in ('bank transfers(1)','bank transfers','bank transfer','xxxx accruals','cash management')
and gjc.je_category_name = gjh.je_category
and gjh.default_effective_date between (:p_start_date) and (nvl(:p_end_date,sysdate))
and ca.org_id=:p_org_id
order by 1
No comments:
Post a Comment