CE_CP_INTERFACE_V

Details

• Schema: FUSION

• Object owner: CE

• Object type: VIEW

Columns

Name

CP_SYS_TRX_ID CP_STMT_HEADER_ID BANK LEGAL_ENTITY SOURCE_SYSTEM SOURCE FLOW_INDICATOR RECON_STATUS CURRENCY_TYPE CURRENCY TYPE FACT_DATE AMOUNT LAST_KNOWN_BAL_DATE EXT_DIM_ATTRIBUTE1 EXT_DIM_ATTRIBUTE2 EXT_DIM_ATTRIBUTE3 EXT_DIM_ATTRIBUTE4 EXT_DIM_ATTRIBUTE5 EXT_DIM_ATTRIBUTE6 EXT_DIM_ATTRIBUTE7 EXT_DIM_ATTRIBUTE8 EXT_DIM_ATTRIBUTE9 EXT_DIM_ATTRIBUTE10 EXT_DIM_ATTRIBUTE11 EXT_DIM_ATTRIBUTE12 EXT_DIM_ATTRIBUTE13 EXT_DIM_ATTRIBUTE14 EXT_DIM_ATTRIBUTE15 CASH_POOL

Query

SQL_Statement

WITH active_cash_pool_members AS ( SELECT CBA.bank_account_name, CPM.sub_bank_account_type_code FROM ce_cash_pool_members CPM, ce_cash_pools CP, ce_bank_accounts CBA WHERE CBA.bank_account_id = CPM.sub_bank_account_id AND CPM.cash_pool_id = CP.cash_pool_id AND CP.enabled_flag = 'Y' ), sub_query AS (SELECT cp_sys_trx_id, -1 AS cp_stmt_header_id, ti.bank_account_name, '[Bank].[' || NVL(ti.bank_name, 'NO_BANK_VALUE') || '].[' || NVL(ti.bank_account_name, 'NO_BANK_ACCOUNT_VALUE') || ']' AS bank, '[Legal Entity].[' || NVL(ti.legal_entity_region, 'NO_LE_REGION_VALUE') || '].[' || NVL(ti.legal_entity_name, 'NO_LEGAL_ENTITY_VALUE') || ']' AS legal_entity, '[Application].[' || NVL(ti.source_system, 'NO_SOURCE_SYSTEM_VALUE') || ']' AS source_system, '[Source].[System Transactions].[' || NVL(ti.source, 'NO_SOURCE_VALUE') || ']' AS source, '[Flow Indicator].[' || NVL(ti.flow_indicator, 'NO_FLOW_INDICATOR_VALUE') || ']' AS flow_indicator, '[Reconciliation Status].[' || NVL(ti.recon_status, 'NO_RECON_STATUS_VALUE') || ']' AS recon_status, '[Currency Type].[Transaction Currency]' AS currency_type, '[Currency].[' || ti.TRANSACTION_CURRENCY_CODE || ']' AS currency, '[Type].[Transaction Type].[' || NVL(ti.transaction_type, 'NO_TRANSACTION_TYPE_VALUE') || ']' AS type, TO_CHAR(ti.transaction_date, 'MM/DD/YYYY') AS fact_DATE, ti.transaction_amount AS amount, null AS LAST_KNOWN_BAL_DATE, NVL(ti.EXT_DIM_ATTRIBUTE1, 'NO_VALUE') AS EXT_DIM_attribute1, NVL(ti.EXT_DIM_ATTRIBUTE2, 'NO_VALUE') AS EXT_DIM_attribute2, NVL(ti.EXT_DIM_ATTRIBUTE3, 'NO_VALUE') AS EXT_DIM_attribute3, NVL(ti.EXT_DIM_ATTRIBUTE4, 'NO_VALUE') AS EXT_DIM_attribute4, NVL(ti.EXT_DIM_ATTRIBUTE5, 'NO_VALUE') AS EXT_DIM_attribute5, NVL(ti.EXT_DIM_ATTRIBUTE6, 'NO_VALUE') AS EXT_DIM_attribute6, NVL(ti.EXT_DIM_ATTRIBUTE7, 'NO_VALUE') AS EXT_DIM_attribute7, NVL(ti.EXT_DIM_ATTRIBUTE8, 'NO_VALUE') AS EXT_DIM_attribute8, NVL(ti.EXT_DIM_ATTRIBUTE9, 'NO_VALUE') AS EXT_DIM_attribute9, NVL(ti.EXT_DIM_ATTRIBUTE10, 'NO_VALUE') AS EXT_DIM_attribute10, NVL(ti.EXT_DIM_ATTRIBUTE11, 'NO_VALUE') AS EXT_DIM_attribute11, NVL(ti.EXT_DIM_ATTRIBUTE12, 'NO_VALUE') AS EXT_DIM_attribute12, NVL(ti.EXT_DIM_ATTRIBUTE13, 'NO_VALUE') AS EXT_DIM_attribute13, NVL(ti.EXT_DIM_ATTRIBUTE14, 'NO_VALUE') AS EXT_DIM_attribute14, NVL(ti.EXT_DIM_ATTRIBUTE15, 'NO_VALUE') AS EXT_DIM_attribute15, ti.CASH_POOL_NAME AS CASH_POOL, ti.PARENT_CASH_POOL_NAME AS PARENT_CASH_POOL FROM ce_cp_sys_trx_int ti WHERE nvl(ti.error_flag,'N') != 'Y' and ti.transaction_date is not null and cp_sys_trx_id not in (select cp_sys_trx_id from ce_cp_facts fac, ce_cp_sys_trx_int sys where fac.source = sys.source and fac.source_system =sys.source_system and fac.src_id = sys.source_transaction_id and exclude_flag = 'Y') UNION ALL SELECT cp_sys_trx_id, -1 AS cp_stmt_header_id, ti.bank_account_name, '[Bank].[' || NVL(ti.bank_name, 'NO_BANK_VALUE') || '].[' || NVL(ti.bank_account_name, 'NO_BANK_ACCOUNT_VALUE') || ']' AS bank, '[Legal Entity].[' || NVL(ti.legal_entity_region, 'NO_LE_REGION_VALUE') || '].[' || NVL(ti.legal_entity_name, 'NO_LEGAL_ENTITY_VALUE') || ']' AS legal_entity, '[Application].[' || NVL(ti.source_system, 'NO_SOURCE_SYSTEM_VALUE') || ']' AS source_system, '[Source].[System Transactions].[' || NVL(ti.source, 'NO_SOURCE_VALUE') || ']' AS source, '[Flow Indicator].[' || NVL(ti.flow_indicator, 'NO_FLOW_INDICATOR_VALUE') || ']' AS flow_indicator, '[Reconciliation Status].[' || NVL(ti.recon_status, 'NO_RECON_STATUS_VALUE') || ']' AS recon_status, '[Currency Type].[Ledger Currency]' AS currency_type, '[Currency].[' || ti.functional_currency_code || ']' AS currency, '[Type].[Transaction Type].[' || NVL(ti.transaction_type, 'NO_TRANSACTION_TYPE_VALUE') || ']' AS type, TO_CHAR(ti.transaction_date, 'MM/DD/YYYY') AS fact_DATE, ti.functional_amount AS amount, null AS LAST_KNOWN_BAL_DATE, NVL(ti.EXT_DIM_ATTRIBUTE1, 'NO_VALUE') AS EXT_DIM_attribute1, NVL(ti.EXT_DIM_ATTRIBUTE2, 'NO_VALUE') AS EXT_DIM_attribute2, NVL(ti.EXT_DIM_ATTRIBUTE3, 'NO_VALUE') AS EXT_DIM_attribute3, NVL(ti.EXT_DIM_ATTRIBUTE4, 'NO_VALUE') AS EXT_DIM_attribute4, NVL(ti.EXT_DIM_ATTRIBUTE5, 'NO_VALUE') AS EXT_DIM_attribute5, NVL(ti.EXT_DIM_ATTRIBUTE6, 'NO_VALUE') AS EXT_DIM_attribute6, NVL(ti.EXT_DIM_ATTRIBUTE7, 'NO_VALUE') AS EXT_DIM_attribute7, NVL(ti.EXT_DIM_ATTRIBUTE8, 'NO_VALUE') AS EXT_DIM_attribute8, NVL(ti.EXT_DIM_ATTRIBUTE9, 'NO_VALUE') AS EXT_DIM_attribute9, NVL(ti.EXT_DIM_ATTRIBUTE10, 'NO_VALUE') AS EXT_DIM_attribute10, NVL(ti.EXT_DIM_ATTRIBUTE11, 'NO_VALUE') AS EXT_DIM_attribute11, NVL(ti.EXT_DIM_ATTRIBUTE12, 'NO_VALUE') AS EXT_DIM_attribute12, NVL(ti.EXT_DIM_ATTRIBUTE13, 'NO_VALUE') AS EXT_DIM_attribute13, NVL(ti.EXT_DIM_ATTRIBUTE14, 'NO_VALUE') AS EXT_DIM_attribute14, NVL(ti.EXT_DIM_ATTRIBUTE15, 'NO_VALUE') AS EXT_DIM_attribute15, ti.CASH_POOL_NAME AS CASH_POOL, ti.PARENT_CASH_POOL_NAME AS PARENT_CASH_POOL FROM ce_cp_sys_trx_int ti WHERE nvl(ti.error_flag,'N') != 'Y' and ti.transaction_date is not null and cp_sys_trx_id not in (select cp_sys_trx_id from ce_cp_facts fac, ce_cp_sys_trx_int sys where fac.source = sys.source and fac.source_system =sys.source_system and fac.src_id = sys.source_transaction_id and exclude_flag = 'Y') UNION ALL SELECT cp_sys_trx_id, -1 AS cp_stmt_header_id, ti.bank_account_name, '[Bank].[' || NVL(ti.bank_name, 'NO_BANK_VALUE') || '].[' || NVL(ti.bank_account_name, 'NO_BANK_ACCOUNT_VALUE') || ']' AS bank, '[Legal Entity].[' || NVL(ti.legal_entity_region, 'NO_LE_REGION_VALUE') || '].[' || NVL(ti.legal_entity_name, 'NO_LEGAL_ENTITY_VALUE') || ']' AS legal_entity, '[Application].[' || NVL(ti.source_system, 'NO_SOURCE_SYSTEM_VALUE') || ']' AS source_system, '[Source].[System Transactions].[' || NVL(ti.source, 'NO_SOURCE_VALUE') || ']' AS source, '[Flow Indicator].[' || NVL(ti.flow_indicator, 'NO_FLOW_INDICATOR_VALUE') || ']' AS flow_indicator, '[Reconciliation Status].[' || NVL(ti.recon_status, 'NO_RECON_STATUS_VALUE') || ']' AS recon_status, '[Currency Type].[Account Currency]' AS currency_type, '[Currency].[' || ti.bank_acct_currency_code || ']' AS currency, '[Type].[Transaction Type].[' || NVL(ti.transaction_type, 'NO_TRANSACTION_TYPE_VALUE') || ']' AS type, TO_CHAR(ti.transaction_date, 'MM/DD/YYYY') AS fact_DATE, ti.bank_acct_amount AS amount, null AS LAST_KNOWN_BAL_DATE, NVL(ti.EXT_DIM_ATTRIBUTE1, 'NO_VALUE') AS EXT_DIM_attribute1, NVL(ti.EXT_DIM_ATTRIBUTE2, 'NO_VALUE') AS EXT_DIM_attribute2, NVL(ti.EXT_DIM_ATTRIBUTE3, 'NO_VALUE') AS EXT_DIM_attribute3, NVL(ti.EXT_DIM_ATTRIBUTE4, 'NO_VALUE') AS EXT_DIM_attribute4, NVL(ti.EXT_DIM_ATTRIBUTE5, 'NO_VALUE') AS EXT_DIM_attribute5, NVL(ti.EXT_DIM_ATTRIBUTE6, 'NO_VALUE') AS EXT_DIM_attribute6, NVL(ti.EXT_DIM_ATTRIBUTE7, 'NO_VALUE') AS EXT_DIM_attribute7, NVL(ti.EXT_DIM_ATTRIBUTE8, 'NO_VALUE') AS EXT_DIM_attribute8, NVL(ti.EXT_DIM_ATTRIBUTE9, 'NO_VALUE') AS EXT_DIM_attribute9, NVL(ti.EXT_DIM_ATTRIBUTE10, 'NO_VALUE') AS EXT_DIM_attribute10, NVL(ti.EXT_DIM_ATTRIBUTE11, 'NO_VALUE') AS EXT_DIM_attribute11, NVL(ti.EXT_DIM_ATTRIBUTE12, 'NO_VALUE') AS EXT_DIM_attribute12, NVL(ti.EXT_DIM_ATTRIBUTE13, 'NO_VALUE') AS EXT_DIM_attribute13, NVL(ti.EXT_DIM_ATTRIBUTE14, 'NO_VALUE') AS EXT_DIM_attribute14, NVL(ti.EXT_DIM_ATTRIBUTE15, 'NO_VALUE') AS EXT_DIM_attribute15, ti.CASH_POOL_NAME AS CASH_POOL, ti.PARENT_CASH_POOL_NAME AS PARENT_CASH_POOL FROM ce_cp_sys_trx_int ti WHERE nvl(ti.error_flag,'N') != 'Y' and ti.transaction_date is not null and cp_sys_trx_id not in (select cp_sys_trx_id from ce_cp_facts fac, ce_cp_sys_trx_int sys where fac.source = sys.source and fac.source_system =sys.source_system and fac.src_id = sys.source_transaction_id and exclude_flag = 'Y') UNION ALL SELECT cp_sys_trx_id, -1 AS cp_stmt_header_id, ti.bank_account_name, '[Bank].[' || NVL(ti.bank_name, 'NO_BANK_VALUE') || '].[' || NVL(ti.bank_account_name, 'NO_BANK_ACCOUNT_VALUE') || ']' AS bank, '[Legal Entity].[' || NVL(ti.legal_entity_region, 'NO_LE_REGION_VALUE') || '].[' || NVL(ti.legal_entity_name, 'NO_LEGAL_ENTITY_VALUE') || ']' AS legal_entity, '[Application].[' || NVL(ti.source_system, 'NO_SOURCE_SYSTEM_VALUE') || ']' AS source_system, '[Source].[System Transactions].[' || NVL(ti.source, 'NO_SOURCE_VALUE') || ']' AS source, '[Flow Indicator].[' || NVL(ti.flow_indicator, 'NO_FLOW_INDICATOR_VALUE') || ']' AS flow_indicator, '[Reconciliation Status].[' || NVL(ti.recon_status, 'NO_RECON_STATUS_VALUE') || ']' AS recon_status, '[Currency Type].[Reporting Currency]' AS currency_type, '[Currency].[' || nvl((select REPORTING_CURRENCY from CE_CP_OPTIONS WHERE PCF_SETUP_FLAG = 'N'),'USD') || ']' AS currency, '[Type].[Transaction Type].[' || NVL(ti.transaction_type, 'NO_TRANSACTION_TYPE_VALUE') || ']' AS type, TO_CHAR(ti.transaction_date, 'MM/DD/YYYY') AS fact_DATE, ti.reporting_amount AS amount, null AS LAST_KNOWN_BAL_DATE, NVL(ti.EXT_DIM_ATTRIBUTE1, 'NO_VALUE') AS EXT_DIM_attribute1, NVL(ti.EXT_DIM_ATTRIBUTE2, 'NO_VALUE') AS EXT_DIM_attribute2, NVL(ti.EXT_DIM_ATTRIBUTE3, 'NO_VALUE') AS EXT_DIM_attribute3, NVL(ti.EXT_DIM_ATTRIBUTE4, 'NO_VALUE') AS EXT_DIM_attribute4, NVL(ti.EXT_DIM_ATTRIBUTE5, 'NO_VALUE') AS EXT_DIM_attribute5, NVL(ti.EXT_DIM_ATTRIBUTE6, 'NO_VALUE') AS EXT_DIM_attribute6, NVL(ti.EXT_DIM_ATTRIBUTE7, 'NO_VALUE') AS EXT_DIM_attribute7, NVL(ti.EXT_DIM_ATTRIBUTE8, 'NO_VALUE') AS EXT_DIM_attribute8, NVL(ti.EXT_DIM_ATTRIBUTE9, 'NO_VALUE') AS EXT_DIM_attribute9, NVL(ti.EXT_DIM_ATTRIBUTE10, 'NO_VALUE') AS EXT_DIM_attribute10, NVL(ti.EXT_DIM_ATTRIBUTE11, 'NO_VALUE') AS EXT_DIM_attribute11, NVL(ti.EXT_DIM_ATTRIBUTE12, 'NO_VALUE') AS EXT_DIM_attribute12, NVL(ti.EXT_DIM_ATTRIBUTE13, 'NO_VALUE') AS EXT_DIM_attribute13, NVL(ti.EXT_DIM_ATTRIBUTE14, 'NO_VALUE') AS EXT_DIM_attribute14, NVL(ti.EXT_DIM_ATTRIBUTE15, 'NO_VALUE') AS EXT_DIM_attribute15, ti.CASH_POOL_NAME AS CASH_POOL, ti.PARENT_CASH_POOL_NAME AS PARENT_CASH_POOL FROM ce_cp_sys_trx_int ti WHERE nvl(ti.error_flag,'N') != 'Y' and ti.transaction_date is not null and cp_sys_trx_id not in (select cp_sys_trx_id from ce_cp_facts fac, ce_cp_sys_trx_int sys where fac.source = sys.source and fac.source_system =sys.source_system and fac.src_id = sys.source_transaction_id and exclude_flag = 'Y') UNION ALL SELECT -1 AS cp_sys_trx_id, stmts.cp_stmt_header_id, details.bank_account_name, '[Bank].[' || NVL(details.bank_name, 'NO_BANK_VALUE') || '].[' || NVL(details.bank_account_name, 'NO_BANK_ACCOUNT_VALUE') || ']' AS bank, '[Legal Entity].[' || NVL(details.legal_entity_region, 'NO_LE_REGION_VALUE') || '].[' || NVL(details.legal_entity_name, 'NO_LEGAL_ENTITY_VALUE') || ']' AS legal_entity, '[Application].[' || NVL(details.source_system, 'NO_SOURCE_SYSTEM_VALUE') || ']' AS source_system, '[Source].[Bank Statements].[' || NVL(details.source, 'NO_SOURCE_VALUE') || ']' AS source, '[Flow Indicator].[' || NVL(details.flow_indicator, 'NO_FLOW_INDICATOR_VALUE') || ']' AS flow_indicator, '[Reconciliation Status].[Not Applicable]' AS recon_status, '[Currency Type].[' || stmts.currency_type || ']' AS currency_type, '[Currency].[' || NVL(stmts.currency, 'NO_CURR_VALUE') || ']' AS currency, '[Type].[Balance Type].[' || stmts.balance_type || ']' AS type, stmts.stmt_date AS FACT_DATE, amount, null as LAST_KNOWN_BAL_DATE, NVL(details.EXT_DIM_ATTRIBUTE1, 'NO_VALUE') AS EXT_DIM_attribute1, NVL(details.EXT_DIM_ATTRIBUTE2, 'NO_VALUE') AS EXT_DIM_attribute2, NVL(details.EXT_DIM_ATTRIBUTE3, 'NO_VALUE') AS EXT_DIM_attribute3, NVL(details.EXT_DIM_ATTRIBUTE4, 'NO_VALUE') AS EXT_DIM_attribute4, NVL(details.EXT_DIM_ATTRIBUTE5, 'NO_VALUE') AS EXT_DIM_attribute5, NVL(details.EXT_DIM_ATTRIBUTE6, 'NO_VALUE') AS EXT_DIM_attribute6, NVL(details.EXT_DIM_ATTRIBUTE7, 'NO_VALUE') AS EXT_DIM_attribute7, NVL(details.EXT_DIM_ATTRIBUTE8, 'NO_VALUE') AS EXT_DIM_attribute8, NVL(details.EXT_DIM_ATTRIBUTE9, 'NO_VALUE') AS EXT_DIM_attribute9, NVL(details.EXT_DIM_ATTRIBUTE10, 'NO_VALUE') AS EXT_DIM_attribute10, NVL(details.EXT_DIM_ATTRIBUTE11, 'NO_VALUE') AS EXT_DIM_attribute11, NVL(details.EXT_DIM_ATTRIBUTE12, 'NO_VALUE') AS EXT_DIM_attribute12, NVL(details.EXT_DIM_ATTRIBUTE13, 'NO_VALUE') AS EXT_DIM_attribute13, NVL(details.EXT_DIM_ATTRIBUTE14, 'NO_VALUE') AS EXT_DIM_attribute14, NVL(details.EXT_DIM_ATTRIBUTE15, 'NO_VALUE') AS EXT_DIM_attribute15, details.CASH_POOL_NAME AS CASH_POOL, details.PARENT_CASH_POOL_NAME AS PARENT_CASH_POOL FROM ( SELECT cp_stmt_header_id, bank_acct_currency_code AS currency, 'Account Currency' AS currency_type, 'Opening Booked Balance' AS balance_type, TO_CHAR(Open_Ledger_Bal_Date, 'MM/DD/YYYY') AS stmt_DATE, bank_acct_open_ledger_bal AS amount FROM ce_cp_stmt_headers_int stmt_int UNION ALL SELECT cp_stmt_header_id, bank_acct_currency_code AS currency, 'Account Currency' AS currency_type, 'Opening Available Balance' AS balance_type, TO_CHAR(Open_Available_Bal_Date, 'MM/DD/YYYY') AS stmt_DATE, bank_acct_open_available_bal AS amount FROM ce_cp_stmt_headers_int stmt_int UNION ALL SELECT cp_stmt_header_id, bank_acct_currency_code AS currency, 'Account Currency' AS currency_type, 'Opening 1 Day Float' AS balance_type, TO_CHAR(Open_Ledger_Bal_Date, 'MM/DD/YYYY') AS stmt_DATE, bank_acct_1day_open_float AS amount FROM ce_cp_stmt_headers_int stmt_int UNION ALL SELECT cp_stmt_header_id, bank_acct_currency_code AS currency, 'Account Currency' AS currency_type, 'Opening 2 Day Float' AS balance_type, TO_CHAR(Open_Ledger_Bal_Date, 'MM/DD/YYYY') AS stmt_DATE, bank_acct_2day_open_float AS amount FROM ce_cp_stmt_headers_int stmt_int UNION ALL SELECT cp_stmt_header_id, bank_acct_currency_code AS currency, 'Account Currency' AS currency_type, 'Closing Booked Balance' AS balance_type, TO_CHAR(Close_Ledger_bal_date, 'MM/DD/YYYY') AS stmt_DATE, bank_acct_close_ledger_bal AS amount FROM ce_cp_stmt_headers_int stmt_int UNION ALL SELECT cp_stmt_header_id, bank_acct_currency_code AS currency, 'Account Currency' AS currency_type, 'Closing Available Balance' AS balance_type, TO_CHAR(close_available_bal_date, 'MM/DD/YYYY') AS stmt_DATE, bank_acct_close_available_bal AS amount FROM ce_cp_stmt_headers_int stmt_int UNION ALL SELECT cp_stmt_header_id, bank_acct_currency_code AS currency, 'Account Currency' AS currency_type, 'Closing 1 Day Float' AS balance_type, TO_CHAR(close_ledger_bal_date, 'MM/DD/YYYY') AS stmt_DATE, bank_acct_1day_close_float AS amount FROM ce_cp_stmt_headers_int stmt_int UNION ALL SELECT cp_stmt_header_id, bank_acct_currency_code AS currency, 'Account Currency' AS currency_type, 'Closing 2 Day Float' AS balance_type, TO_CHAR(close_ledger_bal_date, 'MM/DD/YYYY') AS stmt_DATE, bank_acct_2day_close_float AS amount FROM ce_cp_stmt_headers_int stmt_int UNION ALL SELECT cp_stmt_header_id, functional_currency_code AS currency, 'Ledger Currency' AS currency_type, 'Opening Booked Balance' AS balance_type, TO_CHAR(Open_ledger_Bal_Date, 'MM/DD/YYYY') AS stmt_DATE, functional_open_ledger_bal AS amount FROM ce_cp_stmt_headers_int stmt_int UNION ALL SELECT cp_stmt_header_id, functional_currency_code AS currency, 'Ledger Currency' AS currency_type, 'Opening Available Balance' AS balance_type, TO_CHAR(Open_available_Bal_Date, 'MM/DD/YYYY') AS stmt_DATE, functional_open_available_bal AS amount FROM ce_cp_stmt_headers_int stmt_int UNION ALL SELECT cp_stmt_header_id, functional_currency_code AS currency, 'Ledger Currency' AS currency_type, 'Opening 1 Day Float' AS balance_type, TO_CHAR(Open_ledger_Bal_Date, 'MM/DD/YYYY') AS stmt_DATE, functional_1day_open_float AS amount FROM ce_cp_stmt_headers_int stmt_int UNION ALL SELECT cp_stmt_header_id, functional_currency_code AS currency, 'Ledger Currency' AS currency_type, 'Opening 2 Day Float' AS balance_type, TO_CHAR(Open_ledger_Bal_Date, 'MM/DD/YYYY') AS stmt_DATE, functional_2day_open_float AS amount FROM ce_cp_stmt_headers_int stmt_int UNION ALL SELECT cp_stmt_header_id, functional_currency_code AS currency, 'Ledger Currency' AS currency_type, 'Closing Booked Balance' AS balance_type, TO_CHAR(close_ledger_bal_date, 'MM/DD/YYYY') AS stmt_DATE, functional_close_ledger_bal AS amount FROM ce_cp_stmt_headers_int stmt_int UNION ALL SELECT cp_stmt_header_id, functional_currency_code AS currency, 'Ledger Currency' AS currency_type, 'Closing Available Balance' AS balance_type, TO_CHAR(close_available_bal_date, 'MM/DD/YYYY') AS stmt_DATE, functional_close_available_bal AS amount FROM ce_cp_stmt_headers_int stmt_int UNION ALL SELECT cp_stmt_header_id, functional_currency_code AS currency, 'Ledger Currency' AS currency_type, 'Closing 1 Day Float' AS balance_type, TO_CHAR(close_ledger_bal_date, 'MM/DD/YYYY') AS stmt_DATE, functional_1day_close_float AS amount FROM ce_cp_stmt_headers_int stmt_int UNION ALL SELECT cp_stmt_header_id, functional_currency_code AS currency, 'Ledger Currency' AS currency_type, 'Closing 2 Day Float' AS balance_type, TO_CHAR(close_ledger_bal_date, 'MM/DD/YYYY') AS stmt_DATE, functional_2day_close_float AS amount FROM ce_cp_stmt_headers_int stmt_int UNION ALL SELECT cp_stmt_header_id, nvl((select REPORTING_CURRENCY from CE_CP_OPTIONS WHERE PCF_SETUP_FLAG = 'N'),'USD') AS currency, 'Reporting Currency' AS currency_type, 'Opening Booked Balance' AS balance_type, TO_CHAR(Open_ledger_Bal_Date, 'MM/DD/YYYY') AS stmt_DATE, reporting_open_ledger_bal AS amount FROM ce_cp_stmt_headers_int stmt_int UNION ALL SELECT cp_stmt_header_id, nvl((select REPORTING_CURRENCY from CE_CP_OPTIONS WHERE PCF_SETUP_FLAG = 'N'),'USD') AS currency, 'Reporting Currency' AS currency_type, 'Opening Available Balance' AS balance_type, TO_CHAR(Open_available_Bal_Date, 'MM/DD/YYYY') AS stmt_DATE, reporting_open_available_bal AS amount FROM ce_cp_stmt_headers_int stmt_int UNION ALL SELECT cp_stmt_header_id, nvl((select REPORTING_CURRENCY from CE_CP_OPTIONS WHERE PCF_SETUP_FLAG = 'N'),'USD') AS currency, 'Reporting Currency' AS currency_type, 'Opening 1 Day Float' AS balance_type, TO_CHAR(Open_ledger_Bal_Date, 'MM/DD/YYYY') AS stmt_DATE, reporting_1day_open_float AS amount FROM ce_cp_stmt_headers_int stmt_int UNION ALL SELECT cp_stmt_header_id, nvl((select REPORTING_CURRENCY from CE_CP_OPTIONS WHERE PCF_SETUP_FLAG = 'N'),'USD') AS currency, 'Reporting Currency' AS currency_type, 'Opening 2 Day Float' AS balance_type, TO_CHAR(Open_ledger_Bal_Date, 'MM/DD/YYYY') AS stmt_DATE, reporting_2day_open_float AS amount FROM ce_cp_stmt_headers_int stmt_int UNION ALL SELECT cp_stmt_header_id, nvl((select REPORTING_CURRENCY from CE_CP_OPTIONS WHERE PCF_SETUP_FLAG = 'N'),'USD') AS currency, 'Reporting Currency' AS currency_type, 'Closing Booked Balance' AS balance_type, TO_CHAR(close_ledger_bal_date, 'MM/DD/YYYY') AS stmt_DATE, reporting_close_ledger_bal AS amount FROM ce_cp_stmt_headers_int stmt_int UNION ALL SELECT cp_stmt_header_id, nvl((select REPORTING_CURRENCY from CE_CP_OPTIONS WHERE PCF_SETUP_FLAG = 'N'),'USD') AS currency, 'Reporting Currency' AS currency_type, 'Closing Available Balance' AS balance_type, TO_CHAR(close_available_bal_date, 'MM/DD/YYYY') AS stmt_DATE, reporting_close_available_bal AS amount FROM ce_cp_stmt_headers_int stmt_int UNION ALL SELECT cp_stmt_header_id, nvl((select REPORTING_CURRENCY from CE_CP_OPTIONS WHERE PCF_SETUP_FLAG = 'N'),'USD') AS currency, 'Reporting Currency' AS currency_type, 'Closing 1 Day Float' AS balance_type, TO_CHAR(close_ledger_bal_date, 'MM/DD/YYYY') AS stmt_DATE, reporting_1day_close_float AS amount FROM ce_cp_stmt_headers_int stmt_int UNION ALL SELECT cp_stmt_header_id, nvl((select REPORTING_CURRENCY from CE_CP_OPTIONS WHERE PCF_SETUP_FLAG = 'N'),'USD') AS currency, 'Reporting Currency' AS currency_type, 'Closing 2 Day Float' AS balance_type, TO_CHAR(close_ledger_bal_date, 'MM/DD/YYYY') AS stmt_DATE, reporting_2day_close_float AS amount FROM ce_cp_stmt_headers_int stmt_int) stmts, ce_cp_stmt_headers_int details WHERE stmts.cp_stmt_header_id = details.cp_stmt_header_id and stmts.stmt_date is not null and stmts.cp_stmt_header_id not in (select cp_stmt_header_id from ce_cp_stmt_headers_int h, ce_cp_facts f where h.source_stmt_header_id = f.src_id and h.source_system = f.source_system and h.source = f.source and nvl(exclude_flag,'N') = 'Y') AND nvl(details.error_flag,'N') != 'Y' UNION ALL SELECT -1 AS cp_sys_trx_id, t.cp_stmt_header_id, t.bank_account_name, '[Bank].[' || NVL(t.bank_name, 'NO_BANK_VALUE') || '].[' || NVL(t.bank_account_name, 'NO_BANK_ACCOUNT_VALUE') || ']' AS bank, '[Legal Entity].[' || NVL(t.legal_entity_region, 'NO_LE_REGION_VALUE') || '].[' || NVL(t.legal_entity_name, 'NO_LEGAL_ENTITY_VALUE') || ']' AS legal_entity, '[Application].[' || NVL(t.source_system, 'NO_SOURCE_SYSTEM_VALUE') || ']' AS source_system, '[Source].[Bank Statements].[' || NVL(t.source, 'NO_SOURCE_VALUE') || ']' AS source, '[Flow Indicator].[' || NVL(t.flow_indicator, 'NO_FLOW_INDICATOR_VALUE') || ']' AS flow_indicator, '[Reconciliation Status].[Not Applicable]' AS recon_status, '[Currency Type].[' || stmts.currency_type || ']' AS currency_type, '[Currency].[' || NVL(stmts.currency, 'NO_CURR_VALUE') || ']' AS currency, '[Type].[Balance Type].[' || stmts.balance_type || ']' AS type, stmts.stmt_date AS FACT_DATE, stmts.amount, stmts.LAST_KNOWN_BAL_DATE, NVL(t.EXT_DIM_ATTRIBUTE1, 'NO_VALUE') AS EXT_DIM_attribute1, NVL(t.EXT_DIM_ATTRIBUTE2, 'NO_VALUE') AS EXT_DIM_attribute2, NVL(t.EXT_DIM_ATTRIBUTE3, 'NO_VALUE') AS EXT_DIM_attribute3, NVL(t.EXT_DIM_ATTRIBUTE4, 'NO_VALUE') AS EXT_DIM_attribute4, NVL(t.EXT_DIM_ATTRIBUTE5, 'NO_VALUE') AS EXT_DIM_attribute5, NVL(t.EXT_DIM_ATTRIBUTE6, 'NO_VALUE') AS EXT_DIM_attribute6, NVL(t.EXT_DIM_ATTRIBUTE7, 'NO_VALUE') AS EXT_DIM_attribute7, NVL(t.EXT_DIM_ATTRIBUTE8, 'NO_VALUE') AS EXT_DIM_attribute8, NVL(t.EXT_DIM_ATTRIBUTE9, 'NO_VALUE') AS EXT_DIM_attribute9, NVL(t.EXT_DIM_ATTRIBUTE10, 'NO_VALUE') AS EXT_DIM_attribute10, NVL(t.EXT_DIM_ATTRIBUTE11, 'NO_VALUE') AS EXT_DIM_attribute11, NVL(t.EXT_DIM_ATTRIBUTE12, 'NO_VALUE') AS EXT_DIM_attribute12, NVL(t.EXT_DIM_ATTRIBUTE13, 'NO_VALUE') AS EXT_DIM_attribute13, NVL(t.EXT_DIM_ATTRIBUTE14, 'NO_VALUE') AS EXT_DIM_attribute14, NVL(t.EXT_DIM_ATTRIBUTE15, 'NO_VALUE') AS EXT_DIM_attribute15, t.CASH_POOL_NAME AS CASH_POOL, t.PARENT_CASH_POOL_NAME AS PARENT_CASH_POOL FROM ce_cp_stmt_headers_int t, (SELECT cp_stmt_header_id, bank_acct_currency_code AS currency, 'Account Currency' AS currency_type, 'Balance' AS balance_type, 'Last Known' AS stmt_DATE, bank_acct_bal AS amount, null as last_known_bal_date FROM ce_cp_stmt_headers_int stmt_int UNION ALL SELECT cp_stmt_header_id, functional_currency_code AS currency, 'Ledger Currency' AS currency_type, 'Balance' AS balance_type, 'Last Known' AS stmt_DATE, functional_bal AS amount, null as last_known_bal_date FROM ce_cp_stmt_headers_int stmt_int UNION ALL SELECT cp_stmt_header_id, nvl((select REPORTING_CURRENCY from CE_CP_OPTIONS WHERE PCF_SETUP_FLAG = 'N'),'USD') AS currency, 'Reporting Currency' AS currency_type, 'Balance' AS balance_type, 'Last Known' AS stmt_DATE, reporting_bal AS amount, null as last_known_bal_date FROM ce_cp_stmt_headers_int stmt_int UNION ALL SELECT cp_stmt_header_id, bank_acct_currency_code AS currency, 'Account Currency' AS currency_type, 'Target Balance' AS balance_type, 'Last Known' AS stmt_DATE, bank_acct_target_bal AS amount, null as last_known_bal_date FROM ce_cp_stmt_headers_int stmt_int UNION ALL SELECT cp_stmt_header_id, functional_currency_code AS currency, 'Ledger Currency' AS currency_type, 'Target Balance' AS balance_type, 'Last Known' AS stmt_DATE, functional_target_bal AS amount, null as last_known_bal_date FROM ce_cp_stmt_headers_int stmt_int UNION ALL SELECT cp_stmt_header_id, nvl((select REPORTING_CURRENCY from CE_CP_OPTIONS WHERE PCF_SETUP_FLAG = 'N'),'USD') AS currency, 'Reporting Currency' AS currency_type, 'Target Balance' AS balance_type, 'Last Known' AS stmt_DATE, reporting_target_bal AS amount, null as last_known_bal_date FROM ce_cp_stmt_headers_int stmt_int UNION ALL SELECT cp_stmt_header_id, bank_acct_currency_code AS currency, 'Account Currency' AS currency_type, 'Balance Date' AS balance_type, 'Last Known' AS stmt_DATE, null AS amount, balance_date AS last_known_bal_date FROM ce_cp_stmt_headers_int stmt_int UNION ALL SELECT cp_stmt_header_id, functional_currency_code AS currency, 'Ledger Currency' AS currency_type, 'Balance Date' AS balance_type, 'Last Known' AS stmt_DATE, null AS amount, balance_date as last_known_bal_date FROM ce_cp_stmt_headers_int stmt_int UNION ALL SELECT cp_stmt_header_id, nvl((select REPORTING_CURRENCY from CE_CP_OPTIONS WHERE PCF_SETUP_FLAG = 'N'),'USD') AS currency, 'Reporting Currency' AS currency_type, 'Balance Date' AS balance_type, 'Last Known' AS stmt_DATE, null AS amount, balance_date AS last_known_bal_date FROM ce_cp_stmt_headers_int stmt_int UNION ALL SELECT cp_stmt_header_id, bank_acct_currency_code AS currency, 'Account Currency' AS currency_type, 'Opening Booked Balance' AS balance_type, 'Last Known' AS stmt_DATE, BANK_ACCT_OPEN_LEDGER_BAL AS amount, null AS last_known_bal_date FROM ce_cp_stmt_headers_int stmt_int UNION ALL SELECT cp_stmt_header_id, functional_currency_code AS currency, 'Ledger Currency' AS currency_type, 'Opening Booked Balance' AS balance_type, 'Last Known' AS stmt_DATE, FUNCTIONAL_OPEN_LEDGER_BAL AS amount, null as last_known_bal_date FROM ce_cp_stmt_headers_int stmt_int UNION ALL SELECT cp_stmt_header_id, nvl((select REPORTING_CURRENCY from CE_CP_OPTIONS WHERE PCF_SETUP_FLAG = 'N'),'USD') AS currency, 'Reporting Currency' AS currency_type, 'Opening Booked Balance' AS balance_type, 'Last Known' AS stmt_DATE, REPORTING_OPEN_LEDGER_BAL AS amount, null AS last_known_bal_date FROM ce_cp_stmt_headers_int stmt_int UNION ALL SELECT cp_stmt_header_id, bank_acct_currency_code AS currency, 'Account Currency' AS currency_type, 'Opening Available Balance' AS balance_type, 'Last Known' AS stmt_DATE, BANK_ACCT_OPEN_AVAILABLE_BAL AS amount, null AS last_known_bal_date FROM ce_cp_stmt_headers_int stmt_int UNION ALL SELECT cp_stmt_header_id, functional_currency_code AS currency, 'Ledger Currency' AS currency_type, 'Opening Available Balance' AS balance_type, 'Last Known' AS stmt_DATE, FUNCTIONAL_OPEN_AVAILABLE_BAL AS amount, null as last_known_bal_date FROM ce_cp_stmt_headers_int stmt_int UNION ALL SELECT cp_stmt_header_id, nvl((select REPORTING_CURRENCY from CE_CP_OPTIONS WHERE PCF_SETUP_FLAG = 'N'),'USD') AS currency, 'Reporting Currency' AS currency_type, 'Opening Available Balance' AS balance_type, 'Last Known' AS stmt_DATE, REPORTING_OPEN_AVAILABLE_BAL AS amount, null AS last_known_bal_date FROM ce_cp_stmt_headers_int stmt_int UNION ALL SELECT cp_stmt_header_id, bank_acct_currency_code AS currency, 'Account Currency' AS currency_type, 'Closing Available Balance' AS balance_type, 'Last Known' AS stmt_DATE, BANK_ACCT_CLOSE_AVAILABLE_BAL AS amount, null AS last_known_bal_date FROM ce_cp_stmt_headers_int stmt_int UNION ALL SELECT cp_stmt_header_id, functional_currency_code AS currency, 'Ledger Currency' AS currency_type, 'Closing Available Balance' AS balance_type, 'Last Known' AS stmt_DATE, FUNCTIONAL_CLOSE_AVAILABLE_BAL AS amount, null as last_known_bal_date FROM ce_cp_stmt_headers_int stmt_int UNION ALL SELECT cp_stmt_header_id, nvl((select REPORTING_CURRENCY from CE_CP_OPTIONS WHERE PCF_SETUP_FLAG = 'N'),'USD') AS currency, 'Reporting Currency' AS currency_type, 'Closing Available Balance' AS balance_type, 'Last Known' AS stmt_DATE, REPORTING_CLOSE_AVAILABLE_BAL AS amount, null AS last_known_bal_date FROM ce_cp_stmt_headers_int stmt_int UNION ALL SELECT cp_stmt_header_id, bank_acct_currency_code AS currency, 'Account Currency' AS currency_type, 'Closing Booked Balance' AS balance_type, 'Last Known' AS stmt_DATE, BANK_ACCT_CLOSE_LEDGER_BAL AS amount, null AS last_known_bal_date FROM ce_cp_stmt_headers_int stmt_int UNION ALL SELECT cp_stmt_header_id, functional_currency_code AS currency, 'Ledger Currency' AS currency_type, 'Closing Booked Balance' AS balance_type, 'Last Known' AS stmt_DATE, FUNCTIONAL_CLOSE_LEDGER_BAL AS amount, null as last_known_bal_date FROM ce_cp_stmt_headers_int stmt_int UNION ALL SELECT cp_stmt_header_id, nvl((select REPORTING_CURRENCY from CE_CP_OPTIONS WHERE PCF_SETUP_FLAG = 'N'),'USD') AS currency, 'Reporting Currency' AS currency_type, 'Closing Booked Balance' AS balance_type, 'Last Known' AS stmt_DATE, REPORTING_CLOSE_LEDGER_BAL AS amount, null AS last_known_bal_date FROM ce_cp_stmt_headers_int stmt_int UNION ALL SELECT cp_stmt_header_id, bank_acct_currency_code AS currency, 'Account Currency' AS currency_type, 'Opening 1 Day Float' AS balance_type, 'Last Known' AS stmt_DATE, BANK_ACCT_1DAY_OPEN_FLOAT AS amount, null AS last_known_bal_date FROM ce_cp_stmt_headers_int stmt_int UNION ALL SELECT cp_stmt_header_id, functional_currency_code AS currency, 'Ledger Currency' AS currency_type, 'Opening 1 Day Float' AS balance_type, 'Last Known' AS stmt_DATE, FUNCTIONAL_1DAY_OPEN_FLOAT AS amount, null as last_known_bal_date FROM ce_cp_stmt_headers_int stmt_int UNION ALL SELECT cp_stmt_header_id, nvl((select REPORTING_CURRENCY from CE_CP_OPTIONS WHERE PCF_SETUP_FLAG = 'N'),'USD') AS currency, 'Reporting Currency' AS currency_type, 'Opening 1 Day Float' AS balance_type, 'Last Known' AS stmt_DATE, REPORTING_1DAY_OPEN_FLOAT AS amount, null AS last_known_bal_date FROM ce_cp_stmt_headers_int stmt_int UNION ALL SELECT cp_stmt_header_id, bank_acct_currency_code AS currency, 'Account Currency' AS currency_type, 'Opening 2 Day Float' AS balance_type, 'Last Known' AS stmt_DATE, BANK_ACCT_2DAY_OPEN_FLOAT AS amount, null AS last_known_bal_date FROM ce_cp_stmt_headers_int stmt_int UNION ALL SELECT cp_stmt_header_id, functional_currency_code AS currency, 'Ledger Currency' AS currency_type, 'Opening 2 Day Float' AS balance_type, 'Last Known' AS stmt_DATE, FUNCTIONAL_2DAY_OPEN_FLOAT AS amount, null as last_known_bal_date FROM ce_cp_stmt_headers_int stmt_int UNION ALL SELECT cp_stmt_header_id, nvl((select REPORTING_CURRENCY from CE_CP_OPTIONS WHERE PCF_SETUP_FLAG = 'N'),'USD') AS currency, 'Reporting Currency' AS currency_type, 'Opening 2 Day Float' AS balance_type, 'Last Known' AS stmt_DATE, REPORTING_2DAY_OPEN_FLOAT AS amount, null AS last_known_bal_date FROM ce_cp_stmt_headers_int stmt_int UNION ALL SELECT cp_stmt_header_id, bank_acct_currency_code AS currency, 'Account Currency' AS currency_type, 'Closing 1 Day Float' AS balance_type, 'Last Known' AS stmt_DATE, BANK_ACCT_1DAY_CLOSE_FLOAT AS amount, null AS last_known_bal_date FROM ce_cp_stmt_headers_int stmt_int UNION ALL SELECT cp_stmt_header_id, functional_currency_code AS currency, 'Ledger Currency' AS currency_type, 'Closing 1 Day Float' AS balance_type, 'Last Known' AS stmt_DATE, FUNCTIONAL_1DAY_CLOSE_FLOAT AS amount, null as last_known_bal_date FROM ce_cp_stmt_headers_int stmt_int UNION ALL SELECT cp_stmt_header_id, nvl((select REPORTING_CURRENCY from CE_CP_OPTIONS WHERE PCF_SETUP_FLAG = 'N'),'USD') AS currency, 'Reporting Currency' AS currency_type, 'Closing 1 Day Float' AS balance_type, 'Last Known' AS stmt_DATE, REPORTING_1DAY_CLOSE_FLOAT AS amount, null AS last_known_bal_date FROM ce_cp_stmt_headers_int stmt_int UNION ALL SELECT cp_stmt_header_id, bank_acct_currency_code AS currency, 'Account Currency' AS currency_type, 'Closing 2 Day Float' AS balance_type, 'Last Known' AS stmt_DATE, BANK_ACCT_2DAY_CLOSE_FLOAT AS amount, null AS last_known_bal_date FROM ce_cp_stmt_headers_int stmt_int UNION ALL SELECT cp_stmt_header_id, functional_currency_code AS currency, 'Ledger Currency' AS currency_type, 'Closing 2 Day Float' AS balance_type, 'Last Known' AS stmt_DATE, FUNCTIONAL_2DAY_CLOSE_FLOAT AS amount, null as last_known_bal_date FROM ce_cp_stmt_headers_int stmt_int UNION ALL SELECT cp_stmt_header_id, nvl((select REPORTING_CURRENCY from CE_CP_OPTIONS WHERE PCF_SETUP_FLAG = 'N'),'USD') AS currency, 'Reporting Currency' AS currency_type, 'Closing 2 Day Float' AS balance_type, 'Last Known' AS stmt_DATE, REPORTING_2DAY_CLOSE_FLOAT AS amount, null AS last_known_bal_date FROM ce_cp_stmt_headers_int stmt_int ) stmts, (select max(cp_stmt_header_id) as max_id,bank_account_name,bank_name,source_system,source from ce_cp_stmt_headers_int t1 where balance_date = (select max(t2.balance_date) from ce_cp_stmt_headers_int t2 where t1.bank_account_name = t2.bank_account_name and t1.bank_name = t2.bank_name and t1.source_system = t2.source_system and t1.source = t2.source and t2.BANK_ACCT_BAL is not null and nvl(t2.error_flag,'N') != 'Y' and nvl(t2.delete_flag,'N') != 'Y') and t1.BANK_ACCT_BAL is not null and nvl(t1.error_flag,'N') != 'Y' and nvl(t1.delete_flag,'N') != 'Y' group by bank_account_name,bank_name,source_system,source) last_known_stmt_headers WHERE t.cp_stmt_header_id = max_id and not exists (select src_id from ce_CP_FACTs f where t.bank_account_name = f.bank_account_name and f.bank_name = t.bank_name and f.source = t.source and f.source_system = t.source_system and f.last_known_bal_flag = 'Y' and f.balance_date > t.balance_date) and stmts.cp_stmt_header_id = t.cp_stmt_header_id UNION ALL SELECT -1 AS CP_SYS_TRX_ID, cp_stmt_header_id, h.bank_account_name, '[Bank].[' || NVL(h.bank_name, 'NO_BANK_VALUE') || '].[' || NVL(h.bank_account_name, 'NO_BANK_ACCOUNT_VALUE') || ']' AS bank, '[Legal Entity].[' || NVL(h.legal_entity_region, 'NO_LE_REGION_VALUE') || '].[' || NVL(h.legal_entity_name, 'NO_LEGAL_ENTITY_VALUE') || ']' AS legal_entity, '[Application].[' || NVL(l.source_system, 'NO_SOURCE_SYSTEM_VALUE') || ']' AS source_system, '[Source].[Bank Statements].[' || NVL(l.source, 'NO_SOURCE_VALUE') || ']' AS source, '[Flow Indicator].[' || NVL(l.flow_indicator, 'NO_FLOW_INDICATOR_VALUE') || ']' AS flow_indicator, '[Reconciliation Status].[' || NVL(l.recon_status, 'NO_RECON_STATUS_VALUE') || ']' AS recon_status, '[Currency Type].[Account Currency]' AS currency_type, '[Currency].[' || NVL(h.bank_acct_currency_code, 'NO_CURR_VALUE') || ']' AS currency, '[Type].[Transaction Type].[' || NVL(l.transaction_type, 'NO_TRANSACTION_TYPE_VALUE') || ']' AS type, TO_CHAR(booking_date, 'MM/DD/YYYY') AS fact_DATE, bank_acct_amount AS amount, null AS LAST_KNOWN_BAL_DATE, NVL(l.EXT_DIM_ATTRIBUTE1, 'NO_VALUE') AS EXT_DIM_attribute1, NVL(l.EXT_DIM_ATTRIBUTE2, 'NO_VALUE') AS EXT_DIM_attribute2, NVL(l.EXT_DIM_ATTRIBUTE3, 'NO_VALUE') AS EXT_DIM_attribute3, NVL(l.EXT_DIM_ATTRIBUTE4, 'NO_VALUE') AS EXT_DIM_attribute4, NVL(l.EXT_DIM_ATTRIBUTE5, 'NO_VALUE') AS EXT_DIM_attribute5, NVL(l.EXT_DIM_ATTRIBUTE6, 'NO_VALUE') AS EXT_DIM_attribute6, NVL(l.EXT_DIM_ATTRIBUTE7, 'NO_VALUE') AS EXT_DIM_attribute7, NVL(l.EXT_DIM_ATTRIBUTE8, 'NO_VALUE') AS EXT_DIM_attribute8, NVL(l.EXT_DIM_ATTRIBUTE9, 'NO_VALUE') AS EXT_DIM_attribute9, NVL(l.EXT_DIM_ATTRIBUTE10, 'NO_VALUE') AS EXT_DIM_attribute10, NVL(l.EXT_DIM_ATTRIBUTE11, 'NO_VALUE') AS EXT_DIM_attribute11, NVL(l.EXT_DIM_ATTRIBUTE12, 'NO_VALUE') AS EXT_DIM_attribute12, NVL(l.EXT_DIM_ATTRIBUTE13, 'NO_VALUE') AS EXT_DIM_attribute13, NVL(l.EXT_DIM_ATTRIBUTE14, 'NO_VALUE') AS EXT_DIM_attribute14, NVL(l.EXT_DIM_ATTRIBUTE15, 'NO_VALUE') AS EXT_DIM_attribute15, h.CASH_POOL_NAME AS CASH_POOL, h.PARENT_CASH_POOL_NAME AS PARENT_CASH_POOL FROM ce_cp_stmt_headers_int h, ce_cp_stmt_lines_int l WHERE h.source_stmt_header_id = l.source_stmt_header_id AND h.source || ' Line' = l. source AND h.source_system = l.source_system AND nvl(l.error_flag,'N') != 'Y' and booking_date is not null and cp_stmt_line_id not in (select cp_stmt_line_id from ce_cp_stmt_lines_int li, ce_cp_facts fac where fac.src_id = li.source_stmt_line_id and fac.source_system = li.source_system and fac.source = li.source and fac.parent_id = li.source_stmt_header_id and exclude_flag = 'Y') UNION ALL SELECT -1 AS CP_SYS_TRX_ID, cp_stmt_header_id, h.bank_account_name, '[Bank].[' || NVL(h.bank_name, 'NO_BANK_VALUE') || '].[' || NVL(h.bank_account_name, 'NO_BANK_ACCOUNT_VALUE') || ']' AS bank, '[Legal Entity].[' || NVL(h.legal_entity_region, 'NO_LE_REGION_VALUE') || '].[' || NVL(h.legal_entity_name, 'NO_LEGAL_ENTITY_VALUE') || ']' AS legal_entity, '[Application].[' || NVL(l.source_system, 'NO_SOURCE_SYSTEM_VALUE') || ']' AS source_system, '[Source].[Bank Statements].[' || NVL(l.source, 'NO_SOURCE_VALUE') || ']' AS source, '[Flow Indicator].[' || NVL(l.flow_indicator, 'NO_FLOW_INDICATOR_VALUE') || ']' AS flow_indicator, '[Reconciliation Status].[' || NVL(l.recon_status, 'NO_RECON_STATUS_VALUE') || ']' AS recon_status, '[Currency Type].[Ledger Currency]' AS currency_type, '[Currency].[' || NVL(h.functional_currency_code, 'NO_CURR_VALUE') || ']' AS currency, '[Type].[Transaction Type].[' || NVL(l.transaction_type, 'NO_TRANSACTION_TYPE_VALUE') || ']' AS type, TO_CHAR(booking_date, 'MM/DD/YYYY') AS fact_DATE, functional_amount AS amount, null AS LAST_KNOWN_BAL_DATE, NVL(l.EXT_DIM_ATTRIBUTE1, 'NO_VALUE') AS EXT_DIM_attribute1, NVL(l.EXT_DIM_ATTRIBUTE2, 'NO_VALUE') AS EXT_DIM_attribute2, NVL(l.EXT_DIM_ATTRIBUTE3, 'NO_VALUE') AS EXT_DIM_attribute3, NVL(l.EXT_DIM_ATTRIBUTE4, 'NO_VALUE') AS EXT_DIM_attribute4, NVL(l.EXT_DIM_ATTRIBUTE5, 'NO_VALUE') AS EXT_DIM_attribute5, NVL(l.EXT_DIM_ATTRIBUTE6, 'NO_VALUE') AS EXT_DIM_attribute6, NVL(l.EXT_DIM_ATTRIBUTE7, 'NO_VALUE') AS EXT_DIM_attribute7, NVL(l.EXT_DIM_ATTRIBUTE8, 'NO_VALUE') AS EXT_DIM_attribute8, NVL(l.EXT_DIM_ATTRIBUTE9, 'NO_VALUE') AS EXT_DIM_attribute9, NVL(l.EXT_DIM_ATTRIBUTE10, 'NO_VALUE') AS EXT_DIM_attribute10, NVL(l.EXT_DIM_ATTRIBUTE11, 'NO_VALUE') AS EXT_DIM_attribute11, NVL(l.EXT_DIM_ATTRIBUTE12, 'NO_VALUE') AS EXT_DIM_attribute12, NVL(l.EXT_DIM_ATTRIBUTE13, 'NO_VALUE') AS EXT_DIM_attribute13, NVL(l.EXT_DIM_ATTRIBUTE14, 'NO_VALUE') AS EXT_DIM_attribute14, NVL(l.EXT_DIM_ATTRIBUTE15, 'NO_VALUE') AS EXT_DIM_attribute15, h.CASH_POOL_NAME AS CASH_POOL, h.PARENT_CASH_POOL_NAME AS PARENT_CASH_POOL FROM ce_cp_stmt_headers_int h, ce_cp_stmt_lines_int l WHERE h.source_stmt_header_id = l.source_stmt_header_id AND h.source || ' Line' = l.source AND h.source_system = l.source_system AND nvl(l.error_flag,'N') != 'Y' and booking_date is not null and cp_stmt_line_id not in (select cp_stmt_line_id from ce_cp_stmt_lines_int li, ce_cp_facts fac where fac.src_id = li.source_stmt_line_id and fac.source_system = li.source_system and fac.source = li.source and fac.parent_id = li.source_stmt_header_id and exclude_flag='Y') UNION ALL SELECT -1 AS CP_SYS_TRX_ID, cp_stmt_header_id, h.bank_account_name, '[Bank].[' || NVL(h.bank_name, 'NO_BANK_VALUE') || '].[' || NVL(h.bank_account_name, 'NO_BANK_ACCOUNT_VALUE') || ']' AS bank, '[Legal Entity].[' || NVL(h.legal_entity_region, 'NO_LE_REGION_VALUE') || '].[' || NVL(h.legal_entity_name, 'NO_LEGAL_ENTITY_VALUE') || ']' AS legal_entity, '[Application].[' || NVL(l.source_system, 'NO_SOURCE_SYSTEM_VALUE') || ']' AS source_system, '[Source].[Bank Statements].[' || NVL(l.source, 'NO_SOURCE_VALUE') || ']' AS source, '[Flow Indicator].[' || NVL(l.flow_indicator, 'NO_FLOW_INDICATOR_VALUE') || ']' AS flow_indicator, '[Reconciliation Status].[' || NVL(l.recon_status, 'NO_RECON_STATUS_VALUE') || ']' AS recon_status, '[Currency Type].[Reporting Currency]' AS currency_type, '[Currency].[' || nvl((select REPORTING_CURRENCY from CE_CP_OPTIONS WHERE PCF_SETUP_FLAG = 'N'),'USD') || ']' AS currency, '[Type].[Transaction Type].[' || NVL(l.transaction_type, 'NO_TRANSACTION_TYPE_VALUE') || ']' AS type, TO_CHAR(booking_date, 'MM/DD/YYYY') AS fact_DATE, reporting_amount AS amount, null AS LAST_KNOWN_BAL_DATE, NVL(l.EXT_DIM_ATTRIBUTE1, 'NO_VALUE') AS EXT_DIM_attribute1, NVL(l.EXT_DIM_ATTRIBUTE2, 'NO_VALUE') AS EXT_DIM_attribute2, NVL(l.EXT_DIM_ATTRIBUTE3, 'NO_VALUE') AS EXT_DIM_attribute3, NVL(l.EXT_DIM_ATTRIBUTE4, 'NO_VALUE') AS EXT_DIM_attribute4, NVL(l.EXT_DIM_ATTRIBUTE5, 'NO_VALUE') AS EXT_DIM_attribute5, NVL(l.EXT_DIM_ATTRIBUTE6, 'NO_VALUE') AS EXT_DIM_attribute6, NVL(l.EXT_DIM_ATTRIBUTE7, 'NO_VALUE') AS EXT_DIM_attribute7, NVL(l.EXT_DIM_ATTRIBUTE8, 'NO_VALUE') AS EXT_DIM_attribute8, NVL(l.EXT_DIM_ATTRIBUTE9, 'NO_VALUE') AS EXT_DIM_attribute9, NVL(l.EXT_DIM_ATTRIBUTE10, 'NO_VALUE') AS EXT_DIM_attribute10, NVL(l.EXT_DIM_ATTRIBUTE11, 'NO_VALUE') AS EXT_DIM_attribute11, NVL(l.EXT_DIM_ATTRIBUTE12, 'NO_VALUE') AS EXT_DIM_attribute12, NVL(l.EXT_DIM_ATTRIBUTE13, 'NO_VALUE') AS EXT_DIM_attribute13, NVL(l.EXT_DIM_ATTRIBUTE14, 'NO_VALUE') AS EXT_DIM_attribute14, NVL(l.EXT_DIM_ATTRIBUTE15, 'NO_VALUE') AS EXT_DIM_attribute15, h.CASH_POOL_NAME AS CASH_POOL, h.PARENT_CASH_POOL_NAME AS PARENT_CASH_POOL FROM ce_cp_stmt_headers_int h, ce_cp_stmt_lines_int l WHERE h.source_stmt_header_id = l.source_stmt_header_id AND h.source || ' Line' = l.source AND h.source_system = l.source_system AND nvl(l.error_flag,'N') != 'Y' and booking_date is not null and cp_stmt_line_id not in (select cp_stmt_line_id from ce_cp_stmt_lines_int li, ce_cp_facts fac where fac.src_id = li.source_stmt_line_id and fac.source_system = li.source_system and fac.source = li.source and fac.parent_id = li.source_stmt_header_id and exclude_flag = 'Y')) SELECT cp_sys_trx_id, cp_stmt_header_id, bank, legal_entity, source_system, source, flow_indicator, recon_status, currency_type, currency, type, fact_date, amount, last_known_bal_date, ext_dim_attribute1, ext_dim_attribute2, ext_dim_attribute3, ext_dim_attribute4, ext_dim_attribute5, ext_dim_attribute6, ext_dim_attribute7, ext_dim_attribute8, ext_dim_attribute9, ext_dim_attribute10, ext_dim_attribute11, ext_dim_attribute12, ext_dim_attribute13, ext_dim_attribute14, ext_dim_attribute15, CASE WHEN SQ.cash_pool = 'NO_CASH_POOL_VALUE' THEN '[Cash Pool].[NO_CASH_POOL]' WHEN SQ.cash_pool != 'NO_CASH_POOL_VALUE' AND SQ.parent_cash_pool = '[NO_PARENT_CASH_POOL_VALUE]' THEN DECODE(ACPM.sub_bank_account_type_code, 'INV_ACCT', '[Cash Pool].[' || SQ.cash_pool || '].[Investment Concentration Account]', 'FUND_ACCT', '[Cash Pool].[' || SQ.cash_pool || '].[Funding Concentration Account]', 'CONC_ACCT', '[Cash Pool].[' || SQ.cash_pool || '].[Concentration Account]', 'SUB_ACCT', '[Cash Pool].[' || SQ.cash_pool || '].[Subsidiary Account]') WHEN SQ.parent_cash_pool != '[NO_PARENT_CASH_POOL_VALUE]' THEN DECODE(ACPM.sub_bank_account_type_code, 'INV_ACCT', '[Cash Pool].' || SQ.parent_cash_pool || '.[' || SQ.cash_pool || '].[Investment Concentration Account]', 'FUND_ACCT', '[Cash Pool].' || SQ.parent_cash_pool || '.[' || SQ.cash_pool || '].[Funding Concentration Account]', 'CONC_ACCT', '[Cash Pool].' || SQ.parent_cash_pool || '.[' || SQ.cash_pool || '].[Concentration Account]', 'SUB_ACCT', '[Cash Pool].' || SQ.parent_cash_pool || '.[' || SQ.cash_pool || '].[Subsidiary Account]') END cash_pool FROM sub_query SQ, active_cash_pool_members ACPM WHERE SQ.bank_account_name = ACPM.bank_account_name(+)