FUNCTION: APPS.IEC_GETLOCATIONHEIRARCHY

Source

1 FUNCTION      Iec_GetLocationHeirarchy(p_loc_id IN NUMBER)
2                   RETURN VARCHAR2
3                IS
4                   l_loc_name          VARCHAR2(500);
5                   l_loc_type          VARCHAR2(500);
6                   l_loc_id            NUMBER(15);
7                   l_parent_loc_id     NUMBER(15);
8 
9                   l_heirarchy_str     VARCHAR2(4000);
10                BEGIN
11 
12                   -- Get name for location of leaf node
13                   SELECT LOCATION_AREA_NAME, PARENT_LOCATION_AREA_ID
14                   INTO l_loc_name, l_parent_loc_id
15                   FROM JTF_LOC_AREAS_VL
16                   WHERE LOCATION_AREA_ID = p_loc_id;
17 
18                   l_heirarchy_str := l_loc_name;
19                   l_loc_id := l_parent_loc_id;
20 
21                   WHILE l_loc_id IS NOT NULL LOOP
22                      SELECT LOCATION_AREA_NAME, LOCATION_TYPE_CODE, PARENT_LOCATION_AREA_ID
23                      INTO l_loc_name, l_loc_type, l_parent_loc_id
24                      FROM JTF_LOC_AREAS_VL
25                      WHERE LOCATION_AREA_ID = l_loc_id;
26 
27                      l_heirarchy_str := l_loc_name || '/' || l_heirarchy_str;
28                      l_loc_id := l_parent_loc_id;
29 
30                      EXIT WHEN L_loc_type = 'AREA1';
31 
32                   END LOOP;
33                   RETURN l_heirarchy_str;
34                END;