1 Reply Latest reply: Jan 30, 2013 4:34 AM by Srini-999 RSS

    Need HRMS organization structure Query

    948041
      Hi,
      I need HRMS organization structure query so that if any one login with there ID it has to default to zone level....(If any one from region level login with their ID's it has to deafult to Zone)...any fast reply apreciated...
        • 1. Re: Need HRMS organization structure Query
          Srini-999
          Hi ,

          Use these query's ,i hope it's useful for u.

          SELECT LPAD (' ', 10 * (LEVEL - 1)) || org.NAME HIERARCHY,
          org.organization_id
          FROM hr_all_organization_units org, per_org_structure_elements pose
          WHERE 1 = 1
          AND org.organization_id = pose.organization_id_child
          AND pose.org_structure_version_id = 61
          --and org.name  like '201.Financiale Services'
          START WITH pose.organization_id_parent = 81
          -- Orgnization of parent id -- provide the id from which level the downward hierarchy should be displaed
          CONNECT BY PRIOR pose.organization_id_child = pose.organization_id_parent
          ORDER SIBLINGS BY org.location_id, pose.organization_id_child

          select distinct organization_id_parent ,org_structure_version_id from per_org_structure_elements


          SELECT papf1.full_name leve1_full_name
          , papf1.employee_number level1_empno
          , papf2.full_name leve2_full_name
          , papf2.employee_number level2_empno
          FROM hr.per_all_people_f papf1
          , hr.per_all_assignments_f paaf1
          , hr.per_all_assignments_f paaf2
          , hr.per_all_people_f papf2
          WHERE papf1.person_id = paaf1.person_id
          AND paaf1.supervisor_id = papf2.person_id
          AND papf2.person_id = paaf2.person_id
          AND SYSDATE BETWEEN paaf1.effective_start_date
          AND NVL(paaf1.effective_end_date, SYSDATE + 1)
          AND SYSDATE BETWEEN paaf2.effective_start_date
          AND NVL(paaf2.effective_end_date, SYSDATE + 1)
          AND SYSDATE BETWEEN NVL(papf1.effective_start_date, SYSDATE - 1)
          AND NVL(papf1.effective_end_date, SYSDATE + 1)
          AND SYSDATE BETWEEN NVL(papf2.effective_start_date, SYSDATE - 1)
          AND NVL(papf2.effective_end_date, SYSDATE + 1)
          AND paaf1.primary_flag = 'Y'
          AND paaf1.assignment_type = 'E'
          AND papf1.current_employee_flag = 'Y'
          AND NVL(papf2.current_employee_flag, 'Y') = 'Y'
          AND papf1.EMPLOYEE_NUMBER = 1234
          CONNECT BY PRIOR paaf1.supervisor_id = papf2.person_id;

          Regards,
          Srinivas