VIEW: APPS.XTR_REVALUATION_DETAILS_V

Source

View Text - Preformatted

SELECT MAX(ACTION_CODE) ACTION_CODE, MAX(COMPANY_CODE) COMPANY_CODE, NULL CONTRACT_CODE, MAX(CURRENCYA) CURRENCYA, NULL, ACCOUNT_NO REF_NUMBER, MAX(DEAL_SUBTYPE) DEAL_SUBTYPE, MAX(DEAL_TYPE) DEAL_TYPE, MAX(EFFECTIVE_DATE) EFFECTIVE_DATE, to_number(NULL), MAX(ENTERED_BY) ENTERED_BY, MAX(ENTERED_ON) ENTERED_ON, SUM(FACE_VALUE) FACE_VALUE, to_number(NULL), MIN(PERIOD_FROM) PERIOD_FROM, MAX(PERIOD_TO) PERIOD_TO, MAX(PORTFOLIO_CODE) PORTFOLIO_CODE, MAX(PRODUCT_TYPE) PRODUCT_TYPE, SUM( REALISED_PL) REALISED_PL, MAX(REVAL_CCY) REAVL_CCY, AVG(REVAL_RATE) REVAL_RATE, MAX(TRANSACTION_NO) TRANSACTION_NO, MAX(TRANSACTION_PERIOD) TRANSACTION_PERIOD, AVG(TRANSACTION_RATE) TRANSACTION_RATE, SUM(UNREALISED_PL) UNREALISED_PL, MAX(UPDATED_BY) UPDATED_BY, MAX(UPDATED_ON) UPDATED_ON, MAX(YEAR_BASIS) YEAR_BASIS, MAX(CREATED_BY) CREATED_BY, MAX(CREATED_ON) CREATED_ON, MIN(REVALUATION_DETAILS_ID) REVALUATION_DETAILS_ID, BATCH_ID, DECODE(MIN(COMPLETE_FLAG), 'Y', 'Y', 'N') COMPLETE_FLAG, NULL, to_number(NULL), MAX(OVERWRITE_REASON) OVERWRITE_REASON, SUM(FAIR_VALUE) FAIR_VALUE, REALIZED_FLAG, to_number(NULL), SUM(CURR_GAIN_LOSS_AMOUNT), SUM(SOB_FV_GAIN_LOSS_AMOUNT) SOB_FV_GAIN_LOSS_AMOUNT, to_number(NULL), to_number(NULL), to_number(NULL), AVG(EXCHANGE_RATE_ONE) EXCHANGE_RATE_ONE, to_number(NULL), MAX(AMOUNT_TYPE) AMOUNT_TYPE, to_number(NULL) FROM XTR_REVALUATION_DETAILS WHERE DEAL_TYPE = 'CA' AND REALIZED_FLAG IN ('Y','N') GROUP BY BATCH_ID, ACCOUNT_NO, REALIZED_FLAG UNION ALL SELECT NULL ACTION_CODE, MAX(COMPANY_CODE) COMPANY_CODE, DECODE(DEAL_TYPE,'ONC',MAX(CONTRACT_CODE),NULL) CONTRACT_CODE, MAX(CURRENCYA) CURRENCYA, NULL CURRENCYB, DECODE(DEAL_TYPE,'IG',TO_CHAR(MAX(DEAL_NO)),TO_CHAR(DEAL_NO)) REF_NUMBER, MAX(DEAL_SUBTYPE) DEAL_SUBTYPE, MAX(DEAL_TYPE) DEAL_TYPE, DECODE(DEAL_TYPE,'ONC',TO_DATE(NULL),MAX(EFFECTIVE_DATE)) EFFECTIVE_DATE, to_number(NULL) EFFECTIVE_DAYS, MAX(ENTERED_BY) ENTERED_BY, MAX(ENTERED_ON) ENTERED_ON, DECODE(DEAL_TYPE,'ONC',AVG(FACE_VALUE),SUM(FACE_VALUE)) FACE_VALUE, DECODE(DEAL_TYPE,'IG',MAX(FXO_SELL_REF_AMOUNT),NULL) FXO_SELL_REF_AMOUNT, MIN(PERIOD_FROM) PERIOD_FROM, MAX(PERIOD_TO) PERIOD_TO, MAX(PORTFOLIO_CODE) PORTFOLIO_CODE, MAX(PRODUCT_TYPE) PRODUCT_TYPE, SUM( REALISED_PL) REALISED_PL, MAX(REVAL_CCY) REAVL_CCY, AVG(REVAL_RATE) REVAL_RATE, DECODE(DEAL_TYPE,'ONC',TO_NUMBER(NULL),MAX(TRANSACTION_NO)) TRANSACTION_NO, DECODE(DEAL_TYPE,'ONC',TO_NUMBER(NULL),MAX(TRANSACTION_PERIOD)) TRANSACTION_PERIOD, AVG(TRANSACTION_RATE) TRANSACTION_RATE, SUM(UNREALISED_PL) UNREALISED_PL, MAX(UPDATED_BY) UPDATED_BY, MAX(UPDATED_ON) UPDATED_ON, MAX(YEAR_BASIS) YEAR_BASIS, MAX(CREATED_BY) CREATED_BY, MAX(CREATED_ON) CREATED_ON, MIN(REVALUATION_DETAILS_ID) REVALUATION_DETAILS_ID, BATCH_ID, DECODE(MIN(COMPLETE_FLAG), 'Y', 'Y', 'N') COMPLETE_FLAG, NULL, to_number(NULL), DECODE(DEAL_TYPE,'ONC',NULL,MAX(OVERWRITE_REASON)) OVERWRITE_REASON, DECODE(DEAL_TYPE,'ONC',TO_NUMBER(NULL),SUM(FAIR_VALUE)) FAIR_VALUE, REALIZED_FLAG, to_number(NULL),SUM(CURR_GAIN_LOSS_AMOUNT), SUM(SOB_FV_GAIN_LOSS_AMOUNT) SOB_FV_GAIN_LOSS_AMOUNT, to_number(NULL), to_number(NULL), to_number(NULL), AVG(EXCHANGE_RATE_ONE) EXCHANGE_RATE_ONE, to_number(null), MAX(AMOUNT_TYPE) AMOUNT_TYPE, to_number(NULL) FROM XTR_REVALUATION_DETAILS WHERE DEAL_TYPE IN ('IG','ONC') AND REALIZED_FLAG IN ('Y','N') GROUP BY DEAL_TYPE, BATCH_ID, DEAL_NO, REALIZED_FLAG UNION ALL SELECT ACTION_CODE, COMPANY_CODE, CONTRACT_CODE, CURRENCYA, CURRENCYB, to_char(DEAL_NO) REF_NUMBER, DEAL_SUBTYPE, DEAL_TYPE, EFFECTIVE_DATE, EFFECTIVE_DAYS, ENTERED_BY, ENTERED_ON, FACE_VALUE, FXO_SELL_REF_AMOUNT, PERIOD_FROM, PERIOD_TO, PORTFOLIO_CODE, PRODUCT_TYPE, REALISED_PL, REVAL_CCY, REVAL_RATE, TRANSACTION_NO, TRANSACTION_PERIOD, TRANSACTION_RATE, UNREALISED_PL, UPDATED_BY, UPDATED_ON, YEAR_BASIS, CREATED_BY, CREATED_ON, REVALUATION_DETAILS_ID, BATCH_ID, COMPLETE_FLAG, OVERWRITE_TYPE, OVERWRITE_VALUE, OVERWRITE_REASON, FAIR_VALUE, REALIZED_FLAG, CUMM_GAIN_LOSS_AMOUNT, CURR_GAIN_LOSS_AMOUNT, SOB_FV_GAIN_LOSS_AMOUNT, SOB_FAIR_VALUE_AMOUNT, REVAL_CURR_FV_AMOUNT, CTR_CURR_SOB_CURR_FWD_RATE, EXCHANGE_RATE_ONE, EXCHANGE_RATE_TWO, AMOUNT_TYPE, QUANTITY FROM XTR_REVALUATION_DETAILS WHERE DEAL_TYPE NOT IN ('ONC', 'CA', 'IG')

