[Home] [Help]
PACKAGE BODY: APPS.AMW_LOAD_AUDIT_PROCEDURE_DATA
Source
1 PACKAGE BODY AMW_LOAD_AUDIT_PROCEDURE_DATA AS
2 /* $Header: amwaprb.pls 120.6 2006/09/21 16:44:19 srbalasu noship $ */
3
4
5 G_PKG_NAME CONSTANT VARCHAR2(30):= 'AMW_LOAD_AUDIT_PROCEDURE_DATA';
6 G_FILE_NAME CONSTANT VARCHAR2(12) := 'amwaprb.pls';
7
8 G_USER_ID NUMBER := FND_GLOBAL.USER_ID;
9 G_LOGIN_ID NUMBER := FND_GLOBAL.CONC_LOGIN_ID;
10 v_error_msg VARCHAR2(2000);
11 v_err_msg VARCHAR2(2000);
12 v_error_found boolean;
13
14 AMW_DEBUG_HIGH_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);
15 AMW_DEBUG_LOW_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
16 AMW_DEBUG_MEDIUM_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM);
17
18 /* To Update Audit Procedure Results */
19 PROCEDURE update_apr(
20 errbuf OUT NOCOPY VARCHAR2
21 ,retcode OUT NOCOPY VARCHAR2
22 ,p_batch_id IN NUMBER
23 ,p_user_id IN NUMBER
24 )
25 IS
26
27 -- Audit Procedure Id
28 CURSOR c_apid IS
29 SELECT ap_name,audit_project_id,organization_id,task_id from amw_audit_procedure_interface
30 WHERE created_by = DECODE (p_user_id, NULL, created_by, p_user_id)
31 AND batch_id = DECODE (p_batch_id, NULL, batch_id, p_batch_id);
32
33 -- Audit Procedure Rev Id
34 CURSOR c_aprevid IS
35 SELECT audit_procedure_id,audit_project_id,organization_id,task_id,control_id from amw_audit_procedure_interface
36 WHERE created_by = DECODE (p_user_id, NULL, created_by, p_user_id)
37 AND batch_id = DECODE (p_batch_id, NULL, batch_id, p_batch_id);
38
39 --Project Id
40 CURSOR c_projid IS
41 SELECT audit_project_name,audit_project_startdate from amw_audit_procedure_interface
42 WHERE created_by = DECODE (p_user_id, NULL, created_by, p_user_id)
43 AND batch_id = DECODE (p_batch_id, NULL, batch_id, p_batch_id);
44
45 --Ctrl Id
46 CURSOR c_ctrlid IS
47 SELECT control_name,control_description,audit_project_id,task_id,audit_procedure_id,organization_id from amw_audit_procedure_interface
48 WHERE created_by = DECODE (p_user_id, NULL, created_by, p_user_id)
49 AND batch_id = DECODE (p_batch_id, NULL, batch_id, p_batch_id)
50 and control_name is not null;
51
52 --Task Id
53 CURSOR c_taskid IS
54 SELECT task_name,audit_project_id from amw_audit_procedure_interface
55 WHERE created_by = DECODE (p_user_id, NULL, created_by, p_user_id)
56 AND batch_id = DECODE (p_batch_id, NULL, batch_id, p_batch_id);
57
58
59 --Org Id
60 CURSOR c_orgid IS
61 SELECT organization_name,audit_project_id,task_id from amw_audit_procedure_interface
62 WHERE created_by = DECODE (p_user_id, NULL, created_by, p_user_id)
63 AND batch_id = DECODE (p_batch_id, NULL, batch_id, p_batch_id);
64
65
66 --Step Id
67 CURSOR c_stepid IS
68 SELECT step_name,step_number,audit_procedure_id,audit_procedure_rev_id from amw_audit_procedure_interface
69 WHERE created_by = DECODE (p_user_id, NULL, created_by, p_user_id)
70 AND batch_id = DECODE (p_batch_id, NULL, batch_id, p_batch_id)
71 and step_name is not null;
72
73 -- Audit Procedure work description details
74
75 CURSOR c_ap_details IS
76 SELECT ap_status,ap_work_description,ap_executedby_name,
77 ap_executed_on,audit_procedure_id,audit_procedure_rev_id,
78 audit_project_id,task_id,organization_id,ap_interface_id
79 FROM amw_audit_procedure_interface
80 WHERE created_by = DECODE (p_user_id, NULL, created_by, p_user_id)
81 AND batch_id = DECODE (p_batch_id, NULL, batch_id, p_batch_id);
82
83 -- Audit Steps description details
84
85 CURSOR c_step_details IS
86 SELECT step_status,step_work_description,step_executedby_name,
87 step_executed_on,ap_interface_id,audit_procedure_id,
88 audit_procedure_rev_id,audit_project_id,task_id,organization_id,step_id
89 FROM amw_audit_procedure_interface
90 WHERE created_by = DECODE (p_user_id, NULL, created_by, p_user_id)
91 AND batch_id = DECODE (p_batch_id, NULL, batch_id, p_batch_id)
92 and step_id <> 0;
93
94 -- Effectiveness details
95
96 CURSOR c_effec_details IS
97 SELECT design_effectiveness,op_effectiveness,control_opinion,
98 ctrl_overall_summary,ctrl_overall_description,
99 ctrl_design_summary,ctrl_design_description,
100 ctrl_operating_summary,ctrl_operating_description,
101 audit_procedure_id,audit_procedure_rev_id,
102 audit_project_id,task_id,organization_id,ap_interface_id,control_id
103 FROM amw_audit_procedure_interface
104 WHERE created_by = DECODE (p_user_id, NULL, created_by, p_user_id)
105 AND batch_id = DECODE (p_batch_id, NULL, batch_id, p_batch_id)
106 and control_id <> 0;
107
108 -- Attachment details
109
110 CURSOR c_steps_attach IS
111 SELECT step_id, audit_procedure_rev_id, task_id,audit_project_id,organization_id,
112 steps_attachment_url
113 FROM amw_audit_procedure_interface
114 WHERE created_by = DECODE (p_user_id, NULL, created_by, p_user_id)
115 AND batch_id = DECODE (p_batch_id, NULL, batch_id, p_batch_id)
116 and step_id <> 0;
117
118 -- Variables to store the ids
119
120 v_audit_procedure_id number;
121 v_audit_procedure_rev_id number;
122 v_task_id number;
123 v_org_id number;
124 v_ctrl_id number;
125 v_step_id number;
126 v_audit_project_id number;
127 v_ap_cnt number;
128 v_step_cnt number;
129 v_opinions_cnt number;
130 v_party_id number;
131 v_opinion_id number;
132 v_opinions_upd_id number;
133 v_overall number;
134 v_design number;
135 v_operating number;
136 v_opinion_object_id number;
137 v_emp_id number;
138 v_steps_attach_cnt number;
139 lx_return_status VARCHAR2(30);
140 lx_msg_count NUMBER;
141 lx_msg_data VARCHAR2(2000);
142 LX_MEDIA_ID NUMBER;
143 LX_DOCUMENT_ID NUMBER;
144 LX_ATTACHED_DOCUMENT_ID NUMBER;
145 l_seq_num number;
146 l_attachment_rec AMW_LOAD_AUDIT_PROCEDURE_DATA.fnd_attachment_rec_type;
147 x_return_status VARCHAR2(30);
148 x_msg_count NUMBER;
149 x_msg_data VARCHAR2(2000);
150
151 -- For Opinions Log Table
152
153 v_opinion_log_id number;
154 v_opinions_logid number;
155 v_opinions_set_logid number;
156
157 v_design_detail_logid number;
158 v_operating_detail_logid number;
159 v_overall_detail_logid number;
160
161 BEGIN
162
163 -- To handle exceptions
164 BEGIN
165 -- Adding to the log file
166 fnd_file.put_line (fnd_file.LOG, 'resp id: '||fnd_global.RESP_ID);
167 fnd_file.put_line (fnd_file.LOG, 'resp appl id: '||fnd_global.RESP_APPL_ID);
168 fnd_file.put_line (fnd_file.LOG, 'Entered the Procedure');
169
170 -- To fetch the party id
171 select aecv.party_id into v_party_id
172 from AMW_EMPLOYEES_CURRENT_V aecv, FND_USER fu
173 where aecv.EMPLOYEE_ID = fu.EMPLOYEE_ID
174 and fu.user_id = G_USER_ID;
175
176 --To Update the initial values
177 update amw_audit_procedure_interface set control_id = 0,task_id = 0,organization_id = 0,
178 audit_project_id = 0,audit_procedure_id = 0,audit_procedure_rev_id = 0,step_id = 0
179 where created_by = p_user_id and batch_id = p_batch_id;
180 -- To insert Audit Procedure Interface Id
181 update amw_audit_procedure_interface set AP_INTERFACE_ID= amw_audit_procedure_int_s.nextval where AP_INTERFACE_ID = 0
182 and batch_id = p_batch_id;
183 commit;
184
185 -- To fetch Audit Project Id
186 fnd_file.put_line (fnd_file.LOG, 'To fetch audit procedure');
187 FOR projid_rec IN c_projid LOOP
188 v_audit_project_id := null;
189
190 select audit_project_id into v_audit_project_id from amw_audit_projects_v
191 where substr(to_char(start_date),1,11) = substr(to_char(projid_rec.audit_project_startdate),1,11) and project_name = projid_rec.audit_project_name
192 and template_flag = 'N';
193
194 update amw_audit_procedure_interface set audit_project_id = v_audit_project_id
195 where audit_project_name = projid_rec.audit_project_name and batch_id = p_batch_id;
196
197 commit;
198 END LOOP;
199
200 -- To fetch Task Id
201 fnd_file.put_line (fnd_file.LOG, 'To fetch task id');
202 FOR taskid_rec IN c_taskid LOOP
203 v_task_id := null;
204
205 if taskid_rec.task_name = '-1' then
206 v_task_id := -1;
207
208 else
209 select task_id into v_task_id from amw_audit_tasks_v where task_name = taskid_rec.task_name
210 and audit_project_id = taskid_rec.audit_project_id;
211 end if;
212
213 update amw_audit_procedure_interface set task_id = v_task_id
214 where task_name = taskid_rec.task_name
215 and audit_project_id = taskid_rec.audit_project_id and batch_id = p_batch_id;
216
217 commit;
218 END LOOP;
219
220
221 -- To fetch Org Id
222 fnd_file.put_line (fnd_file.LOG, 'To fetch org id');
223 FOR orgid_rec IN c_orgid LOOP
224 v_org_id := null;
225
226 select organization_id into v_org_id from amw_audit_units_v
227 where name = orgid_rec.organization_name and organization_id in (select pk2 from amw_ap_associations
228 where pk1 = orgid_rec.audit_project_id and object_type = 'PROJECT' and pk4 = orgid_rec.task_id);
229
230 update amw_audit_procedure_interface set organization_id = v_org_id
231 where organization_name = orgid_rec.organization_name and batch_id = p_batch_id
232 and audit_project_id = orgid_rec.audit_project_id and task_id = orgid_rec.task_id;
233
234 commit;
235 END LOOP;
236
237 -- To fetch the audit procedure id
238 fnd_file.put_line (fnd_file.LOG, 'To fetch AP ID');
239 FOR apid_rec IN c_apid LOOP
240 v_audit_procedure_id := null;
241
242 select audit_procedure_id into v_audit_procedure_id from amw_audit_procedures_vl where curr_approved_flag = 'Y'
243 and name = apid_rec.ap_name and audit_procedure_id in (select audit_procedure_id from amw_ap_associations
244 where object_type = 'PROJECT' and pk4 = apid_rec.task_id and pk1 = apid_rec.audit_project_id and pk2 = apid_rec.organization_id);
245
246 update amw_audit_procedure_interface set audit_procedure_id = v_audit_procedure_id
247 where ap_name = apid_rec.ap_name and batch_id = p_batch_id
248 and task_id = apid_rec.task_id and audit_project_id = apid_rec.audit_project_id
249 and organization_id = apid_rec.organization_id;
250
251 commit;
252 END LOOP;
253
254 -- To fetch Ctrl Id
255 fnd_file.put_line (fnd_file.LOG, 'To fetch Ctrl Id');
256 FOR ctrlid_rec IN c_ctrlid LOOP
257 v_ctrl_id := null;
258
259 select control_id into v_ctrl_id from amw_controls_b acb, amw_controls_tl act
260 where act.language(+)= userenv('LANG') and act.name = ctrlid_rec.control_name and act.description = ctrlid_rec.control_description
261 and act.control_rev_id = acb.control_rev_id and acb.curr_approved_flag = 'Y' and acb.control_id in (select pk3 from amw_ap_associations
262 where object_type = 'PROJECT' and pk1 = ctrlid_rec.audit_project_id and pk2 = ctrlid_rec.organization_id and
263 pk4 = ctrlid_rec.task_id and audit_procedure_id = ctrlid_rec.audit_procedure_id);
264
265
266 update amw_audit_procedure_interface set control_id = v_ctrl_id
267 where control_name = ctrlid_rec.control_name and control_description = ctrlid_rec.control_description
268 and batch_id = p_batch_id and audit_project_id = ctrlid_rec.audit_project_id
269 and task_id = ctrlid_rec.task_id and organization_id = ctrlid_rec.organization_id;
270
271 commit;
272 END LOOP;
273
274 -- To fetch audit_procedure_rev_id
275 fnd_file.put_line (fnd_file.LOG, 'To fetch rev id');
276 For aprevid_rec IN c_aprevid LOOP
277 v_audit_procedure_rev_id := null;
278 select distinct audit_procedure_rev_id into v_audit_procedure_rev_id from amw_AP_associations
279 where audit_procedure_id = aprevid_rec.audit_procedure_id and pk1 = aprevid_rec.audit_project_id
280 and pk2 = aprevid_rec.organization_id and pk4 = aprevid_rec.task_id
281 and object_type = 'PROJECT';
282
283 update amw_audit_procedure_interface set audit_procedure_rev_id = v_audit_procedure_rev_id
284 where audit_procedure_id = aprevid_rec.audit_procedure_id and batch_id = p_batch_id
285 and task_id = aprevid_rec.task_id and audit_project_id = aprevid_rec.audit_project_id
286 and organization_id = aprevid_rec.organization_id and control_id = aprevid_rec.control_id;
287
288 commit;
289 END LOOP;
290 -- To fetch step Id
291 fnd_file.put_line (fnd_file.LOG, 'To fetch Step Id');
292 FOR stepid_rec IN c_stepid LOOP
293 v_step_id := null;
294 select ap_step_id into v_step_id from amw_ap_steps_vl aasv, amw_audit_procedures_vl ap
295 where aasv.name = stepid_rec.step_name and aasv.cseqnum = stepid_rec.step_number
296 and aasv.audit_procedure_id = stepid_rec.audit_procedure_id
297 and ap.audit_procedure_rev_id = stepid_rec.audit_procedure_rev_id
298 and ap.audit_procedure_id = stepid_rec.audit_procedure_id
299 and ap.audit_procedure_rev_num >= aasv.from_rev_num
300 and ap.audit_procedure_rev_num < NVL(aasv.to_rev_num, ap.audit_procedure_rev_num + 1) ;
301
302
303 update amw_audit_procedure_interface set step_id = v_step_id
304 where step_name = stepid_rec.step_name and step_number = stepid_rec.step_number
305 and audit_procedure_id = stepid_rec.audit_procedure_id and batch_id = p_batch_id;
306
307 commit;
308 END LOOP;
309
310 -- To fetch the party Id from User Id
311 fnd_file.put_line (fnd_file.LOG, 'User Id' || g_user_id);
312 select person_party_id into v_emp_id from fnd_user where user_id = g_user_id;
313 fnd_file.put_line (fnd_file.LOG, 'Start Updating the details');
314
315 fnd_file.put_line (fnd_file.LOG, 'Updating procedure status');
316 -- To Update the audit procedure status
317 FOR ap_details_rec IN c_ap_details LOOP
318 v_ap_cnt := null;
319
320 -- To fetch the audit_procedure_id and taksid and control id
321
322 if ap_details_rec.audit_procedure_rev_id is null then
323
324 -- Adding Error msg to the interface table
325
326 v_error_msg := 'Audit Procedure Rev id is null for'||
327 ap_details_rec.ap_interface_id||' Batch Id '||p_batch_id;
328
329 update_interface_with_error(v_error_msg,ap_details_rec.ap_interface_id);
330
331 fnd_file.put_line (fnd_file.LOG, 'Audit Procedure Rev id is null for'||
332 ap_details_rec.ap_interface_id||' Batch Id '||p_batch_id);
333 else
334 select count(*) into v_ap_cnt from amw_ap_executions
335 where audit_procedure_rev_id = ap_details_rec.audit_procedure_rev_id
336 and pk1 = ap_details_rec.audit_project_id and pk2 = ap_details_rec.organization_id
337 and pk3 = ap_details_rec.task_id and execution_type = 'AP';
338
339 if v_ap_cnt = 0 then
340 insert into amw_ap_executions
341 (execution_id,audit_procedure_rev_id,last_update_date,
342 last_updated_by,creation_date,created_by,
343 last_update_login,executed_on,executed_by,
344 status,work_desc,execution_type,pk1,pk2,pk3,object_version_number)
345 values(amw_ap_executions_s.nextval,ap_details_rec.audit_procedure_rev_id,sysdate,
346 g_user_id,sysdate,g_user_id,
347 g_login_id,sysdate,v_emp_id,
348 ap_details_rec.ap_status,ap_details_rec.ap_work_description,
349 'AP',ap_details_rec.audit_project_id,ap_details_rec.organization_id,
350 ap_details_rec.task_id,1);
351 commit;
352 else
353
354 update amw_ap_executions set work_desc = ap_details_rec.ap_work_description,
355 status = ap_details_rec.ap_status,executed_by = v_emp_id,
356 executed_on = sysdate
357 where audit_procedure_rev_id = ap_details_rec.audit_procedure_rev_id
358 and pk1 = ap_details_rec.audit_project_id and pk2 = ap_details_rec.organization_id
359 and pk3 = ap_details_rec.task_id and execution_type = 'AP';
360 commit;
361 end if;
362 commit;
363 end if;
364
365 commit;
366
367 END LOOP; -- end of FOR ap_status_rec IN c_ap_status LOOP
368
369
370 fnd_file.put_line (fnd_file.LOG, 'Updating step status');
371 -- To Update the audit Step status
372 FOR step_details_rec IN c_step_details LOOP
373
374 v_step_cnt := null;
375 if step_details_rec.audit_procedure_rev_id is null then
376
377 -- Adding Error msg to the interface table
378
379 v_error_msg := 'Audit Procedure Rev id is null for'||
380 step_details_rec.ap_interface_id||' Batch Id '||p_batch_id;
381
382 update_interface_with_error(v_error_msg,step_details_rec.ap_interface_id);
383
384 fnd_file.put_line (fnd_file.LOG, 'Audit Procedure Rev id is null for'||
385 step_details_rec.ap_interface_id||' Batch Id '||p_batch_id);
386 else
387
388 if step_details_rec.step_id is null then
389
390 -- Adding Error msg to the interface table
391
392 v_error_msg := 'Step id is null for'||
393 step_details_rec.ap_interface_id||' Batch Id '||p_batch_id;
394
395 update_interface_with_error(v_error_msg,step_details_rec.ap_interface_id);
396
397 fnd_file.put_line (fnd_file.LOG, 'Step id is null for'||
398 step_details_rec.ap_interface_id||' Batch Id '||p_batch_id);
399
400 else
401 select count(*) into v_step_cnt from amw_ap_executions
402 where audit_procedure_rev_id = step_details_rec.audit_procedure_rev_id
403 and pk1 = step_details_rec.audit_project_id and pk2 = step_details_rec.organization_id
404 and pk3 = step_details_rec.task_id and execution_type = 'STEP'
405 and ap_step_id = step_details_rec.step_id;
406
407 if v_step_cnt = 0 then
408 insert into amw_ap_executions
409 (execution_id,audit_procedure_rev_id,ap_step_id,
410 last_update_date,last_updated_by,creation_date,created_by,
411 last_update_login,executed_on,executed_by,
412 status,work_desc,execution_type,pk1,pk2,pk3,object_version_number)
413 values(amw_ap_executions_s.nextval,step_details_rec.audit_procedure_rev_id,step_details_rec.step_id,
414 sysdate,g_user_id,sysdate,g_user_id,
415 g_login_id,sysdate,v_emp_id,
416 step_details_rec.step_status,step_details_rec.step_work_description,
417 'STEP',step_details_rec.audit_project_id,step_details_rec.organization_id,
418 step_details_rec.task_id,1);
419 commit;
420 else
421
422 update amw_ap_executions set work_desc = step_details_rec.step_work_description,
423 status = step_details_rec.step_status,executed_by = v_emp_id,
424 executed_on = sysdate
425 where audit_procedure_rev_id = step_details_rec.audit_procedure_rev_id
426 and pk1 = step_details_rec.audit_project_id and pk2 = step_details_rec.organization_id
427 and pk3 = step_details_rec.task_id and execution_type = 'STEP'
428 and ap_step_id = step_details_rec.step_id;
429 commit;
430 end if;
431 commit;
432 end if;
433 end if;
434
435 END LOOP; -- end of FOR step_status_rec IN c_step_details LOOP
436
437 -- To delete the existing Steps
438
439 -- To update the steps attachments
440
441 fnd_file.put_line (fnd_file.LOG, 'Updating step attachments');
442 FOR step_attach_rec IN c_steps_attach LOOP
443
444 if(step_attach_rec.steps_attachment_url IS NOT NULL) THEN
445
446 select count(*) into v_steps_attach_cnt from fnd_documents_tl where document_id in (
447 SELECT document_id FROM FND_ATTACHED_DOCUMENTS WHERE ENTITY_NAME='AMW_PROJECT_STEP'
448 AND PK1_VALUE = step_attach_rec.audit_project_id
449 AND PK2_VALUE = step_attach_rec.organization_id
450 AND PK3_VALUE = step_attach_rec.task_id AND PK4_VALUE = step_attach_rec.audit_procedure_rev_id
451 AND PK5_VALUE = step_attach_rec.step_id) and file_name = step_attach_rec.steps_attachment_url
452 and language(+)= userenv('LANG');
453 if v_steps_attach_cnt = 0 then
454 BEGIN
455 SELECT MAX(SEQ_NUM) INTO L_SEQ_NUM FROM FND_ATTACHED_DOCUMENTS WHERE ENTITY_NAME='AMW_PROJECT_STEP'
456 AND PK1_VALUE = step_attach_rec.audit_project_id
457 AND PK2_VALUE = step_attach_rec.organization_id
458 AND PK3_VALUE = step_attach_rec.task_id AND PK4_VALUE = step_attach_rec.audit_procedure_rev_id
459 AND PK5_VALUE = step_attach_rec.step_id;
460 EXCEPTION
461 WHEN NO_DATA_FOUND THEN
462 L_SEQ_NUM := 0;
463 WHEN OTHERS THEN
464 L_SEQ_NUM := 0;
465 END;
466
467 L_SEQ_NUM := L_SEQ_NUM+1;
468
469 l_attachment_rec.description := 'AUTO: ';
470 l_attachment_rec.file_name := step_attach_rec.steps_attachment_url;
471 l_attachment_rec.datatype_id := 5;
472 l_attachment_rec.seq_num := l_seq_num;
473 l_attachment_rec.entity_name := 'AMW_PROJECT_STEP';
474 l_attachment_rec.pk1_value := to_char(step_attach_rec.audit_project_id);
475 l_attachment_rec.pk2_value := to_char(step_attach_rec.organization_id);
476 l_attachment_rec.pk3_value := to_char(step_attach_rec.task_id);
477 l_attachment_rec.pk4_value := to_char(step_attach_rec.audit_procedure_rev_id);
478 l_attachment_rec.pk5_value := to_char(step_attach_rec.step_id);
479 l_attachment_rec.automatically_added_flag := 'N';
480 l_attachment_rec.category_id := 1;
481 l_attachment_rec.security_type := 4;
482 l_attachment_rec.publish_flag := 'Y';
483 l_attachment_rec.media_id := lx_media_id;
484
485 x_msg_data := null;
486 x_msg_count := 0;
487 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
488 AMW_LOAD_AUDIT_PROCEDURE_DATA.CREATE_FND_ATTACHMENT(
489 p_api_version_number => 1,
490 p_init_msg_list => FND_API.G_TRUE,
491 x_return_status => X_RETURN_STATUS,
492 x_msg_count => X_MSG_COUNT,
493 x_msg_data => X_MSG_DATA,
494 p_Fnd_Attachment_rec => l_attachment_rec,
495 x_document_id => LX_DOCUMENT_ID,
496 x_attached_document_id => LX_ATTACHED_DOCUMENT_ID
497 );
498 commit;
499 END IF;
500
501 END IF; --END OF IF ATTACHMENT URL IS NOT NULL
502
503 END LOOP; -- end of Steps Attachments
504
505 fnd_file.put_line (fnd_file.LOG, 'Getting the component Id');
506 -- T0 Update the Control Effectiveness Details
507
508 select opinion_component_id into v_overall from amw_opinion_componts_b
509 where opinion_component_code = 'OVERALL' and object_opinion_type_id =
510 (select object_opinion_type_id from AMW_OBJECT_OPINION_TYPES where object_id =
511 (select object_id from fnd_objects where obj_name = 'AMW_ORG_AP_CONTROL'));
512
513 select opinion_component_id into v_design from amw_opinion_componts_b
514 where opinion_component_code = 'DESIGN' and object_opinion_type_id =
515 (select object_opinion_type_id from AMW_OBJECT_OPINION_TYPES where object_id =
516 (select object_id from fnd_objects where obj_name = 'AMW_ORG_AP_CONTROL'));
517
518 select opinion_component_id into v_operating from amw_opinion_componts_b
519 where opinion_component_code = 'OPERATING' and object_opinion_type_id =
520 (select object_opinion_type_id from AMW_OBJECT_OPINION_TYPES where object_id =
521 (select object_id from fnd_objects where obj_name = 'AMW_ORG_AP_CONTROL'));
522
523 select object_opinion_type_id into v_opinion_object_id from AMW_OBJECT_OPINION_TYPES
524 where opinion_type_id = (select opinion_type_id from AMW_OPINION_TYPES_b
525 where opinion_type_code = 'EVALUATION') and object_id = (select object_id from
526 fnd_objects where obj_name = 'AMW_ORG_AP_CONTROL');
527
528 fnd_file.put_line (fnd_file.LOG, 'Updating Control evaluation details');
529 FOR eff_details_rec IN c_effec_details LOOP
530
531 v_opinions_cnt := 0;
532
533 if eff_details_rec.audit_procedure_id is null then
534 -- Adding Error msg to the interface table
535
536 v_error_msg := 'Audit Procedure id is null for'||
537 eff_details_rec.ap_interface_id||' Batch Id '||p_batch_id;
538
539 update_interface_with_error(v_error_msg,eff_details_rec.ap_interface_id);
540
541 fnd_file.put_line (fnd_file.LOG, 'Association id is null for'||
542 eff_details_rec.ap_interface_id||' Batch Id '||p_batch_id);
543
544 else
545 select count(*) into v_opinions_cnt from amw_opinions where
546 pk1_value = eff_details_rec.control_id and pk2_value = eff_details_rec.audit_project_id and
547 pk3_value = eff_details_rec.organization_id and pk4_value = eff_details_rec.audit_procedure_id and
548 pk5_value = eff_details_rec.task_id;
549
550
551 if v_opinions_cnt <> 0 then
552
553 select opinion_id into v_opinions_upd_id from amw_opinions where
554 pk1_value = eff_details_rec.control_id and pk2_value = eff_details_rec.audit_project_id and
555 pk3_value = eff_details_rec.organization_id and pk4_value = eff_details_rec.audit_procedure_id and
556 pk5_value = eff_details_rec.task_id;
557
558 if eff_details_rec.control_opinion is not null then
559 update amw_opinion_details set opinion_value_id =eff_details_rec.control_opinion,
560 summary_txt = eff_details_rec.ctrl_overall_summary,
561 description_txt = eff_details_rec.ctrl_overall_description
562 where opinion_id = v_opinions_upd_id and opinion_component_id = v_overall;
563 commit;
564 end if;
565
566 if eff_details_rec.design_effectiveness is not null then
567 update amw_opinion_details set opinion_value_id =eff_details_rec.design_effectiveness,
568 summary_txt = eff_details_rec.ctrl_design_summary,
569 description_txt = eff_details_rec.ctrl_design_description
570 where opinion_id = v_opinions_upd_id and opinion_component_id = v_design;
571 commit;
572 end if;
573
574 if eff_details_rec.op_effectiveness is not null then
575 update amw_opinion_details set opinion_value_id =eff_details_rec.op_effectiveness,
576 summary_txt = eff_details_rec.ctrl_operating_summary,
577 description_txt = eff_details_rec.ctrl_operating_description
578 where opinion_id = v_opinions_upd_id and opinion_component_id = v_operating;
579 commit;
580 end if;
581 else
582 if eff_details_rec.control_opinion is null then
583 eff_details_rec.ctrl_overall_summary := ' ';
584 eff_details_rec.ctrl_overall_description := ' ';
585 end if;
586
587 if eff_details_rec.design_effectiveness is null then
588 eff_details_rec.ctrl_design_summary := ' ';
589 eff_details_rec.ctrl_design_description := ' ';
590 end if;
591
592 if eff_details_rec.op_effectiveness is null then
593 eff_details_rec.ctrl_operating_summary := ' ';
594 eff_details_rec.ctrl_operating_description := ' ';
595 end if;
596
597 v_opinion_id := null;
598
599 if eff_details_rec.control_opinion is not null then
600
601
602 select AMW_OPINIONS_S2.nextval into v_opinion_id from dual;
603
604 -- Inserting into amw_opinions table
605
606 INSERT into amw_opinions(opinion_set_id,opinion_id,object_opinion_type_id,pk1_value,pk2_value,
607 pk3_value,pk4_value,pk5_value,party_id,authored_by,authored_date,last_update_date,last_updated_by,
608 creation_date,created_by,last_update_login) values
609 (AMW_OPINIONS_S1.nextval,v_opinion_id,v_opinion_object_id,eff_details_rec.control_id,eff_details_rec.audit_project_id,
610 eff_details_rec.organization_id,eff_details_rec.audit_procedure_id,eff_details_rec.task_id,v_party_id,G_LOGIN_ID,sysdate,sysdate,G_USER_ID,
611 sysdate,G_USER_ID,G_LOGIN_ID);
612
613 commit;
614 -- Inserting into amw_opinion_details table
615
616 INSERT into amw_opinion_details(opinion_detail_id,opinion_id,opinion_component_id,opinion_value_id,
617 last_update_date,last_updated_by,creation_date,created_by,last_update_login,object_version_number
618 ,summary_txt,description_txt) values
619 (AMW_OPINION_DETAILS_S1.nextval,v_opinion_id,v_overall,eff_details_rec.control_opinion,
620 sysdate,G_USER_ID,sysdate,G_USER_ID,G_LOGIN_ID,1,eff_details_rec.ctrl_overall_summary,eff_details_rec.ctrl_overall_description);
621
622 commit;
623 INSERT into amw_opinion_details(opinion_detail_id,opinion_id,opinion_component_id,opinion_value_id,
624 last_update_date,last_updated_by,creation_date,created_by,last_update_login,object_version_number
625 ,summary_txt,description_txt) values
626 (AMW_OPINION_DETAILS_S1.nextval,v_opinion_id,v_design,eff_details_rec.design_effectiveness,
627 sysdate,G_USER_ID,sysdate,G_USER_ID,G_LOGIN_ID,1,eff_details_rec.ctrl_design_summary,eff_details_rec.ctrl_design_description);
628 commit;
629
630 INSERT into amw_opinion_details(opinion_detail_id,opinion_id,opinion_component_id,opinion_value_id,
631 last_update_date,last_updated_by,creation_date,created_by,last_update_login,object_version_number
632 ,summary_txt,description_txt) values
633 (AMW_OPINION_DETAILS_S1.nextval,v_opinion_id,v_operating,eff_details_rec.op_effectiveness,
634 sysdate,G_USER_ID,sysdate,G_USER_ID,G_LOGIN_ID,1,eff_details_rec.ctrl_operating_summary,eff_details_rec.ctrl_operating_description);
635 commit;
636
637 end if;
638 end if;
639
640 commit;
641
642 -- Inserting into Opinion Log Tables
643
644 select AMW_OPINIONS_LOG_S.nextval into v_opinion_log_id from dual;
645
646
647 select opinion_id into v_opinions_logid from amw_opinions where
648 pk1_value = eff_details_rec.control_id and pk2_value = eff_details_rec.audit_project_id and
649 pk3_value = eff_details_rec.organization_id and pk4_value = eff_details_rec.audit_procedure_id and
650 pk5_value = eff_details_rec.task_id;
651
652 select opinion_set_id into v_opinions_set_logid from amw_opinions where
653 pk1_value = eff_details_rec.control_id and pk2_value = eff_details_rec.audit_project_id and
654 pk3_value = eff_details_rec.organization_id and pk4_value = eff_details_rec.audit_procedure_id and
655 pk5_value = eff_details_rec.task_id;
656
657
658 select opinion_detail_id into v_overall_detail_logid from amw_opinion_details
659 where opinion_id = v_opinions_logid and opinion_component_id = v_overall;
660
661 select opinion_detail_id into v_design_detail_logid from amw_opinion_details
662 where opinion_id = v_opinions_logid and opinion_component_id = v_design;
663
664 select opinion_detail_id into v_operating_detail_logid from amw_opinion_details
665 where opinion_id = v_opinions_logid and opinion_component_id = v_operating;
666
667
668 if v_opinions_logid is not null then
669
670
671 -- Inserting into amw_opinions_log table
672
673 INSERT into amw_opinions_log(opinion_log_id,opinion_set_id,opinion_id,object_opinion_type_id,pk1_value,pk2_value,
674 pk3_value,pk4_value,pk5_value,party_id,authored_by,authored_date,last_update_date,last_updated_by,
675 creation_date,created_by,last_update_login) values
676 (v_opinion_log_id,v_opinions_set_logid,v_opinions_logid,v_opinion_object_id,eff_details_rec.control_id,eff_details_rec.audit_project_id,
677 eff_details_rec.organization_id,eff_details_rec.audit_procedure_id,eff_details_rec.task_id,v_party_id,G_LOGIN_ID,sysdate,sysdate,G_USER_ID,
678 sysdate,G_USER_ID,G_LOGIN_ID);
679
680 commit;
681
682
683 if v_overall_detail_logid is not null then
684
685 -- Inserting into amw_opinion_log_details table
686
687
688 INSERT into amw_opinion_log_details(opinion_log_id,opinion_detail_id,opinion_id,opinion_component_id,opinion_value_id,
689 last_update_date,last_updated_by,creation_date,created_by,last_update_login,object_version_number
690 ,summary_txt,description_txt,opinion_log_detail_id) values
691 (v_opinion_log_id,v_overall_detail_logid,v_opinions_logid,v_overall,eff_details_rec.control_opinion,
692 sysdate,G_USER_ID,sysdate,G_USER_ID,G_LOGIN_ID,1,eff_details_rec.ctrl_overall_summary,eff_details_rec.ctrl_overall_description
693 ,AMW_OPINION_LOG_DETAILS_S.nextval);
694
695 commit;
696
697 if v_design_detail_logid is not null then
698
699 INSERT into amw_opinion_log_details(opinion_log_id,opinion_log_detail_id,opinion_detail_id,opinion_id,opinion_component_id,opinion_value_id,
700 last_update_date,last_updated_by,creation_date,created_by,last_update_login,object_version_number
701 ,summary_txt,description_txt) values
702 (v_opinion_log_id,AMW_OPINION_LOG_DETAILS_S.nextval,v_design_detail_logid,v_opinions_logid,v_design,eff_details_rec.design_effectiveness,
703 sysdate,G_USER_ID,sysdate,G_USER_ID,G_LOGIN_ID,1,eff_details_rec.ctrl_design_summary,eff_details_rec.ctrl_design_description);
704 commit;
705
706 end if;
707
708 if v_operating_detail_logid is not null then
709
710 INSERT into amw_opinion_log_details(opinion_log_id,opinion_log_detail_id,opinion_detail_id,opinion_id,opinion_component_id,opinion_value_id,
711 last_update_date,last_updated_by,creation_date,created_by,last_update_login,object_version_number
712 ,summary_txt,description_txt) values
713 (v_opinion_log_id,AMW_OPINION_LOG_DETAILS_S.nextval,v_operating_detail_logid,v_opinions_logid,v_operating,eff_details_rec.op_effectiveness,
714 sysdate,G_USER_ID,sysdate,G_USER_ID,G_LOGIN_ID,1,eff_details_rec.ctrl_operating_summary,eff_details_rec.ctrl_operating_description);
715 commit;
716
717 end if;
718 end if;
719 end if;
720 end if;
721 END LOOP; -- end of FOR eff_status_rec IN c_step_details LOOP
722 commit;
723
724 -- To delete data from interface table
725 delete from amw_audit_procedure_interface where batch_id = p_batch_id;
726 EXCEPTION
727 WHEN NO_DATA_FOUND THEN
728 NULL;
729 WHEN OTHERS THEN
730 fnd_file.put_line (fnd_file.LOG,'Error in update '||SUBSTR (SQLERRM, 1, 200));
731 END;
732 END update_apr;
733
734 --
735 -- procedure update_interface_with_error
736 --
737 --
738 PROCEDURE update_interface_with_error (
739 p_err_msg IN VARCHAR2
740 ,p_interface_id IN NUMBER
741 )
742 IS
743 l_interface_status amw_audit_procedure_interface.interface_status%TYPE;
744 BEGIN
745 ROLLBACK; -- rollback any inserts done during the current loop process
746 v_error_found := TRUE;
747
748 BEGIN
749 SELECT interface_status
750 INTO l_interface_status
751 FROM amw_audit_procedure_interface
752 WHERE ap_interface_id = p_interface_id;
753 EXCEPTION
754 WHEN OTHERS
755 THEN
756 v_err_msg :=
757 'interface_id: = '
758 || p_interface_id
759 || ' '
760 || SUBSTR (SQLERRM, 1, 100);
761 fnd_file.put_line (fnd_file.LOG, SUBSTR (v_err_msg, 1, 200));
762 END;
763
764 BEGIN
765 UPDATE amw_audit_procedure_interface
766 SET interface_status =
767 l_interface_status
768 || p_err_msg
769 ,error_flag = 'Y'
770 WHERE ap_interface_id = p_interface_id;
771
772 fnd_file.put_line (fnd_file.LOG, SUBSTR (l_interface_status, 1, 200));
773 COMMIT;
774 EXCEPTION
775 WHEN OTHERS
776 THEN
777 v_err_msg :=
778 'Error during package processing '
779 || ' interface_id: = '
780 || p_interface_id
781 || SUBSTR (SQLERRM, 1, 100);
782 fnd_file.put_line (fnd_file.LOG, SUBSTR (v_err_msg, 1, 200));
783 END;
784
785 COMMIT;
786 END update_interface_with_error;
787
788 PROCEDURE Create_Fnd_Attachment(
789 p_api_version_number IN NUMBER,
790 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
791 p_commit IN VARCHAR2 := FND_API.G_FALSE,
792 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
793
794 x_return_status OUT NOCOPY VARCHAR2,
795 x_msg_count OUT NOCOPY NUMBER,
796 x_msg_data OUT NOCOPY VARCHAR2,
797
798 p_Fnd_Attachment_rec IN fnd_attachment_rec_type,
799 x_document_id OUT NOCOPY NUMBER,
800 x_attached_document_id OUT NOCOPY NUMBER
801 )
802
803 IS
804 L_API_NAME CONSTANT VARCHAR2(30) := 'Create_Fnd_Attachment';
805 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
806 l_return_status_full VARCHAR2(1);
807 l_document_ID NUMBER;
808 l_media_ID NUMBER;
809 l_attached_document_ID NUMBER;
810 l_dummy NUMBER;
811 l_seq_num NUMBER := 10;
812 l_row_id VARCHAR2(255);
813 l_Fnd_Attachment_rec fnd_attachment_rec_type;
814 l_create_Attached_Doc boolean := true;
815
816 CURSOR c_attached_doc_id IS
817 SELECT FND_ATTACHED_DOCUMENTS_S.nextval
818 FROM dual;
819
820 CURSOR c_attached_doc_id_exists (l_id IN NUMBER) IS
821 SELECT 1
822 FROM FND_ATTACHED_DOCUMENTS
823 WHERE document_id = l_id;
824
825 BEGIN
826 -- Standard Start of API savepoint
827 SAVEPOINT CREATE_Fnd_Attachment_PVT;
828
829 -- Standard call to check for call compatibility.
830 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
831 p_api_version_number,
832 l_api_name,
833 G_PKG_NAME)
834 THEN
835 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
836 END IF;
837
838 -- Initialize message list if p_init_msg_list is set to TRUE.
839 IF FND_API.to_Boolean( p_init_msg_list )
840 THEN
841 FND_MSG_PUB.initialize;
842 END IF;
843
844 -- Debug Message
845 IF (AMW_DEBUG_HIGH_ON) THEN
846 AMW_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
847 END IF;
848
849
850 -- Initialize API return status to SUCCESS
851 x_return_status := FND_API.G_RET_STS_SUCCESS;
852
853 -- Local variable initialization
854
855 -- =========================================================================
856 -- Validate Environment
857 -- =========================================================================
858
859 IF FND_GLOBAL.User_Id IS NULL
860 THEN
861 AMW_Utility_PVT.Error_Message(p_message_name => 'USER_PROFILE_MISSING');
862 RAISE FND_API.G_EXC_ERROR;
863 END IF;
864
865 -- Debug Message
866 IF (AMW_DEBUG_HIGH_ON) THEN
867 AMW_UTILITY_PVT.debug_message( 'Private API: Calling table handler fnd_documents_pkg.insert_row');
868 END IF;
869
870 l_media_id := p_Fnd_Attachment_rec.media_id;
871
872 -- Invoke table handler
873 fnd_documents_pkg.insert_row(
874 X_rowid => l_row_id,
875 X_document_id => x_document_id,
876 X_creation_date => sysdate,
877 X_created_by => FND_GLOBAL.USER_ID,
878 X_last_update_date => sysdate,
879 X_last_updated_by => FND_GLOBAL.USER_ID,
880 X_last_update_login => FND_GLOBAL.CONC_LOGIN_ID,
881 X_datatype_id => p_Fnd_Attachment_rec.datatype_id,
882 X_category_id => p_Fnd_Attachment_rec.category_id,
883 X_security_type => p_Fnd_Attachment_rec.security_type,
884 X_publish_flag => p_Fnd_Attachment_rec.publish_flag,
885 X_usage_type => p_Fnd_Attachment_rec.usage_type,
886 X_language => p_Fnd_Attachment_rec.language,
887 X_description =>p_Fnd_Attachment_rec.description,
888 X_file_name => p_Fnd_Attachment_rec.file_name,
889 X_media_id => l_media_id
890 );
891 if (p_Fnd_Attachment_rec.datatype_id = 1) then
892
893 /* Verify if the media_id is not null */
894 if (p_Fnd_Attachment_rec.media_id is null) then
895 /* It means that a new text needs to be created, otherwise not */
896 /* Populate Short Text */
897 insert into
898 fnd_documents_short_text
899 (media_id,
900 short_text
901 )
902 values
903 (l_media_id,
904 p_Fnd_Attachment_rec.short_text
905 );
906 else
907 /*
908 Update fnd_documents_tl because FND_API inserts newly generated
909 media_id into that table.
910 */
911 update fnd_documents_tl
912 set media_id = p_Fnd_Attachment_rec.media_id
913 where document_id = x_document_id;
914
915 end if;
916
917 elsif (p_Fnd_Attachment_rec.datatype_id = 6) then /* File */
918 /* For File we have already generated a file id - the fnd_documents_pkg.insert_row
919 table handler has generated a fnd_lobs_s.nextval but that's not what shoule be the
920 reference to the FND_LOBS table - because the upload program has already generated a
921 sequence */
922 /**
923 update fnd_documents_tl
924 set media_id = p_Fnd_Attachment_rec.media_id
925 where document_id = l_document_id;
926 **/
927 null;
928 end if;
929
930 if (p_Fnd_Attachment_rec.attachment_type is not null) then
931
932 if ((p_Fnd_Attachment_rec.attachment_type = 'WEB_TEXT') OR
933 (p_Fnd_Attachment_rec.attachment_type = 'WEB_IMAGE')) then
934
935 l_create_Attached_Doc := false;
936
937 end if;
938
939 end if;
940
941 if (l_create_Attached_Doc) then
942
943 /*
944 IF p_Fnd_Attachment_rec.attached_DOCUMENT_ID IS NULL THEN
945 */
946 LOOP
947 l_dummy := NULL;
948 OPEN c_attached_doc_id;
949 FETCH c_attached_doc_id INTO l_attached_document_ID;
950 CLOSE c_attached_doc_id;
951
952 OPEN c_attached_doc_id_exists(l_attached_document_ID);
953 FETCH c_attached_doc_id_exists INTO l_dummy;
954 CLOSE c_attached_doc_id_exists;
955 EXIT WHEN l_dummy IS NULL;
956 END LOOP;
957
958 l_Fnd_Attachment_rec.attached_document_id := l_attached_document_id;
959 x_attached_document_id := l_attached_document_id;
960
961
962 /* Populate FND Attachments */
963 fnd_attached_documents_pkg.Insert_Row
964 ( x_rowid => l_row_id,
965 X_attached_document_id => l_attached_document_ID,
966 X_document_id => x_document_ID,
967 X_creation_date => sysdate,
968 X_created_by => FND_GLOBAL.USER_ID,
969 X_last_update_date => sysdate,
970 X_last_updated_by => FND_GLOBAL.USER_ID,
971 X_last_update_login => FND_GLOBAL.CONC_LOGIN_ID,
972 X_seq_num => l_seq_num,
973 X_entity_name => p_Fnd_Attachment_rec.entity_name,
974 x_column1 => null,
975 X_pk1_value => p_Fnd_Attachment_rec.pk1_value,
976 X_pk2_value => p_Fnd_Attachment_rec.pk2_value,
977 X_pk3_value => p_Fnd_Attachment_rec.pk3_value,
978 X_pk4_value => p_Fnd_Attachment_rec.pk4_value,
979 X_pk5_value => p_Fnd_Attachment_rec.pk5_value,
980 X_automatically_added_flag => p_Fnd_Attachment_rec.automatically_added_flag,
981 X_datatype_id => null,
982 X_category_id => null,
983 X_security_type => null,
984 X_publish_flag => null,
985 X_usage_type => p_Fnd_Attachment_rec.usage_type,
986 X_language => null,
987 X_media_id => l_media_id,
988 X_doc_attribute_Category => null,
989 X_doc_attribute1 => null,
990 X_doc_attribute2 => null,
991 X_doc_attribute3 => null,
992 X_doc_attribute4 => null,
993 X_doc_attribute5 => null,
994 X_doc_attribute6 => null,
995 X_doc_attribute7 => null,
996 X_doc_attribute8 => null,
997 X_doc_attribute9 => null,
998 X_doc_attribute10 => null,
999 X_doc_attribute11 => null,
1000 X_doc_attribute12 => null,
1001 X_doc_attribute13 => null,
1002 X_doc_attribute14 => null,
1003 X_doc_attribute15 => null
1004 );
1005 end if;
1006
1007
1008 -- Standard check for p_commit
1009 IF FND_API.to_Boolean( p_commit )
1010 THEN
1011 COMMIT WORK;
1012 END IF;
1013
1014
1015 -- Debug Message
1016 IF (AMW_DEBUG_HIGH_ON) THEN
1017 AMW_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
1018 END IF;
1019
1020 -- Standard call to get message count and if count is 1, get message info.
1021 FND_MSG_PUB.Count_And_Get
1022 (p_count => x_msg_count,
1023 p_data => x_msg_data
1024 );
1025
1026
1027 EXCEPTION
1028
1029 WHEN AMW_Utility_PVT.resource_locked THEN
1030 x_return_status := FND_API.g_ret_sts_error;
1031 AMW_Utility_PVT.Error_Message(p_message_name => 'AMW_API_RESOURCE_LOCKED');
1032
1033 WHEN FND_API.G_EXC_ERROR THEN
1034 ROLLBACK TO CREATE_Fnd_Attachment_PVT;
1035 x_return_status := FND_API.G_RET_STS_ERROR;
1036 -- Standard call to get message count and if count=1, get the message
1037 FND_MSG_PUB.Count_And_Get (
1038 p_encoded => FND_API.G_FALSE,
1039 p_count => x_msg_count,
1040 p_data => x_msg_data
1041 );
1042
1043 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1044 ROLLBACK TO CREATE_Fnd_Attachment_PVT;
1045 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1046 -- Standard call to get message count and if count=1, get the message
1047 FND_MSG_PUB.Count_And_Get (
1048 p_encoded => FND_API.G_FALSE,
1049 p_count => x_msg_count,
1050 p_data => x_msg_data
1051 );
1052
1053 WHEN OTHERS THEN
1054 ROLLBACK TO CREATE_Fnd_Attachment_PVT;
1055 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1056 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1057 THEN
1058 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1059 END IF;
1060 -- Standard call to get message count and if count=1, get the message
1061 FND_MSG_PUB.Count_And_Get (
1062 p_encoded => FND_API.G_FALSE,
1063 p_count => x_msg_count,
1064 p_data => x_msg_data
1065 );
1066 End Create_Fnd_Attachment;
1067
1068 END AMW_LOAD_AUDIT_PROCEDURE_DATA;
1069