Product: | GHR - US Federal Human Resources |
---|---|
Description: | Contains the Person Summary information. |
Implementation/DBA Data: | APPS.GHR_PERSON_SUMMARY_V |
SELECT PER.ROWID ROW_ID
, PER.PERSON_ID
, PER.FULL_NAME
, PER.NATIONAL_IDENTIFIER
, PER.DATE_OF_BIRTH
, PER.EMPLOYEE_NUMBER
, PER.APPLICANT_NUMBER
, PER.SEX
, HL_SEX.MEANING D_SEX
, PER.BUSINESS_GROUP_ID
, PER.NPW_NUMBER
, PER.PERSON_TYPE_ID
, ASG.ASSIGNMENT_ID
, AST.PER_SYSTEM_STATUS
, AST.USER_STATUS
, ASG.POSITION_ID
, ASG.LOCATION_ID
, ASG.JOB_ID
FROM HR_LOOKUPS HL_SEX
, PER_ASSIGNMENT_STATUS_TYPES AST
, PER_ASSIGNMENTS_F ASG
, PER_PEOPLE_F PER
, FND_SESSIONS SES
WHERE USERENV('SESSIONID') = SES.SESSION_ID
AND HL_SEX.LOOKUP_TYPE (+) = 'SEX'
AND PER.SEX = HL_SEX.LOOKUP_CODE (+)
AND SES.EFFECTIVE_DATE BETWEEN PER.EFFECTIVE_START_DATE
AND PER.EFFECTIVE_END_DATE
AND ASG.PERSON_ID = PER.PERSON_ID
AND SES.EFFECTIVE_DATE BETWEEN ASG.EFFECTIVE_START_DATE
AND ASG.EFFECTIVE_END_DATE
AND AST.ASSIGNMENT_STATUS_TYPE_ID = ASG.ASSIGNMENT_STATUS_TYPE_ID
AND AST.PER_SYSTEM_STATUS IN ('ACTIVE_ASSIGN'
, 'SUSP_ASSIGN'
, 'ACTIVE_CWK')
AND ASG.PRIMARY_FLAG = 'Y'
AND ASG.ASSIGNMENT_TYPE IN ('E'
, 'C')