View Text - HTML Formatted

SELECT MAX(ACTION_CODE) ACTION_CODE
, MAX(COMPANY_CODE) COMPANY_CODE
, NULL CONTRACT_CODE
, MAX(CURRENCYA) CURRENCYA
, NULL
, ACCOUNT_NO REF_NUMBER
, MAX(DEAL_SUBTYPE) DEAL_SUBTYPE
, MAX(DEAL_TYPE) DEAL_TYPE
, MAX(EFFECTIVE_DATE) EFFECTIVE_DATE
, TO_NUMBER(NULL)
, MAX(ENTERED_BY) ENTERED_BY
, MAX(ENTERED_ON) ENTERED_ON
, SUM(FACE_VALUE) FACE_VALUE
, TO_NUMBER(NULL)
, MIN(PERIOD_FROM) PERIOD_FROM
, MAX(PERIOD_TO) PERIOD_TO
, MAX(PORTFOLIO_CODE) PORTFOLIO_CODE
, MAX(PRODUCT_TYPE) PRODUCT_TYPE
, SUM( REALISED_PL) REALISED_PL
, MAX(REVAL_CCY) REAVL_CCY
, AVG(REVAL_RATE) REVAL_RATE
, MAX(TRANSACTION_NO) TRANSACTION_NO
, MAX(TRANSACTION_PERIOD) TRANSACTION_PERIOD
, AVG(TRANSACTION_RATE) TRANSACTION_RATE
, SUM(UNREALISED_PL) UNREALISED_PL
, MAX(UPDATED_BY) UPDATED_BY
, MAX(UPDATED_ON) UPDATED_ON
, MAX(YEAR_BASIS) YEAR_BASIS
, MAX(CREATED_BY) CREATED_BY
, MAX(CREATED_ON) CREATED_ON
, MIN(REVALUATION_DETAILS_ID) REVALUATION_DETAILS_ID
, BATCH_ID
, DECODE(MIN(COMPLETE_FLAG)
, 'Y'
, 'Y'
, 'N') COMPLETE_FLAG
, NULL
, TO_NUMBER(NULL)
, MAX(OVERWRITE_REASON) OVERWRITE_REASON
, SUM(FAIR_VALUE) FAIR_VALUE
, REALIZED_FLAG
, TO_NUMBER(NULL)
, SUM(CURR_GAIN_LOSS_AMOUNT)
, SUM(SOB_FV_GAIN_LOSS_AMOUNT) SOB_FV_GAIN_LOSS_AMOUNT
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, AVG(EXCHANGE_RATE_ONE) EXCHANGE_RATE_ONE
, TO_NUMBER(NULL)
, MAX(AMOUNT_TYPE) AMOUNT_TYPE
, TO_NUMBER(NULL)
FROM XTR_REVALUATION_DETAILS
WHERE DEAL_TYPE = 'CA'
AND REALIZED_FLAG IN ('Y'
, 'N') GROUP BY BATCH_ID
, ACCOUNT_NO
, REALIZED_FLAG UNION ALL SELECT NULL ACTION_CODE
, MAX(COMPANY_CODE) COMPANY_CODE
, DECODE(DEAL_TYPE
, 'ONC'
, MAX(CONTRACT_CODE)
, NULL) CONTRACT_CODE
, MAX(CURRENCYA) CURRENCYA
, NULL CURRENCYB
, DECODE(DEAL_TYPE
, 'IG'
, TO_CHAR(MAX(DEAL_NO))
, TO_CHAR(DEAL_NO)) REF_NUMBER
, MAX(DEAL_SUBTYPE) DEAL_SUBTYPE
, MAX(DEAL_TYPE) DEAL_TYPE
, DECODE(DEAL_TYPE
, 'ONC'
, TO_DATE(NULL)
, MAX(EFFECTIVE_DATE)) EFFECTIVE_DATE
, TO_NUMBER(NULL) EFFECTIVE_DAYS
, MAX(ENTERED_BY) ENTERED_BY
, MAX(ENTERED_ON) ENTERED_ON
, DECODE(DEAL_TYPE
, 'ONC'
, AVG(FACE_VALUE)
, SUM(FACE_VALUE)) FACE_VALUE
, DECODE(DEAL_TYPE
, 'IG'
, MAX(FXO_SELL_REF_AMOUNT)
, NULL) FXO_SELL_REF_AMOUNT
, MIN(PERIOD_FROM) PERIOD_FROM
, MAX(PERIOD_TO) PERIOD_TO
, MAX(PORTFOLIO_CODE) PORTFOLIO_CODE
, MAX(PRODUCT_TYPE) PRODUCT_TYPE
, SUM( REALISED_PL) REALISED_PL
, MAX(REVAL_CCY) REAVL_CCY
, AVG(REVAL_RATE) REVAL_RATE
, DECODE(DEAL_TYPE
, 'ONC'
, TO_NUMBER(NULL)
, MAX(TRANSACTION_NO)) TRANSACTION_NO
, DECODE(DEAL_TYPE
, 'ONC'
, TO_NUMBER(NULL)
, MAX(TRANSACTION_PERIOD)) TRANSACTION_PERIOD
, AVG(TRANSACTION_RATE) TRANSACTION_RATE
, SUM(UNREALISED_PL) UNREALISED_PL
, MAX(UPDATED_BY) UPDATED_BY
, MAX(UPDATED_ON) UPDATED_ON
, MAX(YEAR_BASIS) YEAR_BASIS
, MAX(CREATED_BY) CREATED_BY
, MAX(CREATED_ON) CREATED_ON
, MIN(REVALUATION_DETAILS_ID) REVALUATION_DETAILS_ID
, BATCH_ID
, DECODE(MIN(COMPLETE_FLAG)
, 'Y'
, 'Y'
, 'N') COMPLETE_FLAG
, NULL
, TO_NUMBER(NULL)
, DECODE(DEAL_TYPE
, 'ONC'
, NULL
, MAX(OVERWRITE_REASON)) OVERWRITE_REASON
, DECODE(DEAL_TYPE
, 'ONC'
, TO_NUMBER(NULL)
, SUM(FAIR_VALUE)) FAIR_VALUE
, REALIZED_FLAG
, TO_NUMBER(NULL)
, SUM(CURR_GAIN_LOSS_AMOUNT)
, SUM(SOB_FV_GAIN_LOSS_AMOUNT) SOB_FV_GAIN_LOSS_AMOUNT
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, AVG(EXCHANGE_RATE_ONE) EXCHANGE_RATE_ONE
, TO_NUMBER(NULL)
, MAX(AMOUNT_TYPE) AMOUNT_TYPE
, TO_NUMBER(NULL)
FROM XTR_REVALUATION_DETAILS
WHERE DEAL_TYPE IN ('IG'
, 'ONC')
AND REALIZED_FLAG IN ('Y'
, 'N') GROUP BY DEAL_TYPE
, BATCH_ID
, DEAL_NO
, REALIZED_FLAG UNION ALL SELECT ACTION_CODE
, COMPANY_CODE
, CONTRACT_CODE
, CURRENCYA
, CURRENCYB
, TO_CHAR(DEAL_NO) REF_NUMBER
, DEAL_SUBTYPE
, DEAL_TYPE
, EFFECTIVE_DATE
, EFFECTIVE_DAYS
, ENTERED_BY
, ENTERED_ON
, FACE_VALUE
, FXO_SELL_REF_AMOUNT
, PERIOD_FROM
, PERIOD_TO
, PORTFOLIO_CODE
, PRODUCT_TYPE
, REALISED_PL
, REVAL_CCY
, REVAL_RATE
, TRANSACTION_NO
, TRANSACTION_PERIOD
, TRANSACTION_RATE
, UNREALISED_PL
, UPDATED_BY
, UPDATED_ON
, YEAR_BASIS
, CREATED_BY
, CREATED_ON
, REVALUATION_DETAILS_ID
, BATCH_ID
, COMPLETE_FLAG
, OVERWRITE_TYPE
, OVERWRITE_VALUE
, OVERWRITE_REASON
, FAIR_VALUE
, REALIZED_FLAG
, CUMM_GAIN_LOSS_AMOUNT
, CURR_GAIN_LOSS_AMOUNT
, SOB_FV_GAIN_LOSS_AMOUNT
, SOB_FAIR_VALUE_AMOUNT
, REVAL_CURR_FV_AMOUNT
, CTR_CURR_SOB_CURR_FWD_RATE
, EXCHANGE_RATE_ONE
, EXCHANGE_RATE_TWO
, AMOUNT_TYPE
, QUANTITY
FROM XTR_REVALUATION_DETAILS
WHERE DEAL_TYPE NOT IN ('ONC'
, 'CA'
, 'IG')