[Home] [Help]
176: p_init_msg_list IN VARCHAR2 DEFAULT 'Y') is
177:
178: -- secure_role_flag varchar2(1);
179: secure_resp_flag varchar2(1);
180: v_fnd_api_ret_code varchar2(1);
181: i BINARY_INTEGER;
182: l_exist_flag varchar2(1):='F';
183:
184: Begin
191:
192: pa_debug.Init_err_stack ( 'Check User Privilege');
193: x_msg_count :=0;
194: x_msg_data:= null;
195: x_return_status:=fnd_api.g_ret_sts_success;
196: x_ret_code:=fnd_api.g_true;
197:
198: ------- Check for License
199: IF pa_product_install_utils.check_function_licensed(p_privilege) <> 'Y' THEN
192: pa_debug.Init_err_stack ( 'Check User Privilege');
193: x_msg_count :=0;
194: x_msg_data:= null;
195: x_return_status:=fnd_api.g_ret_sts_success;
196: x_ret_code:=fnd_api.g_true;
197:
198: ------- Check for License
199: IF pa_product_install_utils.check_function_licensed(p_privilege) <> 'Y' THEN
200: x_ret_code:= fnd_api.g_false;
196: x_ret_code:=fnd_api.g_true;
197:
198: ------- Check for License
199: IF pa_product_install_utils.check_function_licensed(p_privilege) <> 'Y' THEN
200: x_ret_code:= fnd_api.g_false;
201: x_return_status:=fnd_api.g_ret_sts_success;
202: RETURN;
203: END IF;
204: ------- End check for License
197:
198: ------- Check for License
199: IF pa_product_install_utils.check_function_licensed(p_privilege) <> 'Y' THEN
200: x_ret_code:= fnd_api.g_false;
201: x_return_status:=fnd_api.g_ret_sts_success;
202: RETURN;
203: END IF;
204: ------- End check for License
205:
210: IF G_debug_flag = 'Y' THEN
211: pa_debug.write_file('check_user_privilege: ' || 'LOG', pa_debug.G_err_stage);
212: END IF;
213: if fnd_function.test(p_privilege) then
214: x_ret_code:=fnd_api.g_true;
215: x_return_status:=fnd_api.g_ret_sts_success;
216: else
217: x_ret_code:=fnd_api.g_false;
218: x_return_status:=fnd_api.g_ret_sts_success;
211: pa_debug.write_file('check_user_privilege: ' || 'LOG', pa_debug.G_err_stage);
212: END IF;
213: if fnd_function.test(p_privilege) then
214: x_ret_code:=fnd_api.g_true;
215: x_return_status:=fnd_api.g_ret_sts_success;
216: else
217: x_ret_code:=fnd_api.g_false;
218: x_return_status:=fnd_api.g_ret_sts_success;
219: end if;
213: if fnd_function.test(p_privilege) then
214: x_ret_code:=fnd_api.g_true;
215: x_return_status:=fnd_api.g_ret_sts_success;
216: else
217: x_ret_code:=fnd_api.g_false;
218: x_return_status:=fnd_api.g_ret_sts_success;
219: end if;
220: return;
221: end if;
214: x_ret_code:=fnd_api.g_true;
215: x_return_status:=fnd_api.g_ret_sts_success;
216: else
217: x_ret_code:=fnd_api.g_false;
218: x_return_status:=fnd_api.g_ret_sts_success;
219: end if;
220: return;
221: end if;
222:
240: --dbms_output.put_line('p_object_name:'||p_object_name);
241: --dbms_output.put_line('p_object_key:'||p_object_key);
242:
243:
244: v_fnd_api_ret_code:=fnd_data_security.check_function(
245: p_api_version =>l_api_version,
246: p_function =>p_privilege,
247: p_object_name => p_object_name,
248: p_instance_pk1_value=>p_object_key,
252: p_instance_pk5_value => NULL );
253: -- p_user_name => get_grantee_key ); Commented for Bug 4498436.
254:
255: --dbms_output.put_line('after calling fnd_data_sec');
256: --dbms_output.put_line('v_fnd_api_ret_code:'||v_fnd_api_ret_code);
257: if v_fnd_api_ret_code=fnd_api.g_ret_sts_error
258: OR v_fnd_api_ret_code=fnd_api.g_ret_sts_unexp_error then
259: x_ret_code:=fnd_api.g_false;
260: x_return_status:=fnd_api.g_ret_sts_unexp_error;
253: -- p_user_name => get_grantee_key ); Commented for Bug 4498436.
254:
255: --dbms_output.put_line('after calling fnd_data_sec');
256: --dbms_output.put_line('v_fnd_api_ret_code:'||v_fnd_api_ret_code);
257: if v_fnd_api_ret_code=fnd_api.g_ret_sts_error
258: OR v_fnd_api_ret_code=fnd_api.g_ret_sts_unexp_error then
259: x_ret_code:=fnd_api.g_false;
260: x_return_status:=fnd_api.g_ret_sts_unexp_error;
261: FND_MSG_PUB.Count_And_Get
254:
255: --dbms_output.put_line('after calling fnd_data_sec');
256: --dbms_output.put_line('v_fnd_api_ret_code:'||v_fnd_api_ret_code);
257: if v_fnd_api_ret_code=fnd_api.g_ret_sts_error
258: OR v_fnd_api_ret_code=fnd_api.g_ret_sts_unexp_error then
259: x_ret_code:=fnd_api.g_false;
260: x_return_status:=fnd_api.g_ret_sts_unexp_error;
261: FND_MSG_PUB.Count_And_Get
262: (p_count => x_msg_count ,
255: --dbms_output.put_line('after calling fnd_data_sec');
256: --dbms_output.put_line('v_fnd_api_ret_code:'||v_fnd_api_ret_code);
257: if v_fnd_api_ret_code=fnd_api.g_ret_sts_error
258: OR v_fnd_api_ret_code=fnd_api.g_ret_sts_unexp_error then
259: x_ret_code:=fnd_api.g_false;
260: x_return_status:=fnd_api.g_ret_sts_unexp_error;
261: FND_MSG_PUB.Count_And_Get
262: (p_count => x_msg_count ,
263: p_data => x_msg_data
256: --dbms_output.put_line('v_fnd_api_ret_code:'||v_fnd_api_ret_code);
257: if v_fnd_api_ret_code=fnd_api.g_ret_sts_error
258: OR v_fnd_api_ret_code=fnd_api.g_ret_sts_unexp_error then
259: x_ret_code:=fnd_api.g_false;
260: x_return_status:=fnd_api.g_ret_sts_unexp_error;
261: FND_MSG_PUB.Count_And_Get
262: (p_count => x_msg_count ,
263: p_data => x_msg_data
264: );
264: );
265: return;
266: end if;
267:
268: if v_fnd_api_ret_code=fnd_api.g_true then
269: x_ret_code:= fnd_api.g_true;
270: x_return_status:=fnd_api.g_ret_sts_success;
271: return;
272: end if;
265: return;
266: end if;
267:
268: if v_fnd_api_ret_code=fnd_api.g_true then
269: x_ret_code:= fnd_api.g_true;
270: x_return_status:=fnd_api.g_ret_sts_success;
271: return;
272: end if;
273:
266: end if;
267:
268: if v_fnd_api_ret_code=fnd_api.g_true then
269: x_ret_code:= fnd_api.g_true;
270: x_return_status:=fnd_api.g_ret_sts_success;
271: return;
272: end if;
273:
274: if v_fnd_api_ret_code=fnd_api.g_false then
270: x_return_status:=fnd_api.g_ret_sts_success;
271: return;
272: end if;
273:
274: if v_fnd_api_ret_code=fnd_api.g_false then
275: x_ret_code:= fnd_api.g_false;
276: x_return_status:=fnd_api.g_ret_sts_success;
277: ----not return from here. need to continue to do
278: ----responsibility level check
271: return;
272: end if;
273:
274: if v_fnd_api_ret_code=fnd_api.g_false then
275: x_ret_code:= fnd_api.g_false;
276: x_return_status:=fnd_api.g_ret_sts_success;
277: ----not return from here. need to continue to do
278: ----responsibility level check
279: end if;
272: end if;
273:
274: if v_fnd_api_ret_code=fnd_api.g_false then
275: x_ret_code:= fnd_api.g_false;
276: x_return_status:=fnd_api.g_ret_sts_success;
277: ----not return from here. need to continue to do
278: ----responsibility level check
279: end if;
280: -----------End of check role based security-----------
301: G_source_type ,
302: p_object_key );
303:
304:
305: if secure_resp_flag =FND_API.G_RET_STS_UNEXP_ERROR then
306: x_ret_code:=fnd_api.g_false;
307: x_return_status:=fnd_api.g_ret_sts_unexp_error;
308:
309: FND_MSG_PUB.Count_And_Get
302: p_object_key );
303:
304:
305: if secure_resp_flag =FND_API.G_RET_STS_UNEXP_ERROR then
306: x_ret_code:=fnd_api.g_false;
307: x_return_status:=fnd_api.g_ret_sts_unexp_error;
308:
309: FND_MSG_PUB.Count_And_Get
310: (p_count => x_msg_count ,
303:
304:
305: if secure_resp_flag =FND_API.G_RET_STS_UNEXP_ERROR then
306: x_ret_code:=fnd_api.g_false;
307: x_return_status:=fnd_api.g_ret_sts_unexp_error;
308:
309: FND_MSG_PUB.Count_And_Get
310: (p_count => x_msg_count ,
311: p_data => x_msg_data
316: -----Check responsibility level function security in these cases:
317: -----1. The user plays unsecured roles (roles without menu) on the object
318: -----2. The user doesn't play any roles (instance assignment) on the object
319:
320: if secure_resp_flag=fnd_api.g_true then
321: pa_debug.G_err_stage := 'check responsibility level security: in object context';
322: IF G_debug_flag = 'Y' THEN
323: pa_debug.write_file('check_user_privilege: ' || 'LOG', pa_debug.G_err_stage);
324: END IF;
323: pa_debug.write_file('check_user_privilege: ' || 'LOG', pa_debug.G_err_stage);
324: END IF;
325: if p_object_name='PA_PROJECTS' then
326: if pa_security.allow_update (p_object_key)<>'Y' then
327: x_ret_code:=fnd_api.g_false;
328: x_return_status:=fnd_api.g_ret_sts_success;
329: return;
330: end if;
331: end if;
324: END IF;
325: if p_object_name='PA_PROJECTS' then
326: if pa_security.allow_update (p_object_key)<>'Y' then
327: x_ret_code:=fnd_api.g_false;
328: x_return_status:=fnd_api.g_ret_sts_success;
329: return;
330: end if;
331: end if;
332:
330: end if;
331: end if;
332:
333: if fnd_function.test(p_privilege) then
334: x_ret_code:=fnd_api.g_true;
335: x_return_status:=fnd_api.g_ret_sts_success;
336: return;
337: end if;
338:
331: end if;
332:
333: if fnd_function.test(p_privilege) then
334: x_ret_code:=fnd_api.g_true;
335: x_return_status:=fnd_api.g_ret_sts_success;
336: return;
337: end if;
338:
339: x_ret_code:=fnd_api.g_false;
335: x_return_status:=fnd_api.g_ret_sts_success;
336: return;
337: end if;
338:
339: x_ret_code:=fnd_api.g_false;
340: x_return_status:=fnd_api.g_ret_sts_success;
341:
342: ---The following code is for testing the error page
343: /* fnd_message.set_name('PA','PA_SEC_NO_ACCESS');
336: return;
337: end if;
338:
339: x_ret_code:=fnd_api.g_false;
340: x_return_status:=fnd_api.g_ret_sts_success;
341:
342: ---The following code is for testing the error page
343: /* fnd_message.set_name('PA','PA_SEC_NO_ACCESS');
344: fnd_msg_pub.ADD;
357: END IF;
358: fnd_msg_pub.add_exc_msg
359: (p_pkg_name => G_PKG_NAME,
360: p_procedure_name =>'CHECK_USER_PRIVILEGE' );
361: x_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
362: x_ret_code :=fnd_api.g_false;
363: FND_MSG_PUB.Count_And_Get
364: (p_count => x_msg_count ,
365: p_data => x_msg_data
358: fnd_msg_pub.add_exc_msg
359: (p_pkg_name => G_PKG_NAME,
360: p_procedure_name =>'CHECK_USER_PRIVILEGE' );
361: x_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
362: x_ret_code :=fnd_api.g_false;
363: FND_MSG_PUB.Count_And_Get
364: (p_count => x_msg_count ,
365: p_data => x_msg_data
366: );
406: open c_role_sec_enabled;
407: fetch c_role_sec_enabled into v_dummy;
408: if c_role_sec_enabled%found then
409: close c_role_sec_enabled; -- Bug #2994870: closing the cursor.
410: return fnd_api.g_true;
411: else
412: close c_role_sec_enabled; -- Bug #2994870: closing the cursor.
413: return fnd_api.g_false;
414: end if;
409: close c_role_sec_enabled; -- Bug #2994870: closing the cursor.
410: return fnd_api.g_true;
411: else
412: close c_role_sec_enabled; -- Bug #2994870: closing the cursor.
413: return fnd_api.g_false;
414: end if;
415: Exception
416: when others then
417: IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
418: fnd_msg_pub.add_exc_msg
419: (p_pkg_name => G_PKG_NAME,
420: p_procedure_name => 'check_sec_by_role');
421: end if;
422: return fnd_api.g_ret_sts_unexp_error;
423: end;*/
424:
425:
426: ---This API check if responsibility based security is enforced or not
496: FETCH c_unsecured_role INTO v_dummy;
497: IF c_unsecured_role%found THEN
498: --Check responsibility if an unsecured role is found
499: CLOSE c_unsecured_role;
500: RETURN fnd_api.g_true;
501: END IF;
502: CLOSE c_unsecured_role;
503:
504: OPEN c_any_role;
506: IF c_any_role%found THEN
507: /*nisinha bug#8541727 */
508: --Check responsibility if no roles are found
509: CLOSE c_any_role;
510: RETURN fnd_api.g_true;
511: END IF;
512: CLOSE c_any_role;
513:
514: IF p_object_name IN ('PA_PROJECTS')
513:
514: IF p_object_name IN ('PA_PROJECTS')
515: AND pa_security.g_cross_project_user='Y' THEN
516: --profile option override this check
517: RETURN fnd_api.g_true;
518: END IF;
519:
520: RETURN fnd_api.g_false;
521:
516: --profile option override this check
517: RETURN fnd_api.g_true;
518: END IF;
519:
520: RETURN fnd_api.g_false;
521:
522: Exception
523: when others then
524: IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
525: fnd_msg_pub.add_exc_msg
526: (p_pkg_name => G_PKG_NAME,
527: p_procedure_name => 'check_sec_by_resp');
528: end if;
529: return fnd_api.g_ret_sts_unexp_error;
530: end;
531:
532:
533: --function get_resource_person_id
570: v_error_message_code varchar2(30);
571: v_resource_org_id number;
572: v_resource_emp_id number;
573: v_project_org_id number;
574: v_fnd_api_ret_code varchar2(1);
575: v_resource_super_user varchar2(1) := 'N';
576: l_fnd_function_test boolean:=false; -- Added for Bug2970209
577: begin
578:
585:
586: pa_debug.Init_err_stack ( 'check_confirm_asmt');
587: x_msg_count :=0;
588: x_msg_data:= null;
589: x_return_status:=fnd_api.g_ret_sts_success;
590: x_ret_code:=fnd_api.g_true;
591:
592: --Return false if login user is not an employee
593: IF G_source_type<>'PERSON' THEN
586: pa_debug.Init_err_stack ( 'check_confirm_asmt');
587: x_msg_count :=0;
588: x_msg_data:= null;
589: x_return_status:=fnd_api.g_ret_sts_success;
590: x_ret_code:=fnd_api.g_true;
591:
592: --Return false if login user is not an employee
593: IF G_source_type<>'PERSON' THEN
594: x_ret_code:=FND_API.G_FALSE;
590: x_ret_code:=fnd_api.g_true;
591:
592: --Return false if login user is not an employee
593: IF G_source_type<>'PERSON' THEN
594: x_ret_code:=FND_API.G_FALSE;
595: RETURN;
596: END IF;
597:
598: -----Initialization: get resource id, resource org, project org
603: END IF;
604:
605: ------- Check for License
606: IF pa_product_install_utils.check_function_licensed(p_privilege) <> 'Y' THEN
607: x_ret_code:= fnd_api.g_false;
608: RETURN;
609: END IF;
610: ------- End check for License
611:
619: X_RESOURCE_ID=>v_resource_id ,
620: X_RESOURCE_TYPE_ID=>v_resource_type_id ,
621: X_RETURN_STATUS=>v_return_status ,
622: X_ERROR_MESSAGE_CODE=>v_error_message_code );
623: if v_return_status <>fnd_api.g_ret_sts_success then
624: x_msg_count := x_msg_count+1;
625: x_msg_data := v_error_message_code;
626: x_return_status := fnd_api.g_ret_sts_error;
627: x_ret_code:=fnd_api.g_false;
622: X_ERROR_MESSAGE_CODE=>v_error_message_code );
623: if v_return_status <>fnd_api.g_ret_sts_success then
624: x_msg_count := x_msg_count+1;
625: x_msg_data := v_error_message_code;
626: x_return_status := fnd_api.g_ret_sts_error;
627: x_ret_code:=fnd_api.g_false;
628: FND_MSG_PUB.add_exc_msg
629: (p_pkg_name =>'pa_resource_utils',
630: p_procedure_name => 'Check_ResourceName_Or_Id',
623: if v_return_status <>fnd_api.g_ret_sts_success then
624: x_msg_count := x_msg_count+1;
625: x_msg_data := v_error_message_code;
626: x_return_status := fnd_api.g_ret_sts_error;
627: x_ret_code:=fnd_api.g_false;
628: FND_MSG_PUB.add_exc_msg
629: (p_pkg_name =>'pa_resource_utils',
630: p_procedure_name => 'Check_ResourceName_Or_Id',
631: p_error_text => v_error_message_code);
638: p_start_date ,
639: v_resource_org_id ,
640: v_return_status ,
641: v_error_message_code ) ;
642: if v_return_status <>fnd_api.g_ret_sts_success then
643: x_msg_count := x_msg_count+1;
644: x_msg_data := v_error_message_code;
645: x_return_status := v_return_status;
646: x_ret_code:=fnd_api.g_false;
642: if v_return_status <>fnd_api.g_ret_sts_success then
643: x_msg_count := x_msg_count+1;
644: x_msg_data := v_error_message_code;
645: x_return_status := v_return_status;
646: x_ret_code:=fnd_api.g_false;
647: --Bug# 6134740 Fix start
648: /*FND_MSG_PUB.add_exc_msg
649: (p_pkg_name =>G_PKG_NAME,
650: p_procedure_name => 'GET_RESOURCE_ORG_ID',
668: x_return_status => x_return_status,
669: x_msg_count => x_msg_count,
670: x_msg_data => x_msg_data);
671:
672: IF x_return_status<>fnd_api.g_ret_sts_success THEN
673: RETURN;
674: END IF;
675:
676: if x_ret_code=fnd_api.g_true then
672: IF x_return_status<>fnd_api.g_ret_sts_success THEN
673: RETURN;
674: END IF;
675:
676: if x_ret_code=fnd_api.g_true then
677: return;
678: end if;
679: ---------End of check if the user has resource authority------
680: */
685: -- Bug 2991490 Added the following condition.
686: -- If the logged in user is the resource on which the action is being performed, then grant privilege to the user.
687:
688: IF (p_resource_id = pa_resource_utils.get_resource_id(G_source_id) and l_fnd_function_test = true) THEN
689: x_ret_code:=fnd_api.g_true;
690: x_return_status:=fnd_api.g_ret_sts_success;
691: return;
692: END IF;
693:
686: -- If the logged in user is the resource on which the action is being performed, then grant privilege to the user.
687:
688: IF (p_resource_id = pa_resource_utils.get_resource_id(G_source_id) and l_fnd_function_test = true) THEN
689: x_ret_code:=fnd_api.g_true;
690: x_return_status:=fnd_api.g_ret_sts_success;
691: return;
692: END IF;
693:
694:
710: pa_debug.write_file( 'LOG', pa_debug.G_err_stage);
711: END IF;
712:
713: if l_fnd_function_test then
714: x_ret_code:=fnd_api.g_true;
715: x_return_status:=fnd_api.g_ret_sts_success;
716: return;
717: else
718: x_ret_code:=fnd_api.g_false;
711: END IF;
712:
713: if l_fnd_function_test then
714: x_ret_code:=fnd_api.g_true;
715: x_return_status:=fnd_api.g_ret_sts_success;
716: return;
717: else
718: x_ret_code:=fnd_api.g_false;
719: x_return_status:=fnd_api.g_ret_sts_success;
714: x_ret_code:=fnd_api.g_true;
715: x_return_status:=fnd_api.g_ret_sts_success;
716: return;
717: else
718: x_ret_code:=fnd_api.g_false;
719: x_return_status:=fnd_api.g_ret_sts_success;
720: end if;
721:
722: END IF;
715: x_return_status:=fnd_api.g_ret_sts_success;
716: return;
717: else
718: x_ret_code:=fnd_api.g_false;
719: x_return_status:=fnd_api.g_ret_sts_success;
720: end if;
721:
722: END IF;
723:
730: END IF;
731:
732: -- Bug 2970209 The following condition is not needed and it is unambiguoys, because we are defaulting x_ret_code as true
733: -- so this check will not be fired if v_resource_super_user <> Y
734: -- if x_ret_code =fnd_api.g_false then
735: v_resource_emp_id := get_resource_person_id(v_resource_id);
736: check_manager_relation
737: (v_resource_emp_id,
738: -- G_user_emp_id,
741: v_ret_code,
742: v_return_status,
743: v_error_message_code);
744:
745: if v_return_status <>fnd_api.g_ret_sts_success then
746: x_msg_count := x_msg_count+1;
747: x_msg_data := v_error_message_code;
748: x_return_status := v_return_status;
749: x_ret_code:=fnd_api.g_false;
745: if v_return_status <>fnd_api.g_ret_sts_success then
746: x_msg_count := x_msg_count+1;
747: x_msg_data := v_error_message_code;
748: x_return_status := v_return_status;
749: x_ret_code:=fnd_api.g_false;
750: FND_MSG_PUB.add_exc_msg
751: (p_pkg_name =>G_PKG_NAME,
752: p_procedure_name => 'CHECK_MANAGER_RELATION',
753: p_error_text => v_error_message_code);
753: p_error_text => v_error_message_code);
754: RETURN;
755: end if;
756:
757: if (v_ret_code=fnd_api.g_true and l_fnd_function_test = true)then
758: x_ret_code:=v_ret_code;
759: x_return_status:=v_return_status;
760: return;
761: else
758: x_ret_code:=v_ret_code;
759: x_return_status:=v_return_status;
760: return;
761: else
762: x_ret_code:=fnd_api.g_false;
763: x_return_status:=v_return_status;
764: end if;
765:
766: -- end if;
775: --dbms_output.put_line('p_privilege IS : ' || p_privilege);
776: --dbms_output.put_line('v_resource_org_id IS : ' || v_resource_org_id);
777: --dbms_output.put_line('get_grantee_key IS : ' || get_grantee_key);
778:
779: v_fnd_api_ret_code:=fnd_data_security.check_function(
780: p_api_version =>l_api_version,
781: p_function => p_privilege,
782: p_object_name => 'ORGANIZATION',
783: p_instance_pk1_value=>v_resource_org_id,
786: p_instance_pk4_value => NULL,
787: p_instance_pk5_value => NULL );
788: -- p_user_name => get_grantee_key); Commented for Bug 4498436.
789: --dbms_output.put_line('fter fnd_data_security.check_function');
790: --dbms_output.put_line('v_fnd_api_ret_code IS : ' || v_fnd_api_ret_code);
791:
792: if v_fnd_api_ret_code=fnd_api.g_ret_sts_error
793: OR v_fnd_api_ret_code=fnd_api.g_ret_sts_unexp_error then
794: x_ret_code:=fnd_api.g_false;
788: -- p_user_name => get_grantee_key); Commented for Bug 4498436.
789: --dbms_output.put_line('fter fnd_data_security.check_function');
790: --dbms_output.put_line('v_fnd_api_ret_code IS : ' || v_fnd_api_ret_code);
791:
792: if v_fnd_api_ret_code=fnd_api.g_ret_sts_error
793: OR v_fnd_api_ret_code=fnd_api.g_ret_sts_unexp_error then
794: x_ret_code:=fnd_api.g_false;
795: x_return_status:=fnd_api.g_ret_sts_unexp_error;
796: FND_MSG_PUB.Count_And_Get
789: --dbms_output.put_line('fter fnd_data_security.check_function');
790: --dbms_output.put_line('v_fnd_api_ret_code IS : ' || v_fnd_api_ret_code);
791:
792: if v_fnd_api_ret_code=fnd_api.g_ret_sts_error
793: OR v_fnd_api_ret_code=fnd_api.g_ret_sts_unexp_error then
794: x_ret_code:=fnd_api.g_false;
795: x_return_status:=fnd_api.g_ret_sts_unexp_error;
796: FND_MSG_PUB.Count_And_Get
797: (p_count => x_msg_count ,
790: --dbms_output.put_line('v_fnd_api_ret_code IS : ' || v_fnd_api_ret_code);
791:
792: if v_fnd_api_ret_code=fnd_api.g_ret_sts_error
793: OR v_fnd_api_ret_code=fnd_api.g_ret_sts_unexp_error then
794: x_ret_code:=fnd_api.g_false;
795: x_return_status:=fnd_api.g_ret_sts_unexp_error;
796: FND_MSG_PUB.Count_And_Get
797: (p_count => x_msg_count ,
798: p_data => x_msg_data
791:
792: if v_fnd_api_ret_code=fnd_api.g_ret_sts_error
793: OR v_fnd_api_ret_code=fnd_api.g_ret_sts_unexp_error then
794: x_ret_code:=fnd_api.g_false;
795: x_return_status:=fnd_api.g_ret_sts_unexp_error;
796: FND_MSG_PUB.Count_And_Get
797: (p_count => x_msg_count ,
798: p_data => x_msg_data
799: );
805: -- returns TRUE, then give access. At this time do not check if the
806: -- function is in the responsibility.
807: -- Hence commenting out check of l_fnd_function_test
808:
809: --if (v_fnd_api_ret_code=fnd_api.g_true and l_fnd_function_test = true)then
810: if (v_fnd_api_ret_code=fnd_api.g_true) then
811: x_ret_code:= fnd_api.g_true;
812: x_return_status:=fnd_api.g_ret_sts_success;
813: return;
806: -- function is in the responsibility.
807: -- Hence commenting out check of l_fnd_function_test
808:
809: --if (v_fnd_api_ret_code=fnd_api.g_true and l_fnd_function_test = true)then
810: if (v_fnd_api_ret_code=fnd_api.g_true) then
811: x_ret_code:= fnd_api.g_true;
812: x_return_status:=fnd_api.g_ret_sts_success;
813: return;
814: end if;
807: -- Hence commenting out check of l_fnd_function_test
808:
809: --if (v_fnd_api_ret_code=fnd_api.g_true and l_fnd_function_test = true)then
810: if (v_fnd_api_ret_code=fnd_api.g_true) then
811: x_ret_code:= fnd_api.g_true;
812: x_return_status:=fnd_api.g_ret_sts_success;
813: return;
814: end if;
815:
808:
809: --if (v_fnd_api_ret_code=fnd_api.g_true and l_fnd_function_test = true)then
810: if (v_fnd_api_ret_code=fnd_api.g_true) then
811: x_ret_code:= fnd_api.g_true;
812: x_return_status:=fnd_api.g_ret_sts_success;
813: return;
814: end if;
815:
816:
813: return;
814: end if;
815:
816:
817: x_ret_code:= fnd_api.g_false;
818: x_return_status:=fnd_api.g_ret_sts_success;
819: return;
820:
821: -----------End of check role based security-----------
814: end if;
815:
816:
817: x_ret_code:= fnd_api.g_false;
818: x_return_status:=fnd_api.g_ret_sts_success;
819: return;
820:
821: -----------End of check role based security-----------
822:
829: END IF;
830: fnd_msg_pub.add_exc_msg
831: (p_pkg_name => G_PKG_NAME,
832: p_procedure_name =>'CHECK_CONFIRM_ASMT' );
833: x_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
834: x_msg_count:=x_msg_count+1;
835: x_ret_code :=fnd_api.g_false;
836: raise;
837: end;
831: (p_pkg_name => G_PKG_NAME,
832: p_procedure_name =>'CHECK_CONFIRM_ASMT' );
833: x_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
834: x_msg_count:=x_msg_count+1;
835: x_ret_code :=fnd_api.g_false;
836: raise;
837: end;
838:
839:
872: AND TRUNC(resource_effective_start_date)<=TRUNC(NVL(p_start_date, resource_effective_start_date))
873: AND TRUNC(NVL(p_start_date,SYSDATE))<=TRUNC(resource_effective_end_date);
874:
875: BEGIN
876: x_return_status:=fnd_api.g_ret_sts_success;
877: v_person_id:=get_resource_person_id(p_resource_id);
878:
879: /* Added for Bug 6056112 */
880: SELECT nvl(future_term_wf_flag,'N')
889: IF (nvl(l_future_term_wf_flag,'N') = 'Y') THEN
890: OPEN c_fut_term_org_id;
891: FETCH c_fut_term_org_id INTO x_resource_org_id;
892: IF c_fut_term_org_id%NOTFOUND THEN
893: x_return_status := FND_API.G_RET_STS_ERROR;
894: x_error_message_code := 'PA_RESOURCE_ORG_AMBIGUOUS';
895: END IF ;
896: CLOSE c_fut_term_org_id;
897: ELSE
894: x_error_message_code := 'PA_RESOURCE_ORG_AMBIGUOUS';
895: END IF ;
896: CLOSE c_fut_term_org_id;
897: ELSE
898: x_return_status := FND_API.G_RET_STS_ERROR;
899: x_error_message_code := 'PA_RESOURCE_ORG_AMBIGUOUS';
900: END IF ;
901: /* End of Changes for Bug 6056112 */
902: END IF;
911: x_project_org_id out NOCOPY number, --File.Sql.39 bug 4440895
912: x_return_status out NOCOPY varchar2, --File.Sql.39 bug 4440895
913: x_error_message_code out NOCOPY varchar2 ) is --File.Sql.39 bug 4440895
914: begin
915: x_return_status:=fnd_api.g_ret_sts_success;
916: select CARRYING_OUT_ORGANIZATION_ID
917: into x_project_org_id
918: from pa_projects_all
919: where project_id=p_project_id;
918: from pa_projects_all
919: where project_id=p_project_id;
920: exception
921: WHEN NO_DATA_FOUND THEN
922: x_return_status := FND_API.G_RET_STS_ERROR;
923: x_error_message_code := 'PA_PROJ_ORG_AMBIGUOUS';
924: WHEN TOO_MANY_ROWS THEN
925: x_return_status := FND_API.G_RET_STS_ERROR;
926: x_error_message_code := 'PA_PROJ_ORG_AMBIGUOUS';
921: WHEN NO_DATA_FOUND THEN
922: x_return_status := FND_API.G_RET_STS_ERROR;
923: x_error_message_code := 'PA_PROJ_ORG_AMBIGUOUS';
924: WHEN TOO_MANY_ROWS THEN
925: x_return_status := FND_API.G_RET_STS_ERROR;
926: x_error_message_code := 'PA_PROJ_ORG_AMBIGUOUS';
927: WHEN OTHERS THEN
928: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
929: end;
924: WHEN TOO_MANY_ROWS THEN
925: x_return_status := FND_API.G_RET_STS_ERROR;
926: x_error_message_code := 'PA_PROJ_ORG_AMBIGUOUS';
927: WHEN OTHERS THEN
928: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
929: end;
930:
931:
932: --This procedure checks if the p_manager_id is a HR manager
949: l_mgr_str VARCHAR2(4000);
950: l_mgr_str1 VARCHAR2(20);
951:
952: BEGIN
953: x_return_status:=fnd_api.g_ret_sts_success;
954: x_ret_code:=fnd_api.g_false;
955:
956: BEGIN
957: /* Commented for bug 5916094
950: l_mgr_str1 VARCHAR2(20);
951:
952: BEGIN
953: x_return_status:=fnd_api.g_ret_sts_success;
954: x_ret_code:=fnd_api.g_false;
955:
956: BEGIN
957: /* Commented for bug 5916094
958: There can exists overlapping RM for a resource for same 2 resources for a same duration
1012:
1013: END LOOP;
1014:
1015: IF l_is_manager = 'Y' THEN
1016: x_ret_code:=fnd_api.g_true;
1017: END IF;
1018:
1019: EXCEPTION
1020: WHEN NO_DATA_FOUND THEN
1017: END IF;
1018:
1019: EXCEPTION
1020: WHEN NO_DATA_FOUND THEN
1021: x_ret_code:=fnd_api.g_false;
1022: END;
1023:
1024: EXCEPTION
1025: WHEN OTHERS THEN
1022: END;
1023:
1024: EXCEPTION
1025: WHEN OTHERS THEN
1026: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1027: x_ret_code:=fnd_api.g_false;
1028: raise;
1029: END;
1030:
1023:
1024: EXCEPTION
1025: WHEN OTHERS THEN
1026: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1027: x_ret_code:=fnd_api.g_false;
1028: raise;
1029: END;
1030:
1031:
1032: -- This API is called when a person is assigned to a new role
1033: -- from create_project_party
1034: PROCEDURE grant_role
1035: (
1036: p_commit IN VARCHAR2 := FND_API.G_FALSE,
1037: p_debug_mode in varchar2 default 'N',
1038: p_project_role_id IN number,
1039: p_object_name IN VARCHAR2,
1040: p_instance_type IN VARCHAR2,
1060: l_exp_error varchar2(1) := 'F';
1061: l_unexp_error varchar2(1) := 'F';
1062: l_parameter2 fnd_grants.parameter2%TYPE;
1063: l_role_status_menu_id_tbl SYSTEM.pa_num_tbl_type := null;
1064: l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
1065: l_error_message_code VARCHAR2(30);
1066:
1067: begin
1068:
1067: begin
1068:
1069: --dbms_output.put_line('pa_security_pvt.grant_role');
1070:
1071: x_return_status:=fnd_api.g_ret_sts_success;
1072: x_msg_count:=0;
1073: x_msg_data:=null;
1074:
1075: l_grantee_key:=get_grantee_key(p_source_type, p_party_id, 'Y'); -- Added 'Y' parameter for bug 3471913
1102: ,x_role_status_menu_id_tbl => l_role_status_menu_id_tbl
1103: ,x_return_status => l_return_status
1104: ,x_error_message_code => l_error_message_code);
1105:
1106: IF l_return_status <> fnd_api.g_ret_sts_success THEN
1107: PA_UTILS.Add_Message ( p_app_short_name => 'PA'
1108: ,p_msg_name => l_error_message_code );
1109:
1110: ELSE
1110: ELSE
1111:
1112: --dbms_output.put_line('default menu name: '||l_default_menu_name);
1113:
1114: IF l_default_menu_name is not null AND l_return_status = fnd_api.g_ret_sts_success THEN
1115:
1116: IF l_status_code_tbl IS NULL OR l_status_code_tbl.COUNT = 0 THEN
1117: l_parameter2 := 'NON_STATUS_BASED';
1118: ELSE
1147: );
1148:
1149: --dbms_output.put_line('grant_function: '||l_success);
1150:
1151: if l_success <> fnd_api.g_true then
1152: if l_error_code >0 then
1153: l_exp_error := 'T';
1154: else
1155: l_unexp_error := 'T';
1189:
1190: --dbms_output.put_line('grant_function: '||l_success);
1191:
1192:
1193: if l_success <> fnd_api.g_true then
1194: if l_error_code >0 then
1195: l_exp_error := 'T';
1196: else
1197: l_unexp_error := 'T';
1201: END LOOP;
1202: END IF; -- IF l_status_code_tbl IS NOT NULL OR l_status_code_tbl.COUNT > 0 THEN
1203:
1204: if l_exp_error = 'F' and l_unexp_error = 'F' then
1205: l_return_status:=fnd_api.g_ret_sts_success;
1206: else
1207: if l_unexp_error = 'T' then
1208: l_return_status:=fnd_api.g_ret_sts_unexp_error;
1209: else
1204: if l_exp_error = 'F' and l_unexp_error = 'F' then
1205: l_return_status:=fnd_api.g_ret_sts_success;
1206: else
1207: if l_unexp_error = 'T' then
1208: l_return_status:=fnd_api.g_ret_sts_unexp_error;
1209: else
1210: l_return_status:=fnd_api.g_ret_sts_error;
1211: end if;
1212: end if;
1206: else
1207: if l_unexp_error = 'T' then
1208: l_return_status:=fnd_api.g_ret_sts_unexp_error;
1209: else
1210: l_return_status:=fnd_api.g_ret_sts_error;
1211: end if;
1212: end if;
1213:
1214: END IF; -- IF l_default_menu_id is not null THEN
1220:
1221: --dbms_output.put_line('l_exp_error: '||l_exp_error||' l_unexp_error: '||l_unexp_error);
1222: x_return_status := l_return_status;
1223:
1224: IF l_return_status <> fnd_api.g_ret_sts_success THEN
1225: FND_MSG_PUB.Count_And_Get
1226: (p_count => x_msg_count ,
1227: p_data => x_msg_data
1228: );
1237: -- This API is called when Organization Authority is granted
1238: -- to specified resources.
1239: PROCEDURE grant_org_authority
1240: (
1241: p_commit IN VARCHAR2 := FND_API.G_FALSE,
1242: p_debug_mode in varchar2 default 'N',
1243: p_project_role_id IN number,
1244: p_menu_name in varchar2,
1245: p_object_name IN VARCHAR2,
1267: --Clear the global PL/SQL message table
1268: -- FND_MSG_PUB.initialize; commented the call for Bug 2887390
1269:
1270: --dbms_output.put_line('inside grant role');
1271: x_return_status:=fnd_api.g_ret_sts_success;
1272: x_msg_count:=0;
1273: x_msg_data:=null;
1274: if p_menu_name is null then
1275: v_menu_name:=get_menu_name(p_project_role_id);
1308: x_grant_guid =>x_grant_guid,
1309: x_success =>l_success,
1310: x_errorcode =>l_error_code
1311: ) ;
1312: if l_success=fnd_api.g_true then
1313: x_return_status:=fnd_api.g_ret_sts_success;
1314: else
1315: if l_error_code >0 then
1316: x_return_status:=fnd_api.g_ret_sts_error;
1309: x_success =>l_success,
1310: x_errorcode =>l_error_code
1311: ) ;
1312: if l_success=fnd_api.g_true then
1313: x_return_status:=fnd_api.g_ret_sts_success;
1314: else
1315: if l_error_code >0 then
1316: x_return_status:=fnd_api.g_ret_sts_error;
1317: else
1312: if l_success=fnd_api.g_true then
1313: x_return_status:=fnd_api.g_ret_sts_success;
1314: else
1315: if l_error_code >0 then
1316: x_return_status:=fnd_api.g_ret_sts_error;
1317: else
1318: x_return_status:=fnd_api.g_ret_sts_unexp_error;
1319: end if;
1320: FND_MSG_PUB.Count_And_Get
1314: else
1315: if l_error_code >0 then
1316: x_return_status:=fnd_api.g_ret_sts_error;
1317: else
1318: x_return_status:=fnd_api.g_ret_sts_unexp_error;
1319: end if;
1320: FND_MSG_PUB.Count_And_Get
1321: (p_count => x_msg_count ,
1322: p_data => x_msg_data
1329: end;
1330:
1331: PROCEDURE revoke_grant
1332: (
1333: p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
1334: p_debug_mode in varchar2 default 'N',
1335: p_grant_guid in raw,
1336: x_return_status OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
1337: x_msg_count OUT NOCOPY NUMBER , --File.Sql.39 bug 4440895
1344:
1345: --Clear the global PL/SQL message table
1346: FND_MSG_PUB.initialize;
1347:
1348: x_return_status :=fnd_api.g_ret_sts_success;
1349: x_msg_count:=0;
1350: x_msg_data :=null;
1351: --dbms_output.put_line('inside pa revoke_grants');
1352: fnd_grants_pkg.revoke_grant
1356: x_success =>l_success,
1357: x_errorcode =>l_error_code
1358: );
1359:
1360: if l_success=fnd_api.g_true then
1361: x_return_status:=fnd_api.g_ret_sts_success;
1362: else
1363: if l_error_code >0 then
1364: x_return_status:=fnd_api.g_ret_sts_error;
1357: x_errorcode =>l_error_code
1358: );
1359:
1360: if l_success=fnd_api.g_true then
1361: x_return_status:=fnd_api.g_ret_sts_success;
1362: else
1363: if l_error_code >0 then
1364: x_return_status:=fnd_api.g_ret_sts_error;
1365: else
1360: if l_success=fnd_api.g_true then
1361: x_return_status:=fnd_api.g_ret_sts_success;
1362: else
1363: if l_error_code >0 then
1364: x_return_status:=fnd_api.g_ret_sts_error;
1365: else
1366: x_return_status:=fnd_api.g_ret_sts_unexp_error;
1367: end if;
1368: FND_MSG_PUB.Count_And_Get
1362: else
1363: if l_error_code >0 then
1364: x_return_status:=fnd_api.g_ret_sts_error;
1365: else
1366: x_return_status:=fnd_api.g_ret_sts_unexp_error;
1367: end if;
1368: FND_MSG_PUB.Count_And_Get
1369: (p_count => x_msg_count ,
1370: p_data => x_msg_data
1377:
1378:
1379: PROCEDURE revoke_role
1380: (
1381: p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
1382: p_debug_mode in varchar2 default 'N',
1383: p_project_role_id IN number,
1384: p_menu_name in varchar2,
1385: p_object_name IN VARCHAR2,
1442: p_grant_guid => l_grant_guid,
1443: x_success => l_success,
1444: x_errorcode => l_error_code);
1445:
1446: IF l_success=fnd_api.g_true THEN
1447: x_return_status:=fnd_api.g_ret_sts_success;
1448: ELSE
1449: IF l_error_code>0 THEN
1450: x_return_status:=fnd_api.g_ret_sts_error;
1443: x_success => l_success,
1444: x_errorcode => l_error_code);
1445:
1446: IF l_success=fnd_api.g_true THEN
1447: x_return_status:=fnd_api.g_ret_sts_success;
1448: ELSE
1449: IF l_error_code>0 THEN
1450: x_return_status:=fnd_api.g_ret_sts_error;
1451: ELSE
1446: IF l_success=fnd_api.g_true THEN
1447: x_return_status:=fnd_api.g_ret_sts_success;
1448: ELSE
1449: IF l_error_code>0 THEN
1450: x_return_status:=fnd_api.g_ret_sts_error;
1451: ELSE
1452: x_return_status:=fnd_api.g_ret_sts_unexp_error;
1453: END IF;
1454: FND_MSG_PUB.Count_And_Get
1448: ELSE
1449: IF l_error_code>0 THEN
1450: x_return_status:=fnd_api.g_ret_sts_error;
1451: ELSE
1452: x_return_status:=fnd_api.g_ret_sts_unexp_error;
1453: END IF;
1454: FND_MSG_PUB.Count_And_Get
1455: (p_count => x_msg_count ,
1456: p_data => x_msg_data
1457: );
1458: RETURN;
1459: END IF;
1460:
1461: IF FND_API.To_Boolean( p_commit ) THEN
1462: COMMIT WORK;
1463: END IF;
1464:
1465: EXCEPTION
1464:
1465: EXCEPTION
1466: WHEN OTHERS THEN
1467: ROLLBACK TO revoke_role_PUB;
1468: x_return_status := FND_API.g_ret_sts_unexp_error ;
1469: IF FND_MSG_PUB.Check_Msg_Level
1470: (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1471: THEN
1472: FND_MSG_PUB.Add_Exc_Msg
1481: end;
1482:
1483: -- obsoleted API
1484: PROCEDURE update_role
1485: ( p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
1486: p_debug_mode in varchar2 default 'N',
1487: p_grant_guid in raw,
1488: p_project_role_id_old IN number default null,
1489: p_object_name_old IN VARCHAR2 default null,
1503: begin
1504: --Clear the global PL/SQL message table
1505: FND_MSG_PUB.initialize;
1506:
1507: x_return_status:=fnd_api.g_ret_sts_success;
1508: x_msg_count:=0;
1509: x_msg_data:=null;
1510: fnd_grants_pkg.update_grant
1511: (
1514: p_start_date =>p_start_date_new,
1515: p_end_date =>p_end_date_new,
1516: x_success =>l_success
1517: ) ;
1518: if l_success=fnd_api.g_true then
1519: x_return_status:=fnd_api.g_ret_sts_success;
1520: else
1521: x_return_status:=fnd_api.g_ret_sts_unexp_error;
1522: FND_MSG_PUB.Count_And_Get
1515: p_end_date =>p_end_date_new,
1516: x_success =>l_success
1517: ) ;
1518: if l_success=fnd_api.g_true then
1519: x_return_status:=fnd_api.g_ret_sts_success;
1520: else
1521: x_return_status:=fnd_api.g_ret_sts_unexp_error;
1522: FND_MSG_PUB.Count_And_Get
1523: (p_count => x_msg_count ,
1517: ) ;
1518: if l_success=fnd_api.g_true then
1519: x_return_status:=fnd_api.g_ret_sts_success;
1520: else
1521: x_return_status:=fnd_api.g_ret_sts_unexp_error;
1522: FND_MSG_PUB.Count_And_Get
1523: (p_count => x_msg_count ,
1524: p_data => x_msg_data
1525: );
1532:
1533:
1534: PROCEDURE lock_grant
1535: (
1536: p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
1537: p_debug_mode in varchar2 default 'N',
1538: p_grant_guid in raw,
1539: p_project_role_id_old IN number default null,
1540: p_object_name_old IN VARCHAR2 default null,
1606: ) ;
1607: x_return_status:='S';
1608: exception
1609: when others then
1610: x_return_status:=fnd_api.g_ret_sts_unexp_error;
1611: raise;
1612: end;
1613:
1614:
1971:
1972: pa_debug.Init_err_stack ( 'Check_Access_Exist');
1973:
1974: Init_global;
1975: x_ret_code := FND_API.G_TRUE;
1976: x_return_status := FND_API.G_RET_STS_SUCCESS;
1977: x_msg_count := 0;
1978: x_msg_data := NULL;
1979:
1972: pa_debug.Init_err_stack ( 'Check_Access_Exist');
1973:
1974: Init_global;
1975: x_ret_code := FND_API.G_TRUE;
1976: x_return_status := FND_API.G_RET_STS_SUCCESS;
1977: x_msg_count := 0;
1978: x_msg_data := NULL;
1979:
1980: ------- Check for License
1978: x_msg_data := NULL;
1979:
1980: ------- Check for License
1981: IF pa_product_install_utils.check_function_licensed(p_privilege) <> 'Y' THEN
1982: x_ret_code:= fnd_api.g_false;
1983: x_return_status:=fnd_api.g_ret_sts_success;
1984: RETURN;
1985: END IF;
1986: ------- End check for License
1979:
1980: ------- Check for License
1981: IF pa_product_install_utils.check_function_licensed(p_privilege) <> 'Y' THEN
1982: x_ret_code:= fnd_api.g_false;
1983: x_return_status:=fnd_api.g_ret_sts_success;
1984: RETURN;
1985: END IF;
1986: ------- End check for License
1987:
2004: pa_debug.write_file('check_access_exist: ' || 'LOG', pa_debug.G_err_stage);
2005: END IF;
2006: IF fnd_function.test(p_privilege) THEN
2007: --Adding the below code for the bug 3137696
2008: x_ret_code := FND_API.G_TRUE;
2009: x_return_status := FND_API.G_RET_STS_SUCCESS;
2010: RETURN;
2011: --Commented the below code for the bug 3137696
2012: /*IF p_object_name='PA_PROJECTS' THEN
2005: END IF;
2006: IF fnd_function.test(p_privilege) THEN
2007: --Adding the below code for the bug 3137696
2008: x_ret_code := FND_API.G_TRUE;
2009: x_return_status := FND_API.G_RET_STS_SUCCESS;
2010: RETURN;
2011: --Commented the below code for the bug 3137696
2012: /*IF p_object_name='PA_PROJECTS' THEN
2013: -- Bug 1571014, faulty dynamic sql
2074: p_statement_type => 'EXISTS',
2075: x_predicate => l_predicate,
2076: x_return_status => x_return_status);
2077:
2078: -- IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
2079: IF x_return_status<>'T' AND x_return_status<>'F' THEN
2080: x_ret_code := FND_API.G_FALSE;
2081: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2082: FND_MSG_PUB.Count_And_Get
2076: x_return_status => x_return_status);
2077:
2078: -- IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
2079: IF x_return_status<>'T' AND x_return_status<>'F' THEN
2080: x_ret_code := FND_API.G_FALSE;
2081: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2082: FND_MSG_PUB.Count_And_Get
2083: (p_count => x_msg_count ,
2084: p_data => x_msg_data);
2077:
2078: -- IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
2079: IF x_return_status<>'T' AND x_return_status<>'F' THEN
2080: x_ret_code := FND_API.G_FALSE;
2081: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2082: FND_MSG_PUB.Count_And_Get
2083: (p_count => x_msg_count ,
2084: p_data => x_msg_data);
2085: RETURN;
2083: (p_count => x_msg_count ,
2084: p_data => x_msg_data);
2085: RETURN;
2086: ELSIF x_return_status='F' THEN
2087: x_ret_code := FND_API.G_FALSE;
2088: x_return_status := FND_API.G_RET_STS_SUCCESS;
2089: x_msg_count := 0;
2090: x_msg_data := '';
2091: RETURN;
2084: p_data => x_msg_data);
2085: RETURN;
2086: ELSIF x_return_status='F' THEN
2087: x_ret_code := FND_API.G_FALSE;
2088: x_return_status := FND_API.G_RET_STS_SUCCESS;
2089: x_msg_count := 0;
2090: x_msg_data := '';
2091: RETURN;
2092: END IF;
2103: END IF;
2104: OPEN l_cur FOR l_predicate;
2105: FETCH l_cur INTO l_dummy;
2106: IF l_cur%NOTFOUND THEN
2107: x_ret_code := FND_API.G_FALSE;
2108: x_return_status := FND_API.G_RET_STS_SUCCESS;
2109: x_msg_count := 0;
2110: x_msg_data := '';
2111: CLOSE l_cur; -- Bug #2994870: closing the cursor.
2104: OPEN l_cur FOR l_predicate;
2105: FETCH l_cur INTO l_dummy;
2106: IF l_cur%NOTFOUND THEN
2107: x_ret_code := FND_API.G_FALSE;
2108: x_return_status := FND_API.G_RET_STS_SUCCESS;
2109: x_msg_count := 0;
2110: x_msg_data := '';
2111: CLOSE l_cur; -- Bug #2994870: closing the cursor.
2112: RETURN;
2118: pa_debug.G_err_stage := 'exceptions raised';
2119: IF G_debug_flag = 'Y' THEN
2120: pa_debug.write_file('check_access_exist: ' || 'LOG', pa_debug.G_err_stage);
2121: END IF;
2122: x_ret_code := FND_API.G_FALSE;
2123: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2124: FND_MSG_PUB.Count_And_Get
2125: (p_count => x_msg_count ,
2126: p_data => x_msg_data);
2119: IF G_debug_flag = 'Y' THEN
2120: pa_debug.write_file('check_access_exist: ' || 'LOG', pa_debug.G_err_stage);
2121: END IF;
2122: x_ret_code := FND_API.G_FALSE;
2123: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2124: FND_MSG_PUB.Count_And_Get
2125: (p_count => x_msg_count ,
2126: p_data => x_msg_data);
2127:
2181:
2182: -- This API is called when the default Menu is changed
2183: -- in Roles form for existing roles which are in use.
2184: PROCEDURE update_menu
2185: ( p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
2186: p_debug_mode in varchar2 default 'N',
2187: p_project_role_id IN number,
2188: p_menu_id IN number,
2189: x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
2204: AND (parameter2 = 'NON_STATUS_BASED' OR parameter2 = 'DEFAULT')
2205: AND instance_type = 'SET'
2206: AND instance_set_id = G_project_roles_ins_set_id;
2207:
2208: x_return_status:=fnd_api.g_ret_sts_success;
2209:
2210: EXCEPTION
2211: when others then
2212: x_return_status:=fnd_api.g_ret_sts_unexp_error;
2208: x_return_status:=fnd_api.g_ret_sts_success;
2209:
2210: EXCEPTION
2211: when others then
2212: x_return_status:=fnd_api.g_ret_sts_unexp_error;
2213: fnd_msg_pub.add_exc_msg
2214: (p_pkg_name => 'PA_SECURITY_PVT',
2215: p_procedure_name => 'UPDATE_MENU',
2216: p_error_text => SQLCODE);
2221:
2222: -- This API is called when Enforce Role-based Security checkbox
2223: -- is unchecked in Roles form for existing roles which are in use.
2224: PROCEDURE revoke_role_based_sec
2225: ( p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
2226: p_debug_mode in varchar2 default 'N',
2227: p_project_role_id IN number,
2228: x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
2229: x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
2241: WHERE parameter1 = to_char(p_project_role_id)
2242: AND instance_type = 'SET'
2243: AND instance_set_id = G_project_roles_ins_set_id;
2244:
2245: x_return_status:=fnd_api.g_ret_sts_success;
2246:
2247: EXCEPTION
2248: when others then
2249: x_return_status:=fnd_api.g_ret_sts_unexp_error;
2245: x_return_status:=fnd_api.g_ret_sts_success;
2246:
2247: EXCEPTION
2248: when others then
2249: x_return_status:=fnd_api.g_ret_sts_unexp_error;
2250: fnd_msg_pub.add_exc_msg
2251: (p_pkg_name => 'PA_SECURITY_PVT',
2252: p_procedure_name => 'REVOKE_ROLE_BASED_SEC',
2253: p_error_text => SQLCODE);
2257:
2258: -- This API is called when Enforce Role-based Security checkbox
2259: -- is checked in Roles form for existing roles which are in use.
2260: PROCEDURE grant_role_based_sec
2261: ( p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
2262: p_debug_mode in varchar2 default 'N',
2263: p_project_role_id IN number,
2264: x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
2265: x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
2278: l_menu_name_tbl SYSTEM.pa_varchar2_30_tbl_type := null;
2279: l_exp_error varchar2(1) := 'F';
2280: l_unexp_error varchar2(1) := 'F';
2281: l_parameter2 fnd_grants.parameter2%TYPE;
2282: l_return_status varchar2(1) := fnd_api.g_ret_sts_success;
2283: l_error_message_code VARCHAR2(30);
2284: l_grant_guid fnd_grants.grant_guid%TYPE;
2285: l_role_status_menu_id_tbl SYSTEM.pa_num_tbl_type := null;
2286:
2325: ,x_error_message_code => l_error_message_code);
2326:
2327: --dbms_output.put_line('Get_Role_Status_Menus:'||l_return_status);
2328:
2329: IF l_return_status <> fnd_api.g_ret_sts_success THEN
2330: PA_UTILS.Add_Message ( p_app_short_name => 'PA'
2331: ,p_msg_name => l_error_message_code );
2332: ELSE
2333:
2387: x_success => l_success,
2388: x_errorcode => l_error_code
2389: );
2390:
2391: if l_success <> fnd_api.g_true then
2392: if l_error_code >0 then
2393: l_exp_error := 'T';
2394: else
2395: l_unexp_error := 'T';
2424: x_success => l_success,
2425: x_errorcode => l_error_code
2426: ) ;
2427:
2428: if l_success <> fnd_api.g_true then
2429: if l_error_code >0 then
2430: l_exp_error := 'T';
2431: else
2432: l_unexp_error := 'T';
2439:
2440: END LOOP; -- FOR res in get_resources_on_role LOOP
2441:
2442: if l_exp_error = 'T' then
2443: l_return_status:=fnd_api.g_ret_sts_error;
2444: elsif l_unexp_error = 'T' then
2445: l_return_status:=fnd_api.g_ret_sts_unexp_error;
2446: else
2447: l_return_status:=fnd_api.g_ret_sts_success;
2441:
2442: if l_exp_error = 'T' then
2443: l_return_status:=fnd_api.g_ret_sts_error;
2444: elsif l_unexp_error = 'T' then
2445: l_return_status:=fnd_api.g_ret_sts_unexp_error;
2446: else
2447: l_return_status:=fnd_api.g_ret_sts_success;
2448:
2449: END IF;
2443: l_return_status:=fnd_api.g_ret_sts_error;
2444: elsif l_unexp_error = 'T' then
2445: l_return_status:=fnd_api.g_ret_sts_unexp_error;
2446: else
2447: l_return_status:=fnd_api.g_ret_sts_success;
2448:
2449: END IF;
2450:
2451: END IF; -- l_return_status = 'S'
2451: END IF; -- l_return_status = 'S'
2452:
2453: x_return_status:=l_return_status;
2454:
2455: IF l_return_status <> fnd_api.g_ret_sts_success THEN
2456: FND_MSG_PUB.Count_And_Get
2457: (p_count => x_msg_count ,
2458: p_data => x_msg_data
2459: );
2460: END IF;
2461:
2462: EXCEPTION
2463: when others then
2464: x_return_status:=fnd_api.g_ret_sts_unexp_error;
2465: fnd_msg_pub.add_exc_msg
2466: (p_pkg_name => 'PA_SECURITY_PVT',
2467: p_procedure_name => 'GRANT_ROLE_BASED_SEC',
2468: p_error_text => SQLCODE);
2475:
2476: -- This API is called when Status Level is changed
2477: -- in Roles form for existing roles which are in use.
2478: PROCEDURE revoke_status_based_sec
2479: ( p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
2480: p_debug_mode in varchar2 default 'N',
2481: p_project_role_id IN number,
2482: x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
2483: x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
2496: AND (parameter2 = 'USER' OR parameter2 = 'SYSTEM')
2497: AND instance_type = 'SET'
2498: AND instance_set_id = G_project_roles_ins_set_id;
2499:
2500: x_return_status:=fnd_api.g_ret_sts_success;
2501:
2502: EXCEPTION
2503: when others then
2504: x_return_status:=fnd_api.g_ret_sts_unexp_error;
2500: x_return_status:=fnd_api.g_ret_sts_success;
2501:
2502: EXCEPTION
2503: when others then
2504: x_return_status:=fnd_api.g_ret_sts_unexp_error;
2505: fnd_msg_pub.add_exc_msg
2506: (p_pkg_name => 'PA_SECURITY_PVT',
2507: p_procedure_name => 'REVOKE_STATUS_BASED_SEC',
2508: p_error_text => SQLCODE);
2511:
2512: -- This API is called when status/menu under Project Status is changed
2513: -- in Roles form for existing roles which are in use.
2514: PROCEDURE update_status_based_sec
2515: ( p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
2516: p_debug_mode in varchar2 default 'N',
2517: p_project_role_id IN number,
2518: p_status_level IN pa_project_role_types_b.status_level%TYPE,
2519: p_new_status_code_tbl IN SYSTEM.pa_varchar2_30_tbl_type := null,
2555: l_grantee_key fnd_grants.grantee_key%TYPE;
2556: l_status_menu_count NUMBER := 0;
2557: l_parameter2 fnd_grants.parameter2%TYPE;
2558: l_opp_param2 fnd_grants.parameter2%TYPE;
2559: l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
2560: l_grant_guid fnd_grants.grant_guid%TYPE;
2561: l_success varchar2(1);
2562: l_exp_error varchar2(1);
2563: l_unexp_error varchar2(1);
2604: x_success => l_success,
2605: x_errorcode => l_error_code
2606: ) ;
2607:
2608: if l_success <> fnd_api.g_true then
2609: if l_error_code >0 then
2610: l_exp_error := 'T';
2611: else
2612: l_unexp_error := 'T';
2616: END LOOP; -- p_new_status_code_tbl
2617: END LOOP; -- get_resources_on_role
2618:
2619: if l_exp_error = 'T' then
2620: l_return_status:=fnd_api.g_ret_sts_error;
2621: elsif l_unexp_error = 'T' then
2622: l_return_status:=fnd_api.g_ret_sts_unexp_error;
2623: else
2624: l_return_status:=fnd_api.g_ret_sts_success;
2618:
2619: if l_exp_error = 'T' then
2620: l_return_status:=fnd_api.g_ret_sts_error;
2621: elsif l_unexp_error = 'T' then
2622: l_return_status:=fnd_api.g_ret_sts_unexp_error;
2623: else
2624: l_return_status:=fnd_api.g_ret_sts_success;
2625: end if;
2626:
2620: l_return_status:=fnd_api.g_ret_sts_error;
2621: elsif l_unexp_error = 'T' then
2622: l_return_status:=fnd_api.g_ret_sts_unexp_error;
2623: else
2624: l_return_status:=fnd_api.g_ret_sts_success;
2625: end if;
2626:
2627: END IF; -- p_new_status_code_tbl.COUNT > 0 THEN
2628:
2625: end if;
2626:
2627: END IF; -- p_new_status_code_tbl.COUNT > 0 THEN
2628:
2629: IF l_return_status = fnd_api.g_ret_sts_success THEN
2630:
2631: -- 2. delete obsolete records from FND_GRANTS
2632: IF p_del_status_code_tbl IS NOT NULL AND p_del_status_code_tbl.COUNT > 0 THEN
2633:
2682: AND parameter2 = l_opp_param2
2683: AND instance_type = 'SET'
2684: AND instance_set_id = G_project_roles_ins_set_id;
2685:
2686: END IF; -- l_return_status = fnd_api.g_ret_sts_success THEN
2687:
2688: x_return_status := l_return_status;
2689:
2690: EXCEPTION
2688: x_return_status := l_return_status;
2689:
2690: EXCEPTION
2691: when others then
2692: x_return_status:=fnd_api.g_ret_sts_unexp_error;
2693: fnd_msg_pub.add_exc_msg
2694: (p_pkg_name => 'PA_SECURITY_PVT',
2695: p_procedure_name => 'UPDATE_STATUS_BASED_SEC',
2696: p_error_text => SQLCODE);
2739: pa_debug.Init_err_stack ( 'GET_RESP_WITH_ACCESS');
2740:
2741: x_msg_count := 0;
2742: x_msg_data := null;
2743: x_return_status := fnd_api.g_ret_sts_success;
2744: x_has_access := 'N';
2745:
2746: l_old_resp_id := FND_GLOBAL.RESP_ID;
2747: l_old_resp_appl_id := FND_GLOBAL.RESP_APPL_ID;
2802: fnd_msg_pub.add_exc_msg
2803: (p_pkg_name => G_PKG_NAME,
2804: p_procedure_name =>'get_resp_with_access' );
2805:
2806: x_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
2807: x_has_access := 'N';
2808: x_resp_key := '';
2809: x_appl_short_name := '';
2810: