VIEW: APPS.AST_RELATIONSHIP_DETAILS_V

Source

View Text - Preformatted

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

View Text - HTML Formatted

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