ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 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 C9C878DC04AA0259E0530100007FB368 CE Legal Entities Y select distinct xep.name value, null description from xle_entity_profiles xep, ce_bank_accounts cba, ce_bank_acct_uses_all cbaua, ce_security_profiles_v cspv where xep.legal_entity_id = cba.account_owner_org_id and cba.bank_account_id = cbaua.bank_account_id and ((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) US CE Legal Entities CB08ABBA2FB6E927E0530100007FD659 with q_gl_end_bal as ( select 'GL END BALANCE' record_type, null operating_unit, null transaction_number, to_date(null) gl_date, to_date(null) effective_date, null agent, null payment_method, null transaction_subtype, null je_name, to_number(null) je_line, null je_line_type, to_date(null) je_posted_date, null statement_number, to_number(null) statement_line, null statement_trx_type, null status, :c_bank_curr_dsp currency, to_number(null) transaction_amount, to_number(null) accounted_amount, to_number (nvl(bal.period_net_dr,0) - nvl(bal.period_net_cr,0) + nvl(bal.begin_balance_dr,0) - nvl(bal.begin_balance_cr,0)) gl_end_balance, glcc.code_combination_id cash_account_ccid from gl_balances bal, gl_code_combinations glcc where bal.actual_flag(+) = 'A' and bal.ledger_id(+) = :c_set_of_books_id and bal.currency_code(+) = :c_bank_curr_dsp and nvl(bal.translated_flag,'R') = 'R' and bal.period_name(+) = :p_period_name and bal.code_combination_id(+) = glcc.code_combination_id and glcc.chart_of_accounts_id = :c_chart_of_accounts_id and glcc.template_id is null and glcc.code_combination_id in ( select distinct case col.cnum when 1 then cba2.asset_code_combination_id when 2 then cgac2.asset_code_combination_id when 3 then cgac2.ap_asset_ccid when 4 then cgac2.ar_asset_ccid when 5 then cgac2.xtr_asset_ccid end from ce_bank_accounts cba2, ce_bank_acct_uses_all cbaua2, ce_gl_accounts_ccid cgac2, ( select 1 as cnum from dual union all select 2 as cnum from dual union all select 3 as cnum from dual union all select 4 as cnum from dual union all select 5 as cnum from dual ) col where cba2.bank_account_id = :p_bank_account_id and cbaua2.bank_account_id = cba2.bank_account_id and cgac2.bank_acct_use_id = cbaua2.bank_acct_use_id ) ), q_stmt_end_bal as ( select 'STMT END BALANCE' record_type, null operating_unit, null transaction_number, to_date(null) gl_date, to_date(null) effective_date, null agent, null payment_method, null transaction_subtype, null je_name, to_number(null) je_line, null je_line_type, to_date(null) je_posted_date, null statement_number, to_number(null) statement_line, null statement_trx_type, null status, :c_bank_curr_dsp currency, to_number(null) transaction_amount, to_number(:p_closing_balance) accounted_amount, to_number(null) gl_end_balance, to_number(:c_asset_cc_id) cash_account_ccid from dual ), q_ar_receipts as ( /* query 1: fetch latest receipt history event which has hit gl cash account and is not reconciled query 2: fetch unreconciled cleared event for a reversed receipt */ select 'RECEIPT' record_type, fnd_access_control_util.get_org_name(cr.org_id) operating_unit, cr.receipt_number transaction_number, crh.gl_date gl_date, cr.receipt_date effective_date, hz.party_name agent, arm.name payment_method, null transaction_subtype, null je_name, to_number(null) je_line, null je_line_type, to_date(null) je_posted_date, null statement_number, to_number(null) statement_line, null statement_trx_type, crh.status status, cr.currency_code currency, cr.amount transaction_amount, decode(:c_bank_curr_dsp, :c_gl_currency_code, decode(crh.status,'REVERSED',-crh.acctd_amount,crh.acctd_amount), decode(crh.status,'REVERSED',-crh.amount,crh.amount) ) accounted_amount, to_number(null) gl_end_balance, gla.ar_asset_ccid cash_account_ccid from ar_cash_receipts cr, ar_cash_receipt_history crh, hz_cust_accounts cu, hz_parties hz, ar_receipt_methods arm, ce_bank_acct_uses_ou_v bau, ce_bank_accounts ba, ce_gl_accounts_ccid gla, ce_system_parameters sys where cr.cash_receipt_id = crh.cash_receipt_id and cr.remit_bank_acct_use_id = bau.bank_acct_use_id and bau.bank_account_id = :p_bank_account_id and bau.org_id = cr.org_id and bau.bank_account_id = ba.bank_account_id and ba.account_owner_org_id = sys.legal_entity_id and bau.ar_use_enable_flag = 'Y' and gla.bank_acct_use_id = bau.bank_acct_use_id and crh.account_code_combination_id = gla.ar_asset_ccid and crh.status in('REMITTED', 'CLEARED', 'REVERSED') and crh.gl_date <= :c_as_of_date and crh.gl_date >= sys.cashbook_begin_date and decode(crh.status,'REMITTED',nvl(crh.reversal_created_from,'X'),crh.created_from) <> 'RATE ADJUSTMENT TRIGGER' and not ( crh.status = 'REMITTED' and crh.created_from = 'RATE ADJUSTMENT TRIGGER' and crh.reversal_created_from is null and crh.amount = - 1 * cr.amount ) and not ( crh.status ='CLEARED' and nvl(crh.reversal_created_from,'x') ='RATE ADJUSTMENT TRIGGER' ) and crh.posting_control_id > 0 and not exists ( select 1 from ar_cash_receipt_history_all crh_r where crh_r.cash_receipt_history_id = crh.reversal_cash_receipt_hist_id and crh_r.gl_date <= :c_as_of_date and crh_r.posting_control_id > 0 and crh_r.created_from <> 'RATE ADJUSTMENT TRIGGER' ) and cu.cust_account_id(+) = cr.pay_from_customer and hz.party_id(+) = cu.party_id and arm.receipt_method_id = cr.receipt_method_id and cr.status <> 'REV' and not exists ( select null from ce_statement_recon_gt_v sr, ce_statement_lines sl, ce_statement_headers sh where sr.reference_id = crh.cash_receipt_history_id and sr.reference_type = 'RECEIPT' and sr.status_flag = 'M' and sr.current_record_flag = 'Y' and sl.statement_line_id = sr.statement_line_id and sl.statement_header_id = sh.statement_header_id and sh.bank_account_id = :p_bank_account_id and sh.statement_date <= :c_as_of_date union /* this union is required for receipts with rate adjustment that are reconciled. for such cases, the reference will be of the rate adjustment record */ select null from ce_statement_recon_gt_v sr, ce_statement_lines sl, ce_statement_headers sh, ar_cash_receipt_history crh_rc where sr.reference_id = crh_rc.cash_receipt_history_id and sr.reference_type = 'RECEIPT' and sr.status_flag = 'M' and sr.current_record_flag = 'Y' and sl.statement_line_id = sr.statement_line_id and sl.statement_header_id = sh.statement_header_id and sh.bank_account_id = :p_bank_account_id and sh.statement_date <= :c_as_of_date and crh_rc.created_from = 'RATE ADJUSTMENT TRIGGER' and crh_rc.cash_receipt_id = cr.cash_receipt_id ) and /* not exists to filter out receipts reversed without remittance */ not exists ( select 1 from ar_cash_receipt_history_all crh2 where crh.cash_receipt_history_id = crh2.reversal_cash_receipt_hist_id and crh.status = 'REVERSED' and crh2.status = 'CONFIRMED' ) union all select 'RECEIPT' record_type, fnd_access_control_util.get_org_name(cr.org_id) operating_unit, cr.receipt_number transaction_number, crh.gl_date gl_date, cr.receipt_date effective_date, hz.party_name agent, arm.name payment_method, null transaction_subtype, null je_name, to_number(null) je_line, null je_line_type, to_date(null) je_posted_date, null statement_number, to_number(null) statement_line, null statement_trx_type, crh.status status, cr.currency_code currency, cr.amount transaction_amount, decode(:c_bank_curr_dsp,:c_gl_currency_code,crh.acctd_amount,crh.amount) accounted_amount, to_number(null) gl_end_balance, gla.ar_asset_ccid cash_account_ccid from ar_cash_receipts cr, ar_cash_receipt_history crh2, ar_cash_receipt_history crh, hz_cust_accounts cu, hz_parties hz, ar_receipt_methods arm, ce_bank_acct_uses_ou_v bau, ce_gl_accounts_ccid gla, ce_bank_accounts ba, ce_system_parameters sys where cr.cash_receipt_id = crh.cash_receipt_id and cr.remit_bank_acct_use_id = bau.bank_acct_use_id and bau.bank_account_id = :p_bank_account_id and bau.org_id = cr.org_id and bau.bank_account_id = ba.bank_account_id and ba.account_owner_org_id = sys.legal_entity_id and bau.ar_use_enable_flag = 'Y' and gla.bank_acct_use_id = bau.bank_acct_use_id and crh.account_code_combination_id = gla.ar_asset_ccid and crh.status in('REMITTED', 'CLEARED') and crh.gl_date <= :c_as_of_date and crh.gl_date >= sys.cashbook_begin_date and crh.gl_posted_date is not null and crh.created_from <> 'RATE ADJUSTMENT TRIGGER' and crh2.cash_receipt_id = crh.cash_receipt_id and crh2.cash_receipt_history_id = crh.reversal_cash_receipt_hist_id and crh2.status = 'REVERSED' and crh2.gl_date <= :c_as_of_date and crh2.gl_date >= sys.cashbook_begin_date and cu.cust_account_id(+) = cr.pay_from_customer and hz.party_id(+) = cu.party_id and arm.receipt_method_id = cr.receipt_method_id and (cr.status <> 'REV' or (cr.status = 'REV' and crh.reversal_gl_date > :c_as_of_date) ) and not exists ( select null from ce_statement_recon_gt_v sr, ce_statement_lines sl, ce_statement_headers sh where sr.reference_id = crh.cash_receipt_history_id and sr.reference_type = 'RECEIPT' and sr.status_flag = 'M' and sr.current_record_flag = 'Y' and sl.statement_line_id = sr.statement_line_id and sl.statement_header_id = sh.statement_header_id and sh.bank_account_id = :p_bank_account_id and sh.statement_date <= :c_as_of_date ) ), q_ap_payments as ( /* query 1: checks have not hit gl cash, but are reconciled. (query 1 of union) query 2: checks have hit gl cash, but are not reconciled. (query 2 of union) */ select 'PAYMENT' record_type, fnd_access_control_util.get_org_name(c.org_id) operating_unit, to_char(c.check_number) transaction_number, xle.event_date gl_date, c.check_date effective_date, c.vendor_name agent, c.payment_method_code payment_method, null transaction_subtype, null je_name, to_number(null) je_line, null je_line_type, to_date(null) je_posted_date, null statement_number, to_number(null) statement_line, null statement_trx_type, c.status_lookup_code status, c.currency_code currency, c.amount transaction_amount, decode(:c_bank_curr_dsp, :c_gl_currency_code,nvl(nvl(c.cleared_base_amount,c.base_amount),c.amount) ,c.amount ) accounted_amount, to_number(null) gl_end_balance, coalesce(gla.ap_asset_ccid,gla.asset_code_combination_id,ba.asset_code_combination_id) cash_account_ccid from ap_checks_all c, ce_bank_acct_uses_all bau, ce_gl_accounts_ccid gla, ce_security_profiles_gt ou, ce_bank_accounts ba, ce_system_parameters sys, xla_transaction_entities trx, xla_events xle where c.check_date <= trunc(:c_as_of_date) + 1 - 1/24/60/60 and c.ce_bank_acct_use_id = bau.bank_acct_use_id and c.org_id = bau.org_id and bau.bank_account_id = :p_bank_account_id and gla.bank_acct_use_id = bau.bank_acct_use_id and bau.org_id = ou.organization_id and ou.organization_type = 'OPERATING_UNIT' and bau.bank_account_id = ba.bank_account_id and ba.account_owner_org_id = sys.legal_entity_id and trx.application_id = 200 and trx.ledger_id = sys.set_of_books_id and nvl(trx.source_id_int_1,-99) = c.check_id and trx.entity_code = 'AP_PAYMENTS' and xle.application_id = trx.application_id and xle.entity_id = trx.entity_id and xle.event_type_code not in ('PAYMENT CANCELLED','REFUND CANCELLED') and xle.event_number = ( select max(event_number) from xla_events xe2 where xe2.application_id = xle.application_id and xe2.entity_id = xle.entity_id and xe2.event_date <= :c_as_of_date and xe2.event_date >= sys.cashbook_begin_date and xe2.event_status_code = 'P' and xe2.event_type_code not in ('PAYMENT MATURITY ADJUSTED', 'MANUAL PAYMENT ADJUSTED', 'PAYMENT ADJUSTED', 'PAYMENT CLEARING ADJUSTED', 'MANUAL REFUND ADJUSTED', 'REFUND ADJUSTED' ) ) and exists ( select /*+ push_subq no_unnest*/ 1 from ap_payment_history_all h2 where h2.check_id = c.check_id and h2.transaction_type like decode(c.void_date, null, h2.transaction_type,'%CANCEL%') ) and not exists ( select null from xla_ae_headers xeh, xla_ae_lines ael1 where xeh.event_id = xle.event_id and xeh.application_id = xle.application_id and ael1.application_id = xeh.application_id and xeh.entity_id = xle.entity_id and ael1.ae_header_id = xeh.ae_header_id and xeh.event_type_code not in ('PAYMENT UNCLEARED', 'PAYMENT CANCELLED', 'REFUND CANCELLED' ) and ael1.accounting_class_code = 'CASH' ) and /* check that payment is reconciled */ exists ( select null from ce_statement_reconcils_all csr, ce_statement_lines csl, ce_statement_headers csh where csr.reference_id = c.check_id and csr.current_record_flag = 'Y' and csr.reference_type = 'PAYMENT' and csr.status_flag = 'M' and csr.statement_line_id = csl.statement_line_id and csl.statement_header_id = csh.statement_header_id and csh.statement_date <= :c_as_of_date and csh.statement_date >= sys.cashbook_begin_date ) union all select 'PAYMENT' record_type, fnd_access_control_util.get_org_name(c.org_id) operating_unit, to_char(c.check_number) transaction_number, aeh.accounting_date gl_date, c.check_date effective_date, c.vendor_name agent, c.payment_method_code payment_method, null transaction_subtype, null je_name, to_number(null) je_line, null je_line_type, to_date(null) je_posted_date, null statement_number, to_number(null) statement_line, null statement_trx_type, c.status_lookup_code status, c.currency_code currency, c.amount transaction_amount, -1*decode(:c_bank_curr_dsp, :c_gl_currency_code,nvl(nvl(c.cleared_base_amount,c.base_amount),c.amount) ,c.amount ) accounted_amount, to_number(null) gl_end_balance, coalesce(gla.ap_asset_ccid,gla.asset_code_combination_id,ba.asset_code_combination_id) cash_account_ccid from ap_checks_all c, ce_bank_acct_uses_all bau, ce_gl_accounts_ccid gla, ce_security_profiles_gt ou, ce_bank_accounts ba, ce_system_parameters sys, xla_transaction_entities trx, xla_ae_headers aeh where c.check_date <= trunc(:c_as_of_date) + 1 - 1/24/60/60 and c.ce_bank_acct_use_id = bau.bank_acct_use_id and c.org_id = bau.org_id and bau.bank_account_id = :p_bank_account_id and gla.bank_acct_use_id = bau.bank_acct_use_id and bau.org_id = ou.organization_id and ou.organization_type = 'OPERATING_UNIT' and bau.bank_account_id = ba.bank_account_id and ba.account_owner_org_id = sys.legal_entity_id and nvl(trx.source_id_int_1,-99) = c.check_id and trx.entity_code = 'AP_PAYMENTS' and trx.application_id = 200 and trx.ledger_id = sys.set_of_books_id and aeh.entity_id = trx.entity_id and aeh.application_id = trx.application_id and aeh.application_id = 200 and /* fetch latest accounted event before as_of_date and check that it hits the gl cash account */ aeh.event_type_code in ('PAYMENT CLEARED', 'PAYMENT CREATED', 'REFUND RECORDED', 'PAYMENT MATURED' ) and aeh.ledger_id = sys.set_of_books_id and aeh.event_id = ( select max(event_id) from xla_events xe where xe.application_id = 200 and xe.entity_id = trx.entity_id and xe.event_number = ( select max(event_number) from xla_events xe2 where xe2.application_id = 200 and xe2.entity_id = xe.entity_id and xe2.event_date <= :c_as_of_date and xe2.event_date >= sys.cashbook_begin_date and xe2.event_status_code = 'P' and xe2.event_type_code not in ('PAYMENT MATURITY ADJUSTED', 'MANUAL PAYMENT ADJUSTED', 'PAYMENT ADJUSTED', 'PAYMENT CLEARING ADJUSTED', 'MANUAL REFUND ADJUSTED', 'REFUND ADJUSTED' ) ) ) and exists ( select 'x' from xla_ae_lines ael where aeh.ae_header_id = ael.ae_header_id and ael.application_id = 200 and ael.accounting_class_code = 'CASH' ) and exists ( select 1 from ap_payment_history_all h2 where h2.check_id = c.check_id and h2.transaction_type like decode(c.void_date, null, h2.transaction_type, '%CANCEL%') ) and /* check that payment is not reconciled */ not exists ( select /*+ push_subq no_unnest */ null from ce_statement_reconcils_all csr, ce_statement_lines csl, ce_statement_headers csh where csr.reference_id = c.check_id and csr.reference_type = 'PAYMENT' and csr.status_flag = 'M' and csr.current_record_flag = 'Y' and csr.statement_line_id = csl.statement_line_id and csl.statement_header_id = csh.statement_header_id and csh.statement_date <= :c_as_of_date ) ), q_cashflows as ( select 'CASHFLOW' record_type, null operating_unit, to_char(ca.cashflow_id) transaction_number, ch.accounting_date gl_date, ca.cashflow_date effective_date, nvl(xle.name,ca.customer_text) agent, null payment_method, trxn.transaction_sub_type_name transaction_subtype, null je_name, to_number(null) je_line, null je_line_type, to_date(null) je_posted_date, null statement_number, to_number(null) statement_line, null statement_trx_type, l1.meaning status, ca.cashflow_currency_code currency, ca.cashflow_amount transaction_amount, decode(ca.cashflow_direction,'RECEIPT',(-1),'PAYMENT',(1)) * nvl(ch.cleared_amount,ca.cashflow_amount) accounted_amount, to_number(null) gl_end_balance, coalesce(gla.xtr_asset_ccid,gla.asset_code_combination_id,ba.asset_code_combination_id) cash_account_ccid from ce_cashflows ca, ce_cashflow_acct_h ch, ce_bank_accounts ba, ce_bank_acct_uses_all bau, ce_gl_accounts_ccid gla, ce_trxns_subtype_codes trxn, xle_firstparty_information_v xle, ce_lookups l1, ce_system_parameters sys where ca.cashflow_bank_account_id = :p_bank_account_id and ca.cashflow_legal_entity_id = sys.legal_entity_id and ca.cashflow_id = ch.cashflow_id and ba.bank_account_id = ca.cashflow_bank_account_id and bau.bank_account_id = ca.cashflow_bank_account_id and bau.legal_entity_id = ca.cashflow_legal_entity_id and gla.bank_acct_use_id = bau.bank_acct_use_id and ca.source_trxn_subtype_code_id = trxn.trxn_subtype_code_id(+) and ch.accounting_date >= sys.cashbook_begin_date and ca.counterparty_party_id = xle.party_id(+) and l1.lookup_type = 'CASHFLOW_STATUS_CODE' and l1.lookup_code = ca.cashflow_status_code and ca.cashflow_status_code = 'RECONCILED' and ch.event_id = ( select nvl(max(a.event_id),-1) from ce_cashflow_acct_h a where a.cashflow_id = ch.cashflow_id and trunc(a.accounting_date) <= :c_as_of_date ) and ch.event_type = decode(ca.source_trxn_type,'BAT','CE_BAT_CLEARED','STMT','CE_STMT_RECORDED') and ch.status_code = 'UNACCOUNTED' union select 'CASHFLOW' record_type, null operating_unit, to_char(ca.cashflow_id) transaction_number, ch.accounting_date gl_date, ca.cashflow_date effective_date, nvl(xle.name,ca.customer_text) agent, null payment_method, trxn.transaction_sub_type_name transaction_subtype, null je_name, to_number(null) je_line, null je_line_type, to_date(null) je_posted_date, null statement_number, to_number(null) statement_line, null statement_trx_type, l1.meaning status, ca.cashflow_currency_code currency, ca.cashflow_amount transaction_amount, decode(ca.cashflow_direction,'RECEIPT',(1),'PAYMENT',(-1)) * nvl(ch.cleared_amount,ca.cashflow_amount) accounted_amount, to_number(null) gl_end_balance, coalesce(gla.xtr_asset_ccid,gla.asset_code_combination_id,ba.asset_code_combination_id) cash_account_ccid from ce_cashflows ca, ce_cashflow_acct_h ch, ce_bank_accounts ba, ce_bank_acct_uses_all bau, ce_gl_accounts_ccid gla, xla_events e, xla_ae_headers eh, xla_ae_lines el, ce_trxns_subtype_codes trxn, xle_firstparty_information_v xle, ce_lookups l1, ce_system_parameters sys where ca.cashflow_bank_account_id = :p_bank_account_id and ca.source_trxn_subtype_code_id = trxn.trxn_subtype_code_id(+) and ca.cashflow_legal_entity_id = sys.legal_entity_id and ca.cashflow_id = ch.cashflow_id and ba.bank_account_id = ca.cashflow_bank_account_id and bau.bank_account_id = ca.cashflow_bank_account_id and bau.legal_entity_id = ca.cashflow_legal_entity_id and gla.bank_acct_use_id = bau.bank_acct_use_id and ch.event_id = e.event_id and eh.event_id = e.event_id and el.ae_header_id = eh.ae_header_id and el.accounting_class_code = 'CASH' and ch.accounting_date >= sys.cashbook_begin_date and ca.counterparty_party_id = xle.party_id(+) and l1.lookup_type = 'CASHFLOW_STATUS_CODE' and l1.lookup_code = ca.cashflow_status_code and ca.cashflow_status_code = 'CLEARED' and ch.event_id = ( select nvl(max(a.event_id),-1) from ce_cashflow_acct_h a where a.cashflow_id = ch.cashflow_id and trunc(a.accounting_date) <= :c_as_of_date ) and ch.event_type = decode(ca.source_trxn_type,'BAT','CE_BAT_CLEARED','STMT','CE_STMT_RECORDED') and ch.status_code = 'ACCOUNTED' union select 'CASHFLOW' record_type, null operating_unit, to_char(ca.cashflow_id) transaction_number, ch.accounting_date gl_date, ca.cashflow_date effective_date, nvl(xle.name,ca.customer_text) agent, null payment_method, trxn.transaction_sub_type_name transaction_subtype, null je_name, to_number(null) je_line, null je_line_type, to_date(null) je_posted_date, null statement_number, to_number(null) statement_line, null statement_trx_type, l1.meaning status, ca.cashflow_currency_code currency, ca.cashflow_amount transaction_amount, decode(ca.cashflow_direction,'RECEIPT',(-1),'PAYMENT',(1)) * nvl(ch.cleared_amount,ca.cashflow_amount) accounted_amount, to_number(null) gl_end_balance, coalesce(gla.xtr_asset_ccid,gla.asset_code_combination_id,ba.asset_code_combination_id) cash_account_ccid from ce_cashflows ca, ce_cashflow_acct_h ch, ce_bank_accounts ba, ce_bank_acct_uses_all bau, ce_gl_accounts_ccid gla, ce_trxns_subtype_codes trxn, xle_firstparty_information_v xle, ce_lookups l1, ce_system_parameters sys where ca.cashflow_bank_account_id = :p_bank_account_id and ca.source_trxn_subtype_code_id = trxn.trxn_subtype_code_id(+) and ca.cashflow_legal_entity_id = sys.legal_entity_id and ca.cashflow_id = ch.cashflow_id and ba.bank_account_id = ca.cashflow_bank_account_id and bau.bank_account_id = ca.cashflow_bank_account_id and bau.legal_entity_id = ca.cashflow_legal_entity_id and gla.bank_acct_use_id = bau.bank_acct_use_id and ch.accounting_date >= sys.cashbook_begin_date and ca.counterparty_party_id = xle.party_id(+) and l1.lookup_type = 'CASHFLOW_STATUS_CODE' and l1.lookup_code = ca.cashflow_status_code and ca.cashflow_status_code = 'CREATED' and ch.event_id = ( select nvl(max(a.event_id),-1) from ce_cashflow_acct_h a where a.cashflow_id = ch.cashflow_id and trunc(a.accounting_date) <= :c_as_of_date ) and ( (ch.event_type = 'CE_BAT_UNCLEARED' and ch.status_code = 'ACCOUNTED' ) or (ch.event_type = 'CE_BAT_CREATED' ) ) and exists ( select null from ce_statement_lines csl, ce_statement_headers csh, ce_transaction_codes_v cod where csl.trx_type in ('DEBIT','CREDIT','SWEEP_IN','SWEEP_OUT') and csl.trx_code = cod.trx_code and cod.bank_account_id = :p_bank_account_id and cod.reconcile_flag = 'CE' and csl.bank_trx_number = ca.bank_trxn_number and csh.statement_header_id = csl.statement_header_id and csh.bank_account_id = :p_bank_account_id ) ), q_gl_je_lines as ( select 'JE_LINE' record_type, null operating_unit, null transaction_number, jel.effective_date gl_date, jel.effective_date effective_date, null agent, null payment_method, null transaction_subtype, jeh.name je_name, jel.je_line_num je_line, cel.meaning je_line_type, jeh.posted_date je_posted_date, null statement_number, to_number(null) statement_line, null statement_trx_type, gll.meaning status, jeh.currency_code currency, decode(nvl(jel.entered_dr,0),0, jel.entered_cr, jel.entered_dr) transaction_amount, decode(:c_bank_curr_dsp, :c_gl_currency_code,decode(nvl(jel.accounted_dr,0),0, nvl(-1*jel.accounted_cr,0), nvl(jel.accounted_dr,0)), decode(nvl(jel.entered_dr,0) ,0, nvl(-1*jel.entered_cr,0) , nvl(jel.entered_dr,0)) ) accounted_amount, to_number(null) gl_end_balance, aba.asset_ccid cash_account_ccid from gl_je_lines jel, gl_je_headers jeh, ce_lookups cel, gl_lookups gll, gl_sets_of_books sob, ( select cba.bank_account_id, cba.currency_code, cba.asset_code_combination_id asset_ccid from ce_bank_accounts cba union select cba.bank_account_id, cba.currency_code, cgl.ar_asset_ccid asset_ccid from ce_bank_accounts cba, ce_bank_acct_uses_ou_v cbu, ce_gl_accounts_ccid cgl where cba.bank_account_id = cbu.bank_account_id and cbu.bank_acct_use_id = cgl.bank_acct_use_id and cgl.ar_asset_ccid is not null union select cba.bank_account_id, cba.currency_code, cgl.ap_asset_ccid asset_ccid from ce_bank_accounts cba, ce_bank_acct_uses_ou_v cbu, ce_gl_accounts_ccid cgl where cba.bank_account_id = cbu.bank_account_id and cbu.bank_acct_use_id = cgl.bank_acct_use_id and cgl.ap_asset_ccid is not null ) aba where jel.ledger_id = to_number(:c_set_of_books_id) and jel.code_combination_id = aba.asset_ccid and aba.bank_account_id = :p_bank_account_id and sob.set_of_books_id = to_number(:c_set_of_books_id) and decode(aba.currency_code,sob.currency_code, jeh.currency_code, aba.currency_code) = jeh.currency_code and jel.status = 'P' and jel.effective_date <= :c_as_of_date and jel.effective_date >= to_date(:c_cashbook_begin_date) and jeh.je_header_id = jel.je_header_id and jeh.je_source not in ('Payables', 'Receivables', 'AP Translator', 'AR Translator', 'Treasury', 'Cash Management', 'Consolidation', 'Payroll' ) and jeh.je_category <> 'Revaluation' and cel.lookup_type = 'TRX_TYPE' and cel.lookup_code = decode(decode(nvl(jel.entered_dr,0),0,nvl(jel.accounted_dr,0),jel.entered_dr), 0, 'JE_CREDIT' , 'JE_DEBIT' ) and (decode(nvl(jel.entered_dr,0), 0, nvl(jel.accounted_dr, 0), jel.entered_dr) = 0 or decode(nvl(jel.entered_cr,0), 0, nvl(jel.accounted_cr, 0), jel.entered_cr) = 0 ) and gll.lookup_type = 'MJE_BATCH_STATUS' and gll.lookup_code = jel.status and jeh.actual_flag = 'A' and not exists ( select null from ce_statement_recon_gt_v sr, ce_statement_lines sl, ce_statement_headers sh where sr.reference_id = jel.je_line_num and sr.reference_type = 'JE_LINE' and sr.je_header_id = jel.je_header_id and sr.status_flag = 'M' and sr.current_record_flag = 'Y' and sl.statement_line_id = sr.statement_line_id and sl.statement_header_id = sh.statement_header_id and sh.statement_date <= :c_as_of_date and sh.statement_date >= to_date(:c_cashbook_begin_date) ) ), q_stmt_error_lines as ( select 'STMT_LINE_ERROR' record_type, null operating_unit, null transaction_number, sh.gl_date gl_date, sl.trx_date effective_date, null agent, null payment_method, null transaction_subtype, null je_name, to_number(null) je_line, null je_line_type, to_date(null) je_posted_date, sh.statement_number statement_number, sl.line_number statement_line, sl.trx_type statement_trx_type, 'ERROR' status, nvl(sl.currency_code,nvl(sh.currency_code,aba.currency_code)) currency, decode(nvl(sl.currency_code,nvl(sh.currency_code,aba.currency_code)), sob.currency_code, sl.amount,nvl(sl.original_amount, sl.amount) ) transaction_amount, decode(sl.trx_type, 'CREDIT' , - sl.amount, 'MISC_CREDIT', - sl.amount, 'STOP' , - sl.amount, 'DEBIT' , sl.amount, 'MISC_DEBIT' , sl.amount, 'NSF' , sl.amount, 'REJECTED' , sl.amount, 0 ) accounted_amount, to_number(null) gl_end_balance, aba.asset_code_combination_id cash_account_ccid from ce_statement_lines sl, ce_statement_headers sh, ce_bank_accts_gt_v aba, gl_sets_of_books sob, ce_system_parameters sys where sl.statement_header_id = sh.statement_header_id and sh.bank_account_id = :p_bank_account_id and sh.statement_date <= :c_as_of_date and sh.statement_date >= sys.cashbook_begin_date and sl.status = 'ERROR' and aba.bank_account_id = sh.bank_account_id and sob.set_of_books_id = sys.set_of_books_id and sys.legal_entity_id = aba.account_owner_org_id ) -- -- Main Query Starts Here -- select (select xep.name legal_entity from xle_entity_profiles xep, ce_bank_accounts cba where xep.legal_entity_id = cba.account_owner_org_id and cba.bank_account_id = :p_bank_account_id ) legal_entity, :c_name ledger, :p_period_name period, :c_account_number_dsp bank_account_num, :c_account_name_dsp bank_account_name, :c_bank_curr_dsp bank_account_currency, :c_bank_name_dsp bank_name, :c_bank_branch_dsp bank_branch, case x.seq when 1 then 'General Ledger Cash Account Balance' when 2 then 'Bank Statement Closing Balance' when 3 then 'Unreconciled Receipts' when 4 then 'Unreconciled Payments' when 5 then 'Unreconciled Cashflows' when 6 then 'Unreconciled Journal Lines' when 7 then 'Stmnt Lines Marked As Errors' end record_type, x.gl_date, x.effective_date, x.currency, x.transaction_amount, x.accounted_amount, x.operating_unit, x.agent, x.transaction_number, x.payment_method, x.transaction_subtype, x.je_name, x.je_line, x.je_line_type, x.je_posted_date, x.statement_number, x.statement_line, x.statement_trx_type, x.status, x.gl_end_balance, case when x.seq = 1 then sum(x.gl_end_balance) over () - sum(x.accounted_amount) over () else to_number(null) end difference_amount, --gl cash account fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_flex_bal_seg', 'SQLGL', 'GL#', :c_chart_of_accounts_id, NULL, x.cash_account_ccid, 'GL_BALANCING', 'Y', 'VALUE') gl_company_code, fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_flex_bal_seg', 'SQLGL', 'GL#', :c_chart_of_accounts_id, NULL, x.cash_account_ccid, 'GL_BALANCING', 'Y', 'DESCRIPTION') gl_company_desc, fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_flex_bal_seg', 'SQLGL', 'GL#', :c_chart_of_accounts_id, NULL, x.cash_account_ccid, 'GL_ACCOUNT', 'Y', 'VALUE') gl_account_code, fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_flex_bal_seg', 'SQLGL', 'GL#', :c_chart_of_accounts_id, NULL, x.cash_account_ccid, 'GL_ACCOUNT', 'Y', 'DESCRIPTION') gl_account_desc, fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_flex_bal_seg', 'SQLGL', 'GL#', :c_chart_of_accounts_id, NULL, x.cash_account_ccid, 'FA_COST_CTR', 'Y', 'VALUE') gl_cost_center_code, fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_flex_bal_seg', 'SQLGL', 'GL#', :c_chart_of_accounts_id, NULL, x.cash_account_ccid, 'FA_COST_CTR', 'Y', 'DESCRIPTION') gl_cost_center_desc, fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_flex_bal_seg', 'SQLGL', 'GL#', :c_chart_of_accounts_id, NULL, x.cash_account_ccid, 'ALL', 'Y', 'VALUE') gl_cash_account, fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_flex_bal_seg', 'SQLGL', 'GL#', :c_chart_of_accounts_id, NULL, x.cash_account_ccid, 'ALL', 'Y', 'DESCRIPTION') gl_cash_account_desc, -- pivot labels :c_bank_name_dsp || ' - ' || :c_account_number_dsp || ' - ' || :c_account_name_dsp || ' (' || :c_bank_curr_dsp || ')' bank_account, case x.seq when 1 then 'A: General Ledger Cash Account Balance' when 2 then 'B: Bank Statement Closing Balance' when 3 then 'C: + Unreconciled Receipts' when 4 then 'D: +/- Unreconciled Payments' when 5 then 'E: +/- Unreconciled Cashflows' when 6 then 'F: +/- Unreconciled Journal Lines' when 7 then 'G: +/- Stmnt Lines Marked As Errors' end record_type_label, x.seq from ( select 1 seq, q_gl_end_bal.* from q_gl_end_bal union all select 2 seq, q_stmt_end_bal.* from q_stmt_end_bal union all select 3 seq, q_ar_receipts.* from q_ar_receipts union all select 4 seq, q_ap_payments.* from q_ap_payments union all select 5 seq, q_cashflows.* from q_cashflows union all select 6 seq, q_gl_je_lines.* from q_gl_je_lines union all select 7 seq, q_stmt_error_lines.* from q_stmt_error_lines ) x where 1=1 order by x.seq, x.gl_date nulls first, x.effective_date nulls first, x.agent, x.je_name, x.je_line, x.statement_number, x.statement_line, x.transaction_number Y CE CEXRECRE_XML #,##0.00;[Red]-#,##0.00 (:Bank_Account_Name is not null or :Bank_Account_Number is not null) AR CE تقرير تسوية الأستاذ العام Imported from BI Publisher Description: كشوف الحسابات - تسوية Application: إدارة النقدية Source: تقرير تسوية الأستاذ العام Short Name: CEXRECRE D CE Rechnungswesenabstimmung Imported from BI Publisher Description: Kontoauszüge - Abstimmung Application: Cash Management Source: Rechnungswesenabstimmung Short Name: CEXRECRE E CE Conciliación en Contabilidad General Imported from BI Publisher Description: Conciliación - Estados Application: Cash Management Source: Conciliación en Contabilidad General Short Name: CEXRECRE F CE Etat du rapprochement dans Oracle General Ledger Imported from BI Publisher Description: Relevés - Rapprochement Application: Cash Management Source: Etat du rapprochement dans Oracle General Ledger Short Name: CEXRECRE I CE Rapporto Quadratura General Ledger Imported from BI Publisher Description: Rendiconti - Quadratura Application: Gestione flussi di cassa Source: Rapporto Quadratura General Ledger Short Name: CEXRECRE JA CE General Ledger調整レポート Imported from BI Publisher Description: 取引明細書 - 調整 Application: Cash Management Source: General Ledger調整レポート Short Name: CEXRECRE KO CE GL 대사 보고서 Imported from BI Publisher Description: 명세서 - 대사 Application: Cash Management Source: GL 대사 보고서 Short Name: CEXRECRE PTB CE Relatório de Reconciliação do General Ledger Imported from BI Publisher Description: Demonstrativos - Reconciliação Application: Cash Management Source: Relatório de Reconciliação do General Ledger Short Name: CEXRECRE RU CE Отчет о выверке по Главной книге Imported from BI Publisher Description: Выписки - выверка Application: Движение денежных средств Source: Отчет о выверке по Главной книге Short Name: CEXRECRE S CE Avstämningsrapport Imported from BI Publisher Description: Utdrag - Avstämning Application: Cash Management Source: Huvudbok: Avstämningsrapport Short Name: CEXRECRE TR CE Genel Muhasebe Mutabakat Raporu Imported from BI Publisher Description: Ekstreler - Mutabakat Application: Nakit Yönetimi Source: Genel Muhasebe Mutabakat Raporu Short Name: CEXRECRE US CE General Ledger Reconciliation Application: Cash Management Description: Bank Statements - General Ledger Reconciliation Provides equivalent functionality to the following standard Oracle Forms/Reports - General Ledger Reconciliation Report Applicable Templates: Pivot: Reconciliation Summary Source: General Ledger Reconciliation Report Short Name: CEXRECRE DB package: CE_CEXRECRE_XMLP_PKG Please specify either the Bank Account Name or Bank Account Number Parameter ZHS CE 总帐管理系统调节报表 Imported from BI Publisher Description: 报表 - 调节 Application: 现金管理系统 Source: 总帐管理系统调节报表 Short Name: CEXRECRE Enginatics R12 only 1=1 :c_account_name_dsp :c_account_number_dsp :c_adjusted_bank_balance :c_all_translation :c_as_of_date :c_asset_cc_id :c_bank_branch_dsp :c_bank_curr_dsp :c_bank_name_dsp :c_cashbook_begin_date :c_chart_of_accounts_id :c_difference :c_flexdata :c_gl_currency_code :c_name :c_set_of_books_id :c_the_end :p_bank_account_id :p_clearing_ccid :p_closing_balance :p_closing_statement_number :p_conc_request_id :p_debug_mode :p_period_name :p_recon_accounting_flag :p_recon_used_flag :p_sql_trace :p_summ_or_detail :p_test_layout 1 -10 :p_bank_account_id Number select cba.bank_account_id from ce_bank_accounts cba where (:$flex$.Bank_Account_Name is not null or :$flex$.Bank_Account_Number is not null) and (:$flex$.Bank_Account_Name is null or cba.bank_account_name = :$flex$.Bank_Account_Name) and (:$flex$.Bank_Account_Number is null or cba.masked_account_num = :$flex$.Bank_Account_Number) Y AR رقم حساب البنك رقم حساب البنك D Kontonummer Kontonummer F ID du compte bancaire Numéro de compte bancaire I Numero conto bancario Numero conto bancario US Bank Account Id Bank Account Number ID ZHS 银行帐号 银行帐号 2 10 1=1 1=1 LOV CE Legal Entities C9C878DC04AA0259E0530100007FB368 Y select distinct xep.name value, null description from xle_entity_profiles xep, ce_bank_accounts cba, ce_bank_acct_uses_all cbaua, ce_security_profiles_v cspv where xep.legal_entity_id = cba.account_owner_org_id and cba.bank_account_id = cbaua.bank_account_id and ((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 clbovv.name from ce_le_bg_ou_vs_v clbovv, hr_operating_units hou, gl_ledgers gl, gl_ledger_config_details glcd, xle_firstparty_information_v xfi where hou.organization_id=nvl((select mgoat.organization_id from mo_glob_org_access_tmp mgoat where mgoat.organization_id=nvl(fnd_profile.value('DEFAULT_ORG_ID'),mgoat.organization_id) and rownum=1),fnd_global.org_id) and hou.set_of_books_id=gl.ledger_id and gl.object_type_code='L' and gl.configuration_id=glcd.configuration_id and glcd.object_type_code='LEGAL_ENTITY' and glcd.setup_step_code='NONE' and glcd.object_id=xfi.legal_entity_id and xfi.legal_entity_id = clbovv.legal_entity_id Y F Entité légale Legal Entity US Legal Entity Legal Entity 3 20 1=1 1=1 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 30 1=1 1=1 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 40 :p_period_name LOV custom Y select period_name id, period_name value, null description from gl_period_statuses where application_id=101 and set_of_books_id in (select set_of_books_id from ce_system_parameters sp,ce_bank_accounts ba where sp.legal_entity_id=ba.account_owner_org_id and ba.bank_account_id=:$flex$.bank_account_id) and closing_status in ('O','F','C') order by period_year desc,period_num desc Y AR اسم الفترة اسم الفترة D Periodenbezeichnung Periodenbezeichnung F Nom de la période Nom de la période I Nome periodo Nome del periodo US Period Name Period Name ZHS 期间名称 期间名称 6 50 :p_closing_balance LOV custom select csh.control_end_balance value, csh.statement_date description from ce_statement_headers csh where csh.bank_account_id = :$flex$.bank_account_id order by csh.statement_date desc select csh.control_end_balance from ce_statement_headers csh where csh.bank_account_id = :$flex$.bank_account_id and csh.statement_date = (select max(csh2.statement_date) from ce_statement_headers csh2 where csh2.bank_account_id = :$flex$.bank_account_id and csh2.statement_date < (select gps.end_date + 1 from ce_bank_accounts cba, ce_system_parameters csp, gl_period_statuses gps where cba.bank_account_id=:$flex$.bank_account_id and csp.legal_entity_id=cba.account_owner_org_id and gps.application_id=101 and gps.set_of_books_id = csp.set_of_books_id and gps.period_name = :$flex$.Period_Name ) ) Y AR الرصيد الختامي الرصيد الختامي D Endsaldo Endsaldo F Solde de clôture Solde de clôture I Saldo di chiusura Saldo di chiusura US Closing Balance Closing Balance ZHS 期末余额 期末余额 :$flex$.Bank_Account_Name Bank Account Name Bank Account Id :$flex$.Bank_Account_Number Bank Account Number Bank Account Id :$flex$.Period_Name Period Name Closing Balance :$flex$.bank_account_id Bank Account Id Closing Balance :$flex$.bank_account_id Bank Account Id Period Name :$flex$.legal_entity Legal Entity Bank Account Name :$flex$.legal_entity Legal Entity Bank Account Number Pivot: Default Template Summary Pivot with drilldown to details SYSADMIN 12 ACCOUNTED_AMOUNT 17 AGENT 4 7 BANK_ACCOUNT_CURRENCY 5 BANK_ACCOUNT_NAME 4 BANK_ACCOUNT_NUM 6 BANK_NAME 10 CURRENCY 29 DIFFERENCE_AMOUNT 14 EFFECTIVE_DATE 3 8 GL_CASH_ACCOUNT 30 GL_CASH_ACCOUNT_DESC 13 GL_DATE 2 28 GL_END_BALANCE 22 JE_LINE 6 23 JE_LINE_TYPE 21 JE_NAME 5 24 JE_POSTED_DATE 2 LEDGER 1 LEGAL_ENTITY 19 OPERATING_UNIT 18 PAYMENT_METHOD 3 PERIOD 9 RECORD_TYPE 32 RECORD_TYPE_LABEL 31 SEQ 1 26 STATEMENT_LINE 8 25 STATEMENT_NUMBER 7 27 STATEMENT_TRX_TYPE 15 STATUS 11 TRANSACTION_AMOUNT 16 TRANSACTION_NUMBER 9 20 TRANSACTION_SUBTYPE ACCOUNTED_AMOUNT VALUE 2 SUM DIFFERENCE_AMOUNT VALUE 3 SUM GL_CASH_ACCOUNT FILTER 4 GL_END_BALANCE VALUE 1 SUM LEDGER FILTER 2 LEGAL_ENTITY FILTER 1 PERIOD FILTER 3 RECORD_TYPE_LABEL ROW 1 S Site Pivot: Reconciliation Summary Reconciliation Summary Pivot with drilldown to details SYSADMIN 13 ACCOUNTED_AMOUNT 18 AGENT 4 8 BANK_ACCOUNT_CURRENCY 5 BANK_ACCOUNT_NAME 4 BANK_ACCOUNT_NUM 33 BANK_ACCOUNT_PIVOT_LABEL 6 BANK_NAME 7 BRANCH_NAME 11 CURRENCY 30 DIFFERENCE_AMOUNT 15 EFFECTIVE_DATE 3 9 GL_CASH_ACCOUNT 31 GL_CASH_ACCOUNT_DESC 14 GL_DATE 2 29 GL_END_BALANCE 23 JE_LINE 6 24 JE_LINE_TYPE 22 JE_NAME 5 25 JE_POSTED_DATE 2 LEDGER 1 LEGAL_ENTITY 20 OPERATING_UNIT 19 PAYMENT_METHOD 3 PERIOD 10 RECORD_TYPE 34 RECORD_TYPE_LABEL 32 SEQ 1 27 STATEMENT_LINE 8 26 STATEMENT_NUMBER 7 28 STATEMENT_TRX_TYPE 16 STATUS 12 TRANSACTION_AMOUNT 17 TRANSACTION_NUMBER 9 21 TRANSACTION_SUBTYPE ACCOUNTED_AMOUNT VALUE 2 SUM BANK_ACCOUNT_PIVOT_LABEL ROW 1 BANK_NAME FILTER 3 BRANCH_NAME FILTER 4 DIFFERENCE_AMOUNT VALUE 3 SUM GL_CASH_ACCOUNT FILTER 6 GL_END_BALANCE VALUE 1 SUM LEDGER FILTER 2 LEGAL_ENTITY FILTER 1 PERIOD FILTER 5 RECORD_TYPE_LABEL ROW 2 S Site Pivot: Reconciliation Summary