VIEW: APPS.XTR_REVALUATION_DETAILS_SUM_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 = 'Y' GROUP BY BATCH_ID, ACCOUNT_NO, REALIZED_FLAG UNION ALL 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 = 'N' GROUP BY BATCH_ID, ACCOUNT_NO, REALIZED_FLAG UNION ALL SELECT NULL, MAX(COMPANY_CODE) COMPANY_CODE, NULL CONTRACT_CODE, MAX(CURRENCYA) CURRENCYA, NULL, to_char(MAX(DEAL_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, MAX(FXO_SELL_REF_AMOUNT) 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, 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 = 'IG' AND REALIZED_FLAG = 'Y' GROUP BY BATCH_ID, DEAL_NO, REALIZED_FLAG UNION ALL SELECT NULL, MAX(COMPANY_CODE) COMPANY_CODE, NULL CONTRACT_CODE, MAX(CURRENCYA) CURRENCYA, NULL, to_char(MAX(DEAL_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, MAX(FXO_SELL_REF_AMOUNT) 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, 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 = 'IG' AND REALIZED_FLAG = 'N' GROUP BY BATCH_ID, DEAL_NO, REALIZED_FLAG UNION ALL SELECT NULL ACTION_CODE, MAX(COMPANY_CODE) COMPANY_CODE, MAX(CONTRACT_CODE) CONTRACT_CODE, MAX(CURRENCYA) CURRENCYA, NULL CURRENCYB, to_char(DEAL_NO) REF_NUMBER, MAX(DEAL_SUBTYPE) DEAL_SUBTYPE, MAX(DEAL_TYPE) DEAL_TYPE, to_date(NULL) EFFECTIVE_DATE, to_number(null) EFFECTIVE_DAYS, MAX(ENTERED_BY) ENTERED_BY, MAX(ENTERED_ON) ENTERED_ON, AVG(FACE_VALUE) FACE_VALUE, to_number(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, to_number(NULL) TRANSACTION_NO, to_number(NULL) 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), NULL OVERWRITE_REASON, to_number(NULL) FAIR_VALUE, MAX(REALIZED_FLAG) 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 = 'ONC' AND REALIZED_FLAG = 'Y' GROUP BY BATCH_ID, DEAL_NO, REALIZED_FLAG UNION ALL SELECT NULL ACTION_CODE, MAX(COMPANY_CODE) COMPANY_CODE, MAX(CONTRACT_CODE) CONTRACT_CODE, MAX(CURRENCYA) CURRENCYA, NULL CURRENCYB , to_char( DEAL_NO) REF_NUMBER, MAX(DEAL_SUBTYPE) DEAL_SUBTYPE, MAX(DEAL_TYPE) DEAL_TYPE, to_date(NULL) EFFECTIVE_DATE, to_number(null) EFFECTIVE_DAYS, MAX(ENTERED_BY) ENTERED_BY, MAX(ENTERED_ON) ENTERED_ON, AVG(FACE_VALUE) FACE_VALUE, to_number(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, to_number(NULL) TRANSACTION_NO, to_number(NULL) 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), NULL OVERWRITE_REASON, to_number(NULL) FAIR_VALUE, MAX(REALIZED_FLAG) 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 = 'ONC' AND REALIZED_FLAG = 'N' GROUP BY 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', 'NI', 'IRS') UNION ALL SELECT max(ACTION_CODE), max(COMPANY_CODE), max(CONTRACT_CODE), max(CURRENCYA), max(CURRENCYB), TO_CHAR(DEAL_NO) REF_NUMBER, max(DEAL_SUBTYPE), max(DEAL_TYPE), max(EFFECTIVE_DATE), 0 EFFECTIVE_DAYS, MAX(ENTERED_BY) ENTERED_BY, MAX(ENTERED_ON) ENTERED_ON, SUM(FACE_VALUE) FACE_VALUE, to_number(null) FXO_SELL_REF_AMOUNT, min(PERIOD_FROM), max(PERIOD_TO), max(PORTFOLIO_CODE), max(PRODUCT_TYPE), SUM(REALISED_PL) REALISED_PL, max(REVAL_CCY), AVG(REVAL_RATE), 0 TRANSACTION_NO, max(TRANSACTION_PERIOD), avg(TRANSACTION_RATE), SUM(UNREALISED_PL) UNREALISED_PL, MAX(UPDATED_BY) UPDATED_BY, MAX(UPDATED_ON) UPDATED_ON, max(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, max(OVERWRITE_TYPE), max(OVERWRITE_VALUE), max(OVERWRITE_REASON), SUM(FAIR_VALUE) FAIR_VALUE, NVL(REALIZED_FLAG,'N') REALIZED_FLAG, SUM(CUMM_GAIN_LOSS_AMOUNT) CUMM_GAIN_LOSS_AMOUNT, SUM(CURR_GAIN_LOSS_AMOUNT) CURR_GAIN_LOSS_AMOUNT, SUM(SOB_FV_GAIN_LOSS_AMOUNT) SOB_FV_GAIN_LOSS_AMOUNT, SUM(SOB_FAIR_VALUE_AMOUNT) SOB_FAIR_VALUE_AMOUNT, SUM(REVAL_CURR_FV_AMOUNT) REVAL_CURR_FV_AMOUNT, TO_NUMBER(NULL) CTR_CURR_SOB_CURR_FWD_RATE, AVG(EXCHANGE_RATE_ONE) EXCHANGE_RATE_ONE, TO_NUMBER(NULL) EXCHANGE_RATE_TWO, MAX(AMOUNT_TYPE) AMOUNT_TYPE, to_number(NULL) QUANTITY FROM XTR_REVALUATION_DETAILS WHERE DEAL_TYPE = 'NI' AND REALIZED_FLAG = 'Y' GROUP BY BATCH_ID, DEAL_NO, REALIZED_FLAG UNION ALL SELECT max(ACTION_CODE), max(COMPANY_CODE), max(CONTRACT_CODE), max(CURRENCYA), max(CURRENCYB), TO_CHAR(DEAL_NO) REF_NUMBER, max(DEAL_SUBTYPE), max(DEAL_TYPE), max(EFFECTIVE_DATE), max(EFFECTIVE_DAYS), MAX(ENTERED_BY) ENTERED_BY, MAX(ENTERED_ON) ENTERED_ON, SUM(FACE_VALUE), to_number(null) FXO_SELL_REF_AMOUNT, min(PERIOD_FROM), max(PERIOD_TO), max(PORTFOLIO_CODE), max(PRODUCT_TYPE), SUM(REALISED_PL) REALISED_PL, max(REVAL_CCY), avg(REVAL_RATE), 0 TRANSACTION_NO, max(TRANSACTION_PERIOD), avg(TRANSACTION_RATE), SUM(UNREALISED_PL) UNREALISED_PL, MAX(UPDATED_BY) UPDATED_BY, MAX(UPDATED_ON) UPDATED_ON, max(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, max(OVERWRITE_TYPE), max(OVERWRITE_VALUE), max(OVERWRITE_REASON), SUM(FAIR_VALUE) FAIR_VALUE, NVL(REALIZED_FLAG,'N') REALIZED_FLAG, SUM(CUMM_GAIN_LOSS_AMOUNT) CUMM_GAIN_LOSS_AMOUNT, SUM(CURR_GAIN_LOSS_AMOUNT) CURR_GAIN_LOSS_AMOUNT, SUM(SOB_FV_GAIN_LOSS_AMOUNT) SOB_FV_GAIN_LOSS_AMOUNT, SUM(SOB_FAIR_VALUE_AMOUNT) SOB_FAIR_VALUE_AMOUNT, SUM(REVAL_CURR_FV_AMOUNT) REVAL_CURR_FV_AMOUNT, TO_NUMBER(NULL) CTR_CURR_SOB_CURR_FWD_RATE, avg(EXCHANGE_RATE_ONE), TO_NUMBER(NULL) EXCHANGE_RATE_TWO, MAX(AMOUNT_TYPE) AMOUNT_TYPE, TO_NUMBER(NULL) QUANTITY FROM XTR_REVALUATION_DETAILS WHERE DEAL_TYPE = 'NI' AND NVL(REALIZED_FLAG,'N') = 'N' GROUP BY BATCH_ID, DEAL_NO, REALIZED_FLAG UNION ALL SELECT max(ACTION_CODE), max(COMPANY_CODE), max(CONTRACT_CODE), max(decode(DEAL_SUBTYPE, 'FUND', CURRENCYA, NULL)), max(decode(DEAL_SUBTYPE, 'INVEST', CURRENCYB, NULL)), TO_CHAR(SWAP_REF) REF_NUMBER, null DEAL_SUBTYPE, max(DEAL_TYPE), max(EFFECTIVE_DATE), 0 EFFECTIVE_DAYS, MAX(ENTERED_BY) ENTERED_BY, MAX(ENTERED_ON) ENTERED_ON, SUM(DECODE(DEAL_SUBTYPE,'FUND', DECODE(CURRENCYA,REVAL_CCY,-FACE_VALUE,(-FACE_VALUE*EXCHANGE_RATE_ONE)), DECODE(CURRENCYA,REVAL_CCY, FACE_VALUE,( FACE_VALUE*EXCHANGE_RATE_ONE)))) FACE_VALUE, to_number(null) FXO_SELL_REF_AMOUNT, min(PERIOD_FROM), max(PERIOD_TO), max(PORTFOLIO_CODE), max(PRODUCT_TYPE), SUM(REALISED_PL) REALISED_PL, max(REVAL_CCY), AVG(REVAL_RATE) REVAL_RATE, 0 TRANSACTION_NO, max(TRANSACTION_PERIOD), TO_NUMBER(NULL) TRANSACTION_RATE, SUM(UNREALISED_PL) UNREALISED_PL, MAX(UPDATED_BY) UPDATED_BY, MAX(UPDATED_ON) UPDATED_ON, TO_NUMBER(NULL) 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, max(OVERWRITE_TYPE), max(OVERWRITE_VALUE), max(OVERWRITE_REASON), SUM(FAIR_VALUE) FAIR_VALUE, NVL(REALIZED_FLAG,'N') REALIZED_FLAG, SUM(CUMM_GAIN_LOSS_AMOUNT) CUMM_GAIN_LOSS_AMOUNT, SUM(CURR_GAIN_LOSS_AMOUNT) CURR_GAIN_LOSS_AMOUNT, SUM(SOB_FV_GAIN_LOSS_AMOUNT) SOB_FV_GAIN_LOSS_AMOUNT, SUM(SOB_FAIR_VALUE_AMOUNT) SOB_FAIR_VALUE_AMOUNT, SUM(REVAL_CURR_FV_AMOUNT) REVAL_CURR_FV_AMOUNT, TO_NUMBER(NULL) CTR_CURR_SOB_CURR_FWD_RATE, AVG(EXCHANGE_RATE_ONE) EXCHANGE_RATE_ONE, TO_NUMBER(NULL) EXCHANGE_RATE_TWO, MAX(AMOUNT_TYPE) AMOUNT_TYPE, TO_NUMBER(NULL) QUANTITY FROM XTR_REVALUATION_DETAILS WHERE DEAL_TYPE = 'IRS' AND REALIZED_FLAG = 'Y' GROUP BY batch_id, swap_ref, realized_flag UNION ALL SELECT max(ACTION_CODE), max(COMPANY_CODE), max(CONTRACT_CODE), max(decode(DEAL_SUBTYPE, 'FUND', CURRENCYA, NULL)), max(decode(DEAL_SUBTYPE, 'INVEST', CURRENCYB, NULL)), TO_CHAR(SWAP_REF) REF_NUMBER, null DEAL_SUBTYPE, max(DEAL_TYPE), max(EFFECTIVE_DATE), 0 EFFECTIVE_DAYS, MAX(ENTERED_BY) ENTERED_BY, MAX(ENTERED_ON) ENTERED_ON, SUM(DECODE(DEAL_SUBTYPE,'FUND', DECODE(CURRENCYA,REVAL_CCY,-FACE_VALUE,(-FACE_VALUE*EXCHANGE_RATE_ONE)), DECODE(CURRENCYA,REVAL_CCY, FACE_VALUE,( FACE_VALUE*EXCHANGE_RATE_ONE)))) FACE_VALUE, to_number(null) FXO_SELL_REF_AMOUNT, min(PERIOD_FROM), max(PERIOD_TO), max(PORTFOLIO_CODE), max(PRODUCT_TYPE), SUM(REALISED_PL) REALISED_PL, max(REVAL_CCY), AVG(REVAL_RATE) REVAL_RATE, 0 TRANSACTION_NO, max(TRANSACTION_PERIOD), TO_NUMBER(NULL) TRANSACTION_RATE, SUM(UNREALISED_PL) UNREALISED_PL, MAX(UPDATED_BY) UPDATED_BY, MAX(UPDATED_ON) UPDATED_ON, TO_NUMBER(NULL) 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, max(OVERWRITE_TYPE), max(OVERWRITE_VALUE), max(OVERWRITE_REASON), SUM(FAIR_VALUE) FAIR_VALUE, NVL(REALIZED_FLAG,'N') REALIZED_FLAG, SUM(CUMM_GAIN_LOSS_AMOUNT) CUMM_GAIN_LOSS_AMOUNT, SUM(CURR_GAIN_LOSS_AMOUNT) CURR_GAIN_LOSS_AMOUNT, SUM(SOB_FV_GAIN_LOSS_AMOUNT) SOB_FV_GAIN_LOSS_AMOUNT, SUM(SOB_FAIR_VALUE_AMOUNT) SOB_FAIR_VALUE_AMOUNT, SUM(REVAL_CURR_FV_AMOUNT) REVAL_CURR_FV_AMOUNT, TO_NUMBER(NULL) CTR_CURR_SOB_CURR_FWD_RATE, AVG(EXCHANGE_RATE_ONE) EXCHANGE_RATE_ONE, TO_NUMBER(NULL) EXCHANGE_RATE_TWO, MAX(AMOUNT_TYPE) AMOUNT_TYPE, TO_NUMBER(NULL) QUANTITY FROM XTR_REVALUATION_DETAILS WHERE DEAL_TYPE = 'IRS' AND REALIZED_FLAG = 'N' group by batch_id, swap_ref, realized_flag

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 = 'Y' GROUP BY BATCH_ID
, ACCOUNT_NO
, REALIZED_FLAG UNION ALL 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 = 'N' GROUP BY BATCH_ID
, ACCOUNT_NO
, REALIZED_FLAG UNION ALL SELECT NULL
, MAX(COMPANY_CODE) COMPANY_CODE
, NULL CONTRACT_CODE
, MAX(CURRENCYA) CURRENCYA
, NULL
, TO_CHAR(MAX(DEAL_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
, MAX(FXO_SELL_REF_AMOUNT) 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
, 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 = 'IG'
AND REALIZED_FLAG = 'Y' GROUP BY BATCH_ID
, DEAL_NO
, REALIZED_FLAG UNION ALL SELECT NULL
, MAX(COMPANY_CODE) COMPANY_CODE
, NULL CONTRACT_CODE
, MAX(CURRENCYA) CURRENCYA
, NULL
, TO_CHAR(MAX(DEAL_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
, MAX(FXO_SELL_REF_AMOUNT) 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
, 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 = 'IG'
AND REALIZED_FLAG = 'N' GROUP BY BATCH_ID
, DEAL_NO
, REALIZED_FLAG UNION ALL SELECT NULL ACTION_CODE
, MAX(COMPANY_CODE) COMPANY_CODE
, MAX(CONTRACT_CODE) CONTRACT_CODE
, MAX(CURRENCYA) CURRENCYA
, NULL CURRENCYB
, TO_CHAR(DEAL_NO) REF_NUMBER
, MAX(DEAL_SUBTYPE) DEAL_SUBTYPE
, MAX(DEAL_TYPE) DEAL_TYPE
, TO_DATE(NULL) EFFECTIVE_DATE
, TO_NUMBER(NULL) EFFECTIVE_DAYS
, MAX(ENTERED_BY) ENTERED_BY
, MAX(ENTERED_ON) ENTERED_ON
, AVG(FACE_VALUE) FACE_VALUE
, TO_NUMBER(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
, TO_NUMBER(NULL) TRANSACTION_NO
, TO_NUMBER(NULL) 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)
, NULL OVERWRITE_REASON
, TO_NUMBER(NULL) FAIR_VALUE
, MAX(REALIZED_FLAG) 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 = 'ONC'
AND REALIZED_FLAG = 'Y' GROUP BY BATCH_ID
, DEAL_NO
, REALIZED_FLAG UNION ALL SELECT NULL ACTION_CODE
, MAX(COMPANY_CODE) COMPANY_CODE
, MAX(CONTRACT_CODE) CONTRACT_CODE
, MAX(CURRENCYA) CURRENCYA
, NULL CURRENCYB
, TO_CHAR( DEAL_NO) REF_NUMBER
, MAX(DEAL_SUBTYPE) DEAL_SUBTYPE
, MAX(DEAL_TYPE) DEAL_TYPE
, TO_DATE(NULL) EFFECTIVE_DATE
, TO_NUMBER(NULL) EFFECTIVE_DAYS
, MAX(ENTERED_BY) ENTERED_BY
, MAX(ENTERED_ON) ENTERED_ON
, AVG(FACE_VALUE) FACE_VALUE
, TO_NUMBER(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
, TO_NUMBER(NULL) TRANSACTION_NO
, TO_NUMBER(NULL) 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)
, NULL OVERWRITE_REASON
, TO_NUMBER(NULL) FAIR_VALUE
, MAX(REALIZED_FLAG) 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 = 'ONC'
AND REALIZED_FLAG = 'N' GROUP BY 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'
, 'NI'
, 'IRS') UNION ALL SELECT MAX(ACTION_CODE)
, MAX(COMPANY_CODE)
, MAX(CONTRACT_CODE)
, MAX(CURRENCYA)
, MAX(CURRENCYB)
, TO_CHAR(DEAL_NO) REF_NUMBER
, MAX(DEAL_SUBTYPE)
, MAX(DEAL_TYPE)
, MAX(EFFECTIVE_DATE)
, 0 EFFECTIVE_DAYS
, MAX(ENTERED_BY) ENTERED_BY
, MAX(ENTERED_ON) ENTERED_ON
, SUM(FACE_VALUE) FACE_VALUE
, TO_NUMBER(NULL) FXO_SELL_REF_AMOUNT
, MIN(PERIOD_FROM)
, MAX(PERIOD_TO)
, MAX(PORTFOLIO_CODE)
, MAX(PRODUCT_TYPE)
, SUM(REALISED_PL) REALISED_PL
, MAX(REVAL_CCY)
, AVG(REVAL_RATE)
, 0 TRANSACTION_NO
, MAX(TRANSACTION_PERIOD)
, AVG(TRANSACTION_RATE)
, SUM(UNREALISED_PL) UNREALISED_PL
, MAX(UPDATED_BY) UPDATED_BY
, MAX(UPDATED_ON) UPDATED_ON
, MAX(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
, MAX(OVERWRITE_TYPE)
, MAX(OVERWRITE_VALUE)
, MAX(OVERWRITE_REASON)
, SUM(FAIR_VALUE) FAIR_VALUE
, NVL(REALIZED_FLAG
, 'N') REALIZED_FLAG
, SUM(CUMM_GAIN_LOSS_AMOUNT) CUMM_GAIN_LOSS_AMOUNT
, SUM(CURR_GAIN_LOSS_AMOUNT) CURR_GAIN_LOSS_AMOUNT
, SUM(SOB_FV_GAIN_LOSS_AMOUNT) SOB_FV_GAIN_LOSS_AMOUNT
, SUM(SOB_FAIR_VALUE_AMOUNT) SOB_FAIR_VALUE_AMOUNT
, SUM(REVAL_CURR_FV_AMOUNT) REVAL_CURR_FV_AMOUNT
, TO_NUMBER(NULL) CTR_CURR_SOB_CURR_FWD_RATE
, AVG(EXCHANGE_RATE_ONE) EXCHANGE_RATE_ONE
, TO_NUMBER(NULL) EXCHANGE_RATE_TWO
, MAX(AMOUNT_TYPE) AMOUNT_TYPE
, TO_NUMBER(NULL) QUANTITY
FROM XTR_REVALUATION_DETAILS
WHERE DEAL_TYPE = 'NI'
AND REALIZED_FLAG = 'Y' GROUP BY BATCH_ID
, DEAL_NO
, REALIZED_FLAG UNION ALL SELECT MAX(ACTION_CODE)
, MAX(COMPANY_CODE)
, MAX(CONTRACT_CODE)
, MAX(CURRENCYA)
, MAX(CURRENCYB)
, TO_CHAR(DEAL_NO) REF_NUMBER
, MAX(DEAL_SUBTYPE)
, MAX(DEAL_TYPE)
, MAX(EFFECTIVE_DATE)
, MAX(EFFECTIVE_DAYS)
, MAX(ENTERED_BY) ENTERED_BY
, MAX(ENTERED_ON) ENTERED_ON
, SUM(FACE_VALUE)
, TO_NUMBER(NULL) FXO_SELL_REF_AMOUNT
, MIN(PERIOD_FROM)
, MAX(PERIOD_TO)
, MAX(PORTFOLIO_CODE)
, MAX(PRODUCT_TYPE)
, SUM(REALISED_PL) REALISED_PL
, MAX(REVAL_CCY)
, AVG(REVAL_RATE)
, 0 TRANSACTION_NO
, MAX(TRANSACTION_PERIOD)
, AVG(TRANSACTION_RATE)
, SUM(UNREALISED_PL) UNREALISED_PL
, MAX(UPDATED_BY) UPDATED_BY
, MAX(UPDATED_ON) UPDATED_ON
, MAX(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
, MAX(OVERWRITE_TYPE)
, MAX(OVERWRITE_VALUE)
, MAX(OVERWRITE_REASON)
, SUM(FAIR_VALUE) FAIR_VALUE
, NVL(REALIZED_FLAG
, 'N') REALIZED_FLAG
, SUM(CUMM_GAIN_LOSS_AMOUNT) CUMM_GAIN_LOSS_AMOUNT
, SUM(CURR_GAIN_LOSS_AMOUNT) CURR_GAIN_LOSS_AMOUNT
, SUM(SOB_FV_GAIN_LOSS_AMOUNT) SOB_FV_GAIN_LOSS_AMOUNT
, SUM(SOB_FAIR_VALUE_AMOUNT) SOB_FAIR_VALUE_AMOUNT
, SUM(REVAL_CURR_FV_AMOUNT) REVAL_CURR_FV_AMOUNT
, TO_NUMBER(NULL) CTR_CURR_SOB_CURR_FWD_RATE
, AVG(EXCHANGE_RATE_ONE)
, TO_NUMBER(NULL) EXCHANGE_RATE_TWO
, MAX(AMOUNT_TYPE) AMOUNT_TYPE
, TO_NUMBER(NULL) QUANTITY
FROM XTR_REVALUATION_DETAILS
WHERE DEAL_TYPE = 'NI'
AND NVL(REALIZED_FLAG
, 'N') = 'N' GROUP BY BATCH_ID
, DEAL_NO
, REALIZED_FLAG UNION ALL SELECT MAX(ACTION_CODE)
, MAX(COMPANY_CODE)
, MAX(CONTRACT_CODE)
, MAX(DECODE(DEAL_SUBTYPE
, 'FUND'
, CURRENCYA
, NULL))
, MAX(DECODE(DEAL_SUBTYPE
, 'INVEST'
, CURRENCYB
, NULL))
, TO_CHAR(SWAP_REF) REF_NUMBER
, NULL DEAL_SUBTYPE
, MAX(DEAL_TYPE)
, MAX(EFFECTIVE_DATE)
, 0 EFFECTIVE_DAYS
, MAX(ENTERED_BY) ENTERED_BY
, MAX(ENTERED_ON) ENTERED_ON
, SUM(DECODE(DEAL_SUBTYPE
, 'FUND'
, DECODE(CURRENCYA
, REVAL_CCY
, -FACE_VALUE
, (-FACE_VALUE*EXCHANGE_RATE_ONE))
, DECODE(CURRENCYA
, REVAL_CCY
, FACE_VALUE
, ( FACE_VALUE*EXCHANGE_RATE_ONE)))) FACE_VALUE
, TO_NUMBER(NULL) FXO_SELL_REF_AMOUNT
, MIN(PERIOD_FROM)
, MAX(PERIOD_TO)
, MAX(PORTFOLIO_CODE)
, MAX(PRODUCT_TYPE)
, SUM(REALISED_PL) REALISED_PL
, MAX(REVAL_CCY)
, AVG(REVAL_RATE) REVAL_RATE
, 0 TRANSACTION_NO
, MAX(TRANSACTION_PERIOD)
, TO_NUMBER(NULL) TRANSACTION_RATE
, SUM(UNREALISED_PL) UNREALISED_PL
, MAX(UPDATED_BY) UPDATED_BY
, MAX(UPDATED_ON) UPDATED_ON
, TO_NUMBER(NULL) 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
, MAX(OVERWRITE_TYPE)
, MAX(OVERWRITE_VALUE)
, MAX(OVERWRITE_REASON)
, SUM(FAIR_VALUE) FAIR_VALUE
, NVL(REALIZED_FLAG
, 'N') REALIZED_FLAG
, SUM(CUMM_GAIN_LOSS_AMOUNT) CUMM_GAIN_LOSS_AMOUNT
, SUM(CURR_GAIN_LOSS_AMOUNT) CURR_GAIN_LOSS_AMOUNT
, SUM(SOB_FV_GAIN_LOSS_AMOUNT) SOB_FV_GAIN_LOSS_AMOUNT
, SUM(SOB_FAIR_VALUE_AMOUNT) SOB_FAIR_VALUE_AMOUNT
, SUM(REVAL_CURR_FV_AMOUNT) REVAL_CURR_FV_AMOUNT
, TO_NUMBER(NULL) CTR_CURR_SOB_CURR_FWD_RATE
, AVG(EXCHANGE_RATE_ONE) EXCHANGE_RATE_ONE
, TO_NUMBER(NULL) EXCHANGE_RATE_TWO
, MAX(AMOUNT_TYPE) AMOUNT_TYPE
, TO_NUMBER(NULL) QUANTITY
FROM XTR_REVALUATION_DETAILS
WHERE DEAL_TYPE = 'IRS'
AND REALIZED_FLAG = 'Y' GROUP BY BATCH_ID
, SWAP_REF
, REALIZED_FLAG UNION ALL SELECT MAX(ACTION_CODE)
, MAX(COMPANY_CODE)
, MAX(CONTRACT_CODE)
, MAX(DECODE(DEAL_SUBTYPE
, 'FUND'
, CURRENCYA
, NULL))
, MAX(DECODE(DEAL_SUBTYPE
, 'INVEST'
, CURRENCYB
, NULL))
, TO_CHAR(SWAP_REF) REF_NUMBER
, NULL DEAL_SUBTYPE
, MAX(DEAL_TYPE)
, MAX(EFFECTIVE_DATE)
, 0 EFFECTIVE_DAYS
, MAX(ENTERED_BY) ENTERED_BY
, MAX(ENTERED_ON) ENTERED_ON
, SUM(DECODE(DEAL_SUBTYPE
, 'FUND'
, DECODE(CURRENCYA
, REVAL_CCY
, -FACE_VALUE
, (-FACE_VALUE*EXCHANGE_RATE_ONE))
, DECODE(CURRENCYA
, REVAL_CCY
, FACE_VALUE
, ( FACE_VALUE*EXCHANGE_RATE_ONE)))) FACE_VALUE
, TO_NUMBER(NULL) FXO_SELL_REF_AMOUNT
, MIN(PERIOD_FROM)
, MAX(PERIOD_TO)
, MAX(PORTFOLIO_CODE)
, MAX(PRODUCT_TYPE)
, SUM(REALISED_PL) REALISED_PL
, MAX(REVAL_CCY)
, AVG(REVAL_RATE) REVAL_RATE
, 0 TRANSACTION_NO
, MAX(TRANSACTION_PERIOD)
, TO_NUMBER(NULL) TRANSACTION_RATE
, SUM(UNREALISED_PL) UNREALISED_PL
, MAX(UPDATED_BY) UPDATED_BY
, MAX(UPDATED_ON) UPDATED_ON
, TO_NUMBER(NULL) 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
, MAX(OVERWRITE_TYPE)
, MAX(OVERWRITE_VALUE)
, MAX(OVERWRITE_REASON)
, SUM(FAIR_VALUE) FAIR_VALUE
, NVL(REALIZED_FLAG
, 'N') REALIZED_FLAG
, SUM(CUMM_GAIN_LOSS_AMOUNT) CUMM_GAIN_LOSS_AMOUNT
, SUM(CURR_GAIN_LOSS_AMOUNT) CURR_GAIN_LOSS_AMOUNT
, SUM(SOB_FV_GAIN_LOSS_AMOUNT) SOB_FV_GAIN_LOSS_AMOUNT
, SUM(SOB_FAIR_VALUE_AMOUNT) SOB_FAIR_VALUE_AMOUNT
, SUM(REVAL_CURR_FV_AMOUNT) REVAL_CURR_FV_AMOUNT
, TO_NUMBER(NULL) CTR_CURR_SOB_CURR_FWD_RATE
, AVG(EXCHANGE_RATE_ONE) EXCHANGE_RATE_ONE
, TO_NUMBER(NULL) EXCHANGE_RATE_TWO
, MAX(AMOUNT_TYPE) AMOUNT_TYPE
, TO_NUMBER(NULL) QUANTITY
FROM XTR_REVALUATION_DETAILS
WHERE DEAL_TYPE = 'IRS'
AND REALIZED_FLAG = 'N' GROUP BY BATCH_ID
, SWAP_REF
, REALIZED_FLAG