[Home] [Help]
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
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
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
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:
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:
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:
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:
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
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:
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:
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:
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
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
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;
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;
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;
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:
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:
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:
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;
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
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:
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
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'