VIEW: APPS.RA_CUSTOMER_TRX_V

Source

View Text - Preformatted

SELECT CT.ROWID , CT.CUSTOMER_TRX_ID , CT.TRX_NUMBER , CT.TRX_DATE , ARPT_SQL_FUNC_UTIL.GET_FIRST_REAL_DUE_DATE(CT.CUSTOMER_TRX_ID, CT.TERM_ID ,CT.TRX_DATE) , CT.COMPLETE_FLAG , CT.STATUS_TRX , CT.BILL_TO_CUSTOMER_ID , CT.BILL_TO_SITE_USE_ID , CT.SHIP_TO_CUSTOMER_ID , CT.SHIP_TO_SITE_USE_ID , CT.INVOICE_CURRENCY_CODE , CT.PURCHASE_ORDER , CT.INTERNAL_NOTES , CT.SHIP_DATE_ACTUAL , CT.WAYBILL_NUMBER , CT.DOC_SEQUENCE_VALUE , 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_COMMIT.TRX_NUMBER , CT_PREV.TRX_NUMBER , BS_PREV.NAME , CT_REL.TRX_NUMBER , RAC_BILL_PARTY.PARTY_NAME , RAC_BILL.ACCOUNT_NUMBER , SU_BILL.LOCATION , RAA_BILL_LOC.ADDRESS1 , RAA_BILL_LOC.ADDRESS2 , DECODE( RAA_BILL.CUST_ACCT_SITE_ID, NULL, NULL, ARH_ADDR_PKG.FORMAT_LAST_ADDRESS_LINE(RAA_BILL_LOC.ADDRESS_STYLE, RAA_BILL_LOC.ADDRESS3, RAA_BILL_LOC.ADDRESS4, RAA_BILL_LOC.CITY, RAA_BILL_LOC.COUNTY, RAA_BILL_LOC.STATE, RAA_BILL_LOC.PROVINCE, FT_BILL.TERRITORY_SHORT_NAME, RAA_BILL_LOC.POSTAL_CODE) ) , RAC_SHIP_PARTY.PARTY_NAME , RAC_SHIP.ACCOUNT_NUMBER , SU_SHIP.LOCATION , RAA_SHIP_LOC.ADDRESS1 , RAA_SHIP_LOC.ADDRESS2 , DECODE( RAA_SHIP.CUST_ACCT_SITE_ID, NULL, NULL, ARH_ADDR_PKG.FORMAT_LAST_ADDRESS_LINE(RAA_SHIP_LOC.ADDRESS_STYLE, RAA_SHIP_LOC.ADDRESS3, RAA_SHIP_LOC.ADDRESS4, RAA_SHIP_LOC.CITY, RAA_SHIP_LOC.COUNTY, RAA_SHIP_LOC.STATE, RAA_SHIP_LOC.PROVINCE, FT_SHIP.TERRITORY_SHORT_NAME, RAA_SHIP_LOC.POSTAL_CODE) ) , BS.NAME , CTT.NAME , CTT.TYPE , ARPT_SQL_FUNC_UTIL.GET_SALESREP_NAME_NUMBER(CT.PRIMARY_SALESREP_ID, 'NAME',CT.org_id) , ARPT_SQL_FUNC_UTIL.GET_TERM_DETAILS( CT.TERM_ID, 'NAME') , ARPT_SQL_FUNC_UTIL.GET_AGREEMENT_NAME(CT.AGREEMENT_ID) , ORF.DESCRIPTION , ORF.ORGANIZATION_ID , ARPT_SQL_FUNC_UTIL.GET_REFERENCE(CT.ROWID) , GD.GL_DATE , CTT.ACCOUNTING_AFFECT_FLAG , CT.BATCH_SOURCE_ID FROM RA_CUSTOMER_TRX CT, RA_CUSTOMER_TRX CT_COMMIT, RA_CUSTOMER_TRX CT_PREV, RA_CUSTOMER_TRX CT_REL, RA_CUST_TRX_LINE_GL_DIST GD_PREV, RA_CUST_TRX_LINE_GL_DIST GD_COMMIT, RA_CUST_TRX_LINE_GL_DIST GD, RA_BATCH_SOURCES BS_PREV, HZ_CUST_ACCOUNTS RAC_BILL, HZ_PARTIES RAC_BILL_PARTY, HZ_CUST_ACCOUNTS RAC_SHIP, HZ_PARTIES RAC_SHIP_PARTY, HZ_CUST_SITE_USES SU_BILL, HZ_CUST_SITE_USES SU_SHIP, FND_TERRITORIES_VL FT_BILL, FND_TERRITORIES_VL FT_SHIP, HZ_CUST_ACCT_SITES RAA_BILL, HZ_PARTY_SITES RAA_BILL_PS, HZ_LOCATIONS RAA_BILL_LOC, HZ_CUST_ACCT_SITES RAA_SHIP, HZ_PARTY_SITES RAA_SHIP_PS, HZ_LOCATIONS RAA_SHIP_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, RA_BATCH_SOURCES BS, RA_CUST_TRX_TYPES CTT, ORG_FREIGHT ORF WHERE CT.CUSTOMER_TRX_ID = GD.CUSTOMER_TRX_ID AND 'REC' = GD.ACCOUNT_CLASS AND 'Y' = GD.LATEST_REC_FLAG AND CT.INITIAL_CUSTOMER_TRX_ID = CT_COMMIT.CUSTOMER_TRX_ID(+) AND CT.PREVIOUS_CUSTOMER_TRX_ID = CT_PREV.CUSTOMER_TRX_ID(+) AND CT.RELATED_CUSTOMER_TRX_ID = CT_REL.CUSTOMER_TRX_ID(+) AND CT_PREV.CUSTOMER_TRX_ID = GD_PREV.CUSTOMER_TRX_ID(+) AND 'REC' = GD_PREV.ACCOUNT_CLASS(+) AND 'Y' = GD_PREV.LATEST_REC_FLAG(+) AND CT_COMMIT.CUSTOMER_TRX_ID = GD_COMMIT.CUSTOMER_TRX_ID(+) AND 'REC' = GD_COMMIT.ACCOUNT_CLASS(+) AND 'Y' = GD_COMMIT.LATEST_REC_FLAG(+) AND CT_PREV.BATCH_SOURCE_ID = BS_PREV.BATCH_SOURCE_ID(+) 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.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 = RAA_BILL.CUST_ACCT_SITE_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 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 RAA_BILL_LOC.COUNTRY = FT_BILL.TERRITORY_CODE(+) AND RAA_SHIP_LOC.COUNTRY = FT_SHIP.TERRITORY_CODE(+) AND CT.BATCH_SOURCE_ID = BS.BATCH_SOURCE_ID AND CT.CUST_TRX_TYPE_ID = CTT.CUST_TRX_TYPE_ID AND CT.SHIP_VIA = ORF.FREIGHT_CODE(+)

