GMS_ALL_CONTACTS

Details

Schema: FUSION
Object owner: GMS
Object type: VIEW

Columns

Name
PERS_ID NAME ORGANIZATION_ID ORGANIZATION_NAME PHONE_NUMBER EMAIL_ADDRESS JOB_TITLE CONTACT_TYPE

Name

PERS_ID

NAME

ORGANIZATION_ID

ORGANIZATION_NAME

PHONE_NUMBER

EMAIL_ADDRESS

JOB_TITLE

CONTACT_TYPE

Query

SQL_Statement
select /+ FIRST_ROWS(10) / distinct AP.PERSONNEL_ID as PERS_ID, PPN.DISPLAY_NAME as NAME , NULL as ORGANIZATION_ID, NULL as ORGANIZATION_NAME, PPH.PHONE_NUMBER as PHONE_NUMBER, PEA.EMAIL_ADDRESS as EMAIL_ADDRESS, NULL as JOB_TITLE, NULL as CONTACT_TYPE from gms_award_personnel AP, PER_ALL_PEOPLE_F PAP, PER_PERSON_NAMES_F_V PPN, PER_PHONES_V PPH, PER_EMAIL_ADDRESSES_V PEA where AP.PERSONNEL_ID = PAP.PERSON_ID and INTERNAL_FLAG = 'Y' and PAP.PERSON_ID = PPN.PERSON_ID and PAP.PRIMARY_EMAIL_ID = PEA.EMAIL_ADDRESS_ID (+) and PAP.PRIMARY_PHONE_ID = PPH.PHONE_ID (+) and trunc(SYSDATE) between trunc(PAP.EFFECTIVE_START_DATE) and trunc (PAP.EFFECTIVE_END_DATE) and trunc(SYSDATE) between trunc(PPN.EFFECTIVE_START_DATE) and trunc (PPN.EFFECTIVE_END_DATE) union all SELECT DISTINCT EC.PARTY_ID as PERS_ID, EC.PARTY_NAME as RESOURCE_SOURCE, EC.PARTY_ID as ORGANIZATION_ID, NULL as ORGANIZATION_NAME, EC.PRIMARY_PHONE_NUMBER as PHONE_NUMBER, EC.EMAIL_ADDRESS as EMAIL_ADDRESS, EC.JOB_TITLE as JOB_TITLE, null as CONTACT_TYPE from gms_award_personnel AP, GMS_EXTERNAL_CONTACTS_V EC where AP.PERSONNEL_ID = EC.PARTY_ID and AP.INTERNAL_FLAG = 'N'

SQL_Statement

select /*+ FIRST_ROWS(10) */

distinct AP.PERSONNEL_ID as PERS_ID,

PPN.DISPLAY_NAME as NAME ,

NULL as ORGANIZATION_ID,

NULL as ORGANIZATION_NAME,

PPH.PHONE_NUMBER as PHONE_NUMBER,

PEA.EMAIL_ADDRESS as EMAIL_ADDRESS,

NULL as JOB_TITLE,

NULL as CONTACT_TYPE

from gms_award_personnel AP,

PER_ALL_PEOPLE_F PAP,

PER_PERSON_NAMES_F_V PPN,

PER_PHONES_V PPH,

PER_EMAIL_ADDRESSES_V PEA

where AP.PERSONNEL_ID = PAP.PERSON_ID

and INTERNAL_FLAG = 'Y'

and PAP.PERSON_ID = PPN.PERSON_ID

and PAP.PRIMARY_EMAIL_ID = PEA.EMAIL_ADDRESS_ID (+)

and PAP.PRIMARY_PHONE_ID = PPH.PHONE_ID (+)

and trunc(SYSDATE) between trunc(PAP.EFFECTIVE_START_DATE) and trunc

(PAP.EFFECTIVE_END_DATE)

and trunc(SYSDATE) between trunc(PPN.EFFECTIVE_START_DATE) and trunc

(PPN.EFFECTIVE_END_DATE)

union all

SELECT DISTINCT EC.PARTY_ID as PERS_ID,

EC.PARTY_NAME as RESOURCE_SOURCE,

EC.PARTY_ID as ORGANIZATION_ID,

NULL as ORGANIZATION_NAME,

EC.PRIMARY_PHONE_NUMBER as PHONE_NUMBER,

EC.EMAIL_ADDRESS as EMAIL_ADDRESS,

EC.JOB_TITLE as JOB_TITLE,

null as CONTACT_TYPE

from gms_award_personnel AP,

GMS_EXTERNAL_CONTACTS_V EC

where AP.PERSONNEL_ID = EC.PARTY_ID

and AP.INTERNAL_FLAG = 'N'