You are currently viewing Query for Rehire Recommendation Flag
Query for Rehire Recommendation Flag

In this article we will look into the Rehire Recommendation Flag while terminating an employee and also a query to get this information.

The Rehire Recommendation Flag and the Reason will be available on the Manage Work Relationship while performing Termination action.

image 61 1024x473 - Query for Rehire Recommendation Flag

Recommended for Rehire Flag and Reason fields are important in decision making when the same candidate applies for the company in future.

These flags reside on PER_PERIODS_OF_SERVICE table and can be queried from that table.

SQL Query to pull Rehire Recommendation Flags:

SELECT papf.person_number, per_extract_utility.get_decoded_lookup('ORA_PER_YES_NO_NOT_SPECIFIED', ppos.rehire_recommendation) rehire_flag,
	(
		CASE 
			WHEN ppos.rehire_recommendation = 'N'
				THEN per_extract_utility.get_decoded_lookup('PER_PDS_REHIRE_REASON', ppos.rehire_reason)
			ELSE NULL
			END
		) Rehire_Recommend
FROM per_periods_of_service ppos, per_all_people_f papf
WHERE ppos.person_id = papf.person_id
  and sysdate between papf.effective_start_date and papf.effective_end_date
  and ppos.date_start = (
		SELECT MAX(ppos1.date_start)
		FROM per_periods_of_service ppos1
		WHERE ppos1.person_id = ppos.person_id
			AND ppos1.period_type = 'E'
			AND ppos1.date_start <= trunc(sysdate)
			AND ppos1.actual_termination_date IS NOT NULL
		)