GMS_INTERNAL_CONTACTS

Details

Schema: FUSION
Object owner: GMS
Object type: VIEW

Columns

Name
ID AWARD_ID PERSON_ID ROLE_ID NAME PERSON_NUMBER PHONE_NUMBER EMAIL_ADDRESS JOB_TITLE DEPARTMENT START_DATE EFFECTIVE_END_DATE UNIQ_ASSIGN_COUNT

Name

ID

AWARD_ID

PERSON_ID

ROLE_ID

NAME

PERSON_NUMBER

PHONE_NUMBER

EMAIL_ADDRESS

JOB_TITLE

DEPARTMENT

START_DATE

EFFECTIVE_END_DATE

UNIQ_ASSIGN_COUNT

Query

SQL_Statement
SELECT GPP.ID AS ID, GPP.AWARD_ID AS AWARD_ID, GPP.PERSONNEL_ID AS PERSON_ID, GPP.ROLE_ID AS ROLE_ID, PER_INFOR.DISPLAY_NAME AS NAME, PER_INFOR.PERSON_NUMBER AS PERSON_NUMBER, PER_INFOR.PHONE_NUMBER AS PHONE_NUMBER, PER_INFOR.EMAIL_ADDRESS AS EMAIL_ADDRESS, PER_INFOR.JOB_TITLE AS JOB_TITLE, PER_INFOR.DEPARTMENT AS DEPARTMENT, PER_INFOR.PAP_START_DATE AS START_DATE, PER_INFOR.PAP_EFFECTIVE_END_DATE AS EFFECTIVE_END_DATE, ( ROW_NUMBER() OVER(PARTITION BY GPP.ID, GPP.PERSONNEL_ID ORDER BY PER_INFOR.ASSIGNMENT_ID ) ) AS UNIQ_ASSIGN_COUNT FROM GMS_AWARD_PERSONNEL GPP, ( SELECT PAA.ASSIGNMENT_ID AS ASSIGNMENT_ID, PAA.PERSON_ID AS PERSON_ID, PPN.DISPLAY_NAME AS DISPLAY_NAME, PAP.PERSON_NUMBER AS PERSON_NUMBER, PPH.PHONE_NUMBER AS PHONE_NUMBER, PEA.EMAIL_ADDRESS AS EMAIL_ADDRESS, PPJ.NAME AS JOB_TITLE, HRO.NAME AS DEPARTMENT, PAP.START_DATE AS PAP_START_DATE, PAA.EFFECTIVE_START_DATE AS PAA_EFFECTIVE_START_DATE, PAA.EFFECTIVE_END_DATE AS PAA_EFFECTIVE_END_DATE, PAP.EFFECTIVE_START_DATE AS PAP_EFFECTIVE_START_DATE, PAP.EFFECTIVE_END_DATE AS PAP_EFFECTIVE_END_DATE, PPN.EFFECTIVE_START_DATE AS PPN_EFFECTIVE_START_DATE, PPN.EFFECTIVE_END_DATE AS PPN_EFFECTIVE_END_DATE, HRO.EFFECTIVE_START_DATE AS HRO_EFFECTIVE_START_DATE, HRO.EFFECTIVE_END_DATE AS HRO_EFFECTIVE_END_DATE, PPJ.EFFECTIVE_START_DATE AS PPJ_EFFECTIVE_START_DATE, PPJ.EFFECTIVE_END_DATE AS PPJ_EFFECTIVE_END_DATE, HRO.ORGANIZATION_ID, PPJ.JOB_ID, PPH.PHONE_ID, PEA.EMAIL_ADDRESS_ID, PAP.PERSON_ID AS PAP_PERSON_ID, PPN.PERSON_ID AS PPN_PERSON_ID FROM PER_ALL_ASSIGNMENTS_F PAA, PER_ALL_PEOPLE_F PAP, PER_PERSON_NAMES_F_V PPN, PER_PHONES_V PPH, PER_DEPARTMENTS HRO, PER_JOBS_F_VL PPJ, PER_EMAIL_ADDRESSES_V PEA WHERE PAA.PERSON_ID = PAP.PERSON_ID AND PAA.PERSON_ID = PPN.PERSON_ID AND PAP.PRIMARY_PHONE_ID = PPH.PHONE_ID (+) AND PAP.PRIMARY_EMAIL_ID = PEA.EMAIL_ADDRESS_ID (+) AND PAA.ORGANIZATION_ID = HRO.ORGANIZATION_ID AND PAA.JOB_ID = PPJ.JOB_ID (+) AND PAA.PRIMARY_FLAG = 'Y' AND PAA.ASSIGNMENT_TYPE IN ( 'E', 'C' ) AND ( PAA.ASSIGNMENT_STATUS_TYPE = 'ACTIVE' OR PAA.ASSIGNMENT_STATUS_TYPE = 'SUSPENDED' ) ) PER_INFOR WHERE GPP.PERSONNEL_ID = PER_INFOR.PERSON_ID AND TRUNC(GPP.START_DATE) BETWEEN TRUNC(PAA_EFFECTIVE_START_DATE) AND TRUNC(PAA_EFFECTIVE_END_DATE) AND GREATEST(SYSDATE, GPP.START_DATE) BETWEEN TRUNC(PAP_EFFECTIVE_START_DATE) AND TRUNC(PAP_EFFECTIVE_END_DATE) AND GREATEST(SYSDATE, GPP.START_DATE) BETWEEN TRUNC(PPN_EFFECTIVE_START_DATE) AND TRUNC(PPN_EFFECTIVE_END_DATE) AND TRUNC(GPP.START_DATE) BETWEEN TRUNC(HRO_EFFECTIVE_START_DATE) AND TRUNC(HRO_EFFECTIVE_END_DATE) AND TRUNC(GPP.START_DATE) BETWEEN TRUNC(NVL(PPJ_EFFECTIVE_START_DATE, GPP.START_DATE)) AND TRUNC(NVL(PPJ_EFFECTIVE_END_DATE, GPP. START_DATE))

