VIEW: APPS.RA_CUSTOMER_TRX_CR_TRX_V

Source

View Text - Preformatted

SELECT CT.ROWID , CT.CUSTOMER_TRX_ID , CT.TRX_NUMBER , CT.TRX_DATE , CT.TERM_DUE_DATE , CT.INITIAL_CUSTOMER_TRX_ID , CT.PREVIOUS_CUSTOMER_TRX_ID , CT.CUST_TRX_TYPE_ID , CT.BATCH_ID , CT.BATCH_SOURCE_ID , CT.TERM_ID , CT.PRIMARY_SALESREP_ID , CT.AGREEMENT_ID , CT.RECEIPT_METHOD_ID , CT.CUSTOMER_BANK_ACCOUNT_ID , CT.PAYING_CUSTOMER_ID , CT.PAYING_SITE_USE_ID , CT.INVOICING_RULE_ID , CT.COMPLETE_FLAG , CT.STATUS_TRX , CT.BILL_TO_CUSTOMER_ID , CT.BILL_TO_SITE_USE_ID , AS_BILL.CUST_ACCT_SITE_ID , CT.BILL_TO_CONTACT_ID , PARTY_BILL.JGZZ_FISCAL_CODE , CT.SHIP_TO_CUSTOMER_ID , CT.SHIP_TO_SITE_USE_ID , AS_SHIP.CUST_ACCT_SITE_ID , CT.SHIP_TO_CONTACT_ID , PARTY_SHIP.JGZZ_FISCAL_CODE , CT.REMIT_TO_ADDRESS_ID , CT.INVOICE_CURRENCY_CODE , CT.CREATED_FROM , CT.SET_OF_BOOKS_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_COMMIT.TRX_NUMBER , SUBSTRB(PARTY_BILL.PARTY_NAME,1,50) , CUST_BILL.ACCOUNT_NUMBER , SU_BILL.LOCATION , LOC_BILL.ADDRESS1 , LOC_BILL.ADDRESS2 , LOC_BILL.ADDRESS3 || DECODE(LOC_BILL.ADDRESS3, '', '', ', ') || LOC_BILL.CITY || '                                   , ' || NVL(LOC_BILL.STATE, LOC_BILL.PROVINCE) || '      , ' || ARPT_SQL_FUNC_UTIL.GET_TERRITORY(AS_BILL.CUST_ACCT_SITE_ID) , SUBSTRB(PARTY_SHIP.PARTY_NAME,1,50) , CUST_SHIP.ACCOUNT_NUMBER , SU_SHIP.LOCATION , LOC_SHIP.ADDRESS1 , LOC_SHIP.ADDRESS2 , LOC_SHIP.ADDRESS3 || DECODE(LOC_SHIP.ADDRESS3, '', '', ', ') || LOC_SHIP.CITY || '                                   , ' || NVL(LOC_SHIP.STATE, LOC_SHIP.PROVINCE) || '      , ' || ARPT_SQL_FUNC_UTIL.GET_TERRITORY(AS_SHIP.CUST_ACCT_SITE_ID) , LOC_REMIT.ADDRESS1 || '                               , ' || LOC_REMIT.ADDRESS2 || '                          , ' || LOC_REMIT.ADDRESS3 || DECODE(LOC_REMIT.ADDRESS3, '', '', ',          ' ) || LOC_REMIT.CITY || '                           , ' || NVL(LOC_REMIT.STATE, LOC_REMIT.PROVINCE) || '      , ' || ARPT_SQL_FUNC_UTIL.GET_TERRITORY(AS_REMIT.CUST_ACCT_SITE_ID) , LOC_REMIT.ADDRESS1 , LOC_REMIT.ADDRESS2 , LOC_REMIT.ADDRESS3 || DECODE(LOC_REMIT.ADDRESS3, '', '', ', ' ) || LOC_REMIT.CITY || '                                 , ' || NVL(LOC_REMIT.STATE, LOC_REMIT.PROVINCE) || '      , ' || ARPT_SQL_FUNC_UTIL.GET_TERRITORY(AS_REMIT.CUST_ACCT_SITE_ID) , BS.NAME , BS.CREDIT_MEMO_BATCH_SOURCE_ID , RAB.NAME , RAB.BATCH_DATE , RAB.GL_DATE , RAB.BATCH_SOURCE_ID , CTT.NAME , CTT.TYPE , CTT.CREDIT_MEMO_TYPE_ID , RAS.NAME , RAS.SALESREP_NUMBER , RAT.NAME , SOA.NAME , ARPT_SQL_FUNC_UTIL.GET_REFERENCE(CT.ROWID) , GD.GL_DATE , CT.EXCHANGE_RATE , CT.EXCHANGE_RATE_TYPE , CT.EXCHANGE_DATE , GDCT.USER_CONVERSION_TYPE , AL_STATUS.MEANING , CTT.POST_TO_GL , CTT.ACCOUNTING_AFFECT_FLAG , CTT.CREATION_SIGN , CTT.ALLOW_FREIGHT_FLAG , CTT.ALLOW_OVERAPPLICATION_FLAG , CTT.NATURAL_APPLICATION_ONLY_FLAG , CT.SHIP_DATE_ACTUAL , CT.WAYBILL_NUMBER , CT.FOB_POINT , CT.SHIP_VIA , FND_ATTACHMENT_UTIL_PKG.GET_ATCHMT_EXISTS('RA_CUSTOMER_TRX', CT.CUSTOMER_TRX_ID) , SU_BILL.TAX_HEADER_LEVEL_FLAG , CT.LAST_UPDATE_DATE , CT.LAST_UPDATED_BY , CT.CREATION_DATE , CT.CREATED_BY , CT.LAST_UPDATE_LOGIN , CT.org_id , CT.legal_entity_id FROM RA_CUSTOMER_TRX CT, RA_CUSTOMER_TRX_ALL CT_COMMIT, RA_CUST_TRX_LINE_GL_DIST_ALL GD, HZ_CUST_ACCOUNTS CUST_BILL, HZ_PARTIES PARTY_BILL, HZ_CUST_ACCOUNTS CUST_SHIP, HZ_PARTIES PARTY_SHIP, HZ_CUST_SITE_USES_ALL SU_BILL, HZ_CUST_SITE_USES_ALL SU_SHIP, HZ_CUST_ACCT_SITES_ALL AS_BILL, HZ_PARTY_SITES PS_BILL, HZ_LOCATIONS LOC_BILL, HZ_CUST_ACCT_SITES_ALL AS_SHIP, HZ_PARTY_SITES PS_SHIP, HZ_LOCATIONS LOC_SHIP, HZ_CUST_ACCT_SITES_ALL AS_REMIT, HZ_PARTY_SITES PS_REMIT, HZ_LOCATIONS LOC_REMIT, 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_STATUS WHERE CT.PREVIOUS_CUSTOMER_TRX_ID IS NULL AND CT.COMPLETE_FLAG = 'Y' AND CT.INITIAL_CUSTOMER_TRX_ID = CT_COMMIT.CUSTOMER_TRX_ID(+) AND CT.CUSTOMER_TRX_ID = GD.CUSTOMER_TRX_ID AND 'REC' = GD.ACCOUNT_CLASS AND 'Y' = GD.LATEST_REC_FLAG AND CT.BILL_TO_CUSTOMER_ID = CUST_BILL.CUST_ACCOUNT_ID AND CUST_BILL.PARTY_ID = PARTY_BILL.PARTY_ID AND CT.SHIP_TO_CUSTOMER_ID = CUST_SHIP.CUST_ACCOUNT_ID(+) AND CUST_SHIP.PARTY_ID = PARTY_SHIP.PARTY_ID(+) AND CT.BILL_TO_SITE_USE_ID = SU_BILL.SITE_USE_ID AND CT.SHIP_TO_SITE_USE_ID = SU_SHIP.SITE_USE_ID(+) AND SU_BILL.CUST_ACCT_SITE_ID = AS_BILL.CUST_ACCT_SITE_ID AND AS_BILL.PARTY_SITE_ID = PS_BILL.PARTY_SITE_ID AND LOC_BILL.LOCATION_ID = PS_BILL.LOCATION_ID AND SU_SHIP.CUST_ACCT_SITE_ID = AS_SHIP.CUST_ACCT_SITE_ID(+) AND AS_SHIP.PARTY_SITE_ID = PS_SHIP.PARTY_SITE_ID(+) AND LOC_SHIP.LOCATION_ID(+) = PS_SHIP.LOCATION_ID AND CT.REMIT_TO_ADDRESS_ID = AS_REMIT.CUST_ACCT_SITE_ID(+) AND AS_REMIT.PARTY_SITE_ID = PS_REMIT.PARTY_SITE_ID(+) AND LOC_REMIT.LOCATION_ID(+) = PS_REMIT.LOCATION_ID AND CT.BATCH_SOURCE_ID = BS.BATCH_SOURCE_ID AND CT.BATCH_ID = RAB.BATCH_ID(+) AND CT.CUST_TRX_TYPE_ID = CTT.CUST_TRX_TYPE_ID AND CTT.TYPE IN ('INV', 'DEP', 'GUAR', 'DM', 'CB') AND CT.PRIMARY_SALESREP_ID = RAS.SALESREP_ID(+) AND CT.TERM_ID = RAT.TERM_ID(+) AND CT.AGREEMENT_ID = SOA.AGREEMENT_ID(+) AND CT.EXCHANGE_RATE_TYPE = GDCT.CONVERSION_TYPE(+) AND CT.STATUS_TRX = AL_STATUS.LOOKUP_CODE(+) AND 'INVOICE_TRX_STATUS' = AL_STATUS.LOOKUP_TYPE(+) and CTT.org_id = ct.org_id and BS.org_id = ct.org_id

