PJC_PERSON_ASSIGNMENTS

Details

Schema: FUSION
Object owner: PJC
Object type: VIEW

Columns

Name
ASSIGNMENT_ID PERSON_ID PERSON_NUMBER DISPLAY_NAME LIST_NAME ASS_EFFECTIVE_START_DATE ASS_EFFECTIVE_END_DATE ASSIGNMENT_NUMBER ASSIGNMENT_NAME ASSIGNMENT_TYPE ASSIGNMENT_STATUS_TYPE PRIMARY_FLAG BU_ID

Name

ASSIGNMENT_ID

PERSON_ID

PERSON_NUMBER

DISPLAY_NAME

LIST_NAME

ASS_EFFECTIVE_START_DATE

ASS_EFFECTIVE_END_DATE

ASSIGNMENT_NUMBER

ASSIGNMENT_NAME

ASSIGNMENT_TYPE

ASSIGNMENT_STATUS_TYPE

PRIMARY_FLAG

BU_ID

Query

SQL_Statement
SELECT ASSIGNMENTDPEO.ASSIGNMENT_ID AS ASSIGNMENT_ID, PERSONPEO.PERSON_ID AS PERSON_ID, PERSONDPEO.PERSON_NUMBER AS PERSON_NUMBER, PERSONNAMEDPEO.DISPLAY_NAME AS DISPLAY_NAME, PERSONNAMEDPEO.LIST_NAME AS LIST_NAME, ASSIGNMENTDPEO.EFFECTIVE_START_DATE AS ASS_EFFECTIVE_START_DATE, ASSIGNMENTDPEO.EFFECTIVE_END_DATE AS ASS_EFFECTIVE_END_DATE, ASSIGNMENTDPEO.ASSIGNMENT_NUMBER AS ASSIGNMENT_NUMBER, ASSIGNMENTDPEO.ASSIGNMENT_NAME AS ASSIGNMENT_NAME, ASSIGNMENTDPEO.ASSIGNMENT_TYPE AS ASSIGNMENT_TYPE, ASSIGNMENTDPEO.ASSIGNMENT_STATUS_TYPE AS ASSIGNMENT_STATUS_TYPE, ASSIGNMENTDPEO.PRIMARY_FLAG AS PRIMARY_FLAG, ASSIGNMENTDPEO.BUSINESS_UNIT_ID AS BU_ID FROM PER_PERSONS PERSONPEO, PER_ALL_PEOPLE_F PERSONDPEO, PER_PERSON_NAMES_F_V PERSONNAMEDPEO, PER_ALL_ASSIGNMENTS_M ASSIGNMENTDPEO WHERE ( PERSONPEO.PERSON_ID = PERSONDPEO.PERSON_ID ) AND ( PERSONPEO.PERSON_ID = PERSONNAMEDPEO.PERSON_ID ) AND ( PERSONPEO.PERSON_ID = ASSIGNMENTDPEO.PERSON_ID ) AND ( ASSIGNMENTDPEO.WORK_TERMS_ASSIGNMENT_ID IS NOT NULL ) AND ( TRUNC(SYSDATE) BETWEEN PERSONDPEO.EFFECTIVE_START_DATE AND PERSONDPEO.EFFECTIVE_END_DATE ) AND ( TRUNC(SYSDATE) BETWEEN PERSONNAMEDPEO.EFFECTIVE_START_DATE AND PERSONNAMEDPEO.EFFECTIVE_END_DATE ) AND ( TRUNC(SYSDATE) BETWEEN ASSIGNMENTDPEO.EFFECTIVE_START_DATE AND ASSIGNMENTDPEO.EFFECTIVE_END_DATE ) AND ( ASSIGNMENTDPEO.EFFECTIVE_LATEST_CHANGE = 'Y') AND EXISTS (SELECT 1 FROM PJC_ALLOC_RUNS_ALL A WHERE ASSIGNMENTDPEO.ASSIGNMENT_ID = A.LD_ASSIGNMENT_ID) ORDER BY PERSONNAMEDPEO.DISPLAY_NAME

SQL_Statement

SELECT

ASSIGNMENTDPEO.ASSIGNMENT_ID AS ASSIGNMENT_ID,

PERSONPEO.PERSON_ID AS PERSON_ID,

PERSONDPEO.PERSON_NUMBER AS PERSON_NUMBER,

PERSONNAMEDPEO.DISPLAY_NAME AS DISPLAY_NAME,

PERSONNAMEDPEO.LIST_NAME AS LIST_NAME,

ASSIGNMENTDPEO.EFFECTIVE_START_DATE AS ASS_EFFECTIVE_START_DATE,

ASSIGNMENTDPEO.EFFECTIVE_END_DATE AS ASS_EFFECTIVE_END_DATE,

ASSIGNMENTDPEO.ASSIGNMENT_NUMBER AS ASSIGNMENT_NUMBER,

ASSIGNMENTDPEO.ASSIGNMENT_NAME AS ASSIGNMENT_NAME,

ASSIGNMENTDPEO.ASSIGNMENT_TYPE AS ASSIGNMENT_TYPE,

ASSIGNMENTDPEO.ASSIGNMENT_STATUS_TYPE AS ASSIGNMENT_STATUS_TYPE,

ASSIGNMENTDPEO.PRIMARY_FLAG AS PRIMARY_FLAG,

ASSIGNMENTDPEO.BUSINESS_UNIT_ID AS BU_ID

FROM

PER_PERSONS PERSONPEO,

PER_ALL_PEOPLE_F PERSONDPEO,

PER_PERSON_NAMES_F_V PERSONNAMEDPEO,

PER_ALL_ASSIGNMENTS_M ASSIGNMENTDPEO

WHERE ( PERSONPEO.PERSON_ID = PERSONDPEO.PERSON_ID )

AND ( PERSONPEO.PERSON_ID = PERSONNAMEDPEO.PERSON_ID )

AND ( PERSONPEO.PERSON_ID = ASSIGNMENTDPEO.PERSON_ID )

AND ( ASSIGNMENTDPEO.WORK_TERMS_ASSIGNMENT_ID IS NOT NULL )

AND ( TRUNC(SYSDATE) BETWEEN PERSONDPEO.EFFECTIVE_START_DATE AND PERSONDPEO.EFFECTIVE_END_DATE )

AND ( TRUNC(SYSDATE) BETWEEN PERSONNAMEDPEO.EFFECTIVE_START_DATE AND PERSONNAMEDPEO.EFFECTIVE_END_DATE )

AND ( TRUNC(SYSDATE) BETWEEN ASSIGNMENTDPEO.EFFECTIVE_START_DATE AND ASSIGNMENTDPEO.EFFECTIVE_END_DATE )

AND ( ASSIGNMENTDPEO.EFFECTIVE_LATEST_CHANGE = 'Y')

AND EXISTS (SELECT 1 FROM PJC_ALLOC_RUNS_ALL A WHERE ASSIGNMENTDPEO.ASSIGNMENT_ID = A.LD_ASSIGNMENT_ID)

ORDER BY PERSONNAMEDPEO.DISPLAY_NAME