View: XTR_REVALUATION_DETAILS_SUM_V

Product:	XTR - Treasury
Description:
Implementation/DBA Data:	APPS.XTR_REVALUATION_DETAILS_SUM_V

View Text

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

Columns