[Home] [Help]
PACKAGE BODY: APPS.AMW_LOAD_CTRL_DATA
Source
1 PACKAGE BODY AMW_LOAD_CTRL_DATA AS
2 /* $Header: amwctrdb.pls 120.0 2005/05/31 18:37:39 appldev 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 v_err_msg VARCHAR2 (2000);
31 v_table_name VARCHAR2 (240);
32
33 ---02.03.2005 npanandi: increased Varchar2 length of v_import_func
34 ---from 30 to 480 per FND mandate
35 v_import_func CONSTANT VARCHAR2(480) := 'AMW_DATA_IMPORT';
36
37 v_control_db_approval_status VARCHAR2(30);
38
39 v_invalid_requestor_msg VARCHAR2(2000);
40 v_no_import_privilege_msg VARCHAR2(2000);
41 v_control_pending_msg VARCHAR2(2000);
42 --
43 -- function to check the user access privilege
44 --
45 FUNCTION Has_Import_Privilege RETURN Boolean
46 IS
47 CURSOR c_func_exists IS
48 SELECT 'Y'
49 FROM fnd_responsibility r, fnd_compiled_menu_functions m, fnd_form_functions f
50 WHERE r.responsibility_id = fnd_global.resp_id
51 AND r.application_id=fnd_global.resp_appl_id
52 AND r.menu_id = m.menu_id
53 AND m.function_id = f.function_id
54 AND f.function_name = v_import_func;
55 CURSOR c_func_excluded IS
56 SELECT 'Y'
57 FROM fnd_resp_functions rf, fnd_form_functions f
58 WHERE rf.application_id = fnd_global.resp_appl_id
59 AND rf.responsibility_id = fnd_global.resp_id
60 AND rf.rule_type = 'F'
61 AND rf.action_id = f.function_id
62 AND f.function_name = v_import_func;
63
64 l_func_exists VARCHAR2(1);
65 l_func_excluded VARCHAR2(1);
66 BEGIN
67 OPEN c_func_exists;
68 FETCH c_func_exists INTO l_func_exists;
69 IF c_func_exists%NOTFOUND THEN
70 CLOSE c_func_exists;
71 return FALSE;
72 END IF;
73 CLOSE c_func_exists;
74
75 OPEN c_func_excluded;
76 FETCH c_func_excluded INTO l_func_excluded;
77 CLOSE c_func_excluded;
78
79 IF l_func_excluded is not null THEN
80 return FALSE;
81 END IF;
82
83 return TRUE;
84 END Has_Import_Privilege;
85
86 FUNCTION Control_Can_Be_Processed RETURN Boolean
87 IS
88 BEGIN
89 IF v_control_db_approval_status = 'P' THEN
90 update_interface_with_error (v_control_pending_msg
91 ,'AMW_CONTROLS'
92 ,v_interface_id);
93 return FALSE;
94 END IF;
95 return TRUE;
96 END Control_Can_Be_Processed;
97
98 /*****************************************************************************/
99 /*****************************************************************************/
100 PROCEDURE create_controls (
101 errbuf OUT NOCOPY VARCHAR2
102 ,retcode OUT NOCOPY VARCHAR2
103 ,p_batch_id IN NUMBER
104 ,p_user_id IN NUMBER
105 )
106 IS
107 /****************************************************/
108 CURSOR controls_cur
109 IS
110 SELECT ctrl_interface_id
111 ,control_approval_status_code
112 ,control_automation_type_code
113 ,control_description
114 ,control_job_id
115 ,control_location_code
116 ,control_name
117 ,control_source
118 ,control_type_code
119 ,physical_evidence
120 ,revise_ctrl_flag
121 ,control_application_id
122 ,preventive_control
123 ,detective_control
124 ,disclosure_control
125 ,key_mitigating
126 ,verification_source
127 ,verification_source_name
128 ,verification_instruction
129 ,control_obj1
130 ,control_obj2
131 ,control_obj3
132 ,control_obj4
133 ,control_obj5
134 ,control_obj6
135 ,control_obj7
136 ,control_obj8
137 ,control_obj9
138 ,control_obj10
139 ,control_obj11
140 ,control_obj12
141 ,control_obj13
142 ,control_obj14
143 ,control_obj15
144 ,control_obj16
145 ,control_obj17
146 ,control_obj18
147 ,control_obj19
148 ,control_obj20
149 ,control_obj21
150 ,control_obj22
151 ,control_obj23
152 ,control_obj24
153 ,control_obj25
154 ,control_obj26
155 ,control_obj27
156 ,control_obj28
157 ,control_obj29
158 ,control_obj30
159 ,control_assert1
160 ,control_assert2
161 ,control_assert3
162 ,control_assert4
163 ,control_assert5
164 ,control_assert6
165 ,control_assert7
166 ,control_assert8
167 ,control_assert9
168 ,control_assert10
169 ,control_assert11
170 ,control_assert12
171 ,control_assert13
172 ,control_assert14
173 ,control_assert15
174 ,control_assert16
175 ,control_assert17
176 ,control_assert18
177 ,control_assert19
178 ,control_assert20
179 ,control_assert21
180 ,control_assert22
181 ,control_assert23
182 ,control_assert24
183 ,control_assert25
184 ,control_assert26
185 ,control_assert27
186 ,control_assert28
187 ,control_assert29
188 ,control_assert30
189 ,control_comp1
190 ,control_comp2
191 ,control_comp3
192 ,control_comp4
193 ,control_comp5
194 ,control_comp6
195 ,control_comp7
196 ,control_comp8
197 ,control_comp9
198 ,control_comp10
199 ,control_comp11
200 ,control_comp12
201 ,control_comp13
202 ,control_comp14
203 ,control_comp15
204 ,control_comp16
205 ,control_comp17
206 ,control_comp18
207 ,control_comp19
208 ,control_comp20
209 ,control_comp21
210 ,control_comp22
211 ,control_comp23
212 ,control_comp24
213 ,control_comp25
214 ,control_comp26
215 ,control_comp27
216 ,control_comp28
217 ,control_comp29
218 ,control_comp30
219 --npanandi 12.08.2004: Control Enhancements upload
220 ,UOM_CODE
221 ,CONTROL_FREQUENCY
222 ,CTRL_PURPOSE1
223 ,CTRL_PURPOSE2
224 ,CTRL_PURPOSE3
225 ,CTRL_PURPOSE4
226 ,CTRL_PURPOSE5
227 ,CTRL_PURPOSE6
228 ,CTRL_PURPOSE7
229 ,CTRL_PURPOSE8
230 ,CTRL_PURPOSE9
231 ,CTRL_PURPOSE10
232 ,CTRL_PURPOSE11
233 ,CTRL_PURPOSE12
234 ,CTRL_PURPOSE13
235 ,CTRL_PURPOSE14
236 ,CTRL_PURPOSE15
237 ,CTRL_PURPOSE16
238 ,CTRL_PURPOSE17
239 ,CTRL_PURPOSE18
240 ,CTRL_PURPOSE19
241 ,CTRL_PURPOSE20
242 ,CTRL_PURPOSE21
243 ,CTRL_PURPOSE22
244 ,CTRL_PURPOSE23
245 ,CTRL_PURPOSE24
246 ,CTRL_PURPOSE25
247 ,CTRL_PURPOSE26
248 ,CTRL_PURPOSE27
249 ,CTRL_PURPOSE28
250 ,CTRL_PURPOSE29
251 ,CTRL_PURPOSE30
252 ---NPANANDI 12.13.2004: ADDED BELOW FOR CTRL CLASSIFICATION ENH.
253 ,CLASSIFICATION
254 FROM amw_ctrl_interface
255 WHERE created_by = DECODE (p_user_id, NULL, created_by, p_user_id)
256 AND batch_id = DECODE (p_batch_id, NULL, batch_id, p_batch_id)
257 AND process_flag IS NULL
258 AND error_flag IS NULL;
259
260 CURSOR c_requestor_id IS
261 SELECT party_id
262 FROM amw_employees_current_v
263 WHERE employee_id = (select employee_id
264 from fnd_user
265 where user_id = p_user_id)
266 AND rownum = 1;
267
268 CURSOR c_control_exists (c_control_name IN VARCHAR2) IS
269 SELECT b.control_id, b.approval_status
270 FROM amw_controls_b b, amw_controls_tl tl
271 WHERE tl.name = c_control_name
272 AND tl.language = USERENV('LANG')
273 AND tl.control_rev_id = b.control_rev_id
274 AND b.latest_revision_flag='Y';
275
276 l_api_version_number CONSTANT NUMBER := 1.0;
277 l_requestor_id NUMBER;
278 l_amw_delt_ctrl_intf VARCHAR2 (2);
279 l_amw_control_name_prefix VARCHAR2 (30);
280 l_control_rec AMW_CONTROL_PVT.control_rec_type;
281 l_control_found BOOLEAN default true;
282 l_control_approval_status_code VARCHAR2(30);
283 l_control_name VARCHAR2(240);
284 l_application_id NUMBER;
285
286 lx_return_status VARCHAR2(30);
287 lx_msg_count NUMBER;
288 lx_msg_data VARCHAR2(2000);
289 lx_control_id NUMBER;
290 lx_mode_affected VARCHAR2(30);
291 l_object_type_count NUMBER;
292 l_process_flag VARCHAR2(1);
293 e_no_import_access EXCEPTION;
294 e_invalid_requestor_id EXCEPTION;
295
296 l_revise_control_flag VARCHAR2(1);
297
298 ---02.28.2005 npanandi:
299 l_new_control boolean default true;
300 l_has_access varchar2(15);
301
302 BEGIN
303 fnd_file.put_line (fnd_file.LOG, 'resp id: '||fnd_global.RESP_ID);
304 fnd_file.put_line (fnd_file.LOG, 'resp appl id: '||fnd_global.RESP_APPL_ID);
305
306 --
307 -- check access privilege
308 --
309
310
311 IF not Has_Import_Privilege THEN
312 RAISE e_no_import_access;
313 END IF;
314
315 --
316 -- get user requestor_id
317 --
318 v_user_id := p_user_id;
319
320 OPEN c_requestor_id;
321 FETCH c_requestor_id INTO l_requestor_id;
322 IF (c_requestor_id%NOTFOUND) THEN
323 CLOSE c_requestor_id;
324 RAISE e_invalid_requestor_id;
325 END IF;
326 CLOSE c_requestor_id;
327
328
329 --
330 -- get profile info for deleting records from interface table
331 --
332 l_amw_delt_ctrl_intf := NVL(fnd_profile.VALUE ('AMW_DELT_CTRL_INTF'), 'N');
333
334 AMW_UTILITY_PVT.Error_Message(p_message_name => 'AMW_PENDING_CHANGE_ERROR',
335 p_token_name => 'OBJ_TYPE',
336 p_token_value => AMW_UTILITY_PVT.GET_LOOKUP_MEANING('AMW_OBJECT_TYPE','CTRL'));
337 v_control_pending_msg := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.G_LAST,p_encoded => fnd_api.g_false);
338
339 --
340 -- loop processing each record
341 --
342 FOR ctrl_rec IN controls_cur LOOP
343 BEGIN
344 v_interface_id := ctrl_rec.ctrl_interface_id;
345 l_control_approval_status_code := ctrl_rec.control_approval_status_code;
346 l_revise_control_flag := upper(NVL(ctrl_rec.revise_ctrl_flag, 'N'));
347
348 fnd_file.put_line (fnd_file.LOG, 'v_interface_id: '||v_interface_id);
349 fnd_file.put_line (fnd_file.LOG, 'l_control_approval_status_code: '||l_control_approval_status_code);
350 fnd_file.put_line (fnd_file.LOG, 'l_revise_control_flag: '||l_revise_control_flag);
351
352 --
353 -- process control
354 --
355 lx_control_id := null;
356 v_control_db_approval_status := null;
357
358 ---02.18.2005 npanandi: fix for bug 4141121
359 l_amw_control_name_prefix := fnd_profile.VALUE ('AMW_CONTROL_NAME_PREFIX');
360 ---02.18.2005 npanandi: ends fix for bug 4141121
361
362 SELECT DECODE (ctrl_rec.control_name,NULL
363 ,l_amw_control_name_prefix||amw_controls_tl_s1.NEXTVAL
364 ,ctrl_rec.control_name)
365 INTO l_control_name
366 FROM dual;
367
368 OPEN c_control_exists(l_control_name);
369 FETCH c_control_exists INTO lx_control_id, v_control_db_approval_status;
370 CLOSE c_control_exists;
371
372 fnd_file.put_line (fnd_file.LOG, 'l_control_name: '||l_control_name);
373 fnd_file.put_line (fnd_file.LOG, 'lx_control_id: '||lx_control_id);
374 fnd_file.put_line (fnd_file.LOG, 'v_control_db_approval_status: '||v_control_db_approval_status);
375
376 ---02.28.2005 npanandi: added data security checks
377 l_has_access := 'T'; ---setting this to 'T' for new Controls
378 if(lx_control_id is not null) then
379 ---Check for Update privilege here
380 l_new_control := false;
381 fnd_file.put_line (fnd_file.LOG, '************** Checking Update Privilege for l_control_name: '||l_control_name);
382 l_has_access := check_function(
383 p_function => 'AMW_CTRL_UPDATE_PRVLG'
384 ,p_object_name => 'AMW_CONTROL'
385 ,p_instance_pk1_value => lx_control_id
386 ,p_user_id => fnd_global.user_id);
387 fnd_file.put_line (fnd_file.LOG, 'l_has_access: '||l_has_access);
388 fnd_file.put_line (fnd_file.LOG, '************** Checked Update Privilege for l_control_name: '||l_control_name);
389
390 IF l_has_access <> 'T' then
391 v_err_msg := 'Cannot update this Ctrl';
392 update_interface_with_error (v_err_msg
393 ,'AMW_CONTROLS'
394 ,v_interface_id);
395 END IF;
396 end if;
397 ---02.28.2005 npanandi: added data security checks ends
398
399 IF Control_Can_Be_Processed AND
400 ---02.28.2005 npanandi: added check for lHasAccess to update this Ctrl
401 ---only if this user has Upd privilege
402 l_has_access = 'T' and
403 (lx_control_id is null OR
404 l_revise_control_flag = 'Y') THEN
405
406 l_control_rec.name := l_control_name;
407 l_control_rec.description := nvl(ctrl_rec.CONTROL_DESCRIPTION, l_control_name);
411 l_control_rec.automation_type := ctrl_rec.CONTROL_AUTOMATION_TYPE_CODE;
408 l_control_rec.approval_status := l_control_approval_status_code;
409 l_control_rec.control_location := ctrl_rec.CONTROL_LOCATION_CODE;
410 l_control_rec.control_type := ctrl_rec.CONTROL_TYPE_CODE;
412 l_control_rec.application_id := ctrl_rec.control_application_id;
413 l_control_rec.source := ctrl_rec.CONTROL_SOURCE;
414 l_control_rec.physical_evidence := ctrl_rec.PHYSICAL_EVIDENCE;
415 l_control_rec.job_id := ctrl_rec.CONTROL_JOB_ID;
416 l_control_rec.preventive_control := Upper(nvl(ctrl_rec.preventive_control,'N'));
417 l_control_rec.detective_control := Upper(nvl(ctrl_rec.detective_control,'N'));
418 l_control_rec.disclosure_control := Upper(nvl(ctrl_rec.disclosure_control,'N'));
419 l_control_rec.key_mitigating := Upper(nvl(ctrl_rec.key_mitigating,'N'));
420 l_control_rec.verification_source := ctrl_rec.verification_source;
421 l_control_rec.verification_source_name := ctrl_rec.verification_source_name;
422 l_control_rec.verification_instruction := ctrl_rec.verification_instruction;
423 l_control_rec.requestor_id := l_requestor_id;
424 --NPANANDI 12.08.2004: ADDITION OF CONTROL ENHANCEMENTS --> CTRL_FREQ, UOM_CODE
425 L_CONTROL_REC.UOM_CODE := CTRL_REC.UOM_CODE;
426 L_CONTROL_REC.CONTROL_FREQUENCY := CTRL_REC.CONTROL_FREQUENCY;
427 ---NPANANDI 12.13.2004: ADDED BELOW FOR CTRL CLASSIFICATION ENH.
428 L_CONTROL_REC.CLASSIFICATION := CTRL_REC.CLASSIFICATION;
429
430 fnd_file.put_line (fnd_file.LOG,'%%%%%%%%%%%%%% AMW_CONTROL_PVT.Load_Control calling below, checking values passed');
431 fnd_file.put_line (fnd_file.LOG,'l_control_rec.name: '||l_control_rec.name);
432 fnd_file.put_line (fnd_file.LOG,'l_control_rec.description: '||l_control_rec.description);
433 fnd_file.put_line (fnd_file.LOG,'l_control_rec.approval_status: '||l_control_rec.approval_status);
434 fnd_file.put_line (fnd_file.LOG,'l_control_rec.control_location: '||l_control_rec.control_location);
435 fnd_file.put_line (fnd_file.LOG,'l_control_rec.control_type: '||l_control_rec.control_type);
436 fnd_file.put_line (fnd_file.LOG,'l_control_rec.automation_type: '||l_control_rec.automation_type);
437 fnd_file.put_line (fnd_file.LOG,'l_control_rec.application_id: '||l_control_rec.application_id);
438 fnd_file.put_line (fnd_file.LOG,'l_control_rec.source: '||l_control_rec.source);
439 fnd_file.put_line (fnd_file.LOG,'l_control_rec.physical_evidence: '||l_control_rec.physical_evidence);
440 fnd_file.put_line (fnd_file.LOG,'l_control_rec.job_id: '||l_control_rec.job_id);
441 fnd_file.put_line (fnd_file.LOG,'l_control_rec.preventive_control: '||l_control_rec.preventive_control);
442 fnd_file.put_line (fnd_file.LOG,'l_control_rec.detective_control: '||l_control_rec.detective_control);
443 fnd_file.put_line (fnd_file.LOG,'l_control_rec.disclosure_control: '||l_control_rec.disclosure_control);
444 fnd_file.put_line (fnd_file.LOG,'l_control_rec.key_mitigating: '||l_control_rec.key_mitigating);
445 fnd_file.put_line (fnd_file.LOG,'l_control_rec.verification_source: '||l_control_rec.verification_source);
446 fnd_file.put_line (fnd_file.LOG,'l_control_rec.verification_source_name: '||l_control_rec.verification_source_name);
447 fnd_file.put_line (fnd_file.LOG,'l_control_rec.verification_instruction: '||l_control_rec.verification_instruction);
448 fnd_file.put_line (fnd_file.LOG,'l_control_rec.UOM_CODE: '||l_control_rec.UOM_CODE);
449 fnd_file.put_line (fnd_file.LOG,'l_control_rec.CONTROL_FREQUENCY: '||l_control_rec.CONTROL_FREQUENCY);
450 fnd_file.put_line (fnd_file.LOG,'l_control_rec.CLASSIFICATION: '||l_control_rec.CLASSIFICATION);
451
452 AMW_CONTROL_PVT.Load_Control(
453 p_api_version_number => l_api_version_number,
454 p_init_msg_list => FND_API.G_TRUE,
455 p_commit => FND_API.G_FALSE,
456 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
457 p_control_rec => l_control_rec,
458 x_return_status => lx_return_status,
459 x_msg_count => lx_msg_count,
460 x_msg_data => lx_msg_data,
461 x_control_rev_id => vx_control_rev_id,
462 x_control_id => lx_control_id,
463 x_mode_affected => lx_mode_affected
464 );
465
466 ---02.28.2005 npanandi: if new Control, grant CtrlOwner prvlg
467 if(l_new_control) then
468 add_owner_privilege(
469 p_role_name => 'AMW_CTRL_OWNER_ROLE'
470 ,p_object_name => 'AMW_CONTROL'
471 ,p_grantee_type => 'P'
472 ,p_instance_pk1_value => lx_control_id
473 ,p_user_id => FND_GLOBAL.USER_ID);
474 end if;
475 ---02.28.2005 npanandi: if new Control, grant CtrlOwner prvlg
476
477 fnd_file.put_line (fnd_file.LOG,'lx_return_status: '||lx_return_status);
478 IF lx_return_status <> FND_API.G_RET_STS_SUCCESS then
479 v_err_msg := ' ';
480 FOR x IN 1..lx_msg_count LOOP
481 if(length(v_err_msg) < 1800) then
482 v_err_msg := v_err_msg||' '
483 ||substr(fnd_msg_pub.get(p_msg_index => x,
484 p_encoded => fnd_api.g_false), 1,100);
485 end if;
486 END LOOP;
487 update_interface_with_error (v_err_msg
488 ,'AMW_CONTROLS'
492 ---12.28.2004 npanandi: execute the below
489 ,v_interface_id);
490 END IF;
491
493 ---procedures only if AmwControlPvt goes through successfully
494 if(lx_return_status = FND_API.G_RET_STS_SUCCESS)then
495 --
496 -- process control components
497 --
498 control_components(ctrl_rec.control_comp1, '1');
499 control_components(ctrl_rec.control_comp2, '2');
500 control_components(ctrl_rec.control_comp3, '3');
501 control_components(ctrl_rec.control_comp4, '4');
502 control_components(ctrl_rec.control_comp5, '5');
503 control_components(ctrl_rec.control_comp6, '6');
504 control_components(ctrl_rec.control_comp7, '7');
505 control_components(ctrl_rec.control_comp8, '8');
506 control_components(ctrl_rec.control_comp9, '9');
507 control_components(ctrl_rec.control_comp10, '10');
508 control_components(ctrl_rec.control_comp11, '11');
509 control_components(ctrl_rec.control_comp12, '12');
510 control_components(ctrl_rec.control_comp13, '13');
511 control_components(ctrl_rec.control_comp14, '14');
512 control_components(ctrl_rec.control_comp15, '15');
513 control_components(ctrl_rec.control_comp16, '16');
514 control_components(ctrl_rec.control_comp17, '17');
515 control_components(ctrl_rec.control_comp18, '18');
516 control_components(ctrl_rec.control_comp19, '19');
517 control_components(ctrl_rec.control_comp20, '20');
518 control_components(ctrl_rec.control_comp21, '21');
519 control_components(ctrl_rec.control_comp22, '22');
520 control_components(ctrl_rec.control_comp23, '23');
521 control_components(ctrl_rec.control_comp24, '24');
522 control_components(ctrl_rec.control_comp25, '25');
523 control_components(ctrl_rec.control_comp26, '26');
524 control_components(ctrl_rec.control_comp27, '27');
525 control_components(ctrl_rec.control_comp28, '28');
526 control_components(ctrl_rec.control_comp29, '29');
527 control_components(ctrl_rec.control_comp30, '30');
528
529 --
530 -- process control components
531 --
532 control_objectives(ctrl_rec.control_obj1, '1');
533 control_objectives(ctrl_rec.control_obj2, '2');
534 control_objectives(ctrl_rec.control_obj3, '3');
535 control_objectives(ctrl_rec.control_obj4, '4');
536 control_objectives(ctrl_rec.control_obj5, '5');
537 control_objectives(ctrl_rec.control_obj6, '6');
538 control_objectives(ctrl_rec.control_obj7, '7');
539 control_objectives(ctrl_rec.control_obj8, '8');
540 control_objectives(ctrl_rec.control_obj9, '9');
541 control_objectives(ctrl_rec.control_obj10, '10');
542 control_objectives(ctrl_rec.control_obj11, '11');
543 control_objectives(ctrl_rec.control_obj12, '12');
544 control_objectives(ctrl_rec.control_obj13, '13');
545 control_objectives(ctrl_rec.control_obj14, '14');
546 control_objectives(ctrl_rec.control_obj15, '15');
547 control_objectives(ctrl_rec.control_obj16, '16');
548 control_objectives(ctrl_rec.control_obj17, '17');
549 control_objectives(ctrl_rec.control_obj18, '18');
550 control_objectives(ctrl_rec.control_obj19, '19');
551 control_objectives(ctrl_rec.control_obj20, '20');
552 control_objectives(ctrl_rec.control_obj21, '21');
553 control_objectives(ctrl_rec.control_obj22, '22');
554 control_objectives(ctrl_rec.control_obj23, '23');
555 control_objectives(ctrl_rec.control_obj24, '24');
556 control_objectives(ctrl_rec.control_obj25, '25');
557 control_objectives(ctrl_rec.control_obj26, '26');
558 control_objectives(ctrl_rec.control_obj27, '27');
559 control_objectives(ctrl_rec.control_obj28, '28');
560 control_objectives(ctrl_rec.control_obj29, '29');
561 control_objectives(ctrl_rec.control_obj30, '30');
562 --
563 -- process control assertions
564 --
565 control_assertions(ctrl_rec.control_assert1, '1');
566 control_assertions(ctrl_rec.control_assert2, '2');
567 control_assertions(ctrl_rec.control_assert3, '3');
568 control_assertions(ctrl_rec.control_assert4, '4');
569 control_assertions(ctrl_rec.control_assert5, '5');
570 control_assertions(ctrl_rec.control_assert6, '6');
571 control_assertions(ctrl_rec.control_assert7, '7');
572 control_assertions(ctrl_rec.control_assert8, '8');
573 control_assertions(ctrl_rec.control_assert9, '9');
574 control_assertions(ctrl_rec.control_assert10, '10');
575 control_assertions(ctrl_rec.control_assert11, '11');
576 control_assertions(ctrl_rec.control_assert12, '12');
577 control_assertions(ctrl_rec.control_assert13, '13');
578 control_assertions(ctrl_rec.control_assert14, '14');
579 control_assertions(ctrl_rec.control_assert15, '15');
580 control_assertions(ctrl_rec.control_assert16, '16');
581 control_assertions(ctrl_rec.control_assert17, '17');
582 control_assertions(ctrl_rec.control_assert18, '18');
583 control_assertions(ctrl_rec.control_assert19, '19');
584 control_assertions(ctrl_rec.control_assert20, '20');
585 control_assertions(ctrl_rec.control_assert21, '21');
586 control_assertions(ctrl_rec.control_assert22, '22');
587 control_assertions(ctrl_rec.control_assert23, '23');
591 control_assertions(ctrl_rec.control_assert27, '27');
588 control_assertions(ctrl_rec.control_assert24, '24');
589 control_assertions(ctrl_rec.control_assert25, '25');
590 control_assertions(ctrl_rec.control_assert26, '26');
592 control_assertions(ctrl_rec.control_assert28, '28');
593 control_assertions(ctrl_rec.control_assert29, '29');
594 control_assertions(ctrl_rec.control_assert30, '30');
595
596 --NPANANDI 12.08,2004: ADDED BELOW TO PROCESS
597 --CONTROL PURPOSES FOR CONTROL ENHANCEMENT
598
599 --
600 -- process control assertions
601 --
602 CONTROL_PURPOSES(ctrl_rec.CTRL_PURPOSE1, '1');
603 CONTROL_PURPOSES(ctrl_rec.CTRL_PURPOSE2, '2');
604 CONTROL_PURPOSES(ctrl_rec.CTRL_PURPOSE3, '3');
605 CONTROL_PURPOSES(ctrl_rec.CTRL_PURPOSE4, '4');
606 CONTROL_PURPOSES(ctrl_rec.CTRL_PURPOSE5, '5');
607 CONTROL_PURPOSES(ctrl_rec.CTRL_PURPOSE6, '6');
608 CONTROL_PURPOSES(ctrl_rec.CTRL_PURPOSE7, '7');
609 CONTROL_PURPOSES(ctrl_rec.CTRL_PURPOSE8, '8');
610 CONTROL_PURPOSES(ctrl_rec.CTRL_PURPOSE9, '9');
611 CONTROL_PURPOSES(ctrl_rec.CTRL_PURPOSE10, '10');
612 CONTROL_PURPOSES(ctrl_rec.CTRL_PURPOSE11, '11');
613 CONTROL_PURPOSES(ctrl_rec.CTRL_PURPOSE12, '12');
614 CONTROL_PURPOSES(ctrl_rec.CTRL_PURPOSE13, '13');
615 CONTROL_PURPOSES(ctrl_rec.CTRL_PURPOSE14, '14');
616 CONTROL_PURPOSES(ctrl_rec.CTRL_PURPOSE15, '15');
617 CONTROL_PURPOSES(ctrl_rec.CTRL_PURPOSE16, '16');
618 CONTROL_PURPOSES(ctrl_rec.CTRL_PURPOSE17, '17');
619 CONTROL_PURPOSES(ctrl_rec.CTRL_PURPOSE18, '18');
620 CONTROL_PURPOSES(ctrl_rec.CTRL_PURPOSE19, '19');
621 CONTROL_PURPOSES(ctrl_rec.CTRL_PURPOSE20, '20');
622 CONTROL_PURPOSES(ctrl_rec.CTRL_PURPOSE21, '21');
623 CONTROL_PURPOSES(ctrl_rec.CTRL_PURPOSE22, '22');
624 CONTROL_PURPOSES(ctrl_rec.CTRL_PURPOSE23, '23');
625 CONTROL_PURPOSES(ctrl_rec.CTRL_PURPOSE24, '24');
626 CONTROL_PURPOSES(ctrl_rec.CTRL_PURPOSE25, '25');
627 CONTROL_PURPOSES(ctrl_rec.CTRL_PURPOSE26, '26');
628 CONTROL_PURPOSES(ctrl_rec.CTRL_PURPOSE27, '27');
629 CONTROL_PURPOSES(ctrl_rec.CTRL_PURPOSE28, '28');
630 CONTROL_PURPOSES(ctrl_rec.CTRL_PURPOSE29, '29');
631 CONTROL_PURPOSES(ctrl_rec.CTRL_PURPOSE30, '30');
632 end if; ---end of if(lx_return_status = FND_API.G_RET_STS_SUCCESS)then
633 END IF; -- end of IF lx_control_id is null OR...
634
635 EXCEPTION
636 WHEN OTHERS THEN
637 v_err_msg := 'interface_id: = '
638 || v_interface_id
639 || ' '
640 || SUBSTR (SQLERRM, 1, 100);
641 v_table_name := 'UNKNOWN';
642 update_interface_with_error (v_err_msg,v_table_name,v_interface_id);
643 fnd_file.put_line (fnd_file.LOG, 'err in interface rec '||v_interface_id
644 ||': '||SUBSTR (v_err_msg, 1, 200));
645 END;
646 END LOOP;
647
648 --
649 -- check profile option for (deletion of interface record, when the value is 'N', otherwise
650 -- set processed flag to 'Y', and update record
651 --
652 IF v_error_found THEN
653 ROLLBACK;
654 l_process_flag := NULL;
655 ELSE
656 l_process_flag := 'Y';
657 END IF;
658
659 IF UPPER (l_amw_delt_ctrl_intf) <> 'Y' THEN
660 BEGIN
661 UPDATE amw_ctrl_interface
662 SET process_flag = l_process_flag
663 ,last_update_date = SYSDATE
664 ,last_updated_by = v_user_id
665 WHERE batch_id = p_batch_id;
666 EXCEPTION
667 WHEN OTHERS THEN
668 fnd_file.put_line (fnd_file.LOG
669 ,'err in update process flag: '||SUBSTR (SQLERRM, 1, 200));
670 END;
671 ELSE
672 IF NOT v_error_found THEN
673 BEGIN
674 DELETE FROM amw_ctrl_interface
675 WHERE batch_id = p_batch_id;
676
677 EXCEPTION
678 WHEN OTHERS THEN
679 fnd_file.put_line (fnd_file.LOG,'err in delete interface records: '||SUBSTR (SQLERRM, 1, 200));
680 END;
681 END IF;
682 END IF;
683 EXCEPTION
684
685 WHEN e_invalid_requestor_id THEN
686 fnd_file.put_line (fnd_file.LOG
687 , 'Invalid requestor id.');
688
689 BEGIN
690 IF v_invalid_requestor_msg is null THEN
691 v_invalid_requestor_msg := FND_MESSAGE.GET_STRING('AMW', 'AMW_UNKNOWN_EMPLOYEE');
692 END IF;
693 UPDATE amw_ctrl_interface
694 SET error_flag = 'Y'
695 ,interface_status = v_invalid_requestor_msg
696 WHERE batch_id = p_batch_id;
697 EXCEPTION
698 WHEN OTHERS THEN
699 fnd_file.put_line (fnd_file.LOG
700 , 'unexpected exception in handling e_invalid_requestor_id: '||sqlerrm);
701 END;
702 WHEN e_no_import_access THEN
703 fnd_file.put_line (fnd_file.LOG,'no import privilege --> p_batch_id: '||p_batch_id);
704 BEGIN
705 IF v_no_import_privilege_msg is null THEN
706 fnd_file.put_line (fnd_file.LOG,'1 v_no_import_privilege_msg: '||v_no_import_privilege_msg);
707 v_no_import_privilege_msg := FND_MESSAGE.GET_STRING('AMW', 'AMW_NO_IMPORT_ACCESS');
711 SET error_flag = 'Y'
708 fnd_file.put_line (fnd_file.LOG,'2 v_no_import_privilege_msg: '||v_no_import_privilege_msg);
709 END IF;
710 UPDATE amw_ctrl_interface
712 ,interface_status = v_no_import_privilege_msg
713 WHERE batch_id = p_batch_id;
714 fnd_file.put_line (fnd_file.LOG,'updated the Intf table');
715 EXCEPTION
716 WHEN OTHERS THEN
717 fnd_file.put_line (fnd_file.LOG
718 , 'unexpected exception in handling e_no_import_access: '||sqlerrm);
719 END;
720 WHEN others THEN
721 rollback;
722 fnd_file.put_line (fnd_file.LOG, 'unexpected exception in create_controls: '||sqlerrm);
723 END create_controls;
724
725 --
726 -- Insert or update the control components
727 --
728 --
729
730 PROCEDURE control_components (p_ctrl_comp_flag IN VARCHAR2, p_lookup_tag IN VARCHAR2)
731 IS
732 CURSOR c_control_comp IS
733 SELECT lookup_code
734 FROM AMW_LOOKUPS
735 WHERE lookup_type='AMW_ASSESSMENT_COMPONENTS'
736 AND enabled_flag='Y'
737 AND tag=p_lookup_tag;
738
739 l_ctrl_comp_code VARCHAR2(30);
740 l_ctrl_comp_flag VARCHAR2(1);
741 l_comp_exists number;
742 e_invalid_comp EXCEPTION;
743 e_invalid_flag EXCEPTION;
744 BEGIN
745 ---fnd_file.put_line(fnd_file.log,'control_objectives --> p_ctrl_obj_flag: '||p_ctrl_obj_flag||'p_lookup_tag: '||p_lookup_tag);
746
747 IF p_ctrl_comp_flag is not null THEN
748 IF UPPER (p_ctrl_comp_flag) = 'Y' THEN
749 l_ctrl_comp_flag := 'Y';
750 ELSIF UPPER (p_ctrl_comp_flag) = 'N' THEN
751 l_ctrl_comp_flag := 'N';
752 ELSE
753 RAISE e_invalid_flag;
754 END IF;
755
756 OPEN c_control_comp;
757 FETCH c_control_comp INTO l_ctrl_comp_code;
758 IF (c_control_comp%NOTFOUND) THEN
759 CLOSE c_control_comp;
760 RAISE e_invalid_comp;
761 END IF;
762 CLOSE c_control_comp;
763
764 if (l_ctrl_comp_flag = 'N') then
765 select count(*)
766 into l_comp_exists
767 from amw_assessment_components
768 where object_type='CONTROL'
769 AND object_id = vx_control_rev_id
770 AND component_code = l_ctrl_comp_code;
771
772 if(l_comp_exists > 0) then
773 delete from amw_assessment_components
774 where object_type='CONTROL'
775 AND object_id = vx_control_rev_id
776 AND component_code = l_ctrl_comp_code;
777 end if;
778 end if;
779
780 if (l_ctrl_comp_flag = 'Y') then
781 --- this control objective has been selected
782 --- need to check if it is already present or not
783 select count(*)
784 into l_comp_exists
785 from amw_assessment_components
786 where object_type='CONTROL'
787 AND object_id = vx_control_rev_id
788 AND component_code = l_ctrl_comp_code;
789
790 if(l_comp_exists = 0) then
791 ---IF SQL%NOTFOUND THEN
792 INSERT INTO amw_assessment_components
793 (assessment_component_id
794 ,last_update_date
795 ,last_updated_by
796 ,creation_date
797 ,created_by
798 ,last_update_login
799 ,component_code
800 ,object_type
801 ,object_id
802 ,OBJECT_VERSION_NUMBER
803 ) VALUES (
804 amw_assessment_components_s.NEXTVAL
805 ,SYSDATE
806 ,v_user_id
807 ,SYSDATE
808 ,v_user_id
809 ,v_user_id
810 ,l_ctrl_comp_code
811 ,'CONTROL'
812 ,vx_control_rev_id
813 ,1
814 );
815 end if; -- end if for l_obj_exists = 0
816 END IF; --end if for l_obj_flag = 'Y'
817 END IF; -- end if for objective not null condition
818
819 EXCEPTION
820 WHEN e_invalid_flag THEN
821 v_err_msg :=
822 'Error working in procedure control components: '
823 || 'component code tag: '
824 || p_lookup_tag
825 || 'using interface id of: '
826 || v_interface_id
827 || ' flag must be Y/N';
828 v_table_name := 'AMW_ASSESSMENT_COMPONENTS';
829 update_interface_with_error (v_err_msg
830 ,v_table_name
831 ,v_interface_id
832 );
833 WHEN e_invalid_comp THEN
834 v_err_msg :=
835 'Error working in procedure control components: '
836 || 'component code tag: '
837 || p_lookup_tag
838 || 'using interface id of: '
839 || v_interface_id
840 || ' code does not exist';
844 ,v_interface_id
841 v_table_name := 'AMW_ASSESSMENT_COMPONENTS';
842 update_interface_with_error (v_err_msg
843 ,v_table_name
845 );
846 WHEN OTHERS THEN
847 v_err_msg :=
848 'Error working in procedure control components: '
849 || 'component code tag: '
850 || p_lookup_tag
851 || 'using interface id of: '
852 || v_interface_id
853 || ' '
854 || SUBSTR (SQLERRM, 1, 100);
855 v_table_name := 'AMW_ASSESSMENT_COMPONENTS';
856 update_interface_with_error (v_err_msg
857 ,v_table_name
858 ,v_interface_id
859 );
860 END control_components;
861
862
863 --
864 -- Insert or update the control objectives
865 --
866 --
867
868 PROCEDURE control_objectives (p_ctrl_obj_flag IN VARCHAR2, p_lookup_tag IN VARCHAR2)
869 IS
870 CURSOR c_control_obj IS
871 SELECT lookup_code
872 FROM AMW_LOOKUPS
873 WHERE lookup_type='AMW_CONTROL_OBJECTIVES'
874 AND enabled_flag='Y'
875 AND tag=p_lookup_tag;
876
877 l_ctrl_obj_code VARCHAR2(30);
878 l_ctrl_obj_flag VARCHAR2(1);
879 l_obj_exists number;
880 e_invalid_obj EXCEPTION;
881 e_invalid_flag EXCEPTION;
882 BEGIN
883 ---fnd_file.put_line(fnd_file.log,'control_objectives --> p_ctrl_obj_flag: '||p_ctrl_obj_flag||'p_lookup_tag: '||p_lookup_tag);
884
885 IF p_ctrl_obj_flag is not null THEN
886 IF UPPER (p_ctrl_obj_flag) = 'Y' THEN
887 l_ctrl_obj_flag := 'Y';
888 ELSIF UPPER (p_ctrl_obj_flag) = 'N' THEN
889 l_ctrl_obj_flag := 'N';
890 ELSE
891 RAISE e_invalid_flag;
892 END IF;
893
894 OPEN c_control_obj;
895 FETCH c_control_obj INTO l_ctrl_obj_code;
896 IF (c_control_obj%NOTFOUND) THEN
897 CLOSE c_control_obj;
898 RAISE e_invalid_obj;
899 END IF;
900 CLOSE c_control_obj;
901
902 if (l_ctrl_obj_flag = 'N') then
903 select count(*)
904 into l_obj_exists
905 from amw_control_objectives
906 where control_rev_id = vx_control_rev_id
907 AND objective_code = l_ctrl_obj_code;
908
909 if(l_obj_exists > 0) then
910 delete from amw_control_objectives
911 where control_rev_id = vx_control_rev_id
912 AND objective_code = l_ctrl_obj_code;
913 end if;
914 end if;
915
916 if (l_ctrl_obj_flag = 'Y') then
917 --- this control objective has been selected
918 --- need to check if it is already present or not
919 select count(*)
920 into l_obj_exists
921 from amw_control_objectives
922 where control_rev_id = vx_control_rev_id
923 AND objective_code = l_ctrl_obj_code;
924
925 if(l_obj_exists = 0) then
926 ---IF SQL%NOTFOUND THEN
927 INSERT INTO amw_control_objectives
928 (control_objective_id
929 ,last_update_date
930 ,last_updated_by
931 ,creation_date
932 ,created_by
933 ,control_rev_id
934 ,objective_code
935 ---effective_date from and to are not used anywa
936 ---,effective_date_from
937 ---,effective_date_to
938 ,OBJECT_VERSION_NUMBER
939 ) VALUES (
940 amw_control_objectives_s.NEXTVAL
941 ,SYSDATE
942 ,v_user_id
943 ,SYSDATE
944 ,v_user_id
945 ,vx_control_rev_id
946 ,l_ctrl_obj_code
947 ---effective_date from and to are not used anywa
948 ---,SYSDATE
949 ---,DECODE (l_ctrl_obj_flag, 'N', SYSDATE, NULL)
950 ,1
951 );
952 end if; -- end if for l_obj_exists = 0
953 END IF; --end if for l_obj_flag = 'Y'
954 END IF; -- end if for objective not null condition
955
956 EXCEPTION
957 WHEN e_invalid_flag THEN
958 v_err_msg :=
959 'Error working in procedure control objectives: '
960 || 'objective code tag: '
961 || p_lookup_tag
962 || 'using interface id of: '
963 || v_interface_id
964 || ' flag must be Y/N';
965 v_table_name := 'AMW_CONTROL_OBJECTIVES';
966 update_interface_with_error (v_err_msg
967 ,v_table_name
968 ,v_interface_id
969 );
970 WHEN e_invalid_obj THEN
971 v_err_msg :=
972 'Error working in procedure control objectives: '
973 || 'objective code tag: '
974 || p_lookup_tag
975 || 'using interface id of: '
976 || v_interface_id
977 || ' code does not exist';
978 v_table_name := 'AMW_CONTROL_OBJECTIVES';
979 update_interface_with_error (v_err_msg
980 ,v_table_name
981 ,v_interface_id
982 );
983 WHEN OTHERS THEN
984 v_err_msg :=
985 'Error working in procedure control objectives: '
986 || 'objective code tag: '
987 || p_lookup_tag
988 || 'using interface id of: '
989 || v_interface_id
990 || ' '
991 || SUBSTR (SQLERRM, 1, 100);
992 v_table_name := 'AMW_CONTROL_OBJECTIVES';
993 update_interface_with_error (v_err_msg
994 ,v_table_name
995 ,v_interface_id
996 );
997 END control_objectives;
998
999
1000 --
1001 -- Insert or update the control assertions
1002 --
1003 PROCEDURE control_assertions (p_ctrl_assert_flag IN VARCHAR2, p_lookup_tag IN VARCHAR2)
1004 IS
1005 CURSOR c_control_assert IS
1006 SELECT lookup_code
1007 FROM AMW_LOOKUPS
1008 WHERE lookup_type='AMW_CONTROL_ASSERTIONS'
1009 AND enabled_flag='Y'
1010 AND tag=p_lookup_tag;
1011
1012 l_ctrl_assert_code VARCHAR2(30);
1013 l_ctrl_assert_flag VARCHAR2(1);
1014 l_assert_exists number;
1015 e_invalid_assert EXCEPTION;
1016 e_invalid_flag EXCEPTION;
1017 BEGIN
1018 -----fnd_file.put_line(fnd_file.log,'control_assertions --> p_ctrl_assert_flag: '||p_ctrl_assert_flag||'p_lookup_tag: '||p_lookup_tag);
1019
1020 IF (p_ctrl_assert_flag is not null) THEN
1021 IF UPPER (p_ctrl_assert_flag) = 'Y' THEN
1022 l_ctrl_assert_flag := 'Y';
1023 ELSIF UPPER (p_ctrl_assert_flag) = 'N' THEN
1024 l_ctrl_assert_flag := 'N';
1025 ELSE
1026 RAISE e_invalid_flag;
1027 END IF;
1028
1029 OPEN c_control_assert;
1030 FETCH c_control_assert INTO l_ctrl_assert_code;
1031 IF (c_control_assert%NOTFOUND) THEN
1032 CLOSE c_control_assert;
1033 RAISE e_invalid_assert;
1034 END IF;
1035 CLOSE c_control_assert;
1036
1040 ,last_update_date = SYSDATE
1037 /*
1038 UPDATE amw_control_assertions
1039 SET effective_date_to = DECODE (l_ctrl_assert_flag, 'N', SYSDATE, NULL)
1041 ,last_updated_by = v_user_id
1042 ,OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER+1
1043 WHERE control_rev_id = vx_control_rev_id
1044 AND ASSERTION_CODE = l_ctrl_assert_code;
1045 */
1046
1047 if (l_ctrl_assert_flag = 'N') then
1048 select count(*)
1049 into l_assert_exists
1050 from amw_control_assertions
1051 where control_rev_id = vx_control_rev_id
1052 AND assertion_code = l_ctrl_assert_code;
1053
1054 if(l_assert_exists > 0) then
1055 delete from amw_control_assertions
1056 where control_rev_id = vx_control_rev_id
1057 AND assertion_code = l_ctrl_assert_code;
1058 end if;
1059 end if;
1060
1061 if(l_ctrl_assert_flag = 'Y') then
1062 select count(*)
1063 into l_assert_exists
1064 from amw_control_assertions
1065 where control_rev_id = vx_control_rev_id
1066 AND assertion_code = l_ctrl_assert_code;
1067
1068 if(l_assert_exists = 0)then
1069 ---IF SQL%NOTFOUND THEN
1070 INSERT INTO amw_control_assertions
1071 (control_assertion_id
1072 ,last_update_date
1073 ,last_updated_by
1074 ,creation_date
1075 ,created_by
1076 ,control_rev_id
1077 ,ASSERTION_CODE
1078 ----not using the date columns anyway
1079 ----,effective_date_from
1080 ----,effective_date_to
1081 ,OBJECT_VERSION_NUMBER
1082 ) VALUES (
1083 amw_control_assertions_s.NEXTVAL
1084 ,SYSDATE
1085 ,v_user_id
1086 ,SYSDATE
1087 ,v_user_id
1088 ,vx_control_rev_id
1089 ,l_ctrl_assert_code
1090 ----not using the date columns anyway
1091 ----,SYSDATE
1092 ----,DECODE (l_ctrl_assert_flag, 'N', SYSDATE, NULL)
1093 ,1
1094 );
1095 end if; --end of if for l_assert_exists
1096 end if; -- end of if for l_assert_flag = 'Y'
1097 END IF; --- end of if p_assert_flag not null
1098 EXCEPTION
1099 WHEN e_invalid_flag THEN
1100 v_err_msg :=
1101 'Error working in procedure control assertions: '
1102 || 'assertion code tag: '
1103 || p_lookup_tag
1104 || 'using interface id of: '
1105 || v_interface_id
1106 || ' flag must be Y/N';
1107 v_table_name := 'AMW_CONTROL_ASSERTIONS';
1108 update_interface_with_error (v_err_msg
1109 ,v_table_name
1110 ,v_interface_id
1111 );
1112 WHEN e_invalid_assert THEN
1113 v_err_msg :=
1114 'Error working in procedure control assertions: '
1115 || 'assertion code tag: '
1116 || p_lookup_tag
1117 || 'using interface id of: '
1118 || v_interface_id
1119 || ' code does not exist';
1120 v_table_name := 'AMW_CONTROL_ASSERTIONS';
1121 update_interface_with_error (v_err_msg
1122 ,v_table_name
1123 ,v_interface_id
1124 );
1125 WHEN OTHERS THEN
1126 v_err_msg :=
1127 'Error working in procedure control assertions: '
1128 || 'assertion code tag: '
1129 || p_lookup_tag
1130 || 'using interface id of: '
1131 || v_interface_id
1132 || ' '
1133 || SUBSTR (SQLERRM, 1, 100);
1134 v_table_name := 'AMW_CONTROL_ASSERTIONS';
1135 update_interface_with_error (v_err_msg
1136 ,v_table_name
1137 ,v_interface_id
1138 );
1139 END control_assertions;
1140
1141 -- NPANANDI 12.08.2004: ADDED BELOW PROCEDURE TO HANDLE CTRL PURPOSES
1142 -- ---> CONTROL ENHANCEMENT
1143 --
1144 -- Insert or update the control PURPOSES
1145 --
1146 PROCEDURE control_PURPOSES (p_ctrl_PURPOSE_flag IN VARCHAR2, p_lookup_tag IN VARCHAR2)
1147 IS
1148 CURSOR c_control_PURPOSE IS
1149 SELECT lookup_code
1150 FROM AMW_LOOKUPS
1151 WHERE lookup_type='AMW_CONTROL_PURPOSES'
1152 AND enabled_flag='Y'
1153 AND tag=p_lookup_tag;
1154
1155 l_ctrl_PURPOSE_code VARCHAR2(30);
1156 l_ctrl_PURPOSE_flag VARCHAR2(1);
1157 l_PURPOSE_exists number;
1158 e_invalid_PURPOSE EXCEPTION;
1159 e_invalid_flag EXCEPTION;
1160 BEGIN
1161 IF (p_ctrl_PURPOSE_flag is not null) THEN
1162 IF UPPER (p_ctrl_PURPOSE_flag) = 'Y' THEN
1163 l_ctrl_PURPOSE_flag := 'Y';
1164 ELSIF UPPER (p_ctrl_PURPOSE_flag) = 'N' THEN
1165 l_ctrl_PURPOSE_flag := 'N';
1166 ELSE
1167 RAISE e_invalid_flag;
1168 END IF;
1169
1170 OPEN c_control_PURPOSE;
1171 FETCH c_control_PURPOSE INTO l_ctrl_PURPOSE_code;
1172 IF (c_control_PURPOSE%NOTFOUND) THEN
1173 CLOSE c_control_PURPOSE;
1174 RAISE e_invalid_PURPOSE;
1175 END IF;
1176 CLOSE c_control_PURPOSE;
1177
1178 if (l_ctrl_PURPOSE_flag = 'N') then
1182 where control_rev_id = vx_control_rev_id
1179 select count(*)
1180 into l_PURPOSE_exists
1181 from amw_control_PURPOSES
1183 AND PURPOSE_code = l_ctrl_PURPOSE_code;
1184
1185 if(l_PURPOSE_exists > 0) then
1186 delete from amw_control_PURPOSEs
1187 where control_rev_id = vx_control_rev_id
1188 AND PURPOSE_code = l_ctrl_PURPOSE_code;
1189 end if;
1190 end if;
1191
1192 if(l_ctrl_PURPOSE_flag = 'Y') then
1193 select count(*)
1194 into l_PURPOSE_exists
1195 from amw_control_PURPOSEs
1196 where control_rev_id = vx_control_rev_id
1197 AND PURPOSE_code = l_ctrl_PURPOSE_code;
1198
1199 if(l_PURPOSE_exists = 0)then
1200 ---IF SQL%NOTFOUND THEN
1201 INSERT INTO amw_control_PURPOSES(
1202 control_PURPOSE_id
1203 ,last_update_date
1204 ,last_updated_by
1205 ,creation_date
1206 ,created_by
1207 ,control_rev_id
1208 ,PURPOSE_CODE
1209 ,effective_date_from
1210 ----,effective_date_to
1211 ,OBJECT_VERSION_NUMBER
1212 ) VALUES (
1213 amw_control_PURPOSEs_s.NEXTVAL
1214 ,SYSDATE
1215 ,v_user_id
1216 ,SYSDATE
1217 ,v_user_id
1218 ,vx_control_rev_id
1219 ,l_ctrl_PURPOSE_code
1220 ,SYSDATE
1221 ----,DECODE (l_ctrl_PURPOSE_flag, 'N', SYSDATE, NULL)
1222 ,1
1223 );
1224 end if; --end of if for l_PURPOSE_exists
1225 end if; -- end of if for l_PURPOSE_flag = 'Y'
1226 END IF; --- end of if p_PURPOSE_flag not null
1227 EXCEPTION
1228 WHEN e_invalid_flag THEN
1229 v_err_msg := 'Error working in procedure control PURPOSES: '
1230 || 'PURPOSE code tag: '
1231 || p_lookup_tag
1232 || 'using interface id of: '
1233 || v_interface_id
1234 || ' flag must be Y/N';
1235 v_table_name := 'AMW_CONTROL_PURPOSES';
1236 update_interface_with_error (v_err_msg
1237 ,v_table_name
1238 ,v_interface_id);
1239
1240 WHEN e_invalid_PURPOSE THEN
1241 v_err_msg := 'Error working in procedure control PURPOSES: '
1242 || 'PURPOSE code tag: '
1243 || p_lookup_tag
1244 || 'using interface id of: '
1245 || v_interface_id
1246 || ' code does not exist';
1247 v_table_name := 'AMW_CONTROL_PURPOSES';
1248 update_interface_with_error (v_err_msg
1249 ,v_table_name
1250 ,v_interface_id);
1251
1252 WHEN OTHERS THEN
1253 v_err_msg := 'Error working in procedure control PURPOSES: '
1254 || 'PURPOSE code tag: '
1255 || p_lookup_tag
1256 || 'using interface id of: '
1257 || v_interface_id
1258 || ' '
1259 || SUBSTR (SQLERRM, 1, 100);
1260 v_table_name := 'AMW_CONTROL_PURPOSES';
1261 update_interface_with_error (v_err_msg
1262 ,v_table_name
1263 ,v_interface_id);
1264 END control_PURPOSEs;
1265 --NPANANDI 12.08.2004 ENHANCEMENT ENDS
1266
1267 ---
1268 ---02.28.2005 npanandi: add Control Owner privilege here for data security
1269 ---
1270 procedure add_owner_privilege(
1271 p_role_name in varchar2
1272 ,p_object_name in varchar2
1273 ,p_grantee_type in varchar2
1274 ,p_instance_set_id in number
1275 ,p_instance_pk1_value in varchar2
1276 ,p_instance_pk2_value in varchar2
1277 ,p_instance_pk3_value in varchar2
1278 ,p_instance_pk4_value in varchar2
1279 ,p_instance_pk5_value in varchar2
1280 ,p_user_id in number
1281 ,p_start_date in date
1282 ,p_end_date in date
1283 )
1284 is
1285 cursor c_get_party_id is
1286 select person_party_id
1287 from fnd_user
1288 where user_id=p_user_id;
1289
1290 l_return_status varchar2(10);
1291 l_msg_count number;
1292 l_msg_data varchar2(4000);
1293 l_party_id number;
1294 begin
1295 open c_get_party_id;
1296 fetch c_get_party_id into l_party_id;
1297 close c_get_party_id;
1298
1299 amw_security_pub.grant_role_guid(
1300 p_api_version => 1
1301 ,p_role_name => p_role_name
1302 ,p_object_name => p_object_name
1303 ,p_instance_type => 'INSTANCE'
1304 ,p_instance_set_id => null
1305 ,p_instance_pk1_value => p_instance_pk1_value
1306 ,p_instance_pk2_value => null
1307 ,p_instance_pk3_value => null
1308 ,p_instance_pk4_value => null
1309 ,p_instance_pk5_value => null
1310 ,p_party_id => l_party_id
1311 ,p_start_date => sysdate
1312 ,p_end_date => null
1313 ,x_return_status => l_return_status
1314 ,x_errorcode => l_msg_count
1315 ,x_grant_guid => l_msg_data);
1316 exception
1317 when others then
1318 rollback;
1319 end add_owner_privilege;
1320 ---02.28.2005 npanandi: ends method for grant owner privilege
1321
1322 ---
1323 ---02.28.2005 npanandi: function to check access privilege for this Control
1324 ---
1325 function check_function(
1326 p_function in varchar2
1327 ,p_object_name in varchar2
1328 ,p_instance_pk1_value in number
1329 ,p_instance_pk2_value in number
1330 ,p_instance_pk3_value in number
1331 ,p_instance_pk4_value in number
1335 is
1332 ,p_instance_pk5_value in number
1333 ,p_user_id in number
1334 ) return varchar2
1336 cursor c_get_user_name is
1337 select user_name from fnd_user where user_id=p_user_id;
1338
1339 l_has_access varchar2(15) := 'T';
1340 l_user_name varchar2(100); ---fnd_user.user_name colLength = 100
1341 l_security_switch VARCHAR2 (2);
1342 begin
1343 open c_get_user_name;
1344 fetch c_get_user_name into l_user_name;
1345 close c_get_user_name;
1346
1347 l_security_switch := NVL(fnd_profile.VALUE ('AMW_DATA_SECURITY_SWITCH'), 'N');
1348
1349 if(l_security_switch = 'Y') then ---check for Upd prvlg only if Security mode is set on
1350 l_has_access := fnd_data_security.check_function(
1351 p_api_version => 1
1352 ,p_function => p_function
1353 ,p_object_name => p_object_name
1354 ,p_instance_pk1_value => p_instance_pk1_value
1355 ,p_user_name => l_user_name);
1356 end if;
1357
1358 return l_has_access;
1359 end;
1360 ---02.28.2005 npanandi: end function to check access privilege
1361
1362 --
1363 -- procedure update_interface_with_error
1364 --
1365 --
1366 PROCEDURE update_interface_with_error (
1367 p_err_msg IN VARCHAR2
1368 ,p_table_name IN VARCHAR2
1369 ,p_interface_id IN NUMBER
1370 )
1371 IS
1372 l_interface_status amw_ctrl_interface.interface_status%TYPE;
1373 BEGIN
1374 ROLLBACK; -- rollback any inserts done during the current loop process
1375 v_error_found := TRUE;
1376
1377 BEGIN
1378 SELECT interface_status
1379 INTO l_interface_status
1380 FROM amw_ctrl_interface
1381 WHERE ctrl_interface_id = p_interface_id;
1382 EXCEPTION
1383 WHEN OTHERS
1384 THEN
1385 v_err_msg :=
1386 'interface_id: = '
1387 || p_interface_id
1388 || ' '
1389 || SUBSTR (SQLERRM, 1, 100);
1390 fnd_file.put_line (fnd_file.LOG, SUBSTR (v_err_msg, 1, 200));
1391 END;
1392
1393 BEGIN
1394 UPDATE amw_ctrl_interface
1395 SET interface_status =
1396 l_interface_status
1397 -- || 'Error Msg: '
1398 || p_err_msg
1399 -- || ' Table Name: '
1400 -- || p_table_name
1401 || '**'
1402 ,error_flag = 'Y'
1403 WHERE ctrl_interface_id = p_interface_id;
1404
1405 fnd_file.put_line (fnd_file.LOG, SUBSTR (l_interface_status, 1, 200));
1406 COMMIT;
1407 EXCEPTION
1408 WHEN OTHERS
1409 THEN
1410 v_err_msg :=
1411 'Error during package processing '
1412 || ' interface_id: = '
1413 || p_interface_id
1414 || SUBSTR (SQLERRM, 1, 100);
1415 fnd_file.put_line (fnd_file.LOG, SUBSTR (v_err_msg, 1, 200));
1416 END;
1417
1418 COMMIT;
1419 END update_interface_with_error;
1420
1421 END amw_load_ctrl_data;