HRMS API: Create Position Hierarchy Element (hr_pos_hierarchy_ele_api.create_pos_hierarchy_ele)

CREATE OR REPLACE PROCEDURE etscust.xx_upload_pos_hier (l_effective_date DATE)
AS
   v_position_structure_id      NUMBER;
   l_pos_structure_version_id   NUMBER;
   v_pos_structure_element_id   NUMBER;
   v_object_version_number      NUMBER;



BEGIN
   FOR i IN (SELECT *
               FROM xx_pos_hier_data
              WHERE org_id IS NOT NULL
                AND pos_id IS NOT NULL
                AND parent_pos_id IS NOT NULL
                AND parent_pos_id <> pos_id
                AND accept_flag <> 'Y')
   LOOP
      l_pos_structure_version_id := NULL;
      BEGIN
         SELECT position_structure_id
           INTO v_position_structure_id
           FROM per_position_structures
          WHERE NAME = 'Etisalat HR Postion Hierarchy'
            AND business_group_id = i.business_group_id;
      EXCEPTION
         WHEN OTHERS
         THEN
            NULL;
      END;
      BEGIN
         SELECT pos_structure_version_id
           INTO l_pos_structure_version_id
           FROM per_pos_structure_versions
          WHERE position_structure_id = v_position_structure_id
            AND business_group_id = i.business_group_id;
      EXCEPTION
         WHEN OTHERS
         THEN
            l_pos_structure_version_id := 0;
      END;
      IF l_pos_structure_version_id <> 0
      THEN
         BEGIN
            hr_pos_hierarchy_ele_api.create_pos_hierarchy_ele
                   (p_parent_position_id            => i.parent_pos_id,
                    p_pos_structure_version_id      => l_pos_structure_version_id,
                    p_subordinate_position_id       => i.pos_id,
                    p_business_group_id             => i.business_group_id,
                    p_hr_installed                  => NULL,
                    p_effective_date                => l_effective_date,
                    p_pos_structure_element_id      => v_pos_structure_element_id,
                    p_object_version_number         => v_object_version_number
                   );
            DBMS_OUTPUT.put_line (   'v_pos_structure_element_id = '
                                  || v_pos_structure_element_id
                                 );
            DBMS_OUTPUT.put_line (   'v_object_version_number = '
                                  || v_object_version_number
                                 );
            UPDATE xx_pos_hier_data
               SET accept_flag = 'Y'
             WHERE org_id = org_id
               AND parent_pos_id = parent_pos_id
               AND pos_id = pos_id
               AND org_name = org_name
               AND accept_flag <> 'Y';
         EXCEPTION
            WHEN OTHERS
            THEN
               NULL;
         END;
      END IF;
   END LOOP;
   COMMIT;
END;