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.AR_CUSTOMER_CALLS_V

Source

View Text - Preformatted


SELECT /*+ FIRST_ROWS */ ALL ACC.ROWID , ACC.LAST_UPDATED_BY , ACC.LAST_UPDATE_DATE , ACC.LAST_UPDATE_LOGIN , ACC.CREATED_BY , ACC.CREATION_DATE , ACC.CUSTOMER_CALL_ID , ACC.COLLECTOR_ID , ACO.NAME , ACC.CALL_DATE , (ACC.CALL_DATE - TRUNC(ACC.CALL_DATE))*86400 , ACC.STATUS , ACC.CUSTOMER_ID , PARTY.PARTY_NAME , CUST_ACCT.ACCOUNT_NUMBER , ACC.SITE_USE_ID , SITE_USES.LOCATION , SITE_USES.CUST_ACCT_SITE_ID , LOC.ADDRESS1 , LOC.ADDRESS2 , LOC.ADDRESS3 || DECODE(LOC.ADDRESS3, '', '', ', ') || LOC.CITY || '      , ' || NVL(LOC.STATE, LOC.PROVINCE) || '      , ' || FTE.TERRITORY_SHORT_NAME , ACC.CONTACT_ID , SUBSTRB(PARTY_CONT.PERSON_LAST_NAME,1,50) , SUBSTRB(PARTY_CONT.PERSON_FIRST_NAME,1,40) , ORG_CONT.JOB_TITLE , ACC.PHONE_ID , CONT_POINT1.PHONE_AREA_CODE , DECODE(CONT_POINT1.CONTACT_POINT_TYPE,'TLX', CONT_POINT1.TELEX_NUMBER,CONT_POINT1.PHONE_NUMBER) , CONT_POINT1.PHONE_EXTENSION , NVL(CONT_POINT1.PHONE_LINE_TYPE, CONT_POINT1.CONTACT_POINT_TYPE) , ACC.FAX_ID , DECODE(CONT_POINT2.CONTACT_POINT_TYPE,'TLX', CONT_POINT2.TELEX_NUMBER,CONT_POINT2.PHONE_NUMBER) , CONT_POINT2.PHONE_AREA_CODE , ACC.REASON_CODE /*Bug : 1631470 instead of al1,al2,al3 used following function */ , ARPT_SQL_FUNC_UTIL.get_lookup_meaning('CUSTOMER_RESPONSE_REASON',ACC.REASON_CODE) , ACC.CALL_OUTCOME , ARPT_SQL_FUNC_UTIL.get_lookup_meaning('CALL_OUTCOME',ACC.CALL_OUTCOME) , ANO.TEXT , ACC.PROMISE_DATE , ACC.CURRENCY_CODE , ACC.PROMISE_AMOUNT , ACC.FORECAST_DATE , ACC.COLLECTION_FORECAST , ACC.ATTRIBUTE_CATEGORY , ACC.ATTRIBUTE1 , ACC.ATTRIBUTE2 , ACC.ATTRIBUTE3 , ACC.ATTRIBUTE4 , ACC.ATTRIBUTE5 , ACC.ATTRIBUTE6 , ACC.ATTRIBUTE7 , ACC.ATTRIBUTE8 , ACC.ATTRIBUTE9 , ACC.ATTRIBUTE10 , ACC.ATTRIBUTE11 , ACC.ATTRIBUTE12 , ACC.ATTRIBUTE13 , ACC.ATTRIBUTE14 , ACC.ATTRIBUTE15 , ACCT_ROLE.ROWID , ACCT_ROLE.STATUS , PARTY_CONT.CUSTOMER_KEY , ORG_CONT.MAIL_STOP , ORG_CONT.TITLE , ACCT_ROLE.ATTRIBUTE_CATEGORY , ACCT_ROLE.ATTRIBUTE1 , ACCT_ROLE.ATTRIBUTE2 , ACCT_ROLE.ATTRIBUTE3 , ACCT_ROLE.ATTRIBUTE4 , ACCT_ROLE.ATTRIBUTE5 , ACCT_ROLE.ATTRIBUTE6 , ACCT_ROLE.ATTRIBUTE7 , ACCT_ROLE.ATTRIBUTE8 , ACCT_ROLE.ATTRIBUTE9 , ACCT_ROLE.ATTRIBUTE10 , ACCT_ROLE.ATTRIBUTE11 , ACCT_ROLE.ATTRIBUTE12 , ACCT_ROLE.ATTRIBUTE13 , ACCT_ROLE.ATTRIBUTE14 , ACCT_ROLE.ATTRIBUTE15 , ACCT_ROLE.ATTRIBUTE16 , ACCT_ROLE.ATTRIBUTE17 , ACCT_ROLE.ATTRIBUTE18 , ACCT_ROLE.ATTRIBUTE19 , ACCT_ROLE.ATTRIBUTE20 , ACCT_ROLE.ATTRIBUTE21 , ACCT_ROLE.ATTRIBUTE22 , ACCT_ROLE.ATTRIBUTE23 , ACCT_ROLE.ATTRIBUTE24 , ACCT_ROLE.ATTRIBUTE25 , CONT_POINT1.ROWID , CONT_POINT1.PRIMARY_FLAG , CONT_POINT1.ATTRIBUTE_CATEGORY , CONT_POINT1.ATTRIBUTE1 , CONT_POINT1.ATTRIBUTE2 , CONT_POINT1.ATTRIBUTE3 , CONT_POINT1.ATTRIBUTE4 , CONT_POINT1.ATTRIBUTE5 , CONT_POINT1.ATTRIBUTE6 , CONT_POINT1.ATTRIBUTE7 , CONT_POINT1.ATTRIBUTE8 , CONT_POINT1.ATTRIBUTE9 , CONT_POINT1.ATTRIBUTE10 , CONT_POINT1.ATTRIBUTE11 , CONT_POINT1.ATTRIBUTE12 , CONT_POINT1.ATTRIBUTE13 , CONT_POINT1.ATTRIBUTE14 , CONT_POINT1.ATTRIBUTE15 , CONT_POINT2.ROWID , CONT_POINT2.PRIMARY_FLAG , CONT_POINT2.ATTRIBUTE_CATEGORY , CONT_POINT2.ATTRIBUTE1 , CONT_POINT2.ATTRIBUTE2 , CONT_POINT2.ATTRIBUTE3 , CONT_POINT2.ATTRIBUTE4 , CONT_POINT2.ATTRIBUTE5 , CONT_POINT2.ATTRIBUTE6 , CONT_POINT2.ATTRIBUTE7 , CONT_POINT2.ATTRIBUTE8 , CONT_POINT2.ATTRIBUTE9 , CONT_POINT2.ATTRIBUTE10 , CONT_POINT2.ATTRIBUTE11 , CONT_POINT2.ATTRIBUTE12 , CONT_POINT2.ATTRIBUTE13 , CONT_POINT2.ATTRIBUTE14 , CONT_POINT2.ATTRIBUTE15 , ACC.FOLLOW_UP_ACTION , /* ADDED FOR BUG NO 349055 */ ACC.FOLLOW_UP_DATE , /* ADDED FOR BUG NO 349055 */ ARPT_SQL_FUNC_UTIL.get_lookup_meaning('FOLLOW_UP',ACC.FOLLOW_UP_ACTION) , NULL , DECODE(ACC.COMPLETE_FLAG, NULL, 'N', ACC.COMPLETE_FLAG) FROM AR_CUSTOMER_CALLS ACC, HZ_CUST_ACCOUNT_ROLES ACCT_ROLE, HZ_PARTIES PARTY_CONT, HZ_RELATIONSHIPS REL, HZ_ORG_CONTACTS ORG_CONT, HZ_CUST_ACCOUNTS CUST_ACCT, HZ_PARTIES PARTY, HZ_CONTACT_POINTS CONT_POINT1, HZ_CUST_SITE_USES SITE_USES, HZ_CUST_ACCT_SITES ACCT_SITE, HZ_PARTY_SITES PARTY_SITE, HZ_LOCATIONS LOC, AR_COLLECTORS ACO, AR_NOTES ANO, HZ_CONTACT_POINTS CONT_POINT2, FND_TERRITORIES_VL FTE WHERE ( (NVL(CONT_POINT2.PHONE_LINE_TYPE, CONT_POINT2.CONTACT_POINT_TYPE) ='FAX' OR DECODE(CONT_POINT2.CONTACT_POINT_TYPE, 'TLX', CONT_POINT2.TELEX_NUMBER, CONT_POINT2.PHONE_NUMBER) IS NULL)) AND (ACC.CUSTOMER_ID = CUST_ACCT.CUST_ACCOUNT_ID AND CUST_ACCT.PARTY_ID = PARTY.PARTY_ID AND ACC.SITE_USE_ID = SITE_USES.SITE_USE_ID(+) AND SITE_USES.CUST_ACCT_SITE_ID = ACCT_SITE.CUST_ACCT_SITE_ID (+) AND ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID(+) AND LOC.LOCATION_ID(+) = PARTY_SITE.LOCATION_ID AND ACC.CONTACT_ID = ACCT_ROLE.CUST_ACCOUNT_ROLE_ID(+) AND ACCT_ROLE.PARTY_ID = REL.PARTY_ID(+) AND REL.SUBJECT_TABLE_NAME(+) = 'HZ_PARTIES' AND REL.OBJECT_TABLE_NAME(+) = 'HZ_PARTIES' AND REL.DIRECTIONAL_FLAG(+) = 'F' AND ACCT_ROLE.ROLE_TYPE(+) = 'CONTACT' AND ORG_CONT.PARTY_RELATIONSHIP_ID(+) = REL.RELATIONSHIP_ID AND REL.SUBJECT_ID = PARTY_CONT.PARTY_ID(+) AND ACC.PHONE_ID = CONT_POINT1.CONTACT_POINT_ID(+) AND ACC.COLLECTOR_ID = ACO.COLLECTOR_ID AND ACC.CUSTOMER_CALL_ID = ANO.CUSTOMER_CALL_ID(+) AND ACC.FAX_ID = CONT_POINT2.CONTACT_POINT_ID(+) AND LOC.COUNTRY = FTE.TERRITORY_CODE(+))

