View: XTR_TMM_SUMMARY_V

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

View Text

SELECT 'A' ORDER_COLUMN
, R.DEAL_NUMBER DEAL_NOS
, R.TRANSACTION_NUMBER TRANS_NOS
, R.START_DATE SETTLE_DATE
, R.PRINCIPAL_ACTION REFERENCE
, AC.USER_ACTION_CODE
, R.PRINCIPAL_ADJUST AMOUNT
, R.PRINCIPAL_ADJUST ADJUST_PRINCIPAL
, 0 ADJUST_INTEREST
, DECODE(NVL(R.PI_AMOUNT_RECEIVED
, 0)
, 0
, R.NO_OF_DAYS
, 0) NOS_DAYS
, DECODE(NVL(R.PI_AMOUNT_RECEIVED
, 0)
, 0
, R.ACCUM_INTEREST
, 0) INTEREST_CFWD
, R.BALANCE_OUT BALANCE_CFWD
, R.INTEREST_RATE RATE
, DECODE(SIGN(NVL(R.PI_AMOUNT_RECEIVED
, 0) - (NVL(R.ACCUM_INTEREST_BF
, 0) + NVL(R.INTEREST
, 0)))
, 1
, (-1) * (NVL(R.PI_AMOUNT_RECEIVED
, 0) - (NVL(R.ACCUM_INTEREST_BF
, 0) + NVL(R.INTEREST
, 0)))
, 0) PRIN_DECRSE
, DECODE(NVL(R.PI_AMOUNT_RECEIVED
, 0)
, 0
, R.INTEREST
, 0) INTEREST_AMOUNT
, R.STATUS_CODE STATUS_CODE
, DS.USER_STATUS_CODE
, S.AMOUNT_NAME AMOUNT_TYPE
, S.USER_AMOUNT_TYPE FROM XTR_ROLLOVER_TRANSACTIONS_V R
, XTR_AMOUNT_TYPES S
, XTR_DEAL_STATUSES DS
, XTR_AMOUNT_ACTIONS AC
WHERE R.DEAL_TYPE = 'RTMM' AND R.PRINCIPAL_ACTION IS NOT NULL AND S.DEAL_TYPE='RTMM' AND S.AMOUNT_TYPE=NVL(R.PRINCIPAL_AMOUNT_TYPE
, 'PRINFLW') AND (R.DEAL_TYPE = DS.DEAL_TYPE
AND R.STATUS_CODE = DS.STATUS_CODE) AND (R.DEAL_TYPE = AC.DEAL_TYPE
AND NVL(R.PRINCIPAL_AMOUNT_TYPE
, 'PRINFLW') = AC.AMOUNT_TYPE
AND R.PRINCIPAL_ACTION = AC.ACTION_CODE) UNION ALL SELECT 'B' ORDER_COLUMN
, R.DEAL_NUMBER DEAL_NOS
, R.TRANSACTION_NUMBER TRANS_NOS
, NVL(R.SETTLE_DATE
, R.MATURITY_DATE) SETTLE_DATE
, DECODE(NVL(R.PI_AMOUNT_RECEIVED
, 0)
, 0
, 'ADJUSTMENT'
, 'DEDUCTION') REFERENCE
, NULL USER_ACTION_CODE
, ((-1) * R.PI_AMOUNT_RECEIVED) AMOUNT
, DECODE(SIGN(R.PI_AMOUNT_RECEIVED - (NVL(R.ACCUM_INTEREST_BF
, 0) + NVL(R.INTEREST
, 0)))
, 1
, (-1) * (R.PI_AMOUNT_RECEIVED - (NVL(R.ACCUM_INTEREST_BF
, 0) + NVL(R.INTEREST
, 0)))
, 0) ADJUST_PRINCIPAL
, DECODE(SIGN(R.PI_AMOUNT_RECEIVED - (NVL(R.ACCUM_INTEREST_BF
, 0) + NVL(R.INTEREST
, 0)))
, 1
, (-1) * (NVL(R.ACCUM_INTEREST_BF
, 0) + NVL(R.INTEREST
, 0))
, (-1) * R.PI_AMOUNT_RECEIVED) ADJUST_INTEREST
, R.NO_OF_DAYS NOS_DAYS
, R.ACCUM_INTEREST INTEREST_CFWD
, R.BALANCE_OUT BALANCE_CFWD
, R.INTEREST_RATE RATE
, 0 PRIN_DECRSE
, R.INTEREST INTEREST_AMOUNT
, R.STATUS_CODE STATUS_CODE
, DS.USER_STATUS_CODE
, S.AMOUNT_NAME AMOUNT_TYPE
, S.USER_AMOUNT_TYPE FROM XTR_ROLLOVER_TRANSACTIONS_V R
, XTR_AMOUNT_TYPES S
, XTR_DEAL_STATUSES DS WHERE R.DEAL_TYPE = 'RTMM' AND (R.PI_AMOUNT_RECEIVED <> 0 OR (R.PI_AMOUNT_RECEIVED = 0 AND R.PI_AMOUNT_DUE = 0 AND R.NO_OF_DAYS <>0)) AND S.DEAL_TYPE='RTMM' AND S.AMOUNT_TYPE='SETTLE' AND (R.DEAL_TYPE = DS.DEAL_TYPE
AND R.STATUS_CODE = DS.STATUS_CODE) UNION ALL SELECT 'C' ORDER_COLUMN
, R.DEAL_NUMBER DEAL_NOS
, R.TRANSACTION_NUMBER TRANS_NOS
, R.AMOUNT_DATE SETTLE_DATE
, 'WRTEOFF' REFERENCE
, AC.ACTION_CODE
, R.AMOUNT AMOUNT
, 0 ADJUST_PRINCIPAL
, R.AMOUNT ADJUST_INTEREST
, 0 NOS_DAYS
, 0 INTEREST_CFWD
, 0 BALANCE_CFWD
, R.TRANSACTION_RATE RATE
, 0 PRIN_DECRSE
, 0 INTEREST_AMOUNT
, R.STATUS_CODE STATUS_CODE
, DS.USER_STATUS_CODE
, S.AMOUNT_NAME AMOUNT_TYPE
, S.USER_AMOUNT_TYPE FROM XTR_DEAL_DATE_AMOUNTS_V R
, XTR_AMOUNT_TYPES S
, XTR_DEAL_STATUSES DS
, XTR_AMOUNT_ACTIONS AC WHERE R.DEAL_TYPE = 'RTMM' AND R.AMOUNT_TYPE ='WRTEINT' AND S.DEAL_TYPE='RTMM' AND S.AMOUNT_TYPE=R.AMOUNT_TYPE AND (R.DEAL_TYPE = DS.DEAL_TYPE
AND R.STATUS_CODE = DS.STATUS_CODE) AND (R.DEAL_TYPE = AC.DEAL_TYPE
AND AC.AMOUNT_TYPE = 'WRTEINT'
AND AC.ACTION_CODE = 'WRTEOFF')

Columns