eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.XTR_TMM_SUMMARY_V

Source

View Text - Preformatted


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')

View Text - HTML Formatted

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')