eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.RA_CUSTOMER_TRX_CM_V

Source

View Text - Preformatted


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 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(+)

View Text - HTML Formatted

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
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(+)