View: XTR_TAX_BROKERAGE_DETAILS_V

Product:	XTR - Treasury
Description:	Witholding tax and brokerage fee settlement amount due information
Implementation/DBA Data:	APPS.XTR_TAX_BROKERAGE_DETAILS_V

View Text

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

Columns