Untitled
unknown
sql
2 years ago
43 kB
17
Indexable
WITH
PARAMETERS AS (
SELECT
periods.person_id person_id,
periods.actual_termination_date effective_date,
inputs.*
FROM
(
SELECT
:p_businessUnitID business_unit_id,
:p_unitName unit_name,
:p_divisionName division_name,
:p_organizationName organization_name,
:p_costCenter cost_center,
:p_region region,
:p_personNumber person_number,
:p_supervisorNumber supervisor_number,
:p_bandGrad band_grade,
:p_hodNumber hod_number,
:p_chiefNumber chief_number,
:p_startDate start_date,
:p_endDate end_date
FROM
dual) inputs,
per_periods_of_service periods,
per_all_assignments_f assignments
WHERE 1 = 1
AND periods.actual_termination_date IS NOT NULL
AND (periods.actual_termination_date + 1) BETWEEN assignments.effective_start_date AND assignments.effective_end_date
AND assignments.assignment_type IN ('E', 'P')
AND assignments.primary_flag = 'Y'
AND assignments.period_of_service_id = periods.period_of_service_id),
per_org_tmp AS (
SELECT
person_id,
organization_id department,
regexp_substr(trim('/' FROM org_data), '[^//]+', 1, 3) division,
nvl(regexp_substr(trim('/' FROM org_data), '[^//]+', 1, 2), regexp_substr(trim('/' FROM org_data), '[^//]+', 1, 1)) unit
FROM (
SELECT
p.person_id,
assignments.organization_id,
(
SELECT '/' || listagg(organization_id, '/') within GROUP (ORDER BY lev DESC) PATH
FROM (
SELECT
organization_id,
LEVEL lev,
depth
FROM (
SELECT
organizations.organization_id,
org_nodes.parent_organization_id,
org_nodes.depth
FROM
per_org_tree_node_v org_nodes,
hr_all_organization_units_f_vl organizations
WHERE 1 = 1
AND organizations.organization_id = org_nodes.organization_id(+)
AND p.effective_date BETWEEN organizations.effective_start_date AND organizations.effective_end_date) org_nodes
WHERE 1 = 1
AND (depth != 0 OR depth IS NULL)
START WITH organization_id = assignments.organization_id
CONNECT BY organization_id = PRIOR parent_organization_id)) org_data
FROM
per_all_assignments_f assignments,
parameters p
WHERE 1 = 1
AND p.effective_date BETWEEN assignments.effective_start_date AND assignments.effective_end_date
AND assignments.primary_flag = 'Y'
AND assignments.effective_latest_change = 'Y'
AND assignments.person_id = p.person_id)),
per_chiefs_tmp AS (
select
p.person_id person_id,
(select
supervision.supervisor_id
from (
select
supervision.person_id,
supervision.manager_id supervisor_id,
sup_grades.name supervisor_grade,
inner_p.person_id group_id
from
per_assignment_supervisors_f supervision,
per_all_assignments_f sup_assignments,
per_grades sup_grades,
parameters inner_p
where 1=1
and inner_p.effective_date between supervision.effective_start_date and supervision.effective_end_date
and supervision.manager_type = 'LINE_MANAGER'
and supervision.primary_flag = 'Y'
and inner_p.effective_date between sup_assignments.effective_start_date(+) and sup_assignments.effective_end_date(+)
and sup_assignments.assignment_id(+) = supervision.manager_assignment_id
and sup_grades.grade_id(+) = sup_assignments.grade_id) supervision
where 1=1
and supervision.supervisor_grade = '43'
START WITH (supervision.person_id = p.person_id and supervision.group_id = p.person_id)
CONNECT BY (supervision.PERSON_ID = PRIOR supervision.supervisor_id and supervision.group_id = PRIOR supervision.group_id)) chief_id
from
parameters p),
pay_payrolls_tmp AS (
SELECT
p.person_id,
py.payroll_name
FROM
pay_payroll_actions ppa,
PAY_PAYROLL_REL_ACTIONS PRA,
pay_assigned_payrolls_dn apd,
PER_ALL_ASSIGNMENTS_M PAAM,
pay_time_periods ptp,
PAY_REL_GROUPS_DN PRG,
PER_ALL_PEOPLE_F PAPF,
pay_all_payrolls_f py,
pay_consolidation_sets pcs,
pay_payroll_terms pt,
parameters p
WHERE
1 = 1
AND PRA.PAYROLL_ACTION_ID = PPA.PAYROLL_ACTION_ID
AND ppa.action_type IN ('R')
AND PAAM.ASSIGNMENT_ID = PRG.ASSIGNMENT_ID
AND PCS.CONSOLIDATION_SET_ID = NVL(PPA.CONSOLIDATION_SET_ID, PY.CONSOLIDATION_SET_ID)
AND PTP.PERIOD_CATEGORY = 'E'
AND PRG.PAYROLL_RELATIONSHIP_ID = PRA.PAYROLL_RELATIONSHIP_ID
AND pt.payroll_term_id = apd.payroll_term_id
AND py.payroll_id = apd.payroll_id
AND PRA.SOURCE_ACTION_ID IS NULL
AND pt.hr_term_id = PRG.term_id
AND PAAM.ASSIGNMENT_TYPE = 'E'
AND PAAM.PRIMARY_FLAG = 'Y'
AND PAPF.PERSON_ID = PAAM.PERSON_ID
AND TRUNC(p.effective_date) BETWEEN PAPF.EFFECTIVE_START_DATE
AND PAPF.EFFECTIVE_END_DATE
AND TRUNC(p.effective_date) BETWEEN PY.EFFECTIVE_START_DATE
AND PY.EFFECTIVE_END_DATE
AND TRUNC(p.effective_date) BETWEEN PAAM.EFFECTIVE_START_DATE
AND PAAM.EFFECTIVE_END_DATE
AND ptp.payroll_id = py.payroll_id
AND PPA.EARN_TIME_PERIOD_ID = PTP.TIME_PERIOD_ID
AND PCS.CONSOLIDATION_SET_NAME IN ('Mobily Monthly Payroll','MFS Monthly Payroll')
and papf.person_id(+) = p.person_id),
parameters_tr AS (
SELECT txnheader.subject_id person_id,
sysdate effective_date,
txnheader.transaction_id,
inputs.*
FROM hrc_txn_header txnheader,
hrc_txn_data txndata,
fa_fusion_soainfra.wftask wf,
(
SELECT
:p_businessUnitID business_unit_id,
:p_unitName unit_name,
:p_divisionName division_name,
:p_organizationName organization_name,
:p_costCenter cost_center,
:p_region region,
:p_personNumber person_number,
:p_supervisorNumber supervisor_number,
:p_bandGrad band_grade,
:p_hodNumber hod_number,
:p_chiefNumber chief_number,
:p_startDate start_date,
:p_endDate end_date
FROM
dual
) inputs
WHERE 1 = 1
AND txndata.transaction_id = txnheader.transaction_id
AND txnheader.object = 'PER_ALL_ASSIGNMENTS_M'
AND txnheader.module_identifier = 'Resignation'
AND to_char(txndata.transaction_id) = wf.correlationid
AND nvl(decode(txndata.status,'PENDING',wf.taskid,txndata.task_id),1) = nvl(wf.taskid,1)
),
per_org_tr_tmp AS (
SELECT
person_id,
organization_id department,
regexp_substr(trim('/' FROM org_data), '[^//]+', 1, 3) division,
nvl(regexp_substr(trim('/' FROM org_data), '[^//]+', 1, 2), regexp_substr(trim('/' FROM org_data), '[^//]+', 1, 1)) unit
FROM (
SELECT
p.person_id,
assignments.organization_id,
(
SELECT '/' || listagg(organization_id, '/') within GROUP (ORDER BY lev DESC) PATH
FROM (
SELECT
organization_id,
LEVEL lev,
depth
FROM (
SELECT
organizations.organization_id,
org_nodes.parent_organization_id,
org_nodes.depth
FROM
per_org_tree_node_v org_nodes,
hr_all_organization_units_f_vl organizations
WHERE 1 = 1
AND organizations.organization_id = org_nodes.organization_id(+)
AND p.effective_date BETWEEN organizations.effective_start_date AND organizations.effective_end_date) org_nodes
WHERE 1 = 1
AND (depth != 0 OR depth IS NULL)
START WITH organization_id = assignments.organization_id
CONNECT BY organization_id = PRIOR parent_organization_id)) org_data
FROM
per_all_assignments_f assignments,
parameters_tr p
WHERE 1 = 1
AND p.effective_date BETWEEN assignments.effective_start_date AND assignments.effective_end_date
AND assignments.primary_flag = 'Y'
AND assignments.effective_latest_change = 'Y'
AND assignments.person_id = p.person_id)),
per_supervisor_nodes_v_tr_tmp AS (
SELECT assignments.person_id person_id,
mgr_assignments.person_id manager_id,
mgr_grades.grade_code manager_grade
FROM per_assignment_supervisors_f supervisors,
per_all_assignments_f assignments,
per_all_assignments_f mgr_assignments,
per_grades_f mgr_grades
WHERE 1 = 1
AND sysdate BETWEEN supervisors.effective_start_date AND supervisors.effective_end_date
AND supervisors.primary_flag = 'Y'
AND supervisors.manager_type = 'LINE_MANAGER'
AND sysdate BETWEEN assignments.effective_start_date AND assignments.effective_end_date
AND assignments.primary_flag = 'Y'
AND assignments.assignment_id = supervisors.assignment_id
AND sysdate BETWEEN mgr_assignments.effective_start_date AND mgr_assignments.effective_end_date
AND mgr_assignments.primary_flag = 'Y'
AND mgr_assignments.assignment_status_type != 'INACTIVE'
AND mgr_assignments.assignment_id = supervisors.manager_assignment_id
AND sysdate BETWEEN mgr_grades.effective_start_date AND mgr_grades.effective_end_date
AND mgr_grades.active_status = 'A'
AND mgr_grades.grade_id(+) = mgr_assignments.grade_id
),
per_chiefs_tr_tmp AS (
SELECT person_id,
connect_by_root manager_id chief_id
FROM per_supervisor_nodes_v_tr_tmp
WHERE 1 = 1 START WITH (manager_grade = '43')
CONNECT BY
PRIOR person_id = manager_id
),
pay_payrolls_tr_tmp AS (
SELECT
p.person_id,
py.payroll_name
FROM
pay_payroll_actions ppa,
PAY_PAYROLL_REL_ACTIONS PRA,
pay_assigned_payrolls_dn apd,
PER_ALL_ASSIGNMENTS_M PAAM,
pay_time_periods ptp,
PAY_REL_GROUPS_DN PRG,
PER_ALL_PEOPLE_F PAPF,
pay_all_payrolls_f py,
pay_consolidation_sets pcs,
pay_payroll_terms pt,
parameters_tr p
WHERE
1 = 1
AND PRA.PAYROLL_ACTION_ID = PPA.PAYROLL_ACTION_ID
AND ppa.action_type IN ('R')
AND PAAM.ASSIGNMENT_ID = PRG.ASSIGNMENT_ID
AND PCS.CONSOLIDATION_SET_ID = NVL(PPA.CONSOLIDATION_SET_ID, PY.CONSOLIDATION_SET_ID)
AND PTP.PERIOD_CATEGORY = 'E'
AND PRG.PAYROLL_RELATIONSHIP_ID = PRA.PAYROLL_RELATIONSHIP_ID
AND pt.payroll_term_id = apd.payroll_term_id
AND py.payroll_id = apd.payroll_id
AND PRA.SOURCE_ACTION_ID IS NULL
AND pt.hr_term_id = PRG.term_id
AND PAAM.ASSIGNMENT_TYPE = 'E'
AND PAAM.PRIMARY_FLAG = 'Y'
AND PAPF.PERSON_ID = PAAM.PERSON_ID
AND TRUNC(p.effective_date) BETWEEN PAPF.EFFECTIVE_START_DATE
AND PAPF.EFFECTIVE_END_DATE
AND TRUNC(p.effective_date) BETWEEN PY.EFFECTIVE_START_DATE
AND PY.EFFECTIVE_END_DATE
AND TRUNC(p.effective_date) BETWEEN PAAM.EFFECTIVE_START_DATE
AND PAAM.EFFECTIVE_END_DATE
AND ptp.payroll_id = py.payroll_id
AND PPA.EARN_TIME_PERIOD_ID = PTP.TIME_PERIOD_ID
AND PCS.CONSOLIDATION_SET_NAME IN ('Mobily Monthly Payroll','MFS Monthly Payroll')
and papf.person_id(+) = p.person_id),
transactions as (
SELECT txndata.transaction_id,
decode(wf.state, 'ASSIGNED', wf.assigneddate, wf.updateddate) last_process_date,
txndata.submitted_date request_submitted_date,
null wave_of_requested,
null wave_of_days,
null wave_of_approved_days,
initcap(nvl(wf.state,wf.outcome)) request_status,
wf.updatedbydisplayname last_approved_by,
wf.updateddate last_approved_date,
decode(wf.state, 'ASSIGNED', wf.assigneesdisplayname) pending_with,
decode(wf.state, 'ASSIGNED', wf.assigneddate) pending_from_date
FROM hrc_txn_header txnheader,
hrc_txn_data txndata,
per_all_people_f papf,
per_person_names_f ppnf,
fa_fusion_soainfra.wftask wf
WHERE 1 = 1
AND txndata.transaction_id = txnheader.transaction_id
AND txnheader.object = 'PER_ALL_ASSIGNMENTS_M'
AND txnheader.module_identifier = 'Resignation'
AND txnheader.subject_id = papf.person_id(+)
AND trunc(sysdate) BETWEEN papf.effective_start_date(+) AND papf.effective_end_date(+)
AND papf.person_id = ppnf.person_id(+)
AND ppnf.name_type(+) = 'GLOBAL'
AND trunc(sysdate) BETWEEN ppnf.effective_start_date(+) AND ppnf.effective_end_date(+)
AND to_char(txndata.transaction_id) = wf.correlationid
AND nvl(decode(txndata.status,'PENDING',wf.taskid,txndata.task_id),1) = nvl(wf.taskid,1)
)
SELECT
people.person_number pf_number,
names_en.display_name
|| ' (' || people.person_number || ')' employee_name_number,
people_info.date_of_birth date_of_birth,
business_units.name business_unit,
units.name unit_name,
divisions.name division_name,
departments.name department_name,
periods.date_start original_hire_date,
job_meanings.meaning job_level,
job_families.job_family_name grade_band,
positions.position_code position_code,
pos_names_en.name position_name,
pos_names_ar.name position_name_arabic,
positions.effective_start_date position_effective_date,
rtrim(position_location.attribute2,', SA') position_region,
initcap(positions.position_type) position_type,
decode(positions.active_status,
'A', 'Active',
positions.active_status) position_status,
position_organization.name position_organization,
alloc_accounts.segment3 cost_center,
reason_meaning.meaning leaving_reason,
contract_types.meaning contract_type,
initcap(contracts.status) contract_status,
contracts.duration || ' '
|| decode(contracts.duration_units,
'Y', 'Years',
'D', 'Days',
contracts.duration_units) contract_duration,
contracts.contract_number contract_number,
contracts.contract_end_date contract_end_date,
nationalities.meaning nationality,
decode(nationalities.meaning,'Saudi','Saudi','Non Saudi') nationality_category,
ids.national_identifier_number id_or_iqama,
grades.name grade,
email_addresses.email_address email_address,
education_levels.meaning education_level,
NVL2(supervisors.person_id,
supervisor_names.display_name
|| ' ('
|| supervisors.person_number
|| ')',
null) supervisor_name_number,
sup_email_addresses.email_address supervisor_email,
phones.phone_number phone_number,
initcap(assignments.assignment_status_type) employee_status,
parent_positions.name parent_position,
payrolls.payroll_name payroll,
NVL2(hods.person_id,
hod_names.display_name
|| ' ('
|| hods.person_number
|| ')',
null) hod_name_number,
hod_email_addresses.email_address hod_email,
NVL2(chiefs.person_id,
chief_names.display_name
|| ' ('
|| chiefs.person_number
|| ')',
null) chief_name_number,
chief_email_addresses.email_address chief_email,
periods.actual_termination_date actual_termination_date,
floor((periods.actual_termination_date - periods.date_start)
/ 365.24) tenure_years,
floor(mod((periods.actual_termination_date - periods.date_start)
/ 30.437, 12)) tenure_months,
floor(mod(mod((periods.actual_termination_date - periods.date_start),
365.24), 30.437)) tenure_days,
periods.notified_termination_date notified_date,
NULL last_process_date,
NULL request_submitted_date,
periods.attribute1 wave_of_requested,
periods.attribute_number1 wave_of_days,
NULL wave_of_approved_days,
'Approve' request_status,
NULL last_approved_by,
NULL last_approved_date,
NULL pending_with,
NULL pending_from_date
FROM
per_all_people_f people,
per_periods_of_service periods,
per_person_names_f names_en,
per_persons people_info,
per_all_assignments_f assignments,
hr_all_organization_units_f_vl business_units,
per_org_tmp organizations,
hr_all_organization_units_f_vl departments,
hr_all_organization_units_f_vl divisions,
hr_all_organization_units_f_vl units,
per_jobs_f jobs,
fnd_lookup_values job_meanings,
per_job_family_f_tl job_families,
hr_all_positions_f positions,
hr_all_positions_f_tl pos_names_en,
hr_all_positions_f_tl pos_names_ar,
hr_locations_all position_location,
hr_all_organization_units_f_vl position_organization,
pay_payrolls_tmp payrolls,
-- COST CENTER
pay_cost_allocations_f cost_allocs,
pay_cost_alloc_accounts alloc_accounts,
-- leaving_reason
per_action_occurrences action_occurrences,
per_action_reasons_b action_reasons,
fnd_lookup_values_tl reason_meaning,
-- contracts
per_contracts_f contracts,
fnd_lookup_values_tl contract_types,
-- nationalities
per_citizenships citizenships,
fnd_lookup_values_tl nationalities,
-- id
per_national_identifiers ids,
-- supervisors
per_assignment_supervisors_f supervision,
PER_ALL_ASSIGNMENTS_F sup_ASSIGNMENTS,
per_all_people_f supervisors,
per_person_names_f supervisor_names,
per_grades grades,
per_email_addresses email_addresses,
per_people_legislative_f legals,
fnd_lookup_values_tl education_levels,
per_email_addresses sup_email_addresses,
per_phones phones,
-- parent_position
per_pos_tree_node_v positions_tree,
hr_all_positions_f_tl parent_positions,
-- hods
per_asg_responsibilities hod_resps,
per_all_people_f hods,
per_person_names_f hod_names,
per_email_addresses hod_email_addresses,
per_chiefs_tmp people_chiefs,
per_all_people_f chiefs,
per_person_names_f chief_names,
per_email_addresses chief_email_addresses,
PARAMETERS p
WHERE 1 = 1
-- people
AND p.effective_date BETWEEN people.effective_start_date AND people.effective_end_date
-- periods
AND periods.person_id = people.person_id
AND periods.actual_termination_date IS NOT NULL
-- names_en
AND p.effective_date BETWEEN names_en.effective_start_date(+) AND names_en.effective_end_date(+)
AND names_en.name_type(+) = 'GLOBAL'
AND names_en.person_id(+) = people.person_id
-- people_info
AND people_info.person_id(+) = people.person_id
-- assignments
AND (p.effective_date + 1) BETWEEN assignments.effective_start_date AND assignments.effective_end_date
AND assignments.assignment_type IN ('E', 'P')
AND assignments.primary_flag = 'Y'
AND assignments.period_of_service_id = periods.period_of_service_id
-- business_units
AND p.effective_date BETWEEN business_units.effective_start_date(+) AND business_units.effective_end_date(+)
AND business_units.organization_id(+) = assignments.business_unit_id
-- organizations
AND organizations.person_id(+) = people.person_id
AND p.effective_date BETWEEN departments.effective_start_date(+) AND departments.effective_end_date(+)
AND departments.organization_id(+) = organizations.department
AND p.effective_date BETWEEN divisions.effective_start_date(+) AND divisions.effective_end_date(+)
AND divisions.organization_id(+) = organizations.division
AND p.effective_date BETWEEN units.effective_start_date(+) AND units.effective_end_date(+)
AND units.organization_id(+) = organizations.unit
-- jobs
AND p.effective_date BETWEEN jobs.effective_start_date(+) AND jobs.effective_end_date(+)
AND jobs.job_id(+) = assignments.job_id
-- job_meanings
and job_meanings.language(+) = 'US'
and job_meanings.lookup_type(+) = 'MANAGER_LEVEL'
and job_meanings.lookup_code(+) = jobs.manager_level
-- job_families
AND p.effective_date BETWEEN job_families.effective_start_date(+) AND job_families.effective_end_date(+)
AND job_families.language(+) = 'US'
AND job_families.job_family_id(+) = jobs.job_family_id
-- positions
AND p.effective_date BETWEEN positions.effective_start_date(+) AND positions.effective_end_date(+)
AND positions.position_id(+) = assignments.position_id
---- pos_names_en
AND p.effective_date BETWEEN pos_names_en.effective_start_date(+) AND pos_names_en.effective_end_date(+)
AND pos_names_en.language(+) = 'US'
AND pos_names_en.position_id(+) = positions.position_id
---- pos_names_ar
AND p.effective_date BETWEEN pos_names_ar.effective_start_date(+) AND pos_names_ar.effective_end_date(+)
AND pos_names_ar.language(+) = 'AR'
AND pos_names_ar.position_id(+) = positions.position_id
---- position_location
AND p.effective_date BETWEEN position_location.effective_start_date(+) AND position_location.effective_end_date(+)
and position_location.location_id(+) = positions.location_id
---- position_organization
and p.effective_date BETWEEN position_organization.effective_start_date(+) AND position_organization.effective_end_date(+)
and position_organization.organization_id(+) = positions.organization_id
-- payrolls
and payrolls.person_id(+) = people.person_id
-- COST_CENTER
---- COST_ALLOCS
AND p.effective_date BETWEEN cost_allocs.effective_start_date(+) AND cost_allocs.effective_end_date(+)
AND cost_allocs.source_type(+) = 'ORG'
AND cost_allocs.source_id(+) = assignments.organization_id
---- ALLOC_ACCOUNTS
AND alloc_accounts.cost_allocation_record_id(+) = cost_allocs.cost_allocation_record_id
-- leaving_reason
and action_occurrences.action_occurrence_id(+) = periods.action_occurrence_id
and action_reasons.action_reason_id(+) = action_occurrences.action_reason_id
and reason_meaning.language(+) = 'US'
and reason_meaning.lookup_type(+) = 'HRX_FR_REASON_MAPPING'
and reason_meaning.lookup_code(+) = action_reasons.action_reason_code
-- contracts
and p.effective_date BETWEEN contracts.effective_start_date(+) AND contracts.effective_end_date(+)
and contracts.period_of_service_id(+) = periods.period_of_service_id
---- contract_types
and contract_types.language(+) = 'US'
and contract_types.lookup_type(+) = 'CONTRACT_TYPE'
and contract_types.lookup_code(+) = contracts.type
-- nationalities
and p.effective_date between citizenships.date_from(+) and citizenships.date_to(+)
and citizenships.person_id(+) = people.person_id
and nationalities.language(+) = 'US'
and nationalities.lookup_type(+) = 'NATIONALITY'
and nationalities.lookup_code(+) = citizenships.legislation_code
-- ids
and ids.national_identifier_id(+) = people.primary_nid_id
-- supervisors
AND p.effective_date BETWEEN supervision.effective_start_date(+) AND supervision.effective_end_date(+)
AND supervision.PRIMARY_FLAG(+) = 'Y'
AND supervision.manager_type(+) = 'LINE_MANAGER'
and supervision.assignment_id(+) = assignments.assignment_id
---- sup_ASSIGNMENTS
AND p.effective_date BETWEEN sup_ASSIGNMENTS.effective_start_date(+) AND sup_ASSIGNMENTS.effective_end_date(+)
AND sup_ASSIGNMENTS.primary_flag(+) = 'Y'
AND sup_ASSIGNMENTS.assignment_id(+) = supervision.MANAGER_ASSIGNMENT_ID
---- SUPERVISORS
AND p.effective_date BETWEEN supervisors.effective_start_date(+) AND supervisors.effective_end_date(+)
AND supervisors.person_id(+) = sup_ASSIGNMENTS.person_id
---- SUPERVISOR_NAMES
AND p.effective_date BETWEEN supervisor_names.effective_start_date(+) AND supervisor_names.effective_end_date(+)
AND supervisor_names.name_type(+) = 'GLOBAL'
AND supervisor_names.person_id(+) = supervisors.person_id
-- grades
and p.effective_date between grades.effective_start_date(+) and grades.effective_end_date(+)
and grades.grade_id(+) = assignments.grade_id
-- EMAIL_ADDRESSES
AND email_addresses.email_address_id(+) = people.primary_email_id
-- LEGALS
AND p.effective_date BETWEEN legals.effective_start_date(+) AND legals.effective_end_date(+)
AND legals.person_id(+) = people.person_id
-- education_levels
and education_levels.language(+) = 'US'
and education_levels.lookup_type(+) = 'PER_HIGHEST_EDUCATION_LEVEL'
and education_levels.lookup_code(+) = legals.highest_education_level
-- sup_EMAIL_ADDRESSES
AND sup_EMAIL_ADDRESSES.email_address_id(+) = supervisors.primary_email_id
-- phones
and p.effective_date between phones.date_from(+) and phones.date_to(+)
and phones.phone_id(+) = people.primary_phone_id
-- parent_position
AND positions_tree.tree_code(+) = 'MEE_POSITION_TREE'
AND positions_tree.position_id(+) = positions.position_id
and p.effective_date between parent_positions.effective_start_date(+) and parent_positions.effective_end_date(+)
and parent_positions.language(+) = 'US'
and parent_positions.position_id(+) = positions_tree.parent_position_id
-- hod
---- hod_resps
and p.effective_date between nvl(hod_resps.start_date(+), p.effective_date) and nvl(hod_resps.end_date(+), p.effective_date)
and hod_resps.responsibility_type(+) = 'HOD'
and hod_resps.top_department_id(+) = organizations.division
---- hods
AND p.effective_date BETWEEN hods.effective_start_date(+) AND hods.effective_end_date(+)
and hods.person_id(+) = hod_resps.person_id
---- hod_names
AND p.effective_date BETWEEN hod_names.effective_start_date(+) AND hod_names.effective_end_date(+)
AND hod_names.name_type(+) = 'GLOBAL'
AND hod_names.person_id(+) = hods.person_id
-- hod_EMAIL_ADDRESSES
AND hod_EMAIL_ADDRESSES.email_address_id(+) = hods.primary_email_id
-- chief
---- people_chiefs
and people_chiefs.person_id(+) = people.person_id
---- chiefs
AND p.effective_date BETWEEN chiefs.effective_start_date(+) AND chiefs.effective_end_date(+)
and chiefs.person_id(+) = people_chiefs.person_id
---- chief_names
AND p.effective_date BETWEEN chief_names.effective_start_date(+) AND chief_names.effective_end_date(+)
AND chief_names.name_type(+) = 'GLOBAL'
AND chief_names.person_id(+) = chiefs.person_id
-- chief_EMAIL_ADDRESSES
AND chief_EMAIL_ADDRESSES.email_address_id(+) = chiefs.primary_email_id
-- parameters
AND p.person_id = people.person_id
AND NVL(business_units.organization_id, '0') = NVL(p.business_unit_id, NVL(business_units.organization_id, '0'))
AND NVL(units.name, '0') LIKE NVL(('%' || p.unit_name || '%'), NVL(units.name, '0'))
AND NVL(divisions.name, '0') LIKE NVL(('%' || p.division_name || '%'), NVL(divisions.name, '0'))
AND NVL(departments.name, '0') LIKE NVL(('%' || p.organization_name || '%'), NVL(departments.name, '0'))
AND NVL(alloc_accounts.segment3, '0') = NVL(p.cost_center, NVL(alloc_accounts.segment3, '0'))
and nvl(position_location.attribute2, '0') LIKE nvl(('%' || p.region || '%'), nvl(position_location.attribute2, '0'))
AND NVL(people.person_number, '0') = NVL(p.person_number, NVL(people.person_number, '0'))
AND NVL(supervisors.person_number, '0') = NVL(p.supervisor_number, NVL(supervisors.person_number, '0'))
and nvl(null, '0') = nvl(p.band_grade, nvl(null, '0'))
AND NVL(hods.person_number, '0') = NVL(p.hod_number, NVL(hods.person_number, '0'))
AND NVL(chiefs.person_number, '0') = NVL(p.chief_number, NVL(chiefs.person_number, '0'))
AND NVL(periods.actual_termination_date, TRUNC(SYSDATE)) >= NVL(p.start_date, NVL(periods.actual_termination_date, TRUNC(SYSDATE)))
AND NVL(periods.actual_termination_date, TRUNC(SYSDATE)) <= NVL(p.end_date, NVL(periods.actual_termination_date, TRUNC(SYSDATE)))
UNION
SELECT
distinct
people.person_number pf_number,
names_en.display_name
|| ' (' || people.person_number || ')' employee_name_number,
people_info.date_of_birth date_of_birth,
business_units.name business_unit,
units.name unit_name,
divisions.name division_name,
departments.name department_name,
periods.date_start original_hire_date,
job_meanings.meaning job_level,
job_families.job_family_name grade_band,
positions.position_code position_code,
pos_names_en.name position_name,
pos_names_ar.name position_name_arabic,
positions.effective_start_date position_effective_date,
rtrim(position_location.attribute2,', SA') position_region,
initcap(positions.position_type) position_type,
decode(positions.active_status,
'A', 'Active',
positions.active_status) position_status,
position_organization.name position_organization,
alloc_accounts.segment3 cost_center,
reason_meaning.meaning leaving_reason,
contract_types.meaning contract_type,
initcap(contracts.status) contract_status,
contracts.duration || ' '
|| decode(contracts.duration_units,
'Y', 'Years',
'D', 'Days',
contracts.duration_units) contract_duration,
contracts.contract_number contract_number,
contracts.contract_end_date contract_end_date,
nationalities.meaning nationality,
decode(nationalities.meaning,'Saudi','Saudi','Non Saudi') nationality_category,
ids.national_identifier_number id_or_iqama,
grades.name grade,
email_addresses.email_address email_address,
education_levels.meaning education_level,
NVL2(supervisors.person_id,
supervisor_names.display_name
|| ' ('
|| supervisors.person_number
|| ')',
null) supervisor_name_number,
sup_email_addresses.email_address supervisor_email,
phones.phone_number phone_number,
initcap(assignments.assignment_status_type) employee_status,
parent_positions.name parent_position,
payrolls.payroll_name payroll,
NVL2(hods.person_id,
hod_names.display_name
|| ' ('
|| hods.person_number
|| ')',
null) hod_name_number,
hod_email_addresses.email_address hod_email,
NVL2(chiefs.person_id,
chief_names.display_name
|| ' ('
|| chiefs.person_number
|| ')',
null) chief_name_number,
chief_email_addresses.email_address chief_email,
periods.actual_termination_date actual_termination_date,
floor((periods.actual_termination_date - periods.date_start)
/ 365.24) tenure_years,
floor(mod((periods.actual_termination_date - periods.date_start)
/ 30.437, 12)) tenure_months,
floor(mod(mod((periods.actual_termination_date - periods.date_start),
365.24), 30.437)) tenure_days,
periods.notified_termination_date notified_date,
transactions.last_process_date,
transactions.request_submitted_date,
transactions.wave_of_requested,
transactions.wave_of_days,
transactions.wave_of_approved_days,
transactions.request_status,
transactions.last_approved_by,
transactions.last_approved_date,
transactions.pending_with,
transactions.pending_from_date
FROM
per_all_people_f people,
per_periods_of_service periods,
per_person_names_f names_en,
per_persons people_info,
per_all_assignments_f assignments,
hr_all_organization_units_f_vl business_units,
per_org_tr_tmp organizations,
hr_all_organization_units_f_vl departments,
hr_all_organization_units_f_vl divisions,
hr_all_organization_units_f_vl units,
per_jobs_f jobs,
fnd_lookup_values job_meanings,
per_job_family_f_tl job_families,
hr_all_positions_f positions,
hr_all_positions_f_tl pos_names_en,
hr_all_positions_f_tl pos_names_ar,
hr_locations_all position_location,
hr_all_organization_units_f_vl position_organization,
pay_payrolls_tr_tmp payrolls,
-- COST CENTER
pay_cost_allocations_f cost_allocs,
pay_cost_alloc_accounts alloc_accounts,
-- leaving_reason
per_action_occurrences action_occurrences,
per_action_reasons_b action_reasons,
fnd_lookup_values_tl reason_meaning,
-- contracts
per_contracts_f contracts,
fnd_lookup_values_tl contract_types,
-- nationalities
per_citizenships citizenships,
fnd_lookup_values_tl nationalities,
-- id
per_national_identifiers ids,
-- supervisors
per_assignment_supervisors_f supervision,
PER_ALL_ASSIGNMENTS_F sup_ASSIGNMENTS,
per_all_people_f supervisors,
per_person_names_f supervisor_names,
per_grades grades,
per_email_addresses email_addresses,
per_people_legislative_f legals,
fnd_lookup_values_tl education_levels,
per_email_addresses sup_email_addresses,
per_phones phones,
-- parent_position
per_pos_tree_node_v positions_tree,
hr_all_positions_f_tl parent_positions,
-- hods
per_asg_responsibilities hod_resps,
per_all_people_f hods,
per_person_names_f hod_names,
per_email_addresses hod_email_addresses,
per_chiefs_tr_tmp people_chiefs,
per_all_people_f chiefs,
per_person_names_f chief_names,
per_email_addresses chief_email_addresses,
parameters_tr p,
transactions
WHERE 1 = 1
and transactions.transaction_id = p.transaction_id
-- people
AND p.effective_date BETWEEN people.effective_start_date AND people.effective_end_date
and people.person_id = p.person_id
-- periods
AND periods.person_id = people.person_id
--AND periods.actual_termination_date IS NOT NULL
-- names_en
AND p.effective_date BETWEEN names_en.effective_start_date(+) AND names_en.effective_end_date(+)
AND names_en.name_type(+) = 'GLOBAL'
AND names_en.person_id(+) = people.person_id
-- people_info
AND people_info.person_id(+) = people.person_id
-- assignments
AND p.effective_date BETWEEN assignments.effective_start_date AND assignments.effective_end_date
AND assignments.primary_flag = 'Y'
--AND assignments.effective_latest_change = 'Y'
AND assignments.assignment_type IN ('E', 'P')
AND assignments.person_id = p.person_id
-- business_units
AND p.effective_date BETWEEN business_units.effective_start_date(+) AND business_units.effective_end_date(+)
AND business_units.organization_id(+) = assignments.business_unit_id
-- organizations
AND organizations.person_id(+) = people.person_id
AND p.effective_date BETWEEN departments.effective_start_date(+) AND departments.effective_end_date(+)
AND departments.organization_id(+) = organizations.department
AND p.effective_date BETWEEN divisions.effective_start_date(+) AND divisions.effective_end_date(+)
AND divisions.organization_id(+) = organizations.division
AND p.effective_date BETWEEN units.effective_start_date(+) AND units.effective_end_date(+)
AND units.organization_id(+) = organizations.unit
-- jobs
AND p.effective_date BETWEEN jobs.effective_start_date(+) AND jobs.effective_end_date(+)
AND jobs.job_id(+) = assignments.job_id
-- job_meanings
and job_meanings.language(+) = 'US'
and job_meanings.lookup_type(+) = 'MANAGER_LEVEL'
and job_meanings.lookup_code(+) = jobs.manager_level
-- job_families
AND p.effective_date BETWEEN job_families.effective_start_date(+) AND job_families.effective_end_date(+)
AND job_families.language(+) = 'US'
AND job_families.job_family_id(+) = jobs.job_family_id
-- positions
AND p.effective_date BETWEEN positions.effective_start_date(+) AND positions.effective_end_date(+)
AND positions.position_id(+) = assignments.position_id
---- pos_names_en
AND p.effective_date BETWEEN pos_names_en.effective_start_date(+) AND pos_names_en.effective_end_date(+)
AND pos_names_en.language(+) = 'US'
AND pos_names_en.position_id(+) = positions.position_id
---- pos_names_ar
AND p.effective_date BETWEEN pos_names_ar.effective_start_date(+) AND pos_names_ar.effective_end_date(+)
AND pos_names_ar.language(+) = 'AR'
AND pos_names_ar.position_id(+) = positions.position_id
---- position_location
AND p.effective_date BETWEEN position_location.effective_start_date(+) AND position_location.effective_end_date(+)
and position_location.location_id(+) = positions.location_id
---- position_organization
and p.effective_date BETWEEN position_organization.effective_start_date(+) AND position_organization.effective_end_date(+)
and position_organization.organization_id(+) = positions.organization_id
-- payrolls
and payrolls.person_id(+) = people.person_id
-- COST_CENTER
---- COST_ALLOCS
AND p.effective_date BETWEEN cost_allocs.effective_start_date(+) AND cost_allocs.effective_end_date(+)
AND cost_allocs.source_type(+) = 'ORG'
AND cost_allocs.source_id(+) = assignments.organization_id
---- ALLOC_ACCOUNTS
AND alloc_accounts.cost_allocation_record_id(+) = cost_allocs.cost_allocation_record_id
-- leaving_reason
and action_occurrences.action_occurrence_id(+) = periods.action_occurrence_id
and action_reasons.action_reason_id(+) = action_occurrences.action_reason_id
and reason_meaning.language(+) = 'US'
and reason_meaning.lookup_type(+) = 'HRX_FR_REASON_MAPPING'
and reason_meaning.lookup_code(+) = action_reasons.action_reason_code
-- contracts
and p.effective_date BETWEEN contracts.effective_start_date(+) AND contracts.effective_end_date(+)
and contracts.period_of_service_id(+) = periods.period_of_service_id
---- contract_types
and contract_types.language(+) = 'US'
and contract_types.lookup_type(+) = 'CONTRACT_TYPE'
and contract_types.lookup_code(+) = contracts.type
-- nationalities
and p.effective_date between citizenships.date_from(+) and citizenships.date_to(+)
and citizenships.person_id(+) = people.person_id
and nationalities.language(+) = 'US'
and nationalities.lookup_type(+) = 'NATIONALITY'
and nationalities.lookup_code(+) = citizenships.legislation_code
-- ids
and ids.national_identifier_id(+) = people.primary_nid_id
-- supervisors
AND p.effective_date BETWEEN supervision.effective_start_date(+) AND supervision.effective_end_date(+)
AND supervision.PRIMARY_FLAG(+) = 'Y'
AND supervision.manager_type(+) = 'LINE_MANAGER'
and supervision.assignment_id(+) = assignments.assignment_id
---- sup_ASSIGNMENTS
AND p.effective_date BETWEEN sup_ASSIGNMENTS.effective_start_date(+) AND sup_ASSIGNMENTS.effective_end_date(+)
AND sup_ASSIGNMENTS.primary_flag(+) = 'Y'
AND sup_ASSIGNMENTS.assignment_id(+) = supervision.MANAGER_ASSIGNMENT_ID
---- SUPERVISORS
AND p.effective_date BETWEEN supervisors.effective_start_date(+) AND supervisors.effective_end_date(+)
AND supervisors.person_id(+) = sup_ASSIGNMENTS.person_id
---- SUPERVISOR_NAMES
AND p.effective_date BETWEEN supervisor_names.effective_start_date(+) AND supervisor_names.effective_end_date(+)
AND supervisor_names.name_type(+) = 'GLOBAL'
AND supervisor_names.person_id(+) = supervisors.person_id
-- grades
and p.effective_date between grades.effective_start_date(+) and grades.effective_end_date(+)
and grades.grade_id(+) = assignments.grade_id
-- EMAIL_ADDRESSES
AND email_addresses.email_address_id(+) = people.primary_email_id
-- LEGALS
AND p.effective_date BETWEEN legals.effective_start_date(+) AND legals.effective_end_date(+)
AND legals.person_id(+) = people.person_id
-- education_levels
and education_levels.language(+) = 'US'
and education_levels.lookup_type(+) = 'PER_HIGHEST_EDUCATION_LEVEL'
and education_levels.lookup_code(+) = legals.highest_education_level
-- sup_EMAIL_ADDRESSES
AND sup_EMAIL_ADDRESSES.email_address_id(+) = supervisors.primary_email_id
-- phones
and p.effective_date between phones.date_from(+) and phones.date_to(+)
and phones.phone_id(+) = people.primary_phone_id
-- parent_position
AND positions_tree.tree_code(+) = 'MEE_POSITION_TREE'
AND positions_tree.position_id(+) = positions.position_id
and p.effective_date between parent_positions.effective_start_date(+) and parent_positions.effective_end_date(+)
and parent_positions.language(+) = 'US'
and parent_positions.position_id(+) = positions_tree.parent_position_id
-- hod
---- hod_resps
and p.effective_date between nvl(hod_resps.start_date(+), p.effective_date) and nvl(hod_resps.end_date(+), p.effective_date)
and hod_resps.responsibility_type(+) = 'HOD'
and hod_resps.top_department_id(+) = organizations.division
---- hods
AND p.effective_date BETWEEN hods.effective_start_date(+) AND hods.effective_end_date(+)
and hods.person_id(+) = hod_resps.person_id
---- hod_names
AND p.effective_date BETWEEN hod_names.effective_start_date(+) AND hod_names.effective_end_date(+)
AND hod_names.name_type(+) = 'GLOBAL'
AND hod_names.person_id(+) = hods.person_id
-- hod_EMAIL_ADDRESSES
AND hod_EMAIL_ADDRESSES.email_address_id(+) = hods.primary_email_id
-- chief
---- people_chiefs
and people_chiefs.person_id(+) = people.person_id
---- chiefs
AND p.effective_date BETWEEN chiefs.effective_start_date(+) AND chiefs.effective_end_date(+)
and chiefs.person_id(+) = people_chiefs.person_id
---- chief_names
AND p.effective_date BETWEEN chief_names.effective_start_date(+) AND chief_names.effective_end_date(+)
AND chief_names.name_type(+) = 'GLOBAL'
AND chief_names.person_id(+) = chiefs.person_id
-- chief_EMAIL_ADDRESSES
AND chief_EMAIL_ADDRESSES.email_address_id(+) = chiefs.primary_email_id
-- parameters_tr
AND p.person_id = people.person_id
AND NVL(business_units.organization_id, '0') = NVL(p.business_unit_id, NVL(business_units.organization_id, '0'))
AND NVL(units.name, '0') LIKE NVL(('%' || p.unit_name || '%'), NVL(units.name, '0'))
AND NVL(divisions.name, '0') LIKE NVL(('%' || p.division_name || '%'), NVL(divisions.name, '0'))
AND NVL(departments.name, '0') LIKE NVL(('%' || p.organization_name || '%'), NVL(departments.name, '0'))
AND NVL(alloc_accounts.segment3, '0') = NVL(p.cost_center, NVL(alloc_accounts.segment3, '0'))
and nvl(position_location.attribute2, '0') LIKE nvl(('%' || p.region || '%'), nvl(position_location.attribute2, '0'))
AND NVL(people.person_number, '0') = NVL(p.person_number, NVL(people.person_number, '0'))
AND NVL(supervisors.person_number, '0') = NVL(p.supervisor_number, NVL(supervisors.person_number, '0'))
and nvl(null, '0') = nvl(p.band_grade, nvl(null, '0'))
AND NVL(hods.person_number, '0') = NVL(p.hod_number, NVL(hods.person_number, '0'))
AND NVL(chiefs.person_number, '0') = NVL(p.chief_number, NVL(chiefs.person_number, '0'))
AND NVL(periods.actual_termination_date, TRUNC(SYSDATE)) >= NVL(p.start_date, NVL(periods.actual_termination_date, TRUNC(SYSDATE)))
AND NVL(periods.actual_termination_date, TRUNC(SYSDATE)) <= NVL(p.end_date, NVL(periods.actual_termination_date, TRUNC(SYSDATE)))
Editor is loading...
Leave a Comment