[Home] [Help]
PACKAGE BODY: APPS.PV_PG_MEMBERSHIPS_PVT
Source
1 PACKAGE BODY PV_Pg_Memberships_PVT as
2 /* $Header: pvxvmemb.pls 120.6 2006/05/04 13:14:03 dgottlie ship $ */
3 -- ===============================================================
4 -- Start of Comments
5 -- Package name
6 -- PV_Pg_Memberships_PVT
7 -- Purpose
8 --
9 -- History
10 -- 13-SEP-2005 Karen.Tsao Removed call to Terminate_Contract API.
11 --
12 -- NOTE
13 --
14 -- This Api is generated with Latest version of
15 -- Rosetta, where g_miss indicates NULL and
16 -- NULL indicates missing value. Rosetta Version 1.55
17 -- End of Comments
18 -- ===============================================================
19
20
21 G_PKG_NAME CONSTANT VARCHAR2(30):= 'PV_Pg_Memberships_PVT';
22 G_FILE_NAME CONSTANT VARCHAR2(12) := 'pvxvmemb.pls';
23
24 PV_DEBUG_HIGH_ON CONSTANT boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);
25 PV_DEBUG_LOW_ON CONSTANT boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
26 PV_DEBUG_MEDIUM_ON CONSTANT boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM);
27
28 g_log_level CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
29
30 -- G_USER_ID NUMBER := FND_GLOBAL.USER_ID;
31 -- G_LOGIN_ID NUMBER := FND_GLOBAL.CONC_LOGIN_ID;
32 --
33 -- Foreward Procedure Declarations
34 --
35
36 FUNCTION isnumber (
37 l_value VARCHAR2
38 )
39 RETURN NUMBER IS
40 l_number NUMBER;
41 BEGIN
42 BEGIN
43 l_number := l_value;
44 EXCEPTION
45 WHEN OTHERS THEN
46 RETURN NULL;
47 END;
48
49 RETURN l_number;
50 END isnumber;
51
52 PROCEDURE validate_member_type
53 (
54 p_member_type VARCHAR2
55 ,x_return_status OUT NOCOPY VARCHAR2
56 )IS
57
58 l_value VARCHAR2(1);
59 CURSOR memb_csr( attr_cd VARCHAR2 ) IS
60 SELECT 'X'
61 FROM PV_ATTRIBUTE_CODES_VL
62 WHERE ATTRIBUTE_ID = 6
63 AND ENABLED_FLAG = 'Y'
64 AND ATTR_CODE =attr_cd;
65
66 BEGIN
67 x_return_status := FND_API.g_ret_sts_success;
68 OPEN memb_csr( p_member_type );
69 FETCH memb_csr INTO l_value;
70 CLOSE memb_csr;
71 IF l_value IS NULL THEN
72 x_return_status := FND_API.g_ret_sts_error;
73 FND_MESSAGE.set_name('PV', 'PV_INVALID_MEMBER_TYPE');
74 FND_MESSAGE.set_token('MEMBER_TYPE',p_member_type );
75 FND_MSG_PUB.add;
76 END IF;
77
78 END validate_member_type;
79
80 PROCEDURE validate_Lookup(
81 p_lookup_type IN VARCHAR2
82 ,p_lookup_code IN VARCHAR2
83 ,x_return_status OUT NOCOPY VARCHAR2
84 )
85 IS
86 l_lookup_exists VARCHAR2(1);
87 BEGIN
88 x_return_status := FND_API.g_ret_sts_success;
89 --validate lookup
90 l_lookup_exists := PVX_UTILITY_PVT.check_lookup_exists
91 ( p_lookup_table_name => 'PV_LOOKUPS'
92 ,p_lookup_type => p_lookup_type
93 ,p_lookup_code => p_lookup_code
94 );
95 IF NOT FND_API.to_boolean(l_lookup_exists) THEN
96 x_return_status := FND_API.g_ret_sts_error;
97 FND_MESSAGE.set_name('PV', 'PV_INVALID_LOOKUP_CODE');
98 FND_MESSAGE.set_token('LOOKUP_TYPE',p_lookup_type );
99 FND_MESSAGE.set_token('LOOKUP_CODE', p_lookup_code );
100 FND_MSG_PUB.add;
101 END IF;
102
103 END validate_Lookup;
104
105
106
107 PROCEDURE Default_Memb_Items (
108 p_memb_rec IN memb_rec_type ,
109 x_memb_rec OUT NOCOPY memb_rec_type
110 ) ;
111
112
113
114 -- Hint: Primary key needs to be returned.
115 -- ==============================================================================
116 -- Start of Comments
117 -- ==============================================================================
118 -- API Name
119 -- Create_Pg_Memberships
120 -- Type
121 -- Private
122 -- Pre-Req
123 --
124 -- Parameters
125 --
126 -- IN
127 -- p_api_version_number IN NUMBER Required
128 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API_G_FALSE
129 -- p_commit IN VARCHAR2 Optional Default = FND_API.G_FALSE
130 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
131 -- p_memb_rec IN memb_rec_type Required
132 --
133 -- OUT
134 -- x_return_status OUT VARCHAR2
135 -- x_msg_count OUT NUMBER
136 -- x_msg_data OUT VARCHAR2
137 -- Version : Current version 1.0
138 -- Note: This automatic generated procedure definition, it includes standard IN/OUT parameters
139 -- and basic operation, developer must manually add parameters and business logic as necessary.
140 --
141 -- History
142 --
143 -- NOTE
144 --
145 -- End of Comments
146 -- ==============================================================================
147
148 PROCEDURE Create_Pg_Memberships(
149 p_api_version_number IN NUMBER,
150 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
151 p_commit IN VARCHAR2 := FND_API.G_FALSE,
152 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
153
154 x_return_status OUT NOCOPY VARCHAR2,
155 x_msg_count OUT NOCOPY NUMBER,
156 x_msg_data OUT NOCOPY VARCHAR2,
157
158 p_memb_rec IN memb_rec_type := g_miss_memb_rec,
159 x_membership_id OUT NOCOPY NUMBER
160 )
161
162 IS
163 L_API_NAME CONSTANT VARCHAR2(30) := 'Create_Pg_Memberships';
164 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
165 l_return_status_full VARCHAR2(1);
166 l_object_version_number NUMBER := 1;
167 l_org_id NUMBER ;
168 l_membership_id NUMBER;
169 l_dummy NUMBER;
170 CURSOR c_id IS
171 SELECT pv_pg_memberships_s.NEXTVAL
172 FROM dual;
173
174 CURSOR c_id_exists (l_id IN NUMBER) IS
175 SELECT 1
176 FROM PV_PG_MEMBERSHIPS
177 WHERE membership_id = l_id;
178 BEGIN
179 -- Standard Start of API savepoint
180 SAVEPOINT create_pg_memberships_pvt;
181
182 -- Standard call to check for call compatibility.
183 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
184 p_api_version_number,
185 l_api_name,
186 G_PKG_NAME)
187 THEN
188 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
189 END IF;
190
191
192 -- Initialize message list if p_init_msg_list is set to TRUE.
193 IF FND_API.to_Boolean( p_init_msg_list )
194 THEN
195 FND_MSG_PUB.initialize;
196 END IF;
197
198
199 -- Debug Message
200 IF (PV_DEBUG_HIGH_ON) THEN
201 PVX_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
202 END IF;
203 -- Debug Message
204
205
206
207
208 -- Initialize API return status to SUCCESS
209 x_return_status := FND_API.G_RET_STS_SUCCESS;
210
211 -- =========================================================================
212 -- Validate Environment
213 -- =========================================================================
214
215 IF FND_GLOBAL.USER_ID IS NULL
216 THEN
217 PVX_UTILITY_PVT.Error_Message(p_message_name => 'USER_PROFILE_MISSING');
218 RAISE FND_API.G_EXC_ERROR;
219 END IF;
220
221
222
223 IF ( P_validation_level >= FND_API.G_VALID_LEVEL_FULL)
224 THEN
225 -- Debug message
226 -- Debug Message
227 IF (PV_DEBUG_HIGH_ON) THEN
228 PVX_UTILITY_PVT.debug_message('Private API: Validate_Pg_Memberships');
229 END IF;
230
231
232 -- Invoke validation procedures
233 Validate_pg_memberships(
234 p_api_version_number => 1.0,
235 p_init_msg_list => FND_API.G_FALSE,
236 p_validation_level => p_validation_level,
237 p_validation_mode => JTF_PLSQL_API.g_create,
238 p_memb_rec => p_memb_rec,
239 x_return_status => x_return_status,
240 x_msg_count => x_msg_count,
241 x_msg_data => x_msg_data);
242 END IF;
243
244 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
245 RAISE FND_API.G_EXC_ERROR;
246 END IF;
247
248 -- Local variable initialization
249
250 IF p_memb_rec.membership_id IS NULL OR p_memb_rec.membership_id = FND_API.g_miss_num THEN
251 LOOP
252 l_dummy := NULL;
253 OPEN c_id;
254 FETCH c_id INTO l_membership_id;
255 CLOSE c_id;
256
257 OPEN c_id_exists(l_membership_id);
258 FETCH c_id_exists INTO l_dummy;
259 CLOSE c_id_exists;
260 EXIT WHEN l_dummy IS NULL;
261 END LOOP;
262 ELSE
263 l_membership_id := p_memb_rec.membership_id;
264 END IF;
265
266 -- Debug Message
267 IF (PV_DEBUG_HIGH_ON) THEN
268 PVX_UTILITY_PVT.debug_message( 'Private API: Calling create table handler');
269 END IF;
270 -- Invoke table handler(Pv_Pg_Memberships_Pkg.Insert_Row)
271 Pv_Pg_Memberships_Pkg.Insert_Row(
272 px_membership_id => l_membership_id,
273 px_object_version_number => l_object_version_number,
274 p_partner_id => p_memb_rec.partner_id,
275 p_program_id => p_memb_rec.program_id,
276 p_start_date => p_memb_rec.start_date,
277 p_original_end_date => p_memb_rec.original_end_date,
278 p_actual_end_date => p_memb_rec.actual_end_date,
279 p_membership_status_code => p_memb_rec.membership_status_code,
280 p_status_reason_code => p_memb_rec.status_reason_code,
281 p_enrl_request_id => p_memb_rec.enrl_request_id,
282 p_created_by => FND_GLOBAL.USER_ID,
283 p_creation_date => SYSDATE,
284 p_last_updated_by => FND_GLOBAL.USER_ID,
285 p_last_update_date => SYSDATE,
286 p_last_update_login => FND_GLOBAL.conc_login_id,
287 p_attribute1 => p_memb_rec.attribute1,
288 p_attribute2 => p_memb_rec.attribute2,
289 p_attribute3 => p_memb_rec.attribute3,
290 p_attribute4 => p_memb_rec.attribute4,
291 p_attribute5 => p_memb_rec.attribute5,
292 p_attribute6 => p_memb_rec.attribute6,
293 p_attribute7 => p_memb_rec.attribute7,
294 p_attribute8 => p_memb_rec.attribute8,
295 p_attribute9 => p_memb_rec.attribute9,
296 p_attribute10 => p_memb_rec.attribute10,
297 p_attribute11 => p_memb_rec.attribute11,
298 p_attribute12 => p_memb_rec.attribute12,
299 p_attribute13 => p_memb_rec.attribute13,
300 p_attribute14 => p_memb_rec.attribute14,
301 p_attribute15 => p_memb_rec.attribute15
302 );
303
304 x_membership_id := l_membership_id;
305 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
306 RAISE FND_API.G_EXC_ERROR;
307 END IF;
308 --
309 -- End of API body
310 --
311
312 -- Standard check for p_commit
313 IF FND_API.to_Boolean( p_commit )
314 THEN
315 COMMIT WORK;
316 END IF;
317
318
319 -- Debug Message
320 IF (PV_DEBUG_HIGH_ON) THEN
321 PVX_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
322 END IF;
323
324
325
326 -- Standard call to get message count and if count is 1, get message info.
327 FND_MSG_PUB.Count_And_Get
328 (p_count => x_msg_count,
329 p_data => x_msg_data
330 );
331 EXCEPTION
332
333 WHEN PVX_UTILITY_PVT.resource_locked THEN
334 x_return_status := FND_API.g_ret_sts_error;
335 PVX_UTILITY_PVT.Error_Message(p_message_name => 'PV_API_RESOURCE_LOCKED');
336
337 WHEN FND_API.G_EXC_ERROR THEN
338 ROLLBACK TO CREATE_Pg_Memberships_PVT;
339 x_return_status := FND_API.G_RET_STS_ERROR;
340 -- Standard call to get message count and if count=1, get the message
341 FND_MSG_PUB.Count_And_Get (
342 p_encoded => FND_API.G_FALSE,
343 p_count => x_msg_count,
344 p_data => x_msg_data
345 );
346
347 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
348 ROLLBACK TO CREATE_Pg_Memberships_PVT;
349 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
350 -- Standard call to get message count and if count=1, get the message
351 FND_MSG_PUB.Count_And_Get (
352 p_encoded => FND_API.G_FALSE,
353 p_count => x_msg_count,
354 p_data => x_msg_data
355 );
356
357 WHEN OTHERS THEN
358 ROLLBACK TO CREATE_Pg_Memberships_PVT;
359 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
360 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
361 THEN
362 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
363 END IF;
364 -- Standard call to get message count and if count=1, get the message
365 FND_MSG_PUB.Count_And_Get (
366 p_encoded => FND_API.G_FALSE,
367 p_count => x_msg_count,
368 p_data => x_msg_data
369 );
370 End Create_Pg_Memberships;
371
372
373 -- ==============================================================================
374 -- Start of Comments
375 -- ==============================================================================
376 -- API Name
377 -- Update_Pg_Memberships
378 -- Type
379 -- Private
380 -- Pre-Req
381 --
382 -- Parameters
383 --
384 -- IN
385 -- p_api_version_number IN NUMBER Required
386 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API_G_FALSE
387 -- p_commit IN VARCHAR2 Optional Default = FND_API.G_FALSE
388 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
389 -- p_memb_rec IN memb_rec_type Required
390 --
391 -- OUT
392 -- x_return_status OUT VARCHAR2
393 -- x_msg_count OUT NUMBER
394 -- x_msg_data OUT VARCHAR2
395 -- Version : Current version 1.0
396 -- Note: This automatic generated procedure definition, it includes standard IN/OUT parameters
397 -- and basic operation, developer must manually add parameters and business logic as necessary.
398 --
399 -- History
400 --
401 -- NOTE
402 --
403 -- End of Comments
404 -- ==============================================================================
405
406 PROCEDURE Update_Pg_Memberships(
407 p_api_version_number IN NUMBER,
408 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
409 p_commit IN VARCHAR2 := FND_API.G_FALSE,
410 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
411
412 x_return_status OUT NOCOPY VARCHAR2,
413 x_msg_count OUT NOCOPY NUMBER,
414 x_msg_data OUT NOCOPY VARCHAR2,
415
416 p_memb_rec IN memb_rec_type
417 )
418
419 IS
420
421
422 CURSOR c_get_pg_memberships(membership_id NUMBER) IS
423 SELECT *
424 FROM PV_PG_MEMBERSHIPS
425 WHERE membership_id = p_memb_rec.membership_id;
426 -- Hint: Developer need to provide Where clause
427
428
429 L_API_NAME CONSTANT VARCHAR2(30) := 'Update_Pg_Memberships';
430 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
431 -- Local Variables
432 l_object_version_number NUMBER;
433 l_membership_id NUMBER;
434 l_ref_memb_rec c_get_Pg_Memberships%ROWTYPE ;
435 l_tar_memb_rec memb_rec_type := P_memb_rec;
436 l_rowid ROWID;
437
438 BEGIN
439 -- Standard Start of API savepoint
440 SAVEPOINT update_pg_memberships_pvt;
441
442 -- Standard call to check for call compatibility.
443 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
444 p_api_version_number,
445 l_api_name,
446 G_PKG_NAME)
447 THEN
448 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
449 END IF;
450
451
452 -- Initialize message list if p_init_msg_list is set to TRUE.
453 IF FND_API.to_Boolean( p_init_msg_list )
454 THEN
455 FND_MSG_PUB.initialize;
456 END IF;
457
458
459
460 -- Debug Message
461
462
463
464 IF (PV_DEBUG_HIGH_ON) THEN
465 PVX_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
466 END IF;
467
468 -- Initialize API return status to SUCCESS
469 x_return_status := FND_API.G_RET_STS_SUCCESS;
470
471 -- Debug Message
472 IF (PV_DEBUG_HIGH_ON) THEN
473 PVX_UTILITY_PVT.debug_message('Private API: - Open Cursor to Select');
474 END IF;
475
476
477
478 OPEN c_get_Pg_Memberships( l_tar_memb_rec.membership_id);
479
480 FETCH c_get_Pg_Memberships INTO l_ref_memb_rec ;
481
482 If ( c_get_Pg_Memberships%NOTFOUND) THEN
483 PVX_UTILITY_PVT.Error_Message(p_message_name => 'API_MISSING_UPDATE_TARGET',
484 p_token_name => 'INFO',
485 p_token_value => 'Pg_Memberships') ;
486 RAISE FND_API.G_EXC_ERROR;
487 END IF;
488 -- Debug Message
489
490 CLOSE c_get_Pg_Memberships;
491
492
493 If (l_tar_memb_rec.object_version_number is NULL or
494 l_tar_memb_rec.object_version_number = FND_API.G_MISS_NUM ) Then
495 PVX_UTILITY_PVT.Error_Message(p_message_name => 'API_VERSION_MISSING',
496 p_token_name => 'COLUMN',
497 p_token_value => 'Last_Update_Date') ;
498 raise FND_API.G_EXC_ERROR;
499 End if;
500 -- Check Whether record has been changed by someone else
501 If (l_tar_memb_rec.object_version_number <> l_ref_memb_rec.object_version_number) Then
502 PVX_UTILITY_PVT.Error_Message(p_message_name => 'API_RECORD_CHANGED',
503 p_token_name => 'INFO',
504 p_token_value => 'Pg_Memberships') ;
505 raise FND_API.G_EXC_ERROR;
506 End if;
507
508 -- Invoke table handler(Pv_Pg_Memberships_Pkg.Update_Row)
509 IF p_memb_rec.original_end_date < l_ref_memb_rec.start_date THEN
510
511 FND_MESSAGE.set_name('PV', 'PV_END_DATE_SMALL_START_DATE');
512 FND_MSG_PUB.add;
513 RAISE FND_API.G_EXC_ERROR;
514 END IF;
515
516 IF ( P_validation_level >= FND_API.G_VALID_LEVEL_FULL)
517 THEN
518 -- Debug message
519
520 IF (PV_DEBUG_HIGH_ON) THEN
521 PVX_UTILITY_PVT.debug_message('Private API: Validate_Pg_Memberships');
522 END IF;
523
524
525 -- Invoke validation procedures
526 Validate_pg_memberships(
527 p_api_version_number => 1.0,
528 p_init_msg_list => FND_API.G_FALSE,
529 p_validation_level => p_validation_level,
530 p_validation_mode => JTF_PLSQL_API.g_update,
531 p_memb_rec => p_memb_rec,
532 x_return_status => x_return_status,
533 x_msg_count => x_msg_count,
534 x_msg_data => x_msg_data);
535 END IF;
536
537 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
538 RAISE FND_API.G_EXC_ERROR;
539 END IF;
540
541
542 -- Debug Message
543 IF (PV_DEBUG_HIGH_ON) THEN
544 PVX_UTILITY_PVT.debug_message('Private API: Calling update table handler');
545 END IF;
546
547
548
549
550
551 Pv_Pg_Memberships_Pkg.Update_Row(
552 p_membership_id => p_memb_rec.membership_id,
553 p_object_version_number => p_memb_rec.object_version_number,
554 p_partner_id => p_memb_rec.partner_id,
555 p_program_id => p_memb_rec.program_id,
556 p_start_date => p_memb_rec.start_date,
557 p_original_end_date => p_memb_rec.original_end_date,
558 p_actual_end_date => p_memb_rec.actual_end_date,
559 p_membership_status_code => p_memb_rec.membership_status_code,
560 p_status_reason_code => p_memb_rec.status_reason_code,
561 p_enrl_request_id => p_memb_rec.enrl_request_id,
562 p_last_updated_by => FND_GLOBAL.USER_ID,
563 p_last_update_date => SYSDATE,
564 p_last_update_login => FND_GLOBAL.conc_login_id,
565 p_attribute1 => p_memb_rec.attribute1,
566 p_attribute2 => p_memb_rec.attribute2,
567 p_attribute3 => p_memb_rec.attribute3,
568 p_attribute4 => p_memb_rec.attribute4,
569 p_attribute5 => p_memb_rec.attribute5,
570 p_attribute6 => p_memb_rec.attribute6,
571 p_attribute7 => p_memb_rec.attribute7,
572 p_attribute8 => p_memb_rec.attribute8,
573 p_attribute9 => p_memb_rec.attribute9,
574 p_attribute10 => p_memb_rec.attribute10,
575 p_attribute11 => p_memb_rec.attribute11,
576 p_attribute12 => p_memb_rec.attribute12,
577 p_attribute13 => p_memb_rec.attribute13,
578 p_attribute14 => p_memb_rec.attribute14,
579 p_attribute15 => p_memb_rec.attribute15
580 );
581 --
582 -- End of API body.
583 --
584
585 -- Standard check for p_commit
586 IF FND_API.to_Boolean( p_commit )
587 THEN
588 COMMIT WORK;
589 END IF;
590
591
592 -- Debug Message
593 -- Debug Message
594 IF (PV_DEBUG_HIGH_ON) THEN
595 PVX_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
596 END IF;
597
598
599
600 -- Standard call to get message count and if count is 1, get message info.
601 FND_MSG_PUB.Count_And_Get
602 (p_count => x_msg_count,
603 p_data => x_msg_data
604 );
605 EXCEPTION
606
607 WHEN PVX_UTILITY_PVT.resource_locked THEN
608 x_return_status := FND_API.g_ret_sts_error;
609 PVX_UTILITY_PVT.Error_Message(p_message_name => 'PV_API_RESOURCE_LOCKED');
610
611 WHEN FND_API.G_EXC_ERROR THEN
612 ROLLBACK TO UPDATE_Pg_Memberships_PVT;
613 x_return_status := FND_API.G_RET_STS_ERROR;
614 -- Standard call to get message count and if count=1, get the message
615 FND_MSG_PUB.Count_And_Get (
616 p_encoded => FND_API.G_FALSE,
617 p_count => x_msg_count,
618 p_data => x_msg_data
619 );
620
621 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
622 ROLLBACK TO UPDATE_Pg_Memberships_PVT;
623 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
624 -- Standard call to get message count and if count=1, get the message
625 FND_MSG_PUB.Count_And_Get (
626 p_encoded => FND_API.G_FALSE,
627 p_count => x_msg_count,
628 p_data => x_msg_data
629 );
630
631 WHEN OTHERS THEN
632 ROLLBACK TO UPDATE_Pg_Memberships_PVT;
633 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
634 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
635 THEN
636 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
637 END IF;
638 -- Standard call to get message count and if count=1, get the message
639 FND_MSG_PUB.Count_And_Get (
640 p_encoded => FND_API.G_FALSE,
641 p_count => x_msg_count,
642 p_data => x_msg_data
643 );
644 End Update_Pg_Memberships;
645
646
647 -- ==============================================================================
648 -- Start of Comments
649 -- ==============================================================================
650 -- API Name
651 -- Delete_Pg_Memberships
652 -- Type
653 -- Private
654 -- Pre-Req
655 --
656 -- Parameters
657 --
658 -- IN
659 -- p_api_version_number IN NUMBER Required
660 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API_G_FALSE
661 -- p_commit IN VARCHAR2 Optional Default = FND_API.G_FALSE
662 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
663 -- p_membership_id IN NUMBER
664 -- p_object_version_number IN NUMBER Optional Default = NULL
665 --
666 -- OUT
667 -- x_return_status OUT VARCHAR2
668 -- x_msg_count OUT NUMBER
669 -- x_msg_data OUT VARCHAR2
670 -- Version : Current version 1.0
671 -- Note: This automatic generated procedure definition, it includes standard IN/OUT parameters
672 -- and basic operation, developer must manually add parameters and business logic as necessary.
673 --
674 -- History
675 --
676 -- NOTE
677 --
678 -- End of Comments
679 -- ==============================================================================
680
681 PROCEDURE Delete_Pg_Memberships(
682 p_api_version_number IN NUMBER,
683 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
684 p_commit IN VARCHAR2 := FND_API.G_FALSE,
685 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
686 x_return_status OUT NOCOPY VARCHAR2,
687 x_msg_count OUT NOCOPY NUMBER,
688 x_msg_data OUT NOCOPY VARCHAR2,
689 p_membership_id IN NUMBER,
690 p_object_version_number IN NUMBER
691 )
692
693 IS
694 L_API_NAME CONSTANT VARCHAR2(30) := 'Delete_Pg_Memberships';
695 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
696 l_object_version_number NUMBER;
697
698 BEGIN
699 -- Standard Start of API savepoint
700 SAVEPOINT delete_pg_memberships_pvt;
701
702 -- Standard call to check for call compatibility.
703 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
704 p_api_version_number,
705 l_api_name,
706 G_PKG_NAME)
707 THEN
708 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
709 END IF;
710
711
712 -- Initialize message list if p_init_msg_list is set to TRUE.
713 IF FND_API.to_Boolean( p_init_msg_list )
714 THEN
715 FND_MSG_PUB.initialize;
716 END IF;
717
718
719
720 -- Debug Message
721 IF (PV_DEBUG_HIGH_ON) THEN
722 PVX_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
723 END IF;
724
725
726
727
728
729 -- Initialize API return status to SUCCESS
730 x_return_status := FND_API.G_RET_STS_SUCCESS;
731
732 --
733 -- Api body
734 --
735
736 -- Invoke table handler(Pv_Pg_Memberships_Pkg.Delete_Row)
737 Pv_Pg_Memberships_Pkg.Delete_Row(
738 p_membership_id => p_membership_id,
739 p_object_version_number => p_object_version_number );
740 --
741 -- End of API body
742 --
743
744 -- Standard check for p_commit
745 IF FND_API.to_Boolean( p_commit )
746 THEN
747 COMMIT WORK;
748 END IF;
749
750
751
752 -- Debug Message
753 IF (PV_DEBUG_HIGH_ON) THEN
754 PVX_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
755 END IF;
756
757
758
759
760 -- Standard call to get message count and if count is 1, get message info.
761 FND_MSG_PUB.Count_And_Get
762 (p_count => x_msg_count,
763 p_data => x_msg_data
764 );
765 EXCEPTION
766
767 WHEN PVX_UTILITY_PVT.resource_locked THEN
768 x_return_status := FND_API.g_ret_sts_error;
769 PVX_UTILITY_PVT.Error_Message(p_message_name => 'PV_API_RESOURCE_LOCKED');
770
771 WHEN FND_API.G_EXC_ERROR THEN
772 ROLLBACK TO DELETE_Pg_Memberships_PVT;
773 x_return_status := FND_API.G_RET_STS_ERROR;
774 -- Standard call to get message count and if count=1, get the message
775 FND_MSG_PUB.Count_And_Get (
776 p_encoded => FND_API.G_FALSE,
777 p_count => x_msg_count,
778 p_data => x_msg_data
779 );
780
781 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
782 ROLLBACK TO DELETE_Pg_Memberships_PVT;
783 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
784 -- Standard call to get message count and if count=1, get the message
785 FND_MSG_PUB.Count_And_Get (
786 p_encoded => FND_API.G_FALSE,
787 p_count => x_msg_count,
788 p_data => x_msg_data
789 );
790
791 WHEN OTHERS THEN
792 ROLLBACK TO DELETE_Pg_Memberships_PVT;
793 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
794 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
795 THEN
796 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
797 END IF;
798 -- Standard call to get message count and if count=1, get the message
799 FND_MSG_PUB.Count_And_Get (
800 p_encoded => FND_API.G_FALSE,
801 p_count => x_msg_count,
802 p_data => x_msg_data
803 );
804 End Delete_Pg_Memberships;
805
806
807
808 -- Hint: Primary key needs to be returned.
809 -- ==============================================================================
810 -- Start of Comments
811 -- ==============================================================================
812 -- API Name
813 -- Lock_Pg_Memberships
814 -- Type
815 -- Private
816 -- Pre-Req
817 --
818 -- Parameters
819 --
820 -- IN
821 -- p_api_version_number IN NUMBER Required
822 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API_G_FALSE
823 -- p_commit IN VARCHAR2 Optional Default = FND_API.G_FALSE
824 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
825 -- p_memb_rec IN memb_rec_type Required
826 --
827 -- OUT
828 -- x_return_status OUT VARCHAR2
829 -- x_msg_count OUT NUMBER
830 -- x_msg_data OUT VARCHAR2
831 -- Version : Current version 1.0
832 -- Note: This automatic generated procedure definition, it includes standard IN/OUT parameters
833 -- and basic operation, developer must manually add parameters and business logic as necessary.
834 --
835 -- History
836 --
837 -- NOTE
838 --
839 -- End of Comments
840 -- ==============================================================================
841
842 PROCEDURE Lock_Pg_Memberships(
843 p_api_version_number IN NUMBER,
844 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
845
846 x_return_status OUT NOCOPY VARCHAR2,
847 x_msg_count OUT NOCOPY NUMBER,
848 x_msg_data OUT NOCOPY VARCHAR2,
849
850 p_membership_id IN NUMBER,
851 p_object_version IN NUMBER
852 )
853
854 IS
855 L_API_NAME CONSTANT VARCHAR2(30) := 'Lock_Pg_Memberships';
856 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
857 L_FULL_NAME CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
858 l_membership_id NUMBER;
859
860 BEGIN
861
862 -- Debug Message
863
864
865 IF (PV_DEBUG_HIGH_ON) THEN
866 PVX_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
867 END IF;
868
869
870
871 -- Initialize message list if p_init_msg_list is set to TRUE.
872 IF FND_API.to_Boolean( p_init_msg_list )
873 THEN
874 FND_MSG_PUB.initialize;
875 END IF;
876
877
878
879 -- Standard call to check for call compatibility.
880 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
881 p_api_version_number,
882 l_api_name,
883 G_PKG_NAME)
884 THEN
885 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
886 END IF;
887
888
889
890 -- Initialize API return status to SUCCESS
891 x_return_status := FND_API.G_RET_STS_SUCCESS;
892
893
894 ------------------------ lock -------------------------
895 Pv_Pg_Memberships_Pkg.Lock_Row(l_membership_id,p_object_version);
896
897
898 -------------------- finish --------------------------
899 FND_MSG_PUB.count_and_get(
900 p_encoded => FND_API.g_false,
901 p_count => x_msg_count,
902 p_data => x_msg_data);
903
904 IF (PV_DEBUG_HIGH_ON) THEN
905 PVX_UTILITY_PVT.debug_message(l_full_name ||': end');
906 END IF;
907
908 EXCEPTION
909
910 WHEN PVX_UTILITY_PVT.resource_locked THEN
911 x_return_status := FND_API.g_ret_sts_error;
912 PVX_UTILITY_PVT.Error_Message(p_message_name => 'PV_API_RESOURCE_LOCKED');
913
914 WHEN FND_API.G_EXC_ERROR THEN
915 ROLLBACK TO LOCK_Pg_Memberships_PVT;
916 x_return_status := FND_API.G_RET_STS_ERROR;
917 -- Standard call to get message count and if count=1, get the message
918 FND_MSG_PUB.Count_And_Get (
919 p_encoded => FND_API.G_FALSE,
920 p_count => x_msg_count,
921 p_data => x_msg_data
922 );
923
924 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
925 ROLLBACK TO LOCK_Pg_Memberships_PVT;
926 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
927 -- Standard call to get message count and if count=1, get the message
928 FND_MSG_PUB.Count_And_Get (
929 p_encoded => FND_API.G_FALSE,
930 p_count => x_msg_count,
931 p_data => x_msg_data
932 );
933
934 WHEN OTHERS THEN
935 ROLLBACK TO LOCK_Pg_Memberships_PVT;
936 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
937 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
938 THEN
939 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
940 END IF;
941 -- Standard call to get message count and if count=1, get the message
942 FND_MSG_PUB.Count_And_Get (
943 p_encoded => FND_API.G_FALSE,
944 p_count => x_msg_count,
945 p_data => x_msg_data
946 );
947 End Lock_Pg_Memberships;
948
949
950
951
952 PROCEDURE check_Memb_Uk_Items(
953 p_memb_rec IN memb_rec_type,
954 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create,
955 x_return_status OUT NOCOPY VARCHAR2)
956 IS
957 l_valid_flag VARCHAR2(1);
958
959 BEGIN
960 x_return_status := FND_API.g_ret_sts_success;
961 IF p_validation_mode = JTF_PLSQL_API.g_create
962 AND p_memb_rec.membership_id IS NOT NULL
963 THEN
964 l_valid_flag := PVX_UTILITY_PVT.check_uniqueness(
965 'pv_pg_memberships',
966 'membership_id = ''' || p_memb_rec.membership_id ||''''
967 );
968 END IF;
969
970 IF l_valid_flag = FND_API.g_false THEN
971 PVX_UTILITY_PVT.Error_Message(p_message_name => 'PV_membership_id_DUPLICATE');
972 x_return_status := FND_API.g_ret_sts_error;
973 END IF;
974
975 END check_Memb_Uk_Items;
976
977
978
979 PROCEDURE check_Memb_Req_Items(
980 p_memb_rec IN memb_rec_type,
981 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create,
982 x_return_status OUT NOCOPY VARCHAR2
983 )
984 IS
985 BEGIN
986 x_return_status := FND_API.g_ret_sts_success;
987
988 IF p_validation_mode = JTF_PLSQL_API.g_create THEN
989
990 /**
991 IF p_memb_rec.membership_id = FND_API.G_MISS_NUM OR p_memb_rec.membership_id IS NULL THEN
992 PVX_UTILITY_PVT.Error_Message('AMS_API_MISSING_FIELD', 'MISS_FIELD', 'MEMBERSHIP_ID' );
993 x_return_status := FND_API.g_ret_sts_error;
994 END IF;
995
996
997 IF p_memb_rec.object_version_number = FND_API.G_MISS_NUM OR p_memb_rec.object_version_number IS NULL THEN
998 PVX_UTILITY_PVT.Error_Message('AMS_API_MISSING_FIELD', 'MISS_FIELD', 'OBJECT_VERSION_NUMBER' );
999 x_return_status := FND_API.g_ret_sts_error;
1000 END IF;
1001 */
1002
1003 IF p_memb_rec.partner_id = FND_API.G_MISS_NUM OR p_memb_rec.partner_id IS NULL THEN
1004 PVX_UTILITY_PVT.Error_Message('AMS_API_MISSING_FIELD', 'MISS_FIELD', 'PARTNER_ID' );
1005 x_return_status := FND_API.g_ret_sts_error;
1006 END IF;
1007
1008
1009 IF p_memb_rec.program_id = FND_API.G_MISS_NUM OR p_memb_rec.program_id IS NULL THEN
1010 PVX_UTILITY_PVT.Error_Message('AMS_API_MISSING_FIELD', 'MISS_FIELD', 'PROGRAM_ID' );
1011 x_return_status := FND_API.g_ret_sts_error;
1012 END IF;
1013
1014
1015 IF p_memb_rec.start_date = FND_API.G_MISS_DATE OR p_memb_rec.start_date IS NULL THEN
1016 PVX_UTILITY_PVT.Error_Message('AMS_API_MISSING_FIELD', 'MISS_FIELD', 'START_DATE' );
1017 x_return_status := FND_API.g_ret_sts_error;
1018 END IF;
1019
1020
1021 IF p_memb_rec.membership_status_code = FND_API.g_miss_char OR p_memb_rec.membership_status_code IS NULL THEN
1022 PVX_UTILITY_PVT.Error_Message('AMS_API_MISSING_FIELD', 'MISS_FIELD', 'MEMBERSHIP_STATUS_CODE' );
1023 x_return_status := FND_API.g_ret_sts_error;
1024 END IF;
1025
1026
1027 IF p_memb_rec.enrl_request_id = FND_API.G_MISS_NUM OR p_memb_rec.enrl_request_id IS NULL THEN
1028 PVX_UTILITY_PVT.Error_Message('AMS_API_MISSING_FIELD', 'MISS_FIELD', 'ENRL_REQUEST_ID' );
1029 x_return_status := FND_API.g_ret_sts_error;
1030 END IF;
1031
1032
1033 ELSE
1034
1035
1036 IF p_memb_rec.membership_id = FND_API.G_MISS_NUM THEN
1037 PVX_UTILITY_PVT.Error_Message('AMS_API_MISSING_FIELD', 'MISS_FIELD', 'MEMBERSHIP_ID' );
1038 x_return_status := FND_API.g_ret_sts_error;
1039 END IF;
1040
1041
1042 IF p_memb_rec.object_version_number = FND_API.G_MISS_NUM THEN
1043 PVX_UTILITY_PVT.Error_Message('AMS_API_MISSING_FIELD', 'MISS_FIELD', 'OBJECT_VERSION_NUMBER' );
1044 x_return_status := FND_API.g_ret_sts_error;
1045 END IF;
1046
1047
1048 IF p_memb_rec.partner_id = FND_API.G_MISS_NUM THEN
1049 PVX_UTILITY_PVT.Error_Message('AMS_API_MISSING_FIELD', 'MISS_FIELD', 'PARTNER_ID' );
1050 x_return_status := FND_API.g_ret_sts_error;
1051 END IF;
1052
1053
1054 IF p_memb_rec.program_id = FND_API.G_MISS_NUM THEN
1055 PVX_UTILITY_PVT.Error_Message('AMS_API_MISSING_FIELD', 'MISS_FIELD', 'PROGRAM_ID' );
1056 x_return_status := FND_API.g_ret_sts_error;
1057 END IF;
1058
1059
1060 IF p_memb_rec.start_date = FND_API.G_MISS_DATE THEN
1061 PVX_UTILITY_PVT.Error_Message('AMS_API_MISSING_FIELD', 'MISS_FIELD', 'START_DATE' );
1062 x_return_status := FND_API.g_ret_sts_error;
1063 END IF;
1064
1065
1066 IF p_memb_rec.membership_status_code = FND_API.g_miss_char THEN
1067 PVX_UTILITY_PVT.Error_Message('AMS_API_MISSING_FIELD', 'MISS_FIELD', 'MEMBERSHIP_STATUS_CODE' );
1068 x_return_status := FND_API.g_ret_sts_error;
1069 END IF;
1070
1071
1072 IF p_memb_rec.enrl_request_id = FND_API.G_MISS_NUM THEN
1073 PVX_UTILITY_PVT.Error_Message('AMS_API_MISSING_FIELD', 'MISS_FIELD', 'ENRL_REQUEST_ID' );
1074 x_return_status := FND_API.g_ret_sts_error;
1075 END IF;
1076 END IF;
1077
1078 END check_Memb_Req_Items;
1079
1080
1081
1082 PROCEDURE check_Memb_Fk_Items(
1083 p_memb_rec IN memb_rec_type,
1084 x_return_status OUT NOCOPY VARCHAR2
1085 )
1086 IS
1087 BEGIN
1088 x_return_status := FND_API.g_ret_sts_success;
1089
1090 -- Enter custom code here
1091
1092 END check_Memb_Fk_Items;
1093
1094
1095
1096 PROCEDURE check_Memb_Lookup_Items(
1097 p_memb_rec IN memb_rec_type,
1098 x_return_status OUT NOCOPY VARCHAR2
1099 )
1100 IS
1101 l_exists VARCHAR2(1);
1102 l_lookup_type VARCHAR2(30);
1103 l_lookup_exists VARCHAR2(1);
1104 BEGIN
1105 x_return_status := FND_API.g_ret_sts_success;
1106 --validate lookup
1107 /**l_lookup_type := 'PV_MEMB_STATUS_REASON_CODE';
1108 l_lookup_exists := PVX_UTILITY_PVT.check_lookup_exists
1109 ( p_lookup_table_name => 'PV_LOOKUPS'
1110 ,p_lookup_type => l_lookup_type
1111 ,p_lookup_code => p_memb_rec.status_reason_code
1112 );
1113 IF NOT FND_API.to_boolean(l_lookup_exists) THEN
1114 x_return_status := FND_API.g_ret_sts_error;
1115 FND_MESSAGE.set_name('PV', 'PV_INVALID_LOOKUP_CODE');
1116 FND_MESSAGE.set_token('LOOKUP_TYPE', l_lookup_type );
1117 FND_MESSAGE.set_token('LOOKUP_CODE', p_memb_rec.status_reason_code );
1118 FND_MSG_PUB.add;
1119 END IF;
1120 */
1121
1122 -- Enter custom code here
1123
1124 END check_Memb_Lookup_Items;
1125
1126
1127
1128 PROCEDURE Check_Memb_Items (
1129 P_memb_rec IN memb_rec_type,
1130 p_validation_mode IN VARCHAR2,
1131 x_return_status OUT NOCOPY VARCHAR2
1132 )
1133 IS
1134 l_return_status VARCHAR2(1);
1135 BEGIN
1136
1137 l_return_status := FND_API.g_ret_sts_success;
1138 -- Check Items Uniqueness API calls
1139
1140 check_Memb_Uk_Items(
1141 p_memb_rec => p_memb_rec,
1142 p_validation_mode => p_validation_mode,
1143 x_return_status => x_return_status);
1144 IF x_return_status <> FND_API.g_ret_sts_success THEN
1145 l_return_status := FND_API.g_ret_sts_error;
1146 END IF;
1147
1148 -- Check Items Required/NOT NULL API calls
1149
1150 check_memb_req_items(
1151 p_memb_rec => p_memb_rec,
1152 p_validation_mode => p_validation_mode,
1153 x_return_status => x_return_status);
1154 IF x_return_status <> FND_API.g_ret_sts_success THEN
1155 l_return_status := FND_API.g_ret_sts_error;
1156 END IF;
1157 -- Check Items Foreign Keys API calls
1158
1159 check_memb_FK_items(
1160 p_memb_rec => p_memb_rec,
1161 x_return_status => x_return_status);
1162 IF x_return_status <> FND_API.g_ret_sts_success THEN
1163 l_return_status := FND_API.g_ret_sts_error;
1164 END IF;
1165 -- Check Items Lookups
1166
1167 check_memb_Lookup_items(
1168 p_memb_rec => p_memb_rec,
1169 x_return_status => x_return_status);
1170 IF x_return_status <> FND_API.g_ret_sts_success THEN
1171 l_return_status := FND_API.g_ret_sts_error;
1172 END IF;
1173
1174 x_return_status := l_return_status;
1175
1176 END Check_memb_Items;
1177
1178
1179
1180
1181
1182 PROCEDURE Complete_Memb_Rec (
1183 p_memb_rec IN memb_rec_type,
1184 x_complete_rec OUT NOCOPY memb_rec_type)
1185 IS
1186 l_return_status VARCHAR2(1);
1187
1188 CURSOR c_complete IS
1189 SELECT *
1190 FROM pv_pg_memberships
1191 WHERE membership_id = p_memb_rec.membership_id;
1192 l_memb_rec c_complete%ROWTYPE;
1193 BEGIN
1194 x_complete_rec := p_memb_rec;
1195
1196
1197 OPEN c_complete;
1198 FETCH c_complete INTO l_memb_rec;
1199 CLOSE c_complete;
1200
1201 -- membership_id
1202 IF p_memb_rec.membership_id IS NULL THEN
1203 x_complete_rec.membership_id := l_memb_rec.membership_id;
1204 END IF;
1205
1206 -- object_version_number
1207 IF p_memb_rec.object_version_number IS NULL THEN
1208 x_complete_rec.object_version_number := l_memb_rec.object_version_number;
1209 END IF;
1210
1211 -- partner_id
1212 IF p_memb_rec.partner_id IS NULL THEN
1213 x_complete_rec.partner_id := l_memb_rec.partner_id;
1214 END IF;
1215
1216 -- program_id
1217 IF p_memb_rec.program_id IS NULL THEN
1218 x_complete_rec.program_id := l_memb_rec.program_id;
1219 END IF;
1220
1221 -- start_date
1222 IF p_memb_rec.start_date IS NULL THEN
1223 x_complete_rec.start_date := l_memb_rec.start_date;
1224 END IF;
1225
1226 -- original_end_date
1227 IF p_memb_rec.original_end_date IS NULL THEN
1228 x_complete_rec.original_end_date := l_memb_rec.original_end_date;
1229 END IF;
1230
1231 -- actual_end_date
1232 IF p_memb_rec.actual_end_date IS NULL THEN
1233 x_complete_rec.actual_end_date := l_memb_rec.actual_end_date;
1234 END IF;
1235
1236 -- membership_status_code
1237 IF p_memb_rec.membership_status_code IS NULL THEN
1238 x_complete_rec.membership_status_code := l_memb_rec.membership_status_code;
1239 END IF;
1240
1241 -- status_reason_code
1242 IF p_memb_rec.status_reason_code IS NULL THEN
1243 x_complete_rec.status_reason_code := l_memb_rec.status_reason_code;
1244 END IF;
1245
1246 -- enrl_request_id
1247 IF p_memb_rec.enrl_request_id IS NULL THEN
1248 x_complete_rec.enrl_request_id := l_memb_rec.enrl_request_id;
1249 END IF;
1250
1251 -- created_by
1252 IF p_memb_rec.created_by IS NULL THEN
1253 x_complete_rec.created_by := l_memb_rec.created_by;
1254 END IF;
1255
1256 -- creation_date
1257 IF p_memb_rec.creation_date IS NULL THEN
1258 x_complete_rec.creation_date := l_memb_rec.creation_date;
1259 END IF;
1260
1261 -- last_updated_by
1262 IF p_memb_rec.last_updated_by IS NULL THEN
1263 x_complete_rec.last_updated_by := l_memb_rec.last_updated_by;
1264 END IF;
1265
1266 -- last_update_date
1267 IF p_memb_rec.last_update_date IS NULL THEN
1268 x_complete_rec.last_update_date := l_memb_rec.last_update_date;
1269 END IF;
1270
1271 -- last_update_login
1272 IF p_memb_rec.last_update_login IS NULL THEN
1273 x_complete_rec.last_update_login := l_memb_rec.last_update_login;
1274 END IF;
1275 -- Note: Developers need to modify the procedure
1276 -- to handle any business specific requirements.
1277 END Complete_Memb_Rec;
1278
1279
1280
1281
1282 PROCEDURE Default_Memb_Items ( p_memb_rec IN memb_rec_type ,
1283 x_memb_rec OUT NOCOPY memb_rec_type )
1284 IS
1285 l_memb_rec memb_rec_type := p_memb_rec;
1286 BEGIN
1287 -- Developers should put their code to default the record type
1288 -- e.g. IF p_campaign_rec.status_code IS NULL
1289 -- OR p_campaign_rec.status_code = FND_API.G_MISS_CHAR THEN
1290 -- l_campaign_rec.status_code := 'NEW' ;
1291 -- END IF ;
1292 --
1293 NULL ;
1294 END;
1295
1296
1297
1298
1299 PROCEDURE Validate_Pg_Memberships(
1300 p_api_version_number IN NUMBER,
1301 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1302 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1303 p_memb_rec IN memb_rec_type,
1304 p_validation_mode IN VARCHAR2,
1305 x_return_status OUT NOCOPY VARCHAR2,
1306 x_msg_count OUT NOCOPY NUMBER,
1307 x_msg_data OUT NOCOPY VARCHAR2
1308 )
1309 IS
1310 L_API_NAME CONSTANT VARCHAR2(30) := 'Validate_Pg_Memberships';
1311 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
1312 l_object_version_number NUMBER;
1313 l_memb_rec memb_rec_type;
1314 l_memb_rec_out memb_rec_type;
1315
1316 BEGIN
1317 -- Standard Start of API savepoint
1318 SAVEPOINT validate_pg_memberships_;
1319
1320 -- Standard call to check for call compatibility.
1321 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1322 p_api_version_number,
1323 l_api_name,
1324 G_PKG_NAME)
1325 THEN
1326 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1327 END IF;
1328
1329
1330 -- Initialize message list if p_init_msg_list is set to TRUE.
1331 IF FND_API.to_Boolean( p_init_msg_list )
1332 THEN
1333 FND_MSG_PUB.initialize;
1334 END IF;
1335
1336
1337 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
1338 Check_memb_Items(
1339 p_memb_rec => p_memb_rec,
1340 p_validation_mode => p_validation_mode,
1341 x_return_status => x_return_status
1342 );
1343
1344 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1345 RAISE FND_API.G_EXC_ERROR;
1346 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1347 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1348 END IF;
1349 END IF;
1350
1351 IF p_validation_mode = JTF_PLSQL_API.g_create THEN
1352 Default_Memb_Items (p_memb_rec => p_memb_rec ,
1353 x_memb_rec => l_memb_rec) ;
1354 END IF ;
1355
1356
1357 Complete_memb_Rec(
1358 p_memb_rec => l_memb_rec,
1359 x_complete_rec => l_memb_rec_out
1360 );
1361
1362 l_memb_rec := l_memb_rec_out;
1363
1364 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
1365 Validate_memb_Rec(
1366 p_api_version_number => 1.0,
1367 p_init_msg_list => FND_API.G_FALSE,
1368 x_return_status => x_return_status,
1369 x_msg_count => x_msg_count,
1370 x_msg_data => x_msg_data,
1371 p_memb_rec => l_memb_rec);
1372
1373 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1374 RAISE FND_API.G_EXC_ERROR;
1375 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1376 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1377 END IF;
1378 END IF;
1379
1380
1381 -- Debug Message
1382 IF (PV_DEBUG_HIGH_ON) THEN
1383 PVX_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
1384 END IF;
1385
1386
1387 -- Initialize API return status to SUCCESS
1388 x_return_status := FND_API.G_RET_STS_SUCCESS;
1389
1390 IF (PV_DEBUG_HIGH_ON) THEN
1391 PVX_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
1392 END IF;
1393 -- Debug Message
1394
1395
1396
1397 -- Standard call to get message count and if count is 1, get message info.
1398 FND_MSG_PUB.Count_And_Get
1399 (p_count => x_msg_count,
1400 p_data => x_msg_data
1401 );
1402 EXCEPTION
1403
1404 WHEN PVX_UTILITY_PVT.resource_locked THEN
1405 x_return_status := FND_API.g_ret_sts_error;
1406 PVX_UTILITY_PVT.Error_Message(p_message_name => 'PV_API_RESOURCE_LOCKED');
1407
1408 WHEN FND_API.G_EXC_ERROR THEN
1409 ROLLBACK TO VALIDATE_Pg_Memberships_;
1410 x_return_status := FND_API.G_RET_STS_ERROR;
1411 -- Standard call to get message count and if count=1, get the message
1412 FND_MSG_PUB.Count_And_Get (
1413 p_encoded => FND_API.G_FALSE,
1414 p_count => x_msg_count,
1415 p_data => x_msg_data
1416 );
1417
1418 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1419 ROLLBACK TO VALIDATE_Pg_Memberships_;
1420 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1421 -- Standard call to get message count and if count=1, get the message
1422 FND_MSG_PUB.Count_And_Get (
1423 p_encoded => FND_API.G_FALSE,
1424 p_count => x_msg_count,
1425 p_data => x_msg_data
1426 );
1427
1428 WHEN OTHERS THEN
1429 ROLLBACK TO VALIDATE_Pg_Memberships_;
1430 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1431 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1432 THEN
1433 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1434 END IF;
1435 -- Standard call to get message count and if count=1, get the message
1436 FND_MSG_PUB.Count_And_Get (
1437 p_encoded => FND_API.G_FALSE,
1438 p_count => x_msg_count,
1439 p_data => x_msg_data
1440 );
1441 End Validate_Pg_Memberships;
1442
1443
1444 PROCEDURE Validate_Memb_Rec (
1445 p_api_version_number IN NUMBER,
1446 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1447 x_return_status OUT NOCOPY VARCHAR2,
1448 x_msg_count OUT NOCOPY NUMBER,
1449 x_msg_data OUT NOCOPY VARCHAR2,
1450 p_memb_rec IN memb_rec_type
1451 )
1452 IS
1453 BEGIN
1454 -- Initialize message list if p_init_msg_list is set to TRUE.
1455 IF FND_API.to_Boolean( p_init_msg_list )
1456 THEN
1457 FND_MSG_PUB.initialize;
1458 END IF;
1459
1460
1461
1462 -- Initialize API return status to SUCCESS
1463 x_return_status := FND_API.G_RET_STS_SUCCESS;
1464
1465 -- Hint: Validate data
1466 -- If data not valid
1467 -- THEN
1468 -- x_return_status := FND_API.G_RET_STS_ERROR;
1469
1470 -- Debug Message
1471 -- Debug Message
1472 IF (PV_DEBUG_HIGH_ON) THEN
1473 PVX_UTILITY_PVT.debug_message('Private API: Validate_dm_model_rec');
1474 END IF;
1475
1476 -- Standard call to get message count and if count is 1, get message info.
1477 FND_MSG_PUB.Count_And_Get
1478 (p_count => x_msg_count,
1479 p_data => x_msg_data
1480 );
1481 END Validate_memb_Rec;
1482
1483 --------------------------------------------------------------------------
1484
1485 --------------------------------------------------------------------------
1486 FUNCTION isTerminatable(p_program_id IN NUMBER,p_partner_id IN NUMBER)
1487 RETURN BOOLEAN IS
1488
1489 l_default_program_id NUMBER;
1490 l_isterminatable boolean:=true;
1491 l_relationship VARCHAR2(15);
1492
1493 CURSOR pstatus_cur(p_ptr_id NUMBER) IS
1494 SELECT status
1495 FROM pv_partner_profiles
1496 WHERE partner_id=p_ptr_id;
1497
1498 BEGIN
1499 l_default_program_id:= isnumber(FND_PROFILE.VALUE('PV_PARTNER_DEFAULT_PROGRAM'));
1500 IF (l_default_program_id is NOT NULL) AND (l_default_program_id =p_program_id) THEN
1501 OPEN pstatus_cur(p_partner_id);
1502 FETCH pstatus_cur into l_relationship;
1503 CLOSE pstatus_cur;
1504
1505 IF l_relationship='I' THEN
1506 l_isterminatable:=true;
1507 ELSE
1508 l_isterminatable:=false;
1509 END IF;
1510 END IF;
1511 RETURN l_isterminatable;
1512
1513 EXCEPTION
1514 WHEN OTHERS THEN
1515 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1516
1517 END isTerminatable;
1518
1519 -------------------------------------------------------------------------------
1520
1521 ----------------------
1522 -- PROCEDURE
1523 -- cancel_all_enrollments
1524 --
1525 -- PURPOSE
1526 -- Terminate one membership given the membership_id. This is also a private procedure
1527 -- this procedure terminates the contract as well as send terminate notifictaion
1528 -- and also log into history
1529 -- but does not terminate deafault program membeship unless the partnership is terminated
1530 -- also terminate pre-reqs if any
1531 -- IN
1532 -- partner_id NUMBER
1533 --
1534 -- USED BY
1535 --
1536 --
1537 -- HISTORY
1538 -- pukken CREATION
1539 --------------------------------------------------------------------------
1540
1541 PROCEDURE cancel_all_enrollments
1542 (
1543 p_enrollment_id_tbl IN JTF_NUMBER_TABLE
1544 , p_status_reason_code IN VARCHAR2 -- pass 'MEMBER_TYPE_CHANGE' if it is happening because of member type change -- it validates against PV_MEMB_STATUS_REASON_CODE
1545 , p_comments IN VARCHAR2 DEFAULT NULL -- pass 'Membership terminated by system as member type is changed' if it is changed because of member type change
1546 , x_return_status OUT NOCOPY VARCHAR2
1547 , x_msg_count OUT NOCOPY NUMBER
1548 , x_msg_data OUT NOCOPY VARCHAR2
1549
1550 )
1551 IS
1552
1553 l_enrl_request_rec PV_Pg_Enrl_Requests_PVT.enrl_request_rec_type ;
1554 l_param_tbl_var PVX_UTILITY_PVT.log_params_tbl_type;
1555 l_object_version_number NUMBER;
1556 l_partner_id NUMBER;
1557 l_enrl_request_id NUMBER;
1558 l_meaning VARCHAR2(80);
1559 l_program_name VARCHAR2(80);
1560
1561 CURSOR enrq_csr (enrl_id NUMBER ) IS
1562 SELECT enrq.enrl_request_id
1563 , enrq.partner_id
1564 , enrq.object_version_number
1565 , prgm.program_name
1566 FROM pv_pg_enrl_requests enrq
1567 , pv_partner_program_vl prgm
1568 WHERE enrq.enrl_request_id=enrl_id
1569 AND enrq.program_id=prgm.program_id;
1570
1571 BEGIN
1572
1573 x_return_status := FND_API.g_ret_sts_success;
1574 IF p_enrollment_id_tbl.exists(1) THEN
1575 FOR i in 1.. p_enrollment_id_tbl.count() LOOP
1576
1577
1578 OPEN enrq_csr(p_enrollment_id_tbl(i));
1579 FETCH enrq_csr into l_enrl_request_id,l_partner_id,l_object_version_number,l_program_name;
1580 CLOSE enrq_csr;
1581 l_enrl_request_rec.enrl_request_id:= l_enrl_request_id;
1582 l_enrl_request_rec.object_version_number:=l_object_version_number;
1583 l_enrl_request_rec.request_status_code:='CANCELLED';
1584
1585 PV_Pg_Enrl_Requests_PVT.Update_Pg_Enrl_Requests
1586 ( p_api_version_number => 1.0
1587 ,p_init_msg_list => Fnd_Api.g_false
1588 ,p_commit => Fnd_Api.g_false
1589 ,x_return_status => x_return_status
1590 ,x_msg_count => x_msg_count
1591 ,x_msg_data => x_msg_data
1592 ,p_enrl_request_rec => l_enrl_request_rec
1593 );
1594
1595
1596 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1597 RAISE FND_API.G_EXC_ERROR;
1598 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1599 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1600 END IF;
1601
1602 PVX_UTILITY_PVT.get_lookup_meaning
1603 (
1604 p_lookup_type => 'PV_MEMB_STATUS_REASON_CODE'
1605 , p_lookup_code => p_status_reason_code
1606 , x_return_status => x_return_status
1607 , x_meaning => l_meaning
1608 );
1609 l_param_tbl_var(1).param_name := 'PROGRAM_NAME';
1610 l_param_tbl_var(1).param_value := l_program_name;
1611
1612 l_param_tbl_var(2).param_name := 'STATUS_REASON_CODE';
1613 l_param_tbl_var(2).param_value := l_meaning ;
1614
1615 PVX_UTILITY_PVT.create_history_log
1616 (
1617 p_arc_history_for_entity_code => 'ENRQ'
1618 , p_history_for_entity_id => l_enrl_request_id
1619 , p_history_category_code => 'ENROLLMENT'
1620 , p_message_code => 'PV_ENRL_CANCELLED'
1621 , p_comments => p_comments
1622 , p_partner_id => l_partner_id
1623 , p_access_level_flag => 'P'
1624 , p_interaction_level => PVX_Utility_PVT.G_INTERACTION_LEVEL_10
1625 , p_log_params_tbl => l_param_tbl_var
1626 , p_init_msg_list => FND_API.g_false
1627 , p_commit => FND_API.G_FALSE
1628 , x_return_status => x_return_status
1629 , x_msg_count => x_msg_count
1630 , x_msg_data => x_msg_data
1631 );
1632 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1633 RAISE FND_API.G_EXC_ERROR;
1634 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1635 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1636 END IF;
1637
1638 PVX_UTILITY_PVT.create_history_log
1639 (
1640 p_arc_history_for_entity_code => 'MEMBERSHIP'
1641 , p_history_for_entity_id => l_enrl_request_id
1642 , p_history_category_code => 'ENROLLMENT'
1643 , p_message_code => 'PV_ENRL_CANCELLED'
1644 , p_comments => p_comments
1645 , p_partner_id => l_partner_id
1646 , p_access_level_flag => 'P'
1647 , p_interaction_level => PVX_Utility_PVT.G_INTERACTION_LEVEL_50
1648 , p_log_params_tbl => l_param_tbl_var
1649 , p_init_msg_list => FND_API.g_false
1650 , p_commit => FND_API.G_FALSE
1651 , x_return_status => x_return_status
1652 , x_msg_count => x_msg_count
1653 , x_msg_data => x_msg_data
1654 );
1655 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1656 RAISE FND_API.G_EXC_ERROR;
1657 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1658 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1659 END IF;
1660 END LOOP;
1661 END IF; -- end of if if atleast one membership id exists in the passed in table
1662
1663 END cancel_all_enrollments;
1664
1665 ------------------------------------------------------------
1666 -- PROCEDURE
1667 -- term_all_memberships
1668 --
1669 -- PURPOSE
1670 -- Terminate one membership given the membership_id. This is also a private procedure
1671 -- this procedure terminates the contract as well as send terminate notifictaion
1672 -- and also log into history
1673 -- but does not terminate deafault program membeship unless the partnership is terminated
1674 -- also terminate pre-reqs if any
1675 -- IN
1676 -- partner_id NUMBER
1677 --
1678 -- USED BY
1679 --
1680 --
1681 -- HISTORY
1682 -- pukken CREATION
1683 --------------------------------------------------------------------------
1684
1685 PROCEDURE term_all_memberships
1686 (
1687 p_membership_table IN JTF_NUMBER_TABLE
1688 , p_event_code IN VARCHAR2
1689 , p_status_reason_code IN VARCHAR2
1690 , p_message_code IN VARCHAR2
1691 , p_comments IN VARCHAR2 DEFAULT NULL
1692 , x_return_status OUT NOCOPY VARCHAR2
1693 , x_msg_count OUT NOCOPY NUMBER
1694 , x_msg_data OUT NOCOPY VARCHAR2
1695
1696 ) IS
1697
1698 CURSOR pstatus_cur(p_ptr_id NUMBER) IS
1699 SELECT status
1700 FROM pv_partner_profiles
1701 WHERE partner_id=p_ptr_id;
1702
1703 CURSOR contract_cur(mmbr_id NUMBER) IS
1704 SELECT contract_id
1705 FROM pv_pg_enrl_requests enrq, pv_pg_memberships memb
1706 WHERE memb.membership_id=mmbr_id
1707 AND memb.enrl_request_id=enrq.enrl_request_id;
1708
1709 CURSOR memb_cur (mmbr_id NUMBER ) IS
1710 SELECT memb.partner_id
1711 , memb.program_id
1712 , memb.object_version_number
1713 , memb.enrl_request_id
1714 , prgm.program_name
1715 FROM pv_pg_memberships memb
1716 , pv_partner_program_vl prgm
1717 WHERE memb.membership_id=mmbr_id
1718 AND memb.program_id=prgm.program_id;
1719
1720
1721 l_isterminatable boolean:=true;
1722 l_object_version_number NUMBER;
1723 l_defult_program_id NUMBER;
1724 l_partner_id NUMBER;
1725 l_temp_partner_id NUMBER;
1726 l_program_id NUMBER;
1727 l_enrl_request_id NUMBER;
1728 l_program_name VARCHAR2(80);
1729 l_relationship VARCHAR2(60);
1730 l_meaning VARCHAR2(80);
1731 l_pv_pg_memb_rec memb_rec_type;
1732 l_notif_event_code VARCHAR2(30);
1733 l_param_tbl_var PVX_UTILITY_PVT.log_params_tbl_type;
1734 BEGIN
1735
1736 -- get the partner status from partner_profiles table
1737 -- if partner _relationship is terminated then terminate the default membership also.
1738 --get the program_id of the membership we are terminateing l_program_id
1739 -- if the progr
1740 -- update membership record and call responsiblity management
1741 -- set the membership record to be updated
1742 x_return_status := FND_API.g_ret_sts_success;
1743 IF p_membership_table.exists(1) THEN
1744 FOR i in 1.. p_membership_table.count() LOOP
1745
1746 OPEN memb_cur(p_membership_table(i));
1747 FETCH memb_cur into l_partner_id,l_program_id,l_object_version_number,l_enrl_request_id,l_program_name;
1748 CLOSE memb_cur;
1749 -- update the memberships table
1750 l_pv_pg_memb_rec.membership_id := p_membership_table(i);
1751 l_pv_pg_memb_rec.actual_end_date := sysdate;
1752 l_pv_pg_memb_rec.membership_status_code := p_event_code;
1753 l_pv_pg_memb_rec.status_reason_code := p_status_reason_code;
1754 l_pv_pg_memb_rec.object_version_number:= l_object_version_number;
1755 Update_Pg_Memberships
1756 ( p_api_version_number => 1.0
1757 , p_init_msg_list => Fnd_Api.g_false
1758 , p_commit => Fnd_Api.g_false
1759 , x_return_status => x_return_status
1760 , x_msg_count => x_msg_count
1761 , x_msg_data => x_msg_data
1762 , p_memb_rec => l_pv_pg_memb_rec
1763 );
1764 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1765 RAISE FND_API.G_EXC_ERROR;
1766 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1767 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1768 END IF;
1769
1770 IF p_event_code <> 'DOWNGRADED' THEN
1771
1772 -- call responsibility management api
1773 Pv_User_Resp_Pvt.manage_memb_resp
1774 ( p_api_version_number => 1.0
1775 , p_init_msg_list => Fnd_Api.g_false
1776 , p_commit => Fnd_Api.g_false
1777 , p_membership_id => p_membership_table(i)
1778 , x_return_status => x_return_status
1779 , x_msg_count => x_msg_count
1780 , x_msg_data => x_msg_data
1781 );
1782
1783 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1784 RAISE FND_API.G_EXC_ERROR;
1785 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1786 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1787 END IF;
1788
1789 END IF;
1790
1791 -- write to the logs and set the message tokens
1792 l_param_tbl_var(1).param_name := 'PROGRAM_NAME';
1793 l_param_tbl_var(1).param_value := l_program_name;
1794
1795 PVX_UTILITY_PVT.get_lookup_meaning
1796 (
1797 p_lookup_type => 'PV_MEMBERSHIP_STATUS'
1798 , p_lookup_code => p_event_code
1799 , x_return_status => x_return_status
1800 , x_meaning => l_meaning
1801 );
1802
1803 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1804 RAISE FND_API.G_EXC_ERROR;
1805 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1806 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1807 END IF;
1808 l_param_tbl_var(2).param_name := 'EVENT';
1809 l_param_tbl_var(2).param_value := l_meaning;
1810 l_meaning:=null;
1811
1812 PVX_UTILITY_PVT.get_lookup_meaning
1813 (
1814 p_lookup_type => 'PV_MEMB_STATUS_REASON_CODE'
1815 , p_lookup_code => p_status_reason_code
1816 , x_return_status => x_return_status
1817 , x_meaning => l_meaning
1818 );
1819
1820
1821 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1822 RAISE FND_API.G_EXC_ERROR;
1823 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1824 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1825 END IF;
1826
1827 l_param_tbl_var(3).param_name := 'STATUS_REASON_CODE';
1828 l_param_tbl_var(3).param_value := l_meaning;
1829 PVX_UTILITY_PVT.create_history_log
1830 (
1831 p_arc_history_for_entity_code => 'ENRQ'
1832 , p_history_for_entity_id => l_enrl_request_id
1833 , p_history_category_code => 'ENROLLMENT'
1834 , p_message_code => 'PV_MEMBERSHIP_STATUS_CHANGE'
1835 , p_comments => p_comments
1836 , p_partner_id => l_partner_id
1837 , p_access_level_flag => 'P'
1838 , p_interaction_level => PVX_Utility_PVT.G_INTERACTION_LEVEL_10
1839 , p_log_params_tbl => l_param_tbl_var
1840 , p_init_msg_list => FND_API.g_false
1841 , p_commit => FND_API.G_FALSE
1842 , x_return_status => x_return_status
1843 , x_msg_count => x_msg_count
1844 , x_msg_data => x_msg_data
1845 );
1846
1847 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1848 RAISE FND_API.G_EXC_ERROR;
1849 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1850 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1851 END IF;
1852 PVX_UTILITY_PVT.create_history_log
1853 (
1854 p_arc_history_for_entity_code => 'MEMBERSHIP'
1855 , p_history_for_entity_id => l_enrl_request_id
1856 , p_history_category_code => 'ENROLLMENT'
1857 , p_message_code => 'PV_MEMBERSHIP_STATUS_CHANGE'
1858 , p_comments => p_comments
1859 , p_partner_id => l_partner_id
1860 , p_access_level_flag => 'P'
1861 , p_interaction_level => PVX_Utility_PVT.G_INTERACTION_LEVEL_50
1862 , p_log_params_tbl => l_param_tbl_var
1863 , p_init_msg_list => FND_API.g_false
1864 , p_commit => FND_API.G_FALSE
1865 , x_return_status => x_return_status
1866 , x_msg_count => x_msg_count
1867 , x_msg_data => x_msg_data
1868 );
1869
1870 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1871 RAISE FND_API.G_EXC_ERROR;
1872 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1873 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1874 END IF;
1875
1876 IF p_status_reason_code = 'TERMINATED_BY_GLOBAL' THEN
1877 l_notif_event_code := 'GLOBAL_TERMINATE_SUBSIDIARY';
1878 ELSE
1879 l_notif_event_code := 'PG_TERMINATE';
1880 END IF;
1881 -- we do not want to send notification from here if membership is downgraded
1882 -- it will be send from the downgrade_membership api.
1883 IF p_event_code <> 'DOWNGRADED' THEN
1884
1885 PV_PG_NOTIF_UTILITY_PVT.Send_Workflow_Notification
1886 (
1887 p_api_version_number => 1.0
1888 , p_init_msg_list => Fnd_Api.g_false
1889 , p_commit => Fnd_Api.g_false
1890 , p_validation_level => FND_API.g_valid_level_full
1891 , p_context_id => l_partner_id
1892 , p_context_code => p_event_code
1893 , p_target_ctgry => 'PARTNER'
1894 , p_target_ctgry_pt_id => l_partner_id
1895 , p_notif_event_code => l_notif_event_code
1896 , p_entity_id => l_enrl_request_id
1897 , p_entity_code => 'ENRQ'
1898 , p_wait_time => 0
1899 , x_return_status => x_return_status
1900 , x_msg_count => x_msg_count
1901 , x_msg_data => x_msg_data
1902 );
1903 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1904 RAISE FND_API.G_EXC_ERROR;
1905 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1906 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1907 END IF;
1908 END IF;
1909 END LOOP;
1910 END IF; -- end of if if atleast one membership id exists in the passed in table
1911 END term_all_memberships;
1912
1913
1914
1915
1916 --------------------------------------------------------------------------
1917 -- PROCEDURE
1918 -- PV_PG_MEMBERSHIPS_PVT.Terminate_ptr_memberships
1919 --
1920 -- PURPOSE
1921 -- Terminate all memberships for a given partner. If the partner is
1922 -- a global partner, terminate its appropraite subsidiary memberships also
1923 -- IN
1924 -- partner_id NUMBER
1925 --
1926 -- USED BY
1927 -- called from change membership type api and can also be called independently
1928 -- to terminate all partner memberships.
1929 --
1930 -- HISTORY
1931 -- pukken CREATION
1932 --------------------------------------------------------------------------
1933
1934 PROCEDURE Terminate_ptr_memberships
1935 (
1936 p_api_version_number IN NUMBER
1937 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
1938 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
1939 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
1940 ,p_partner_id IN NUMBER --partner id for which all memberships need to be terminated
1941 ,p_memb_type IN VARCHAR -- if not given, will get from profile, should be 'SUBSIDIARY','GLOBAL','STANDARD'
1942 ,p_status_reason_code IN VARCHAR2 -- pass 'MEMBER_TYPE_CHANGE' if it is happening because of member type change -- it validates against PV_MEMB_STATUS_REASON_CODE
1943 ,p_comments IN VARCHAR2 DEFAULT NULL -- pass 'Membership terminated by system as member type is changed' if it is changed because of member type change
1944 ,x_return_status OUT NOCOPY VARCHAR2
1945 ,x_msg_count OUT NOCOPY NUMBER
1946 ,x_msg_data OUT NOCOPY VARCHAR2
1947 ) IS
1948
1949
1950 CURSOR memb_csr( p_ptr_id NUMBER) IS
1951 SELECT membership_id,program_id
1952 FROM pv_pg_memberships
1953 WHERE partner_id=p_ptr_id
1954 AND membership_status_code IN ('ACTIVE','FUTURE');
1955
1956 CURSOR enrq_csr( p_ptr_id NUMBER) IS
1957 SELECT enrl_request_id
1958 FROM pv_pg_enrl_requests
1959 WHERE partner_id=p_ptr_id
1960 AND request_status_code in ('INCOMPLETE','AWAITING_APPROVAL');
1961
1962
1963 CURSOR memb_type_csr(ptr_id NUMBER) IS
1964 SELECT enty.attr_value
1965 FROM pv_enty_attr_values enty
1966 WHERE enty.entity = 'PARTNER'
1967 AND enty.entity_id = ptr_id
1968 AND enty.attribute_id = 6
1969 AND enty.latest_flag = 'Y';
1970
1971 -- fix this SQL 12266991
1972 -- Fix this SQL 12267007
1973 CURSOR c_get_subs_csr (g_ptr_id NUMBER) IS
1974 SELECT subs_prof.partner_id
1975 FROM pv_partner_profiles subs_prof
1976 , pv_partner_profiles global_prof
1977 , pv_enty_attr_values subs_enty_val
1978 , hz_relationships rel
1979 WHERE global_prof.partner_id = g_ptr_id
1980 AND global_prof.partner_party_id = rel.subject_id
1981 AND rel.relationship_type = 'PARTNER_HIERARCHY'
1982 AND rel.relationship_code = 'PARENT_OF'
1983 AND rel.status = 'A'
1984 AND NVL(rel.start_date, SYSDATE) <= SYSDATE
1985 AND NVL(rel.end_date, SYSDATE) >= SYSDATE
1986 AND rel.object_id = subs_prof.partner_party_id
1987 AND subs_enty_val.entity_id = subs_prof.partner_id
1988 AND subs_enty_val.entity = 'PARTNER'
1989 AND subs_enty_val.attribute_id = 6
1990 AND subs_enty_val.latest_flag = 'Y'
1991 AND subs_enty_val.attr_value = 'SUBSIDIARY';
1992
1993 CURSOR c_get_membs_csr(l_sub_str_table JTF_NUMBER_TABLE ) IS
1994 SELECT /*+ CARDINALITY(sptr 10) */
1995 memb.membership_id membership_id
1996 , memb.program_id program_id
1997 FROM pv_pg_memberships memb
1998 , (SELECT column_value FROM TABLE (CAST(l_sub_str_table AS JTF_NUMBER_TABLE))) sptr
1999 WHERE memb.partner_id=sptr.column_value
2000 AND memb.membership_status_code IN ('ACTIVE','FUTURE');
2001
2002
2003 CURSOR c_get_enrls_csr(l_sub_str_table JTF_NUMBER_TABLE ) IS
2004 SELECT /*+ CARDINALITY(sptr 10) */ enrq.enrl_request_id enrl_request_id
2005 FROM pv_pg_enrl_requests enrq
2006 , (SELECT column_value FROM TABLE (CAST(l_sub_str_table AS JTF_NUMBER_TABLE))) sptr
2007 WHERE enrq.partner_id=sptr.column_value
2008 AND request_status_code IN ('INCOMPLETE','AWAITING_APPROVAL');
2009
2010 l_api_name CONSTANT VARCHAR2(30) := 'Terminate_ptr_memberships ';
2011 l_api_version_number CONSTANT NUMBER := 1.0;
2012 l_exists VARCHAR2(1);
2013 l_lookup_type VARCHAR2(30);
2014 l_memb_id_tbl JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
2015 l_enrl_id_tbl JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
2016 l_member_type VARCHAR2(30);
2017 counter NUMBER := 1;
2018 l_default_program_id NUMBER;
2019 l_subs_tbl JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
2020
2021 BEGIN
2022 /**
2023 1. get all the membership_id from memberships table that are ACTIVE, FUTURE and populate
2024 them in l_l_memberships_id table Call terminate_membership
2025 2. also if membtype is global, get all the subsidiaryy partners and
2026 loop and get the program mmberships for each subsidiary partner
2027 and add them to thel_memberships_id's table
2028 call terminate_membership
2029 3. check wheher any of those other programs has pendingenrollment, cancel them and cancel any associated orders
2030 4. end date all the invitaions for the subsidiary partner
2031
2032 */
2033
2034 -- Standard Start of API savepoint
2035 SAVEPOINT Terminate_ptr_memberships;
2036
2037 -- Standard call to check for call compatibility.
2038 IF NOT FND_API.Compatible_API_Call
2039 ( l_api_version_number
2040 ,p_api_version_number
2041 ,l_api_name
2042 ,G_PKG_NAME
2043 )
2044 THEN
2045 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2046 END IF;
2047
2048 -- Initialize message list if p_init_msg_list is set to TRUE.
2049 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2050 FND_MSG_PUB.initialize;
2051 END IF;
2052
2053 -- Debug Message
2054 IF (PV_DEBUG_HIGH_ON) THEN
2055 PVX_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
2056 END IF;
2057
2058
2059 -- Initialize API return status to SUCCESS
2060 x_return_status := FND_API.G_RET_STS_SUCCESS;
2061
2062
2063 -- Validate Environment
2064 IF FND_GLOBAL.USER_ID IS NULL THEN
2065 PVX_UTILITY_PVT.Error_Message(p_message_name => 'USER_PROFILE_MISSING');
2066 RAISE FND_API.G_EXC_ERROR;
2067 END IF;
2068
2069 -- validate partner id
2070 IF p_partner_id IS NULL THEN
2071 PVX_UTILITY_PVT.Error_Message(p_message_name => 'PV_PARTNER_ID_MISSING'); -- seed this message
2072 RAISE FND_API.G_EXC_ERROR;
2073 END IF;
2074
2075 --validate status reason code
2076 l_default_program_id := isnumber(FND_PROFILE.VALUE('PV_PARTNER_DEFAULT_PROGRAM'));
2077 -- get all the membership to be terminated into a table of memberships
2078
2079 FOR membs in memb_csr( p_partner_id ) LOOP
2080 IF p_status_reason_code= 'MEMBER_TYPE_CHANGE' THEN
2081 IF ( l_default_program_id IS NULL OR l_default_program_id <> membs.program_id ) THEN
2082
2083 l_memb_id_tbl.extend(1);
2084 l_memb_id_tbl(counter) := membs.membership_id;
2085 counter := counter+1;
2086 END IF;
2087 ELSE
2088 l_memb_id_tbl.extend(1);
2089 l_memb_id_tbl(counter) := membs.membership_id;
2090 counter := counter+1;
2091 END IF;
2092
2093 END LOOP;
2094
2095 IF l_memb_id_tbl.exists(1) THEN
2096
2097 term_all_memberships
2098 (
2099 p_membership_table => l_memb_id_tbl
2100 , p_event_code => 'TERMINATED'
2101 , p_status_reason_code => p_status_reason_code
2102 , p_message_code => 'PV_TERMINATE_ALL_PRGM_MEMB'
2103 , p_comments => p_comments
2104 , x_return_status => x_return_status
2105 , x_msg_count => x_msg_count
2106 , x_msg_data => x_msg_data
2107
2108 );
2109
2110 --write to the logs
2111 END IF;
2112
2113 IF x_return_status = FND_API.g_ret_sts_error THEN
2114 RAISE FND_API.g_exc_error;
2115 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
2116 RAISE FND_API.g_exc_unexpected_error;
2117 END IF;
2118
2119 OPEN enrq_csr(p_partner_id);
2120 FETCH enrq_csr BULK COLLECT INTO l_enrl_id_tbl;
2121 CLOSE enrq_csr;
2122
2123 IF l_enrl_id_tbl.exists(1) THEN
2124
2125 cancel_all_enrollments
2126 (
2127 p_enrollment_id_tbl => l_enrl_id_tbl
2128 ,p_status_reason_code => p_status_reason_code
2129 ,p_comments => p_comments
2130 ,x_return_status => x_return_status
2131 ,x_msg_count => x_msg_count
2132 ,x_msg_data => x_msg_data
2133 );
2134 IF x_return_status = FND_API.g_ret_sts_error THEN
2135 RAISE FND_API.g_exc_error;
2136 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
2137 RAISE FND_API.g_exc_unexpected_error;
2138 END IF;
2139
2140 END IF;
2141
2142
2143
2144 l_enrl_id_tbl.delete();
2145 l_memb_id_tbl.delete();
2146 counter :=1;
2147
2148 l_member_type:=p_memb_type;
2149 IF l_member_type IS NULL THEN
2150 OPEN memb_type_csr(p_partner_id);
2151 FETCH memb_type_csr INTO l_member_type;
2152 CLOSE memb_type_csr;
2153 END IF;
2154
2155 IF l_member_type='GLOBAL' THEN
2156 ---write a query to get the membership id's of alll subsidiary partners.
2157 ---and populate them to l_memb_id_tbl
2158
2159 OPEN c_get_subs_csr (p_partner_id);
2160 FETCH c_get_subs_csr BULK COLLECT INTO l_subs_tbl ;
2161 CLOSE c_get_subs_csr;
2162 FOR mes in c_get_membs_csr( l_subs_tbl ) LOOP
2163
2164
2165 IF p_status_reason_code= 'MEMBER_TYPE_CHANGE' THEN
2166
2167
2168 IF ( l_default_program_id IS NULL OR l_default_program_id <> mes.program_id ) THEN
2169 l_memb_id_tbl.extend(1);
2170 l_memb_id_tbl(counter) := mes.membership_id;
2171 counter := counter+1;
2172 END IF;
2173 ELSE
2174 l_memb_id_tbl.extend(1);
2175 l_memb_id_tbl(counter) := mes.membership_id;
2176 counter := counter+1;
2177 END IF;
2178
2179 END LOOP;
2180
2181
2182 IF l_memb_id_tbl.exists(1) THEN
2183
2184 term_all_memberships
2185 (
2186 p_membership_table => l_memb_id_tbl
2187 , p_event_code => 'TERMINATED'
2188 , p_status_reason_code => 'GLOBAL_MEMBER_CHANGED'
2189 , p_message_code => 'PV_TERMINATE_ALL_SUBS_MEMB'
2190 , p_comments => p_comments
2191 , x_return_status => x_return_status
2192 , x_msg_count => x_msg_count
2193 , x_msg_data => x_msg_data
2194
2195 );
2196
2197 IF x_return_status = FND_API.g_ret_sts_error THEN
2198 RAISE FND_API.g_exc_error;
2199 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
2200 RAISE FND_API.g_exc_unexpected_error;
2201 END IF;
2202
2203 --write to the logs
2204 END IF;
2205
2206 OPEN c_get_enrls_csr(l_subs_tbl);
2207 FETCH c_get_enrls_csr BULK COLLECT INTO l_enrl_id_tbl;
2208 CLOSE c_get_enrls_csr;
2209
2210 IF l_enrl_id_tbl.exists(1) THEN
2211
2212 cancel_all_enrollments
2213 (
2214 p_enrollment_id_tbl => l_enrl_id_tbl
2215 ,p_status_reason_code => p_status_reason_code
2216 ,p_comments => p_comments
2217 ,x_return_status => x_return_status
2218 ,x_msg_count => x_msg_count
2219 ,x_msg_data => x_msg_data
2220 );
2221 IF x_return_status = FND_API.g_ret_sts_error THEN
2222 RAISE FND_API.g_exc_error;
2223 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
2224 RAISE FND_API.g_exc_unexpected_error;
2225 END IF;
2226
2227 END IF;
2228
2229 END IF;
2230
2231
2232
2233
2234 -- Standard call to get message count and if count is 1, get message info.
2235 FND_MSG_PUB.Count_And_Get
2236 (p_count => x_msg_count,
2237 p_data => x_msg_data
2238 );
2239
2240 -- Debug Message
2241
2242 -- Debug Message
2243 IF (PV_DEBUG_HIGH_ON) THEN
2244 PVX_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
2245 END IF;
2246
2247 -- Standard check for p_commit
2248 IF FND_API.to_Boolean( p_commit ) THEN
2249 COMMIT WORK;
2250 END IF;
2251
2252 EXCEPTION
2253 WHEN FND_API.G_EXC_ERROR THEN
2254 ROLLBACK TO Terminate_ptr_memberships;
2255 x_return_status := FND_API.G_RET_STS_ERROR;
2256 -- Standard call to get message count and if count=1, get the message
2257 FND_MSG_PUB.Count_And_Get
2258 ( p_encoded => FND_API.G_FALSE
2259 ,p_count => x_msg_count
2260 ,p_data => x_msg_data
2261 );
2262
2263 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2264 ROLLBACK TO Terminate_ptr_memberships;
2265 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2266 -- Standard call to get message count and if count=1, get the message
2267 FND_MSG_PUB.Count_And_Get
2268 ( p_encoded => FND_API.G_FALSE
2269 ,p_count => x_msg_count
2270 ,p_data => x_msg_data
2271 );
2272
2273 WHEN OTHERS THEN
2274 ROLLBACK TO Terminate_ptr_memberships;
2275 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2276 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2277 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
2278 END IF;
2279 FND_MSG_PUB.Count_And_Get
2280 ( p_encoded => FND_API.G_FALSE
2281 ,p_count => x_msg_count
2282 ,p_data => x_msg_data
2283 );
2284 END Terminate_ptr_memberships;
2285
2286
2287 FUNCTION getUniqueIDs(
2288 p_ids IN JTF_NUMBER_TABLE
2289 )
2290 RETURN JTF_NUMBER_TABLE IS
2291
2292 l_unique_id_tbl JTF_NUMBER_TABLE:=JTF_NUMBER_TABLE();
2293 counter NUMBER:=1;
2294
2295 BEGIN
2296 FOR x IN (
2297 SELECT * FROM TABLE (CAST(p_ids AS JTF_NUMBER_TABLE))
2298 GROUP BY column_value )
2299 LOOP
2300
2301 l_unique_id_tbl.extend(1);
2302 l_unique_id_tbl(counter):=x.column_value;
2303 counter:=counter+1;
2304
2305 END LOOP;
2306 RETURN l_unique_id_tbl;
2307
2308 END getUniqueIDs;
2309
2310
2311 -- this function gives all the programs in the pre-req hierarchy starting from bottom to top
2312 -- So if we have a hieararchy o3 programs A-> B->C, this function will return you
2313 -- B and C , if you pass in A. If you pass B, then the function will return you C and so on.
2314 PROCEDURE get_prereq_programs
2315 (
2316 p_program_id IN NUMBER
2317 ,l_prereq_program_ids IN OUT NOCOPY JTF_NUMBER_TABLE
2318
2319 )
2320 IS
2321 CURSOR prereq_csr(p_prgm_id NUMBER) IS
2322 SELECT DISTINCT(change_to_program_id)
2323 FROM pv_pg_enrl_change_rules
2324 WHERE change_direction_code='PREREQUISITE'
2325 AND ACTIVE_FLAG='Y'
2326 START WITH change_from_program_id=p_prgm_id
2327 CONNECT BY change_from_program_id=PRIOR change_to_program_id
2328 AND PRIOR CHANGE_TO_PROGRAM_ID<>CHANGE_FROM_PROGRAM_ID;
2329
2330 BEGIN
2331
2332 OPEN prereq_csr(p_program_id);
2333 FETCH prereq_csr BULK COLLECT INTO l_prereq_program_ids;
2334 CLOSE prereq_csr;
2335
2336
2337 EXCEPTION
2338
2339 WHEN OTHERS THEN
2340 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2341 END get_prereq_programs;
2342
2343
2344 FUNCTION get_dependent_program_id
2345 (
2346
2347 p_membership_id IN NUMBER
2348
2349 ) RETURN JTF_NUMBER_TABLE IS
2350 l_dependent_program_id_tbl JTF_NUMBER_TABLE:=JTF_NUMBER_TABLE();
2351 l_highest_level NUMBER;
2352 l_program_id NUMBER;
2353 counter NUMBER:=1;
2354
2355
2356 CURSOR mmbr_transitions_csr(to_mmbr_id NUMBER) IS
2357 SELECT memb.program_id program_id,trn.lvl actlevel
2358 FROM pv_pg_memberships memb
2359 ,pv_partner_program_b pvpp,
2360 (
2361
2362 SELECT from_membership_id,min(level) lvl
2363 FROM pv_pg_mmbr_transitions
2364 START WITH to_membership_id=to_mmbr_id
2365 CONNECT BY to_membership_id=prior from_membership_id
2366 GROUP BY from_membership_id,level
2367 ) trn
2368 WHERE GLOBAL_MMBR_REQD_FLAG = 'Y'
2369 AND pvpp.program_id=memb.program_id
2370 AND memb.membership_id=trn.from_membership_id
2371 ORDER by actlevel;
2372
2373 CURSOR prg_csr ( to_mmbr_id NUMBER ) IS
2374 SELECT memb.program_id
2375 FROM pv_pg_memberships memb
2376 ,pv_partner_program_b pvpp
2377 WHERE GLOBAL_MMBR_REQD_FLAG = 'Y'
2378 AND pvpp.program_id=memb.program_id
2379 AND memb.membership_id=to_mmbr_id;
2380
2381 BEGIN
2382
2383 FOR mem_trans in mmbr_transitions_csr(p_membership_id) LOOP
2384
2385 IF l_highest_level IS NULL THEN
2386 --add to the greatest id tbl
2387 l_highest_level:=mem_trans.actlevel;
2388 l_dependent_program_id_tbl.extend(1);
2389 l_dependent_program_id_tbl(1):=mem_trans.program_id;
2390 ELSE
2391 IF l_highest_level<mem_trans.actlevel THEN
2392
2393 --set highest level
2394 l_highest_level:=mem_trans.actlevel;
2395 l_dependent_program_id_tbl.delete();
2396 counter:=1;
2397 l_dependent_program_id_tbl.extend(1);
2398 l_dependent_program_id_tbl(1):=mem_trans.program_id;
2399 ELSIF l_highest_level= mem_trans.actlevel THEN
2400 l_dependent_program_id_tbl.extend(1);
2401 counter:=counter+1;
2402 l_dependent_program_id_tbl(counter):=mem_trans.program_id;
2403 END IF;
2404 END IF;
2405 END LOOP;
2406
2407 /*we are not inserting data into member transitions table when its a new enrollment request
2408 because there is no from membership.
2409 so to find the dependent program , just query for the GLOBAL_MMBR_REQD_FLAG for the program
2410 of the terminating membership
2411 */
2412
2413 OPEN prg_csr ( p_membership_id ) ;
2414 FETCH prg_csr INTO l_program_id;
2415 CLOSE prg_csr;
2416 IF l_program_id is NOT NULL THEN
2417 l_dependent_program_id_tbl.extend(1);
2418 l_dependent_program_id_tbl(1):=l_program_id;
2419 END IF;
2420
2421
2422 RETURN l_dependent_program_id_tbl;
2423
2424 EXCEPTION
2425
2426 WHEN OTHERS THEN
2427 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2428
2429 END get_dependent_program_id;
2430
2431
2432
2433 --------------------------------------------------------------------------
2434 -- PROCEDURE
2435 -- PV_PG_MEMBERSHIPS_PVT.Terminate__membership
2436 --
2437 -- PURPOSE
2438 -- Terminate a membership for a given partner. If the partner is
2439 -- a global partner, terminate its appropraite subsidiary memberships also
2440 -- IN
2441 -- membership_id IN NUMBER
2442 -- membership_id from memberships table
2443 -- p_event_code IN VARCHAR2
2444 -- validated against the lookup PV_MEMBERSHIP_STATUS
2445 -- p_memb_type IN VARCHAR
2446 -- if not given, will get from profile, should be 'SUBSIDIARY','GLOBAL','STANDARD'
2447 -- p_status_reason_code IN VARCHAR2
2448 -- validates against PV_MEMB_STATUS_REASON_CODE
2449 -- USED BY
2450 -- this api is called when you want to terminate,expire or downgrade a single program membership
2451 -- HISTORY
2452 -- pukken CREATION
2453 --------------------------------------------------------------------------
2454
2455 PROCEDURE Terminate_membership
2456 (
2457 p_api_version_number IN NUMBER
2458 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
2459 , p_commit IN VARCHAR2 := FND_API.G_FALSE
2460 , p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
2461 , p_membership_id IN NUMBER
2462 , p_event_code IN VARCHAR2
2463 , p_memb_type IN VARCHAR
2464 , p_status_reason_code IN VARCHAR2
2465 , p_comments IN VARCHAR2 DEFAULT NULL
2466 , x_return_status OUT NOCOPY VARCHAR2
2467 , x_msg_count OUT NOCOPY NUMBER
2468 , x_msg_data OUT NOCOPY VARCHAR2
2469 ) IS
2470
2471 CURSOR enrl_csr( ptr_id NUMBER, prgm_id_tbl JTF_NUMBER_TABLE ) IS
2472 SELECT enr.enrl_request_id
2473 , enr.request_status_code
2474 , memb.membership_status_code
2475 , memb.membership_id
2476 FROM pv_pg_enrl_requests enr
2477 , pv_pg_memberships memb
2478 WHERE enr.partner_id = ptr_id
2479 AND enr.program_id
2480 IN ( SELECT * FROM TABLE ( CAST( prgm_id_tbl AS JTF_NUMBER_TABLE ) ) )
2481 AND enr.enrl_request_id = memb.enrl_request_id(+);
2482
2483
2484 CURSOR enrl_sub_csr( ptr_id_tbl JTF_NUMBER_TABLE, prgm_id_tbl JTF_NUMBER_TABLE ) IS
2485 SELECT /*+ CARDINALITY(ptr 10) */
2486 enr.enrl_request_id
2487 , enr.request_status_code
2488 , memb.membership_status_code
2489 , memb.membership_id
2490 FROM pv_pg_enrl_requests enr
2491 , pv_pg_memberships memb
2492 , ( SELECT column_value FROM TABLE ( CAST( ptr_id_tbl AS JTF_NUMBER_TABLE ) ) ) ptr
2493 , ( SELECT column_value FROM TABLE ( CAST( prgm_id_tbl AS JTF_NUMBER_TABLE ) ) ) prg
2494 WHERE enr.partner_id =ptr.column_value
2495 AND enr.program_id =prg.column_value
2496 AND enr.enrl_request_id = memb.enrl_request_id(+);
2497 /*
2498 -- added new SQL above to fix this SQL reported in 11.5.10 CU1 in sql repositiry 12267124
2499 SELECT enr.enrl_request_id
2500 , enr.request_status_code
2501 , memb.membership_status_code
2502 , memb.membership_id
2503 FROM pv_pg_enrl_requests enr
2504 , pv_pg_memberships memb
2505 WHERE enr.partner_id
2506 IN ( SELECT * FROM TABLE ( CAST( ptr_id_tbl AS JTF_NUMBER_TABLE ) ) )
2507 AND enr.program_id
2508 IN ( SELECT * FROM TABLE ( CAST( prgm_id_tbl AS JTF_NUMBER_TABLE ) ) )
2509 AND enr.enrl_request_id = memb.enrl_request_id(+);
2510 */
2511
2512 CURSOR subsidiary_csr( global_partner_id NUMBER, p_depentent_id_tbl JTF_NUMBER_TABLE ) IS
2513 SELECT enrq.enrl_request_id
2514 , enrq.request_status_code
2515 , memb.membership_status_code
2516 , memb.membership_id
2517 , memb.partner_id
2518 FROM pv_partner_profiles subs_prof
2519 , pv_partner_profiles global_prof
2520 , pv_enty_attr_values subs_enty_val
2521
2522 , hz_relationships rel
2523 , pv_pg_memberships memb
2524 , pv_pg_enrl_requests enrq
2525 WHERE global_prof.partner_id = global_partner_id
2526 AND global_prof.partner_party_id = rel.subject_id
2527 AND rel.relationship_type = 'PARTNER_HIERARCHY'
2528 AND rel.object_id = subs_prof.partner_party_id
2529 AND rel.relationship_code = 'PARENT_OF'
2530 AND rel.status = 'A'
2531 AND NVL(rel.start_date, SYSDATE) <= SYSDATE
2532 AND NVL(rel.end_date, SYSDATE) >= SYSDATE
2533 AND subs_enty_val.entity = 'PARTNER'
2534 AND subs_enty_val.entity_id = subs_prof.partner_id
2535 AND subs_enty_val.attribute_id = 6
2536 AND subs_enty_val.latest_flag = 'Y'
2537 AND subs_enty_val.attr_value = 'SUBSIDIARY'
2538 AND subs_prof.partner_id = enrq.partner_id
2539 AND enrq.enrl_request_id = memb.enrl_request_id(+)
2540 AND enrq.dependent_program_id
2541 IN ( SELECT * FROM TABLE ( CAST( p_depentent_id_tbl AS JTF_NUMBER_TABLE ) ) );
2542
2543 CURSOR prereq_sub_csr( memb_id_tbl JTF_NUMBER_TABLE ) IS
2544 SELECT /*+ LEADING(t) */ DISTINCT( program_id )
2545 FROM pv_pg_memberships memb
2546 , (SELECT column_value FROM TABLE (CAST(memb_id_tbl AS JTF_NUMBER_TABLE))) t
2547 WHERE t.column_value=memb.membership_id;
2548 /*
2549 -- added new SQL above to fix this SQL reported in 11.5.10 CU1 in sql repositiry 12267161
2550 SELECT DISTINCT( program_id )
2551 FROM pv_pg_memberships
2552 WHERE membership_id
2553 IN ( SELECT * FROM TABLE ( CAST( memb_id_tbl AS JTF_NUMBER_TABLE ) ) );
2554 */
2555 CURSOR memb_type_csr( memb_id NUMBER ) IS
2556 SELECT enty.attr_value
2557 , memb.program_id
2558 , memb.partner_id
2559 FROM pv_pg_memberships memb
2560 , pv_enty_attr_values enty
2561 WHERE memb.membership_id = memb_id
2562 AND memb.partner_id = enty.entity_id
2563 AND enty.entity = 'PARTNER'
2564 AND enty.entity_id = memb.partner_id
2565 AND enty.attribute_id = 6
2566 AND enty.latest_flag = 'Y';
2567
2568 CURSOR memb_csr ( memb_id NUMBER ) IS
2569 SELECT program_id
2570 , partner_id
2571 FROM pv_pg_memberships
2572 WHERE membership_id = memb_id;
2573
2574 l_isTerminatable boolean := true;
2575 l_program_id_tbl JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
2576 l_membid_tbl JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
2577 l_enrl_req_tbl JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
2578 l_prereq_sub_id_tbl JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
2579 l_all_prereq_prgm_tbl JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
2580 l_prereq_prgm_tbl JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
2581 l_all_depend_prgmids_tbl JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
2582 l_depend_prgm_ids_tbl JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
2583 l_sub_partner_id_tbl JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
2584
2585 l_member_type VARCHAR2(30);
2586 l_program_id NUMBER := NULL;
2587 l_partner_id NUMBER := NULL;
2588 mcounter NUMBER := 1;
2589 ecounter NUMBER := 1;
2590 dep_counter NUMBER := 1;
2591 subscounter NUMBER := 1;
2592 l_api_name CONSTANT VARCHAR2(30) := 'Terminate_membership';
2593 l_api_version_number CONSTANT NUMBER := 1.0;
2594 l_status_reason_code VARCHAR2(30);
2595 l_message_code VARCHAR2(30);
2596
2597 l_event_code VARCHAR2(30);
2598
2599 BEGIN
2600 -- Standard Start of API savepoint
2601 SAVEPOINT Terminate_membership ;
2602 -- Standard call to check for call compatibility.
2603 IF NOT FND_API.Compatible_API_Call
2604 ( l_api_version_number
2605 ,p_api_version_number
2606 ,l_api_name
2607 ,G_PKG_NAME
2608 )
2609 THEN
2610 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2611 END IF;
2612 -- Initialize message list if p_init_msg_list is set to TRUE.
2613 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2614 FND_MSG_PUB.initialize;
2615 END IF;
2616
2617 -- Debug Message
2618
2619 IF (PV_DEBUG_HIGH_ON) THEN
2620 PVX_UTILITY_PVT.debug_message( 'Private API: ' || l_api_name || 'start' );
2621 END IF;
2622
2623 -- Initialize API return status to SUCCESS
2624 x_return_status := FND_API.G_RET_STS_SUCCESS;
2625 -- Validate Environment
2626 IF FND_GLOBAL.USER_ID IS NULL THEN
2627 PVX_UTILITY_PVT.Error_Message(p_message_name => 'USER_PROFILE_MISSING');
2628 RAISE FND_API.G_EXC_ERROR;
2629 END IF;
2630 -- validate p_status_reason_code
2631 IF p_status_reason_code is NOT NULL THEN
2632 validate_Lookup
2633 (
2634 p_lookup_type => 'PV_MEMB_STATUS_REASON_CODE'
2635 ,p_lookup_code => p_status_reason_code
2636 ,x_return_status => x_return_status
2637 );
2638 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2639 RAISE FND_API.G_EXC_ERROR;
2640 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2641 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2642 END IF;
2643 END IF;
2644
2645 -- validate p_event_code
2646 validate_Lookup
2647 (
2648 p_lookup_type => 'PV_MEMBERSHIP_STATUS'
2649 ,p_lookup_code => p_event_code
2650 ,x_return_status => x_return_status
2651 );
2652 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2653 RAISE FND_API.G_EXC_ERROR;
2654 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2655 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2656 END IF;
2657
2658 -- VALIDATE the passed in member type value thats passed in
2659 -- find out the existing the member type if its not passed in.. If its passed , validate it
2660 IF p_memb_type is NULL THEN
2661 OPEN memb_type_csr( p_membership_id );
2662 FETCH memb_type_csr INTO l_member_type, l_program_id, l_partner_id;
2663 CLOSE memb_type_csr;
2664 ELSE
2665 --VALIDATE the passed in member type value thats passed in
2666 /*validate_Lookup
2667 (
2668 p_lookup_type => 'PV_MEMBER_TYPE_CODE'
2669 , p_lookup_code => p_memb_type
2670 , x_return_status => x_return_status
2671 );
2672 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2673 RAISE FND_API.G_EXC_ERROR;
2674 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2675 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2676 END IF;
2677 */
2678 validate_member_type
2679 (
2680 p_member_type => p_memb_type
2681 ,x_return_status => x_return_status
2682 );
2683 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2684 RAISE FND_API.G_EXC_ERROR;
2685 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2686 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2687 END IF;
2688 l_member_type := p_memb_type;
2689 END IF;
2690 -- need to validate status reason code also. need to evaluate whether we should do it here or in term_all_memberships
2691
2692 -- call term_all_memberships to terminate/expire this membership
2693 -- add the membership_id to l_memberships
2694
2695 l_membid_tbl.extend(1);
2696 l_membid_tbl(1):= p_membership_id;
2697
2698 IF p_event_code = 'TERMINATED' THEN
2699 l_message_code := 'PV_MEMBERSHIP_TERMINATED';
2700 ELSIF p_event_code = 'EXPIRED' THEN
2701 l_message_code := 'PV_MEMBERSHIP_EXPIRED';
2702 ELSIF p_event_code = 'DOWNGRADED' THEN
2703 l_message_code := 'PV_MEMBERSHIP_DOWNGRADED';
2704 END IF;
2705
2706
2707
2708 term_all_memberships
2709 (
2710 p_membership_table => l_membid_tbl
2711 , p_event_code => p_event_code
2712 , p_status_reason_code => p_status_reason_code
2713 , p_message_code => l_message_code
2714 , p_comments => p_comments
2715 , x_return_status => x_return_status
2716 , x_msg_count => x_msg_count
2717 , x_msg_data => x_msg_data
2718
2719 );
2720 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2721 RAISE FND_API.G_EXC_ERROR;
2722 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2723 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2724 END IF;
2725
2726 -- incase of downgrades there is a seperate api downgrade_membership which will call this api.
2727 -- so the term_all_memberships will put the membership status of the membership to downgraded
2728 -- if there are dependent memberships , those memberships needs to be terminated and it shouild
2729 -- not be downgraded and hence the if else below.
2730 IF p_event_code = 'DOWNGRADED' THEN
2731 l_event_code := 'TERMINATED';
2732 ELSE
2733 l_event_code := p_event_code;
2734 END IF;
2735 -- delete all values from the l_membid_tbl variable
2736 l_membid_tbl.delete();
2737 -- get the programid of the membership that got terminated. this is to terminate/expire any prepreq programs.
2738 IF l_program_id IS NULL THEN
2739 OPEN memb_csr( p_membership_id );
2740 FETCH memb_csr INTO l_program_id, l_partner_id;
2741 CLOSE memb_csr;
2742 END IF;
2743
2744 -- get the pre-reqs of the terminated/expired program that will return a table of program_ids.
2745
2746 get_prereq_programs( l_program_id, l_program_id_tbl );
2747
2748 IF l_program_id_tbl.exists(1) THEN
2749
2750
2751
2752 /**
2753 OPEN a cursor to get all enrollment requests (l_partner_id,l_program_id_tbl)
2754 loop through the cursor and cancel the enrollments if they are incomplete
2755 if not check wwhether they have active or future memberships and if yes, add
2756 the membership id to l_membid_tbl for termination
2757 call terminate_all_memberships with appropriate status reason code
2758 */
2759 FOR enrl in enrl_csr( l_partner_id,l_program_id_tbl ) LOOP
2760
2761 IF enrl.request_status_code IN ( 'INCOMPELTE','AWAITING_APPROVAL' ) THEN
2762
2763 -- add the enrollment request_id to l_enrl_req_tbl for cancellation
2764 l_enrl_req_tbl.extend(1);
2765 l_enrl_req_tbl(ecounter) := enrl.enrl_request_id;
2766 ecounter := ecounter+1;
2767 ELSE
2768 -- add to the l_membid_tbl for terminating the prepreqs
2769 IF enrl.membership_status_code IN ( 'ACTIVE','FUTURE' ) THEN
2770
2771 l_membid_tbl.extend(1);
2772 l_membid_tbl(mcounter) := enrl.membership_id;
2773 mcounter := mcounter+1;
2774 END IF;
2775 END IF;
2776 END LOOP;
2777 -- call the terminate api to terminate/expire all the memberships in the l_membid_tbl table
2778
2779 IF l_membid_tbl.exists(1) THEN
2780
2781 IF p_event_code = 'TERMINATED' THEN
2782 l_status_reason_code := 'PREREQ_MEMBERSHIP_TERMINATED';
2783 l_message_code := 'PV_PREREQ_MEMB_TERMINATED';
2784 ELSIF p_event_code = 'EXPIRED' THEN
2785 l_status_reason_code := 'PREREQ_MEMBERSHIP_EXPIRED';
2786 l_message_code := 'PV_PREREQ_MEMB_EXPIRED';
2787 ELSIF p_event_code = 'DOWNGRADED' THEN
2788 l_status_reason_code := 'PREREQ_MEMBERSHIP_DOWNGRADED';
2789 l_message_code := 'PV_PREREQ_MEMB_DOWNGRADED';
2790 END IF;
2791
2792 term_all_memberships
2793 (
2794 p_membership_table => l_membid_tbl
2795 , p_event_code => l_event_code
2796 , p_status_reason_code => l_status_reason_code
2797 , p_message_code => l_message_code
2798 , p_comments => p_comments
2799 , x_return_status => x_return_status
2800 , x_msg_count => x_msg_count
2801 , x_msg_data => x_msg_data
2802
2803 );
2804
2805 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2806 RAISE FND_API.G_EXC_ERROR;
2807 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2808 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2809 END IF;
2810
2811 END IF;
2812 --call cancel_all_enrollments to cancel all the enrollemnt requests in the l_enrl_req_tbl
2813 IF l_enrl_req_tbl.exists(1) THEN
2814
2815 cancel_all_enrollments
2816 (
2817 p_enrollment_id_tbl => l_enrl_req_tbl
2818 , p_status_reason_code => p_status_reason_code
2819 , p_comments => p_comments
2820 , x_return_status => x_return_status
2821 , x_msg_count => x_msg_count
2822 , x_msg_data => x_msg_data
2823 );
2824 IF x_return_status = FND_API.g_ret_sts_error THEN
2825 RAISE FND_API.g_exc_error;
2826 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
2827 RAISE FND_API.g_exc_unexpected_error;
2828 END IF;
2829 END IF;
2830 -- delete all values from the l_enrl_req_tbl variable
2831 l_enrl_req_tbl.delete();
2832 ecounter := 1;
2833 END IF; -- end of if , if any prepreq programs exist.
2834
2835 /** add the membership id (p_membership_id) to l_membid_tbl table.
2836 this is done to get all the memberships that we terminated including prereqs in l_membid_tbl table.
2837 */
2838
2839 l_membid_tbl.extend(1);
2840 l_membid_tbl(mcounter) := p_membership_id;
2841 /** now if member type is GLOBAL, we need to terminate/expire all corresponding subsidiary memberships or
2842 any corresponding subsidiary memberships thats in the upgrade /downgarde path of the terminated program.
2843 If the membership is being downgraded, don't do anything on the subsidiary memberships.
2844 */
2845 IF l_member_type = 'GLOBAL' AND p_event_code<> 'DOWNGRADED' THEN
2846
2847 -- for each membership id in l_mmeberships_tbl, find out all the dependent programs
2848 FOR m in 1..l_membid_tbl.count() LOOP
2849
2850 l_depend_prgm_ids_tbl := get_dependent_program_id( l_membid_tbl(m) );
2851
2852 IF l_depend_prgm_ids_tbl.exists(1) THEN
2853 --need to add all the values in l_depend_prgm_ids_tbl into another and keep adding it
2854 --for all the memberships
2855 FOR n in 1.. l_depend_prgm_ids_tbl.count() LOOP
2856 l_all_depend_prgmids_tbl.extend(1);
2857 l_all_depend_prgmids_tbl(dep_counter) := l_depend_prgm_ids_tbl(n);
2858 dep_counter := dep_counter+1;
2859
2860 END LOOP;
2861 END IF;
2862
2863 END LOOP;
2864
2865 l_membid_tbl.delete();
2866 mcounter := 1;
2867 -- pick all enrollment requests that are dependent on these programs
2868 -- and whose partner_id is a subsidiary of the global
2869 -- if that enrollment is incomplete or awaiting approval, cancel that enrollment request
2870 -- else if its approved,, get the membership id associated with it and add it to l_subs_memb_id_tbl
2871 -- for termination
2872 -- also get the prereq programs for the terminated memberships and cancel/terminate any enrollments there.
2873
2874 IF l_all_depend_prgmids_tbl.exists(1) THEN
2875
2876 -- there could be same program ids in l_all_depend_prgmids_tbl. so get the distinct ids
2877 l_all_depend_prgmids_tbl := getUniqueIDs( l_all_depend_prgmids_tbl );
2878
2879 FOR sub_enr in subsidiary_csr( l_partner_id, l_all_depend_prgmids_tbl ) LOOP
2880
2881 IF sub_enr.request_status_code IN ( 'INCOMPELTE','AWAITING_APPROVAL' ) THEN
2882 --cancel the enrollments and write to log that this is because of prereq program got terminated
2883 l_enrl_req_tbl.extend(1);
2884 l_enrl_req_tbl(ecounter) := sub_enr.enrl_request_id;
2885 ecounter := ecounter+1;
2886 ELSE
2887 IF sub_enr.membership_status_code IN ( 'ACTIVE','FUTURE' ) THEN
2888 l_membid_tbl.extend(1);
2889 l_membid_tbl(mcounter) := sub_enr.membership_id;
2890 --just capture all subsidiaries partner_ids whose atleast one membership is getting terminated
2891 l_sub_partner_id_tbl.extend(1);
2892 l_sub_partner_id_tbl(mcounter) := sub_enr.partner_id;
2893 mcounter := mcounter+1;
2894 END IF;
2895 END IF;
2896
2897 END LOOP;
2898 -- set the message code and status reasom code
2899 IF p_event_code = 'TERMINATED' THEN
2900 l_status_reason_code := 'GLOBAL_MEMBERSHIP_TERMINATED';
2901 l_message_code := 'PV_GLOBAL_MEMB_TERMINATED';
2902 ELSIF p_event_code = 'EXPIRED' THEN
2903 l_status_reason_code := 'GLOBAL_MEMBERSHIP_EXPIRED';
2904 l_message_code := 'PV_GLOBAL_MEMB_EXPIRED';
2905 END IF;
2906 --cancel all related enrollemnts .. these are becuase of WW tremination
2907 IF l_enrl_req_tbl.exists(1) THEN
2908 cancel_all_enrollments
2909 (
2910 p_enrollment_id_tbl => l_enrl_req_tbl
2911 , p_status_reason_code => p_status_reason_code
2912 , p_comments => p_comments
2913 , x_return_status => x_return_status
2914 , x_msg_count => x_msg_count
2915 , x_msg_data => x_msg_data
2916 );
2917 IF x_return_status = FND_API.g_ret_sts_error THEN
2918 RAISE FND_API.g_exc_error;
2919 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
2920 RAISE FND_API.g_exc_unexpected_error;
2921 END IF;
2922 END IF;
2923 l_enrl_req_tbl.delete();
2924 ecounter := 1;
2925 -- terminate the subsidiary memberships that are in the l_membid_tbl table
2926 IF l_membid_tbl.exists(1) THEN
2927 --terminate the dependent programs
2928
2929 term_all_memberships
2930 (
2931 p_membership_table => l_membid_tbl
2932 , p_event_code => l_event_code
2933 , p_status_reason_code => l_status_reason_code
2934 , p_message_code => l_message_code
2935 , p_comments => p_comments
2936 , x_return_status => x_return_status
2937 , x_msg_count => x_msg_count
2938 , x_msg_data => x_msg_data
2939
2940 );
2941 -- find out all the program ids for the subsidiary memberships that got terminated
2942 OPEN prereq_sub_csr (l_membid_tbl);
2943 FETCH prereq_sub_csr BULK COLLECT INTO l_prereq_sub_id_tbl;
2944 CLOSE prereq_sub_csr;
2945 l_membid_tbl.delete();
2946 mcounter:=1;
2947 IF l_prereq_sub_id_tbl.exists(1) THEN
2948 -- loop through and find out all the prereq programs
2949 FOR s in 1..l_prereq_sub_id_tbl.count() LOOP
2950 --get the prereqs for each program and add it to another table variable
2951 get_prereq_programs( l_prereq_sub_id_tbl(s), l_prereq_prgm_tbl );
2952 --l_prereq_prgm_tbl := get_prereq_programs( l_prereq_sub_id_tbl(s) );
2953 IF l_prereq_prgm_tbl.exists(1) THEN
2954 FOR t in 1..l_prereq_prgm_tbl.count() LOOP
2955 l_all_prereq_prgm_tbl.extend(1);
2956 l_all_prereq_prgm_tbl(subscounter):= l_prereq_prgm_tbl(t);
2957 subscounter := subscounter+1;
2958
2959 END LOOP;
2960 END IF;
2961
2962 END LOOP;
2963 END IF;
2964 IF l_all_prereq_prgm_tbl.exists(1) THEN
2965 -- there could be same program ids in l_all_depend_prgmids_tbl. so get the distinct ids
2966 l_all_prereq_prgm_tbl := getUniqueIDs( l_all_prereq_prgm_tbl );
2967 FOR enroll in enrl_sub_csr( l_sub_partner_id_tbl, l_all_prereq_prgm_tbl ) LOOP
2968 IF enroll.request_status_code IN ( 'INCOMPELTE','AWAITING_APPROVAL' ) THEN
2969 --cancel the enrollments and write to log that this is because of prereq program got terminated
2970 l_enrl_req_tbl.extend(1);
2971 l_enrl_req_tbl(ecounter) := enroll.enrl_request_id;
2972 ecounter := ecounter+1;
2973 ELSE
2974 IF enroll.membership_status_code IN ( 'ACTIVE','FUTURE' ) THEN
2975 l_membid_tbl.extend(1);
2976 l_membid_tbl(mcounter) := enroll.membership_id;
2977 mcounter := mcounter+1;
2978 END IF;
2979 END IF;
2980
2981 END LOOP;
2982
2983 IF p_event_code = 'TERMINATED' THEN
2984 l_status_reason_code := 'SUBS_PREREQ_MEMB_TERMINATED';
2985 l_message_code := 'PV_SUBS_PREREQ_MEMB_TERMINATED';
2986 ELSIF p_event_code = 'EXPIRED' THEN
2987 l_status_reason_code := 'SUBS_PREREQ_MEMB_EXPIRED';
2988 l_message_code := 'PV_SUBS_PREREQ_MEMB_EXPIRED';
2989 END IF;
2990
2991 -- cancel all related prerequisite enrollments of the subsidiaries.
2992 IF l_enrl_req_tbl.exists(1) THEN
2993 cancel_all_enrollments
2994 (
2995 p_enrollment_id_tbl => l_enrl_req_tbl
2996 , p_status_reason_code => p_status_reason_code
2997 , p_comments => p_comments
2998 , x_return_status => x_return_status
2999 , x_msg_count => x_msg_count
3000 , x_msg_data => x_msg_data
3001 );
3002 IF x_return_status = FND_API.g_ret_sts_error THEN
3003 RAISE FND_API.g_exc_error;
3004 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
3005 RAISE FND_API.g_exc_unexpected_error;
3006 END IF;
3007 END IF;
3008
3009 --terminate the dependent programs prereqs
3010 IF l_membid_tbl.exists(1) THEN
3011 --terminate the dependent programs
3012 term_all_memberships
3013 (
3014 p_membership_table => l_membid_tbl
3015 , p_event_code => l_event_code
3016 , p_status_reason_code => l_status_reason_code
3017 , p_message_code => l_message_code
3018 , p_comments => p_comments
3019 , x_return_status => x_return_status
3020 , x_msg_count => x_msg_count
3021 , x_msg_data => x_msg_data
3022
3023 );
3024 IF x_return_status = FND_API.g_ret_sts_error THEN
3025 RAISE FND_API.g_exc_error;
3026 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
3027 RAISE FND_API.g_exc_unexpected_error;
3028 END IF;
3029 END IF;
3030
3031 END IF;-- end of if, if there are any prereqs to terminated subsidiary programs
3032
3033 END IF; --end of if , if there are dependenden subsidiary memberships to be terminated
3034
3035 END IF; --end of if , if there are dependent programs for the global membership prorgam
3036
3037 END IF; -- end of if , if member type is global
3038 -- Debug Message
3039 IF (PV_DEBUG_HIGH_ON) THEN
3040 PVX_UTILITY_PVT.debug_message( 'Private API: ' || l_api_name || 'end' );
3041 END IF;
3042
3043 -- Standard call to get message count and if count is 1, get message info.
3044 FND_MSG_PUB.Count_And_Get
3045 (
3046 p_count => x_msg_count
3047 , p_data => x_msg_data
3048 );
3049 IF FND_API.to_Boolean( p_commit ) THEN
3050 COMMIT WORK;
3051 END IF;
3052
3053 EXCEPTION
3054 WHEN FND_API.G_EXC_ERROR THEN
3055 ROLLBACK TO Terminate_membership;
3056 x_return_status := FND_API.G_RET_STS_ERROR;
3057 -- Standard call to get message count and if count=1, get the message
3058 FND_MSG_PUB.Count_And_Get (
3059 p_encoded => FND_API.G_FALSE,
3060 p_count => x_msg_count,
3061 p_data => x_msg_data
3062 );
3063
3064 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3065 ROLLBACK TO Terminate_membership;
3066 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3067 -- Standard call to get message count and if count=1, get the message
3068 FND_MSG_PUB.Count_And_Get (
3069 p_encoded => FND_API.G_FALSE,
3070 p_count => x_msg_count,
3071 p_data => x_msg_data
3072 );
3073
3074 WHEN OTHERS THEN
3075 ROLLBACK TO Terminate_membership;
3076 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3077 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3078 THEN
3079 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
3080 END IF;
3081 -- Standard call to get message count and if count=1, get the message
3082 FND_MSG_PUB.Count_And_Get (
3083 p_encoded => FND_API.G_FALSE,
3084 p_count => x_msg_count,
3085 p_data => x_msg_data
3086 );
3087
3088 END Terminate_membership;
3089
3090 PROCEDURE downgrade_membership
3091 (
3092 p_api_version_number IN NUMBER
3093 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
3094 , p_commit IN VARCHAR2 := FND_API.G_FALSE
3095 , p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
3096 , p_membership_id IN NUMBER -- membership id of the program that you are dwongrading
3097 , p_status_reason_code IN VARCHAR2 -- reason for termoination or downgrade
3098 , p_comments IN VARCHAR2 DEFAULT NULL
3099 , p_program_id_downgraded_to IN NUMBER --programid into which the partner is downgraded to.
3100 , p_requestor_resource_id IN NUMBER --resource_id of the user who's performing the action
3101 , x_new_memb_id OUT NOCOPY NUMBER
3102 , x_return_status OUT NOCOPY VARCHAR2
3103 , x_msg_count OUT NOCOPY NUMBER
3104 , x_msg_data OUT NOCOPY VARCHAR2
3105 )
3106 IS
3107 CURSOR membership_csr(p_memb_id NUMBER) IS
3108 SELECT partner_id
3109 , original_end_date
3110 , enrl_request_id
3111 , program_name
3112 FROM pv_pg_memberships memb
3113 , pv_partner_program_vl prgm
3114 WHERE membership_id = p_memb_id
3115 AND memb.program_id=prgm.program_id;
3116
3117 CURSOR to_program_csr ( p_progm_id IN NUMBER ) IS
3118 SELECT program_name
3119 FROM pv_partner_program_vl
3120 where program_id=p_progm_id ;
3121
3122
3123
3124
3125 l_api_name CONSTANT VARCHAR2(30) := 'downgrade_membership';
3126 l_api_version_number CONSTANT NUMBER := 1.0;
3127 l_pv_pg_new_memb_rec memb_rec_type;
3128 l_pv_pg_enrq_rec PV_Pg_Enrl_Requests_PVT.enrl_request_rec_type;
3129 l_mmbr_tran_rec pv_pg_mmbr_transitions_PVT.mmbr_tran_rec_type;
3130 l_partner_id NUMBER;
3131 l_enrl_request_id NUMBER;
3132 l_membership_id NUMBER;
3133 l_original_end_date DATE;
3134 l_mmbr_transition_id NUMBER;
3135 l_from_enrl_request_id NUMBER;
3136 l_from_program_name VARCHAR2(60);
3137 l_to_program_name VARCHAR2(60);
3138 l_param_tbl_var PVX_UTILITY_PVT.log_params_tbl_type;
3139 BEGIN
3140 -- Standard Start of API savepoint
3141 SAVEPOINT downgrade_membership ;
3142 -- Standard call to check for call compatibility.
3143 IF NOT FND_API.Compatible_API_Call
3144 ( l_api_version_number
3145 ,p_api_version_number
3146 ,l_api_name
3147 ,G_PKG_NAME
3148 )
3149 THEN
3150 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3151 END IF;
3152 -- Initialize message list if p_init_msg_list is set to TRUE.
3153 IF FND_API.to_Boolean( p_init_msg_list ) THEN
3154 FND_MSG_PUB.initialize;
3155 END IF;
3156 -- Debug Message
3157 IF (PV_DEBUG_HIGH_ON) THEN
3158 PVX_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
3159 END IF;
3160
3161 -- Initialize API return status to SUCCESS
3162 x_return_status := FND_API.G_RET_STS_SUCCESS;
3163 -- Validate Environment
3164 IF FND_GLOBAL.USER_ID IS NULL THEN
3165 PVX_UTILITY_PVT.Error_Message(p_message_name => 'USER_PROFILE_MISSING');
3166 RAISE FND_API.G_EXC_ERROR;
3167 END IF;
3168 -- call terminate membership with event code as downgraded
3169 Terminate_membership
3170 (
3171 p_api_version_number =>1.0
3172 , p_init_msg_list => FND_API.G_FALSE
3173 , p_commit => FND_API.G_FALSE
3174 , p_validation_level => FND_API.g_valid_level_full
3175 , p_membership_id => p_membership_id
3176 , p_event_code => 'DOWNGRADED'
3177 , p_memb_type => NULL
3178 , p_status_reason_code => 'POOR_PERF'
3179 , p_comments => p_comments
3180 , x_return_status => x_return_status
3181 , x_msg_count => x_msg_count
3182 , x_msg_data => x_msg_data
3183 );
3184 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
3185 RAISE FND_API.G_EXC_ERROR;
3186 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3187 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3188 END IF;
3189
3190 OPEN membership_csr(p_membership_id);
3191 FETCH membership_csr INTO l_partner_id,l_original_end_date,l_from_enrl_request_id,l_from_program_name;
3192 CLOSE membership_csr;
3193
3194 -- create an enrollment request with approved status
3195 l_pv_pg_enrq_rec.partner_id := l_partner_id;
3196 l_pv_pg_enrq_rec.program_id := p_program_id_downgraded_to;
3197 l_pv_pg_enrq_rec.requestor_resource_id := p_requestor_resource_id;
3198 l_pv_pg_enrq_rec.request_status_code := 'APPROVED';
3199 l_pv_pg_enrq_rec.enrollment_type_code := 'DOWNGRADE';
3200 l_pv_pg_enrq_rec.payment_status_code := 'NOT_SUBMITTED';
3201 l_pv_pg_enrq_rec.request_submission_date := sysdate;
3202 l_pv_pg_enrq_rec.request_initiated_by_code := 'VENDOR';
3203 l_pv_pg_enrq_rec.contract_status_code := 'NOT_SIGNED';
3204
3205 PV_Pg_Enrl_Requests_PVT.Create_Pg_Enrl_Requests
3206 (
3207 p_api_version_number =>1.0
3208 , p_init_msg_list => FND_API.G_FALSE
3209 , p_commit => FND_API.G_FALSE
3210 , p_validation_level => FND_API.g_valid_level_full
3211 , x_return_status => x_return_status
3212 , x_msg_count => x_msg_count
3213 , x_msg_data => x_msg_data
3214 , p_enrl_request_rec => l_pv_pg_enrq_rec
3215 , x_enrl_request_id => l_enrl_request_id
3216 );
3217 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
3218 RAISE FND_API.G_EXC_ERROR;
3219 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3220 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3221 END IF;
3222 --create a membership record with the downgraded program id and end date of the program from which it was
3223 --downgraded
3224 l_pv_pg_new_memb_rec.enrl_request_id := l_enrl_request_id;
3225 l_pv_pg_new_memb_rec.start_date := sysdate;
3226 l_pv_pg_new_memb_rec.original_end_date := l_original_end_date;
3227 l_pv_pg_new_memb_rec.membership_status_code := 'ACTIVE';
3228 l_pv_pg_new_memb_rec.partner_id := l_partner_id;
3229 l_pv_pg_new_memb_rec.program_id := p_program_id_downgraded_to;
3230
3231 PV_Pg_Memberships_PVT.Create_Pg_memberships
3232 ( p_api_version_number=>1.0
3233 , p_init_msg_list => FND_API.G_FALSE
3234 , p_commit => FND_API.G_FALSE
3235 , p_validation_level => FND_API.g_valid_level_full
3236 , x_return_status => x_return_status
3237 , x_msg_count => x_msg_count
3238 , x_msg_data => x_msg_data
3239 , p_memb_rec => l_pv_pg_new_memb_rec
3240 , x_membership_id => l_membership_id
3241 );
3242 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
3243 RAISE FND_API.G_EXC_ERROR;
3244 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3245 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3246 END IF;
3247 x_new_memb_id :=l_membership_id;
3248 --insert into member transitions table
3249
3250 l_mmbr_tran_rec.from_membership_id:=p_membership_id;
3251 l_mmbr_tran_rec.to_membership_id:=l_membership_id;
3252 pv_pg_mmbr_transitions_PVT.Create_Mmbr_Trans
3253 (
3254 p_api_version_number =>1.0
3255 , p_init_msg_list => FND_API.G_FALSE
3256 , p_commit => FND_API.G_FALSE
3257 , p_validation_level => FND_API.g_valid_level_full
3258 , x_return_status => x_return_status
3259 , x_msg_count => x_msg_count
3260 , x_msg_data => x_msg_data
3261 , p_mmbr_tran_rec => l_mmbr_tran_rec
3262 , x_mmbr_transition_id => l_mmbr_transition_id
3263 );
3264 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
3265 RAISE FND_API.G_EXC_ERROR;
3266 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3267 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3268 END IF;
3269 --call responsiblity management api for the new membership
3270 Pv_User_Resp_Pvt.manage_memb_resp
3271 (
3272 p_api_version_number => 1.0
3273 , p_init_msg_list => Fnd_Api.g_false
3274 , p_commit => Fnd_Api.g_false
3275 , p_membership_id => l_membership_id
3276 , x_return_status => x_return_status
3277 , x_msg_count => x_msg_count
3278 , x_msg_data => x_msg_data
3279 );
3280
3281 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
3282 RAISE FND_API.G_EXC_ERROR;
3283 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3284 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3285 END IF;
3286
3287 OPEN to_program_csr ( p_program_id_downgraded_to );
3288 FETCH to_program_csr INTO l_to_program_name;
3289 CLOSE to_program_csr;
3290
3291 l_param_tbl_var(1).param_name := 'FROM_PROGRAM_NAME';
3292 l_param_tbl_var(1).param_value := l_from_program_name;
3293
3294 l_param_tbl_var(2).param_name := 'TO_PROGRAM_NAME';
3295 l_param_tbl_var(2).param_value := l_to_program_name;
3296
3297
3298 PVX_UTILITY_PVT.create_history_log
3299 (
3300 p_arc_history_for_entity_code => 'MEMBERSHIP'
3301 , p_history_for_entity_id => l_enrl_request_id
3302 , p_history_category_code => 'ENROLLMENT'
3303 , p_message_code => 'PV_MEMBERSHIP_DOWNGRADED'
3304 , p_comments => p_comments
3305 , p_partner_id => l_partner_id
3306 , p_access_level_flag => 'P'
3307 , p_interaction_level => PVX_Utility_PVT.G_INTERACTION_LEVEL_50
3308 , p_log_params_tbl => l_param_tbl_var
3309 , p_init_msg_list => FND_API.g_false
3310 , p_commit => FND_API.G_FALSE
3311 , x_return_status => x_return_status
3312 , x_msg_count => x_msg_count
3313 , x_msg_data => x_msg_data
3314 );
3315
3316
3317 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
3318 RAISE FND_API.G_EXC_ERROR;
3319 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3320 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3321 END IF;
3322
3323 PV_PG_NOTIF_UTILITY_PVT.Send_Workflow_Notification
3324 (
3325 p_api_version_number => 1.0
3326 , p_init_msg_list => FND_API.G_FALSE
3327 , p_commit => FND_API.G_FALSE
3328 , p_validation_level => FND_API.G_VALID_LEVEL_FULL
3329 , p_context_id => p_program_id_downgraded_to
3330 , p_context_code => 'PROGRAM'
3331 , p_target_ctgry => 'PARTNER'
3332 , p_target_ctgry_pt_id => l_partner_id -- this should be PARTNER ID
3333 , p_notif_event_code => 'PG_DOWNGRADE'
3334 , p_entity_id => l_from_enrl_request_id
3335 , p_entity_code => 'ENRQ'
3336 , p_wait_time => 0
3337 , x_return_status => x_return_status
3338 , x_msg_count => x_msg_count
3339 , x_msg_data => x_msg_data
3340 );
3341
3342
3343 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
3344 RAISE FND_API.G_EXC_ERROR;
3345 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3346 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3347 END IF;
3348
3349 -- Debug Message
3350 IF (PV_DEBUG_HIGH_ON) THEN
3351 PVX_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
3352 END IF;
3353
3354 -- Standard call to get message count and if count is 1, get message info.
3355 FND_MSG_PUB.Count_And_Get
3356 (
3357 p_count => x_msg_count
3358 , p_data => x_msg_data
3359 );
3360 IF FND_API.to_Boolean( p_commit ) THEN
3361 COMMIT WORK;
3362 END IF;
3363
3364 EXCEPTION
3365 WHEN FND_API.G_EXC_ERROR THEN
3366 ROLLBACK TO downgrade_membership;
3367 x_return_status := FND_API.G_RET_STS_ERROR;
3368 -- Standard call to get message count and if count=1, get the message
3369 FND_MSG_PUB.Count_And_Get (
3370 p_encoded => FND_API.G_FALSE,
3371 p_count => x_msg_count,
3372 p_data => x_msg_data
3373 );
3374
3375 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3376 ROLLBACK TO downgrade_membership;
3377 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3378 -- Standard call to get message count and if count=1, get the message
3379 FND_MSG_PUB.Count_And_Get (
3380 p_encoded => FND_API.G_FALSE,
3381 p_count => x_msg_count,
3382 p_data => x_msg_data
3383 );
3384
3385 WHEN OTHERS THEN
3386 ROLLBACK TO downgrade_membership;
3387 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3388 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3389 THEN
3390 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
3391 END IF;
3392 -- Standard call to get message count and if count=1, get the message
3393 FND_MSG_PUB.Count_And_Get (
3394 p_encoded => FND_API.G_FALSE,
3395 p_count => x_msg_count,
3396 p_data => x_msg_data
3397 );
3398 END downgrade_membership;
3399
3400 -- to calculate the end date for a program from sysdate.
3401 -- this should be called only when the memebership end date is updated
3402 FUNCTION getenddate( p_program_id in NUMBER )
3403 RETURN DATE IS
3404
3405 CURSOR rec_cur(p_prgm_id NUMBER, start_date DATE ) IS
3406 SELECT program_end_date
3407 , decode( membership_period_unit
3408 , 'DAY', start_date+membership_valid_period
3409 , 'MONTH', add_months( start_date, membership_valid_period )
3410 , 'YEAR', add_months( start_date, 12*membership_valid_period )
3411 , null
3412 ) membership_end_date
3413 FROM pv_partner_program_b
3414 WHERE program_id=p_prgm_id;
3415
3416 l_program_end_date DATE;
3417 l_membership_end_date DATE;
3418 l_start_date DATE;
3419
3420 BEGIN
3421
3422 OPEN rec_cur( p_program_id, sysdate);
3423 FETCH rec_cur into l_program_end_date,l_membership_end_date;
3424 IF rec_cur%found THEN
3425 IF l_membership_end_date is NULL THEN
3426 l_membership_end_date := l_program_end_date;
3427 END IF;
3428 END IF;
3429 CLOSE rec_cur;
3430 RETURN l_membership_end_date;
3431
3432 EXCEPTION
3433
3434 WHEN OTHERS THEN
3435 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3436 END getenddate;
3437
3438
3439 PROCEDURE Update_membership_end_date
3440 (
3441 p_api_version_number IN NUMBER
3442 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
3443 , p_commit IN VARCHAR2 := FND_API.G_FALSE
3444 , p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
3445 , p_membership_id IN NUMBER -- membership_id for which end date needs to be updated
3446 , p_new_date IN DATE
3447 , p_comments IN VARCHAR2 DEFAULT NULL
3448 , x_return_status OUT NOCOPY VARCHAR2
3449 , x_msg_count OUT NOCOPY NUMBER
3450 , x_msg_data OUT NOCOPY VARCHAR2
3451 ) IS
3452
3453
3454 ---CURSOR TO get the membertype and partner_id
3455 CURSOR memb_type_csr(memb_id NUMBER) IS
3456 SELECT memb.partner_id
3457 , memb.object_version_number
3458 , enty.attr_value
3459 , memb.original_end_date
3460 FROM pv_pg_memberships memb
3461 , pv_enty_attr_values enty
3462 WHERE memb.membership_id=memb_id
3463 AND memb.partner_id=enty.entity_id
3464 AND enty.entity = 'PARTNER'
3465 AND enty.entity_id = memb.partner_id
3466 AND enty.attribute_id = 6
3467 AND enty.latest_flag = 'Y';
3468
3469 --cursor to get all the subsidiaries and all their active memberships
3470 --that are dependent on this membership id that is being updated
3471
3472 CURSOR subsidiary_csr( global_partner_id NUMBER,p_depentent_id_tbl JTF_NUMBER_TABLE) IS
3473 SELECT memb.membership_id
3474 , memb.object_version_number
3475 , memb.original_end_date
3476 , memb.partner_id
3477 , memb.program_id
3478 FROM pv_partner_profiles subs_prof
3479 , pv_partner_profiles global_prof
3480 , pv_enty_attr_values subs_enty_val
3481 , hz_relationships rel
3482 , pv_pg_memberships memb
3483 , pv_pg_enrl_requests enrl
3484 WHERE global_prof.partner_id = global_partner_id
3485 AND global_prof.partner_party_id = rel.subject_id
3486 AND rel.relationship_type = 'PARTNER_HIERARCHY'
3487 AND rel.object_id = subs_prof.partner_party_id
3488 AND rel.relationship_code = 'PARENT_OF'
3489 AND rel.status = 'A'
3490 AND NVL(rel.start_date, SYSDATE) <= SYSDATE
3491 AND NVL(rel.end_date, SYSDATE) >= SYSDATE
3492 AND subs_enty_val.entity = 'PARTNER'
3493 AND subs_enty_val.entity_id = subs_prof.partner_id
3494 AND subs_enty_val.attribute_id = 6
3495 AND subs_enty_val.latest_flag = 'Y'
3496 AND subs_enty_val.attr_value = 'SUBSIDIARY'
3497 AND subs_prof.partner_id=memb.partner_id
3498 AND memb.membership_status_code='ACTIVE'
3499 AND memb.enrl_request_id=enrl.enrl_request_id
3500 AND enrl.dependent_program_id
3501 in ( SELECT * FROM TABLE ( CAST( p_depentent_id_tbl AS JTF_NUMBER_TABLE ) ) );
3502
3503 l_api_name CONSTANT VARCHAR2(30) := 'Update_membership_end_date';
3504 l_api_version_number CONSTANT NUMBER := 1.0;
3505 l_dependent_program_id JTF_NUMBER_TABLE;
3506 l_partner_id NUMBER;
3507 l_member_type VARCHAR2(30);
3508 l_object_version_number NUMBER;
3509 l_pv_pg_memb_rec memb_rec_type;
3510 l_global_current_end_date DATE;
3511 l_subs_end_date DATE;
3512
3513 BEGIN
3514
3515 -- Standard Start of API savepoint
3516 SAVEPOINT Update_membership_end_date ;
3517 -- Standard call to check for call compatibility.
3518 IF NOT FND_API.Compatible_API_Call
3519 ( l_api_version_number
3520 ,p_api_version_number
3521 ,l_api_name
3522 ,G_PKG_NAME
3523 )
3524 THEN
3525 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3526 END IF;
3527 -- Initialize message list if p_init_msg_list is set to TRUE.
3528 IF FND_API.to_Boolean( p_init_msg_list ) THEN
3529 FND_MSG_PUB.initialize;
3530 END IF;
3531 -- Debug Message
3532 IF (PV_DEBUG_HIGH_ON) THEN
3533 PVX_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
3534 END IF;
3535
3536 -- Initialize API return status to SUCCESS
3537 x_return_status := FND_API.G_RET_STS_SUCCESS;
3538 -- Validate Environment
3539 IF FND_GLOBAL.USER_ID IS NULL THEN
3540 PVX_UTILITY_PVT.Error_Message(p_message_name => 'USER_PROFILE_MISSING');
3541 RAISE FND_API.G_EXC_ERROR;
3542 END IF;
3543
3544 OPEN memb_type_csr( p_membership_id );
3545 FETCH memb_type_csr INTO l_partner_id,l_object_version_number,l_member_type,l_global_current_end_date;
3546 CLOSE memb_type_csr;
3547
3548 l_pv_pg_memb_rec.membership_id := p_membership_id;
3549 l_pv_pg_memb_rec.original_end_date := p_new_date;
3550 l_pv_pg_memb_rec.object_version_number := l_object_version_number;
3551 PV_Pg_Memberships_PVT.Update_Pg_Memberships
3552 ( p_api_version_number => 1.0
3553 , p_init_msg_list => Fnd_Api.g_false
3554 , p_commit => Fnd_Api.g_false
3555 , x_return_status => x_return_status
3556 , x_msg_count => x_msg_count
3557 , x_msg_data => x_msg_data
3558 , p_memb_rec => l_pv_pg_memb_rec
3559 );
3560 --also write to the history log
3561 IF l_member_type='GLOBAL' THEN
3562 l_dependent_program_id :=get_dependent_program_id( p_membership_id );
3563 IF l_dependent_program_id.exists(1) THEN
3564 FOR subsidiary in subsidiary_csr(l_partner_id,l_dependent_program_id) LOOP
3565 -- set the membership record to be updated
3566 l_subs_end_date := getenddate( subsidiary.program_id );
3567 IF l_subs_end_date > p_new_date THEN
3568 l_subs_end_date := p_new_date;
3569 END IF;
3570 l_pv_pg_memb_rec.membership_id := subsidiary.membership_id;
3571 l_pv_pg_memb_rec.original_end_date := l_subs_end_date;
3572 l_pv_pg_memb_rec.object_version_number := subsidiary.object_version_number;
3573 PV_Pg_Memberships_PVT.Update_Pg_Memberships
3574 ( p_api_version_number => 1.0
3575 ,p_init_msg_list => Fnd_Api.g_false
3576 ,p_commit => Fnd_Api.g_false
3577 ,x_return_status => x_return_status
3578 ,x_msg_count => x_msg_count
3579 ,x_msg_data => x_msg_data
3580 ,p_memb_rec => l_pv_pg_memb_rec
3581 );
3582 -- also write to the history log
3583 END LOOP;
3584 END IF;
3585 END IF;
3586 -- Debug Message
3587 IF (PV_DEBUG_HIGH_ON) THEN
3588 PVX_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
3589 END IF;
3590
3591 -- Standard call to get message count and if count is 1, get message info.
3592 FND_MSG_PUB.Count_And_Get
3593 (p_count => x_msg_count,
3594 p_data => x_msg_data
3595 );
3596 IF FND_API.to_Boolean( p_commit ) THEN
3597 COMMIT WORK;
3598 END IF;
3599
3600 EXCEPTION
3601 WHEN FND_API.G_EXC_ERROR THEN
3602 ROLLBACK TO Update_membership_end_date;
3603 x_return_status := FND_API.G_RET_STS_ERROR;
3604 -- Standard call to get message count and if count=1, get the message
3605 FND_MSG_PUB.Count_And_Get (
3606 p_encoded => FND_API.G_FALSE,
3607 p_count => x_msg_count,
3608 p_data => x_msg_data
3609 );
3610 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3611 ROLLBACK TO Update_membership_end_date;
3612 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3613 -- Standard call to get message count and if count=1, get the message
3614 FND_MSG_PUB.Count_And_Get (
3615 p_encoded => FND_API.G_FALSE,
3616 p_count => x_msg_count,
3617 p_data => x_msg_data
3618 );
3619 WHEN OTHERS THEN
3620 ROLLBACK TO Update_membership_end_date;
3621 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3622 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3623 THEN
3624 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
3625 END IF;
3626 -- Standard call to get message count and if count=1, get the message
3627 FND_MSG_PUB.Count_And_Get (
3628 p_encoded => FND_API.G_FALSE,
3629 p_count => x_msg_count,
3630 p_data => x_msg_data
3631 );
3632 END Update_membership_end_date;
3633
3634 /*****************************
3635 * logging_enabled
3636 *****************************/
3637 FUNCTION logging_enabled (p_log_level IN NUMBER)
3638 RETURN BOOLEAN
3639 IS
3640 BEGIN
3641 RETURN (p_log_level >= FND_LOG.G_CURRENT_RUNTIME_LEVEL);
3642 END;
3643
3644 /*****************************
3645 * debug_message
3646 *****************************/
3647 PROCEDURE debug_message
3648 (
3649 p_log_level IN NUMBER
3650 ,p_module_name IN VARCHAR2
3651 ,p_text IN VARCHAR2
3652 )
3653 IS
3654 BEGIN
3655
3656
3657 -- IF logging_enabled (p_log_level) THEN
3658 IF (p_log_level >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
3659 FND_LOG.STRING(p_log_level, p_module_name, p_text);
3660 END IF;
3661
3662 END debug_message;
3663
3664 /*****************************
3665 * WRITE_LOG
3666 *****************************/
3667 PROCEDURE WRITE_LOG
3668 (
3669 p_api_name IN VARCHAR2
3670 , p_log_message IN VARCHAR2
3671 )
3672 IS
3673
3674 BEGIN
3675 debug_message (
3676 p_log_level => g_log_level
3677 ,p_module_name => 'plsql.pv'||'.'|| g_pkg_name||'.'||p_api_name||'.'||p_log_message
3678 ,p_text => p_log_message
3679 );
3680 END WRITE_LOG;
3681
3682 /*****************************
3683 * TERMINATE_PTR_MEMBERSHIPS
3684 *****************************/
3685 FUNCTION TERMINATE_PTR_MEMBERSHIPS
3686 ( p_subscription_guid in raw,
3687 p_event in out NOCOPY wf_event_t)
3688 RETURN VARCHAR2
3689 IS
3690 l_api_name CONSTANT VARCHAR2(30) := 'TERMINATE_PTR_MEMBERSHIPS';
3691 l_partner_id NUMBER;
3692 l_old_status VARCHAR2(1);
3693 l_new_status VARCHAR2(1);
3694 x_return_status VARCHAR2(10);
3695 x_msg_count NUMBER;
3696 x_msg_data VARCHAR2(2000);
3697
3698 BEGIN
3699 FND_MSG_PUB.initialize;
3700 IF (PV_DEBUG_HIGH_ON) THEN
3701 WRITE_LOG(l_api_name, 'Start TERMINATE_PTR_MEMBERSHIPS');
3702 END IF;
3703 l_partner_id := p_event.GetValueForParameter('PARTNER_ID');
3704 l_old_status := p_event.GetValueForParameter('OLD_PARTNER_STATUS');
3705 l_new_status := p_event.GetValueForParameter('NEW_PARTNER_STATUS');
3706 IF (PV_DEBUG_HIGH_ON) THEN
3707 WRITE_LOG(l_api_name, 'l_partner_id = ' || l_partner_id);
3708 WRITE_LOG(l_api_name, 'l_old_status = ' || l_old_status);
3709 WRITE_LOG(l_api_name, 'l_new_status = ' || l_new_status);
3710 END IF;
3711
3712 x_return_status := FND_API.G_RET_STS_SUCCESS;
3713 IF (l_old_status = 'A' and l_new_status = 'I') THEN
3714 IF (PV_DEBUG_HIGH_ON) THEN
3715 WRITE_LOG(l_api_name, 'before calling Terminate_ptr_memberships');
3716 END IF;
3717 PV_Pg_Memberships_PVT.Terminate_ptr_memberships (
3718 p_api_version_number => 1.0
3719 ,p_init_msg_list => FND_API.G_FALSE
3720 ,p_commit => FND_API.G_FALSE
3721 ,p_partner_id => l_partner_id
3722 ,p_memb_type => null
3723 ,p_status_reason_code => 'PTR_INACTIVE'
3724 ,x_return_status => x_return_status
3725 ,x_msg_count => x_msg_count
3726 ,x_msg_data => x_msg_data
3727 );
3728 IF (PV_DEBUG_HIGH_ON) THEN
3729 WRITE_LOG(l_api_name, 'x_return_status = ' || x_return_status);
3730 END IF;
3731 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
3732 RAISE FND_API.G_EXC_ERROR;
3733 END IF;
3734
3735 IF (PV_DEBUG_HIGH_ON) THEN
3736 WRITE_LOG(l_api_name, 'before calling Register_term_ptr_memb_type');
3737 END IF;
3738 Pv_ptr_member_type_pvt.Register_term_ptr_memb_type (
3739 p_api_version_number => 1.0
3740 ,p_init_msg_list => FND_API.G_FALSE
3741 ,p_commit => FND_API.G_FALSE
3742 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
3743 ,p_partner_id => l_partner_id
3744 ,p_current_memb_type => null
3745 ,p_new_memb_type => null
3746 ,p_global_ptr_id => null
3747 ,x_return_status => x_return_status
3748 ,x_msg_count => x_msg_count
3749 ,x_msg_data => x_msg_data
3750 );
3751 IF (PV_DEBUG_HIGH_ON) THEN
3752 WRITE_LOG(l_api_name, 'x_return_status = ' || x_return_status);
3753 END IF;
3754 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
3755 RAISE FND_API.G_EXC_ERROR;
3756 END IF;
3757
3758 IF (PV_DEBUG_HIGH_ON) THEN
3759 WRITE_LOG(l_api_name, 'before calling revoke_default_resp');
3760 END IF;
3761 Pv_User_Resp_Pvt.revoke_default_resp (
3762 p_api_version_number => 1.0
3763 ,p_init_msg_list => FND_API.G_FALSE
3764 ,p_commit => FND_API.G_FALSE
3765 ,x_return_status => x_return_status
3766 ,x_msg_count => x_msg_count
3767 ,x_msg_data => x_msg_data
3768 ,p_partner_id => l_partner_id
3769 );
3770 IF (PV_DEBUG_HIGH_ON) THEN
3771 WRITE_LOG(l_api_name, 'x_return_status = ' || x_return_status);
3772 END IF;
3773 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
3774 RAISE FND_API.G_EXC_ERROR;
3775 END IF;
3776 ELSIF (l_old_status = 'I' and l_new_status = 'A') THEN
3777 IF (PV_DEBUG_HIGH_ON) THEN
3778 WRITE_LOG(l_api_name, 'before calling assign_default_resp');
3779 END IF;
3780 Pv_User_Resp_Pvt.assign_default_resp (
3781 p_api_version_number => 1.0
3782 ,p_init_msg_list => FND_API.G_FALSE
3783 ,p_commit => FND_API.G_FALSE
3784 ,x_return_status => x_return_status
3785 ,x_msg_count => x_msg_count
3786 ,x_msg_data => x_msg_data
3787 ,p_partner_id => l_partner_id
3788 );
3789 IF (PV_DEBUG_HIGH_ON) THEN
3790 WRITE_LOG(l_api_name, 'x_return_status = ' || x_return_status);
3791 END IF;
3792 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
3793 RAISE FND_API.G_EXC_ERROR;
3794 END IF;
3795 END IF;
3796 RETURN 'SUCCESS';
3797 EXCEPTION
3798 WHEN FND_API.G_EXC_ERROR THEN
3799 WF_CORE.CONTEXT('PV_PG_MEMBERSHIPS_PVT', 'TERMINATE_PTR_MEMBERSHIPS', p_event.getEventName(), p_subscription_guid);
3800 WF_EVENT.setErrorInfo(p_event, 'Error from Terminate_ptr_memberships');
3801 RETURN 'ERROR';
3802 WHEN OTHERS THEN
3803 WF_CORE.CONTEXT('PV_PG_MEMBERSHIPS_PVT', 'TERMINATE_PTR_MEMBERSHIPS', p_event.getEventName(), p_subscription_guid);
3804 WF_EVENT.setErrorInfo(p_event, 'ERROR');
3805 RETURN 'ERROR';
3806 END;
3807
3808
3809 END PV_Pg_Memberships_PVT;