[Home] [Help]
PACKAGE BODY: APPS.AMW_LOAD_AP_DATA
Source
1 PACKAGE BODY AMW_LOAD_AP_DATA AS
2 /* $Header: amwapldb.pls 120.2.12000000.2 2007/03/15 06:09:07 srbalasu ship $ */
3 /*****************************************************************************/
4 /*****************************************************************************/
5 /*****************************************************************************/
6 /* Major Functionality of the followIng procedure includes: */
7 /* Reads the amw_risk-ctrl_interface table */
8 /* following tables: */
9 /* INSERTS OR UPDATES ARE DONE AGAINIST THE FOLLOWING TABLES */
10 /* Insert into AMW_RISKS_B and AMW_RISKS_TL */
11 /* Insert into AMW_CONTROLS_B and AMW_CONTROLS_TL */
12 /* Insert into AMW_CONTROL_ASSOCIATIONS */
13 /* Insert into AMW_RISK_ASSOCIATIONS */
14 /* Insert into AMW_CONTROL_OBJECTIVES */
15 /* Insert into AMW_CONTROL_ASSERTIONS */
16 /* Updates amw_ap_interface, with error messages */
17 /* Deleting successful production inserts, based on profile */
18 /* */
19 /*****************************************************************************/
20 --
21 -- Used for exception processing
22 --
23 -- npanandi 11/08/2004 Fixed bug# 3824295 on the mainline
24
25 type t_AP_name IS table of amw_AP_INTERFACE.AP_name%type INDEX BY BINARY_INTEGER;
26 v_AP_name t_AP_name;
27
28 G_USER_ID NUMBER := FND_GLOBAL.USER_ID;
29 G_LOGIN_ID NUMBER := FND_GLOBAL.CONC_LOGIN_ID;
30 v_error_found BOOLEAN DEFAULT FALSE;
31 v_user_id NUMBER;
32 v_interface_id NUMBER;
33 vx_control_rev_id NUMBER;
34 vx_process_objective_id NUMBER := null;
35 lx_risk_rev_id NUMBER;
36 v_err_msg VARCHAR2 (2000);
37 v_table_name VARCHAR2 (240);
38 v_count NUMBER;
39 ---02.03.2005 npanandi: increased Varchar2 length of v_import_func
40 ---from 30 to 480 per FND mandate
41 v_import_func CONSTANT VARCHAR2(480) := 'AMW_DATA_IMPORT';
42
43 v_ap_db_approval_status VARCHAR2(30);
44
45 v_invalid_requestor_msg VARCHAR2(2000);
46 v_no_import_privilege_msg VARCHAR2(2000);
47 v_invalid_risk_type VARCHAR2(2000);
48
49 v_ap_pending_msg VARCHAR2(2000);
50
51 v_valid_risk_type number := 0;
52
53 --
54 -- function to check the user access privilege
55 --
56 FUNCTION Has_Import_Privilege RETURN Boolean
57 IS
58 CURSOR c_func_exists IS
59 SELECT 'Y'
60 FROM fnd_responsibility r, fnd_compiled_menu_functions m, fnd_form_functions f
61 WHERE r.responsibility_id = fnd_global.resp_id
62 AND r.application_id=fnd_global.resp_appl_id
63 AND r.menu_id = m.menu_id
64 AND m.function_id = f.function_id
65 AND f.function_name = v_import_func;
66 CURSOR c_func_excluded IS
67 SELECT 'Y'
68 FROM fnd_resp_functions rf, fnd_form_functions f
69 WHERE rf.application_id = fnd_global.resp_appl_id
70 AND rf.responsibility_id = fnd_global.resp_id
71 AND rf.rule_type = 'F'
72 AND rf.action_id = f.function_id
73 AND f.function_name = v_import_func;
74
75 l_func_exists VARCHAR2(1);
76 l_func_excluded VARCHAR2(1);
77 BEGIN
78 OPEN c_func_exists;
79 FETCH c_func_exists INTO l_func_exists;
80 IF c_func_exists%NOTFOUND THEN
81 CLOSE c_func_exists;
82 return FALSE;
83 END IF;
84 CLOSE c_func_exists;
85
86 OPEN c_func_excluded;
87 FETCH c_func_excluded INTO l_func_excluded;
88 CLOSE c_func_excluded;
89
90 IF l_func_excluded is not null THEN
91 return FALSE;
92 END IF;
93
94 return TRUE;
95 END Has_Import_Privilege;
96
97
98 FUNCTION AP_Can_Be_Processed RETURN Boolean
99 IS
100 BEGIN
101 IF v_ap_db_approval_status = 'P' THEN
102 update_interface_with_error (v_ap_pending_msg
103 ,'AMW_CONTROLS'
104 ,v_interface_id);
105 return FALSE;
106 END IF;
107 return TRUE;
108 END ap_Can_Be_Processed;
109
110 /*****************************************************************************/
111 /*****************************************************************************/
112 PROCEDURE create_audit_procedures (
113 errbuf OUT NOCOPY VARCHAR2
114 ,retcode OUT NOCOPY VARCHAR2
115 ,p_batch_id IN NUMBER
116 ,p_user_id IN NUMBER
117 )
118 IS
119 /****************************************************/
120 CURSOR C_GET_INV_AP IS
121 SELECT AP_NAME
122 ,NVL(AP_APPROVAL_STATUS_CODE,'D') AS AP_APPROVAL_STATUS_CODE
123 ,AP_INTERFACE_ID
124 FROM AMW_AP_INTERFACE
125 WHERE created_by = DECODE (p_user_id, NULL, created_by, p_user_id)
126 AND batch_id = DECODE (p_batch_id, NULL, batch_id, p_batch_id)
127 AND process_flag IS NULL
128 AND error_flag IS NULL
129 ORDER BY AP_INTERFACE_ID ASC;
130
131
132 CURSOR audit_procedures_cur
133 IS
134 SELECT ap_name,
135 ap_description,
136 ap_approval_status_code,
137 ap_end_date,
138 revise_ap_flag,
139 control_name,
140 ap_step_name,
141 ap_step_description,
142 ap_step_samplesize,
143 ---01.14.2005 npanandi: ApStepSeqNum column now supports
144 ---alphanumeric post AMW.D
145 ---so, created ApStepNum2 (Varchar2) column in AmwApInterface tbl
146 ---and quering the new column while retaining the previous alias
147 /** ap_step_seqnum, **/
148 ap_step_seqnum2 as ap_step_seqnum,
149 ap_interface_id,
150 upper(nvl(design_effectiveness,'N')) as design_effectiveness,
151 upper(nvl(op_effectiveness,'N')) as op_effectiveness
152 --npanandi 12.13.2004: added following for AP classification
153 ,CLASSIFICATION
154 FROM amw_ap_interface
155 WHERE created_by = DECODE (p_user_id, NULL, created_by, p_user_id)
156 AND batch_id = DECODE (p_batch_id, NULL, batch_id, p_batch_id)
157 AND process_flag IS NULL
158 AND error_flag IS NULL
159 ORDER BY AP_INTERFACE_ID ASC;
160
161 CURSOR c_requestor_id IS
162 SELECT party_id
163 FROM amw_employees_current_v
164 WHERE employee_id = (select employee_id
165 from fnd_user
166 where user_id = p_user_id)
167 AND rownum = 1;
168
169 CURSOR c_ap_exists (c_ap_name IN VARCHAR2) IS
170 SELECT b.audit_procedure_id, b.approval_status
171 FROM amw_audit_procedures_b b, amw_audit_procedures_tl tl
172 WHERE tl.name = c_ap_name
173 AND tl.language = USERENV('LANG')
174 AND tl.audit_procedure_rev_id = b.audit_procedure_rev_id
175 AND b.latest_revision_flag='Y';
176
177
178 CURSOR c_step_exists (c_step_num IN NUMBER,c_ap_id IN NUMBER) IS
179 SELECT b.ap_step_id,
180 b.name,
181 b.description,
182 b.samplesize,
183 b.from_rev_num,
184 b.to_rev_num
185 FROM amw_ap_steps_vl b
186 WHERE b.seqnum = c_step_num
187 AND b.audit_procedure_id = c_ap_id;
188
189 CURSOR c_get_from_rev_num(c_audit_procedure_rev_id in number) is
190 Select audit_procedure_rev_num
191 From amw_audit_procedures_b
192 Where audit_procedure_rev_id = c_audit_procedure_rev_id;
193
194 lx_step_rec c_step_exists%rowtype;
195
196 l_api_version_number CONSTANT NUMBER := 1.0;
197 l_requestor_id NUMBER;
198 l_amw_delt_ap_intf VARCHAR2 (2);
199 l_amw_ap_name_prefix VARCHAR2 (30);
200 l_ap_rec AMW_AUDIT_PROCEDURES_PVT.audit_procedure_rec_type;
201 l_ap_found BOOLEAN default true;
202 l_step_found BOOLEAN default true;
203 l_process_steps BOOLEAN default false;
204 l_ap_approval_status_code VARCHAR2(30);
205 l_ap_step_name VARCHAR2(240);
206 l_control_id NUMBER;
207
208 lx_return_status VARCHAR2(30);
209 lx_msg_count NUMBER;
210 lx_msg_data VARCHAR2(2000);
211 lx_risk_id NUMBER;
212 lx_audit_procedure_id NUMBER;
213 lx_audit_procedure_rev_id NUMBER;
214 lx_ap_step_id NUMBER;
215 lx_ap_seqnum NUMBER;
216 lx_mode_affected VARCHAR2(30);
217 l_from_rev_num NUMBER;
218 l_object_type_count NUMBER;
219 l_process_flag VARCHAR2(1);
220 e_no_import_access EXCEPTION;
221 e_invalid_requestor_id EXCEPTION;
222 e_invalid_risk_type EXCEPTION;
223 INV_AP_UPL_STATUSES EXCEPTION;
224
225 L_AP_EXISTS BOOLEAN;
226 l_revise_ap_flag VARCHAR2(1);
227 L_APPROVAL_DATE DATE;
228 L_ERR_MSG VARCHAR2(2000);
229 L_COUNT NUMBER;
230 L_AUDIT_PROCEDURE_ID number;
231 L_AUDIT_PROCEDURE_REV_ID number;
232
233 ---03.01.2005 npanandi:
234 l_new_ap boolean default true;
235 l_has_access varchar2(15);
236 BEGIN
237 fnd_file.put_line (fnd_file.LOG, 'resp id: '||fnd_global.RESP_ID);
238 fnd_file.put_line (fnd_file.LOG, 'resp appl id: '||fnd_global.RESP_APPL_ID);
239
240 --
241 -- check access privilege
242 --
243
244 IF not Has_Import_Privilege THEN
245 RAISE e_no_import_access;
246 END IF;
247
248 --
249 -- get user requestor_id
250 --
251 v_user_id := p_user_id;
252
253 OPEN c_requestor_id;
254 FETCH c_requestor_id INTO l_requestor_id;
255 IF (c_requestor_id%NOTFOUND) THEN
256 CLOSE c_requestor_id;
257 RAISE e_invalid_requestor_id;
258 END IF;
259 CLOSE c_requestor_id;
260
261
262 --
263 -- get profile info for deleting records from interface table
264 --
265 l_amw_delt_ap_intf := NVL(fnd_profile.VALUE ('AMW_DELT_AP_INTF'), 'N');
266
267 AMW_UTILITY_PVT.Error_Message(p_message_name => 'AMW_PENDING_CHANGE_ERROR',
268 p_token_name => 'OBJ_TYPE',
269 p_token_value => AMW_UTILITY_PVT.GET_LOOKUP_MEANING('AMW_OBJECT_TYPE','AP'));
270
271 v_ap_pending_msg := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.G_LAST,
272 p_encoded => fnd_api.g_false);
273
274
275
276 --
277 -- loop processing each record
278 --
279
280 ---FIRST CHECK HERE TO SEE IF THERE IS ANY CHANGE
281 ---IN AP STATUS BETWEEN DIFF. ROWS FOR THE SAME AP BEING UPLOADED
282 FOR AP_INV_UPL IN C_GET_INV_AP LOOP
283 v_interface_id := AP_INV_UPL.AP_INTERFACE_ID;
284 BEGIN
285 SELECT count(*)
286 INTO v_count
287 FROM AMW_AP_INTERFACE
288 WHERE BATCH_ID=p_batch_id
289 AND AP_NAME=AP_INV_UPL.AP_NAME
290 AND NVL(AP_APPROVAL_STATUS_CODE,'D') <> AP_INV_UPL.AP_APPROVAL_STATUS_CODE;
291
292 FND_FILE.PUT_LINE(FND_FILE.LOG, 'v_interface_id: '||v_interface_id);
293 IF(v_count > 0) THEN
294 RAISE INV_AP_UPL_STATUSES;
295 END IF;
296 EXCEPTION
297 WHEN NO_DATA_FOUND THEN
298 NULL;
299 END;
300 END LOOP;
301
302 --PROCESS THE AUDIT PROCEDURE HERE
303 for AP_REC IN AUDIT_PROCEDURES_CUR LOOP
304 BEGIN
305 --SET INTERFACE_ID TO NULL FOR THIS LOOP,
306 --TO AVOID ANY ERRORS DUE TO GLOBAL VARIABLES
307 V_INTERFACE_ID := NULL;
308 v_interface_id := ap_rec.ap_INTERFACE_ID;
309 l_ap_approval_status_code := ap_rec.ap_approval_status_code;
310 l_revise_ap_flag := upper(NVL(ap_rec.revise_ap_flag, 'N'));
311
312 l_ap_rec.end_date := ap_rec.ap_end_date;
313 l_ap_rec.approval_status := l_ap_approval_status_code;
314 l_ap_rec.audit_procedure_name := ap_rec.ap_name;
315 l_ap_rec.audit_procedure_description := ap_rec.ap_description;
316 l_ap_rec.requestor_id := l_requestor_id;
317 --NPANANDI 12.13.2004: ADDED BELOW FOR AP CLASSIFICATION
318 L_AP_REC.CLASSIFICATION := AP_REC.CLASSIFICATION;
319
320 IF(l_ap_approval_status_code = 'A')THEN
321 L_APPROVAL_DATE := SYSDATE;
322 END IF;
323
324 L_AP_EXISTS := FALSE;
325 BEGIN
326 SELECT COUNT(1) INTO L_COUNT
327 FROM AMW_AP_INTERFACE
328 WHERE BATCH_ID=P_BATCH_ID
329 AND AP_NAME=ap_rec.ap_name
330 AND AP_INTERFACE_ID < V_INTERFACE_ID;
331
332 IF(L_COUNT > 0)THEN
333 L_AP_EXISTS := TRUE;
334 END IF;
335 EXCEPTION
336 WHEN NO_DATA_FOUND THEN
337 L_AP_EXISTS := FALSE;
338 END;
339
340 IF(NOT L_AP_EXISTS) THEN
341 V_AP_NAME(v_interface_id) := ap_rec.ap_name;
342
343 ---03.01.2005 npanandi: added data security checks
344 l_has_access := 'T'; ---setting this to 'T' for new Controls
345 lx_audit_procedure_id := null; ---setting this to NULL to avoid conflict with value from previous loop
346 begin
347 SELECT b.audit_procedure_id
348 into lx_audit_procedure_id
349 FROM amw_audit_procedures_b b, amw_audit_procedures_tl tl
350 WHERE tl.name = ap_rec.ap_name
351 AND tl.language = USERENV('LANG')
352 AND tl.audit_procedure_rev_id = b.audit_procedure_rev_id
353 AND b.latest_revision_flag='Y';
354 exception
355 when no_data_found then
356 l_has_access := 'T';
357 when others then
358 l_has_access := 'T';
359 end;
360
361 ----03.01.2005 npanandi: setting this to TRUE for this loop
362 ----to avoid confusion from previous loop value
363 l_new_ap := true;
364 if(lx_audit_procedure_id is not null) then
365 ---Check for Update privilege here
366 l_new_ap := false;
367 l_has_access := check_function(
368 p_function => 'AMW_UPDATE_AP_DETAILS'
369 ,p_object_name => 'AMW_AUDIT_PROCEDURE'
370 ,p_instance_pk1_value => lx_audit_procedure_id
371 ,p_user_id => fnd_global.user_id);
372
373 IF l_has_access <> 'T' then
374 v_err_msg := 'Cannot update this Audit Procedure';
375 update_interface_with_error (v_err_msg
376 ,'AMW_AUDIT_PROCEDURE'
377 ,v_interface_id);
378 END IF;
379 end if;
380 ---03.01.2005 npanandi: added data security checks ends
381
382 ---03.01.2005 npanandi: call for LoadAP reqd. only if this has l_has_access='T'
383 if(l_has_access = 'T') then
384 AMW_AUDIT_PROCEDURES_PVT.Load_Ap(
385 p_api_version_number => l_api_version_number,
386 p_init_msg_list => FND_API.G_TRUE,
387 p_commit => FND_API.G_FALSE,
388 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
389 x_return_status => lx_return_status,
390 x_msg_count => lx_msg_count,
391 x_msg_data => lx_msg_data,
392 p_audit_procedure_rec => l_ap_rec,
393 x_audit_procedure_rev_id => lx_audit_procedure_rev_id,
394 x_audit_procedure_id => lx_audit_procedure_id,
395 P_APPROVAL_DATE => L_APPROVAL_DATE);
396
397 ---03.01.2005 npanandi: if new Audit Procedure, grant APOwner prvlg
398 if(l_new_ap) then
399 add_owner_privilege(
400 p_role_name => 'AMW_AP_OWNER_ROLE'
401 ,p_object_name => 'AMW_AUDIT_PROCEDURE'
402 ,p_grantee_type => 'P'
403 ,p_instance_pk1_value => lx_audit_procedure_id
404 ,p_user_id => FND_GLOBAL.USER_ID);
405 end if;
406 ---03.01.2005 npanandi: if new Audit Procedure, grant APOwner prvlg
407
408 IF lx_return_status <> FND_API.G_RET_STS_SUCCESS then
409 v_err_msg := ' ';
410 FOR x IN 1..lx_msg_count LOOP
411 if(length(v_err_msg) < 1800) then
412 v_err_msg := v_err_msg||' '||substr(
413 fnd_msg_pub.get(p_msg_index => x,
414 p_encoded => fnd_api.g_false), 1,100);
415 end if;
416 END LOOP;
417 update_interface_with_error (v_err_msg
418 ,'AMW_AUDIT_PROCEDURES'
419 ,v_interface_id);
420 END IF;
421 end if; ---03.01.2005 npanandi: end of if l_has_access = 'T' or not Check
422 END IF; --end of if for ap_exists in this upload or not
423 EXCEPTION
424 WHEN OTHERS THEN
425 V_ERR_MSG := NULL;
426 v_err_msg := 'interface_id: = '|| V_interface_id|| ' '
427 || SUBSTR (SQLERRM, 1, 100);
428 update_interface_with_error (v_err_msg
429 ,'AMW_AUDIT_PROCEDURES'
430 ,v_interface_id);
431 END; --end of begin for this loop
432
433 --process ap_steps for this loop
434 --GET THE AP_ID
435 BEGIN
436 SELECT AUDIT_PROCEDURE_ID,AUDIT_PROCEDURE_REV_ID
437 INTO L_AUDIT_PROCEDURE_ID,L_AUDIT_PROCEDURE_REV_ID
438 FROM AMW_AUDIT_PROCEDURES_VL
439 WHERE NAME=AP_REC.AP_NAME
440 AND LATEST_REVISION_FLAG='Y';
441
442 ---03.01.2005 npanandi: call for LoadAP reqd. only if this has l_has_access='T'
443 if(l_has_access = 'T') then
444 AMW_AUDIT_PROCEDURES_PVT.INSERT_AP_STEP(
445 P_API_VERSION_NUMBER => L_API_VERSION_NUMBER,
446 P_INIT_MSG_LIST => FND_API.G_TRUE,
447 P_COMMIT => FND_API.G_FALSE,
448 P_VALIDATION_LEVEL => FND_API.G_VALID_LEVEL_FULL,
449 P_SAMPLESIZE => AP_REC.ap_step_samplesize,
450 P_AUDIT_PROCEDURE_ID => L_AUDIT_PROCEDURE_ID,
451 P_SEQNUM => AP_REC.AP_STEP_SEQNUM,
452 P_REQUESTOR_ID => L_REQUESTOR_ID,
453 P_NAME => AP_REC.AP_STEP_NAME,
454 P_DESCRIPTION => AP_REC.AP_STEP_DESCRIPTION,
455 P_AUDIT_PROCEDURE_REV_ID => L_AUDIT_PROCEDURE_REV_ID,
456 P_USER_ID => G_USER_ID,
457 X_RETURN_STATUS => LX_RETURN_STATUS,
458 X_MSG_COUNT => LX_MSG_COUNT,
459 X_MSG_DATA => LX_MSG_DATA);
460
461 IF lx_return_status <> FND_API.G_RET_STS_SUCCESS then
462 v_err_msg := ' ';
463 FOR x IN 1..lx_msg_count LOOP
464 if(length(v_err_msg) < 1800) then
465 v_err_msg := v_err_msg||' '||substr(
466 fnd_msg_pub.get(p_msg_index => x,
467 p_encoded => fnd_api.g_false), 1,100);
468 end if;
469 END LOOP;
470 update_interface_with_error (v_err_msg
471 ,'AMW_AUDIT_PROCEDURES'
472 ,v_interface_id);
473 END IF;
474 end if; ---03.01.2005 npanandi: end of l_has_access = 'T' check for ApStep insertions
475 EXCEPTION
476 WHEN NO_DATA_FOUND THEN
477 V_ERR_MSG := 'interface_id: = '|| V_interface_id|| ',no data found for this AP_Step';
478 update_interface_with_error (v_err_msg
479 ,'AMW_AUDIT_PROCEDURES'
480 ,v_interface_id);
481 END;
482
483 --PROCESS CONTROL ASSOCIATIONS
484 IF(AP_REC.CONTROL_NAME IS NOT NULL) THEN
485 BEGIN
486 IF(AP_REC.DESIGN_EFFECTIVENESS='N' AND AP_REC.OP_EFFECTIVENESS='N')THEN
487 --THROW ERROR
488 V_ERR_MSG := FND_MESSAGE.GET_STRING('AMW','AMW_ASSOC_AP_EFF_WEBADI_MSG');
489 update_interface_with_error (v_err_msg
490 ,'AMW_AUDIT_PROCEDURES'
491 ,v_interface_id);
492 ELSE
493 fnd_file.put_line(fnd_file.LOG,'FOR THIS LOOP');
494 fnd_file.put_line(fnd_file.LOG,'L_AUDIT_PROCEDURE_ID: '||L_AUDIT_PROCEDURE_ID||', L_AUDIT_PROCEDURE_REV_ID: '||L_AUDIT_PROCEDURE_REV_ID);
495
496 ---03.01.2005 npanandi: call for LoadAP reqd. only if this has l_has_access='T'
497 if(l_has_access = 'T') then
498 AMW_AUDIT_PROCEDURES_PVT.INSERT_AP_CONTROL_ASSOC(
499 P_API_VERSION_NUMBER => L_API_VERSION_NUMBER,
500 P_INIT_MSG_LIST => FND_API.G_TRUE,
501 P_COMMIT => FND_API.G_FALSE,
502 P_VALIDATION_LEVEL => FND_API.G_VALID_LEVEL_FULL,
503 P_CONTROL_ID => AP_REC.CONTROL_NAME,
504 P_AUDIT_PROCEDURE_ID => L_AUDIT_PROCEDURE_ID,
505 P_DES_EFF => AP_REC.DESIGN_EFFECTIVENESS,
506 P_OP_EFF => AP_REC.OP_EFFECTIVENESS,
507 P_APPROVAL_DATE => L_APPROVAL_DATE,
508 P_USER_ID => G_USER_ID,
509 X_RETURN_STATUS => LX_RETURN_STATUS,
510 X_MSG_COUNT => LX_MSG_COUNT,
511 X_MSG_DATA => LX_MSG_DATA);
512 end if; ---03.01.2005 npanandi: end of l_has_access = 'T' check for Ctrl Assoc
513 END IF; --END OF CHECK FOR EFFECTIVENESS VALIDATION
514 EXCEPTION
515 WHEN NO_DATA_FOUND THEN
516 NULL;
517 END; --END OF BEGIN BLOCK FOR PROCESSING CTRL ASSOCIATION
518 END IF; --END OF IF CONTROL ID EXISTS
519 END LOOP;
520
521 --
522 -- check profile option for (deletion of interface record, when the value is 'N', otherwise
523 -- set processed flag to 'Y', and update record
524 --
525
526 IF v_error_found THEN
527 ROLLBACK;
528 l_process_flag := NULL;
529 ELSE
530 l_process_flag := 'Y';
531 END IF;
532
533 IF UPPER (l_amw_delt_ap_intf) <> 'Y' THEN
534 BEGIN
535 UPDATE amw_ap_interface
536 SET process_flag = l_process_flag
537 ,last_update_date = SYSDATE
538 ,last_updated_by = v_user_id
539 WHERE batch_id = p_batch_id;
540 EXCEPTION
541 WHEN OTHERS THEN
542 fnd_file.put_line (fnd_file.LOG,'err in update process flag: '||SUBSTR (SQLERRM, 1, 200));
543 END;
544 ELSE
545 IF NOT v_error_found THEN
546 BEGIN
547 DELETE FROM amw_ap_interface
548 WHERE batch_id = p_batch_id;
549
550 EXCEPTION
551 WHEN OTHERS THEN
552 fnd_file.put_line (fnd_file.LOG,'err in delete interface records: '||SUBSTR (SQLERRM, 1, 200));
553 END;
554 END IF;
555 END IF;
556
557 EXCEPTION
558 ---NPANANDI 11.21.2004 --> CHECK FOR CONSISTENT STATUS PER UPLOAD OF SAME AP
559 WHEN INV_AP_UPL_STATUSES THEN
560 BEGIN
561 FND_FILE.PUT_LINE(FND_FILE.LOG, 'INVALID AP UPLOAD STATUS FOUND' );
562 L_ERR_MSG := 'Multiple Approval Statuses found for this Procedure in this Upload';
563 update_interface_with_error(
564 p_ERR_MSG => L_ERR_MSG
565 ,p_table_name => 'AMW_AUDIT_PROCEDURES_B'
566 ,P_INTERFACE_ID => V_INTERFACE_ID);
567 EXCEPTION
568 WHEN OTHERS THEN
569 fnd_file.put_line (fnd_file.LOG, 'unexpected exception in handling INV_AP_UPL_STATUSES: '||sqlerrm);
570 END;
571 ----Exception1
572 WHEN e_invalid_requestor_id THEN
573 fnd_file.put_line (fnd_file.LOG, 'Invalid requestor id.');
574 BEGIN
575 IF v_invalid_requestor_msg is null THEN
576 v_invalid_requestor_msg := FND_MESSAGE.GET_STRING('AMW', 'AMW_UNKNOWN_EMPLOYEE');
577 END IF;
578 UPDATE amw_ap_interface
579 SET error_flag = 'Y'
580 ,interface_status = v_invalid_requestor_msg
581 WHERE batch_id = p_batch_id;
582 EXCEPTION
583 WHEN OTHERS THEN
584 fnd_file.put_line (fnd_file.LOG, 'unexpected exception in handling e_invalid_requestor_id: '||sqlerrm);
585 END;
586
587 ----Exception2
588 WHEN e_no_import_access THEN
589 fnd_file.put_line (fnd_file.LOG, 'no import privilege');
590 BEGIN
591 IF v_no_import_privilege_msg is null THEN
592 v_no_import_privilege_msg := FND_MESSAGE.GET_STRING('AMW', 'AMW_NO_IMPORT_ACCESS');
593 END IF;
594 fnd_file.put_line(fnd_file.LOG,'v_no_import_privilege_msg: '||v_no_import_privilege_msg);
595 UPDATE amw_ap_interface
596 SET error_flag = 'Y'
597 ,interface_status = v_no_import_privilege_msg
598 WHERE batch_id = p_batch_id;
599 EXCEPTION
600 WHEN OTHERS THEN
601 fnd_file.put_line (fnd_file.LOG, 'unexpected exception in handling e_no_import_access: '||sqlerrm);
602 END;
603
604 ----Exception3
605 WHEN others THEN
606 rollback;
607 fnd_file.put_line (fnd_file.LOG, 'unexpected exception in create_audit_procedures: '||sqlerrm);
608 END create_audit_procedures;
609
610 ---
611 ---03.01.2005 npanandi: add Audit Procedure Owner privilege here for data security
612 ---
613 procedure add_owner_privilege(
614 p_role_name in varchar2
615 ,p_object_name in varchar2
616 ,p_grantee_type in varchar2
617 ,p_instance_set_id in number
618 ,p_instance_pk1_value in varchar2
619 ,p_instance_pk2_value in varchar2
620 ,p_instance_pk3_value in varchar2
621 ,p_instance_pk4_value in varchar2
622 ,p_instance_pk5_value in varchar2
623 ,p_user_id in number
624 ,p_start_date in date
625 ,p_end_date in date
626 )
627 is
628 cursor c_get_party_id is
629 select person_party_id
630 from fnd_user
631 where user_id=p_user_id;
632
633 l_return_status varchar2(10);
634 l_msg_count number;
635 l_msg_data varchar2(4000);
636 l_party_id number;
637 begin
638 open c_get_party_id;
639 fetch c_get_party_id into l_party_id;
640 close c_get_party_id;
641
642 amw_security_pub.grant_role_guid(
643 p_api_version => 1
644 ,p_role_name => p_role_name
645 ,p_object_name => p_object_name
646 ,p_instance_type => 'INSTANCE'
647 ,p_instance_set_id => null
648 ,p_instance_pk1_value => p_instance_pk1_value
649 ,p_instance_pk2_value => null
650 ,p_instance_pk3_value => null
651 ,p_instance_pk4_value => null
652 ,p_instance_pk5_value => null
653 ,p_party_id => l_party_id
654 ,p_start_date => sysdate
655 ,p_end_date => null
656 ,x_return_status => l_return_status
657 ,x_errorcode => l_msg_count
658 ,x_grant_guid => l_msg_data);
659 exception
660 when others then
661 rollback;
662 end add_owner_privilege;
663 ---03.01.2005 npanandi: ends method for grant owner privilege
664
665 ---
666 ---03.01.2005 npanandi: function to check access privilege for this Audit Procedure
667 ---
668 function check_function(
669 p_function in varchar2
670 ,p_object_name in varchar2
671 ,p_instance_pk1_value in number
672 ,p_instance_pk2_value in number
673 ,p_instance_pk3_value in number
674 ,p_instance_pk4_value in number
675 ,p_instance_pk5_value in number
676 ,p_user_id in number
677 ) return varchar2
678 is
679 cursor c_get_user_name is
680 select user_name from fnd_user where user_id=p_user_id;
681
682 l_has_access varchar2(15) := 'T';
683 l_user_name varchar2(100); ---fnd_user.user_name colLength = 100
684 l_security_switch VARCHAR2 (2);
685 begin
686 open c_get_user_name;
687 fetch c_get_user_name into l_user_name;
688 close c_get_user_name;
689
690 l_security_switch := NVL(fnd_profile.VALUE ('AMW_DATA_SECURITY_SWITCH'), 'N');
691
692 if(l_security_switch = 'Y') then ---check for Upd prvlg only if Security mode is set on
693 l_has_access := fnd_data_security.check_function(
694 p_api_version => 1
695 ,p_function => p_function
696 ,p_object_name => p_object_name
697 ,p_instance_pk1_value => p_instance_pk1_value
698 ,p_user_name => l_user_name);
699 end if;
700 fnd_file.put_line (fnd_file.LOG, '&&&&&&&&&&&&&&& inside check_function --> l_security_switch: '||l_security_switch||', l_has_access: '||l_has_access);
701
702 return l_has_access;
703 end;
704 ---03.01.2005 npanandi: end function to check access privilege
705
706 --
707 -- procedure update_interface_with_error
708 --
709 --
710 PROCEDURE update_interface_with_error (
711 p_err_msg IN VARCHAR2
712 ,p_table_name IN VARCHAR2
713 ,p_interface_id IN NUMBER
714 )
715 IS
716 l_interface_status amw_ap_interface.interface_status%TYPE;
717 BEGIN
718 ROLLBACK; -- rollback any inserts done during the current loop process
719 v_error_found := TRUE;
720
721 BEGIN
722 SELECT interface_status
723 INTO l_interface_status
724 FROM amw_ap_interface
725 WHERE ap_interface_id = p_interface_id;
726 EXCEPTION
727 WHEN OTHERS
728 THEN
729 v_err_msg :=
730 'interface_id: = '
731 || p_interface_id
732 || ' '
733 || SUBSTR (SQLERRM, 1, 100);
734 fnd_file.put_line (fnd_file.LOG, SUBSTR (v_err_msg, 1, 200));
735 END;
736
737 BEGIN
738 UPDATE amw_ap_interface
739 SET interface_status =
740 l_interface_status
741 -- || 'Error Msg: '
742 || p_err_msg
743 -- || ' Table Name: '
744 -- || p_table_name
745 || '**'
746 ,error_flag = 'Y'
747 WHERE ap_interface_id = p_interface_id;
748
749 fnd_file.put_line (fnd_file.LOG, SUBSTR (l_interface_status, 1, 200));
750 COMMIT;
751 EXCEPTION
752 WHEN OTHERS
753 THEN
754 v_err_msg :=
755 'Error during package processing '
756 || ' interface_id: = '
757 || p_interface_id
758 || SUBSTR (SQLERRM, 1, 100);
759 fnd_file.put_line (fnd_file.LOG, SUBSTR (v_err_msg, 1, 200));
760 END;
761
762 COMMIT;
763 END update_interface_with_error;
764
765 END amw_load_ap_data;