[Home] [Help]
PACKAGE BODY: APPS.FEM_UTILS
Source
1 PACKAGE BODY FEM_UTILS AS
2 --$Header: FEMUTILB.pls 120.0.12010000.2 2008/10/10 22:23:00 huli ship $
3 --=============================================================================
4
5
6 -- 000000000111111111122222222223333333333444444444455555555556
7 -- 123456789012345678901234567890123456789012345678901234567890
8 G_APP_NAME CONSTANT VARCHAR2(4) := 'FEM';
9 G_PKG_NAME CONSTANT VARCHAR2(25) := 'FEM_UTILS';
10 G_MODULE_NAME CONSTANT VARCHAR2(40) := 'fem.plsql.' || G_PKG_NAME || '.';
11
12 G_LOG_STATEMENT CONSTANT NUMBER := fnd_log.level_statement;
13 G_LOG_PROCEDURE CONSTANT NUMBER := fnd_log.level_procedure;
14 G_LOG_EVENT CONSTANT NUMBER := fnd_log.level_event;
15 G_LOG_EXCEPTION CONSTANT NUMBER := fnd_log.level_exception;
16 G_LOG_ERROR CONSTANT NUMBER := fnd_log.level_error;
17 G_LOG_UNEXPECTED CONSTANT NUMBER := fnd_log.level_unexpected;
18
19
20 -- *******************************************************************************************
21 -- name : set_master_err_state
22 -- Function : provide simple mechanism for updating master error state..
23 -- the idea behind the error state is that it can only be
24 -- >increased<. when the value of the error state is increased, it indicates
25 -- a more fatal condition. So the smallest error code should be the most
26 -- innocuous, the largest number the most fatal.
27 -- this function also acts as a wrapper for calls to the error message API
28 -- to force use of this function for all errors generated.
29 -- Parameters
30 -- IN
31 -- err_state IN NUMBER
32 -- - current error state we wish to set..
33 --
34 -- HISTORY
35 -- 22-Apr-2004 rjking created
36 --
37 -- *******************************************************************************************
38 PROCEDURE set_master_err_state( p_master_err_state IN OUT NOCOPY NUMBER,
39 err_state IN NUMBER,
40 p_app_name IN VARCHAR2,
41 p_msg_name IN VARCHAR2,
42 p_token1 IN VARCHAR2 DEFAULT NULL,
43 p_value1 IN VARCHAR2 DEFAULT NULL,
44 p_trans1 IN VARCHAR2 DEFAULT NULL,
45 p_token2 IN VARCHAR2 DEFAULT NULL,
46 p_value2 IN VARCHAR2 DEFAULT NULL,
47 p_trans2 IN VARCHAR2 DEFAULT NULL,
48 p_token3 IN VARCHAR2 DEFAULT NULL,
49 p_value3 IN VARCHAR2 DEFAULT NULL,
50 p_trans3 IN VARCHAR2 DEFAULT NULL,
51 p_token4 IN VARCHAR2 DEFAULT NULL,
52 p_value4 IN VARCHAR2 DEFAULT NULL,
53 p_trans4 IN VARCHAR2 DEFAULT NULL,
54 p_token5 IN VARCHAR2 DEFAULT NULL,
55 p_value5 IN VARCHAR2 DEFAULT NULL,
56 p_trans5 IN VARCHAR2 DEFAULT NULL,
57 p_token6 IN VARCHAR2 DEFAULT NULL,
58 p_value6 IN VARCHAR2 DEFAULT NULL,
59 p_trans6 IN VARCHAR2 DEFAULT NULL,
60 p_token7 IN VARCHAR2 DEFAULT NULL,
61 p_value7 IN VARCHAR2 DEFAULT NULL,
62 p_trans7 IN VARCHAR2 DEFAULT NULL,
63 p_token8 IN VARCHAR2 DEFAULT NULL,
64 p_value8 IN VARCHAR2 DEFAULT NULL,
65 p_trans8 IN VARCHAR2 DEFAULT NULL,
66 p_token9 IN VARCHAR2 DEFAULT NULL,
67 p_value9 IN VARCHAR2 DEFAULT NULL,
68 p_trans9 IN VARCHAR2 DEFAULT NULL ) IS
69
70 l_module_name VARCHAR2(70) := G_MODULE_NAME || 'set_master_err_state';
71 BEGIN
72
73
74 fem_engines_pkg.tech_message( p_severity=>G_LOG_PROCEDURE ,
75 p_module=> l_module_name,
76 p_msg_text=> 'ENTRY');
77
78 IF p_master_err_state < err_state THEN
79 p_master_err_state := err_state; -- incoming state is at a higher priority.. update our master state.
80 END IF;
81
82 fem_engines_pkg.tech_message(p_severity=>G_LOG_STATEMENT ,
83 p_module=> l_module_name,
84 p_msg_text=> 'p_master_err_state := ' || p_master_err_state);
85 fem_engines_pkg.tech_message(p_severity=>G_LOG_STATEMENT ,
86 p_module=> l_module_name,
87 p_msg_text=> 'p_msg_name := ' || p_msg_name);
88
89 -- generate our message.
90 fem_engines_pkg.put_message( p_app_name,
91 p_msg_name,
92 p_token1 ,
93 p_value1 ,
94 p_trans1 ,
95 p_token2 ,
96 p_value2 ,
97 p_trans2 ,
98 p_token3 ,
99 p_value3 ,
100 p_trans3 ,
101 p_token4 ,
102 p_value4 ,
103 p_trans4 ,
104 p_token5 ,
105 p_value5 ,
106 p_trans5 ,
107 p_token6 ,
108 p_value6 ,
109 p_trans6 ,
110 p_token7 ,
111 p_value7 ,
112 p_trans7 ,
113 p_token8 ,
114 p_value8 ,
115 p_trans8 ,
116 p_token9 ,
117 p_value9 ,
118 p_trans9 );
119
120 fem_engines_pkg.tech_message( p_severity=>G_LOG_PROCEDURE ,
121 p_module=> l_module_name,
122 p_msg_text=> 'EXIT');
123 END set_master_err_state;
124
125
126 Procedure GetObjNameandFolderUsingObj(p_Object_ID in NUMBER
127 ,x_Object_Name OUT NOCOPY VARCHAR2
128 ,x_Folder_Name OUT NOCOPY VARCHAR2) IS
129 cursor getObjandFolderName is
130 select
131 a.object_name
132 ,b.folder_name
133 from
134 fem_object_catalog_vl a
135 ,fem_folders_vl b
136 where
137 a.object_id = p_Object_ID
138 and a.folder_id = b.folder_id;
139 l_Object_Name FEM_OBJECT_CATALOG_VL.OBJECT_NAME%TYPE;
140 l_Folder_Name FEM_FOLDERS_VL.FOLDER_NAME%TYPE;
141 Begin
142 OPEN getObjandFolderName;
143 FETCH getObjandFolderName into
144 l_Object_Name
145 ,l_Folder_Name;
146 CLOSE getObjandFolderName;
147 x_Object_Name := l_Object_Name;
148 x_Folder_Name := l_Folder_Name;
149
150 End GetObjNameandFolderUsingObj;
151
152 Procedure GetObjNameandFolderUsingDef(p_Obj_Def_ID IN NUMBER
153 ,x_Object_Name OUT NOCOPY VARCHAR2
154 ,x_Folder_Name OUT NOCOPY VARCHAR2) IS
155 CURSOR getObjandFolderName IS
156 select
157 a.object_name
158 ,b.folder_name
159 from
160 fem_object_definition_b c
161 ,fem_object_catalog_vl a
162 ,fem_folders_vl b
163 where
164 c.object_definition_id = p_Obj_Def_ID
165 and c.object_id = a.object_id
166 and a.folder_id = b.folder_id;
167 l_Object_Name FEM_OBJECT_CATALOG_VL.OBJECT_NAME%TYPE;
168 l_Folder_Name FEM_FOLDERS_VL.FOLDER_NAME%TYPE;
169 Begin
170 OPEN getObjandFolderName;
171 FETCH getObjandFolderName into
172 l_Object_Name
173 ,l_Folder_name;
174 CLOSE getObjandFolderName;
175 x_Object_Name := l_Object_Name;
176 x_Folder_Name := l_Folder_Name;
177
178 End GetObjNameandFolderUsingDef;
179
180 Function getVersionCount(X_Object_ID NUMBER)
181 RETURN NUMBER IS
182 st_count NUMBER := 0;
183 Begin
184
185 Begin
186 select count(*) into st_count
187 from fem_object_definition_b
188 where object_id = X_Object_ID
189 and approval_status_code <> 'SUBMIT_DELETE' ;
190 EXCEPTION
191 WHEN NO_DATA_FOUND THEN
192 st_count := 0;
193 End;
194 return st_count;
195 End getVersionCount;
196
197 ----------------------------------------------------------------
198 -- Function get_user_name returns the user_name stored in FND_USER
199 -- when passed a user_id
200 --
201 FUNCTION get_user_name(l_user_id IN NUMBER)
202 RETURN VARCHAR2
203 IS
204 l_user_name fnd_user.user_name%TYPE := '';
205
206 cursor l_user_cursor is
207 select user_name
208 from fnd_user
209 where user_id = l_user_id;
210
211 BEGIN
212
213 open l_user_cursor;
214 fetch l_user_cursor into l_user_name;
215 close l_user_cursor;
216
217 RETURN(l_user_name);
218
219 END get_user_name;
220
221 ----------------------------------------------------------------
222 Function migrationEnabledForUser RETURN VARCHAR2 IS
223 v_enabled VARCHAR2(255);
224 Begin
225 v_enabled := FND_PROFILE.VALUE_SPECIFIC('FEM_RULE_MIGRATION_ACCESS');
226 If v_enabled = 'Y' then
227 RETURN 'Y';
228 Else
229 RETURN 'N';
230 End If;
231 -- l_userCount NUMBER := 0;
232 --
233 -- cursor l_migrateEnabled_Cursor is
234 -- select count(user_id)
235 -- from FEM_DB_LINK_USERS
236 -- where user_id = FND_GLOBAL.USER_ID;--1002894
237 --Begin
238 -- OPEN l_migrateEnabled_Cursor;
239 -- FETCH l_migrateEnabled_Cursor into l_userCount;
240 -- CLOSE l_migrateEnabled_Cursor;
241 --
242 -- If l_userCount = 0 then
243 -- RETURN 'N';
244 -- Else
245 -- RETURN 'Y';
246 -- End If;
247 End migrationEnabledForUser;
248
249 ----------------------------------------------------------------
250 Function getRuleSetObjectDefID(X_RULE_SET_OBJECT_ID IN NUMBER) RETURN NUMBER IS
251
252 cursor l_getMultipleDefnFlag IS
253 select
254 multiple_definitions_flag
255 from
256 Fem_Object_Types a
257 ,Fem_object_catalog_b b
258 where
259 b.object_id = X_RULE_SET_OBJECT_ID
260 and b.object_type_code = a.object_type_code;
261
262 l_MultipleDefn_Flag VARCHAR2(1) := NULL;
263
264 cursor l_getObjectDefId is
265 select object_definition_id
266 from FEM_OBJECT_DEFINITION_B
267 where object_id = X_RULE_SET_OBJECT_ID ;
268
269 l_Object_Def_ID NUMBER := 0;
270
271 Begin
272 OPEN l_getMultipleDefnFlag;
273 FETCH l_getMultipleDefnFlag into
274 l_MultipleDefn_Flag;
275 CLOSE l_getMultipleDefnFlag;
276
277 If (l_MultipleDefn_Flag = 'Y') then
278 l_Object_Def_ID := NULL ;
279 Else
280 OPEN l_getObjectDefId;
281 FETCH l_getObjectDefId into l_Object_Def_ID;
282 CLOSE l_getObjectDefId;
283 End If;
284
285 RETURN l_Object_Def_ID;
286
287 End getRuleSetObjectDefID;
288
289 Function getFolderPrivilege(X_Object_ID IN NUMBER) RETURN VARCHAR2 IS
290
291 l_UserID NUMBER := 0;
292 l_Count NUMBER := 0;
293 l_Folder_ID FEM_FOLDERS_B.FOLDER_ID%TYPE := NULL;
294
295 cursor l_getUserID is
296 select FND_GLOBAL.USER_ID() from dual;
297
298 cursor l_getFolderID is
299 select a.Folder_ID
300 from FEM_OBJECT_CATALOG_B a
301 where a.object_id = X_Object_ID;
302
303 cursor l_getCount is
304 select count(user_id)
305 from FEM_USER_FOLDERS
306 where user_id = l_UserID
307 and folder_id = l_Folder_ID;
308
309 Begin
310
311 OPEN l_getUserID;
312 FETCH l_getUserID into l_UserID;
313 CLOSE l_getUserID;
314
315 OPEN l_getFolderID;
316 FETCH l_getFolderID into l_Folder_ID;
317 CLOSE l_getFolderID;
318
319 OPEN l_getCount;
320 FETCH l_getCount INTO l_Count;
321 CLOSE l_getCount;
322
323 If l_Count > 0 then
324 return 'Y';
325 Else
326 return 'N';
327 End If;
328
329 End getFolderPrivilege;
330
331
332 ----------------------------------------------------------------
333 FUNCTION getLookupMeaning(p_Application_ID IN NUMBER
334 ,p_Lookup_Type IN VARCHAR2
335 ,p_Lookup_Code IN VARCHAR2
336 ) RETURN VARCHAR2 IS
337 X_Meaning VARCHAR2(80);
338 Begin
339 Begin
340 select
341 meaning
342 into
343 X_Meaning
344 from
345 FND_LOOKUP_VALUES
346 WHERE LANGUAGE = userenv('LANG')
347 and VIEW_APPLICATION_ID = p_Application_ID
348 and LOOKUP_TYPE = p_Lookup_Type
349 and LOOKUP_CODE = p_Lookup_Code
350 and SECURITY_GROUP_ID = fnd_global.lookup_security_group(LOOKUP_TYPE, VIEW_APPLICATION_ID);
351
352 EXCEPTION
353 WHEN NO_DATA_FOUND THEN
354 X_Meaning := '['||p_Lookup_Code||']';
355 End;
356
357 RETURN X_Meaning;
358
359 End getLookupMeaning;
360
361 end FEM_UTILS;