[Home] [Help]
PACKAGE BODY: APPS.AMW_RISK_PVT
Source
1 PACKAGE BODY AMW_RISK_PVT as
2 /* $Header: amwvrskb.pls 120.0 2005/05/31 23:24:25 appldev noship $ */
3
4 -- ===============================================================
5 -- Package name
6 -- AMW_Risk_PVT
7 -- Purpose
8 -- for Import Risk : Load_Risk (without knowing any risk_id in advance)
9 -- for direct call : Operate_Risk (knowing risk_id or risk_rev_id)
10 -- History
11 -- 7/23/2003 tsho Creates
12 -- 12/09/2004 tsho modify for new column in base table: Classification
13 -- 01/05/2005 tsho add Approve_Risk procedure to approve risk without workflow
14 -- ===============================================================
15
16
17 G_PKG_NAME CONSTANT VARCHAR2(30) := 'AMW_Risk_PVT';
18 G_FILE_NAME CONSTANT VARCHAR2(12) := 'amwvrskb.pls';
19
20
21 -- ===============================================================
22 -- Procedure name
23 -- Load_Risk
24 -- Purpose
25 -- for Import Risk with approval_status 'A' or 'D'
26 -- ===============================================================
27 PROCEDURE Load_Risk(
28 p_api_version_number IN NUMBER,
29 p_init_msg_list IN VARCHAR2 := G_FALSE,
30 p_commit IN VARCHAR2 := G_FALSE,
31 p_validation_level IN NUMBER := G_VALID_LEVEL_FULL,
32 x_return_status OUT NOCOPY VARCHAR2,
33 x_msg_count OUT NOCOPY NUMBER,
34 x_msg_data OUT NOCOPY VARCHAR2,
35 p_risk_rec IN risk_rec_type,
36 x_risk_rev_id OUT NOCOPY NUMBER,
37 x_risk_id OUT NOCOPY NUMBER
38 )
39 IS
40 l_api_name CONSTANT VARCHAR2(30) := 'Load_Risk';
41 l_dummy NUMBER;
42 l_dummy_risk_rec risk_rec_type := NULL;
43
44 CURSOR c_name_exists (l_risk_name IN VARCHAR2) IS
45 SELECT risk_id
46 FROM amw_risks_all_vl
47 WHERE name = l_risk_name;
48 l_risk_id amw_risks_all_vl.risk_id%TYPE;
49
50 CURSOR c_revision_exists (l_risk_id IN NUMBER) IS
51 SELECT count(*)
52 FROM amw_risks_b
53 GROUP BY risk_id
54 HAVING risk_id=l_risk_id;
55
56 CURSOR c_approval_status (l_risk_id IN NUMBER) IS
57 SELECT risk_rev_id,
58 approval_status
59 FROM amw_risks_b
60 WHERE risk_id=l_risk_id AND
61 latest_revision_flag='Y';
62 l_approval_status c_approval_status%ROWTYPE;
63
64
65 BEGIN
66 AMW_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
67 x_return_status := G_RET_STS_SUCCESS;
68
69
70 IF p_risk_rec.approval_status ='P' THEN
71 AMW_UTILITY_PVT.Error_Message(p_message_name => 'AMW_INVALID_STATUS',
72 p_token_name => 'OBJ_TYPE',
73 p_token_value => G_OBJ_TYPE);
74 RAISE FND_API.G_EXC_ERROR;
75 ELSIF p_risk_rec.approval_status ='R' THEN
76 AMW_UTILITY_PVT.Error_Message(p_message_name => 'AMW_INVALID_STATUS',
77 p_token_name => 'OBJ_TYPE',
78 p_token_value => G_OBJ_TYPE);
79 RAISE FND_API.G_EXC_ERROR;
80 ELSIF p_risk_rec.approval_status IS NOT NULL AND p_risk_rec.approval_status <> 'A' AND p_risk_rec.approval_status <> 'D' THEN
81 -- if it's null, the default will be 'D' , other pass-in unwanted data will be Invalid
82 AMW_UTILITY_PVT.Error_Message(p_message_name => 'AMW_INVALID_STATUS',
83 p_token_name => 'OBJ_TYPE',
84 p_token_value => G_OBJ_TYPE);
85 RAISE FND_API.G_EXC_ERROR;
86 END IF;
87
88
89 l_risk_id := NULL;
90 OPEN c_name_exists(p_risk_rec.risk_name);
91 FETCH c_name_exists INTO l_risk_id;
92 CLOSE c_name_exists;
93
94 IF l_risk_id IS NULL THEN
95 -- no existing risk with pass-in risk_name, then call operation with mode G_OP_CREATE
96 Operate_Risk(
97 p_operate_mode => G_OP_CREATE,
98 p_api_version_number => p_api_version_number,
99 p_init_msg_list => p_init_msg_list,
100 p_commit => p_commit,
101 p_validation_level => p_validation_level,
102 x_return_status => x_return_status,
103 x_msg_count => x_msg_count,
104 x_msg_data => x_msg_data,
105 p_risk_rec => p_risk_rec,
106 x_risk_rev_id => x_risk_rev_id,
107 x_risk_id => x_risk_id);
108 IF x_return_status<>G_RET_STS_SUCCESS THEN
109 AMW_UTILITY_PVT.Error_Message(p_message_name => 'AMW_EXE_ERROR',
110 p_token_name => 'OBJ_TYPE',
111 p_token_value => G_OBJ_TYPE);
112 RAISE FND_API.G_EXC_ERROR;
113 END IF;
114
115 ELSE
116 l_dummy_risk_rec := p_risk_rec;
117 l_dummy_risk_rec.risk_id := l_risk_id;
118 l_dummy := NULL;
119 OPEN c_revision_exists(l_risk_id);
120 FETCH c_revision_exists INTO l_dummy;
121 CLOSE c_revision_exists;
122
123 IF l_dummy IS NULL OR l_dummy < 1 THEN
124 -- no corresponding risk_id in AMW_RISKS_B is wrong
125 AMW_UTILITY_PVT.Error_Message(p_message_name => 'AMW_UNEXPECT_ERROR',
126 p_token_name => 'OBJ_TYPE',
127 p_token_value => G_OBJ_TYPE);
128 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
129 ELSIF l_dummy = 1 THEN
130 -- has only one record for risk_id in AMW_RISKS_B with pass-in name
131 OPEN c_approval_status(l_risk_id);
132 FETCH c_approval_status INTO l_approval_status;
133 CLOSE c_approval_status;
134
135 IF l_approval_status.approval_status='P' THEN
136 -- this record is Pending Approval, cannot do G_OP_UPDATE or G_OP_REVISE
137 AMW_UTILITY_PVT.Error_Message(p_message_name => 'AMW_PENDING_CHANGE_ERROR',
138 p_token_name => 'OBJ_TYPE',
139 p_token_value => G_OBJ_TYPE);
140 RAISE FND_API.G_EXC_ERROR;
141 ELSIF l_approval_status.approval_status='D' THEN
142 Operate_Risk(
143 p_operate_mode => G_OP_UPDATE,
144 p_api_version_number => p_api_version_number,
145 p_init_msg_list => p_init_msg_list,
146 p_commit => p_commit,
147 p_validation_level => p_validation_level,
148 x_return_status => x_return_status,
149 x_msg_count => x_msg_count,
150 x_msg_data => x_msg_data,
151 p_risk_rec => l_dummy_risk_rec,
152 x_risk_rev_id => x_risk_rev_id,
153 x_risk_id => x_risk_id);
154 IF x_return_status<>G_RET_STS_SUCCESS THEN
155 AMW_UTILITY_PVT.Error_Message(p_message_name => 'AMW_EXE_ERROR',
156 p_token_name => 'OBJ_TYPE',
157 p_token_value => G_OBJ_TYPE);
158 RAISE FND_API.G_EXC_ERROR;
159 END IF;
160
161 ELSIF l_approval_status.approval_status='A' OR l_approval_status.approval_status='R' THEN
162 Operate_Risk(
163 p_operate_mode => G_OP_REVISE,
164 p_api_version_number => p_api_version_number,
165 p_init_msg_list => p_init_msg_list,
166 p_commit => p_commit,
167 p_validation_level => p_validation_level,
168 x_return_status => x_return_status,
169 x_msg_count => x_msg_count,
170 x_msg_data => x_msg_data,
171 p_risk_rec => l_dummy_risk_rec,
172 x_risk_rev_id => x_risk_rev_id,
173 x_risk_id => x_risk_id);
174 IF x_return_status<>G_RET_STS_SUCCESS THEN
175 AMW_UTILITY_PVT.Error_Message(p_message_name => 'AMW_EXE_ERROR',
176 p_token_name => 'OBJ_TYPE',
177 p_token_value => G_OBJ_TYPE);
178 RAISE FND_API.G_EXC_ERROR;
179 END IF;
180
181 END IF; -- end of if:l_approval_status.approval_status
182 ELSE
183 -- l_dummy > 1 : has revised before
184 Operate_Risk(
185 p_operate_mode => G_OP_REVISE,
186 p_api_version_number => p_api_version_number,
187 p_init_msg_list => p_init_msg_list,
188 p_commit => p_commit,
189 p_validation_level => p_validation_level,
190 x_return_status => x_return_status,
191 x_msg_count => x_msg_count,
192 x_msg_data => x_msg_data,
193 p_risk_rec => l_dummy_risk_rec,
194 x_risk_rev_id => x_risk_rev_id,
195 x_risk_id => x_risk_id);
196 IF x_return_status<>G_RET_STS_SUCCESS THEN
197 AMW_UTILITY_PVT.Error_Message(p_message_name => 'AMW_EXE_ERROR',
198 p_token_name => 'OBJ_TYPE',
199 p_token_value => G_OBJ_TYPE);
200 RAISE FND_API.G_EXC_ERROR;
201 END IF;
202
203 END IF; -- end of if:l_dummy
204
205 END IF; -- end of if:l_risk_id
206
207 AMW_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
208
209 -- Standard call to get message count and if count is 1, get message info.
210 FND_MSG_PUB.Count_And_Get(
211 p_count => x_msg_count,
212 p_data => x_msg_data);
213
214 EXCEPTION
215
216 WHEN FND_API.G_EXC_ERROR THEN
217
218 x_return_status := G_RET_STS_ERROR;
219 -- Standard call to get message count and if count=1, get the message
220 FND_MSG_PUB.Count_And_Get (
221 p_encoded => G_FALSE,
222 p_count => x_msg_count,
223 p_data => x_msg_data);
224
225 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
226
227 x_return_status := G_RET_STS_UNEXP_ERROR;
228 -- Standard call to get message count and if count=1, get the message
229 FND_MSG_PUB.Count_And_Get (
230 p_encoded => G_FALSE,
231 p_count => x_msg_count,
232 p_data => x_msg_data);
233
234 WHEN OTHERS THEN
235
236 x_return_status := G_RET_STS_UNEXP_ERROR;
237 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
238 THEN
239 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
240 END IF;
241 -- Standard call to get message count and if count=1, get the message
242 FND_MSG_PUB.Count_And_Get (
243 p_encoded => G_FALSE,
244 p_count => x_msg_count,
245 p_data => x_msg_data);
246
247 END Load_Risk;
248
249
250
251 -- ===============================================================
252 -- Procedure name
253 -- Operate_Risk
254 -- Purpose
255 -- operate risk depends on the pass-in p_operate_mode:
256 -- G_OP_CREATE
257 -- G_OP_UPDATE
258 -- G_OP_REVISE
259 -- G_OP_DELETE
260 -- Notes
261 -- the G_OP_UPDATE mode here is in business logic meaning,
262 -- not as the same as update in table handler meaning.
263 -- same goes to other p_operate_mode if it happens to
264 -- have similar name.
265 -- ===============================================================
266 PROCEDURE Operate_Risk(
267 p_operate_mode IN VARCHAR2,
268 p_api_version_number IN NUMBER,
269 p_init_msg_list IN VARCHAR2 := G_FALSE,
270 p_commit IN VARCHAR2 := G_FALSE,
271 p_validation_level IN NUMBER := G_VALID_LEVEL_FULL,
272 x_return_status OUT NOCOPY VARCHAR2,
273 x_msg_count OUT NOCOPY NUMBER,
274 x_msg_data OUT NOCOPY VARCHAR2,
275 p_risk_rec IN risk_rec_type,
276 x_risk_rev_id OUT NOCOPY NUMBER,
277 x_risk_id OUT NOCOPY NUMBER
278 )
279 IS
280 l_api_name CONSTANT VARCHAR2(30) := 'Operate_Risk';
281 l_risk_rev_id NUMBER := NULL;
282 l_dummy_risk_rec risk_rec_type;
283
284 CURSOR c_draft_revision (l_risk_id IN NUMBER) IS
285 SELECT risk_rev_id
286 FROM amw_risks_b
287 WHERE risk_id = l_risk_id AND approval_status='D' AND latest_revision_flag='Y';
288
289 BEGIN
290 -- Initialize message list if p_init_msg_list is set to TRUE.
291 IF FND_API.to_Boolean( p_init_msg_list )
292 THEN
293 FND_MSG_PUB.initialize;
294 END IF;
295
296 AMW_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
297
298 IF p_operate_mode = G_OP_CREATE THEN
299 l_dummy_risk_rec := p_risk_rec;
300 l_dummy_risk_rec.object_version_number := 1;
301 l_dummy_risk_rec.risk_rev_num := 1;
302 l_dummy_risk_rec.latest_revision_flag := 'Y';
303
304 IF p_risk_rec.approval_status = 'A' THEN
305 l_dummy_risk_rec.approval_status := 'A';
306 l_dummy_risk_rec.curr_approved_flag := 'Y';
307 l_dummy_risk_rec.approval_date := SYSDATE;
308 ELSE
309 l_dummy_risk_rec.approval_status := 'D';
310 l_dummy_risk_rec.curr_approved_flag := 'N';
311 END IF;
312
313 Create_Risk(
314 p_operate_mode => p_operate_mode,
315 p_api_version_number => p_api_version_number,
316 p_init_msg_list => p_init_msg_list,
317 p_commit => p_commit,
318 p_validation_level => p_validation_level,
319 x_return_status => x_return_status,
320 x_msg_count => x_msg_count,
321 x_msg_data => x_msg_data,
322 p_risk_rec => l_dummy_risk_rec,
323 x_risk_rev_id => x_risk_rev_id,
324 x_risk_id => x_risk_id);
325
326 IF x_return_status<>G_RET_STS_SUCCESS THEN
327 AMW_UTILITY_PVT.Error_Message(p_message_name => 'AMW_EXE_ERROR',
328 p_token_name => 'OBJ_TYPE',
329 p_token_value => G_OBJ_TYPE);
330 RAISE FND_API.G_EXC_ERROR;
331 END IF;
332
333 ELSIF p_operate_mode = G_OP_UPDATE THEN
334 l_dummy_risk_rec := p_risk_rec;
335 l_dummy_risk_rec.curr_approved_flag := 'N';
336 l_dummy_risk_rec.latest_revision_flag := 'Y';
337
338 IF p_risk_rec.approval_status = 'A' THEN
339 l_dummy_risk_rec.approval_status := 'A';
340 l_dummy_risk_rec.curr_approved_flag := 'Y';
341 l_dummy_risk_rec.approval_date := SYSDATE;
342 ELSE
343 l_dummy_risk_rec.approval_status := 'D';
344 l_dummy_risk_rec.curr_approved_flag := 'N';
345 END IF;
346
347
348 Update_Risk(
349 p_operate_mode => p_operate_mode,
350 p_api_version_number => p_api_version_number,
351 p_init_msg_list => p_init_msg_list,
352 p_commit => p_commit,
353 p_validation_level => p_validation_level,
354 x_return_status => x_return_status,
355 x_msg_count => x_msg_count,
356 x_msg_data => x_msg_data,
357 p_risk_rec => l_dummy_risk_rec,
358 x_risk_rev_id => x_risk_rev_id,
359 x_risk_id => x_risk_id);
360
361 IF x_return_status<>G_RET_STS_SUCCESS THEN
362 AMW_UTILITY_PVT.Error_Message(p_message_name => 'AMW_EXE_ERROR',
363 p_token_name => 'OBJ_TYPE',
364 p_token_value => G_OBJ_TYPE);
365 RAISE FND_API.G_EXC_ERROR;
366 END IF;
367
368 ELSIF p_operate_mode = G_OP_REVISE THEN
369 l_risk_rev_id := NULL;
370 OPEN c_draft_revision(p_risk_rec.risk_id);
371 FETCH c_draft_revision INTO l_risk_rev_id;
372 CLOSE c_draft_revision;
373
374 -- has revision with APPROVAL_STATUS='D' exists
375 IF l_risk_rev_id IS NOT NULL THEN
376 l_dummy_risk_rec := p_risk_rec;
377 l_dummy_risk_rec.latest_revision_flag := 'Y';
378
379 IF p_risk_rec.approval_status = 'A' THEN
380 l_dummy_risk_rec.approval_status := 'A';
381 l_dummy_risk_rec.curr_approved_flag := 'Y';
382 l_dummy_risk_rec.approval_date := SYSDATE;
383 ELSE
384 l_dummy_risk_rec.approval_status := 'D';
385 l_dummy_risk_rec.curr_approved_flag := 'N';
386 END IF;
387
388
389 Update_Risk(
390 p_operate_mode => p_operate_mode,
391 p_api_version_number => p_api_version_number,
392 p_init_msg_list => p_init_msg_list,
393 p_commit => p_commit,
394 p_validation_level => p_validation_level,
395 x_return_status => x_return_status,
396 x_msg_count => x_msg_count,
397 x_msg_data => x_msg_data,
398 p_risk_rec => l_dummy_risk_rec,
399 x_risk_rev_id => x_risk_rev_id,
400 x_risk_id => x_risk_id);
401
402 IF x_return_status<>G_RET_STS_SUCCESS THEN
403 AMW_UTILITY_PVT.Error_Message(p_message_name => 'AMW_EXE_ERROR',
404 p_token_name => 'OBJ_TYPE',
405 p_token_value => G_OBJ_TYPE);
406 RAISE FND_API.G_EXC_ERROR;
407 END IF;
408
409 ELSE
410 l_dummy_risk_rec := p_risk_rec;
411
412
413 Revise_Without_Revision_Exists(
414 p_operate_mode => p_operate_mode,
415 p_api_version_number => p_api_version_number,
416 p_init_msg_list => p_init_msg_list,
417 p_commit => p_commit,
418 p_validation_level => p_validation_level,
419 x_return_status => x_return_status,
420 x_msg_count => x_msg_count,
421 x_msg_data => x_msg_data,
422 p_risk_rec => l_dummy_risk_rec,
423 x_risk_rev_id => x_risk_rev_id,
424 x_risk_id => x_risk_id);
425
426 IF x_return_status<>G_RET_STS_SUCCESS THEN
427 AMW_UTILITY_PVT.Error_Message(p_message_name => 'AMW_EXE_ERROR',
428 p_token_name => 'OBJ_TYPE',
429 p_token_value => G_OBJ_TYPE);
430 RAISE FND_API.G_EXC_ERROR;
431 END IF;
432
433 END IF;
434 ELSIF p_operate_mode = G_OP_DELETE THEN
435 Delete_Risk(
436 p_operate_mode => p_operate_mode,
437 p_api_version_number => p_api_version_number,
438 p_init_msg_list => p_init_msg_list,
439 p_commit => p_commit,
440 p_validation_level => p_validation_level,
441 x_return_status => x_return_status,
442 x_msg_count => x_msg_count,
443 x_msg_data => x_msg_data,
444 p_risk_rev_id => p_risk_rec.risk_rev_id,
445 x_risk_id => x_risk_id);
446
447 IF x_return_status<>G_RET_STS_SUCCESS THEN
448 AMW_UTILITY_PVT.Error_Message(p_message_name => 'AMW_EXE_ERROR',
449 p_token_name => 'OBJ_TYPE',
450 p_token_value => G_OBJ_TYPE);
451 RAISE FND_API.G_EXC_ERROR;
452 END IF;
453
454 ELSE
455 AMW_UTILITY_PVT.Error_Message(p_message_name => 'AMW_UNEXPECT_ERROR',
456 p_token_name => 'OBJ_TYPE',
457 p_token_value => G_OBJ_TYPE);
458 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
459 END IF;
460
461 AMW_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
462
463 -- Standard call to get message count and if count is 1, get message info.
464 FND_MSG_PUB.Count_And_Get
465 (p_count => x_msg_count,
466 p_data => x_msg_data);
467
468 EXCEPTION
469
470 WHEN FND_API.G_EXC_ERROR THEN
471 x_return_status := G_RET_STS_ERROR;
472 -- Standard call to get message count and if count=1, get the message
473 FND_MSG_PUB.Count_And_Get (
474 p_encoded => G_FALSE,
475 p_count => x_msg_count,
476 p_data => x_msg_data
477 );
478
479 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
480 x_return_status := G_RET_STS_UNEXP_ERROR;
481 -- Standard call to get message count and if count=1, get the message
482 FND_MSG_PUB.Count_And_Get (
483 p_encoded => G_FALSE,
484 p_count => x_msg_count,
485 p_data => x_msg_data);
486
487 WHEN OTHERS THEN
488 x_return_status := G_RET_STS_UNEXP_ERROR;
489 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
490 THEN
491 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
492 END IF;
493 -- Standard call to get message count and if count=1, get the message
494 FND_MSG_PUB.Count_And_Get (
495 p_encoded => G_FALSE,
496 p_count => x_msg_count,
497 p_data => x_msg_data);
498
499 END Operate_Risk;
500
501
502
503
504 -- ===============================================================
505 -- Procedure name
506 -- Create_Risk
507 -- Purpose
508 -- create risk with specified approval_status,
509 -- if no specified approval_status in pass-in p_risk_rec,
510 -- the default approval_status is set to 'D'.
511 -- ===============================================================
512 PROCEDURE Create_Risk(
513 p_operate_mode IN VARCHAR2,
514 p_api_version_number IN NUMBER,
515 p_init_msg_list IN VARCHAR2 := G_FALSE,
516 p_commit IN VARCHAR2 := G_FALSE,
517 p_validation_level IN NUMBER := G_VALID_LEVEL_FULL,
518
519 x_return_status OUT NOCOPY VARCHAR2,
520 x_msg_count OUT NOCOPY NUMBER,
521 x_msg_data OUT NOCOPY VARCHAR2,
522
523 p_risk_rec IN risk_rec_type,
524 x_risk_rev_id OUT NOCOPY NUMBER,
525 x_risk_id OUT NOCOPY NUMBER
526 )
527 IS
528 L_API_NAME CONSTANT VARCHAR2(30) := 'Create_Risk';
529 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
530 l_return_status_full VARCHAR2(1);
531 l_object_version_number NUMBER := 1;
532 l_RISK_ID NUMBER;
533 l_RISK_REV_ID NUMBER;
534 l_dummy NUMBER;
535 l_risk_rec risk_rec_type;
536 l_dummy_risk_rec risk_rec_type;
537 l_row_id amw_risks_all_vl.row_id%TYPE;
538
539 CURSOR c_rev_id IS
540 SELECT AMW_RISK_REV_ID_S.NEXTVAL
541 FROM dual;
542
543 CURSOR c_rev_id_exists (l_rev_id IN NUMBER) IS
544 SELECT 1
545 FROM AMW_RISKS_B
546 WHERE RISK_REV_ID = l_rev_id;
547
548 CURSOR c_id IS
549 SELECT AMW_RISK_ID_S.NEXTVAL
550 FROM dual;
551
552 CURSOR c_id_exists (l_id IN NUMBER) IS
553 SELECT 1
554 FROM AMW_RISKS_B
555 WHERE RISK_ID = l_id;
556
557 BEGIN
558 -- Standard Start of API savepoint
559 SAVEPOINT CREATE_Risk_PVT;
560
561 -- Standard call to check for call compatibility.
562 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
563 p_api_version_number,
564 l_api_name,
565 G_PKG_NAME)
566 THEN
567 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
568 END IF;
569
570 -- Initialize message list if p_init_msg_list is set to TRUE.
571 IF FND_API.to_Boolean( p_init_msg_list )
572 THEN
573 FND_MSG_PUB.initialize;
574 END IF;
575
576 AMW_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
577
578 AMW_UTILITY_PVT.debug_message('p_operate_mode: ' || p_operate_mode);
579 -- Initialize API return status to SUCCESS
580 x_return_status := G_RET_STS_SUCCESS;
581
582 IF p_risk_rec.RISK_REV_ID IS NULL OR p_risk_rec.RISK_REV_ID = FND_API.g_miss_num THEN
583 LOOP
584 l_dummy := NULL;
585 OPEN c_rev_id;
586 FETCH c_rev_id INTO l_RISK_REV_ID;
587 CLOSE c_rev_id;
588
589 OPEN c_rev_id_exists(l_RISK_REV_ID);
590 FETCH c_rev_id_exists INTO l_dummy;
591 CLOSE c_rev_id_exists;
592 EXIT WHEN l_dummy IS NULL;
593 END LOOP;
594 ELSE
595 l_risk_rev_id := p_risk_rec.risk_rev_id;
596 END IF;
597
598 IF p_risk_rec.RISK_ID IS NULL OR p_risk_rec.RISK_ID = FND_API.g_miss_num THEN
599 LOOP
600 l_dummy := NULL;
601 OPEN c_id;
602 FETCH c_id INTO l_RISK_ID;
603 CLOSE c_id;
604
605 OPEN c_id_exists(l_RISK_ID);
606 FETCH c_id_exists INTO l_dummy;
607 CLOSE c_id_exists;
608 EXIT WHEN l_dummy IS NULL;
609 END LOOP;
610 ELSE
611 l_risk_id := p_risk_rec.risk_id;
612 END IF;
613
614 x_risk_id := l_risk_id;
615 x_risk_rev_id := l_risk_rev_id;
616
617 l_risk_rec := p_risk_rec;
618 l_risk_rec.risk_id := l_risk_id;
619 l_risk_rec.risk_rev_id := l_risk_rev_id;
620
621
622 IF FND_GLOBAL.User_Id IS NULL THEN
623 AMW_UTILITY_PVT.Error_Message(p_message_name => 'USER_PROFILE_MISSING');
624 RAISE FND_API.G_EXC_ERROR;
625 END IF;
626
627 IF (P_validation_level >= G_VALID_LEVEL_FULL) THEN
628 AMW_UTILITY_PVT.debug_message('Private API: Validate_Risk');
629
630 -- Invoke validation procedures
631 Validate_risk(
632 p_operate_mode => p_operate_mode,
633 p_api_version_number => p_api_version_number,
634 p_init_msg_list => G_FALSE,
635 p_validation_level => p_validation_level,
636 p_risk_rec => l_risk_rec,
637 x_risk_rec => l_dummy_risk_rec,
638 x_return_status => x_return_status,
639 x_msg_count => x_msg_count,
640 x_msg_data => x_msg_data);
641 END IF;
642
643 IF x_return_status<>G_RET_STS_SUCCESS THEN
644 AMW_UTILITY_PVT.Error_Message(p_message_name => 'AMW_EXE_ERROR',
645 p_token_name => 'OBJ_TYPE',
646 p_token_value => G_OBJ_TYPE);
647 RAISE FND_API.G_EXC_ERROR;
648 END IF;
649
650
651 AMW_UTILITY_PVT.debug_message( 'Private API: Calling create table handler');
652
653 -- Invoke table handler(AMW_RISKS_PKG.Insert_Row)
654 AMW_UTILITY_PVT.debug_message( 'Private API: Calling AMW_RISKS_PKG.Insert_Row');
655 AMW_RISKS_PKG.Insert_Row(
656 x_rowid => l_row_id,
657 x_name => l_dummy_risk_rec.risk_name,
658 x_description => l_dummy_risk_rec.risk_description,
659 x_risk_id => l_dummy_risk_rec.risk_id,
660 x_last_update_date => SYSDATE,
661 x_last_update_login => G_LOGIN_ID,
662 x_created_by => G_USER_ID,
663 x_last_updated_by => G_USER_ID,
664 x_risk_impact => l_dummy_risk_rec.risk_impact,
665 x_likelihood => l_dummy_risk_rec.likelihood,
666 x_attribute_category => l_dummy_risk_rec.attribute_category,
667 x_attribute1 => l_dummy_risk_rec.attribute1,
668 x_attribute2 => l_dummy_risk_rec.attribute2,
669 x_attribute3 => l_dummy_risk_rec.attribute3,
670 x_attribute4 => l_dummy_risk_rec.attribute4,
671 x_attribute5 => l_dummy_risk_rec.attribute5,
672 x_attribute6 => l_dummy_risk_rec.attribute6,
673 x_attribute7 => l_dummy_risk_rec.attribute7,
674 x_attribute8 => l_dummy_risk_rec.attribute8,
675 x_attribute9 => l_dummy_risk_rec.attribute9,
676 x_attribute10 => l_dummy_risk_rec.attribute10,
677 x_attribute11 => l_dummy_risk_rec.attribute11,
678 x_attribute12 => l_dummy_risk_rec.attribute12,
679 x_attribute13 => l_dummy_risk_rec.attribute13,
680 x_attribute14 => l_dummy_risk_rec.attribute14,
681 x_attribute15 => l_dummy_risk_rec.attribute15,
682 x_security_group_id => l_dummy_risk_rec.security_group_id,
683 x_risk_type => l_dummy_risk_rec.risk_type,
684 x_approval_status => l_dummy_risk_rec.approval_status,
685 x_object_version_number => l_object_version_number,
686 x_approval_date => l_dummy_risk_rec.approval_date,
687 x_creation_date => SYSDATE,
688 x_risk_rev_num => l_dummy_risk_rec.risk_rev_num,
689 x_risk_rev_id => l_dummy_risk_rec.risk_rev_id,
690 x_requestor_id => l_dummy_risk_rec.requestor_id,
691 x_orig_system_reference => l_dummy_risk_rec.orig_system_reference,
692 x_latest_revision_flag => l_dummy_risk_rec.latest_revision_flag,
693 x_end_date => l_dummy_risk_rec.end_date,
694 x_curr_approved_flag => l_dummy_risk_rec.curr_approved_flag,
695 X_MATERIAL => l_dummy_risk_rec.material,
696 X_CLASSIFICATION => l_dummy_risk_rec.classification);
697
698 IF x_return_status <> G_RET_STS_SUCCESS THEN
699 AMW_UTILITY_PVT.Error_Message(p_message_name => 'AMW_EXE_ERROR',
700 p_token_name => 'OBJ_TYPE',
701 p_token_value => G_OBJ_TYPE);
702 RAISE FND_API.G_EXC_ERROR;
703 END IF;
704
705 -- Standard check for p_commit
706 IF FND_API.to_Boolean( p_commit )
707 THEN
708 COMMIT WORK;
709 END IF;
710
711
712 AMW_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
713
714 -- Standard call to get message count and if count is 1, get message info.
715 FND_MSG_PUB.Count_And_Get
716 (p_count => x_msg_count,
717 p_data => x_msg_data);
718
719 EXCEPTION
720 WHEN AMW_UTILITY_PVT.resource_locked THEN
721 x_return_status := G_RET_STS_ERROR;
722 AMW_UTILITY_PVT.Error_Message(p_message_name => 'AMW_API_RESOURCE_LOCKED');
723
724 WHEN FND_API.G_EXC_ERROR THEN
725 ROLLBACK TO CREATE_Risk_PVT;
726 x_return_status := G_RET_STS_ERROR;
727 -- Standard call to get message count and if count=1, get the message
728 FND_MSG_PUB.Count_And_Get (
729 p_encoded => G_FALSE,
730 p_count => x_msg_count,
731 p_data => x_msg_data);
732
733 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
734 ROLLBACK TO CREATE_Risk_PVT;
735 x_return_status := G_RET_STS_UNEXP_ERROR;
736 -- Standard call to get message count and if count=1, get the message
737 FND_MSG_PUB.Count_And_Get (
738 p_encoded => G_FALSE,
739 p_count => x_msg_count,
740 p_data => x_msg_data);
741
742 WHEN OTHERS THEN
743 ROLLBACK TO CREATE_Risk_PVT;
744 x_return_status := G_RET_STS_UNEXP_ERROR;
745 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
746 THEN
747 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
748 END IF;
749 -- Standard call to get message count and if count=1, get the message
750 FND_MSG_PUB.Count_And_Get (
751 p_encoded => G_FALSE,
752 p_count => x_msg_count,
753 p_data => x_msg_data);
754
755 End Create_Risk;
756
757
758
759 -- ===============================================================
760 -- Procedure name
761 -- Update_Risk
762 -- Purpose
763 -- update risk with specified risk_rev_id,
764 -- if no specified risk_rev_id in pass-in p_risk_rec,
765 -- this will update the one with specified risk_id having
766 -- latest_revision_flag='Y' AND approval_status='D'.
767 -- Notes
768 -- if risk_rev_id is not specified, then
769 -- risk_id is a must when calling Update_Risk
770 -- ===============================================================
771 PROCEDURE Update_Risk(
772 p_operate_mode IN VARCHAR2,
773 p_api_version_number IN NUMBER,
774 p_init_msg_list IN VARCHAR2 := G_FALSE,
775 p_commit IN VARCHAR2 := G_FALSE,
776 p_validation_level IN NUMBER := G_VALID_LEVEL_FULL,
777
778 x_return_status OUT NOCOPY VARCHAR2,
779 x_msg_count OUT NOCOPY NUMBER,
780 x_msg_data OUT NOCOPY VARCHAR2,
781
782 p_risk_rec IN risk_rec_type,
783 x_risk_rev_id OUT NOCOPY NUMBER,
784 x_risk_id OUT NOCOPY NUMBER
785 )
786 IS
787 l_api_name CONSTANT VARCHAR2(30) := 'Update_Risk';
788 l_api_version_number CONSTANT NUMBER := 1.0;
789 l_risk_rev_id NUMBER;
790 l_risk_rec risk_rec_type;
791 l_dummy_risk_rec risk_rec_type;
792
793 CURSOR c_target_revision (l_risk_id IN NUMBER) IS
794 SELECT risk_rev_id
795 FROM amw_risks_b
796 WHERE risk_id = l_risk_id AND approval_status='D' AND latest_revision_flag='Y';
797
798 BEGIN
799 -- Standard Start of API savepoint
800 SAVEPOINT UPDATE_Risk_PVT;
801
802 -- Standard call to check for call compatibility.
803 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
804 p_api_version_number,
805 l_api_name,
806 G_PKG_NAME)
807 THEN
808 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
809 END IF;
810
811 -- Initialize message list if p_init_msg_list is set to TRUE.
812 IF FND_API.to_Boolean( p_init_msg_list )
813 THEN
814 FND_MSG_PUB.initialize;
815 END IF;
816
817 AMW_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
818
819
820 -- Initialize API return status to SUCCESS
821 x_return_status := G_RET_STS_SUCCESS;
822
823 AMW_UTILITY_PVT.debug_message('Private API: - Open Cursor to Select');
824
825 -- if no specified target risk_rev_id, find if from risk_id
826 IF p_risk_rec.risk_rev_id IS NULL OR p_risk_rec.risk_rev_id = FND_API.g_miss_num THEN
827 l_risk_rev_id := NULL;
828 OPEN c_target_revision(p_risk_rec.risk_id);
829 FETCH c_target_revision INTO l_risk_rev_id;
830 CLOSE c_target_revision;
831 IF l_risk_rev_id IS NULL THEN
832 x_return_status := G_RET_STS_ERROR;
833 AMW_UTILITY_PVT.debug_message('l_risk_rev_id in Update_Risk is NULL');
834 RAISE FND_API.G_EXC_ERROR;
835 END IF;
836 ELSE
837 l_risk_rev_id := p_risk_rec.risk_rev_id;
838 END IF; -- end of if:p_risk_rec.risk_rev_id
839
840 AMW_UTILITY_PVT.debug_message('l_risk_rev_id:'||l_risk_rev_id);
841
842 x_risk_id := p_risk_rec.risk_id;
843 x_risk_rev_id := l_risk_rev_id;
844
845 l_risk_rec := p_risk_rec;
846 l_risk_rec.risk_rev_id := l_risk_rev_id;
847
848 IF ( P_validation_level >= G_VALID_LEVEL_FULL)
849 THEN
850 AMW_UTILITY_PVT.debug_message('Private API: Validate_Risk');
851
852 -- Invoke validation procedures
853 Validate_risk(
854 p_operate_mode => p_operate_mode,
855 p_api_version_number => p_api_version_number,
856 p_init_msg_list => G_FALSE,
857 p_validation_level => p_validation_level,
858 p_risk_rec => l_risk_rec,
859 x_risk_rec => l_dummy_risk_rec,
860 x_return_status => x_return_status,
861 x_msg_count => x_msg_count,
862 x_msg_data => x_msg_data);
863 END IF;
864
865 IF x_return_status<>G_RET_STS_SUCCESS THEN
866 AMW_UTILITY_PVT.Error_Message(p_message_name => 'AMW_EXE_ERROR',
867 p_token_name => 'OBJ_TYPE',
868 p_token_value => G_OBJ_TYPE);
869 RAISE FND_API.G_EXC_ERROR;
870 END IF;
871
872 -- Invoke table handler(AMW_RISKS_PKG.Update_Row)
873 AMW_RISKS_PKG.Update_Row(
874 x_name => l_dummy_risk_rec.risk_name,
875 x_description => l_dummy_risk_rec.risk_description,
876 x_risk_id => l_dummy_risk_rec.risk_id,
877 x_last_update_date => SYSDATE,
878 x_last_update_login => G_LOGIN_ID,
879 x_last_updated_by => G_USER_ID,
880 x_risk_impact => l_dummy_risk_rec.risk_impact,
881 x_likelihood => l_dummy_risk_rec.likelihood,
882 x_attribute_category => l_dummy_risk_rec.attribute_category,
883 x_attribute1 => l_dummy_risk_rec.attribute1,
884 x_attribute2 => l_dummy_risk_rec.attribute2,
885 x_attribute3 => l_dummy_risk_rec.attribute3,
886 x_attribute4 => l_dummy_risk_rec.attribute4,
887 x_attribute5 => l_dummy_risk_rec.attribute5,
888 x_attribute6 => l_dummy_risk_rec.attribute6,
889 x_attribute7 => l_dummy_risk_rec.attribute7,
890 x_attribute8 => l_dummy_risk_rec.attribute8,
891 x_attribute9 => l_dummy_risk_rec.attribute9,
892 x_attribute10 => l_dummy_risk_rec.attribute10,
893 x_attribute11 => l_dummy_risk_rec.attribute11,
894 x_attribute12 => l_dummy_risk_rec.attribute12,
895 x_attribute13 => l_dummy_risk_rec.attribute13,
896 x_attribute14 => l_dummy_risk_rec.attribute14,
897 x_attribute15 => l_dummy_risk_rec.attribute15,
898 x_security_group_id => l_dummy_risk_rec.security_group_id,
899 x_risk_type => l_dummy_risk_rec.risk_type,
900 x_approval_status => l_dummy_risk_rec.approval_status,
901 x_object_version_number => l_dummy_risk_rec.object_version_number,
902 x_approval_date => l_dummy_risk_rec.approval_date,
903 x_risk_rev_num => l_dummy_risk_rec.risk_rev_num,
904 x_risk_rev_id => l_dummy_risk_rec.risk_rev_id,
905 x_requestor_id => l_dummy_risk_rec.requestor_id,
906 x_orig_system_reference => l_dummy_risk_rec.orig_system_reference,
907 x_latest_revision_flag => l_dummy_risk_rec.latest_revision_flag,
908 x_end_date => l_dummy_risk_rec.end_date,
909 x_curr_approved_flag => l_dummy_risk_rec.curr_approved_flag,
910 X_MATERIAL => l_dummy_risk_rec.material,
911 X_CLASSIFICATION => l_dummy_risk_rec.classification);
912
913 -- Standard check for p_commit
914 IF FND_API.to_Boolean( p_commit )
915 THEN
916 COMMIT WORK;
917 END IF;
918
919
920 AMW_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
921
922 -- Standard call to get message count and if count is 1, get message info.
923 FND_MSG_PUB.Count_And_Get
924 (p_count => x_msg_count,
925 p_data => x_msg_data);
926
927 EXCEPTION
928
929 WHEN AMW_UTILITY_PVT.resource_locked THEN
930 x_return_status := G_RET_STS_ERROR;
931 AMW_UTILITY_PVT.Error_Message(p_message_name => 'AMW_API_RESOURCE_LOCKED');
932
933 WHEN FND_API.G_EXC_ERROR THEN
934 ROLLBACK TO UPDATE_Risk_PVT;
935 x_return_status := G_RET_STS_ERROR;
936 -- Standard call to get message count and if count=1, get the message
937 FND_MSG_PUB.Count_And_Get (
938 p_encoded => G_FALSE,
939 p_count => x_msg_count,
940 p_data => x_msg_data);
941
942 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
943 ROLLBACK TO UPDATE_Risk_PVT;
944 x_return_status := G_RET_STS_UNEXP_ERROR;
945 -- Standard call to get message count and if count=1, get the message
946 FND_MSG_PUB.Count_And_Get (
947 p_encoded => G_FALSE,
948 p_count => x_msg_count,
949 p_data => x_msg_data);
950
951 WHEN OTHERS THEN
952 ROLLBACK TO UPDATE_Risk_PVT;
953 x_return_status := G_RET_STS_UNEXP_ERROR;
954 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
955 THEN
956 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
957 END IF;
958 -- Standard call to get message count and if count=1, get the message
959 FND_MSG_PUB.Count_And_Get (
960 p_encoded => G_FALSE,
961 p_count => x_msg_count,
962 p_data => x_msg_data);
963
964 End Update_Risk;
965
966
967
968
969 -- ===============================================================
970 -- Procedure name
971 -- Delete_Risk
972 -- Purpose
973 -- delete risk with specified risk_rev_id.
974 -- ===============================================================
975 PROCEDURE Delete_Risk(
976 p_operate_mode IN VARCHAR2,
977 p_api_version_number IN NUMBER,
978 p_init_msg_list IN VARCHAR2 := G_FALSE,
979 p_commit IN VARCHAR2 := G_FALSE,
980 p_validation_level IN NUMBER := G_VALID_LEVEL_FULL,
981 x_return_status OUT NOCOPY VARCHAR2,
982 x_msg_count OUT NOCOPY NUMBER,
983 x_msg_data OUT NOCOPY VARCHAR2,
984 p_risk_rev_id IN NUMBER,
985 x_risk_id OUT NOCOPY NUMBER
986 )
987 IS
988 L_API_NAME CONSTANT VARCHAR2(30) := 'Delete_Risk';
989 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
990
991 BEGIN
992 -- Standard Start of API savepoint
993 SAVEPOINT DELETE_Risk_PVT;
994
995 -- Standard call to check for call compatibility.
996 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
997 p_api_version_number,
998 l_api_name,
999 G_PKG_NAME)
1000 THEN
1001 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1002 END IF;
1003
1004 -- Initialize message list if p_init_msg_list is set to TRUE.
1005 IF FND_API.to_Boolean( p_init_msg_list )
1006 THEN
1007 FND_MSG_PUB.initialize;
1008 END IF;
1009
1010 AMW_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
1011
1012
1013 -- Initialize API return status to SUCCESS
1014 x_return_status := G_RET_STS_SUCCESS;
1015
1016 AMW_UTILITY_PVT.debug_message( 'Private API: Calling delete table handler');
1017
1018 -- Invoke table handler(AMW_RISKS_PKG.Delete_Row)
1019 AMW_RISKS_PKG.Delete_Row(
1020 x_RISK_REV_ID => p_RISK_REV_ID);
1021
1022
1023 -- Standard check for p_commit
1024 IF FND_API.to_Boolean( p_commit )
1025 THEN
1026 COMMIT WORK;
1027 END IF;
1028
1029
1030 AMW_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
1031
1032 -- Standard call to get message count and if count is 1, get message info.
1033 FND_MSG_PUB.Count_And_Get
1034 (p_count => x_msg_count,
1035 p_data => x_msg_data);
1036
1037 EXCEPTION
1038
1039 WHEN AMW_UTILITY_PVT.resource_locked THEN
1040 x_return_status := G_RET_STS_ERROR;
1041 AMW_UTILITY_PVT.Error_Message(p_message_name => 'AMW_API_RESOURCE_LOCKED');
1042
1043 WHEN FND_API.G_EXC_ERROR THEN
1044 ROLLBACK TO DELETE_Risk_PVT;
1045 x_return_status := G_RET_STS_ERROR;
1046 -- Standard call to get message count and if count=1, get the message
1047 FND_MSG_PUB.Count_And_Get (
1048 p_encoded => G_FALSE,
1049 p_count => x_msg_count,
1050 p_data => x_msg_data);
1051
1052 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1053 ROLLBACK TO DELETE_Risk_PVT;
1054 x_return_status := G_RET_STS_UNEXP_ERROR;
1055 -- Standard call to get message count and if count=1, get the message
1056 FND_MSG_PUB.Count_And_Get (
1057 p_encoded => G_FALSE,
1058 p_count => x_msg_count,
1059 p_data => x_msg_data);
1060
1061 WHEN OTHERS THEN
1062 ROLLBACK TO DELETE_Risk_PVT;
1063 x_return_status := G_RET_STS_UNEXP_ERROR;
1064 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1065 THEN
1066 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1067 END IF;
1068 -- Standard call to get message count and if count=1, get the message
1069 FND_MSG_PUB.Count_And_Get (
1070 p_encoded => G_FALSE,
1071 p_count => x_msg_count,
1072 p_data => x_msg_data);
1073
1074 End Delete_Risk;
1075
1076
1077
1078 -- ===============================================================
1079 -- Procedure name
1080 -- Revise_Without_Revision_Exists
1081 -- Purpose
1082 -- revise risk with specified risk_id,
1083 -- it'll revise the one having latest_revision_flag='Y'
1084 -- AND approval_status='A' OR 'R' of specified risk_id.
1085 -- the new revision created by this call will have
1086 -- latest_revision_flag='Y', and the approval_status
1087 -- will be set to 'D' if not specified in the p_risk_rec
1088 -- the revisee(the old one) will have latest_revision_flag='N'
1089 -- Note
1090 -- actually the name for Revise_Without_Revision_Exists
1091 -- should be Revise_Without_Draft_Revision_Exists if there's
1092 -- no limitation for the procedure name.
1093 -- ===============================================================
1094 PROCEDURE Revise_Without_Revision_Exists(
1095 p_operate_mode IN VARCHAR2,
1096 p_api_version_number IN NUMBER,
1097 p_init_msg_list IN VARCHAR2 := G_FALSE,
1098 p_commit IN VARCHAR2 := G_FALSE,
1099 p_validation_level IN NUMBER := G_VALID_LEVEL_FULL,
1100
1101 x_return_status OUT NOCOPY VARCHAR2,
1102 x_msg_count OUT NOCOPY NUMBER,
1103 x_msg_data OUT NOCOPY VARCHAR2,
1104
1105 p_risk_rec IN risk_rec_type,
1106 x_risk_rev_id OUT NOCOPY NUMBER,
1107 x_risk_id OUT NOCOPY NUMBER
1108 )
1109 IS
1110 l_api_name CONSTANT VARCHAR2(30) := 'Revise_Without_Revision_Exists';
1111 l_dummy_risk_rec risk_rec_type := NULL;
1112 l_risk_rec risk_rec_type := NULL;
1113 l_risk_description amw_risks_tl.description%TYPE;
1114
1115 -- find the target revision to be revised
1116 CURSOR c_target_revision (l_risk_id IN NUMBER) IS
1117 SELECT risk_rev_id,
1118 risk_rev_num,
1119 object_version_number
1120 FROM amw_risks_b
1121 WHERE risk_id = l_risk_id AND ( approval_status='A' OR approval_status='R') AND latest_revision_flag='Y';
1122 target_revision c_target_revision%ROWTYPE;
1123
1124 BEGIN
1125 -- Standard Start of API savepoint
1126 SAVEPOINT REVISE_Risk_PVT;
1127
1128 -- Initialize message list if p_init_msg_list is set to TRUE.
1129 IF FND_API.to_Boolean( p_init_msg_list )
1130 THEN
1131 FND_MSG_PUB.initialize;
1132 END IF;
1133
1134 AMW_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
1135
1136
1137 -- Initialize API return status to SUCCESS
1138 x_return_status := G_RET_STS_SUCCESS;
1139
1140 OPEN c_target_revision(p_risk_rec.risk_id);
1141 FETCH c_target_revision INTO target_revision;
1142 CLOSE c_target_revision;
1143
1144 -- update the target(latest existing) revision
1145 l_risk_rec.risk_id := p_risk_rec.risk_id;
1146 l_risk_rec.risk_rev_id := target_revision.risk_rev_id;
1147 l_risk_rec.latest_revision_flag := 'N';
1148 -- 05.13.2004 tsho: bug 3595420, need to be consistent with UI
1149 --l_risk_rec.end_date := SYSDATE;
1150 l_risk_rec.object_version_number := target_revision.object_version_number+1;
1151
1152 IF p_risk_rec.approval_status = 'A' THEN
1153 l_risk_rec.curr_approved_flag := 'N';
1154 -- 05.13.2004 tsho: bug 3595420, need to be consistent with UI
1155 l_risk_rec.end_date := SYSDATE;
1156 END IF;
1157
1158 Complete_risk_Rec(
1159 p_risk_rec => l_risk_rec,
1160 x_complete_rec => l_dummy_risk_rec);
1161
1162 l_risk_description := l_dummy_risk_rec.risk_description;
1163
1164 Update_Risk(
1165 p_operate_mode => p_operate_mode,
1166 p_api_version_number => p_api_version_number,
1167 p_init_msg_list => p_init_msg_list,
1168 p_commit => p_commit,
1169 p_validation_level => p_validation_level,
1170 x_return_status => x_return_status,
1171 x_msg_count => x_msg_count,
1172 x_msg_data => x_msg_data,
1173 p_risk_rec => l_dummy_risk_rec,
1174 x_risk_rev_id => x_risk_rev_id,
1175 x_risk_id => x_risk_id);
1176
1177 IF x_return_status <> G_RET_STS_SUCCESS THEN
1178 AMW_UTILITY_PVT.Error_Message(p_message_name => 'AMW_EXE_ERROR',
1179 p_token_name => 'OBJ_TYPE',
1180 p_token_value => G_OBJ_TYPE);
1181 RAISE FND_API.G_EXC_ERROR;
1182 END IF;
1183
1184
1185 x_risk_id := p_risk_rec.risk_id;
1186
1187 -- create the new revision
1188 l_dummy_risk_rec := p_risk_rec;
1189 l_dummy_risk_rec.latest_revision_flag := 'Y';
1190 l_dummy_risk_rec.object_version_number := 1;
1191 l_dummy_risk_rec.risk_rev_num := target_revision.risk_rev_num+1;
1192
1193 IF p_risk_rec.risk_description IS NULL THEN
1194 l_dummy_risk_rec.risk_description := l_risk_description;
1195 END IF;
1196
1197 IF p_risk_rec.approval_status = 'A' THEN
1198 l_dummy_risk_rec.approval_status := 'A';
1199 l_dummy_risk_rec.curr_approved_flag := 'Y';
1200 l_dummy_risk_rec.approval_date := SYSDATE;
1201 ELSE
1202 l_dummy_risk_rec.approval_status := 'D';
1203 -- 05.13.2004 tsho: bug 3595420, need to be consistent with UI
1204 --l_dummy_risk_rec.curr_approved_flag := 'N';
1205 l_dummy_risk_rec.curr_approved_flag := 'R';
1206 END IF;
1207
1208 Create_Risk(
1209 p_operate_mode => p_operate_mode,
1210 p_api_version_number => p_api_version_number,
1211 p_init_msg_list => p_init_msg_list,
1212 p_commit => p_commit,
1213 p_validation_level => p_validation_level,
1214 x_return_status => x_return_status,
1215 x_msg_count => x_msg_count,
1216 x_msg_data => x_msg_data,
1217 p_risk_rec => l_dummy_risk_rec,
1218 x_risk_rev_id => x_risk_rev_id,
1219 x_risk_id => x_risk_id);
1220
1221 IF x_return_status <> G_RET_STS_SUCCESS THEN
1222 AMW_UTILITY_PVT.Error_Message(p_message_name => 'AMW_EXE_ERROR',
1223 p_token_name => 'OBJ_TYPE',
1224 p_token_value => G_OBJ_TYPE);
1225 RAISE FND_API.G_EXC_ERROR;
1226 END IF;
1227
1228 -- Standard check for p_commit
1229 IF FND_API.to_Boolean( p_commit )
1230 THEN
1231 COMMIT WORK;
1232 END IF;
1233
1234 AMW_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
1235
1236 -- Standard call to get message count and if count is 1, get message info.
1237 FND_MSG_PUB.Count_And_Get
1238 (p_count => x_msg_count,
1239 p_data => x_msg_data);
1240
1241 EXCEPTION
1242
1243 WHEN AMW_UTILITY_PVT.resource_locked THEN
1244 x_return_status := G_RET_STS_ERROR;
1245 AMW_UTILITY_PVT.Error_Message(p_message_name => 'AMW_API_RESOURCE_LOCKED');
1246
1247 WHEN FND_API.G_EXC_ERROR THEN
1248 ROLLBACK TO REVISE_Risk_PVT;
1249 x_return_status := G_RET_STS_ERROR;
1250 -- Standard call to get message count and if count=1, get the message
1251 FND_MSG_PUB.Count_And_Get (
1252 p_encoded => G_FALSE,
1253 p_count => x_msg_count,
1254 p_data => x_msg_data);
1255
1256 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1257 ROLLBACK TO REVISE_Risk_PVT;
1258 x_return_status := G_RET_STS_UNEXP_ERROR;
1259 -- Standard call to get message count and if count=1, get the message
1260 FND_MSG_PUB.Count_And_Get (
1261 p_encoded => G_FALSE,
1262 p_count => x_msg_count,
1263 p_data => x_msg_data);
1264
1265 WHEN OTHERS THEN
1266 ROLLBACK TO REVISE_Risk_PVT;
1267 x_return_status := G_RET_STS_UNEXP_ERROR;
1268 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1269 THEN
1270 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1271 END IF;
1272 -- Standard call to get message count and if count=1, get the message
1273 FND_MSG_PUB.Count_And_Get (
1274 p_encoded => G_FALSE,
1275 p_count => x_msg_count,
1276 p_data => x_msg_data);
1277
1278 END Revise_Without_Revision_Exists;
1279
1280
1281
1282 -- ===============================================================
1283 -- Procedure name
1284 -- check_risk_uk_items
1285 -- Purpose
1286 -- check the uniqueness of the items which have been marked
1287 -- as unique in table
1288 -- ===============================================================
1289 PROCEDURE check_risk_uk_items(
1290 p_operate_mode IN VARCHAR2,
1291 p_risk_rec IN risk_rec_type,
1292 x_return_status OUT NOCOPY VARCHAR2
1293 )
1294 IS
1295 l_valid_flag VARCHAR2(1);
1296
1297 BEGIN
1298 x_return_status := G_RET_STS_SUCCESS;
1299
1300 -- 07.23.2003 tsho
1301 -- comment out for performance: since the uniqueness of
1302 -- risk_rev_id and risk_id have been checked when creating
1303 /*
1304 IF p_operate_mode = G_OP_CREATE THEN
1305 l_valid_flag := AMW_UTILITY_PVT.check_uniqueness(
1306 'AMW_RISKS_B',
1307 'RISK_REV_ID = ''' || p_risk_rec.RISK_REV_ID ||''''
1308 );
1309 ELSE
1310 l_valid_flag := AMW_UTILITY_PVT.check_uniqueness(
1311 'AMW_RISKS_B',
1312 'RISK_REV_ID = ''' || p_risk_rec.RISK_REV_ID ||
1313 ''' AND RISK_REV_ID <> ' || p_risk_rec.RISK_REV_ID
1314 );
1315 END IF;
1316 */
1317 END check_risk_uk_items;
1318
1319
1320
1321 -- ===============================================================
1322 -- Procedure name
1323 -- check_risk_req_items
1324 -- Purpose
1325 -- check the requireness of the items which have been marked
1326 -- as NOT NULL in table
1327 -- Note
1328 -- since the standard default with
1329 -- FND_API.G_MISS_XXX v.s. NULL has been changed to:
1330 -- if user want to update to Null, pass in G_MISS_XXX
1331 -- else if user want to update to some value, pass in value
1332 -- else if user doesn't want to update, pass in NULL.
1333 -- Reference
1334 -- http://www-apps.us.oracle.com/atg/performance/
1335 -- Standards and Templates>Business Object API Coding Standards
1336 -- 2.3.1 Differentiating between Missing parameters and Null parameters
1337 -- ===============================================================
1338 PROCEDURE check_risk_req_items(
1339 p_operate_mode IN VARCHAR2,
1340 p_risk_rec IN risk_rec_type,
1341 x_return_status OUT NOCOPY VARCHAR2
1342 )
1343 IS
1344 BEGIN
1345 x_return_status := G_RET_STS_SUCCESS;
1346
1347 IF p_operate_mode = G_OP_CREATE THEN
1348 IF p_risk_rec.risk_impact IS NULL THEN
1349 AMW_UTILITY_PVT.Error_Message(p_message_name => 'AMW_REQUIRE_ITEM_ERROR',
1350 p_token_name => 'ITEM',
1351 p_token_value => 'risk_impact');
1352 x_return_status := G_RET_STS_ERROR;
1353 RAISE FND_API.G_EXC_ERROR;
1354 END IF;
1355
1356 IF p_risk_rec.likelihood IS NULL THEN
1357 AMW_UTILITY_PVT.Error_Message(p_message_name => 'AMW_REQUIRE_ITEM_ERROR',
1358 p_token_name => 'ITEM',
1359 p_token_value => 'likelihood');
1360 x_return_status := G_RET_STS_ERROR;
1361 RAISE FND_API.G_EXC_ERROR;
1362 END IF;
1363
1364 IF p_risk_rec.risk_rev_num IS NULL THEN
1365 AMW_UTILITY_PVT.Error_Message(p_message_name => 'AMW_REQUIRE_ITEM_ERROR',
1366 p_token_name => 'ITEM',
1367 p_token_value => 'risk_rev_num');
1368 x_return_status := G_RET_STS_ERROR;
1369 RAISE FND_API.G_EXC_ERROR;
1370 END IF;
1371
1372 IF p_risk_rec.latest_revision_flag IS NULL THEN
1373 AMW_UTILITY_PVT.Error_Message(p_message_name => 'AMW_REQUIRE_ITEM_ERROR',
1374 p_token_name => 'ITEM',
1375 p_token_value => 'latest_revision_flag');
1376 x_return_status := G_RET_STS_ERROR;
1377 RAISE FND_API.G_EXC_ERROR;
1378 END IF;
1379
1380 IF p_risk_rec.curr_approved_flag IS NULL THEN
1381 AMW_UTILITY_PVT.Error_Message(p_message_name => 'AMW_REQUIRE_ITEM_ERROR',
1382 p_token_name => 'ITEM',
1383 p_token_value => 'curr_approved_flag');
1384 x_return_status := G_RET_STS_ERROR;
1385 RAISE FND_API.G_EXC_ERROR;
1386 END IF;
1387
1388 ELSE
1389 IF p_risk_rec.risk_rev_id = FND_API.g_miss_num THEN
1390 AMW_UTILITY_PVT.Error_Message(p_message_name => 'AMW_REQUIRE_ITEM_ERROR',
1391 p_token_name => 'ITEM',
1392 p_token_value => 'risk_rev_id');
1393 x_return_status := G_RET_STS_ERROR;
1394 RAISE FND_API.G_EXC_ERROR;
1395 END IF;
1396
1397 IF p_risk_rec.risk_id = FND_API.g_miss_num THEN
1398 AMW_UTILITY_PVT.Error_Message(p_message_name => 'AMW_REQUIRE_ITEM_ERROR',
1399 p_token_name => 'ITEM',
1400 p_token_value => 'risk_id');
1401 x_return_status := G_RET_STS_ERROR;
1402 RAISE FND_API.G_EXC_ERROR;
1403 END IF;
1404
1405 IF p_risk_rec.risk_impact = FND_API.g_miss_char THEN
1406 AMW_UTILITY_PVT.Error_Message(p_message_name => 'AMW_REQUIRE_ITEM_ERROR',
1407 p_token_name => 'ITEM',
1408 p_token_value => 'risk_impact');
1409 x_return_status := G_RET_STS_ERROR;
1410 RAISE FND_API.G_EXC_ERROR;
1411 END IF;
1412
1413 IF p_risk_rec.likelihood = FND_API.g_miss_char THEN
1414 AMW_UTILITY_PVT.Error_Message(p_message_name => 'AMW_REQUIRE_ITEM_ERROR',
1415 p_token_name => 'ITEM',
1416 p_token_value => 'likelihood');
1417 x_return_status := G_RET_STS_ERROR;
1418 RAISE FND_API.G_EXC_ERROR;
1419 END IF;
1420
1421 IF p_risk_rec.risk_rev_num = FND_API.g_miss_num THEN
1422 AMW_UTILITY_PVT.Error_Message(p_message_name => 'AMW_REQUIRE_ITEM_ERROR',
1423 p_token_name => 'ITEM',
1424 p_token_value => 'risk_rev_num');
1425 x_return_status := G_RET_STS_ERROR;
1426 RAISE FND_API.G_EXC_ERROR;
1427 END IF;
1428
1429 IF p_risk_rec.latest_revision_flag = FND_API.g_miss_char THEN
1430 AMW_UTILITY_PVT.Error_Message(p_message_name => 'AMW_REQUIRE_ITEM_ERROR',
1431 p_token_name => 'ITEM',
1432 p_token_value => 'latest_revision_flag');
1433 x_return_status := G_RET_STS_ERROR;
1434 RAISE FND_API.G_EXC_ERROR;
1435 END IF;
1436
1437 IF p_risk_rec.curr_approved_flag = FND_API.g_miss_char THEN
1438 AMW_UTILITY_PVT.Error_Message(p_message_name => 'AMW_REQUIRE_ITEM_ERROR',
1439 p_token_name => 'ITEM',
1440 p_token_value => 'curr_approved_flag');
1441 x_return_status := G_RET_STS_ERROR;
1442 RAISE FND_API.G_EXC_ERROR;
1443 END IF;
1444
1445 END IF; -- end of if:p_operate_mode
1446
1447 END check_risk_req_items;
1448
1449
1450
1451 -- ===============================================================
1452 -- Procedure name
1453 -- check_risk_FK_items
1454 -- Purpose
1455 -- check forien key of the items
1456 -- ===============================================================
1457 PROCEDURE check_risk_FK_items(
1458 p_operate_mode IN VARCHAR2,
1459 p_risk_rec IN risk_rec_type,
1460 x_return_status OUT NOCOPY VARCHAR2
1461 )
1462 IS
1463 BEGIN
1464 x_return_status := G_RET_STS_SUCCESS;
1465 END check_risk_FK_items;
1466
1467
1468
1469 -- ===============================================================
1470 -- Procedure name
1471 -- check_risk_Lookup_items
1472 -- Purpose
1473 -- check lookup of the items
1474 -- ===============================================================
1475 PROCEDURE check_risk_Lookup_items(
1476 p_operate_mode IN VARCHAR2,
1477 p_risk_rec IN risk_rec_type,
1478 x_return_status OUT NOCOPY VARCHAR2
1479 )
1480 IS
1481 BEGIN
1482 x_return_status := G_RET_STS_SUCCESS;
1483 END check_risk_Lookup_items;
1484
1485
1486
1487 -- ===============================================================
1488 -- Procedure name
1489 -- Check_risk_Items
1490 -- Purpose
1491 -- check all the necessaries for items
1492 -- Note
1493 -- Check_risk_Items is the container for calling all the
1494 -- other validation procedures on items(check_xxx_Items)
1495 -- the validation on items should be only table column constraints
1496 -- not the business logic validation.
1497 -- ===============================================================
1498 PROCEDURE Check_risk_Items (
1499 p_operate_mode IN VARCHAR2,
1500 P_risk_rec IN risk_rec_type,
1501 x_return_status OUT NOCOPY VARCHAR2
1502 )
1503 IS
1504 BEGIN
1505 -- Check Items Uniqueness API calls
1506 check_risk_uk_items(
1507 p_operate_mode => p_operate_mode,
1508 p_risk_rec => p_risk_rec,
1509 x_return_status => x_return_status);
1510 IF x_return_status <> G_RET_STS_SUCCESS THEN
1511 AMW_UTILITY_PVT.Error_Message(p_message_name => 'AMW_EXE_ERROR',
1512 p_token_name => 'OBJ_TYPE',
1513 p_token_value => G_OBJ_TYPE);
1514 RAISE FND_API.G_EXC_ERROR;
1515 END IF;
1516
1517 -- Check Items Required/NOT NULL API calls
1518 check_risk_req_items(
1519 p_operate_mode => p_operate_mode,
1520 p_risk_rec => p_risk_rec,
1521 x_return_status => x_return_status);
1522 IF x_return_status <> G_RET_STS_SUCCESS THEN
1523 AMW_UTILITY_PVT.Error_Message(p_message_name => 'AMW_EXE_ERROR',
1524 p_token_name => 'OBJ_TYPE',
1525 p_token_value => G_OBJ_TYPE);
1526 RAISE FND_API.G_EXC_ERROR;
1527 END IF;
1528
1529 -- Check Items Foreign Keys API calls
1530 check_risk_FK_items(
1531 p_operate_mode => p_operate_mode,
1532 p_risk_rec => p_risk_rec,
1533 x_return_status => x_return_status);
1534 IF x_return_status <> G_RET_STS_SUCCESS THEN
1535 AMW_UTILITY_PVT.Error_Message(p_message_name => 'AMW_EXE_ERROR',
1536 p_token_name => 'OBJ_TYPE',
1537 p_token_value => G_OBJ_TYPE);
1538 RAISE FND_API.G_EXC_ERROR;
1539 END IF;
1540
1541 -- Check Items Lookups
1542 check_risk_Lookup_items(
1543 p_operate_mode => p_operate_mode,
1544 p_risk_rec => p_risk_rec,
1545 x_return_status => x_return_status);
1546 IF x_return_status <> G_RET_STS_SUCCESS THEN
1547 AMW_UTILITY_PVT.Error_Message(p_message_name => 'AMW_EXE_ERROR',
1548 p_token_name => 'OBJ_TYPE',
1549 p_token_value => G_OBJ_TYPE);
1550 RAISE FND_API.G_EXC_ERROR;
1551 END IF;
1552
1553 END Check_risk_Items;
1554
1555
1556
1557 -- ===============================================================
1558 -- Procedure name
1559 -- Complete_risk_Rec
1560 -- Purpose
1561 -- complete(fill out) the items which are not specified.
1562 -- Note
1563 -- basically, this is called when G_OP_UPDATE, G_OP_REVISE
1564 -- ===============================================================
1565 PROCEDURE Complete_risk_Rec (
1566 p_risk_rec IN risk_rec_type,
1567 x_complete_rec OUT NOCOPY risk_rec_type
1568 )
1569 IS
1570 l_api_name CONSTANT VARCHAR2(30) := 'Complete_risk_Rec';
1571 l_return_status VARCHAR2(1);
1572
1573 CURSOR c_complete IS
1574 SELECT *
1575 FROM amw_risks_b
1576 WHERE risk_rev_id = p_risk_rec.risk_rev_id;
1577 l_risk_rec c_complete%ROWTYPE;
1578
1579
1580 CURSOR c_tl_complete IS
1581 SELECT name,
1582 description
1583 FROM amw_risks_all_vl
1584 WHERE risk_rev_id = p_risk_rec.risk_rev_id;
1585 l_risk_tl_rec c_tl_complete%ROWTYPE;
1586
1587
1588 BEGIN
1589 AMW_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
1590 x_complete_rec := p_risk_rec;
1591
1592 OPEN c_complete;
1593 FETCH c_complete INTO l_risk_rec;
1594 CLOSE c_complete;
1595
1596 OPEN c_tl_complete;
1597 FETCH c_tl_complete INTO l_risk_tl_rec;
1598 CLOSE c_tl_complete;
1599
1600 -- risk_rev_id
1601 IF p_risk_rec.risk_rev_id IS NULL THEN
1602 AMW_UTILITY_PVT.Error_Message(p_message_name => 'AMW_UNEXPECT_ERROR',
1603 p_token_name => 'OBJ_TYPE',
1604 p_token_value => G_OBJ_TYPE);
1605 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1606 END IF;
1607
1608 -- risk_id
1609 IF p_risk_rec.risk_id IS NULL THEN
1610 x_complete_rec.risk_id := l_risk_rec.risk_id;
1611 END IF;
1612
1613 -- risk_name
1614 IF p_risk_rec.risk_name IS NULL THEN
1615 x_complete_rec.risk_name := l_risk_tl_rec.name;
1616 END IF;
1617
1618 -- risk_description
1619 IF p_risk_rec.risk_description IS NULL THEN
1620 x_complete_rec.risk_description := l_risk_tl_rec.description;
1621 END IF;
1622
1623 -- last_update_date
1624 IF p_risk_rec.last_update_date IS NULL THEN
1625 x_complete_rec.last_update_date := l_risk_rec.last_update_date;
1626 END IF;
1627
1628 -- last_update_login
1629 IF p_risk_rec.last_update_login IS NULL THEN
1630 x_complete_rec.last_update_login := l_risk_rec.last_update_login;
1631 END IF;
1632
1633 -- created_by
1634 IF p_risk_rec.created_by IS NULL THEN
1635 x_complete_rec.created_by := l_risk_rec.created_by;
1636 END IF;
1637
1638 -- last_updated_by
1639 IF p_risk_rec.last_updated_by IS NULL THEN
1640 x_complete_rec.last_updated_by := l_risk_rec.last_updated_by;
1641 END IF;
1642
1643 -- risk_impact
1644 IF p_risk_rec.risk_impact IS NULL THEN
1645 x_complete_rec.risk_impact := l_risk_rec.risk_impact;
1646 END IF;
1647
1648 -- likelihood
1649 IF p_risk_rec.likelihood IS NULL THEN
1650 x_complete_rec.likelihood := l_risk_rec.likelihood;
1651 END IF;
1652
1653 -- material
1654 IF p_risk_rec.material IS NULL THEN
1655 x_complete_rec.material := l_risk_rec.material;
1656 END IF;
1657
1658 -- classification
1659 IF p_risk_rec.classification IS NULL THEN
1660 x_complete_rec.classification := l_risk_rec.classification;
1661 END IF;
1662
1663 -- security_group_id
1664 IF p_risk_rec.security_group_id IS NULL THEN
1665 x_complete_rec.security_group_id := l_risk_rec.security_group_id;
1666 END IF;
1667
1668 -- risk_type
1669 IF p_risk_rec.risk_type IS NULL THEN
1670 x_complete_rec.risk_type := l_risk_rec.risk_type;
1671 END IF;
1672
1673 -- approval_status
1674 IF p_risk_rec.approval_status IS NULL THEN
1675 x_complete_rec.approval_status := l_risk_rec.approval_status;
1676 END IF;
1677
1678 -- object_version_number
1679 IF p_risk_rec.object_version_number IS NULL THEN
1680 x_complete_rec.object_version_number := l_risk_rec.object_version_number;
1681 END IF;
1682
1683 -- approval_date
1684 IF p_risk_rec.approval_date IS NULL THEN
1685 x_complete_rec.approval_date := l_risk_rec.approval_date;
1686 END IF;
1687
1688 -- creation_date
1689 IF p_risk_rec.creation_date IS NULL THEN
1690 x_complete_rec.creation_date := l_risk_rec.creation_date;
1691 END IF;
1692
1693 -- risk_rev_num
1694 IF p_risk_rec.risk_rev_num IS NULL THEN
1695 x_complete_rec.risk_rev_num := l_risk_rec.risk_rev_num;
1696 END IF;
1697 AMW_UTILITY_PVT.debug_message('risk_rev_num: ' || x_complete_rec.risk_rev_num);
1698
1699 -- requestor_id
1700 IF p_risk_rec.requestor_id IS NULL THEN
1701 x_complete_rec.requestor_id := l_risk_rec.requestor_id;
1702 END IF;
1703
1704 -- orig_system_reference
1705 IF p_risk_rec.orig_system_reference IS NULL THEN
1706 x_complete_rec.orig_system_reference := l_risk_rec.orig_system_reference;
1707 END IF;
1708
1709 -- latest_revision_flag
1710 IF p_risk_rec.latest_revision_flag IS NULL THEN
1711 x_complete_rec.latest_revision_flag := l_risk_rec.latest_revision_flag;
1712 END IF;
1713
1714 -- end_date
1715 IF p_risk_rec.end_date IS NULL THEN
1716 x_complete_rec.end_date := l_risk_rec.end_date;
1717 END IF;
1718
1719 -- curr_approved_flag
1720 IF p_risk_rec.curr_approved_flag IS NULL THEN
1721 x_complete_rec.curr_approved_flag := l_risk_rec.curr_approved_flag;
1722 END IF;
1723
1724 -- attribute_category
1725 IF p_risk_rec.attribute_category IS NULL THEN
1726 x_complete_rec.attribute_category := l_risk_rec.attribute_category;
1727 END IF;
1728
1729 -- attribute1
1730 IF p_risk_rec.attribute1 IS NULL THEN
1731 x_complete_rec.attribute1 := l_risk_rec.attribute1;
1732 END IF;
1733
1734 -- attribute2
1735 IF p_risk_rec.attribute2 IS NULL THEN
1736 x_complete_rec.attribute2 := l_risk_rec.attribute2;
1737 END IF;
1738
1739 -- attribute3
1740 IF p_risk_rec.attribute3 IS NULL THEN
1741 x_complete_rec.attribute3 := l_risk_rec.attribute3;
1742 END IF;
1743
1744 -- attribute4
1745 IF p_risk_rec.attribute4 IS NULL THEN
1746 x_complete_rec.attribute4 := l_risk_rec.attribute4;
1747 END IF;
1748
1749 -- attribute5
1750 IF p_risk_rec.attribute5 IS NULL THEN
1751 x_complete_rec.attribute5 := l_risk_rec.attribute5;
1752 END IF;
1753
1754 -- attribute6
1755 IF p_risk_rec.attribute6 IS NULL THEN
1756 x_complete_rec.attribute6 := l_risk_rec.attribute6;
1757 END IF;
1758
1759 -- attribute7
1760 IF p_risk_rec.attribute7 IS NULL THEN
1761 x_complete_rec.attribute7 := l_risk_rec.attribute7;
1762 END IF;
1763
1764 -- attribute8
1765 IF p_risk_rec.attribute8 IS NULL THEN
1766 x_complete_rec.attribute8 := l_risk_rec.attribute8;
1767 END IF;
1768
1769 -- attribute9
1770 IF p_risk_rec.attribute9 IS NULL THEN
1771 x_complete_rec.attribute9 := l_risk_rec.attribute9;
1772 END IF;
1773
1774 -- attribute10
1775 IF p_risk_rec.attribute10 IS NULL THEN
1776 x_complete_rec.attribute10 := l_risk_rec.attribute10;
1777 END IF;
1778
1779 -- attribute11
1780 IF p_risk_rec.attribute11 IS NULL THEN
1781 x_complete_rec.attribute11 := l_risk_rec.attribute11;
1782 END IF;
1783
1784 -- attribute12
1785 IF p_risk_rec.attribute12 IS NULL THEN
1786 x_complete_rec.attribute12 := l_risk_rec.attribute12;
1787 END IF;
1788
1789 -- attribute13
1790 IF p_risk_rec.attribute13 IS NULL THEN
1791 x_complete_rec.attribute13 := l_risk_rec.attribute13;
1792 END IF;
1793
1794 -- attribute14
1795 IF p_risk_rec.attribute14 IS NULL THEN
1796 x_complete_rec.attribute14 := l_risk_rec.attribute14;
1797 END IF;
1798
1799 -- attribute15
1800 IF p_risk_rec.attribute15 IS NULL THEN
1801 x_complete_rec.attribute15 := l_risk_rec.attribute15;
1802 END IF;
1803
1804 AMW_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
1805 END Complete_risk_Rec;
1806
1807
1808
1809 -- ===============================================================
1810 -- Procedure name
1811 -- Validate_risk
1812 -- Purpose
1813 -- Validate_risk is the container for calling all the other
1814 -- validation procedures on one record(Validate_xxx_Rec) and
1815 -- the container of validation on items(Check_Risk_Items)
1816 -- Note
1817 -- basically, this should be called before calling table handler
1818 -- ===============================================================
1819 PROCEDURE Validate_risk(
1820 p_operate_mode IN VARCHAR2,
1821 p_api_version_number IN NUMBER,
1822 p_init_msg_list IN VARCHAR2 := G_FALSE,
1823 p_validation_level IN NUMBER := G_VALID_LEVEL_FULL,
1824 p_risk_rec IN risk_rec_type,
1825 x_risk_rec OUT NOCOPY risk_rec_type,
1826 x_return_status OUT NOCOPY VARCHAR2,
1827 x_msg_count OUT NOCOPY NUMBER,
1828 x_msg_data OUT NOCOPY VARCHAR2
1829 )
1830 IS
1831 L_API_NAME CONSTANT VARCHAR2(30) := 'Validate_Risk';
1832 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
1833 l_object_version_number NUMBER;
1834 l_risk_rec risk_rec_type;
1835
1836 BEGIN
1837 -- Standard Start of API savepoint
1838 SAVEPOINT VALIDATE_Risk_;
1839 AMW_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
1840
1841 -- Initialize API return status to SUCCESS
1842 x_return_status := G_RET_STS_SUCCESS;
1843
1844 -- Standard call to check for call compatibility.
1845 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1846 p_api_version_number,
1847 l_api_name,
1848 G_PKG_NAME)
1849 THEN
1850 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1851 END IF;
1852
1853 -- Initialize message list if p_init_msg_list is set to TRUE.
1854 IF FND_API.to_Boolean( p_init_msg_list )
1855 THEN
1856 FND_MSG_PUB.initialize;
1857 END IF;
1858
1859 l_risk_rec := p_risk_rec;
1860 -- 07.21.2003 tsho, only update and revise need complete_risk_rec
1861 IF p_operate_mode = G_OP_UPDATE OR p_operate_mode = G_OP_REVISE THEN
1862 Complete_risk_Rec(
1863 p_risk_rec => p_risk_rec,
1864 x_complete_rec => l_risk_rec);
1865 END IF;
1866
1867
1868 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
1869 Check_risk_Items(
1870 p_operate_mode => p_operate_mode,
1871 p_risk_rec => l_risk_rec,
1872 x_return_status => x_return_status);
1873
1874 IF x_return_status = G_RET_STS_ERROR THEN
1875 RAISE FND_API.G_EXC_ERROR;
1876 ELSIF x_return_status = G_RET_STS_UNEXP_ERROR THEN
1877 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1878 END IF;
1879 END IF;
1880
1881
1882 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
1883 Validate_risk_Rec(
1884 p_operate_mode => p_operate_mode,
1885 p_api_version_number => 1.0,
1886 p_init_msg_list => G_FALSE,
1887 x_return_status => x_return_status,
1888 x_msg_count => x_msg_count,
1889 x_msg_data => x_msg_data,
1890 p_risk_rec => l_risk_rec);
1891
1892 IF x_return_status = G_RET_STS_ERROR THEN
1893 RAISE FND_API.G_EXC_ERROR;
1894 ELSIF x_return_status = G_RET_STS_UNEXP_ERROR THEN
1895 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1896 END IF;
1897 END IF;
1898
1899 x_risk_rec := l_risk_rec;
1900
1901 AMW_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
1902
1903 -- Standard call to get message count and if count is 1, get message info.
1904 FND_MSG_PUB.Count_And_Get
1905 (p_count => x_msg_count,
1906 p_data => x_msg_data);
1907
1908 EXCEPTION
1909
1910 WHEN AMW_UTILITY_PVT.resource_locked THEN
1911 x_return_status := G_RET_STS_ERROR;
1912 AMW_UTILITY_PVT.Error_Message(p_message_name => 'AMW_API_RESOURCE_LOCKED');
1913
1914 WHEN FND_API.G_EXC_ERROR THEN
1915 ROLLBACK TO VALIDATE_Risk_;
1916 x_return_status := G_RET_STS_ERROR;
1917 -- Standard call to get message count and if count=1, get the message
1918 FND_MSG_PUB.Count_And_Get (
1919 p_encoded => G_FALSE,
1920 p_count => x_msg_count,
1921 p_data => x_msg_data);
1922
1923 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1924 ROLLBACK TO VALIDATE_Risk_;
1925 x_return_status := G_RET_STS_UNEXP_ERROR;
1926 -- Standard call to get message count and if count=1, get the message
1927 FND_MSG_PUB.Count_And_Get (
1928 p_encoded => G_FALSE,
1929 p_count => x_msg_count,
1930 p_data => x_msg_data);
1931
1932 WHEN OTHERS THEN
1933 ROLLBACK TO VALIDATE_Risk_;
1934 x_return_status := G_RET_STS_UNEXP_ERROR;
1935 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1936 THEN
1937 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1938 END IF;
1939 -- Standard call to get message count and if count=1, get the message
1940 FND_MSG_PUB.Count_And_Get (
1941 p_encoded => G_FALSE,
1942 p_count => x_msg_count,
1943 p_data => x_msg_data);
1944
1945 End Validate_Risk;
1946
1947
1948
1949 -- ===============================================================
1950 -- Procedure name
1951 -- Validate_risk_rec
1952 -- Purpose
1953 -- check all the necessaries for one record,
1954 -- this includes the cross-items validation
1955 -- Note
1956 -- Validate_risk_rec is the dispatcher of
1957 -- other validation procedures on one record.
1958 -- business logic validation should go here.
1959 -- ===============================================================
1960 PROCEDURE Validate_risk_rec(
1961 p_operate_mode IN VARCHAR2,
1962 p_api_version_number IN NUMBER,
1963 p_init_msg_list IN VARCHAR2 := G_FALSE,
1964 x_return_status OUT NOCOPY VARCHAR2,
1965 x_msg_count OUT NOCOPY NUMBER,
1966 x_msg_data OUT NOCOPY VARCHAR2,
1967 p_risk_rec IN risk_rec_type
1968 )
1969 IS
1970 l_api_name CONSTANT VARCHAR2(30) := 'Validate_Risk_Rec';
1971
1972 BEGIN
1973 -- Initialize message list if p_init_msg_list is set to TRUE.
1974 IF FND_API.to_Boolean( p_init_msg_list )
1975 THEN
1976 FND_MSG_PUB.initialize;
1977 END IF;
1978
1979 AMW_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
1980
1981 -- Initialize API return status to SUCCESS
1982 x_return_status := G_RET_STS_SUCCESS;
1983
1984 IF p_operate_mode = G_OP_CREATE THEN
1985 Validate_create_risk_rec(
1986 x_return_status => x_return_status,
1987 x_msg_count => x_msg_count,
1988 x_msg_data => x_msg_data,
1989 p_risk_rec => p_risk_rec);
1990 IF x_return_status<>G_RET_STS_SUCCESS THEN
1991 AMW_UTILITY_PVT.Error_Message(p_message_name => 'AMW_EXE_ERROR',
1992 p_token_name => 'OBJ_TYPE',
1993 p_token_value => G_OBJ_TYPE);
1994 RAISE FND_API.G_EXC_ERROR;
1995 END IF;
1996
1997 ELSIF p_operate_mode = G_OP_UPDATE THEN
1998 Validate_update_risk_rec(
1999 x_return_status => x_return_status,
2000 x_msg_count => x_msg_count,
2001 x_msg_data => x_msg_data,
2002 p_risk_rec => p_risk_rec);
2003 IF x_return_status<>G_RET_STS_SUCCESS THEN
2004 AMW_UTILITY_PVT.Error_Message(p_message_name => 'AMW_EXE_ERROR',
2005 p_token_name => 'OBJ_TYPE',
2006 p_token_value => G_OBJ_TYPE);
2007 RAISE FND_API.G_EXC_ERROR;
2008 END IF;
2009
2010 ELSIF p_operate_mode = G_OP_REVISE THEN
2011 Validate_revise_risk_rec(
2012 x_return_status => x_return_status,
2013 x_msg_count => x_msg_count,
2014 x_msg_data => x_msg_data,
2015 p_risk_rec => p_risk_rec);
2016 IF x_return_status<>G_RET_STS_SUCCESS THEN
2017 AMW_UTILITY_PVT.Error_Message(p_message_name => 'AMW_EXE_ERROR',
2018 p_token_name => 'OBJ_TYPE',
2019 p_token_value => G_OBJ_TYPE);
2020 RAISE FND_API.G_EXC_ERROR;
2021 END IF;
2022
2023 ELSIF p_operate_mode = G_OP_DELETE THEN
2024 Validate_delete_risk_rec(
2025 x_return_status => x_return_status,
2026 x_msg_count => x_msg_count,
2027 x_msg_data => x_msg_data,
2028 p_risk_rec => p_risk_rec);
2029 IF x_return_status<>G_RET_STS_SUCCESS THEN
2030 AMW_UTILITY_PVT.Error_Message(p_message_name => 'AMW_EXE_ERROR',
2031 p_token_name => 'OBJ_TYPE',
2032 p_token_value => G_OBJ_TYPE);
2033 RAISE FND_API.G_EXC_ERROR;
2034 END IF;
2035
2036 ELSE
2037 AMW_UTILITY_PVT.Error_Message(p_message_name => 'AMW_UNEXPECT_ERROR',
2038 p_token_name => 'OBJ_TYPE',
2039 p_token_value => G_OBJ_TYPE);
2040 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2041 END IF;
2042
2043
2044 AMW_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
2045
2046 -- Standard call to get message count and if count is 1, get message info.
2047 FND_MSG_PUB.Count_And_Get
2048 (p_count => x_msg_count,
2049 p_data => x_msg_data);
2050
2051 END Validate_risk_Rec;
2052
2053
2054
2055
2056 -- ===============================================================
2057 -- Procedure name
2058 -- Validate_create_risk_rec
2059 -- Purpose
2060 -- this is the validation for mode G_OP_CREATE.
2061 -- Note
2062 -- risk name cannot be duplicated in table
2063 -- ===============================================================
2064 PROCEDURE Validate_create_risk_rec(
2065 x_return_status OUT NOCOPY VARCHAR2,
2066 x_msg_count OUT NOCOPY NUMBER,
2067 x_msg_data OUT NOCOPY VARCHAR2,
2068 p_risk_rec IN risk_rec_type
2069 )
2070 IS
2071 l_api_name CONSTANT VARCHAR2(30) := 'Validate_Create_Risk_Rec';
2072 l_dummy NUMBER;
2073
2074 CURSOR c_name_exists (l_risk_name IN VARCHAR2) IS
2075 SELECT 1
2076 FROM amw_risks_all_vl
2077 WHERE name = l_risk_name;
2078
2079 BEGIN
2080 AMW_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
2081
2082 x_return_status := G_RET_STS_SUCCESS;
2083
2084 l_dummy := NULL;
2085 OPEN c_name_exists(p_risk_rec.risk_name);
2086 FETCH c_name_exists INTO l_dummy;
2087 CLOSE c_name_exists;
2088
2089 IF l_dummy IS NOT NULL THEN
2090 AMW_UTILITY_PVT.Error_Message(p_message_name => 'AMW_UNIQUE_ITEM_ERROR',
2091 p_token_name => 'ITEM',
2092 p_token_value => 'risk_name');
2093 x_return_status := G_RET_STS_ERROR;
2094 RAISE FND_API.G_EXC_ERROR;
2095 END IF;
2096
2097 AMW_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
2098
2099 -- Standard call to get message count and if count is 1, get message info.
2100 FND_MSG_PUB.Count_And_Get
2101 (p_count => x_msg_count,
2102 p_data => x_msg_data);
2103
2104 EXCEPTION
2105 WHEN FND_API.G_EXC_ERROR THEN
2106
2107 x_return_status := G_RET_STS_ERROR;
2108 -- Standard call to get message count and if count=1, get the message
2109 FND_MSG_PUB.Count_And_Get (
2110 p_encoded => G_FALSE,
2111 p_count => x_msg_count,
2112 p_data => x_msg_data);
2113
2114 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2115
2116 x_return_status := G_RET_STS_UNEXP_ERROR;
2117 -- Standard call to get message count and if count=1, get the message
2118 FND_MSG_PUB.Count_And_Get (
2119 p_encoded => G_FALSE,
2120 p_count => x_msg_count,
2121 p_data => x_msg_data);
2122
2123 WHEN OTHERS THEN
2124
2125 x_return_status := G_RET_STS_UNEXP_ERROR;
2126 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2127 THEN
2128 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
2129 END IF;
2130 -- Standard call to get message count and if count=1, get the message
2131 FND_MSG_PUB.Count_And_Get (
2132 p_encoded => G_FALSE,
2133 p_count => x_msg_count,
2134 p_data => x_msg_data);
2135
2136 END Validate_create_risk_Rec;
2137
2138
2139
2140 -- ===============================================================
2141 -- Procedure name
2142 -- Validate_update_risk_rec
2143 -- Purpose
2144 -- this is the validation for mode G_OP_UPDATE.
2145 -- Note
2146 -- risk name cannot be duplicated in table.
2147 -- only the risk with approval_status='D' can be use G_OP_UPDATE
2148 -- ===============================================================
2149 PROCEDURE Validate_update_risk_rec(
2150 x_return_status OUT NOCOPY VARCHAR2,
2151 x_msg_count OUT NOCOPY NUMBER,
2152 x_msg_data OUT NOCOPY VARCHAR2,
2153 p_risk_rec IN risk_rec_type
2154 )
2155 IS
2156 l_api_name CONSTANT VARCHAR2(30) := 'Validate_Update_Risk_Rec';
2157 l_dummy NUMBER;
2158
2159 -- c_target_risk is holding the info of target risk which is going to be updated
2160 CURSOR c_target_risk (l_risk_rev_id IN NUMBER) IS
2161 SELECT approval_status
2162 FROM amw_risks_b
2163 WHERE risk_rev_id = l_risk_rev_id;
2164 target_risk c_target_risk%ROWTYPE;
2165
2166 CURSOR c_name_exists (l_risk_name IN VARCHAR2,l_risk_id IN NUMBER) IS
2167 SELECT 1
2168 FROM amw_risks_all_vl
2169 WHERE name = l_risk_name AND risk_id <> l_risk_id;
2170
2171 BEGIN
2172 AMW_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
2173
2174 x_return_status := G_RET_STS_SUCCESS;
2175
2176 -- only approval_status='D' can be updated
2177 OPEN c_target_risk(p_risk_rec.risk_rev_id);
2178 FETCH c_target_risk INTO target_risk;
2179 CLOSE c_target_risk;
2180 IF target_risk.approval_status <> 'D' THEN
2181 x_return_status := G_RET_STS_ERROR;
2182 AMW_UTILITY_PVT.debug_message('approval_status <> D');
2183 END IF;
2184
2185 -- name duplication is not allowed
2186 l_dummy := NULL;
2187 OPEN c_name_exists(p_risk_rec.risk_name,p_risk_rec.risk_id);
2188 FETCH c_name_exists INTO l_dummy;
2189 CLOSE c_name_exists;
2190 IF l_dummy IS NOT NULL THEN
2191 AMW_UTILITY_PVT.debug_message('name exists');
2192 AMW_UTILITY_PVT.Error_Message(p_message_name => 'AMW_UNIQUE_ITEM_ERROR',
2193 p_token_name => 'ITEM',
2194 p_token_value => 'risk_name');
2195 x_return_status := G_RET_STS_ERROR;
2196 RAISE FND_API.G_EXC_ERROR;
2197 END IF;
2198
2199 AMW_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
2200
2201 -- Standard call to get message count and if count is 1, get message info.
2202 FND_MSG_PUB.Count_And_Get
2203 (p_count => x_msg_count,
2204 p_data => x_msg_data);
2205
2206 EXCEPTION
2207
2208 WHEN FND_API.G_EXC_ERROR THEN
2209
2210 x_return_status := G_RET_STS_ERROR;
2211 -- Standard call to get message count and if count=1, get the message
2212 FND_MSG_PUB.Count_And_Get (
2213 p_encoded => G_FALSE,
2214 p_count => x_msg_count,
2215 p_data => x_msg_data);
2216
2217 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2218
2219 x_return_status := G_RET_STS_UNEXP_ERROR;
2220 -- Standard call to get message count and if count=1, get the message
2221 FND_MSG_PUB.Count_And_Get (
2222 p_encoded => G_FALSE,
2223 p_count => x_msg_count,
2224 p_data => x_msg_data);
2225
2226 WHEN OTHERS THEN
2227
2228 x_return_status := G_RET_STS_UNEXP_ERROR;
2229 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2230 THEN
2231 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
2232 END IF;
2233 -- Standard call to get message count and if count=1, get the message
2234 FND_MSG_PUB.Count_And_Get (
2235 p_encoded => G_FALSE,
2236 p_count => x_msg_count,
2237 p_data => x_msg_data);
2238
2239 END Validate_update_risk_Rec;
2240
2241
2242
2243 -- ===============================================================
2244 -- Procedure name
2245 -- Validate_revise_risk_rec
2246 -- Purpose
2247 -- this is the validation for mode G_OP_REVISE.
2248 -- Note
2249 -- changing risk name when revising a risk is not allowed.
2250 -- ===============================================================
2251 PROCEDURE Validate_revise_risk_rec(
2252 x_return_status OUT NOCOPY VARCHAR2,
2253 x_msg_count OUT NOCOPY NUMBER,
2254 x_msg_data OUT NOCOPY VARCHAR2,
2255 p_risk_rec IN risk_rec_type
2256 )
2257 IS
2258 l_api_name CONSTANT VARCHAR2(30) := 'Validate_Revise_Risk_Rec';
2259 l_dummy NUMBER;
2260
2261 -- c_target_risk is holding the info of target risk from amw_risks_b which is going to be revised
2262 CURSOR c_target_risk (l_risk_rev_id IN NUMBER) IS
2263 SELECT approval_status
2264 FROM amw_risks_b
2265 WHERE risk_rev_id = l_risk_rev_id;
2266 target_risk c_target_risk%ROWTYPE;
2267
2268 CURSOR c_get_name (l_risk_rev_id IN NUMBER) IS
2269 SELECT name
2270 FROM amw_risks_all_vl
2271 WHERE risk_rev_id = l_risk_rev_id;
2272 original_risk_name amw_risks_all_vl.name%TYPE;
2273
2274 BEGIN
2275 AMW_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
2276
2277 x_return_status := G_RET_STS_SUCCESS;
2278
2279 -- change the name when revise a risk is not allowed
2280 OPEN c_get_name(p_risk_rec.risk_rev_id);
2281 FETCH c_get_name INTO original_risk_name;
2282 CLOSE c_get_name;
2283 IF original_risk_name <> p_risk_rec.risk_name THEN
2284 x_return_status := G_RET_STS_ERROR;
2285 AMW_UTILITY_PVT.Error_Message(p_message_name => 'AMW_EXE_ERROR',
2286 p_token_name => 'OBJ_TYPE',
2287 p_token_value => G_OBJ_TYPE);
2288 RAISE FND_API.G_EXC_ERROR;
2289 END IF;
2290
2291 AMW_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
2292
2293 -- Standard call to get message count and if count is 1, get message info.
2294 FND_MSG_PUB.Count_And_Get
2295 (p_count => x_msg_count,
2296 p_data => x_msg_data);
2297
2298 EXCEPTION
2299
2300 WHEN FND_API.G_EXC_ERROR THEN
2301
2302 x_return_status := G_RET_STS_ERROR;
2303 -- Standard call to get message count and if count=1, get the message
2304 FND_MSG_PUB.Count_And_Get (
2305 p_encoded => G_FALSE,
2306 p_count => x_msg_count,
2307 p_data => x_msg_data);
2308
2309 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2310
2311 x_return_status := G_RET_STS_UNEXP_ERROR;
2312 -- Standard call to get message count and if count=1, get the message
2313 FND_MSG_PUB.Count_And_Get (
2314 p_encoded => G_FALSE,
2315 p_count => x_msg_count,
2316 p_data => x_msg_data);
2317
2318 WHEN OTHERS THEN
2319
2320 x_return_status := G_RET_STS_UNEXP_ERROR;
2321 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2322 THEN
2323 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
2324 END IF;
2325 -- Standard call to get message count and if count=1, get the message
2326 FND_MSG_PUB.Count_And_Get (
2327 p_encoded => G_FALSE,
2328 p_count => x_msg_count,
2329 p_data => x_msg_data);
2330
2331 END Validate_revise_risk_Rec;
2332
2333
2334
2335 -- ===============================================================
2336 -- Procedure name
2337 -- Validate_delete_risk_rec
2338 -- Purpose
2339 -- this is the validation for mode G_OP_DELETE.
2340 -- Note
2341 -- not implemented yet.
2342 -- need to find out when(approval_status='?') can G_OP_DELETE.
2343 -- ===============================================================
2344 PROCEDURE Validate_delete_risk_rec(
2345 x_return_status OUT NOCOPY VARCHAR2,
2346 x_msg_count OUT NOCOPY NUMBER,
2347 x_msg_data OUT NOCOPY VARCHAR2,
2348 p_risk_rec IN risk_rec_type
2349 )
2350 IS
2351 l_api_name CONSTANT VARCHAR2(30) := 'Validate_Delete_Risk_Rec';
2352 l_dummy NUMBER;
2353
2354 CURSOR c_risk_exists (l_risk_rev_id IN NUMBER) IS
2355 SELECT 1
2356 FROM amw_risks_b
2357 WHERE risk_rev_id = l_risk_rev_id;
2358
2359 BEGIN
2360 AMW_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
2361
2362 x_return_status := G_RET_STS_SUCCESS;
2363
2364 -- can only delete a risk which exists and has APPROVAL_STATUS='''
2365 l_dummy := NULL;
2366 OPEN c_risk_exists(p_risk_rec.risk_rev_id);
2367 FETCH c_risk_exists INTO l_dummy;
2368 CLOSE c_risk_exists;
2369 IF l_dummy IS NULL THEN
2370 AMW_UTILITY_PVT.Error_Message(p_message_name => 'AMW_EXE_ERROR',
2371 p_token_name => 'OBJ_TYPE',
2372 p_token_value => G_OBJ_TYPE);
2373 x_return_status := G_RET_STS_ERROR;
2374 RAISE FND_API.G_EXC_ERROR;
2375 END IF;
2376
2377 AMW_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
2378
2379 -- Standard call to get message count and if count is 1, get message info.
2380 FND_MSG_PUB.Count_And_Get
2381 (p_count => x_msg_count,
2382 p_data => x_msg_data);
2383
2384 EXCEPTION
2385
2386 WHEN FND_API.G_EXC_ERROR THEN
2387
2388 x_return_status := G_RET_STS_ERROR;
2389 -- Standard call to get message count and if count=1, get the message
2390 FND_MSG_PUB.Count_And_Get (
2391 p_encoded => G_FALSE,
2392 p_count => x_msg_count,
2393 p_data => x_msg_data);
2394
2395 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2396
2397 x_return_status := G_RET_STS_UNEXP_ERROR;
2398 -- Standard call to get message count and if count=1, get the message
2399 FND_MSG_PUB.Count_And_Get (
2400 p_encoded => G_FALSE,
2401 p_count => x_msg_count,
2402 p_data => x_msg_data);
2403
2404 WHEN OTHERS THEN
2405
2406 x_return_status := G_RET_STS_UNEXP_ERROR;
2407 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2408 THEN
2409 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
2410 END IF;
2411 -- Standard call to get message count and if count=1, get the message
2412 FND_MSG_PUB.Count_And_Get (
2413 p_encoded => G_FALSE,
2414 p_count => x_msg_count,
2415 p_data => x_msg_data);
2416
2417 END Validate_delete_risk_Rec;
2418
2419
2420 -- ===============================================================
2421 -- Procedure name
2422 -- Approve_Risk
2423 -- Purpose
2424 -- to approve the risk without going through workflow
2425 -- Note
2426 --
2427 -- ===============================================================
2428 PROCEDURE Approve_Risk(
2429 p_risk_rev_id IN NUMBER,
2430 p_init_msg_list IN VARCHAR2 := G_FALSE,
2431 x_return_status OUT NOCOPY VARCHAR2,
2432 x_msg_count OUT NOCOPY NUMBER,
2433 x_msg_data OUT NOCOPY VARCHAR2
2434 )
2435 IS
2436
2437 l_api_name CONSTANT VARCHAR2(30) := 'Approve_Risk';
2438 l_date DATE;
2439
2440 -- find the target revision (previous latest approved one)
2441 l_target_risk_rev_id NUMBER;
2442 CURSOR c_target_revision (l_risk_rev_id IN NUMBER) IS
2443 SELECT risk_rev_id
2444 FROM amw_risks_b
2445 WHERE risk_id = (
2446 SELECT r.risk_id
2447 FROM amw_risks_b r
2448 WHERE r.risk_rev_id = l_risk_rev_id
2449 )
2450 AND curr_approved_flag='Y';
2451
2452 BEGIN
2453 x_return_status := FND_API.G_RET_STS_SUCCESS;
2454
2455 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2456 FND_MSG_PUB.initialize;
2457 END IF;
2458
2459 IF G_USER_ID IS NULL THEN
2460 AMW_Utility_PVT.Error_Message(p_message_name => 'USER_PROFILE_MISSING');
2461 RAISE FND_API.G_EXC_ERROR;
2462 END IF;
2463
2464 -- 01.05.2005 tsho: make the date consistent for approval_date, update_date....etc
2465 l_date := sysdate;
2466 l_target_risk_rev_id := null;
2467
2468 OPEN c_target_revision(p_risk_rev_id);
2469 FETCH c_target_revision INTO l_target_risk_rev_id;
2470 CLOSE c_target_revision;
2471
2472 IF (l_target_risk_rev_id IS NOT NULL) THEN
2473 -- update the previous latest approved revision of specified risk
2474 update amw_risks_b
2475 set curr_approved_flag='N'
2476 ,latest_revision_flag ='N'
2477 ,last_update_date=l_date
2478 ,last_updated_by=G_USER_ID
2479 ,last_update_login=G_LOGIN_ID
2480 ,end_date=l_date
2481 where risk_rev_id = l_target_risk_rev_id;
2482 END IF; -- end of if: _target_risk_rev_id IS NOT NULL
2483
2484 -- approve the specified risk by risk_rev_id
2485 update amw_risks_b
2486 set approval_status='A'
2487 ,curr_approved_flag='Y'
2488 ,latest_revision_flag ='Y'
2489 ,approval_date=l_date
2490 ,last_update_date=l_date
2491 ,last_updated_by=G_USER_ID
2492 ,last_update_login=G_LOGIN_ID
2493 where risk_rev_id=p_risk_rev_id;
2494
2495 EXCEPTION
2496 WHEN FND_API.G_EXC_ERROR THEN
2497 ROLLBACK;
2498 x_return_status := FND_API.G_RET_STS_ERROR;
2499 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,p_count => x_msg_count,p_data => x_msg_data);
2500
2501 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2502 ROLLBACK;
2503 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2504 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,p_count => x_msg_count,p_data => x_msg_data);
2505
2506 WHEN OTHERS THEN
2507 ROLLBACK;
2508 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2509 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2510 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,l_api_name);
2511 END IF;
2512 FND_MSG_PUB.Count_And_Get (p_encoded => FND_API.G_FALSE,p_count => x_msg_count,p_data => x_msg_data);
2513 END Approve_Risk;
2514
2515
2516 -- ----------------------------------------------------------------------
2517 END AMW_Risk_PVT;