1 PACKAGE BODY AMW_CONTROLS_PAGE_PKG as
2 /* $Header: amwcnpgb.pls 120.1 2005/11/10 05:09:10 appldev noship $ */
3
4 --NPANANDI 11.19.2004 BEGIN
5 G_USER_ID NUMBER := FND_GLOBAL.USER_ID;
6 G_LOGIN_ID NUMBER := FND_GLOBAL.CONC_LOGIN_ID;
7 G_PKG_NAME CONSTANT VARCHAR2(30):= 'AMW_CONTROLS_PAGE_PKG';
8 --NPANANDI 11.19.2004 END
9
10 FUNCTION OBJECTIVE_PRESENT (P_CONTROL_REV_ID IN NUMBER,
11 P_OBJECTIVE_CODE IN VARCHAR2) RETURN VARCHAR2 IS
12 n number;
13 BEGIN
14 select count(*)
15 into n
16 from amw_control_objectives
17 where control_rev_id = P_CONTROL_REV_ID
18 and objective_code = P_OBJECTIVE_CODE;
19
20 if n > 0 then
21 return 'Y';
22 else
23 return 'N';
24 end if;
25 END OBJECTIVE_PRESENT;
26
27 FUNCTION new_OBJECTIVE_PRESENT (P_CONTROL_REV_ID IN NUMBER,
28 P_OBJECTIVE_CODE IN VARCHAR2) RETURN VARCHAR2 IS
29 n number;
30 yes varchar2(80);
31 no varchar2(80);
32 BEGIN
33 select count(*)
34 into n
35 from amw_control_objectives
36 where control_rev_id = P_CONTROL_REV_ID
37 and objective_code = P_OBJECTIVE_CODE;
38
39 select meaning
40 into yes
41 from fnd_lookups
42 where lookup_type='YES_NO'
43 and lookup_code='Y';
44
45 select meaning
46 into no
47 from fnd_lookups
48 where lookup_type='YES_NO'
49 and lookup_code='N';
50
51 if n > 0 then
52 ---return 'Y';
53 return yes;
54 else
55 ---return 'N';
56 return no;
57 end if;
58 END new_OBJECTIVE_PRESENT;
59
60
61 FUNCTION preventive_control_PRESENT (P_CONTROL_REV_ID IN NUMBER)
62 RETURN VARCHAR2 IS
63 n varchar2(1);
64 BEGIN
65 select preventive_control
66 into n
67 from amw_controls_all_vl
68 where control_rev_id = P_CONTROL_REV_ID;
69 ---and objective_code = P_OBJECTIVE_CODE;
70
71 ---if n > 0 then
72 --- return 'Y';
73 ---else
74 --- return 'N';
75 ---end if;
76 return n;
77 END preventive_control_PRESENT;
78
79 ------------------------------------------------------------------------------------------------------------
80 FUNCTION GET_OBJ (P_CONTROL_REV_ID IN NUMBER,P_TAG_NUM IN NUMBER)
81 RETURN VARCHAR2 IS
82 N varchar2(1);
83 BEGIN
84 BEGIN
85 SELECT 'Y'
86 INTO N
87 FROM AMW_CONTROL_OBJECTIVES
88 WHERE CONTROL_REV_ID=P_CONTROL_REV_ID
89 AND OBJECTIVE_CODE IN (SELECT LOOKUP_CODE
90 FROM AMW_LOOKUPS
91 WHERE LOOKUP_TYPE='AMW_CONTROL_OBJECTIVES'
92 AND TAG=P_TAG_NUM);
93 RETURN N;
94 EXCEPTION
95 WHEN NO_DATA_FOUND THEN
96 RETURN 'N';
97 END;
98
99 ---RETURN N;
100 END GET_OBJ;
101
102
103 ------------------------------------------------------------------------------------------------------------
104 FUNCTION ASSERTION_PRESENT (P_CONTROL_REV_ID IN NUMBER,
105 P_ASSERTION_CODE IN VARCHAR2) RETURN VARCHAR2 IS
106 n number;
107 BEGIN
108 select count(*)
109 into n
110 from amw_control_assertions
111 where control_rev_id = P_CONTROL_REV_ID
112 and assertion_code = P_ASSERTION_CODE;
113
114 if n > 0 then
115 return 'Y';
116 else
117 return 'N';
118 end if;
119 END ASSERTION_PRESENT;
120
121 ------------------------------------------------------------------------------------------------------------
122 FUNCTION new_ASSERTION_PRESENT (P_CONTROL_REV_ID IN NUMBER,
123 P_ASSERTION_CODE IN VARCHAR2) RETURN VARCHAR2 IS
124 n number;
125 yes varchar2(80);
126 no varchar2(80);
127 BEGIN
128 select count(*)
129 into n
130 from amw_control_assertions
131 where control_rev_id = P_CONTROL_REV_ID
132 and assertion_code = P_ASSERTION_CODE;
133
134 select meaning
135 into yes
136 from fnd_lookups
137 where lookup_type='YES_NO'
138 and lookup_code='Y';
139
140 select meaning
141 into no
142 from fnd_lookups
143 where lookup_type='YES_NO'
144 and lookup_code='N';
145
146 if n > 0 then
147 ---return 'Y';
148 return yes;
149 else
150 ---return 'N';
151 return no;
152 end if;
153 END new_ASSERTION_PRESENT;
154
155
156 ------------------------------------------------------------------------------------------------------------
157 FUNCTION component_PRESENT (P_CONTROL_REV_ID IN NUMBER,
158 P_component_CODE IN VARCHAR2) RETURN VARCHAR2 IS
159 n number;
160 BEGIN
161 select count(*)
162 into n
163 from amw_assessment_components
164 where object_type = 'CONTROL'
165 and object_id = P_CONTROL_REV_ID
166 and component_code = P_component_CODE;
167
168 if n > 0 then
169 return 'Y';
170 else
171 return 'N';
172 end if;
173 END component_PRESENT;
174
175 ------------------------------------------------------------------------------------------------------------
176 FUNCTION new_component_PRESENT (P_CONTROL_REV_ID IN NUMBER,
177 P_component_CODE IN VARCHAR2) RETURN VARCHAR2 IS
178 n number;
179 yes varchar2(80);
180 no varchar2(80);
181 BEGIN
182 select count(*)
183 into n
184 from amw_assessment_components
185 where object_type = 'CONTROL'
186 and object_id = P_CONTROL_REV_ID
187 and component_code = P_component_CODE;
188
189 select meaning
190 into yes
191 from fnd_lookups
192 where lookup_type='YES_NO'
193 and lookup_code='Y';
194
195 select meaning
196 into no
197 from fnd_lookups
198 where lookup_type='YES_NO'
199 and lookup_code='N';
200
201 if n > 0 then
202 ---return 'Y';
203 return yes;
204 else
205 ---return 'N';
206 return no;
207 end if;
208 END new_component_PRESENT;
209
210
211 ------------------------------------------------------------------------------------------------------------
212 FUNCTION association_exists (P_process_objective_ID IN NUMBER) RETURN VARCHAR2 IS
213 n number;
214 BEGIN
215 select count(*)
216 into n
217 from amw_objective_associations
218 where process_objective_id = P_process_objective_ID;
219
220
221 if n > 0 then
222 return 'Y';
223 else
224 return 'N';
225 end if;
226 END association_exists;
227
228 -----------------------------------------------------------------------------------------------------------------
229 FUNCTION GET_LOOKUP_VALUE(p_lookup_type in varchar2,
230 p_lookup_code in varchar2) return varchar2 is
231 l_meaning varchar2(80);
232 begin
233 select meaning
234 into l_meaning
235 from amw_lookups
236 where lookup_type = p_lookup_type
237 and lookup_code = p_lookup_code
238 AND enabled_flag ='Y'
239 AND (end_date_active > SYSDATE or end_date_active is null);
240
241 return l_meaning;
242 exception
243 when no_data_found then
244 return null;
245 when others then
246 return null;
247 end;
248
249 --------------------------------------------------------------------------------------------------------------------
250 FUNCTION GET_CONTROL_SOURCE (p_control_source_id varchar2,
251 p_control_type varchar2,
252 p_automation_type varchar2,
253 p_application_id number,
254 p_control_rev_id number) return varchar2 is
255
256 l_control_source_name varchar2(240);
257 l_ita_installed VARCHAR2(1);
258 l_control_source_id varchar2(240);
259 begin
260 --npanandi 12.04.2004: added to handle conversion of ControlSource from char to varchar2
261 if(p_control_source_id is not null)then
262 l_control_source_id := trim(p_control_source_id);
263 end if;
264 ---npanandi 12.04.2004 end
265
266 if ((p_control_type = 'A' or p_control_type = 'C') AND (p_automation_type = '1')) then
267 select user_profile_option_name
268 into l_control_source_name
269 from fnd_profile_options_vl
270 --where to_char(profile_option_id) = p_control_source_id
271 where to_char(profile_option_id) = l_control_source_id
272 and application_id = p_application_id;
273 elsif ((p_control_type = 'A' or p_control_type = 'C') AND (p_automation_type = '2')) then
274 /**select user_form_name
275 into l_control_source_name
276 from fnd_form_vl
277 where to_char(form_id) = p_control_source_id
278 and application_id = p_application_id;
279 **/
280 if(p_application_id is not null)then
281 select user_function_name
282 into l_control_source_name
283 from fnd_form_functions_vl
284 --where to_char(function_id) = p_control_source_id
285 where to_char(function_id) = l_control_source_id
286 and application_id = p_application_id;
287 else
288 select user_function_name
289 into l_control_source_name
290 from fnd_form_functions_vl
291 --where to_char(function_id) = p_control_source_id
292 where to_char(function_id) = l_control_source_id
293 and application_id is null;
294 end if;
295
296 elsif ((p_control_type = 'A' or p_control_type = 'C') AND (p_automation_type = '5')) then
297 select distinct display_name
298 into l_control_source_name
299 from wf_activities_vl
300 --where name = p_control_source_id and type='PROCESS'
301 where name = l_control_source_id and type='PROCESS'
302 and end_date is null;
303 elsif ((p_control_type = 'A' or p_control_type = 'C') AND (p_automation_type = '7')) then
304 select fcpv.user_concurrent_program_name
305 into l_control_source_name
306 from fnd_concurrent_programs_vl fcpv
307 ---where to_char(fcpv.concurrent_program_id) = p_control_source_id
308 where to_char(fcpv.concurrent_program_id) = l_control_source_id
309 and fcpv.application_id=p_application_id and fcpv.enabled_flag='Y';
310
311 /*** select to_char(fcpv.concurrent_program_id) control_source_id,
312 fcpv.user_concurrent_program_name control_source_name,
313 fcpv.application_id application_id,
314 (select application_name from fnd_application_vl where application_id=fcpv.application_id) applicationName,
315 'REPORT' lov_type,
316 'A' control_type,
317 '7' automation_type
318 from amw_controls_all_vl acav, fnd_concurrent_programs_vl fcpv
319 where acav.application_id=fcpv.application_id and enabled_flag='Y'
320 ***/
321 elsif ((p_control_type = 'A' or p_control_type = 'C') AND (p_automation_type = '9')) then
322 select itl.NAME control_source_name
323 into l_control_source_name
324 from bis_application_measures am,
325 bis_indicators i ,bis_indicators_tl itl,
326 fnd_application_vl a
327 ---where to_char(am.indicator_id) = p_control_source_id
328 where to_char(am.indicator_id) = l_control_source_id
329 and am.indicator_id = i.INDICATOR_ID
330 AND i.INDICATOR_ID = itl.INDICATOR_ID
331 AND itl.LANGUAGE = USERENV('LANG')
332 AND am.application_id = a.application_id
333 and a.application_id = p_application_id;
334 --psomanat 12.04.2004: added to support association of canstraint to Control
335 elsif ((p_control_type = 'A' or p_control_type = 'C') AND (p_automation_type = 'SOD')) then
336 select distinct ac.CONSTRAINT_NAME
337 into l_control_source_name
338 from AMW_Constraints_vl ac
339 where to_char(ac.CONSTRAINT_ID) = l_control_source_id;
340 elsif ((p_control_type = 'A' or p_control_type = 'C') AND (p_automation_type = '10')) then
341 begin
342 l_ita_installed := 'N';
343 select 'Y'
344 into l_ita_installed
345 from fnd_product_installations
346 where application_id=438;
347 exception
348 when no_data_found then
349 l_ita_installed := 'N';
350 end;
351
352 if(l_ita_installed = 'Y')then
353 ---03.07.2005 npanandi: changed below query bugfix 4192248
354 ---EXECUTE IMMEDIATE 'SELECT PARAMETER_NAME from ITA_SETUP_PARAMETERS_VL WHERE PARAMETER_CODE=:B1' INTO l_control_source_name USING l_CONTROL_SOURCE_ID;
355 EXECUTE IMMEDIATE 'SELECT isgv.SETUP_GROUP_NAME||'': ''||PARAMETER_NAME '
356 ||' from ITA_SETUP_PARAMETERS_VL ispv, ita_setup_groups_vl isgv WHERE ispv.AUDIT_ENABLED_FLAG=''Y'' and ispv.SETUP_GROUP_CODE=isgv.SETUP_GROUP_CODE and ispv.PARAMETER_CODE=:B1' INTO l_control_source_name USING l_CONTROL_SOURCE_ID;
357 else
358 l_control_source_name := null;
359 end if;
360 end if;
361
362 return l_control_source_name;
363 exception
364 when others then
365 l_control_source_name := null;
366 return l_control_source_name;
367 end;
368
369
370
371 ------------------------------------------------------------------------------------------------------------
372 PROCEDURE PROCESS_OBJECTIVE (p_init_msg_list IN VARCHAR2,
373 p_commit IN VARCHAR2,
374 p_validate_only IN VARCHAR2,
375 p_select_flag IN VARCHAR2,
376 p_control_rev_id IN NUMBER,
377 p_objective_code IN VARCHAR2,
378 x_return_status OUT NOCOPY VARCHAR2,
379 x_msg_count OUT NOCOPY NUMBER,
380 x_msg_data OUT NOCOPY VARCHAR2) IS
381
382 l_creation_date date;
383 l_created_by number;
384 l_last_update_date date;
385 l_last_updated_by number;
386 l_last_update_login number;
387 l_control_objective_id number;
388 BEGIN
389
390 -- create savepoint if p_commit is true
391 IF p_commit = FND_API.G_TRUE THEN
392 SAVEPOINT process_objective_save_point;
393 END IF;
394
395 -- initialize message list if p_init_msg_list is set to true
396 if FND_API.to_boolean(nvl(p_init_msg_list, FND_API.G_FALSE)) then
397 fnd_msg_pub.initialize;
398 end if;
399
400 -- initialize return status to success
401 x_return_status := fnd_api.g_ret_sts_success;
402
403
404 delete from amw_control_objectives
405 where control_rev_id = p_control_rev_id
406 and objective_code = p_objective_code;
407
408
409 if (p_select_flag = 'Y') then
410
411 l_creation_date := SYSDATE;
412 l_created_by := FND_GLOBAL.USER_ID;
413 l_last_update_date := SYSDATE;
414 l_last_updated_by := FND_GLOBAL.USER_ID;
415 l_last_update_login := FND_GLOBAL.USER_ID;
416
417 select amw_control_objectives_s.nextval into l_control_objective_id from dual;
418
419 insert into amw_control_objectives (control_objective_id,
420 control_rev_id,
421 objective_code,
422 creation_date,
423 created_by,
424 last_update_date,
425 last_updated_by,
426 last_update_login)
427 values (l_control_objective_id,
428 p_control_rev_id,
429 p_objective_code,
430 l_creation_date,
431 l_created_by,
432 l_last_update_date,
433 l_last_updated_by,
434 l_last_update_login);
435
436 end if;
437 EXCEPTION
438 WHEN FND_API.G_EXC_ERROR THEN
439 IF p_commit = FND_API.G_TRUE THEN
440 ROLLBACK TO process_objective_save_point;
441 END IF;
442
443 x_return_status := FND_API.G_RET_STS_ERROR;
444
445 fnd_msg_pub.count_and_get(p_count => x_msg_count,
446 p_data => x_msg_data);
447
448 WHEN OTHERS THEN
449 IF p_commit = FND_API.G_TRUE THEN
450 ROLLBACK TO create_prop_person_support;
451 END IF;
452
453 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
454
455 fnd_msg_pub.add_exc_msg(p_pkg_name => 'AMW_CONTROLS_PKG',
456 p_procedure_name => 'PROCESS_OBJECTIVE',
457 p_error_text => SUBSTRB(SQLERRM,1,240));
458
459 fnd_msg_pub.count_and_get(p_count => x_msg_count,
460 p_data => x_msg_data);
461
462
463 END PROCESS_OBJECTIVE;
464
465 -------------------------------------------------------------------------------------------------------------------
466 PROCEDURE PROCESS_ASSERTION (p_init_msg_list IN VARCHAR2,
467 p_commit IN VARCHAR2,
468 p_validate_only IN VARCHAR2,
469 p_select_flag IN VARCHAR2,
470 p_control_rev_id IN NUMBER,
471 p_assertion_code IN VARCHAR2,
472 x_return_status OUT NOCOPY VARCHAR2,
473 x_msg_count OUT NOCOPY NUMBER,
474 x_msg_data OUT NOCOPY VARCHAR2) IS
475
476 l_creation_date date;
477 l_created_by number;
478 l_last_update_date date;
479 l_last_updated_by number;
480 l_last_update_login number;
481 l_control_assertion_id number;
482 BEGIN
483
484 -- create savepoint if p_commit is true
485 IF p_commit = FND_API.G_TRUE THEN
486 SAVEPOINT process_assertion_save_point;
487 END IF;
488
489 -- initialize message list if p_init_msg_list is set to true
490 if FND_API.to_boolean(nvl(p_init_msg_list, FND_API.G_FALSE)) then
491 fnd_msg_pub.initialize;
492 end if;
493
494 -- initialize return status to success
495 x_return_status := fnd_api.g_ret_sts_success;
496
497
498 delete from amw_control_assertions
499 where control_rev_id = p_control_rev_id
500 and assertion_code = p_assertion_code;
501
502
503 if (p_select_flag = 'Y') then
504
505 l_creation_date := SYSDATE;
506 l_created_by := FND_GLOBAL.USER_ID;
507 l_last_update_date := SYSDATE;
508 l_last_updated_by := FND_GLOBAL.USER_ID;
509 l_last_update_login := FND_GLOBAL.USER_ID;
510
511 select amw_control_assertions_s.nextval into l_control_assertion_id from dual;
512
513 insert into amw_control_assertions (control_assertion_id,
514 control_rev_id,
515 assertion_code,
516 creation_date,
517 created_by,
518 last_update_date,
519 last_updated_by,
520 last_update_login)
521 values (l_control_assertion_id,
522 p_control_rev_id,
523 p_assertion_code,
524 l_creation_date,
525 l_created_by,
526 l_last_update_date,
527 l_last_updated_by,
528 l_last_update_login);
529
530 end if;
531 EXCEPTION
532 WHEN FND_API.G_EXC_ERROR THEN
533 IF p_commit = FND_API.G_TRUE THEN
534 ROLLBACK TO process_assertion_save_point;
535 END IF;
536
537 x_return_status := FND_API.G_RET_STS_ERROR;
538
539 fnd_msg_pub.count_and_get(p_count => x_msg_count,
540 p_data => x_msg_data);
541
542 WHEN OTHERS THEN
543 IF p_commit = FND_API.G_TRUE THEN
544 ROLLBACK TO create_prop_person_support;
545 END IF;
546
547 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
548
549 fnd_msg_pub.add_exc_msg(p_pkg_name => 'AMW_CONTROLS_PKG',
550 p_procedure_name => 'PROCESS_ASSERTION',
551 p_error_text => SUBSTRB(SQLERRM,1,240));
552
553 fnd_msg_pub.count_and_get(p_count => x_msg_count,
554 p_data => x_msg_data);
555
556
557 END PROCESS_ASSERTION;
558
559 -------------------------------------------------------------------------------------------------------------------
560 PROCEDURE PROCESS_component (p_init_msg_list IN VARCHAR2,
561 p_commit IN VARCHAR2,
562 p_validate_only IN VARCHAR2,
563 p_select_flag IN VARCHAR2,
564 p_control_rev_id IN NUMBER,
565 p_component_code IN VARCHAR2,
566 x_return_status OUT NOCOPY VARCHAR2,
567 x_msg_count OUT NOCOPY NUMBER,
568 x_msg_data OUT NOCOPY VARCHAR2) IS
569
570 l_creation_date date;
571 l_created_by number;
572 l_last_update_date date;
573 l_last_updated_by number;
574 l_last_update_login number;
575 l_assessment_component_id number;
576 BEGIN
577
578 -- create savepoint if p_commit is true
579 IF p_commit = FND_API.G_TRUE THEN
580 SAVEPOINT process_component_save_point;
581 END IF;
582
583 -- initialize message list if p_init_msg_list is set to true
584 if FND_API.to_boolean(nvl(p_init_msg_list, FND_API.G_FALSE)) then
585 fnd_msg_pub.initialize;
586 end if;
587
588 -- initialize return status to success
589 x_return_status := fnd_api.g_ret_sts_success;
590
591
592 delete from amw_assessment_components
593 where object_id = p_control_rev_id
594 and object_type = 'CONTROL'
595 and component_code = p_component_code;
596
597
598 if (p_select_flag = 'Y') then
599
600 l_creation_date := SYSDATE;
601 l_created_by := FND_GLOBAL.USER_ID;
602 l_last_update_date := SYSDATE;
603 l_last_updated_by := FND_GLOBAL.USER_ID;
604 l_last_update_login := FND_GLOBAL.USER_ID;
605
606 select amw_assessment_components_s.nextval into l_assessment_component_id from dual;
607
608 insert into amw_assessment_components (assessment_component_id,
609 component_code,
610 object_type,
611 object_id,
612 creation_date,
613 created_by,
614 last_update_date,
615 last_updated_by,
616 last_update_login,
617 object_version_number)
618 values (l_assessment_component_id,
619 p_component_code,
620 'CONTROL',
621 p_control_rev_id,
622 l_creation_date,
623 l_created_by,
624 l_last_update_date,
625 l_last_updated_by,
626 l_last_update_login,
627 1);
628 end if;
629
630 EXCEPTION
631 WHEN FND_API.G_EXC_ERROR THEN
632 IF p_commit = FND_API.G_TRUE THEN
633 ROLLBACK TO process_component_save_point;
634 END IF;
635
636 x_return_status := FND_API.G_RET_STS_ERROR;
637
638 fnd_msg_pub.count_and_get(p_count => x_msg_count,
639 p_data => x_msg_data);
640
641 WHEN OTHERS THEN
642 IF p_commit = FND_API.G_TRUE THEN
643 ROLLBACK TO create_prop_person_support;
644 END IF;
645
646 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
647
648 fnd_msg_pub.add_exc_msg(p_pkg_name => 'AMW_CONTROLS_PKG',
649 p_procedure_name => 'PROCESS_COMPONENT',
650 p_error_text => SUBSTRB(SQLERRM,1,240));
651
652 fnd_msg_pub.count_and_get(p_count => x_msg_count,
653 p_data => x_msg_data);
654
655
656 END PROCESS_component;
657
658 -------------------------------------------------------------------------------------------------------------------
659 PROCEDURE delete_control_association (p_init_msg_list IN VARCHAR2,
660 p_commit IN VARCHAR2,
661 p_object_type IN VARCHAR2,
662 p_risk_association_id IN NUMBER,
663 p_orig_control_id in number,
664 x_return_status OUT NOCOPY VARCHAR2,
665 x_msg_count OUT NOCOPY NUMBER,
666 x_msg_data OUT NOCOPY VARCHAR2) IS
667
668 ----String pObjectType,String pRiskId, String pProcessOrgId, String pControlId)
669
670 l_creation_date date;
671 l_created_by number;
672 l_last_update_date date;
673 l_last_updated_by number;
674 l_last_update_login number;
675
676 cursor get_association_row(l_object_type in varchar2,l_control_id in varchar2, l_pk1 in varchar2) is
677 select control_association_id from amw_control_associations
678 where object_type = l_object_type
679 and pk1 = l_pk1
680 and control_id = l_control_id;
681
682 l_control_association_id get_association_row%rowtype;
683
684 BEGIN
685
686 -- create savepoint if p_commit is true
687 IF p_commit = FND_API.G_TRUE THEN
688 SAVEPOINT update_association_save_point;
689 END IF;
690
691 -- initialize message list if p_init_msg_list is set to true
692 if FND_API.to_boolean(nvl(p_init_msg_list, FND_API.G_FALSE)) then
693 fnd_msg_pub.initialize;
694 end if;
695
696 -- initialize return status to success
697 x_return_status := fnd_api.g_ret_sts_success;
698
699 OPEN get_association_row(p_object_type,p_orig_control_id,p_risk_association_id);
700 FETCH get_association_row INTO l_control_association_id;
701 CLOSE get_association_row;
702
703 if(l_control_association_id.control_association_id is not null)then
704 delete from amw_control_associations
705 where control_association_id = l_control_association_id.control_association_id;
706
707 if (sql%notfound) then
708 raise no_data_found;
709 end if;
710 else
711 raise fnd_api.g_exc_error;
712 end if;
713
714 EXCEPTION
715 WHEN FND_API.G_EXC_ERROR THEN
716 IF p_commit = FND_API.G_TRUE THEN
717 ROLLBACK TO update_association_save_point;
718 END IF;
719
720 x_return_status := FND_API.G_RET_STS_ERROR;
721
722 fnd_msg_pub.count_and_get(p_count => x_msg_count,
723 p_data => x_msg_data);
724
725 WHEN OTHERS THEN
726 IF p_commit = FND_API.G_TRUE THEN
727 ROLLBACK TO create_prop_person_support;
728 END IF;
729
730 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
731
732 fnd_msg_pub.add_exc_msg(p_pkg_name => 'AMW_CONTROLS_PKG',
733 p_procedure_name => 'UPDATE_CONTROL_ASSOCIATION',
734 p_error_text => SUBSTRB(SQLERRM,1,240));
735
736 fnd_msg_pub.count_and_get(p_count => x_msg_count,
737 p_data => x_msg_data);
738
739 END delete_control_association;
740
741 -------------------------------------------------------------------------------------------------------------------
742 PROCEDURE delete_obj_assert_comp (p_init_msg_list IN VARCHAR2,
743 p_commit IN VARCHAR2,
744 p_control_rev_id in number,
745 x_return_status OUT NOCOPY VARCHAR2,
746 x_msg_count OUT NOCOPY NUMBER,
747 x_msg_data OUT NOCOPY VARCHAR2) IS
748
749 ----String pObjectType,String pRiskId, String pProcessOrgId, String pControlId)
750
751 l_creation_date date;
752 l_created_by number;
753 l_last_update_date date;
754 l_last_updated_by number;
755 l_last_update_login number;
756
757 cursor get_association_row(l_object_type in varchar2,l_control_id in varchar2, l_pk1 in varchar2) is
758 select control_association_id from amw_control_associations
759 where object_type = l_object_type
760 and pk1 = l_pk1
761 and control_id = l_control_id;
762
763 l_control_association_id get_association_row%rowtype;
764
765 BEGIN
766
767 -- create savepoint if p_commit is true
768 IF p_commit = FND_API.G_TRUE THEN
769 SAVEPOINT delete_save_point;
770 END IF;
771
772 -- initialize message list if p_init_msg_list is set to true
773 if FND_API.to_boolean(nvl(p_init_msg_list, FND_API.G_FALSE)) then
774 fnd_msg_pub.initialize;
775 end if;
776
777 -- initialize return status to success
778 x_return_status := fnd_api.g_ret_sts_success;
779 delete from amw_control_objectives where control_rev_id = p_control_rev_id;
780
781 /*
782 if (sql%notfound) then
783 raise no_data_found;
784 end if;
785 */
786 ---if(x_return_status = fnd_api.g_ret_sts_success) then
787 delete from amw_control_assertions where control_rev_id = p_control_rev_id;
788 /*
789 if(sql%notfound) then
790 raise no_data_found;
791 end if;
792 */
793 ---end if;
794 ---if(x_return_status = fnd_api.g_ret_sts_success) then
795 delete from amw_assessment_components
796 where object_id = p_control_rev_id
797 and object_type = 'CONTROL';
798 /*
799 if(sql%notfound) then
800 raise no_data_found;
801 end if;
802 */
803 ---end if;
804
805 EXCEPTION
806 WHEN FND_API.G_EXC_ERROR THEN
807 IF p_commit = FND_API.G_TRUE THEN
808 ROLLBACK TO delete_save_point;
809 END IF;
810 x_return_status := FND_API.G_RET_STS_ERROR;
811 fnd_msg_pub.count_and_get(p_count => x_msg_count,
812 p_data => x_msg_data);
813
814 WHEN OTHERS THEN
815 IF p_commit = FND_API.G_TRUE THEN
816 ROLLBACK TO create_prop_person_support;
817 END IF;
818 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
819 fnd_msg_pub.add_exc_msg(p_pkg_name => 'AMW_CONTROLS_PKG',
820 p_procedure_name => 'UPDATE_CONTROL_ASSOCIATION',
821 p_error_text => SUBSTRB(SQLERRM,1,240));
822 fnd_msg_pub.count_and_get(p_count => x_msg_count,
823 p_data => x_msg_data);
824
825 END delete_obj_assert_comp;
826
827 --npanandi 11.16.2004
828 --enhancement bugfix: 3391157
829 ------------------------------------------------------------------------------------------------------------
830 FUNCTION IS_CONTROL_EFFECTIVE(
831 P_ORGANIZATION_ID IN NUMBER
832 ,P_CONTROL_ID IN NUMBER
833 ) RETURN VARCHAR2 IS
834
835 CTRL_EFF VARCHAR2(2);
836
837 BEGIN
838 BEGIN
839 SELECT DECODE(aov.audit_result_code,'EFFECTIVE','Y','N')
840 INTO CTRL_EFF
841 FROM AMW_OPINIONS_V aov
842 WHERE aov.object_name = 'AMW_ORG_CONTROL'
843 AND aov.opinion_type_code = 'EVALUATION'
844 AND aov.pk3_value = P_ORGANIZATION_ID
845 AND aov.pk1_value = P_CONTROL_ID
846 AND aov.authored_date = (select max(aov2.authored_date)
847 from AMW_OPINIONS aov2
848 where aov2.object_opinion_type_id = aov.object_opinion_type_id
849 and aov2.pk3_value = aov.pk3_value
850 and aov2.pk1_value = aov.pk1_value);
851 ---AND aov.audit_result_code <> 'EFFECTIVE';
852 EXCEPTION
853 WHEN NO_DATA_FOUND THEN
854 CTRL_EFF := 'NT';
855 WHEN TOO_MANY_ROWS THEN
856 CTRL_EFF := 'N';
857 END;
858
859 RETURN CTRL_EFF;
860 END IS_CONTROL_EFFECTIVE;
861
862 FUNCTION GET_POLICY(P_CONTROL_ID IN NUMBER) RETURN VARCHAR2
863 IS
864 L_POLICY_NAME VARCHAR2(240);
865 L_IS_ITA_INSTALLED VARCHAR2(1);
866 BEGIN
867 BEGIN
868 SELECT 'Y'
869 INTO L_IS_ITA_INSTALLED
870 FROM FND_PRODUCT_INSTALLATIONS
871 WHERE APPLICATION_ID=438;
872 EXCEPTION
873 WHEN NO_DATA_FOUND THEN
874 L_IS_ITA_INSTALLED := 'N';
875 END;
876
877 ---NPANANDI 11.29.2004: HANDLING CALL TO ITA_POLICY_VL DYNAMICALLY
878 IF(L_IS_ITA_INSTALLED IS NOT NULL AND L_IS_ITA_INSTALLED = 'Y')THEN
879 BEGIN
880 EXECUTE IMMEDIATE 'SELECT POLICY_NAME FROM ITA_POLICY_VL WHERE CONTROL_ID=:B1' INTO L_POLICY_NAME USING P_CONTROL_ID;
881 EXCEPTION
882 WHEN NO_DATA_FOUND THEN
883 L_POLICY_NAME := NULL;
884 WHEN OTHERS THEN
885 L_POLICY_NAME := NULL;
886 END;
887 END IF;
888
889 RETURN L_POLICY_NAME;
890 END GET_POLICY;
891
892 PROCEDURE IS_WKFLW_APPR_DISBLD(
893 P_CONTROL_REV_ID IN NUMBER
894 ,P_PROFILE_OPTION OUT NOCOPY VARCHAR2
895 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
896 ,x_return_status OUT NOCOPY VARCHAR2
897 ,x_msg_count OUT NOCOPY NUMBER
898 ,x_msg_data OUT NOCOPY VARCHAR2
899 )
900 IS
901 CURSOR c_old_appr_CTRL(p_CTRL_rev_id In NUMBER) IS
902 SELECT CTRL2.Control_REV_ID
903 FROM amw_controls_b CTRL1 , amw_controls_b CTRL2
904 WHERE CTRL1.Control_id = CTRL2.control_id
905 AND CTRL1.control_rev_id = p_CTRL_rev_id
906 AND CTRL2.curr_approved_flag= 'Y'
907 and CTRL2.latest_revision_flag = 'N';
908
909 L_PROFILE_OPTION_VALUE VARCHAR2(1);
910 L_WKFLW_APPR_DISBLD VARCHAR2(1);
911 L_OLD_APPR_CTRL_REV_ID NUMBER;
912
913 L_API_NAME CONSTANT VARCHAR2(30) := 'IS_WKFLW_APPR_DISBLD';
914 L_DATE DATE;
915 BEGIN
916 x_return_status := FND_API.G_RET_STS_SUCCESS;
917 IF FND_API.to_Boolean( p_init_msg_list ) THEN
918 FND_MSG_PUB.initialize;
919 END IF;
920
921 IF FND_GLOBAL.User_Id IS NULL THEN
922 AMW_Utility_PVT.Error_Message(p_message_name => 'USER_PROFILE_MISSING');
923 RAISE FND_API.G_EXC_ERROR;
924 END IF;
925
926 L_PROFILE_OPTION_VALUE := NVL(fnd_profile.VALUE('AMW_DISABLE_WORKFLOW_APPROVAL'),'N');
927 --HARDCODING PURELY FOR TESTINGP PURPOSES!! REMOVE THIS ASAP!!!
928 --L_PROFILE_OPTION_VALUE := 'Y';
929
930 IF(L_PROFILE_OPTION_VALUE = 'N')THEN
931 L_WKFLW_APPR_DISBLD := 'N';
932 ELSE
933 L_WKFLW_APPR_DISBLD := 'Y';
934 ---DO THE PROCESSING HERE
935 OPEN c_old_appr_CTRL(P_CONTROL_REV_ID) ;
936 FETCH c_old_appr_CTRL INTO l_old_appr_CTRL_rev_id;
937 CLOSE c_old_appr_CTRL;
938
939 update amw_controls_b
940 set approval_status='A'
941 --,object_version_number=object_version_number+1
942 ,curr_approved_flag='Y'
943 ,latest_revision_flag ='Y'
944 ,approval_date=SYSDATE
945 ,LAST_UPDATE_DATE=SYSDATE
946 ,LAST_UPDATED_BY=G_USER_ID
947 ,LAST_UPDATE_LOGIN=G_LOGIN_ID
948 where control_rev_id=P_CONTROL_REV_ID;
949
950 IF (l_old_appr_CTRL_rev_id IS NOT NULL) THEN
951 UPDATE AMW_CONTROLS_B
952 SET END_DATE=SYSDATE
953 ,CURR_APPROVED_FLAG='N'
954 ,LATEST_REVISION_FLAG='N'
955 --,OBJECT_VERSION_NUMBER=OBJECT_VERSION_NUMBER+1
956 ,LAST_UPDATE_DATE=SYSDATE
957 ,LAST_UPDATED_BY=G_USER_ID
958 ,LAST_UPDATE_LOGIN=G_LOGIN_ID
959 WHERE CONTROL_REV_ID=l_old_appr_CTRL_rev_id;
960 END IF;
961 END IF;
962
963 p_profile_option := L_WKFLW_APPR_DISBLD;
964 EXCEPTION
965 WHEN FND_API.G_EXC_ERROR THEN
966 ROLLBACK;
967 x_return_status := FND_API.G_RET_STS_ERROR;
968 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,p_count => x_msg_count,p_data => x_msg_data);
969
970 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
971 ROLLBACK;
972 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
973 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,p_count => x_msg_count,p_data => x_msg_data);
974
975 WHEN OTHERS THEN
976 ROLLBACK;
977 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
978 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
979 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,l_api_name);
980 END IF;
981 FND_MSG_PUB.Count_And_Get (p_encoded => FND_API.G_FALSE,p_count => x_msg_count,p_data => x_msg_data);
982 END IS_WKFLW_APPR_DISBLD;
983
984 ---------------------------------------------------------------------
985 ----npanandi 12.02.2004: Added below function to get UnitOfMeasureTL
986 ----given UoM_Code, and UoM_Class (from Profile Option)
987 ---------------------------------------------------------------------
988 ---- modified by qliu to get the uom from amw_lookups
989 FUNCTION GET_UOM_TL(P_UOM_CODE IN VARCHAR2) RETURN VARCHAR2
990 IS
991 -- L_UOM_CLASS MTL_UNITS_OF_MEASURE_VL.UOM_CLASS%TYPE;
992 -- LX_UNIT_OF_MEASURE_TL MTL_UNITS_OF_MEASURE_VL.UNIT_OF_MEASURE_TL%TYPE;
993 L_UOM_CLASS AMW_LOOKUPS.LOOKUP_CODE%TYPE;
994 LX_UNIT_OF_MEASURE_TL AMW_LOOKUPS.MEANING%TYPE;
995 BEGIN
996 /*
997 L_UOM_CLASS := FND_PROFILE.VALUE('AMW_CTRL_UOM_CLASS');
998
999 BEGIN
1000 SELECT UNIT_OF_MEASURE_TL
1001 INTO LX_UNIT_OF_MEASURE_TL
1002 FROM MTL_UNITS_OF_MEASURE_VL
1003 WHERE UOM_CLASS=L_UOM_CLASS
1004 AND UOM_CODE=P_UOM_CODE;
1005 --AND BASE_UOM_FLAG='Y';
1006 EXCEPTION
1007 WHEN NO_DATA_FOUND THEN
1008 LX_UNIT_OF_MEASURE_TL := NULL;
1009 WHEN OTHERS THEN
1010 LX_UNIT_OF_MEASURE_TL := NULL;
1011 END;
1012 */
1013 BEGIN
1014 SELECT meaning
1015 INTO LX_UNIT_OF_MEASURE_TL
1016 FROM AMW_LOOKUPS
1017 WHERE lookup_type='AMW_CONTROL_FREQUENCY'
1018 AND lookup_code=P_UOM_CODE;
1019 EXCEPTION
1020 WHEN NO_DATA_FOUND THEN
1021 LX_UNIT_OF_MEASURE_TL := NULL;
1022 WHEN OTHERS THEN
1023 LX_UNIT_OF_MEASURE_TL := NULL;
1024 END;
1025
1026 RETURN LX_UNIT_OF_MEASURE_TL;
1027 END GET_UOM_TL;
1028
1029 ---------------------------------------------------------------------
1030 ----npanandi 12.03.2004: Added below function to check
1031 ----if this Ctrl contains this CtrlPurposeCode or not
1032 ---------------------------------------------------------------------
1033 FUNCTION PURPOSE_PRESENT (
1034 P_CONTROL_REV_ID IN NUMBER,
1035 P_PURPOSE_CODE IN VARCHAR2) RETURN VARCHAR2
1036 IS
1037 n number;
1038 BEGIN
1039 select count(*)
1040 into n
1041 from amw_control_purposes
1042 where control_rev_id = P_CONTROL_REV_ID
1043 and PURPOSE_code = P_PURPOSE_CODE;
1044
1045 if n > 0 then
1046 return 'Y';
1047 else
1048 return 'N';
1049 end if;
1050 END PURPOSE_PRESENT;
1051
1052 ------------------------------------------------------------------------------------------------------------
1053 FUNCTION NEW_PURPOSE_PRESENT (
1054 P_CONTROL_REV_ID IN NUMBER,
1055 P_PURPOSE_CODE IN VARCHAR2) RETURN VARCHAR2
1056 IS
1057 n number;
1058 yes varchar2(80);
1059 no varchar2(80);
1060 BEGIN
1061 select count(*)
1062 into n
1063 from amw_control_PURPOSES
1064 where control_rev_id = P_CONTROL_REV_ID
1065 and PURPOSE_code = P_PURPOSE_CODE;
1066
1067 select meaning
1068 into yes
1069 from fnd_lookups
1070 where lookup_type='YES_NO'
1071 and lookup_code='Y';
1072
1073 select meaning
1074 into no
1075 from fnd_lookups
1076 where lookup_type='YES_NO'
1077 and lookup_code='N';
1078
1079 if n > 0 then
1080 return yes;
1081 else
1082 return no;
1083 end if;
1084 END NEW_PURPOSE_PRESENT;
1085
1086 ---------------------------------------------------------------------
1087 ----npanandi 12.03.2004: Added below function to insert
1088 ----CtrlPurposeCode for this CtrlRevId
1089 ---------------------------------------------------------------------
1090 PROCEDURE PROCESS_PURPOSE(
1091 p_init_msg_list IN VARCHAR2,
1092 p_commit IN VARCHAR2,
1093 p_validate_only IN VARCHAR2,
1094 p_select_flag IN VARCHAR2,
1095 p_control_rev_id IN NUMBER,
1096 p_PURPOSE_code IN VARCHAR2,
1097 x_return_status OUT NOCOPY VARCHAR2,
1098 x_msg_count OUT NOCOPY NUMBER,
1099 x_msg_data OUT NOCOPY VARCHAR2)
1100 IS
1101 l_creation_date date;
1102 l_created_by number;
1103 l_last_update_date date;
1104 l_last_updated_by number;
1105 l_last_update_login number;
1106 l_control_PURPOSE_id number;
1107 BEGIN
1108 -- create savepoint if p_commit is true
1109 IF p_commit = FND_API.G_TRUE THEN
1110 SAVEPOINT process_PURPOSE_save_point;
1111 END IF;
1112
1113 -- initialize message list if p_init_msg_list is set to true
1114 if FND_API.to_boolean(nvl(p_init_msg_list, FND_API.G_FALSE)) then
1115 fnd_msg_pub.initialize;
1116 end if;
1117
1118 -- initialize return status to success
1119 x_return_status := fnd_api.g_ret_sts_success;
1120
1121 delete from amw_control_PURPOSES
1122 where control_rev_id = p_control_rev_id
1123 and PURPOSE_code = p_PURPOSE_code;
1124
1125 if (p_select_flag = 'Y') then
1126 insert into amw_control_PURPOSES(
1127 control_PURPOSE_id
1128 ,control_rev_id
1129 ,PURPOSE_codE
1130 ,creation_date
1131 ,created_by
1132 ,last_update_date
1133 ,last_updated_by
1134 ,last_update_login) values (
1135 AMW_CONTROL_PURPOSES_S.NEXTVAL
1136 ,p_control_rev_id
1137 ,p_PURPOSE_code
1138 ,SYSDATE
1139 ,FND_GLOBAL.USER_ID
1140 ,SYSDATE
1141 ,FND_GLOBAL.USER_ID
1142 ,FND_GLOBAL.LOGIN_ID);
1143 end if;
1144 EXCEPTION
1145 WHEN FND_API.G_EXC_ERROR THEN
1146 IF p_commit = FND_API.G_TRUE THEN
1147 ROLLBACK TO process_PURPOSE_save_point;
1148 END IF;
1149 x_return_status := FND_API.G_RET_STS_ERROR;
1150 fnd_msg_pub.count_and_get(p_count => x_msg_count,p_data => x_msg_data);
1151 WHEN OTHERS THEN
1152 IF p_commit = FND_API.G_TRUE THEN
1153 ROLLBACK TO process_PURPOSE_save_point;
1154 END IF;
1155 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1156 fnd_msg_pub.add_exc_msg(p_pkg_name => 'AMW_CONTROLS_PAGE_PKG',
1157 p_procedure_name => 'PROCESS_PURPOSE',
1158 p_error_text => SUBSTRB(SQLERRM,1,240));
1159 fnd_msg_pub.count_and_get(p_count => x_msg_count,p_data => x_msg_data);
1160 END PROCESS_PURPOSE;
1161
1162
1163
1164 FUNCTION get_control_objective_rl(
1165 p_process_id in number,
1166 p_risk_id in number,
1167 p_control_id in number,
1168 p_rev in number) RETURN VARCHAR2
1169 IS
1170 l_obj varchar2(2000);
1171 BEGIN
1172 select vl.name
1173 into l_obj
1174 from amw_process ap,
1175 amw_objective_associations ao,
1176 amw_process_objectives_vl vl
1177 where ao.object_type = 'CONTROL'
1178 and ao.pk1 = p_process_id
1179 and ao.pk2 = p_risk_id
1180 and ao.pk3 = p_control_id
1181 and ap.process_id = p_process_id
1182 and ap.revision_number = p_rev
1183 and ((ap.approval_date is null and ap.end_date is null and ao.deletion_date is null) OR
1184 (ap.approval_date is not null and ao.approval_date <= ap.approval_date and
1185 (ao.deletion_approval_date is null or ao.deletion_approval_date >= ap.approval_end_date)))
1186 and vl.process_objective_id = ao.process_objective_id;
1187
1188 RETURN l_obj;
1189
1190 EXCEPTION
1191 WHEN OTHERS THEN
1192 return null;
1193
1194 END get_control_objective_rl;
1195
1196 END AMW_CONTROLS_PAGE_PKG;
[Home] [Help]