1 PACKAGE BODY cz_security_pvt
2 /* $Header: czsecurb.pls 120.4 2007/09/05 20:38:54 smanna ship $ */
3
4 AS
5
6 -----Pkg body declarations
7 TYPE ref_cursor IS REF CURSOR;
8
9 -----exception declarations
10 FUNCTION_NAME_NULL EXCEPTION;
11 PRIVILEGE_IS_NULL EXCEPTION;
12 INVALID_PRIVILEGE EXCEPTION;
13 INSTANCE_SET_ERR EXCEPTION;
14 MENU_ID_NOT_FOUND EXCEPTION;
15 OBJECT_ID_NOT_FOUND EXCEPTION;
16 PRIV_ALREADY_EXISTS EXCEPTION;
17 NO_PRIV_EXISTS EXCEPTION;
18 NO_ENTITY_ACCESS_CONTROL EXCEPTION;
19 ENTITY_IS_ALREADY_LOCKED EXCEPTION;
20 HAS_NO_LOCK_PRIV EXCEPTION;
21 INVALID_USER_NAME EXCEPTION;
22 INVALID_APPLICATION EXCEPTION;
23 INVALID_RESPONSIBILITY EXCEPTION;
24 USER_NAME_NULL EXCEPTION;
25 APPL_NAME_NULL EXCEPTION;
26 RESP_NAME_NULL EXCEPTION;
27 NO_LOCK_CONTROL_REQUIRED EXCEPTION;
28 NO_LOCKS_REQD_FOR_EDIT EXCEPTION;
29 NULL_USER_NAME EXCEPTION;
30 RESP_IS_NULL EXCEPTION;
31 ENTITY_ROLE_IS_NULL EXCEPTION;
32 INVALID_ROLE EXCEPTION;
33 INVALID_UI_STYLE EXCEPTION;
34 PROJECT_IS_LOCKED EXCEPTION;
35 ENTITY_LOCKED_BY_USER EXCEPTION;
36 ENTITY_LOCKED_OTH_USER EXCEPTION;
37 MODEL_LOCKED EXCEPTION;
38
39 ----------->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
40 ---get default acces profile value
41 FUNCTION get_default_access_profile
42 RETURN VARCHAR2
43 IS
44 l_return_value VARCHAR2(100):= '0';
45 BEGIN
46 l_return_value := FND_PROFILE.value(DEFAULT_ENTITY_ACCESS);
47 RETURN l_return_value ;
48 END get_default_access_profile;
49
50 ------->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
51 -----get profile value
52 FUNCTION get_profile_value(p_profile IN VARCHAR2)
53 RETURN VARCHAR2
54 IS
55 l_profile_value VARCHAR2(30);
56
57 BEGIN
58 IF (p_profile IS NOT NULL) THEN
59 l_profile_value := FND_PROFILE.value(p_profile);
60 ELSE
61 l_profile_value := NULL;
62 END IF;
63 RETURN l_profile_value;
64 END get_profile_value;
65
66 ----------->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
67 -----function that returns function_id for a function name
68 FUNCTION get_function_id (p_function_name IN VARCHAR2)
69 RETURN NUMBER
70 IS
71 l_function_id NUMBER := 0;
72 BEGIN
73 SELECT function_id
74 INTO l_function_id
75 FROM fnd_form_functions
76 WHERE function_name = UPPER(p_function_name);
77 RETURN l_function_id;
78 EXCEPTION
79 WHEN NO_DATA_FOUND THEN
80 l_function_id := 0;
81 RETURN l_function_id;
82 END get_function_id ;
83
84 --------->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
85 PROCEDURE log_lock_history (p_entity_type IN NUMBER,
86 p_entity_id IN NUMBER,
87 p_event IN VARCHAR2,
88 p_event_note IN VARCHAR2)
89 IS
90
91 BEGIN
92 insert into cz_lock_history (ENTITY_TYPE,INSTANCE_PK1_VALUE,INSTANCE_PK2_VALUE,INSTANCE_PK3_VALUE
93 ,INSTANCE_PK4_VALUE,EVENT,EVENT_DATE,USER_NAME,EVENT_NOTE)
94 VALUES (p_entity_type,p_entity_id ,0,0,0,p_event,sysdate,FND_GLOBAL.user_name,p_event_note);
95 EXCEPTION
96 WHEN OTHERS THEN
97 NULL; ----message needs to be logged
98 END log_lock_history ;
99
100 ----------->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
101 -----function that returns the menu id for a menu name
102 FUNCTION get_menu_id_for_menuname (p_menu_name IN VARCHAR2)
103 RETURN NUMBER
104 IS
105 l_menu_id NUMBER := 0;
106 BEGIN
107 SELECT menu_id
108 INTO l_menu_id
109 FROM fnd_menus
110 WHERE fnd_menus.menu_name = p_menu_name
111 AND fnd_menus.type = SECURITY_MENU;
112 RETURN l_menu_id;
113 EXCEPTION
114 WHEN NO_DATA_FOUND THEN
115 l_menu_id := 0;
116 RETURN l_menu_id;
117 END get_menu_id_for_menuname;
118
119 ----------->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
120 -----Function that returns a menu_id for a function name
121 -----
122 FUNCTION get_menu_id_for_func_name(p_function_name IN VARCHAR2,
123 p_menu_name IN VARCHAR2)
124 RETURN NUMBER
125 IS
126
127 l_menu_id NUMBER := 0;
128
129 BEGIN
130 SELECT menu_id
131 INTO l_menu_id
132 FROM fnd_compiled_menu_functions
133 WHERE fnd_compiled_menu_functions.function_id IN (SELECT function_id
134 FROM fnd_form_functions
135 WHERE fnd_form_functions.function_name = p_function_name)
136 AND fnd_compiled_menu_functions.menu_id = (SELECT menu_id
137 FROM fnd_menus
138 WHERE fnd_menus.menu_name = p_menu_name);
139 RETURN l_menu_id;
140 EXCEPTION
141 WHEN NO_DATA_FOUND THEN
142 RETURN l_menu_id;
143 END get_menu_id_for_func_name;
144
145 ----------->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
146 -----function that returns an object_id for an entity type
147 FUNCTION get_object_id (p_entity_type IN VARCHAR2)
148 RETURN NUMBER
149 IS
150
151 l_object_id NUMBER := 0;
152
153 BEGIN
154 SELECT object_id
155 INTO l_object_id
156 FROM fnd_objects
157 WHERE obj_name = UPPER(p_entity_type);
158 RETURN l_object_id;
159 EXCEPTION
160 WHEN NO_DATA_FOUND THEN
161 l_object_id := 0;
162 RETURN l_object_id;
163 END get_object_id ;
164
165 ----------->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
166 ----function that returns the instance_set_id for a given object_name
167 ----and predicate
168 FUNCTION get_instance_set_id(p_object_id IN NUMBER,
169 p_predicate IN VARCHAR2)
170 RETURN NUMBER
171 IS
172
173 l_instance_set_id NUMBER := 0;
174 BEGIN
175 SELECT instance_set_id
176 INTO l_instance_set_id
177 FROM FND_OBJECT_INSTANCE_SETS
178 WHERE object_id = p_object_id
179 AND instance_set_name = UPPER(p_predicate);
180 RETURN l_instance_set_id;
181 EXCEPTION
182 WHEN NO_DATA_FOUND THEN
183 l_instance_set_id := 0;
184 RETURN l_instance_set_id;
185 END get_instance_set_id;
186 ---------->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
187 FUNCTION get_priv(p_instance_set_id IN NUMBER,
188 p_menu_id IN VARCHAR2,
189 p_user_name IN VARCHAR2)
190 RETURN VARCHAR2
191 IS
192 l_check_grant VARCHAR2(1) := 'F';
193 BEGIN
194 SELECT 'T'
195 INTO l_check_grant
196 FROM fnd_grants
197 WHERE grantee_key = UPPER(p_user_name)
198 AND instance_set_id = p_instance_set_id
199 AND menu_id = p_menu_id;
200 RETURN l_check_grant;
201 EXCEPTION
202 WHEN OTHERS THEN
203 l_check_grant := 'F';
204 RETURN l_check_grant;
205 END;
206
207 -------->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
208 FUNCTION get_grant_access(p_model_id IN NUMBER)
209 RETURN VARCHAR2 IS
210 l_user_name VARCHAR2(100) := 'NOUSER';
211 l_priv VARCHAR2(1) := 'N';
212 l_cur REF_CURSOR;
213 BEGIN
214 l_user_name := FND_GLOBAL.user_name;
215 OPEN l_cur FOR 'SELECT '||l_user_name||'_MANAGE FROM CZ_GRANTS_ON_ENTITIES_VIEW
216 WHERE model_id = '||p_model_id||' AND entity_type = ''MODEL'' ';
217 LOOP
218 FETCH l_cur INTO l_priv;
219 EXIT WHEN l_cur%NOTFOUND;
220 END LOOP;
221 CLOSE l_cur;
222 RETURN l_priv;
223 EXCEPTION
224 WHEN OTHERS THEN
225 RETURN l_priv;
226 END get_grant_access;
227 --------->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
228 -- Stubbed as part of the bug 4861666, as this code is not utilized
229 -- in the system due to the obsoletion of "View Entity Access" feature
230 PROCEDURE GET_CZ_ACCESS_SUMMARY (p_model_id NUMBER)
231 AS
232 BEGIN
233 NULL;
234 END GET_CZ_ACCESS_SUMMARY ;
235 --------->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
236 PROCEDURE create_grants_on_models_v
237 AS
238 CURSOR C1 IS SELECT obj_name from fnd_objects;
239 l_column_name fnd_objects.obj_name%TYPE;
240 l_execute_str VARCHAR2(2000);
241
242 BEGIN
243 l_execute_str := 'CREATE OR REPLACE VIEW CZ_GRANTS_ON_MODELS as SELECT model_id, object_name as model_name,';
244 OPEN C1;
245 LOOP
246 FETCH C1 INTO l_column_name;
247 EXIT WHEN C1%NOTFOUND;
248 l_execute_str := l_execute_str||' '||l_column_name||',';
249 END LOOP;
250 CLOSE C1;
251 l_execute_str := RTRIM(l_execute_str,',');
252 l_execute_str := l_execute_str ||', cz_security_pvt.get_grant_access(model_id) as has_grant_access ' ||
253 ' FROM CZ_GRANTS_ON_ENTITIES_VIEW WHERE entity_type = ''MODEL'' ';
254 EXECUTE IMMEDIATE l_execute_str;
255 EXCEPTION
256 WHEN OTHERS THEN
257 RAISE;
258 END create_grants_on_models_v;
259
260 ----------->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
261 -- Stubbed as part of the bug 4861666, as this code is not utilized
262 -- in the system due to the obsoletion of "View Entity Access" feature
263 PROCEDURE GET_CZ_GRANTS_VIEW
264 AS
265 BEGIN
266 NULL;
267 END GET_CZ_GRANTS_VIEW;
268
269 ----------->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
270 -----function that returns predicate for an entity type
271 FUNCTION get_instance_set_name(p_entity_type IN VARCHAR2,
272 p_instance_pk1_value IN NUMBER)
273 RETURN VARCHAR2
274 IS
275
276 l_set_name VARCHAR2(30);
277
278 BEGIN
279 IF (p_entity_type = cz_security_pvt.MODEL) THEN
280 l_set_name := 'CZ_MODEL:'||to_char(p_instance_pk1_value);
281 ELSIF (p_entity_type = cz_security_pvt.UI) THEN
282 l_set_name := 'CZ_UI_DEF_ID:'||to_char(p_instance_pk1_value);
283 ELSIF (p_entity_type = cz_security_pvt.RULEFOLDER) THEN
284 l_set_name := 'CZ_RULEFOLDER:'||to_char(p_instance_pk1_value);
285 END IF;
286 RETURN l_set_name ;
287 END get_instance_set_name;
288
289 ---------->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
290 -----function that returns predicate for an entity type
291 FUNCTION get_predicate(p_entity_type IN VARCHAR2,
292 p_instance_pk1_value IN NUMBER)
293 RETURN VARCHAR2
294 IS
295
296 l_predicate VARCHAR2(4000);
297
298 BEGIN
299 IF (p_entity_type = cz_security_pvt.MODEL) THEN
300 l_predicate := 'DEVL_PROJECT_ID='||p_instance_pk1_value;
301 ELSIF (p_entity_type = cz_security_pvt.UI) THEN
302 l_predicate := 'DEVL_PROJECT_ID='||p_instance_pk1_value;
303 ELSIF (p_entity_type = cz_security_pvt.RULEFOLDER) THEN
304 l_predicate := 'RULE_FOLDER_ID='||p_instance_pk1_value;
305 END IF;
306 RETURN l_predicate;
307 END get_predicate;
308
309 ----------->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
310 -----procedure to grant privilege
311 PROCEDURE grant_privilege(p_api_version IN NUMBER,
312 p_user_name IN VARCHAR2,
313 p_entity_role IN VARCHAR2,
314 p_instance_pk1_value IN NUMBER,
315 x_return_status OUT NOCOPY VARCHAR2,
316 x_msg_count OUT NOCOPY NUMBER,
317 x_msg_data OUT NOCOPY VARCHAR2)
318 IS
319
320 l_api_name CONSTANT VARCHAR2(30) := 'grant_privilege';
321 l_api_version CONSTANT NUMBER := 1.0;
322 l_user_name fnd_grants.grantee_key%TYPE;
323 l_ctx_resp_id fnd_grants.CTX_RESP_ID%TYPE;
324 l_privilege VARCHAR2(100);
325 l_menu_name VARCHAR2(100);
326 l_menu_id NUMBER;
327 l_object_id NUMBER;
328 l_instance_set_id NUMBER;
329 l_resp_id NUMBER;
330 l_function_name fnd_form_functions.function_name%TYPE;
331 l_check_grant NUMBER := 0;
332 l_inst_name VARCHAR2(30);
333 l_entity_type VARCHAR2(30);
334
335
336 BEGIN
337 x_return_status := 'S';
338 x_msg_count := 0;
339 ---check api version
340 IF NOT FND_API.Compatible_API_Call(l_api_version,p_api_version,l_api_name,G_PKG_NAME) THEN
341 RAISE G_INCOMPATIBLE_API;
342 END IF;
343
344 ----default user name
345 IF (p_user_name IS NULL) THEN
346 l_user_name := FND_GLOBAL.USER_NAME;
347 ELSE
348 l_user_name := p_user_name;
349 END IF;
350
351 IF (l_user_name IS NULL) THEN
352 RAISE NULL_USER_NAME;
353 END IF;
354
355 IF (p_entity_role IS NULL) THEN
356 RAISE ENTITY_ROLE_IS_NULL;
357 END IF;
358 l_menu_id := get_menu_id_for_menuname(p_entity_role);
359 IF (l_menu_id = 0) THEN
360 RAISE MENU_ID_NOT_FOUND;
361 END IF;
362
363 IF (p_entity_role = cz_security_pvt.MANAGE_MODEL_ROLE) THEN
364 l_entity_type := cz_security_pvt.MODEL;
365 l_inst_name := 'CZ_MODEL:'||p_instance_pk1_value;
366 ELSIF (p_entity_role = cz_security_pvt.EDIT_MODEL_ROLE) THEN
367 l_entity_type := cz_security_pvt.MODEL;
368 l_inst_name := 'CZ_MODEL:'||p_instance_pk1_value;
369 ELSIF (p_entity_role = cz_security_pvt.EDIT_RULE_ROLE) THEN
370 l_entity_type := cz_security_pvt.RULEFOLDER;
371 l_inst_name := 'CZ_RULEFOLDER:'||p_instance_pk1_value;
372 ELSIF (p_entity_role = cz_security_pvt.EDIT_UI_ROLE) THEN
373 l_entity_type := cz_security_pvt.UI;
374 l_inst_name := 'CZ_UI_DEF_ID:'||p_instance_pk1_value;
375 ELSE
376 RAISE INVALID_ROLE;
377 END IF;
378
379 ----get object id from fnd_objects
380 l_object_id := get_object_id(l_entity_type);
381 IF (l_object_id = 0) THEN
382 RAISE OBJECT_ID_NOT_FOUND;
383 END IF;
384
385 -----initialize resp id
386 l_resp_id := FND_GLOBAL.resp_id;
387 IF (l_resp_id IS NULL) THEN
388 RAISE RESP_IS_NULL;
389 END IF;
390
391 ----get instance_set_id
392 l_instance_set_id := get_instance_set_id(l_object_id,l_inst_name);
393 IF (l_instance_set_id = 0) THEN
394 RAISE INSTANCE_SET_ERR;
395 END IF;
396
397 ---check if grant exists
398 BEGIN
399 SELECT 1
400 INTO l_check_grant
401 FROM fnd_grants
402 WHERE grantee_key = UPPER(l_user_name)
403 AND instance_set_id = l_instance_set_id
404 AND menu_id = l_menu_id
405 AND object_id = l_object_id;
406 EXCEPTION
407 WHEN OTHERS THEN
408 l_check_grant := 0;
409 END;
410
411 IF (l_check_grant = 1) THEN
412 RAISE PRIV_ALREADY_EXISTS;
413 ELSE
414 ----insert into fnd_grants table
415 INSERT INTO FND_GRANTS ( GRANT_GUID,GRANTEE_TYPE,GRANTEE_KEY,MENU_ID,START_DATE,OBJECT_ID
416 ,INSTANCE_TYPE,INSTANCE_SET_ID,INSTANCE_PK1_VALUE,INSTANCE_PK2_VALUE
417 ,INSTANCE_PK3_VALUE,INSTANCE_PK4_VALUE,INSTANCE_PK5_VALUE,CREATED_BY
418 ,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_LOGIN
419 ,CTX_SECGRP_ID,CTX_RESP_ID,CTX_RESP_APPL_ID,CTX_ORG_ID)
420 VALUES (sys_guid(),'GLOBAL',p_user_name,l_menu_id,sysdate,l_object_id
421 ,'INSTANCE',l_instance_set_id,p_instance_pk1_value,'*NULL*','*NULL*','*NULL*','*NULL*',FND_GLOBAL.USER_ID,
422 sysdate,FND_GLOBAL.USER_ID,sysdate,FND_GLOBAL.USER_ID,-1,l_resp_id,-1,-1);
423 END IF;
424 EXCEPTION
425 WHEN G_INCOMPATIBLE_API THEN
426 x_msg_data := CZ_UTILS.GET_TEXT('CZ_SEC_API_VERSION_ERR','CODEVERSION',l_api_version,'VERSION',p_api_version);
427 x_return_status := FND_API.G_RET_STS_ERROR;
431 x_return_status := FND_API.G_RET_STS_ERROR;
428 x_msg_count := 1;
429 WHEN NULL_USER_NAME THEN
430 x_msg_data := CZ_UTILS.GET_TEXT('CZ_SEC_USER_IS_NULL');
432 x_msg_count := 1;
433 WHEN RESP_IS_NULL THEN
434 x_msg_data := CZ_UTILS.GET_TEXT('CZ_SEC_RESP_IS_NULL');
435 x_return_status := FND_API.G_RET_STS_ERROR;
436 x_msg_count := 1;
437 WHEN INVALID_ROLE THEN
438 x_msg_data := CZ_UTILS.GET_TEXT('CZ_SEC_INVALID_ROLE');
439 x_return_status := FND_API.G_RET_STS_ERROR;
440 x_msg_count := 1;
441 WHEN FUNCTION_NAME_NULL THEN
442 x_msg_data := CZ_UTILS.GET_TEXT('CZ_SEC_FUNC_NAME_IS_NULL');
443 x_return_status := FND_API.G_RET_STS_ERROR;
444 x_msg_count := 1;
445 WHEN ENTITY_ROLE_IS_NULL THEN
446 x_msg_data := CZ_UTILS.GET_TEXT('CZ_SEC_ENTITY_ROLE_IS_NULL');
447 x_return_status := FND_API.G_RET_STS_ERROR;
448 x_msg_count := 1;
449 WHEN MENU_ID_NOT_FOUND THEN
450 x_msg_data := CZ_UTILS.GET_TEXT('CZ_SEC_INVALID_ROLE', 'Privilege', p_entity_role );
451 x_return_status := FND_API.G_RET_STS_ERROR;
452 x_msg_count := 1;
453 WHEN OBJECT_ID_NOT_FOUND THEN
454 x_msg_data := CZ_UTILS.GET_TEXT('CZ_SEC_OBJECT_ID_ERR', 'object_id',l_entity_type);
455 x_return_status := FND_API.G_RET_STS_ERROR;
456 x_msg_count := 1;
457 WHEN INSTANCE_SET_ERR THEN
458 x_msg_data := CZ_UTILS.GET_TEXT('CZ_INVALID_INSTANCE_SET', 'SET',l_inst_name,
459 'ENTITY_TYPE',l_entity_type, 'ERROR', SQLERRM);
460 x_msg_count := 1;
461 x_return_status := FND_API.G_RET_STS_ERROR;
462 WHEN PRIV_ALREADY_EXISTS THEN
463 NULL; ---not necessary to return a message
464 WHEN OTHERS THEN
465 x_msg_data := SQLERRM;
466 x_return_status := FND_API.G_RET_STS_ERROR;
467 END grant_privilege;
468
469 ----------------------------------------------------------------
470 ------revoke privilege on an entity
471 PROCEDURE revoke_privilege(p_api_version IN NUMBER,
472 p_user_name IN VARCHAR2,
473 p_entity_role IN VARCHAR2,
474 p_instance_pk1_value IN NUMBER,
475 x_return_status OUT NOCOPY VARCHAR2,
476 x_msg_count OUT NOCOPY NUMBER,
477 x_msg_data OUT NOCOPY VARCHAR2)
478 IS
479
480 l_api_name CONSTANT VARCHAR2(30) := 'revoke_privilege';
481 l_api_version CONSTANT NUMBER := 1.0;
482 l_object_id NUMBER;
483 l_instance_set_id NUMBER;
484 l_predicate VARCHAR2(2000);
485 l_menu_id NUMBER;
486 l_function_id NUMBER;
487 l_check_grant NUMBER := 0;
488 l_instance_set_name VARCHAR2(30);
489 l_entity_type VARCHAR2(30);
490
491 BEGIN
492 x_return_status := 'S';
493 x_msg_count := 0;
494 ---check api version
495 IF NOT FND_API.Compatible_API_Call(l_api_version,p_api_version,l_api_name,G_PKG_NAME) THEN
496 RAISE G_INCOMPATIBLE_API;
497 END IF;
498
499 IF (p_entity_role IS NULL) THEN
500 RAISE ENTITY_ROLE_IS_NULL;
501 END IF;
502 l_menu_id := get_menu_id_for_menuname(p_entity_role);
503 IF (l_menu_id = 0) THEN
504 RAISE MENU_ID_NOT_FOUND;
505 END IF;
506
507 IF (p_entity_role = cz_security_pvt.MANAGE_MODEL_ROLE) THEN
508 l_entity_type := cz_security_pvt.MODEL;
509 ELSIF (p_entity_role = cz_security_pvt.EDIT_MODEL_ROLE) THEN
510 l_entity_type := cz_security_pvt.MODEL;
511 ELSIF (p_entity_role = cz_security_pvt.EDIT_RULE_ROLE) THEN
512 l_entity_type := cz_security_pvt.RULEFOLDER;
513 ELSIF (p_entity_role = cz_security_pvt.EDIT_UI_ROLE) THEN
514 l_entity_type := cz_security_pvt.UI;
515 ELSE
516 RAISE INVALID_ROLE;
517 END IF;
518
519 -----get object_id
520 l_object_id := get_object_id (l_entity_type);
521 IF (l_object_id = 0) THEN
522 RAISE OBJECT_ID_NOT_FOUND;
523 END IF;
524
525 ----get predicate
526 l_instance_set_name := get_instance_set_name(l_entity_type,p_instance_pk1_value);
527
528 -----get instance_set_id
529 l_instance_set_id := get_instance_set_id(l_object_id,l_instance_set_name);
530
531 ----check if privilege actually exists
532 SELECT 1
533 INTO l_check_grant
534 FROM fnd_grants
535 WHERE grantee_key = UPPER(p_user_name)
536 AND instance_set_id = l_instance_set_id
537 AND menu_id = l_menu_id
538 AND object_id = l_object_id;
539
540 IF (l_check_grant <> 1) THEN
541 RAISE NO_PRIV_EXISTS; ---do we need to do a check and return an error message
542 ELSE
543 DELETE FROM fnd_grants
544 WHERE grantee_key = UPPER(p_user_name)
545 AND menu_id = l_menu_id
546 AND object_id = l_object_id
547 AND instance_set_id = l_instance_set_id
548 AND instance_pk1_value = to_char(p_instance_pk1_value);
549 END IF;
550 EXCEPTION
551 WHEN G_INCOMPATIBLE_API THEN
552 x_msg_data := CZ_UTILS.GET_TEXT('CZ_SEC_API_VERSION_ERR','CODEVERSION',l_api_version,'VERSION',p_api_version);
553 x_return_status := FND_API.G_RET_STS_ERROR;
557 WHEN INVALID_ENTITY_TYPE THEN
554 WHEN OBJECT_ID_NOT_FOUND THEN
555 x_msg_data := CZ_UTILS.GET_TEXT('CZ_SEC_OBJECT_ID_ERR', 'object_id', l_entity_type);
556 x_return_status := FND_API.G_RET_STS_ERROR;
558 x_msg_data := CZ_UTILS.GET_TEXT('CZ_SEC_INVALID_ENTITY_TYP', 'OBJECTTYPE', l_entity_type);
559 x_return_status := FND_API.G_RET_STS_ERROR;
560 WHEN ENTITY_ROLE_IS_NULL THEN
561 x_msg_data := CZ_UTILS.GET_TEXT('CZ_SEC_ENTITY_ROLE_IS_NULL');
562 x_return_status := FND_API.G_RET_STS_ERROR;
563 x_msg_count := 1;
564 WHEN MENU_ID_NOT_FOUND THEN
565 x_msg_data := CZ_UTILS.GET_TEXT('CZ_SEC_INVALID_ROLE', 'Privilege', p_entity_role );
566 x_return_status := FND_API.G_RET_STS_ERROR;
567 x_msg_count := 1;
568 WHEN NO_PRIV_EXISTS THEN
569 x_msg_data := CZ_UTILS.GET_TEXT('CZ_SEC_NO_PRIV_EXISTS');
570 -----x_return_status := FND_API.G_RET_STS_ERROR;
571 WHEN OTHERS THEN
572 x_msg_data := CZ_UTILS.GET_TEXT('CZ_SEC_FATAL_ERR', 'SQLERRM', SQLERRM);
573 x_return_status := FND_API.G_RET_STS_ERROR;
574 END revoke_privilege;
575
576 -------------------------------------------------------------
577 PROCEDURE revoke_privilege(p_api_version IN NUMBER,
578 p_instance_pk1_value IN NUMBER,
579 x_return_status OUT NOCOPY VARCHAR2,
580 x_msg_count OUT NOCOPY NUMBER,
581 x_msg_data OUT NOCOPY VARCHAR2)
582 IS
583
584 l_object_id NUMBER;
585 l_instance_set_id NUMBER;
586 l_predicate VARCHAR2(2000);
587 l_menu_id NUMBER;
588 l_function_id NUMBER;
589 l_check_grant NUMBER := 0;
590 l_instance_set_name VARCHAR2(30);
591 l_entity_type VARCHAR2(30);
592
593 BEGIN
594 l_entity_type := cz_security_pvt.MODEL;
595 l_object_id := get_object_id (l_entity_type);
596 l_instance_set_name := get_instance_set_name(l_entity_type,p_instance_pk1_value);
597 l_instance_set_id := get_instance_set_id(l_object_id,l_instance_set_name);
598 l_menu_id := get_menu_id_for_menuname(cz_security_pvt.MANAGE_MODEL_ROLE);
599
600 DELETE FROM fnd_grants WHERE menu_id = l_menu_id AND object_id = l_object_id
601 AND instance_set_id = l_instance_set_id AND instance_pk1_value = to_char(p_instance_pk1_value);
602
603 l_object_id := get_object_id (l_entity_type);
604 l_instance_set_name := get_instance_set_name(l_entity_type,p_instance_pk1_value);
605 l_instance_set_id := get_instance_set_id(l_object_id,l_instance_set_name);
606 l_menu_id := get_menu_id_for_menuname(cz_security_pvt.EDIT_MODEL_ROLE);
607
608 DELETE FROM fnd_grants WHERE menu_id = l_menu_id AND object_id = l_object_id
609 AND instance_set_id = l_instance_set_id AND instance_pk1_value = to_char(p_instance_pk1_value);
610
611 l_entity_type := cz_security_pvt.RULEFOLDER;
612 l_object_id := get_object_id (l_entity_type);
613 l_instance_set_name := get_instance_set_name(l_entity_type,p_instance_pk1_value);
614 l_instance_set_id := get_instance_set_id(l_object_id,l_instance_set_name);
615 l_menu_id := get_menu_id_for_menuname(cz_security_pvt.EDIT_RULE_ROLE);
616
617 DELETE FROM fnd_grants WHERE menu_id = l_menu_id AND object_id = l_object_id
618 AND instance_set_id = l_instance_set_id AND instance_pk1_value = to_char(p_instance_pk1_value);
619
620 l_entity_type := cz_security_pvt.UI;
621 l_object_id := get_object_id (l_entity_type);
622 l_instance_set_name := get_instance_set_name(l_entity_type,p_instance_pk1_value);
623 l_instance_set_id := get_instance_set_id(l_object_id,l_instance_set_name);
624 l_menu_id := get_menu_id_for_menuname(cz_security_pvt.EDIT_UI_ROLE);
625
626 DELETE FROM fnd_grants WHERE menu_id = l_menu_id AND object_id = l_object_id
627 AND instance_set_id = l_instance_set_id AND instance_pk1_value = to_char(p_instance_pk1_value);
628 END;
629
630 --------------------------------------------------------------------
631 --------check privilege
632 ----@p_api_version : api version current version is 1.0
633 ----@p_user_name : fnd user ex: OPERATIONS
634 ----@p_function_name : fnd function , function name of the task
635 ----@p_entity_type : Fnd_objects.obj_name ex MODEL, UI RULEFOLDER
636 ----for the above use global constants
637 ----@p_instance_pk1_value : primary key of the object ex devl_project_id or rule_folder_id
638 FUNCTION has_privileges (p_api_version IN NUMBER,
639 p_user_name IN VARCHAR2,
640 p_function_name IN VARCHAR2,
641 p_entity_type IN VARCHAR2,
642 p_instance_pk1_value IN NUMBER)
643 RETURN VARCHAR2
644 IS
645
646 l_priv VARCHAR2(100) := 'T';
647 l_profile_value VARCHAR2(1000);
648 l_api_name CONSTANT VARCHAR2(1000) := 'has_privileges';
649 l_api_version CONSTANT NUMBER := 1.0;
650 l_function_name fnd_form_functions.function_name%TYPE;
651 l_user_name VARCHAR2(40);
652 BEGIN
653 ---check api version
654 IF NOT FND_API.Compatible_API_Call(l_api_version,p_api_version,l_api_name,G_PKG_NAME) THEN
655 RAISE G_INCOMPATIBLE_API;
656 END IF;
657 l_profile_value := get_profile_value(USE_ENTITY_ACCESS_CONTROL);
658 IF ( (l_profile_value <> 'Y') ) THEN
659 RAISE NO_ENTITY_ACCESS_CONTROL;
660 ELSE
661 l_priv := 'T';
662 END IF;
666 l_priv := 'F';
663 RETURN l_priv;
664 EXCEPTION
665 WHEN G_INCOMPATIBLE_API THEN
667 RETURN l_priv;
668 WHEN NO_ENTITY_ACCESS_CONTROL THEN
669 l_priv := 'T';
670 RETURN l_priv;
671 END has_privileges;
672
673 ---------------------------------------------------
674 FUNCTION has_privileges (p_function_name IN VARCHAR2,
675 p_entity_type IN VARCHAR2,
676 p_instance_pk1_value IN NUMBER)
677 RETURN VARCHAR2
678 IS
679 l_function_name fnd_form_functions.function_name%TYPE;
680 l_user_name VARCHAR2(40);
681 BEGIN
682 RETURN 'T';
683 EXCEPTION
684 WHEN OTHERS THEN
685 RETURN 'U';
686 END has_privileges;
687
688 ------------------------------------------------------------------------
689 -----check lock on a entity
690 FUNCTION is_locked (p_entity_name IN VARCHAR2,
691 p_primary_key IN VARCHAR2,
692 p_primary_key_value IN NUMBER )
693 RETURN VARCHAR2
694 IS
695
696 l_checkout_user VARCHAR2(40) := NULL;
697 checkout_cur ref_cursor;
698
699 BEGIN
700 OPEN checkout_cur FOR 'SELECT checkout_user FROM '||p_entity_name||'
701 WHERE '||p_primary_key||' = '||p_primary_key_value;
702 LOOP
703 FETCH checkout_cur INTO l_checkout_user;
704 EXIT WHEN checkout_cur%NOTFOUND;
705 END LOOP;
706 CLOSE checkout_cur;
707 RETURN l_checkout_user;
708 END is_locked ;
709 --------->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
710 PROCEDURE lock_entity(p_model_id IN NUMBER,
711 x_return_status OUT NOCOPY VARCHAR2,
712 x_msg_data OUT NOCOPY VARCHAR2)
713 IS
714 BEGIN
715 x_return_status := FND_API.G_RET_STS_SUCCESS;
716 x_msg_data := '';
717 END lock_entity ;
718
719 --------->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
720 PROCEDURE unlock_entity(p_model_id IN NUMBER,
721 x_return_status OUT NOCOPY VARCHAR2,
722 x_msg_data OUT NOCOPY VARCHAR2)
723 IS
724 BEGIN
725 x_return_status := FND_API.G_RET_STS_SUCCESS;
726 x_msg_data := '';
727 END unlock_entity;
728
729 ---------->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
730 PROCEDURE get_entities_to_lock(p_entity_type IN VARCHAR2,
731 p_entity_id IN NUMBER,
732 x_locked_entities OUT NOCOPY number_type_tbl)
733 IS
734
735 rec_count NUMBER := 0;
736 l_ui_style cz_ui_defs.ui_style%TYPE;
737 l_devl_project_id cz_ui_defs.devl_project_id%TYPE;
738 l_cz_ui_defs_id_tbl number_type_tbl;
739 l_cz_ui_defs_style_tbl varchar_type_tbl;
740
741 BEGIN
742 IF (p_entity_type = cz_security_pvt.MODEL) THEN
743 -----get all model entities to lock
744 BEGIN
745 SELECT distinct component_id
746 BULK
747 COLLECT
748 INTO x_locked_entities
749 FROM cz_model_ref_expls
750 WHERE cz_model_ref_expls.model_id = p_entity_id
751 AND cz_model_ref_expls.deleted_flag = '0'
752 AND cz_model_ref_expls.ps_node_type = 263;
753 EXCEPTION
754 WHEN NO_DATA_FOUND THEN
755 NULL;
756 END;
757
758 rec_count := x_locked_entities.COUNT + 1;
759 x_locked_entities(rec_count) := p_entity_id;
760
761 ELSIF (p_entity_type = cz_security_pvt.UI) THEN
762
763 -----get UI style
764 BEGIN
765 SELECT ui_style
766 INTO l_ui_style
767 FROM cz_ui_defs
768 WHERE cz_ui_defs.devl_project_id = p_entity_id
769 AND cz_ui_defs.deleted_flag = '0';
770 EXCEPTION
771 WHEN NO_DATA_FOUND THEN
772 NULL;
773 END;
774 -----get all UI entities to lock
775 IF (l_ui_style IN ('0','3') ) THEN
776
777 BEGIN
778 SELECT ui_def_ref_id
779 BULK
780 COLLECT
781 INTO x_locked_entities
782 FROM cz_ui_nodes
783 WHERE cz_ui_nodes.deleted_flag = '0'
784 AND cz_ui_nodes.ui_def_ref_id IS NOT NULL
785 AND cz_ui_nodes.ui_def_id = p_entity_id;
786 EXCEPTION
787 WHEN NO_DATA_FOUND THEN
788 NULL;
789 END;
790
791 rec_count := x_locked_entities.COUNT + 1;
792 x_locked_entities(rec_count) := p_entity_id;
793
794 ELSIF (l_ui_style = '7') THEN
795
796 BEGIN
797 SELECT ref_ui_def_id
798 BULK
799 COLLECT
800 INTO x_locked_entities
801 FROM cz_ui_refs
802 WHERE cz_ui_refs.ui_def_id = p_entity_id
803 AND cz_ui_refs.deleted_flag = '0'
804 AND cz_ui_refs.ref_ui_def_id IN (SELECT ui_def_id
805 FROM cz_ui_defs
806 WHERE cz_ui_defs.deleted_flag = '0'
807 AND cz_ui_defs.ui_style = '7');
808 EXCEPTION
809 WHEN NO_DATA_FOUND THEN
810 NULL;
811 END;
812 rec_count := x_locked_entities.COUNT + 1;
813 x_locked_entities(rec_count) := p_entity_id;
814 END IF;
815
819 BEGIN
816 ELSIF (p_entity_type = cz_security_pvt.RULEFOLDER) THEN
817 -----get all RULEFOLDER entities to lock
818 -----Do we user connect by or developer will handle it
820 SELECT rule_folder_id
821 BULK
822 COLLECT
823 INTO x_locked_entities
824 FROM cz_rule_folders
825 WHERE cz_rule_folders.object_type = 'RFL'
826 AND cz_rule_folders.deleted_flag = '0'
827 AND cz_rule_folders.rule_folder_id = p_entity_id;
828 EXCEPTION
829 WHEN NO_DATA_FOUND THEN
830 NULL;
831 END;
832 END IF;
833 EXCEPTION
834 WHEN OTHERS THEN
835 RAISE;
836 END get_entities_to_lock;
837
838 ------------>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
839 PROCEDURE get_already_locked_entities(p_entity_type IN VARCHAR2,
840 p_entity_id IN NUMBER,
841 x_locked_entities OUT NOCOPY number_type_tbl,
842 x_checkout_user_tbl OUT NOCOPY varchar_type_tbl)
843 IS
844
845 rec_count NUMBER := 0;
846 l_ui_style cz_ui_defs.ui_style%TYPE;
847 l_locked_entities number_type_tbl;
848 l_checkout_user_tbl varchar_type_tbl;
849
850 BEGIN
851 IF (p_entity_type = cz_security_pvt.MODEL) THEN
852 -----get all model entities to lock
853 SELECT component_id
854 BULK
855 COLLECT
856 INTO l_locked_entities
857 FROM cz_model_ref_expls
858 WHERE model_id = p_entity_id
859 AND deleted_flag = '0'
860 AND component_id IN (SELECT devl_project_id
861 FROM cz_devl_projects
862 WHERE cz_devl_projects.deleted_flag = '0'
863 AND checkout_user IS NOT NULL );
864
865 IF (l_locked_entities.COUNT > 0) THEN
866 FOR J IN l_locked_entities.FIRST..l_locked_entities.LAST
867 LOOP
868 SELECT checkout_user
869 BULK
870 COLLECT
871 INTO l_checkout_user_tbl
872 FROM cz_ps_nodes
873 WHERE cz_ps_nodes.ps_node_id = l_locked_entities(j)
874 AND cz_ps_nodes.deleted_flag = '0'
875 AND cz_ps_nodes.checkout_user IS NOT NULL;
876
877 IF (l_checkout_user_tbl.COUNT > 0) THEN
878 rec_count := x_locked_entities.COUNT + 1;
879 x_locked_entities(rec_count) := l_locked_entities(j);
880 x_checkout_user_tbl(rec_count) := l_checkout_user_tbl(1);
881 END IF;
882 END LOOP;
883 END IF;
884
885 ELSIF (p_entity_type = cz_security_pvt.UI) THEN
886
887 -----get UI style
888 SELECT ui_style
889 INTO l_ui_style
890 FROM cz_ui_defs
891 WHERE cz_ui_defs.ui_def_id = p_entity_id
892 AND cz_ui_defs.deleted_flag = '0';
893
894 -----get all UI entities to lock
895 IF (l_ui_style IN ('0','3') ) THEN
896
897 SELECT ui_def_ref_id
898 BULK
899 COLLECT
900 INTO l_locked_entities
901 FROM cz_ui_nodes
902 WHERE deleted_flag = '0'
903 AND ui_def_ref_id IS NOT NULL
904 AND ui_def_id = p_entity_id;
905
906 IF (l_locked_entities.COUNT > 0) THEN
907 FOR J IN l_locked_entities.FIRST..l_locked_entities.LAST
908 LOOP
909 SELECT checkout_user
910 BULK
911 COLLECT
912 INTO l_checkout_user_tbl
913 FROM cz_ui_defs
914 WHERE cz_ui_defs.ui_def_id = l_locked_entities(j)
915 AND cz_ui_defs.deleted_flag = '0'
916 AND cz_ui_defs.checkout_user IS NOT NULL;
917
918 IF (l_checkout_user_tbl.COUNT > 0) THEN
919 rec_count := x_locked_entities.COUNT + 1;
920 x_locked_entities(rec_count) := l_locked_entities(j);
921 x_checkout_user_tbl(rec_count) := l_checkout_user_tbl(1);
922 END IF;
923 END LOOP;
924 END IF;
925
926 ELSIF (l_ui_style = '7') THEN
927
928 SELECT ref_ui_def_id
929 BULK
930 COLLECT
931 INTO l_locked_entities
932 FROM cz_ui_refs
933 WHERE cz_ui_refs.ui_def_id = p_entity_id
934 AND cz_ui_refs.deleted_flag = '0'
935 AND cz_ui_refs.ref_ui_def_id IN (SELECT ui_def_id
936 FROM cz_ui_defs x
937 WHERE x.deleted_flag = '0'
938 AND x.checkout_user IS NOT NULL
939 AND x.ui_style = '7');
940
941 IF (l_locked_entities.COUNT > 0) THEN
942 FOR J IN l_locked_entities.FIRST..l_locked_entities.LAST
943 LOOP
944 SELECT checkout_user
945 BULK
946 COLLECT
947 INTO l_checkout_user_tbl
948 FROM cz_ui_defs
949 WHERE cz_ui_defs.ui_def_id = l_locked_entities(j)
950 AND cz_ui_defs.deleted_flag = '0'
951 AND cz_ui_defs.checkout_user IS NOT NULL;
952
953 IF (l_checkout_user_tbl.COUNT > 0) THEN
954 rec_count := x_locked_entities.COUNT + 1;
958 END LOOP;
955 x_locked_entities(rec_count) := l_locked_entities(j);
956 x_checkout_user_tbl(rec_count) := l_checkout_user_tbl(1);
957 END IF;
959 END IF;
960 END IF;
961
962 ELSIF (p_entity_type = cz_security_pvt.RULEFOLDER) THEN
963
964 -----get all RULEFOLDER entities to lock
965 -----Do we user connect by or developer will handle it
966 SELECT rule_folder_id,checkout_user
967 BULK
968 COLLECT
969 INTO x_locked_entities,x_checkout_user_tbl
970 FROM cz_rule_folders
971 WHERE cz_rule_folders.object_type = 'RFL'
972 AND cz_rule_folders.deleted_flag = '0'
973 AND cz_rule_folders.devl_project_id = p_entity_id
974 AND cz_rule_folders.checkout_user IS NOT NULL;
975
976 END IF;
977 EXCEPTION
978 WHEN OTHERS THEN
979 RAISE;
980 END get_already_locked_entities;
981
982 ----------->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
983 PROCEDURE update_to_lock_entities(p_entity_type IN VARCHAR2,
984 p_user_name IN VARCHAR2,
985 p_entities_to_lock IN number_type_tbl)
986 IS
987
988 l_table_name VARCHAR2(128);
989 l_primary_key_name VARCHAR2(128);
990 l_user_id NUMBER := FND_GLOBAL.USER_ID;
991 l_str VARCHAR2(2000);
992 l_event_note VARCHAR2(2000);
993 l_entity NUMBER;
994 BEGIN
995 IF (p_entity_type = cz_security_pvt.MODEL) THEN
996 l_table_name := 'cz_devl_projects';
997 l_primary_key_name := 'devl_project_id';
998 l_entity := 2;
999 ELSIF (p_entity_type = cz_security_pvt.UI) THEN
1000 l_table_name := 'cz_ui_defs';
1001 l_primary_key_name := 'ui_def_id';
1002 l_entity := 3;
1003 ELSIF (p_entity_type = cz_security_pvt.RULEFOLDER) THEN
1004 l_table_name := 'cz_rule_folders';
1005 l_primary_key_name := 'rule_folder_id';
1006 l_entity := 4;
1007 END IF;
1008
1009 IF (p_entities_to_lock.COUNT > 0) THEN
1010 FOR toLock IN p_entities_to_lock.FIRST..p_entities_to_lock.LAST
1011 LOOP
1012 EXECUTE IMMEDIATE
1013 'UPDATE '||l_table_name||' SET checkout_user = :1 WHERE '||l_primary_key_name||' = :2 '
1014 USING p_user_name,p_entities_to_lock(toLock);
1015 END LOOP;
1016 END IF;
1017 EXCEPTION
1018 WHEN OTHERS THEN
1019 RAISE;
1020 END update_to_lock_entities;
1021
1022 ---------->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
1023 PROCEDURE update_to_unlock_entities(p_entity_type IN VARCHAR2,
1024 p_entities_to_unlock IN number_type_tbl)
1025 IS
1026
1027 BEGIN
1028 update_to_lock_entities(p_entity_type,'',p_entities_to_unlock);
1029 EXCEPTION
1030 WHEN OTHERS THEN
1031 RAISE;
1032 END update_to_unlock_entities;
1033
1034 ----------->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
1035 ------function that checks if it is necessary to lock
1036 ------a model
1037 PROCEDURE is_lock_required (p_lock_profile IN VARCHAR2,
1038 x_return_status OUT NOCOPY VARCHAR2,
1039 x_msg_data OUT NOCOPY VARCHAR2,
1040 x_msg_count OUT NOCOPY NUMBER)
1041 IS
1042 l_profile_value VARCHAR2(100);
1043 PROFILE_VALUE_NULL EXCEPTION;
1044
1045 BEGIN
1046 l_profile_value := FND_PROFILE.value(p_lock_profile);
1047 IF (l_profile_value IS NULL) THEN
1048 RAISE PROFILE_VALUE_NULL;
1049 END IF;
1050
1051 IF ( (l_profile_value = 'Y') OR (l_profile_value = 'YES') ) THEN
1052 x_return_status := 'Y';
1053 ELSE
1054 x_return_status := 'N';
1055 END IF;
1056 EXCEPTION
1057 WHEN PROFILE_VALUE_NULL THEN
1058 x_msg_data := CZ_UTILS.GET_TEXT('CZ_SEC_PROFILE_NULL');
1059 x_msg_count := 1;
1060 x_return_status := 'U';
1061 WHEN OTHERS THEN
1062 x_msg_data := CZ_UTILS.GET_TEXT('CZ_SEC_PRIV_FATAL_ERR', 'ERR', SQLERRM);
1063 x_msg_count := 1;
1064 x_return_status := 'U';
1065 END is_lock_required ;
1066
1067 ------------>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
1068 -----procedure to lock entity
1069 PROCEDURE lock_entity (p_api_version IN NUMBER,
1070 p_user_name IN VARCHAR2,
1071 p_entity_type IN VARCHAR2,
1072 p_instance_pk1_value IN NUMBER,
1073 p_lock_type IN VARCHAR2,
1074 x_locked_entities OUT NOCOPY number_type_tbl,
1075 x_return_status OUT NOCOPY VARCHAR2,
1076 x_msg_count OUT NOCOPY NUMBER,
1077 x_msg_data OUT NOCOPY VARCHAR2)
1078 IS
1079 BEGIN
1080 x_return_status := FND_API.G_RET_STS_SUCCESS;
1081 x_msg_count := 0;
1082 x_msg_data := '';
1083 END lock_entity;
1084
1085 ---------------->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
1086 -----procedure called by back end PL/SQL API(s)
1087 -----to lock an entity
1088 PROCEDURE lock_entity (p_model_id IN NUMBER,
1089 p_function_name IN VARCHAR2,
1090 x_locked_entities OUT NOCOPY number_type_tbl,
1091 x_return_status OUT NOCOPY VARCHAR2,
1092 x_msg_count OUT NOCOPY NUMBER,
1096 x_return_status := FND_API.G_RET_STS_SUCCESS;
1093 x_msg_data OUT NOCOPY VARCHAR2)
1094 IS
1095 BEGIN
1097 x_msg_count := 0;
1098 x_msg_data := '';
1099 END lock_entity;
1100
1101 --------------->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
1102 -----API used for Developer check out
1103 PROCEDURE lock_entity (p_model_id IN NUMBER,
1104 p_function_name IN VARCHAR2,
1105 x_return_status OUT NOCOPY VARCHAR2,
1106 x_msg_count OUT NOCOPY NUMBER,
1107 x_msg_data OUT NOCOPY VARCHAR2)
1108 IS
1109 BEGIN
1110 x_return_status := FND_API.G_RET_STS_SUCCESS;
1111 x_msg_count := 0;
1112 x_msg_data := '';
1113 END lock_entity;
1114
1115 --------------->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
1116 PROCEDURE lock_entity (p_api_version IN NUMBER,
1117 p_user_name IN VARCHAR2,
1118 p_entity_type IN VARCHAR2,
1119 p_instance_pk1_value IN NUMBER,
1120 p_lock_type IN VARCHAR2,
1121 x_return_status OUT NOCOPY VARCHAR2,
1122 x_msg_count OUT NOCOPY NUMBER,
1123 x_msg_data OUT NOCOPY VARCHAR2)
1124 IS
1125 BEGIN
1126 x_return_status := FND_API.G_RET_STS_SUCCESS;
1127 x_msg_count := 0;
1128 x_msg_data := '';
1129 END lock_entity;
1130
1131 ---------------->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
1132 PROCEDURE unlock_entity (p_api_version IN NUMBER,
1133 p_user_name IN VARCHAR2,
1134 p_entity_type IN VARCHAR2,
1135 p_instance_pk1_value IN NUMBER,
1136 p_locked_entities IN OUT NOCOPY number_type_tbl,
1137 x_return_status OUT NOCOPY VARCHAR2,
1138 x_msg_count OUT NOCOPY NUMBER,
1139 x_msg_data OUT NOCOPY VARCHAR2)
1140 IS
1141
1142 l_api_name CONSTANT VARCHAR2(30) := 'ulock_entity';
1143 l_api_version CONSTANT NUMBER := 1.0;
1144 l_user_priv VARCHAR2(1);
1145 l_function_name fnd_form_functions.function_name%TYPE;
1146 l_proj_id NUMBER := 0;
1147 l_locked_entities_tbl number_type_tbl;
1148 BEGIN
1149 x_return_status := FND_API.G_RET_STS_SUCCESS;
1150 x_msg_count := 0;
1151 x_msg_data := '';
1152 END unlock_entity;
1153
1154 -------->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
1155 PROCEDURE unlock_entity (p_model_id IN NUMBER,
1156 p_function_name IN VARCHAR2,
1157 p_locked_entities IN OUT NOCOPY number_type_tbl,
1158 x_return_status OUT NOCOPY VARCHAR2,
1159 x_msg_count OUT NOCOPY NUMBER,
1160 x_msg_data OUT NOCOPY VARCHAR2)
1161 IS
1162 BEGIN
1163 x_return_status := FND_API.G_RET_STS_SUCCESS;
1164 x_msg_count := 0;
1165 x_msg_data := '';
1166 END unlock_entity;
1167
1168 ------------>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
1169 PROCEDURE unlock_entity (p_model_id IN NUMBER,
1170 p_function_name IN VARCHAR2,
1171 x_return_status OUT NOCOPY VARCHAR2,
1172 x_msg_count OUT NOCOPY NUMBER,
1173 x_msg_data OUT NOCOPY VARCHAR2)
1174 IS
1175 BEGIN
1176 x_return_status := FND_API.G_RET_STS_SUCCESS;
1177 x_msg_count := 0;
1178 x_msg_data := '';
1179 END unlock_entity;
1180
1181 ------------>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
1182 PROCEDURE unlock_model (p_model_id IN NUMBER,
1183 x_return_status OUT NOCOPY VARCHAR2,
1184 x_msg_count OUT NOCOPY NUMBER,
1185 x_msg_data OUT NOCOPY VARCHAR2)
1186 IS
1187 BEGIN
1188 x_return_status := 'T';
1189 x_msg_count := 0;
1190 x_msg_data := '';
1191 END unlock_model;
1192
1193 ----------->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
1194 PROCEDURE unlock_entity (p_api_version IN NUMBER,
1195 p_user_name IN VARCHAR2,
1196 p_entity_type IN VARCHAR2,
1197 p_instance_pk1_value IN NUMBER,
1198 p_lock_type IN VARCHAR2,
1199 x_return_status OUT NOCOPY VARCHAR2,
1200 x_msg_count OUT NOCOPY NUMBER,
1201 x_msg_data OUT NOCOPY VARCHAR2)
1202 IS
1203 BEGIN
1204 x_return_status := FND_API.G_RET_STS_SUCCESS;
1205 x_msg_count := 0;
1206 x_msg_data := '';
1207 END unlock_entity;
1208
1209 ------------>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
1210 FUNCTION get_user_id(p_user_name IN VARCHAR2)
1211 RETURN NUMBER
1212 IS
1213
1214 l_user_id NUMBER := 0;
1215
1216 BEGIN
1217 SELECT user_id
1218 INTO l_user_id
1219 FROM fnd_user
1220 WHERE user_name = UPPER(p_user_name);
1221 RETURN l_user_id;
1222 END get_user_id;
1223
1224 ------------>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
1228
1225 FUNCTION get_application_id(p_application IN VARCHAR2)
1226 RETURN NUMBER
1227 IS
1229 l_application_id NUMBER := 0;
1230
1231 BEGIN
1232 SELECT application_id
1233 INTO l_application_id
1234 FROM fnd_application
1235 WHERE application_short_name = UPPER(p_application);
1236 RETURN l_application_id ;
1237 END get_application_id;
1238
1239 ------------>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
1240 FUNCTION get_responsibility_id(p_responsibility IN VARCHAR2)
1241 RETURN NUMBER
1242 IS
1243
1244 l_responsibility_id NUMBER := 0;
1245
1246 BEGIN
1247 SELECT responsibility_id
1248 INTO l_responsibility_id
1249 FROM fnd_responsibility
1250 WHERE responsibility_key = UPPER(p_responsibility)
1251 AND application_id = 708;
1252 RETURN l_responsibility_id;
1253 END get_responsibility_id;
1254
1255 ------------->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
1256 ------procedure called by back end PL/SQL API(s)
1257 ------to check if the user has privilege to execute
1258 ------the stored procedure on the model
1259 PROCEDURE has_privileges(p_model_id IN NUMBER,
1260 p_function_name IN VARCHAR2,
1261 x_return_status OUT NOCOPY VARCHAR2,
1262 x_msg_data OUT NOCOPY VARCHAR2,
1263 x_msg_count OUT NOCOPY NUMBER)
1264 IS
1265 BEGIN
1266 x_return_status := HAS_PRIVILEGE;
1267 x_msg_count := 0;
1268 x_msg_data := '';
1269 END has_privileges;
1270
1271 -------------->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
1272 ----This API would return 'T' if the user has edit access on atleast one entity (MODEL,UI,RULEFOLDER),
1273 -----otherwise it will return 'F'.
1274 -----This is used for the enable or disable the edit icon in the repository.
1275 FUNCTION has_model_privileges(p_model_id IN NUMBER)
1276 RETURN VARCHAR2
1277 IS
1278 BEGIN
1279 RETURN 'T';
1280 END has_model_privileges;
1281
1282 ------------->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
1283 -----procedure called by back end API(s) to check for priv
1284 -----and locking entities
1285 PROCEDURE check_priv_and_lock_entity (p_api_version IN NUMBER,
1286 p_user_name IN VARCHAR2,
1287 p_responsibility IN VARCHAR2,
1288 p_application IN VARCHAR2,
1289 p_entity_type IN VARCHAR2,
1290 p_instance_pk1_value IN NUMBER,
1291 x_locked_entities OUT NOCOPY number_type_tbl,
1292 x_return_status OUT NOCOPY VARCHAR2,
1293 x_msg_count OUT NOCOPY NUMBER,
1294 x_msg_data OUT NOCOPY VARCHAR2)
1295 IS
1296
1297 l_api_name CONSTANT VARCHAR2(30) := 'check_priv_and_lock_entity';
1298 l_api_version CONSTANT NUMBER := 1.0;
1299 l_user_id NUMBER := 0;
1300 l_application_id NUMBER := 0;
1301 l_resp_id NUMBER := 0;
1302 l_profile_value VARCHAR2(255) := '';
1303
1304 BEGIN
1305 x_return_status := FND_API.G_RET_STS_SUCCESS;
1306 x_msg_count := 0;
1307
1308 ---check api version
1309 IF NOT FND_API.Compatible_API_Call(l_api_version,p_api_version,l_api_name,G_PKG_NAME) THEN
1310 RAISE G_INCOMPATIBLE_API;
1311 END IF;
1312
1313 -----check if models have to be locked for global operations
1314 l_profile_value := get_profile_value(LOCK_MODELS_FOR_GLOPS);
1315 IF (l_profile_value = 'NO') THEN
1316 RAISE NO_LOCK_CONTROL_REQUIRED;
1317 END IF;
1318
1319 ----get user id
1320 IF (p_user_name IS NULL) THEN
1321 RAISE USER_NAME_NULL;
1322 END IF;
1323
1324 l_user_id := get_user_id(p_user_name);
1325 IF (l_user_id = 0) THEN
1326 RAISE INVALID_USER_NAME;
1327 END IF;
1328
1329 ----get application id
1330 IF (p_application IS NULL) THEN
1331 RAISE APPL_NAME_NULL;
1332 END IF;
1333
1334 l_application_id := get_application_id(p_application);
1335 IF (l_application_id = 0) THEN
1336 RAISE INVALID_APPLICATION;
1337 END IF;
1338
1339 ----get responsibility id
1340 IF (p_responsibility IS NULL) THEN
1341 RAISE RESP_NAME_NULL;
1342 END IF;
1343
1344 l_resp_id := get_responsibility_id(p_responsibility);
1345 IF (l_resp_id = 0) THEN
1346 RAISE INVALID_RESPONSIBILITY;
1347 END IF;
1348
1349 ----fnd initialize
1350 fnd_global.apps_initialize(l_user_id,l_resp_id,l_application_id);
1351
1352 -----lock entity
1353 cz_security_pvt.lock_entity (p_api_version,
1354 p_user_name,
1355 p_entity_type,
1356 p_instance_pk1_value,
1357 DEEP_LOCK,
1358 x_locked_entities,
1359 x_return_status,
1360 x_msg_count,
1361 x_msg_data);
1362
1363 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1364 x_msg_data := CZ_UTILS.GET_TEXT('CZ_SEC_LOCK_ENTITY_ERR');
1365 x_msg_count := 1;
1366 x_return_status := FND_API.G_RET_STS_ERROR;
1367 END IF;
1368
1369 EXCEPTION
1373 x_return_status := FND_API.G_RET_STS_ERROR;
1370 WHEN G_INCOMPATIBLE_API THEN
1371 x_msg_data := CZ_UTILS.GET_TEXT('CZ_SEC_API_VERSION_ERR','CODEVERSION',l_api_version,'VERSION',p_api_version);
1372 x_msg_count := 1;
1374 WHEN NO_LOCK_CONTROL_REQUIRED THEN
1375 NULL; ----do nothing
1376 WHEN USER_NAME_NULL THEN
1377 x_msg_data := CZ_UTILS.GET_TEXT('CZ_SEC_USER_NAME_NULL');
1378 x_msg_count := 1;
1379 x_return_status := FND_API.G_RET_STS_ERROR;
1380 WHEN INVALID_USER_NAME THEN
1381 x_msg_data := CZ_UTILS.GET_TEXT('CZ_SEC_INVALID_USER_NAME','USERNAME',p_user_name);
1382 x_msg_count := 1;
1383 x_return_status := FND_API.G_RET_STS_ERROR;
1384 WHEN APPL_NAME_NULL THEN
1385 x_msg_data := CZ_UTILS.GET_TEXT('CZ_SEC_APPL_NAME_NULL');
1386 x_msg_count := 1;
1387 x_return_status := FND_API.G_RET_STS_ERROR;
1388 WHEN RESP_NAME_NULL THEN
1389 x_msg_data := CZ_UTILS.GET_TEXT('CZ_SEC_RESP_NAME_NULL');
1390 x_msg_count := 1;
1391 x_return_status := FND_API.G_RET_STS_ERROR;
1392 WHEN INVALID_APPLICATION THEN
1393 x_msg_data := CZ_UTILS.GET_TEXT('CZ_SEC_INVALID_APPLICATION','APPLICATION',p_application);
1394 x_msg_count := 1;
1395 x_return_status := FND_API.G_RET_STS_ERROR;
1396 WHEN INVALID_RESPONSIBILITY THEN
1397 x_msg_data := CZ_UTILS.GET_TEXT('CZ_SEC_INVALID_RESPONSIBILITY','RESPONSIBILITY',p_responsibility);
1398 x_msg_count := 1;
1399 x_return_status := FND_API.G_RET_STS_ERROR;
1400 WHEN OTHERS THEN
1401 x_msg_data := SQLERRM;
1402 x_msg_count := 1;
1403 x_return_status := FND_API.G_RET_STS_ERROR;
1404 END check_priv_and_lock_entity;
1405
1406 -------->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
1407 FUNCTION are_models_locked (p_model_id IN NUMBER)
1408 RETURN VARCHAR2
1409 IS
1410 l_models number_type_tbl;
1411 l_status VARCHAR2(2000);
1412 rec_count NUMBER := 0;
1413 l_checkout_user VARCHAR2(40) := '';
1414 l_model_id NUMBER := 0;
1415 MODEL_ID_IS_NULL EXCEPTION;
1416
1417 BEGIN
1418 l_status := '0';
1419 IF (p_model_id IS NULL) THEN
1420 RAISE MODEL_ID_IS_NULL;
1421 END IF;
1422
1423 SELECT component_id
1424 BULK
1425 COLLECT
1426 INTO l_models
1427 FROM cz_model_ref_expls
1428 WHERE model_id = p_model_id
1429 AND ps_node_type = 263
1430 AND deleted_flag = '0';
1431
1432 rec_count := l_models.COUNT + 1;
1433 l_models(rec_count) := p_model_id;
1434
1435 IF (l_models.COUNT > 0) THEN
1436 FOR modelId IN l_models.FIRST..l_models.LAST
1437 LOOP
1438 l_model_id := l_models(modelId);
1439 SELECT checkout_user
1440 INTO l_checkout_user
1441 FROM cz_devl_projects
1442 WHERE cz_devl_projects.devl_project_id = l_model_id
1443 AND cz_devl_projects.deleted_flag = '0';
1444
1445 IF (l_checkout_user IS NULL) THEN
1446 l_status := '0';
1447 ELSIF (l_checkout_user = FND_GLOBAL.user_name) THEN
1448 l_status := '1';
1449 ELSE
1450 l_status := '2';
1451 EXIT;
1452 END IF;
1453 END LOOP;
1454 END IF;
1455 RETURN l_status;
1456 EXCEPTION
1457 WHEN MODEL_ID_IS_NULL THEN
1458 l_status := CZ_UTILS.GET_TEXT('CZ_SEC_MODEL_ID_NULL');
1459 RETURN l_status;
1460 WHEN OTHERS THEN
1461 l_status := SQLERRM;
1462 RETURN l_status;
1463 END are_models_locked;
1464
1465 ------->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
1466 FUNCTION are_models_locked (p_model_id IN NUMBER, p_checkout_user IN VARCHAR2)
1467 RETURN VARCHAR2
1468 IS
1469 l_models number_type_tbl;
1470 l_status VARCHAR2(2000);
1471 l_checkout_user_tbl varchar_type_tbl;
1472
1473 BEGIN
1474 l_status := '0';
1475 SELECT checkout_user
1476 BULK
1477 COLLECT
1478 INTO l_checkout_user_tbl
1479 FROM cz_devl_projects
1480 WHERE cz_devl_projects.devl_project_id IN ( SELECT component_id
1481 FROM cz_model_ref_expls
1482 WHERE model_id = p_model_id
1483 AND ps_node_type = 263
1484 AND deleted_flag = '0')
1485
1486 AND cz_devl_projects.deleted_flag = '0'
1487 AND cz_devl_projects.checkout_user IS NOT NULL;
1488
1489 IF (l_checkout_user_tbl.COUNT > 0) THEN
1490 FOR I IN l_checkout_user_tbl.FIRST..l_checkout_user_tbl.LAST
1491 LOOP
1492 IF (l_checkout_user_tbl(i) = FND_GLOBAL.user_name) THEN
1493 l_status := '1';
1494 ELSIF (l_checkout_user_tbl(i) <> FND_GLOBAL.user_name) THEN
1495 l_status := '2';
1496 EXIT;
1497 END IF;
1498 END LOOP;
1499 END IF;
1500 RETURN l_status;
1501 EXCEPTION
1502 WHEN OTHERS THEN
1503 l_status := SQLERRM;
1504 RETURN l_status;
1505 END are_models_locked;
1506
1507 ------->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
1508 FUNCTION check_devl_project(p_entity_id IN NUMBER,
1509 p_entity IN VARCHAR2)
1510 RETURN VARCHAR2
1511 IS
1512 l_checkout_user VARCHAR2(40);
1513 l_proj_id NUMBER := 0 ;
1514 l_status VARCHAR2(1) := '0';
1518 ELSIF (p_entity = cz_security_pvt.UI) THEN
1515 BEGIN
1516 IF (p_entity = cz_security_pvt.MODEL) THEN
1517 l_proj_id := p_entity_id;
1519 SELECT devl_project_id INTO l_proj_id
1520 FROM cz_ui_defs
1521 WHERE ui_def_id = p_entity_id
1522 AND deleted_flag = '0';
1523 ELSIF (p_entity = cz_security_pvt.RULEFOLDER) THEN
1524 begin
1525 SELECT devl_project_id INTO l_proj_id
1526 FROM cz_rule_folders
1527 WHERE rule_folder_id = p_entity_id
1528 AND object_type = 'RFL'
1529 AND deleted_flag = '0';
1530 exception
1531 when no_data_found then
1532 null;
1533 end;
1534 END IF;
1535
1536 begin
1537 SELECT checkout_user INTO l_checkout_user
1538 FROM cz_devl_projects
1539 WHERE cz_devl_projects.devl_project_id = l_proj_id;
1540 exception
1541 when no_data_found then
1542 null;
1543 end;
1544
1545 IF ( (l_checkout_user IS NOT NULL) AND (l_checkout_user = FND_GLOBAL.user_name) ) THEN
1546 RETURN '3';
1547 ELSIF ( (l_checkout_user IS NOT NULL) AND (l_checkout_user <> FND_GLOBAL.user_name) ) THEN
1548 RETURN '4';
1549 ELSE
1550 RETURN '0';
1551 END IF;
1552 EXCEPTION
1553 WHEN OTHERS tHEN
1554 RETURN l_status;
1555 END check_devl_project;
1556
1557 ------>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
1558 FUNCTION check_devl_project(p_checkout_user IN VARCHAR2)
1559 RETURN VARCHAR2
1560 IS
1561 BEGIN
1562 IF ( (p_checkout_user IS NOT NULL) AND (p_checkout_user = FND_GLOBAL.user_name) ) THEN
1563 RETURN '3';
1564 ELSIF ( (p_checkout_user IS NOT NULL) AND (p_checkout_user <> FND_GLOBAL.user_name) ) THEN
1565 RETURN '4';
1566 ELSE
1567 RETURN '0';
1568 END IF;
1569 EXCEPTION
1570 WHEN OTHERS tHEN
1571 RETURN '0';
1572 END check_devl_project;
1573
1574 ------>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
1575 FUNCTION lock_model_structure (p_model_id IN NUMBER)
1576 RETURN VARCHAR2
1577 IS
1578 l_ret_status VARCHAR2(1);
1579 l_checkout_user VARCHAR2(40);
1580 l_event_note VARCHAR2(2000);
1581
1582 BEGIN
1583 l_ret_status := 'T';
1584 BEGIN
1585 SELECT checkout_user
1586 INTO l_checkout_user
1587 FROM cz_ps_nodes
1588 WHERE ps_node_id = p_model_id;
1589 EXCEPTION
1590 WHEN NO_DATA_FOUND THEN
1591 l_checkout_user := NULL;
1592 END;
1593
1594 IF (l_checkout_user IS NULL) THEN
1595 UPDATE cz_ps_nodes
1596 SET checkout_user = FND_GLOBAL.user_name
1597 WHERE ps_node_id = p_model_id;
1598
1599 l_event_note := CZ_UTILS.GET_TEXT('CZ_SEC_LOCK_MODEL_STRUCTURE',
1600 'LOCKEDBY',FND_GLOBAL.user_name,'LOCKDATE',
1601 to_char(sysdate,'mm-dd-yyyy hh24:mi:ss'));
1602 log_lock_history (2,p_model_id,'LOCK_STRUCTURE',l_event_note);
1603 ELSIF (l_checkout_user <> FND_GLOBAL.user_name) THEN
1604 l_ret_status := 'F';
1605 END IF;
1606 RETURN l_ret_status;
1607 EXCEPTION
1608 WHEN OTHERS THEN
1609 RETURN 'F';
1610 END lock_model_structure ;
1611 ------->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
1612 FUNCTION is_model_structure_locked (p_model_id IN NUMBER)
1613 RETURN VARCHAR2
1614 IS
1615
1616 l_status VARCHAR2(2000) := '0';
1617 l_checkout_user VARCHAR2(40) := '';
1618 MODEL_ID_IS_NULL EXCEPTION;
1619
1620 BEGIN
1621 l_status := '0';
1622 IF (p_model_id IS NULL) THEN
1623 RAISE MODEL_ID_IS_NULL;
1624 END IF;
1625
1626 l_status := check_devl_project(p_model_id,cz_security_pvt.MODEL);
1627 IF (l_status IN ('3','4')) THEN
1628 RAISE MODEL_LOCKED;
1629 END IF;
1630
1631 SELECT checkout_user
1632 INTO l_checkout_user
1633 FROM cz_ps_nodes
1634 WHERE cz_ps_nodes.ps_node_id = p_model_id
1635 AND cz_ps_nodes.deleted_flag = '0';
1636
1637 IF (l_checkout_user IS NULL) THEN
1638 l_status := '0';
1639 ELSIF (l_checkout_user = FND_GLOBAL.user_name) THEN
1640 l_status := '1';
1641 ELSE
1642 l_status := '2';
1643 END IF;
1644 RETURN l_status;
1645 EXCEPTION
1646 WHEN NO_DATA_FOUND THEN
1647 l_status := CZ_UTILS.GET_TEXT('CZ_SEC_NO_MODEL_FOUND', 'MODELID', p_model_id);
1648 RETURN l_status;
1649 WHEN MODEL_LOCKED THEN
1650 RETURN l_status;
1651 WHEN MODEL_ID_IS_NULL THEN
1652 l_status := CZ_UTILS.GET_TEXT('CZ_SEC_MODEL_ID_NULL');
1653 RETURN l_status;
1654 WHEN OTHERS THEN
1655 l_status := SQLERRM;
1656 RETURN l_status;
1657 END is_model_structure_locked;
1658
1659 --------->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
1660 FUNCTION unlock_model_structure (p_model_id IN NUMBER)
1661 RETURN VARCHAR2
1662 IS
1663 l_ret_status VARCHAR2(1) := 'T';
1664 l_checkout_user VARCHAR2(40);
1665 l_event_note VARCHAR2(2000);
1666 BEGIN
1667 l_ret_status := check_devl_project(p_model_id,cz_security_pvt.MODEL);
1668 IF (l_ret_status <> '0') THEN
1669 RETURN 'F';
1670 ELSE
1671 l_ret_status := 'T';
1672 END IF;
1673
1677 FROM cz_ps_nodes
1674 BEGIN
1675 SELECT checkout_user
1676 INTO l_checkout_user
1678 WHERE ps_node_id = p_model_id;
1679 EXCEPTION
1680 WHEN NO_DATA_FOUND THEN
1681 l_checkout_user := NULL;
1682 END;
1683
1684 IF ( (l_checkout_user IS NOT NULL) AND (l_checkout_user = FND_GLOBAL.user_name) ) THEN
1685 UPDATE cz_ps_nodes
1686 SET checkout_user = NULL
1687 WHERE ps_node_id = p_model_id;
1688
1689 l_event_note := CZ_UTILS.GET_TEXT('CZ_SEC_UNLOCK_MODEL_STRUCTURE',
1690 'UNLOCKEDBY',FND_GLOBAL.user_name,'UNLOCKDATE',to_char(sysdate,'mm-dd-yyyy hh24:mi:ss'));
1691 log_lock_history (2,p_model_id,'UNLOCK_STRUCTURE',l_event_note);
1692 ELSIF ( (l_checkout_user IS NOT NULL) AND (l_checkout_user <> FND_GLOBAL.user_name) )THEN
1693 l_ret_status := 'F';
1694 END IF;
1695 RETURN l_ret_status;
1696 EXCEPTION
1697 WHEN OTHERS THEN
1698 RETURN 'F';
1699 END unlock_model_structure ;
1700
1701 --------->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
1702 FUNCTION is_root_ui_locked (p_ui_def_id IN NUMBER)
1703 RETURN VARCHAR2
1704 IS
1705 l_ui_bl number_type_tbl;
1706 l_status VARCHAR2(2000);
1707 rec_count NUMBER := 0;
1708 l_checkout_user VARCHAR2(40) := '';
1709 l_checkout_user_tbl varchar_type_tbl;
1710 UI_DEF_ID_IS_NULL EXCEPTION;
1711
1712 BEGIN
1713 l_status := '0';
1714 IF (p_ui_def_id IS NULL) THEN
1715 RAISE UI_DEF_ID_IS_NULL;
1716 END IF;
1717
1718 l_status := check_devl_project(p_ui_def_id,cz_security_pvt.UI);
1719 IF ( (l_status = '3') OR (l_status = '4') ) THEN
1720 RAISE MODEL_LOCKED;
1721 END IF;
1722
1723 get_already_locked_entities(cz_security_pvt.UI,p_ui_def_id,l_ui_bl,l_checkout_user_tbl);
1724 IF (l_checkout_user_tbl.COUNT > 0) THEN
1725 FOR uiId IN l_checkout_user_tbl.FIRST..l_checkout_user_tbl.LAST
1726 LOOP
1727 IF ( (l_checkout_user IS NOT NULL) AND (l_checkout_user_tbl(uiId) <> FND_GLOBAL.user_name)) THEN
1728 l_status := CZ_UTILS.GET_TEXT('CZ_SEC_ENTITY_IS_LOCKED','ObjectId', cz_security_pvt.UI,
1729 'Id',l_ui_bl(uiId),'User', l_checkout_user_tbl(uiId) );
1730 EXIT;
1731 END IF;
1732 END LOOP;
1733 END IF;
1734 RETURN l_status;
1735 EXCEPTION
1736 WHEN UI_DEF_ID_IS_NULL THEN
1737 l_status := CZ_UTILS.GET_TEXT('CZ_SEC_UI_DEF_ID_NULL');
1738 RETURN l_status;
1739 WHEN MODEL_LOCKED THEN
1740 RETURN l_status;
1741 WHEN OTHERS THEN
1742 l_status := SQLERRM;
1743 RETURN l_status;
1744 END is_root_ui_locked ;
1745
1746 ------->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
1747 FUNCTION is_ui_def_locked (p_ui_def_id IN NUMBER)
1748 RETURN VARCHAR2
1749 IS
1750
1751 l_status VARCHAR2(2000);
1752 l_checkout_user VARCHAR2(40) := '';
1753 UI_DEF_ID_IS_NULL EXCEPTION;
1754
1755 BEGIN
1756 l_status := '0';
1757 IF (p_ui_def_id IS NULL) THEN
1758 RAISE UI_DEF_ID_IS_NULL;
1759 END IF;
1760
1761 l_status := check_devl_project(p_ui_def_id,cz_security_pvt.UI);
1762 IF ( (l_status = '3') OR (l_status = '4') ) THEN
1763 RAISE MODEL_LOCKED;
1764 END IF;
1765
1766 SELECT checkout_user
1767 INTO l_checkout_user
1768 FROM cz_ui_defs
1769 WHERE cz_ui_defs.ui_def_id = p_ui_def_id
1770 AND cz_ui_defs.deleted_flag = '0';
1771
1772 IF (l_checkout_user IS NULL) THEN
1773 l_status := '0';
1774 ELSIF (l_checkout_user = FND_GLOBAL.user_name) THEN
1775 l_status := '1';
1776 ELSE
1777 l_status := '2';
1778 END IF;
1779 RETURN l_status;
1780 EXCEPTION
1781 WHEN NO_DATA_FOUND THEN
1782 l_status := CZ_UTILS.GET_TEXT('CZ_SEC_NO_MODEL_FOUND', 'UIDEFID', p_ui_def_id);
1783 RETURN l_status;
1784 WHEN MODEL_LOCKED THEN
1785 RETURN l_status;
1786 WHEN UI_DEF_ID_IS_NULL THEN
1787 l_status := CZ_UTILS.GET_TEXT('CZ_SEC_UI_DEF_ID_NULL');
1788 RETURN l_status;
1789 WHEN OTHERS THEN
1790 l_status := SQLERRM;
1791 RETURN l_status;
1792 END is_ui_def_locked ;
1793
1794 ----->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
1795 FUNCTION is_ui_def_locked (p_ui_def_id IN NUMBER,
1796 p_checkout_user IN VARCHAR2)
1797 RETURN VARCHAR2
1798 IS
1799
1800 l_status VARCHAR2(2000);
1801 l_checkout_user VARCHAR2(40) := '';
1802
1803 BEGIN
1804 l_status := '0';
1805 IF (l_checkout_user = FND_GLOBAL.user_name) THEN
1806 l_status := '1';
1807 ELSIF (l_checkout_user <> FND_GLOBAL.user_name) THEN
1808 l_status := '2';
1809 END IF;
1810 RETURN l_status;
1811 EXCEPTION
1812 WHEN OTHERS THEN
1813 l_status := SQLERRM;
1814 RETURN l_status;
1815 END is_ui_def_locked ;
1816
1817 ----->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
1818 FUNCTION lock_ui_def (p_ui_def_id IN NUMBER)
1819 RETURN VARCHAR2
1820 IS
1821 l_ret_status VARCHAR2(1);
1822 l_checkout_user VARCHAR2(40);
1823 l_event_note VARCHAR2(2000);
1824 BEGIN
1825 l_ret_status := 'T';
1826 BEGIN
1830 WHERE ui_def_id = p_ui_def_id ;
1827 SELECT checkout_user
1828 INTO l_checkout_user
1829 FROM cz_ui_defs
1831 EXCEPTION
1832 WHEN NO_DATA_FOUND THEN
1833 l_checkout_user := NULL;
1834 END;
1835
1836 IF (l_checkout_user IS NULL) THEN
1837 UPDATE cz_ui_defs
1838 SET checkout_user = FND_GLOBAL.user_name
1839 WHERE ui_def_id = p_ui_def_id ;
1840 l_event_note := CZ_UTILS.GET_TEXT('CZ_SEC_LOCK_UI_EVENT',
1841 'LOCKEDBY',FND_GLOBAL.user_name,'LOCKDATE',to_char(sysdate,'mm-dd-yyyy hh24:mi:ss'));
1842 log_lock_history (3,p_ui_def_id,'LOCK_UI',l_event_note);
1843 ELSIF (l_checkout_user <> FND_GLOBAL.user_name) THEN
1844 l_ret_status := 'F';
1845 END IF;
1846 RETURN l_ret_status;
1847 EXCEPTION
1848 WHEN OTHERS THEN
1849 RETURN 'F';
1850 END lock_ui_def ;
1851
1852 ------>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
1853 FUNCTION unlock_ui_def (p_ui_def_id IN NUMBER)
1854 RETURN VARCHAR2
1855 IS
1856 l_ret_status VARCHAR2(1) := 'T';
1857 l_checkout_user VARCHAR2(40);
1858 l_event_note VARCHAR2(2000);
1859 BEGIN
1860 l_ret_status := check_devl_project(p_ui_def_id,cz_security_pvt.UI);
1861 IF (l_ret_status <> '0') THEN
1862 RETURN 'F';
1863 ELSE
1864 l_ret_status := 'T';
1865 END IF;
1866
1867 BEGIN
1868 SELECT checkout_user
1869 INTO l_checkout_user
1870 FROM cz_ui_defs
1871 WHERE ui_def_id = p_ui_def_id ;
1872 EXCEPTION
1873 WHEN NO_DATA_FOUND THEN
1874 l_checkout_user := NULL;
1875 END;
1876
1877 IF ( (l_checkout_user IS NOT NULL) AND (l_checkout_user = FND_GLOBAL.user_name) ) THEN
1878 UPDATE cz_ui_defs
1879 SET checkout_user = NULL
1880 WHERE ui_def_id = p_ui_def_id ;
1881 l_event_note := CZ_UTILS.GET_TEXT('CZ_SEC_UNLOCK_UI_EVENT',
1882 'UNLOCKEDBY',FND_GLOBAL.user_name,'UNLOCKDATE',to_char(sysdate,'mm-dd-yyyy hh24:mi:ss'));
1883 log_lock_history (3,p_ui_def_id,'UNLOCK_UI',l_event_note);
1884 ELSIF ( (l_checkout_user IS NOT NULL) AND (l_checkout_user <> FND_GLOBAL.user_name) )THEN
1885 l_ret_status := 'F';
1886 END IF;
1887 RETURN l_ret_status;
1888 EXCEPTION
1889 WHEN OTHERS THEN
1890 RETURN 'F';
1891 END unlock_ui_def ;
1892
1893 ---------->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
1894 FUNCTION is_root_rulefolder_locked (p_model_id IN NUMBER)
1895 RETURN VARCHAR2
1896 IS
1897 l_rule_tbl number_type_tbl;
1898 l_status VARCHAR2(2000);
1899 rec_count NUMBER := 0;
1900 l_checkout_user VARCHAR2(40) := '';
1901 l_checkout_user_tbl varchar_type_tbl;
1902 l_user_name VARCHAR2(40) := FND_GLOBAL.user_name;
1903 MODELID_IS_NULL EXCEPTION;
1904
1905 BEGIN
1906 l_status := '0';
1907 IF (p_model_id IS NULL) THEN
1908 RAISE MODELID_IS_NULL;
1909 END IF;
1910
1911 l_status := check_devl_project(p_model_id,cz_security_pvt.RULEFOLDER);
1912 IF ( (l_status = '3') OR (l_status = '4') ) THEN
1913 RAISE MODEL_LOCKED;
1914 END IF;
1915
1916 get_already_locked_entities(cz_security_pvt.RULEFOLDER,p_model_id,l_rule_tbl,l_checkout_user_tbl);
1917 IF (l_checkout_user_tbl.COUNT > 0) THEN
1918 FOR FldId IN l_checkout_user_tbl.FIRST..l_checkout_user_tbl.LAST
1919 LOOP
1920 l_checkout_user := l_checkout_user_tbl(FldId);
1921 IF (l_checkout_user IS NULL) THEN
1922 l_status := '0';
1923 ELSIF (l_checkout_user = FND_GLOBAL.user_name) THEN
1924 l_status := '1';
1925 ELSE
1926 l_status := '2';
1927 EXIT;
1928 END IF;
1929 END LOOP;
1930 END IF;
1931 RETURN l_status;
1932 EXCEPTION
1933 WHEN MODELID_IS_NULL THEN
1934 l_status := CZ_UTILS.GET_TEXT('CZ_SEC_MODELID_IS_NULL');
1935 RETURN l_status;
1936 WHEN MODEL_LOCKED THEN
1937 RETURN l_status;
1938 WHEN OTHERS THEN
1939 l_status := SQLERRM;
1940 RETURN l_status;
1941 END is_root_rulefolder_locked ;
1942
1943 ------>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
1944 FUNCTION is_root_rulefolder_locked (p_model_id IN NUMBER, p_checkout_user IN VARCHAR2)
1945 RETURN VARCHAR2
1946 IS
1947 l_rule_tbl number_type_tbl;
1948 l_status VARCHAR2(2000);
1949 rec_count NUMBER := 0;
1950 l_checkout_user VARCHAR2(40) := '';
1951 l_checkout_user_tbl varchar_type_tbl;
1952 l_user_name VARCHAR2(40) := FND_GLOBAL.user_name;
1953
1954 BEGIN
1955 l_status := '0';
1956 SELECT rule_folder_id,checkout_user
1957 BULK
1958 COLLECT
1959 INTO l_rule_tbl,l_checkout_user_tbl
1960 FROM cz_rule_folders
1961 WHERE cz_rule_folders.object_type = 'RFL'
1962 AND cz_rule_folders.deleted_flag = '0'
1963 AND cz_rule_folders.devl_project_id = p_model_id
1964 AND cz_rule_folders.checkout_user IS NOT NULL;
1965
1966 IF (l_checkout_user_tbl.COUNT > 0) THEN
1967 FOR FldId IN l_checkout_user_tbl.FIRST..l_checkout_user_tbl.LAST
1968 LOOP
1969 l_checkout_user := l_checkout_user_tbl(FldId);
1970 IF (l_checkout_user = FND_GLOBAL.user_name) THEN
1971 l_status := '1';
1975 END IF;
1972 ELSIF (l_checkout_user <> FND_GLOBAL.user_name) THEN
1973 l_status := '2';
1974 EXIT;
1976 END LOOP;
1977 END IF;
1978 RETURN l_status;
1979 EXCEPTION
1980 WHEN OTHERS THEN
1981 l_status := SQLERRM;
1982 RETURN l_status;
1983 END is_root_rulefolder_locked ;
1984
1985 ------->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
1986 FUNCTION is_rulefolder_lockable (p_rule_folder_id IN NUMBER)
1987 RETURN VARCHAR2
1988 IS
1989
1990 l_status VARCHAR2(2000);
1991 l_checkout_user VARCHAR2(40) := '';
1992 l_parent_user VARCHAR2(40) := '';
1993 l_user_name VARCHAR2(40) := FND_GLOBAL.user_name;
1994 l_parent_rule_folder_id NUMBER := 0;
1995 l_fld_user VARCHAR2(40) := '';
1996
1997 RULE_FOLDER_ID_IS_NULL EXCEPTION;
1998 CURSOR checkout_user_cur IS
1999 select checkout_user
2000 FROM cz_rule_folders
2001 where deleted_flag = '0'
2002 and object_type = 'RFL'
2003 and checkout_user is not null
2004 start with rule_folder_id = p_rule_folder_id
2005 connect by prior parent_rule_folder_id = rule_folder_id;
2006
2007 BEGIN
2008 l_status := 'Y';
2009 IF (p_rule_folder_id IS NULL) THEN
2010 RAISE RULE_FOLDER_ID_IS_NULL;
2011 END IF;
2012
2013 l_status := check_devl_project(p_rule_folder_id,cz_security_pvt.RULEFOLDER);
2014 IF ( (l_status = '3') OR (l_status = '4') ) THEN
2015 l_status := 'N';
2016 RAISE MODEL_LOCKED;
2017 ELSE
2018 l_status := 'Y';
2019 END IF;
2020
2021 BEGIN
2022 SELECT checkout_user INTO l_fld_user
2023 FROM cz_rule_folders
2024 WHERE cz_rule_folders.rule_folder_id = p_rule_folder_id
2025 AND cz_rule_folders.object_type = 'RFL';
2026 EXCEPTION
2027 WHEN OTHERS THEN
2028 l_fld_user := NULL;
2029 END;
2030
2031 IF ( (l_fld_user IS NOT NULL) AND (l_fld_user = FND_GLOBAL.user_name) ) THEN
2032 RETURN 'Y';
2033 ELSIF ( (l_fld_user IS NOT NULL) AND (l_fld_user <> FND_GLOBAL.user_name) ) THEN
2034 RETURN 'N';
2035 END IF;
2036
2037 BEGIN
2038 SELECT checkout_user,parent_rule_folder_id
2039 INTO l_parent_user,l_parent_rule_folder_id
2040 FROM cz_rule_folders
2041 WHERE cz_rule_folders.rule_folder_id = (SELECT parent_rule_folder_id
2042 FROM cz_rule_folders
2043 WHERE rule_folder_id = p_rule_folder_id
2044 AND object_type = 'RFL'
2045 AND deleted_flag = '0')
2046 AND cz_rule_folders.object_type = 'RFL';
2047 EXCEPTION
2048 WHEN OTHERS THEN
2049 l_parent_rule_folder_id := 0;
2050 l_parent_user := NULL;
2051 END;
2052
2053 IF (l_parent_user IS NOT NULL) THEN ----AND (l_parent_user <> FND_GLOBAL.user_name) THEN
2054 l_status := 'N';
2055 RAISE MODEL_LOCKED;
2056 END IF;
2057
2058 IF (l_parent_rule_folder_id > 0) THEN
2059
2060 OPEN checkout_user_cur;
2061 LOOP
2062 FETCH checkout_user_cur INTO l_checkout_user;
2063 EXIT WHEN checkout_user_cur%NOTFOUND;
2064 IF ( (l_checkout_user IS NOT NULL) ) THEN
2065 l_status := 'N';
2066 EXIT;
2067 END IF;
2068 END LOOP;
2069 CLOSE checkout_user_cur;
2070 END IF;
2071 RETURN l_status;
2072 EXCEPTION
2073 WHEN NO_DATA_FOUND THEN
2074 l_status := CZ_UTILS.GET_TEXT('CZ_SEC_NO_MODEL_FOUND','RULEFOLDERID',p_rule_folder_id );
2075 RETURN l_status;
2076 WHEN MODEL_LOCKED THEN
2077 RETURN l_status;
2078 WHEN RULE_FOLDER_ID_IS_NULL THEN
2079 l_status := CZ_UTILS.GET_TEXT('CZ_SEC_RULE_FOLDER_ID_NULL');
2080 RETURN l_status;
2081 WHEN OTHERS THEN
2082 l_status := SQLERRM;
2083 RETURN l_status;
2084 END is_rulefolder_lockable;
2085
2086 -------->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
2087 FUNCTION is_rulefolder_locked (p_rule_folder_id IN NUMBER)
2088 RETURN VARCHAR2
2089 IS
2090
2091 l_status VARCHAR2(2000);
2092 l_checkout_user VARCHAR2(40) := '';
2093 l_user_name VARCHAR2(40) := FND_GLOBAL.user_name;
2094 l_is_locakable VARCHAR2(2000) := 'N';
2095 RULE_FOLDER_ID_IS_NULL EXCEPTION;
2096
2097 BEGIN
2098 l_status := '0';
2099 IF (p_rule_folder_id IS NULL) THEN
2100 RAISE RULE_FOLDER_ID_IS_NULL;
2101 END IF;
2102
2103 l_status := check_devl_project(p_rule_folder_id,cz_security_pvt.RULEFOLDER);
2104 IF (l_status IN ('3','4')) THEN
2105 RAISE MODEL_LOCKED;
2106 END IF;
2107
2108 l_is_locakable := is_rulefolder_lockable(p_rule_folder_id);
2109 IF (l_is_locakable <> 'Y') THEN
2110 l_status := '2';
2111 RAISE MODEL_LOCKED;
2112 END IF;
2113
2114 BEGIN
2115 SELECT checkout_user
2116 INTO l_checkout_user
2117 FROM cz_rule_folders
2118 WHERE cz_rule_folders.rule_folder_id = p_rule_folder_id
2119 AND cz_rule_folders.object_type = 'RFL'
2123 l_checkout_user := NULL;
2120 AND cz_rule_folders.deleted_flag = '0';
2121 EXCEPTION
2122 WHEN NO_DATA_FOUND THEN
2124 END;
2125
2126 IF (l_checkout_user IS NULL) THEN
2127 l_status := '0';
2128 ELSIF (l_checkout_user = FND_GLOBAL.user_name) THEN
2129 l_status := '1';
2130 ELSE
2131 l_status := '2';
2132 END IF;
2133 RETURN l_status;
2134 EXCEPTION
2135 WHEN NO_DATA_FOUND THEN
2136 l_status := CZ_UTILS.GET_TEXT('CZ_SEC_NO_MODEL_FOUND','RULEFOLDERID',p_rule_folder_id );
2137 RETURN l_status;
2138 WHEN MODEL_LOCKED THEN
2139 RETURN l_status;
2140 WHEN RULE_FOLDER_ID_IS_NULL THEN
2141 l_status := CZ_UTILS.GET_TEXT('CZ_SEC_RULE_FOLDER_ID_NULL');
2142 RETURN l_status;
2143 WHEN OTHERS THEN
2144 l_status := SQLERRM;
2145 RETURN l_status;
2146 END is_rulefolder_locked;
2147
2148 ------------->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
2149 FUNCTION lock_rulefolder(p_rule_folder_id IN NUMBER)
2150 RETURN VARCHAR2
2151 IS
2152 l_ret_status VARCHAR2(1);
2153 l_rule_fld_tbl number_type_tbl;
2154 l_checkout_user_tbl varchar_type_tbl;
2155 rec_count NUMBER := 0;
2156 l_checkout_user VARCHAR2(40) := '';
2157 l_event_note VARCHAR2(2000);
2158 BEGIN
2159 l_ret_status := 'T';
2160 BEGIN
2161 SELECT checkout_user
2162 INTO l_checkout_user
2163 FROM cz_rule_folders
2164 WHERE rule_folder_id = p_rule_folder_id
2165 AND deleted_flag = '0'
2166 AND object_type = 'RFL';
2167 EXCEPTION
2168 WHEN NO_DATA_FOUND THEN
2169 l_checkout_user := NULL;
2170 END;
2171
2172 IF (l_checkout_user IS NULL) THEN
2173 BEGIN
2174 select rule_folder_id,checkout_user
2175 BULK
2176 COLLECT
2177 INTO l_rule_fld_tbl,l_checkout_user_tbl
2178 FROM cz_rule_folders
2179 where deleted_flag = '0'
2180 and object_type = 'RFL'
2181 start with rule_folder_id = p_rule_folder_id
2182 connect by prior rule_folder_id = parent_rule_folder_id;
2183 EXCEPTION
2184 WHEN NO_DATA_FOUND THEN
2185 NULL;
2186 END;
2187
2188 IF (l_checkout_user_tbl.COUNT > 0) THEN
2189 FOR FldId IN l_checkout_user_tbl.FIRST..l_checkout_user_tbl.LAST
2190 LOOP
2191 l_checkout_user := l_checkout_user_tbl(FldId);
2192 IF ( (l_checkout_user IS NOT NULL) AND (l_checkout_user <> FND_GLOBAL.user_name) ) THEN
2193 RETURN 'F';
2194 END IF;
2195 END LOOP;
2196 END IF;
2197
2198 IF (l_rule_fld_tbl.COUNT > 0) THEN
2199 FOR I IN l_rule_fld_tbl.FIRST..l_rule_fld_tbl.LAST
2200 LOOP
2201 UPDATE cz_rule_folders
2202 SET checkout_user = FND_GLOBAL.user_name
2203 WHERE rule_folder_id = l_rule_fld_tbl(i)
2204 AND object_type = 'RFL';
2205 l_event_note := CZ_UTILS.GET_TEXT('CZ_SEC_LOCK_RFL_EVENT',
2206 'LOCKEDBY',FND_GLOBAL.user_name,'LOCKDATE',to_char(sysdate,'mm-dd-yyyy hh24:mi:ss'));
2207 log_lock_history (4,p_rule_folder_id,'LOCK_RULEFOLDER',l_event_note);
2208 END LOOP;
2209 END IF;
2210 ELSIF (l_checkout_user <> FND_GLOBAL.user_name) THEN
2211 l_ret_status := 'F';
2212 END IF;
2213 RETURN l_ret_status;
2214 EXCEPTION
2215 WHEN OTHERS THEN
2216 RETURN 'F';
2217 END lock_rulefolder;
2218
2219 ------>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
2220 FUNCTION unlock_rulefolder(p_rule_folder_id IN NUMBER)
2221 RETURN VARCHAR2
2222 IS
2223 l_ret_status VARCHAR2(1) := 'T';
2224 l_checkout_user VARCHAR2(40);
2225 l_rule_fld_tbl number_type_tbl;
2226 l_event_note VARCHAR2(2000);
2227 BEGIN
2228 l_ret_status := check_devl_project(p_rule_folder_id,cz_security_pvt.RULEFOLDER);
2229 IF (l_ret_status <> '0') THEN
2230 RETURN 'F';
2231 ELSE
2232 l_ret_status := 'T';
2233 END IF;
2234
2235 BEGIN
2236 SELECT checkout_user
2237 INTO l_checkout_user
2238 FROM cz_rule_folders
2239 WHERE rule_folder_id = p_rule_folder_id
2240 AND object_type = 'RFL';
2241 EXCEPTION
2242 WHEN NO_DATA_FOUND THEN
2243 l_checkout_user := NULL;
2244 END;
2245
2246 IF ( (l_checkout_user IS NOT NULL) AND (l_checkout_user = FND_GLOBAL.user_name) ) THEN
2247 BEGIN
2248 select rule_folder_id
2249 BULK
2250 COLLECT
2251 INTO l_rule_fld_tbl
2252 FROM cz_rule_folders
2253 where deleted_flag = '0'
2254 and object_type = 'RFL'
2255 start with rule_folder_id = p_rule_folder_id
2256 connect by prior rule_folder_id = parent_rule_folder_id;
2257 EXCEPTION
2258 WHEN NO_DATA_FOUND THEN
2259 NULL;
2260 END;
2261
2262 IF (l_rule_fld_tbl.COUNT > 0) THEN
2263 FOR I IN l_rule_fld_tbl.FIRST..l_rule_fld_tbl.LAST
2264 LOOP
2265 UPDATE cz_rule_folders
2266 SET checkout_user = NULL
2267 WHERE rule_folder_id = l_rule_fld_tbl(i)
2268 AND object_type = 'RFL';
2269 l_event_note := CZ_UTILS.GET_TEXT('CZ_SEC_UNLOCK_RFL_EVENT',
2273 END IF;
2270 'UNLOCKEDBY',FND_GLOBAL.user_name,'UNLOCKDATE',to_char(sysdate,'mm-dd-yyyy hh24:mi:ss'));
2271 log_lock_history (4,p_rule_folder_id,'UNLOCK_RULEFOLDER',l_event_note);
2272 END LOOP;
2274 ELSIF ( (l_checkout_user IS NOT NULL) AND (l_checkout_user <> FND_GLOBAL.user_name) )THEN
2275 l_ret_status := 'F';
2276 END IF;
2277 RETURN l_ret_status;
2278 EXCEPTION
2279 WHEN OTHERS THEN
2280 RETURN 'F';
2281 END unlock_rulefolder;
2282
2283 ---------->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
2284 FUNCTION is_model_locked (p_model_id IN NUMBER)
2285 RETURN VARCHAR2
2286 IS
2287 l_ui_tbl number_type_tbl;
2288 l_are_models_locked VARCHAR2(2000) := '0';
2289 l_are_uis_locked VARCHAR2(2000) := '0';
2290 l_are_rulefld_locked VARCHAR2(2000) := '0';
2291 l_is_structure_locked VARCHAR2(2000) := '0';
2292 l_checkout_user VARCHAR2(40);
2293 BEGIN
2294 ----check devl proj
2295 SELECT checkout_user
2296 INTO l_checkout_user
2297 FROM cz_devl_projects
2298 WHERE cz_devl_projects.devl_project_id = p_model_id;
2299
2300 IF ( (l_checkout_user IS NOT NULL) AND (l_checkout_user = FND_GLOBAL.user_name) ) THEN
2301 RETURN '1';
2302 ELSIF ((l_checkout_user IS NOT NULL) AND (l_checkout_user <> FND_GLOBAL.user_name) ) THEN
2303 RETURN '2';
2304 END IF;
2305
2306 ---check model structure
2307 l_are_models_locked := are_models_locked (p_model_id);
2308 l_is_structure_locked := is_model_structure_locked (p_model_id);
2309 IF ( (l_is_structure_locked = '2') OR (l_is_structure_locked = '4') ) THEN
2310 RETURN l_is_structure_locked ;
2311 END IF;
2312
2313 BEGIN
2314 SELECT ui_def_id
2315 BULK
2316 COLLECT
2317 INTO l_ui_tbl
2318 FROM cz_ui_defs
2319 WHERE cz_ui_defs.devl_project_id = p_model_id
2320 AND cz_ui_defs.deleted_flag = '0'
2321 AND cz_ui_defs.checkout_user IS NOT NULL;
2322 EXCEPTION
2323 WHEN NO_DATA_FOUND THEN
2324 NULL;
2325 END;
2326
2327 ----check uis
2328 IF (l_ui_tbl.COUNT > 0) THEN
2329 FOR ui IN l_ui_tbl.FIRST..l_ui_tbl.LAST
2330 LOOP
2331 l_are_uis_locked := is_ui_def_locked (l_ui_tbl(ui));
2332 IF (l_are_uis_locked NOT IN ('0','1') ) THEN
2333 EXIT;
2334 END IF;
2335 END LOOP;
2336 END IF;
2337
2338 ---check rulefolders
2339 l_are_rulefld_locked := is_root_rulefolder_locked (p_model_id);
2340 IF ( (l_are_models_locked = '0') AND (l_are_rulefld_locked = '0')
2341 AND (l_are_uis_locked = '0') AND (l_is_structure_locked = '0') ) THEN
2342 RETURN '0';
2343 ELSE
2344 RETURN '3';
2345 END IF;
2346 EXCEPTION
2347 WHEN OTHERS THEN
2348 RETURN 'U';
2349 END is_model_locked;
2350
2351 ---------------->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
2352 FUNCTION is_model_locked (p_model_id IN NUMBER, p_checkout_user IN VARCHAR2, p_username IN VARCHAR2)
2353 RETURN VARCHAR2
2354 IS
2355 l_ui_tbl number_type_tbl;
2356 l_are_models_locked VARCHAR2(2000) := '0';
2357 l_are_uis_locked VARCHAR2(2000) := '0';
2358 l_are_rulefld_locked VARCHAR2(2000) := '0';
2359 l_checkout_user VARCHAR2(40);
2360 l_ui_checkout_tbl varchar_type_tbl;
2361
2362
2363 BEGIN
2364 IF ( (p_checkout_user IS NOT NULL) AND (p_checkout_user = FND_GLOBAL.user_name) ) THEN
2365 RETURN '1';
2366 ELSIF ((p_checkout_user IS NOT NULL) AND (p_checkout_user <> FND_GLOBAL.user_name) ) THEN
2367 RETURN '2';
2368 END IF;
2369
2370 ---check model structure
2371 l_are_models_locked := are_models_locked (p_model_id,p_checkout_user);
2372
2373 BEGIN
2374 SELECT ui_def_id,checkout_user
2375 BULK
2376 COLLECT
2377 INTO l_ui_tbl,l_ui_checkout_tbl
2378 FROM cz_ui_defs
2379 WHERE cz_ui_defs.devl_project_id = p_model_id
2380 AND cz_ui_defs.deleted_flag = '0'
2381 AND cz_ui_defs.checkout_user IS NOT NULL;
2382 EXCEPTION
2383 WHEN NO_DATA_FOUND THEN
2384 NULL;
2385 END;
2386
2387 ----check uis
2388 IF (l_ui_tbl.COUNT > 0) THEN
2389 FOR ui IN l_ui_tbl.FIRST..l_ui_tbl.LAST
2390 LOOP
2391 l_are_uis_locked := is_ui_def_locked (l_ui_tbl(ui),l_ui_checkout_tbl(ui));
2392 IF (l_are_uis_locked NOT IN ('0','1') ) THEN
2393 EXIT;
2394 END IF;
2395 END LOOP;
2396 END IF;
2397
2398 ---check rulefolders
2399 l_are_rulefld_locked := is_root_rulefolder_locked (p_model_id,p_checkout_user);
2400 IF ( (l_are_models_locked = '0') AND (l_are_rulefld_locked = '0')
2401 AND (l_are_uis_locked = '0') ) THEN
2402 RETURN '0';
2403 ELSE
2404 RETURN '3';
2405 END IF;
2406 EXCEPTION
2407 WHEN OTHERS THEN
2408 RETURN 'U';
2409 END is_model_locked;
2410
2411 ----------------->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
2412 -----check lock on a entity
2413 PROCEDURE is_model_locked (p_devl_project_id IN VARCHAR2,
2414 x_return_status OUT NOCOPY VARCHAR2,
2415 x_msg_count OUT NOCOPY NUMBER,
2419 l_checkout_user VARCHAR2(40) := NULL;
2416 x_msg_data OUT NOCOPY VARCHAR2)
2417 IS
2418
2420 l_deleted_flag VARCHAR2(1) := '0';
2421 MODEL_DELETED EXCEPTION;
2422 MODEL_IS_LOCKED EXCEPTION;
2423
2424 BEGIN
2425 x_return_status := 'F';
2426 x_msg_count := 0;
2427 x_msg_data := '';
2428 SELECT checkout_user,deleted_flag INTO l_checkout_user,l_deleted_flag
2429 FROM cz_devl_projects
2430 WHERE cz_devl_projects.devl_project_id = p_devl_project_id;
2431
2432 IF (l_deleted_flag = '1') THEN
2433 RAISE MODEL_DELETED;
2434 END IF;
2435
2436 IF (l_checkout_user IS NOT NULL) THEN
2437 RAISE MODEL_IS_LOCKED;
2438 END IF;
2439 EXCEPTION
2440 WHEN MODEL_DELETED THEN
2441 x_return_status := 'T';
2442 x_msg_count := 1;
2443 x_msg_data := CZ_UTILS.GET_TEXT('CZ_MODEL_DOES_NOT_EXIST', 'PROJID', p_devl_project_id);
2444 WHEN MODEL_IS_LOCKED THEN
2445 x_return_status := 'T';
2446 x_msg_count := 1;
2447 x_msg_data := CZ_UTILS.GET_TEXT('CZ_MODEL_IS_LOCKED', 'USER', l_checkout_user);
2448 WHEN OTHERS THEN
2449 x_return_status := 'T';
2450 x_msg_count := 1;
2451 x_msg_data := CZ_UTILS.GET_TEXT('CZ_SEC_FATAL_ERR', 'ERROR', SQLERRM);
2452 END is_model_locked ;
2453
2454 ------------>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
2455 FUNCTION is_model_lockable (p_model_id IN NUMBER)
2456 RETURN VARCHAR2
2457 IS
2458 l_ui_tbl number_type_tbl;
2459 l_are_models_locked VARCHAR2(2000) := '0';
2460 l_are_uis_locked VARCHAR2(2000) := '0';
2461 l_are_rulefld_locked VARCHAR2(2000) := '0';
2462 l_checkout_user VARCHAR2(40);
2463
2464 BEGIN
2465 ----check devl proj
2466 SELECT checkout_user
2467 INTO l_checkout_user
2468 FROM cz_devl_projects
2469 WHERE cz_devl_projects.devl_project_id = p_model_id;
2470
2471 IF ( (l_checkout_user IS NOT NULL) AND (l_checkout_user = FND_GLOBAL.user_name) ) THEN
2472 RETURN 'Y';
2473 ELSIF ((l_checkout_user IS NOT NULL) AND (l_checkout_user <> FND_GLOBAL.user_name) ) THEN
2474 RETURN 'N';
2475 END IF;
2476
2477 ---check model structure
2478 l_are_models_locked := are_models_locked (p_model_id);
2479
2480 BEGIN
2481 SELECT ui_def_id
2482 BULK
2483 COLLECT
2484 INTO l_ui_tbl
2485 FROM cz_ui_defs
2486 WHERE cz_ui_defs.devl_project_id = p_model_id
2487 AND cz_ui_defs.deleted_flag = '0';
2488 EXCEPTION
2489 WHEN NO_DATA_FOUND THEN
2490 NULL;
2491 END;
2492
2493 ----check uis
2494 IF (l_ui_tbl.COUNT > 0) THEN
2495 FOR ui IN l_ui_tbl.FIRST..l_ui_tbl.LAST
2496 LOOP
2497 l_are_uis_locked := is_ui_def_locked (l_ui_tbl(ui));
2498 IF (l_are_uis_locked NOT IN ('0','1') ) THEN
2499 EXIT;
2500 END IF;
2501 END LOOP;
2502 END IF;
2503
2504 ---check rulefolders
2505 l_are_rulefld_locked := is_root_rulefolder_locked (p_model_id);
2506 IF ( (l_are_models_locked = '0') AND (l_are_rulefld_locked = '0')
2507 AND (l_are_uis_locked = '0') ) THEN
2508 RETURN 'Y';
2509 ELSE
2510 RETURN 'N';
2511 END IF;
2512 EXCEPTION
2513 WHEN OTHERS THEN
2514 RETURN 'U';
2515 END is_model_lockable;
2516
2517 ------>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
2518 FUNCTION is_model_editable (p_model_id IN NUMBER)
2519 RETURN VARCHAR2
2520 IS
2521 l_has_priv_status VARCHAR2(1) := 'F';
2522 l_is_lockable VARCHAR2(1) := 'T';
2523 l_status VARCHAR2(1) := 'N';
2524 l_checkout_user VARCHAR2(40);
2525 l_profile_value VARCHAR2(100);
2526 BEGIN
2527 SELECT checkout_user
2528 INTO l_checkout_user
2529 FROM cz_devl_projects
2530 WHERE devl_project_id = p_model_id
2531 AND deleted_flag = '0';
2532
2533 IF ( (l_checkout_user IS NOT NULL) AND (l_checkout_user <> FND_GLOBAL.user_name) ) THEN
2534 l_is_lockable := 'F';
2535 END IF;
2536 l_has_priv_status := has_model_privileges(p_model_id);
2537 IF ((l_has_priv_status = 'T') AND (l_is_lockable = 'T') ) THEN
2538 RETURN 'T';
2539 ELSE
2540 RETURN 'F';
2541 END IF;
2542 END;
2543
2544 ------->>>>>>>>>>>>>>>>>>>>>>>>>>
2545 FUNCTION is_model_editable (p_model_id IN NUMBER, p_checkout_user IN VARCHAR2, p_user_name IN VARCHAR2)
2546 RETURN VARCHAR2
2547 IS
2548 l_has_priv_status VARCHAR2(1) := 'F';
2549 l_is_lockable VARCHAR2(1) := 'T';
2550 l_status VARCHAR2(1) := 'N';
2551 l_checkout_user VARCHAR2(40);
2552 l_profile_value VARCHAR2(100);
2553 BEGIN
2554 IF ( (p_checkout_user IS NOT NULL) AND (p_checkout_user <> FND_GLOBAL.user_name) ) THEN
2555 l_is_lockable := 'F';
2556 END IF;
2557 IF ((g_has_priv_status = 'T') AND (l_is_lockable = 'T') ) THEN
2558 RETURN 'T';
2559 ELSE
2560 RETURN 'F';
2561 END IF;
2562 END;
2563
2564 -------->>>>>>>>>>>>>>>>>>>>>>>>>>
2565 FUNCTION is_structure_editable (p_model_id IN NUMBER)
2566 RETURN VARCHAR2
2567 IS
2568 l_has_priv_status VARCHAR2(1) := 'F';
2569 l_is_lockable VARCHAR2(1) := 'N';
2570 l_status VARCHAR2(1) := 'N';
2574 l_is_lockable := is_model_structure_locked (p_model_id);
2571 l_profile_value VARCHAR2(100);
2572 BEGIN
2573 l_has_priv_status := has_model_privileges(p_model_id);
2575 l_profile_value := FND_PROFILE.value(LOCK_MODELS_FOR_EDIT);
2576
2577 IF (l_profile_value = 'N') THEN
2578 IF ((l_has_priv_status = 'T') AND (l_is_lockable = '0') ) THEN
2579 RETURN 'F';
2580 ELSIF ((l_has_priv_status = 'T') AND (l_is_lockable = '1') ) THEN
2581 RETURN 'T';
2582 ELSIF ((l_has_priv_status = 'T') AND (l_is_lockable = '3') ) THEN
2583 RETURN 'T';
2584 ELSE
2585 RETURN 'F';
2586 END IF;
2587 ELSIF (l_profile_value = 'Y') THEN
2588 IF ((l_has_priv_status = 'T') AND (l_is_lockable = '0')) THEN
2589 RETURN 'F';
2590 ELSIF ((l_has_priv_status = 'T') AND (l_is_lockable IN ('1','3')) ) THEN
2591 RETURN 'T';
2592 ELSE
2593 RETURN 'F';
2594 END IF;
2595 END IF;
2596
2597 END is_structure_editable ;
2598
2599 ------->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
2600 FUNCTION is_ui_def_editable(p_ui_def_id IN NUMBER)
2601 RETURN VARCHAR2
2602
2603 IS
2604 l_has_priv_status VARCHAR2(1) := 'F';
2605 l_is_lockable VARCHAR2(2000) := 'N';
2606 l_status VARCHAR2(1) := 'N';
2607 l_proj_id NUMBER;
2608 l_profile_value VARCHAR2(100);
2609 BEGIN
2610 SELECT devl_project_id INTO l_proj_id FROM cz_ui_defs WHERE ui_def_id = p_ui_def_id ;
2611 l_has_priv_status := has_privileges(1.0,FND_GLOBAL.user_name,LOCK_UI_FUNC,cz_security_pvt.UI,l_proj_id);
2612 l_is_lockable := is_ui_def_locked(p_ui_def_id);
2613 l_profile_value := FND_PROFILE.value(LOCK_MODELS_FOR_EDIT);
2614 IF (l_profile_value = 'N') THEN
2615 IF ((l_has_priv_status = 'T') AND (l_is_lockable = '0') ) THEN
2616 RETURN 'F';
2617 ELSIF ((l_has_priv_status = 'T') AND (l_is_lockable = '1') ) THEN
2618 RETURN 'T';
2619 ELSIF ((l_has_priv_status = 'T') AND (l_is_lockable = '3') ) THEN
2620 RETURN 'T';
2621 ELSE
2622 RETURN 'F';
2623 END IF;
2624 ELSIF (l_profile_value = 'Y') THEN
2625 IF ((l_has_priv_status = 'T') AND (l_is_lockable = '0')) THEN
2626 RETURN 'F';
2627 ELSIF ((l_has_priv_status = 'T') AND (l_is_lockable IN ('1','3')) ) THEN
2628 RETURN 'T';
2629 ELSE
2630 RETURN 'F';
2631 END IF;
2632 END IF;
2633 END is_ui_def_editable;
2634
2635 ------>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
2636 FUNCTION is_rulefolder_editable(p_rulefolder_id IN NUMBER)
2637 RETURN VARCHAR2
2638
2639 IS
2640 l_has_priv_status VARCHAR2(2000) := 'F';
2641 l_is_lockable VARCHAR2(2000) := 'N';
2642 l_status VARCHAR2(1) := 'N';
2643 l_profile_value VARCHAR2(100);
2644 BEGIN
2645 l_has_priv_status := has_privileges(1.0,FND_GLOBAL.user_name,LOCK_RULEFOLDER_FUNC,
2646 cz_security_pvt.RULEFOLDER,p_rulefolder_id);
2647 l_is_lockable := is_rulefolder_locked(p_rulefolder_id);
2648 l_profile_value := FND_PROFILE.value(LOCK_MODELS_FOR_EDIT);
2649
2650 IF (l_profile_value = 'N') THEN
2651 IF ((l_has_priv_status = 'T') AND (l_is_lockable = '0') ) THEN
2652 RETURN 'F';
2653 ELSIF ((l_has_priv_status = 'T') AND (l_is_lockable = '1') ) THEN
2654 RETURN 'T';
2655 ELSIF ((l_has_priv_status = 'T') AND (l_is_lockable = '3') ) THEN
2656 RETURN 'T';
2657 ELSE
2658 RETURN 'F';
2659 END IF;
2660 ELSIF (l_profile_value = 'Y') THEN
2661 IF ((l_has_priv_status = 'T') AND (l_is_lockable = '0')) THEN
2662 RETURN 'F';
2663 ELSIF ((l_has_priv_status = 'T') AND (l_is_lockable IN ('1','3')) ) THEN
2664 RETURN 'T';
2665 ELSE
2666 RETURN 'F';
2667 END IF;
2668 END IF;
2669 END is_rulefolder_editable;
2670
2671 -----------to be deleted
2672 ------------------to be deletd
2673 FUNCTION is_root_model_locked (p_model_id IN NUMBER)
2674 RETURN VARCHAR2
2675 IS
2676 l_ui_tbl number_type_tbl;
2677 l_are_models_locked VARCHAR2(2000) := 'N';
2678 l_are_uis_locked VARCHAR2(2000) := 'N';
2679 l_are_rulefld_locked VARCHAR2(2000) := 'N';
2680
2681 BEGIN
2682 ---check model structure
2683 l_are_models_locked := are_models_locked (p_model_id);
2684 BEGIN
2685 SELECT ui_def_id
2686 BULK
2687 COLLECT
2688 INTO l_ui_tbl
2689 FROM cz_ui_defs
2690 WHERE cz_ui_defs.devl_project_id = p_model_id
2691 AND cz_ui_defs.deleted_flag = '0';
2692 EXCEPTION
2693 WHEN NO_DATA_FOUND THEN
2694 NULL;
2695 END;
2696
2697 ----check uis
2698 IF (l_ui_tbl.COUNT > 0) THEN
2699 FOR ui IN l_ui_tbl.FIRST..l_ui_tbl.LAST
2700 LOOP
2701 l_are_uis_locked := is_root_ui_locked (l_ui_tbl(ui));
2702 IF (l_are_uis_locked <> 'N') THEN
2703 EXIT;
2704 END IF;
2705 END LOOP;
2706 END IF;
2707
2708 ---check rulefolders
2709 l_are_rulefld_locked := is_root_rulefolder_locked (p_model_id);
2713 ELSE
2710 IF ( (l_are_models_locked = 'Y') OR (l_are_rulefld_locked = 'Y')
2711 OR (l_are_uis_locked = 'Y') ) THEN
2712 RETURN 'Y';
2714 RETURN 'N';
2715 END IF;
2716 EXCEPTION
2717 WHEN OTHERS THEN
2718 RETURN 'U';
2719 END is_root_model_locked;
2720
2721 ---------------
2722 FUNCTION is_parent_rulefolder_locked (p_rule_folder_id IN NUMBER)
2723 RETURN VARCHAR2
2724 IS
2725
2726 l_status VARCHAR2(2000);
2727 l_checkout_user VARCHAR2(40) := '';
2728 RULE_FOLDER_ID_IS_NULL EXCEPTION;
2729
2730 BEGIN
2731 l_status := 'N';
2732 IF (p_rule_folder_id IS NULL) THEN
2733 RAISE RULE_FOLDER_ID_IS_NULL;
2734 END IF;
2735
2736 SELECT checkout_user
2737 INTO l_checkout_user
2738 FROM cz_rule_folders
2739 WHERE cz_rule_folders.rule_folder_id = p_rule_folder_id
2740 AND cz_rule_folders.object_type = 'RFL'
2741 AND cz_rule_folders.deleted_flag = '0';
2742
2743 IF ( (l_checkout_user IS NOT NULL) AND (l_checkout_user <> FND_GLOBAL.user_name)) THEN
2744 l_status := CZ_UTILS.GET_TEXT('CZ_SEC_ENTITY_IS_LOCKED','ObjectId', cz_security_pvt.RULEFOLDER,
2745 'Id',p_rule_folder_id,'User', l_checkout_user);
2746 END IF;
2747 RETURN l_status;
2748 EXCEPTION
2749 WHEN NO_DATA_FOUND THEN
2750 l_status := CZ_UTILS.GET_TEXT('CZ_SEC_NO_MODEL_FOUND','RULEFOLDERID',p_rule_folder_id );
2751 RETURN l_status;
2752 WHEN RULE_FOLDER_ID_IS_NULL THEN
2753 l_status := CZ_UTILS.GET_TEXT('CZ_SEC_RULE_FOLDER_ID_NULL');
2754 RETURN l_status;
2755 WHEN OTHERS THEN
2756 l_status := SQLERRM;
2757 RETURN l_status;
2758 END is_parent_rulefolder_locked;
2759
2760 ------------
2761 FUNCTION is_parent_ui_locked (p_ui_def_id IN NUMBER)
2762 RETURN VARCHAR2
2763 IS
2764
2765 l_status VARCHAR2(2000);
2766 l_checkout_user VARCHAR2(40) := '';
2767 UI_DEF_ID_IS_NULL EXCEPTION;
2768
2769 BEGIN
2770 l_status := 'N';
2771 IF (p_ui_def_id IS NULL) THEN
2772 RAISE UI_DEF_ID_IS_NULL;
2773 END IF;
2774
2775 SELECT checkout_user
2776 INTO l_checkout_user
2777 FROM cz_ui_defs
2778 WHERE cz_ui_defs.ui_def_id = p_ui_def_id
2779 AND cz_ui_defs.deleted_flag = '0';
2780
2781 IF ( (l_checkout_user IS NOT NULL) AND (l_checkout_user <> FND_GLOBAL.user_name)) THEN
2782 l_status := CZ_UTILS.GET_TEXT('CZ_SEC_ENTITY_IS_LOCKED','ObjectId', cz_security_pvt.UI,
2783 'Id',p_ui_def_id,'User', l_checkout_user);
2784 END IF;
2785 RETURN l_status;
2786 EXCEPTION
2787 WHEN NO_DATA_FOUND THEN
2788 l_status := CZ_UTILS.GET_TEXT('CZ_SEC_NO_MODEL_FOUND', 'UIDEFID', p_ui_def_id);
2789 RETURN l_status;
2790 WHEN UI_DEF_ID_IS_NULL THEN
2791 l_status := CZ_UTILS.GET_TEXT('CZ_SEC_UI_DEF_ID_NULL');
2792 RETURN l_status;
2793 WHEN OTHERS THEN
2794 l_status := SQLERRM;
2795 RETURN l_status;
2796 END is_parent_ui_locked ;
2797
2798 ----------------------
2799 FUNCTION is_parent_model_locked (p_model_id IN NUMBER)
2800 RETURN VARCHAR2
2801 IS
2802
2803 l_status VARCHAR2(2000) := 'N';
2804 l_checkout_user VARCHAR2(40) := '';
2805 MODEL_ID_IS_NULL EXCEPTION;
2806
2807 BEGIN
2808 l_status := 'N';
2809 IF (p_model_id IS NULL) THEN
2810 RAISE MODEL_ID_IS_NULL;
2811 END IF;
2812
2813 SELECT checkout_user
2814 INTO l_checkout_user
2815 FROM cz_devl_projects
2816 WHERE cz_devl_projects.devl_project_id = p_model_id
2817 AND cz_devl_projects.deleted_flag = '0';
2818
2819 IF (l_checkout_user IS NOT NULL) THEN
2820 ------l_status := CZ_UTILS.GET_TEXT('CZ_SEC_ENTITY_IS_LOCKED','ObjectId', cz_security_pvt.MODEL,
2821 ----- 'Id',p_model_id,'User', l_checkout_user); */
2822 l_status := 'Y';
2823 ELSE
2824 l_status := 'N';
2825 END IF;
2826 RETURN l_status;
2827 EXCEPTION
2828 WHEN NO_DATA_FOUND THEN
2829 l_status := CZ_UTILS.GET_TEXT('CZ_SEC_NO_MODEL_FOUND', 'MODELID', p_model_id);
2830 RETURN l_status;
2831 WHEN MODEL_ID_IS_NULL THEN
2832 l_status := CZ_UTILS.GET_TEXT('CZ_SEC_MODEL_ID_NULL');
2833 RETURN l_status;
2834 WHEN OTHERS THEN
2835 l_status := SQLERRM;
2836 RETURN l_status;
2837 END is_parent_model_locked;
2838
2839 ------>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
2840 FUNCTION has_model_privileges(p_model_id IN NUMBER, p_object_type IN VARCHAR2)
2841 RETURN VARCHAR2
2842 IS
2843 l_return_status VARCHAR2(1);
2844 BEGIN
2845 IF (p_object_type = 'PRJ') THEN
2846 l_return_status := has_model_privileges(p_model_id);
2847 ELSE
2848 l_return_status := 'T';
2849 END IF;
2850 RETURN l_return_status;
2851 END has_model_privileges;
2852
2853
2854 FUNCTION is_rulefolder_locked(p_rule_folder_id IN NUMBER,p_object_type IN VARCHAR2)
2855 RETURN VARCHAR2
2856 IS
2857 l_return_status VARCHAR2(1);
2858 BEGIN
2859 IF (p_object_type = 'RFL') THEN
2860 l_return_status := is_rulefolder_locked(p_rule_folder_id);
2861 ELSE
2862 l_return_status := 'T';
2863 END IF;
2864 RETURN l_return_status;
2865 END is_rulefolder_locked;
2869 p_parent_rulefolder_id IN NUMBER)
2866
2867 FUNCTION is_rulefolder_editable(p_rulefolder_id IN NUMBER,
2868 p_object_type IN VARCHAR2,
2870 RETURN VARCHAR2
2871 IS
2872 l_return_status VARCHAR2(1);
2873 BEGIN
2874 IF (p_object_type = 'RFL') THEN
2875 l_return_status := is_rulefolder_editable(p_rulefolder_id);
2876 ELSE
2877 l_return_status := is_rulefolder_editable(p_parent_rulefolder_id);
2878 END IF;
2879 RETURN l_return_status;
2880 END is_rulefolder_editable;
2881
2882
2883 FUNCTION is_model_locked (p_model_id IN NUMBER,p_object_type IN VARCHAR2)
2884 RETURN VARCHAR2
2885 IS
2886 l_return_status VARCHAR2(1);
2887 BEGIN
2888 IF (p_object_type = 'PRJ') THEN
2889 l_return_status := is_model_locked (p_model_id);
2890 ELSE
2891 l_return_status := 'T';
2892 END IF;
2893 RETURN l_return_status;
2894 END is_model_locked;
2895
2896 ---------------------------------
2897 FUNCTION is_model_locked (p_model_id IN NUMBER,
2898 p_object_type IN VARCHAR2,
2899 p_checkout_user IN VARCHAR2,
2900 p_flag IN NUMBER)
2901 RETURN VARCHAR2
2902 IS
2903 l_return_status VARCHAR2(1);
2904 BEGIN
2905 IF (p_object_type = 'PRJ') THEN
2906 l_return_status := is_model_locked (p_model_id,p_checkout_user,fnd_global.user_name);
2907 ELSE
2908 l_return_status := 'T';
2909 END IF;
2910 RETURN l_return_status;
2911 END is_model_locked;
2912
2913 -----------------------------------
2914 FUNCTION is_model_editable (p_model_id IN NUMBER,p_object_type IN VARCHAR2)
2915 RETURN VARCHAR2
2916 IS
2917 l_return_status VARCHAR2(1);
2918 BEGIN
2919 IF (p_object_type = 'PRJ') THEN
2920 l_return_status := is_model_editable (p_model_id);
2921 ELSE
2922 l_return_status := 'T';
2923 END IF;
2924 RETURN l_return_status;
2925 END is_model_editable;
2926
2927 --------------------------------------
2928 FUNCTION is_model_editable (p_model_id IN NUMBER,
2929 p_object_type IN VARCHAR2,
2930 p_checkout_user IN VARCHAR2,
2931 p_flag IN VARCHAR2 )
2932 RETURN VARCHAR2
2933 IS
2934 l_return_status VARCHAR2(1);
2935 BEGIN
2936 IF (p_object_type = 'PRJ') THEN
2937 l_return_status := is_model_editable (p_model_id,p_checkout_user,fnd_global.user_name);
2938 ELSE
2939 l_return_status := 'T';
2940 END IF;
2941 RETURN l_return_status;
2942 END is_model_editable;
2943
2944 -------->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
2945 FUNCTION get_user_name(p_user_id IN NUMBER)
2946 RETURN VARCHAR2
2947 IS
2948 l_user_name VARCHAR2(100);
2949 BEGIN
2950 SELECT user_name INTO l_user_name
2951 FROM fnd_user
2952 WHERE user_id = p_user_id ;
2953 RETURN l_user_name ;
2954 EXCEPTION
2955 WHEN OTHERS THEN
2956 RETURN 'NONE';
2957 END get_user_name;
2958
2959 --------->>>>>>>>>>>>>>>>>>>>>>>>>>>>>
2960 FUNCTION get_resp_name (p_user_id IN NUMBER)
2961 RETURN VARCHAR2
2962 IS
2963
2964 CURSOR resp_cur IS SELECT responsibility_id from FND_USER_RESP_GROUPS
2965 WHERE user_id = p_user_id
2966 AND responsibility_application_id = 708;
2967
2968 l_responsibility_id NUMBER := 0;
2969 l_resp_name VARCHAR2(2000);
2970 l_return_str VARCHAR2(2000) := NULL;
2971 BEGIN
2972 OPEN resp_cur;
2973 LOOP
2974 FETCH resp_cur INTO l_responsibility_id;
2975 EXIT WHEN resp_cur%NOTFOUND;
2976 SELECT responsibility_name INTO l_resp_name
2977 FROM fnd_responsibility_tl
2978 WHERE responsibility_id = l_responsibility_id
2979 AND language = userenv('LANG');
2980 l_return_str := l_return_str||', '||l_resp_name;
2981 END LOOP;
2982 CLOSE resp_cur;
2983 l_return_str := RTRIM(l_return_str, ', ');
2984 l_return_str := LTRIM(l_return_str, ', ');
2985 return l_return_str;
2986 EXCEPTION
2987 WHEN OTHERS THEN
2988 l_return_str := ' ';
2989 return l_return_str ;
2990 END get_resp_name ;
2991
2992 ----------->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
2993 -- Stubbed as part of the bug 4861666, as this code is not utilized
2994 -- in the system due to the obsoletion of "View Entity Access" feature
2995 PROCEDURE GET_CZ_GRANTS_UPDATE (p_entity_id IN NUMBER,
2996 p_entity_type IN VARCHAR2,
2997 p_model_id IN NUMBER,
2998 p_priv IN VARCHAR2,
2999 p_user_name in varchar2,
3000 p_role in varchar2)
3001 AS
3002 BEGIN
3003 NULL;
3004 END GET_CZ_GRANTS_UPDATE;
3005 -------------------------------------------------
3006 -----11.5.10 + Locking only
3007 ------------------------------------------------
3008 FUNCTION get_locking_profile_value
3009 RETURN VARCHAR2
3010 IS
3011 l_profile_value VARCHAR2(255);
3012 BEGIN
3013 l_profile_value := FND_PROFILE.VALUE(LOCK_REQUIRE_LOCKING);
3014 RETURN l_profile_value;
3018 PROCEDURE add_to_error_stack(p_model_id IN NUMBER,
3015 END;
3016
3017 -------->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
3019 x_model_name_tbl IN OUT NOCOPY model_name_tbl,
3020 x_checkout_user_tbl IN OUT NOCOPY checkout_user_tbl)
3021 IS
3022
3023 BEGIN
3024 SELECT name, checkout_user
3025 BULK
3026 COLLECT
3027 INTO x_model_name_tbl, x_checkout_user_tbl
3028 FROM cz_devl_projects
3029 WHERE cz_devl_projects.devl_project_id IN (SELECT component_id
3030 FROM cz_model_ref_expls
3031 WHERE cz_model_ref_expls.deleted_flag = '0'
3032 AND cz_model_ref_expls.model_id = p_model_id)
3033 AND (cz_devl_projects.checkout_user IS NOT NULL
3034 AND cz_devl_projects.checkout_user <> FND_GLOBAL.user_name)
3035 AND cz_devl_projects.deleted_flag = '0';
3036 EXCEPTION
3037 WHEN OTHERS THEN
3038 NULL;
3039 END;
3040
3041 --------->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
3042 PROCEDURE get_checkout_user(p_obj_id IN NUMBER,
3043 p_obj_type IN VARCHAR2,
3044 x_checkout_user IN OUT NOCOPY VARCHAR2,
3045 x_model_name IN OUT NOCOPY VARCHAR2)
3046 IS
3047 BEGIN
3048 IF (p_obj_type = 'PRJ') THEN
3049 SELECT name, checkout_user
3050 INTO x_model_name,x_checkout_user
3051 FROM cz_devl_projects
3052 WHERE cz_devl_projects.devl_project_id = p_obj_id ;
3053 ELSIF (p_obj_type = 'UIT') THEN
3054 SELECT template_name, checkout_user
3055 INTO x_model_name,x_checkout_user
3056 FROM cz_ui_templates
3057 WHERE cz_ui_templates.template_id = p_obj_id ;
3058 END IF;
3059 EXCEPTION
3060 WHEN OTHERS THEN
3061 NULL;
3062 END get_checkout_user;
3063
3064 ------------>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
3065 PROCEDURE get_models_to_lock (p_model_id IN NUMBER,
3066 p_references IN NUMBER,
3067 x_models_to_lock OUT NOCOPY number_type_tbl)
3068 IS
3069 BEGIN
3070 x_models_to_lock.DELETE;
3071 IF (p_references = 0) THEN
3072 SELECT distinct a.component_id
3073 BULK
3074 COLLECT
3075 INTO x_models_to_lock
3076 FROM cz_model_ref_expls a
3077 WHERE a.model_id = p_model_id
3078 AND a.deleted_flag = '0'
3079 AND a.component_id IN ( SELECT devl_project_id
3080 FROM cz_devl_projects
3081 WHERE checkout_user IS NULL
3082 AND devl_project_id = a.component_id );
3083 ELSIF (p_references = 1) THEN
3084 SELECT devl_project_id
3085 BULK
3086 COLLECT
3087 INTO x_models_to_lock
3088 FROM cz_devl_projects
3089 WHERE checkout_user IS NULL
3090 AND devl_project_id = p_model_id;
3091 END IF;
3092 EXCEPTION
3093 WHEN NO_DATA_FOUND THEN
3094 NULL;
3095 END;
3096
3097 ------------>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
3098 /*#
3099 * This is the public interface for force unlock operations on a model in Oracle Configurator
3100 * @param p_api_version number. Current version of the API is 1.0
3101 * @param p_model_id number. devl_project_id of the model from cz_devl_projects table
3102 * @param p_unlock_references A value of FND_API.G_TRUE indicates that the child models if any should be
3103 * force unlocked. A value of FND_API.G_FALSE indicates that only the root model
3104 * will be unlocked
3105 * @param p_init_msg_list FND_API.G_TRUE if the API should initialize the FND stack, FND_API.G_FALSE if not.
3106 * @param x_return_status standard FND status. (ex:FND_API.G_RET_STS_SUCCESS )
3107 * @param x_msg_count number of messages on the stack.
3108 * @param x_msg_data standard FND OUT parameter for message. Messages are written to the FND error stack
3109 * @rep:scope public
3110 * @rep:product CZ
3111 * @rep:displayname API for working with force unlock operations on a model
3112 * @rep:lifecycle active
3113 * @rep:compatibility S
3114 * @rep:category BUSINESS_ENTITY CZ_SECURITY
3115 */
3116
3117 PROCEDURE force_unlock_model (p_api_version IN NUMBER,
3118 p_model_id IN NUMBER,
3119 p_unlock_references IN VARCHAR2,
3120 p_init_msg_list IN VARCHAR2,
3121 x_return_status OUT NOCOPY VARCHAR2,
3122 x_msg_count OUT NOCOPY NUMBER,
3123 x_msg_data OUT NOCOPY VARCHAR2)
3124 IS
3125
3126 MODELID_IS_NULL EXCEPTION;
3127 MODEL_UNLOCK_ERR EXCEPTION;
3128 NO_FORCE_UNLOCK_PRIV EXCEPTION;
3129 l_model_tbl cz_security_pvt.number_type_tbl;
3130 l_count NUMBER := 0;
3131 l_unlock_references VARCHAR2(1);
3132 l_has_priv BOOLEAN;
3133 l_checkout_user cz_devl_projects.checkout_user%TYPE;
3134 l_model_name cz_devl_projects.name%TYPE;
3135
3136 BEGIN
3137 ----initialize FND stack
3138 x_return_status := FND_API.G_RET_STS_SUCCESS;
3142 ----initialize the message stack depending on the input parameter
3139 x_msg_data := NULL;
3140 x_msg_count := 0;
3141
3143 IF(p_init_msg_list = FND_API.G_TRUE)THEN fnd_msg_pub.initialize; END IF;
3144
3145 ----check if the input parameter p_model_id is NULL
3146 ----if it is NULL raise an exception
3147 IF (p_model_id IS NULL) THEN
3148 RAISE MODELID_IS_NULL;
3149 END IF; /* end if of IF (p_model_id IS NULL) */
3150
3151 -----check if the user has privilege to force unlock
3152 -----a model. If the user has no privilege to force
3153 -----unlock the model raise an exception
3154 l_has_priv := FND_FUNCTION.TEST(UNLOCK_FUNCTION);
3155 IF (NOT l_has_priv) THEN
3156 RAISE NO_FORCE_UNLOCK_PRIV;
3157 END IF; /* IF (NOT l_has_priv) */
3158
3159 -----validate input parameter p_unlock_references
3160 IF (p_unlock_references IS NULL) THEN
3161 l_unlock_references := DO_NOT_UNLOCK_CHILD_MODELS;
3162 ELSIF (p_unlock_references = FND_API.G_FALSE) THEN
3163 l_unlock_references := DO_NOT_UNLOCK_CHILD_MODELS;
3164 ELSIF (p_unlock_references = FND_API.G_TRUE) THEN
3165 l_unlock_references := UNLOCK_CHILD_MODELS;
3166 END IF; /* end if of (p_unlock_references IS NULL) */
3167
3168 ------if p_unlock_references IS FND_API.G_TRUE then
3169 ------do the following
3170 IF (l_unlock_references = UNLOCK_CHILD_MODELS) THEN
3171 l_model_tbl.DELETE;
3172 BEGIN
3173 SELECT distinct component_id
3174 BULK
3175 COLLECT
3176 INTO l_model_tbl
3177 FROM cz_model_ref_expls
3178 WHERE model_id = p_model_id
3179 AND deleted_flag = '0'
3180 AND ps_node_type IN (263,264);
3181 EXCEPTION
3182 WHEN NO_DATA_FOUND THEN
3183 NULL;
3184 END;
3185 l_count := l_model_tbl.COUNT + 1;
3186 l_model_tbl(l_count) := p_model_id;
3187 IF (l_model_tbl.COUNT > 0) THEN
3188 FOR I IN l_model_tbl.FIRST..l_model_tbl.LAST
3189 LOOP
3190 UPDATE cz_devl_projects
3191 SET cz_devl_projects.checkout_user = NULL,
3192 cz_devl_projects.checkout_time = NULL
3193 WHERE cz_devl_projects.devl_project_id = l_model_tbl(i);
3194 IF (SQL%ROWCOUNT = 0) THEN
3195 get_checkout_user(l_model_tbl(i),'PRJ',l_checkout_user,l_model_name);
3196 RAISE MODEL_UNLOCK_ERR ;
3197 END IF;
3198 END LOOP;
3199 END IF;
3200 ELSE /* else of IF (l_unlock_references = UNLOCK_CHILD_MODELS) THEN */
3201 UPDATE cz_devl_projects
3202 SET cz_devl_projects.checkout_user = NULL,
3203 cz_devl_projects.checkout_time = NULL
3204 WHERE cz_devl_projects.devl_project_id = p_model_id;
3205
3206 IF (SQL%ROWCOUNT = 0) THEN
3207 get_checkout_user(p_model_id,'PRJ',l_checkout_user,l_model_name);
3208 RAISE MODEL_UNLOCK_ERR;
3209 END IF;
3210 END IF; /* end if of IF (l_unlock_references = UNLOCK_CHILD_MODELS) */
3211 COMMIT;
3212 EXCEPTION
3213 WHEN MODELID_IS_NULL THEN
3214 NULL;
3215 WHEN NO_FORCE_UNLOCK_PRIV THEN
3216 x_return_status := FND_API.G_RET_STS_ERROR;
3217 x_msg_count := 1;
3218 FND_MESSAGE.SET_NAME( 'CZ','CZ_NO_FORCE_UNLOCK_PRIV');
3219 --FND_MESSAGE.SET_TOKEN('USERNAME',FND_GLOBAL.USER_NAME);
3220 FND_MSG_PUB.ADD;
3221 fnd_msg_pub.count_and_get(p_count => x_msg_count,p_data => x_msg_data);
3222 WHEN MODEL_UNLOCK_ERR THEN
3223 x_return_status := FND_API.G_RET_STS_ERROR;
3224 x_msg_count := 1;
3225 FND_MESSAGE.SET_NAME( 'CZ','CZ_MODEL_UNLOCK_ERR');
3226 FND_MESSAGE.SET_TOKEN('MODELNAME', l_model_name);
3227 FND_MESSAGE.SET_TOKEN('USERNAME', l_checkout_user);
3228 FND_MSG_PUB.ADD;
3229 fnd_msg_pub.count_and_get(p_count => x_msg_count,p_data => x_msg_data);
3230 WHEN OTHERS THEN
3231 x_return_status := FND_API.G_RET_STS_ERROR;
3232 x_msg_count := 1;
3233 get_checkout_user(p_model_id, 'PRJ', l_checkout_user, l_model_name);
3234 x_msg_data := CZ_UTILS.GET_TEXT('CZ_UNLOCK_FATAL_ERR', 'OBJECTNAME', l_model_name, 'SQLERRM', SQLERRM);
3235 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
3236 END force_unlock_model;
3237
3238 --------------------
3239 /*#
3240 * This is the public interface for force unlock operations on a UI content template in Oracle Configurator
3241 * @param p_api_version number. Current version of the API is 1.0
3242 * @param p_template_id number. Template_id of the template from cz_ui_templates table
3243 * @param p_init_msg_list FND_API.G_TRUE if the API should initialize the FND stack, FND_API.G_FALSE if not.
3244 * @param x_return_status standard FND status. (ex:FND_API.G_RET_STS_SUCCESS )
3245 * @param x_msg_count number of messages on the stack.
3246 * @param x_msg_data standard FND OUT parameter for message. Messages are written to the FND error stack
3247 * @rep:scope public
3248 * @rep:product CZ
3249 * @rep:displayname API for working with force unlock operations on a UI content template
3250 * @rep:lifecycle active
3251 * @rep:compatibility S
3252 * @rep:category BUSINESS_ENTITY CZ_SECURITY
3253 */
3257 p_init_msg_list IN VARCHAR2,
3254
3255 PROCEDURE force_unlock_template (p_api_version IN NUMBER,
3256 p_template_id IN NUMBER,
3258 x_return_status OUT NOCOPY VARCHAR2,
3259 x_msg_count OUT NOCOPY NUMBER,
3260 x_msg_data OUT NOCOPY VARCHAR2)
3261 IS
3262
3263 templateID_IS_NULL EXCEPTION;
3264 template_UNLOCK_ERR EXCEPTION;
3265 NO_FORCE_UNLOCK_PRIV EXCEPTION;
3266 l_template_tbl cz_security_pvt.number_type_tbl;
3267 l_count NUMBER := 0;
3268 l_unlock_references VARCHAR2(1);
3269 l_has_priv BOOLEAN;
3270 l_checkout_user cz_devl_projects.checkout_user%TYPE;
3271 l_template_name cz_devl_projects.name%TYPE;
3272
3273 BEGIN
3274
3275 ----initialize FND stack
3276 x_return_status := FND_API.G_RET_STS_SUCCESS;
3277 x_msg_data := NULL;
3278 x_msg_count := 0;
3279
3280 ----initialize the message stack depending on the input parameter
3281 IF(p_init_msg_list = FND_API.G_TRUE)THEN fnd_msg_pub.initialize; END IF;
3282
3283 ----check if the input parameter p_template_id is NULL
3284 ----if it is NULL raise an exception
3285 IF (p_template_id IS NULL) THEN
3286 RAISE templateID_IS_NULL;
3287 END IF; /* IF (p_template_id IS NULL) */
3288
3289 -----check if the user has privilege to force unlock
3290 -----a template. If the user has no privilege to force
3291 -----unlock the template then raise an exception
3292 l_has_priv := FND_FUNCTION.TEST(UNLOCK_FUNCTION);
3293 IF (NOT l_has_priv) THEN
3294 RAISE NO_FORCE_UNLOCK_PRIV;
3295 END IF; /* IF (NOT l_has_priv) */
3296
3297 -----unlock the template by setting the checkout user
3298 -----and check out time to NULL
3299 UPDATE cz_ui_templates
3300 SET cz_ui_templates.checkout_user = NULL,
3301 cz_ui_templates.checkout_time = NULL
3302 WHERE cz_ui_templates.template_id = p_template_id;
3303
3304 IF (SQL%ROWCOUNT = 0) THEN
3305 get_checkout_user(p_template_id,'UIT',l_checkout_user,l_template_name);
3306 RAISE template_UNLOCK_ERR;
3307 END IF;
3308 COMMIT;
3309 EXCEPTION
3310 WHEN TEMPLATEID_IS_NULL THEN
3311 NULL;
3312 WHEN NO_FORCE_UNLOCK_PRIV THEN
3313 x_return_status := FND_API.G_RET_STS_ERROR;
3314 x_msg_count := 1;
3315 FND_MESSAGE.SET_NAME( 'CZ','CZ_NO_FORCE_UNLOCK_PRIV');
3316 --FND_MESSAGE.SET_TOKEN('USERNAME',FND_GLOBAL.USER_NAME);
3317 FND_MSG_PUB.ADD;
3318 fnd_msg_pub.count_and_get(p_count => x_msg_count,p_data => x_msg_data);
3319 WHEN TEMPLATE_UNLOCK_ERR THEN
3320 x_return_status := FND_API.G_RET_STS_ERROR;
3321 x_msg_count := 1;
3322 FND_MESSAGE.SET_NAME( 'CZ','CZ_UNLOCK_TMPL_ERR');
3323 FND_MESSAGE.SET_TOKEN('TEMPLATENAME', l_template_name);
3324 FND_MESSAGE.SET_TOKEN('USERNAME', l_checkout_user);
3325 FND_MSG_PUB.ADD;
3326 fnd_msg_pub.count_and_get(p_count => x_msg_count,p_data => x_msg_data);
3327 WHEN OTHERS THEN
3328 x_return_status := FND_API.G_RET_STS_ERROR;
3329 x_msg_count := 1;
3330 get_checkout_user(p_template_id, 'UIT', l_checkout_user, l_template_name);
3331 x_msg_data := CZ_UTILS.GET_TEXT('CZ_UNLOCK_FATAL_ERR', 'OBJECTNAME', l_template_name, 'SQLERRM', SQLERRM);
3332 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
3333 END force_unlock_template;
3334
3335 --------------------
3336 /*#
3337 * This is the public interface for lock operations on a UI content template in Oracle Configurator
3338 * @param p_api_version number. Current version of the API is 1.0
3339 * @param p_template_id number. Template_id of the template from cz_ui_templates table
3340 * @param p_commit_flag A value of FND_API.G_TRUE indicates that the a commit be issued at the end of the
3341 * the procedure. A value of FND_API.G_FALSE indicates that no COMMIT is done.
3342 * @param p_init_msg_list FND_API.G_TRUE if the API should initialize the FND stack, FND_API.G_FALSE if not.
3343 * @param x_return_status standard FND status. (ex:FND_API.G_RET_STS_SUCCESS )
3344 * @param x_msg_count number of messages on the stack.
3345 * @param x_msg_data standard FND OUT parameter for message. Messages are written to the FND error stack
3346 * @rep:scope public
3347 * @rep:product CZ
3348 * @rep:displayname API for working with force lock operations on a UI content template
3349 * @rep:lifecycle active
3350 * @rep:compatibility S
3351 * @rep:category BUSINESS_ENTITY CZ_SECURITY
3352 */
3353
3354 PROCEDURE lock_template(p_api_version IN NUMBER,
3355 p_template_id IN NUMBER,
3356 p_commit_flag IN VARCHAR2,
3357 p_init_msg_list IN VARCHAR2,
3358 x_return_status OUT NOCOPY VARCHAR2,
3359 x_msg_count OUT NOCOPY NUMBER,
3360 x_msg_data OUT NOCOPY VARCHAR2)
3361 IS
3362
3363 l_checkout_user cz_ui_templates.checkout_user%TYPE;
3364 l_template_name cz_ui_templates.template_name%TYPE;
3365 TEMPLATE_IS_LOCKED EXCEPTION;
3366 TEMPLATEID_IS_NULL EXCEPTION;
3367 NO_LOCKING_REQUIRED EXCEPTION;
3368 l_commit_flag VARCHAR2(1);
3369 l_lock_profile VARCHAR2(3);
3370
3371 BEGIN
3372
3373 ----initialize FND stack
3377
3374 x_return_status := FND_API.G_RET_STS_SUCCESS;
3375 x_msg_data := NULL;
3376 x_msg_count := 0;
3378 ----initialize the message stack depending on the input parameter
3379 IF(p_init_msg_list = FND_API.G_TRUE)THEN fnd_msg_pub.initialize; END IF;
3380
3381 ----check if locking is enabled
3382 ----if the site level profile for locking is not enabled then
3383 ----there is no need to do locking
3384 l_lock_profile := get_locking_profile_value;
3385 IF (UPPER(NVL(l_lock_profile,'Y')) IN ('N','NO')) THEN
3386 RAISE NO_LOCKING_REQUIRED;
3387 END IF; /*IF (UPPER(NVL(l_lock_profile,'Y')) IN ('N','NO')) */
3388
3389 -----check if the input parameter p_template_id
3390 -----has a value
3391 IF (p_template_id IS NULL) THEN
3392 RAISE TEMPLATEID_IS_NULL;
3393 END IF; /* IF (p_template_id IS NULL) */
3394
3395 -----initialize l_commit_flag
3396 IF (p_commit_flag IS NULL) THEN
3397 l_commit_flag := DO_NOT_COMMIT;
3398 ELSIF (p_commit_flag = FND_API.G_TRUE) THEN
3399 l_commit_flag := DO_COMMIT;
3400 ELSIF (p_commit_flag = FND_API.G_FALSE) THEN
3401 l_commit_flag := DO_NOT_COMMIT;
3402 END IF; /* IF (p_commit_flag IS NULL) */
3403
3404 -----set the checkout_user and checkout_time
3405 UPDATE cz_ui_templates
3406 SET cz_ui_templates.checkout_user = FND_GLOBAL.user_name,
3407 cz_ui_templates.checkout_time = sysdate
3408 WHERE cz_ui_templates.template_id = p_template_id
3409 AND (cz_ui_templates.checkout_user IS NULL);
3410 IF (SQL%ROWCOUNT = 0) THEN
3411 get_checkout_user(p_template_id,'UIT',l_checkout_user,l_template_name);
3412 IF(l_checkout_user<>FND_GLOBAL.user_name AND l_checkout_user is not null)
3413 THEN
3414 RAISE TEMPLATE_IS_LOCKED;
3415 END IF;
3416 END IF;
3417 IF (l_commit_flag = DO_COMMIT) THEN COMMIT; END IF; /* IF (l_commit_flag = '0') */
3418 EXCEPTION
3419 WHEN NO_LOCKING_REQUIRED THEN
3420 NULL;
3421 WHEN TEMPLATEID_IS_NULL THEN
3422 NULL;
3423 WHEN TEMPLATE_IS_LOCKED THEN
3424 x_return_status := FND_API.G_RET_STS_ERROR;
3425 FND_MESSAGE.SET_NAME( 'CZ','CZ_LOCK_TEMPLATE_ERR');
3426 FND_MESSAGE.SET_TOKEN('TEMPLATENAME', l_template_name);
3427 FND_MESSAGE.SET_TOKEN('USERNAME' , l_checkout_user);
3428 FND_MSG_PUB.ADD;
3429 fnd_msg_pub.count_and_get(p_count => x_msg_count,p_data => x_msg_data, p_encoded => FND_API.G_FALSE);
3430 WHEN OTHERS THEN
3431 x_return_status := FND_API.G_RET_STS_ERROR;
3432 x_msg_count := 1;
3433 get_checkout_user(p_template_id, 'UIT', l_checkout_user, l_template_name);
3434 FND_MESSAGE.SET_NAME('CZ','CZ_LOCK_FATAL_ERR');
3435 FND_MESSAGE.SET_TOKEN('OBJECTNAME', l_template_name);
3436 FND_MESSAGE.SET_TOKEN('SQLERRM', SQLERRM);
3437 fnd_msg_pub.count_and_get(p_count => x_msg_count,p_data => x_msg_data);
3438 END lock_template;
3439
3440 --------->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
3441 /*#
3442 * This is the public interface for lock operations on a UI content template in Oracle Configurator
3443 * @param p_api_version number. Current version of the API is 1.0
3444 * @param p_templates_to_lock array of templates to lock
3445 * @param p_commit_flag A value of FND_API.G_TRUE indicates that the a commit be issued at the end of the
3446 * the procedure. A value of FND_API.G_FALSE indicates that no COMMIT is done.
3447 * @param p_init_msg_list FND_API.G_TRUE if the API should initialize the FND stack, FND_API.G_FALSE if not.
3448 * @param x_locked_templates templates locked by this procedure
3449 * @param x_return_status standard FND status. (ex:FND_API.G_RET_STS_SUCCESS )
3450 * @param x_msg_count number of messages on the stack.
3451 * @param x_msg_data standard FND OUT parameter for message. Messages are written to the FND error stack
3452 * @rep:scope public
3453 * @rep:product CZ
3454 * @rep:displayname API for working with force lock operations on a UI content template
3455 * @rep:lifecycle active
3456 * @rep:compatibility S
3457 * @rep:category BUSINESS_ENTITY CZ_SECURITY
3458 */
3459 PROCEDURE lock_template(p_api_version IN NUMBER,
3460 p_templates_to_lock IN cz_security_pvt.number_type_tbl,
3461 p_commit_flag IN VARCHAR2,
3462 p_init_msg_list IN VARCHAR2,
3463 x_locked_templates OUT NOCOPY cz_security_pvt.number_type_tbl,
3464 x_return_status OUT NOCOPY VARCHAR2,
3465 x_msg_count OUT NOCOPY NUMBER,
3466 x_msg_data OUT NOCOPY VARCHAR2)
3467 IS
3468
3469 NO_TEMPLATES_IDS EXCEPTION;
3470 l_count NUMBER;
3471 l_checkout_user cz_ui_templates.checkout_user%TYPE;
3472 l_return_status VARCHAR2(1);
3473
3474 BEGIN
3475
3476 x_return_status := FND_API.G_RET_STS_SUCCESS;
3477
3478 ----initialize the message stack depending on the input parameter
3479 IF(p_init_msg_list = FND_API.G_TRUE)THEN fnd_msg_pub.initialize; END IF;
3480
3481 -----check if the input parameter p_template_id
3482 -----has a value
3483 IF (p_templates_to_lock.COUNT = 0) THEN
3484 RAISE NO_TEMPLATES_IDS;
3485 END IF; /* IF (p_templates_to_lock.COUNT = 0) */
3486
3487 IF (p_templates_to_lock.COUNT > 0) THEN
3491 BEGIN
3488 FOR I IN p_templates_to_lock.FIRST..p_templates_to_lock.LAST
3489 LOOP
3490 l_checkout_user := NULL;
3492 SELECT checkout_user
3493 INTO l_checkout_user
3494 FROM cz_ui_templates
3495 WHERE template_id = p_templates_to_lock(i)
3496 AND checkout_user = FND_GLOBAL.user_name;
3497 EXCEPTION
3498 WHEN NO_DATA_FOUND THEN
3499 l_checkout_user := NULL;
3500 END;
3501
3502 IF (l_checkout_user IS NULL) THEN
3503 l_count := x_locked_templates.COUNT + 1;
3504 x_locked_templates(l_count) := p_templates_to_lock(i);
3505 cz_security_pvt.lock_template(1.0,
3506 p_templates_to_lock(i),
3507 FND_API.G_FALSE,
3508 FND_API.G_FALSE,
3509 l_return_status,
3510 x_msg_count,
3511 x_msg_data);
3512 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3513 x_return_status := FND_API.G_RET_STS_ERROR;
3514 END IF;
3515 END IF;
3516 END LOOP;
3517 END IF;
3518 fnd_msg_pub.count_and_get(p_count => x_msg_count,p_data => x_msg_data,p_encoded => FND_API.G_FALSE);
3519 IF (p_commit_flag = FND_API.G_TRUE) THEN COMMIT; END IF; /* IF (p_commit_flag = '0') */
3520 EXCEPTION
3521 WHEN NO_TEMPLATES_IDS THEN
3522 NULL;
3523 WHEN OTHERS THEN
3524 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3525 x_return_status := SQLERRM;
3526 x_msg_count := 1;
3527 fnd_msg_pub.count_and_get(p_count => x_msg_count,p_data => x_msg_data);
3528 END lock_template;
3529
3530 ------------------------------
3531 ---------->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
3532 /*#
3533 * This is the public interface for unlock operations on a UI content template in Oracle Configurator
3534 * @param p_template_id number. Template_id of the template from cz_ui_templates table
3535 * @param p_init_msg_list FND_API.G_TRUE if the API should initialize the FND stack, FND_API.G_FALSE if not.
3536 * @param x_return_status standard FND status. (ex:FND_API.G_RET_STS_SUCCESS )
3537 * @param x_msg_count number of messages on the stack.
3538 * @param x_msg_data standard FND OUT parameter for message. Messages are written to the FND error stack
3539 * @rep:scope public
3540 * @rep:product CZ
3541 * @rep:displayname API for working with unlock operations on a UI content template
3542 * @rep:lifecycle active
3543 * @rep:compatibility S
3544 * @rep:category BUSINESS_ENTITY CZ_SECURITY
3545 */
3546
3547 PROCEDURE unlock_template(p_api_version IN NUMBER,
3548 p_template_id IN NUMBER,
3549 p_init_msg_list IN VARCHAR2,
3550 x_return_status OUT NOCOPY VARCHAR2,
3551 x_msg_count OUT NOCOPY NUMBER,
3552 x_msg_data OUT NOCOPY VARCHAR2)
3553 IS
3554
3555 TEMPLATEID_IS_NULL EXCEPTION;
3556 TEMPLATE_UNLOCK_ERR EXCEPTION;
3557 NO_LOCKING_REQUIRED EXCEPTION;
3558 l_count NUMBER := 0;
3559 l_checkout_user cz_ui_templates.checkout_user%TYPE;
3560 l_template_name cz_ui_templates.template_name%TYPE;
3561 l_commit_flag VARCHAR2(1);
3562 l_lock_profile VARCHAR2(3);
3563
3564 BEGIN
3565 ----initialize FND stack
3566 x_return_status := FND_API.G_RET_STS_SUCCESS;
3567 x_msg_count := 0;
3568 x_msg_data := NULL;
3569 ----initialize the message stack depending on the input parameter
3570 IF(p_init_msg_list = FND_API.G_TRUE)THEN fnd_msg_pub.initialize; END IF;
3571
3572 -----check if the input parameter p_template_id
3573 -----has a value
3574 IF (p_template_id IS NULL) THEN
3575 RAISE TEMPLATEID_IS_NULL;
3576 END IF; /* IF (p_template_id IS NULL) */
3577
3578 ----check if locking is enabled
3579 ----if the site level profile for locking is not enabled then
3580 ----there is no need to do unlocking
3581 l_lock_profile := get_locking_profile_value;
3582 IF (UPPER(NVL(l_lock_profile,'Y')) IN ('N','NO')) THEN
3583 RAISE NO_LOCKING_REQUIRED;
3584 END IF; /* IF (UPPER(NVL(l_lock_profile,'Y')) IN ('N','NO')) */
3585
3586 -----set the checkout_user and checkout_time
3587 UPDATE cz_ui_templates
3588 SET cz_ui_templates.checkout_user = NULL,
3589 cz_ui_templates.checkout_time = NULL
3590 WHERE cz_ui_templates.template_id = p_template_id
3591 AND (cz_ui_templates.checkout_user IS NULL
3592 OR cz_ui_templates.checkout_user = FND_GLOBAL.user_name);
3593 IF (SQL%ROWCOUNT = 0) THEN
3594 get_checkout_user(p_template_id,'UIT',l_checkout_user,l_template_name);
3595 RAISE TEMPLATE_UNLOCK_ERR;
3596 END IF; /* IF (SQL%ROWCOUNT = 0) */
3597 EXCEPTION
3598 WHEN NO_LOCKING_REQUIRED THEN
3599 NULL;
3600 WHEN TEMPLATEID_IS_NULL THEN
3601 NULL;
3602 WHEN TEMPLATE_UNLOCK_ERR THEN
3603 x_return_status := FND_API.G_RET_STS_ERROR;
3604 x_msg_count := 1;
3605 FND_MESSAGE.SET_NAME( 'CZ','CZ_UNLOCK_TMPL_ERR');
3606 FND_MESSAGE.SET_TOKEN('TEMPLATENAME', l_template_name);
3607 FND_MESSAGE.SET_TOKEN('USERNAME', l_checkout_user);
3608 FND_MSG_PUB.ADD;
3612 x_msg_count := 1;
3609 fnd_msg_pub.count_and_get(p_count => x_msg_count,p_data => x_msg_data);
3610 WHEN OTHERS THEN
3611 x_return_status := FND_API.G_RET_STS_ERROR;
3613 get_checkout_user(p_template_id, 'UIT', l_checkout_user, l_template_name);
3614 x_msg_data := CZ_UTILS.GET_TEXT('CZ_UNLOCK_FATAL_ERR', 'OBJECTNAME', l_template_name, 'SQLERRM', SQLERRM);
3615 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
3616 END unlock_template;
3617
3618 ----------------------
3619 PROCEDURE unlock_template(p_api_version IN NUMBER,
3620 p_templates_to_unlock IN cz_security_pvt.number_type_tbl,
3621 p_commit_flag IN VARCHAR2,
3622 p_init_msg_list IN VARCHAR2,
3623 x_return_status OUT NOCOPY VARCHAR2,
3624 x_msg_count OUT NOCOPY NUMBER,
3625 x_msg_data OUT NOCOPY VARCHAR2)
3626 IS
3627
3628 NO_TEMPLATES_IDS EXCEPTION;
3629 l_return_status VARCHAR2(1);
3630
3631 BEGIN
3632
3633 x_return_status := FND_API.G_RET_STS_SUCCESS;
3634 x_msg_count := 0;
3635 ----initialize the message stack depending on the input parameter
3636 IF(p_init_msg_list = FND_API.G_TRUE)THEN fnd_msg_pub.initialize; END IF;
3637
3638 -----check if the input parameter p_template_id
3639 -----has a value
3640 IF (p_templates_to_unlock.COUNT = 0) THEN
3641 RAISE NO_TEMPLATES_IDS;
3642 END IF; /* IF (p_templates_to_lock.COUNT = 0) */
3643
3644 IF (p_templates_to_unlock.COUNT > 0) THEN
3645 FOR I IN p_templates_to_unlock.FIRST..p_templates_to_unlock.LAST
3646 LOOP
3647 cz_security_pvt.unlock_template(1.0,
3648 p_templates_to_unlock(i),
3649 FND_API.G_FALSE,
3650 l_return_status,
3651 x_msg_count,
3652 x_msg_data);
3653 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3654 x_return_status := FND_API.G_RET_STS_ERROR;
3655 END IF;
3656 END LOOP;
3657 END IF;
3658 fnd_msg_pub.count_and_get(p_count => x_msg_count,p_data => x_msg_data);
3659 IF (p_commit_flag = FND_API.G_TRUE) THEN COMMIT; END IF; /* IF (p_commit_flag = '0') */
3660 EXCEPTION
3661 WHEN NO_TEMPLATES_IDS THEN
3662 NULL;
3663 WHEN OTHERS THEN
3664 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3665 x_return_status := SQLERRM;
3666 x_msg_count := 1;
3667 fnd_msg_pub.count_and_get(p_count => x_msg_count,p_data => x_msg_data);
3668 END unlock_template;
3669
3670 ---------------------
3671 /*#
3672 * This is the public interface for lock operations on a model in Oracle Configurator
3673 * @param p_model_id number. devl_project_id of the model from cz_devl_projects table
3674 * @param p_lock_child_models A value of FND_API.G_TRUE indicates that the child models if any should be
3675 * locked. A value of FND_API.G_FALSE indicates that only the root model
3676 * will be locked
3677 * @param p_commit_flag A value of FND_API.G_TRUE indicates that the a commit be issued at the end of the
3678 * the procedure. A value of FND_API.G_FALSE indicates that no COMMIT is done.
3679 * @param p_init_msg_list FND_API.G_TRUE if the API should initialize the FND stack, FND_API.G_FALSE if not.
3680 * @param x_locked_entities Contains models locked by this procedure call. This when passed as an input parameter
3681 to unlock_model API would ensure that only those models that have been locked by the lock API are unlocked. Models
3682 * that were previously locked would not be unlocked (by the same user). The retaining of the lock state
3683 * is done only during implicit locks and not when an unlock is done from developer.
3684 * @param x_return_status standard FND status. (ex:FND_API.G_RET_STS_SUCCESS )
3685 * @param x_msg_count number of messages on the stack.
3686 * @param x_msg_data standard FND OUT parameter for message. Messages are written to the FND error stack
3687 * @rep:scope public
3688 * @rep:product CZ
3689 * @rep:displayname API for working with lock operations on a model
3690 * @rep:lifecycle active
3691 * @rep:compatibility S
3692 * @rep:category BUSINESS_ENTITY CZ_SECURITY
3693 *
3694 * Validations: The lock_model API validates the following:
3695 * 1. validate input parameters
3696 * 2. Check for the profile value 'CZ: Require Locking'. If 'Yes' then lock model
3697 * otherwise return a status of 'S'
3698 * 3. When doing a lock on the model and its children, if any of the model(s)
3699 * are locked by a different user (it is ok to be locked by the same user)
3700 * an exception is raised.
3701 * The error messages are written to the FND stack and there would be one message
3702 * for each model locked by a different user.
3703 * The message would contain the name of the model and the user who locked it.
3704 *
3705 * Error reporting: Messages are written to FND error stack. The caller would have to get all the
3706 * messages from the stack. No messages are logged to cz_db_logs.
3707 *
3708 * Usage
3712 * p_commit_flag => FND_API.G_TRUE,
3709 * lock model and its children : cz_security_pvt.lock_model(
3710 * p_model_id => <devl_project_id of the model>,
3711 * p_lock_child_models => FND_API.G_TRUE,
3713 * p_init_msg_list => FND_API.G_TRUE,
3714 * x_prev_locked_entities => l_locked_entities,
3715 * x_return_status => x_return_status,
3716 * x_msg_count => x_msg_count,
3717 * x_msg_data => x_msg_data);
3718 *
3719 * lock root model only : cz_security_pvt.lock_model(
3720 * p_model_id => <devl_project_id of the model>,
3721 * p_lock_child_models => FND_API.G_FALSE,
3722 * p_commit_flag => FND_API.G_TRUE,
3723 * p_init_msg_list => FND_API.G_TRUE,
3724 * x_locked_entities => l_locked_entities,
3725 * x_return_status => x_return_status,
3726 * x_msg_count => x_msg_count,
3727 * x_msg_data => x_msg_data);
3728 */
3729
3730 PROCEDURE lock_model(p_api_version IN NUMBER,
3731 p_model_id IN NUMBER,
3732 p_lock_child_models IN VARCHAR2,
3733 p_commit_flag IN VARCHAR2,
3734 p_init_msg_list IN VARCHAR2,
3735 x_locked_entities OUT NOCOPY number_type_tbl,
3736 x_return_status OUT NOCOPY VARCHAR2,
3737 x_msg_count OUT NOCOPY NUMBER,
3738 x_msg_data OUT NOCOPY VARCHAR2)
3739 IS
3740
3741 l_checkout_user cz_devl_projects.checkout_user%TYPE;
3742 l_model_name cz_devl_projects.name%TYPE;
3743 l_model_name_tbl cz_security_pvt.model_name_tbl;
3744 l_checkout_user_tbl cz_security_pvt.checkout_user_tbl;
3745 MODEL_IS_LOCKED EXCEPTION;
3746 MODELID_IS_NULL EXCEPTION;
3747 INVALID_MODEL_ID EXCEPTION;
3748 NO_LOCKING_REQUIRED EXCEPTION;
3749 LOCK_SINGLE_MODEL_ERR EXCEPTION;
3750 l_model_tbl cz_security_pvt.number_type_tbl;
3751 l_prev_locked_models cz_security_pvt.number_type_tbl;
3752 l_count NUMBER := 0;
3753 l_lock_child_models VARCHAR2(1);
3754 l_commit_flag VARCHAR2(1);
3755 l_lock_profile VARCHAR2(3);
3756 l_model_id cz_devl_projects.devl_project_id%TYPE;
3757 l_seeded_flag cz_rp_entries.seeded_flag%TYPE;
3758
3759 BEGIN
3760 ----initialize FND stack
3761 x_return_status := FND_API.G_RET_STS_SUCCESS;
3762 x_msg_data := NULL;
3763 x_msg_count := 0;
3764 x_locked_entities.DELETE;
3765
3766 ----initialize the message stack depending on the input parameter
3767 IF(p_init_msg_list = FND_API.G_TRUE)THEN fnd_msg_pub.initialize; END IF;
3768
3769 ----check if locking is enabled
3770 ----if the site level profile for locking is not enabled then
3771 ----there is no need to do locking
3772 l_lock_profile := get_locking_profile_value;
3773 IF (UPPER(NVL(l_lock_profile,'Y')) IN ('N','NO')) THEN
3774 RAISE NO_LOCKING_REQUIRED;
3775 END IF;
3776
3777 ----check if the input parameter p_model_id
3778 -----has a value
3779 IF (p_model_id IS NULL) THEN
3780 RAISE MODELID_IS_NULL;
3781 ELSE
3782 BEGIN
3783 SELECT devl_project_id, seeded_flag
3784 INTO l_model_id, l_seeded_flag
3785 FROM cz_rp_entries a, cz_devl_projects b
3786 WHERE b.devl_project_id = p_model_id
3787 AND b.devl_project_id = a.object_id
3788 AND a.deleted_flag = '0'
3789 AND b.deleted_flag = '0'
3790 AND a.object_type='PRJ';
3791 EXCEPTION
3792 WHEN NO_DATA_FOUND THEN
3793 RAISE INVALID_MODEL_ID;
3794 END;
3795 END IF; /* IF (p_model_id IS NULL) */
3796
3797 IF (l_seeded_flag = '1') THEN
3798 RAISE NO_LOCKING_REQUIRED;
3799 END IF;
3800
3801 -----initialize l_commit_flag
3802 IF (p_commit_flag IS NULL) THEN
3803 l_commit_flag := DO_NOT_COMMIT;
3804 ELSIF (p_commit_flag = FND_API.G_TRUE) THEN
3805 l_commit_flag := DO_COMMIT;
3806 ELSIF (p_commit_flag = FND_API.G_FALSE) THEN
3807 l_commit_flag := DO_NOT_COMMIT;
3808 END IF; /* IF (p_commit_flag IS NULL) */
3809
3810 IF (p_lock_child_models IS NULL) THEN
3811 l_lock_child_models := DO_NOT_LOCK_CHILD_MODELS;
3812 ELSIF (p_lock_child_models = FND_API.G_TRUE) THEN
3813 l_lock_child_models := LOCK_CHILD_MODELS;
3814 ELSIF (p_lock_child_models = FND_API.G_FALSE) THEN
3815 l_lock_child_models := DO_NOT_LOCK_CHILD_MODELS;
3816 END IF; /* IF (p_lock_child_models IS NULL) */
3817
3818 IF (l_lock_child_models = LOCK_CHILD_MODELS) THEN
3819 l_model_tbl.DELETE;
3820 get_models_to_lock(p_model_id,0,x_locked_entities);
3821 BEGIN
3822 SELECT distinct component_id
3823 BULK
3824 COLLECT
3825 INTO l_model_tbl
3826 FROM cz_model_ref_expls
3827 WHERE model_id = p_model_id
3828 AND deleted_flag = '0'
3829 AND ps_node_type IN (263,264);
3830 EXCEPTION
3834 l_count := l_model_tbl.COUNT + 1;
3831 WHEN NO_DATA_FOUND THEN
3832 NULL;
3833 END;
3835 l_model_tbl(l_count) := p_model_id;
3836 IF (l_model_tbl.COUNT > 0) THEN
3837 FOR I IN l_model_tbl.FIRST..l_model_tbl.LAST
3838 LOOP
3839 l_count := 0;
3840 UPDATE cz_devl_projects
3841 SET cz_devl_projects.checkout_user = FND_GLOBAL.user_name,
3842 cz_devl_projects.checkout_time = sysdate
3843 WHERE cz_devl_projects.devl_project_id = l_model_tbl(i)
3844 AND (cz_devl_projects.checkout_user IS NULL)
3845 AND cz_devl_projects.deleted_flag = '0';
3846
3847 l_count := SQL%ROWCOUNT;
3848 IF (l_count = 0)
3849 THEN
3850 get_checkout_user(l_model_tbl(i),'PRJ',l_checkout_user,l_model_name);
3851 IF(l_checkout_user<>FND_GLOBAL.user_name AND l_checkout_user is not null)
3852 THEN
3853 RAISE MODEL_IS_LOCKED;
3854 END IF;
3855 END IF;
3856 END LOOP;
3857 END IF;
3858 ELSE
3859 l_count := 0;
3860 get_models_to_lock(p_model_id,1,x_locked_entities);
3861 UPDATE cz_devl_projects
3862 SET cz_devl_projects.checkout_user = FND_GLOBAL.user_name,
3863 cz_devl_projects.checkout_time = SYSDATE
3864 WHERE cz_devl_projects.devl_project_id = p_model_id
3865 AND (cz_devl_projects.checkout_user IS NULL);
3866 l_count := SQL%ROWCOUNT;
3867 IF (l_count = 0)
3868 THEN
3869 get_checkout_user(p_model_id,'PRJ',l_checkout_user,l_model_name);
3870 IF(l_checkout_user<>FND_GLOBAL.user_name AND l_checkout_user is not null)
3871 THEN
3872 RAISE LOCK_SINGLE_MODEL_ERR;
3873 END IF;
3874 END IF;
3875 END IF; /* IF (l_lock_child_models = LOCK_CHILD_MODELS) */
3876 IF (l_commit_flag = DO_COMMIT) THEN
3877 COMMIT;
3878 END IF; /* IF (l_commit_flag = 1) */
3879 EXCEPTION
3880 WHEN INVALID_MODEL_ID THEN
3881 x_return_status := FND_API.G_RET_STS_ERROR;
3882 x_msg_count := 1;
3883 FND_MESSAGE.SET_NAME( 'CZ','CZ_INVALID_MODEL_ID');
3884 FND_MSG_PUB.ADD;
3885 fnd_msg_pub.count_and_get(p_count => x_msg_count,p_data => x_msg_data);
3886 WHEN LOCK_SINGLE_MODEL_ERR THEN
3887 x_return_status := FND_API.G_RET_STS_ERROR;
3888 x_msg_count := 1;
3889 FND_MESSAGE.SET_NAME( 'CZ','CZ_LOCK_MODEL_ERR');
3890 FND_MESSAGE.SET_TOKEN('MODELNAME', l_model_name);
3891 FND_MESSAGE.SET_TOKEN('USERNAME', l_checkout_user);
3892 FND_MSG_PUB.ADD;
3893 fnd_msg_pub.count_and_get(p_count => x_msg_count,p_data => x_msg_data);
3894 WHEN NO_LOCKING_REQUIRED THEN
3895 NULL;
3896 WHEN MODELID_IS_NULL THEN
3897 NULL;
3898 WHEN MODEL_IS_LOCKED THEN
3899 x_return_status := FND_API.G_RET_STS_ERROR;
3900 add_to_error_stack(p_model_id,l_model_name_tbl,l_checkout_user_tbl);
3901 IF (l_model_name_tbl.COUNT > 0) THEN
3902 FOR I IN l_model_name_tbl.FIRST..l_model_name_tbl.LAST
3903 LOOP
3904 x_msg_count := x_msg_count + 1;
3905 FND_MESSAGE.SET_NAME('CZ', 'CZ_LOCK_MODEL_ERR');
3906 FND_MESSAGE.SET_TOKEN('MODELNAME', l_model_name_tbl(i));
3907 FND_MESSAGE.SET_TOKEN('USERNAME',l_checkout_user_tbl(i));
3908 FND_MSG_PUB.ADD;
3909 END LOOP;
3910 END IF;
3911 fnd_msg_pub.count_and_get(p_count => x_msg_count,p_data => x_msg_data);
3912 WHEN OTHERS THEN
3913 x_return_status := FND_API.G_RET_STS_ERROR;
3914 x_msg_count := 1;
3915 get_checkout_user(p_model_id, 'PRJ', l_checkout_user, l_model_name);
3916 FND_MESSAGE.SET_NAME('CZ','CZ_LOCK_FATAL_ERR');
3917 FND_MESSAGE.SET_TOKEN('OBJECTNAME', l_model_name);
3918 FND_MESSAGE.SET_TOKEN('SQLERRM', SQLERRM);
3919 fnd_msg_pub.count_and_get(p_count => x_msg_count,p_data => x_msg_data);
3920 END lock_model;
3921
3922 ---------->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
3923 /*#
3924 * This is the public interface for unlock operations on a model in Oracle Configurator
3925 * @param p_model_id number. devl_project_id of the model from cz_devl_projects table
3926 * @param p_unlock_child_models A value of FND_API.G_TRUE indicates that the child models if any should be
3927 * unlocked. A value of FND_API.G_FALSE indicates that only the root model
3928 * will be unlocked
3929 * @param p_models_to_unlock would contain an array of model id(s) that have been populated with
3930 * locked models during the execution of the lock model API. The unlock_model API will unlock only the models
3931 * in this array .
3932 * @param p_init_msg_list FND_API.G_TRUE if the API should initialize the FND stack, FND_API.G_FALSE if not.
3933 * @param x_return_status standard FND status. (ex:FND_API.G_RET_STS_SUCCESS )
3934 * @param x_msg_count number of messages on the stack.
3935 * @param x_msg_data standard FND OUT parameter for message. Messages are written to the FND error stack
3936 * @rep:scope public
3937 * @rep:product CZ
3938 * @rep:displayname API for working with unlock operations on a model
3939 * @rep:lifecycle active
3940 * @rep:compatibility S
3941 * @rep:category BUSINESS_ENTITY CZ_SECURITY
3942 *
3946 * p_models_to_unlock : cz_security_pvt.unlock_model(
3943 * Usage
3944 *
3945 * unlock models in the array
3947 * p_commit_flag => FND_API.G_TRUE,
3948 * p_models_to_unlock => l_locked_entities,
3949 * p_init_msg_list => FND_API.G_TRUE,
3950 * x_return_status => x_return_status,
3951 * x_msg_count => x_msg_count,
3952 * x_msg_data => x_msg_data);
3953 *
3954 */
3955
3956 PROCEDURE unlock_model(p_api_version IN NUMBER,
3957 p_commit_flag IN VARCHAR2,
3958 p_models_to_unlock IN number_type_tbl,
3959 p_init_msg_list IN VARCHAR2,
3960 x_return_status OUT NOCOPY VARCHAR2,
3961 x_msg_count OUT NOCOPY NUMBER,
3962 x_msg_data OUT NOCOPY VARCHAR2)
3963 IS
3964 l_return_status VARCHAR2(1);
3965 l_msg_count NUMBER;
3966 l_msg_data VARCHAR2(2000);
3967
3968 BEGIN
3969 ----initialize FND stack
3970 x_return_status := FND_API.G_RET_STS_SUCCESS;
3971 IF (p_init_msg_list = FND_API.G_TRUE) THEN
3972 FND_MSG_PUB.initialize;
3973 END IF;
3974
3975 IF (p_models_to_unlock.COUNT > 0) THEN
3976 FOR I IN p_models_to_unlock.FIRST..p_models_to_unlock.LAST
3977 LOOP
3978 unlock_model(1.0,p_models_to_unlock(i),FND_API.G_FALSE,FND_API.G_FALSE,
3979 l_return_status,l_msg_count,l_msg_data);
3980 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3981 x_return_status := FND_API.G_RET_STS_ERROR;
3982 END IF;
3983 END LOOP;
3984 END IF;
3985 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3986 fnd_msg_pub.count_and_get(p_count => x_msg_count,p_data => x_msg_data);
3987 END IF;
3988 IF (p_commit_flag = FND_API.G_TRUE) THEN
3989 COMMIT;
3990 END IF;
3991 END unlock_model;
3992
3993 ---------->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
3994 PROCEDURE unlock_model(p_api_version IN NUMBER,
3995 p_model_id IN NUMBER,
3996 p_commit_flag IN VARCHAR2,
3997 p_init_msg_list IN VARCHAR2,
3998 x_return_status OUT NOCOPY VARCHAR2,
3999 x_msg_count OUT NOCOPY NUMBER,
4000 x_msg_data OUT NOCOPY VARCHAR2)
4001 IS
4002
4003 MODEL_IS_NOT_UNLOCKED EXCEPTION;
4004 MODELID_IS_NULL EXCEPTION;
4005 MODEL_UNLOCK_ERR EXCEPTION;
4006 NO_LOCKING_REQUIRED EXCEPTION;
4007 l_model_tbl cz_security_pvt.number_type_tbl;
4008 l_count NUMBER := 0;
4009 l_checkout_user cz_devl_projects.checkout_user%TYPE;
4010 l_model_name_tbl cz_security_pvt.model_name_tbl;
4011 l_checkout_user_tbl cz_security_pvt.checkout_user_tbl;
4012 l_model_name cz_devl_projects.name%TYPE;
4013 l_commit_flag VARCHAR2(1);
4014 l_lock_profile VARCHAR2(3);
4015
4016 BEGIN
4017 ----initialize FND stack
4018 x_return_status := FND_API.G_RET_STS_SUCCESS;
4019 x_msg_data := NULL;
4020 x_msg_count := 0;
4021
4022 ----initialize the message stack depending on the input parameter
4023 IF(p_init_msg_list = FND_API.G_TRUE)THEN fnd_msg_pub.initialize; END IF;
4024
4025 -----check if the input parameter p_model_id
4026 -----has a value
4027 IF (p_model_id IS NULL) THEN
4028 RAISE MODELID_IS_NULL;
4029 END IF; /* IF (p_model_id IS NULL) */
4030
4031 ----check if locking is enabled
4032 ----if the site level profile for locking is not enabled then
4033 ----there is no need to do locking
4034 l_lock_profile := get_locking_profile_value;
4035 IF (UPPER(NVL(l_lock_profile,'Y')) IN ('N','NO')) THEN
4036 RAISE NO_LOCKING_REQUIRED;
4037 END IF; /* IF (UPPER(NVL(l_lock_profile,'Y')) IN ('N','NO')) */
4038
4039 -----initialize l_commit_flag
4040 IF (p_commit_flag IS NULL) THEN
4041 l_commit_flag := DO_NOT_COMMIT;
4042 ELSIF (p_commit_flag = FND_API.G_TRUE) THEN
4043 l_commit_flag := DO_COMMIT;
4044 ELSIF (p_commit_flag = FND_API.G_FALSE) THEN
4045 l_commit_flag := DO_NOT_COMMIT;
4046 END IF; /* IF (p_commit_flag IS NULL) */
4047
4048
4049 UPDATE cz_devl_projects
4050 SET cz_devl_projects.checkout_user = NULL,
4051 cz_devl_projects.checkout_time = NULL
4052 WHERE cz_devl_projects.devl_project_id = p_model_id
4053 AND (cz_devl_projects.checkout_user IS NULL
4054 OR cz_devl_projects.checkout_user = FND_GLOBAL.user_name);
4055
4056 IF (SQL%ROWCOUNT = 0) THEN
4057 get_checkout_user(p_model_id,'PRJ',l_checkout_user,l_model_name);
4058 RAISE MODEL_UNLOCK_ERR;
4059 END IF;
4060 IF (l_commit_flag = DO_COMMIT) THEN COMMIT; END IF;
4061 EXCEPTION
4062 WHEN NO_LOCKING_REQUIRED THEN
4063 NULL;
4064 WHEN MODELID_IS_NULL THEN
4065 NULL;
4066 WHEN MODEL_UNLOCK_ERR THEN
4067 x_return_status := FND_API.G_RET_STS_ERROR;
4068 add_to_error_stack(p_model_id,l_model_name_tbl,l_checkout_user_tbl);
4069 IF (l_model_name_tbl.COUNT > 0) THEN
4070 FOR I IN l_model_name_tbl.FIRST..l_model_name_tbl.LAST
4074 FND_MESSAGE.SET_TOKEN('MODELNAME', l_model_name_tbl(i));
4071 LOOP
4072 x_msg_count := x_msg_count + 1;
4073 FND_MESSAGE.SET_NAME( 'CZ','CZ_MODEL_UNLOCK_ERR');
4075 FND_MESSAGE.SET_TOKEN('USERNAME', l_checkout_user_tbl(i));
4076 FND_MSG_PUB.ADD;
4077 END LOOP;
4078 END IF;
4079 fnd_msg_pub.count_and_get(p_count => x_msg_count,p_data => x_msg_data);
4080 WHEN OTHERS THEN
4081 x_return_status := FND_API.G_RET_STS_ERROR;
4082 get_checkout_user(p_model_id, 'PRJ', l_checkout_user, l_model_name);
4083 x_msg_data := CZ_UTILS.GET_TEXT('CZ_UNLOCK_FATAL_ERR', 'OBJECTNAME', l_model_name, 'SQLERRM', SQLERRM);
4084 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
4085 END unlock_model;
4086 ---------->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
4087 ------wrappers
4088
4089 PROCEDURE lock_template(p_api_version IN NUMBER,
4090 p_template_id IN NUMBER,
4091 p_init_msg_list IN VARCHAR2,
4092 x_return_status OUT NOCOPY VARCHAR2,
4093 x_msg_count OUT NOCOPY NUMBER,
4094 x_msg_data OUT NOCOPY VARCHAR2)
4095 IS
4096
4097 l_init_msg_list VARCHAR2(1) := FND_API.G_FALSE;
4098
4099 BEGIN
4100
4101 IF(p_init_msg_list = DO_INIT_MSG_LIST)THEN l_init_msg_list := FND_API.G_TRUE; END IF;
4102
4103 lock_template(p_api_version, p_template_id, FND_API.G_FALSE, l_init_msg_list, x_return_status,
4104 x_msg_count, x_msg_data);
4105 END;
4106
4107 ----------------
4108 PROCEDURE unlock_template(p_api_version IN NUMBER,
4109 p_template_id IN NUMBER,
4110 p_force_unlock IN VARCHAR2,
4111 p_init_msg_list IN VARCHAR2,
4112 x_return_status OUT NOCOPY VARCHAR2,
4113 x_msg_count OUT NOCOPY NUMBER,
4114 x_msg_data OUT NOCOPY VARCHAR2)
4115 IS
4116
4117 l_init_msg_list VARCHAR2(1) := FND_API.G_FALSE;
4118
4119 BEGIN
4120
4121 IF(p_init_msg_list = DO_INIT_MSG_LIST)THEN l_init_msg_list := FND_API.G_TRUE; END IF;
4122
4123 IF (p_force_unlock = '0') THEN
4124 unlock_template(p_api_version, p_template_id, l_init_msg_list, x_return_status, x_msg_count, x_msg_data);
4125 ELSE
4126 force_unlock_template (p_api_version, p_template_id, l_init_msg_list, x_return_status, x_msg_count,
4127 x_msg_data);
4128 END IF;
4129 END;
4130
4131 --------->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
4132 ----For developer
4133 PROCEDURE lock_model(p_api_version IN NUMBER,
4134 p_model_id IN NUMBER,
4135 p_lock_child_models IN VARCHAR2,
4136 p_commit_flag IN VARCHAR2,
4137 p_init_msg_list IN VARCHAR2,
4138 x_return_status OUT NOCOPY VARCHAR2,
4139 x_msg_count OUT NOCOPY NUMBER,
4140 x_msg_data OUT NOCOPY VARCHAR2)
4141 IS
4142
4143 l_models_locked_tbl number_type_tbl;
4144 l_lock_child_models VARCHAR2(1) := FND_API.G_FALSE;
4145 l_commit_flag VARCHAR2(1) := FND_API.G_FALSE;
4146 l_init_msg_list VARCHAR2(1) := FND_API.G_FALSE;
4147
4148 BEGIN
4149
4150 IF(p_lock_child_models = LOCK_CHILD_MODELS)THEN l_lock_child_models := FND_API.G_TRUE; END IF;
4151 IF(p_commit_flag = DO_COMMIT)THEN l_commit_flag := FND_API.G_TRUE; END IF;
4152 IF(p_init_msg_list = DO_INIT_MSG_LIST)THEN l_init_msg_list := FND_API.G_TRUE; END IF;
4153
4154 lock_model(p_api_version, p_model_id, l_lock_child_models, l_commit_flag, l_init_msg_list,
4155 l_models_locked_tbl, x_return_status, x_msg_count, x_msg_data);
4156 END;
4157
4158 ---------->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
4159 ----Wrappers to be used by Import, Publishing, Logic Gen and UI Gen. Hide the p_init_msg_list parameter.
4160 ----The message list is not initialized.
4161 PROCEDURE lock_model(p_api_version IN NUMBER,
4162 p_model_id IN NUMBER,
4163 p_lock_child_models IN VARCHAR2,
4164 p_commit_flag IN VARCHAR2,
4165 x_locked_entities OUT NOCOPY number_type_tbl,
4166 x_return_status OUT NOCOPY VARCHAR2,
4167 x_msg_count OUT NOCOPY NUMBER,
4168 x_msg_data OUT NOCOPY VARCHAR2)
4169 IS
4170 BEGIN
4171
4172 lock_model(p_api_version, p_model_id, p_lock_child_models, FND_API.G_TRUE, FND_API.G_FALSE,
4173 x_locked_entities, x_return_status, x_msg_count, x_msg_data);
4174 END;
4175
4176 PROCEDURE unlock_model(p_api_version IN NUMBER,
4177 p_commit_flag IN VARCHAR2,
4178 p_models_to_unlock IN number_type_tbl,
4179 x_return_status OUT NOCOPY VARCHAR2,
4180 x_msg_count OUT NOCOPY NUMBER,
4181 x_msg_data OUT NOCOPY VARCHAR2)
4182 IS
4183 BEGIN
4184
4185 unlock_model(p_api_version,p_commit_flag,p_models_to_unlock,
4186 FND_API.G_FALSE, x_return_status,x_msg_count,x_msg_data);
4187 END;
4188
4192 p_model_id IN NUMBER,
4189 ---------->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
4190 ----unlock model for rule import
4191 PROCEDURE unlock_model (p_api_version IN NUMBER,
4193 x_return_status OUT NOCOPY VARCHAR2,
4194 x_msg_count OUT NOCOPY NUMBER,
4195 x_msg_data OUT NOCOPY VARCHAR2)
4196 IS
4197
4198 BEGIN
4199 ----initialize FND stack
4200 FND_MSG_PUB.initialize;
4201 x_return_status := FND_API.G_RET_STS_SUCCESS;
4202 x_msg_data := NULL;
4203 x_msg_count := 0;
4204
4205 cz_security_pvt.unlock_model(1.0,p_model_id,FND_API.G_FALSE,FND_API.G_FALSE,
4206 x_return_status,x_msg_count,x_msg_data);
4207
4208 END unlock_model;
4209
4210 ----------->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
4211 PROCEDURE unlock_model(p_api_version IN NUMBER,
4212 p_models_to_unlock IN SYSTEM.CZ_NUMBER_TBL_TYPE,
4213 p_commit_flag IN VARCHAR2,
4214 p_init_msg_list IN VARCHAR2,
4215 x_return_status OUT NOCOPY VARCHAR2,
4216 x_msg_count OUT NOCOPY NUMBER,
4217 x_msg_data OUT NOCOPY VARCHAR2)
4218
4219 IS
4220 l_api_name CONSTANT VARCHAR2(30) := 'unlock_model';
4221 l_msg_count NUMBER;
4222 l_msg_data VARCHAR2(2000);
4223 l_return_status VARCHAR2(1);
4224 BEGIN
4225 x_return_status := FND_API.G_RET_STS_SUCCESS;
4226 IF (p_init_msg_list = FND_API.G_TRUE) THEN
4227 FND_MSG_PUB.initialize;
4228 x_msg_data := NULL;
4229 x_msg_count := 0;
4230 END IF;
4231
4232 IF (p_models_to_unlock.COUNT > 0) THEN
4233 FOR I IN p_models_to_unlock.FIRST..p_models_to_unlock.LAST
4234 LOOP
4235 cz_security_pvt.unlock_model(1.0,
4236 p_models_to_unlock(i),
4237 FND_API.G_FALSE,
4238 FND_API.G_FALSE,
4239 l_return_status,
4240 l_msg_count,
4241 l_msg_data);
4242 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
4243 x_return_status := FND_API.G_RET_STS_ERROR;
4244 END IF;
4245 END LOOP;
4246 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
4247 fnd_msg_pub.count_and_get(FND_API.G_FALSE, x_msg_count, x_msg_data);
4248 END IF;
4249 END IF;
4250 IF (p_commit_flag = FND_API.G_TRUE) THEN COMMIT; END IF;
4251 EXCEPTION
4252 WHEN OTHERS THEN
4253 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4254 fnd_msg_pub.add_exc_msg(G_PKG_NAME, l_api_name);
4255 fnd_msg_pub.count_and_get(FND_API.G_FALSE, x_msg_count, x_msg_data);
4256 END;
4257
4258 ---------->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
4259 END cz_security_pvt;
[Home] [Help]