View: RA_CUSTOMER_TRX_CM_V

Product:	AR - Receivables
Description:	(Release 115 Only)
Implementation/DBA Data:	APPS.RA_CUSTOMER_TRX_CM_V

View Text

SELECT CT.ROWID
, CT.CUSTOMER_TRX_ID
, CT.TRX_NUMBER
, CT.OLD_TRX_NUMBER
, CT.TRX_DATE
, CT.TERM_DUE_DATE
, /* RESOLVE THESE FOREIGN KEY REFERENCES */ CT.PREVIOUS_CUSTOMER_TRX_ID
, CT.INITIAL_CUSTOMER_TRX_ID
, CT.RELATED_BATCH_SOURCE_ID
, CT.RELATED_CUSTOMER_TRX_ID
, CT.CUST_TRX_TYPE_ID
, CT.BATCH_ID
, CT.BATCH_SOURCE_ID
, CT.REASON_CODE
, CT.TERM_ID
, CT.PRIMARY_SALESREP_ID
, CT.AGREEMENT_ID
, CT.CREDIT_METHOD_FOR_RULES
, CT.CREDIT_METHOD_FOR_INSTALLMENTS
, CT.RECEIPT_METHOD_ID
, CT.INVOICING_RULE_ID
, CT.FINANCE_CHARGES
, CT.COMPLETE_FLAG
, CT.CUSTOMER_BANK_ACCOUNT_ID
, CT.RECURRED_FROM_TRX_NUMBER
, CT.STATUS_TRX
, CT.DEFAULT_TAX_EXEMPT_FLAG
, CT.SOLD_TO_CUSTOMER_ID
, CT.SOLD_TO_SITE_USE_ID
, CT.SOLD_TO_CONTACT_ID
, CT.BILL_TO_CUSTOMER_ID
, CT.BILL_TO_SITE_USE_ID
, RAA_BILL.CUST_ACCT_SITE_ID
, CT.BILL_TO_CONTACT_ID
, RAC_BILL_PARTY.JGZZ_FISCAL_CODE
, CT.SHIP_TO_CUSTOMER_ID
, CT.SHIP_TO_SITE_USE_ID
, RAA_SHIP.CUST_ACCT_SITE_ID
, CT.SHIP_TO_CONTACT_ID
, RAC_SHIP_PARTY.JGZZ_FISCAL_CODE
, CT.REMIT_TO_ADDRESS_ID
, /* END FOREIGN KEY REFERENCES TO BE RESOLVED */ CT.INVOICE_CURRENCY_CODE
, CT.CREATED_FROM
, CT.SET_OF_BOOKS_ID
, CT.PRINTING_ORIGINAL_DATE
, CT.PRINTING_LAST_PRINTED
, CT.PRINTING_OPTION
, CT.PRINTING_COUNT
, CT.PRINTING_PENDING
, CT.LAST_PRINTED_SEQUENCE_NUM
, CT.PURCHASE_ORDER
, CT.PURCHASE_ORDER_REVISION
, CT.PURCHASE_ORDER_DATE
, CT.CUSTOMER_REFERENCE
, CT.CUSTOMER_REFERENCE_DATE
, CT.COMMENTS
, CT.INTERNAL_NOTES
, CT.EXCHANGE_RATE_TYPE
, CT.EXCHANGE_DATE
, CT.EXCHANGE_RATE
, CT.TERRITORY_ID
, CT.END_DATE_COMMITMENT
, CT.START_DATE_COMMITMENT
, CT_COMMIT.TRX_DATE
, GD_COMMIT.GL_DATE
, CT.ORIG_SYSTEM_BATCH_NAME
, CT.SHIP_DATE_ACTUAL
, CT.WAYBILL_NUMBER
, CT.FOB_POINT
, CT.SHIP_VIA
, CT.DOC_SEQUENCE_ID
, CT.DOC_SEQUENCE_VALUE
, CT.PAYING_CUSTOMER_ID
, CT.PAYING_SITE_USE_ID
, CT.ATTRIBUTE_CATEGORY
, CT.ATTRIBUTE1
, CT.ATTRIBUTE2
, CT.ATTRIBUTE3
, CT.ATTRIBUTE4
, CT.ATTRIBUTE5
, CT.ATTRIBUTE6
, CT.ATTRIBUTE7
, CT.ATTRIBUTE8
, CT.ATTRIBUTE9
, CT.ATTRIBUTE10
, CT.ATTRIBUTE11
, CT.ATTRIBUTE12
, CT.ATTRIBUTE13
, CT.ATTRIBUTE14
, CT.ATTRIBUTE15
, CT.INTERFACE_HEADER_CONTEXT
, CT.INTERFACE_HEADER_ATTRIBUTE1
, CT.INTERFACE_HEADER_ATTRIBUTE2
, CT.INTERFACE_HEADER_ATTRIBUTE3
, CT.INTERFACE_HEADER_ATTRIBUTE4
, CT.INTERFACE_HEADER_ATTRIBUTE5
, CT.INTERFACE_HEADER_ATTRIBUTE6
, CT.INTERFACE_HEADER_ATTRIBUTE7
, CT.INTERFACE_HEADER_ATTRIBUTE8
, CT.INTERFACE_HEADER_ATTRIBUTE9
, CT.INTERFACE_HEADER_ATTRIBUTE10
, CT.INTERFACE_HEADER_ATTRIBUTE11
, CT.INTERFACE_HEADER_ATTRIBUTE12
, CT.INTERFACE_HEADER_ATTRIBUTE13
, CT.INTERFACE_HEADER_ATTRIBUTE14
, CT.INTERFACE_HEADER_ATTRIBUTE15
, CT.DEFAULT_USSGL_TRX_CODE_CONTEXT
, CT.DEFAULT_USSGL_TRANSACTION_CODE
, CT.LAST_UPDATE_DATE
, CT.LAST_UPDATED_BY
, CT.CREATION_DATE
, CT.CREATED_BY
, CT.LAST_UPDATE_LOGIN
, CT.POST_REQUEST_ID
, CT.REQUEST_ID
, CT.PROGRAM_APPLICATION_ID
, CT.PROGRAM_ID
, CT.PROGRAM_UPDATE_DATE
, CT.POSTING_CONTROL_ID
, CT.RA_POST_LOOP_NUMBER
, CT_COMMIT.TRX_NUMBER
, CT_REL.TRX_NUMBER
, SUBSTRB(RAC_BILL_PARTY.PARTY_NAME
, 1
, 50)
, RAC_BILL.ACCOUNT_NUMBER
, SU_BILL.LOCATION
, RAA_BILL_LOC.ADDRESS1
, RAA_BILL_LOC.ADDRESS2
, RAA_BILL_LOC.ADDRESS3 || DECODE(RAA_BILL_LOC.ADDRESS3
, ''
, ''
, '
, ') || RAA_BILL_LOC.CITY || '
, ' || NVL(RAA_BILL_LOC.STATE
, RAA_BILL_LOC.PROVINCE) || '
, ' || ARPT_SQL_FUNC_UTIL.GET_TERRITORY(RAA_BILL.CUST_ACCT_SITE_ID)
, SUBSTRB(RACO_BILL_PARTY.PERSON_FIRST_NAME
, 1
, 40) ||' ' || SUBSTRB(RACO_BILL_PARTY.PERSON_LAST_NAME
, 1
, 50)
, SUBSTRB(RAC_SHIP_PARTY.PARTY_NAME
, 1
, 50)
, RAC_SHIP.ACCOUNT_NUMBER
, SU_SHIP.LOCATION
, RAA_SHIP_LOC.ADDRESS1
, RAA_SHIP_LOC.ADDRESS2
, RAA_SHIP_LOC.ADDRESS3 || DECODE(RAA_SHIP_LOC.ADDRESS3
, ''
, ''
, '
, ') || RAA_SHIP_LOC.CITY || '
, ' || NVL(RAA_SHIP_LOC.STATE
, RAA_SHIP_LOC.PROVINCE) || '
, ' || ARPT_SQL_FUNC_UTIL.GET_TERRITORY(RAA_SHIP.CUST_ACCT_SITE_ID)
, SUBSTRB(RACO_SHIP_PARTY.PERSON_FIRST_NAME
, 1
, 40) || ' ' || SUBSTRB(RACO_SHIP_PARTY.PERSON_LAST_NAME
, 1
, 50)
, SUBSTRB(RAC_SOLD_PARTY.PARTY_NAME
, 1
, 50)
, RAC_SOLD.ACCOUNT_NUMBER
, SUBSTRB(RAC_PAYING_PARTY.PARTY_NAME
, 1
, 50)
, RAC_PAYING.ACCOUNT_NAME
, SU_PAYING.LOCATION
, RAA_REMIT_LOC.ADDRESS1 || '
, ' || RAA_REMIT_LOC.ADDRESS2 || '
, ' || RAA_REMIT_LOC.ADDRESS3 || DECODE(RAA_REMIT_LOC.ADDRESS3
, ''
, ''
, '
, ' ) || RAA_REMIT_LOC.CITY || '
, ' || NVL(RAA_REMIT_LOC.STATE
, RAA_REMIT_LOC.PROVINCE) || '
, ' || ARPT_SQL_FUNC_UTIL.GET_TERRITORY(RAA_REMIT.CUST_ACCT_SITE_ID)
, RAA_REMIT_LOC.ADDRESS1
, RAA_REMIT_LOC.ADDRESS2
, RAA_REMIT_LOC.ADDRESS3 || DECODE(RAA_REMIT_LOC.ADDRESS3
, ''
, ''
, '
, ' ) || RAA_REMIT_LOC.CITY || '
, ' || NVL(RAA_REMIT_LOC.STATE
, RAA_REMIT_LOC.PROVINCE) || '
, ' || ARPT_SQL_FUNC_UTIL.GET_TERRITORY(RAA_REMIT.CUST_ACCT_SITE_ID)
, NULL
, NULL
, NULL
, NULL
, ARM.NAME
, BS.NAME
, BS.COPY_DOC_NUMBER_FLAG
, BS.COPY_INV_TIDFF_TO_CM_FLAG
, BS.BATCH_SOURCE_TYPE
, DECODE(CT.BATCH_ID
, NULL
, 'NONE'
, CT_CRTRX.BATCH_ID
, 'TRX_BATCH'
, 'CM_BATCH')
, RAB.NAME
, RAB.BATCH_DATE
, RAB.GL_DATE
, RAB.BATCH_SOURCE_ID
, CTT.NAME
, CTT.TYPE
, RAS.NAME
, RAS.SALESREP_NUMBER
, RAT.NAME
, SOA.NAME
, NVL(AL_INV_REASON.MEANING
, AL_CM_REASON.MEANING)
, ARPT_SQL_FUNC_UTIL.GET_REFERENCE(CT.ROWID)
, GD.GL_DATE
, GDCT.USER_CONVERSION_TYPE
, AL_STATUS.MEANING
, ARPT_SQL_FUNC_UTIL.GET_CB_INVOICE( CT.CUSTOMER_TRX_ID
, CTT.TYPE)
, ARPT_SQL_FUNC_UTIL.GET_DISPUTE_AMOUNT( CT.CUSTOMER_TRX_ID
, CTT.TYPE
, CTT.ACCOUNTING_AFFECT_FLAG )
, ARPT_SQL_FUNC_UTIL.GET_DISPUTE_DATE( CT.CUSTOMER_TRX_ID
, CTT.TYPE
, CTT.ACCOUNTING_AFFECT_FLAG )
, ARPT_SQL_FUNC_UTIL.GET_REVENUE_RECOG_RUN_FLAG(CT.CUSTOMER_TRX_ID
, CT.INVOICING_RULE_ID)
, ARPT_SQL_FUNC_UTIL.GET_POSTED_FLAG( CT.CUSTOMER_TRX_ID
, CTT.POST_TO_GL
, CT.COMPLETE_FLAG )
, ARPT_SQL_FUNC_UTIL.GET_SELECTED_FOR_PAYMENT_FLAG( CT.CUSTOMER_TRX_ID
, CTT.ACCOUNTING_AFFECT_FLAG
, CT.COMPLETE_FLAG )
, ARPT_SQL_FUNC_UTIL.GET_ACTIVITY_FLAG( CT.CUSTOMER_TRX_ID
, CTT.ACCOUNTING_AFFECT_FLAG
, CT.COMPLETE_FLAG
, CTT.TYPE
, CT.INITIAL_CUSTOMER_TRX_ID
, CT.PREVIOUS_CUSTOMER_TRX_ID )
, DECODE(CT.INVOICING_RULE_ID
, NULL
, 'N'
, 'Y')
, DECODE(CT.PRINTING_LAST_PRINTED
, NULL
, 'N'
, 'Y')
, CTT.POST_TO_GL
, CTT.ACCOUNTING_AFFECT_FLAG
, CTT.CREATION_SIGN
, CTT.ALLOW_FREIGHT_FLAG
, CTT.ALLOW_OVERAPPLICATION_FLAG
, CTT.NATURAL_APPLICATION_ONLY_FLAG
, FND_ATTACHMENT_UTIL_PKG.GET_ATCHMT_EXISTS('RA_CUSTOMER_TRX'
, CT.CUSTOMER_TRX_ID)
, CT.GLOBAL_ATTRIBUTE_CATEGORY
, CT.GLOBAL_ATTRIBUTE1
, CT.GLOBAL_ATTRIBUTE2
, CT.GLOBAL_ATTRIBUTE3
, CT.GLOBAL_ATTRIBUTE4
, CT.GLOBAL_ATTRIBUTE5
, CT.GLOBAL_ATTRIBUTE6
, CT.GLOBAL_ATTRIBUTE7
, CT.GLOBAL_ATTRIBUTE8
, CT.GLOBAL_ATTRIBUTE9
, CT.GLOBAL_ATTRIBUTE10
, CT.GLOBAL_ATTRIBUTE11
, CT.GLOBAL_ATTRIBUTE12
, CT.GLOBAL_ATTRIBUTE13
, CT.GLOBAL_ATTRIBUTE14
, CT.GLOBAL_ATTRIBUTE15
, CT.GLOBAL_ATTRIBUTE16
, CT.GLOBAL_ATTRIBUTE17
, CT.GLOBAL_ATTRIBUTE18
, CT.GLOBAL_ATTRIBUTE19
, CT.GLOBAL_ATTRIBUTE20
, CT.GLOBAL_ATTRIBUTE21
, CT.GLOBAL_ATTRIBUTE22
, CT.GLOBAL_ATTRIBUTE23
, CT.GLOBAL_ATTRIBUTE24
, CT.GLOBAL_ATTRIBUTE25
, CT.GLOBAL_ATTRIBUTE26
, CT.GLOBAL_ATTRIBUTE27
, CT.GLOBAL_ATTRIBUTE28
, CT.GLOBAL_ATTRIBUTE29
, CT.GLOBAL_ATTRIBUTE30
, BS.DEFAULT_REFERENCE /* 7136591 */
FROM RA_CUSTOMER_TRX CT
, RA_CUSTOMER_TRX_ALL CT_CRTRX
, RA_CUSTOMER_TRX_ALL CT_COMMIT
, RA_CUSTOMER_TRX_ALL CT_REL
, RA_CUST_TRX_LINE_GL_DIST_ALL GD
, RA_CUST_TRX_LINE_GL_DIST_ALL GD_COMMIT
, HZ_CUST_ACCOUNTS RAC_BILL
, HZ_PARTIES RAC_BILL_PARTY
, HZ_CUST_ACCOUNTS RAC_SHIP
, HZ_PARTIES RAC_SHIP_PARTY
, HZ_CUST_ACCOUNTS RAC_SOLD
, HZ_PARTIES RAC_SOLD_PARTY
, HZ_CUST_ACCOUNTS RAC_PAYING
, HZ_PARTIES RAC_PAYING_PARTY
, HZ_CUST_SITE_USES_ALL SU_BILL
, HZ_CUST_SITE_USES_ALL SU_SHIP
, HZ_CUST_SITE_USES_ALL SU_PAYING
, HZ_CUST_ACCT_SITES_ALL RAA_BILL
, HZ_PARTY_SITES RAA_BILL_PS
, HZ_LOCATIONS RAA_BILL_LOC
, HZ_CUST_ACCT_SITES_ALL RAA_SHIP
, HZ_PARTY_SITES RAA_SHIP_PS
, HZ_LOCATIONS RAA_SHIP_LOC
, HZ_CUST_ACCT_SITES_ALL RAA_REMIT
, HZ_PARTY_SITES RAA_REMIT_PS
, HZ_LOCATIONS RAA_REMIT_LOC
, HZ_CUST_ACCOUNT_ROLES RACO_SHIP
, HZ_PARTIES RACO_SHIP_PARTY
, HZ_RELATIONSHIPS RACO_SHIP_REL
, HZ_CUST_ACCOUNT_ROLES RACO_BILL
, HZ_PARTIES RACO_BILL_PARTY
, HZ_RELATIONSHIPS RACO_BILL_REL
, AR_RECEIPT_METHODS ARM
, RA_BATCH_SOURCES_ALL BS
, RA_BATCHES_ALL RAB
, RA_CUST_TRX_TYPES_ALL CTT
, RA_SALESREPS RAS
, RA_TERMS RAT
, SO_AGREEMENTS SOA
, GL_DAILY_CONVERSION_TYPES GDCT
, AR_LOOKUPS AL_INV_REASON
, AR_LOOKUPS AL_CM_REASON
, AR_LOOKUPS AL_STATUS
WHERE CT.INITIAL_CUSTOMER_TRX_ID = CT_COMMIT.CUSTOMER_TRX_ID(+)
AND CT.ORG_ID = CT_COMMIT.ORG_ID(+)
AND CT.RELATED_CUSTOMER_TRX_ID = CT_REL.CUSTOMER_TRX_ID(+)
AND CT.ORG_ID = CT_REL.ORG_ID(+)
AND CT.PREVIOUS_CUSTOMER_TRX_ID = CT_CRTRX.CUSTOMER_TRX_ID
AND CT.ORG_ID = CT_CRTRX.ORG_ID
AND CT.CUSTOMER_TRX_ID = GD.CUSTOMER_TRX_ID
AND CT.ORG_ID = GD.ORG_ID
AND 'REC' = GD.ACCOUNT_CLASS
AND 'Y' = GD.LATEST_REC_FLAG
AND CT_COMMIT.CUSTOMER_TRX_ID = GD_COMMIT.CUSTOMER_TRX_ID(+)
AND CT_COMMIT.ORG_ID = GD_COMMIT.ORG_ID(+)
AND 'REC' = GD_COMMIT.ACCOUNT_CLASS(+)
AND 'Y' = GD_COMMIT.LATEST_REC_FLAG(+)
AND CT.BILL_TO_CUSTOMER_ID = RAC_BILL.CUST_ACCOUNT_ID
AND RAC_BILL.PARTY_ID = RAC_BILL_PARTY.PARTY_ID
AND CT.SHIP_TO_CUSTOMER_ID = RAC_SHIP.CUST_ACCOUNT_ID(+)
AND RAC_SHIP.PARTY_ID = RAC_SHIP_PARTY.PARTY_ID(+)
AND CT.SOLD_TO_CUSTOMER_ID = RAC_SOLD.CUST_ACCOUNT_ID(+)
AND RAC_SOLD.PARTY_ID = RAC_SOLD_PARTY.PARTY_ID(+)
AND CT.PAYING_CUSTOMER_ID = RAC_PAYING.CUST_ACCOUNT_ID(+)
AND RAC_PAYING.PARTY_ID = RAC_PAYING_PARTY.PARTY_ID(+)
AND CT.BILL_TO_SITE_USE_ID = SU_BILL.SITE_USE_ID
AND CT.ORG_ID = SU_BILL.ORG_ID(+)
AND CT.SHIP_TO_SITE_USE_ID = SU_SHIP.SITE_USE_ID (+)
AND CT.ORG_ID = SU_SHIP.ORG_ID(+)
AND CT.PAYING_SITE_USE_ID = SU_PAYING.SITE_USE_ID(+)
AND CT.ORG_ID = SU_PAYING.ORG_ID(+)
AND SU_BILL.CUST_ACCT_SITE_ID = RAA_BILL.CUST_ACCT_SITE_ID
AND SU_BILL.ORG_ID = RAA_BILL.ORG_ID
AND RAA_BILL.PARTY_SITE_ID = RAA_BILL_PS.PARTY_SITE_ID
AND RAA_BILL_LOC.LOCATION_ID = RAA_BILL_PS.LOCATION_ID
AND SU_SHIP.CUST_ACCT_SITE_ID = RAA_SHIP.CUST_ACCT_SITE_ID(+)
AND SU_SHIP.ORG_ID = RAA_SHIP.ORG_ID(+)
AND RAA_SHIP.PARTY_SITE_ID = RAA_SHIP_PS.PARTY_SITE_ID(+)
AND RAA_SHIP_LOC.LOCATION_ID(+) = RAA_SHIP_PS.LOCATION_ID
AND CT.BILL_TO_CONTACT_ID = RACO_BILL.CUST_ACCOUNT_ROLE_ID(+)
AND RACO_BILL.PARTY_ID = RACO_BILL_REL.PARTY_ID(+)
AND RACO_BILL_REL.SUBJECT_TABLE_NAME(+) = 'HZ_PARTIES'
AND RACO_BILL_REL.OBJECT_TABLE_NAME(+) = 'HZ_PARTIES'
AND RACO_BILL_REL.DIRECTIONAL_FLAG(+) = 'F'
AND RACO_BILL.ROLE_TYPE(+) = 'CONTACT'
AND RACO_BILL_REL.SUBJECT_ID = RACO_BILL_PARTY.PARTY_ID(+)
AND CT.SHIP_TO_CONTACT_ID = RACO_SHIP.CUST_ACCOUNT_ROLE_ID(+)
AND RACO_SHIP.PARTY_ID = RACO_SHIP_REL.PARTY_ID(+)
AND RACO_SHIP_REL.SUBJECT_TABLE_NAME(+) = 'HZ_PARTIES'
AND RACO_SHIP_REL.OBJECT_TABLE_NAME(+) = 'HZ_PARTIES'
AND RACO_SHIP_REL.DIRECTIONAL_FLAG(+) = 'F'
AND RACO_SHIP.ROLE_TYPE(+) = 'CONTACT'
AND RACO_SHIP_REL.SUBJECT_ID = RACO_SHIP_PARTY.PARTY_ID(+)
AND CT.REMIT_TO_ADDRESS_ID = RAA_REMIT.CUST_ACCT_SITE_ID(+)
AND CT.ORG_ID = RAA_REMIT.ORG_ID(+)
AND RAA_REMIT.PARTY_SITE_ID = RAA_REMIT_PS.PARTY_SITE_ID(+)
AND RAA_REMIT_LOC.LOCATION_ID(+) = RAA_REMIT_PS.LOCATION_ID
AND CT.RECEIPT_METHOD_ID = ARM.RECEIPT_METHOD_ID(+)
AND CT.BATCH_SOURCE_ID = BS.BATCH_SOURCE_ID
AND CT.ORG_ID = BS.ORG_ID
AND CT.BATCH_ID = RAB.BATCH_ID(+)
AND CT.ORG_ID = RAB.ORG_ID(+)
AND CT.CUST_TRX_TYPE_ID = CTT.CUST_TRX_TYPE_ID
AND CT.ORG_ID = CTT.ORG_ID
AND CT.PRIMARY_SALESREP_ID = RAS.SALESREP_ID(+)
AND CT.ORG_ID = RAS.ORG_ID(+)
AND CT.TERM_ID = RAT.TERM_ID(+)
AND CT.AGREEMENT_ID = SOA.AGREEMENT_ID(+)
AND CT.EXCHANGE_RATE_TYPE = GDCT.CONVERSION_TYPE(+)
AND 'CREDIT_MEMO_REASON' = AL_CM_REASON.LOOKUP_TYPE(+)
AND CT.REASON_CODE = AL_CM_REASON.LOOKUP_CODE(+)
AND 'INVOICING_REASON' = AL_INV_REASON.LOOKUP_TYPE(+)
AND CT.REASON_CODE = AL_INV_REASON.LOOKUP_CODE(+)
AND 'INVOICE_TRX_STATUS' = AL_STATUS.LOOKUP_TYPE(+)
AND CT.STATUS_TRX = AL_STATUS.LOOKUP_CODE(+)

Columns