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_TAX_BROKERAGE_DETAILS_V

Source

View Text - Preformatted


SELECT a.DEAL_NUMBER DEAL_NUM, a.TRANSACTION_NUMBER TRANS_NUM, a.CPARTY_CODE, a.CLIENT_CODE, a.COMPANY_CODE, decode(a.tax_code, null, null, ta.PAYEE) TAX_PAYEE, decode(a.BROKERAGE_CODE, null, null, br.PAYEE) BROKERAGE_PAYEE, a.DEAL_TYPE, dt.USER_DEAL_TYPE, a.DEAL_SUBTYPE, st.USER_DEAL_SUBTYPE, a.PRODUCT_TYPE, a.BROKERAGE_AMOUNT, a.TAX_AMOUNT, a.DEAL_DATE, a.CURRENCY, a.BROKERAGE_CODE, a.TAX_CODE, a.TAX_SETTLED_REFERENCE, a.BKGE_SETTLED_REFERENCE from XTR_ROLLOVER_TRANSACTIONS_V a, XTR_DEAL_TYPES dt, XTR_DEAL_SUBTYPES st, XTR_TAX_BROKERAGE_SETUP ta, XTR_TAX_BROKERAGE_SETUP br WHERE a.DEAL_TYPE = 'ONC' and a.STATUS_CODE <>'CANCELLED' and (a.TAX_AMOUNT > 0 or a.BROKERAGE_AMOUNT > 0) AND a.DEAL_TYPE = dt.DEAL_TYPE AND (a.DEAL_TYPE = st.DEAL_TYPE AND a.DEAL_SUBTYPE = st.DEAL_SUBTYPE) and ta.REFERENCE_CODE (+) = a.TAX_CODE and ta.DEDUCTION_TYPE (+) = 'T' and br.REFERENCE_CODE (+) = a.BROKERAGE_CODE and br.DEDUCTION_TYPE (+) = 'B' UNION ALL select a.DEAL_NO DEAL_NUM, a.TRANSACTION_NO TRANS_NUM, a.CPARTY_CODE, a.CLIENT_CODE, a.COMPANY_CODE, decode(a.tax_code, null, null, ta.PAYEE) TAX_PAYEE, decode(a.BROKERAGE_CODE, null, null, br.PAYEE) BROKERAGE_PAYEE, a.DEAL_TYPE, dt.USER_DEAL_TYPE, a.DEAL_SUBTYPE, st.USER_DEAL_SUBTYPE, a.PRODUCT_TYPE, a.BROKERAGE_AMOUNT, a.TAX_AMOUNT, a.DEAL_DATE, nvl(a.BROKERAGE_CURRENCY,a.CURRENCY), a.BROKERAGE_CODE, a.TAX_CODE, a.TAX_SETTLED_REFERENCE, a.BKGE_SETTLED_REFERENCE from XTR_DEALS_V a, XTR_DEAL_TYPES dt, XTR_DEAL_SUBTYPES st, XTR_TAX_BROKERAGE_SETUP ta, XTR_TAX_BROKERAGE_SETUP br where a.DEAL_TYPE <> 'ONC' and a.STATUS_CODE <>'CANCELLED' and (a.TAX_AMOUNT > 0 or a.BROKERAGE_AMOUNT > 0) AND a.DEAL_TYPE = dt.DEAL_TYPE AND (a.DEAL_TYPE = st.DEAL_TYPE AND a.DEAL_SUBTYPE = st.DEAL_SUBTYPE) and ta.REFERENCE_CODE (+) = a.TAX_CODE and ta.DEDUCTION_TYPE (+) = 'T' and br.REFERENCE_CODE (+) = a.BROKERAGE_CODE and br.DEDUCTION_TYPE (+) = 'B' UNION ALL SELECT a.DEAL_NUMBER DEAL_NUM, a.TRANSACTION_NUMBER TRANS_NUM, a.CPARTY_CODE, a.CLIENT_CODE, a.COMPANY_CODE, ta.PAYEE TAX_PAYEE, null BROKERAGE_PAYEE, a.DEAL_TYPE, dt.USER_DEAL_TYPE, a.DEAL_SUBTYPE, st.USER_DEAL_SUBTYPE, a.PRODUCT_TYPE, to_number(null), a.TAX_AMOUNT, a.DEAL_DATE, a.CURRENCY, null BROKERAGE_CODE, a.TAX_CODE, a.TAX_SETTLED_REFERENCE, to_number(null) BKGE_SETTLED_REFERENCE from XTR_ROLLOVER_TRANSACTIONS_V a, XTR_DEAL_TYPES dt, XTR_DEAL_SUBTYPES st, XTR_TAX_BROKERAGE_SETUP ta where a.DEAL_TYPE <> 'ONC' and a.DEAL_TYPE <> 'NI' and a.STATUS_CODE <>'CANCELLED' and a.TAX_AMOUNT > 0 AND a.DEAL_TYPE = dt.DEAL_TYPE AND (a.DEAL_TYPE = st.DEAL_TYPE AND a.DEAL_SUBTYPE = st.DEAL_SUBTYPE) and ta.DEDUCTION_TYPE = 'T' and ta.REFERENCE_CODE = a.TAX_CODE and a.TAX_CODE is not null UNION ALL SELECT a.DEAL_NUMBER DEAL_NUM, a.TRANSACTION_NUMBER TRANS_NUM, a.CPARTY_CODE, a.CLIENT_CODE, a.COMPANY_CODE, ta.PAYEE TAX_PAYEE, null BROKERAGE_PAYEE, a.DEAL_TYPE, dt.USER_DEAL_TYPE, a.DEAL_SUBTYPE, st.USER_DEAL_SUBTYPE, a.PRODUCT_TYPE, to_number(null), a.PRINCIPAL_TAX_AMOUNT TAX_AMOUNT, a.DEAL_DATE, a.CURRENCY, null BROKERAGE_CODE, a.PRINCIPAL_TAX_CODE TAX_CODE, a.PRINCIPAL_TAX_SETTLED_REF TAX_SETTLED_REFERENCE, to_number(null) BKGE_SETTLED_REFERENCE from XTR_ROLLOVER_TRANSACTIONS_V a, XTR_DEAL_TYPES dt, XTR_DEAL_SUBTYPES st, XTR_TAX_BROKERAGE_SETUP ta where a.STATUS_CODE <>'CANCELLED' and a.DEAL_TYPE<>'NI' and a.PRINCIPAL_TAX_AMOUNT > 0 AND a.DEAL_TYPE = dt.DEAL_TYPE AND (a.DEAL_TYPE = st.DEAL_TYPE AND a.DEAL_SUBTYPE = st.DEAL_SUBTYPE) and ta.DEDUCTION_TYPE = 'T' and ta.REFERENCE_CODE = a.PRINCIPAL_TAX_CODE and a.PRINCIPAL_TAX_CODE is not null UNION ALL select a.DEAL_NO DEAL_NUM, a.TRANSACTION_NO TRANS_NUM, a.CPARTY_CODE, a.CLIENT_CODE, a.COMPANY_CODE, ta.PAYEE TAX_PAYEE, null BROKERAGE_PAYEE, a.DEAL_TYPE, dt.USER_DEAL_TYPE, a.DEAL_SUBTYPE, st.USER_DEAL_SUBTYPE, a.PRODUCT_TYPE, to_number(null), a.INCOME_TAX_AMOUNT, a.DEAL_DATE, a.CURRENCY, null BROKERAGE_CODE, a.INCOME_TAX_CODE, a.INCOME_TAX_SETTLED_REF, to_number(null) BKGE_SETTLED_REFERENCE from XTR_DEALS_V a, XTR_DEAL_TYPES dt, XTR_DEAL_SUBTYPES st, XTR_TAX_BROKERAGE_SETUP ta where a.DEAL_TYPE <> 'ONC' and a.STATUS_CODE <>'CANCELLED' and a.INCOME_TAX_AMOUNT > 0 AND a.DEAL_TYPE = dt.DEAL_TYPE AND (a.DEAL_TYPE = st.DEAL_TYPE AND a.DEAL_SUBTYPE = st.DEAL_SUBTYPE) and ta.DEDUCTION_TYPE = 'T' and ta.REFERENCE_CODE = a.INCOME_TAX_CODE and a.INCOME_TAX_CODE is not null

