Query to extract Department Information

Below query can be used to extract loaded departments.

 select hrc.object_name, 
           hrc.source_system_owner, 
           hrc.source_system_id,
           to_char(pd.effective_start_date,'YYYY/MM/DD') effectivestartdate,
           pd.name departmentname,
           decode(pd.status,'A','Active','I','Inactive',pd.status) status,
           (select internal_location_code
               from hr_locations hl
            where hl.location_id = pd.location_id
           ) location,         
   (  SELECT ORG_INFORMATION1
     FROM HR_ORGANIZATION_INFORMATION_F hoif,
      per_person_names_f_v ppnfv
    WHERE pd.organization_id = hoif.organization_id
AND hoif.ORG_INFORMATION_CONTEXT = 'PER_ORG_MANAGER_INFO'
AND SYSDATE BETWEEN ppnfv.effective_start_date and ppnfv.effective_end_date
AND ppnfv.person_id=hoif.ORG_INFORMATION2
   ) ReportingName,    
   (  SELECT PPNFV.FULL_NAME
     FROM HR_ORGANIZATION_INFORMATION_F hoif,
      per_person_names_f_v ppnfv
    WHERE pd.organization_id = hoif.organization_id
AND hoif.ORG_INFORMATION_CONTEXT = 'PER_ORG_MANAGER_INFO'
AND SYSDATE BETWEEN ppnfv.effective_start_date and ppnfv.effective_end_date
AND ppnfv.person_id=hoif.ORG_INFORMATION2
   ) Manager,
(  SELECT ORG_INFORMATION7
     FROM HR_ORGANIZATION_INFORMATION_F hoif
    WHERE pd.organization_id = hoif.organization_id
AND hoif.ORG_INFORMATION_CONTEXT = 'PER_GL_COST_CENTER_INFO'
   ) RecordIdentifier,    
   ( SELECT fvvs.value_set_code
     FROM HR_ORGANIZATION_INFORMATION_F hoif,
      fnd_vs_value_sets fvvs
    WHERE pd.organization_id = hoif.organization_id
AND hoif.ORG_INFORMATION_CONTEXT = 'PER_GL_COST_CENTER_INFO'
and HOIF.ORG_INFORMATION2  = fvvs.value_set_id 
   ) CompanyValueSet, 
(  SELECT ORG_INFORMATION3
     FROM HR_ORGANIZATION_INFORMATION_F hoif
    WHERE pd.organization_id = hoif.organization_id
AND hoif.ORG_INFORMATION_CONTEXT = 'PER_GL_COST_CENTER_INFO'
   ) Company,
( SELECT fvvs.value_set_code
     FROM HR_ORGANIZATION_INFORMATION_F hoif,
      fnd_vs_value_sets fvvs
    WHERE pd.organization_id = hoif.organization_id
AND hoif.ORG_INFORMATION_CONTEXT = 'PER_GL_COST_CENTER_INFO'
and HOIF.ORG_INFORMATION4  = fvvs.value_set_id 
   ) CostCenterValueSet,
  ( SELECT ORG_INFORMATION1
     FROM HR_ORGANIZATION_INFORMATION_F hoif
    WHERE pd.organization_id = hoif.organization_id
AND hoif.ORG_INFORMATION_CONTEXT = 'PER_GL_COST_CENTER_INFO'
   ) CostCenter,
   (  SELECT PPNFV.FULL_NAME
     FROM HR_ORGANIZATION_INFORMATION_F hoif,
      per_person_names_f_v ppnfv
    WHERE pd.organization_id = hoif.organization_id
AND hoif.ORG_INFORMATION_CONTEXT = 'PER_GL_COST_CENTER_INFO'
AND SYSDATE BETWEEN ppnfv.effective_start_date and ppnfv.effective_end_date
AND ppnfv.person_id=hoif.ORG_INFORMATION6
   ) CostCenterManager,
(  SELECT ORG_INFORMATION1
     FROM HR_ORGANIZATION_INFORMATION_F hoif
    WHERE pd.organization_id = hoif.organization_id
AND hoif.ORG_INFORMATION_CONTEXT = 'PER_WORK_DAY_INFO'
   ) WorkStartTime,    
(  SELECT ORG_INFORMATION2
     FROM HR_ORGANIZATION_INFORMATION_F hoif
    WHERE pd.organization_id = hoif.organization_id
AND hoif.ORG_INFORMATION_CONTEXT = 'PER_WORK_DAY_INFO'
   ) WorkEndTime,
(  SELECT ORG_INFORMATION_NUMBER1    
     FROM HR_ORGANIZATION_INFORMATION_F hoif
    WHERE pd.organization_id = hoif.organization_id
AND hoif.ORG_INFORMATION_CONTEXT = 'PER_WORK_DAY_INFO'
   ) StandardWorkingHours,
(  SELECT flv.meaning
     FROM HR_ORGANIZATION_INFORMATION_F hoif,
      fnd_lookup_values flv
    WHERE pd.organization_id = hoif.organization_id
AND hoif.ORG_INFORMATION_CONTEXT = 'PER_WORK_DAY_INFO'
AND flv.lookup_code = hoif.ORG_INFORMATION4
AND flv.lookup_type = 'FREQUENCY'
AND flv.language ='US'
   ) StandardWorkingFrequency    
 from hrc_integration_key_map hrc,
      per_departments pd
where hrc.object_name like 'Organization'
and pd.organization_id = hrc.surrogate_id