1. Tables and Views for HCM
  2. PER_PERSON_LIST_V

PER_PERSON_LIST_V

Details

  • Schema: FUSION

  • Object owner: PER

  • Object type: VIEW

Columns

Name

ID

VALUE

DESCRIPTION

Query

SQL_Statement

SELECT to_char(pn.person_id) AS Id,pn.list_name AS Value,

'('||p.person_number ||') '|| '('||l.meaning ||') '|| case when j.Name is not null then j.NAME || ' - ' end || case when a.assignment_number<>a.assignment_name then a.assignment_number||' - '||a.assignment_name else a.assignment_number end|| decode(pn.legislation_code,null,'',' ('||pn.legislation_code||')') Description

FROM per_person_names_f pn, LATERAL(select * from (

select person_id,assignment_number,assignment_name,job_id,assignment_status_type,

row_number() over (partition by person_id order by decode(assignment_status_type,'ACTIVE','1','SUSPENDED','2','3'||assignment_status_type),effective_end_date desc, effective_start_date desc, rowid ) rn

FROM PER_ALL_ASSIGNMENTS_M a WHERE pn.person_id = a.person_id and primary_flag='Y' and ASSIGNMENT_TYPE in('E','C') AND trunc(sysdate) BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE AND EFFECTIVE_LATEST_CHANGE = 'Y'

) where rn = 1 ) a, per_all_people_f p, PER_JOBS_F_VL j,HCM_LOOKUPS l

WHERE pn.PERSON_ID= p.PERSON_ID AND pn.name_type = 'GLOBAL' AND (trunc(sysdate) BETWEEN pn.EFFECTIVE_START_DATE AND pn.EFFECTIVE_END_DATE) AND (trunc(sysdate) BETWEEN p.EFFECTIVE_START_DATE AND p.EFFECTIVE_END_DATE)

AND a.JOB_ID = j.JOB_ID(+) AND (trunc(sysdate) BETWEEN j.EFFECTIVE_START_DATE(+) AND j.EFFECTIVE_END_DATE(+))

AND l.lookup_type(+) ='PER_ASS_SYS_STATUS' AND a.assignment_status_type=l.lookup_code(+)