[Home] [Help]
PACKAGE BODY: APPS.AMW_AUDIT_PROCEDURES_PVT
Source
1 PACKAGE BODY AMW_AUDIT_PROCEDURES_PVT as
2 /* $Header: amwvrcdb.pls 120.1 2005/10/04 05:50:36 appldev noship $ */
3 -- ===============================================================
4 -- Package name
5 -- AMW_AUDIT_PROCEDURES_PVT
6 -- Purpose
7 -- for Import Audit Procedure : Load_AP (without knowing any audit_procedure_id in advance)
8 -- for direct call : Operate_AP (knowing audit_procedure_id or audit_procedure_rev_id)
9 -- History
10 -- 12/08/2003 tsho Creates
11 -- ===============================================================
12
13
14 G_PKG_NAME CONSTANT VARCHAR2(30) := 'AMW_AUDIT_PROCEDURES_PVT';
15 G_FILE_NAME CONSTANT VARCHAR2(12) := 'amwvrcdb.pls';
16
17
18 -- ===============================================================
19 -- Procedure name
20 -- Load_AP
21 -- Purpose
22 -- for Import Audit Procedure with approval_status 'A' or 'D'
23 -- ===============================================================
24 PROCEDURE Load_AP(
25 p_api_version_number IN NUMBER,
26 p_init_msg_list IN VARCHAR2,
27 p_commit IN VARCHAR2,
28 p_validation_level IN NUMBER,
29 x_return_status OUT NOCOPY VARCHAR2,
30 x_msg_count OUT NOCOPY NUMBER,
31 x_msg_data OUT NOCOPY VARCHAR2,
32 p_audit_procedure_rec IN audit_procedure_rec_type,
33 x_audit_procedure_rev_id OUT NOCOPY NUMBER,
34 x_audit_procedure_id OUT NOCOPY NUMBER,
35 p_approval_date IN DATE
36 )
37 IS
38 l_api_name CONSTANT VARCHAR2(30) := 'Load_AP';
39 l_dummy NUMBER;
40 l_dummy_audit_procedure_rec audit_procedure_rec_type := NULL;
41 l_approval_date DATE;
42
43 CURSOR c_name_exists (l_audit_procedure_name IN VARCHAR2) IS
44 SELECT audit_procedure_id
45 FROM amw_audit_procedures_vl
46 WHERE name = l_audit_procedure_name;
47 l_audit_procedure_id amw_audit_procedures_vl.audit_procedure_id%TYPE;
48
49 CURSOR c_revision_exists (l_audit_procedure_id IN NUMBER) IS
50 SELECT count(*)
51 FROM amw_audit_procedures_b
52 GROUP BY audit_procedure_id
53 HAVING audit_procedure_id=l_audit_procedure_id;
54
55 CURSOR c_approval_status (l_audit_procedure_id IN NUMBER) IS
56 SELECT audit_procedure_rev_id,
57 approval_status
58 FROM amw_audit_procedures_b
59 WHERE audit_procedure_id=l_audit_procedure_id AND
60 latest_revision_flag='Y';
61 l_approval_status c_approval_status%ROWTYPE;
62
63
64 BEGIN
65 AMW_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
66 x_return_status := G_RET_STS_SUCCESS;
67
68
69 IF p_audit_procedure_rec.approval_status ='P' THEN
70 AMW_UTILITY_PVT.Error_Message(p_message_name => 'AMW_INVALID_STATUS',
71 p_token_name => 'OBJ_TYPE',
72 p_token_value => G_OBJ_TYPE);
73 RAISE FND_API.G_EXC_ERROR;
74 ELSIF p_audit_procedure_rec.approval_status ='R' THEN
75 AMW_UTILITY_PVT.Error_Message(p_message_name => 'AMW_INVALID_STATUS',
76 p_token_name => 'OBJ_TYPE',
77 p_token_value => G_OBJ_TYPE);
78 RAISE FND_API.G_EXC_ERROR;
79 ELSIF p_audit_procedure_rec.approval_status IS NOT NULL AND p_audit_procedure_rec.approval_status <> 'A' AND p_audit_procedure_rec.approval_status <> 'D' THEN
80 -- if it's null, the default will be 'D' , other pass-in unwanted data will be Invalid
81 AMW_UTILITY_PVT.Error_Message(p_message_name => 'AMW_INVALID_STATUS',
82 p_token_name => 'OBJ_TYPE',
83 p_token_value => G_OBJ_TYPE);
84 RAISE FND_API.G_EXC_ERROR;
85 END IF;
86
87 l_approval_date := p_approval_date;
88 l_audit_procedure_id := NULL;
89 OPEN c_name_exists(p_audit_procedure_rec.audit_procedure_name);
90 FETCH c_name_exists INTO l_audit_procedure_id;
91 CLOSE c_name_exists;
92
93 if (p_approval_date is null)
94 then
95 l_approval_date := SYSDATE;
96 end if;
97
98 IF l_audit_procedure_id IS NULL THEN
99 -- no existing audit procedure with pass-in audit_procedure_name, then call operation with mode G_OP_CREATE
100 Operate_AP(
101 p_operate_mode => G_OP_CREATE,
102 p_api_version_number => p_api_version_number,
103 p_init_msg_list => p_init_msg_list,
104 p_commit => p_commit,
105 p_validation_level => p_validation_level,
106 x_return_status => x_return_status,
107 x_msg_count => x_msg_count,
108 x_msg_data => x_msg_data,
109 p_audit_procedure_rec => p_audit_procedure_rec,
110 x_audit_procedure_rev_id => x_audit_procedure_rev_id,
111 x_audit_procedure_id => x_audit_procedure_id,
112 p_approval_date => l_approval_date);
113 IF x_return_status<>G_RET_STS_SUCCESS THEN
114 AMW_UTILITY_PVT.Error_Message(p_message_name => 'AMW_EXE_ERROR',
115 p_token_name => 'OBJ_TYPE',
116 p_token_value => G_OBJ_TYPE);
117 RAISE FND_API.G_EXC_ERROR;
118 END IF;
119
120 ELSE
121 l_dummy_audit_procedure_rec := p_audit_procedure_rec;
122 l_dummy_audit_procedure_rec.audit_procedure_id := l_audit_procedure_id;
123 l_dummy := NULL;
124 OPEN c_revision_exists(l_audit_procedure_id);
125 FETCH c_revision_exists INTO l_dummy;
126 CLOSE c_revision_exists;
127
128 IF l_dummy IS NULL OR l_dummy < 1 THEN
129 -- no corresponding audit_procedure_id in AMW_AUDIT_PROCEDURES_B is wrong
130 AMW_UTILITY_PVT.Error_Message(p_message_name => 'AMW_UNEXPECT_ERROR',
131 p_token_name => 'OBJ_TYPE',
132 p_token_value => G_OBJ_TYPE);
133 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
134 ELSIF l_dummy = 1 THEN
135 -- has only one record for audit_procedure_id in AMW_AUDIT_PROCEDURES_B with pass-in name
136 OPEN c_approval_status(l_audit_procedure_id);
137 FETCH c_approval_status INTO l_approval_status;
138 CLOSE c_approval_status;
139
140 IF l_approval_status.approval_status='P' THEN
141 -- this record is Pending Approval, cannot do G_OP_UPDATE or G_OP_REVISE
142 AMW_UTILITY_PVT.Error_Message(p_message_name => 'AMW_PENDING_CHANGE_ERROR',
143 p_token_name => 'OBJ_TYPE',
144 p_token_value => G_OBJ_TYPE);
145 RAISE FND_API.G_EXC_ERROR;
146 ELSIF l_approval_status.approval_status='D' THEN
147 Operate_AP(
148 p_operate_mode => G_OP_UPDATE,
149 p_api_version_number => p_api_version_number,
150 p_init_msg_list => p_init_msg_list,
151 p_commit => p_commit,
152 p_validation_level => p_validation_level,
153 x_return_status => x_return_status,
154 x_msg_count => x_msg_count,
155 x_msg_data => x_msg_data,
156 p_audit_procedure_rec => l_dummy_audit_procedure_rec,
157 x_audit_procedure_rev_id => x_audit_procedure_rev_id,
158 x_audit_procedure_id => x_audit_procedure_id,
159 p_approval_date => l_approval_date);
160 IF x_return_status<>G_RET_STS_SUCCESS THEN
161 AMW_UTILITY_PVT.Error_Message(p_message_name => 'AMW_EXE_ERROR',
162 p_token_name => 'OBJ_TYPE',
163 p_token_value => G_OBJ_TYPE);
164 RAISE FND_API.G_EXC_ERROR;
165 END IF;
166
167 ELSIF l_approval_status.approval_status='A' OR l_approval_status.approval_status='R' THEN
168 Operate_AP(
169 p_operate_mode => G_OP_REVISE,
170 p_api_version_number => p_api_version_number,
171 p_init_msg_list => p_init_msg_list,
172 p_commit => p_commit,
173 p_validation_level => p_validation_level,
174 x_return_status => x_return_status,
175 x_msg_count => x_msg_count,
176 x_msg_data => x_msg_data,
177 p_audit_procedure_rec => l_dummy_audit_procedure_rec,
178 x_audit_procedure_rev_id => x_audit_procedure_rev_id,
179 x_audit_procedure_id => x_audit_procedure_id,
180 p_approval_date => l_approval_date);
181 IF x_return_status<>G_RET_STS_SUCCESS THEN
182 AMW_UTILITY_PVT.Error_Message(p_message_name => 'AMW_EXE_ERROR',
183 p_token_name => 'OBJ_TYPE',
184 p_token_value => G_OBJ_TYPE);
185 RAISE FND_API.G_EXC_ERROR;
186 END IF;
187
188 END IF; -- end of if:l_approval_status.approval_status
189 ELSE
190 -- l_dummy > 1 : has revised before
191 Operate_AP(
192 p_operate_mode => G_OP_REVISE,
193 p_api_version_number => p_api_version_number,
194 p_init_msg_list => p_init_msg_list,
195 p_commit => p_commit,
196 p_validation_level => p_validation_level,
197 x_return_status => x_return_status,
198 x_msg_count => x_msg_count,
199 x_msg_data => x_msg_data,
200 p_audit_procedure_rec => l_dummy_audit_procedure_rec,
201 x_audit_procedure_rev_id => x_audit_procedure_rev_id,
202 x_audit_procedure_id => x_audit_procedure_id,
203 p_approval_date => l_approval_date);
204 IF x_return_status<>G_RET_STS_SUCCESS THEN
205 AMW_UTILITY_PVT.Error_Message(p_message_name => 'AMW_EXE_ERROR',
206 p_token_name => 'OBJ_TYPE',
207 p_token_value => G_OBJ_TYPE);
208 RAISE FND_API.G_EXC_ERROR;
209 END IF;
210
211 END IF; -- end of if:l_dummy
212
213 END IF; -- end of if:l_audit_procedure_id
214
215 AMW_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
216
217 -- Standard call to get message count and if count is 1, get message info.
218 FND_MSG_PUB.Count_And_Get(
219 p_count => x_msg_count,
220 p_data => x_msg_data);
221
222 EXCEPTION
223
224 WHEN FND_API.G_EXC_ERROR THEN
225
226 x_return_status := G_RET_STS_ERROR;
227 -- Standard call to get message count and if count=1, get the message
228 FND_MSG_PUB.Count_And_Get (
229 p_encoded => G_FALSE,
230 p_count => x_msg_count,
231 p_data => x_msg_data);
232
233 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
234
235 x_return_status := G_RET_STS_UNEXP_ERROR;
236 -- Standard call to get message count and if count=1, get the message
237 FND_MSG_PUB.Count_And_Get (
238 p_encoded => G_FALSE,
239 p_count => x_msg_count,
240 p_data => x_msg_data);
241
242 WHEN OTHERS THEN
243
244 x_return_status := G_RET_STS_UNEXP_ERROR;
245 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
246 THEN
247 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
248 END IF;
249 -- Standard call to get message count and if count=1, get the message
250 FND_MSG_PUB.Count_And_Get (
251 p_encoded => G_FALSE,
252 p_count => x_msg_count,
253 p_data => x_msg_data);
254
255 END Load_AP;
256
257
258
259 -- ===============================================================
260 -- Procedure name
261 -- Operate_AP
262 -- Purpose
263 -- operate audit procedure depends on the pass-in p_operate_mode:
264 -- G_OP_CREATE
265 -- G_OP_UPDATE
266 -- G_OP_REVISE
267 -- G_OP_DELETE
268 -- Notes
269 -- the G_OP_UPDATE mode here is in business logic meaning,
270 -- not as the same as update in table handler meaning.
271 -- same goes to other p_operate_mode if it happens to
272 -- have similar name.
273 -- ===============================================================
274 PROCEDURE Operate_AP(
275 p_operate_mode IN VARCHAR2,
276 p_api_version_number IN NUMBER,
277 p_init_msg_list IN VARCHAR2,
278 p_commit IN VARCHAR2,
279 p_validation_level IN NUMBER,
280 x_return_status OUT NOCOPY VARCHAR2,
281 x_msg_count OUT NOCOPY NUMBER,
282 x_msg_data OUT NOCOPY VARCHAR2,
283 p_audit_procedure_rec IN audit_procedure_rec_type,
284 x_audit_procedure_rev_id OUT NOCOPY NUMBER,
285 x_audit_procedure_id OUT NOCOPY NUMBER,
286 p_approval_date IN DATE
287 )
288 IS
289 l_api_name CONSTANT VARCHAR2(30) := 'Operate_AP';
290 l_audit_procedure_rev_id NUMBER := NULL;
291 l_dummy_audit_procedure_rec audit_procedure_rec_type;
292
293 CURSOR c_draft_revision (l_audit_procedure_id IN NUMBER) IS
294 SELECT audit_procedure_rev_id
295 FROM amw_audit_procedures_b
296 WHERE audit_procedure_id = l_audit_procedure_id AND approval_status='D' AND latest_revision_flag='Y';
297
298 BEGIN
299 -- Initialize message list if p_init_msg_list is set to TRUE.
300 IF FND_API.to_Boolean( p_init_msg_list )
301 THEN
302 FND_MSG_PUB.initialize;
303 END IF;
304
305 AMW_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
306
307
308 IF p_operate_mode = G_OP_CREATE THEN
309 l_dummy_audit_procedure_rec := p_audit_procedure_rec;
310 l_dummy_audit_procedure_rec.object_version_number := 1;
311 l_dummy_audit_procedure_rec.audit_procedure_rev_num := 1;
312 l_dummy_audit_procedure_rec.latest_revision_flag := 'Y';
313
314 IF p_audit_procedure_rec.approval_status = 'A' THEN
315 l_dummy_audit_procedure_rec.approval_status := 'A';
316 l_dummy_audit_procedure_rec.curr_approved_flag := 'Y';
317 l_dummy_audit_procedure_rec.approval_date := p_approval_date;
318 ELSE
319 l_dummy_audit_procedure_rec.approval_status := 'D';
320 l_dummy_audit_procedure_rec.curr_approved_flag := 'N';
321 END IF;
322
323 Create_AP(
324 p_operate_mode => p_operate_mode,
325 p_api_version_number => p_api_version_number,
326 p_init_msg_list => p_init_msg_list,
327 p_commit => p_commit,
328 p_validation_level => p_validation_level,
329 x_return_status => x_return_status,
330 x_msg_count => x_msg_count,
331 x_msg_data => x_msg_data,
332 p_audit_procedure_rec => l_dummy_audit_procedure_rec,
333 x_audit_procedure_rev_id => x_audit_procedure_rev_id,
334 x_audit_procedure_id => x_audit_procedure_id);
335
336 IF x_return_status<>G_RET_STS_SUCCESS THEN
337 AMW_UTILITY_PVT.Error_Message(p_message_name => 'AMW_EXE_ERROR',
338 p_token_name => 'OBJ_TYPE',
339 p_token_value => G_OBJ_TYPE);
340 RAISE FND_API.G_EXC_ERROR;
341 END IF;
342
343 ELSIF p_operate_mode = G_OP_UPDATE THEN
344 l_dummy_audit_procedure_rec := p_audit_procedure_rec;
345 l_dummy_audit_procedure_rec.curr_approved_flag := 'N';
346 l_dummy_audit_procedure_rec.latest_revision_flag := 'Y';
347
348 IF p_audit_procedure_rec.approval_status = 'A' THEN
349 l_dummy_audit_procedure_rec.approval_status := 'A';
350 l_dummy_audit_procedure_rec.curr_approved_flag := 'Y';
351 l_dummy_audit_procedure_rec.approval_date := p_approval_date;
352 ELSE
353 l_dummy_audit_procedure_rec.approval_status := 'D';
354 l_dummy_audit_procedure_rec.curr_approved_flag := 'N';
355 END IF;
356
357
358 fnd_file.put_line (fnd_file.LOG, '&&&&&&&&&&&&&&& Going to Update &&&&&&&&&&&&&&&');
359 Update_AP(
360 p_operate_mode => p_operate_mode,
361 p_api_version_number => p_api_version_number,
362 p_init_msg_list => p_init_msg_list,
363 p_commit => p_commit,
364 p_validation_level => p_validation_level,
365 x_return_status => x_return_status,
366 x_msg_count => x_msg_count,
367 x_msg_data => x_msg_data,
368 p_audit_procedure_rec => l_dummy_audit_procedure_rec,
369 x_audit_procedure_rev_id => x_audit_procedure_rev_id,
370 x_audit_procedure_id => x_audit_procedure_id);
371
372 fnd_file.put_line (fnd_file.LOG, '&&&&&&&&&&&&&&& Came out of Update &&&&&&&&&&&&&&&');
373 IF x_return_status<>G_RET_STS_SUCCESS THEN
374 AMW_UTILITY_PVT.Error_Message(p_message_name => 'AMW_EXE_ERROR',
375 p_token_name => 'OBJ_TYPE',
376 p_token_value => G_OBJ_TYPE);
377 RAISE FND_API.G_EXC_ERROR;
378 END IF;
379
380 ELSIF p_operate_mode = G_OP_REVISE THEN
381 l_audit_procedure_rev_id := NULL;
382 OPEN c_draft_revision(p_audit_procedure_rec.audit_procedure_id);
383 FETCH c_draft_revision INTO l_audit_procedure_rev_id;
384 CLOSE c_draft_revision;
385
386 -- has revision with APPROVAL_STATUS='D' exists
387 IF l_audit_procedure_rev_id IS NOT NULL THEN
388 l_dummy_audit_procedure_rec := p_audit_procedure_rec;
389 l_dummy_audit_procedure_rec.latest_revision_flag := 'Y';
390
391 IF p_audit_procedure_rec.approval_status = 'A' THEN
392 l_dummy_audit_procedure_rec.approval_status := 'A';
393 l_dummy_audit_procedure_rec.curr_approved_flag := 'Y';
394 l_dummy_audit_procedure_rec.approval_date := p_approval_date;
395 ELSE
396 l_dummy_audit_procedure_rec.approval_status := 'D';
397 l_dummy_audit_procedure_rec.curr_approved_flag := 'N';
398 END IF;
399
400
401 Update_AP(
402 p_operate_mode => p_operate_mode,
403 p_api_version_number => p_api_version_number,
404 p_init_msg_list => p_init_msg_list,
405 p_commit => p_commit,
406 p_validation_level => p_validation_level,
407 x_return_status => x_return_status,
408 x_msg_count => x_msg_count,
409 x_msg_data => x_msg_data,
410 p_audit_procedure_rec => l_dummy_audit_procedure_rec,
411 x_audit_procedure_rev_id => x_audit_procedure_rev_id,
412 x_audit_procedure_id => x_audit_procedure_id);
413
414
415 IF x_return_status<>G_RET_STS_SUCCESS THEN
416 AMW_UTILITY_PVT.Error_Message(p_message_name => 'AMW_EXE_ERROR',
417 p_token_name => 'OBJ_TYPE',
418 p_token_value => G_OBJ_TYPE);
419 RAISE FND_API.G_EXC_ERROR;
420 END IF;
421
422
423 ELSE
424 l_dummy_audit_procedure_rec := p_audit_procedure_rec;
425
426
427 Revise_Without_Revision_Exists(
428 p_operate_mode => p_operate_mode,
429 p_api_version_number => p_api_version_number,
430 p_init_msg_list => p_init_msg_list,
431 p_commit => p_commit,
432 p_validation_level => p_validation_level,
433 x_return_status => x_return_status,
434 x_msg_count => x_msg_count,
435 x_msg_data => x_msg_data,
436 p_audit_procedure_rec => l_dummy_audit_procedure_rec,
437 x_audit_procedure_rev_id => x_audit_procedure_rev_id,
438 x_audit_procedure_id => x_audit_procedure_id);
439
440 IF x_return_status<>G_RET_STS_SUCCESS THEN
441 AMW_UTILITY_PVT.Error_Message(p_message_name => 'AMW_EXE_ERROR',
442 p_token_name => 'OBJ_TYPE',
443 p_token_value => G_OBJ_TYPE);
444 RAISE FND_API.G_EXC_ERROR;
445 END IF;
446
447 END IF;
448 ELSIF p_operate_mode = G_OP_DELETE THEN
449
450 Delete_AP(
451 p_operate_mode => p_operate_mode,
452 p_api_version_number => p_api_version_number,
453 p_init_msg_list => p_init_msg_list,
454 p_commit => p_commit,
455 p_validation_level => p_validation_level,
456 x_return_status => x_return_status,
457 x_msg_count => x_msg_count,
458 x_msg_data => x_msg_data,
459 p_audit_procedure_rev_id => p_audit_procedure_rec.audit_procedure_rev_id,
460 x_audit_procedure_id => x_audit_procedure_id);
461
462 IF x_return_status<>G_RET_STS_SUCCESS THEN
463 AMW_UTILITY_PVT.Error_Message(p_message_name => 'AMW_EXE_ERROR',
464 p_token_name => 'OBJ_TYPE',
465 p_token_value => G_OBJ_TYPE);
466 RAISE FND_API.G_EXC_ERROR;
467 END IF;
468
469 ELSE
470 AMW_UTILITY_PVT.Error_Message(p_message_name => 'AMW_UNEXPECT_ERROR',
471 p_token_name => 'OBJ_TYPE',
472 p_token_value => G_OBJ_TYPE);
473 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
474 END IF;
475
476
477 AMW_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
478
479 -- Standard call to get message count and if count is 1, get message info.
480 FND_MSG_PUB.Count_And_Get
481 (p_count => x_msg_count,
482 p_data => x_msg_data);
483
484 EXCEPTION
485
486 WHEN FND_API.G_EXC_ERROR THEN
487 x_return_status := G_RET_STS_ERROR;
488 -- Standard call to get message count and if count=1, get the message
489 FND_MSG_PUB.Count_And_Get (
490 p_encoded => G_FALSE,
491 p_count => x_msg_count,
492 p_data => x_msg_data
493 );
494
495 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
496 x_return_status := G_RET_STS_UNEXP_ERROR;
497 -- Standard call to get message count and if count=1, get the message
498 FND_MSG_PUB.Count_And_Get (
499 p_encoded => G_FALSE,
500 p_count => x_msg_count,
501 p_data => x_msg_data);
502
503 WHEN OTHERS THEN
504
505 x_return_status := G_RET_STS_UNEXP_ERROR;
506 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
507 THEN
508 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
509 END IF;
510 -- Standard call to get message count and if count=1, get the message
511 FND_MSG_PUB.Count_And_Get (
512 p_encoded => G_FALSE,
513 p_count => x_msg_count,
514 p_data => x_msg_data);
515
516 END Operate_AP;
517
518
519
520
521 -- ===============================================================
522 -- Procedure name
523 -- Create_AP
524 -- Purpose
525 -- create audit procedure with specified approval_status,
526 -- if no specified approval_status in pass-in p_audit_procedure_rec,
527 -- the default approval_status is set to 'D'.
528 -- ===============================================================
529 PROCEDURE Create_AP(
530 p_operate_mode IN VARCHAR2,
531 p_api_version_number IN NUMBER,
532 p_init_msg_list IN VARCHAR2,
533 p_commit IN VARCHAR2,
534 p_validation_level IN NUMBER,
535
536 x_return_status OUT NOCOPY VARCHAR2,
537 x_msg_count OUT NOCOPY NUMBER,
538 x_msg_data OUT NOCOPY VARCHAR2,
539
540 p_audit_procedure_rec IN audit_procedure_rec_type,
541 x_audit_procedure_rev_id OUT NOCOPY NUMBER,
542 x_audit_procedure_id OUT NOCOPY NUMBER
543 )
544 IS
545 L_API_NAME CONSTANT VARCHAR2(30) := 'Create_AP';
546 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
547 l_return_status_full VARCHAR2(1);
548 l_object_version_number NUMBER := 1;
549 l_audit_procedure_id NUMBER;
550 l_audit_procedure_rev_id NUMBER;
551 l_dummy NUMBER;
552 l_audit_procedure_rec audit_procedure_rec_type;
553 l_dummy_audit_procedure_rec audit_procedure_rec_type;
554 l_row_id amw_audit_procedures_vl.row_id%TYPE;
555
556 CURSOR c_rev_id IS
557 SELECT amw_procedure_rev_s.nextval
558 FROM dual;
559
560 CURSOR c_rev_id_exists (l_rev_id IN NUMBER) IS
561 SELECT 1
562 FROM amw_audit_procedures_b
563 WHERE audit_procedure_rev_id = l_rev_id;
564
565 CURSOR c_id IS
566 SELECT amw_procedures_s.nextval
567 FROM dual;
568
569 CURSOR c_id_exists (l_id IN NUMBER) IS
570 SELECT 1
571 FROM amw_audit_procedures_b
572 WHERE audit_procedure_id = l_id;
573
574 BEGIN
575 -- Standard Start of API savepoint
576 SAVEPOINT CREATE_AP_PVT;
577
578 -- Standard call to check for call compatibility.
579 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
580 p_api_version_number,
581 l_api_name,
582 G_PKG_NAME)
583 THEN
584 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
585 END IF;
586
587 -- Initialize message list if p_init_msg_list is set to TRUE.
588 IF FND_API.to_Boolean( p_init_msg_list )
589 THEN
590 FND_MSG_PUB.initialize;
591 END IF;
592
593 AMW_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
594
595 AMW_UTILITY_PVT.debug_message('p_operate_mode: ' || p_operate_mode);
596 -- Initialize API return status to SUCCESS
597 x_return_status := G_RET_STS_SUCCESS;
598
599 IF p_audit_procedure_rec.audit_procedure_rev_id IS NULL OR p_audit_procedure_rec.audit_procedure_rev_id = FND_API.g_miss_num THEN
600 LOOP
601 l_dummy := NULL;
602 OPEN c_rev_id;
603 FETCH c_rev_id INTO l_audit_procedure_rev_id;
604 CLOSE c_rev_id;
605
606 OPEN c_rev_id_exists(l_audit_procedure_rev_id);
607 FETCH c_rev_id_exists INTO l_dummy;
608 CLOSE c_rev_id_exists;
609 EXIT WHEN l_dummy IS NULL;
610 END LOOP;
611 ELSE
612 l_audit_procedure_rev_id := p_audit_procedure_rec.audit_procedure_rev_id;
613 END IF;
614
615 IF p_audit_procedure_rec.audit_procedure_id IS NULL OR p_audit_procedure_rec.audit_procedure_id = FND_API.g_miss_num THEN
616 LOOP
617 l_dummy := NULL;
618 OPEN c_id;
619 FETCH c_id INTO l_audit_procedure_id;
620 CLOSE c_id;
621
622 OPEN c_id_exists(l_audit_procedure_id);
623 FETCH c_id_exists INTO l_dummy;
624 CLOSE c_id_exists;
625 EXIT WHEN l_dummy IS NULL;
626 END LOOP;
627 ELSE
628 l_audit_procedure_id := p_audit_procedure_rec.audit_procedure_id;
629 END IF;
630
631 x_audit_procedure_id := l_audit_procedure_id;
632 x_audit_procedure_rev_id := l_audit_procedure_rev_id;
633
634 l_audit_procedure_rec := p_audit_procedure_rec;
635 l_audit_procedure_rec.audit_procedure_id := l_audit_procedure_id;
636 l_audit_procedure_rec.audit_procedure_rev_id := l_audit_procedure_rev_id;
637
638
639 IF FND_GLOBAL.User_Id IS NULL THEN
640 AMW_UTILITY_PVT.Error_Message(p_message_name => 'USER_PROFILE_MISSING');
641 RAISE FND_API.G_EXC_ERROR;
642 END IF;
643
644 IF (P_validation_level >= G_VALID_LEVEL_FULL) THEN
645 AMW_UTILITY_PVT.debug_message('Private API: Validate_AP');
646
647 -- Invoke validation procedures
648 Validate_AP(
649 p_operate_mode => p_operate_mode,
650 p_api_version_number => p_api_version_number,
651 p_init_msg_list => G_FALSE,
652 p_validation_level => p_validation_level,
653 p_audit_procedure_rec => l_audit_procedure_rec,
654 x_audit_procedure_rec => l_dummy_audit_procedure_rec,
655 x_return_status => x_return_status,
656 x_msg_count => x_msg_count,
657 x_msg_data => x_msg_data);
658 END IF;
659
660 IF x_return_status<>G_RET_STS_SUCCESS THEN
661 AMW_UTILITY_PVT.Error_Message(p_message_name => 'AMW_EXE_ERROR',
662 p_token_name => 'OBJ_TYPE',
663 p_token_value => G_OBJ_TYPE);
664 RAISE FND_API.G_EXC_ERROR;
665 END IF;
666
667
668 AMW_UTILITY_PVT.debug_message( 'Private API: Calling create table handler');
669
670 -- Invoke table handler(AMW_AUDIT_PROCEDURES_PKG.Insert_Row)
671 AMW_UTILITY_PVT.debug_message( 'Private API: Calling AMW_AUDIT_PROCEDURES_PKG.Insert_Row');
672 AMW_AUDIT_PROCEDURES_PKG.Insert_Row(
673 x_rowid => l_row_id,
674 x_audit_procedure_rev_id => l_dummy_audit_procedure_rec.audit_procedure_rev_id,
675 x_project_id => l_dummy_audit_procedure_rec.project_id,
676 x_classification => l_dummy_audit_procedure_rec.classification,
677 x_attribute10 => l_dummy_audit_procedure_rec.attribute10,
678 x_attribute11 => l_dummy_audit_procedure_rec.attribute11,
679 x_attribute12 => l_dummy_audit_procedure_rec.attribute12,
680 x_attribute13 => l_dummy_audit_procedure_rec.attribute13,
681 x_attribute14 => l_dummy_audit_procedure_rec.attribute14,
682 x_attribute15 => l_dummy_audit_procedure_rec.attribute15,
683 x_object_version_number => l_object_version_number,
684 x_approval_status => l_dummy_audit_procedure_rec.approval_status,
685 x_orig_system_reference => l_dummy_audit_procedure_rec.orig_system_reference,
686 x_requestor_id => l_dummy_audit_procedure_rec.requestor_id,
687 x_attribute6 => l_dummy_audit_procedure_rec.attribute6,
688 x_attribute7 => l_dummy_audit_procedure_rec.attribute7,
689 x_attribute8 => l_dummy_audit_procedure_rec.attribute8,
690 x_attribute9 => l_dummy_audit_procedure_rec.attribute9,
691 x_security_group_id => l_dummy_audit_procedure_rec.security_group_id,
692 x_audit_procedure_id => l_dummy_audit_procedure_rec.audit_procedure_id,
693 x_audit_procedure_rev_num => l_dummy_audit_procedure_rec.audit_procedure_rev_num,
694 x_end_date => l_dummy_audit_procedure_rec.end_date,
695 x_approval_date => l_dummy_audit_procedure_rec.approval_date,
696 x_curr_approved_flag => l_dummy_audit_procedure_rec.curr_approved_flag,
697 x_latest_revision_flag => l_dummy_audit_procedure_rec.latest_revision_flag,
698 x_attribute5 => l_dummy_audit_procedure_rec.attribute5,
699 x_attribute_category => l_dummy_audit_procedure_rec.attribute_category,
700 x_attribute1 => l_dummy_audit_procedure_rec.attribute1,
701 x_attribute2 => l_dummy_audit_procedure_rec.attribute2,
702 x_attribute3 => l_dummy_audit_procedure_rec.attribute3,
703 x_attribute4 => l_dummy_audit_procedure_rec.attribute4,
704 x_name => l_dummy_audit_procedure_rec.audit_procedure_name,
705 x_description => l_dummy_audit_procedure_rec.audit_procedure_description,
706 x_creation_date => SYSDATE,
707 x_created_by => G_USER_ID,
708 x_last_update_date => SYSDATE,
709 x_last_updated_by => G_USER_ID,
710 x_last_update_login => G_LOGIN_ID);
711
712 IF x_return_status <> G_RET_STS_SUCCESS THEN
713 AMW_UTILITY_PVT.Error_Message(p_message_name => 'AMW_EXE_ERROR',
714 p_token_name => 'OBJ_TYPE',
715 p_token_value => G_OBJ_TYPE);
716 RAISE FND_API.G_EXC_ERROR;
717 END IF;
718
719 -- Standard check for p_commit
720 IF FND_API.to_Boolean( p_commit )
721 THEN
722 COMMIT WORK;
723 END IF;
724
725
726 AMW_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
727
728 -- Standard call to get message count and if count is 1, get message info.
729 FND_MSG_PUB.Count_And_Get
730 (p_count => x_msg_count,
731 p_data => x_msg_data);
732
733 EXCEPTION
734 WHEN AMW_UTILITY_PVT.resource_locked THEN
735 x_return_status := G_RET_STS_ERROR;
736 AMW_UTILITY_PVT.Error_Message(p_message_name => 'AMW_API_RESOURCE_LOCKED');
737
738 WHEN FND_API.G_EXC_ERROR THEN
739 ROLLBACK TO CREATE_AP_PVT;
740 x_return_status := G_RET_STS_ERROR;
741 -- Standard call to get message count and if count=1, get the message
742 FND_MSG_PUB.Count_And_Get (
743 p_encoded => G_FALSE,
744 p_count => x_msg_count,
745 p_data => x_msg_data);
746
747 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
748 ROLLBACK TO CREATE_AP_PVT;
749 x_return_status := G_RET_STS_UNEXP_ERROR;
750 -- Standard call to get message count and if count=1, get the message
751 FND_MSG_PUB.Count_And_Get (
752 p_encoded => G_FALSE,
753 p_count => x_msg_count,
754 p_data => x_msg_data);
755
756 WHEN OTHERS THEN
757 ROLLBACK TO CREATE_AP_PVT;
758 x_return_status := G_RET_STS_UNEXP_ERROR;
759 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
760 THEN
761 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
762 END IF;
763 -- Standard call to get message count and if count=1, get the message
764 FND_MSG_PUB.Count_And_Get (
765 p_encoded => G_FALSE,
766 p_count => x_msg_count,
767 p_data => x_msg_data);
768
769 End Create_AP;
770
771
772
773 -- ===============================================================
774 -- Procedure name
775 -- Update_AP
776 -- Purpose
777 -- update audit procedure with specified audit_procedure_rev_id,
778 -- if no specified audit_procedure_rev_id in pass-in p_audit_procedure_rec,
779 -- this will update the one with specified audit_procedure_id having
780 -- latest_revision_flag='Y' AND approval_status='D'.
781 -- Notes
782 -- if audit_procedure_rev_id is not specified, then
783 -- audit_procedure_id is a must when calling Update_AP
784 -- ===============================================================
785 PROCEDURE Update_AP(
786 p_operate_mode IN VARCHAR2,
787 p_api_version_number IN NUMBER,
788 p_init_msg_list IN VARCHAR2,
789 p_commit IN VARCHAR2,
790 p_validation_level IN NUMBER,
791
792 x_return_status OUT NOCOPY VARCHAR2,
793 x_msg_count OUT NOCOPY NUMBER,
794 x_msg_data OUT NOCOPY VARCHAR2,
795
796 p_audit_procedure_rec IN audit_procedure_rec_type,
797 x_audit_procedure_rev_id OUT NOCOPY NUMBER,
798 x_audit_procedure_id OUT NOCOPY NUMBER
799 )
800 IS
801 l_api_name CONSTANT VARCHAR2(30) := 'Update_AP';
802 l_api_version_number CONSTANT NUMBER := 1.0;
803 l_audit_procedure_rev_id NUMBER;
804 l_audit_procedure_rec audit_procedure_rec_type;
805 l_dummy_audit_procedure_rec audit_procedure_rec_type;
806 l_classification number;
807
808 CURSOR c_target_revision (l_audit_procedure_id IN NUMBER) IS
809 SELECT audit_procedure_rev_id
810 FROM amw_audit_procedures_b
811 WHERE audit_procedure_id = l_audit_procedure_id AND approval_status='D' AND latest_revision_flag='Y';
812
813 BEGIN
814 -- Standard Start of API savepoint
815 SAVEPOINT UPDATE_AP_PVT;
816
817
818 -- Standard call to check for call compatibility.
819 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
820 p_api_version_number,
821 l_api_name,
822 G_PKG_NAME)
823 THEN
824 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
825 END IF;
826
827 -- Initialize message list if p_init_msg_list is set to TRUE.
828 IF FND_API.to_Boolean( p_init_msg_list )
829 THEN
830 FND_MSG_PUB.initialize;
831 END IF;
832
833 AMW_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
834
835
836 -- Initialize API return status to SUCCESS
837 x_return_status := G_RET_STS_SUCCESS;
838
839 AMW_UTILITY_PVT.debug_message('Private API: - Open Cursor to Select');
840
841
842 -- if no specified target audit_procedure_rev_id, find if from audit_procedure_id
843 IF p_audit_procedure_rec.audit_procedure_rev_id IS NULL OR p_audit_procedure_rec.audit_procedure_rev_id = FND_API.g_miss_num THEN
844 l_audit_procedure_rev_id := NULL;
845 OPEN c_target_revision(p_audit_procedure_rec.audit_procedure_id);
846 FETCH c_target_revision INTO l_audit_procedure_rev_id;
847 CLOSE c_target_revision;
848 IF l_audit_procedure_rev_id IS NULL THEN
849 x_return_status := G_RET_STS_ERROR;
850 AMW_UTILITY_PVT.debug_message('l_audit_procedure_rev_id in Update_AP is NULL');
851 RAISE FND_API.G_EXC_ERROR;
852 END IF;
853 ELSE
854 l_audit_procedure_rev_id := p_audit_procedure_rec.audit_procedure_rev_id;
855 END IF; -- end of if:p_audit_procedure_rec.audit_procedure_rev_id
856
857
858 AMW_UTILITY_PVT.debug_message('l_audit_procedure_rev_id:'||l_audit_procedure_rev_id);
859
860 x_audit_procedure_id := p_audit_procedure_rec.audit_procedure_id;
861 x_audit_procedure_rev_id := l_audit_procedure_rev_id;
862
863 l_audit_procedure_rec := p_audit_procedure_rec;
864 l_audit_procedure_rec.audit_procedure_rev_id := l_audit_procedure_rev_id;
865
866
867 IF ( P_validation_level >= G_VALID_LEVEL_FULL)
868 THEN
869 AMW_UTILITY_PVT.debug_message('Private API: Validate_AP');
870
871 -- Invoke validation procedures
872 Validate_AP(
873 p_operate_mode => p_operate_mode,
874 p_api_version_number => p_api_version_number,
875 p_init_msg_list => G_FALSE,
876 p_validation_level => p_validation_level,
877 p_audit_procedure_rec => l_audit_procedure_rec,
878 x_audit_procedure_rec => l_dummy_audit_procedure_rec,
879 x_return_status => x_return_status,
880 x_msg_count => x_msg_count,
881 x_msg_data => x_msg_data);
882 END IF;
883
884
885 IF x_return_status<>G_RET_STS_SUCCESS THEN
886 AMW_UTILITY_PVT.Error_Message(p_message_name => 'AMW_EXE_ERROR',
887 p_token_name => 'OBJ_TYPE',
888 p_token_value => G_OBJ_TYPE);
889 RAISE FND_API.G_EXC_ERROR;
890 END IF;
891
892
893 -- check if the AP has a classification already
894 begin
895 SELECT classification
896 INTO l_classification
897 FROM amw_audit_procedures_b
898 ----03.01.2005 npanandi: ERROR in below JOIN
899 ----audit_procedure_id should be equated to l_dummy_audit_procedure_rec.audit_procedure_id;
900 ----WHERE audit_procedure_id = l_dummy_audit_procedure_rec.audit_procedure_rev_id;
901 WHERE audit_procedure_id = l_dummy_audit_procedure_rec.audit_procedure_id;
902 exception
903 when no_data_found then
904 null;
905 when others then
906 null;
907 end;
908
909
910 IF l_classification IS NOT NULL
911 THEN
912 l_dummy_audit_procedure_rec.classification := l_classification;
913 END IF;
914
915
916 -- Invoke table handler(AMW_AUDIT_PROCEDURES_PKG.Update_Row)
917 AMW_AUDIT_PROCEDURES_PKG.Update_Row(
918 x_audit_procedure_rev_id => l_dummy_audit_procedure_rec.audit_procedure_rev_id,
919 x_project_id => l_dummy_audit_procedure_rec.project_id,
920 x_classification => l_dummy_audit_procedure_rec.classification,
921 x_attribute10 => l_dummy_audit_procedure_rec.attribute10,
922 x_attribute11 => l_dummy_audit_procedure_rec.attribute11,
923 x_attribute12 => l_dummy_audit_procedure_rec.attribute12,
924 x_attribute13 => l_dummy_audit_procedure_rec.attribute13,
925 x_attribute14 => l_dummy_audit_procedure_rec.attribute14,
926 x_attribute15 => l_dummy_audit_procedure_rec.attribute15,
927 x_object_version_number => l_dummy_audit_procedure_rec.object_version_number,
928 x_approval_status => l_dummy_audit_procedure_rec.approval_status,
929 x_orig_system_reference => l_dummy_audit_procedure_rec.orig_system_reference,
930 x_requestor_id => l_dummy_audit_procedure_rec.requestor_id,
931 x_attribute6 => l_dummy_audit_procedure_rec.attribute6,
932 x_attribute7 => l_dummy_audit_procedure_rec.attribute7,
933 x_attribute8 => l_dummy_audit_procedure_rec.attribute8,
934 x_attribute9 => l_dummy_audit_procedure_rec.attribute9,
935 x_security_group_id => l_dummy_audit_procedure_rec.security_group_id,
936 x_audit_procedure_id => l_dummy_audit_procedure_rec.audit_procedure_id,
937 x_audit_procedure_rev_num => l_dummy_audit_procedure_rec.audit_procedure_rev_num,
938 x_end_date => l_dummy_audit_procedure_rec.end_date,
939 x_approval_date => l_dummy_audit_procedure_rec.approval_date,
940 x_curr_approved_flag => l_dummy_audit_procedure_rec.curr_approved_flag,
941 x_latest_revision_flag => l_dummy_audit_procedure_rec.latest_revision_flag,
942 x_attribute5 => l_dummy_audit_procedure_rec.attribute5,
943 x_attribute_category => l_dummy_audit_procedure_rec.attribute_category,
944 x_attribute1 => l_dummy_audit_procedure_rec.attribute1,
945 x_attribute2 => l_dummy_audit_procedure_rec.attribute2,
946 x_attribute3 => l_dummy_audit_procedure_rec.attribute3,
947 x_attribute4 => l_dummy_audit_procedure_rec.attribute4,
948 x_name => l_dummy_audit_procedure_rec.audit_procedure_name,
949 x_description => l_dummy_audit_procedure_rec.audit_procedure_description,
950 x_last_update_date => SYSDATE,
951 x_last_updated_by => G_USER_ID,
952 x_last_update_login => G_LOGIN_ID);
953
954
955 -- anmalhot - if approval status = 'A' then approve the control associations
956 if(l_dummy_audit_procedure_rec.approval_status = 'A')
957 then
958 UPDATE amw_ap_associations
959 SET approval_date = l_dummy_audit_procedure_rec.approval_date
960 WHERE audit_procedure_id = l_dummy_audit_procedure_rec.audit_procedure_id
961 AND object_type = 'CTRL';
962 end if;
963
964
965 -- Standard check for p_commit
966 IF FND_API.to_Boolean( p_commit )
967 THEN
968 COMMIT WORK;
969 END IF;
970
971
972 AMW_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
973
974 -- Standard call to get message count and if count is 1, get message info.
975 FND_MSG_PUB.Count_And_Get
976 (p_count => x_msg_count,
977 p_data => x_msg_data);
978
979
980 EXCEPTION
981
982 WHEN AMW_UTILITY_PVT.resource_locked THEN
983 x_return_status := G_RET_STS_ERROR;
984 AMW_UTILITY_PVT.Error_Message(p_message_name => 'AMW_API_RESOURCE_LOCKED');
985
986 WHEN FND_API.G_EXC_ERROR THEN
987 ROLLBACK TO UPDATE_AP_PVT;
988 x_return_status := G_RET_STS_ERROR;
989 -- Standard call to get message count and if count=1, get the message
990 FND_MSG_PUB.Count_And_Get (
991 p_encoded => G_FALSE,
992 p_count => x_msg_count,
993 p_data => x_msg_data);
994
995 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
996 ROLLBACK TO UPDATE_AP_PVT;
997 x_return_status := G_RET_STS_UNEXP_ERROR;
998 -- Standard call to get message count and if count=1, get the message
999 FND_MSG_PUB.Count_And_Get (
1000 p_encoded => G_FALSE,
1001 p_count => x_msg_count,
1002 p_data => x_msg_data);
1003
1004 WHEN OTHERS THEN
1005 ROLLBACK TO UPDATE_AP_PVT;
1006 x_return_status := G_RET_STS_UNEXP_ERROR;
1007 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1008 THEN
1009 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1010 END IF;
1011 -- Standard call to get message count and if count=1, get the message
1012 FND_MSG_PUB.Count_And_Get (
1013 p_encoded => G_FALSE,
1014 p_count => x_msg_count,
1015 p_data => x_msg_data);
1016
1017 End Update_AP;
1018
1019
1020
1021
1022 -- ===============================================================
1023 -- Procedure name
1024 -- Delete_AP
1025 -- Purpose
1026 -- delete audit procedure with specified audit_procedure_rev_id.
1027 -- ===============================================================
1028 PROCEDURE Delete_AP(
1029 p_operate_mode IN VARCHAR2,
1030 p_api_version_number IN NUMBER,
1031 p_init_msg_list IN VARCHAR2,
1032 p_commit IN VARCHAR2,
1033 p_validation_level IN NUMBER,
1034 x_return_status OUT NOCOPY VARCHAR2,
1035 x_msg_count OUT NOCOPY NUMBER,
1036 x_msg_data OUT NOCOPY VARCHAR2,
1037 p_audit_procedure_rev_id IN NUMBER,
1038 x_audit_procedure_id OUT NOCOPY NUMBER
1039 )
1040 IS
1041 L_API_NAME CONSTANT VARCHAR2(30) := 'Delete_AP';
1042 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
1043
1044 BEGIN
1045 -- Standard Start of API savepoint
1046 SAVEPOINT DELETE_AP_PVT;
1047
1048 -- Standard call to check for call compatibility.
1049 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1050 p_api_version_number,
1051 l_api_name,
1052 G_PKG_NAME)
1053 THEN
1054 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1055 END IF;
1056
1057 -- Initialize message list if p_init_msg_list is set to TRUE.
1058 IF FND_API.to_Boolean( p_init_msg_list )
1059 THEN
1060 FND_MSG_PUB.initialize;
1061 END IF;
1062
1063 AMW_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
1064
1065
1066 -- Initialize API return status to SUCCESS
1067 x_return_status := G_RET_STS_SUCCESS;
1068
1069 AMW_UTILITY_PVT.debug_message( 'Private API: Calling delete table handler');
1070
1071 -- Invoke table handler(AMW_AUDIT_PROCEDURES_PKG.Delete_Row)
1072 AMW_AUDIT_PROCEDURES_PKG.Delete_Row(
1073 x_audit_procedure_rev_id => p_audit_procedure_rev_id);
1074
1075
1076 -- Standard check for p_commit
1077 IF FND_API.to_Boolean( p_commit )
1078 THEN
1079 COMMIT WORK;
1080 END IF;
1081
1082
1083 AMW_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
1084
1085 -- Standard call to get message count and if count is 1, get message info.
1086 FND_MSG_PUB.Count_And_Get
1087 (p_count => x_msg_count,
1088 p_data => x_msg_data);
1089
1090 EXCEPTION
1091
1092 WHEN AMW_UTILITY_PVT.resource_locked THEN
1093 x_return_status := G_RET_STS_ERROR;
1094 AMW_UTILITY_PVT.Error_Message(p_message_name => 'AMW_API_RESOURCE_LOCKED');
1095
1096 WHEN FND_API.G_EXC_ERROR THEN
1097 ROLLBACK TO DELETE_AP_PVT;
1098 x_return_status := G_RET_STS_ERROR;
1099 -- Standard call to get message count and if count=1, get the message
1100 FND_MSG_PUB.Count_And_Get (
1101 p_encoded => G_FALSE,
1102 p_count => x_msg_count,
1103 p_data => x_msg_data);
1104
1105 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1106 ROLLBACK TO DELETE_AP_PVT;
1107 x_return_status := G_RET_STS_UNEXP_ERROR;
1108 -- Standard call to get message count and if count=1, get the message
1109 FND_MSG_PUB.Count_And_Get (
1110 p_encoded => G_FALSE,
1111 p_count => x_msg_count,
1112 p_data => x_msg_data);
1113
1114 WHEN OTHERS THEN
1115 ROLLBACK TO DELETE_AP_PVT;
1116 x_return_status := G_RET_STS_UNEXP_ERROR;
1117 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1118 THEN
1119 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1120 END IF;
1121 -- Standard call to get message count and if count=1, get the message
1122 FND_MSG_PUB.Count_And_Get (
1123 p_encoded => G_FALSE,
1124 p_count => x_msg_count,
1125 p_data => x_msg_data);
1126
1127 End Delete_AP;
1128
1129
1130
1131 -- ===============================================================
1132 -- Procedure name
1133 -- Revise_Without_Revision_Exists
1134 -- Purpose
1135 -- revise audit procedure with specified audit_procedure_id,
1136 -- it'll revise the one having latest_revision_flag='Y'
1137 -- AND approval_status='A' OR 'R' of specified audit_procedure_id.
1138 -- the new revision created by this call will have
1139 -- latest_revision_flag='Y', and the approval_status
1140 -- will be set to 'D' if not specified in the p_audit_procedure_rec
1141 -- the revisee(the old one) will have latest_revision_flag='N'
1142 -- Note
1143 -- actually the name for Revise_Without_Revision_Exists
1144 -- should be Revise_Without_Draft_Revision_Exists if there's
1145 -- no limitation for the procedure name.
1146 -- ===============================================================
1147 PROCEDURE Revise_Without_Revision_Exists(
1148 p_operate_mode IN VARCHAR2,
1149 p_api_version_number IN NUMBER,
1150 p_init_msg_list IN VARCHAR2,
1151 p_commit IN VARCHAR2,
1152 p_validation_level IN NUMBER,
1153
1154 x_return_status OUT NOCOPY VARCHAR2,
1155 x_msg_count OUT NOCOPY NUMBER,
1156 x_msg_data OUT NOCOPY VARCHAR2,
1157
1158 p_audit_procedure_rec IN audit_procedure_rec_type,
1159 x_audit_procedure_rev_id OUT NOCOPY NUMBER,
1160 x_audit_procedure_id OUT NOCOPY NUMBER
1161 )
1162 IS
1163 l_api_name CONSTANT VARCHAR2(30) := 'Revise_Without_Revision_Exists';
1164 l_dummy_audit_procedure_rec audit_procedure_rec_type := NULL;
1165 l_audit_procedure_rec audit_procedure_rec_type := NULL;
1166 l_audit_procedure_description amw_audit_procedures_tl.description%TYPE;
1167 l_classification number;
1168
1169 -- find the target revision to be revised
1170 CURSOR c_target_revision (l_audit_procedure_id IN NUMBER) IS
1171 SELECT audit_procedure_rev_id,
1172 audit_procedure_id,
1173 audit_procedure_rev_num,
1174 object_version_number
1175 FROM amw_audit_procedures_b
1176 WHERE audit_procedure_id = l_audit_procedure_id AND ( approval_status='A' OR approval_status='R') AND latest_revision_flag='Y';
1177 target_revision c_target_revision%ROWTYPE;
1178
1179 BEGIN
1180 -- Standard Start of API savepoint
1181 SAVEPOINT REVISE_AP_PVT;
1182
1183
1184 -- Initialize message list if p_init_msg_list is set to TRUE.
1185 IF FND_API.to_Boolean( p_init_msg_list )
1186 THEN
1187 FND_MSG_PUB.initialize;
1188 END IF;
1189
1190 AMW_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
1191
1192
1193 -- Initialize API return status to SUCCESS
1194 x_return_status := G_RET_STS_SUCCESS;
1195
1196
1197 OPEN c_target_revision(p_audit_procedure_rec.audit_procedure_id);
1198 FETCH c_target_revision INTO target_revision;
1199 CLOSE c_target_revision;
1200
1201
1202 -- update the target(latest existing) revision
1203 l_audit_procedure_rec.audit_procedure_id := p_audit_procedure_rec.audit_procedure_id;
1204 l_audit_procedure_rec.audit_procedure_rev_id := target_revision.audit_procedure_rev_id;
1205 l_audit_procedure_rec.latest_revision_flag := 'N';
1206 -- 05.13.2004 tsho: bug 3595420, need to be consistent with UI
1207 --l_audit_procedure_rec.end_date := SYSDATE;
1208 l_audit_procedure_rec.object_version_number := target_revision.object_version_number+1;
1209
1210
1211 IF p_audit_procedure_rec.approval_status = 'A' THEN
1212 l_audit_procedure_rec.curr_approved_flag := 'N';
1213 -- 05.13.2004 tsho: bug 3595420, need to be consistent with UI
1214 l_audit_procedure_rec.end_date := SYSDATE;
1215 END IF;
1216
1217
1218 Complete_AP_Rec(
1219 p_audit_procedure_rec => l_audit_procedure_rec,
1220 x_complete_rec => l_dummy_audit_procedure_rec);
1221
1222
1223 l_audit_procedure_description := l_dummy_audit_procedure_rec.audit_procedure_description;
1224
1225 Update_AP(
1226 p_operate_mode => p_operate_mode,
1227 p_api_version_number => p_api_version_number,
1228 p_init_msg_list => p_init_msg_list,
1229 p_commit => p_commit,
1230 p_validation_level => p_validation_level,
1231 x_return_status => x_return_status,
1232 x_msg_count => x_msg_count,
1233 x_msg_data => x_msg_data,
1234 p_audit_procedure_rec => l_dummy_audit_procedure_rec,
1235 x_audit_procedure_rev_id => x_audit_procedure_rev_id,
1236 x_audit_procedure_id => x_audit_procedure_id);
1237
1238
1239 IF x_return_status <> G_RET_STS_SUCCESS THEN
1240
1241 AMW_UTILITY_PVT.Error_Message(p_message_name => 'AMW_EXE_ERROR',
1242 p_token_name => 'OBJ_TYPE',
1243 p_token_value => G_OBJ_TYPE);
1244 RAISE FND_API.G_EXC_ERROR;
1245 END IF;
1246
1247
1248 x_audit_procedure_id := p_audit_procedure_rec.audit_procedure_id;
1249
1250 -- create the new revision
1251 l_dummy_audit_procedure_rec := p_audit_procedure_rec;
1252 l_dummy_audit_procedure_rec.latest_revision_flag := 'Y';
1253 l_dummy_audit_procedure_rec.object_version_number := 1;
1254 l_dummy_audit_procedure_rec.audit_procedure_rev_num := target_revision.audit_procedure_rev_num+1;
1255
1256
1257 IF p_audit_procedure_rec.audit_procedure_description IS NULL THEN
1258 l_dummy_audit_procedure_rec.audit_procedure_description := l_audit_procedure_description;
1259 END IF;
1260
1261
1262 IF p_audit_procedure_rec.approval_status = 'A' THEN
1263 l_dummy_audit_procedure_rec.approval_status := 'A';
1264 l_dummy_audit_procedure_rec.curr_approved_flag := 'Y';
1265 l_dummy_audit_procedure_rec.approval_date := SYSDATE;
1266 ELSE
1267 l_dummy_audit_procedure_rec.approval_status := 'D';
1268 -- 05.13.2004 tsho: bug 3595420, need to be consistent with UI
1269 --l_dummy_audit_procedure_rec.curr_approved_flag := 'N';
1270 l_dummy_audit_procedure_rec.curr_approved_flag := 'R';
1271 END IF;
1272
1273
1274 -- check if the AP has a classification already
1275 ---03.01.2005 npanandi: inserted the below SQL into a local Begin block
1276 ---webADI upload gives a "Exact Fetch return More than one Row" error
1277 begin
1278 SELECT classification
1279 INTO l_classification
1280 FROM amw_audit_procedures_b
1281 ---WHERE audit_procedure_id = target_revision.audit_procedure_rev_id;
1282 WHERE audit_procedure_id = target_revision.audit_procedure_id;
1283 exception
1284 when no_data_found then
1285 null;
1286 when others then
1287 null;
1288 end;
1289
1290 IF l_classification IS NOT NULL
1291 THEN
1292 l_dummy_audit_procedure_rec.classification := l_classification;
1293 END IF;
1294
1295 Create_AP(
1296 p_operate_mode => p_operate_mode,
1297 p_api_version_number => p_api_version_number,
1298 p_init_msg_list => p_init_msg_list,
1299 p_commit => p_commit,
1300 p_validation_level => p_validation_level,
1301 x_return_status => x_return_status,
1302 x_msg_count => x_msg_count,
1303 x_msg_data => x_msg_data,
1304 p_audit_procedure_rec => l_dummy_audit_procedure_rec,
1305 x_audit_procedure_rev_id => x_audit_procedure_rev_id,
1306 x_audit_procedure_id => x_audit_procedure_id);
1307
1308 IF x_return_status <> G_RET_STS_SUCCESS THEN
1309 AMW_UTILITY_PVT.Error_Message(p_message_name => 'AMW_EXE_ERROR',
1310 p_token_name => 'OBJ_TYPE',
1311 p_token_value => G_OBJ_TYPE);
1312 RAISE FND_API.G_EXC_ERROR;
1313 END IF;
1314
1315 -- Standard check for p_commit
1316 IF FND_API.to_Boolean( p_commit )
1317 THEN
1318 COMMIT WORK;
1319 END IF;
1320
1321 AMW_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
1322
1323 -- Standard call to get message count and if count is 1, get message info.
1324 FND_MSG_PUB.Count_And_Get
1325 (p_count => x_msg_count,
1326 p_data => x_msg_data);
1327
1328 EXCEPTION
1329
1330 WHEN AMW_UTILITY_PVT.resource_locked THEN
1331 x_return_status := G_RET_STS_ERROR;
1332 AMW_UTILITY_PVT.Error_Message(p_message_name => 'AMW_API_RESOURCE_LOCKED');
1333
1334 WHEN FND_API.G_EXC_ERROR THEN
1335 ROLLBACK TO REVISE_AP_PVT;
1336 x_return_status := G_RET_STS_ERROR;
1337 -- Standard call to get message count and if count=1, get the message
1338 FND_MSG_PUB.Count_And_Get (
1339 p_encoded => G_FALSE,
1340 p_count => x_msg_count,
1341 p_data => x_msg_data);
1342
1343 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1344 ROLLBACK TO REVISE_AP_PVT;
1345 x_return_status := G_RET_STS_UNEXP_ERROR;
1346 -- Standard call to get message count and if count=1, get the message
1347 FND_MSG_PUB.Count_And_Get (
1348 p_encoded => G_FALSE,
1349 p_count => x_msg_count,
1350 p_data => x_msg_data);
1351
1352 WHEN OTHERS THEN
1353 ROLLBACK TO REVISE_AP_PVT;
1354 x_return_status := G_RET_STS_UNEXP_ERROR;
1355 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1356 THEN
1357 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1358 END IF;
1359 -- Standard call to get message count and if count=1, get the message
1360 FND_MSG_PUB.Count_And_Get (
1361 p_encoded => G_FALSE,
1362 p_count => x_msg_count,
1363 p_data => x_msg_data);
1364
1365 END Revise_Without_Revision_Exists;
1366
1367
1368
1369 -- ===============================================================
1370 -- Procedure name
1371 -- check_AP_uk_items
1372 -- Purpose
1373 -- check the uniqueness of the items which have been marked
1374 -- as unique in table
1375 -- ===============================================================
1376 PROCEDURE check_AP_uk_items(
1377 p_operate_mode IN VARCHAR2,
1378 p_audit_procedure_rec IN audit_procedure_rec_type,
1379 x_return_status OUT NOCOPY VARCHAR2
1380 )
1381 IS
1382 l_valid_flag VARCHAR2(1);
1383
1384 BEGIN
1385 x_return_status := G_RET_STS_SUCCESS;
1386
1387 -- 07.23.2003 tsho
1388 -- comment out for performance: since the uniqueness of
1389 -- audit_procedure_rev_id and audit_procedure_id have been checked when creating
1390 /*
1391 IF p_operate_mode = G_OP_CREATE THEN
1392 l_valid_flag := AMW_UTILITY_PVT.check_uniqueness(
1393 'amw_audit_procedures_b',
1394 'audit_procedure_rev_id = ''' || p_audit_procedure_rec.audit_procedure_rev_id ||''''
1395 );
1396 ELSE
1397 l_valid_flag := AMW_UTILITY_PVT.check_uniqueness(
1398 'amw_audit_procedures_b',
1399 'audit_procedure_rev_id = ''' || p_audit_procedure_rec.audit_procedure_rev_id ||
1400 ''' AND audit_procedure_rev_id <> ' || p_audit_procedure_rec.audit_procedure_rev_id
1401 );
1402 END IF;
1403 */
1404 END check_AP_uk_items;
1405
1406
1407
1408 -- ===============================================================
1409 -- Procedure name
1410 -- check_AP_req_items
1411 -- Purpose
1412 -- check the requireness of the items which have been marked
1413 -- as NOT NULL in table
1414 -- Note
1415 -- since the standard default with
1416 -- FND_API.G_MISS_XXX v.s. NULL has been changed to:
1417 -- if user want to update to Null, pass in G_MISS_XXX
1418 -- else if user want to update to some value, pass in value
1419 -- else if user doesn't want to update, pass in NULL.
1420 -- Reference
1421 -- http://www-apps.us.oracle.com/atg/performance/
1422 -- Standards and Templates>Business Object API Coding Standards
1423 -- 2.3.1 Differentiating between Missing parameters and Null parameters
1424 -- ===============================================================
1425 PROCEDURE check_AP_req_items(
1426 p_operate_mode IN VARCHAR2,
1427 p_audit_procedure_rec IN audit_procedure_rec_type,
1428 x_return_status OUT NOCOPY VARCHAR2
1429 )
1430 IS
1431 BEGIN
1432 x_return_status := G_RET_STS_SUCCESS;
1433
1434 IF p_operate_mode = G_OP_CREATE THEN
1435
1436 IF p_audit_procedure_rec.audit_procedure_rev_num IS NULL THEN
1437 AMW_UTILITY_PVT.Error_Message(p_message_name => 'AMW_REQUIRE_ITEM_ERROR',
1438 p_token_name => 'ITEM',
1439 p_token_value => 'audit_procedure_rev_num');
1440 x_return_status := G_RET_STS_ERROR;
1441 RAISE FND_API.G_EXC_ERROR;
1442 END IF;
1443
1444 IF p_audit_procedure_rec.latest_revision_flag IS NULL THEN
1445 AMW_UTILITY_PVT.Error_Message(p_message_name => 'AMW_REQUIRE_ITEM_ERROR',
1446 p_token_name => 'ITEM',
1447 p_token_value => 'latest_revision_flag');
1448 x_return_status := G_RET_STS_ERROR;
1449 RAISE FND_API.G_EXC_ERROR;
1450 END IF;
1451
1452 IF p_audit_procedure_rec.curr_approved_flag IS NULL THEN
1453 AMW_UTILITY_PVT.Error_Message(p_message_name => 'AMW_REQUIRE_ITEM_ERROR',
1454 p_token_name => 'ITEM',
1455 p_token_value => 'curr_approved_flag');
1456 x_return_status := G_RET_STS_ERROR;
1457 RAISE FND_API.G_EXC_ERROR;
1458 END IF;
1459
1460 ELSE
1461 IF p_audit_procedure_rec.audit_procedure_rev_id = FND_API.g_miss_num THEN
1462 AMW_UTILITY_PVT.Error_Message(p_message_name => 'AMW_REQUIRE_ITEM_ERROR',
1463 p_token_name => 'ITEM',
1464 p_token_value => 'audit_procedure_rev_id');
1465 x_return_status := G_RET_STS_ERROR;
1466 RAISE FND_API.G_EXC_ERROR;
1467 END IF;
1468
1469 IF p_audit_procedure_rec.audit_procedure_id = FND_API.g_miss_num THEN
1470 AMW_UTILITY_PVT.Error_Message(p_message_name => 'AMW_REQUIRE_ITEM_ERROR',
1471 p_token_name => 'ITEM',
1472 p_token_value => 'audit_procedure_id');
1473 x_return_status := G_RET_STS_ERROR;
1474 RAISE FND_API.G_EXC_ERROR;
1475 END IF;
1476
1477 IF p_audit_procedure_rec.audit_procedure_rev_num = FND_API.g_miss_num THEN
1478 AMW_UTILITY_PVT.Error_Message(p_message_name => 'AMW_REQUIRE_ITEM_ERROR',
1479 p_token_name => 'ITEM',
1480 p_token_value => 'audit_procedure_rev_num');
1481 x_return_status := G_RET_STS_ERROR;
1482 RAISE FND_API.G_EXC_ERROR;
1483 END IF;
1484
1485 IF p_audit_procedure_rec.latest_revision_flag = FND_API.g_miss_char THEN
1486 AMW_UTILITY_PVT.Error_Message(p_message_name => 'AMW_REQUIRE_ITEM_ERROR',
1487 p_token_name => 'ITEM',
1488 p_token_value => 'latest_revision_flag');
1489 x_return_status := G_RET_STS_ERROR;
1490 RAISE FND_API.G_EXC_ERROR;
1491 END IF;
1492
1493 IF p_audit_procedure_rec.curr_approved_flag = FND_API.g_miss_char THEN
1494 AMW_UTILITY_PVT.Error_Message(p_message_name => 'AMW_REQUIRE_ITEM_ERROR',
1495 p_token_name => 'ITEM',
1496 p_token_value => 'curr_approved_flag');
1497 x_return_status := G_RET_STS_ERROR;
1498 RAISE FND_API.G_EXC_ERROR;
1499 END IF;
1500
1501 END IF; -- end of if:p_operate_mode
1502
1503 END check_AP_req_items;
1504
1505
1506
1507 -- ===============================================================
1508 -- Procedure name
1509 -- check_AP_FK_items
1510 -- Purpose
1511 -- check forien key of the items
1512 -- ===============================================================
1513 PROCEDURE check_AP_FK_items(
1514 p_operate_mode IN VARCHAR2,
1515 p_audit_procedure_rec IN audit_procedure_rec_type,
1516 x_return_status OUT NOCOPY VARCHAR2
1517 )
1518 IS
1519 BEGIN
1520 x_return_status := G_RET_STS_SUCCESS;
1521 END check_AP_FK_items;
1522
1523
1524
1525 -- ===============================================================
1526 -- Procedure name
1527 -- check_AP_Lookup_items
1528 -- Purpose
1529 -- check lookup of the items
1530 -- ===============================================================
1531 PROCEDURE check_AP_Lookup_items(
1532 p_operate_mode IN VARCHAR2,
1533 p_audit_procedure_rec IN audit_procedure_rec_type,
1534 x_return_status OUT NOCOPY VARCHAR2
1535 )
1536 IS
1537 BEGIN
1538 x_return_status := G_RET_STS_SUCCESS;
1539 END check_AP_Lookup_items;
1540
1541
1542
1543 -- ===============================================================
1544 -- Procedure name
1545 -- Check_AP_Items
1546 -- Purpose
1547 -- check all the necessaries for items
1548 -- Note
1549 -- Check_AP_Items is the container for calling all the
1550 -- other validation procedures on items(check_xxx_Items)
1551 -- the validation on items should be only table column constraints
1552 -- not the business logic validation.
1553 -- ===============================================================
1554 PROCEDURE Check_AP_Items (
1555 p_operate_mode IN VARCHAR2,
1556 P_audit_procedure_rec IN audit_procedure_rec_type,
1557 x_return_status OUT NOCOPY VARCHAR2
1558 )
1559 IS
1560 BEGIN
1561 -- Check Items Uniqueness API calls
1562 check_AP_uk_items(
1563 p_operate_mode => p_operate_mode,
1564 p_audit_procedure_rec => p_audit_procedure_rec,
1565 x_return_status => x_return_status);
1566 IF x_return_status <> G_RET_STS_SUCCESS THEN
1567 AMW_UTILITY_PVT.Error_Message(p_message_name => 'AMW_EXE_ERROR',
1568 p_token_name => 'OBJ_TYPE',
1569 p_token_value => G_OBJ_TYPE);
1570 RAISE FND_API.G_EXC_ERROR;
1571 END IF;
1572
1573 -- Check Items Required/NOT NULL API calls
1574 check_AP_req_items(
1575 p_operate_mode => p_operate_mode,
1576 p_audit_procedure_rec => p_audit_procedure_rec,
1577 x_return_status => x_return_status);
1578 IF x_return_status <> G_RET_STS_SUCCESS THEN
1579 AMW_UTILITY_PVT.Error_Message(p_message_name => 'AMW_EXE_ERROR',
1580 p_token_name => 'OBJ_TYPE',
1581 p_token_value => G_OBJ_TYPE);
1582 RAISE FND_API.G_EXC_ERROR;
1583 END IF;
1584
1585 -- Check Items Foreign Keys API calls
1586 check_AP_FK_items(
1587 p_operate_mode => p_operate_mode,
1588 p_audit_procedure_rec => p_audit_procedure_rec,
1589 x_return_status => x_return_status);
1590 IF x_return_status <> G_RET_STS_SUCCESS THEN
1591 AMW_UTILITY_PVT.Error_Message(p_message_name => 'AMW_EXE_ERROR',
1592 p_token_name => 'OBJ_TYPE',
1593 p_token_value => G_OBJ_TYPE);
1594 RAISE FND_API.G_EXC_ERROR;
1595 END IF;
1596
1597 -- Check Items Lookups
1598 check_AP_Lookup_items(
1599 p_operate_mode => p_operate_mode,
1600 p_audit_procedure_rec => p_audit_procedure_rec,
1601 x_return_status => x_return_status);
1602 IF x_return_status <> G_RET_STS_SUCCESS THEN
1603 AMW_UTILITY_PVT.Error_Message(p_message_name => 'AMW_EXE_ERROR',
1604 p_token_name => 'OBJ_TYPE',
1605 p_token_value => G_OBJ_TYPE);
1606 RAISE FND_API.G_EXC_ERROR;
1607 END IF;
1608
1609 END Check_AP_Items;
1610
1611
1612
1613 -- ===============================================================
1614 -- Procedure name
1615 -- Complete_AP_Rec
1616 -- Purpose
1617 -- complete(fill out) the items which are not specified.
1618 -- Note
1619 -- basically, this is called when G_OP_UPDATE, G_OP_REVISE
1620 -- ===============================================================
1621 PROCEDURE Complete_AP_Rec (
1622 p_audit_procedure_rec IN audit_procedure_rec_type,
1623 x_complete_rec OUT NOCOPY audit_procedure_rec_type
1624 )
1625 IS
1626 l_api_name CONSTANT VARCHAR2(30) := 'Complete_AP_Rec';
1627 l_return_status VARCHAR2(1);
1628
1629 CURSOR c_complete IS
1630 SELECT *
1631 FROM amw_audit_procedures_b
1632 WHERE audit_procedure_rev_id = p_audit_procedure_rec.audit_procedure_rev_id;
1633 l_audit_procedure_rec c_complete%ROWTYPE;
1634
1635
1636 CURSOR c_tl_complete IS
1637 SELECT name,
1638 description
1639 FROM amw_audit_procedures_vl
1640 WHERE audit_procedure_rev_id = p_audit_procedure_rec.audit_procedure_rev_id;
1641 l_audit_procedure_tl_rec c_tl_complete%ROWTYPE;
1642
1643
1644 BEGIN
1645 AMW_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
1646 x_complete_rec := p_audit_procedure_rec;
1647
1648 OPEN c_complete;
1649 FETCH c_complete INTO l_audit_procedure_rec;
1650 CLOSE c_complete;
1651
1652 OPEN c_tl_complete;
1653 FETCH c_tl_complete INTO l_audit_procedure_tl_rec;
1654 CLOSE c_tl_complete;
1655
1656 -- audit_procedure_rev_id
1657 IF p_audit_procedure_rec.audit_procedure_rev_id IS NULL THEN
1658 AMW_UTILITY_PVT.Error_Message(p_message_name => 'AMW_UNEXPECT_ERROR',
1659 p_token_name => 'OBJ_TYPE',
1660 p_token_value => G_OBJ_TYPE);
1661 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1662 END IF;
1663
1664 -- audit_procedure_id
1665 IF p_audit_procedure_rec.audit_procedure_id IS NULL THEN
1666 x_complete_rec.audit_procedure_id := l_audit_procedure_rec.audit_procedure_id;
1667 END IF;
1668
1669 -- audit_procedure_name
1670 IF p_audit_procedure_rec.audit_procedure_name IS NULL THEN
1671 x_complete_rec.audit_procedure_name := l_audit_procedure_tl_rec.name;
1672 END IF;
1673
1674 -- audit_procedure_description
1675 IF p_audit_procedure_rec.audit_procedure_description IS NULL THEN
1676 x_complete_rec.audit_procedure_description := l_audit_procedure_tl_rec.description;
1677 END IF;
1678
1679 -- last_update_date
1680 IF p_audit_procedure_rec.last_update_date IS NULL THEN
1681 x_complete_rec.last_update_date := l_audit_procedure_rec.last_update_date;
1682 END IF;
1683
1684 -- last_update_login
1685 IF p_audit_procedure_rec.last_update_login IS NULL THEN
1686 x_complete_rec.last_update_login := l_audit_procedure_rec.last_update_login;
1687 END IF;
1688
1689 -- created_by
1690 IF p_audit_procedure_rec.created_by IS NULL THEN
1691 x_complete_rec.created_by := l_audit_procedure_rec.created_by;
1692 END IF;
1693
1694 -- last_updated_by
1695 IF p_audit_procedure_rec.last_updated_by IS NULL THEN
1696 x_complete_rec.last_updated_by := l_audit_procedure_rec.last_updated_by;
1697 END IF;
1698
1699 -- security_group_id
1700 IF p_audit_procedure_rec.security_group_id IS NULL THEN
1701 x_complete_rec.security_group_id := l_audit_procedure_rec.security_group_id;
1702 END IF;
1703
1704 -- approval_status
1705 IF p_audit_procedure_rec.approval_status IS NULL THEN
1706 x_complete_rec.approval_status := l_audit_procedure_rec.approval_status;
1707 END IF;
1708
1709 -- object_version_number
1710 IF p_audit_procedure_rec.object_version_number IS NULL THEN
1711 x_complete_rec.object_version_number := l_audit_procedure_rec.object_version_number;
1712 END IF;
1713
1714 -- approval_date
1715 IF p_audit_procedure_rec.approval_date IS NULL THEN
1716 x_complete_rec.approval_date := l_audit_procedure_rec.approval_date;
1717 END IF;
1718
1719 -- creation_date
1720 IF p_audit_procedure_rec.creation_date IS NULL THEN
1721 x_complete_rec.creation_date := l_audit_procedure_rec.creation_date;
1722 END IF;
1723
1724 -- audit_procedure_rev_num
1725 IF p_audit_procedure_rec.audit_procedure_rev_num IS NULL THEN
1726 x_complete_rec.audit_procedure_rev_num := l_audit_procedure_rec.audit_procedure_rev_num;
1727 END IF;
1728 AMW_UTILITY_PVT.debug_message('audit_procedure_rev_num: ' || x_complete_rec.audit_procedure_rev_num);
1729
1730 -- requestor_id
1731 IF p_audit_procedure_rec.requestor_id IS NULL THEN
1732 x_complete_rec.requestor_id := l_audit_procedure_rec.requestor_id;
1733 END IF;
1734
1735 -- orig_system_reference
1736 IF p_audit_procedure_rec.orig_system_reference IS NULL THEN
1737 x_complete_rec.orig_system_reference := l_audit_procedure_rec.orig_system_reference;
1738 END IF;
1739
1740 -- latest_revision_flag
1741 IF p_audit_procedure_rec.latest_revision_flag IS NULL THEN
1742 x_complete_rec.latest_revision_flag := l_audit_procedure_rec.latest_revision_flag;
1743 END IF;
1744
1745 -- end_date
1746 IF p_audit_procedure_rec.end_date IS NULL THEN
1747 x_complete_rec.end_date := l_audit_procedure_rec.end_date;
1748 END IF;
1749
1750 -- curr_approved_flag
1751 IF p_audit_procedure_rec.curr_approved_flag IS NULL THEN
1752 x_complete_rec.curr_approved_flag := l_audit_procedure_rec.curr_approved_flag;
1753 END IF;
1754
1755 -- attribute_category
1756 IF p_audit_procedure_rec.attribute_category IS NULL THEN
1757 x_complete_rec.attribute_category := l_audit_procedure_rec.attribute_category;
1758 END IF;
1759
1760 -- attribute1
1761 IF p_audit_procedure_rec.attribute1 IS NULL THEN
1762 x_complete_rec.attribute1 := l_audit_procedure_rec.attribute1;
1763 END IF;
1764
1765 -- attribute2
1766 IF p_audit_procedure_rec.attribute2 IS NULL THEN
1767 x_complete_rec.attribute2 := l_audit_procedure_rec.attribute2;
1768 END IF;
1769
1770 -- attribute3
1771 IF p_audit_procedure_rec.attribute3 IS NULL THEN
1772 x_complete_rec.attribute3 := l_audit_procedure_rec.attribute3;
1773 END IF;
1774
1775 -- attribute4
1776 IF p_audit_procedure_rec.attribute4 IS NULL THEN
1777 x_complete_rec.attribute4 := l_audit_procedure_rec.attribute4;
1778 END IF;
1779
1780 -- attribute5
1781 IF p_audit_procedure_rec.attribute5 IS NULL THEN
1782 x_complete_rec.attribute5 := l_audit_procedure_rec.attribute5;
1783 END IF;
1784
1785 -- attribute6
1786 IF p_audit_procedure_rec.attribute6 IS NULL THEN
1787 x_complete_rec.attribute6 := l_audit_procedure_rec.attribute6;
1788 END IF;
1789
1790 -- attribute7
1791 IF p_audit_procedure_rec.attribute7 IS NULL THEN
1792 x_complete_rec.attribute7 := l_audit_procedure_rec.attribute7;
1793 END IF;
1794
1795 -- attribute8
1796 IF p_audit_procedure_rec.attribute8 IS NULL THEN
1797 x_complete_rec.attribute8 := l_audit_procedure_rec.attribute8;
1798 END IF;
1799
1800 -- attribute9
1801 IF p_audit_procedure_rec.attribute9 IS NULL THEN
1802 x_complete_rec.attribute9 := l_audit_procedure_rec.attribute9;
1803 END IF;
1804
1805 -- attribute10
1806 IF p_audit_procedure_rec.attribute10 IS NULL THEN
1807 x_complete_rec.attribute10 := l_audit_procedure_rec.attribute10;
1808 END IF;
1809
1810 -- attribute11
1811 IF p_audit_procedure_rec.attribute11 IS NULL THEN
1812 x_complete_rec.attribute11 := l_audit_procedure_rec.attribute11;
1813 END IF;
1814
1815 -- attribute12
1816 IF p_audit_procedure_rec.attribute12 IS NULL THEN
1817 x_complete_rec.attribute12 := l_audit_procedure_rec.attribute12;
1818 END IF;
1819
1820 -- attribute13
1821 IF p_audit_procedure_rec.attribute13 IS NULL THEN
1822 x_complete_rec.attribute13 := l_audit_procedure_rec.attribute13;
1823 END IF;
1824
1825 -- attribute14
1826 IF p_audit_procedure_rec.attribute14 IS NULL THEN
1827 x_complete_rec.attribute14 := l_audit_procedure_rec.attribute14;
1828 END IF;
1829
1830 -- attribute15
1831 IF p_audit_procedure_rec.attribute15 IS NULL THEN
1832 x_complete_rec.attribute15 := l_audit_procedure_rec.attribute15;
1833 END IF;
1834
1835 AMW_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
1836 END Complete_AP_Rec;
1837
1838
1839
1840 -- ===============================================================
1841 -- Procedure name
1842 -- Validate_AP
1843 -- Purpose
1844 -- Validate_AP is the container for calling all the other
1845 -- validation procedures on one record(Validate_xxx_Rec) and
1846 -- the container of validation on items(Check_AP_Items)
1847 -- Note
1848 -- basically, this should be called before calling table handler
1849 -- ===============================================================
1850 PROCEDURE Validate_AP(
1851 p_operate_mode IN VARCHAR2,
1852 p_api_version_number IN NUMBER,
1853 p_init_msg_list IN VARCHAR2,
1854 p_validation_level IN NUMBER,
1855 p_audit_procedure_rec IN audit_procedure_rec_type,
1856 x_audit_procedure_rec OUT NOCOPY audit_procedure_rec_type,
1857 x_return_status OUT NOCOPY VARCHAR2,
1858 x_msg_count OUT NOCOPY NUMBER,
1859 x_msg_data OUT NOCOPY VARCHAR2
1860 )
1861 IS
1862 L_API_NAME CONSTANT VARCHAR2(30) := 'Validate_AP';
1863 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
1864 l_object_version_number NUMBER;
1865 l_audit_procedure_rec audit_procedure_rec_type;
1866
1867 BEGIN
1868 -- Standard Start of API savepoint
1869 SAVEPOINT VALIDATE_AP_;
1870 AMW_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
1871
1872 -- Initialize API return status to SUCCESS
1873 x_return_status := G_RET_STS_SUCCESS;
1874
1875 -- Standard call to check for call compatibility.
1876 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1877 p_api_version_number,
1878 l_api_name,
1879 G_PKG_NAME)
1880 THEN
1881 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1882 END IF;
1883
1884 -- Initialize message list if p_init_msg_list is set to TRUE.
1885 IF FND_API.to_Boolean( p_init_msg_list )
1886 THEN
1887 FND_MSG_PUB.initialize;
1888 END IF;
1889
1890 l_audit_procedure_rec := p_audit_procedure_rec;
1891 -- 07.21.2003 tsho, only update and revise need complete_AP_rec
1892 IF p_operate_mode = G_OP_UPDATE OR p_operate_mode = G_OP_REVISE THEN
1893 Complete_AP_Rec(
1894 p_audit_procedure_rec => p_audit_procedure_rec,
1895 x_complete_rec => l_audit_procedure_rec);
1896 END IF;
1897
1898
1899 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
1900 Check_AP_Items(
1901 p_operate_mode => p_operate_mode,
1902 p_audit_procedure_rec => l_audit_procedure_rec,
1903 x_return_status => x_return_status);
1904
1905 IF x_return_status = G_RET_STS_ERROR THEN
1906 RAISE FND_API.G_EXC_ERROR;
1907 ELSIF x_return_status = G_RET_STS_UNEXP_ERROR THEN
1908 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1909 END IF;
1910 END IF;
1911
1912
1913 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
1914 Validate_AP_Rec(
1915 p_operate_mode => p_operate_mode,
1916 p_api_version_number => 1.0,
1917 p_init_msg_list => G_FALSE,
1918 x_return_status => x_return_status,
1919 x_msg_count => x_msg_count,
1920 x_msg_data => x_msg_data,
1921 p_audit_procedure_rec => l_audit_procedure_rec);
1922
1923 IF x_return_status = G_RET_STS_ERROR THEN
1924 RAISE FND_API.G_EXC_ERROR;
1925 ELSIF x_return_status = G_RET_STS_UNEXP_ERROR THEN
1926 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1927 END IF;
1928 END IF;
1929
1930 x_audit_procedure_rec := l_audit_procedure_rec;
1931
1932 AMW_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
1933
1934 -- Standard call to get message count and if count is 1, get message info.
1935 FND_MSG_PUB.Count_And_Get
1936 (p_count => x_msg_count,
1937 p_data => x_msg_data);
1938
1939 EXCEPTION
1940
1941 WHEN AMW_UTILITY_PVT.resource_locked THEN
1942 x_return_status := G_RET_STS_ERROR;
1943 AMW_UTILITY_PVT.Error_Message(p_message_name => 'AMW_API_RESOURCE_LOCKED');
1944
1945 WHEN FND_API.G_EXC_ERROR THEN
1946 ROLLBACK TO VALIDATE_AP_;
1947 x_return_status := G_RET_STS_ERROR;
1948 -- Standard call to get message count and if count=1, get the message
1949 FND_MSG_PUB.Count_And_Get (
1950 p_encoded => G_FALSE,
1951 p_count => x_msg_count,
1952 p_data => x_msg_data);
1953
1954 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1955 ROLLBACK TO VALIDATE_AP_;
1956 x_return_status := G_RET_STS_UNEXP_ERROR;
1957 -- Standard call to get message count and if count=1, get the message
1958 FND_MSG_PUB.Count_And_Get (
1959 p_encoded => G_FALSE,
1960 p_count => x_msg_count,
1961 p_data => x_msg_data);
1962
1963 WHEN OTHERS THEN
1964 ROLLBACK TO VALIDATE_AP_;
1965 x_return_status := G_RET_STS_UNEXP_ERROR;
1966 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1967 THEN
1968 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1969 END IF;
1970 -- Standard call to get message count and if count=1, get the message
1971 FND_MSG_PUB.Count_And_Get (
1972 p_encoded => G_FALSE,
1973 p_count => x_msg_count,
1974 p_data => x_msg_data);
1975
1976 End Validate_AP;
1977
1978
1979
1980 -- ===============================================================
1981 -- Procedure name
1982 -- Validate_AP_rec
1983 -- Purpose
1984 -- check all the necessaries for one record,
1985 -- this includes the cross-items validation
1986 -- Note
1987 -- Validate_AP_rec is the dispatcher of
1988 -- other validation procedures on one record.
1989 -- business logic validation should go here.
1990 -- ===============================================================
1991 PROCEDURE Validate_AP_rec(
1992 p_operate_mode IN VARCHAR2,
1993 p_api_version_number IN NUMBER,
1994 p_init_msg_list IN VARCHAR2,
1995 x_return_status OUT NOCOPY VARCHAR2,
1996 x_msg_count OUT NOCOPY NUMBER,
1997 x_msg_data OUT NOCOPY VARCHAR2,
1998 p_audit_procedure_rec IN audit_procedure_rec_type
1999 )
2000 IS
2001 l_api_name CONSTANT VARCHAR2(30) := 'Validate_AP_Rec';
2002
2003 BEGIN
2004 -- Initialize message list if p_init_msg_list is set to TRUE.
2005 IF FND_API.to_Boolean( p_init_msg_list )
2006 THEN
2007 FND_MSG_PUB.initialize;
2008 END IF;
2009
2010 AMW_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
2011
2012 -- Initialize API return status to SUCCESS
2013 x_return_status := G_RET_STS_SUCCESS;
2014
2015 IF p_operate_mode = G_OP_CREATE THEN
2016 Validate_create_AP_rec(
2017 x_return_status => x_return_status,
2018 x_msg_count => x_msg_count,
2019 x_msg_data => x_msg_data,
2020 p_audit_procedure_rec => p_audit_procedure_rec);
2021 IF x_return_status<>G_RET_STS_SUCCESS THEN
2022 AMW_UTILITY_PVT.Error_Message(p_message_name => 'AMW_EXE_ERROR',
2023 p_token_name => 'OBJ_TYPE',
2024 p_token_value => G_OBJ_TYPE);
2025 RAISE FND_API.G_EXC_ERROR;
2026 END IF;
2027
2028 ELSIF p_operate_mode = G_OP_UPDATE THEN
2029 Validate_update_AP_rec(
2030 x_return_status => x_return_status,
2031 x_msg_count => x_msg_count,
2032 x_msg_data => x_msg_data,
2033 p_audit_procedure_rec => p_audit_procedure_rec);
2034 IF x_return_status<>G_RET_STS_SUCCESS THEN
2035 AMW_UTILITY_PVT.Error_Message(p_message_name => 'AMW_EXE_ERROR',
2036 p_token_name => 'OBJ_TYPE',
2037 p_token_value => G_OBJ_TYPE);
2038 RAISE FND_API.G_EXC_ERROR;
2039 END IF;
2040
2041 ELSIF p_operate_mode = G_OP_REVISE THEN
2042 Validate_revise_AP_rec(
2043 x_return_status => x_return_status,
2044 x_msg_count => x_msg_count,
2045 x_msg_data => x_msg_data,
2046 p_audit_procedure_rec => p_audit_procedure_rec);
2047 IF x_return_status<>G_RET_STS_SUCCESS THEN
2048 AMW_UTILITY_PVT.Error_Message(p_message_name => 'AMW_EXE_ERROR',
2049 p_token_name => 'OBJ_TYPE',
2050 p_token_value => G_OBJ_TYPE);
2051 RAISE FND_API.G_EXC_ERROR;
2052 END IF;
2053
2054 ELSIF p_operate_mode = G_OP_DELETE THEN
2055 Validate_delete_AP_rec(
2056 x_return_status => x_return_status,
2057 x_msg_count => x_msg_count,
2058 x_msg_data => x_msg_data,
2059 p_audit_procedure_rec => p_audit_procedure_rec);
2060 IF x_return_status<>G_RET_STS_SUCCESS THEN
2061 AMW_UTILITY_PVT.Error_Message(p_message_name => 'AMW_EXE_ERROR',
2062 p_token_name => 'OBJ_TYPE',
2063 p_token_value => G_OBJ_TYPE);
2064 RAISE FND_API.G_EXC_ERROR;
2065 END IF;
2066
2067 ELSE
2068 AMW_UTILITY_PVT.Error_Message(p_message_name => 'AMW_UNEXPECT_ERROR',
2069 p_token_name => 'OBJ_TYPE',
2070 p_token_value => G_OBJ_TYPE);
2071 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2072 END IF;
2073
2074
2075 AMW_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
2076
2077 -- Standard call to get message count and if count is 1, get message info.
2078 FND_MSG_PUB.Count_And_Get
2079 (p_count => x_msg_count,
2080 p_data => x_msg_data);
2081
2082 END Validate_AP_rec;
2083
2084
2085
2086
2087 -- ===============================================================
2088 -- Procedure name
2089 -- Validate_create_AP_rec
2090 -- Purpose
2091 -- this is the validation for mode G_OP_CREATE.
2092 -- Note
2093 -- audit_procedure name cannot be duplicated in table
2094 -- ===============================================================
2095 PROCEDURE Validate_create_AP_rec(
2096 x_return_status OUT NOCOPY VARCHAR2,
2097 x_msg_count OUT NOCOPY NUMBER,
2098 x_msg_data OUT NOCOPY VARCHAR2,
2099 p_audit_procedure_rec IN audit_procedure_rec_type
2100 )
2101 IS
2102 l_api_name CONSTANT VARCHAR2(30) := 'Validate_Create_AP_Rec';
2103 l_dummy NUMBER;
2104
2105 CURSOR c_name_exists (l_audit_procedure_name IN VARCHAR2) IS
2106 SELECT 1
2107 FROM amw_audit_procedures_vl
2108 WHERE name = l_audit_procedure_name;
2109
2110 BEGIN
2111 AMW_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
2112
2113 x_return_status := G_RET_STS_SUCCESS;
2114
2115 l_dummy := NULL;
2116 OPEN c_name_exists(p_audit_procedure_rec.audit_procedure_name);
2117 FETCH c_name_exists INTO l_dummy;
2118 CLOSE c_name_exists;
2119
2120 IF l_dummy IS NOT NULL THEN
2121 AMW_UTILITY_PVT.Error_Message(p_message_name => 'AMW_UNIQUE_ITEM_ERROR',
2122 p_token_name => 'ITEM',
2123 p_token_value => 'audit_procedure_name');
2124 x_return_status := G_RET_STS_ERROR;
2125 RAISE FND_API.G_EXC_ERROR;
2126 END IF;
2127
2128 AMW_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
2129
2130 -- Standard call to get message count and if count is 1, get message info.
2131 FND_MSG_PUB.Count_And_Get
2132 (p_count => x_msg_count,
2133 p_data => x_msg_data);
2134
2135 EXCEPTION
2136 WHEN FND_API.G_EXC_ERROR THEN
2137
2138 x_return_status := G_RET_STS_ERROR;
2139 -- Standard call to get message count and if count=1, get the message
2140 FND_MSG_PUB.Count_And_Get (
2141 p_encoded => G_FALSE,
2142 p_count => x_msg_count,
2143 p_data => x_msg_data);
2144
2145 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2146
2147 x_return_status := G_RET_STS_UNEXP_ERROR;
2148 -- Standard call to get message count and if count=1, get the message
2149 FND_MSG_PUB.Count_And_Get (
2150 p_encoded => G_FALSE,
2151 p_count => x_msg_count,
2152 p_data => x_msg_data);
2153
2154 WHEN OTHERS THEN
2155
2156 x_return_status := G_RET_STS_UNEXP_ERROR;
2157 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2158 THEN
2159 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
2160 END IF;
2161 -- Standard call to get message count and if count=1, get the message
2162 FND_MSG_PUB.Count_And_Get (
2163 p_encoded => G_FALSE,
2164 p_count => x_msg_count,
2165 p_data => x_msg_data);
2166
2167 END Validate_create_AP_rec;
2168
2169
2170
2171 -- ===============================================================
2172 -- Procedure name
2173 -- Validate_update_AP_rec
2174 -- Purpose
2175 -- this is the validation for mode G_OP_UPDATE.
2176 -- Note
2177 -- audit procedure name cannot be duplicated in table.
2178 -- only the audit procedure with approval_status='D' can be use G_OP_UPDATE
2179 -- ===============================================================
2180 PROCEDURE Validate_update_AP_rec(
2181 x_return_status OUT NOCOPY VARCHAR2,
2182 x_msg_count OUT NOCOPY NUMBER,
2183 x_msg_data OUT NOCOPY VARCHAR2,
2184 p_audit_procedure_rec IN audit_procedure_rec_type
2185 )
2186 IS
2187 l_api_name CONSTANT VARCHAR2(30) := 'Validate_Update_AP_Rec';
2188 l_dummy NUMBER;
2189
2190 -- c_target_audit_procedure is holding the info of target audit procedure which is going to be updated
2191 CURSOR c_target_audit_procedure (l_audit_procedure_rev_id IN NUMBER) IS
2192 SELECT approval_status
2193 FROM amw_audit_procedures_b
2194 WHERE audit_procedure_rev_id = l_audit_procedure_rev_id;
2195 target_audit_procedure c_target_audit_procedure%ROWTYPE;
2196
2197 CURSOR c_name_exists (l_audit_procedure_name IN VARCHAR2,l_audit_procedure_id IN NUMBER) IS
2198 SELECT 1
2199 FROM amw_audit_procedures_vl
2200 WHERE name = l_audit_procedure_name AND audit_procedure_id <> l_audit_procedure_id;
2201
2202 BEGIN
2203 AMW_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
2204
2205 x_return_status := G_RET_STS_SUCCESS;
2206
2207 -- only approval_status='D' can be updated
2208 OPEN c_target_audit_procedure(p_audit_procedure_rec.audit_procedure_rev_id);
2209 FETCH c_target_audit_procedure INTO target_audit_procedure;
2210 CLOSE c_target_audit_procedure;
2211 IF target_audit_procedure.approval_status <> 'D' THEN
2212 x_return_status := G_RET_STS_ERROR;
2213 AMW_UTILITY_PVT.debug_message('approval_status <> D');
2214 END IF;
2215
2216 -- name duplication is not allowed
2217 l_dummy := NULL;
2218 OPEN c_name_exists(p_audit_procedure_rec.audit_procedure_name,p_audit_procedure_rec.audit_procedure_id);
2219 FETCH c_name_exists INTO l_dummy;
2220 CLOSE c_name_exists;
2221 IF l_dummy IS NOT NULL THEN
2222 AMW_UTILITY_PVT.debug_message('name exists');
2223 AMW_UTILITY_PVT.Error_Message(p_message_name => 'AMW_UNIQUE_ITEM_ERROR',
2224 p_token_name => 'ITEM',
2225 p_token_value => 'audit_procedure_name');
2226 x_return_status := G_RET_STS_ERROR;
2227 RAISE FND_API.G_EXC_ERROR;
2228 END IF;
2229
2230 AMW_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
2231
2232 -- Standard call to get message count and if count is 1, get message info.
2233 FND_MSG_PUB.Count_And_Get
2234 (p_count => x_msg_count,
2235 p_data => x_msg_data);
2236
2237 EXCEPTION
2238
2239 WHEN FND_API.G_EXC_ERROR THEN
2240
2241 x_return_status := G_RET_STS_ERROR;
2242 -- Standard call to get message count and if count=1, get the message
2243 FND_MSG_PUB.Count_And_Get (
2244 p_encoded => G_FALSE,
2245 p_count => x_msg_count,
2246 p_data => x_msg_data);
2247
2248 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2249
2250 x_return_status := G_RET_STS_UNEXP_ERROR;
2251 -- Standard call to get message count and if count=1, get the message
2252 FND_MSG_PUB.Count_And_Get (
2253 p_encoded => G_FALSE,
2254 p_count => x_msg_count,
2255 p_data => x_msg_data);
2256
2257 WHEN OTHERS THEN
2258
2259 x_return_status := G_RET_STS_UNEXP_ERROR;
2260 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2261 THEN
2262 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
2263 END IF;
2264 -- Standard call to get message count and if count=1, get the message
2265 FND_MSG_PUB.Count_And_Get (
2266 p_encoded => G_FALSE,
2267 p_count => x_msg_count,
2268 p_data => x_msg_data);
2269
2270 END Validate_update_AP_rec;
2271
2272
2273
2274 -- ===============================================================
2275 -- Procedure name
2276 -- Validate_revise_AP_rec
2277 -- Purpose
2278 -- this is the validation for mode G_OP_REVISE.
2279 -- Note
2280 -- changing audit procedure name when revising an audit procedure is not allowed.
2281 -- ===============================================================
2282 PROCEDURE Validate_revise_AP_rec(
2283 x_return_status OUT NOCOPY VARCHAR2,
2284 x_msg_count OUT NOCOPY NUMBER,
2285 x_msg_data OUT NOCOPY VARCHAR2,
2286 p_audit_procedure_rec IN audit_procedure_rec_type
2287 )
2288 IS
2289 l_api_name CONSTANT VARCHAR2(30) := 'Validate_Revise_AP_Rec';
2290 l_dummy NUMBER;
2291
2292 -- c_target_audit_procedure is holding the info of target audit procedure from amw_audit_procedures_b which is going to be revised
2293 CURSOR c_target_audit_procedure (l_audit_procedure_rev_id IN NUMBER) IS
2294 SELECT approval_status
2295 FROM amw_audit_procedures_b
2296 WHERE audit_procedure_rev_id = l_audit_procedure_rev_id;
2297 target_audit_procedure c_target_audit_procedure%ROWTYPE;
2298
2299 CURSOR c_get_name (l_audit_procedure_rev_id IN NUMBER) IS
2300 SELECT name
2301 FROM amw_audit_procedures_vl
2302 WHERE audit_procedure_rev_id = l_audit_procedure_rev_id;
2303 original_audit_procedure_name amw_audit_procedures_vl.name%TYPE;
2304
2305 BEGIN
2306 AMW_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
2307
2308 x_return_status := G_RET_STS_SUCCESS;
2309
2310 -- change the name when revise an audit procedure is not allowed
2311 OPEN c_get_name(p_audit_procedure_rec.audit_procedure_rev_id);
2312 FETCH c_get_name INTO original_audit_procedure_name;
2313 CLOSE c_get_name;
2314 IF original_audit_procedure_name <> p_audit_procedure_rec.audit_procedure_name THEN
2315 x_return_status := G_RET_STS_ERROR;
2316 AMW_UTILITY_PVT.Error_Message(p_message_name => 'AMW_EXE_ERROR',
2317 p_token_name => 'OBJ_TYPE',
2318 p_token_value => G_OBJ_TYPE);
2319 RAISE FND_API.G_EXC_ERROR;
2320 END IF;
2321
2322 AMW_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
2323
2324 -- Standard call to get message count and if count is 1, get message info.
2325 FND_MSG_PUB.Count_And_Get
2326 (p_count => x_msg_count,
2327 p_data => x_msg_data);
2328
2329 EXCEPTION
2330
2331 WHEN FND_API.G_EXC_ERROR THEN
2332
2333 x_return_status := G_RET_STS_ERROR;
2334 -- Standard call to get message count and if count=1, get the message
2335 FND_MSG_PUB.Count_And_Get (
2336 p_encoded => G_FALSE,
2337 p_count => x_msg_count,
2338 p_data => x_msg_data);
2339
2340 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2341
2342 x_return_status := G_RET_STS_UNEXP_ERROR;
2343 -- Standard call to get message count and if count=1, get the message
2344 FND_MSG_PUB.Count_And_Get (
2345 p_encoded => G_FALSE,
2346 p_count => x_msg_count,
2347 p_data => x_msg_data);
2348
2349 WHEN OTHERS THEN
2350
2351 x_return_status := G_RET_STS_UNEXP_ERROR;
2352 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2353 THEN
2354 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
2355 END IF;
2356 -- Standard call to get message count and if count=1, get the message
2357 FND_MSG_PUB.Count_And_Get (
2358 p_encoded => G_FALSE,
2359 p_count => x_msg_count,
2360 p_data => x_msg_data);
2361
2362 END Validate_revise_AP_rec;
2363
2364
2365
2366 -- ===============================================================
2367 -- Procedure name
2368 -- Validate_delete_AP_rec
2369 -- Purpose
2370 -- this is the validation for mode G_OP_DELETE.
2371 -- Note
2372 -- not implemented yet.
2373 -- need to find out when(approval_status='?') can G_OP_DELETE.
2374 -- ===============================================================
2375 PROCEDURE Validate_delete_AP_rec(
2376 x_return_status OUT NOCOPY VARCHAR2,
2377 x_msg_count OUT NOCOPY NUMBER,
2378 x_msg_data OUT NOCOPY VARCHAR2,
2379 p_audit_procedure_rec IN audit_procedure_rec_type
2380 )
2381 IS
2382 l_api_name CONSTANT VARCHAR2(30) := 'Validate_Delete_AP_Rec';
2383 l_dummy NUMBER;
2384
2385 CURSOR c_audit_procedure_exists (l_audit_procedure_rev_id IN NUMBER) IS
2386 SELECT 1
2387 FROM amw_audit_procedures_b
2388 WHERE audit_procedure_rev_id = l_audit_procedure_rev_id;
2389
2390 BEGIN
2391 AMW_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
2392
2393 x_return_status := G_RET_STS_SUCCESS;
2394
2395 -- can only delete an audit procedure which exists and has APPROVAL_STATUS='''
2396 l_dummy := NULL;
2397 OPEN c_audit_procedure_exists(p_audit_procedure_rec.audit_procedure_rev_id);
2398 FETCH c_audit_procedure_exists INTO l_dummy;
2399 CLOSE c_audit_procedure_exists;
2400 IF l_dummy IS NULL THEN
2401 AMW_UTILITY_PVT.Error_Message(p_message_name => 'AMW_EXE_ERROR',
2402 p_token_name => 'OBJ_TYPE',
2403 p_token_value => G_OBJ_TYPE);
2404 x_return_status := G_RET_STS_ERROR;
2405 RAISE FND_API.G_EXC_ERROR;
2406 END IF;
2407
2408 AMW_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
2409
2410 -- Standard call to get message count and if count is 1, get message info.
2411 FND_MSG_PUB.Count_And_Get
2412 (p_count => x_msg_count,
2413 p_data => x_msg_data);
2414
2415 EXCEPTION
2416
2417 WHEN FND_API.G_EXC_ERROR THEN
2418
2419 x_return_status := G_RET_STS_ERROR;
2420 -- Standard call to get message count and if count=1, get the message
2421 FND_MSG_PUB.Count_And_Get (
2422 p_encoded => G_FALSE,
2423 p_count => x_msg_count,
2424 p_data => x_msg_data);
2425
2426 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2427
2428 x_return_status := G_RET_STS_UNEXP_ERROR;
2429 -- Standard call to get message count and if count=1, get the message
2430 FND_MSG_PUB.Count_And_Get (
2431 p_encoded => G_FALSE,
2432 p_count => x_msg_count,
2433 p_data => x_msg_data);
2434
2435 WHEN OTHERS THEN
2436
2437 x_return_status := G_RET_STS_UNEXP_ERROR;
2438 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2439 THEN
2440 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
2441 END IF;
2442 -- Standard call to get message count and if count=1, get the message
2443 FND_MSG_PUB.Count_And_Get (
2444 p_encoded => G_FALSE,
2445 p_count => x_msg_count,
2446 p_data => x_msg_data);
2447
2448 END Validate_delete_AP_rec;
2449
2450
2451 -- ===============================================================
2452 -- Procedure name
2453 -- copy_audit_step
2454 -- Purpose
2455 -- this procedure copies audit steps from from_ap_rev_id to
2456 -- to_ap_rev_id
2457 -- Note
2458 --
2459 -- ===============================================================
2460 PROCEDURE copy_audit_steps(
2461 p_api_version IN NUMBER,
2462 p_init_msg_list IN VARCHAR2, -- default FND_API.G_FALSE,
2463 p_commit IN VARCHAR2, -- default FND_API.G_FALSE,
2464 p_validation_level IN NUMBER, -- default FND_API.G_VALID_LEVEL_FULL,
2465 x_return_status OUT NOCOPY VARCHAR2,
2466 x_msg_count OUT NOCOPY NUMBER,
2467 x_msg_data OUT NOCOPY VARCHAR2,
2468 x_from_ap_rev_id IN NUMBER,
2469 x_to_ap_id IN NUMBER
2470 )
2471 IS
2472 l_api_name CONSTANT VARCHAR2(30) := 'copy_audit_steps';
2473 l_api_version CONSTANT NUMBER := 1.0;
2474 l_object_version_number NUMBER := 1;
2475 l_from_rev_num NUMBER := 1;
2476 l_row_id amw_ap_steps_vl.row_id%TYPE;
2477 l_id NUMBER;
2478 CURSOR steps_b IS SELECT
2479 AMW_AP_STEPS_S.NEXTVAL STEP_ID,
2480 SEQNUM,
2481 SAMPLESIZE,
2482 step.LAST_UPDATE_DATE,
2483 step.LAST_UPDATED_BY,
2484 step.CREATION_DATE,
2485 step.CREATED_BY,
2486 step.LAST_UPDATE_LOGIN,
2487 step.ATTRIBUTE_CATEGORY,
2488 step.ATTRIBUTE1,
2489 step.ATTRIBUTE2,
2490 step.ATTRIBUTE3,
2491 step.ATTRIBUTE4,
2492 step.ATTRIBUTE5,
2493 step.ATTRIBUTE6,
2494 step.ATTRIBUTE7,
2495 step.ATTRIBUTE8,
2496 step.ATTRIBUTE9,
2497 step.ATTRIBUTE10,
2498 step.ATTRIBUTE11,
2499 step.ATTRIBUTE12,
2500 step.ATTRIBUTE13,
2501 step.ATTRIBUTE14,
2502 step.ATTRIBUTE15,
2503 step.SECURITY_GROUP_ID,
2504 step.OBJECT_VERSION_NUMBER,
2505 step.ORIG_SYSTEM_REFERENCE,
2506 step.REQUESTOR_ID,
2507 step.NAME,
2508 step.DESCRIPTION,
2509 step.CSEQNUM
2510 FROM AMW_AP_STEPS_VL step, AMW_AUDIT_PROCEDURES_B ap
2511
2512 WHERE ap.audit_procedure_rev_id = x_from_ap_rev_id and
2513 ap.audit_procedure_id = step.audit_procedure_id and
2514 ap.audit_procedure_rev_num >= step.from_rev_num and
2515 ap.audit_procedure_rev_num < NVL ( step.to_rev_num, ap.audit_procedure_rev_num + 1) ;
2516
2517
2518 BEGIN
2519 -- Standard Start of API savepoint
2520 SAVEPOINT COPY_AUDIT_STEPS_SAVEPT;
2521
2522 -- Standard call to check for call compatibility.
2523 IF NOT FND_API.Compatible_API_Call (l_api_version,
2524 p_api_version,
2525 l_api_name,
2526 G_PKG_NAME)
2527 THEN
2528 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2529 END IF;
2530
2531 -- Initialize message list if p_init_msg_list is set to TRUE.
2532 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2533 FND_MSG_PUB.initialize;
2534 END IF;
2535 -- Initialize API return status to success
2536 x_return_status := FND_API.G_RET_STS_SUCCESS;
2537
2538 IF x_to_ap_id IS NULL OR x_to_ap_id = FND_API.G_MISS_NUM
2539 THEN
2540 -- missing or NULL required parameter
2541 -- 1. Set the return status to error
2542 -- 2. Write a message to the message list.
2543 -- 3. Return to the caller.
2544 x_return_status := FND_API.G_RET_STS_ERROR ;
2545 FND_MESSAGE.SET_NAME ('AMW', 'AMW_MISS_TO_AP_ID');
2546 FND_MSG_PUB.Add;
2547 RETURN;
2548 END IF;
2549 FOR steprec IN steps_b
2550 LOOP
2551
2552 AMW_AP_STEPS_PKG.INSERT_ROW (
2553 X_ROWID => l_row_id,
2554 X_AP_STEP_ID => steprec.STEP_ID,
2555 X_ATTRIBUTE4 => steprec.ATTRIBUTE4,
2556 X_ATTRIBUTE5 => steprec.ATTRIBUTE5,
2557 X_ATTRIBUTE1 => steprec.ATTRIBUTE1 ,
2558 X_ATTRIBUTE6 => steprec.ATTRIBUTE6 ,
2559 X_ATTRIBUTE7 => steprec.ATTRIBUTE7,
2560 X_ATTRIBUTE8 => steprec.ATTRIBUTE8,
2561 X_ATTRIBUTE9 => steprec.ATTRIBUTE9,
2562 X_SAMPLESIZE => steprec.SAMPLESIZE,
2563 X_AUDIT_PROCEDURE_ID => x_to_ap_id,
2564 X_SEQNUM => steprec.SEQNUM,
2565 X_ATTRIBUTE2 => steprec.ATTRIBUTE2,
2566 X_ATTRIBUTE3 => steprec.ATTRIBUTE3,
2567 X_ATTRIBUTE10 => steprec.ATTRIBUTE10,
2568 X_ATTRIBUTE11 => steprec.ATTRIBUTE11,
2569 X_ATTRIBUTE12 => steprec.ATTRIBUTE12,
2570 X_ATTRIBUTE13 => steprec.ATTRIBUTE13,
2571 X_ATTRIBUTE14 => steprec.ATTRIBUTE14,
2572 X_ATTRIBUTE15 => steprec.ATTRIBUTE15,
2573 X_SECURITY_GROUP_ID => steprec.SECURITY_GROUP_ID,
2574 X_OBJECT_VERSION_NUMBER => l_object_version_number,
2575 X_ORIG_SYSTEM_REFERENCE => steprec.ORIG_SYSTEM_REFERENCE,
2576 X_REQUESTOR_ID => steprec.REQUESTOR_ID,
2577 X_ATTRIBUTE_CATEGORY => steprec.ATTRIBUTE_CATEGORY,
2578 X_NAME => steprec.NAME,
2579 X_DESCRIPTION => steprec.DESCRIPTION,
2580 X_CREATION_DATE => SYSDATE,
2581 X_CREATED_BY => G_USER_ID,
2582 X_LAST_UPDATE_DATE => SYSDATE,
2583 X_LAST_UPDATED_BY => G_USER_ID,
2584 X_LAST_UPDATE_LOGIN => G_LOGIN_ID,
2585 X_FROM_REV_NUM => l_from_rev_num,
2586 X_TO_REV_NUM => NULL,
2587 X_CSEQNUM => steprec.CSEQNUM);
2588 END LOOP;
2589 IF FND_API.To_Boolean( p_commit ) THEN
2590 COMMIT WORK;
2591 END IF;
2592 -- Standard call to get message count and if count is 1, get message info.
2593 FND_MSG_PUB.Count_And_Get
2594 ( p_count => x_msg_count ,
2595 p_data => x_msg_data
2596 );
2597 EXCEPTION
2598 WHEN FND_API.G_EXC_ERROR THEN
2599 ROLLBACK TO COPY_AUDIT_STEPS_SAVEPT;
2600 x_return_status := FND_API.G_RET_STS_ERROR ;
2601 FND_MSG_PUB.Count_And_Get
2602 ( p_count => x_msg_count ,
2603 p_data => x_msg_data
2604 );
2605 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2606 ROLLBACK TO COPY_AUDIT_STEPS_SAVEPT;
2607 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2608 FND_MSG_PUB.Count_And_Get
2609 ( p_count => x_msg_count ,
2610 p_data => x_msg_data
2611 );
2612 WHEN OTHERS THEN
2613 ROLLBACK TO COPY_AUDIT_STEPS_SAVEPT;
2614 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2615 IF FND_MSG_PUB.Check_Msg_Level
2616 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2617 THEN
2618 FND_MSG_PUB.Add_Exc_Msg
2619 ( G_PKG_NAME ,
2620 l_api_name
2621 );
2622 END IF;
2623 FND_MSG_PUB.Count_And_Get
2624 ( p_count => x_msg_count ,
2625 p_data => x_msg_data
2626 );
2627 END copy_audit_steps;
2628
2629 -- ===============================================================
2630 -- Procedure name
2631 -- copy_tasks
2632 -- Purpose
2633 -- this procedure copies tasks from from_ap_id to
2634 -- to_ap_id
2635 -- Note
2636 --
2637 -- ===============================================================
2638 PROCEDURE copy_tasks(
2639 p_api_version IN NUMBER,
2640 p_init_msg_list IN VARCHAR2, -- default FND_API.G_FALSE,
2641 p_commit IN VARCHAR2, -- default FND_API.G_FALSE,
2642 p_validation_level IN NUMBER, -- default FND_API.G_VALID_LEVEL_FULL,
2643 x_return_status OUT NOCOPY VARCHAR2,
2644 x_msg_count OUT NOCOPY NUMBER,
2645 x_msg_data OUT NOCOPY VARCHAR2,
2646 x_from_ap_id IN NUMBER,
2647 x_to_ap_id IN NUMBER
2648 )
2649 IS
2650 l_api_name CONSTANT VARCHAR2(30) := 'copy_tasks';
2651 l_api_version CONSTANT NUMBER := 1.0;
2652
2653 BEGIN
2654 -- Standard Start of API savepoint
2655 SAVEPOINT COPY_TASKS_SAVEPT;
2656
2657 -- Standard call to check for call compatibility.
2658 IF NOT FND_API.Compatible_API_Call (l_api_version,
2659 p_api_version,
2660 l_api_name,
2661 G_PKG_NAME)
2662 THEN
2663 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2664 END IF;
2665
2666 -- Initialize message list if p_init_msg_list is set to TRUE.
2667 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2668 FND_MSG_PUB.initialize;
2669 END IF;
2670 -- Initialize API return status to success
2671 x_return_status := FND_API.G_RET_STS_SUCCESS;
2672
2673 IF x_to_ap_id IS NULL OR x_to_ap_id = FND_API.G_MISS_NUM
2674 THEN
2675 -- missing or NULL required parameter
2676 -- 1. Set the return status to error
2677 -- 2. Write a message to the message list.
2678 -- 3. Return to the caller.
2679 x_return_status := FND_API.G_RET_STS_ERROR ;
2680 FND_MESSAGE.SET_NAME ('AMW', 'AMW_MISS_TO_AP_ID');
2681 FND_MSG_PUB.Add;
2682 RETURN;
2683 END IF;
2684
2685 --FOR taskrec IN tasks
2686 --LOOP
2687
2688 INSERT INTO AMW_AP_TASKS (AP_TASK_ID,
2689 AUDIT_PROCEDURE_ID,
2690 TASK_ID,
2691 PROJECT_ID,
2692 LAST_UPDATE_DATE,
2693 LAST_UPDATED_BY ,
2694 CREATION_DATE ,
2695 CREATED_BY ,
2696 LAST_UPDATE_LOGIN,
2697 ATTRIBUTE_CATEGORY,
2698 ATTRIBUTE1 ,
2699 ATTRIBUTE2 ,
2700 ATTRIBUTE3 ,
2701 ATTRIBUTE4 ,
2702 ATTRIBUTE5 ,
2703 ATTRIBUTE6 ,
2704 ATTRIBUTE7 ,
2705 ATTRIBUTE8 ,
2706 ATTRIBUTE9 ,
2707 ATTRIBUTE10 ,
2708 ATTRIBUTE11 ,
2709 ATTRIBUTE12 ,
2710 ATTRIBUTE13 ,
2711 ATTRIBUTE14 ,
2712 ATTRIBUTE15 ,
2713 SECURITY_GROUP_ID,
2714 OBJECT_VERSION_NUMBER,
2715 ORIG_SYSTEM_REFERENCE,
2716 REQUESTOR_ID)
2717 (SELECT AMW_AP_TASKS_S.NEXTVAL,
2718 x_to_ap_id,
2719 taskrec.TASK_ID,
2720 taskrec.PROJECT_ID,
2721 SYSDATE,
2722 G_USER_ID,
2723 SYSDATE,
2724 G_USER_ID,
2725 G_LOGIN_ID,
2726 taskrec.ATTRIBUTE_CATEGORY,
2727 taskrec.ATTRIBUTE1,
2728 taskrec.ATTRIBUTE2,
2729 taskrec.ATTRIBUTE3,
2730 taskrec.ATTRIBUTE4,
2731 taskrec.ATTRIBUTE5,
2732 taskrec.ATTRIBUTE6,
2733 taskrec.ATTRIBUTE7,
2734 taskrec.ATTRIBUTE8,
2735 taskrec.ATTRIBUTE9,
2736 taskrec.ATTRIBUTE10,
2737 taskrec.ATTRIBUTE11,
2738 taskrec.ATTRIBUTE12,
2739 taskrec.ATTRIBUTE13,
2740 taskrec.ATTRIBUTE14,
2741 taskrec.ATTRIBUTE15,
2742 taskrec.SECURITY_GROUP_ID,
2743 1,
2744 taskrec.ORIG_SYSTEM_REFERENCE,
2745 taskrec.REQUESTOR_ID
2746 FROM AMW_AP_TASKS taskrec
2747 WHERE audit_procedure_id = x_from_ap_id);
2748 --END LOOP;
2749 IF FND_API.To_Boolean( p_commit ) THEN
2750 COMMIT WORK;
2751 END IF;
2752 -- Standard call to get message count and if count is 1, get message info.
2753 FND_MSG_PUB.Count_And_Get
2754 ( p_count => x_msg_count ,
2755 p_data => x_msg_data
2756 );
2757 EXCEPTION
2758 WHEN FND_API.G_EXC_ERROR THEN
2759 ROLLBACK TO COPY_TASKS_SAVEPT;
2760 x_return_status := FND_API.G_RET_STS_ERROR ;
2761 FND_MSG_PUB.Count_And_Get
2762 ( p_count => x_msg_count ,
2763 p_data => x_msg_data
2764 );
2765 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2766 ROLLBACK TO COPY_TASKS_SAVEPT;
2767 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2768 FND_MSG_PUB.Count_And_Get
2769 ( p_count => x_msg_count ,
2770 p_data => x_msg_data
2771 );
2772 WHEN OTHERS THEN
2773 ROLLBACK TO COPY_TASKS_SAVEPT;
2774 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2775 IF FND_MSG_PUB.Check_Msg_Level
2776 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2777 THEN
2778 FND_MSG_PUB.Add_Exc_Msg
2779 ( G_PKG_NAME ,
2780 l_api_name
2781 );
2782 END IF;
2783 FND_MSG_PUB.Count_And_Get
2784 ( p_count => x_msg_count ,
2785 p_data => x_msg_data
2786 );
2787 END copy_tasks;
2788
2789 -- ===============================================================
2790 -- Procedure name
2791 -- copy_controls
2792 -- Purpose
2793 -- this procedure copies controls from from_ap_id to
2794 -- to_ap_id
2795 -- Note
2796 --
2797 -- ===============================================================
2798 PROCEDURE copy_controls(
2799 p_api_version IN NUMBER,
2800 p_init_msg_list IN VARCHAR2, -- default FND_API.G_FALSE,
2801 p_commit IN VARCHAR2, -- default FND_API.G_FALSE,
2802 p_validation_level IN NUMBER, -- default FND_API.G_VALID_LEVEL_FULL,
2803 x_return_status OUT NOCOPY VARCHAR2,
2804 x_msg_count OUT NOCOPY NUMBER,
2805 x_msg_data OUT NOCOPY VARCHAR2,
2806 x_from_ap_id IN NUMBER,
2807 x_to_ap_id IN NUMBER
2808 )
2809 IS
2810 l_api_name CONSTANT VARCHAR2(30) := 'copy_controls';
2811 l_api_version CONSTANT NUMBER := 1.0;
2812
2813 BEGIN
2814 -- Standard Start of API savepoint
2815 SAVEPOINT COPY_CONTROLS_SAVEPT;
2816
2817 -- Standard call to check for call compatibility.
2818 IF NOT FND_API.Compatible_API_Call (l_api_version,
2819 p_api_version,
2820 l_api_name,
2821 G_PKG_NAME)
2822 THEN
2823 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2824 END IF;
2825
2826 -- Initialize message list if p_init_msg_list is set to TRUE.
2827 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2828 FND_MSG_PUB.initialize;
2829 END IF;
2830 -- Initialize API return status to success
2831 x_return_status := FND_API.G_RET_STS_SUCCESS;
2832
2833 IF x_to_ap_id IS NULL OR x_to_ap_id = FND_API.G_MISS_NUM
2834 THEN
2835 -- missing or NULL required parameter
2836 -- 1. Set the return status to error
2837 -- 2. Write a message to the message list.
2838 -- 3. Return to the caller.
2839 x_return_status := FND_API.G_RET_STS_ERROR ;
2840 FND_MESSAGE.SET_NAME ('AMW', 'AMW_MISS_TO_AP_ID');
2841 FND_MSG_PUB.Add;
2842 RETURN;
2843 END IF;
2844
2845 --FOR ctrlrec IN controls_assoc
2846 --LOOP
2847
2848 INSERT INTO AMW_AP_ASSOCIATIONS (AP_ASSOCIATION_ID,
2849 LAST_UPDATE_DATE,
2850 LAST_UPDATED_BY,
2851 CREATION_DATE,
2852 CREATED_BY,
2853 LAST_UPDATE_LOGIN ,
2854 PK1 ,
2855 PK2 ,
2856 PK3 ,
2857 PK4 ,
2858 PK5 ,
2859 OBJECT_TYPE ,
2860 AUDIT_PROCEDURE_ID,
2861 ATTRIBUTE_CATEGORY,
2862 ATTRIBUTE1 ,
2863 ATTRIBUTE2 ,
2864 ATTRIBUTE3 ,
2865 ATTRIBUTE4 ,
2866 ATTRIBUTE5 ,
2867 ATTRIBUTE6 ,
2868 ATTRIBUTE7 ,
2869 ATTRIBUTE8 ,
2870 ATTRIBUTE9 ,
2871 ATTRIBUTE10 ,
2872 ATTRIBUTE11 ,
2873 ATTRIBUTE12 ,
2874 ATTRIBUTE13 ,
2875 ATTRIBUTE14 ,
2876 ATTRIBUTE15 ,
2877 SECURITY_GROUP_ID ,
2878 OBJECT_VERSION_NUMBER,
2879 DESIGN_EFFECTIVENESS ,
2880 OP_EFFECTIVENESS)
2881 (SELECT AMW_AP_ASSOCIATIONS_S.NEXTVAL,
2882 SYSDATE,
2883 G_USER_ID,
2884 SYSDATE,
2885 G_USER_ID,
2886 G_LOGIN_ID,
2887 ctrlrec.PK1,
2888 ctrlrec.PK2,
2889 ctrlrec.PK3,
2890 ctrlrec.PK4,
2891 ctrlrec.PK5,
2892 ctrlrec.OBJECT_TYPE,
2893 x_to_ap_id,
2894 ctrlrec.ATTRIBUTE_CATEGORY,
2895 ctrlrec.ATTRIBUTE1,
2896 ctrlrec.ATTRIBUTE2,
2897 ctrlrec.ATTRIBUTE3,
2898 ctrlrec.ATTRIBUTE4,
2899 ctrlrec.ATTRIBUTE5,
2900 ctrlrec.ATTRIBUTE6,
2901 ctrlrec.ATTRIBUTE7,
2902 ctrlrec.ATTRIBUTE8,
2903 ctrlrec.ATTRIBUTE9,
2904 ctrlrec.ATTRIBUTE10,
2905 ctrlrec.ATTRIBUTE11,
2906 ctrlrec.ATTRIBUTE12,
2907 ctrlrec.ATTRIBUTE13,
2908 ctrlrec.ATTRIBUTE14,
2909 ctrlrec.ATTRIBUTE15,
2910 ctrlrec.SECURITY_GROUP_ID,
2911 1,
2912 ctrlrec.DESIGN_EFFECTIVENESS,
2913 ctrlrec.OP_EFFECTIVENESS
2914 FROM AMW_AP_ASSOCIATIONS ctrlrec
2915 WHERE audit_procedure_id = x_from_ap_id);
2916 --END LOOP;
2917 IF FND_API.To_Boolean( p_commit ) THEN
2918 COMMIT WORK;
2919 END IF;
2920 -- Standard call to get message count and if count is 1, get message info.
2921 FND_MSG_PUB.Count_And_Get
2922 ( p_count => x_msg_count ,
2923 p_data => x_msg_data
2924 );
2925 EXCEPTION
2926 WHEN FND_API.G_EXC_ERROR THEN
2927 ROLLBACK TO COPY_CONTROLS_SAVEPT;
2928 x_return_status := FND_API.G_RET_STS_ERROR ;
2929 FND_MSG_PUB.Count_And_Get
2930 ( p_count => x_msg_count ,
2931 p_data => x_msg_data
2932 );
2933 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2934 ROLLBACK TO COPY_CONTROLS_SAVEPT;
2935 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2936 FND_MSG_PUB.Count_And_Get
2937 ( p_count => x_msg_count ,
2938 p_data => x_msg_data
2939 );
2940 WHEN OTHERS THEN
2941 ROLLBACK TO COPY_CONTROLS_SAVEPT;
2942 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2943 IF FND_MSG_PUB.Check_Msg_Level
2944 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2945 THEN
2946 FND_MSG_PUB.Add_Exc_Msg
2947 ( G_PKG_NAME ,
2948 l_api_name
2949 );
2950 END IF;
2951 FND_MSG_PUB.Count_And_Get
2952 ( p_count => x_msg_count ,
2953 p_data => x_msg_data
2954 );
2955
2956 END copy_controls;
2957
2958 --
2959 -- Insert ap_steps
2960 --
2961 --
2962 procedure insert_ap_step(
2963 p_api_version_number IN NUMBER,
2964 p_init_msg_list IN VARCHAR2,
2965 p_commit IN VARCHAR2,
2966 p_validation_level IN NUMBER,
2967 p_samplesize in number,
2968 p_audit_procedure_id in number,
2969 p_seqnum in varchar2,
2970 p_requestor_id in number,
2971 p_name in varchar2,
2972 p_description in varchar2,
2973 p_audit_procedure_rev_id in number,
2974 p_user_id in number,
2975 x_return_status OUT NOCOPY VARCHAR2,
2976 x_msg_count OUT NOCOPY NUMBER,
2977 x_msg_data OUT NOCOPY VARCHAR2)
2978 is
2979 CURSOR c_step_exists (c_step_num IN varchar2,c_ap_id IN NUMBER, c_from_ap_rev_num IN NUMBER) IS
2980 SELECT b.ap_step_id,
2981 b.name,
2982 b.description,
2983 b.samplesize,
2984 b.from_rev_num,
2985 b.to_rev_num,
2986 b.object_version_number
2987 FROM amw_ap_steps_vl b
2988 WHERE b.cseqnum = c_step_num
2989 AND b.audit_procedure_id = c_ap_id
2990 AND b.from_rev_num = c_from_ap_rev_num;
2991
2992 CURSOR c_step_exists_for_prev_rev (c_step_num IN varchar2,c_ap_id IN NUMBER, c_from_ap_rev_num IN NUMBER) IS
2993 SELECT b.ap_step_id,
2994 b.name,
2995 b.description,
2996 b.samplesize,
2997 b.cseqnum,
2998 b.from_rev_num,
2999 b.to_rev_num,
3000 b.object_version_number
3001 FROM amw_ap_steps_vl b
3002 WHERE b.cseqnum = c_step_num
3003 AND b.audit_procedure_id = c_ap_id
3004 AND b.to_rev_num is null and b.from_rev_num <> c_from_ap_rev_num;
3005
3006 CURSOR c_get_rev_num(c_audit_procedure_rev_id in number) is
3007 Select audit_procedure_rev_num
3008 From amw_audit_procedures_b
3009 Where audit_procedure_rev_id = c_audit_procedure_rev_id;
3010
3011 cursor get_ap_steps_id is
3012 select amw_ap_steps_s.nextval from dual;
3013
3014 l_api_name CONSTANT VARCHAR2(30) := 'Insert_AP_Step';
3015 l_api_version_number CONSTANT NUMBER := 1.0;
3016
3017 l_ap_step_id number;
3018 lx_rowid amw_ap_steps_vl.row_id%type;
3019 l_ap_rev_num number;
3020 lx_step_rec c_step_exists%rowtype;
3021 lx_prev_step_rec c_step_exists_for_prev_rev%rowtype;
3022 begin
3023 -- Standard Start of API savepoint
3024 SAVEPOINT INSERT_AP_STEP_PVT;
3025 -- Standard call to check for call compatibility.
3026 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
3027 p_api_version_number,
3028 l_api_name,
3029 G_PKG_NAME)
3030 THEN
3031 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3032 END IF;
3033
3034 -- Initialize message list if p_init_msg_list is set to TRUE.
3035 IF FND_API.to_Boolean( p_init_msg_list )
3036 THEN
3037 FND_MSG_PUB.initialize;
3038 END IF;
3039
3040 -- added npanandi 11/08/2004
3041 x_return_status := fnd_api.g_ret_sts_success;
3042
3043 fnd_file.put_line(fnd_file.LOG,'Inside insert_ap_step --> x_return_status: '||x_return_status);
3044
3045 open c_get_rev_num(p_audit_procedure_rev_id);
3046 fetch c_get_rev_num into l_ap_rev_num;
3047 close c_get_rev_num;
3048
3049 if(l_ap_rev_num is null)
3050 then
3051 AMW_UTILITY_PVT.Error_Message(p_message_name => 'AMW_UNEXPECT_ERROR',
3052 p_token_name => 'OBJ_TYPE',
3053 p_token_value => G_OBJ_TYPE);
3054 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3055 end if;
3056
3057 -- check if a step with the same p_seqnum exists with from_rev_num = l_rev_num. If it does then update
3058 -- it. Check if a step with the same p_seqnum exists with to_rev_num = null. If it does then set
3059 -- to_rev_num = l_rev_num and insert a new step row with from_rev_num = l_rev_num.
3060 OPEN c_step_exists(p_seqnum, p_audit_procedure_id, l_ap_rev_num);
3061 FETCH c_step_exists INTO lx_step_rec;
3062 CLOSE c_step_exists;
3063
3064 OPEN c_step_exists_for_prev_rev(p_seqnum, p_audit_procedure_id, l_ap_rev_num);
3065 FETCH c_step_exists_for_prev_rev INTO lx_prev_step_rec;
3066 CLOSE c_step_exists_for_prev_rev;
3067
3068 open get_ap_steps_id;
3069 fetch get_ap_steps_id into l_ap_step_id;
3070 close get_ap_steps_id;
3071
3072 if(lx_step_rec.ap_step_id is not null)
3073 then
3074 -- update the step
3075 amw_ap_steps_pkg.update_row(
3076 X_AP_STEP_ID => lx_step_rec.ap_step_id,
3077 X_ATTRIBUTE4 => null,
3078 X_ATTRIBUTE5 => null,
3079 X_ATTRIBUTE1 => null,
3080 X_ATTRIBUTE6 => null,
3081 X_ATTRIBUTE7 => null,
3082 X_ATTRIBUTE8 => null,
3083 X_ATTRIBUTE9 => null,
3084 X_SAMPLESIZE => p_samplesize,
3085 X_AUDIT_PROCEDURE_ID => p_audit_procedure_id,
3086 X_SEQNUM => null,
3087 X_ATTRIBUTE2 => null,
3088 X_ATTRIBUTE3 => null,
3089 X_ATTRIBUTE10 => null,
3090 X_ATTRIBUTE11 => null,
3091 X_ATTRIBUTE12 => null,
3092 X_ATTRIBUTE13 => null,
3093 X_ATTRIBUTE14 => null,
3094 X_ATTRIBUTE15 => null,
3095 X_SECURITY_GROUP_ID => null,
3096 X_OBJECT_VERSION_NUMBER => lx_step_rec.object_version_number + 1,
3097 X_ORIG_SYSTEM_REFERENCE => null,
3098 X_REQUESTOR_ID => p_requestor_id,
3099 X_ATTRIBUTE_CATEGORY => null,
3100 X_NAME => p_name,
3101 X_DESCRIPTION => p_description,
3102 X_LAST_UPDATE_DATE => sysdate,
3103 X_LAST_UPDATED_BY => p_user_id,
3104 X_LAST_UPDATE_LOGIN => p_user_id,
3105 X_FROM_REV_NUM => l_ap_rev_num,
3106 X_TO_REV_NUM => null,
3107 X_CSEQNUM => p_seqnum);
3108 elsif(lx_prev_step_rec.ap_step_id is not null)
3109 then
3110 -- set to_rev_num and insert a new row
3111 amw_ap_steps_pkg.update_row(
3112 X_AP_STEP_ID => lx_prev_step_rec.ap_step_id,
3113 X_ATTRIBUTE4 => null,
3114 X_ATTRIBUTE5 => null,
3115 X_ATTRIBUTE1 => null,
3116 X_ATTRIBUTE6 => null,
3117 X_ATTRIBUTE7 => null,
3118 X_ATTRIBUTE8 => null,
3119 X_ATTRIBUTE9 => null,
3120 X_SAMPLESIZE => lx_prev_step_rec.samplesize,
3121 X_AUDIT_PROCEDURE_ID => p_audit_procedure_id,
3122 X_SEQNUM => null,
3123 X_ATTRIBUTE2 => null,
3124 X_ATTRIBUTE3 => null,
3125 X_ATTRIBUTE10 => null,
3126 X_ATTRIBUTE11 => null,
3127 X_ATTRIBUTE12 => null,
3128 X_ATTRIBUTE13 => null,
3129 X_ATTRIBUTE14 => null,
3130 X_ATTRIBUTE15 => null,
3131 X_SECURITY_GROUP_ID => null,
3132 X_OBJECT_VERSION_NUMBER => lx_prev_step_rec.object_version_number + 1,
3133 X_ORIG_SYSTEM_REFERENCE => null,
3134 X_REQUESTOR_ID => p_requestor_id,
3135 X_ATTRIBUTE_CATEGORY => null,
3136 X_NAME => lx_prev_step_rec.name,
3137 X_DESCRIPTION => lx_prev_step_rec.description,
3138 X_LAST_UPDATE_DATE => sysdate,
3139 X_LAST_UPDATED_BY => p_user_id,
3140 X_LAST_UPDATE_LOGIN => p_user_id,
3141 X_FROM_REV_NUM => lx_prev_step_rec.from_rev_num,
3142 X_TO_REV_NUM => l_ap_rev_num,
3143 X_CSEQNUM => lx_prev_step_rec.cseqnum);
3144
3145 amw_ap_steps_pkg.insert_row(X_ROWID => lx_rowid,
3146 X_AP_STEP_ID => l_ap_step_id,
3147 X_ATTRIBUTE4 => null,
3148 X_ATTRIBUTE5 => null,
3149 X_ATTRIBUTE1 => null,
3150 X_ATTRIBUTE6 => null,
3151 X_ATTRIBUTE7 => null,
3152 X_ATTRIBUTE8 => null,
3153 X_ATTRIBUTE9 => null,
3154 X_SAMPLESIZE => p_samplesize,
3155 X_AUDIT_PROCEDURE_ID => p_audit_procedure_id,
3156 X_SEQNUM => null,
3157 X_ATTRIBUTE2 => null,
3158 X_ATTRIBUTE3 => null,
3159 X_ATTRIBUTE10 => null,
3160 X_ATTRIBUTE11 => null,
3161 X_ATTRIBUTE12 => null,
3162 X_ATTRIBUTE13 => null,
3163 X_ATTRIBUTE14 => null,
3164 X_ATTRIBUTE15 => null,
3165 X_SECURITY_GROUP_ID => null,
3166 X_OBJECT_VERSION_NUMBER => 1,
3167 X_ORIG_SYSTEM_REFERENCE => null,
3168 X_REQUESTOR_ID => p_requestor_id,
3169 X_ATTRIBUTE_CATEGORY => null,
3170 X_NAME => p_name,
3171 X_DESCRIPTION => p_description,
3172 X_CREATION_DATE => sysdate,
3173 X_CREATED_BY => p_user_id,
3174 X_LAST_UPDATE_DATE => sysdate,
3175 X_LAST_UPDATED_BY => p_user_id,
3176 X_LAST_UPDATE_LOGIN => p_user_id,
3177 X_FROM_REV_NUM => l_ap_rev_num,
3178 X_TO_REV_NUM => null,
3179 X_CSEQNUM => p_seqnum);
3180 else
3181 -- create a new step as it does not exist
3182 amw_ap_steps_pkg.insert_row(X_ROWID => lx_rowid,
3183 X_AP_STEP_ID => l_ap_step_id,
3184 X_ATTRIBUTE4 => null,
3185 X_ATTRIBUTE5 => null,
3186 X_ATTRIBUTE1 => null,
3187 X_ATTRIBUTE6 => null,
3188 X_ATTRIBUTE7 => null,
3189 X_ATTRIBUTE8 => null,
3190 X_ATTRIBUTE9 => null,
3191 X_SAMPLESIZE => p_samplesize,
3192 X_AUDIT_PROCEDURE_ID => p_audit_procedure_id,
3193 X_SEQNUM => null,
3194 X_ATTRIBUTE2 => null,
3195 X_ATTRIBUTE3 => null,
3196 X_ATTRIBUTE10 => null,
3197 X_ATTRIBUTE11 => null,
3198 X_ATTRIBUTE12 => null,
3199 X_ATTRIBUTE13 => null,
3200 X_ATTRIBUTE14 => null,
3201 X_ATTRIBUTE15 => null,
3202 X_SECURITY_GROUP_ID => null,
3203 X_OBJECT_VERSION_NUMBER => 1,
3204 X_ORIG_SYSTEM_REFERENCE => null,
3205 X_REQUESTOR_ID => p_requestor_id,
3206 X_ATTRIBUTE_CATEGORY => null,
3207 X_NAME => p_name,
3208 X_DESCRIPTION => p_description,
3209 X_CREATION_DATE => sysdate,
3210 X_CREATED_BY => p_user_id,
3211 X_LAST_UPDATE_DATE => sysdate,
3212 X_LAST_UPDATED_BY => p_user_id,
3213 X_LAST_UPDATE_LOGIN => p_user_id,
3214 X_FROM_REV_NUM => l_ap_rev_num,
3215 X_TO_REV_NUM => null,
3216 X_CSEQNUM => p_seqnum);
3217 end if;
3218
3219 -- Standard check for p_commit
3220 IF FND_API.to_Boolean( p_commit )
3221 THEN
3222 COMMIT WORK;
3223 END IF;
3224
3225 fnd_file.put_line(fnd_file.LOG,'Done with amw_ap_steps_pkg.insert_row');
3226
3227 exception
3228 WHEN OTHERS THEN
3229 ROLLBACK TO INSERT_AP_STEP_PVT;
3230 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3231 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
3232 p_count => x_msg_count,
3233 p_data => x_msg_data);
3234
3235 end insert_ap_step;
3236
3237 --
3238 -- Insert control association
3239 --
3240 --
3241 procedure insert_ap_control_assoc(
3242 p_api_version_number IN NUMBER,
3243 p_init_msg_list IN VARCHAR2,
3244 p_commit IN VARCHAR2,
3245 p_validation_level IN NUMBER,
3246 p_control_id in number,
3247 p_audit_procedure_id in number,
3248 p_des_eff in varchar2,
3249 p_op_eff in varchar2,
3250 p_approval_date in date,
3251 p_user_id in number,
3252 x_return_status OUT NOCOPY VARCHAR2,
3253 x_msg_count OUT NOCOPY NUMBER,
3254 x_msg_data OUT NOCOPY VARCHAR2)
3255 is
3256 CURSOR c_assoc_exists (c_control_id IN NUMBER,c_ap_id IN NUMBER) IS
3257 SELECT a.ap_association_id,
3258 a.pk1,
3259 a.audit_procedure_id,
3260 a.design_effectiveness,
3261 a.op_effectiveness,
3262 a.object_version_number
3263 FROM amw_ap_associations a
3264 WHERE a.audit_procedure_id = c_ap_id
3265 AND a.object_type = 'CTRL'
3266 AND a.pk1 = c_control_id
3267 AND a.deletion_date is null
3268 AND a.approval_date is null;
3269
3270 CURSOR c_prev_assoc_exists (c_control_id IN NUMBER,c_ap_id IN NUMBER) IS
3271 SELECT a.ap_association_id,
3272 a.pk1,
3273 a.audit_procedure_id,
3274 a.design_effectiveness,
3275 a.op_effectiveness,
3276 a.object_type,
3277 a.object_version_number
3278 FROM amw_ap_associations a
3279 WHERE a.audit_procedure_id = c_ap_id
3280 AND a.object_type = 'CTRL'
3281 AND a.pk1 = c_control_id
3282 AND a.deletion_date is null
3283 AND a.approval_date is not null;
3284
3285 l_api_name CONSTANT VARCHAR2(30) := 'Insert_AP_Control_Assoc';
3286 l_api_version_number CONSTANT NUMBER := 1.0;
3287 lx_assoc_rec c_assoc_exists%rowtype;
3288 lx_prev_assoc_rec c_prev_assoc_exists%rowtype;
3289 l_date date;
3290 begin
3291 -- Standard Start of API savepoint
3292 SAVEPOINT INSERT_AP_CONTROL_ASSOC_PVT;
3293 -- Standard call to check for call compatibility.
3294 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
3295 p_api_version_number,
3296 l_api_name,
3297 G_PKG_NAME)
3298 THEN
3299 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3300 END IF;
3301
3302 -- Initialize message list if p_init_msg_list is set to TRUE.
3303 IF FND_API.to_Boolean( p_init_msg_list )
3304 THEN
3305 FND_MSG_PUB.initialize;
3306 END IF;
3307 x_return_status := fnd_api.g_ret_sts_success;
3308
3309 fnd_file.put_line(fnd_file.LOG,'Inside insert_ap_control_assoc --> x_return_status: '||x_return_status);
3310
3311 if(p_control_id is null OR p_audit_procedure_id is null)
3312 then
3313 AMW_UTILITY_PVT.Error_Message(p_message_name => 'AMW_UNEXPECT_ERROR',
3314 p_token_name => 'OBJ_TYPE',
3315 p_token_value => G_OBJ_TYPE);
3316 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3317 end if;
3318 if(p_des_eff = 'N' AND p_op_eff = 'N') then
3319 AMW_UTILITY_PVT.Error_Message(p_message_name => 'AMW_ASSOC_AP_EFF_WEBADI_MSG');
3320 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3321 end if;
3322
3323 if(p_approval_date is null)
3324 then
3325 l_date := SYSDATE;
3326 else
3327 l_date := p_approval_date;
3328 end if;
3329
3330 -- check if there is an association with approval_date as null. If there is then
3331 -- update it.
3332 -- Check if there is an association with approval_date as not null and deletion_date as null.
3333 -- set the deletion_date for that and insert a new row.
3334 OPEN c_assoc_exists(p_control_id, p_audit_procedure_id);
3335 FETCH c_assoc_exists INTO lx_assoc_rec;
3336 CLOSE c_assoc_exists;
3337
3338 OPEN c_prev_assoc_exists(p_control_id, p_audit_procedure_id);
3339 FETCH c_prev_assoc_exists INTO lx_prev_assoc_rec;
3340 CLOSE c_prev_assoc_exists;
3341 if(lx_assoc_rec.ap_association_id is not null)
3342 then
3343 -- update the association
3344 UPDATE amw_ap_associations
3345 SET design_effectiveness = p_des_eff,
3346 op_effectiveness = p_op_eff,
3347 object_version_number = object_version_number + 1,
3348 approval_date = p_approval_date
3349 WHERE ap_association_id = lx_assoc_rec.ap_association_id;
3350 elsif(lx_prev_assoc_rec.ap_association_id is not null)
3351 then
3352 -- set deletion_date and insert a new row
3353 UPDATE amw_ap_associations
3354 SET deletion_date = l_date,
3355 object_version_number = object_version_number + 1,
3356 deletion_approval_date = p_approval_date
3357 WHERE ap_association_id = lx_prev_assoc_rec.ap_association_id;
3358
3359 INSERT INTO amw_ap_associations
3360 (ap_association_id
3361 ,last_update_date
3362 ,last_updated_by
3363 ,creation_date
3364 ,created_by
3365 ,last_update_login
3366 ,audit_procedure_id
3367 ,pk1
3368 ,object_type
3369 ,design_effectiveness
3370 ,op_effectiveness
3371 ,object_version_number
3372 ,association_creation_date
3373 ,approval_date
3374 ,deletion_date
3375 ,deletion_approval_date
3376 )
3377 VALUES (amw_ap_associations_s.NEXTVAL
3378 ,SYSDATE
3379 ,p_user_id
3380 ,SYSDATE
3381 ,p_user_id
3382 ,p_user_id
3383 ,p_audit_procedure_id
3384 ,p_control_id
3385 ,'CTRL'
3386 ,p_des_eff
3387 ,p_op_eff
3388 ,1
3389 ,l_date
3390 ,p_approval_date
3391 ,null
3392 ,null
3393 );
3394 else
3395 -- create a new assoc as it does not exist
3396 INSERT INTO amw_ap_associations
3397 (ap_association_id
3398 ,last_update_date
3399 ,last_updated_by
3400 ,creation_date
3401 ,created_by
3402 ,last_update_login
3403 ,audit_procedure_id
3404 ,pk1
3405 ,object_type
3406 ,design_effectiveness
3407 ,op_effectiveness
3408 ,object_version_number
3409 ,association_creation_date
3410 ,approval_date
3411 ,deletion_date
3412 ,deletion_approval_date
3413 )
3414 VALUES (amw_ap_associations_s.NEXTVAL
3415 ,SYSDATE
3416 ,p_user_id
3417 ,SYSDATE
3418 ,p_user_id
3419 ,p_user_id
3420 ,p_audit_procedure_id
3421 ,p_control_id
3422 ,'CTRL'
3423 ,p_des_eff
3424 ,p_op_eff
3425 ,1
3426 ,l_date
3427 ,p_approval_date
3428 ,null
3429 ,null
3430 );
3431 end if;
3432
3433 -- Standard check for p_commit
3434 IF FND_API.to_Boolean( p_commit )
3435 THEN
3436 COMMIT WORK;
3437 END IF;
3438
3439 fnd_file.put_line(fnd_file.LOG,'Done with amw_ap_steps_pkg.insert_row');
3440
3441 exception
3442 WHEN OTHERS THEN
3443 ROLLBACK TO INSERT_AP_CONTROL_ASSOC_PVT;
3444 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3445 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
3446 p_count => x_msg_count,
3447 p_data => x_msg_data);
3448
3449 end insert_ap_control_assoc;
3450
3451 procedure copy_ext_attr(
3452 p_api_version_number IN NUMBER,
3453 p_init_msg_list IN VARCHAR2,
3454 p_commit IN VARCHAR2,
3455 p_validation_level IN NUMBER,
3456 p_from_audit_procedure_id in number,
3457 p_to_audit_procedure_id in number,
3458 x_return_status OUT NOCOPY VARCHAR2,
3459 x_msg_count OUT NOCOPY NUMBER,
3460 x_msg_data OUT NOCOPY VARCHAR2)
3461 IS
3462 l_api_name CONSTANT VARCHAR2(30) := 'copy_ext_attr';
3463 l_api_version_number CONSTANT NUMBER := 1.0;
3464 l_object_id FND_OBJECTS.object_id%TYPE;
3465 l_error_code NUMBER;
3466 l_application_id fnd_application.application_id%TYPE;
3467 l_orig_item_pk_value_pairs EGO_COL_NAME_VALUE_PAIR_ARRAY;
3468 l_new_item_pk_value_pairs EGO_COL_NAME_VALUE_PAIR_ARRAY;
3469 l_commit VARCHAR2(20);
3470 CURSOR c_fnd_object_id(cp_object_name IN VARCHAR2) IS
3471 SELECT object_id
3472 FROM fnd_objects
3473 WHERE obj_name = cp_object_name;
3474
3475 CURSOR c_get_application_id IS
3476 SELECT application_id
3477 FROM fnd_application
3478 WHERE application_short_name = 'AMW';
3479 begin
3480 -- Standard Start of API savepoint
3481 SAVEPOINT COPY_EXT_ATTR;
3482 -- Standard call to check for call compatibility.
3483 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
3484 p_api_version_number,
3485 l_api_name,
3486 G_PKG_NAME)
3487 THEN
3488 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3489 END IF;
3490
3491 -- Initialize message list if p_init_msg_list is set to TRUE.
3492 IF FND_API.to_Boolean( p_init_msg_list )
3493 THEN
3494 FND_MSG_PUB.initialize;
3495 END IF;
3496 x_return_status := fnd_api.g_ret_sts_success;
3497
3498 fnd_file.put_line(fnd_file.LOG,'Inside copy_ext_attr --> x_return_status: '||x_return_status);
3499
3500 if(p_from_audit_procedure_id is null OR p_to_audit_procedure_id is null)
3501 then
3502 AMW_UTILITY_PVT.Error_Message(p_message_name => 'AMW_UNEXPECT_ERROR',
3503 p_token_name => 'OBJ_TYPE',
3504 p_token_value => G_OBJ_TYPE);
3505 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3506 end if;
3507 OPEN c_fnd_object_id (cp_object_name => 'AMW_AUDIT_PROCEDURE');
3508 FETCH c_fnd_object_id INTO l_object_id;
3509 IF c_fnd_object_id%NOTFOUND THEN
3510 l_object_id := -1;
3511 END IF;
3512 CLOSE c_fnd_object_id;
3513
3514 OPEN c_get_application_id;
3515 FETCH c_get_application_id INTO l_application_id;
3516 IF c_get_application_id%NOTFOUND THEN
3517 l_application_id := -1;
3518 END IF;
3519 CLOSE c_get_application_id;
3520
3521 l_orig_item_pk_value_pairs := EGO_COL_NAME_VALUE_PAIR_ARRAY(
3522 EGO_COL_NAME_VALUE_PAIR_OBJ('AUDIT_PROCEDURE_ID', p_from_audit_procedure_id));
3523 l_new_item_pk_value_pairs := EGO_COL_NAME_VALUE_PAIR_ARRAY(
3524 EGO_COL_NAME_VALUE_PAIR_OBJ('AUDIT_PROCEDURE_ID', p_to_audit_procedure_id));
3525
3526 EGO_USER_ATTRS_DATA_PUB.Copy_User_Attrs_Data (
3527 p_api_version => 1.0
3528 ,p_application_id => l_application_id
3529 ,p_object_id => l_object_id
3530 ,p_object_name => 'AMW_AUDIT_PROCEDURE'
3531 ,p_old_pk_col_value_pairs => l_orig_item_pk_value_pairs
3532 ,p_old_dtlevel_col_value_pairs => NULL
3533 ,p_new_pk_col_value_pairs => l_new_item_pk_value_pairs
3534 ,p_new_dtlevel_col_value_pairs => NULL
3535 ,p_new_cc_col_value_pairs => NULL
3536 ,p_commit => FND_API.G_FALSE
3537 ,x_return_status => x_return_status
3538 ,x_errorcode => l_error_code
3539 ,x_msg_count => x_msg_count
3540 ,x_msg_data => x_msg_data
3541 );
3542 EXCEPTION
3543
3544 WHEN FND_API.G_EXC_ERROR THEN
3545
3546 ROLLBACK TO COPY_EXT_ATTR;
3547 x_return_status := G_RET_STS_ERROR;
3548 -- Standard call to get message count and if count=1, get the message
3549 FND_MSG_PUB.Count_And_Get (
3550 p_encoded => G_FALSE,
3551 p_count => x_msg_count,
3552 p_data => x_msg_data);
3553
3554 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3555
3556 ROLLBACK TO COPY_EXT_ATTR;
3557 x_return_status := G_RET_STS_UNEXP_ERROR;
3558 -- Standard call to get message count and if count=1, get the message
3559 FND_MSG_PUB.Count_And_Get (
3560 p_encoded => G_FALSE,
3561 p_count => x_msg_count,
3562 p_data => x_msg_data);
3563
3564 WHEN OTHERS THEN
3565
3566 ROLLBACK TO COPY_EXT_ATTR;
3567 x_return_status := G_RET_STS_UNEXP_ERROR;
3568 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3569 THEN
3570 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
3571 END IF;
3572 -- Standard call to get message count and if count=1, get the message
3573 FND_MSG_PUB.Count_And_Get (
3574 p_encoded => G_FALSE,
3575 p_count => x_msg_count,
3576 p_data => x_msg_data);
3577
3578
3579 END copy_ext_attr;
3580
3581 procedure revise_ap_if_necessary(
3582 p_api_version_number IN NUMBER,
3583 p_init_msg_list IN VARCHAR2,
3584 p_commit IN VARCHAR2,
3585 p_validation_level IN NUMBER,
3586 p_audit_procedure_id IN NUMBER,
3587 x_return_status OUT NOCOPY VARCHAR2,
3588 x_msg_count OUT NOCOPY NUMBER,
3589 x_msg_data OUT NOCOPY VARCHAR2)
3590 IS
3591 l_api_name CONSTANT VARCHAR2(30) := 'revise_ap_if_necessary';
3592 l_api_version_number CONSTANT NUMBER := 1.0;
3593 CURSOR c_revision_exists (l_audit_procedure_id IN NUMBER) IS
3594 SELECT count(*)
3595 FROM amw_audit_procedures_b
3596 GROUP BY audit_procedure_id
3597 HAVING audit_procedure_id=l_audit_procedure_id;
3598
3599 CURSOR c_approval_status (l_audit_procedure_id IN NUMBER) IS
3600 SELECT audit_procedure_rev_id,
3601 approval_status
3602 FROM amw_audit_procedures_b
3603 WHERE audit_procedure_id=l_audit_procedure_id AND
3604 latest_revision_flag='Y';
3605 l_approval_status c_approval_status%ROWTYPE;
3606 l_dummy NUMBER;
3607 CURSOR c_get_rev_id IS
3608 SELECT amw_procedure_rev_s.nextval
3609 FROM dual;
3610 l_audit_procedure_rev_id NUMBER;
3611
3612 BEGIN
3613 -- Standard Start of API savepoint
3614 SAVEPOINT REVISE_AP_IF_NECESSARY;
3615 -- Standard call to check for call compatibility.
3616 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
3617 p_api_version_number,
3618 l_api_name,
3619 G_PKG_NAME)
3620 THEN
3621 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3622 END IF;
3623
3624 -- Initialize message list if p_init_msg_list is set to TRUE.
3625 IF FND_API.to_Boolean( p_init_msg_list )
3626 THEN
3627 FND_MSG_PUB.initialize;
3628 END IF;
3629 x_return_status := fnd_api.g_ret_sts_success;
3630
3631 OPEN c_revision_exists(p_audit_procedure_id);
3632 FETCH c_revision_exists INTO l_dummy;
3633 CLOSE c_revision_exists;
3634
3635 IF l_dummy IS NULL OR l_dummy < 1
3636 THEN
3637 -- no corresponding audit_procedure_id in AMW_AUDIT_PROCEDURES_B is wrong
3638 AMW_UTILITY_PVT.Error_Message(p_message_name => 'AMW_UNEXPECT_ERROR',
3639 p_token_name => 'OBJ_TYPE',
3640 p_token_value => G_OBJ_TYPE);
3641 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3642 ELSIF l_dummy >= 1
3643 THEN
3644 -- has only one record for audit_procedure_id in AMW_AUDIT_PROCEDURES_B with pass-in name
3645 OPEN c_approval_status(p_audit_procedure_id);
3646 FETCH c_approval_status INTO l_approval_status;
3647 CLOSE c_approval_status;
3648
3649 IF l_approval_status.approval_status='P'
3650 THEN
3651 -- this record is Pending Approval, cannot do G_OP_UPDATE or G_OP_REVISE
3652 AMW_UTILITY_PVT.Error_Message(p_message_name => 'AMW_PENDING_CHANGE_ERROR',
3653 p_token_name => 'OBJ_TYPE',
3654 p_token_value => G_OBJ_TYPE);
3655 RAISE FND_API.G_EXC_ERROR;
3656 ELSIF l_approval_status.approval_status='A' OR l_approval_status.approval_status='R'
3657 THEN
3658 OPEN c_get_rev_id;
3659 FETCH c_get_rev_id INTO l_audit_procedure_rev_id;
3660 CLOSE c_get_rev_id;
3661
3662 insert into AMW_AUDIT_PROCEDURES_B (
3663 PROJECT_ID,
3664 ATTRIBUTE10,
3665 ATTRIBUTE11,
3666 ATTRIBUTE12,
3667 ATTRIBUTE13,
3668 ATTRIBUTE14,
3669 ATTRIBUTE15,
3670 OBJECT_VERSION_NUMBER,
3671 APPROVAL_STATUS,
3672 ORIG_SYSTEM_REFERENCE,
3673 REQUESTOR_ID,
3674 ATTRIBUTE6,
3675 ATTRIBUTE7,
3676 ATTRIBUTE8,
3677 ATTRIBUTE9,
3678 SECURITY_GROUP_ID,
3679 AUDIT_PROCEDURE_ID,
3680 AUDIT_PROCEDURE_REV_ID,
3681 AUDIT_PROCEDURE_REV_NUM,
3682 END_DATE,
3683 APPROVAL_DATE,
3684 CURR_APPROVED_FLAG,
3685 LATEST_REVISION_FLAG,
3686 ATTRIBUTE5,
3687 ATTRIBUTE_CATEGORY,
3688 ATTRIBUTE1,
3689 ATTRIBUTE2,
3690 ATTRIBUTE3,
3691 ATTRIBUTE4,
3692 CREATION_DATE,
3693 CREATED_BY,
3694 LAST_UPDATE_DATE,
3695 LAST_UPDATED_BY,
3696 LAST_UPDATE_LOGIN,
3697 CLASSIFICATION
3698 ) (
3699 SELECT PROJECT_ID,
3700 ATTRIBUTE10,
3701 ATTRIBUTE11,
3702 ATTRIBUTE12,
3703 ATTRIBUTE13,
3704 ATTRIBUTE14,
3705 ATTRIBUTE15,
3706 1,
3707 'D',
3708 ORIG_SYSTEM_REFERENCE,
3709 REQUESTOR_ID,
3710 ATTRIBUTE6,
3711 ATTRIBUTE7,
3712 ATTRIBUTE8,
3713 ATTRIBUTE9,
3714 SECURITY_GROUP_ID,
3715 AUDIT_PROCEDURE_ID,
3716 l_audit_procedure_rev_id,
3717 AUDIT_PROCEDURE_REV_NUM + 1,
3718 NULL,
3719 NULL,
3720 'N',
3721 'Y',
3722 ATTRIBUTE5,
3723 ATTRIBUTE_CATEGORY,
3724 ATTRIBUTE1,
3725 ATTRIBUTE2,
3726 ATTRIBUTE3,
3727 ATTRIBUTE4,
3728 SYSDATE,
3729 G_USER_ID,
3730 SYSDATE,
3731 G_USER_ID,
3732 G_LOGIN_ID,
3733 CLASSIFICATION
3734 FROM AMW_AUDIT_PROCEDURES_B
3735 WHERE AUDIT_PROCEDURE_REV_ID = l_approval_status.audit_procedure_rev_id
3736 );
3737
3738 insert into AMW_AUDIT_PROCEDURES_TL (
3739 AUDIT_PROCEDURE_REV_ID,
3740 NAME,
3741 DESCRIPTION,
3742 LAST_UPDATE_DATE,
3743 LAST_UPDATED_BY,
3744 CREATION_DATE,
3745 CREATED_BY,
3746 LAST_UPDATE_LOGIN,
3747 SECURITY_GROUP_ID,
3748 LANGUAGE,
3749 SOURCE_LANG
3750 ) (select
3751 l_audit_procedure_rev_id,
3752 NAME,
3753 DESCRIPTION,
3754 SYSDATE,
3755 G_USER_ID,
3756 SYSDATE,
3757 G_USER_ID,
3758 G_LOGIN_ID,
3759 SECURITY_GROUP_ID,
3760 LANGUAGE,
3761 SOURCE_LANG
3762 from AMW_AUDIT_PROCEDURES_TL
3763 where AUDIT_PROCEDURE_REV_ID = l_approval_status.audit_procedure_rev_id);
3764
3765 UPDATE AMW_AUDIT_PROCEDURES_B
3766 SET LATEST_REVISION_FLAG = 'N',
3767 END_DATE = SYSDATE,
3768 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1
3769 WHERE AUDIT_PROCEDURE_REV_ID = l_approval_status.audit_procedure_rev_id;
3770
3771 END IF; -- end of if:l_approval_status.approval_status
3772 END IF;
3773 EXCEPTION
3774
3775 WHEN FND_API.G_EXC_ERROR THEN
3776
3777 ROLLBACK TO REVISE_AP_IF_NECESSARY;
3778 x_return_status := G_RET_STS_ERROR;
3779 -- Standard call to get message count and if count=1, get the message
3780 FND_MSG_PUB.Count_And_Get (
3781 p_encoded => G_FALSE,
3782 p_count => x_msg_count,
3783 p_data => x_msg_data);
3784
3785 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3786
3787 ROLLBACK TO REVISE_AP_IF_NECESSARY;
3788 x_return_status := G_RET_STS_UNEXP_ERROR;
3789 -- Standard call to get message count and if count=1, get the message
3790 FND_MSG_PUB.Count_And_Get (
3791 p_encoded => G_FALSE,
3792 p_count => x_msg_count,
3793 p_data => x_msg_data);
3794
3795 WHEN OTHERS THEN
3796
3797 ROLLBACK TO REVISE_AP_IF_NECESSARY;
3798 x_return_status := G_RET_STS_UNEXP_ERROR;
3799 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3800 THEN
3801 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
3802 END IF;
3803 -- Standard call to get message count and if count=1, get the message
3804 FND_MSG_PUB.Count_And_Get (
3805 p_encoded => G_FALSE,
3806 p_count => x_msg_count,
3807 p_data => x_msg_data);
3808
3809
3810 END revise_ap_if_necessary;
3811
3812 END AMW_AUDIT_PROCEDURES_PVT;