My Oracle Support Banner

FN_ACCT_CASH_FLOW_POPULA_BDE Execution Observations (Doc ID 2957476.1)

Last updated on JULY 02, 2023

Applies to:

Oracle Financial Services Liquidity Risk Measurement and Management - Version 8.0.7 and later
Information in this document applies to any platform.

Goal

In LRMS 8.1.2.2, US contractual run is failing on DT FN_ACCT_CASH_FLOW_POPULA_BDE.

Incorrect order of parameters are passed to the insert statement and the v_query is showing incorrect SQL statement and function return is successful even though it fails on the insert statement. 

 Below is the statement

COALESCE( SUM(CASE WHEN DIM_CASH_FLOW_TYPE.V_CASH_FLOW_TYPE_CODE = 'P' THEN CASE WHEN DIM_PRODUCT.V_BALANCE_SHEET_CATEGORY IN ('ASSET') AND FP1.N_TOT_ROFF_POSV >= 0 THEN FP1.N_TOT_ROFF_POSV * VW_LRM_CCY_CONV_FACTOR_RCY.N_EXCHANGE_RATE WHEN DIM_PRODUCT.V_BALANCE_SHEET_CATEGORY IN ('OFF BALANCE SHEET') AND N_RCV_RCV_LEG_ACCT_SKEY IS NOT NULL AND FP1.N_TOT_ROFF_POSV >= 0 THEN FP1.N_TOT_ROFF_POSV * VW_LRM_CCY_CONV_FACTOR_RCY.N_EXCHANGE_RATE WHEN DIM_PRODUCT.V_BALANCE_SHEET_CATEGORY IN ('OFF BALANCE SHEET') AND N_PAY_RCV_LEG_ACCT_SKEY IS NOT NULL AND FP1.N_TOT_ROFF_POSV <= 0 THEN ABS(FP1.N_TOT_ROFF_POSV) * VW_LRM_CCY_CONV_FACTOR_RCY.N_EXCHANGE_RATE WHEN DIM_PRODUCT.V_BALANCE_SHEET_CATEGORY IN ('LIABILITY') AND FP1.N_TOT_ROFF_POSV <= 0 THEN ABS(FP1.N_TOT_ROFF_POSV) * VW_LRM_CCY_CONV_FACTOR_RCY.N_EXCHANGE_RATE END WHEN DIM_CASH_FLOW_TYPE.V_CASH_FLOW_TYPE_CODE = 'I' THEN CASE WHEN DIM_PRODUCT.V_BALANCE_SHEET_CATEGORY IN ('ASSET') AND FP1.N_INT_CASH_FLOW >= 0 THEN FP1.N_INT_CASH_FLOW * VW_LRM_CCY_CONV_FACTOR_RCY.N_EXCHANGE_RATE WHEN DIM_PRODUCT.V_BALANCE_SHEET_CATEGORY IN ('OFF BALANCE SHEET') AND N_RCV_RCV_LEG_ACCT_SKEY IS NOT NULL AND FP1.N_INT_CASH_FLOW >= 0 THEN FP1.N_INT_CASH_FLOW * VW_LRM_CCY_CONV_FACTOR_RCY.N_EXCHANGE_RATE
2 WHEN DIM_PRODUCT.V_BALANCE_SHEET_CATEGORY IN ('OFF BALANCE SHEET') AND N_PAY_RCV_LEG_ACCT_SKEY IS NOT NULL AND FP1.N_INT_CASH_FLOW <= 0 THEN ABS(FP1.N_INT_CASH_FLOW) * VW_LRM_CCY_CONV_FACTOR_RCY.N_EXCHANGE_RATE WHEN DIM_PRODUCT.V_BALANCE_SHEET_CATEGORY IN ('LIABILITY') AND FP1.N_INT_CASH_FLOW <= 0 THEN ABS(FP1.N_INT_CASH_FLOW) * VW_LRM_CCY_CONV_FACTOR_RCY.N_EXCHANGE_RATE END WHEN DIM_CASH_FLOW_TYPE.V_CASH_FLOW_TYPE_CODE = 'DR' THEN CASE WHEN DIM_PRODUCT.V_BALANCE_SHEET_CATEGORY IN ('ASSET') THEN FP1.N_DIVIDENDS * VW_LRM_CCY_CONV_FACTOR_RCY.N_EXCHANGE_RATE WHEN DIM_PRODUCT.V_BALANCE_SHEET_CATEGORY IN ('OFF BALANCE SHEET') AND N_RCV_RCV_LEG_ACCT_SKEY IS NOT NULL THEN FP1.N_DIVIDENDS * VW_LRM_CCY_CONV_FACTOR_RCY.N_EXCHANGE_RATE END END), 0) - COALESCE( SUM(CASE WHEN DIM_CASH_FLOW_TYPE.V_CASH_FLOW_TYPE_CODE = 'P' THEN CASE WHEN DIM_PRODUCT.V_BALANCE_SHEET_CATEGORY IN ('ASSET') AND FP1.N_TOT_ROFF_POSV <= 0 THEN ABS(FP1.N_TOT_ROFF_POSV) * VW_LRM_CCY_CONV_FACTOR_RCY.N_EXCHANGE_RATE WHEN DIM_PRODUCT.V_BALANCE_SHEET_CATEGORY IN ('OFF BALANCE SHEET') AND N_PAY_RCV_LEG_ACCT_SKEY IS NOT NULL AND FP1.N_TOT_ROFF_POSV >= 0 THEN FP1.N_TOT_ROFF_POSV * VW_LRM_CCY_CONV_FACTOR_RCY.N_EXCHANGE_RATE WHEN DIM_PRODUCT.V_BALANCE_SHEET_CATEGORY IN ('OFF BALANCE SHEET') AND N_RCV_RCV_LEG_ACCT_SKEY IS NOT NULL AND FP1.N_TOT_ROFF_POSV <= 0 THEN ABS(FP1.N_TOT_ROFF_POSV) * VW_LRM_CCY_CONV_FACTOR_RCY.N_EXCHANGE_RATE WHEN DIM_PRODUCT.V_BALANCE_SHEET_CATEGORY IN ('LIABILITY') AND FP1.N_TOT_ROFF_POSV >= 0 THEN FP1.N_TOT_ROFF_POSV * VW_LRM_CCY_CONV_FACTOR_RCY.N_EXCHANGE_RATE END WHEN DIM_CASH_FLOW_TYPE.V_CASH_FLOW_TYPE_CODE = 'I' THEN CASE WHEN DIM_PRODUCT.V_BALANCE_SHEET_CATEGORY IN ('ASSET') AND FP1.N_INT_CASH_FLOW <= 0 THEN ABS(FP1.N_INT_CASH_FLOW) * VW_LRM_CCY_CONV_FACTOR_RCY.N_EXCHANGE_RATE
3 WHEN DIM_PRODUCT.V_BALANCE_SHEET_CATEGORY IN ('OFF BALANCE SHEET') AND N_RCV_RCV_LEG_ACCT_SKEY IS NOT NULL AND FP1.N_INT_CASH_FLOW <= 0 THEN ABS(FP1.N_INT_CASH_FLOW) * VW_LRM_CCY_CONV_FACTOR_RCY.N_EXCHANGE_RATE WHEN DIM_PRODUCT.V_BALANCE_SHEET_CATEGORY IN ('OFF BALANCE SHEET') AND N_PAY_RCV_LEG_ACCT_SKEY IS NOT NULL AND FP1.N_INT_CASH_FLOW >= 0 THEN FP1.N_INT_CASH_FLOW * VW_LRM_CCY_CONV_FACTOR_RCY.N_EXCHANGE_RATE WHEN DIM_PRODUCT.V_BALANCE_SHEET_CATEGORY IN ('LIABILITY') AND FP1.N_INT_CASH_FLOW >= 0 THEN FP1.N_INT_CASH_FLOW * VW_LRM_CCY_CONV_FACTOR_RCY.N_EXCHANGE_RATE END WHEN DIM_CASH_FLOW_TYPE.V_CASH_FLOW_TYPE_CODE = 'DR' THEN CASE WHEN DIM_PRODUCT.V_BALANCE_SHEET_CATEGORY IN ('LIABILITY') THEN FP1.N_DIVIDENDS * VW_LRM_CCY_CONV_FACTOR_RCY.N_EXCHANGE_RATE WHEN DIM_PRODUCT.V_BALANCE_SHEET_CATEGORY IN ('OFF BALANCE SHEET') AND N_PAY_RCV_LEG_ACCT_SKEY IS NOT NULL THEN FP1.N_DIVIDENDS * VW_LRM_CCY_CONV_FACTOR_RCY.N_EXCHANGE_RATE END END),0) N_NET_AMOUNT_RCY

Solution

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Goal
Solution
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.