1. Tables and Views for Financials
  2. AR_DOCUMENT_TRANSFERS_V

AR_DOCUMENT_TRANSFERS_V

Details

  • Schema: FUSION

  • Object owner: AR

  • Object type: VIEW

Columns

Name

TRX_CLASS

TRX_TYPE_SEQ_ID

CUST_CLASS

CUST_ACCOUNT_ID

TRX_DATE

TRX_NUMBER

SOURCE_ID

SOURCE_ID_COLUMN

SOURCE_TABLE

TP_SOURCE_TABLE

TP_SOURCE_ID

PRINTING_PENDING

ORG_ID

B2B_TP_CODE

Query

SQL_Statement

SELECT TT.TYPE TRX_CLASS,

TT.CUST_TRX_TYPE_SEQ_ID TRX_TYPE_SEQ_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,

T.PRINTING_PENDING PRINTING_PENDING,

T.ORG_ID ORG_ID,

PROF.B2B_TP_CODE

FROM RA_CUSTOMER_TRX T,

RA_CUST_TRX_TYPES_ALL TT,

HZ_CUST_ACCT_SITES_ALL CAS,

HZ_CUST_SITE_USES_ALL CSU,

HZ_CUST_ACCOUNTS CA,

HZ_CUSTOMER_PROFILES_F PROF,

(SELECT NVL(MAX(ENABLED_FLAG),'N') XML_FEATURE_ENABLED FROM FND_LOOKUP_VALUES_B WHERE LOOKUP_TYPE = 'AR_FEATURES' AND LOOKUP_CODE = 'AR_XML_INVOICE_ENHANCED') FND,

HZ_CUSTOMER_PROFILES_F SITE_PROF,

XLE_ENTITY_PROFILES XEP

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_SEQ_ID = TT.CUST_TRX_TYPE_SEQ_ID

AND TT.TYPE = 'INV'

AND PROF.CUST_ACCOUNT_ID = CA.CUST_ACCOUNT_ID

AND PROF.SITE_USE_ID IS NULL

AND TRUNC(T.TRX_DATE) BETWEEN TRUNC(PROF.EFFECTIVE_START_DATE) AND NVL(TRUNC(PROF.EFFECTIVE_END_DATE),TRUNC(T.TRX_DATE))

AND PROF.TXN_DELIVERY_METHOD = 'XML_INV'

AND T.PRINTING_PENDING = 'Y'

AND T.PRINTING_OPTION = 'PRI'

AND PROF.XML_INV_FLAG = 'Y'

AND T.BILL_TO_CUSTOMER_ID = SITE_PROF.CUST_ACCOUNT_ID(+)

AND T.BILL_TO_SITE_USE_ID = SITE_PROF.SITE_USE_ID(+)

AND TRUNC(T.TRX_DATE) BETWEEN TRUNC(SITE_PROF.EFFECTIVE_START_DATE(+)) AND NVL(TRUNC(SITE_PROF.EFFECTIVE_END_DATE(+)),TRUNC(T.TRX_DATE))

AND ((NVL(SITE_PROF.TXN_DELIVERY_METHOD,PROF.TXN_DELIVERY_METHOD) = 'XML_INV'

AND FND.XML_FEATURE_ENABLED = 'Y')

OR (FND.XML_FEATURE_ENABLED = 'N' AND NVL(SITE_PROF.TXN_DELIVERY_METHOD,PROF.TXN_DELIVERY_METHOD) IN ('XML_INV','PRINT_INV','EMAIL_INV')))

AND XEP.LEGAL_ENTITY_ID = T.LEGAL_ENTITY_ID

AND NVL(XEP.LE_INFORMATION16,'XX') = 'XX'

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_SEQ_ID TRX_TYPE_SEQ_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,

T.PRINTING_PENDING PRINTING_PENDING,

T.ORG_ID ORG_ID,

PROF.B2B_TP_CODE

FROM RA_CUSTOMER_TRX T,

RA_CUST_TRX_TYPES_ALL TT,

HZ_CUST_ACCT_SITES_ALL CAS,

HZ_CUST_SITE_USES_ALL CSU,

HZ_CUST_ACCOUNTS CA,

HZ_CUSTOMER_PROFILES_F PROF,

(SELECT NVL(MAX(ENABLED_FLAG),'N') XML_FEATURE_ENABLED FROM FND_LOOKUP_VALUES_B WHERE LOOKUP_TYPE = 'AR_FEATURES' AND LOOKUP_CODE = 'AR_XML_INVOICE_ENHANCED') FND,

HZ_CUSTOMER_PROFILES_F SITE_PROF,

