API to Create Organization Hierarchy

DECLARE

   l_effective_date             DATE := TO_DATE ('01-JAN-1951', 'DD-MON-YYYY');

   l_org_structure_version_id   NUMBER := NULL;

   l_business_group_id          NUMBER := NULL;

   l_inactive_org_warning       BOOLEAN;

   l_org_structure_element_id   NUMBER := NULL;

   l_object_version_number      NUMBER := NULL;

   l_org_child_id               NUMBER := NULL;

   l_org_parent_id              NUMBER := NULL;

BEGIN

   -- Get Structure Version ID

   BEGIN

      SELECT posv.org_structure_version_id, pos.business_group_id

        INTO l_org_structure_version_id, l_business_group_id

        FROM per_organization_structures pos, per_org_structure_versions posv

       WHERE UPPER (pos.name) = 'XXXX ORGANIZATION HIERARCHY'

             AND pos.primary_structure_flag = 'Y'

             AND posv.organization_structure_id =

                    pos.organization_structure_id

             AND posv.version_number = 1

             AND pos.business_group_id =

                    FND_PROFILE.VALUE ('PER_BUSINESS_GROUP_ID');

   EXCEPTION

      WHEN OTHERS

      THEN

         l_org_structure_version_id := NULL;

         l_business_group_id := NULL;

   END;

   -- Get Organization Parent

   BEGIN

      SELECT organization_id

        INTO l_org_parent_id

        FROM hr_all_organization_units

       WHERE UPPER (name) = 'SECURITY AND ROYAL GUARD'

             AND SYSDATE BETWEEN date_from

                             AND NVL (date_to,

                                      TO_DATE ('31-DEC-4712', 'DD-MON-YYYY'));

   EXCEPTION

      WHEN OTHERS

      THEN

         l_org_parent_id := NULL;

   END;

   -- Get Organization Child

   BEGIN

      SELECT organization_id

        INTO l_org_child_id

        FROM hr_all_organization_units

       WHERE UPPER (name) =

                '1021106 -LEBANON PALACE - BAHAR SAFI-SECURITY AND ROYAL GUARD'

             AND SYSDATE BETWEEN date_from

                             AND NVL (date_to,

                                      TO_DATE ('31-DEC-4712', 'DD-MON-YYYY'));

   EXCEPTION

      WHEN OTHERS

      THEN

         l_org_child_id := NULL;

   END;

   IF     l_org_structure_version_id IS NOT NULL

      AND l_org_child_id IS NOT NULL

      AND l_org_parent_id IS NOT NULL

   THEN

      BEGIN

         HR_HIERARCHY_ELEMENT_API.

          create_hierarchy_element (

            p_validate                   => FALSE,

            p_effective_date             => l_effective_date,

            p_organization_id_parent     => l_org_parent_id,

            p_org_structure_version_id   => l_org_structure_version_id,

            p_organization_id_child      => l_org_child_id,

            p_business_group_id          => l_business_group_id,

            p_pos_control_enabled_flag   => 'N',

            p_inactive_org_warning       => l_inactive_org_warning,

            p_org_structure_element_id   => l_org_structure_element_id,

            p_object_version_number      => l_object_version_number);

         COMMIT;

         DBMS_OUTPUT.

          put_line (

            l_org_structure_element_id

            || ' has been created successfully !!! ');

      EXCEPTION

         WHEN OTHERS

         THEN

            DBMS_OUTPUT.put_line ('Inner Block Exception: ' || SQLERRM);

      END;

   ELSE

      DBMS_OUTPUT.put_line ('ID''s not found !!!!');

   END IF;

EXCEPTION

   WHEN OTHERS

   THEN

      DBMS_OUTPUT.put_line ('Main Block Exception: ' || SQLERRM);

END;