View: XTR_REVALUATION_DETAILS_V

Product:	XTR - Treasury
Description:
Implementation/DBA Data:	APPS.XTR_REVALUATION_DETAILS_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 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')

Columns