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