Employer and AssignmentID.sql

-- Simply identify which Employer or Company an Employee belongs using these quick queries for Oracle HCM Cloud

SELECT DISTINCT PPNF.FULL_NAME "Employee"
	, PAAM.ASSIGNMENT_NUMBER "AssignmentID"
	, HOUFT.NAME "Employer"
FROM PER_ALL_ASSIGNMENTS_M PAAM
	, HR_ORGANIZATION_UNITS_F_TL HOUFT
	, PER_PERSON_NAMES_F PPNF
WHERE 1 = 1
	AND PAAM.LEGAL_ENTITY_ID = HOUFT.ORGANIZATION_ID
	AND PPNF.PERSON_ID = PAAM.PERSON_ID
	AND PPNF.NAME_TYPE = 'GLOBAL'
	AND PAAM.EFFECTIVE_LATEST_CHANGE = 'Y'
	AND PAAM.PRIMARY_FLAG = 'Y'
	AND PAAM.ASSIGNMENT_TYPE NOT LIKE '%T'
	AND HOUFT.LANGUAGE = HOUFT.SOURCE_LANG
	AND SYSDATE BETWEEN HOUFT.EFFECTIVE_START_DATE AND HOUFT.EFFECTIVE_END_DATE
	AND SYSDATE BETWEEN PPNF.EFFECTIVE_START_DATE AND PPNF.EFFECTIVE_END_DATE
	AND PPNF.FULL_NAME LIKE NVL(:Employee, PPNF.FULL_NAME)
	AND PAAM.ASSIGNMENT_NUMBER LIKE NVL(:AssignmentID, PAAM.ASSIGNMENT_NUMBER)
	AND HOUFT.NAME LIKE NVL(:Employer, HOUFT.NAME)
ORDER BY PAAM.ASSIGNMENT_NUMBER

/*
--List of Employers
SELECT DISTINCT HOUFT.NAME
FROM HR_ORGANIZATION_UNITS_F_TL HOUFT
	, HR_ORG_UNIT_CLASSIFICATIONS_F HOUCF
WHERE 1 = 1
	AND HOUCF.ORGANIZATION_ID = HOUFT.ORGANIZATION_ID
	AND HOUFT.LANGUAGE = HOUFT.SOURCE_LANG
	AND HOUCF.CLASSIFICATION_CODE = 'HCM_LEMP'
	AND SYSDATE BETWEEN HOUFT.EFFECTIVE_START_DATE AND HOUFT.EFFECTIVE_END_DATE
	AND SYSDATE BETWEEN HOUCF.EFFECTIVE_START_DATE AND HOUCF.EFFECTIVE_END_DATE
ORDER BY HOUFT.NAME
*/