GMS_AWARD_EXTERNAL_CONTACTS_V

Details

• Schema: FUSION

• Object owner: GMS

• Object type: VIEW

Columns

Name
PARTY_NAME PARTY_ID EMAIL_ADDRESS PRIMARY_PHONE_NUMBER ORG_CONTACT_ID DEPARTMENT DEPARTMENT_CODE JOB_TITLE JOB_TITLE_CODE PARTY_RELATIONSHIP_ID CONTACT_NUMBER PARENT_PARTY_ID AWARD_PERSONNEL_ID AWARD_ID

Query

SQL_Statement

SELECT ChildParty.PARTY_NAME, ChildParty.PARTY_ID, ChildParty.EMAIL_ADDRESS, decode(ChildParty.PRIMARY_PHONE_NUMBER,NULL,NULL,ChildParty.PRIMARY_PHONE_COUNTRY_CODE || '-' || ChildParty.PRIMARY_PHONE_AREA_CODE || '-'|| ChildParty.PRIMARY_PHONE_NUMBER) as PRIMARY_PHONE_NUMBER, OrgContacts.ORG_CONTACT_ID, OrgContacts.DEPARTMENT, OrgContacts.DEPARTMENT_CODE, OrgContacts.JOB_TITLE, OrgContacts.JOB_TITLE_CODE, OrgContacts.PARTY_RELATIONSHIP_ID, OrgContacts.CONTACT_NUMBER, Relationship.SUBJECT_ID AS PARENT_PARTY_ID, AwardPersonnel.ID AS AWARD_PERSONNEL_ID, AwardPersonnel.AWARD_ID AS AWARD_ID FROM GMS_AWARD_PERSONNEL AwardPersonnel, fusion.GMS_AWARD_HEADERS_B AwardHeader, HZ_PARTIES ChildParty, HZ_RELATIONSHIPS Relationship, HZ_ORG_CONTACTS OrgContacts, gms_sponsors_b Sponsor WHERE AwardPersonnel.AWARD_ID = AwardHeader.ID and AwardHeader.sponsor_id = Sponsor.sponsor_id AND AwardPersonnel.INTERNAL_FLAG = 'N' AND AwardPersonnel.PARTY_ID = ChildParty.PARTY_ID AND Sponsor.PARTY_ID = Relationship.SUBJECT_ID AND ChildParty.PARTY_ID = Relationship.OBJECT_ID AND Relationship.OBJECT_TYPE = 'PERSON' AND Relationship.RELATIONSHIP_CODE = 'CONTACT' AND OrgContacts.PARTY_RELATIONSHIP_ID(+) = Relationship.RELATIONSHIP_ID