View Text - HTML Formatted

SELECT A.DEAL_NUMBER DEAL_NUM
, A.TRANSACTION_NUMBER TRANS_NUM
, A.CPARTY_CODE
, A.CLIENT_CODE
, A.COMPANY_CODE
, DECODE(A.TAX_CODE
, NULL
, NULL
, TA.PAYEE) TAX_PAYEE
, DECODE(A.BROKERAGE_CODE
, NULL
, NULL
, BR.PAYEE) BROKERAGE_PAYEE
, A.DEAL_TYPE
, DT.USER_DEAL_TYPE
, A.DEAL_SUBTYPE
, ST.USER_DEAL_SUBTYPE
, A.PRODUCT_TYPE
, A.BROKERAGE_AMOUNT
, A.TAX_AMOUNT
, A.DEAL_DATE
, A.CURRENCY
, A.BROKERAGE_CODE
, A.TAX_CODE
, A.TAX_SETTLED_REFERENCE
, A.BKGE_SETTLED_REFERENCE
FROM XTR_ROLLOVER_TRANSACTIONS_V A
, XTR_DEAL_TYPES DT
, XTR_DEAL_SUBTYPES ST
, XTR_TAX_BROKERAGE_SETUP TA
, XTR_TAX_BROKERAGE_SETUP BR
WHERE A.DEAL_TYPE = 'ONC'
AND A.STATUS_CODE <>'CANCELLED'
AND (A.TAX_AMOUNT > 0 OR A.BROKERAGE_AMOUNT > 0)
AND A.DEAL_TYPE = DT.DEAL_TYPE
AND (A.DEAL_TYPE = ST.DEAL_TYPE
AND A.DEAL_SUBTYPE = ST.DEAL_SUBTYPE)
AND TA.REFERENCE_CODE (+) = A.TAX_CODE
AND TA.DEDUCTION_TYPE (+) = 'T'
AND BR.REFERENCE_CODE (+) = A.BROKERAGE_CODE
AND BR.DEDUCTION_TYPE (+) = 'B' UNION ALL SELECT A.DEAL_NO DEAL_NUM
, A.TRANSACTION_NO TRANS_NUM
, A.CPARTY_CODE
, A.CLIENT_CODE
, A.COMPANY_CODE
, DECODE(A.TAX_CODE
, NULL
, NULL
, TA.PAYEE) TAX_PAYEE
, DECODE(A.BROKERAGE_CODE
, NULL
, NULL
, BR.PAYEE) BROKERAGE_PAYEE
, A.DEAL_TYPE
, DT.USER_DEAL_TYPE
, A.DEAL_SUBTYPE
, ST.USER_DEAL_SUBTYPE
, A.PRODUCT_TYPE
, A.BROKERAGE_AMOUNT
, A.TAX_AMOUNT
, A.DEAL_DATE
, NVL(A.BROKERAGE_CURRENCY
, A.CURRENCY)
, A.BROKERAGE_CODE
, A.TAX_CODE
, A.TAX_SETTLED_REFERENCE
, A.BKGE_SETTLED_REFERENCE
FROM XTR_DEALS_V A
, XTR_DEAL_TYPES DT
, XTR_DEAL_SUBTYPES ST
, XTR_TAX_BROKERAGE_SETUP TA
, XTR_TAX_BROKERAGE_SETUP BR
WHERE A.DEAL_TYPE <> 'ONC'
AND A.STATUS_CODE <>'CANCELLED'
AND (A.TAX_AMOUNT > 0 OR A.BROKERAGE_AMOUNT > 0)
AND A.DEAL_TYPE = DT.DEAL_TYPE
AND (A.DEAL_TYPE = ST.DEAL_TYPE
AND A.DEAL_SUBTYPE = ST.DEAL_SUBTYPE)
AND TA.REFERENCE_CODE (+) = A.TAX_CODE
AND TA.DEDUCTION_TYPE (+) = 'T'
AND BR.REFERENCE_CODE (+) = A.BROKERAGE_CODE
AND BR.DEDUCTION_TYPE (+) = 'B' UNION ALL SELECT A.DEAL_NUMBER DEAL_NUM
, A.TRANSACTION_NUMBER TRANS_NUM
, A.CPARTY_CODE
, A.CLIENT_CODE
, A.COMPANY_CODE
, TA.PAYEE TAX_PAYEE
, NULL BROKERAGE_PAYEE
, A.DEAL_TYPE
, DT.USER_DEAL_TYPE
, A.DEAL_SUBTYPE
, ST.USER_DEAL_SUBTYPE
, A.PRODUCT_TYPE
, TO_NUMBER(NULL)
, A.TAX_AMOUNT
, A.DEAL_DATE
, A.CURRENCY
, NULL BROKERAGE_CODE
, A.TAX_CODE
, A.TAX_SETTLED_REFERENCE
, TO_NUMBER(NULL) BKGE_SETTLED_REFERENCE
FROM XTR_ROLLOVER_TRANSACTIONS_V A
, XTR_DEAL_TYPES DT
, XTR_DEAL_SUBTYPES ST
, XTR_TAX_BROKERAGE_SETUP TA
WHERE A.DEAL_TYPE <> 'ONC'
AND A.DEAL_TYPE <> 'NI'
AND A.STATUS_CODE <>'CANCELLED'
AND A.TAX_AMOUNT > 0
AND A.DEAL_TYPE = DT.DEAL_TYPE
AND (A.DEAL_TYPE = ST.DEAL_TYPE
AND A.DEAL_SUBTYPE = ST.DEAL_SUBTYPE)
AND TA.DEDUCTION_TYPE = 'T'
AND TA.REFERENCE_CODE = A.TAX_CODE
AND A.TAX_CODE IS NOT NULL UNION ALL SELECT A.DEAL_NUMBER DEAL_NUM
, A.TRANSACTION_NUMBER TRANS_NUM
, A.CPARTY_CODE
, A.CLIENT_CODE
, A.COMPANY_CODE
, TA.PAYEE TAX_PAYEE
, NULL BROKERAGE_PAYEE
, A.DEAL_TYPE
, DT.USER_DEAL_TYPE
, A.DEAL_SUBTYPE
, ST.USER_DEAL_SUBTYPE
, A.PRODUCT_TYPE
, TO_NUMBER(NULL)
, A.PRINCIPAL_TAX_AMOUNT TAX_AMOUNT
, A.DEAL_DATE
, A.CURRENCY
, NULL BROKERAGE_CODE
, A.PRINCIPAL_TAX_CODE TAX_CODE
, A.PRINCIPAL_TAX_SETTLED_REF TAX_SETTLED_REFERENCE
, TO_NUMBER(NULL) BKGE_SETTLED_REFERENCE
FROM XTR_ROLLOVER_TRANSACTIONS_V A
, XTR_DEAL_TYPES DT
, XTR_DEAL_SUBTYPES ST
, XTR_TAX_BROKERAGE_SETUP TA
WHERE A.STATUS_CODE <>'CANCELLED'
AND A.DEAL_TYPE<>'NI'
AND A.PRINCIPAL_TAX_AMOUNT > 0
AND A.DEAL_TYPE = DT.DEAL_TYPE
AND (A.DEAL_TYPE = ST.DEAL_TYPE
AND A.DEAL_SUBTYPE = ST.DEAL_SUBTYPE)
AND TA.DEDUCTION_TYPE = 'T'
AND TA.REFERENCE_CODE = A.PRINCIPAL_TAX_CODE
AND A.PRINCIPAL_TAX_CODE IS NOT NULL UNION ALL SELECT A.DEAL_NO DEAL_NUM
, A.TRANSACTION_NO TRANS_NUM
, A.CPARTY_CODE
, A.CLIENT_CODE
, A.COMPANY_CODE
, TA.PAYEE TAX_PAYEE
, NULL BROKERAGE_PAYEE
, A.DEAL_TYPE
, DT.USER_DEAL_TYPE
, A.DEAL_SUBTYPE
, ST.USER_DEAL_SUBTYPE
, A.PRODUCT_TYPE
, TO_NUMBER(NULL)
, A.INCOME_TAX_AMOUNT
, A.DEAL_DATE
, A.CURRENCY
, NULL BROKERAGE_CODE
, A.INCOME_TAX_CODE
, A.INCOME_TAX_SETTLED_REF
, TO_NUMBER(NULL) BKGE_SETTLED_REFERENCE
FROM XTR_DEALS_V A
, XTR_DEAL_TYPES DT
, XTR_DEAL_SUBTYPES ST
, XTR_TAX_BROKERAGE_SETUP TA
WHERE A.DEAL_TYPE <> 'ONC'
AND A.STATUS_CODE <>'CANCELLED'
AND A.INCOME_TAX_AMOUNT > 0
AND A.DEAL_TYPE = DT.DEAL_TYPE
AND (A.DEAL_TYPE = ST.DEAL_TYPE
AND A.DEAL_SUBTYPE = ST.DEAL_SUBTYPE)
AND TA.DEDUCTION_TYPE = 'T'
AND TA.REFERENCE_CODE = A.INCOME_TAX_CODE
AND A.INCOME_TAX_CODE IS NOT NULL