CE_CP_INCREMENTAL_UPDATE_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, fac.bank_account_name, '[Bank].[' || NVL(fac.bank_name, 'NO_BANK_VALUE') || '].[' || NVL(fac.bank_account_name, 'NO_BANK_ACCOUNT_VALUE') || ']' AS bank, '[Legal Entity].[' || NVL(fac.legal_entity_region, 'NO_LE_REGION_VALUE') || '].[' || NVL(fac.legal_entity_name, 'NO_LEGAL_ENTITY_VALUE') || ']' AS legal_entity, '[Application].[' || NVL(fac.source_system, 'NO_SOURCE_SYSTEM_VALUE') || ']' AS source_system, '[Source].[System Transactions].[' || NVL(fac.source, 'NO_SOURCE_VALUE') || ']' AS source, '[Flow Indicator].[' || NVL(fac.flow_indicator, 'NO_FLOW_INDICATOR_VALUE') || ']' AS flow_indicator, '[Reconciliation Status].[' || NVL(fac.recon_status, 'NO_RECON_STATUS_VALUE') || ']' AS recon_status, '[Currency Type].[Transaction Currency]' AS currency_type, '[Currency].[' || fac.transaction_currency || ']' AS currency, '[Type].[Transaction Type].[' || NVL(fac.transaction_type, 'NO_TRANSACTION_TYPE_VALUE') || ']' AS type, TO_CHAR(fac.trx_date, 'MM/DD/YYYY') AS fact_DATE, -fac.TRX_AMOUNT AS amount, null AS last_known_bal_date, NVL(fac.EXT_DIM_ATTRIBUTE1, 'NO_VALUE') AS ext_dim_attribute1, NVL(fac.EXT_DIM_ATTRIBUTE2, 'NO_VALUE') AS ext_dim_attribute2, NVL(fac.EXT_DIM_ATTRIBUTE3, 'NO_VALUE') AS ext_dim_attribute3, NVL(fac.EXT_DIM_ATTRIBUTE4, 'NO_VALUE') AS ext_dim_attribute4, NVL(fac.EXT_DIM_ATTRIBUTE5, 'NO_VALUE') AS ext_dim_attribute5, NVL(fac.EXT_DIM_ATTRIBUTE6, 'NO_VALUE') AS ext_dim_attribute6, NVL(fac.EXT_DIM_ATTRIBUTE7, 'NO_VALUE') AS ext_dim_attribute7, NVL(fac.EXT_DIM_ATTRIBUTE8, 'NO_VALUE') AS ext_dim_attribute8, NVL(fac.EXT_DIM_ATTRIBUTE9, 'NO_VALUE') AS ext_dim_attribute9, NVL(fac.EXT_DIM_ATTRIBUTE10, 'NO_VALUE') AS ext_dim_attribute10, NVL(fac.EXT_DIM_ATTRIBUTE11, 'NO_VALUE') AS ext_dim_attribute11, NVL(fac.EXT_DIM_ATTRIBUTE12, 'NO_VALUE') AS ext_dim_attribute12, NVL(fac.EXT_DIM_ATTRIBUTE13, 'NO_VALUE') AS ext_dim_attribute13, NVL(fac.EXT_DIM_ATTRIBUTE14, 'NO_VALUE') AS ext_dim_attribute14, NVL(fac.EXT_DIM_ATTRIBUTE15, 'NO_VALUE') AS ext_dim_attribute15, fac.CASH_POOL_NAME, fac.PARENT_CASH_POOL_NAME FROM ce_cp_sys_trx_int trx_int, ce_cp_facts fac WHERE trx_int.SOURCE_TRANSACTION_ID = fac.src_id AND trx_int.source = fac.source AND trx_int.source_system = fac.source_system AND NVL(exclude_flag,'N') != 'Y' and NVL(DELETE_FLAG,'N') != 'Y' AND nvl(trx_int.error_flag,'N') != 'Y' UNION ALL SELECT cp_sys_trx_id, -1 AS cp_stmt_header_id, fac.bank_account_name, '[Bank].[' || NVL(fac.bank_name, 'NO_BANK_VALUE') || '].[' || NVL(fac.bank_account_name, 'NO_BANK_ACCOUNT_VALUE') || ']' AS bank, '[Legal Entity].[' || NVL(fac.legal_entity_region, 'NO_LE_REGION_VALUE') || '].[' || NVL(fac.legal_entity_name, 'NO_LEGAL_ENTITY_VALUE') || ']' AS legal_entity, '[Application].[' || NVL(fac.source_system, 'NO_SOURCE_SYSTEM_VALUE') || ']' AS source_system, '[Source].[System Transactions].[' || NVL(fac.source, 'NO_SOURCE_VALUE') || ']' AS source, '[Flow Indicator].[' || NVL(fac.flow_indicator, 'NO_FLOW_INDICATOR_VALUE') || ']' AS flow_indicator, '[Reconciliation Status].[' || NVL(fac.recon_status, 'NO_RECON_STATUS_VALUE') || ']' AS recon_status, '[Currency Type].[Ledger Currency]' AS currency_type, '[Currency].[' || fac.functional_currency || ']' AS currency, '[Type].[Transaction Type].[' || NVL(fac.transaction_type, 'NO_TRANSACTION_TYPE_VALUE') || ']' AS type, TO_CHAR(fac.trx_date, 'MM/DD/YYYY') AS fact_DATE, -fac.TRX_AMOUNT_FC AS amount, null AS LAST_KNOWN_BAL_DATE, NVL(fac.EXT_DIM_ATTRIBUTE1, 'NO_VALUE') AS ext_dim_attribute1, NVL(fac.EXT_DIM_ATTRIBUTE2, 'NO_VALUE') AS ext_dim_attribute2, NVL(fac.EXT_DIM_ATTRIBUTE3, 'NO_VALUE') AS ext_dim_attribute3, NVL(fac.EXT_DIM_ATTRIBUTE4, 'NO_VALUE') AS ext_dim_attribute4, NVL(fac.EXT_DIM_ATTRIBUTE5, 'NO_VALUE') AS ext_dim_attribute5, NVL(fac.EXT_DIM_ATTRIBUTE6, 'NO_VALUE') AS ext_dim_attribute6, NVL(fac.EXT_DIM_ATTRIBUTE7, 'NO_VALUE') AS ext_dim_attribute7, NVL(fac.EXT_DIM_ATTRIBUTE8, 'NO_VALUE') AS ext_dim_attribute8, NVL(fac.EXT_DIM_ATTRIBUTE9, 'NO_VALUE') AS ext_dim_attribute9, NVL(fac.EXT_DIM_ATTRIBUTE10, 'NO_VALUE') AS ext_dim_attribute10, NVL(fac.EXT_DIM_ATTRIBUTE11, 'NO_VALUE') AS ext_dim_attribute11, NVL(fac.EXT_DIM_ATTRIBUTE12, 'NO_VALUE') AS ext_dim_attribute12, NVL(fac.EXT_DIM_ATTRIBUTE13, 'NO_VALUE') AS ext_dim_attribute13, NVL(fac.EXT_DIM_ATTRIBUTE14, 'NO_VALUE') AS ext_dim_attribute14, NVL(fac.EXT_DIM_ATTRIBUTE15, 'NO_VALUE') AS ext_dim_attribute15, fac.CASH_POOL_NAME, fac.PARENT_CASH_POOL_NAME FROM ce_cp_sys_trx_int trx_int, ce_cp_facts fac WHERE trx_int.SOURCE_TRANSACTION_ID = fac.src_id AND trx_int.source = fac.source AND trx_int.source_system = fac.source_system AND NVL(exclude_flag,'N') != 'Y' and NVL(DELETE_FLAG,'N') != 'Y' AND nvl(trx_int.error_flag,'N') != 'Y' UNION ALL SELECT cp_sys_trx_id, -1 AS cp_stmt_header_id, fac.bank_account_name, '[Bank].[' || NVL(fac.bank_name, 'NO_BANK_VALUE') || '].[' || NVL(fac.bank_account_name, 'NO_BANK_ACCOUNT_VALUE') || ']' AS bank, '[Legal Entity].[' || NVL(fac.legal_entity_region, 'NO_LE_REGION_VALUE') || '].[' || NVL(fac.legal_entity_name, 'NO_LEGAL_ENTITY_VALUE') || ']' AS legal_entity, '[Application].[' || NVL(fac.source_system, 'NO_SOURCE_SYSTEM_VALUE') || ']' AS source_system, '[Source].[System Transactions].[' || NVL(fac.source, 'NO_SOURCE_VALUE') || ']' AS source, '[Flow Indicator].[' || NVL(fac.flow_indicator, 'NO_FLOW_INDICATOR_VALUE') || ']' AS flow_indicator, '[Reconciliation Status].[' || NVL(fac.recon_status, 'NO_RECON_STATUS_VALUE') || ']' AS recon_status, '[Currency Type].[Reporting Currency]' AS currency_type, '[Currency].[' || fac.reporting_currency || ']' AS currency, '[Type].[Transaction Type].[' || NVL(fac.transaction_type, 'NO_TRANSACTION_TYPE_VALUE') || ']' AS type, TO_CHAR(fac.trx_date, 'MM/DD/YYYY') AS fact_DATE, -fac.TRX_AMOUNT_RC AS amount, null AS LAST_KNOWN_BAL_DATE, NVL(fac.EXT_DIM_ATTRIBUTE1, 'NO_VALUE') AS ext_dim_attribute1, NVL(fac.EXT_DIM_ATTRIBUTE2, 'NO_VALUE') AS ext_dim_attribute2, NVL(fac.EXT_DIM_ATTRIBUTE3, 'NO_VALUE') AS ext_dim_attribute3, NVL(fac.EXT_DIM_ATTRIBUTE4, 'NO_VALUE') AS ext_dim_attribute4, NVL(fac.EXT_DIM_ATTRIBUTE5, 'NO_VALUE') AS ext_dim_attribute5, NVL(fac.EXT_DIM_ATTRIBUTE6, 'NO_VALUE') AS ext_dim_attribute6, NVL(fac.EXT_DIM_ATTRIBUTE7, 'NO_VALUE') AS ext_dim_attribute7, NVL(fac.EXT_DIM_ATTRIBUTE8, 'NO_VALUE') AS ext_dim_attribute8, NVL(fac.EXT_DIM_ATTRIBUTE9, 'NO_VALUE') AS ext_dim_attribute9, NVL(fac.EXT_DIM_ATTRIBUTE10, 'NO_VALUE') AS ext_dim_attribute10, NVL(fac.EXT_DIM_ATTRIBUTE11, 'NO_VALUE') AS ext_dim_attribute11, NVL(fac.EXT_DIM_ATTRIBUTE12, 'NO_VALUE') AS ext_dim_attribute12, NVL(fac.EXT_DIM_ATTRIBUTE13, 'NO_VALUE') AS ext_dim_attribute13, NVL(fac.EXT_DIM_ATTRIBUTE14, 'NO_VALUE') AS ext_dim_attribute14, NVL(fac.EXT_DIM_ATTRIBUTE15, 'NO_VALUE') AS ext_dim_attribute15, fac.CASH_POOL_NAME, fac.PARENT_CASH_POOL_NAME FROM ce_cp_sys_trx_int trx_int, ce_cp_facts fac WHERE trx_int.SOURCE_TRANSACTION_ID = fac.src_id AND trx_int.source = fac.source AND trx_int.source_system = fac.source_system AND NVL(exclude_flag,'N') != 'Y' and NVL(DELETE_FLAG,'N') != 'Y' AND nvl(trx_int.error_flag,'N') != 'Y' UNION ALL SELECT cp_sys_trx_id, -1 AS cp_stmt_header_id, fac.bank_account_name, '[Bank].[' || NVL(fac.bank_name, 'NO_BANK_VALUE') || '].[' || NVL(fac.bank_account_name, 'NO_BANK_ACCOUNT_VALUE') || ']' AS bank, '[Legal Entity].[' || NVL(fac.legal_entity_region, 'NO_LE_REGION_VALUE') || '].[' || NVL(fac.legal_entity_name, 'NO_LEGAL_ENTITY_VALUE') || ']' AS legal_entity, '[Application].[' || NVL(fac.source_system, 'NO_SOURCE_SYSTEM_VALUE') || ']' AS source_system, '[Source].[System Transactions].[' || NVL(fac.source, 'NO_SOURCE_VALUE') || ']' AS source, '[Flow Indicator].[' || NVL(fac.flow_indicator, 'NO_FLOW_INDICATOR_VALUE') || ']' AS flow_indicator, '[Reconciliation Status].[' || NVL(fac.recon_status, 'NO_RECON_STATUS_VALUE') || ']' AS recon_status, '[Currency Type].[Account Currency]' AS currency_type, '[Currency].[' || fac.bank_account_currency || ']' AS currency, '[Type].[Transaction Type].[' || NVL(fac.transaction_type, 'NO_TRANSACTION_TYPE_VALUE') || ']' AS type, TO_CHAR(fac.trx_date, 'MM/DD/YYYY') AS fact_DATE, -fac.TRX_AMOUNT_AC AS amount, null AS LAST_KNOWN_BAL_DATE, NVL(fac.EXT_DIM_ATTRIBUTE1, 'NO_VALUE') AS ext_dim_attribute1, NVL(fac.EXT_DIM_ATTRIBUTE2, 'NO_VALUE') AS ext_dim_attribute2, NVL(fac.EXT_DIM_ATTRIBUTE3, 'NO_VALUE') AS ext_dim_attribute3, NVL(fac.EXT_DIM_ATTRIBUTE4, 'NO_VALUE') AS ext_dim_attribute4, NVL(fac.EXT_DIM_ATTRIBUTE5, 'NO_VALUE') AS ext_dim_attribute5, NVL(fac.EXT_DIM_ATTRIBUTE6, 'NO_VALUE') AS ext_dim_attribute6, NVL(fac.EXT_DIM_ATTRIBUTE7, 'NO_VALUE') AS ext_dim_attribute7, NVL(fac.EXT_DIM_ATTRIBUTE8, 'NO_VALUE') AS ext_dim_attribute8, NVL(fac.EXT_DIM_ATTRIBUTE9, 'NO_VALUE') AS ext_dim_attribute9, NVL(fac.EXT_DIM_ATTRIBUTE10, 'NO_VALUE') AS ext_dim_attribute10, NVL(fac.EXT_DIM_ATTRIBUTE11, 'NO_VALUE') AS ext_dim_attribute11, NVL(fac.EXT_DIM_ATTRIBUTE12, 'NO_VALUE') AS ext_dim_attribute12, NVL(fac.EXT_DIM_ATTRIBUTE13, 'NO_VALUE') AS ext_dim_attribute13, NVL(fac.EXT_DIM_ATTRIBUTE14, 'NO_VALUE') AS ext_dim_attribute14, NVL(fac.EXT_DIM_ATTRIBUTE15, 'NO_VALUE') AS ext_dim_attribute15, fac.CASH_POOL_NAME, fac.PARENT_CASH_POOL_NAME FROM ce_cp_sys_trx_int trx_int, ce_cp_facts fac WHERE trx_int.SOURCE_TRANSACTION_ID = fac.src_id AND trx_int.source = fac.source AND trx_int.source_system = fac.source_system AND NVL(exclude_flag,'N') != 'Y' and NVL(DELETE_FLAG,'N') != 'Y' AND nvl(trx_int.error_flag,'N') != 'Y' UNION ALL SELECT -1 AS cp_sys_trx_id, stmts.cp_stmt_header_id, fac.bank_account_name, '[Bank].[' || NVL(fac.bank_name, 'NO_BANK_VALUE') || '].[' || NVL(fac.bank_account_name, 'NO_BANK_ACCOUNT_VALUE') || ']' AS bank, '[Legal Entity].[' || NVL(fac.legal_entity_region, 'NO_LE_REGION_VALUE') || '].[' || NVL(fac.legal_entity_name, 'NO_LEGAL_ENTITY_VALUE') || ']' AS legal_entity, '[Application].[' || NVL(fac.source_system, 'NO_SOURCE_SYSTEM_VALUE') || ']' AS source_system, '[Source].[Bank Statements].[' || NVL(fac.source, 'NO_SOURCE_VALUE') || ']' AS source, '[Flow Indicator].[' || NVL(fac.flow_indicator, 'NO_FLOW_INDICATOR_VALUE') || ']' AS flow_indicator, '[Reconciliation Status].[' || NVL(fac.recon_status, 'NO_RECON_STATUS_VALUE') || ']' AS recon_status, '[Currency Type].[' || stmts.currency_type || ']' AS currency_type, '[Currency].[' || stmts.currency || ']' as currency, '[Type].[Balance Type].[' || stmts.balance_type || ']' AS type, stmts.stmt_date AS FACT_DATE, amount, null as last_known_bal_date, NVL(fac.EXT_DIM_ATTRIBUTE1, 'NO_VALUE') AS ext_dim_attribute1, NVL(fac.EXT_DIM_ATTRIBUTE2, 'NO_VALUE') AS ext_dim_attribute2, NVL(fac.EXT_DIM_ATTRIBUTE3, 'NO_VALUE') AS ext_dim_attribute3, NVL(fac.EXT_DIM_ATTRIBUTE4, 'NO_VALUE') AS ext_dim_attribute4, NVL(fac.EXT_DIM_ATTRIBUTE5, 'NO_VALUE') AS ext_dim_attribute5, NVL(fac.EXT_DIM_ATTRIBUTE6, 'NO_VALUE') AS ext_dim_attribute6, NVL(fac.EXT_DIM_ATTRIBUTE7, 'NO_VALUE') AS ext_dim_attribute7, NVL(fac.EXT_DIM_ATTRIBUTE8, 'NO_VALUE') AS ext_dim_attribute8, NVL(fac.EXT_DIM_ATTRIBUTE9, 'NO_VALUE') AS ext_dim_attribute9, NVL(fac.EXT_DIM_ATTRIBUTE10, 'NO_VALUE') AS ext_dim_attribute10, NVL(fac.EXT_DIM_ATTRIBUTE11, 'NO_VALUE') AS ext_dim_attribute11, NVL(fac.EXT_DIM_ATTRIBUTE12, 'NO_VALUE') AS ext_dim_attribute12, NVL(fac.EXT_DIM_ATTRIBUTE13, 'NO_VALUE') AS ext_dim_attribute13, NVL(fac.EXT_DIM_ATTRIBUTE14, 'NO_VALUE') AS ext_dim_attribute14, NVL(fac.EXT_DIM_ATTRIBUTE15, 'NO_VALUE') AS ext_dim_attribute15, fac.CASH_POOL_NAME, fac.PARENT_CASH_POOL_NAME FROM ( SELECT cp_stmt_header_id, 'Account Currency' AS currency_type, facts.BANK_ACCOUNT_CURRENCY AS currency, TO_CHAR(facts.Opbd_Balance_Date, 'MM/DD/YYYY') AS stmt_DATE, 'Opening Booked Balance' AS balance_type, -facts.OPEN_LEDGER_AC AS amount FROM ce_cp_stmt_headers_int stmt_int, ce_cp_facts facts WHERE stmt_int.SOURCE_STMT_HEADER_ID = facts.src_id AND stmt_int.source = facts.source AND stmt_int.source_system = facts.source_system AND NVL(exclude_flag,'N') != 'Y' and nvl(last_known_bal_flag,'N') = 'N' and NVL(DELETE_FLAG,'N') != 'Y' UNION ALL SELECT cp_stmt_header_id, 'Account Currency' AS currency_type, facts.BANK_ACCOUNT_CURRENCY AS currency, TO_CHAR(facts.Opav_Balance_Date, 'MM/DD/YYYY') AS stmt_DATE, 'Opening Available Balance' AS balance_type, -facts.OPEN_AVAILABLE_AC AS amount FROM ce_cp_stmt_headers_int stmt_int, ce_cp_facts facts WHERE stmt_int.SOURCE_STMT_HEADER_ID = facts.src_id AND stmt_int.source = facts.source AND stmt_int.source_system = facts.source_system AND NVL(exclude_flag,'N') != 'Y' and nvl(last_known_bal_flag,'N') = 'N' and NVL(DELETE_FLAG,'N') != 'Y' UNION ALL SELECT cp_stmt_header_id, 'Account Currency' AS currency_type, facts.BANK_ACCOUNT_CURRENCY AS currency, TO_CHAR(facts.Opbd_Balance_Date, 'MM/DD/YYYY') AS stmt_DATE, 'Opening 1 Day Float' AS balance_type, -facts.OPEN_FLOAT_1DAY_AC AS amount FROM ce_cp_stmt_headers_int stmt_int, ce_cp_facts facts WHERE stmt_int.SOURCE_STMT_HEADER_ID = facts.src_id AND stmt_int.source = facts.source AND stmt_int.source_system = facts.source_system AND NVL(exclude_flag,'N') != 'Y' and nvl(last_known_bal_flag,'N') = 'N' and NVL(DELETE_FLAG,'N') != 'Y' UNION ALL SELECT cp_stmt_header_id, 'Account Currency' AS currency_type, facts.BANK_ACCOUNT_CURRENCY AS currency, TO_CHAR(facts.Opbd_Balance_Date, 'MM/DD/YYYY') AS stmt_DATE, 'Opening 2 Day Float' AS balance_type, -facts.OPEN_FLOAT_2DAY_AC AS amount FROM ce_cp_stmt_headers_int stmt_int, ce_cp_facts facts WHERE stmt_int.SOURCE_STMT_HEADER_ID = facts.src_id AND stmt_int.source = facts.source AND stmt_int.source_system = facts.source_system AND NVL(exclude_flag,'N') != 'Y' and nvl(last_known_bal_flag,'N') = 'N' and NVL(DELETE_FLAG,'N') != 'Y' UNION ALL SELECT cp_stmt_header_id, 'Account Currency' AS currency_type, facts.BANK_ACCOUNT_CURRENCY AS currency, TO_CHAR(facts.clbd_balance_date, 'MM/DD/YYYY') AS stmt_DATE, 'Closing Booked Balance' AS balance_type, -facts.CLOSE_LEDGER_AC AS amount FROM ce_cp_stmt_headers_int stmt_int, ce_cp_facts facts WHERE stmt_int.SOURCE_STMT_HEADER_ID = facts.src_id AND stmt_int.source = facts.source AND stmt_int.source_system = facts.source_system AND NVL(exclude_flag,'N') != 'Y' and nvl(last_known_bal_flag,'N') = 'N' and NVL(DELETE_FLAG,'N') != 'Y' UNION ALL SELECT cp_stmt_header_id, 'Account Currency' AS currency_type, facts.BANK_ACCOUNT_CURRENCY AS currency, TO_CHAR(facts.clav_balance_date, 'MM/DD/YYYY') AS stmt_DATE, 'Closing Available Balance' AS balance_type, -facts.CLOSE_AVAILABLE_AC AS amount FROM ce_cp_stmt_headers_int stmt_int, ce_cp_facts facts WHERE stmt_int.SOURCE_STMT_HEADER_ID = facts.src_id AND stmt_int.source = facts.source AND stmt_int.source_system = facts.source_system AND NVL(exclude_flag,'N') != 'Y' and nvl(last_known_bal_flag,'N') = 'N' and NVL(DELETE_FLAG,'N') != 'Y' UNION ALL SELECT cp_stmt_header_id, 'Account Currency' AS currency_type, facts.BANK_ACCOUNT_CURRENCY AS currency, TO_CHAR(facts.clbd_balance_date, 'MM/DD/YYYY') AS stmt_DATE, 'Closing 1 Day Float' AS balance_type, -facts.CLOSE_FLOAT_1DAY_AC AS amount FROM ce_cp_stmt_headers_int stmt_int, ce_cp_facts facts WHERE stmt_int.SOURCE_STMT_HEADER_ID = facts.src_id AND stmt_int.source = facts.source AND stmt_int.source_system = facts.source_system AND NVL(exclude_flag,'N') != 'Y' and nvl(last_known_bal_flag,'N') = 'N' and NVL(DELETE_FLAG,'N') != 'Y' UNION ALL SELECT cp_stmt_header_id, 'Account Currency' AS currency_type, facts.BANK_ACCOUNT_CURRENCY AS currency, TO_CHAR(facts.clbd_balance_date, 'MM/DD/YYYY') AS stmt_DATE, 'Closing 2 Day Float' AS balance_type, -facts.CLOSE_FLOAT_2DAY_AC AS amount FROM ce_cp_stmt_headers_int stmt_int, ce_cp_facts facts WHERE stmt_int.SOURCE_STMT_HEADER_ID = facts.src_id AND stmt_int.source = facts.source AND stmt_int.source_system = facts.source_system AND NVL(exclude_flag,'N') != 'Y' and nvl(last_known_bal_flag,'N') = 'N' and NVL(DELETE_FLAG,'N') != 'Y' UNION ALL SELECT cp_stmt_header_id, 'Ledger Currency' AS currency_type, facts.FUNCTIONAL_CURRENCY AS currency, TO_CHAR(facts.Opbd_Balance_Date, 'MM/DD/YYYY') AS stmt_DATE, 'Opening Booked Balance' AS balance_type, -facts.OPEN_LEDGER_FC AS amount FROM ce_cp_stmt_headers_int stmt_int, ce_cp_facts facts WHERE stmt_int.SOURCE_STMT_HEADER_ID = facts.src_id AND stmt_int.source = facts.source AND stmt_int.source_system = facts.source_system AND NVL(exclude_flag,'N') != 'Y' and nvl(last_known_bal_flag,'N') = 'N' and NVL(DELETE_FLAG,'N') != 'Y' UNION ALL SELECT cp_stmt_header_id, 'Ledger Currency' AS currency_type, facts.FUNCTIONAL_CURRENCY AS currency, TO_CHAR(facts.Opav_Balance_Date, 'MM/DD/YYYY') AS stmt_DATE, 'Opening Available Balance' AS balance_type, -facts.OPEN_AVAILABLE_FC AS amount FROM ce_cp_stmt_headers_int stmt_int, ce_cp_facts facts WHERE stmt_int.SOURCE_STMT_HEADER_ID = facts.src_id AND stmt_int.source = facts.source AND stmt_int.source_system = facts.source_system AND NVL(exclude_flag,'N') != 'Y' and nvl(last_known_bal_flag,'N') = 'N' and NVL(DELETE_FLAG,'N') != 'Y' UNION ALL SELECT cp_stmt_header_id, 'Ledger Currency' AS currency_type, facts.FUNCTIONAL_CURRENCY AS currency, TO_CHAR(facts.Opbd_Balance_Date, 'MM/DD/YYYY') AS stmt_DATE, 'Opening 1 Day Float' AS balance_type, -facts.OPEN_FLOAT_1DAY_FC AS amount FROM ce_cp_stmt_headers_int stmt_int, ce_cp_facts facts WHERE stmt_int.SOURCE_STMT_HEADER_ID = facts.src_id AND stmt_int.source = facts.source AND stmt_int.source_system = facts.source_system AND NVL(exclude_flag,'N') != 'Y' and NVL(DELETE_FLAG,'N') != 'Y' and nvl(last_known_bal_flag,'N') = 'N' UNION ALL SELECT cp_stmt_header_id, 'Ledger Currency' AS currency_type, facts.FUNCTIONAL_CURRENCY AS currency, TO_CHAR(facts.Opbd_Balance_Date, 'MM/DD/YYYY') AS stmt_DATE, 'Opening 2 Day Float' AS balance_type, -facts.OPEN_FLOAT_2DAY_FC AS amount FROM ce_cp_stmt_headers_int stmt_int, ce_cp_facts facts WHERE stmt_int.SOURCE_STMT_HEADER_ID = facts.src_id AND stmt_int.source = facts.source AND stmt_int.source_system = facts.source_system AND NVL(exclude_flag,'N') != 'Y' and nvl(last_known_bal_flag,'N') = 'N' and NVL(DELETE_FLAG,'N') != 'Y' UNION ALL SELECT cp_stmt_header_id, 'Ledger Currency' AS currency_type, facts.FUNCTIONAL_CURRENCY AS currency, TO_CHAR(facts.clbd_balance_date, 'MM/DD/YYYY') AS stmt_DATE, 'Closing Booked Balance' AS balance_type, -facts.CLOSE_LEDGER_FC AS amount FROM ce_cp_stmt_headers_int stmt_int, ce_cp_facts facts WHERE stmt_int.SOURCE_STMT_HEADER_ID = facts.src_id AND stmt_int.source = facts.source AND stmt_int.source_system = facts.source_system AND NVL(exclude_flag,'N') != 'Y' and nvl(last_known_bal_flag,'N') = 'N' and NVL(DELETE_FLAG,'N') != 'Y' UNION ALL SELECT cp_stmt_header_id, 'Ledger Currency' AS currency_type, facts.FUNCTIONAL_CURRENCY AS currency, TO_CHAR(facts.clav_balance_date, 'MM/DD/YYYY') AS stmt_DATE, 'Closing Available Balance' AS balance_type, -facts.CLOSE_AVAILABLE_FC AS amount FROM ce_cp_stmt_headers_int stmt_int, ce_cp_facts facts WHERE stmt_int.SOURCE_STMT_HEADER_ID = facts.src_id AND stmt_int.source = facts.source AND stmt_int.source_system = facts.source_system AND NVL(exclude_flag,'N') != 'Y' and nvl(last_known_bal_flag,'N') = 'N' and NVL(DELETE_FLAG,'N') != 'Y' UNION ALL SELECT cp_stmt_header_id, 'Ledger Currency' AS currency_type, facts.FUNCTIONAL_CURRENCY AS currency, TO_CHAR(facts.clbd_balance_date, 'MM/DD/YYYY') AS stmt_DATE, 'Closing 1 Day Float' AS balance_type, -facts.CLOSE_FLOAT_1DAY_FC AS amount FROM ce_cp_stmt_headers_int stmt_int, ce_cp_facts facts WHERE stmt_int.SOURCE_STMT_HEADER_ID = facts.src_id AND stmt_int.source = facts.source AND stmt_int.source_system = facts.source_system AND NVL(exclude_flag,'N') != 'Y' and nvl(last_known_bal_flag,'N') = 'N' and NVL(DELETE_FLAG,'N') != 'Y' UNION ALL SELECT cp_stmt_header_id, 'Ledger Currency' AS currency_type, facts.FUNCTIONAL_CURRENCY AS currency, TO_CHAR(facts.clbd_balance_date, 'MM/DD/YYYY') AS stmt_DATE, 'Closing 2 Day Float' AS balance_type, -facts.CLOSE_FLOAT_2DAY_FC AS amount FROM ce_cp_stmt_headers_int stmt_int, ce_cp_facts facts WHERE stmt_int.SOURCE_STMT_HEADER_ID = facts.src_id AND stmt_int.source = facts.source AND stmt_int.source_system = facts.source_system AND NVL(exclude_flag,'N') != 'Y' and nvl(last_known_bal_flag,'N') = 'N' and NVL(DELETE_FLAG,'N') != 'Y' UNION ALL SELECT cp_stmt_header_id, 'Reporting Currency' AS currency_type, facts.REPORTING_CURRENCY AS currency, TO_CHAR(facts.Opbd_Balance_Date, 'MM/DD/YYYY') AS stmt_DATE, 'Opening Booked Balance' AS balance_type, -facts.OPEN_LEDGER_RC AS amount FROM ce_cp_stmt_headers_int stmt_int, ce_cp_facts facts WHERE stmt_int.SOURCE_STMT_HEADER_ID = facts.src_id AND stmt_int.source = facts.source AND stmt_int.source_system = facts.source_system AND NVL(exclude_flag,'N') != 'Y' and nvl(last_known_bal_flag,'N') = 'N' and NVL(DELETE_FLAG,'N') != 'Y' UNION ALL SELECT cp_stmt_header_id, 'Reporting Currency' AS currency_type, facts.REPORTING_CURRENCY AS currency, TO_CHAR(facts.Opav_Balance_Date, 'MM/DD/YYYY') AS stmt_DATE, 'Opening Available Balance' AS balance_type, -facts.OPEN_AVAILABLE_RC AS amount FROM ce_cp_stmt_headers_int stmt_int, ce_cp_facts facts WHERE stmt_int.SOURCE_STMT_HEADER_ID = facts.src_id AND stmt_int.source = facts.source AND stmt_int.source_system = facts.source_system AND NVL(exclude_flag,'N') != 'Y' and nvl(last_known_bal_flag,'N') = 'N' and NVL(DELETE_FLAG,'N') != 'Y' UNION ALL SELECT cp_stmt_header_id, 'Reporting Currency' AS currency_type, facts.REPORTING_CURRENCY AS currency, TO_CHAR(facts.Opbd_Balance_Date, 'MM/DD/YYYY') AS stmt_DATE, 'Opening 1 Day Float' AS balance_type, -facts.OPEN_FLOAT_1DAY_RC AS amount FROM ce_cp_stmt_headers_int stmt_int, ce_cp_facts facts WHERE stmt_int.SOURCE_STMT_HEADER_ID = facts.src_id AND stmt_int.source = facts.source AND stmt_int.source_system = facts.source_system AND NVL(exclude_flag,'N') != 'Y' and nvl(last_known_bal_flag,'N') = 'N' and NVL(DELETE_FLAG,'N') != 'Y' UNION ALL SELECT cp_stmt_header_id, 'Reporting Currency' AS currency_type, facts.REPORTING_CURRENCY AS currency, TO_CHAR(facts.Opbd_Balance_Date, 'MM/DD/YYYY') AS stmt_DATE, 'Opening 2 Day Float' AS balance_type, -facts.OPEN_FLOAT_2DAY_RC AS amount FROM ce_cp_stmt_headers_int stmt_int, ce_cp_facts facts WHERE stmt_int.SOURCE_STMT_HEADER_ID = facts.src_id AND stmt_int.source = facts.source AND stmt_int.source_system = facts.source_system AND NVL(exclude_flag,'N') != 'Y' and nvl(last_known_bal_flag,'N') = 'N' and NVL(DELETE_FLAG,'N') != 'Y' UNION ALL SELECT cp_stmt_header_id, 'Reporting Currency' AS currency_type, facts.REPORTING_CURRENCY AS currency, TO_CHAR(facts.clbd_balance_date, 'MM/DD/YYYY') AS stmt_DATE, 'Closing Booked Balance' AS balance_type, -facts.CLOSE_LEDGER_RC AS amount FROM ce_cp_stmt_headers_int stmt_int, ce_cp_facts facts WHERE stmt_int.SOURCE_STMT_HEADER_ID = facts.src_id AND stmt_int.source = facts.source AND stmt_int.source_system = facts.source_system AND NVL(exclude_flag,'N') != 'Y' and nvl(last_known_bal_flag,'N') = 'N' and NVL(DELETE_FLAG,'N') != 'Y' UNION ALL SELECT cp_stmt_header_id, 'Reporting Currency' AS currency_type, facts.REPORTING_CURRENCY AS currency, TO_CHAR(facts.clav_balance_date, 'MM/DD/YYYY') AS stmt_DATE, 'Closing Available Balance' AS balance_type, -facts.CLOSE_AVAILABLE_RC AS amount FROM ce_cp_stmt_headers_int stmt_int, ce_cp_facts facts WHERE stmt_int.SOURCE_STMT_HEADER_ID = facts.src_id AND stmt_int.source = facts.source AND stmt_int.source_system = facts.source_system AND NVL(exclude_flag,'N') != 'Y' and nvl(last_known_bal_flag,'N') = 'N' and NVL(DELETE_FLAG,'N') != 'Y' UNION ALL SELECT cp_stmt_header_id, 'Reporting Currency' AS currency_type, facts.REPORTING_CURRENCY AS currency, TO_CHAR(facts.clbd_balance_date, 'MM/DD/YYYY') AS stmt_DATE, 'Closing 1 Day Float' AS balance_type, -facts.CLOSE_FLOAT_1DAY_RC AS amount FROM ce_cp_stmt_headers_int stmt_int, ce_cp_facts facts WHERE stmt_int.SOURCE_STMT_HEADER_ID = facts.src_id AND stmt_int.source = facts.source AND stmt_int.source_system = facts.source_system AND NVL(exclude_flag,'N') != 'Y' and nvl(last_known_bal_flag,'N') = 'N' and NVL(DELETE_FLAG,'N') != 'Y' UNION ALL SELECT cp_stmt_header_id, 'Reporting Currency' AS currency_type, facts.REPORTING_CURRENCY AS currency, TO_CHAR(facts.clbd_balance_date, 'MM/DD/YYYY') AS stmt_DATE, 'Closing 2 Day Float' AS balance_type, -facts.CLOSE_FLOAT_2DAY_RC AS amount FROM ce_cp_stmt_headers_int stmt_int, ce_cp_facts facts WHERE stmt_int.SOURCE_STMT_HEADER_ID = facts.src_id AND stmt_int.source = facts.source AND stmt_int.source_system = facts.source_system AND NVL(exclude_flag,'N') != 'Y' and nvl(last_known_bal_flag,'N') = 'N' and NVL(DELETE_FLAG,'N') != 'Y') stmts, ce_cp_stmt_headers_int details, CE_CP_FACTS fac WHERE stmts.cp_stmt_header_id = details.cp_stmt_header_id AND stmts.stmt_date IS NOT NULL AND details.SOURCE_STMT_HEADER_ID = fac.src_id AND details.source = fac.source AND details.source_system = fac.source_system AND NVL(exclude_flag,'N') != 'Y' AND nvl(details.error_flag,'N') != 'Y' AND fac.last_known_bal_flag is null UNION ALL SELECT -1 AS CP_SYS_TRX_ID, h.cp_stmt_header_id, fac.bank_account_name, '[Bank].[' || NVL(fac.bank_name, 'NO_BANK_VALUE') || '].[' || NVL(fac.bank_account_name, 'NO_BANK_ACCOUNT_VALUE') || ']' AS bank, '[Legal Entity].[' || NVL(fac.legal_entity_region, 'NO_LE_REGION_VALUE') || '].[' || NVL(fac.legal_entity_name, 'NO_LEGAL_ENTITY_VALUE') || ']' AS legal_entity, '[Application].[' || NVL(fac.source_system, 'NO_SOURCE_SYSTEM_VALUE') || ']' AS source_system, NVL(fac.source, 'NO_SOURCE_VALUE') AS source, '[Flow Indicator].[' || NVL(fac.flow_indicator, 'NO_FLOW_INDICATOR_VALUE') || ']' AS flow_indicator, '[Reconciliation Status].[' || NVL(fac.recon_status, 'NO_RECON_STATUS_VALUE') || ']' AS recon_status, '[Currency Type].[Account Currency]' AS currency_type, '[Currency].[' || NVL(fac.bank_account_currency, 'NO_CURR_VALUE') || ']' AS currency, '[Type].[Transaction Type].[' || NVL(fac.transaction_type, 'NO_TRANSACTION_TYPE_VALUE') || ']' AS type, TO_CHAR(fac.TRX_DATE, 'MM/DD/YYYY') AS fact_DATE, -fac.trx_amount_ac AS amount, null AS LAST_KNOWN_BAL_DATE, NVL(fac.EXT_DIM_ATTRIBUTE1, 'NO_VALUE') AS ext_dim_attribute1, NVL(fac.EXT_DIM_ATTRIBUTE2, 'NO_VALUE') AS ext_dim_attribute2, NVL(fac.EXT_DIM_ATTRIBUTE3, 'NO_VALUE') AS ext_dim_attribute3, NVL(fac.EXT_DIM_ATTRIBUTE4, 'NO_VALUE') AS ext_dim_attribute4, NVL(fac.EXT_DIM_ATTRIBUTE5, 'NO_VALUE') AS ext_dim_attribute5, NVL(fac.EXT_DIM_ATTRIBUTE6, 'NO_VALUE') AS ext_dim_attribute6, NVL(fac.EXT_DIM_ATTRIBUTE7, 'NO_VALUE') AS ext_dim_attribute7, NVL(fac.EXT_DIM_ATTRIBUTE8, 'NO_VALUE') AS ext_dim_attribute8, NVL(fac.EXT_DIM_ATTRIBUTE9, 'NO_VALUE') AS ext_dim_attribute9, NVL(fac.EXT_DIM_ATTRIBUTE10, 'NO_VALUE') AS ext_dim_attribute10, NVL(fac.EXT_DIM_ATTRIBUTE11, 'NO_VALUE') AS ext_dim_attribute11, NVL(fac.EXT_DIM_ATTRIBUTE12, 'NO_VALUE') AS ext_dim_attribute12, NVL(fac.EXT_DIM_ATTRIBUTE13, 'NO_VALUE') AS ext_dim_attribute13, NVL(fac.EXT_DIM_ATTRIBUTE14, 'NO_VALUE') AS ext_dim_attribute14, NVL(fac.EXT_DIM_ATTRIBUTE15, 'NO_VALUE') AS ext_dim_attribute15, fac.CASH_POOL_NAME, fac.PARENT_CASH_POOL_NAME FROM ce_cp_stmt_headers_int h, ce_cp_stmt_lines_int l, CE_CP_FACTS fac 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 l.SOURCE_STMT_line_ID = fac.src_id AND l.source = fac.source AND l.source_system = fac.source_system AND NVL(exclude_flag,'N') != 'Y' AND nvl(l.error_flag,'N') != 'Y' and nvl(l.delete_flag,'N') != 'Y' UNION ALL SELECT -1 AS CP_SYS_TRX_ID, h.cp_stmt_header_id, fac.bank_account_name, '[Bank].[' || NVL(fac.bank_name, 'NO_BANK_VALUE') || '].[' || NVL(fac.bank_account_name, 'NO_BANK_ACCOUNT_VALUE') || ']' AS bank, '[Legal Entity].[' || NVL(fac.legal_entity_region, 'NO_LE_REGION_VALUE') || '].[' || NVL(fac.legal_entity_name, 'NO_LEGAL_ENTITY_VALUE') || ']' AS legal_entity, '[Application].[' || NVL(fac.source_system, 'NO_SOURCE_SYSTEM_VALUE') || ']' AS source_system, NVL(fac.source, 'NO_SOURCE_VALUE') AS source, '[Flow Indicator].[' || NVL(fac.flow_indicator, 'NO_FLOW_INDICATOR_VALUE') || ']' AS flow_indicator, '[Reconciliation Status].[' || NVL(fac.recon_status, 'NO_RECON_STATUS_VALUE') || ']' AS recon_status, '[Currency Type].[Ledger Currency]' AS currency_type, '[Currency].[' || NVL(fac.functional_currency, 'NO_CURR_VALUE') || ']' AS currency, '[Type].[Transaction Type].[' || NVL(fac.transaction_type, 'NO_TRANSACTION_TYPE_VALUE') || ']' AS type, TO_CHAR(fac.TRX_DATE, 'MM/DD/YYYY') AS fact_DATE, -fac.trx_amount_fc AS amount, null AS LAST_KNOWN_BAL_DATE, NVL(fac.EXT_DIM_ATTRIBUTE1, 'NO_VALUE') AS ext_dim_attribute1, NVL(fac.EXT_DIM_ATTRIBUTE2, 'NO_VALUE') AS ext_dim_attribute2, NVL(fac.EXT_DIM_ATTRIBUTE3, 'NO_VALUE') AS ext_dim_attribute3, NVL(fac.EXT_DIM_ATTRIBUTE4, 'NO_VALUE') AS ext_dim_attribute4, NVL(fac.EXT_DIM_ATTRIBUTE5, 'NO_VALUE') AS ext_dim_attribute5, NVL(fac.EXT_DIM_ATTRIBUTE6, 'NO_VALUE') AS ext_dim_attribute6, NVL(fac.EXT_DIM_ATTRIBUTE7, 'NO_VALUE') AS ext_dim_attribute7, NVL(fac.EXT_DIM_ATTRIBUTE8, 'NO_VALUE') AS ext_dim_attribute8, NVL(fac.EXT_DIM_ATTRIBUTE9, 'NO_VALUE') AS ext_dim_attribute9, NVL(fac.EXT_DIM_ATTRIBUTE10, 'NO_VALUE') AS ext_dim_attribute10, NVL(fac.EXT_DIM_ATTRIBUTE11, 'NO_VALUE') AS ext_dim_attribute11, NVL(fac.EXT_DIM_ATTRIBUTE12, 'NO_VALUE') AS ext_dim_attribute12, NVL(fac.EXT_DIM_ATTRIBUTE13, 'NO_VALUE') AS ext_dim_attribute13, NVL(fac.EXT_DIM_ATTRIBUTE14, 'NO_VALUE') AS ext_dim_attribute14, NVL(fac.EXT_DIM_ATTRIBUTE15, 'NO_VALUE') AS ext_dim_attribute15, fac.CASH_POOL_NAME, fac.PARENT_CASH_POOL_NAME FROM ce_cp_stmt_headers_int h, ce_cp_stmt_lines_int l, CE_CP_FACTS fac 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 l.SOURCE_STMT_line_ID = fac.src_id AND l.source = fac.source AND l.source_system = fac.source_system AND NVL(exclude_flag,'N') != 'Y' AND nvl(l.error_flag,'N') != 'Y' and nvl(l.delete_flag,'N') != 'Y' UNION ALL SELECT -1 AS CP_SYS_TRX_ID, h.cp_stmt_header_id, fac.bank_account_name, '[Bank].[' || NVL(fac.bank_name, 'NO_BANK_VALUE') || '].[' || NVL(fac.bank_account_name, 'NO_BANK_ACCOUNT_VALUE') || ']' AS bank, '[Legal Entity].[' || NVL(fac.legal_entity_region, 'NO_LE_REGION_VALUE') || '].[' || NVL(fac.legal_entity_name, 'NO_LEGAL_ENTITY_VALUE') || ']' AS legal_entity, '[Application].[' || NVL(fac.source_system, 'NO_SOURCE_SYSTEM_VALUE') || ']' AS source_system, NVL(fac.source, 'NO_SOURCE_VALUE') AS source, '[Flow Indicator].[' || NVL(fac.flow_indicator, 'NO_FLOW_INDICATOR_VALUE') || ']' AS flow_indicator, '[Reconciliation Status].[' || NVL(fac.recon_status, 'NO_RECON_STATUS_VALUE') || ']' AS recon_status, '[Currency Type].[Reporting Currency]' AS currency_type, '[Currency].[' || NVL(fac.reporting_currency, 'NO_CURR_VALUE') || ']' AS currency, '[Type].[Transaction Type].[' || NVL(fac.transaction_type, 'NO_TRANSACTION_TYPE_VALUE') || ']' AS type, TO_CHAR(fac.TRX_DATE, 'MM/DD/YYYY') AS fact_DATE, -fac.trx_amount_rc AS amount, null AS LAST_KNOWN_BAL_DATE, NVL(fac.EXT_DIM_ATTRIBUTE1, 'NO_VALUE') AS ext_dim_attribute1, NVL(fac.EXT_DIM_ATTRIBUTE2, 'NO_VALUE') AS ext_dim_attribute2, NVL(fac.EXT_DIM_ATTRIBUTE3, 'NO_VALUE') AS ext_dim_attribute3, NVL(fac.EXT_DIM_ATTRIBUTE4, 'NO_VALUE') AS ext_dim_attribute4, NVL(fac.EXT_DIM_ATTRIBUTE5, 'NO_VALUE') AS ext_dim_attribute5, NVL(fac.EXT_DIM_ATTRIBUTE6, 'NO_VALUE') AS ext_dim_attribute6, NVL(fac.EXT_DIM_ATTRIBUTE7, 'NO_VALUE') AS ext_dim_attribute7, NVL(fac.EXT_DIM_ATTRIBUTE8, 'NO_VALUE') AS ext_dim_attribute8, NVL(fac.EXT_DIM_ATTRIBUTE9, 'NO_VALUE') AS ext_dim_attribute9, NVL(fac.EXT_DIM_ATTRIBUTE10, 'NO_VALUE') AS ext_dim_attribute10, NVL(fac.EXT_DIM_ATTRIBUTE11, 'NO_VALUE') AS ext_dim_attribute11, NVL(fac.EXT_DIM_ATTRIBUTE12, 'NO_VALUE') AS ext_dim_attribute12, NVL(fac.EXT_DIM_ATTRIBUTE13, 'NO_VALUE') AS ext_dim_attribute13, NVL(fac.EXT_DIM_ATTRIBUTE14, 'NO_VALUE') AS ext_dim_attribute14, NVL(fac.EXT_DIM_ATTRIBUTE15, 'NO_VALUE') AS ext_dim_attribute15, fac.CASH_POOL_NAME, fac.PARENT_CASH_POOL_NAME FROM ce_cp_stmt_headers_int h, ce_cp_stmt_lines_int l, CE_CP_FACTS fac 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 l.SOURCE_STMT_line_ID = fac.src_id AND l.source = fac.source AND l.source_system = fac.source_system AND NVL(exclude_flag,'N') != 'Y' AND nvl(l.error_flag,'N') != 'Y' and nvl(l.delete_flag,'N') != '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_name = 'NO_CASH_POOL_VALUE' THEN '[Cash Pool].[NO_CASH_POOL]' WHEN SQ.cash_pool_name != 'NO_CASH_POOL_VALUE' AND SQ.parent_cash_pool_name = '[NO_PARENT_CASH_POOL_VALUE]' THEN DECODE(ACPM.sub_bank_account_type_code, 'INV_ACCT', '[Cash Pool].[' || SQ.cash_pool_name || '].[Investment Concentration Account]', 'FUND_ACCT', '[Cash Pool].[' || SQ.cash_pool_name || '].[Funding Concentration Account]', 'CONC_ACCT', '[Cash Pool].[' || SQ.cash_pool_name || '].[Concentration Account]', 'SUB_ACCT', '[Cash Pool].[' || SQ.cash_pool_name || '].[Subsidiary Account]') WHEN SQ.parent_cash_pool_name != '[NO_PARENT_CASH_POOL_VALUE]' THEN DECODE(ACPM.sub_bank_account_type_code, 'INV_ACCT', '[Cash Pool].' || SQ.parent_cash_pool_name || '.[' || SQ.cash_pool_name || '].[Investment Concentration Account]', 'FUND_ACCT', '[Cash Pool].' || SQ.parent_cash_pool_name || '.[' || SQ.cash_pool_name || '].[Funding Concentration Account]', 'CONC_ACCT', '[Cash Pool].' || SQ.parent_cash_pool_name || '.[' || SQ.cash_pool_name || '].[Concentration Account]', 'SUB_ACCT', '[Cash Pool].' || SQ.parent_cash_pool_name || '.[' || SQ.cash_pool_name || '].[Subsidiary Account]') END cash_pool FROM sub_query SQ, active_cash_pool_members ACPM WHERE SQ.bank_account_name = ACPM.bank_account_name(+)