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