VIEW: APPS.XTR_LINKED_TRANSACTIONS_V

Source

View Text - Preformatted

SELECT a.DEAL_LINKING_CODE, a.DEAL_NUMBER DEAL_NUM, a.TRANSACTION_NUMBER TRANS_NUM, a.CPARTY_CODE, a.CLIENT_CODE, a.COMPANY_CODE, a.DEAL_TYPE, dt.USER_DEAL_TYPE, a.DEAL_SUBTYPE, st.USER_DEAL_SUBTYPE, a.PRODUCT_TYPE, a.DEAL_DATE, a.CURRENCY CURRENCY_BUY, '   ' CURRENCY_SELL, a.UPDATED_BY, a.UPDATED_ON from XTR_ROLLOVER_TRANSACTIONS_V a, XTR_DEAL_TYPES dt, XTR_DEAL_SUBTYPES st WHERE a.DEAL_TYPE = 'ONC' and a.STATUS_CODE <> 'CANCELLED' and a.DEAL_LINKING_CODE is NOT NULL AND A.DEAL_TYPE = dt.DEAL_TYPE AND (A.DEAL_TYPE = st.DEAL_TYPE AND A.DEAL_SUBTYPE = st.DEAL_SUBTYPE) UNION ALL select b.DEAL_LINKING_CODE, b.DEAL_NO DEAL_NUM, b.TRANSACTION_NO TRANS_NUM, b.CPARTY_CODE, b.CLIENT_CODE, b.COMPANY_CODE, b.DEAL_TYPE, dt1.USER_DEAL_TYPE, b.DEAL_SUBTYPE, st1.USER_DEAL_SUBTYPE, b.PRODUCT_TYPE, b.DEAL_DATE, nvl(b.CURRENCY_BUY,b.CURRENCY) CURRENCY_BUY, b.CURRENCY_SELL, b.UPDATED_BY, b.UPDATED_ON from XTR_DEALS_V b, XTR_DEAL_TYPES dt1, XTR_DEAL_SUBTYPES st1 where b.DEAL_TYPE NOT IN('ONC','CMF') and b.STATUS_CODE <> 'CANCELLED' and b.DEAL_LINKING_CODE is NOT NULL AND b.DEAL_TYPE = dt1.DEAL_TYPE AND (b.DEAL_TYPE = st1.DEAL_TYPE AND b.DEAL_SUBTYPE = st1.DEAL_SUBTYPE) union all select b.DEAL_LINK_CODE, 0 DEAL_NUM , b.TRANSACTION_NUMBER TRANS_NUM, b.thirdparty_CODe , '   ' CLIENT_CODE, b.COMPANY_CODE, b.DEAL_TYPE, dt1.USER_DEAL_TYPE, b.DEAL_SUBTYPE, st1.USER_DEAL_SUBTYPE, '   ' PRODUCT_TYPE, b.value_date, b.CURRENCY CURRENCY_BUY, '   ' CURRENCY_SELL, b.UPDATED_BY, b.UPDATED_ON from XTR_EXPOSURE_TRANSACTIONS_V b, XTR_DEAL_TYPES dt1, XTR_DEAL_SUBTYPES st1 where b.STATUS_CODE <> 'CANCELLED' and b.DEAL_LINK_CODE is NOT NULL AND b.DEAL_TYPE = dt1.DEAL_TYPE AND (b.DEAL_TYPE = st1.DEAL_TYPE AND b.DEAL_SUBTYPE = st1.DEAL_SUBTYPE) UNION ALL select g.DEAL_LINKING_CODE, g.DEAL_NUMBER, g.TRANSACTION_NUMBER, g.party_code, to_char(NULL) CLIENT_CODE, g.COMPANY_CODE, g.DEAL_TYPE, dt1.USER_DEAL_TYPE, decode(sign(g.BALANCE_OUT), -1, 'FUND', 'INVEST') DEAL_SUBTYPE, st1.USER_DEAL_SUBTYPE, g.PRODUCT_TYPE, g.transfer_date, g.CURRENCY CURRENCY_BUY, to_char(NULL) CURRENCY_SELL, g.UPDATED_BY, g.UPDATED_ON from XTR_INTERGROUP_TRANSFERS g, XTR_DEAL_TYPES dt1, XTR_DEAL_SUBTYPES st1 where g.DEAL_LINKING_CODE is NOT NULL AND g.DEAL_TYPE = dt1.DEAL_TYPE AND (g.DEAL_TYPE = st1.DEAL_TYPE AND decode(sign(g.BALANCE_OUT), -1, 'FUND', 'INVEST') = st1.DEAL_SUBTYPE)

View Text - HTML Formatted

