[Home] [Help]
166: p_init_msg_list IN VARCHAR2 DEFAULT 'Y') is
167:
168: -- secure_role_flag varchar2(1);
169: secure_resp_flag varchar2(1);
170: v_fnd_api_ret_code varchar2(1);
171: i BINARY_INTEGER;
172: l_exist_flag varchar2(1):='F';
173:
174: Begin
181:
182: pa_debug.Init_err_stack ( 'Check User Privilege');
183: x_msg_count :=0;
184: x_msg_data:= null;
185: x_return_status:=fnd_api.g_ret_sts_success;
186: x_ret_code:=fnd_api.g_true;
187:
188: ------- Check for License
189: IF pa_product_install_utils.check_function_licensed(p_privilege) <> 'Y' THEN
182: pa_debug.Init_err_stack ( 'Check User Privilege');
183: x_msg_count :=0;
184: x_msg_data:= null;
185: x_return_status:=fnd_api.g_ret_sts_success;
186: x_ret_code:=fnd_api.g_true;
187:
188: ------- Check for License
189: IF pa_product_install_utils.check_function_licensed(p_privilege) <> 'Y' THEN
190: x_ret_code:= fnd_api.g_false;
186: x_ret_code:=fnd_api.g_true;
187:
188: ------- Check for License
189: IF pa_product_install_utils.check_function_licensed(p_privilege) <> 'Y' THEN
190: x_ret_code:= fnd_api.g_false;
191: x_return_status:=fnd_api.g_ret_sts_success;
192: RETURN;
193: END IF;
194: ------- End check for License
187:
188: ------- Check for License
189: IF pa_product_install_utils.check_function_licensed(p_privilege) <> 'Y' THEN
190: x_ret_code:= fnd_api.g_false;
191: x_return_status:=fnd_api.g_ret_sts_success;
192: RETURN;
193: END IF;
194: ------- End check for License
195:
200: IF G_debug_flag = 'Y' THEN
201: pa_debug.write_file('check_user_privilege: ' || 'LOG', pa_debug.G_err_stage);
202: END IF;
203: if fnd_function.test(p_privilege) then
204: x_ret_code:=fnd_api.g_true;
205: x_return_status:=fnd_api.g_ret_sts_success;
206: else
207: x_ret_code:=fnd_api.g_false;
208: x_return_status:=fnd_api.g_ret_sts_success;
201: pa_debug.write_file('check_user_privilege: ' || 'LOG', pa_debug.G_err_stage);
202: END IF;
203: if fnd_function.test(p_privilege) then
204: x_ret_code:=fnd_api.g_true;
205: x_return_status:=fnd_api.g_ret_sts_success;
206: else
207: x_ret_code:=fnd_api.g_false;
208: x_return_status:=fnd_api.g_ret_sts_success;
209: end if;
203: if fnd_function.test(p_privilege) then
204: x_ret_code:=fnd_api.g_true;
205: x_return_status:=fnd_api.g_ret_sts_success;
206: else
207: x_ret_code:=fnd_api.g_false;
208: x_return_status:=fnd_api.g_ret_sts_success;
209: end if;
210: return;
211: end if;
204: x_ret_code:=fnd_api.g_true;
205: x_return_status:=fnd_api.g_ret_sts_success;
206: else
207: x_ret_code:=fnd_api.g_false;
208: x_return_status:=fnd_api.g_ret_sts_success;
209: end if;
210: return;
211: end if;
212:
230: --dbms_output.put_line('p_object_name:'||p_object_name);
231: --dbms_output.put_line('p_object_key:'||p_object_key);
232:
233:
234: v_fnd_api_ret_code:=fnd_data_security.check_function(
235: p_api_version =>l_api_version,
236: p_function =>p_privilege,
237: p_object_name => p_object_name,
238: p_instance_pk1_value=>p_object_key,
242: p_instance_pk5_value => NULL );
243: -- p_user_name => get_grantee_key ); Commented for Bug 4498436.
244:
245: --dbms_output.put_line('after calling fnd_data_sec');
246: --dbms_output.put_line('v_fnd_api_ret_code:'||v_fnd_api_ret_code);
247: if v_fnd_api_ret_code=fnd_api.g_ret_sts_error
248: OR v_fnd_api_ret_code=fnd_api.g_ret_sts_unexp_error then
249: x_ret_code:=fnd_api.g_false;
250: x_return_status:=fnd_api.g_ret_sts_unexp_error;
243: -- p_user_name => get_grantee_key ); Commented for Bug 4498436.
244:
245: --dbms_output.put_line('after calling fnd_data_sec');
246: --dbms_output.put_line('v_fnd_api_ret_code:'||v_fnd_api_ret_code);
247: if v_fnd_api_ret_code=fnd_api.g_ret_sts_error
248: OR v_fnd_api_ret_code=fnd_api.g_ret_sts_unexp_error then
249: x_ret_code:=fnd_api.g_false;
250: x_return_status:=fnd_api.g_ret_sts_unexp_error;
251: FND_MSG_PUB.Count_And_Get
244:
245: --dbms_output.put_line('after calling fnd_data_sec');
246: --dbms_output.put_line('v_fnd_api_ret_code:'||v_fnd_api_ret_code);
247: if v_fnd_api_ret_code=fnd_api.g_ret_sts_error
248: OR v_fnd_api_ret_code=fnd_api.g_ret_sts_unexp_error then
249: x_ret_code:=fnd_api.g_false;
250: x_return_status:=fnd_api.g_ret_sts_unexp_error;
251: FND_MSG_PUB.Count_And_Get
252: (p_count => x_msg_count ,
245: --dbms_output.put_line('after calling fnd_data_sec');
246: --dbms_output.put_line('v_fnd_api_ret_code:'||v_fnd_api_ret_code);
247: if v_fnd_api_ret_code=fnd_api.g_ret_sts_error
248: OR v_fnd_api_ret_code=fnd_api.g_ret_sts_unexp_error then
249: x_ret_code:=fnd_api.g_false;
250: x_return_status:=fnd_api.g_ret_sts_unexp_error;
251: FND_MSG_PUB.Count_And_Get
252: (p_count => x_msg_count ,
253: p_data => x_msg_data
246: --dbms_output.put_line('v_fnd_api_ret_code:'||v_fnd_api_ret_code);
247: if v_fnd_api_ret_code=fnd_api.g_ret_sts_error
248: OR v_fnd_api_ret_code=fnd_api.g_ret_sts_unexp_error then
249: x_ret_code:=fnd_api.g_false;
250: x_return_status:=fnd_api.g_ret_sts_unexp_error;
251: FND_MSG_PUB.Count_And_Get
252: (p_count => x_msg_count ,
253: p_data => x_msg_data
254: );
254: );
255: return;
256: end if;
257:
258: if v_fnd_api_ret_code=fnd_api.g_true then
259: x_ret_code:= fnd_api.g_true;
260: x_return_status:=fnd_api.g_ret_sts_success;
261: return;
262: end if;
255: return;
256: end if;
257:
258: if v_fnd_api_ret_code=fnd_api.g_true then
259: x_ret_code:= fnd_api.g_true;
260: x_return_status:=fnd_api.g_ret_sts_success;
261: return;
262: end if;
263:
256: end if;
257:
258: if v_fnd_api_ret_code=fnd_api.g_true then
259: x_ret_code:= fnd_api.g_true;
260: x_return_status:=fnd_api.g_ret_sts_success;
261: return;
262: end if;
263:
264: if v_fnd_api_ret_code=fnd_api.g_false then
260: x_return_status:=fnd_api.g_ret_sts_success;
261: return;
262: end if;
263:
264: if v_fnd_api_ret_code=fnd_api.g_false then
265: x_ret_code:= fnd_api.g_false;
266: x_return_status:=fnd_api.g_ret_sts_success;
267: ----not return from here. need to continue to do
268: ----responsibility level check
261: return;
262: end if;
263:
264: if v_fnd_api_ret_code=fnd_api.g_false then
265: x_ret_code:= fnd_api.g_false;
266: x_return_status:=fnd_api.g_ret_sts_success;
267: ----not return from here. need to continue to do
268: ----responsibility level check
269: end if;
262: end if;
263:
264: if v_fnd_api_ret_code=fnd_api.g_false then
265: x_ret_code:= fnd_api.g_false;
266: x_return_status:=fnd_api.g_ret_sts_success;
267: ----not return from here. need to continue to do
268: ----responsibility level check
269: end if;
270: -----------End of check role based security-----------
291: G_source_type ,
292: p_object_key );
293:
294:
295: if secure_resp_flag =FND_API.G_RET_STS_UNEXP_ERROR then
296: x_ret_code:=fnd_api.g_false;
297: x_return_status:=fnd_api.g_ret_sts_unexp_error;
298:
299: FND_MSG_PUB.Count_And_Get
292: p_object_key );
293:
294:
295: if secure_resp_flag =FND_API.G_RET_STS_UNEXP_ERROR then
296: x_ret_code:=fnd_api.g_false;
297: x_return_status:=fnd_api.g_ret_sts_unexp_error;
298:
299: FND_MSG_PUB.Count_And_Get
300: (p_count => x_msg_count ,
293:
294:
295: if secure_resp_flag =FND_API.G_RET_STS_UNEXP_ERROR then
296: x_ret_code:=fnd_api.g_false;
297: x_return_status:=fnd_api.g_ret_sts_unexp_error;
298:
299: FND_MSG_PUB.Count_And_Get
300: (p_count => x_msg_count ,
301: p_data => x_msg_data
306: -----Check responsibility level function security in these cases:
307: -----1. The user plays unsecured roles (roles without menu) on the object
308: -----2. The user doesn't play any roles (instance assignment) on the object
309:
310: if secure_resp_flag=fnd_api.g_true then
311: pa_debug.G_err_stage := 'check responsibility level security: in object context';
312: IF G_debug_flag = 'Y' THEN
313: pa_debug.write_file('check_user_privilege: ' || 'LOG', pa_debug.G_err_stage);
314: END IF;
313: pa_debug.write_file('check_user_privilege: ' || 'LOG', pa_debug.G_err_stage);
314: END IF;
315: if p_object_name='PA_PROJECTS' then
316: if pa_security.allow_update (p_object_key)<>'Y' then
317: x_ret_code:=fnd_api.g_false;
318: x_return_status:=fnd_api.g_ret_sts_success;
319: return;
320: end if;
321: end if;
314: END IF;
315: if p_object_name='PA_PROJECTS' then
316: if pa_security.allow_update (p_object_key)<>'Y' then
317: x_ret_code:=fnd_api.g_false;
318: x_return_status:=fnd_api.g_ret_sts_success;
319: return;
320: end if;
321: end if;
322:
320: end if;
321: end if;
322:
323: if fnd_function.test(p_privilege) then
324: x_ret_code:=fnd_api.g_true;
325: x_return_status:=fnd_api.g_ret_sts_success;
326: return;
327: end if;
328:
321: end if;
322:
323: if fnd_function.test(p_privilege) then
324: x_ret_code:=fnd_api.g_true;
325: x_return_status:=fnd_api.g_ret_sts_success;
326: return;
327: end if;
328:
329: x_ret_code:=fnd_api.g_false;
325: x_return_status:=fnd_api.g_ret_sts_success;
326: return;
327: end if;
328:
329: x_ret_code:=fnd_api.g_false;
330: x_return_status:=fnd_api.g_ret_sts_success;
331:
332: ---The following code is for testing the error page
333: /* fnd_message.set_name('PA','PA_SEC_NO_ACCESS');
326: return;
327: end if;
328:
329: x_ret_code:=fnd_api.g_false;
330: x_return_status:=fnd_api.g_ret_sts_success;
331:
332: ---The following code is for testing the error page
333: /* fnd_message.set_name('PA','PA_SEC_NO_ACCESS');
334: fnd_msg_pub.ADD;
347: END IF;
348: fnd_msg_pub.add_exc_msg
349: (p_pkg_name => G_PKG_NAME,
350: p_procedure_name =>'CHECK_USER_PRIVILEGE' );
351: x_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
352: x_ret_code :=fnd_api.g_false;
353: FND_MSG_PUB.Count_And_Get
354: (p_count => x_msg_count ,
355: p_data => x_msg_data
348: fnd_msg_pub.add_exc_msg
349: (p_pkg_name => G_PKG_NAME,
350: p_procedure_name =>'CHECK_USER_PRIVILEGE' );
351: x_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
352: x_ret_code :=fnd_api.g_false;
353: FND_MSG_PUB.Count_And_Get
354: (p_count => x_msg_count ,
355: p_data => x_msg_data
356: );
396: open c_role_sec_enabled;
397: fetch c_role_sec_enabled into v_dummy;
398: if c_role_sec_enabled%found then
399: close c_role_sec_enabled; -- Bug #2994870: closing the cursor.
400: return fnd_api.g_true;
401: else
402: close c_role_sec_enabled; -- Bug #2994870: closing the cursor.
403: return fnd_api.g_false;
404: end if;
399: close c_role_sec_enabled; -- Bug #2994870: closing the cursor.
400: return fnd_api.g_true;
401: else
402: close c_role_sec_enabled; -- Bug #2994870: closing the cursor.
403: return fnd_api.g_false;
404: end if;
405: Exception
406: when others then
407: IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
408: fnd_msg_pub.add_exc_msg
409: (p_pkg_name => G_PKG_NAME,
410: p_procedure_name => 'check_sec_by_role');
411: end if;
412: return fnd_api.g_ret_sts_unexp_error;
413: end;*/
414:
415:
416: ---This API check if responsibility based security is enforced or not
486: FETCH c_unsecured_role INTO v_dummy;
487: IF c_unsecured_role%found THEN
488: --Check responsibility if an unsecured role is found
489: CLOSE c_unsecured_role;
490: RETURN fnd_api.g_true;
491: END IF;
492: CLOSE c_unsecured_role;
493:
494: OPEN c_any_role;
495: FETCH c_any_role INTO v_dummy;
496: IF c_any_role%notfound THEN
497: --Check responsibility if no roles are found
498: CLOSE c_any_role;
499: RETURN fnd_api.g_true;
500: END IF;
501: CLOSE c_any_role;
502:
503: IF p_object_name IN ('PA_PROJECTS')
502:
503: IF p_object_name IN ('PA_PROJECTS')
504: AND pa_security.g_cross_project_user='Y' THEN
505: --profile option override this check
506: RETURN fnd_api.g_true;
507: END IF;
508:
509: RETURN fnd_api.g_false;
510:
505: --profile option override this check
506: RETURN fnd_api.g_true;
507: END IF;
508:
509: RETURN fnd_api.g_false;
510:
511: Exception
512: when others then
513: IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
514: fnd_msg_pub.add_exc_msg
515: (p_pkg_name => G_PKG_NAME,
516: p_procedure_name => 'check_sec_by_resp');
517: end if;
518: return fnd_api.g_ret_sts_unexp_error;
519: end;
520:
521:
522: --function get_resource_person_id
559: v_error_message_code varchar2(30);
560: v_resource_org_id number;
561: v_resource_emp_id number;
562: v_project_org_id number;
563: v_fnd_api_ret_code varchar2(1);
564: v_resource_super_user varchar2(1) := 'N';
565: l_fnd_function_test boolean:=false; -- Added for Bug2970209
566: begin
567:
574:
575: pa_debug.Init_err_stack ( 'check_confirm_asmt');
576: x_msg_count :=0;
577: x_msg_data:= null;
578: x_return_status:=fnd_api.g_ret_sts_success;
579: x_ret_code:=fnd_api.g_true;
580:
581: --Return false if login user is not an employee
582: IF G_source_type<>'PERSON' THEN
575: pa_debug.Init_err_stack ( 'check_confirm_asmt');
576: x_msg_count :=0;
577: x_msg_data:= null;
578: x_return_status:=fnd_api.g_ret_sts_success;
579: x_ret_code:=fnd_api.g_true;
580:
581: --Return false if login user is not an employee
582: IF G_source_type<>'PERSON' THEN
583: x_ret_code:=FND_API.G_FALSE;
579: x_ret_code:=fnd_api.g_true;
580:
581: --Return false if login user is not an employee
582: IF G_source_type<>'PERSON' THEN
583: x_ret_code:=FND_API.G_FALSE;
584: RETURN;
585: END IF;
586:
587: -----Initialization: get resource id, resource org, project org
592: END IF;
593:
594: ------- Check for License
595: IF pa_product_install_utils.check_function_licensed(p_privilege) <> 'Y' THEN
596: x_ret_code:= fnd_api.g_false;
597: RETURN;
598: END IF;
599: ------- End check for License
600:
608: X_RESOURCE_ID=>v_resource_id ,
609: X_RESOURCE_TYPE_ID=>v_resource_type_id ,
610: X_RETURN_STATUS=>v_return_status ,
611: X_ERROR_MESSAGE_CODE=>v_error_message_code );
612: if v_return_status <>fnd_api.g_ret_sts_success then
613: x_msg_count := x_msg_count+1;
614: x_msg_data := v_error_message_code;
615: x_return_status := fnd_api.g_ret_sts_error;
616: x_ret_code:=fnd_api.g_false;
611: X_ERROR_MESSAGE_CODE=>v_error_message_code );
612: if v_return_status <>fnd_api.g_ret_sts_success then
613: x_msg_count := x_msg_count+1;
614: x_msg_data := v_error_message_code;
615: x_return_status := fnd_api.g_ret_sts_error;
616: x_ret_code:=fnd_api.g_false;
617: FND_MSG_PUB.add_exc_msg
618: (p_pkg_name =>'pa_resource_utils',
619: p_procedure_name => 'Check_ResourceName_Or_Id',
612: if v_return_status <>fnd_api.g_ret_sts_success then
613: x_msg_count := x_msg_count+1;
614: x_msg_data := v_error_message_code;
615: x_return_status := fnd_api.g_ret_sts_error;
616: x_ret_code:=fnd_api.g_false;
617: FND_MSG_PUB.add_exc_msg
618: (p_pkg_name =>'pa_resource_utils',
619: p_procedure_name => 'Check_ResourceName_Or_Id',
620: p_error_text => v_error_message_code);
627: p_start_date ,
628: v_resource_org_id ,
629: v_return_status ,
630: v_error_message_code ) ;
631: if v_return_status <>fnd_api.g_ret_sts_success then
632: x_msg_count := x_msg_count+1;
633: x_msg_data := v_error_message_code;
634: x_return_status := v_return_status;
635: x_ret_code:=fnd_api.g_false;
631: if v_return_status <>fnd_api.g_ret_sts_success then
632: x_msg_count := x_msg_count+1;
633: x_msg_data := v_error_message_code;
634: x_return_status := v_return_status;
635: x_ret_code:=fnd_api.g_false;
636: --Bug# 6134740 Fix start
637: /*FND_MSG_PUB.add_exc_msg
638: (p_pkg_name =>G_PKG_NAME,
639: p_procedure_name => 'GET_RESOURCE_ORG_ID',
657: x_return_status => x_return_status,
658: x_msg_count => x_msg_count,
659: x_msg_data => x_msg_data);
660:
661: IF x_return_status<>fnd_api.g_ret_sts_success THEN
662: RETURN;
663: END IF;
664:
665: if x_ret_code=fnd_api.g_true then
661: IF x_return_status<>fnd_api.g_ret_sts_success THEN
662: RETURN;
663: END IF;
664:
665: if x_ret_code=fnd_api.g_true then
666: return;
667: end if;
668: ---------End of check if the user has resource authority------
669: */
674: -- Bug 2991490 Added the following condition.
675: -- If the logged in user is the resource on which the action is being performed, then grant privilege to the user.
676:
677: IF (p_resource_id = pa_resource_utils.get_resource_id(G_source_id) and l_fnd_function_test = true) THEN
678: x_ret_code:=fnd_api.g_true;
679: x_return_status:=fnd_api.g_ret_sts_success;
680: return;
681: END IF;
682:
675: -- If the logged in user is the resource on which the action is being performed, then grant privilege to the user.
676:
677: IF (p_resource_id = pa_resource_utils.get_resource_id(G_source_id) and l_fnd_function_test = true) THEN
678: x_ret_code:=fnd_api.g_true;
679: x_return_status:=fnd_api.g_ret_sts_success;
680: return;
681: END IF;
682:
683:
699: pa_debug.write_file( 'LOG', pa_debug.G_err_stage);
700: END IF;
701:
702: if l_fnd_function_test then
703: x_ret_code:=fnd_api.g_true;
704: x_return_status:=fnd_api.g_ret_sts_success;
705: return;
706: else
707: x_ret_code:=fnd_api.g_false;
700: END IF;
701:
702: if l_fnd_function_test then
703: x_ret_code:=fnd_api.g_true;
704: x_return_status:=fnd_api.g_ret_sts_success;
705: return;
706: else
707: x_ret_code:=fnd_api.g_false;
708: x_return_status:=fnd_api.g_ret_sts_success;
703: x_ret_code:=fnd_api.g_true;
704: x_return_status:=fnd_api.g_ret_sts_success;
705: return;
706: else
707: x_ret_code:=fnd_api.g_false;
708: x_return_status:=fnd_api.g_ret_sts_success;
709: end if;
710:
711: END IF;
704: x_return_status:=fnd_api.g_ret_sts_success;
705: return;
706: else
707: x_ret_code:=fnd_api.g_false;
708: x_return_status:=fnd_api.g_ret_sts_success;
709: end if;
710:
711: END IF;
712:
719: END IF;
720:
721: -- Bug 2970209 The following condition is not needed and it is unambiguoys, because we are defaulting x_ret_code as true
722: -- so this check will not be fired if v_resource_super_user <> Y
723: -- if x_ret_code =fnd_api.g_false then
724: v_resource_emp_id := get_resource_person_id(v_resource_id);
725: check_manager_relation
726: (v_resource_emp_id,
727: -- G_user_emp_id,
730: v_ret_code,
731: v_return_status,
732: v_error_message_code);
733:
734: if v_return_status <>fnd_api.g_ret_sts_success then
735: x_msg_count := x_msg_count+1;
736: x_msg_data := v_error_message_code;
737: x_return_status := v_return_status;
738: x_ret_code:=fnd_api.g_false;
734: if v_return_status <>fnd_api.g_ret_sts_success then
735: x_msg_count := x_msg_count+1;
736: x_msg_data := v_error_message_code;
737: x_return_status := v_return_status;
738: x_ret_code:=fnd_api.g_false;
739: FND_MSG_PUB.add_exc_msg
740: (p_pkg_name =>G_PKG_NAME,
741: p_procedure_name => 'CHECK_MANAGER_RELATION',
742: p_error_text => v_error_message_code);
742: p_error_text => v_error_message_code);
743: RETURN;
744: end if;
745:
746: if (v_ret_code=fnd_api.g_true and l_fnd_function_test = true)then
747: x_ret_code:=v_ret_code;
748: x_return_status:=v_return_status;
749: return;
750: else
747: x_ret_code:=v_ret_code;
748: x_return_status:=v_return_status;
749: return;
750: else
751: x_ret_code:=fnd_api.g_false;
752: x_return_status:=v_return_status;
753: end if;
754:
755: -- end if;
764: --dbms_output.put_line('p_privilege IS : ' || p_privilege);
765: --dbms_output.put_line('v_resource_org_id IS : ' || v_resource_org_id);
766: --dbms_output.put_line('get_grantee_key IS : ' || get_grantee_key);
767:
768: v_fnd_api_ret_code:=fnd_data_security.check_function(
769: p_api_version =>l_api_version,
770: p_function => p_privilege,
771: p_object_name => 'ORGANIZATION',
772: p_instance_pk1_value=>v_resource_org_id,
775: p_instance_pk4_value => NULL,
776: p_instance_pk5_value => NULL );
777: -- p_user_name => get_grantee_key); Commented for Bug 4498436.
778: --dbms_output.put_line('fter fnd_data_security.check_function');
779: --dbms_output.put_line('v_fnd_api_ret_code IS : ' || v_fnd_api_ret_code);
780:
781: if v_fnd_api_ret_code=fnd_api.g_ret_sts_error
782: OR v_fnd_api_ret_code=fnd_api.g_ret_sts_unexp_error then
783: x_ret_code:=fnd_api.g_false;
777: -- p_user_name => get_grantee_key); Commented for Bug 4498436.
778: --dbms_output.put_line('fter fnd_data_security.check_function');
779: --dbms_output.put_line('v_fnd_api_ret_code IS : ' || v_fnd_api_ret_code);
780:
781: if v_fnd_api_ret_code=fnd_api.g_ret_sts_error
782: OR v_fnd_api_ret_code=fnd_api.g_ret_sts_unexp_error then
783: x_ret_code:=fnd_api.g_false;
784: x_return_status:=fnd_api.g_ret_sts_unexp_error;
785: FND_MSG_PUB.Count_And_Get
778: --dbms_output.put_line('fter fnd_data_security.check_function');
779: --dbms_output.put_line('v_fnd_api_ret_code IS : ' || v_fnd_api_ret_code);
780:
781: if v_fnd_api_ret_code=fnd_api.g_ret_sts_error
782: OR v_fnd_api_ret_code=fnd_api.g_ret_sts_unexp_error then
783: x_ret_code:=fnd_api.g_false;
784: x_return_status:=fnd_api.g_ret_sts_unexp_error;
785: FND_MSG_PUB.Count_And_Get
786: (p_count => x_msg_count ,
779: --dbms_output.put_line('v_fnd_api_ret_code IS : ' || v_fnd_api_ret_code);
780:
781: if v_fnd_api_ret_code=fnd_api.g_ret_sts_error
782: OR v_fnd_api_ret_code=fnd_api.g_ret_sts_unexp_error then
783: x_ret_code:=fnd_api.g_false;
784: x_return_status:=fnd_api.g_ret_sts_unexp_error;
785: FND_MSG_PUB.Count_And_Get
786: (p_count => x_msg_count ,
787: p_data => x_msg_data
780:
781: if v_fnd_api_ret_code=fnd_api.g_ret_sts_error
782: OR v_fnd_api_ret_code=fnd_api.g_ret_sts_unexp_error then
783: x_ret_code:=fnd_api.g_false;
784: x_return_status:=fnd_api.g_ret_sts_unexp_error;
785: FND_MSG_PUB.Count_And_Get
786: (p_count => x_msg_count ,
787: p_data => x_msg_data
788: );
794: -- returns TRUE, then give access. At this time do not check if the
795: -- function is in the responsibility.
796: -- Hence commenting out check of l_fnd_function_test
797:
798: --if (v_fnd_api_ret_code=fnd_api.g_true and l_fnd_function_test = true)then
799: if (v_fnd_api_ret_code=fnd_api.g_true) then
800: x_ret_code:= fnd_api.g_true;
801: x_return_status:=fnd_api.g_ret_sts_success;
802: return;
795: -- function is in the responsibility.
796: -- Hence commenting out check of l_fnd_function_test
797:
798: --if (v_fnd_api_ret_code=fnd_api.g_true and l_fnd_function_test = true)then
799: if (v_fnd_api_ret_code=fnd_api.g_true) then
800: x_ret_code:= fnd_api.g_true;
801: x_return_status:=fnd_api.g_ret_sts_success;
802: return;
803: end if;
796: -- Hence commenting out check of l_fnd_function_test
797:
798: --if (v_fnd_api_ret_code=fnd_api.g_true and l_fnd_function_test = true)then
799: if (v_fnd_api_ret_code=fnd_api.g_true) then
800: x_ret_code:= fnd_api.g_true;
801: x_return_status:=fnd_api.g_ret_sts_success;
802: return;
803: end if;
804:
797:
798: --if (v_fnd_api_ret_code=fnd_api.g_true and l_fnd_function_test = true)then
799: if (v_fnd_api_ret_code=fnd_api.g_true) then
800: x_ret_code:= fnd_api.g_true;
801: x_return_status:=fnd_api.g_ret_sts_success;
802: return;
803: end if;
804:
805:
802: return;
803: end if;
804:
805:
806: x_ret_code:= fnd_api.g_false;
807: x_return_status:=fnd_api.g_ret_sts_success;
808: return;
809:
810: -----------End of check role based security-----------
803: end if;
804:
805:
806: x_ret_code:= fnd_api.g_false;
807: x_return_status:=fnd_api.g_ret_sts_success;
808: return;
809:
810: -----------End of check role based security-----------
811:
818: END IF;
819: fnd_msg_pub.add_exc_msg
820: (p_pkg_name => G_PKG_NAME,
821: p_procedure_name =>'CHECK_CONFIRM_ASMT' );
822: x_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
823: x_msg_count:=x_msg_count+1;
824: x_ret_code :=fnd_api.g_false;
825: raise;
826: end;
820: (p_pkg_name => G_PKG_NAME,
821: p_procedure_name =>'CHECK_CONFIRM_ASMT' );
822: x_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
823: x_msg_count:=x_msg_count+1;
824: x_ret_code :=fnd_api.g_false;
825: raise;
826: end;
827:
828:
861: AND TRUNC(resource_effective_start_date)<=TRUNC(NVL(p_start_date, resource_effective_start_date))
862: AND TRUNC(NVL(p_start_date,SYSDATE))<=TRUNC(resource_effective_end_date);
863:
864: BEGIN
865: x_return_status:=fnd_api.g_ret_sts_success;
866: v_person_id:=get_resource_person_id(p_resource_id);
867:
868: /* Added for Bug 6056112 */
869: SELECT nvl(future_term_wf_flag,'N')
878: IF (nvl(l_future_term_wf_flag,'N') = 'Y') THEN
879: OPEN c_fut_term_org_id;
880: FETCH c_fut_term_org_id INTO x_resource_org_id;
881: IF c_fut_term_org_id%NOTFOUND THEN
882: x_return_status := FND_API.G_RET_STS_ERROR;
883: x_error_message_code := 'PA_RESOURCE_ORG_AMBIGUOUS';
884: END IF ;
885: CLOSE c_fut_term_org_id;
886: ELSE
883: x_error_message_code := 'PA_RESOURCE_ORG_AMBIGUOUS';
884: END IF ;
885: CLOSE c_fut_term_org_id;
886: ELSE
887: x_return_status := FND_API.G_RET_STS_ERROR;
888: x_error_message_code := 'PA_RESOURCE_ORG_AMBIGUOUS';
889: END IF ;
890: /* End of Changes for Bug 6056112 */
891: END IF;
900: x_project_org_id out NOCOPY number, --File.Sql.39 bug 4440895
901: x_return_status out NOCOPY varchar2, --File.Sql.39 bug 4440895
902: x_error_message_code out NOCOPY varchar2 ) is --File.Sql.39 bug 4440895
903: begin
904: x_return_status:=fnd_api.g_ret_sts_success;
905: select CARRYING_OUT_ORGANIZATION_ID
906: into x_project_org_id
907: from pa_projects_all
908: where project_id=p_project_id;
907: from pa_projects_all
908: where project_id=p_project_id;
909: exception
910: WHEN NO_DATA_FOUND THEN
911: x_return_status := FND_API.G_RET_STS_ERROR;
912: x_error_message_code := 'PA_PROJ_ORG_AMBIGUOUS';
913: WHEN TOO_MANY_ROWS THEN
914: x_return_status := FND_API.G_RET_STS_ERROR;
915: x_error_message_code := 'PA_PROJ_ORG_AMBIGUOUS';
910: WHEN NO_DATA_FOUND THEN
911: x_return_status := FND_API.G_RET_STS_ERROR;
912: x_error_message_code := 'PA_PROJ_ORG_AMBIGUOUS';
913: WHEN TOO_MANY_ROWS THEN
914: x_return_status := FND_API.G_RET_STS_ERROR;
915: x_error_message_code := 'PA_PROJ_ORG_AMBIGUOUS';
916: WHEN OTHERS THEN
917: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
918: end;
913: WHEN TOO_MANY_ROWS THEN
914: x_return_status := FND_API.G_RET_STS_ERROR;
915: x_error_message_code := 'PA_PROJ_ORG_AMBIGUOUS';
916: WHEN OTHERS THEN
917: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
918: end;
919:
920:
921: --This procedure checks if the p_manager_id is a HR manager
938: l_mgr_str VARCHAR2(4000);
939: l_mgr_str1 VARCHAR2(20);
940:
941: BEGIN
942: x_return_status:=fnd_api.g_ret_sts_success;
943: x_ret_code:=fnd_api.g_false;
944:
945: BEGIN
946: /* Commented for bug 5916094
939: l_mgr_str1 VARCHAR2(20);
940:
941: BEGIN
942: x_return_status:=fnd_api.g_ret_sts_success;
943: x_ret_code:=fnd_api.g_false;
944:
945: BEGIN
946: /* Commented for bug 5916094
947: There can exists overlapping RM for a resource for same 2 resources for a same duration
1001:
1002: END LOOP;
1003:
1004: IF l_is_manager = 'Y' THEN
1005: x_ret_code:=fnd_api.g_true;
1006: END IF;
1007:
1008: EXCEPTION
1009: WHEN NO_DATA_FOUND THEN
1006: END IF;
1007:
1008: EXCEPTION
1009: WHEN NO_DATA_FOUND THEN
1010: x_ret_code:=fnd_api.g_false;
1011: END;
1012:
1013: EXCEPTION
1014: WHEN OTHERS THEN
1011: END;
1012:
1013: EXCEPTION
1014: WHEN OTHERS THEN
1015: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1016: x_ret_code:=fnd_api.g_false;
1017: raise;
1018: END;
1019:
1012:
1013: EXCEPTION
1014: WHEN OTHERS THEN
1015: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1016: x_ret_code:=fnd_api.g_false;
1017: raise;
1018: END;
1019:
1020:
1021: -- This API is called when a person is assigned to a new role
1022: -- from create_project_party
1023: PROCEDURE grant_role
1024: (
1025: p_commit IN VARCHAR2 := FND_API.G_FALSE,
1026: p_debug_mode in varchar2 default 'N',
1027: p_project_role_id IN number,
1028: p_object_name IN VARCHAR2,
1029: p_instance_type IN VARCHAR2,
1049: l_exp_error varchar2(1) := 'F';
1050: l_unexp_error varchar2(1) := 'F';
1051: l_parameter2 fnd_grants.parameter2%TYPE;
1052: l_role_status_menu_id_tbl SYSTEM.pa_num_tbl_type := null;
1053: l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
1054: l_error_message_code VARCHAR2(30);
1055:
1056: begin
1057:
1056: begin
1057:
1058: --dbms_output.put_line('pa_security_pvt.grant_role');
1059:
1060: x_return_status:=fnd_api.g_ret_sts_success;
1061: x_msg_count:=0;
1062: x_msg_data:=null;
1063:
1064: l_grantee_key:=get_grantee_key(p_source_type, p_party_id, 'Y'); -- Added 'Y' parameter for bug 3471913
1091: ,x_role_status_menu_id_tbl => l_role_status_menu_id_tbl
1092: ,x_return_status => l_return_status
1093: ,x_error_message_code => l_error_message_code);
1094:
1095: IF l_return_status <> fnd_api.g_ret_sts_success THEN
1096: PA_UTILS.Add_Message ( p_app_short_name => 'PA'
1097: ,p_msg_name => l_error_message_code );
1098:
1099: ELSE
1099: ELSE
1100:
1101: --dbms_output.put_line('default menu name: '||l_default_menu_name);
1102:
1103: IF l_default_menu_name is not null AND l_return_status = fnd_api.g_ret_sts_success THEN
1104:
1105: IF l_status_code_tbl IS NULL OR l_status_code_tbl.COUNT = 0 THEN
1106: l_parameter2 := 'NON_STATUS_BASED';
1107: ELSE
1136: );
1137:
1138: --dbms_output.put_line('grant_function: '||l_success);
1139:
1140: if l_success <> fnd_api.g_true then
1141: if l_error_code >0 then
1142: l_exp_error := 'T';
1143: else
1144: l_unexp_error := 'T';
1178:
1179: --dbms_output.put_line('grant_function: '||l_success);
1180:
1181:
1182: if l_success <> fnd_api.g_true then
1183: if l_error_code >0 then
1184: l_exp_error := 'T';
1185: else
1186: l_unexp_error := 'T';
1190: END LOOP;
1191: END IF; -- IF l_status_code_tbl IS NOT NULL OR l_status_code_tbl.COUNT > 0 THEN
1192:
1193: if l_exp_error = 'F' and l_unexp_error = 'F' then
1194: l_return_status:=fnd_api.g_ret_sts_success;
1195: else
1196: if l_unexp_error = 'T' then
1197: l_return_status:=fnd_api.g_ret_sts_unexp_error;
1198: else
1193: if l_exp_error = 'F' and l_unexp_error = 'F' then
1194: l_return_status:=fnd_api.g_ret_sts_success;
1195: else
1196: if l_unexp_error = 'T' then
1197: l_return_status:=fnd_api.g_ret_sts_unexp_error;
1198: else
1199: l_return_status:=fnd_api.g_ret_sts_error;
1200: end if;
1201: end if;
1195: else
1196: if l_unexp_error = 'T' then
1197: l_return_status:=fnd_api.g_ret_sts_unexp_error;
1198: else
1199: l_return_status:=fnd_api.g_ret_sts_error;
1200: end if;
1201: end if;
1202:
1203: END IF; -- IF l_default_menu_id is not null THEN
1209:
1210: --dbms_output.put_line('l_exp_error: '||l_exp_error||' l_unexp_error: '||l_unexp_error);
1211: x_return_status := l_return_status;
1212:
1213: IF l_return_status <> fnd_api.g_ret_sts_success THEN
1214: FND_MSG_PUB.Count_And_Get
1215: (p_count => x_msg_count ,
1216: p_data => x_msg_data
1217: );
1226: -- This API is called when Organization Authority is granted
1227: -- to specified resources.
1228: PROCEDURE grant_org_authority
1229: (
1230: p_commit IN VARCHAR2 := FND_API.G_FALSE,
1231: p_debug_mode in varchar2 default 'N',
1232: p_project_role_id IN number,
1233: p_menu_name in varchar2,
1234: p_object_name IN VARCHAR2,
1256: --Clear the global PL/SQL message table
1257: -- FND_MSG_PUB.initialize; commented the call for Bug 2887390
1258:
1259: --dbms_output.put_line('inside grant role');
1260: x_return_status:=fnd_api.g_ret_sts_success;
1261: x_msg_count:=0;
1262: x_msg_data:=null;
1263: if p_menu_name is null then
1264: v_menu_name:=get_menu_name(p_project_role_id);
1297: x_grant_guid =>x_grant_guid,
1298: x_success =>l_success,
1299: x_errorcode =>l_error_code
1300: ) ;
1301: if l_success=fnd_api.g_true then
1302: x_return_status:=fnd_api.g_ret_sts_success;
1303: else
1304: if l_error_code >0 then
1305: x_return_status:=fnd_api.g_ret_sts_error;
1298: x_success =>l_success,
1299: x_errorcode =>l_error_code
1300: ) ;
1301: if l_success=fnd_api.g_true then
1302: x_return_status:=fnd_api.g_ret_sts_success;
1303: else
1304: if l_error_code >0 then
1305: x_return_status:=fnd_api.g_ret_sts_error;
1306: else
1301: if l_success=fnd_api.g_true then
1302: x_return_status:=fnd_api.g_ret_sts_success;
1303: else
1304: if l_error_code >0 then
1305: x_return_status:=fnd_api.g_ret_sts_error;
1306: else
1307: x_return_status:=fnd_api.g_ret_sts_unexp_error;
1308: end if;
1309: FND_MSG_PUB.Count_And_Get
1303: else
1304: if l_error_code >0 then
1305: x_return_status:=fnd_api.g_ret_sts_error;
1306: else
1307: x_return_status:=fnd_api.g_ret_sts_unexp_error;
1308: end if;
1309: FND_MSG_PUB.Count_And_Get
1310: (p_count => x_msg_count ,
1311: p_data => x_msg_data
1318: end;
1319:
1320: PROCEDURE revoke_grant
1321: (
1322: p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
1323: p_debug_mode in varchar2 default 'N',
1324: p_grant_guid in raw,
1325: x_return_status OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
1326: x_msg_count OUT NOCOPY NUMBER , --File.Sql.39 bug 4440895
1333:
1334: --Clear the global PL/SQL message table
1335: FND_MSG_PUB.initialize;
1336:
1337: x_return_status :=fnd_api.g_ret_sts_success;
1338: x_msg_count:=0;
1339: x_msg_data :=null;
1340: --dbms_output.put_line('inside pa revoke_grants');
1341: fnd_grants_pkg.revoke_grant
1345: x_success =>l_success,
1346: x_errorcode =>l_error_code
1347: );
1348:
1349: if l_success=fnd_api.g_true then
1350: x_return_status:=fnd_api.g_ret_sts_success;
1351: else
1352: if l_error_code >0 then
1353: x_return_status:=fnd_api.g_ret_sts_error;
1346: x_errorcode =>l_error_code
1347: );
1348:
1349: if l_success=fnd_api.g_true then
1350: x_return_status:=fnd_api.g_ret_sts_success;
1351: else
1352: if l_error_code >0 then
1353: x_return_status:=fnd_api.g_ret_sts_error;
1354: else
1349: if l_success=fnd_api.g_true then
1350: x_return_status:=fnd_api.g_ret_sts_success;
1351: else
1352: if l_error_code >0 then
1353: x_return_status:=fnd_api.g_ret_sts_error;
1354: else
1355: x_return_status:=fnd_api.g_ret_sts_unexp_error;
1356: end if;
1357: FND_MSG_PUB.Count_And_Get
1351: else
1352: if l_error_code >0 then
1353: x_return_status:=fnd_api.g_ret_sts_error;
1354: else
1355: x_return_status:=fnd_api.g_ret_sts_unexp_error;
1356: end if;
1357: FND_MSG_PUB.Count_And_Get
1358: (p_count => x_msg_count ,
1359: p_data => x_msg_data
1366:
1367:
1368: PROCEDURE revoke_role
1369: (
1370: p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
1371: p_debug_mode in varchar2 default 'N',
1372: p_project_role_id IN number,
1373: p_menu_name in varchar2,
1374: p_object_name IN VARCHAR2,
1431: p_grant_guid => l_grant_guid,
1432: x_success => l_success,
1433: x_errorcode => l_error_code);
1434:
1435: IF l_success=fnd_api.g_true THEN
1436: x_return_status:=fnd_api.g_ret_sts_success;
1437: ELSE
1438: IF l_error_code>0 THEN
1439: x_return_status:=fnd_api.g_ret_sts_error;
1432: x_success => l_success,
1433: x_errorcode => l_error_code);
1434:
1435: IF l_success=fnd_api.g_true THEN
1436: x_return_status:=fnd_api.g_ret_sts_success;
1437: ELSE
1438: IF l_error_code>0 THEN
1439: x_return_status:=fnd_api.g_ret_sts_error;
1440: ELSE
1435: IF l_success=fnd_api.g_true THEN
1436: x_return_status:=fnd_api.g_ret_sts_success;
1437: ELSE
1438: IF l_error_code>0 THEN
1439: x_return_status:=fnd_api.g_ret_sts_error;
1440: ELSE
1441: x_return_status:=fnd_api.g_ret_sts_unexp_error;
1442: END IF;
1443: FND_MSG_PUB.Count_And_Get
1437: ELSE
1438: IF l_error_code>0 THEN
1439: x_return_status:=fnd_api.g_ret_sts_error;
1440: ELSE
1441: x_return_status:=fnd_api.g_ret_sts_unexp_error;
1442: END IF;
1443: FND_MSG_PUB.Count_And_Get
1444: (p_count => x_msg_count ,
1445: p_data => x_msg_data
1446: );
1447: RETURN;
1448: END IF;
1449:
1450: IF FND_API.To_Boolean( p_commit ) THEN
1451: COMMIT WORK;
1452: END IF;
1453:
1454: EXCEPTION
1453:
1454: EXCEPTION
1455: WHEN OTHERS THEN
1456: ROLLBACK TO revoke_role_PUB;
1457: x_return_status := FND_API.g_ret_sts_unexp_error ;
1458: IF FND_MSG_PUB.Check_Msg_Level
1459: (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1460: THEN
1461: FND_MSG_PUB.Add_Exc_Msg
1470: end;
1471:
1472: -- obsoleted API
1473: PROCEDURE update_role
1474: ( p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
1475: p_debug_mode in varchar2 default 'N',
1476: p_grant_guid in raw,
1477: p_project_role_id_old IN number default null,
1478: p_object_name_old IN VARCHAR2 default null,
1492: begin
1493: --Clear the global PL/SQL message table
1494: FND_MSG_PUB.initialize;
1495:
1496: x_return_status:=fnd_api.g_ret_sts_success;
1497: x_msg_count:=0;
1498: x_msg_data:=null;
1499: fnd_grants_pkg.update_grant
1500: (
1503: p_start_date =>p_start_date_new,
1504: p_end_date =>p_end_date_new,
1505: x_success =>l_success
1506: ) ;
1507: if l_success=fnd_api.g_true then
1508: x_return_status:=fnd_api.g_ret_sts_success;
1509: else
1510: x_return_status:=fnd_api.g_ret_sts_unexp_error;
1511: FND_MSG_PUB.Count_And_Get
1504: p_end_date =>p_end_date_new,
1505: x_success =>l_success
1506: ) ;
1507: if l_success=fnd_api.g_true then
1508: x_return_status:=fnd_api.g_ret_sts_success;
1509: else
1510: x_return_status:=fnd_api.g_ret_sts_unexp_error;
1511: FND_MSG_PUB.Count_And_Get
1512: (p_count => x_msg_count ,
1506: ) ;
1507: if l_success=fnd_api.g_true then
1508: x_return_status:=fnd_api.g_ret_sts_success;
1509: else
1510: x_return_status:=fnd_api.g_ret_sts_unexp_error;
1511: FND_MSG_PUB.Count_And_Get
1512: (p_count => x_msg_count ,
1513: p_data => x_msg_data
1514: );
1521:
1522:
1523: PROCEDURE lock_grant
1524: (
1525: p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
1526: p_debug_mode in varchar2 default 'N',
1527: p_grant_guid in raw,
1528: p_project_role_id_old IN number default null,
1529: p_object_name_old IN VARCHAR2 default null,
1595: ) ;
1596: x_return_status:='S';
1597: exception
1598: when others then
1599: x_return_status:=fnd_api.g_ret_sts_unexp_error;
1600: raise;
1601: end;
1602:
1603:
1960:
1961: pa_debug.Init_err_stack ( 'Check_Access_Exist');
1962:
1963: Init_global;
1964: x_ret_code := FND_API.G_TRUE;
1965: x_return_status := FND_API.G_RET_STS_SUCCESS;
1966: x_msg_count := 0;
1967: x_msg_data := NULL;
1968:
1961: pa_debug.Init_err_stack ( 'Check_Access_Exist');
1962:
1963: Init_global;
1964: x_ret_code := FND_API.G_TRUE;
1965: x_return_status := FND_API.G_RET_STS_SUCCESS;
1966: x_msg_count := 0;
1967: x_msg_data := NULL;
1968:
1969: ------- Check for License
1967: x_msg_data := NULL;
1968:
1969: ------- Check for License
1970: IF pa_product_install_utils.check_function_licensed(p_privilege) <> 'Y' THEN
1971: x_ret_code:= fnd_api.g_false;
1972: x_return_status:=fnd_api.g_ret_sts_success;
1973: RETURN;
1974: END IF;
1975: ------- End check for License
1968:
1969: ------- Check for License
1970: IF pa_product_install_utils.check_function_licensed(p_privilege) <> 'Y' THEN
1971: x_ret_code:= fnd_api.g_false;
1972: x_return_status:=fnd_api.g_ret_sts_success;
1973: RETURN;
1974: END IF;
1975: ------- End check for License
1976:
1993: pa_debug.write_file('check_access_exist: ' || 'LOG', pa_debug.G_err_stage);
1994: END IF;
1995: IF fnd_function.test(p_privilege) THEN
1996: --Adding the below code for the bug 3137696
1997: x_ret_code := FND_API.G_TRUE;
1998: x_return_status := FND_API.G_RET_STS_SUCCESS;
1999: RETURN;
2000: --Commented the below code for the bug 3137696
2001: /*IF p_object_name='PA_PROJECTS' THEN
1994: END IF;
1995: IF fnd_function.test(p_privilege) THEN
1996: --Adding the below code for the bug 3137696
1997: x_ret_code := FND_API.G_TRUE;
1998: x_return_status := FND_API.G_RET_STS_SUCCESS;
1999: RETURN;
2000: --Commented the below code for the bug 3137696
2001: /*IF p_object_name='PA_PROJECTS' THEN
2002: -- Bug 1571014, faulty dynamic sql
2063: p_statement_type => 'EXISTS',
2064: x_predicate => l_predicate,
2065: x_return_status => x_return_status);
2066:
2067: -- IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
2068: IF x_return_status<>'T' AND x_return_status<>'F' THEN
2069: x_ret_code := FND_API.G_FALSE;
2070: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2071: FND_MSG_PUB.Count_And_Get
2065: x_return_status => x_return_status);
2066:
2067: -- IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
2068: IF x_return_status<>'T' AND x_return_status<>'F' THEN
2069: x_ret_code := FND_API.G_FALSE;
2070: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2071: FND_MSG_PUB.Count_And_Get
2072: (p_count => x_msg_count ,
2073: p_data => x_msg_data);
2066:
2067: -- IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
2068: IF x_return_status<>'T' AND x_return_status<>'F' THEN
2069: x_ret_code := FND_API.G_FALSE;
2070: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2071: FND_MSG_PUB.Count_And_Get
2072: (p_count => x_msg_count ,
2073: p_data => x_msg_data);
2074: RETURN;
2072: (p_count => x_msg_count ,
2073: p_data => x_msg_data);
2074: RETURN;
2075: ELSIF x_return_status='F' THEN
2076: x_ret_code := FND_API.G_FALSE;
2077: x_return_status := FND_API.G_RET_STS_SUCCESS;
2078: x_msg_count := 0;
2079: x_msg_data := '';
2080: RETURN;
2073: p_data => x_msg_data);
2074: RETURN;
2075: ELSIF x_return_status='F' THEN
2076: x_ret_code := FND_API.G_FALSE;
2077: x_return_status := FND_API.G_RET_STS_SUCCESS;
2078: x_msg_count := 0;
2079: x_msg_data := '';
2080: RETURN;
2081: END IF;
2092: END IF;
2093: OPEN l_cur FOR l_predicate;
2094: FETCH l_cur INTO l_dummy;
2095: IF l_cur%NOTFOUND THEN
2096: x_ret_code := FND_API.G_FALSE;
2097: x_return_status := FND_API.G_RET_STS_SUCCESS;
2098: x_msg_count := 0;
2099: x_msg_data := '';
2100: CLOSE l_cur; -- Bug #2994870: closing the cursor.
2093: OPEN l_cur FOR l_predicate;
2094: FETCH l_cur INTO l_dummy;
2095: IF l_cur%NOTFOUND THEN
2096: x_ret_code := FND_API.G_FALSE;
2097: x_return_status := FND_API.G_RET_STS_SUCCESS;
2098: x_msg_count := 0;
2099: x_msg_data := '';
2100: CLOSE l_cur; -- Bug #2994870: closing the cursor.
2101: RETURN;
2107: pa_debug.G_err_stage := 'exceptions raised';
2108: IF G_debug_flag = 'Y' THEN
2109: pa_debug.write_file('check_access_exist: ' || 'LOG', pa_debug.G_err_stage);
2110: END IF;
2111: x_ret_code := FND_API.G_FALSE;
2112: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2113: FND_MSG_PUB.Count_And_Get
2114: (p_count => x_msg_count ,
2115: p_data => x_msg_data);
2108: IF G_debug_flag = 'Y' THEN
2109: pa_debug.write_file('check_access_exist: ' || 'LOG', pa_debug.G_err_stage);
2110: END IF;
2111: x_ret_code := FND_API.G_FALSE;
2112: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2113: FND_MSG_PUB.Count_And_Get
2114: (p_count => x_msg_count ,
2115: p_data => x_msg_data);
2116:
2170:
2171: -- This API is called when the default Menu is changed
2172: -- in Roles form for existing roles which are in use.
2173: PROCEDURE update_menu
2174: ( p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
2175: p_debug_mode in varchar2 default 'N',
2176: p_project_role_id IN number,
2177: p_menu_id IN number,
2178: x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
2193: AND (parameter2 = 'NON_STATUS_BASED' OR parameter2 = 'DEFAULT')
2194: AND instance_type = 'SET'
2195: AND instance_set_id = G_project_roles_ins_set_id;
2196:
2197: x_return_status:=fnd_api.g_ret_sts_success;
2198:
2199: EXCEPTION
2200: when others then
2201: x_return_status:=fnd_api.g_ret_sts_unexp_error;
2197: x_return_status:=fnd_api.g_ret_sts_success;
2198:
2199: EXCEPTION
2200: when others then
2201: x_return_status:=fnd_api.g_ret_sts_unexp_error;
2202: fnd_msg_pub.add_exc_msg
2203: (p_pkg_name => 'PA_SECURITY_PVT',
2204: p_procedure_name => 'UPDATE_MENU',
2205: p_error_text => SQLCODE);
2210:
2211: -- This API is called when Enforce Role-based Security checkbox
2212: -- is unchecked in Roles form for existing roles which are in use.
2213: PROCEDURE revoke_role_based_sec
2214: ( p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
2215: p_debug_mode in varchar2 default 'N',
2216: p_project_role_id IN number,
2217: x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
2218: x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
2230: WHERE parameter1 = to_char(p_project_role_id)
2231: AND instance_type = 'SET'
2232: AND instance_set_id = G_project_roles_ins_set_id;
2233:
2234: x_return_status:=fnd_api.g_ret_sts_success;
2235:
2236: EXCEPTION
2237: when others then
2238: x_return_status:=fnd_api.g_ret_sts_unexp_error;
2234: x_return_status:=fnd_api.g_ret_sts_success;
2235:
2236: EXCEPTION
2237: when others then
2238: x_return_status:=fnd_api.g_ret_sts_unexp_error;
2239: fnd_msg_pub.add_exc_msg
2240: (p_pkg_name => 'PA_SECURITY_PVT',
2241: p_procedure_name => 'REVOKE_ROLE_BASED_SEC',
2242: p_error_text => SQLCODE);
2246:
2247: -- This API is called when Enforce Role-based Security checkbox
2248: -- is checked in Roles form for existing roles which are in use.
2249: PROCEDURE grant_role_based_sec
2250: ( p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
2251: p_debug_mode in varchar2 default 'N',
2252: p_project_role_id IN number,
2253: x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
2254: x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
2267: l_menu_name_tbl SYSTEM.pa_varchar2_30_tbl_type := null;
2268: l_exp_error varchar2(1) := 'F';
2269: l_unexp_error varchar2(1) := 'F';
2270: l_parameter2 fnd_grants.parameter2%TYPE;
2271: l_return_status varchar2(1) := fnd_api.g_ret_sts_success;
2272: l_error_message_code VARCHAR2(30);
2273: l_grant_guid fnd_grants.grant_guid%TYPE;
2274: l_role_status_menu_id_tbl SYSTEM.pa_num_tbl_type := null;
2275:
2314: ,x_error_message_code => l_error_message_code);
2315:
2316: --dbms_output.put_line('Get_Role_Status_Menus:'||l_return_status);
2317:
2318: IF l_return_status <> fnd_api.g_ret_sts_success THEN
2319: PA_UTILS.Add_Message ( p_app_short_name => 'PA'
2320: ,p_msg_name => l_error_message_code );
2321: ELSE
2322:
2376: x_success => l_success,
2377: x_errorcode => l_error_code
2378: );
2379:
2380: if l_success <> fnd_api.g_true then
2381: if l_error_code >0 then
2382: l_exp_error := 'T';
2383: else
2384: l_unexp_error := 'T';
2413: x_success => l_success,
2414: x_errorcode => l_error_code
2415: ) ;
2416:
2417: if l_success <> fnd_api.g_true then
2418: if l_error_code >0 then
2419: l_exp_error := 'T';
2420: else
2421: l_unexp_error := 'T';
2428:
2429: END LOOP; -- FOR res in get_resources_on_role LOOP
2430:
2431: if l_exp_error = 'T' then
2432: l_return_status:=fnd_api.g_ret_sts_error;
2433: elsif l_unexp_error = 'T' then
2434: l_return_status:=fnd_api.g_ret_sts_unexp_error;
2435: else
2436: l_return_status:=fnd_api.g_ret_sts_success;
2430:
2431: if l_exp_error = 'T' then
2432: l_return_status:=fnd_api.g_ret_sts_error;
2433: elsif l_unexp_error = 'T' then
2434: l_return_status:=fnd_api.g_ret_sts_unexp_error;
2435: else
2436: l_return_status:=fnd_api.g_ret_sts_success;
2437:
2438: END IF;
2432: l_return_status:=fnd_api.g_ret_sts_error;
2433: elsif l_unexp_error = 'T' then
2434: l_return_status:=fnd_api.g_ret_sts_unexp_error;
2435: else
2436: l_return_status:=fnd_api.g_ret_sts_success;
2437:
2438: END IF;
2439:
2440: END IF; -- l_return_status = 'S'
2440: END IF; -- l_return_status = 'S'
2441:
2442: x_return_status:=l_return_status;
2443:
2444: IF l_return_status <> fnd_api.g_ret_sts_success THEN
2445: FND_MSG_PUB.Count_And_Get
2446: (p_count => x_msg_count ,
2447: p_data => x_msg_data
2448: );
2449: END IF;
2450:
2451: EXCEPTION
2452: when others then
2453: x_return_status:=fnd_api.g_ret_sts_unexp_error;
2454: fnd_msg_pub.add_exc_msg
2455: (p_pkg_name => 'PA_SECURITY_PVT',
2456: p_procedure_name => 'GRANT_ROLE_BASED_SEC',
2457: p_error_text => SQLCODE);
2464:
2465: -- This API is called when Status Level is changed
2466: -- in Roles form for existing roles which are in use.
2467: PROCEDURE revoke_status_based_sec
2468: ( p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
2469: p_debug_mode in varchar2 default 'N',
2470: p_project_role_id IN number,
2471: x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
2472: x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
2485: AND (parameter2 = 'USER' OR parameter2 = 'SYSTEM')
2486: AND instance_type = 'SET'
2487: AND instance_set_id = G_project_roles_ins_set_id;
2488:
2489: x_return_status:=fnd_api.g_ret_sts_success;
2490:
2491: EXCEPTION
2492: when others then
2493: x_return_status:=fnd_api.g_ret_sts_unexp_error;
2489: x_return_status:=fnd_api.g_ret_sts_success;
2490:
2491: EXCEPTION
2492: when others then
2493: x_return_status:=fnd_api.g_ret_sts_unexp_error;
2494: fnd_msg_pub.add_exc_msg
2495: (p_pkg_name => 'PA_SECURITY_PVT',
2496: p_procedure_name => 'REVOKE_STATUS_BASED_SEC',
2497: p_error_text => SQLCODE);
2500:
2501: -- This API is called when status/menu under Project Status is changed
2502: -- in Roles form for existing roles which are in use.
2503: PROCEDURE update_status_based_sec
2504: ( p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
2505: p_debug_mode in varchar2 default 'N',
2506: p_project_role_id IN number,
2507: p_status_level IN pa_project_role_types_b.status_level%TYPE,
2508: p_new_status_code_tbl IN SYSTEM.pa_varchar2_30_tbl_type := null,
2544: l_grantee_key fnd_grants.grantee_key%TYPE;
2545: l_status_menu_count NUMBER := 0;
2546: l_parameter2 fnd_grants.parameter2%TYPE;
2547: l_opp_param2 fnd_grants.parameter2%TYPE;
2548: l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
2549: l_grant_guid fnd_grants.grant_guid%TYPE;
2550: l_success varchar2(1);
2551: l_exp_error varchar2(1);
2552: l_unexp_error varchar2(1);
2593: x_success => l_success,
2594: x_errorcode => l_error_code
2595: ) ;
2596:
2597: if l_success <> fnd_api.g_true then
2598: if l_error_code >0 then
2599: l_exp_error := 'T';
2600: else
2601: l_unexp_error := 'T';
2605: END LOOP; -- p_new_status_code_tbl
2606: END LOOP; -- get_resources_on_role
2607:
2608: if l_exp_error = 'T' then
2609: l_return_status:=fnd_api.g_ret_sts_error;
2610: elsif l_unexp_error = 'T' then
2611: l_return_status:=fnd_api.g_ret_sts_unexp_error;
2612: else
2613: l_return_status:=fnd_api.g_ret_sts_success;
2607:
2608: if l_exp_error = 'T' then
2609: l_return_status:=fnd_api.g_ret_sts_error;
2610: elsif l_unexp_error = 'T' then
2611: l_return_status:=fnd_api.g_ret_sts_unexp_error;
2612: else
2613: l_return_status:=fnd_api.g_ret_sts_success;
2614: end if;
2615:
2609: l_return_status:=fnd_api.g_ret_sts_error;
2610: elsif l_unexp_error = 'T' then
2611: l_return_status:=fnd_api.g_ret_sts_unexp_error;
2612: else
2613: l_return_status:=fnd_api.g_ret_sts_success;
2614: end if;
2615:
2616: END IF; -- p_new_status_code_tbl.COUNT > 0 THEN
2617:
2614: end if;
2615:
2616: END IF; -- p_new_status_code_tbl.COUNT > 0 THEN
2617:
2618: IF l_return_status = fnd_api.g_ret_sts_success THEN
2619:
2620: -- 2. delete obsolete records from FND_GRANTS
2621: IF p_del_status_code_tbl IS NOT NULL AND p_del_status_code_tbl.COUNT > 0 THEN
2622:
2671: AND parameter2 = l_opp_param2
2672: AND instance_type = 'SET'
2673: AND instance_set_id = G_project_roles_ins_set_id;
2674:
2675: END IF; -- l_return_status = fnd_api.g_ret_sts_success THEN
2676:
2677: x_return_status := l_return_status;
2678:
2679: EXCEPTION
2677: x_return_status := l_return_status;
2678:
2679: EXCEPTION
2680: when others then
2681: x_return_status:=fnd_api.g_ret_sts_unexp_error;
2682: fnd_msg_pub.add_exc_msg
2683: (p_pkg_name => 'PA_SECURITY_PVT',
2684: p_procedure_name => 'UPDATE_STATUS_BASED_SEC',
2685: p_error_text => SQLCODE);