PACKAGE BODY: APPS.EGO_CATG_MAP_UTIL_PKG

Source

1 PACKAGE BODY ego_catg_map_util_pkg AS
2 /* $Header: EGOUCMB.pls 120.4 2007/09/26 08:33:59 ysireesh noship $ */
3 
4 -- Given a ACC Id, to provide the ACC Id, ACC name
5 PROCEDURE Get_Alt_Catalog_Ctgr_Mapping
6 (
7     P_GPC_ID  IN NUMBER,
8     X_ACC_CATEGORY_ID OUT NOCOPY NUMBER,
9     X_ACC_CATALOG_ID OUT NOCOPY NUMBER
10 )
11 IS
12 
13    l_category_mapping_id NUMBER;
14 
15    CURSOR c_get_category_mapping_id(cp_category_id NUMBER)
16   IS
17    SELECT
18          catg_map_id, target_catg_id
19    FROM EGO_CATG_MAP_DTLS
20    WHERE source_catg_id = cp_category_id
21    AND ROWNUM = 1;
22 
23   CURSOR c_get_catalog_id(cp_catg_map_id NUMBER)
24   IS
25    SELECT
26          target_catg_set_id
27    FROM EGO_CATG_MAP_HDRS_B
28    WHERE catg_map_id = cp_catg_map_id;
29 
30 BEGIN
31 
32    OPEN c_get_category_mapping_id(cp_category_id => p_gpc_id);
33    FETCH c_get_category_mapping_id INTO l_category_mapping_id, x_acc_category_id;
34    CLOSE c_get_category_mapping_id;
35 
36    OPEN c_get_catalog_id(cp_catg_map_id => l_category_mapping_id);
37    FETCH c_get_catalog_id INTO x_acc_catalog_id;
38    CLOSE c_get_catalog_id;
39 
40 EXCEPTION
41   WHEN NO_DATA_FOUND THEN
42      x_acc_category_id := NULL;
43      x_acc_catalog_id := NULL;
44 
45 END Get_Alt_Catalog_Ctgr_Mapping;
46 
47 
48 PROCEDURE Get_Item_Catalog_Ctgr_Mapping
49 (
50    P_GPC_ID  IN VARCHAR2,
51    X_ICC_CATEGORY_ID OUT NOCOPY NUMBER
52 )
53 IS
54 
55   l_category_id    mtl_categories_kfv.CATEGORY_ID%TYPE;
56 
57   CURSOR c_get_icc_category_id(cp_category_id NUMBER)
58   IS
59    SELECT
60          target_catg_id
61    FROM EGO_CATG_MAP_DTLS det, EGO_CATG_MAP_HDRS_B hdr
62    WHERE det.catg_map_id = hdr.catg_map_id
63    AND source_catg_id = cp_category_id
64    AND target_catg_set_id = -1
65    AND ENABLED_FLAG = 'Y'
66    AND ROWNUM = 1;
67 
68 BEGIN
69 
70    SELECT CATEGORY_ID
71    INTO l_category_id
72    FROM mtl_categories_kfv
73    WHERE SEGMENT2 = P_GPC_ID
74    AND ROWNUM = 1;
75 
76    OPEN c_get_icc_category_id(cp_category_id => l_category_id);
77    FETCH c_get_icc_category_id INTO x_icc_category_id;
78    CLOSE c_get_icc_category_id;
79 
80 EXCEPTION
81    WHEN NO_DATA_FOUND THEN
82      x_icc_category_id := NULL;
83 
84 END Get_Item_Catalog_Ctgr_Mapping;
85 
86 END EGO_CATG_MAP_UTIL_PKG;