CE_CP_ESTIMATED_BALANCE_V

Details

• Schema: FUSION

• Object owner: CE

• Object type: VIEW

Columns

Name

BANK LEGAL_ENTITY SOURCE_SYSTEM SOURCE FLOW_INDICATOR RECON_STATUS CURRENCY_TYPE CURRENCY TYPE FACT_DATE AMOUNT 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 bank_account_name, '[Bank].[' || NVL(bank_name, 'NO_BANK_VALUE') || '].[' || NVL(bank_account_name, 'NO_BANK_ACCOUNT_VALUE') || ']' AS bank, '[Legal Entity].[' || NVL(legal_entity_region, 'NO_LE_REGION_VALUE') || '].[' || NVL(legal_entity_name, 'NO_LEGAL_ENTITY_VALUE') || ']' AS legal_entity, '[Application].[' || SOURCE_SYSTEM || ']' as source_system, '[Source].[Bank Statements].[' || SOURCE || ']' AS source, '[Flow Indicator].[Bank Balance]' as flow_indicator, '[Reconciliation Status].[Not Applicable]' as recon_status, '[Currency Type].[Ledger Currency]' AS currency_type, '[Currency].[' || nvl(FUNCTIONAL_CURRENCY,'NO_CURR_VALUE') || ']' as CURRENCY, '[Type].[Balance Type].[Estimated Balance]' AS type, '[Time].[Last Known]' as fact_date, (CLOSE_LEDGER_FC + nvl(bal_correction_fc,0)) as AMOUNT, nvl(EXT_DIM_attribute1,'NO_VALUE') as EXT_DIM_attribute1, nvl(EXT_DIM_attribute2,'NO_VALUE') as EXT_DIM_attribute2, nvl(EXT_DIM_attribute3,'NO_VALUE') as EXT_DIM_attribute3, nvl(EXT_DIM_attribute4,'NO_VALUE') as EXT_DIM_attribute4, nvl(EXT_DIM_attribute5,'NO_VALUE') as EXT_DIM_attribute5, nvl(EXT_DIM_attribute6,'NO_VALUE') as EXT_DIM_attribute6, nvl(EXT_DIM_attribute7,'NO_VALUE') as EXT_DIM_attribute7, nvl(EXT_DIM_attribute8,'NO_VALUE') as EXT_DIM_attribute8, nvl(EXT_DIM_attribute9,'NO_VALUE') as EXT_DIM_attribute9, nvl(EXT_DIM_attribute10,'NO_VALUE') as EXT_DIM_attribute10, nvl(EXT_DIM_attribute11,'NO_VALUE') as EXT_DIM_attribute11, nvl(EXT_DIM_attribute12,'NO_VALUE') as EXT_DIM_attribute12, nvl(EXT_DIM_attribute13,'NO_VALUE') as EXT_DIM_attribute13, nvl(EXT_DIM_attribute14,'NO_VALUE') as EXT_DIM_attribute14, nvl(EXT_DIM_attribute15,'NO_VALUE') as EXT_DIM_attribute15, CASH_POOL_NAME, PARENT_CASH_POOL_NAME FROM CE_CP_FACTS, (SELECT stmt_facts.bank_Account_name as ban,stmt_facts.bank_name as ba,stmt_facts.source_system as ss, stmt_facts.source as src, SUM(DECODE(FLOW_INDICATOR,'Inflow',TRX_AMOUNT_FC,-TRX_AMOUNT_FC)) as BAL_CORRECTION_FC FROM CE_CP_FACTS trx_facts, (SELECT BANK_NAME,BANK_ACCOUNT_NAME, SOURCE_SYSTEM, SOURCE, (CLBD_BALANCE_DATE+1) as bal_date FROM CE_CP_FACTS WHERE LAST_KNOWN_BAL_FLAG='E' and CLOSE_LEDGER_AC is not null) stmt_facts where trx_facts.SOURCE IN ('AP Payments','AR Receipts','AP Invoices','AR Invoices', 'External Transactions','Payroll') and trx_facts.bank_Account_name = stmt_facts.bank_Account_name and trx_facts.bank_name = stmt_facts.bank_name and trx_facts.source_system = stmt_facts.source_system AND TRX_DATE >= bal_date and TRX_DATE <= trunc(SYSDATE)-1 group by stmt_facts.bank_account_name,stmt_facts.bank_name, stmt_facts.source_system, stmt_facts.source) corrected_balances WHERE BANK_NAME=BA(+) and BANK_ACCOUNT_NAME=BAN(+) and SOURCE_SYSTEM=SS(+) and SOURCE = SRC(+) and LAST_KNOWN_BAL_FLAG='E' UNION ALL SELECT bank_account_name, '[Bank].[' || NVL(bank_name, 'NO_BANK_VALUE') || '].[' || NVL(bank_account_name, 'NO_BANK_ACCOUNT_VALUE') || ']' AS bank, '[Legal Entity].[' || NVL(legal_entity_region, 'NO_LE_REGION_VALUE') || '].[' || NVL(legal_entity_name, 'NO_LEGAL_ENTITY_VALUE') || ']' AS legal_entity, '[Application].[' || SOURCE_SYSTEM || ']' as source_system, '[Source].[Bank Statements].[' || SOURCE || ']' AS source, '[Flow Indicator].[Bank Balance]' as flow_indicator, '[Reconciliation Status].[Not Applicable]' as recon_status, '[Currency Type].[Account Currency]' AS currency_type, '[Currency].[' || nvl(BANK_ACCOUNT_CURRENCY,'NO_CURR_VALUE') || ']' as CURRENCY, '[Type].[Balance Type].[Estimated Balance]' AS type, '[Time].[Last Known]' as fact_date, (CLOSE_LEDGER_AC+nvl(BAL_CORRECTION_AC,0)) as amount, nvl(EXT_DIM_attribute1,'NO_VALUE') as EXT_DIM_attribute1, nvl(EXT_DIM_attribute2,'NO_VALUE') as EXT_DIM_attribute2, nvl(EXT_DIM_attribute3,'NO_VALUE') as EXT_DIM_attribute3, nvl(EXT_DIM_attribute4,'NO_VALUE') as EXT_DIM_attribute4, nvl(EXT_DIM_attribute5,'NO_VALUE') as EXT_DIM_attribute5, nvl(EXT_DIM_attribute6,'NO_VALUE') as EXT_DIM_attribute6, nvl(EXT_DIM_attribute7,'NO_VALUE') as EXT_DIM_attribute7, nvl(EXT_DIM_attribute8,'NO_VALUE') as EXT_DIM_attribute8, nvl(EXT_DIM_attribute9,'NO_VALUE') as EXT_DIM_attribute9, nvl(EXT_DIM_attribute10,'NO_VALUE') as EXT_DIM_attribute10, nvl(EXT_DIM_attribute11,'NO_VALUE') as EXT_DIM_attribute11, nvl(EXT_DIM_attribute12,'NO_VALUE') as EXT_DIM_attribute12, nvl(EXT_DIM_attribute13,'NO_VALUE') as EXT_DIM_attribute13, nvl(EXT_DIM_attribute14,'NO_VALUE') as EXT_DIM_attribute14, nvl(EXT_DIM_attribute15,'NO_VALUE') as EXT_DIM_attribute15, CASH_POOL_NAME, PARENT_CASH_POOL_NAME FROM CE_CP_FACTS, (SELECT stmt_facts.bank_Account_name as ban,stmt_facts.bank_name as ba,stmt_facts.source_system as ss, stmt_facts.source as src, SUM(DECODE(FLOW_INDICATOR,'Inflow',TRX_AMOUNT_AC,-TRX_AMOUNT_AC)) as BAL_CORRECTION_AC FROM CE_CP_FACTS trx_facts, (SELECT BANK_NAME,BANK_ACCOUNT_NAME, SOURCE_SYSTEM, SOURCE, (CLBD_BALANCE_DATE+1) as bal_date FROM CE_CP_FACTS WHERE LAST_KNOWN_BAL_FLAG='E' and CLOSE_LEDGER_AC is not null) stmt_facts where trx_facts.SOURCE IN ('AP Payments','AR Receipts','AP Invoices','AR Invoices', 'External Transactions','Payroll') and trx_facts.bank_Account_name = stmt_facts.bank_Account_name and trx_facts.bank_name = stmt_facts.bank_name and trx_facts.source_system = stmt_facts.source_system AND TRX_DATE >= bal_date and TRX_DATE <= trunc(SYSDATE)-1 group by stmt_facts.bank_account_name,stmt_facts.bank_name, stmt_facts.source_system, stmt_facts.source) corrected_balances WHERE BANK_NAME=BA(+) and BANK_ACCOUNT_NAME=BAN(+) and SOURCE_SYSTEM=SS(+) and SOURCE = SRC(+) and LAST_KNOWN_BAL_FLAG='E' UNION ALL SELECT bank_account_name, '[Bank].[' || NVL(bank_name, 'NO_BANK_VALUE') || '].[' || NVL(bank_account_name, 'NO_BANK_ACCOUNT_VALUE') || ']' AS bank, '[Legal Entity].[' || NVL(legal_entity_region, 'NO_LE_REGION_VALUE') || '].[' || NVL(legal_entity_name, 'NO_LEGAL_ENTITY_VALUE') || ']' AS legal_entity, '[Application].[' || SOURCE_SYSTEM || ']' as source_system, '[Source].[Bank Statements].[' || SOURCE || ']' AS source, '[Flow Indicator].[Bank Balance]' as flow_indicator, '[Reconciliation Status].[Not Applicable]' as recon_status, '[Currency Type].[Reporting Currency]' AS currency_type, '[Currency].[' || nvl(REPORTING_CURRENCY,'USD') || ']' as CURRENCY, '[Type].[Balance Type].[Estimated Balance]' AS type, '[Time].[Last Known]' as fact_date, (CLOSE_LEDGER_RC + nvl(bal_correction_rc,0)) as amount, nvl(EXT_DIM_attribute1,'NO_VALUE') as EXT_DIM_attribute1, nvl(EXT_DIM_attribute2,'NO_VALUE') as EXT_DIM_attribute2, nvl(EXT_DIM_attribute3,'NO_VALUE') as EXT_DIM_attribute3, nvl(EXT_DIM_attribute4,'NO_VALUE') as EXT_DIM_attribute4, nvl(EXT_DIM_attribute5,'NO_VALUE') as EXT_DIM_attribute5, nvl(EXT_DIM_attribute6,'NO_VALUE') as EXT_DIM_attribute6, nvl(EXT_DIM_attribute7,'NO_VALUE') as EXT_DIM_attribute7, nvl(EXT_DIM_attribute8,'NO_VALUE') as EXT_DIM_attribute8, nvl(EXT_DIM_attribute9,'NO_VALUE') as EXT_DIM_attribute9, nvl(EXT_DIM_attribute10,'NO_VALUE') as EXT_DIM_attribute10, nvl(EXT_DIM_attribute11,'NO_VALUE') as EXT_DIM_attribute11, nvl(EXT_DIM_attribute12,'NO_VALUE') as EXT_DIM_attribute12, nvl(EXT_DIM_attribute13,'NO_VALUE') as EXT_DIM_attribute13, nvl(EXT_DIM_attribute14,'NO_VALUE') as EXT_DIM_attribute14, nvl(EXT_DIM_attribute15,'NO_VALUE') as EXT_DIM_attribute15, CASH_POOL_NAME, PARENT_CASH_POOL_NAME FROM CE_CP_FACTS, (SELECT stmt_facts.bank_Account_name as ban,stmt_facts.bank_name as ba,stmt_facts.source_system as ss, stmt_facts.source as src, SUM(DECODE(FLOW_INDICATOR,'Inflow',TRX_AMOUNT_RC,-TRX_AMOUNT_RC)) as BAL_CORRECTION_RC FROM CE_CP_FACTS trx_facts, (SELECT BANK_NAME,BANK_ACCOUNT_NAME, SOURCE_SYSTEM, SOURCE, (CLBD_BALANCE_DATE+1) as bal_date FROM CE_CP_FACTS WHERE LAST_KNOWN_BAL_FLAG='E' and CLOSE_LEDGER_AC is not null) stmt_facts where trx_facts.SOURCE IN ('AP Payments','AR Receipts','AP Invoices','AR Invoices', 'External Transactions','Payroll') and trx_facts.bank_Account_name = stmt_facts.bank_Account_name and trx_facts.bank_name = stmt_facts.bank_name and trx_facts.source_system = stmt_facts.source_system AND TRX_DATE >= bal_date and TRX_DATE <= trunc(SYSDATE)-1 group by stmt_facts.bank_account_name,stmt_facts.bank_name, stmt_facts.source_system, stmt_facts.source) corrected_balances WHERE BANK_NAME=BA(+) and BANK_ACCOUNT_NAME=BAN(+) and SOURCE_SYSTEM=SS(+) and SOURCE = SRC(+) and LAST_KNOWN_BAL_FLAG='E' UNION ALL SELECT bank_account_name, '[Bank].[' || NVL(bank_name, 'NO_BANK_VALUE') || '].[' || NVL(bank_account_name, 'NO_BANK_ACCOUNT_VALUE') || ']' AS bank, '[Legal Entity].[' || NVL(legal_entity_region, 'NO_LE_REGION_VALUE') || '].[' || NVL(legal_entity_name, 'NO_LEGAL_ENTITY_VALUE') || ']' AS legal_entity, '[Application].[' || SOURCE_SYSTEM || ']' as source_system, '[Source].[Bank Statements].[' || SOURCE || ']' AS source, '[Flow Indicator].[Bank Balance]' as flow_indicator, '[Reconciliation Status].[Not Applicable]' as recon_status, '[Currency Type].[Ledger Currency]' AS currency_type, '[Currency].[' || nvl(FUNCTIONAL_CURRENCY,'NO_CURR_VALUE') || ']' as CURRENCY, '[Type].[Balance Type].[Days Since Last Statement]' AS type, '[Time].[Last Known]' as fact_date, CE_CASH_UTIL.get_business_days(TRANSACTION_CALENDAR_ID,BALANCE_DATE+1,trunc(SYSDATE)) as AMOUNT, nvl(EXT_DIM_attribute1,'NO_VALUE') as EXT_DIM_attribute1, nvl(EXT_DIM_attribute2,'NO_VALUE') as EXT_DIM_attribute2, nvl(EXT_DIM_attribute3,'NO_VALUE') as EXT_DIM_attribute3, nvl(EXT_DIM_attribute4,'NO_VALUE') as EXT_DIM_attribute4, nvl(EXT_DIM_attribute5,'NO_VALUE') as EXT_DIM_attribute5, nvl(EXT_DIM_attribute6,'NO_VALUE') as EXT_DIM_attribute6, nvl(EXT_DIM_attribute7,'NO_VALUE') as EXT_DIM_attribute7, nvl(EXT_DIM_attribute8,'NO_VALUE') as EXT_DIM_attribute8, nvl(EXT_DIM_attribute9,'NO_VALUE') as EXT_DIM_attribute9, nvl(EXT_DIM_attribute10,'NO_VALUE') as EXT_DIM_attribute10, nvl(EXT_DIM_attribute11,'NO_VALUE') as EXT_DIM_attribute11, nvl(EXT_DIM_attribute12,'NO_VALUE') as EXT_DIM_attribute12, nvl(EXT_DIM_attribute13,'NO_VALUE') as EXT_DIM_attribute13, nvl(EXT_DIM_attribute14,'NO_VALUE') as EXT_DIM_attribute14, nvl(EXT_DIM_attribute15,'NO_VALUE') as EXT_DIM_attribute15, CASH_POOL_NAME, PARENT_CASH_POOL_NAME FROM CE_CP_FACTS WHERE LAST_KNOWN_BAL_FLAG='Y' and BALANCE_AC is not null UNION ALL SELECT bank_account_name, '[Bank].[' || NVL(bank_name, 'NO_BANK_VALUE') || '].[' || NVL(bank_account_name, 'NO_BANK_ACCOUNT_VALUE') || ']' AS bank, '[Legal Entity].[' || NVL(legal_entity_region, 'NO_LE_REGION_VALUE') || '].[' || NVL(legal_entity_name, 'NO_LEGAL_ENTITY_VALUE') || ']' AS legal_entity, '[Application].[' || SOURCE_SYSTEM || ']' as source_system, '[Source].[Bank Statements].[' || SOURCE || ']' AS source, '[Flow Indicator].[Bank Balance]' as flow_indicator, '[Reconciliation Status].[Not Applicable]' as recon_status, '[Currency Type].[Account Currency]' AS currency_type, '[Currency].[' || nvl(BANK_ACCOUNT_CURRENCY,'NO_CURR_VALUE') || ']' as CURRENCY, '[Type].[Balance Type].[Days Since Last Statement]' AS type, '[Time].[Last Known]' as fact_date, CE_CASH_UTIL.get_business_days(TRANSACTION_CALENDAR_ID,BALANCE_DATE+1,trunc(SYSDATE)) as AMOUNT, nvl(EXT_DIM_attribute1,'NO_VALUE') as EXT_DIM_attribute1, nvl(EXT_DIM_attribute2,'NO_VALUE') as EXT_DIM_attribute2, nvl(EXT_DIM_attribute3,'NO_VALUE') as EXT_DIM_attribute3, nvl(EXT_DIM_attribute4,'NO_VALUE') as EXT_DIM_attribute4, nvl(EXT_DIM_attribute5,'NO_VALUE') as EXT_DIM_attribute5, nvl(EXT_DIM_attribute6,'NO_VALUE') as EXT_DIM_attribute6, nvl(EXT_DIM_attribute7,'NO_VALUE') as EXT_DIM_attribute7, nvl(EXT_DIM_attribute8,'NO_VALUE') as EXT_DIM_attribute8, nvl(EXT_DIM_attribute9,'NO_VALUE') as EXT_DIM_attribute9, nvl(EXT_DIM_attribute10,'NO_VALUE') as EXT_DIM_attribute10, nvl(EXT_DIM_attribute11,'NO_VALUE') as EXT_DIM_attribute11, nvl(EXT_DIM_attribute12,'NO_VALUE') as EXT_DIM_attribute12, nvl(EXT_DIM_attribute13,'NO_VALUE') as EXT_DIM_attribute13, nvl(EXT_DIM_attribute14,'NO_VALUE') as EXT_DIM_attribute14, nvl(EXT_DIM_attribute15,'NO_VALUE') as EXT_DIM_attribute15, CASH_POOL_NAME, PARENT_CASH_POOL_NAME FROM CE_CP_FACTS WHERE LAST_KNOWN_BAL_FLAG='Y' and BALANCE_AC is not null UNION ALL SELECT bank_account_name, '[Bank].[' || NVL(bank_name, 'NO_BANK_VALUE') || '].[' || NVL(bank_account_name, 'NO_BANK_ACCOUNT_VALUE') || ']' AS bank, '[Legal Entity].[' || NVL(legal_entity_region, 'NO_LE_REGION_VALUE') || '].[' || NVL(legal_entity_name, 'NO_LEGAL_ENTITY_VALUE') || ']' AS legal_entity, '[Application].[' || SOURCE_SYSTEM || ']' as source_system, '[Source].[Bank Statements].[' || SOURCE || ']' AS source, '[Flow Indicator].[Bank Balance]' as flow_indicator, '[Reconciliation Status].[Not Applicable]' as recon_status, '[Currency Type].[Reporting Currency]' AS currency_type, '[Currency].[' || nvl(REPORTING_CURRENCY,'USD') || ']' as CURRENCY, '[Type].[Balance Type].[Days Since Last Statement]' AS type, '[Time].[Last Known]' as fact_date, CE_CASH_UTIL.get_business_days(TRANSACTION_CALENDAR_ID,BALANCE_DATE+1,trunc(SYSDATE)) as AMOUNT, nvl(EXT_DIM_attribute1,'NO_VALUE') as EXT_DIM_attribute1, nvl(EXT_DIM_attribute2,'NO_VALUE') as EXT_DIM_attribute2, nvl(EXT_DIM_attribute3,'NO_VALUE') as EXT_DIM_attribute3, nvl(EXT_DIM_attribute4,'NO_VALUE') as EXT_DIM_attribute4, nvl(EXT_DIM_attribute5,'NO_VALUE') as EXT_DIM_attribute5, nvl(EXT_DIM_attribute6,'NO_VALUE') as EXT_DIM_attribute6, nvl(EXT_DIM_attribute7,'NO_VALUE') as EXT_DIM_attribute7, nvl(EXT_DIM_attribute8,'NO_VALUE') as EXT_DIM_attribute8, nvl(EXT_DIM_attribute9,'NO_VALUE') as EXT_DIM_attribute9, nvl(EXT_DIM_attribute10,'NO_VALUE') as EXT_DIM_attribute10, nvl(EXT_DIM_attribute11,'NO_VALUE') as EXT_DIM_attribute11, nvl(EXT_DIM_attribute12,'NO_VALUE') as EXT_DIM_attribute12, nvl(EXT_DIM_attribute13,'NO_VALUE') as EXT_DIM_attribute13, nvl(EXT_DIM_attribute14,'NO_VALUE') as EXT_DIM_attribute14, nvl(EXT_DIM_attribute15,'NO_VALUE') as EXT_DIM_attribute15, CASH_POOL_NAME, PARENT_CASH_POOL_NAME FROM CE_CP_FACTS WHERE LAST_KNOWN_BAL_FLAG='Y' and BALANCE_AC is not null ) SELECT bank, legal_entity, source_system, source, flow_indicator, recon_status, currency_type, currency, type, fact_date, amount, 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(+)