[Home] [Help]
PACKAGE BODY: APPS.EGO_CATEGORY_SET_PUB
Source
1 PACKAGE BODY EGO_CATEGORY_SET_PUB AS
2 /* $Header: EGOCSTPB.pls 120.2 2006/01/25 08:45:23 bparthas noship $ */
3
4 g_pkg_name VARCHAR2(30) := 'EGO_CATEGORY_SET_PUB';
5 g_app_name VARCHAR2(3) := 'EGO';
6 g_current_user_id NUMBER := EGO_SCTX.Get_User_Id();
7 g_current_login_id NUMBER := FND_GLOBAL.Login_Id;
8 g_plsql_err VARCHAR2(17) := 'EGO_PLSQL_ERR';
9 g_pkg_name_token VARCHAR2(8) := 'PKG_NAME';
10 g_api_name_token VARCHAR2(8) := 'API_NAME';
11 g_sql_err_msg_token VARCHAR2(11) := 'SQL_ERR_MSG';
12
13
14
15 -------------------------------------------
16
17 FUNCTION Check_DBI_59_Installed
18 RETURN VARCHAR2
19 IS
20
21 l_dbi_59_is_installed VARCHAR2(1);
22 l_exist NUMBER := 0;
23 BEGIN
24
25 --Pre R12, this method checked for existence of ENI_DENORM_HRCHY
26 -- in user_objects.
27 -- Since R12 is a single APPS delivery, all package objects
28 -- are expected to available, and hence this package has been
29 -- modified to return Y in all cases
30
31 l_dbi_59_is_installed := 'Y';
32 RETURN( l_dbi_59_is_installed );
33 END Check_DBI_59_Installed;
34
35 -------------------------------------------
36
37
38
39 PROCEDURE Process_Category_Set_Assoc
40 ( p_cat_set_id IN NUMBER
41 , p_child_id IN NUMBER
42 , p_parent_id IN NUMBER
43 , p_mode_flag IN VARCHAR2
44 , x_return_status OUT NOCOPY VARCHAR2
45 , x_msg_count OUT NOCOPY NUMBER
46 , x_msg_data OUT NOCOPY VARCHAR2
47
48 )
49 IS
50 l_dbi_59_installed VARCHAR2(1);
51 BEGIN
52
53 l_dbi_59_installed := Check_DBI_59_Installed();
54
55 IF ( l_dbi_59_installed = 'Y' ) THEN
56
57 EXECUTE IMMEDIATE
58 ' BEGIN '||
59 ' ENI_DENORM_HRCHY.INSERT_INTO_STAGING '||
60 ' ( '||
61 ' p_object_type => :p_object_type '||
62 ' , p_object_id => :p_cat_set_id '||
63 ' , p_child_id => :p_child_id '||
64 ' , p_parent_id => :p_parent_id '||
65 ' , p_mode_flag => :p_mode_flag '||
66 ' , x_return_status => :x_return_status '||
67 ' , x_msg_count => :x_msg_count '||
68 ' , x_msg_data => :x_msg_data '||
69 ' ); '||
70 ' END;'
71 USING IN 'CATEGORY_SET',
72 IN p_cat_set_id,
73 IN p_child_id,
74 IN p_parent_id,
75 IN p_mode_flag,
76 OUT x_return_status,
77 OUT x_msg_count,
78 OUT x_msg_data;
79
80
81 IF ( x_return_status = FND_API.g_RET_STS_ERROR ) THEN
82 FND_MESSAGE.Set_Encoded (x_msg_data);
83 APP_EXCEPTION.Raise_Exception;
84 ELSIF ( x_return_status = FND_API.g_RET_STS_UNEXP_ERROR ) THEN
85 FND_MESSAGE.Set_Encoded (x_msg_data);
86 APP_EXCEPTION.Raise_Exception;
87 END IF;
88
89 END IF;
90
91
92 END Process_Category_Set_Assoc;
93
94
95 ---------------------------------------------------
96 -- This method returns 'Y' if default DBI Category Set exists
97 -- ELSE returns 'N'
98 ---------------------------------------------------
99 FUNCTION Check_DBI_Default_Exists
100 RETURN VARCHAR2
101 IS
102
103 l_default_exists VARCHAR2(1);
104 l_exists NUMBER := 0;
105
106 BEGIN
107
108 l_default_exists := 'N';
109
110 select count(1) into l_exists
111 from mtl_default_category_sets
112 where functional_area_id = G_DBI_FUNCTIONAL_AREA_ID;
113
114 if (l_exists <> 0 ) then
115 -- default exists
116 l_default_exists := 'Y';
117 end if;
118
119 RETURN( l_default_exists );
120
121 END Check_DBI_Default_Exists;
122
123
124 ---------------------------------------------------
125 --This method will return the default category set id for DBI
126 --IF it exists, ELSE returns -1
127 ---------------------------------------------------
128
129 FUNCTION Get_DBI_Default_Category_Set
130 RETURN NUMBER
131 IS
132
133 l_default_exists VARCHAR2(1);
134 l_DBI_category_set_id NUMBER := -1;
135 BEGIN
136
137 l_default_exists := Check_DBI_Default_Exists();
138
139 IF( l_default_exists = 'Y' ) THEN
140
141 SELECT category_set_id INTO l_DBI_category_set_id
142 FROM
143 (select * from mtl_default_category_sets
144 where functional_area_id = EGO_CATEGORY_SET_PUB.G_DBI_FUNCTIONAL_AREA_ID
145 and rownum = 1);
146
147 END IF;
148
149 RETURN( l_DBI_category_set_id );
150
151 END ;
152
153 ---------------------------------------------------
154 --This method will return 'Y' if this category is a category included in the default category set for DBI
155 --ELSE returns 'N'
156 ---------------------------------------------------
157 FUNCTION Is_DBI_Catalog_Category
158 (
159 p_Category_Id IN NUMBER
160 )
161 RETURN VARCHAR2
162 IS
163 l_DBI_category_set_id NUMBER;
164 l_is_DBI_catalog_category VARCHAR2(1) := 'N';
165 l_exists NUMBER := 0;
166 BEGIN
167
168 l_DBI_category_set_id := Get_DBI_Default_Category_Set();
169 IF ( l_DBI_category_set_id <> -1 ) THEN
170
171 select count(1) into l_exists
172 from mtl_category_set_valid_cats
173 where category_set_id = l_DBI_category_set_id
174 and category_id = p_Category_Id;
175
176 IF( l_exists <> 0 ) THEN
177 l_is_DBI_catalog_category := 'Y';
178 END IF;
179 END IF;
180
181 RETURN( l_is_DBI_Catalog_Category );
182
183 END Is_DBI_Catalog_Category;
184
185
186 ---------------------------------------------------
187 --Insert into DBI staging table to indicate that this category Description and/or Disable_Date has been changed
188 --IF this category is a DBI category AND DBI 11.5.9 is installed
189 --PARAMETERS
190 --p_mode_flag = 'C' for Description update
191 --p_mode_flag = 'E' for Disable Date update
192 ---------------------------------------------------
193
194 PROCEDURE Process_DBI_Category
195 ( p_category_id IN NUMBER
196 , p_language_code IN VARCHAR2
197 , p_mode_flag IN VARCHAR2
198 , x_return_status OUT NOCOPY VARCHAR2
199 , x_msg_count OUT NOCOPY NUMBER
200 , x_msg_data OUT NOCOPY VARCHAR2
201
202 )
203 IS
204 BEGIN
205
206 Process_DBI_Category( p_category_id,
207 p_mode_flag,
208 x_return_status,
209 x_msg_count,
210 x_msg_data);
211
212 END Process_DBI_Category;
213
214 PROCEDURE Process_DBI_Category
215 ( p_category_id IN NUMBER
216 , p_mode_flag IN VARCHAR2
217 , x_return_status OUT NOCOPY VARCHAR2
218 , x_msg_count OUT NOCOPY NUMBER
219 , x_msg_data OUT NOCOPY VARCHAR2
220
221 )
222 IS
223 l_is_DBI_category VARCHAR2(1);
224 BEGIN
225
226 l_is_DBI_category := Is_DBI_Catalog_Category(p_Category_Id);
227
228 IF (l_is_DBI_category = 'Y') THEN
229 Process_Category_Set_Assoc
230 ( p_cat_set_id => Get_DBI_Default_Category_Set()
231 , p_child_id => p_category_id
232 , p_parent_id => null
233 , p_mode_flag => p_mode_flag
234 , x_return_status => x_return_status
235 , x_msg_count => x_msg_count
236 , x_msg_data => x_msg_data
237 );
238
239 END IF;
240
241
242 END Process_DBI_Category;
243
244
245 END EGO_CATEGORY_SET_PUB;