Cacher is the code snippet organizer for pro developers

FUNCTION get_requesting_dept_type (p_org_id NUMBER)
      RETURN VARCHAR2
   IS
      v_result                   VARCHAR2 (150);
      v_organization_id_parent   NUMBER;
      v_org_id                   NUMBER;
      CURSOR cur
               IS
                SELECT organization_id_child                            
                  FROM per_org_structure_elements_v
                 WHERE org_structure_version_id =
                                                xxcma_custom_pkg.get_primary_struct_version_id
                   AND organization_id_parent = 104
                   AND organization_id_child NOT IN (
                                                      SELECT organization_id
                                                        FROM per_assignments_f
                                                       WHERE 1 = 1
                                                         AND SYSDATE BETWEEN effective_start_date AND effective_end_date
                                                         AND primary_flag = 'Y'
                                                         AND hr_general.decode_position_latest_name (position_id) LIKE
                                                                                                               'Director%');
   BEGIN
      v_org_id := p_org_id;

      LOOP
         SELECT organization_id_parent
           INTO v_organization_id_parent
           FROM per_org_structure_elements_v
          WHERE org_structure_version_id =
                                xxcma_custom_pkg.get_primary_struct_version_id
            AND organization_id_child = v_org_id;

         IF v_organization_id_parent = 104           -- Board of Commissioners
         THEN
         
          FOR i IN cur
         LOOP
            IF v_org_id = i.organization_id_child
            THEN
               RETURN 'CH';
            END IF;
         END LOOP;
         
            IF v_org_id = 106
            THEN
               RETURN 'CH';                                -- Chairman office
            ELSIF v_org_id = 6095
            THEN
               RETURN 'CS';                             -- Corporate Services
            ELSE
               RETURN 'OTHERS';                                 -- Any others
            END IF;
         END IF;

         v_org_id := v_organization_id_parent;
         EXIT WHEN v_org_id IN (104, 0);
      END LOOP;

      RETURN 'OTHERS';
   EXCEPTION
      WHEN OTHERS
      THEN
         RETURN 'OTHERS';
   END get_requesting_dept_type;

SELECT organization_id_child, D_CHILD_NAME
          -- INTO v_organization_id_parent
           FROM per_org_structure_elements_v
          WHERE org_structure_version_id =
                                xxcma_custom_pkg.get_primary_struct_version_id
           -- AND organization_id_child = v_org_id;
           and organization_id_parent=104 
           and organization_id_child 
           
           not in ( SELECT ORGANIZATION_ID
                                    FROM  PER_ASSIGNMENTS_F
                                    WHERE 1=1
                                    --USERENV('LANG')='US'
                                    AND sysdate between  EFFECTIVE_START_DATE and EFFECTIVE_END_DATE
                                    AND PRIMARY_FLAG = 'Y'
                                    AND HR_GENERAL.DECODE_POSITION_LATEST_NAME(POSITION_ID) like 'Director%')