[Home] [Help]
PACKAGE BODY: APPS.PV_PG_NOTIF_UTILITY_PVT
Source
1 PACKAGE BODY PV_PG_NOTIF_UTILITY_PVT as
2 /* $Header: pvxvpnub.pls 120.9 2011/04/20 06:30:21 hekkiral ship $ */
3
4 /*----------------------------------------------------------------------------
5 -- HISTORY
6 -- 20-SEP-2002 rdsharma Created
7 -- 11-NOV-2002 SVEERAVE Since the package pv_pgp_notif_pvt is changed to
8 -- pv_ge_party_notif_pvt, all the references are changed
9 -- accordingly. Since pv_ge_party_notifications table
10 -- is change to have partner_id column instead of partner_party_id
11 -- set_pgp_notif procedure is changed to capture partner_id
12 -- all call-outs to set_pgp_notif is changed to pass partner_id
13 -- intead of partner_party_id.
14 -- 05-DEC-2002 SVEERAVE Replaced new line character with <BR> for html formatting,
15 -- Added get_Notification_Body procedure.
16 -- 06-DEC-2002 SVEERAVE Replaced partner portal URL with jtflogin.jsp for now.
17 -- 11-DEC-2002 RDSHARMA Modified the file to resolve the issue for
18 -- Termination notificationn by changing the HISTORY
19 -- CATEGORY to ENROLLMENT, during creation of History
20 -- log record.
21 -- 12-MAR-2002 RDSHARMA Modified the file to resolve the issue for
22 -- Bug # 2794559.
23 -- 03/25/2003 sveerave Modified from GetFullBody to GetBody as
24 -- GetFullBody is failing to get full message body for bug#2862626
25 -- 03/25/2003 sveerave Modified from GetFullBody to GetBody as
26 -- GetFullBody is failing to get full message body for bug#2862626
27 -- 08/04/2003 sveerave Fix for bug#3072153. Changed <BR> to wf_core.newline for alert_message
28 -- notifications, and wherever there is URL, i.e. send_welcome_notif,
29 -- send_mbrship_exp_notif- those notifications are implemented
30 -- through pl/sql document for which added a new w/f enabled pl/sql doc
31 -- proc, set_msg_doc, and the above two procedures are modified to
32 -- to call this notification.
33 -- 08/22/2003 sveerave Fix for bug# 3107892. Changed message from ALERT_MESSAGE to
34 -- DOC_MESSAGE in send_mbrship_exp_notif method.
35 -- 09/15/2003 sveerave Added partner_id for create_history_log API call out
36 -- Changed ENRQ object to GENERAL, and entity_object_id to
37 -- partner_id instead of enrl_request_id in this call out
38 -- 10/08/2003 pukken Modified Expire_Membership to procedure. called PV_Pg_Memberships_PVT.Terminate_membership
39 -- with event code as expired.
40 -- 10/18/2003 pukken Added new procedure Send_Workflow_Notification to send the notifications
41 -- 30-MAR-2004 pukken fix bug 3428446
42 -- 15-APR-2005 pukken to fix bug 4301902 to modify Expire_Memberships conc program so that even if
43 -- expire or renewal fails for one membership, processing continues and error is logged.
44
45 -- 31-OCT-2005 kvattiku In send_welcome_notif, commented out and modified code so that the URL is
46 obtained from profile. (fix for bug 4666288)
47 -----------------------------------------------------------------------------*/
48
49 G_PKG_NAME CONSTANT VARCHAR2(30):= 'PV_PG_NOTIF_UTILITY_PVT';
50 G_FILE_NAME CONSTANT VARCHAR2(12) := 'pvxvpnub.pls';
51
52 -- G_USER_ID NUMBER := FND_GLOBAL.USER_ID;
53 -- G_LOGIN_ID NUMBER := FND_GLOBAL.CONC_LOGIN_ID;
54
55 -- Logging function - Local
56 --
57 -- p_which = 1. write to log
58 -- p_which = 2, write to output
59 --
60 PV_DEBUG_HIGH_ON CONSTANT boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);
61 PV_DEBUG_LOW_ON CONSTANT boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
62 PV_DEBUG_MEDIUM_ON CONSTANT boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM);
63 g_log_level CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
64
65 PROCEDURE Write_Log(p_which number, p_mssg varchar2) IS
66 BEGIN
67 FND_FILE.put(p_which, p_mssg);
68 FND_FILE.NEW_LINE(p_which, 1);
69 END Write_Log;
70
71
72
73
74 FUNCTION logging_enabled (p_log_level IN NUMBER)
75 RETURN BOOLEAN
76 IS
77 BEGIN
78 RETURN (p_log_level >= FND_LOG.G_CURRENT_RUNTIME_LEVEL);
79 END;
80
81 PROCEDURE debug_message
82 (
83 p_log_level IN NUMBER
84 , p_module_name IN VARCHAR2
85 , p_text IN VARCHAR2
86 )
87 IS
88 BEGIN
89 /*
90 IF logging_enabled (p_log_level) THEN
91 FND_LOG.STRING(p_log_level, p_module_name, p_text);
92 END IF;
93 */
94 -- Debug Message
95 IF (PV_DEBUG_HIGH_ON) THEN
96 PVX_UTILITY_PVT.debug_message( p_module_name ||' : '|| p_text);
97 END IF;
98
99 END debug_message;
100
101 PROCEDURE WRITE_TO_FND_LOG
102 (
103 p_api_name IN VARCHAR2
104 , p_log_message IN VARCHAR2
105 )
106 IS
107
108 BEGIN
109 debug_message
110 (
111 p_log_level => g_log_level
112 , p_module_name => 'plsql.pv'||'.'|| g_pkg_name||'.'||p_api_name||'.'||p_log_message
113 , p_text => p_log_message
114 );
115 END WRITE_TO_FND_LOG;
116
117 --======================
118 /*============================================================================
119 -- Start of Comments
120 -- PROCEDURE
121 -- get_enrl_requests_details
122 --
123 -- PURPOSE
124 -- This procedure will return enrollment request details
125 -- from PV_PG_ENRL_REQUESTS table for a given enrl_requests_id
126 -- Called By
127 -- NOTES
128 -- End of Comments
129 ============================================================================*/
130 PROCEDURE get_enrl_requests_details(
131 p_enrl_request_id IN NUMBER,
132 x_req_submission_date OUT NOCOPY DATE,
133 x_partner_program_id OUT NOCOPY NUMBER,
134 x_partner_program OUT NOCOPY VARCHAR2,
135 x_enrollment_duration OUT NOCOPY VARCHAR2,
136 x_enrollment_type OUT NOCOPY VARCHAR2,
137 x_req_resource_id OUT NOCOPY NUMBER,
138 x_prtnr_vndr_relship_id OUT NOCOPY NUMBER,
139 x_return_status OUT NOCOPY VARCHAR2
140 )
141 IS
142
143 /* Get the Enrollment Request details in cursor c_pg_enrl_requests */
144 CURSOR c_pg_enrl_requests (cv_enrl_request_id IN NUMBER) IS
145 SELECT enrl_req.creation_date,
146 prog.program_id,
147 prog.program_name,
148 prog.membership_valid_period ||' ' || lookup1.meaning,
149 lookup2.meaning,
150 enrl_req.Requestor_resource_id,
151 enrl_req.partner_id
152 FROM pv_pg_enrl_requests enrl_req,
153 pv_partner_program_vl prog,
154 fnd_lookups lookup1,
155 fnd_lookups lookup2
156 WHERE enrl_req.enrl_request_id = cv_enrl_request_id
157 AND enrl_req.program_id = prog.program_id
158 AND lookup1.lookup_type='PV_PROGRAM_PMNT_UNIT'
159 AND lookup1.lookup_code = prog.membership_period_unit
160 AND lookup2.lookup_type='PV_ENROLLMENT_REQUEST_TYPE'
161 AND lookup2.lookup_code = enrl_req.enrollment_type_code;
162
163 BEGIN
164 /* Initialize API return status to success */
165 x_return_status := FND_API.G_RET_STS_SUCCESS;
166
167 OPEN c_pg_enrl_requests( p_enrl_request_id );
168 FETCH c_pg_enrl_requests
169 INTO x_req_submission_date,
170 x_partner_program_id,
171 x_partner_program,
172 x_enrollment_duration,
173 x_enrollment_type,
174 x_req_resource_id,
175 x_prtnr_vndr_relship_id ;
176
177 IF ( c_pg_enrl_requests%NOTFOUND) THEN
178 x_return_status := FND_API.G_RET_STS_ERROR;
179 END IF;
180
181 CLOSE c_pg_enrl_requests;
182
183 EXCEPTION
184 WHEN OTHERS THEN
185 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
186 END get_enrl_requests_details;
187
188 /*============================================================================
189 -- Start of Comments
190 -- PROCEDURE
191 -- get_requestor_details
192 --
193 -- PURPOSE
194 -- This procedure will return enrollment requestor details from
195 -- JTF_RS_RESOURCE_EXTNS table for a given resource_id
196 -- Called By
197 -- NOTES
198 -- End of Comments
199 =============================================================================*/
200 PROCEDURE get_requestor_details(
201 p_req_resource_id IN NUMBER,
202 x_user_id OUT NOCOPY NUMBER,
203 x_source_name OUT NOCOPY VARCHAR2,
204 x_user_name OUT NOCOPY VARCHAR2,
205 x_return_status OUT NOCOPY VARCHAR2
206 )
207 IS
208 /* Get the Requestor details in cursor c_reqestor_details */
209 CURSOR c_requestor_details(cv_req_resource_id IN NUMBER) IS
210 SELECT user_id, source_name, user_name
211 FROM JTF_RS_RESOURCE_EXTNS
212 WHERE resource_id = cv_req_resource_id;
213
214 BEGIN
215 /* Initialize API return status to success */
216 x_return_status := FND_API.G_RET_STS_SUCCESS;
217
218 OPEN c_requestor_details( p_req_resource_id );
219 FETCH c_requestor_details
220 INTO x_user_id,
221 x_source_name,
222 x_user_name;
223
224 IF ( c_requestor_details%NOTFOUND) THEN
225 x_return_status := FND_API.G_RET_STS_ERROR;
226 END IF;
227
228 CLOSE c_requestor_details;
229
230 EXCEPTION
231 WHEN OTHERS THEN
232 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
233 END get_requestor_details;
234
235
236 /*============================================================================
237 -- Start of Comments
238 -- PROCEDURE
239 -- get_partnor_vendor_details
240 --
241 -- PURPOSE
242 -- This procedure will return partner and vendor details
243 -- given the partner_id
244 --
245 -- Called By
246 -- NOTES
247 -- End of Comments
248 =============================================================================*/
249 PROCEDURE get_partner_vendor_details(
250 p_partner_id IN NUMBER,
251 x_vendor_party_id OUT NOCOPY NUMBER,
252 x_vendor_name OUT NOCOPY VARCHAR2,
253 x_partner_party_id OUT NOCOPY NUMBER,
254 x_partner_comp_name OUT NOCOPY VARCHAR2,
255 x_return_status OUT NOCOPY VARCHAR2
256 )
257 IS
258
259 /* Get the Partner and Vendor details in cursor c_vendor_prtnr_details */
260 CURSOR c_vendor_prtnr_details(cv_partner_id IN NUMBER) IS
261 SELECT vendor.party_id VENDOR_ID,
262 vendor.party_name VENDOR_NAME,
263 partner.party_id PARTNER_ID,
264 partner.party_name PARTNER_NAME
265 FROM pv_partner_profiles prtnr_profile,
266 hz_relationships rel_ship,
267 hz_parties partner,
268 hz_parties vendor
269 WHERE prtnr_profile.partner_id =cv_partner_id
270 AND prtnr_profile.partner_id = rel_ship.party_id
271 AND prtnr_profile.partner_party_id = rel_ship.object_id
272 AND rel_ship.party_id = cv_partner_id
273 AND rel_ship.subject_id = vendor.party_id
274 AND rel_ship.object_id = partner.PARTY_ID;
275
276
277 BEGIN
278
279 /* Initialize API return status to success */
280 x_return_status := FND_API.G_RET_STS_SUCCESS;
281
282 /* Get the Partner and Vendor details in cursor c_vendor_prtnr_details */
283 OPEN c_vendor_prtnr_details( p_partner_id );
284 FETCH c_vendor_prtnr_details
285 INTO x_vendor_party_id,
286 x_vendor_name ,
287 x_partner_party_id,
288 x_partner_comp_name;
289
290 IF ( c_vendor_prtnr_details%NOTFOUND) THEN
291 x_return_status := FND_API.G_RET_STS_ERROR;
292 END IF;
293
294 CLOSE c_vendor_prtnr_details;
295
296 EXCEPTION
297 WHEN OTHERS THEN
298 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
299 END get_partner_vendor_details;
300
301 /*============================================================================
302 -- Start of Comments
303 -- PROCEDURE
304 -- get_prtnr_vendor_details
305 --
306 -- PURPOSE
307 -- This procedure will return partner and vendor details
308 -- for a given relationship_id between Partner and the Vendor
309 -- Called By
310 -- NOTES
311 -- End of Comments
312 =============================================================================*/
313 PROCEDURE get_prtnr_vendor_details(
314 p_enrl_request_id IN NUMBER,
315 x_vendor_party_id OUT NOCOPY NUMBER,
316 x_vendor_name OUT NOCOPY VARCHAR2,
317 x_partner_party_id OUT NOCOPY NUMBER,
318 x_partner_comp_name OUT NOCOPY VARCHAR2,
319 x_return_status OUT NOCOPY VARCHAR2
320 )
321 IS
322
323 /* Get the Partner and Vendor details in cursor c_vendor_prtnr_details */
324 CURSOR c_vendor_prtnr_details(cv_enrl_request_id IN NUMBER) IS
325 SELECT vendor.party_id VENDOR_ID,
326 vendor.party_name VENDOR_NAME,
327 partner.party_id PARTNER_ID,
328 partner.party_name PARTNER_NAME
329 FROM pv_pg_enrl_requests enrl_req,
330 pv_partner_profiles prtnr_profile,
331 hz_relationships rel_ship,
332 hz_parties partner,
333 hz_parties vendor
334 WHERE enrl_req.enrl_request_id = cv_enrl_request_id
335 AND enrl_req.partner_id= prtnr_profile.partner_id
336 AND prtnr_profile.partner_id = rel_ship.party_id
337 AND prtnr_profile.partner_party_id = rel_ship.object_id
338 AND enrl_req.partner_id = rel_ship.party_id
339 AND rel_ship.subject_id = vendor.party_id
340 AND rel_ship.object_id = partner.PARTY_ID;
341
342
343 BEGIN
344
345 /* Initialize API return status to success */
346 x_return_status := FND_API.G_RET_STS_SUCCESS;
347
348 /* Get the Partner and Vendor details in cursor c_vendor_prtnr_details */
349 OPEN c_vendor_prtnr_details( p_enrl_request_id );
350 FETCH c_vendor_prtnr_details
351 INTO x_vendor_party_id,
352 x_vendor_name ,
353 x_partner_party_id,
354 x_partner_comp_name;
355
356 IF ( c_vendor_prtnr_details%NOTFOUND) THEN
357 x_return_status := FND_API.G_RET_STS_ERROR;
358 END IF;
359
360 CLOSE c_vendor_prtnr_details;
361
362 EXCEPTION
363 WHEN OTHERS THEN
364 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
365 END get_prtnr_vendor_details;
366
367 /*============================================================================
368 -- Start of Comments
369 -- PROCEDURE
370 -- get_membership_details
371 --
372 -- PURPOSE
373 -- This procedure will return all the membership details for a
374 -- partner user enrolled for a program for a given membership_id
375 -- Called By
376 -- NOTES
377 -- End of Comments
378 =============================================================================*/
379 PROCEDURE get_membership_details(
380 p_membership_id IN NUMBER,
381 x_req_submission_date OUT NOCOPY DATE,
382 x_partner_program_id OUT NOCOPY NUMBER,
383 x_partner_program OUT NOCOPY VARCHAR2,
384 x_enrl_request_id OUT NOCOPY NUMBER,
385 x_enrollment_start_date OUT NOCOPY DATE,
386 x_enrollment_end_date OUT NOCOPY DATE,
387 x_req_resource_id OUT NOCOPY NUMBER,
388 x_prtnr_vndr_relship_id OUT NOCOPY NUMBER,
389 x_enrollment_type OUT NOCOPY VARCHAR2,
390 x_return_status OUT NOCOPY VARCHAR2
391 )
392 IS
393
394 /* Cursor : Get the Membership details in cursor c_pg_membership_details */
395 CURSOR c_pg_membership_details (cv_membership_id IN NUMBER) IS
396 SELECT enrl_req.creation_date ,
397 program.program_id ,
398 program.program_name ,
399 membership.enrl_request_id ,
400 membership.start_date ,
401 membership.original_end_date ,
402 enrl_req.requestor_resource_id,
403 enrl_req.partner_id,
404 lookup.meaning
405 FROM pv_pg_memberships membership,
406 pv_pg_enrl_requests enrl_req,
407 pv_partner_program_vl program,
408 fnd_lookups lookup
409 WHERE membership.membership_id = cv_membership_id
410 AND enrl_req.enrl_request_id = membership.enrl_request_id
411 AND membership.program_id = program.program_id
412 AND lookup.lookup_type='PV_ENROLLMENT_REQUEST_TYPE'
413 AND lookup.lookup_code = enrl_req.enrollment_type_code;
414
415 BEGIN
416 /* Initialize API return status to success */
417 x_return_status := FND_API.G_RET_STS_SUCCESS;
418
419 /* Get the Enrollment details in cursor c_pg_enrollment_details */
420 OPEN c_pg_membership_details( p_membership_id );
421 FETCH c_pg_membership_details INTO
422 x_req_submission_date ,
423 x_partner_program_id ,
424 x_partner_program ,
425 x_enrl_request_id ,
426 x_enrollment_start_date ,
427 x_enrollment_end_date ,
428 x_req_resource_id ,
429 x_prtnr_vndr_relship_id ,
430 x_enrollment_type ;
431
432 IF ( c_pg_membership_details%NOTFOUND) THEN
433 x_return_status := FND_API.G_RET_STS_ERROR;
434 END IF;
435
436 CLOSE c_pg_membership_details;
437
438 EXCEPTION
439 WHEN OTHERS THEN
440 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
441 END get_membership_details;
442
443 /*============================================================================*/
444 -- Start of Comments
445 -- NAME
446 -- Get_Users_List
447 --
448 -- PURPOSE
449 -- This Procedure will be return all the users for a given partner Vendor
450 -- relationship id and the requestor resourse id.
451 --
452 -- Called By
453 -- NOTES
454 -- End of Comments
455
456 /*============================================================================*/
457
458 PROCEDURE get_users_list
459 ( p_partner_id IN NUMBER,
460 x_user_notify_rec_tbl OUT NOCOPY user_notify_rec_tbl_type ,
461 x_user_count OUT NOCOPY NUMBER,
462 x_return_status OUT NOCOPY VARCHAR2
463 )
464 IS
465
466 l_user_type VARCHAR2(255);
467 l_user_id NUMBER;
468 l_user_name VARCHAR2(100);
469 l_user_resource_id NUMBER;
470 l_user_cnt NUMBER;
471
472 /* Declare the cursor to get the USERS List for the given partner_id
473 and the user type (of type PV_PARTNER_PRIMARY_USER or
474 PV_PARTNER_BUSINESS_USER ) */
475 CURSOR c_user_list (cv_partner_id IN NUMBER) IS
476 SELECT user_id,
477 user_name,
478 resource_id
479 FROM pv_partner_primary_users_v
480 WHERE partner_id = cv_partner_id ;
481
482 BEGIN
483 /* Initialize API return status to success */
484 x_return_status := FND_API.G_RET_STS_SUCCESS;
485
486 /* Open the c_user_list cursor to get all the Users of that type */
487 OPEN c_user_list (p_partner_id) ;
488 l_user_cnt := 1;
489 LOOP
490 FETCH c_user_list INTO l_user_id, l_user_name, l_user_resource_id;
491 EXIT WHEN c_user_list%notfound;
492 x_user_notify_rec_tbl(l_user_cnt).user_id := l_user_id;
493 x_user_notify_rec_tbl(l_user_cnt).user_name := l_user_name;
494 x_user_notify_rec_tbl(l_user_cnt).user_resource_id := l_user_resource_id;
495 l_user_cnt := l_user_cnt + 1;
496 END LOOP;
497 x_user_count := l_user_cnt - 1;
498
499 IF ( x_user_count = 0 ) THEN
500 x_return_status := FND_API.G_RET_STS_ERROR;
501 END IF;
502
503 CLOSE c_user_list ;
504 EXCEPTION
505 WHEN OTHERS THEN
506 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
507 END get_users_list;
508
509 /*============================================================================
510 -- Start of Comments
511 -- NAME
512 -- Get_Resource_Role
513 --
514 -- PURPOSE
515 -- This Procedure will be return the workflow user role for
516 -- the resourceid sent
517 -- Called By
518 -- NOTES
519 -- End of Comments
520
521 =============================================================================*/
522
523 PROCEDURE get_resource_role(
524 p_resource_id IN NUMBER,
525 x_role_name OUT NOCOPY VARCHAR2,
526 x_role_display_name OUT NOCOPY VARCHAR2 ,
527 x_return_status OUT NOCOPY VARCHAR2
528 )
529 IS
530 l_msg_count NUMBER;
531 l_msg_data VARCHAR2(4000);
532 l_error_msg VARCHAR2(4000);
533
534 CURSOR c_resource IS
535 SELECT source_id, user_id, category
536 FROM JTF_RS_RESOURCE_EXTNS_VL
537 WHERE resource_id > 0
538 AND ( category = 'EMPLOYEE'
539 OR category = 'PARTNER'
540 OR category = 'PARTY')
541 AND resource_id = p_resource_id ;
542
543 l_person_id number;
544 l_user_id number;
545 l_category varchar2(30);
546
547 BEGIN
548 /* Initialize API return status to success */
549 x_return_status := FND_API.G_RET_STS_SUCCESS;
550
551 OPEN c_resource ;
552 FETCH c_resource INTO l_person_id , l_user_id, l_category;
553 IF c_resource%NOTFOUND THEN
554 CLOSE c_resource ;
555 x_return_status := FND_API.G_RET_STS_ERROR;
556 return;
557 END IF;
558
559 CLOSE c_resource ;
560
561 /* Pass the Employee ID to get the Role */
562 IF l_category = 'PARTY' THEN
563 WF_DIRECTORY.getrolename
564 ( p_orig_system => 'FND_USR',
565 p_orig_system_id => l_user_id ,
566 p_name => x_role_name,
567 p_display_name => x_role_display_name
568 );
569 IF x_role_name is null then
570 x_return_status := FND_API.G_RET_STS_ERROR;
571 return;
572 END IF;
573 ELSIF l_category = 'EMPLOYEE' THEN
574 WF_DIRECTORY.getrolename
575 ( p_orig_system => 'PER',
576 p_orig_system_id => l_person_id ,
577 p_name => x_role_name,
578 p_display_name => x_role_display_name
579 );
580 IF x_role_name is null then
581 x_return_status := FND_API.G_RET_STS_ERROR;
582 return;
583 END IF;
584 END IF;
585 EXCEPTION
586 WHEN OTHERS THEN
587 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
588 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
589 fnd_msg_pub.add_exc_msg('AMS_UTLITY_PVT', 'Get_Resource_Role');
590 END IF;
591 RAISE;
592
593 END Get_Resource_Role;
594
595 /*============================================================================
596 -- Start of Comments
597 -- NAME
598 -- Get_Notification_Name
599 --
600 -- PURPOSE
601 -- This function returns the Notification name by selecting the
602 -- meaning for the send notification code for 'PV_NOTIFICATION_TYPE'.
603 -- Called By
604 -- NOTES
605 -- End of Comments
606
607 =============================================================================*/
608
609 FUNCTION get_Notification_Name(p_notif_code IN VARCHAR2)
610 RETURN VARCHAR2
611 IS
612 CURSOR c_notification IS
613 SELECT meaning
614 FROM FND_LOOKUPS
615 WHERE lookup_type= 'PV_NOTIFICATION_TYPE'
616 AND lookup_code= p_notif_code;
617
618 l_notification_name VARCHAR2(80):= NULL;
619
620 BEGIN
621 OPEN c_notification ;
622 FETCH c_notification INTO l_notification_name;
623
624 CLOSE c_notification ;
625 return l_notification_name;
626 END get_Notification_Name;
627
628 /*============================================================================
629 -- Start of Comments
630 -- NAME
631 -- Get_Program_Url
632 --
633 -- PURPOSE
634 -- This function returns the URL to display the benefits of the program
635 -- to the primary user, who's successfully enrolled for that program.
636 -- Called By
637 -- NOTES
638 -- End of Comments
639
640 =============================================================================*/
641
642 FUNCTION get_Program_Url(p_program_id IN NUMBER)
643 RETURN VARCHAR2
644 IS
645 /* Get the web server jsp agent. */
646 CURSOR c_jsp_agent IS
647 SELECT fnd_web_config.jsp_agent FROM dual;
648
649 /* Get the function url. */
650 CURSOR c_function_url IS
651 SELECT web_html_call
652 FROM fnd_form_functions
653 WHERE function_name = 'PV_ENRL_NOW';
654
655 /* Get the cItemVersionId. */
656 CURSOR c_Item_Version_Id(cv_program_id NUMBER) IS
657 SELECT nvl(citem_version_id ,0)
658 FROM pv_partner_program_b
659 WHERE program_id = cv_program_id;
660
661 l_jsp_agent VARCHAR2(200) := NULL;
662 l_function_url VARCHAR2(1000) := NULL;
663 l_item_ver_id NUMBER;
664 l_program_url VARCHAR2(4000) := NULL;
665 l_param_list VARCHAR2(1000) := NULL;
666 l_char VARCHAR2(1) := '&';
667
668 BEGIN
669
670 /* Get the web server jsp agent. */
671 OPEN c_jsp_agent ;
672 FETCH c_jsp_agent INTO l_jsp_agent ;
673 CLOSE c_jsp_agent ;
674
675 /* Get the function url. */
676 OPEN c_function_url;
677 FETCH c_function_url INTO l_function_url;
678 CLOSE c_function_url;
679
680 /* Get the cItemVersionId. */
681 OPEN c_Item_Version_Id(p_program_id);
682 FETCH c_Item_Version_Id INTO l_item_ver_id;
683 CLOSE c_Item_Version_Id ;
684
685 /* Concatenate the following paramters -
686 i) PAGE.OBJ.ID_NAME0=programId
687 ii) PAGE.OBJ.ID0=program_id
688 iii) PAGE.OBJ.ID_NAME1=cItemVersionId
689 iv) PAGE.OBJ.ID1=citem_ver_id
690 v) PAGE.OBJ.objType=MEMB'
691 */
692 l_param_list := '?PAGE.OBJ.ID_NAME0=programId' || l_char ;
693 l_param_list := l_param_list || 'PAGE.OBJ.ID0='||p_program_id|| l_char ;
694 l_param_list := l_param_list || 'PAGE.OBJ.ID_NAME1=cItemVersionId' || l_char ;
695 l_param_list := l_param_list || 'PAGE.OBJ.ID1='||l_item_ver_id|| l_char ;
696 l_param_list := l_param_list || 'PAGE.OBJ.objType=MEMB';
697
698 /* Form the final program URL */
699 l_program_url := l_jsp_agent || l_function_url || l_param_list ;
700
701 return l_program_url;
702 END get_Program_Url;
703
704 /*============================================================================
705 -- Start of Comments
706 -- NAME
707 -- Check_Notif_Rule_Active
708 --
709 -- PURPOSE
710 -- This Procedure will return the ACTIVE_FLAG value for the Notification
711 -- rule set for the given Program Id and Notification Type.
712 --
713 -- Called By
714 -- NOTES
715 -- End of Comments
716
717 =============================================================================*/
718
719 FUNCTION check_Notif_Rule_Active( p_program_id IN NUMBER ,
720 p_notif_type IN VARCHAR2 )
721 RETURN VARCHAR2
722 IS
723 CURSOR c_notif_rule_active IS
724 SELECT active_flag
725 FROM pv_ge_notif_rules_vl
726 WHERE notif_type_code = p_notif_type
727 AND arc_notif_for_entity_code = 'PRGM'
728 AND notif_for_entity_id = p_program_id ;
729
730 l_active_flag VARCHAR2(1) := 'Y' ;
731
732 BEGIN
733 OPEN c_notif_rule_active ;
734 FETCH c_notif_rule_active INTO l_active_flag;
735 CLOSE c_notif_rule_active ;
736
737 IF l_active_flag IS NULL THEN
738 l_active_flag:= 'Y';
739 END IF;
740 return l_active_flag;
741 END check_Notif_Rule_Active;
742
743 /*============================================================================
744 -- Start of Comments
745 -- PROCEDURE
746 -- Validate_Enrl_Requests
747 --
748 -- PURPOSE
749 -- This procedure validates the enrollment request id or enrollment id.
750 --
751 -- Called By
752 -- NOTES
753 -- End of Comments
754 ============================================================================*/
755 PROCEDURE Validate_Enrl_Requests (
756 p_item_id IN NUMBER ,
757 p_item_name IN VARCHAR2,
758 x_return_status OUT NOCOPY VARCHAR2
759 )
760 IS
761 error_flag BOOLEAN:=FALSE;
762 BEGIN
763 -- Initialize API return status to SUCCESS
764 x_return_status := FND_API.G_RET_STS_SUCCESS;
765
766 -- Hint: Validate data
767 -- If data not valid
768 -- THEN
769 -- x_return_status := FND_API.G_RET_STS_ERROR;
770 IF ( p_item_id = FND_API.G_MISS_NUM OR
771 p_item_id IS NULL ) THEN
772
773 error_flag := TRUE;
774 x_return_status := FND_API.G_RET_STS_ERROR;
775 FND_MESSAGE.SET_NAME('PV', 'PV_MISSING_ITEM');
776 FND_MESSAGE.SET_TOKEN('ITEM_NAME', p_item_name);
777 FND_MSG_PUB.Add;
778 END IF;
779
780
781
782 END Validate_Enrl_Requests;
783
784 /*============================================================================*/
785 -- Start of Comments
786 -- NAME
787 -- Set_Pgp_Notif
788 --
789 -- PURPOSE
790 -- This procedure set the proper values in pgp_notif_rec, before calling the
791 -- Create_Ge_Party_Notif procedure.
792 --
793 -- Called By
794 --
795 -- NOTES
796 -- SVEERAVE 11/11/02 Changed p_partner_party_id to p_partner_id
797 -- End of Comments
798
799 /*============================================================================*/
800 PROCEDURE Set_Pgp_Notif(
801 p_notif_id IN NUMBER,
802 p_object_version IN NUMBER,
803 p_partner_id IN NUMBER,
804 p_user_id IN NUMBER,
805 p_arc_notif_for_entity_code IN VARCHAR2,
806 p_notif_for_entity_id IN NUMBER,
807 p_notif_type_code IN VARCHAR2,
808 x_return_status OUT NOCOPY VARCHAR2 ,
809 x_pgp_notif_rec OUT NOCOPY PV_GE_PARTY_NOTIF_PVT.pgp_notif_rec_type )
810 IS
811 BEGIN
812 -- Initialize API return status to success
813 x_return_status := FND_API.G_RET_STS_SUCCESS;
814
815 -- Initialize OUT parameter with the supplied parameter values
816 x_pgp_notif_rec.notification_id := p_notif_id;
817 x_pgp_notif_rec.object_version_number := p_object_version;
818 x_pgp_notif_rec.partner_id := p_partner_id ;
819 x_pgp_notif_rec.recipient_user_id := p_user_id;
820 x_pgp_notif_rec.arc_notif_for_entity_code := p_arc_notif_for_entity_code;
821 x_pgp_notif_rec.notif_for_entity_id := p_notif_for_entity_id;
822 x_pgp_notif_rec.notif_type_code := p_notif_type_code;
823
824 -- Debug Message
825 IF (PV_DEBUG_HIGH_ON) THEN
826 PVX_UTILITY_PVT.debug_message('Set_Pgp_Notif');
827 END IF;
828
829 EXCEPTION
830 WHEN OTHERS THEN
831 x_return_status := FND_API.G_RET_STS_ERROR;
832 END Set_Pgp_Notif;
833
834 /*============================================================================
835 -- Start of Comments
836 -- PROCEDURE
837 -- get_enrl_requests_details
838 --
839 -- PURPOSE
840 -- This procedure will return enrollment request and membership details
841 -- from PV_PG_ENRL_REQUESTS table and PV_PG_MEMBERSHIPS_TABLE for a given enrl_requests_id
842 -- Called By
843 -- NOTES
844 -- End of Comments
845 ============================================================================*/
846 PROCEDURE get_enrl_memb_details(
847 p_enrl_request_id IN NUMBER,
848 x_req_submission_date OUT NOCOPY DATE,
849 x_partner_program_id OUT NOCOPY NUMBER,
850 x_partner_program OUT NOCOPY VARCHAR2,
851 x_enrollment_duration OUT NOCOPY VARCHAR2,
852 x_enrollment_type OUT NOCOPY VARCHAR2,
853 x_req_resource_id OUT NOCOPY NUMBER,
854 x_prtnr_vndr_relship_id OUT NOCOPY NUMBER,
855 x_start_date OUT NOCOPY DATE,
856 x_end_date OUT NOCOPY DATE,
857 x_membership_id OUT NOCOPY NUMBER,
858 x_return_status OUT NOCOPY VARCHAR2
859 )
860 IS
861 CURSOR c_pg_enrl_requests (cv_enrl_request_id IN NUMBER) IS
862 SELECT enrl_req.creation_date
863 , prog.program_id
864 , prog.program_name
865 , prog.membership_valid_period ||' ' || lookup1.meaning
866 , lookup2.meaning
867 , enrl_req.Requestor_resource_id
868 , enrl_req.partner_id
869 , memb.start_date
870 , memb.ORIGINAL_END_DATE
871 , memb.membership_id
872 FROM pv_pg_enrl_requests enrl_req
873 , pv_partner_program_vl prog
874 , fnd_lookups lookup1
875 , fnd_lookups lookup2
876 , pv_pg_memberships memb
877 WHERE enrl_req.enrl_request_id = cv_enrl_request_id
878 AND enrl_req.program_id = prog.program_id
879 AND lookup1.lookup_type='PV_PROGRAM_PMNT_UNIT'
880 AND lookup1.lookup_code = prog.membership_period_unit
881 AND lookup2.lookup_type='PV_ENROLLMENT_REQUEST_TYPE'
882 AND lookup2.lookup_code = enrl_req.enrollment_type_code
883 AND enrl_req.enrl_request_id=memb.enrl_request_id(+);
884
885
886 BEGIN
887 /* Initialize API return status to success */
888 x_return_status := FND_API.G_RET_STS_SUCCESS;
889
890 OPEN c_pg_enrl_requests( p_enrl_request_id );
891 FETCH c_pg_enrl_requests
892 INTO x_req_submission_date,
893 x_partner_program_id,
894 x_partner_program,
895 x_enrollment_duration,
896 x_enrollment_type,
897 x_req_resource_id,
898 x_prtnr_vndr_relship_id,
899 x_start_date,
900 x_end_date,
901 x_membership_id;
902
903 IF ( c_pg_enrl_requests%NOTFOUND) THEN
904 x_return_status := FND_API.G_RET_STS_ERROR;
905 END IF;
906
907 CLOSE c_pg_enrl_requests;
908
909 EXCEPTION
910 WHEN OTHERS THEN
911 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
912 END get_enrl_memb_details;
913
914 /*============================================================================
915
916
917 /*============================================================================
918 -- Start of comments
919 -- API name : send_thnkyou_notif
920 -- Type : Private.
921 -- Function : This API compiles and sends the Thank you notification to all
922 -- partner users, once the partner user successfully enrolled to
923 -- a partner program.
924 -- Pre-reqs : None.
925 -- Parameters :
926 -- IN : p_api_version IN NUMBER Required
927 -- p_init_msg_list IN VARCHAR2 Optional
928 -- Default = FND_API.G_FALSE
929 -- p_commit IN VARCHAR2 Optional
930 -- Default = FND_API.G_FALSE
931 -- p_validation_level IN NUMBER Optional
932 -- Default = FND_API.G_VALID_LEVEL_FULL
933 -- p_enrl_request_id IN NUMBER Required
934 --
935 -- OUT : x_return_status OUT VARCHAR2(1)
936 -- x_msg_count OUT NUMBER
937 -- x_msg_data OUT VARCHAR2(2000)
938 -- .
939 -- Version : Current version 1.0
940 -- Initial version 1.0
941 --
942 -- Notes : Note text
943 --
944 -- End of comments
945 ============================================================================*/
946 PROCEDURE send_thnkyou_notif (
947 p_api_version IN NUMBER ,
948 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
949 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
950 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL ,
951 x_return_status OUT NOCOPY VARCHAR2 ,
952 x_msg_count OUT NOCOPY NUMBER ,
953 x_msg_data OUT NOCOPY VARCHAR2 ,
954 p_enrl_request_id IN NUMBER
955 )
956 IS
957
958 /* Declaration of local variables. */
959 l_api_name CONSTANT VARCHAR2(30) := 'send_thnkyou_notif';
960 l_api_version CONSTANT NUMBER := 1.0;
961 l_return_status VARCHAR2(1);
962
963 l_enrl_request_id NUMBER;
964 l_req_resource_id NUMBER ;
965 l_req_submission_date VARCHAR2(240);
966 l_partner_program_id NUMBER;
967 l_partner_program VARCHAR2(240);
968 l_enrollment_duration VARCHAR2(240);
969 l_enrollment_type VARCHAR2(240);
970 l_prtnr_vndr_relship_id NUMBER;
971 l_user_id NUMBER;
972 l_notif_user_id NUMBER;
973 l_source_name VARCHAR2(360);
974 l_requestor_name VARCHAR2(360);
975 l_user_name VARCHAR2(100);
976 l_vendor_party_id NUMBER;
977 l_vendor_name VARCHAR2(360);
978 l_partner_party_id NUMBER;
979 l_partner_comp_name VARCHAR2(360);
980 l_pgp_notif_rec PV_GE_PARTY_NOTIF_PVT.pgp_notif_rec_type ;
981 l_arc_notif_for_entity_code VARCHAR2(30) := 'ENRQ';
982 l_notif_type_code VARCHAR2(30) := 'PG_THANKYOU';
983
984 /* Declaration of local variables for all the message attributes */
985 l_thankyou_subject VARCHAR2(240);
986 l_enrl_alert VARCHAR2(240);
987 l_thankyou_greetings VARCHAR2(240);
988 l_enrl_processing VARCHAR2(240);
989 l_alert_thanks VARCHAR2(240);
990 l_alert_closing VARCHAR2(240);
991 l_enrollment_team VARCHAR2(240);
992
993
994 l_item_type VARCHAR2(8) := 'PVXNUTIL';
995 l_message_name VARCHAR2(20):= 'ALERT_MESSGAE';
996 l_message_hdr VARCHAR2(2000):= NULL;
997 l_message_body VARCHAR2(4000):= NULL;
998 l_message_footer VARCHAR2(2000):= NULL;
999 l_role_name VARCHAR2(100);
1000 l_display_role_name VARCHAR2(240);
1001 l_notif_id NUMBER;
1002 l_user_count NUMBER;
1003 l_user_resource_id NUMBER ;
1004 x_user_notify_rec_tbl user_notify_rec_tbl_type;
1005 x_party_notification_id NUMBER;
1006 -- l_newline_msg VARCHAR2(1) := FND_GLOBAL.Newline;
1007 -- l_newline VARCHAR2(10) := l_newline_msg || '<BR>';
1008 -- l_newline VARCHAR2(5) := '<BR>';
1009 l_newline VARCHAR2(5) := wf_core.newline;
1010
1011 l_notif_rule_active VARCHAR2(1):='N';
1012 l_log_params_tbl PVX_UTILITY_PVT.log_params_tbl_type;
1013
1014 BEGIN
1015 /* Standard Start of API savepoint */
1016 SAVEPOINT send_thnkyou_notif_PVT;
1017
1018 /* Standard call to check for call compatibility. */
1019 IF NOT FND_API.Compatible_API_Call (l_api_version ,
1020 p_api_version ,
1021 l_api_name ,
1022 G_PKG_NAME
1023 )
1024 THEN
1025 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1026 END IF;
1027
1028 /* Initialize message list if p_init_msg_list is set to TRUE. */
1029 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1030 FND_MSG_PUB.initialize;
1031 END IF;
1032
1033 /* Initialize API return status to success */
1034 x_return_status := FND_API.G_RET_STS_SUCCESS;
1035
1036 /* Validate the Enrollment Request Id */
1037 IF ( P_validation_level >= FND_API.G_VALID_LEVEL_FULL)
1038 THEN
1039 /* Debug message */
1040 IF (PV_DEBUG_HIGH_ON) THEN
1041 PVX_UTILITY_PVT.debug_message('Validate_Enrl_Requests_Id');
1042 END IF;
1043 /* Invoke validation procedures */
1044 Validate_Enrl_Requests
1045 ( p_enrl_request_id
1046 , 'ENRL_REQUEST_ID'
1047 , l_return_status
1048 );
1049 /* If any errors happen abort API. */
1050 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1051 RAISE FND_API.G_EXC_ERROR;
1052 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1053 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1054 END IF;
1055 END IF;
1056
1057 /* Get the enrollment request details */
1058 get_enrl_requests_details(
1059 p_enrl_request_id => p_enrl_request_id ,
1060 x_req_submission_date => l_req_submission_date,
1061 x_partner_program_id => l_partner_program_id,
1062 x_partner_program => l_partner_program,
1063 x_enrollment_duration => l_enrollment_duration,
1064 x_enrollment_type => l_enrollment_type,
1065 x_req_resource_id => l_req_resource_id,
1066 x_prtnr_vndr_relship_id => l_prtnr_vndr_relship_id,
1067 x_return_status => x_return_status);
1068
1069 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
1070 THEN
1071 FND_MESSAGE.SET_NAME('PV', 'PV_ENRL_REQ_NOT_EXIST');
1072 FND_MESSAGE.SET_TOKEN('ENRL_REQUEST_ID',p_enrl_request_id);
1073 FND_MSG_PUB.Add;
1074 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1075 RAISE FND_API.G_EXC_ERROR;
1076 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1077 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1078 END IF;
1079 END IF;
1080
1081 /* If Notification Rule is Active for the given PROGRAM_ID, then only
1082 proceed, else do not send the notification. */
1083
1084 l_notif_rule_active := check_Notif_Rule_Active(
1085 p_program_id => l_partner_program_id,
1086 p_notif_type => 'PG_THANKYOU' ) ;
1087
1088 IF ( l_notif_rule_active = 'Y' )
1089 THEN
1090 /* Get the Partner and Vendor details */
1091 get_prtnr_vendor_details(
1092 p_enrl_request_id => p_enrl_request_id ,
1093 x_vendor_party_id => l_vendor_party_id,
1094 x_vendor_name => l_vendor_name,
1095 x_partner_party_id => l_partner_party_id,
1096 x_partner_comp_name => l_partner_comp_name,
1097 x_return_status => x_return_status
1098 );
1099
1100 /* Check for Procedure's x_return_status */
1101 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
1102 THEN
1103 FND_MESSAGE.SET_NAME('PV','PV_PRTNR_VNDR_NOT_EXIST');
1104 FND_MESSAGE.SET_TOKEN('ENRL_REQUEST_ID',p_enrl_request_id);
1105 FND_MSG_PUB.Add;
1106 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1107 RAISE FND_API.G_EXC_ERROR;
1108 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1109 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1110 END IF;
1111 END IF;
1112 /* Get the requestor details */
1113 get_requestor_details(
1114 p_req_resource_id => l_req_resource_id,
1115 x_user_id => l_user_id,
1116 x_source_name => l_source_name,
1117 x_user_name => l_user_name,
1118 x_return_status => x_return_status
1119 );
1120
1121 /* Check for Procedure's x_return_status */
1122 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
1123 THEN
1124 FND_MESSAGE.SET_NAME('PV','PV_REQUESTOR_NOT_EXIST');
1125 FND_MESSAGE.SET_TOKEN('REQ_RESOURCE_ID',l_req_resource_id);
1126 FND_MSG_PUB.Add;
1127 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1128 RAISE FND_API.G_EXC_ERROR;
1129 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1130 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1131 END IF;
1132 END IF;
1133
1134 /* Get the user list */
1135 get_users_list(
1136 p_partner_id => l_prtnr_vndr_relship_id,
1137 x_user_notify_rec_tbl => x_user_notify_rec_tbl ,
1138 x_user_count => l_user_count,
1139 x_return_status => x_return_status ) ;
1140
1141 /* Check for Procedure's x_return_status */
1142 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1143 FND_MESSAGE.SET_NAME('PV','PV_NO_PRIMARY_USER_EXIST');
1144 FND_MESSAGE.SET_TOKEN('PARTNER_ID',l_prtnr_vndr_relship_id);
1145 FND_MSG_PUB.Add;
1146 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1147 RAISE FND_API.G_EXC_ERROR;
1148 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1149 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1150 END IF;
1151 END IF;
1152
1153 /* Send the notification to all the users from that partner Organization
1154 for the given partner vendor relationship id. */
1155
1156 FOR i IN 1 .. l_user_count LOOP
1157 l_user_resource_id := x_user_notify_rec_tbl(i).user_resource_id;
1158 l_notif_user_id := x_user_notify_rec_tbl(i).user_id;
1159
1160 /* Get the role name for the given 'p_requestor_id'.
1161 IF p_send_to_role_name IS NULL THEN */
1162 get_resource_role(
1163 p_resource_id => l_user_resource_id,
1164 x_role_name => l_role_name,
1165 x_role_display_name => l_display_role_name,
1166 x_return_status => x_return_status
1167 );
1168
1169 /* Check for Procedure's x_return_status */
1170 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1171 FND_MESSAGE.SET_NAME('PV','PV_RES_ROLE_NOT_EXIST');
1172 FND_MESSAGE.SET_TOKEN('RESOURCE_ID',l_user_resource_id);
1173 FND_MSG_PUB.Add;
1174 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1175 RAISE FND_API.G_EXC_ERROR;
1176 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1177 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1178 END IF;
1179 END IF;
1180
1181 /* Use the 'WF_Notification.send' procedure to send the notification */
1182
1183 l_notif_id := WF_Notification.send ( role => l_role_name
1184 , msg_type => 'PVXNUTIL'
1185 -- , msg_name => 'DOC_MESSAGE');
1186 , msg_name => 'ALERT_MESSAGE');
1187 /* Set all the entity attributes by replacing the supplyied parameters.*/
1188 -- WF_Notification.SetAttrText(l_notif_id,'DOCUMENT_ID', 'PVXNUTIL:'||l_notif_id);
1189 /* Set the Vendor Name */
1190 /*
1191 fnd_message.set_name('PV', 'PV_VENDOR_NM');
1192 fnd_message.set_token('PV_VENDOR_NAME', l_vendor_name);
1193 WF_Notification.SetAttrText (l_notif_id, 'PV_VENDOR_NM', fnd_message.get);
1194 */
1195
1196 -- Set the subject line
1197 fnd_message.set_name('PV', 'PV_NTF_THANKYOU_SUBJECT');
1198 fnd_message.set_token('PV_PARTNER_PROGRAM', l_partner_program);
1199 WF_Notification.SetAttrText (l_notif_id, 'SUBJECT', fnd_message.get);
1200
1201
1202 -- Set the Message Header
1203 fnd_message.set_name('PV', 'PV_NTF_ENRL_ALERT');
1204 fnd_message.set_token('PV_VENDOR_NM', l_vendor_name);
1205 l_message_hdr := fnd_message.get || l_newline;
1206
1207
1208 l_message_body := fnd_message.get_string('PV', 'PV_NTF_THANKYOU_GREETINGS') || l_newline;
1209 l_message_body := l_message_body || l_newline;
1210 l_message_body := l_message_body || fnd_message.get_string('PV', 'PV_NTF_ENRL_PROCESSING')|| l_newline ;
1211 l_message_body := l_message_body || l_newline;
1212
1213 -- Set the Partner Company Name
1214 fnd_message.set_name('PV', 'PV_NTF_PARTNER_NM');
1215 fnd_message.set_token('PV_PARTNER_NM', l_partner_comp_name);
1216 l_message_body := l_message_body ||fnd_message.get|| l_newline ;
1217
1218 -- Set the requestor name
1219 fnd_message.set_name('PV', 'PV_NTF_REQUESTOR_NM');
1220 fnd_message.set_token('PV_REQUESTOR_NM', rtrim(l_source_name));
1221 l_message_body := l_message_body || fnd_message.get || l_newline ;
1222
1223 -- Set the request submission date
1224 fnd_message.set_name('PV', 'PV_NTF_REQ_SUBMIT_DT');
1225 fnd_message.set_token('PV_REQ_SUBMIT_DT', l_req_submission_date);
1226 l_message_body := l_message_body || fnd_message.get|| l_newline;
1227
1228 -- Set the partner program name
1229 fnd_message.set_name('PV', 'PV_NTF_PARTNER_PRGM');
1230 fnd_message.set_token('PV_PARTNER_PRGM', l_partner_program);
1231 l_message_body := l_message_body || fnd_message.get || l_newline;
1232
1233 -- Set the enrollment duration
1234 fnd_message.set_name('PV', 'PV_NTF_ENRL_DURATION');
1235 fnd_message.set_token('PV_ENRL_DURATION', l_enrollment_duration);
1236 l_message_body := l_message_body || fnd_message.get || l_newline;
1237
1238 -- Set the enrollment type
1239 fnd_message.set_name('PV', 'PV_NTF_ENRL_TYPE');
1240 fnd_message.set_token('PV_ENRL_TYPE', l_enrollment_type);
1241 l_message_body := l_message_body || fnd_message.get || l_newline;
1242
1243 -- Get the values for all message attributes from the message list for Message Footer
1244 l_message_footer := l_newline || fnd_message.get_string('PV', 'PV_NTF_ALERT_THANKS') || l_newline;
1245 l_message_footer := l_message_footer || fnd_message.get_string('PV', 'PV_NTF_ALERT_CLOSING')|| l_newline;
1246 l_message_footer := l_message_footer || fnd_message.get_string('PV', 'PV_NTF_ENROLLMENT_TEAM') || l_newline;
1247
1248 WF_Notification.SetAttrText(l_notif_id,'MESSAGE_HEADER', l_message_hdr);
1249 WF_Notification.SetAttrText(l_notif_id,'MESSAGE_BODY', l_message_body);
1250 WF_Notification.SetAttrText(l_notif_id,'MESSAGE_FOOTER', l_message_footer);
1251
1252 WF_NOTIFICATION.Denormalize_Notification(l_notif_id);
1253
1254 /* Set the record for Create_Ge_Party_Notif API */
1255 Set_Pgp_Notif(
1256 p_notif_id => l_notif_id,
1257 p_object_version => 1,
1258 p_partner_id => l_prtnr_vndr_relship_id,
1259 p_user_id => l_notif_user_id,
1260 p_arc_notif_for_entity_code => l_arc_notif_for_entity_code,
1261 p_notif_for_entity_id=> p_enrl_request_id,
1262 p_notif_type_code => l_notif_type_code,
1263 x_return_status => x_return_status ,
1264 x_pgp_notif_rec => l_pgp_notif_rec );
1265
1266 /* Check for Procedure's x_return_status */
1267 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1268 FND_MESSAGE.SET_NAME('PV','PV_SET_NOTIF_REC');
1269 FND_MESSAGE.SET_TOKEN('NOTIFICATION_ID',l_notif_id);
1270 FND_MSG_PUB.Add;
1271 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1272 RAISE FND_API.G_EXC_ERROR;
1273 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1274 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1275 END IF;
1276 END IF;
1277
1278 /* Call the Create_Ge_Party_Notif to insert a record in PV_GE_PARTY_NOTIFICATION */
1279 PV_GE_PARTY_NOTIF_PVT.Create_Ge_Party_Notif (
1280 p_api_version_number => 1.0,
1281 p_init_msg_list => FND_API.G_FALSE ,
1282 p_commit => FND_API.G_FALSE ,
1283 p_validation_level => FND_API.G_VALID_LEVEL_FULL ,
1284 x_return_status => x_return_status ,
1285 x_msg_count => x_msg_count,
1286 x_msg_data => x_msg_data ,
1287 p_pgp_notif_rec => l_pgp_notif_rec,
1288 x_party_notification_id => x_party_notification_id );
1289
1290 /* Check for Procedure's x_return_status */
1291 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1292 FND_MESSAGE.SET_NAME('PV','PV_GE_PARTY_NOTIF_REC');
1293 FND_MESSAGE.SET_TOKEN('NOTIFICATION_ID',l_notif_id);
1294 FND_MSG_PUB.Add;
1295 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1296 RAISE FND_API.G_EXC_ERROR;
1297 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1298 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1299 END IF;
1300 END IF;
1301
1302 END LOOP;
1303
1304 /* call transaction history log to record this log. */
1305 /* Set the log params for History log. */
1306 l_log_params_tbl(1).param_name := 'NOTIFICATION_TYPE';
1307 l_log_params_tbl(1).param_value := get_Notification_Name(l_notif_type_code);
1308 l_log_params_tbl(2).param_name := 'ITEM_NAME';
1309 l_log_params_tbl(2).param_value := 'ENRL_REQUEST_ID';
1310 l_log_params_tbl(3).param_name := 'ITEM_ID';
1311 l_log_params_tbl(3).param_value := p_enrl_request_id;
1312
1313 /* call transaction history log to record this log. */
1314 PVX_Utility_PVT.create_history_log(
1315 p_arc_history_for_entity_code => 'GENERAL', --'ENRQ',
1316 p_history_for_entity_id => l_prtnr_vndr_relship_id, --p_enrl_request_id,
1317 p_history_category_code => 'ENROLLMENT',
1318 p_message_code => 'PV_NOTIF_HISTORY_MSG',
1319 p_partner_id => l_prtnr_vndr_relship_id,
1320 p_log_params_tbl => l_log_params_tbl,
1321 x_return_status => x_return_status,
1322 x_msg_count => x_msg_count,
1323 x_msg_data => x_msg_data );
1324
1325 /* Check for Procedure's x_return_status */
1326 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1327 FND_MESSAGE.SET_NAME('PV','PV_CR_HISTORY_LOG');
1328 FND_MESSAGE.SET_TOKEN('ID',p_enrl_request_id);
1329 FND_MSG_PUB.Add;
1330 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1331 RAISE FND_API.G_EXC_ERROR;
1332 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1333 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1334 END IF;
1335 END IF;
1336
1337 /* Standard check of p_commit. */
1338 IF FND_API.To_Boolean( p_commit ) THEN
1339 COMMIT WORK;
1340 END IF;
1341
1342 /* Standard call to get message count and if count is 1, get message info.*/
1343 FND_MSG_PUB.Count_And_Get
1344 ( p_count => x_msg_count ,
1345 p_data => x_msg_data
1346 );
1347 END IF;
1348
1349 EXCEPTION
1350 WHEN FND_API.G_EXC_ERROR THEN
1351 ROLLBACK TO send_thnkyou_notif_PVT;
1352 x_return_status := FND_API.G_RET_STS_ERROR ;
1353 FND_MSG_PUB.Count_And_Get
1354 ( p_count => x_msg_count ,
1355 p_data => x_msg_data
1356 );
1357
1358 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1359 ROLLBACK TO send_thnkyou_notif_PVT;
1360 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1361 FND_MSG_PUB.Count_And_Get
1362 ( p_count => x_msg_count ,
1363 p_data => x_msg_data
1364 );
1365
1366 WHEN OTHERS THEN
1367 ROLLBACK TO send_thnkyou_notif_PVT;
1368 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1369 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1370 THEN
1371 FND_MSG_PUB.Add_Exc_Msg
1372 ( G_FILE_NAME
1373 , G_PKG_NAME
1374 );
1375 END IF;
1376 FND_MSG_PUB.Count_And_Get
1377 ( p_count => x_msg_count
1378 , p_data => x_msg_data
1379 );
1380 END send_thnkyou_notif;
1381
1382 /*============================================================================
1383 -- Start of comments
1384 -- API name : send_welcome_notif
1385 -- Type : Private.
1386 -- Function : This API compiles and sends the Welcome Notification to a
1387 -- partner, once the partner user's enrollment request is
1388 -- approved by the approver.
1389 -- Pre-reqs : None.
1390 -- Parameters :
1391 -- IN : p_api_version IN NUMBER Required
1392 -- p_init_msg_list IN VARCHAR2 Optional
1393 -- Default = FND_API.G_FALSE
1394 -- p_commit IN VARCHAR2 Optional
1395 -- Default = FND_API.G_FALSE
1396 -- p_validation_level IN NUMBER Optional
1397 -- Default = FND_API.G_VALID_LEVEL_FULL
1398 -- p_membership_id IN NUMBER Required
1399 --
1400 -- OUT : x_return_status OUT VARCHAR2(1)
1401 -- x_msg_count OUT NUMBER
1402 -- x_msg_data OUT VARCHAR2(2000)
1403 --
1404 -- Version : Current version 1.0
1405 -- Initial version 1.0
1406 --
1407 -- Notes : Note text
1408 --
1409 -- End of comments
1410 ============================================================================*/
1411 PROCEDURE send_welcome_notif (
1412 p_api_version IN NUMBER ,
1413 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
1414 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
1415 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL ,
1416 x_return_status OUT NOCOPY VARCHAR2 ,
1417 x_msg_count OUT NOCOPY NUMBER ,
1418 x_msg_data OUT NOCOPY VARCHAR2 ,
1419 p_membership_id IN NUMBER
1420 )
1421 IS
1422
1423 /* Declaration of local variables. */
1424 l_api_name CONSTANT VARCHAR2(30) := 'send_welcome_notif';
1425 l_api_version CONSTANT NUMBER := 1.0;
1426 l_return_status VARCHAR2(1);
1427
1428 l_membership_id NUMBER;
1429 l_enrl_request_id NUMBER;
1430 l_req_resource_id NUMBER;
1431 l_req_submission_date VARCHAR2(240);
1432 l_enrollment_start_date VARCHAR2(240);
1433 l_enrollment_end_date VARCHAR2(240);
1434 l_partner_program_id NUMBER;
1435 l_partner_program VARCHAR2(240);
1436 l_enrollment_type VARCHAR2(240);
1437 l_prtnr_vndr_relship_id NUMBER;
1438 l_user_id NUMBER;
1439 l_notif_user_id NUMBER;
1440 l_source_name VARCHAR2(360);
1441 l_requestor_name VARCHAR2(360);
1442 l_user_name VARCHAR2(100);
1443 l_vendor_name VARCHAR2(360);
1444 l_vendor_party_id NUMBER;
1445 l_partner_party_id NUMBER;
1446 l_partner_comp_name VARCHAR2(360);
1447 l_pgp_notif_rec PV_GE_PARTY_NOTIF_PVT.pgp_notif_rec_type ;
1448 l_arc_notif_for_entity_code VARCHAR2(30) := 'ENRQ';
1449 l_notif_type_code VARCHAR2(30) := 'PG_WELCOME';
1450 l_message_hdr VARCHAR2(2000):= NULL;
1451 l_message_body VARCHAR2(4000):= NULL;
1452 l_message_footer VARCHAR2(2000):= NULL;
1453
1454 /* Declaration of local variables for all the message attributes */
1455 l_alert_thanks VARCHAR2(240);
1456 l_alert_closing VARCHAR2(240);
1457 l_enrollment_team VARCHAR2(240);
1458
1459 l_item_type VARCHAR2(8) := 'PVXNUTIL';
1460 -- l_message_name VARCHAR2(20):= 'ALERT_MESSAGE';
1461 l_message_name VARCHAR2(20):= 'DOC_MESSAGE';
1462
1463 l_role_name VARCHAR2(100);
1464 l_display_role_name VARCHAR2(240);
1465 l_notif_id NUMBER;
1466 l_user_count NUMBER;
1467 l_user_resource_id NUMBER ;
1468 x_user_notify_rec_tbl user_notify_rec_tbl_type;
1469 x_party_notification_id NUMBER;
1470 -- l_newline_msg VARCHAR2(1) := FND_GLOBAL.Newline;
1471 -- l_newline VARCHAR2(10) := l_newline_msg || '<BR>';
1472 l_newline VARCHAR2(5) := '<BR>'; -- not using wf_core as we are using pl/sql document
1473 -- l_newline VARCHAR2(5) := wf_core.newline;
1474
1475 l_notif_rule_active VARCHAR2(1):='N';
1476 l_log_params_tbl PVX_UTILITY_PVT.log_params_tbl_type;
1477 l_prtner_portal_url VARCHAR2(4000);
1478 l_login_url VARCHAR2(4000);
1479
1480 BEGIN
1481 /* Standard Start of API savepoint */
1482 SAVEPOINT send_welcome_notif_PVT;
1483
1484 /* Standard call to check for call compatibility. */
1485 IF NOT FND_API.Compatible_API_Call (l_api_version ,
1486 p_api_version ,
1487 l_api_name ,
1488 G_PKG_NAME
1489 )
1490 THEN
1491 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1492 END IF;
1493
1494 /* Initialize message list if p_init_msg_list is set to TRUE. */
1495 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1496 FND_MSG_PUB.initialize;
1497 END IF;
1498
1499 /* Initialize API return status to success */
1500 x_return_status := FND_API.G_RET_STS_SUCCESS;
1501
1502 /* Validate the Enrollment Request Id */
1503 IF ( P_validation_level >= FND_API.G_VALID_LEVEL_FULL)
1504 THEN
1505 /* Debug message */
1506 IF (PV_DEBUG_HIGH_ON) THEN
1507
1508 PVX_UTILITY_PVT.debug_message('Validate_membership_Id');
1509 END IF;
1510
1511 /* Invoke validation procedures */
1512 Validate_Enrl_Requests
1513 ( p_membership_id ,
1514 'MEMBERSHIP_ID',
1515 l_return_status
1516 );
1517
1518 /* If any errors happen abort API. */
1519 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1520 RAISE FND_API.G_EXC_ERROR;
1521 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1522 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1523 END IF;
1524 END IF;
1525
1526 /* Get the membership details*/
1527 get_membership_details(
1528 p_membership_id => p_membership_id ,
1529 x_req_submission_date => l_req_submission_date,
1530 x_partner_program_id => l_partner_program_id,
1531 x_partner_program => l_partner_program,
1532 x_enrl_request_id => l_enrl_request_id,
1533 x_enrollment_start_date => l_enrollment_start_date,
1534 x_enrollment_end_date => l_enrollment_end_date,
1535 x_req_resource_id => l_req_resource_id,
1536 x_prtnr_vndr_relship_id => l_prtnr_vndr_relship_id,
1537 x_enrollment_type => l_enrollment_type,
1538 x_return_status => x_return_status);
1539
1540
1541 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1542 FND_MESSAGE.SET_NAME('PV','PV_MBRSHIP_NOT_EXIST');
1543 FND_MESSAGE.SET_TOKEN('MEMBERSHIP_ID',p_membership_id);
1544 FND_MSG_PUB.Add;
1545 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1546 RAISE FND_API.G_EXC_ERROR;
1547 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1548 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1549 END IF;
1550 END IF;
1551
1552 /* If Notification Rule is Active for the given PROGRAM_ID, then only
1553 proceed, else do not send the notification. */
1554
1555 l_notif_rule_active := check_Notif_Rule_Active(
1556 p_program_id => l_partner_program_id,
1557 p_notif_type => 'PG_WELCOME' ) ;
1558
1559 IF ( l_notif_rule_active = 'Y' )
1560 THEN
1561 /* Get the Partner and Vendor details */
1562 get_prtnr_vendor_details(
1563 p_enrl_request_id => l_enrl_request_id ,
1564 x_vendor_party_id => l_vendor_party_id,
1565 x_vendor_name => l_vendor_name,
1566 x_partner_party_id => l_partner_party_id,
1567 x_partner_comp_name => l_partner_comp_name,
1568 x_return_status => x_return_status);
1569
1570
1571 /* Check for Procedure's x_return_status */
1572 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1573 FND_MESSAGE.SET_NAME('PV','PV_PRTNR_VNDR_NOT_EXIST');
1574 FND_MESSAGE.SET_TOKEN('ENRL_REQUEST_ID',l_enrl_request_id);
1575 FND_MSG_PUB.Add;
1576 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1577 RAISE FND_API.G_EXC_ERROR;
1578 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1579 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1580 END IF;
1581 END IF;
1582
1583 /* Get the requestor details */
1584 get_requestor_details(
1585 p_req_resource_id => l_req_resource_id,
1586 x_user_id => l_user_id,
1587 x_source_name => l_source_name,
1588 x_user_name => l_user_name,
1589 x_return_status => x_return_status);
1590
1591 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1592 FND_MESSAGE.SET_NAME('PV','PV_REQUESTOR_NOT_EXIST');
1593 FND_MESSAGE.SET_TOKEN('REQ_RESOURCE_ID',l_req_resource_id);
1594 FND_MSG_PUB.Add;
1595 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1596 RAISE FND_API.G_EXC_ERROR;
1597 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1598 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1599 END IF;
1600 END IF;
1601
1602 /* Get the user list */
1603 get_users_list(
1604 p_partner_id => l_prtnr_vndr_relship_id,
1605 x_user_notify_rec_tbl => x_user_notify_rec_tbl ,
1606 x_user_count => l_user_count,
1607 x_return_status => x_return_status ) ;
1608
1609 /* Check for Procedure's x_return_status */
1610 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1611 FND_MESSAGE.SET_NAME('PV','PV_NO_PRIMARY_USER_EXIST');
1612 FND_MESSAGE.SET_TOKEN('PARTNER_ID',l_prtnr_vndr_relship_id);
1613 FND_MSG_PUB.Add;
1614 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1615 RAISE FND_API.G_EXC_ERROR;
1616 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1617 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1618 END IF;
1619 END IF;
1620
1621 /* Send the notification to all the users from that partner Organization
1622 for the given partner vendor relationship id. */
1623
1624 FOR i IN 1 .. l_user_count
1625 LOOP
1626 l_user_resource_id := x_user_notify_rec_tbl(i).user_resource_id;
1627 l_notif_user_id := x_user_notify_rec_tbl(i).user_id;
1628
1629 /* Get the role name for the given 'p_requestor_id'. */
1630 /*IF p_send_to_role_name IS NULL THEN */
1631 get_resource_role(
1632 p_resource_id => l_user_resource_id,
1633 x_role_name => l_role_name,
1634 x_role_display_name => l_display_role_name,
1635 x_return_status => x_return_status
1636 );
1637
1638 /* Check for Procedure's x_return_status */
1639 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
1640 THEN
1641 FND_MESSAGE.SET_NAME('PV','PV_RES_ROLE_NOT_EXIST');
1642 FND_MESSAGE.SET_TOKEN('RESOURCE_ID',l_user_resource_id);
1643 FND_MSG_PUB.Add;
1644 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1645 RAISE FND_API.G_EXC_ERROR;
1646 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1647 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1648 END IF;
1649 END IF;
1650
1651 /* Use the 'WF_Notification.send' procedure to send the notification */
1652
1653 l_notif_id := WF_Notification.send (
1654 role => l_role_name
1655 , msg_type => l_item_type
1656 , msg_name => l_message_name );
1657
1658 WF_Notification.SetAttrText(l_notif_id,'NOTIF_DOC_ID', 'PVXNUTIL:'||l_notif_id); -- passing the doc id
1659
1660 /* Set the subject line */
1661 fnd_message.set_name('PV', 'PV_NTF_WELCOME_SUBJECT');
1662 fnd_message.set_token('PV_PARTNER_PROGRAM', l_partner_program);
1663 WF_Notification.SetAttrText (l_notif_id, 'SUBJECT', fnd_message.get);
1664
1665 /* Set the Message Header */
1666 fnd_message.set_name('PV', 'PV_NTF_ENRL_ALERT');
1667 fnd_message.set_token('PV_VENDOR_NM', l_vendor_name);
1668 l_message_hdr := fnd_message.get || l_newline;
1669
1670 /* Set all the entity attributes by replacing the supplied parameters. */
1671
1672 /* Set the Vendor Name
1673 fnd_message.set_name('PV', 'PV_VENDOR_NM');
1674 fnd_message.set_token('PV_VENDOR_NAME', l_vendor_name);
1675 WF_Notification.SetAttrText (l_notif_id, 'PV_VENDOR_NM', fnd_message.get);
1676 */
1677
1678 /* Set the welcome greeting Line */
1679 fnd_message.set_name('PV', 'PV_NTF_WELCOME_GREETINGS');
1680 fnd_message.set_token('PV_PARTNER_PROGRAM', l_partner_program);
1681 l_message_body := fnd_message.get || l_newline;
1682 l_message_body := l_message_body || l_newline;
1683
1684 /* Set the Partner Company Name */
1685 fnd_message.set_name('PV', 'PV_NTF_PARTNER_NM');
1686 fnd_message.set_token('PV_PARTNER_NM', l_partner_comp_name);
1687 l_message_body := l_message_body || fnd_message.get || l_newline;
1688
1689 /* Set the Requestor Name */
1690 fnd_message.set_name('PV', 'PV_NTF_REQUESTOR_NM');
1691 fnd_message.set_token('PV_REQUESTOR_NM', l_source_name);
1692 l_message_body := l_message_body || fnd_message.get || l_newline;
1693
1694 /* Set the Request Submission Date */
1695 fnd_message.set_name('PV', 'PV_NTF_REQ_SUBMIT_DT');
1696 fnd_message.set_token('PV_REQ_SUBMIT_DT', l_req_submission_date);
1697 l_message_body := l_message_body || fnd_message.get || l_newline;
1698
1699 /* Set the Partner Program Name */
1700 fnd_message.set_name('PV', 'PV_NTF_PARTNER_PRGM');
1701 fnd_message.set_token('PV_PARTNER_PRGM', l_partner_program);
1702 l_message_body := l_message_body || fnd_message.get || l_newline;
1703
1704 /* Set the Enrollment Start Date */
1705 fnd_message.set_name('PV', 'PV_NTF_ENRL_START_DT');
1706 fnd_message.set_token('PV_ENRL_START_DT', l_enrollment_start_date);
1707 l_message_body := l_message_body || fnd_message.get || l_newline;
1708
1709 /* Set the Enrollment End Date */
1710 fnd_message.set_name('PV', 'PV_NTF_ENRL_END_DT');
1711 fnd_message.set_token('PV_ENRL_END_DT', l_enrollment_end_date);
1712 l_message_body := l_message_body || fnd_message.get || l_newline;
1713
1714 /* Set the Enrollment Type */
1715 fnd_message.set_name('PV', 'PV_NTF_ENRL_TYPE');
1716 fnd_message.set_token('PV_ENRL_TYPE', l_enrollment_type);
1717 l_message_body := l_message_body || fnd_message.get || l_newline;
1718 l_message_body := l_message_body || l_newline;
1719
1720 --kvattiku Oct 31, 05 Commented out and modified code so that the URL is obtained from profile.
1721 --/* Set the Log-in portal line */
1722 --l_login_url := get_Program_Url(l_partner_program_id);
1723 l_login_url := FND_PROFILE.VALUE('PV_WORKFLOW_ISTORE_URL');
1724 l_prtner_portal_url := '<a href="'|| l_login_url || '">'|| l_partner_program || '</a>';
1725
1726 /*
1727 l_prtner_portal_url := icx_sec.createRFURL(
1728 p_function_name => 'PV_MYPARTNER_ORGZN',
1729 p_application_id => 691,
1730 p_responsibility_id => 23073,
1731 p_security_group_id => fnd_global.security_group_id );
1732 */
1733
1734 fnd_message.set_name('PV', 'PV_NTF_LOGIN_PORTAL');
1735 fnd_message.set_token('PV_LOGIN_PORTAL', l_prtner_portal_url);
1736 --fnd_message.set_token('PV_LOGIN_PORTAL', '');
1737 l_message_body := l_message_body || fnd_message.get || l_newline;
1738
1739
1740 /* Get the values for all message attributes from the message list for Message Footer */
1741 l_message_footer := l_newline || fnd_message.get_string('PV', 'PV_NTF_ALERT_THANKS') || l_newline;
1742 l_message_footer := l_message_footer || fnd_message.get_string('PV', 'PV_NTF_ALERT_CLOSING')|| l_newline;
1743 l_message_footer := l_message_footer || fnd_message.get_string('PV', 'PV_NTF_ENROLLMENT_TEAM') || l_newline;
1744
1745
1746 WF_Notification.SetAttrText(l_notif_id,'MESSAGE_HEADER', l_message_hdr);
1747 WF_Notification.SetAttrText(l_notif_id,'MESSAGE_BODY', l_message_body);
1748 WF_Notification.SetAttrText(l_notif_id,'MESSAGE_FOOTER', l_message_footer);
1749
1750 WF_NOTIFICATION.Denormalize_Notification(l_notif_id);
1751
1752 /* Set the record for Create_Ge_Party_Notif API */
1753 Set_Pgp_Notif(
1754 p_notif_id => l_notif_id,
1755 p_object_version => 1,
1756 p_partner_id => l_prtnr_vndr_relship_id,
1757 p_user_id => l_notif_user_id,
1758 p_arc_notif_for_entity_code => l_arc_notif_for_entity_code,
1759 p_notif_for_entity_id => p_membership_id,
1760 p_notif_type_code => l_notif_type_code,
1761 x_return_status => x_return_status ,
1762 x_pgp_notif_rec => l_pgp_notif_rec );
1763
1764 /* Check for Procedure's x_return_status */
1765 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1766 FND_MESSAGE.SET_NAME('PV','PV_SET_NOTIF_REC');
1767 FND_MESSAGE.SET_TOKEN('NOTIFICATION_ID',l_notif_id);
1768 FND_MSG_PUB.Add;
1769 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1770 RAISE FND_API.G_EXC_ERROR;
1771 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1772 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1773 END IF;
1774 END IF;
1775
1776 /* Call the Create_Ge_Party_Notif to insert a record in PV_GE_PARTY_NOTIFICATION */
1777
1778 PV_GE_PARTY_NOTIF_PVT.Create_Ge_Party_Notif (
1779 p_api_version_number => 1.0,
1780 p_init_msg_list => FND_API.G_FALSE ,
1781 p_commit => FND_API.G_FALSE ,
1782 p_validation_level => FND_API.G_VALID_LEVEL_FULL ,
1783 x_return_status => x_return_status ,
1784 x_msg_count => x_msg_count,
1785 x_msg_data => x_msg_data ,
1786 p_pgp_notif_rec => l_pgp_notif_rec,
1787 x_party_notification_id => x_party_notification_id );
1788
1789 /* Check for Procedure's x_return_status */
1790 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1791 FND_MESSAGE.SET_NAME('PV','PV_GE_PARTY_NOTIF_REC');
1792 FND_MESSAGE.SET_TOKEN('NOTIFICATION_ID',l_notif_id);
1793 FND_MSG_PUB.Add;
1794 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1795 RAISE FND_API.G_EXC_ERROR;
1796 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1797 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1798 END IF;
1799 END IF;
1800
1801 END LOOP;
1802
1803 /* call transaction history log to record this log. */
1804 /* Set the log params for History log. */
1805 l_log_params_tbl(1).param_name := 'NOTIFICATION_TYPE';
1806 l_log_params_tbl(1).param_value := get_Notification_Name(l_notif_type_code);
1807 l_log_params_tbl(2).param_name := 'ITEM_NAME';
1808 l_log_params_tbl(2).param_value := 'MEMBERSHIP_ID';
1809 l_log_params_tbl(3).param_name := 'ITEM_ID';
1810 l_log_params_tbl(3).param_value := p_membership_id;
1811
1812 /* call transaction history log to record this log. */
1813 PVX_Utility_PVT.create_history_log(
1814 p_arc_history_for_entity_code=> 'MBRSHIP',
1815 p_history_for_entity_id => p_membership_id,
1816 p_history_category_code => 'APPROVAL',
1817 p_message_code => 'PV_NOTIF_HISTORY_MSG',
1818 p_partner_id => l_prtnr_vndr_relship_id,
1819 p_log_params_tbl => l_log_params_tbl,
1820 x_return_status => x_return_status,
1821 x_msg_count => x_msg_count,
1822 x_msg_data => x_msg_data );
1823
1824 /* Check for Procedure's x_return_status */
1825 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1826 FND_MESSAGE.SET_NAME('PV','PV_CR_HISTORY_LOG');
1827 FND_MESSAGE.SET_TOKEN('ID',p_membership_id);
1828 FND_MSG_PUB.Add;
1829 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1830 RAISE FND_API.G_EXC_ERROR;
1831 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1832 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1833 END IF;
1834 END IF;
1835
1836 -- Standard check of p_commit.
1837 IF FND_API.To_Boolean( p_commit ) THEN
1838 COMMIT WORK;
1839 END IF;
1840
1841 -- Standard call to get message count and if count is 1, get message info.
1842 FND_MSG_PUB.Count_And_Get
1843 ( p_count => x_msg_count ,
1844 p_data => x_msg_data
1845 );
1846
1847 END IF; /* End the IF condition for check_Notif_Rule_Active */
1848
1849 EXCEPTION
1850 WHEN FND_API.G_EXC_ERROR THEN
1851 ROLLBACK TO send_welcome_notif_PVT;
1852 x_return_status := FND_API.G_RET_STS_ERROR ;
1853 FND_MSG_PUB.Count_And_Get
1854 ( p_count => x_msg_count ,
1855 p_data => x_msg_data
1856 );
1857
1858 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1859 ROLLBACK TO send_welcome_notif_PVT;
1860 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1861 FND_MSG_PUB.Count_And_Get
1862 ( p_count => x_msg_count ,
1863 p_data => x_msg_data
1864 );
1865
1866 WHEN OTHERS THEN
1867 ROLLBACK TO send_welcome_notif_PVT;
1868 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1869 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1870 THEN
1871 FND_MSG_PUB.Add_Exc_Msg
1872 ( G_FILE_NAME
1873 , G_PKG_NAME
1874 );
1875 END IF;
1876 FND_MSG_PUB.Count_And_Get
1877 ( p_count => x_msg_count
1878 , p_data => x_msg_data
1879 );
1880 END send_welcome_notif;
1881
1882 /*============================================================================
1883 -- Start of comments
1884 -- API name : send_rejection_notif
1885 -- Type : Private.
1886 -- Function : This API compiles and sends the rejection notification to a
1887 -- partner, once the partner user's enrollment request for a
1888 -- partner program is rejected by the approver.
1889 -- Pre-reqs : None.
1890 -- Parameters :
1891 -- IN : p_api_version IN NUMBER Required
1892 -- p_init_msg_list IN VARCHAR2 Optional
1893 -- Default = FND_API.G_FALSE
1894 -- p_commit IN VARCHAR2 Optional
1895 -- Default = FND_API.G_FALSE
1896 -- p_validation_level IN NUMBER Optional
1897 -- Default = FND_API.G_VALID_LEVEL_FULL
1898 -- p_enrl_request_id IN NUMBER Required
1899 --
1900 -- OUT : x_return_status OUT VARCHAR2(1)
1901 -- x_msg_count OUT NUMBER
1902 -- x_msg_data OUT VARCHAR2(2000)
1903 --
1904 -- Version : Current version 1.0
1905 -- Initial version 1.0
1906 --
1907 -- Notes : Note text
1908 --
1909 -- End of comments
1910 ============================================================================*/
1911 PROCEDURE send_rejection_notif (
1912 p_api_version IN NUMBER ,
1913 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
1914 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
1915 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL ,
1916 x_return_status OUT NOCOPY VARCHAR2 ,
1917 x_msg_count OUT NOCOPY NUMBER ,
1918 x_msg_data OUT NOCOPY VARCHAR2 ,
1919 p_enrl_request_id IN NUMBER
1920 )
1921 IS
1922
1923 /* Declaration of local variables. */
1924 l_api_name CONSTANT VARCHAR2(30) := 'send_rejection_notif';
1925 l_api_version CONSTANT NUMBER := 1.0;
1926 l_return_status VARCHAR2(1);
1927
1928 l_enrl_request_id NUMBER;
1929 l_req_resource_id NUMBER ;
1930 l_req_submission_date VARCHAR2(240);
1931 l_enrollment_duration VARCHAR2(240);
1932 l_partner_program_id NUMBER;
1933 l_partner_program VARCHAR2(240);
1934 l_enrollment_type VARCHAR2(240);
1935 l_prtnr_vndr_relship_id NUMBER;
1936 l_user_id NUMBER;
1937 l_notif_user_id NUMBER;
1938 l_source_name VARCHAR2(360);
1939 l_requestor_name VARCHAR2(360);
1940 l_user_name VARCHAR2(100);
1941 l_vendor_party_id NUMBER;
1942 l_partner_party_id NUMBER;
1943 l_vendor_name VARCHAR2(360);
1944 l_partner_comp_name VARCHAR2(360);
1945 l_pgp_notif_rec PV_GE_PARTY_NOTIF_PVT.pgp_notif_rec_type ;
1946 l_arc_notif_for_entity_code VARCHAR2(30) := 'ENRQ';
1947 l_notif_type_code VARCHAR2(30) := 'PG_REJECT';
1948
1949 /* Declaration of local variables for all the message attributes */
1950 l_enrl_alert VARCHAR2(240);
1951 l_rejection_info VARCHAR2(240);
1952 l_alert_thanks VARCHAR2(240);
1953 l_alert_closing VARCHAR2(240);
1954 l_enrollment_team VARCHAR2(240);
1955 l_message_hdr VARCHAR2(2000):= NULL;
1956 l_message_body VARCHAR2(4000):= NULL;
1957 l_message_footer VARCHAR2(2000):= NULL;
1958
1959
1960 l_item_type VARCHAR2(8) := 'PVXNUTIL';
1961 l_message_name VARCHAR2(20):= 'ALERT_MESSAGE';
1962 -- l_message_name VARCHAR2(20):= 'DOC_MESSAGE';
1963
1964 l_role_name VARCHAR2(100);
1965 l_display_role_name VARCHAR2(240);
1966 l_notif_id NUMBER;
1967 l_user_count NUMBER;
1968 l_user_resource_id NUMBER ;
1969 x_user_notify_rec_tbl user_notify_rec_tbl_type;
1970 x_party_notification_id NUMBER;
1971 -- l_newline_msg VARCHAR2(1) := FND_GLOBAL.Newline;
1972 -- l_newline VARCHAR2(10) := l_newline_msg || '<BR>';
1973 -- l_newline VARCHAR2(5) := '<BR>';
1974 l_newline VARCHAR2(5) := wf_core.newline;
1975
1976 l_notif_rule_active VARCHAR2(1):='N';
1977 l_log_params_tbl PVX_UTILITY_PVT.log_params_tbl_type;
1978
1979 BEGIN
1980 -- Standard Start of API savepoint
1981 SAVEPOINT send_rejection_notif_PVT;
1982
1983 -- Standard call to check for call compatibility.
1984 IF NOT FND_API.Compatible_API_Call (l_api_version ,
1985 p_api_version ,
1986 l_api_name ,
1987 G_PKG_NAME
1988 )
1989 THEN
1990 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1991 END IF;
1992
1993 -- Initialize message list if p_init_msg_list is set to TRUE.
1994 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1995 FND_MSG_PUB.initialize;
1996 END IF;
1997
1998 /* Initialize API return status to success */
1999 x_return_status := FND_API.G_RET_STS_SUCCESS;
2000
2001 /* Validate the Enrollment Request Id */
2002 IF ( P_validation_level >= FND_API.G_VALID_LEVEL_FULL)
2003 THEN
2004 -- Debug message
2005 IF (PV_DEBUG_HIGH_ON) THEN
2006
2007 PVX_UTILITY_PVT.debug_message('Validate_Enrl_Requests_Id');
2008 END IF;
2009
2010 -- Invoke validation procedures
2011 Validate_Enrl_Requests
2012 ( p_enrl_request_id ,
2013 'ENRL_REQUEST_ID',
2014 l_return_status
2015 );
2016
2017 -- If any errors happen abort API.
2018 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2019 RAISE FND_API.G_EXC_ERROR;
2020 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2021 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2022 END IF;
2023 END IF;
2024
2025 /* Get the enrollment request details*/
2026 get_enrl_requests_details(
2027 p_enrl_request_id => p_enrl_request_id ,
2028 x_req_submission_date => l_req_submission_date,
2029 x_partner_program_id => l_partner_program_id,
2030 x_partner_program => l_partner_program,
2031 x_enrollment_duration => l_enrollment_duration,
2032 x_enrollment_type => l_enrollment_type,
2033 x_req_resource_id => l_req_resource_id,
2034 x_prtnr_vndr_relship_id => l_prtnr_vndr_relship_id,
2035 x_return_status => x_return_status);
2036
2037 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2038 FND_MESSAGE.SET_NAME('PV', 'PV_ENRL_REQ_NOT_EXIST');
2039 FND_MESSAGE.SET_TOKEN('ENRL_REQUEST_ID',p_enrl_request_id);
2040 FND_MSG_PUB.Add;
2041 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2042 RAISE FND_API.G_EXC_ERROR;
2043 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2044 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2045 END IF;
2046 END IF;
2047
2048 /* If Notification Rule is Active for the given PROGRAM_ID, then only
2049 proceed, else do not send the notification. */
2050
2051 l_notif_rule_active := check_Notif_Rule_Active(
2052 p_program_id => l_partner_program_id,
2053 p_notif_type => 'PG_REJECT' ) ;
2054
2055 IF ( l_notif_rule_active = 'Y' ) THEN
2056
2057 /* Get the Partner and Vendor details */
2058 get_prtnr_vendor_details(
2059 p_enrl_request_id => p_enrl_request_id ,
2060 x_vendor_party_id => l_vendor_party_id,
2061 x_vendor_name => l_vendor_name,
2062 x_partner_party_id => l_partner_party_id,
2063 x_partner_comp_name => l_partner_comp_name,
2064 x_return_status => x_return_status);
2065
2066 /* Check for API return status */
2067 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2068 FND_MESSAGE.SET_NAME('PV','PV_PRTNR_VNDR_NOT_EXIST');
2069 FND_MESSAGE.SET_TOKEN('ENRL_REQUEST_ID',p_enrl_request_id);
2070 FND_MSG_PUB.Add;
2071 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2072 RAISE FND_API.G_EXC_ERROR;
2073 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2074 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2075 END IF;
2076 END IF;
2077
2078 /* Validate the Enrollment Requestor Resource Id */
2079 IF ( P_validation_level >= FND_API.G_VALID_LEVEL_FULL)
2080 THEN
2081 -- Debug message
2082 IF (PV_DEBUG_HIGH_ON) THEN
2083
2084 PVX_UTILITY_PVT.debug_message('Validate_Enrl_Requestor_Resource_Id');
2085 END IF;
2086
2087 -- Invoke validation procedures
2088 Validate_Enrl_Requests
2089 ( l_req_resource_id ,
2090 'REQUESTOR_RESOURCE_ID',
2091 l_return_status
2092 );
2093
2094 -- If any errors happen abort API.
2095 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2096 RAISE FND_API.G_EXC_ERROR;
2097 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2098 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2099 END IF;
2100
2101 END IF;
2102
2103 /* Get the requestor details */
2104 get_requestor_details(
2105 p_req_resource_id => l_req_resource_id,
2106 x_user_id => l_user_id,
2107 x_source_name => l_source_name,
2108 x_user_name => l_user_name,
2109 x_return_status => x_return_status);
2110
2111 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2112 FND_MESSAGE.SET_NAME('PV','PV_REQUESTOR_NOT_EXIST');
2113 FND_MESSAGE.SET_TOKEN('REQ_RESOURCE_ID',l_req_resource_id);
2114 FND_MSG_PUB.Add;
2115 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2116 RAISE FND_API.G_EXC_ERROR;
2117 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2118 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2119 END IF;
2120 END IF;
2121
2122 /* Get the user list */
2123 get_users_list(
2124 p_partner_id => l_prtnr_vndr_relship_id,
2125 x_user_notify_rec_tbl => x_user_notify_rec_tbl ,
2126 x_user_count => l_user_count,
2127 x_return_status => x_return_status ) ;
2128
2129 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2130 FND_MESSAGE.SET_NAME('PV','PV_NO_PRIMARY_USER_EXIST');
2131 FND_MESSAGE.SET_TOKEN('PARTNER_ID',l_prtnr_vndr_relship_id);
2132 FND_MSG_PUB.Add;
2133 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2134 RAISE FND_API.G_EXC_ERROR;
2135 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2136 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2137 END IF;
2138 END IF;
2139
2140 /* Send the notification to all the users from that partner Organization
2141 for the given partner vendor relationship id. */
2142
2143 FOR i IN 1 .. l_user_count LOOP
2144 l_user_resource_id := x_user_notify_rec_tbl(i).user_resource_id;
2145 l_notif_user_id := x_user_notify_rec_tbl(i).user_id;
2146
2147 /* Get the role name for the given 'p_requestor_id'. */
2148 /*IF p_send_to_role_name IS NULL THEN */
2149 get_resource_role(
2150 p_resource_id => l_user_resource_id,
2151 x_role_name => l_role_name,
2152 x_role_display_name => l_display_role_name,
2153 x_return_status => x_return_status
2154 );
2155
2156 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2157 FND_MESSAGE.SET_NAME('PV','PV_RES_ROLE_NOT_EXIST');
2158 FND_MESSAGE.SET_TOKEN('RESOURCE_ID',l_user_resource_id);
2159 FND_MSG_PUB.Add;
2160 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2161 RAISE FND_API.G_EXC_ERROR;
2162 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2163 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2164 END IF;
2165 END IF;
2166
2167 /* Use the 'WF_Notification.send' procedure to send the notification */
2168 l_notif_id := WF_Notification.send (
2169 role => l_role_name
2170 , msg_type => l_item_type
2171 , msg_name => l_message_name );
2172
2173 /* Set all the entity attributes by replacing the supplyied parameters. */
2174 -- WF_Notification.SetAttrText(l_notif_id,'DOCUMENT_ID', 'PVXNUTIL:'||l_notif_id);
2175 /* Set the Vendor Name */
2176 /*
2177 fnd_message.set_name('PV', 'PV_VENDOR_NM');
2178 fnd_message.set_token('PV_VENDOR_NAME', l_vendor_name);
2179 WF_Notification.SetAttrText (l_notif_id, 'PV_VENDOR_NM', fnd_message.get);
2180 */
2181
2182 /* Set the subject line */
2183 fnd_message.set_name('PV', 'PV_NTF_REJECTION_SUBJECT');
2184 fnd_message.set_token('PV_PARTNER_PROGRAM', l_partner_program);
2185 WF_Notification.SetAttrText (l_notif_id, 'SUBJECT', fnd_message.get);
2186
2187 /* Set the Message Header */
2188 fnd_message.set_name('PV', 'PV_NTF_ENRL_ALERT');
2189 fnd_message.set_token('PV_VENDOR_NM', l_vendor_name);
2190 l_message_hdr := fnd_message.get || l_newline;
2191 -- WF_Notification.SetAttrText (l_notif_id, 'MESSAGE_HEADER', l_message_hdr);
2192
2193 /* Set the Message Body */
2194 fnd_message.set_name('PV', 'PV_NTF_REJECTION_MESG');
2195 fnd_message.set_token('PV_PARTNER_PROGRAM', l_partner_program);
2196 l_message_body := fnd_message.get || l_newline;
2197 l_message_body := l_message_body || l_newline;
2198
2199 /* Set the Partner Company Name */
2200 fnd_message.set_name('PV', 'PV_NTF_PARTNER_NM');
2201 fnd_message.set_token('PV_PARTNER_NM', l_partner_comp_name);
2202 l_message_body := l_message_body || fnd_message.get || l_newline;
2203
2204 /* Set the requestor name */
2205 fnd_message.set_name('PV', 'PV_NTF_REQUESTOR_NM');
2206 fnd_message.set_token('PV_REQUESTOR_NM', rtrim(l_source_name));
2207 l_message_body := l_message_body || fnd_message.get || l_newline;
2208
2209 /* Set the request submission date */
2210 fnd_message.set_name('PV', 'PV_NTF_REQ_SUBMIT_DT');
2211 fnd_message.set_token('PV_REQ_SUBMIT_DT', l_req_submission_date);
2212 l_message_body := l_message_body || fnd_message.get || l_newline;
2213
2214 /* Set the partner program name */
2215 fnd_message.set_name('PV', 'PV_NTF_PARTNER_PRGM');
2216 fnd_message.set_token('PV_PARTNER_PRGM', l_partner_program);
2217 l_message_body := l_message_body || fnd_message.get || l_newline;
2218
2219 /* Set the enrollment type */
2220 fnd_message.set_name('PV', 'PV_NTF_ENRL_TYPE');
2221 fnd_message.set_token('PV_ENRL_TYPE', l_enrollment_type);
2222 l_message_body := l_message_body || fnd_message.get|| l_newline;
2223
2224 /* Get the values for all message attributes from the message list for Message Footer */
2225 l_message_footer := l_newline || fnd_message.get_string('PV', 'PV_NTF_ALERT_THANKS') || l_newline;
2226 l_message_footer := l_message_footer || fnd_message.get_string('PV', 'PV_NTF_ALERT_CLOSING')|| l_newline;
2227 l_message_footer := l_message_footer || fnd_message.get_string('PV', 'PV_NTF_ENROLLMENT_TEAM') || l_newline;
2228
2229 WF_Notification.SetAttrText(l_notif_id,'MESSAGE_HEADER', l_message_hdr);
2230 WF_Notification.SetAttrText(l_notif_id,'MESSAGE_BODY', l_message_body);
2231 WF_Notification.SetAttrText(l_notif_id,'MESSAGE_FOOTER', l_message_footer);
2232
2233 WF_NOTIFICATION.Denormalize_Notification(l_notif_id);
2234
2235 /* Set the record for Create_Ge_Party_Notif API */
2236 Set_Pgp_Notif(
2237 p_notif_id => l_notif_id,
2238 p_object_version => 1,
2239 p_partner_id => l_prtnr_vndr_relship_id,
2240 p_user_id => l_notif_user_id,
2241 p_arc_notif_for_entity_code => l_arc_notif_for_entity_code,
2242 p_notif_for_entity_id => p_enrl_request_id,
2243 p_notif_type_code => l_notif_type_code,
2244 x_return_status => x_return_status ,
2245 x_pgp_notif_rec => l_pgp_notif_rec );
2246
2247 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2248 FND_MESSAGE.SET_NAME('PV','PV_SET_NOTIF_REC');
2249 FND_MESSAGE.SET_TOKEN('NOTIFICATION_ID',l_notif_id);
2250 FND_MSG_PUB.Add;
2251 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2252 RAISE FND_API.G_EXC_ERROR;
2253 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2254 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2255 END IF;
2256 END IF;
2257
2258 /* Call the Create_Ge_Party_Notif to insert a record in PV_GE_PARTY_NOTIFICATION */
2259
2260 PV_GE_PARTY_NOTIF_PVT.Create_Ge_Party_Notif (
2261 p_api_version_number => 1.0,
2262 p_init_msg_list => FND_API.G_FALSE ,
2263 p_commit => FND_API.G_FALSE ,
2264 p_validation_level => FND_API.G_VALID_LEVEL_FULL ,
2265 x_return_status => x_return_status ,
2266 x_msg_count => x_msg_count,
2267 x_msg_data => x_msg_data ,
2268 p_pgp_notif_rec => l_pgp_notif_rec,
2269 x_party_notification_id => x_party_notification_id );
2270
2271 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2272 FND_MESSAGE.SET_NAME('PV','PV_GE_PARTY_NOTIF_REC');
2273 FND_MESSAGE.SET_TOKEN('NOTIFICATION_ID',l_notif_id);
2274 FND_MSG_PUB.Add;
2275 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2276 RAISE FND_API.G_EXC_ERROR;
2277 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2278 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2279 END IF;
2280 END IF;
2281
2282
2283 END LOOP;
2284
2285 /* call transaction history log to record this log. */
2286 /* Set the log params for History log. */
2287 l_log_params_tbl(1).param_name := 'NOTIFICATION_TYPE';
2288 l_log_params_tbl(1).param_value := get_Notification_Name(l_notif_type_code);
2289 l_log_params_tbl(2).param_name := 'ITEM_NAME';
2290 l_log_params_tbl(2).param_value := 'ENRL_REQUEST_ID';
2291 l_log_params_tbl(3).param_name := 'ITEM_ID';
2292 l_log_params_tbl(3).param_value := p_enrl_request_id;
2293
2294 /* call transaction history log to record this log. */
2295 PVX_Utility_PVT.create_history_log(
2296 p_arc_history_for_entity_code=> 'GENERAL', --'ENRQ',
2297 p_history_for_entity_id => l_prtnr_vndr_relship_id, --p_enrl_request_id,
2298 p_history_category_code => 'APPROVAL',
2299 p_message_code => 'PV_NOTIF_HISTORY_MSG',
2300 p_partner_id => l_prtnr_vndr_relship_id,
2301 p_log_params_tbl => l_log_params_tbl,
2302 x_return_status => x_return_status,
2303 x_msg_count => x_msg_count,
2304 x_msg_data => x_msg_data );
2305
2306 /* if any error happens rollback only this row, and proceed to next record. otherwise commit */
2307 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2308 FND_MESSAGE.SET_NAME('PV','PV_CR_HISTORY_LOG');
2309 FND_MESSAGE.SET_TOKEN('ID',p_enrl_request_id);
2310 FND_MSG_PUB.Add;
2311 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2312 RAISE FND_API.G_EXC_ERROR;
2313 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2314 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2315 END IF;
2316 END IF;
2317
2318 -- Standard check of p_commit.
2319 IF FND_API.To_Boolean( p_commit ) THEN
2320 COMMIT WORK;
2321 END IF;
2322
2323 -- Standard call to get message count and if count is 1, get message info.
2324 FND_MSG_PUB.Count_And_Get
2325 ( p_count => x_msg_count ,
2326 p_data => x_msg_data
2327 );
2328
2329 END IF; /* End the IF condition for check_Notif_Rule_Active */
2330
2331 EXCEPTION
2332 WHEN FND_API.G_EXC_ERROR THEN
2333 ROLLBACK TO send_rejection_notif_PVT;
2334 x_return_status := FND_API.G_RET_STS_ERROR ;
2335 FND_MSG_PUB.Count_And_Get
2336 ( p_count => x_msg_count ,
2337 p_data => x_msg_data
2338 );
2339
2340 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2341 ROLLBACK TO send_rejection_notif_PVT;
2342 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2343 FND_MSG_PUB.Count_And_Get
2344 ( p_count => x_msg_count ,
2345 p_data => x_msg_data
2346 );
2347
2348 WHEN OTHERS THEN
2349 ROLLBACK TO send_rejection_notif_PVT;
2350 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2351 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2352 THEN
2353 FND_MSG_PUB.Add_Exc_Msg
2354 ( G_FILE_NAME
2355 , G_PKG_NAME
2356 );
2357 END IF;
2358 FND_MSG_PUB.Count_And_Get
2359 ( p_count => x_msg_count
2360 , p_data => x_msg_data
2361 );
2362 END send_rejection_notif;
2363 /*============================================================================
2364 -- Start of comments
2365 -- API name : send_cntrct_notrcvd_notif
2366 -- Type : Private.
2367 -- Function : This API compiles and sends the 'Signed Contract is not received'
2368 -- notification to a partner, when there signed copy of contract is
2369 -- not received by the vendor, which is required for approval the
2370 -- enrollment request.
2371 -- Pre-reqs : None.
2372 -- Parameters :
2373 -- IN : p_api_version IN NUMBER Required
2374 -- p_init_msg_list IN VARCHAR2 Optional
2375 -- Default = FND_API.G_FALSE
2376 -- p_commit IN VARCHAR2 Optional
2377 -- Default = FND_API.G_FALSE
2378 -- p_validation_level IN NUMBER Optional
2379 -- Default = FND_API.G_VALID_LEVEL_FULL
2380 -- p_enrl_request_id IN NUMBER Required
2381 --
2382 -- OUT : x_return_status OUT VARCHAR2(1)
2383 -- x_msg_count OUT NUMBER
2384 -- x_msg_data OUT VARCHAR2(2000)
2385 --
2386 -- Version : Current version 1.0
2387 -- Initial version 1.0
2388 --
2389 -- Notes : Note text
2390 --
2391 -- End of comments
2392 ============================================================================*/
2393 PROCEDURE send_cntrct_notrcvd_notif (
2394 p_api_version IN NUMBER ,
2395 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
2396 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
2397 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL ,
2398 x_return_status OUT NOCOPY VARCHAR2 ,
2399 x_msg_count OUT NOCOPY NUMBER ,
2400 x_msg_data OUT NOCOPY VARCHAR2 ,
2401 p_enrl_request_id IN NUMBER
2402 )
2403 IS
2404
2405 /* Declaration of local variables. */
2406 l_api_name CONSTANT VARCHAR2(30) := 'send_cntrct_notrcvd_notif';
2407 l_api_version CONSTANT NUMBER := 1.0;
2408 l_return_status VARCHAR2(1);
2409
2410 l_enrl_request_id NUMBER;
2411 l_req_resource_id NUMBER ;
2412 l_req_submission_date VARCHAR2(240);
2413 l_enrollment_duration VARCHAR2(240);
2414 l_partner_program_id NUMBER;
2415 l_partner_program VARCHAR2(240);
2416 l_enrollment_type VARCHAR2(240);
2417 l_prtnr_vndr_relship_id NUMBER;
2418 l_user_id NUMBER;
2419 l_notif_user_id NUMBER;
2420 l_source_name VARCHAR2(360);
2421 l_requestor_name VARCHAR2(360);
2422 l_user_name VARCHAR2(100);
2423 l_vendor_party_id NUMBER;
2424 l_partner_party_id NUMBER;
2425 l_vendor_name VARCHAR2(360);
2426 l_partner_comp_name VARCHAR2(360);
2427 l_pgp_notif_rec PV_GE_PARTY_NOTIF_PVT.pgp_notif_rec_type ;
2428 l_arc_notif_for_entity_code VARCHAR2(30) := 'ENRQ';
2429 l_notif_type_code VARCHAR2(30) := 'PG_CONTRCT_NRCVD';
2430 l_message_hdr VARCHAR2(2000):= NULL;
2431 l_message_body VARCHAR2(4000):= NULL;
2432 l_message_footer VARCHAR2(2000):= NULL;
2433
2434 /* Declaration of local variables for all the message attributes */
2435 l_cntrct_mesg VARCHAR2(240);
2436 l_cntrct_addl_mesg VARCHAR2(240);
2437 l_enrl_alert VARCHAR2(240);
2438 l_alert_thanks VARCHAR2(240);
2439 l_alert_closing VARCHAR2(240);
2440 l_enrollment_team VARCHAR2(240);
2441
2442 l_item_type VARCHAR2(8) := 'PVXNUTIL';
2443 l_message_name VARCHAR2(30):= 'ALERT_MESSAGE';
2444 -- l_message_name VARCHAR2(20):= 'DOC_MESSAGE';
2445
2446 l_role_name VARCHAR2(100);
2447 l_display_role_name VARCHAR2(240);
2448 l_notif_id NUMBER;
2449 l_user_count NUMBER;
2450 l_user_resource_id NUMBER ;
2451 x_user_notify_rec_tbl user_notify_rec_tbl_type;
2452 x_party_notification_id NUMBER;
2453 -- l_newline_msg VARCHAR2(1) := FND_GLOBAL.Newline;
2454 -- l_newline VARCHAR2(10) := l_newline_msg || '<BR>';
2455 -- l_newline VARCHAR2(5) := '<BR>';
2456 l_newline VARCHAR2(5) := wf_core.newline;
2457
2458 l_notif_rule_active VARCHAR2(1):='N';
2459 l_log_params_tbl PVX_UTILITY_PVT.log_params_tbl_type;
2460
2461 BEGIN
2462 -- Standard Start of API savepoint
2463 SAVEPOINT send_cntrct_notrcvd_notif_PVT;
2464
2465 -- Standard call to check for call compatibility.
2466 IF NOT FND_API.Compatible_API_Call (l_api_version ,
2467 p_api_version ,
2468 l_api_name ,
2469 G_PKG_NAME
2470 )
2471 THEN
2472 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2473 END IF;
2474
2475 -- Initialize message list if p_init_msg_list is set to TRUE.
2476 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2477 FND_MSG_PUB.initialize;
2478 END IF;
2479
2480 /* Initialize API return status to success */
2481 x_return_status := FND_API.G_RET_STS_SUCCESS;
2482
2483 /* Validate the Enrollment Request Id */
2484 IF ( P_validation_level >= FND_API.G_VALID_LEVEL_FULL)
2485 THEN
2486 -- Debug message
2487 IF (PV_DEBUG_HIGH_ON) THEN
2488
2489 PVX_UTILITY_PVT.debug_message('Validate_Enrl_Requests_Id');
2490 END IF;
2491
2492 -- Invoke validation procedures
2493 Validate_Enrl_Requests
2494 ( p_enrl_request_id ,
2495 'ENRL_REQUEST_ID',
2496 l_return_status
2497 );
2498
2499 -- If any errors happen abort API.
2500 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2501 RAISE FND_API.G_EXC_ERROR;
2502 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2503 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2504 END IF;
2505 END IF;
2506
2507 /* Get the enrollment request details*/
2508 get_enrl_requests_details(
2509 p_enrl_request_id => p_enrl_request_id ,
2510 x_req_submission_date => l_req_submission_date,
2511 x_partner_program_id => l_partner_program_id,
2512 x_partner_program => l_partner_program,
2513 x_enrollment_duration => l_enrollment_duration,
2514 x_enrollment_type => l_enrollment_type,
2515 x_req_resource_id => l_req_resource_id,
2516 x_prtnr_vndr_relship_id => l_prtnr_vndr_relship_id,
2517 x_return_status => x_return_status);
2518
2519 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2520 FND_MESSAGE.SET_NAME('PV', 'PV_ENRL_REQ_NOT_EXIST');
2521 FND_MESSAGE.SET_TOKEN('ENRL_REQUEST_ID',p_enrl_request_id);
2522 FND_MSG_PUB.Add;
2523 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2524 RAISE FND_API.G_EXC_ERROR;
2525 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2526 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2527 END IF;
2528 END IF;
2529
2530 /* If Notification Rule is Active for the given PROGRAM_ID, then only
2531 proceed, else do not send the notification. */
2532
2533 l_notif_rule_active := check_Notif_Rule_Active(
2534 p_program_id => l_partner_program_id,
2535 p_notif_type => 'PG_CONTRCT_NRCVD') ;
2536
2537 IF ( l_notif_rule_active = 'Y' ) THEN
2538
2539 /* Get the Partner and Vendor details */
2540 get_prtnr_vendor_details(
2541 p_enrl_request_id => p_enrl_request_id ,
2542 x_vendor_party_id => l_vendor_party_id,
2543 x_vendor_name => l_vendor_name,
2544 x_partner_party_id => l_partner_party_id,
2545 x_partner_comp_name => l_partner_comp_name,
2546 x_return_status => x_return_status);
2547
2548 /* Check the Procedure's x_return_status */
2549 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2550 FND_MESSAGE.SET_NAME('PV','PV_PRTNR_VNDR_NOT_EXIST');
2551 FND_MESSAGE.SET_TOKEN('ENRL_REQUEST_ID',p_enrl_request_id);
2552 FND_MSG_PUB.Add;
2553 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2554 RAISE FND_API.G_EXC_ERROR;
2555 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2556 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2557 END IF;
2558 END IF;
2559
2560 /* Get the requestor details */
2561 get_requestor_details(
2562 p_req_resource_id => l_req_resource_id,
2563 x_user_id => l_user_id,
2564 x_source_name => l_source_name,
2565 x_user_name => l_user_name,
2566 x_return_status => x_return_status);
2567
2568 /* Check the Procedure's x_return_status */
2569 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2570 FND_MESSAGE.SET_NAME('PV','PV_REQUESTOR_NOT_EXIST');
2571 FND_MESSAGE.SET_TOKEN('REQ_RESOURCE_ID',l_req_resource_id);
2572 FND_MSG_PUB.Add;
2573 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2574 RAISE FND_API.G_EXC_ERROR;
2575 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2576 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2577 END IF;
2578 END IF;
2579
2580 /* Get the user list */
2581 get_users_list(
2582 p_partner_id => l_prtnr_vndr_relship_id,
2583 x_user_notify_rec_tbl => x_user_notify_rec_tbl ,
2584 x_user_count => l_user_count,
2585 x_return_status => x_return_status ) ;
2586
2587 /* Check the Procedure's x_return_status */
2588 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2589 FND_MESSAGE.SET_NAME('PV','PV_NO_PRIMARY_USER_EXIST');
2590 FND_MESSAGE.SET_TOKEN('PARTNER_ID',l_prtnr_vndr_relship_id);
2591 FND_MSG_PUB.Add;
2592 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2593 RAISE FND_API.G_EXC_ERROR;
2594 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2595 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2596 END IF;
2597 END IF;
2598
2599 /* Send the notification to all the users from that partner Organization
2600 for the given partner vendor relationship id. */
2601
2602 FOR i IN 1 .. l_user_count LOOP
2603 l_user_resource_id := x_user_notify_rec_tbl(i).user_resource_id;
2604 l_notif_user_id := x_user_notify_rec_tbl(i).user_id;
2605
2606 /* Get the role name for the given 'p_requestor_id'. */
2607 get_resource_role(
2608 p_resource_id => l_user_resource_id,
2609 x_role_name => l_role_name,
2610 x_role_display_name => l_display_role_name,
2611 x_return_status => x_return_status
2612 );
2613
2614 /* Check the Procedure's x_return_status */
2615 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2616 FND_MESSAGE.SET_NAME('PV','PV_RES_ROLE_NOT_EXIST');
2617 FND_MESSAGE.SET_TOKEN('RESOURCE_ID',l_user_resource_id);
2618 FND_MSG_PUB.Add;
2619 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2620 RAISE FND_API.G_EXC_ERROR;
2621 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2622 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2623 END IF;
2624 END IF;
2625
2626 /* Use the 'WF_Notification.send' procedure to send the notification */
2627 l_notif_id := WF_Notification.send (
2628 role => l_role_name
2629 , msg_type => l_item_type
2630 , msg_name => l_message_name );
2631
2632 /* Set all the entity attributes by replacing the supplied parameters. */
2633 -- WF_Notification.SetAttrText(l_notif_id,'DOCUMENT_ID', 'PVXNUTIL:'||l_notif_id);
2634
2635 /* Set the subject line */
2636 fnd_message.set_name('PV', 'PV_NTF_CONTRACT_SUBJECT');
2637 fnd_message.set_token('PV_PARTNER_PROGRAM', l_partner_program);
2638 WF_Notification.SetAttrText (l_notif_id, 'SUBJECT', fnd_message.get);
2639
2640 /* Set the Message Header */
2641 fnd_message.set_name('PV', 'PV_NTF_ENRL_ALERT');
2642 fnd_message.set_token('PV_VENDOR_NM', l_vendor_name);
2643 l_message_hdr := fnd_message.get || l_newline;
2644
2645 /* Set the Vendor Name */
2646 /*
2647 fnd_message.set_name('PV', 'PV_VENDOR_NM');
2648 fnd_message.set_token('PV_VENDOR_NAME', l_vendor_name);
2649 WF_Notification.SetAttrText (l_notif_id, 'PV_VENDOR_NM', fnd_message.get);
2650 */
2651
2652 l_message_body := fnd_message.get_string('PV', 'PV_NTF_CONTRACT_MESG')|| l_newline;
2653 l_message_body := l_message_body || l_newline;
2654
2655 /* Set the Partner Company Name */
2656 fnd_message.set_name('PV', 'PV_NTF_PARTNER_NM');
2657 fnd_message.set_token('PV_PARTNER_NM', l_partner_comp_name);
2658 l_message_body := l_message_body || fnd_message.get || l_newline;
2659
2660 /* Set the Requestor Name */
2661 fnd_message.set_name('PV', 'PV_NTF_REQUESTOR_NM');
2662 fnd_message.set_token('PV_REQUESTOR_NM', l_source_name);
2663 l_message_body := l_message_body || fnd_message.get || l_newline;
2664
2665 /* Set the request submission date */
2666 fnd_message.set_name('PV', 'PV_NTF_REQ_SUBMIT_DT');
2667 fnd_message.set_token('PV_REQ_SUBMIT_DT', l_req_submission_date);
2668 l_message_body := l_message_body || fnd_message.get || l_newline;
2669
2670 /* Set the Partner Program Name */
2671 fnd_message.set_name('PV', 'PV_NTF_PARTNER_PRGM');
2672 fnd_message.set_token('PV_PARTNER_PRGM', l_partner_program);
2673 l_message_body := l_message_body || fnd_message.get || l_newline;
2674
2675 /* Set the Enrollment Type */
2676 fnd_message.set_name('PV', 'PV_NTF_ENRL_TYPE');
2677 fnd_message.set_token('PV_ENRL_TYPE', l_enrollment_type);
2678 l_message_body := l_message_body || fnd_message.get || l_newline;
2679 l_message_body := l_message_body || l_newline;
2680
2681 /* Get the values for all message attributes from the message list */
2682 l_message_body := l_message_body || fnd_message.get_string('PV', 'PV_NTF_CONTRACT_ADDL_MESG')|| l_newline;
2683
2684 /* Get the values for all message attributes from the message list for Message Footer */
2685 l_message_footer := l_newline || fnd_message.get_string('PV', 'PV_NTF_ALERT_THANKS') || l_newline;
2686 l_message_footer := l_message_footer || fnd_message.get_string('PV', 'PV_NTF_ALERT_CLOSING')|| l_newline;
2687 l_message_footer := l_message_footer || fnd_message.get_string('PV', 'PV_NTF_ENROLLMENT_TEAM') || l_newline;
2688
2689 WF_Notification.SetAttrText(l_notif_id,'MESSAGE_HEADER', l_message_hdr);
2690 WF_Notification.SetAttrText(l_notif_id,'MESSAGE_BODY', l_message_body);
2691 WF_Notification.SetAttrText(l_notif_id,'MESSAGE_FOOTER', l_message_footer);
2692
2693 WF_NOTIFICATION.Denormalize_Notification(l_notif_id);
2694
2695 /* Set the record for Create_Ge_Party_Notif API */
2696 Set_Pgp_Notif(
2697 p_notif_id => l_notif_id,
2698 p_object_version => 1,
2699 p_partner_id => l_prtnr_vndr_relship_id,
2700 p_user_id => l_notif_user_id,
2701 p_arc_notif_for_entity_code => l_arc_notif_for_entity_code,
2702 p_notif_for_entity_id => p_enrl_request_id,
2703 p_notif_type_code => l_notif_type_code,
2704 x_return_status => x_return_status ,
2705 x_pgp_notif_rec => l_pgp_notif_rec );
2706
2707 /* Check the Procedure's x_return_status */
2708 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2709 FND_MESSAGE.SET_NAME('PV','PV_SET_NOTIF_REC');
2710 FND_MESSAGE.SET_TOKEN('NOTIFICATION_ID',l_notif_id);
2711 FND_MSG_PUB.Add;
2712 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2713 RAISE FND_API.G_EXC_ERROR;
2714 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2715 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2716 END IF;
2717 END IF;
2718
2719 /* Call the Create_Ge_Party_Notif to insert a record in PV_GE_PARTY_NOTIFICATION */
2720
2721 PV_GE_PARTY_NOTIF_PVT.Create_Ge_Party_Notif (
2722 p_api_version_number => 1.0,
2723 p_init_msg_list => FND_API.G_FALSE ,
2724 p_commit => FND_API.G_FALSE ,
2725 p_validation_level => FND_API.G_VALID_LEVEL_FULL ,
2726 x_return_status => x_return_status ,
2727 x_msg_count => x_msg_count,
2728 x_msg_data => x_msg_data ,
2729 p_pgp_notif_rec => l_pgp_notif_rec,
2730 x_party_notification_id => x_party_notification_id );
2731
2732 /* Check the Procedure's x_return_status */
2733 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2734 FND_MESSAGE.SET_NAME('PV','PV_GE_PARTY_NOTIF_REC');
2735 FND_MESSAGE.SET_TOKEN('NOTIFICATION_ID',l_notif_id);
2736 FND_MSG_PUB.Add;
2737 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2738 RAISE FND_API.G_EXC_ERROR;
2739 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2740 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2741 END IF;
2742 END IF;
2743
2744
2745 END LOOP;
2746
2747 /* call transaction history log to record this log. */
2748 /* Set the log params for History log. */
2749 l_log_params_tbl(1).param_name := 'NOTIFICATION_TYPE';
2750 l_log_params_tbl(1).param_value := get_Notification_Name(l_notif_type_code);
2751 l_log_params_tbl(2).param_name := 'ITEM_NAME';
2752 l_log_params_tbl(2).param_value := 'ENRL_REQUEST_ID';
2753 l_log_params_tbl(3).param_name := 'ITEM_ID';
2754 l_log_params_tbl(3).param_value := p_enrl_request_id;
2755
2756 /* call transaction history log to record this log. */
2757 PVX_Utility_PVT.create_history_log(
2758 p_arc_history_for_entity_code => 'GENERAL', --'ENRQ',
2759 p_history_for_entity_id => l_prtnr_vndr_relship_id, --p_enrl_request_id,
2760 p_history_category_code => 'CONTRACT',
2761 p_message_code => 'PV_NOTIF_HISTORY_MSG',
2762 p_log_params_tbl => l_log_params_tbl,
2763 p_partner_id => l_prtnr_vndr_relship_id,
2764 x_return_status => x_return_status,
2765 x_msg_count => x_msg_count,
2766 x_msg_data => x_msg_data );
2767
2768 /* Check for x_return_status */
2769 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2770 FND_MESSAGE.SET_NAME('PV','PV_CR_HISTORY_LOG');
2771 FND_MESSAGE.SET_TOKEN('ID',p_enrl_request_id);
2772 FND_MSG_PUB.Add;
2773 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2774 RAISE FND_API.G_EXC_ERROR;
2775 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2776 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2777 END IF;
2778 END IF;
2779
2780 -- Standard check of p_commit.
2781 IF FND_API.To_Boolean( p_commit ) THEN
2782 COMMIT WORK;
2783 END IF;
2784
2785 -- Standard call to get message count and if count is 1, get message info.
2786 FND_MSG_PUB.Count_And_Get
2787 ( p_count => x_msg_count ,
2788 p_data => x_msg_data
2789 );
2790
2791 END IF; /* End the IF condition for check_Notif_Rule_Active */
2792
2793 EXCEPTION
2794 WHEN FND_API.G_EXC_ERROR THEN
2795 ROLLBACK TO send_cntrct_notrcvd_notif_PVT;
2796 x_return_status := FND_API.G_RET_STS_ERROR ;
2797 FND_MSG_PUB.Count_And_Get
2798 ( p_count => x_msg_count ,
2799 p_data => x_msg_data
2800 );
2801 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2802 ROLLBACK TO send_cntrct_notrcvd_notif_PVT;
2803 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2804 FND_MSG_PUB.Count_And_Get
2805 ( p_count => x_msg_count ,
2806 p_data => x_msg_data
2807 );
2808 WHEN OTHERS THEN
2809 ROLLBACK TO send_cntrct_notrcvd_notif_PVT;
2810 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2811
2812 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2813 THEN
2814 FND_MSG_PUB.Add_Exc_Msg
2815 ( G_FILE_NAME
2816 , G_PKG_NAME
2817 );
2818 END IF;
2819 FND_MSG_PUB.Count_And_Get
2820 ( p_count => x_msg_count
2821 , p_data => x_msg_data
2822 );
2823 END send_cntrct_notrcvd_notif;
2824
2825 /*============================================================================
2826 -- Start of comments
2827 -- API name : send_mbrship_exp_notif
2828 -- Type : Private.
2829 -- Function : This API compiles and sends the 'Membership Expiry' Notification
2830 -- to a partner, once the partner user's enrollment is going to
2831 -- expire in near future.
2832 -- Pre-reqs : None.
2833 -- Parameters :
2834 -- IN : p_api_version IN NUMBER Required
2835 -- p_init_msg_list IN VARCHAR2 Optional
2836 -- Default = FND_API.G_FALSE
2837 -- p_commit IN VARCHAR2 Optional
2838 -- Default = FND_API.G_FALSE
2839 -- p_validation_level IN NUMBER Optional
2840 -- Default = FND_API.G_VALID_LEVEL_FULL
2841 -- p_membership_id IN NUMBER Required
2842 --
2843 -- OUT : x_return_status OUT VARCHAR2(1)
2844 -- x_msg_count OUT NUMBER
2845 -- x_msg_data OUT VARCHAR2(2000)
2846 --
2847 -- Version : Current version 1.0
2848 -- Initial version 1.0
2849 --
2850 -- Notes : Note text
2851 --
2852 -- End of comments
2853 ============================================================================*/
2854 PROCEDURE send_mbrship_exp_notif (
2855 p_api_version IN NUMBER ,
2856 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
2857 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
2858 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL ,
2859 x_return_status OUT NOCOPY VARCHAR2 ,
2860 x_msg_count OUT NOCOPY NUMBER ,
2861 x_msg_data OUT NOCOPY VARCHAR2 ,
2862 p_membership_id IN NUMBER
2863 )
2864 IS
2865
2866 /* Declaration of local variables. */
2867 l_api_name CONSTANT VARCHAR2(30) := 'send_membership_expiry_notif';
2868 l_api_version CONSTANT NUMBER := 1.0;
2869 l_return_status VARCHAR2(1);
2870
2871 l_membership_id NUMBER;
2872 l_enrl_request_id NUMBER;
2873 l_req_resource_id NUMBER ;
2874 l_req_submission_date VARCHAR2(240);
2875 l_enrollment_start_date VARCHAR2(240);
2876 l_enrollment_end_date VARCHAR2(240);
2877 l_partner_program_id NUMBER;
2878 l_partner_program VARCHAR2(240);
2879 l_enrollment_type VARCHAR2(240);
2880 l_prtnr_vndr_relship_id NUMBER;
2881 l_user_id NUMBER;
2882 l_notif_user_id NUMBER;
2883 l_source_name VARCHAR2(360);
2884 l_requestor_name VARCHAR2(360);
2885 l_user_name VARCHAR2(100);
2886 l_vendor_name VARCHAR2(360);
2887 l_vendor_party_id NUMBER;
2888 l_partner_party_id NUMBER;
2889 l_partner_comp_name VARCHAR2(360);
2890 l_enrl_expiry_in_days VARCHAR2(80);
2891 l_pgp_notif_rec PV_GE_PARTY_NOTIF_PVT.pgp_notif_rec_type ;
2892 l_arc_notif_for_entity_code VARCHAR2(30) := 'MEMBR';
2893 l_notif_type_code VARCHAR2(30) := 'PG_MEM_EXP';
2894
2895 /* Declaration of local variables for all the message attributes */
2896 l_expiry_mesg VARCHAR2(240);
2897 l_expiry_addl_mesg VARCHAR2(240);
2898 l_enrl_alert VARCHAR2(240);
2899 l_alert_thanks VARCHAR2(240);
2900 l_alert_closing VARCHAR2(240);
2901 l_enrollment_team VARCHAR2(240);
2902
2903 l_item_type VARCHAR2(8) := 'PVXNUTIL';
2904 -- l_message_name VARCHAR2(20):= 'ALERT_MESSAGE';
2905 l_message_name VARCHAR2(20):= 'DOC_MESSAGE';
2906
2907 l_message_hdr VARCHAR2(2000):= NULL;
2908 l_message_body VARCHAR2(4000):= NULL;
2909 l_message_footer VARCHAR2(2000):= NULL;
2910 l_role_name VARCHAR2(100);
2911 l_display_role_name VARCHAR2(240);
2912 l_notif_id NUMBER;
2913 l_user_count NUMBER;
2914 l_user_resource_id NUMBER ;
2915 x_user_notify_rec_tbl user_notify_rec_tbl_type;
2916 x_party_notification_id NUMBER;
2917 -- l_newline_msg VARCHAR2(1) := FND_GLOBAL.Newline;
2918 -- l_newline VARCHAR2(10) := l_newline_msg || '<BR>';
2919 l_newline VARCHAR2(5) := '<BR>'; -- not using wf_core as we are using pl/sql document
2920 -- l_newline VARCHAR2(5) := wf_core.newline;
2921
2922 l_notif_rule_active VARCHAR2(1);
2923 l_log_params_tbl PVX_UTILITY_PVT.log_params_tbl_type;
2924 l_prtner_portal_url VARCHAR2(2000);
2925 l_login_url VARCHAR2(4000);
2926
2927 BEGIN
2928 -- Standard Start of API savepoint
2929 SAVEPOINT send_mbership_expiry_notif_PVT;
2930
2931 -- Standard call to check for call compatibility.
2932 IF NOT FND_API.Compatible_API_Call (l_api_version ,
2933 p_api_version ,
2934 l_api_name ,
2935 G_PKG_NAME
2936 )
2937 THEN
2938 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2939 END IF;
2940
2941 -- Initialize message list if p_init_msg_list is set to TRUE.
2942 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2943 FND_MSG_PUB.initialize;
2944 END IF;
2945
2946 /* Initialize API return status to success */
2947 x_return_status := FND_API.G_RET_STS_SUCCESS;
2948
2949 /* Validate the Enrollment Request Id */
2950 IF ( P_validation_level >= FND_API.G_VALID_LEVEL_FULL)
2951 THEN
2952 -- Debug message
2953 IF (PV_DEBUG_HIGH_ON) THEN
2954
2955 PVX_UTILITY_PVT.debug_message('Validate_Membership_Id');
2956 END IF;
2957
2958 -- Invoke validation procedures
2959 Validate_Enrl_Requests
2960 ( p_membership_id ,
2961 'MEMBERSHIP_ID',
2962 l_return_status
2963 );
2964
2965 /* If any errors happen abort API. */
2966 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2967 RAISE FND_API.G_EXC_ERROR;
2968 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2969 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2970 END IF;
2971 END IF;
2972
2973 /* Get the membership details*/
2974 get_membership_details(
2975 p_membership_id => p_membership_id ,
2976 x_req_submission_date => l_req_submission_date,
2977 x_partner_program_id => l_partner_program_id,
2978 x_partner_program => l_partner_program,
2979 x_enrl_request_id => l_enrl_request_id,
2980 x_enrollment_start_date => l_enrollment_start_date,
2981 x_enrollment_end_date => l_enrollment_end_date,
2982 x_req_resource_id => l_req_resource_id,
2983 x_prtnr_vndr_relship_id => l_prtnr_vndr_relship_id,
2984 x_enrollment_type => l_enrollment_type,
2985 x_return_status => x_return_status);
2986
2987 /* Check Procedure's x_return_status */
2988 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2989 FND_MESSAGE.SET_NAME('PV','PV_MBRSHIP_NOT_EXIST');
2990 FND_MESSAGE.SET_TOKEN('MEMBERSHIP_ID',p_membership_id);
2991 FND_MSG_PUB.Add;
2992 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2993 RAISE FND_API.G_EXC_ERROR;
2994 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2995 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2996 END IF;
2997 END IF;
2998
2999 /* If Notification Rule is Active for the given PROGRAM_ID, then only
3000 proceed, else do not send the notification. */
3001
3002 l_notif_rule_active := check_Notif_Rule_Active(
3003 p_program_id => l_partner_program_id,
3004 p_notif_type => 'PG_MEM_EXP' ) ;
3005
3006 IF ( l_notif_rule_active = 'Y' ) THEN
3007
3008 /* Get the Partner and Vendor details */
3009 get_prtnr_vendor_details(
3010 p_enrl_request_id => l_enrl_request_id ,
3011 x_vendor_party_id => l_vendor_party_id,
3012 x_vendor_name => l_vendor_name,
3013 x_partner_party_id => l_partner_party_id,
3014 x_partner_comp_name => l_partner_comp_name,
3015 x_return_status => x_return_status);
3016
3017 /* Check for Procedure's x_return_status */
3018 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3019 FND_MESSAGE.SET_NAME('PV','PV_PRTNR_VNDR_NOT_EXIST');
3020 FND_MESSAGE.SET_TOKEN('ENRL_REQUEST_ID',l_enrl_request_id);
3021 FND_MSG_PUB.Add;
3022 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
3023 RAISE FND_API.G_EXC_ERROR;
3024 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3025 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3026 END IF;
3027 END IF;
3028
3029 /* Validate the Enrollment Requestor Resource Id */
3030 IF ( P_validation_level >= FND_API.G_VALID_LEVEL_FULL)
3031 THEN
3032 -- Debug message
3033 IF (PV_DEBUG_HIGH_ON) THEN
3034
3035 PVX_UTILITY_PVT.debug_message('Validate_Enrl_Requestor_Resource_Id');
3036 END IF;
3037
3038 -- Invoke validation procedures
3039 Validate_Enrl_Requests
3040 ( l_req_resource_id ,
3041 'REQUESTOR_RESOURCE_ID',
3042 l_return_status
3043 );
3044
3045 /* If any errors happen abort API. */
3046 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
3047 RAISE FND_API.G_EXC_ERROR;
3048 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3049 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3050 END IF;
3051
3052 END IF;
3053 /* Get the requestor details */
3054 get_requestor_details(
3055 p_req_resource_id => l_req_resource_id,
3056 x_user_id => l_user_id,
3057 x_source_name => l_source_name,
3058 x_user_name => l_user_name,
3059 x_return_status => x_return_status);
3060
3061 /* Check the Procedure's x_return_status */
3062 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3063 FND_MESSAGE.SET_NAME('PV','PV_REQUESTOR_NOT_EXIST');
3064 FND_MESSAGE.SET_TOKEN('REQ_RESOURCE_ID',l_req_resource_id);
3065 FND_MSG_PUB.Add;
3066 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
3067 RAISE FND_API.G_EXC_ERROR;
3068 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3069 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3070 END IF;
3071 END IF;
3072
3073 /* Get the user list */
3074 get_users_list(
3075 p_partner_id => l_prtnr_vndr_relship_id,
3076 x_user_notify_rec_tbl => x_user_notify_rec_tbl ,
3077 x_user_count => l_user_count,
3078 x_return_status => x_return_status ) ;
3079
3080 /* Check the Procedure's x_return_status */
3081 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3082 FND_MESSAGE.SET_NAME('PV','PV_NO_PRIMARY_USER_EXIST');
3083 FND_MESSAGE.SET_TOKEN('PARTNER_ID',l_prtnr_vndr_relship_id);
3084 FND_MSG_PUB.Add;
3085 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
3086 RAISE FND_API.G_EXC_ERROR;
3087 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3088 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3089 END IF;
3090 END IF;
3091
3092 /* Send the notification to all the users from that partner Organization
3093 for the given partner vendor relationship id. */
3094
3095 FOR i IN 1 .. l_user_count LOOP
3096 l_user_resource_id := x_user_notify_rec_tbl(i).user_resource_id;
3097 l_notif_user_id := x_user_notify_rec_tbl(i).user_id;
3098
3099 /* Get the role name for the given 'p_requestor_id'. */
3100 /*IF p_send_to_role_name IS NULL THEN */
3101 get_resource_role(
3102 p_resource_id => l_user_resource_id,
3103 x_role_name => l_role_name,
3104 x_role_display_name => l_display_role_name,
3105 x_return_status => x_return_status
3106 );
3107
3108 /* Check the Procedure's x_return_status */
3109 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3110 FND_MESSAGE.SET_NAME('PV','PV_RES_ROLE_NOT_EXIST');
3111 FND_MESSAGE.SET_TOKEN('RESOURCE_ID',l_user_resource_id);
3112 FND_MSG_PUB.Add;
3113 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
3114 RAISE FND_API.G_EXC_ERROR;
3115 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3116 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3117 END IF;
3118 END IF;
3119
3120 /* Use the 'WF_Notification.send' procedure to send the notification */
3121 l_notif_id := WF_Notification.send (
3122 role => l_role_name
3123 , msg_type => l_item_type
3124 , msg_name => l_message_name );
3125
3126 WF_Notification.SetAttrText(l_notif_id,'NOTIF_DOC_ID', 'PVXNUTIL:'||l_notif_id); -- passing the doc id
3127
3128 /* Set the subject line */
3129 fnd_message.set_name('PV', 'PV_NTF_EXPIRY_SUBJECT');
3130 fnd_message.set_token('PV_PARTNER_PROGRAM', l_partner_program);
3131 WF_Notification.SetAttrText (l_notif_id, 'SUBJECT', fnd_message.get);
3132
3133 /* Set the Message Header */
3134 fnd_message.set_name('PV', 'PV_NTF_ENRL_ALERT');
3135 fnd_message.set_token('PV_VENDOR_NM', l_vendor_name);
3136 l_message_hdr := fnd_message.get || l_newline;
3137
3138 /* Set all the entity attributes by replacing the supplied parameters. */
3139
3140 /* Set the Vendor Name */
3141 /*
3142 fnd_message.set_name('PV', 'PV_VENDOR_NM');
3143 fnd_message.set_token('PV_VENDOR_NAME', l_vendor_name);
3144 WF_Notification.SetAttrText (l_notif_id, 'PV_VENDOR_NM', fnd_message.get);
3145 */
3146
3147 l_message_body := fnd_message.get_string('PV', 'PV_NTF_EXPIRY_MESG')|| l_newline;
3148 l_message_body := l_message_body || l_newline;
3149
3150 /* Set the Partner Company Name */
3151 fnd_message.set_name('PV', 'PV_NTF_PARTNER_NM');
3152 fnd_message.set_token('PV_PARTNER_NM', l_partner_comp_name);
3153 l_message_body := l_message_body || fnd_message.get || l_newline;
3154
3155 /* Set the Requestor Name */
3156 fnd_message.set_name('PV', 'PV_NTF_REQUESTOR_NM');
3157 fnd_message.set_token('PV_REQUESTOR_NM', l_source_name);
3158 l_message_body := l_message_body || fnd_message.get || l_newline;
3159
3160 /* Set the Partner Program Name */
3161 fnd_message.set_name('PV', 'PV_NTF_PARTNER_PRGM');
3162 fnd_message.set_token('PV_PARTNER_PRGM', l_partner_program);
3163 l_message_body := l_message_body || fnd_message.get || l_newline;
3164
3165 /* Set the Enrollment Start Date */
3166 fnd_message.set_name('PV', 'PV_NTF_ENRL_START_DT');
3167 fnd_message.set_token('PV_ENRL_START_DT', l_enrollment_start_date);
3168 l_message_body := l_message_body || fnd_message.get || l_newline;
3169
3170 /* Set the Enrollment End Date */
3171 fnd_message.set_name('PV', 'PV_NTF_ENRL_END_DT');
3172 fnd_message.set_token('PV_ENRL_END_DT', l_enrollment_end_date);
3173 l_message_body := l_message_body || fnd_message.get || l_newline;
3174
3175 /* Set the Expiry in # of days */
3176 fnd_message.set_name('PV', 'PV_NTF_EXPIRY_IN_DAYS');
3177 --fnd_message.set_token('PV_ENRL_EXPIRY_IN_DAYS', trunc(to_date(l_enrollment_end_date) - sysdate) );
3178 fnd_message.set_token('PV_ENRL_EXPIRY_IN_DAYS', trunc( to_date( l_enrollment_end_date,'DD-MM-YY' ) - to_date( sysdate,'DD-MM-YY') ) );
3179 l_message_body := l_message_body || fnd_message.get || l_newline;
3180
3181 /* Get the values for all message attributes from the message list */
3182 /* Set the Log-in portal line */
3183 /* l_prtner_portal_url := icx_sec.createRFURL(
3184 p_function_name => 'PV_MYPARTNER_ORGZN',
3185 p_application_id => 691,
3186 p_responsibility_id => 23073,
3187 p_security_group_id => fnd_global.security_group_id );
3188 */
3189 /* Set the Log-in portal line */
3190 l_login_url := FND_PROFILE.VALUE('PV_WORKFLOW_RESPOND_URL');
3191 l_prtner_portal_url := '<a href="'|| l_login_url || '">'|| l_partner_program || '</a>';
3192
3193 fnd_message.set_name('PV', 'PV_NTF_EXPIRY_ADDL_MESG');
3194 fnd_message.set_token('PV_PARTNER_PORTAL_URL', l_prtner_portal_url);
3195 l_message_body := l_message_body ||fnd_message.get || l_newline;
3196
3197 /* Get the values for all message attributes from the message list for Message Footer */
3198 l_message_footer := l_newline || fnd_message.get_string('PV', 'PV_NTF_ALERT_THANKS') || l_newline;
3199 l_message_footer := l_message_footer || fnd_message.get_string('PV', 'PV_NTF_ALERT_CLOSING')|| l_newline;
3200 l_message_footer := l_message_footer || fnd_message.get_string('PV', 'PV_NTF_ENROLLMENT_TEAM') || l_newline;
3201
3202 WF_Notification.SetAttrText(l_notif_id,'MESSAGE_HEADER', l_message_hdr);
3203 WF_Notification.SetAttrText(l_notif_id,'MESSAGE_BODY', l_message_body);
3204 WF_Notification.SetAttrText(l_notif_id,'MESSAGE_FOOTER', l_message_footer);
3205
3206 WF_NOTIFICATION.Denormalize_Notification(l_notif_id);
3207
3208 /* Set the record for Create_Ge_Party_Notif API */
3209 Set_Pgp_Notif(
3210 p_notif_id => l_notif_id,
3211 p_object_version => 1,
3212 p_partner_id => l_prtnr_vndr_relship_id,
3213 p_user_id => l_notif_user_id,
3214 p_arc_notif_for_entity_code => l_arc_notif_for_entity_code,
3215 p_notif_for_entity_id => p_membership_id,
3216 p_notif_type_code => l_notif_type_code,
3217 x_return_status => x_return_status ,
3218 x_pgp_notif_rec => l_pgp_notif_rec );
3219
3220 /* Check the Procedure's x_return_status */
3221 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3222 FND_MESSAGE.SET_NAME('PV','PV_SET_NOTIF_REC');
3223 FND_MESSAGE.SET_TOKEN('NOTIFICATION_ID',l_notif_id);
3224 FND_MSG_PUB.Add;
3225 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
3226 RAISE FND_API.G_EXC_ERROR;
3227 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3228 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3229 END IF;
3230 END IF;
3231
3232 /* Call the Create_Ge_Party_Notif to insert a record in PV_GE_PARTY_NOTIFICATION */
3233
3234 PV_GE_PARTY_NOTIF_PVT.Create_Ge_Party_Notif (
3235 p_api_version_number => 1.0,
3236 p_init_msg_list => FND_API.G_FALSE ,
3237 p_commit => FND_API.G_FALSE ,
3238 p_validation_level => FND_API.G_VALID_LEVEL_FULL ,
3239 x_return_status => x_return_status ,
3240 x_msg_count => x_msg_count,
3241 x_msg_data => x_msg_data ,
3242 p_pgp_notif_rec => l_pgp_notif_rec,
3243 x_party_notification_id => x_party_notification_id );
3244
3245 /* Check the Procedure's x_return_status */
3246 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3247 FND_MESSAGE.SET_NAME('PV','PV_GE_PARTY_NOTIF_REC');
3248 FND_MESSAGE.SET_TOKEN('NOTIFICATION_ID',l_notif_id);
3249 FND_MSG_PUB.Add;
3250 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
3251 RAISE FND_API.G_EXC_ERROR;
3252 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3253 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3254 END IF;
3255 END IF;
3256
3257 END LOOP;
3258
3259 /* call transaction history log to record this log. */
3260 /* Set the log params for History log. */
3261 l_log_params_tbl(1).param_name := 'NOTIFICATION_TYPE';
3262 l_log_params_tbl(1).param_value := get_Notification_Name(l_notif_type_code);
3263 l_log_params_tbl(2).param_name := 'ITEM_NAME';
3264 l_log_params_tbl(2).param_value := 'MEMBERSHIP_ID';
3265 l_log_params_tbl(3).param_name := 'ITEM_ID';
3266 l_log_params_tbl(3).param_value := p_membership_id;
3267
3268 /* call transaction history log to record this log. */
3269 PVX_Utility_PVT.create_history_log(
3270 p_arc_history_for_entity_code => 'MBRSHIP',
3271 p_history_for_entity_id => p_membership_id,
3272 p_history_category_code => 'PAYMENT',
3273 p_message_code => 'PV_NOTIF_HISTORY_MSG',
3274 p_partner_id => l_prtnr_vndr_relship_id,
3275 p_log_params_tbl => l_log_params_tbl,
3276 x_return_status => x_return_status,
3277 x_msg_count => x_msg_count,
3278 x_msg_data => x_msg_data );
3279
3280 /* Check for x_return_status */
3281 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3282 FND_MESSAGE.SET_NAME('PV','PV_CR_HISTORY_LOG');
3283 FND_MESSAGE.SET_TOKEN('ID',p_membership_id);
3284 FND_MSG_PUB.Add;
3285 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
3286 RAISE FND_API.G_EXC_ERROR;
3287 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3288 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3289 END IF;
3290 END IF;
3291
3292 -- Standard check of p_commit.
3293 IF FND_API.To_Boolean( p_commit ) THEN
3294 COMMIT WORK;
3295 END IF;
3296
3297 -- Standard call to get message count and if count is 1, get message info.
3298 FND_MSG_PUB.Count_And_Get
3299 ( p_count => x_msg_count ,
3300 p_data => x_msg_data
3301 );
3302
3303 END IF; /* End the IF condition for check_Notif_Rule_Active */
3304
3305 EXCEPTION
3306 WHEN FND_API.G_EXC_ERROR THEN
3307 ROLLBACK TO send_mbership_expiry_notif_PVT;
3308 x_return_status := FND_API.G_RET_STS_ERROR ;
3309 FND_MSG_PUB.Count_And_Get
3310 ( p_count => x_msg_count ,
3311 p_data => x_msg_data
3312 );
3313 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3314 ROLLBACK TO send_mbership_expiry_notif_PVT;
3315 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3316 FND_MSG_PUB.Count_And_Get
3317 ( p_count => x_msg_count ,
3318 p_data => x_msg_data
3319 );
3320 WHEN OTHERS THEN
3321 ROLLBACK TO send_mbership_expiry_notif_PVT;
3322 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3323 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3324 THEN
3325 FND_MSG_PUB.Add_Exc_Msg
3326 ( G_FILE_NAME
3327 , G_PKG_NAME
3328 );
3329 END IF;
3330 FND_MSG_PUB.Count_And_Get
3331 ( p_count => x_msg_count
3332 , p_data => x_msg_data
3333 );
3334 END send_mbrship_exp_notif;
3335
3336 /*============================================================================
3337 -- Start of comments
3338 -- API name : send_mbrship_change_notif
3339 -- Type : Private.
3340 -- Function : This API compiles and sends notification to all partner
3341 -- primary users in case of Upgrade/Downgrade/Termination/Invite
3342 -- membership to a partner user.
3343 -- Pre-reqs : None.
3344 -- Parameters :
3345 -- IN : p_api_version IN NUMBER Required
3346 -- p_init_msg_list IN VARCHAR2 Optional
3347 -- Default = FND_API.G_FALSE
3348 -- p_commit IN VARCHAR2 Optional
3349 -- Default = FND_API.G_FALSE
3350 -- p_validation_level IN NUMBER Optional
3351 -- Default = FND_API.G_VALID_LEVEL_FULL
3352 -- p_mbr_upgrade_rec IN NUMBER Required
3353 --
3354 -- OUT : x_return_status OUT VARCHAR2(1)
3355 -- x_msg_count OUT NUMBER
3356 -- x_msg_data OUT VARCHAR2(2000)
3357 --
3358 -- Version : Current version 1.0
3359 -- Initial version 1.0
3360 --
3361 -- Notes : Note text
3362 --
3363 -- End of comments
3364 ============================================================================*/
3365 PROCEDURE send_mbrship_chng_notif (
3366 p_api_version IN NUMBER ,
3367 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
3368 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
3369 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL ,
3370 x_return_status OUT NOCOPY VARCHAR2 ,
3371 x_msg_count OUT NOCOPY NUMBER ,
3372 x_msg_data OUT NOCOPY VARCHAR2 ,
3373 p_mbrship_chng_rec IN PV_PG_NOTIF_UTILITY_PVT.mbrship_chng_rec_type
3374 )
3375 IS
3376
3377 /* Declaration of local variables. */
3378 l_api_name CONSTANT VARCHAR2(30) := 'send_mbrship_change_notif';
3379 l_api_version CONSTANT NUMBER := 1.0;
3380 l_return_status VARCHAR2(1);
3381
3382 l_membership_resource_id NUMBER;
3383 l_partner_program VARCHAR2(240);
3384 l_prtnr_vndr_relship_id NUMBER;
3385 l_enrl_request_id NUMBER;
3386 l_user_id NUMBER;
3387 l_notif_user_id NUMBER;
3388 l_source_name VARCHAR2(360);
3389 l_requestor_name VARCHAR2(360);
3390 l_user_name VARCHAR2(100);
3391 l_vendor_name VARCHAR2(360);
3392 l_partner_comp_name VARCHAR2(360);
3393
3394 /* Declaration of local variables for all the message attributes */
3395 l_enrl_alert VARCHAR2(240);
3396 l_alert_thanks VARCHAR2(240);
3397 l_alert_closing VARCHAR2(240);
3398 l_enrollment_team VARCHAR2(240);
3399
3400 l_item_type VARCHAR2(8) := 'PVXNUTIL';
3401 l_message_name VARCHAR2(20):= 'ALERT_MESSAGE';
3402 -- l_message_name VARCHAR2(20):= 'DOC_MESSAGE';
3403
3404 l_message_hdr VARCHAR2(2000):= NULL;
3405 l_message_body VARCHAR2(4000):= NULL;
3406 l_message_footer VARCHAR2(2000):= NULL;
3407 l_role_name VARCHAR2(100);
3408 l_display_role_name VARCHAR2(240);
3409 l_user_count NUMBER;
3410 l_user_resource_id NUMBER ;
3411 x_user_notify_rec_tbl user_notify_rec_tbl_type;
3412 l_notif_id NUMBER;
3413 x_party_notification_id NUMBER;
3414 l_pgp_notif_rec PV_GE_PARTY_NOTIF_PVT.pgp_notif_rec_type ;
3415 l_arc_notif_for_entity_code VARCHAR2(30) := 'ENRQ';
3416 l_notif_type_code VARCHAR2(30) ;
3417 l_history_category_code VARCHAR2(30) ;
3418 l_log_params_tbl PVX_UTILITY_PVT.log_params_tbl_type;
3419
3420 BEGIN
3421
3422 /* Standard Start of API savepoint */
3423 SAVEPOINT send_mbrship_chng_notif;
3424
3425 /* Standard call to check for call compatibility. */
3426 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
3427 p_api_version ,
3428 l_api_name ,
3429 G_PKG_NAME
3430 ) THEN
3431 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3432 END IF;
3433
3434 /* Initialize message list if p_init_msg_list is set to TRUE. */
3435 IF FND_API.to_Boolean( p_init_msg_list ) THEN
3436 FND_MSG_PUB.initialize;
3437 END IF;
3438
3439 /* Initialize API return status to success */
3440 x_return_status := FND_API.G_RET_STS_SUCCESS;
3441
3442
3443 /* Validate the partner contact resource id and partner id. */
3444 IF ( p_validation_level >= FND_API.G_VALID_LEVEL_FULL)
3445 THEN
3446 /* Debug message */
3447 IF (PV_DEBUG_HIGH_ON) THEN
3448
3449 PVX_UTILITY_PVT.debug_message('Validate_partner_contact_resource_Id');
3450 END IF;
3451
3452 /* Invoke validation procedures */
3453
3454 Validate_Enrl_Requests
3455 ( p_mbrship_chng_rec.id,
3456 'ID',
3457 l_return_status
3458 );
3459
3460 /* If any errors happen abort API. */
3461 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
3462 RAISE FND_API.G_EXC_ERROR;
3463 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3464 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3465 END IF;
3466
3467 /* Debug message */
3468 IF (PV_DEBUG_HIGH_ON) THEN
3469
3470 PVX_UTILITY_PVT.debug_message('Validate_partner_vendor_relship_Id');
3471 END IF;
3472
3473 /* Invoke validation procedures */
3474 Validate_Enrl_Requests
3475 ( p_mbrship_chng_rec.partner_id ,
3476 'PRNTR_VENDOR_RELSHIP_ID',
3477 l_return_status
3478 );
3479
3480 /* If any errors happen abort API. */
3481 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
3482 RAISE FND_API.G_EXC_ERROR;
3483 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3484 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3485 END IF;
3486
3487 END IF;
3488
3489 /* Get the requestor details */
3490 get_requestor_details(
3491 p_req_resource_id => p_mbrship_chng_rec.resource_id,
3492 x_user_id => l_user_id,
3493 x_source_name => l_source_name,
3494 x_user_name => l_user_name,
3495 x_return_status => x_return_status);
3496
3497 /* Check the Procedure's x_return_status */
3498 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3499 FND_MESSAGE.SET_NAME('PV','PV_REQUESTOR_NOT_EXIST');
3500 FND_MESSAGE.SET_TOKEN('REQ_RESOURCE_ID',p_mbrship_chng_rec.resource_id);
3501 FND_MSG_PUB.Add;
3502 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
3503 RAISE FND_API.G_EXC_ERROR;
3504 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3505 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3506 END IF;
3507 END IF;
3508
3509 /* Call 'get_users_list' procedure, to find out, all users from that partner
3510 orgnization of same type, to whome, we have to send upgrade notification. */
3511 get_users_list(
3512 p_partner_id => p_mbrship_chng_rec.partner_id,
3513 x_user_notify_rec_tbl => x_user_notify_rec_tbl ,
3514 x_user_count => l_user_count,
3515 x_return_status => x_return_status ) ;
3516
3517
3518 /* Check the Procedure's x_return_status */
3519 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3520 FND_MESSAGE.SET_NAME('PV','PV_NO_PRIMARY_USER_EXIST');
3521 FND_MESSAGE.SET_TOKEN('PARTNER_ID',l_prtnr_vndr_relship_id);
3522 FND_MSG_PUB.Add;
3523 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
3524 RAISE FND_API.G_EXC_ERROR;
3525 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3526 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3527 END IF;
3528 END IF;
3529
3530
3531 /* Execute in a loop the Send notification process, for all the users,
3532 which we got from the previous step. */
3533
3534 FOR i IN 1 .. l_user_count LOOP
3535 l_user_resource_id := x_user_notify_rec_tbl(i).user_resource_id;
3536 l_notif_user_id := x_user_notify_rec_tbl(i).user_id;
3537
3538 get_resource_role(
3539 p_resource_id => l_user_resource_id,
3540 x_role_name => l_role_name,
3541 x_role_display_name => l_display_role_name,
3542 x_return_status => x_return_status
3543 );
3544
3545 /* Check the Procedure's x_return_status */
3546 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3547 FND_MESSAGE.SET_NAME('PV','PV_RES_ROLE_NOT_EXIST');
3548 FND_MESSAGE.SET_TOKEN('RESOURCE_ID',l_user_resource_id);
3549 FND_MSG_PUB.Add;
3550 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
3551 RAISE FND_API.G_EXC_ERROR;
3552 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3553 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3554 END IF;
3555 END IF;
3556
3557 l_notif_id := WF_Notification.send ( role => l_role_name
3558 , msg_type => l_item_type
3559 , msg_name => l_message_name );
3560
3561 -- WF_Notification.SetAttrText(l_notif_id,'DOCUMENT_ID', 'PVXNUTIL:'||l_notif_id);
3562 /* Set all entity attributes by replacing the supplied parameters. */
3563 WF_Notification.SetAttrText(l_notif_id, 'SUBJECT',p_mbrship_chng_rec.MESSAGE_SUBJ );
3564
3565 WF_Notification.SetAttrText(l_notif_id, 'MESSAGE_HEADER', l_message_hdr);
3566 WF_Notification.SetAttrText(l_notif_id, 'MESSAGE_BODY',p_mbrship_chng_rec.MESSAGE_BODY );
3567 WF_Notification.SetAttrText(l_notif_id, 'MESSAGE_FOOTER', l_message_footer);
3568
3569 WF_NOTIFICATION.Denormalize_Notification(l_notif_id);
3570
3571 /* Set the record for Set_Pgp_Notif API */
3572 Set_Pgp_Notif(
3573 p_notif_id => l_notif_id,
3574 p_object_version => 1,
3575 p_partner_id => p_mbrship_chng_rec.partner_id ,
3576 p_user_id => l_notif_user_id,
3577 p_arc_notif_for_entity_code => l_arc_notif_for_entity_code,
3578 p_notif_for_entity_id => p_mbrship_chng_rec.id,
3579 p_notif_type_code => p_mbrship_chng_rec.NOTIF_TYPE,
3580 x_return_status => x_return_status ,
3581 x_pgp_notif_rec => l_pgp_notif_rec );
3582
3583
3584 /* Check the Procedure's x_return_status */
3585 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3586 FND_MESSAGE.SET_NAME('PV','PV_SET_NOTIF_REC');
3587 FND_MESSAGE.SET_TOKEN('NOTIFICATION_ID',l_notif_id);
3588 FND_MSG_PUB.Add;
3589 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
3590 RAISE FND_API.G_EXC_ERROR;
3591 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3592 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3593 END IF;
3594 END IF;
3595
3596
3597 /* Call the Create_Ge_Party_Notif to insert a record in PV_GE_PARTY_NOTIFICATION */
3598 PV_GE_PARTY_NOTIF_PVT.Create_Ge_Party_Notif (
3599 p_api_version_number => 1.0,
3600 p_init_msg_list => FND_API.G_FALSE ,
3601 p_commit => FND_API.G_FALSE ,
3602 p_validation_level => FND_API.G_VALID_LEVEL_FULL ,
3603 x_return_status => x_return_status ,
3604 x_msg_count => x_msg_count,
3605 x_msg_data => x_msg_data ,
3606 p_pgp_notif_rec => l_pgp_notif_rec,
3607 x_party_notification_id => x_party_notification_id );
3608
3609 /* Check the Procedure's x_return_status */
3610 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3611 FND_MESSAGE.SET_NAME('PV','PV_GE_PARTY_NOTIF_REC');
3612 FND_MESSAGE.SET_TOKEN('NOTIFICATION_ID',l_notif_id);
3613 FND_MSG_PUB.Add;
3614 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
3615 RAISE FND_API.G_EXC_ERROR;
3616 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3617 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3618 END IF;
3619 END IF;
3620
3621 END LOOP;
3622
3623
3624 /* call transaction history log to record this log. */
3625 /* Set the log params for History log. */
3626 l_log_params_tbl(1).param_name := 'NOTIFICATION_TYPE';
3627 l_log_params_tbl(1).param_value := get_Notification_Name(p_mbrship_chng_rec.NOTIF_TYPE);
3628 l_log_params_tbl(2).param_name := 'ITEM_NAME';
3629 l_log_params_tbl(2).param_value := 'ID';
3630 l_log_params_tbl(3).param_name := 'ITEM_ID';
3631 l_log_params_tbl(3).param_value := p_mbrship_chng_rec.id;
3632
3633 /* Select the proper hitory category code based on the Notification Type.*/
3634 IF (p_mbrship_chng_rec.NOTIF_TYPE = 'PG_INVITE') THEN
3635 l_history_category_code := 'INVITE' ;
3636 ELSIF (p_mbrship_chng_rec.NOTIF_TYPE = 'PG_UPGRADE' ) THEN
3637 l_history_category_code := 'UPGRADE' ;
3638 ELSIF (p_mbrship_chng_rec.NOTIF_TYPE = 'PG_DOWNGRADE' ) THEN
3639 l_history_category_code := 'DOWNGRADE' ;
3640 ELSIF (p_mbrship_chng_rec.NOTIF_TYPE = 'PG_TERMINATE' ) THEN
3641 l_history_category_code := 'TERMINATE' ;
3642 END IF;
3643
3644 /* call transaction history log to record this log. */
3645
3646 PVX_Utility_PVT.create_history_log(
3647 p_arc_history_for_entity_code => 'GENERAL', --ENRQ',
3648 p_history_for_entity_id => p_mbrship_chng_rec.partner_id, --p_mbrship_chng_rec.id,
3649 p_history_category_code => l_history_category_code,
3650 p_message_code => 'PV_NOTIF_HISTORY_MSG',
3651 p_partner_id => p_mbrship_chng_rec.partner_id,
3652 p_log_params_tbl => l_log_params_tbl,
3653 x_return_status => x_return_status,
3654 x_msg_count => x_msg_count,
3655 x_msg_data => x_msg_data );
3656
3657 -- Check for x_return_status
3658 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3659 FND_MESSAGE.SET_NAME('PV','PV_CR_HISTORY_LOG');
3660 FND_MESSAGE.SET_TOKEN('ID',p_mbrship_chng_rec.id);
3661 FND_MSG_PUB.Add;
3662 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
3663 RAISE FND_API.G_EXC_ERROR;
3664 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3665 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3666 END IF;
3667 END IF;
3668
3669 /* Set OUT values Standard check of p_commit. */
3670 IF FND_API.To_Boolean( p_commit ) THEN
3671 COMMIT WORK;
3672 END IF;
3673
3674 /* Standard call to get message count and if count is 1, get message info. */
3675 FND_MSG_PUB.Count_And_Get(
3676 p_count => x_msg_count ,
3677 p_data => x_msg_data
3678 );
3679
3680 EXCEPTION
3681 WHEN FND_API.G_EXC_ERROR THEN
3682 ROLLBACK TO send_mbrship_chng_notif;
3683 x_return_status := FND_API.G_RET_STS_ERROR ;
3684 FND_MSG_PUB.Count_And_Get
3685 ( p_count => x_msg_count ,
3686 p_data => x_msg_data
3687 );
3688 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3689 ROLLBACK TO send_mbrship_chng_notif;
3690 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3691 FND_MSG_PUB.Count_And_Get
3692 ( p_count => x_msg_count ,
3693 p_data => x_msg_data
3694 );
3695 WHEN OTHERS THEN
3696 ROLLBACK TO send_mbrship_chng_notif;
3697 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3698 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3699 THEN
3700 FND_MSG_PUB.Add_Exc_Msg
3701 ( G_FILE_NAME
3702 , G_PKG_NAME
3703 );
3704 END IF;
3705 FND_MSG_PUB.Count_And_Get
3706 ( p_count => x_msg_count
3707 , p_data => x_msg_data
3708 );
3709 End send_mbrship_chng_notif;
3710
3711 /*============================================================================
3712 -- Start of Comments
3713 -- PROCEDURE
3714 -- send_ini_rmdr_notif
3715 --
3716 -- PURPOSE
3717 -- This procedure send the initial or a reminder notification.
3718 --
3719 -- Called By
3720 -- NOTES
3721 -- End of Comments
3722 ============================================================================*/
3723 PROCEDURE send_ini_rmdr_notif(
3724 ITEMTYPE IN VARCHAR2,
3725 ITEMKEY IN VARCHAR2,
3726 ACTID IN NUMBER,
3727 FUNCMODE IN VARCHAR2,
3728 RESULTOUT OUT NOCOPY VARCHAR2
3729 )
3730 IS
3731 /* Declare local variables */
3732 l_mbrship_id NUMBER;
3733 l_enrl_req_id NUMBER;
3734 l_notif_type VARCHAR2(30);
3735 l_wait_in_days NUMBER;
3736 l_itemtype VARCHAR2(30) ;
3737 l_itemkey VARCHAR2(240);
3738 x_return_status VARCHAR2(1);
3739 x_msg_count NUMBER;
3740 x_msg_data VARCHAR2(240);
3741
3742 BEGIN
3743 l_itemtype := itemtype;
3744 l_itemkey := itemkey;
3745
3746 IF ( funcmode = 'RUN' ) THEN
3747
3748 /* Get the notification type from the workflow Itemtype 'PVXNUTIL' */
3749 l_notif_type := wf_engine.GetItemAttrText(
3750 ITEMTYPE => l_itemtype,
3751 ITEMKEY => l_itemkey,
3752 ANAME => 'NOTIFICATION_TYPE' );
3753
3754 /* Get wait in Days, applicable for any type of notification. */
3755 l_wait_in_days := wf_engine.GetItemAttrNumber(
3756 ITEMTYPE => l_itemtype,
3757 ITEMKEY => l_itemkey,
3758 ANAME => 'WAIT_PERIOD_IN_DAYS' );
3759
3760 /* Check for Notification Type. It may be 'PG_MEM_EXP' or 'PG_CONTRCT_NRCVD' */
3761 IF (l_notif_type = 'PG_MEM_EXP' ) THEN
3762
3763 /* Get the membership Id from the workflow Itemtype 'PVXNUTIL' */
3764 l_mbrship_id := wf_engine.GetItemAttrNumber(
3765 ITEMTYPE => l_itemtype,
3766 ITEMKEY => l_itemkey,
3767 ANAME => 'MEMBERSHIP_ID' );
3768
3769 /* Call the send_membership_expiry_notif */
3770 PV_PG_NOTIF_UTILITY_PVT.send_mbrship_exp_notif (
3771 p_api_version => 1.0 ,
3772 p_init_msg_list => FND_API.G_FALSE ,
3773 p_commit => FND_API.G_FALSE ,
3774 p_validation_level => FND_API.G_VALID_LEVEL_FULL ,
3775 x_return_status => x_return_status ,
3776 x_msg_count => x_msg_count ,
3777 x_msg_data => x_msg_data ,
3778 p_membership_id => l_mbrship_id );
3779
3780 IF (X_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS ) THEN
3781 FND_MESSAGE.SET_NAME('PV','PV_API_ERROR_MESSAGE');
3782 FND_MESSAGE.SET_TOKEN('PROC_NAME','PVX_Utility_PVT.send_ini_rmdr_notif');
3783 FND_MESSAGE.SET_TOKEN('ITEM_NAME','MEMBERSHIP_ID');
3784 FND_MESSAGE.SET_TOKEN('ITEM_ID',l_mbrship_id);
3785 FND_MSG_PUB.Add;
3786 END IF;
3787
3788
3789 ELSIF (l_notif_type = 'PG_CONTRCT_NRCVD' ) THEN
3790 /* Get the Enrollment Request Id from the workflow Itemtype 'PVXNUTIL' */
3791 l_enrl_req_id := wf_engine.GetItemAttrNumber(
3792 ITEMTYPE => l_itemtype,
3793 ITEMKEY => l_itemkey,
3794 ANAME => 'ENROLLMENT_REQUEST_ID' );
3795
3796 /* Call the send_cntrct_notrcvd_notif */
3797 PV_PG_NOTIF_UTILITY_PVT.send_cntrct_notrcvd_notif (
3798 p_api_version => 1.0 ,
3799 p_init_msg_list => FND_API.G_FALSE ,
3800 p_commit => FND_API.G_FALSE ,
3801 p_validation_level => FND_API.G_VALID_LEVEL_FULL ,
3802 x_return_status => x_return_status ,
3803 x_msg_count => x_msg_count ,
3804 x_msg_data => x_msg_data ,
3805 p_enrl_request_id => l_enrl_req_id );
3806
3807 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
3808 FND_MESSAGE.SET_NAME('PV','PV_API_ERROR_MESSAGE');
3809 FND_MESSAGE.SET_TOKEN('PROC_NAME','PVX_Utility_PVT.send_ini_rmdr_notif');
3810 FND_MESSAGE.SET_TOKEN('ITEM_NAME','ENRL_REQUEST_ID');
3811 FND_MESSAGE.SET_TOKEN('ITEM_ID',l_enrl_req_id);
3812 FND_MSG_PUB.Add;
3813 END IF;
3814
3815 END IF;
3816
3817 END IF;
3818 RESULTOUT := 'COMPLETE:Y';
3819 return;
3820 EXCEPTION
3821 -- The line below records this function call in the error system
3822 -- in the case of an exception.
3823 WHEN OTHERS THEN
3824 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3825 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,'send_ini_rmdr_notif');
3826 END IF;
3827 wf_core.context(G_PKG_NAME,'send_ini_rmdr_notif', itemtype,itemkey,to_char(actid),funcmode);
3828
3829 END send_ini_rmdr_notif;
3830
3831 /*============================================================================
3832 -- Start of Comments
3833 -- PROCEDURE
3834 -- check_for_rmdr_notif
3835 --
3836 -- PURPOSE
3837 -- This procedure checks, whether, is there any need to send a reminder notification
3838 --
3839 -- Called By
3840 -- NOTES
3841 -- End of Comments
3842 ============================================================================*/
3843 PROCEDURE check_for_rmdr_notif(
3844 ITEMTYPE IN VARCHAR2,
3845 ITEMKEY IN VARCHAR2,
3846 ACTID IN NUMBER,
3847 FUNCMODE IN VARCHAR2,
3848 RESULTOUT OUT NOCOPY VARCHAR2
3849 )
3850 IS
3851 /* Cursor declaration to check the remainder flag for a given membership_id,
3852 whether we have to send any reminder or not */
3853 CURSOR c_Check_Memexp_Rmdr_Flg (cv_membership_id NUMBER) IS
3854 SELECT 'Y'
3855 FROM pv_pg_memberships mmbr
3856 WHERE mmbr.membership_id = cv_membership_id
3857 AND NOT EXISTS
3858 ( SELECT 1
3859 FROM pv_pg_mmbr_transitions trans,
3860 pv_pg_memberships mmbr_future
3861 WHERE trans.from_membership_id = mmbr.membership_id
3862 AND trans.to_membership_id = mmbr_future.membership_id
3863 AND mmbr_future.membership_status_code = 'FUTURE' );
3864
3865 /* Cursor declaration to check the remainder flag for a given enrl_request_id,
3866 whether we have to send any reminder notification or not in case of 'Signed
3867 Contract not received' notification */
3868 CURSOR c_Check_Cnrcvd_Rmdr_Flg (cv_enrl_request_id NUMBER) IS
3869 SELECT 'Y'
3870 FROM pv_pg_enrl_requests enrq
3871 WHERE enrq.enrl_request_id = cv_enrl_request_id
3872 AND enrq.contract_status_code = 'AWAITING_FAX_OR_MAIL';
3873
3874 /* Declare local variables */
3875 l_mbrship_id NUMBER;
3876 l_enrl_req_id NUMBER;
3877 l_notif_type VARCHAR2(30);
3878 l_wait_in_days NUMBER;
3879 l_itemtype VARCHAR2(30) ;
3880 l_itemkey VARCHAR2(240);
3881 l_rmdr_flag VARCHAR2(1) := 'N';
3882 x_return_status VARCHAR2(1);
3883 x_msg_count NUMBER;
3884 x_msg_data VARCHAR2(240);
3885 l_end_date DATE;
3886 l_expiry_days NUMBER;
3887 l_date_format VARCHAR2(80);
3888 BEGIN
3889 l_itemtype := itemtype;
3890 l_itemkey := itemkey;
3891
3892 IF ( funcmode = 'RUN' ) THEN
3893
3894 /* Get the WAIT_PERIOD_IN_DAYS attribute from the workflow Itemtype 'PVXNUTIL' */
3895 l_wait_in_days := wf_engine.GetItemAttrText(
3896 ITEMTYPE => l_itemtype,
3897 ITEMKEY => l_itemkey,
3898 ANAME => 'WAIT_PERIOD_IN_DAYS' );
3899 /* Check for WAIT_PERIOD_IN_DAYS */
3900 IF ( l_wait_in_days > 0)
3901 THEN
3902 /* Get the notification type from the workflow Itemtype 'PVXNUTIL' */
3903 l_notif_type := wf_engine.GetItemAttrText(
3904 ITEMTYPE => l_itemtype,
3905 ITEMKEY => l_itemkey,
3906 ANAME => 'NOTIFICATION_TYPE' );
3907
3908 /* Check for Notification Type. It may be 'PG_MEM_EXP' or 'PG_CONTRCT_NRCVD' */
3909 IF (l_notif_type = 'PG_MEM_EXP' ) THEN
3910 /* Get the membership Id from the workflow Itemtype 'PVXNUTIL' */
3911 l_mbrship_id := wf_engine.GetItemAttrNumber(
3912 ITEMTYPE => l_itemtype,
3913 ITEMKEY => l_itemkey,
3914 ANAME => 'MEMBERSHIP_ID' );
3915
3916 OPEN c_Check_Memexp_Rmdr_Flg(l_mbrship_id);
3917 FETCH c_Check_Memexp_Rmdr_Flg INTO l_rmdr_flag;
3918
3919 IF ( l_rmdr_flag = 'Y' ) THEN
3920 l_end_date:= wf_engine.GetItemAttrDate(
3921 ITEMTYPE => l_itemtype,
3922 ITEMKEY => l_itemkey,
3923 ANAME => 'END_DATE' );
3924 --l_date_format := 'DD-MON-YYYY';
3925 l_expiry_days := to_number(trunc( l_end_date ) - sysdate );
3926 IF l_expiry_days <0 THEN
3927 l_expiry_days := 0;
3928 END IF;
3929 wf_engine.setItemAttrText
3930 (
3931 ITEMTYPE => l_itemtype
3932 , ITEMKEY => l_itemkey
3933 , ANAME => 'MBRSHIP_EXPIRY_IN_DAYS'
3934 , AVALUE => to_char(l_expiry_days)
3935 );
3936
3937 RESULTOUT := 'COMPLETE:Y';
3938 return;
3939 END IF; /* End if for l_rmdr_flag check */
3940
3941 ELSIF (l_notif_type = 'PG_CONTRCT_NRCVD' ) THEN
3942 /* Get the Enrollment Request Id from the workflow Itemtype 'PVXNUTIL' */
3943 l_enrl_req_id := wf_engine.GetItemAttrNumber(
3944 ITEMTYPE => l_itemtype,
3945 ITEMKEY => l_itemkey,
3946 ANAME => 'ENROLLMENT_REQUEST_ID' );
3947
3948 OPEN c_Check_Cnrcvd_Rmdr_Flg(l_enrl_req_id);
3949 FETCH c_Check_Cnrcvd_Rmdr_Flg INTO l_rmdr_flag;
3950
3951 IF ( l_rmdr_flag = 'Y' ) THEN
3952 RESULTOUT := 'COMPLETE:Y';
3953 return;
3954 END IF; /* End if for l_rmdr_flag check */
3955 END IF; /* End IF for l_notif_type Check */
3956 END IF ; /* End if for l_wait_in_days Check */
3957 END IF; /* End If for FUNMODE Check */
3958
3959 RESULTOUT := 'COMPLETE:N';
3960
3961 END check_for_rmdr_notif;
3962
3963 /*============================================================================
3964 -- Start of Comments
3965 -- PROCEDURE
3966 -- Prtnr_Prgm_Enrl_notif
3967 --
3968 -- PURPOSE
3969 -- This procedure is called from the Concurrent Request program for sending the
3970 -- Membership Expiry and Signed Contract not received notifications.
3971 --
3972 -- Called By
3973 -- NOTES
3974 -- End of Comments
3975 ============================================================================*/
3976 PROCEDURE Prtnr_Prgm_Enrl_notif(
3977 ERRBUF OUT NOCOPY VARCHAR2,
3978 RETCODE OUT NOCOPY VARCHAR2 )
3979 IS
3980 -- Get all the memberships for which initial expiry notification is to be sent(exclude
3981 -- the ones FOR which already notifications are sent)
3982
3983 CURSOR c_get_memberships IS
3984 SELECT mmbr.membership_id,
3985 mmbr.enrl_request_id,
3986 mmbr.partner_id,
3987 (notif_rule.repeat_freq_value * DECODE(notif_rule.repeat_freq_unit, 'PV_DAYS',1,'PV_WEEK', 7,'PV_MONTH', 30)) wait_time_in_days
3988 FROM pv_pg_memberships mmbr,
3989 pv_ge_notif_rules_b notif_rule
3990 WHERE mmbr.membership_status_code = 'ACTIVE'
3991 AND trunc(mmbr.original_end_date - sysdate) < notif_rule.send_notif_before_value *
3992 DECODE(notif_rule.send_notif_before_unit, 'PV_DAYS',1,'PV_WEEK', 7,
3993 'PV_MONTH', 30)
3994 AND mmbr.program_id = notif_rule.notif_for_entity_id
3995 AND notif_rule.arc_notif_for_entity_code = 'PRGM'
3996 AND notif_rule.active_flag = 'Y'
3997 AND notif_rule.notif_type_code = 'PG_MEM_EXP'
3998 AND NOT EXISTS
3999 ( SELECT 1
4000 FROM pv_pg_mmbr_transitions trans,
4001 pv_pg_memberships mmbr_future
4002 WHERE trans.from_membership_id = mmbr.membership_id
4003 AND trans.to_membership_id = mmbr_future.membership_id
4004 AND mmbr_future.membership_status_code = 'FUTURE'
4005 )
4006 AND NOT EXISTS
4007 ( SELECT 1
4008 FROM pv_ge_party_notifications sent_notif
4009 WHERE sent_notif.ARC_NOTIF_FOR_ENTITY_CODE = 'ENRQ'
4010 AND sent_notif.NOTIF_FOR_ENTITY_ID = mmbr.enrl_request_id
4011 AND sent_notif.notif_type_code = notif_rule.notif_type_code
4012 AND sent_notif.partner_id = mmbr.partner_id
4013 );
4014
4015 /* Get all the enrollment requests for which initial signed contract not
4016 received notification is to be sent(exclude the ones FOR which already
4017 notifications are sent)*/
4018 CURSOR c_get_enrollment_requests IS
4019 SELECT enrq.enrl_request_id,
4020 enrq.partner_id,
4021 notif_rule.repeat_freq_value*
4022 DECODE(notif_rule.repeat_freq_unit, 'PV_DAYS',1,
4023 'PV_WEEK', 7,
4024 'PV_MONTH', 30) "wait_time_in_days"
4025 FROM pv_pg_enrl_requests enrq,
4026 pv_ge_notif_rules_b notif_rule
4027 WHERE enrq.contract_status_code = 'AWAITING_FAX_OR_MAIL'
4028 AND enrq.request_status_code in ('AWAITING_APPROVAL', 'APPROVED')
4029 AND (enrq.request_submission_date -sysdate) < notif_rule.send_notif_after_value *
4030 DECODE(notif_rule.send_notif_after_unit, 'PV_DAYS',1,
4031 'PV_WEEK', 7,
4032 'PV_MONTH', 30)
4033 AND enrq.program_id = notif_rule.notif_for_entity_id
4034 AND notif_rule.arc_notif_for_entity_code = 'PRGM'
4035 AND notif_rule.active_flag = 'Y'
4036 AND notif_rule.notif_type_code = 'PG_CONTRCT_NRCVD'
4037 AND NOT EXISTS
4038 ( SELECT 1
4039 FROM pv_ge_party_notifications sent_notif
4040 WHERE sent_notif.ARC_NOTIF_FOR_ENTITY_CODE = 'ENRQ'
4041 AND sent_notif. NOTIF_FOR_ENTITY_ID = enrq.enrl_request_id
4042 AND sent_notif.notif_type_code = notif_rule.notif_type_code
4043 AND sent_notif.partner_id = enrq.partner_id);
4044
4045 /* Declaration of Local variables. */
4046
4047 l_mbrship_id NUMBER;
4048 l_enrl_request_id NUMBER;
4049 l_wait_time_in_days NUMBER;
4050 l_partner_id NUMBER;
4051 l_itemtype VARCHAR2(240) := 'PVXNUTIL';
4052 l_itemkey VARCHAR2(240);
4053
4054 p_debug_mode VARCHAR2(1);
4055 l_status BOOLEAN;
4056 x_return_status VARCHAR2(1);
4057 x_msg_count NUMBER;
4058 x_msg_data VARCHAR2(240);
4059 p_api_version_number CONSTANT NUMBER := 1.0;
4060 p_init_msg_list VARCHAR2(100) := FND_API.G_FALSE;
4061 p_commit VARCHAR2(100) := FND_API.G_FALSE;
4062 p_validation_level NUMBER := FND_API.G_VALID_LEVEL_FULL;
4063
4064 BEGIN
4065
4066
4067 /* *** Send membership expiry notification Start *** */
4068 Write_log (1, '*** Send membership expiry notification Start ***');
4069
4070 /* Process all the Membership expiry records selected in c_get_memberships Cursor */
4071
4072 OPEN c_get_memberships;
4073 LOOP
4074 FETCH c_get_memberships INTO l_mbrship_id,l_enrl_request_id,l_partner_id, l_wait_time_in_days ;
4075
4076 IF ( c_get_memberships%NOTFOUND) THEN
4077 Close c_get_memberships;
4078 exit;
4079 END IF;
4080 PV_PG_NOTIF_UTILITY_PVT.Send_Workflow_Notification
4081 (
4082 p_api_version_number => p_api_version_number
4083 , p_init_msg_list => p_init_msg_list
4084 , p_commit => p_commit
4085 , p_validation_level => p_validation_level
4086 , p_context_id => l_partner_id
4087 , p_context_code => 'PARTNER'
4088 , p_target_ctgry => 'PARTNER'
4089 , p_target_ctgry_pt_id => l_partner_id
4090 , p_notif_event_code => 'PG_MEM_EXP'
4091 , p_entity_id => l_enrl_request_id
4092 , p_entity_code => 'ENRQ'
4093 , p_wait_time => l_wait_time_in_days
4094 , x_return_status => x_return_status
4095 , x_msg_count => x_msg_count
4096 , x_msg_data => x_msg_data
4097 );
4098
4099 END LOOP;
4100
4101 IF c_get_memberships%ISOPEN THEN
4102 CLOSE c_get_memberships;
4103 END IF;
4104
4105 /* *** Send membership expiry notification End *** */
4106
4107 /* *** Send Signed Contract Copy not received notification Start *** */
4108 OPEN c_get_enrollment_requests;
4109 LOOP
4110 FETCH c_get_enrollment_requests INTO l_enrl_request_id, l_partner_id,l_wait_time_in_days ;
4111
4112 IF ( c_get_enrollment_requests%NOTFOUND) THEN
4113 Close c_get_enrollment_requests;
4114 exit;
4115 END IF;
4116 PV_PG_NOTIF_UTILITY_PVT.Send_Workflow_Notification
4117 (
4118 p_api_version_number => p_api_version_number
4119 , p_init_msg_list => p_init_msg_list
4120 , p_commit => p_commit
4121 , p_validation_level => p_validation_level
4122 , p_context_id => l_partner_id
4123 , p_context_code => 'PARTNER'
4124 , p_target_ctgry => 'PARTNER'
4125 , p_target_ctgry_pt_id => l_partner_id
4126 , p_notif_event_code => 'PG_CONTRCT_NRCVD'
4127 , p_entity_id => l_enrl_request_id
4128 , p_entity_code => 'ENRQ'
4129 , p_wait_time => l_wait_time_in_days
4130 , x_return_status => x_return_status
4131 , x_msg_count => x_msg_count
4132 , x_msg_data => x_msg_data
4133 );
4134
4135
4136 END LOOP;
4137
4138 IF c_get_enrollment_requests%ISOPEN THEN
4139 CLOSE c_get_enrollment_requests;
4140 END IF;
4141
4142 /* *** Send Signed Contract Copy not received notification End *** */
4143 COMMIT;
4144
4145 EXCEPTION
4146 WHEN FND_API.G_EXC_ERROR THEN
4147 ERRBUF := ERRBUF || sqlerrm;
4148 RETCODE := FND_API.G_RET_STS_ERROR;
4149 ROLLBACK ;
4150 l_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR', SQLERRM);
4151 Write_log (1, 'Error in PV_PG_NOTIF_UTILITY_PVT.Prtnr_Prgm_Enrl_notif');
4152 Write_log (1, 'SQLCODE ' || to_char(SQLCODE) ||
4153 ' SQLERRM ' || substr(SQLERRM, 1, 100));
4154 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4155 ERRBUF := ERRBUF||sqlerrm;
4156 RETCODE := FND_API.G_RET_STS_UNEXP_ERROR;
4157 ROLLBACK ;
4158 l_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR', SQLERRM);
4159 Write_log (1, 'Unexpected error in Error in PV_PG_NOTIF_UTILITY_PVT.Prtnr_Prgm_Enrl_notif');
4160 Write_log (1, 'SQLCODE ' || to_char(SQLCODE) ||
4161 ' SQLERRM ' || substr(SQLERRM, 1, 100));
4162 WHEN OTHERS THEN
4163 ERRBUF := ERRBUF||sqlerrm;
4164 RETCODE := '2';
4165 ROLLBACK ;
4166 l_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR', SQLERRM);
4167 Write_log (1, 'Other error in Error in PV_PG_NOTIF_UTILITY_PVT.Prtnr_Prgm_Enrl_notif');
4168 Write_log (1, 'SQLCODE ' || to_char(SQLCODE) ||
4169 ' SQLERRM ' || substr(SQLERRM, 1, 100));
4170 END Prtnr_Prgm_Enrl_notif;
4171
4172 /*============================================================================
4173 -- Start of Comments
4174 -- PROCEDURE
4175 -- Expire_Memberships
4176 --
4177 -- PURPOSE
4178 -- This procedure updates the membership status either to EXPIRE or RENEW based
4179 -- on the status criteria.
4180 --
4181 -- Called By
4182 -- NOTES
4183 -- End of Comments
4184 ============================================================================*/
4185 PROCEDURE Expire_Memberships(
4186 ERRBUF OUT NOCOPY VARCHAR2,
4187 RETCODE OUT NOCOPY VARCHAR2 )
4188 IS
4189 /* Concurrent Program for expiring or renewing the memberships:
4190 Get all the memberships to be expired - for which there are no
4191 early renewals or renewals. */
4192 CURSOR c_get_expired_memberships IS
4193 SELECT mmbr.membership_id membership_id
4194 , mmbr.partner_id
4195 , mmbr.object_version_number
4196 FROM pv_pg_memberships mmbr
4197 WHERE mmbr.membership_status_code = 'ACTIVE'
4198 AND trunc(SYSDATE - mmbr.original_end_date) >= 1
4199 AND NOT EXISTS
4200 ( SELECT 1
4201 FROM pv_pg_mmbr_transitions trans,
4202 pv_pg_memberships mmbr_future
4203 WHERE trans.from_membership_id = mmbr.membership_id
4204 AND trans.to_membership_id = mmbr_future.membership_id
4205 AND mmbr_future.membership_status_code = 'FUTURE' ) ;
4206
4207
4208 CURSOR c_get_status(mmbr_id NUMBER) IS
4209 SELECT membership_status_code
4210 FROM pv_pg_memberships
4211 WHERE membership_id=mmbr_id;
4212
4213 -- Get all the memberships to be renewed for early renewals cases.
4214 CURSOR c_get_renew_memberships IS
4215 SELECT mmbr.membership_id current_membership_id,
4216 mmbr_future.membership_id future_membership_id,
4217 mmbr.partner_id,
4218 mmbr.object_version_number,
4219 mmbr_future.object_version_number future_memb_obj_ver_no
4220 FROM pv_pg_memberships mmbr,
4221 pv_pg_memberships mmbr_future,
4222 pv_pg_mmbr_transitions trans
4223 WHERE mmbr.membership_status_code = 'ACTIVE'
4224 --AND trunc(SYSDATE - mmbr.original_end_date) >= 1
4225 AND mmbr.original_end_date <= trunc(SYSDATE -1 )
4226 AND trans.from_membership_id = mmbr.membership_id
4227 AND trans.to_membership_id = mmbr_future.membership_id
4228 AND mmbr_future.membership_status_code = 'FUTURE';
4229
4230 CURSOR c_getptrprgm(memb_id NUMBER) IS
4231 SELECT distinct party.party_name, prgm.program_name
4232 FROM hz_parties party
4233 , pv_partner_profiles prof
4234 , pv_pg_memberships memb
4235 , pv_partner_program_vl prgm
4236 WHERE prof.status = 'A'
4237 AND prof.partner_party_id = party.party_id
4238 AND memb.partner_id = prof.partner_id
4239 AND memb.membership_id = memb_id
4240 AND memb.program_id=prgm.program_id;
4241
4242 /* Declaration of local variables. */
4243 l_return_status VARCHAR2(1):= FND_API.G_RET_STS_SUCCESS;
4244 l_status BOOLEAN;
4245 l_log_params_tbl PVX_UTILITY_PVT.log_params_tbl_type;
4246 l_membership_id NUMBER;
4247 l_obj_version_no NUMBER;
4248 l_current_mbrship_id NUMBER;
4249 l_future_mbrship_id NUMBER;
4250 x_return_status VARCHAR2(1):= FND_API.G_RET_STS_SUCCESS;
4251 x_msg_count NUMBER;
4252 x_msg_data VARCHAR2(4000);
4253 l_memb_rec PV_Pg_Memberships_PVT.memb_rec_type;
4254 l_membership_status VARCHAR2(30);
4255 l_partner_name VARCHAR2(360);
4256 l_program_name VARCHAR2(60);
4257 l_index number;
4258
4259 BEGIN
4260
4261 /* Standard Start of API savepoint */
4262 SAVEPOINT Expire_Memberships;
4263
4264 /* Logic to update the membership status to EXPIRE for all the EXPIRED members */
4265 Write_log (1, 'Updating the Membership Status to EXPIRED -');
4266 FOR l_get_expire_memberships_rec IN c_get_expired_memberships
4267 LOOP
4268 /* call update table handler for pv_pg_memberships by passing
4269 membership_status_code = 'EXPIRED', actual_end_date as sysdate */
4270 /*l_memb_rec.membership_id := l_get_expire_memberships_rec.membership_id;
4271 l_memb_rec.membership_status_code := 'EXPIRED';
4272 l_memb_rec.actual_end_date := sysdate;
4273 l_memb_rec.object_version_number := l_get_expire_memberships_rec.object_version_number;
4274 Write_log (1,'Membership Id :'||l_memb_rec.membership_id);
4275 PV_Pg_Memberships_PVT.Update_Pg_Memberships(
4276 p_api_version_number => 1.0,
4277 p_init_msg_list => FND_API.G_FALSE,
4278 p_commit => FND_API.G_FALSE,
4279 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
4280 x_return_status => x_return_status,
4281 x_msg_count => x_msg_count ,
4282 x_msg_data => x_msg_data ,
4283 p_memb_rec => l_memb_rec );
4284 */
4285 /** making changes for 11.5.10 by pukken. since we need to take care of prereqs and subsidiary memberships
4286 call terminate membership api with event code as EXPIRED
4287 Before calling the api, check for membership status once again,
4288 because of the first terminate_membership call in this loop
4289 could have expired the next membership id in the loop. so query for the status again
4290 */
4291 l_membership_status := null;
4292 OPEN c_get_status( l_get_expire_memberships_rec.membership_id ) ;
4293 FETCH c_get_status INTO l_membership_status;
4294 CLOSE c_get_status;
4295 IF l_membership_status='ACTIVE' THEN
4296 Write_log (1,'Before calling expire api for Membership Id :'|| l_get_expire_memberships_rec.membership_id);
4297 SAVEPOINT Terminate_membership;
4298 PV_Pg_Memberships_PVT.Terminate_membership
4299 (
4300 p_api_version_number =>1.0
4301 , p_init_msg_list => FND_API.g_true
4302 , p_commit => FND_API.G_FALSE
4303 , p_validation_level => FND_API.g_valid_level_full
4304 , p_membership_id => l_get_expire_memberships_rec.membership_id
4305 , p_event_code => 'EXPIRED'
4306 , p_memb_type => NULL
4307 , p_status_reason_code => 'EXPIRED_BY_SYSTEM'
4308 , p_comments => NULL
4309 , x_return_status => x_return_status
4310 , x_msg_count => x_msg_count
4311 , x_msg_data => x_msg_data
4312 );
4313
4314 /* Check for x_return_status */
4315
4316 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
4317 Write_log (1,'Expired Membership id'|| l_get_expire_memberships_rec.membership_id || 'with Status as :' ||x_return_status);
4318 ELSE
4319 OPEN c_getptrprgm(l_get_expire_memberships_rec.membership_id);
4320 FETCH c_getptrprgm INTO l_partner_name,l_program_name;
4321 CLOSE c_getptrprgm;
4322 Write_log (1,'Error expiring membership in program:'||l_program_name||'for partner: ' || l_partner_name|| 'with Status as :'||x_return_status || 'with error message as: ' || x_msg_data );
4323
4324 for I in 1 .. x_msg_count LOOP
4325 fnd_msg_pub.Get
4326 ( p_msg_index => FND_MSG_PUB.G_NEXT
4327 ,p_encoded => FND_API.G_FALSE
4328 ,p_data => x_msg_data
4329 ,p_msg_index_out => l_index
4330 );
4331 Write_log (1,x_msg_data);
4332 end loop;
4333 Write_log (1,'End of error stack for expiring membership in the program'|| l_program_name|| 'for the partner' || l_partner_name );
4334 ROLLBACK to Terminate_membership;
4335 END IF;
4336
4337 END IF;
4338 END LOOP;
4339 Write_log (1, 'Finished Updating the Membership Status to EXPIRED');
4340
4341 x_return_status := FND_API.G_RET_STS_SUCCESS;
4342 /* Pick all the memberships to be renewed. */
4343 Write_log (1, 'Updating the Membership Status to RENEW for following members -');
4344
4345 FOR l_get_renew_memberships_rec IN c_get_renew_memberships LOOP
4346 -- call update API for pv_pg_memberships by passing
4347 -- membership_status_code = 'RENEWED', actual_end_date as sysdate
4348 -- for membership_id = l_get_renew_memberships_rec.current_membership_id
4349 l_memb_rec.membership_id := l_get_renew_memberships_rec.current_membership_id;
4350 l_memb_rec.membership_status_code := 'RENEWED';
4351 l_memb_rec.actual_end_date := sysdate;
4352 l_memb_rec.object_version_number := l_get_renew_memberships_rec.object_version_number;
4353
4354 Write_log (1,'Before Changing to Renewed status for Membership Id :'||l_memb_rec.membership_id);
4355 SAVEPOINT renew_membership;
4356
4357 PV_Pg_Memberships_PVT.Update_Pg_Memberships(
4358 p_api_version_number => 1.0,
4359 p_init_msg_list => FND_API.G_TRUE,
4360 p_commit => FND_API.G_FALSE,
4361 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
4362 x_return_status => x_return_status,
4363 x_msg_count => x_msg_count ,
4364 x_msg_data => x_msg_data ,
4365 p_memb_rec => l_memb_rec );
4366
4367 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
4368 Write_log (1,'Renewed Membership Status : '||x_return_status);
4369 ELSE
4370 OPEN c_getptrprgm(l_memb_rec.membership_id);
4371 FETCH c_getptrprgm INTO l_partner_name,l_program_name;
4372 CLOSE c_getptrprgm;
4373 Write_log (1,'Error in renewing membership in program :'|| l_program_name|| ' for partner: ' || l_partner_name || ' with status as: ' || x_return_status || ' and error message is: ' || x_msg_data );
4374 for I in 1 .. x_msg_count LOOP
4375 fnd_msg_pub.Get
4376 ( p_msg_index => FND_MSG_PUB.G_NEXT
4377 ,p_encoded => FND_API.G_FALSE
4378 ,p_data => x_msg_data
4379 ,p_msg_index_out => l_index
4380 );
4381 Write_log (1,x_msg_data);
4382 end loop;
4383 Write_log (1,'End of error stack for renewing membership in the program: '|| l_program_name|| ' for the partner: ' || l_partner_name );
4384 ROLLBACK to renew_membership;
4385 END IF;
4386
4387
4388
4389 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
4390 -- call update API for pv_pg_memberships by passing
4391 -- membership_status_code = 'ACTIVE',
4392 -- for membership_id = l_get_renew_memberships_rec.future_membership_id
4393 l_memb_rec.membership_id := l_get_renew_memberships_rec.future_membership_id;
4394 l_memb_rec.membership_status_code := 'ACTIVE';
4395 l_memb_rec.object_version_number := l_get_renew_memberships_rec.future_memb_obj_ver_no;
4396 Write_log (1,'Before Changing to Active status for Membership Id :'||l_memb_rec.membership_id);
4397 PV_Pg_Memberships_PVT.Update_Pg_Memberships(
4398 p_api_version_number => 1.0,
4399 p_init_msg_list => FND_API.G_FALSE,
4400 p_commit => FND_API.G_FALSE,
4401 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
4402 x_return_status => x_return_status,
4403 x_msg_count => x_msg_count ,
4404 x_msg_data => x_msg_data ,
4405 p_memb_rec => l_memb_rec );
4406
4407 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
4408 Write_log (1,'Activated Membership Status :'||x_return_status);
4409 ELSE
4410 OPEN c_getptrprgm(l_memb_rec.membership_id);
4411 FETCH c_getptrprgm INTO l_partner_name,l_program_name;
4412 CLOSE c_getptrprgm;
4413 Write_log (1,'Error activating membership in program '|| l_program_name|| ' for partner ' || l_partner_name ||' with status as ' || x_return_status || ' and the error message is ' || x_msg_data );
4414 for I in 1 .. x_msg_count LOOP
4415 fnd_msg_pub.Get
4416 ( p_msg_index => FND_MSG_PUB.G_NEXT
4417 ,p_encoded => FND_API.G_FALSE
4418 ,p_data => x_msg_data
4419 ,p_msg_index_out => l_index
4420 );
4421 Write_log (1,x_msg_data);
4422 end loop;
4423 Write_log (1,'End of error stack for activating membership in the program: '|| l_program_name|| ' for the partner: ' || l_partner_name );
4424 ROLLBACK to renew_membership;
4425 END IF;
4426 END IF;
4427
4428
4429
4430 -- We may need to call the activate_contract_api, here. -- will let you know very soon which API needs to go here.
4431
4432 -- call transaction history log to record this log.
4433 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
4434 /* Set the log params for History log. */
4435 l_log_params_tbl(1).param_name := 'MBRSHIP_RENEW_DT';
4436 l_log_params_tbl(1).param_value := to_char(sysdate);
4437 l_log_params_tbl(2).param_name := 'CURRENT_MBRSHIP_ID';
4438 l_log_params_tbl(2).param_value := l_get_renew_memberships_rec.current_membership_id;
4439 l_log_params_tbl(3).param_name := 'FUTURE_MBRSHIP_ID';
4440 l_log_params_tbl(3).param_value := l_get_renew_memberships_rec.future_membership_id;
4441
4442 /* call transaction history log to record this log. */
4443 Write_log (1,'Before Creating History Log for '|| l_get_renew_memberships_rec.current_membership_id );
4444 PVX_UTILITY_PVT.create_history_log
4445 (
4446 p_arc_history_for_entity_code => 'MEMBERSHIP'
4447 , p_history_for_entity_id => l_get_renew_memberships_rec.current_membership_id
4448 , p_history_category_code => 'ENROLLMENT'
4449 , p_message_code => 'PV_MEMBERSHIP_RENEWED'
4450 , p_comments => null
4451 , p_partner_id => l_get_renew_memberships_rec.partner_id
4452 , p_access_level_flag => 'P'
4453 , p_interaction_level => PVX_Utility_PVT.G_INTERACTION_LEVEL_50
4454 , p_log_params_tbl => l_log_params_tbl
4455 , p_init_msg_list => FND_API.g_false
4456 , p_commit => FND_API.G_FALSE
4457 , x_return_status => x_return_status
4458 , x_msg_count => x_msg_count
4459 , x_msg_data => x_msg_data
4460 );
4461
4462 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
4463 Write_log (1,'Creating History Log '||x_return_status);
4464 ELSE
4465 OPEN c_getptrprgm(l_get_renew_memberships_rec.current_membership_id);
4466 FETCH c_getptrprgm INTO l_partner_name,l_program_name;
4467 CLOSE c_getptrprgm;
4468 Write_log (1,'Error Creating Enrollment Log for program: '|| l_program_name|| ' for partner ' || l_partner_name || ' with status as ' || x_return_status || ' and error message is ' || x_msg_data );
4469 for I in 1 .. x_msg_count LOOP
4470 fnd_msg_pub.Get
4471 ( p_msg_index => FND_MSG_PUB.G_NEXT
4472 ,p_encoded => FND_API.G_FALSE
4473 ,p_data => x_msg_data
4474 ,p_msg_index_out => l_index
4475 );
4476 Write_log (1,x_msg_data);
4477 end loop;
4478 Write_log (1,'End of error stack in creating history log' );
4479 ROLLBACK to renew_membership;
4480 END IF;
4481 END IF;
4482 END LOOP;
4483
4484 EXCEPTION
4485 WHEN FND_API.G_EXC_ERROR THEN
4486 ERRBUF := ERRBUF || sqlerrm;
4487 RETCODE := FND_API.G_RET_STS_ERROR;
4488 ROLLBACK TO Expire_Memberships;
4489 l_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR', SQLERRM);
4490 Write_log (1, 'Error in Updating Membership Status(PV_PG_NOTIF_UTILITY_PVT.Expire_Memberships)');
4491 Write_log (1, 'SQLCODE ' || to_char(SQLCODE) ||
4492 ' SQLERRM ' || substr(SQLERRM, 1, 100));
4493 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4494 ERRBUF := ERRBUF||sqlerrm;
4495 RETCODE := FND_API.G_RET_STS_UNEXP_ERROR;
4496 ROLLBACK TO Expire_Memberships;
4497 l_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR', SQLERRM);
4498 Write_log (1, 'Unexpected error in in Updating Membership Status(PV_PG_NOTIF_UTILITY_PVT.Expire_Memberships)');
4499 Write_log (1, 'SQLCODE ' || to_char(SQLCODE) ||
4500 ' SQLERRM ' || substr(SQLERRM, 1, 100));
4501 WHEN OTHERS THEN
4502 ERRBUF := ERRBUF||sqlerrm;
4503 RETCODE := '2';
4504 ROLLBACK TO Expire_Memberships;
4505 l_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR', SQLERRM);
4506 Write_log (1, 'Other error in in Updating Membership Status(PV_PG_NOTIF_UTILITY_PVT.Expire_Memberships)');
4507 Write_log (1, 'SQLCODE ' || to_char(SQLCODE) ||
4508 ' SQLERRM ' || substr(SQLERRM, 1, 100));
4509 END Expire_Memberships;
4510
4511 --------------------------------------------------------------------------
4512 -- PROCEDURE
4513 -- get_Notification_Body
4514 --
4515 -- PURPOSE
4516 -- Gets Notification Body in HTML from Workflow.
4517 -- IN
4518 -- notification_id NUMBER
4519 -- OUT
4520 -- Notification body in HTML
4521 -- USED BY
4522 -- Notifications Detail screen from partner portal
4523 -- HISTORY
4524 -- 12/05/2002 sveerave CREATION
4525 -- 03/25/2003 sveerave Modified from GetFullBody to GetBody as
4526 -- GetFullBody is failing to get full message body for bug#2862626
4527 --------------------------------------------------------------------------
4528 FUNCTION get_Notification_Body(p_notif_id IN NUMBER)
4529 RETURN VARCHAR2 IS
4530 l_msgBody VARCHAR2(4000);
4531 --l_flag BOOLEAN DEFAULT FALSE;
4532
4533 BEGIN
4534 /*
4535 WF_NOTIFICATION.GetFullBody( nid => p_notif_id
4536 , msgbody => l_msgBody
4537 , end_of_body => l_flag
4538 , disptype => l_disptype
4539 );
4540 */
4541 l_msgBody := WF_NOTIFICATION.getBody( nid => p_notif_id
4542 , disptype => wf_notification.doc_html
4543 );
4544 RETURN l_msgBody;
4545 END get_Notification_Body;
4546
4547 --------------------------------------------------------------------------
4548 -- PROCEDURE
4549 -- set_msg_doc
4550 --
4551 -- PURPOSE
4552 -- Sets Message Document for PL/SQL Document.
4553 -- IN -- as per PL/SQL Notification Document stadards
4554 -- document_id IN VARCHAR2
4555 -- display_type IN VARCHAR2
4556 --
4557 -- OUT
4558 -- document IN OUT NOCOPY VARCHAR2
4559 -- document_type IN OUT NOCOPY VARCHAR2 -- USED BY
4560
4561 -- HISTORY
4562 -- 08/04/2003 sveerave CREATION
4563 --------------------------------------------------------------------------
4564
4565 PROCEDURE set_msg_doc (
4566 document_id IN VARCHAR2
4567 , display_type IN VARCHAR2
4568 , document IN OUT NOCOPY VARCHAR2
4569 , document_type IN OUT NOCOPY VARCHAR2
4570 ) IS
4571 l_api_name VARCHAR2 (61) := g_pkg_name || 'set_msg_doc';
4572 l_notif_id NUMBER;
4573
4574 BEGIN
4575
4576 ams_utility_pvt.debug_message (
4577 l_api_name
4578 || 'Entering'
4579 || 'document id '
4580 || document_id
4581 );
4582
4583 l_notif_id := TO_NUMBER(SUBSTR(document_id,10)); --'PVXNUTIL:';
4584 document := document || wf_notification.getAttrText(l_notif_id,'MESSAGE_HEADER');
4585 document := document ||'<BR>'||wf_notification.getAttrText(l_notif_id,'MESSAGE_BODY');
4586 document := document ||'<BR>'||wf_notification.getAttrText(l_notif_id,'MESSAGE_FOOTER');
4587
4588 document_type := wf_notification.doc_html;
4589 -- document := document || g_message_body_doc;
4590 RETURN;
4591 END set_msg_doc;
4592
4593
4594 PROCEDURE set_event_code
4595 (
4596 itemtype IN VARCHAR2
4597 , itemkey IN VARCHAR2
4598 , actid IN NUMBER
4599 , funcmode IN VARCHAR2
4600 , resultout OUT NOCOPY VARCHAR2
4601 )
4602 IS
4603
4604
4605 l_api_name CONSTANT VARCHAR2(30) := 'SET_EVENT_CODE';
4606 l_event_code VARCHAR2(30) ;
4607 BEGIN
4608
4609 l_event_code := WF_ENGINE.GetItemAttrText (
4610 itemtype => itemtype
4611 , itemkey => itemkey
4612 , aname => 'PARTNER_EVENT_CODE'
4613 );
4614
4615
4616
4617 PVX_UTILITY_PVT.debug_message (L_API_NAME || ' - FUNCMODE: ' || funcmode);
4618 -- RUN mode - Normal Process Execution
4619 IF (funcmode = 'RUN') THEN
4620 resultout := 'COMPLETE:' ||l_event_code ;
4621 RETURN;
4622 ELSIF (funcmode = 'CANCEL') THEN
4623 resultout := 'COMPLETE:' ;
4624 RETURN;
4625 -- TIMEOUT mode - Normal Process Execution
4626 ELSIF (funcmode = 'TIMEOUT') THEN
4627 resultout := 'COMPLETE:' ;
4628 RETURN;
4629 END IF;
4630
4631 PVX_UTILITY_PVT.debug_message (L_API_NAME || ' - RESULT: ' || resultout);
4632
4633 -- write to log
4634 EXCEPTION
4635 WHEN OTHERS THEN
4636 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
4637 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
4638 END IF;
4639 wf_core.context(G_PKG_NAME,'set_event_code', itemtype,itemkey,to_char(actid),funcmode);
4640 resultout := 'COMPLETE:' ;
4641 raise;
4642
4643 END set_event_code;
4644
4645 FUNCTION getUserIdTbl( val IN VARCHAR2 )
4646 RETURN JTF_NUMBER_TABLE
4647 IS
4648
4649 n NUMBER;
4650 y NUMBER:=1;
4651 x VARCHAR2(30);
4652 l_value VARCHAR2(2000);
4653 l_user_id_tbl JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
4654 ct NUMBER:=1;
4655
4656 BEGIN
4657 l_value :=val;
4658 LOOP
4659 n := instr( l_value, ',' );
4660 x := substr(l_value,1,n-1);
4661 l_value:=substr(l_value,n+1);
4662 l_user_id_tbl.extend(1);
4663 IF n>0 THEN
4664 l_user_id_tbl(ct) :=to_number(x);
4665 ELSE
4666 l_user_id_tbl(ct) :=to_number(l_value);
4667 END IF;
4668 ct:=ct+1;
4669 EXIT WHEN n=0;
4670 END LOOP;
4671 RETURN l_user_id_tbl;
4672 END getUserIdTbl;
4673
4674
4675
4676 PROCEDURE log_action
4677 (
4678 itemtype IN VARCHAR2
4679 , itemkey IN VARCHAR2
4680 , actid IN NUMBER
4681 , funcmode IN VARCHAR2
4682 , resultout OUT NOCOPY VARCHAR2
4683 ) IS
4684
4685
4686 L_API_NAME CONSTANT VARCHAR2(30) := 'log_action';
4687 l_object_id NUMBER;
4688 l_object_type VARCHAR2(30);
4689 l_approver_id NUMBER;
4690 l_flag VARCHAR2(1);
4691 l_event_code VARCHAR2(30);
4692 l_notification_id NUMBER;
4693 l_pgp_notif_rec PV_GE_PARTY_NOTIF_PVT.pgp_notif_rec_type ;
4694 x_party_notifid NUMBER;
4695 l_partner_id NUMBER;
4696 l_entity_id NUMBER;
4697 l_entity_code VARCHAR2(30);
4698 l_user_id VARCHAR2(2000);
4699 l_user_id_tbl JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
4700 x_return_status VARCHAR2(1);
4701 x_msg_count NUMBER;
4702 x_msg_data VARCHAR2(4000);
4703
4704 BEGIN
4705
4706 l_notification_id := wf_engine.context_nid;
4707
4708 IF (PV_DEBUG_HIGH_ON) THEN
4709 PVX_UTILITY_PVT.debug_message (L_API_NAME || ' - FUNCMODE: ' || funcmode);
4710 END IF;
4711
4712
4713
4714 l_event_code := WF_ENGINE.GetItemAttrText
4715 (
4716 itemtype => itemtype
4717 , itemkey => itemkey
4718 , aname => 'PARTNER_EVENT_CODE'
4719 );
4720
4721 l_partner_id := WF_ENGINE.GetItemAttrText
4722 (
4723 itemtype => itemtype
4724 , itemkey => itemkey
4725 , aname => 'PARTNER_ID'
4726 );
4727
4728 l_user_id := WF_ENGINE.GetItemAttrText
4729 (
4730 itemtype => itemtype
4731 , itemkey => itemkey
4732 , aname => 'RECIPIENT_USER_ID'
4733 );
4734
4735 l_entity_code := WF_ENGINE.GetItemAttrText
4736 (
4737 itemtype => itemtype
4738 , itemkey => itemkey
4739 , aname => 'ENTITY_CODE'
4740 );
4741
4742 l_entity_id := WF_ENGINE.GetItemAttrNumber
4743 (
4744 itemtype => itemtype
4745 , itemkey => itemkey
4746 , aname => 'ENTITY_ID'
4747 );
4748
4749
4750 IF l_notification_id is not null THEN
4751 l_user_id_tbl:=getUseridTbl(l_user_id);
4752 for i in 1..l_user_id_tbl.count() loop
4753 Set_Pgp_Notif(
4754 p_notif_id => l_notification_id,
4755 p_object_version => 1,
4756 p_partner_id => l_partner_id ,
4757 p_user_id => l_user_id_tbl(i),
4758 p_arc_notif_for_entity_code =>l_entity_code,
4759 p_notif_for_entity_id => l_entity_id,
4760 p_notif_type_code => l_event_code,
4761 x_return_status => x_return_status ,
4762 x_pgp_notif_rec => l_pgp_notif_rec
4763 );
4764
4765
4766 /* Check the Procedure's x_return_status */
4767 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4768 FND_MESSAGE.SET_NAME('PV','PV_SET_NOTIF_REC');
4769 FND_MESSAGE.SET_TOKEN('NOTIFICATION_ID',l_notification_id);
4770 FND_MSG_PUB.Add;
4771 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
4772 RAISE FND_API.G_EXC_ERROR;
4773 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
4774 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4775 END IF;
4776 END IF;
4777
4778
4779 /* Call the Create_Ge_Party_Notif to insert a record in PV_GE_PARTY_NOTIFICATIONS */
4780 PV_GE_PARTY_NOTIF_PVT.Create_Ge_Party_Notif (
4781 p_api_version_number => 1.0,
4782 p_init_msg_list => FND_API.G_FALSE ,
4783 p_commit => FND_API.G_FALSE ,
4784 p_validation_level => FND_API.G_VALID_LEVEL_FULL ,
4785 x_return_status => x_return_status ,
4786 x_msg_count => x_msg_count,
4787 x_msg_data => x_msg_data ,
4788 p_pgp_notif_rec => l_pgp_notif_rec,
4789 x_party_notification_id => x_party_notifid
4790 );
4791
4792 /* Check the Procedure's x_return_status */
4793 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4794 FND_MESSAGE.SET_NAME('PV','PV_GE_PARTY_NOTIF_REC');
4795 FND_MESSAGE.SET_TOKEN('NOTIFICATION_ID',l_notification_id);
4796 FND_MSG_PUB.Add;
4797 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
4798 RAISE FND_API.G_EXC_ERROR;
4799 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
4800 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4801 END IF;
4802 END IF;
4803 END LOOP;
4804 resultout := 'COMPLETE:';
4805 --commit;
4806 RETURN;
4807 END IF;
4808 resultout := 'COMPLETE:';
4809 IF (PV_DEBUG_HIGH_ON) THEN
4810 PVX_UTILITY_PVT.debug_message(L_API_NAME || ' - RESULT: ' || resultout);
4811
4812 END IF;
4813
4814
4815 EXCEPTION
4816 WHEN OTHERS THEN
4817 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
4818 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
4819 END IF;
4820
4821 --write_to_enrollment_log
4822 wf_core.context(G_PKG_NAME,'log_action', itemtype,itemkey,to_char(actid),funcmode);
4823 resultout := 'COMPLETE:' ;
4824 raise;
4825
4826 END log_action ;
4827
4828
4829 --------------------------------------------------------------------------
4830 -- PROCEDURE
4831 -- Send_Workflow_Notification
4832 --
4833 -- PURPOSE
4834 -- to start the workflow process that sends notifications
4835 -- IN
4836 -- , p_context_id IN NUMBER
4837 -- this could be partner_id, vendor id , depending on the context you are in
4838 -- , p_context_code IN VARCHAR2
4839 -- who is senting the notification validated against PV_ENTITY_TYPE
4840 -- , p_target_ctgry IN VARCHAR
4841 -- to whom the notification be sent 'PARTNER', 'VAD', 'GLOBAL', 'SUBSIDIARY' validated against pv_entity_notif_category
4842 -- , p_target_ctgry_pt_id IN NUMBER
4843 -- pass partner_id of the partner to whom notifiction needs to be sent,
4844 -- , p_notif_event_code IN VARCHAR
4845 -- the event due to which this is being called validated against PV_NOTIFICATION_EVENT_TYPE
4846 -- , p_entity_id IN NUMBER
4847 -- if the notification is related to program enrollment pass enrl_request_id.
4848 -- else pass corressponfing entity ids depending on what entity you are sending the notification for
4849 -- , p_entity_code IN VARCHAR2
4850 -- pass 'ENRQ' for enrollment related, PARTNER for partner related
4851 -- like member type change, INVITE incase of inviations related. validated against PV_ENTITY_TYPE
4852 -- , p_wait_time IN NUMBER
4853 -- wait time in days after which the reminder needs to be sent pass zero if no reminder is to be sent
4854
4855 -- HISTORY
4856 -- 10-Oct-2003 pukken CREATION
4857
4858 --------------------------------------------------------------------------
4859 PROCEDURE Send_Workflow_Notification
4860 (
4861 p_api_version_number IN NUMBER
4862 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
4863 , p_commit IN VARCHAR2 := FND_API.G_FALSE
4864 , p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
4865 , p_context_id IN NUMBER
4866 , p_context_code IN VARCHAR2
4867 , p_target_ctgry IN VARCHAR
4868 , p_target_ctgry_pt_id IN NUMBER
4869 , p_notif_event_code IN VARCHAR
4870 , p_entity_id IN NUMBER
4871 , p_entity_code IN VARCHAR2
4872 , p_wait_time IN NUMBER
4873 , x_return_status OUT NOCOPY VARCHAR2
4874 , x_msg_count OUT NOCOPY NUMBER
4875 , x_msg_data OUT NOCOPY VARCHAR2
4876 ) IS
4877
4878 l_api_name CONSTANT VARCHAR2(30) := 'Send_Workflow_Notification';
4879 l_itemType CONSTANT VARCHAR2(30) :='PVXNUTIL' ;
4880 l_api_version_number CONSTANT NUMBER := 1.0;
4881 l_itemKey VARCHAR2(80) := p_target_ctgry_pt_id || p_target_ctgry ;
4882 l_notify_type VARCHAR2(20);
4883 l_pt_role_list wf_directory.usertable;
4884 l_notif_user_id VARCHAR2(2000);
4885 l_pt_adhoc_role VARCHAR2(80);
4886 l_role_disp_name VARCHAR2(80);
4887 l_prtner_portal_url VARCHAR2(4000);
4888 l_login_url VARCHAR2(4000);
4889 l_send_respond_url VARCHAR2(500);
4890 l_vendor_org_name VARCHAR2(50);
4891 l_email_enabled VARCHAR2(5);
4892 l_lookup_exists VARCHAR2(1);
4893 l_entity_code VARCHAR2(30);
4894 l_notif_rule_active VARCHAR2(1):='Y';
4895 l_partner_program VARCHAR2(240);
4896 l_to_partner_program VARCHAR2(240);
4897 l_enrollment_duration VARCHAR2(240);
4898 l_enrollment_type VARCHAR2(240);
4899 l_source_name VARCHAR2(360);
4900 l_requestor_name VARCHAR2(360);
4901 l_user_name VARCHAR2(100);
4902 l_vendor_name VARCHAR2(360);
4903 l_partner_comp_name VARCHAR2(360);
4904 l_event_meaning VARCHAR2(80);
4905 l_string VARCHAR2(1000);
4906 l_date_format VARCHAR2(50);
4907 l_entity_id NUMBER;
4908 l_notif_targeted_ptr_id NUMBER; -- partner_id to whom the notification is targeted to.
4909 l_user_count NUMBER;
4910 l_partner_id NUMBER;
4911 l_expiry_days NUMBER;
4912 l_enrl_request_id NUMBER;
4913 l_req_resource_id NUMBER;
4914 l_partner_program_id NUMBER;
4915 l_prtnr_vndr_relship_id NUMBER;
4916 l_user_id NUMBER;
4917 --l_notif_user_id NUMBER;
4918 l_vendor_party_id NUMBER;
4919 l_partner_party_id NUMBER;
4920 l_membership_id NUMBER;
4921 l_start_date DATE;
4922 l_end_date DATE;
4923 l_req_submission_date DATE;
4924 x_user_notify_rec_tbl user_notify_rec_tbl_type;
4925 l_val NUMBER;
4926 CURSOR c_prgm_csr ( prgm_id NUMBER ) IS
4927 SELECT program_name
4928 FROM pv_partner_program_vl
4929 WHERE program_id=prgm_id;
4930
4931 CURSOR c_inv_csr ( inv_hdr_id NUMBER ) IS
4932 SELECT prgm.program_name
4933 FROM PV_PG_INVITE_HEADERS_b inv
4934 , pv_partner_program_vl prgm
4935 WHERE inv.invite_header_id=inv_hdr_id
4936 AND inv.invite_for_program_id =prgm.program_id;
4937
4938 BEGIN
4939 -- Standard call to check for call compatibility.
4940 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
4941 p_api_version_number,
4942 l_api_name,
4943 G_PKG_NAME) THEN
4944 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4945 END IF;
4946 -- Initialize message list if p_init_msg_list is set to TRUE.
4947 IF FND_API.to_Boolean( p_init_msg_list )
4948 THEN
4949 fnd_msg_pub.initialize;
4950 END IF;
4951
4952
4953 IF (PV_DEBUG_HIGH_ON) THEN
4954 PVX_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
4955 END IF;
4956
4957 x_return_status := FND_API.G_RET_STS_SUCCESS ;
4958
4959 SELECT PV_LEAD_WORKFLOWS_S.nextval
4960 INTO l_val
4961 FROM dual;
4962 l_itemKey := l_itemKey || l_val;
4963 -- check for null
4964 /**
4965 IF ( p_context_code = FND_API.G_MISS_CHAR OR p_context_code IS NULL ) THEN
4966 FND_MESSAGE.SET_NAME('PV', 'PV_MISSING_ITEM');
4967 FND_MESSAGE.SET_TOKEN('ITEM_NAME', p_context_code);
4968 RAISE FND_API.G_EXC_ERROR;
4969 END IF;
4970
4971 IF ( p_target_ctgry = FND_API.G_MISS_CHAR OR p_target_ctgry IS NULL ) THEN
4972 FND_MESSAGE.SET_NAME('PV', 'PV_MISSING_ITEM');
4973 FND_MESSAGE.SET_TOKEN('ITEM_NAME', p_target_ctgry);
4974 RAISE FND_API.G_EXC_ERROR;
4975 END IF;
4976
4977 IF ( p_entity_code = FND_API.G_MISS_CHAR OR p_entity_code IS NULL ) THEN
4978 FND_MESSAGE.SET_NAME('PV', 'PV_MISSING_ITEM');
4979 FND_MESSAGE.SET_TOKEN('ITEM_NAME', p_entity_code);
4980 RAISE FND_API.G_EXC_ERROR;
4981 END IF;
4982
4983
4984 IF ( p_notif_event_code = FND_API.G_MISS_CHAR OR p_notif_event_code IS NULL ) THEN
4985 FND_MESSAGE.SET_NAME('PV', 'PV_MISSING_ITEM');
4986 FND_MESSAGE.SET_TOKEN('ITEM_NAME', p_notif_event_code);
4987 RAISE FND_API.G_EXC_ERROR;
4988 END IF;
4989
4990 */
4991 --validate the lookupcode for target category
4992 l_lookup_exists := PVX_UTILITY_PVT.check_lookup_exists
4993 ( p_lookup_table_name => 'PV_LOOKUPS'
4994 ,p_lookup_type => 'PV_ENTITY_NOTIF_CATEGORY'
4995 ,p_lookup_code => p_target_ctgry
4996 );
4997
4998 IF NOT FND_API.to_boolean(l_lookup_exists) THEN
4999 FND_MESSAGE.set_name('PV', 'PV_INVALID_LOOKUP_CODE');
5000 FND_MESSAGE.set_token('LOOKUP_TYPE','PV_ENTITY_NOTIF_CATEGORY' );
5001 FND_MESSAGE.set_token('LOOKUP_CODE', p_target_ctgry );
5002 fnd_msg_pub.Add;
5003 RAISE FND_API.G_EXC_ERROR;
5004 END IF;
5005
5006 --validate the lookupcode for event
5007 l_lookup_exists := PVX_UTILITY_PVT.check_lookup_exists
5008 ( p_lookup_table_name => 'PV_LOOKUPS'
5009 ,p_lookup_type => 'PV_NOTIFICATION_EVENT_TYPE'
5010 ,p_lookup_code => p_notif_event_code
5011 );
5012 IF NOT FND_API.to_boolean(l_lookup_exists) THEN
5013 FND_MESSAGE.set_name('PV', 'PV_INVALID_LOOKUP_CODE');
5014 FND_MESSAGE.set_token('LOOKUP_TYPE','PV_NOTIFICATION_EVENT_TYPE' );
5015 FND_MESSAGE.set_token('LOOKUP_CODE', p_notif_event_code );
5016 fnd_msg_pub.Add;
5017 RAISE FND_API.G_EXC_ERROR;
5018 END IF;
5019
5020 --validate the lookupcodfe for entity code
5021 l_lookup_exists := PVX_UTILITY_PVT.check_lookup_exists
5022 ( p_lookup_table_name => 'PV_LOOKUPS'
5023 ,p_lookup_type => 'PV_ENTITY_TYPE'
5024 ,p_lookup_code => p_entity_code
5025 );
5026 IF NOT FND_API.to_boolean(l_lookup_exists) THEN
5027 FND_MESSAGE.set_name('PV', 'PV_INVALID_LOOKUP_CODE');
5028 FND_MESSAGE.set_token('LOOKUP_TYPE','PV_ENTITY_TYPE' );
5029 FND_MESSAGE.set_token('LOOKUP_CODE', p_entity_code );
5030 fnd_msg_pub.Add;
5031 RAISE FND_API.G_EXC_ERROR;
5032 END IF;
5033
5034 --validate the lookup for context code
5035 /**
5036 l_lookup_exists := PVX_UTILITY_PVT.check_lookup_exists
5037 ( p_lookup_table_name => 'PV_LOOKUPS'
5038 ,p_lookup_type => 'PV_ENTITY_TYPE'
5039 ,p_lookup_code => p_context_code
5040 );
5041 IF NOT FND_API.to_boolean(l_lookup_exists) THEN
5042 FND_MESSAGE.set_name('PV', 'PV_INVALID_LOOKUP_CODE');
5043 FND_MESSAGE.set_token('LOOKUP_TYPE','PV_ENTITY_TYPE' );
5044 FND_MESSAGE.set_token('LOOKUP_CODE', p_context_code );
5045 RAISE FND_API.G_EXC_ERROR;
5046 END IF;
5047 */
5048 IF p_entity_code IN ( 'GLOBAL', 'SUBSIDIARY', 'STANDARD' ) THEN
5049 l_entity_code := 'PARTNER';
5050 ELSE
5051 l_entity_code := p_entity_code;
5052 END IF;
5053
5054 /* Invoke validation procedures
5055 validate p_context_id, if context_code is anything other than VENDOR
5056 */
5057 IF p_context_code <> 'VENDOR' THEN
5058 Validate_Enrl_Requests
5059 ( p_context_id
5060 , p_context_code || '_ID'
5061 , x_return_status
5062 );
5063 /* If any errors happen abort API. */
5064 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
5065 RAISE FND_API.G_EXC_ERROR;
5066 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
5067 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5068 END IF;
5069 END IF;
5070
5071 Validate_Enrl_Requests
5072 ( p_target_ctgry_pt_id
5073 , p_target_ctgry || '_ID'
5074 , x_return_status
5075 );
5076 /* If any errors happen abort API. */
5077 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
5078 RAISE FND_API.G_EXC_ERROR;
5079 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
5080 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5081 END IF;
5082
5083 l_notif_targeted_ptr_id:= p_target_ctgry_pt_id ;
5084
5085 Validate_Enrl_Requests
5086 ( p_entity_id
5087 , 'ENTITY_ID'
5088 , x_return_status
5089 );
5090 /* If any errors happen abort API. */
5091 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
5092 RAISE FND_API.G_EXC_ERROR;
5093 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
5094 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5095 END IF;
5096
5097 IF p_entity_code = 'ENRQ' THEN
5098 /* Get the enrollment request details */
5099 get_enrl_memb_details(
5100 p_enrl_request_id => p_entity_id ,
5101 x_req_submission_date => l_req_submission_date,
5102 x_partner_program_id => l_partner_program_id,
5103 x_partner_program => l_partner_program,
5104 x_enrollment_duration => l_enrollment_duration,
5105 x_enrollment_type => l_enrollment_type,
5106 x_req_resource_id => l_req_resource_id,
5107 x_prtnr_vndr_relship_id => l_prtnr_vndr_relship_id,
5108 x_start_date => l_start_date,
5109 x_end_date => l_end_date,
5110 x_membership_id => l_membership_id,
5111 x_return_status => x_return_status);
5112
5113
5114 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
5115 THEN
5116 FND_MESSAGE.SET_NAME('PV', 'PV_ENRL_REQ_NOT_EXIST');
5117 FND_MESSAGE.SET_TOKEN('ENRL_REQUEST_ID',p_entity_id);
5118 FND_MSG_PUB.Add;
5119 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
5120 RAISE FND_API.G_EXC_ERROR;
5121 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
5122 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5123 END IF;
5124 END IF;
5125
5126
5127
5128
5129 /* Get the Partner and Vendor details */
5130 get_prtnr_vendor_details(
5131 p_enrl_request_id => p_entity_id ,
5132 x_vendor_party_id => l_vendor_party_id,
5133 x_vendor_name => l_vendor_name,
5134 x_partner_party_id => l_partner_party_id,
5135 x_partner_comp_name => l_partner_comp_name,
5136 x_return_status => x_return_status
5137 );
5138
5139 /* Check for Procedure's x_return_status */
5140 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
5141 THEN
5142 FND_MESSAGE.SET_NAME('PV','PV_PRTNR_VNDR_NOT_EXIST');
5143 FND_MESSAGE.SET_TOKEN('ENRL_REQUEST_ID',p_entity_id);
5144 FND_MSG_PUB.Add;
5145 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
5146 RAISE FND_API.G_EXC_ERROR;
5147 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
5148 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5149 END IF;
5150 END IF;
5151
5152
5153 /* Get the requestor details */
5154 get_requestor_details(
5155 p_req_resource_id => l_req_resource_id,
5156 x_user_id => l_user_id,
5157 x_source_name => l_source_name,
5158 x_user_name => l_user_name,
5159 x_return_status => x_return_status
5160 );
5161
5162
5163 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
5164 THEN
5165 FND_MESSAGE.SET_NAME('PV','PV_REQUESTOR_NOT_EXIST');
5166 FND_MESSAGE.SET_TOKEN('REQ_RESOURCE_ID',l_req_resource_id);
5167 FND_MSG_PUB.Add;
5168 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
5169 RAISE FND_API.G_EXC_ERROR;
5170 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
5171 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5172 END IF;
5173 END IF;
5174
5175 l_notif_rule_active := check_Notif_Rule_Active
5176 (
5177 p_program_id => l_partner_program_id
5178 , p_notif_type => p_notif_event_code
5179 ) ;
5180
5181
5182 END IF;
5183
5184 IF l_notif_rule_active= 'N' THEN
5185
5186 return;
5187 END IF;
5188
5189 /* Get the user list */
5190 get_users_list
5191 (
5192 p_partner_id => l_notif_targeted_ptr_id
5193 , x_user_notify_rec_tbl => x_user_notify_rec_tbl
5194 , x_user_count => l_user_count
5195 , x_return_status => x_return_status
5196 ) ;
5197
5198 /* Check for Procedure's x_return_status */
5199 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
5200
5201 IF x_return_status IN ( FND_API.G_RET_STS_ERROR, FND_API.G_RET_STS_UNEXP_ERROR ) THEN
5202
5203 -- raise error if its an invitation. else.. log it and return without sending any notification
5204 IF p_notif_event_code IN ( 'PG_INVITE','VAD_INVITE_IMP') THEN
5205 FND_MESSAGE.SET_NAME('PV','PV_NO_PRIMARY_CNTCT_FOR_INVITE');
5206 FND_MSG_PUB.Add;
5207 RAISE FND_API.G_EXC_ERROR;
5208 ELSE
5209 fnd_message.set_name ('PV', 'PV_NO_PRIMARY_USER_EXIST');
5210 fnd_message.set_token ('PARTNER_ID',l_notif_targeted_ptr_id);
5211 FND_MSG_PUB.Add;
5212 l_string := SUBSTR(fnd_message.get,1,1000);
5213 WRITE_TO_FND_LOG(l_api_name,l_string );
5214 x_return_status := FND_API.G_RET_STS_SUCCESS;
5215 IF x_msg_count is null THEN
5216 x_msg_count := 0;
5217 END IF;
5218 return;
5219 END IF;
5220 END IF;
5221 END IF;
5222
5223 FOR i IN 1 .. l_user_count LOOP
5224 --l_user_resource_id := x_user_notify_rec_tbl(i).user_resource_id;
5225 l_notif_user_id := l_notif_user_id || ',' || x_user_notify_rec_tbl(i).user_id;
5226 l_pt_role_list(i) := x_user_notify_rec_tbl(i).user_name;
5227 END LOOP;
5228
5229 IF l_pt_role_list.count > 0 then
5230 l_notif_user_id :=substr(l_notif_user_id,2);
5231 l_pt_adhoc_role := 'PV_' || l_itemKey ;
5232 l_role_disp_name :='Primary Users';
5233 -- Debug Message
5234 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
5235 PVX_UTILITY_PVT.debug_message( 'Creating role PT: '|| l_pt_adhoc_role);
5236 END IF;
5237 wf_directory.CreateAdHocRole2
5238 (
5239 role_name => l_pt_adhoc_role
5240 , role_display_name => l_pt_adhoc_role
5241 , role_users => l_pt_role_list
5242 );
5243 END IF;
5244
5245
5246 IF l_pt_role_list.count < 1 THEN
5247 return;
5248 ELSE
5249 -- Once the parameters for workflow is validated, start the workflow
5250 wf_engine.CreateProcess
5251 (
5252 ItemType => l_itemType
5253 , ItemKey => l_itemKey
5254 , process => 'EVENT_NOTIF_PROCESS'
5255 );
5256
5257 wf_engine.SetItemUserKey
5258 (
5259 ItemType => l_itemType
5260 , ItemKey => l_itemKey
5261 , userKey => l_itemkey
5262 );
5263
5264 wf_engine.SetItemAttrText
5265 (
5266 ItemType => l_itemType
5267 , ItemKey => l_itemKey
5268 , aname => 'NOTIFY_ROLE'
5269 , avalue => l_pt_adhoc_role
5270 );
5271
5272 wf_engine.SetItemAttrText
5273 (
5274 ItemType => l_itemType
5275 , ItemKey => l_itemKey
5276 , aname => 'PARTNER_EVENT_CODE'
5277 , avalue => p_notif_event_code
5278 );
5279
5280 -- NOTIFICATION_TYPE can be different from the event in some cases
5281 -- so that can be set accordingly here or later in the workflow process
5282 -- so right now setting it to p_notif_event_code
5283
5284 wf_engine.SetItemAttrText
5285 (
5286 ItemType => l_itemType
5287 , ItemKey => l_itemKey
5288 , aname => 'NOTIFICATION_TYPE'
5289 , avalue => p_notif_event_code
5290 );
5291
5292 wf_engine.SetItemAttrNumber
5293 (
5294 ItemType => l_itemType
5295 , ItemKey => l_itemKey
5296 , aname => 'PARTNER_ID'
5297 , avalue => l_notif_targeted_ptr_id --the partner_id to whom the notificationneeds to be sent
5298 );
5299
5300 wf_engine.SetItemAttrNumber
5301 (
5302 ItemType => l_itemType
5303 , ItemKey => l_itemKey
5304 , aname => 'ENTITY_ID'
5305 , avalue => p_entity_id
5306 );
5307
5308 wf_engine.SetItemAttrText
5309 (
5310 ItemType => l_itemType
5311 , ItemKey => l_itemKey
5312 , aname => 'ENTITY_CODE'
5313 , avalue => l_entity_code
5314 );
5315
5316 wf_engine.SetItemAttrText
5317 (
5318 ItemType => l_itemType
5319 , ItemKey => l_itemKey
5320 , aname => 'RECIPIENT_USER_ID'
5321 , avalue => l_notif_user_id
5322 );
5323
5324 -- set wait period in number of days in the workflow
5325 wf_engine.setItemAttrNumber(
5326 ITEMTYPE => l_itemtype,
5327 ITEMKEY => l_itemkey,
5328 ANAME => 'WAIT_PERIOD_IN_DAYS',
5329 AVALUE => p_wait_time
5330 );
5331
5332 l_login_url := FND_PROFILE.VALUE('PV_WORKFLOW_ISTORE_URL');
5333 --l_prtner_portal_url := '<a href="'|| l_login_url || '">'|| l_partner_program || '</a>';
5334 wf_engine.setItemAttrText
5335 (
5336 ITEMTYPE => l_itemtype
5337 , ITEMKEY => l_itemkey
5338 , ANAME => 'ISTORE_LOGIN_URL'
5339 , AVALUE => l_login_url
5340 );
5341
5342 IF l_entity_code = 'ENRQ' THEN
5343 -- set the program name
5344 wf_engine.setItemAttrText
5345 (
5346 ITEMTYPE => l_itemtype
5347 , ITEMKEY => l_itemkey
5348 , ANAME => 'PROGRAM_NAME'
5349 , AVALUE => l_partner_program
5350 );
5351
5352 -- set the vendor org name
5353 wf_engine.setItemAttrText
5354 (
5355 ITEMTYPE => l_itemtype
5356 , ITEMKEY => l_itemkey
5357 , ANAME => 'VENDOR_ORG_NAME'
5358 , AVALUE => l_vendor_name
5359 );
5360
5361 -- set the PARTNER_NAME
5362 wf_engine.setItemAttrText
5363 (
5364 ITEMTYPE => l_itemtype
5365 , ITEMKEY => l_itemkey
5366 , ANAME => 'PARTNER_NAME'
5367 , AVALUE => l_partner_comp_name
5368 );
5369
5370 -- set the REQUESTOR_NAME
5371 wf_engine.setItemAttrText
5372 (
5373 ITEMTYPE => l_itemtype
5374 , ITEMKEY => l_itemkey
5375 , ANAME => 'REQUESTOR_NAME'
5376 , AVALUE => l_source_name
5377 );
5378
5379 -- set the submit date
5380 wf_engine.setItemAttrDate
5381 (
5382 ITEMTYPE => l_itemtype
5383 , ITEMKEY => l_itemkey
5384 , ANAME => 'ENROLL_SUBMIT_DATE'
5385 , AVALUE => l_req_submission_date
5386 );
5387
5388 -- set the enroll type
5389 wf_engine.setItemAttrText
5390 (
5391 ITEMTYPE => l_itemtype
5392 , ITEMKEY => l_itemkey
5393 , ANAME => 'ENROLLMENT_TYPE'
5394 , AVALUE => l_enrollment_type
5395 );
5396
5397 -- set the Enrollment Duration
5398 wf_engine.setItemAttrText
5399 (
5400 ITEMTYPE => l_itemtype
5401 , ITEMKEY => l_itemkey
5402 , ANAME => 'ENROLLMENT_DURATION'
5403 , AVALUE => l_enrollment_duration
5404 );
5405 -- set the START_DATE
5406 wf_engine.setItemAttrDate
5407 (
5408 ITEMTYPE => l_itemtype
5409 , ITEMKEY => l_itemkey
5410 , ANAME => 'START_DATE'
5411 , AVALUE => l_start_date
5412 );
5413
5414 -- set the END_DATE
5415 wf_engine.setItemAttrDate
5416 (
5417 ITEMTYPE => l_itemtype
5418 , ITEMKEY => l_itemkey
5419 , ANAME => 'END_DATE'
5420 , AVALUE => l_end_date
5421 );
5422
5423 -- set membership id.
5424 wf_engine.setItemAttrNumber
5425 (
5426 ITEMTYPE => l_itemtype
5427 , ITEMKEY => l_itemkey
5428 , ANAME => 'MEMBERSHIP_ID'
5429 , AVALUE => l_membership_id
5430 );
5431
5432 --set enrollment request_id
5433 wf_engine.setItemAttrNumber
5434 (
5435 ITEMTYPE => l_itemtype
5436 , ITEMKEY => l_itemkey
5437 , ANAME => 'ENROLLMENT_REQUEST_ID'
5438 , AVALUE => p_entity_id
5439 );
5440 /* Set the Expiry in # of days */
5441 --l_date_format := 'DD-MON-YYYY';
5442 l_expiry_days := to_number(trunc(nvl(l_end_date, sysdate) ) - sysdate );
5443 IF l_expiry_days <0 THEN
5444 l_expiry_days := 0;
5445 END IF;
5446
5447 wf_engine.setItemAttrText
5448 (
5449 ITEMTYPE => l_itemtype
5450 , ITEMKEY => l_itemkey
5451 , ANAME => 'MBRSHIP_EXPIRY_IN_DAYS'
5452 , AVALUE => to_char(l_expiry_days)
5453 );
5454
5455 -- set the event meaning when Subsidiary Partner's enrollment has been approved/rejected/terminated by the Vendor
5456 IF p_notif_event_code = 'PG_TERMINATE' THEN
5457 PVX_UTILITY_PVT.get_lookup_meaning
5458 (
5459 p_lookup_type => 'PV_MEMBERSHIP_STATUS'
5460 , p_lookup_code => p_context_code
5461 , x_return_status => x_return_status
5462 , x_meaning => l_event_meaning
5463 );
5464 wf_engine.setItemAttrText
5465 (
5466 ITEMTYPE => l_itemtype
5467 , ITEMKEY => l_itemkey
5468 , ANAME => 'EVENT_MEANING'
5469 , AVALUE => l_event_meaning
5470 );
5471
5472 END IF;
5473
5474 IF p_notif_event_code = 'SUBSIDIARY_PTNR_ENROLL' THEN
5475 IF p_context_code IN ( 'APPROVED' , 'REJECTED' ) THEN
5476 PVX_UTILITY_PVT.get_lookup_meaning
5477 (
5478 p_lookup_type => 'PV_ENROLLMENT_REQUEST_STATUS'
5479 , p_lookup_code => p_context_code
5480 , x_return_status => x_return_status
5481 , x_meaning => l_event_meaning
5482 );
5483 ELSE
5484 PVX_UTILITY_PVT.get_lookup_meaning
5485 (
5486 p_lookup_type => 'PV_NOTIFICATION_EVENT_TYPE'
5487 , p_lookup_code => p_notif_event_code
5488 , x_return_status => x_return_status
5489 , x_meaning => l_event_meaning
5490 );
5491 END IF;
5492
5493 wf_engine.setItemAttrText
5494 (
5495 ITEMTYPE => l_itemtype
5496 , ITEMKEY => l_itemkey
5497 , ANAME => 'EVENT_MEANING'
5498 , AVALUE => l_event_meaning
5499 );
5500 END IF;
5501
5502 IF p_notif_event_code = 'PG_DOWNGRADE' THEN
5503 wf_engine.setItemAttrText
5504 (
5505 ITEMTYPE => l_itemtype
5506 , ITEMKEY => l_itemkey
5507 , ANAME => 'FROM_PROGRAM'
5508 , AVALUE => l_partner_program
5509 );
5510
5511 OPEN c_prgm_csr(p_context_id);
5512 FETCH c_prgm_csr INTO l_to_partner_program;
5513 CLOSE c_prgm_csr;
5514
5515 wf_engine.setItemAttrText
5516 (
5517 ITEMTYPE => l_itemtype
5518 , ITEMKEY => l_itemkey
5519 , ANAME => 'TO_PROGRAM'
5520 , AVALUE => l_to_partner_program
5521 );
5522
5523 END IF;
5524
5525 ELSIF l_entity_code IN ( 'PARTNER', 'INVITE' ) THEN
5526 IF p_notif_event_code = 'SUBSIDIARY_PTNR_REGISTRATION' THEN
5527 l_partner_id :=p_context_id;
5528 ELSE
5529 l_partner_id := l_notif_targeted_ptr_id;
5530 END IF;
5531
5532 get_partner_vendor_details
5533 (
5534 p_partner_id => l_partner_id
5535 , x_vendor_party_id => l_vendor_party_id
5536 , x_vendor_name => l_vendor_name
5537 , x_partner_party_id => l_partner_party_id
5538 , x_partner_comp_name => l_partner_comp_name
5539 , x_return_status => x_return_status
5540 );
5541 /* Check for Procedure's x_return_status */
5542 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
5543 FND_MESSAGE.SET_NAME('PV','PV_PRTNR_VNDR_NOT_EXIST');
5544 FND_MESSAGE.SET_TOKEN('PARTNER_ID',l_partner_id );
5545 FND_MSG_PUB.Add;
5546 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
5547 RAISE FND_API.G_EXC_ERROR;
5548 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
5549 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5550 END IF;
5551 END IF;
5552
5553 -- set the vendor org name
5554 wf_engine.setItemAttrText
5555 (
5556 ITEMTYPE => l_itemtype
5557 , ITEMKEY => l_itemkey
5558 , ANAME => 'VENDOR_ORG_NAME'
5559 , AVALUE => l_vendor_name
5560 );
5561
5562 -- set the PARTNER_NAME
5563 wf_engine.setItemAttrText
5564 (
5565 ITEMTYPE => l_itemtype
5566 , ITEMKEY => l_itemkey
5567 , ANAME => 'PARTNER_NAME'
5568 , AVALUE => l_partner_comp_name
5569 );
5570
5571 IF p_notif_event_code IN ( 'PG_INVITE', 'VAD_INVITE_IMP' ) THEN
5572 OPEN c_inv_csr (p_entity_id);
5573 FETCH c_inv_csr INTO l_partner_program;
5574 CLOSE c_inv_csr ;
5575 wf_engine.setItemAttrText
5576 (
5577 ITEMTYPE => l_itemtype
5578 , ITEMKEY => l_itemkey
5579 , ANAME => 'PROGRAM_NAME'
5580 , AVALUE => l_partner_program
5581 );
5582 END IF;
5583
5584 IF p_notif_event_code = 'MEMBER_TYPE_CHANGE' THEN
5585 wf_engine.setItemAttrText
5586 (
5587 ITEMTYPE => l_itemtype
5588 , ITEMKEY => l_itemkey
5589 , ANAME => 'FROM_MEMBER_TYPE'
5590 , AVALUE => p_context_code
5591 );
5592
5593 wf_engine.setItemAttrText
5594 (
5595 ITEMTYPE => l_itemtype
5596 , ITEMKEY => l_itemkey
5597 , ANAME => 'TO_MEMBER_TYPE'
5598 , AVALUE => p_entity_code
5599 );
5600 END IF;
5601
5602
5603 END IF; -- END OF IF .
5604
5605
5606
5607 wf_engine.StartProcess
5608 (
5609 ItemType => l_itemType
5610 , ItemKey => l_itemKey
5611 );
5612 -- we can call the following procedure to see whether workflow was able to send notification successfully.
5613 -- but commenting this ouit because the error message could be seen by partner user.
5614 /** pv_assignment_pub.checkforErrors
5615 (p_api_version_number => 1.0
5616 ,p_init_msg_list => FND_API.G_FALSE
5617 ,p_commit => FND_API.G_FALSE
5618 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
5619 ,p_itemtype => l_itemType
5620 ,p_itemkey => l_itemKey
5621 ,x_msg_count => x_msg_count
5622 ,x_msg_data => x_msg_data
5623 ,x_return_status => x_return_status);
5624
5625 -- Check the x_return_status. If its not successful throw an exception.
5626 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
5627 raise FND_API.G_EXC_ERROR;
5628 end if;
5629
5630 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
5631 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
5632 fnd_message.Set_token('TEXT', 'After Checkforerror');
5633 fnd_msg_pub.Add;
5634 END IF;
5635 */
5636 END IF;
5637 -- Standard call to get message count and if count is 1, get message info.
5638 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_TRUE,
5639 p_count => x_msg_count,
5640 p_data => x_msg_data);
5641
5642 IF (PV_DEBUG_HIGH_ON) THEN
5643 PVX_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
5644 END IF;
5645
5646 IF FND_API.To_Boolean ( p_commit ) THEN
5647 COMMIT WORK;
5648 END IF;
5649
5650 EXCEPTION
5651
5652 WHEN FND_API.G_EXC_ERROR THEN
5653 x_return_status := FND_API.G_RET_STS_ERROR ;
5654 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_TRUE,
5655 p_count => x_msg_count,
5656 p_data => x_msg_data);
5657 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
5658 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
5659 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_TRUE,
5660 p_count => x_msg_count,
5661 p_data => x_msg_data);
5662 WHEN OTHERS THEN
5663 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5664 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
5665 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_TRUE,
5666 p_count => x_msg_count,
5667 p_data => x_msg_data);
5668 end Send_Workflow_Notification;
5669
5670
5671
5672 PROCEDURE Send_Invitations
5673 (
5674 p_api_version_number IN NUMBER
5675 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
5676 , p_commit IN VARCHAR2 := FND_API.G_FALSE
5677 , p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
5678 , p_partner_id IN NUMBER
5679 , p_invite_header_id IN NUMBER
5680 , p_from_program_id IN NUMBER DEFAULT NULL
5681 , p_notif_event_code IN VARCHAR2
5682 , p_discount_value IN VARCHAR2
5683 , p_discount_unit IN VARCHAR2
5684 , p_currency IN VARCHAR2
5685 , p_end_date IN DATE
5686 , x_return_status OUT NOCOPY VARCHAR2
5687 , x_msg_count OUT NOCOPY NUMBER
5688 , x_msg_data OUT NOCOPY VARCHAR2
5689 ) IS
5690
5691 l_api_name CONSTANT VARCHAR2(30) := 'Send_Invitation';
5692 l_itemType CONSTANT VARCHAR2(30) :='PVXNUTIL' ;
5693 l_api_version_number CONSTANT NUMBER := 1.0;
5694 l_itemKey VARCHAR2(30) := p_partner_id ||'INVITE';
5695 l_notify_type VARCHAR2(20);
5696 l_pt_role_list wf_directory.usertable;
5697 l_notif_user_id VARCHAR2(2000);
5698 l_pt_adhoc_role VARCHAR2(80);
5699 l_role_disp_name VARCHAR2(80);
5700 l_prtner_portal_url VARCHAR2(4000);
5701 l_login_url VARCHAR2(4000);
5702 l_send_respond_url VARCHAR2(500);
5703 l_vendor_org_name VARCHAR2(50);
5704 l_email_enabled VARCHAR2(5);
5705 l_lookup_exists VARCHAR2(1);
5706 l_entity_code VARCHAR2(30);
5707 l_notif_rule_active VARCHAR2(1):='Y';
5708 l_partner_program VARCHAR2(240);
5709 l_to_partner_program VARCHAR2(240);
5710 l_from_partner_program VARCHAR2(240);
5711 l_enrollment_duration VARCHAR2(240);
5712 l_enrollment_type VARCHAR2(240);
5713 l_source_name VARCHAR2(360);
5714 l_requestor_name VARCHAR2(360);
5715 l_user_name VARCHAR2(100);
5716 l_vendor_name VARCHAR2(360);
5717 l_partner_comp_name VARCHAR2(360);
5718 l_event_meaning VARCHAR2(80);
5719 l_discount_meaning VARCHAR2(80);
5720 l_currency VARCHAR2(80);
5721 l_string VARCHAR2(1000):=null;
5722 l_discount_str VARCHAR2(120);
5723 l_entity_id NUMBER;
5724 l_notif_targeted_ptr_id NUMBER; -- partner_id to whom the notification is targeted to.
5725 l_user_count NUMBER;
5726 l_partner_id NUMBER;
5727 l_expiry_days NUMBER;
5728 l_enrl_request_id NUMBER;
5729 l_req_resource_id NUMBER;
5730 l_partner_program_id NUMBER;
5731 l_prtnr_vndr_relship_id NUMBER;
5732 l_user_id NUMBER;
5733 --l_notif_user_id NUMBER;
5734 l_vendor_party_id NUMBER;
5735 l_partner_party_id NUMBER;
5736 l_membership_id NUMBER;
5737 l_start_date DATE;
5738 l_end_date DATE;
5739 l_req_submission_date DATE;
5740 x_user_notify_rec_tbl user_notify_rec_tbl_type;
5741 l_val NUMBER;
5742
5743 CURSOR c_prgm_csr ( prgm_id NUMBER ) IS
5744 SELECT program_name
5745 FROM pv_partner_program_vl
5746 WHERE program_id=prgm_id;
5747
5748 CURSOR c_inv_csr ( inv_hdr_id NUMBER ) IS
5749 SELECT prgm.program_name
5750 FROM PV_PG_INVITE_HEADERS_b inv
5751 , pv_partner_program_vl prgm
5752 WHERE inv.invite_header_id=inv_hdr_id
5753 AND inv.invite_for_program_id =prgm.program_id;
5754
5755 CURSOR c_currency_csr ( currencyCode VARCHAR2 ) IS
5756 SELECT name
5757 FROM fnd_currencies_vl
5758 WHERE currency_code = currencyCode;
5759
5760 BEGIN
5761 -- Standard call to check for call compatibility.
5762 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
5763 p_api_version_number,
5764 l_api_name,
5765 G_PKG_NAME) THEN
5766 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5767 END IF;
5768 -- Initialize message list if p_init_msg_list is set to TRUE.
5769 IF FND_API.to_Boolean( p_init_msg_list )
5770 THEN
5771 fnd_msg_pub.initialize;
5772 END IF;
5773
5774
5775 IF (PV_DEBUG_HIGH_ON) THEN
5776 PVX_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
5777 END IF;
5778
5779 x_return_status := FND_API.G_RET_STS_SUCCESS ;
5780
5781 SELECT PV_LEAD_WORKFLOWS_S.nextval
5782 INTO l_val
5783 FROM dual;
5784
5785 l_itemKey := l_itemKey || l_val;
5786 /* Get the user list */
5787 get_users_list
5788 (
5789 p_partner_id => p_partner_id
5790 , x_user_notify_rec_tbl => x_user_notify_rec_tbl
5791 , x_user_count => l_user_count
5792 , x_return_status => x_return_status
5793 ) ;
5794
5795 /* Check for Procedure's x_return_status */
5796 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
5797 FND_MESSAGE.SET_NAME('PV','PV_NO_PRIMARY_CNTCT_FOR_INVITE');
5798 FND_MSG_PUB.Add;
5799 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
5800 RAISE FND_API.G_EXC_ERROR;
5801 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
5802 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5803 END IF;
5804 END IF;
5805
5806 FOR i IN 1 .. l_user_count LOOP
5807 --l_user_resource_id := x_user_notify_rec_tbl(i).user_resource_id;
5808 l_notif_user_id := l_notif_user_id || ',' || x_user_notify_rec_tbl(i).user_id;
5809 l_pt_role_list(i) := x_user_notify_rec_tbl(i).user_name;
5810 END LOOP;
5811
5812 IF l_pt_role_list.count > 0 then
5813 l_notif_user_id :=substr(l_notif_user_id,2);
5814 l_pt_adhoc_role := 'PV_' || l_itemKey ;
5815 l_role_disp_name :='Primary Users';
5816 -- Debug Message
5817 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
5818 PVX_UTILITY_PVT.debug_message( 'Creating role PT: '|| l_pt_adhoc_role);
5819 END IF;
5820 wf_directory.CreateAdHocRole2
5821 (
5822 role_name => l_pt_adhoc_role
5823 , role_display_name => l_pt_adhoc_role
5824 , role_users => l_pt_role_list
5825 );
5826 END IF;
5827
5828
5829 IF l_pt_role_list.count < 1 THEN
5830 return;
5831 ELSE
5832 -- Once the parameters for workflow is validated, start the workflow
5833 wf_engine.CreateProcess
5834 (
5835 ItemType => l_itemType
5836 , ItemKey => l_itemKey
5837 , process => 'EVENT_NOTIF_PROCESS'
5838 );
5839
5840 wf_engine.SetItemUserKey
5841 (
5842 ItemType => l_itemType
5843 , ItemKey => l_itemKey
5844 , userKey => l_itemkey
5845 );
5846
5847 wf_engine.SetItemAttrText
5848 (
5849 ItemType => l_itemType
5850 , ItemKey => l_itemKey
5851 , aname => 'NOTIFY_ROLE'
5852 , avalue => l_pt_adhoc_role
5853 );
5854
5855 -- set the discount
5856 IF p_discount_unit IS NOT NULL THEN
5857 IF p_discount_unit= 'AMT' THEN
5858 OPEN c_currency_csr ( p_currency );
5859 FETCH c_currency_csr INTO l_currency;
5860 CLOSE c_currency_csr;
5861 l_discount_str:= p_discount_value || ' ' || l_currency ;
5862 ELSE
5863 PVX_UTILITY_PVT.get_lookup_meaning
5864 (
5865 p_lookup_type => 'PV_OFFER_DISCOUNT_TYPE'
5866 , p_lookup_code => p_discount_unit
5867 , x_return_status => x_return_status
5868 , x_meaning => l_discount_meaning
5869 );
5870 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
5871 RAISE FND_API.G_EXC_ERROR;
5872 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
5873 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5874 END IF;
5875 l_discount_str:= p_discount_value || ' ' || l_discount_meaning ;
5876 END IF;
5877 fnd_message.set_name ('PV', 'PV_PRGM_DISCOUNT_MSG');
5878 fnd_message.set_token ('DISCOUNT', l_discount_str);
5879 fnd_message.set_token ('END_DATE', p_end_date);
5880 l_string := SUBSTR(fnd_message.get,1,1000);
5881
5882 END IF;
5883
5884 wf_engine.SetItemAttrText
5885 (
5886 ItemType => l_itemType
5887 , ItemKey => l_itemKey
5888 , aname => 'DISCOUNT_STRING'
5889 , avalue => l_string
5890 );
5891
5892 wf_engine.SetItemAttrText
5893 (
5894 ItemType => l_itemType
5895 , ItemKey => l_itemKey
5896 , aname => 'PARTNER_EVENT_CODE'
5897 , avalue => p_notif_event_code
5898 );
5899
5900 -- NOTIFICATION_TYPE can be different from the event in some cases
5901 -- so that can be set accordingly here or later in the workflow process
5902 -- so right now setting it to p_notif_event_code
5903
5904 wf_engine.SetItemAttrText
5905 (
5906 ItemType => l_itemType
5907 , ItemKey => l_itemKey
5908 , aname => 'NOTIFICATION_TYPE'
5909 , avalue => p_notif_event_code
5910 );
5911
5912 wf_engine.SetItemAttrNumber
5913 (
5914 ItemType => l_itemType
5915 , ItemKey => l_itemKey
5916 , aname => 'PARTNER_ID'
5917 , avalue => p_partner_id --the partner_id to whom the notificationneeds to be sent
5918 );
5919
5920 wf_engine.SetItemAttrNumber
5921 (
5922 ItemType => l_itemType
5923 , ItemKey => l_itemKey
5924 , aname => 'ENTITY_ID'
5925 , avalue => p_invite_header_id
5926 );
5927
5928 wf_engine.SetItemAttrText
5929 (
5930 ItemType => l_itemType
5931 , ItemKey => l_itemKey
5932 , aname => 'ENTITY_CODE'
5933 , avalue => 'INVITE'
5934 );
5935
5936 wf_engine.SetItemAttrText
5937 (
5938 ItemType => l_itemType
5939 , ItemKey => l_itemKey
5940 , aname => 'RECIPIENT_USER_ID'
5941 , avalue => l_notif_user_id
5942 );
5943
5944 -- set wait period in number of days in the workflow
5945 wf_engine.setItemAttrNumber(
5946 ITEMTYPE => l_itemtype,
5947 ITEMKEY => l_itemkey,
5948 ANAME => 'WAIT_PERIOD_IN_DAYS',
5949 AVALUE => 0
5950 );
5951
5952 l_login_url := FND_PROFILE.VALUE('PV_WORKFLOW_ISTORE_URL');
5953 --l_prtner_portal_url := '<a href="'|| l_login_url || '">'|| l_partner_program || '</a>';
5954 wf_engine.setItemAttrText
5955 (
5956 ITEMTYPE => l_itemtype
5957 , ITEMKEY => l_itemkey
5958 , ANAME => 'ISTORE_LOGIN_URL'
5959 , AVALUE => l_login_url
5960 );
5961
5962 get_partner_vendor_details
5963 (
5964 p_partner_id => p_partner_id
5965 , x_vendor_party_id => l_vendor_party_id
5966 , x_vendor_name => l_vendor_name
5967 , x_partner_party_id => l_partner_party_id
5968 , x_partner_comp_name => l_partner_comp_name
5969 , x_return_status => x_return_status
5970 );
5971 /* Check for Procedure's x_return_status */
5972 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
5973 FND_MESSAGE.SET_NAME('PV','PV_PRTNR_VNDR_NOT_EXIST');
5974 FND_MESSAGE.SET_TOKEN('PARTNER_ID',l_partner_id );
5975 FND_MSG_PUB.Add;
5976 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
5977 RAISE FND_API.G_EXC_ERROR;
5978 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
5979 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5980 END IF;
5981 END IF;
5982
5983 -- set the vendor org name
5984 wf_engine.setItemAttrText
5985 (
5986 ITEMTYPE => l_itemtype
5987 , ITEMKEY => l_itemkey
5988 , ANAME => 'VENDOR_ORG_NAME'
5989 , AVALUE => l_vendor_name
5990 );
5991
5992 -- set the PARTNER_NAME
5993 wf_engine.setItemAttrText
5994 (
5995 ITEMTYPE => l_itemtype
5996 , ITEMKEY => l_itemkey
5997 , ANAME => 'PARTNER_NAME'
5998 , AVALUE => l_partner_comp_name
5999 );
6000
6001
6002 OPEN c_inv_csr (p_invite_header_id);
6003 FETCH c_inv_csr INTO l_partner_program;
6004 CLOSE c_inv_csr ;
6005
6006 wf_engine.setItemAttrText
6007 (
6008 ITEMTYPE => l_itemtype
6009 , ITEMKEY => l_itemkey
6010 , ANAME => 'PROGRAM_NAME'
6011 , AVALUE => l_partner_program
6012 );
6013
6014 wf_engine.setItemAttrText
6015 (
6016 ITEMTYPE => l_itemtype
6017 , ITEMKEY => l_itemkey
6018 , ANAME => 'TO_PROGRAM'
6019 , AVALUE => l_partner_program
6020 );
6021
6022 IF p_from_program_id IS NOT NULL THEN
6023 OPEN c_prgm_csr(p_from_program_id);
6024 FETCH c_prgm_csr INTO l_from_partner_program;
6025 CLOSE c_prgm_csr;
6026
6027 wf_engine.setItemAttrText
6028 (
6029 ITEMTYPE => l_itemtype
6030 , ITEMKEY => l_itemkey
6031 , ANAME => 'FROM_PROGRAM'
6032 , AVALUE => l_from_partner_program
6033 );
6034 END IF;
6035
6036 wf_engine.StartProcess
6037 (
6038 ItemType => l_itemType
6039 , ItemKey => l_itemKey
6040 );
6041
6042 END IF;
6043 -- Standard call to get message count and if count is 1, get message info.
6044 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_TRUE,
6045 p_count => x_msg_count,
6046 p_data => x_msg_data);
6047
6048 IF (PV_DEBUG_HIGH_ON) THEN
6049 PVX_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
6050 END IF;
6051
6052 IF FND_API.To_Boolean ( p_commit ) THEN
6053 COMMIT WORK;
6054 END IF;
6055
6056 EXCEPTION
6057
6058 WHEN FND_API.G_EXC_ERROR THEN
6059 x_return_status := FND_API.G_RET_STS_ERROR ;
6060 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_TRUE,
6061 p_count => x_msg_count,
6062 p_data => x_msg_data);
6063 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
6064 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
6065 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_TRUE,
6066 p_count => x_msg_count,
6067 p_data => x_msg_data);
6068 WHEN OTHERS THEN
6069 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
6070 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
6071 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_TRUE,
6072 p_count => x_msg_count,
6073 p_data => x_msg_data);
6074 end Send_Invitations;
6075
6076
6077
6078 END PV_PG_NOTIF_UTILITY_PVT;