Scribd
Upload
32 views3 pages

Query

The document contains an SQL query that retrieves employee information including personal details, address, employment status, and salary from various tables in a database. It filters the results based on specific conditions such as current employment status, primary address, and effective dates. Additionally, it ensures that the data is relevant to a particular business group and within a defined date range for address creation or updates.

Uploaded by

Srikkanth Mani
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
32 views3 pages

Query

The document contains an SQL query that retrieves employee information including personal details, address, employment status, and salary from various tables in a database. It filters the results based on specific conditions such as current employment status, primary address, and effective dates. Additionally, it ensures that the data is relevant to a particular business group and within a defined date range for address creation or updates.

Uploaded by

Srikkanth Mani
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
You are on page 1/ 3

SELECT

papf.person_id,
papf.employee_number,
papf.national_identifier employee_ssn,
upper(
papf.first_name
) first_name,
upper(
papf.middle_names
) middle_initial,
upper(
papf.last_name
) last_name,
upper(
papf.sex
) gender,
to_char(
papf.date_of_birth, 'RRRRMMDD'
) date_of_birth,
upper(
pa.address_line1
) address1,
upper(
pa.address_line2
) address2,
upper(
pa.town_or_city
) city,
upper(
pa.region_1
) state,
pa.postal_code zip,
to_char(
papf.original_date_of_hire, 'RRRRMMDD'
) date_of_hire,--to_char
(ppos.actual_termination_date,'RRRRMMDD')
NULL termination_date,
NULL termination_reason,
NULL employee_status,
ppp.proposed_salary_n salary_annual,
to_char(
paaf.effective_start_date, 'RRRRMMDD'
) work_location_eff_date,
hsc.segment1 profit_center,
(
SELECT
meaning
FROM
fnd_lookup_values
WHERE
lookup_type = 'SD_ONSHORE_OFFSHORE'
AND language = userenv(
'LANG'
)
AND lookup_code = ppg.segment2
AND enabled_flag = 'Y'
) employee_category,
paaf.ass_attribute26 employee_class
FROM
per_all_people_f papf,
per_all_assignments_f paaf,
pay_people_groups ppg,
per_person_types ppt,
per_person_type_usages_f pptu,
per_periods_of_service ppos,
per_pay_proposals ppp,
hr_soft_coding_keyflex hsc,
(
SELECT
person_id,
address_line1,
address_line2,
town_or_city,
region_1,
postal_code,creation_date,last_update_date
FROM
per_addresses
WHERE
date_to IS NULL
AND primary_flag = 'Y'
ORDER BY
date_from DESC
) pa
WHERE
1 = 1
AND papf.person_id = paaf.person_id
AND papf.business_group_id = paaf.business_group_id
AND papf.person_id = pptu.person_id
AND papf.person_type_id = pptu.person_type_id
AND pptu.person_type_id = ppt.person_type_id
AND papf.current_employee_flag = 'Y'
AND paaf.primary_flag = 'Y'
AND paaf.assignment_type = 'E'
AND ppt.user_person_type = 'Employee'
AND paaf.people_group_id = ppg.people_group_id
AND papf.person_id = ppos.person_id
AND papf.business_group_id = ppos.business_group_id
AND ppos.period_of_service_id = paaf.period_of_service_id

AND papf.person_id = pa.person_id (+)


AND paaf.soft_coding_keyflex_id = hsc.soft_coding_keyflex_id (+)
AND ppp.assignment_id = paaf.assignment_id
AND ppp.change_date = (
SELECT
MAX(d.change_date)
FROM
per_pay_proposals d
WHERE
d.assignment_id = paaf.assignment_id
AND d.business_group_id = paaf.business_group_id
AND d.approved = 'Y'
)
AND trunc(sysdate) BETWEEN trunc(
papf.effective_start_date
) AND trunc(
papf.effective_end_date
)
AND trunc(sysdate) BETWEEN trunc(
paaf.effective_start_date
) AND trunc(
paaf.effective_end_date
)
AND trunc(sysdate) BETWEEN trunc(
pptu.effective_start_date
) AND trunc(
pptu.effective_end_date
)
AND papf.business_group_id = 82
and (trunc (pa.last_update_date)!=trunc (pa.creation_Date)
or trunc (pa.creation_Date)!=trunc (papf.creation_Date))
and (pa.creation_date between '01-MAY-2023' and '31-MAY-2023'
Or pa.last_update_date between '01-MAY-2023' and '31-MAY-2023')
--and papf.person_id =646680

You might also like