1 PACKAGE BODY AMW_DELETE_OBJECT_PVT as
2 /* $Header: amwvobjb.pls 120.1.12000000.2 2007/03/09 10:00:37 psomanat ship $ */
3
4 -- ===============================================================
5 -- Package name
6 -- AMW_DELETE_OBJECT_PVT
7 -- Purpose
8 -- for handling object delete actions
9 --
10 -- History
11 -- 12/06/2004 tsho Creates
12 -- ===============================================================
13
14
15 G_PKG_NAME CONSTANT VARCHAR2(30) := 'AMW_DELETE_OBJECT_PVT';
16 G_FILE_NAME CONSTANT VARCHAR2(12) := 'amwvobjb.pls';
17
18
19 -- Risk query check list
20 t_risk_dynamic_sql_list G_VARRAY_TABLE := G_VARRAY_TABLE(
21 G_VARCHAR_VARRAY('select risk_id from amw_risk_associations where risk_id = :1','Y')
22 ,G_VARCHAR_VARRAY('select object_type from amw_control_associations where ( object_type = ''RISK'' and pk2 =:1 ) or (object_type = ''RISK_ORG'' and pk3 =:1) ','Y')
23 );
24
25
26 -- Control query check list
27 t_ctrl_dynamic_sql_list G_VARRAY_TABLE := G_VARRAY_TABLE(
28 G_VARCHAR_VARRAY('select control_id from amw_control_associations where control_id = :1','Y')
29 ,G_VARCHAR_VARRAY('select object_type from amw_ap_associations where ( object_type = ''CTRL'' and pk1 =:1 ) or (object_type = ''CTRL_ORG'' and pk3 =:1) ','Y')
30 );
31
32
33 -- Audit Procedure query check list
34 t_ap_dynamic_sql_list G_VARRAY_TABLE := G_VARRAY_TABLE(
35 G_VARCHAR_VARRAY( 'SELECT control_id '
36 ||' FROM amw_control_associations WHERE control_id IN ( '
37 ||' SELECT pk1 '
38 ||' FROM amw_ap_associations '
39 ||' WHERE audit_procedure_id = :1 '
40 ||' AND object_type = ''CTRL'' '
41 ||' UNION '
42 ||' SELECT pk2 '
43 ||' FROM amw_ap_associations '
44 ||' WHERE object_type =''ENTITY_AP'' '
45 ||' AND audit_procedure_id = :1 '
46 ||' AND deletion_date IS NULL '
47 ||' UNION '
48 ||' SELECT pk3 '
49 ||' FROM amw_ap_associations '
50 ||' WHERE object_type =''PROJECT'' '
51 ||' AND audit_procedure_id = :1 '
52 ||' AND deletion_date IS NULL '
53 ||' UNION '
54 ||' SELECT pk3 '
55 ||' FROM amw_ap_associations '
56 ||' WHERE object_type in (''CTRL_ORG'',''CTRL_FINCERT'',''BUSIPROC_CERTIFICATION'') '
57 ||' AND audit_procedure_id = :1 ) ','Y')
58 );
59
60
61
62 -- ===============================================================
63 -- Procedure name
64 -- Delete_Objects
65 -- Purpose
66 -- Delete specified Objs if it's allowed (ie, if it's not in use by others)
67 -- Params
68 -- p_object_type_and_id1 := the obj needs to be checked (format: OBJECT_TYPE#OBJECT_ID)
69 -- p_object_type_and_id2 := the obj needs to be checked (format: OBJECT_TYPE#OBJECT_ID)
70 -- p_object_type_and_id3 := the obj needs to be checked (format: OBJECT_TYPE#OBJECT_ID)
71 -- p_object_type_and_id4 := the obj needs to be checked (format: OBJECT_TYPE#OBJECT_ID)
72 -- Notes
73 -- format for Risk: RISK#113
74 -- format for Control: CTRL#113
75 -- format for Audit Procedure: AP#113
76 -- ===============================================================
77 PROCEDURE Delete_Objects(
78 errbuf OUT NOCOPY VARCHAR2,
79 retcode OUT NOCOPY VARCHAR2,
80 p_object_type_and_id1 IN VARCHAR2 := NULL,
81 p_object_type_and_id2 IN VARCHAR2 := NULL,
82 p_object_type_and_id3 IN VARCHAR2 := NULL,
83 p_object_type_and_id4 IN VARCHAR2 := NULL
84 )
85 IS
86 L_API_NAME CONSTANT VARCHAR2(30) := 'Delete_Objects';
87 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
88
89 l_object_type1 VARCHAR2(30);
90 l_object_id1 NUMBER;
91
92 l_object_type2 VARCHAR2(30);
93 l_object_id2 NUMBER;
94
95 l_object_type3 VARCHAR2(30);
96 l_object_id3 NUMBER;
97
98 l_object_type4 VARCHAR2(30);
99 l_object_id4 NUMBER;
100
101 l_dummy_type_and_id VARCHAR(80);
102
103 BEGIN
104 --FND_FILE.put_line(fnd_file.log,'inside api '||L_API_NAME);
105
106 IF p_object_type_and_id1 IS NOT NULL THEN
107 FND_FILE.put_line(fnd_file.log, '******* p_object_type_and_id1 = '||p_object_type_and_id1||' *******');
108 l_dummy_type_and_id := trim(p_object_type_and_id1);
109 l_object_type1 := substr(l_dummy_type_and_id, 1, instr(l_dummy_type_and_id, '#')-1);
110 l_object_id1 := to_number(substr(l_dummy_type_and_id, instr(l_dummy_type_and_id, '#')+1));
111 Delete_Object(p_object_type => l_object_type1, p_object_id => l_object_id1);
112 END IF;
113
114 IF p_object_type_and_id2 IS NOT NULL THEN
115 FND_FILE.put_line(fnd_file.log, '******* p_object_type_and_id2 = '||p_object_type_and_id2||' *******');
116 l_dummy_type_and_id := trim(p_object_type_and_id2);
117 l_object_type2 := substr(l_dummy_type_and_id, 1, instr(l_dummy_type_and_id, '#')-1);
118 l_object_id2 := to_number(substr(l_dummy_type_and_id, instr(l_dummy_type_and_id, '#')+1));
119 Delete_Object(p_object_type => l_object_type2, p_object_id => l_object_id2);
120 END IF;
121
122 IF p_object_type_and_id3 IS NOT NULL THEN
123 FND_FILE.put_line(fnd_file.log, '******* p_object_type_and_id3 = '||p_object_type_and_id3||' *******');
124 l_dummy_type_and_id := trim(p_object_type_and_id3);
125 l_object_type3 := substr(l_dummy_type_and_id, 1, instr(l_dummy_type_and_id, '#')-1);
126 l_object_id3 := to_number(substr(l_dummy_type_and_id, instr(l_dummy_type_and_id, '#')+1));
127 Delete_Object(p_object_type => l_object_type3, p_object_id => l_object_id3);
128 END IF;
129
130 IF p_object_type_and_id4 IS NOT NULL THEN
131 FND_FILE.put_line(fnd_file.log, '******* p_object_type_and_id4 = '||p_object_type_and_id4||' *******');
132 l_dummy_type_and_id := trim(p_object_type_and_id4);
133 l_object_type4 := substr(l_dummy_type_and_id, 1, instr(l_dummy_type_and_id, '#')-1);
134 l_object_id4 := to_number(substr(l_dummy_type_and_id, instr(l_dummy_type_and_id, '#')+1));
135 Delete_Object(p_object_type => l_object_type4, p_object_id => l_object_id4);
136 END IF;
137
138
139 End Delete_Objects;
140
141
142
143 -- ===============================================================
144 -- Procedure name
145 -- Delete_Object
146 -- Purpose
147 -- Delete specified Obj if it's allowed (ie, if it's not in use by others)
148 -- Params
149 -- p_object_type := the obj needs to be checked,
150 -- p_object_id := the id of specified obj
151 -- ===============================================================
152 PROCEDURE Delete_Object(
153 p_object_type IN VARCHAR2,
154 p_object_id IN NUMBER
155 )
156 IS
157 l_Is_Object_In_Use VARCHAR2(1);
158
159 BEGIN
160 l_Is_Object_In_Use := 'Y';
161
162 IF (p_object_type IS NOT NULL) THEN
163 IF (p_object_type = 'RISK') THEN
164 l_Is_Object_In_Use := Is_Object_In_Use(p_dynamic_sql_list => t_risk_dynamic_sql_list,
165 p_bind_value => p_object_id);
166 IF (l_Is_Object_In_Use = 'N') THEN
167 -- object is not in use, can delete it
168 Delete_Risk(p_risk_id => p_object_id);
169 ELSE
170 FND_FILE.put_line(fnd_file.log, 'Cannot Delete Risk : risk_id = '||p_object_id);
171 END IF;
172
173 ELSIF (p_object_type = 'CTRL') THEN
174 l_Is_Object_In_Use := Is_Object_In_Use(p_dynamic_sql_list => t_ctrl_dynamic_sql_list,
175 p_bind_value => p_object_id);
176 IF (l_Is_Object_In_Use = 'N') THEN
177 -- object is not in use, can delete it
178 Delete_Ctrl(p_ctrl_id => p_object_id);
179 ELSE
180 FND_FILE.put_line(fnd_file.log, 'Cannot Delete Control : control_id = '||p_object_id);
181 END IF;
182
183 ELSIF (p_object_type = 'AP') THEN
184 l_Is_Object_In_Use := Is_Object_In_Use(p_dynamic_sql_list => t_ap_dynamic_sql_list,
185 p_bind_value => p_object_id);
186 IF (l_Is_Object_In_Use = 'N') THEN
187 -- object is not in use, can delete it
188 Delete_Ap(p_ap_id => p_object_id);
189 ELSE
190 FND_FILE.put_line(fnd_file.log, 'Cannot Delete Audit Procedure : audit_procedure_id = '||p_object_id);
191 END IF;
192
193 END IF;
194 END IF; -- end of if: p_object_type IS NOT NULL
195
196 END Delete_Object;
197
198
199
200 -- ===============================================================
201 -- Procedure name
202 -- Delete_Risk
203 -- Purpose
204 -- Delete specified risk
205 -- Params
206 -- p_risk_id
207 -- ===============================================================
208 PROCEDURE Delete_Risk(
209 p_risk_id IN NUMBER
210 )
211 IS
212 -- find the list of risk_rev_id by specified risk_id
213 l_risk_rev_id_list G_NUMBER_TABLE;
214
215 BEGIN
216 IF (p_risk_id IS NOT NULL) THEN
217 SELECT risk_rev_id
218 BULK COLLECT INTO l_risk_rev_id_list
219 FROM AMW_RISKS_B
220 WHERE risk_id = p_risk_id;
221
222 -- Risk Type Association
223 FORALL i IN l_risk_rev_id_list.FIRST .. l_risk_rev_id_list.LAST
224 DELETE FROM AMW_RISK_TYPE WHERE risk_rev_id = l_risk_rev_id_list(i);
225
226 -- Extensible Attr
227 DELETE FROM AMW_RISK_EXT_B WHERE risk_id = p_risk_id;
228 DELETE FROM AMW_RISK_EXT_TL WHERE risk_id = p_risk_id;
229
230 -- Attachment
231 FND_ATTACHED_DOCUMENTS2_PKG.delete_attachments('AMW_RISKS',
232 p_risk_id,
233 null,
234 null,
235 null,
236 null,
237 'N',
238 null);
239
240 -- Risk
241 FORALL i IN l_risk_rev_id_list.FIRST .. l_risk_rev_id_list.LAST
242 DELETE FROM AMW_RISKS_TL WHERE risk_rev_id = l_risk_rev_id_list(i);
243
244 DELETE FROM AMW_RISKS_B WHERE risk_id = p_risk_id;
245
246 FND_FILE.put_line(fnd_file.log, 'Delete Risk : risk_id = '||p_risk_id);
247
248 IF (sql%notfound) then
249 RAISE no_data_found;
250 END IF;
251
252 END IF;
253
254 EXCEPTION
255 WHEN no_data_found THEN
256 NULL;
257 WHEN others THEN
258 NULL;
259 END Delete_Risk;
260
261
262 -- ===============================================================
263 -- Procedure name
264 -- Delete_Ctrl
265 -- Purpose
266 -- Delete specified control
267 -- Params
268 -- p_ctrl_id
269 -- ===============================================================
270 PROCEDURE Delete_Ctrl(
271 p_ctrl_id IN NUMBER
272 )
273 IS
274 -- find the list of control_rev_id by specified control_id
275 l_ctrl_rev_id_list G_NUMBER_TABLE;
276
277 BEGIN
278 IF (p_ctrl_id IS NOT NULL) THEN
279 SELECT control_rev_id
280 BULK COLLECT INTO l_ctrl_rev_id_list
281 FROM AMW_CONTROLS_B
282 WHERE control_id = p_ctrl_id;
283
284 -- Control Objective
285 FORALL i IN l_ctrl_rev_id_list.FIRST .. l_ctrl_rev_id_list.LAST
286 DELETE FROM AMW_CONTROL_OBJECTIVES WHERE control_rev_id = l_ctrl_rev_id_list(i);
287
288 -- Control Assertion
289 FORALL i IN l_ctrl_rev_id_list.FIRST .. l_ctrl_rev_id_list.LAST
290 DELETE FROM AMW_CONTROL_ASSERTIONS WHERE control_rev_id = l_ctrl_rev_id_list(i);
291
292 -- Control Purpose
293 FORALL i IN l_ctrl_rev_id_list.FIRST .. l_ctrl_rev_id_list.LAST
294 DELETE FROM AMW_CONTROL_PURPOSES WHERE control_rev_id = l_ctrl_rev_id_list(i);
295
296 -- Control Component
297 FORALL i IN l_ctrl_rev_id_list.FIRST .. l_ctrl_rev_id_list.LAST
298 DELETE FROM AMW_ASSESSMENT_COMPONENTS WHERE OBJECT_TYPE='CONTROL' AND OBJECT_ID = l_ctrl_rev_id_list(i);
299
300 -- Extensible Attr
301 DELETE FROM AMW_CTRL_EXT_B WHERE control_id = p_ctrl_id;
302 DELETE FROM AMW_CTRL_EXT_TL WHERE control_id = p_ctrl_id;
303
304 -- Attachment
305 FND_ATTACHED_DOCUMENTS2_PKG.delete_attachments('AMW_CONTROLS',
306 p_ctrl_id,
307 null,
308 null,
309 null,
310 null,
311 'N',
312 null);
313 -- Control
314 FORALL i IN l_ctrl_rev_id_list.FIRST .. l_ctrl_rev_id_list.LAST
315 DELETE FROM AMW_CONTROLS_TL WHERE control_rev_id = l_ctrl_rev_id_list(i);
316
317 DELETE FROM AMW_CONTROLS_B WHERE control_id = p_ctrl_id;
318
319 FND_FILE.put_line(fnd_file.log, 'Delete Control : control_id = '||p_ctrl_id);
320
321 IF (sql%notfound) then
322 RAISE no_data_found;
323 END IF;
324
325 END IF;
326
327 EXCEPTION
328 WHEN no_data_found THEN
329 NULL;
330 WHEN others THEN
331 NULL;
332 END Delete_Ctrl;
333
334
335 -- ===============================================================
339 -- Delete specified audit procedure
336 -- Procedure name
337 -- Delete_Ap
338 -- Purpose
340 -- Params
341 -- p_ap_id
342 -- ===============================================================
343 PROCEDURE Delete_Ap(
344 p_ap_id IN NUMBER
345 )
346 IS
347 -- find the list of audit_procedure_rev_id by specified audit_procedure_id
348 l_ap_rev_id_list G_NUMBER_TABLE;
349
350 BEGIN
351 IF (p_ap_id IS NOT NULL) THEN
352 SELECT audit_procedure_rev_id
353 BULK COLLECT INTO l_ap_rev_id_list
354 FROM AMW_AUDIT_PROCEDURES_B
355 WHERE audit_procedure_id = p_ap_id;
356
357 -- Step
358 DELETE FROM AMW_AP_STEPS_TL WHERE ap_step_id in (SELECT ap_step_id FROM amw_ap_steps_b WHERE audit_procedure_id = p_ap_id);
359 DELETE FROM AMW_AP_STEPS_B WHERE audit_procedure_id = p_ap_id;
360
361 -- Task
362 DELETE FROM AMW_AP_TASKS WHERE audit_procedure_id = p_ap_id;
363
364 -- Extensible Attr
365 DELETE FROM AMW_AP_EXT_B WHERE audit_procedure_id = p_ap_id;
366 DELETE FROM AMW_AP_EXT_TL WHERE audit_procedure_id = p_ap_id;
367
368 -- Attachment
369 FND_ATTACHED_DOCUMENTS2_PKG.delete_attachments('AMW_AUDIT_PRCD',
370 p_ap_id,
371 null,
372 null,
373 null,
374 null,
375 'N',
376 null);
377
378 -- Audit Procedure
379 FORALL i IN l_ap_rev_id_list.FIRST .. l_ap_rev_id_list.LAST
380 DELETE FROM AMW_AUDIT_PROCEDURES_TL WHERE audit_procedure_rev_id = l_ap_rev_id_list(i);
381
382 DELETE FROM AMW_AUDIT_PROCEDURES_B WHERE audit_procedure_id = p_ap_id;
383 DELETE FROM AMW_AP_ASSOCIATIONS WHERE audit_procedure_id = p_ap_id;
384
385 FND_FILE.put_line(fnd_file.log, 'Delete Audit Procedure : audit_procedure_id = '||p_ap_id);
386
387
388 IF (sql%notfound) then
389 RAISE no_data_found;
390 END IF;
391
392 END IF;
393
394 EXCEPTION
395 WHEN no_data_found THEN
396 NULL;
397 WHEN others THEN
398 NULL;
399 END Delete_Ap;
400
401
402 -- ===============================================================
403 -- Function name
404 -- Is_Record_Exist
405 -- Purpose
406 -- check if any records found for pass-in query
407 -- return BOOLEAN TRUE if at least one record is found;
408 -- return BOOLEAN FALSE otherwise.
409 -- Params
410 -- p_dynamic_sql := the sql needs to be checked,
411 -- can have variables defined(ie. :1 ...etc)
412 -- p_bind_value := default is Null.
413 -- this param is required if variables are defined in p_dynamic_sql param.
414 -- Notes
415 -- can only bind same value to the variables (:1) defined in p_dynamic_sql
416 -- ===============================================================
417 FUNCTION Is_Record_Exist(
418 p_dynamic_sql IN G_VARCHAR_VARRAY,
419 p_bind_value IN NUMBER := NULL
420 )
421 RETURN BOOLEAN
422 IS
426 rows_processed INTEGER;
423 l_Is_Exist BOOLEAN;
424
425 cursor_name INTEGER;
427
428 BEGIN
429 l_Is_Exist := TRUE;
430
431 IF (p_dynamic_sql IS NOT NULL) THEN
432 IF ((p_dynamic_sql(1) IS NOT NULL) AND (p_dynamic_sql(2) IS NOT NULL)) THEN
433 BEGIN
434 cursor_name := dbms_sql.open_cursor;
435 dbms_sql.parse(cursor_name, p_dynamic_sql(1), dbms_sql.NATIVE);
436
437 IF (p_dynamic_sql(2) = 'Y') THEN
438 dbms_sql.bind_variable(cursor_name, ':1', p_bind_value);
439 END IF; -- end of if: p_dynamic_sql(2) = 'Y'
440
441 rows_processed := dbms_sql.execute_and_fetch(cursor_name);
442 dbms_sql.close_cursor(cursor_name);
443 IF (rows_processed = 0) THEN
444 -- no rows found, thus it's not in use
445 l_Is_Exist := FALSE;
446 END IF;
447
448 EXCEPTION
449 WHEN OTHERS THEN
450 dbms_sql.close_cursor(cursor_name);
451 END;
452
453 END IF; -- end of if: p_dynamic_sql(1) IS NOT NULL
454
455 END IF; -- end of if: p_dynamic_sql IS NOT NULL
456
457 RETURN l_Is_Exist;
458
459 END Is_Record_Exist;
460
461
462 -- ===============================================================
463 -- Function name
464 -- Is_Object_In_Use
465 -- Purpose
466 -- check if any records found for pass-in query check list
467 -- return 'Y' if at least one record is found;
468 -- return 'N' otherwise.
469 -- Params
470 -- p_dynamic_sql_list := the sql list needs to be checked,
471 -- p_bind_value := default is Null.
472 -- ===============================================================
473 FUNCTION Is_Object_In_Use(
474 p_dynamic_sql_list IN G_VARRAY_TABLE,
475 p_bind_value IN NUMBER := NULL
476 )
477 RETURN VARCHAR
478 IS
479
480 l_Is_Object_In_Use VARCHAR2(1);
481
482 BEGIN
483 l_Is_Object_In_Use := 'N';
484
485 FOR i in p_dynamic_sql_list.FIRST .. p_dynamic_sql_list.LAST
486 LOOP
487
488 IF (Is_Record_Exist(p_dynamic_sql_list(i), p_bind_value) = TRUE) THEN
489 FND_FILE.put_line(fnd_file.log, 'object is in use : '||p_dynamic_sql_list(i)(1));
490 l_Is_Object_In_Use := 'Y';
491 EXIT;
492 END IF;
493 END LOOP;
494
495 RETURN l_Is_Object_In_Use;
496
497 EXCEPTION
498 WHEN others THEN
499 NULL;
500 END Is_Object_In_Use;
501
502
503 -- ----------------------------------------------------------------------
504
505 END AMW_DELETE_OBJECT_PVT;
[Home] [Help]