SQL query to pull the EEO Information

We have a seeded EEO report which pulls the EEO information that needs to be submitted to the government agencies in US. If there is a need to get the breakdown of details by location or want to validate if all counts are reported correctly there isn’t any seeded functionality available.

SQL Query to pull the EEO information:

SELECT pap.person_number
	,ppnf.full_name
	,CASE 
		WHEN trim(com_inf.lle_information_number1) IS NULL
			THEN hrloc.address_line_1
		ELSE loc_proxy.address_line_1
		END address_line_1
	,CASE 
		WHEN trim(com_inf.lle_information_number1) IS NULL
			THEN hrloc.address_line_2
		ELSE loc_proxy.address_line_2
		END address_line_2
	,CASE 
		WHEN trim(com_inf.lle_information_number1) IS NULL
			THEN hrloc.town_or_city
		ELSE loc_proxy.town_or_city
		END town_or_city
	,CASE 
		WHEN trim(com_inf.lle_information_number1) IS NULL
			THEN hrloc.region_1
		ELSE loc_proxy.region_1
		END region_1
	,CASE 
		WHEN trim(com_inf.lle_information_number1) IS NULL
			THEN hrloc.region_2
		ELSE loc_proxy.region_2
		END region_2
	,CASE 
		WHEN trim(com_inf.lle_information_number1) IS NULL
			THEN hrloc.postal_code
		ELSE loc_proxy.postal_code
		END postal_code
	,eeo_inf.lle_information2 EEO1_UNIT_NUMBER
	,eeo_inf.lle_information8 REPORTED_LAST_YEAR
	,us_inf.lle_information4 DUNS_NUMBER
	,us_inf.lle_information5 NAICS_NUMBER
	,pjl.information2 EEO1_CATEGORY
	,pjl.information1 FLSA_STATUS
	,ethn.meaning ETHNICITY
	,pplf.sex GENDER
	,Paa.Assignment_Status_Type
	,to_char(paa.effective_start_date, 'MM/DD/YYYY') effective_start_date
FROM per_all_assignments_m paa
	,per_ethnicities pe
	,per_people_legislative_f pplf
	,per_job_leg_f pjl
	,hcm_lookups ethn
	,per_location_leg_f com_inf
	,per_Location_Leg_F us_inf
	,hr_locations_all_f_vl hrloc
	,per_location_leg_f eeo_inf
	,per_all_people_f pap
	,per_person_names_f ppnf
	,hr_locations_all_f_vl loc_proxy
WHERE pap.person_id = paa.person_id
	AND paa.person_id = pe.person_id(+)
	AND pe.primary_flag(+) = 'Y'
	AND paa.person_id = pplf.person_id(+)
	AND paa.person_id = ppnf.person_id
	AND paa.Location_Id = us_inf.Location_Id(+)
	AND ppnf.name_type = 'GLOBAL'
	AND trunc(sysdate) BETWEEN ppnf.effective_start_date
		AND ppnf.effective_end_date
	AND paa.job_id = pjl.job_id(+)
	AND pe.ethnicity = ethn.lookup_code(+)
	AND paa.location_id = com_inf.location_id(+)
	AND paa.Location_Id = hrloc.Location_Id
	AND Paa.Primary_Flag = 'Y'
	--AND Paa.Assignment_Status_Type in ('ACTIVE','SUSPENDED')
	AND Paa.Assignment_Type = 'E'
	AND Paa.Effective_Latest_Change = 'Y'
	AND Pjl.Legislation_Code(+) = 'US'
	AND Pplf.Legislation_Code(+) = 'US'
	AND Com_Inf.Legislation_Code(+) = 'US'
	AND Pe.Legislation_Code = 'US'
	AND Ethn.Lookup_Type = 'PER_ETHNICITY'
	AND Ethn.Tag LIKE '%+US%'
	AND Ethn.Lookup_Code IN ('1','2','3','4','5','6','7')
	AND Com_Inf.Lle_Information_Category(+) = 'HRX_US_LOC_EEO_VETS_INF'
	AND Us_Inf.Lle_Information5(+) IS NOT NULL
	AND Us_Inf.Lle_Information_Category(+) = 'HRX_US_REPORTING_INFORMATION'
	AND us_inf.Legislation_code(+) = 'US'
	AND Paa.Location_Id = eeo_inf.Location_Id(+)
	AND Eeo_Inf.Lle_Information_Category(+) = 'HRX_US_EEO1_INFORMATION'
	AND Eeo_Inf.Legislation_Code(+) = 'US'
	AND ((Eeo_Inf.Lle_Information8 = 'N' AND Eeo_Inf.Lle_Information2 IS NULL)
		OR (Eeo_Inf.Lle_Information8 = 'Y' AND Eeo_Inf.Lle_Information2 IS NOT NULL))
	AND sysdate BETWEEN pap.Effective_Start_Date
		AND pap.Effective_End_Date
	AND sysdate BETWEEN Pplf.Effective_Start_Date(+)
		AND Pplf.Effective_End_Date(+)
	AND sysdate BETWEEN Pjl.Effective_Start_Date(+)
		AND Pjl.Effective_End_Date(+)
	AND sysdate BETWEEN Com_Inf.Effective_Start_Date(+)
		AND Com_Inf.Effective_End_Date(+)
	AND sysdate BETWEEN hrloc.Effective_Start_Date
		AND hrloc.Effective_End_Date
	AND sysdate BETWEEN Us_Inf.Effective_Start_Date(+)
		AND Us_Inf.Effective_End_Date(+)
	AND sysdate BETWEEN paa.effective_start_date
		AND paa.effective_end_date
	AND paa.effective_start_date BETWEEN '2022-01-01' AND '2022-01-31'
	AND trunc(sysdate) BETWEEN eeo_inf.effective_start_date(+)
		AND eeo_inf.effective_end_date(+)
	AND loc_proxy.location_id(+) = com_inf.lle_information_number1
	AND sysdate BETWEEN loc_proxy.Effective_Start_Date(+)
		AND loc_proxy.Effective_End_Date(+)
ORDER BY papf.person_number