View: OTA_CUSTOMER_CONTACTS_V

Product:	OTA - Learning Management
Description:	View to list all Contacts for a Customer.
Implementation/DBA Data:	APPS.OTA_CUSTOMER_CONTACTS_V

View Text

SELECT ACCT_ROLE.CUST_ACCOUNT_ID
, ACCT_ROLE.CUST_ACCOUNT_ROLE_ID
, SUBSTRB(PARTY.PERSON_FIRST_NAME
, 1
, 40)
, SUBSTRB(PARTY.PERSON_LAST_NAME
, 1
, 50)
, PARTY.PERSON_PRE_NAME_ADJUNCT TITLE
, TIT.MEANING TITLE_MEANING
, OTA_TDB_BUS.GET_FULL_NAME(SUBSTRB(PARTY.PERSON_LAST_NAME
, 1
, 50)
, TIT.MEANING
, SUBSTRB(PARTY.PERSON_FIRST_NAME
, 1
, 40))
, ACCT_ROLE.STATUS
, ROL.RESPONSIBILITY_TYPE
, ORG_CONT.MAIL_STOP
, ACCT_ROLE.CUST_ACCT_SITE_ID
, LOC.ADDRESS1||DECODE(LOC.ADDRESS1
, NULL
, ''
, '
, ')|| LOC.ADDRESS2|| DECODE(LOC.ADDRESS2
, NULL
, ''
, '
, ')|| LOC.ADDRESS3|| DECODE(LOC.ADDRESS3
, NULL
, ''
, '
, ')|| LOC.ADDRESS4|| DECODE(LOC.ADDRESS4
, NULL
, ''
, '
, ')|| LOC.CITY||DECODE(LOC.CITY
, NULL
, ''
, '
, ')|| LOC.STATE||DECODE(LOC.STATE
, NULL
, ''
, '
, ')|| LOC.PROVINCE|| DECODE(LOC.PROVINCE
, NULL
, ''
, '
, ')|| LOC.COUNTY|| DECODE(LOC.COUNTY
, NULL
, ''
, '
, ')|| LOC.POSTAL_CODE|| DECODE(LOC.POSTAL_CODE
, NULL
, ''
, '
, ')|| LOC.COUNTRY ADDRESS
, NULL
, NULL
FROM HZ_CUST_ACCOUNT_ROLES ACCT_ROLE
, HZ_PARTIES PARTY
, HZ_RELATIONSHIPS REL
, HZ_ORG_CONTACTS ORG_CONT
, HZ_PARTIES REL_PARTY
, HZ_CUST_ACCOUNTS ROLE_ACCT
, AR_LOOKUPS TIT
, HZ_ROLE_RESPONSIBILITY ROL
, HZ_LOCATIONS LOC
, HZ_CUST_ACCT_SITES_ALL ACCT_SITE
, HZ_PARTY_SITES PARTY_SITE
WHERE PARTY.PERSON_PRE_NAME_ADJUNCT = TIT.LOOKUP_CODE(+)
AND TIT.LOOKUP_TYPE(+) = 'CONTACT_TITLE'
AND ROL.CUST_ACCOUNT_ROLE_ID (+) = ACCT_ROLE.CUST_ACCOUNT_ROLE_ID
AND ROL.PRIMARY_FLAG (+) = 'Y'
AND ACCT_SITE.CUST_ACCT_SITE_ID (+) = ACCT_ROLE.CUST_ACCT_SITE_ID
AND PARTY_SITE.LOCATION_ID = LOC.LOCATION_ID(+)
AND ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID(+)
AND ACCT_ROLE.PARTY_ID = REL.PARTY_ID
AND ACCT_ROLE.ROLE_TYPE = 'CONTACT'
AND ORG_CONT.PARTY_RELATIONSHIP_ID = REL.RELATIONSHIP_ID
AND REL.SUBJECT_ID = PARTY.PARTY_ID
AND REL.PARTY_ID = REL_PARTY.PARTY_ID
AND REL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
AND REL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
AND ACCT_ROLE.CUST_ACCOUNT_ID = ROLE_ACCT.CUST_ACCOUNT_ID
AND ROLE_ACCT.PARTY_ID = REL.OBJECT_ID
AND DECODE(FND_PROFILE.VALUE('OTA_HR_GLOBAL_BUSINESS_GROUP_ID')
, NULL
, (NVL(ACCT_SITE.ORG_ID (+)
, NVL(TO_NUMBER(DECODE(SUBSTRB( USERENV('CLIENT_INFO')
, 1
, 1)
, ' '
, NULL
, SUBSTRB( USERENV('CLIENT_INFO')
, 1
, 10)))
, -99)))
, 1 ) = DECODE(FND_PROFILE.VALUE('OTA_HR_GLOBAL_BUSINESS_GROUP_ID')
, NULL
, (NVL(TO_NUMBER(DECODE(SUBSTRB( USERENV('CLIENT_INFO')
, 1
, 1)
, ' '
, NULL
, SUBSTRB(USERENV( 'CLIENT_INFO')
, 1
, 10)))
, -99))
, 1 )

Columns