SELECT A.DEAL_LINKING_CODE
, A.DEAL_NUMBER DEAL_NUM
, A.TRANSACTION_NUMBER TRANS_NUM
, A.CPARTY_CODE
, A.CLIENT_CODE
, A.COMPANY_CODE
, A.DEAL_TYPE
, DT.USER_DEAL_TYPE
, A.DEAL_SUBTYPE
, ST.USER_DEAL_SUBTYPE
, A.PRODUCT_TYPE
, A.DEAL_DATE
, A.CURRENCY CURRENCY_BUY
, ' ' CURRENCY_SELL
, A.UPDATED_BY
, A.UPDATED_ON
FROM XTR_ROLLOVER_TRANSACTIONS_V A
, XTR_DEAL_TYPES DT
, XTR_DEAL_SUBTYPES ST
WHERE A.DEAL_TYPE = 'ONC'
AND A.STATUS_CODE <> 'CANCELLED'
AND A.DEAL_LINKING_CODE IS NOT NULL
AND A.DEAL_TYPE = DT.DEAL_TYPE
AND (A.DEAL_TYPE = ST.DEAL_TYPE
AND A.DEAL_SUBTYPE = ST.DEAL_SUBTYPE) UNION ALL SELECT B.DEAL_LINKING_CODE
, B.DEAL_NO DEAL_NUM
, B.TRANSACTION_NO TRANS_NUM
, B.CPARTY_CODE
, B.CLIENT_CODE
, B.COMPANY_CODE
, B.DEAL_TYPE
, DT1.USER_DEAL_TYPE
, B.DEAL_SUBTYPE
, ST1.USER_DEAL_SUBTYPE
, B.PRODUCT_TYPE
, B.DEAL_DATE
, NVL(B.CURRENCY_BUY
, B.CURRENCY) CURRENCY_BUY
, B.CURRENCY_SELL
, B.UPDATED_BY
, B.UPDATED_ON
FROM XTR_DEALS_V B
, XTR_DEAL_TYPES DT1
, XTR_DEAL_SUBTYPES ST1
WHERE B.DEAL_TYPE NOT IN('ONC'
, 'CMF')
AND B.STATUS_CODE <> 'CANCELLED'
AND B.DEAL_LINKING_CODE IS NOT NULL
AND B.DEAL_TYPE = DT1.DEAL_TYPE
AND (B.DEAL_TYPE = ST1.DEAL_TYPE
AND B.DEAL_SUBTYPE = ST1.DEAL_SUBTYPE) UNION ALL SELECT B.DEAL_LINK_CODE
, 0 DEAL_NUM
, B.TRANSACTION_NUMBER TRANS_NUM
, B.THIRDPARTY_CODE
, ' ' CLIENT_CODE
, B.COMPANY_CODE
, B.DEAL_TYPE
, DT1.USER_DEAL_TYPE
, B.DEAL_SUBTYPE
, ST1.USER_DEAL_SUBTYPE
, ' ' PRODUCT_TYPE
, B.VALUE_DATE
, B.CURRENCY CURRENCY_BUY
, ' ' CURRENCY_SELL
, B.UPDATED_BY
, B.UPDATED_ON
FROM XTR_EXPOSURE_TRANSACTIONS_V B
, XTR_DEAL_TYPES DT1
, XTR_DEAL_SUBTYPES ST1
WHERE B.STATUS_CODE <> 'CANCELLED'
AND B.DEAL_LINK_CODE IS NOT NULL
AND B.DEAL_TYPE = DT1.DEAL_TYPE
AND (B.DEAL_TYPE = ST1.DEAL_TYPE
AND B.DEAL_SUBTYPE = ST1.DEAL_SUBTYPE) UNION ALL SELECT G.DEAL_LINKING_CODE
, G.DEAL_NUMBER
, G.TRANSACTION_NUMBER
, G.PARTY_CODE
, TO_CHAR(NULL) CLIENT_CODE
, G.COMPANY_CODE
, G.DEAL_TYPE
, DT1.USER_DEAL_TYPE
, DECODE(SIGN(G.BALANCE_OUT)
, -1
, 'FUND'
, 'INVEST') DEAL_SUBTYPE
, ST1.USER_DEAL_SUBTYPE
, G.PRODUCT_TYPE
, G.TRANSFER_DATE
, G.CURRENCY CURRENCY_BUY
, TO_CHAR(NULL) CURRENCY_SELL
, G.UPDATED_BY
, G.UPDATED_ON
FROM XTR_INTERGROUP_TRANSFERS G
, XTR_DEAL_TYPES DT1
, XTR_DEAL_SUBTYPES ST1
WHERE G.DEAL_LINKING_CODE IS NOT NULL
AND G.DEAL_TYPE = DT1.DEAL_TYPE
AND (G.DEAL_TYPE = ST1.DEAL_TYPE
AND DECODE(SIGN(G.BALANCE_OUT)
, -1
, 'FUND'
, 'INVEST') = ST1.DEAL_SUBTYPE)