VIEW: APPS.XTR_TRANS_VALIDATIONS_V

Source

View Text - Preformatted

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)

View Text - HTML Formatted

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)