View Text - HTML Formatted

SELECT /*+ FIRST_ROWS */ ALL ACC.ROWID
, ACC.LAST_UPDATED_BY
, ACC.LAST_UPDATE_DATE
, ACC.LAST_UPDATE_LOGIN
, ACC.CREATED_BY
, ACC.CREATION_DATE
, ACC.CUSTOMER_CALL_ID
, ACC.COLLECTOR_ID
, ACO.NAME
, ACC.CALL_DATE
, (ACC.CALL_DATE - TRUNC(ACC.CALL_DATE))*86400
, ACC.STATUS
, ACC.CUSTOMER_ID
, PARTY.PARTY_NAME
, CUST_ACCT.ACCOUNT_NUMBER
, ACC.SITE_USE_ID
, SITE_USES.LOCATION
, SITE_USES.CUST_ACCT_SITE_ID
, LOC.ADDRESS1
, LOC.ADDRESS2
, LOC.ADDRESS3 || DECODE(LOC.ADDRESS3
, ''
, ''
, '
, ') || LOC.CITY || '
, ' || NVL(LOC.STATE
, LOC.PROVINCE) || '
, ' || FTE.TERRITORY_SHORT_NAME
, ACC.CONTACT_ID
, SUBSTRB(PARTY_CONT.PERSON_LAST_NAME
, 1
, 50)
, SUBSTRB(PARTY_CONT.PERSON_FIRST_NAME
, 1
, 40)
, ORG_CONT.JOB_TITLE
, ACC.PHONE_ID
, CONT_POINT1.PHONE_AREA_CODE
, DECODE(CONT_POINT1.CONTACT_POINT_TYPE
, 'TLX'
, CONT_POINT1.TELEX_NUMBER
, CONT_POINT1.PHONE_NUMBER)
, CONT_POINT1.PHONE_EXTENSION
, NVL(CONT_POINT1.PHONE_LINE_TYPE
, CONT_POINT1.CONTACT_POINT_TYPE)
, ACC.FAX_ID
, DECODE(CONT_POINT2.CONTACT_POINT_TYPE
, 'TLX'
, CONT_POINT2.TELEX_NUMBER
, CONT_POINT2.PHONE_NUMBER)
, CONT_POINT2.PHONE_AREA_CODE
, ACC.REASON_CODE /*BUG : 1631470 INSTEAD OF AL1
, AL2
, AL3 USED FOLLOWING FUNCTION */
, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('CUSTOMER_RESPONSE_REASON'
, ACC.REASON_CODE)
, ACC.CALL_OUTCOME
, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('CALL_OUTCOME'
, ACC.CALL_OUTCOME)
, ANO.TEXT
, ACC.PROMISE_DATE
, ACC.CURRENCY_CODE
, ACC.PROMISE_AMOUNT
, ACC.FORECAST_DATE
, ACC.COLLECTION_FORECAST
, ACC.ATTRIBUTE_CATEGORY
, ACC.ATTRIBUTE1
, ACC.ATTRIBUTE2
, ACC.ATTRIBUTE3
, ACC.ATTRIBUTE4
, ACC.ATTRIBUTE5
, ACC.ATTRIBUTE6
, ACC.ATTRIBUTE7
, ACC.ATTRIBUTE8
, ACC.ATTRIBUTE9
, ACC.ATTRIBUTE10
, ACC.ATTRIBUTE11
, ACC.ATTRIBUTE12
, ACC.ATTRIBUTE13
, ACC.ATTRIBUTE14
, ACC.ATTRIBUTE15
, ACCT_ROLE.ROWID
, ACCT_ROLE.STATUS
, PARTY_CONT.CUSTOMER_KEY
, ORG_CONT.MAIL_STOP
, ORG_CONT.TITLE
, ACCT_ROLE.ATTRIBUTE_CATEGORY
, ACCT_ROLE.ATTRIBUTE1
, ACCT_ROLE.ATTRIBUTE2
, ACCT_ROLE.ATTRIBUTE3
, ACCT_ROLE.ATTRIBUTE4
, ACCT_ROLE.ATTRIBUTE5
, ACCT_ROLE.ATTRIBUTE6
, ACCT_ROLE.ATTRIBUTE7
, ACCT_ROLE.ATTRIBUTE8
, ACCT_ROLE.ATTRIBUTE9
, ACCT_ROLE.ATTRIBUTE10
, ACCT_ROLE.ATTRIBUTE11
, ACCT_ROLE.ATTRIBUTE12
, ACCT_ROLE.ATTRIBUTE13
, ACCT_ROLE.ATTRIBUTE14
, ACCT_ROLE.ATTRIBUTE15
, ACCT_ROLE.ATTRIBUTE16
, ACCT_ROLE.ATTRIBUTE17
, ACCT_ROLE.ATTRIBUTE18
, ACCT_ROLE.ATTRIBUTE19
, ACCT_ROLE.ATTRIBUTE20
, ACCT_ROLE.ATTRIBUTE21
, ACCT_ROLE.ATTRIBUTE22
, ACCT_ROLE.ATTRIBUTE23
, ACCT_ROLE.ATTRIBUTE24
, ACCT_ROLE.ATTRIBUTE25
, CONT_POINT1.ROWID
, CONT_POINT1.PRIMARY_FLAG
, CONT_POINT1.ATTRIBUTE_CATEGORY
, CONT_POINT1.ATTRIBUTE1
, CONT_POINT1.ATTRIBUTE2
, CONT_POINT1.ATTRIBUTE3
, CONT_POINT1.ATTRIBUTE4
, CONT_POINT1.ATTRIBUTE5
, CONT_POINT1.ATTRIBUTE6
, CONT_POINT1.ATTRIBUTE7
, CONT_POINT1.ATTRIBUTE8
, CONT_POINT1.ATTRIBUTE9
, CONT_POINT1.ATTRIBUTE10
, CONT_POINT1.ATTRIBUTE11
, CONT_POINT1.ATTRIBUTE12
, CONT_POINT1.ATTRIBUTE13
, CONT_POINT1.ATTRIBUTE14
, CONT_POINT1.ATTRIBUTE15
, CONT_POINT2.ROWID
, CONT_POINT2.PRIMARY_FLAG
, CONT_POINT2.ATTRIBUTE_CATEGORY
, CONT_POINT2.ATTRIBUTE1
, CONT_POINT2.ATTRIBUTE2
, CONT_POINT2.ATTRIBUTE3
, CONT_POINT2.ATTRIBUTE4
, CONT_POINT2.ATTRIBUTE5
, CONT_POINT2.ATTRIBUTE6
, CONT_POINT2.ATTRIBUTE7
, CONT_POINT2.ATTRIBUTE8
, CONT_POINT2.ATTRIBUTE9
, CONT_POINT2.ATTRIBUTE10
, CONT_POINT2.ATTRIBUTE11
, CONT_POINT2.ATTRIBUTE12
, CONT_POINT2.ATTRIBUTE13
, CONT_POINT2.ATTRIBUTE14
, CONT_POINT2.ATTRIBUTE15
, ACC.FOLLOW_UP_ACTION
, /* ADDED FOR BUG NO 349055 */ ACC.FOLLOW_UP_DATE
, /* ADDED FOR BUG NO 349055 */ ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('FOLLOW_UP'
, ACC.FOLLOW_UP_ACTION)
, NULL
, DECODE(ACC.COMPLETE_FLAG
, NULL
, 'N'
, ACC.COMPLETE_FLAG)
FROM AR_CUSTOMER_CALLS ACC
, HZ_CUST_ACCOUNT_ROLES ACCT_ROLE
, HZ_PARTIES PARTY_CONT
, HZ_RELATIONSHIPS REL
, HZ_ORG_CONTACTS ORG_CONT
, HZ_CUST_ACCOUNTS CUST_ACCT
, HZ_PARTIES PARTY
, HZ_CONTACT_POINTS CONT_POINT1
, HZ_CUST_SITE_USES SITE_USES
, HZ_CUST_ACCT_SITES ACCT_SITE
, HZ_PARTY_SITES PARTY_SITE
, HZ_LOCATIONS LOC
, AR_COLLECTORS ACO
, AR_NOTES ANO
, HZ_CONTACT_POINTS CONT_POINT2
, FND_TERRITORIES_VL FTE
WHERE ( (NVL(CONT_POINT2.PHONE_LINE_TYPE
, CONT_POINT2.CONTACT_POINT_TYPE) ='FAX' OR DECODE(CONT_POINT2.CONTACT_POINT_TYPE
, 'TLX'
, CONT_POINT2.TELEX_NUMBER
, CONT_POINT2.PHONE_NUMBER) IS NULL))
AND (ACC.CUSTOMER_ID = CUST_ACCT.CUST_ACCOUNT_ID
AND CUST_ACCT.PARTY_ID = PARTY.PARTY_ID
AND ACC.SITE_USE_ID = SITE_USES.SITE_USE_ID(+)
AND SITE_USES.CUST_ACCT_SITE_ID = ACCT_SITE.CUST_ACCT_SITE_ID (+)
AND ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID(+)
AND LOC.LOCATION_ID(+) = PARTY_SITE.LOCATION_ID
AND ACC.CONTACT_ID = ACCT_ROLE.CUST_ACCOUNT_ROLE_ID(+)
AND ACCT_ROLE.PARTY_ID = REL.PARTY_ID(+)
AND REL.SUBJECT_TABLE_NAME(+) = 'HZ_PARTIES'
AND REL.OBJECT_TABLE_NAME(+) = 'HZ_PARTIES'
AND REL.DIRECTIONAL_FLAG(+) = 'F'
AND ACCT_ROLE.ROLE_TYPE(+) = 'CONTACT'
AND ORG_CONT.PARTY_RELATIONSHIP_ID(+) = REL.RELATIONSHIP_ID
AND REL.SUBJECT_ID = PARTY_CONT.PARTY_ID(+)
AND ACC.PHONE_ID = CONT_POINT1.CONTACT_POINT_ID(+)
AND ACC.COLLECTOR_ID = ACO.COLLECTOR_ID
AND ACC.CUSTOMER_CALL_ID = ANO.CUSTOMER_CALL_ID(+)
AND ACC.FAX_ID = CONT_POINT2.CONTACT_POINT_ID(+)
AND LOC.COUNTRY = FTE.TERRITORY_CODE(+))