View: AST_RELATIONSHIP_DETAILS_V

Product:	AST - TeleSales
Description:
Implementation/DBA Data:	APPS.AST_RELATIONSHIP_DETAILS_V

View Text

SELECT REL.PARTY_ID
, PARTY.PARTY_NAME
, ROLE_LKUP.MEANING
, ORG_CONTACT.JOB_TITLE
, TITLE_LKUP.MEANING
, ORG_CONTACT.DEPARTMENT
, DEPT_LKUP.MEANING
, PARTY.ADDRESS1 || ' ' || PARTY.CITY || ' ' || PARTY.STATE || ' ' || PARTY.POSTAL_CODE || ' ' || TER.TERRITORY_SHORT_NAME
, ORG_CONTACT.MAIL_STOP
, CONT_POINT.PHONE_COUNTRY_CODE
, CONT_POINT.PHONE_AREA_CODE
, CONT_POINT.PHONE_NUMBER
, CONT_POINT.PHONE_EXTENSION
, PARTY.EMAIL_ADDRESS
FROM HZ_PARTIES PARTY
, FND_TERRITORIES_TL TER
, HZ_ORG_CONTACTS ORG_CONTACT
, HZ_ORG_CONTACT_ROLES ROLES
, HZ_RELATIONSHIPS REL
, HZ_CONTACT_POINTS CONT_POINT
, AR_LOOKUPS ROLE_LKUP
, AR_LOOKUPS TITLE_LKUP
, AR_LOOKUPS DEPT_LKUP
WHERE ORG_CONTACT.PARTY_RELATIONSHIP_ID = REL.RELATIONSHIP_ID
AND REL.DIRECTIONAL_FLAG = 'F'
AND PARTY.PARTY_ID = REL.PARTY_ID
AND TER.TERRITORY_CODE(+) = PARTY.COUNTRY
AND TER.LANGUAGE(+) = USERENV('LANG')
AND ORG_CONTACT.ORG_CONTACT_ID = ROLES.ORG_CONTACT_ID(+)
AND ROLES.PRIMARY_FLAG(+) = 'Y'
AND CONT_POINT.OWNER_TABLE_NAME(+) = 'HZ_PARTIES'
AND CONT_POINT.OWNER_TABLE_ID(+) = REL.PARTY_ID
AND CONT_POINT.CONTACT_POINT_TYPE(+) = 'PHONE'
AND CONT_POINT.PRIMARY_FLAG(+) = 'Y'
AND ROLE_LKUP.LOOKUP_TYPE(+) = 'CONTACT_ROLE_TYPE'
AND ROLE_LKUP.LOOKUP_CODE(+)= ROLES.ROLE_TYPE
AND TITLE_LKUP.LOOKUP_TYPE(+) = 'RESPONSIBILITY'
AND TITLE_LKUP.LOOKUP_CODE(+) = ORG_CONTACT.JOB_TITLE_CODE
AND DEPT_LKUP.LOOKUP_TYPE(+) = 'DEPARTMENT_TYPE'
AND DEPT_LKUP.LOOKUP_CODE(+) = ORG_CONTACT.DEPARTMENT_CODE

Columns