Product: | XTR - Treasury |
---|---|
Description: | This view is used in the Transaction Validation form. |
Implementation/DBA Data: | APPS.XTR_TRANS_VALIDATIONS_V |
SELECT CONFO_VALIDATION_BOTH
, DEAL_NO
, ACTION_TYPE
, CPARTY_CODE
, DATE_ACTION_INITIATED
, COMPANY_CODE
, STATUS_CODE
, TRANSACTION_NO
, NVL(CONFO_PARTY_CODE
, CPARTY_CODE)
, CLIENT_CODE
, FILE_NAME
, INITIALLY_PRINTED_ON
, REPRINT_REQUESTED_BY
, REPRINTED_ON
, DEAL_TYPE
, DEAL_SUBTYPE
, CURRENCY
, AMOUNT
, AMOUNT_TYPE
, CONFIRMATION_VALIDATED_BY
, CONFIRMATION_VALIDATED_ON
, SETTLE_DATE
, PARTY_CONFO_CHECKED
, PARTY_CONFO_FILE_NAME
, PARTY_CONFO_CHECKED_ON
, PARTY_CONFO_CHECKED_BY
, DEAL_UPDATED_YN
FROM XTR_CONFIRMATION_DETAILS XD
WHERE STATUS_CODE <> 'CANCELLED'
AND NOT EXISTS ( SELECT CONTRACT_NO
, ROLLOVER_TRANSACTION_NO
FROM XTR_REVIEW_ALL_CONTRACTS_V
WHERE DEAL_NO = CONTRACT_NO
AND (TRANSACTION_NO = ROLLOVER_TRANSACTION_NO OR TRANSACTION_NO IS NULL OR ROLLOVER_TRANSACTION_NO IS NULL)
AND CONTRACT_STATUS = 'CANCELLED' )
AND ( EXISTS ( SELECT DEAL_NUMBER
FROM XTR_ROLLOVER_TRANSACTIONS_V
WHERE DEAL_NO = DEAL_NUMBER ) OR DEAL_TYPE <> 'IRS' )
AND EXISTS ( SELECT XCA.PARTY_CODE
FROM XTR_COMPANY_AUTHORITIES XCA
, XTR_DEALER_CODES XDC
WHERE XD.COMPANY_CODE = XCA.PARTY_CODE
AND XCA.DEALER_CODE = XDC.DEALER_CODE
AND XCA.COMPANY_AUTHORISED_FOR_INPUT = 'Y'
AND XDC.USER_ID = FND_GLOBAL.USER_ID)
AND EXISTS ( SELECT 1
FROM XTR_AUTH_TYPE_SUBTYPE_PROD TSP
, XTR_DEALS D
, XTR_DEALER_CODES XDC
WHERE XD.DEAL_TYPE<>'ONC'
AND XD.DEAL_TYPE=TSP.DEAL_TYPE
AND XD.DEAL_SUBTYPE=TSP.DEAL_SUBTYPE
AND XD.DEAL_NO=D.DEAL_NO
AND D.PRODUCT_TYPE=TSP.PRODUCT_TYPE
AND TSP.VALIDATION_AUTHORIZED='Y'
AND XDC.USER_ID = FND_GLOBAL.USER_ID
AND TSP.USER_NAME = XDC.DEALER_CODE UNION ALL SELECT 1
FROM XTR_AUTH_TYPE_SUBTYPE_PROD TSP
, XTR_ROLLOVER_TRANSACTIONS D
, XTR_DEALER_CODES XDC
WHERE (XD.DEAL_TYPE='ONC')
AND XD.DEAL_TYPE=TSP.DEAL_TYPE
AND XD.DEAL_SUBTYPE=TSP.DEAL_SUBTYPE
AND XD.DEAL_NO=D.DEAL_NUMBER
AND XD.TRANSACTION_NO=D.TRANSACTION_NUMBER
AND D.PRODUCT_TYPE=TSP.PRODUCT_TYPE
AND TSP.VALIDATION_AUTHORIZED='Y'
AND XDC.USER_ID = FND_GLOBAL.USER_ID
AND TSP.USER_NAME = XDC.DEALER_CODE UNION ALL SELECT 1
FROM XTR_AUTH_TYPE_SUBTYPE_PROD TSP
, XTR_EXPOSURE_TRANSACTIONS D
, XTR_DEALER_CODES XDC
WHERE XD.DEAL_TYPE = 'EXP'
AND XD.DEAL_TYPE=TSP.DEAL_TYPE
AND XD.DEAL_SUBTYPE=TSP.DEAL_SUBTYPE
AND XD.TRANSACTION_NO=D.TRANSACTION_NUMBER
AND D.EXPOSURE_TYPE=TSP.PRODUCT_TYPE
AND TSP.VALIDATION_AUTHORIZED='Y'
AND XDC.USER_ID = FND_GLOBAL.USER_ID
AND TSP.USER_NAME = XDC.DEALER_CODE UNION ALL SELECT 1
FROM XTR_AUTH_TYPE_SUBTYPE_PROD TSP
, XTR_INTERACCT_TRANSFERS IAC
, XTR_DEALER_CODES XDC
WHERE XD.DEAL_TYPE = 'IAC'
AND XD.DEAL_TYPE=TSP.DEAL_TYPE
AND XD.DEAL_SUBTYPE=TSP.DEAL_SUBTYPE
AND XD.DEAL_NO=0
AND XD.TRANSACTION_NO=IAC.TRANSACTION_NUMBER
AND IAC.PRODUCT_TYPE=TSP.PRODUCT_TYPE
AND TSP.VALIDATION_AUTHORIZED='Y'
AND XDC.USER_ID = FND_GLOBAL.USER_ID
AND TSP.USER_NAME = XDC.DEALER_CODE)