XLE_ENTITY_PROFILES XEP

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_SEQ_ID = TT.CUST_TRX_TYPE_SEQ_ID

AND TT.TYPE = 'CM'

AND PROF.CUST_ACCOUNT_ID = CA.CUST_ACCOUNT_ID

AND PROF.SITE_USE_ID IS NULL

AND TRUNC(T.TRX_DATE) BETWEEN TRUNC(PROF.EFFECTIVE_START_DATE) AND NVL(TRUNC(PROF.EFFECTIVE_END_DATE),TRUNC(T.TRX_DATE))

AND PROF.TXN_DELIVERY_METHOD = 'XML_INV'

AND T.PRINTING_PENDING = 'Y'

AND T.PRINTING_OPTION = 'PRI'

AND PROF.XML_CM_FLAG = 'Y'

AND T.BILL_TO_CUSTOMER_ID = SITE_PROF.CUST_ACCOUNT_ID(+)

AND T.BILL_TO_SITE_USE_ID = SITE_PROF.SITE_USE_ID(+)

AND TRUNC(T.TRX_DATE) BETWEEN TRUNC(SITE_PROF.EFFECTIVE_START_DATE(+)) AND NVL(TRUNC(SITE_PROF.EFFECTIVE_END_DATE(+)),TRUNC(T.TRX_DATE))

AND ((NVL(SITE_PROF.TXN_DELIVERY_METHOD,PROF.TXN_DELIVERY_METHOD) = 'XML_INV'

AND FND.XML_FEATURE_ENABLED = 'Y')

OR (FND.XML_FEATURE_ENABLED = 'N' AND NVL(SITE_PROF.TXN_DELIVERY_METHOD,PROF.TXN_DELIVERY_METHOD) IN ('XML_INV','PRINT_INV','EMAIL_INV')))

AND XEP.LEGAL_ENTITY_ID = T.LEGAL_ENTITY_ID

AND NVL(XEP.LE_INFORMATION16,'XX') = 'XX'

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_SEQ_ID TRX_TYPE_SEQ_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,

T.PRINTING_PENDING PRINTING_PENDING,

T.ORG_ID ORG_ID,

PROF.B2B_TP_CODE

FROM RA_CUSTOMER_TRX T,

RA_CUST_TRX_TYPES_ALL TT,

HZ_CUST_ACCT_SITES_ALL CAS,

HZ_CUST_SITE_USES_ALL CSU,

HZ_CUST_ACCOUNTS CA,

HZ_CUSTOMER_PROFILES_F PROF,

(SELECT NVL(MAX(ENABLED_FLAG),'N') XML_FEATURE_ENABLED FROM FND_LOOKUP_VALUES_B WHERE LOOKUP_TYPE = 'AR_FEATURES' AND LOOKUP_CODE = 'AR_XML_INVOICE_ENHANCED') FND,

HZ_CUSTOMER_PROFILES_F SITE_PROF,

XLE_ENTITY_PROFILES xep

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_SEQ_ID = TT.CUST_TRX_TYPE_SEQ_ID

AND TT.TYPE = 'DM'

AND PROF.CUST_ACCOUNT_ID = CA.CUST_ACCOUNT_ID

AND PROF.SITE_USE_ID IS NULL

AND TRUNC(T.TRX_DATE) BETWEEN TRUNC(PROF.EFFECTIVE_START_DATE) AND NVL(TRUNC(PROF.EFFECTIVE_END_DATE),TRUNC(T.TRX_DATE))

AND PROF.TXN_DELIVERY_METHOD = 'XML_INV'

AND T.PRINTING_PENDING = 'Y'

AND T.PRINTING_OPTION = 'PRI'

AND PROF.XML_DM_FLAG = 'Y'

AND T.BILL_TO_CUSTOMER_ID = SITE_PROF.CUST_ACCOUNT_ID(+)

AND T.BILL_TO_SITE_USE_ID = SITE_PROF.SITE_USE_ID(+)

AND TRUNC(T.TRX_DATE) BETWEEN TRUNC(SITE_PROF.EFFECTIVE_START_DATE(+)) AND NVL(TRUNC(SITE_PROF.EFFECTIVE_END_DATE(+)),TRUNC(T.TRX_DATE))

AND ((NVL(SITE_PROF.TXN_DELIVERY_METHOD,PROF.TXN_DELIVERY_METHOD) = 'XML_INV'

AND FND.XML_FEATURE_ENABLED = 'Y')

OR (FND.XML_FEATURE_ENABLED = 'N' AND NVL(SITE_PROF.TXN_DELIVERY_METHOD,PROF.TXN_DELIVERY_METHOD) IN ('XML_INV','PRINT_INV','EMAIL_INV')))

