ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN CA011D4BA5BE62B6E0530100007FF184 CE Business Groups Y select clbovv.name value, null description from ce_le_bg_ou_vs_v clbovv where clbovv.organization_type='BUSINESS_GROUP' order by clbovv.name US CE Business Groups 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 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 CA012435B5946734E0530100007FFFA2 CE Operating Units Y select clbovv.name value, null description from ce_le_bg_ou_vs_v clbovv where clbovv.organization_type='OPERATING_UNIT' order by clbovv.name US CE Operating Units C6E085D46F9851A9E05362FB09050512 select x.bank_account_currency, x.bank_account_name, x.bank_account_num bank_account_number, x.bank_name, x.bank_branch_name branch_name, x.org_name organization, case x.type when 'PAYMENTS' then 'Payments in Transit' when 'RECEIPTS' then 'Receipts in Transit' when 'ROI_LINE' then 'Open-Interface Transactions in Transit' when 'PAYROLL' then 'Payroll Payments in Transit' else x.type || ' in Transit' end in_transit_type, x.supplier_customer "Supplier/Customer/Agent Name", x.payment_date "Payment/Remit/Trx Date", x.maturity_date, x.payrcpt_num "Payment/Receipt/Trx Number", x.payment_method "Payment Method/Trx Type", x.c_currency_code currency, x.amount "Payment/Receipt/Trx Amount", x.base_amount bank_account_amount, x.cash_in_transit net_cash_in_transit, x.check_number, -- pivot labels x.bank_name || ' - ' || x.bank_account_num || ' - ' || x.bank_account_name || ' (' || x.bank_account_currency || ')' bank_account_pivot_label, x.org_name organization_pivot_label, x.transaction_order in_transit_type_order from ( select 'PAYMENTS' type, nvl(c.vendor_name,c.remit_to_supplier_name) supplier_customer, ba.bank_account_name bank_account_name, ba.masked_account_num bank_account_num, ba.currency_code bank_account_currency, bb.bank_name, bb.bank_branch_name, c.check_date payment_date, c.future_pay_due_date maturity_date, to_char(c.check_number) payrcpt_num, pmt.payment_method_name payment_method, c.currency_code c_currency_code, nvl(c.amount,0) amount, decode(c.currency_code,ba.currency_code,nvl(-c.amount,0),nvl(-c.base_amount,0)) cash_in_transit, decode(c.currency_code,ba.currency_code,nvl(c.amount,0),nvl(c.base_amount,0)) base_amount, c.check_number check_number, '20' transaction_order, ou.name org_name from ce_bank_accounts ba, ce_bank_branches_v bb, ap_checks_all c, ce_bank_acct_uses_all bau, ce_security_profiles_gt ou, ce_system_parameters sys, iby_payment_methods_vl pmt, ap_payment_history_all apha where bb.branch_party_id = ba.bank_branch_id and ba.bank_account_id = bau.bank_account_id and c.payment_method_code = pmt.payment_method_code and apha.check_id = c.check_id and apha.accounting_date <= nvl(:p_as_of_date, trunc(sysdate)) and not exists (select null from ap_payment_history_all aph2 where aph2.check_id = c.check_id and aph2.creation_date > apha.creation_date and aph2.accounting_date <= nvl(:p_as_of_date, trunc(sysdate)) ) and apha.transaction_type not in('PAYMENT CLEARING', 'PAYMENT CANCELLED','PAYMENT CLEARING ADJUSTED','REFUND CANCELLED') and c.status_lookup_code not in ('RECONCILED','CLEARED') and bau.bank_acct_use_id = c.ce_bank_acct_use_id and bau.ap_use_enable_flag = 'Y' and bau.org_id = c.org_id and bau.org_id = ou.organization_id and ou.organization_type = 'OPERATING_UNIT' and ba.account_owner_org_id = sys.legal_entity_id and trunc(c.check_date) <= nvl(:p_as_of_date, trunc(sysdate)) and c.check_date >= sys.cashbook_begin_date and case when c.remit_to_supplier_id is not null and c.remit_to_supplier_id = decode(c.status_lookup_code,'SET UP', -1, 'SPOILED' , -2 , c.remit_to_supplier_id) then 1 when nvl(c.vendor_id,-1) = decode(c.status_lookup_code,'SET UP', -1, 'SPOILED' , -2 , nvl(c.vendor_id,-1)) then 1 end = 1 and :p_type in ('AR_AND_AP', 'PAYMENTS', 'ALL') and ou.name = nvl(ou.name,ou.name) -- union all -- select 'RECEIPTS' type, hz.party_name supplier_customer, ba.bank_account_name, ba.masked_account_num bank_account_num, ba.currency_code bank_account_currency, bb.bank_name, bb.bank_branch_name, acrh.trx_date payment_date, ps.due_date maturity_date, acr.receipt_number payrcpt_num, arm.name payment_method, acr.currency_code c_currency_code, nvl(acrh.amount,0) amount, decode(acr.currency_code,ba.currency_code,nvl(acrh.amount,0),nvl(acrh.acctd_amount,0)) cash_in_transit, decode(acr.currency_code,ba.currency_code,nvl(acrh.amount,0),nvl(acrh.acctd_amount,0)) base_amount, 0 check_number, '10' transaction_order, ou.name org_name from ar_receipt_methods arm, ar_payment_schedules_all ps, ce_bank_accounts ba, ce_bank_branches_v bb, hz_cust_accounts cu, hz_parties hz, ar_cash_receipts acr, ar_cash_receipt_history_all acrh, ce_bank_acct_uses_all bau, ce_security_profiles_gt ou, ce_system_parameters sys where arm.receipt_method_id = acr.receipt_method_id and cu.cust_account_id(+) = acr.pay_from_customer and hz.party_id(+) = cu.party_id and ba.bank_branch_id = bb.branch_party_id and ba.bank_account_id = bau.bank_account_id and bau.bank_acct_use_id = acr.remit_bank_acct_use_id and bau.org_id = acr.org_id and bau.org_id = ou.organization_id and ou.organization_type = 'OPERATING_UNIT' and ba.account_owner_org_id = sys.legal_entity_id and acr.type in ('CASH','MISC') and ps.cash_receipt_id (+) = acrh.cash_receipt_id and acr.cash_receipt_id = acrh.cash_receipt_id and acrh.status = 'REMITTED' and acrh.trx_date <= nvl(:p_as_of_date, sysdate) and not exists (select 'x' from ar_cash_receipt_history_all acrh2 where acrh2.cash_receipt_id = acr.cash_receipt_id and acrh2.status in('CLEARED','REVERSED') and acrh2.trx_date <= nvl(:p_as_of_date, sysdate) and acrh2.trx_date >= acrh.trx_date ) and acr.receipt_date <= nvl(:p_as_of_date,sysdate) and acrh.trx_date >= sys.cashbook_begin_date and :p_type in ('AR_AND_AP', 'RECEIPTS','ALL') and ou.name = nvl(ou.name,ou.name) -- union all -- select 'ROI_LINE' type, null supplier_customer, ba.bank_account_name bank_account_name, ba.masked_account_num bank_account_num, ba.currency_code bank_account_currency, bb.bank_name, bb.bank_branch_name, roi.trx_date payment_date, to_date(null) maturity_date, roi.trx_number payrcpt_num, trx_type_dsp payment_method, roi.currency_code c_currency_code, nvl(roi.amount,0) amount, decode(roi.currency_code ,ba.currency_code, decode(roi.trx_type, 'PAYMENT', nvl(-roi.amount,0), nvl(roi.amount, 0)) , decode(roi.trx_type, 'PAYMENT', nvl(-roi.acctd_amount,0), nvl(roi.acctd_amount,0)) ) cash_in_transit, decode(roi.currency_code,ba.currency_code, nvl(roi.amount,0),nvl(roi.acctd_amount,0)) base_amount, 0 check_number, '30' transaction_order, null org_name from ce_999_interface_v roi, ce_bank_accts_gt_v ba, ce_bank_branches_v bb, ce_system_parameters sys, ce_statement_recon_gt_v cre where ba.bank_branch_id = bb.branch_party_id and ba.bank_account_id = roi.bank_account_id and ba.account_owner_org_id = sys.legal_entity_id and ba.recon_enable_oi_flag = 'Y' and roi.status = ba.recon_oi_float_status and roi.trx_date <= nvl(:p_as_of_date, sysdate) and roi.trx_date >= sys.cashbook_begin_date and cre.reference_id (+) = roi.trx_id and cre.reference_type (+) = 'ROI_LINE' and nvl(cre.status_flag,'U') = 'U' and nvl(cre.current_record_flag,'Y') = 'Y' and :p_type in ('ROI_LINES', 'ALL') -- union all -- Payroll Payments -- select 'PAYROLL' type, null supplier_customer, ba.bank_account_name bank_account_name, ba.masked_account_num bank_account_num, ba.currency_code bank_account_currency, bb.bank_name, bb.bank_branch_name, ppa.effective_date payment_date, to_date(null) maturity_date, paa.serial_number payrcpt_num, popm.org_payment_method_name payment_method, popm.currency_code c_currency_code, ppp.value amount, decode(popm.currency_code,ba.currency_code,nvl(-ppp.value,0),nvl(-ppp.base_currency_value,0)) cash_in_transit, decode(popm.currency_code,ba.currency_code,nvl(ppp.value,0),nvl(ppp.base_currency_value,0)) base_amount, 0 check_number, '40' transaction_order, ou.name org_name from ce_bank_branches_v bb, ce_bank_accounts ba, gl_sets_of_books sob, ce_system_parameters sys, pay_ce_reconciled_payments pcrp, pay_pre_payments ppp, pay_assignment_actions paa, pay_payroll_actions ppa, pay_org_payment_methods_f popm, ce_bank_acct_uses_all bau, ce_security_profiles_gt ou, pay_payment_types ppt where bb.branch_party_id = ba.bank_branch_id and ba.bank_account_id = bau.bank_account_id and bau.payroll_bank_account_id = popm.external_account_id and bau.org_id = ou.organization_id and ou.organization_type = 'BUSINESS_GROUP' and ba.account_owner_org_id = sys.legal_entity_id and sys.set_of_books_id = sob.set_of_books_id and pcrp.assignment_action_id (+) = paa.assignment_action_id and ppp.org_payment_method_id = popm.org_payment_method_id and ppa.payroll_action_id = paa.payroll_action_id and paa.pre_payment_id = ppp.pre_payment_id and ppt.payment_type_id = popm.payment_type_id --9495956 and ppa.action_type in ( 'P', 'H', 'E','M') and ( ppa.action_type <> 'E' or (ppa.action_type = 'E' and paa.serial_number not in ('-1','-2')) ) and ( (ppa.action_type != 'M') or (ppa.action_type = 'M' and ppt.reconciliation_function is not null) ) and paa.action_status in ('C', 'V') and ( ( (pcrp.cleared_date is null ) or (pcrp.cleared_date > nvl(:p_as_of_date, sysdate) ) ) or ( (paa.action_status = 'V') and (ppa.effective_date > nvl(:p_as_of_date, sysdate) ) ) ) and ppa.effective_date <= nvl(:p_as_of_date, sysdate) and ppa.effective_date >= sys.cashbook_begin_date and ppa.effective_date between popm.effective_start_date and popm.effective_end_date and not exists (select * from pay_action_interlocks pai where pai.locked_action_id = paa.assignment_action_id ) and :p_type in ( 'PAYROLLS','ALL') and ou.name = nvl(:p_business_group,ou.name) -- union all -- Voided Payroll Payments -- select 'PAYROLL' type, null supplier_customer, ba.bank_account_name bank_account_name, ba.masked_account_num bank_account_num, ba.currency_code bank_account_currency, bb.bank_name, bb.bank_branch_name, ppa.effective_date payment_date, to_date(null) maturity_date, paa.serial_number payrcpt_num, popm.org_payment_method_name payment_method, popm.currency_code c_currency_code, ppp.value amount, decode(popm.currency_code,ba.currency_code,nvl(-ppp.value,0),nvl(-ppp.base_currency_value,0)) cash_in_transit, decode(popm.currency_code,ba.currency_code,nvl(ppp.value,0),nvl(ppp.base_currency_value,0)) base_amount, 0 check_number, '40' transaction_order, ou.name org_name from ce_bank_branches_v bb, ce_bank_accounts ba, gl_sets_of_books sob, ce_system_parameters sys, pay_ce_reconciled_payments pcrp, pay_pre_payments ppp, pay_assignment_actions paa, pay_payroll_actions ppa, pay_org_payment_methods_f popm, pay_action_interlocks pai, ce_bank_acct_uses_all bau, ce_security_profiles_gt ou where bb.branch_party_id = ba.bank_branch_id and ba.bank_account_id = bau.bank_account_id and bau.payroll_bank_account_id = popm.external_account_id and bau.org_id = ou.organization_id and ou.organization_type = 'BUSINESS_GROUP' and ba.account_owner_org_id = sys.legal_entity_id and sys.set_of_books_id = sob.set_of_books_id and pcrp.assignment_action_id (+) = paa.assignment_action_id and ppp.org_payment_method_id = popm.org_payment_method_id and ppa.payroll_action_id = paa.payroll_action_id and paa.pre_payment_id = ppp.pre_payment_id and ppa.action_type = 'H' and paa.action_status = 'C' and nvl(ppa.date_earned,ppa.effective_date) <= nvl(:p_as_of_date, sysdate) and nvl(ppa.date_earned,ppa.effective_date) >= sys.cashbook_begin_date and pai.locked_action_id = paa.assignment_action_id and ppa.effective_date between popm.effective_start_date and popm.effective_end_date and exists (select * from pay_assignment_actions paa2, pay_payroll_actions ppa2 where paa2.assignment_action_id = pai.locking_action_id and paa2.payroll_action_id = ppa2.payroll_action_id and ppa2.effective_date > nvl(:p_as_of_date, sysdate) and ppa2.action_type = 'D' ) and :p_type in ( 'PAYROLLS','ALL') and ou.name = nvl(:p_business_group,ou.name) ) x where 1=1 order by x.bank_account_currency, x.bank_account_name, x.bank_account_num, x.transaction_order, x.org_name, x.check_number, x.payrcpt_num Changed to lowercase Y #,##0.00;[Red]-#,##0.00 CE_CEXCSHTR_XMLP_PKG US CE Cash in Transit Application: Cash Management Description: Cash in Transit Provides equivalent functionality to the following standard Oracle Forms/Reports - Cash in Transit Applicable Templates: - Pivot: Cash in Transit - Pivot: Bank Account Currency, Bank Account, In Transit Type, Operating Unit, Third Party (Supplier/Customer) with drill down to details - Cash In Transit Detail - Detail Extract Source: Cash in Transit (CEXCSHTR) DB package: CE_CEXCSHTR_XMLP_PKG (required to initialize security) Enginatics R12 only 1=1 :c_all_translation :c_bank_account_name_dsp :c_bank_account_num_dsp :c_bank_branch_name_dsp :c_bank_currency_code_dsp :c_bank_name_dsp :c_cb_begin_date :c_company_name_header :c_date_check_lex :c_date_receipt_lex :c_order_by :c_order_by_meaning :c_recon_check_lex :c_sort_by_lex :c_the_end :p_as_of_date :p_as_of_date_t :p_bank_account_id :p_bg_org_id :p_business_group :p_conc_request_id :p_debug_mode :p_min_precision :p_org_id :p_sort_by :p_sql_trace :p_type 1 10 1=1 x.bank_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 Bank US Bank Name Bank 2 20 1=1 x.bank_branch_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 AR فرع البنك فرع البنك D Zweigstelle Zweigstelle E Sucursal Bancaria Sucursal Bancaria F Succursale bancaire Agence bancaire I Filiale banca Filiale banca JA 銀行支店 銀行支店 KO 은행 지점 은행 지점 PTB Agência Bancária Agência Bancária RU Филиал банка Филиал банка S Bankkontor Bankkontor TR Banka Şubesi Banka Şubesi US Bank Branch Bank Branch ZHS 分行 分行 3 30 1=1 x.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 Bank Account Name US Bank Account Name Bank Account Name 4 40 1=1 x.bank_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 AR رقم حساب البنك رقم حساب البنك D Kontonummer Kontonummer E Número de Cuenta Bancaria Número de Cuenta Bancaria F Numéro de compte bancaire Numéro de compte bancaire I Numero conto bancario Numero conto bancario JA 銀行口座番号 銀行口座番号 KO 은행 계정 번호 은행 계좌 번호 PTB Número da Conta Bancária Número da Conta Bancária RU № банковского счета № банковского счета S Bankkontonummer Bankkontonummer TR Banka Hesap Numarası Banka Hesap Numarası US Bank Account Number Bank Account Num ZHS 银行帐号 银行帐号 5 50 :p_type LOV Oracle CE_TRAN_TYPE_CEXCSHTR Y select lookup_code id, meaning value, description description from ce_lookups where lookup_type='TRAN_ORG_TYPE' and lookup_code in ('PAYMENTS','RECEIPTS','AR_AND_AP','ROI_LINES','PAYROLLS','ALL') order by value,description ALL Y AR نوع الحركة نوع الحركة D Transaktionsart Transaktionsart E Tipo de Transacción Tipo de Transacción F Type de transaction Type de mouvement I Tipo transazione Tipo di transazione JA 取引タイプ 取引タイプ KO 거래 유형 거래 유형 PTB Tipo de Transação Tipo de transação RU Тип транзакции Тип транзакции S Transaktionstyp Transaktionstyp TR İşlem Tipi İşlem tipi US Transaction Type Transaction type ZHS 事务处理类型 事务处理类型 6 70 :p_business_group LOV CE Business Groups CA011D4BA5BE62B6E0530100007FF184 Y select clbovv.name value, null description from ce_le_bg_ou_vs_v clbovv where clbovv.organization_type='BUSINESS_GROUP' order by clbovv.name AR مجموعة أعمال كشف الرواتب مجموعة أعمال كشف الرواتب D Lohn und Gehalt: Unternehmensgruppe Lohn und Gehalt: Unternehmensgruppe E Grupo de Trabajo de Nóminas Grupo de Trabajo de Nóminas F Groupe d'affaires Paie Entité professionnelle Payroll I Business group cicli paghe Business group cicli paghe JA 給与ビジネス・グループ 給与ビジネス・グループ KO Payroll 업무 그룹 Payroll 업무 그룹 PTB Grupo Funcional da Folha de Pagamento Grupo Funcional da Folha de Pagamento RU ЗРП: бизнес-группа ЗРП: бизнес-группа S Verksamhetsgrupp för lönegrupp Verksamhetsgrupp för lönegrupp TR Bordro İş Grubu Bordro İş Grubu US Payroll Business Group Payroll Business Group ZHS Oracle Payroll - 业务组 Oracle Payroll - 业务组 7 80 :p_operating_unit LOV CE Operating Units CA012435B5946734E0530100007FFFA2 Y select clbovv.name value, null description from ce_le_bg_ou_vs_v clbovv where clbovv.organization_type='OPERATING_UNIT' order by clbovv.name AR وحدة تشغيل حساب المدفوعات/المقبوضات وحدة تشغيل حساب المدفوعات/المقبوضات D KR/DE Betriebseinheit KR/DE Betriebseinheit E Unidad Operativa de AP/AR Unidad Operativa de AP/AR F Unité opérationnelle AP/AR Unité opérationnelle AP/AR I Unità operativa AP/AR Unità operativa AP/AR JA AP/AR営業単位 AP/AR営業単位 KO AP/AR 운영 단위 AP/AR 운영 단위 PTB Unidade Operacional AP/AR Unidade Operacional AP/AR RU КР/ДБ: операционная единица КР/ДБ: операционная единица S Lev.resk./kundresk.: Operativ enhet Lev.resk./kundresk.: Operativ enhet TR AP/AR Faaliyet Birimi AP/AR Faaliyet Birimi US AP/AR Operating Unit AP/AR Operating Unit ZHS AP/AR 业务实体 AP/AR 业务实体 8 90 :p_as_of_date Date AR تاريخ المقاصة من تاريخ المقاصة من D Von Auszifferungsdatum Von Auszifferungsdatum E De Fecha de Conciliación De Fecha de Conciliación F En date du Date d'apurement - Du I Data liquidazione - Da Data liquidazione - Da JA 決済日 自 決済日 自 KO 정산 일자: 부터 정산 일자: 부터 PTB Data da Compensação - De Data da Compensação - De RU Дата перерасчета с Дата перерасчета с S Från regleringsdatum Från regleringsdatum TR İlk Takas Tarihi İlk Takas Tarihi US As Of Date As of Date ZHS 结清日期自 结清日期自 :$flex$.Bank_name Bank Name Bank Branch :$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 842AD259889FFD0844A9E4E577DF72B3 Cash in Transit Detail Cash in Transit detail extract SYSADMIN 15 BANK_ACCOUNT_AMOUNT 3 BANK_ACCOUNT_CURRENCY 1 1 BANK_ACCOUNT_NAME 2 2 BANK_ACCOUNT_NUMBER 3 4 BANK_NAME 5 BRANCH_NAME 13 CURRENCY 6 IN_TRANSIT_TYPE 4 10 MATURITY_DATE 16 NET_CASH_IN_TRANSIT 7 ORGANIZATION 5 11 Payment Method/Trx Type 14 Payment/Receipt/Trx Amount 12 Payment/Receipt/Trx Number 7 9 Payment/Remit/Trx Date 6 8 Supplier/Customer/Agent Name S Site 85001546B09F8F3D5906C77B791E5036 Pivot: Cash in Transit Pivot: Cash in Transit by Bank Account Currency, Bank Account, In Transit Type, Operating Unit, Third Party (Supplier/Customer) with drill down to details SYSADMIN 15 BANK_ACCOUNT_AMOUNT 3 BANK_ACCOUNT_CURRENCY 1 1 BANK_ACCOUNT_NAME 2 2 BANK_ACCOUNT_NUMBER 3 17 BANK_ACCOUNT_PIVOT_LABEL 4 BANK_NAME 5 BRANCH_NAME 13 CURRENCY 6 IN_TRANSIT_TYPE 4 10 MATURITY_DATE 16 NET_CASH_IN_TRANSIT 7 ORGANIZATION 5 18 ORGANIZATION_PIVOT_LABEL 11 Payment Method/Trx Type 14 Payment/Receipt/Trx Amount 12 Payment/Receipt/Trx Number 7 9 Payment/Remit/Trx Date 6 8 Supplier/Customer/Agent Name BANK_ACCOUNT_AMOUNT VALUE 3 SUM BANK_ACCOUNT_CURRENCY ROW 1 BANK_ACCOUNT_NAME FILTER 1 BANK_ACCOUNT_NUMBER FILTER 2 BANK_ACCOUNT_PIVOT_LABEL ROW 2 BANK_NAME FILTER 3 BRANCH_NAME FILTER 4 IN_TRANSIT_TYPE VALUE 1 COUNT IN_TRANSIT_TYPE ROW 3 NET_CASH_IN_TRANSIT VALUE 4 SUM ORGANIZATION FILTER 5 ORGANIZATION_PIVOT_LABEL ROW 4 Payment/Receipt/Trx Amount VALUE 2 SUM Supplier/Customer/Agent Name ROW 5 S Site 85001546B09F8F3D5906C77B791E5036