VIEW: APPS.AR_DOCUMENT_TRANSFERS_V

Source

View Text - Preformatted

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)

View Text - HTML Formatted

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)