PACKAGE BODY: APPS.EGO_CATG_MAP_UTIL_PKG

Source

1 PACKAGE BODY ego_catg_map_util_pkg AS
2 /* $Header: EGOUCMB.pls 120.4.12010000.2 2009/02/19 22:46:39 akbharga ship $ */
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   l_category_id    mtl_categories_kfv.CATEGORY_ID%TYPE;
15 /*Bug 7184385, changed cursors to c_get_acc_category_id to get correct mapped target category and category_set id */
16 
17 /*
18  CURSOR c_get_category_mapping_id(cp_category_id NUMBER)
19   IS
20    SELECT
21          catg_map_id, target_catg_id
22    FROM EGO_CATG_MAP_DTLS
23    WHERE source_catg_id = cp_category_id
24    AND ROWNUM = 1;
25 
26 
27   CURSOR c_get_catalog_id(cp_catg_map_id NUMBER)
28   IS
29    SELECT
30          target_catg_set_id
31    FROM EGO_CATG_MAP_HDRS_B
32    WHERE catg_map_id = cp_catg_map_id;*/
33 
34 
35 
36 
37 CURSOR c_get_acc_category_id(cp_category_id NUMBER)
38   IS
39    SELECT
40          target_catg_id,target_catg_set_id
41    FROM EGO_CATG_MAP_DTLS det, EGO_CATG_MAP_HDRS_B hdr
42    WHERE det.catg_map_id = hdr.catg_map_id
43    AND source_catg_id = cp_category_id
44    AND ENABLED_FLAG = 'Y'
45    AND ROWNUM = 1;
46 
47 BEGIN
48 
49 
50 
51 
52    /*OPEN c_get_category_mapping_id(cp_category_id => p_gpc_id);
53    FETCH c_get_category_mapping_id INTO l_category_mapping_id, x_acc_category_id;
54    CLOSE c_get_category_mapping_id;
55 
56    OPEN c_get_catalog_id(cp_catg_map_id => l_category_mapping_id);
57    FETCH c_get_catalog_id INTO x_acc_catalog_id;
58    CLOSE c_get_catalog_id;*/
59 
60 /*Bug 7184385, changed select to get correct category_id passed a gpc_id */
61 select mck.category_id into l_category_id
62 from mtl_categories_kfv mck,
63 mtl_category_sets mcs,
64 mtl_default_category_sets_fk_v mdcs
65 where mdcs.functional_area_id = 21
66 and mdcs.category_set_id = mcs.category_set_id
67 and mcs.structure_id = mck.structure_id
68 and mck.segment2 = to_char(p_gpc_id);
69 
70 
71 
72 
73    OPEN c_get_acc_category_id(cp_category_id => l_category_id);
74    FETCH c_get_acc_category_id INTO x_acc_category_id, x_acc_catalog_id;
75    CLOSE c_get_acc_category_id;
76 
77 
78 EXCEPTION
79   WHEN NO_DATA_FOUND THEN
80      x_acc_category_id := NULL;
81      x_acc_catalog_id := NULL;
82 
83 END Get_Alt_Catalog_Ctgr_Mapping;
84 
85 
86 PROCEDURE Get_Item_Catalog_Ctgr_Mapping
87 (
88    P_GPC_ID  IN VARCHAR2,
89    X_ICC_CATEGORY_ID OUT NOCOPY NUMBER
90 )
91 IS
92 
93   l_category_id    mtl_categories_kfv.CATEGORY_ID%TYPE;
94 
95   CURSOR c_get_icc_category_id(cp_category_id NUMBER)
96   IS
97    SELECT
98          target_catg_id
99    FROM EGO_CATG_MAP_DTLS det, EGO_CATG_MAP_HDRS_B hdr
100    WHERE det.catg_map_id = hdr.catg_map_id
101    AND source_catg_id = cp_category_id
102    AND target_catg_set_id = -1
103    AND ENABLED_FLAG = 'Y'
104    AND ROWNUM = 1;
105 
106 BEGIN
107 
108    SELECT CATEGORY_ID
109    INTO l_category_id
110    FROM mtl_categories_kfv
111    WHERE SEGMENT2 = P_GPC_ID
112    AND ROWNUM = 1;
113 
114    OPEN c_get_icc_category_id(cp_category_id => l_category_id);
115    FETCH c_get_icc_category_id INTO x_icc_category_id;
116    CLOSE c_get_icc_category_id;
117 
118 EXCEPTION
119    WHEN NO_DATA_FOUND THEN
120      x_icc_category_id := NULL;
121 
122 END Get_Item_Catalog_Ctgr_Mapping;
123 
124 END EGO_CATG_MAP_UTIL_PKG;