[Home] [Help]
PACKAGE BODY: APPS.PV_WORKFLOW_PUB
Source
1 PACKAGE BODY PV_WORKFLOW_PUB as
2 /* $Header: pvxwffnb.pls 120.2 2006/05/31 04:16:45 dhii ship $ */
3
4 -- Start of Comments
5
6 -- Package name : PV_WORKFLOW_PUB
7 -- Purpose :
8 -- History :
9 --
10 -- NOTE :
11 -- End of Comments
12 --
13
14
15 G_PKG_NAME CONSTANT VARCHAR2(30):= 'PV_WORKFLOW_PUB';
16 G_FILE_NAME CONSTANT VARCHAR2(12) := 'pvxwffnb.pls';
17
18
19 procedure BYPASS_CM_APPROVAL_CHK (
20 itemtype in varchar2,
21 itemkey in varchar2,
22 actid in number,
23 funcmode in varchar2,
24 resultout in OUT NOCOPY varchar2)
25 IS
26 l_api_name CONSTANT VARCHAR2(30) := 'BYPASS_CM_APPROVAL_CHK';
27 l_api_version_number CONSTANT NUMBER := 1.0;
28
29 l_resultout varchar2(50);
30 l_return_status varchar2(1);
31 l_msg_count number;
32 l_msg_data varchar2(2000);
33 l_temp varchar2(40);
34 l_assignment_id number;
35 l_assignment_id_tbl pv_assignment_pub.g_number_table_type := pv_assignment_pub.g_number_table_type();
36
37 cursor lc_get_assignment (pc_itemtype varchar2,
38 pc_itemkey varchar2 ) is
39 select la.lead_assignment_id
40 from pv_lead_assignments la
41 where la.wf_item_type = pc_itemtype
42 and la.wf_item_key = pc_itemkey
43 and la.status <> pv_assignment_pub.g_la_status_pt_created;
44
45
46 BEGIN
47
48 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
49 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
50 fnd_message.Set_Token('TEXT', 'In ' || l_api_name || ' Funcmode: ' || funcmode);
51 fnd_msg_pub.Add;
52 END IF;
53
54 if (funcmode = 'RUN') then
55
56 l_temp := wf_engine.GetItemAttrText( itemtype => itemtype,
57 itemkey => itemkey,
58 aname => g_wf_attr_bypass_cm_approval);
59
60 if l_temp not in (g_wf_lkup_yes,
61 g_wf_lkup_no) or l_temp is null then
62
63 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
64 fnd_message.Set_token('TEXT', 'Invalid bypass CM Approval flag: ' || l_temp);
65 fnd_msg_pub.Add;
66 raise FND_API.G_EXC_ERROR;
67
68 end if;
69
70 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
71 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
72 fnd_message.Set_Token('TEXT', 'Bypass CM: ' || l_temp);
73 fnd_msg_pub.Add;
74 END IF;
75
76 if l_temp = g_wf_lkup_yes then -- bypass CM OK
77
78 open lc_get_assignment (pc_itemtype => itemtype,
79 pc_itemkey => itemkey);
80 loop
81 fetch lc_get_assignment into l_assignment_id;
82 exit when lc_get_assignment%notfound;
83 l_assignment_id_tbl.extend;
84 l_assignment_id_tbl(l_assignment_id_tbl.last) := l_assignment_id;
85 end loop;
86 close lc_get_assignment;
87
88 for i in 1 .. l_assignment_id_tbl.count loop
89
90 pv_assignment_pvt.UpdateAssignment (
91 p_api_version_number => 1.0
92 ,p_init_msg_list => FND_API.G_FALSE
93 ,p_commit => FND_API.G_FALSE
94 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
95 ,p_action => pv_assignment_pub.g_asgn_action_status_update
96 ,p_lead_assignment_id => l_assignment_id_tbl(i)
97 ,p_status_date => sysdate
98 ,p_status => pv_assignment_pub.g_la_status_cm_bypassed
99 ,p_reason_code => NULL
100 ,p_rank => NULL
101 ,x_msg_count => l_msg_count
102 ,x_msg_data => l_msg_data
103 ,x_return_status => l_return_status);
104
105 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
106 raise FND_API.G_EXC_ERROR;
107 end if;
108
109 end loop;
110 end if;
111
112 l_resultout := 'COMPLETE:' || l_temp;
113
114 elsif (funcmode = 'CANCEL') then
115 l_resultout := 'COMPLETE';
116
117 elsif (funcmode in ('RESPOND', 'FORWARD', 'TRANSFER')) then
118 l_resultout := 'COMPLETE';
119
120 elsif (funcmode = 'TIMEOUT') then
121 l_resultout := 'COMPLETE';
122
123 end if;
124
125 resultout := l_resultout;
126
127 EXCEPTION
128 WHEN FND_API.G_EXC_ERROR THEN
129
130 fnd_msg_pub.Count_And_Get(
131 p_encoded => FND_API.G_TRUE
132 ,p_count => l_msg_count
133 ,p_data => l_msg_data);
134
135 wf_core.context(G_PKG_NAME, l_api_name,l_msg_data);
136 raise;
137
138 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
139
140 fnd_msg_pub.Count_And_Get(
141 p_encoded => FND_API.G_TRUE
142 ,p_count => l_msg_count
143 ,p_data => l_msg_data);
144
145 wf_core.context(G_PKG_NAME, l_api_name,l_msg_data);
146 raise;
147
148 WHEN OTHERS THEN
149
150 fnd_msg_pub.Count_And_Get(
151 p_encoded => FND_API.G_TRUE
152 ,p_count => l_msg_count
153 ,p_data => l_msg_data);
154
155 wf_core.context(G_PKG_NAME, l_api_name,l_msg_data);
156 raise;
157
158 end BYPASS_CM_APPROVAL_CHK;
159
160
161 procedure SET_TIMEOUT (
162 itemtype in varchar2,
163 itemkey in varchar2,
164 actid in number,
165 funcmode in varchar2,
166 resultout in OUT NOCOPY varchar2)
167 IS
168 l_api_name CONSTANT VARCHAR2(30) := 'SET_TIMEOUT';
169 l_api_version_number CONSTANT NUMBER := 1.0;
170
171 l_resultout varchar2(30);
172 l_timeout_type varchar2(30);
173 l_return_status varchar2(1);
174 l_msg_count number;
175 l_msg_data varchar2(2000);
176
177 BEGIN
178
179 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
180 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
181 fnd_message.Set_Token('TEXT', 'In ' || l_api_name || ' Funcmode: ' || funcmode);
182 fnd_msg_pub.Add;
183 END IF;
184
185 if (funcmode = 'RUN') then
186
187 l_timeout_type := wf_engine.GetActivityAttrText(itemtype => itemtype,
188 itemkey => itemkey,
189 actid => actid,
190 aname => g_wf_attr_pvt_timeout_type);
191 pv_assignment_pvt.setTimeout (
192 p_api_version_number => 1.0,
193 p_init_msg_list => FND_API.G_FALSE,
194 p_commit => FND_API.G_FALSE,
195 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
196 p_itemtype => itemType,
197 p_itemkey => itemKey,
198 p_partner_id => NULL,
199 p_timeoutType => l_timeout_type,
200 x_return_status => l_return_status,
201 x_msg_count => l_msg_count,
202 x_msg_data => l_msg_data);
203
204 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
205 raise FND_API.G_EXC_ERROR;
206 end if;
207
208 l_resultout := 'COMPLETE:';
209
210 elsif (funcmode = 'CANCEL') then
211 l_resultout := 'COMPLETE';
212
213 elsif (funcmode in ('RESPOND', 'FORWARD', 'TRANSFER')) then
214 l_resultout := 'COMPLETE';
215
216 elsif (funcmode = 'TIMEOUT') then
217 l_resultout := 'COMPLETE';
218
219 end if;
220
221 resultout := l_resultout;
222
223 EXCEPTION
224 WHEN FND_API.G_EXC_ERROR THEN
225
226 fnd_msg_pub.Count_And_Get(
227 p_encoded => FND_API.G_TRUE
228 ,p_count => l_msg_count
229 ,p_data => l_msg_data);
230
231 wf_core.context(G_PKG_NAME, l_api_name,l_msg_data);
232 raise;
233
234 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
235
236 fnd_msg_pub.Count_And_Get(
237 p_encoded => FND_API.G_TRUE
238 ,p_count => l_msg_count
239 ,p_data => l_msg_data);
240
241 wf_core.context(G_PKG_NAME, l_api_name,l_msg_data);
242 raise;
243
244 WHEN OTHERS THEN
245
246 fnd_msg_pub.Count_And_Get(
247 p_encoded => FND_API.G_TRUE
248 ,p_count => l_msg_count
249 ,p_data => l_msg_data);
250
251 wf_core.context(G_PKG_NAME, l_api_name,l_msg_data);
252 raise;
253
254 end SET_TIMEOUT;
255
256
257 procedure WAIT_ON_MATCH (
258 itemtype in varchar2,
259 itemkey in varchar2,
260 actid in number,
261 funcmode in varchar2,
262 resultout in OUT NOCOPY varchar2)
263
264 IS
265 l_api_name CONSTANT VARCHAR2(30) := 'WAIT_ON_MATCH';
266 l_api_version_number CONSTANT NUMBER := 1.0;
267
268 l_resultout varchar2(30);
269 l_return_status varchar2(1);
270 l_routing_outcome varchar2(30);
271 l_routing_stage varchar2(30);
272 l_msg_count number;
273 l_msg_data varchar2(2000);
274
275 l_assignment_id number;
276 l_assign_status varchar2(30);
277
278 l_assignment_id_tbl pv_assignment_pub.g_number_table_type := pv_assignment_pub.g_number_table_type();
279 l_assign_status_tbl pv_assignment_pub.g_varchar_table_type :=pv_assignment_pub.g_varchar_table_type();
280
281 cursor lc_get_match_outcome (pc_itemtype varchar2,
282 pc_itemkey varchar2) is
283 select a.lead_assignment_id, a.status
284 from pv_lead_assignments a
285 where a.wf_item_type = pc_itemtype
286 and a.wf_item_key = pc_itemkey;
287
288 BEGIN
289
290 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
291 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
292 fnd_message.Set_Token('TEXT', 'In ' || l_api_name || ' Funcmode: ' || funcmode);
293 fnd_msg_pub.Add;
294 END IF;
295
296 if (funcmode = 'RUN') then
297
298 pv_assignment_pvt.send_notification (
299 p_api_version_number => 1.0
300 ,p_init_msg_list => FND_API.G_FALSE
301 ,p_commit => FND_API.G_FALSE
302 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
303 ,p_itemtype => itemType
304 ,p_itemkey => itemKey
305 ,p_activity_id => actid
306 ,P_route_stage => pv_assignment_pub.g_r_status_matched
307 ,p_partner_id => NULL
308 ,x_return_status => l_return_status
309 ,x_msg_count => l_msg_count
310 ,x_msg_data => l_msg_data);
311
312 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
313 raise FND_API.G_EXC_ERROR;
314 end if;
315
316 wf_standard.block(itemtype => itemtype,
317 itemkey => itemkey,
318 actid => actid,
319 funcmode => 'RUN',
320 resultout => l_resultout);
321
322 elsif (funcmode = 'CANCEL') then
323 l_resultout := 'COMPLETE';
324
325 elsif (funcmode in ('RESPOND', 'FORWARD', 'TRANSFER')) then
326 l_resultout := 'COMPLETE';
327
328 elsif (funcmode = 'TIMEOUT') then
329
330 open lc_get_match_outcome (pc_itemtype => itemtype, pc_itemkey => itemkey);
331
332 loop
333 fetch lc_get_match_outcome into l_assignment_id, l_assign_status;
334 exit when lc_get_match_outcome%notfound;
335
336 l_assignment_id_tbl.extend;
337 l_assignment_id_tbl(l_assignment_id_tbl.last) := l_assignment_id;
338 l_assign_status_tbl.extend;
339 l_assign_status_tbl(l_assign_status_tbl.last) := l_assign_status;
340 end loop;
341
342 close lc_get_match_outcome;
343
344 for i in 1 .. l_assignment_id_tbl.count loop
345
346 if l_assign_status_tbl(i) = pv_assignment_pub.g_la_status_assigned then
347
348 -- CM did not act and timeout happened
349
350 pv_assignment_pvt.UpdateAssignment (
351 p_api_version_number => 1.0
352 ,p_init_msg_list => FND_API.G_FALSE
353 ,p_commit => FND_API.G_FALSE
354 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
355 ,p_action => pv_assignment_pub.g_asgn_action_status_update
356 ,p_lead_assignment_id => l_assignment_id_tbl(i)
357 ,p_status_date => sysdate
358 ,p_status => pv_assignment_pub.g_la_status_cm_timeout
359 ,p_reason_code => NULL
360 ,p_rank => NULL
361 ,x_msg_count => l_msg_count
362 ,x_msg_data => l_msg_data
363 ,x_return_status => l_return_status);
364
365 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
366 raise FND_API.G_EXC_ERROR;
367 end if;
368
369 update pv_party_notifications
370 set resource_response = pv_assignment_pub.g_la_status_cm_timeout,
371 response_date = sysdate,
372 object_version_number = object_version_number + 1,
373 last_update_date = sysdate,
374 last_updated_by = FND_GLOBAL.user_id,
375 last_update_login = FND_GLOBAL.login_id
376 where lead_assignment_id = l_assignment_id_tbl(i)
377 and resource_response is null
378 and notification_type = pv_assignment_pub.g_notify_type_matched_to;
379
380 end if;
381 end loop;
382
383 wf_engine.SetItemAttrText (itemtype => itemType,
384 itemkey => itemKey,
385 aname => g_wf_attr_routing_outcome,
386 avalue => g_wf_lkup_match_timedout);
387
388 l_resultout := 'COMPLETE:' || g_wf_lkup_match_timedout;
389
390 end if;
391
392 resultout := l_resultout;
393
394 EXCEPTION
395 WHEN FND_API.G_EXC_ERROR THEN
396
397 fnd_msg_pub.Count_And_Get(
398 p_encoded => FND_API.G_TRUE
399 ,p_count => l_msg_count
400 ,p_data => l_msg_data);
401
402 wf_core.context(G_PKG_NAME, l_api_name,l_msg_data);
403 raise;
404
405 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
406
407 fnd_msg_pub.Count_And_Get(
408 p_encoded => FND_API.G_TRUE
409 ,p_count => l_msg_count
410 ,p_data => l_msg_data);
411
412 wf_core.context(G_PKG_NAME, l_api_name,l_msg_data);
413 raise;
414
415 WHEN OTHERS THEN
416
417 fnd_msg_pub.Count_And_Get(
418 p_encoded => FND_API.G_TRUE
419 ,p_count => l_msg_count
420 ,p_data => l_msg_data);
421
422 wf_core.context(G_PKG_NAME, l_api_name,l_msg_data);
423 raise;
424
425 end WAIT_ON_MATCH;
426
427
428 procedure PROCESS_MATCH_OUTCOME (
429 itemtype in varchar2,
430 itemkey in varchar2,
431 actid in number,
432 funcmode in varchar2,
433 resultout in OUT NOCOPY varchar2)
434 IS
435 l_api_name CONSTANT VARCHAR2(30) := 'PROCESS_MATCH_OUTCOME';
436 l_api_version_number CONSTANT NUMBER := 1.0;
437
438 l_match_outcome varchar2(30);
439
440 l_resultout varchar2(30);
441 l_return_status varchar2(1);
442 l_msg_count number;
443 l_msg_data varchar2(2000);
444
445 BEGIN
446
447 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
448 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
449 fnd_message.Set_Token('TEXT', 'In ' || l_api_name || ' Funcmode: ' || funcmode);
450 fnd_msg_pub.Add;
451 END IF;
452
453 if (funcmode = 'RUN') then
454
455 l_match_outcome := wf_engine.GetItemAttrText(itemtype => itemtype,
456 itemkey => itemkey,
457 aname => g_wf_attr_routing_outcome);
458
459 -- in case salesrep have added access records for partner/partner contact
460 -- in direct matching and cm has rejected
461
462 pv_assignment_pvt.removeRejectedFromAccess (
463 p_api_version_number => 1.0,
464 p_init_msg_list => FND_API.G_FALSE,
465 p_commit => FND_API.G_FALSE,
466 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
467 p_itemtype => itemType,
468 p_itemkey => itemKey,
469 p_partner_id => NULL,
470 x_return_status => l_return_status,
471 x_msg_count => l_msg_count,
472 x_msg_data => l_msg_data);
473
474 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
475 raise FND_API.G_EXC_ERROR;
476 end if;
477
478 l_resultout := 'COMPLETE:' || l_match_outcome;
479
480 elsif (funcmode = 'CANCEL') then
481 l_resultout := 'COMPLETE';
482
483 elsif (funcmode in ('RESPOND', 'FORWARD', 'TRANSFER')) then
484 l_resultout := 'COMPLETE';
485
486 elsif (funcmode = 'TIMEOUT') then
487 l_resultout := 'COMPLETE';
488
489 end if;
490
491 resultout := l_resultout;
492
493 EXCEPTION
494 WHEN FND_API.G_EXC_ERROR THEN
495
496 fnd_msg_pub.Count_And_Get(
497 p_encoded => FND_API.G_TRUE
498 ,p_count => l_msg_count
499 ,p_data => l_msg_data);
500
501 wf_core.context(G_PKG_NAME, l_api_name,l_msg_data);
502 raise;
503
504 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
505
506 fnd_msg_pub.Count_And_Get(
507 p_encoded => FND_API.G_TRUE
508 ,p_count => l_msg_count
509 ,p_data => l_msg_data);
510
511 wf_core.context(G_PKG_NAME, l_api_name,l_msg_data);
512 raise;
513
514 WHEN OTHERS THEN
515
516 fnd_msg_pub.Count_And_Get(
517 p_encoded => FND_API.G_TRUE
518 ,p_count => l_msg_count
519 ,p_data => l_msg_data);
520
521 wf_core.context(G_PKG_NAME, l_api_name,l_msg_data);
522 raise;
523
524 end PROCESS_MATCH_OUTCOME;
525
526
527 procedure GET_ASSIGNMENT_TYPE (
528 itemtype in varchar2,
529 itemkey in varchar2,
530 actid in number,
531 funcmode in varchar2,
532 resultout in OUT NOCOPY varchar2)
533 IS
534 l_api_name CONSTANT VARCHAR2(30) := 'GET_ASSIGNMENT_TYPE';
535 l_api_version_number CONSTANT NUMBER := 1.0;
536
537 l_partner_id number;
538 l_assign_sequence pls_integer;
539 l_assignment_type varchar2(30);
540 l_resultout varchar2(30);
541 l_return_status varchar2(1);
542 l_msg_count number;
543 l_msg_data varchar2(2000);
544
545 cursor lc_get_pt_id (pc_itemtype varchar2,
546 pc_itemkey varchar2) is
547 select partner_id, assign_sequence
548 from pv_lead_assignments la
549 where la.wf_item_type = pc_itemtype
550 and la.wf_item_key = pc_itemkey
551 and status in (pv_assignment_pub.g_la_status_cm_approved,
552 pv_assignment_pub.g_la_status_cm_added,
553 pv_assignment_pub.g_la_status_cm_bypassed,
554 pv_assignment_pub.g_la_status_cm_app_for_pt,
555 pv_assignment_pub.g_la_status_cm_timeout)
556 order by assign_sequence;
557
558 BEGIN
559
560 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
561 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
562 fnd_message.Set_Token('TEXT', 'In ' || l_api_name || ' Funcmode: ' || funcmode);
563 fnd_msg_pub.Add;
564 END IF;
565
566 if (funcmode = 'RUN') then
567
568 pv_assignment_pvt.send_notification (
569 p_api_version_number => 1.0
570 ,p_init_msg_list => FND_API.G_FALSE
571 ,p_commit => FND_API.G_FALSE
572 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
573 ,p_itemtype => itemType
574 ,p_itemkey => itemKey
575 ,p_activity_id => actid
576 ,P_route_stage => pv_assignment_pub.g_r_status_matched
577 ,p_partner_id => null
578 ,x_return_status => l_return_status
579 ,x_msg_count => l_msg_count
580 ,x_msg_data => l_msg_data);
581
582 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
583 raise FND_API.G_EXC_ERROR;
584 end if;
585
586 pv_assignment_pvt.update_routing_stage (
587 p_api_version_number => 1.0,
588 p_init_msg_list => FND_API.G_FALSE,
589 p_commit => FND_API.G_FALSE,
590 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
591 p_itemType => itemtype,
592 p_itemKey => itemKey,
593 p_routing_stage => pv_assignment_pub.g_r_status_offered,
594 p_active_but_open_flag => 'N',
595 x_return_status => l_return_status,
596 x_msg_count => l_msg_count,
597 x_msg_data => l_msg_data);
598
599 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
600 raise FND_API.G_EXC_ERROR;
601 end if;
602
603 l_assignment_type := wf_engine.GetItemAttrText( itemtype => itemtype,
604 itemkey => itemkey,
605 aname => g_wf_attr_assignment_type);
606
607 if l_assignment_type = g_wf_lkup_single then
608
609 open lc_get_pt_id(pc_itemtype => itemtype, pc_itemkey => itemkey);
610 fetch lc_get_pt_id into l_partner_id, l_assign_sequence;
611
612 if lc_get_pt_id%notfound then
613
614 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
615 fnd_message.SET_TOKEN('TEXT', 'Cannot find Partner ID for itemkey: ' || itemkey );
616 fnd_msg_pub.ADD;
617 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
618
619 end if;
620
621 -- make sure there is only one partner in SINGLE assignment
622
623 fetch lc_get_pt_id into l_partner_id, l_assign_sequence;
624
625 if lc_get_pt_id%found then
626
627 fnd_message.Set_Name('PV', 'PV_MULTIPLE_PRTNR_SINGLE');
628 fnd_msg_pub.ADD;
629 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
630
631 end if;
632
633 close lc_get_pt_id;
634
635 pv_assignment_pvt.set_offered_attributes (
636 p_api_version_number => 1.0,
637 p_init_msg_list => FND_API.G_FALSE,
638 p_commit => FND_API.G_FALSE,
639 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
640 p_itemtype => itemType,
641 p_itemkey => itemKey,
642 p_partner_id => l_partner_id,
643 x_return_status => l_return_status,
644 x_msg_count => l_msg_count,
645 x_msg_data => l_msg_data);
646
647 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
648 raise FND_API.G_EXC_ERROR;
649 end if;
650
651 elsif l_assignment_type = g_wf_lkup_serial then
652
653 open lc_get_pt_id(pc_itemtype => itemtype, pc_itemkey => itemkey);
654 fetch lc_get_pt_id into l_partner_id, l_assign_sequence;
655
656 if lc_get_pt_id%notfound then
657
658 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
659 fnd_message.SET_TOKEN('TEXT', 'Cannot find Partner ID for itemkey: ' || itemkey );
660 fnd_msg_pub.ADD;
661 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
662
663 end if;
664
665 close lc_get_pt_id;
666
667 wf_engine.SetItemAttrNumber (itemtype => itemType,
668 itemkey => itemKey,
669 aname => g_wf_attr_next_serial_rank,
670 avalue => l_assign_sequence);
671
672 elsif l_assignment_type = g_wf_lkup_broadcast then
673
674 pv_assignment_pvt.set_offered_attributes (
675 p_api_version_number => 1.0,
676 p_init_msg_list => FND_API.G_FALSE,
677 p_commit => FND_API.G_FALSE,
678 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
679 p_itemtype => itemType,
680 p_itemkey => itemKey,
681 p_partner_id => null,
682 x_return_status => l_return_status,
683 x_msg_count => l_msg_count,
684 x_msg_data => l_msg_data);
685
686 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
687 raise FND_API.G_EXC_ERROR;
688 end if;
689
690
691 elsif l_assignment_type = g_wf_lkup_joint then
692
693 pv_assignment_pvt.set_offered_attributes (
694 p_api_version_number => 1.0,
695 p_init_msg_list => FND_API.G_FALSE,
696 p_commit => FND_API.G_FALSE,
697 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
698 p_itemtype => itemType,
699 p_itemkey => itemKey,
700 p_partner_id => null,
701 x_return_status => l_return_status,
702 x_msg_count => l_msg_count,
703 x_msg_data => l_msg_data);
704
705 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
706 raise FND_API.G_EXC_ERROR;
707 end if;
708
709 else
710
711 fnd_message.Set_Name('PV', 'PV_INVALID_ASSIGN_TYPE');
712 fnd_message.SET_TOKEN('TYPE', l_assignment_type);
713 fnd_msg_pub.ADD;
714 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
715
716 end if;
717
718 l_resultout := 'COMPLETE:' || l_assignment_type;
719
720 elsif (funcmode = 'CANCEL') then
721 l_resultout := 'COMPLETE';
722
723 elsif (funcmode in ('RESPOND', 'FORWARD', 'TRANSFER')) then
724 l_resultout := 'COMPLETE';
725
726 elsif (funcmode = 'TIMEOUT') then
727 l_resultout := 'COMPLETE';
728
729 end if;
730
731 resultout := l_resultout;
732
733 EXCEPTION
734 WHEN FND_API.G_EXC_ERROR THEN
735
736 fnd_msg_pub.Count_And_Get(
737 p_encoded => FND_API.G_TRUE
738 ,p_count => l_msg_count
739 ,p_data => l_msg_data);
740
741 wf_core.context(G_PKG_NAME, l_api_name,l_msg_data);
742 raise;
743
744 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
745
746 fnd_msg_pub.Count_And_Get(
747 p_encoded => FND_API.G_TRUE
748 ,p_count => l_msg_count
749 ,p_data => l_msg_data);
750
751 wf_core.context(G_PKG_NAME, l_api_name,l_msg_data);
752 raise;
753
754 WHEN OTHERS THEN
755
756 fnd_msg_pub.Count_And_Get(
757 p_encoded => FND_API.G_TRUE
758 ,p_count => l_msg_count
759 ,p_data => l_msg_data);
760
761 wf_core.context(G_PKG_NAME, l_api_name,l_msg_data);
762 raise;
763
764 end GET_ASSIGNMENT_TYPE;
765
766
767
768 procedure SERIAL_NEXT_PARTNER (
769 itemtype in varchar2,
770 itemkey in varchar2,
771 actid in number,
772 funcmode in varchar2,
773 resultout in OUT NOCOPY varchar2)
774 IS
775 l_api_name CONSTANT VARCHAR2(30) := 'SERIAL_NEXT_PARTNER';
776 l_api_version_number CONSTANT NUMBER := 1.0;
777
778 l_resultout varchar2(30);
779 l_return_status varchar2(1);
780 l_msg_count number;
781 l_msg_data varchar2(2000);
782
783 l_partner_id number;
784 l_next_partner_id number;
785 l_current_seq pls_integer;
786 l_next_seq pls_integer;
787
788 l_assignment_log_rec PV_ASSIGNMENT_PVT.assignment_log_rec_type;
789 l_assignment_log_id number;
790 l_lead_id number;
791 l_lead_workflow_id number;
792 l_lead_assignment_id number;
793 l_next_lead_assignment_id number;
794
795 cursor lc_get_pt_id (pc_itemtype varchar2,
796 pc_itemkey varchar2,
797 pc_sequence number) is
798 select la.partner_id, la.assign_sequence, a.lead_id, a.lead_workflow_id, la.lead_assignment_id
799 from pv_lead_workflows a, pv_lead_assignments la
800 where la.wf_item_type = pc_itemtype
801 and la.wf_item_key = pc_itemkey
802 and la.assign_sequence >= pc_sequence
803 and la.status in ( pv_assignment_pub.g_la_status_cm_approved,
804 pv_assignment_pub.g_la_status_cm_app_for_pt,
805 pv_assignment_pub.g_la_status_cm_bypassed,
806 pv_assignment_pub.g_la_status_cm_timeout)
807 and la.wf_item_type = a.wf_item_type
808 and la.wf_item_key = a.wf_item_key
809 order by assign_sequence;
810
811 BEGIN
812 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
813 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
814 fnd_message.Set_Token('TEXT', 'In ' || l_api_name || ' Funcmode: ' || funcmode);
815 fnd_msg_pub.Add;
816 END IF;
817
818 if (funcmode = 'RUN') then
819
820 l_current_seq := wf_engine.GetItemAttrNumber( itemtype => itemtype,
821 itemkey => itemkey,
822 aname => g_wf_attr_next_serial_rank);
823 if l_current_seq <> -999 then
824
825 open lc_get_pt_id (pc_itemtype => itemtype,
826 pc_itemkey => itemkey,
827 pc_sequence => l_current_seq);
828
829 fetch lc_get_pt_id into l_partner_id, l_current_seq, l_lead_id, l_lead_workflow_id, l_lead_assignment_id;
830
831 if lc_get_pt_id%found then
832 fetch lc_get_pt_id into l_next_partner_id, l_next_seq, l_lead_id, l_lead_workflow_id, l_next_lead_assignment_id;
833 end if;
834
835 close lc_get_pt_id;
836
837 end if;
838
839 if l_partner_id is null then
840
841 l_resultout := 'COMPLETE:' || g_wf_lkup_false;
842
843 else
844
845 pv_assignment_pvt.set_offered_attributes (
846 p_api_version_number => 1.0,
847 p_init_msg_list => FND_API.G_FALSE,
848 p_commit => FND_API.G_FALSE,
849 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
850 p_itemtype => itemType,
851 p_itemkey => itemKey,
852 p_partner_id => l_partner_id,
853 x_return_status => l_return_status,
854 x_msg_count => l_msg_count,
855 x_msg_data => l_msg_data);
856
857 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
858 raise FND_API.G_EXC_ERROR;
859 end if;
860
861 wf_engine.SetItemAttrNumber (itemtype => itemType,
862 itemkey => itemKey,
863 aname => g_wf_attr_current_serial_rank,
864 avalue => l_current_seq);
865
866 wf_engine.SetItemAttrNumber (itemtype => itemType,
867 itemkey => itemKey,
868 aname => g_wf_attr_next_serial_rank,
869 avalue => nvl(l_next_seq, -999));
870
871 l_assignment_log_rec.LEAD_ID := l_lead_id;
872 l_assignment_log_rec.FROM_LEAD_STATUS := 'MATCHED';
873 l_assignment_log_rec.TO_LEAD_STATUS := 'OFFERED';
874 l_assignment_log_rec.WF_ITEM_TYPE := itemtype;
875 l_assignment_log_rec.WF_ITEM_KEY := itemkey;
876 l_assignment_log_rec.WORKFLOW_ID := l_lead_workflow_id;
877 l_assignment_log_rec.partner_id := l_partner_id;
878 l_assignment_log_rec.lead_assignment_id := l_lead_assignment_id;
879
880 PV_ASSIGNMENT_PVT.Create_assignment_log_row (
881 p_api_version_number => 1.0,
882 p_init_msg_list => FND_API.G_FALSE,
883 p_commit => FND_API.G_FALSE,
884 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
885 p_assignment_log_rec => l_assignment_log_rec,
886 x_assignment_id => l_assignment_log_id,
887 x_return_status => l_return_status,
888 x_msg_count => l_msg_count,
889 x_msg_data => l_msg_data);
890
891 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
892 raise FND_API.G_EXC_ERROR;
893 end if;
894
895 l_resultout := 'COMPLETE:' || g_wf_lkup_true;
896
897 end if;
898
899 elsif (funcmode = 'CANCEL') then
900 l_resultout := 'COMPLETE';
901
902 elsif (funcmode in ('RESPOND', 'FORWARD', 'TRANSFER')) then
903 l_resultout := 'COMPLETE';
904
905 elsif (funcmode = 'TIMEOUT') then
906 l_resultout := 'COMPLETE';
907
908 end if;
909
910 resultout := l_resultout;
911
912 EXCEPTION
913 WHEN FND_API.G_EXC_ERROR THEN
914
915 fnd_msg_pub.Count_And_Get(
916 p_encoded => FND_API.G_TRUE
917 ,p_count => l_msg_count
918 ,p_data => l_msg_data);
919
920 wf_core.context(G_PKG_NAME, l_api_name,l_msg_data);
921 raise;
922
923 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
924
925 fnd_msg_pub.Count_And_Get(
926 p_encoded => FND_API.G_TRUE
927 ,p_count => l_msg_count
928 ,p_data => l_msg_data);
929
930 wf_core.context(G_PKG_NAME, l_api_name,l_msg_data);
931 raise;
932
933 WHEN OTHERS THEN
934
935 fnd_msg_pub.Count_And_Get(
936 p_encoded => FND_API.G_TRUE
937 ,p_count => l_msg_count
938 ,p_data => l_msg_data);
939
940 wf_core.context(G_PKG_NAME, l_api_name,l_msg_data);
941 raise;
942
943 end SERIAL_NEXT_PARTNER;
944
945
946 procedure WAIT_ON_OFFER (
947 itemtype in varchar2,
948 itemkey in varchar2,
949 actid in number,
950 funcmode in varchar2,
951 resultout in OUT NOCOPY varchar2)
952
953 IS
954 l_api_name CONSTANT VARCHAR2(30) := 'WAIT_ON_OFFER';
955 l_api_version_number CONSTANT NUMBER := 1.0;
956
957 l_resultout varchar2(30);
958 l_return_status varchar2(1);
959 l_msg_count number;
960 l_msg_data varchar2(2000);
961
962 l_assignment_type varchar2(30);
963 l_pt_org_rs_id number;
964 l_lead_id number;
965 l_access_id number;
966 l_customer_id number;
967 l_address_id number;
968 l_partner_id number;
969
970 cursor lc_get_partner_org (pc_itemtype varchar2,
971 pc_itemkey varchar2) is
972 select
973 b.resource_id partner_org_rs_id
974 from pv_lead_assignments la,
975 jtf_rs_resource_extns b
976 where
977 la.wf_item_type = pc_itemtype
978 and la.wf_item_key = pc_itemkey
979 and la.status = pv_assignment_pub.g_la_status_cm_app_for_pt
980 and la.partner_id = b.source_id
981 and b.category = 'PARTNER';
982
983 BEGIN
984
985 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
986 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
987 fnd_message.Set_Token('TEXT', 'In ' || l_api_name || ' Funcmode: ' || funcmode);
988 fnd_msg_pub.Add;
989 END IF;
990
991 if (funcmode = 'RUN') then
992
993 l_partner_id := wf_engine.GetItemAttrNumber(itemtype => itemtype,
994 itemkey => itemkey,
995 aname => g_wf_attr_partner_id);
996 pv_assignment_pvt.send_notification (
997 p_api_version_number => 1.0
998 ,p_init_msg_list => FND_API.G_FALSE
999 ,p_commit => FND_API.G_FALSE
1000 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
1001 ,p_itemtype => itemType
1002 ,p_itemkey => itemKey
1003 ,p_activity_id => actid
1004 ,P_route_stage => pv_assignment_pub.g_r_status_offered
1005 ,p_partner_id => l_partner_id
1006 ,x_return_status => l_return_status
1007 ,x_msg_count => l_msg_count
1008 ,x_msg_data => l_msg_data);
1009
1010 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1011 raise FND_API.G_EXC_ERROR;
1012 end if;
1013
1014 l_assignment_type := wf_engine.GetItemAttrText( itemtype => itemtype,
1015 itemkey => itemkey,
1016 aname => g_wf_attr_assignment_type);
1017 if l_assignment_type = g_wf_lkup_joint then
1018
1019 l_lead_id := wf_engine.GetItemAttrNumber( itemtype => itemtype,
1020 itemkey => itemkey,
1021 aname => g_wf_attr_opportunity_id);
1022
1023 l_customer_id := wf_engine.GetItemAttrNumber( itemtype => itemtype,
1024 itemkey => itemkey,
1025 aname => g_wf_attr_customer_id);
1026
1027 l_address_id := wf_engine.GetItemAttrNumber( itemtype => itemtype,
1028 itemkey => itemkey,
1029 aname => g_wf_attr_address_id);
1030
1031 wf_engine.SetItemAttrNumber (itemtype => itemType,
1032 itemkey => itemKey,
1033 aname => g_wf_attr_wf_activity_id,
1034 avalue => actid);
1035
1036
1037 open lc_get_partner_org (pc_itemtype => itemtype, pc_itemkey => itemkey);
1038
1039 loop
1040 fetch lc_get_partner_org into l_pt_org_rs_id;
1041 exit when lc_get_partner_org%notfound;
1042
1043 pv_assign_util_pvt.updateaccess(
1044 p_api_version_number => 1.0,
1045 p_init_msg_list => FND_API.G_FALSE,
1046 p_commit => FND_API.G_FALSE,
1047 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1048 p_itemtype => itemType,
1049 p_itemkey => itemKey,
1050 p_current_username => NULL,
1051 p_lead_id => l_lead_id,
1052 p_customer_id => l_customer_id,
1053 p_address_id => l_address_id,
1054 p_access_action => pv_assignment_pub.G_ADD_ACCESS,
1055 p_resource_id => l_pt_org_rs_id,
1056 p_access_type => pv_assignment_pub.G_PT_ORG_ACCESS,
1057 x_access_id => l_access_id,
1058 x_return_status => l_return_status,
1059 x_msg_count => l_msg_count,
1060 x_msg_data => l_msg_data);
1061
1062 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1063 raise FND_API.G_EXC_ERROR;
1064 end if;
1065
1066 end loop;
1067 close lc_get_partner_org;
1068
1069 if l_pt_org_rs_id is not null then
1070
1071 pv_assignment_pvt.update_routing_stage (
1072 p_api_version_number => 1.0,
1073 p_init_msg_list => FND_API.G_FALSE,
1074 p_commit => FND_API.G_FALSE,
1075 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1076 p_itemType => itemtype,
1077 p_itemKey => itemKey,
1078 p_routing_stage => pv_assignment_pub.g_r_status_active,
1079 p_active_but_open_flag => 'Y',
1080 x_return_status => l_return_status,
1081 x_msg_count => l_msg_count,
1082 x_msg_data => l_msg_data);
1083
1084 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1085 raise FND_API.G_EXC_ERROR;
1086 end if;
1087
1088 end if;
1089 end if;
1090
1091 wf_standard.block(itemtype => itemtype,
1092 itemkey => itemkey,
1093 actid => actid,
1094 funcmode => 'RUN',
1095 resultout => l_resultout);
1096
1097 elsif (funcmode = 'CANCEL') then
1098 l_resultout := 'COMPLETE';
1099
1100 elsif (funcmode in ('RESPOND', 'FORWARD', 'TRANSFER')) then
1101 l_resultout := 'COMPLETE';
1102
1103 elsif (funcmode = 'TIMEOUT') then
1104 l_resultout := g_wf_lkup_offer_timedout;
1105
1106 end if;
1107
1108 resultout := l_resultout;
1109
1110 EXCEPTION
1111 WHEN FND_API.G_EXC_ERROR THEN
1112
1113 fnd_msg_pub.Count_And_Get(
1114 p_encoded => FND_API.G_TRUE
1115 ,p_count => l_msg_count
1116 ,p_data => l_msg_data);
1117
1118 wf_core.context(G_PKG_NAME, l_api_name,l_msg_data);
1119 raise;
1120
1121 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1122
1123 fnd_msg_pub.Count_And_Get(
1124 p_encoded => FND_API.G_TRUE
1125 ,p_count => l_msg_count
1126 ,p_data => l_msg_data);
1127
1128 wf_core.context(G_PKG_NAME, l_api_name,l_msg_data);
1129 raise;
1130
1131 WHEN OTHERS THEN
1132
1133 fnd_msg_pub.Count_And_Get(
1134 p_encoded => FND_API.G_TRUE
1135 ,p_count => l_msg_count
1136 ,p_data => l_msg_data);
1137
1138 wf_core.context(G_PKG_NAME, l_api_name,l_msg_data);
1139 raise;
1140
1141 end WAIT_ON_OFFER;
1142
1143
1144 procedure PROCESS_OFFER_OUTCOME (
1145 itemtype in varchar2,
1146 itemkey in varchar2,
1147 actid in number,
1148 funcmode in varchar2,
1149 resultout in OUT NOCOPY varchar2)
1150 IS
1151 l_api_name CONSTANT VARCHAR2(30) := 'PROCESS_OFFER_OUTCOME';
1152 l_api_version_number CONSTANT NUMBER := 1.0;
1153
1154 l_assignment_type varchar2(30);
1155 l_offer_outcome varchar2(30);
1156 l_username varchar2(100);
1157 l_response varchar2(30);
1158 l_rank pls_integer;
1159 l_lead_id number;
1160 l_access_id number;
1161 l_partner_id number;
1162 l_resource_id number;
1163 l_assignment_id number;
1164 l_customer_id number;
1165 l_partner_org_rs_id number;
1166 l_address_id number;
1167 l_lc_partner_rs_id NUMBER;
1168 l_username_tbl pv_assignment_pub.g_varchar_table_type := pv_assignment_pub.g_varchar_table_type();
1169 l_response_tbl pv_assignment_pub.g_varchar_table_type := pv_assignment_pub.g_varchar_table_type();
1170 l_resource_id_tbl pv_assignment_pub.g_number_table_type := pv_assignment_pub.g_number_table_type();
1171 l_partner_id_tbl pv_assignment_pub.g_number_table_type := pv_assignment_pub.g_number_table_type();
1172 l_assignment_id_tbl pv_assignment_pub.g_number_table_type := pv_assignment_pub.g_number_table_type();
1173
1174 l_resultout varchar2(30);
1175 l_return_status varchar2(1);
1176 l_msg_count number;
1177 l_msg_data varchar2(2000);
1178
1179 -- this can be written using just joins (wf_item_activity_statuses,
1180 -- wf_process_activities, wf_activity_transitions) but if the
1181 -- timeout transition in WF is removed, it will not work
1182
1183 cursor lc_chk_for_timeout (pc_itemtype varchar2,
1184 pc_itemkey varchar2,
1185 pc_from_activity_name varchar2,
1186 pc_to_activity_id number) is
1187 select a.activity_result_code
1188 from wf_item_activity_statuses a
1189 where a.item_type = pc_itemtype
1190 and a.item_key = pc_itemkey
1191 and a.process_activity =
1192 (select d.from_process_activity
1193 from wf_process_activities c, wf_activity_transitions d
1194 where d.to_process_activity = pc_to_activity_id
1195 and d.from_process_activity = c.instance_id
1196 and c.activity_name = pc_from_activity_name and rownum < 2);
1197
1198 cursor lc_get_pt_response (pc_itemtype varchar2,
1199 pc_itemkey varchar2,
1200 pc_partner_id number) is
1201 select la.lead_assignment_id, la.partner_id, la.status
1202 from pv_lead_assignments la
1203 where la.wf_item_type = pc_itemtype
1204 and la.wf_item_key = pc_itemkey
1205 and la.partner_id = pc_partner_id;
1206
1207
1208 -- reusable sql
1209 cursor lc_get_all_pt_response (pc_itemtype varchar2,
1210 pc_itemkey varchar2) is
1211 select la.lead_assignment_id, la.partner_id, la.status
1212 from pv_lead_assignments la
1213 where la.wf_item_type = pc_itemtype
1214 and la.wf_item_key = pc_itemkey;
1215
1216 cursor lc_get_offered_to_for_pt (pc_itemtype varchar2,
1217 pc_itemkey varchar2,
1218 pc_partner_id number,
1219 pc_notify_type varchar2) is
1220 select usr.user_name, pn.resource_id
1221 from pv_lead_assignments la,
1222 pv_party_notifications pn,
1223 fnd_user usr
1224 where la.wf_item_type = pc_itemtype
1225 and la.wf_item_key = pc_itemkey
1226 and la.partner_id = pc_partner_id
1227 and la.lead_assignment_id = pn.lead_assignment_id
1228 and pn.notification_type = pc_notify_type
1229 and pn.user_id = usr.user_id ;
1230
1231
1232 cursor lc_get_uniq_cm_for_pt (pc_itemtype varchar2,
1233 pc_itemkey varchar2,
1234 pc_partner_id number,
1235 pc_notify_type varchar2) is
1236 select usr.user_name, pn.resource_id
1237 from pv_lead_assignments la,
1238 pv_party_notifications pn,
1239 fnd_user usr
1240 where la.wf_item_type = pc_itemtype
1241 and la.wf_item_key = pc_itemkey
1242 and la.partner_id = pc_partner_id
1243 and la.lead_assignment_id = pn.lead_assignment_id
1244 and pn.notification_type = pc_notify_type
1245 and pn.user_id = usr.user_id
1246 and not exists
1247 (select 1
1248 from pv_lead_assignments la2,
1249 pv_party_notifications pn2
1250 where la2.wf_item_type = pc_itemtype
1251 and la2.wf_item_key = pc_itemkey
1252 and la2.partner_id <> la.partner_id
1253 and la2.status in (pv_assignment_pub.g_la_status_cm_timeout,
1254 pv_assignment_pub.g_la_status_cm_approved,
1255 pv_assignment_pub.g_la_status_cm_bypassed,
1256 pv_assignment_pub.g_la_status_cm_app_for_pt,
1257 pv_assignment_pub.g_la_status_pt_approved)
1258 and la2.lead_assignment_id = pn2.lead_assignment_id
1259 and pn2.notification_type = pc_notify_type
1260 and pn2.user_id = pn.user_id );
1261
1262 -- for removing sales team partners when timeout ( cm_bypassed, cm_approved, cm_timeout)
1263 cursor lc_get_pt_org (pc_itemtype varchar2,
1264 pc_itemkey varchar2) is
1265 select b.resource_id partner_org_rs_id
1266 from pv_lead_assignments la,
1267 jtf_rs_resource_extns b
1268 where
1269 la.wf_item_type = pc_itemtype
1270 and la.wf_item_key = pc_itemkey
1271 and la.status = pv_assignment_pub.g_la_status_pt_timeout
1272 and la.partner_id = b.source_id
1273 and b.category = 'PARTNER';
1274
1275 -- for removing sales team partners when lost chance
1276 cursor lc_get_pt_lc_org (pc_itemtype varchar2,
1277 pc_itemkey varchar2) is
1278 select b.resource_id partner_org_rs_id
1279 from pv_lead_assignments la,
1280 jtf_rs_resource_extns b
1281 where
1282 la.wf_item_type = pc_itemtype
1283 and la.wf_item_key = pc_itemkey
1284 and la.status = pv_assignment_pub.g_la_status_lost_chance
1285 and la.partner_id = b.source_id
1286 and b.category = 'PARTNER';
1287
1288 cursor lc_get_partner_org (pc_itemtype varchar2,
1289 pc_itemkey varchar2) is
1290 select
1291 b.resource_id partner_org_rs_id
1292 from pv_lead_assignments la,
1293 jtf_rs_resource_extns b
1294 where
1295 la.wf_item_type = pc_itemtype
1296 and la.wf_item_key = pc_itemkey
1297 and la.status in (pv_assignment_pub.g_la_status_cm_app_for_pt,
1298 pv_assignment_pub.g_la_status_pt_approved)
1299 and la.partner_id = b.source_id
1300 and b.category = 'PARTNER';
1301
1302 cursor lc_get_lost_chance_pt (pc_itemtype varchar2,
1303 pc_itemkey varchar2,
1304 pc_rank number) is
1305 select la.lead_assignment_id, la.partner_id
1306 from pv_lead_assignments la
1307 where la.wf_item_type = pc_itemtype
1308 and la.wf_item_key = pc_itemkey
1309 and la.assign_sequence > pc_rank
1310 and la.status in (pv_assignment_pub.g_la_status_cm_timeout,
1311 pv_assignment_pub.g_la_status_cm_bypassed,
1312 pv_assignment_pub.g_la_status_cm_app_for_pt,
1313 pv_assignment_pub.g_la_status_cm_approved);
1314
1315 BEGIN
1316 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
1317 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
1318 fnd_message.Set_Token('TEXT', 'In ' || l_api_name || ' Funcmode: ' || funcmode);
1319 fnd_msg_pub.Add;
1320 END IF;
1321
1322 if (funcmode = 'RUN') then
1323
1324 l_assignment_type := wf_engine.GetItemAttrText( itemtype => itemtype,
1325 itemkey => itemkey,
1326 aname => g_wf_attr_assignment_type);
1327
1328 l_lead_id := wf_engine.GetItemAttrNumber( itemtype => itemtype,
1329 itemkey => itemkey,
1330 aname => g_wf_attr_opportunity_id);
1331
1332 l_customer_id := wf_engine.GetItemAttrNumber( itemtype => itemtype,
1333 itemkey => itemkey,
1334 aname => g_wf_attr_customer_id);
1335
1336 l_address_id := wf_engine.GetItemAttrNumber( itemtype => itemtype,
1337 itemkey => itemkey,
1338 aname => g_wf_attr_address_id);
1339
1340 -- check to see if timeout happened
1341
1342 open lc_chk_for_timeout ( pc_itemtype => itemtype,
1343 pc_itemkey => itemkey,
1344 pc_from_activity_name => g_wf_fn_pt_response_block,
1345 pc_to_activity_id => actid);
1346
1347 fetch lc_chk_for_timeout into l_offer_outcome;
1348 close lc_chk_for_timeout;
1349
1350 if l_offer_outcome is null then
1351
1352 -- outcome is null if CM_APP_FOR_PT or CM_ADD_APP_FOR_PT
1353
1354 l_offer_outcome := g_wf_lkup_offer_approved;
1355
1356 elsif l_offer_outcome = g_wf_lkup_offer_timedout then
1357
1358 if l_assignment_type in (g_wf_lkup_single, g_wf_lkup_serial) then
1359
1360 -- partner_id is set in single and serial assignment
1361
1362 l_partner_id := wf_engine.GetItemAttrNumber( itemtype => itemtype,
1363 itemkey => itemkey,
1364 aname => g_wf_attr_partner_id);
1365
1366 open lc_get_pt_response(pc_itemtype => itemtype,
1367 pc_itemkey => itemkey,
1368 pc_partner_id => l_partner_id);
1369
1370 loop
1371 fetch lc_get_pt_response into l_assignment_id, l_partner_id, l_response;
1372 exit when lc_get_pt_response%notfound;
1373
1374 l_assignment_id_tbl.extend;
1375 l_partner_id_tbl.extend;
1376 l_response_tbl.extend;
1377 l_assignment_id_tbl(l_assignment_id_tbl.last) := l_assignment_id;
1378 l_partner_id_tbl(l_partner_id_tbl.last) := l_partner_id;
1379 l_response_tbl(l_response_tbl.last) := l_response;
1380
1381 end loop;
1382 close lc_get_pt_response;
1383
1384 elsif l_assignment_type in (g_wf_lkup_broadcast, g_wf_lkup_joint) then
1385
1386 open lc_get_all_pt_response(pc_itemtype => itemtype,
1387 pc_itemkey => itemkey);
1388
1389 loop
1390 fetch lc_get_all_pt_response into l_assignment_id, l_partner_id, l_response;
1391 exit when lc_get_all_pt_response%notfound;
1392
1393 l_assignment_id_tbl.extend;
1394 l_partner_id_tbl.extend;
1395 l_response_tbl.extend;
1396 l_assignment_id_tbl(l_assignment_id_tbl.last) := l_assignment_id;
1397 l_partner_id_tbl(l_partner_id_tbl.last) := l_partner_id;
1398 l_response_tbl(l_response_tbl.last) := l_response;
1399
1400 end loop;
1401 close lc_get_all_pt_response;
1402
1403 end if; -- assignment type check
1404
1405 if l_response_tbl.count = 0 then
1406
1407 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
1408 fnd_message.Set_Token('TEXT', 'Cannot find assignment (itemkey): ' || itemkey);
1409 fnd_msg_pub.Add;
1410
1411 raise FND_API.G_EXC_ERROR;
1412
1413 end if;
1414
1415 for i in 1 .. l_response_tbl.count loop
1416
1417 -- for single and serial, this loop should only be executed once
1418
1419 if l_response_tbl(i) in (pv_assignment_pub.g_la_status_cm_approved,
1420 pv_assignment_pub.g_la_status_cm_bypassed,
1421 pv_assignment_pub.g_la_status_cm_timeout) then
1422
1423 -- partner timed out because status was not changed
1424
1425 pv_assignment_pvt.UpdateAssignment (
1426 p_api_version_number => 1.0
1427 ,p_init_msg_list => FND_API.G_FALSE
1428 ,p_commit => FND_API.G_FALSE
1429 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
1430 ,p_action => pv_assignment_pub.g_asgn_action_status_update
1431 ,p_lead_assignment_id => l_assignment_id_tbl(i)
1432 ,p_status_date => sysdate
1433 ,p_status => pv_assignment_pub.g_la_status_pt_timeout
1434 ,p_reason_code => NULL
1435 ,p_rank => NULL
1436 ,x_msg_count => l_msg_count
1437 ,x_msg_data => l_msg_data
1438 ,x_return_status => l_return_status);
1439
1440 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1441 raise FND_API.G_EXC_ERROR;
1442 end if;
1443
1444 -- remove all partner contacts for partner from access
1445
1446 l_username_tbl.delete;
1447 l_resource_id_tbl.delete;
1448
1449 open lc_get_offered_to_for_pt (pc_itemtype => itemtype,
1450 pc_itemkey => itemkey,
1451 pc_partner_id => l_partner_id_tbl(i),
1452 pc_notify_type => pv_assignment_pub.g_notify_type_offered_to);
1453
1454 loop
1455 fetch lc_get_offered_to_for_pt into l_username, l_resource_id;
1456 exit when lc_get_offered_to_for_pt%notfound;
1457
1458 l_username_tbl.extend;
1459 l_resource_id_tbl.extend;
1460 l_username_tbl(l_username_tbl.last) := l_username;
1461 l_resource_id_tbl(l_resource_id_tbl.last) := l_resource_id;
1462
1463 end loop;
1464 close lc_get_offered_to_for_pt;
1465
1466 for i in 1 .. l_username_tbl.count loop
1467
1468 pv_assign_util_pvt.updateaccess(
1469 p_api_version_number => 1.0,
1470 p_init_msg_list => FND_API.G_FALSE,
1471 p_commit => FND_API.G_FALSE,
1472 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1473 p_itemtype => itemType,
1474 p_itemkey => itemKey,
1475 p_current_username => l_username_tbl(i),
1476 p_lead_id => l_lead_id,
1477 p_customer_id => null,
1478 p_address_id => null,
1479 p_access_action => pv_assignment_pub.G_REMOVE_ACCESS,
1480 p_resource_id => l_resource_id_tbl(i),
1481 p_access_type => pv_assignment_pub.g_pt_access,
1482 x_access_id => l_access_id,
1483 x_return_status => l_return_status,
1484 x_msg_count => l_msg_count,
1485 x_msg_data => l_msg_data);
1486
1487 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1488 raise FND_API.G_EXC_ERROR;
1489 end if;
1490
1491 end loop;
1492
1493 -- remove all CMs for partner from access if there is no more partner for the CM
1494
1495 l_username_tbl.delete;
1496 l_resource_id_tbl.delete;
1497
1498 open lc_get_uniq_cm_for_pt (pc_itemtype => itemtype,
1499 pc_itemkey => itemkey,
1500 pc_partner_id => l_partner_id_tbl(i),
1501 pc_notify_type => pv_assignment_pub.g_notify_type_matched_to);
1502 loop
1503 fetch lc_get_uniq_cm_for_pt into l_username, l_resource_id;
1504 exit when lc_get_uniq_cm_for_pt%notfound;
1505
1506 l_username_tbl.extend;
1507 l_resource_id_tbl.extend;
1508 l_username_tbl(l_username_tbl.last) := l_username;
1509 l_resource_id_tbl(l_resource_id_tbl.last) := l_resource_id;
1510
1511 end loop;
1512 close lc_get_uniq_cm_for_pt;
1513
1514 for i in 1 .. l_username_tbl.count loop
1515
1516 pv_assign_util_pvt.updateaccess(
1517 p_api_version_number => 1.0,
1518 p_init_msg_list => FND_API.G_FALSE,
1519 p_commit => FND_API.G_FALSE,
1520 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1521 p_itemtype => itemType,
1522 p_itemkey => itemKey,
1523 p_current_username => l_username_tbl(i),
1524 p_lead_id => l_lead_id,
1525 p_customer_id => null,
1526 p_address_id => null,
1527 p_access_action => pv_assignment_pub.G_REMOVE_ACCESS,
1528 p_resource_id => l_resource_id_tbl(i),
1529 p_access_type => pv_assignment_pub.g_cm_access,
1530 x_access_id => l_access_id,
1531 x_return_status => l_return_status,
1532 x_msg_count => l_msg_count,
1533 x_msg_data => l_msg_data);
1534
1535 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1536 raise FND_API.G_EXC_ERROR;
1537 end if;
1538
1539 end loop;
1540 end if;
1541 end loop;
1542
1543
1544
1545 open lc_get_pt_org (pc_itemtype => itemtype, pc_itemkey => itemkey);
1546
1547 loop
1548 fetch lc_get_pt_org into l_partner_org_rs_id;
1549 exit when lc_get_pt_org%notfound;
1550
1551 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
1552 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
1553 fnd_message.Set_Token('TEXT', 'partner org rs id for timeout '||l_partner_org_rs_id);
1554 fnd_msg_pub.Add;
1555 END IF;
1556
1557 pv_assign_util_pvt.updateaccess(
1558 p_api_version_number => 1.0,
1559 p_init_msg_list => FND_API.G_FALSE,
1560 p_commit => FND_API.G_FALSE,
1561 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1562 p_itemtype => itemType,
1563 p_itemkey => itemKey,
1564 p_current_username => NULL,
1565 p_lead_id => l_lead_id,
1566 p_customer_id => l_customer_id,
1567 p_address_id => l_address_id,
1568 p_access_action => pv_assignment_pub.G_REMOVE_ACCESS,
1569 p_resource_id => l_partner_org_rs_id,
1570 p_access_type => pv_assignment_pub.G_PT_ORG_ACCESS,
1571 x_access_id => l_access_id,
1572 x_return_status => l_return_status,
1573 x_msg_count => l_msg_count,
1574 x_msg_data => l_msg_data);
1575
1576 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1577 raise FND_API.G_EXC_ERROR;
1578 end if;
1579
1580 end loop;
1581 close lc_get_pt_org;
1582
1583
1584 elsif l_offer_outcome = g_wf_lkup_offer_withdrawn then
1585
1586 pv_assignment_pvt.removeRejectedFromAccess (
1587 p_api_version_number => 1.0,
1588 p_init_msg_list => FND_API.G_FALSE,
1589 p_commit => FND_API.G_FALSE,
1590 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1591 p_itemtype => itemType,
1592 p_itemkey => itemKey,
1593 p_partner_id => NULL,
1594 x_return_status => l_return_status,
1595 x_msg_count => l_msg_count,
1596 x_msg_data => l_msg_data);
1597
1598 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1599 raise FND_API.G_EXC_ERROR;
1600 end if;
1601
1602 end if; -- l_offer_outcome
1603
1604 if l_offer_outcome = g_wf_lkup_offer_approved then
1605
1606 if l_assignment_type in (g_wf_lkup_serial, g_wf_lkup_broadcast) then
1607
1608 if l_assignment_type = g_wf_lkup_broadcast then
1609 l_rank := -1;
1610 else
1611 l_rank := wf_engine.GetItemAttrNumber( itemtype => itemtype,
1612 itemkey => itemkey,
1613 aname => g_wf_attr_current_serial_rank);
1614 end if;
1615
1616 open lc_get_lost_chance_pt (pc_itemtype => itemtype,
1617 pc_itemkey => itemkey,
1618 pc_rank => l_rank);
1619 loop
1620
1621 fetch lc_get_lost_chance_pt into l_assignment_id, l_partner_id;
1622 exit when lc_get_lost_chance_pt%notfound;
1623
1624 l_assignment_id_tbl.extend;
1625 l_partner_id_tbl.extend;
1626 l_assignment_id_tbl(l_assignment_id_tbl.last) := l_assignment_id;
1627 l_partner_id_tbl(l_partner_id_tbl.last) := l_partner_id;
1628
1629 end loop;
1630
1631 close lc_get_lost_chance_pt;
1632
1633 for i in 1 .. l_assignment_id_tbl.count loop
1634
1635 pv_assignment_pvt.UpdateAssignment (
1636 p_api_version_number => 1.0
1637 ,p_init_msg_list => FND_API.G_FALSE
1638 ,p_commit => FND_API.G_FALSE
1639 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
1640 ,p_action => pv_assignment_pub.g_asgn_action_status_update
1641 ,p_lead_assignment_id => l_assignment_id_tbl(i)
1642 ,p_status_date => sysdate
1643 ,p_status => pv_assignment_pub.g_la_status_lost_chance
1644 ,p_reason_code => NULL
1645 ,p_rank => NULL
1646 ,x_msg_count => l_msg_count
1647 ,x_msg_data => l_msg_data
1648 ,x_return_status => l_return_status);
1649
1650 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1651 raise FND_API.G_EXC_ERROR;
1652 end if;
1653
1654 -- remove all partner contacts for partner from access
1655
1656 l_username_tbl.delete;
1657 l_resource_id_tbl.delete;
1658
1659 open lc_get_offered_to_for_pt (pc_itemtype => itemtype,
1660 pc_itemkey => itemkey,
1661 pc_partner_id => l_partner_id_tbl(i),
1662 pc_notify_type => pv_assignment_pub.g_notify_type_offered_to);
1663
1664 loop
1665 l_username_tbl.extend;
1666 l_resource_id_tbl.extend;
1667
1668 fetch lc_get_offered_to_for_pt into l_username_tbl(l_username_tbl.last),
1669 l_resource_id_tbl(l_username_tbl.last);
1670 exit when lc_get_offered_to_for_pt%notfound;
1671
1672 end loop;
1673
1674 close lc_get_offered_to_for_pt;
1675 l_username_tbl.trim;
1676 l_resource_id_tbl.trim;
1677
1678 for i in 1 .. l_username_tbl.count loop
1679
1680 pv_assign_util_pvt.updateAccess(
1681 p_api_version_number => 1.0,
1682 p_init_msg_list => FND_API.G_FALSE,
1683 p_commit => FND_API.G_FALSE,
1684 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1685 p_itemtype => itemType,
1686 p_itemkey => itemKey,
1687 p_current_username => l_username_tbl(i),
1688 p_lead_id => l_lead_id,
1689 p_customer_id => null,
1690 p_address_id => null,
1691 p_access_action => pv_assignment_pub.G_REMOVE_ACCESS,
1692 p_resource_id => l_resource_id_tbl(i),
1693 p_access_type => pv_assignment_pub.G_PT_ACCESS,
1694 x_access_id => l_access_id,
1695 x_return_status => l_return_status,
1696 x_msg_count => l_msg_count,
1697 x_msg_data => l_msg_data);
1698
1699 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1700 raise FND_API.G_EXC_ERROR;
1701 end if;
1702
1703 end loop;
1704
1705 -- remove all CMs for partner from access that are not CMs of the approved partner
1706
1707 l_username_tbl.delete;
1708 l_resource_id_tbl.delete;
1709
1710 open lc_get_uniq_cm_for_pt (pc_itemtype => itemtype,
1711 pc_itemkey => itemkey,
1712 pc_partner_id => l_partner_id_tbl(i),
1713 pc_notify_type => pv_assignment_pub.g_notify_type_matched_to);
1714 loop
1715 l_username_tbl.extend;
1716 l_resource_id_tbl.extend;
1717
1718 fetch lc_get_uniq_cm_for_pt into l_username_tbl(l_username_tbl.last),
1719 l_resource_id_tbl(l_username_tbl.last);
1720 exit when lc_get_uniq_cm_for_pt%notfound;
1721
1722 end loop;
1723
1724 close lc_get_uniq_cm_for_pt;
1725 l_username_tbl.trim;
1726 l_resource_id_tbl.trim;
1727
1728 for i in 1 .. l_username_tbl.count loop
1729
1730 pv_assign_util_pvt.updateAccess(
1731 p_api_version_number => 1.0,
1732 p_init_msg_list => FND_API.G_FALSE,
1733 p_commit => FND_API.G_FALSE,
1734 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1735 p_itemtype => itemType,
1736 p_itemkey => itemKey,
1737 p_current_username => l_username_tbl(i),
1738 p_lead_id => l_lead_id,
1739 p_customer_id => null,
1740 p_address_id => null,
1741 p_access_action => pv_assignment_pub.G_REMOVE_ACCESS,
1742 p_resource_id => l_resource_id_tbl(i),
1743 p_access_type => pv_assignment_pub.G_CM_ACCESS,
1744 x_access_id => l_access_id,
1745 x_return_status => l_return_status,
1746 x_msg_count => l_msg_count,
1747 x_msg_data => l_msg_data);
1748
1749 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1750 raise FND_API.G_EXC_ERROR;
1751 end if;
1752
1753 end loop;
1754 end loop; -- lost_chance
1755
1756 open lc_get_pt_lc_org (pc_itemtype => itemtype, pc_itemkey => itemkey);
1757
1758 loop
1759 fetch lc_get_pt_lc_org into l_lc_partner_rs_id;
1760 exit when lc_get_pt_lc_org%notfound;
1761
1762 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
1763 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
1764 fnd_message.Set_Token('TEXT', 'partner org rs id for timeout '||l_partner_org_rs_id);
1765 fnd_msg_pub.Add;
1766 END IF;
1767
1768 pv_assign_util_pvt.updateaccess(
1769 p_api_version_number => 1.0,
1770 p_init_msg_list => FND_API.G_FALSE,
1771 p_commit => FND_API.G_FALSE,
1772 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1773 p_itemtype => itemType,
1774 p_itemkey => itemKey,
1775 p_current_username => NULL,
1776 p_lead_id => l_lead_id,
1777 p_customer_id => l_customer_id,
1778 p_address_id => l_address_id,
1779 p_access_action => pv_assignment_pub.G_REMOVE_ACCESS,
1780 p_resource_id => l_lc_partner_rs_id,
1781 p_access_type => pv_assignment_pub.G_PT_ORG_ACCESS,
1782 x_access_id => l_access_id,
1783 x_return_status => l_return_status,
1784 x_msg_count => l_msg_count,
1785 x_msg_data => l_msg_data);
1786
1787 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1788 raise FND_API.G_EXC_ERROR;
1789 end if;
1790
1791 end loop;
1792 close lc_get_pt_lc_org;
1793 end if; -- end l_assignment_type in serial,broadcast
1794
1795 -- for single, serial, broadcast, joint
1796 -- add partner org to access for approved partner
1797
1798
1799
1800 open lc_get_partner_org (pc_itemtype => itemtype, pc_itemkey => itemkey);
1801
1802 loop
1803 fetch lc_get_partner_org into l_partner_org_rs_id;
1804 exit when lc_get_partner_org%notfound;
1805
1806 pv_assign_util_pvt.updateaccess(
1807 p_api_version_number => 1.0,
1808 p_init_msg_list => FND_API.G_FALSE,
1809 p_commit => FND_API.G_FALSE,
1810 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1811 p_itemtype => itemType,
1812 p_itemkey => itemKey,
1813 p_current_username => NULL,
1814 p_lead_id => l_lead_id,
1815 p_customer_id => l_customer_id,
1816 p_address_id => l_address_id,
1817 p_access_action => pv_assignment_pub.G_ADD_ACCESS,
1818 p_resource_id => l_partner_org_rs_id,
1819 p_access_type => pv_assignment_pub.G_PT_ORG_ACCESS,
1820 x_access_id => l_access_id,
1821 x_return_status => l_return_status,
1822 x_msg_count => l_msg_count,
1823 x_msg_data => l_msg_data);
1824
1825 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1826 raise FND_API.G_EXC_ERROR;
1827 end if;
1828
1829 end loop;
1830 close lc_get_partner_org;
1831
1832 end if; -- end l_offer_outcome = g_wf_lkup_offer_approved
1833
1834 -- in case of serial, we only want to send notification for current partner
1835
1836 l_partner_id := wf_engine.GetItemAttrNumber( itemtype => itemtype,
1837 itemkey => itemkey,
1838 aname => g_wf_attr_partner_id);
1839
1840
1841 if l_offer_outcome = g_wf_lkup_offer_timedout then
1842
1843 pv_assignment_pvt.send_notification (
1844 p_api_version_number => 1.0
1845 ,p_init_msg_list => FND_API.G_FALSE
1846 ,p_commit => FND_API.G_FALSE
1847 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
1848 ,p_itemtype => itemType
1849 ,p_itemkey => itemKey
1850 ,p_activity_id => actid
1851 ,P_route_stage => pv_assignment_pub.g_r_status_offered
1852 ,p_partner_id => l_partner_id
1853 ,x_return_status => l_return_status
1854 ,x_msg_count => l_msg_count
1855 ,x_msg_data => l_msg_data);
1856
1857 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1858 raise FND_API.G_EXC_ERROR;
1859 end if;
1860
1861 end if;
1862
1863 wf_engine.SetItemAttrText (itemtype => itemType,
1864 itemkey => itemKey,
1865 aname => g_wf_attr_offer_outcome,
1866 avalue => l_offer_outcome);
1867
1868 -- this will also work for serial also as the serial process
1869 -- is not exited until all pts have responded or current
1870 -- pt has accepted or the opp is withdrawn
1871
1872 wf_engine.SetItemAttrText (itemtype => itemType,
1873 itemkey => itemKey,
1874 aname => g_wf_attr_routing_outcome,
1875 avalue => l_offer_outcome);
1876
1877 l_resultout := 'COMPLETE:' || l_offer_outcome;
1878
1879 elsif (funcmode = 'CANCEL') then
1880 l_resultout := 'COMPLETE';
1881
1882 elsif (funcmode in ('RESPOND', 'FORWARD', 'TRANSFER')) then
1883 l_resultout := 'COMPLETE';
1884
1885 elsif (funcmode = 'TIMEOUT') then
1886 l_resultout := 'COMPLETE';
1887
1888 end if;
1889
1890 resultout := l_resultout;
1891
1892 EXCEPTION
1893 WHEN FND_API.G_EXC_ERROR THEN
1894
1895 fnd_msg_pub.Count_And_Get(
1896 p_encoded => FND_API.G_TRUE
1897 ,p_count => l_msg_count
1898 ,p_data => l_msg_data);
1899
1900 wf_core.context(G_PKG_NAME, l_api_name,l_msg_data);
1901 raise;
1902
1903 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1904
1905 fnd_msg_pub.Count_And_Get(
1906 p_encoded => FND_API.G_TRUE
1907 ,p_count => l_msg_count
1908 ,p_data => l_msg_data);
1909
1910 wf_core.context(G_PKG_NAME, l_api_name,l_msg_data);
1911 raise;
1912
1913 WHEN OTHERS THEN
1914
1915 fnd_msg_pub.Count_And_Get(
1916 p_encoded => FND_API.G_TRUE
1917 ,p_count => l_msg_count
1918 ,p_data => l_msg_data);
1919
1920 wf_core.context(G_PKG_NAME, l_api_name,l_msg_data);
1921 raise;
1922
1923 end PROCESS_OFFER_OUTCOME;
1924
1925
1926 procedure BYPASS_PT_APPROVAL_CHK (
1927 itemtype in varchar2,
1928 itemkey in varchar2,
1929 actid in number,
1930 funcmode in varchar2,
1931 resultout in OUT NOCOPY varchar2)
1932 IS
1933 l_api_name CONSTANT VARCHAR2(30) := 'BYPASS_PT_APPROVAL_CHK';
1934 l_api_version_number CONSTANT NUMBER := 1.0;
1935
1936 l_resultout varchar2(40);
1937 l_return_status varchar2(1);
1938 l_msg_count number;
1939 l_msg_data varchar2(2000);
1940 l_assignment_type varchar2(40);
1941 l_pt_contact_role_name varchar2(40);
1942 l_partner_id number;
1943 l_status varchar2(30);
1944
1945 cursor lc_bypass_pt_ok_chk (pc_itemtype varchar2,
1946 pc_itemkey varchar2,
1947 pc_partner_id number) is
1948 select la.status
1949 from pv_lead_assignments la
1950 where la.wf_item_type = pc_itemtype
1951 and la.wf_item_key = pc_itemkey
1952 and la.partner_id = pc_partner_id;
1953
1954 cursor lc_any_bypass_pt_ok_chk (pc_itemtype varchar2,
1955 pc_itemkey varchar2) is
1956 select la.status
1957 from pv_lead_assignments la
1958 where la.wf_item_type = pc_itemtype
1959 and la.wf_item_key = pc_itemkey
1960 and la.status = pv_assignment_pub.g_la_status_cm_app_for_pt;
1961
1962
1963 BEGIN
1964
1965 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
1966 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
1967 fnd_message.Set_Token('TEXT', 'In ' || l_api_name || ' Funcmode: ' || funcmode);
1968 fnd_msg_pub.Add;
1969 END IF;
1970
1971 if (funcmode = 'RUN') then
1972
1973 l_assignment_type := wf_engine.GetItemAttrText( itemtype => itemtype,
1974 itemkey => itemkey,
1975 aname => g_wf_attr_assignment_type);
1976
1977 if l_assignment_type in (g_wf_lkup_single, g_wf_lkup_serial) then
1978
1979 -- partner_id is set in single and serial assignment
1980
1981 l_partner_id := wf_engine.GetItemAttrNumber( itemtype => itemtype,
1982 itemkey => itemkey,
1983 aname => g_wf_attr_partner_id);
1984
1985 open lc_bypass_pt_ok_chk (pc_itemtype => itemtype, pc_itemkey => itemkey, pc_partner_id => l_partner_id);
1986 fetch lc_bypass_pt_ok_chk into l_status;
1987 close lc_bypass_pt_ok_chk;
1988
1989 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
1990 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
1991 fnd_message.Set_Token('TEXT', 'Bypass PT chk: ' || l_status);
1992 fnd_msg_pub.Add;
1993 END IF;
1994
1995 elsif l_assignment_type = g_wf_lkup_joint then
1996
1997 open lc_any_bypass_pt_ok_chk (pc_itemtype => itemtype, pc_itemkey => itemkey);
1998 fetch lc_any_bypass_pt_ok_chk into l_status;
1999 close lc_any_bypass_pt_ok_chk;
2000
2001 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
2002 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
2003 fnd_message.Set_Token('TEXT', 'Bypass PT chk: ' || nvl(l_status, 'N'));
2004 fnd_msg_pub.Add;
2005 END IF;
2006
2007 elsif l_assignment_type = g_wf_lkup_broadcast then
2008
2009 -- not supported in broadcast
2010
2011 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
2012 fnd_message.Set_token('TEXT', 'Incorrect WF function usage for assignment type: ' || l_assignment_type);
2013 fnd_msg_pub.Add;
2014 raise FND_API.G_EXC_ERROR;
2015
2016 else
2017
2018 fnd_message.Set_Name('PV', 'PV_INVALID_ASSIGN_TYPE');
2019 fnd_message.SET_TOKEN('TYPE', l_assignment_type);
2020 fnd_msg_pub.ADD;
2021 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2022
2023 end if;
2024
2025 if l_status = pv_assignment_pub.g_la_status_cm_app_for_pt then
2026 l_resultout := 'COMPLETE:' || g_wf_lkup_yes;
2027 else
2028 l_resultout := 'COMPLETE:' || g_wf_lkup_no;
2029 end if;
2030
2031
2032 elsif (funcmode = 'CANCEL') then
2033 l_resultout := 'COMPLETE';
2034
2035 elsif (funcmode in ('RESPOND', 'FORWARD', 'TRANSFER')) then
2036 l_resultout := 'COMPLETE';
2037
2038 elsif (funcmode = 'TIMEOUT') then
2039 l_resultout := 'COMPLETE';
2040
2041 end if;
2042
2043 resultout := l_resultout;
2044
2045 EXCEPTION
2046 WHEN FND_API.G_EXC_ERROR THEN
2047
2048 fnd_msg_pub.Count_And_Get(
2049 p_encoded => FND_API.G_TRUE
2050 ,p_count => l_msg_count
2051 ,p_data => l_msg_data);
2052
2053 wf_core.context(G_PKG_NAME, l_api_name,l_msg_data);
2054 raise;
2055
2056 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2057
2058 fnd_msg_pub.Count_And_Get(
2059 p_encoded => FND_API.G_TRUE
2060 ,p_count => l_msg_count
2061 ,p_data => l_msg_data);
2062
2063 wf_core.context(G_PKG_NAME, l_api_name,l_msg_data);
2064 raise;
2065
2066 WHEN OTHERS THEN
2067
2068 fnd_msg_pub.Count_And_Get(
2069 p_encoded => FND_API.G_TRUE
2070 ,p_count => l_msg_count
2071 ,p_data => l_msg_data);
2072
2073 wf_core.context(G_PKG_NAME, l_api_name,l_msg_data);
2074 raise;
2075
2076 end BYPASS_PT_APPROVAL_CHK;
2077
2078
2079 procedure NEED_PT_OK_CHK (
2080 itemtype in varchar2,
2081 itemkey in varchar2,
2082 actid in number,
2083 funcmode in varchar2,
2084 resultout in OUT NOCOPY varchar2)
2085 IS
2086 l_api_name CONSTANT VARCHAR2(30) := 'NEED_PT_OK_CHK';
2087 l_api_version_number CONSTANT NUMBER := 1.0;
2088
2089 l_resultout varchar2(40);
2090 l_return_status varchar2(1);
2091 l_msg_count number;
2092 l_msg_data varchar2(2000);
2093 l_assignment_type varchar2(40);
2094 l_partner_id number;
2095 l_pt_contact_role_name varchar2(40);
2096 l_status varchar2(30);
2097
2098 cursor lc_any_need_pt_ok_chk (pc_itemtype varchar2,
2099 pc_itemkey varchar2) is
2100 select la.status
2101 from pv_lead_assignments la
2102 where la.wf_item_type = pc_itemtype
2103 and la.wf_item_key = pc_itemkey
2104 and la.status in (pv_assignment_pub.g_la_status_cm_approved,
2105 pv_assignment_pub.g_la_status_cm_added,
2106 pv_assignment_pub.g_la_status_cm_bypassed,
2107 pv_assignment_pub.g_la_status_cm_timeout);
2108 BEGIN
2109
2110 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
2111 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
2112 fnd_message.Set_Token('TEXT', 'In ' || l_api_name || ' Funcmode: ' || funcmode);
2113 fnd_msg_pub.Add;
2114 END IF;
2115
2116 if (funcmode = 'RUN') then
2117
2118 l_assignment_type := wf_engine.GetItemAttrText( itemtype => itemtype,
2119 itemkey => itemkey,
2120 aname => g_wf_attr_assignment_type);
2121
2122 if l_assignment_type in (g_wf_lkup_single, g_wf_lkup_serial, g_wf_lkup_broadcast) then
2123
2124 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
2125 fnd_message.Set_token('TEXT', 'Incorrect WF function usage for assignment type: ' || l_assignment_type);
2126 fnd_msg_pub.Add;
2127 raise FND_API.G_EXC_ERROR;
2128
2129 elsif l_assignment_type = g_wf_lkup_joint then
2130
2131 open lc_any_need_pt_ok_chk (pc_itemtype => itemtype, pc_itemkey => itemkey);
2132 fetch lc_any_need_pt_ok_chk into l_status;
2133 close lc_any_need_pt_ok_chk;
2134
2135 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
2136 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
2137 fnd_message.Set_Token('TEXT', 'Bypass PT chk: ' || nvl(l_status, 'N'));
2138 fnd_msg_pub.Add;
2139 END IF;
2140
2141 else
2142
2143 fnd_message.Set_Name('PV', 'PV_INVALID_ASSIGN_TYPE');
2144 fnd_message.SET_TOKEN('TYPE', l_assignment_type);
2145 fnd_msg_pub.ADD;
2146 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2147
2148 end if;
2149
2150 if l_status is NULL then
2151 l_resultout := 'COMPLETE:' || g_wf_lkup_no;
2152 else
2153 l_resultout := 'COMPLETE:' || g_wf_lkup_yes;
2154 end if;
2155
2156 elsif (funcmode = 'CANCEL') then
2157 l_resultout := 'COMPLETE';
2158
2159 elsif (funcmode in ('RESPOND', 'FORWARD', 'TRANSFER')) then
2160 l_resultout := 'COMPLETE';
2161
2162 elsif (funcmode = 'TIMEOUT') then
2163 l_resultout := 'COMPLETE';
2164
2165 end if;
2166
2167 resultout := l_resultout;
2168
2169 EXCEPTION
2170 WHEN FND_API.G_EXC_ERROR THEN
2171
2172 fnd_msg_pub.Count_And_Get(
2173 p_encoded => FND_API.G_TRUE
2174 ,p_count => l_msg_count
2175 ,p_data => l_msg_data);
2176
2177 wf_core.context(G_PKG_NAME, l_api_name,l_msg_data);
2178 raise;
2179
2180 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2181
2182 fnd_msg_pub.Count_And_Get(
2183 p_encoded => FND_API.G_TRUE
2184 ,p_count => l_msg_count
2185 ,p_data => l_msg_data);
2186
2187 wf_core.context(G_PKG_NAME, l_api_name,l_msg_data);
2188 raise;
2189
2190 WHEN OTHERS THEN
2191
2192 fnd_msg_pub.Count_And_Get(
2193 p_encoded => FND_API.G_TRUE
2194 ,p_count => l_msg_count
2195 ,p_data => l_msg_data);
2196
2197 wf_core.context(G_PKG_NAME, l_api_name,l_msg_data);
2198 raise;
2199
2200 end NEED_PT_OK_CHK;
2201
2202
2203 procedure WRAPUP_PROCESSING (
2204 itemtype in varchar2,
2205 itemkey in varchar2,
2206 actid in number,
2207 funcmode in varchar2,
2208 resultout in OUT NOCOPY varchar2)
2209
2210 IS
2211 l_api_name CONSTANT VARCHAR2(30) := 'WRAPUP_PROCESSING';
2212 l_api_version_number CONSTANT NUMBER := 1.0;
2213
2214 l_resultout varchar2(30);
2215 l_return_status varchar2(1);
2216 l_routing_outcome varchar2(30);
2217 l_routing_stage varchar2(30);
2218 l_routing_type varchar2(30);
2219 l_partner_id number := NULL;
2220
2221 l_assignment_type varchar2(30);
2222
2223 l_msg_count number;
2224 l_msg_data varchar2(2000);
2225
2226 l_notify_profile varchar2(30);
2227 l_lead_id number;
2228 l_notify_pt_flag varchar2(1);
2229 l_notify_cm_flag varchar2(1);
2230 l_notify_am_flag varchar2(1);
2231 l_notify_ot_flag varchar2(1);
2232 l_notify_enabled_flag varchar2(1);
2233 l_combination_count pls_integer := 0;
2234 lc_ref_cursor pv_assignment_pub.g_ref_cursor_type;
2235
2236 BEGIN
2237
2238 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
2239 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
2240 fnd_message.Set_Token('TEXT', 'In ' || l_api_name || ' Funcmode: ' || funcmode);
2241 fnd_msg_pub.Add;
2242 END IF;
2243
2244 if (funcmode = 'RUN') then
2245
2246 l_routing_outcome := wf_engine.GetItemAttrText(itemtype => itemtype,
2247 itemkey => itemkey,
2248 aname => g_wf_attr_routing_outcome);
2249
2250 l_lead_id := wf_engine.GetItemAttrNumber( itemtype => itemtype,
2251 itemkey => itemkey,
2252 aname => g_wf_attr_opportunity_id);
2253
2254
2255 if l_routing_outcome in (pv_workflow_pub.g_wf_lkup_match_rejected,
2256 pv_workflow_pub.g_wf_lkup_offer_rejected,
2257 pv_workflow_pub.g_wf_lkup_offer_timedout) then
2258
2259 l_routing_stage := pv_assignment_pub.g_r_status_recycled;
2260
2261 --000000000000000000000000000000000000000000000000000000000000000000000000
2262 -- In a joint routing, if at least one of the partners has approved the
2263 -- routing, the routing status should remain ACTIVE even though the
2264 -- other partners may have timed out or rejected the assignment.
2265 --000000000000000000000000000000000000000000000000000000000000000000000000
2266 FOR x IN (SELECT routing_type
2267 FROM pv_lead_workflows
2268 WHERE lead_id = l_lead_id AND
2269 latest_routing_flag = 'Y')
2270 LOOP
2271 l_routing_type := x.routing_type;
2272 END LOOP;
2273
2274 -- use global constant vairables instead
2275 IF (l_routing_type = 'JOINT') THEN
2276 FOR x IN (SELECT COUNT(*) approved_count
2277 FROM pv_lead_assignments
2278 WHERE wf_item_type = itemtype AND
2279 wf_item_key = itemkey AND
2280 status IN (pv_assignment_pub.g_la_status_cm_add_app_for_pt,
2281 pv_assignment_pub.g_la_status_cm_app_for_pt,
2282 pv_assignment_pub.g_la_status_pt_approved))
2283 LOOP
2284 IF (x.approved_count > 0) THEN
2285 l_routing_stage := PV_ASSIGNMENT_PUB.g_r_status_active;
2286 END IF;
2287 END LOOP;
2288 END IF;
2289 --000000000000000000000000000000000000000000000000000000000000000000000000
2290
2291
2292 elsif l_routing_outcome in (pv_workflow_pub.g_wf_lkup_match_withdrawn,
2293 pv_workflow_pub.g_wf_lkup_offer_withdrawn) then
2294
2295 l_routing_stage := pv_assignment_pub.g_r_status_withdrawn;
2296
2297 IF l_routing_outcome = pv_workflow_pub.g_wf_lkup_offer_withdrawn THEN
2298
2299 l_assignment_type := wf_engine.GetItemAttrText(itemtype => itemtype,
2300 itemkey => itemkey,
2301 aname => pv_workflow_pub.g_wf_attr_assignment_type);
2302
2303 IF l_assignment_type = g_wf_lkup_serial THEN
2304 l_partner_id := wf_engine.GetItemAttrNumber( itemtype => itemtype,
2305 itemkey => itemkey,
2306 aname => g_wf_attr_partner_id);
2307 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
2308 fnd_message.Set_Token('TEXT', 'l_partner_id ' || l_partner_id);
2309 fnd_msg_pub.Add;
2310
2311 END IF;
2312
2313 END IF;
2314
2315 elsif l_routing_outcome in (pv_workflow_pub.g_wf_lkup_offer_approved) then
2316
2317 l_routing_stage := pv_assignment_pub.g_r_status_active;
2318
2319 else
2320
2321 fnd_message.SET_NAME('PV', 'Invalid routing outcome: ' || l_routing_outcome);
2322 fnd_msg_pub.ADD;
2323 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2324
2325 end if;
2326 IF l_routing_stage <> pv_assignment_pub.g_r_status_active THEN
2327 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
2328 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
2329 fnd_message.Set_Token('TEXT', 'checking and removing preferred partner');
2330 fnd_msg_pub.Add;
2331 END IF;
2332 IF l_lead_id IS NOT NULL THEN
2333 PV_ASSIGN_UTIL_PVT.removePreferedPartner
2334 (
2335 p_api_version_number => 1.0,
2336 p_init_msg_list => FND_API.G_FALSE,
2337 p_commit => FND_API.G_FALSE,
2338 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
2339 p_lead_id => l_lead_id,
2340 p_item_type => itemtype,
2341 p_item_key => itemkey,
2342 p_partner_id => NULL,
2343 x_return_status => l_return_status,
2344 x_msg_count => l_msg_count,
2345 x_msg_data => l_msg_data
2346 );
2347 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2348 RAISE FND_API.G_EXC_ERROR;
2349 END IF;
2350 END IF;
2351
2352
2353 END IF;
2354 pv_assignment_pvt.update_routing_stage (
2355 p_api_version_number => 1.0,
2356 p_init_msg_list => FND_API.G_FALSE,
2357 p_commit => FND_API.G_FALSE,
2358 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
2359 p_itemType => itemtype,
2360 p_itemKey => itemKey,
2361 p_routing_stage => l_routing_stage,
2362 p_active_but_open_flag => 'N',
2363 x_return_status => l_return_status,
2364 x_msg_count => l_msg_count,
2365 x_msg_data => l_msg_data);
2366
2367 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
2368 raise FND_API.G_EXC_ERROR;
2369 end if;
2370
2371 pv_assignment_pvt.send_notification (
2372 p_api_version_number => 1.0
2373 ,p_init_msg_list => FND_API.G_FALSE
2374 ,p_commit => FND_API.G_FALSE
2375 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
2376 ,p_itemtype => itemType
2377 ,p_itemkey => itemKey
2378 ,p_activity_id => actid
2379 ,P_route_stage => l_routing_stage
2380 ,p_partner_id => l_partner_id
2381 ,x_return_status => l_return_status
2382 ,x_msg_count => l_msg_count
2383 ,x_msg_data => l_msg_data);
2384
2385 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
2386 raise FND_API.G_EXC_ERROR;
2387 end if;
2388
2389 l_resultout := 'COMPLETE:null';
2390
2391 elsif (funcmode = 'CANCEL') then
2392 l_resultout := 'COMPLETE';
2393
2394 elsif (funcmode in ('RESPOND', 'FORWARD', 'TRANSFER')) then
2395 l_resultout := 'COMPLETE';
2396
2397 elsif (funcmode = 'TIMEOUT') then
2398 l_resultout := 'COMPLETE';
2399
2400 end if;
2401
2402 resultout := l_resultout;
2403
2404 EXCEPTION
2405 WHEN FND_API.G_EXC_ERROR THEN
2406
2407 fnd_msg_pub.Count_And_Get(
2408 p_encoded => FND_API.G_TRUE
2409 ,p_count => l_msg_count
2410 ,p_data => l_msg_data);
2411
2412 wf_core.context(G_PKG_NAME, l_api_name,l_msg_data);
2413 raise;
2414
2415 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2416
2417 fnd_msg_pub.Count_And_Get(
2418 p_encoded => FND_API.G_TRUE
2419 ,p_count => l_msg_count
2420 ,p_data => l_msg_data);
2421
2422 wf_core.context(G_PKG_NAME, l_api_name,l_msg_data);
2423 raise;
2424
2425 WHEN OTHERS THEN
2426
2427 fnd_msg_pub.Count_And_Get(
2428 p_encoded => FND_API.G_TRUE
2429 ,p_count => l_msg_count
2430 ,p_data => l_msg_data);
2431
2432 wf_core.context(G_PKG_NAME, l_api_name,l_msg_data);
2433 raise;
2434
2435 end WRAPUP_PROCESSING;
2436
2437 procedure ABANDON_FYI (
2438 itemtype in varchar2,
2439 itemkey in varchar2,
2440 actid in number,
2441 funcmode in varchar2,
2442 resultout in OUT NOCOPY varchar2)
2443
2444 IS
2445 l_api_name CONSTANT VARCHAR2(30) := 'ABANDON_FYI';
2446 l_api_version_number CONSTANT NUMBER := 1.0;
2447
2448 l_resultout varchar2(30);
2449 l_return_status varchar2(1);
2450 l_routing_stage varchar2(30);
2451 l_msg_count number;
2452 l_msg_data varchar2(2000);
2453 l_partner_id NUMBER;
2454
2455
2456 BEGIN
2457
2458 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
2459 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
2460 fnd_message.Set_Token('TEXT', 'In ' || l_api_name || ' Funcmode: ' || funcmode);
2461 fnd_msg_pub.Add;
2462 END IF;
2463
2464 if (funcmode = 'RUN') then
2465
2466 l_partner_id := wf_engine.GetItemAttrText(itemtype => itemType,
2467 itemkey => itemKey,
2468 aname => pv_workflow_pub.g_wf_attr_ext_org_party_id);
2469
2470 pv_assignment_pvt.send_notification (
2471 p_api_version_number => 1.0
2472 ,p_init_msg_list => FND_API.G_FALSE
2473 ,p_commit => FND_API.G_FALSE
2474 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
2475 ,p_itemtype => itemType
2476 ,p_itemkey => itemKey
2477 ,p_activity_id => actid
2478 ,P_route_stage => pv_assignment_pub.g_r_status_abandoned
2479 ,p_partner_id => l_partner_id
2480 ,x_return_status => l_return_status
2481 ,x_msg_count => l_msg_count
2482 ,x_msg_data => l_msg_data);
2483
2484 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
2485 raise FND_API.G_EXC_ERROR;
2486 end if;
2487
2488
2489 elsif (funcmode = 'CANCEL') then
2490 l_resultout := 'COMPLETE';
2491
2492 elsif (funcmode in ('RESPOND', 'FORWARD', 'TRANSFER')) then
2493 l_resultout := 'COMPLETE';
2494
2495 elsif (funcmode = 'TIMEOUT') then
2496 l_resultout := 'TIMEOUT';
2497
2498 end if;
2499
2500 resultout := l_resultout;
2501
2502 EXCEPTION
2503 WHEN FND_API.G_EXC_ERROR THEN
2504
2505 fnd_msg_pub.Count_And_Get(
2506 p_encoded => FND_API.G_TRUE
2507 ,p_count => l_msg_count
2508 ,p_data => l_msg_data);
2509
2510 wf_core.context(G_PKG_NAME, l_api_name,l_msg_data);
2511 raise;
2512
2513 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2514
2515 fnd_msg_pub.Count_And_Get(
2516 p_encoded => FND_API.G_TRUE
2517 ,p_count => l_msg_count
2518 ,p_data => l_msg_data);
2519
2520 wf_core.context(G_PKG_NAME, l_api_name,l_msg_data);
2521 raise;
2522
2523 WHEN OTHERS THEN
2524
2525 fnd_msg_pub.Count_And_Get(
2526 p_encoded => FND_API.G_TRUE
2527 ,p_count => l_msg_count
2528 ,p_data => l_msg_data);
2529
2530 wf_core.context(G_PKG_NAME, l_api_name,l_msg_data);
2531 raise;
2532
2533 end ABANDON_FYI;
2534
2535 procedure WITHDRAW_FYI (
2536 itemtype in varchar2,
2537 itemkey in varchar2,
2538 actid in number,
2539 funcmode in varchar2,
2540 resultout in OUT NOCOPY varchar2)
2541
2542 IS
2543 l_api_name CONSTANT VARCHAR2(30) := 'WITHDRAW_FYI';
2544 l_api_version_number CONSTANT NUMBER := 1.0;
2545
2546 l_resultout varchar2(30);
2547 l_return_status varchar2(1);
2548 l_routing_stage varchar2(30);
2549 l_msg_count number;
2550 l_msg_data varchar2(2000);
2551 l_partner_id NUMBER;
2552
2553
2554 BEGIN
2555
2556 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
2557 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
2558 fnd_message.Set_Token('TEXT', 'In ' || l_api_name || ' Funcmode: ' || funcmode);
2559 fnd_msg_pub.Add;
2560 END IF;
2561
2562 if (funcmode = 'RUN') then
2563
2564 pv_assignment_pvt.send_notification (
2565 p_api_version_number => 1.0
2566 ,p_init_msg_list => FND_API.G_FALSE
2567 ,p_commit => FND_API.G_FALSE
2568 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
2569 ,p_itemtype => itemType
2570 ,p_itemkey => itemKey
2571 ,p_activity_id => actid
2572 ,P_route_stage => pv_assignment_pub.g_r_status_withdrawn
2573 ,p_partner_id => NULL
2574 ,x_return_status => l_return_status
2575 ,x_msg_count => l_msg_count
2576 ,x_msg_data => l_msg_data);
2577
2578 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
2579 raise FND_API.G_EXC_ERROR;
2580 end if;
2581
2582
2583 elsif (funcmode = 'CANCEL') then
2584 l_resultout := 'COMPLETE';
2585
2586 elsif (funcmode in ('RESPOND', 'FORWARD', 'TRANSFER')) then
2587 l_resultout := 'COMPLETE';
2588
2589 elsif (funcmode = 'TIMEOUT') then
2590 l_resultout := 'TIMEOUT';
2591
2592 end if;
2593
2594 resultout := l_resultout;
2595
2596 EXCEPTION
2597 WHEN FND_API.G_EXC_ERROR THEN
2598
2599 fnd_msg_pub.Count_And_Get(
2600 p_encoded => FND_API.G_TRUE
2601 ,p_count => l_msg_count
2602 ,p_data => l_msg_data);
2603
2604 wf_core.context(G_PKG_NAME, l_api_name,l_msg_data);
2605 raise;
2606
2607 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2608
2609 fnd_msg_pub.Count_And_Get(
2610 p_encoded => FND_API.G_TRUE
2611 ,p_count => l_msg_count
2612 ,p_data => l_msg_data);
2613
2614 wf_core.context(G_PKG_NAME, l_api_name,l_msg_data);
2615 raise;
2616
2617 WHEN OTHERS THEN
2618
2619 fnd_msg_pub.Count_And_Get(
2620 p_encoded => FND_API.G_TRUE
2621 ,p_count => l_msg_count
2622 ,p_data => l_msg_data);
2623
2624 wf_core.context(G_PKG_NAME, l_api_name,l_msg_data);
2625 raise;
2626
2627 end WITHDRAW_FYI;
2628
2629 procedure GET_PRODUCTS (document_id in varchar2,
2630 display_type in varchar2,
2631 document in out nocopy varchar2,
2632 document_type in out nocopy varchar2) IS
2633
2634 -- ----------------------------------------------------------------------------------
2635 -- pklin
2636 -- Eliminated reference to wf_notifications from all the queries
2637 -- ----------------------------------------------------------------------------------
2638 cursor lc_get_products (pc_entity_id number, pc_notification_id number) is
2639 select c.CONCAT_CAT_PARENTAGE, decode(nvl(b.total_amount,0),0,'', b.total_amount || ' ' || a.currency_code) amount
2640 from as_leads_all a, as_lead_lines_all b, eni_prod_den_hrchy_parents_v c
2641 where a.lead_id = pc_entity_id
2642 and a.lead_id = b.lead_id
2643 and b.product_cat_set_id = c.category_set_id
2644 and b.product_category_id = c.category_id;
2645
2646 cursor lc_max_products_length (pc_entity_id number, pc_notification_id number) is
2647 select max(length(c.CONCAT_CAT_PARENTAGE)), max(length(to_char(b.total_amount) || ' ' || a.currency_code))
2648 from as_leads_all a, as_lead_lines_all b, eni_prod_den_hrchy_parents_v c
2649 where a.lead_id = pc_entity_id
2650 and a.lead_id = b.lead_id
2651 and b.product_cat_set_id = c.category_set_id
2652 and b.product_category_id = c.category_id;
2653
2654 cursor lc_get_label (pc_notification_id number) is
2655 select attribute_code,attribute_label_long
2656 from ak_attributes_tl ak
2657 where attribute_application_id = 522
2658 AND ATTRIBUTE_code in ('ASF_AMOUNT','ASF_PRODUCT_CATEGORY')
2659 AND ak.language= userenv('LANG');
2660
2661 l_max_length_products number;
2662 l_max_length_amount number;
2663 l_entity_id number;
2664 l_notification_id number;
2665 l_label_amount varchar2(30);
2666 l_label_products varchar2(200);
2667 l_products_list varchar2(4000);
2668 l_has_products boolean;
2669
2670 BEGIN
2671
2672 l_entity_id := substr(document_id, 1, instr(document_id, ':')-1);
2673 l_notification_id := substr(document_id, length(l_entity_id)+2);
2674
2675 if display_type = 'text/plain' then
2676 open lc_max_products_length(pc_entity_id => l_entity_id, pc_notification_id => l_notification_id);
2677 fetch lc_max_products_length into l_max_length_products, l_max_length_amount;
2678 close lc_max_products_length;
2679 end if;
2680
2681 for l_label_rec in lc_get_label(pc_notification_id => l_notification_id) loop
2682
2683 if l_label_rec.attribute_code = 'ASF_AMOUNT' then
2684 l_label_amount := l_label_rec.attribute_label_long;
2685 l_max_length_amount := greatest(l_max_length_amount, length(l_label_amount));
2686 elsif l_label_rec.attribute_code = 'ASF_PRODUCT_CATEGORY' then
2687 l_label_products := l_label_rec.attribute_label_long;
2688 l_max_length_products := greatest(l_max_length_products, length(l_label_products));
2689 end if;
2690
2691 end loop;
2692
2693 for l_prod_rec in lc_get_products(pc_entity_id => l_entity_id, pc_notification_id => l_notification_id)
2694 loop
2695 l_has_products := true;
2696 if display_type = 'text/html' then
2697 l_products_list := l_products_list || '<tr><td>' || l_prod_rec.concat_cat_parentage ||
2698 '</td><td align="right">' || l_prod_rec.amount || '</td></tr>';
2699 elsif display_type = 'text/plain' then
2700 l_products_list := l_products_list || rpad(l_prod_rec.concat_cat_parentage, l_max_length_products + 5) ||
2701 lpad(l_prod_rec.amount, l_max_length_amount) || fnd_global.local_chr(10);
2702 end if;
2703 end loop;
2704
2705 if l_has_products and display_type = 'text/html' then
2706 l_products_list := '<table><tr><th align="left">' || l_label_products || '</th><th align="right">' ||
2707 l_label_amount || '</th></tr>' || l_products_list || '</table>';
2708
2709 elsif l_has_products and display_type = 'text/plain' then
2710 l_products_list := rpad(l_label_products, l_max_length_products+2) || lpad(l_label_amount, l_max_length_amount+2) ||
2711 fnd_global.local_chr(10) || l_products_list;
2712 end if;
2713
2714 document := l_products_list;
2715
2716 END;
2717
2718 procedure GET_OPPTY_CONTACTS (document_id in varchar2,
2719 display_type in varchar2,
2720 document in out nocopy varchar2,
2721 document_type in out nocopy varchar2) IS
2722
2723 cursor lc_get_contacts (pc_lead_id number) is
2724 SELECT EMAIL_ADDRESS emailAddress, PRIMARY_CONTACT_FLAG primaryContact,
2725 PERSON_PRE_NAME_ADJUNCT title, FIRST_NAME||',' || LAST_NAME fullName,
2726 trim(PHONE_COUNTRY_CODE || ' ' || AREA_CODE || ' ' || PHONE_NUMBER || ' ' ||EXTENSION) phoneNumber
2727 FROM AS_OPPORTUNITY_CONTACTS_V WHERE lead_id = pc_lead_id;
2728
2729 cursor lc_max_lengths (pc_entity_id number) is
2730 SELECT max(length(EMAIL_ADDRESS)),
2731 max(length(PERSON_PRE_NAME_ADJUNCT)), max(length( FIRST_NAME||',' || LAST_NAME)),
2732 max(length(trim(PHONE_COUNTRY_CODE || ' ' || AREA_CODE || ' ' || PHONE_NUMBER || ' ' ||EXTENSION)))
2733 FROM AS_OPPORTUNITY_CONTACTS_V WHERE lead_id = pc_entity_id;
2734
2735 cursor lc_get_label (pc_notification_id number) is
2736 select attribute_code,attribute_label_long
2737 from ak_attributes_tl ak
2738 where attribute_application_id = 522
2739 AND ATTRIBUTE_code in ('ASF_EMAIL','ASF_PRIMARY','ASF_TITLE','ASF_PHONE','ASF_CONTACT_NAME')
2740 AND ak.language= userenv('LANG');
2741
2742 l_label_title varchar2(50);
2743 l_label_name varchar2(50);
2744 l_label_primary varchar2(50);
2745 l_label_phone varchar2(50);
2746 l_label_email varchar2(50);
2747
2748 l_max_length_email number := 0;
2749 l_max_length_title number := 0;
2750 l_max_length_name number := 0;
2751 l_max_length_phone number := 0;
2752
2753 l_entity_id number;
2754 l_notification_id number;
2755 l_contacts_list varchar2(4000);
2756 l_has_contacts boolean;
2757
2758 BEGIN
2759
2760 l_entity_id := substr(document_id, 1, instr(document_id, ':')-1);
2761 l_notification_id := substr(document_id, length(l_entity_id)+2);
2762
2763 if display_type = 'text/plain' then
2764 open lc_max_lengths(pc_entity_id => l_entity_id);
2765 fetch lc_max_lengths into l_max_length_email,l_max_length_title,l_max_length_name,l_max_length_phone;
2766 close lc_max_lengths;
2767 end if;
2768
2769 for l_label_rec in lc_get_label(pc_notification_id => l_notification_id) loop
2770
2771 if l_label_rec.attribute_code = 'ASF_EMAIL' then
2772 l_label_email := l_label_rec.attribute_label_long;
2773 l_max_length_email := greatest(l_max_length_email, length(l_label_email));
2774 elsif l_label_rec.attribute_code = 'ASF_PRIMARY' then
2775 l_label_primary := l_label_rec.attribute_label_long;
2776 elsif l_label_rec.attribute_code = 'ASF_TITLE' then
2777 l_label_title := l_label_rec.attribute_label_long;
2778 l_max_length_title := greatest(l_max_length_title, length(l_label_title));
2779 elsif l_label_rec.attribute_code = 'ASF_PHONE' then
2780 l_label_phone := l_label_rec.attribute_label_long;
2781 l_max_length_phone := greatest(l_max_length_phone, length(l_label_phone));
2782 elsif l_label_rec.attribute_code = 'ASF_CONTACT_NAME' then
2783 l_label_name := l_label_rec.attribute_label_long;
2784 l_max_length_name := greatest(l_max_length_name, length(l_label_name));
2785 end if;
2786
2787 end loop;
2788
2789 for l_contact_rec in lc_get_contacts(pc_lead_id => l_entity_id)
2790 loop
2791 l_has_contacts := true;
2792 if display_type = 'text/html' then
2793 l_contacts_list := l_contacts_list || '<tr>' ||
2794 '<td nowrap>' || l_contact_rec.title || '</td>' ||
2795 '<td nowrap>' || l_contact_rec.fullName || '</td>' ||
2796 '<td nowrap>' || l_contact_rec.phoneNumber || '</td>' ||
2797 '<td nowrap>' || l_contact_rec.emailAddress || '</td>' ||
2798 '<td align="center">' || l_contact_rec.primaryContact || '</td></tr>';
2799
2800 elsif display_type = 'text/plain' then
2801 l_contacts_list := l_contacts_list ||
2802 rpad( nvl(l_contact_rec.title,' ') , l_max_length_title+2) ||
2803 rpad( nvl(l_contact_rec.fullName,' ') , l_max_length_name+2) ||
2804 rpad( nvl(l_contact_rec.phoneNumber,' ') , l_max_length_phone+2) ||
2805 rpad( nvl(l_contact_rec.emailAddress,' ') , l_max_length_email+2) ||
2806 ' ' || l_contact_rec.primaryContact || fnd_global.local_chr(10);
2807 end if;
2808 end loop;
2809
2810 if l_has_contacts and display_type = 'text/html' then
2811 l_contacts_list := '<table><tr><th align="left">' || l_label_title || '</th><th align="left">' ||
2812 l_label_name || '</th><th align="left">' ||
2813 l_label_phone || '</th><th align="left">' || l_label_email || '</th><th>' || l_label_primary ||
2814 '</th></tr>' || l_contacts_list || '</table>';
2815
2816 elsif l_has_contacts and display_type = 'text/plain' then
2817 l_contacts_list := rpad(l_label_title, l_max_length_title+2) || rpad(l_label_name, l_max_length_name+2) ||
2818 rpad(l_label_phone, l_max_length_phone+2) || rpad(l_label_email, l_max_length_email+2) ||
2819 l_label_primary || fnd_global.local_chr(10) || l_contacts_list;
2820 end if;
2821
2822 document := l_contacts_list;
2823
2824 END;
2825
2826 procedure GET_PUBLISH_NOTES (document_id in varchar2,
2827 display_type in varchar2,
2828 document in out nocopy varchar2,
2829 document_type in out nocopy varchar2) IS
2830
2831 cursor lc_get_notes(pc_entity_id number) is
2832 select entered_date, entered_by_name, notes, NOTES_DETAIL
2833 from jtf_notes_vl
2834 where source_object_code = 'OPPORTUNITY'
2835 AND SOURCE_OBJECT_ID = pc_entity_id
2836 AND NOTE_STATUS = 'E' -- only publish notes
2837 ORDER BY CREATION_DATE DESC;
2838
2839 cursor lc_max_lengths(pc_entity_id number) is
2840 select max(length(entered_date)), max(length(entered_by_name))
2841 from jtf_notes_vl
2842 where source_object_code = 'OPPORTUNITY'
2843 AND SOURCE_OBJECT_ID = pc_entity_id
2844 AND NOTE_STATUS = 'E';
2845
2846 cursor lc_get_label (pc_notification_id number) is
2847 select attribute_code,attribute_label_long
2848 from ak_attributes_tl ak
2849 where attribute_application_id = 522
2850 AND ATTRIBUTE_code in ('ASF_DATE','ASF_CREATED_BY','ASF_NOTE')
2851 AND ak.language= userenv('LANG');
2852
2853 l_label_date varchar2(50);
2854 l_label_name varchar2(50);
2855 l_label_note varchar2(50);
2856
2857 l_max_length_date number := 0;
2858 l_max_length_name number := 0;
2859
2860 l_entity_id number;
2861 l_notification_id number;
2862 l_notes_break_pos number;
2863 l_note_size binary_integer := 4000;
2864 l_notes_line varchar2(200);
2865 l_notes_frag varchar2(100);
2866 l_notes_varchar varchar2(4000);
2867 l_notes_list varchar2(10000);
2868 l_has_notes boolean;
2869 l_notes_end boolean;
2870 l_first_line boolean;
2871
2872 begin
2873
2874 l_entity_id := substr(document_id, 1, instr(document_id, ':')-1);
2875 l_notification_id := substr(document_id, length(l_entity_id)+2);
2876
2877 if display_type = 'text/plain' then
2878 open lc_max_lengths(pc_entity_id => l_entity_id);
2879 fetch lc_max_lengths into l_max_length_date,l_max_length_name;
2880 close lc_max_lengths;
2881 end if;
2882
2883 for l_label_rec in lc_get_label(pc_notification_id => l_notification_id) loop
2884
2885 if l_label_rec.attribute_code = 'ASF_DATE' then
2886 l_label_date := l_label_rec.attribute_label_long;
2887 l_max_length_date := greatest(l_max_length_date, length(l_label_date));
2888 elsif l_label_rec.attribute_code = 'ASF_CREATED_BY' then
2889 l_label_name := l_label_rec.attribute_label_long;
2890 l_max_length_name := greatest(l_max_length_name, length(l_label_name));
2891 elsif l_label_rec.attribute_code = 'ASF_NOTE' then
2892 l_label_note := l_label_rec.attribute_label_long;
2893 end if;
2894
2895 end loop;
2896
2897 for l_note_rec in lc_get_notes(pc_entity_id => l_entity_id) loop
2898
2899 l_has_notes := true;
2900
2901 l_notes_varchar := dbms_lob.substr(lob_loc => l_note_rec.notes_detail, amount => l_note_size, offset => 1);
2902 if l_notes_varchar is null or length(l_notes_varchar) = 0 then
2903 l_notes_varchar := l_note_rec.notes;
2904 end if;
2905
2906 if display_type = 'text/html' then
2907 l_notes_list := l_notes_list || '<tr valign="top">' ||
2908 '<td nowrap>' || l_note_rec.entered_date || '</td>' ||
2909 '<td nowrap>' || l_note_rec.entered_by_name || '</td>' ||
2910 '<td wrap>' || l_notes_varchar || '</td></tr>';
2911
2912 elsif display_type = 'text/plain' then
2913
2914 l_notes_line := rpad( nvl(to_char(l_note_rec.entered_date),' ') , l_max_length_date+2) ||
2915 rpad( nvl(l_note_rec.entered_by_name,' ') , l_max_length_name+2);
2916
2917 l_notes_end := false;
2918 l_first_line := true;
2919
2920 while not l_notes_end loop
2921
2922 l_notes_break_pos := instr(l_notes_varchar,' ',50);
2923 if l_notes_break_pos = 0 then
2924 l_notes_break_pos := 101; -- show only 1st 100 chars
2925 l_notes_end := true; -- cannot break the note or notes is less than 50
2926 end if;
2927
2928 l_notes_frag := substr(l_notes_varchar, 1, l_notes_break_pos-1);
2929
2930 if l_first_line then
2931 l_notes_list := l_notes_list || l_notes_line || l_notes_frag || fnd_global.local_chr(10);
2932 else
2933 l_notes_list := l_notes_list || lpad(l_notes_frag, length(l_notes_line)+length(l_notes_frag)) || fnd_global.local_chr(10);
2934 end if;
2935
2936 l_notes_varchar := substr(l_notes_varchar, l_notes_break_pos+1);
2937
2938 if length(l_notes_varchar) = 0 then
2939 l_notes_end := true;
2940 end if;
2941
2942 l_first_line := false;
2943 end loop;
2944
2945 end if;
2946
2947 end loop;
2948
2949 if l_has_notes and display_type = 'text/html' then
2950 l_notes_list := '<table><tr><th align="left">' || l_label_date || '</th><th align="left">' ||
2951 l_label_name || '</th><th align="left">' || l_label_note || '</th></tr>' || l_notes_list || '</table>';
2952
2953 elsif l_has_notes and display_type = 'text/plain' then
2954 l_notes_list := rpad(l_label_date, l_max_length_date+2) || rpad(l_label_name, l_max_length_name+2) ||
2955 l_label_note || fnd_global.local_chr(10) || l_notes_list;
2956 end if;
2957 document := l_notes_list;
2958
2959 end;
2960
2961 -- ----------------------------------------------------------------------------------
2962 -- get_assign_type_mean
2963 -- ----------------------------------------------------------------------------------
2964 procedure get_assign_type_mean (
2965 document_id in varchar2,
2966 display_type in varchar2,
2967 document in out nocopy varchar2,
2968 document_type in out nocopy varchar2) IS
2969
2970 cursor lc_get_assign_type_mean (pc_lead_id number) is
2971 SELECT LOWER(b.meaning) meaning
2972 FROM pv_lead_workflows a,
2973 fnd_lookup_values_vl b
2974 WHERE a.lead_id = pc_lead_id AND
2975 a.routing_type = b.lookup_code AND
2976 b.lookup_type = 'PV_ASSIGNMENT_TYPE';
2977
2978 l_entity_id number;
2979 l_notification_id number;
2980
2981 BEGIN
2982 l_entity_id := substr(document_id, 1, instr(document_id, ':')-1);
2983 l_notification_id := substr(document_id, length(l_entity_id)+2);
2984
2985 FOR x in lc_get_assign_type_mean(l_entity_id) LOOP
2986 document := x.meaning;
2987 END LOOP;
2988 END;
2989
2990 -- ----------------------------------------------------------------------------------
2991 -- get_vendor_org_name
2992 -- ----------------------------------------------------------------------------------
2993 procedure get_vendor_org_name (
2994 document_id in varchar2,
2995 display_type in varchar2,
2996 document in out nocopy varchar2,
2997 document_type in out nocopy varchar2) IS
2998
2999 CURSOR lc_get_vendor_org_name (pc_entity_id NUMBER) IS
3000 SELECT otl.name vendor_name
3001 FROM pv_lead_workflows a,
3002 fnd_user b,
3003 hr_all_organization_units o,
3004 hr_all_organization_units_tl otl,
3005 per_all_people_f p
3006 WHERE a.lead_id = pc_entity_id AND
3007 a.created_by = b.user_id AND
3008 o.organization_id = otl.organization_id AND
3009 otl.language = userenv('lang') AND
3010 o.organization_id = p.business_group_id AND
3011 b.employee_id = p.person_id and
3012 p.effective_start_date <= sysdate and
3013 p.effective_end_date >= sysdate
3014 ;
3015
3016 l_entity_id number;
3017 l_notification_id number;
3018
3019 BEGIN
3020 l_entity_id := substr(document_id, 1, instr(document_id, ':')-1);
3021 l_notification_id := substr(document_id, length(l_entity_id)+2);
3022
3023 FOR x in lc_get_vendor_org_name(pc_entity_id => l_entity_id) LOOP
3024 document := x.vendor_name;
3025 END LOOP;
3026 END;
3027
3028 procedure get_accept_user_org (
3029 document_id in varchar2,
3030 display_type in varchar2,
3031 document in out nocopy varchar2,
3032 document_type in out nocopy varchar2) is
3033 l_partner_id number;
3034 l_notification_id number;
3035
3036 cursor c1 (pc_notification number, pc_partner_id number) is
3037 select
3038 nvl(h.name, e.party_name) organization
3039 from
3040 PV_LEAD_ASSIGNMENTS C,
3041 pv_oppty_routing_logs a,
3042 pv_partner_profiles d,
3043 hz_parties e,
3044 HR_ALL_ORGANIZATION_UNITS_TL h
3045 where
3046 c.wf_item_type = 'PVASGNMT'
3047 and c.wf_item_key = (select substr(context,10,LENGTH(CONTEXT)-10)
3048 from wf_notifications where notification_id = pc_notification)
3049 and c.partner_id = pc_partner_id
3050 and c.lead_assignment_id = a.lead_assignment_id
3051 and a.user_response in ('PT_APPROVED', 'CM_APP_FOR_PT')
3052 and c.partner_id = d.partner_id
3053 and d.partner_party_id = e.party_id
3054 AND A.vendor_business_unit_id = H.ORGANIZATION_ID (+)
3055 AND H.LANGUAGE (+) = USERENV('LANG');
3056
3057 BEGIN
3058 l_partner_id := substr(document_id, 1, instr(document_id, ':')-1);
3059 l_notification_id := substr(document_id, length(l_partner_id)+2);
3060
3061 FOR x in c1 (pc_notification => l_notification_id, pc_partner_id => l_partner_id) LOOP
3062 document := x.organization;
3063 END LOOP;
3064 END;
3065
3066
3067 procedure get_accept_user_name (
3068 document_id in varchar2,
3069 display_type in varchar2,
3070 document in out nocopy varchar2,
3071 document_type in out nocopy varchar2) is
3072
3073 l_partner_id number;
3074 l_notification_id number;
3075
3076 cursor c1 (pc_notification number, pc_partner_id number) is
3077 select
3078 case
3079 when a.vendor_user_id is null then
3080 (select hzp.party_name
3081 from hz_relationships hzr, hz_parties hzp
3082 where hzr.party_id=g.source_id and hzr.subject_type='PERSON' and
3083 hzr.subject_id=hzp.party_id and hzr.object_type= 'ORGANIZATION' )
3084 else (g.source_first_name || ' ' || g.source_last_name)
3085 end person_name
3086 from
3087 PV_LEAD_ASSIGNMENTS C,
3088 pv_oppty_routing_logs a,
3089 jtf_rs_resource_extns g
3090 where
3091 c.wf_item_type = 'PVASGNMT'
3092 and c.wf_item_key = (select substr(context,10,LENGTH(CONTEXT)-10)
3093 from wf_notifications where notification_id = pc_notification)
3094 and c.partner_id = pc_partner_id
3095 and c.lead_assignment_id = a.lead_assignment_id
3096 and a.user_response in ('PT_APPROVED', 'CM_APP_FOR_PT')
3097 and nvl(a.vendor_user_id, a.pt_contact_user_id) = g.user_id (+);
3098
3099
3100 BEGIN
3101 l_partner_id := substr(document_id, 1, instr(document_id, ':')-1);
3102 l_notification_id := substr(document_id, length(l_partner_id)+2);
3103
3104 FOR x in c1 (pc_notification => l_notification_id, pc_partner_id => l_partner_id) LOOP
3105 document := x.person_name;
3106 END LOOP;
3107 END;
3108
3109
3110 End PV_WORKFLOW_PUB;