AR_DOC_TRANSFER_EMAIL_V
Details
-
Schema: FUSION
-
Object owner: AR
-
Object type: VIEW
Columns
Name |
---|
CUSTOMER_TRX_ID PARTY_SITE_ID TRX_DATE ORG_ID TRX_CLASS CUST_TRX_TYPE_SEQ_ID TRX_NUMBER CUST_ACCOUNT_ID |
Query
SQL_Statement |
---|
SELECT TRX.CUSTOMER_TRX_ID, CAS.PARTY_SITE_ID,TRX.TRX_DATE,TRX.ORG_ID,TRX.TRX_CLASS,TRX.CUST_TRX_TYPE_SEQ_ID,TRX.TRX_NUMBER,PROF.CUST_ACCOUNT_ID FROM RA_CUSTOMER_TRX_ALL TRX, HZ_CUSTOMER_PROFILES_F PROF, HZ_CUST_ACCT_SITES_ALL CAS, HZ_CUST_SITE_USES_ALL CSU WHERE TRX.BILL_TO_CUSTOMER_ID = PROF.CUST_ACCOUNT_ID AND TRX.BILL_TO_SITE_USE_ID = CSU.SITE_USE_ID AND CSU.CUST_ACCT_SITE_ID = CAS.CUST_ACCT_SITE_ID AND PROF.SITE_USE_ID IS NULL AND TRUNC(TRX.TRX_DATE) BETWEEN TRUNC(PROF.EFFECTIVE_START_DATE) AND NVL(TRUNC(PROF.EFFECTIVE_END_DATE),TRUNC(TRX.TRX_DATE)) AND PROF.TXN_DELIVERY_METHOD = 'XML_INV' AND TRX.TRX_CLASS IN ('CM','ONACC') AND TRX.COMPLETE_FLAG = 'Y' AND TRX.PRINTING_PENDING = 'N' AND TRX.DELIVERY_METHOD_CODE = 'EMAIL_INV' AND TRX.PRINTING_OPTION = 'PRI' AND TRX.LAST_PRINTED_SEQUENCE_NUM IS NOT NULL AND PROF.XML_CM_FLAG = 'Y' AND NOT EXISTS (SELECT 'X' FROM AR_DOCUMENT_TRANSFERS DT WHERE DT.SOURCE_TABLE = 'RA_CUSTOMER_TRX' AND DT.SOURCE_ID = TRX.CUSTOMER_TRX_ID ) UNION SELECT TRX.CUSTOMER_TRX_ID, CAS.PARTY_SITE_ID,TRX.TRX_DATE,TRX.ORG_ID,TRX.TRX_CLASS,TRX.CUST_TRX_TYPE_SEQ_ID,TRX.TRX_NUMBER,PROF.CUST_ACCOUNT_ID FROM RA_CUSTOMER_TRX_ALL TRX, HZ_CUSTOMER_PROFILES_F PROF, HZ_CUST_ACCT_SITES_ALL CAS, HZ_CUST_SITE_USES_ALL CSU WHERE TRX.BILL_TO_CUSTOMER_ID = PROF.CUST_ACCOUNT_ID AND TRX.BILL_TO_SITE_USE_ID = CSU.SITE_USE_ID AND CSU.CUST_ACCT_SITE_ID = CAS.CUST_ACCT_SITE_ID AND PROF.SITE_USE_ID IS NULL AND TRUNC(TRX.TRX_DATE) BETWEEN TRUNC(PROF.EFFECTIVE_START_DATE) AND NVL(TRUNC(PROF.EFFECTIVE_END_DATE),TRUNC(TRX.TRX_DATE)) AND PROF.TXN_DELIVERY_METHOD = 'XML_INV' AND TRX.TRX_CLASS ='INV' AND TRX.COMPLETE_FLAG = 'Y' AND TRX.PRINTING_PENDING = 'N' AND TRX.DELIVERY_METHOD_CODE = 'EMAIL_INV' AND TRX.PRINTING_OPTION = 'PRI' AND TRX.LAST_PRINTED_SEQUENCE_NUM IS NOT NULL AND PROF.XML_INV_FLAG = 'Y' AND NOT EXISTS (SELECT 'X' FROM AR_DOCUMENT_TRANSFERS DT WHERE DT.SOURCE_TABLE = 'RA_CUSTOMER_TRX' AND DT.SOURCE_ID = TRX.CUSTOMER_TRX_ID ) UNION SELECT TRX.CUSTOMER_TRX_ID, CAS.PARTY_SITE_ID,TRX.TRX_DATE,TRX.ORG_ID,TRX.TRX_CLASS,TRX.CUST_TRX_TYPE_SEQ_ID,TRX.TRX_NUMBER,PROF.CUST_ACCOUNT_ID FROM RA_CUSTOMER_TRX_ALL TRX, HZ_CUSTOMER_PROFILES_F PROF, HZ_CUST_ACCT_SITES_ALL CAS, HZ_CUST_SITE_USES_ALL CSU WHERE TRX.BILL_TO_CUSTOMER_ID = PROF.CUST_ACCOUNT_ID AND TRX.BILL_TO_SITE_USE_ID = CSU.SITE_USE_ID AND CSU.CUST_ACCT_SITE_ID = CAS.CUST_ACCT_SITE_ID AND PROF.SITE_USE_ID IS NULL AND TRUNC(TRX.TRX_DATE) BETWEEN TRUNC(PROF.EFFECTIVE_START_DATE) AND NVL(TRUNC(PROF.EFFECTIVE_END_DATE),TRUNC(TRX.TRX_DATE)) AND PROF.TXN_DELIVERY_METHOD = 'XML_INV' AND TRX.TRX_CLASS ='CB' AND TRX.COMPLETE_FLAG = 'Y' AND TRX.PRINTING_PENDING = 'N' AND TRX.DELIVERY_METHOD_CODE = 'EMAIL_INV' AND TRX.PRINTING_OPTION = 'PRI' AND TRX.LAST_PRINTED_SEQUENCE_NUM IS NOT NULL AND PROF.XML_CB_FLAG = 'Y' AND NOT EXISTS (SELECT 'X' FROM AR_DOCUMENT_TRANSFERS DT WHERE DT.SOURCE_TABLE = 'RA_CUSTOMER_TRX' AND DT.SOURCE_ID = TRX.CUSTOMER_TRX_ID ) UNION SELECT TRX.CUSTOMER_TRX_ID, CAS.PARTY_SITE_ID,TRX.TRX_DATE,TRX.ORG_ID,TRX.TRX_CLASS,TRX.CUST_TRX_TYPE_SEQ_ID,TRX.TRX_NUMBER,PROF.CUST_ACCOUNT_ID FROM RA_CUSTOMER_TRX_ALL TRX, HZ_CUSTOMER_PROFILES_F PROF, HZ_CUST_ACCT_SITES_ALL CAS, HZ_CUST_SITE_USES_ALL CSU WHERE TRX.BILL_TO_CUSTOMER_ID = PROF.CUST_ACCOUNT_ID AND TRX.BILL_TO_SITE_USE_ID = CSU.SITE_USE_ID AND CSU.CUST_ACCT_SITE_ID = CAS.CUST_ACCT_SITE_ID AND PROF.SITE_USE_ID IS NULL AND TRUNC(TRX.TRX_DATE) BETWEEN TRUNC(PROF.EFFECTIVE_START_DATE) AND NVL(TRUNC(PROF.EFFECTIVE_END_DATE),TRUNC(TRX.TRX_DATE)) AND PROF.TXN_DELIVERY_METHOD = 'XML_INV' AND TRX.TRX_CLASS ='DM' AND TRX.COMPLETE_FLAG = 'Y' AND TRX.PRINTING_PENDING = 'N' AND TRX.DELIVERY_METHOD_CODE = 'EMAIL_INV' AND TRX.PRINTING_OPTION = 'PRI' AND TRX.LAST_PRINTED_SEQUENCE_NUM IS NOT NULL AND PROF.XML_DM_FLAG = 'Y' AND NOT EXISTS (SELECT 'X' FROM AR_DOCUMENT_TRANSFERS DT WHERE DT.SOURCE_TABLE = 'RA_CUSTOMER_TRX' AND DT.SOURCE_ID = TRX.CUSTOMER_TRX_ID ) |