View Text - HTML Formatted

SELECT CT.ROWID
, CT.CUSTOMER_TRX_ID
, CT.TRX_NUMBER
, CT.TRX_DATE
, CT.TERM_DUE_DATE
, CT.INITIAL_CUSTOMER_TRX_ID
, CT.PREVIOUS_CUSTOMER_TRX_ID
, CT.CUST_TRX_TYPE_ID
, CT.BATCH_ID
, CT.BATCH_SOURCE_ID
, CT.TERM_ID
, CT.PRIMARY_SALESREP_ID
, CT.AGREEMENT_ID
, CT.RECEIPT_METHOD_ID
, CT.CUSTOMER_BANK_ACCOUNT_ID
, CT.PAYING_CUSTOMER_ID
, CT.PAYING_SITE_USE_ID
, CT.INVOICING_RULE_ID
, CT.COMPLETE_FLAG
, CT.STATUS_TRX
, CT.BILL_TO_CUSTOMER_ID
, CT.BILL_TO_SITE_USE_ID
, AS_BILL.CUST_ACCT_SITE_ID
, CT.BILL_TO_CONTACT_ID
, PARTY_BILL.JGZZ_FISCAL_CODE
, CT.SHIP_TO_CUSTOMER_ID
, CT.SHIP_TO_SITE_USE_ID
, AS_SHIP.CUST_ACCT_SITE_ID
, CT.SHIP_TO_CONTACT_ID
, PARTY_SHIP.JGZZ_FISCAL_CODE
, CT.REMIT_TO_ADDRESS_ID
, CT.INVOICE_CURRENCY_CODE
, CT.CREATED_FROM
, CT.SET_OF_BOOKS_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_COMMIT.TRX_NUMBER
, SUBSTRB(PARTY_BILL.PARTY_NAME
, 1
, 50)
, CUST_BILL.ACCOUNT_NUMBER
, SU_BILL.LOCATION
, LOC_BILL.ADDRESS1
, LOC_BILL.ADDRESS2
, LOC_BILL.ADDRESS3 || DECODE(LOC_BILL.ADDRESS3
, ''
, ''
, '
, ') || LOC_BILL.CITY || '
, ' || NVL(LOC_BILL.STATE
, LOC_BILL.PROVINCE) || '
, ' || ARPT_SQL_FUNC_UTIL.GET_TERRITORY(AS_BILL.CUST_ACCT_SITE_ID)
, SUBSTRB(PARTY_SHIP.PARTY_NAME
, 1
, 50)
, CUST_SHIP.ACCOUNT_NUMBER
, SU_SHIP.LOCATION
, LOC_SHIP.ADDRESS1
, LOC_SHIP.ADDRESS2
, LOC_SHIP.ADDRESS3 || DECODE(LOC_SHIP.ADDRESS3
, ''
, ''
, '
, ') || LOC_SHIP.CITY || '
, ' || NVL(LOC_SHIP.STATE
, LOC_SHIP.PROVINCE) || '
, ' || ARPT_SQL_FUNC_UTIL.GET_TERRITORY(AS_SHIP.CUST_ACCT_SITE_ID)
, LOC_REMIT.ADDRESS1 || '
, ' || LOC_REMIT.ADDRESS2 || '
, ' || LOC_REMIT.ADDRESS3 || DECODE(LOC_REMIT.ADDRESS3
, ''
, ''
, '
, ' ) || LOC_REMIT.CITY || '
, ' || NVL(LOC_REMIT.STATE
, LOC_REMIT.PROVINCE) || '
, ' || ARPT_SQL_FUNC_UTIL.GET_TERRITORY(AS_REMIT.CUST_ACCT_SITE_ID)
, LOC_REMIT.ADDRESS1
, LOC_REMIT.ADDRESS2
, LOC_REMIT.ADDRESS3 || DECODE(LOC_REMIT.ADDRESS3
, ''
, ''
, '
, ' ) || LOC_REMIT.CITY || '
, ' || NVL(LOC_REMIT.STATE
, LOC_REMIT.PROVINCE) || '
, ' || ARPT_SQL_FUNC_UTIL.GET_TERRITORY(AS_REMIT.CUST_ACCT_SITE_ID)
, BS.NAME
, BS.CREDIT_MEMO_BATCH_SOURCE_ID
, RAB.NAME
, RAB.BATCH_DATE
, RAB.GL_DATE
, RAB.BATCH_SOURCE_ID
, CTT.NAME
, CTT.TYPE
, CTT.CREDIT_MEMO_TYPE_ID
, RAS.NAME
, RAS.SALESREP_NUMBER
, RAT.NAME
, SOA.NAME
, ARPT_SQL_FUNC_UTIL.GET_REFERENCE(CT.ROWID)
, GD.GL_DATE
, CT.EXCHANGE_RATE
, CT.EXCHANGE_RATE_TYPE
, CT.EXCHANGE_DATE
, GDCT.USER_CONVERSION_TYPE
, AL_STATUS.MEANING
, CTT.POST_TO_GL
, CTT.ACCOUNTING_AFFECT_FLAG
, CTT.CREATION_SIGN
, CTT.ALLOW_FREIGHT_FLAG
, CTT.ALLOW_OVERAPPLICATION_FLAG
, CTT.NATURAL_APPLICATION_ONLY_FLAG
, CT.SHIP_DATE_ACTUAL
, CT.WAYBILL_NUMBER
, CT.FOB_POINT
, CT.SHIP_VIA
, FND_ATTACHMENT_UTIL_PKG.GET_ATCHMT_EXISTS('RA_CUSTOMER_TRX'
, CT.CUSTOMER_TRX_ID)
, SU_BILL.TAX_HEADER_LEVEL_FLAG
, CT.LAST_UPDATE_DATE
, CT.LAST_UPDATED_BY
, CT.CREATION_DATE
, CT.CREATED_BY
, CT.LAST_UPDATE_LOGIN
, CT.ORG_ID
, CT.LEGAL_ENTITY_ID
FROM RA_CUSTOMER_TRX CT
, RA_CUSTOMER_TRX_ALL CT_COMMIT
, RA_CUST_TRX_LINE_GL_DIST_ALL GD
, HZ_CUST_ACCOUNTS CUST_BILL
, HZ_PARTIES PARTY_BILL
, HZ_CUST_ACCOUNTS CUST_SHIP
, HZ_PARTIES PARTY_SHIP
, HZ_CUST_SITE_USES_ALL SU_BILL
, HZ_CUST_SITE_USES_ALL SU_SHIP
, HZ_CUST_ACCT_SITES_ALL AS_BILL
, HZ_PARTY_SITES PS_BILL
, HZ_LOCATIONS LOC_BILL
, HZ_CUST_ACCT_SITES_ALL AS_SHIP
, HZ_PARTY_SITES PS_SHIP
, HZ_LOCATIONS LOC_SHIP
, HZ_CUST_ACCT_SITES_ALL AS_REMIT
, HZ_PARTY_SITES PS_REMIT
, HZ_LOCATIONS LOC_REMIT
, 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_STATUS
WHERE CT.PREVIOUS_CUSTOMER_TRX_ID IS NULL
AND CT.COMPLETE_FLAG = 'Y'
AND CT.INITIAL_CUSTOMER_TRX_ID = CT_COMMIT.CUSTOMER_TRX_ID(+)
AND CT.CUSTOMER_TRX_ID = GD.CUSTOMER_TRX_ID
AND 'REC' = GD.ACCOUNT_CLASS
AND 'Y' = GD.LATEST_REC_FLAG
AND CT.BILL_TO_CUSTOMER_ID = CUST_BILL.CUST_ACCOUNT_ID
AND CUST_BILL.PARTY_ID = PARTY_BILL.PARTY_ID
AND CT.SHIP_TO_CUSTOMER_ID = CUST_SHIP.CUST_ACCOUNT_ID(+)
AND CUST_SHIP.PARTY_ID = PARTY_SHIP.PARTY_ID(+)
AND CT.BILL_TO_SITE_USE_ID = SU_BILL.SITE_USE_ID
AND CT.SHIP_TO_SITE_USE_ID = SU_SHIP.SITE_USE_ID(+)
AND SU_BILL.CUST_ACCT_SITE_ID = AS_BILL.CUST_ACCT_SITE_ID
AND AS_BILL.PARTY_SITE_ID = PS_BILL.PARTY_SITE_ID
AND LOC_BILL.LOCATION_ID = PS_BILL.LOCATION_ID
AND SU_SHIP.CUST_ACCT_SITE_ID = AS_SHIP.CUST_ACCT_SITE_ID(+)
AND AS_SHIP.PARTY_SITE_ID = PS_SHIP.PARTY_SITE_ID(+)
AND LOC_SHIP.LOCATION_ID(+) = PS_SHIP.LOCATION_ID
AND CT.REMIT_TO_ADDRESS_ID = AS_REMIT.CUST_ACCT_SITE_ID(+)
AND AS_REMIT.PARTY_SITE_ID = PS_REMIT.PARTY_SITE_ID(+)
AND LOC_REMIT.LOCATION_ID(+) = PS_REMIT.LOCATION_ID
AND CT.BATCH_SOURCE_ID = BS.BATCH_SOURCE_ID
AND CT.BATCH_ID = RAB.BATCH_ID(+)
AND CT.CUST_TRX_TYPE_ID = CTT.CUST_TRX_TYPE_ID
AND CTT.TYPE IN ('INV'
, 'DEP'
, 'GUAR'
, 'DM'
, 'CB')
AND CT.PRIMARY_SALESREP_ID = RAS.SALESREP_ID(+)
AND CT.TERM_ID = RAT.TERM_ID(+)
AND CT.AGREEMENT_ID = SOA.AGREEMENT_ID(+)
AND CT.EXCHANGE_RATE_TYPE = GDCT.CONVERSION_TYPE(+)
AND CT.STATUS_TRX = AL_STATUS.LOOKUP_CODE(+)
AND 'INVOICE_TRX_STATUS' = AL_STATUS.LOOKUP_TYPE(+)
AND CTT.ORG_ID = CT.ORG_ID
AND BS.ORG_ID = CT.ORG_ID