1. Tables and Views for Financials
  2. AR_DOC_TRANSFER_EMAIL_V

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

)