[Home] [Help]
PACKAGE BODY: APPS.PV_PRGM_BENEFITS_PVT
Source
1 PACKAGE BODY PV_PRGM_BENEFITS_PVT as
2 /* $Header: pvxvppbb.pls 120.1 2006/07/25 17:49:51 dgottlie noship $ */
3 -- ===============================================================
4 -- Start of Comments
5 -- Package name
6 -- PV_PRGM_BENEFITS_PVT
7 -- Purpose
8 --
9 -- History
10 -- 28-FEB-2002 Jessica.Lee Created
11 -- 1-APR-2002 Peter.Nixon Modified
12 -- Changed benefit_id NUMBER to benefit_code VARCHAR2
13 -- 24-SEP-2003 Karen.Tsao Modified for 11.5.10
14 -- 02-OCT-2003 Karen.Tsao Update the Create_Prgm_Benefits, Update_Prgm_Benefits,
15 -- and Complete_Rec with three new column responsibility_id
16 -- 06-NOV-2003 Karen.Tsao Took out column responsibility_id
17 -- NOTE
18 --
19 -- End of Comments
20 -- ===============================================================
21
22
23 G_PKG_NAME CONSTANT VARCHAR2(30) := 'PV_PRGM_BENEFITS_PVT';
24 G_FILE_NAME CONSTANT VARCHAR2(12) := 'pvxvpbsb.pls';
25
26
27 PV_DEBUG_HIGH_ON CONSTANT boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);
28 PV_DEBUG_LOW_ON CONSTANT boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
29 PV_DEBUG_MEDIUM_ON CONSTANT boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM);
30
31 PROCEDURE Create_Prgm_Benefits(
32 p_api_version_number IN NUMBER
33 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
34 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
35 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
36
37 ,x_return_status OUT NOCOPY VARCHAR2
38 ,x_msg_count OUT NOCOPY NUMBER
39 ,x_msg_data OUT NOCOPY VARCHAR2
40
41 ,p_prgm_benefits_rec IN prgm_benefits_rec_type := g_miss_prgm_benefits_rec
42 ,x_program_benefits_id OUT NOCOPY NUMBER
43 )
44
45 IS
46 l_api_version_number CONSTANT NUMBER := 1.0;
47 l_api_name CONSTANT VARCHAR2(30) := 'Create_Prgm_Benefits';
48 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
49
50 l_return_status VARCHAR2(1);
51 l_prgm_benefits_rec prgm_benefits_rec_type := p_prgm_benefits_rec;
52
53 l_object_version_number NUMBER := 1;
54 l_uniqueness_check VARCHAR2(1);
55
56 -- Cursor to get the sequence for pv_program_benefits_id
57 CURSOR c_program_benefits_id_seq IS
58 SELECT PV_PROGRAM_BENEFITS_S.NEXTVAL
59 FROM dual;
60
61
62 -- Cursor to validate the uniqueness
63 CURSOR c_prgm_benefits_id_seq_exists (l_id IN NUMBER) IS
64 SELECT 'X'
65 FROM PV_PROGRAM_BENEFITS
66 WHERE PROGRAM_BENEFITS_ID = l_id;
67
68 BEGIN
69 ---------------Initialize --------------------
70 -- Standard Start of API savepoint
71 SAVEPOINT CREATE_Prgm_Benefits_PVT;
72
73 -- Initialize message list if p_init_msg_list is set to TRUE.
74 IF FND_API.to_Boolean( p_init_msg_list )
75 THEN
76 FND_MSG_PUB.initialize;
77 END IF;
78
79 -- Standard call to check for call compatibility.
80 IF NOT FND_API.Compatible_API_Call (
81 l_api_version_number
82 ,p_api_version_number
83 ,l_api_name
84 ,G_PKG_NAME
85 )
86 THEN
87 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
88 END IF;
89
90 -- Debug Message
91 IF (PV_DEBUG_HIGH_ON) THEN
92
93 PVX_UTILITY_PVT.debug_message('Private API: ' || l_api_name || ' - start');
94 END IF;
95
96
97 -- Initialize API return status to SUCCESS
98 x_return_status := FND_API.G_RET_STS_SUCCESS;
99
100 --------------- validate -------------------------
101
102 IF (PV_DEBUG_HIGH_ON) THEN
103
104
105
106 PVX_Utility_PVT.debug_message(l_full_name ||': validate');
107
108 END IF;
109
110 IF FND_GLOBAL.User_Id IS NULL THEN
111 FND_MESSAGE.set_name('PV', 'PV_API_USER_PROFILE_MISSING');
112 FND_MSG_PUB.add;
113 RAISE FND_API.G_EXC_ERROR;
114 END IF;
115
116
117 IF l_prgm_benefits_rec.program_benefits_id IS NULL OR
118 l_prgm_benefits_rec.program_benefits_id = FND_API.g_miss_num THEN
119 LOOP
120 -- Get the identifier
121 OPEN c_program_benefits_id_seq;
122 FETCH c_program_benefits_id_seq INTO l_prgm_benefits_rec.program_benefits_id;
123 CLOSE c_program_benefits_id_seq;
124
125 -- Check the uniqueness of the identifier
126 OPEN c_prgm_benefits_id_seq_exists(l_prgm_benefits_rec.program_benefits_id);
127 FETCH c_prgm_benefits_id_seq_exists INTO l_uniqueness_check;
128 -- Exit when the identifier uniqueness is established
129 EXIT WHEN c_prgm_benefits_id_seq_exists%ROWCOUNT = 0;
130 CLOSE c_prgm_benefits_id_seq_exists;
131 END LOOP;
132 END IF;
133
134 -- Debug message
135 IF (PV_DEBUG_HIGH_ON) THEN
136
137 PVX_UTILITY_PVT.debug_message('Private API: ' || l_full_name || ' - program_benefits_id = '|| l_prgm_benefits_rec.program_benefits_id);
138 END IF;
139
140 -- Populate the default required items
141 l_prgm_benefits_rec.last_update_date := SYSDATE;
142 l_prgm_benefits_rec.last_updated_by := FND_GLOBAL.user_id;
143 l_prgm_benefits_rec.creation_date := SYSDATE;
144 l_prgm_benefits_rec.created_by := FND_GLOBAL.user_id;
145 l_prgm_benefits_rec.last_update_login := FND_GLOBAL.conc_login_id;
146 l_prgm_benefits_rec.object_version_number := l_object_version_number;
147
148 IF ( P_validation_level >= FND_API.G_VALID_LEVEL_FULL) THEN
149 -- Debug message
150 IF (PV_DEBUG_HIGH_ON) THEN
151
152 PVX_UTILITY_PVT.debug_message('Private API: ' || l_full_name || ' - Validate_Prgm_Benefits');
153 END IF;
154
155
156 -- Invoke validation procedures
157 Validate_Prgm_Benefits(
158 p_api_version_number => 1.0
159 ,p_init_msg_list => FND_API.G_FALSE
160 ,p_validation_level => p_validation_level
161 ,p_validation_mode => JTF_PLSQL_API.g_create
162 ,p_prgm_benefits_rec => l_prgm_benefits_rec
163 ,x_return_status => x_return_status
164 ,x_msg_count => x_msg_count
165 ,x_msg_data => x_msg_data
166 );
167 -- Debug message
168 IF (PV_DEBUG_HIGH_ON) THEN
169
170 PVX_UTILITY_PVT.debug_message('Private API: ' || l_api_name || ' - Validate_Prgm_Benefits return_status = ' || x_return_status );
171 END IF;
172
173 END IF;
174
175 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
176 RAISE FND_API.G_EXC_ERROR;
177 END IF;
178
179 IF x_return_status = FND_API.g_ret_sts_error THEN
180 RAISE FND_API.g_exc_error;
181 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
182 RAISE FND_API.g_exc_unexpected_error;
183 END IF;
184
185 -- Debug Message
186 IF (PV_DEBUG_HIGH_ON) THEN
187
188 PVX_UTILITY_PVT.debug_message( 'Private API:' || l_full_name || ' - Calling create table handler');
189 END IF;
190
191 -- Invoke table handler(PV_PRGM_BENEFITS_PKG.Insert_Row)
192 PV_PRGM_BENEFITS_PKG.Insert_Row(
193 px_program_benefits_id => l_prgm_benefits_rec.program_benefits_id
194 ,p_program_id => l_prgm_benefits_rec.program_id
195 ,p_benefit_code => l_prgm_benefits_rec.benefit_code
196 ,p_benefit_id => l_prgm_benefits_rec.benefit_id
197 ,p_benefit_type_code => l_prgm_benefits_rec.benefit_type_code
198 ,p_delete_flag => l_prgm_benefits_rec.delete_flag
199 ,p_last_update_login => l_prgm_benefits_rec.last_update_login
200 ,p_object_version_number => l_object_version_number
201 ,p_last_update_date => l_prgm_benefits_rec.last_update_date
202 ,p_last_updated_by => l_prgm_benefits_rec.last_updated_by
203 ,p_created_by => l_prgm_benefits_rec.created_by
204 ,p_creation_date => l_prgm_benefits_rec.creation_date
205 );
206
207 x_program_benefits_id := l_prgm_benefits_rec.program_benefits_id;
208
209 IF l_return_status = FND_API.g_ret_sts_error THEN
210 RAISE FND_API.g_exc_error;
211 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
212 RAISE FND_API.g_exc_unexpected_error;
213 END IF;
214
215 FND_MSG_PUB.count_and_get(
216 p_encoded => FND_API.g_false
217 ,p_count => x_msg_count
218 ,p_data => x_msg_data
219 );
220
221 -- Debug Message
222 IF (PV_DEBUG_HIGH_ON) THEN
223
224 PVX_UTILITY_PVT.debug_message('Private API: ' || l_api_name || ' - end');
225 END IF;
226
227 -- Standard check for p_commit
228 IF FND_API.to_Boolean( p_commit ) THEN
229 COMMIT WORK;
230 END IF;
231
232
233 EXCEPTION
234 WHEN FND_API.G_EXC_ERROR THEN
235 ROLLBACK TO CREATE_PRGM_BENEFITS_PVT;
236 x_return_status := FND_API.G_RET_STS_ERROR;
237 -- Standard call to get message count and if count=1, get the message
238 FND_MSG_PUB.Count_And_Get (
239 p_encoded => FND_API.G_FALSE
240 ,p_count => x_msg_count
241 ,p_data => x_msg_data
242 );
243
244 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
245 ROLLBACK TO CREATE_PRGM_BENEFITS_PVT;
246 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
247 -- Standard call to get message count and if count=1, get the message
248 FND_MSG_PUB.Count_And_Get (
249 p_encoded => FND_API.G_FALSE
250 ,p_count => x_msg_count
251 ,p_data => x_msg_data
252 );
253
254 WHEN OTHERS THEN
255 ROLLBACK TO CREATE_Prgm_Benefits_PVT;
256 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
257 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
258 THEN
259 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
260 END IF;
261
262 -- Standard call to get message count and if count=1, get the message
263 FND_MSG_PUB.Count_And_Get (
264 p_encoded => FND_API.G_FALSE
265 ,p_count => x_msg_count
266 ,p_data => x_msg_data
267 );
268
269 END Create_Prgm_Benefits;
270
271
272 PROCEDURE Update_Prgm_Benefits(
273 p_api_version_number IN NUMBER
274 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
275 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
276 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
277
278 ,x_return_status OUT NOCOPY VARCHAR2
279 ,x_msg_count OUT NOCOPY NUMBER
280 ,x_msg_data OUT NOCOPY VARCHAR2
281
282 ,p_prgm_benefits_rec IN prgm_benefits_rec_type
283 )
284
285 IS
286
287 CURSOR c_get_prgm_benefits(cv_program_benefits_id NUMBER) IS
288 SELECT *
289 FROM PV_PROGRAM_BENEFITS
290 WHERE PROGRAM_BENEFITS_ID = cv_program_benefits_id;
291
292 l_api_name CONSTANT VARCHAR2(30) := 'Update_Prgm_Benefits';
293 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
294 l_api_version_number CONSTANT NUMBER := 1.0;
295
296 -- Local Variables
297 l_ref_prgm_benefits_rec c_get_Prgm_Benefits%ROWTYPE ;
298 l_tar_prgm_benefits_rec PV_PRGM_BENEFITS_PVT.prgm_benefits_rec_type := p_prgm_benefits_rec;
299 l_rowid ROWID;
300
301 BEGIN
302 ---------Initialize ------------------
303
304 -- Standard Start of API savepoint
305 SAVEPOINT UPDATE_Prgm_Benefits_PVT;
306
307 -- Standard call to check for call compatibility.
308 IF NOT FND_API.Compatible_API_Call (l_api_version_number
309 ,p_api_version_number
310 ,l_api_name
311 ,G_PKG_NAME
312 )
313 THEN
314 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
315 END IF;
316
317 -- Initialize message list if p_init_msg_list is set to TRUE.
318 IF FND_API.to_Boolean( p_init_msg_list )
319 THEN
320 FND_MSG_PUB.initialize;
321 END IF;
322
323 -- Initialize API return status to SUCCESS
324 x_return_status := FND_API.G_RET_STS_SUCCESS;
325
326 OPEN c_get_Prgm_Benefits( l_tar_prgm_benefits_rec.program_benefits_id);
327 FETCH c_get_Prgm_Benefits INTO l_ref_prgm_benefits_rec ;
328
329 IF ( c_get_Prgm_Benefits%NOTFOUND) THEN
330 FND_MESSAGE.set_name('PV', 'PV_API_MISSING_ENTITY');
331 FND_MESSAGE.set_token('MODE','Update');
332 FND_MESSAGE.set_token('ENTITY','Program_Benefits');
333 FND_MESSAGE.set_token('ID',TO_CHAR(l_tar_prgm_benefits_rec.program_benefits_id));
334 FND_MSG_PUB.ADD;
335 RAISE FND_API.G_EXC_ERROR;
336 END IF;
337
338 -- Debug Message
339 IF (PV_DEBUG_HIGH_ON) THEN
340
341 PVX_UTILITY_PVT.debug_message('Private API: '||l_full_name||' - Close Cursor');
342 END IF;
343 CLOSE c_get_Prgm_Benefits;
344
345 If (l_tar_prgm_benefits_rec.object_version_number is NULL or
346 l_tar_prgm_benefits_rec.object_version_number = FND_API.G_MISS_NUM ) THEN
347
348 FND_MESSAGE.set_name('PV', 'PV_API_VERSION_MISSING');
349 FND_MESSAGE.set_token('COLUMN','OBJECT_VERSION_NUMBER');
350 FND_MSG_PUB.add;
351 RAISE FND_API.G_EXC_ERROR;
352 END IF;
353
354 -- Check Whether record has been changed by someone else
355 IF (l_tar_prgm_benefits_rec.object_version_number <> l_ref_prgm_benefits_rec.object_version_number) THEN
356 FND_MESSAGE.set_name('PV', 'PV_API_RECORD_CHANGED');
357 FND_MESSAGE.set_token('VALUE','PROGRAM_BENEFITS');
358 FND_MSG_PUB.ADD;
359 RAISE FND_API.G_EXC_ERROR;
360 END IF;
361
362 IF ( p_validation_level >= FND_API.G_VALID_LEVEL_FULL) THEN
363 -- Debug message
364 IF (PV_DEBUG_HIGH_ON) THEN
365
366 PVX_UTILITY_PVT.debug_message('Private API: '||l_full_name||' - Validate_Prgm_Benefits');
367 END IF;
368
369 -- Invoke validation procedures
370 Validate_Prgm_Benefits(
371 p_api_version_number => 1.0
372 ,p_init_msg_list => FND_API.G_FALSE
373 ,p_validation_level => p_validation_level
374 ,p_validation_mode => JTF_PLSQL_API.g_update
375 ,p_prgm_benefits_rec => p_prgm_benefits_rec
376 ,x_return_status => x_return_status
377 ,x_msg_count => x_msg_count
378 ,x_msg_data => x_msg_data
379 );
380 END IF;
381
382 IF x_return_status = FND_API.g_ret_sts_error THEN
383 RAISE FND_API.g_exc_error;
384 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
385 RAISE FND_API.g_exc_unexpected_error;
386 END IF;
387
388 -- replace g_miss_char/num/date with current column values
389 Complete_Rec(
390 p_prgm_benefits_rec => p_prgm_benefits_rec
391 ,x_complete_rec => l_tar_prgm_benefits_rec
392 );
393
394 -- Debug Message
395 IF (PV_DEBUG_HIGH_ON) THEN
396
397 Pvx_Utility_Pvt.debug_message('Private API: '||l_full_name||' - Calling update table handler');
398 END IF;
399
400 -- Invoke table handler(PV_PRGM_BENEFITS_PKG.Update_Row)
401 PV_PRGM_BENEFITS_PKG.Update_Row(
402 p_program_benefits_id => l_tar_prgm_benefits_rec.program_benefits_id
403 ,p_program_id => l_tar_prgm_benefits_rec.program_id
404 ,p_benefit_code => l_tar_prgm_benefits_rec.benefit_code
405 ,p_benefit_id => l_tar_prgm_benefits_rec.benefit_id
406 ,p_benefit_type_code => l_tar_prgm_benefits_rec.benefit_type_code
407 ,p_delete_flag => l_tar_prgm_benefits_rec.delete_flag
408 ,p_last_update_login => FND_GLOBAL.conc_login_id
409 ,p_object_version_number => l_tar_prgm_benefits_rec.object_version_number
410 ,p_last_update_date => SYSDATE
411 ,p_last_updated_by => FND_GLOBAL.user_id
412 );
413
414 -- Check for commit
415 IF FND_API.to_boolean(p_commit) THEN
416 COMMIT;
417 END IF;
418
419 FND_MSG_PUB.count_and_get(
420 p_encoded => FND_API.g_false
421 ,p_count => x_msg_count
422 ,p_data => x_msg_data
423 );
424
425 -- Debug Message
426 IF (PV_DEBUG_HIGH_ON) THEN
427
428 PVX_UTILITY_PVT.debug_message('Private API: ' || l_api_name || ' - end');
429 END IF;
430
431 EXCEPTION
432 WHEN FND_API.G_EXC_ERROR THEN
433 ROLLBACK TO UPDATE_Prgm_Benefits_PVT;
434 x_return_status := FND_API.G_RET_STS_ERROR;
435 -- Standard call to get message count and if count=1, get the message
436 FND_MSG_PUB.Count_And_Get (
437 p_encoded => FND_API.G_FALSE
438 ,p_count => x_msg_count
439 ,p_data => x_msg_data
440 );
441
442 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
443 ROLLBACK TO UPDATE_Prgm_Benefits_PVT;
444 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
445 -- Standard call to get message count and if count=1, get the message
446 FND_MSG_PUB.Count_And_Get (
447 p_encoded => FND_API.G_FALSE,
448 p_count => x_msg_count,
449 p_data => x_msg_data
450 );
451
452 WHEN OTHERS THEN
453 ROLLBACK TO UPDATE_Prgm_Benefits_PVT;
454 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
455 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
456 THEN
457 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
458 END IF;
459 -- Standard call to get message count and if count=1, get the message
460 FND_MSG_PUB.Count_And_Get (
461 p_encoded => FND_API.G_FALSE
462 ,p_count => x_msg_count
463 ,p_data => x_msg_data
464 );
465
466 End Update_Prgm_Benefits;
467
468
469
470 PROCEDURE Delete_Prgm_Benefits(
471 p_api_version_number IN NUMBER
472 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
473 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
474 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
475 ,x_return_status OUT NOCOPY VARCHAR2
476 ,x_msg_count OUT NOCOPY NUMBER
477 ,x_msg_data OUT NOCOPY VARCHAR2
478 ,p_program_benefits_id IN NUMBER
479 ,p_object_version_number IN NUMBER
480 )
481
482 IS
483
484 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Prgm_Benefits';
485 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
486 l_api_version_number CONSTANT NUMBER := 1.0;
487 l_object_version_number NUMBER;
488
489 BEGIN
490
491 ---- Initialize----------------
492
493 -- Standard Start of API savepoint
494 SAVEPOINT DELETE_Prgm_Benefits_PVT;
495
496 -- Debug Message
497 IF (PV_DEBUG_HIGH_ON) THEN
498
499 PVX_UTILITY_PVT.debug_message('Private API: ' || l_api_name || ' - start');
500 END IF;
501
502 -- Standard call to check for call compatibility.
503 IF NOT FND_API.Compatible_API_Call (l_api_version_number
504 ,p_api_version_number
505 ,l_api_name
506 ,G_PKG_NAME
507 )
508 THEN
509 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
510 END IF;
511
512 -- Initialize message list if p_init_msg_list is set to TRUE.
513 IF FND_API.to_Boolean( p_init_msg_list )
514 THEN
515 FND_MSG_PUB.initialize;
516 END IF;
517
518 -- Initialize API return status to SUCCESS
519 x_return_status := FND_API.G_RET_STS_SUCCESS;
520
521 -- Debug Message
522 IF (PV_DEBUG_HIGH_ON) THEN
523
524 PVX_UTILITY_PVT.debug_message( 'Private API: Calling delete table handler...');
525 END IF;
526
527 -- Invoke table handler(PV_PRGM_BENEFITS_PKG.Delete_Row)
528 PV_PRGM_BENEFITS_PKG.Delete_Row(
529 p_program_benefits_id => p_program_benefits_id
530 ,p_object_version_number => p_object_version_number
531 );
532
533 -- Check for commit
534 IF FND_API.to_boolean(p_commit) THEN
535 COMMIT;
536 END IF;
537
538 FND_MSG_PUB.count_and_get(
539 p_encoded => FND_API.g_false
540 ,p_count => x_msg_count
541 ,p_data => x_msg_data
542 );
543
544 -- Debug Message
545 IF (PV_DEBUG_HIGH_ON) THEN
546
547 PVX_UTILITY_PVT.debug_message('Private API: ' || l_api_name || ' - end');
548 END IF;
549
550 EXCEPTION
551 WHEN FND_API.G_EXC_ERROR THEN
552 ROLLBACK TO DELETE_Prgm_Benefits_PVT;
553 x_return_status := FND_API.G_RET_STS_ERROR;
554 -- Standard call to get message count and if count=1, get the message
555 FND_MSG_PUB.Count_And_Get (
556 p_encoded => FND_API.G_FALSE
557 ,p_count => x_msg_count
558 ,p_data => x_msg_data
559 );
560
561 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
562 ROLLBACK TO DELETE_Prgm_Benefits_PVT;
563 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
564 -- Standard call to get message count and if count=1, get the message
565 FND_MSG_PUB.Count_And_Get (
566 p_encoded => FND_API.G_FALSE
567 ,p_count => x_msg_count
568 ,p_data => x_msg_data
569 );
570
571 WHEN OTHERS THEN
572 ROLLBACK TO DELETE_Prgm_Benefits_PVT;
573 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
574 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
575 THEN
576 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
577 END IF;
578 -- Standard call to get message count and if count=1, get the message
579 FND_MSG_PUB.Count_And_Get (
580 p_encoded => FND_API.G_FALSE
581 ,p_count => x_msg_count
582 ,p_data => x_msg_data
583 );
584
585 END Delete_Prgm_Benefits;
586
587
588
589 PROCEDURE Lock_Prgm_Benefits(
590 p_api_version_number IN NUMBER
591 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
592
593 ,x_return_status OUT NOCOPY VARCHAR2
594 ,x_msg_count OUT NOCOPY NUMBER
595 ,x_msg_data OUT NOCOPY VARCHAR2
596
597 ,px_program_benefits_id IN NUMBER
598 ,p_object_version IN NUMBER
599 )
600
601 IS
602
603 l_api_name CONSTANT VARCHAR2(30) := 'Lock_Prgm_Benefits';
604 l_api_version_number CONSTANT NUMBER := 1.0;
605 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
606 l_program_benefits_id NUMBER;
607
608 CURSOR c_Prgm_Benefits IS
609 SELECT program_benefits_id
610 FROM PV_PROGRAM_BENEFITS
611 WHERE program_benefits_id = px_program_benefits_id
612 AND object_version_number = p_object_version
613 FOR UPDATE NOWAIT;
614
615 BEGIN
616
617 -- Debug Message
618 IF (PV_DEBUG_HIGH_ON) THEN
619
620 PVX_UTILITY_PVT.debug_message('Private API: ' || l_full_name || 'start');
621 END IF;
622
623 -- Initialize message list if p_init_msg_list is set to TRUE.
624 IF FND_API.to_Boolean( p_init_msg_list )
625 THEN
626 FND_MSG_PUB.initialize;
627 END IF;
628
629 -- Standard call to check for call compatibility.
630 IF NOT FND_API.Compatible_API_Call (
631 l_api_version_number
632 ,p_api_version_number
633 ,l_api_name
634 ,G_PKG_NAME
635 )
636 THEN
637 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
638 END IF;
639
640
641 -- Initialize API return status to SUCCESS
642 x_return_status := FND_API.G_RET_STS_SUCCESS;
643
644
645 ------------------------ lock -------------------------
646
647 IF (PV_DEBUG_HIGH_ON) THEN
648
649
650
651 PVX_UTILITY_PVT.debug_message(l_full_name||': start');
652
653 END IF;
654 OPEN c_Prgm_Benefits;
655
656 FETCH c_Prgm_Benefits INTO l_program_benefits_id;
657
658 IF (c_Prgm_Benefits%NOTFOUND) THEN
659 CLOSE c_Prgm_Benefits;
660 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
661 FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
662 FND_MSG_PUB.add;
663 END IF;
664 RAISE FND_API.g_exc_error;
665 END IF;
666
667 CLOSE c_Prgm_Benefits;
668
669 -------------------- finish --------------------------
670 FND_MSG_PUB.count_and_get(
671 p_encoded => FND_API.g_false
672 ,p_count => x_msg_count
673 ,p_data => x_msg_data
674 );
675
676 -- Debug Message
677 IF (PV_DEBUG_HIGH_ON) THEN
678
679 PVX_UTILITY_PVT.debug_message('Private API: ' || l_api_name || ' - end');
680 END IF;
681
682 EXCEPTION
683 /*
684 WHEN PVX_UTILITY_PVT.resource_locked THEN
685 x_return_status := FND_API.g_ret_sts_error;
686 PVX_UTILITY_PVT.Error_Message(p_message_name => 'AMS_API_RESOURCE_LOCKED');
687 */
688 WHEN FND_API.G_EXC_ERROR THEN
689 ROLLBACK TO LOCK_Prgm_Benefits_PVT;
690 x_return_status := FND_API.G_RET_STS_ERROR;
691 -- Standard call to get message count and if count=1, get the message
692 FND_MSG_PUB.Count_And_Get (
693 p_encoded => FND_API.G_FALSE
694 ,p_count => x_msg_count
695 ,p_data => x_msg_data
696 );
697
698 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
699 ROLLBACK TO LOCK_Prgm_Benefits_PVT;
700 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
701 -- Standard call to get message count and if count=1, get the message
702 FND_MSG_PUB.Count_And_Get (
703 p_encoded => FND_API.G_FALSE
704 ,p_count => x_msg_count
705 ,p_data => x_msg_data
706 );
707
708 WHEN OTHERS THEN
709 ROLLBACK TO LOCK_Prgm_Benefits_PVT;
710 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
711 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
712 THEN
713 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
714 END IF;
715 -- Standard call to get message count and if count=1, get the message
716 FND_MSG_PUB.Count_And_Get (
717 p_encoded => FND_API.G_FALSE
718 ,p_count => x_msg_count
719 ,p_data => x_msg_data
720 );
721 END Lock_Prgm_Benefits;
722
723
724
725 PROCEDURE Check_UK_Items(
726 p_prgm_benefits_rec IN prgm_benefits_rec_type
727 ,p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create
728 ,x_return_status OUT NOCOPY VARCHAR2
729 )
730
731 IS
732
733 l_valid_flag VARCHAR2(1);
734
735 BEGIN
736
737 x_return_status := FND_API.g_ret_sts_success;
738 IF p_validation_mode = JTF_PLSQL_API.g_create THEN
739
740 l_valid_flag := PVX_UTILITY_PVT.check_uniqueness(
741 'PV_PROGRAM_BENEFITS',
742 'program_benefits_id = ''' || p_prgm_benefits_rec.program_benefits_id ||''''
743 );
744
745 IF l_valid_flag = FND_API.g_false THEN
746 FND_MESSAGE.set_name('PV', 'PV_API_DUPLICATE_ENTITY');
747 FND_MESSAGE.set_token('ID',to_char(p_prgm_benefits_rec.program_benefits_id) );
748 FND_MESSAGE.set_token('ENTITY','PARTNER_BENEFITS');
749 FND_MSG_PUB.add;
750 x_return_status := FND_API.g_ret_sts_error;
751 RETURN;
752 END IF;
753 -- Debug message
754 IF (PV_DEBUG_HIGH_ON) THEN
755
756 PVX_UTILITY_PVT.debug_message('- In Check_UK_Items API' );
757 END IF;
758 END IF;
759
760 END Check_UK_Items;
761
762
763
764 PROCEDURE Check_Req_Items(
765 p_prgm_benefits_rec IN prgm_benefits_rec_type
766 ,p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create
767 ,x_return_status OUT NOCOPY VARCHAR2
768 )
769
770 IS
771
772 BEGIN
773
774 x_return_status := FND_API.g_ret_sts_success;
775
776 IF p_validation_mode = JTF_PLSQL_API.g_create THEN
777
778 IF p_prgm_benefits_rec.program_benefits_id = FND_API.g_miss_num
779 OR p_prgm_benefits_rec.program_benefits_id IS NULL THEN
780 FND_MESSAGE.set_name('PV', 'PV_API_MISSING_REQ_COLUMN');
781 FND_MESSAGE.set_token('COLUMN','PROGRAM_BENEFITS_ID');
782 FND_MSG_PUB.add;
783 x_return_status := FND_API.g_ret_sts_error;
784 RETURN;
785 END IF;
786
787
788 IF p_prgm_benefits_rec.program_id = FND_API.g_miss_num
789 OR p_prgm_benefits_rec.program_id IS NULL THEN
790 FND_MESSAGE.set_name('PV', 'PV_API_MISSING_REQ_COLUMN');
791 FND_MESSAGE.set_token('COLUMN','PROGRAM_ID');
792 FND_MSG_PUB.add;
793 x_return_status := FND_API.g_ret_sts_error;
794 RETURN;
795 END IF;
796
797 /*
798 IF p_prgm_benefits_rec.benefit_code = FND_API.g_miss_char
799 OR p_prgm_benefits_rec.benefit_code IS NULL THEN
800 FND_MESSAGE.set_name('PV', 'PV_API_MISSING_REQ_COLUMN');
801 FND_MESSAGE.set_token('COLUMN','BENEFIT_CODE');
802 FND_MSG_PUB.add;
803 x_return_status := FND_API.g_ret_sts_error;
804 RETURN;
805 END IF;
806 */
807
808 IF p_prgm_benefits_rec.benefit_id = FND_API.g_miss_num
809 OR p_prgm_benefits_rec.benefit_id IS NULL THEN
810 FND_MESSAGE.set_name('PV', 'PV_API_MISSING_REQ_COLUMN');
811 FND_MESSAGE.set_token('COLUMN','BENEFIT_ID');
812 FND_MSG_PUB.add;
813 x_return_status := FND_API.g_ret_sts_error;
814 RETURN;
815 END IF;
816
817
818 IF p_prgm_benefits_rec.benefit_type_code = FND_API.g_miss_char
819 OR p_prgm_benefits_rec.benefit_type_code IS NULL THEN
820 FND_MESSAGE.set_name('PV', 'PV_API_MISSING_REQ_COLUMN');
821 FND_MESSAGE.set_token('COLUMN','BENEFIT_TYPE_CODE');
822 FND_MSG_PUB.add;
823 x_return_status := FND_API.g_ret_sts_error;
824 RETURN;
825 END IF;
826
827
828 IF p_prgm_benefits_rec.delete_flag = FND_API.g_miss_char
829 OR p_prgm_benefits_rec.delete_flag IS NULL THEN
830 FND_MESSAGE.set_name('PV', 'PV_API_MISSING_REQ_COLUMN');
831 FND_MESSAGE.set_token('COLUMN','DELETE_FLAG');
832 FND_MSG_PUB.add;
833 x_return_status := FND_API.g_ret_sts_error;
834 RETURN;
835 END IF;
836
837
838 IF p_prgm_benefits_rec.last_update_login = FND_API.g_miss_num
839 OR p_prgm_benefits_rec.last_update_login IS NULL THEN
840 FND_MESSAGE.set_name('PV', 'PV_API_MISSING_REQ_COLUMN');
841 FND_MESSAGE.set_token('COLUMN','LAST_UPDATE_LOGIN');
842 FND_MSG_PUB.add;
843 x_return_status := FND_API.g_ret_sts_error;
844 RETURN;
845 END IF;
846
847
848 IF p_prgm_benefits_rec.object_version_number = FND_API.g_miss_num
849 OR p_prgm_benefits_rec.object_version_number IS NULL THEN
850 FND_MESSAGE.set_name('PV', 'PV_API_MISSING_REQ_COLUMN');
851 FND_MESSAGE.set_token('COLUMN','OBJECT_VERSION_NUMBER');
852 FND_MSG_PUB.add;
853 x_return_status := FND_API.g_ret_sts_error;
854 RETURN;
855 END IF;
856
857
858 IF p_prgm_benefits_rec.last_update_date = FND_API.g_miss_date
859 OR p_prgm_benefits_rec.last_update_date IS NULL THEN
860 FND_MESSAGE.set_name('PV', 'PV_API_MISSING_REQ_COLUMN');
861 FND_MESSAGE.set_token('COLUMN','LAST_UPDATE_DATE');
862 FND_MSG_PUB.add;
863 x_return_status := FND_API.g_ret_sts_error;
864 RETURN;
865 END IF;
866
867
868 IF p_prgm_benefits_rec.last_updated_by = FND_API.g_miss_num
869 OR p_prgm_benefits_rec.last_updated_by IS NULL THEN
870 FND_MESSAGE.set_name('PV', 'PV_API_MISSING_REQ_COLUMN');
871 FND_MESSAGE.set_token('COLUMN','LAST_UPDATED_BY');
872 FND_MSG_PUB.add;
873 x_return_status := FND_API.g_ret_sts_error;
874 RETURN;
875 END IF;
876
877 -- Debug message
878 IF (PV_DEBUG_HIGH_ON) THEN
879
880 PVX_UTILITY_PVT.debug_message('- In Check_Req_Items API Before Created_by Check' );
881 END IF;
882
883 IF p_prgm_benefits_rec.created_by = FND_API.g_miss_num
884 OR p_prgm_benefits_rec.created_by IS NULL THEN
885 FND_MESSAGE.set_name('PV', 'PV_API_MISSING_REQ_COLUMN');
886 FND_MESSAGE.set_token('COLUMN','CREATED_BY');
887 FND_MSG_PUB.add;
888 x_return_status := FND_API.g_ret_sts_error;
889 RETURN;
890 END IF;
891
892
893 IF p_prgm_benefits_rec.creation_date = FND_API.g_miss_date
894 OR p_prgm_benefits_rec.creation_date IS NULL THEN
895 FND_MESSAGE.set_name('PV', 'PV_API_MISSING_REQ_COLUMN');
896 FND_MESSAGE.set_token('COLUMN','CREATION_DATE');
897 FND_MSG_PUB.add;
898 x_return_status := FND_API.g_ret_sts_error;
899 RETURN;
900 END IF;
901 ELSE
902
903 IF p_prgm_benefits_rec.program_benefits_id IS NULL THEN
904 FND_MESSAGE.set_name('PV', 'PV_API_MISSING_REQ_COLUMN');
905 FND_MESSAGE.set_token('COLUMN','PROGRAM_BENEFITS_ID');
906 FND_MSG_PUB.add;
907 x_return_status := FND_API.g_ret_sts_error;
908 RETURN;
909 END IF;
910
911 IF p_prgm_benefits_rec.benefit_id = FND_API.g_miss_num
912 OR p_prgm_benefits_rec.benefit_id IS NULL THEN
913 FND_MESSAGE.set_name('PV', 'PV_API_MISSING_REQ_COLUMN');
914 FND_MESSAGE.set_token('COLUMN','BENEFIT_ID');
915 FND_MSG_PUB.add;
916 x_return_status := FND_API.g_ret_sts_error;
917 RETURN;
918 END IF;
919
920
921 IF p_prgm_benefits_rec.benefit_type_code = FND_API.g_miss_char
922 OR p_prgm_benefits_rec.benefit_type_code IS NULL THEN
923 FND_MESSAGE.set_name('PV', 'PV_API_MISSING_REQ_COLUMN');
924 FND_MESSAGE.set_token('COLUMN','BENEFIT_TYPE_CODE');
925 FND_MSG_PUB.add;
926 x_return_status := FND_API.g_ret_sts_error;
927 RETURN;
928 END IF;
929
930
931 IF p_prgm_benefits_rec.delete_flag = FND_API.g_miss_char
932 OR p_prgm_benefits_rec.delete_flag IS NULL THEN
933 FND_MESSAGE.set_name('PV', 'PV_API_MISSING_REQ_COLUMN');
934 FND_MESSAGE.set_token('COLUMN','DELETE_FLAG');
935 FND_MSG_PUB.add;
936 x_return_status := FND_API.g_ret_sts_error;
937 RETURN;
938 END IF;
939
940
941 IF p_prgm_benefits_rec.object_version_number IS NULL THEN
942 FND_MESSAGE.set_name('PV', 'PV_API_MISSING_REQ_COLUMN');
943 FND_MESSAGE.set_token('COLUMN','OBJECT_VERSION_NUMBER');
944 FND_MSG_PUB.add;
945 x_return_status := FND_API.g_ret_sts_error;
946 RETURN;
947 END IF;
948 END IF;
949
950 END Check_Req_Items;
951
952
953
954 PROCEDURE Check_FK_Items(
955 p_prgm_benefits_rec IN prgm_benefits_rec_type
956 ,x_return_status OUT NOCOPY VARCHAR2
957 )
958 IS
959
960 BEGIN
961
962 x_return_status := FND_API.g_ret_sts_success;
963
964 ----------------------- PROGRAM_ID ------------------------
965 IF (p_prgm_benefits_rec.PROGRAM_ID <> FND_API.g_miss_num
966 AND p_prgm_benefits_rec.PROGRAM_ID IS NOT NULL ) THEN
967
968 -- Debug message
969 IF (PV_DEBUG_HIGH_ON) THEN
970
971 PVX_UTILITY_PVT.debug_message('- In Check_FK_Items : Before PROGRAM_ID fk check : PROGRAM_ID ' || p_prgm_benefits_rec.PROGRAM_ID);
972 END IF;
973
974 IF PVX_Utility_PVT.check_fk_exists(
975 'PV_PARTNER_PROGRAM_B', -- Parent schema object having the primary key
976 'PROGRAM_ID', -- Column name in the parent object that maps to the fk value
977 p_prgm_benefits_rec.PROGRAM_ID, -- Value of fk to be validated against the parent object's pk column
978 PVX_utility_PVT.g_number, -- datatype of fk
979 NULL
980 ) = FND_API.g_false
981 THEN
982 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
983 THEN
984 FND_MESSAGE.set_name('PV', 'PV_NOT_A_VALID_PARTNER_PROGRAM');
985 FND_MSG_PUB.add;
986 END IF;
987
988 x_return_status := FND_API.g_ret_sts_error;
989 RETURN;
990 END IF;
991 END IF;
992
993 -- Debug message
994 IF (PV_DEBUG_HIGH_ON) THEN
995
996 PVX_UTILITY_PVT.debug_message('- In Check_FK_Items : After program_id fk check ');
997 END IF;
998
999 END Check_FK_Items;
1000
1001
1002
1003 PROCEDURE Check_Lookup_Items(
1004 p_prgm_benefits_rec IN prgm_benefits_rec_type
1005 ,x_return_status OUT NOCOPY VARCHAR2
1006 )
1007
1008 IS
1009
1010 BEGIN
1011
1012 x_return_status := FND_API.g_ret_sts_success;
1013
1014 /*
1015 ----------------------- Benefit_Code lookup ------------------------
1016 IF p_prgm_benefits_rec.Benefit_Code <> FND_API.g_miss_char THEN
1017
1018 IF PVX_Utility_PVT.check_lookup_exists(
1019 'FND_LOOKUP_VALUES', -- Look up Table Name
1020 'PV_PROGRAM_BENEFITS', -- Lookup Type
1021 p_prgm_benefits_rec.Benefit_Code -- Lookup Code
1022 ) = FND_API.g_false
1023 THEN
1024 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
1025 THEN
1026 FND_MESSAGE.set_name('PV', 'PV_NOT_A_VALID_PROGRAM_BENEFIT');
1027 FND_MSG_PUB.add;
1028 END IF;
1029 x_return_status := FND_API.g_ret_sts_error;
1030 RETURN;
1031
1032 END IF;
1033 END IF;
1034 */
1035
1036 -- Debug message
1037 IF (PV_DEBUG_HIGH_ON) THEN
1038
1039 PVX_UTILITY_PVT.debug_message('- In Check_Lookup_Items : x_return_status = '||x_return_status);
1040 END IF;
1041
1042 END Check_Lookup_Items;
1043
1044
1045
1046 PROCEDURE Check_Items (
1047 p_prgm_benefits_rec IN prgm_benefits_rec_type
1048 ,p_validation_mode IN VARCHAR2
1049 ,x_return_status OUT NOCOPY VARCHAR2
1050 )
1051
1052 IS
1053
1054 l_api_name CONSTANT VARCHAR2(30) := 'Check_Items';
1055 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
1056
1057 BEGIN
1058
1059 -- Debug message
1060 IF (PV_DEBUG_HIGH_ON) THEN
1061
1062 PVX_UTILITY_PVT.debug_message('- Check_Items API prior to Check_Req_Items call');
1063 END IF;
1064
1065 -- Check Items Required/NOT NULL API calls
1066 Check_Req_Items(
1067 p_prgm_benefits_rec => p_prgm_benefits_rec
1068 ,p_validation_mode => p_validation_mode
1069 ,x_return_status => x_return_status
1070 );
1071
1072 -- Debug message
1073 IF (PV_DEBUG_HIGH_ON) THEN
1074
1075 PVX_UTILITY_PVT.debug_message('- After Check_Req_Items. return status = ' || x_return_status);
1076 END IF;
1077
1078 IF x_return_status <> FND_API.g_ret_sts_success THEN
1079 RETURN;
1080 END IF;
1081
1082 -- Debug message
1083 IF (PV_DEBUG_HIGH_ON) THEN
1084
1085 PVX_UTILITY_PVT.debug_message('- Check_Items API prior to Check_UK_Items call');
1086 END IF;
1087
1088 -- Check Items Uniqueness API calls
1089 Check_UK_Items(
1090 p_prgm_benefits_rec => p_prgm_benefits_rec
1091 ,p_validation_mode => p_validation_mode
1092 ,x_return_status => x_return_status
1093 );
1094
1095 -- Debug message
1096 IF (PV_DEBUG_HIGH_ON) THEN
1097
1098 PVX_UTILITY_PVT.debug_message('- After Check_UK_Items. return status = ' || x_return_status);
1099 END IF;
1100
1101 IF x_return_status <> FND_API.g_ret_sts_success THEN
1102 RETURN;
1103 END IF;
1104
1105 -- Debug message
1106 IF (PV_DEBUG_HIGH_ON) THEN
1107
1108 PVX_UTILITY_PVT.debug_message('- Check_Items API prior to Check_FK_Items call');
1109 END IF;
1110
1111 -- Check Items Foreign Keys API calls
1112 Check_FK_Items(
1113 p_prgm_benefits_rec => p_prgm_benefits_rec
1114 ,x_return_status => x_return_status
1115 );
1116
1117 -- Debug message
1118 IF (PV_DEBUG_HIGH_ON) THEN
1119
1120 PVX_UTILITY_PVT.debug_message('- After Check_FK_Items. return status = ' || x_return_status);
1121 END IF;
1122
1123 IF x_return_status <> FND_API.g_ret_sts_success THEN
1124 RETURN;
1125 END IF;
1126
1127 -- Debug message
1128 IF (PV_DEBUG_HIGH_ON) THEN
1129
1130 PVX_UTILITY_PVT.debug_message('- Check_Items API prior to Check_Lookup_Items call');
1131 END IF;
1132
1133 -- Check Items Lookups
1134 Check_Lookup_Items(
1135 p_prgm_benefits_rec => p_prgm_benefits_rec
1136 ,x_return_status => x_return_status
1137 );
1138
1139 -- Debug message
1140 IF (PV_DEBUG_HIGH_ON) THEN
1141
1142 PVX_UTILITY_PVT.debug_message('- After Check_Lookup_Items. return status = ' || x_return_status);
1143 END IF;
1144
1145 IF x_return_status <> FND_API.g_ret_sts_success THEN
1146 RETURN;
1147 END IF;
1148
1149 END Check_Items;
1150
1151
1152
1153 PROCEDURE Complete_Rec (
1154 p_prgm_benefits_rec IN prgm_benefits_rec_type
1155 ,x_complete_rec OUT NOCOPY prgm_benefits_rec_type
1156 )
1157
1158 IS
1159
1160 CURSOR c_complete IS
1161 SELECT *
1162 FROM pv_program_benefits
1163 WHERE program_benefits_id = p_prgm_benefits_rec.program_benefits_id;
1164
1165 l_prgm_benefits_rec c_complete%ROWTYPE;
1166
1167 BEGIN
1168
1169 x_complete_rec := p_prgm_benefits_rec;
1170
1171
1172 OPEN c_complete;
1173 FETCH c_complete INTO l_prgm_benefits_rec;
1174 CLOSE c_complete;
1175
1176 -- Debug message
1177 IF (PV_DEBUG_HIGH_ON) THEN
1178
1179 PVX_UTILITY_PVT.debug_message('- In Complete_Rec API prior to assigning program_id');
1180 END IF;
1181
1182 -- program_benefits_id
1183 -- IF p_prgm_benefits_rec.program_benefits_id = FND_API.g_miss_num THEN
1184 IF p_prgm_benefits_rec.program_benefits_id IS NULL THEN
1185 x_complete_rec.program_benefits_id := l_prgm_benefits_rec.program_benefits_id;
1186 END IF;
1187
1188 -- program_id
1189 -- IF p_prgm_benefits_rec.program_id = FND_API.g_miss_num THEN
1190 IF p_prgm_benefits_rec.program_id IS NULL THEN
1191 x_complete_rec.program_id := l_prgm_benefits_rec.program_id;
1192 END IF;
1193
1194 -- benefit_code
1195 -- IF p_prgm_benefits_rec.benefit_code = FND_API.g_miss_char THEN
1196 IF p_prgm_benefits_rec.benefit_code IS NULL THEN
1197 x_complete_rec.benefit_code := l_prgm_benefits_rec.benefit_code;
1198 END IF;
1199
1200 -- benefit_id
1201 -- IF p_prgm_benefits_rec.benefit_id = FND_API.g_miss_num THEN
1202 IF p_prgm_benefits_rec.benefit_id IS NULL THEN
1203 x_complete_rec.benefit_id := l_prgm_benefits_rec.benefit_id;
1204 END IF;
1205
1206 -- benefit_type_code
1207 -- IF p_prgm_benefits_rec.benefit_type_code = FND_API.g_miss_char THEN
1208 IF p_prgm_benefits_rec.benefit_type_code IS NULL THEN
1209 x_complete_rec.benefit_type_code := l_prgm_benefits_rec.benefit_type_code;
1210 END IF;
1211
1212 -- delete_flag
1213 -- IF p_prgm_benefits_rec.delete_flag = FND_API.g_miss_char THEN
1214 IF p_prgm_benefits_rec.delete_flag IS NULL THEN
1215 x_complete_rec.delete_flag := l_prgm_benefits_rec.delete_flag;
1216 END IF;
1217
1218 -- last_update_login
1219 -- IF p_prgm_benefits_rec.last_update_login = FND_API.g_miss_num THEN
1220 IF p_prgm_benefits_rec.last_update_login IS NULL THEN
1221 x_complete_rec.last_update_login := l_prgm_benefits_rec.last_update_login;
1222 END IF;
1223
1224 -- object_version_number
1225 -- IF p_prgm_benefits_rec.object_version_number = FND_API.g_miss_num THEN
1226 IF p_prgm_benefits_rec.object_version_number IS NULL THEN
1227 x_complete_rec.object_version_number := l_prgm_benefits_rec.object_version_number;
1228 END IF;
1229
1230 -- last_update_date
1231 -- IF p_prgm_benefits_rec.last_update_date = FND_API.g_miss_date THEN
1232 IF p_prgm_benefits_rec.last_update_date IS NULL THEN
1233 x_complete_rec.last_update_date := l_prgm_benefits_rec.last_update_date;
1234 END IF;
1235
1236 -- last_updated_by
1237 -- IF p_prgm_benefits_rec.last_updated_by = FND_API.g_miss_num THEN
1238 IF p_prgm_benefits_rec.last_updated_by IS NULL THEN
1239 x_complete_rec.last_updated_by := l_prgm_benefits_rec.last_updated_by;
1240 END IF;
1241
1242 -- created_by
1243 -- IF p_prgm_benefits_rec.created_by = FND_API.g_miss_num THEN
1244 IF p_prgm_benefits_rec.created_by IS NULL THEN
1245 x_complete_rec.created_by := l_prgm_benefits_rec.created_by;
1246 END IF;
1247
1248 -- creation_date
1249 -- IF p_prgm_benefits_rec.creation_date = FND_API.g_miss_date THEN
1250 IF p_prgm_benefits_rec.creation_date IS NULL THEN
1251 x_complete_rec.creation_date := l_prgm_benefits_rec.creation_date;
1252 END IF;
1253
1254 END Complete_Rec;
1255
1256
1257
1258 PROCEDURE Validate_Prgm_Benefits(
1259 p_api_version_number IN NUMBER
1260 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
1261 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
1262 ,p_prgm_benefits_rec IN prgm_benefits_rec_type
1263 ,p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.G_UPDATE
1264 ,x_return_status OUT NOCOPY VARCHAR2
1265 ,x_msg_count OUT NOCOPY NUMBER
1266 ,x_msg_data OUT NOCOPY VARCHAR2
1267 )
1268
1269 IS
1270
1271 l_api_name CONSTANT VARCHAR2(30) := 'Validate_Prgm_Benefits';
1272 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
1273 l_api_version_number CONSTANT NUMBER := 1.0;
1274 l_object_version_number NUMBER;
1275 l_prgm_benefits_rec PV_PRGM_BENEFITS_PVT.prgm_benefits_rec_type;
1276
1277 BEGIN
1278
1279 -- Standard Start of API savepoint
1280 SAVEPOINT Validate_Prgm_Benefits_;
1281
1282 -- Initialize API return status to SUCCESS
1283 x_return_status := FND_API.G_RET_STS_SUCCESS;
1284
1285 -- Standard call to check for call compatibility.
1286 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1287 p_api_version_number,
1288 l_api_name,
1289 G_PKG_NAME)
1290 THEN
1291 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1292 END IF;
1293
1294 -- Initialize message list if p_init_msg_list is set to TRUE.
1295 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1296 FND_MSG_PUB.initialize;
1297 END IF;
1298
1299 -- Debug Message
1300 IF (PV_DEBUG_HIGH_ON) THEN
1301
1302 PVX_UTILITY_PVT.debug_message(' Private API: ' || l_full_name || ' - start');
1303 END IF;
1304
1305 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
1306 -- Debug message
1307 IF (PV_DEBUG_HIGH_ON) THEN
1308
1309 PVX_UTILITY_PVT.debug_message(' Private API: ' || l_full_name || ' - prior to Check_Items call');
1310 END IF;
1311
1312 Check_Items(
1313 p_prgm_benefits_rec => p_prgm_benefits_rec
1314 ,p_validation_mode => p_validation_mode
1315 ,x_return_status => x_return_status
1316 );
1317
1318 -- Debug message
1319 IF (PV_DEBUG_HIGH_ON) THEN
1320
1321 PVX_UTILITY_PVT.debug_message(' Private API: ' || l_full_name || ' - return status after Check_Items call ' || x_return_status);
1322 END IF;
1323
1324 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1325 RAISE FND_API.G_EXC_ERROR;
1326 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1327 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1328 END IF;
1329 END IF;
1330
1331 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
1332 Validate_Rec(
1333 p_api_version_number => 1.0
1334 ,p_init_msg_list => FND_API.G_FALSE
1335 ,x_return_status => x_return_status
1336 ,x_msg_count => x_msg_count
1337 ,x_msg_data => x_msg_data
1338 ,p_prgm_benefits_rec => l_prgm_benefits_rec
1339 );
1340
1341 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1342 RAISE FND_API.G_EXC_ERROR;
1343 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1344 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1345 END IF;
1346 END IF;
1347
1348
1349 -- Debug Message
1350 IF (PV_DEBUG_HIGH_ON) THEN
1351
1352 PVX_UTILITY_PVT.debug_message('Private API: ' || l_full_name || ' - end');
1353 END IF;
1354
1355 -- Standard call to get message count and if count is 1, get message info.
1356 FND_MSG_PUB.Count_And_Get
1357 ( p_encoded => FND_API.G_FALSE,
1358 p_count => x_msg_count,
1359 p_data => x_msg_data
1360 );
1361
1362 EXCEPTION
1363 WHEN FND_API.G_EXC_ERROR THEN
1364 ROLLBACK TO Validate_Prgm_Benefits_;
1365 x_return_status := FND_API.G_RET_STS_ERROR;
1366 -- Standard call to get message count and if count=1, get the message
1367 FND_MSG_PUB.Count_And_Get (
1368 p_encoded => FND_API.G_FALSE
1369 ,p_count => x_msg_count
1370 ,p_data => x_msg_data
1371 );
1372
1373 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1374 ROLLBACK TO Validate_Prgm_Benefits_;
1375 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1376 -- Standard call to get message count and if count=1, get the message
1377 FND_MSG_PUB.Count_And_Get (
1378 p_encoded => FND_API.G_FALSE
1379 ,p_count => x_msg_count
1380 ,p_data => x_msg_data
1381 );
1382
1383 WHEN OTHERS THEN
1384 ROLLBACK TO Validate_Prgm_Benefits_;
1385 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1386 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1387 THEN
1388 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1389 END IF;
1390 -- Standard call to get message count and if count=1, get the message
1391 FND_MSG_PUB.Count_And_Get (
1392 p_encoded => FND_API.G_FALSE
1393 ,p_count => x_msg_count
1394 ,p_data => x_msg_data
1395 );
1396
1397 End Validate_Prgm_Benefits;
1398
1399
1400
1401 PROCEDURE Validate_Rec(
1402 p_api_version_number IN NUMBER
1403 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
1404 ,x_return_status OUT NOCOPY VARCHAR2
1405 ,x_msg_count OUT NOCOPY NUMBER
1406 ,x_msg_data OUT NOCOPY VARCHAR2
1407 ,p_prgm_benefits_rec IN prgm_benefits_rec_type
1408 ,p_validation_mode IN VARCHAR2
1409 )
1410
1411 IS
1412
1413 BEGIN
1414 -- Initialize message list if p_init_msg_list is set to TRUE.
1415 IF FND_API.to_Boolean( p_init_msg_list )
1416 THEN
1417 FND_MSG_PUB.initialize;
1418 END IF;
1419
1420 -- Initialize API return status to SUCCESS
1421 x_return_status := FND_API.G_RET_STS_SUCCESS;
1422
1423 -- Hint: Validate data
1424 -- If data not valid
1425 -- THEN
1426 -- x_return_status := FND_API.G_RET_STS_ERROR;
1427
1428 -- Standard call to get message count and if count is 1, get message info.
1429 FND_MSG_PUB.Count_And_Get
1430 (p_count => x_msg_count,
1431 p_data => x_msg_data
1432 );
1433
1434 END Validate_Rec;
1435
1436
1437 END PV_PRGM_BENEFITS_PVT;