ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN C9B298A27BC27D53E0530100007F6F5A CE Bank Transaction Types Y select cl.meaning value, cl.description description from ce_lookups cl where cl.lookup_type='BANK_TRX_TYPE' order by cl.meaning US C8A7FD7AC923667BE0530100007F4469 CE Internal Bank Account Check Digits (Secured) Y with ce_sec_v as (select /*+ materialize */ cbaua.bank_account_id, cspv.organization_type from ce_bank_acct_uses_all cbaua, ce_security_profiles_v cspv where ((cbaua.org_id = cspv.organization_id and cspv.organization_type = 'OPERATING_UNIT') or (cbaua.legal_entity_id = cspv.organization_id and cspv.organization_type = 'LEGAL_ENTITY') or (cbaua.org_id = cspv.organization_id and cspv.organization_type = 'BUSINESS_GROUP') ) and trunc(nvl(cbaua.end_date,sysdate)) >= trunc(sysdate) ) select cba.check_digits value, hp.party_name||' ('||cba.currency_code||') Account:' || cba.bank_account_name || ' (' || cba.masked_account_num || ')' description from ce_bank_accounts cba, hz_parties hp where cba.check_digits is not null and cba.account_classification = 'INTERNAL' and nvl(cba.netting_acct_flag,'N') = 'N' and hp.party_id = cba.bank_id and (:$flex$.legal_entity is null or cba.account_owner_org_id in (select xep.legal_entity_id from xle_entity_profiles xep where xxen_util.contains(:$flex$.legal_entity,xep.name) = 'Y') ) and ( ((cba.ap_use_allowed_flag = 'Y' or cba.ar_use_allowed_flag ='Y') and exists (select null from ce_sec_v where ce_sec_v.bank_account_id = cba.bank_account_id and ce_sec_v.organization_type = 'OPERATING_UNIT') ) or (cba.xtr_use_allowed_flag ='Y' and exists (select null from ce_sec_v where ce_sec_v.bank_account_id = cba.bank_account_id and ce_sec_v.organization_type = 'LEGAL_ENTITY') ) or (cba.pay_use_allowed_flag ='Y' and exists (select null from ce_sec_v where ce_sec_v.bank_account_id = cba.bank_account_id and ce_sec_v.organization_type = 'BUSINESS_GROUP') ) ) order by cba.check_digits US CE Internal Bank Account Check Digits secured by CE Security Profile C8A7FD7AC922667BE0530100007F4469 CE Internal Bank Account Currency (Secured) Y select distinct cba.currency_code value, fcv.name description from ce_bank_accounts cba, fnd_currencies_vl fcv where fcv.currency_code = cba.currency_code and ( ((cba.ap_use_allowed_flag = 'Y' or cba.ar_use_allowed_flag ='Y') and exists (select null from ce_bank_acct_uses_all cbaua,ce_security_profiles_v cspv where cbaua.bank_account_id = cba.bank_account_id and cbaua.org_id = cspv.organization_id and cspv.organization_type = 'OPERATING_UNIT' and trunc(nvl(cbaua.end_date,sysdate)) >= trunc(sysdate)) ) or (cba.xtr_use_allowed_flag ='Y' and exists (select null from ce_bank_acct_uses_all cbaua,ce_security_profiles_v cspv where cbaua.bank_account_id = cba.bank_account_id and cbaua.org_id = cspv.organization_id and cspv.organization_type = 'LEGAL_ENTITY' and trunc(nvl(cbaua.end_date,sysdate)) >= trunc(sysdate)) ) or (cba.pay_use_allowed_flag ='Y' and exists (select null from ce_bank_acct_uses_all cbaua,ce_security_profiles_v cspv where cbaua.bank_account_id = cba.bank_account_id and cbaua.org_id = cspv.organization_id and cspv.organization_type = 'BUSINESS_GROUP' and trunc(nvl(cbaua.end_date,sysdate)) >= trunc(sysdate)) ) ) order by cba.currency_code US CE Internal Bank Account Curency Codes secured by CE Security Profile C8A7FD7AC920667BE0530100007F4469 CE Internal Bank Account Name (Secured) Y with ce_sec_v as (select /*+ materialize */ cbaua.bank_account_id, cspv.organization_type from ce_bank_acct_uses_all cbaua, ce_security_profiles_v cspv where ((cbaua.org_id = cspv.organization_id and cspv.organization_type = 'OPERATING_UNIT') or (cbaua.legal_entity_id = cspv.organization_id and cspv.organization_type = 'LEGAL_ENTITY') or (cbaua.org_id = cspv.organization_id and cspv.organization_type = 'BUSINESS_GROUP') ) and trunc(nvl(cbaua.end_date,sysdate)) >= trunc(sysdate) ) select cba.bank_account_name value, hpb.party_name||' ('||cba.currency_code||') Account#: ' || cba.masked_account_num description from ce_bank_accounts cba, hz_parties hpb, hz_parties hpbb where cba.account_classification = 'INTERNAL' and nvl(cba.netting_acct_flag,'N') = 'N' and hpb.party_id = cba.bank_id and hpbb.party_id = cba.bank_branch_id and (:$flex$.legal_entity is null or cba.account_owner_org_id in (select xep.legal_entity_id from xle_entity_profiles xep where xxen_util.contains(:$flex$.legal_entity,xep.name) = 'Y') ) and hpb.party_name = nvl(:$flex$.bank_name,hpb.party_name) and hpbb.party_name = nvl(:$flex$.bank_branch,hpbb.party_name) and ( ((cba.ap_use_allowed_flag = 'Y' or cba.ar_use_allowed_flag ='Y') and exists (select null from ce_sec_v where ce_sec_v.bank_account_id = cba.bank_account_id and ce_sec_v.organization_type = 'OPERATING_UNIT') ) or (cba.xtr_use_allowed_flag ='Y' and exists (select null from ce_sec_v where ce_sec_v.bank_account_id = cba.bank_account_id and ce_sec_v.organization_type = 'LEGAL_ENTITY') ) or (cba.pay_use_allowed_flag ='Y' and exists (select null from ce_sec_v where ce_sec_v.bank_account_id = cba.bank_account_id and ce_sec_v.organization_type = 'BUSINESS_GROUP') ) ) order by cba.bank_account_name US CE Internal Bank Account Names secured by CE Security Profile C8A7FD7AC91F667BE0530100007F4469 CE Internal Bank Account Number (secured, masked) Y with ce_sec_v as (select /*+ materialize */ cbaua.bank_account_id, cspv.organization_type from ce_bank_acct_uses_all cbaua, ce_security_profiles_v cspv where ((cbaua.org_id = cspv.organization_id and cspv.organization_type = 'OPERATING_UNIT') or (cbaua.legal_entity_id = cspv.organization_id and cspv.organization_type = 'LEGAL_ENTITY') or (cbaua.org_id = cspv.organization_id and cspv.organization_type = 'BUSINESS_GROUP') ) and trunc(nvl(cbaua.end_date,sysdate)) >= trunc(sysdate) ) select cba.masked_account_num value, hpb.party_name||' ('||cba.currency_code||') Account:' || cba.bank_account_name description from ce_bank_accounts cba, hz_parties hpb, hz_parties hpbb where cba.account_classification = 'INTERNAL' and nvl(cba.netting_acct_flag,'N') = 'N' and hpb.party_id = cba.bank_id and hpbb.party_id = cba.bank_branch_id and (:$flex$.legal_entity is null or cba.account_owner_org_id in (select xep.legal_entity_id from xle_entity_profiles xep where xxen_util.contains(:$flex$.legal_entity,xep.name) = 'Y') ) and hpb.party_name = nvl(:$flex$.bank_name,hpb.party_name) and hpbb.party_name = nvl(:$flex$.bank_branch,hpbb.party_name) and ( ((cba.ap_use_allowed_flag = 'Y' or cba.ar_use_allowed_flag ='Y') and exists (select null from ce_sec_v where ce_sec_v.bank_account_id = cba.bank_account_id and ce_sec_v.organization_type = 'OPERATING_UNIT') ) or (cba.xtr_use_allowed_flag ='Y' and exists (select null from ce_sec_v where ce_sec_v.bank_account_id = cba.bank_account_id and ce_sec_v.organization_type = 'LEGAL_ENTITY') ) or (cba.pay_use_allowed_flag ='Y' and exists (select null from ce_sec_v where ce_sec_v.bank_account_id = cba.bank_account_id and ce_sec_v.organization_type = 'BUSINESS_GROUP') ) ) order by cba.masked_account_num US CE Internal Bank Account Numbers secured by CE Security Profile C8A7FD7AC924667BE0530100007F4469 CE Internal Bank Account Statement Doc Seq Number (Secured, Account Dependent) Y with ce_sec_v as (select /*+ materialize */ cbaua.bank_account_id, cspv.organization_type from ce_bank_acct_uses_all cbaua, ce_security_profiles_v cspv where ((cbaua.org_id = cspv.organization_id and cspv.organization_type = 'OPERATING_UNIT') or (cbaua.legal_entity_id = cspv.organization_id and cspv.organization_type = 'LEGAL_ENTITY') or (cbaua.org_id = cspv.organization_id and cspv.organization_type = 'BUSINESS_GROUP') ) and trunc(nvl(cbaua.end_date,sysdate)) >= trunc(sysdate) ) select csh.doc_sequence_value value, csh.statement_date || ' ' || hp.party_name||' ('||cba.currency_code||') Account:' || cba.bank_account_name || ' (' || cba.masked_account_num || ')' description from ce_statement_headers csh, ce_bank_accounts cba, hz_parties hp where cba.account_classification = 'INTERNAL' and nvl(cba.netting_acct_flag,'N') = 'N' and csh.doc_sequence_value is not null and csh.bank_account_id = cba.bank_account_id and hp.party_id = cba.bank_id and (:$flex$.bank_account_name is null or xxen_util.contains(:$flex$.bank_account_name,cba.bank_account_name) = 'Y') and (:$flex$.bank_account_number is null or xxen_util.contains(:$flex$.bank_account_number,cba.masked_account_num) = 'Y') and (:$flex$.check_digits is null or xxen_util.contains(:$flex$.check_digits,cba.check_digits) = 'Y') and (:$flex$.legal_entity is null or cba.account_owner_org_id in (select xep.legal_entity_id from xle_entity_profiles xep where xxen_util.contains(:$flex$.legal_entity,xep.name) = 'Y') ) and ( ((cba.ap_use_allowed_flag = 'Y' or cba.ar_use_allowed_flag ='Y') and exists (select null from ce_sec_v where ce_sec_v.bank_account_id = cba.bank_account_id and ce_sec_v.organization_type = 'OPERATING_UNIT') ) or (cba.xtr_use_allowed_flag ='Y' and exists (select null from ce_sec_v where ce_sec_v.bank_account_id = cba.bank_account_id and ce_sec_v.organization_type = 'LEGAL_ENTITY') ) or (cba.pay_use_allowed_flag ='Y' and exists (select null from ce_sec_v where ce_sec_v.bank_account_id = cba.bank_account_id and ce_sec_v.organization_type = 'BUSINESS_GROUP') ) ) order by csh.doc_sequence_value desc US CE Internal Bank Account Statement Document Sequence Numbers secured by CE Security Profile and Dependent on Account Number and/or Account Name C8A7FD7AC921667BE0530100007F4469 CE Internal Bank Account Statement Number (Secured, Account Dependent) Y with ce_sec_v as (select /*+ materialize */ cbaua.bank_account_id, cspv.organization_type from ce_bank_acct_uses_all cbaua, ce_security_profiles_v cspv where ((cbaua.org_id = cspv.organization_id and cspv.organization_type = 'OPERATING_UNIT') or (cbaua.legal_entity_id = cspv.organization_id and cspv.organization_type = 'LEGAL_ENTITY') or (cbaua.org_id = cspv.organization_id and cspv.organization_type = 'BUSINESS_GROUP') ) and trunc(nvl(cbaua.end_date,sysdate)) >= trunc(sysdate) ) select csh.statement_number value, csh.statement_date || ' ' || hp.party_name||' ('||cba.currency_code||') Account:' || cba.bank_account_name || ' (' || cba.masked_account_num || ')' description from ce_statement_headers csh, ce_bank_accounts cba, hz_parties hp where cba.account_classification = 'INTERNAL' and nvl(cba.netting_acct_flag,'N') = 'N' and csh.bank_account_id = cba.bank_account_id and hp.party_id = cba.bank_id and (:$flex$.bank_account_name is null or xxen_util.contains(:$flex$.bank_account_name,cba.bank_account_name) = 'Y') and (:$flex$.bank_account_number is null or xxen_util.contains(:$flex$.bank_account_number,cba.masked_account_num) = 'Y') and (:$flex$.check_digits is null or xxen_util.contains(:$flex$.check_digits,cba.check_digits) = 'Y') and (:$flex$.legal_entity is null or cba.account_owner_org_id in (select xep.legal_entity_id from xle_entity_profiles xep where xxen_util.contains(:$flex$.legal_entity,xep.name) = 'Y') ) and ( ((cba.ap_use_allowed_flag = 'Y' or cba.ar_use_allowed_flag ='Y') and exists (select null from ce_sec_v where ce_sec_v.bank_account_id = cba.bank_account_id and ce_sec_v.organization_type = 'OPERATING_UNIT') ) or (cba.xtr_use_allowed_flag ='Y' and exists (select null from ce_sec_v where ce_sec_v.bank_account_id = cba.bank_account_id and ce_sec_v.organization_type = 'LEGAL_ENTITY') ) or (cba.pay_use_allowed_flag ='Y' and exists (select null from ce_sec_v where ce_sec_v.bank_account_id = cba.bank_account_id and ce_sec_v.organization_type = 'BUSINESS_GROUP') ) ) order by csh.statement_date desc US CE Internal Bank Account Statement Numbers secured by CE Security Profile and Dependent on Account Number and/or Account Name CB5B97B15AC6A126E05362FB09055DE0 CE Internal Bank Branch Names Y select cbbv.bank_branch_name value, cbbv.bank_name description from ce_bank_branches_v cbbv where cbbv.branch_party_id in (select cba.bank_branch_id from ce_bank_accounts cba where cba.account_classification='INTERNAL' and nvl(cba.netting_acct_flag,'N') = 'N' ) and cbbv.bank_name = nvl(:$flex$.Bank_name,cbbv.bank_name) order by cbbv.bank_branch_name, cbbv.bank_name US CE Internal Bank Branch Names CB5B97B15AC5A126E05362FB09055DE0 CE Internal Bank Names Y select distinct cbbv.bank_name value, null description from ce_bank_branches_v cbbv where cbbv.branch_party_id in (select cba.bank_branch_id from ce_bank_accounts cba where cba.account_classification='INTERNAL' and nvl(cba.netting_acct_flag,'N') = 'N' ) order by cbbv.bank_name US CE Internal Bank Names 8E2FF36EDE9079D2E0530100007F1FF2 FND Currency Y select fcv.currency_code value, fcv.description from fnd_currencies_vl fcv where fcv.enabled_flag='Y' order by fcv.currency_code US 8E2FF36EDEA679D2E0530100007F1FF2 Yes Y select 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from dual US C8A7FD7AC91E667BE0530100007F4469 with ce_bank_statements as ( select /*+ ordered index(hpb hz_parties_u1) index(hpbb hz_parties_u1) index(xep xle_entity_profiles_u1) index(gcc gl_code_combinations_u1) push_pred(nsf_recon) push_pred(oth_recon) */ -- Statement Details xep.name legal_entity, cbagv.masked_account_num bank_account_num, cbagv.bank_account_name bank_account_name, nvl(xxen_util.meaning(cbagv.bank_account_type,'BANK_ACCOUNT_TYPE',260),cbagv.bank_account_type) bank_account_type, cbagv.currency_code bank_account_currency, hpb.party_name bank_name, hpbb.party_name branch_name, csh.statement_number statement_num, csh.doc_sequence_value document_number, trunc(csh.statement_date) statement_date, trunc(csh.gl_date) gl_date, csh.check_digits, csh.control_begin_balance control_opening_balance, nvl(csh.control_total_cr,0) - nvl(csh.control_total_dr,0) control_net_movement, csh.control_end_balance control_closing_balance, csh.control_total_cr control_receipts, csh.control_total_dr control_payments, nvl(csh.control_cr_line_count,0) + nvl(csh.control_dr_line_count,0) control_lines_count, csh.control_cr_line_count control_receipts_count, csh.control_dr_line_count control_payments_count, csh.cashflow_balance, csh.int_calc_balance value_dated_balance, csh.one_day_float, csh.two_day_float, xxen_util.meaning(csh.auto_loaded_flag,'YES_NO',0) auto_loaded_flag, xxen_util.meaning(csh.statement_complete_flag,'YES_NO',0) statement_complete_flag, xxen_util.meaning(cbagv.multi_currency_allowed_flag,'YES_NO',0) multi_currency_flag, -- -- Statement Line Details csl.line_number line_num, xxen_util.meaning(csl.trx_type,'BANK_TRX_TYPE',260) trx_type, csl.trx_code code, csl.bank_trx_number trx_number, trunc(csl.trx_date) trx_date, trunc(csl.effective_date) value_date, xxen_util.meaning(csl.status,'STATEMENT_LINE_STATUS',260) status, csl.status status_code, -- credit/debit amounts decode(count(csl.statement_line_id) over (partition by csl.statement_line_id order by csl.statement_line_id rows between unbounded preceding and current row) ,1,case when csl.trx_type in ('MISC_CREDIT','CREDIT','STOP','SWEEP_IN') then csl.amount else null end ,null ) receipt_amount, decode(count(csl.statement_line_id) over (partition by csl.statement_line_id order by csl.statement_line_id rows between unbounded preceding and current row) ,1,case when csl.trx_type in ('MISC_DEBIT','DEBIT','REJECTED','NSF','SWEEP_OUT') then csl.amount else null end ,null ) payment_amount, -- net amounts decode(count(csl.statement_line_id) over (partition by csl.statement_line_id order by csl.statement_line_id rows between unbounded preceding and current row) ,1,case when csl.trx_type in ('MISC_DEBIT','DEBIT','REJECTED','NSF','SWEEP_OUT') then -1 else 1 end * csl.amount ,null ) net_amount, decode(count(csl.statement_line_id) over (partition by csl.statement_line_id order by csl.statement_line_id rows between unbounded preceding and current row) ,1,case when csl.trx_type in ('MISC_DEBIT','DEBIT','REJECTED','NSF','SWEEP_OUT') then -1 else 1 end * decode(csl.status ,'EXTERNAL',csl.amount ,decode(csl.trx_type,'NSF',nsf_recon.amount_matched,oth_recon.amount_matched) ) ,null ) net_reconciled_amount, decode(count(csl.statement_line_id) over (partition by csl.statement_line_id order by csl.statement_line_id rows between unbounded preceding and current row) ,1,case when csl.trx_type in ('MISC_DEBIT','DEBIT','REJECTED','NSF','SWEEP_OUT') then -1 else 1 end * decode(csl.status ,'EXTERNAL',to_number(null) ,decode(sign(csl.amount - nvl(decode(csl.trx_type,'NSF',nsf_recon.amount_matched,oth_recon.amount_matched),0)) ,1,csl.amount - nvl(decode(csl.trx_type,'NSF',nsf_recon.amount_matched,oth_recon.amount_matched),0) ,null ) ) ,null ) net_unreconciled_amount, decode(count(csl.statement_line_id) over (partition by csl.statement_line_id order by csl.statement_line_id rows between unbounded preceding and current row) ,1,case when csl.trx_type in ('MISC_DEBIT','DEBIT','REJECTED','NSF','SWEEP_OUT') then -1 else 1 end * decode(csl.status ,'EXTERNAL',to_number(null) ,decode(sign(csl.amount - nvl(decode(csl.trx_type,'NSF',nsf_recon.amount_matched,oth_recon.amount_matched),0)) ,-1,-(csl.amount - nvl(decode(csl.trx_type,'NSF',nsf_recon.amount_matched,oth_recon.amount_matched),0)) ,null ) ) ,null ) net_overreconciled_amount, -- amounts decode(count(csl.statement_line_id) over (partition by csl.statement_line_id order by csl.statement_line_id rows between unbounded preceding and current row) ,1,csl.amount ,null ) amount, decode(count(csl.statement_line_id) over (partition by csl.statement_line_id order by csl.statement_line_id rows between unbounded preceding and current row) ,1,decode(csl.status ,'EXTERNAL',csl.amount ,decode(csl.trx_type,'NSF',nsf_recon.amount_matched,oth_recon.amount_matched) ) ,null ) reconciled_amount, decode(count(csl.statement_line_id) over (partition by csl.statement_line_id order by csl.statement_line_id rows between unbounded preceding and current row) ,1,decode(csl.status ,'EXTERNAL',to_number(null) ,decode(sign(csl.amount - nvl(decode(csl.trx_type,'NSF',nsf_recon.amount_matched,oth_recon.amount_matched),0)) ,1,csl.amount - nvl(decode(csl.trx_type,'NSF',nsf_recon.amount_matched,oth_recon.amount_matched),0) ,null ) ) ,null ) unreconciled_amount, decode(count(csl.statement_line_id) over (partition by csl.statement_line_id order by csl.statement_line_id rows between unbounded preceding and current row) ,1,decode(csl.status ,'EXTERNAL',to_number(null) ,decode(sign(csl.amount - nvl(decode(csl.trx_type,'NSF',nsf_recon.amount_matched,oth_recon.amount_matched),0)) ,-1,-(csl.amount - nvl(decode(csl.trx_type,'NSF',nsf_recon.amount_matched,oth_recon.amount_matched),0)) ,null ) ) ,null ) overreconciled_amount, -- -- counts decode(count(csl.statement_line_id) over (partition by csl.statement_line_id order by csl.statement_line_id rows between unbounded preceding and current row) ,1,1 ,null ) line_count, decode(count(csl.statement_line_id) over (partition by csl.statement_line_id order by csl.statement_line_id rows between unbounded preceding and current row) ,1,case when csl.trx_type in ('MISC_CREDIT','CREDIT','STOP','SWEEP_IN') then 1 else 0 end ,null ) receipt_line_count, decode(count(csl.statement_line_id) over (partition by csl.statement_line_id order by csl.statement_line_id rows between unbounded preceding and current row) ,1,case when csl.trx_type in ('MISC_DEBIT','DEBIT','REJECTED','NSF','SWEEP_OUT') then 1 else 0 end ,null ) payment_line_count, decode(count(csl.statement_line_id) over (partition by csl.statement_line_id order by csl.statement_line_id rows between unbounded preceding and current row) ,1,decode(csl.status,'UNRECONCILED',1,'ERROR',1,0) ,null ) unreconciled_line_count, decode(count(csl.statement_line_id) over (partition by csl.statement_line_id order by csl.statement_line_id rows between unbounded preceding and current row) ,1,decode(csl.trx_type,'NSF',nsf_recon.count_matched,oth_recon.count_matched) ,null ) reconciled_trx_count, -- charges decode(count(csl.statement_line_id) over (partition by csl.statement_line_id order by csl.statement_line_id rows between unbounded preceding and current row) ,1,decode(csl.charges_amount,0,to_number(null),csl.charges_amount) ,null ) charges_amount, -- csl.currency_code trx_currency, trunc(csl.exchange_rate_date) exchange_date, ( select gdct.user_conversion_type from gl_daily_conversion_types gdct where gdct.conversion_type = csl.exchange_rate_type ) exchange_rate_type, csl.exchange_rate, csl.original_amount line_original_amount, (select sum( case when crtv.application_id = 101 then nvl((select nvl(gjl.entered_dr,gjl.entered_cr) from gl_je_lines gjl where gjl.je_header_id = crtv.cash_receipt_id and gjl.je_line_num = crtv.trx_id),0) when crtv.application_id = 200 then nvl((select aca.amount from ap_checks_all aca where aca.check_id = crtv.trx_id),0) when crtv.application_id = 222 then nvl((select nvl(acrha.amount+nvl(acrha.factor_discount_amount,0), acrha.amount+nvl(acrha.factor_discount_amount,0)) from ar_cash_receipt_history_all acrha where acrha.cash_receipt_history_id = crtv.trx_id),0) when crtv.application_id = 260 and crtv.clearing_trx_type = 'CASHFLOW' then nvl((select cc.cashflow_amount from ce_cashflows cc, fnd_currencies fc where cc.cashflow_id = crtv.trx_id and cc.cashflow_currency_code = fc.currency_code),0) when crtv.application_id = 260 and crtv.clearing_trx_type = 'STATEMENT' then nvl((select csl.amount from ce_statement_lines csl where csl.statement_line_id = crtv.trx_id),0) when crtv.application_id = 801 and crtv.clearing_trx_type = 'PAY_EFT' then nvl((select ppp.value from pay_assignment_actions paa, pay_pre_payments ppp where paa.assignment_action_id = crtv.trx_id and paa.pre_payment_id = ppp.pre_payment_id),0) when crtv.application_id = 801 and crtv.clearing_trx_type = 'PAY' and crtv.status = 'V' then nvl((select ppp.value from pay_action_interlocks pai, pay_assignment_actions paa, pay_pre_payments ppp where pai.locking_action_id = crtv.trx_id and pai.locked_action_id = paa.assignment_action_id and paa.pre_payment_id = ppp.pre_payment_id),0) when crtv.application_id = 801 and crtv.clearing_trx_type = 'PAY' and nvl(crtv.status,'X') != 'V' then nvl((select ppp.value from pay_assignment_actions paa, pay_pre_payments ppp where paa.assignment_action_id = crtv.trx_id and paa.pre_payment_id = ppp.pre_payment_id),0) when crtv.application_id = 999 then nvl((select civ.amount from ce_999_interface_v civ where civ.trx_id = crtv.trx_id),0) when crtv.application_id = 185 then nvl((select abs(xss.settlement_amount) from xtr_settlement_summary xss where xss.settlement_summary_id = crtv.trx_id),0) end ) from ce_reconciled_transactions_v crtv where crtv.statement_line_id = csl.statement_line_id ) trx_original_amount, (select sum( case when crtv.application_id = 101 then nvl((select nvl(gjl.accounted_dr,gjl.accounted_cr) from gl_je_lines gjl where gjl.je_header_id = crtv.cash_receipt_id and gjl.je_line_num = crtv.trx_id),0) when crtv.application_id = 200 then nvl((select nvl(aca.base_amount,aca.amount) from ap_checks_all aca where aca.check_id = crtv.trx_id),0) when crtv.application_id = 222 then nvl((select nvl(acrha.acctd_amount+nvl(acrha.acctd_factor_discount_amount,0), acrha.amount+nvl(acrha.factor_discount_amount,0)) from ar_cash_receipt_history_all acrha where acrha.cash_receipt_history_id = crtv.trx_id),0) when crtv.application_id = 260 and crtv.clearing_trx_type = 'CASHFLOW' then nvl((select nvl(cc.base_amount,decode(fc.minimum_accountable_unit, null, round((cc.cashflow_amount * nvl(cc.cashflow_exchange_rate,1)), nvl(fc.precision,2)), (round(((cc.cashflow_amount * nvl(cc.cashflow_exchange_rate,1))/fc.minimum_accountable_unit),0) * fc.minimum_accountable_unit))) from ce_cashflows cc, fnd_currencies fc where cc.cashflow_id = crtv.trx_id and cc.cashflow_currency_code = fc.currency_code),0) when crtv.application_id = 260 and crtv.clearing_trx_type = 'STATEMENT' then nvl((select nvl(csl.original_amount,csl.amount) from ce_statement_lines csl where csl.statement_line_id = crtv.trx_id),0) when crtv.application_id = 801 and crtv.clearing_trx_type = 'PAY_EFT' then nvl((select nvl(ppp.base_currency_value, ppp.value) from pay_assignment_actions paa, pay_pre_payments ppp where paa.assignment_action_id = crtv.trx_id and paa.pre_payment_id = ppp.pre_payment_id),0) when crtv.application_id = 801 and crtv.clearing_trx_type = 'PAY' and crtv.status = 'V' then nvl((select nvl(ppp.base_currency_value, ppp.value) from pay_action_interlocks pai, pay_assignment_actions paa, pay_pre_payments ppp where pai.locking_action_id = crtv.trx_id and pai.locked_action_id = paa.assignment_action_id and paa.pre_payment_id = ppp.pre_payment_id),0) when crtv.application_id = 801 and crtv.clearing_trx_type = 'PAY' and nvl(crtv.status,'X') != 'V' then nvl((select nvl(ppp.base_currency_value, ppp.value) from pay_assignment_actions paa, pay_pre_payments ppp where paa.assignment_action_id = crtv.trx_id and paa.pre_payment_id = ppp.pre_payment_id),0) when crtv.application_id = 999 then nvl((select nvl(civ.acctd_amount,civ.amount) from ce_999_interface_v civ where civ.trx_id = crtv.trx_id),0) when crtv.application_id = 185 then nvl((select abs(xss.settlement_amount) from xtr_settlement_summary xss where xss.settlement_summary_id = crtv.trx_id),0) end ) from ce_reconciled_transactions_v crtv where crtv.statement_line_id = csl.statement_line_id ) trx_original_amount_acctd, -- nvl( csl.customer_text , ( select /*+ push_pred */ crtv.agent_name from ce_reconciled_transactions_v crtv where crtv.statement_line_id = csl.statement_line_id and rownum=1 ) ) agent, csl.bank_account_text agent_bank_account, csl.invoice_text invoice, csl.trx_text description, ( select /*+ push_pred */ distinct listagg(crev.error_message,', ') within group (order by crev.error_message) over () from ( select distinct crev.statement_header_id, crev.statement_line_id, fnd_message.get_string(crev.application_short_name,crev.message_name) error_message from ce_reconciliation_errors_v crev ) crev where crev.statement_header_id = csl.statement_header_id and crev.statement_line_id = csl.statement_line_id ) error_messages, xxen_util.user_name(csl.last_updated_by) line_last_updated_by, xxen_util.client_time(csl.last_update_date) line_last_updated_date, -- -- GL Cash Account Details nvl2(gcc.code_combination_id,fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_flex_bal_seg', 'SQLGL', 'GL#', gcc.chart_of_accounts_id, NULL, gcc.code_combination_id, 'GL_BALANCING', 'Y', 'VALUE'),null) gl_company_code, nvl2(gcc.code_combination_id,fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_flex_bal_seg', 'SQLGL', 'GL#', gcc.chart_of_accounts_id, NULL, gcc.code_combination_id, 'GL_BALANCING', 'Y', 'DESCRIPTION'),null) gl_company_desc, nvl2(gcc.code_combination_id,fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_flex_bal_seg', 'SQLGL', 'GL#', gcc.chart_of_accounts_id, NULL, gcc.code_combination_id, 'GL_ACCOUNT', 'Y', 'VALUE'),null) gl_account_code, nvl2(gcc.code_combination_id,fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_flex_bal_seg', 'SQLGL', 'GL#', gcc.chart_of_accounts_id, NULL, gcc.code_combination_id, 'GL_ACCOUNT', 'Y', 'DESCRIPTION'),null) gl_account_desc, nvl2(gcc.code_combination_id,fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_flex_bal_seg', 'SQLGL', 'GL#', gcc.chart_of_accounts_id, NULL, gcc.code_combination_id, 'FA_COST_CTR', 'Y', 'VALUE'),null) gl_cost_center_code, nvl2(gcc.code_combination_id,fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_flex_bal_seg', 'SQLGL', 'GL#', gcc.chart_of_accounts_id, NULL, gcc.code_combination_id, 'FA_COST_CTR', 'Y', 'DESCRIPTION'),null) gl_cost_center_desc, nvl2(gcc.code_combination_id,fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_flex_bal_seg', 'SQLGL', 'GL#', gcc.chart_of_accounts_id, NULL, gcc.code_combination_id, 'ALL', 'Y', 'VALUE'),null) gl_cash_account, nvl2(gcc.code_combination_id,fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_flex_bal_seg', 'SQLGL', 'GL#', gcc.chart_of_accounts_id, NULL, gcc.code_combination_id, 'ALL', 'Y', 'DESCRIPTION'),null) gl_cash_account_desc, -- csh.statement_header_id, csl.statement_line_id, count(csl.statement_line_id) over (partition by csl.statement_line_id order by csl.statement_line_id rows between unbounded preceding and current row) recon_sort_num, decode(csl.status,'EXTERNAL','N',decode(sign(csl.amount - nvl(decode(csl.trx_type,'NSF',nsf_recon.amount_matched,oth_recon.amount_matched),0)),1,'Y','N')) unrecon_amount_exists_flag from ce_bank_accts_gt_v cbagv, ce_statement_headers csh, ce_statement_lines csl, hz_parties hpb, hz_parties hpbb, xle_entity_profiles xep, gl_code_combinations gcc, ( select csra.statement_line_id, sum( decode(csra.reference_type ,'RECEIPT',decode(acra.type ,'MISC',decode(acrha.status,'CLEARED',nvl(-csra.amount,0),nvl(csra.amount,0)) , nvl(csra.amount,0) ) ,nvl(csra.amount,0) ) ) amount_matched, count(*) count_matched from ce_statement_reconcils_all csra, ar_cash_receipt_history_all acrha, ar_cash_receipts_all acra where csra.status_flag = 'M' and csra.current_record_flag = 'Y' and csra.reference_id = acrha.cash_receipt_history_id (+) and acrha.cash_receipt_id = acra.cash_receipt_id (+) group by csra.statement_line_id ) nsf_recon, ( select csl.statement_line_id, sum(decode(decode(csl.trx_type,'MISC_CREDIT','RECEIPT','CREDIT','RECEIPT','MISC_DEBIT','PAYMENT','SWEEP_IN','RECEIPT','SWEEP_OUT','PAYMENT','DEBIT','PAYMENT','OTHERS') , 'RECEIPT', decode(csra.reference_type,'PAYMENT',nvl(-csra.amount,0),nvl(csra.amount,0)) , 'PAYMENT', decode(csra.reference_type,'RECEIPT',nvl(-csra.amount,0),nvl(csra.amount,0)) , nvl(csra.amount,0) ) ) amount_matched, count(*) count_matched from ce_statement_reconcils_all csra, ce_statement_lines csl where csra.statement_line_id = csl.statement_line_id and csra.status_flag = 'M' and csra.current_record_flag = 'Y' group by csl.statement_line_id ) oth_recon where csh.bank_account_id = cbagv.bank_account_id and csl.statement_header_id = csh.statement_header_id and hpb.party_id = cbagv.bank_id and hpbb.party_id = cbagv.bank_branch_id and xep.legal_entity_id = cbagv.account_owner_org_id and gcc.code_combination_id (+) = cbagv.asset_code_combination_id and nsf_recon.statement_line_id (+) = decode(csl.trx_type,'NSF',csl.statement_line_id,null) and oth_recon.statement_line_id (+) = decode(csl.trx_type,'NSF',null,csl.statement_line_id) and 1=1 ) ,ce_recon_transactions as ( select /*+ push_pred */ crtv.statement_line_id, crtv.type_meaning recon_trx_type, crtv.trx_number recon_trx_number, crtv.currency_code recon_trx_currency, crtv.amount recon_trx_amount, crtv.bank_account_amount recon_bank_account_amount, case when crtv.status in ('STOP INITIATED', 'VOIDED', 'V') then to_number(null) else crtv.amount_cleared end recon_trx_amount_cleared, trunc(crtv.cleared_date) recon_trx_cleared_date, trunc(crtv.maturity_date) recon_trx_maturity_date, trunc(crtv.value_date) recon_trx_value_date, trunc(crtv.gl_date) recon_trx_gl_date, trunc(crtv.trx_date) recon_trx_date, trunc(crtv.exchange_rate_date) recon_trx_exchange_rate_date, crtv.exchange_rate_type recon_trx_exchange_rate_type, crtv.exchange_rate_dsp recon_trx_exchange_rate, crtv.bank_charges recon_trx_bank_charges, crtv.bank_errors recon_trx_bank_errors, crtv.remittance_number recon_trx_remittance_number, crtv.batch_name recon_trx_batch_name, case when crtv.cash_receipt_id is not null and crtv.application_id = 222 then ( select ifte.payment_system_order_number from ar_cash_receipts_all acra, iby_fndcpt_tx_extensions ifte where acra.payment_trxn_extension_id = ifte.trxn_extension_id(+) and acra.cash_receipt_id = crtv.cash_receipt_id and rownum=1 ) else null end recon_trx_pson, crtv.agent_name recon_trx_agent_name, crtv.reference_type_dsp recon_trx_reference_type, case when crtv.reference_type = 'REMITTANCE' then ( select aba.name from ar_batches_all aba where aba.batch_id = crtv.reference_id and rownum=1) when crtv.reference_type = 'PAYMENT_BATCH' then to_char(crtv.reference_id) when crtv.reference_type = 'RECEIPT' then ( select acra.receipt_number from ar_cash_receipts_all acra where acra.cash_receipt_id = crtv.reference_id and rownum=1) when crtv.reference_type in ('PAYMENT','REFUND') then ( select to_char(aca.check_number) from ap_checks_all aca where aca.check_id = crtv.reference_id and rownum=1) end recon_trx_reference_number, haou.name recon_trx_operating_unit, crtv.status_dsp recon_trx_status from ce_reconciled_transactions_v crtv, hr_all_organization_units haou where 2=2 and :p_show_trx is not null and haou.organization_id (+) = crtv.org_id ) -- -- Main Query Starts Here select /*+ use_nl(crt cebs) */ cebs.legal_entity, cebs.bank_account_num, cebs.bank_account_name, cebs.bank_account_type, cebs.bank_account_currency, cebs.bank_name, cebs.branch_name, cebs.statement_num, cebs.document_number, cebs.statement_date, cebs.gl_date, cebs.check_digits, cebs.control_opening_balance, cebs.control_net_movement, cebs.control_closing_balance, cebs.control_receipts, cebs.control_payments, cebs.control_lines_count, cebs.control_receipts_count, cebs.control_payments_count, -- case when nvl(cebs.control_receipts,0) = nvl(sum(cebs.receipt_amount) over (partition by cebs.statement_header_id),0) and nvl(cebs.control_payments,0) = nvl(sum(cebs.payment_amount) over (partition by cebs.statement_header_id),0) then null else 'Yes' end control_amount_error, case when nvl(cebs.control_receipts_count,0) = nvl(sum(cebs.receipt_line_count) over (partition by cebs.statement_header_id),0) and nvl(cebs.control_payments_count,0) = nvl(sum(cebs.payment_line_count) over (partition by cebs.statement_header_id),0) then null else 'Yes' end control_count_error, -- cebs.cashflow_balance, cebs.value_dated_balance, cebs.one_day_float, cebs.two_day_float, cebs.auto_loaded_flag, cebs.statement_complete_flag, cebs.multi_currency_flag, -- -- Statement Line Details cebs.line_num, cebs.trx_type, cebs.code, cebs.trx_number, cebs.trx_date, cebs.value_date, cebs.status, cebs.receipt_amount, cebs.payment_amount, cebs.net_amount, cebs.net_reconciled_amount, cebs.net_unreconciled_amount, cebs.net_overreconciled_amount, cebs.amount, cebs.reconciled_amount, cebs.unreconciled_amount, cebs.overreconciled_amount, cebs.line_count, cebs.receipt_line_count, cebs.payment_line_count, cebs.unreconciled_line_count, cebs.reconciled_trx_count, cebs.charges_amount, cebs.trx_currency, cebs.exchange_date, cebs.exchange_rate_type, cebs.exchange_rate, cebs.line_original_amount, cebs.trx_original_amount, cebs.trx_original_amount_acctd, decode(sign(cebs.net_reconciled_amount),-1,-1,1) * abs(cebs.trx_original_amount) net_trx_original_amount, decode(sign(cebs.net_reconciled_amount),-1,-1,1) * abs(cebs.trx_original_amount_acctd) net_trx_original_amount_acctd, cebs.agent, cebs.agent_bank_account, cebs.invoice, cebs.description, cebs.error_messages, cebs.line_last_updated_by, cebs.line_last_updated_date, -- -- Reconcilation Trx Details crt.recon_trx_type, crt.recon_trx_number, crt.recon_trx_currency, crt.recon_trx_amount, crt.recon_bank_account_amount, crt.recon_trx_amount_cleared, crt.recon_trx_cleared_date, crt.recon_trx_maturity_date, crt.recon_trx_value_date, crt.recon_trx_gl_date, crt.recon_trx_date, crt.recon_trx_exchange_rate_date, crt.recon_trx_exchange_rate_type, crt.recon_trx_exchange_rate, crt.recon_trx_bank_charges, crt.recon_trx_bank_errors, crt.recon_trx_remittance_number, crt.recon_trx_batch_name, crt.recon_trx_pson, crt.recon_trx_agent_name, crt.recon_trx_reference_type, crt.recon_trx_reference_number, crt.recon_trx_operating_unit, crt.recon_trx_status, -- -- GL Cash Account Details cebs.gl_company_code, cebs.gl_company_desc, cebs.gl_account_code, cebs.gl_account_desc, cebs.gl_cost_center_code, cebs.gl_cost_center_desc, cebs.gl_cash_account, cebs.gl_cash_account_desc, -- cebs.statement_header_id, cebs.statement_line_id, cebs.recon_sort_num, -- pivot labels cebs.gl_company_code || ' - ' || cebs.gl_company_desc gl_company_pivot_label, cebs.bank_name || ' - ' || cebs.bank_account_num || ' - ' || cebs.bank_account_name || ' (' || cebs.bank_account_currency || ')' bank_account_pivot_label, to_char(cebs.statement_date,'YYYY-MM-DD') || ' - ' || cebs.statement_num statement_num_pivot_label from ce_bank_statements cebs, ce_recon_transactions crt where 3=3 and cebs.statement_line_id = crt.statement_line_id (+) order by cebs.legal_entity, cebs.bank_name, cebs.bank_account_num, cebs.statement_date, cebs.statement_num, cebs.line_num, cebs.recon_sort_num, lpad(crt.recon_trx_number, 240) Y CE_CEXSTMRR_XMLP_PKG :Bank_Name is not null or :Bank_Branch is not null or :Bank_Account_Name is not null or :Bank_Account_Number is not null or :Check_Digits is not null or :Statement_Date_From is not null or :Statement_Date_To is not null or :Statement_GL_Date_From is not null or :Statement_GL_Date_To is not null or :Statement_Number_From is not null or :Statement_Number_To is not null or :Document_Number_From is not null or :Document_Number_To is not null or :Statement_Currency is not null or :Statement_Complete is not null or :Trx_Date_From is not null or :Trx_Date_To is not null or :Payer_Payee_Name_Like is not null or :Trx_Type is not null or :Trx_Number is not null or :Invoice_Number is not null or :Trx_Amount is not null or :Trx_Currency is not null or :Display_Reconciled_Transactio is not null or :Transaction_Cleared_Date_From is not null or :Transaction_Cleared_Date_To is not null US CE Bank Statement and Reconciliation Application: Cash Management Description: Bank Statements - Bank Statement Details and Reconciliation Provides equivalent functionality to the following standard Oracle Forms/Reports - Bank Statements and Reconciliation Form Applicable Templates: Pivot: Bank Statement by Transaction Type Bank Statement Detail Bank Statement Detail with Reconciled Trxs - Bank Statement Summary Report Applicable Templates: Pivot: Bank Statement by Transaction Type Bank Statement Summary - Bank Statement Detail Report Applicable Templates: Pivot: Bank Statement by Transaction Type Bank Statement Detail Bank Statement Detail with Reconciled Trxs This Blitz Report offers an extended set of parameters over and above the standard form/reports to search for specific reconciled transactions. Sources: Bank Statement Detail Report (CEXSTMRR) Bank Statement Summary Report (CEXSTMSR) DB package: CE_CEXSTMRR_XMLP_PKG (required to initialize security) Enginatics R12 only 1=1 2=2 3=3 :c_account_name_dsp :c_account_number_dsp :c_all_translation :c_bank_branch_dsp :c_bank_curr_dsp :c_bank_name_dsp :c_display_message :c_external_translation :c_gl_currency_code :c_name :c_set_of_books_id :c_stat_date_sql_lex :c_stat_number_sql_lex :c_the_end :c_trx_reference_type :p_bank_account_id :p_conc_request_id :p_debug_mode :p_show_trx :p_sql_trace :p_stat_date_from :p_stat_date_from_t :p_stat_date_to :p_stat_date_to_t :p_stat_number_from :p_stat_number_to :p_statement_or_account :p_stmt_line_status :p_test_layout 1 10 1=1 hpb.party_name=:p_bank_name LOV CE Internal Bank Names CB5B97B15AC5A126E05362FB09055DE0 Y select distinct cbbv.bank_name value, null description from ce_bank_branches_v cbbv where cbbv.branch_party_id in (select cba.bank_branch_id from ce_bank_accounts cba where cba.account_classification='INTERNAL' and nvl(cba.netting_acct_flag,'N') = 'N' ) order by cbbv.bank_name F Nom de la banque US Bank Name 2 20 1=1 hpbb.party_name=:p_bank_branch_name LOV CE Internal Bank Branch Names CB5B97B15AC6A126E05362FB09055DE0 Y select cbbv.bank_branch_name value, cbbv.bank_name description from ce_bank_branches_v cbbv where cbbv.branch_party_id in (select cba.bank_branch_id from ce_bank_accounts cba where cba.account_classification='INTERNAL' and nvl(cba.netting_acct_flag,'N') = 'N' ) and cbbv.bank_name = nvl(:$flex$.Bank_name,cbbv.bank_name) order by cbbv.bank_branch_name, cbbv.bank_name F Succursale bancaire US Bank Branch 3 30 1=1 cbagv.bank_account_name=:p_account_name LOV CE Internal Bank Account Name (Secured) C8A7FD7AC920667BE0530100007F4469 Y with ce_sec_v as (select /*+ materialize */ cbaua.bank_account_id, cspv.organization_type from ce_bank_acct_uses_all cbaua, ce_security_profiles_v cspv where ((cbaua.org_id = cspv.organization_id and cspv.organization_type = 'OPERATING_UNIT') or (cbaua.legal_entity_id = cspv.organization_id and cspv.organization_type = 'LEGAL_ENTITY') or (cbaua.org_id = cspv.organization_id and cspv.organization_type = 'BUSINESS_GROUP') ) and trunc(nvl(cbaua.end_date,sysdate)) >= trunc(sysdate) ) select cba.bank_account_name value, hpb.party_name||' ('||cba.currency_code||') Account#: ' || cba.masked_account_num description from ce_bank_accounts cba, hz_parties hpb, hz_parties hpbb where cba.account_classification = 'INTERNAL' and nvl(cba.netting_acct_flag,'N') = 'N' and hpb.party_id = cba.bank_id and hpbb.party_id = cba.bank_branch_id and (:$flex$.legal_entity is null or cba.account_owner_org_id in (select xep.legal_entity_id from xle_entity_profiles xep where xxen_util.contains(:$flex$.legal_entity,xep.name) = 'Y') ) and hpb.party_name = nvl(:$flex$.bank_name,hpb.party_name) and hpbb.party_name = nvl(:$flex$.bank_branch,hpbb.party_name) and ( ((cba.ap_use_allowed_flag = 'Y' or cba.ar_use_allowed_flag ='Y') and exists (select null from ce_sec_v where ce_sec_v.bank_account_id = cba.bank_account_id and ce_sec_v.organization_type = 'OPERATING_UNIT') ) or (cba.xtr_use_allowed_flag ='Y' and exists (select null from ce_sec_v where ce_sec_v.bank_account_id = cba.bank_account_id and ce_sec_v.organization_type = 'LEGAL_ENTITY') ) or (cba.pay_use_allowed_flag ='Y' and exists (select null from ce_sec_v where ce_sec_v.bank_account_id = cba.bank_account_id and ce_sec_v.organization_type = 'BUSINESS_GROUP') ) ) order by cba.bank_account_name F Nom du compte bancaire US Bank Account Name 4 40 1=1 cbagv.masked_account_num=:p_account_num LOV CE Internal Bank Account Number (secured, masked) C8A7FD7AC91F667BE0530100007F4469 Y with ce_sec_v as (select /*+ materialize */ cbaua.bank_account_id, cspv.organization_type from ce_bank_acct_uses_all cbaua, ce_security_profiles_v cspv where ((cbaua.org_id = cspv.organization_id and cspv.organization_type = 'OPERATING_UNIT') or (cbaua.legal_entity_id = cspv.organization_id and cspv.organization_type = 'LEGAL_ENTITY') or (cbaua.org_id = cspv.organization_id and cspv.organization_type = 'BUSINESS_GROUP') ) and trunc(nvl(cbaua.end_date,sysdate)) >= trunc(sysdate) ) select cba.masked_account_num value, hpb.party_name||' ('||cba.currency_code||') Account:' || cba.bank_account_name description from ce_bank_accounts cba, hz_parties hpb, hz_parties hpbb where cba.account_classification = 'INTERNAL' and nvl(cba.netting_acct_flag,'N') = 'N' and hpb.party_id = cba.bank_id and hpbb.party_id = cba.bank_branch_id and (:$flex$.legal_entity is null or cba.account_owner_org_id in (select xep.legal_entity_id from xle_entity_profiles xep where xxen_util.contains(:$flex$.legal_entity,xep.name) = 'Y') ) and hpb.party_name = nvl(:$flex$.bank_name,hpb.party_name) and hpbb.party_name = nvl(:$flex$.bank_branch,hpbb.party_name) and ( ((cba.ap_use_allowed_flag = 'Y' or cba.ar_use_allowed_flag ='Y') and exists (select null from ce_sec_v where ce_sec_v.bank_account_id = cba.bank_account_id and ce_sec_v.organization_type = 'OPERATING_UNIT') ) or (cba.xtr_use_allowed_flag ='Y' and exists (select null from ce_sec_v where ce_sec_v.bank_account_id = cba.bank_account_id and ce_sec_v.organization_type = 'LEGAL_ENTITY') ) or (cba.pay_use_allowed_flag ='Y' and exists (select null from ce_sec_v where ce_sec_v.bank_account_id = cba.bank_account_id and ce_sec_v.organization_type = 'BUSINESS_GROUP') ) ) order by cba.masked_account_num F Numéro de compte bancaire US Bank Account Number 5 50 1=1 csh.check_digits=:p_check_digits LOV CE Internal Bank Account Check Digits (Secured) C8A7FD7AC923667BE0530100007F4469 Y with ce_sec_v as (select /*+ materialize */ cbaua.bank_account_id, cspv.organization_type from ce_bank_acct_uses_all cbaua, ce_security_profiles_v cspv where ((cbaua.org_id = cspv.organization_id and cspv.organization_type = 'OPERATING_UNIT') or (cbaua.legal_entity_id = cspv.organization_id and cspv.organization_type = 'LEGAL_ENTITY') or (cbaua.org_id = cspv.organization_id and cspv.organization_type = 'BUSINESS_GROUP') ) and trunc(nvl(cbaua.end_date,sysdate)) >= trunc(sysdate) ) select cba.check_digits value, hp.party_name||' ('||cba.currency_code||') Account:' || cba.bank_account_name || ' (' || cba.masked_account_num || ')' description from ce_bank_accounts cba, hz_parties hp where cba.check_digits is not null and cba.account_classification = 'INTERNAL' and nvl(cba.netting_acct_flag,'N') = 'N' and hp.party_id = cba.bank_id and (:$flex$.legal_entity is null or cba.account_owner_org_id in (select xep.legal_entity_id from xle_entity_profiles xep where xxen_util.contains(:$flex$.legal_entity,xep.name) = 'Y') ) and ( ((cba.ap_use_allowed_flag = 'Y' or cba.ar_use_allowed_flag ='Y') and exists (select null from ce_sec_v where ce_sec_v.bank_account_id = cba.bank_account_id and ce_sec_v.organization_type = 'OPERATING_UNIT') ) or (cba.xtr_use_allowed_flag ='Y' and exists (select null from ce_sec_v where ce_sec_v.bank_account_id = cba.bank_account_id and ce_sec_v.organization_type = 'LEGAL_ENTITY') ) or (cba.pay_use_allowed_flag ='Y' and exists (select null from ce_sec_v where ce_sec_v.bank_account_id = cba.bank_account_id and ce_sec_v.organization_type = 'BUSINESS_GROUP') ) ) order by cba.check_digits F Chiffres de contrôle US Check Digits 6 60 1=1 csh.statement_date>=:p_statement_date_from Date F Date de déclaration du US Statement Date From 7 70 1=1 csh.statement_date<:p_statement_date_to+1 Date :$flex$.Statement_Date_From F Date du relevé au US Statement Date To 8 80 1=1 csh.gl_date>=:p_gl_date_from Date F Date du relevé GL Du US Statement GL Date From 9 90 1=1 csh.gl_date<:p_gl_date_to+1 Date :$flex$.statement_gl_date_from F Déclaration GL Date à US Statement GL Date To 10 100 1=1 csh.statement_number>=:p_statement_num_from LOV CE Internal Bank Account Statement Number (Secured, Account Dependent) C8A7FD7AC921667BE0530100007F4469 Y with ce_sec_v as (select /*+ materialize */ cbaua.bank_account_id, cspv.organization_type from ce_bank_acct_uses_all cbaua, ce_security_profiles_v cspv where ((cbaua.org_id = cspv.organization_id and cspv.organization_type = 'OPERATING_UNIT') or (cbaua.legal_entity_id = cspv.organization_id and cspv.organization_type = 'LEGAL_ENTITY') or (cbaua.org_id = cspv.organization_id and cspv.organization_type = 'BUSINESS_GROUP') ) and trunc(nvl(cbaua.end_date,sysdate)) >= trunc(sysdate) ) select csh.statement_number value, csh.statement_date || ' ' || hp.party_name||' ('||cba.currency_code||') Account:' || cba.bank_account_name || ' (' || cba.masked_account_num || ')' description from ce_statement_headers csh, ce_bank_accounts cba, hz_parties hp where cba.account_classification = 'INTERNAL' and nvl(cba.netting_acct_flag,'N') = 'N' and csh.bank_account_id = cba.bank_account_id and hp.party_id = cba.bank_id and (:$flex$.bank_account_name is null or xxen_util.contains(:$flex$.bank_account_name,cba.bank_account_name) = 'Y') and (:$flex$.bank_account_number is null or xxen_util.contains(:$flex$.bank_account_number,cba.masked_account_num) = 'Y') and (:$flex$.check_digits is null or xxen_util.contains(:$flex$.check_digits,cba.check_digits) = 'Y') and (:$flex$.legal_entity is null or cba.account_owner_org_id in (select xep.legal_entity_id from xle_entity_profiles xep where xxen_util.contains(:$flex$.legal_entity,xep.name) = 'Y') ) and ( ((cba.ap_use_allowed_flag = 'Y' or cba.ar_use_allowed_flag ='Y') and exists (select null from ce_sec_v where ce_sec_v.bank_account_id = cba.bank_account_id and ce_sec_v.organization_type = 'OPERATING_UNIT') ) or (cba.xtr_use_allowed_flag ='Y' and exists (select null from ce_sec_v where ce_sec_v.bank_account_id = cba.bank_account_id and ce_sec_v.organization_type = 'LEGAL_ENTITY') ) or (cba.pay_use_allowed_flag ='Y' and exists (select null from ce_sec_v where ce_sec_v.bank_account_id = cba.bank_account_id and ce_sec_v.organization_type = 'BUSINESS_GROUP') ) ) order by csh.statement_date desc F Numéro de déclaration De US Statement Number From 11 110 1=1 csh.statement_number<=:p_statement_num_to LOV CE Internal Bank Account Statement Number (Secured, Account Dependent) C8A7FD7AC921667BE0530100007F4469 Y with ce_sec_v as (select /*+ materialize */ cbaua.bank_account_id, cspv.organization_type from ce_bank_acct_uses_all cbaua, ce_security_profiles_v cspv where ((cbaua.org_id = cspv.organization_id and cspv.organization_type = 'OPERATING_UNIT') or (cbaua.legal_entity_id = cspv.organization_id and cspv.organization_type = 'LEGAL_ENTITY') or (cbaua.org_id = cspv.organization_id and cspv.organization_type = 'BUSINESS_GROUP') ) and trunc(nvl(cbaua.end_date,sysdate)) >= trunc(sysdate) ) select csh.statement_number value, csh.statement_date || ' ' || hp.party_name||' ('||cba.currency_code||') Account:' || cba.bank_account_name || ' (' || cba.masked_account_num || ')' description from ce_statement_headers csh, ce_bank_accounts cba, hz_parties hp where cba.account_classification = 'INTERNAL' and nvl(cba.netting_acct_flag,'N') = 'N' and csh.bank_account_id = cba.bank_account_id and hp.party_id = cba.bank_id and (:$flex$.bank_account_name is null or xxen_util.contains(:$flex$.bank_account_name,cba.bank_account_name) = 'Y') and (:$flex$.bank_account_number is null or xxen_util.contains(:$flex$.bank_account_number,cba.masked_account_num) = 'Y') and (:$flex$.check_digits is null or xxen_util.contains(:$flex$.check_digits,cba.check_digits) = 'Y') and (:$flex$.legal_entity is null or cba.account_owner_org_id in (select xep.legal_entity_id from xle_entity_profiles xep where xxen_util.contains(:$flex$.legal_entity,xep.name) = 'Y') ) and ( ((cba.ap_use_allowed_flag = 'Y' or cba.ar_use_allowed_flag ='Y') and exists (select null from ce_sec_v where ce_sec_v.bank_account_id = cba.bank_account_id and ce_sec_v.organization_type = 'OPERATING_UNIT') ) or (cba.xtr_use_allowed_flag ='Y' and exists (select null from ce_sec_v where ce_sec_v.bank_account_id = cba.bank_account_id and ce_sec_v.organization_type = 'LEGAL_ENTITY') ) or (cba.pay_use_allowed_flag ='Y' and exists (select null from ce_sec_v where ce_sec_v.bank_account_id = cba.bank_account_id and ce_sec_v.organization_type = 'BUSINESS_GROUP') ) ) order by csh.statement_date desc :$flex$.Statement_Number_From F Numéro de déclaration à US Statement Number To 12 120 1=1 csh.doc_sequence_value>=:p_document_num_from LOV CE Internal Bank Account Statement Doc Seq Number (Secured, Account Dependent) C8A7FD7AC924667BE0530100007F4469 Y with ce_sec_v as (select /*+ materialize */ cbaua.bank_account_id, cspv.organization_type from ce_bank_acct_uses_all cbaua, ce_security_profiles_v cspv where ((cbaua.org_id = cspv.organization_id and cspv.organization_type = 'OPERATING_UNIT') or (cbaua.legal_entity_id = cspv.organization_id and cspv.organization_type = 'LEGAL_ENTITY') or (cbaua.org_id = cspv.organization_id and cspv.organization_type = 'BUSINESS_GROUP') ) and trunc(nvl(cbaua.end_date,sysdate)) >= trunc(sysdate) ) select csh.doc_sequence_value value, csh.statement_date || ' ' || hp.party_name||' ('||cba.currency_code||') Account:' || cba.bank_account_name || ' (' || cba.masked_account_num || ')' description from ce_statement_headers csh, ce_bank_accounts cba, hz_parties hp where cba.account_classification = 'INTERNAL' and nvl(cba.netting_acct_flag,'N') = 'N' and csh.doc_sequence_value is not null and csh.bank_account_id = cba.bank_account_id and hp.party_id = cba.bank_id and (:$flex$.bank_account_name is null or xxen_util.contains(:$flex$.bank_account_name,cba.bank_account_name) = 'Y') and (:$flex$.bank_account_number is null or xxen_util.contains(:$flex$.bank_account_number,cba.masked_account_num) = 'Y') and (:$flex$.check_digits is null or xxen_util.contains(:$flex$.check_digits,cba.check_digits) = 'Y') and (:$flex$.legal_entity is null or cba.account_owner_org_id in (select xep.legal_entity_id from xle_entity_profiles xep where xxen_util.contains(:$flex$.legal_entity,xep.name) = 'Y') ) and ( ((cba.ap_use_allowed_flag = 'Y' or cba.ar_use_allowed_flag ='Y') and exists (select null from ce_sec_v where ce_sec_v.bank_account_id = cba.bank_account_id and ce_sec_v.organization_type = 'OPERATING_UNIT') ) or (cba.xtr_use_allowed_flag ='Y' and exists (select null from ce_sec_v where ce_sec_v.bank_account_id = cba.bank_account_id and ce_sec_v.organization_type = 'LEGAL_ENTITY') ) or (cba.pay_use_allowed_flag ='Y' and exists (select null from ce_sec_v where ce_sec_v.bank_account_id = cba.bank_account_id and ce_sec_v.organization_type = 'BUSINESS_GROUP') ) ) order by csh.doc_sequence_value desc F Numéro du document De US Document Number From 13 130 1=1 csh.doc_sequence_value<=:p_document_num_to LOV CE Internal Bank Account Statement Doc Seq Number (Secured, Account Dependent) C8A7FD7AC924667BE0530100007F4469 Y with ce_sec_v as (select /*+ materialize */ cbaua.bank_account_id, cspv.organization_type from ce_bank_acct_uses_all cbaua, ce_security_profiles_v cspv where ((cbaua.org_id = cspv.organization_id and cspv.organization_type = 'OPERATING_UNIT') or (cbaua.legal_entity_id = cspv.organization_id and cspv.organization_type = 'LEGAL_ENTITY') or (cbaua.org_id = cspv.organization_id and cspv.organization_type = 'BUSINESS_GROUP') ) and trunc(nvl(cbaua.end_date,sysdate)) >= trunc(sysdate) ) select csh.doc_sequence_value value, csh.statement_date || ' ' || hp.party_name||' ('||cba.currency_code||') Account:' || cba.bank_account_name || ' (' || cba.masked_account_num || ')' description from ce_statement_headers csh, ce_bank_accounts cba, hz_parties hp where cba.account_classification = 'INTERNAL' and nvl(cba.netting_acct_flag,'N') = 'N' and csh.doc_sequence_value is not null and csh.bank_account_id = cba.bank_account_id and hp.party_id = cba.bank_id and (:$flex$.bank_account_name is null or xxen_util.contains(:$flex$.bank_account_name,cba.bank_account_name) = 'Y') and (:$flex$.bank_account_number is null or xxen_util.contains(:$flex$.bank_account_number,cba.masked_account_num) = 'Y') and (:$flex$.check_digits is null or xxen_util.contains(:$flex$.check_digits,cba.check_digits) = 'Y') and (:$flex$.legal_entity is null or cba.account_owner_org_id in (select xep.legal_entity_id from xle_entity_profiles xep where xxen_util.contains(:$flex$.legal_entity,xep.name) = 'Y') ) and ( ((cba.ap_use_allowed_flag = 'Y' or cba.ar_use_allowed_flag ='Y') and exists (select null from ce_sec_v where ce_sec_v.bank_account_id = cba.bank_account_id and ce_sec_v.organization_type = 'OPERATING_UNIT') ) or (cba.xtr_use_allowed_flag ='Y' and exists (select null from ce_sec_v where ce_sec_v.bank_account_id = cba.bank_account_id and ce_sec_v.organization_type = 'LEGAL_ENTITY') ) or (cba.pay_use_allowed_flag ='Y' and exists (select null from ce_sec_v where ce_sec_v.bank_account_id = cba.bank_account_id and ce_sec_v.organization_type = 'BUSINESS_GROUP') ) ) order by csh.doc_sequence_value desc :$flex$.Document_Number_From F Numéro de document À US Document Number To 14 140 1=1 csh.currency_code=:p_currency_code LOV CE Internal Bank Account Currency (Secured) C8A7FD7AC922667BE0530100007F4469 Y select distinct cba.currency_code value, fcv.name description from ce_bank_accounts cba, fnd_currencies_vl fcv where fcv.currency_code = cba.currency_code and ( ((cba.ap_use_allowed_flag = 'Y' or cba.ar_use_allowed_flag ='Y') and exists (select null from ce_bank_acct_uses_all cbaua,ce_security_profiles_v cspv where cbaua.bank_account_id = cba.bank_account_id and cbaua.org_id = cspv.organization_id and cspv.organization_type = 'OPERATING_UNIT' and trunc(nvl(cbaua.end_date,sysdate)) >= trunc(sysdate)) ) or (cba.xtr_use_allowed_flag ='Y' and exists (select null from ce_bank_acct_uses_all cbaua,ce_security_profiles_v cspv where cbaua.bank_account_id = cba.bank_account_id and cbaua.org_id = cspv.organization_id and cspv.organization_type = 'LEGAL_ENTITY' and trunc(nvl(cbaua.end_date,sysdate)) >= trunc(sysdate)) ) or (cba.pay_use_allowed_flag ='Y' and exists (select null from ce_bank_acct_uses_all cbaua,ce_security_profiles_v cspv where cbaua.bank_account_id = cba.bank_account_id and cbaua.org_id = cspv.organization_id and cspv.organization_type = 'BUSINESS_GROUP' and trunc(nvl(cbaua.end_date,sysdate)) >= trunc(sysdate)) ) ) order by cba.currency_code F Devise du relevé US Statement Currency 15 150 1=1 ( :p_sl_status='ALL' or :p_sl_status='RECONCILED' and csl.status in ('RECONCILED','EXTERNAL') or :p_sl_status='UNRECONCILED' and decode(csl.status,'EXTERNAL','N',decode(sign(csl.amount-nvl(decode(csl.trx_type,'NSF',nsf_recon.amount_matched,oth_recon.amount_matched),0)),1,'Y','N'))='Y' ) LOV Oracle CE_CEXSTMRR_SL_STATUS Y select lookup_code id, meaning value, description description from ce_lookups where lookup_type='REPORT_SL_STATUS' order by lookup_code ALL Y F Statut de la ligne de déclaration US Statement Line Status 16 160 1=1 csh.statement_complete_flag=:p_statement_complete_flag LOV Oracle Yes_No Y select lookup_code id, meaning value, null description from fnd_lookups where fnd_lookups.lookup_type='YES_NO' order by value,description F Déclaration complète US Statement Complete 17 170 1=1 csl.trx_date>=:p_trx_date_from Date F Trx Date de US Trx Date From 18 180 1=1 csl.trx_date<:p_trx_date_to+1 Date :$flex$.Trx_Date_From F Trx Date à US Trx Date To 19 190 1=1 (lower(csl.customer_text) like lower(:p_agent) || '%' or exists (select null from ce_reconciled_transactions_v crtv where crtv.statement_line_id = csl.statement_line_id and lower(crtv.agent_name) like lower(:p_agent) || '%' ) ) Char F Nom du payeur/du bénéficiaire comme US Payer/Payee Name Like 20 200 1=1 xxen_util.meaning(csl.trx_type,'BANK_TRX_TYPE',260)=:p_trx_type LOV CE Bank Transaction Types C9B298A27BC27D53E0530100007F6F5A Y select cl.meaning value, cl.description description from ce_lookups cl where cl.lookup_type='BANK_TRX_TYPE' order by cl.meaning F Type de Trx US Trx Type 21 210 1=1 csl.bank_trx_number=:p_trx_number Char F Numéro Trx US Trx Number 22 220 1=1 csl.invoice_text=:p_invoice_number Char F Numéro de facture US Invoice Number 23 230 1=1 (csl.amount=:p_trx_amount or csl.original_amount=:p_trx_amount ) Number F Montant du Trx US Trx Amount 24 240 1=1 csl.currency_code=:p_trx_currency LOV FND Currency 8E2FF36EDE9079D2E0530100007F1FF2 Y select fcv.currency_code value, fcv.description from fnd_currencies_vl fcv where fcv.enabled_flag='Y' order by fcv.currency_code F Monnaie Trx US Trx Currency 25 250 :p_show_trx LOV Yes 8E2FF36EDEA679D2E0530100007F1FF2 Y select 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from dual F Afficher les transactions réconciliées US Display Reconciled Transactions 26 260 3=3 (crt.recon_trx_cleared_date>=:p_cleared_date_from or :p_show_trx is null) Date US Transaction Cleared Date From 27 270 3=3 (crt.recon_trx_cleared_date<:p_cleared_date_to+1 or :p_show_trx is null) Date :$flex$.Transaction_Cleared_Date_From US Transaction Cleared Date To :$flex$.Bank_name Bank Name Bank Branch :$flex$.Document_Number_From Document Number From Document Number To :$flex$.Statement_Date_From Statement Date From Y Statement Date To :$flex$.Statement_Number_From Statement Number From Statement Number To :$flex$.Transaction_Cleared_Date_From Transaction Cleared Date From Y Transaction Cleared Date To :$flex$.Trx_Date_From Trx Date From Y Trx Date To :$flex$.bank_account_name Bank Account Name Document Number From :$flex$.bank_account_name Bank Account Name Document Number To :$flex$.bank_account_name Bank Account Name Statement Number From :$flex$.bank_account_name Bank Account Name Statement Number To :$flex$.bank_account_number Bank Account Number Document Number From :$flex$.bank_account_number Bank Account Number Document Number To :$flex$.bank_account_number Bank Account Number Statement Number From :$flex$.bank_account_number Bank Account Number Statement Number To :$flex$.bank_branch Bank Branch Bank Account Name :$flex$.bank_branch Bank Branch Bank Account Number :$flex$.bank_name Bank Name Bank Account Name :$flex$.bank_name Bank Name Bank Account Number :$flex$.check_digits Check Digits Document Number From :$flex$.check_digits Check Digits Document Number To :$flex$.check_digits Check Digits Statement Number From :$flex$.check_digits Check Digits Statement Number To :$flex$.statement_gl_date_from Statement GL Date From Y Statement GL Date To Bank Statement Detail Y Bank Statement Detail SYSADMIN 55 AGENT 56 AGENT_BANK_ACCOUNT 45 AMOUNT 27 AUTO_LOADED_FLAG 5 BANK_ACCOUNT_CURRENCY 3 BANK_ACCOUNT_NAME 2 BANK_ACCOUNT_NUM 2 4 BANK_ACCOUNT_TYPE 6 BANK_NAME 1 7 BRANCH_NAME 23 CASHFLOW_BALANCE 44 CHARGES_AMOUNT 12 CHECK_DIGITS 32 CODE 21 CONTROL_AMOUNT_ERROR 15 CONTROL_CLOSING_BALANCE 22 CONTROL_COUNT_ERROR 18 CONTROL_LINES_COUNT 14 CONTROL_NET_MOVEMENT 13 CONTROL_OPENING_BALANCE 17 CONTROL_PAYMENTS 20 CONTROL_PAYMENTS_COUNT 16 CONTROL_RECEIPTS 19 CONTROL_RECEIPTS_COUNT 58 DESCRIPTION 9 DOCUMENT_NUMBER 59 ERROR_MESSAGES 47 EXCHANGE_DATE 49 EXCHANGE_RATE 48 EXCHANGE_RATE_TYPE 64 GL_ACCOUNT_CODE 65 GL_ACCOUNT_DESC 68 GL_CASH_ACCOUNT 69 GL_CASH_ACCOUNT_DESC 62 GL_COMPANY_CODE 63 GL_COMPANY_DESC 66 GL_COST_CENTER_CODE 67 GL_COST_CENTER_DESC 11 GL_DATE 57 INVOICE -55.0056 LAST_UPDATED_BY -55.0057 LAST_UPDATED_DATE 1 LEGAL_ENTITY 60 LINE_LAST_UPDATED_BY 61 LINE_LAST_UPDATED_DATE 30 LINE_NUM 5 50 LINE_ORIGINAL_AMOUNT 29 MULTI_CURRENCY_FLAG 39 NET_AMOUNT 42 NET_OVERRECONCILED_AMOUNT 40 NET_RECONCILED_AMOUNT 53 NET_TRX_ORIGINAL_AMOUNT 54 NET_TRX_ORIGINAL_AMOUNT_ACCTD 41 NET_UNRECONCILED_AMOUNT 25 ONE_DAY_FLOAT 38 PAYMENT_AMOUNT 37 RECEIPT_AMOUNT 43 RECONCILED_TRX_COUNT 70 RECON_SORT_NUM 6 28 STATEMENT_COMPLETE_FLAG 10 STATEMENT_DATE 3 8 STATEMENT_NUM 4 36 STATUS 46 TRX_CURRENCY 34 TRX_DATE 33 TRX_NUMBER 51 TRX_ORIGINAL_AMOUNT 52 TRX_ORIGINAL_AMOUNT_ACCTD 31 TRX_TYPE 26 TWO_DAY_FLOAT 35 VALUE_DATE 24 VALUE_DATED_BALANCE S Site Display Reconciled Transactions Transaction Cleared Date From Transaction Cleared Date To Bank Statement Detail with Reconciled Trxs Bank Statement Detail including details of each Reconciled Transaction SYSADMIN 55 AGENT 56 AGENT_BANK_ACCOUNT 45 AMOUNT 27 AUTO_LOADED_FLAG 5 BANK_ACCOUNT_CURRENCY 3 BANK_ACCOUNT_NAME 2 BANK_ACCOUNT_NUM 2 4 BANK_ACCOUNT_TYPE 6 BANK_NAME 1 7 BRANCH_NAME 23 CASHFLOW_BALANCE 44 CHARGES_AMOUNT 12 CHECK_DIGITS 32 CODE 21 CONTROL_AMOUNT_ERROR 15 CONTROL_CLOSING_BALANCE 22 CONTROL_COUNT_ERROR 18 CONTROL_LINES_COUNT 14 CONTROL_NET_MOVEMENT 13 CONTROL_OPENING_BALANCE 17 CONTROL_PAYMENTS 20 CONTROL_PAYMENTS_COUNT 16 CONTROL_RECEIPTS 19 CONTROL_RECEIPTS_COUNT 82 DESCRIPTION 9 DOCUMENT_NUMBER 83 ERROR_MESSAGES 47 EXCHANGE_DATE 49 EXCHANGE_RATE 48 EXCHANGE_RATE_TYPE 88 GL_ACCOUNT_CODE 89 GL_ACCOUNT_DESC 92 GL_CASH_ACCOUNT 93 GL_CASH_ACCOUNT_DESC 86 GL_COMPANY_CODE 87 GL_COMPANY_DESC 90 GL_COST_CENTER_CODE 91 GL_COST_CENTER_DESC 11 GL_DATE 57 INVOICE -55.0056 LAST_UPDATED_BY -55.0057 LAST_UPDATED_DATE 1 LEGAL_ENTITY 84 LINE_LAST_UPDATED_BY 85 LINE_LAST_UPDATED_DATE 30 LINE_NUM 5 50 LINE_ORIGINAL_AMOUNT 29 MULTI_CURRENCY_FLAG 39 NET_AMOUNT 42 NET_OVERRECONCILED_AMOUNT 40 NET_RECONCILED_AMOUNT 53 NET_TRX_ORIGINAL_AMOUNT 54 NET_TRX_ORIGINAL_AMOUNT_ACCTD 41 NET_UNRECONCILED_AMOUNT 25 ONE_DAY_FLOAT 38 PAYMENT_AMOUNT 37 RECEIPT_AMOUNT 43 RECONCILED_TRX_COUNT 62 RECON_BANK_ACCOUNT_AMOUNT 94 RECON_SORT_NUM 6 77 RECON_TRX_AGENT_NAME 61 RECON_TRX_AMOUNT 63 RECON_TRX_AMOUNT_CLEARED 72 RECON_TRX_BANK_CHARGES 73 RECON_TRX_BANK_ERRORS 75 RECON_TRX_BATCH_NAME 64 RECON_TRX_CLEARED_DATE 60 RECON_TRX_CURRENCY 68 RECON_TRX_DATE 71 RECON_TRX_EXCHANGE_RATE 69 RECON_TRX_EXCHANGE_RATE_DATE 70 RECON_TRX_EXCHANGE_RATE_TYPE 67 RECON_TRX_GL_DATE 65 RECON_TRX_MATURITY_DATE 59 RECON_TRX_NUMBER 80 RECON_TRX_OPERATING_UNIT 76 RECON_TRX_PSON 79 RECON_TRX_REFERENCE_NUMBER 78 RECON_TRX_REFERENCE_TYPE 74 RECON_TRX_REMITTANCE_NUMBER 81 RECON_TRX_STATUS 58 RECON_TRX_TYPE 66 RECON_TRX_VALUE_DATE 28 STATEMENT_COMPLETE_FLAG 10 STATEMENT_DATE 3 8 STATEMENT_NUM 4 36 STATUS 46 TRX_CURRENCY 34 TRX_DATE 33 TRX_NUMBER 51 TRX_ORIGINAL_AMOUNT 52 TRX_ORIGINAL_AMOUNT_ACCTD 31 TRX_TYPE 26 TWO_DAY_FLOAT 35 VALUE_DATE 24 VALUE_DATED_BALANCE Display Reconciled Transactions Y S Site Bank Statement Summary Bank Statement Summary SYSADMIN 38 AUTO_LOADED_FLAG 5 BANK_ACCOUNT_CURRENCY 3 BANK_ACCOUNT_NAME 2 BANK_ACCOUNT_NUM 2 4 BANK_ACCOUNT_TYPE 6 BANK_NAME 1 7 BRANCH_NAME 34 CASHFLOW_BALANCE 33 CHARGES_AMOUNT SUM 12 CHECK_DIGITS 21 CONTROL_AMOUNT_ERROR 15 CONTROL_CLOSING_BALANCE 22 CONTROL_COUNT_ERROR 18 CONTROL_LINES_COUNT 14 CONTROL_NET_MOVEMENT 13 CONTROL_OPENING_BALANCE 17 CONTROL_PAYMENTS 20 CONTROL_PAYMENTS_COUNT 16 CONTROL_RECEIPTS 19 CONTROL_RECEIPTS_COUNT 9 DOCUMENT_NUMBER 43 GL_ACCOUNT_CODE 44 GL_ACCOUNT_DESC 47 GL_CASH_ACCOUNT 48 GL_CASH_ACCOUNT_DESC 41 GL_COMPANY_CODE 42 GL_COMPANY_DESC 45 GL_COST_CENTER_CODE 46 GL_COST_CENTER_DESC 11 GL_DATE 1 LEGAL_ENTITY 29 LINE_COUNT SUM 40 MULTI_CURRENCY_FLAG 25 NET_AMOUNT SUM 28 NET_OVERRECONCILED_AMOUNT SUM 26 NET_RECONCILED_AMOUNT SUM 27 NET_UNRECONCILED_AMOUNT SUM 36 ONE_DAY_FLOAT 24 PAYMENT_AMOUNT SUM 31 PAYMENT_LINE_COUNT SUM 23 RECEIPT_AMOUNT SUM 30 RECEIPT_LINE_COUNT SUM 39 STATEMENT_COMPLETE_FLAG 10 STATEMENT_DATE 3 8 STATEMENT_NUM 4 37 TWO_DAY_FLOAT 32 UNRECONCILED_LINE_COUNT SUM 35 VALUE_DATED_BALANCE S Site Display Reconciled Transactions Transaction Cleared Date From Transaction Cleared Date To Pivot: Bank Statements by Transaction Type Y Pivot: Bank Statements by Transaction Type with Drilldown to Bank Statement Lines SYSADMIN 55 AGENT 56 AGENT_BANK_ACCOUNT 45 AMOUNT 27 AUTO_LOADED_FLAG 5 BANK_ACCOUNT_CURRENCY 3 BANK_ACCOUNT_NAME 2 BANK_ACCOUNT_NUM 2 70 BANK_ACCOUNT_PIVOT_LABEL 4 BANK_ACCOUNT_TYPE 6 BANK_NAME 1 7 BRANCH_NAME 23 CASHFLOW_BALANCE 44 CHARGES_AMOUNT 12 CHECK_DIGITS 32 CODE 21 CONTROL_AMOUNT_ERROR 15 CONTROL_CLOSING_BALANCE 22 CONTROL_COUNT_ERROR 18 CONTROL_LINES_COUNT 14 CONTROL_NET_MOVEMENT 13 CONTROL_OPENING_BALANCE 17 CONTROL_PAYMENTS 20 CONTROL_PAYMENTS_COUNT 16 CONTROL_RECEIPTS 19 CONTROL_RECEIPTS_COUNT 58 DESCRIPTION 9 DOCUMENT_NUMBER 59 ERROR_MESSAGES 47 EXCHANGE_DATE 49 EXCHANGE_RATE 48 EXCHANGE_RATE_TYPE 64 GL_ACCOUNT_CODE 65 GL_ACCOUNT_DESC 68 GL_CASH_ACCOUNT 69 GL_CASH_ACCOUNT_DESC 62 GL_COMPANY_CODE 63 GL_COMPANY_DESC 66 GL_COST_CENTER_CODE 67 GL_COST_CENTER_DESC 11 GL_DATE 57 INVOICE -55.0056 LAST_UPDATED_BY -55.0057 LAST_UPDATED_DATE 1 LEGAL_ENTITY 72 LINE_COUNT 60 LINE_LAST_UPDATED_BY 61 LINE_LAST_UPDATED_DATE 30 LINE_NUM 50 LINE_ORIGINAL_AMOUNT 29 MULTI_CURRENCY_FLAG 39 NET_AMOUNT 42 NET_OVERRECONCILED_AMOUNT 40 NET_RECONCILED_AMOUNT 53 NET_TRX_ORIGINAL_AMOUNT 54 NET_TRX_ORIGINAL_AMOUNT_ACCTD 41 NET_UNRECONCILED_AMOUNT 25 ONE_DAY_FLOAT 38 PAYMENT_AMOUNT 74 PAYMENT_LINE_COUNT 37 RECEIPT_AMOUNT 73 RECEIPT_LINE_COUNT 43 RECONCILED_TRX_COUNT 28 STATEMENT_COMPLETE_FLAG 10 STATEMENT_DATE 3 8 STATEMENT_NUM 4 71 STATEMENT_NUM_PIVOT_LABEL 36 STATUS 46 TRX_CURRENCY 34 TRX_DATE 33 TRX_NUMBER 51 TRX_ORIGINAL_AMOUNT 52 TRX_ORIGINAL_AMOUNT_ACCTD 31 TRX_TYPE 26 TWO_DAY_FLOAT 75 UNRECONCILED_LINE_COUNT 35 VALUE_DATE 24 VALUE_DATED_BALANCE BANK_ACCOUNT_CURRENCY FILTER 7 BANK_ACCOUNT_NAME FILTER 6 BANK_ACCOUNT_NUM FILTER 5 BANK_ACCOUNT_PIVOT_LABEL ROW 1 BANK_NAME FILTER 3 BRANCH_NAME FILTER 4 CHARGES_AMOUNT VALUE 12 SUM CONTROL_CLOSING_BALANCE VALUE 3 MAX CONTROL_NET_MOVEMENT VALUE 2 MAX CONTROL_OPENING_BALANCE VALUE 1 MAX GL_COMPANY_DESC FILTER 2 LEGAL_ENTITY FILTER 1 LINE_COUNT VALUE 8 SUM NET_AMOUNT VALUE 4 SUM NET_OVERRECONCILED_AMOUNT VALUE 7 SUM NET_RECONCILED_AMOUNT VALUE 5 SUM NET_UNRECONCILED_AMOUNT VALUE 6 SUM PAYMENT_LINE_COUNT VALUE 10 SUM RECEIPT_LINE_COUNT VALUE 9 SUM STATEMENT_NUM_PIVOT_LABEL ROW 2 TRX_TYPE ROW 3 UNRECONCILED_LINE_COUNT VALUE 11 SUM S Site Display Reconciled Transactions Transaction Cleared Date From Transaction Cleared Date To ANONYMOUS Bank Statement Detail with Reconciled Trxs SYSADMIN Bank Statement Detail ENGINATICS Bank Statement Detail ALEXANDER.PAVLIK Bank Statement Detail with Reconciled Trxs Pivot: Bank Statements by Transaction Type