Department Details (SQL Script)

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

This Department Details report provides detailed information on departments within the organization, including department names, effective dates, department type, location details and associated business groups. It ensures only active and valid department records are included by filtering based on effective date ranges and classification codes.

#sqlquery

SELECT
--SQL4Fusion (An Orbit Analytics Project)
--This query retrieves Department details
  -- Department Details
  HAOUFT.NAME                       DEPARTMENT_NAME,
  HOUCF.CATEGORY_CODE               CATEGORY,
  HAOUF.TYPE,
  HOUCF.STATUS,
  HOUCF.CREATED_BY,
  HAOUF.EFFECTIVE_START_DATE,
  HAOUF.EFFECTIVE_END_DATE,
  -- Location Details
  HLAF.INTERNAL_LOCATION_CODE,
  HLAF.LOCATION_NAME,
  HLAF.ADDRESS_LINE_1,
  HLAF.ADDRESS_LINE_2,
  HLAF.TOWN_OR_CITY,
  HLAF.COUNTRY,
  HLAF.POSTAL_CODE
FROM
  HR_ALL_ORGANIZATION_UNITS_F          HAOUF,
  HR_ORG_UNIT_CLASSIFICATIONS_F        HOUCF,
  HR_ORGANIZATION_UNITS_F_TL           HAOUFT,
  HR_LOCATIONS_ALL_F_VL                HLAF
WHERE
  1 = 1
  AND HAOUF.ORGANIZATION_ID             = HOUCF.ORGANIZATION_ID
  AND HOUCF.CLASSIFICATION_CODE         = 'DEPARTMENT'
  AND HAOUF.ORGANIZATION_ID             = HAOUFT.ORGANIZATION_ID
  AND HAOUFT.LANGUAGE                   = USERENV ('LANG')
  AND HAOUFT.EFFECTIVE_START_DATE       = HAOUF.EFFECTIVE_START_DATE
  AND HAOUFT.EFFECTIVE_END_DATE         = HAOUF.EFFECTIVE_END_DATE
  AND HLAF.LOCATION_ID                  = HAOUF.LOCATION_ID
  AND SYSDATE BETWEEN HOUCF.EFFECTIVE_START_DATE AND HOUCF.EFFECTIVE_END_DATE
  AND SYSDATE BETWEEN HLAF.EFFECTIVE_START_DATE AND HLAF.EFFECTIVE_END_DATE
  ORDER BY HOUCF.CATEGORY_CODE , 
           HAOUFT.NAME