Introduction
This Post Provides the SQL Query that returns the Assignment Details of the Employees
Query
SELECT peo.person_number employee_number,
loc.internal_location_code location_code, loc.location_name LOCATION,
per_name.last_name, per_name.first_name,
per_name.middle_names middle_name, pa.address_line_1,
pa.address_line_2, pa.town_or_city city, pa.region_2 state,
pa.postal_code zip_code,
TO_CHAR (per.date_of_birth, ‘YYYY-MM-DD’) birth_date,
TO_CHAR (ser.date_start, ‘YYYY-MM-DD’) last_hire_date,
TO_CHAR (ser.actual_termination_date, ‘YYYY-MM-DD’) termination_date,
DECODE (per_leg.sex, ‘M’, ‘Male’, ‘F’, ‘Female’, NULL) gender,
asg_status.user_status employment_status, pj.job_code,
pj.NAME job_name, pay.payroll_name pay_group
FROM per_all_people_f peo,
per_persons per,
per_person_names_f per_name,
per_people_legislative_f per_leg,
per_periods_of_service ser,
per_national_identifiers per_ssn,
per_all_assignments_m asg,
per_assignment_status_types_tl asg_status,
per_person_addresses_v pa,
hr_locations_all loc,
per_jobs pj,
pay_all_payrolls_f pay,
pay_rel_groups_dn payrel,
pay_assigned_payrolls_dn papd
WHERE per.person_id = peo.person_id
AND per_name.person_id = peo.person_id
AND per_leg.person_id = peo.person_id
AND per_ssn.person_id(+) = peo.person_id
AND asg.person_id = peo.person_id
AND ser.person_id = peo.person_id
AND peo.person_id = pa.person_id(+)
AND asg.assignment_id = payrel.assignment_id
AND loc.location_id(+) = asg.location_id
AND asg.job_id = pj.job_id(+)
AND per_ssn.national_identifier_id(+) = peo.primary_nid_id
AND per_name.legislation_code = asg.legislation_code
AND per_leg.legislation_code = asg.legislation_code
AND asg.period_of_service_id = ser.period_of_service_id
AND papd.payroll_id = pay.payroll_id
AND papd.payroll_term_id = payrel.parent_rel_group_id
AND asg.assignment_status_type_id = asg_status.assignment_status_type_id
AND per_name.name_type = ‘GLOBAL’
AND asg.primary_flag = ‘Y’
AND asg.system_person_type = ‘EMP’
AND asg.assignment_status_type = ‘ACTIVE’
–AND asg.employment_category = ‘FR’
–AND peo.person_number = ‘671047’
AND pa.address_type = ‘HOME’
AND payrel.group_type = ‘A’
AND ser.date_start = (SELECT MAX (ser1.date_start)
FROM per_periods_of_service ser1
WHERE ser1.person_id = ser.person_id)
AND NVL (TRUNC (ser.actual_termination_date), TRUNC (SYSDATE)) BETWEEN peo.effective_start_date AND peo.effective_end_date
AND NVL (TRUNC (ser.actual_termination_date), TRUNC (SYSDATE)) BETWEEN per_name.effective_start_date AND per_name.effective_end_date
AND NVL (TRUNC (ser.actual_termination_date), TRUNC (SYSDATE)) BETWEEN per_leg.effective_start_date AND per_leg.effective_end_date
AND NVL (TRUNC (ser.actual_termination_date), TRUNC (SYSDATE)) BETWEEN asg.effective_start_date AND asg.effective_end_date
AND NVL (TRUNC (ser.actual_termination_date), TRUNC (SYSDATE)) BETWEEN pa.effective_start_date(+) AND pa.effective_end_date(+)
AND NVL (TRUNC (ser.actual_termination_date), TRUNC (SYSDATE)) BETWEEN loc.effective_start_date(+) AND loc.effective_end_date(+)
AND NVL (TRUNC (ser.actual_termination_date), TRUNC (SYSDATE)) BETWEEN pay.effective_start_date(+) AND pay.effective_end_date(+)
AND NVL (TRUNC (ser.actual_termination_date), TRUNC (SYSDATE)) BETWEEN payrel.start_date AND payrel.end_date
AND NVL (TRUNC (ser.actual_termination_date), TRUNC (SYSDATE)) BETWEEN papd.start_date AND NVL (papd.lspd, TO_DATE (’31/12/4712′, ‘DD/MM/YYYY’))
To Know More about Post
Email : Doyen.ebiz@gmail.com
