1 PACKAGE BODY CS_SR_SECURITY_UTIL AS
2 /* $Header: csusecb.pls 120.2 2005/10/12 15:11:38 spusegao noship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CS_SR_SECURITY_UTIL';
5
6 -- This function returns back the where predicate that gets appended to select
7 -- statements on the 'cs_incidents_b_sec' secure view. The where predicate is
8 -- retrieved from the AOL grants model by invoking a FND API.
9 FUNCTION SET_SR_ACCESS (
10 object_schema IN VARCHAR2,
11 object_name IN VARCHAR2 )
12 RETURN VARCHAR2
13 IS
14 lx_return_status VARCHAR2(3);
15 lx_predicate VARCHAR2(4000);
16 BEGIN
17
18 -- invoke the AOL API to return back the where predicate that is defined
19 -- as a Grant
20 -- Refer to script AFSCDSCS.pls for instructions on how to use the API.
21 fnd_data_security.get_security_predicate (
22 p_api_version => 1.0,
23 p_function => 'CS_SR_VIEW',
24 p_object_name => 'CS_SERVICE_REQUEST',
25 p_table_alias => 'CS_INCIDENTS_B_SEC',
26 -- p_grant_instance_type => 'SET',
27 -- p_user_name => 'GLOBAL',
28 x_predicate => lx_predicate,
29 x_return_status => lx_return_status );
30
31 if ( lx_return_status = 'T' ) then
32 return ( lx_predicate );
33 else
34 -- if a predicate is not returned back, then return NULL
35 IF lx_predicate IS NOT NULL THEN
36 return lx_predicate ;
37 ELSE
38 return '1=2';
39 -- return null;
40 END IF ;
41
42 -- if error status is returned from the FND API, clear the message
43 -- stack.(The FND API pushes a msg. into the stack incase of an error)
44 FND_MESSAGE.CLEAR();
45 end if;
46
47 EXCEPTION
48 when others then
49 -- return null if any unknown exception is raised
50 return '1=2';
51
52 END SET_SR_ACCESS ;
53
54 -- This function returns back the where predicate that gets appended to select
55 -- statements on the 'cs_sr_types_select_b' secure view. The where predicate
56 -- is retrieved from the AOL grants model by invoking a FND API.
57 FUNCTION SET_SR_TYPE_ACCESS (
58 object_schema IN VARCHAR2,
59 object_name IN VARCHAR2 )
60 RETURN VARCHAR2
61 IS
62 lx_return_status VARCHAR2(3);
63 lx_predicate VARCHAR2(4000);
64 BEGIN
65
66 -- invoke the AOL API to return back the where predicate that is defined
67 -- as a Grant
68 -- Refer to script AFSCDSCS.pls for instructions on how to use the API.
69 fnd_data_security.get_security_predicate (
70 p_api_version => 1.0,
71 p_function => 'CS_SR_TYPES_SELECT_SEC',
72 p_object_name => 'CS_SR_TYPE',
73 p_grant_instance_type => 'SET',
74 p_user_name => 'GLOBAL',
75 x_predicate => lx_predicate,
76 x_return_status => lx_return_status );
77
78 if ( lx_return_status = 'T' ) then
79 return ( lx_predicate );
80 else
81 -- if a predicate is not returned back, then return NULL
82 IF lx_predicate IS NOT NULL THEN
83 return lx_predicate ;
84 ELSE
85 return null;
86 END IF ;
87
88 -- if error status is returned from the FND API, clear the message
89 -- stack.(The FND API pushes a msg. into the stack incase of an error)
90 FND_MESSAGE.CLEAR();
91 end if;
92
93 EXCEPTION
94 when others then
95 -- return null if any unknown exception is raised
96 return null;
97
98 END SET_SR_TYPE_ACCESS ;
99
100 -- This function returns back the where predicate that gets appended to select
101 -- statements on the 'cs_jtf_resource_select_sec' secure view. The where
102 -- predicate is retrieved from the AOL grants model by invoking a FND API.
103 FUNCTION SET_SR_RESOURCE_ACCESS (
104 object_schema IN VARCHAR2,
105 object_name IN VARCHAR2 )
106 RETURN VARCHAR2
107 IS
108 lx_return_status VARCHAR2(3);
109 lx_predicate VARCHAR2(4000);
110 BEGIN
111
112 -- invoke the AOL API to return back the where predicate that is defined
113 -- as a Grant
114 -- Refer to script AFSCDSCS.pls for instructions on how to use the API.
115 fnd_data_security.get_security_predicate (
116 p_api_version => 1.0,
117 p_function => 'CS_JTF_RS_RESOURCE_EXTNS_SEC',
118 p_object_name => 'JTF_TASK_RESOURCE',
119 p_grant_instance_type => 'SET',
120 p_user_name => 'GLOBAL',
121 x_predicate => lx_predicate,
125 return ( lx_predicate );
122 x_return_status => lx_return_status );
123
124 if ( lx_return_status = 'T' ) then
126 else
127 -- if a predicate is not returned back, then return NULL
128 IF lx_predicate IS NOT NULL THEN
129 return lx_predicate ;
130 ELSE
131 return null;
132 END IF ;
133
134 -- if error status is returned from the FND API, clear the message
135 -- stack.(The FND API pushes a msg. into the stack incase of an error)
136 FND_MESSAGE.CLEAR();
137 end if;
138
139 EXCEPTION
140 when others then
141 -- return null if any unknown exception is raised
142 return null;
143
144 END SET_SR_RESOURCE_ACCESS ;
145
146 -- This function returns back the where predicate that gets appended to select
147 -- statements on the 'cs_sr_access_resp_sec' secure view. The where predicate is
148 -- retrieved from the AOL grants model by invoking a FND API.
149 FUNCTION SET_SR_ACCESS_RESP (
150 object_schema IN VARCHAR2,
151 object_name IN VARCHAR2 )
152 RETURN VARCHAR2
153 IS
154 lx_return_status VARCHAR2(3);
155 lx_predicate VARCHAR2(4000);
156 BEGIN
157
158 -- invoke the AOL API to return back the where predicate that is defined
159 -- as a Grant
160 -- Refer to script AFSCDSCS.pls for instructions on how to use the API.
161 fnd_data_security.get_security_predicate (
162 p_api_version => 1.0,
163 p_function => 'CS_SR_ACCESS_RESP_SEC',
164 p_object_name => 'CS_SERVICE_REQUEST',
165 p_grant_instance_type => 'SET',
166 p_user_name => 'GLOBAL',
167 x_predicate => lx_predicate,
168 x_return_status => lx_return_status );
169
170 if ( lx_return_status = 'T' ) then
171 return ( lx_predicate );
172 else
173 -- return back a success status
174 IF lx_predicate IS NOT NULL THEN
175 return lx_predicate ;
176 ELSE
177 return null;
178 END IF ;
179
180 -- if error status is returned from the FND API, clear the message
181 -- stack.(The FND API pushes a msg. into the stack incase of an error)
182 FND_MESSAGE.CLEAR();
183 end if;
184
185 EXCEPTION
186 when others then
187 -- return null if any unknown exception is raised
188 return null;
189
190 END SET_SR_ACCESS_RESP ;
191
192 -- Procedure to enable Service security VPD policies. This proc. is invoked
193 -- from the Service Security - System Options OA page.
194 PROCEDURE ENABLE_SR_POLICIES (
195 x_return_status OUT NOCOPY VARCHAR2 )
196 IS
197 l_api_name_full VARCHAR2(40) := g_pkg_name || '.ENABLE_SR_POLICIES';
198 BEGIN
199 x_return_status := FND_API.G_RET_STS_SUCCESS;
200
201 dbms_rls.enable_policy(
202 object_schema => 'APPS',
203 object_name => 'CS_INCIDENTS_B_SEC',
204 policy_name => 'CS_SR_SEC_SR_ACCESS',
205 enable => TRUE );
206
207 dbms_rls.enable_policy(
208 object_schema => 'APPS',
209 object_name => 'CS_SR_TYPES_SELECT_SEC',
210 policy_name => 'CS_SR_SEC_SRTYPE_ACCESS',
211 enable => TRUE );
212
213 dbms_rls.enable_policy(
214 object_schema => 'APPS',
215 object_name => 'CS_JTF_RS_RESOURCE_EXTNS_SEC',
216 policy_name => 'CS_SR_JTF_RESOURCE_ACCESS',
217 enable => TRUE );
218
219 dbms_rls.enable_policy(
220 object_schema => 'APPS',
221 object_name => 'CS_SR_ACCESS_RESP_SEC',
222 policy_name => 'CS_SR_SEC_RESP_ACCESS',
223 enable => TRUE );
224
225 EXCEPTION
226 WHEN OTHERS THEN
227 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
228 fnd_message.set_name ('CS', 'CS_API_SR_UNKNOWN_ERROR');
229 fnd_message.set_token ('P_TEXT',l_api_name_full||'-'||SQLERRM);
230 fnd_msg_pub.ADD;
231
232 END ENABLE_SR_POLICIES;
233
234 -- Procedure to disable Service security VPD policies. This proc. is invoked
235 -- from the Service Security - System Options OA page.
236 PROCEDURE DISABLE_SR_POLICIES (
237 x_return_status OUT NOCOPY VARCHAR2 )
238 IS
239 l_api_name_full VARCHAR2(40) := g_pkg_name || '.DISABLE_SR_POLICIES';
240 BEGIN
241 x_return_status := FND_API.G_RET_STS_SUCCESS;
242
243 dbms_rls.enable_policy(
244 object_schema => 'APPS',
245 object_name => 'CS_INCIDENTS_B_SEC',
246 policy_name => 'CS_SR_SEC_SR_ACCESS',
247 enable => FALSE );
248
249 dbms_rls.enable_policy(
250 object_schema => 'APPS',
251 object_name => 'CS_SR_TYPES_SELECT_SEC',
252 policy_name => 'CS_SR_SEC_SRTYPE_ACCESS',
253 enable => FALSE );
254
255 dbms_rls.enable_policy(
256 object_schema => 'APPS',
257 object_name => 'CS_JTF_RS_RESOURCE_EXTNS_SEC',
258 policy_name => 'CS_SR_JTF_RESOURCE_ACCESS',
259 enable => FALSE );
260
261 dbms_rls.enable_policy(
262 object_schema => 'APPS',
263 object_name => 'CS_SR_ACCESS_RESP_SEC',
264 policy_name => 'CS_SR_SEC_RESP_ACCESS',
265 enable => FALSE );
266
267 EXCEPTION
268 WHEN OTHERS THEN
269 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
270 fnd_message.set_name ('CS', 'CS_API_SR_UNKNOWN_ERROR');
271 fnd_message.set_token ('P_TEXT',l_api_name_full||'-'||SQLERRM);
272 fnd_msg_pub.ADD;
273
277 -- Subscription function to the JTF task assignment create and update
274 END DISABLE_SR_POLICIES;
275
276 --
278 -- event
279
280 FUNCTION SECURE_SR_TASK_ASSIGN (
281 p_subscription_guid IN RAW,
282 p_event IN OUT NOCOPY WF_EVENT_T )
283 RETURN VARCHAR2
284 IS
285 -- cursor to fetch the task id for which the assignment is created or
286 -- updated
287 cursor get_task_asgn ( c_task_assignment_id IN NUMBER ) is
288 select task_id
289 from jtf_task_assignments
290 where task_assignment_id = c_task_assignment_id;
291
292 l_task_id NUMBER;
293
294 -- cursor to fetch the source object id and type to which the task is
295 -- associated to
296 cursor get_tasks ( c_task_id IN NUMBER ) is
297 select source_object_id, source_object_type_code
298 from jtf_tasks_b
299 where task_id = c_task_id;
300
301 l_source_id NUMBER;
302 l_source_type VARCHAR2(240);
303
304 -- cursor to fetch the SR Type id of the SR whose task owner is
305 -- getting assigned.
306 cursor get_sr_type ( c_incident_id IN NUMBER ) is
307 select incident_type_id
308 from cs_incidents_all_b
309 where incident_id = c_incident_id;
310
311 l_incident_type_id NUMBER;
312
313 -- cursor that queries from the service JTF secure view. If security is
314 -- enabled and is set to Standard, the following where predicate will be
315 -- appended to the cursor select
316 -- OR EXISTS
317 -- ( SELECT '1'
318 -- from fnd_user_resp_groups ur,
319 -- cs_sr_type_mapping csmap
320 -- WHERE (cs_jtf_rs_resource_extns_sec.user_id IS NULL )
321 -- OR cs_jtf_rs_resource_extns_sec.user_id = ur.user_id
322 -- AND ur.responsibility_id = csmap.Responsibility_id
323 -- AND ur. responsibility application_id = csmap.application_id
324 -- AND csmap.business_usage = 'AGENT'
325 -- AND csmap.incident_type_id = sys_context(CS_SR_SECURITY, SRTYPE_ID)
326 -- AND trunc(sysdate) between trunc(nvl(csmap.start_date, sysdate))
327 -- and trunc(nvl(csmap.end_date,sysdate)) ))
328
329 cursor check_sr_access ( c_resource_id IN NUMBER )is
330 select 1
331 from cs_jtf_rs_resource_extns_sec
332 where resource_id = c_resource_id;
333
334 l_count NUMBER;
335
336 l_task_assignment_id NUMBER;
337 l_resource_id NUMBER;
338 l_resource_type_code VARCHAR2(240);
339 l_assignment_status_id NUMBER;
340
341 l_api_name CONSTANT VARCHAR2(40) := '.CS_SR_SECURE_TASK_ASSIGN';
342 l_api_name_full VARCHAR2(70) := G_PKG_NAME || l_api_name;
343
344 l_event_name VARCHAR2(240) := p_event.getEventName( );
345
346 BEGIN
347 -- get the task_assignment_id from the event payload
348 l_task_assignment_id := p_event.GetValueForParameter('TASK_ASSIGNMENT_ID');
349
350 -- if the event is an update, then need to prepend NEW_ to the WF attribute name
351 if ( l_event_name = 'oracle.apps.jtf.cac.task.updateTaskAssignment' ) then
352 l_resource_id := p_event.GetValueForParameter('NEW_RESOURCE_ID');
353 l_resource_type_code := p_event.GetValueForParameter('NEW_RESOURCE_TYPE_CODE');
354 l_assignment_status_id := p_event.GetValueForParameter('NEW_ASSIGNMENT_STATUS_ID');
355 else
356 l_resource_id := p_event.GetValueForParameter('RESOURCE_ID');
357 l_resource_type_code := p_event.GetValueForParameter('RESOURCE_TYPE_CODE');
358 l_assignment_status_id := p_event.GetValueForParameter('ASSIGNMENT_STATUS_ID');
359 end if;
360
361 -- perform the validation only if the resource id is available. On update, if the
362 -- resource is not changed, the JTF event does not publish the old value to the
363 -- event.
364 if ( l_resource_id is not null ) then
365 -- get the task id from the task assignments table
366 open get_task_asgn( l_task_assignment_id );
367 fetch get_task_asgn into l_task_id;
368 close get_task_asgn;
369
370 -- get the task details from the task_id retrieved from the task asgn.
371 open get_tasks ( l_task_id );
372 fetch get_tasks into l_source_id, l_source_type;
373 close get_tasks;
374
375 -- if the source type is SR then proceed with the check for security,
376 -- if not, stop and return control
377
378 if ((l_source_type = 'SR') AND (l_resource_type_code = 'RS_EMPLOYEE')) then
379 -- get the SR Type to set the context
380 open get_sr_type ( l_source_id );
381 fetch get_sr_type into l_incident_type_id;
382 close get_sr_type;
383
384 -- set the SR Type contex
385 cs_sr_security_context.set_sr_security_context (
386 p_context_attribute => 'SRTYPE_ID',
387 p_context_attribute_value => l_incident_type_id );
388
389 -- query from the CS JTF resource secure view
390 open check_sr_access (l_resource_id );
391 fetch check_sr_access into l_count;
392 close check_sr_access;
393
394 if ( l_count <= 0 or l_count is null ) then
395 -- resource assigned to the task does not have access to the SR type.
396 -- return an error status to the JTF tasks event api.
397 -- since this is a business event, need to set the value of the return
398 -- status on the busines event's parameter list. The jtf API that raised
399 -- the event will retrieve the return status from the parameter list
400
401 wf_event.addparametertolist(
402 p_name => 'X_RETURN_STATUS',
406 fnd_message.set_name ('CS','CS_SR_JTF_TASK_ASSIGN_INVALID');
403 p_value => 'ERROR',
404 p_parameterlist => p_event.parameter_list );
405
407 fnd_message.set_token('API_NAME', l_api_name_full);
408 fnd_msg_pub.add;
409 else
410 -- resource assigned to the task has access to the SR type and can be
411 -- assigned to the task. Return back a sucess status
412 wf_event.addparametertolist(
413 p_name => 'X_RETURN_STATUS',
414 p_value => 'SUCCESS',
415 p_parameterlist => p_event.parameter_list );
416 end if;
417 end if; -- if ( l_source_type = 'SR' )
418 else
419 -- resource assigned to the task is not an individual resource and hence has access to the SR
420 -- type and can be assigned to the task. Return back a sucess status
421
422 wf_event.addparametertolist(
423 p_name => 'X_RETURN_STATUS',
424 p_value => 'SUCCESS',
425 p_parameterlist => p_event.parameter_list );
426
427 end if; -- if ( l_resource_id is not null ) then
428
429 -- Always return a success for the WF execution. If the assignment does not satisfy
430 -- the service security rules, the JTF API that raised the event, will look for
431 -- the value of the return status and will stop execution
432 RETURN 'SUCCESS';
433
434 EXCEPTION
435 WHEN OTHERS THEN
436 fnd_message.set_name ('CS', 'CS_API_SR_UNKNOWN_ERROR');
437 fnd_message.set_token ('P_TEXT',l_api_name_full||'-'||SQLERRM );
438 fnd_msg_pub.ADD;
439 wf_event.addparametertolist(
440 p_name => 'X_RETURN_STATUS',
441 p_value => 'ERROR',
442 p_parameterlist => p_event.parameter_list );
443 RETURN 'ERROR';
444 END SECURE_SR_TASK_ASSIGN;
445
446 --
447 -- Subscription function to the JTF task create and update event
448
449 FUNCTION SECURE_SR_TASK_OWNER (
450 p_subscription_guid IN RAW,
451 p_event IN OUT NOCOPY WF_EVENT_T )
452 RETURN VARCHAR2
453 IS
454 -- cursor to fetch the resource, source object id and type for the task
455 cursor get_tasks ( c_task_id IN NUMBER ) is
456 select owner_id, owner_type_code ,
457 source_object_id, source_object_type_code
458 from jtf_tasks_b
459 where task_id = c_task_id;
460
461 l_task_id NUMBER;
462 l_owner_id NUMBER;
463 l_owner_type_code VARCHAR2(240);
464 l_source_id NUMBER;
465 l_source_type VARCHAR2(240);
466
467 -- cursor to fetch the SR Type id of the SR associated to the task
468 cursor get_sr_type ( c_incident_id IN NUMBER ) is
469 select incident_type_id
470 from cs_incidents_all_b
471 where incident_id = c_incident_id;
472
473 l_incident_type_id NUMBER;
474
475 -- cursor that queries from the service JTF secure view. If security is
476 -- enabled and is set to Standard, the following where predicate will be
477 -- appended to the cursor select
478 -- OR EXISTS
479 -- ( SELECT '1'
480 -- from fnd_user_resp_groups ur,
481 -- cs_sr_type_mapping csmap
482 -- WHERE (cs_jtf_rs_resource_extns_sec.user_id IS NULL )
483 -- OR cs_jtf_rs_resource_extns_sec.user_id = ur.user_id
484 -- AND ur.responsibility_id = csmap.Responsibility_id
485 -- AND ur. responsibility application_id = csmap.application_id
486 -- AND csmap.business_usage = 'AGENT'
487 -- AND csmap.incident_type_id = sys_context(CS_SR_SECURITY, SRTYPE_ID)
488 -- AND trunc(sysdate) between trunc(nvl(csmap.start_date, sysdate))
489 -- and trunc(nvl(csmap.end_date,sysdate)) ))
490
491 cursor check_sr_access ( c_resource_id IN NUMBER )is
492 select 1
493 from cs_jtf_rs_resource_extns_sec
494 where resource_id = c_resource_id;
495
496 l_count NUMBER;
497
498 l_api_name CONSTANT VARCHAR2(40) := '.SECURE_SR_TASK_OWNER';
499 l_api_name_full VARCHAR2(70) := G_PKG_NAME || l_api_name;
500
501 BEGIN
502
503 -- get the task_id from the event payload
504 l_task_id := p_event.GetValueForParameter('TASK_ID');
505
506 -- get the task details from the task_id retrieved from the task asgn.
507 open get_tasks ( l_task_id );
508 fetch get_tasks into l_owner_id , l_owner_type_code,
509 l_source_id , l_source_type;
510 close get_tasks;
511
512 -- if the source type is SR then proceed with the check for security,
513 -- if not, stop and return control
514
515 if ((l_source_type = 'SR') AND (l_owner_type_code = 'RS_EMPLOYEE')) then
516 -- get the SR Type to set the context
517 open get_sr_type ( l_source_id );
518 fetch get_sr_type into l_incident_type_id;
519 close get_sr_type;
520
521 -- set the SR Type contex
522 cs_sr_security_context.set_sr_security_context (
523 p_context_attribute => 'SRTYPE_ID',
524 p_context_attribute_value => l_incident_type_id );
525
526 -- query from the CS JTF resource secure view
527 open check_sr_access (l_owner_id );
528 fetch check_sr_access into l_count;
529 close check_sr_access;
530
531 if ( l_count <= 0 or l_count is null ) then
532 -- resource assigned to the task does not have access to the SR type.
533 -- return an error status to the JTF tasks event api.
534 -- since this is a business event, need to set the value of the return
538 p_name => 'X_RETURN_STATUS',
535 -- status on the busines event's parameter list. The jtf API that raised
536 -- the event will retrieve the return status from the parameter list
537 wf_event.addparametertolist(
539 p_value => 'ERROR',
540 p_parameterlist => p_event.parameter_list );
541
542 fnd_message.set_name ('CS','CS_SR_JTF_TASK_OWNER_INVALID');
543 fnd_message.set_token('API_NAME', l_api_name_full);
544 fnd_msg_pub.add;
545 else
546 -- resource assigned to the task has access to the SR type and can be
547 -- assigned to the task. Return back a sucess status
548 wf_event.addparametertolist(
549 p_name => 'X_RETURN_STATUS',
550 p_value => 'SUCCESS',
551 p_parameterlist => p_event.parameter_list );
552 end if;
553 else
554 -- resource assigned to the task is not an inidividual resource and hence has access to the SR
555 -- type and can be assigned to the task. Return back a sucess status
556
557 wf_event.addparametertolist(
558 p_name => 'X_RETURN_STATUS',
559 p_value => 'SUCCESS',
560 p_parameterlist => p_event.parameter_list );
561 end if; -- if ( l_source_type = 'SR' )
562
563 -- always return a success for the WF execution. If the owner does not satisfy
564 -- the service security rules, the JTF API that raised the event, will look for
565 -- the value of the return status and will stop execution
566 RETURN 'SUCCESS';
567
568 EXCEPTION
569 WHEN OTHERS THEN
570 fnd_message.set_name ('CS', 'CS_API_SR_UNKNOWN_ERROR');
571 fnd_message.set_token ('P_TEXT',l_api_name_full||'-'||SQLERRM );
572 fnd_msg_pub.ADD;
573 wf_event.addparametertolist(
574 p_name => 'X_RETURN_STATUS',
575 p_value => 'ERROR',
576 p_parameterlist => p_event.parameter_list );
577 RETURN 'ERROR';
578 END SECURE_SR_TASK_OWNER;
579
580 /*************************************************************************
581 Name - Alter_SR_Policies
582
583 DESCRIPTION of Procedure Alter_SR_Policies
584 The proccedure is to alter the service owned database VPD policies.
585 Logic
586 IF p_security_setting = 'ANONE' THEN
587 Disable the existing service VPD policies
588 ELSIF p_security_setting = 'BSTANDARD' THEN
589 Drop the existing Service VPD policies
590 Create the service VPD policies as 'Static' policies
591 ELSIF p_security_setting = 'CCUSTOM' THEN
592 Drop the existing Service VPD policies
593 Create the service VPD policies as 'Dynamic' policies
594 END IF ;
595
596 *************************************************************************/
597
598 PROCEDURE Alter_SR_Policies
599 (p_security_setting IN VARCHAR2,
600 x_return_status OUT NOCOPY VARCHAR2,
601 x_msg_count OUT NOCOPY NUMBER,
602 x_msg_data OUT NOCOPY VARCHAR2) IS
603
604 BEGIN
605 x_return_status := FND_API.G_RET_STS_SUCCESS;
606
607 IF p_security_setting = 'ANONE' THEN
608 DISABLE_SR_POLICIES(x_return_status => x_return_status);
609
610 ELSIF (p_security_setting = 'BSTANDARD') OR (p_security_setting = 'CCUSTOM') THEN
611 -- Drop the service owned VPD policies
612 -- Drop the VPD policy associated with CS_INCIDENTS_B_SEC object
613
614 DBMS_RLS.drop_policy
615 (object_schema => 'APPS',
616 object_name => 'CS_INCIDENTS_B_SEC',
617 policy_name => 'CS_SR_SEC_SR_ACCESS');
618
619 -- Drop the VPD policy associated with CS_SR_TYPES_SELECT_SEC object
620 DBMS_RLS.drop_policy
621 (object_schema => 'APPS',
622 object_name => 'CS_SR_TYPES_SELECT_SEC',
623 policy_name => 'CS_SR_SEC_SRTYPE_ACCESS');
624
625 -- Drop the VPD policy associated with CS_JTF_RS_RESOURCE_EXTNS_SEC object
626 DBMS_RLS.drop_policy
627 (object_schema => 'APPS',
628 object_name => 'CS_JTF_RS_RESOURCE_EXTNS_SEC',
629 policy_name => 'CS_SR_JTF_RESOURCE_ACCESS');
630
631 -- Drop the VPD policy associated with CS_SR_ACCESS_RESP_SEC object
632 DBMS_RLS.drop_policy
633 (object_schema => 'APPS',
634 object_name => 'CS_SR_ACCESS_RESP_SEC',
635 policy_name => 'CS_SR_SEC_RESP_ACCESS');
636
637 -- re create service owned VPD policies as static VPD policies
638
639 -- Create the VPD policy associated with CS_INCIDENTS_B_SEC object
640 DBMS_RLS.add_policy
641 (object_schema => 'APPS',
642 object_name => 'CS_INCIDENTS_B_SEC',
643 policy_name => 'CS_SR_SEC_SR_ACCESS',
644 function_schema => 'APPS',
645 policy_function => 'CS_SR_SECURITY_UTIL.SET_SR_ACCESS',
646 statement_types => 'SELECT',
647 static_policy => FALSE,
648 long_predicate => TRUE );
649
650 -- Create the VPD policy associated with CS_SR_TYPES_SELECT_SEC object
651 DBMS_RLS.add_policy
652 (object_schema => 'APPS',
653 object_name => 'CS_SR_TYPES_SELECT_SEC',
654 policy_name => 'CS_SR_SEC_SRTYPE_ACCESS',
655 function_schema => 'APPS',
656 policy_function => 'FND_GENERIC_POLICY.GET_PREDICATE',
657 statement_types => 'SELECT',
658 static_policy => FALSE,
659 long_predicate => TRUE );
660
661 -- Create the VPD policy associated with CS_JTF_RS_RESOURCE_EXTNS_SEC object
662 DBMS_RLS.add_policy
663 (object_schema => 'APPS',
664 object_name => 'CS_JTF_RS_RESOURCE_EXTNS_SEC',
665 policy_name => 'CS_SR_JTF_RESOURCE_ACCESS',
666 function_schema => 'APPS',
667 policy_function => 'FND_GENERIC_POLICY.GET_PREDICATE',
668 statement_types => 'SELECT',
669 static_policy => FALSE,
670 long_predicate => TRUE );
671
672 -- Create the VPD policy associated with CS_SR_ACCESS_RESP_SEC object
673 DBMS_RLS.add_policy
674 (object_schema => 'APPS',
675 object_name => 'CS_SR_ACCESS_RESP_SEC',
676 policy_name => 'CS_SR_SEC_RESP_ACCESS',
677 function_schema => 'APPS',
678 policy_function => 'FND_GENERIC_POLICY.GET_PREDICATE',
679 statement_types => 'SELECT',
680 static_policy => FALSE,
681 long_predicate => TRUE );
682
683 END IF;
684
685 EXCEPTION
686 WHEN others THEN
687 x_return_status := FND_API.G_RET_STS_ERROR;
688 FND_MSG_PUB.Count_And_Get
689 (p_count => x_msg_count,
690 p_data => x_msg_data );
691
692 END Alter_SR_Policies;
693
694 END CS_SR_SECURITY_UTIL;
[Home] [Help]