[Home] [Help]
PACKAGE BODY: APPS.PV_BENFT_STATUS_CHANGE
Source
1 PACKAGE BODY PV_BENFT_STATUS_CHANGE AS
2 /* $Header: pvstchgb.pls 120.7 2006/05/09 16:19:04 saarumug ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30):= 'PV_BENFT_STATUS_CHANGE';
5 G_FILE_NAME CONSTANT VARCHAR2(12) := 'pvstchgb.pls';
6
7 /**
8 * Procedure to convert comma separated user list to a
9 * wf_directory.UserTable type.
10 **/
11 PROCEDURE CONVERT_LIST_TO_TABLE(p_role_list IN VARCHAR2
12 ,x_role_list_tbl OUT NOCOPY wf_directory.UserTable);
13
14
15 PROCEDURE STATUS_CHANGE_notification(
16 p_api_version_number IN NUMBER,
17 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
18 p_commit IN VARCHAR2 := FND_API.G_FALSE,
19 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
20 P_BENEFIT_ID IN NUMBER,
21 P_STATUS IN VARCHAR2,
22 P_ENTITY_ID IN NUMBER,
23 P_PARTNER_ID IN NUMBER,
24 p_msg_callback_api IN VARCHAR2,
25 p_user_callback_api IN VARCHAR2,
26 p_user_role IN VARCHAR2 DEFAULT NULL,
27 x_return_status OUT NOCOPY VARCHAR2,
28 x_msg_count OUT NOCOPY NUMBER,
29 x_msg_data OUT NOCOPY VARCHAR2) is
30
31 l_api_name CONSTANT VARCHAR2(30) := 'STATUS_CHANGE_NOTIFICATION';
32 l_api_version_number CONSTANT NUMBER := 1.0;
33
34 CURSOR lc_get_benefit_type (pc_benefit_id number) is
35 select benefit_type_code from pv_ge_benefits_b
36 where benefit_id = pc_benefit_id;
37
38 CURSOR lc_get_message (pc_benefit_id number, pc_status varchar2, pc_userrole varchar2) is
39 select user_role, wf_message_type, wf_message_name
40 from pv_notification_setups
41 where benefit_id = pc_benefit_id
42 and entity_status = pc_status
43 AND user_role like pc_userrole;
44
45 cursor lc_get_cm (pc_partner_id number) is
46 select fnd_user.user_name
47 from pv_partner_accesses acc, jtf_rs_resource_extns res, fnd_user
48 where acc.partner_id = pc_partner_id
49 and acc.resource_id = res.resource_id
50 and res.user_id = fnd_user.user_id;
51
52 cursor lc_get_approvers (pc_benefit_type varchar2, pc_entity_id number) is
53 select fnd_user.user_name
54 from pv_ge_temp_approvers apr, fnd_user
55 where apr.arc_appr_for_entity_code = pc_benefit_type
56 and apr.appr_for_entity_id = pc_entity_id
57 and apr.approver_id = fnd_user.user_id
58 AND APR.approval_status_code IN ('PENDING_APPROVAL','PENDING_DEFAULT')
59 and apr.approver_type_code = 'USER';
60
61 l_benefit_type varchar2(8);
62 l_adhoc_role varchar2(200);
63 l_role_list varchar2(3000);
64 l_user_type varchar2(30);
65 l_msg_type varchar2(30);
66 l_msg_name varchar2(30);
67 l_itemkey varchar2(200);
68 l_group_notify_id number;
69 l_context varchar2(1000);
70 l_has_notification boolean := false;
71
72 l_role_list_tbl WF_DIRECTORY.UserTable; --Bug 5124079
73
74 BEGIN
75 -- Standard call to check for call compatibility.
76
77 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
78 p_api_version_number,
79 l_api_name,
80 G_PKG_NAME) THEN
81 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
82
83 END IF;
84
85 -- Initialize message list if p_init_msg_list is set to TRUE.
86 IF FND_API.to_Boolean( p_init_msg_list )
87 THEN
88 fnd_msg_pub.initialize;
89 END IF;
90
91 if( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
92 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
93 'pv.plsql.PV_BENFT_STATUS_CHANGE.STATUS_CHANGE_NOTIFICATION.start',
94 'Benefit id:' || p_benefit_id || '. Entity id: ' || p_entity_id ||
95 '. Status:' || p_status || '. Partner id:' || p_partner_id ||
96 '. Message callback API: ' || p_msg_callback_api || 'User type: ' || nvl(p_user_role,'NULL') ||
97 '. User Callback API: ' || p_user_callback_api);
98 end if;
99
100 -- Initialize API return status to success
101 x_return_status := FND_API.G_RET_STS_SUCCESS;
102
103 open lc_get_benefit_type(pc_benefit_id => p_benefit_id);
104 fetch lc_get_benefit_type into l_benefit_type;
105 close lc_get_benefit_type;
106
107 IF l_benefit_type is null then
108 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
109 fnd_message.Set_Token('TEXT', 'Benefit does not exist. Benefit id: ' || p_benefit_id);
110 fnd_msg_pub.Add;
111 raise FND_API.G_EXC_UNEXPECTED_ERROR;
112 end if;
113
114 if p_user_role is null then
115 open lc_get_message(pc_benefit_id => p_benefit_id, pc_status => p_status, pc_userrole => '%');
116 else
117 open lc_get_message(pc_benefit_id => p_benefit_id, pc_status => p_status, pc_userrole => p_user_role);
118 end if;
119
120 loop
121 fetch lc_get_message into l_user_type, l_msg_type, l_msg_name;
122 exit when lc_get_message%notfound;
123
124 if( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
125 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
126 'pv.plsql.PV_BENFT_STATUS_CHANGE.STATUS_CHANGE_NOTIFICATION.info',
127 'Notification Setup: User type:' || l_user_type || '. Message type:' || l_msg_type
128 || '. Message Name:' || l_msg_name);
129 END IF;
130
131 l_has_notification := true;
132
133 l_role_list := '';
134
135 execute immediate 'select ' || p_user_callback_api ||
136 '(:itemtype, :entity_id, :usertype, :status) from dual'
137 into l_role_list using l_benefit_type, p_entity_id, l_user_type, p_status ;
138
139 if l_role_list is null then
140
141 if( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
142 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
143 'pv.plsql.PV_BENFT_STATUS_CHANGE.STATUS_CHANGE_NOTIFICATION.info',
144 'None found from user callback api. executing system sql');
145 END IF;
146
147 if l_user_type = 'CHANNEL_MANAGER' then
148
149 for l_row in lc_get_cm(pc_partner_id => p_partner_id) loop
150 l_role_list := l_role_list || ',' || l_row.user_name;
151 end loop;
152 l_role_list := substr(l_role_list,2);
153
154 elsif l_user_type = 'BENEFIT_APPROVER' then
155
156 for l_row in lc_get_approvers(pc_benefit_type => l_benefit_type, pc_entity_id => p_entity_id) loop
157 l_role_list := l_role_list || ',' || l_row.user_name;
158 end loop;
159 l_role_list := substr(l_role_list,2);
160
161 elsif l_user_type = 'DQM_APPROVER' then
162
163 for l_row in lc_get_approvers(pc_benefit_type => 'PVDQMAPR', pc_entity_id => p_entity_id) loop
164 l_role_list := l_role_list || ',' || l_row.user_name;
165 end loop;
166 l_role_list := substr(l_role_list,2);
167
168 else
169 if( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
170 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
171 'pv.plsql.PV_BENFT_STATUS_CHANGE.STATUS_CHANGE_NOTIFICATION.info',
172 'Possible error. Unrecognized user role:' || l_user_type);
173 END IF;
174 end if;
175 end if;
176
177 CONVERT_LIST_TO_TABLE(p_role_list => l_role_list,
178 x_role_list_tbl => l_role_list_tbl);
179
180 if l_role_list_tbl.COUNT > 0 then
181 if( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
182 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
183 'pv.plsql.PV_BENFT_STATUS_CHANGE.STATUS_CHANGE_NOTIFICATION.info',
184 'user list for ' || l_user_type || ' is:' || l_role_list);
185 END IF;
186
187 l_itemkey := l_msg_type||'|'||l_user_type||'|'||l_msg_name||'|'||p_entity_id||
188 '|'||to_char(sysdate,'YYYYMMDDHH24MISS');
189
190 if( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
191 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
192 'pv.plsql.PV_BENFT_STATUS_CHANGE.STATUS_CHANGE_NOTIFICATION.info',
193 'Creating process for itemtype:' || l_benefit_type || '. itemkey: ' || l_itemkey);
194 END IF;
195
196 wf_engine.CreateProcess ( ItemType => l_BENEFIT_TYPE,
197 ItemKey => l_itemkey,
198 process => 'NOOP_PROCESS',
199 user_key => l_itemkey);
200
201 execute immediate 'BEGIN ' ||
202 p_msg_callback_api || '(:itemtype, :itemkey, :entity_id, :usertype, :status); ' ||
203 'END;'
204 using l_benefit_type, l_itemkey, p_entity_id, l_user_type, p_status;
205
206 l_adhoc_role := l_itemkey;
207 wf_directory.CreateAdHocRole2(role_name => l_adhoc_role,
208 role_display_name => l_adhoc_role,
209 role_users => l_role_list_tbl);
210
211 l_context := l_benefit_type || ':' || l_itemkey || ':';
212
213 l_group_notify_id := wf_notification.sendGroup(
214 role => l_adhoc_role,
215 msg_type => l_msg_type,
216 msg_name => l_msg_name,
217 due_date => null,
218 callback => 'wf_engine.cb',
219 context => l_context,
220 send_comment => NULL,
221 priority => NULL );
222
223 if( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
224 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
225 'pv.plsql.PV_BENFT_STATUS_CHANGE.STATUS_CHANGE_NOTIFICATION.info',
226 'Sent notification to role: ' || l_adhoc_role ||
227 ' Using message: ' || l_msg_name || '. Notify id: ' || l_group_notify_id );
228 END IF;
229
230 wf_engine.StartProcess(itemtype => l_benefit_type,
231 itemkey => l_itemkey);
232
233 else
234 if( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
235 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
236 'pv.plsql.PV_BENFT_STATUS_CHANGE.STATUS_CHANGE_NOTIFICATION.info',
237 'No users found for user type: ' || l_user_type);
238 END IF;
239 end if;
240
241 end loop;
242 close lc_get_message;
243
244 if not l_has_notification then
245 if( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
246 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
247 'pv.plsql.PV_BENFT_STATUS_CHANGE.STATUS_CHANGE_NOTIFICATION.info',
248 'No Notifications has been setup for this benefit');
249 END IF;
250 end if;
251
252 IF FND_API.To_Boolean ( p_commit ) THEN
253 COMMIT WORK;
254 END IF;
255
256 if( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
257 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
258 'pv.plsql.PV_BENFT_STATUS_CHANGE.STATUS_CHANGE_NOTIFICATION.end', 'Exiting' );
259 end if;
260
261 -- Standard call to get message count and if count is 1, get message info.
262 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_TRUE,
263 p_count => x_msg_count,
264 p_data => x_msg_data);
265 EXCEPTION
266 WHEN FND_API.G_EXC_ERROR THEN
267
268 x_return_status := FND_API.G_RET_STS_ERROR ;
269
270 if( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
271 FND_LOG.STRING(FND_LOG.LEVEL_ERROR,
272 'pv.plsql.PV_BENFT_STATUS_CHANGE.STATUS_CHANGE_NOTIFICATION.error', fnd_msg_pub.get(p_encoded => 'F') );
273 end if;
274
275 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_TRUE,
276 p_count => x_msg_count,
277 p_data => x_msg_data);
278 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
279
280 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
281
282 if( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
283 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
284 'pv.plsql.PV_BENFT_STATUS_CHANGE.STATUS_CHANGE_NOTIFICATION.unexpected', fnd_msg_pub.get(p_encoded => 'F') );
285 end if;
286
287 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_TRUE,
288 p_count => x_msg_count,
289 p_data => x_msg_data);
290 WHEN OTHERS THEN
291
292 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
293 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
294
295 if( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
296 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
297 'pv.plsql.PV_BENFT_STATUS_CHANGE.STATUS_CHANGE_NOTIFICATION.unexpected', fnd_msg_pub.get(p_encoded => 'F') );
298 end if;
299
300 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_TRUE,
301 p_count => x_msg_count,
302 p_data => x_msg_data);
303 END;
304
305
306 /*********************************************************
307 * This PROCEDURE is used to convert a comma separated list
308 * of Users who will be notified using a Workflow notification
309 * Bug 5124097 requires the CreateAdHocRole2 API to be called
310 * instead od CreateAdHocRole so that usernames with blank
311 * spaces can be supported. So this procedure converts a
312 * comma separated list like JOHN SMITH,TOM JONES,JIM BATES
313 * to a wf_directory.UserTable with these names.
314 * - JOHN SMITH
315 * - TOM JONES
316 * - JIM BATES
317 * this is called by the STATUS_CHANGE_notification API just before
318 * the call to CreateAdHocRole2.
319 *
320 * Updates : Made changes for Bug 5189270.
321 */
322 PROCEDURE CONVERT_LIST_TO_TABLE(p_role_list IN VARCHAR2
323 ,x_role_list_tbl OUT NOCOPY wf_directory.UserTable)
324 IS
325 l_index NUMBER := 1;
326 l_to_position NUMBER := 1;
327 l_from_position NUMBER := 1;
328 l_temp VARCHAR2(100);
329 BEGIN
330
331 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
332 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
333 'pv.plsql.CONVERT_LIST_TO_TABLE.GET_PRODUCTS.begin',
334 'p_role_list '||p_role_list);
335 END IF;
336
337 -- This call will give 0 for no occurances of ',' and
338 -- the index of ',' in the string if it does occur in
339 -- the string. If it is in the first position
340 -- it will return 1
341 l_to_position := INSTR(p_role_list,',',1,l_index);
342
343 IF ( LENGTH(p_role_list) > 0 AND l_to_position > 0 ) THEN
344 WHILE (l_to_position <> 0 )
345 LOOP
346
347 IF (l_from_position = 1 and l_to_position <> 0 ) THEN
348 l_temp := substr(p_role_list,l_from_position,l_to_position-1);
349 ELSIF(l_from_position > 1 and l_to_position <> 0 ) THEN
350 l_temp := substr(p_role_list,l_from_position+1,l_to_position-l_from_position-1);
351 END IF;
352
353 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
354 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
355 'pv.plsql.CONVERT_LIST_TO_TABLE.GET_PRODUCTS.begin',
356 'Next User===>'||l_temp);
357 END IF;
358
359 x_role_list_tbl(l_index) := l_temp;
360 IF l_to_position <> 0 THEN
361 l_index := l_index + 1;
362 l_from_position := l_to_position;
363 l_to_position := INSTR(p_role_list,',',1,l_index);
364 END IF;
365 END LOOP;
366
367 l_temp := substr(p_role_list,l_from_position+1,LENGTH(p_role_list)-l_from_position+1);
368 x_role_list_tbl(l_index) := l_temp;
369
370 ELSE
371
372 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
373 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
374 'pv.plsql.CONVERT_LIST_TO_TABLE.GET_PRODUCTS.begin',
375 'There were no Commas so only one user...');
376 END IF;
377
378 IF(TRIM(p_role_list) IS NOT NULL) THEN
379 x_role_list_tbl(l_index) := p_role_list;
380 END IF;
381
382 END IF;
383
384 FOR i IN 1..x_role_list_tbl.COUNT
385 LOOP
386 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
387 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
388 'pv.plsql.CONVERT_LIST_TO_TABLE.GET_PRODUCTS.begin',
389 'USER ['||x_role_list_tbl(i)||']');
390 END IF;
391 END LOOP;
392
393 EXCEPTION
394 WHEN OTHERS THEN
395
396 IF( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
397 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,
398 'pv.plsql.PV_BENFT_STATUS_CHANGE.CONVERT_LIST_TO_TABLE.unexpected', FALSE );
399 END IF;
400
401 END CONVERT_LIST_TO_TABLE;
402
403
404 --=============================================================================+
405 --| Public Procedure |
406 --| STATUS_CHANGE_LOGGING |
407 --| |
408 --| Parameters |
409 --| IN |
410 --| OUT |
411 --| |
412 --| |
413 --| NOTES |
414 --| |
415 --| HISTORY |
416 --| |
417 --==============================================================================
418 PROCEDURE STATUS_CHANGE_LOGGING(
419 p_api_version_number IN NUMBER,
420 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
421 p_commit IN VARCHAR2 := FND_API.G_FALSE,
422 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
423 P_BENEFIT_ID IN NUMBER,
424 P_STATUS IN VARCHAR2,
425 P_ENTITY_ID IN NUMBER,
426 P_PARTNER_ID IN NUMBER,
427 x_return_status OUT NOCOPY VARCHAR2,
428 x_msg_count OUT NOCOPY NUMBER,
429 x_msg_data OUT NOCOPY VARCHAR2)
430 is
431 l_api_name CONSTANT VARCHAR2(30) := 'STATUS_CHANGE_LOGGING';
432 l_api_version_number CONSTANT NUMBER := 1.0;
433
434 l_access_level varchar2(1) := 'V';
435
436 l_message_name varchar2(30);
437 l_decline_code varchar2(30);
438 l_entity_type varchar2(20);
439 l_referral_code varchar2(50);
440 l_benefit_type varchar2(10);
441 l_linked_to number;
442 l_order_id number;
443 l_log_params_tbl pvx_utility_pvt.log_params_tbl_type;
444 l_entity_number varchar2(100);
445 l_referral_code_ref varchar2(50);
446 l_approved_count NUMBER;
447 l_decline_reason_code varchar2(30);
448
449 cursor lc_access_level (pc_status varchar2, pc_benefit_type varchar2) is
450 select 'P' from pv_benft_status_maps
451 WHERE partner_status_code = pc_status
452 and benefit_type = pc_benefit_type;
453
454 cursor lc_get_entity_detail (pc_entity_id number) is
455 select ref.referral_code, ben.benefit_type_code, ref.entity_type, ref.entity_id_linked_to,
456 ref.decline_reason_code, ref.order_id
457 from pv_referrals_b ref, pv_ge_benefits_b ben
458 where ref.referral_id = pc_entity_id and ref.benefit_id = ben.benefit_id;
459
460 cursor lc_oppty_linked_flag (pc_lead_id number) is
461 select lead_number, prm_referral_code from as_leads_all where lead_id = pc_lead_id;
462
463 cursor lc_lead_linked_flag (pc_lead_id number) is
464 select lead_number, decode(source_system,'REFERRAL',source_primary_reference,NULL)
465 from as_sales_leads where sales_lead_id = pc_lead_id;
466
467 cursor lc_ref_linked_flag (pc_referral_id number) is
468 select referral_code from pv_referrals_b where referral_id = pc_referral_id;
469
470 cursor lc_event_reason IS
471 select decline_reason_code from pv_referrals_b
472 where referral_id = P_ENTITY_ID;
473
474 cursor lc_current_approvers(pc_benefit_type varchar2, pc_referral_id number) is
475 select apr.approver_id, jrre.source_name
476 from pv_ge_temp_approvers apr, jtf_rs_resource_extns jrre
477 where apr.arc_appr_for_entity_code = pc_benefit_type
478 and apr.appr_for_entity_id = pc_referral_id
479 and apr.approver_id = jrre.user_id
480 and APR.approval_status_code IN ('PENDING_APPROVAL','PENDING_DEFAULT')
481 and apr.approver_type_code = 'USER';
482
483 BEGIN
484 -- Standard call to check for call compatibility.
485
486 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
487 p_api_version_number,
488 l_api_name,
489 G_PKG_NAME) THEN
490 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
491
492 END IF;
493
494 -- Initialize message list if p_init_msg_list is set to TRUE.
495 IF FND_API.to_Boolean( p_init_msg_list )
496 THEN
497 fnd_msg_pub.initialize;
498 END IF;
499
500 if( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
501 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
502 'pv.plsql.PV_BENFT_STATUS_CHANGE.STATUS_CHANGE_LOGGING.start',
503 'Benefit id:' || p_benefit_id || '. Entity id: ' || p_entity_id ||
504 '. Status:' || p_status || '. Partner id:' || p_partner_id);
505 end if;
506
507 -- Initialize API return status to success
508 x_return_status := FND_API.G_RET_STS_SUCCESS;
509
510 open lc_get_entity_detail (pc_entity_id => p_entity_id);
511 fetch lc_get_entity_detail into l_referral_code, l_benefit_type, l_entity_type,
512 l_linked_to, l_decline_code, l_order_id;
513 close lc_get_entity_detail;
514
515 open lc_access_level(pc_status => p_status, pc_benefit_type => l_benefit_type);
516 fetch lc_access_level into l_access_level;
517 close lc_access_level;
518
519 l_log_params_tbl(1).param_name := 'REFERRAL_CODE';
520 l_log_params_tbl(1).param_value := l_referral_code;
521
522 if l_benefit_type = 'PVREFFRL' then
523
524 if p_status = 'DRAFT' then
525 l_message_name := 'PV_LG_REF_DRAFT';
526
527 elsif p_status = 'SUBMITTED_FOR_APPROVAL' then
528 l_message_name := 'PV_LG_REF_SUBMITTED';
529
530 elsif p_status = 'APPRVD_PENDNG_CSTMR_DQM' then
531 l_message_name := 'PV_LG_REF_APPRVD_PEND_DQM';
532
533 elsif p_status = 'DECLINED' then
534 -- --------------------------------------------------------------------
535 -- Before check for anything, check if the referral had been approved
536 -- at some point in time.
537 -- --------------------------------------------------------------------
538 FOR x IN (SELECT COUNT(*) approved_count
539 FROM pv_ge_history_log_vl
540 WHERE ARC_HISTORY_FOR_ENTITY_CODE = 'PVREFFRL' AND
541 history_for_entity_id = p_entity_id AND
542 message_code IN ('PV_LG_REF_APPROVED',
543 'PV_LG_REF_APPROVED_DUP_OPPTY',
544 'PV_LG_REF_APPROVED_DUP_LEAD',
545 'PV_LG_REF_APPROVED_DUP_REF'))
546 LOOP
547 l_approved_count := x.approved_count;
548 END LOOP;
549
550 -- --------------------------------------------------------------------
551 -- If l_approved_count > 0, this means this referral had been approved
552 -- at some point in time.
553 -- --------------------------------------------------------------------
554 IF (l_approved_count > 0) THEN
555 l_message_name := 'PV_LG_REF_REVALUATED_DECLINED';
556
557 -- --------------------------------------------------------------------
558 -- All other cases.
559 -- --------------------------------------------------------------------
560 ELSE
561 if l_linked_to is not null and l_entity_type = 'LEAD' then
562 l_message_name := 'PV_LG_REF_DECLINED_DUP_OPPTY';
563 l_log_params_tbl(2).param_name := 'OPP_NUMBER';
564 l_log_params_tbl(2).param_value := l_linked_to;
565
566 elsif l_linked_to is not null and l_entity_type = 'SALES_LEAD' then
567 l_message_name := 'PV_LG_REF_DECLINED_DUP_LEAD';
568 l_log_params_tbl(2).param_name := 'LEAD_NUMBER';
569 l_log_params_tbl(2).param_value := l_linked_to;
570
571 elsif l_linked_to is not null and l_entity_type = 'PVREFFRL' then
572 l_message_name := 'PV_LG_REF_DECLINED_DUP_REF';
573 l_log_params_tbl(2).param_name := 'REF_NUMBER';
574 l_log_params_tbl(2).param_value := l_LINKED_TO;
575
576 else
577 l_message_name := 'PV_LG_REF_DECLINED_REASON';
578
579 OPEN lc_event_reason;
580 FETCH lc_event_reason INTO l_decline_reason_code;
581 CLOSE lc_event_reason;
582
583 l_log_params_tbl(2).param_name := 'DECLINE_REASON';
584 l_log_params_tbl(2).param_value := l_decline_reason_code;
585 l_log_params_tbl(2).param_type := 'LOOKUP';
586 l_log_params_tbl(2).param_lookup_type := 'PV_REFERRAL_DECLINE_REASON';
587
588 end if;
589 END IF;
590
591 elsif p_status = 'APPROVED' then
592
593 -- since we are only setting prm_referral_code on new oppty/lead, if l_referral_code_ref
594 -- is null, it means that a link has happened
595
596 if l_entity_type = 'LEAD' then
597 open lc_oppty_linked_flag(pc_lead_id => l_linked_to);
598 fetch lc_oppty_linked_flag into l_entity_number, l_referral_code_ref;
599 close lc_oppty_linked_flag;
600
601 elsif l_entity_type = 'SALES_LEAD' then
602 open lc_lead_linked_flag(pc_lead_id => l_linked_to);
603 fetch lc_lead_linked_flag into l_entity_number, l_referral_code_ref;
604 close lc_lead_linked_flag;
605
606 elsif l_entity_type = 'PVREFFRL' then
607 open lc_ref_linked_flag(pc_referral_id => l_linked_to);
608 fetch lc_ref_linked_flag into l_entity_number;
609 close lc_ref_linked_flag;
610 l_referral_code_ref := null;
611 end if;
612
613 if l_referral_code_ref is null and l_entity_type = 'LEAD' then
614 l_message_name := 'PV_LG_REF_APPROVED_DUP_OPPTY';
615 l_log_params_tbl(2).param_name := 'OPP_NUMBER';
616 l_log_params_tbl(2).param_value := l_entity_number;
617
618 elsif l_referral_code_ref is null and l_entity_type = 'SALES_LEAD' then
619 l_message_name := 'PV_LG_REF_APPROVED_DUP_LEAD';
620 l_log_params_tbl(2).param_name := 'LEAD_NUMBER';
621 l_log_params_tbl(2).param_value := l_entity_number;
622
623 elsif l_referral_code_ref is null and l_entity_type = 'PVREFFRL' then
624 l_message_name := 'PV_LG_REF_APPROVED_DUP_REF';
625 l_log_params_tbl(2).param_name := 'REF_NUMBER';
626 l_log_params_tbl(2).param_value := l_entity_number;
627
628 else
629 l_message_name := 'PV_LG_REF_APPROVED';
630 end if;
631
632 elsif p_status = 'COMP_INITIATED' then
633 l_message_name := 'PV_LG_REF_COMP_INITIATED';
634
635 elsif p_status = 'COMP_ACCEPTED' then
636 l_message_name := 'PV_LG_REF_COMP_ACCEPTED';
637
638 elsif p_status = 'COMP_CANCELLED' then
639 l_message_name := 'PV_LG_REF_COMP_CANCELLED';
640
641 elsif p_status = 'COMP_AWAIT_PRT_ACCEPT' then
642 l_message_name := 'PV_LG_REF_COMP_AWAIT_PT_ACCEPT';
643
644 elsif p_status = 'COMP_BEING_NEGOTIATED' then
645 l_message_name := 'PV_LG_REF_COMP_NEGOTIATION';
646
647 elsif p_status = 'PAYMENT_BEING_PROCESSED' then
648 l_message_name := 'PV_LG_REF_PYMT_BEING_PROCESSED';
649
650 elsif p_status = 'CLOSED_FEE_PAID' then
651 l_message_name := 'PV_LG_REF_CLOSED_FEE_PAID';
652
653 elsif p_status = 'CLOSED_DEAD_LEAD' then
654 l_message_name := 'PV_LG_REF_CLOSED_DEAD_LEAD';
655
656 elsif p_status = 'CLOSED_LOST_OPPTY' then
657 l_message_name := 'PV_LG_REF_CLOSED_LOST_OPPTY';
658
659 elsif p_status = 'EXPIRED' then
660 l_message_name := 'PV_LG_REF_EXPIRED';
661
662 elsif p_status = 'MANUAL_CLOSE' then
663 l_message_name := 'PV_LG_REF_CLOSED';
664
665 elsif p_status = 'MANUAL_EXTEND' then
666 l_message_name := 'PV_LG_REF_EXTENDED';
667
668 end if;
669
670 elsif l_benefit_type = 'PVDEALRN' then
671
672 if p_status = 'DRAFT' then
673 l_message_name := 'PV_LG_DEAL_DRAFT';
674
675 elsif p_status = 'SUBMITTED_FOR_APPROVAL' then
676 l_message_name := 'PV_LG_DEAL_SUBMITTED';
677
678 elsif p_status = 'APPRVD_PENDNG_CSTMR_DQM' then
679 l_message_name := 'PV_LG_DEAL_APPRVD_PEND_DQM';
680
681 elsif p_status = 'DECLINED' then
682 -- --------------------------------------------------------------------
683 -- Before check for anything, check if the referral had been approved
684 -- at some point in time.
685 -- --------------------------------------------------------------------
686 FOR x IN (SELECT COUNT(*) approved_count
687 FROM pv_ge_history_log_vl
688 WHERE ARC_HISTORY_FOR_ENTITY_CODE = 'PVDEALRN' AND
689 history_for_entity_id = p_entity_id AND
690 message_code IN ('PV_LG_DEAL_APPROVED',
691 'PV_LG_DEAL_APPROVED_DUP_DEAL',
692 'PV_LG_DEAL_APPROVED_DUP_LEAD',
693 'PV_LG_DEAL_APPROVED_DUP_OPPTY'))
694 LOOP
695 l_approved_count := x.approved_count;
696 END LOOP;
697
698 -- --------------------------------------------------------------------
699 -- If l_approved_count > 0, this means this referral had been approved
700 -- at some point in time.
701 -- --------------------------------------------------------------------
702 IF (l_approved_count > 0) THEN
703 l_message_name := 'PV_LG_DEAL_REVALUATED_DECLINED';
704
705 -- --------------------------------------------------------------------
706 -- All other cases.
707 -- --------------------------------------------------------------------
708 ELSE
709 if l_linked_to is not null and l_entity_type = 'LEAD' then
710 l_message_name := 'PV_LG_DEAL_DECLINED_DUP_OPPTY';
711 l_log_params_tbl(2).param_name := 'OPP_NUMBER';
712 l_log_params_tbl(2).param_value := l_linked_to;
713
714 elsif l_linked_to is not null and l_entity_type = 'SALES_LEAD' then
715 l_message_name := 'PV_LG_DEAL_DECLINED_DUP_LEAD';
716 l_log_params_tbl(2).param_name := 'LEAD_NUMBER';
717 l_log_params_tbl(2).param_value := l_linked_to;
718
719 elsif l_linked_to is not null and l_entity_type = 'PVDEALRN' then
720 l_message_name := 'PV_LG_DEAL_DECLINED_DUP_DEAL';
721 l_log_params_tbl(2).param_name := 'DEAL_NUMBER';
722 l_log_params_tbl(2).param_value := l_LINKED_TO;
723
724 else
725 l_message_name := 'PV_LG_DEAL_DECLINED_REASON';
726
727 OPEN lc_event_reason;
728 FETCH lc_event_reason INTO l_decline_reason_code;
729 CLOSE lc_event_reason;
730
731 l_log_params_tbl(2).param_name := 'DECLINE_REASON';
732 l_log_params_tbl(2).param_value := l_decline_reason_code;
733 l_log_params_tbl(2).param_type := 'LOOKUP';
734 l_log_params_tbl(2).param_lookup_type := 'PV_REFERRAL_DECLINE_REASON';
735
736 end if;
737 END IF;
738
739 elsif p_status = 'APPROVED' then
740
741 if l_entity_type = 'LEAD' then
742 open lc_oppty_linked_flag(pc_lead_id => l_linked_to);
743 fetch lc_oppty_linked_flag into l_entity_number, l_referral_code_ref;
744 close lc_oppty_linked_flag;
745
746 elsif l_entity_type = 'SALES_LEAD' then
747 open lc_lead_linked_flag(pc_lead_id => l_linked_to);
748 fetch lc_lead_linked_flag into l_entity_number, l_referral_code_ref;
749 close lc_lead_linked_flag;
750
751 elsif l_entity_type = 'PVDEALRN' then
752 open lc_ref_linked_flag(pc_referral_id => l_linked_to);
753 fetch lc_ref_linked_flag into l_entity_number;
754 close lc_ref_linked_flag;
755 l_referral_code_ref := null;
756 end if;
757
758 if l_referral_code_ref is null and l_entity_type = 'LEAD' then
759 l_message_name := 'PV_LG_DEAL_APPROVED_DUP_OPPTY';
760 l_log_params_tbl(2).param_name := 'OPP_NUMBER';
761 l_log_params_tbl(2).param_value := l_entity_number;
762
763 elsif l_referral_code_ref is null and l_entity_type = 'SALES_LEAD' then
764 l_message_name := 'PV_LG_DEAL_APPROVED_DUP_LEAD';
765 l_log_params_tbl(2).param_name := 'LEAD_NUMBER';
766 l_log_params_tbl(2).param_value := l_entity_number;
767
768 elsif l_referral_code_ref is null and l_entity_type = 'PVDEALRN' then
769 l_message_name := 'PV_LG_DEAL_APPROVED_DUP_DEAL';
770 l_log_params_tbl(2).param_name := 'DEAL_NUMBER';
771 l_log_params_tbl(2).param_value := l_entity_number;
772
773 else
774 l_message_name := 'PV_LG_DEAL_APPROVED';
775 end if;
776
777
778 elsif p_status = 'CLOSED_LOST_OPPTY' then
779 l_message_name := 'PV_LG_DEAL_CLOSED_LOST_OPPTY';
780
781 elsif p_status = 'CLOSED_OPPTY_WON' then
782 l_message_name := 'PV_LG_DEAL_CLOSED_WON_OPPTY';
783
784 elsif p_status = 'EXPIRED' then
785 l_message_name := 'PV_LG_DEAL_EXPIRED';
786
787 elsif p_status = 'MANUAL_CLOSE' then
788 l_message_name := 'PV_LG_DEAL_CLOSED';
789
790 elsif p_status = 'MANUAL_EXTEND' then
791 l_message_name := 'PV_LG_DEAL_EXTENDED';
792 end if;
793
794 end if;
795
796 if l_message_name is not null then
797
798 PVX_Utility_PVT.create_history_log(
799 p_arc_history_for_entity_code => l_benefit_type,
800 p_history_for_entity_id => p_entity_id,
801 p_history_category_code => 'GENERAL',
802 p_message_code => l_message_name,
803 p_partner_id => p_partner_id,
804 p_access_level_flag => l_access_level,
805 p_interaction_level => pvx_utility_pvt.G_INTERACTION_LEVEL_50,
806 p_comments => NULL,
807 p_log_params_tbl => l_log_params_tbl,
808 x_return_status => x_return_status,
809 x_msg_count => x_msg_count,
810 x_msg_data => x_msg_data);
811
812 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
813 raise FND_API.G_EXC_ERROR;
814 end if;
815
816 end if;
817
818
819 if p_status = 'SUBMITTED_FOR_APPROVAL' then
820
821 l_message_name := null;
822
823 IF l_benefit_type = 'PVREFFRL' then
824 l_message_name := 'PV_LG_REF_REQR_APPRVD_BY_USER';
825 ELSIF l_benefit_type = 'PVDEALRN' then
826 l_message_name := 'PV_LG_DEAL_REQR_APPRVD_BY_USER';
827 ELSIF l_benefit_type = 'PVDQMAPR' then
828 l_message_name := 'PV_LG_DQM_REQR_DEDUP_BY_USER';
829 END IF;
830
831 if( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
832 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE
833 ,'pv.plsql.PV_BENFT_STATUS_CHANGE.STATUS_CHANGE_LOGGING.start'
834 ,'Approvers notification Message:'||l_message_name);
835 end if;
836
837
838 FOR l_approvers IN lc_current_approvers(pc_benefit_type => l_benefit_type
839 , pc_referral_id => p_entity_id)
840 LOOP
841
842 IF l_message_name IS NOT NULL THEN
843
844 l_log_params_tbl.DELETE;
845 l_log_params_tbl(1).param_name := 'APPROVER';
846 l_log_params_tbl(1).param_value := l_approvers.source_name;
847
848 if( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
849 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE
850 ,'pv.plsql.PV_BENFT_STATUS_CHANGE.STATUS_CHANGE_LOGGING.start'
851 ,'Logging notification for:'||l_approvers.source_name);
852 end if;
853
854 PVX_Utility_PVT.create_history_log(
855 p_arc_history_for_entity_code => l_benefit_type,
856 p_history_for_entity_id => p_entity_id,
857 p_history_category_code => 'GENERAL',
858 p_message_code => l_message_name,
859 p_partner_id => p_partner_id,
860 p_access_level_flag => 'V',
861 p_interaction_level => pvx_utility_pvt.G_INTERACTION_LEVEL_10,
862 p_comments => NULL,
863 p_log_params_tbl => l_log_params_tbl,
864 x_return_status => x_return_status,
865 x_msg_count => x_msg_count,
866 x_msg_data => x_msg_data);
867
868 END IF;
869
870 END LOOP;
871
872 --if x_return_status <> FND_API.G_RET_STS_SUCCESS then
873 -- raise FND_API.G_EXC_ERROR;
874 --end if;
875
876 end if;
877
878 IF FND_API.To_Boolean ( p_commit ) THEN
879 COMMIT WORK;
880 END IF;
881
882 if( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
883 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
884 'pv.plsql.PV_BENFT_STATUS_CHANGE.STATUS_CHANGE_LOGGING.end', 'Exiting');
885 end if;
886
887 -- Standard call to get message count and if count is 1, get message info.
888 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_TRUE,
889 p_count => x_msg_count,
890 p_data => x_msg_data);
891
892 EXCEPTION
893
894 WHEN FND_API.G_EXC_ERROR THEN
895
896 x_return_status := FND_API.G_RET_STS_ERROR ;
897 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_TRUE,
898 p_count => x_msg_count,
899 p_data => x_msg_data);
900
901 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
902
903 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
904 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_TRUE,
905 p_count => x_msg_count,
906 p_data => x_msg_data);
907
908 WHEN OTHERS THEN
909
910 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
911 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
912 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_TRUE,
913 p_count => x_msg_count,
914 p_data => x_msg_data);
915
916 END;
917
918 --=============================================================================+
919 --| Public Procedure |
920 --| Claim_Ref_Status_Change_Sub |
921 --| |
922 --| Parameters |
923 --| IN |
924 --| OUT |
925 --| |
926 --| |
927 --| NOTES |
928 --| |
929 --| HISTORY |
930 --| |
931 --==============================================================================
932 FUNCTION CLAIM_REF_STATUS_CHANGE_SUB(
933 p_subscription_guid in raw,
934 p_event in out nocopy wf_event_t)
935 RETURN VARCHAR2
936 IS
937
938 l_api_name CONSTANT VARCHAR2(30) := 'CLAIM_REF_STATUS_CHANGE_SUB';
939
940 l_rule varchar2(20);
941 l_parameter_list wf_parameter_list_t := wf_parameter_list_t();
942 l_parameter_t wf_parameter_t := wf_parameter_t(null, null);
943 l_parameter_name l_parameter_t.name%type;
944 i pls_integer;
945
946 l_msg_callback_api varchar2(60);
947 l_user_callback_api varchar2(60);
948 l_benefit_id number;
949 l_claim_id number;
950 l_status varchar2(30);
951 l_event_name varchar2(50);
952 l_entity_id varchar2(100);
953 l_partner_id number;
954 l_user_list varchar2(2000);
955 l_msg_count number;
956 l_msg_data varchar2(2000);
957 l_return_status varchar2(10);
958 l_claim_status_code varchar2(30);
959 l_org_id number;
960 l_referral_id number;
961 l_referral_status_code varchar2(25);
962
963 CURSOR c_ref_details IS
964 SELECT REF.benefit_id, REF.referral_id, REF.partner_id
965 FROM pv_referrals_b REF
966 WHERE REF.claim_id = l_claim_id;
967
968 BEGIN
969
970 l_parameter_list := p_event.getParameterList();
971 l_entity_id := p_event.getEventKey();
972 l_event_name := p_event.getEventName();
973
974 if( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
975 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
976 'pv.plsql.PV_BENFT_STATUS_CHANGE.CLAIM_REF_STATUS_CHANGE_SUB.start',
977 'Event name: ' || l_event_name || 'Event key: ' || l_entity_id);
978 end if;
979
980 if l_parameter_list is not null then
981 -- ---------------------------------------------------------------
982 -- Setting referral status based on the event name.
983 -- ---------------------------------------------------------------
984 IF (LOWER(l_event_name) = 'oracle.apps.ozf.claim.referralapproval') THEN
985 l_referral_status_code := 'COMP_AWAIT_PRT_ACCEPT';
986
987 ELSIF (LOWER(l_event_name) = 'oracle.apps.ozf.claim.paymentpaid') THEN
988 l_referral_status_code := 'CLOSED_FEE_PAID';
989
990 ELSIF (LOWER(l_event_name) = 'oracle.apps.ozf.claim.updatestatus') THEN
991 -- -----------------------------------------------------------------
992 -- if status_code is 'CANCELLED'
993 -- -----------------------------------------------------------------
994 l_referral_status_code := 'COMP_CANCELLED';
995 END IF;
996
997
998 -- ---------------------------------------------------------------
999 -- Retrieving parameters.
1000 -- ---------------------------------------------------------------
1001 i := l_parameter_list.first;
1002 while ( i <= l_parameter_list.last) loop
1003
1004 l_parameter_name := null;
1005 l_parameter_name := l_parameter_list(i).getName();
1006
1007 IF (l_parameter_name = 'CLAIM_ID') then
1008 l_claim_id := l_parameter_list(i).getValue();
1009
1010 ELSIF (l_parameter_name = 'ORG_ID') then
1011 l_org_id := l_parameter_list(i).getValue();
1012
1013 ELSIF (l_parameter_name = 'STATUS_CODE') then
1014 l_claim_status_code := l_parameter_list(i).getValue();
1015 END IF;
1016
1017 i := l_parameter_list.next(i);
1018 end loop;
1019
1020 -- -----------------------------------------------------------------
1021 -- If the event is claim update status and
1022 -- claim status is not 'CANCELLED', exit the code. We don't need
1023 -- to process this event.
1024 -- -----------------------------------------------------------------
1025 IF (LOWER(l_event_name) = 'oracle.apps.ozf.claim.updatestatus' AND
1026 UPPER(l_claim_status_code) <> 'CANCELLED')
1027 THEN
1028 RETURN 'SUCCESS';
1029 END IF;
1030
1031 IF (LOWER(l_event_name) = 'oracle.apps.ozf.claim.updatestatus' AND
1032 l_claim_id IS NULL)
1033 THEN
1034 if( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1035 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1036 'pv.plsql.PV_BENFT_STATUS_CHANGE.CLAIM_REF_STATUS_CHANGE_SUB',
1037 'Event name: ' || l_event_name || ' ' ||
1038 '--> There is no claim ID for this event.');
1039 end if;
1040
1041 RETURN 'SUCCESS';
1042 END IF;
1043
1044 -- ----------------------------------------------------------------
1045 -- Retrieve benefit_id, referral_id, and partner_id from claim_id.
1046 -- ----------------------------------------------------------------
1047 FOR x IN c_ref_details LOOP
1048 l_referral_id := x.referral_id;
1049 l_benefit_id := x.benefit_id;
1050 l_partner_id := x.partner_id;
1051 END LOOP;
1052
1053 IF (l_referral_id IS NULL) THEN
1054 if( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1055 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1056 'pv.plsql.PV_BENFT_STATUS_CHANGE.CLAIM_REF_STATUS_CHANGE_SUB',
1057 'Event name: ' || l_event_name || ' ' ||
1058 'claim_id : ' || l_claim_id || ' ' ||
1059 '--> There is no corresponding referral for this claim.');
1060 end if;
1061
1062 RETURN 'SUCCESS';
1063 END IF;
1064
1065 -- ----------------------------------------------------------------
1066 -- Update Referral Status
1067 -- ----------------------------------------------------------------
1068 UPDATE pv_referrals_b
1069 SET referral_status = l_referral_status_code
1070 WHERE referral_id = l_referral_id;
1071
1072
1073 -- -------------------------------------------------
1074 -- Raise business event
1075 -- oracle.apps.pv.benefit.referral.statusChange
1076 -- -------------------------------------------------
1077 pv_benft_status_change.status_change_raise(
1078 p_api_version_number => 1.0,
1079 p_init_msg_list => FND_API.G_FALSE,
1080 p_commit => FND_API.G_FALSE,
1081 p_event_name => 'oracle.apps.pv.benefit.referral.statusChange',
1082 p_benefit_id => l_benefit_id,
1083 p_entity_id => l_referral_id,
1084 p_status_code => l_referral_status_code,
1085 p_partner_id => l_partner_id,
1086 p_msg_callback_api => 'pv_benft_status_change.REFERRAL_SET_MSG_ATTRS',
1087 p_user_callback_api => 'pv_benft_status_change.REFERRAL_RETURN_USERLIST',
1088 x_return_status => l_return_status,
1089 x_msg_count => l_msg_count,
1090 x_msg_data => l_msg_data);
1091
1092 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
1093 raise FND_API.G_EXC_ERROR;
1094 end if;
1095
1096 -- -------------------------------------------------
1097 -- Log the event.
1098 -- -------------------------------------------------
1099 STATUS_CHANGE_LOGGING(
1100 p_api_version_number => 1.0,
1101 p_init_msg_list => FND_API.G_FALSE,
1102 p_commit => FND_API.G_FALSE,
1103 p_benefit_id => l_benefit_id,
1104 P_STATUS => l_referral_status_code,
1105 p_entity_id => l_referral_id,
1106 p_partner_id => l_partner_id,
1107 x_return_status => l_return_status,
1108 x_msg_count => l_msg_count,
1109 x_msg_data => l_msg_data
1110 );
1111
1112 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
1113 raise FND_API.G_EXC_ERROR;
1114 end if;
1115
1116 end if;
1117
1118 if( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1119 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1120 'pv.plsql.PV_BENFT_STATUS_CHANGE.CLAIM_REF_STATUS_CHANGE_SUB.end', 'Exiting');
1121 end if;
1122
1123 RETURN 'SUCCESS';
1124
1125 EXCEPTION
1126 WHEN OTHERS THEN
1127
1128 if( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1129 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,
1130 'pv.plsql.PV_BENFT_STATUS_CHANGE.CLAIM_REF_STATUS_CHANGE_SUB.unexpected', FALSE );
1131 end if;
1132
1133 fnd_msg_pub.Count_And_Get(p_encoded => FND_API.G_TRUE
1134 ,p_count => l_msg_count
1135 ,p_data => l_msg_data);
1136
1137 WF_CORE.CONTEXT(G_PKG_NAME, L_API_NAME, P_EVENT.GETEVENTNAME(), P_SUBSCRIPTION_GUID);
1138 WF_EVENT.SETERRORINFO(P_EVENT,'ERROR');
1139 RETURN 'ERROR';
1140 END CLAIM_REF_STATUS_CHANGE_SUB;
1141 -- ====================End of CLAIM_REF_STATUS_CHANGE_SUB=======================
1142
1143
1144
1145
1146 PROCEDURE REFERRAL_SET_MSG_ATTRS(
1147 p_itemtype IN VARCHAR2,
1148 p_itemkey IN VARCHAR2,
1149 P_ENTITY_ID IN NUMBER,
1150 P_USER_TYPE IN VARCHAR2,
1151 P_STATUS IN VARCHAR2) IS
1152
1153 l_api_name CONSTANT VARCHAR2(30) := 'REFERRAL_SET_MSG_ATTRS';
1154
1155 l_referral_id number;
1156 l_referral_code varchar2(50);
1157 l_referral_name varchar2(100);
1158 l_comp_amount varchar2(20);
1159 l_partner_org_name varchar2(100);
1160 l_partner_cont_name varchar2(100);
1161 l_creator_name varchar2(100);
1162 l_customer_address varchar2(200);
1163 l_customer_name varchar2(250);
1164 l_customer_cont_name varchar2(100);
1165 l_entity_status varchar2(100);
1166 l_entity_creation_date date;
1167 l_notes_clob CLOB;
1168 l_notes_varchar varchar2(4000);
1169 l_notes varchar2(2000);
1170 l_note_size binary_integer := 4000;
1171 l_partner_url varchar2(500);
1172 l_url varchar2(500);
1173 l_function_id number;
1174
1175 /* Dynamic sql for backward compatibility
1176 of CASE WHEN on 8i PL/SQL */
1177 TYPE t_ref_cursor IS REF CURSOR;
1178 l_get_referral_details t_ref_cursor;
1179 l_get_referral_details_sql VARCHAR2(3000);
1180
1181
1182 cursor lc_get_notes(pc_entity_type varchar2, pc_entity_id number) is
1183 select notes, NOTES_DETAIL
1184 from jtf_notes_vl
1185 where source_object_code = pc_entity_type
1186 AND SOURCE_OBJECT_ID = pc_entity_id
1187 AND NOTE_STATUS = 'E' -- only publish notes
1188 ORDER BY CREATION_DATE DESC;
1189
1190 cursor lc_get_function (pc_function_name varchar2) is
1191 select function_id from fnd_form_functions
1192 where function_name = pc_function_name;
1193
1194
1195 BEGIN
1196
1197 if( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1198 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1199 'pv.plsql.PV_BENFT_STATUS_CHANGE.REFERRAL_SET_MSG_ATTRS.start',
1200 'Item type:' || p_itemtype || 'Item key:' || p_itemkey || '. Entity id: ' ||
1201 p_entity_id || '. Status:' || p_status || '. User type: ' || p_user_type);
1202 end if;
1203
1204 /* Constructing Dynamic sql for backward compatibility
1205 * of CASE WHEN on 8i PL/SQL
1206 */
1207 l_get_referral_details_sql :=
1208 'select
1209 a.referral_id,
1210 a.referral_code,
1211 a.referral_name,
1212 c.party_name,
1213 a.customer_name,
1214 ARP_ADDR_LABEL_PKG.FORMAT_ADDRESS_LABEL(Null,a.customer_address1,a.customer_address2,
1215 a.customer_address3,a.customer_address4,a.customer_CITY,a.customer_COUNTY,a.customer_STATE,
1216 a.customer_PROVINCE,a.customer_POSTAL_CODE,Null,a.customer_country,Null,Null,Null,Null,Null,
1217 NULL,NULL,NULL,2000,1,1) ADDRESS,
1218 hzp.party_name pt_contact_name,
1219 (CASE
1220 WHEN creator.source_first_name IS NULL AND creator.source_last_name IS NULL
1221 AND creator.category = ''PARTY'' THEN
1222 (SELECT hzp.party_name
1223 FROM hz_relationships hzr, hz_parties hzp
1224 WHERE hzr.party_id = creator.source_id
1225 AND hzr.subject_type=''PERSON''
1226 AND hzr.subject_id = hzp.party_id
1227 AND hzr.object_type= ''ORGANIZATION'')
1228 ELSE
1229 creator.source_name
1230 END) creator_name,
1231 a.customer_contact_first_name || '' '' || a.customer_contact_last_name,
1232 lkup.meaning,
1233 A.creation_date,
1234 a.actual_compensation_amt || '' '' || a.currency_code
1235 from
1236 pv_referrals_vl a,
1237 pv_partner_profiles b,
1238 hz_parties c,
1239 jtf_rs_resource_extns pt_cont,
1240 jtf_rs_resource_extns creator,
1241 pv_lookups lkup,
1242 hz_relationships hzr,
1243 hz_parties hzp
1244 where a.referral_id = :1
1245 and a.partner_id = b.partner_id
1246 and b.partner_party_id = c.party_id
1247 and a.partner_contact_resource_id = pt_cont.resource_id
1248 and a.created_by = creator.user_id
1249 and a.referral_status = lkup.lookup_code
1250 and lkup.lookup_type = ''PV_BENEFIT_ENTITY_STATUS''
1251 AND hzr.party_id = pt_cont.source_id
1252 AND hzr.subject_type=''PERSON''
1253 AND hzr.subject_id = hzp.party_id
1254 AND hzr.object_type= ''ORGANIZATION'' ';
1255
1256
1257 OPEN l_get_referral_details FOR l_get_referral_details_sql
1258 USING p_entity_id;
1259
1260 FETCH l_get_referral_details INTO l_referral_id, l_referral_code, l_referral_name,
1261 l_partner_org_name, l_customer_name, l_customer_address, l_partner_cont_name,
1262 l_creator_name, l_customer_cont_name, l_entity_status, l_entity_creation_date, l_comp_amount;
1263 CLOSE l_get_referral_details;
1264
1265 open lc_get_notes(pc_entity_type => p_itemtype, pc_entity_id => p_entity_id);
1266 fetch lc_get_notes into l_notes, l_notes_clob;
1267 close lc_get_notes;
1268
1269 l_notes_varchar := dbms_lob.substr(lob_loc => l_notes_clob, amount => l_note_size, offset => 1);
1270 if l_notes_varchar is null or length(l_notes_varchar) = 0 then
1271 l_notes_varchar := l_notes;
1272 end if;
1273
1274
1275 l_partner_url := fnd_profile.value('PV_WORKFLOW_ISTORE_URL');
1276 l_partner_url := substr(l_partner_url,1,instr(l_partner_url,'/',1,3)-1); -- just get the http://<host>:<port>
1277
1278 if p_itemtype = 'PVREFFRL' then
1279
1280 -- we need vendor and partner side functions because they point to different OAHP and OAPB paraneters
1281 -- we don't want to hardcode the parameters here to allow users to customize the web_html_call
1282 -- in order to support this, we will have 2 functions
1283
1284 open lc_get_function (pc_function_name => 'PV_REF_NOTIF_LINK_VENDOR');
1285 fetch lc_get_function into l_function_id;
1286 close lc_get_function;
1287
1288 l_url := fnd_run_function.get_run_function_url(l_function_id,-1,-1,0, 'referralId=' ||
1289 L_REFERRAL_ID || '&entityType=PVREFFRL');
1290
1291 wf_engine.SetItemAttrText( itemtype => p_itemtype,
1292 itemkey => p_itemKey,
1293 aname => 'VENDOR_LOGIN_URL',
1294 avalue => l_url);
1295
1296 open lc_get_function (pc_function_name => 'PV_REFERRAL_OVERVIEW_PT');
1297 fetch lc_get_function into l_function_id;
1298 close lc_get_function;
1299
1300 l_url := fnd_run_function.get_run_function_url(l_function_id,-1,-1,0, 'referralId=' ||
1301 L_REFERRAL_ID || '&entityType=PVREFFRL');
1302
1303 if length(l_partner_url) > 0 then -- if profile is set, use it for partner URL
1304
1305 l_url := l_partner_url || substr(l_url, instr(l_url,'/',1,3));
1306
1307 end if;
1308
1309 wf_engine.SetItemAttrText( itemtype => p_itemtype,
1310 itemkey => p_itemKey,
1311 aname => 'PARTNER_LOGIN_URL',
1312 avalue => l_url);
1313
1314 wf_engine.SetItemAttrText( itemtype => p_itemtype,
1315 itemkey => p_itemKey,
1316 aname => 'REFERRAL_CODE',
1317 avalue => l_referral_code);
1318
1319 wf_engine.SetItemAttrText( itemtype => p_itemtype,
1320 itemkey => p_itemKey,
1321 aname => 'REFERRAL_NAME',
1322 avalue => l_referral_name);
1323
1324 wf_engine.SetItemAttrText( itemtype => p_itemtype,
1325 itemkey => p_itemKey,
1326 aname => 'REFERRAL_CREATOR',
1327 avalue => l_creator_name);
1328
1329 wf_engine.SetItemAttrText( itemtype => p_itemtype,
1330 itemkey => p_itemKey,
1331 aname => 'REFERRAL_STATUS',
1332 avalue => l_entity_status);
1333
1334 wf_engine.SetItemAttrDate( itemtype => p_itemtype,
1335 itemkey => p_itemKey,
1336 aname => 'REFERRAL_CREATE_DATE',
1337 avalue => l_entity_creation_date);
1338
1339 wf_engine.SetItemAttrText( itemtype => p_itemtype,
1340 itemkey => p_itemKey,
1341 aname => 'REFERRAL_COMMISSION_AMT',
1342 avalue => l_comp_amount);
1343
1344 elsif p_itemtype = 'PVDEALRN' then
1345
1346 open lc_get_function (pc_function_name => 'PV_REF_NOTIF_LINK_VENDOR');
1347 fetch lc_get_function into l_function_id;
1348 close lc_get_function;
1349
1350 l_url := fnd_run_function.get_run_function_url(l_function_id,-1,-1,0, 'referralId=' ||
1351 L_REFERRAL_ID || '&entityType=PVDEALRN');
1352
1353 wf_engine.SetItemAttrText( itemtype => p_itemtype,
1354 itemkey => p_itemKey,
1355 aname => 'VENDOR_LOGIN_URL',
1356 avalue => l_url);
1357
1358 open lc_get_function (pc_function_name => 'PV_DEALRN_OVERVIEW_PT');
1359 fetch lc_get_function into l_function_id;
1360 close lc_get_function;
1361
1362 l_url := fnd_run_function.get_run_function_url(l_function_id,-1,-1,0, 'referralId=' ||
1363 L_REFERRAL_ID || '&entityType=PVDEALRN');
1364
1365 if length(l_partner_url) > 0 then -- if profile is set, use it for partner URL
1366
1367 l_url := l_partner_url || substr(l_url, instr(l_url,'/',1,3));
1368
1369 end if;
1370
1371 wf_engine.SetItemAttrText( itemtype => p_itemtype,
1372 itemkey => p_itemKey,
1373 aname => 'PARTNER_LOGIN_URL',
1374 avalue => l_url);
1375
1376 wf_engine.SetItemAttrText( itemtype => p_itemtype,
1377 itemkey => p_itemKey,
1378 aname => 'DEAL_CODE',
1379 avalue => l_referral_code);
1380
1381 wf_engine.SetItemAttrText( itemtype => p_itemtype,
1382 itemkey => p_itemKey,
1383 aname => 'DEAL_NAME',
1384 avalue => l_referral_name);
1385
1386 wf_engine.SetItemAttrText( itemtype => p_itemtype,
1387 itemkey => p_itemKey,
1388 aname => 'DEAL_CREATOR',
1389 avalue => l_creator_name);
1390
1391 wf_engine.SetItemAttrText( itemtype => p_itemtype,
1392 itemkey => p_itemKey,
1393 aname => 'DEAL_STATUS',
1394 avalue => l_entity_status);
1395
1396 wf_engine.SetItemAttrDate( itemtype => p_itemtype,
1397 itemkey => p_itemKey,
1398 aname => 'DEAL_CREATE_DATE',
1399 avalue => l_entity_creation_date);
1400 end if;
1401
1402 wf_engine.SetItemAttrNumber( itemtype => p_itemtype,
1403 itemkey => p_itemKey,
1404 aname => 'ENTITY_ID',
1405 avalue => l_referral_id);
1406
1407 wf_engine.SetItemAttrDate( itemtype => p_itemtype,
1408 itemkey => p_itemKey,
1409 aname => 'TODAY_DATE',
1410 avalue => sysdate);
1411
1412 wf_engine.SetItemAttrText( itemtype => p_itemtype,
1413 itemkey => p_itemKey,
1414 aname => 'PARTNER_ORG_NAME',
1415 avalue => l_partner_org_name);
1416
1417 wf_engine.SetItemAttrText( itemtype => p_itemtype,
1418 itemkey => p_itemKey,
1419 aname => 'CUSTOMER_ADDRESS',
1420 avalue => l_customer_address);
1421
1422 wf_engine.SetItemAttrText( itemtype => p_itemtype,
1423 itemkey => p_itemKey,
1424 aname => 'CUSTOMER_NAME',
1425 avalue => l_customer_name);
1426
1427 wf_engine.SetItemAttrText( itemtype => p_itemtype,
1428 itemkey => p_itemKey,
1429 aname => 'CUSTOMER_CONTACT',
1430 avalue => l_customer_cont_name);
1431
1432 wf_engine.SetItemAttrText( itemtype => p_itemtype,
1433 itemkey => p_itemKey,
1434 aname => 'PT_CONTACT_NAME',
1435 avalue => l_partner_cont_name);
1436
1437 wf_engine.SetItemAttrText( itemtype => p_itemtype,
1438 itemkey => p_itemKey,
1439 aname => 'LAST_NOTE',
1440 avalue => l_notes_varchar);
1441
1442 if( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1443 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1444 'pv.plsql.PV_BENFT_STATUS_CHANGE.REFERRAL_SET_MSG_ATTRS.end', 'Exiting');
1445 end if;
1446
1447 END;
1448
1449 FUNCTION REFERRAL_RETURN_USERLIST(
1450 p_benefit_type IN VARCHAR2,
1451 P_ENTITY_ID IN NUMBER,
1452 P_USER_ROLE IN VARCHAR2,
1453 P_STATUS IN VARCHAR2) RETURN VARCHAR2
1454 is
1455 l_role_list varchar2(1000);
1456 l_partner_id number;
1457
1458 cursor lc_get_ext_super_users(pc_permission varchar2,
1459 pc_partner_id number) is
1460 SELECT
1461 usr.user_name
1462 FROM
1463 pv_partner_profiles prof,
1464 hz_relationships pr2,
1465 jtf_rs_resource_extns pj,
1466 fnd_user usr
1467 WHERE
1468 prof.partner_id = pc_partner_id
1469 and prof.partner_party_id = pr2.object_id
1470 and pr2.subject_table_name = 'HZ_PARTIES'
1471 and pr2.object_table_name = 'HZ_PARTIES'
1472 and pr2.directional_flag = 'F'
1473 and pr2.relationship_code = 'EMPLOYEE_OF'
1474 and pr2.relationship_type = 'EMPLOYMENT'
1475 and (pr2.end_date is null or pr2.end_date > sysdate)
1476 and pr2.status = 'A'
1477 and pr2.party_id = pj.source_id
1478 and pj.category = 'PARTY'
1479 and usr.user_id = pj.user_id
1480 and (usr.end_date > sysdate OR usr.end_date IS NULL)
1481 and exists(select 1 from jtf_auth_principal_maps jtfpm,
1482 jtf_auth_principals_b jtfp1, jtf_auth_domains_b jtfd,
1483 jtf_auth_principals_b jtfp2, jtf_auth_role_perms jtfrp,
1484 jtf_auth_permissions_b jtfperm
1485 where PJ.user_name = jtfp1.principal_name
1486 and jtfp1.is_user_flag=1
1487 and jtfp1.jtf_auth_principal_id=jtfpm.jtf_auth_principal_id
1488 and jtfpm.jtf_auth_parent_principal_id = jtfp2.jtf_auth_principal_id
1489 and jtfp2.is_user_flag=0
1490 and jtfp2.jtf_auth_principal_id = jtfrp.jtf_auth_principal_id
1491 and jtfrp.positive_flag = 1
1492 and jtfrp.jtf_auth_permission_id = jtfperm.jtf_auth_permission_id
1493 and jtfperm.permission_name = pc_permission
1494 and jtfd.jtf_auth_domain_id=jtfpm.jtf_auth_domain_id
1495 and jtfd.domain_name='CRM_DOMAIN' );
1496
1497 cursor lc_get_int_super_users(pc_permission varchar2) is
1498 select usr.user_name
1499 from jtf_auth_principal_maps jtfpm,
1500 jtf_auth_principals_b jtfp1, jtf_auth_domains_b jtfd,
1501 jtf_auth_principals_b jtfp2, jtf_auth_role_perms jtfrp,
1502 jtf_auth_permissions_b jtfperm, jtf_rs_resource_extns pj,
1503 fnd_user usr
1504 where PJ.user_name = jtfp1.principal_name
1505 and pj.category = 'EMPLOYEE'
1506 and usr.user_id = pj.user_id
1507 and (usr.end_date > sysdate OR usr.end_date IS NULL)
1508 and jtfp1.is_user_flag=1
1509 and jtfp1.jtf_auth_principal_id=jtfpm.jtf_auth_principal_id
1510 and jtfpm.jtf_auth_parent_principal_id = jtfp2.jtf_auth_principal_id
1511 and jtfp2.is_user_flag=0
1512 and jtfp2.jtf_auth_principal_id = jtfrp.jtf_auth_principal_id
1513 and jtfrp.positive_flag = 1
1514 and jtfrp.jtf_auth_permission_id = jtfperm.jtf_auth_permission_id
1515 and jtfperm.permission_name = pc_permission
1516 and jtfd.jtf_auth_domain_id=jtfpm.jtf_auth_domain_id
1517 and jtfd.domain_name='CRM_DOMAIN';
1518
1519 cursor lc_get_partner_id(pc_entity_id number) is
1520 select partner_id from pv_referrals_b where referral_id = pc_entity_id;
1521
1522 cursor lc_get_pt_cont(pc_entity_id number) is
1523 select fnd.user_name
1524 from fnd_user fnd, pv_referrals_b ref, jtf_rs_resource_extns jtf
1525 where ref.partner_contact_resource_id = jtf.resource_id
1526 and jtf.user_id = fnd.user_id
1527 and ref.referral_id = pc_entity_id;
1528
1529 cursor lc_get_lead_owner (pc_entity_id number) is
1530 select c.user_name
1531 from as_sales_leads a, pv_referrals_b b, jtf_rs_resource_extns c
1532 where b.referral_id = pc_entity_id
1533 and b.entity_id_linked_to = a.sales_lead_id
1534 and a.assign_to_salesforce_id = c.resource_id;
1535
1536 cursor lc_get_oppty_slsteam (pc_entity_id number) is
1537 select c.user_name
1538 from as_accesses_all a, pv_referrals_b b, jtf_rs_resource_extns c
1539 where b.referral_id = pc_entity_id
1540 and b.entity_id_linked_to = a.lead_id
1541 and a.salesforce_id = c.resource_id
1542 and c.category = 'EMPLOYEE';
1543
1544 -- bug 3671420
1545 cursor lc_get_all_approvers (pc_benefit_type varchar2, pc_entity_id number) is
1546 select distinct fnd_user.user_name
1547 from pv_ge_temp_approvers apr, fnd_user
1548 where apr.arc_appr_for_entity_code = pc_benefit_type
1549 and apr.appr_for_entity_id = pc_entity_id
1550 and apr.approver_id = fnd_user.user_id
1551 AND APR.approval_status_code IN ('PENDING_APPROVAL','PENDING_DEFAULT','APPROVED')
1552 and apr.approver_type_code = 'USER';
1553
1554 begin
1555 if( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1556 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1557 'pv.plsql.PV_BENFT_STATUS_CHANGE.REFERRAL_RETURN_USERLIST.start',
1558 'Benefit type:' || p_benefit_type || '. Entity id: ' || p_entity_id ||
1559 '. Status:' || p_status || '. User type: ' || p_user_role);
1560 end if;
1561
1562 open lc_get_partner_id(pc_entity_id => p_entity_id);
1563 fetch lc_get_partner_id into l_partner_id;
1564 close lc_get_partner_id;
1565
1566 if p_user_role = 'DEAL_SUPERUSER_EXT' then
1567
1568 for l_row in lc_get_ext_super_users(pc_permission => 'PV_DEAL_SUPERUSER',
1569 pc_partner_id => l_partner_id) loop
1570 l_role_list := l_role_list || ',' || l_row.user_name;
1571 end loop;
1572 l_role_list := substr(l_role_list,2);
1573
1574 elsif p_user_role = 'REFERRAL_SUPERUSER_EXT' then
1575
1576 for l_row in lc_get_ext_super_users(pc_permission => 'PV_REFERRAL_SUPERUSER',
1577 pc_partner_id => l_partner_id) loop
1578 l_role_list := l_role_list || ',' || l_row.user_name;
1579 end loop;
1580 l_role_list := substr(l_role_list,2);
1581
1582 elsif p_user_role = 'DEAL_SUPERUSER_INT' then
1583
1584 for l_row in lc_get_int_super_users(pc_permission => 'PV_DEAL_SUPERUSER') loop
1585 l_role_list := l_role_list || ',' || l_row.user_name;
1586 end loop;
1587 l_role_list := substr(l_role_list,2);
1588
1589 elsif p_user_role = 'REFERRAL_SUPERUSER_INT' then
1590
1591 for l_row in lc_get_int_super_users(pc_permission => 'PV_REFERRAL_SUPERUSER') loop
1592 l_role_list := l_role_list || ',' || l_row.user_name;
1593 end loop;
1594 l_role_list := substr(l_role_list,2);
1595
1596 elsif p_user_role = 'PT_CONTACT' then
1597
1598 for l_row in lc_get_pt_cont(pc_entity_id => p_entity_id) loop
1599 l_role_list := l_role_list || ',' || l_row.user_name;
1600 end loop;
1601 l_role_list := substr(l_role_list,2);
1602
1603 elsif p_user_role = 'LEAD_OWNER' then
1604
1605 if fnd_profile.value('PV_COPY_OWNER_ON_NOTIFICATION') = 'Y' then
1606
1607 for l_row in lc_get_lead_owner(pc_entity_id => p_entity_id) loop
1608 l_role_list := l_role_list || ',' || l_row.user_name;
1609 end loop;
1610 l_role_list := substr(l_role_list,2);
1611
1612 end if;
1613
1614 elsif p_user_role = 'OPPTY_SLSTEAM_INT' then
1615
1616 if fnd_profile.value('PV_COPY_OWNER_ON_NOTIFICATION') = 'Y' then
1617
1618 for l_row in lc_get_oppty_slsteam(pc_entity_id => p_entity_id) loop
1619 l_role_list := l_role_list || ',' || l_row.user_name;
1620 end loop;
1621 l_role_list := substr(l_role_list,2);
1622
1623 end if;
1624
1625 elsif p_user_role = 'BENEFIT_APPROVER' and p_status <> 'SUBMITTED_FOR_APPROVAL' then
1626
1627 for l_row in lc_get_all_approvers(pc_benefit_type => p_benefit_type, pc_entity_id => p_entity_id) loop
1628 l_role_list := l_role_list || ',' || l_row.user_name;
1629 end loop;
1630 l_role_list := substr(l_role_list,2);
1631
1632 else
1633 if( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1634 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1635 'pv.plsql.PV_BENFT_STATUS_CHANGE.REFERRAL_RETURN_USERLIST.info',
1636 'Unrecognized user role:' || p_user_role);
1637 END IF;
1638 end if;
1639
1640 if( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1641 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1642 'pv.plsql.PV_BENFT_STATUS_CHANGE.REFERRAL_RETURN_USERLIST.end', 'Exiting');
1643 end if;
1644
1645 return l_role_list;
1646 end;
1647
1648 FUNCTION STATUS_CHANGE_SUB(
1649 p_subscription_guid in raw,
1650 p_event in out nocopy wf_event_t) return varchar2
1651 is
1652
1653 l_api_name CONSTANT VARCHAR2(30) := 'STATUS_CHANGE_SUB';
1654
1655 l_rule varchar2(20);
1656 l_parameter_list wf_parameter_list_t := wf_parameter_list_t();
1657 l_parameter_t wf_parameter_t := wf_parameter_t(null, null);
1658 l_parameter_name l_parameter_t.name%type;
1659 i pls_integer;
1660
1661 l_msg_callback_api varchar2(60);
1662 l_user_callback_api varchar2(60);
1663 l_benefit_id number;
1664 l_status varchar2(30);
1665 l_event_name varchar2(50);
1666 l_entity_id number;
1667 l_partner_id number;
1668 l_user_list varchar2(2000);
1669 l_msg_count number;
1670 l_msg_data varchar2(2000);
1671 l_return_status varchar2(10);
1672
1673 BEGIN
1674
1675 l_parameter_list := p_event.getParameterList();
1676 l_entity_id := p_event.getEventKey();
1677 l_event_name := p_event.getEventName();
1678
1679 if( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1680 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1681 'pv.plsql.PV_BENFT_STATUS_CHANGE.STATUS_CHANGE_SUB.start',
1682 'Event name: ' || l_event_name || 'Event key: ' || l_entity_id);
1683 end if;
1684
1685 if l_parameter_list is not null then
1686 i := l_parameter_list.first;
1687 while ( i <= l_parameter_list.last) loop
1688
1689 l_parameter_name := null;
1690 l_parameter_name := l_parameter_list(i).getName();
1691
1692 IF l_parameter_name = 'MSG_CALLBACK_API' then
1693 l_msg_callback_api := l_parameter_list(i).getValue();
1694 elsif l_parameter_name = 'USER_CALLBACK_API' then
1695 l_user_callback_api := l_parameter_list(i).getValue();
1696 elsif l_parameter_name = 'BENEFIT_ID' THEN
1697 l_benefit_id := l_parameter_list(i).getValue();
1698 elsif l_parameter_name = 'STATUS_CODE' THEN
1699 l_status := l_parameter_list(i).getValue();
1700 elsif l_parameter_name = 'PARTNER_ID' THEN
1701 l_partner_id := l_parameter_list(i).getValue();
1702 END IF;
1703
1704 i := l_parameter_list.next(i);
1705 end loop;
1706
1707 pv_benft_status_change.STATUS_CHANGE_notification(
1708 p_api_version_number => 1.0,
1709 p_init_msg_list => fnd_api.G_FALSE,
1710 P_BENEFIT_ID => l_benefit_id,
1711 P_STATUS => l_status,
1712 P_ENTITY_ID => l_entity_id,
1713 P_PARTNER_ID => l_partner_id,
1714 p_msg_callback_api => l_msg_callback_api,
1715 p_user_callback_api => l_user_callback_api,
1716 x_return_status => l_return_status,
1717 x_msg_count => l_msg_count,
1718 x_msg_data => l_msg_data);
1719
1720 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1721 raise FND_API.G_EXC_ERROR;
1722 end if;
1723
1724 end if;
1725
1726 if( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1727 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1728 'pv.plsql.PV_BENFT_STATUS_CHANGE.STATUS_CHANGE_SUB.end', 'Exiting');
1729 end if;
1730
1731 RETURN 'SUCCESS';
1732
1733 EXCEPTION
1734 WHEN OTHERS THEN
1735
1736 if( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1737 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,
1738 'pv.plsql.PV_BENFT_STATUS_CHANGE.STATUS_CHANGE_SUB.unexpected', FALSE );
1739 end if;
1740
1741 fnd_msg_pub.Count_And_Get(p_encoded => FND_API.G_TRUE
1742 ,p_count => l_msg_count
1743 ,p_data => l_msg_data);
1744
1745 WF_CORE.CONTEXT(G_PKG_NAME, L_API_NAME, P_EVENT.GETEVENTNAME(), P_SUBSCRIPTION_GUID);
1746 WF_EVENT.SETERRORINFO(P_EVENT,'ERROR');
1747 RETURN 'ERROR';
1748 END;
1749
1750 PROCEDURE STATUS_CHANGE_RAISE(
1751 p_api_version_number IN NUMBER,
1752 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1753 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1754 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1755 p_event_name IN VARCHAR2,
1756 p_benefit_id IN NUMBER,
1757 p_entity_id IN NUMBER,
1758 p_status_code IN VARCHAR2,
1759 p_partner_id IN NUMBER,
1760 p_msg_callback_api IN VARCHAR2,
1761 p_user_callback_api IN VARCHAR2,
1762 x_return_status OUT NOCOPY VARCHAR2,
1763 x_msg_count OUT NOCOPY NUMBER,
1764 x_msg_data OUT NOCOPY VARCHAR2) is
1765
1766 l_api_name CONSTANT VARCHAR2(30) := 'STATUS_CHANGE_RAISE';
1767 l_api_version_number CONSTANT NUMBER := 1.0;
1768
1769 l_return_status varchar2(30);
1770 l_msg_count number;
1771 l_msg_data varchar2(1000);
1772
1773 l_parameter_list wf_parameter_list_t := wf_parameter_list_t();
1774 l_parameter_t wf_parameter_t := wf_parameter_t(null, null);
1775
1776 BEGIN
1777 -- Standard call to check for call compatibility.
1778
1779 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
1780 p_api_version_number,
1781 l_api_name,
1782 G_PKG_NAME) THEN
1783 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1784
1785 END IF;
1786
1787 -- Initialize message list if p_init_msg_list is set to TRUE.
1788 IF FND_API.to_Boolean( p_init_msg_list )
1789 THEN
1790 fnd_msg_pub.initialize;
1791 END IF;
1792
1793 if( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1794 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1795 'pv.plsql.PV_BENFT_STATUS_CHANGE.STATUS_CHANGE_RAISE.begin',
1796 'Event:' || p_event_name || '. Benefit id:' || p_benefit_id ||
1797 '. Status code:' || p_status_code || '. Partner id:' || p_partner_id ||
1798 '. Message callback API: ' || p_msg_callback_api ||
1799 '. User Callback API: ' || p_user_callback_api);
1800 end if;
1801
1802 -- Initialize API return status to success
1803 x_return_status := FND_API.G_RET_STS_SUCCESS;
1804
1805 l_parameter_t.setName('BENEFIT_ID');
1806 l_parameter_t.setValue(p_benefit_id);
1807 l_parameter_list.extend;
1808 l_parameter_list(1) := l_parameter_t;
1809
1810 l_parameter_t.setName('STATUS_CODE');
1811 l_parameter_t.setValue(p_status_code);
1812 l_parameter_list.extend;
1813 l_parameter_list(2) := l_parameter_t;
1814
1815 l_parameter_t.setName('PARTNER_ID');
1816 l_parameter_t.setValue(p_partner_id);
1817 l_parameter_list.extend;
1818 l_parameter_list(3) := l_parameter_t;
1819
1820 l_parameter_t.setName('MSG_CALLBACK_API');
1821 l_parameter_t.setValue(p_msg_callback_api);
1822 l_parameter_list.extend;
1823 l_parameter_list(4) := l_parameter_t;
1824
1825 l_parameter_t.setName('USER_CALLBACK_API');
1826 l_parameter_t.setValue(p_user_callback_api);
1827 l_parameter_list.extend;
1828 l_parameter_list(5) := l_parameter_t;
1829
1830 if( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1831 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1832 'pv.plsql.PV_BENFT_STATUS_CHANGE.STATUS_CHANGE_RAISE.raiseEvent', 'Calling pvx_event_pkg.raise_event' );
1833 end if;
1834
1835 pvx_event_pkg.raise_event(
1836 p_event_name => p_event_name,
1837 p_event_key => p_entity_id,
1838 p_data => null,
1839 p_parameters => l_parameter_list);
1840
1841 IF FND_API.To_Boolean ( p_commit ) THEN
1842 COMMIT WORK;
1843 END IF;
1844
1845 if( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1846 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1847 'pv.plsql.PV_BENFT_STATUS_CHANGE.STATUS_CHANGE_RAISE.end', 'Exiting' );
1848 end if;
1849
1850 -- Standard call to get message count and if count is 1, get message info.
1851 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_TRUE,
1852 p_count => x_msg_count,
1853 p_data => x_msg_data);
1854
1855 EXCEPTION
1856 WHEN FND_API.G_EXC_ERROR THEN
1857
1858 x_return_status := FND_API.G_RET_STS_ERROR ;
1859
1860 if( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1861 FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR,
1862 'pv.plsql.PV_BENFT_STATUS_CHANGE.STATUS_CHANGE_RAISE.error', FALSE );
1863 end if;
1864
1865 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_TRUE,
1866 p_count => x_msg_count,
1867 p_data => x_msg_data);
1868 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1869
1870 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1871
1872 if( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1873 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,
1874 'pv.plsql.PV_BENFT_STATUS_CHANGE.STATUS_CHANGE_RAISE.unexpected', FALSE );
1875 end if;
1876
1877 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_TRUE,
1878 p_count => x_msg_count,
1879 p_data => x_msg_data);
1880 WHEN OTHERS THEN
1881
1882 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1883 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
1884
1885 if( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1886 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,
1887 'pv.plsql.PV_BENFT_STATUS_CHANGE.STATUS_CHANGE_RAISE.unexpected', FALSE );
1888 end if;
1889
1890 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_TRUE,
1891 p_count => x_msg_count,
1892 p_data => x_msg_data);
1893 END;
1894
1895 procedure GET_DECLINE_REASON (document_id in varchar2,
1896 display_type in varchar2,
1897 document in out nocopy varchar2,
1898 document_type in out nocopy varchar2) IS
1899
1900 cursor lc_get_reason (pc_entity_id number) is
1901 select b.meaning from pv_referrals_b a, FND_LOOKUP_VALUES_VL b
1902 where a.referral_id = pc_entity_id
1903 and a.decline_reason_code = b.lookup_code
1904 and b.lookup_type = 'PV_REFERRAL_DECLINE_REASON';
1905
1906 l_entity_id number;
1907 l_translated_reason varchar2(100);
1908
1909 BEGIN
1910 if( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1911 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1912 'pv.plsql.PV_BENFT_STATUS_CHANGE.GET_DECLINE_REASON.begin',
1913 'Document_id:' || document_id || '. display_type:' || display_type);
1914 end if;
1915
1916 if display_type in ('text/plain', 'text/html') then
1917 l_entity_id := substr(document_id, 1, instr(document_id, ':')-1);
1918 open lc_get_reason(pc_entity_id => l_entity_id);
1919 fetch lc_get_reason into l_translated_reason;
1920 close lc_get_reason;
1921
1922 if( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1923 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1924 'pv.plsql.PV_BENFT_STATUS_CHANGE.GET_DECLINE_REASON.info', 'Reason: ' || l_translated_reason );
1925 end if;
1926
1927 document := l_translated_reason;
1928 end if;
1929
1930 if( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1931 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1932 'pv.plsql.PV_BENFT_STATUS_CHANGE.GET_DECLINE_REASON.end', 'Exiting');
1933 end if;
1934 END;
1935
1936 procedure GET_PRODUCTS (document_id in varchar2,
1937 display_type in varchar2,
1938 document in out nocopy varchar2,
1939 document_type in out nocopy varchar2) IS
1940
1941 cursor lc_get_products (pc_entity_id number) is
1942 select c.CONCAT_CAT_PARENTAGE, b.amount || ' ' || a.currency_code amount
1943 from pv_referrals_b a, pv_referred_products b, eni_prod_den_hrchy_parents_v c
1944 where a.referral_id = pc_entity_id
1945 and a.referral_id = b.referral_id
1946 and b.product_category_set_id = c.category_set_id
1947 and b.product_category_id = c.category_id;
1948
1949 cursor lc_max_products_length (pc_entity_id number) is
1950 select max(length(c.CONCAT_CAT_PARENTAGE)), max(length(to_char(b.amount) || ' ' || a.currency_code))
1951 from pv_referrals_b a, pv_referred_products b, eni_prod_den_hrchy_parents_v c
1952 where a.referral_id = pc_entity_id
1953 and a.referral_id = b.referral_id
1954 and b.product_category_set_id = c.category_set_id
1955 and b.product_category_id = c.category_id;
1956
1957 cursor lc_get_label is
1958 select attribute_code,attribute_label_long
1959 from ak_attributes_vl ak
1960 where attribute_application_id = 522
1961 AND ATTRIBUTE_code in ('ASF_AMOUNT','ASF_PRODUCT_CATEGORY');
1962
1963 l_entity_id number;
1964 l_max_length_amount number;
1965 l_max_length_products number;
1966 l_products_list varchar2(4000);
1967 l_label_amount varchar2(30);
1968 l_label_products varchar2(200);
1969 l_has_products boolean;
1970
1971 BEGIN
1972 if( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1973 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1974 'pv.plsql.PV_BENFT_STATUS_CHANGE.GET_PRODUCTS.begin',
1975 'Document_id:' || document_id || '. display_type:' || display_type);
1976 end if;
1977
1978 l_entity_id := substr(document_id, 1, instr(document_id, ':')-1);
1979
1980 if display_type = 'text/plain' then
1981 open lc_max_products_length(pc_entity_id => l_entity_id);
1982 fetch lc_max_products_length into l_max_length_products, l_max_length_amount;
1983 close lc_max_products_length;
1984 end if;
1985
1986 for l_label_rec in lc_get_label loop
1987
1988 if l_label_rec.attribute_code = 'ASF_AMOUNT' then
1989 l_label_amount := l_label_rec.attribute_label_long;
1990 l_max_length_amount := greatest(l_max_length_amount, length(l_label_amount));
1991 elsif l_label_rec.attribute_code = 'ASF_PRODUCT_CATEGORY' then
1992 l_label_products := l_label_rec.attribute_label_long;
1993 l_max_length_products := greatest(l_max_length_products, length(l_label_products));
1994 end if;
1995
1996 end loop;
1997
1998
1999 for l_prod_rec in lc_get_products(pc_entity_id => l_entity_id)
2000 loop
2001 l_has_products := true;
2002 if( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
2003 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2004 'pv.plsql.PV_BENFT_STATUS_CHANGE.GET_PRODUCTS.info', 'Product: ' || l_prod_rec.CONCAT_CAT_PARENTAGE );
2005 end if;
2006 if display_type = 'text/html' then
2007
2008 l_products_list := l_products_list || '<tr><td>' || l_prod_rec.concat_cat_parentage ||
2009 '</td><td align="right">' || l_prod_rec.amount || '</td></tr>';
2010
2011 elsif display_type = 'text/plain' then
2012
2013 l_products_list := l_products_list || rpad(l_prod_rec.concat_cat_parentage, l_max_length_products + 5) ||
2014 lpad(l_prod_rec.amount, l_max_length_amount) || fnd_global.local_chr(10);
2015 end if;
2016 end loop;
2017
2018 if l_has_products and display_type = 'text/html' then
2019 l_products_list := '<table><tr><th align="left">' || l_label_products || '</th><th align="right">' ||
2020 l_label_amount || '</th></tr>' || l_products_list || '</table>';
2021
2022 elsif l_has_products and display_type = 'text/plain' then
2023 l_products_list := rpad(l_label_products, l_max_length_products+2) || lpad(l_label_amount, l_max_length_amount+2) ||
2024 fnd_global.local_chr(10) || l_products_list;
2025 end if;
2026
2027 document := l_products_list;
2028
2029 if( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
2030 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
2031 'pv.plsql.PV_BENFT_STATUS_CHANGE.GET_PRODUCTS.end', 'Exiting');
2032 end if;
2033 END;
2034
2035 END;