Untitled

mail@pastecode.io avatar
unknown
sql
7 months ago
43 kB
4
Indexable
Never
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)))




Leave a Comment