AND XEP.LEGAL_ENTITY_ID = T.LEGAL_ENTITY_ID

AND NVL(XEP.LE_INFORMATION16,'XX') = 'XX'

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_SEQ_ID TRX_TYPE_SEQ_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,

T.PRINTING_PENDING PRINTING_PENDING,

T.ORG_ID ORG_ID,

PROF.B2B_TP_CODE

FROM RA_CUSTOMER_TRX T,

RA_CUST_TRX_TYPES_ALL TT,

HZ_CUST_ACCT_SITES_ALL CAS,

HZ_CUST_SITE_USES_ALL CSU,

HZ_CUST_ACCOUNTS CA,

HZ_CUSTOMER_PROFILES_F PROF,

(SELECT NVL(MAX(ENABLED_FLAG),'N') XML_FEATURE_ENABLED FROM FND_LOOKUP_VALUES_B WHERE LOOKUP_TYPE = 'AR_FEATURES' AND LOOKUP_CODE = 'AR_XML_INVOICE_ENHANCED') FND,

HZ_CUSTOMER_PROFILES_F SITE_PROF,

XLE_ENTITY_PROFILES XEP

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_SEQ_ID = TT.CUST_TRX_TYPE_SEQ_ID

AND TT.TYPE = 'CB'

AND PROF.CUST_ACCOUNT_ID = CA.CUST_ACCOUNT_ID

AND PROF.SITE_USE_ID IS NULL

AND TRUNC(T.TRX_DATE) BETWEEN TRUNC(PROF.EFFECTIVE_START_DATE) AND NVL(TRUNC(PROF.EFFECTIVE_END_DATE),TRUNC(T.TRX_DATE))

AND PROF.TXN_DELIVERY_METHOD = 'XML_INV'

AND T.PRINTING_PENDING = 'Y'

AND T.PRINTING_OPTION = 'PRI'

AND PROF.XML_CB_FLAG = 'Y'

AND T.BILL_TO_CUSTOMER_ID = SITE_PROF.CUST_ACCOUNT_ID(+)

AND T.BILL_TO_SITE_USE_ID = SITE_PROF.SITE_USE_ID(+)

AND TRUNC(T.TRX_DATE) BETWEEN TRUNC(SITE_PROF.EFFECTIVE_START_DATE(+)) AND NVL(TRUNC(SITE_PROF.EFFECTIVE_END_DATE(+)),TRUNC(T.TRX_DATE))

AND ((NVL(SITE_PROF.TXN_DELIVERY_METHOD,PROF.TXN_DELIVERY_METHOD) = 'XML_INV'

AND FND.XML_FEATURE_ENABLED = 'Y')

OR (FND.XML_FEATURE_ENABLED = 'N' AND NVL(SITE_PROF.TXN_DELIVERY_METHOD,PROF.TXN_DELIVERY_METHOD) IN ('XML_INV','PRINT_INV','EMAIL_INV')))

AND XEP.LEGAL_ENTITY_ID = T.LEGAL_ENTITY_ID

AND NVL(XEP.LE_INFORMATION16,'XX') = 'XX'

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_SEQ_ID TRX_TYPE_SEQ_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,

T.PRINTING_PENDING PRINTING_PENDING,

T.ORG_ID ORG_ID,

PROF.B2B_TP_CODE

FROM RA_CUSTOMER_TRX T,

RA_CUST_TRX_TYPES_ALL TT,

HZ_CUST_ACCT_SITES_ALL CAS,

HZ_CUST_SITE_USES_ALL CSU,

HZ_CUST_ACCOUNTS CA,

XLE_ENTITY_PROFILES XEP,

HZ_CUSTOMER_PROFILES_F PROF

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 PROF.CUST_ACCOUNT_ID = CA.CUST_ACCOUNT_ID

AND PROF.SITE_USE_ID IS NULL

AND TRUNC(T.TRX_DATE) BETWEEN TRUNC(PROF.EFFECTIVE_START_DATE) AND NVL(TRUNC(PROF.EFFECTIVE_END_DATE),TRUNC(T.TRX_DATE))

AND T.COMPLETE_FLAG = 'Y'

AND T.CUST_TRX_TYPE_SEQ_ID = TT.CUST_TRX_TYPE_SEQ_ID

AND TT.TYPE IN ('INV','DM','CM','CB')

AND T.PRINTING_PENDING = 'Y'

AND T.PRINTING_OPTION = 'PRI'

AND XEP.LEGAL_ENTITY_ID = T.LEGAL_ENTITY_ID

AND NVL(XEP.LE_INFORMATION16,'XX') = 'ORA_XML_WITH_PDF'

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 )