Employee Document Details (SQL Script)

Human Capital Management (HCM) Human Resources (HRMS)
1

This Employee Document Details query displays active employee assignment details along with their issued documents and organizational information as of today, helping HR and management verify current employee statuses and associated documentation for compliance and operational purposes.

#sqlquery

SELECT 
	--SQL4Fusion (An Orbit Analytics Project)
    --Retrieve active employee document details with assignment and organization info.
	PAPF.PERSON_NUMBER,
	PAAM.ASSIGNMENT_STATUS_TYPE,
	HAOU.NAME ORGANIZATION_NAME,
	PAAM.ASSIGNMENT_NUMBER,
	PPNFV.FULL_NAME EMPLOYEE_NAME,
	HDOR.DOCUMENT_NAME,
	HDTT.DOCUMENT_TYPE,
	HDTT.SEED_DATA_SOURCE,
	HDOR.ISSUED_DATE,
	HDOR.ISSUING_LOCATION,
	HDOR.ISSUING_COUNTRY,
	HDOR.DATE_FROM DOC_DATE_FROM,
	HDOR.DATE_TO DOC_DATE_TO
FROM 
	PER_PERSON_NAMES_F_V PPNFV,
	PER_ALL_ASSIGNMENTS_M PAAM,
	HR_ALL_ORGANIZATION_UNITS HAOU,
	PER_ALL_PEOPLE_F PAPF,
	HR_DOCUMENTS_OF_RECORD HDOR,
	HR_DOCUMENT_TYPES_TL HDTT
WHERE 
	PPNFV.PERSON_ID=PAPF.PERSON_ID
	AND HDOR.PERSON_ID=PPNFV.PERSON_ID
	AND HDOR.DOCUMENT_TYPE_ID=HDTT.DOCUMENT_TYPE_ID
	AND PAPF.PERSON_ID = PAAM.PERSON_ID
	AND PAAM.ASSIGNMENT_TYPE NOT IN ('ET','CT','PT')
	AND PAAM.ASSIGNMENT_STATUS_TYPE = 'ACTIVE'
	AND PAAM.ORGANIZATION_ID = HAOU.ORGANIZATION_ID
	AND PAAM.PRIMARY_FLAG = 'Y'
	AND PAAM.EFFECTIVE_LATEST_CHANGE = 'Y'
	AND SYSDATE BETWEEN PPNFV.EFFECTIVE_START_DATE
	AND PPNFV.EFFECTIVE_END_DATE
	AND SYSDATE BETWEEN PAPF.EFFECTIVE_START_DATE
	AND PAPF.EFFECTIVE_END_DATE
	AND SYSDATE BETWEEN PAAM.EFFECTIVE_START_DATE
	AND PAAM.EFFECTIVE_END_DATE
	AND HDTT.LANGUAGE = 'US'
    AND PPNFV.NAME_TYPE = 'GLOBAL'