View Text - HTML Formatted

SELECT CT.ROWID
, CT.CUSTOMER_TRX_ID
, CT.TRX_NUMBER
, CT.TRX_DATE
, ARPT_SQL_FUNC_UTIL.GET_FIRST_REAL_DUE_DATE(CT.CUSTOMER_TRX_ID
, CT.TERM_ID
, CT.TRX_DATE)
, CT.COMPLETE_FLAG
, CT.STATUS_TRX
, CT.BILL_TO_CUSTOMER_ID
, CT.BILL_TO_SITE_USE_ID
, CT.SHIP_TO_CUSTOMER_ID
, CT.SHIP_TO_SITE_USE_ID
, CT.INVOICE_CURRENCY_CODE
, CT.PURCHASE_ORDER
, CT.INTERNAL_NOTES
, CT.SHIP_DATE_ACTUAL
, CT.WAYBILL_NUMBER
, CT.DOC_SEQUENCE_VALUE
, 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_COMMIT.TRX_NUMBER
, CT_PREV.TRX_NUMBER
, BS_PREV.NAME
, CT_REL.TRX_NUMBER
, RAC_BILL_PARTY.PARTY_NAME
, RAC_BILL.ACCOUNT_NUMBER
, SU_BILL.LOCATION
, RAA_BILL_LOC.ADDRESS1
, RAA_BILL_LOC.ADDRESS2
, DECODE( RAA_BILL.CUST_ACCT_SITE_ID
, NULL
, NULL
, ARH_ADDR_PKG.FORMAT_LAST_ADDRESS_LINE(RAA_BILL_LOC.ADDRESS_STYLE
, RAA_BILL_LOC.ADDRESS3
, RAA_BILL_LOC.ADDRESS4
, RAA_BILL_LOC.CITY
, RAA_BILL_LOC.COUNTY
, RAA_BILL_LOC.STATE
, RAA_BILL_LOC.PROVINCE
, FT_BILL.TERRITORY_SHORT_NAME
, RAA_BILL_LOC.POSTAL_CODE) )
, RAC_SHIP_PARTY.PARTY_NAME
, RAC_SHIP.ACCOUNT_NUMBER
, SU_SHIP.LOCATION
, RAA_SHIP_LOC.ADDRESS1
, RAA_SHIP_LOC.ADDRESS2
, DECODE( RAA_SHIP.CUST_ACCT_SITE_ID
, NULL
, NULL
, ARH_ADDR_PKG.FORMAT_LAST_ADDRESS_LINE(RAA_SHIP_LOC.ADDRESS_STYLE
, RAA_SHIP_LOC.ADDRESS3
, RAA_SHIP_LOC.ADDRESS4
, RAA_SHIP_LOC.CITY
, RAA_SHIP_LOC.COUNTY
, RAA_SHIP_LOC.STATE
, RAA_SHIP_LOC.PROVINCE
, FT_SHIP.TERRITORY_SHORT_NAME
, RAA_SHIP_LOC.POSTAL_CODE) )
, BS.NAME
, CTT.NAME
, CTT.TYPE
, ARPT_SQL_FUNC_UTIL.GET_SALESREP_NAME_NUMBER(CT.PRIMARY_SALESREP_ID
, 'NAME'
, CT.ORG_ID)
, ARPT_SQL_FUNC_UTIL.GET_TERM_DETAILS( CT.TERM_ID
, 'NAME')
, ARPT_SQL_FUNC_UTIL.GET_AGREEMENT_NAME(CT.AGREEMENT_ID)
, ORF.DESCRIPTION
, ORF.ORGANIZATION_ID
, ARPT_SQL_FUNC_UTIL.GET_REFERENCE(CT.ROWID)
, GD.GL_DATE
, CTT.ACCOUNTING_AFFECT_FLAG
, CT.BATCH_SOURCE_ID
FROM RA_CUSTOMER_TRX CT
, RA_CUSTOMER_TRX CT_COMMIT
, RA_CUSTOMER_TRX CT_PREV
, RA_CUSTOMER_TRX CT_REL
, RA_CUST_TRX_LINE_GL_DIST GD_PREV
, RA_CUST_TRX_LINE_GL_DIST GD_COMMIT
, RA_CUST_TRX_LINE_GL_DIST GD
, RA_BATCH_SOURCES BS_PREV
, HZ_CUST_ACCOUNTS RAC_BILL
, HZ_PARTIES RAC_BILL_PARTY
, HZ_CUST_ACCOUNTS RAC_SHIP
, HZ_PARTIES RAC_SHIP_PARTY
, HZ_CUST_SITE_USES SU_BILL
, HZ_CUST_SITE_USES SU_SHIP
, FND_TERRITORIES_VL FT_BILL
, FND_TERRITORIES_VL FT_SHIP
, HZ_CUST_ACCT_SITES RAA_BILL
, HZ_PARTY_SITES RAA_BILL_PS
, HZ_LOCATIONS RAA_BILL_LOC
, HZ_CUST_ACCT_SITES RAA_SHIP
, HZ_PARTY_SITES RAA_SHIP_PS
, HZ_LOCATIONS RAA_SHIP_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
, RA_BATCH_SOURCES BS
, RA_CUST_TRX_TYPES CTT
, ORG_FREIGHT ORF
WHERE CT.CUSTOMER_TRX_ID = GD.CUSTOMER_TRX_ID
AND 'REC' = GD.ACCOUNT_CLASS
AND 'Y' = GD.LATEST_REC_FLAG
AND CT.INITIAL_CUSTOMER_TRX_ID = CT_COMMIT.CUSTOMER_TRX_ID(+)
AND CT.PREVIOUS_CUSTOMER_TRX_ID = CT_PREV.CUSTOMER_TRX_ID(+)
AND CT.RELATED_CUSTOMER_TRX_ID = CT_REL.CUSTOMER_TRX_ID(+)
AND CT_PREV.CUSTOMER_TRX_ID = GD_PREV.CUSTOMER_TRX_ID(+)
AND 'REC' = GD_PREV.ACCOUNT_CLASS(+)
AND 'Y' = GD_PREV.LATEST_REC_FLAG(+)
AND CT_COMMIT.CUSTOMER_TRX_ID = GD_COMMIT.CUSTOMER_TRX_ID(+)
AND 'REC' = GD_COMMIT.ACCOUNT_CLASS(+)
AND 'Y' = GD_COMMIT.LATEST_REC_FLAG(+)
AND CT_PREV.BATCH_SOURCE_ID = BS_PREV.BATCH_SOURCE_ID(+)
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.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 = RAA_BILL.CUST_ACCT_SITE_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 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 RAA_BILL_LOC.COUNTRY = FT_BILL.TERRITORY_CODE(+)
AND RAA_SHIP_LOC.COUNTRY = FT_SHIP.TERRITORY_CODE(+)
AND CT.BATCH_SOURCE_ID = BS.BATCH_SOURCE_ID
AND CT.CUST_TRX_TYPE_ID = CTT.CUST_TRX_TYPE_ID
AND CT.SHIP_VIA = ORF.FREIGHT_CODE(+)