[Home] [Help]
PACKAGE BODY: APPS.AMW_LOAD_RC_DATA
Source
1 PACKAGE BODY AMW_LOAD_RC_DATA AS
2 /* $Header: amwrcldb.pls 120.4.12000000.1 2007/01/16 20:40:57 appldev ship $ */
3 /*****************************************************************************/
4 /*****************************************************************************/
5 /*****************************************************************************/
6 /* Major Functionality of the followIng procedure includes: */
7 /* Reads the amw_risk-ctrl_interface table */
8 /* following tables: */
9 /* INSERTS OR UPDATES ARE DONE AGAINIST THE FOLLOWING TABLES */
10 /* Insert into AMW_RISKS_B and AMW_RISKS_TL */
11 /* Insert into AMW_CONTROLS_B and AMW_CONTROLS_TL */
12 /* Insert into AMW_CONTROL_ASSOCIATIONS */
13 /* Insert into AMW_RISK_ASSOCIATIONS */
14 /* Insert into AMW_CONTROL_OBJECTIVES */
15 /* Insert into AMW_CONTROL_ASSERTIONS */
16 /* Updates amw_risk-ctrl_interface, with error messages */
17 /* Deleting successful production inserts, based on profile */
18 /* */
19 /*****************************************************************************/
20 --
21 -- Used for exception processing
22 --
23
24 G_USER_ID NUMBER := FND_GLOBAL.USER_ID;
25 G_LOGIN_ID NUMBER := FND_GLOBAL.CONC_LOGIN_ID;
26 v_error_found BOOLEAN DEFAULT FALSE;
27 v_user_id NUMBER;
28 v_interface_id NUMBER;
29 vx_control_rev_id NUMBER;
30 vx_process_objective_id NUMBER := null;
31 lx_risk_rev_id NUMBER;
32 v_err_msg VARCHAR2 (2000);
33 v_table_name VARCHAR2 (240);
34
35 ---02.03.2005 npanandi: increased Varchar2 length of v_import_func
36 ---from 30 to 480 per FND mandate
37 v_import_func CONSTANT VARCHAR2(480) := 'AMW_DATA_IMPORT';
38
39 v_risk_db_approval_status VARCHAR2(30);
40 v_control_db_approval_status VARCHAR2(30);
41
42 v_invalid_requestor_msg VARCHAR2(2000);
43 v_no_import_privilege_msg VARCHAR2(2000);
44 v_invalid_risk_type VARCHAR2(2000);
45
46 v_risk_pending_msg VARCHAR2(2000);
47 v_control_pending_msg VARCHAR2(2000);
48
49 v_valid_risk_type number := 0;
50
51 --
52 -- function to check the user access privilege
53 --
54 FUNCTION Has_Import_Privilege RETURN Boolean
55 IS
56 CURSOR c_func_exists IS
57 SELECT 'Y'
58 FROM fnd_responsibility r, fnd_compiled_menu_functions m, fnd_form_functions f
59 WHERE r.responsibility_id = fnd_global.resp_id
60 AND r.application_id=fnd_global.resp_appl_id
61 AND r.menu_id = m.menu_id
62 AND m.function_id = f.function_id
63 AND f.function_name = v_import_func;
64
65 CURSOR c_func_excluded IS
66 SELECT 'Y'
67 FROM fnd_resp_functions rf, fnd_form_functions f
68 WHERE rf.application_id = fnd_global.resp_appl_id
69 AND rf.responsibility_id = fnd_global.resp_id
70 AND rf.rule_type = 'F'
71 AND rf.action_id = f.function_id
72 AND f.function_name = v_import_func;
73
74 l_func_exists VARCHAR2(1);
75 l_func_excluded VARCHAR2(1);
76 BEGIN
77 OPEN c_func_exists;
78 FETCH c_func_exists INTO l_func_exists;
79 IF c_func_exists%NOTFOUND THEN
80 CLOSE c_func_exists;
81 return FALSE;
82 END IF;
83 CLOSE c_func_exists;
84
85 OPEN c_func_excluded;
86 FETCH c_func_excluded INTO l_func_excluded;
87 CLOSE c_func_excluded;
88
89 IF l_func_excluded is not null THEN
90 return FALSE;
91 END IF;
92
93 return TRUE;
94 END Has_Import_Privilege;
95
96
97 FUNCTION Risk_Can_Be_Processed RETURN Boolean
98 IS
99 BEGIN
100 IF v_risk_db_approval_status = 'P' THEN
101 update_interface_with_error (v_risk_pending_msg
102 ,'AMW_RISKS'
103 ,v_interface_id);
104 return FALSE;
105 END IF;
106 return TRUE;
107 END Risk_Can_Be_Processed;
108
109
110 FUNCTION Control_Can_Be_Processed RETURN Boolean
111 IS
112 BEGIN
113 IF v_control_db_approval_status = 'P' THEN
114 update_interface_with_error (v_control_pending_msg
115 ,'AMW_CONTROLS'
116 ,v_interface_id);
117 return FALSE;
118 END IF;
119 return TRUE;
120 END Control_Can_Be_Processed;
121
122 /*****************************************************************************/
123 /*****************************************************************************/
124 PROCEDURE create_risks_and_controls (
125 errbuf OUT NOCOPY VARCHAR2
126 ,retcode OUT NOCOPY VARCHAR2
127 ,p_batch_id IN NUMBER
128 ,p_user_id IN NUMBER
129 )
130 IS
131 /****************************************************/
132 CURSOR risk_controls_cur
133 IS
134 SELECT risk_approval_status_code
135 ,control_automation_type_code
136 ,control_description
137 ,control_location_code
138 ,control_name
139 ,control_type_code
140 ,control_application_id
141 ,risk_impact_code
142 ,risk_control_interface_id
143 ,control_job_id
144 ,risk_likelihood_code
145 ,physical_evidence
146 ,process_name
147 ,process_code
148 ,risk_description
149 ,risk_name
150 ,risk_type_code
151 ,control_source
152 ,control_approval_status_code
153 ,process_id
154 ,parent_process_id
155 ,process_objective_id
156 ,process_objective_name
157 ,process_obj_description
158 ,upper(material) as material
159 ,decode(nvl(upper(material),'N'),'N',null,material_value) as material_value
160 ,ap_name
161 ,upper(nvl(design_effectiveness,'N')) as design_effectiveness
162 ,upper(nvl(op_effectiveness,'N')) as op_effectiveness
163 ,preventive_control
164 ,detective_control
165 ,disclosure_control
166 ,key_mitigating
167 ,verification_source
168 ,verification_source_name
169 ,verification_instruction
170 ,risk_type1
171 ,risk_type2
172 ,risk_type3
173 ,risk_type4
174 ,risk_type5
175 ,risk_type6
176 ,risk_type7
177 ,risk_type8
178 ,risk_type9
179 ,risk_type10
180 ,risk_type11
181 ,risk_type12
182 ,risk_type13
183 ,risk_type14
184 ,risk_type15
185 ,risk_type16
186 ,risk_type17
187 ,risk_type18
188 ,risk_type19
189 ,risk_type20
190 ,risk_type21
191 ,risk_type22
192 ,risk_type23
193 ,risk_type24
194 ,risk_type25
195 ,risk_type26
196 ,risk_type27
197 ,risk_type28
198 ,risk_type29
199 ,risk_type30
200 ,control_comp1
201 ,control_comp2
202 ,control_comp3
203 ,control_comp4
204 ,control_comp5
205 ,control_comp6
206 ,control_comp7
207 ,control_comp8
208 ,control_comp9
209 ,control_comp10
210 ,control_comp11
211 ,control_comp12
212 ,control_comp13
213 ,control_comp14
214 ,control_comp15
215 ,control_comp16
216 ,control_comp17
217 ,control_comp18
218 ,control_comp19
219 ,control_comp20
220 ,control_comp21
221 ,control_comp22
222 ,control_comp23
223 ,control_comp24
224 ,control_comp25
225 ,control_comp26
226 ,control_comp27
227 ,control_comp28
228 ,control_comp29
229 ,control_comp30
230 ,control_obj1
231 ,control_obj2
232 ,control_obj3
233 ,control_obj4
234 ,control_obj5
235 ,control_obj6
236 ,control_obj7
237 ,control_obj8
238 ,control_obj9
239 ,control_obj10
240 ,control_obj11
241 ,control_obj12
242 ,control_obj13
243 ,control_obj14
244 ,control_obj15
245 ,control_obj16
246 ,control_obj17
247 ,control_obj18
248 ,control_obj19
249 ,control_obj20
250 ,control_obj21
251 ,control_obj22
252 ,control_obj23
253 ,control_obj24
254 ,control_obj25
255 ,control_obj26
256 ,control_obj27
257 ,control_obj28
258 ,control_obj29
259 ,control_obj30
260 ,control_assert1
261 ,control_assert2
262 ,control_assert3
263 ,control_assert4
264 ,control_assert5
265 ,control_assert6
266 ,control_assert7
267 ,control_assert8
268 ,control_assert9
269 ,control_assert10
270 ,control_assert11
271 ,control_assert12
272 ,control_assert13
273 ,control_assert14
274 ,control_assert15
275 ,control_assert16
276 ,control_assert17
277 ,control_assert18
278 ,control_assert19
279 ,control_assert20
280 ,control_assert21
281 ,control_assert22
282 ,control_assert23
283 ,control_assert24
284 ,control_assert25
285 ,control_assert26
286 ,control_assert27
287 ,control_assert28
288 ,control_assert29
289 ,control_assert30
290 ,revise_risk_flag
291 ,revise_ctrl_flag
292 --npanandi 12.10.2004: added the foll
293 --for Risk/Ctrl Classification
294 ,risk_classification
295 ,ctrl_classification
296 ,uom_code
297 FROM amw_risk_ctrl_interface
298 WHERE created_by = DECODE (p_user_id, NULL, created_by, p_user_id)
299 AND batch_id = DECODE (p_batch_id, NULL, batch_id, p_batch_id)
300 AND process_flag IS NULL
301 AND error_flag IS NULL;
302
303 CURSOR c_requestor_id IS
304 SELECT party_id
305 FROM amw_employees_current_v
306 WHERE employee_id = (select employee_id
307 from fnd_user
308 where user_id = p_user_id)
309 AND rownum = 1;
310
311 CURSOR c_risk_exists (c_risk_name IN VARCHAR2) IS
312 SELECT b.risk_id, b.approval_status
313 FROM amw_risks_b b, amw_risks_tl tl
314 WHERE tl.name = c_risk_name
315 AND tl.language = USERENV('LANG')
316 AND tl.risk_rev_id = b.risk_rev_id
317 AND b.latest_revision_flag='Y';
318
319 CURSOR c_control_exists (c_control_name IN VARCHAR2) IS
320 SELECT b.control_id, b.approval_status
321 FROM amw_controls_b b, amw_controls_tl tl
322 WHERE tl.name = c_control_name
323 AND tl.language = USERENV('LANG')
324 AND tl.control_rev_id = b.control_rev_id
325 AND b.latest_revision_flag='Y';
326
327 --
328 --npanandi 11.24.2004 added for ProcessId check handling
329 --in Pl/Sql API instead of GroupValidator
330 --
331 CURSOR C_CHECK_VALIDITY IS
332 SELECT PROCESS_ID
333 ,PROCESS_NAME
334 ,PROCESS_CODE
335 ,risk_control_interface_id
336 FROM AMW_RISK_CTRL_INTERFACE
337 WHERE CREATED_BY=DECODE(P_USER_ID,NULL,CREATED_BY,P_USER_ID)
338 AND BATCH_ID=DECODE(p_batch_id, NULL, batch_id, p_batch_id)
339 AND process_flag IS NULL
340 AND error_flag IS NULL
341 ---NPANANDI 12.09.2004: FIX TO CHECK ONLY
342 ---IF PROCESS NAME ARE EITHER DEFINED
343 AND NOT (PROCESS_CODE IS NULL AND PROCESS_NAME IS NULL)
344 ORDER BY BATCH_ID DESc;
345
346 l_api_version_number CONSTANT NUMBER := 1.0;
347 l_requestor_id NUMBER;
348 l_amw_delt_risk_cntl_intf VARCHAR2 (2);
349 l_amw_control_name_prefix VARCHAR2 (30);
350 l_risk_rec AMW_RISK_PVT.risk_rec_type;
351 l_control_rec AMW_CONTROL_PVT.control_rec_type;
352 l_control_found BOOLEAN default true;
353 l_process_obj_column BOOLEAN default true;
354 l_risk_approval_status_code VARCHAR2(30);
355 l_control_approval_status_code VARCHAR2(30);
356 l_control_name VARCHAR2(240);
357 l_process_id NUMBER;
358 l_process_objective_id NUMBER;
359 l_application_id NUMBER;
360 l_process_objectives_count number;
361 l_risk_objectives_count number;
362
363 lx_return_status VARCHAR2(30);
364 lx_msg_count NUMBER;
365 lx_msg_data VARCHAR2(2000);
366 lx_risk_id NUMBER;
367 lx_control_id NUMBER;
368 lx_mode_affected VARCHAR2(30);
369 l_object_type_count NUMBER;
370 l_process_flag VARCHAR2(1);
371 e_no_import_access EXCEPTION;
372 e_invalid_requestor_id EXCEPTION;
373 e_invalid_risk_type EXCEPTION;
374
375 l_revise_risk_flag VARCHAR2(1);
376 l_revise_control_flag VARCHAR2(1);
377 L_PROCESS_CODE VARCHAR2(30);
378
379 L_CONTROL_ASSOCIATION_ID NUMBER;
380 L_APPROVAL_DATE DATE;
381 L_AP_ASSOCIATION_ID NUMBER;
382
383 L_PROC_OBJ_ASSOCIATION_ID NUMBER;
384 L_PROC_OBJ_APPROVAL_DATE DATE;
385 L_RISK_OBJ_ASSOCIATION_ID NUMBER;
386 L_RISK_OBJ_APPROVAL_DATE DATE;
387 L_RISK_ASSOCIATION_ID NUMBER;
388 L_RISK_APPROVAL_DATE DATE;
389 L_AP_APPROVAL_DATE DATE;
390 L_ASSOC_AP_TO_CTRL BOOLEAN DEFAULT FALSE;
391
392 ---01.13.2005 NPANANDI: ADDED BELOW VARS FOR CTRL TO OBJ ASSOCIATION
393 L_CTRL_OBJ_ASSOCIATION_ID NUMBER;
394 L_CTRL_OBJ_APPROVAL_DATE DATE;
395 l_ctrl_objective_id number;
396 ---02.28.2005 npanandi: added below variables for data security check
397 l_new_risk boolean default true;
398 l_has_risk_access varchar2(15);
399 l_new_control boolean default true;
400 l_has_ctrl_access varchar2(15);
401
402 ---03.02.2005 npanandi: added below vars for data security checks
403 l_has_ap_access varchar2(15) := 'T'; --defaulting to 'T', which means 'has access'
404 l_has_risk_ctrl_assn_access varchar2(15) := 'T'; --defaulting to 'T', which means 'has access'
405 l_has_proc_obj_assoc_access varchar2(15) := 'T'; --defaulting to 'T', which means 'has access'
406 l_has_proc_risk_assoc_access varchar2(15) := 'T'; --defaulting to 'T', which means 'has access'
407 BEGIN
408 fnd_file.put_line (fnd_file.LOG, 'resp id: '||fnd_global.RESP_ID);
409 fnd_file.put_line (fnd_file.LOG, 'resp appl id: '||fnd_global.RESP_APPL_ID);
410
411 --
412 -- check access privilege
413 --
414
415 IF not Has_Import_Privilege THEN
416 RAISE e_no_import_access;
417 END IF;
418
419 --
420 -- get user requestor_id
421 --
422 v_user_id := p_user_id;
423
424 OPEN c_requestor_id;
425 FETCH c_requestor_id INTO l_requestor_id;
426 IF (c_requestor_id%NOTFOUND) THEN
427 CLOSE c_requestor_id;
428 RAISE e_invalid_requestor_id;
429 END IF;
430 CLOSE c_requestor_id;
431
432 --
433 -- get profile info for deleting records from interface table
434 --
435 l_amw_delt_risk_cntl_intf :=
436 NVL(fnd_profile.VALUE ('AMW_DELT_RISK_CNTRL_INTF'), 'N');
437
438 --
439 -- get profile info for null control names and descriptions
440 --
441 --02.01.2005 npanandi: fix for Customer to upload Controls with only sequence numbers
442 --l_amw_control_name_prefix := NVL(fnd_profile.VALUE ('AMW_CONTROL_NAME_PREFIX'), 'ORAC-');
443 --l_amw_control_name_prefix := fnd_profile.VALUE ('AMW_CONTROL_NAME_PREFIX');
444 l_amw_control_name_prefix := null;
445
446
447 AMW_UTILITY_PVT.Error_Message(p_message_name => 'AMW_PENDING_CHANGE_ERROR',
448 p_token_name => 'OBJ_TYPE',
449 p_token_value =>
450 AMW_UTILITY_PVT.GET_LOOKUP_MEANING('AMW_OBJECT_TYPE','RISK'));
451 v_risk_pending_msg := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.G_LAST,
452 p_encoded => fnd_api.g_false);
453
454 AMW_UTILITY_PVT.Error_Message(p_message_name => 'AMW_PENDING_CHANGE_ERROR',
455 p_token_name => 'OBJ_TYPE',
456 p_token_value =>
457 AMW_UTILITY_PVT.GET_LOOKUP_MEANING('AMW_OBJECT_TYPE','CTRL'));
458 v_control_pending_msg := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.G_LAST,
459 p_encoded => fnd_api.g_false);
460
461
462 ---
463 ---NPANANDI 11.24.2004
464 ---CHECK THE DATA VALIDITY BETWEEN PROCESS_CODE,PROCESS_NAME,PROCESS_ID
465 ---
466 FOR VALID_REC IN C_CHECK_VALIDITY LOOP
467 BEGIN
468
469 --PROCESS_NAME, ORG_ID ARE ALWAYS NON-NULL
470 --PROCESS_CODE CAN BE NULL
471 IF(VALID_REC.PROCESS_CODE IS NULL) THEN
472 SELECT PROCESS_CODE,PROCESS_ID INTO L_PROCESS_CODE,L_PROCESS_ID
473 FROM AMW_LATEST_REVISIONS_V
474 WHERE DISPLAY_NAME=VALID_REC.PROCESS_NAME;
475 ELSE
476 SELECT PROCESS_CODE,PROCESS_ID INTO L_PROCESS_CODE,L_PROCESS_ID
477 FROM AMW_LATEST_REVISIONS_V
478 WHERE DISPLAY_NAME=VALID_REC.PROCESS_NAME
479 AND PROCESS_CODE=VALID_REC.PROCESS_CODE;
480 END IF;
481 EXCEPTION
482 WHEN TOO_MANY_ROWS THEN
483 V_ERR_MSG := 'Multiple processes exist with same Process Name, please select Unique Process Code';
484 update_interface_with_error (v_err_msg
485 ,'AMW_RISKS'
486 ,VALID_REC.risk_control_interface_id);
487 WHEN NO_DATA_FOUND THEN
488 V_ERR_MSG := 'Please select valid combination of Process Name and Process Code';
489 update_interface_with_error (v_err_msg
490 ,'AMW_RISKS'
491 ,VALID_REC.risk_control_interface_id);
492 END;
493 END LOOP;
494
495 --
496 -- loop processing each record
497 --
498 FOR rc_rec IN risk_controls_cur LOOP
499 BEGIN
500 v_interface_id := rc_rec.RISK_CONTROL_INTERFACE_ID;
501 l_risk_approval_status_code := rc_rec.risk_approval_status_code;
502 l_control_approval_status_code :=
503 rc_rec.control_approval_status_code;
504
505 l_revise_risk_flag := upper(NVL(rc_rec.revise_risk_flag, 'N'));
506 l_revise_control_flag := upper(NVL(rc_rec.revise_ctrl_flag, 'N'));
507 --
508 -- process risk
509 --
510 lx_risk_id := null;
511 v_risk_db_approval_status := null;
512 OPEN c_risk_exists(rc_rec.risk_name);
513 FETCH c_risk_exists INTO lx_risk_id, v_risk_db_approval_status;
514 CLOSE c_risk_exists;
515
516 ---03.01.2005 npanandi: added data security checks
517 l_has_risk_access := 'T'; ---setting this to 'T' for new Risks
518 l_new_risk := true; ---setting this to TRUE to avoid conflict with previous loop value
519 if(lx_risk_id is not null) then
520 ---Check for Update privilege here
521 l_new_risk := false;
522 fnd_file.put_line (fnd_file.LOG, '************** Checking Update Privilege for rc_rec.risk_name: '||rc_rec.risk_name);
523 l_has_risk_access := check_function(
524 p_function => 'AMW_RISK_UPDATE_PRVLG'
525 ,p_object_name => 'AMW_RISK'
526 ,p_instance_pk1_value => lx_risk_id
527 ,p_user_id => fnd_global.user_id);
528 fnd_file.put_line (fnd_file.LOG, 'l_has_risk_access: '||l_has_risk_access);
529 fnd_file.put_line (fnd_file.LOG, '************** Checked Update Privilege for rc_rec.risk_name: '||rc_rec.risk_name);
530
531 IF l_has_risk_access <> 'T' then
532 v_err_msg := 'Cannot update this Risk';
533 update_interface_with_error (v_err_msg
534 ,'AMW_RISKS'
535 ,v_interface_id);
536 END IF;
537 end if;
538 ---03.01.2005 npanandi: added data security checks ends
539
540
541 L_PROCESS_ID := null;
542
543 FND_FILE.PUT_LINE(FND_FILE.LOG, 'GETTING PROCESS_ID');
544 --npanandi 12.09.2004: check to get processId
545 --only if processCode and processName are not both
546 --uniformly NULL
547 if(not (rc_rec.process_code is null and rc_rec.process_name is null))then
548 --executing these if blocks as before
549 if(rc_rec.process_code is null)then
550 FND_FILE.PUT_LINE(FND_FILE.LOG, 'v_interface_id: '||v_interface_id||', process_code is null --> display_name: '||rc_rec.process_name);
551 select process_id into l_process_id
552 from amw_latest_revisions_v
553 where display_name=rc_rec.process_name;
554 else
555 FND_FILE.PUT_LINE(FND_FILE.LOG, 'v_interface_id: '||v_interface_id||', process_code is exists --> display_name: '||rc_rec.process_name||', process_code: '||rc_rec.process_code);
556 select process_id into l_process_id
557 from amw_latest_revisions_v
558 where display_name=rc_rec.process_name
559 and process_code=rc_rec.process_code;
560 end if;
561 end if; --end of npanandi check 12.09.2004
562 FND_FILE.PUT_LINE(FND_FILE.LOG, 'GOT PROCESS_ID: '||l_process_id);
563
564 IF((L_PROCESS_ID IS NOT NULL) AND (L_PROCESS_ID <> -1)) THEN
565 FND_FILE.PUT_LINE(FND_FILE.LOG, '>>>>>>> Revising ProcessId: '||L_PROCESS_ID);
566 AMW_RL_HIERARCHY_PKG.revise_process_if_necessary(
567 P_PROCESS_ID => L_PROCESS_ID
568 );
569 END IF;
570
571
572 IF Risk_Can_Be_Processed AND
573 ---03.01.2005 npanandi: added check for lHasRiskAccess to update this Risk
574 ---only if this user has Upd privilege
575 l_has_risk_access = 'T' and
576 (lx_risk_id is null OR
577 l_revise_risk_flag = 'Y') THEN
578 l_risk_rec.risk_impact := rc_rec.risk_impact_code;
579 l_risk_rec.risk_type := rc_rec.risk_type_code;
580 l_risk_rec.approval_status := l_risk_approval_status_code;
581 l_risk_rec.likelihood := rc_rec.risk_likelihood_code;
582 l_risk_rec.risk_name := rc_rec.risk_name;
583 l_risk_rec.risk_description := rc_rec.risk_description;
584 l_risk_rec.requestor_id := l_requestor_id;
585 l_risk_rec.material := nvl(rc_rec.material,'N');
586 --npanandi 12.10.2004: added the following for Risk Classification
587 l_risk_rec.classification := rc_rec.risk_classification;
588 /** Added this by Nilesh 08/29/2003
589 Currently Tsun Tsun's API needs a valid value for risk_type_code
590 But due to multiple Risk_Type_Associations, use of
591 Risk_Type_Code in amw_risks_b is now obsoleted
592 Hence passing a blank field!
593 **/
594 l_risk_rec.risk_type := 'C';
595
596 AMW_RISK_PVT.Load_Risk(
597 p_api_version_number => l_api_version_number,
598 p_init_msg_list => FND_API.G_TRUE,
599 p_commit => FND_API.G_FALSE,
600 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
601 p_risk_rec => l_risk_rec,
602 x_return_status => lx_return_status,
603 x_msg_count => lx_msg_count,
604 x_msg_data => lx_msg_data,
605 x_risk_rev_id => lx_risk_rev_id,
606 x_risk_id => lx_risk_id
607 );
608
609 ---03.01.2005 npanandi: if new Control, grant CtrlOwner prvlg
610 if(l_new_risk) then
611 add_owner_privilege(
612 p_role_name => 'AMW_RISK_OWNER_ROLE'
613 ,p_object_name => 'AMW_RISK'
614 ,p_grantee_type => 'P'
615 ,p_instance_pk1_value => lx_risk_id
616 ,p_user_id => FND_GLOBAL.USER_ID);
617 end if;
618 ---02.28.2005 npanandi: if new Control, grant CtrlOwner prvlg
619
620 fnd_file.put_line (fnd_file.LOG,'lx_return_status: '||lx_return_status);
621 fnd_file.put_line (fnd_file.LOG,'lx_risk_rev_id: '||lx_risk_rev_id);
622 fnd_file.put_line (fnd_file.LOG,'lx_risk_id: '||lx_risk_id);
623
624 IF lx_return_status <> FND_API.G_RET_STS_SUCCESS then
625 v_err_msg := ' ';
626 FOR x IN 1..lx_msg_count LOOP
627 if(length(v_err_msg) < 1800) then
628 v_err_msg := v_err_msg||' '||substr(fnd_msg_pub.get(p_msg_index => x,
629 p_encoded => fnd_api.g_false), 1,100);
630 end if;
631 END LOOP;
632 update_interface_with_error (v_err_msg
633 ,'AMW_RISKS'
634 ,v_interface_id);
635 END IF;
636
637 --
638 -- process risk types
639 --
640 /*** 05.19.2006 npanandi: bug 5210357 fix --- setting the global parameter value to zero, else
641 Risks with no RiskTypes get uploaded ***/
642 v_valid_risk_type := 0;
643 /*** 05.19.2006 npanandi: bug 5210357 fix ends ***/
644
645 IF NOT v_error_found THEN
646 risk_types(rc_rec.risk_type1, '1', 'n');
647 risk_types(rc_rec.risk_type2, '2', 'n');
648 risk_types(rc_rec.risk_type3, '3', 'n');
649 risk_types(rc_rec.risk_type4, '4', 'n');
650 risk_types(rc_rec.risk_type5, '5', 'n');
651 risk_types(rc_rec.risk_type6, '6', 'n');
652 risk_types(rc_rec.risk_type7, '7', 'n');
653 risk_types(rc_rec.risk_type8, '8', 'n');
654 risk_types(rc_rec.risk_type9, '9', 'n');
655 risk_types(rc_rec.risk_type10, '10', 'n');
656 risk_types(rc_rec.risk_type11, '11', 'n');
657 risk_types(rc_rec.risk_type12, '12', 'n');
658 risk_types(rc_rec.risk_type13, '13', 'n');
659 risk_types(rc_rec.risk_type14, '14', 'n');
660 risk_types(rc_rec.risk_type15, '15', 'n');
661 risk_types(rc_rec.risk_type16, '16', 'n');
662 risk_types(rc_rec.risk_type17, '17', 'n');
663 risk_types(rc_rec.risk_type18, '18', 'n');
664 risk_types(rc_rec.risk_type19, '19', 'n');
665 risk_types(rc_rec.risk_type20, '20', 'n');
666 risk_types(rc_rec.risk_type21, '21', 'n');
667 risk_types(rc_rec.risk_type22, '22', 'n');
668 risk_types(rc_rec.risk_type23, '23', 'n');
669 risk_types(rc_rec.risk_type24, '24', 'n');
670 risk_types(rc_rec.risk_type25, '25', 'n');
671 risk_types(rc_rec.risk_type26, '26', 'n');
672 risk_types(rc_rec.risk_type27, '27', 'n');
673 risk_types(rc_rec.risk_type28, '28', 'n');
674 risk_types(rc_rec.risk_type29, '29', 'n');
675 risk_types(rc_rec.risk_type30, '30', 'y');
676 END IF; --end of if for v_error_found
677 END IF; -- end of process risk
678 --
679 -- only process control then control info exists
680 --
681 l_control_found := true;
682 IF rc_rec.control_automation_type_code is null
683 AND rc_rec.control_description is null
684 AND rc_rec.control_location_code is null
685 AND rc_rec.control_name is null
686 AND rc_rec.control_type_code is null
687 AND rc_rec.control_job_id is null
688 AND rc_rec.physical_evidence is null
689 AND rc_rec.control_source is null
690 AND rc_rec.preventive_control is null
691 AND rc_rec.detective_control is null
692 AND rc_rec.disclosure_control is null
693 AND rc_rec.key_mitigating is null
694 AND rc_rec.verification_source is null
695 AND rc_rec.verification_source_name is null
696 AND rc_rec.verification_instruction is null
697 AND rc_rec.control_comp1 is null
698 AND rc_rec.control_comp2 is null
699 AND rc_rec.control_comp3 is null
700 AND rc_rec.control_comp4 is null
701 AND rc_rec.control_comp5 is null
702 AND rc_rec.control_comp6 is null
703 AND rc_rec.control_comp7 is null
704 AND rc_rec.control_comp8 is null
705 AND rc_rec.control_comp9 is null
706 AND rc_rec.control_comp10 is null
707 AND rc_rec.control_comp11 is null
708 AND rc_rec.control_comp12 is null
709 AND rc_rec.control_comp13 is null
710 AND rc_rec.control_comp14 is null
711 AND rc_rec.control_comp15 is null
712 AND rc_rec.control_comp16 is null
713 AND rc_rec.control_comp17 is null
714 AND rc_rec.control_comp18 is null
715 AND rc_rec.control_comp19 is null
716 AND rc_rec.control_comp20 is null
717 AND rc_rec.control_comp21 is null
718 AND rc_rec.control_comp22 is null
719 AND rc_rec.control_comp23 is null
720 AND rc_rec.control_comp24 is null
721 AND rc_rec.control_comp25 is null
722 AND rc_rec.control_comp26 is null
723 AND rc_rec.control_comp27 is null
724 AND rc_rec.control_comp28 is null
725 AND rc_rec.control_comp29 is null
726 AND rc_rec.control_comp30 is null
727 AND rc_rec.control_obj1 is null
728 AND rc_rec.control_obj2 is null
729 AND rc_rec.control_obj3 is null
730 AND rc_rec.control_obj4 is null
731 AND rc_rec.control_obj5 is null
732 AND rc_rec.control_obj6 is null
733 AND rc_rec.control_obj7 is null
734 AND rc_rec.control_obj8 is null
735 AND rc_rec.control_obj9 is null
736 AND rc_rec.control_obj10 is null
737 AND rc_rec.control_obj11 is null
738 AND rc_rec.control_obj12 is null
739 AND rc_rec.control_obj13 is null
740 AND rc_rec.control_obj14 is null
741 AND rc_rec.control_obj15 is null
742 AND rc_rec.control_obj16 is null
743 AND rc_rec.control_obj17 is null
744 AND rc_rec.control_obj18 is null
745 AND rc_rec.control_obj19 is null
746 AND rc_rec.control_obj20 is null
747 AND rc_rec.control_obj21 is null
748 AND rc_rec.control_obj22 is null
749 AND rc_rec.control_obj23 is null
750 AND rc_rec.control_obj24 is null
751 AND rc_rec.control_obj25 is null
752 AND rc_rec.control_obj26 is null
753 AND rc_rec.control_obj27 is null
754 AND rc_rec.control_obj28 is null
755 AND rc_rec.control_obj29 is null
756 AND rc_rec.control_obj30 is null
757 AND rc_rec.control_assert1 is null
758 AND rc_rec.control_assert2 is null
759 AND rc_rec.control_assert3 is null
760 AND rc_rec.control_assert4 is null
761 AND rc_rec.control_assert5 is null
762 AND rc_rec.control_assert6 is null
763 AND rc_rec.control_assert7 is null
764 AND rc_rec.control_assert8 is null
765 AND rc_rec.control_assert9 is null
766 AND rc_rec.control_assert10 is null
767 AND rc_rec.control_assert11 is null
768 AND rc_rec.control_assert12 is null
769 AND rc_rec.control_assert13 is null
770 AND rc_rec.control_assert14 is null
771 AND rc_rec.control_assert15 is null
772 AND rc_rec.control_assert16 is null
773 AND rc_rec.control_assert17 is null
774 AND rc_rec.control_assert18 is null
775 AND rc_rec.control_assert19 is null
776 AND rc_rec.control_assert20 is null
777 AND rc_rec.control_assert21 is null
778 AND rc_rec.control_assert22 is null
779 AND rc_rec.control_assert23 is null
780 AND rc_rec.control_assert24 is null
781 AND rc_rec.control_assert25 is null
782 AND rc_rec.control_assert26 is null
783 AND rc_rec.control_assert27 is null
784 AND rc_rec.control_assert28 is null
785 AND rc_rec.control_assert29 is null
786 AND rc_rec.control_assert30 is null
787 AND rc_rec.control_approval_status_code is null
788 ---npanandi 12.10.2004: added foll. for Ctrl Classification
789 AND rc_rec.ctrl_classification is null
790 THEN
791 l_control_found := false;
792 END IF;
793
794 IF l_control_found THEN
795 --
796 -- process control
797 --
798 lx_control_id := null;
799 v_control_db_approval_status := null;
800 OPEN c_control_exists(rc_rec.control_name);
801 FETCH c_control_exists INTO lx_control_id, v_control_db_approval_status;
802 CLOSE c_control_exists;
803
804 ---03.01.2005 npanandi: added data security checks
805 l_has_ctrl_access := 'T'; ---setting this to 'T' for new Risks
806 l_new_control := true; ---setting this to TRUE to avoid conflict with previous loop value
807 if(lx_control_id is not null) then
808 ---Check for Update privilege here
809 l_new_control := false;
810 fnd_file.put_line (fnd_file.LOG, '************** Checking Update Privilege for rc_rec.control_name: '||rc_rec.control_name);
811 l_has_ctrl_access := check_function(
812 p_function => 'AMW_CTRL_UPDATE_PRVLG'
813 ,p_object_name => 'AMW_CONTROL'
814 ,p_instance_pk1_value => lx_control_id
815 ,p_user_id => fnd_global.user_id);
816 fnd_file.put_line (fnd_file.LOG, 'l_has_ctrl_access: '||l_has_ctrl_access);
817 fnd_file.put_line (fnd_file.LOG, '************** Checked Update Privilege for rc_rec.control_name: '||rc_rec.control_name);
818
819 IF l_has_ctrl_access <> 'T' then
820 v_err_msg := 'Cannot update this Ctrl';
821 update_interface_with_error (v_err_msg
822 ,'AMW_CONTROLS'
823 ,v_interface_id);
824 END IF;
825 end if;
826 ---03.01.2005 npanandi: added data security checks ends
827
828
829 IF Control_Can_Be_Processed AND
830 ---02.28.2005 npanandi: added check for lHasAccess to update this Ctrl
831 ---only if this user has Upd privilege
832 l_has_ctrl_access = 'T' and
833 (lx_control_id is null OR
834 l_revise_control_flag = 'Y') THEN
835
836 SELECT DECODE (rc_rec.control_name,NULL
837 ,l_amw_control_name_prefix||amw_controls_tl_s1.NEXTVAL
838 ,rc_rec.control_name)
839 INTO l_control_name
840 FROM dual;
841
842 l_control_rec.name := l_control_name;
843 l_control_rec.description := nvl(rc_rec.CONTROL_DESCRIPTION, l_control_name);
844 l_control_rec.approval_status := l_control_approval_status_code;
845 l_control_rec.control_type := rc_rec.CONTROL_TYPE_CODE;
846 l_control_rec.source := rc_rec.CONTROL_SOURCE;
847 l_control_rec.control_location := rc_rec.CONTROL_LOCATION_CODE;
848 l_control_rec.automation_type:= rc_rec.CONTROL_AUTOMATION_TYPE_CODE;
849 l_control_rec.job_id := rc_rec.CONTROL_JOB_ID;
850 l_control_rec.physical_evidence := rc_rec.PHYSICAL_EVIDENCE;
851 l_control_rec.requestor_id := l_requestor_id;
852 l_control_rec.application_id := rc_rec.control_application_id;
853
854 -- KOSRINIV Changes on 2 April 2004...
855 l_control_rec.preventive_control := rc_rec.preventive_control;
856 l_control_rec.detective_control := rc_rec.detective_control;
857 l_control_rec.disclosure_control := rc_rec.disclosure_control;
858 l_control_rec.key_mitigating := rc_rec.key_mitigating;
859 l_control_rec.verification_source := rc_rec.verification_source;
860 l_control_rec.verification_source_name := rc_rec.verification_source_name;
861 l_control_rec.verification_instruction := rc_rec.verification_instruction;
862
863 ---npanandi 12.10.2004: added foll. for Ctrl Classification
864 l_control_rec.classification := rc_rec.ctrl_classification;
865 l_control_rec.uom_code := rc_rec.uom_code;
866 AMW_CONTROL_PVT.Load_Control(
867 p_api_version_number => l_api_version_number,
868 p_init_msg_list => FND_API.G_TRUE,
869 p_commit => FND_API.G_FALSE,
870 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
871 p_control_rec => l_control_rec,
872 x_return_status => lx_return_status,
873 x_msg_count => lx_msg_count,
874 x_msg_data => lx_msg_data,
875 x_control_rev_id => vx_control_rev_id,
876 x_control_id => lx_control_id,
877 x_mode_affected => lx_mode_affected
878 );
879
880 ---03.01.2005 npanandi: if new Control, grant CtrlOwner prvlg
881 if(l_new_control) then
882 add_owner_privilege(
883 p_role_name => 'AMW_CTRL_OWNER_ROLE'
884 ,p_object_name => 'AMW_CONTROL'
885 ,p_grantee_type => 'P'
886 ,p_instance_pk1_value => lx_control_id
887 ,p_user_id => FND_GLOBAL.USER_ID);
888 end if;
889 ---03.01.2005 npanandi: if new Control, grant CtrlOwner prvlg
890
891 fnd_file.put_line (fnd_file.LOG,'lx_return_status: '||lx_return_status);
892 fnd_file.put_line (fnd_file.LOG,'lx_risk_rev_id: '||lx_risk_rev_id);
893 fnd_file.put_line (fnd_file.LOG,'vx_control_rev_id: '||vx_control_rev_id);
894
895 IF lx_return_status <> FND_API.G_RET_STS_SUCCESS then
896 v_err_msg := ' ';
897 FOR x IN 1..lx_msg_count LOOP
898 if(length(v_err_msg) < 1800) then
899 v_err_msg := v_err_msg||' '
900 ||substr(fnd_msg_pub.get(p_msg_index => x,
901 p_encoded => fnd_api.g_false), 1,100);
902 end if;
903 END LOOP;
904 update_interface_with_error (v_err_msg
905 ,'AMW_CONTROLS'
906 ,v_interface_id);
907 END IF;
908
909 IF NOT v_error_found THEN
910 --
911 -- process control components
912 --
913 control_components(rc_rec.control_comp1, '1');
914 control_components(rc_rec.control_comp2, '2');
915 control_components(rc_rec.control_comp3, '3');
916 control_components(rc_rec.control_comp4, '4');
917 control_components(rc_rec.control_comp5, '5');
918 control_components(rc_rec.control_comp6, '6');
919 control_components(rc_rec.control_comp7, '7');
920 control_components(rc_rec.control_comp8, '8');
921 control_components(rc_rec.control_comp9, '9');
922 control_components(rc_rec.control_comp10, '10');
923 control_components(rc_rec.control_comp11, '11');
924 control_components(rc_rec.control_comp12, '12');
925 control_components(rc_rec.control_comp13, '13');
926 control_components(rc_rec.control_comp14, '14');
927 control_components(rc_rec.control_comp15, '15');
928 control_components(rc_rec.control_comp16, '16');
929 control_components(rc_rec.control_comp17, '17');
930 control_components(rc_rec.control_comp18, '18');
931 control_components(rc_rec.control_comp19, '19');
932 control_components(rc_rec.control_comp20, '20');
933 control_components(rc_rec.control_comp21, '21');
934 control_components(rc_rec.control_comp22, '22');
935 control_components(rc_rec.control_comp23, '23');
936 control_components(rc_rec.control_comp24, '24');
937 control_components(rc_rec.control_comp25, '25');
938 control_components(rc_rec.control_comp26, '26');
939 control_components(rc_rec.control_comp27, '27');
940 control_components(rc_rec.control_comp28, '28');
941 control_components(rc_rec.control_comp29, '29');
942 control_components(rc_rec.control_comp30, '30');
943
944
945 --
946 -- process control objectives
947 --
948 control_objectives(rc_rec.control_obj1, '1');
949 control_objectives(rc_rec.control_obj2, '2');
950 control_objectives(rc_rec.control_obj3, '3');
951 control_objectives(rc_rec.control_obj4, '4');
952 control_objectives(rc_rec.control_obj5, '5');
953 control_objectives(rc_rec.control_obj6, '6');
954 control_objectives(rc_rec.control_obj7, '7');
955 control_objectives(rc_rec.control_obj8, '8');
956 control_objectives(rc_rec.control_obj9, '9');
957 control_objectives(rc_rec.control_obj10, '10');
958 control_objectives(rc_rec.control_obj11, '11');
959 control_objectives(rc_rec.control_obj12, '12');
960 control_objectives(rc_rec.control_obj13, '13');
961 control_objectives(rc_rec.control_obj14, '14');
962 control_objectives(rc_rec.control_obj15, '15');
963 control_objectives(rc_rec.control_obj16, '16');
964 control_objectives(rc_rec.control_obj17, '17');
965 control_objectives(rc_rec.control_obj18, '18');
966 control_objectives(rc_rec.control_obj19, '19');
967 control_objectives(rc_rec.control_obj20, '20');
968 control_objectives(rc_rec.control_obj21, '21');
969 control_objectives(rc_rec.control_obj22, '22');
970 control_objectives(rc_rec.control_obj23, '23');
971 control_objectives(rc_rec.control_obj24, '24');
972 control_objectives(rc_rec.control_obj25, '25');
973 control_objectives(rc_rec.control_obj26, '26');
974 control_objectives(rc_rec.control_obj27, '27');
975 control_objectives(rc_rec.control_obj28, '28');
976 control_objectives(rc_rec.control_obj29, '29');
977 control_objectives(rc_rec.control_obj30, '30');
978 --
979 -- process control assertions
980 --
981 control_assertions(rc_rec.control_assert1, '1');
982 control_assertions(rc_rec.control_assert2, '2');
983 control_assertions(rc_rec.control_assert3, '3');
984 control_assertions(rc_rec.control_assert4, '4');
985 control_assertions(rc_rec.control_assert5, '5');
986 control_assertions(rc_rec.control_assert6, '6');
987 control_assertions(rc_rec.control_assert7, '7');
988 control_assertions(rc_rec.control_assert8, '8');
989 control_assertions(rc_rec.control_assert9, '9');
990 control_assertions(rc_rec.control_assert10, '10');
991 control_assertions(rc_rec.control_assert11, '11');
992 control_assertions(rc_rec.control_assert12, '12');
993 control_assertions(rc_rec.control_assert13, '13');
994 control_assertions(rc_rec.control_assert14, '14');
995 control_assertions(rc_rec.control_assert15, '15');
996 control_assertions(rc_rec.control_assert16, '16');
997 control_assertions(rc_rec.control_assert17, '17');
998 control_assertions(rc_rec.control_assert18, '18');
999 control_assertions(rc_rec.control_assert19, '19');
1000 control_assertions(rc_rec.control_assert20, '20');
1001 control_assertions(rc_rec.control_assert21, '21');
1002 control_assertions(rc_rec.control_assert22, '22');
1003 control_assertions(rc_rec.control_assert23, '23');
1004 control_assertions(rc_rec.control_assert24, '24');
1005 control_assertions(rc_rec.control_assert25, '25');
1006 control_assertions(rc_rec.control_assert26, '26');
1007 control_assertions(rc_rec.control_assert27, '27');
1008 control_assertions(rc_rec.control_assert28, '28');
1009 control_assertions(rc_rec.control_assert29, '29');
1010 control_assertions(rc_rec.control_assert30, '30');
1011 END IF; --- end of if for v_error_found ....
1012 END IF; -- end of IF lx_control_id is null OR...
1013
1014 --
1015 -- create the control associations
1016 --
1017
1018 IF (NOT v_error_found AND L_PROCESS_ID IS NOT NULL AND L_PROCESS_ID <> -1) THEN
1019 --SET THESE 2 VARIABLES TO NULL FOR THIS LOOP
1020 L_CONTROL_ASSOCIATION_ID := NULL;
1021 L_APPROVAL_DATE := NULL;
1022 --CHECK FOR EXISTING LATEST_REVISION OF THIS ASSOCIATION, IF ANY
1023 fnd_file.PUT_LINE(fnd_file.log, 'L_PROCESS_ID: '||L_PROCESS_ID||', LX_RISK_ID: '||LX_RISK_ID);
1024 BEGIN
1025 SELECT CONTROL_ASSOCIATION_ID,APPROVAL_DATE
1026 INTO L_CONTROL_ASSOCIATION_ID,L_APPROVAL_DATE
1027 FROM AMW_CONTROL_ASSOCIATIONS
1028 WHERE CONTROL_ID=LX_CONTROL_ID
1029 AND PK1=L_PROCESS_ID
1030 AND PK2=LX_RISK_ID
1031 AND OBJECT_TYPE='RISK'
1032 AND DELETION_DATE IS NULL;
1033 EXCEPTION
1034 WHEN NO_DATA_FOUND THEN
1035 NULL;
1036 END;
1037
1038 ---03.02.2005 npanandi: check here to see if this Process has
1039 ---AMW_UPD_RL_PROC_RISK_CTRL_ASSN privilege to associate this Risk to Ctrl
1040 l_has_risk_ctrl_assn_access := 'T'; --setting this to 'T' to avoid conflict
1041 --with value from previous loop
1042 l_has_risk_ctrl_assn_access := check_function(
1043 p_function => 'AMW_UPD_RL_PROC_RISK_CTRL_ASSN'
1044 ,p_object_name => 'AMW_PROCESS_APPR_ETTY'
1045 ,p_instance_pk1_value => l_PROCESS_ID
1046 ,p_user_id => fnd_global.user_id);
1047 fnd_file.put_line(fnd_file.log,'%%%%%%%%%%%%%%%%%% l_has_risk_ctrl_assn_access: '||l_has_risk_ctrl_assn_access||' %%%%%%%%%%%%%%%%%%');
1048
1049 IF(L_CONTROL_ASSOCIATION_ID IS NULL and l_has_risk_ctrl_assn_access = 'T') THEN
1050 --NO ROW RETRIEVED, SO ASSOCIATION DOESN'T EXIST YET
1051 --CREATE AN ASSOCIATION
1052 CREATE_AMW_CTRL_ASSOC(
1053 P_CONTROL_ID => lx_control_id
1054 ,P_PROCESS_ID => L_PROCESS_ID
1055 ,P_RISK_ID => lx_risk_id
1056 );
1057 ELSE
1058 --THIS MEANS THAT ASSOCIATION EXISTS, SO CHECK APPROVAL_DATE
1059
1060 --IF APPROVAL_DATE IS NULL FOR CONTROL_ASSOCIATIONS,
1061 --THIS MEANS THAT THIS ASSOCIATIONS
1062 --IS LATEST_REVISION, SO SIMPLY UPDATE ASSOC ATTRIBUTES
1063 --but of course, for Ctrl - Risk Associations, there ARE NO
1064 --association attributes ....
1065 IF(L_APPROVAL_DATE IS NOT NULL and l_has_risk_ctrl_assn_access = 'T') THEN
1066 --THIS MEANS THAT THIS IS LATEST REVISION FOR THIS ASSOCIATION
1067 --AND IS APPROVED, SO
1068 BEGIN
1069 UPDATE AMW_CONTROL_ASSOCIATIONS
1070 SET DELETION_DATE=SYSDATE
1071 ,OBJECT_VERSION_NUMBER=OBJECT_VERSION_NUMBER+1
1072 ,LAST_UPDATE_DATE=SYSDATE
1073 ,LAST_UPDATED_BY=G_USER_ID
1074 ,LAST_UPDATE_LOGIN=G_LOGIN_ID
1075 WHERE CONTROL_ASSOCIATION_ID=L_CONTROL_ASSOCIATION_ID;
1076
1077 -- ...AND CREATE A NEW ROW FOR THE LATEST ASSOCIATION
1078 CREATE_AMW_CTRL_ASSOC(
1079 P_CONTROL_ID => lx_control_id
1080 ,P_PROCESS_ID => L_PROCESS_ID
1081 ,P_RISK_ID => lx_risk_id
1082 );
1083 EXCEPTION
1084 WHEN OTHERS THEN
1085 V_ERR_MSG := 'INTERFACE_ID := '||v_interface_id||' '||SUBSTR (SQLERRM, 1, 200);
1086 UPDATE_INTERFACE_WITH_ERROR(V_ERR_MSG,V_TABLE_NAME,v_interface_id);
1087 END;
1088 END IF; --END OF IF FOR APPROVAL DATE NULL CHECK
1089 --END IF; -- end of complex if condition
1090 END IF; -- end of L_CONTROL_ASSOCIATION_ID NULLITY CHK
1091 END IF; -- end of if not v_error_found
1092
1093 ---
1094 ---Create the Audit_Procedure_Association
1095 ---
1096 IF ((NOT v_error_found) AND (rc_rec.ap_name IS NOT NULL)) THEN
1097 if(rc_rec.design_effectiveness = 'N' and rc_rec.op_effectiveness = 'N') then
1098 ---fnd_message.set_name('AMW','AMW_ASSOC_AP_EFF_WEBADI_MSG');
1099 v_err_msg := fnd_message.GET_STRING('AMW','AMW_ASSOC_AP_EFF_WEBADI_MSG');
1100 ---v_err_msg := fnd_message.get;
1101 update_interface_with_error (v_err_msg
1102 ,v_table_name
1103 ,v_interface_id);
1104 else
1105 fnd_file.put_line (fnd_file.LOG,'%%%%%% Processing AP_Association data');
1106 fnd_file.PUT_LINE(fnd_file.log, 'CREATING AP_ASSOCIATIONS HERE');
1107 --SET THESE 2 VARS TO NULL FOR PROCESSING THIS LOOP
1108 L_AP_ASSOCIATION_ID := NULL;
1109 L_AP_APPROVAL_DATE := NULL;
1110 --NPANANDI 10/26/2004: CHANGED THE ABOVE SELECT CHECK
1111 --FOR EXISTING AP 2 CTRL ASSOCIATION
1112 BEGIN
1113 SELECT AP_ASSOCIATION_ID,APPROVAL_DATE
1114 INTO L_AP_ASSOCIATION_ID,L_AP_APPROVAL_DATE
1115 FROM AMW_AP_ASSOCIATIONS
1116 WHERE AUDIT_PROCEDURE_ID=RC_REC.AP_NAME
1117 AND PK1=LX_CONTROL_ID
1118 AND OBJECT_TYPE='CTRL'
1119 AND DELETION_DATE IS NULL;
1120 EXCEPTION
1121 WHEN OTHERS THEN
1122 NULL; --DO NOTHING
1123 END;
1124
1125 fnd_file.PUT_LINE(fnd_file.log, 'L_AP_ASSOCIATION_ID: '||L_AP_ASSOCIATION_ID||', L_AP_APPROVAL_DATE: '||L_AP_APPROVAL_DATE);
1126 L_ASSOC_AP_TO_CTRL := FALSE;
1127 IF((((l_revise_control_flag ='Y' OR v_control_db_approval_status is null)
1128 AND l_control_approval_status_code = 'A')
1129 OR (l_revise_control_flag='N' and v_control_db_approval_status='A'))) THEN
1130 L_ASSOC_AP_TO_CTRL := TRUE;
1131 END IF;
1132
1133 ---03.01.2005 npanandi: check here to see if this AP has AMW_UPDATE_AP_DETAILS
1134 ---privilege to associate this Control to AP
1135 l_has_ap_access := check_function(
1136 p_function => 'AMW_UPDATE_AP_DETAILS'
1137 ,p_object_name => 'AMW_AUDIT_PROCEDURE'
1138 ,p_instance_pk1_value => RC_REC.AP_NAME
1139 ,p_user_id => fnd_global.user_id);
1140 fnd_file.put_line(fnd_file.log,'%%%%%%%%%%%%%%%%%% l_has_ap_access: '||l_has_ap_access||' %%%%%%%%%%%%%%%%%%');
1141
1142 IF(L_ASSOC_AP_TO_CTRL and l_has_ap_access = 'T') THEN --putting this check, because Ctrl
1143 --to AP association is only for Approved Ctrls
1144
1145 fnd_file.put_line (fnd_file.LOG,'l_object_type_count --> '||l_object_type_count);
1146 ---IF l_object_type_count = 0 THEN
1147 IF( L_AP_ASSOCIATION_ID IS NULL) THEN
1148 --for associating between Controls , take due cognizance of the
1149 --revise flags the following gory logic!
1150 fnd_file.PUT_LINE(fnd_file.log, 'L_ASSOC_AP_TO_CTRL: MUST BE TRUE, HENCE I AM HERE');
1151 fnd_file.put_line (fnd_file.LOG,'~~~~~~~~~~~~~~~~~~~AP_Association processable, values to be entered: ');
1152 fnd_file.put_line (fnd_file.LOG,'pk1: '||lx_control_id);
1153 fnd_file.put_line (fnd_file.LOG,'audit_procedure_id: '||rc_rec.ap_name);
1154
1155 --NPANANDI 12/09/2004:
1156 --WHEN CTRL TO AP ASSOCIATION IS BEING DONE, POST AMW.D
1157 --AP NEEDS TO BE REVISED
1158 --THIS IS HANDLED IN CREATE_AMW_AP_ASSOC
1159 fnd_file.PUT_LINE(fnd_file.log, 'CREATE_AMW_AP_ASSOC START');
1160 LX_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
1161 LX_MSG_COUNT := 0;
1162 LX_MSG_DATA := NULL;
1163 CREATE_AMW_AP_ASSOC(
1164 P_CONTROL_ID => lx_control_id
1165 ,P_AUDIT_PROCEDURE_ID => rc_rec.ap_name
1166 ,P_DESIGN_EFFECTIVENESS => rc_rec.design_effectiveness
1167 ,P_OP_EFFECTIVENESS => rc_rec.op_effectiveness
1168 ,X_RETURN_STATUS => LX_RETURN_STATUS
1169 ,X_MSG_COUNT => LX_MSG_COUNT
1170 ,X_MSG_DATA => LX_MSG_DATA
1171 );
1172 IF lx_return_status <> FND_API.G_RET_STS_SUCCESS then
1173 v_err_msg := ' ';
1174 FOR x IN 1..lx_msg_count LOOP
1175 if(length(v_err_msg) < 1800) then
1176 v_err_msg := v_err_msg||' '||substr(
1177 fnd_msg_pub.get(p_msg_index => x,
1178 p_encoded => fnd_api.g_false), 1,100);
1179 end if;
1180 END LOOP;
1181 update_interface_with_error (v_err_msg
1182 ,'AMW_AP_ASSOCIATIONS'
1183 ,v_interface_id);
1184 END IF;
1185 fnd_file.PUT_LINE(fnd_file.log, 'CREATE_AMW_AP_ASSOC END');
1186 ELSE
1187 --THIS MEANS THAT ASSOCIATION EXISTS, SO CHECK APPROVAL_DATE
1188
1189 --IF APPROVAL_DATE IS NULL FOR CONTROL_ASSOCIATIONS,
1190 --THIS MEANS THAT THIS ASSOCIATIONS
1191 --IS LATEST_REVISION, SO SIMPLY UPDATE ASSOC ATTRIBUTES
1192 IF(L_AP_APPROVAL_DATE IS NOT NULL) THEN
1193 --THIS MEANS THAT THIS IS LATEST REVISION FOR THIS ASSOCIATION
1194 --AND IS APPROVED, SO
1195 fnd_file.PUT_LINE(fnd_file.log, 'UPSERTING AP 2 CTRL ASSOCIATION');
1196 BEGIN
1197 UPDATE AMW_AP_ASSOCIATIONS
1198 SET DELETION_DATE=SYSDATE
1199 ,OBJECT_VERSION_NUMBER=OBJECT_VERSION_NUMBER+1
1200 ,LAST_UPDATE_DATE=SYSDATE
1201 ,LAST_UPDATED_BY=G_USER_ID
1202 ,LAST_UPDATE_LOGIN=G_LOGIN_ID
1203 WHERE AP_ASSOCIATION_ID=L_AP_ASSOCIATION_ID;
1204
1205 -- ...AND CREATE A NEW ROW FOR THE LATEST ASSOCIATION
1206 CREATE_AMW_AP_ASSOC(
1207 P_CONTROL_ID => lx_control_id
1208 ,P_AUDIT_PROCEDURE_ID => rc_rec.ap_name
1209 ,P_DESIGN_EFFECTIVENESS => rc_rec.design_effectiveness
1210 ,P_OP_EFFECTIVENESS => rc_rec.op_effectiveness
1211 ,X_RETURN_STATUS => LX_RETURN_STATUS
1212 ,X_MSG_COUNT => LX_MSG_COUNT
1213 ,X_MSG_DATA => LX_MSG_DATA
1214 );
1215 IF lx_return_status <> FND_API.G_RET_STS_SUCCESS then
1216 v_err_msg := ' ';
1217 FOR x IN 1..lx_msg_count LOOP
1218 if(length(v_err_msg) < 1800) then
1219 v_err_msg := v_err_msg||' '||substr(
1220 fnd_msg_pub.get(p_msg_index => x,
1221 p_encoded => fnd_api.g_false), 1,100);
1222 end if;
1223 END LOOP;
1224 update_interface_with_error (v_err_msg
1225 ,'AMW_AP_ASSOCIATIONS'
1226 ,v_interface_id);
1227 END IF;
1228 EXCEPTION
1229 WHEN OTHERS THEN
1230 V_ERR_MSG := 'INTERFACE_ID := '||v_interface_id||' '||SUBSTR (SQLERRM, 1, 200);
1231 UPDATE_INTERFACE_WITH_ERROR(V_ERR_MSG,V_TABLE_NAME,v_interface_id);
1232 END;
1233 ELSE
1234 --THIS MEANS THAT AP 2 CTRL ASSOCIATION IS LATEST REVISION
1235 -- YET APPROVED, SO
1236 --SIMPLY UPDATE THE ASSOCIATION ATTRIBUTES HERE
1237 fnd_file.PUT_LINE(fnd_file.log, 'UPDATING AP 2 CTRL ASSOCIATION --> L_AP_ASSOCIATION_ID: '||L_AP_ASSOCIATION_ID);
1238 --IF(L_ASSOC_AP_TO_CTRL) THEN
1239 BEGIN
1240 UPDATE AMW_AP_ASSOCIATIONS
1241 SET DESIGN_EFFECTIVENESS=rc_rec.design_effectiveness
1242 ,OP_EFFECTIVENESS=rc_rec.op_effectiveness
1243 ,OBJECT_VERSION_NUMBER=OBJECT_VERSION_NUMBER+1
1244 ,LAST_UPDATE_DATE=SYSDATE
1245 ,LAST_UPDATED_BY=G_USER_ID
1246 ,LAST_UPDATE_LOGIN=G_LOGIN_ID
1247 WHERE AP_ASSOCIATION_ID=L_AP_ASSOCIATION_ID;
1248 EXCEPTION
1249 WHEN OTHERS THEN
1250 V_ERR_MSG := 'INTERFACE_ID := '||v_interface_id||' '||SUBSTR (SQLERRM, 1, 200);
1251 UPDATE_INTERFACE_WITH_ERROR(V_ERR_MSG,V_TABLE_NAME,v_interface_id);
1252 END;
1253 END IF; --END OF L_APPROVAL_DATE IS NOT NULL CHECK
1254 END IF; -- end of L_AP_ASSOCIATION_ID IS NULL if block
1255 END IF; --END OF CTRL APPR STATUS CHECK FOR AP - CTRL ASSOCIATION
1256 END IF; -- end of if Design_Eff and Op_Eff not null
1257 END IF; -- end of if not v_error_found
1258 END IF; -- end of IF l_control_found THEN
1259
1260 --
1261 --create the Process_Objective
1262 --
1263 lx_return_status := FND_API.G_RET_STS_SUCCESS;
1264 vx_process_objective_id := null; ---Process_Objective_Id being reset here
1265 ---because for this loop, it is different
1266 l_process_obj_column := TRUE;
1267 if(rc_rec.process_objective_name is null or (trim(rc_rec.process_objective_name) = ''))then
1268 l_process_obj_column := FALSE;
1269 end if;
1270
1271 ---if(not ((rc_rec.process_objective_name is null) or (trim(rc_rec.process_objective_name) = ' ')))then
1272 ---if(not ((rc_rec.process_objective_name is null) or (trim(rc_rec.process_objective_name) = '')))then
1273 if(l_process_obj_column = true)then
1274 fnd_file.put_line(fnd_file.LOG,'########## Before Going to Create_Process_Objectives');
1275 fnd_file.put_line(fnd_file.LOG,'vx_process_objective_id: '||vx_process_objective_id);
1276 create_process_objectives(p_process_objective_name => rc_rec.process_objective_name,
1277 p_process_obj_description => rc_rec.process_obj_description,
1278 p_requestor_id => l_requestor_id,
1279 x_return_status => lx_return_status);
1280
1281 fnd_file.put_line(fnd_file.LOG,'########## After Going to Create_Process_Objectives');
1282 fnd_file.put_line(fnd_file.LOG,'vx_process_objective_id: '||vx_process_objective_id);
1283
1284 IF lx_return_status <> FND_API.G_RET_STS_SUCCESS then
1285 v_err_msg := ' ';
1286 FOR x IN 1..lx_msg_count LOOP
1287 if(length(v_err_msg) < 1800) then
1288 v_err_msg := v_err_msg||' '
1289 ||substr(fnd_msg_pub.get(p_msg_index => x,
1290 p_encoded => fnd_api.g_false), 1,100);
1291 end if;
1292 END LOOP;
1293 update_interface_with_error (v_err_msg
1294 ,'AMW_PROCESS_OBJECTIVES'
1295 ,v_interface_id);
1296 END IF;
1297 end if; --end of if for process obj name nulkl
1298 --
1299 -- create the process objective associations
1300 --
1301 l_process_objective_id := vx_process_objective_id;
1302
1303 ---fnd_file.put_line (fnd_file.LOG, 'v_error_found: '||v_error_found);
1304 fnd_file.put_line (fnd_file.LOG, 'rc_rec.process_objective_name: '||rc_rec.process_objective_name);
1305 fnd_file.put_line (fnd_file.LOG, 'l_process_id: '||l_process_id);
1306 fnd_file.put_line (fnd_file.LOG, 'l_process_objective_id: '||l_process_objective_id);
1307
1308 IF((not v_error_found) AND
1309 (l_process_obj_column = TRUE) AND
1310 (l_process_id is not null) AND
1311 (l_process_id <> -1) AND
1312 (l_process_objective_id is not null)) then
1313 --SET THESE 2 VARS TO NULL FOR THIS LOOP
1314 L_PROC_OBJ_ASSOCIATION_ID := NULL;
1315 L_PROC_OBJ_APPROVAL_DATE := NULL;
1316 --npanandi 10/26/2004: changed the way insert logic is handled
1317 --post AMW.D+
1318 BEGIN
1319 SELECT OBJECTIVE_ASSOCIATION_ID,APPROVAL_DATE
1320 INTO L_PROC_OBJ_ASSOCIATION_ID,L_PROC_OBJ_APPROVAL_DATE
1321 FROM AMW_OBJECTIVE_ASSOCIATIONS
1322 WHERE PROCESS_OBJECTIVE_ID=l_process_objective_id
1323 AND PK1=l_process_id
1324 AND OBJECT_TYPE='PROCESS'
1325 AND DELETION_DATE IS NULL;
1326 EXCEPTION
1327 WHEN NO_DATA_FOUND THEN
1328 NULL; ---DO NOTHING ....
1329 END;
1330
1331 ---03.02.2005 npanandi: check here to see if this Process has AMW_UPD_RL_PROC_OBJ_ASSOC
1332 ---privilege to associate this Objective to Process
1333
1334 ---IMPORTANTE --> check this privilege only when associating this Objective
1335 ---to a Process AND NOT to a Risk!!
1336 l_has_proc_obj_assoc_access := 'T'; ---set to T to avoid conflict with previous loop value
1337 l_has_proc_obj_assoc_access := check_function(
1338 p_function => 'AMW_UPD_RL_PROC_OBJ_ASSOC'
1339 ,p_object_name => 'AMW_PROCESS_APPR_ETTY'
1340 ,p_instance_pk1_value => l_process_id
1341 ,p_user_id => fnd_global.user_id);
1342 fnd_file.put_line(fnd_file.log,'%%%%%%%%%%%%%%%%%%% l_has_proc_obj_assoc_access: '||l_has_proc_obj_assoc_access||' %%%%%%%%%%%%%%%%%%%');
1343
1344 fnd_file.put_line (fnd_file.LOG, 'inside if for process: l_process_objective_id '||l_process_objective_id);
1345 fnd_file.put_line (fnd_file.LOG, 'inside if for risk: l_process_objectives_count '||l_process_objectives_count);
1346 IF (L_PROC_OBJ_ASSOCIATION_ID IS NULL and l_has_proc_obj_assoc_access = 'T') THEN
1347 --NO ROW RETRIEVED, SO ASSOCIATION DOESN'T EXIST YET
1348 --CREATE AN ASSOCIATION, SET ASSOCIATION_CREATION_DATE=SYSDATE
1349 FND_FILE.PUT_LINE(FND_FILE.LOG,'Need to change the way data is inserted in amw_risk_associations');
1350 CREATE_AMW_OBJ_ASSOC(
1351 P_PROCESS_OBJECTIVE_ID => l_process_objective_id
1352 ,P_PK1 => l_process_id
1353 ,P_OBJECT_TYPE => 'PROCESS'
1354 );
1355 ELSE
1356 --THIS MEANS THAT ASSOCIATION EXISTS, SO CHECK APPROVAL_DATE
1357
1358 --IF APPROVAL_DATE IS NULL FOR OBJECTIVE_ASSOCIATIONS,
1359 --THIS MEANS THAT THIS ASSOCIATION
1360 --IS LATEST_REVISION, SO SIMPLY UPDATE ASSOC ATTRIBUTES
1361 IF(L_PROC_OBJ_APPROVAL_DATE IS NOT NULL and l_has_proc_obj_assoc_access = 'T') THEN
1362 --THIS MEANS THAT THIS IS LATEST REVISION FOR THIS ASSOCIATION
1363 --AND IS APPROVED, SO
1364 FND_FILE.PUT_LINE(FND_FILE.LOG,'UPDATE THIS ASSOCIATION, THEN INSERT');
1365 BEGIN
1366 UPDATE AMW_OBJECTIVE_ASSOCIATIONS
1367 SET DELETION_DATE=SYSDATE
1368 ,OBJECT_VERSION_NUMBER=OBJECT_VERSION_NUMBER+1
1369 ,LAST_UPDATE_DATE=SYSDATE
1370 ,LAST_UPDATED_BY=G_USER_ID
1371 ,LAST_UPDATE_LOGIN=G_LOGIN_ID
1372 WHERE OBJECTIVE_ASSOCIATION_ID=L_PROC_OBJ_ASSOCIATION_ID;
1373
1374 -- ...AND CREATE A NEW ROW FOR THE LATEST ASSOCIATION
1375 CREATE_AMW_OBJ_ASSOC(
1376 P_PROCESS_OBJECTIVE_ID => l_process_objective_id
1377 ,P_PK1 => l_process_id
1378 ,P_OBJECT_TYPE => 'PROCESS'
1379 );
1380 EXCEPTION
1381 WHEN OTHERS THEN
1382 V_ERR_MSG := 'INTERFACE_ID := '||v_interface_id||' '||SUBSTR (SQLERRM, 1, 200);
1383 UPDATE_INTERFACE_WITH_ERROR(V_ERR_MSG,V_TABLE_NAME,v_interface_id);
1384 END;
1385 END IF;
1386 end if; --end of if for process insertions
1387 end if; ---end of if no error found for process insertions
1388
1389 --
1390 -- create the risk objective associations
1391 --
1392 fnd_file.put_line (fnd_file.LOG, 'rc_rec.process_objective_name: '||rc_rec.process_objective_name);
1393 fnd_file.put_line (fnd_file.LOG, 'l_process_objective_id: '||l_process_objective_id);
1394
1395 IF((not v_error_found) AND
1396 (l_process_obj_column = TRUE) AND
1397 (l_process_objective_id is not null)) then
1398 --SET THESE 2 VARS TO NULL FOR THIS LOOP PROCESSING
1399 L_RISK_OBJ_ASSOCIATION_ID := NULL;
1400 L_RISK_OBJ_APPROVAL_DATE := NULL;
1401 --npanandi 10/26/2004: changed the way check is done post AMW.D
1402 BEGIN
1403 SELECT OBJECTIVE_ASSOCIATION_ID,APPROVAL_DATE
1404 INTO L_RISK_OBJ_ASSOCIATION_ID,L_RISK_OBJ_APPROVAL_DATE
1405 FROM AMW_OBJECTIVE_ASSOCIATIONS
1406 WHERE PROCESS_OBJECTIVE_ID=l_process_objective_id
1407 AND PK1=lx_risk_id
1408 AND OBJECT_TYPE='RISK'
1409 AND DELETION_DATE IS NULL;
1410 EXCEPTION
1411 WHEN NO_DATA_FOUND THEN
1412 NULL; --DO NOTHING ...
1413 END;
1414
1415 ---IF l_risk_objectives_count = 0 THEN
1416 IF(L_RISK_OBJ_ASSOCIATION_ID IS NULL) THEN
1417 --NO ROW RETRIEVED, SO ASSOCIATION DOESN'T EXIST YET
1418 --CREATE AN ASSOCIATION, SET ASSOCIATION_CREATION_DATE=SYSDATE
1419 FND_FILE.PUT_LINE(FND_FILE.LOG,'Need to change the way data is inserted in amw_risk_associations');
1420 CREATE_AMW_OBJ_ASSOC(
1421 P_PROCESS_OBJECTIVE_ID => l_process_objective_id
1422 ,P_PK1 => lx_risk_id
1423 ,P_OBJECT_TYPE => 'RISK'
1424 );
1425 fnd_file.put_line (fnd_file.LOG, 'inserting lx_risk_id: '||lx_risk_id||' l_process_objective_id '||l_process_objective_id);
1426 ELSE
1427 --THIS MEANS THAT ASSOCIATION EXISTS, SO CHECK APPROVAL_DATE
1428
1429 --IF APPROVAL_DATE IS NULL FOR OBJECTIVE_ASSOCIATIONS,
1430 --THIS MEANS THAT THIS ASSOCIATION
1431 --IS LATEST_REVISION, SO SIMPLY UPDATE ASSOC ATTRIBUTES
1432 IF(L_RISK_OBJ_APPROVAL_DATE IS NOT NULL) THEN
1433 --THIS MEANS THAT THIS IS LATEST REVISION FOR THIS ASSOCIATION
1434 --AND IS APPROVED, SO
1435 FND_FILE.PUT_LINE(FND_FILE.LOG,'UPDATE THIS ASSOCIATION, THEN INSERT');
1436 BEGIN
1437 UPDATE AMW_OBJECTIVE_ASSOCIATIONS
1438 SET DELETION_DATE=SYSDATE
1439 ,OBJECT_VERSION_NUMBER=OBJECT_VERSION_NUMBER+1
1440 ,LAST_UPDATE_DATE=SYSDATE
1441 ,LAST_UPDATED_BY=G_USER_ID
1442 ,LAST_UPDATE_LOGIN=G_LOGIN_ID
1443 WHERE OBJECTIVE_ASSOCIATION_ID=L_RISK_OBJ_ASSOCIATION_ID;
1444
1445 -- ...AND CREATE A NEW ROW FOR THE LATEST ASSOCIATION
1446 CREATE_AMW_OBJ_ASSOC(
1447 P_PROCESS_OBJECTIVE_ID => l_process_objective_id
1448 ,P_PK1 => lx_risk_id
1449 ,P_OBJECT_TYPE => 'RISK'
1450 );
1451 EXCEPTION
1452 WHEN OTHERS THEN
1453 V_ERR_MSG := 'INTERFACE_ID := '||v_interface_id||' '||SUBSTR (SQLERRM, 1, 200);
1454 UPDATE_INTERFACE_WITH_ERROR(V_ERR_MSG,V_TABLE_NAME,v_interface_id);
1455 END;
1456 END IF; --END OF IF FOR L_RISK_OBJ_APPR_DATE CHK
1457 end if; --end of if L_RISK_OBJ_ASSOCIATION_ID NULLITY CHK
1458 end if; --- end of if no error found for risk insertions
1459
1460 --
1461 --
1462 -- 01.13.2005 npanandi: create the Control - objective associations
1463 -- per the changed datamodel for Process/Control Objective associations
1464 --
1465 --
1466 IF((not v_error_found) AND
1467 (l_process_obj_column = TRUE) AND
1468 (l_process_objective_id is not null) AND
1469 ---need processId for pk1 column
1470 (l_process_id is not null) AND
1471 (l_process_id <> -1) AND
1472 ---need riskId for pk2 column
1473 lx_risk_id is not null AND
1474 ---need controlId for pk3 column
1475 lx_control_id is not null) then
1476 ---this means that processId, riskId, controlId and processObjectiveId
1477 ---are all valid ---> hence create the Ctrl - ProcObj association
1478 ---as per the new datamodel
1479
1480 --SET THESE 2 VARS TO NULL FOR THIS LOOP PROCESSING
1481 L_CTRL_OBJ_ASSOCIATION_ID := NULL;
1482 L_CTRL_OBJ_APPROVAL_DATE := NULL;
1483 BEGIN
1484 SELECT OBJECTIVE_ASSOCIATION_ID,APPROVAL_DATE,process_objective_id
1485 INTO L_CTRL_OBJ_ASSOCIATION_ID,L_CTRL_OBJ_APPROVAL_DATE,l_ctrl_objective_id
1486 FROM AMW_OBJECTIVE_ASSOCIATIONS
1487 WHERE
1488 -- PROCESS_OBJECTIVE_ID=l_process_objective_id AND
1489 PK1=l_process_id
1490 AND PK2=LX_RISK_ID
1491 AND PK3=LX_CONTROL_ID
1492 AND OBJECT_TYPE='CONTROL'
1493 AND DELETION_DATE IS NULL;
1494 EXCEPTION
1495 WHEN NO_DATA_FOUND THEN
1496 NULL; --DO NOTHING ...
1497 END;
1498
1499 IF(L_CTRL_OBJ_ASSOCIATION_ID IS NULL) THEN
1500 CREATE_AMW_OBJ_ASSOC(
1501 P_PROCESS_OBJECTIVE_ID => l_process_objective_id
1502 ,P_PK1 => l_process_id
1503 --01.13.2005 npanandi: added pk2,pk3 for Ctrl to Obj association
1504 ,P_PK2 => LX_RISK_ID
1505 ,P_PK3 => LX_CONTROL_ID
1506 ,P_OBJECT_TYPE => 'CONTROL'
1507 );
1508 fnd_file.put_line (fnd_file.LOG, 'inserting lx_CONTROL_id: '||lx_CONTROL_id||' l_process_objective_id '||l_process_objective_id);
1509 ELSE
1510 --THIS MEANS THAT ASSOCIATION EXISTS, SO CHECK APPROVAL_DATE
1511
1512 --IF APPROVAL_DATE IS NULL FOR OBJECTIVE_ASSOCIATIONS,
1513 --THIS MEANS THAT THIS ASSOCIATION
1514 --IS LATEST_REVISION, SO SIMPLY UPDATE ASSOC ATTRIBUTES
1515 IF(L_CTRL_OBJ_APPROVAL_DATE IS NOT NULL) THEN
1516 --THIS MEANS THAT THIS IS LATEST REVISION FOR THIS ASSOCIATION
1517 --AND IS APPROVED, SO
1518 BEGIN
1519 if ( l_ctrl_objective_id <> l_process_objective_id) then
1520 UPDATE AMW_OBJECTIVE_ASSOCIATIONS
1521 SET DELETION_DATE=SYSDATE
1522 ,OBJECT_VERSION_NUMBER=OBJECT_VERSION_NUMBER+1
1523 ,LAST_UPDATE_DATE=SYSDATE
1524 ,LAST_UPDATED_BY=G_USER_ID
1525 ,LAST_UPDATE_LOGIN=G_LOGIN_ID
1526 WHERE OBJECTIVE_ASSOCIATION_ID=L_CTRL_OBJ_ASSOCIATION_ID;
1527
1528 -- ...AND CREATE A NEW ROW FOR THE LATEST ASSOCIATION
1529 CREATE_AMW_OBJ_ASSOC(
1530 P_PROCESS_OBJECTIVE_ID => l_process_objective_id
1531 ,P_PK1 => l_process_id
1532 --01.13.2005 npanandi: added pk2,pk3 for Ctrl to Obj association
1533 ,P_PK2 => LX_RISK_ID
1534 ,P_PK3 => LX_CONTROL_ID
1535 ,P_OBJECT_TYPE => 'CONTROL'
1536 );
1537 end if;
1538 EXCEPTION
1539 WHEN OTHERS THEN
1540 V_ERR_MSG := 'INTERFACE_ID := '||v_interface_id||' '||SUBSTR (SQLERRM, 1, 200);
1541 UPDATE_INTERFACE_WITH_ERROR(V_ERR_MSG,V_TABLE_NAME,v_interface_id);
1542 END;
1543 else
1544 if ( l_ctrl_objective_id <> l_process_objective_id) then
1545 begin
1546 delete from AMW_OBJECTIVE_ASSOCIATIONS
1547 WHERE OBJECTIVE_ASSOCIATION_ID=L_CTRL_OBJ_ASSOCIATION_ID;
1548
1549 -- ...AND CREATE A NEW ROW FOR THE LATEST ASSOCIATION
1550 CREATE_AMW_OBJ_ASSOC(
1551 P_PROCESS_OBJECTIVE_ID => l_process_objective_id
1552 ,P_PK1 => l_process_id
1553 --01.13.2005 npanandi: added pk2,pk3 for Ctrl to Obj association
1554 ,P_PK2 => LX_RISK_ID
1555 ,P_PK3 => LX_CONTROL_ID
1556 ,P_OBJECT_TYPE => 'CONTROL'
1557 );
1558 EXCEPTION
1559 WHEN OTHERS THEN
1560 V_ERR_MSG := 'INTERFACE_ID := '||v_interface_id||' '||SUBSTR (SQLERRM, 1, 200);
1561 UPDATE_INTERFACE_WITH_ERROR(V_ERR_MSG,V_TABLE_NAME,v_interface_id);
1562 END;
1563 end if;
1564 END IF; --END OF IF FOR L_CTRL_OBJ_APPR_DATE CHK
1565 end if; --end of if L_CTRL_OBJ_ASSOCIATION_ID NULLITY CHK
1566 end if; --- end of if no error found for CTRL insertions
1567
1568 --
1569 -- create the risk associations
1570 --
1571 IF (not v_error_found AND l_process_id is not null AND l_process_id <> -1
1572 --NPANANDI 10/26/2004:
1573 --DISREGARD THE REVISE FLAGS FOR ASSOCIATIONS, POST AMW.D
1574
1575 --AND
1576 --(((l_revise_risk_flag ='Y' OR v_risk_db_approval_status is null)
1577 -- AND l_risk_approval_status_code = 'A')
1578 --OR (l_revise_risk_flag='N' and v_risk_db_approval_status='A')
1579 ) THEN
1580 --SET THESE 2 VARS TO NULL FOR THIS LOOP PROCESSING
1581 L_RISK_ASSOCIATION_ID := NULL;
1582 L_RISK_APPROVAL_DATE := NULL;
1583 --npanandi 10/26/2004: changed the way check is done post AMW.D
1584 BEGIN
1585 SELECT RISK_ASSOCIATION_ID,APPROVAL_DATE
1586 INTO L_RISK_ASSOCIATION_ID,L_RISK_APPROVAL_DATE
1587 FROM AMW_RISK_ASSOCIATIONS
1588 WHERE RISK_ID=lx_risk_id
1589 AND PK1=L_PROCESS_ID
1590 AND OBJECT_TYPE='PROCESS'
1591 AND DELETION_DATE IS NULL;
1592 EXCEPTION
1593 WHEN NO_DATA_FOUND THEN
1594 NULL;
1595 END;
1596
1597 ---03.02.2005 npanandi: check here to see if this Process has AMW_UPDATE_AP_DETAILS
1598 ---privilege to associate this Control to AP
1599 l_has_proc_risk_assoc_access := 'T';
1600 l_has_proc_risk_assoc_access := check_function(
1601 p_function => 'AMW_UPD_RL_PROC_RISK_ASSOC'
1602 ,p_object_name => 'AMW_PROCESS_APPR_ETTY'
1603 ,p_instance_pk1_value => L_PROCESS_ID
1604 ,p_user_id => fnd_global.user_id);
1605 fnd_file.put_line(fnd_file.log,'%%%%%%%%%%%%%%% l_has_proc_risk_assoc_access: '||l_has_proc_risk_assoc_access||' %%%%%%%%%%%%%%%');
1606
1607 ---03.02.2005 npanandi: added this for access privilege
1608 ---check before inserting/update AmwRiskAssociations
1609 if(l_has_proc_risk_assoc_access = 'T') then
1610
1611 IF(L_RISK_ASSOCIATION_ID IS NULL) THEN
1612 --NO ROW RETRIEVED, SO ASSOCIATION DOESN'T EXIST YET
1613 --CREATE AN ASSOCIATION, SET ASSOCIATION_CREATION_DATE=SYSDATE
1614 CREATE_AMW_RISK_ASSOC(
1615 P_RISK_ID => lx_risk_id
1616 ,P_PROCESS_ID => l_process_id
1617 ,P_RISK_LIKELIHOOD_CODE => rc_rec.risk_likelihood_code
1618 ,P_RISK_IMPACT_CODE => rc_rec.risk_impact_code
1619 ,P_MATERIAL => RC_REC.MATERIAL
1620 ,P_MATERIAL_VALUE => RC_REC.MATERIAL_VALUE);
1621
1622 ELSE
1623 --THIS MEANS THAT ASSOCIATION EXISTS, SO CHECK APPROVAL_DATE
1624
1625 --IF APPROVAL_DATE IS NULL FOR RISK_ASSOCIATIONS,
1626 --THIS MEANS THAT THIS ASSOCIATION
1627 --IS LATEST_REVISION, SO SIMPLY UPDATE ASSOC ATTRIBUTES
1628 IF(L_RISK_APPROVAL_DATE IS NOT NULL) THEN
1629 --THIS MEANS THAT THIS IS LATEST REVISION FOR THIS ASSOCIATION
1630 --AND IS APPROVED, SO
1631 FND_FILE.PUT_LINE(FND_FILE.LOG,'UPDATE THIS ASSOCIATION, THEN INSERT');
1632 BEGIN
1633 UPDATE AMW_RISK_ASSOCIATIONS
1634 SET DELETION_DATE=SYSDATE
1635 ,OBJECT_VERSION_NUMBER=OBJECT_VERSION_NUMBER+1
1636 ,LAST_UPDATE_DATE=SYSDATE
1637 ,LAST_UPDATED_BY=G_USER_ID
1638 ,LAST_UPDATE_LOGIN=G_LOGIN_ID
1639 WHERE RISK_ASSOCIATION_ID=L_RISK_ASSOCIATION_ID;
1640
1641 CREATE_AMW_RISK_ASSOC(
1642 P_RISK_ID => lx_risk_id
1643 ,P_PROCESS_ID => l_process_id
1644 ,P_RISK_LIKELIHOOD_CODE => rc_rec.risk_likelihood_code
1645 ,P_RISK_IMPACT_CODE => rc_rec.risk_impact_code
1646 ,P_MATERIAL => RC_REC.MATERIAL
1647 ,P_MATERIAL_VALUE => RC_REC.MATERIAL_VALUE);
1648 EXCEPTION
1649 WHEN OTHERS THEN
1650 V_ERR_MSG := 'INTERFACE_ID := '||v_interface_id||' '||SUBSTR (SQLERRM, 1, 200);
1651 UPDATE_INTERFACE_WITH_ERROR(V_ERR_MSG,V_TABLE_NAME,v_interface_id);
1652 END;
1653 ELSE
1654 --THIS MEANS THAT THIS IS LATEST REVISION FOR THIS ASSOCIATION
1655 --AND IS NOT APPROVED, SO SIMPLY UPDATE THE ATTRIBUTES
1656 UPDATE AMW_RISK_ASSOCIATIONS
1657 SET RISK_LIKELIHOOD_CODE=rc_rec.risk_likelihood_code
1658 ,RISK_IMPACT_CODE=rc_rec.risk_IMPACT_code
1659 ,MATERIAL=RC_REC.MATERIAL
1660 ,MATERIAL_VALUE=RC_REC.MATERIAL_VALUE
1661 ,OBJECT_VERSION_NUMBER=OBJECT_VERSION_NUMBER+1
1662 ,LAST_UPDATE_DATE=SYSDATE
1663 ,LAST_UPDATED_BY=G_USER_ID
1664 ,LAST_UPDATE_LOGIN=G_LOGIN_ID
1665 WHERE RISK_ASSOCIATION_ID=L_RISK_ASSOCIATION_ID;
1666 END IF;
1667 END IF; -- end of IF l_object_type_count = 0 THEN
1668 end if; --end of if for l_has_proc_risk_assoc_access check
1669 END IF; -- --- end of if no error found for risk insertions
1670
1671 --NEED TO CALL BELOW APIS TO RESET RISK/CTRL COUNTS
1672 --THIS HAVE TO BE CALLED ON A PER ROW BASIS, AS AGAINST
1673 --THE EARLIER AMIT'S SYNCH API, SINCE THE BELOW APIS TAKE PROCESS_ID AS PARAMETER
1674 IF(L_PROCESS_ID IS NOT NULL AND L_PROCESS_ID <> -1) THEN
1675 AMW_RL_HIERARCHY_PKG.update_latest_control_counts(P_PROCESS_ID => L_PROCESS_ID);
1676 AMW_RL_HIERARCHY_PKG.update_latest_risk_counts(P_PROCESS_ID => L_PROCESS_ID);
1677 END IF;
1678
1679 EXCEPTION
1680 WHEN OTHERS THEN
1681 v_err_msg := 'interface_id: = '|| v_interface_id|| ' '|| SUBSTR (SQLERRM, 1, 100);
1682 v_table_name := 'UNKNOWN';
1683 update_interface_with_error (v_err_msg
1684 ,v_table_name
1685 ,v_interface_id);
1686 fnd_file.put_line (fnd_file.LOG, 'err in interface rec '||v_interface_id
1687 ||': '||SUBSTR (v_err_msg, 1, 200));
1688 END;
1689 END LOOP;
1690
1691 --
1692 -- check profile option for (deletion of interface record, when the value is 'N', otherwise
1693 -- set processed flag to 'Y', and update record
1694 --
1695 IF v_error_found THEN
1696 ROLLBACK;
1697 l_process_flag := NULL;
1698 ELSE
1699 l_process_flag := 'Y';
1700 END IF;
1701
1702
1703 IF UPPER (l_amw_delt_risk_cntl_intf) <> 'Y' THEN
1704 BEGIN
1705 UPDATE amw_risk_ctrl_interface
1706 SET process_flag = l_process_flag
1707 ,last_update_date = SYSDATE
1708 ,last_updated_by = v_user_id
1709 WHERE batch_id = p_batch_id;
1710 EXCEPTION
1711 WHEN OTHERS THEN
1712 fnd_file.put_line (fnd_file.LOG
1713 ,'err in update process flag: '||SUBSTR (SQLERRM, 1, 200));
1714 END;
1715 ELSE
1716 IF NOT v_error_found THEN
1717 BEGIN
1718 DELETE FROM amw_risk_ctrl_interface
1719 WHERE batch_id = p_batch_id;
1720
1721 EXCEPTION
1722 WHEN OTHERS THEN
1723 fnd_file.put_line (fnd_file.LOG
1724 ,'err in delete interface records: '||SUBSTR (SQLERRM, 1, 200));
1725 END;
1726 END IF;
1727 END IF;
1728
1729
1730 EXCEPTION
1731 /** nilesh added for invalid risk type **/
1732 WHEN e_invalid_risk_type THEN
1733 fnd_file.put_line (fnd_file.LOG
1734 , 'Invalid Risk Type.');
1735
1736 BEGIN
1737 IF v_invalid_risk_type is null THEN
1738 v_invalid_risk_type := FND_MESSAGE.GET_STRING('AMW', 'AMW_INVALID_RISK_TYPE');
1739 END IF;
1740 UPDATE amw_risk_ctrl_interface
1741 SET error_flag = 'Y'
1742 ,interface_status = v_invalid_risk_type
1743 WHERE batch_id = p_batch_id;
1744 EXCEPTION
1745 WHEN OTHERS THEN
1746 fnd_file.put_line (fnd_file.LOG
1747 , 'unexpected exception in handling e_invalid_risk_type: '||sqlerrm);
1748 END;
1749 /** nilesh added for invalid risk type **/
1750
1751 WHEN e_invalid_requestor_id THEN
1752 fnd_file.put_line (fnd_file.LOG
1753 , 'Invalid requestor id.');
1754
1755 BEGIN
1756 IF v_invalid_requestor_msg is null THEN
1757 v_invalid_requestor_msg := FND_MESSAGE.GET_STRING('AMW', 'AMW_UNKNOWN_EMPLOYEE');
1758 END IF;
1759 UPDATE amw_risk_ctrl_interface
1760 SET error_flag = 'Y'
1761 ,interface_status = v_invalid_requestor_msg
1762 WHERE batch_id = p_batch_id;
1763 EXCEPTION
1764 WHEN OTHERS THEN
1765 fnd_file.put_line (fnd_file.LOG
1766 , 'unexpected exception in handling e_invalid_requestor_id: '||sqlerrm);
1767 END;
1768 WHEN e_no_import_access THEN
1769 fnd_file.put_line (fnd_file.LOG
1770 , 'no import privilege');
1771
1772 BEGIN
1773 IF v_no_import_privilege_msg is null THEN
1774 v_no_import_privilege_msg := FND_MESSAGE.GET_STRING('AMW', 'AMW_NO_IMPORT_ACCESS');
1775 END IF;
1776 UPDATE amw_risk_ctrl_interface
1777 SET error_flag = 'Y'
1778 ,interface_status = v_no_import_privilege_msg
1779 WHERE batch_id = p_batch_id;
1780 EXCEPTION
1781 WHEN OTHERS THEN
1782 fnd_file.put_line (fnd_file.LOG
1783 , 'unexpected exception in handling e_no_import_access: '||sqlerrm);
1784 END;
1785 WHEN others THEN
1786 rollback;
1787 fnd_file.put_line (fnd_file.LOG
1788 , 'unexpected exception in create_risks_and_controls: '||sqlerrm);
1789 END create_risks_and_controls;
1790
1791 --
1792 -- Insert or update the Risk Types
1793 --
1794 --
1795 PROCEDURE create_process_objectives (p_process_objective_name IN VARCHAR2,
1796 p_process_obj_description IN Varchar2,
1797 p_requestor_id IN number,
1798 x_return_status out nocopy varchar2)
1799 IS
1800 CURSOR c_get_objective IS
1801 SELECT name,process_objective_id
1802 FROM AMW_process_objectives_vl
1803 WHERE name = p_process_objective_name;
1804
1805
1806 CURSOR c_process_objective_id IS
1807 SELECT AMW_process_objectives_S.NEXTVAL
1808 FROM dual;
1809
1810 l_rowid amw_process_objectives_vl.row_id%type;
1811 l_get_objective c_get_objective%rowtype;
1812 l_process_objective_id number;
1813 e_invalid_obj EXCEPTION;
1814 e_invalid_flag EXCEPTION;
1815 e_invalid_risk_type_flag exception;
1816 BEGIN
1817 fnd_file.put_line(fnd_file.log,'~~~~~~~~~~~~~~~~~~~~~~ create_process_objectives: START');
1818 fnd_file.put_line(fnd_file.log,'p_process_objective_name: '||p_process_objective_name);
1819 open c_get_objective;
1820 fetch c_get_objective into l_get_objective;
1821 close c_get_objective;
1822
1823 if(l_get_objective.process_objective_id is null)then
1824
1825 fnd_file.put_line(fnd_file.log,'p_process_objective_name does not exist');
1826
1827 OPEN c_process_objective_id;
1828 FETCH c_process_objective_id INTO l_process_objective_id;
1829 CLOSE c_process_objective_id;
1830
1831 amw_process_objectives_b_pkg.insert_row(
1832 X_ROWID => l_rowid,
1833 x_last_updated_by => g_user_id,
1834 x_last_update_date => sysdate,
1835 x_created_by => g_user_id,
1836 x_creation_date => sysdate,
1837 x_last_update_login => g_login_id,
1838 x_objective_type => 'C',
1839 x_start_date => sysdate,
1840 x_end_date => null,
1841 x_attribute_category => null,
1842 x_attribute1 => null,
1843 x_attribute2 => null,
1844 x_attribute3 => null,
1845 x_attribute4 => null,
1846 x_attribute5 => null,
1847 x_attribute6 => null,
1848 x_attribute7 => null,
1849 x_attribute8 => null,
1850 x_attribute9 => null,
1851 x_attribute10 => null,
1852 x_attribute11 => null,
1853 x_attribute12 => null,
1854 x_attribute13 => null,
1855 x_attribute14 => null,
1856 x_attribute15 => null,
1857 x_security_group_id => null,
1858 x_object_version_number => 1,
1859 x_process_objective_id => l_process_objective_id,
1860 x_requestor_id => p_requestor_id,
1861 X_NAME => p_process_objective_name,
1862 X_DESCRIPTION => p_process_obj_description
1863 );
1864
1865 vx_process_objective_id := l_process_objective_id;
1866 fnd_file.put_line(fnd_file.log,'vx_process_objective_id: '||vx_process_objective_id);
1867 else
1868 vx_process_objective_id := l_get_objective.process_objective_id;
1869 fnd_file.put_line(fnd_file.log,'p_process_objective_name exists --> vx_process_objective_id: '||vx_process_objective_id);
1870 update AMW_PROCESS_OBJECTIVEs_TL set
1871 NAME = p_process_objective_name,
1872 DESCRIPTION = p_process_obj_description,
1873 LAST_UPDATE_DATE = sysdate,
1874 LAST_UPDATED_BY = g_user_id,
1875 LAST_UPDATE_LOGIN = g_login_id,
1876 SOURCE_LANG = userenv('LANG')
1877 where PROCESS_OBJECTIVE_ID = l_get_objective.process_objective_id
1878 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
1879
1880 if (sql%notfound) then
1881 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1882 end if;
1883
1884 end if;
1885
1886 fnd_file.put_line(fnd_file.log,'~~~~~~~~~~~~~~~~~~~~~~ create_process_objectives: END');
1887
1888 EXCEPTION
1889 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1890 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1891 v_err_msg :=
1892 'Error working in procedure Create_Process_Objectives: '
1893 || 'Process_Objective_Name: '
1894 || p_process_objective_name
1895 || 'using interface id of: '
1896 || v_interface_id
1897 || ' '
1898 || SUBSTR (SQLERRM, 1, 100)
1899 || ' ';
1900 v_table_name := 'AMW_PROCESS_OBJECTIVES_B';
1901 update_interface_with_error (v_err_msg
1902 ,v_table_name
1903 ,v_interface_id
1904 );
1905
1906 WHEN OTHERS THEN
1907 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1908 v_err_msg :=
1909 'Error working in procedure Create_Process_Objectives: '
1910 || 'Process_Objective_Name: '
1911 || p_process_objective_name
1912 || 'using interface id of: '
1913 || v_interface_id
1914 || ' '
1915 || SUBSTR (SQLERRM, 1, 100)
1916 || ' ';
1917 v_table_name := 'AMW_PROCESS_OBJECTIVES_B';
1918 update_interface_with_error (v_err_msg
1919 ,v_table_name
1920 ,v_interface_id
1921 );
1922 END create_process_objectives;
1923
1924
1925
1926 --
1927 -- Insert or update the Risk Types
1928 --
1929 --
1930 PROCEDURE risk_types (p_risk_type_flag IN VARCHAR2, p_lookup_tag IN VARCHAR2, p_is_last_call in varchar2)
1931 IS
1932
1933 -- kosriniv changing cursor defn. for basel-II enhancement
1934 CURSOR c_risk_type IS
1935 SELECT risk_type_code
1936 FROM AMW_SETUP_RISK_TYPES_VL
1937 WHERE
1938 start_date <= sysdate AND (end_date IS NULL OR end_date >= sysdate)
1939 AND tag=p_lookup_tag;
1940 --CURSOR c_risk_type IS
1941 -- SELECT lookup_code
1942 -- FROM AMW_LOOKUPS
1943 -- WHERE lookup_type='AMW_RISK_TYPE'
1944 -- AND enabled_flag='Y'
1945 -- AND tag=p_lookup_tag;
1946
1947 CURSOR c_risk_type_id IS
1948 SELECT AMW_RISK_TYPE_S.NEXTVAL
1949 FROM dual;
1950
1951 l_risk_type_code VARCHAR2(30);
1952 l_risk_type_code_del VARCHAR2(30);
1953 l_risk_type_flag VARCHAR2(1);
1954 l_risk_type_flag_dummy VARCHAR2(1);
1955 ---l_risk_type_exists varchar2(30);
1956 l_risk_type_exists number;
1957 l_RISK_type_id number;
1958 e_invalid_obj EXCEPTION;
1959 e_invalid_flag EXCEPTION;
1960 e_invalid_risk_type_flag exception;
1961 BEGIN
1962
1963 --if(p_risk_type_flag is null or trim(p_risk_type_flag) = '')then
1964 -- l_risk_type_flag_dummy := 'N';
1965 --else
1966 l_risk_type_flag_dummy := upper(p_risk_type_flag);
1967
1968
1969 --end if;
1970 if(l_risk_type_flag_dummy = 'Y') then
1971 v_valid_risk_type := v_valid_risk_type+1;
1972 end if;
1973
1974 if(upper(p_is_last_call) = 'Y')then
1975 if(v_valid_risk_type = 0) then
1976 raise e_invalid_risk_type_flag;
1977 end if;
1978 end if;
1979
1980 IF l_risk_type_flag_dummy is not null THEN
1981 IF UPPER (l_risk_type_flag_dummy) = 'Y' THEN
1982 l_risk_type_flag := 'Y';
1983 ELSIF UPPER (l_risk_type_flag_dummy) = 'N' THEN
1984 l_risk_type_flag := 'N';
1985 ELSE
1986 RAISE e_invalid_flag;
1987 END IF;
1988 --end if;
1989
1990 --if(p_risk_type_flag is null) then
1991 --l_risk_type_flag := 'N';
1992 --end if;
1993
1994 OPEN c_risk_type;
1995 FETCH c_risk_type INTO l_risk_type_code;
1996 IF (c_risk_type%NOTFOUND) THEN
1997 CLOSE c_risk_type;
1998 ---RAISE e_invalid_obj;
1999 END IF;
2000 CLOSE c_risk_type;
2001
2002
2003 if(l_risk_type_flag = 'N') then
2004
2005 select count(*) into l_risk_type_exists
2006 from amw_risk_type
2007 where risk_rev_id = lx_risk_rev_id
2008 and risk_type_code = l_risk_type_code;
2009
2010 if(l_risk_type_exists > 0) then
2011 delete from amw_risk_type
2012 where risk_rev_id=lx_risk_rev_id
2013 and risk_type_code=l_risk_type_code;
2014 end if;
2015 end if;
2016
2017 ---insert into test_test (text,creation_date) values ('risk_type_flag: '||p_risk_type_flag||' lookup_tag: '||p_lookup_tag,sysdate);
2018
2019
2020 if(l_risk_type_flag = 'Y') then
2021
2022 --this risk_type has been selected for this risk ....
2023 --need to check if this row already exists in amw_risk_type
2024 --for this risk_rev_id
2025
2026 select count(*) into l_risk_type_exists
2027 from amw_risk_type
2028 where risk_rev_id = lx_risk_rev_id
2029 and risk_type_code = l_risk_type_code;
2030
2031 --IF SQL%NOTFOUND THEN
2032 if(l_risk_type_exists = 0) then
2033
2034 --this means that this risk_type is not present
2035 --for the current risk ... hence insert a new row
2036
2037 OPEN c_risk_type_id;
2038 FETCH c_risk_type_id INTO l_RISK_type_id;
2039 CLOSE c_risk_type_id;
2040
2041 INSERT INTO amw_risk_type
2042 (risk_type_id,
2043 last_update_date,
2044 last_updated_by,
2045 creation_date,
2046 created_by,
2047 last_update_login,
2048 risk_rev_id,
2049 risk_type_code,
2050 OBJECT_VERSION_NUMBER) VALUES
2051 (l_risk_type_id,
2052 SYSDATE,
2053 g_user_id,
2054 SYSDATE,
2055 g_user_id,
2056 g_login_id,
2057 lx_risk_rev_id,
2058 l_risk_type_code,
2059 1);
2060 END IF; -- end if for l_risk_type_exists = 0
2061 end if; --- end if for l_risk_type_flag = 'Y'
2062 END IF; -- end of if risk_type_flag is not null condition
2063
2064 EXCEPTION
2065 WHEN e_invalid_risk_type_flag THEN
2066 v_err_msg :=
2067 'Error working in procedure risk types: '
2068 || 'Atleast one Risk_Type flag must be Y/N';
2069 v_table_name := 'AMW_RISK_TYPE';
2070 update_interface_with_error (v_err_msg
2071 ,v_table_name
2072 ,v_interface_id
2073 );
2074
2075 WHEN e_invalid_flag THEN
2076 v_err_msg :=
2077 'Error working in procedure risk types: '
2078 || 'risk_type_code tag: '
2079 || p_lookup_tag
2080 || 'using interface id of: '
2081 || v_interface_id
2082 || ' flag must be Y/N';
2083 v_table_name := 'AMW_RISK_TYPE';
2084 update_interface_with_error (v_err_msg
2085 ,v_table_name
2086 ,v_interface_id
2087 );
2088 WHEN e_invalid_obj THEN
2089 v_err_msg :=
2090 'Error working in procedure risk types: '
2091 || 'risk_type_code tag: '
2092 || p_lookup_tag
2093 || 'using interface id of: '
2094 || v_interface_id
2095 || ' code does not exist';
2096 v_table_name := 'AMW_RISK_TYPE';
2097 update_interface_with_error (v_err_msg
2098 ,v_table_name
2099 ,v_interface_id
2100 );
2101 WHEN OTHERS THEN
2102 v_err_msg :=
2103 'Error working in procedure risk types: '
2104 || 'risk_type_code tag: '
2105 || p_lookup_tag
2106 || 'using interface id of: '
2107 || v_interface_id
2108 || ' '
2109 || SUBSTR (SQLERRM, 1, 100)
2110 || ' ';
2111 v_table_name := 'AMW_RISK_TYPE';
2112 update_interface_with_error (v_err_msg
2113 ,v_table_name
2114 ,v_interface_id
2115 );
2116 END risk_types;
2117
2118 --
2119 -- Insert or update the control objectives
2120 --
2121 --
2122
2123 PROCEDURE control_objectives (p_ctrl_obj_flag IN VARCHAR2, p_lookup_tag IN VARCHAR2)
2124 IS
2125 CURSOR c_control_obj IS
2126 SELECT lookup_code
2127 FROM AMW_LOOKUPS
2128 WHERE lookup_type='AMW_CONTROL_OBJECTIVES'
2129 AND enabled_flag='Y'
2130 AND tag=p_lookup_tag;
2131
2132 l_ctrl_obj_code VARCHAR2(30);
2133 l_ctrl_obj_flag VARCHAR2(1);
2134 l_obj_exists number;
2135 e_invalid_obj EXCEPTION;
2136 e_invalid_flag EXCEPTION;
2137 BEGIN
2138 ---fnd_file.put_line(fnd_file.log,'control_objectives --> p_ctrl_obj_flag: '||p_ctrl_obj_flag||'p_lookup_tag: '||p_lookup_tag);
2139
2140 IF p_ctrl_obj_flag is not null THEN
2141 IF UPPER (p_ctrl_obj_flag) = 'Y' THEN
2142 l_ctrl_obj_flag := 'Y';
2143 ELSIF UPPER (p_ctrl_obj_flag) = 'N' THEN
2144 l_ctrl_obj_flag := 'N';
2145 ELSE
2146 RAISE e_invalid_flag;
2147 END IF;
2148
2149 OPEN c_control_obj;
2150 FETCH c_control_obj INTO l_ctrl_obj_code;
2151 IF (c_control_obj%NOTFOUND) THEN
2152 CLOSE c_control_obj;
2153 RAISE e_invalid_obj;
2154 END IF;
2155 CLOSE c_control_obj;
2156
2157 if (l_ctrl_obj_flag = 'N') then
2158 select count(*)
2159 into l_obj_exists
2160 from amw_control_objectives
2161 where control_rev_id = vx_control_rev_id
2162 AND objective_code = l_ctrl_obj_code;
2163
2164 if(l_obj_exists > 0) then
2165 delete from amw_control_objectives
2166 where control_rev_id = vx_control_rev_id
2167 AND objective_code = l_ctrl_obj_code;
2168 end if;
2169 end if;
2170
2171 if (l_ctrl_obj_flag = 'Y') then
2172 --- this control objective has been selected
2173 --- need to check if it is already present or not
2174 select count(*)
2175 into l_obj_exists
2176 from amw_control_objectives
2177 where control_rev_id = vx_control_rev_id
2178 AND objective_code = l_ctrl_obj_code;
2179
2180 if(l_obj_exists = 0) then
2181 ---IF SQL%NOTFOUND THEN
2182 INSERT INTO amw_control_objectives
2183 (control_objective_id
2184 ,last_update_date
2185 ,last_updated_by
2186 ,creation_date
2187 ,created_by
2188 ,control_rev_id
2189 ,objective_code
2190 ,OBJECT_VERSION_NUMBER
2191 ) VALUES (
2192 amw_control_objectives_s.NEXTVAL
2193 ,SYSDATE
2194 ,v_user_id
2195 ,SYSDATE
2196 ,v_user_id
2197 ,vx_control_rev_id
2198 ,l_ctrl_obj_code
2199 ,1
2200 );
2201 end if; -- end if for l_obj_exists = 0
2202 END IF; --end if for l_obj_flag = 'Y'
2203 END IF; -- end if for objective not null condition
2204
2205 EXCEPTION
2206 WHEN e_invalid_flag THEN
2207 v_err_msg :=
2208 'Error working in procedure control objectives: '
2209 || 'objective code tag: '
2210 || p_lookup_tag
2211 || 'using interface id of: '
2212 || v_interface_id
2213 || ' flag must be Y/N';
2214 v_table_name := 'AMW_CONTROL_OBJECTIVES';
2215 update_interface_with_error (v_err_msg
2216 ,v_table_name
2217 ,v_interface_id
2218 );
2219 WHEN e_invalid_obj THEN
2220 v_err_msg :=
2221 'Error working in procedure control objectives: '
2222 || 'objective code tag: '
2223 || p_lookup_tag
2224 || 'using interface id of: '
2225 || v_interface_id
2226 || ' code does not exist';
2227 v_table_name := 'AMW_CONTROL_OBJECTIVES';
2228 update_interface_with_error (v_err_msg
2229 ,v_table_name
2230 ,v_interface_id
2231 );
2232 WHEN OTHERS THEN
2233 v_err_msg :=
2234 'Error working in procedure control objectives: '
2235 || 'objective code tag: '
2236 || p_lookup_tag
2237 || 'using interface id of: '
2238 || v_interface_id
2239 || ' '
2240 || SUBSTR (SQLERRM, 1, 100);
2241 v_table_name := 'AMW_CONTROL_OBJECTIVES';
2242 update_interface_with_error (v_err_msg
2243 ,v_table_name
2244 ,v_interface_id
2245 );
2246 END control_objectives;
2247
2248
2249 --
2250 -- Insert or update the control assertions
2251 --
2252 PROCEDURE control_assertions (p_ctrl_assert_flag IN VARCHAR2, p_lookup_tag IN VARCHAR2)
2253 IS
2254 CURSOR c_control_assert IS
2255 SELECT lookup_code
2256 FROM AMW_LOOKUPS
2257 WHERE lookup_type='AMW_CONTROL_ASSERTIONS'
2258 AND enabled_flag='Y'
2259 AND tag=p_lookup_tag;
2260
2261 l_ctrl_assert_code VARCHAR2(30);
2262 l_ctrl_assert_flag VARCHAR2(1);
2263 l_assert_exists number;
2264 e_invalid_assert EXCEPTION;
2265 e_invalid_flag EXCEPTION;
2266 BEGIN
2267 -----fnd_file.put_line(fnd_file.log,'control_assertions --> p_ctrl_assert_flag: '||p_ctrl_assert_flag||'p_lookup_tag: '||p_lookup_tag);
2268
2269 IF (p_ctrl_assert_flag is not null) THEN
2270 IF UPPER (p_ctrl_assert_flag) = 'Y' THEN
2271 l_ctrl_assert_flag := 'Y';
2272 ELSIF UPPER (p_ctrl_assert_flag) = 'N' THEN
2273 l_ctrl_assert_flag := 'N';
2274 ELSE
2275 RAISE e_invalid_flag;
2276 END IF;
2277
2278 OPEN c_control_assert;
2279 FETCH c_control_assert INTO l_ctrl_assert_code;
2280 IF (c_control_assert%NOTFOUND) THEN
2281 CLOSE c_control_assert;
2282 RAISE e_invalid_assert;
2283 END IF;
2284 CLOSE c_control_assert;
2285
2286 if (l_ctrl_assert_flag = 'N') then
2287 select count(*)
2288 into l_assert_exists
2289 from amw_control_assertions
2290 where control_rev_id = vx_control_rev_id
2291 AND assertion_code = l_ctrl_assert_code;
2292
2293 if(l_assert_exists > 0) then
2294 delete from amw_control_assertions
2295 where control_rev_id = vx_control_rev_id
2296 AND assertion_code = l_ctrl_assert_code;
2297 end if;
2298 end if;
2299
2300 if(l_ctrl_assert_flag = 'Y') then
2301 select count(*)
2302 into l_assert_exists
2303 from amw_control_assertions
2304 where control_rev_id = vx_control_rev_id
2305 AND assertion_code = l_ctrl_assert_code;
2306
2307 if(l_assert_exists = 0)then
2308 ---IF SQL%NOTFOUND THEN
2309 INSERT INTO amw_control_assertions
2310 (control_assertion_id
2311 ,last_update_date
2312 ,last_updated_by
2313 ,creation_date
2314 ,created_by
2315 ,control_rev_id
2316 ,ASSERTION_CODE
2317 ----not using the date columns anyway
2318 ----,effective_date_from
2319 ----,effective_date_to
2320 ,OBJECT_VERSION_NUMBER
2321 ) VALUES (
2322 amw_control_assertions_s.NEXTVAL
2323 ,SYSDATE
2324 ,v_user_id
2325 ,SYSDATE
2326 ,v_user_id
2327 ,vx_control_rev_id
2328 ,l_ctrl_assert_code
2329 ----not using the date columns anyway
2330 ----,SYSDATE
2331 ----,DECODE (l_ctrl_assert_flag, 'N', SYSDATE, NULL)
2332 ,1
2333 );
2334 end if; --end of if for l_assert_exists
2335 end if; -- end of if for l_assert_flag = 'Y'
2336 END IF; --- end of if p_assert_flag not null
2337 EXCEPTION
2338 WHEN e_invalid_flag THEN
2339 v_err_msg :=
2340 'Error working in procedure control assertions: '
2341 || 'assertion code tag: '
2342 || p_lookup_tag
2343 || 'using interface id of: '
2344 || v_interface_id
2345 || ' flag must be Y/N';
2346 v_table_name := 'AMW_CONTROL_ASSERTIONS';
2347 update_interface_with_error (v_err_msg
2348 ,v_table_name
2349 ,v_interface_id
2350 );
2351 WHEN e_invalid_assert THEN
2352 v_err_msg :=
2353 'Error working in procedure control assertions: '
2354 || 'assertion code tag: '
2355 || p_lookup_tag
2356 || 'using interface id of: '
2357 || v_interface_id
2358 || ' code does not exist';
2359 v_table_name := 'AMW_CONTROL_ASSERTIONS';
2360 update_interface_with_error (v_err_msg
2361 ,v_table_name
2362 ,v_interface_id
2363 );
2364 WHEN OTHERS THEN
2365 v_err_msg :=
2366 'Error working in procedure control assertions: '
2367 || 'assertion code tag: '
2368 || p_lookup_tag
2369 || 'using interface id of: '
2370 || v_interface_id
2371 || ' '
2372 || SUBSTR (SQLERRM, 1, 100);
2373 v_table_name := 'AMW_CONTROL_ASSERTIONS';
2374 update_interface_with_error (v_err_msg
2375 ,v_table_name
2376 ,v_interface_id
2377 );
2378 END control_assertions;
2379
2380
2381 --
2382 -- procedure update_interface_with_error
2383 --
2384 --
2385 PROCEDURE update_interface_with_error (
2386 p_err_msg IN VARCHAR2
2387 ,p_table_name IN VARCHAR2
2388 ,p_interface_id IN NUMBER
2389 )
2390 IS
2391 l_interface_status amw_risk_ctrl_interface.interface_status%TYPE;
2392 BEGIN
2393 ROLLBACK; -- rollback any inserts done during the current loop process
2394 v_error_found := TRUE;
2395
2396 BEGIN
2397 SELECT interface_status
2398 INTO l_interface_status
2399 FROM amw_risk_ctrl_interface
2400 WHERE risk_control_interface_id = p_interface_id;
2401 EXCEPTION
2402 WHEN OTHERS
2403 THEN
2404 v_err_msg :=
2405 'interface_id: = '
2406 || p_interface_id
2407 || ' '
2408 || SUBSTR (SQLERRM, 1, 100);
2409 fnd_file.put_line (fnd_file.LOG, SUBSTR (v_err_msg, 1, 200));
2410 END;
2411
2412 BEGIN
2413 UPDATE amw_risk_ctrl_interface
2414 SET interface_status =
2415 l_interface_status
2416 -- || 'Error Msg: '
2417 || p_err_msg
2418 -- || ' Table Name: '
2419 -- || p_table_name
2420 || '**'
2421 ,error_flag = 'Y'
2422 WHERE risk_control_interface_id = p_interface_id;
2423
2424 fnd_file.put_line (fnd_file.LOG, SUBSTR (l_interface_status, 1, 200));
2425 COMMIT;
2426 EXCEPTION
2427 WHEN OTHERS
2428 THEN
2429 v_err_msg :=
2430 'Error during package processing '
2431 || ' interface_id: = '
2432 || p_interface_id
2433 || SUBSTR (SQLERRM, 1, 100);
2434 fnd_file.put_line (fnd_file.LOG, SUBSTR (v_err_msg, 1, 200));
2435 END;
2436
2437 COMMIT;
2438 END update_interface_with_error;
2439
2440 --KOSRINIV ---control_components procedure
2441 --
2442 -- Insert or update the control components
2443 --
2444 --
2445
2446 PROCEDURE control_components (p_ctrl_comp_flag IN VARCHAR2, p_lookup_tag IN VARCHAR2)
2447 IS
2448 CURSOR c_control_comp IS
2449 SELECT lookup_code
2450 FROM AMW_LOOKUPS
2451 WHERE lookup_type='AMW_ASSESSMENT_COMPONENTS'
2452 AND enabled_flag='Y'
2453 AND tag=p_lookup_tag;
2454
2455 l_ctrl_comp_code VARCHAR2(30);
2456 l_ctrl_comp_flag VARCHAR2(1);
2457 l_comp_exists number;
2458 e_invalid_comp EXCEPTION;
2459 e_invalid_flag EXCEPTION;
2460 BEGIN
2461 ---fnd_file.put_line(fnd_file.log,'control_objectives --> p_ctrl_obj_flag: '||p_ctrl_obj_flag||'p_lookup_tag: '||p_lookup_tag);
2462
2463 IF p_ctrl_comp_flag is not null THEN
2464 IF UPPER (p_ctrl_comp_flag) = 'Y' THEN
2465 l_ctrl_comp_flag := 'Y';
2466 ELSIF UPPER (p_ctrl_comp_flag) = 'N' THEN
2467 l_ctrl_comp_flag := 'N';
2468 ELSE
2469 RAISE e_invalid_flag;
2470 END IF;
2471
2472 OPEN c_control_comp;
2473 FETCH c_control_comp INTO l_ctrl_comp_code;
2474 IF (c_control_comp%NOTFOUND) THEN
2475 CLOSE c_control_comp;
2476 RAISE e_invalid_comp;
2477 END IF;
2478 CLOSE c_control_comp;
2479
2480 if (l_ctrl_comp_flag = 'N') then
2481 select count(*)
2482 into l_comp_exists
2483 from amw_assessment_components
2484 where object_type='CONTROL'
2485 AND object_id = vx_control_rev_id
2486 AND component_code = l_ctrl_comp_code;
2487
2488 if(l_comp_exists > 0) then
2489 delete from amw_assessment_components
2490 where object_type='CONTROL'
2491 AND object_id = vx_control_rev_id
2492 AND component_code = l_ctrl_comp_code;
2493 end if;
2494 end if;
2495
2496 if (l_ctrl_comp_flag = 'Y') then
2497 --- this control objective has been selected
2498 --- need to check if it is already present or not
2499 select count(*)
2500 into l_comp_exists
2501 from amw_assessment_components
2502 where object_type='CONTROL'
2503 AND object_id = vx_control_rev_id
2504 AND component_code = l_ctrl_comp_code;
2505
2506 if(l_comp_exists = 0) then
2507 ---IF SQL%NOTFOUND THEN
2508 INSERT INTO amw_assessment_components
2509 (assessment_component_id
2510 ,last_update_date
2511 ,last_updated_by
2512 ,creation_date
2513 ,created_by
2514 ,last_update_login
2515 ,component_code
2516 ,object_type
2517 ,object_id
2518 ,OBJECT_VERSION_NUMBER
2519 ) VALUES (
2520 amw_assessment_components_s.NEXTVAL
2521 ,SYSDATE
2522 ,v_user_id
2523 ,SYSDATE
2524 ,v_user_id
2525 ,v_user_id
2526 ,l_ctrl_comp_code
2527 ,'CONTROL'
2528 ,vx_control_rev_id
2529 ,1
2530 );
2531 end if; -- end if for l_obj_exists = 0
2532 END IF; --end if for l_obj_flag = 'Y'
2533 END IF; -- end if for objective not null condition
2534
2535 EXCEPTION
2536 WHEN e_invalid_flag THEN
2537 v_err_msg :=
2538 'Error working in procedure control components: '
2539 || 'component code tag: '
2540 || p_lookup_tag
2541 || 'using interface id of: '
2542 || v_interface_id
2543 || ' flag must be Y/N';
2544 v_table_name := 'AMW_ASSESSMENT_COMPONENTS';
2545 update_interface_with_error (v_err_msg
2546 ,v_table_name
2547 ,v_interface_id
2548 );
2549 WHEN e_invalid_comp THEN
2550 v_err_msg :=
2551 'Error working in procedure control components: '
2552 || 'component code tag: '
2553 || p_lookup_tag
2554 || 'using interface id of: '
2555 || v_interface_id
2556 || ' code does not exist';
2557 v_table_name := 'AMW_ASSESSMENT_COMPONENTS';
2558 update_interface_with_error (v_err_msg
2559 ,v_table_name
2560 ,v_interface_id
2561 );
2562 WHEN OTHERS THEN
2563 v_err_msg :=
2564 'Error working in procedure control components: '
2565 || 'component code tag: '
2566 || p_lookup_tag
2567 || 'using interface id of: '
2568 || v_interface_id
2569 || ' '
2570 || SUBSTR (SQLERRM, 1, 100);
2571 v_table_name := 'AMW_ASSESSMENT_COMPONENTS';
2572 update_interface_with_error (v_err_msg
2573 ,v_table_name
2574 ,v_interface_id
2575 );
2576 END control_components;
2577
2578 PROCEDURE CREATE_AMW_CTRL_ASSOC(
2579 P_CONTROL_ID IN NUMBER
2580 ,P_PROCESS_ID IN NUMBER
2581 ,P_RISK_ID IN NUMBER
2582 )
2583 IS
2584 l_has_risk_ctrl_assn_access varchar2(15) := 'T'; --defaulting to 'T', which means 'has access'
2585 BEGIN
2586 /**
2587 ---03.02.2005 npanandi: check here to see if this Process has
2588 ---AMW_UPD_RL_PROC_RISK_CTRL_ASSN privilege to associate this Risk to Ctrl
2589 l_has_risk_ctrl_assn_access := check_function(
2590 p_function => 'AMW_UPD_RL_PROC_RISK_CTRL_ASSN'
2591 ,p_object_name => 'AMW_PROCESS_APPR_ETTY'
2592 ,p_instance_pk1_value => P_PROCESS_ID
2593 ,p_user_id => fnd_global.user_id);
2594
2595 fnd_file.put_line(fnd_file.log,'&&&&&&&&&&&&&&& l_has_risk_ctrl_assn_access: '||l_has_risk_ctrl_assn_access||' &&&&&&&&&&&&&&&');
2596 --03.02.2005 npanandi: imposing the foll. IF condition to
2597 --create the association only if access privilege is present
2598 if(l_has_risk_ctrl_assn_access = 'T') then
2599 **/
2600 INSERT INTO amw_control_associations(
2601 control_association_id
2602 ,last_update_date
2603 ,last_updated_by
2604 ,creation_date
2605 ,created_by
2606 ,control_id
2607 ,pk1
2608 ,PK2
2609 ,object_type
2610 ,effective_date_from
2611 ,effective_date_to
2612 ,ASSOCIATION_CREATION_DATE
2613 ,OBJECT_VERSION_NUMBER)
2614 VALUES (
2615 amw_control_associations_s.NEXTVAL
2616 ,SYSDATE
2617 ,v_user_id
2618 ,SYSDATE
2619 ,v_user_id
2620 ,P_control_id
2621 ,P_PROCESS_ID
2622 ,P_RISK_ID
2623 ,'RISK'
2624 ,SYSDATE
2625 ,NULL
2626 ,SYSDATE
2627 ,1
2628 );
2629 ---end if; ---end of l_has_risk_ctrl_assn_access check
2630 END CREATE_AMW_CTRL_ASSOC;
2631
2632 PROCEDURE CREATE_AMW_AP_ASSOC(
2633 P_CONTROL_ID IN NUMBER
2634 ,P_AUDIT_PROCEDURE_ID IN NUMBER
2635 ,P_DESIGN_EFFECTIVENESS IN VARCHAR2
2636 ,P_OP_EFFECTIVENESS IN VARCHAR2
2637 --NPANANDI 12.09.2004: ADDED RETURN STATUS BECAUSE
2638 --CALLING REVISE_AP BELOW
2639 ,X_RETURN_STATUS OUT NOCOPY VARCHAR2
2640 ,X_MSG_COUNT OUT NOCOPY NUMBER
2641 ,X_MSG_DATA OUT NOCOPY VARCHAR2
2642 )
2643 IS
2644 l_has_ap_access varchar2(15) := 'T'; --defaulting to 'T', which means 'has access'
2645 BEGIN
2646 /**
2647 ---03.01.2005 npanandi: check here to see if this AP has AMW_UPDATE_AP_DETAILS
2648 ---privilege to associate this Control to AP
2649 l_has_ap_access := check_function(
2650 p_function => 'AMW_UPDATE_AP_DETAILS'
2651 ,p_object_name => 'AMW_AUDIT_PROCEDURE'
2652 ,p_instance_pk1_value => P_AUDIT_PROCEDURE_ID
2653 ,p_user_id => fnd_global.user_id);
2654
2655 fnd_file.put_line(fnd_file.log,'&&&&&&&&&&&&&&& l_has_ap_access: '||l_has_ap_access||' &&&&&&&&&&&&&&&');
2656 --03.01.2005 npanandi: imposing the foll. IF condition to
2657 --create the association only if access privilege is present
2658 if(l_has_ap_access = 'T') then
2659 **/
2660 fnd_file.put_line(fnd_file.log,'Associating Ctrl to Audit Procedure');
2661 --NPANANDI 12.09.2004: CALLING REVISE_AP FOR CHANGED DATAMODEL
2662 --
2663 AMW_AUDIT_PROCEDURES_PVT.Revise_Ap_If_Necessary(
2664 p_api_version_number => 1,
2665 p_init_msg_list => FND_API.G_TRUE,
2666 P_audit_procedure_id => P_audit_procedure_id,
2667 x_return_status => x_return_status,
2668 x_msg_count => x_msg_count,
2669 x_msg_data => x_msg_data
2670 );
2671
2672 IF(x_return_status = FND_API.G_RET_STS_SUCCESS)THEN
2673 INSERT INTO amw_ap_associations(
2674 ap_association_id
2675 ,last_update_date
2676 ,last_updated_by
2677 ,creation_date
2678 ,created_by
2679 ,last_update_login
2680 ,pk1
2681 ,object_type
2682 ,audit_procedure_id
2683 ,design_effectiveness
2684 ,op_effectiveness
2685 ,object_version_number
2686 ,ASSOCIATION_CREATION_DATE)
2687 VALUES (
2688 AMW_AP_ASSOCIATIONS_S.NEXTVAL
2689 ,SYSDATE
2690 ,v_user_id
2691 ,SYSDATE
2692 ,v_user_id
2693 ,v_user_id
2694 ,P_control_id
2695 ,'CTRL'
2696 ,P_AUDIT_PROCEDURE_ID
2697 ,NVL(P_DESIGN_EFFECTIVENESS,'N')
2698 ,NVL(P_OP_EFFECTIVENESS,'N')
2699 ,1
2700 ,SYSDATE
2701 );
2702 END IF;
2703 ---end if; ---03.01.2005 npanandi: end of if for l_has_ap_access check
2704 END CREATE_AMW_AP_ASSOC;
2705
2706 PROCEDURE CREATE_AMW_OBJ_ASSOC(
2707 P_PROCESS_OBJECTIVE_ID IN NUMBER
2708 ,P_PK1 IN NUMBER
2709 ---01.13.2005 npanandi: added pk2,pk3,pk4,pk5 for Ctrl to Obj association
2710 ,P_PK2 IN NUMBER
2711 ,P_PK3 IN NUMBER
2712 ,P_PK4 IN NUMBER
2713 ,P_PK5 IN NUMBER
2714 ,P_OBJECT_TYPE IN VARCHAR2
2715 )
2716 IS
2717 l_has_proc_obj_assoc_access varchar2(15) := 'T'; --defaulting to 'T', which means 'has access'
2718 BEGIN
2719 /**
2720 ---03.02.2005 npanandi: check here to see if this Process has AMW_UPD_RL_PROC_OBJ_ASSOC
2721 ---privilege to associate this Objective to Process
2722
2723 ---IMPORTANTE --> check this privilege only when associating this Objective
2724 ---to a Process AND NOT to a Risk!!
2725 if(P_OBJECT_TYPE = 'PROCESS')then
2726 l_has_proc_obj_assoc_access := check_function(
2727 p_function => 'AMW_UPD_RL_PROC_OBJ_ASSOC'
2728 ,p_object_name => 'AMW_PROCESS_APPR_ETTY'
2729 ,p_instance_pk1_value => P_PK1
2730 ,p_user_id => fnd_global.user_id);
2731 end if;
2732
2733 fnd_file.put_line(fnd_file.log,'&&&&&&&&&&&&&&& l_has_proc_obj_assoc_access: '||l_has_proc_obj_assoc_access||' &&&&&&&&&&&&&&&');
2734 --03.02.2005 npanandi: imposing the foll. IF condition to
2735 --create the association only if access privilege is present,
2736 --and moreover, only when associating Process to Objective in the spreadsheet
2737 if(l_has_proc_obj_assoc_access = 'T') then
2738 **/
2739 INSERT INTO amw_objective_associations(
2740 objective_association_id
2741 ,last_update_date
2742 ,last_updated_by
2743 ,creation_date
2744 ,created_by
2745 ,process_objective_id
2746 ,pk1
2747 ---01.13.2005 npanandi: added pk2,pk3,pk4,pk5 for Ctrl to Obj association
2748 ,PK2
2749 ,PK3
2750 ,PK4
2751 ,PK5
2752 ,object_type
2753 ,effective_date_from
2754 ,ASSOCIATION_CREATION_DATE
2755 ,OBJECT_VERSION_NUMBER)
2756 VALUES (
2757 amw_objective_associations_s.NEXTVAL
2758 ,SYSDATE
2759 ,v_user_id
2760 ,SYSDATE
2761 ,v_user_id
2762 ,P_PROCESS_OBJECTIVE_ID
2763 ,P_PK1 --PROCESS_ID OR RISK_ID
2764 ,P_PK2 --NULL OR RISK_ID
2765 ,P_PK3 --NULL OR CONTROL_ID
2766 ,P_PK4
2767 ,P_PK5
2768 ,P_OBJECT_TYPE --'PROCESS' OR 'RISK' OR 'CONTROL'
2769 ,SYSDATE
2770 ,SYSDATE
2771 ,1
2772 );
2773 ---end if; ---end of check for l_has_proc_obj_assoc_access
2774 END CREATE_AMW_OBJ_ASSOC;
2775
2776 PROCEDURE CREATE_AMW_RISK_ASSOC(
2777 P_RISK_ID IN NUMBER
2778 ,P_PROCESS_ID IN NUMBER
2779 ,P_RISK_LIKELIHOOD_CODE IN VARCHAR2
2780 ,P_RISK_IMPACT_CODE IN VARCHAR2
2781 ,P_MATERIAL IN VARCHAR2
2782 ,P_MATERIAL_VALUE IN NUMBER
2783 )
2784 IS
2785 l_has_proc_risk_assoc_access varchar2(15) := 'T'; --defaulting to 'T', which means 'has access'
2786 BEGIN
2787 /**
2788 ---03.02.2005 npanandi: check here to see if this Process has AMW_UPDATE_AP_DETAILS
2789 ---privilege to associate this Control to AP
2790 l_has_proc_risk_assoc_access := check_function(
2791 p_function => 'AMW_UPD_RL_PROC_RISK_ASSOC'
2792 ,p_object_name => 'AMW_PROCESS_APPR_ETTY'
2793 ,p_instance_pk1_value => P_PROCESS_ID
2794 ,p_user_id => fnd_global.user_id);
2795
2796 fnd_file.put_line(fnd_file.log,'&&&&&&&&&&&&&&& l_has_proc_risk_assoc_access: '||l_has_proc_risk_assoc_access||' &&&&&&&&&&&&&&&');
2797 --03.02.2005 npanandi: imposing the foll. IF condition to
2798 --create the association only if access privilege is present
2799 if(l_has_proc_risk_assoc_access = 'T') then
2800 **/
2801 INSERT INTO amw_risk_associations(
2802 risk_association_id
2803 ,last_update_date
2804 ,last_updated_by
2805 ,creation_date
2806 ,created_by
2807 ,risk_id
2808 ,pk1
2809 ,object_type
2810 ,effective_date_from
2811 ,ASSOCIATION_CREATION_DATE
2812 ,OBJECT_VERSION_NUMBER
2813 ,RISK_LIKELIHOOD_CODE
2814 ,RISK_IMPACT_CODE
2815 ,MATERIAL
2816 ,MATERIAL_VALUE)
2817 VALUES (
2818 amw_risk_associations_s.NEXTVAL
2819 ,SYSDATE
2820 ,v_user_id
2821 ,SYSDATE
2822 ,v_user_id
2823 ,P_RISK_ID
2824 ,P_process_id
2825 ,'PROCESS'
2826 ,SYSDATE
2827 ,SYSDATE
2828 ,1
2829 ,P_risk_likelihood_code
2830 ,P_risk_impact_code
2831 ,P_MATERIAL
2832 ,P_MATERIAL_VALUE
2833 );
2834 --end if; ---end of check for l_has_proc_risk_assoc_access
2835 END CREATE_AMW_RISK_ASSOC;
2836
2837 ---
2838 ---03.01.2005 npanandi: add Risk/Ctrl Owner privilege here for data security
2839 ---
2840 procedure add_owner_privilege(
2841 p_role_name in varchar2
2842 ,p_object_name in varchar2
2843 ,p_grantee_type in varchar2
2844 ,p_instance_set_id in number
2845 ,p_instance_pk1_value in varchar2
2846 ,p_instance_pk2_value in varchar2
2847 ,p_instance_pk3_value in varchar2
2848 ,p_instance_pk4_value in varchar2
2849 ,p_instance_pk5_value in varchar2
2850 ,p_user_id in number
2851 ,p_start_date in date
2852 ,p_end_date in date
2853 )
2854 is
2855 cursor c_get_party_id is
2856 select person_party_id
2857 from fnd_user
2858 where user_id=p_user_id;
2859
2860 l_return_status varchar2(10);
2861 l_msg_count number;
2862 l_msg_data varchar2(4000);
2863 l_party_id number;
2864 begin
2865 open c_get_party_id;
2866 fetch c_get_party_id into l_party_id;
2867 close c_get_party_id;
2868
2869 amw_security_pub.grant_role_guid(
2870 p_api_version => 1
2871 ,p_role_name => p_role_name
2872 ,p_object_name => p_object_name
2873 ,p_instance_type => 'INSTANCE'
2874 ,p_instance_set_id => null
2875 ,p_instance_pk1_value => p_instance_pk1_value
2876 ,p_instance_pk2_value => null
2877 ,p_instance_pk3_value => null
2878 ,p_instance_pk4_value => null
2879 ,p_instance_pk5_value => null
2880 ,p_party_id => l_party_id
2881 ,p_start_date => sysdate
2882 ,p_end_date => null
2883 ,x_return_status => l_return_status
2884 ,x_errorcode => l_msg_count
2885 ,x_grant_guid => l_msg_data);
2886 exception
2887 when others then
2888 rollback;
2889 end add_owner_privilege;
2890 ---03.01.2005 npanandi: ends method for grant owner privilege
2891
2892 ---
2893 ---03.01.2005 npanandi: function to check access privilege for this Risk/Ctrl
2894 ---
2895 function check_function(
2896 p_function in varchar2
2897 ,p_object_name in varchar2
2898 ,p_instance_pk1_value in number
2899 ,p_instance_pk2_value in number
2900 ,p_instance_pk3_value in number
2901 ,p_instance_pk4_value in number
2902 ,p_instance_pk5_value in number
2903 ,p_user_id in number
2904 ) return varchar2
2905 is
2906 cursor c_get_user_name is
2907 select user_name from fnd_user where user_id=p_user_id;
2908
2909 l_has_access varchar2(15) := 'T';
2910 l_user_name varchar2(100); ---fnd_user.user_name colLength = 100
2911 l_security_switch VARCHAR2 (2);
2912 begin
2913 open c_get_user_name;
2914 fetch c_get_user_name into l_user_name;
2915 close c_get_user_name;
2916
2917 l_security_switch := NVL(fnd_profile.VALUE ('AMW_DATA_SECURITY_SWITCH'), 'N');
2918
2919 if(l_security_switch = 'Y') then ---check for Upd prvlg only if Security mode is set on
2920 l_has_access := fnd_data_security.check_function(
2921 p_api_version => 1
2922 ,p_function => p_function
2923 ,p_object_name => p_object_name
2924 ,p_instance_pk1_value => p_instance_pk1_value
2925 ,p_user_name => l_user_name);
2926 end if;
2927 return l_has_access;
2928 end;
2929 ---03.01.2005 npanandi: end function to check access privilege
2930
2931
2932 END amw_load_rc_data;