This query retrieves a list of active employees, their assignment numbers, organizational affiliations, and location information. It filters out non-primary and inactive assignments. The report ensures data consistency by filtering for currently effective records across all relevant tables.
#sqlquery
SELECT
-- SQL4Fusion (An Orbit Analytics Project)
-- Retrieves active employee assignments with location and organization details
HAOU.NAME ORGANIZATION,
PAPF.PERSON_NUMBER EMPLOYEE_NUMBER,
PPNFV.FULL_NAME EMPLOYEE_NAME,
PAAF.ASSIGNMENT_NUMBER,
PAAF.ASSIGNMENT_STATUS_TYPE,
PLF.INTERNAL_LOCATION_CODE,
PLDFT.LOCATION_NAME,
PLF.COUNTRY,
PLF.CATEGORY_CODE
FROM
PER_PERSON_NAMES_F_V PPNFV,
PER_ALL_ASSIGNMENTS_M PAAF,
HR_ALL_ORGANIZATION_UNITS HAOU,
PER_ALL_PEOPLE_F PAPF,
PER_LOCATIONS PLF,
PER_LOCATION_DETAILS_F PLDF,
PER_LOCATION_DETAILS_F_TL PLDFT
WHERE
PPNFV.PERSON_ID = PAPF.PERSON_ID
AND PAPF.PERSON_ID = PAAF.PERSON_ID
AND PAAF.ASSIGNMENT_TYPE NOT IN ('ET','CT','PT')
AND PAAF.ASSIGNMENT_STATUS_TYPE = 'ACTIVE'
AND PAAF.ORGANIZATION_ID = HAOU.ORGANIZATION_ID
AND PAAF.PRIMARY_FLAG = 'Y'
AND PAAF.EFFECTIVE_LATEST_CHANGE = 'Y'
AND PAAF.LOCATION_ID = PLF.LOCATION_ID
AND PLDF.LOCATION_ID = PAAF.LOCATION_ID
AND PLDFT.LOCATION_DETAILS_ID = PLDF.LOCATION_DETAILS_ID
AND PLDFT.LANGUAGE = USERENV('LANG')
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 PAAF.EFFECTIVE_START_DATE AND PAAF.EFFECTIVE_END_DATE
AND SYSDATE BETWEEN PLDF.EFFECTIVE_START_DATE AND PLDF.EFFECTIVE_END_DATE
AND SYSDATE BETWEEN PLDFT.EFFECTIVE_START_DATE AND PLDFT.EFFECTIVE_END_DATE;