View: AR_DOCUMENT_TRANSFERS_V

Product:	AR - Receivables
Description:
Implementation/DBA Data:	APPS.AR_DOCUMENT_TRANSFERS_V

View Text

SELECT TT.TYPE TRX_CLASS
, TT.CUST_TRX_TYPE_ID TRX_TYPE_ID
, CA.CUSTOMER_CLASS_CODE CUST_CLASS
, CA.CUST_ACCOUNT_ID CUST_ACCOUNT_ID
, T.TRX_DATE TRX_DATE
, T.TRX_NUMBER TRX_NUMBER
, T.CUSTOMER_TRX_ID SOURCE_ID
, 'CUSTOMER_TRX_ID' SOURCE_ID_COLUMN
, 'RA_CUSTOMER_TRX' SOURCE_TABLE
, 'HZ_PARTY_SITES' TP_SOURCE_TABLE
, CAS.PARTY_SITE_ID TP_SOURCE_ID
, ETD.TRANSACTION_TYPE ECX_TRX_TYPE
, ETD.TRANSACTION_SUBTYPE ECX_TRX_SUBTYPE
, ETH.PARTY_TYPE ECX_PARTY_TYPE
, 'ORACLE.APPS.AR.TRANSMIT.XMLINVCMDMCBDEP' EVENT_NAME
FROM RA_CUSTOMER_TRX T
, RA_CUST_TRX_TYPES TT
, HZ_CUST_SITE_USES CSU
, HZ_CUST_ACCT_SITES CAS
, HZ_CUST_ACCOUNTS CA
, ECX_TP_HEADERS ETH
, ECX_TP_DETAILS_V ETD
WHERE T.BILL_TO_CUSTOMER_ID = CA.CUST_ACCOUNT_ID AND T.BILL_TO_SITE_USE_ID = CSU.SITE_USE_ID AND CSU.CUST_ACCT_SITE_ID = CAS.CUST_ACCT_SITE_ID AND T.COMPLETE_FLAG = 'Y' AND T.CUST_TRX_TYPE_ID = TT.CUST_TRX_TYPE_ID AND TT.TYPE IN ('INV'
, 'DM'
, 'CM'
, 'CB'
, 'DEP') AND ETH.PARTY_SITE_ID = CAS.PARTY_SITE_ID AND ETH.PARTY_TYPE = 'C' AND ETH.TP_HEADER_ID = ETD.TP_HEADER_ID AND ETD.TRANSACTION_TYPE = 'AR' AND ETD.TRANSACTION_SUBTYPE = DECODE(TT.TYPE
, 'INV'
, 'PROCESS_INVOICE'
, 'DM'
, 'PROCESS_DEBIT_MEMO'
, 'CM'
, 'PROCESS_CREDIT_MEMO'
, 'CB'
, 'PROCESS_CHARGE_BACK'
, 'DEP'
, 'PROCESS_DEPOSIT'
, NULL)
AND NOT EXISTS ( SELECT 'X' FROM AR_DOCUMENT_TRANSFERS DT WHERE DT.SOURCE_TABLE = 'RA_CUSTOMER_TRX'
AND DT.SOURCE_ID = T.CUSTOMER_TRX_ID) UNION SELECT TT.TYPE TRX_CLASS
, TT.CUST_TRX_TYPE_ID TRX_TYPE_ID
, CA.CUSTOMER_CLASS_CODE CUST_CLASS
, CA.CUST_ACCOUNT_ID CUST_ACCOUNT_ID
, T.TRX_DATE TRX_DATE
, T.TRX_NUMBER TRX_NUMBER
, T.CUSTOMER_TRX_ID SOURCE_ID
, 'CUSTOMER_TRX_ID' SOURCE_ID_COLUMN
, 'RA_CUSTOMER_TRX' SOURCE_TABLE
, 'HZ_PARTY_SITES' TP_SOURCE_TABLE
, CAS.PARTY_SITE_ID TP_SOURCE_ID
, ETD.TRANSACTION_TYPE ECX_TRX_TYPE
, ETD.TRANSACTION_SUBTYPE ECX_TRX_SUBTYPE
, ETH.PARTY_TYPE ECX_PARTY_TYPE
, 'ORACLE.APPS.CLN.AR.3C3.TRANSMIT.INVOICE' EVENT_NAME
FROM RA_CUSTOMER_TRX T
, RA_CUST_TRX_TYPES TT
, HZ_CUST_SITE_USES CSU
, HZ_CUST_ACCT_SITES CAS
, HZ_CUST_ACCOUNTS CA
, ECX_TP_HEADERS ETH
, ECX_TP_DETAILS_V ETD
WHERE T.BILL_TO_CUSTOMER_ID = CA.CUST_ACCOUNT_ID
AND T.BILL_TO_SITE_USE_ID = CSU.SITE_USE_ID
AND CSU.CUST_ACCT_SITE_ID = CAS.CUST_ACCT_SITE_ID
AND T.COMPLETE_FLAG = 'Y'
AND T.CUST_TRX_TYPE_ID = TT.CUST_TRX_TYPE_ID
AND TT.TYPE IN ('INV'
, 'DM'
, 'CM'
, 'CB'
, 'DEP')
AND ETH.PARTY_SITE_ID = CAS.PARTY_SITE_ID
AND ETH.PARTY_TYPE = 'C'
AND ETH.TP_HEADER_ID = ETD.TP_HEADER_ID
AND ETD.TRANSACTION_TYPE = 'CLN'
AND ETD.TRANSACTION_SUBTYPE = 'NTFYINVCO'
AND NOT EXISTS ( SELECT 'X'
FROM AR_DOCUMENT_TRANSFERS DT
WHERE DT.SOURCE_TABLE = 'RA_CUSTOMER_TRX'
AND DT.SOURCE_ID = T.CUSTOMER_TRX_ID)

Columns