APPS.AR_CUSTOMER_CALLS_V
[Home] [Help]
| Product: | AR - Receivables |
|---|---|
| Description: | (Release 11.5 Only) |
| Implementation/DBA Data: |
APPS.AR_CUSTOMER_CALLS_V
|
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(+))