1 package body Pv_Enrq_Bins_PVT as
2 /* $Header: pvxvbinb.pls 120.5 2006/05/11 11:54:47 dgottlie ship $*/
3 -- Start of Comments
4 --
5 -- NAME
6 -- Pv_Enrq_Bins_PVT
7 --
8 -- PURPOSE
9 -- This package contains API's for displaying data in program enrollment bins.
10 --
11 -- HISTORY
12 -- 11/12/2002 pukken CREATION
13 -- 08/01/2003 pukken changed the API for 11.15.10 changes
14 -- 18-FEB-2004 pukken fix for bug 3443733
15 -- 15-jun-2004 pukken fix for bug 3695436
16 -- 04-MAR-2005 pukken to develop ER 4208712
17 -- 22-NOV-2005 ktsao fix for bug 4749395
18 -- 18-JAN-2006 ktsao fix for bug 4948563
19 -- 28-MAR-2006 ktsao fix for bug 5116650
20 -- NOTE :
21 -- Copyright (c) 2002 Oracle Corporation Redwood Shores, California, USA
22 -- All rights reserved.
23
24 g_pkg_name CONSTANT VARCHAR2 (30) := 'Pv_Enrq_Bins_PVT';
25 g_file_name CONSTANT VARCHAR2 (15) := 'pvxvbinb.pls';
26 g_program_mode CONSTANT VARCHAR2 (15) := 'BINS';
27
28 PV_DEBUG_HIGH_ON CONSTANT boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);
29 PV_DEBUG_LOW_ON CONSTANT boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
30 PV_DEBUG_MEDIUM_ON CONSTANT boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM);
31
32
33 FUNCTION getInviteHeaderId(p_partner_id in number,p_program_id in NUMBER,p_invite_type_code in VARCHAR2)
34 RETURN NUMBER is
35 l_invite_header_id NUMBER:=null ;
36
37 CURSOR rec_cur(p_ptr_id number,p_prgm_id number,p_inv_type_code VARCHAR2) IS
38 SELECT max(invite_header_id)
39 FROM PV_PG_INVITE_HEADERS_b
40 WHERE partner_id=p_ptr_id
41 AND nvl(invite_end_date,sysdate+1)>sysdate
42 AND invite_for_program_id =p_prgm_id
43 AND invite_type_code =p_inv_type_code;
44
45
46
47 BEGIN
48 OPEN rec_cur(p_partner_id,p_program_id,p_invite_type_code);
49 FETCH rec_cur into l_invite_header_id;
50 CLOSE rec_cur;
51 RETURN l_invite_header_id;
52
53 EXCEPTION
54
55 WHEN OTHERS THEN
56 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
57 END getInviteHeaderId;
58
59
60 --------------------------------------------------------------------------
61 -- FUNCTION
62 -- isGlobalEnrolled
63 --
64 -- PURPOSE
65 -- To check whether the global partner of the given subsidiary partner has active or future membership
66 -- In a given program or in the upgrade path of the given program
67 -- IN
68 -- p_program_id NUMBER
69 -- p_subs_partner_id NUMBER this should be subsidiary partner Id
70 -- OUT
71 -- is a boolean value
72
73 FUNCTION isGlobalEnrolled(p_program_id in NUMBER, p_subs_partner_id in number)
74 RETURN BOOLEAN IS
75
76 CURSOR isenroll_csr(p_prgm_id NUMBER,p_global_ptr_id NUMBER) IS
77 Select 'X' from dual
78 WHERE EXISTS
79 (
80 select membership_id from pv_pg_memberships
81 where membership_status_code in ('ACTIVE','FUTURE')
82 AND partner_id=p_global_ptr_id
83 AND program_id IN
84 (
85 select CHANGE_to_program_id from pv_pg_enrl_change_rules
86 where change_direction_code='UPGRADE'
87 AND ACTIVE_FLAG='Y'
88 START with change_from_program_id=p_prgm_id
89 CONNECT by CHANGE_FROM_PROGRAM_id=PRIOR CHANGE_to_program_id
90 and CHANGE_TO_PROGRAM_ID<>PRIOR CHANGE_FROM_PROGRAM_Id
91 union select p_prgm_id FROM DUAL
92 )
93 );
94
95
96 CURSOR global_id_csr(p_sub_ptr_id NUMBER) IS
97 SELECT glob_prof.partner_id
98 FROM pv_partner_profiles subs_prof
99 ,pv_partner_profiles glob_prof
100 ,hz_relationships rel
101 WHERE rel.subject_id=subs_prof.partner_party_id
102 AND rel.relationship_code = 'SUBSIDIARY_OF'
103 AND rel.relationship_type = 'PARTNER_HIERARCHY'
104 AND rel.status = 'A'
105 AND NVL(rel.start_date, SYSDATE) <= SYSDATE
106 AND NVL(rel.end_date, SYSDATE) >= SYSDATE
107 AND subs_prof.partner_id=p_sub_ptr_id
108 AND REL.OBJECT_ID=glob_prof.partner_party_id;
109
110 l_global_ptr_id NUMBER;
111 l_global_enrolled VARCHAR2(1);
112 is_global_enrolled boolean:=false;
113
114 BEGIN
115
116
117 OPEN global_id_csr(p_subs_partner_id);
118 FETCH global_id_csr INTO l_global_ptr_id;
119 CLOSE global_id_csr;
120
121 IF l_global_ptr_id IS NOT NULL THEN
122
123 OPEN isenroll_csr(p_program_id,l_global_ptr_id);
124 FETCH isenroll_csr INTO l_global_enrolled;
125 CLOSE isenroll_csr;
126
127 IF l_global_enrolled='X' THEN
128
129 is_global_enrolled:=true;
130 END IF;
131 END IF;
132 RETURN is_global_enrolled;
133
134 EXCEPTION
135 WHEN NO_DATA_FOUND THEN
136 RETURN FALSE;
137 WHEN OTHERS THEN
138 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
139 END isGlobalEnrolled;
140
141 PROCEDURE new_programs
142 (
143 p_api_version_number IN NUMBER
144 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
145 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
146 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
147 ,p_partner_id IN NUMBER
148 ,p_member_type IN VARCHAR2
149 ,p_isprereq_eval IN VARCHAR2 :='Y'
150 ,x_enrq_param_cur OUT NOCOPY enrq_param_ref
151 ,x_return_status OUT NOCOPY VARCHAR2
152 ,x_msg_count OUT NOCOPY NUMBER
153 ,x_msg_data OUT NOCOPY VARCHAR2
154 ) IS
155
156
157 CURSOR prgm_csr(ptnr_id IN NUMBER) IS
158 SELECT pvppb.program_id program_id
159 ,pvppb.program_name program_name
160 ,pvppb.citem_version_id citem_version_id
161 ,pvppb.global_mmbr_reqd_flag global_mmbr_reqd_flag
162 ,pvppb.prereq_process_rule_id prereq_process_rule_id
163 ,pvppb.no_fee_flag no_fee_flag
164 ,pvppb.vad_invite_allow_flag vad_invite_allow_flag
165 FROM pv_partner_program_type_b pvpptb
166 ,pv_partner_program_vl pvppb
167 WHERE pvppb.program_status_code = 'ACTIVE'
168 AND pvppb.program_level_code ='MEMBERSHIP'
169 AND pvppb.enabled_flag = 'Y'
170 AND nvl(pvppb.allow_enrl_until_date, sysdate) > sysdate-1
171 AND pvppb.program_type_id = pvpptb.program_type_ID
172 AND pvpptb.ACTIVE_FLAG = 'Y'
173 AND pvpptb.enabled_flag = 'Y'
174 -- AND inv.invite_type_code(+)='INVITE'
175 AND EXISTS
176 (
177 SELECT 'X' FROM pv_program_partner_types pvppt
178 WHERE pvppt.partner_type IN
179 (
180 SELECT attr_value
181 FROM pv_enty_attr_values pveav
182 WHERE pveav.enabled_flag = 'Y'
183 AND pveav.latest_flag = 'Y'
184 AND pveav.entity = 'PARTNER'
185 AND pveav.entity_id = ptnr_id
186 AND pveav.attribute_id = 3
187 )
188 AND pvpptb.program_type_id = pvppt.program_type_id
189 )
190 AND pvppb.program_id NOT IN
191 (
192 SELECT rules.change_to_program_id
193 FROM pv_pg_enrl_change_rules rules
194 WHERE change_direction_code = 'UPGRADE'
195 AND effective_from_date <= sysdate
196 AND nvl(effective_to_date, sysdate) >= sysdate
197 AND active_flag = 'Y'
198 )
199 /* AND EXISTS -- check for pre-populated cache for pre-req evaluation
200 ( SELECT 1
201 FROM pv_pg_elig_programs elig
202 WHERE elig.program_id = pvppb.program_id
203 AND elig.partner_id = ptnr_id
204 ) */
205 ;
206
207
208 CURSOR enrl_csr(ptr_id NUMBER ,prgm_id NUMBER) IS
209 SELECT enrl_request_id,request_status_code,enrollment_type_code
210 FROM pv_pg_enrl_requests
211 WHERE enrl_request_id=
212 (
213 SELECT max(enrl_request_id)
214 FROM pv_pg_enrl_requests
215 WHERE partner_id=ptr_id
216 AND program_id=prgm_id
217 );
218
219 CURSOR memb_csr(enrl_id NUMBER) IS
220 SELECT membership_id,membership_status_code
221 FROM pv_pg_memberships
222 WHERE enrl_request_id=enrl_id;
223
224 CURSOR prgm_wo_prereq_csr(ptnr_id IN NUMBER) IS
225 SELECT pvppb.program_id program_id
226 ,pvppb.global_mmbr_reqd_flag global_mmbr_reqd_flag
227 ,pvppb.prereq_process_rule_id prereq_process_rule_id
228 ,pvppb.no_fee_flag no_fee_flag
229 ,pvppb.vad_invite_allow_flag vad_invite_allow_flag
230 FROM pv_partner_program_type_b pvpptb ,pv_partner_program_vl pvppb
231 WHERE pvppb.program_status_code = 'ACTIVE'
232 AND pvppb.program_level_code ='MEMBERSHIP'
233 AND pvppb.enabled_flag = 'Y'
234 AND nvl(pvppb.allow_enrl_until_date, sysdate) > sysdate-1
235 AND pvppb.program_type_id = pvpptb.program_type_ID
236 AND pvpptb.ACTIVE_FLAG = 'Y'
237 AND pvpptb.enabled_flag = 'Y'
238 AND EXISTS
239 (
240 SELECT 'X' FROM pv_program_partner_types pvppt
241 WHERE pvppt.partner_type IN
242 (
243 SELECT attr_value
244 FROM pv_enty_attr_values pveav
245 WHERE pveav.enabled_flag = 'Y'
246 AND pveav.latest_flag = 'Y'
247 AND pveav.entity = 'PARTNER'
248 AND pveav.entity_id = ptnr_id
249 AND pveav.attribute_id = 3
250 )
251 AND pvpptb.program_type_id = pvppt.program_type_id
252 )
253 AND pvppb.program_id NOT IN
254 (
255 SELECT rules.change_to_program_id
256 FROM pv_pg_enrl_change_rules rules
257 WHERE change_direction_code = 'UPGRADE'
258 AND effective_from_date <= sysdate
259 AND nvl(effective_to_date, sysdate) >= sysdate
260 AND active_flag = 'Y'
261 );
262
263
264 l_enrq_param_tbl PV_ENRL_REQ_PARAM_TBL := PV_ENRL_REQ_PARAM_TBL ();
265 l_api_name CONSTANT VARCHAR2(30) := 'new_programs';
266 l_api_version_number CONSTANT NUMBER := 1.0;
267 l_request_status_code VARCHAR2(30):=null;
268 l_membership_status_code VARCHAR2(30);
269 l_enrl_request_id NUMBER;
270 l_membership_id NUMBER;
271 l_enrollment_type_code VARCHAR2(30);
272 l_user_id NUMBER;
273 counter NUMBER:=1;
274 l_eligible_flag boolean:=true;
275 TYPE csr_type IS REF CURSOR;
276
277 prereq_csr csr_type ;
278 l_query_str1 VARCHAR2(1200);
279
280 l_cache VARCHAR2(1);
281 BEGIN
282 --Standard call to check for call compatibility.
283 IF NOT FND_API.Compatible_API_Call ( l_api_version_number
284 ,p_api_version_number
285 ,l_api_name
286 ,G_PKG_NAME
287 )
288
289 THEN
290 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
291 END IF;
292
293 -- Initialize message list if p_init_msg_list is set to TRUE.
294 IF FND_API.to_Boolean( p_init_msg_list )
295 THEN
296 FND_MSG_PUB.initialize;
297 END IF;
298
299 -- Debug Message
300 IF (PV_DEBUG_HIGH_ON) THEN
301 PVX_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
302 END IF;
303 -- Initialize API return status to SUCCESS
304
305 x_return_status := FND_API.G_RET_STS_SUCCESS;
306 --l_query_str1:= 'SELECT ''X'' FROM pv_pg_elig_programs elig WHERE elig.partner_id=' || to_char(p_partner_id) || 'AND elig.program_id =';
307 l_query_str1:= 'SELECT ''X'' FROM pv_pg_elig_programs elig WHERE elig.partner_id=:1' || 'AND elig.program_id =:2';
308
309 IF p_isprereq_eval='Y' THEN -- This check is to query the pre_req cache data as well
310 FOR rec_prgm in prgm_csr(p_partner_id) LOOP
311 l_request_status_code:=null;
312 l_enrl_request_id:=null;
313 l_enrollment_type_code:=null;
314 l_membership_status_code:=null;
315 l_eligible_flag:=true;
316 l_cache:=null;
317 --prereq evalution being moved as a dynamic query becase of theissue
318 --with this package getting invalidated after running the cincrurrent ptogram to refresh the cache.
319
320 OPEN prereq_csr FOR l_query_str1 USING p_partner_id,rec_prgm.program_id;
321 FETCH prereq_csr INTO l_cache;
322 CLOSE prereq_csr;
323
324 IF l_cache='X' THEN
325
326 IF p_member_type='SUBSIDIARY' THEN
327 IF rec_prgm.global_mmbr_reqd_flag='Y' THEN
328 l_eligible_flag:=isGlobalEnrolled(rec_prgm.program_id,p_partner_id);
329 END IF;
330 END If;
331
332 IF l_eligible_flag=true THEN
333 OPEN enrl_csr(p_partner_id,rec_prgm.program_id);
334 FETCH enrl_csr INTO l_enrl_request_id,l_request_status_code,l_enrollment_type_code;
335 CLOSE enrl_csr;
336
337 IF l_request_status_code IS NULL THEN
338 --populate the table
339 -- ----dbms_output.PUT_LINE('inside enrollment null');
340 l_enrq_param_tbl.EXTEND(1);
341 ----dbms_output.PUT_LINE('after extend ');
342 l_enrq_param_tbl(counter) := PV_ENRL_REQ_PARAM
343 (
344 rec_prgm.program_id
345 ,rec_prgm.program_name
346 ,rec_prgm.citem_version_id
347 , getInviteHeaderId(p_partner_id ,rec_prgm.program_id , 'INVITE')
348 -- ,rec_prgm.invite_header_id
349 ,null
350 ,null
351 ,null -- enrollment request id
352 ,null -- enrollment type code
353 ,null -- enrollment custome set up id
354 ,rec_prgm.prereq_process_rule_id
355 ,rec_prgm.no_fee_flag
356 ,rec_prgm.vad_invite_allow_flag
357 );
358
359
360 counter:=counter+1;
361 -- Fixed for bug 4749395
362 ELSIF ( l_request_status_code ='REJECTED' OR l_request_status_code='CANCELLED' ) and l_enrollment_type_code IN ('NEW') THEN
363 l_enrq_param_tbl.EXTEND(1);
364 ----dbms_output.PUT_LINE('after extend ');
365 l_enrq_param_tbl(counter) := PV_ENRL_REQ_PARAM
366 (
370 , getInviteHeaderId(p_partner_id ,rec_prgm.program_id , 'INVITE')
367 rec_prgm.program_id
368 ,rec_prgm.program_name
369 ,rec_prgm.citem_version_id
371 --,rec_prgm.invite_header_id
372 ,null
373 ,null
374 ,null -- enrollment request id
375 ,null -- enrollment type code
376 ,null -- enrollment custome set up id
377 ,rec_prgm.prereq_process_rule_id
378 ,rec_prgm.no_fee_flag
379 ,rec_prgm.vad_invite_allow_flag
380 );
381
382 counter:=counter+1;
383
384 ELSIF l_request_status_code ='APPROVED' AND l_enrollment_type_code IN ('NEW','RENEW','UPGRADE') THEN
385 OPEN memb_csr(l_enrl_request_id);
386 FETCH memb_csr INTO l_membership_id,l_membership_status_code;
387 CLOSE memb_csr;
388 IF l_membership_status_code='TERMINATED' THEN
389 l_enrq_param_tbl.EXTEND(1);
390 l_enrq_param_tbl(counter) := PV_ENRL_REQ_PARAM
391 (
392 rec_prgm.program_id
393 ,rec_prgm.program_name
394 ,rec_prgm.citem_version_id
395 , getInviteHeaderId(p_partner_id ,rec_prgm.program_id , 'INVITE')
396 --,rec_prgm.invite_header_id
397 ,to_char(l_membership_id)
398 ,null
399 ,null -- enrollment request id
400 ,null -- enrollment type code
401 ,null -- enrollment custome set up id
402 ,rec_prgm.prereq_process_rule_id
403 ,rec_prgm.no_fee_flag
404 ,rec_prgm.vad_invite_allow_flag
405 );
406
407 counter:=counter+1;
408 END IF;
409 END IF;
410 END IF;
411 END IF; -- end of if , for pre-req evaluation..
412 END LOOP;
413 ELSIF p_isprereq_eval='N' THEN
414 /** --this block will get all the programs that the partner is eligible to join without going
415 -- against the prereq tables. This would be only called incase of invite flow where
416 -- a new partner is created and wants to invite the partner into a program.
417 -- At this time, the list of new programs would be given out from this block
418 -- and the prereq evaluation would be done for the list of programs returned by this block
419 */
420 FOR rec_prgm in prgm_wo_prereq_csr(p_partner_id) LOOP
421 l_eligible_flag:=true;
422 IF p_member_type='SUBSIDIARY' THEN
423 IF rec_prgm.global_mmbr_reqd_flag='Y' THEN
424 l_eligible_flag:=isGlobalEnrolled(rec_prgm.program_id,p_partner_id);
425 END IF;
426 END IF;
427 IF l_eligible_flag=true THEN
428 l_enrq_param_tbl.EXTEND(1);
429 l_enrq_param_tbl(counter) := PV_ENRL_REQ_PARAM (
430 rec_prgm.program_id
431 ,null
432 ,null
433 ,null
434 ,null
435 ,null
436 ,null -- enrollment request id
437 ,null -- enrollment type code
438 ,null -- enrollment custome set up id
439 ,rec_prgm.prereq_process_rule_id
440 ,rec_prgm.no_fee_flag
441 ,rec_prgm.vad_invite_allow_flag
442 );
443
444 counter:=counter+1;
445 END IF;
446 END LOOP;
447 END IF;
448
449 ----dbms_output.PUT_LINE('before for looop ');
450
451 --We'll query l_enrq_param_tbl once all the programs related parameters are populated in l_enrq_param_tbl.
452
453 OPEN x_enrq_param_cur FOR SELECT * FROM TABLE( CAST (l_enrq_param_tbl AS PV_ENRL_REQ_PARAM_TBL)) order by upper(programname) asc;
454
455 -- Check for commit
456 IF FND_API.to_boolean(p_commit) THEN
457 COMMIT;
458 END IF;
462 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
459
460 EXCEPTION
461 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
463 -- Standard call to get message count and if count=1, get the message
464 FND_MSG_PUB.Count_And_Get (
465 p_encoded => FND_API.G_FALSE,
466 p_count => x_msg_count,
467 p_data => x_msg_data
468 );
469
470 WHEN OTHERS THEN
471 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
472 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
473 THEN
474 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
475 END IF;
476 -- Standard call to get message count and if count=1, get the message
477 FND_MSG_PUB.Count_And_Get (
478 p_encoded => FND_API.G_FALSE,
479 p_count => x_msg_count,
480 p_data => x_msg_data
481 );
482 END new_programs;
483
484
485 PROCEDURE renewable_programs
486 (
487 p_api_version_number IN NUMBER
488 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
489 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
490 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
491 ,p_partner_id IN NUMBER
492 ,p_member_type IN VARCHAR2
493 ,p_isprereq_eval IN VARCHAR2 :='Y'
494 ,x_enrq_param_cur OUT NOCOPY enrq_param_ref
495 ,x_return_status OUT NOCOPY VARCHAR2
496 ,x_msg_count OUT NOCOPY NUMBER
497 ,x_msg_data OUT NOCOPY VARCHAR2
498 ) IS
499
500 CURSOR prgm_csr1(ptr_id IN NUMBER) IS
501 SELECT pvppb.program_id program_id
502 ,pvppb.program_name program_name
503 ,pvppb.citem_version_id citem_version_id
504 ,pvppb.global_mmbr_reqd_flag global_mmbr_reqd_flag
505 ,pvppb.prereq_process_rule_id prereq_process_rule_id
506 ,pvppb.no_fee_flag no_fee_flag
507 ,pvppb.vad_invite_allow_flag vad_invite_allow_flag
508 ,memb.membership_id membership_id
509 ,memb.membership_status_code membership_status_code
510 ,memb.original_end_date original_end_date
511 -- ,inv.invite_header_id invite_header_id
512 FROM pv_partner_program_type_b pvpptb
513 ,pv_partner_program_vl pvppb
514 ,pv_pg_memberships memb
515 -- ,pv_pg_invite_headers_b inv
516 WHERE pvppb.program_status_code = 'ACTIVE'
517 AND pvppb.program_level_code ='MEMBERSHIP'
518 AND pvppb.enabled_flag = 'Y'
519 AND nvl(pvppb.allow_enrl_until_date, sysdate) > sysdate-1
520 AND pvppb.program_type_id = pvpptb.program_type_ID
521 AND pvpptb.ACTIVE_FLAG = 'Y'
522 AND pvpptb.enabled_flag = 'Y'
523 --AND inv.partner_id (+) = ptr_id
524 --AND inv.invite_for_program_id (+) = pvppb.program_id
525 -- AND NVL(inv.invite_end_date, sysdate+1) > sysdate
526 --AND inv.invite_type_code(+)='INVITE'
527 AND EXISTS
528 ( SELECT 'X' FROM pv_program_partner_types pvppt
529 WHERE pvppt.partner_type IN
530 (
531 SELECT attr_value
532 FROM pv_enty_attr_values pveav
533 WHERE pveav.enabled_flag = 'Y'
534 AND pveav.latest_flag = 'Y'
535 AND pveav.entity = 'PARTNER'
536 AND pveav.entity_id = ptr_id
537 AND pveav.attribute_id = 3
538 )
539 AND pvpptb.program_type_id = pvppt.program_type_id
540 )
541 AND pvppb.program_id =memb.program_id
542 AND memb.membership_id =
543 ( SELECT max(membership_id)
544 FROM PV_PG_MEMBERSHIPS
545 WHERE program_id=memb.program_id
546 AND partner_id=ptr_id
547 AND MEMBERSHIP_STATUS_CODE IN ('ACTIVE','EXPIRED')
548
549 )
550 /*AND EXISTS -- check for pre-populated cache for pre-req evaluation
551 ( SELECT 1
552 FROM pv_pg_elig_programs elig
553 WHERE elig.program_id = pvppb.program_id
554 AND elig.partner_id = ptr_id
555 )*/
556 ;
557
558 CURSOR notify_rule_csr(program_id NUMBER) IS
559 SELECT decode(send_notif_before_unit, 'PV_MONTHS',add_months(sysdate,send_notif_before_value)
560 ,'PV_WEEKS', sysdate+ send_notif_before_value*7
561 ,'PV_DAYS', sysdate+send_notif_before_value,null) cdate
562
563 FROM pv_ge_notif_rules_b
564 WHERE arc_notif_for_entity_code = 'PRGM'
565 AND notif_for_entity_id = program_id
566 AND notif_type_code = 'PG_MEM_EXP'
567 AND active_flag = 'Y';
568
569 CURSOR enrl_csr(ptr_id NUMBER ,prgm_id NUMBER) IS
570 SELECT enrl_request_id,request_status_code ,enrollment_type_code
571 FROM pv_pg_enrl_requests
572 WHERE enrl_request_id=
573 (
574 SELECT max(enrl_request_id)
575 FROM pv_pg_enrl_requests
576 WHERE partner_id=ptr_id
577 AND program_id=prgm_id
578 AND enrollment_type_code='RENEW'
579 );
580
581
582 CURSOR upgrade_rule_csr(prgm_id NUMBER,ptr_id NUMBER) IS
583 SELECT 'X'
584 FROM pv_pg_enrl_change_rules rules
588 AND active_flag = 'Y'
585 WHERE change_direction_code = 'UPGRADE'
586 AND effective_from_date <= sysdate
587 AND nvl(effective_to_date, sysdate) >= sysdate
589 AND change_from_program_id=prgm_id
590 AND
591 EXISTS
592 ( SELECT enrl_request_id
593 FROM pv_pg_enrl_requests
594 WHERE partner_id=ptr_id
595 AND request_status_code IN ('AWAITING_APPROVAL','APPROVED','INCOMPLETE')
596 AND program_id=rules.change_to_program_id
597 );
598
599 CURSOR memb_csr(enrl_id NUMBER) IS
600 SELECT membership_status_code
601 FROM pv_pg_memberships
602 WHERE enrl_request_id=enrl_id;
603
604 l_enrq_param_tbl PV_ENRL_REQ_PARAM_TBL := PV_ENRL_REQ_PARAM_TBL ();
605 l_api_name CONSTANT VARCHAR2(30) := 'renewable_programs';
606 l_api_version_number CONSTANT NUMBER := 1.0;
607 l_request_status_code VARCHAR2(30);
608 l_membership_status_code VARCHAR2(30);
609 l_enrl_request_id NUMBER;
610 l_membership_id NUMBER;
611 counter NUMBER:=1;
612 --l_flag varchar2(1):='Y';
613 l_upgrade_exists varchar2(1):=null;
614 l_cdate DATE:=null;
615 l_enrollment_type_code VARCHAR2(30);
616 l_user_id NUMBER;
617 l_eligible_flag boolean:=true;
618 isValid boolean:=false;
619 TYPE csr_type IS REF CURSOR;
620 prereq_csr csr_type ;
621 l_query_str1 VARCHAR2(1200);
622
623 l_cache VARCHAR2(1);
624
625 BEGIN
626
627 --Standard call to check for call compatibility.
628 IF NOT FND_API.Compatible_API_Call ( l_api_version_number
629 ,p_api_version_number
630 ,l_api_name
631 ,G_PKG_NAME
632 )
633
634 THEN
635 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
636 END IF;
637
638 -- Initialize message list if p_init_msg_list is set to TRUE.
639 IF FND_API.to_Boolean( p_init_msg_list )
640 THEN
641 FND_MSG_PUB.initialize;
642 END IF;
643
644 -- Debug Message
645 IF (PV_DEBUG_HIGH_ON) THEN
646 PVX_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
647 END IF;
648 -- Initialize API return status to SUCCESS
649 x_return_status := FND_API.G_RET_STS_SUCCESS;
650 --l_query_str1:= 'SELECT ''X'' FROM pv_pg_elig_programs elig WHERE elig.partner_id=' || to_char(p_partner_id) || 'AND elig.program_id =';
651 l_query_str1:= 'SELECT ''X'' FROM pv_pg_elig_programs elig WHERE elig.partner_id=:1' || 'AND elig.program_id =:2';
652 FOR rec_p in prgm_csr1(p_partner_id) LOOP
653 l_eligible_flag:=true;
654 l_cdate:=null ;
655 l_upgrade_exists:=null;
656 l_request_status_code:=null;
657 isValid :=false;
658 l_cache:=null;
659
660
661 OPEN prereq_csr FOR l_query_str1 USING p_partner_id,rec_p.program_id;
662 FETCH prereq_csr INTO l_cache;
663 CLOSE prereq_csr;
664
665 IF l_cache='X' THEN
666
667 --this if clause is to check if the partner is eligible for early renewal
668 IF rec_p.membership_status_code='ACTIVE' THEN
669 OPEN notify_rule_csr(rec_p.program_id);
670 FETCH notify_rule_csr INTO l_cdate;
671 CLOSE notify_rule_csr;
672 IF l_cdate is not null THEN
673 IF rec_p.original_end_date>l_cdate THEN
674 l_eligible_flag:=false;
675 ELSE
676 l_eligible_flag:=true;
677 END IF;
678 ELSE
679 l_eligible_flag:=false;
680 END IF;
681 END IF;
682
683 --if the member type is subsidiary,check whether global has enrolled
684 IF p_member_type='SUBSIDIARY' AND rec_p.membership_status_code='EXPIRED' AND rec_p.global_mmbr_reqd_flag='Y' THEN
685 l_eligible_flag:=isGlobalEnrolled(rec_p.program_id,p_partner_id);
686 END IF;
687
688 IF l_eligible_flag=true THEN
689 OPEN enrl_csr(p_partner_id,rec_p.program_id);
690 FETCH enrl_csr INTO l_enrl_request_id,l_request_status_code,l_enrollment_type_code;
691 CLOSE enrl_csr;
692 IF l_request_status_code IS NULL OR l_request_status_code IN ( 'REJECTED','CANCELLED') THEN
693
694
695 --check whether there is an upgrade request from this program to some other program
696 OPEN upgrade_rule_csr(rec_p.program_id,p_partner_id);
697 FETCH upgrade_rule_csr INTO l_upgrade_exists;
698 CLOSE upgrade_rule_csr;
699
700 IF l_upgrade_exists IS NULL THEN
701 isValid:=true;
702
703 END IF;
704 ELSIF l_request_status_code='APPROVED' THEN
705 OPEN memb_csr(l_enrl_request_id);
706 FETCH memb_csr INTO l_membership_status_code;
707 CLOSE memb_csr;
708 IF l_membership_status_code IN ( 'EXPIRED', 'DOWNGRADED' ) THEN
709 isValid:=true;
710 END IF;
711 END IF;
712 IF isValid=true THEN
713 l_enrq_param_tbl.EXTEND(1);
717 ,rec_p.citem_version_id
714 l_enrq_param_tbl(counter) := PV_ENRL_REQ_PARAM(
715 rec_p.program_id
716 ,rec_p.program_name
718 , getInviteHeaderId(p_partner_id ,rec_p.program_id , 'INVITE')
719 --,rec_p.invite_header_id
720 ,rec_p.membership_id
721 ,null
722 ,null -- enrollment request id
723 ,null -- enrollment type code
724 ,null -- enrollment custome set up id
725 ,rec_p.prereq_process_rule_id
726 ,rec_p.no_fee_flag
727 ,rec_p.vad_invite_allow_flag
728
729 );
730
731
732 counter:=counter+1;
733 END IF;
734 END IF; -- end of if , if eligible
735 END IF; -- end of if for preewquite cache
736 END LOOP;-- END OF FOR LOOP
737
738 -- open ref cursor by querying l_enrq_param_tbl once all the programs related parameters are populated in l_enrq_param_tbl.
739 Open x_enrq_param_cur for select * from TABLE(cast (l_enrq_param_tbl as PV_ENRL_REQ_PARAM_TBL)) order by upper(programname) asc;
740
741 -- Check for commit
742 IF FND_API.to_boolean(p_commit) THEN
743 COMMIT;
744 END IF;
745
746 EXCEPTION
747 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
748 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
749 -- Standard call to get message count and if count=1, get the message
750 FND_MSG_PUB.Count_And_Get (
751 p_encoded => FND_API.G_FALSE,
752 p_count => x_msg_count,
753 p_data => x_msg_data
754 );
755
756 WHEN OTHERS THEN
757 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
758 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
759 THEN
760 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
761 END IF;
762 -- Standard call to get message count and if count=1, get the message
763 FND_MSG_PUB.Count_And_Get (
764 p_encoded => FND_API.G_FALSE,
765 p_count => x_msg_count,
766 p_data => x_msg_data
767 );
768 END renewable_programs;
769
770
771 PROCEDURE upgradable_programs
772 (
773 p_api_version_number IN NUMBER
774 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
775 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
776 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
777 ,p_partner_id IN NUMBER
778 ,p_member_type IN VARCHAR2
779 ,p_isprereq_eval IN VARCHAR2 :='Y'
780 ,x_enrq_param_cur OUT NOCOPY enrq_param_ref
781 ,x_return_status OUT NOCOPY VARCHAR2
782 ,x_msg_count OUT NOCOPY NUMBER
783 ,x_msg_data OUT NOCOPY VARCHAR2
784 ) IS
785
786 /** pick up all programs of the active memberships for that partner.
787 pick up the to_programs for all these programs from upgrade rules table.
788 see whether there is an Active,upgraded membership in memberships table
789 if there is none,display this program.
790 if this TO program exist in memberships table,don't pick it up...
791 else pick it up..
792 also query the prereq tables
793 */
794 CURSOR upgrd_prgm_csr(p_ptr_id NUMBER ) IS
795 SELECT rules.change_to_program_id program_id
796 ,pvppb.program_name program_name
797 ,pvppb.citem_version_id citem_version_id
798 ,memb.membership_id membership_id
799 ,rules.change_from_program_id change_from_program_id
800 ,rules.enrl_change_rule_id enrl_change_rule_id
801 ,pvppb.global_mmbr_reqd_flag global_mmbr_reqd_flag
802 ,pvppb.prereq_process_rule_id prereq_process_rule_id
803 ,pvppb.no_fee_flag no_fee_flag
804 ,pvppb.vad_invite_allow_flag vad_invite_allow_flag
805 FROM pv_pg_enrl_change_rules rules
806 ,pv_pg_memberships memb
807 ,pv_partner_program_vl pvppb
808 ,pv_partner_program_type_b pvpptb
809
810 WHERE pvppb.program_status_code = 'ACTIVE'
811 AND pvppb.program_level_code ='MEMBERSHIP'
812 AND pvppb.enabled_flag = 'Y'
813 AND nvl(pvppb.allow_enrl_until_date, sysdate) > sysdate-1
814 AND pvppb.program_type_id = pvpptb.program_type_ID
815 AND pvpptb.ACTIVE_FLAG = 'Y'
816 AND pvpptb.enabled_flag = 'Y'
817 AND rules.change_from_program_id=memb.program_id
818 AND memb.partner_id=p_ptr_id
819 --AND memb.membership_status_code in ('ACTIVE' , 'UPGRADED')
820 AND memb.program_id= (
821 SELECT memb10.program_id
822 FROM pv_pg_memberships memb10
823 WHERE memb10.membership_id =
824 ( SELECT max(membership_id)
825 FROM pv_pg_memberships memb9
829 AND memb10.membership_status_code in ('ACTIVE' , 'UPGRADED')
826 WHERE memb9.program_id=memb.program_id
827 AND memb9.partner_id=memb.partner_id
828 )
830 )
831
832 AND rules.change_direction_code = 'UPGRADE'
833 AND rules.effective_from_date <= sysdate
834 AND nvl(rules.effective_to_date, sysdate) >= sysdate
835 AND rules.active_flag = 'Y'
836 AND rules.change_to_program_id=pvppb.program_id
837 AND rules.change_to_program_id not in (
838 /*SELECT memb2.program_id
839 FROM pv_pg_memberships memb2
840 WHERE memb2.program_id=rules.change_to_program_id
841 AND memb2.partner_id=memb.partner_id
842 AND memb2.membership_status_code in ('ACTIVE','UPGRADED','EXPIRED')
843 */
844 SELECT memb2.program_id
845 FROM pv_pg_memberships memb2
846 WHERE memb2.membership_id =
847 (
848 SELECT max(membership_id)
849 FROM pv_pg_memberships memb3
850 WHERE memb3.program_id=rules.change_to_program_id
851 AND memb3.partner_id=memb.partner_id
852
853 )
854 AND memb2.membership_status_code in ('ACTIVE','UPGRADED','EXPIRED','FUTURE')
855
856 )
857 AND EXISTS
858 ( SELECT 'X' FROM pv_program_partner_types pvppt
859 WHERE pvppt.partner_type IN
860 (
861 SELECT attr_value
862 FROM pv_enty_attr_values pveav
863 WHERE pveav.enabled_flag = 'Y'
864 AND pveav.latest_flag = 'Y'
865 AND pveav.entity = 'PARTNER'
866 AND pveav.entity_id =p_ptr_id
867 AND pveav.attribute_id = 3
868 )
869 AND pvpptb.program_type_id = pvppt.program_type_id
870 )
871 /* AND EXISTS -- check for pre-populated cache for pre-req evaluation
872 ( SELECT 1
873 FROM pv_pg_elig_programs elig
874 WHERE elig.program_id = pvppb.program_id
875 AND elig.partner_id = p_ptr_id
876 )
877 */
878 order by rules.change_to_program_id desc;
879
880 CURSOR enrl_csr(ptr_id NUMBER ,prgm_id NUMBER) IS
881 SELECT enrq.enrl_request_id,enrq.request_status_code,enrq.enrollment_type_code, memb.membership_status_code
882 FROM pv_pg_enrl_requests enrq
883 , pv_pg_memberships memb
884 WHERE enrq.enrl_request_id=
885 (
886 SELECT max(enrl_request_id)
887 FROM pv_pg_enrl_requests
888 WHERE partner_id=ptr_id
889 AND program_id=prgm_id
890
891 )
892 and enrq.enrl_request_id=memb.enrl_request_id(+);
893
894
895
896 l_enrq_param_tbl PV_ENRL_REQ_PARAM_TBL := PV_ENRL_REQ_PARAM_TBL ();
897 l_api_name CONSTANT VARCHAR2(30) := 'upgradable_programs';
898 l_api_version_number CONSTANT NUMBER := 1.0;
899 l_eligible_flag boolean:=true;
900 l_memb_flag boolean:=true;
901 l_request_status_code VARCHAR2(30):=null;
902 l_membership_status_code VARCHAR2(30);
903 l_enrl_request_id NUMBER;
904 l_membership_id NUMBER;
905 l_enrollment_type_code VARCHAR2(30);
906 l_prevMembrId VARCHAR2(1000);
907 l_upgrdRlId VARCHAR2(1000);
908 counter NUMBER:=0;
909 l_program_id NUMBER:=NULL;
910 TYPE csr_type IS REF CURSOR;
911 prereq_csr csr_type ;
912 l_query_str1 VARCHAR2(1200);
913
914 l_cache VARCHAR2(1);
915
916 BEGIN
917
918 --Standard call to check for call compatibility.
919 IF NOT FND_API.Compatible_API_Call ( l_api_version_number
920 ,p_api_version_number
921 ,l_api_name
922 ,G_PKG_NAME
923 )
924
925 THEN
926 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
927 END IF;
928
929 -- Initialize message list if p_init_msg_list is set to TRUE.
930 IF FND_API.to_Boolean( p_init_msg_list )
931 THEN
932 FND_MSG_PUB.initialize;
933 END IF;
934
935 -- Debug Message
936 IF (PV_DEBUG_HIGH_ON) THEN
937 PVX_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
938 END IF;
939 -- Initialize API return status to SUCCESS
940 x_return_status := FND_API.G_RET_STS_SUCCESS;
941 --l_query_str1:= 'SELECT ''X'' FROM pv_pg_elig_programs elig WHERE elig.partner_id=' || to_char(p_partner_id) || 'AND elig.program_id =';
942 l_query_str1:= 'SELECT ''X'' FROM pv_pg_elig_programs elig WHERE elig.partner_id=:1' || 'AND elig.program_id =:2';
943 FOR rec_prgm in upgrd_prgm_csr(p_partner_id) LOOP
944 l_eligible_flag:=true;
945 l_cache:=null;
946 l_memb_flag :=true;
947
948 OPEN prereq_csr FOR l_query_str1 USING p_partner_id,rec_prgm.program_id ;
949 FETCH prereq_csr INTO l_cache;
950 CLOSE prereq_csr;
951
952 IF l_cache='X' THEN
956
953 IF p_member_type='SUBSIDIARY' AND rec_prgm.global_mmbr_reqd_flag='Y' THEN
954 l_eligible_flag:=isGlobalEnrolled(rec_prgm.program_id,p_partner_id);
955 END IF;
957 IF l_eligible_flag=true THEN
958 OPEN enrl_csr(p_partner_id,rec_prgm.program_id);
959 FETCH enrl_csr INTO l_enrl_request_id,l_request_status_code,l_enrollment_type_code,l_membership_status_code;
960 CLOSE enrl_csr;
961 IF l_request_status_code IS NULL OR l_request_status_code IN ( 'REJECTED','CANCELLED', 'APPROVED' ) THEN
962 -- the logic here is , the query could fetch you the same program more than once
963 -- if there is multiple upgrade path. so intially l_program_id is null
964 -- in the next loop, if the program_id is same, then the previous membership id and
965 -- upgrade rule id is concatenated. if not , the l_enrq_param_tbl is extended and initialised
966 IF l_enrollment_type_code ='APPROVED' THEN
967 IF l_membership_status_code ='ACTIVE' THEN
968 l_memb_flag :=false;
969 ELSE
970 l_memb_flag :=true;
971 END IF;
972 END IF;
973
974 IF l_memb_flag =true THEN
975
976 IF l_program_id=rec_prgm.program_id THEN
977 l_prevMembrId:=l_prevMembrId || ':' || to_char(rec_prgm.membership_id);
978 l_upgrdRlId:=l_upgrdRlId || ':' || to_char(rec_prgm.enrl_change_rule_id);
979 l_enrq_param_tbl(counter) := PV_ENRL_REQ_PARAM(
980 rec_prgm.program_id
981 ,rec_prgm.program_name
982 ,rec_prgm.citem_version_id
983 , getInviteHeaderId(p_partner_id ,rec_prgm.program_id , 'UPGRADE')
984 --,rec_prgm.invite_header_id
985 ,l_prevMembrId
986 , l_upgrdRlId
987 ,null -- enrollment request id
988 ,null -- enrollment type code
989 ,null -- enrollment custome set up id
990 ,rec_prgm.prereq_process_rule_id
991 ,rec_prgm.no_fee_flag
992 ,rec_prgm.vad_invite_allow_flag
993
994 );
995
996 ELSE
997 counter:=counter+1;
998 l_prevMembrId:=to_char(rec_prgm.membership_id);
999 l_upgrdRlId:=to_char(rec_prgm.enrl_change_rule_id);
1000 l_enrq_param_tbl.EXTEND(1);
1001 l_enrq_param_tbl(counter) := PV_ENRL_REQ_PARAM(
1002 rec_prgm.program_id
1003 ,rec_prgm.program_name
1004 ,rec_prgm.citem_version_id
1005 ,getInviteHeaderId(p_partner_id ,rec_prgm.program_id , 'UPGRADE')
1006 ,to_char(rec_prgm.membership_id)
1007 ,to_char(rec_prgm.enrl_change_rule_id)
1008 ,null -- enrollment request id
1009 ,null -- enrollment type code
1010 ,null -- enrollment custome set up id
1011 ,rec_prgm.prereq_process_rule_id
1012 ,rec_prgm.no_fee_flag
1013 ,rec_prgm.vad_invite_allow_flag
1014 );
1015 l_program_id:=rec_prgm.program_id;
1016 END IF;
1017 END IF;
1018 END IF;
1019 END IF;--end of if ,if the program is eligible
1020 l_request_status_code:=null;
1021 END IF; -- end of if for preewquite cache
1022 END LOOP;
1023
1024 --We'll query l_enrq_param_tbl once all the programs related parameters are populated in l_enrq_param_tbl.
1025 Open x_enrq_param_cur for select * from TABLE(cast (l_enrq_param_tbl as PV_ENRL_REQ_PARAM_TBL)) order by upper(programname) asc;
1026
1027 -- Check for commit
1028 IF FND_API.to_boolean(p_commit) THEN
1029 COMMIT;
1030 END IF;
1031
1032 EXCEPTION
1033 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1034 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1035 -- Standard call to get message count and if count=1, get the message
1036 FND_MSG_PUB.Count_And_Get (
1037 p_encoded => FND_API.G_FALSE,
1038 p_count => x_msg_count,
1042 WHEN OTHERS THEN
1039 p_data => x_msg_data
1040 );
1041
1043 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1044 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1045 THEN
1046 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1047 END IF;
1048 -- Standard call to get message count and if count=1, get the message
1049 FND_MSG_PUB.Count_And_Get (
1050 p_encoded => FND_API.G_FALSE,
1051 p_count => x_msg_count,
1052 p_data => x_msg_data
1053 );
1054 END upgradable_programs;
1055
1056
1057 PROCEDURE incomplete_programs
1058 (
1059 p_api_version_number IN NUMBER
1060 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
1061 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
1062 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
1063 ,p_partner_id IN NUMBER
1064 ,p_member_type IN VARCHAR2
1065 ,p_isprereq_eval IN VARCHAR2 :='Y'
1066 ,x_enrq_param_cur OUT NOCOPY enrq_param_ref
1067 ,x_return_status OUT NOCOPY VARCHAR2
1068 ,x_msg_count OUT NOCOPY NUMBER
1069 ,x_msg_data OUT NOCOPY VARCHAR2
1070 ) IS
1071
1072 CURSOR incomplet_csr(p_ptr_id NUMBER ) IS
1073 SELECT enrq.enrl_request_id
1074 ,enrq.enrollment_type_code
1075 ,enrq.program_id
1076 ,enrq.custom_setup_id
1077 ,pvppb.program_name
1078 ,pvppb.citem_version_id
1079 ,pvppb.global_mmbr_reqd_flag
1080 ,pvppb.prereq_process_rule_id prereq_process_rule_id
1081 ,pvppb.no_fee_flag no_fee_flag
1082 ,pvppb.vad_invite_allow_flag vad_invite_allow_flag
1083 FROM pv_partner_program_type_b pvpptb
1084 ,pv_partner_program_vl pvppb
1085 ,pv_pg_enrl_requests enrq
1086 WHERE pvppb.program_status_code = 'ACTIVE'
1087 AND pvppb.program_level_code ='MEMBERSHIP'
1088 AND pvppb.enabled_flag = 'Y'
1089 AND nvl(pvppb.allow_enrl_until_date, sysdate) > sysdate-1
1090 AND pvppb.program_type_id = pvpptb.program_type_ID AND pvpptb.ACTIVE_FLAG = 'Y'
1091 AND pvpptb.enabled_flag = 'Y'
1092 AND EXISTS
1093 ( SELECT 'X'
1094 FROM pv_program_partner_types pvppt
1095 WHERE pvppt.partner_type
1096 IN ( SELECT attr_value
1097 FROM pv_enty_attr_values pveav
1098 WHERE pveav.enabled_flag = 'Y'
1099 AND pveav.latest_flag = 'Y'
1100 AND pveav.entity = 'PARTNER'
1101 AND pveav.entity_id =p_ptr_id
1102 AND pveav.attribute_id = 3
1103 )
1104 AND pvpptb.program_type_id = pvppt.program_type_id
1105 )
1106 AND enrq.enrl_request_id =
1107 ( SELECT max(enrl_request_id)
1108 FROM pv_pg_enrl_requests
1109 WHERE partner_id= p_ptr_id
1110 AND program_id=pvppb.PROGRAM_ID
1111 AND request_status_code='INCOMPLETE'
1112 )
1113 /*AND EXISTS -- check for pre-populated cache for pre-req evaluation
1114 ( SELECT 1
1115 FROM pv_pg_elig_programs elig
1116 WHERE elig.program_id = pvppb.program_id
1117 AND elig.partner_id = p_ptr_id
1118 )
1119 */
1120 ;
1121
1122 l_enrq_param_tbl PV_ENRL_REQ_PARAM_TBL := PV_ENRL_REQ_PARAM_TBL ();
1123 l_api_name CONSTANT VARCHAR2(30) := 'incomplete_programs';
1124 l_api_version_number CONSTANT NUMBER := 1.0;
1125 counter NUMBER:=0;
1126 TYPE csr_type IS REF CURSOR;
1127 prereq_csr csr_type ;
1128 l_query_str1 VARCHAR2(1200);
1129
1130 l_cache VARCHAR2(1);
1131
1132 BEGIN
1133
1134 --Standard call to check for call compatibility.
1135 IF NOT FND_API.Compatible_API_Call ( l_api_version_number
1136 ,p_api_version_number
1137 ,l_api_name
1138 ,G_PKG_NAME
1139 )
1140
1141 THEN
1142 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1143 END IF;
1144
1145 -- Initialize message list if p_init_msg_list is set to TRUE.
1146 IF FND_API.to_Boolean( p_init_msg_list )
1147 THEN
1148 FND_MSG_PUB.initialize;
1149 END IF;
1150
1151 -- Debug Message
1152 IF (PV_DEBUG_HIGH_ON) THEN
1153 PVX_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
1154 END IF;
1155 -- Initialize API return status to SUCCESS
1156 x_return_status := FND_API.G_RET_STS_SUCCESS;
1157
1158 -- logic to get the list of new programs that partner is eligible to see goes here.
1159 --Add each of program, add the following parameter values to the following table
1160 --l_query_str1:= 'SELECT ''X'' FROM pv_pg_elig_programs elig WHERE elig.partner_id=' || to_char(p_partner_id) || 'AND elig.program_id =';
1161 l_query_str1:= 'SELECT ''X'' FROM pv_pg_elig_programs elig WHERE elig.partner_id=:1' || 'AND elig.program_id =:2';
1162 FOR rec_prgm in incomplet_csr(p_partner_id) LOOP
1166 FETCH prereq_csr INTO l_cache;
1163 l_cache:=null;
1164
1165 OPEN prereq_csr FOR l_query_str1 USING p_partner_id,rec_prgm.program_id ;
1167 CLOSE prereq_csr;
1168
1169 IF l_cache='X' THEN
1170 counter:=counter+1;
1171 l_enrq_param_tbl.EXTEND(1);
1172 l_enrq_param_tbl(counter) := PV_ENRL_REQ_PARAM( rec_prgm.program_id
1173 ,rec_prgm.program_name
1174 ,rec_prgm.citem_version_id
1175 ,null -- need to clarify business logic here
1176 ,null -- previous membership id
1177 ,null -- upgrade rule id
1178 ,rec_prgm.enrl_request_id -- enrollment request id
1179 ,rec_prgm.enrollment_type_code -- enrollment type code
1180 ,rec_prgm.custom_setup_id -- enrollment custome set up id
1181 ,rec_prgm.prereq_process_rule_id
1182 ,rec_prgm.no_fee_flag
1183 ,rec_prgm.vad_invite_allow_flag
1184
1185 );
1186 END IF; -- end of if for preewquite cache
1187 END LOOP;
1188
1189 --We'll query l_enrq_param_tbl once all the programs related parameters are populated in l_enrq_param_tbl.
1190 Open x_enrq_param_cur for select * from TABLE(cast (l_enrq_param_tbl as PV_ENRL_REQ_PARAM_TBL)) order by upper(programname) asc;
1191
1192 -- Check for commit
1193 IF FND_API.to_boolean(p_commit) THEN
1194 COMMIT;
1195 END IF;
1196
1197 EXCEPTION
1198 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1199 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1200 -- Standard call to get message count and if count=1, get the message
1201 FND_MSG_PUB.Count_And_Get (
1202 p_encoded => FND_API.G_FALSE,
1203 p_count => x_msg_count,
1204 p_data => x_msg_data
1205 );
1206
1207 WHEN OTHERS THEN
1208 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1209 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1210 THEN
1211 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1212 END IF;
1213 -- Standard call to get message count and if count=1, get the message
1214 FND_MSG_PUB.Count_And_Get (
1215 p_encoded => FND_API.G_FALSE,
1216 p_count => x_msg_count,
1217 p_data => x_msg_data
1218 );
1219 END incomplete_programs;
1220
1221
1222 PROCEDURE newAndInCompletePrograms
1223 (
1224 p_api_version_number IN NUMBER
1225 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
1226 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
1227 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
1228 ,p_partner_id IN NUMBER
1229 ,p_member_type IN VARCHAR2
1230 ,p_isprereq_eval IN VARCHAR2 :='Y'
1231 ,x_enrq_param_cur OUT NOCOPY enrq_param_ref
1232 ,x_return_status OUT NOCOPY VARCHAR2
1233 ,x_msg_count OUT NOCOPY NUMBER
1234 ,x_msg_data OUT NOCOPY VARCHAR2
1235 ) IS
1236 l_api_name CONSTANT VARCHAR2(30) := 'newAndInCompletePrograms';
1237 l_api_version_number CONSTANT NUMBER := 1.0;
1238 l_new_enrq_param_ref enrq_param_ref;
1239 l_inc_enrq_param_ref enrq_param_ref;
1240 --l_newinc_enrq_param_ref enrq_param_ref;
1241 l_nienrq_param_tbl PV_ENRL_REQ_PARAM_TBL := PV_ENRL_REQ_PARAM_TBL ();
1242 l_programId NUMBER;
1243 l_programName VARCHAR2(60);
1244 l_citemVersionId NUMBER;
1245 l_inviteHeaderId NUMBER;
1246 l_prevMembrId VARCHAR2(1000) ;
1247 l_upgrdRlId VARCHAR2(1000);
1248 l_enrlId NUMBER;
1249 l_enrlTypeCode VARCHAR2(30);
1250 l_enrlCustSetupId NUMBER;
1251 l_prereqProcessRuleId NUMBER;
1252 l_no_fee VARCHAR2(1);
1253 l_vad_invite VARCHAR2(1);
1254 counter NUMBER:=1;
1255 BEGIN
1256 --Standard call to check for call compatibility.
1257 IF NOT FND_API.Compatible_API_Call ( l_api_version_number
1258 ,p_api_version_number
1259 ,l_api_name
1260 ,G_PKG_NAME
1261 )
1262
1263 THEN
1264 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1265 END IF;
1266
1267 -- Initialize message list if p_init_msg_list is set to TRUE.
1268 IF FND_API.to_Boolean( p_init_msg_list )
1269 THEN
1270 FND_MSG_PUB.initialize;
1271 END IF;
1272
1273 -- Debug Message
1274 IF (PV_DEBUG_HIGH_ON) THEN
1275 PVX_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
1276 END IF;
1277 -- Initialize API return status to SUCCESS
1278 x_return_status := FND_API.G_RET_STS_SUCCESS;
1279 new_programs
1280 (
1284 ,p_validation_level => p_validation_level
1281 p_api_version_number => p_api_version_number
1282 ,p_init_msg_list => p_init_msg_list
1283 ,p_commit => p_commit
1285 ,p_partner_id => p_partner_id
1286 ,p_member_type => p_member_type
1287 ,p_isprereq_eval => p_isprereq_eval
1288 ,x_enrq_param_cur => l_new_enrq_param_ref
1289 ,x_return_status => x_return_status
1290 ,x_msg_count => x_msg_count
1291 ,x_msg_data => x_msg_data
1292 );
1293 LOOP
1294 -- you need to fetch all of it even though you don't neeed it.
1295 FETCH l_new_enrq_param_ref into l_programId, l_programName, l_citemVersionId, l_inviteHeaderId, l_prevMembrId , l_upgrdRlId ,
1296 l_enrlId , l_enrlTypeCode, l_enrlCustSetupId,l_prereqProcessRuleId, l_no_fee, l_vad_invite;
1297
1298 EXIT WHEN l_new_enrq_param_ref%NOTFOUND;
1299
1300 IF l_programId IS NOT NULL THEN
1301 l_nienrq_param_tbl.EXTEND(1);
1302 l_nienrq_param_tbl(counter) := PV_ENRL_REQ_PARAM( l_programId
1303 ,l_programName
1304 , l_citemVersionId
1305 , l_inviteHeaderId
1306 ,l_prevMembrId
1307 ,l_upgrdRlId
1308 ,l_enrlId -- enrollment request id
1309 ,l_enrlTypeCode -- enrollment type code
1310 ,l_enrlCustSetupId-- enrollment custome set up id
1311 ,l_prereqProcessRuleId
1312 ,l_no_fee
1313 ,l_vad_invite
1314
1315 );
1316 counter:=counter+1;
1317 END IF;
1318
1319
1320
1321 END LOOP;
1322 close l_new_enrq_param_ref ;
1323
1324 incomplete_programs
1325 (
1326 p_api_version_number => p_api_version_number
1327 ,p_init_msg_list => p_init_msg_list
1328 ,p_commit => p_commit
1329 ,p_validation_level => p_validation_level
1330 ,p_partner_id => p_partner_id
1331 ,p_member_type => p_member_type
1332 ,p_isprereq_eval => p_isprereq_eval
1333 ,x_enrq_param_cur => l_inc_enrq_param_ref
1334 ,x_return_status => x_return_status
1335 ,x_msg_count => x_msg_count
1336 ,x_msg_data => x_msg_data
1337 );
1338
1339 LOOP
1340 -- you need to fetch all of it even though you don't neeed it.
1341 FETCH l_inc_enrq_param_ref into l_programId, l_programName, l_citemVersionId, l_inviteHeaderId, l_prevMembrId , l_upgrdRlId ,
1342 l_enrlId , l_enrlTypeCode, l_enrlCustSetupId,l_prereqProcessRuleId, l_no_fee, l_vad_invite;
1343
1344 EXIT WHEN l_inc_enrq_param_ref%NOTFOUND;
1345
1346 IF l_programId IS NOT NULL THEN
1347 l_nienrq_param_tbl.EXTEND(1);
1348 l_nienrq_param_tbl(counter) := PV_ENRL_REQ_PARAM( l_programId
1349 ,l_programName
1350 , l_citemVersionId
1351 , l_inviteHeaderId
1352 ,l_prevMembrId
1353 ,l_upgrdRlId
1354 ,l_enrlId -- enrollment request id
1355 ,l_enrlTypeCode -- enrollment type code
1356 ,l_enrlCustSetupId-- enrollment custome set up id
1360
1357 ,l_prereqProcessRuleId
1358 ,l_no_fee
1359 ,l_vad_invite
1361 );
1362 counter:=counter+1;
1363 END IF;
1364
1365
1366
1367 END LOOP;
1368 close l_inc_enrq_param_ref ;
1369
1370
1371 OPEN x_enrq_param_cur FOR SELECT * FROM TABLE( CAST (l_nienrq_param_tbl AS PV_ENRL_REQ_PARAM_TBL)) order by upper(programname) asc;
1372
1373
1374 -- Check for commit
1375 IF FND_API.to_boolean(p_commit) THEN
1376 COMMIT;
1377 END IF;
1378
1379 EXCEPTION
1380 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1381 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1382 -- Standard call to get message count and if count=1, get the message
1383 FND_MSG_PUB.Count_And_Get (
1384 p_encoded => FND_API.G_FALSE,
1385 p_count => x_msg_count,
1386 p_data => x_msg_data
1387 );
1388
1389 WHEN OTHERS THEN
1390 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1391 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1392 THEN
1393 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1394 END IF;
1395 -- Standard call to get message count and if count=1, get the message
1396 FND_MSG_PUB.Count_And_Get (
1397 p_encoded => FND_API.G_FALSE,
1398 p_count => x_msg_count,
1399 p_data => x_msg_data
1400 );
1401 END newAndInCompletePrograms;
1402
1403 PROCEDURE isPartnerEligible
1404 (
1405 p_api_version_number IN NUMBER
1406 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
1407 , p_commit IN VARCHAR2 := FND_API.G_FALSE
1408 , p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
1409 , p_partner_id IN NUMBER
1410 , p_from_program_id IN NUMBER
1411 , p_to_program_id IN NUMBER
1412 , p_enrq_type IN VARCHAR -- permitted values here are 'NEW', 'UPGRADE' for 11.5.10.
1413 , x_elig_flag OUT NOCOPY VARCHAR2 -- PASS 'Y' if eligible, PASS 'N' if not eligible
1414 , x_return_status OUT NOCOPY VARCHAR2
1415 , x_msg_count OUT NOCOPY NUMBER
1416 , x_msg_data OUT NOCOPY VARCHAR2
1417 )
1418 IS
1419 l_api_name CONSTANT VARCHAR2(30) := 'isPartnerEligible';
1420 l_api_version_number CONSTANT NUMBER := 1.0;
1421 l_global_mmbr_reqd_flag VARCHAR2(1);
1422 l_eligible_flag boolean:=true;
1423 l_member_type VARCHAR2(30);
1424 l_request_status_code VARCHAR2(30);
1425 l_enrollment_type_code VARCHAR2(30);
1426
1427 CURSOR memb_type( p_ptr_id NUMBER) IS
1428 SELECT attr_value
1429 FROM pv_enty_attr_values
1430 WHERE entity='PARTNER'
1431 AND entity_id=p_ptr_id
1432 AND attribute_id=6
1433 AND latest_flag='Y';
1434
1435 CURSOR isElig( ptnr_id NUMBER, p_prgm_id NUMBER ) IS
1436 SELECT 'Y' , global_mmbr_reqd_flag
1437 FROM pv_partner_program_type_b pvpptb
1438 , pv_partner_program_b pvppb
1439 WHERE program_id=p_prgm_id
1440 AND nvl(allow_enrl_until_date, sysdate) > sysdate-1
1441 AND pvppb.program_type_id = pvpptb.program_type_ID
1442 AND pvpptb.ACTIVE_FLAG = 'Y'
1443 AND pvpptb.enabled_flag = 'Y'
1444 AND program_id NOT IN
1445 (
1446 SELECT rules.change_to_program_id
1447 FROM pv_pg_enrl_change_rules rules
1448 WHERE change_direction_code = 'UPGRADE'
1449 AND effective_from_date <= sysdate
1450 AND nvl(effective_to_date, sysdate) >= sysdate
1451 AND active_flag = 'Y'
1452 )
1453 AND EXISTS
1454 (
1455 SELECT 'X'
1456 FROM pv_program_partner_types pvppt
1457 WHERE pvppt.partner_type IN
1458 (
1459 SELECT attr_value
1460 FROM pv_enty_attr_values pveav
1461 WHERE pveav.enabled_flag = 'Y'
1462 AND pveav.latest_flag = 'Y'
1463 AND pveav.entity = 'PARTNER'
1464 AND pveav.entity_id = ptnr_id
1465 AND pveav.attribute_id = 3
1466 )
1467 AND pvpptb.program_type_id = pvppt.program_type_id
1468 )
1469 /*AND EXISTS -- check for pre-populated cache for pre-req evaluation
1470 (
1471 SELECT 1
1472 FROM pv_pg_elig_programs elig
1473 WHERE elig.program_id = pvppb.program_id
1474 AND elig.partner_id = ptnr_id
1475 )*/
1476 ;
1477
1478 CURSOR c_upgrade_csr( p_ptr_id IN NUMBER, p_from_id IN NUMBER, p_to_id IN NUMBER) IS
1479 SELECT 'Y'
1480 , global_mmbr_reqd_flag
1481 FROM pv_pg_enrl_change_rules rules
1482 , pv_pg_memberships memb
1483 , pv_partner_program_vl pvppb
1484 , pv_partner_program_type_b pvpptb
1485 WHERE pvppb.program_status_code = 'ACTIVE'
1486 AND pvppb.program_level_code ='MEMBERSHIP'
1487 AND pvppb.enabled_flag = 'Y'
1488 AND pvppb.program_id=p_to_id
1489 AND nvl(pvppb.allow_enrl_until_date, sysdate) > sysdate-1
1490 AND pvppb.program_type_id = pvpptb.program_type_ID
1491 AND pvpptb.ACTIVE_FLAG = 'Y'
1492 AND pvpptb.enabled_flag = 'Y'
1493 AND memb.program_id=p_from_id
1494 AND memb.partner_id=p_ptr_id
1495 --AND memb.membership_status_code in ('ACTIVE' , 'UPGRADED')
1496 AND memb.program_id= (
1497 SELECT memb10.program_id
1498 FROM pv_pg_memberships memb10
1499 WHERE memb10.membership_id =
1500 ( SELECT max(membership_id)
1501 FROM pv_pg_memberships memb9
1502 WHERE memb9.program_id=memb.program_id
1503 AND memb9.partner_id=memb.partner_id
1504 )
1505 AND memb10.membership_status_code in ('ACTIVE' , 'UPGRADED')
1506 )
1507
1508 AND rules.change_from_program_id =p_from_id
1509 AND rules.change_direction_code = 'UPGRADE'
1510 AND rules.effective_from_date <= sysdate
1511 AND nvl(rules.effective_to_date, sysdate) >= sysdate
1512 AND rules.active_flag = 'Y'
1513 AND rules.change_to_program_id=p_to_id
1514 AND rules.change_to_program_id not in
1515 (
1516 /* SELECT memb2.program_id
1517 FROM pv_pg_memberships memb2
1518 WHERE memb2.program_id=p_to_id
1519 AND memb2.partner_id=p_ptr_id
1520 AND memb2.membership_status_code in ('ACTIVE', 'UPGRADED')
1521 */
1522 SELECT memb2.program_id
1523 FROM pv_pg_memberships memb2
1524 WHERE memb2.membership_id =
1525 (
1526 SELECT max(membership_id)
1527 FROM pv_pg_memberships memb3
1528 WHERE memb3.program_id=rules.change_to_program_id
1529 AND memb3.partner_id=memb.partner_id
1530 )
1531 AND memb2.membership_status_code in ('ACTIVE','UPGRADED','EXPIRED')
1532
1533 )
1534 AND EXISTS
1535 (
1536 SELECT 'X'
1537 FROM pv_program_partner_types pvppt
1538 WHERE pvppt.partner_type IN
1539 (
1540 SELECT attr_value
1541 FROM pv_enty_attr_values pveav
1542 WHERE pveav.enabled_flag = 'Y'
1543 AND pveav.latest_flag = 'Y'
1544 AND pveav.entity = 'PARTNER'
1545 AND pveav.entity_id = p_ptr_id
1546 AND pveav.attribute_id = 3
1547 )
1548 AND pvpptb.program_type_id = pvppt.program_type_id
1549 )
1550 /* AND EXISTS -- check for pre-populated cache for pre-req evaluation
1551 (
1552 SELECT 1
1553 FROM pv_pg_elig_programs elig
1554 WHERE elig.program_id = pvppb.program_id
1555 AND elig.partner_id = p_ptr_id
1556 )
1557 */
1558 ;
1559
1560 CURSOR enrl_csr( ptr_id IN NUMBER, prgm_id IN NUMBER ) IS
1561 SELECT request_status_code,enrollment_type_code
1562 FROM pv_pg_enrl_requests
1563 WHERE enrl_request_id=
1564 (
1565 SELECT max(enrl_request_id)
1566 FROM pv_pg_enrl_requests
1567 WHERE partner_id=ptr_id
1568 AND program_id=prgm_id
1569
1570 );
1571
1572 TYPE csr_type IS REF CURSOR;
1573 prereq_csr csr_type ;
1574 l_query_str1 VARCHAR2(1200);
1575
1576 l_cache VARCHAR2(1):=null;
1577
1578 BEGIN
1579 SAVEPOINT isPartnerEligible;
1580 --Standard call to check for call compatibility.
1581 IF NOT FND_API.Compatible_API_Call ( l_api_version_number
1582 ,p_api_version_number
1583 ,l_api_name
1584 ,G_PKG_NAME
1585 )
1586
1587 THEN
1588 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1589 END IF;
1590
1591 -- Initialize message list if p_init_msg_list is set to TRUE.
1592 IF FND_API.to_Boolean( p_init_msg_list )
1593 THEN
1594 FND_MSG_PUB.initialize;
1595 END IF;
1596
1597 -- Debug Message
1598 IF (PV_DEBUG_HIGH_ON) THEN
1599 PVX_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
1600 END IF;
1601 -- Initialize API return status to SUCCESS
1602 x_return_status := FND_API.G_RET_STS_SUCCESS;
1603 x_elig_flag := 'N';
1604
1605 --l_query_str1:= 'SELECT ''X'' FROM pv_pg_elig_programs elig WHERE elig.partner_id=' || to_char(p_partner_id) || 'AND elig.program_id='|| to_char( p_to_program_id);
1606 l_query_str1:= 'SELECT ''X'' FROM pv_pg_elig_programs elig WHERE elig.partner_id=:1' || 'AND elig.program_id =:2';
1607
1608 OPEN prereq_csr FOR l_query_str1 USING p_partner_id, p_to_program_id;
1609 FETCH prereq_csr INTO l_cache;
1610 CLOSE prereq_csr;
1611
1612 IF l_cache='X' THEN
1613 IF p_enrq_type = 'NEW' THEN
1614
1615 OPEN isElig( p_partner_id , p_to_program_id ) ;
1616 FETCH isElig INTO x_elig_flag,l_global_mmbr_reqd_flag;
1617 CLOSE isElig;
1618
1619 IF x_elig_flag= 'Y' THEN
1620 OPEN memb_type( p_partner_id );
1621 FETCH memb_type INTO l_member_type;
1622 CLOSE memb_type;
1623 IF l_member_type='SUBSIDIARY' THEN
1624 IF l_global_mmbr_reqd_flag ='Y' THEN
1625 l_eligible_flag:=isGlobalEnrolled(p_to_program_id ,p_partner_id);
1626 END IF;
1627 IF l_eligible_flag=false THEN
1628 x_elig_flag := 'N' ;
1629 END IF;
1630 END If;
1631 ELSE
1632 x_elig_flag := 'N' ;
1633 END IF;
1634 ELSIF p_enrq_type = 'UPGRADE' THEN
1635 -- start: if its upgrade
1636 OPEN c_upgrade_csr( p_partner_id , p_from_program_id, p_to_program_id ) ;
1637 FETCH c_upgrade_csr INTO x_elig_flag,l_global_mmbr_reqd_flag;
1638 CLOSE c_upgrade_csr;
1639
1640 IF x_elig_flag= 'Y' THEN
1641 OPEN enrl_csr( p_partner_id, p_to_program_id );
1642 FETCH enrl_csr INTO l_request_status_code,l_enrollment_type_code;
1643 CLOSE enrl_csr;
1644 --check whether there is an already an APPROVED enrollment request
1645 -- Fixed for bug 5116650. Only checked for AWAITING_APPROVAL by taking out the APPROVED status
1646 IF ( l_request_status_code IS NOT NULL AND l_request_status_code IN ( 'AWAITING_APPROVAL' ) ) THEN
1647 x_elig_flag := 'N';
1648
1649 END IF;
1650
1651 -- check member type and if its subsidiary , check whether global has enrolled.
1652 IF x_elig_flag= 'Y' THEN
1653
1654 OPEN memb_type( p_partner_id );
1655 FETCH memb_type INTO l_member_type;
1656 CLOSE memb_type;
1657 IF l_member_type='SUBSIDIARY' THEN
1658
1659 IF l_global_mmbr_reqd_flag ='Y' THEN
1660 l_eligible_flag:=isGlobalEnrolled(p_to_program_id ,p_partner_id);
1661 END IF;
1662 IF l_eligible_flag=false THEN
1663 x_elig_flag := 'N' ;
1664 END IF;
1665 END IF;
1666 -- end of member type check
1667 END IF;
1668 ELSE
1669 x_elig_flag := 'N' ;
1670 END IF;
1671 END IF; -- end : if its upgrade
1672 ELSE
1673 x_elig_flag := 'N';
1674 END IF; -- end of if for preewquite cache
1675 -- Debug Message
1676 IF (PV_DEBUG_HIGH_ON) THEN
1677 PVX_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
1678 END IF;
1679 -- Check for commit
1680 IF FND_API.to_boolean(p_commit) THEN
1681 COMMIT;
1682 END IF;
1683
1684 EXCEPTION
1685 WHEN FND_API.G_EXC_ERROR THEN
1686 ROLLBACK TO isPartnerEligible;
1687 x_return_status := FND_API.G_RET_STS_ERROR;
1688 -- Standard call to get message count and if count=1, get the message
1689 FND_MSG_PUB.Count_And_Get (
1690 p_encoded => FND_API.G_FALSE,
1691 p_count => x_msg_count,
1692 p_data => x_msg_data
1693 );
1694 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1695 ROLLBACK TO isPartnerEligible;
1696 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1697 -- Standard call to get message count and if count=1, get the message
1698 FND_MSG_PUB.Count_And_Get (
1699 p_encoded => FND_API.G_FALSE,
1700 p_count => x_msg_count,
1701 p_data => x_msg_data
1702 );
1703 WHEN OTHERS THEN
1704 ROLLBACK TO isPartnerEligible;
1705 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1706 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1707 THEN
1708 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1709 END IF;
1710 -- Standard call to get message count and if count=1, get the message
1711 FND_MSG_PUB.Count_And_Get (
1712 p_encoded => FND_API.G_FALSE,
1713 p_count => x_msg_count,
1714 p_data => x_msg_data
1715 );
1716
1717 END isPartnerEligible;
1718
1719 END Pv_Enrq_Bins_PVT;
[Home] [Help]