CREATE OR REPLACE VIEW TEMP_VW AS SELECT DECODE(SLT_SLIQ_TRADE.SLT_PARENT_TRADE_ID, NULL, SLT_SLIQ_TRADE.SLT_TRADE_ID, SLT_SLIQ_TRADE.SLT_PARENT_TRADE_ID) GROUP_ID, SLT_SLIQ_TRADE.SLT_TRADE_ID, SLT_SLIQ_TRADE.SLT_PARENT_TRADE_ID, SLT_SLIQ_TRADE.IS_PARENT_FLAG, SLT_SLIQ_TRADE.TRADE_TYPE_IND, SLT_SLIQ_TRADE.TRADE_RID, SLT_SLIQ_TRADE.TRADE_ID, SLT_SLIQ_TRADE.PARENT_TRADE_ID, SLT_SLIQ_TRADE.CLS_TYPE_IND, SLT_SLIQ_TRADE.E_SETTL_TRADE_ID, NVL(SLT_E_SETTL_TRADE.TSI_TRADE_ID,SLT_E_SETTL_TRADE.CPAR_TRADE_ID) TRADE_REF_ID, SLT_SLIQ_TRADE.TRADE_LOCK_IND, SLT_SLIQ_TRADE.DEAL_NAME, SLT_SLIQ_TRADE.DEAL_CUSIP, SLT_SLIQ_TRADE.EXCP_TYPE_CODE, SLT_SLIQ_TRADE_FACILITY.FACILITY_NAME, SLT_SLIQ_TRADE_FACILITY.GES_FAC_ID, SLT_SLIQ_TRADE_FACILITY.FACILITY_CUSIP, SLT_SLIQ_TRADE_FACILITY.FACILITY_CCY, SLT_SLIQ_TRADE.ORIGINAL_TRADE_AMOUNT, SLT_SLIQ_TRADE.CURRENT_TRADE_AMOUNT, SLT_SLIQ_TRADE.CLOSED_TRADE_AMOUNT, SLT_SLIQ_TRADE_FACILITY.ORIGINAL_FACILITY_AMOUNT, SLT_SLIQ_TRADE_FACILITY.CURRENT_FACILITY_AMOUNT, SLT_SLIQ_TRADE_FACILITY.CLOSED_FACILITY_AMOUNT, SLT_SLIQ_PORTFOLIO.AVAILABLE_FACILITY_AMOUNT, SLT_SLIQ_PORTFOLIO.AVAILABLE_POSITION, SLT_SLIQ_TRADE.COUNTERPARTY, SLT_SLIQ_TRADE.PARENT_COUNTERPARTY, SLT_SLIQ_TRADE.AGENT_BANK, SLT_SLIQ_TRADE_FACILITY.PTFLO_ALLOC_DONE, SLT_SLIQ_TRADE.BUY_SELL_IND, SLT_SLIQ_TRADE.TRADE_DATE, SLT_SLIQ_TRADE.TARGET_SETTL_DATE, SLT_SLIQ_TRADE.DEAL_CCY, SLT_SLIQ_TRADE.STCL_DATE, SLT_SLIQ_TRADE.STCL_TYPE, SLT_SLIQ_TRADE.AFFIR_EXEC_DATE, (SELECT SLT_MIS_CODE.CODE_DESC FROM SLT_MIS_CODE WHERE SLT_MIS_CODE.MIS_CODE_ID= SLT_SLIQ_TRADE.AFFIR_STATUS) AFFIR_STATUS, (SELECT SLT_MIS_CODE.CODE_DESC FROM SLT_MIS_CODE WHERE SLT_MIS_CODE.MIS_CODE_ID=SLT_SLIQ_TRADE.AFFIR_MIS_CODE) AFFIR_MIS_CODE, SLT_SLIQ_TRADE.AFFIR_COMMENT, SLT_SLIQ_TRADE.CONFIRM_EXEC_DATE, (SELECT SLT_MIS_CODE.CODE_DESC FROM SLT_MIS_CODE WHERE SLT_MIS_CODE.MIS_CODE_ID=SLT_SLIQ_TRADE.CONFIRM_STATUS) CONFIRM_STATUS, (SELECT SLT_MIS_CODE.CODE_DESC FROM SLT_MIS_CODE WHERE SLT_MIS_CODE.MIS_CODE_ID=SLT_SLIQ_TRADE.CONFIRM_MIS_CODE) CONFIRM_MIS_CODE , SLT_SLIQ_TRADE.CONFIRM_COMMENT, SLT_SLIQ_TRADE.TRANS_DOC_EXEC_DATE, (SELECT SLT_MIS_CODE.CODE_DESC FROM SLT_MIS_CODE WHERE SLT_MIS_CODE.MIS_CODE_ID=SLT_SLIQ_TRADE.TRANS_DOC_STATUS) TRANS_DOC_STATUS, (SELECT SLT_MIS_CODE.CODE_DESC FROM SLT_MIS_CODE WHERE SLT_MIS_CODE.MIS_CODE_ID=SLT_SLIQ_TRADE.TRANS_DOC_MIS_CODE) TRANS_DOC_MIS_CODE , SLT_SLIQ_TRADE.TRANS_DOC_COMMENT, SLT_SLIQ_TRADE.FM_EXEC_DATE, (SELECT SLT_MIS_CODE.CODE_DESC FROM SLT_MIS_CODE WHERE SLT_MIS_CODE.MIS_CODE_ID=SLT_SLIQ_TRADE.FM_STATUS) FM_STATUS, (SELECT SLT_MIS_CODE.CODE_DESC FROM SLT_MIS_CODE WHERE SLT_MIS_CODE.MIS_CODE_ID=SLT_SLIQ_TRADE.FM_MIS_CODE) FM_MIS_CODE, SLT_SLIQ_TRADE.FM_COMMENT, SLT_SLIQ_PORTFOLIO.LEGAL_ENTITY, SLT_SLIQ_TRADE.MTM_USD, SLT_SLIQ_TRADE.RISK_BOOK_DESC RISK_BOOK_CODE, SLT_SLIQ_TRADE.RISK_TYPE, SLT_SLIQ_PORTFOLIO.PTFLO_CODE, SLT_SLIQ_PORTFOLIO.PTFLO_NAME, SLT_SLIQ_PORTFOLIO.PTFLO_ALLOC, SLT_SLIQ_TRADE.BUSINESS_CODE, SLT_SLIQ_TRADE.ASGN_PTCP_CODE, SLT_SLIQ_TRADE_FACILITY.FACILITY_PRICE, SLT_SLIQ_TRADE.PAR_DISTRESS_IND, SLT_SLIQ_TRADE.LSTA_LMA_IND, SLT_SLIQ_TRADE.SCND_EARLY_DAY_TRADE, SLT_SLIQ_TRADE.CIRCLE_STATUS, SLT_SLIQ_TRADE.CLOSER_USER_ID, SLT_SLIQ_TRADE.CONFIRM_USER_ID, SLT_SLIQ_TRADE.SETTL_USER_ID, (SELECT COM_USER_DATA.FIRST_NAME || ' ' || COM_USER_DATA.LAST_NAME FROM COM_USER_DATA WHERE COM_USER_DATA.USER_ID = SLT_SLIQ_TRADE.CLOSER_USER_ID) CLOSER_USER_NAME, (SELECT COM_USER_DATA.FIRST_NAME || ' ' || COM_USER_DATA.LAST_NAME FROM COM_USER_DATA WHERE COM_USER_DATA.USER_ID = SLT_SLIQ_TRADE.CONFIRM_USER_ID) CONFIRM_USER_NAME, (SELECT COM_USER_DATA.FIRST_NAME || ' ' || COM_USER_DATA.LAST_NAME FROM COM_USER_DATA WHERE COM_USER_DATA.USER_ID = SLT_SLIQ_TRADE.SETTL_USER_ID) SETTL_USER_NAME, SLT_SLIQ_TRADE.UPCM_CNTRCT_EXPR_RLVR_DTE, SLT_SLIQ_TRADE.UPCM_PRIN_REPYMT_DTE, SLT_SLIQ_TRADE.LIQ_TRADE_EVENT, SLT_SLIQ_TRADE_FACILITY.FUNDED_LOANS, SLT_SLIQ_TRADE_FACILITY.UNFUNDED_CMT, SLT_SLIQ_TRADE_FACILITY.DC_ACCR_INTEREST, SLT_SLIQ_TRADE_FACILITY.DC_COST_OF_CARRY, SLT_SLIQ_TRADE.OTP_TRADE_DTE_FEE, SLT_SLIQ_TRADE_FACILITY.BNFT_OF_CMT_RDN, SLT_SLIQ_TRADE.ASGN_FEES, SLT_SLIQ_TRADE.ASGN_FEES_PROVSN, SLT_SLIQ_TRADE.COUNTERPARTY_TYPE, SLT_SLIQ_TRADE_FACILITY.UPFRONT_FEE, SLT_SLIQ_TRADE.BREAK_COST, NULL ACCRUED_INTEREST, TRUNC(SYSDATE - SLT_SLIQ_TRADE.TRADE_DATE) CONFIRM_AGING, CASE WHEN TRUNC(SYSDATE - SLT_SLIQ_TRADE.TRADE_DATE) BETWEEN 0 AND 14 THEN '0-14' WHEN TRUNC(SYSDATE - SLT_SLIQ_TRADE.TRADE_DATE) BETWEEN 15 AND 30 THEN '15-30' WHEN TRUNC(SYSDATE - SLT_SLIQ_TRADE.TRADE_DATE) BETWEEN 31 AND 60 THEN '31-60' WHEN TRUNC(SYSDATE - SLT_SLIQ_TRADE.TRADE_DATE) BETWEEN 61 AND 90 THEN '61-90' WHEN TRUNC(SYSDATE - SLT_SLIQ_TRADE.TRADE_DATE) BETWEEN 91 AND 180 THEN '91-180' ELSE '180+' END CONFIRM_AGE_BUCKET, TRUNC(SYSDATE - SLT_SLIQ_TRADE.TRADE_DATE) AFFIR_AGING, CASE WHEN TRUNC(SYSDATE - SLT_SLIQ_TRADE.TRADE_DATE) BETWEEN 0 AND 3 THEN '0-3' WHEN TRUNC(SYSDATE - SLT_SLIQ_TRADE.TRADE_DATE) BETWEEN 4 AND 6 THEN '4-6' ELSE '6+' END AFFIR_AGE_BUCKET, TRUNC(SYSDATE - SLT_SLIQ_TRADE.TARGET_SETTL_DATE) TRADE_AGING, CASE WHEN TRUNC(SYSDATE - SLT_SLIQ_TRADE.TARGET_SETTL_DATE) BETWEEN 0 AND 14 THEN '0-14' WHEN TRUNC(SYSDATE - SLT_SLIQ_TRADE.TARGET_SETTL_DATE) BETWEEN 15 AND 30 THEN '15-30' WHEN TRUNC(SYSDATE - SLT_SLIQ_TRADE.TARGET_SETTL_DATE) BETWEEN 31 AND 60 THEN '31-60' WHEN TRUNC(SYSDATE - SLT_SLIQ_TRADE.TARGET_SETTL_DATE) BETWEEN 61 AND 90 THEN '61-90' WHEN TRUNC(SYSDATE - SLT_SLIQ_TRADE.TARGET_SETTL_DATE) BETWEEN 91 AND 180 THEN '91-180' ELSE '180+' END TRADE_AGE_BUCKET, CASE WHEN (SLT_SLIQ_TRADE.STCL_DATE = SLT_SLIQ_TRADE.UPCM_CNTRCT_EXPR_RLVR_DTE OR SLT_SLIQ_TRADE.STCL_DATE = SLT_SLIQ_TRADE.UPCM_PRIN_REPYMT_DTE) THEN 'Y' ELSE 'N' END UPCM_LOAN_ACTIVITY_FLAG, TRUNC(SLT_SLIQ_TRADE.STCL_DATE - SLT_SLIQ_TRADE.UPCM_CNTRCT_EXPR_RLVR_DTE) UPCM_CNTRCT_EXPR_RLVR_DTE_DIFF, TRUNC(SLT_SLIQ_TRADE.STCL_DATE - SLT_SLIQ_TRADE.UPCM_PRIN_REPYMT_DTE) UPCM_PRIN_REPYMT_DTE_DIFF, SLT_SLIQ_TRADE.ECO_EXCP_OVRRD_FLAG, SLT_SLIQ_TRADE.FM_EXCP_OVRRD_FLAG, SLT_SLIQ_TRADE.POS_EXCP_OVRRD_FLAG, SLT_SLIQ_TRADE.CLSD_EXCP_OVRRD_FLAG, CASE WHEN SLT_SLIQ_TRADE.ECO_EXCP_OVRRD_FLAG = 'Y' OR SLT_SLIQ_TRADE.FM_EXCP_OVRRD_FLAG = 'Y' OR SLT_SLIQ_TRADE.POS_EXCP_OVRRD_FLAG = 'Y' OR SLT_SLIQ_TRADE.CLSD_EXCP_OVRRD_FLAG = 'Y' THEN 'Y' ELSE 'N' END EXCP_OVRRD_FLAG, SLT_SLIQ_TRADE.GENERIC_UNIQUE_ID, SLT_SLIQ_TRADE_FACILITY.TRADE_FACILITY_ID, SLT_SLIQ_TRADE.AFFIR_MIS_CODE AFFIR_MIS_CODE_ID , SLT_SLIQ_TRADE.AFFIR_STATUS AFFIR_STATUS_ID, SLT_SLIQ_TRADE.CONFIRM_STATUS CONFIRM_STATUS_ID, SLT_SLIQ_TRADE.CONFIRM_MIS_CODE CONFIRM_MIS_CODE_ID, SLT_SLIQ_TRADE.TRANS_DOC_STATUS TRANS_DOC_STATUS_ID, SLT_SLIQ_TRADE.TRANS_DOC_MIS_CODE TRANS_DOC_MIS_CODE_ID, SLT_SLIQ_TRADE.FM_STATUS FM_STATUS_ID, SLT_SLIQ_TRADE.FM_MIS_CODE FM_MIS_CODE_ID FROM SLT_SLIQ_TRADE INNER JOIN SLT_SLIQ_TRADE_FACILITY ON (SLT_SLIQ_TRADE.TRADE_RID = SLT_SLIQ_TRADE_FACILITY.TRADE_RID) INNER JOIN SLT_SLIQ_PORTFOLIO ON (SLT_SLIQ_PORTFOLIO.TRADE_RID =SLT_SLIQ_TRADE_FACILITY.TRADE_RID AND SLT_SLIQ_PORTFOLIO.TRADE_FACILITY_ID =SLT_SLIQ_TRADE_FACILITY.TRADE_FACILITY_ID ) LEFT OUTER JOIN SLT_E_SETTL_TRADE ON (SLT_E_SETTL_TRADE.SLT_TRADE_ID =SLT_SLIQ_TRADE.E_SETTL_TRADE_ID ) WHERE 1= CASE WHEN SLT_SLIQ_TRADE.CIRCLE_STATUS='CNPERM' AND SLT_SLIQ_TRADE.IS_PARENT_FLAG='Y' THEN 1 WHEN SLT_SLIQ_TRADE.CIRCLE_STATUS='CNPERM' AND SLT_SLIQ_TRADE.IS_PARENT_FLAG='N' THEN 2 WHEN SLT_SLIQ_TRADE.CIRCLE_STATUS='CAN' THEN 2 ELSE 1 END UNION ALL SELECT DECODE(SLT_E_SETTL_TRADE.SLT_PARENT_TRADE_ID, NULL, SLT_E_SETTL_TRADE.SLT_TRADE_ID, SLT_E_SETTL_TRADE.SLT_PARENT_TRADE_ID) GROUP_ID, SLT_E_SETTL_TRADE.SLT_TRADE_ID, SLT_E_SETTL_TRADE.SLT_PARENT_TRADE_ID, SLT_E_SETTL_TRADE.IS_PARENT_FLAG, SLT_E_SETTL_TRADE.TRADE_TYPE_IND, NULL TRADE_RID, NULL TRADE_ID, NULL PARENT_TRADE_ID, SLT_E_SETTL_TRADE.CLS_TYPE_IND, SLT_E_SETTL_TRADE.SLT_TRADE_ID E_SETTL_TRADE_ID, CASE WHEN SLT_E_SETTL_TRADE.CLS_TYPE_IND='TSI' THEN SLT_E_SETTL_TRADE.TSI_TRADE_ID ELSE SLT_E_SETTL_TRADE.CPAR_TRADE_ID END TRADE_REF_ID, NULL TRADE_LOCK_IND, SLT_E_SETTL_TRADE.DEAL_NAME, SLT_E_SETTL_TRADE.DEAL_CUSIP, 'UNMATCH' EXCP_TYPE_CODE, SLT_E_SETTL_FACILITY.FACILITY_NAME, NULL GES_FAC_ID, SLT_E_SETTL_FACILITY.FACILITY_CUSIP, SLT_E_SETTL_FACILITY.FACILITY_CCY, NULL ORIGINAL_TRADE_AMOUNT, NULL CURRENT_TRADE_AMOUNT, NULL CLOSED_TRADE_AMOUNT, SLT_E_SETTL_FACILITY.ORIGINAL_FACILITY_AMOUNT, SLT_E_SETTL_FACILITY.CURRENT_FACILITY_AMOUNT, SLT_E_SETTL_FACILITY.CLOSED_FACILITY_AMOUNT, NULL AVAILABLE_FACILITY_AMOUNT, NULL AVAILABLE_POSITION, NULL COUNTERPARTY, NULL PARENT_COUNTERPARTY, SLT_E_SETTL_TRADE.AGENT_BANK AGENT_BANK, NULL PTFLO_ALLOC_DONE, SLT_E_SETTL_TRADE.BUY_SELL_IND, SLT_E_SETTL_TRADE.TRADE_DATE, NULL TARGET_SETTL_DATE, SLT_E_SETTL_TRADE.DEAL_CCY, NULL STCL_DATE, NULL STCL_TYPE, NULL AFFIR_EXEC_DATE, NULL AFFIR_STATUS, NULL AFFIR_MIS_CODE, NULL AFFIR_COMMENT, SLT_E_SETTL_TRADE.CONFIRM_EXEC_DATE, SLT_E_SETTL_TRADE.CONFIRM_STATUS, NULL CONFIRM_MIS_CODE, NULL CONFIRM_COMMENT, SLT_E_SETTL_TRADE.TRANS_DOC_EXEC_DATE, SLT_E_SETTL_TRADE.TRANS_DOC_STATUS, NULL TRANS_DOC_MIS_CODE, NULL TRANS_DOC_COMMENT, SLT_E_SETTL_TRADE.FM_EXEC_DATE, SLT_E_SETTL_TRADE.FM_STATUS, NULL FM_MIS_CODE, NULL FM_COMMENT, NULL LEGAL_ENTITY, NULL MTM_USD, NULL RISK_BOOK_CODE, NULL RISK_TYPE, NULL PTFLO_CODE, NULL PTFLO_NAME, NULL PTFLO_ALLOC, NULL BUSINESS_CODE, SLT_E_SETTL_TRADE.ASGN_PTCP_CODE, SLT_E_SETTL_FACILITY.FACILITY_PRICE, SLT_E_SETTL_TRADE.PAR_DISTRESS_IND, SLT_E_SETTL_TRADE.LSTA_LMA_IND, SLT_E_SETTL_TRADE.SCND_EARLY_DAY_TRADE, SLT_E_SETTL_TRADE.CIRCLE_STATUS, NULL CLOSER_USER_ID, NULL CONFIRM_USER_ID, NULL SETTL_USER_ID, NULL CLOSER_USER_NAME, NULL CONFIRM_USER_NAME, NULL SETTL_USER_NAME, NULL UPCM_CNTRCT_EXPR_RLVR_DTE, NULL UPCM_PRIN_REPYMT_DTE, NULL LIQ_TRADE_EVENT, SLT_E_SETTL_FACILITY.FUNDED_LOANS, SLT_E_SETTL_FACILITY.UNFUNDED_CMT, SLT_E_SETTL_FACILITY.DC_ACCR_INTEREST, SLT_E_SETTL_FACILITY.DC_COST_OF_CARRY, SLT_E_SETTL_TRADE.OTP_TRADE_DTE_FEE, SLT_E_SETTL_FACILITY.BNFT_OF_CMT_RDN, SLT_E_SETTL_TRADE.ASGN_FEES, SLT_E_SETTL_TRADE.ASGN_FEES_PROVSN, NULL COUNTERPARTY_TYPE, SLT_E_SETTL_FACILITY.UPFRONT_FEE, SLT_E_SETTL_TRADE.BREAK_COST, SLT_E_SETTL_FACILITY.ACCRUED_INTEREST, NULL CONFIRM_AGING, NULL CONFIRM_AGE_BUCKET, NULL AFFIR_AGING, NULL AFFIR_AGE_BUCKET, NULL TRADE_AGING, NULL TRADE_AGE_BUCKET, NULL UPCM_LOAN_ACTIVITY_FLAG, NULL UPCM_CNTRCT_EXPR_RLVR_DTE_DIFF, NULL UPCM_PRIN_REPYMT_DTE_DIFF, NULL ECO_EXCP_OVRRD_FLAG, NULL FM_EXCP_OVRRD_FLAG, NULL POS_EXCP_OVRRD_FLAG, NULL CLSD_EXCP_OVRRD_FLAG, NULL EXCP_OVRRD_FLAG, NULL GENERIC_UNIQUE_ID, SLT_E_SETTL_FACILITY.E_SETTL_FACILITY_ID TRADE_FACILITY_ID, NULL AFFIR_MIS_CODE_ID , NULL AFFIR_STATUS_ID, NULL CONFIRM_STATUS_ID, NULL CONFIRM_MIS_CODE_ID, NULL TRANS_DOC_STATUS_ID, NULL TRANS_DOC_MIS_CODE_ID, NULL FM_STATUS_ID, NULL FM_MIS_CODE_ID FROM SLT_E_SETTL_TRADE, SLT_E_SETTL_FACILITY WHERE SLT_E_SETTL_TRADE.SLT_TRADE_ID=SLT_E_SETTL_FACILITY.SLT_TRADE_ID AND 1= CASE WHEN SLT_E_SETTL_TRADE.CIRCLE_STATUS='CNPERM' AND SLT_E_SETTL_TRADE.IS_PARENT_FLAG='Y' THEN 1 WHEN SLT_E_SETTL_TRADE.CIRCLE_STATUS='CNPERM' AND SLT_E_SETTL_TRADE.IS_PARENT_FLAG='N' THEN 2 WHEN SLT_E_SETTL_TRADE.CIRCLE_STATUS='CAN' THEN 2 ELSE 1 END AND NOT EXISTS ( SELECT SLT_SLIQ_TRADE.TRADE_RID FROM SLT_SLIQ_TRADE WHERE SLT_SLIQ_TRADE.E_SETTL_TRADE_ID=SLT_E_SETTL_TRADE.SLT_TRADE_ID)