select
(select PPF.PERSON_NUMBER from PER_ALL_PEOPLE_F PPF
where 1=1
and PPF.PERSON_ID="PER_PERSON_NAMES_F_V"."PERSON_ID"
and trunc(sysdate) between PPF."EFFECTIVE_START_DATE" and PPF."EFFECTIVE_END_DATE"
and rownum=1) as "PERSON_NUMBER",
to_char( "PER_PERSON_NAMES_F_V"."PERSON_ID" ) as "PERSON_ID",
( select source_system_id from fusion.HRC_INTEGRATION_KEY_MAP
where 1=1
and object_name='Person'
and source_system_owner in ('WORKDAY')
and surrogate_id="PER_PERSON_NAMES_F_V"."PERSON_ID") PER_SOURCE_SYSTEM_ID,
"PER_PERSON_NAMES_F_V"."FIRST_NAME" as "FIRST_NAME",
"PER_PERSON_NAMES_F_V"."LAST_NAME" as "LAST_NAME",
"PER_PERSON_NAMES_F_V"."DISPLAY_NAME" as "DISPLAY_NAME",
(select PPF.PERSON_NUMBER from PER_ALL_PEOPLE_F PPF
,PER_PERSON_NAMES_F_V M1
,PER_ASSIGNMENT_SUPERVISORS_F_V PA
where 1=1
and PPF.PERSON_ID=M1.PERSON_ID
and trunc(sysdate) between PPF."EFFECTIVE_START_DATE" and PPF."EFFECTIVE_END_DATE"
and M1.person_id=PA.manager_id
and pa.person_id="PER_PERSON_NAMES_F_V"."PERSON_ID"
and pa.assignment_id="PER_ALL_ASSIGNMENTS_M"."ASSIGNMENT_ID"
and trunc(sysdate) between M1."EFFECTIVE_START_DATE" and M1."EFFECTIVE_END_DATE"
and rownum=1) as "MANAGER_ID",
(select FULL_NAME from PER_PERSON_NAMES_F_V M1,
PER_ASSIGNMENT_SUPERVISORS_F_V PA
where 1=1
and M1.person_id=PA.manager_id
and pa.person_id="PER_PERSON_NAMES_F_V"."PERSON_ID"
and pa.assignment_id="PER_ALL_ASSIGNMENTS_M"."ASSIGNMENT_ID"
and trunc(sysdate) between M1."EFFECTIVE_START_DATE" and M1."EFFECTIVE_END_DATE"
and rownum=1) as "MANAGER_NAME",
( select source_system_id from fusion.HRC_INTEGRATION_KEY_MAP H,
PER_ASSIGNMENT_SUPERVISORS_F_V PA
where 1=1
and H.object_name='AssignmentSupervisor'
and H.source_system_owner in ('WORKDAY')
and H.surrogate_id=pa.assignment_supervisor_id
and pa.person_id="PER_PERSON_NAMES_F_V"."PERSON_ID"
and pa.assignment_id="PER_ALL_ASSIGNMENTS_M"."ASSIGNMENT_ID"
) as "ASS_SUPVSR_SOURCE_SYSTEM_ID",
( select source_system_id from fusion.HRC_INTEGRATION_KEY_MAP H
where 1=1
and H.object_name='Assignment'
and H.source_system_owner in ('WORKDAY')
and H.surrogate_id="PER_ALL_ASSIGNMENTS_M".work_terms_assignment_id) as "WK_TERM_SOURCE_SYSTEMID",
"PER_PERSON_NAMES_F_V"."CREATED_BY" as "CREATED_BY",
"PER_PERSON_NAMES_F_V"."CREATION_DATE" as "CREATION_DATE",
"PER_USERS"."USERNAME" as "USERNAME",
(select EMAIL_ADDRESS from PER_EMAIL_ADDRESSES_V PEA
where 1=1
and PEA.PERSON_ID="PER_PERSON_NAMES_F_V"."PERSON_ID"
--and PEA.EMAIL_ADDRESS_ID="PER_PERSON_NAMES_F_V".PRIMARY_ADDRESS_ID
and TRUNC(SYSDATE) between DATE_FROM and NVL(DATE_TO,SYSDATE+1)
and rownum=1) as "EMAIL_ADDRESS",
"PER_PERSON_NAMES_F_V"."EFFECTIVE_START_DATE" as "EFFECTIVE_START_DATE",
"HR_ORGANIZATION_UNITS_1"."NAME" as "BUSINESS_UNIT",
to_char("PER_ALL_ASSIGNMENTS_M"."ASSIGNMENT_ID") as "ASSIGNMENT_ID",
to_char("PER_ALL_ASSIGNMENTS_M"."ASSIGNMENT_NUMBER") as "ASSIGNMENT_NUMBER",
( select source_system_id from fusion.HRC_INTEGRATION_KEY_MAP
where 1=1
and object_name='Assignment'
and source_system_owner in ('WORKDAY')
and surrogate_id="PER_ALL_ASSIGNMENTS_M"."ASSIGNMENT_ID") ASS_SOURCE_SYSTEM_ID,
"PER_ALL_ASSIGNMENTS_M"."EFFECTIVE_START_DATE" as "ASSIG_EFFECTIVE_START_DATE",
"PER_ALL_ASSIGNMENTS_M"."ACTION_CODE" as "ACTION_CODE",
"HR_ORGANIZATION_UNITS_2"."NAME" as "LEGAL_ENTITY_CODE",
"PER_JOBS_F_VL"."JOB_CODE" as "JOB_CODE",
"PER_JOBS_F_VL"."NAME" as "JOB_NAME",
"HR_ORGANIZATION_UNITS"."NAME" as "DEPARTMENT_NAME",
"PER_LOCATION_DETAILS_F_VL"."LOCATION_CODE" as "LOCATION_CODE",
"PER_ALL_ASSIGNMENTS_M"."ASSIGNMENT_STATUS_TYPE" as "ASSIGNMENT_STATUS_TYPE",
"PER_PERSON_ADDRESSES_V"."ADDRESS_LINE_1" as "ADDRESS_LINE_1",
"PER_PERSON_ADDRESSES_V"."POSTAL_CODE" as "POSTAL_CODE",
"PER_PERSON_ADDRESSES_V"."REGION_2" as "REGION_2",
"PER_PERSON_ADDRESSES_V"."TOWN_OR_CITY" as "TOWN_OR_CITY",
"PER_PERSON_ADDRESSES_V"."COUNTRY" as "COUNTRY",
"PER_ALL_ASSIGNMENTS_M"."MANAGER_FLAG" as "MANAGER_FLAG",
"PER_PERIODS_OF_SERVICE_V"."ACTUAL_TERMINATION_DATE" as "ACTUAL_TERMINATION_DATE",
"PER_PERIODS_OF_SERVICE_V"."ACCEPTED_TERMINATION_DATE" as "ACCEPTED_TERMINATION_DATE",
"PER_ALL_ASSIGNMENTS_M"."ASS_ATTRIBUTE2" as "TIMEKEEPER_CONTEXTUAL",
( select source_system_id from fusion.HRC_INTEGRATION_KEY_MAP H
where 1=1
and H.object_name='PeriodOfService'
and H.source_system_owner in ('WORKDAY')
and H.surrogate_id="PER_ALL_ASSIGNMENTS_M".PERIOD_OF_SERVICE_ID) as "WK_RELATION_SOURCE_SYSTEMID",
(select source_system_id
from fusion.HRC_INTEGRATION_KEY_MAP H
WHERE 1=1
and h.object_name ='PersonName'
and h.surrogate_id="PER_PERSON_NAMES_F_V".person_name_id
and h.SOURCE_SYSTEM_OWNER in ('WORKDAY')) as "PER_NAME_SOURCE_SYSTEM_ID",
(select source_system_id
from fusion.HRC_INTEGRATION_KEY_MAP H
WHERE 1=1
and h.object_name = 'Address'
and h.surrogate_id=PER_PERSON_ADDRESSES_V.address_id
and h.SOURCE_SYSTEM_OWNER in ('WORKDAY')) as "ADDRESS_SOURCE_SYSTEM_ID",
(select H.source_system_id
from fusion.HRC_INTEGRATION_KEY_MAP H
,PER_EMAIL_ADDRESSES_V PER_EMAIL_ADDRESSES_V
WHERE 1=1
and h.object_name = 'EmailAddress'
and h.SOURCE_SYSTEM_OWNER in ('WORKDAY')
and h.surrogate_id=PER_EMAIL_ADDRESSES_V.email_address_id
and PER_EMAIL_ADDRESSES_V.PERSON_ID="PER_PERSON_NAMES_F_V"."PERSON_ID"
and TRUNC(SYSDATE) between DATE_FROM and NVL(DATE_TO,SYSDATE+1)
and rownum=1
) as "EMAIL_SOURCE_SYSTEM_ID",
(select assignment_number
from per_all_assignments_f aaf
where 1=1
and aaf.assignment_id="PER_ALL_ASSIGNMENTS_M".WORK_TERMS_ASSIGNMENT_ID
and rownum=1) as "WK_TERM_ASSIGNMENT_NUM",
"PER_PERSON_ADDRESSES_V"."ADDRESS_LINE_2" as "ADDRESS_LINE_2",
"PER_ALL_ASSIGNMENTS_M"."ASS_ATTRIBUTE1" as "TIMEKEEPER",
"HR_ORGANIZATION_UNITS"."ORGANIZATION_ID" as "DEPARTMENT_ID"
from "HR_ORGANIZATION_UNITS" "HR_ORGANIZATION_UNITS_2",
"HR_ORGANIZATION_UNITS" "HR_ORGANIZATION_UNITS_1",
"HR_ORGANIZATION_UNITS" "HR_ORGANIZATION_UNITS",
"PER_PERSON_TYPES_VL" "PER_PERSON_TYPES_VL",
"PER_ASSIGNMENT_STATUS_TYPES_VL" "PER_ASSIGNMENT_STATUS_TYPES_VL",
"PER_PERSON_ADDRESSES_V" "PER_PERSON_ADDRESSES_V",
"PER_GRADES_F_VL" "PER_GRADES_F_VL",
"PER_JOBS_F_VL" "PER_JOBS_F_VL",
"HR_ALL_POSITIONS_F_VL" "HR_ALL_POSITIONS_F_VL",
"PER_LOCATION_DETAILS_F_VL" "PER_LOCATION_DETAILS_F_VL",
"PER_ALL_ASSIGNMENTS_M" "PER_ALL_ASSIGNMENTS_M" ,
"PER_USERS" "PER_USERS",
"PER_PERSON_NAMES_F_V" "PER_PERSON_NAMES_F_V",
"PER_PERIODS_OF_SERVICE_V" "PER_PERIODS_OF_SERVICE_V"
where 1=1
and "PER_PERSON_NAMES_F_V"."PERSON_ID"="PER_USERS"."PERSON_ID"(+)
and "PER_PERSON_NAMES_F_V"."EFFECTIVE_START_DATE"=( Select max(P.EFFECTIVE_START_DATE)
from PER_PERSON_NAMES_F_V P
where 1=1
and P.PERSON_ID="PER_PERSON_NAMES_F_V"."PERSON_ID")
and "PER_PERSON_NAMES_F_V"."PERSON_ID"="PER_ALL_ASSIGNMENTS_M"."PERSON_ID"
and "PER_ALL_ASSIGNMENTS_M"."PERSON_ID"="PER_PERIODS_OF_SERVICE_V"."PERSON_ID"(+)
and "PER_ALL_ASSIGNMENTS_M"."PERIOD_OF_SERVICE_ID"="PER_PERIODS_OF_SERVICE_V"."PERIOD_OF_SERVICE_ID"(+)
and "PER_ALL_ASSIGNMENTS_M"."ASSIGNMENT_TYPE" in ('E','C')
and "PER_ALL_ASSIGNMENTS_M"."LOCATION_ID"="PER_LOCATION_DETAILS_F_VL"."LOCATION_ID"(+)
and "PER_ALL_ASSIGNMENTS_M"."POSITION_ID"="HR_ALL_POSITIONS_F_VL"."POSITION_ID"(+)
and "PER_ALL_ASSIGNMENTS_M"."JOB_ID"="PER_JOBS_F_VL"."JOB_ID"(+)
and "PER_ALL_ASSIGNMENTS_M"."GRADE_ID"="PER_GRADES_F_VL"."GRADE_ID"(+)
and "PER_PERSON_NAMES_F_V"."PERSON_ID"="PER_PERSON_ADDRESSES_V"."PERSON_ID"(+)
and trunc(sysdate) between "PER_PERSON_ADDRESSES_V"."EFFECTIVE_START_DATE" and NVL("PER_PERSON_ADDRESSES_V"."EFFECTIVE_END_DATE",SYSDATE+1)
and "PER_ALL_ASSIGNMENTS_M"."ASSIGNMENT_STATUS_TYPE_ID"="PER_ASSIGNMENT_STATUS_TYPES_VL"."ASSIGNMENT_STATUS_TYPE_ID"
and "PER_ALL_ASSIGNMENTS_M"."PERSON_TYPE_ID"="PER_PERSON_TYPES_VL"."PERSON_TYPE_ID"
and "PER_ALL_ASSIGNMENTS_M"."ORGANIZATION_ID"="HR_ORGANIZATION_UNITS"."ORGANIZATION_ID"(+)
and "PER_ALL_ASSIGNMENTS_M"."LEGAL_ENTITY_ID"="HR_ORGANIZATION_UNITS_2"."ORGANIZATION_ID"
and "PER_ALL_ASSIGNMENTS_M"."BUSINESS_UNIT_ID"="HR_ORGANIZATION_UNITS_1"."ORGANIZATION_ID"(+)
and trunc(sysdate) between "PER_ALL_ASSIGNMENTS_M"."EFFECTIVE_START_DATE" and "PER_ALL_ASSIGNMENTS_M"."EFFECTIVE_END_DATE"
and "PER_ALL_ASSIGNMENTS_M"."EFFECTIVE_END_DATE" between "HR_ORGANIZATION_UNITS"."DATE_FROM"(+) and "HR_ORGANIZATION_UNITS"."DATE_TO"(+)
and "PER_ALL_ASSIGNMENTS_M"."EFFECTIVE_END_DATE" between "HR_ORGANIZATION_UNITS_1"."DATE_FROM"(+) and "HR_ORGANIZATION_UNITS_1"."DATE_TO"(+)
and "PER_ALL_ASSIGNMENTS_M"."EFFECTIVE_END_DATE" between "HR_ORGANIZATION_UNITS_2"."DATE_FROM" and "HR_ORGANIZATION_UNITS_2"."DATE_TO"
and "PER_ALL_ASSIGNMENTS_M"."EFFECTIVE_END_DATE" between "HR_ALL_POSITIONS_F_VL"."EFFECTIVE_START_DATE"(+) and "HR_ALL_POSITIONS_F_VL"."EFFECTIVE_END_DATE"(+)
and "PER_ALL_ASSIGNMENTS_M"."EFFECTIVE_END_DATE" between "PER_JOBS_F_VL"."EFFECTIVE_START_DATE"(+) and "PER_JOBS_F_VL"."EFFECTIVE_END_DATE"(+)
and "PER_ALL_ASSIGNMENTS_M"."EFFECTIVE_END_DATE" between "PER_GRADES_F_VL"."EFFECTIVE_START_DATE"(+) and "PER_GRADES_F_VL"."EFFECTIVE_END_DATE"(+)
--and "PER_PERSON_NAMES_F_V"."PERSON_ID" --in('300000062940645','300000063542903','300000072973100')
and "PER_ALL_ASSIGNMENTS_M".assignment_number=(select max(assignment_number) from PER_ALL_ASSIGNMENTS_M PAM
where 1=1
and PAM.person_id="PER_ALL_ASSIGNMENTS_M"."PERSON_ID"
and PAM."ASSIGNMENT_TYPE" in ('E','C')
and trunc(sysdate) between PAM.EFFECTIVE_START_DATE and PAM.EFFECTIVE_END_DATE
)
Order by to_char( "PER_PERSON_NAMES_F_V"."PERSON_ID" )
(select PPF.PERSON_NUMBER from PER_ALL_PEOPLE_F PPF
where 1=1
and PPF.PERSON_ID="PER_PERSON_NAMES_F_V"."PERSON_ID"
and trunc(sysdate) between PPF."EFFECTIVE_START_DATE" and PPF."EFFECTIVE_END_DATE"
and rownum=1) as "PERSON_NUMBER",
to_char( "PER_PERSON_NAMES_F_V"."PERSON_ID" ) as "PERSON_ID",
( select source_system_id from fusion.HRC_INTEGRATION_KEY_MAP
where 1=1
and object_name='Person'
and source_system_owner in ('WORKDAY')
and surrogate_id="PER_PERSON_NAMES_F_V"."PERSON_ID") PER_SOURCE_SYSTEM_ID,
"PER_PERSON_NAMES_F_V"."FIRST_NAME" as "FIRST_NAME",
"PER_PERSON_NAMES_F_V"."LAST_NAME" as "LAST_NAME",
"PER_PERSON_NAMES_F_V"."DISPLAY_NAME" as "DISPLAY_NAME",
(select PPF.PERSON_NUMBER from PER_ALL_PEOPLE_F PPF
,PER_PERSON_NAMES_F_V M1
,PER_ASSIGNMENT_SUPERVISORS_F_V PA
where 1=1
and PPF.PERSON_ID=M1.PERSON_ID
and trunc(sysdate) between PPF."EFFECTIVE_START_DATE" and PPF."EFFECTIVE_END_DATE"
and M1.person_id=PA.manager_id
and pa.person_id="PER_PERSON_NAMES_F_V"."PERSON_ID"
and pa.assignment_id="PER_ALL_ASSIGNMENTS_M"."ASSIGNMENT_ID"
and trunc(sysdate) between M1."EFFECTIVE_START_DATE" and M1."EFFECTIVE_END_DATE"
and rownum=1) as "MANAGER_ID",
(select FULL_NAME from PER_PERSON_NAMES_F_V M1,
PER_ASSIGNMENT_SUPERVISORS_F_V PA
where 1=1
and M1.person_id=PA.manager_id
and pa.person_id="PER_PERSON_NAMES_F_V"."PERSON_ID"
and pa.assignment_id="PER_ALL_ASSIGNMENTS_M"."ASSIGNMENT_ID"
and trunc(sysdate) between M1."EFFECTIVE_START_DATE" and M1."EFFECTIVE_END_DATE"
and rownum=1) as "MANAGER_NAME",
( select source_system_id from fusion.HRC_INTEGRATION_KEY_MAP H,
PER_ASSIGNMENT_SUPERVISORS_F_V PA
where 1=1
and H.object_name='AssignmentSupervisor'
and H.source_system_owner in ('WORKDAY')
and H.surrogate_id=pa.assignment_supervisor_id
and pa.person_id="PER_PERSON_NAMES_F_V"."PERSON_ID"
and pa.assignment_id="PER_ALL_ASSIGNMENTS_M"."ASSIGNMENT_ID"
) as "ASS_SUPVSR_SOURCE_SYSTEM_ID",
( select source_system_id from fusion.HRC_INTEGRATION_KEY_MAP H
where 1=1
and H.object_name='Assignment'
and H.source_system_owner in ('WORKDAY')
and H.surrogate_id="PER_ALL_ASSIGNMENTS_M".work_terms_assignment_id) as "WK_TERM_SOURCE_SYSTEMID",
"PER_PERSON_NAMES_F_V"."CREATED_BY" as "CREATED_BY",
"PER_PERSON_NAMES_F_V"."CREATION_DATE" as "CREATION_DATE",
"PER_USERS"."USERNAME" as "USERNAME",
(select EMAIL_ADDRESS from PER_EMAIL_ADDRESSES_V PEA
where 1=1
and PEA.PERSON_ID="PER_PERSON_NAMES_F_V"."PERSON_ID"
--and PEA.EMAIL_ADDRESS_ID="PER_PERSON_NAMES_F_V".PRIMARY_ADDRESS_ID
and TRUNC(SYSDATE) between DATE_FROM and NVL(DATE_TO,SYSDATE+1)
and rownum=1) as "EMAIL_ADDRESS",
"PER_PERSON_NAMES_F_V"."EFFECTIVE_START_DATE" as "EFFECTIVE_START_DATE",
"HR_ORGANIZATION_UNITS_1"."NAME" as "BUSINESS_UNIT",
to_char("PER_ALL_ASSIGNMENTS_M"."ASSIGNMENT_ID") as "ASSIGNMENT_ID",
to_char("PER_ALL_ASSIGNMENTS_M"."ASSIGNMENT_NUMBER") as "ASSIGNMENT_NUMBER",
( select source_system_id from fusion.HRC_INTEGRATION_KEY_MAP
where 1=1
and object_name='Assignment'
and source_system_owner in ('WORKDAY')
and surrogate_id="PER_ALL_ASSIGNMENTS_M"."ASSIGNMENT_ID") ASS_SOURCE_SYSTEM_ID,
"PER_ALL_ASSIGNMENTS_M"."EFFECTIVE_START_DATE" as "ASSIG_EFFECTIVE_START_DATE",
"PER_ALL_ASSIGNMENTS_M"."ACTION_CODE" as "ACTION_CODE",
"HR_ORGANIZATION_UNITS_2"."NAME" as "LEGAL_ENTITY_CODE",
"PER_JOBS_F_VL"."JOB_CODE" as "JOB_CODE",
"PER_JOBS_F_VL"."NAME" as "JOB_NAME",
"HR_ORGANIZATION_UNITS"."NAME" as "DEPARTMENT_NAME",
"PER_LOCATION_DETAILS_F_VL"."LOCATION_CODE" as "LOCATION_CODE",
"PER_ALL_ASSIGNMENTS_M"."ASSIGNMENT_STATUS_TYPE" as "ASSIGNMENT_STATUS_TYPE",
"PER_PERSON_ADDRESSES_V"."ADDRESS_LINE_1" as "ADDRESS_LINE_1",
"PER_PERSON_ADDRESSES_V"."POSTAL_CODE" as "POSTAL_CODE",
"PER_PERSON_ADDRESSES_V"."REGION_2" as "REGION_2",
"PER_PERSON_ADDRESSES_V"."TOWN_OR_CITY" as "TOWN_OR_CITY",
"PER_PERSON_ADDRESSES_V"."COUNTRY" as "COUNTRY",
"PER_ALL_ASSIGNMENTS_M"."MANAGER_FLAG" as "MANAGER_FLAG",
"PER_PERIODS_OF_SERVICE_V"."ACTUAL_TERMINATION_DATE" as "ACTUAL_TERMINATION_DATE",
"PER_PERIODS_OF_SERVICE_V"."ACCEPTED_TERMINATION_DATE" as "ACCEPTED_TERMINATION_DATE",
"PER_ALL_ASSIGNMENTS_M"."ASS_ATTRIBUTE2" as "TIMEKEEPER_CONTEXTUAL",
( select source_system_id from fusion.HRC_INTEGRATION_KEY_MAP H
where 1=1
and H.object_name='PeriodOfService'
and H.source_system_owner in ('WORKDAY')
and H.surrogate_id="PER_ALL_ASSIGNMENTS_M".PERIOD_OF_SERVICE_ID) as "WK_RELATION_SOURCE_SYSTEMID",
(select source_system_id
from fusion.HRC_INTEGRATION_KEY_MAP H
WHERE 1=1
and h.object_name ='PersonName'
and h.surrogate_id="PER_PERSON_NAMES_F_V".person_name_id
and h.SOURCE_SYSTEM_OWNER in ('WORKDAY')) as "PER_NAME_SOURCE_SYSTEM_ID",
(select source_system_id
from fusion.HRC_INTEGRATION_KEY_MAP H
WHERE 1=1
and h.object_name = 'Address'
and h.surrogate_id=PER_PERSON_ADDRESSES_V.address_id
and h.SOURCE_SYSTEM_OWNER in ('WORKDAY')) as "ADDRESS_SOURCE_SYSTEM_ID",
(select H.source_system_id
from fusion.HRC_INTEGRATION_KEY_MAP H
,PER_EMAIL_ADDRESSES_V PER_EMAIL_ADDRESSES_V
WHERE 1=1
and h.object_name = 'EmailAddress'
and h.SOURCE_SYSTEM_OWNER in ('WORKDAY')
and h.surrogate_id=PER_EMAIL_ADDRESSES_V.email_address_id
and PER_EMAIL_ADDRESSES_V.PERSON_ID="PER_PERSON_NAMES_F_V"."PERSON_ID"
and TRUNC(SYSDATE) between DATE_FROM and NVL(DATE_TO,SYSDATE+1)
and rownum=1
) as "EMAIL_SOURCE_SYSTEM_ID",
(select assignment_number
from per_all_assignments_f aaf
where 1=1
and aaf.assignment_id="PER_ALL_ASSIGNMENTS_M".WORK_TERMS_ASSIGNMENT_ID
and rownum=1) as "WK_TERM_ASSIGNMENT_NUM",
"PER_PERSON_ADDRESSES_V"."ADDRESS_LINE_2" as "ADDRESS_LINE_2",
"PER_ALL_ASSIGNMENTS_M"."ASS_ATTRIBUTE1" as "TIMEKEEPER",
"HR_ORGANIZATION_UNITS"."ORGANIZATION_ID" as "DEPARTMENT_ID"
from "HR_ORGANIZATION_UNITS" "HR_ORGANIZATION_UNITS_2",
"HR_ORGANIZATION_UNITS" "HR_ORGANIZATION_UNITS_1",
"HR_ORGANIZATION_UNITS" "HR_ORGANIZATION_UNITS",
"PER_PERSON_TYPES_VL" "PER_PERSON_TYPES_VL",
"PER_ASSIGNMENT_STATUS_TYPES_VL" "PER_ASSIGNMENT_STATUS_TYPES_VL",
"PER_PERSON_ADDRESSES_V" "PER_PERSON_ADDRESSES_V",
"PER_GRADES_F_VL" "PER_GRADES_F_VL",
"PER_JOBS_F_VL" "PER_JOBS_F_VL",
"HR_ALL_POSITIONS_F_VL" "HR_ALL_POSITIONS_F_VL",
"PER_LOCATION_DETAILS_F_VL" "PER_LOCATION_DETAILS_F_VL",
"PER_ALL_ASSIGNMENTS_M" "PER_ALL_ASSIGNMENTS_M" ,
"PER_USERS" "PER_USERS",
"PER_PERSON_NAMES_F_V" "PER_PERSON_NAMES_F_V",
"PER_PERIODS_OF_SERVICE_V" "PER_PERIODS_OF_SERVICE_V"
where 1=1
and "PER_PERSON_NAMES_F_V"."PERSON_ID"="PER_USERS"."PERSON_ID"(+)
and "PER_PERSON_NAMES_F_V"."EFFECTIVE_START_DATE"=( Select max(P.EFFECTIVE_START_DATE)
from PER_PERSON_NAMES_F_V P
where 1=1
and P.PERSON_ID="PER_PERSON_NAMES_F_V"."PERSON_ID")
and "PER_PERSON_NAMES_F_V"."PERSON_ID"="PER_ALL_ASSIGNMENTS_M"."PERSON_ID"
and "PER_ALL_ASSIGNMENTS_M"."PERSON_ID"="PER_PERIODS_OF_SERVICE_V"."PERSON_ID"(+)
and "PER_ALL_ASSIGNMENTS_M"."PERIOD_OF_SERVICE_ID"="PER_PERIODS_OF_SERVICE_V"."PERIOD_OF_SERVICE_ID"(+)
and "PER_ALL_ASSIGNMENTS_M"."ASSIGNMENT_TYPE" in ('E','C')
and "PER_ALL_ASSIGNMENTS_M"."LOCATION_ID"="PER_LOCATION_DETAILS_F_VL"."LOCATION_ID"(+)
and "PER_ALL_ASSIGNMENTS_M"."POSITION_ID"="HR_ALL_POSITIONS_F_VL"."POSITION_ID"(+)
and "PER_ALL_ASSIGNMENTS_M"."JOB_ID"="PER_JOBS_F_VL"."JOB_ID"(+)
and "PER_ALL_ASSIGNMENTS_M"."GRADE_ID"="PER_GRADES_F_VL"."GRADE_ID"(+)
and "PER_PERSON_NAMES_F_V"."PERSON_ID"="PER_PERSON_ADDRESSES_V"."PERSON_ID"(+)
and trunc(sysdate) between "PER_PERSON_ADDRESSES_V"."EFFECTIVE_START_DATE" and NVL("PER_PERSON_ADDRESSES_V"."EFFECTIVE_END_DATE",SYSDATE+1)
and "PER_ALL_ASSIGNMENTS_M"."ASSIGNMENT_STATUS_TYPE_ID"="PER_ASSIGNMENT_STATUS_TYPES_VL"."ASSIGNMENT_STATUS_TYPE_ID"
and "PER_ALL_ASSIGNMENTS_M"."PERSON_TYPE_ID"="PER_PERSON_TYPES_VL"."PERSON_TYPE_ID"
and "PER_ALL_ASSIGNMENTS_M"."ORGANIZATION_ID"="HR_ORGANIZATION_UNITS"."ORGANIZATION_ID"(+)
and "PER_ALL_ASSIGNMENTS_M"."LEGAL_ENTITY_ID"="HR_ORGANIZATION_UNITS_2"."ORGANIZATION_ID"
and "PER_ALL_ASSIGNMENTS_M"."BUSINESS_UNIT_ID"="HR_ORGANIZATION_UNITS_1"."ORGANIZATION_ID"(+)
and trunc(sysdate) between "PER_ALL_ASSIGNMENTS_M"."EFFECTIVE_START_DATE" and "PER_ALL_ASSIGNMENTS_M"."EFFECTIVE_END_DATE"
and "PER_ALL_ASSIGNMENTS_M"."EFFECTIVE_END_DATE" between "HR_ORGANIZATION_UNITS"."DATE_FROM"(+) and "HR_ORGANIZATION_UNITS"."DATE_TO"(+)
and "PER_ALL_ASSIGNMENTS_M"."EFFECTIVE_END_DATE" between "HR_ORGANIZATION_UNITS_1"."DATE_FROM"(+) and "HR_ORGANIZATION_UNITS_1"."DATE_TO"(+)
and "PER_ALL_ASSIGNMENTS_M"."EFFECTIVE_END_DATE" between "HR_ORGANIZATION_UNITS_2"."DATE_FROM" and "HR_ORGANIZATION_UNITS_2"."DATE_TO"
and "PER_ALL_ASSIGNMENTS_M"."EFFECTIVE_END_DATE" between "HR_ALL_POSITIONS_F_VL"."EFFECTIVE_START_DATE"(+) and "HR_ALL_POSITIONS_F_VL"."EFFECTIVE_END_DATE"(+)
and "PER_ALL_ASSIGNMENTS_M"."EFFECTIVE_END_DATE" between "PER_JOBS_F_VL"."EFFECTIVE_START_DATE"(+) and "PER_JOBS_F_VL"."EFFECTIVE_END_DATE"(+)
and "PER_ALL_ASSIGNMENTS_M"."EFFECTIVE_END_DATE" between "PER_GRADES_F_VL"."EFFECTIVE_START_DATE"(+) and "PER_GRADES_F_VL"."EFFECTIVE_END_DATE"(+)
--and "PER_PERSON_NAMES_F_V"."PERSON_ID" --in('300000062940645','300000063542903','300000072973100')
and "PER_ALL_ASSIGNMENTS_M".assignment_number=(select max(assignment_number) from PER_ALL_ASSIGNMENTS_M PAM
where 1=1
and PAM.person_id="PER_ALL_ASSIGNMENTS_M"."PERSON_ID"
and PAM."ASSIGNMENT_TYPE" in ('E','C')
and trunc(sysdate) between PAM.EFFECTIVE_START_DATE and PAM.EFFECTIVE_END_DATE
)
Order by to_char( "PER_PERSON_NAMES_F_V"."PERSON_ID" )