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 F82893FE27E8AB4BE0530100007FB42A CE Internal Bank Account Number (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_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 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 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 C9FDE8BE76245CB0E0530100007F7D1E with ce_avail_trx_v as ( select catv.bank_account_id, catv.bank_account_name, catv.bank_account_num, catv.bank_currency_code, catv.bank_name, catv.bank_branch_name, xxen_util.meaning('JE_LINES','TRAN_ORG_TYPE',260) source, 'JE_LINES' source_code, catv.trx_type, catv.clearing_trx_type, catv.batch_name, catv.agent_name, catv.trx_date, catv.maturity_date, catv.bank_account_amount, catv.trx_number, catv.type_meaning, catv.payment_method, catv.currency_code, catv.amount, catv.journal_category, catv.period_name, catv.journal_entry_line_number, catv.status, catv.trx_id, catv.org_id, catv.legal_entity_id, catv.code_combination_id from ce_101_transactions_v catv where :p_type in ('JE_LINES','ALL') union all select catv.bank_account_id, catv.bank_account_name, catv.bank_account_num, catv.bank_currency_code, catv.bank_name, catv.bank_branch_name, xxen_util.meaning('XTR_LINES','TRAN_ORG_TYPE',260) source, 'XTR_LINES' source_code, catv.trx_type, catv.clearing_trx_type, catv.batch_name, catv.agent_name, catv.trx_date, catv.maturity_date, catv.bank_account_amount, catv.trx_number, catv.type_meaning, catv.payment_method, catv.currency_code, catv.amount, catv.journal_category, catv.period_name, catv.journal_entry_line_number, catv.status, catv.trx_id, catv.org_id, catv.legal_entity_id, null code_combination_id from ce_185_transactions_v catv where :p_type in ('XTR_LINES','ALL') union all select catv.bank_account_id, catv.bank_account_name, catv.bank_account_num, catv.bank_currency_code, catv.bank_name, catv.bank_branch_name, xxen_util.meaning('PAYMENTS','TRAN_ORG_TYPE',260) source, 'PAYMENTS' source_code, catv.trx_type, catv.clearing_trx_type, catv.batch_name, catv.agent_name, catv.trx_date, catv.maturity_date, catv.bank_account_amount, catv.trx_number, catv.type_meaning, catv.payment_method, catv.currency_code, catv.amount, catv.journal_category, catv.period_name, catv.journal_entry_line_number, catv.status, catv.trx_id, catv.org_id, catv.legal_entity_id, null code_combination_id from ce_200_transactions_v catv where :p_type in ('PAYMENTS','ALL') union all select catv.bank_account_id, catv.bank_account_name, catv.bank_account_num, catv.bank_currency_code, catv.bank_name, catv.bank_branch_name, xxen_util.meaning('RECEIPTS','TRAN_ORG_TYPE',260) source, 'RECEIPTS' source_code, catv.trx_type, catv.clearing_trx_type, catv.batch_name, catv.agent_name, catv.trx_date, catv.maturity_date, catv.bank_account_amount, catv.trx_number, catv.type_meaning, catv.payment_method, catv.currency_code, catv.amount, catv.journal_category, catv.period_name, catv.journal_entry_line_number, catv.status, catv.trx_id, catv.org_id, catv.legal_entity_id, null code_combination_id from ce_222_transactions_v catv where :p_type in ('RECEIPTS','ALL') union all select catv.bank_account_id, catv.bank_account_name, catv.bank_account_num, catv.bank_currency_code, catv.bank_name, catv.bank_branch_name, xxen_util.meaning('PAYROLLS','TRAN_ORG_TYPE',260) source, 'PAYROLLS' source_code, catv.trx_type, catv.clearing_trx_type, catv.batch_name, catv.agent_name, catv.trx_date, catv.maturity_date, catv.bank_account_amount, catv.trx_number, catv.type_meaning, catv.payment_method, catv.currency_code, catv.amount, catv.journal_category, catv.period_name, catv.journal_entry_line_number, catv.status, catv.trx_id, catv.org_id, catv.legal_entity_id, null code_combination_id from ce_801_transactions_v catv where :p_type in ('PAYROLLS','ALL') union all select catv.bank_account_id, catv.bank_account_name, catv.bank_account_num, catv.bank_currency_code, catv.bank_name, catv.bank_branch_name, xxen_util.meaning('PAYROLLS','TRAN_ORG_TYPE',260) source, 'PAYROLLS' source_code, catv.trx_type, catv.clearing_trx_type, catv.batch_name, catv.agent_name, catv.trx_date, catv.maturity_date, catv.bank_account_amount, catv.trx_number, catv.type_meaning, catv.payment_method, catv.currency_code, catv.amount, catv.journal_category, catv.period_name, catv.journal_entry_line_number, catv.status, catv.trx_id, catv.org_id, catv.legal_entity_id, null code_combination_id from ce_801_eft_transactions_v catv where :p_type in ('PAYROLLS','ALL') union all select catv.bank_account_id, catv.bank_account_name, catv.bank_account_num, catv.bank_currency_code, catv.bank_name, catv.bank_branch_name, xxen_util.meaning('ROI_LINES','TRAN_ORG_TYPE',260) source, 'ROI_LINES' source_code, catv.trx_type, catv.clearing_trx_type, catv.batch_name, catv.agent_name, catv.trx_date, catv.maturity_date, catv.bank_account_amount, catv.trx_number, catv.type_meaning, catv.payment_method, catv.currency_code, catv.amount, catv.journal_category, catv.period_name, catv.journal_entry_line_number, catv.status, catv.trx_id, catv.org_id, catv.legal_entity_id, null code_combination_id from ce_999_transactions_v catv where catv.clearing_trx_type != 'XTR_LINE' and -- exclude 185 lines :p_type in ('ROI_LINES','ALL') ) -- -- Main Query Starts Here -- select cat.legal_entity, cat.bank_account_num, cat.bank_account_name, cat.bank_account_currency, cat.bank_name, cat.branch_name, cat.source, cat.type, case when cat.type_code in ('CASH','MISC') and cat.transaction_order = 10 then 'Available Receipts' when cat.type_code in ('CASH','MISC') and cat.transaction_order = 20 then 'Reversed Receipts' when cat.type_code in ('PAYMENT') and cat.transaction_order = 30 then 'Available Payments' when cat.type_code in ('PAYMENT') and cat.transaction_order = 40 then 'Voided Payments' when cat.type_code in ('PAYROLL') and cat.transaction_order = 35 then 'Available Payroll Payments' when cat.type_code in ('PAYROLL') and cat.transaction_order = 45 then 'Voided Payroll Payments' when cat.type_code in ('REFUND') and cat.transaction_order = 50 then 'Available Refunds' when cat.type_code in ('REFUND') and cat.transaction_order = 60 then 'Voided Refunds' when cat.type_code in ('STATEMENT') and cat.transaction_order = 75 then 'Unreconciled Statement Lines' when cat.type_code in ('STATEMENT') and cat.transaction_order = 80 then 'Reconciled Statement Lines' when cat.type_code in ('CASHFLOW') and cat.transaction_order = 90 then 'Available Cashflows' when cat.type_code in ('JE_LINE') then 'Available Journal Entry Lines' when cat.type_code in ('ROI-R') then 'Available Open-Interface Transactions - Receipts' when cat.type_code in ('ROI-P') then 'Available Open-Interface Transactions - Payments' end type_description, cat.effective_date, cat.maturity_date, cat.agent_name, cat.payment_method, cat.transaction_number, cat.journal_batch_name, cat.journal_period, cat.journal_category, cat.journal_line_number, cat.journal_descripton, cat.journal_account, cat.statement_number, cat.line_type, cat.transaction_subtype, cat.direction, cat.currency_code, cat.amount, round(case when cat.type_code in ('JE_LINE','STATEMENT') then cat.account_amount else cat.bank_account_amount end,fc.precision) bank_account_amount, round(cat.account_amount,fc.precision) net_amount_available, -- case when cat.source_code in ('RECEIPTS','PAYMENTS') then cat.organization end ar_ap_operating_unit, case when cat.source_code in ('PAYROLLS') then cat.organization end payroll_business_group, cat.treasury_legal_entity, -- -- GL Cash Account Details cat.gl_company_code, cat.gl_company_desc, cat.gl_account_code, cat.gl_account_desc, cat.gl_cost_center_code, cat.gl_cost_center_desc, cat.gl_cash_account, cat.gl_cash_account_desc, -- pivot labels cat.gl_company_code || ' - ' || cat.gl_company_desc gl_company_pivot_label, cat.bank_name || ' - ' || cat.bank_account_num || ' - ' || cat.bank_account_name || ' (' || cat.bank_account_currency || ')' bank_account_pivot_label from ( select catv.bank_account_num bank_account_num, catv.bank_account_name bank_account_name, catv.bank_currency_code bank_account_currency, nvl(xxen_util.meaning(cbagv.bank_account_type,'BANK_ACCOUNT_TYPE',260),cbagv.bank_account_type) bank_account_type, xep.name legal_entity, catv.bank_name bank_name, catv.bank_branch_name branch_name, catv.source source, catv.source_code source_code, decode( catv.clearing_trx_type , 'MISC' , 'CASH' , 'PAY' , 'PAYROLL' , 'PAY_EFT' , 'PAYROLL' , 'XTR_LINE', decode( catv.trx_type , 'PAYMENT', 'ROI-P' , 'CASH' , 'ROI-R' ) , 'ROI_LINE', decode( catv.trx_type , 'PAYMENT', 'ROI-P' , 'CASH' , 'ROI-R' ) , catv.clearing_trx_type ) type_code, decode( catv.trx_type , 'PAYMENT', catv.type_meaning , 'REFUND' , catv.type_meaning , 'CASH' , catv.type_meaning , 'MISC' , catv.type_meaning || ' ' || xxen_util.meaning('CASH','TRX_TYPE',260) ) type, trunc(catv.trx_date) effective_date, trunc(nvl(catv.maturity_date ,catv.trx_date)) maturity_date, decode( catv.clearing_trx_type , 'JE_LINE', null , catv.agent_name ) agent_name, decode( catv.clearing_trx_type , 'JE_LINE', null , catv.payment_method ) payment_method, decode( catv.clearing_trx_type , 'JE_LINE', null , catv.trx_number ) transaction_number, decode( catv.clearing_trx_type , 'JE_LINE', catv.batch_name ) journal_batch_name, decode( catv.clearing_trx_type , 'JE_LINE', catv.period_name ) journal_period, decode( catv.clearing_trx_type , 'JE_LINE', catv.journal_category ) journal_category, decode( catv.clearing_trx_type , 'JE_LINE', catv.journal_entry_line_number ) journal_line_number, decode( catv.clearing_trx_type , 'JE_LINE', catv.trx_number ) journal_descripton, decode( catv.clearing_trx_type , 'JE_LINE', fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_flex_bal_seg', 'SQLGL', 'GL#', gcc.chart_of_accounts_id, NULL, catv.code_combination_id, 'ALL', 'Y', 'VALUE') ) journal_account, null statement_number, decode( catv.clearing_trx_type , 'JE_LINE', catv.type_meaning ) line_type, null transaction_subtype, null direction, catv.currency_code currency_code, catv.bank_account_amount bank_account_amount, catv.amount amount, decode( catv.trx_type , 'PAYMENT', decode(catv.status, 'VOIDED', catv.bank_account_amount, -catv.bank_account_amount) , 'REFUND' , decode(catv.status, 'VOIDED', catv.bank_account_amount, -catv.bank_account_amount) , 'CASH' , decode(catv.status, 'REVERSED', -catv.bank_account_amount, catv.bank_account_amount) , 'MISC' , decode(catv.status, 'REVERSED', -catv.bank_account_amount, catv.bank_account_amount) ) account_amount, decode( catv.clearing_trx_type , 'CASH' , decode(catv.status, 'REVERSED', 20, 10) , 'MISC' , decode(catv.status, 'REVERSED', 20, 10) , 'PAYMENT' , decode(catv.status, 'VOIDED' , 40, 30) , 'PAY' , decode(catv.status, 'V' , 45, 35) , 'PAY_EFT' , decode(catv.status, 'V' , 45, 35) , 'REFUND' , decode(catv.status,'VOIDED' , 60, 50) , 'JE_LINE' , 70 , 'XTR_LINE', 85 , 'ROI_LINE', 85 ) transaction_order, catv.trx_id trx_id, haou.name organization, xep2.name treasury_legal_entity, -- -- GL Cash Account Details 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') gl_company_code, 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') gl_company_desc, 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') gl_account_code, 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') gl_account_desc, 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') gl_cost_center_code, 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') gl_cost_center_desc, 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') gl_cash_account, 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') gl_cash_account_desc from ce_avail_trx_v catv, ce_bank_accts_gt_v cbagv, ce_system_parameters csp, xle_entity_profiles xep, gl_code_combinations gcc, hr_all_organization_units haou, xle_entity_profiles xep2 where cbagv.bank_account_id = catv.bank_account_id and csp.legal_entity_id (+) = cbagv.account_owner_org_id and xep.legal_entity_id = cbagv.account_owner_org_id and gcc.code_combination_id = cbagv.asset_code_combination_id and haou.organization_id (+) = catv.org_id and xep2.legal_entity_id (+) = catv.legal_entity_id and catv.status != decode( nvl(csp.show_void_payment_flag, 'N') , 'N', decode(catv.clearing_trx_type,'PAY', 'V', 'PAY_EFT', 'V', 'VOIDED') , ' x' ) and catv.clearing_trx_type != 'STATEMENT' and nvl(catv.journal_category,'Y') != decode(catv.clearing_trx_type,'JE_LINE','Revaluation','X') and catv.trx_date >= csp.cashbook_begin_date and ( ( :p_type in ('PAYMENTS', 'RECEIPTS', 'AR_AND_AP','ALL') and haou.name = nvl(:p_operating_unit, haou.name) and catv.clearing_trx_type in ( 'PAYMENT', 'REFUND', 'CASH', 'MISC') ) or ( :p_type in ('XTR_LINES', 'ALL') and catv.legal_entity_id = csp.legal_entity_id and xep2.name = nvl(:p_legal_entity, xep2.name) and catv.clearing_trx_type = 'XTR_LINE' ) or ( :p_type in ( 'PAYROLLS', 'ALL') and catv.legal_entity_id is null and haou.name = nvl(:p_business_group, haou.name) and catv.clearing_trx_type in ( 'PAY', 'PAY_EFT') ) or ( :p_type in ('ROI_LINES', 'JE_LINES','ALL') and catv.org_id is null and catv.legal_entity_id is null and catv.clearing_trx_type in ( 'ROI_LINE', 'JE_LINE') ) ) and 1=1 and 2=2 union all select distinct catv.bank_account_num bank_account_num, catv.bank_account_name bank_account_name, catv.bank_currency_code bank_account_currency, nvl(xxen_util.meaning(cbagv.bank_account_type,'BANK_ACCOUNT_TYPE',260),cbagv.bank_account_type) bank_account_type, xep.name legal_entity, catv.bank_name bank_name, catv.bank_branch_name branch_name, xxen_util.meaning('STATEMENTS','TRAN_ORG_TYPE',260) source, 'STATEMENTS' source_code, catv.clearing_trx_type type_code, catv.type_meaning type, trunc(catv.trx_date) effective_date, trunc(catv.maturity_date) maturity_date, null agent_name, null payment_method, catv.trx_number transaction_number, null journal_batch_name, null journal_period, null journal_category, to_number(null) journal_line_number, null journal_descripton, null journal_account, csh.statement_number statement_number, catv.type_meaning line_type, null transaction_subtype, null direction, catv.currency_code currency_code, catv.bank_account_amount bank_account_amount, catv.amount amount, decode( catv.trx_type , 'DEBIT' , -catv.bank_account_amount , 'MISC_DEBIT', -catv.bank_account_amount , catv.bank_account_amount ) account_amount, decode( catv.clearing_trx_type , 'STATEMENT',75 ) transaction_order, catv.trx_id trx_id, null organization, null treasury_legal_entity, -- -- GL Cash Account Details 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') gl_company_code, 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') gl_company_desc, 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') gl_account_code, 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') gl_account_desc, 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') gl_cost_center_code, 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') gl_cost_center_desc, 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') gl_cash_account, 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') gl_cash_account_desc from ce_260_transactions_v catv, ce_bank_accts_gt_v cbagv, ce_statement_headers csh, xle_entity_profiles xep, gl_code_combinations gcc where cbagv.bank_account_id = catv.bank_account_id and catv.reference_id = csh.statement_header_id and xep.legal_entity_id = cbagv.account_owner_org_id and gcc.code_combination_id = cbagv.asset_code_combination_id and :p_type in ('STATEMENTS','ALL') and 1=1 and 2=2 union all select catv.bank_account_num bank_account_num, catv.bank_account_name bank_account_name, catv.bank_currency_code bank_account_currency, nvl(xxen_util.meaning(cbagv.bank_account_type,'BANK_ACCOUNT_TYPE',260),cbagv.bank_account_type) bank_account_type, xep.name legal_entity, catv.bank_name bank_name, catv.bank_branch_name branch_name, xxen_util.meaning('CASHFLOWS','TRAN_ORG_TYPE',260) source, 'CASHFLOWS' source_code, catv.clearing_trx_type type_code, catv.type_meaning type, trunc(nvl(catv.value_date ,catv.trx_date)) effective_date, trunc(catv.maturity_date) maturity_date, decode( catv.clearing_trx_type , 'CASHFLOW', catv.counterparty , catv.agent_name ) agent_name, null payment_method, null transaction_number, null journal_batch_name, null journal_period, null journal_category, to_number(null) journal_line_number, null journal_descripton, null journal_account, null statement_number, null line_type, catv.trxn_subtype transaction_subtype, c1.meaning direction, catv.currency_code currency_code, decode( catv.trx_type ,'PAYMENT', -catv.bank_account_amount , catv.bank_account_amount ) bank_account_amount, catv.amount amount, decode( catv.trx_type , 'PAYMENT', -catv.bank_account_amount , catv.bank_account_amount ) account_amount, decode(catv.clearing_trx_type ,'CASHFLOW', 90) transaction_order, catv.trx_id trx_id, null organization, xep2.name treasury_legal_entity, -- -- 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 from ce_260_cf_transactions_v catv, ce_bank_accts_gt_v cbagv, ce_system_parameters csp, xle_entity_profiles xep, gl_code_combinations gcc, xle_entity_profiles xep2, ce_lookups c1 where cbagv.bank_account_id = catv.bank_account_id and csp.legal_entity_id = catv.legal_entity_id and xep.legal_entity_id = cbagv.account_owner_org_id and gcc.code_combination_id (+) = cbagv.asset_code_combination_id and xep2.legal_entity_id = catv.legal_entity_id and c1.lookup_type = 'CE_CASHFLOW_DIRECTION' and c1.lookup_code = catv.trx_type and :p_type in ('CASHFLOWS','ALL') and xep2.name = nvl(:p_legal_entity,xep2.name) and 1=1 and 3=3 ) cat, fnd_currencies fc where fc.currency_code = cat.currency_code order by cat.bank_name, cat.bank_account_num, cat.transaction_order, cat.effective_date Y CE_CEXSTMRR_XMLP_PKG US CE Transactions Available for Reconciliation Application: Cash Management Description: Transactions - Transactions Available for Reconciliation Provides equivalent functionality to the following standard Oracle Forms/Reports - Transactions Available for Reconciliation Report Applicable Templates: Pivot: Transactions Available by Bank Account, Source and Type Source: Transactions Available for Reconciliation Report (CEXAVTRX) 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_business_group :p_conc_request_id :p_debug_mode :p_legal_entity :p_operating_unit :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 :p_type 1 10 1=1 catv.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 US Bank Name 2 20 1=1 catv.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 F Succursale bancaire US Bank Branch 3 30 1=1 catv.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 catv.bank_account_num=:p_account_num LOV CE Internal Bank Account Number (secured) F82893FE27E8AB4BE0530100007FB42A 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_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 :p_type LOV Oracle CE_TRAN_ORG_TYPE Y select lookup_code id, meaning value, description description from ce_lookups where lookup_type='TRAN_ORG_TYPE' order by value,description ALL Y F Type de transaction US Transaction Type 6 60 :p_legal_entity 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) F Trésorerie Entité légale US Treasury Legal Entity 7 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 F Groupe d'affaires Paie US Payroll Business Group 8 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 F Unité opérationnelle AR/AP US AR/AP Operating Unit 9 90 2=2 catv.trx_date>=:p_trx_date_from Date F Date d'entrée en vigueur Du US Effective Date From 10 3=3 nvl(catv.value_date,catv.trx_date)>=:p_trx_date_from F Date d'entrée en vigueur Du US Effective Date From 11 100 2=2 catv.trx_date<:p_trx_date_to+1 Date F Date d'entrée en vigueur Au US Effective Date To 12 3=3 nvl(catv.value_date,catv.trx_date)<:p_trx_date_to+1 F Date d'entrée en vigueur Au US Effective Date To :$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 Pivot: Transactions Available by Bank Account, Source and Type Pivot: Transactions Available by Bank Account, Source and Type SYSADMIN 12 AGENT_NAME 26 AMOUNT 29 AR_AP_OPERATING_UNIT 27 BANK_ACCOUNT_AMOUNT 4 BANK_ACCOUNT_CURRENCY 3 BANK_ACCOUNT_NAME 2 BANK_ACCOUNT_NUM 2 40 BANK_ACCOUNT_PIVOT_LABEL 5 BANK_NAME 1 6 BRANCH_NAME 25 CURRENCY_CODE 24 DIRECTION 10 EFFECTIVE_DATE 5 34 GL_ACCOUNT_CODE 35 GL_ACCOUNT_DESC 38 GL_CASH_ACCOUNT 39 GL_CASH_ACCOUNT_DESC 32 GL_COMPANY_CODE 33 GL_COMPANY_DESC 36 GL_COST_CENTER_CODE 37 GL_COST_CENTER_DESC 20 JOURNAL_ACCOUNT 15 JOURNAL_BATCH_NAME 17 JOURNAL_CATEGORY 19 JOURNAL_DESCRIPTON 18 JOURNAL_LINE_NUMBER 16 JOURNAL_PERIOD 1 LEGAL_ENTITY 23 LINE_TYPE 11 MATURITY_DATE 28 NET_AMOUNT_AVAILABLE 13 PAYMENT_METHOD 30 PAYROLL_BUSINESS_GROUP 7 SOURCE 3 21 STATEMENT_NUMBER 14 TRANSACTION_NUMBER 22 TRANSACTION_SUBTYPE 31 TREASURY_LEGAL_ENTITY 8 TYPE 9 TYPE_DESCRIPTION 4 BANK_ACCOUNT_AMOUNT VALUE 1 SUM 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 GL_COMPANY_DESC FILTER 2 LEGAL_ENTITY FILTER 1 NET_AMOUNT_AVAILABLE VALUE 2 SUM SOURCE ROW 2 TYPE_DESCRIPTION ROW 3 S Site Pivot: Transactions Available by Bank Account, Source and Type