INSERT /*+enable_parallel_dml append parallel(8)*/ INTO FCT_AGG_CASH_FLOWS ( N_AGG_CASH_FLOW_SKEY, N_AS_OF_DATE_SKEY, N_CASH_FLOW_TYPE_SKEY, N_RESULT_BUCKET_SKEY, N_RUN_SKEY, F_CNTRL_BY_TREASURY_FLAG, N_ASSET_LEVEL_SKEY, F_ACCOUNT_FORWARD_START_FLAG, F_ACCT_DWNGRDE_TRIGGER_FLAG, F_ACCT_FULLY_COV_INSU_SCHEME, F_AFFILIATE_BRKR_SWEEP_DEP_FLG, F_BROKER_INVOLVED_FLAG, F_CASH_COMINGLING_FLAG, F_COLLATERAL_COVER_SHORT_POS, F_COLL_SEGREGATED_FLAG, F_COVERING_BANKS_SHORT_POS, F_CST_DEP_OR_CNSL_SUB_DEP_INST, F_CUSTOMER_AFFILIATE_FLAG, F_CUSTOMER_CHILD_FLAG, F_CUST_CON_SUB_FIN_SECTOR_ENT, F_CUST_DEPSITRY_INSTITUTN_FLAG, F_CUST_FINANCIAL_ENTITY_FLAG, F_CUST_FIN_OR_CNSL_SUB_FIN_ENT, F_CUST_SOV_MDB_USGSE_FLAG, F_DOMESTIC_CUSTOMER_FLAG, F_EFF_INSURANCE_SCHEME_FLAG, F_EMBEDDED_OPTIONS_FLAG, F_ESCROW_ACCOUNT_FLAG, F_ESTABLISHED_RELATIONSHIP, F_EXP_ONE_TO_FOUR_FAMILY, F_FULL_PERFORMING_ASSET_FLAG, F_GUARANTOR_US_FLAG, F_HIGH_STABILITY_FLAG, F_HOME_JURISDICTION_FLAG, F_HQLA_COLL_SUBSTITUTION_FLAG, F_HQLA_ELIGIBLITY_FLAG, F_INSTITUTIONAL_NETWORK_FLAG, F_INTRA_BANK_FLAG, F_ISSUER_PRIMARY_MARKET_MAKER, F_ISSUER_SUBSIDIARY_FLAG, F_ISSUER_US_FLAG, F_LARGE_CUSTOMER_FLAG, F_MIT_REHYP_GRT_ORG_MAT_LS_HOR, F_NETTING_AGREEMENT_FLAG, F_NON_MAT_ACCT_IND, F_NON_OPT_NON_BRK_DEP_WHSL_DEP, F_OPERATIONAL_ACCOUNT_FLAG, F_REHYPOTHECATED_FLAG, F_RESIDUAL_MAT_LESS_LIQ_HORZ, F_SECURED_FLAG, F_SELL_FLAG, F_SOLD_EXCLUSIVELY_IN_RET_MKT, F_STRUCTURED_DEBT_FLAG, F_THIRD_PARTY_PLACED_FLAG, F_TRADE_RELATED_OBLIGATIONS, F_TRANSACTIONAL_ACCT_FLAG, F_UNDERLY_COLL_COVER_SHORT_POS, F_UNDRLYNG_ASST_SEGREGATED_IND, F_UNDRLYNG_RECV_HQLA_FLAG, KS_EXCLUSIVE_CUSTOMER, KS_INTHEMONEY_FLAG, KS_KOL_FLAG, KS_LIEN_FLAG, KS_LOAN_RELATIONSHIP_FLAG, KS_OTH_FACILITY_FLAG, KS_TDR_STATUS_FLG, N_ACCOUNT_RISK_WEIGHT_SKEY, N_ALMCOA_SKEY, N_BROKER_DEPOSIT_TYPE_SKEY, N_COLL_SUBSTITU_ASSET_LVL_SKEY, N_CREDIT_LINE_PURPOSE_CAT_SKEY, N_CREDIT_LINE_PURPOSE_SKEY, N_CUST_SKEY, N_EFF_RESIDUAL_MAT_BAND_SKEY, N_ENTITY_SKEY, N_INSUR_SCHE_COVER_TYPE_SKEY, N_LOB_SKEY, N_NETTING_AGREEMENT_SKEY, N_NON_CONTRA_OBLIG_TYPE_SKEY, N_PROD_SKEY, N_RESIDUAL_MATURITY_BAND_SKEY, N_RESIDUAL_MATURITY_TB_SKEY, N_REVOCABLE_STATUS_SKEY, N_SBSTBL_ASST_LVL_ENT_SKEY, N_STANDARD_CUSTOMER_TYPE_SKEY, N_STANDARD_PRODUCT_TYPE_SKEY, N_UNDERLYING_ASSET_LEVEL_SKEY, N_UNDERLY_RECV_ASSET_LVL_SKEY, V_ISO_CURRENCY_CD, V_OPTION_IN_OUT_AT_MONEY_IND, V_WHOLESALE_RETAIL_IND, N_ACCT_STATUS_SKEY, N_INFLOW_AMT, N_INFLOW_AMT_RCY, N_OUTFLOW_AMT, N_OUTFLOW_AMT_RCY, N_LARGE_CUST_RANK ) WITH VW_CCY_CONV_FACTOR AS ------ below step just finds out the exchange rate, we can remove the inner join but just make sure that there is runskey stuff---------- (SELECT FCT_FCST_EXCHANGE_RATES.V_FROM_CURRENCY, FCT_FCST_EXCHANGE_RATES.V_TO_CURRENCY, FCT_FCST_EXCHANGE_RATES.N_EXCHANGE_RATE, FCT_LRM_TIME_BUCKET_DETAILS.N_ENTITY_SKEY FROM FCT_FCST_EXCHANGE_RATES INNER JOIN FCT_LRM_TIME_BUCKET_DETAILS ON FCT_FCST_EXCHANGE_RATES.N_BUCKET_SKEY = FCT_LRM_TIME_BUCKET_DETAILS.N_RESULT_BUCKET_SKEY AND FCT_FCST_EXCHANGE_RATES.N_RUN_SKEY = FCT_LRM_TIME_BUCKET_DETAILS.N_RUN_SKEY AND FCT_FCST_EXCHANGE_RATES.N_AS_OF_DATE_SKEY = FCT_LRM_TIME_BUCKET_DETAILS.N_MIS_DATE_SKEY AND FCT_FCST_EXCHANGE_RATES.N_RUN_SKEY = 451 AND FCT_FCST_EXCHANGE_RATES.N_AS_OF_DATE_SKEY = 20161130 WHERE FCT_LRM_TIME_BUCKET_DETAILS.F_OVERNIGHT_BUCKET_FLAG = 'Y' AND FCT_LRM_TIME_BUCKET_DETAILS.F_COMPUTATIONAL_TIME_BKT_FLAG = 'Y' ) SELECT /*+ FULL(FSI_LRM_INSTRUMENT) parallel(FSI_LRM_INSTRUMENT 8) parallel(FCT_ACCOUNT_CASH_FLOWS 8) parallel(FSI_ACCT_COMPOSITE_KEY_MAP 8) */ FSI_ACCT_COMPOSITE_KEY_MAP.N_COMPOSITE_SKEY, FCT_ACCOUNT_CASH_FLOWS.N_AS_OF_DATE_SKEY, FCT_ACCOUNT_CASH_FLOWS.N_CASH_FLOW_TYPE_SKEY, FCT_ACCOUNT_CASH_FLOWS.N_RESULT_BUCKET_SKEY, FCT_ACCOUNT_CASH_FLOWS.N_RUN_SKEY, MAX(FSI_LRM_INSTRUMENT.F_CNTRL_BY_TREASURY_FLAG), MAX(COALESCE(FSI_LRM_INSTRUMENT.N_ASSET_LEVEL_SKEY,2)), MAX(FSI_LRM_INSTRUMENT.F_ACCOUNT_FORWARD_START_FLAG), MAX(FSI_LRM_INSTRUMENT.F_ACCT_DWNGRDE_TRIGGER_FLAG), MAX(FSI_LRM_INSTRUMENT.F_ACCT_FULLY_COV_INSU_SCHEME), MAX(FSI_LRM_INSTRUMENT.F_AFFILIATE_BRKR_SWEEP_DEP_FLG), MAX(FSI_LRM_INSTRUMENT.F_BROKER_INVOLVED_FLAG), MAX(FSI_LRM_INSTRUMENT.F_CASH_COMINGLING_FLAG), MAX(FSI_LRM_INSTRUMENT.F_COLLATERAL_COVER_SHORT_POS), MAX(FSI_LRM_INSTRUMENT.F_COLL_SEGREGATED_FLAG), MAX(FSI_LRM_INSTRUMENT.F_COVERING_BANKS_SHORT_POS), MAX(FSI_LRM_INSTRUMENT.F_CST_DEP_OR_CNSL_SUB_DEP_INST), MAX(FSI_LRM_INSTRUMENT.F_CUSTOMER_AFFILIATE_FLAG), MAX(FSI_LRM_INSTRUMENT.F_CUSTOMER_CHILD_FLAG), MAX(FSI_LRM_INSTRUMENT.F_CUST_CON_SUB_FIN_SECTOR_ENT), MAX(FSI_LRM_INSTRUMENT.F_CUST_DEPSITRY_INSTITUTN_FLAG), MAX(FSI_LRM_INSTRUMENT.F_CUST_FINANCIAL_ENTITY_FLAG), MAX(FSI_LRM_INSTRUMENT.F_CUST_FIN_OR_CNSL_SUB_FIN_ENT), MAX(FSI_LRM_INSTRUMENT.F_CUST_SOV_MDB_USGSE_FLAG), MAX(FSI_LRM_INSTRUMENT.F_DOMESTIC_CUSTOMER_FLAG), MAX(FSI_LRM_INSTRUMENT.F_EFF_INSURANCE_SCHEME_FLAG), MAX(FSI_LRM_INSTRUMENT.F_EMBEDDED_OPTIONS_FLAG), MAX(FSI_LRM_INSTRUMENT.F_ESCROW_ACCOUNT_FLAG), MAX(FSI_LRM_INSTRUMENT.F_ESTABLISHED_RELATIONSHIP), MAX(FSI_LRM_INSTRUMENT.F_EXP_ONE_TO_FOUR_FAMILY), MAX(FSI_LRM_INSTRUMENT.F_FULL_PERFORMING_ASSET_FLAG), MAX(FSI_LRM_INSTRUMENT.F_GUARANTOR_US_FLAG), MAX(FSI_LRM_INSTRUMENT.F_HIGH_STABILITY_FLAG), MAX(FSI_LRM_INSTRUMENT.F_HOME_JURISDICTION_FLAG), MAX(FSI_LRM_INSTRUMENT.F_HQLA_COLL_SUBSTITUTION_FLAG), MAX(FSI_LRM_INSTRUMENT.F_HQLA_ELIGIBILITY_FLAG), MAX(FSI_LRM_INSTRUMENT.F_INSTITUTIONAL_NETWORK_FLAG), MAX(FSI_LRM_INSTRUMENT.F_INTRA_BANK_FLAG), MAX(FSI_LRM_INSTRUMENT.F_ISSUER_PRIMARY_MARKET_MAKER), MAX(FSI_LRM_INSTRUMENT.F_ISSUER_SUBSIDIARY_FLAG), MAX(FSI_LRM_INSTRUMENT.F_ISSUER_US_FLAG), MAX(FSI_LRM_INSTRUMENT.F_LARGE_CUSTOMER_FLAG), MAX(FSI_LRM_INSTRUMENT.F_MIT_REHYP_GRT_ORG_MAT_LS_HOR), MAX(FSI_LRM_INSTRUMENT.F_NETTING_AGREEMENT_FLAG), MAX(FSI_LRM_INSTRUMENT.F_NON_MAT_ACCT_IND), MAX(FSI_LRM_INSTRUMENT.F_NON_OPT_NON_BRK_DEP_WHSL_DEP), MAX(FSI_LRM_INSTRUMENT.F_OPERATIONAL_ACCOUNT_FLAG), MAX(FSI_LRM_INSTRUMENT.F_REHYPOTHECATED_FLAG), MAX(FSI_LRM_INSTRUMENT.F_RESIDUAL_MAT_LESS_LIQ_HORZ), MAX(FSI_LRM_INSTRUMENT.F_SECURED_FLAG), MAX(FSI_LRM_INSTRUMENT.F_SELL_FLAG), MAX(FSI_LRM_INSTRUMENT.F_SOLD_EXCLUSIVELY_IN_RET_MKT), MAX(FSI_LRM_INSTRUMENT.F_STRUCTURED_DEBT_FLAG), MAX(FSI_LRM_INSTRUMENT.F_THIRD_PARTY_PLACED_FLAG), MAX(FSI_LRM_INSTRUMENT.F_TRADE_RELATED_OBLIGATIONS), MAX(FSI_LRM_INSTRUMENT.F_TRANSACTIONAL_ACCT_FLAG), MAX(FSI_LRM_INSTRUMENT.F_UNDERLY_COLL_COVER_SHORT_POS), MAX(FSI_LRM_INSTRUMENT.F_UNDRLYNG_ASST_SEGREGATED_IND), MAX(FSI_LRM_INSTRUMENT.F_UNDRLYNG_RECV_HQLA_FLAG), MAX(FSI_LRM_INSTRUMENT.KS_EXCLUSIVE_CUSTOMER), MAX(FSI_LRM_INSTRUMENT.KS_INTHEMONEY_FLAG), MAX(FSI_LRM_INSTRUMENT.KS_KOL_FLAG), MAX(FSI_LRM_INSTRUMENT.KS_LIEN_FLAG), MAX(FSI_LRM_INSTRUMENT.KS_LOAN_RELATIONSHIP_FLAG), MAX(FSI_LRM_INSTRUMENT.KS_OTH_FACILITY_FLAG), MAX(FSI_LRM_INSTRUMENT.KS_TDR_STATUS_FLG), MAX(FSI_LRM_INSTRUMENT.N_ACCOUNT_RISK_WEIGHT_SKEY), MAX(FSI_LRM_INSTRUMENT.N_ALMCOA_SKEY), MAX(FSI_LRM_INSTRUMENT.N_BROKER_DEPOSIT_TYPE_SKEY), MAX(FSI_LRM_INSTRUMENT.N_COLL_SUBSTITU_ASSET_LVL_SKEY), MAX(FSI_LRM_INSTRUMENT.N_CREDIT_LINE_PURPOSE_CAT_SKEY), MAX(FSI_LRM_INSTRUMENT.N_CREDIT_LINE_PURPOSE_SKEY), MAX( CASE WHEN 'Y' IN (FSI_LRM_INSTRUMENT.F_INTRA_BANK_FLAG,FSI_LRM_INSTRUMENT.F_LARGE_CUSTOMER_FLAG) THEN FSI_LRM_INSTRUMENT.N_CUSTOMER_SKEY ELSE 0 END), MAX(FSI_LRM_INSTRUMENT.N_EFF_RESIDUAL_MAT_BAND_SKEY), MAX(FSI_LRM_INSTRUMENT.N_ENTITY_SKEY), MAX(FSI_LRM_INSTRUMENT.N_INSUR_SCHE_COVER_TYPE_SKEY), MAX(FSI_LRM_INSTRUMENT.N_LOB_SKEY), MAX(FSI_LRM_INSTRUMENT.N_NETTING_AGREEMENT_SKEY), MAX(FSI_LRM_INSTRUMENT.N_NON_CONTRA_OBLIG_TYPE_SKEY), MAX(FSI_LRM_INSTRUMENT.N_PRODUCT_SKEY), MAX(FSI_LRM_INSTRUMENT.N_RESIDUAL_MATURITY_BAND_SKEY), MAX(FSI_LRM_INSTRUMENT.N_RESIDUAL_MATURITY_TB_SKEY), MAX(FSI_LRM_INSTRUMENT.N_REVOCABLE_STATUS_SKEY), MAX(FSI_LRM_INSTRUMENT.N_SBSTBL_ASST_LVL_ENT_SKEY), MAX(FSI_LRM_INSTRUMENT.N_STANDARD_CUSTOMER_TYPE_SKEY), MAX(FSI_LRM_INSTRUMENT.N_STANDARD_PRODUCT_TYPE_SKEY), MAX(FSI_LRM_INSTRUMENT.N_UNDERLYING_ASSET_LEVEL_SKEY), MAX(FSI_LRM_INSTRUMENT.N_UNDERLY_RECV_ASSET_LVL_SKEY), MAX(FCT_ACCOUNT_CASH_FLOWS.V_ISO_CURRENCY_CD), MAX(FSI_LRM_INSTRUMENT.V_OPTION_IN_OUT_AT_MONEY_IND), MAX(FSI_LRM_INSTRUMENT.V_WHOLESALE_RETAIL_IND), MAX(FSI_LRM_INSTRUMENT.N_ACCT_STATUS_SKEY), SUM(N_INFLOW_AMT), SUM( CASE WHEN FSI_LRM_INSTRUMENT.F_NETTING_AGREEMENT_FLAG = 'Y' THEN (N_INFLOW_AMT * VW_LRM_CCY_CONV_FACTOR_RCY.N_EXCHANGE_RATE) - (N_OUTFLOW_AMT * VW_LRM_CCY_CONV_FACTOR_RCY.N_EXCHANGE_RATE) ELSE (N_INFLOW_AMT * VW_LRM_CCY_CONV_FACTOR_RCY.N_EXCHANGE_RATE) END ), SUM(N_OUTFLOW_AMT), SUM( CASE WHEN FSI_LRM_INSTRUMENT.F_NETTING_AGREEMENT_FLAG = 'Y' THEN (N_OUTFLOW_AMT * VW_LRM_CCY_CONV_FACTOR_RCY.N_EXCHANGE_RATE) - (N_INFLOW_AMT * VW_LRM_CCY_CONV_FACTOR_RCY.N_EXCHANGE_RATE) ELSE (N_OUTFLOW_AMT * VW_LRM_CCY_CONV_FACTOR_RCY.N_EXCHANGE_RATE) END), MAX(FSI_LRM_INSTRUMENT.N_LARGE_CUST_RANK) FROM FSI_LRM_INSTRUMENT INNER JOIN FCT_ACCOUNT_CASH_FLOWS ON FSI_LRM_INSTRUMENT.N_LRM_INSTRUMENT_ID = FCT_ACCOUNT_CASH_FLOWS.N_ACCT_SKEY AND FSI_LRM_INSTRUMENT.N_RUN_SKEY = 451 AND FSI_LRM_INSTRUMENT.N_RUN_SKEY = FCT_ACCOUNT_CASH_FLOWS.N_RUN_SKEY AND FSI_LRM_INSTRUMENT.N_AS_OF_DATE_SKEY = FCT_ACCOUNT_CASH_FLOWS.N_AS_OF_DATE_SKEY AND FSI_LRM_INSTRUMENT.N_AS_OF_DATE_SKEY = 20161130 INNER JOIN FSI_ACCT_COMPOSITE_KEY_MAP ON FSI_LRM_INSTRUMENT.N_LRM_INSTRUMENT_ID = FSI_ACCT_COMPOSITE_KEY_MAP.N_ACCT_SKEY AND FSI_LRM_INSTRUMENT.N_RUN_SKEY = FSI_ACCT_COMPOSITE_KEY_MAP.N_RUN_SKEY AND FCT_ACCOUNT_CASH_FLOWS.V_ISO_CURRENCY_CD = FSI_ACCT_COMPOSITE_KEY_MAP.V_CASHFLOW_CCY_CODE INNER JOIN FCT_LRM_RUN_PARAM ON FSI_LRM_INSTRUMENT.N_RUN_SKEY = FCT_LRM_RUN_PARAM.N_RUN_SKEY AND FSI_LRM_INSTRUMENT.N_AS_OF_DATE_SKEY = FCT_LRM_RUN_PARAM.N_AS_OF_DATE_SKEY INNER JOIN VW_CCY_CONV_FACTOR VW_LRM_CCY_CONV_FACTOR_RCY ON VW_LRM_CCY_CONV_FACTOR_RCY.V_FROM_CURRENCY = FCT_ACCOUNT_CASH_FLOWS.V_ISO_CURRENCY_CD AND VW_LRM_CCY_CONV_FACTOR_RCY.V_TO_CURRENCY = FCT_LRM_RUN_PARAM.V_REPORTING_CURRENCY_CODE AND VW_LRM_CCY_CONV_FACTOR_RCY.N_ENTITY_SKEY = FSI_LRM_INSTRUMENT.N_ENTITY_SKEY GROUP BY FSI_ACCT_COMPOSITE_KEY_MAP.N_COMPOSITE_SKEY, FCT_ACCOUNT_CASH_FLOWS.N_AS_OF_DATE_SKEY, FCT_ACCOUNT_CASH_FLOWS.N_CASH_FLOW_TYPE_SKEY, FCT_ACCOUNT_CASH_FLOWS.N_RESULT_BUCKET_SKEY, FCT_ACCOUNT_CASH_FLOWS.N_RUN_SKEY
|