SQL_Statement

SELECT

GPP.ID AS ID,

GPP.AWARD_ID AS AWARD_ID,

GPP.PERSONNEL_ID AS PERSON_ID,

GPP.ROLE_ID AS ROLE_ID,

PER_INFOR.DISPLAY_NAME AS NAME,

PER_INFOR.PERSON_NUMBER AS PERSON_NUMBER,

PER_INFOR.PHONE_NUMBER AS PHONE_NUMBER,

PER_INFOR.EMAIL_ADDRESS AS EMAIL_ADDRESS,

PER_INFOR.JOB_TITLE AS JOB_TITLE,

PER_INFOR.DEPARTMENT AS DEPARTMENT,

PER_INFOR.PAP_START_DATE AS START_DATE,

PER_INFOR.PAP_EFFECTIVE_END_DATE AS EFFECTIVE_END_DATE,

( ROW_NUMBER()

OVER(PARTITION BY GPP.ID, GPP.PERSONNEL_ID

ORDER BY

PER_INFOR.ASSIGNMENT_ID

) ) AS UNIQ_ASSIGN_COUNT

FROM

GMS_AWARD_PERSONNEL GPP,

(

SELECT

PAA.ASSIGNMENT_ID AS ASSIGNMENT_ID,

PAA.PERSON_ID AS PERSON_ID,

PPN.DISPLAY_NAME AS DISPLAY_NAME,

PAP.PERSON_NUMBER AS PERSON_NUMBER,

PPH.PHONE_NUMBER AS PHONE_NUMBER,

PEA.EMAIL_ADDRESS AS EMAIL_ADDRESS,

PPJ.NAME AS JOB_TITLE,

HRO.NAME AS DEPARTMENT,

PAP.START_DATE AS PAP_START_DATE,

PAA.EFFECTIVE_START_DATE AS PAA_EFFECTIVE_START_DATE,

PAA.EFFECTIVE_END_DATE AS PAA_EFFECTIVE_END_DATE,

PAP.EFFECTIVE_START_DATE AS PAP_EFFECTIVE_START_DATE,

PAP.EFFECTIVE_END_DATE AS PAP_EFFECTIVE_END_DATE,

PPN.EFFECTIVE_START_DATE AS PPN_EFFECTIVE_START_DATE,

PPN.EFFECTIVE_END_DATE AS PPN_EFFECTIVE_END_DATE,

HRO.EFFECTIVE_START_DATE AS HRO_EFFECTIVE_START_DATE,

HRO.EFFECTIVE_END_DATE AS HRO_EFFECTIVE_END_DATE,

PPJ.EFFECTIVE_START_DATE AS PPJ_EFFECTIVE_START_DATE,

PPJ.EFFECTIVE_END_DATE AS PPJ_EFFECTIVE_END_DATE,

HRO.ORGANIZATION_ID,

PPJ.JOB_ID,

PPH.PHONE_ID,

PEA.EMAIL_ADDRESS_ID,

PAP.PERSON_ID AS PAP_PERSON_ID,

PPN.PERSON_ID AS PPN_PERSON_ID

FROM

PER_ALL_ASSIGNMENTS_F PAA,

PER_ALL_PEOPLE_F PAP,

PER_PERSON_NAMES_F_V PPN,

PER_PHONES_V PPH,

PER_DEPARTMENTS HRO,

PER_JOBS_F_VL PPJ,

PER_EMAIL_ADDRESSES_V PEA

WHERE

PAA.PERSON_ID = PAP.PERSON_ID

AND PAA.PERSON_ID = PPN.PERSON_ID

AND PAP.PRIMARY_PHONE_ID = PPH.PHONE_ID (+)

AND PAP.PRIMARY_EMAIL_ID = PEA.EMAIL_ADDRESS_ID (+)

AND PAA.ORGANIZATION_ID = HRO.ORGANIZATION_ID

AND PAA.JOB_ID = PPJ.JOB_ID (+)

AND PAA.PRIMARY_FLAG = 'Y'

AND PAA.ASSIGNMENT_TYPE IN ( 'E', 'C' )

AND ( PAA.ASSIGNMENT_STATUS_TYPE = 'ACTIVE'

OR PAA.ASSIGNMENT_STATUS_TYPE = 'SUSPENDED' )

) PER_INFOR

WHERE

GPP.PERSONNEL_ID = PER_INFOR.PERSON_ID

AND TRUNC(GPP.START_DATE) BETWEEN TRUNC(PAA_EFFECTIVE_START_DATE) AND TRUNC(PAA_EFFECTIVE_END_DATE)

AND GREATEST(SYSDATE, GPP.START_DATE) BETWEEN TRUNC(PAP_EFFECTIVE_START_DATE) AND TRUNC(PAP_EFFECTIVE_END_DATE)

AND GREATEST(SYSDATE, GPP.START_DATE) BETWEEN TRUNC(PPN_EFFECTIVE_START_DATE) AND TRUNC(PPN_EFFECTIVE_END_DATE)

AND TRUNC(GPP.START_DATE) BETWEEN TRUNC(HRO_EFFECTIVE_START_DATE) AND TRUNC(HRO_EFFECTIVE_END_DATE)

AND TRUNC(GPP.START_DATE) BETWEEN TRUNC(NVL(PPJ_EFFECTIVE_START_DATE, GPP.START_DATE)) AND TRUNC(NVL(PPJ_EFFECTIVE_END_DATE, GPP.

START_DATE))