[Home] [Help]
PACKAGE BODY: APPS.PV_PRGM_APPROVAL_PVT
Source
1 PACKAGE BODY pv_prgm_approval_pvt AS
2 /* $Header: pvxvpapb.pls 120.13 2006/08/10 18:16:03 speddu ship $*/
3
4
5
6 -- Start of Comments
7 --
8 -- NAME
9 -- pv_prgm_approval_pvt
10 --
11 -- PURPOSE
12 -- This package contains all approval related procedures for Partner Programs
13 --
14 -- HISTORY
15 -- 05/23/2002 pukken CREATION
16 -- 06/23/2002 pukken added start workflow for child programs
17 -- 12/04/2002 SVEERAVE added Process_errored_requests that will
18 -- be called from conc. request.
19 -- 02/25/2003 pukken added Code to fix bug 2821087 regarding showing partner name
20 -- 02/25/2003 pukken Fixed Bug 2821062
21 -- 05/20/2003 pukken Fixed Bug 2999737 and Bug 2999721
22 -- 10/24/2003 pukken Made calls to the new workflow api and also made changes to create_history call
23 -- 11/26/2003 pukken Added validation to call welcome notification in default memb api only
24 -- if atleast one primary user exist
25 -- 02/26/2004 pukken Modified procedure getstart_end_date to fix bug 3454657
26 -- 10/05/2005 pukken Took out the reference to contract_id in checkcontract_status()
27 -- 12/19/2005 ktsao Fixed for bug 4868295 - performance issue(SQL ID 15006635). Added "and apdt.approval_type='CONCEPT'" to improve performance.
28
29 -- NOTE :
30 -- Copyright (c) 2002 Oracle Corporation Redwood Shores, California, USA
31 -- All rights reserved.
32
33 g_pkg_name CONSTANT VARCHAR2 (30) := 'pv_prgm_approval_pvt';
34 g_file_name CONSTANT VARCHAR2 (15) := 'pvxvpapb.pls';
35 g_program_mode CONSTANT VARCHAR2 (15) := 'WORKFLOW';
36 g_isApproverInList boolean:=false;
37 g_approver_response VARCHAR2(30):=NULL;
38
39 PV_DEBUG_HIGH_ON CONSTANT boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);
40 PV_DEBUG_LOW_ON CONSTANT boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
41 PV_DEBUG_MEDIUM_ON CONSTANT boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM);
42
43 PROCEDURE Write_Log(p_which number, p_mssg varchar2) IS
44 BEGIN
45 FND_FILE.put(p_which, p_mssg);
46 FND_FILE.NEW_LINE(p_which, 1);
47 END Write_Log;
48
49 PROCEDURE CheckApprInTempApprTable
50 ( p_enrl_request_id IN NUMBER
51 , p_approver_id IN NUMBER
52 , x_entity_approver_id OUT NOCOPY NUMBER
53 , x_objNo OUT NOCOPY NUMBER
54 , x_approval_status_code OUT NOCOPY VARCHAR2
55 ) IS
56 CURSOR temp_appr_csr(enrl_id NUMBER ,apprid NUMBER )IS
57 SELECT entity_approver_id, object_version_number,approval_status_code
58 FROM pv_ge_temp_approvers
59 WHERE APPR_FOR_ENTITY_ID =enrl_id
60 AND ARC_APPR_FOR_ENTITY_CODE='ENRQ'
61 AND APPROVER_ID=DECODE( APPROVER_TYPE_CODE,'PERSON',apprid,'USER',FND_GLOBAL.USER_ID,null );
62
63
64 BEGIN
65 OPEN temp_appr_csr( p_enrl_request_id,p_approver_id );
66 FETCH temp_appr_csr INTO x_entity_approver_id, x_objNo,x_approval_status_code ;
67 CLOSE temp_appr_csr;
68 EXCEPTION
69 WHEN OTHERS THEN
70 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
71 END CheckApprInTempApprTable;
72
73 --------------------------------------------------------------------------
74 -- FUNCTION
75 -- isPartnerType
76 --
77 -- PURPOSE
78 -- Checks whether the partner is of partner type passed in
79 -- IN
80 -- enrollment_request_id NUMBER
81 -- partner_type VARCHAR
82 -- OUT
83 -- ame_util.booleanAttributeTrue if exists
84 -- ame_util.booleanAttributeFalse if not exists
85 -- USED BY
86 -- Program Approval API, and Activate API.
87 -- HISTORY
88 -- 12/13/2002 CREATION
89 --------------------------------------------------------------------------
90
91 FUNCTION isPartnerType(p_partner_id IN NUMBER,p_partner_type IN VARCHAR2)
92 RETURN VARCHAR2 IS
93 CURSOR partnerType_cur(p_part_id number,p_ptr_type varchar2) IS
94 SELECT 'Y'
95 FROM DUAL
96 WHERE EXISTS (SELECT 1
97 FROM pv_enty_attr_values
98 WHERE entity = 'PARTNER'
99 AND attribute_id = 3
100 AND latest_flag = 'Y'
101 AND entity_id = p_part_id
102 AND attr_value= p_ptr_type
103 AND attr_value_extn = 'Y'
104 );
105
106
107
108 l_exists_flag VARCHAR2(1) := 'N';
109
110 BEGIN
111 OPEN partnerType_cur(p_partner_id,p_partner_type);
112 FETCH partnerType_cur INTO l_exists_flag;
113 CLOSE partnerType_cur;
114 IF l_exists_flag='Y' THEN
115 RETURN ame_util.booleanAttributeTrue;
116 ELSE
117 RETURN ame_util.booleanAttributeFalse;
118 END IF;
119
120 EXCEPTION
121 WHEN NO_DATA_FOUND THEN
122 RETURN ame_util.booleanAttributeFalse;
123 WHEN OTHERS THEN
124 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
125 END isPartnerType;
126
127
128 FUNCTION getUserId(p_user_name IN VARCHAR2)
129 RETURN NUMBER IS
130 CURSOR user_csr(uname VARCHAR2) IS
131 SELECT user_id
132 FROM fnd_user
133 WHERE user_name=uname;
134 l_user_id NUMBER;
135 BEGIN
136 OPEN user_csr(p_user_name);
137 FETCH user_csr INTO l_user_id;
138 CLOSE user_csr;
139 RETURN l_user_id;
140 EXCEPTION
141 WHEN OTHERS THEN
142 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
143 END getUserId;
144 --------------------------------------------------------------------------
145 -- FUNCTION
146 -- isnumber
147 --
148 -- PURPOSE
149 -- Simple function to check whether value entered is a number
150 -- returns null if value entered is not a number
151 -- IN
152 -- l_value VARCHAR2
153
154 -- OUT
155 -- l_number NUMBER or null if value entered is not a number
156 -- HISTORY
157 -- 18-APR-2003 pukken CREATION
158 --------------------------------------------------------------------------
159
160 FUNCTION isnumber (
161 l_value VARCHAR2
162 )
163 RETURN NUMBER IS
164 l_number NUMBER;
165 BEGIN
166 BEGIN
167 l_number := l_value;
168 EXCEPTION
169 WHEN OTHERS THEN
170 RETURN NULL;
171 END;
172
173 RETURN l_number;
174 END isnumber;
175
176
177 FUNCTION getenddate(p_program_id in number,p_previous_enr_end_date in DATE) return DATE is
178 l_program_end_date DATE;
179 l_membership_end_date DATE;
180 l_start_date DATE;
181 cursor rec_cur(p_prgm_id number,start_date date) is
182 select program_end_date,
183 decode( membership_period_unit
184 ,'DAY',start_date+membership_valid_period
185 ,'MONTH',add_months(start_date,membership_valid_period)
186 ,'YEAR',add_months(start_date,12*membership_valid_period)
187 ,null
188 ) membership_end_date
189 from pv_partner_program_b
190 where program_id=p_prgm_id;
191
192
193 BEGIN
194
195
196 OPEN rec_cur(p_program_id,p_previous_enr_end_date);
197 FETCH rec_cur into l_program_end_date,l_membership_end_date;
198 if rec_cur%found THEN
199 if l_membership_end_date is NULL then--this should never happen.. clarify the business logic.
200 l_membership_end_date:=l_program_end_date;
201 end if;
202 end if;
203 CLOSE rec_cur;
204 return l_membership_end_date;
205
206 EXCEPTION
207
208 WHEN OTHERS THEN
209 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
210
211 END getenddate;
212
213
214 FUNCTION getGlobalenddate(p_partner_id in number,p_dependent_program_id in number,p_enrollment_type in VARCHAR,p_start_date in DATE) return DATE is
215 CURSOR enrtype_new_csr(g_ptr_id NUMBER,dependent_id NUMBER) IS
216 SELECT min(original_end_date)
217 FROM pv_pg_memberships
218 WHERE partner_id=g_ptr_id
219 AND membership_status_code='ACTIVE'
220 AND program_id IN
221 (
222 SELECT distinct(change_to_program_id)
223 FROM pv_pg_enrl_change_rules
224 START WITH change_from_program_id=dependent_id
225 AND change_direction_code='UPGRADE'
226 AND ACTIVE_FLAG='Y'
227 AND nvl(EFFECTIVE_TO_DATE,sysdate+1)>=sysdate
228 CONNECT BY change_from_program_id=PRIOR change_to_program_id
229 AND change_direction_code='UPGRADE'
230 AND ACTIVE_FLAG='Y'
231 AND nvl(EFFECTIVE_TO_DATE,sysdate+1)>=sysdate
232 UNION
233 SELECT dependent_id
234 FROM
235 DUAL
236 );
237
238 CURSOR enrtype_renew_csr(g_ptr_id NUMBER,dependent_id NUMBER) IS
239 SELECT min(original_end_date)
240 FROM pv_pg_memberships
241 WHERE partner_id=g_ptr_id
242 AND membership_status_code='FUTURE'
243 AND program_id IN
244 (
245 SELECT distinct(change_to_program_id)
246 FROM pv_pg_enrl_change_rules
247 START WITH change_from_program_id=dependent_id
248 AND change_direction_code='UPGRADE'
249 AND ACTIVE_FLAG='Y'
250 AND nvl(EFFECTIVE_TO_DATE,sysdate+1)>=sysdate
251 CONNECT BY change_from_program_id=PRIOR change_to_program_id
252 AND change_direction_code='UPGRADE'
253 AND ACTIVE_FLAG='Y'
254 AND nvl(EFFECTIVE_TO_DATE,sysdate+1)>=sysdate
255 UNION
256 SELECT dependent_id
257 FROM
258 DUAL
259 );
260
261 CURSOR get_global_csr( sub_ptr_id NUMBER) IS
262 SELECT glob.partner_id
263 FROM pv_partner_profiles glob
264 , hz_relationships rel
265 , pv_partner_profiles sub
266 WHERE glob.partner_party_id= rel.object_id
267 AND rel.subject_id=sub.partner_party_id
268 AND sub.partner_id=sub_ptr_id
269 AND relationship_type='PARTNER_HIERARCHY'
270 AND rel.status='A'
271 AND NVL(rel.start_date, SYSDATE) <= SYSDATE
272 AND NVL(rel.end_date, SYSDATE) >= SYSDATE ;
273
274 l_global_ptr_id NUMBER;
275 l_end_date DATE:=null;
276
277 BEGIN
278 --get the global partner_id
279 OPEN get_global_csr(p_partner_id);
280 FETCH get_global_csr INTO l_global_ptr_id;
281 CLOSE get_global_csr;
282 /**if enrollment type is new,upgrade or if its renewal after membership expiry,
283 get the min(original_end_date)of global membership in the dependent program or any other program
284 in the upgrade path
285 if its early renewal,check whether global has a future membership in any program in the
286 upgrade path
287 */
288 IF l_global_ptr_id IS NOT NULL THEN
289 IF ( p_start_date>sysdate AND p_enrollment_type ='RENEW' ) THEN
290 --its early renewal
291 OPEN enrtype_renew_csr(l_global_ptr_id,p_dependent_program_id );
292 FETCH enrtype_renew_csr INTO l_end_date;
293 CLOSE enrtype_renew_csr;
294 END IF;
295
296 IF l_end_date IS NULL THEN
297 OPEN enrtype_new_csr(l_global_ptr_id,p_dependent_program_id );
298 FETCH enrtype_new_csr INTO l_end_date;
299 CLOSE enrtype_new_csr;
300 END IF;
301 END IF;
302
303 IF l_end_date IS NULL THEN
304 l_end_date:=sysdate;
305 END IF;
306 RETURN l_end_date;
307
308 EXCEPTION
309 WHEN OTHERS THEN
310 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
311
312 END getGlobalenddate;
313
314 PROCEDURE getstart_and_end_date( p_api_version_number IN NUMBER
315 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
316 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
317 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
318 ,enrl_request_id IN NUMBER
319 ,x_start_date OUT NOCOPY DATE
320 ,x_end_date OUT NOCOPY DATE
321 ,x_return_status OUT NOCOPY VARCHAR2
322 ,x_msg_count OUT NOCOPY NUMBER
323 ,x_msg_data OUT NOCOPY VARCHAR2
324 ) IS
325 l_prev_end_date DATE :=null;
326 l_prev_membership_id NUMBER;
327 l_tentative_start_date DATE:=null;
328 l_tentative_end_date DATE:=null;
329 l_request_status_code VARCHAR2(30);
330 p_enrollment_type VARCHAR2(30);
331 p_program_id NUMBER;
332 l_memb_start_date DATE:=null;
333 l_memb_end_date DATE:=null;
334 l_api_name CONSTANT VARCHAR2(30) := 'getstart_and_end_date';
335 l_partner_id NUMBER;
336 l_dependent_program_id NUMBER;
337
338 l_memb_type VARCHAR2(30);
339 CURSOR prev_memb_date_cur(p_enrl_req_id number) IS
340 SELECT nvl(actual_end_date,original_end_date) prev_end_date
341 FROM pv_pg_memberships memb,pv_pg_enrq_init_sources eni
342 WHERE eni.prev_membership_id=memb.membership_id
343 AND eni.enrl_request_id=p_enrl_req_id;
344
345 CURSOR enr_requests_dtl_cur (p_enrl_req_id number) IS
346 SELECT enrollment_type_code,program_id,tentative_start_date,tentative_end_date,request_status_code,partner_id,dependent_program_id
347 FROM pv_pg_enrl_requests
348 WHERE enrl_request_id=p_enrl_req_id;
349
350 CURSOR memb_type_cur(p_ptnr_id IN NUMBER) IS
351 SELECT attr_value
352 FROM pv_enty_attr_values
353 WHERE entity_id=p_ptnr_id
354 AND attribute_id=6
355 AND latest_flag='Y'
356 AND entity='PARTNER';
357
358 CURSOR appr_enrl_req_csr( p_enrl_req_id number ) IS
359 SELECT start_date start_date
360 , nvl(actual_end_date,original_end_date) end_date
361 FROM pv_pg_memberships memb
362 WHERE enrl_request_id=p_enrl_req_id;
363
364 BEGIN
365 x_return_status := FND_API.G_RET_STS_SUCCESS;
366 OPEN enr_requests_dtl_cur(enrl_request_id);
367 FETCH enr_requests_dtl_cur
368 INTO p_enrollment_type
369 , p_program_id
370 , x_start_date
371 , x_end_date
372 , l_request_status_code
373 , l_partner_id
374 , l_dependent_program_id ;
375 CLOSE enr_requests_dtl_cur ;
376
377 --if membership is aleady created,get the start and end date from memberships table
378 OPEN appr_enrl_req_csr( enrl_request_id );
379 FETCH appr_enrl_req_csr INTO x_start_date,x_end_date;
380 CLOSE appr_enrl_req_csr;
381
382 IF( x_start_date is NULL OR x_end_date is NULL ) THEN
383
384 IF p_enrollment_type in ('NEW','UPGRADE','DOWNGRADE') THEN
385 x_start_date:=sysdate;
386 ELSIF p_enrollment_type='RENEW' THEN
387 OPEN prev_memb_date_cur(enrl_request_id);
388 FETCH prev_memb_date_cur INTO l_prev_end_date;
389 CLOSE prev_memb_date_cur;
390 IF l_prev_end_date<sysdate THEN
391 x_start_date:=sysdate;
392 ELSE
393 x_start_date:=l_prev_end_date;
394 END IF;
395 END IF;
396 IF x_end_date IS NULL THEN
397 x_end_date :=getenddate(p_program_id,x_start_date);
398 END IF;
399 -- check whether the partner is Subsidiary and also check the dependent program id.
400 -- if dependent program id has a value it means that the partner enrolled is because of global's
401 -- membership in this program or any other global membership in the upgrade hierarchy.
402 -- we should not check the global membership required flag for the enrolling program, instead the dependent program id.
403 OPEN memb_type_cur(l_partner_id);
404 FETCH memb_type_cur INTO l_memb_type;
405 CLOSE memb_type_cur;
406 IF l_memb_type='SUBSIDIARY' THEN
407 IF l_dependent_program_id IS NOT NULL THEN
408 --get the membership end_date from global partner
409 x_end_date :=getGlobalenddate(l_partner_id,l_dependent_program_id,p_enrollment_type, x_start_date);
410 --this should never happen.
411 IF x_start_date>x_end_date THEN
412 x_start_date:=x_end_date;
413 END IF;
414 END IF;
415 END IF;
416 END IF; --end of if else, if enrollment request is not approved.
417
418 EXCEPTION
419 WHEN OTHERS THEN
420 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
421 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)THEN
422 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
423 END IF;
424 -- Standard call to get message count and if count=1, get the message
425 FND_MSG_PUB.Count_And_Get (
426 p_encoded => FND_API.G_FALSE
427 ,p_count => x_msg_count
428 ,p_data => x_msg_data
429 );
430
431 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
432 END getstart_and_end_date;
433
434 FUNCTION iscontract_exists(p_program_id IN number) RETURN boolean IS
435 l_temp varchar2(1);
436 isrecord boolean:=false ;
437 CURSOR rec_cur(p_prgm_id number) IS
438 SELECT 'X'
439 FROM dual
440 WHERE EXISTS
441 ( SELECT 'X'
442 FROM pv_program_contracts
443 WHERE program_id = p_prgm_id
444 );
445
446 BEGIN
447 OPEN rec_cur(p_program_id);
448 FETCH rec_cur INTO l_temp;
449 IF rec_cur%FOUND THEN
450 isrecord:=true;
451 END IF;
452 CLOSE rec_cur;
453 RETURN isrecord;
454
455 EXCEPTION
456
457 WHEN OTHERS THEN
458 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
459
460 END iscontract_exists;
461
462 FUNCTION ispayment_exists(p_program_id IN number) RETURN boolean IS
463 no_fee varchar2(1);
464 CURSOR rec_cur(p_prgm_id number) IS
465 SELECT no_fee_flag
466 FROM pv_partner_program_b
467 WHERE program_id = p_prgm_id;
468
469
470 BEGIN
471 OPEN rec_cur(p_program_id);
472 FETCH rec_cur into no_fee;
473 CLOSE rec_cur;
474 IF no_fee IS NOT NULL AND no_fee='N' THEN
475 RETURN TRUE;
476 ELSE
477 RETURN FALSE;
478 END IF;
479
480
481 EXCEPTION
482
483 WHEN OTHERS THEN
484 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
485
486 END ispayment_exists;
487
488
489 FUNCTION getCustomSetupID(p_program_id IN number) RETURN number IS
490 l_customSetupID number:=null;
491 l_any_contract boolean;
492 l_any_payment boolean;
493
494 BEGIN
495 l_any_contract:=isContract_Exists(p_program_id);
496 l_any_payment:=isPayment_Exists(p_program_id);
497 IF (l_any_contract) THEN
498
499 --with contract, with payment
500 IF (l_any_payment) THEN
501 l_customSetupID := 7004;
502 ELSE --with contract, no payment
503 l_customSetupID := 7006;
504 END IF;
505
506 ELSE
507 --no contract, with payment
508 IF (l_any_payment) THEN
509 l_customSetupID := 7005;
510 ELSE --no contract, no payment
511 l_customSetupID := 7007;
512 END IF;
513
514 END IF;
515
516 return l_customSetupID;
517
518 EXCEPTION
519
520 WHEN OTHERS THEN
521 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
522
523 END getCustomSetupID;
524
525
526
527 FUNCTION check_pending_default(p_enrollment_req_id in number) return boolean is
528 l_temp varchar2(1);
529 isrecord boolean:=false ;
530 cursor rec_cur(p_enrl_req_id number) is
531 select 'X' from dual where exists (
532 select entity_approver_id from pv_ge_temp_approvers
533 where appr_for_entity_id=p_enrl_req_id
534 and approval_status_code='PENDING_DEFAULT');
535
536 BEGIN
537
538 OPEN rec_cur(p_enrollment_req_id);
539 FETCH rec_cur into l_temp;
540 if rec_cur%found THEN
541
542 isrecord:=true;
543 end if;
544 CLOSE rec_cur;
545 return isrecord;
546
547 EXCEPTION
548
549 WHEN OTHERS THEN
550 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
551
552 END check_pending_default;
553
554
555 FUNCTION check_record_exists(p_enrollment_req_id in number) return boolean is
556 l_temp varchar2(1);
557 isrecord boolean:=false ;
558 cursor rec_cur(p_enrl_req_id number) is
559 select 'X' from dual where exists
560 ( select entity_approver_id from pv_ge_temp_approvers where appr_for_entity_id=p_enrl_req_id);
561
562 BEGIN
563 OPEN rec_cur(p_enrollment_req_id);
564 FETCH rec_cur into l_temp;
565 if rec_cur%found THEN
566 isrecord:=true;
567 end if;
568 CLOSE rec_cur;
569 return isrecord;
570
571 EXCEPTION
572
573 WHEN OTHERS THEN
574 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
575
576 END check_record_exists;
577
578
579 FUNCTION isApproverInList (p_enrollment_req_id in number,p_approver_id in number) return boolean is
580
581 l_isApproverInList boolean:=false;
582 x_nextApproversOut ame_util.approversTable2;
583 xitemIndexesOut ame_util.idList;
584 xitemClassesOut ame_util.stringList;
585 xitemIdsOut ame_util.stringList;
586 xitemSourcesOut ame_util.longStringList;
587 x_approvalProcessCompleteYNOut VARCHAR2(100);
588 currApprRec ame_util.approverRecord2;
589
590 BEGIN
591 -- get all the approver list and loop till you find the matching
592 -- and set the flag to true if you find any.
593 ----DBMS_OUTPUT.PUT_LINE('before get all approvers');
594
595 /** Following is required as we expect AME to return their new statuses. Bug # 4879218 **/
596 ame_util2.detailedApprovalStatusFlagYN := ame_util.booleanTrue;
597
598 ame_api2.getAllApprovers1
599 ( applicationIdIn =>691,
600 transactionTypeIn => 'ENRQ',
601 transactionIdIn => p_enrollment_req_id,
602 approvalProcessCompleteYNOut => x_approvalProcessCompleteYNOut,
603 approversOut => x_nextApproversOut,
604 itemIndexesOut => xitemIndexesOut,
605 itemClassesOut => xitemClassesOut,
606 itemIdsOut => xitemIdsOut,
607 itemSourcesOut => xitemSourcesOut
608
609 );
610 FOR i IN 1..x_nextApproversOut.COUNT LOOP
611 currApprRec := x_nextApproversOut(i);
612 IF p_approver_id=currApprRec.orig_system_id THEN
613 ----DBMS_OUTPUT.PUT_LINE('inside if');
614 l_isApproverInList:=true;
615 exit;
616 END IF;
617 END LOOP;
618
619 return l_isApproverInList;
620
621 EXCEPTION
622
623 WHEN OTHERS THEN
624 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
625
626 END isApproverInList;
627
628
629
630
631 FUNCTION check_pending_approval (p_enrollment_req_id in number) return boolean is
632 l_temp varchar2(1);
633 isPendingApproval boolean:=false;
634 cursor rec_cur(p_enrl_req_id number) is
635 select 'X' from dual where exists (
636 select entity_approver_id from pv_ge_temp_approvers
637 where appr_for_entity_id=p_enrl_req_id
638 and approval_status_code in ('PENDING_APPROVAL'));
639 BEGIN
640 OPEN rec_cur(p_enrollment_req_id);
641 FETCH rec_cur into l_temp;
642 if rec_cur%found THEN
643 isPendingApproval:=true;
644 end if;
645 CLOSE rec_cur;
646 return isPendingApproval;
647
648 EXCEPTION
649
650 WHEN OTHERS THEN
651 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
652
653 END check_pending_approval;
654
655
656
657 FUNCTION checkcontract_status (p_enrollment_req_id in number) return boolean is
658 l_temp varchar2(30);
659 isApprovable boolean:=false;
660 CURSOR rec_cur(p_enrl_req_id number) IS
661 SELECT contract_status_code
662 FROM pv_pg_enrl_requests
663 WHERE enrl_request_id=p_enrl_req_id;
664
665 BEGIN
666 OPEN rec_cur(p_enrollment_req_id);
667 FETCH rec_cur into l_temp;
668 IF (l_temp='SIGNED' or l_temp='NOT_SIGNED') THEN
669 isApprovable:=true;
670 END IF;
671 CLOSE rec_cur;
672 return isApprovable;
673
674 EXCEPTION
675
676 WHEN OTHERS THEN
677 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
678
679 END checkcontract_status;
680
681
682
683 FUNCTION checklist_status(p_enrollment_req_id in number) return boolean is
684 l_temp varchar2(1);
685
686 isChecked boolean:=false;
687 CURSOR rec_cur(p_enrl_req_id number) IS
688 SELECT prgm.allow_enrl_wout_chklst_flag
689 FROM pv_pg_enrl_requests enrq, pv_partner_program_b prgm
690 WHERE enrq.program_id=prgm.program_id
691 AND enrl_request_id=p_enrl_req_id;
692
693 CURSOR checklistresponse_cur(p_enrl_req_id number) IS
694 SELECT 'X' from dual
695 where EXISTS
696 ( SELECT checklist_item_id
697 FROM pv_ge_chklst_responses
698 WHERE response_for_entity_id = p_enrl_req_id
699 AND RESPONSE_FLAG='N'
700 );
701
702
703 BEGIN
704 OPEN rec_cur(p_enrollment_req_id);
705 FETCH rec_cur into l_temp;
706 CLOSE rec_cur;
707 IF l_temp='N' THEN
708 OPEN checklistresponse_cur(p_enrollment_req_id);
709 FETCH checklistresponse_cur into l_temp;
710 IF checklistresponse_cur%found THEN
711 isChecked:=false;
712 ELSE
713 isChecked:=true;
714 END IF;
715 CLOSE checklistresponse_cur;
716 ELSE
717 isChecked:=true;
718 END IF;
719
720 return isChecked;
721
722 EXCEPTION
723
724 WHEN OTHERS THEN
725 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
726
727 END checklist_status;
728
729 -- Fixed for bug 4868295
730 FUNCTION isApproverExists (p_program_type_id in number) return boolean is
731 l_temp varchar2(1);
732 isavailable boolean:=false ;
733 cursor app_cur(p_prgm_type_id varchar) is
734 select 'X' from dual where exists
735 (select approver_id from ams_approvers appr,ams_approval_details apdt
736 where nvl(appr.start_date_active,sysdate)<=sysdate
737 and nvl(appr.end_date_active,sysdate)>=sysdate
738 and appr.ams_approval_detail_id =apdt.approval_detail_id
739 and apdt.approval_object_type=p_prgm_type_id
740 and apdt.approval_object='PRGT'
741 and apdt.approval_type='CONCEPT'
742 and nvl(apdt.active_flag,'Y') = 'Y'
743 and nvl(appr.active_flag,'Y')='Y'
744 );
745 BEGIN
746 OPEN app_cur(to_char(p_program_type_id));
747 FETCH app_cur into l_temp;
748 if app_cur%found THEN
749 isavailable:=true;
750 end if;
751 CLOSE app_cur;
752 return isavailable;
753
754 EXCEPTION
755
756 WHEN OTHERS THEN
757 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
758
759 END isApproverExists;
760
761
762 FUNCTION isParentApproved (p_parent_program_id in number) return boolean is
763
764 l_parent_program_status varchar2(30);
765 isApproved boolean:=false ;
766 cursor parentprogramstatus_cur(p_parent_prgm_id number) is
767 select PROGRAM_STATUS_CODE from pv_partner_program_b where program_id=p_parent_prgm_id and ENABLED_FLAG='Y';
768 BEGIN
769 OPEN parentprogramstatus_cur(p_parent_program_id);
770 FETCH parentprogramstatus_cur into l_parent_program_status;
771 if parentprogramstatus_cur%found THEN
772 if l_parent_program_status in ('APPROVED','ACTIVE') THEN
773 isApproved:=true;
774 end if;
775 end if;
776 CLOSE parentprogramstatus_cur;
777 return isApproved;
778
779 EXCEPTION
780
781 WHEN OTHERS THEN
782 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
783
784 END isParentApproved;
785 --------------------------------------------------------------------------
786 -- PROCEDURE
787 -- Notify_requestor_FYI
788 --
789 -- PURPOSE
790 -- Generate the FYI Document for display in messages, either
791 -- text or html
792 -- IN
793 -- document_id - Item Key
794 -- display_type - either 'text/plain' or 'text/html'
795 -- document - document buffer
796 -- document_type - type of document buffer created, either 'text/plain'
797 -- or 'text/html'
798 -- OUT
799 -- USED BY
800 -- - Oracle Partner Programs Generic Apporval
801 -- HISTORY
802 -- 05/22/2002 pukken CREATION
803
804 PROCEDURE notify_requestor_fyi (
805 document_id IN VARCHAR2,
806 display_type IN VARCHAR2,
807 document IN OUT NOCOPY VARCHAR2,
808 document_type IN OUT NOCOPY VARCHAR2
809 ) IS
810 l_api_name VARCHAR2 (61)
811 := g_pkg_name
812 || 'Notify_Requestor_FYI';
813 l_program_id NUMBER;
814
815 l_program_name VARCHAR2 (200);
816 l_hyphen_pos1 NUMBER;
817 l_fyi_notification VARCHAR2 (10000);
818 l_activity_type VARCHAR2 (30);
819 l_item_type VARCHAR2 (100);
820 l_item_key VARCHAR2 (100);
821 l_approval_type VARCHAR2 (30);
822 l_approver VARCHAR2 (200);
823 l_note VARCHAR2 (3000);
824 l_string VARCHAR2 (1000);
825 l_string1 VARCHAR2 (2500);
826 l_start_date DATE;
827 l_end_date DATE;
828 l_owner_name VARCHAR2 (300);
829 l_level_meaning VARCHAR2 (150);
830 l_program_description VARCHAR2 (240);
831 l_company_name VARCHAR2 (360);
832 l_requester VARCHAR2 (30);
833 l_string2 VARCHAR2 (2500);
834
835
836 CURSOR c_program_rec (p_program_id IN NUMBER) IS
837 SELECT PROGRAM_NAME,MEANING,PROGRAM_START_DATE,PROGRAM_END_DATE,SOURCE_NAME,PROGRAM_DESCRIPTION,SOURCE_BUSINESS_GRP_NAME
838 FROM PV_PARTNER_PROGRAM_VL ,JTF_RS_RESOURCE_EXTNS,FND_LOOKUP_VALUES_VL
839 WHERE PROGRAM_ID =p_program_id
840 AND PROGRAM_OWNER_RESOURCE_ID =RESOURCE_ID
841 AND LOOKUP_CODE=PROGRAM_LEVEL_CODE
842 AND LOOKUP_TYPE='PV_PROGRAM_LEVEL';
843
844 BEGIN
845 ams_utility_pvt.debug_message (
846 l_api_name
847 || 'Entering'
848 || 'document id '
849 || document_id
850 );
851 document_type := 'text/plain';
852 -- parse document_id for the ':' dividing item type name from item key value
853 -- document_id value will take the form <ITEMTYPE>:<ITEMKEY> starting with
854 -- release 2.5 version of this demo
855 l_hyphen_pos1 := INSTR (document_id, ':');
856 l_item_type :=
857 SUBSTR (document_id, 1, l_hyphen_pos1
858 - 1);
859 l_item_key := SUBSTR (document_id, l_hyphen_pos1
860 + 1);
861 l_activity_type :=
862 wf_engine.getitemattrtext (
863 itemtype=> l_item_type,
864 itemkey=> l_item_key,
865 aname => 'AMS_ACTIVITY_TYPE'
866 );
867 l_program_id :=
868 wf_engine.getitemattrtext (
869 itemtype=> l_item_type,
870 itemkey=> l_item_key,
871 aname => 'AMS_ACTIVITY_ID'
872 );
873
874 l_note :=
875 wf_engine.getitemattrtext (
876 itemtype=> l_item_type,
877 itemkey=> l_item_key,
878 aname => 'AMS_NOTES_FROM_REQUESTOR'
879 );
880 l_approver :=
881 wf_engine.getitemattrtext (
882 itemtype=> l_item_type,
883 itemkey=> l_item_key,
884 aname => 'AMS_APPROVER_DISPLAY_NAME'
885 );
886 OPEN c_program_rec (l_program_id);
887 FETCH c_program_rec INTO l_program_name,l_level_meaning,l_start_date,l_end_date,l_owner_name,l_program_description, l_company_name;
888 CLOSE c_program_rec;
889
890 fnd_message.set_name ('PV', 'PV_WF_NTF_REQUESTER_FYI_SUB');
891 fnd_message.set_token ('PROGRAM_NAME', l_program_name, FALSE);
892
893 l_string := SUBSTR(fnd_message.get,1,1000);
894
895 fnd_message.set_name ('PV', 'PV_WF_NTF_PROGRAM_REQ_INFO');
896 fnd_message.set_token ('COMPANY_NAME', l_company_name, FALSE);
897 fnd_message.set_token ('PROGRAM_NAME', l_program_name, FALSE);
898 fnd_message.set_token ('PROGRAM_LEVEL', l_level_meaning, FALSE);
899 fnd_message.set_token ('START_DATE', l_start_date, FALSE);
900 fnd_message.set_token ('END_DATE', l_end_date, FALSE);
901 fnd_message.set_token ('OWNER', l_owner_name, FALSE);
902 fnd_message.set_token ('DESCRIPTION', l_program_description, FALSE);
903 l_string1 := SUBSTR(FND_MESSAGE.Get,1,2500);
904
905
906 l_fyi_notification := SUBSTR(l_string
907 || fnd_global.local_chr (10)
908 || l_string1
909 || fnd_global.local_chr (10)
910 || l_string2,1,10000);
911 document := document
912 || l_fyi_notification;
913 document_type := 'text/plain';
914 RETURN;
915
916 EXCEPTION
917 WHEN OTHERS THEN
918 wf_core.context (
919 'AMSGAPP',
920 'Notify_requestor_FYI',
921 l_item_type,
922 l_item_key
923 );
924 RAISE;
925 END notify_requestor_fyi;
926
927
928 --------------------------------------------------------------------------
929 -- PROCEDURE
930 -- Notify_requestor_of Approval
931 --
932 -- PURPOSE
933 -- Generate the Approval Document for display in messages, either
934 -- text or html
935 -- IN
936 -- document_id - Item Key
937 -- display_type - either 'text/plain' or 'text/html'
938 -- document - document buffer
939 -- document_type - type of document buffer created, either 'text/plain'
940 -- or 'text/html'
941 -- OUT
942 -- USED BY
943 -- - Oracle MArketing Generic Apporval
944 -- HISTORY
945 -- 03/15/2001 pukken CREATION
946 ----------------------------------------------------------------------------
947
948 PROCEDURE notify_requestor_of_approval (
949 document_id IN VARCHAR2,
950 display_type IN VARCHAR2,
951 document IN OUT NOCOPY VARCHAR2,
952 document_type IN OUT NOCOPY VARCHAR2
953 ) IS
954 l_api_name VARCHAR2 (61)
955 := g_pkg_name
956 || 'Notify_Requestor_of_approval' ;
957 l_program_id NUMBER;
958
959 l_program_name VARCHAR2 (200);
960 l_hyphen_pos1 NUMBER;
961 l_appr_notification VARCHAR2 (10000);
962 l_activity_type VARCHAR2 (30);
963 l_item_type VARCHAR2 (100);
964 l_item_key VARCHAR2 (100);
965 l_approval_type VARCHAR2 (30);
966 l_approver VARCHAR2 (200);
967 l_note VARCHAR2 (3000);
968 l_approver_notes VARCHAR2 (3000);
969 l_string VARCHAR2 (1000);
970 l_string1 VARCHAR2 (2500);
971 l_start_date DATE;
972 l_end_date DATE;
973 l_owner_name VARCHAR2 (300);
974 l_level_meaning VARCHAR2 (150);
975 l_program_description VARCHAR2 (240);
976 l_company_name VARCHAR2 (360);
977 l_requester VARCHAR2 (30);
978 l_string2 VARCHAR2 (2500);
979
980
981 CURSOR c_program_rec (p_program_id IN NUMBER) IS
982 SELECT PROGRAM_NAME,MEANING,PROGRAM_START_DATE,PROGRAM_END_DATE,SOURCE_NAME,PROGRAM_DESCRIPTION,SOURCE_BUSINESS_GRP_NAME
983 FROM PV_PARTNER_PROGRAM_VL ,JTF_RS_RESOURCE_EXTNS,FND_LOOKUP_VALUES_VL
984 WHERE PROGRAM_ID =p_program_id
985 AND PROGRAM_OWNER_RESOURCE_ID =RESOURCE_ID
986 AND LOOKUP_CODE=PROGRAM_LEVEL_CODE
987 AND LOOKUP_TYPE='PV_PROGRAM_LEVEL';
988
989 BEGIN
990 ams_utility_pvt.debug_message (
991 l_api_name
992 || 'Entering'
993 || 'document id '
994 || document_id
995 );
996 document_type := 'text/plain';
997 -- parse document_id for the ':' dividing item type name from item key value
998 -- document_id value will take the form <ITEMTYPE>:<ITEMKEY> starting with
999 -- release 2.5 version of this demo
1000 l_hyphen_pos1 := INSTR (document_id, ':');
1001 l_item_type :=
1002 SUBSTR (document_id, 1, l_hyphen_pos1
1003 - 1);
1004 l_item_key := SUBSTR (document_id, l_hyphen_pos1
1005 + 1);
1006 l_activity_type :=
1007 wf_engine.getitemattrtext (
1008 itemtype=> l_item_type,
1009 itemkey=> l_item_key,
1010 aname => 'AMS_ACTIVITY_TYPE'
1011 );
1012 l_program_id :=
1013 wf_engine.getitemattrtext (
1014 itemtype=> l_item_type,
1015 itemkey=> l_item_key,
1016 aname => 'AMS_ACTIVITY_ID'
1017 );
1018
1019 l_note :=
1020 wf_engine.getitemattrtext (
1021 itemtype=> l_item_type,
1022 itemkey=> l_item_key,
1023 aname => 'AMS_NOTES_FROM_REQUESTOR'
1024 );
1025
1026 l_approver :=
1027 wf_engine.getitemattrtext (
1028 itemtype=> l_item_type,
1029 itemkey=> l_item_key,
1030 aname => 'AMS_APPROVER_DISPLAY_NAME'
1031 );
1032
1033 l_approver_notes :=
1034 wf_engine.getitemattrtext (
1035 itemtype=> l_item_type,
1036 itemkey=> l_item_key,
1037 aname => 'APPROVAL_NOTE'
1038 );
1039
1040 OPEN c_program_rec (l_program_id);
1041 FETCH c_program_rec INTO l_program_name,l_level_meaning,l_start_date,l_end_date,l_owner_name,l_program_description, l_company_name;
1042 CLOSE c_program_rec;
1043
1044 fnd_message.set_name ('PV', 'PV_WF_NTF_REQUESTER_APP_SUB');
1045 fnd_message.set_token ('PROGRAM_NAME', l_program_name, FALSE);
1046
1047 l_string := SUBSTR(fnd_message.get,1,1000);
1048
1049 fnd_message.set_name ('PV', 'PV_WF_NTF_PROGRAM_REQ_INFO_REQ');
1050 fnd_message.set_token ('COMPANY_NAME', l_company_name, FALSE);
1051 fnd_message.set_token ('PROGRAM_NAME', l_program_name, FALSE);
1052 fnd_message.set_token ('PROGRAM_LEVEL', l_level_meaning, FALSE);
1053 fnd_message.set_token ('START_DATE', l_start_date, FALSE);
1054 fnd_message.set_token ('END_DATE', l_end_date, FALSE);
1055 fnd_message.set_token ('OWNER', l_owner_name, FALSE);
1056 fnd_message.set_token ('DESCRIPTION', l_program_description, FALSE);
1057 fnd_message.set_token ('APPROVER', l_approver , FALSE);
1058 fnd_message.set_token ('APPR_NOTES', l_approver_notes , FALSE);
1059
1060
1061 l_string1 := SUBSTR(FND_MESSAGE.Get,1,2500);
1062
1063 -- IF (display_type = 'text/plain') THEN
1064 l_appr_notification := SUBSTR(l_string
1065 || fnd_global.local_chr (10)
1066 || l_string1
1067 || fnd_global.local_chr (10)
1068 || l_string2,1,10000);
1069 document := document
1070 || l_appr_notification;
1071 document_type := 'text/plain';
1072 RETURN;
1073 EXCEPTION
1074 WHEN OTHERS THEN
1075 wf_core.context (
1076 'AMSGAPP',
1077 'Notify_Requestor_of_approval',
1078 l_item_type,
1079 l_item_key
1080 );
1081 RAISE;
1082 END notify_requestor_of_approval;
1083
1084
1085 --------------------------------------------------------------------------
1086 -- PROCEDURE
1087 -- Notify_requestor_of rejection
1088 --
1089 -- PURPOSE
1090 -- Generate the Rejection Document for display in messages, either
1091 -- text or html
1092 -- IN
1093 -- document_id - Item Key
1094 -- display_type - either 'text/plain' or 'text/html'
1095 -- document - document buffer
1096 -- document_type - type of document buffer created, either 'text/plain'
1097 -- or 'text/html'
1098 -- OUT
1099 -- USED BY
1100 -- - Oracle MArketing Generic Apporval
1101 -- HISTORY
1102 -- 03/15/2001 pukken CREATION
1103 -------------------------------------------------------------------------------
1104
1105 PROCEDURE notify_requestor_of_rejection (
1106 document_id IN VARCHAR2,
1107 display_type IN VARCHAR2,
1108 document IN OUT NOCOPY VARCHAR2,
1109 document_type IN OUT NOCOPY VARCHAR2
1110 ) IS
1111 l_api_name VARCHAR2 (100)
1112 := g_pkg_name
1113 || 'Notify_Requestor_of_rejection';
1114 l_program_id NUMBER;
1115
1116 l_program_name VARCHAR2 (200);
1117 l_hyphen_pos1 NUMBER;
1118 l_rej_notification VARCHAR2 (10000);
1119 l_activity_type VARCHAR2 (30);
1120 l_item_type VARCHAR2 (100);
1121 l_item_key VARCHAR2 (100);
1122 l_approval_type VARCHAR2 (30);
1123 l_approver VARCHAR2 (200);
1124 l_approver_notes VARCHAR2 (3000);
1125 l_note VARCHAR2 (3000);
1126 l_string VARCHAR2 (1000);
1127 l_string1 VARCHAR2 (2500);
1128 l_start_date DATE;
1129 l_end_date DATE;
1130 l_owner_name VARCHAR2 (300);
1131 l_level_meaning VARCHAR2 (150);
1132 l_program_description VARCHAR2 (240);
1133 l_company_name VARCHAR2 (360);
1134 l_requester VARCHAR2 (30);
1135 l_string2 VARCHAR2 (2500);
1136
1137
1138 CURSOR c_program_rec (p_program_id IN NUMBER) IS
1139 SELECT PROGRAM_NAME,MEANING,PROGRAM_START_DATE,PROGRAM_END_DATE,SOURCE_NAME,PROGRAM_DESCRIPTION,SOURCE_BUSINESS_GRP_NAME
1140 FROM PV_PARTNER_PROGRAM_VL ,JTF_RS_RESOURCE_EXTNS,FND_LOOKUP_VALUES_VL
1141 WHERE PROGRAM_ID =p_program_id
1142 AND PROGRAM_OWNER_RESOURCE_ID =RESOURCE_ID
1143 AND LOOKUP_CODE=PROGRAM_LEVEL_CODE
1144 AND LOOKUP_TYPE='PV_PROGRAM_LEVEL';
1145
1146 BEGIN
1147 ams_utility_pvt.debug_message (
1148 l_api_name
1149 || 'Entering'
1150 || 'document id '
1151 || document_id
1152 );
1153 document_type := 'text/plain';
1154 -- parse document_id for the ':' dividing item type name from item key value
1155 -- document_id value will take the form <ITEMTYPE>:<ITEMKEY> starting with
1156 -- release 2.5 version of this demo
1157 l_hyphen_pos1 := INSTR (document_id, ':');
1158 l_item_type :=
1159 SUBSTR (document_id, 1, l_hyphen_pos1
1160 - 1);
1161 l_item_key := SUBSTR (document_id, l_hyphen_pos1
1162 + 1);
1163 l_activity_type :=
1164 wf_engine.getitemattrtext (
1165 itemtype=> l_item_type,
1166 itemkey=> l_item_key,
1167 aname => 'AMS_ACTIVITY_TYPE'
1168 );
1169 l_program_id :=
1170 wf_engine.getitemattrtext (
1171 itemtype=> l_item_type,
1172 itemkey=> l_item_key,
1173 aname => 'AMS_ACTIVITY_ID'
1174 );
1175
1176 l_note :=
1177 wf_engine.getitemattrtext (
1178 itemtype=> l_item_type,
1179 itemkey=> l_item_key,
1180 aname => 'AMS_NOTES_FROM_REQUESTOR'
1181 );
1182 l_approver :=
1183 wf_engine.getitemattrtext (
1184 itemtype=> l_item_type,
1185 itemkey=> l_item_key,
1186 aname => 'AMS_APPROVER_DISPLAY_NAME'
1187 );
1188
1189 l_approver_notes :=
1190 wf_engine.getitemattrtext (
1191 itemtype=> l_item_type,
1192 itemkey=> l_item_key,
1193 aname => 'APPROVAL_NOTE'
1194 );
1195
1196 OPEN c_program_rec (l_program_id);
1197 FETCH c_program_rec INTO l_program_name,l_level_meaning,l_start_date,l_end_date,l_owner_name,l_program_description, l_company_name;
1198 CLOSE c_program_rec;
1199
1200 fnd_message.set_name ('PV', 'PV_WF_NTF_REQUESTER_REJ_SUB');
1201 fnd_message.set_token ('PROGRAM_NAME', l_program_name, FALSE);
1202
1203 l_string := SUBSTR(fnd_message.get,1,1000);
1204
1205 fnd_message.set_name ('PV', 'PV_WF_NTF_PROGRAM_REQ_INFO_REJ');
1206 fnd_message.set_token ('COMPANY_NAME', l_company_name, FALSE);
1207 fnd_message.set_token ('PROGRAM_NAME', l_program_name, FALSE);
1208 fnd_message.set_token ('PROGRAM_LEVEL', l_level_meaning, FALSE);
1209 fnd_message.set_token ('START_DATE', l_start_date, FALSE);
1210 fnd_message.set_token ('END_DATE', l_end_date, FALSE);
1211 fnd_message.set_token ('OWNER', l_owner_name, FALSE);
1212 fnd_message.set_token ('DESCRIPTION', l_program_description, FALSE);
1213 fnd_message.set_token ('APPROVER', l_approver , FALSE);
1214 fnd_message.set_token ('APPR_NOTES', l_approver_notes , FALSE);
1215
1216
1217 l_string1 := SUBSTR(FND_MESSAGE.Get,1,2500);
1218
1219 l_rej_notification := SUBSTR(l_string
1220 || fnd_global.local_chr (10)
1221 || l_string1
1222 || fnd_global.local_chr (10)
1223 || l_string2,1,10000);
1224 document := document
1225 || l_rej_notification;
1226 document_type := 'text/plain';
1227 RETURN;
1228
1229
1230 EXCEPTION
1231 WHEN OTHERS THEN
1232 wf_core.context (
1233 'AMSGAPP',
1234 'Notify_requestor_of_rejection',
1235 l_item_type,
1236 l_item_key
1237 );
1238 RAISE;
1239 END notify_requestor_of_rejection;
1240
1241
1242 --------------------------------------------------------------------------
1243 -- PROCEDURE
1244 -- notify_approval_required
1245 --
1246 -- PURPOSE
1247 -- Generate the Notify Approval Document for display in messages, either
1248 -- text or html
1249 -- IN
1250 -- document_id - Item Key
1251 -- display_type - either 'text/plain' or 'text/html'
1252 -- document - document buffer
1253 -- document_type - type of document buffer created, either 'text/plain'
1254 -- or 'text/html'
1255 -- OUT
1256 -- USED BY
1257 -- - Oracle MArketing Generic Apporval
1258 -- HISTORY
1259 -- 03/15/2001 pukken CREATION
1260
1261
1262 PROCEDURE notify_approval_required (
1263 document_id IN VARCHAR2,
1264 display_type IN VARCHAR2,
1265 document IN OUT NOCOPY VARCHAR2,
1266 document_type IN OUT NOCOPY VARCHAR2
1267 ) IS
1268 l_api_name VARCHAR2 (100)
1269 := g_pkg_name
1270 || 'Notify_approval_required';
1271 l_program_id NUMBER;
1272
1273 l_program_name VARCHAR2 (200);
1274 l_hyphen_pos1 NUMBER;
1275 l_appreq_notification VARCHAR2 (10000);
1276 l_activity_type VARCHAR2 (30);
1277 l_item_type VARCHAR2 (100);
1278 l_item_key VARCHAR2 (100);
1279 l_approval_type VARCHAR2 (30);
1280 l_approver VARCHAR2 (200);
1281 l_note VARCHAR2 (3000);
1282 l_string VARCHAR2 (1000);
1283 l_string1 VARCHAR2 (2500);
1284 l_start_date DATE;
1285 l_end_date DATE;
1286 l_owner_name VARCHAR2 (300);
1287 l_level_meaning VARCHAR2 (150);
1288 l_program_description VARCHAR2 (240);
1289 l_requester VARCHAR2 (30);
1290 l_string2 VARCHAR2 (2500);
1291 l_company_name VARCHAR2 (360);
1292 l_url1 VARCHAR2 (360);
1293 l_url2 VARCHAR2 (360);
1294
1295 CURSOR c_program_rec (p_program_id IN NUMBER) IS
1296 SELECT PROGRAM_NAME,MEANING,PROGRAM_START_DATE,PROGRAM_END_DATE,SOURCE_NAME,PROGRAM_DESCRIPTION,SOURCE_BUSINESS_GRP_NAME
1297 FROM PV_PARTNER_PROGRAM_VL ,JTF_RS_RESOURCE_EXTNS,FND_LOOKUP_VALUES_VL
1298 WHERE PROGRAM_ID =p_program_id
1299 AND PROGRAM_OWNER_RESOURCE_ID =RESOURCE_ID
1300 AND LOOKUP_CODE=PROGRAM_LEVEL_CODE
1301 AND LOOKUP_TYPE='PV_PROGRAM_LEVEL';
1302
1303 BEGIN
1304 ams_utility_pvt.debug_message (
1305 l_api_name
1306 || 'Entering'
1307 || 'document id '
1308 || document_id
1309 );
1310 document_type := 'text/plain';
1311 -- parse document_id for the ':' dividing item type name from item key value
1312 -- document_id value will take the form <ITEMTYPE>:<ITEMKEY> starting with
1313 -- release 2.5 version of this demo
1314 l_hyphen_pos1 := INSTR (document_id, ':');
1315 l_item_type :=
1316 SUBSTR (document_id, 1, l_hyphen_pos1
1317 - 1);
1318 l_item_key := SUBSTR (document_id, l_hyphen_pos1
1319 + 1);
1320 l_activity_type :=
1321 wf_engine.getitemattrtext (
1322 itemtype=> l_item_type,
1323 itemkey=> l_item_key,
1324 aname => 'AMS_ACTIVITY_TYPE'
1325 );
1326 l_program_id :=
1327 wf_engine.getitemattrtext (
1328 itemtype=> l_item_type,
1329 itemkey=> l_item_key,
1330 aname => 'AMS_ACTIVITY_ID'
1331 );
1332
1333 l_note :=
1334 wf_engine.getitemattrtext (
1335 itemtype=> l_item_type,
1336 itemkey=> l_item_key,
1337 aname => 'AMS_NOTES_FROM_REQUESTOR'
1338 );
1339 l_approver :=
1340 wf_engine.getitemattrtext (
1341 itemtype=> l_item_type,
1342 itemkey=> l_item_key,
1343 aname => 'AMS_APPROVER_DISPLAY_NAME'
1344 );
1345 l_url1 := FND_PROFILE.VALUE('PV_WORKFLOW_RESPOND_URL');
1346 l_url2 := FND_PROFILE.VALUE('PV_WORKFLOW_RESPOND_SELF_SERVICE_URL');
1347
1348 OPEN c_program_rec (l_program_id);
1349 FETCH c_program_rec INTO l_program_name,l_level_meaning,l_start_date,l_end_date,l_owner_name,l_program_description, l_company_name;
1350 CLOSE c_program_rec;
1351
1352 fnd_message.set_name ('PV', 'PV_WF_NTF_APPROVER_OF_REQ_SUB');
1353 fnd_message.set_token ('PROGRAM_NAME', l_program_name, FALSE);
1354
1355 l_string := SUBSTR(fnd_message.get,1,1000);
1356
1357 fnd_message.set_name ('PV', 'PV_WF_NTF_PROGRAM_REQ_INFO_AP1');
1358 fnd_message.set_token ('COMPANY_NAME', l_company_name, FALSE);
1359 fnd_message.set_token ('URL1', l_url1, FALSE);
1360 fnd_message.set_token ('URL2', l_url2, FALSE);
1361 fnd_message.set_token ('PROGRAM_NAME', l_program_name, FALSE);
1362 fnd_message.set_token ('PROGRAM_LEVEL', l_level_meaning, FALSE);
1363 fnd_message.set_token ('START_DATE', l_start_date, FALSE);
1364 fnd_message.set_token ('END_DATE', l_end_date, FALSE);
1365 fnd_message.set_token ('OWNER', l_owner_name, FALSE);
1366 fnd_message.set_token ('DESCRIPTION', l_program_description, FALSE);
1367 l_string1 := SUBSTR(FND_MESSAGE.Get,1,2500);
1368
1369
1370 l_appreq_notification := l_string
1371 || fnd_global.local_chr (10)
1372 || l_string1
1373 || fnd_global.local_chr (10)
1374 || l_string2;
1375 document := document
1376 || l_appreq_notification;
1377 document_type := 'text/plain';
1378 RETURN;
1379
1380 EXCEPTION
1381 WHEN OTHERS THEN
1382 wf_core.context (
1383 'AMSGAPP',
1384 'notify_approval_required',
1385 l_item_type,
1386 l_item_key
1387 );
1388 RAISE;
1389 END notify_approval_required;
1390
1391
1392 --------------------------------------------------------------------------
1393 -- PROCEDURE
1394 -- notify_appr_req_reminder
1395 --
1396 -- PURPOSE
1397 -- Generate the Rejection Document for display in messages, either
1398 -- text or html
1399 -- IN
1400 -- document_id - Item Key
1401 -- display_type - either 'text/plain' or 'text/html'
1402 -- document - document buffer
1403 -- document_type - type of document buffer created, either 'text/plain'
1404 -- or 'text/html'
1405 -- OUT
1406 -- USED BY
1407 -- - Oracle MArketing Generic Apporval
1408 -- HISTORY
1409 -- 03/15/2001 pukken CREATION
1410
1411 PROCEDURE notify_appr_req_reminder (
1412 document_id IN VARCHAR2,
1413 display_type IN VARCHAR2,
1414 document IN OUT NOCOPY VARCHAR2,
1415 document_type IN OUT NOCOPY VARCHAR2
1416 ) IS
1417 l_api_name VARCHAR2 (100)
1418 := g_pkg_name
1419 || 'notify_appr_req_reminder';
1420 l_program_id NUMBER;
1421
1422 l_program_name VARCHAR2 (200);
1423 l_hyphen_pos1 NUMBER;
1424 l_apprem_notification VARCHAR2 (10000);
1425 l_activity_type VARCHAR2 (30);
1426 l_item_type VARCHAR2 (100);
1427 l_item_key VARCHAR2 (100);
1428 l_approval_type VARCHAR2 (30);
1429 l_approver VARCHAR2 (200);
1430 l_note VARCHAR2 (3000);
1431 l_string VARCHAR2 (1000);
1432 l_string1 VARCHAR2 (2500);
1433 l_start_date DATE;
1434 l_end_date DATE;
1435 l_owner_name VARCHAR2 (300);
1436 l_level_meaning VARCHAR2 (150);
1437 l_program_description VARCHAR2 (240);
1438 l_requester VARCHAR2 (30);
1439 l_string2 VARCHAR2 (2500);
1440 l_company_name VARCHAR2 (360);
1441 l_url1 VARCHAR2 (360);
1442 l_url2 VARCHAR2 (360);
1443
1444 CURSOR c_program_rec (p_program_id IN NUMBER) IS
1445 SELECT PROGRAM_NAME,MEANING,PROGRAM_START_DATE,PROGRAM_END_DATE,SOURCE_NAME,PROGRAM_DESCRIPTION,SOURCE_BUSINESS_GRP_NAME
1446 FROM PV_PARTNER_PROGRAM_VL ,JTF_RS_RESOURCE_EXTNS,FND_LOOKUP_VALUES_VL
1447 WHERE PROGRAM_ID =p_program_id
1448 AND PROGRAM_OWNER_RESOURCE_ID =RESOURCE_ID
1449 AND LOOKUP_CODE=PROGRAM_LEVEL_CODE
1450 AND LOOKUP_TYPE='PV_PROGRAM_LEVEL';
1451
1452 BEGIN
1453 ams_utility_pvt.debug_message (
1454 l_api_name
1455 || 'Entering'
1456 || 'document id '
1457 || document_id
1458 );
1459 document_type := 'text/plain';
1460 -- parse document_id for the ':' dividing item type name from item key value
1461 -- document_id value will take the form <ITEMTYPE>:<ITEMKEY> starting with
1462 -- release 2.5 version of this demo
1463 l_hyphen_pos1 := INSTR (document_id, ':');
1464 l_item_type :=
1465 SUBSTR (document_id, 1, l_hyphen_pos1
1466 - 1);
1467 l_item_key := SUBSTR (document_id, l_hyphen_pos1
1468 + 1);
1469 l_activity_type :=
1470 wf_engine.getitemattrtext (
1471 itemtype=> l_item_type,
1472 itemkey=> l_item_key,
1473 aname => 'AMS_ACTIVITY_TYPE'
1474 );
1475 l_program_id :=
1476 wf_engine.getitemattrtext (
1477 itemtype=> l_item_type,
1478 itemkey=> l_item_key,
1479 aname => 'AMS_ACTIVITY_ID'
1480 );
1481
1482 l_note :=
1483 wf_engine.getitemattrtext (
1484 itemtype=> l_item_type,
1485 itemkey=> l_item_key,
1486 aname => 'AMS_NOTES_FROM_REQUESTOR'
1487 );
1488 l_approver :=
1489 wf_engine.getitemattrtext (
1490 itemtype=> l_item_type,
1491 itemkey=> l_item_key,
1492 aname => 'AMS_APPROVER_DISPLAY_NAME'
1493 );
1494 l_url1 := FND_PROFILE.VALUE('PV_WORKFLOW_RESPOND_URL');
1495 l_url2 := FND_PROFILE.VALUE('PV_WORKFLOW_RESPOND_SELFSERVICE_URL');
1496
1497 OPEN c_program_rec (l_program_id);
1498 FETCH c_program_rec INTO l_program_name,l_level_meaning,l_start_date,l_end_date,l_owner_name,l_program_description, l_company_name;
1499 CLOSE c_program_rec;
1500
1501 fnd_message.set_name ('PV', 'PV_WF_NTF_APPROVER_OF_REQ_SUB');
1502 fnd_message.set_token ('PROGRAM_NAME', l_program_name, FALSE);
1503
1504 l_string := SUBSTR(fnd_message.get,1,1000);
1505
1506 fnd_message.set_name ('PV', 'PV_WF_NTF_PROGRAM_REQ_INFO_RM1');
1507 fnd_message.set_token ('COMPANY_NAME', l_company_name, FALSE);
1508 fnd_message.set_token ('URL1', l_url1, FALSE);
1509 fnd_message.set_token ('URL2', l_url2, FALSE);
1510 fnd_message.set_token ('PROGRAM_NAME', l_program_name, FALSE);
1511 fnd_message.set_token ('PROGRAM_LEVEL', l_level_meaning, FALSE);
1512 fnd_message.set_token ('START_DATE', l_start_date, FALSE);
1513 fnd_message.set_token ('END_DATE', l_end_date, FALSE);
1514 fnd_message.set_token ('OWNER', l_owner_name, FALSE);
1515 fnd_message.set_token ('DESCRIPTION', l_program_description, FALSE);
1516 l_string1 := SUBSTR(FND_MESSAGE.Get,1,2500);
1517
1518 l_apprem_notification := l_string
1519 || fnd_global.local_chr (10)
1520 || l_string1
1521 || fnd_global.local_chr (10)
1522 || l_string2;
1523 document := document
1524 || l_apprem_notification;
1525 document_type := 'text/plain';
1526 RETURN;
1527
1528
1529
1530 EXCEPTION
1531 WHEN OTHERS THEN
1532 wf_core.context (
1533 'AMSGAPP',
1534 'notify_appr_req_reminder',
1535 l_item_type,
1536 l_item_key
1537 );
1538 RAISE;
1539 END notify_appr_req_reminder;
1540
1541
1542 ---------------------------------------------------------------------
1543 -- PROCEDURE
1544 -- set_parprgm_activity_details
1545 --
1546 --
1547 -- PURPOSE
1548 -- This Procedure will set all the item attribute details
1549 --
1550 --
1551 -- IN
1552 --
1553 --
1554 -- OUT
1555 --
1556 -- Used By Activities
1557 --
1558 -- NOTES
1559 --
1560 --
1561 --
1562 -- HISTORY
1563 -- 02/20/2001 pukken CREATION
1564 -- End of Comments
1565 --------------------------------------------------------------------
1566 PROCEDURE set_parprgm_activity_details (
1567 itemtype IN VARCHAR2,
1568 itemkey IN VARCHAR2,
1569 actid IN NUMBER,
1570 funcmode IN VARCHAR2,
1571 resultout OUT NOCOPY VARCHAR2
1572 ) IS
1573 l_activity_id NUMBER;
1574
1575 l_activity_type VARCHAR2 (30) := 'PRGT';
1576 l_approval_type VARCHAR2 (30) := 'CONCEPT';
1577 l_object_details ams_gen_approval_pvt.objrectyp;
1578 l_approval_detail_id NUMBER;
1579 l_approver_seq NUMBER;
1580 l_return_status VARCHAR2 (1);
1581 l_msg_count NUMBER;
1582 l_msg_data VARCHAR2 (4000);
1583 l_error_msg VARCHAR2 (4000);
1584 l_orig_stat_id NUMBER;
1585 x_resource_id NUMBER;
1586 --l_full_name VARCHAR2 (60);
1587 --l_fund_number VARCHAR2 (30);
1588 --l_requested_amt NUMBER;
1589 l_approver VARCHAR2 (200);
1590 l_string VARCHAR2 (3000);
1591
1592 --the cursor below picks up the program type id based on the program id.
1593 CURSOR c_program_type_rec (p_program_id IN NUMBER) IS
1594 SELECT ppv.program_name,to_char(ppv.program_type_id) from pv_partner_program_vl ppv, pv_partner_program_type_vl ppt
1595 WHERE ppv.program_id=p_program_id
1596 AND ppv.program_type_id=ppt.program_type_id;
1597
1598
1599 BEGIN
1600 fnd_msg_pub.initialize;
1601 l_activity_id :=
1602 wf_engine.getitemattrnumber (
1603 itemtype=> itemtype,
1604 itemkey=> itemkey,
1605 aname => 'AMS_ACTIVITY_ID'
1606 );
1607 OPEN c_program_type_rec (l_activity_id);
1608 FETCH c_program_type_rec INTO l_object_details.name,
1609 l_object_details.object_type;
1610 CLOSE c_program_type_rec;
1611
1612 IF (funcmode = 'RUN') THEN
1613 ams_gen_approval_pvt.get_approval_details (
1614 p_activity_id=> l_activity_id,
1615 p_activity_type=> l_activity_type,
1616 p_approval_type=> l_approval_type,
1617 p_object_details=> l_object_details,
1618 x_approval_detail_id=> l_approval_detail_id,
1619 x_approver_seq=> l_approver_seq,
1620 x_return_status=> l_return_status
1621 );
1622
1623 IF l_return_status = fnd_api.g_ret_sts_success THEN
1624
1625 wf_engine.setitemattrnumber (
1626 itemtype=> itemtype,
1627 itemkey=> itemkey,
1628 aname => 'AMS_APPROVAL_DETAIL_ID',
1629 avalue=> l_approval_detail_id
1630 );
1631 wf_engine.setitemattrnumber (
1632 itemtype=> itemtype,
1633 itemkey=> itemkey,
1634 aname => 'AMS_APPROVER_SEQ',
1635 avalue=> l_approver_seq
1636 );
1637
1638 --- set all the subjects here
1639 fnd_message.set_name ('PV', 'PV_WF_NTF_REQUESTER_FYI_SUB');
1640 fnd_message.set_token (
1641 'PROGRAM_NAME',
1642 l_object_details.name,
1643 FALSE
1644 );
1645
1646
1647 l_string := fnd_message.get;
1648 wf_engine.setitemattrtext (
1649 itemtype=> itemtype,
1650 itemkey=> itemkey,
1651 aname => 'FYI_SUBJECT',
1652 avalue=> l_string
1653 );
1654
1655 fnd_message.set_name ('PV', 'PV_WF_NTF_REQUESTER_APP_SUB');
1656 fnd_message.set_token ('PROGRAM_NAME', l_object_details.name, FALSE );
1657
1658 l_string := fnd_message.get;
1659 wf_engine.setitemattrtext (
1660 itemtype=> itemtype,
1661 itemkey=> itemkey,
1662 aname => 'APRV_SUBJECT',
1663 avalue=> l_string
1664 );
1665
1666 fnd_message.set_name ('PV', 'PV_WF_NTF_REQUESTER_REJ_SUB');
1667 fnd_message.set_token ('PROGRAM_NAME',l_object_details.name,FALSE );
1668
1669 l_string := fnd_message.get;
1670
1671 wf_engine.setitemattrtext (
1672 itemtype=> itemtype,
1673 itemkey=> itemkey,
1674 aname => 'REJECT_SUBJECT',
1675 avalue=> l_string
1676 );
1677 fnd_message.set_name ('PV', 'PV_WF_NTF_APPROVER_OF_REQ_SUB');
1678 fnd_message.set_token (
1679 'PROGRAM_NAME',
1680 l_object_details.name,
1681 FALSE
1682 );
1683
1684 l_string := fnd_message.get;
1685
1686 wf_engine.setitemattrtext (
1687 itemtype=> itemtype,
1688 itemkey=> itemkey,
1689 aname => 'APP_SUBJECT',
1690 avalue=> l_string
1691 );
1692
1693 resultout := 'COMPLETE:SUCCESS';
1694 ELSE
1695 fnd_msg_pub.count_and_get (
1696 p_encoded=> fnd_api.g_false,
1697 p_count=> l_msg_count,
1698 p_data=> l_msg_data
1699 );
1700 ams_gen_approval_pvt.handle_err (
1701 p_itemtype=> itemtype,
1702 p_itemkey=> itemkey,
1703 p_msg_count=> l_msg_count, -- Number of error Messages
1704 p_msg_data=> l_msg_data,
1705 p_attr_name=> 'AMS_ERROR_MSG',
1706 x_error_msg=> l_error_msg
1707 );
1708 wf_core.context (
1709 'ams_gen_approval_pvt',
1710 'Set_Activity_Details',
1711 itemtype,
1712 itemkey,
1713 actid,
1714 l_error_msg
1715 );
1716 -- RAISE FND_API.G_EXC_ERROR;
1717 resultout := 'COMPLETE:ERROR';
1718 END IF;
1719 END IF;
1720
1721 --
1722 -- CANCEL mode
1723 --
1724 IF (funcmode = 'CANCEL') THEN
1725 resultout := 'COMPLETE:';
1726 RETURN;
1727 END IF;
1728
1729 --
1730 -- TIMEOUT mode
1731 --
1732 IF (funcmode = 'TIMEOUT') THEN
1733 resultout := 'COMPLETE:';
1734 RETURN;
1735 END IF;
1736 --
1737
1738 EXCEPTION
1739 WHEN fnd_api.g_exc_error THEN
1740 wf_core.context (
1741 'AMS_FundApproval_pvt',
1742 'set_parprgm_activity_details',
1743 itemtype,
1744 itemkey,
1745 actid,
1746 funcmode,
1747 l_error_msg
1748 );
1749 RAISE;
1750 WHEN OTHERS THEN
1751 fnd_msg_pub.count_and_get (
1752 p_encoded=> fnd_api.g_false,
1753 p_count=> l_msg_count,
1754 p_data=> l_msg_data
1755 );
1756 RAISE;
1757 END set_parprgm_activity_details;
1758
1759
1760 ---------------------------------------------------------------------
1761 -- PROCEDURE
1762 -- Update_ParProgram_Status
1763 --
1764 --
1765 -- PURPOSE
1766 -- This Procedure will update the status
1767 --
1768 --
1769 -- IN
1770 --
1771 --
1772 -- OUT
1773 --
1774 -- Used By Activities
1775 --
1776 -- NOTES
1777 --
1778 --
1779 --
1780 -- HISTORY
1781 -- 02/20/2001 pukken CREATION
1782 -- End of Comments
1783 -------------------------------------------------------------------
1784
1785
1786 PROCEDURE update_parprogram_status (
1787 itemtype IN VARCHAR2,
1788 itemkey IN VARCHAR2,
1789 actid IN NUMBER,
1790 funcmode IN VARCHAR2,
1791 resultout OUT NOCOPY VARCHAR2
1792 ) IS
1793 l_status_code VARCHAR2 (15);
1794 l_child_prog_stat_code VARCHAR2 (30);
1795 l_api_version CONSTANT NUMBER := 1.0;
1796 l_return_status VARCHAR2 (1) := fnd_api.g_ret_sts_success;
1797 l_msg_count NUMBER;
1798 l_msg_data VARCHAR2 (4000);
1799 l_api_name CONSTANT VARCHAR2 (30) := 'Update_ParProgram_Status';
1800 l_full_name CONSTANT VARCHAR2 (60) := g_pkg_name
1801 || '.'
1802 || l_api_name;
1803 l_program_rec PV_PARTNER_PROGRAM_PVT.ptr_prgm_rec_type;
1804 l_next_status_id NUMBER;
1805 l_approved_amount NUMBER;
1806 l_update_status VARCHAR2 (12);
1807 l_error_msg VARCHAR2 (4000);
1808 l_object_version_number NUMBER;
1809
1810 l_program_id NUMBER;
1811
1812 CURSOR citem_csr(prgm_id NUMBER) IS
1813 SELECT cont.object_version_number object_version_number , prog.citem_version_id citem_version_id
1814 FROM ibc_citem_versions_b cont_ver, ibc_content_items cont,pv_partner_program_b prog
1815 WHERE prog.program_id = prgm_id
1816 AND prog.citem_version_id = cont_ver.citem_version_id
1817 AND cont_ver.content_item_id = cont.content_item_id;
1818
1819 l_citem_object_version_number NUMBER;
1820 l_citem_version_id NUMBER;
1821
1822 CURSOR c_get_partner_program(cv_program_id NUMBER) IS
1823 SELECT *
1824 FROM PV_PARTNER_PROGRAM_B
1825 WHERE PROGRAM_PARENT_ID = cv_program_id
1826 AND ENABLED_FLAG='Y'
1827 AND SUBMIT_CHILD_NODES='Y'
1828 AND PROGRAM_STATUS_CODE='NEW';
1829
1830
1831 CURSOR c_get_status_code(cv_status_code VARCHAR2) IS
1832 SELECT user_status_id
1833 FROM AMS_USER_STATUSES_B
1834 where SYSTEM_STATUS_TYPE='PV_PROGRAM_STATUS'
1835 and SYSTEM_STATUS_CODE=cv_status_code;
1836
1837 CURSOR c_get_objverno(cv_program_id NUMBER) IS
1838 SELECT object_version_number
1839 FROM pv_partner_program_b
1840 WHERE program_id=cv_program_id;
1841
1842 CURSOR c_get_status_child(cv_program_id NUMBER) IS
1843 SELECT program_status_code,object_version_number
1844 FROM pv_partner_program_b
1845 WHERE program_id=cv_program_id;
1846
1847 l_user_status_for_new NUMBER;
1848 l_user_status_for_approved NUMBER;
1849 l_user_status_for_rejected NUMBER;
1850 l_user_status_for_pa NUMBER;
1851 l_valid_approvers boolean :=false;
1852 l_check_flag boolean :=false;
1853 BEGIN
1854 IF funcmode = 'RUN' THEN
1855 l_update_status :=
1856 wf_engine.getitemattrtext (
1857 itemtype=> itemtype,
1858 itemkey=> itemkey,
1859 aname => 'UPDATE_GEN_STATUS'
1860 );
1861
1862
1863 IF l_update_status = 'APPROVED' THEN
1864 l_next_status_id :=
1865 wf_engine.getitemattrnumber (
1866 itemtype=> itemtype,
1867 itemkey=> itemkey,
1868 aname => 'AMS_NEW_STAT_ID'
1869 );
1870 l_status_code:='APPROVED';
1871
1872 ams_utility_pvt.debug_message ( l_full_name
1873 || l_update_status);
1874 ELSE
1875 l_next_status_id :=
1876 wf_engine.getitemattrnumber (
1877 itemtype=> itemtype,
1878 itemkey=> itemkey,
1879 aname => 'AMS_REJECT_STAT_ID'
1880 );
1881 l_status_code:='REJECTED';
1882 END IF;
1883
1884 /**
1885 l_object_version_number :=
1886 wf_engine.getitemattrnumber (
1887 itemtype=> itemtype,
1888 itemkey=> itemkey,
1889 aname => 'AMS_OBJECT_VERSION_NUMBER'
1890 );
1891
1892 */
1893
1894 l_program_id :=
1895 wf_engine.getitemattrnumber (
1896 itemtype=> itemtype,
1897 itemkey=> itemkey,
1898 aname => 'AMS_ACTIVITY_ID'
1899 );
1900 -- x_return_status := fnd_api.g_ret_sts_success;
1901
1902 OPEN c_get_objverno(l_program_id);
1903 FETCH c_get_objverno INTO l_object_version_number;
1904 CLOSE c_get_objverno;
1905
1906 l_program_rec.program_id := l_program_id;
1907 l_program_rec.program_status_code := l_status_code;
1908 l_program_rec.user_status_id:= l_next_status_id;
1909 l_program_rec.object_version_number := l_object_version_number;
1910
1911
1912 ams_utility_pvt.debug_message (
1913 l_full_name
1914 || l_status_code
1915 );
1916
1917
1918
1919 PV_PARTNER_PROGRAM_PVT.Update_Partner_Program (
1920 p_api_version_number=> l_api_version,
1921 p_init_msg_list=> fnd_api.g_false,
1922 --p_commit => FND_API.G_FALSE,
1923 --p_validation_level => FND_API.g_valid_level_full,
1924 x_return_status=> l_return_status,
1925 x_msg_count=> l_msg_count,
1926 x_msg_data=> l_msg_data,
1927 p_ptr_prgm_rec=> l_program_rec
1928 );
1929
1930 OPEN citem_csr(l_program_id);
1931 FETCH citem_csr INTO l_citem_object_version_number,l_citem_version_id;
1932 CLOSE citem_csr;
1933
1934 IF l_citem_version_id is NOT NULL THEN
1935 IBC_CITEM_ADMIN_GRP.approve_item(
1936 p_citem_ver_id => l_citem_version_id
1937 ,p_commit => FND_API.G_FALSE
1938 ,p_init_msg_list => FND_API.g_false
1939 ,p_api_version_number => IBC_CITEM_ADMIN_GRP.G_API_VERSION_DEFAULT
1940 ,px_object_version_number => l_citem_object_version_number
1941 ,x_return_status => l_return_status
1942 ,x_msg_count => l_msg_count
1943 ,x_msg_data => l_msg_data
1944 );
1945 END IF;
1946
1947
1948 /** pick up all the child programs and check whether the parent is approved
1949 and check the value of submitchildnodes of each of these child programs
1950 if true, call startworkflow for these child programs in a loop
1951 */
1952 IF l_status_code='APPROVED' THEN
1953 OPEN c_get_status_code('NEW');
1954 FETCH c_get_status_code into l_user_status_for_new;
1955 IF ( c_get_status_code%NOTFOUND) THEN
1956 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1957 END IF;
1958 CLOSE c_get_status_code;
1959 OPEN c_get_status_code('APPROVED');
1960 FETCH c_get_status_code into l_user_status_for_approved;
1961 IF ( c_get_status_code%NOTFOUND) THEN
1962 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1963 END IF;
1964 CLOSE c_get_status_code;
1965 OPEN c_get_status_code('REJECTED');
1966 FETCH c_get_status_code into l_user_status_for_rejected;
1967 IF ( c_get_status_code%NOTFOUND) THEN
1968 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1969 END IF;
1970 CLOSE c_get_status_code;
1971
1972 OPEN c_get_status_code('PENDING_APPROVAL');
1973 FETCH c_get_status_code into l_user_status_for_pa;
1974 IF ( c_get_status_code%NOTFOUND) THEN
1975 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1976 END IF;
1977 CLOSE c_get_status_code;
1978
1979 for child_cur in c_get_partner_program(l_program_id) loop
1980 IF l_check_flag=false THEN
1981 l_valid_approvers:=isApproverExists(child_cur.PROGRAM_TYPE_ID);
1982 END IF;
1983 /** code needs to be written to sent another notification to the requester if
1984 program type approver becomes invalid maybe sometime after the requester
1985 submitted the entire node forapproval*/
1986
1987 IF l_valid_approvers=true THEN
1988 ams_gen_approval_pvt.StartProcess( p_activity_type =>'PRGT'
1989 ,p_activity_id=>child_cur.program_id
1990 ,p_approval_type=>'CONCEPT'
1991 ,p_object_version_number=>child_cur.object_version_number
1992 ,p_orig_stat_id=>l_user_status_for_new
1993 ,p_new_stat_id=>l_user_status_for_approved
1994 ,p_reject_stat_id=>l_user_status_for_rejected
1995 ,p_requester_userid=>child_cur.program_owner_resource_id
1996 ,p_notes_from_requester=>null
1997 ,p_workflowprocess=>'AMSGAPP'
1998 ,p_item_type=>'AMSGAPP'
1999 );
2000
2001
2002 OPEN c_get_status_child(child_cur.program_id);
2003 FETCH c_get_status_child INTO l_child_prog_stat_code,l_object_version_number;
2004 CLOSE c_get_status_child;
2005 --the child program could automatically go to approved if owner and approver is same
2006
2007 IF l_child_prog_stat_code <>'APPROVED' THEN
2008
2009 update pv_partner_program_b set PROGRAM_STATUS_CODE='PENDING_APPROVAL',
2010 USER_STATUS_ID=l_user_status_for_pa,
2011 object_version_number=l_object_version_number+1
2012 where
2013 program_id=child_cur.program_id;
2014
2015
2016 END IF;
2017 END IF;
2018 l_check_flag :=true;
2019 end loop;
2020
2021 END IF;
2022 IF l_return_status <> fnd_api.g_ret_sts_success THEN
2023 ams_gen_approval_pvt.handle_err (
2024 p_itemtype=> itemtype,
2025 p_itemkey=> itemkey,
2026 p_msg_count=> l_msg_count, -- Number of error Messages
2027 p_msg_data=> l_msg_data,
2028 p_attr_name=> 'AMS_ERROR_MSG',
2029 x_error_msg=> l_error_msg
2030 );
2031 resultout := 'COMPLETE:ERROR';
2032 ELSE
2033 resultout := 'COMPLETE:SUCCESS';
2034 END IF;
2035 END IF;
2036
2037 -- CANCEL mode
2038 --
2039 IF (funcmode = 'CANCEL') THEN
2040 resultout := 'COMPLETE:';
2041 RETURN;
2042 END IF;
2043
2044 --
2045 -- TIMEOUT mode
2046 --
2047 IF (funcmode = 'TIMEOUT') THEN
2048 resultout := 'COMPLETE:';
2049 RETURN;
2050 END IF;
2051
2052 fnd_msg_pub.count_and_get (
2053 p_encoded=> fnd_api.g_false,
2054 p_count=> l_msg_count,
2055 p_data=> l_msg_data
2056 );
2057 ams_utility_pvt.debug_message (
2058 l_full_name
2059 || ': l_return_status'
2060 || l_return_status
2061 );
2062 EXCEPTION
2063 WHEN OTHERS THEN
2064 -- x_return_status := fnd_api.g_ret_sts_error;
2065 fnd_msg_pub.count_and_get (
2066 p_encoded=> fnd_api.g_false,
2067 p_count=> l_msg_count,
2068 p_data=> l_msg_data
2069 );
2070 RAISE;
2071 END update_parprogram_status;
2072
2073
2074
2075 PROCEDURE check_approved ( itemtype IN VARCHAR2
2076 ,itemkey IN VARCHAR2
2077 ,actid IN NUMBER
2078 ,funcmode IN VARCHAR2
2079 ,resultout OUT NOCOPY VARCHAR2
2080 ) IS
2081
2082
2083 L_API_NAME CONSTANT VARCHAR2(30) := 'CHECK_APPROVED';
2084 l_object_id NUMBER;
2085 l_object_type VARCHAR2(30);
2086 l_approver_id NUMBER;
2087 l_flag VARCHAR2(1);
2088
2089 CURSOR c_temp_appr_cur(p_obj_id IN NUMBER,p_appr_id IN NUMBER) IS
2090 SELECT 'X'
2091 FROM pv_ge_temp_Approvers
2092 WHERE approval_status_code IN ( 'APPROVED','REJECTED', 'PEER_RESPONDED','APPROVER_CHANGED')
2093 AND entity_approver_id=p_obj_id
2094 AND approver_id=p_appr_id
2095 AND arc_appr_for_entity_code='ENRQ';
2096 BEGIN
2097
2098 l_object_id := WF_ENGINE.GetItemAttrNumber(
2099 itemtype => itemtype
2100 , itemkey => itemkey
2101 , aname => 'OBJECT_ID'
2102 );
2103
2104 l_object_type := WF_ENGINE.GetItemAttrText (
2105 itemtype => itemtype
2106 , itemkey => itemkey
2107 , aname => 'OBJECT_TYPE'
2108 );
2109
2110 l_approver_id := WF_ENGINE.GetItemAttrNumber (
2111 itemtype => itemtype
2112 , itemkey => itemkey
2113 , aname => 'APPROVER_ID'
2114 );
2115
2116
2117 AMS_Utility_PVT.debug_message (L_API_NAME || ' - FUNCMODE: ' || funcmode);
2118 -- RUN mode - Normal Process Execution
2119 IF (funcmode = 'RUN') THEN
2120
2121 OPEN c_temp_appr_cur(l_object_id,l_approver_id );
2122 FETCH c_temp_appr_cur INTO l_flag;
2123 IF c_temp_appr_cur%found THEN
2124 resultout := 'COMPLETE:Y' ;
2125 ELSE
2126 resultout := 'COMPLETE:N' ;
2127 END IF;
2128 CLOSE c_temp_appr_cur;
2129 RETURN;
2130 ELSIF (funcmode = 'CANCEL') THEN
2131 resultout := 'COMPLETE:' ;
2132 RETURN;
2133 -- TIMEOUT mode - Normal Process Execution
2134 ELSIF (funcmode = 'TIMEOUT') THEN
2135 resultout := 'COMPLETE:' ;
2136 RETURN;
2137 --
2138 -- Other execution modes may be created in the future. The following
2139 -- activity will indicate that it does not implement a mode
2140 -- by returning null
2141 --
2142
2143 END IF;
2144
2145 AMS_Utility_PVT.debug_message (L_API_NAME || ' - RESULT: ' || resultout);
2146
2147 -- write to log
2148 EXCEPTION
2149 WHEN OTHERS THEN
2150 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2151 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
2152 END IF;
2153
2154 --write_to_enrollment_log
2155 wf_core.context(G_PKG_NAME,'check_approved', itemtype,itemkey,to_char(actid),funcmode);
2156 resultout := 'COMPLETE:' ;
2157 raise;
2158
2159 END check_approved;
2160
2161
2162 PROCEDURE getAttributeValues(
2163 p_entity_approver_id IN NUMBER
2164 ,x_partner_name OUT NOCOPY VARCHAR2
2165 ,x_program_name OUT NOCOPY VARCHAR2
2166 ,x_enrollment_type OUT NOCOPY VARCHAR2
2167 ,x_return_status OUT NOCOPY VARCHAR2
2168 )
2169 IS
2170
2171 /* Get the Enrollment Request details in cursor c_pg_enrl_requests */
2172 CURSOR c_enr_cur (cv_enrl_id IN NUMBER) IS
2173 /**SELECT partner.party_name
2174 ,ppvl.program_name
2175 ,fl.description enrollment_type
2176 FROM pv_partner_program_vl ppvl
2177 ,fnd_lookups fl
2178 ,pv_pg_enrl_requests pper
2179 ,pv_partner_profiles ppp
2180 ,hz_parties PARTNER
2181 WHERE pper.partner_id = ppp.partner_id
2182 AND ppp.partner_id=PARTNER.party_id
2183 AND fl.lookup_type='PV_ENROLLMENT_REQUEST_TYPE'
2184 AND fl.lookup_code = pper.enrollment_type_code
2185 AND pper.program_id = ppvl.program_id
2186 AND pper.enrl_request_id =cv_enrl_request_id;
2187 */
2188 SELECT partner.party_name
2189 ,ppvl.program_name
2190 ,fl.description enrollment_type
2191 FROM pv_partner_program_vl ppvl
2192 ,fnd_lookups fl
2193 ,pv_pg_enrl_requests pper
2194 ,pv_partner_profiles ppp
2195 ,hz_parties PARTNER
2196
2197 WHERE pper.partner_id = ppp.partner_id
2198 AND ppp.partner_party_id=PARTNER.party_id
2199 AND fl.lookup_type='PV_ENROLLMENT_REQUEST_TYPE'
2200 AND fl.lookup_code = pper.enrollment_type_code
2201 AND pper.program_id = ppvl.program_id
2202 AND pper.enrl_request_id =cv_enrl_id;
2203
2204
2205
2206
2207 BEGIN
2208 /* Initialize API return status to success */
2209 x_return_status := FND_API.G_RET_STS_SUCCESS;
2210
2211 OPEN c_enr_cur( p_entity_approver_id );
2212 FETCH c_enr_cur
2213 INTO x_partner_name
2214 ,x_program_name
2215 ,x_enrollment_type;
2216 CLOSE c_enr_cur;
2217
2218 EXCEPTION
2219 WHEN OTHERS THEN
2220 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2221 END getAttributeValues;
2222
2223
2224 PROCEDURE Initialize_Var
2225 ( p_object_id IN NUMBER
2226 , p_object_type IN VARCHAR2
2227 , p_itemtype IN VARCHAR2
2228 , p_itemkey IN VARCHAR2
2229 , p_approver_id IN NUMBER
2230 , p_role_name IN VARCHAR2
2231 , p_display_name IN VARCHAR2
2232 )
2233 IS
2234 l_return_status VARCHAR2(1);
2235 l_msg_count NUMBER;
2236 l_msg_data VARCHAR2(4000);
2237 l_partner_name VARCHAR2(360);
2238 l_program_name VARCHAR2(60);
2239 l_enrollment_type VARCHAR2(30);
2240 l_country VARCHAR2(100);
2241
2242 l_role_name VARCHAR2(100);
2243 l_display_role_name VARCHAR2(100);
2244 l_approver_id NUMBER;
2245 l_rem NUMBER;
2246 l_string_sub VARCHAR2(1000);
2247 l_string VARCHAR2(3000);
2248
2249 BEGIN
2250
2251 getAttributeValues
2252 ( p_entity_approver_id =>p_object_id
2253 , x_partner_name =>l_partner_name
2254 , x_program_name =>l_program_name
2255 , x_enrollment_type =>l_enrollment_type
2256 , x_return_status =>l_return_status
2257 );
2258
2259 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2260 RAISE FND_API.G_EXC_ERROR;
2261 END IF;
2262
2263 WF_ENGINE.SetItemAttrNumber
2264 ( itemtype => p_itemtype
2265 , itemkey => p_itemkey
2266 , aname => 'OBJECT_ID'
2267 , avalue => p_object_id
2268 );
2269
2270 WF_ENGINE.SetItemAttrText
2271 ( itemtype => p_itemtype
2272 , itemkey => p_itemkey
2273 , aname => 'OBJECT_TYPE'
2274 , avalue => p_object_type
2275 );
2276
2277 fnd_message.set_name ('PV', 'PV_ENRQ_FYI_NOTIF_SUB');
2278 fnd_message.set_token ('PROGRAM_NAME', UPPER(l_program_name), FALSE);
2279 fnd_message.set_token ('PARTNER_NAME', UPPER(l_partner_name), FALSE);
2280
2281 l_string_sub := SUBSTR(fnd_message.get,1,1000);
2282
2283 WF_ENGINE.SetItemAttrText(
2284 itemtype => p_itemtype
2285 , itemkey => p_itemkey
2286 , aname => 'APPR_SUBJECT'
2287 , avalue => l_string_sub
2288 );
2289
2290 fnd_message.set_name ('PV', 'PV_ENRQ_FYI_NOTIF_BODY');
2291 fnd_message.set_token ('PARTNER_NAME', l_partner_name, FALSE);
2292 fnd_message.set_token ('PROGRAM_NAME', l_program_name, FALSE);
2293 fnd_message.set_token ('ENROLLMENT_TYPE', l_enrollment_type, FALSE);
2294 l_string := SUBSTR(fnd_message.get,1,3000);
2295
2296 WF_ENGINE.SetItemAttrText(
2297 itemtype => p_itemtype
2298 , itemkey => p_itemkey
2299 , aname => 'FYI_BODY'
2300 , avalue => l_string
2301 );
2302
2303 fnd_message.set_name ('PV', 'PV_ENRQ_FYI_REM_SUB');
2304 fnd_message.set_token ('PROGRAM_NAME', UPPER(l_program_name), FALSE);
2305 fnd_message.set_token ('PARTNER_NAME', UPPER(l_partner_name), FALSE);
2306 l_string_sub := SUBSTR(fnd_message.get,1,1000);
2307
2308 WF_ENGINE.SetItemAttrText(
2309 itemtype => p_itemtype
2310 , itemkey => p_itemkey
2311 , aname => 'REMINDER_SUBJECT'
2312 , avalue => l_string_sub
2313 );
2314
2315 fnd_message.set_name ('PV', 'PV_ENRQ_FYI_REM_BODY');
2316 fnd_message.set_token ('PARTNER_NAME', l_partner_name, FALSE);
2317 fnd_message.set_token ('PROGRAM_NAME', l_program_name, FALSE);
2318 fnd_message.set_token ('ENROLLMENT_TYPE', l_enrollment_type, FALSE);
2319 l_string := SUBSTR(fnd_message.get,1,3000);
2320
2321 WF_ENGINE.SetItemAttrText(
2322 itemtype => p_itemtype
2323 , itemkey => p_itemkey
2324 , aname => 'REMINDER_BODY'
2325 , avalue => l_string
2326 );
2327
2328
2329 WF_ENGINE.SetItemAttrNumber(
2330 itemtype => p_itemtype
2331 , itemkey => p_itemkey
2332 , aname => 'APPROVER_ID'
2333 , avalue => p_approver_id
2334 );
2335
2336 l_rem :=isnumber(FND_PROFILE.VALUE('PV_ENRQ_REM_DURATION'));
2337
2338 IF l_rem IS NULL THEN
2339 l_rem:=30;
2340 END IF;
2341
2342 IF l_rem >180 THEN
2343 l_rem:=180;
2344 END IF;
2345
2346
2347 -- convert the days to minutes
2348 l_rem:=l_rem*24*60;
2349
2350 WF_ENGINE.SetItemAttrNumber(
2351 itemtype => p_itemtype
2352 , itemkey => p_itemkey
2353 , aname => 'REMINDER_DURATION'
2354 , avalue => l_rem
2355 );
2356
2357
2358 /*PV_PG_NOTIF_UTILITY_PVT.get_resource_role(
2359 p_resource_id => p_resource_id
2360 ,x_role_name => l_role_name
2361 ,x_role_display_name => l_display_role_name
2362 ,x_return_status => l_return_status
2363 );
2364 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2365 RAISE FND_API.G_EXC_ERROR;
2366 END IF;
2367 */
2368
2369 WF_ENGINE.SetItemAttrText (
2370 itemtype => p_itemtype
2371 , itemkey => p_itemkey
2372 , aname => 'OWNER_USERNAME'
2373 , avalue => p_role_name );
2374
2375
2376 END Initialize_Var ;
2377
2378
2379 PROCEDURE StartProcess
2380 ( p_object_id IN NUMBER -- enrl_request_id
2381 , p_object_type IN VARCHAR2 --'ENRQ'
2382 , processName IN VARCHAR2
2383 , itemtype IN VARCHAR2
2384 , p_entity_approver_id IN VARCHAR2 --this is the primary key in temp approvers table
2385 , p_role_name IN VARCHAR2
2386 , p_display_name IN VARCHAR2
2387 , x_itemkey OUT NOCOPY VARCHAR2
2388 )
2389 IS
2390
2391
2392 L_API_NAME CONSTANT VARCHAR2(30) := 'STARTPROCESS';
2393 l_itemtype VARCHAR2(30) := itemtype;
2394 itemkey VARCHAR2(30) := p_object_id||p_object_type||p_entity_approver_id||TO_CHAR(SYSDATE,'DDMMRRRRHH24MISS');
2395 itemuserkey VARCHAR2(80) := p_object_id||'-'||p_object_type;
2396 l_return_status VARCHAR2(1);
2397 l_msg_count NUMBER;
2398 l_msg_data VARCHAR2(4000);
2399
2400 l_approver_rec Pv_Ge_Temp_Approvers_PVT.APPROVER_REC_TYPE;
2401
2402 CURSOR approver_dtl_cur (p_entity_appr_id number) IS
2403 SELECT object_version_number,approver_id
2404 FROM pv_ge_temp_approvers
2405 WHERE entity_approver_id=p_entity_appr_id;
2406
2407 BEGIN
2408 -- clear the message buffer
2409 FND_MSG_PUB.initialize;
2410 --write to logs that workflow is getting initiated.
2411 WF_ENGINE.CreateProcess
2412 ( itemtype => l_itemtype
2413 , itemkey => itemkey
2414 , process => processName -- 'PV_APPROVER_NOTIFICATIONS'
2415 );
2416 --add debug messages.
2417
2418 OPEN approver_dtl_cur(p_entity_approver_id);
2419 FETCH approver_dtl_cur into l_approver_rec.object_version_number ,l_approver_rec.approver_id;
2420 CLOSE approver_dtl_cur;
2421 l_approver_rec.entity_approver_id:=p_entity_approver_id;
2422
2423 l_approver_rec.workflow_item_key:=itemkey;
2424
2425 Initialize_Var
2426 ( p_object_id => p_object_id
2427 , p_object_type => p_object_type
2428 , p_itemtype => l_itemtype
2429 , p_itemkey => itemkey
2430 , p_approver_id => l_approver_rec.approver_id
2431 , p_role_name => p_role_name
2432 , p_display_name => p_display_name
2433 );
2434
2435 WF_ENGINE.StartProcess
2436 ( itemtype => l_itemtype
2437 , itemkey => itemkey
2438 );
2439
2440
2441 --write to the approver logs with the itemkey for tracking purposes.
2442 Pv_Ge_Temp_Approvers_PVT.Update_Ptr_Enr_Temp_Appr
2443 ( p_api_version_number => 1.0
2444 , p_init_msg_list => FND_API.g_false
2445 , p_commit => FND_API.G_FALSE
2446 , p_validation_level => FND_API.g_valid_level_full
2447 , x_return_status => l_return_status
2448 , x_msg_count => l_msg_count
2449 , x_msg_data => l_msg_data
2450 , p_approver_rec => l_approver_rec
2451 );
2452
2453 IF l_return_status = FND_API.g_ret_sts_error THEN
2454 RAISE FND_API.g_exc_error;
2455 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
2456 RAISE FND_API.g_exc_unexpected_error;
2457 END IF;
2458
2459
2460 EXCEPTION
2461 -- The line below records this function call in the error system
2462 -- in the case of an exception.
2463 WHEN OTHERS THEN
2464 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2465 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
2466 END IF;
2467
2468 --write_buffer_to_log (p_object_type, p_object_id);
2469
2470 wf_core.context (G_PKG_NAME, 'StartProcess',p_object_id,itemuserkey);
2471
2472 raise;
2473
2474 END StartProcess;
2475
2476 --this procedure should be called only when u create new membership record by passing
2477 -- a valid enrollment_request_id with the record
2478 PROCEDURE setmembershipdetails( pv_pg_memb_rec IN OUT NOCOPY PV_Pg_Memberships_PVT.memb_rec_type
2479 ,x_return_status OUT NOCOPY VARCHAR2
2480 ,x_msg_count OUT NOCOPY NUMBER
2481 ,x_msg_data OUT NOCOPY VARCHAR2
2482 ) IS
2483
2484 CURSOR enr_request_cur(p_enrl_req_id number) IS
2485 SELECT partner_id,program_id,enrollment_type_code,tentative_start_date,tentative_end_date
2486 FROM pv_pg_enrl_requests
2487 WHERE enrl_request_id=p_enrl_req_id;
2488
2489 l_enrollment_type_code varchar2(30);
2490 l_tentative_start_date DATE:=null;
2491 l_tentative_end_date DATE:=null;
2492
2493 BEGIN
2494 ----DBMS_OUTPUT.PUT_LINE('inside setmembership');
2495 OPEN enr_request_cur(pv_pg_memb_rec.enrl_request_id);
2496 FETCH enr_request_cur
2497 INTO pv_pg_memb_rec.partner_id
2498 ,pv_pg_memb_rec.program_id
2499 ,l_enrollment_type_code
2500 ,l_tentative_start_date
2501 ,l_tentative_end_date;
2502 CLOSE enr_request_cur;
2503 getstart_and_end_date(
2504 p_api_version_number => 1.0
2505 ,p_init_msg_list => FND_API.g_false
2506 ,p_commit => FND_API.G_FALSE
2507 ,p_validation_level => FND_API.g_valid_level_full
2508 ,enrl_request_id => pv_pg_memb_rec.enrl_request_id
2509 ,x_start_date =>l_tentative_start_date
2510 ,x_end_date =>l_tentative_end_date
2511 ,x_return_status => x_return_status
2512 ,x_msg_count => x_msg_count
2513 ,x_msg_data => x_msg_data
2514 );
2515 pv_pg_memb_rec.start_date:=l_tentative_start_date;
2516 pv_pg_memb_rec.original_end_date:=l_tentative_end_date;
2517 IF l_enrollment_type_code in ('NEW','UPGRADE','DOWNGRADE') THEN
2518 pv_pg_memb_rec.membership_status_code:='ACTIVE';
2519 ELSIF l_enrollment_type_code='RENEW' THEN
2520 IF pv_pg_memb_rec.start_date>sysdate THEN
2521 pv_pg_memb_rec.membership_status_code:='FUTURE';
2522 ELSE
2523 pv_pg_memb_rec.membership_status_code:='ACTIVE';
2524 END IF;
2525 END IF;
2526
2527
2528
2529 EXCEPTION
2530 WHEN OTHERS THEN
2531 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2532 END setmembershipdetails;
2533
2534 -- this api is called when the enrollment request is finally approved or when its rejected by any approvers
2535 PROCEDURE process_response
2536 ( enrl_request_id IN NUMBER
2537 , approvalStatus IN VARCHAR2
2538 , x_return_status OUT NOCOPY VARCHAR2
2539 , x_msg_count OUT NOCOPY NUMBER
2540 , x_msg_data OUT NOCOPY VARCHAR2
2541 ) IS
2542
2543 CURSOR approver_dtl_cur (p_enrl_req_id number) IS
2544 SELECT object_version_number,entity_approver_id,approver_id,approver_type_code
2545 FROM pv_ge_temp_approvers
2546 WHERE appr_for_entity_id=p_enrl_req_id;
2547
2548 CURSOR enr_requests_dtl_cur (p_enrl_req_id number) IS
2549 SELECT enrq.object_version_number,enrollment_type_code,order_header_id,partner_id,program_name, enrq.program_id
2550 FROM pv_pg_enrl_requests enrq
2551 , pv_partner_program_vl prgm
2552 WHERE enrq.enrl_request_id=p_enrl_req_id
2553 AND enrq.program_id=prgm.program_id;
2554
2555 CURSOR membership_dtl_cur(p_membership_id number) IS
2556 SELECT object_version_number
2557 FROM pv_pg_memberships
2558 WHERE membership_id=p_membership_id;
2559
2560 CURSOR prev_memb_id_cur(p_enrl_req_id number) IS
2561 SELECT prev_membership_id
2562 FROM pv_pg_enrq_init_sources
2563 WHERE enrl_request_id=p_enrl_req_id;
2564
2565 /**
2566 CURSOR check_attr_exist(p_program_id NUMBER) IS
2567 SELECT 'X' from dual where exists( SELECT distinct(entity_attr_id)
2568 FROM pv_ge_qsnr_elements_b
2569 WHERE arc_used_by_entity_code='PRGM'
2570 AND used_by_entity_id in
2571 ( SELECT program_id
2572 FROM pv_partner_program_b
2573 START WITH program_id =p_program_id
2574 CONNECT BY program_id = prior program_parent_id
2575 )
2576 );
2577 */
2578
2579 CURSOR check_attr_exist(p_program_id NUMBER) IS
2580 SELECT 'X' from dual where exists( SELECT distinct(attr.attribute_id)
2581 FROM pv_ge_qsnr_elements_b qsnr, pv_entity_attrs attr
2582 WHERE arc_used_by_entity_code='PRGM'
2583 AND attr.ENTITY_ATTR_ID=qsnr.entity_attr_id
2584 AND used_by_entity_id in
2585 ( SELECT program_id
2586 FROM pv_partner_program_b
2587 START WITH program_id =p_program_id
2588 CONNECT BY program_id = prior program_parent_id
2589 )
2590 );
2591
2592
2593 CURSOR attribute_id_csr(p_program_id NUMBER) IS
2594 SELECT distinct(attr.attribute_id)
2595 FROM pv_ge_qsnr_elements_b qsnr, pv_entity_attrs attr
2596 WHERE arc_used_by_entity_code='PRGM'
2597 AND attr.ENTITY_ATTR_ID=qsnr.entity_attr_id
2598 AND used_by_entity_id in
2599 ( SELECT program_id
2600 FROM pv_partner_program_b
2601 START WITH program_id =p_program_id
2602 CONNECT BY program_id = prior program_parent_id
2603 );
2604
2605
2606
2607 /**
2608 SELECT distinct(entity_attr_id)
2609 FROM pv_ge_qsnr_elements_b
2610 WHERE arc_used_by_entity_code='PRGM'
2611 AND used_by_entity_id in
2612 ( SELECT program_id
2613 FROM pv_partner_program_b
2614 START WITH program_id =p_program_id
2615 CONNECT BY program_id = prior program_parent_id
2616 );
2617 */
2618 CURSOR pending_appovers_csr ( enrl_id NUMBER ) IS
2619 SELECT entity_approver_id,object_version_number
2620 FROM pv_ge_temp_approvers
2621 WHERE APPR_FOR_ENTITY_ID =enrl_id
2622 AND APPROVAL_STATUS_CODE IN ('PENDING_APPROVAL','PENDING_DEFAULT');
2623
2624 l_attr_id_tbl PV_ENTY_ATTR_VALUE_PUB.NUMBER_TABLE;
2625
2626 pv_pg_memb_rec PV_Pg_Memberships_PVT.memb_rec_type;
2627 pv_pg_prev_memb_rec PV_Pg_Memberships_PVT.memb_rec_type;
2628 l_approver_rec Pv_Ge_Temp_Approvers_PVT.APPROVER_REC_TYPE;
2629 l_enrq_rec PV_Pg_Enrl_Requests_PVT.enrl_request_rec_type;
2630 l_mmbr_tran_rec pv_pg_mmbr_transitions_PVT.mmbr_tran_rec_type;
2631 l_api_name CONSTANT VARCHAR2(30) := 'process_approved_requests';
2632 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
2633 l_api_version_number CONSTANT NUMBER := 1.0;
2634 --This value for l_default_approver_id needs to be retrieved from profile
2635
2636 l_membership_id NUMBER;
2637 l_entity_approver_id NUMBER;
2638 l_object_version_number NUMBER(9);
2639 l_enrollment_type_code VARCHAR2(30);
2640 l_previous_membership_id NUMBER;
2641 l_mmbr_transition_id NUMBER;
2642 l_partner_id NUMBER;
2643 l_program_id NUMBER;
2644 l_previous_end_date DATE;
2645 l_message_code VARCHAR2(30);
2646 L_ORDER_HEADER_ID NUMBER;
2647 l_param_tbl_var PVX_UTILITY_PVT.log_params_tbl_type;
2648 l_attr_id_exists varchar2(1):=null;
2649 l_program_name VARCHAR2(60);
2650
2651 BEGIN
2652 -- this api is called when the enrollment request is finally approved or when its rejected by any approvers
2653
2654 --update the temp approvers table with the approvalstatus
2655 /*OPEN approver_dtl_cur(enrl_request_id);
2656 FETCH approver_dtl_cur into
2657 l_approver_rec.object_version_number
2658 ,l_approver_rec.entity_approver_id
2659 ,l_approver_rec.approver_id
2660 ,l_approver_rec.approver_type_code;
2661 CLOSE approver_dtl_cur;
2662
2663 ----DBMS_OUTPUT.PUT_LINE('inside process response');
2664
2665 l_approver_rec.approval_status_code:=approvalStatus;
2666
2667 Pv_Ge_Temp_Approvers_PVT.Update_Ptr_Enr_Temp_Appr(
2668 p_api_version_number => 1.0
2669 ,p_init_msg_list => FND_API.g_false
2670 ,p_commit => FND_API.G_FALSE
2671 ,p_validation_level => FND_API.g_valid_level_full
2672 ,x_return_status => x_return_status
2673 ,x_msg_count => x_msg_count
2674 ,x_msg_data => x_msg_data
2675 ,p_approver_rec =>l_approver_rec
2676 );
2677
2678 IF x_return_status = FND_API.g_ret_sts_error THEN
2679 RAISE FND_API.g_exc_error;
2680 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
2681 RAISE FND_API.g_exc_unexpected_error;
2682 END IF;
2683 */
2684
2685 FOR x in pending_appovers_csr(enrl_request_id) LOOP
2686 l_approver_rec.entity_approver_id :=x.entity_approver_id;
2687 l_approver_rec.object_version_number:=x.object_version_number;
2688 l_approver_rec.approval_status_code:='PEER_RESPONDED';
2689 Pv_Ge_Temp_Approvers_PVT.Update_Ptr_Enr_Temp_Appr
2690 ( p_api_version_number => 1.0
2691 , p_init_msg_list => FND_API.g_false
2692 , p_commit => FND_API.g_false
2693 , p_validation_level => FND_API.g_valid_level_full
2694 , x_return_status => x_return_status
2695 , x_msg_count => x_msg_count
2696 , x_msg_data => x_msg_data
2697 , p_approver_rec =>l_approver_rec
2698 );
2699 END LOOP;
2700
2701 --set the record to update enrollment requests table
2702 OPEN enr_requests_dtl_cur(enrl_request_id);
2703 FETCH enr_requests_dtl_cur into l_enrq_rec.object_version_number,l_enrollment_type_code,l_order_header_id,l_partner_id,l_program_name, l_program_id ;
2704 CLOSE enr_requests_dtl_cur;
2705
2706 l_enrq_rec.enrl_request_id:=enrl_request_id;
2707 l_enrq_rec.request_status_code:=approvalStatus;
2708
2709
2710 --Also update the previous membership records depending on the enrollment type of the current request
2711 -- Also insert into member transitions table if the current request is upgrade or renewal.
2712 IF approvalStatus='APPROVED' THEN
2713 -- call the api to create a membership record in memberships table
2714 pv_pg_memb_rec.enrl_request_id:=enrl_request_id;
2715 ----DBMS_OUTPUT.PUT_LINE('before setting enrollment record');
2716
2717 setmembershipdetails( pv_pg_memb_rec => pv_pg_memb_rec
2718 ,x_return_status =>x_return_status
2719 ,x_msg_count =>x_msg_count
2720 ,x_msg_data =>x_msg_data
2721 );
2722 ----DBMS_OUTPUT.PUT_LINE('after setting enrollment record');
2723
2724 l_partner_id:=pv_pg_memb_rec.partner_id;
2725 l_program_id:=pv_pg_memb_rec.program_id;
2726
2727 PV_Pg_Memberships_PVT.Create_Pg_memberships(
2728 p_api_version_number=>1.0
2729 ,p_init_msg_list => FND_API.g_false
2730 ,p_commit => FND_API.G_FALSE
2731 ,p_validation_level => FND_API.g_valid_level_full
2732 ,x_return_status => x_return_status
2733 ,x_msg_count => x_msg_count
2734 ,x_msg_data => x_msg_data
2735 ,p_memb_rec => pv_pg_memb_rec
2736 ,x_membership_id => l_membership_id
2737 );
2738
2739 IF x_return_status = FND_API.g_ret_sts_error THEN
2740 RAISE FND_API.g_exc_error;
2741 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
2742 RAISE FND_API.g_exc_unexpected_error;
2743 END IF;
2744
2745 ----DBMS_OUTPUT.PUT_LINE('after creating enrollment'||l_membership_id);
2746
2747 --update the enrollment requests table with the approvalstatus and tentative start and end dates.
2748 --this api is called after creating membership record is to get the membership start and end dates
2749 --from the memberhip record.
2750
2751 l_enrq_rec.tentative_start_date:= pv_pg_memb_rec.start_date;
2752 l_enrq_rec.tentative_end_date:= pv_pg_memb_rec.original_end_date;
2753
2754 PV_Pg_Enrl_Requests_PVT.Update_Pg_Enrl_Requests(
2755 p_api_version_number => 1.0
2756 ,p_init_msg_list => FND_API.g_false
2757 ,p_commit => FND_API.G_FALSE
2758 ,p_validation_level => FND_API.g_valid_level_full
2759 ,x_return_status => x_return_status
2760 ,x_msg_count => x_msg_count
2761 ,x_msg_data => x_msg_data
2762 ,p_enrl_request_rec => l_enrq_rec );
2763
2764 IF x_return_status = FND_API.g_ret_sts_error THEN
2765 RAISE FND_API.g_exc_error;
2766 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
2767 RAISE FND_API.g_exc_unexpected_error;
2768 END IF;
2769
2770 l_param_tbl_var(1).param_name := 'PROGRAM_NAME';
2771 l_param_tbl_var(1).param_value := l_program_name;
2772
2773 PVX_UTILITY_PVT.create_history_log
2774 ( p_arc_history_for_entity_code => 'ENRQ'
2775 ,p_history_for_entity_id => enrl_request_id
2776 ,p_history_category_code => 'APPROVAL'
2777 ,p_message_code => 'PV_ENR_REQ_APPROVED'
2778 ,p_comments => null
2779 ,p_partner_id => l_partner_id
2780 ,p_access_level_flag => 'P'
2781 ,p_interaction_level => PVX_Utility_PVT.G_INTERACTION_LEVEL_50
2782 ,p_log_params_tbl => l_param_tbl_var
2783 ,p_init_msg_list => FND_API.g_false
2784 ,p_commit => FND_API.G_FALSE
2785 ,x_return_status => x_return_status
2786 ,x_msg_count => x_msg_count
2787 ,x_msg_data => x_msg_data
2788 );
2789
2790 ----DBMS_OUTPUT.PUT_LINE('after log');
2791 IF x_return_status = FND_API.g_ret_sts_error THEN
2792 RAISE FND_API.g_exc_error;
2793 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
2794 RAISE FND_API.g_exc_unexpected_error;
2795 END IF;
2796
2797 PVX_UTILITY_PVT.create_history_log
2798 ( p_arc_history_for_entity_code => 'MEMBERSHIP'
2799 ,p_history_for_entity_id => enrl_request_id
2800 ,p_history_category_code => 'APPROVAL'
2801 ,p_message_code => 'PV_PRGM_MEMB_CREATED'
2802 ,p_comments => null
2803 ,p_partner_id => l_partner_id
2804 ,p_access_level_flag => 'P'
2805 ,p_interaction_level => PVX_Utility_PVT.G_INTERACTION_LEVEL_50
2806 ,p_log_params_tbl => l_param_tbl_var
2807 ,p_init_msg_list => FND_API.g_false
2808 ,p_commit => FND_API.G_FALSE
2809 ,x_return_status => x_return_status
2810 ,x_msg_count => x_msg_count
2811 ,x_msg_data => x_msg_data
2812 );
2813
2814 ----DBMS_OUTPUT.PUT_LINE('after log');
2815 IF x_return_status = FND_API.g_ret_sts_error THEN
2816 RAISE FND_API.g_exc_error;
2817 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
2818 RAISE FND_API.g_exc_unexpected_error;
2819 END IF;
2820
2821 IF l_enrollment_type_code='UPGRADE' THEN
2822 --end date the previous enrollment
2823
2824 FOR rec_cur in prev_memb_id_cur(enrl_request_id) LOOP
2825
2826 OPEN membership_dtl_cur(rec_cur.prev_membership_id);
2827 FETCH membership_dtl_cur into pv_pg_prev_memb_rec.object_version_number;
2828 CLOSE membership_dtl_cur;
2829
2830 pv_pg_prev_memb_rec.membership_id:=rec_cur.prev_membership_id;
2831 pv_pg_prev_memb_rec.actual_end_date:=sysdate;
2832 pv_pg_prev_memb_rec.membership_status_code:='UPGRADED';
2833
2834 PV_Pg_Memberships_PVT.Update_pg_memberships(
2835 p_api_version_number => 1.0
2836 ,p_init_msg_list => FND_API.g_false
2837 ,p_commit => FND_API.G_FALSE
2838 ,p_validation_level => FND_API.g_valid_level_full
2839 ,x_return_status => x_return_status
2840 ,x_msg_count => x_msg_count
2841 ,x_msg_data => x_msg_data
2842 ,p_memb_rec => pv_pg_prev_memb_rec
2843 );
2844
2845
2846 IF x_return_status = FND_API.g_ret_sts_error THEN
2847 RAISE FND_API.g_exc_error;
2848 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
2849 RAISE FND_API.g_exc_unexpected_error;
2850 END IF;
2851
2852 --call pv_pg_mmbr_transitions table api
2853 --confirm the business logic below
2854
2855 l_mmbr_tran_rec.FROM_MEMBERSHIP_ID:=rec_cur.prev_membership_id;
2856 l_mmbr_tran_rec.TO_MEMBERSHIP_ID:=l_membership_id;
2857
2858
2859 pv_pg_mmbr_transitions_PVT.Create_Mmbr_Trans
2860 ( p_api_version_number =>1.0
2861 ,p_init_msg_list => FND_API.g_false
2862 ,p_commit => FND_API.G_FALSE
2863 ,p_validation_level => FND_API.g_valid_level_full
2864 ,x_return_status => x_return_status
2865 ,x_msg_count => x_msg_count
2866 ,x_msg_data => x_msg_data
2867 ,p_mmbr_tran_rec => l_mmbr_tran_rec
2868 ,x_mmbr_transition_id => l_mmbr_transition_id
2869 );
2870
2871 IF x_return_status = FND_API.g_ret_sts_error THEN
2872 RAISE FND_API.g_exc_error;
2873 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
2874 RAISE FND_API.g_exc_unexpected_error;
2875 END IF;
2876
2877 END LOOP ;
2878 END IF;--end of processing if its upgrade
2879
2880 IF l_enrollment_type_code='RENEW' THEN
2881
2882
2883 OPEN prev_memb_id_cur(enrl_request_id);
2884 FETCH prev_memb_id_cur into l_previous_membership_id;
2885 CLOSE prev_memb_id_cur;
2886
2887 pv_pg_prev_memb_rec.membership_id:=l_previous_membership_id;
2888
2889 IF pv_pg_memb_rec.membership_status_code='ACTIVE' THEN
2890 pv_pg_prev_memb_rec.membership_status_code:='RENEWED';
2891 END IF;
2892
2893 OPEN membership_dtl_cur(l_previous_membership_id);
2894 FETCH membership_dtl_cur into pv_pg_prev_memb_rec.object_version_number;
2895 CLOSE membership_dtl_cur;
2896
2897
2898 PV_Pg_Memberships_PVT.Update_Pg_memberships(
2899 p_api_version_number => 1.0
2900 ,p_init_msg_list => FND_API.g_false
2901 ,p_commit => FND_API.G_FALSE
2902 ,p_validation_level => FND_API.g_valid_level_full
2903 ,x_return_status => x_return_status
2904 ,x_msg_count => x_msg_count
2905 ,x_msg_data => x_msg_data
2906 ,p_memb_rec => pv_pg_prev_memb_rec
2907 );
2908
2909
2910 IF x_return_status = FND_API.g_ret_sts_error THEN
2911 RAISE FND_API.g_exc_error;
2912 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
2913 RAISE FND_API.g_exc_unexpected_error;
2914 END IF;
2915
2916 --call pv_pg_mmbr_transitions table api
2917 l_mmbr_tran_rec.FROM_MEMBERSHIP_ID:=l_previous_membership_id;
2918 l_mmbr_tran_rec.TO_MEMBERSHIP_ID:=l_membership_id;
2919
2920 pv_pg_mmbr_transitions_PVT.Create_Mmbr_Trans(
2921 p_api_version_number => 1.0
2922 ,p_init_msg_list => FND_API.g_false
2923 ,p_commit => FND_API.G_FALSE
2924 ,p_validation_level => FND_API.g_valid_level_full
2925 ,x_return_status => x_return_status
2926 ,x_msg_count => x_msg_count
2927 ,x_msg_data => x_msg_data
2928 ,p_mmbr_tran_rec => l_mmbr_tran_rec
2929 ,x_mmbr_transition_id => l_mmbr_transition_id
2930 );
2931
2932
2933 END IF; --end of renewal if
2934
2935 /* call responsiblity management api. that api will take care of granting/revoking responsibilties
2936 for currnet memberships or previous memberships as required */
2937
2938 Pv_User_Resp_Pvt.manage_memb_resp
2939 ( p_api_version_number => 1.0
2940 ,p_init_msg_list => Fnd_Api.g_false
2941 ,p_commit => Fnd_Api.g_false
2942 ,p_membership_id => l_membership_id
2943 ,x_return_status => x_return_status
2944 ,x_msg_count => x_msg_count
2945 ,x_msg_data => x_msg_data
2946 );
2947 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2948 RAISE FND_API.G_EXC_ERROR;
2949 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2950 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2951 END IF;
2952
2953 --store the attribute values at the time of enrollment approvals
2954 OPEN check_attr_exist(l_program_id);
2955 FETCH check_attr_exist INTO l_attr_id_exists;
2956 CLOSE check_attr_exist;
2957
2958 IF l_attr_id_exists='X' THEN
2959 OPEN attribute_id_csr(l_program_id);
2960 FETCH attribute_id_csr BULK COLLECT INTO l_attr_id_tbl;
2961 CLOSE attribute_id_csr;
2962
2963 pv_enty_attr_value_pub.copy_partner_attr_values
2964 ( p_api_version_number =>1.0
2965 ,p_init_msg_list => fnd_api.g_false
2966 ,p_commit => fnd_api.g_false
2967 ,p_validation_level => fnd_api.g_valid_level_full
2968 ,x_return_status => x_return_status
2969 ,x_msg_count => x_msg_count
2970 ,x_msg_data => x_msg_data
2971 ,p_attr_id_tbl => l_attr_id_tbl
2972 ,p_entity => 'ENRQ'
2973 ,p_entity_id => enrl_request_id
2974 ,p_partner_id => l_partner_id
2975 );
2976 ----DBMS_OUTPUT.PUT_LINE('after copy');
2977 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2978 RAISE FND_API.G_EXC_ERROR;
2979 ELSIF x_return_status =FND_API.G_RET_STS_UNEXP_ERROR THEN
2980 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2981 END IF;
2982 END IF; --end of if for coying attributes if there is any attribute defined
2983
2984
2985 ----DBMS_OUTPUT.PUT_LINE('after copy2');
2986 IF l_order_header_id is not null THEN
2987 ----DBMS_OUTPUT.PUT_LINE('before book order');
2988 PV_ORDER_MGMT_PVT.book_order
2989 (
2990 p_api_version_number =>1.0
2991 ,p_init_msg_list => Fnd_Api.g_false
2992 ,p_commit => Fnd_Api.g_false
2993 ,p_order_header_id => l_order_header_id
2994 ,x_return_status => x_return_status
2995 ,x_msg_count => x_msg_count
2996 ,x_msg_data => x_msg_data
2997 );
2998 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2999 RAISE FND_API.G_EXC_ERROR;
3000 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3001 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3002 END IF;
3003 ----DBMS_OUTPUT.PUT_LINE('after book order');
3004 END IF;
3005
3006 -- send the welcome alert from workflow
3007 ----DBMS_OUTPUT.PUT_LINE('before notify');
3008 /**
3009 PV_PG_NOTIF_UTILITY_PVT.send_welcome_notif
3010 ( p_api_version => 1.0
3011 ,p_init_msg_list => Fnd_Api.g_false
3012 ,p_commit => Fnd_Api.g_false
3013 ,p_validation_level => FND_API.g_valid_level_full
3014 ,x_return_status => x_return_status
3015 ,x_msg_count => x_msg_count
3016 ,x_msg_data => x_msg_data
3017 ,p_membership_id => l_membership_id
3018 ) ;
3019 */
3020 -- calling the new workflow process in 11.5.10 :pukken
3021 PV_PG_NOTIF_UTILITY_PVT.Send_Workflow_Notification
3022 (
3023 p_api_version_number => 1.0
3024 , p_init_msg_list => Fnd_Api.g_false
3025 , p_commit => Fnd_Api.g_false
3026 , p_validation_level => FND_API.g_valid_level_full
3027 , p_context_id => l_partner_id
3028 , p_context_code => 'PARTNER'
3029 , p_target_ctgry => 'PARTNER'
3030 , p_target_ctgry_pt_id => l_partner_id
3031 , p_notif_event_code => 'PG_WELCOME'
3032 , p_entity_id => enrl_request_id
3033 , p_entity_code => 'ENRQ'
3034 , p_wait_time => 0
3035 , x_return_status => x_return_status
3036 , x_msg_count => x_msg_count
3037 , x_msg_data => x_msg_data
3038 );
3039
3040 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
3041 RAISE FND_API.G_EXC_ERROR;
3042 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3043 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3044 END IF;
3045
3046
3047
3048
3049
3050 ELSE --end of approved code
3051 --the code below is for enrollments that are rejected by any appprover.
3052 --update the enrollment requests table with the approvalstatus
3053 PV_Pg_Enrl_Requests_PVT.Update_Pg_Enrl_Requests(
3054 p_api_version_number => 1.0
3055 ,p_init_msg_list => FND_API.g_false
3056 ,p_commit => FND_API.G_FALSE
3057 ,p_validation_level => FND_API.g_valid_level_full
3058 ,x_return_status => x_return_status
3059 ,x_msg_count => x_msg_count
3060 ,x_msg_data => x_msg_data
3061 ,p_enrl_request_rec => l_enrq_rec
3062 );
3063
3064 IF x_return_status = FND_API.g_ret_sts_error THEN
3065 RAISE FND_API.g_exc_error;
3066 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
3067 RAISE FND_API.g_exc_unexpected_error;
3068 END IF;
3069
3070
3071 IF l_order_header_id is not null THEN
3072 PV_ORDER_MGMT_PVT.cancel_order
3073 (
3074 p_api_version_number =>1.0
3075 ,p_init_msg_list => Fnd_Api.g_false
3076 ,p_commit => Fnd_Api.g_false
3077 ,p_order_header_id => l_order_header_id
3078 ,x_return_status => x_return_status
3079 ,x_msg_count => x_msg_count
3080 ,x_msg_data => x_msg_data
3081 );
3082 END IF;
3083
3084 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
3085 RAISE FND_API.G_EXC_ERROR;
3086 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3087 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3088 END IF;
3089
3090
3091 --store the attribute values at the time of enrollment approvals
3092 OPEN check_attr_exist(l_program_id);
3093 FETCH check_attr_exist INTO l_attr_id_exists;
3094 CLOSE check_attr_exist;
3095
3096 IF l_attr_id_exists='X' THEN
3097 OPEN attribute_id_csr(l_program_id);
3098 FETCH attribute_id_csr BULK COLLECT INTO l_attr_id_tbl;
3099 CLOSE attribute_id_csr;
3100
3101 pv_enty_attr_value_pub.copy_partner_attr_values
3102 ( p_api_version_number =>1.0
3103 ,p_init_msg_list => fnd_api.g_false
3104 ,p_commit => fnd_api.g_false
3105 ,p_validation_level => fnd_api.g_valid_level_full
3106 ,x_return_status => x_return_status
3107 ,x_msg_count => x_msg_count
3108 ,x_msg_data => x_msg_data
3109 ,p_attr_id_tbl => l_attr_id_tbl
3110 ,p_entity => 'ENRQ'
3111 ,p_entity_id => enrl_request_id
3112 ,p_partner_id => l_partner_id
3113 );
3114 ----DBMS_OUTPUT.PUT_LINE('after copy');
3115 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
3116 RAISE FND_API.G_EXC_ERROR;
3117 ELSIF x_return_status =FND_API.G_RET_STS_UNEXP_ERROR THEN
3118 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3119 END IF;
3120 END IF; --end of if for coying attributes if there is any attribute defined
3121
3122
3123 --send workflow notification to partner about the rejection of enrollment request
3124 /**PV_PG_NOTIF_UTILITY_PVT.send_rejection_notif
3125 ( p_api_version => 1.0
3126 ,p_init_msg_list => Fnd_Api.g_false
3127 ,p_commit => Fnd_Api.g_false
3128 ,p_validation_level => FND_API.g_valid_level_full
3129 ,x_return_status => x_return_status
3130 ,x_msg_count => x_msg_count
3131 ,x_msg_data => x_msg_data
3132 ,p_enrl_request_id => enrl_request_id
3133 ) ;
3134 */
3135 -- calling the new workflow process in 11.5.10 pukken
3136 PV_PG_NOTIF_UTILITY_PVT.Send_Workflow_Notification
3137 (
3138 p_api_version_number => 1.0
3139 , p_init_msg_list => Fnd_Api.g_false
3140 , p_commit => Fnd_Api.g_false
3141 , p_validation_level => FND_API.g_valid_level_full
3142 , p_context_id => l_partner_id
3143 , p_context_code => 'PARTNER'
3144 , p_target_ctgry => 'PARTNER'
3145 , p_target_ctgry_pt_id => l_partner_id
3146 , p_notif_event_code => 'PG_REJECT'
3147 , p_entity_id => enrl_request_id
3148 , p_entity_code => 'ENRQ'
3149 , p_wait_time => 0
3150 , x_return_status => x_return_status
3151 , x_msg_count => x_msg_count
3152 , x_msg_data => x_msg_data
3153 );
3154
3155 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
3156 RAISE FND_API.G_EXC_ERROR;
3157 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3158 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3159 END IF;
3160 ----DBMS_OUTPUT.PUT_LINE('after rejection notify');
3161
3162 END IF; --end of if rejected
3163 -- also if this is a subsidiary need to send notifictaion to global
3164 -- also need to make a call to send notifictaion to VAD incase this enrollment is beacuse of VAD inviation
3165 -- that IMP's enrollment had been approved.
3166 send_notifications
3167 (
3168 p_api_version_number => 1.0
3169 , p_init_msg_list => Fnd_Api.g_false
3170 , p_commit => Fnd_Api.g_false
3171 , p_validation_level => FND_API.g_valid_level_full
3172 , p_partner_id => l_partner_id
3173 , p_enrl_request_id => enrl_request_id
3174 , p_memb_type => null
3175 , p_enrq_status => approvalStatus
3176 , x_return_status => x_return_status
3177 , x_msg_count => x_msg_count
3178 , x_msg_data => x_msg_data
3179 );
3180 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
3181 RAISE FND_API.G_EXC_ERROR;
3182 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3183 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3184 END IF;
3185
3186 EXCEPTION
3187
3188 WHEN OTHERS THEN
3189 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3190
3191 END process_response;
3192
3193 --------------------------------------------------------------------------
3194 -- PROCEDURE
3195 -- submit_enrl_req_for_approval
3196 --
3197 -- PURPOSE
3198 -- to submit an enrollment request for approval.
3199 --
3200 -- HISTORY
3201 -- 09/24/2002 pukken CREATION
3202 --------------------------------------------------------------------------
3203
3204 PROCEDURE submit_enrl_req_for_approval
3205 (
3206 p_api_version_number IN NUMBER
3207 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
3208 , p_commit IN VARCHAR2 := FND_API.G_FALSE
3209 , p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
3210 , enrl_request_id IN NUMBER
3211 , entity_code IN VARCHAR2
3212 , x_return_status OUT NOCOPY VARCHAR2
3213 , x_msg_count OUT NOCOPY NUMBER
3214 , x_msg_data OUT NOCOPY VARCHAR2
3215 )IS
3216
3217 CURSOR approver_dtl_cur (p_enrl_req_id number, p_person_id NUMBER,p_user_id NUMBER) IS
3218 SELECT object_version_number,entity_approver_id,approver_id,approver_type_code
3219 FROM pv_ge_temp_approvers
3220 WHERE appr_for_entity_id=p_enrl_req_id
3221 AND approver_id = DECODE( APPROVER_TYPE_CODE,'PERSON',p_person_id,'USER',p_user_id,null )
3222 AND arc_appr_for_entity_code='ENRQ';
3223
3224 CURSOR c_resource_per_cur(p_appr_id NUMBER) IS
3225 SELECT res.resource_id,res.resource_name
3226 FROM jtf_rs_resource_extns_vl res,fnd_user fnd
3227 WHERE fnd.employee_id = p_appr_id
3228 AND res.user_id=fnd.user_id;
3229
3230 CURSOR c_resource_usr_cur(p_appr_id NUMBER) IS
3231 SELECT res.resource_id,res.resource_name
3232 FROM jtf_rs_resource_extns_vl res,fnd_user fnd
3233 WHERE fnd.user_id = p_appr_id
3234 AND res.user_id=fnd.user_id;
3235
3236 CURSOR c_enrl_cur ( enrl_id NUMBER ) IS
3237 SELECT partner_id
3238 FROM pv_pg_enrl_requests
3239 WHERE enrl_request_id=enrl_id;
3240
3241 CURSOR pending_appovers_csr ( enrl_id NUMBER ) IS
3242 SELECT entity_approver_id,object_version_number
3243 FROM pv_ge_temp_approvers
3244 WHERE APPR_FOR_ENTITY_ID =enrl_id
3245 AND APPROVAL_STATUS_CODE IN ('PENDING_APPROVAL','PENDING_DEFAULT');
3246
3247 CURSOR person_id_csr ( p_user_id NUMBER ) IS
3248 SELECT person_id ,full_name
3249 FROM per_all_people_f per
3250 , FND_USER usr
3251 WHERE user_id=p_user_id
3252 AND usr.person_party_id=per.party_id;
3253
3254 CURSOR is_partner_usr_csr( p_usr_id NUMBER ) IS
3255 SELECT 'Y'
3256 FROM fnd_user usr
3257 , jtf_rs_resource_extns_vl res
3258 WHERE usr.user_id=p_usr_id
3259 AND usr.user_id=res.user_id
3260 AND res.category='PARTY';
3261
3262 l_isPartnerFlag VARCHAR2(1);
3263 --nextApprover AME_UTIL.APPROVERRECORD;
3264 pv_pg_memb_rec PV_Pg_Memberships_PVT.memb_rec_type;
3265 pv_pg_prev_memb_rec PV_Pg_Memberships_PVT.memb_rec_type;
3266 l_approver_rec Pv_Ge_Temp_Approvers_PVT.APPROVER_REC_TYPE;
3267 l_check_row_pa BOOLEAN :=FALSE;
3268 l_check_row BOOLEAN:=FALSE;
3269 l_checkrow_pending BOOLEAN:=FALSE;
3270 l_api_name CONSTANT VARCHAR2(30) := 'submit_enrl_req_for_approval';
3271 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
3272 l_api_version_number CONSTANT NUMBER := 1.0;
3273 --This value for l_default_approver_id needs to be retrieved from profile
3274 l_default_approver_id NUMBER;
3275 l_admin_id NUMBER;
3276 l_admin_type VARCHAR2(15);
3277 l_approver_id NUMBER;
3278 l_approver_type VARCHAR2(15);
3279 l_membership_id NUMBER;
3280 l_entity_approver_id NUMBER;
3281 l_itemkey VARCHAR2(30);
3282 l_object_version_number NUMBER(9);
3283 l_resource_name VARCHAR2(360);
3284 l_resource_id NUMBER;
3285 l_param_tbl_var PVX_UTILITY_PVT.log_params_tbl_type;
3286 l_partner_id NUMBER;
3287 l_display_name VARCHAR2(240);
3288 l_personid NUMBER;
3289 x_role_name VARCHAR2(320);
3290 x_role_display_name VARCHAR2(360);
3291 l_approverPersonId NUMBER;
3292 l_approverUserId NUMBER;
3293 l_rec_appr Pv_Ge_Temp_Approvers_PVT.APPROVER_REC_TYPE;
3294 l_approval_status_code VARCHAR2(30);
3295 x_approvalProcessCompleteYNOut VARCHAR2(100);
3296 x_nextApproversOut ame_util.approversTable2;
3297 xitemIndexesOut ame_util.idList;
3298 xitemClassesOut ame_util.stringList;
3299 xitemIdsOut ame_util.stringList;
3300 xitemSourcesOut ame_util.longStringList;
3301 xproductionIndexesOut ame_util.idList;
3302 xvariableNamesOut ame_util.stringList;
3303 xvariableValuesOut ame_util.stringList;
3304 xtransVariableNamesOut ame_util.stringList;
3305 xtransVariableValuesOut ame_util.stringList;
3306 adminApprRec ame_util.approverRecord2;
3307 currApprRec ame_util.approverRecord2;
3308
3309 BEGIN
3310 -- call AME api to get the next
3311 SAVEPOINT submit_enrl_req_for_approval;
3312 -- Standard call to check for call compatibility.
3313 IF NOT FND_API.Compatible_API_Call
3314 ( l_api_version_number
3315 , p_api_version_number
3316 , l_api_name
3317 , G_PKG_NAME
3318 )
3319 THEN
3320 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3321 END IF;
3322
3323 -- Initialize message list if p_init_msg_list is set to TRUE.
3324 IF FND_API.to_Boolean( p_init_msg_list ) THEN
3325 FND_MSG_PUB.initialize;
3326 END IF;
3327
3328 -- Debug Message
3329 IF (PV_DEBUG_HIGH_ON) THEN
3330 PVX_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
3331 END IF;
3332 -- Initialize API return status to SUCCESS
3333 x_return_status := FND_API.G_RET_STS_SUCCESS;
3334
3335 OPEN c_enrl_cur( enrl_request_id );
3336 FETCH c_enrl_cur INTO l_partner_id;
3337 CLOSE c_enrl_cur;
3338
3339
3340 /** Following is required as we expect AME to return their new statuses. Bug # 4879218 **/
3341 ame_util2.detailedApprovalStatusFlagYN := ame_util.booleanTrue;
3342
3343 AME_API2.getNextApprovers3
3344 ( applicationIdIn => 691
3345 , transactionTypeIn => 'ENRQ'
3346 , transactionIdIn => enrl_request_id
3347 , flagApproversAsNotifiedIn => ame_util.booleanTrue
3348 , approvalProcessCompleteYNOut => x_approvalProcessCompleteYNOut
3349 , nextApproversOut => x_nextApproversOut
3350 , itemIndexesOut => xitemIndexesOut
3351 , itemClassesOut => xitemClassesOut
3352 , itemIdsOut => xitemIdsOut
3353 , itemSourcesOut => xitemSourcesOut
3354 , productionIndexesOut => xproductionIndexesOut
3355 , variableNamesOut => xvariableNamesOut
3356 , variableValuesOut => xvariableValuesOut
3357 , transVariableNamesOut => xtransVariableNamesOut
3358 , transVariableValuesOut => xtransVariableValuesOut
3359 );
3360
3361
3362 If x_approvalProcessCompleteYNOut=ame_util2.completeFullyApproved THEN
3363 -- This means there are no more approvers to approve and approval is complete
3364 l_check_row_pa:=check_pending_approval(enrl_request_id);--this should be queried with status 'pending approval'
3365 IF l_check_row_pa =true AND g_isApproverInList=true THEN
3366
3367
3368 -- update temp approvers with status approved
3369 l_approverPersonId:= FND_GLOBAL.EMPLOYEE_ID;
3370 l_approverUserId:= FND_GLOBAL.USER_ID;
3371
3372 OPEN approver_dtl_cur(enrl_request_id,l_approverPersonId, l_approverUserId);
3373 FETCH approver_dtl_cur into l_rec_appr.object_version_number,
3374 l_rec_appr.entity_approver_id,
3375 l_rec_appr.approver_id,
3376 l_rec_appr.approver_type_code;
3377 CLOSE approver_dtl_cur;
3378
3379 IF l_rec_appr.entity_approver_id IS NOT NULL THEN
3380 l_rec_appr.approval_status_code:='APPROVED';
3381 Pv_Ge_Temp_Approvers_PVT.Update_Ptr_Enr_Temp_Appr
3382 ( p_api_version_number => 1.0
3383 , p_init_msg_list => FND_API.g_false
3384 , p_commit => FND_API.g_false
3385 , p_validation_level => FND_API.g_valid_level_full
3386 , x_return_status => x_return_status
3387 , x_msg_count => x_msg_count
3388 , x_msg_data => x_msg_data
3389 , p_approver_rec =>l_rec_appr
3390 );
3391 ----DBMS_OUTPUT.PUT_LINE('inserted into temp approvers');
3392 IF x_return_status = FND_API.g_ret_sts_error THEN
3393 RAISE FND_API.g_exc_error;
3394 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
3395 RAISE FND_API.g_exc_unexpected_error;
3396 END IF;
3397 END IF;
3398
3399 process_response
3400 ( enrl_request_id =>enrl_request_id
3401 , approvalStatus =>'APPROVED'
3402 , x_return_status =>x_return_status
3403 , x_msg_count =>x_msg_count
3404 , x_msg_data =>x_msg_data
3405 );
3406
3407 IF x_return_status = FND_API.g_ret_sts_error THEN
3408 RAISE FND_API.g_exc_error;
3409 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
3410 RAISE FND_API.g_exc_unexpected_error;
3411 END IF;
3412
3413 ELSIF l_check_row_pa =true AND g_isApproverInList=false THEN
3414
3415 --call process response
3416 /* there could have been approvers for the enrollment and
3417 since the rule changed, they are no longer valid approvers and
3418 hence their approval status should be updated
3419 */
3420 FOR x in pending_appovers_csr(enrl_request_id) LOOP
3421 l_rec_appr.entity_approver_id :=x.entity_approver_id;
3422 l_rec_appr.object_version_number:=x.object_version_number;
3423 l_rec_appr.approval_status_code:='APPROVER_CHANGED';
3424 Pv_Ge_Temp_Approvers_PVT.Update_Ptr_Enr_Temp_Appr
3425 ( p_api_version_number => 1.0
3426 , p_init_msg_list => FND_API.g_false
3427 , p_commit => FND_API.g_false
3428 , p_validation_level => FND_API.g_valid_level_full
3429 , x_return_status => x_return_status
3430 , x_msg_count => x_msg_count
3431 , x_msg_data => x_msg_data
3432 , p_approver_rec =>l_rec_appr
3433 );
3434 ----DBMS_OUTPUT.PUT_LINE('inserted into temp approvers');
3435 IF x_return_status = FND_API.g_ret_sts_error THEN
3436 RAISE FND_API.g_exc_error;
3437 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
3438 RAISE FND_API.g_exc_unexpected_error;
3439 END IF;
3440 END LOOP;
3441
3442 process_response
3443 ( enrl_request_id =>enrl_request_id
3444 , approvalStatus =>'APPROVED'
3445 , x_return_status =>x_return_status
3446 , x_msg_count =>x_msg_count
3447 , x_msg_data =>x_msg_data
3448 );
3449 ----DBMS_OUTPUT.PUT_LINE('inserted into temp approvers');
3450 IF x_return_status = FND_API.g_ret_sts_error THEN
3451 RAISE FND_API.g_exc_error;
3452 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
3453 RAISE FND_API.g_exc_unexpected_error;
3454 END IF;
3455
3456
3457 END IF;
3458
3459
3460 ELSIF x_approvalProcessCompleteYNOut=ame_util2.completeNoApprovers THEN
3461 -- This means no appprovers are returned by OAM which means route the request to default approver
3462
3463 /**this means that there is no rule satisfying the criteria. so find OUT the
3464 the default approver from the profile value and send the FYI
3465 notification to the default approver. if the request
3466 was sent to default approver, OAM does not record that and we cannot update OAM in that case.
3467 We store the information in Pv_Ge_Temp_Approvers table that the request is awating approval
3468 from the default approver and so the approval status code is PENDING_DEFAULT
3469 */
3470 l_default_approver_id:= isnumber( FND_PROFILE.VALUE('PV_ENRQ_DEFAULT_APPR') );
3471 IF ( l_default_approver_id is NULL ) THEN
3472 FND_MESSAGE.set_name('PV', 'PV_ENRQ_APPR_NOT_SET');
3473 FND_MSG_PUB.add;
3474 RAISE FND_API.G_EXC_ERROR;
3475 END IF;
3476
3477
3478 l_approver_rec.arc_appr_for_entity_code :='ENRQ';
3479 l_approver_rec.appr_for_entity_id :=enrl_request_id;
3480 -- get the person id for the default approver
3481 OPEN person_id_csr (l_default_approver_id);
3482 FETCH person_id_csr INTO l_approver_rec.approver_id,l_display_name;
3483 CLOSE person_id_csr;
3484 l_approver_rec.approver_type_code:='PERSON';
3485 l_approver_rec.approval_status_code:='PENDING_DEFAULT';
3486 -- check whether there exists a row for this approver for this enrollment request
3487 CheckApprInTempApprTable(enrl_request_id,l_approver_rec.approver_id,l_entity_approver_id,l_object_version_number,l_approval_status_code );
3488
3489 IF l_entity_approver_id IS NOT NULL THEN
3490 -- get approval status also and if its pending approval, approve it..otherwise put status as pending default
3491 /* rare scenario
3492 if current approver approving it and if rule changed in AME and no rule satisfies ..
3493 means route to default approver and if current approver is also default approver ,
3494 then enrollment should be approved, else put it as pending default and sent notification
3495 */
3496 l_approver_rec.entity_approver_id :=l_entity_approver_id;
3497 l_approver_rec.object_version_number:=l_object_version_number;
3498
3499 IF g_approver_response='APPROVED' THEN
3500 l_approver_rec.approval_status_code:=g_approver_response;
3501 IF l_approval_status_code='PENDING_APPROVAL' THEN
3502 /* there could have been approvers for the enrollment and
3503 since the rule changed, they are no longer valid approvers and
3504 hence their approval status should be updated
3505 */
3506
3507 Pv_Ge_Temp_Approvers_PVT.Update_Ptr_Enr_Temp_Appr
3508 ( p_api_version_number => 1.0
3509 , p_init_msg_list => FND_API.g_false
3510 , p_commit => FND_API.g_false
3511 , p_validation_level => FND_API.g_valid_level_full
3512 , x_return_status => x_return_status
3513 , x_msg_count => x_msg_count
3514 , x_msg_data => x_msg_data
3515 , p_approver_rec =>l_approver_rec
3516
3517 );
3518 ----DBMS_OUTPUT.PUT_LINE('inserted into temp approvers');
3519 IF x_return_status = FND_API.g_ret_sts_error THEN
3520 RAISE FND_API.g_exc_error;
3521 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
3522 RAISE FND_API.g_exc_unexpected_error;
3523 END IF;
3524 --call process response
3525 /* there could have been approvers for the enrollment and
3526 since the rule changed, they are no longer valid approvers and
3527 hence their approval status should be updated
3528 */
3529 FOR x in pending_appovers_csr(enrl_request_id) LOOP
3530 l_rec_appr.entity_approver_id :=x.entity_approver_id;
3531 l_rec_appr.object_version_number:=x.object_version_number;
3532 l_rec_appr.approval_status_code:='APPROVER_CHANGED';
3533 Pv_Ge_Temp_Approvers_PVT.Update_Ptr_Enr_Temp_Appr
3534 ( p_api_version_number => 1.0
3535 , p_init_msg_list => FND_API.g_false
3536 , p_commit => FND_API.g_false
3537 , p_validation_level => FND_API.g_valid_level_full
3538 , x_return_status => x_return_status
3539 , x_msg_count => x_msg_count
3540 , x_msg_data => x_msg_data
3541 , p_approver_rec =>l_rec_appr
3542 );
3543 ----DBMS_OUTPUT.PUT_LINE('inserted into temp approvers');
3544 IF x_return_status = FND_API.g_ret_sts_error THEN
3545 RAISE FND_API.g_exc_error;
3546 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
3547 RAISE FND_API.g_exc_unexpected_error;
3548 END IF;
3549 END LOOP;
3550
3551 process_response
3552 ( enrl_request_id =>enrl_request_id
3553 , approvalStatus =>g_approver_response
3554 , x_return_status =>x_return_status
3555 , x_msg_count =>x_msg_count
3556 , x_msg_data =>x_msg_data
3557 );
3558 ----DBMS_OUTPUT.PUT_LINE('inserted into temp approvers');
3559 IF x_return_status = FND_API.g_ret_sts_error THEN
3560 RAISE FND_API.g_exc_error;
3561 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
3562 RAISE FND_API.g_exc_unexpected_error;
3563 END IF;
3564 ELSE
3565 Pv_Ge_Temp_Approvers_PVT.Update_Ptr_Enr_Temp_Appr
3566 ( p_api_version_number => 1.0
3567 , p_init_msg_list => FND_API.g_false
3568 , p_commit => FND_API.g_false
3569 , p_validation_level => FND_API.g_valid_level_full
3570 , x_return_status => x_return_status
3571 , x_msg_count => x_msg_count
3572 , x_msg_data => x_msg_data
3573 , p_approver_rec =>l_approver_rec
3574
3575 );
3576 ----DBMS_OUTPUT.PUT_LINE('inserted into temp approvers');
3577 IF x_return_status = FND_API.g_ret_sts_error THEN
3578 RAISE FND_API.g_exc_error;
3579 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
3580 RAISE FND_API.g_exc_unexpected_error;
3581 END IF;
3582 WF_DIRECTORY.getrolename
3583 ( p_orig_system => 'PER'
3584 , p_orig_system_id => l_approver_rec.approver_id
3585 , p_name => x_role_name
3586 , p_display_name => x_role_display_name
3587 );
3588
3589 StartProcess
3590 ( p_object_id => enrl_request_id
3591 , p_object_type => 'ENRQ'
3592 , processName => 'PV_APPROVER_NOTIFICATIONS'
3593 , itemtype => 'PVERAPNT'
3594 , p_entity_approver_id => l_entity_approver_id
3595 , p_role_name => x_role_name
3596 , p_display_name => x_role_display_name
3597 , x_itemkey => l_itemkey
3598 ) ;
3599 END IF;
3600 ELSE
3601 -- means if its rejected
3602 l_approver_rec.approval_status_code:=g_approver_response;
3603 Pv_Ge_Temp_Approvers_PVT.Update_Ptr_Enr_Temp_Appr
3604 ( p_api_version_number => 1.0
3605 , p_init_msg_list => FND_API.g_false
3606 , p_commit => FND_API.g_false
3607 , p_validation_level => FND_API.g_valid_level_full
3608 , x_return_status => x_return_status
3609 , x_msg_count => x_msg_count
3610 , x_msg_data => x_msg_data
3611 , p_approver_rec =>l_approver_rec
3612
3613 );
3614 ----DBMS_OUTPUT.PUT_LINE('inserted into temp approvers');
3615 IF x_return_status = FND_API.g_ret_sts_error THEN
3616 RAISE FND_API.g_exc_error;
3617 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
3618 RAISE FND_API.g_exc_unexpected_error;
3619 END IF;
3620 FOR x in pending_appovers_csr(enrl_request_id) LOOP
3621 l_rec_appr.entity_approver_id :=x.entity_approver_id;
3622 l_rec_appr.object_version_number:=x.object_version_number;
3623 l_rec_appr.approval_status_code:='APPROVER_CHANGED';
3624 Pv_Ge_Temp_Approvers_PVT.Update_Ptr_Enr_Temp_Appr
3625 ( p_api_version_number => 1.0
3626 , p_init_msg_list => FND_API.g_false
3627 , p_commit => FND_API.g_false
3628 , p_validation_level => FND_API.g_valid_level_full
3629 , x_return_status => x_return_status
3630 , x_msg_count => x_msg_count
3631 , x_msg_data => x_msg_data
3632 , p_approver_rec =>l_rec_appr
3633 );
3634 ----DBMS_OUTPUT.PUT_LINE('inserted into temp approvers');
3635 IF x_return_status = FND_API.g_ret_sts_error THEN
3636 RAISE FND_API.g_exc_error;
3637 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
3638 RAISE FND_API.g_exc_unexpected_error;
3639 END IF;
3640 END LOOP;
3641 --call process response
3642 process_response
3643 ( enrl_request_id =>enrl_request_id
3644 , approvalStatus =>g_approver_response
3645 , x_return_status =>x_return_status
3646 , x_msg_count =>x_msg_count
3647 , x_msg_data =>x_msg_data
3648 );
3649 ----DBMS_OUTPUT.PUT_LINE('inserted into temp approvers');
3650 IF x_return_status = FND_API.g_ret_sts_error THEN
3651 RAISE FND_API.g_exc_error;
3652 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
3653 RAISE FND_API.g_exc_unexpected_error;
3654 END IF;
3655 END IF;
3656 ELSE
3657 /* there could have been approvers for the enrollment and
3658 since the rule changed, they are no longer valid approvers and
3659 hence their approval status should be updated
3660 */
3661 FOR x in pending_appovers_csr(enrl_request_id) LOOP
3662 l_rec_appr.entity_approver_id :=x.entity_approver_id;
3663 l_rec_appr.object_version_number:=x.object_version_number;
3664 l_rec_appr.approval_status_code:='APPROVER_CHANGED';
3665 Pv_Ge_Temp_Approvers_PVT.Update_Ptr_Enr_Temp_Appr
3666 ( p_api_version_number => 1.0
3667 , p_init_msg_list => FND_API.g_false
3668 , p_commit => FND_API.g_false
3669 , p_validation_level => FND_API.g_valid_level_full
3670 , x_return_status => x_return_status
3671 , x_msg_count => x_msg_count
3672 , x_msg_data => x_msg_data
3673 , p_approver_rec =>l_rec_appr
3674 );
3675 IF x_return_status = FND_API.g_ret_sts_error THEN
3676 RAISE FND_API.g_exc_error;
3677 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
3678 RAISE FND_API.g_exc_unexpected_error;
3679 END IF;
3680 END LOOP;
3681
3682 /* create a row for the new default approver with status pending_default */
3683 Pv_Ge_Temp_Approvers_PVT.Create_Ptr_Enr_Temp_Appr
3684 ( p_api_version_number =>l_api_version_number
3685 , x_return_status =>x_return_status
3686 , x_msg_count =>x_msg_count
3687 , x_msg_data =>x_msg_data
3688 , p_approver_rec =>l_approver_rec
3689 , x_entity_approver_id =>l_entity_approver_id
3690 );
3691
3692 ----DBMS_OUTPUT.PUT_LINE('inserted into temp approvers');
3693 IF x_return_status = FND_API.g_ret_sts_error THEN
3694 RAISE FND_API.g_exc_error;
3695 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
3696 RAISE FND_API.g_exc_unexpected_error;
3697 END IF;
3698 -- also log into enrollment history that its beem send to default approver.
3699
3700 l_param_tbl_var(1).param_name := 'APPROVER_NAME';
3701 l_param_tbl_var(1).param_value := l_display_name;
3702 PVX_UTILITY_PVT.create_history_log
3703 ( p_arc_history_for_entity_code => 'ENRQ'
3704 , p_history_for_entity_id => enrl_request_id
3705 , p_history_category_code => 'APPROVAL'
3706 , p_message_code => 'PV_ENR_REQ_TO_DEFAULT_APPR'
3707 , p_comments => null
3708 , p_partner_id => l_partner_id
3709 , p_access_level_flag => 'P'
3710 , p_interaction_level => PVX_Utility_PVT.G_INTERACTION_LEVEL_10
3711 , p_log_params_tbl => l_param_tbl_var
3712 , p_init_msg_list => FND_API.g_false
3713 , p_commit => FND_API.G_FALSE
3714 , x_return_status => x_return_status
3715 , x_msg_count => x_msg_count
3716 , x_msg_data => x_msg_data
3717 );
3718 IF x_return_status = FND_API.g_ret_sts_error THEN
3719 RAISE FND_API.g_exc_error;
3720 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
3721 RAISE FND_API.g_exc_unexpected_error;
3722 END IF;
3723 --call workflow process
3724
3725 WF_DIRECTORY.getrolename
3726 ( p_orig_system => 'PER'
3727 , p_orig_system_id => l_approver_rec.approver_id
3728 , p_name => x_role_name
3729 , p_display_name => x_role_display_name
3730 );
3731
3732 StartProcess
3733 ( p_object_id => enrl_request_id
3734 , p_object_type => 'ENRQ'
3735 , processName => 'PV_APPROVER_NOTIFICATIONS'
3736 , itemtype => 'PVERAPNT'
3737 , p_entity_approver_id => l_entity_approver_id
3738 , p_role_name => x_role_name
3739 , p_display_name => x_role_display_name
3740 , x_itemkey => l_itemkey
3741 ) ;
3742 END IF;
3743 ----DBMS_OUTPUT.PUT_LINE('after finishing workflow process');
3744
3745 ELSIF x_approvalProcessCompleteYNOut = ame_util2.notCompleted THEN
3746 /* We need to update temp approvers table record with approved status
3747 for the logged in user for this enrollment request. The approval status
3748 for the logged in user can be only approved in this scenario.
3749 */
3750
3751 l_approverPersonId:= FND_GLOBAL.EMPLOYEE_ID;
3752 l_approverUserId:= FND_GLOBAL.USER_ID;
3753
3754 OPEN approver_dtl_cur(enrl_request_id,l_approverPersonId, l_approverUserId);
3755 FETCH approver_dtl_cur into l_rec_appr.object_version_number,
3756 l_rec_appr.entity_approver_id,
3757 l_rec_appr.approver_id,
3758 l_rec_appr.approver_type_code;
3759 CLOSE approver_dtl_cur;
3760
3761 ----DBMS_OUTPUT.PUT_LINE('if after approval dtl _cur' || l_rec_appr.entity_approver_id );
3762 IF l_rec_appr.entity_approver_id IS NOT NULL THEN
3763 l_rec_appr.approval_status_code:=g_approver_response;
3764 Pv_Ge_Temp_Approvers_PVT.Update_Ptr_Enr_Temp_Appr
3765 ( p_api_version_number => 1.0
3766 , p_init_msg_list => FND_API.g_false
3767 , p_commit => FND_API.g_false
3768 , p_validation_level => FND_API.g_valid_level_full
3769 , x_return_status => x_return_status
3770 , x_msg_count => x_msg_count
3771 , x_msg_data => x_msg_data
3772 , p_approver_rec =>l_rec_appr
3773 );
3774 IF x_return_status = FND_API.g_ret_sts_error THEN
3775 RAISE FND_API.g_exc_error;
3776 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
3777 RAISE FND_API.g_exc_unexpected_error;
3778 END IF;
3779 END IF;
3780 ----DBMS_OUTPUT.PUT_LINE('iafter temp');
3781 /**if nextApprover.approval_status = ame_util.exception, it means an error has occurred
3782 it may return the admin approver or it may just return a exception status
3783 write to enrollment logs that there is an error
3784 */
3785
3786 IF x_nextApproversOut.COUNT>0 THEN
3787 adminApprRec := x_nextApproversOut(1);
3788 IF (adminApprRec.approval_status = ame_util.exceptionStatus) THEN
3789 IF adminApprRec.name IS NOT NULL THEN
3790 l_admin_id := adminApprRec.orig_system_id;
3791 l_admin_type:=adminApprRec.orig_system;
3792 l_param_tbl_var(1).param_name := 'ADMINISTRATOR';
3793 IF adminApprRec.display_name IS NULL THEN
3794 l_param_tbl_var(1).param_value := adminApprRec.name;
3795 ELSE
3796 l_param_tbl_var(1).param_value := adminApprRec.display_name;
3797 END If;
3798 ----DBMS_OUTPUT.PUT_LINE('exception');
3799 PVX_UTILITY_PVT.create_history_log
3800 ( p_arc_history_for_entity_code => 'ENRQ'
3801 , p_history_for_entity_id => enrl_request_id
3802 , p_history_category_code => 'APPROVAL'
3803 , p_message_code => 'PV_ERROR_ENR_APPROVAL'
3804 , p_comments => null
3805 , p_partner_id => l_partner_id
3806 , p_access_level_flag => 'P'
3807 , p_interaction_level => PVX_Utility_PVT.G_INTERACTION_LEVEL_10
3808 , p_log_params_tbl => l_param_tbl_var
3809 , p_init_msg_list => FND_API.g_false
3810 , p_commit => FND_API.G_FALSE
3811 , x_return_status => x_return_status
3812 , x_msg_count => x_msg_count
3813 , x_msg_data => x_msg_data
3814 );
3815 IF x_return_status = FND_API.g_ret_sts_error THEN
3816 RAISE FND_API.g_exc_error;
3817 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
3818 RAISE FND_API.g_exc_unexpected_error;
3819 END IF;
3820
3821 IF l_admin_id IS NOT NULL THEN
3822 l_approver_rec:=NULL;
3823 l_approver_rec.arc_appr_for_entity_code :='ENRQ';
3824 l_approver_rec.appr_for_entity_id :=enrl_request_id;
3825 l_approver_rec.approver_id :=l_admin_id;
3826 l_approver_rec.approver_type_code:=l_admin_type;
3827 l_approver_rec.approval_status_code:='ERROR';
3828 CheckApprInTempApprTable(enrl_request_id,l_approver_rec.approver_id,l_entity_approver_id,l_object_version_number,l_approval_status_code );
3829 IF l_entity_approver_id IS NOT NULL THEN
3830 -- update
3831 ----DBMS_OUTPUT.PUT_LINE('going for update');
3832 l_approver_rec.entity_approver_id :=l_entity_approver_id;
3833 l_approver_rec.object_version_number:=l_object_version_number;
3834 Pv_Ge_Temp_Approvers_PVT.Update_Ptr_Enr_Temp_Appr
3835 ( p_api_version_number => 1.0
3836 , p_init_msg_list => FND_API.g_false
3837 , p_commit => FND_API.g_false
3838 , p_validation_level => FND_API.g_valid_level_full
3839 , x_return_status => x_return_status
3840 , x_msg_count => x_msg_count
3841 , x_msg_data => x_msg_data
3842 , p_approver_rec =>l_approver_rec
3843
3844 );
3845 ELSE
3846 Pv_Ge_Temp_Approvers_PVT.Create_Ptr_Enr_Temp_Appr
3847 ( p_api_version_number =>l_api_version_number
3848 , x_return_status =>x_return_status
3849 , x_msg_count =>x_msg_count
3850 , x_msg_data =>x_msg_data
3851 , p_approver_rec =>l_approver_rec
3852 , x_entity_approver_id =>l_entity_approver_id
3853 );
3854 END IF;
3855 IF x_return_status = FND_API.g_ret_sts_error THEN
3856 RAISE FND_API.g_exc_error;
3857 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
3858 RAISE FND_API.g_exc_unexpected_error;
3859 END IF;
3860 END IF; --end of if if no row exists in approver table for errored records.
3861 END IF; -- end of if , if adminApprover.name is not null
3862 ELSE
3863 /*Incase of First Responder Wins, we need to make sure to temp approvers
3864 table to 'PEER_RESPONDED status' and the logic to check whether this is First responder wins is
3865 if there are more approvers and if the approval process is incomplete and if the temp
3866 approvers has any record with 'PENDING_APPROVAL' status, then it needs to be updated
3867 with status 'PEER_RESPONDED'
3868 */
3869
3870 IF x_nextApproversOut.COUNT>0 and x_approvalProcessCompleteYNOut=ame_util2.notCompleted THEN
3871 l_approver_rec:=NULL;
3872 FOR x in pending_appovers_csr(enrl_request_id) LOOP
3873 l_approver_rec.entity_approver_id :=x.entity_approver_id;
3874 l_approver_rec.object_version_number:=x.object_version_number;
3875 l_approver_rec.approval_status_code:='PEER_RESPONDED';
3876 Pv_Ge_Temp_Approvers_PVT.Update_Ptr_Enr_Temp_Appr
3877 ( p_api_version_number => 1.0
3878 , p_init_msg_list => FND_API.g_false
3879 , p_commit => FND_API.g_false
3880 , p_validation_level => FND_API.g_valid_level_full
3881 , x_return_status => x_return_status
3882 , x_msg_count => x_msg_count
3883 , x_msg_data => x_msg_data
3884 , p_approver_rec =>l_approver_rec
3885 );
3886 IF x_return_status = FND_API.g_ret_sts_error THEN
3887 RAISE FND_API.g_exc_error;
3888 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
3889 RAISE FND_API.g_exc_unexpected_error;
3890 END IF;
3891 END LOOP;
3892 END If;
3893
3894 FOR i IN 1..x_nextApproversOut.COUNT LOOP
3895 currApprRec := x_nextApproversOut(i);
3896
3897 IF currApprRec.orig_system='PER' THEN
3898 l_personid:=currApprRec.orig_system_id;
3899 l_approver_type:='PERSON';
3900 ELSE
3901 OPEN is_partner_usr_csr (FND_GLOBAL.USER_ID);
3902 FETCH is_partner_usr_csr INTO l_isPartnerFlag;
3903 CLOSE is_partner_usr_csr;
3904 IF l_isPartnerFlag IS NULL THEN
3905 FND_MESSAGE.set_name('PV', 'PV_ENRQ_INCAPPRMSG_TO_CM');
3906 ELSE
3907 FND_MESSAGE.set_name('PV', 'PV_ENRQ_INCAPPRMSG_TO_PTNR');
3908 END IF;
3909 FND_MSG_PUB.add;
3910 RAISE FND_API.G_EXC_ERROR;
3911 END IF;
3912
3913 IF l_personid IS NOT NULL THEN
3914 l_approver_rec:=NULL;
3915 -- insert into temp approvers table
3916 l_approver_rec.arc_appr_for_entity_code :='ENRQ';
3917 l_approver_rec.appr_for_entity_id :=enrl_request_id;
3918 l_approver_rec.approver_id := l_personid;
3919 l_approver_rec.approver_type_code:=l_approver_type;
3920 l_approver_rec.approval_status_code:='PENDING_APPROVAL';
3921 CheckApprInTempApprTable(enrl_request_id,l_approver_rec.approver_id,l_entity_approver_id,l_object_version_number,l_approval_status_code );
3922 IF l_entity_approver_id IS NOT NULL THEN
3923 -- update
3924 ----DBMS_OUTPUT.PUT_LINE('going for update');
3925 l_approver_rec.entity_approver_id :=l_entity_approver_id;
3926 l_approver_rec.object_version_number:=l_object_version_number;
3927 Pv_Ge_Temp_Approvers_PVT.Update_Ptr_Enr_Temp_Appr
3928 ( p_api_version_number => 1.0
3929 , p_init_msg_list => FND_API.g_false
3930 , p_commit => FND_API.g_false
3931 , p_validation_level => FND_API.g_valid_level_full
3932 , x_return_status => x_return_status
3933 , x_msg_count => x_msg_count
3934 , x_msg_data => x_msg_data
3935 , p_approver_rec =>l_approver_rec
3936
3937 );
3938 ELSE
3939 Pv_Ge_Temp_Approvers_PVT.Create_Ptr_Enr_Temp_Appr(
3940 p_api_version_number =>l_api_version_number
3941 ,x_return_status =>x_return_status
3942 ,x_msg_count =>x_msg_count
3943 ,x_msg_data =>x_msg_data
3944 ,p_approver_rec =>l_approver_rec
3945 ,x_entity_approver_id =>l_entity_approver_id
3946 );
3947 END IF;
3948 IF x_return_status = FND_API.g_ret_sts_error THEN
3949 RAISE FND_API.g_exc_error;
3950 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
3951 RAISE FND_API.g_exc_unexpected_error;
3952 END IF;
3953 END If;
3954 -- --DBMS_OUTPUT.PUT_LINE('before calling history for submiiting to approver');
3955 -- also write to the enrolllments log with the approver name
3956 -- get the resource name of the approver to log into enrollment log
3957 l_param_tbl_var(1).param_name := 'APPROVER_NAME';
3958 IF currApprRec.display_name IS NULL THEN
3959 l_param_tbl_var(1).param_value := currApprRec.name;
3960 ELSE
3961 l_param_tbl_var(1).param_value := currApprRec.display_name;
3962 END If;
3963
3964
3965 PVX_UTILITY_PVT.create_history_log
3966 ( p_arc_history_for_entity_code => 'ENRQ'
3967 , p_history_for_entity_id => enrl_request_id
3968 , p_history_category_code => 'APPROVAL'
3969 , p_message_code => 'PV_ENR_REQ_SUBMITTED_TO_APPR'
3970 , p_comments => null
3971 , p_partner_id => l_partner_id
3972 , p_access_level_flag => 'P'
3973 , p_interaction_level => PVX_Utility_PVT.G_INTERACTION_LEVEL_10
3974 , p_log_params_tbl => l_param_tbl_var
3975 , p_init_msg_list => FND_API.g_false
3976 , p_commit => FND_API.G_FALSE
3977 , x_return_status => x_return_status
3978 , x_msg_count => x_msg_count
3979 , x_msg_data => x_msg_data
3980 );
3981
3982 ----DBMS_OUTPUT.PUT_LINE('after calling history for submiiting to approver');
3983 IF x_return_status = FND_API.g_ret_sts_error THEN
3984 RAISE FND_API.g_exc_error;
3985 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
3986 RAISE FND_API.g_exc_unexpected_error;
3987 END IF;
3988
3989 StartProcess
3990 ( p_object_id => enrl_request_id
3991 , p_object_type => 'ENRQ'
3992 , processName => 'PV_APPROVER_NOTIFICATIONS'
3993 , itemtype => 'PVERAPNT'
3994 , p_entity_approver_id => l_entity_approver_id
3995 , p_role_name => currApprRec.name
3996 , p_display_name => currApprRec.display_name
3997 , x_itemkey => l_itemkey
3998 ) ;
3999
4000
4001 ----DBMS_OUTPUT.PUT_LINE('afterwlow call');
4002 END LOOP;
4003 END IF; --end of if else for admin exception or not.
4004 END IF; --end of if , if approver count>0
4005 END IF;-- end of first if
4006 IF FND_API.to_Boolean( p_commit ) THEN
4007 COMMIT WORK;
4008 END IF;
4009
4010 EXCEPTION
4011
4012 WHEN FND_API.G_EXC_ERROR THEN
4013 ROLLBACK TO submit_enrl_req_for_approval;
4014 x_return_status := FND_API.G_RET_STS_ERROR;
4015 -- Standard call to get message count and if count=1, get the message
4016 FND_MSG_PUB.Count_And_Get (
4017 p_encoded => FND_API.G_FALSE
4018 ,p_count => x_msg_count
4019 ,p_data => x_msg_data
4020 );
4021 --write to enrollment log that an error has occured?. how do we handle the situation if an error occured in process
4022 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4023 ROLLBACK TO submit_enrl_req_for_approval;
4024 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4025 -- Standard call to get message count and if count=1, get the message
4026 FND_MSG_PUB.Count_And_Get (
4027 p_encoded => FND_API.G_FALSE
4028 ,p_count => x_msg_count
4029 ,p_data => x_msg_data
4030 );
4031
4032 WHEN OTHERS THEN
4033 ROLLBACK TO submit_enrl_req_for_approval;
4034 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4035 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4036 THEN
4037 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
4038 END IF;
4039 -- Standard call to get message count and if count=1, get the message
4040 FND_MSG_PUB.Count_And_Get (
4041 p_encoded => FND_API.G_FALSE
4042 ,p_count => x_msg_count
4043 ,p_data => x_msg_data
4044 );
4045 END submit_enrl_req_for_approval;
4046
4047 --------------------------------------------------------------------------
4048 -- PROCEDURE
4049 -- update_enrl_req_status
4050 --
4051 -- PURPOSE
4052 -- called when approver rejects or approves an enrollment request.
4053 --
4054 -- HISTORY
4055 -- 10/05/2002 pukken CREATION
4056 --------------------------------------------------------------------------
4057
4058 PROCEDURE update_enrl_req_status
4059 (
4060 p_api_version_number IN NUMBER
4061 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
4062 , p_commit IN VARCHAR2 := FND_API.G_FALSE
4063 , p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
4064 , enrl_request_id IN NUMBER
4065 , entity_code IN VARCHAR2
4066 , approvalStatus IN VARCHAR2
4067 , start_date IN DATE
4068 , end_date IN DATE
4069 , x_return_status OUT NOCOPY VARCHAR2
4070 , x_msg_count OUT NOCOPY NUMBER
4071 , x_msg_data OUT NOCOPY VARCHAR2
4072 ) IS
4073
4074 l_api_name CONSTANT VARCHAR2(30) := 'update_enrl_req_status';
4075 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
4076 l_api_version_number CONSTANT NUMBER := 1.0;
4077 l_checkrow_pending boolean:=false;
4078 l_approver_rec Pv_Ge_Temp_Approvers_PVT.approver_rec_type;
4079 pv_pg_memb_rec PV_Pg_Memberships_PVT.memb_rec_type;
4080 l_enrq_rec PV_Pg_Enrl_Requests_PVT.enrl_request_rec_type;
4081 l_membership_id number;
4082 l_approvalStatus varchar2(50);
4083 l_approverPersonId number;
4084 l_approverUserId number;
4085 l_message_code varchar2(30);
4086 l_contract_signed boolean:=false;
4087 l_checklistStatus boolean:=false;
4088 l_allow_appr_wo_contract varchar2(5);
4089 l_param_tbl_var PVX_UTILITY_PVT.log_params_tbl_type;
4090 l_param_tbl_default PVX_UTILITY_PVT.log_params_tbl_type;
4091 l_resource_name VARCHAR2(360);
4092 l_resource_id NUMBER;
4093 l_default_approver_id NUMBER;
4094 l_default_person_id NUMBER;
4095 l_partner_id NUMBER;
4096 l_default_appr VARCHAR2(60);
4097 x_role_name VARCHAR2(320);
4098 x_role_display_name VARCHAR2(360);
4099 l_display_name VARCHAR2(360);
4100
4101 CURSOR approver_dtl_cur (p_enrl_req_id number, p_person_id NUMBER,p_user_id NUMBER) IS
4102 SELECT object_version_number,entity_approver_id,approver_id,approver_type_code
4103 FROM pv_ge_temp_approvers
4104 WHERE appr_for_entity_id=p_enrl_req_id
4105 AND APPROVER_ID = DECODE( APPROVER_TYPE_CODE,'PERSON',p_person_id,'USER',p_user_id,null )
4106 AND ARC_APPR_FOR_ENTITY_CODE='ENRQ';
4107
4108 CURSOR enrq_dtl_cur (p_enrl_req_id number) IS
4109 SELECT object_version_number, partner_id
4110 FROM pv_pg_enrl_requests
4111 WHERE enrl_request_id=p_enrl_req_id;
4112
4113 CURSOR c_resource_per_cur(p_appr_id NUMBER) IS
4114 SELECT res.resource_id,res.resource_name
4115 FROM jtf_rs_resource_extns_vl res,fnd_user fnd
4116 WHERE fnd.employee_id = p_appr_id
4117 AND res.user_id=fnd.user_id;
4118
4119 CURSOR c_resource_usr_cur(p_appr_id NUMBER) IS
4120 SELECT res.resource_id,res.resource_name
4121 FROM jtf_rs_resource_extns_vl res,fnd_user fnd
4122 WHERE fnd.user_id = p_appr_id
4123 AND res.user_id=fnd.user_id;
4124
4125 CURSOR person_id_csr ( p_user_id NUMBER ) IS
4126 SELECT person_id ,full_name
4127 FROM per_all_people_f per
4128 , FND_USER usr
4129 WHERE user_id=p_user_id
4130 AND usr.person_party_id=per.party_id;
4131
4132 BEGIN
4133 -- call AME api to get the next approver
4134 SAVEPOINT update_enrl_req_status;
4135 -- Standard call to check for call compatibility.
4136 IF NOT FND_API.Compatible_API_Call
4137 ( l_api_version_number
4138 , p_api_version_number
4139 , l_api_name
4140 , G_PKG_NAME
4141 )
4142 THEN
4143 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4144 END IF;
4145 -- Initialize message list if p_init_msg_list is set to TRUE.
4146 IF FND_API.to_Boolean( p_init_msg_list ) THEN
4147 FND_MSG_PUB.initialize;
4148 END IF;
4149 PVX_UTILITY_PVT.debug_message('FND Global user id is'|| FND_GLOBAL.USER_ID );
4150 PVX_UTILITY_PVT.debug_message('FND Employee user id is'|| FND_GLOBAL.EMPLOYEE_ID );
4151 -- Debug Message
4152 IF (PV_DEBUG_HIGH_ON) THEN
4153 PVX_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
4154 PVX_UTILITY_PVT.debug_message('FND Global user id is'|| FND_GLOBAL.USER_ID );
4155 PVX_UTILITY_PVT.debug_message('FND Employee user id is'|| FND_GLOBAL.EMPLOYEE_ID );
4156 END IF;
4157 -- Initialize API return status to SUCCESS
4158 x_return_status := FND_API.G_RET_STS_SUCCESS;
4159 --RAISE FND_API.g_exc_error;
4160 g_approver_response:=approvalStatus;
4161 l_approverPersonId:= FND_GLOBAL.EMPLOYEE_ID;
4162 l_approverUserId:= FND_GLOBAL.USER_ID;
4163
4164 WF_DIRECTORY.getrolename
4165 ( p_orig_system => 'PER'
4166 , p_orig_system_id => l_approverPersonId
4167 , p_name => x_role_name
4168 , p_display_name => x_role_display_name
4169 );
4170
4171 OPEN enrq_dtl_cur (enrl_request_id);
4172 FETCH enrq_dtl_cur into l_enrq_rec.object_version_number,l_partner_id;
4173 CLOSE enrq_dtl_cur;
4174
4175 IF approvalStatus='APPROVED' THEN
4176 --check the profile value first whether the request can be approved without signing contract
4177 l_allow_appr_wo_contract := FND_PROFILE.VALUE('PV_ALLOW_APPROVAL_WITHOUT_CONTRACT');
4178 IF l_allow_appr_wo_contract <> 'Y' THEN
4179 l_contract_signed:=checkcontract_status(enrl_request_id);
4180 IF l_contract_signed =false THEN
4181 FND_MESSAGE.set_name('PV', 'PV_ENRQ_CONTRACT_NOT_SIGNED');
4182 FND_MSG_PUB.add;
4183 RAISE FND_API.G_EXC_ERROR;
4184 END IF;
4185 END IF;
4186
4187 --check whther approver has checked alll the checklist items
4188 --depending on the flag set at the program level,raise error if they are not checked.
4189 l_checklistStatus:=checklist_status(enrl_request_id);
4190 IF l_checklistStatus=false THEN
4191 FND_MESSAGE.set_name('PV', 'PV_CHECKLIST_INCOMPLETE');
4192 --FND_MESSAGE.set_token ('CHECKLISTLINK', l_checklistlink, FALSE);
4193 FND_MSG_PUB.add;
4194 RAISE FND_API.G_EXC_ERROR;
4195 END IF;
4196
4197 l_approvalStatus:=ame_util.approvedStatus;
4198 l_message_code :='PV_ENR_REQ_APPROVED_BY_APPR';
4199
4200 --update enrollments table with the start and end date
4201 l_enrq_rec.enrl_request_id:=enrl_request_id;
4202 l_enrq_rec.tentative_start_date:=start_date;
4203 l_enrq_rec.tentative_end_date:=end_date;
4204
4205 PV_Pg_Enrl_Requests_PVT.Update_Pg_Enrl_Requests
4206 ( p_api_version_number => 1.0
4207 , p_init_msg_list => FND_API.g_false
4208 , p_commit => FND_API.G_FALSE
4209 , p_validation_level => FND_API.g_valid_level_full
4210 , x_return_status => x_return_status
4211 , x_msg_count => x_msg_count
4212 , x_msg_data => x_msg_data
4213 , p_enrl_request_rec => l_enrq_rec
4214 );
4215 IF x_return_status = FND_API.g_ret_sts_error THEN
4216 RAISE FND_API.g_exc_error;
4217 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
4218 RAISE FND_API.g_exc_unexpected_error;
4219 END IF;
4220 ELSE
4221 l_approvalStatus:=ame_util.rejectStatus;
4222 l_message_code :='PV_ENR_REQ_REJECTED';
4223 END IF;
4224
4225
4226
4227 OPEN approver_dtl_cur(enrl_request_id,l_approverPersonId, l_approverUserId);
4228 FETCH approver_dtl_cur into l_approver_rec.object_version_number,
4229 l_approver_rec.entity_approver_id,
4230 l_approver_rec.approver_id,
4231 l_approver_rec.approver_type_code;
4232 CLOSE approver_dtl_cur;
4233
4234 l_approver_rec.approval_status_code:= approvalStatus;
4235
4236 -- PLEASE DO NOT ADD CODE HERE TO UPDATE TEMP APPROVERS TABLE WITH THE APPROVERS RESPONSE. IF YOU
4237 -- ADD ,IT WILL SCREW UP THE ENTIRE LOGIC. I HAVE ADDED CODED TO UPDATE TEMP APPROVERS TABLE WITH APPROVERS
4238 -- RESPONSE IN APPROPRIATE PLACES.Just before you call process_response you can update temp approvers table
4239
4240
4241 -- also log into enrollments log
4242 l_param_tbl_var(1).param_name := 'APPROVER_NAME';
4243 l_param_tbl_var(1).param_value := x_role_display_name;
4244
4245 --write to enrollments log that xyz approver approved it or rejected it
4246 PVX_UTILITY_PVT.create_history_log
4247 ( p_arc_history_for_entity_code => 'ENRQ'
4248 , p_history_for_entity_id => enrl_request_id
4249 , p_history_category_code => 'APPROVAL'
4250 , p_message_code => l_message_code
4251 , p_comments => null
4252 , p_partner_id => l_partner_id
4253 , p_access_level_flag => 'P'
4254 , p_interaction_level => PVX_Utility_PVT.G_INTERACTION_LEVEL_50
4255 , p_log_params_tbl => l_param_tbl_var
4256 , p_init_msg_list => FND_API.g_false
4257 , p_commit => FND_API.G_FALSE
4258 , x_return_status => x_return_status
4259 , x_msg_count => x_msg_count
4260 , x_msg_data => x_msg_data
4261 );
4262 IF x_return_status = FND_API.g_ret_sts_error THEN
4263 RAISE FND_API.g_exc_error;
4264 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
4265 RAISE FND_API.g_exc_unexpected_error;
4266 END IF;
4267
4268 l_checkrow_pending:=check_pending_default(enrl_request_id);
4269 -- the if block below updates OAM with the approval status . if the request
4270 -- was sent to default approver, OAM does not record that and we cannot update OAM in that case.
4271
4272 IF l_checkrow_pending=false THEN --code if approver was returned by OAM
4273 /** check in oam whether the approver approving the request is in the list of approvers
4274 this could happen , if rules defined in OAM changes and thereby the list of approvers could change.
4275 */
4276
4277 g_isApproverInList:=isApproverInList(enrl_request_id,l_approverPersonId);
4278
4279 IF g_isApproverInList=true THEN
4280
4281 /** Following is required as we expect AME to return their new statuses. Bug # 4879218 **/
4282 ame_util2.detailedApprovalStatusFlagYN := ame_util.booleanTrue;
4283
4284 ame_api2.updateApprovalStatus2
4285 ( applicationIdIn => 691
4286 , transactionTypeIn => 'ENRQ'
4287 , transactionIdIn => enrl_request_id
4288 , approvalStatusIn => l_approvalStatus
4289 , approverNameIn => x_role_name
4290 , itemClassIn => null
4291 , itemIdIn => null
4292 , actionTypeIdIn => null
4293 , groupOrChainIdIn => null
4294 , occurrenceIn => null
4295 , forwardeeIn => null
4296 , updateItemIn => null
4297 );
4298 IF approvalStatus='APPROVED' THEN
4299
4300 submit_enrl_req_for_approval
4301 ( p_api_version_number
4302 , p_init_msg_list
4303 , FND_API.G_FALSE
4304 , p_validation_level
4305 , enrl_request_id
4306 , 'ENRQ'
4307 , x_return_status
4308 , x_msg_count
4309 , x_msg_data
4310 );
4311 IF x_return_status = FND_API.g_ret_sts_error THEN
4312 RAISE FND_API.g_exc_error;
4313 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
4314 RAISE FND_API.g_exc_unexpected_error;
4315 END IF;
4316 ELSE
4317 -- means enrollment has been rejected
4318 Pv_Ge_Temp_Approvers_PVT.Update_Ptr_Enr_Temp_Appr
4319 ( p_api_version_number => 1.0
4320 , p_init_msg_list => FND_API.g_false
4321 , p_commit => FND_API.g_false
4322 , p_validation_level => FND_API.g_valid_level_full
4323 , x_return_status => x_return_status
4324 , x_msg_count => x_msg_count
4325 , x_msg_data => x_msg_data
4326 , p_approver_rec =>l_approver_rec
4327
4328 );
4329 IF x_return_status = FND_API.g_ret_sts_error THEN
4330 RAISE FND_API.g_exc_error;
4331 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
4332 RAISE FND_API.g_exc_unexpected_error;
4333 END IF;
4334
4335 process_response
4336 ( enrl_request_id => enrl_request_id
4337 , approvalStatus => approvalStatus
4338 , x_return_status => x_return_status
4339 , x_msg_count => x_msg_count
4340 , x_msg_data => x_msg_data
4341 );
4342 IF x_return_status = FND_API.g_ret_sts_error THEN
4343 RAISE FND_API.g_exc_error;
4344 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
4345 RAISE FND_API.g_exc_unexpected_error;
4346 END IF;
4347 END IF;
4348 ELSE
4349 -- means current approver is not in the list of approvers anymore
4350 PVX_UTILITY_PVT.create_history_log
4351 ( p_arc_history_for_entity_code => 'ENRQ'
4352 , p_history_for_entity_id => enrl_request_id
4353 , p_history_category_code => 'APPROVAL'
4354 , p_message_code => 'PV_APPROVER_CHANGED'
4355 , p_comments => null
4356 , p_partner_id => l_partner_id
4357 , p_access_level_flag => 'P'
4358 , p_interaction_level => PVX_Utility_PVT.G_INTERACTION_LEVEL_10
4359 , p_log_params_tbl => l_param_tbl_var
4360 , p_init_msg_list => FND_API.g_false
4361 , p_commit => FND_API.G_FALSE
4362 , x_return_status => x_return_status
4363 , x_msg_count => x_msg_count
4364 , x_msg_data => x_msg_data
4365 );
4366 IF x_return_status = FND_API.g_ret_sts_error THEN
4367 RAISE FND_API.g_exc_error;
4368 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
4369 RAISE FND_API.g_exc_unexpected_error;
4370 END IF;
4371 submit_enrl_req_for_approval
4372 ( p_api_version_number
4373 , p_init_msg_list
4374 , FND_API.G_FALSE
4375 , p_validation_level
4376 , enrl_request_id
4377 , 'ENRQ'
4378 , x_return_status
4379 , x_msg_count
4380 , x_msg_data
4381 );
4382 IF x_return_status = FND_API.g_ret_sts_error THEN
4383 RAISE FND_API.g_exc_error;
4384 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
4385 RAISE FND_API.g_exc_unexpected_error;
4386 END IF;
4387 END IF;
4388 --g_isApproverInList:=true;
4389 ELSE --code if approver is deafault approver
4390 l_default_approver_id:= isnumber(FND_PROFILE.VALUE('PV_ENRQ_DEFAULT_APPR'));
4391 IF ( l_default_approver_id is NULL ) THEN
4392
4393 FND_MESSAGE.set_name('PV', 'PV_ENRQ_APPR_NOT_SET');
4394 FND_MSG_PUB.add;
4395 RAISE FND_API.G_EXC_ERROR;
4396 END IF;
4397 OPEN person_id_csr (l_default_approver_id);
4398 FETCH person_id_csr INTO l_default_person_id,l_display_name;
4399 CLOSE person_id_csr;
4400 PVX_UTILITY_PVT.debug_message('FND l_default_person_id is'|| l_default_person_id );
4401 IF l_default_person_id=l_approverPersonId THEN
4402 --there is no need to call OAM if the request has gone to default approver
4403 /*PVX_UTILITY_PVT.create_history_log
4404 ( p_arc_history_for_entity_code => 'ENRQ'
4405 , p_history_for_entity_id => enrl_request_id
4406 , p_history_category_code => 'APPROVAL'
4407 , p_message_code => l_message_code
4408 , p_comments => null
4409 , p_partner_id => l_partner_id
4410 , p_access_level_flag => 'P'
4411 , p_interaction_level => PVX_Utility_PVT.G_INTERACTION_LEVEL_10
4412 , p_log_params_tbl => l_param_tbl_var
4413 , p_init_msg_list => FND_API.g_false
4414 , p_commit => FND_API.G_FALSE
4415 , x_return_status => x_return_status
4416 , x_msg_count => x_msg_count
4417 , x_msg_data => x_msg_data
4418 );
4419 IF x_return_status = FND_API.g_ret_sts_error THEN
4420 RAISE FND_API.g_exc_error;
4421 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
4422 RAISE FND_API.g_exc_unexpected_error;
4423 END IF;
4424 */
4425 Pv_Ge_Temp_Approvers_PVT.Update_Ptr_Enr_Temp_Appr
4426 ( p_api_version_number => 1.0
4427 , p_init_msg_list => FND_API.g_false
4428 , p_commit => FND_API.g_false
4429 , p_validation_level => FND_API.g_valid_level_full
4430 , x_return_status => x_return_status
4431 , x_msg_count => x_msg_count
4432 , x_msg_data => x_msg_data
4433 , p_approver_rec =>l_approver_rec
4434 );
4435 IF x_return_status = FND_API.g_ret_sts_error THEN
4436 RAISE FND_API.g_exc_error;
4437 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
4438 RAISE FND_API.g_exc_unexpected_error;
4439 END IF;
4440
4441 process_response
4442 ( enrl_request_id => enrl_request_id
4443 , approvalStatus => approvalStatus
4444 , x_return_status => x_return_status
4445 , x_msg_count => x_msg_count
4446 , x_msg_data => x_msg_data
4447 );
4448
4449 IF x_return_status = FND_API.g_ret_sts_error THEN
4450 RAISE FND_API.g_exc_error;
4451 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
4452 RAISE FND_API.g_exc_unexpected_error;
4453 END IF;
4454 ELSE
4455 --write to the logs that the current approver for this enrollment request has changed
4456 PVX_UTILITY_PVT.create_history_log
4457 ( p_arc_history_for_entity_code => 'ENRQ'
4458 , p_history_for_entity_id => enrl_request_id
4459 , p_history_category_code => 'APPROVAL'
4460 , p_message_code => 'PV_APPROVER_CHANGED'
4461 , p_comments => null
4462 , p_partner_id => l_partner_id
4463 , p_access_level_flag => 'P'
4464 , p_interaction_level => PVX_Utility_PVT.G_INTERACTION_LEVEL_10
4465 , p_log_params_tbl => l_param_tbl_var
4466 , p_init_msg_list => FND_API.g_false
4467 , p_commit => FND_API.G_FALSE
4468 , x_return_status => x_return_status
4469 , x_msg_count => x_msg_count
4470 , x_msg_data => x_msg_data
4471 );
4472 IF x_return_status = FND_API.g_ret_sts_error THEN
4473 RAISE FND_API.g_exc_error;
4474 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
4475 RAISE FND_API.g_exc_unexpected_error;
4476 END IF;
4477 submit_enrl_req_for_approval
4478 ( p_api_version_number
4479 , p_init_msg_list
4480 , FND_API.G_FALSE
4481 , p_validation_level
4482 , enrl_request_id
4483 , 'ENRQ'
4484 , x_return_status
4485 , x_msg_count
4486 , x_msg_data
4487 );
4488 IF x_return_status = FND_API.g_ret_sts_error THEN
4489 RAISE FND_API.g_exc_error;
4490 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
4491 RAISE FND_API.g_exc_unexpected_error;
4492 END IF;
4493 END IF;
4494 END IF; -- end of if else for approver is deafault approver
4495 IF FND_API.to_Boolean( p_commit ) THEN
4496 COMMIT WORK;
4497 END IF;
4498 EXCEPTION
4499 WHEN FND_API.G_EXC_ERROR THEN
4500 ROLLBACK TO update_enrl_req_status;
4501 x_return_status := FND_API.G_RET_STS_ERROR;
4502 -- Standard call to get message count and if count=1, get the message
4503 FND_MSG_PUB.Count_And_Get
4504 ( p_encoded => FND_API.G_FALSE
4505 , p_count => x_msg_count
4506 , p_data => x_msg_data
4507 );
4508 --write to enrollment log that an error has occured?. how do we handle the situation if an error occured in process
4509 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4510 ROLLBACK TO update_enrl_req_status;
4511 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4512 -- Standard call to get message count and if count=1, get the message
4513 FND_MSG_PUB.Count_And_Get
4514 ( p_encoded => FND_API.G_FALSE
4515 , p_count => x_msg_count
4516 , p_data => x_msg_data
4517 );
4518 WHEN OTHERS THEN
4519 ROLLBACK TO update_enrl_req_status;
4520 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4521 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
4522 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
4523 END IF;
4524 -- Standard call to get message count and if count=1, get the message
4525 FND_MSG_PUB.Count_And_Get
4526 ( p_encoded => FND_API.G_FALSE
4527 , p_count => x_msg_count
4528 , p_data => x_msg_data
4529 );
4530 END update_enrl_req_status;
4531
4532
4533 --------------------------------------------------------------------------
4534 -- PROCEDURE
4535 -- Process_errored_requests
4536 --
4537 -- PURPOSE
4538 -- Process the enrollment requests which are errored while finding next
4539 -- approver in OAM. This will be called by concurrent program.
4540 -- IN
4541 -- std. conc. request parameters.
4542 -- ERRBUF
4543 -- RETCODE
4544 -- OUT
4545 -- USED BY
4546 -- Concurrent program
4547 -- HISTORY
4548 -- 12/04/2002 sveerave CREATION
4549 --------------------------------------------------------------------------
4550
4551
4552 PROCEDURE Process_errored_requests(
4553 ERRBUF OUT NOCOPY VARCHAR2,
4554 RETCODE OUT NOCOPY VARCHAR2 )
4555 IS
4556 /* Get all the errored enrollment requests. */
4557 CURSOR c_get_errored_requests IS
4558 SELECT appr.appr_for_entity_id, appr.arc_appr_for_entity_code
4559 FROM pv_ge_temp_approvers appr
4560 WHERE appr.approval_status_code = 'ERROR'
4561 AND appr.arc_appr_for_entity_code = 'ENRQ';
4562 -- local variables
4563 l_enrl_request_id NUMBER;
4564 l_return_status VARCHAR2(1);
4565 l_msg_count NUMBER;
4566 l_msg_data VARCHAR2(240);
4567
4568 BEGIN
4569 /* Standard Start of API savepoint */
4570 SAVEPOINT Process_errored_requests;
4571 /* Logic to update the membership status to EXPIRE for all the EXPIRED members */
4572 FOR l_get_errored_requests_rec IN c_get_errored_requests LOOP
4573 l_enrl_request_id := l_get_errored_requests_rec.appr_for_entity_id;
4574 Write_log (1, 'Processing enrollment request id: '|| l_enrl_request_id);
4575 submit_enrl_req_for_approval(
4576 p_api_version_number => 1.0
4577 ,p_init_msg_list => FND_API.G_FALSE
4578 ,p_commit => FND_API.G_FALSE
4579 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
4580 ,enrl_request_id => l_get_errored_requests_rec.appr_for_entity_id
4581 ,entity_code => l_get_errored_requests_rec.arc_appr_for_entity_code
4582 ,x_return_status => l_return_status
4583 ,x_msg_count => l_msg_count
4584 ,x_msg_data => l_msg_data );
4585 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
4586 RAISE FND_API.G_EXC_ERROR;
4587 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
4588 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4589 END IF;
4590 END LOOP;
4591
4592 -- return the success code.
4593 retcode := '0';
4594
4595 EXCEPTION
4596 WHEN FND_API.G_EXC_ERROR THEN
4597 ERRBUF := ERRBUF || sqlerrm;
4598 RETCODE := '1';
4599 ROLLBACK TO Process_errored_requests;
4600 --l_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR', SQLERRM);
4601 Write_log (1, 'Error in submitting the enrollment request id of '|| l_enrl_request_id ||' for approval');
4602 Write_log (1, 'SQLCODE ' || to_char(SQLCODE) || ' SQLERRM ' || substr(SQLERRM, 1, 100));
4603 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4604 ERRBUF := ERRBUF||sqlerrm;
4605 RETCODE := '1';
4606 ROLLBACK TO Process_errored_requests;
4607 --l_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR', SQLERRM);
4608 Write_log (1, 'Unexpected Error in submitting the enrollment request id of '|| l_enrl_request_id ||' for approval');
4609 Write_log (1, 'SQLCODE ' || to_char(SQLCODE) || ' SQLERRM ' || substr(SQLERRM, 1, 100));
4610 WHEN OTHERS THEN
4611 ERRBUF := ERRBUF||sqlerrm;
4612 RETCODE := '2';
4613 ROLLBACK TO Process_errored_requests;
4614 --l_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR', SQLERRM);
4615 Write_log (1, 'Other Exception in running the conc. program for processing the errored enrollment requests');
4616 Write_log (1, 'SQLCODE ' || to_char(SQLCODE) || ' SQLERRM ' || substr(SQLERRM, 1, 100));
4617
4618 END Process_errored_requests;
4619
4620 --------------------------------------------------------------------------
4621 -- PROCEDURE
4622 -- terminate_downgrade_memb
4623 --
4624 -- PURPOSE
4625 -- called when user clicks upgrade or terminate
4626 --
4627 -- HISTORY
4628 -- 10/04/2002 pukken CREATION
4629 --------------------------------------------------------------------------
4630
4631
4632 PROCEDURE terminate_downgrade_memb(
4633 p_api_version_number IN NUMBER
4634 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
4635 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
4636 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
4637 ,p_membership_id IN NUMBER
4638 ,p_event_code IN VARCHAR2 -- pass 'TERMINATED' or 'DOWNGRADED' depending on the event
4639 ,p_status_reason_code IN VARCHAR2 -- reason for termoination or downgrade
4640 ,p_comments IN VARCHAR2 DEFAULT NULL
4641 ,p_program_id_downgraded_to IN NUMBER --programid into which the partner is downgraded to.
4642 ,p_requestor_resource_id IN NUMBER --resource_id of the user who's performing the action
4643 ,p_new_memb_id OUT NOCOPY NUMBER
4644 ,x_return_status OUT NOCOPY VARCHAR2
4645 ,x_msg_count OUT NOCOPY NUMBER
4646 ,x_msg_data OUT NOCOPY VARCHAR2) IS
4647
4648 l_pv_pg_memb_rec PV_Pg_Memberships_PVT.memb_rec_type;
4649 l_pv_pg_new_memb_rec PV_Pg_Memberships_PVT.memb_rec_type;
4650 l_pv_pg_enrq_rec PV_Pg_Enrl_Requests_PVT.enrl_request_rec_type;
4651 l_mmbr_tran_rec pv_pg_mmbr_transitions_PVT.mmbr_tran_rec_type;
4652 l_partner_id NUMBER;
4653 l_enrl_request_id NUMBER;
4654 l_membership_id NUMBER;
4655 l_original_end_date DATE;
4656 l_mmbr_transition_id NUMBER;
4657 l_message_code VARCHAR2(30);
4658 l_param_tbl_var PVX_UTILITY_PVT.log_params_tbl_type;
4659 l_api_name CONSTANT VARCHAR2(30) := 'terminate_downgrade_memb';
4660 l_api_version_number CONSTANT NUMBER := 1.0;
4661 l_memb_active BOOLEAN:=false;
4662 l_program_name VARCHAR2(60);
4663 l_custom_setup_id NUMBER;
4664
4665 CURSOR membership_csr(p_memb_id NUMBER) IS
4666 SELECT memb.object_version_number,memb.partner_id,memb.original_end_date,enrq.custom_setup_id
4667 FROM pv_pg_memberships memb,pv_pg_enrl_requests enrq
4668 WHERE memb.membership_id=p_memb_id
4669 AND memb.enrl_request_id=enrq.enrl_request_id;
4670
4671 CURSOR program_name_csr(p_prgm_id NUMBER) IS
4672 SELECT program_name
4673 FROM pv_partner_program_tl
4674 WHERE program_id=p_prgm_id;
4675
4676
4677 BEGIN
4678 SAVEPOINT terminate_downgrade_memb;
4679 -- Standard call to check for call compatibility.
4680 IF NOT FND_API.Compatible_API_Call ( l_api_version_number
4681 ,p_api_version_number
4682 ,l_api_name
4683 ,G_PKG_NAME
4684 )
4685
4686 THEN
4687 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4688 END IF;
4689
4690 -- Initialize message list if p_init_msg_list is set to TRUE.
4691 IF FND_API.to_Boolean( p_init_msg_list )
4692 THEN
4693 FND_MSG_PUB.initialize;
4694 END IF;
4695
4696 -- Debug Message
4697 IF (PV_DEBUG_HIGH_ON) THEN
4698
4699 PVX_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
4700 END IF;
4701
4702 -- Initialize API return status to SUCCESS
4703 x_return_status := FND_API.G_RET_STS_SUCCESS;
4704
4705 -- =========================================================================
4706 -- Validate Environment
4707 -- =========================================================================
4708
4709 IF FND_GLOBAL.USER_ID IS NULL THEN
4710 PVX_UTILITY_PVT.Error_Message(p_message_name => 'USER_PROFILE_MISSING');
4711 RAISE FND_API.G_EXC_ERROR;
4712 END IF;
4713
4714
4715
4716
4717 -- update membership record and call responsiblity management
4718 -- set the membership record to be updated
4719 l_pv_pg_memb_rec.membership_id:=p_membership_id;
4720 l_pv_pg_memb_rec.actual_end_date:=sysdate;
4721 l_pv_pg_memb_rec.membership_status_code:=p_event_code;
4722 l_pv_pg_memb_rec.status_reason_code:=p_status_reason_code;
4723
4724 OPEN membership_csr(p_membership_id);
4725 FETCH membership_csr INTO l_pv_pg_memb_rec.object_version_number,l_partner_id,l_original_end_date,l_custom_setup_id;
4726 CLOSE membership_csr;
4727
4728
4729 PV_Pg_Memberships_PVT.Update_Pg_Memberships
4730 ( p_api_version_number => 1.0
4731 ,p_init_msg_list => Fnd_Api.g_false
4732 ,p_commit => Fnd_Api.g_false
4733 ,x_return_status => x_return_status
4734 ,x_msg_count => x_msg_count
4735 ,x_msg_data => x_msg_data
4736 ,p_memb_rec => l_pv_pg_memb_rec
4737 );
4738
4739 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
4740 RAISE FND_API.G_EXC_ERROR;
4741 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
4742 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4743 END IF;
4744
4745
4746 Pv_User_Resp_Pvt.manage_memb_resp
4747 ( p_api_version_number => 1.0
4748 ,p_init_msg_list => Fnd_Api.g_false
4749 ,p_commit => Fnd_Api.g_false
4750 ,p_membership_id => p_membership_id
4751 ,x_return_status => x_return_status
4752 ,x_msg_count => x_msg_count
4753 ,x_msg_data => x_msg_data
4754 );
4755
4756 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
4757 RAISE FND_API.G_EXC_ERROR;
4758 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
4759 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4760 END IF;
4761
4762
4763 p_new_memb_id:=null;
4764
4765 IF p_event_code='DOWNGRADED' THEN
4766
4767 --set message code for history logs
4768 l_message_code:='PV_MEMBERSHIP_DOWNGRADED';
4769
4770 --create an enrollment request with approved status
4771 l_pv_pg_enrq_rec.partner_id:=l_partner_id;
4772 l_pv_pg_enrq_rec.program_id:=p_program_id_downgraded_to;
4773 l_pv_pg_enrq_rec.requestor_resource_id:= p_requestor_resource_id;
4774 l_pv_pg_enrq_rec.request_status_code:='APPROVED';
4775 l_pv_pg_enrq_rec.enrollment_type_code:='DOWNGRADE';
4776 l_pv_pg_enrq_rec.payment_status_code:='NOT_SUBMITTED';
4777
4778 l_pv_pg_enrq_rec.request_submission_date:=sysdate;
4779 l_pv_pg_enrq_rec.request_initiated_by_code:='VENDOR';
4780 l_pv_pg_enrq_rec.contract_status_code:='NOT_SIGNED';
4781 l_pv_pg_enrq_rec.custom_setup_id:=getCustomSetupID(p_program_id_downgraded_to);
4782
4783 PV_Pg_Enrl_Requests_PVT.Create_Pg_Enrl_Requests
4784 ( p_api_version_number =>1.0
4785 ,p_init_msg_list => FND_API.g_false
4786 ,p_commit => FND_API.G_FALSE
4787 ,p_validation_level => FND_API.g_valid_level_full
4788 ,x_return_status => x_return_status
4789 ,x_msg_count => x_msg_count
4790 ,x_msg_data => x_msg_data
4791 ,p_enrl_request_rec => l_pv_pg_enrq_rec
4792 ,x_enrl_request_id => l_enrl_request_id
4793 );
4794
4795 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
4796 RAISE FND_API.G_EXC_ERROR;
4797 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
4798 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4799 END IF;
4800
4801
4802 --create a membership record with the downgraded program id and end date of the program from which it was
4803 --downgraded
4804 l_pv_pg_new_memb_rec.enrl_request_id:=l_enrl_request_id;
4805 l_pv_pg_new_memb_rec.start_date:=sysdate;
4806 l_pv_pg_new_memb_rec.original_end_date:=l_original_end_date;
4807 l_pv_pg_new_memb_rec.membership_status_code:='ACTIVE';
4808 l_pv_pg_new_memb_rec.partner_id:=l_partner_id;
4809 l_pv_pg_new_memb_rec.program_id:=p_program_id_downgraded_to;
4810
4811 PV_Pg_Memberships_PVT.Create_Pg_memberships
4812 ( p_api_version_number=>1.0
4813 ,p_init_msg_list => FND_API.g_false
4814 ,p_commit => FND_API.G_FALSE
4815 ,p_validation_level => FND_API.g_valid_level_full
4816 ,x_return_status => x_return_status
4817 ,x_msg_count => x_msg_count
4818 ,x_msg_data => x_msg_data
4819 ,p_memb_rec => l_pv_pg_new_memb_rec
4820 ,x_membership_id => l_membership_id
4821 );
4822
4823 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
4824 RAISE FND_API.G_EXC_ERROR;
4825 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
4826 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4827 END IF;
4828
4829 p_new_memb_id :=l_membership_id;
4830 --insert into member transitions table
4831 l_mmbr_tran_rec.from_membership_id:=p_membership_id;
4832 l_mmbr_tran_rec.to_membership_id:=l_membership_id;
4833 pv_pg_mmbr_transitions_PVT.Create_Mmbr_Trans
4834 ( p_api_version_number =>1.0
4835 ,p_init_msg_list => FND_API.g_false
4836 ,p_commit => FND_API.G_FALSE
4837 ,p_validation_level => FND_API.g_valid_level_full
4838 ,x_return_status => x_return_status
4839 ,x_msg_count => x_msg_count
4840 ,x_msg_data => x_msg_data
4841 ,p_mmbr_tran_rec => l_mmbr_tran_rec
4842 ,x_mmbr_transition_id => l_mmbr_transition_id
4843 );
4844
4845 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
4846 RAISE FND_API.G_EXC_ERROR;
4847 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
4848 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4849 END IF;
4850
4851 --call responsiblity management api for the new membership
4852 Pv_User_Resp_Pvt.manage_memb_resp
4853 ( p_api_version_number => 1.0
4854 ,p_init_msg_list => Fnd_Api.g_false
4855 ,p_commit => Fnd_Api.g_false
4856 ,p_membership_id => l_membership_id
4857 ,x_return_status => x_return_status
4858 ,x_msg_count => x_msg_count
4859 ,x_msg_data => x_msg_data
4860 );
4861
4862 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
4863 RAISE FND_API.G_EXC_ERROR;
4864 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
4865 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4866 END IF;
4867
4868 ELSIF p_event_code='TERMINATED' THEN
4869 --set message code for history logs
4870 l_message_code:='PV_MEMBERSHIP_TERMINATED';
4871 END IF;
4872
4873 --call the history log api to capture any comments from the user
4874 PVX_UTILITY_PVT.create_history_log
4875 ( p_arc_history_for_entity_code => 'ENRQ'
4876 ,p_history_for_entity_id => l_enrl_request_id
4877 ,p_history_category_code => 'APPROVAL'
4878 ,p_message_code => l_message_code
4879 ,p_comments => null
4880 ,p_partner_id => l_partner_id
4881 ,p_access_level_flag => 'P'
4882 ,p_interaction_level => PVX_Utility_PVT.G_INTERACTION_LEVEL_10
4883 ,p_log_params_tbl => l_param_tbl_var
4884 ,p_init_msg_list => FND_API.g_false
4885 ,p_commit => FND_API.G_FALSE
4886 ,x_return_status => x_return_status
4887 ,x_msg_count => x_msg_count
4888 ,x_msg_data => x_msg_data
4889 );
4890
4891 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
4892 RAISE FND_API.G_EXC_ERROR;
4893 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
4894 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4895 END IF;
4896 -- Standard check for p_commit
4897 IF FND_API.to_Boolean( p_commit ) THEN
4898 COMMIT WORK;
4899 END IF;
4900
4901
4902 -- Debug Message
4903 IF (PV_DEBUG_HIGH_ON) THEN
4904
4905 PVX_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
4906 END IF;
4907
4908
4909 -- Standard call to get message count and if count is 1, get message info.
4910 FND_MSG_PUB.Count_And_Get
4911 ( p_count => x_msg_count
4912 ,p_data => x_msg_data
4913 );
4914
4915 EXCEPTION
4916
4917 WHEN PVX_UTILITY_PVT.resource_locked THEN
4918 x_return_status := FND_API.g_ret_sts_error;
4919 PVX_UTILITY_PVT.Error_Message(p_message_name => 'PV_API_RESOURCE_LOCKED');
4920
4921 WHEN FND_API.G_EXC_ERROR THEN
4922 ROLLBACK TO terminate_downgrade_memb;
4923 x_return_status := FND_API.G_RET_STS_ERROR;
4924 -- Standard call to get message count and if count=1, get the message
4925 FND_MSG_PUB.Count_And_Get (
4926 p_encoded => FND_API.G_FALSE,
4927 p_count => x_msg_count,
4928 p_data => x_msg_data
4929 );
4930
4931 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4932 ROLLBACK TO terminate_downgrade_memb;
4933 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4934 -- Standard call to get message count and if count=1, get the message
4935 FND_MSG_PUB.Count_And_Get (
4936 p_encoded => FND_API.G_FALSE,
4937 p_count => x_msg_count,
4938 p_data => x_msg_data
4939 );
4940
4941 WHEN OTHERS THEN
4942 ROLLBACK TO terminate_downgrade_memb;
4943 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4944 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4945 THEN
4946 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
4947 END IF;
4948 -- Standard call to get message count and if count=1, get the message
4949 FND_MSG_PUB.Count_And_Get (
4950 p_encoded => FND_API.G_FALSE,
4951 p_count => x_msg_count,
4952 p_data => x_msg_data
4953 );
4954 End terminate_downgrade_memb;
4955
4956 --------------------------------------------------------------------------
4957 -- PROCEDURE
4958 -- Create_Default_Membership
4959 --
4960 -- PURPOSE
4961 -- Create membership into a default program . This is called when new partner is created
4962 -- IN
4963 -- p_partner_id - partner_id of the partner
4964 -- p_requestor_resource_id- resource_id of the user who's performing the action
4965 -- USED BY
4966 -- User Management while creating new partner
4967 -- HISTORY
4968 -- 05-June-2003 pukken CREATION
4969 --------------------------------------------------------------------------
4970
4971 PROCEDURE Create_Default_Membership (
4972 p_api_version_number IN NUMBER
4973 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
4974 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
4975 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
4976 ,p_partner_id IN NUMBER
4977 ,p_requestor_resource_id IN NUMBER --resource_id of the user who's performing the action
4978 ,x_return_status OUT NOCOPY VARCHAR2
4979 ,x_msg_count OUT NOCOPY NUMBER
4980 ,x_msg_data OUT NOCOPY VARCHAR2
4981 ) IS
4982
4983 pv_pg_memb_rec PV_Pg_Memberships_PVT.memb_rec_type;
4984 l_pv_pg_enrq_rec PV_Pg_Enrl_Requests_PVT.enrl_request_rec_type;
4985 l_api_name CONSTANT VARCHAR2(30) := 'Create_Default_Membership';
4986 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
4987 l_api_version_number CONSTANT NUMBER := 1.0;
4988 l_membership_id NUMBER;
4989 l_entity_approver_id NUMBER;
4990 l_enrl_request_id NUMBER;
4991 l_default_program_id NUMBER:=null;
4992 l_param_tbl_var PVX_UTILITY_PVT.log_params_tbl_type;
4993 --This value for l_default_approver_id needs to be retrieved from profile
4994 l_isPrimaryFlag VARCHAR2(1);
4995 l_program_name VARCHAR2(60);
4996
4997 CURSOR isPrimaryExist ( ptr_id IN NUMBER ) IS
4998 SELECT 'Y'
4999 FROM dual
5000 WHERE EXISTS
5001 (
5002 SELECT user_id
5003 FROM pv_partner_primary_users_v
5004 WHERE partner_id = ptr_id
5005 );
5006
5007 CURSOR c_program_csr( prgm_id IN NUMBER ) IS
5008 SELECT program_name
5009 FROM pv_partner_program_vl
5010 WHERE program_id=prgm_id;
5011
5012 BEGIN
5013 SAVEPOINT Create_Default_Membership;
5014 -- Standard call to check for call compatibility.
5015 IF NOT FND_API.Compatible_API_Call ( l_api_version_number
5016 ,p_api_version_number
5017 ,l_api_name
5018 ,G_PKG_NAME
5019 )
5020
5021 THEN
5022 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5023 END IF;
5024
5025 -- Initialize message list if p_init_msg_list is set to TRUE.
5026 IF FND_API.to_Boolean( p_init_msg_list )
5027 THEN
5028 FND_MSG_PUB.initialize;
5029 END IF;
5030
5031 -- Debug Message
5032 IF (PV_DEBUG_HIGH_ON) THEN
5033
5034 PVX_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
5035 END IF;
5036
5037 -- Initialize API return status to SUCCESS
5038 x_return_status := FND_API.G_RET_STS_SUCCESS;
5039
5040 -- =========================================================================
5041 -- Validate Environment
5042 -- =========================================================================
5043
5044 IF FND_GLOBAL.USER_ID IS NULL THEN
5045 PVX_UTILITY_PVT.Error_Message(p_message_name => 'USER_PROFILE_MISSING');
5046 RAISE FND_API.G_EXC_ERROR;
5047 END IF;
5048 --create an enrollment request with approved status
5049
5050 l_default_program_id:= isnumber(FND_PROFILE.VALUE('PV_PARTNER_DEFAULT_PROGRAM'));
5051 IF l_default_program_id is NOT NULL THEN
5052 l_pv_pg_enrq_rec.partner_id:=p_partner_id;
5053 l_pv_pg_enrq_rec.program_id:= l_default_program_id;
5054 l_pv_pg_enrq_rec.requestor_resource_id:=p_requestor_resource_id;
5055 l_pv_pg_enrq_rec.request_status_code:='APPROVED';
5056 l_pv_pg_enrq_rec.enrollment_type_code:='NEW';
5057 l_pv_pg_enrq_rec.request_submission_date:=sysdate;
5058 l_pv_pg_enrq_rec.request_initiated_by_code:='DEFAULT_ENROLLMENT';
5059 l_pv_pg_enrq_rec.contract_status_code:='NOT_SIGNED';
5060 l_pv_pg_enrq_rec.payment_status_code:='NOT_SUBMITTED';
5061 l_pv_pg_enrq_rec.custom_setup_id:= getCustomSetupID(l_default_program_id);
5062
5063 PV_Pg_Enrl_Requests_PVT.Create_Pg_Enrl_Requests
5064 ( p_api_version_number =>1.0
5065 ,p_init_msg_list => FND_API.g_false
5066 ,p_commit => FND_API.G_FALSE
5067 ,p_validation_level => FND_API.g_valid_level_full
5068 ,x_return_status => x_return_status
5069 ,x_msg_count => x_msg_count
5070 ,x_msg_data => x_msg_data
5071 ,p_enrl_request_rec => l_pv_pg_enrq_rec
5072 ,x_enrl_request_id => l_enrl_request_id
5073 );
5074
5075 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
5076 RAISE FND_API.G_EXC_ERROR;
5077 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
5078 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5079 END IF;
5080
5081
5082 --create a membership record with the default program id and grant all the benefits and ----responibilities associated with this program.
5083 pv_pg_memb_rec.enrl_request_id:=l_enrl_request_id;
5084
5085 setmembershipdetails( pv_pg_memb_rec => pv_pg_memb_rec
5086 ,x_return_status =>x_return_status
5087 ,x_msg_count =>x_msg_count
5088 ,x_msg_data =>x_msg_data
5089 );
5090
5091 PV_Pg_Memberships_PVT.Create_Pg_memberships
5092 (
5093 p_api_version_number=>1.0
5094 ,p_init_msg_list => FND_API.g_false
5095 ,p_commit => FND_API.G_FALSE
5096 ,p_validation_level => FND_API.g_valid_level_full
5097 ,x_return_status => x_return_status
5098 ,x_msg_count => x_msg_count
5099 ,x_msg_data => x_msg_data
5100 ,p_memb_rec => pv_pg_memb_rec
5101 ,x_membership_id => l_membership_id
5102 );
5103
5104 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
5105 RAISE FND_API.G_EXC_ERROR;
5106 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
5107 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5108 END IF;
5109
5110 -- call responsiblity management api.
5111
5112 Pv_User_Resp_Pvt.manage_memb_resp
5113 ( p_api_version_number => 1.0
5114 ,p_init_msg_list => Fnd_Api.g_false
5115 ,p_commit => Fnd_Api.g_false
5116 ,p_membership_id => l_membership_id
5117 ,x_return_status => x_return_status
5118 ,x_msg_count => x_msg_count
5119 ,x_msg_data => x_msg_data
5120 );
5121
5122 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
5123 RAISE FND_API.G_EXC_ERROR;
5124 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
5125 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5126 END IF;
5127
5128 OPEN c_program_csr( l_default_program_id );
5129 FETCH c_program_csr INTO l_program_name;
5130 CLOSE c_program_csr;
5131
5132 --call the history log api to capture any comments from the user
5133 l_param_tbl_var(1).param_name := 'PROGRAM_NAME';
5134 l_param_tbl_var(1).param_value := l_program_name;
5135
5136 PVX_UTILITY_PVT.create_history_log
5137 ( p_arc_history_for_entity_code => 'MEMBERSHIP'
5138 ,p_history_for_entity_id => l_enrl_request_id
5139 ,p_history_category_code => 'APPROVAL'
5140 ,p_message_code => 'PV_DEFAULT_MEMBERSHIP'
5141 ,p_comments => null
5142 ,p_partner_id => p_partner_id
5143 ,p_access_level_flag => 'P'
5144 ,p_interaction_level => PVX_Utility_PVT.G_INTERACTION_LEVEL_50
5145 ,p_log_params_tbl => l_param_tbl_var
5146 ,p_init_msg_list => FND_API.g_false
5147 ,p_commit => FND_API.G_FALSE
5148 ,x_return_status => x_return_status
5149 ,x_msg_count => x_msg_count
5150 ,x_msg_data => x_msg_data
5151 );
5152
5153 -- calling the new workflow process in 11.5.10 pukken
5154 -- call workflow notification only if atleast one primary user exit
5155
5156 OPEN isPrimaryExist( p_partner_id );
5157 FETCH isPrimaryExist INTO l_isPrimaryFlag;
5158 ClOSE isPrimaryExist;
5159
5160 IF l_isPrimaryFlag= 'Y' THEN
5161 PV_PG_NOTIF_UTILITY_PVT.Send_Workflow_Notification
5162 (
5163 p_api_version_number => 1.0
5164 , p_init_msg_list => Fnd_Api.g_false
5165 , p_commit => Fnd_Api.g_false
5166 , p_validation_level => FND_API.g_valid_level_full
5167 , p_context_id => p_partner_id
5168 , p_context_code => 'PARTNER'
5169 , p_target_ctgry => 'PARTNER'
5170 , p_target_ctgry_pt_id => p_partner_id
5171 , p_notif_event_code => 'PG_WELCOME'
5172 , p_entity_id => l_enrl_request_id
5173 , p_entity_code => 'ENRQ'
5174 , p_wait_time => 0
5175 , x_return_status => x_return_status
5176 , x_msg_count => x_msg_count
5177 , x_msg_data => x_msg_data
5178 );
5179
5180
5181 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
5182 RAISE FND_API.G_EXC_ERROR;
5183 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
5184 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5185 END IF;
5186 END IF;
5187
5188 END IF;
5189 -- Standard check for p_commit
5190 IF FND_API.to_Boolean( p_commit ) THEN
5191 COMMIT WORK;
5192 END IF;
5193
5194 EXCEPTION
5195
5196
5197 WHEN FND_API.G_EXC_ERROR THEN
5198 ROLLBACK TO Create_Default_Membership;
5199 x_return_status := FND_API.G_RET_STS_ERROR;
5200 -- Standard call to get message count and if count=1, get the message
5201 FND_MSG_PUB.Count_And_Get (
5202 p_encoded => FND_API.G_FALSE,
5203 p_count => x_msg_count,
5204 p_data => x_msg_data
5205 );
5206
5207 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
5208 ROLLBACK TO Create_Default_Membership;
5209 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5210 -- Standard call to get message count and if count=1, get the message
5211 FND_MSG_PUB.Count_And_Get (
5212 p_encoded => FND_API.G_FALSE,
5213 p_count => x_msg_count,
5214 p_data => x_msg_data
5215 );
5216
5217 WHEN OTHERS THEN
5218 ROLLBACK TO Create_Default_Membership;
5219 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5220 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
5221 THEN
5222 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
5223 END IF;
5224 -- Standard call to get message count and if count=1, get the message
5225 FND_MSG_PUB.Count_And_Get (
5226 p_encoded => FND_API.G_FALSE,
5227 p_count => x_msg_count,
5228 p_data => x_msg_data
5229 );
5230
5231 End Create_Default_Membership;
5232
5233 ---------------------------------------------------------------------
5234 -- PURPOSE
5235 -- 1.Send thank you notification if enrollment is submiited for approval.
5236 -- 2.Send other required notifications , which are
5237 -- a) If member type is Subsidiary, then sent to global partner about subsidiaries enrolllment( sent to global)
5238 -- b) IMP applies for membership into Partner Program (sent to VAD) if VAD invited IMP for this enrollment
5239 -- HISTORY
5240 -- 31-Oct-2003 pukken CREATION
5241
5242 PROCEDURE send_notifications
5243 (
5244 p_api_version_number IN NUMBER
5245 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
5246 , p_commit IN VARCHAR2 := FND_API.G_FALSE
5247 , p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
5248 , p_partner_id IN NUMBER
5249 , p_enrl_request_id IN NUMBER -- enrollment request id
5250 , p_memb_type IN VARCHAR2 -- member type of the partner
5251 , p_enrq_status IN VARCHAR2 -- enrollment_status pass 'AWAITING_APPROVAL' incase submitting for approval
5252 , x_return_status OUT NOCOPY VARCHAR2
5253 , x_msg_count OUT NOCOPY NUMBER
5254 , x_msg_data OUT NOCOPY VARCHAR2
5255 )
5256 IS
5257 l_api_name CONSTANT VARCHAR2(30) := 'send_notifications';
5258 l_api_version_number CONSTANT NUMBER := 1.0;
5259 l_memb_type VARCHAR2(30);
5260 l_global_partner_id NUMBER;
5261 l_notif_event_code VARCHAR2(30);
5262 l_context_code VARCHAR2(30);
5263 l_vad_ptr_id NUMBER;
5264 l_lookup_exists VARCHAR2(1);
5265 CURSOR c_vad_csr(enrl_id IN NUMBER ) IS
5266 SELECT invite.invited_by_partner_id
5267 FROM pv_pg_invite_headers_b invite
5268 , pv_pg_enrl_requests enrq
5269 WHERE enrq.enrl_request_id=enrl_id
5270 AND enrq.invite_header_id=invite.invite_header_id;
5271
5272 CURSOR c_memb_type_csr(ptr_id NUMBER) IS
5273 SELECT enty.attr_value
5274 FROM pv_enty_attr_values enty
5275 WHERE enty.entity = 'PARTNER'
5276 AND enty.entity_id = ptr_id
5277 AND enty.attribute_id = 6
5278 AND enty.latest_flag = 'Y';
5279
5280 CURSOR c_get_global_csr ( p_subs_partner_id IN NUMBER ) IS
5281 SELECT global_prof.partner_id
5282 FROM pv_partner_profiles global_prof
5283 , pv_partner_profiles subs_prof
5284 , hz_relationships rel
5285 WHERE subs_prof.partner_party_id=rel.subject_id
5286 AND rel.relationship_code = 'SUBSIDIARY_OF'
5287 AND rel.relationship_type = 'PARTNER_HIERARCHY'
5288 AND rel.status = 'A'
5289 AND NVL(rel.start_date, SYSDATE) <= SYSDATE
5290 AND NVL(rel.end_date, SYSDATE) >= SYSDATE
5291 AND subs_prof.partner_id=p_subs_partner_id
5292 AND rel.object_id=global_prof.partner_party_id;
5293
5294 BEGIN
5295 -- Standard Start of API savepoint
5296 SAVEPOINT send_notifications ;
5297 -- Standard call to check for call compatibility.
5298 IF NOT FND_API.Compatible_API_Call
5299 ( l_api_version_number
5300 ,p_api_version_number
5301 ,l_api_name
5302 ,G_PKG_NAME
5303 )
5304 THEN
5305 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5306 END IF;
5307 -- Initialize message list if p_init_msg_list is set to TRUE.
5308 IF FND_API.to_Boolean( p_init_msg_list ) THEN
5309 FND_MSG_PUB.initialize;
5310 END IF;
5311 -- Debug Message
5312 PVX_UTILITY_PVT.debug_message( 'Private API: ' || l_api_name || 'start' );
5313 -- Initialize API return status to SUCCESS
5314 x_return_status := FND_API.G_RET_STS_SUCCESS;
5315 -- Validate Environment
5316 IF FND_GLOBAL.USER_ID IS NULL THEN
5317 PVX_UTILITY_PVT.Error_Message(p_message_name => 'USER_PROFILE_MISSING');
5318 RAISE FND_API.G_EXC_ERROR;
5319 END IF;
5320
5321 --validate the lookupcode for target category
5322 l_lookup_exists := PVX_UTILITY_PVT.check_lookup_exists
5323 ( p_lookup_table_name => 'PV_LOOKUPS'
5324 ,p_lookup_type => 'PV_ENRQ_REQUEST_STATUS_CODE'
5325 ,p_lookup_code => p_enrq_status
5326 );
5327
5328 IF NOT FND_API.to_boolean(l_lookup_exists) THEN
5329 FND_MESSAGE.set_name('PV', 'PV_INVALID_LOOKUP_CODE');
5330 FND_MESSAGE.set_token('LOOKUP_TYPE','PV_ENRQ_REQUEST_STATUS_CODE' );
5331 FND_MESSAGE.set_token('LOOKUP_CODE', p_enrq_status );
5332 RAISE FND_API.G_EXC_ERROR;
5333 END IF;
5334
5335 -- send thank you notifictaion
5336 IF p_enrq_status ='AWAITING_APPROVAL' THEN
5337 PV_PG_NOTIF_UTILITY_PVT.Send_Workflow_Notification
5338 (
5339 p_api_version_number => p_api_version_number
5340 , p_init_msg_list => p_init_msg_list
5341 , p_commit => p_commit
5342 , p_validation_level => p_validation_level
5343 , p_context_id => p_partner_id
5344 , p_context_code => 'PARTNER'
5345 , p_target_ctgry => 'PARTNER'
5346 , p_target_ctgry_pt_id => p_partner_id
5347 , p_notif_event_code => 'PG_THANKYOU'
5348 , p_entity_id => p_enrl_request_id
5349 , p_entity_code => 'ENRQ'
5350 , p_wait_time => 0
5351 , x_return_status => x_return_status
5352 , x_msg_count => x_msg_count
5353 , x_msg_data => x_msg_data
5354 );
5355 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
5356 RAISE FND_API.G_EXC_ERROR;
5357 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
5358 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5359 END IF;
5360 END IF;
5361
5362 l_memb_type := p_memb_type;
5363 IF l_memb_type IS NULL THEN
5364 OPEN c_memb_type_csr(p_partner_id);
5365 FETCH c_memb_type_csr INTO l_memb_type;
5366 CLOSE c_memb_type_csr;
5367 END IF;
5368
5369 IF l_memb_type = 'SUBSIDIARY' THEN
5370 OPEN c_get_global_csr( p_partner_id );
5371 FETCH c_get_global_csr INTO l_global_partner_id;
5372 CLOSE c_get_global_csr;
5373 -- send notification to the global partner
5374
5375 IF p_enrq_status IN ( 'APPROVED', 'REJECTED' ) THEN
5376 l_notif_event_code := 'SUBSIDIARY_PTNR_ENROLL';
5377 l_context_code := p_enrq_status;
5378 ELSIF p_enrq_status = 'AWAITING_APPROVAL' THEN
5379 l_notif_event_code := 'SUBSIDIARY_PTNR_APPLY';
5380 l_context_code := 'PARTNER';
5381 END IF;
5382 PV_PG_NOTIF_UTILITY_PVT.Send_Workflow_Notification
5383 (
5384 p_api_version_number => 1.0
5385 , p_init_msg_list => FND_API.G_FALSE
5386 , p_commit => FND_API.G_FALSE
5387 , p_validation_level => FND_API.G_VALID_LEVEL_FULL
5388 , p_context_id => p_partner_id --this should be subsidiary partner_id.
5389 , p_context_code => l_context_code
5390 , p_target_ctgry => 'GLOBAL'
5391 , p_target_ctgry_pt_id => l_global_partner_id -- this should be global partner id
5392 , p_notif_event_code => l_notif_event_code
5393 , p_entity_id => p_enrl_request_id
5394 , p_entity_code => 'ENRQ'
5395 , p_wait_time => 0
5396 , x_return_status => x_return_status
5397 , x_msg_count => x_msg_count
5398 , x_msg_data => x_msg_data
5399 );
5400 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
5401 RAISE FND_API.G_EXC_ERROR;
5402 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
5403 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5404 END IF;
5405 END IF;
5406 -- also need to make a call to send notifictaion to VAD incase this enrollment is beacuse of VAD inviation
5407 -- pass notifp_notif_event_code => 'IMP_APPLY' if its apply
5408 -- pass notifp_notif_event_code => 'IMP_ACCEPTED'if its application has been accepted
5409
5410 OPEN c_vad_csr(p_enrl_request_id);
5411 FETCH c_vad_csr INTO l_vad_ptr_id ;
5412 CLOSE c_vad_csr;
5413
5414 IF l_vad_ptr_id IS NOT NULL AND p_enrq_status IN ('APPROVED','AWAITING_APPROVAL') THEN
5415 IF p_enrq_status ='APPROVED' THEN
5416 l_notif_event_code := 'IMP_ACCEPTED';
5417 ELSIF p_enrq_status = 'AWAITING_APPROVAL' THEN
5418 l_notif_event_code := 'IMP_APPLY';
5419 END IF;
5420 PV_PG_NOTIF_UTILITY_PVT.Send_Workflow_Notification
5421 (
5422 p_api_version_number => 1.0
5423 , p_init_msg_list => FND_API.G_FALSE
5424 , p_commit => FND_API.G_FALSE
5425 , p_validation_level => FND_API.G_VALID_LEVEL_FULL
5426 , p_context_id => p_partner_id --this should be partner_id.
5427 , p_context_code => 'PARTNER'
5428 , p_target_ctgry => 'VAD'
5429 , p_target_ctgry_pt_id => l_vad_ptr_id -- this should be VAD PARTNER ID
5430 , p_notif_event_code => l_notif_event_code
5431 , p_entity_id => p_enrl_request_id
5432 , p_entity_code => 'ENRQ'
5433 , p_wait_time => 0
5434 , x_return_status => x_return_status
5435 , x_msg_count => x_msg_count
5436 , x_msg_data => x_msg_data
5437 );
5438 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
5439 RAISE FND_API.G_EXC_ERROR;
5440 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
5441 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5442 END IF;
5443 END IF;
5444 -- Debug Message
5445 PVX_UTILITY_PVT.debug_message( 'Private API: ' || l_api_name || 'end' );
5446 -- Standard call to get message count and if count is 1, get message info.
5447 FND_MSG_PUB.Count_And_Get
5448 (
5449 p_count => x_msg_count
5450 , p_data => x_msg_data
5451 );
5452 IF FND_API.to_Boolean( p_commit ) THEN
5453 COMMIT WORK;
5454 END IF;
5455
5456 EXCEPTION
5457 WHEN FND_API.G_EXC_ERROR THEN
5458 ROLLBACK TO send_notifications;
5459 x_return_status := FND_API.G_RET_STS_ERROR;
5460 -- Standard call to get message count and if count=1, get the message
5461 FND_MSG_PUB.Count_And_Get (
5462 p_encoded => FND_API.G_FALSE,
5463 p_count => x_msg_count,
5464 p_data => x_msg_data
5465 );
5466
5467 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
5468 ROLLBACK TO send_notifications;
5469 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5470 -- Standard call to get message count and if count=1, get the message
5471 FND_MSG_PUB.Count_And_Get (
5472 p_encoded => FND_API.G_FALSE,
5473 p_count => x_msg_count,
5474 p_data => x_msg_data
5475 );
5476
5477 WHEN OTHERS THEN
5478 ROLLBACK TO send_notifications;
5479 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5480 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
5481 THEN
5482 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
5483 END IF;
5484 -- Standard call to get message count and if count=1, get the message
5485 FND_MSG_PUB.Count_And_Get (
5486 p_encoded => FND_API.G_FALSE,
5487 p_count => x_msg_count,
5488 p_data => x_msg_data
5489 );
5490 END send_notifications;
5491
5492 END pv_prgm_approval_pvt;