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.

[Home] [Help]

View: XTR_CASH_STATEMENTS_V

Product:	XTR - Treasury
Description:	- Retrofitted
Implementation/DBA Data:	Not implemented in this database

View Text

SELECT R.TRANSACTION_NUMBER TNUM
, R.DEAL_NUMBER DEAL_NUM
, R.INTEREST + DECODE(NVL(R.ACCUM_INT_ACTION
, 'NILL')
, 'NILL'
, NVL(R.ACCUM_INTEREST_BF
, 0)
, 0) INT_AMT
, 'A' SUB_ORD
, 0 INT_SET
, 0 BROKERAGE
, 0 TAX
, 'BALANCE FORWARD' COMMENTS
, DECODE(NVL(R.ACCUM_INT_ACTION
, 'NILL')
, 'NILL'
, DECODE(GREATEST(NVL(R.MATURITY_DATE
, P.PERIOD_TO + 1)
, P.PERIOD_TO)
, P.PERIOD_TO
, R.NO_OF_DAYS
, P.PERIOD_FROM - R.START_DATE + DECODE(P.INCL_TO_DATE
, 'Y'
, 1
, 0))
, 0) DAYS
, R.CURRENCY CCY
, NVL(TO_CHAR(R.MATURITY_DATE
, 'DDMONYY')
, R.PRODUCT_TYPE) TERMDESC
, P.PERIOD_FROM ORD_DATE
, NVL(R.INTEREST_RATE
, 0) INT_RATE
, R.PRODUCT_TYPE PRODTYPE
, R.PRINCIPAL_ADJUST PRIN_TOT
, DECODE(NVL(R.CLIENT_CODE
, 'NILL')
, 'NILL'
, R.COMPANY_CODE
, R.CPARTY_CODE) PRIN_PARTY
, R.DEAL_SUBTYPE DEAL_SUB
, ST.USER_DEAL_SUBTYPE
, NVL(R.CLIENT_CODE
, R.CPARTY_CODE) PTY
, R.CLIENT_CODE CLIENT
, R.CPARTY_CODE CPARTY
, R.COMPANY_CODE COMPANY
, R.YEAR_CALC_TYPE CALC_TYPE
, R.DEALER_CODE DEALER
, P.SELECTION_ID REF
, P.PERIOD_FROM PERIOD_FROM
, P.PERIOD_TO PERIOD_TO FROM XTR_ROLLOVER_TRANSACTIONS_V R
, XTR_CASH_PERIOD P
, XTR_DEAL_SUBTYPES ST WHERE ((R.START_DATE < P.PERIOD_FROM
AND NVL(R.MATURITY_DATE
, P.PERIOD_FROM + 1) >= P.PERIOD_FROM ) OR (R.START_DATE = P.PERIOD_FROM
AND R.CREATED_BY = 'AUTO PROCESS')) AND R.DEAL_TYPE = P.DEAL_TYPE AND R.STATUS_CODE != 'CANCELLED' AND R.COMPANY_CODE LIKE NVL(UPPER(P.COMPANY_CODE)
, '%') AND R.DEAL_SUBTYPE LIKE NVL(UPPER(P.DEAL_SUBTYPE)
, '%') AND R.PRODUCT_TYPE LIKE NVL(UPPER(P.PRODUCT_TYPE)
, '%') AND R.CURRENCY LIKE NVL(UPPER(P.CURRENCY)
, '%') AND R.PORTFOLIO_CODE LIKE NVL(UPPER(P.PORTFOLIO_CODE)
, '%') AND R.SECURITY_TYPE LIKE NVL(P.SECURITY_TYPE
, '%') AND R.CPARTY_CODE LIKE NVL(UPPER(P.CPARTY_CODE)
, '%') AND ((P.AS_PRINCIPAL = 'Y'
AND R.CLIENT_CODE IS NULL) OR (R.CLIENT_CODE LIKE NVL(UPPER(P.CLIENT_CODE)
, '%')
AND P.AS_PRINCIPAL = 'N')) AND (R.DEAL_TYPE = ST.DEAL_TYPE
AND R.DEAL_SUBTYPE = ST.DEAL_SUBTYPE) UNION ALL SELECT R.TRANSACTION_NUMBER TNUM
, R.DEAL_NUMBER DEAL_NUM
, DECODE(NVL(R.REPAY_AMOUNT
, 0)
, 0
, 0
, DECODE(GREATEST(NVL(R.MATURITY_DATE
, P.PERIOD_TO + 1)
, P.PERIOD_TO)
, P.PERIOD_TO
, NVL(R.INTEREST
, 0)
, R.BALANCE_OUT * (R.INTEREST_RATE / 100) * (P.PERIOD_TO - DECODE(GREATEST(P.PERIOD_FROM
, R.START_DATE)
, P.PERIOD_FROM
, P.PERIOD_FROM
, R.START_DATE) + DECODE(P.INCL_TO_DATE
, 'Y'
, 1
, 0)) / DECODE(R.YEAR_CALC_TYPE
, 'ACTUAL365'
, 365
, 360))) INT_AMT
, 'B' SUB_ORD
, NVL(R.ACCUM_INTEREST_BF
, 0) INT_SET
, 0 BROKERAGE
, NVL(R.TAX_AMOUNT
, 0) TAX
, 'REINVEST INTEREST' COMMENTS
, DECODE(GREATEST(NVL(R.MATURITY_DATE
, P.PERIOD_TO + 1)
, P.PERIOD_TO)
, P.PERIOD_TO
, R.NO_OF_DAYS
, P.PERIOD_TO - R.START_DATE + DECODE(P.INCL_TO_DATE
, 'Y'
, 1
, 0)) DAYS
, R.CURRENCY CCY
, NVL(TO_CHAR(R.MATURITY_DATE
, 'DDMONYY')
, R.PRODUCT_TYPE) TERMDESC
, R.START_DATE ORD_DATE
, NVL(R.INTEREST_RATE
, 0) INT_RATE
, R.PRODUCT_TYPE PRODTYPE
, NVL(R.ACCUM_INTEREST_BF
, 0) - NVL(R.TAX_AMOUNT
, 0) PRIN_TOT
, DECODE(NVL(R.CLIENT_CODE
, 'NILL')
, 'NILL'
, R.COMPANY_CODE
, R.CPARTY_CODE) PRIN_PARTY
, R.DEAL_SUBTYPE DEAL_SUB
, ST.USER_DEAL_SUBTYPE
, NVL(R.CLIENT_CODE
, R.CPARTY_CODE) PTY
, R.CLIENT_CODE CLIENT
, R.CPARTY_CODE CPARTY
, R.COMPANY_CODE COMPANY
, R.YEAR_CALC_TYPE CALC_TYPE
, R.DEALER_CODE DEALER
, P.SELECTION_ID REF
, P.PERIOD_FROM PERIOD_FROM
, P.PERIOD_TO PERIOD_TO
FROM XTR_ROLLOVER_TRANSACTIONS_V R
, XTR_CASH_PERIOD P
, XTR_DEAL_SUBTYPES ST
WHERE R.START_DATE = P.PERIOD_FROM
AND R.ACCUM_INT_ACTION = 'R'
AND R.CREATED_BY = 'AUTO PROCESS'
AND R.DEAL_TYPE = P.DEAL_TYPE
AND R.STATUS_CODE != 'CANCELLED'
AND R.COMPANY_CODE LIKE NVL(UPPER(P.COMPANY_CODE)
, '%')
AND R.DEAL_SUBTYPE LIKE NVL(UPPER(P.DEAL_SUBTYPE)
, '%')
AND R.PRODUCT_TYPE LIKE NVL(UPPER(P.PRODUCT_TYPE)
, '%')
AND R.CURRENCY LIKE NVL(UPPER(P.CURRENCY)
, '%')
AND R.PORTFOLIO_CODE LIKE NVL(UPPER(P.PORTFOLIO_CODE)
, '%')
AND R.SECURITY_TYPE LIKE NVL(P.SECURITY_TYPE
, '%')
AND R.CPARTY_CODE LIKE NVL(UPPER(P.CPARTY_CODE)
, '%')
AND ((P.AS_PRINCIPAL = 'Y'
AND R.CLIENT_CODE IS NULL) OR (R.CLIENT_CODE LIKE NVL(UPPER(P.CLIENT_CODE)
, '%')
AND P.AS_PRINCIPAL = 'N')) AND (R.DEAL_TYPE = ST.DEAL_TYPE
AND R.DEAL_SUBTYPE = ST.DEAL_SUBTYPE) UNION ALL SELECT R.TRANSACTION_NUMBER TNUM
, R.DEAL_NUMBER DEAL_NUM
, DECODE(NVL(R.OLD_PRODUCT_TYPE
, 'NILL')
, 'NILL'
, DECODE(NVL(R.REPAY_AMOUNT
, 0)
, 0
, DECODE(GREATEST(NVL(R.MATURITY_DATE
, P.PERIOD_TO + 1)
, P.PERIOD_TO)
, P.PERIOD_TO
, NVL(R.INTEREST
, 0)
, R.BALANCE_OUT * (R.INTEREST_RATE / 100) * (P.PERIOD_TO - DECODE(GREATEST(P.PERIOD_FROM
, R.START_DATE)
, P.PERIOD_FROM
, P.PERIOD_FROM
, R.START_DATE) + DECODE(P.INCL_TO_DATE
, 'Y'
, 1
, 0)) / DECODE(R.YEAR_CALC_TYPE
, 'ACTUAL365'
, 365
, 360))
, 0)
, 0) INT_AMT
, 'C' SUB_ORD
, NVL(R.ACCUM_INTEREST_BF
, 0) INT_SET
, NVL(R.BROKERAGE_AMOUNT
, 0) BROKERAGE
, NVL(R.TAX_AMOUNT
, 0) TAX
, 'REINVEST INTEREST' COMMENTS
, 0 DAYS
, R.CURRENCY CCY
, NVL(TO_CHAR(R.MATURITY_DATE
, 'DDMONYY')
, NVL(R.OLD_PRODUCT_TYPE
, R.PRODUCT_TYPE)) TERMDESC
, R.START_DATE ORD_DATE
, 0 INT_RATE
, NVL(R.OLD_PRODUCT_TYPE
, R.PRODUCT_TYPE) PRODTYPE
, NVL(R.ACCUM_INTEREST_BF
, 0) - NVL(R.TAX_AMOUNT
, 0) PRIN_TOT
, DECODE(NVL(R.CLIENT_CODE
, 'NILL')
, 'NILL'
, R.COMPANY_CODE
, R.CPARTY_CODE) PRIN_PARTY
, R.DEAL_SUBTYPE DEAL_SUB
, ST.USER_DEAL_SUBTYPE
, NVL(R.CLIENT_CODE
, R.CPARTY_CODE) PTY
, R.CLIENT_CODE CLIENT
, R.CPARTY_CODE CPARTY
, R.COMPANY_CODE COMPANY
, R.YEAR_CALC_TYPE CALC_TYPE
, R.DEALER_CODE DEALER
, P.SELECTION_ID REF
, P.PERIOD_FROM PERIOD_FROM
, P.PERIOD_TO PERIOD_TO
FROM XTR_ROLLOVER_TRANSACTIONS_V R
, XTR_CASH_PERIOD P
, XTR_DEAL_SUBTYPES ST
WHERE R.START_DATE BETWEEN P.PERIOD_FROM
AND P.PERIOD_TO
AND R.ACCUM_INT_ACTION = 'R'
AND R.CREATED_BY != 'AUTO PROCESS'
AND R.DEAL_TYPE = P.DEAL_TYPE
AND R.STATUS_CODE != 'CANCELLED'
AND R.COMPANY_CODE LIKE NVL(UPPER(P.COMPANY_CODE)
, '%')
AND R.DEAL_SUBTYPE LIKE NVL(UPPER(P.DEAL_SUBTYPE)
, '%')
AND NVL(R.OLD_PRODUCT_TYPE
, R.PRODUCT_TYPE) LIKE NVL(UPPER(P.PRODUCT_TYPE)
, '%')
AND R.SECURITY_TYPE LIKE NVL(P.SECURITY_TYPE
, '%')
AND R.CURRENCY LIKE NVL(UPPER(P.CURRENCY)
, '%')
AND R.PORTFOLIO_CODE LIKE NVL(UPPER(P.PORTFOLIO_CODE)
, '%')
AND R.CPARTY_CODE LIKE NVL(UPPER(P.CPARTY_CODE)
, '%')
AND ((P.AS_PRINCIPAL = 'Y'
AND R.CLIENT_CODE IS NULL) OR (R.CLIENT_CODE LIKE NVL(UPPER(P.CLIENT_CODE)
, '%')
AND P.AS_PRINCIPAL = 'N')) AND (R.DEAL_TYPE = ST.DEAL_TYPE
AND R.DEAL_SUBTYPE = ST.DEAL_SUBTYPE) UNION ALL SELECT R.TRANSACTION_NUMBER TNUM
, R.DEAL_NUMBER DEAL_NUM
, 0 INT_AMT
, 'D' SUB_ORD
, NVL(R.ACCUM_INTEREST_BF
, 0) INT_SET
, NVL(R.BROKERAGE_AMOUNT
, 0) BROKERAGE
, NVL(R.TAX_AMOUNT
, 0) TAX
, DECODE(NVL(R.OLD_PRODUCT_TYPE
, R.PRODUCT_TYPE)
, R.PRODUCT_TYPE
, 'INT. SETTLED'
, 'INT. TRANSFERRED') COMMENTS
, DECODE(NVL(R.REPAY_AMOUNT
, 0)
, 0
, NVL(R.NO_OF_DAYS
, 0)
, 0) DAYS
, R.CURRENCY CCY
, NVL(TO_CHAR(R.MATURITY_DATE
, 'DDMONYY')
, NVL(R.OLD_PRODUCT_TYPE
, R.PRODUCT_TYPE)) TERMDESC
, R.START_DATE ORD_DATE
, 0 INT_RATE
, NVL(R.OLD_PRODUCT_TYPE
, R.PRODUCT_TYPE) PRODTYPE
, 0 PRIN_TOT
, DECODE(NVL(R.CLIENT_CODE
, 'NILL')
, 'NILL'
, R.COMPANY_CODE
, R.CPARTY_CODE) PRIN_PARTY
, R.DEAL_SUBTYPE DEAL_SUB
, ST.USER_DEAL_SUBTYPE
, NVL(R.CLIENT_CODE
, R.CPARTY_CODE) PTY
, R.CLIENT_CODE CLIENT
, R.CPARTY_CODE CPARTY
, R.COMPANY_CODE COMPANY
, R.YEAR_CALC_TYPE CALC_TYPE
, R.DEALER_CODE DEALER
, P.SELECTION_ID REF
, P.PERIOD_FROM PERIOD_FROM
, P.PERIOD_TO PERIOD_TO
FROM XTR_ROLLOVER_TRANSACTIONS_V R
, XTR_CASH_PERIOD P
, XTR_DEAL_SUBTYPES ST
WHERE R.START_DATE BETWEEN P.PERIOD_FROM
AND P.PERIOD_TO
AND R.ACCUM_INT_ACTION = 'P'
AND R.DEAL_TYPE = P.DEAL_TYPE
AND R.STATUS_CODE != 'CANCELLED'
AND R.COMPANY_CODE LIKE NVL(UPPER(P.COMPANY_CODE)
, '%')
AND R.DEAL_SUBTYPE LIKE NVL(UPPER(P.DEAL_SUBTYPE)
, '%')
AND NVL(R.OLD_PRODUCT_TYPE
, R.PRODUCT_TYPE) LIKE NVL(UPPER(P.PRODUCT_TYPE)
, '%')
AND R.SECURITY_TYPE LIKE NVL(P.SECURITY_TYPE
, '%')
AND R.CURRENCY LIKE NVL(UPPER(P.CURRENCY)
, '%')
AND R.PORTFOLIO_CODE LIKE NVL(UPPER(P.PORTFOLIO_CODE)
, '%')
AND R.CPARTY_CODE LIKE NVL(UPPER(P.CPARTY_CODE)
, '%')
AND ((P.AS_PRINCIPAL = 'Y'
AND R.CLIENT_CODE IS NULL) OR (R.CLIENT_CODE LIKE NVL(UPPER(P.CLIENT_CODE)
, '%')
AND P.AS_PRINCIPAL = 'N')) AND (R.DEAL_TYPE = ST.DEAL_TYPE
AND R.DEAL_SUBTYPE = ST.DEAL_SUBTYPE) UNION ALL SELECT R.TRANSACTION_NUMBER TNUM
, R.DEAL_NUMBER DEAL_NUM
, DECODE(TRUNC(R.MATURITY_DATE) - TRUNC(R.START_DATE)
, 0
, 0
, DECODE(GREATEST(NVL(R.MATURITY_DATE
, P.PERIOD_TO + 1)
, P.PERIOD_TO)
, P.PERIOD_TO
, R.INTEREST
, R.BALANCE_OUT * (R.INTEREST_RATE / 100) * (P.PERIOD_TO - DECODE(GREATEST(P.PERIOD_FROM
, R.START_DATE)
, P.PERIOD_FROM
, P.PERIOD_FROM
, R.START_DATE) + DECODE(P.INCL_TO_DATE
, 'Y'
, 1
, 0)) / DECODE(R.YEAR_CALC_TYPE
, 'ACTUAL365'
, 365
, 360))) INT_AMT
, 'E' SUB_ORD
, 0 INT_SET
, NVL(R.BROKERAGE_AMOUNT
, 0) BROKERAGE
, 0 TAX
, 'NEW TRANSACTION' COMMENTS
, DECODE(GREATEST(NVL(R.MATURITY_DATE
, P.PERIOD_TO + 1)
, P.PERIOD_TO)
, P.PERIOD_TO
, R.NO_OF_DAYS
, DECODE(LEAST(NVL(R.MATURITY_DATE
, P.PERIOD_TO + 1)
, P.PERIOD_TO)
, P.PERIOD_TO
, (P.PERIOD_TO - R.START_DATE + DECODE(P.INCL_TO_DATE
, 'Y'
, 1
, 0))
, DECODE(R.REPAY_AMOUNT
, 0
, DECODE(R.OLD_PRODUCT_TYPE
, R.PRODUCT_TYPE
, R.NO_OF_DAYS)
, 0))) DAYS
, R.CURRENCY CCY
, NVL(TO_CHAR(R.MATURITY_DATE
, 'DDMONYY')
, R.PRODUCT_TYPE) TERMDESC
, R.START_DATE ORD_DATE
, NVL(R.INTEREST_RATE
, 0) INT_RATE
, R.PRODUCT_TYPE PRODTYPE
, R.PRINCIPAL_ADJUST PRIN_TOT
, DECODE(NVL(R.CLIENT_CODE
, 'NILL')
, 'NILL'
, R.COMPANY_CODE
, R.CPARTY_CODE) PRIN_PARTY
, R.DEAL_SUBTYPE DEAL_SUB
, ST.USER_DEAL_SUBTYPE
, NVL(R.CLIENT_CODE
, R.CPARTY_CODE) PTY
, R.CLIENT_CODE CLIENT
, R.CPARTY_CODE CPARTY
, R.COMPANY_CODE COMPANY
, R.YEAR_CALC_TYPE CALC_TYPE
, R.DEALER_CODE DEALER
, P.SELECTION_ID REF
, P.PERIOD_FROM PERIOD_FROM
, P.PERIOD_TO PERIOD_TO
FROM XTR_ROLLOVER_TRANSACTIONS_V R
, XTR_CASH_PERIOD P
, XTR_DEAL_SUBTYPES ST
WHERE R.START_DATE BETWEEN P.PERIOD_FROM
AND P.PERIOD_TO
AND R.PRINCIPAL_AMOUNT_TYPE = 'NEW'
AND R.DEAL_TYPE = P.DEAL_TYPE
AND R.STATUS_CODE != 'CANCELLED'
AND R.COMPANY_CODE LIKE NVL(UPPER(P.COMPANY_CODE)
, '%')
AND R.DEAL_SUBTYPE LIKE NVL(UPPER(P.DEAL_SUBTYPE)
, '%')
AND R.PRODUCT_TYPE LIKE NVL(UPPER(P.PRODUCT_TYPE)
, '%')
AND R.CURRENCY LIKE NVL(UPPER(P.CURRENCY)
, '%')
AND R.PORTFOLIO_CODE LIKE NVL(UPPER(P.PORTFOLIO_CODE)
, '%')
AND R.SECURITY_TYPE LIKE NVL(P.SECURITY_TYPE
, '%')
AND R.CPARTY_CODE LIKE NVL(UPPER(P.CPARTY_CODE)
, '%')
AND ((P.AS_PRINCIPAL = 'Y'
AND R.CLIENT_CODE IS NULL) OR (R.CLIENT_CODE LIKE NVL(UPPER(P.CLIENT_CODE)
, '%')
AND P.AS_PRINCIPAL = 'N')) AND (R.DEAL_TYPE = ST.DEAL_TYPE
AND R.DEAL_SUBTYPE = ST.DEAL_SUBTYPE) UNION ALL SELECT R.TRANSACTION_NUMBER TNUM
, R.DEAL_NUMBER DEAL_NUM
, DECODE(NVL(R.OLD_PRODUCT_TYPE
, 'NILL')
, 'NILL'
, DECODE(GREATEST(NVL(R.MATURITY_DATE
, P.PERIOD_TO + 1)
, P.PERIOD_TO)
, P.PERIOD_TO
, R.INTEREST
, R.BALANCE_OUT * (R.INTEREST_RATE / 100) * (P.PERIOD_TO - DECODE(GREATEST(P.PERIOD_FROM
, R.START_DATE)
, P.PERIOD_FROM
, P.PERIOD_FROM
, R.START_DATE) + DECODE(P.INCL_TO_DATE
, 'Y'
, 1
, 0)) / DECODE(R.YEAR_CALC_TYPE
, 'ACTUAL365'
, 365
, 360))
, 0) INT_AMT
, 'F' SUB_ORD
, 0 INT_SET
, NVL(R.BROKERAGE_AMOUNT
, 0) BROKERAGE
, 0 TAX
, 'PRINCIPAL REPAYMENT' COMMENTS
, DECODE(NVL(R.OLD_PRODUCT_TYPE
, 'NILL')
, 'NILL'
, DECODE(TRUNC(R.MATURITY_DATE) - TRUNC(R.START_DATE)
, 0
, 0
, DECODE(LEAST(NVL(R.MATURITY_DATE
, P.PERIOD_TO + 1) - P.PERIOD_TO)
, 1
, (P.PERIOD_TO - R.START_DATE + DECODE(P.INCL_TO_DATE
, 'Y'
, 1
, 0))
, R.NO_OF_DAYS))
, 0) DAYS
, R.CURRENCY CCY
, NVL(TO_CHAR(R.MATURITY_DATE
, 'DDMONYY')
, NVL(R.OLD_PRODUCT_TYPE
, R.PRODUCT_TYPE)) TERMDESC
, R.START_DATE ORD_DATE
, DECODE(NVL(R.OLD_PRODUCT_TYPE
, 'NILL')
, 'NILL'
, NVL(R.INTEREST_RATE
, 0)
, 0) INT_RATE
, NVL(R.OLD_PRODUCT_TYPE
, R.PRODUCT_TYPE) PRODTYPE
, NVL((-1) * R.REPAY_AMOUNT
, 0) PRIN_TOT
, DECODE(NVL(R.CLIENT_CODE
, 'NILL')
, 'NILL'
, R.COMPANY_CODE
, R.CPARTY_CODE) PRIN_PARTY
, R.DEAL_SUBTYPE DEAL_SUB
, ST.USER_DEAL_SUBTYPE
, NVL(R.CLIENT_CODE
, R.CPARTY_CODE) PTY
, R.CLIENT_CODE CLIENT
, R.CPARTY_CODE CPARTY
, R.COMPANY_CODE COMPANY
, R.YEAR_CALC_TYPE CALC_TYPE
, R.DEALER_CODE DEALER
, P.SELECTION_ID REF
, P.PERIOD_FROM PERIOD_FROM
, P.PERIOD_TO PERIOD_TO
FROM XTR_ROLLOVER_TRANSACTIONS_V R
, XTR_CASH_PERIOD P
, XTR_DEAL_SUBTYPES ST
WHERE R.START_DATE BETWEEN P.PERIOD_FROM
AND P.PERIOD_TO
AND NVL(R.REPAY_AMOUNT
, 0) > 0
AND R.DEAL_TYPE = P.DEAL_TYPE
AND R.STATUS_CODE != 'CANCELLED'
AND R.COMPANY_CODE LIKE NVL(UPPER(P.COMPANY_CODE)
, '%')
AND R.DEAL_SUBTYPE LIKE NVL(UPPER(P.DEAL_SUBTYPE)
, '%')
AND NVL(R.OLD_PRODUCT_TYPE
, R.PRODUCT_TYPE) LIKE NVL(UPPER(P.PRODUCT_TYPE)
, '%')
AND R.CURRENCY LIKE NVL(UPPER(P.CURRENCY)
, '%')
AND R.PORTFOLIO_CODE LIKE NVL(UPPER(P.PORTFOLIO_CODE)
, '%')
AND R.SECURITY_TYPE LIKE NVL(P.SECURITY_TYPE
, '%')
AND R.CPARTY_CODE LIKE NVL(UPPER(P.CPARTY_CODE)
, '%')
AND ((P.AS_PRINCIPAL = 'Y'
AND R.CLIENT_CODE IS NULL) OR (R.CLIENT_CODE LIKE NVL(UPPER(P.CLIENT_CODE)
, '%')
AND P.AS_PRINCIPAL = 'N')) AND (R.DEAL_TYPE = ST.DEAL_TYPE
AND R.DEAL_SUBTYPE = ST.DEAL_SUBTYPE) UNION ALL SELECT R.TRANSACTION_NUMBER TNUM
, R.DEAL_NUMBER DEAL_NUM
, DECODE(GREATEST(NVL(R.MATURITY_DATE
, P.PERIOD_TO + 1)
, P.PERIOD_TO)
, P.PERIOD_TO
, R.INTEREST
, R.BALANCE_OUT * (R.INTEREST_RATE / 100) * (P.PERIOD_TO - DECODE(GREATEST(P.PERIOD_FROM
, R.START_DATE)
, P.PERIOD_FROM
, P.PERIOD_FROM
, R.START_DATE) + DECODE(P.INCL_TO_DATE
, 'Y'
, 1
, 0)) / DECODE(R.YEAR_CALC_TYPE
, 'ACTUAL365'
, 365
, 360)) INT_AMT
, 'G' SUB_ORD
, 0 INT_SET
, NVL(R.BROKERAGE_AMOUNT
, 0) BROKERAGE
, 0 TAX
, 'RENEGOTIATION' COMMENTS
, NVL(R.NO_OF_DAYS
, 0) DAYS
, R.CURRENCY CCY
, NVL(TO_CHAR(R.MATURITY_DATE
, 'DDMONYY')
, R.PRODUCT_TYPE) TERMDESC
, R.START_DATE ORD_DATE
, NVL(R.INTEREST_RATE
, 0) INT_RATE
, R.PRODUCT_TYPE PRODTYPE
, 0 PRIN_TOT
, DECODE(NVL(R.CLIENT_CODE
, 'NILL')
, 'NILL'
, R.COMPANY_CODE
, R.CPARTY_CODE) PRIN_PARTY
, R.DEAL_SUBTYPE DEAL_SUB
, ST.USER_DEAL_SUBTYPE
, NVL(R.CLIENT_CODE
, R.CPARTY_CODE) PTY
, R.CLIENT_CODE CLIENT
, R.CPARTY_CODE CPARTY
, R.COMPANY_CODE COMPANY
, R.YEAR_CALC_TYPE CALC_TYPE
, R.DEALER_CODE DEALER
, P.SELECTION_ID REF
, P.PERIOD_FROM PERIOD_FROM
, P.PERIOD_TO PERIOD_TO
FROM XTR_ROLLOVER_TRANSACTIONS_V R
, XTR_CASH_PERIOD P
, XTR_DEAL_SUBTYPES ST
WHERE R.START_DATE BETWEEN P.PERIOD_FROM
AND P.PERIOD_TO
AND R.OLD_PRODUCT_TYPE IS NULL
AND R.CREATED_BY != 'AUTO PROCESS'
AND NVL(R.ACCUM_INT_ACTION
, 'NILL') != 'R'
AND NVL(R.REPAY_AMOUNT
, 0) = 0
AND R.PRINCIPAL_AMOUNT_TYPE IS NULL
AND R.DEAL_TYPE = P.DEAL_TYPE
AND R.STATUS_CODE != 'CANCELLED'
AND R.COMPANY_CODE LIKE NVL(UPPER(P.COMPANY_CODE)
, '%')
AND R.DEAL_SUBTYPE LIKE NVL(UPPER(P.DEAL_SUBTYPE)
, '%')
AND R.PRODUCT_TYPE LIKE NVL(UPPER(P.PRODUCT_TYPE)
, '%')
AND R.SECURITY_TYPE LIKE NVL(P.SECURITY_TYPE
, '%')
AND R.CURRENCY LIKE NVL(UPPER(P.CURRENCY)
, '%')
AND R.PORTFOLIO_CODE LIKE NVL(UPPER(P.PORTFOLIO_CODE)
, '%')
AND R.CPARTY_CODE LIKE NVL(UPPER(P.CPARTY_CODE)
, '%')
AND ((P.AS_PRINCIPAL = 'Y'
AND R.CLIENT_CODE IS NULL) OR (R.CLIENT_CODE LIKE NVL(UPPER(P.CLIENT_CODE)
, '%')
AND P.AS_PRINCIPAL = 'N')) AND (R.DEAL_TYPE = ST.DEAL_TYPE
AND R.DEAL_SUBTYPE = ST.DEAL_SUBTYPE) UNION ALL SELECT R.TRANSACTION_NUMBER TNUM
, R.DEAL_NUMBER DEAL_NUM
, 0 INT_AMT
, 'H' SUB_ORD
, DECODE(NVL(R.ACCUM_INT_ACTION
, 'NILL')
, 'NILL'
, NVL(R.ACCUM_INTEREST_BF
, 0)
, 0) INT_SET
, NVL(R.BROKERAGE_AMOUNT
, 0) BROKERAGE
, 0 TAX
, 'TRANSFER TO '||R.PRODUCT_TYPE COMMENTS
, 0 DAYS
, R.CURRENCY CCY
, NVL(TO_CHAR(R.MATURITY_DATE
, 'DDMONYY')
, R.OLD_PRODUCT_TYPE) TERMDESC
, R.START_DATE ORD_DATE
, 0 INT_RATE
, R.OLD_PRODUCT_TYPE PRODTYPE
, (-1) * R.PRINCIPAL_ADJUST PRIN_TOT
, DECODE(NVL(R.CLIENT_CODE
, 'NILL')
, 'NILL'
, R.COMPANY_CODE
, R.CPARTY_CODE) PRIN_PARTY
, R.DEAL_SUBTYPE DEAL_SUB
, ST.USER_DEAL_SUBTYPE
, NVL(R.CLIENT_CODE
, R.CPARTY_CODE) PTY
, R.CLIENT_CODE CLIENT
, R.CPARTY_CODE CPARTY
, R.COMPANY_CODE COMPANY
, R.YEAR_CALC_TYPE CALC_TYPE
, R.DEALER_CODE DEALER
, P.SELECTION_ID REF
, P.PERIOD_FROM PERIOD_FROM
, P.PERIOD_TO PERIOD_TO
FROM XTR_ROLLOVER_TRANSACTIONS_V R
, XTR_CASH_PERIOD P
, XTR_DEAL_SUBTYPES ST
WHERE R.START_DATE BETWEEN P.PERIOD_FROM
AND P.PERIOD_TO
AND R.OLD_PRODUCT_TYPE != R.PRODUCT_TYPE
AND R.DEAL_TYPE = P.DEAL_TYPE
AND R.STATUS_CODE != 'CANCELLED'
AND R.COMPANY_CODE LIKE NVL(UPPER(P.COMPANY_CODE)
, '%')
AND R.DEAL_SUBTYPE LIKE NVL(UPPER(P.DEAL_SUBTYPE)
, '%')
AND R.OLD_PRODUCT_TYPE LIKE NVL(UPPER(P.PRODUCT_TYPE)
, '%')
AND R.CURRENCY LIKE NVL(UPPER(P.CURRENCY)
, '%')
AND R.PORTFOLIO_CODE LIKE NVL(UPPER(P.PORTFOLIO_CODE)
, '%')
AND R.SECURITY_TYPE LIKE NVL(P.SECURITY_TYPE
, '%')
AND R.CPARTY_CODE LIKE NVL(UPPER(P.CPARTY_CODE)
, '%')
AND ((P.AS_PRINCIPAL = 'Y'
AND R.CLIENT_CODE IS NULL) OR (R.CLIENT_CODE LIKE NVL(UPPER(P.CLIENT_CODE)
, '%')
AND P.AS_PRINCIPAL = 'N')) AND (R.DEAL_TYPE = ST.DEAL_TYPE
AND R.DEAL_SUBTYPE = ST.DEAL_SUBTYPE) UNION ALL SELECT R.TRANSACTION_NUMBER TNUM
, R.DEAL_NUMBER DEAL_NUM
, DECODE(GREATEST(NVL(R.MATURITY_DATE
, P.PERIOD_TO + 1)
, P.PERIOD_TO)
, P.PERIOD_TO
, R.INTEREST
, R.BALANCE_OUT * (R.INTEREST_RATE / 100) * (P.PERIOD_TO - DECODE(GREATEST(P.PERIOD_FROM
, R.START_DATE)
, P.PERIOD_FROM
, P.PERIOD_FROM
, R.START_DATE) + DECODE(P.INCL_TO_DATE
, 'Y'
, 1
, 0)) / DECODE(R.YEAR_CALC_TYPE
, 'ACTUAL365'
, 365
, 360)) + DECODE(NVL(R.ACCUM_INT_ACTION
, 'NILL')
, 'NILL'
, NVL(R.ACCUM_INTEREST_BF
, 0)
, 0) INT_AMT
, 'I' SUB_ORD
, 0 INT_SET
, NVL(R.BROKERAGE_AMOUNT
, 0) BROKERAGE
, 0 TAX
, 'TRANSFER
FROM '||R.OLD_PRODUCT_TYPE COMMENTS
, DECODE(NVL(R.NO_OF_DAYS
, 0)
, 0
, (P.PERIOD_TO - R.START_DATE + DECODE(P.INCL_TO_DATE
, 'Y'
, 1
, 0))
, R.NO_OF_DAYS) DAYS
, R.CURRENCY CCY
, NVL(TO_CHAR(R.MATURITY_DATE
, 'DDMONYY')
, R.PRODUCT_TYPE) TERMDESC
, R.START_DATE ORD_DATE
, NVL(R.INTEREST_RATE
, 0) INT_RATE
, R.PRODUCT_TYPE PRODTYPE
, R.PRINCIPAL_ADJUST PRINTOT
, DECODE(NVL(R.CLIENT_CODE
, 'NILL')
, 'NILL'
, R.COMPANY_CODE
, R.CPARTY_CODE) PRIN_PARTY
, R.DEAL_SUBTYPE DEAL_SUB
, ST.USER_DEAL_SUBTYPE
, NVL(R.CLIENT_CODE
, R.CPARTY_CODE) PTY
, R.CLIENT_CODE CLIENT
, R.CPARTY_CODE CPARTY
, R.COMPANY_CODE COMPANY
, R.YEAR_CALC_TYPE CALC_TYPE
, R.DEALER_CODE DEALER
, P.SELECTION_ID REF
, P.PERIOD_FROM PERIOD_FROM
, P.PERIOD_TO PERIOD_TO
FROM XTR_ROLLOVER_TRANSACTIONS_V R
, XTR_CASH_PERIOD P
, XTR_DEAL_SUBTYPES ST
WHERE R.START_DATE BETWEEN P.PERIOD_FROM
AND P.PERIOD_TO
AND R.OLD_PRODUCT_TYPE != R.PRODUCT_TYPE
AND R.DEAL_TYPE = P.DEAL_TYPE
AND R.STATUS_CODE != 'CANCELLED'
AND R.COMPANY_CODE LIKE NVL(UPPER(P.COMPANY_CODE)
, '%')
AND R.DEAL_SUBTYPE LIKE NVL(UPPER(P.DEAL_SUBTYPE)
, '%')
AND R.PRODUCT_TYPE LIKE NVL(UPPER(P.PRODUCT_TYPE)
, '%')
AND R.CURRENCY LIKE NVL(UPPER(P.CURRENCY)
, '%')
AND R.PORTFOLIO_CODE LIKE NVL(UPPER(P.PORTFOLIO_CODE)
, '%')
AND R.SECURITY_TYPE LIKE NVL(P.SECURITY_TYPE
, '%')
AND R.CPARTY_CODE LIKE NVL(UPPER(P.CPARTY_CODE)
, '%')
AND ((P.AS_PRINCIPAL = 'Y'
AND R.CLIENT_CODE IS NULL) OR (R.CLIENT_CODE LIKE NVL(UPPER(P.CLIENT_CODE)
, '%')
AND P.AS_PRINCIPAL = 'N')) AND (R.DEAL_TYPE = ST.DEAL_TYPE
AND R.DEAL_SUBTYPE = ST.DEAL_SUBTYPE)

Columns

Name
TNUM
DEAL_NUM
INT_AMT
SUB_ORD
INT_SET
BROKERAGE
TAX
COMMENTS
DAYS
CCY
TERMDESC
ORD_DATE
INT_RATE
PRODTYPE
PRIN_TOT
PRIN_PARTY
DEAL_SUB
USER_DEAL_SUBTYPE
PTY
CLIENT
CPARTY
COMPANY
CALC_TYPE
DEALER
REF
PERIOD_FROM
PERIOD_TO