[Home] [Help]
PACKAGE BODY: APPS.PV_ASSIGNMENT_PVT
Source
1 PACKAGE BODY PV_ASSIGNMENT_PVT as
2 /* $Header: pvasgnpb.pls 120.9 2006/12/06 20:49:18 dhii noship $ */
3 -- Start of Comments
4
5 -- Package name : PV_ASSIGNMENT_PVT
6 -- Purpose :
7 -- History :
8 --
9 -- NOTE :
10 -- End of Comments
11 --
12
13
14 G_PKG_NAME CONSTANT VARCHAR2(30):= 'PV_ASSIGNMENT_PVT ';
15 G_FILE_NAME CONSTANT VARCHAR2(12) := 'pvasgnpb.pls';
16
17
18 -- ----------------------------------------------------------------------------------
19 -- ORA-00054: resource busy and acquire with NOWAIT specified
20 -- ----------------------------------------------------------------------------------
21 g_e_resource_busy EXCEPTION;
22 PRAGMA EXCEPTION_INIT(g_e_resource_busy, -54);
23
24
25 -- -----------------------------------------------------------------------------------
26 -- ======================== Private Procedure Declaration ============================
27 -- -----------------------------------------------------------------------------------
28 PROCEDURE Debug(
29 p_msg_string IN VARCHAR2
30 );
31
32
33 PROCEDURE Set_Message(
34 p_msg_level IN NUMBER,
35 p_msg_name IN VARCHAR2,
36 p_token1 IN VARCHAR2,
37 p_token1_value IN VARCHAR2,
38 p_token2 IN VARCHAR2 := NULL,
39 p_token2_value IN VARCHAR2 := NULL,
40 p_token3 IN VARCHAR2 := NULL,
41 p_token3_value IN VARCHAR2 := NULL
42 );
43
44
45
46 -- -----------------------------------------------------------------------------------
47 -- ============================= Procedure Body ======================================
48 -- -----------------------------------------------------------------------------------
49
50 PROCEDURE Create_Oppty_Routing_Log_Row
51 (
52 P_Api_Version_Number IN NUMBER,
53 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
54 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
55 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
56 P_oppty_routing_log_rec IN oppty_routing_log_rec_type,
57 X_Return_Status OUT NOCOPY VARCHAR2,
58 X_Msg_Count OUT NOCOPY NUMBER,
59 X_Msg_Data OUT NOCOPY VARCHAR2
60 )
61 IS
62 l_api_name CONSTANT VARCHAR2(30) := 'Create_Oppty_Routing_Log_Row';
63 l_api_version_number CONSTANT NUMBER := 1.0;
64
65 CURSOR C2 IS SELECT PV_OPPTY_ROUTING_LOGS_S.nextval FROM sys.dual;
66
67 CURSOR get_org_id ( pc_user_id NUMBER)
68 IS
69 SELECT business_group_id
70 FROM per_all_people_f a
71 , fnd_user b
72 WHERE b.user_id = pc_user_id
73 AND b.employee_id = a.person_id;
74
75 l_routing_log_id NUMBER;
76 l_business_unit_id NUMBER;
77
78
79
80 BEGIN
81 -- Standard call to check for call compatibility.
82 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
83 p_api_version_number,
84 l_api_name,
85 G_PKG_NAME)
86 THEN
87 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
88 END IF;
89
90 -- Initialize message list if p_init_msg_list is set to TRUE.
91 IF FND_API.to_Boolean( p_init_msg_list )
92 THEN
93 FND_MSG_PUB.initialize;
94 END IF;
95
96 -- Debug Message
97 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
98 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
99 fnd_message.Set_Token('TEXT', 'In ' || l_api_name);
100 fnd_msg_pub.Add;
101 END IF;
102
103 -- Initialize API return status to SUCCESS
104 x_return_status := FND_API.G_RET_STS_SUCCESS;
105
106 OPEN C2;
107 FETCH C2 INTO l_routing_log_id;
108 CLOSE C2;
109
110 IF P_oppty_routing_log_rec.vendor_user_id IS NOT NULL THEN
111
112 OPEN get_org_id(P_oppty_routing_log_rec.vendor_user_id);
113 FETCH get_org_id INTO l_business_unit_id;
114 CLOSE get_org_id;
115
116 END IF;
117
118 INSERT INTO pv_oppty_routing_logs
119 (
120 OPPTY_ROUTING_LOG_ID
121 , EVENT
122 , LEAD_ID
123 , LEAD_WORKFLOW_ID
124 , ROUTING_TYPE
125 , LATEST_ROUTING_FLAG
126 , BYPASS_CM_FLAG
127 , LEAD_ASSIGNMENT_ID
128 , EVENT_DATE
129 , VENDOR_USER_ID
130 , PT_CONTACT_USER_ID
131 , USER_RESPONSE
132 , REASON_CODE
133 , USER_TYPE
134 , VENDOR_BUSINESS_UNIT_ID
135 )
136 VALUES
137 (
138 l_routing_log_id
139 , P_oppty_routing_log_rec.event
140 , P_oppty_routing_log_rec.lead_id
141 , P_oppty_routing_log_rec.lead_workflow_id
142 , P_oppty_routing_log_rec.routing_type
143 , P_oppty_routing_log_rec.latest_routing_flag
144 , P_oppty_routing_log_rec.bypass_cm_flag
145 , P_oppty_routing_log_rec.lead_assignment_id
146 , P_oppty_routing_log_rec.event_date
147 , P_oppty_routing_log_rec.vendor_user_id
148 , P_oppty_routing_log_rec.pt_contact_user_id
149 , P_oppty_routing_log_rec.user_response
150 , P_oppty_routing_log_rec.reason_code
151 , P_oppty_routing_log_rec.user_type
152 , l_business_unit_id
153 );
154 -- End of API body
155 --
156
157 -- Standard check for p_commit
158 IF FND_API.to_Boolean( p_commit )
159 THEN
160 COMMIT WORK;
161 END IF;
162
163 -- Standard call to get message count and if count is 1, get message info.
164 FND_MSG_PUB.Count_And_Get
165 ( p_count => x_msg_count,
166 p_data => x_msg_data
167 );
168
169 EXCEPTION
170
171 WHEN FND_API.G_EXC_ERROR THEN
172
173 x_return_status := FND_API.G_RET_STS_ERROR ;
174 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_TRUE,
175 p_count => x_msg_count,
176 p_data => x_msg_data);
177
178 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
179
180 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
181 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_TRUE,
182 p_count => x_msg_count,
183 p_data => x_msg_data);
184
185 WHEN OTHERS THEN
186
187 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
188 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
189 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_FALSE,
190 p_count => x_msg_count,
191 p_data => x_msg_data);
192
193 END Create_Oppty_Routing_Log_Row;
194
195 PROCEDURE Create_assignment_log_row(
196 P_Api_Version_Number IN NUMBER,
197 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
198 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
199 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
200 P_assignment_log_rec IN assignment_log_rec_type,
201 X_assignment_id OUT NOCOPY NUMBER,
202 X_Return_Status OUT NOCOPY VARCHAR2,
203 X_Msg_Count OUT NOCOPY NUMBER,
204 X_Msg_Data OUT NOCOPY VARCHAR2
205 )
206 IS
207 l_api_name CONSTANT VARCHAR2(30) := 'Create_assignment_log_row';
208 l_api_version_number CONSTANT NUMBER := 1.0;
209
210 CURSOR C2 IS SELECT PV_ASSIGNMENT_LOGS_S.nextval FROM sys.dual;
211 l_assignment_log_id number;
212
213 BEGIN
214
215 -- Standard call to check for call compatibility.
216 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
217 p_api_version_number,
218 l_api_name,
219 G_PKG_NAME)
220 THEN
221 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
222 END IF;
223
224 -- Initialize message list if p_init_msg_list is set to TRUE.
225 IF FND_API.to_Boolean( p_init_msg_list )
226 THEN
227 FND_MSG_PUB.initialize;
228 END IF;
229
230 -- Debug Message
231 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
232 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
233 fnd_message.Set_Token('TEXT', 'In ' || l_api_name);
234 fnd_msg_pub.Add;
235 END IF;
236
237 -- Initialize API return status to SUCCESS
238 x_return_status := FND_API.G_RET_STS_SUCCESS;
239
240 --
241 -- API body
242 --
243
244 OPEN C2;
245 FETCH C2 INTO l_assignment_log_id;
246 CLOSE C2;
247
248 INSERT into pv_assignment_logs (
249 ASSIGNMENT_ID,
250 LAST_UPDATE_DATE,
251 LAST_UPDATED_BY,
252 CREATION_DATE,
253 CREATED_BY,
254 LAST_UPDATE_LOGIN,
255 OBJECT_VERSION_NUMBER,
256 LEAD_ASSIGNMENT_ID,
257 PARTNER_ID,
258 ASSIGN_SEQUENCE,
259 CM_ID,
260 LEAD_ID,
261 DURATION,
262 FROM_LEAD_STATUS,
263 TO_LEAD_STATUS,
264 STATUS,
265 STATUS_DATE,
266 WF_ITEM_TYPE,
267 WF_ITEM_KEY,
268 WF_PT_USER,
269 WF_CM_USER,
270 WORKFLOW_ID,
271 ERROR_TXT,
272 TRANS_TYPE,
273 STATUS_CHANGE_COMMENTS
274 ) values (
275 l_assignment_log_id,
276 sysdate,
277 fnd_global.user_id,
278 sysdate,
279 fnd_global.user_id,
280 fnd_global.conc_login_id,
281 1,
282 p_assignment_log_rec.LEAD_ASSIGNMENT_ID,
283 p_assignment_log_rec.PARTNER_ID,
284 p_assignment_log_rec.ASSIGN_SEQUENCE,
285 p_assignment_log_rec.CM_ID,
286 p_assignment_log_rec.LEAD_ID,
287 p_assignment_log_rec.DURATION,
288 p_assignment_log_rec.FROM_LEAD_STATUS,
289 p_assignment_log_rec.TO_LEAD_STATUS,
290 p_assignment_log_rec.STATUS,
291 p_assignment_log_rec.STATUS_DATE,
292 p_assignment_log_rec.WF_ITEM_TYPE,
293 p_assignment_log_rec.WF_ITEM_KEY,
294 p_assignment_log_rec.WF_PT_USER,
295 p_assignment_log_rec.WF_CM_USER,
296 p_assignment_log_rec.WORKFLOW_ID,
297 p_assignment_log_rec.ERROR_TXT,
298 p_assignment_log_rec.TRANS_TYPE,
299 p_assignment_log_rec.STATUS_CHANGE_COMMENTS
300 );
301
302 x_assignment_id := l_assignment_log_id;
303
304 --
305 -- End of API body
306 --
307
308 -- Standard check for p_commit
309 IF FND_API.to_Boolean( p_commit )
310 THEN
311 COMMIT WORK;
312 END IF;
313
314 -- Standard call to get message count and if count is 1, get message info.
315 FND_MSG_PUB.Count_And_Get
316 ( p_count => x_msg_count,
317 p_data => x_msg_data
318 );
319
320 EXCEPTION
321
322 WHEN FND_API.G_EXC_ERROR THEN
323
324 x_return_status := FND_API.G_RET_STS_ERROR ;
325 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_TRUE,
326 p_count => x_msg_count,
327 p_data => x_msg_data);
328
329 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
330
331 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
332 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_TRUE,
333 p_count => x_msg_count,
334 p_data => x_msg_data);
335
336 WHEN OTHERS THEN
337
338 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
339 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
340 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_FALSE,
341 p_count => x_msg_count,
342 p_data => x_msg_data);
343
344 End Create_assignment_log_row;
345
346
347
348 PROCEDURE update_party_response(
349 P_Api_Version_Number IN NUMBER,
350 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
351 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
352 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
353 P_rowid IN ROWID,
354 p_lead_assignment_id IN NUMBER,
355 p_party_resource_id IN NUMBER,
356 p_response IN VARCHAR2,
357 p_reason_code IN VARCHAR2,
358 p_rank IN NUMBER,
359 X_Return_Status OUT NOCOPY VARCHAR2,
360 X_Msg_Count OUT NOCOPY NUMBER,
361 X_Msg_Data OUT NOCOPY VARCHAR2
362 )
363
364 IS
365 l_api_name CONSTANT VARCHAR2(30) := 'update_party_response';
366 l_api_version_number CONSTANT NUMBER := 1.0;
367
368 l_lead_assignment_id number;
369 l_party_resource_id number;
370 l_response varchar2(30);
371
372 begin
373 -- Standard call to check for call compatibility.
374
375 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
376 p_api_version_number,
377 l_api_name,
378 G_PKG_NAME) THEN
379 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
380
381 END IF;
382
383 -- Initialize message list if p_init_msg_list is set to TRUE.
384 IF FND_API.to_Boolean( p_init_msg_list )
385 THEN
386 fnd_msg_pub.initialize;
387 END IF;
388
389 -- Debug Message
390 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
391 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
392 fnd_message.Set_Token('TEXT', 'In ' || l_api_name);
393 fnd_msg_pub.Add;
394 END IF;
395
396 x_return_status := FND_API.G_RET_STS_SUCCESS ;
397
398
399 update pv_party_notifications
400 set resource_response = p_response,
401 response_date = sysdate,
402 object_version_number = object_version_number + 1,
403 last_update_date = sysdate,
404 last_updated_by = FND_GLOBAL.user_id,
405 last_update_login = FND_GLOBAL.login_id
406 where rowid = p_rowid
407 returning lead_assignment_id, resource_id
408 into l_lead_assignment_id, l_party_resource_id;
409
410 IF (SQL%NOTFOUND) THEN
411 fnd_message.SET_NAME('PV', 'Cannot find row to update');
412 fnd_msg_pub.ADD;
413
414 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
415 END IF;
416
417 if (l_lead_assignment_id <> p_lead_assignment_id or
418 l_party_resource_id <> p_party_resource_id )
419 then
420 fnd_message.SET_NAME('PV', 'Updated wrong row');
421 fnd_msg_pub.ADD;
422
423 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
424 end if;
425
426 if p_response in (pv_assignment_pub.g_la_status_cm_added, pv_assignment_pub.g_la_status_cm_add_app_for_pt) then
427 l_response := pv_assignment_pub.g_la_status_cm_approved;
428 else
429 l_response := p_response;
430 end if;
431
432 UpdateAssignment (
433 p_api_version_number => 1.0
434 ,p_init_msg_list => FND_API.G_FALSE
435 ,p_commit => FND_API.G_FALSE
436 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
437 ,p_action => pv_assignment_pub.g_asgn_action_status_update
438 ,p_lead_assignment_id => p_lead_assignment_id
439 ,p_status_date => sysdate
440 ,p_status => l_response
441 ,p_reason_code => p_reason_code
442 ,p_rank => p_rank
443 ,x_msg_count => x_msg_count
444 ,x_msg_data => x_msg_data
445 ,x_return_status => x_return_status);
446
447 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
448 raise FND_API.G_EXC_ERROR;
449 end if;
450
451 IF FND_API.To_Boolean ( p_commit ) THEN
452 COMMIT WORK;
453 END IF;
454
455 -- Standard call to get message count and if count is 1, get message info.
456 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_TRUE,
457 p_count => x_msg_count,
458 p_data => x_msg_data);
459 EXCEPTION
460
461 WHEN FND_API.G_EXC_ERROR THEN
462
463 x_return_status := FND_API.G_RET_STS_ERROR ;
464 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_TRUE,
465 p_count => x_msg_count,
466 p_data => x_msg_data);
467
468 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
469
470 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
471 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_TRUE,
472 p_count => x_msg_count,
473 p_data => x_msg_data);
474
475 WHEN OTHERS THEN
476
477 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
478 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
479 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_FALSE,
480 p_count => x_msg_count,
481 p_data => x_msg_data);
482
483 End update_party_response;
484
485
486 PROCEDURE bulk_set_party_notify_id(
487 P_Api_Version_Number IN NUMBER,
488 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
489 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
490 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
491 p_itemtype IN VARCHAR2,
492 p_itemkey IN VARCHAR2,
493 p_notify_type IN VARCHAR2,
494 X_Return_Status OUT NOCOPY VARCHAR2,
495 X_Msg_Count OUT NOCOPY NUMBER,
496 X_Msg_Data OUT NOCOPY VARCHAR2
497 )
498
499 IS
500 l_api_name CONSTANT VARCHAR2(30) := 'bulk_set_party_notify_id';
501 l_api_version_number CONSTANT NUMBER := 1.0;
502
503 l_partner_id number;
504 l_size number;
505 l_notify_id_tbl pv_assignment_pub.g_number_table_type;
506 l_party_notify_id_tbl pv_assignment_pub.g_number_table_type;
507
508 cursor lc_get_notified (pc_itemtype varchar2,
509 pc_itemkey varchar2,
510 pc_partner_id number,
511 pc_notify_type varchar2) is
512 select c.notification_id,
513 b.party_notification_id
514 from pv_party_notifications b,
515 wf_item_activity_statuses d,
516 wf_notifications c,
517 fnd_user usr
518 where b.wf_item_type = pc_itemtype
519 and b.wf_item_key = pc_itemkey
520 and b.notification_type = pc_notify_type
521 and d.item_type = b.wf_item_type
522 and d.item_key = b.wf_item_key
523 and d.assigned_user = 'PV' || pc_notify_type || pc_itemkey || '+' || pc_partner_id
524 and b.user_id = usr.user_id
525 and usr.user_name = c.original_recipient
526 and c.context = pc_itemtype || ':' || pc_itemkey || ':' || d.process_activity;
527
528 begin
529 -- Standard call to check for call compatibility.
530
531 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
532 p_api_version_number,
533 l_api_name,
534 G_PKG_NAME) THEN
535 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
536
537 END IF;
538
539 -- Initialize message list if p_init_msg_list is set to TRUE.
540 IF FND_API.to_Boolean( p_init_msg_list )
541 THEN
542 fnd_msg_pub.initialize;
543 END IF;
544
545 -- Debug Message
546 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
547 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
548 fnd_message.Set_Token('TEXT', 'In ' || l_api_name);
549 fnd_msg_pub.Add;
550 END IF;
551
552 x_return_status := FND_API.G_RET_STS_SUCCESS ;
553
554 l_partner_id := nvl(wf_engine.GetItemAttrNumber(
555 itemtype => p_itemtype,
556 itemkey => p_itemkey,
557 aname => pv_workflow_pub.g_wf_attr_partner_id), 0);
558
559 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
560 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
561 fnd_message.Set_Token('TEXT', 'Get notify id for partner_id: ' || l_partner_id);
562 fnd_msg_pub.Add;
563 END IF;
564
565 open lc_get_notified (pc_itemtype => p_itemtype,
566 pc_itemkey => p_itemkey,
567 pc_partner_id => l_partner_id,
568 pc_notify_type => p_notify_type);
569 l_size := 0;
570 l_notify_id_tbl := pv_assignment_pub.g_number_table_type();
571 l_party_notify_id_tbl := pv_assignment_pub.g_number_table_type();
572
573 loop
574
575 l_notify_id_tbl.extend;
576 l_party_notify_id_tbl.extend;
577 l_size := l_size + 1;
578
579 fetch lc_get_notified into l_notify_id_tbl(l_size), l_party_notify_id_tbl(l_size);
580 exit when lc_get_notified%notfound;
581
582 end loop;
583 close lc_get_notified;
584 l_notify_id_tbl.trim;
585 l_party_notify_id_tbl.trim;
586
587 if l_party_notify_id_tbl.count > 0 then
588
589 forall j in 1 .. l_party_notify_id_tbl.count
590 update pv_party_notifications
591 set notification_id = l_notify_id_tbl(j),
592 object_version_number = object_version_number + 1,
593 last_update_date = sysdate,
594 last_updated_by = FND_GLOBAL.user_id,
595 last_update_login = FND_GLOBAL.login_id
596 where party_notification_id = l_party_notify_id_tbl(j);
597
598 end if;
599
600 IF FND_API.To_Boolean ( p_commit ) THEN
601 COMMIT WORK;
602 END IF;
603
604 -- Standard call to get message count and if count is 1, get message info.
605 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_TRUE,
606 p_count => x_msg_count,
607 p_data => x_msg_data);
608 EXCEPTION
609
610 WHEN FND_API.G_EXC_ERROR THEN
611
612 x_return_status := FND_API.G_RET_STS_ERROR ;
613 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_TRUE,
614 p_count => x_msg_count,
615 p_data => x_msg_data);
616
617 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
618
619 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
620 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_TRUE,
621 p_count => x_msg_count,
622 p_data => x_msg_data);
623
624 WHEN OTHERS THEN
625
626 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
627 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
628 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_FALSE,
629 p_count => x_msg_count,
630 p_data => x_msg_data);
631
632 End bulk_set_party_notify_id;
633
634
635 procedure UpdateAssignment (
636 p_api_version_number IN NUMBER
637 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
638 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
639 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
640 ,p_action IN VARCHAR2
641 ,p_lead_assignment_id IN number
642 ,p_status_date IN DATE
643 ,p_status IN VARCHAR2
644 ,p_reason_code IN VARCHAR2
645 ,p_rank IN NUMBER
646 ,x_msg_count OUT NOCOPY NUMBER
647 ,x_msg_data OUT NOCOPY VARCHAR2
648 ,x_return_status OUT NOCOPY VARCHAR2) is
649
650 l_api_name CONSTANT VARCHAR2(30) := 'UpdateAssignment';
651 l_api_version_number CONSTANT NUMBER := 1.0;
652
653 l_rowid rowid;
654 l_assignment_log_id number;
655 l_assignment_rec pv_assign_util_pvt.ASSIGNMENT_REC_TYPE;
656
657 l_object_version_number NUMBER;
658 l_partner_id NUMBER;
659 l_lead_id NUMBER;
660 l_assign_sequence NUMBER;
661 l_status_date DATE;
662 l_status VARCHAR2(40);
663 l_reason_code VARCHAR2(30);
664 l_routing_status VARCHAR2(30);
665 l_wf_item_type VARCHAR2(40);
666 l_wf_item_key VARCHAR2(40);
667 l_lead_workflow_id NUMBER;
668 l_routing_type VARCHAR2(40);
669 l_latest_routing_flag VARCHAR2(10);
670 l_bypass_cm_flag VARCHAR2(10);
671 l_user_category VARCHAR2(40);
672 l_notification_type VARCHAR2(40);
673
674 l_partner_access_code varchar2(30);
675 l_related_party_access_code varchar2(30);
676 l_org_to_vend_party_id NUMBER := NULL;
677 l_oppty_routing_log_rec oppty_routing_log_rec_type;
678
679
680 CURSOR lc_get_assign_row (pc_lead_assignment_id number) IS
681 SELECT
682 a.rowid,
683 a.object_version_number,
684 a.partner_id,
685 a.assign_sequence,
686 a.lead_id,
687 a.status,
688 a.reason_code ,
689 a.status_date,
690 a.wf_item_type,
691 a.wf_item_key,
692 a.partner_access_code,
693 a.related_party_access_code,
694 b.routing_status,
695 b.lead_workflow_id,
696 b.routing_type,
697 b.latest_routing_flag,
698 b.bypass_cm_ok_flag
699 FROM pv_lead_assignments a, pv_lead_workflows b
700 WHERE a.lead_assignment_id = pc_lead_assignment_id
701 AND a.wf_item_type = b.wf_item_type
702 AND a.wf_item_key = b.wf_item_key;
703
704 CURSOR lc_get_notify_type ( pc_wf_item_type VARCHAR2
705 , pc_wf_item_key VARCHAR2 )
706 IS
707 SELECT notification_type
708 FROM pv_party_notifications a
709 WHERE a.wf_item_key = pc_wf_item_key
710 AND a.wf_item_type = pc_wf_item_type
711 AND a.user_id = fnd_global.user_id
712 AND a.notification_type= pv_assignment_pub.g_notify_type_matched_to;
713
714 /*SELECT notification_type
715 FROM pv_party_notifications a, pv_assignment_logs c
716 WHERE a.user_id = c.created_by
717 AND a.wf_item_key = c.wf_item_key
718 AND a.wf_item_key = pc_wf_item_key
719 AND a.wf_item_type = pc_wf_item_type;
720 */
721
722 CURSOR lc_get_vad_assign (pc_lead_assignment_id number) IS
723 select pv_assign.lead_assignment_id
724 from hz_relationships EMP_TO_ORG,
725 hz_relationships ORG_TO_VEND,
726 hz_organization_profiles HZOP,
727 pv_lead_assignments PV_ASSIGN,
728 pv_lead_workflows PV_LEAD_WF,
729 jtf_rs_resource_extns LEAD_SOURCE,
730 pv_enty_attr_values PEAV
731 where PV_ASSIGN.lead_assignment_id = pc_lead_assignment_id
732 and PV_ASSIGN.wf_item_type = PV_LEAD_WF.wf_item_type
733 and PV_ASSIGN.wf_item_key = PV_LEAD_WF.wf_item_key
734 and PV_LEAD_WF.created_by = LEAD_SOURCE.user_id
735 and EMP_TO_ORG.party_id = LEAD_SOURCE.source_id
736 and EMP_TO_ORG.subject_table_name = 'HZ_PARTIES'
737 and EMP_TO_ORG.object_table_name = 'HZ_PARTIES'
738 and EMP_TO_ORG.directional_flag = 'F'
739 and EMP_TO_ORG.status in ('A', 'I')
740 and EMP_TO_ORG.relationship_code = 'EMPLOYEE_OF'
741 and EMP_TO_ORG.relationship_type = 'EMPLOYMENT'
742 and EMP_TO_ORG.object_id = ORG_TO_VEND.subject_id
743 and ORG_TO_VEND.subject_table_name = 'HZ_PARTIES'
744 and ORG_TO_VEND.object_table_name = 'HZ_PARTIES'
745 and ORG_TO_VEND.status in ('A', 'I')
746 and ORG_TO_VEND.relationship_type = 'PARTNER'
747 and ORG_TO_VEND.object_id = HZOP.party_id
748 and HZOP.internal_flag = 'Y'
749 and HZOP.effective_end_date is null
750 and ORG_TO_VEND.party_id = PV_ASSIGN.related_party_id
751 and PEAV.entity_id(+) = ORG_TO_VEND.party_id
752 and PEAV.entity(+) = 'PARTNER'
753 and PEAV.attribute_id(+) = 3
754 and PEAV.attr_value = 'VAD';
755
756 CURSOR lc_get_user_type (pc_user_id NUMBER) IS
757 SELECT extn.category
758 FROM fnd_user fuser,
759 jtf_rs_resource_extns extn
760 WHERE fuser.user_id = pc_user_id
761 AND fuser.user_id = extn.user_id;
762
763 begin
764 -- Standard call to check for call compatibility.
765
766 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
767 p_api_version_number,
768 l_api_name,
769 G_PKG_NAME) THEN
770 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
771
772 END IF;
773
774 -- Initialize message list if p_init_msg_list is set to TRUE.
775 IF FND_API.to_Boolean( p_init_msg_list )
776 THEN
777 fnd_msg_pub.initialize;
778 END IF;
779
780 -- Debug Message
781 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
782
783 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
784 fnd_message.Set_Token('TEXT', 'In ' || l_api_name || '. ID: ' || p_lead_assignment_id || ' Action: ' || p_action);
785 fnd_msg_pub.Add;
786
787 if p_action = pv_assignment_pub.g_asgn_action_status_update then
788
789 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
790 fnd_message.Set_Name('PV', 'Status: ' || p_status);
791 fnd_msg_pub.Add;
792
793 end if;
794
795 END IF;
796
797 x_return_status := FND_API.G_RET_STS_SUCCESS ;
798
799 -- validate p_action modes
800
801 if p_action is NULL or
802 p_action not in (pv_assignment_pub.g_asgn_action_status_update,
803 pv_assignment_pub.g_asgn_action_move_to_log) then
804
805 fnd_message.SET_NAME('PV', 'PV_DEBUG_MESSAGE');
806 fnd_message.SET_TOKEN('TEXT', 'Invalid action mode:' || nvl(p_action, 'NULL') );
807 fnd_msg_pub.ADD;
808
809 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
810
811 end if;
812
813 OPEN lc_get_assign_row(pc_lead_assignment_id => p_lead_assignment_id);
814
815 FETCH lc_get_assign_row INTO
816 l_rowid,
817 l_object_version_number,
818 l_partner_id,
819 l_assign_sequence,
820 l_lead_id,
821 l_status,
822 l_reason_code ,
823 l_status_date,
824 l_wf_item_type,
825 l_wf_item_key,
826 l_partner_access_code,
827 l_related_party_access_code,
828 l_routing_status,
829 l_lead_workflow_id,
830 l_routing_type,
831 l_latest_routing_flag,
832 l_bypass_cm_flag;
833 CLOSE lc_get_assign_row;
834
835 IF (l_rowid is NULL) THEN
836 fnd_message.SET_NAME('PV', 'PV_DEBUG_MESSAGE');
837 fnd_message.SET_TOKEN('TEXT', 'Cannot find row');
838 fnd_msg_pub.ADD;
839
840 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
841 END IF;
842
843 if p_action = pv_assignment_pub.g_asgn_action_status_update then
844
845 if l_routing_status in (pv_assignment_pub.g_r_status_active, pv_assignment_pub.g_r_status_offered) then
846
847 -- we are not doing this for matched status here because partners/related_party_id access do not
848 -- change until all CMs have approved/rejected or timedout
849 -- that's why we are doing it in set_offered_attributes API
850
851 if p_status in ( pv_assignment_pub.g_la_status_pt_rejected,
852 pv_assignment_pub.g_la_status_pt_timeout,
853 pv_assignment_pub.g_la_status_pt_abandoned,
854 pv_assignment_pub.g_la_status_offer_withdrawn,
855 pv_assignment_pub.g_la_status_lost_chance) then
856
857 OPEN lc_get_vad_assign(pc_lead_assignment_id => p_lead_assignment_id);
858 FETCH lc_get_vad_assign into l_org_to_vend_party_id;
859 CLOSE lc_get_vad_assign;
860
861 IF l_org_to_vend_party_id is NULL THEN
862 l_related_party_access_code := pv_assignment_pub.g_assign_access_none;
863 END IF;
864
865 l_partner_access_code := pv_assignment_pub.g_assign_access_none;
866
867 elsif p_status in ( pv_assignment_pub.g_la_status_pt_approved,
868 pv_assignment_pub.g_la_status_cm_app_for_pt) then
869
870 -- note: status will never be cm_add_app_for_pt because we change it to cm_app_for_pt
871 -- and that status is only done during matched mode
872
873 l_partner_access_code := pv_assignment_pub.g_assign_access_update;
874 l_related_party_access_code := pv_assignment_pub.g_assign_access_update;
875
876 end if;
877
878 end if;
879
880 update pv_lead_assignments
881 set status_date = p_status_date,
882 status = p_status,
883 reason_code = p_reason_code ,
884 assign_sequence = nvl(p_rank, assign_sequence),
885 partner_access_code = l_partner_access_code,
886 related_party_access_code = decode(nvl(related_party_id,-999), -999, null, l_related_party_access_code),
887 object_version_number = object_version_number + 1,
888 last_update_date = sysdate,
889 last_updated_by = FND_GLOBAL.user_id,
890 last_update_login = FND_GLOBAL.login_id
891 where rowid = l_rowid;
892
893 IF (SQL%NOTFOUND) THEN
894 fnd_message.SET_NAME('PV', 'PV_DEBUG_MESSAGE');
895 fnd_message.SET_TOKEN('TEXT', 'Cannot find row to update');
896 fnd_msg_pub.ADD;
897
898 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
899 END IF;
900
901 l_assignment_rec.lead_id := l_lead_id;
902 l_assignment_rec.partner_id := l_partner_id;
903 l_assignment_rec.status := p_status;
904
905 pv_assign_util_pvt.Log_assignment_status (
906 p_api_version_number => 1.0,
907 p_init_msg_list => FND_API.G_FALSE,
908 p_commit => FND_API.G_FALSE,
909 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
910 p_assignment_rec => l_assignment_rec,
911 x_return_status => x_return_status,
912 x_msg_count => x_msg_count,
913 x_msg_data => x_msg_data);
914
915 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
916 raise FND_API.G_EXC_ERROR;
917 end if;
918 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
919 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
920 fnd_message.Set_Token('TEXT', 'START:Logging in Opportunity Routing Log ');
921 fnd_msg_pub.Add;
922 END IF;
923
924 -- vansub
925 -- Start :Rivendell Update
926 -- Logging Routing Changes
927 IF p_status <> pv_assignment_pub.g_la_status_cm_bypassed THEN
928
929 IF p_status = pv_assignment_pub.g_la_status_cm_rejected THEN
930 l_oppty_routing_log_rec.event := 'ASSIGN_REJECT';
931 ELSIF p_status IN ( pv_assignment_pub.g_la_status_cm_approved
932 , pv_assignment_pub.g_la_status_cm_app_for_pt
933 , pv_assignment_pub.g_la_status_cm_timeout
934 )
935 THEN
936 l_oppty_routing_log_rec.event := 'ASSIGN_ACCEPT';
937 ELSIF p_status = pv_assignment_pub.g_la_status_pt_rejected THEN
938 l_oppty_routing_log_rec.event := 'OPPTY_DECLINE';
939 ELSIF p_status = pv_assignment_pub.g_la_status_pt_timeout THEN
940 l_oppty_routing_log_rec.event := 'OPPTY_RECYCLE';
941 ELSIF p_status = pv_assignment_pub.g_la_status_pt_approved THEN
942 l_oppty_routing_log_rec.event := 'OPPTY_ACCEPT';
943 ELSIF p_status = pv_assignment_pub.g_la_status_pt_abandoned THEN
944 l_oppty_routing_log_rec.event := 'OPPTY_ABANDON';
945 ELSIF p_status IN ( pv_assignment_pub.g_la_status_offer_withdrawn
946 , pv_assignment_pub.g_la_status_match_withdrawn )
947 THEN
948 l_oppty_routing_log_rec.event := 'ASSIGN_WITHDRAW';
949 ELSIF p_status = pv_assignment_pub.g_la_status_active_withdrawn THEN
950 l_oppty_routing_log_rec.event := 'OPPTY_WITHDRAW';
951 ELSIF p_status = pv_assignment_pub.g_la_status_lost_chance THEN
952 l_oppty_routing_log_rec.event := 'OPPTY_TAKEN';
953 END IF;
954 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
955 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
956 fnd_message.Set_Token('TEXT', 'Status : '||p_status||' Event : '||l_oppty_routing_log_rec.event);
957 fnd_msg_pub.Add;
958 END IF;
959 l_oppty_routing_log_rec.lead_id := l_lead_id;
960 l_oppty_routing_log_rec.lead_workflow_id := l_lead_workflow_id;
961 l_oppty_routing_log_rec.routing_type := l_routing_type;
962 l_oppty_routing_log_rec.latest_routing_flag := l_latest_routing_flag;
963 l_oppty_routing_log_rec.bypass_cm_flag := l_bypass_cm_flag;
964 l_oppty_routing_log_rec.lead_assignment_id := p_lead_assignment_id;
965 l_oppty_routing_log_rec.event_date := p_status_date;
966 l_oppty_routing_log_rec.user_response := p_status;
967
968 -- Setting Vendor and Partner User ID
969 OPEN lc_get_user_type (FND_GLOBAL.user_id);
970 FETCH lc_get_user_type INTO l_user_category;
971 CLOSE lc_get_user_type;
972
973 IF l_user_category = PV_ASSIGNMENT_PUB.g_resource_employee THEN
974 l_oppty_routing_log_rec.vendor_user_id := FND_GLOBAL.user_id;
975 l_oppty_routing_log_rec.pt_contact_user_id := NULL;
976 ELSIF l_user_category = PV_ASSIGNMENT_PUB.g_resource_party THEN
977 l_oppty_routing_log_rec.vendor_user_id := NULL;
978 l_oppty_routing_log_rec.pt_contact_user_id := FND_GLOBAL.user_id;
979 END IF;
980 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
981 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
982 fnd_message.Set_Token('TEXT', 'Vendor User ID '||l_oppty_routing_log_rec.vendor_user_id);
983 fnd_msg_pub.Add;
984 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
985 fnd_message.Set_Token('TEXT', 'Partner User ID '||l_oppty_routing_log_rec.pt_contact_user_id);
986 fnd_msg_pub.Add;
987 END IF;-- Setting Vendor and Partner User ID to SYSTEM and user type also to SYSTEM
988 IF p_status IN ( pv_assignment_pub.g_la_status_cm_timeout
989 , pv_assignment_pub.g_la_status_pt_timeout
990 , pv_assignment_pub.g_la_status_lost_chance
991 )
992 THEN
993 l_oppty_routing_log_rec.vendor_user_id := NULL;
994 l_oppty_routing_log_rec.pt_contact_user_id := NULL;
995 l_oppty_routing_log_rec.user_type := 'SYSTEM';
996 ELSIF p_status IN ( pv_assignment_pub.g_la_status_cm_approved
997 , pv_assignment_pub.g_la_status_cm_app_for_pt
998 , pv_assignment_pub.g_la_status_cm_rejected
999 )
1000 THEN
1001 l_oppty_routing_log_rec.user_type := 'CM';
1002 ELSIF p_status IN ( pv_assignment_pub.g_la_status_pt_approved
1003 , pv_assignment_pub.g_la_status_pt_abandoned
1004 , pv_assignment_pub.g_la_status_pt_rejected)
1005 THEN
1006 l_oppty_routing_log_rec.user_type := 'PT';
1007 ELSIF p_status IN ( pv_assignment_pub.g_la_status_offer_withdrawn
1008 , pv_assignment_pub.g_la_status_match_withdrawn
1009 , pv_assignment_pub.g_la_status_active_withdrawn)
1010 THEN
1011
1012 -- When Opportunity is withdrawn by SalesRep the record does not
1013 -- make into pv_party_notifications. Only CM and PT will be in Party Notifications
1014 -- Hence retrieving the Salesrep withdraw from pv_assignment_logs
1015
1016 OPEN lc_get_notify_type(l_wf_item_type, l_wf_item_key);
1017 FETCH lc_get_notify_type INTO l_notification_type;
1018 CLOSE lc_get_notify_type;
1019
1020 IF l_notification_type IS NULL THEN
1021 l_oppty_routing_log_rec.user_type := 'SR';
1022 ELSE
1023 l_oppty_routing_log_rec.user_type := 'CM';
1024 END IF;
1025 END IF;
1026
1027 IF p_status IN ( pv_assignment_pub.g_la_status_pt_rejected
1028 , pv_assignment_pub.g_la_status_pt_abandoned )
1029 THEN
1030 l_oppty_routing_log_rec.reason_code := p_reason_code;
1031 ELSE
1032 l_oppty_routing_log_rec.reason_code := NULL;
1033 END IF;
1034
1035
1036 pv_assignment_pvt.Create_Oppty_Routing_Log_Row (
1037 p_api_version_number => 1.0,
1038 p_init_msg_list => FND_API.G_FALSE,
1039 p_commit => FND_API.G_FALSE,
1040 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1041 P_oppty_routing_log_rec => l_oppty_routing_log_rec,
1042 x_return_status => x_return_status,
1043 x_msg_count => x_msg_count,
1044 x_msg_data => x_msg_data);
1045
1046 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1047 RAISE FND_API.G_EXC_ERROR;
1048 END IF;
1049 END IF;
1050
1051 -- vansub
1052 -- End :Rivendell Update
1053 -- Logging Routing Changes elsif p_action = pv_assignment_pub.g_asgn_action_move_to_log then
1054 elsif p_action = pv_assignment_pub.g_asgn_action_move_to_log then
1055
1056 delete from pv_lead_assignments where rowid = l_rowid;
1057
1058 IF (SQL%ROWCOUNT = 0) THEN
1059 fnd_message.SET_NAME('PV', 'PV_DEBUG_MESSAGE');
1060 fnd_message.SET_TOKEN('TEXT', 'Cannot find row to delete');
1061 fnd_msg_pub.ADD;
1062
1063 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1064 END IF;
1065
1066 end if;
1067
1068 pv_leadlog_pvt.InsertAssignLogRow (
1069 X_Rowid => l_rowid,
1070 x_assignlog_ID => l_assignment_log_id,
1071 p_Lead_assignment_ID => p_lead_assignment_ID,
1072 p_Last_Updated_By => FND_GLOBAL.USER_ID,
1073 p_Last_Update_Date => SYSDATE,
1074 p_Last_Update_Login => FND_GLOBAL.LOGIN_ID,
1075 p_Created_By => FND_GLOBAL.USER_ID,
1076 p_Creation_Date => SYSDATE,
1077 p_Object_Version_Number => l_object_version_number,
1078 p_lead_id => l_lead_id,
1079 p_partner_id => l_partner_id,
1080 p_assign_sequence => l_assign_sequence,
1081 p_status_date => l_status_date,
1082 p_status => l_status,
1083 p_wf_item_type => l_wf_item_type,
1084 p_wf_item_key => l_wf_item_key,
1085 p_trans_type => NULL,
1086 p_error_txt => NULL,
1087 p_status_change_comments => NULL,
1088 p_cm_id => NULL,
1089 p_duration => NULL,
1090 p_wf_pt_user => NULL,
1091 p_wf_cm_user => NULL,
1092 x_return_status => x_return_status);
1093
1094 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
1095 raise FND_API.G_EXC_ERROR;
1096 end if;
1097
1098
1099 IF FND_API.To_Boolean ( p_commit ) THEN
1100 COMMIT WORK;
1101 END IF;
1102
1103 -- Standard call to get message count and if count is 1, get message info.
1104 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_TRUE,
1105 p_count => x_msg_count,
1106 p_data => x_msg_data);
1107 EXCEPTION
1108 -- -------------------------------------------------------------------------------
1109 -- pklin
1110 -- Capture "ORA-00054: resource busy and acquire with NOWAIT specified" error
1111 -- so that no other user/session can update the row in pv_lead_assignments
1112 -- when the current session has not completed yet.
1113 -- -------------------------------------------------------------------------------
1114 WHEN g_e_resource_busy THEN
1115 x_return_status := FND_API.G_RET_STS_ERROR;
1116 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_TRUE,
1117 p_count => x_msg_count,
1118 p_data => x_msg_data);
1119
1120 RAISE;
1121
1122
1123 WHEN FND_API.G_EXC_ERROR THEN
1124
1125 x_return_status := FND_API.G_RET_STS_ERROR ;
1126 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_TRUE,
1127 p_count => x_msg_count,
1128 p_data => x_msg_data);
1129
1130 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1131
1132 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1133 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_TRUE,
1134 p_count => x_msg_count,
1135 p_data => x_msg_data);
1136
1137 WHEN OTHERS THEN
1138
1139 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1140 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
1141 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_TRUE,
1142 p_count => x_msg_count,
1143 p_data => x_msg_data);
1144
1145 end UpdateAssignment;
1146
1147
1148 procedure removeRejectedFromAccess (
1149 p_api_version_number IN NUMBER
1150 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
1151 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
1152 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
1153 ,p_itemtype IN VARCHAR2
1154 ,p_itemkey IN VARCHAR2
1155 ,p_partner_id IN VARCHAR2
1156 ,x_msg_count OUT NOCOPY NUMBER
1157 ,x_msg_data OUT NOCOPY VARCHAR2
1158 ,x_return_status OUT NOCOPY VARCHAR2) is
1159
1160
1161
1162 CURSOR lc_get_routing_status ( pc_itemtype VARCHAR2
1163 , pc_itemkey VARCHAR2 )
1164 IS
1165 SELECT routing_status
1166 FROM pv_lead_workflows
1167 WHERE wf_item_type = pc_itemtype
1168 AND wf_item_key = pc_itemkey;
1169
1170 -- lc_get_reject_accesses + lc_get_reject_accesses_pt
1171 -- will select all cm, partner contact, partner org that are in the assignment table
1172 -- associated with the opportunity for all cm_rejected/pt_rejected/pt_timeout/lost_chance/
1173 -- match_withdrawn/offer_withdrawn partners
1174 -- it will not select anyone/thing not in assignment table (which is perfect)
1175
1176 cursor lc_get_reject_accesses(pc_itemtype varchar2, pc_itemkey varchar2) is
1177 select
1178 a1.lead_id, d.user_name access_user, a2.resource_id
1179 from
1180 pv_lead_assignments a1,
1181 pv_party_notifications a2,
1182 jtf_rs_resource_extns b,
1183 as_accesses_all c,
1184 fnd_user d
1185 where
1186 a1.wf_item_type = pc_itemtype
1187 and a1.wf_item_key = pc_itemkey
1188 and a1.status in ( pv_assignment_pub.g_la_status_cm_rejected,
1189 pv_assignment_pub.g_la_status_pt_rejected,
1190 pv_assignment_pub.g_la_status_pt_timeout,
1191 pv_assignment_pub.g_la_status_lost_chance,
1192 pv_assignment_pub.g_la_status_match_withdrawn,
1193 pv_assignment_pub.g_la_status_offer_withdrawn)
1194 and not exists
1195 (select 1 from pv_lead_assignments la , pv_party_notifications pn
1196 where la.wf_item_type = pc_itemtype
1197 and la.wf_item_key = pc_itemkey
1198 and la.status in (pv_assignment_pub.g_la_status_cm_approved,
1199 pv_assignment_pub.g_la_status_pt_approved,
1200 pv_assignment_pub.g_la_status_pt_created,
1201 pv_assignment_pub.g_la_status_cm_app_for_pt,
1202 pv_assignment_pub.g_la_status_cm_timeout)
1203 and la.lead_assignment_id = pn.lead_assignment_id
1204 and pn.resource_id = a2.resource_id)
1205 and a1.lead_assignment_id = a2.lead_assignment_id
1206 and a2.resource_id = b.resource_id
1207 and b.user_id = d.user_id
1208 and a2.resource_id = c.salesforce_id
1209 and a1.lead_id = c.lead_id;
1210
1211
1212 cursor lc_get_reject_accesses_pt(pc_itemtype varchar2, pc_itemkey varchar2) is
1213 select
1214 a.lead_id, 'PARTNER', b.resource_id
1215 from
1216 pv_lead_assignments a,
1217 jtf_rs_resource_extns b,
1218 as_accesses_all c
1219 where
1220 a.wf_item_type = pc_itemtype
1221 and a.wf_item_key = pc_itemkey
1222 and a.status in ( pv_assignment_pub.g_la_status_cm_rejected,
1223 pv_assignment_pub.g_la_status_pt_rejected,
1224 pv_assignment_pub.g_la_status_pt_timeout,
1225 pv_assignment_pub.g_la_status_lost_chance,
1226 pv_assignment_pub.g_la_status_match_withdrawn,
1227 pv_assignment_pub.g_la_status_offer_withdrawn)
1228 and a.partner_id = b.source_id
1229 and b.category = 'PARTNER'
1230 AND B.RESOURCE_ID = C.SALESFORCE_ID
1231 and c.lead_id = a.lead_id;
1232
1233 -- this will select all cm, partner contact, partner org that are in the assignment table
1234 -- associated with the active opportunity for all partners when all partners have abandoned
1235 -- the opportunity
1236
1237
1238 cursor lc_get_pt_cm_accesses (pc_itemtype varchar2, pc_itemkey varchar2, pc_partner_id number) is
1239 select
1240 d.lead_id, 'PARTY', c.resource_id
1241 from
1242 pv_lead_assignments la,
1243 pv_partner_profiles pvpp,
1244 hz_relationships b,
1245 jtf_rs_resource_extns c,
1246 as_accesses_all d
1247 where
1248 la.wf_item_type = pc_itemtype and
1249 la.wf_item_key = pc_itemkey and
1250 la.partner_id = pc_partner_id and
1251 la.partner_id = pvpp.partner_id and
1252 pvpp.status in ('A', 'I') and
1253 pvpp.partner_party_id = b.object_id and
1254 b.subject_table_name = 'HZ_PARTIES' and
1255 b.object_table_name = 'HZ_PARTIES' and
1256 b.directional_flag = 'F' and
1257 b.relationship_code = 'EMPLOYEE_OF' and
1258 b.relationship_type = 'EMPLOYMENT' and
1259 b.status in ('A', 'I') and
1260 b.party_id = c.source_id and
1261 c.category = pv_assignment_pub.g_resource_party and
1262 c.resource_id = d.salesforce_id and
1263 d.lead_id = la.lead_id
1264 union all
1265 select
1266 a1.lead_id, d.user_name access_user, a2.resource_id
1267 from
1268 pv_lead_assignments a1,
1269 pv_party_notifications a2,
1270 jtf_rs_resource_extns b,
1271 as_accesses_all c,
1272 fnd_user d
1273 where
1274 a1.wf_item_type = pc_itemtype
1275 and a1.wf_item_key = pc_itemkey
1276 and a1.partner_id = pc_partner_id
1277 and not exists
1278 (select 1 from pv_lead_assignments la , pv_party_notifications pn
1279 where la.wf_item_type = pc_itemtype
1280 and la.wf_item_key = pc_itemkey
1281 and la.partner_id <> a1.partner_id
1282 and la.status in (pv_assignment_pub.g_la_status_cm_approved,
1283 pv_assignment_pub.g_la_status_pt_approved,
1284 pv_assignment_pub.g_la_status_pt_created,
1285 pv_assignment_pub.g_la_status_cm_app_for_pt,
1286 pv_assignment_pub.g_la_status_cm_timeout)
1287 and la.lead_assignment_id = pn.lead_assignment_id
1288 and pn.resource_id = a2.resource_id)
1289 and a1.lead_assignment_id = a2.lead_assignment_id
1290 and a2.notification_type = pv_assignment_pub.g_notify_type_matched_to
1291 and a2.resource_id = b.resource_id
1292 and b.user_id = d.user_id
1293 and a2.resource_id = c.salesforce_id
1294 and a1.lead_id = c.lead_id
1295 union all
1296 select
1297 c.lead_id, 'PARTNER', b.resource_id
1298 from
1299 pv_lead_assignments la,
1300 jtf_rs_resource_extns b,
1301 as_accesses_all c
1302 where
1303 la.wf_item_type = pc_itemtype and
1304 la.wf_item_key = pc_itemkey and
1305 la.partner_id = pc_partner_id and
1306 la.partner_id = b.source_id and
1307 b.category = 'PARTNER' and
1308 B.RESOURCE_ID = C.SALESFORCE_ID and
1309 c.lead_id = la.lead_id;
1310
1311 -- this will select all partner contact, partner org that are in the assignment table
1312 -- associated with the active opportunity for all partners when cm withdraws an active
1313 -- opportunity
1314 CURSOR lc_get_pt_accesses (pc_itemtype varchar2, pc_itemkey varchar2)
1315 IS
1316 SELECT
1317 d.lead_id, 'PARTY', c.resource_id
1318 FROM
1319 pv_lead_assignments la,
1320 pv_partner_profiles pvpp,
1321 hz_relationships b,
1322 jtf_rs_resource_extns c,
1323 as_accesses_all d
1324 WHERE
1325 la.wf_item_type = pc_itemtype
1326 AND la.wf_item_key = pc_itemkey
1327 AND la.partner_id = pvpp.partner_id
1328 AND pvpp.status in ('A', 'I')
1329 AND pvpp.partner_party_id = b.object_id
1330 AND b.subject_table_name = 'HZ_PARTIES'
1331 AND b.object_table_name = 'HZ_PARTIES'
1332 AND b.directional_flag = 'F'
1333 AND b.relationship_code = 'EMPLOYEE_OF'
1334 AND b.relationship_type = 'EMPLOYMENT'
1335 AND b.status in ('A', 'I')
1336 AND b.party_id = c.source_id
1337 AND c.category = pv_assignment_pub.g_resource_party
1338 AND c.resource_id = d.salesforce_id
1339 AND d.lead_id = la.lead_id
1340 UNION ALL
1341 SELECT
1342 c.lead_id, 'PARTNER', b.resource_id
1343 FROM
1344 pv_lead_assignments la,
1345 jtf_rs_resource_extns b,
1346 as_accesses_all c
1347 WHERE
1348 la.wf_item_type = pc_itemtype
1349 AND la.wf_item_key = pc_itemkey
1350 AND la.partner_id = b.source_id
1351 AND b.category = 'PARTNER'
1352 AND b.resource_id = c.salesforce_id
1353 AND c.lead_id = la.lead_id;
1354
1355
1356
1357 l_api_name CONSTANT VARCHAR2(30) := 'removeRejectedFromAccess';
1358 l_api_version_number CONSTANT NUMBER := 1.0;
1359
1360 l_party varchar2(100);
1361 l_resource_id number;
1362 l_access_id number;
1363 l_lead_id number;
1364 l_access_type number;
1365 l_routing_status VARCHAR2(30);
1366 l_rm_reject_pt_flag boolean := false;
1367
1368 begin
1369 --
1370 -- Access is removed for the resources in three instances
1371 -- 1. When CM withdraws a matched/offered opportunity
1372 -- 2. When CM withdraws an active opportunity
1373 -- 3. When Partner Abandons the opportunity
1374 --
1375 -- Standard call to check for call compatibility.
1376
1377 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
1378 p_api_version_number,
1379 l_api_name,
1380 G_PKG_NAME) THEN
1381 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1382
1383 END IF;
1384
1385 -- Initialize message list if p_init_msg_list is set to TRUE.
1386 IF FND_API.to_Boolean( p_init_msg_list )
1387 THEN
1388 fnd_msg_pub.initialize;
1389 END IF;
1390
1391 -- Debug Message
1392 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
1393 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
1394 fnd_message.Set_Token('TEXT', 'In ' || l_api_name || '. itemkey = ' || p_itemkey);
1395 fnd_msg_pub.Add;
1396 END IF;
1397
1398 x_return_status := FND_API.G_RET_STS_SUCCESS ;
1399
1400 OPEN lc_get_routing_status ( pc_itemtype => p_itemtype
1401 , pc_itemkey => p_itemkey );
1402 FETCH lc_get_routing_status INTO l_routing_status;
1403 CLOSE lc_get_routing_status;
1404
1405 -- Partner Id will have a value only when Partner Abandons the opportunity
1406
1407 IF p_partner_id IS NULL THEN
1408 IF l_routing_status = pv_assignment_pub.g_r_status_active THEN
1409
1410 open lc_get_pt_accesses (pc_itemtype => p_itemtype, pc_itemkey => p_itemkey);
1411 ELSE
1412 -- this will select all cm, partner contact, partner org that are in the assignment table
1413 -- associated with the cm_rejected/pt_rejected/pt_timeout/lost_chance/
1414 -- match_withdrawn/offer_withdrawn opportunity
1415 open lc_get_reject_accesses(pc_itemtype => p_itemtype, pc_itemkey => p_itemkey );
1416 open lc_get_reject_accesses_pt(pc_itemtype => p_itemtype, pc_itemkey => p_itemkey );
1417 l_rm_reject_pt_flag := true;
1418 END IF;
1419 ELSE
1420 -- this will select all cm, partner contact, partner org that are in the assignment table
1421 -- associated with the active opportunity for all partners when all partners have abandoned
1422 -- the opportunity
1423
1424 open lc_get_pt_cm_accesses (pc_itemtype => p_itemtype, pc_itemkey => p_itemkey, pc_partner_id => p_partner_id);
1425 END IF;
1426
1427 LOOP
1428 IF p_partner_id IS NULL THEN
1429 IF l_routing_status = pv_assignment_pub.g_r_status_active THEN
1430 FETCH lc_get_pt_accesses INTO l_lead_id, l_party, l_resource_id;
1431 EXIT WHEN lc_get_pt_accesses%NOTFOUND;
1432 ELSE
1433 FETCH lc_get_reject_accesses INTO l_lead_id, l_party, l_resource_id;
1434 EXIT WHEN lc_get_reject_accesses%NOTFOUND;
1435 END IF;
1436 ELSE
1437 FETCH lc_get_pt_cm_accesses INTO l_lead_id, l_party, l_resource_id;
1438 EXIT WHEN lc_get_pt_cm_accesses%NOTFOUND;
1439 END IF;
1440
1441 -- Debug Message
1442 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
1443 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
1444 fnd_message.Set_Token('TEXT', 'Removing ' || l_party || ' from accesses');
1445 fnd_msg_pub.Add;
1446 END IF;
1447
1448 if l_party = 'PARTNER' then
1449 l_access_type := pv_assignment_pub.G_PT_ORG_ACCESS;
1450 else
1451 l_access_type := pv_assignment_pub.G_PT_ACCESS;
1452 end if;
1453
1454 pv_assign_util_pvt.UpdateAccess(
1455 p_api_version_number => 1.0,
1456 p_init_msg_list => FND_API.G_FALSE,
1457 p_commit => FND_API.G_FALSE,
1458 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1459 p_itemtype => p_itemType,
1460 p_itemkey => p_itemKey,
1461 p_current_username => NULL, --- obsolete column
1462 p_lead_id => l_lead_id,
1463 p_customer_id => null,
1464 p_address_id => null,
1465 p_access_action => pv_assignment_pub.G_REMOVE_ACCESS,
1466 p_resource_id => l_resource_id,
1467 p_access_type => l_access_type,
1468 x_access_id => l_access_id,
1469 x_return_status => x_return_status,
1470 x_msg_count => x_msg_count,
1471 x_msg_data => x_msg_data);
1472
1473 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
1474 raise FND_API.G_EXC_ERROR;
1475 end if;
1476
1477 end loop;
1478
1479 if l_rm_reject_pt_flag then
1480
1481 LOOP
1482 FETCH lc_get_reject_accesses_pt INTO l_lead_id, l_party, l_resource_id;
1483 EXIT WHEN lc_get_reject_accesses_pt%NOTFOUND;
1484
1485 -- Debug Message
1486 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
1487 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
1488 fnd_message.Set_Token('TEXT', 'Removing ' || l_party || ' from accesses');
1489 fnd_msg_pub.Add;
1490 END IF;
1491
1492 pv_assign_util_pvt.UpdateAccess(
1493 p_api_version_number => 1.0,
1494 p_init_msg_list => FND_API.G_FALSE,
1495 p_commit => FND_API.G_FALSE,
1496 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1497 p_itemtype => p_itemType,
1498 p_itemkey => p_itemKey,
1499 p_current_username => NULL, --- obsolete column
1500 p_lead_id => l_lead_id,
1501 p_customer_id => null,
1502 p_address_id => null,
1503 p_access_action => pv_assignment_pub.G_REMOVE_ACCESS,
1504 p_resource_id => l_resource_id,
1505 p_access_type => pv_assignment_pub.G_PT_ORG_ACCESS,
1506 x_access_id => l_access_id,
1507 x_return_status => x_return_status,
1508 x_msg_count => x_msg_count,
1509 x_msg_data => x_msg_data);
1510
1511 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
1512 raise FND_API.G_EXC_ERROR;
1513 end if;
1514
1515 end loop;
1516 end if;
1517
1518 IF p_partner_id IS NULL THEN
1519 IF l_routing_status = pv_assignment_pub.g_r_status_active THEN
1520 close lc_get_pt_accesses;
1521 ELSE
1522 close lc_get_reject_accesses;
1523 close lc_get_reject_accesses_pt;
1524 END IF;
1525 ELSE
1526 CLOSE lc_get_pt_cm_accesses;
1527 END IF;
1528
1529 IF FND_API.To_Boolean ( p_commit ) THEN
1530 COMMIT WORK;
1531 END IF;
1532
1533 -- Standard call to get message count and if count is 1, get message info.
1534 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_TRUE,
1535 p_count => x_msg_count,
1536 p_data => x_msg_data);
1537 EXCEPTION
1538
1539 WHEN FND_API.G_EXC_ERROR THEN
1540
1541 x_return_status := FND_API.G_RET_STS_ERROR ;
1542 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_TRUE,
1543 p_count => x_msg_count,
1544 p_data => x_msg_data);
1545
1546 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1547
1548 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1549 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_TRUE,
1550 p_count => x_msg_count,
1551 p_data => x_msg_data);
1552
1553 WHEN OTHERS THEN
1554
1555 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1556 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
1557
1558 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_TRUE,
1559 p_count => x_msg_count,
1560 p_data => x_msg_data);
1561
1562 end removeRejectedFromAccess;
1563
1564
1565 procedure setTimeout (
1566 p_api_version_number IN NUMBER
1567 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
1568 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
1569 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
1570 ,p_itemtype IN varchar2
1571 ,p_itemkey IN varchar2
1572 ,p_partner_id in number
1573 ,p_timeoutType in varchar2
1574 ,x_msg_count OUT NOCOPY NUMBER
1575 ,x_msg_data OUT NOCOPY VARCHAR2
1576 ,x_return_status OUT NOCOPY VARCHAR2) is
1577
1578 l_api_name CONSTANT VARCHAR2(30) := 'setTimeout';
1579 l_api_version_number CONSTANT NUMBER := 1.0;
1580
1581 l_query varchar2(2000);
1582 l_timeout_profile varchar2(100);
1583 l_notification_type varchar2(100);
1584 l_timeout number := 0;
1585 l_lead_id number ;
1586 lc_cursor pv_assignment_pub.g_ref_cursor_type;
1587 l_matched_due_date date;
1588 l_no_of_wkend number;
1589 l_offered_due_date date;
1590 l_GMT_date date;
1591 l_due_date date;
1592 l_GMT_time varchar2(60);
1593 l_matched_GMT_date date;
1594 l_offered_GMT_date date;
1595 l_matched_GMT_time varchar2(30);
1596 l_offered_GMT_time varchar2(30);
1597 l_server_timezone_id number;
1598 l_GMT_timezone_id number;
1599 l_process_rule_id number;
1600 l_timeout_uom varchar2(100);
1601 l_rule_timeout number;
1602 l_match_timeout number := 0;
1603 l_offer_timeout number := 0;
1604
1605 CURSOR lc_get_rule_timeout(lc_timeoutType varchar2,
1606 lc_process_rule_id number)
1607 is
1608 SELECT decode(lc_timeoutType, pv_assignment_pub.g_matched_timeout,
1609 decode(cm_timeout_uom_code,'DAYS',(cm_timeout*24),cm_timeout)
1610 , pv_assignment_pub.g_offered_timeout,
1611 decode(partner_timeout_uom_code,'DAYS',(partner_timeout*24),partner_timeout))
1612 FROM PV_ENTITY_ROUTINGS
1613 WHERE PROCESS_RULE_ID = lc_process_rule_id;
1614
1615
1616
1617
1618 begin
1619 -- Standard call to check for call compatibility.
1620
1621 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
1622 p_api_version_number,
1623 l_api_name,
1624 G_PKG_NAME) THEN
1625 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1626
1627 END IF;
1628
1629 -- Initialize message list if p_init_msg_list is set to TRUE.
1630 IF FND_API.to_Boolean( p_init_msg_list )
1631 THEN
1632 fnd_msg_pub.initialize;
1633 END IF;
1634
1635 -- Debug Message
1636 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
1637 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
1638 fnd_message.Set_Token('TEXT', 'In ' || l_api_name || '. itemkey: ' || p_itemkey || '. Type: ' || p_timeouttype);
1639 fnd_msg_pub.Add;
1640 END IF;
1641
1642 x_return_status := FND_API.G_RET_STS_SUCCESS ;
1643
1644
1645 if p_timeoutType = pv_assignment_pub.g_matched_timeout then
1646
1647 l_timeout_profile := 'PV_DEFAULT_CM_TIMEOUT';
1648
1649 elsif p_timeoutType = pv_assignment_pub.g_offered_timeout then
1650
1651 l_timeout_profile := 'PV_DEFAULT_PT_TIMEOUT';
1652
1653 else
1654
1655 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
1656 fnd_message.set_token('TEXT', 'Invalid timeout type: ' || p_timeoutType);
1657 fnd_msg_pub.Add;
1658
1659 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1660
1661 end if;
1662
1663
1664 l_lead_id := wf_engine.GetItemAttrNumber(itemtype => p_itemtype,
1665 itemkey => p_itemkey,
1666 aname => pv_workflow_pub.g_wf_attr_opportunity_id);
1667
1668 l_process_rule_id := wf_engine.GetItemAttrNumber(itemtype => p_itemtype,
1669 itemkey => p_itemkey,
1670 aname => pv_workflow_pub.g_wf_attr_process_rule_id);
1671
1672
1673 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
1674 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
1675 fnd_message.Set_Token('TEXT', 'Process Rule ID from set timeout '||l_process_rule_id);
1676 fnd_msg_pub.Add;
1677 END IF;
1678
1679
1680 --l_server_timezone_id := fnd_profile.value('AMS_SYSTEM_TIMEZONE_ID');
1681 l_server_timezone_id := fnd_profile.value('SERVER_TIMEZONE_ID');
1682
1683
1684
1685 select UPGRADE_TZ_ID into l_GMT_timezone_id
1686 from fnd_timezones_vl
1687 where timezone_code = 'GMT';
1688
1689 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
1690 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
1691 fnd_message.Set_Token('TEXT', 'GMT Timezone ID '|| l_GMT_timezone_id);
1692 fnd_msg_pub.Add;
1693 END IF;
1694
1695 -- -----------------------------------------------------------------------
1696 -- This query retrieves CM or partner timeout based on the address' country.
1697 -- If the address is not provided, it will retrieve the timeout from
1698 -- a default profile value (PV_DEFAULT_PT_TIMEOUT or PV_DEFAULT_CM_TIMEOUT).
1699 -- -----------------------------------------------------------------------
1700 l_query :=
1701 'select nvl(max(timeout_period), fnd_profile.value(:bv1))*60 ' ||
1702 'from pv_country_timeouts pr ' ||
1703 'where pr.timeout_type = :1 ' ||
1704 'and pr.country_code in ';
1705
1706 if p_timeoutType = pv_assignment_pub.g_matched_timeout then
1707
1708 l_query := l_query || ' ( select loc.country from '||
1709 ' hz_locations loc, hz_party_sites pty, as_leads_all lead '||
1710 ' where pty.location_id = loc.location_id '||
1711 ' and pty.party_site_id = lead.address_id '||
1712 ' and lead.lead_id = :2 ) ';
1713
1714 elsif p_timeoutType = pv_assignment_pub.g_offered_timeout then
1715
1716 l_query := l_query || ' ( select hzl.country from '||
1717 'hz_locations hzl, hz_party_sites hzps, pv_lead_assignments lead, '||
1718 'hz_parties partner, hz_relationships hzrl, hz_organization_profiles hzop '||
1719 'where hzl.location_id = hzps.location_id '||
1720 'and hzps.party_id = partner.party_id '||
1721 'and hzrl.party_id = lead.partner_id '||
1722 'and hzrl.subject_id = partner.party_id '||
1723 'and hzrl.object_id = hzop.party_id '||
1724 'and hzrl.subject_table_name = ''HZ_PARTIES'' '||
1725 'and hzrl.object_table_name = ''HZ_PARTIES'' '||
1726 'and hzrl.status in (''A'',''I'') '||
1727 'and hzop.internal_flag = ''Y'' '||
1728 'and hzop.effective_end_date is null '||
1729 'and partner.status = ''A'' '||
1730 'and lead.wf_item_type = :2 ' ||
1731 'and lead.wf_item_key = :3 ' ||
1732 'and hzps.identifying_address_flag(+) = ''Y'' ';
1733
1734 if p_partner_id is not null and p_timeoutType = pv_assignment_pub.g_offered_timeout then
1735 l_query := l_query || ' and lead.partner_id = :4 )';
1736 else
1737 l_query := l_query || ')';
1738 end if;
1739
1740 end if;
1741
1742
1743 if p_timeoutType = pv_assignment_pub.g_matched_timeout then
1744
1745 if l_process_rule_id is not null then
1746
1747 open lc_get_rule_timeout(p_timeoutType, l_process_rule_id);
1748 fetch lc_get_rule_timeout into l_rule_timeout;
1749
1750 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
1751 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
1752 fnd_message.Set_Token('TEXT', 'Timeout type is '||p_timeoutType||'Rule timeout is '|| l_rule_timeout);
1753 fnd_msg_pub.Add;
1754 END IF;
1755
1756 IF lc_get_rule_timeout%FOUND and l_rule_timeout is not null THEN
1757 l_timeout := l_rule_timeout*60;
1758 ELSE
1759 close lc_get_rule_timeout;
1760
1761 open lc_cursor for l_query using l_timeout_profile, p_timeoutType, l_lead_id;
1762 fetch lc_cursor into l_timeout;
1763 close lc_cursor;
1764 END IF;
1765
1766 else
1767
1768 open lc_cursor for l_query using l_timeout_profile, p_timeoutType, l_lead_id;
1769 fetch lc_cursor into l_timeout;
1770 close lc_cursor;
1771
1772 end if;
1773
1774 elsif p_timeoutType = pv_assignment_pub.g_offered_timeout then
1775
1776 if l_process_rule_id is not null then
1777
1778 open lc_get_rule_timeout(p_timeoutType, l_process_rule_id);
1779 fetch lc_get_rule_timeout into l_rule_timeout;
1780
1781
1782 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
1783 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
1784 fnd_message.Set_Token('TEXT', 'Rule timeout is from auto matching rule '|| l_rule_timeout);
1785 fnd_msg_pub.Add;
1786 END IF;
1787
1788 IF lc_get_rule_timeout%FOUND and l_rule_timeout is not null THEN
1789 l_timeout := l_rule_timeout*60;
1790 ELSE
1791 close lc_get_rule_timeout;
1792
1793 if p_partner_id is not null then
1794 open lc_cursor for l_query using l_timeout_profile, p_timeoutType, p_itemtype, p_itemkey, p_partner_id;
1795 else
1796 open lc_cursor for l_query using l_timeout_profile, p_timeoutType, p_itemtype, p_itemkey;
1797 end if;
1798
1799 fetch lc_cursor into l_timeout;
1800 close lc_cursor;
1801 END IF;
1802
1803 else
1804 if p_partner_id is not null then
1805 open lc_cursor for l_query using l_timeout_profile, p_timeoutType, p_itemtype, p_itemkey, p_partner_id;
1806 else
1807 open lc_cursor for l_query using l_timeout_profile, p_timeoutType, p_itemtype, p_itemkey;
1808 end if;
1809 fetch lc_cursor into l_timeout;
1810 close lc_cursor;
1811
1812 end if;
1813
1814
1815 end if;
1816
1817 -- ------------------------------------------------------------------
1818 -- If l_timeout is NULL, i.e. no address defined for this customer and
1819 -- no default timeout profile specified, throw an exception.
1820 -- ------------------------------------------------------------------
1821 IF (l_timeout IS NULL AND l_timeout_profile = 'PV_DEFAULT_CM_TIMEOUT') THEN
1822 Set_Message(p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
1823 p_msg_name => 'PV_NO_DEFAULT_CM_TIMEOUT',
1824 p_token1 => null,
1825 p_token1_value => null,
1826 p_token2 => null,
1827 p_token2_value => null);
1828
1829 RAISE FND_API.G_EXC_ERROR;
1830
1831 ELSIF (l_timeout IS NULL AND l_timeout_profile = 'PV_DEFAULT_PT_TIMEOUT') THEN
1832 Set_Message(p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
1833 p_msg_name => 'PV_NO_DEFAULT_PT_TIMEOUT',
1834 p_token1 => null,
1835 p_token1_value => null,
1836 p_token2 => null,
1837 p_token2_value => null);
1838
1839 RAISE FND_API.G_EXC_ERROR;
1840 END IF;
1841
1842 -- wf will disable timeout if 0. Since what we want is to have wf
1843 -- follow the timeout path immediately, we set it to 1 minute
1844
1845 if l_timeout = 0 then
1846 l_timeout := 1;
1847 end if;
1848
1849 l_timeout := l_timeout/60/24;
1850
1851 /* Get timeout date */
1852
1853 pvx_utility_pvt.add_business_days
1854 (
1855 p_no_of_days => l_timeout,
1856 x_business_date => l_due_date
1857 );
1858
1859 HZ_TIMEZONE_PUB.get_time(
1860 p_api_version => 1.0,
1861 p_init_msg_list => p_init_msg_list,
1862 p_source_tz_id => l_server_timezone_id ,
1863 p_dest_tz_id => l_GMT_timezone_id ,
1864 p_source_day_time => l_due_date,
1865 x_dest_day_time => l_GMT_date,
1866 x_return_status => x_return_status,
1867 x_msg_count => x_msg_count,
1868 x_msg_data => x_msg_data);
1869
1870
1871 l_GMT_time := to_char(l_GMT_date,'DD-MON-YYYY HH24:MI')||' '||'GMT';
1872
1873
1874 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
1875 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
1876 fnd_message.Set_Token('TEXT', 'GMT timeout is '|| l_GMT_time);
1877 fnd_msg_pub.Add;
1878 END IF;
1879
1880
1881 IF p_timeoutType = pv_assignment_pub.g_matched_timeout THEN
1882
1883 update pv_lead_workflows set matched_due_date = l_due_date,
1884 object_version_number = object_version_number + 1
1885 where wf_item_type = p_itemtype
1886 and wf_item_key = p_itemkey;
1887
1888 wf_engine.SetItemAttrNumber( itemtype => p_itemtype,
1889 itemkey => p_itemkey,
1890 aname => pv_workflow_pub.g_wf_attr_matched_timeout,
1891 avalue => (l_due_date-sysdate)*60*24);
1892
1893 wf_engine.SetItemAttrText( itemtype => p_itemtype,
1894 itemkey => p_itemkey,
1895 aname => pv_workflow_pub.g_wf_attr_matched_timeout_dt,
1896 avalue => l_GMT_time);
1897
1898
1899 ELSIF p_timeoutType = pv_assignment_pub.g_offered_timeout THEN
1900
1901 update pv_lead_workflows set offered_due_date = l_due_date,
1902 object_version_number = object_version_number + 1
1903 where wf_item_type = p_itemtype
1904 and wf_item_key = p_itemkey;
1905
1906 wf_engine.SetItemAttrNumber( itemtype => p_itemtype,
1907 itemkey => p_itemkey,
1908 aname => pv_workflow_pub.g_wf_attr_offered_timeout,
1909 avalue => (l_due_date-sysdate)*60*24);
1910
1911 wf_engine.SetItemAttrText( itemtype => p_itemtype,
1912 itemkey => p_itemkey,
1913 aname => pv_workflow_pub.g_wf_attr_offered_timeout_dt,
1914 avalue => l_GMT_time);
1915
1916 END IF;
1917
1918 -- Debug Message
1919 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
1920 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
1921 fnd_message.Set_token('TEXT', 'Timeout set to: ' || l_timeout);
1922 fnd_msg_pub.Add;
1923 END IF;
1924
1925 IF FND_API.To_Boolean ( p_commit ) THEN
1926 COMMIT WORK;
1927 END IF;
1928
1929 -- Standard call to get message count and if count is 1, get message info.
1930 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_TRUE,
1931 p_count => x_msg_count,
1932 p_data => x_msg_data);
1933 EXCEPTION
1934
1935 WHEN FND_API.G_EXC_ERROR THEN
1936
1937 x_return_status := FND_API.G_RET_STS_ERROR ;
1938 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_TRUE,
1939 p_count => x_msg_count,
1940 p_data => x_msg_data);
1941
1942 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1943
1944 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1945 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_TRUE,
1946 p_count => x_msg_count,
1947 p_data => x_msg_data);
1948
1949 WHEN OTHERS THEN
1950
1951 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1952 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
1953 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_TRUE,
1954 p_count => x_msg_count,
1955 p_data => x_msg_data);
1956
1957 end setTimeout;
1958
1959
1960 procedure SetPartnerAttributes (
1961 p_api_version_number IN NUMBER
1962 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
1963 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
1964 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
1965 ,p_itemType in varchar2
1966 ,p_itemKey in varchar2
1967 ,p_partner_id in NUMBER
1968 ,p_partner_org in varchar2
1969 ,x_msg_count OUT NOCOPY NUMBER
1970 ,x_msg_data OUT NOCOPY VARCHAR2
1971 ,x_return_status OUT NOCOPY VARCHAR2) is
1972
1973 l_api_name CONSTANT VARCHAR2(30) := 'SetPartnerAttributes';
1974 l_api_version_number CONSTANT NUMBER := 1.0;
1975
1976 l_pt_contact_role_name varchar2(50);
1977 l_assignment_type varchar2(30);
1978
1979 begin
1980 -- Standard call to check for call compatibility.
1981
1982 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
1983 p_api_version_number,
1984 l_api_name,
1985 G_PKG_NAME) THEN
1986 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1987
1988 END IF;
1989
1990 -- Initialize message list if p_init_msg_list is set to TRUE.
1991 IF FND_API.to_Boolean( p_init_msg_list )
1992 THEN
1993 fnd_msg_pub.initialize;
1994 END IF;
1995
1996 -- Debug Message
1997 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
1998 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
1999 fnd_message.Set_Token('TEXT', 'In ' || l_api_name || '. Itemkey: ' || p_itemkey || '. Partner id: ' || p_partner_id);
2000 fnd_msg_pub.Add;
2001 END IF;
2002
2003 x_return_status := FND_API.G_RET_STS_SUCCESS ;
2004
2005 l_assignment_type := wf_engine.GetItemAttrText( itemtype => p_itemtype,
2006 itemkey => p_itemkey,
2007 aname => pv_workflow_pub.g_wf_attr_assignment_type);
2008
2009 -- for joint, we are reusing the same role_name to send email to
2010 -- both CM_APP_FOR_PT partners and CM_APPROVED partners
2011 -- Therefore, do it in bypass/require pt approval check API
2012
2013 if p_partner_id is not null then
2014 -- in broadcast and joint, partner_id will not be set because of multiple values
2015
2016 /*****************************************************/
2017 /* set the partners organization name */
2018 /*****************************************************/
2019
2020 wf_engine.SetItemAttrText (itemtype => p_itemType,
2021 itemkey => p_itemKey,
2022 aname => pv_workflow_pub.g_wf_attr_partner_org,
2023 avalue => p_partner_org);
2024
2025 /*****************************************************/
2026 /* set the partners id */
2027 /*****************************************************/
2028
2029 wf_engine.SetItemAttrText (itemtype => p_itemType,
2030 itemkey => p_itemKey,
2031 aname => pv_workflow_pub.g_wf_attr_partner_id,
2032 avalue => p_partner_id);
2033
2034 end if;
2035
2036 IF FND_API.To_Boolean ( p_commit ) THEN
2037 COMMIT WORK;
2038 END IF;
2039
2040 -- Standard call to get message count and if count is 1, get message info.
2041 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_TRUE,
2042 p_count => x_msg_count,
2043 p_data => x_msg_data);
2044 EXCEPTION
2045
2046 WHEN FND_API.G_EXC_ERROR THEN
2047
2048 x_return_status := FND_API.G_RET_STS_ERROR ;
2049 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_TRUE,
2050 p_count => x_msg_count,
2051 p_data => x_msg_data);
2052
2053 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2054
2055 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2056 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_TRUE,
2057 p_count => x_msg_count,
2058 p_data => x_msg_data);
2059
2060 WHEN OTHERS THEN
2061
2062 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2063 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
2064 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_TRUE,
2065 p_count => x_msg_count,
2066 p_data => x_msg_data);
2067
2068 end SetPartnerAttributes;
2069
2070
2071 procedure set_offered_attributes (
2072 p_api_version_number IN NUMBER
2073 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
2074 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
2075 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
2076 ,p_itemType in varchar2
2077 ,p_itemKey in varchar2
2078 ,p_partner_id IN number
2079 ,x_msg_count OUT NOCOPY NUMBER
2080 ,x_msg_data OUT NOCOPY VARCHAR2
2081 ,x_return_status OUT NOCOPY VARCHAR2) is
2082
2083 l_api_name CONSTANT VARCHAR2(30) := 'set_offered_attributes';
2084 l_api_version_number CONSTANT NUMBER := 1.0;
2085
2086 l_customer_id number;
2087 l_resource_id number;
2088 l_address_id number;
2089 l_partner_org varchar2(80);
2090 l_username varchar2(100);
2091 l_lead_id number;
2092 l_temp_number number;
2093 l_username_tbl pv_assignment_pub.g_varchar_table_type := pv_assignment_pub.g_varchar_table_type();
2094 l_resource_id_tbl pv_assignment_pub.g_number_table_type := pv_assignment_pub.g_number_table_type();
2095
2096 cursor lc_get_pt_org_name (pc_partner_id number) is
2097 select pt.party_name
2098 from hz_relationships pr,
2099 hz_organization_profiles op,
2100 hz_parties pt
2101 where pr.party_id = pc_partner_id
2102 and pr.subject_table_name = 'HZ_PARTIES'
2103 and pr.object_table_name = 'HZ_PARTIES'
2104 and pr.status in ('A', 'I')
2105 and pr.object_id = op.party_id
2106 and op.internal_flag = 'Y'
2107 and op.effective_end_date is null
2108 and pr.subject_id = pt.party_id
2109 and pt.status in ('A', 'I');
2110
2111 cursor lc_get_all_offered_to (pc_itemtype varchar2,
2112 pc_itemkey varchar2,
2113 pc_notify_type varchar2) is
2114 select usr.user_name, pn.resource_id
2115 from pv_lead_assignments la,
2116 pv_party_notifications pn,
2117 fnd_user usr
2118 where la.wf_item_type = pc_itemtype
2119 and la.wf_item_key = pc_itemkey
2120 and la.status in ( pv_assignment_pub.g_la_status_cm_approved,
2121 pv_assignment_pub.g_la_status_cm_added,
2122 pv_assignment_pub.g_la_status_cm_bypassed,
2123 pv_assignment_pub.g_la_status_cm_app_for_pt,
2124 pv_assignment_pub.g_la_status_cm_timeout)
2125 and la.lead_assignment_id = pn.lead_assignment_id
2126 and pn.notification_type = pc_notify_type
2127 and usr.user_id = pn.user_id;
2128
2129 cursor lc_get_offered_to_for_pt (pc_itemtype varchar2,
2130 pc_itemkey varchar2,
2131 pc_partner_id number,
2132 pc_notify_type varchar2) is
2133 select usr.user_name, pn.resource_id
2134 from pv_lead_assignments la,
2135 pv_party_notifications pn,
2136 fnd_user usr
2137 where la.wf_item_type = pc_itemtype
2138 and la.wf_item_key = pc_itemkey
2139 and la.partner_id = pc_partner_id
2140 and la.lead_assignment_id = pn.lead_assignment_id
2141 and pn.notification_type = pc_notify_type
2142 and usr.user_id = pn.user_id ;
2143
2144 begin
2145 -- Standard call to check for call compatibility.
2146
2147 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
2148 p_api_version_number,
2149 l_api_name,
2150 G_PKG_NAME) THEN
2151 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2152
2153 END IF;
2154
2155 -- Initialize message list if p_init_msg_list is set to TRUE.
2156 IF FND_API.to_Boolean( p_init_msg_list )
2157 THEN
2158 fnd_msg_pub.initialize;
2159 END IF;
2160
2161 -- Debug Message
2162 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
2163 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
2164 fnd_message.Set_Token('TEXT', 'In ' || l_api_name);
2165 fnd_msg_pub.Add;
2166 END IF;
2167
2168 x_return_status := FND_API.G_RET_STS_SUCCESS ;
2169
2170 l_customer_id := wf_engine.GetItemAttrNumber(itemtype => p_itemtype,
2171 itemkey => p_itemkey,
2172 aname => pv_workflow_pub.g_wf_attr_customer_id);
2173
2174 l_address_id := wf_engine.GetItemAttrNumber(itemtype => p_itemtype,
2175 itemkey => p_itemkey,
2176 aname => pv_workflow_pub.g_wf_attr_address_id);
2177
2178 l_lead_id := wf_engine.GetItemAttrNumber(itemtype => p_itemtype,
2179 itemkey => p_itemkey,
2180 aname => pv_workflow_pub.g_wf_attr_opportunity_id);
2181
2182 if p_partner_id is not null then
2183
2184 open lc_get_pt_org_name( pc_partner_id => p_partner_id);
2185 fetch lc_get_pt_org_name into l_partner_org;
2186
2187 if lc_get_pt_org_name%notfound then
2188
2189 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
2190 fnd_message.Set_token('TEXT', 'Cannot find partner for itemkey: ' || p_itemkey || '. Partner id: ' || p_partner_id);
2191 fnd_msg_pub.Add;
2192 raise FND_API.G_EXC_ERROR;
2193
2194 end if;
2195
2196 close lc_get_pt_org_name;
2197
2198 end if;
2199
2200 SetPartnerAttributes (
2201 p_api_version_number => 1.0
2202 ,p_init_msg_list => FND_API.G_FALSE
2203 ,p_commit => FND_API.G_FALSE
2204 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
2205 ,p_itemType => p_itemtype
2206 ,p_itemKey => p_itemkey
2207 ,p_partner_id => p_partner_id
2208 ,p_partner_org => l_partner_org
2209 ,x_msg_count => x_msg_count
2210 ,x_msg_data => x_msg_data
2211 ,x_return_status => x_return_status);
2212
2213 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
2214 raise FND_API.G_EXC_ERROR;
2215 end if;
2216
2217 if p_partner_id is null then
2218
2219 -- in case of broadcast or joint
2220
2221 open lc_get_all_offered_to (pc_itemtype => p_itemtype,
2222 pc_itemkey => p_itemkey,
2223 pc_notify_type => pv_assignment_pub.g_notify_type_offered_to);
2224 loop
2225
2226 fetch lc_get_all_offered_to into l_username, l_resource_id;
2227 exit when lc_get_all_offered_to%notfound;
2228
2229 l_username_tbl.extend;
2230 l_resource_id_tbl.extend;
2231 l_username_tbl(l_username_tbl.last) := l_username;
2232 l_resource_id_tbl(l_resource_id_tbl.last) := l_resource_id;
2233
2234 end loop;
2235 close lc_get_all_offered_to;
2236
2237 else
2238 -- in case of single or serial
2239
2240 open lc_get_offered_to_for_pt (pc_itemtype => p_itemtype,
2241 pc_itemkey => p_itemkey,
2242 pc_partner_id => p_partner_id,
2243 pc_notify_type => pv_assignment_pub.g_notify_type_offered_to);
2244 loop
2245
2246 fetch lc_get_offered_to_for_pt into l_username, l_resource_id;
2247 exit when lc_get_offered_to_for_pt%notfound;
2248
2249 l_username_tbl.extend;
2250 l_resource_id_tbl.extend;
2251 l_username_tbl(l_username_tbl.last) := l_username;
2252 l_resource_id_tbl(l_resource_id_tbl.last) := l_resource_id;
2253
2254 end loop;
2255 close lc_get_offered_to_for_pt;
2256
2257 end if;
2258
2259 for i in 1 .. l_resource_id_tbl.count loop
2260
2261 pv_assign_util_pvt.UpdateAccess(
2262 p_api_version_number => 1.0,
2263 p_init_msg_list => FND_API.G_FALSE,
2264 p_commit => FND_API.G_FALSE,
2265 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
2266 p_itemtype => p_itemType,
2267 p_itemkey => p_itemKey,
2268 p_current_username => l_username_tbl(i),
2269 p_lead_id => l_lead_id,
2270 p_customer_id => l_customer_id,
2271 p_address_id => l_address_id,
2272 p_access_action => pv_assignment_pub.G_ADD_ACCESS,
2273 p_resource_id => l_resource_id_tbl(i),
2274 p_access_type => pv_assignment_pub.G_PT_ACCESS,
2275 x_access_id => l_temp_number,
2276 x_return_status => x_return_status,
2277 x_msg_count => x_msg_count,
2278 x_msg_data => x_msg_data);
2279
2280 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
2281 raise FND_API.G_EXC_ERROR;
2282 end if;
2283
2284 end loop;
2285
2286 pv_assignment_pvt.setTimeout (
2287 p_api_version_number => 1.0,
2288 p_init_msg_list => FND_API.G_FALSE,
2289 p_commit => FND_API.G_FALSE,
2290 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
2291 p_itemtype => p_itemType,
2292 p_itemkey => p_itemKey,
2293 p_partner_id => p_partner_id,
2294 p_timeoutType => pv_assignment_pub.g_offered_timeout,
2295 x_return_status => x_return_status,
2296 x_msg_count => x_msg_count,
2297 x_msg_data => x_msg_data);
2298
2299 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
2300 raise FND_API.G_EXC_ERROR;
2301 end if;
2302
2303 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
2304 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
2305 fnd_message.Set_Token('TEXT', 'Updating opportunity last offered date for partner ' || nvl(to_char(p_partner_id), '(s)'));
2306 fnd_msg_pub.Add;
2307 END IF;
2308
2309 if p_partner_id is not null then
2310 -- single and serial
2311
2312 update pv_partner_profiles
2313 set oppty_last_offered_date = sysdate,
2314 object_version_number = object_version_number + 1,
2315 last_update_date = sysdate,
2316 last_updated_by = FND_GLOBAL.user_id,
2317 last_update_login = FND_GLOBAL.login_id
2318 where partner_id = p_partner_id;
2319
2320 else
2321 -- broadcast and joint
2322
2323 update pv_partner_profiles
2324 set oppty_last_offered_date = sysdate,
2325 object_version_number = object_version_number + 1,
2326 last_update_date = sysdate,
2327 last_updated_by = FND_GLOBAL.user_id,
2328 last_update_login = FND_GLOBAL.login_id
2329 where partner_id in (select partner_id from pv_lead_assignments
2330 where wf_item_type = p_itemtype
2331 and wf_item_key = p_itemkey
2332 and status in
2333 ( pv_assignment_pub.g_la_status_cm_approved,
2334 pv_assignment_pub.g_la_status_cm_added,
2335 pv_assignment_pub.g_la_status_cm_bypassed,
2336 pv_assignment_pub.g_la_status_cm_app_for_pt,
2337 pv_assignment_pub.g_la_status_cm_timeout
2338 ));
2339 end if;
2340
2341 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
2342 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
2343 fnd_message.Set_Token('TEXT', 'Setting access code for partner ' || nvl(to_char(p_partner_id), '(s)'));
2344 fnd_msg_pub.Add;
2345 END IF;
2346
2347 if p_partner_id is not null then
2348 -- single and serial
2349
2350 update pv_lead_assignments
2351 set partner_access_code = decode(status,
2352 pv_assignment_pub.g_la_status_cm_app_for_pt, pv_assignment_pub.g_assign_access_update,
2353 pv_assignment_pub.g_assign_access_view),
2354 object_version_number = object_version_number + 1,
2355 last_update_date = sysdate,
2356 last_updated_by = FND_GLOBAL.user_id,
2357 last_update_login = FND_GLOBAL.login_id
2358 where wf_item_type = p_itemtype
2359 and wf_item_key = p_itemkey
2360 and partner_id = p_partner_id;
2361
2362 else
2363 -- broadcast and joint
2364
2365 update pv_lead_assignments
2366 set partner_access_code = decode(status,
2367 pv_assignment_pub.g_la_status_cm_app_for_pt, pv_assignment_pub.g_assign_access_update,
2368 pv_assignment_pub.g_assign_access_view),
2369 object_version_number = object_version_number + 1,
2370 last_update_date = sysdate,
2371 last_updated_by = FND_GLOBAL.user_id,
2372 last_update_login = FND_GLOBAL.login_id
2373 where rowid in (select rowid from pv_lead_assignments
2374 where wf_item_type = p_itemtype
2375 and wf_item_key = p_itemkey
2376 and status in
2377 ( pv_assignment_pub.g_la_status_cm_approved,
2378 pv_assignment_pub.g_la_status_cm_added,
2379 pv_assignment_pub.g_la_status_cm_bypassed,
2380 pv_assignment_pub.g_la_status_cm_app_for_pt,
2381 pv_assignment_pub.g_la_status_cm_timeout
2382 ));
2383 end if;
2384
2385 IF FND_API.To_Boolean ( p_commit ) THEN
2386 COMMIT WORK;
2387 END IF;
2388
2389 -- Standard call to get message count and if count is 1, get message info.
2390 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_TRUE,
2391 p_count => x_msg_count,
2392 p_data => x_msg_data);
2393 EXCEPTION
2394
2395 WHEN FND_API.G_EXC_ERROR THEN
2396
2397 x_return_status := FND_API.G_RET_STS_ERROR ;
2398 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_TRUE,
2399 p_count => x_msg_count,
2400 p_data => x_msg_data);
2401
2402 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2403
2404 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2405 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_TRUE,
2406 p_count => x_msg_count,
2407 p_data => x_msg_data);
2408
2409 WHEN OTHERS THEN
2410
2411 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2412 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
2413 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_TRUE,
2414 p_count => x_msg_count,
2415 p_data => x_msg_data);
2416
2417 end set_offered_attributes;
2418
2419
2420 -- -----------------------------------------------------------------------------------
2421 -- Procedure Update_Routing_Stage
2422 -- -----------------------------------------------------------------------------------
2423 procedure update_routing_stage (
2424 p_api_version_number IN NUMBER
2425 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
2426 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
2427 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
2428 ,p_itemtype IN varchar2
2429 ,p_itemkey IN varchar2
2430 ,p_routing_stage IN VARCHAR2
2431 ,p_active_but_open_flag IN VARCHAR2
2432 ,x_msg_count OUT NOCOPY NUMBER
2433 ,x_msg_data OUT NOCOPY VARCHAR2
2434 ,x_return_status OUT NOCOPY VARCHAR2) is
2435
2436 l_api_name CONSTANT VARCHAR2(30) := 'update_routing_stage';
2437 l_api_version_number CONSTANT NUMBER := 1.0;
2438
2439 l_routing_type varchar2(30);
2440 l_rowid ROWID;
2441 l_lead_id number;
2442 l_lead_workflow_id number;
2443 l_assignment_log_id number;
2444 l_assignment_type varchar2(30);
2445 l_prior_routing varchar2(30);
2446 l_wf_status varchar2(30);
2447 l_entity varchar2(30);
2448 l_assignment_log_rec assignment_log_rec_type;
2449 l_log_params_tbl pvx_utility_pvt.log_params_tbl_type;
2450
2451 cursor lc_get_workflow (pc_itemtype varchar2,
2452 pc_itemkey varchar2) is
2453 select rowid,
2454 lead_id,
2455 lead_workflow_id,
2456 routing_status,
2457 routing_type
2458 from pv_lead_workflows
2459 where wf_item_type = pc_itemtype
2460 and wf_item_key = pc_itemkey;
2461
2462
2463 cursor lc_get_offered_pt (pc_workflow_id number) is
2464 select b.lead_assignment_id,b.partner_id
2465 from pv_lead_workflows a, pv_lead_assignments b
2466 where a.lead_workflow_id = pc_workflow_id
2467 and a.wf_item_type = b.wf_item_type
2468 and a.wf_item_key = b.wf_item_key
2469 and b.status in ('CM_APPROVED','CM_TIMEOUT','CM_BYPASSED','CM_APP_FOR_PT');
2470
2471 -- ADDED (the not exists condition is needed for joint as there
2472 -- could be multiple partners accepting at different time
2473 -- and this api is called each time. we do not want to log
2474 -- duplicate logs
2475 cursor lc_get_active_pt (pc_workflow_id number) is
2476 select b.lead_assignment_id,b.partner_id
2477 from pv_lead_workflows a, pv_lead_assignments b
2478 where a.lead_workflow_id = pc_workflow_id
2479 and a.wf_item_type = b.wf_item_type
2480 and a.wf_item_key = b.wf_item_key
2481 and b.status in ('PT_APPROVED','CM_APP_FOR_PT')
2482 and not exists (select 1 from pv_assignment_logs aa
2483 where aa.lead_assignment_id = b.lead_assignment_id
2484 and aa.to_lead_status = 'ACTIVE');
2485
2486 cursor lc_get_abandon_pt (pc_workflow_id number) is
2487 select b.lead_assignment_id,b.partner_id
2488 from pv_lead_workflows a, pv_lead_assignments b
2489 where a.lead_workflow_id = pc_workflow_id
2490 and a.wf_item_type = b.wf_item_type
2491 and a.wf_item_key = b.wf_item_key
2492 and b.status in ('PT_ABANDONED')
2493 and not exists (select 1 from pv_assignment_logs aa
2494 where aa.lead_assignment_id = b.lead_assignment_id
2495 and aa.to_lead_status = 'ABANDONED');
2496
2497
2498 begin
2499 -- Standard call to check for call compatibility.
2500
2501 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
2502 p_api_version_number,
2503 l_api_name,
2504 G_PKG_NAME) THEN
2505 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2506
2507 END IF;
2508
2509 -- Initialize message list if p_init_msg_list is set to TRUE.
2510 IF FND_API.to_Boolean( p_init_msg_list )
2511 THEN
2512 fnd_msg_pub.initialize;
2513 END IF;
2514
2515 -- Debug Message
2516 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
2517 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
2518 fnd_message.Set_Token('TEXT', 'In ' || l_api_name);
2519 fnd_msg_pub.Add;
2520 END IF;
2521
2522 x_return_status := FND_API.G_RET_STS_SUCCESS ;
2523
2524 if p_routing_stage not in ( pv_assignment_pub.g_r_status_active,
2525 pv_assignment_pub.g_r_status_matched,
2526 pv_assignment_pub.g_r_status_offered,
2527 pv_assignment_pub.g_r_status_recycled,
2528 pv_assignment_pub.g_r_status_abandoned,
2529 pv_assignment_pub.g_r_status_unassigned,
2530 pv_assignment_pub.g_r_status_withdrawn) then
2531
2532 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
2533 fnd_message.SET_TOKEN('TEXT', 'Invalid workflow routing stage. Itemkey: ' || p_itemkey ||
2534 '. Stage: ' || p_routing_stage);
2535 fnd_msg_pub.ADD;
2536
2537 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2538
2539 end if;
2540
2541 open lc_get_workflow (pc_itemtype => p_itemtype,
2542 pc_itemkey => p_itemkey);
2543
2544 fetch lc_get_workflow into l_rowid, l_lead_id, l_lead_workflow_id, l_prior_routing, l_routing_type;
2545 close lc_get_workflow;
2546
2547
2548 if l_rowid is null then
2549 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
2550 fnd_message.SET_TOKEN('TEXT', 'Cannot find workflow row to update. Itemkey: ' || p_itemkey);
2551 fnd_msg_pub.ADD;
2552 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2553 end if;
2554
2555 if p_routing_stage = pv_assignment_pub.g_r_status_active and p_active_but_open_flag = 'Y' then
2556
2557 -- only possible in joint selling where we want to set routing_stage to ACTIVE the moment
2558 -- 1 partner accept
2559
2560 l_wf_status := pv_assignment_pub.g_wf_status_open;
2561
2562 elsif p_routing_stage = pv_assignment_pub.g_r_status_active and nvl(p_active_but_open_flag, 'N') = 'N' then
2563
2564 l_wf_status := pv_assignment_pub.g_wf_status_closed;
2565
2566 elsif p_routing_stage in ( pv_assignment_pub.g_r_status_unassigned,
2567 pv_assignment_pub.g_r_status_recycled,
2568 pv_assignment_pub.g_r_status_abandoned,
2569 pv_assignment_pub.g_r_status_withdrawn) then
2570
2571 l_wf_status := pv_assignment_pub.g_wf_status_closed;
2572
2573 elsif p_routing_stage in ( pv_assignment_pub.g_r_status_matched,
2574 pv_assignment_pub.g_r_status_offered) then
2575
2576 l_wf_status := pv_assignment_pub.g_wf_status_open;
2577
2578 -- -----------------------------------------------------------------------------
2579 -- Log Offered status for each partner that have been approved by the CM(s).
2580 -- -----------------------------------------------------------------------------
2581 FOR x IN (SELECT lead_number FROM as_leads_all WHERE lead_id = l_lead_id) LOOP
2582 l_log_params_tbl(1).param_name := 'OPP_NUMBER';
2583 l_log_params_tbl(1).param_value := x.lead_number;
2584 END LOOP;
2585
2586 l_log_params_tbl(2).param_name := 'OPP_ROUTING_STATUS';
2587 l_log_params_tbl(2).param_value := pv_assignment_pub.g_r_status_offered;
2588
2589 FOR x IN (
2590 SELECT a.partner_id, c.party_name
2591 FROM pv_lead_assignments a,
2592 pv_partner_profiles b,
2593 hz_parties c
2594 WHERE a.wf_item_type = p_itemtype AND
2595 a.wf_item_key = p_itemkey AND
2596 a.status IN ('CM_APPROVED', 'CM_BYPASSED', 'CM_TIMEOUT') AND
2597 a.partner_id = b.partner_id AND
2598 b.partner_party_id = c.party_id
2599 )
2600 LOOP
2601 l_log_params_tbl(3).param_name := 'PARTNER_NAME';
2602 l_log_params_tbl(3).param_value := x.party_name;
2603
2604 PVX_Utility_PVT.create_history_log(
2605 p_arc_history_for_entity_code => 'OPPORTUNITY',
2606 p_history_for_entity_id => l_lead_id,
2607 p_history_category_code => 'GENERAL',
2608 p_message_code => 'PV_LG_RTNG_OFFERED',
2609 p_partner_id => x.partner_id,
2610 p_access_level_flag => 'V',
2611 p_interaction_level => pvx_utility_pvt.G_INTERACTION_LEVEL_50,
2612 p_comments => NULL,
2613 p_log_params_tbl => l_log_params_tbl,
2614 x_return_status => x_return_status,
2615 x_msg_count => x_msg_count,
2616 x_msg_data => x_msg_data
2617 );
2618 END LOOP;
2619
2620 end if;
2621
2622 update pv_lead_workflows
2623 set routing_status = p_routing_stage,
2624 wf_status = l_wf_status,
2625 object_version_number = object_version_number + 1,
2626 last_update_date = sysdate,
2627 last_updated_by = FND_GLOBAL.user_id,
2628 last_update_login = FND_GLOBAL.login_id
2629 where rowid = l_rowid returning entity into l_entity;
2630
2631 l_assignment_log_rec.LEAD_ID := l_lead_id;
2632 l_assignment_log_rec.FROM_LEAD_STATUS := l_prior_routing;
2633 l_assignment_log_rec.TO_LEAD_STATUS := p_routing_stage;
2634 l_assignment_log_rec.WF_ITEM_TYPE := p_itemtype;
2635 l_assignment_log_rec.WF_ITEM_KEY := p_itemkey;
2636 l_assignment_log_rec.WORKFLOW_ID := l_lead_workflow_id;
2637
2638 if p_routing_stage = 'OFFERED' then
2639 if l_routing_type <> 'SERIAL' then
2640 -- serial handled in pv_workflow_pub.serial_next_partner
2641 for lrec in lc_get_offered_pt (pc_workflow_id => l_lead_workflow_id) loop
2642
2643 l_assignment_log_rec.partner_id := lrec.partner_id;
2644 l_assignment_log_rec.lead_assignment_id := lrec.lead_assignment_id;
2645
2646 Create_assignment_log_row (
2647 p_api_version_number => 1.0,
2648 p_init_msg_list => FND_API.G_FALSE,
2649 p_commit => FND_API.G_FALSE,
2650 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
2651 p_assignment_log_rec => l_assignment_log_rec,
2652 x_assignment_id => l_assignment_log_id,
2653 x_return_status => x_return_status,
2654 x_msg_count => x_msg_count,
2655 x_msg_data => x_msg_data);
2656
2657 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
2658 raise FND_API.G_EXC_ERROR;
2659 end if;
2660 end loop;
2661 end if;
2662
2663 elsif p_routing_stage = 'ACTIVE' then
2664 for lrec in lc_get_active_pt (pc_workflow_id => l_lead_workflow_id) loop
2665
2666 l_assignment_log_rec.partner_id := lrec.partner_id;
2667 l_assignment_log_rec.lead_assignment_id := lrec.lead_assignment_id;
2668
2669 Create_assignment_log_row (
2670 p_api_version_number => 1.0,
2671 p_init_msg_list => FND_API.G_FALSE,
2672 p_commit => FND_API.G_FALSE,
2673 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
2674 p_assignment_log_rec => l_assignment_log_rec,
2675 x_assignment_id => l_assignment_log_id,
2676 x_return_status => x_return_status,
2677 x_msg_count => x_msg_count,
2678 x_msg_data => x_msg_data);
2679
2680 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
2681 raise FND_API.G_EXC_ERROR;
2682 end if;
2683 end loop;
2684
2685 elsif p_routing_stage = 'ABANDONED' then
2686 for lrec in lc_get_abandon_pt (pc_workflow_id => l_lead_workflow_id) loop
2687
2688 l_assignment_log_rec.partner_id := lrec.partner_id;
2689 l_assignment_log_rec.lead_assignment_id := lrec.lead_assignment_id;
2690
2691 Create_assignment_log_row (
2692 p_api_version_number => 1.0,
2693 p_init_msg_list => FND_API.G_FALSE,
2694 p_commit => FND_API.G_FALSE,
2695 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
2696 p_assignment_log_rec => l_assignment_log_rec,
2697 x_assignment_id => l_assignment_log_id,
2698 x_return_status => x_return_status,
2699 x_msg_count => x_msg_count,
2700 x_msg_data => x_msg_data);
2701
2702 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
2703 raise FND_API.G_EXC_ERROR;
2704 end if;
2705 end loop;
2706 else
2707 Create_assignment_log_row (
2708 p_api_version_number => 1.0,
2709 p_init_msg_list => FND_API.G_FALSE,
2710 p_commit => FND_API.G_FALSE,
2711 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
2712 p_assignment_log_rec => l_assignment_log_rec,
2713 x_assignment_id => l_assignment_log_id,
2714 x_return_status => x_return_status,
2715 x_msg_count => x_msg_count,
2716 x_msg_data => x_msg_data);
2717
2718 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
2719 raise FND_API.G_EXC_ERROR;
2720 end if;
2721 end if;
2722
2723 if l_wf_status = pv_assignment_pub.g_wf_status_closed then
2724
2725 if p_routing_stage in (pv_assignment_pub.g_r_status_withdrawn,
2726 pv_assignment_pub.g_r_status_recycled,
2727 pv_assignment_pub.g_r_status_abandoned) then
2728
2729 l_assignment_type := pv_assignment_pub.g_r_status_unassigned;
2730 else
2731 l_assignment_type := null;
2732 end if;
2733
2734 if l_entity = 'OPPORTUNITY' then
2735
2736 update as_leads_all
2737 set prm_assignment_type = nvl(l_assignment_type, prm_assignment_type),
2738 auto_assignment_type = 'TAP'
2739 where lead_id = l_lead_id;
2740
2741 elsif l_entity = 'LEAD' then
2742
2743 update as_sales_leads
2744 set prm_assignment_type = nvl(l_assignment_type, prm_assignment_type),
2745 auto_assignment_type = 'TAP'
2746 where sales_lead_id = l_lead_id;
2747
2748 end if;
2749
2750 end if;
2751
2752 IF FND_API.To_Boolean ( p_commit ) THEN
2753 COMMIT WORK;
2754 END IF;
2755
2756 -- Standard call to get message count and if count is 1, get message info.
2757 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_TRUE,
2758 p_count => x_msg_count,
2759 p_data => x_msg_data);
2760 EXCEPTION
2761
2762 WHEN FND_API.G_EXC_ERROR THEN
2763
2764 x_return_status := FND_API.G_RET_STS_ERROR ;
2765 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_TRUE,
2766 p_count => x_msg_count,
2767 p_data => x_msg_data);
2768
2769 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2770
2771 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2772 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_TRUE,
2773 p_count => x_msg_count,
2774 p_data => x_msg_data);
2775
2776 WHEN OTHERS THEN
2777
2778 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2779 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
2780 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_TRUE,
2781 p_count => x_msg_count,
2782 p_data => x_msg_data);
2783
2784 end update_routing_stage;
2785
2786
2787 procedure StartWorkflow (
2788 p_api_version_number IN NUMBER,
2789 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2790 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2791 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2792 p_itemKey IN VARCHAR2,
2793 p_itemType IN VARCHAR2,
2794 p_creating_username IN VARCHAR2,
2795 p_attrib_values_rec IN attrib_values_rec_type,
2796 x_return_status OUT NOCOPY VARCHAR2,
2797 x_msg_count OUT NOCOPY NUMBER,
2798 x_msg_data OUT NOCOPY VARCHAR2) is
2799
2800 l_api_name CONSTANT VARCHAR2(30) := 'StartWorkflow';
2801 l_api_version_number CONSTANT NUMBER := 1.0;
2802
2803 l_role_name varchar2(30);
2804 l_email_enabled varchar2(30);
2805 l_vendor_respond_URL varchar2(200);
2806 l_pt_respond_URL varchar2(200);
2807
2808
2809 begin
2810 -- Standard call to check for call compatibility.
2811
2812 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
2813 p_api_version_number,
2814 l_api_name,
2815 G_PKG_NAME) THEN
2816 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2817
2818 END IF;
2819
2820 -- Initialize message list if p_init_msg_list is set to TRUE.
2821 IF FND_API.to_Boolean( p_init_msg_list )
2822 THEN
2823 fnd_msg_pub.initialize;
2824 END IF;
2825
2826 -- Debug Message
2827 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
2828 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
2829 fnd_message.Set_Token('TEXT', 'In ' || l_api_name || '. Itemtype: ' || p_itemtype || '. Itemkey: ' || p_itemkey);
2830 fnd_msg_pub.Add;
2831 END IF;
2832
2833 -- Initialize API return status to success
2834 x_return_status := FND_API.G_RET_STS_SUCCESS;
2835
2836 -- Create Assignment Process
2837 IF p_attrib_values_rec.process_name = pv_workflow_pub.g_wf_pcs_initiate_assignment THEN
2838 wf_engine.CreateProcess ( ItemType => p_itemtype,
2839 ItemKey => p_itemkey,
2840 process => pv_workflow_pub.g_wf_pcs_initiate_assignment);
2841
2842 wf_engine.SetItemUserKey (itemType => p_itemtype,
2843 itemKey => p_itemkey,
2844 userKey => p_itemkey);
2845
2846 -- Setting Org Type Attribute
2847 wf_engine.SetItemAttrText ( itemtype => p_itemtype,
2848 itemkey => p_itemkey,
2849 aname => pv_workflow_pub.g_wf_attr_organization_type,
2850 avalue => p_attrib_values_rec.org_type);
2851
2852 -- Setting Partner Id attribute
2853 wf_engine.SetItemAttrText ( itemtype => p_itemtype,
2854 itemkey => p_itemkey,
2855 aname => pv_workflow_pub.g_wf_attr_ext_org_party_id,
2856 avalue => p_attrib_values_rec.pt_org_party_id);
2857
2858 -- Setting bypass_cm_ok_flag attribute
2859 wf_engine.SetItemAttrText ( itemtype => p_itemtype,
2860 itemkey => p_itemkey,
2861 aname => pv_workflow_pub.g_wf_attr_bypass_cm_approval,
2862 avalue => p_attrib_values_rec.bypass_cm_ok_flag);
2863 -- Setting customer_id attribute
2864 wf_engine.SetItemAttrNumber ( itemtype => p_itemtype,
2865 itemkey => p_itemkey,
2866 aname => pv_workflow_pub.g_wf_attr_customer_id,
2867 avalue => p_attrib_values_rec.customer_id);
2868 -- Setting Address Id attribute
2869 wf_engine.SetItemAttrNumber( itemtype => p_itemtype,
2870 itemkey => p_itemkey,
2871 aname => pv_workflow_pub.g_wf_attr_address_id,
2872 avalue => p_attrib_values_rec.address_id);
2873
2874 l_vendor_respond_url := fnd_profile.value('PV_WORKFLOW_RESPOND_SELF_SERVICE_URL');
2875 l_pt_respond_url := fnd_profile.value('PV_WORKFLOW_ISTORE_URL');
2876
2877 -- Setting CM Respond URL Attribute
2878 wf_engine.SetItemAttrText ( itemtype => p_itemType,
2879 itemkey => p_itemKey,
2880 aname => pv_workflow_pub.g_wf_attr_cm_respond_url,
2881 avalue => l_vendor_respond_URL);
2882
2883 -- Setting Partner Respond URL Attribute
2884 wf_engine.SetItemAttrText ( itemtype => p_itemType,
2885 itemkey => p_itemKey,
2886 aname => 'PV_PT_RESPOND_URL_ATTR',
2887 avalue => l_pt_respond_URL);
2888 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
2889 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
2890 fnd_message.Set_Token('TEXT', 'process rule id in the if of workflow set'|| p_attrib_values_rec.process_rule_id);
2891 fnd_msg_pub.Add;
2892 END IF;
2893
2894 -- Setting Process Rule ID Attribute
2895 wf_engine.SetItemAttrNumber( itemtype => p_itemType,
2896 itemkey => p_itemKey,
2897 aname => pv_workflow_pub.g_wf_attr_process_rule_id,
2898 avalue => p_attrib_values_rec.process_rule_id);
2899
2900
2901 -- Channel Manager Withdrawing Active Opportunity
2902 ELSIF p_attrib_values_rec.process_name = pv_workflow_pub.g_wf_pcs_withdraw_fyi THEN
2903 wf_engine.CreateProcess ( ItemType => p_itemtype,
2904 ItemKey => p_itemkey,
2905 process => pv_workflow_pub.g_wf_pcs_withdraw_fyi);
2906 END IF;
2907
2908 wf_engine.SetItemAttrNumber( itemtype => p_itemtype,
2909 itemkey => p_itemkey,
2910 aname => pv_workflow_pub.g_wf_attr_opportunity_id,
2911 avalue => p_attrib_values_rec.lead_id);
2912
2913 wf_engine.SetItemAttrText ( itemtype => p_itemtype,
2914 itemkey => p_itemkey,
2915 aname => pv_workflow_pub.g_wf_attr_entity_name,
2916 avalue => p_attrib_values_rec.entity_name);
2917
2918 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
2919 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
2920 fnd_message.Set_Token('TEXT', 'in startworkflow Entity Amount'||p_attrib_values_rec.entity_amount);
2921 fnd_msg_pub.Add;
2922 END IF;
2923 wf_engine.SetItemAttrText( itemtype => p_itemtype,
2924 itemkey => p_itemkey,
2925 aname => pv_workflow_pub.g_wf_attr_entity_amount,
2926 avalue => p_attrib_values_rec.entity_amount);
2927
2928
2929 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
2930 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
2931 fnd_message.Set_Token('TEXT', 'after the entity amount');
2932 fnd_msg_pub.Add;
2933 END IF;
2934
2935 wf_engine.SetItemAttrText ( itemtype => p_itemtype,
2936 itemkey => p_itemkey,
2937 aname => pv_workflow_pub.g_wf_attr_opp_number,
2938 avalue => p_attrib_values_rec.lead_number);
2939
2940 wf_engine.SetItemAttrText ( itemtype => p_itemtype,
2941 itemkey => p_itemkey,
2942 aname => pv_workflow_pub.g_wf_attr_customer_name,
2943 avalue => p_attrib_values_rec.customer_name);
2944
2945 wf_engine.SetItemAttrText (itemtype => p_itemType,
2946 itemkey => p_itemKey,
2947 aname => pv_workflow_pub.g_wf_attr_assignment_type,
2948 avalue => p_attrib_values_rec.assignment_type);
2949
2950 l_email_enabled := nvl(fnd_profile.value('PV_EMAIL_NOTIFICATION_FLAG'), pv_workflow_pub.g_wf_lkup_yes);
2951
2952 wf_engine.SetItemAttrText ( itemtype => p_itemType,
2953 itemkey => p_itemKey,
2954 aname => pv_workflow_pub.g_wf_attr_email_enabled,
2955 avalue => l_email_enabled);
2956
2957
2958
2959 wf_engine.StartProcess( itemtype => p_itemtype,
2960 itemkey => p_itemkey);
2961
2962
2963 IF FND_API.To_Boolean ( p_commit ) THEN
2964 COMMIT WORK;
2965 END IF;
2966
2967 -- Standard call to get message count and if count is 1, get message info.
2968 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_TRUE,
2969 p_count => x_msg_count,
2970 p_data => x_msg_data);
2971 EXCEPTION
2972
2973 WHEN FND_API.G_EXC_ERROR THEN
2974
2975 x_return_status := FND_API.G_RET_STS_ERROR ;
2976 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_TRUE,
2977 p_count => x_msg_count,
2978 p_data => x_msg_data);
2979
2980 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2981
2982 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2983 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_TRUE,
2984 p_count => x_msg_count,
2985 p_data => x_msg_data);
2986
2987 WHEN OTHERS THEN
2988
2989 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2990 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
2991 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_TRUE,
2992 p_count => x_msg_count,
2993 p_data => x_msg_data);
2994 end StartWorkflow;
2995
2996
2997 procedure validateResponse (
2998 p_api_version_number IN NUMBER
2999 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
3000 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
3001 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
3002 ,p_response_code IN VARCHAR2
3003 ,p_routing_status IN VARCHAR2
3004 ,p_decision_maker_flag IN VARCHAR2
3005 ,p_notify_type IN VARCHAR2
3006 ,x_msg_count OUT NOCOPY NUMBER
3007 ,x_msg_data OUT NOCOPY VARCHAR2
3008 ,x_return_status OUT NOCOPY VARCHAR2) is
3009
3010 cursor lc_code_meaning (pc_lookup_type varchar2,
3011 pc_lookup_code varchar2) is
3012 select meaning
3013 from pv_lookups
3014 where lookup_type = pc_lookup_type
3015 and lookup_code = pc_lookup_code;
3016
3017 l_routing_status varchar2(300);
3018 l_response_txt varchar2(500);
3019 l_routing_status_txt varchar2(500);
3020
3021 l_api_name CONSTANT VARCHAR2(30) := 'validateResponse';
3022 l_api_version_number CONSTANT NUMBER := 1.0;
3023
3024 begin
3025
3026 -- Standard call to check for call compatibility.
3027
3028 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
3029 p_api_version_number,
3030 l_api_name,
3031 G_PKG_NAME) THEN
3032 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3033
3034 END IF;
3035
3036 -- Initialize message list if p_init_msg_list is set to TRUE.
3037 IF FND_API.to_Boolean( p_init_msg_list )
3038 THEN
3039 fnd_msg_pub.initialize;
3040 END IF;
3041
3042 -- Debug Message
3043 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
3044 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
3045 fnd_message.Set_Token('TEXT', 'In ' || l_api_name);
3046 fnd_msg_pub.Add;
3047 END IF;
3048
3049 x_return_status := FND_API.G_RET_STS_SUCCESS ;
3050
3051 if p_response_code is null then
3052
3053 fnd_message.SET_NAME('PV', 'PV_INVALID_RESPONSE');
3054 fnd_message.SET_TOKEN('STATUS', p_response_code);
3055 fnd_msg_pub.ADD;
3056
3057 raise FND_API.G_EXC_ERROR;
3058
3059 end if;
3060
3061 if p_decision_maker_flag <> 'Y' or p_decision_maker_flag is NULL then
3062
3063 fnd_message.set_name('PV', 'PV_NOT_DECISION_MAKER');
3064 fnd_msg_pub.ADD;
3065
3066 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3067
3068 end if;
3069
3070 open lc_code_meaning ( pc_lookup_type => 'PV_ASSIGNMENT_STATUS',
3071 pc_lookup_code => p_response_code);
3072
3073 fetch lc_code_meaning into l_response_txt;
3074 close lc_code_meaning;
3075
3076 if l_response_txt is null then
3077
3078 fnd_message.SET_NAME('PV', 'PV_INVALID_RESPONSE');
3079 fnd_message.SET_TOKEN('STATUS', p_response_code);
3080 fnd_msg_pub.ADD;
3081
3082 raise FND_API.G_EXC_ERROR;
3083 end if;
3084
3085 if p_routing_status = pv_assignment_pub.g_r_status_matched and
3086 p_notify_type = pv_assignment_pub.g_notify_type_matched_to and
3087 p_response_code in (pv_assignment_pub.g_la_status_cm_approved,
3088 pv_assignment_pub.g_la_status_cm_app_for_pt,
3089 pv_assignment_pub.g_la_status_cm_rejected,
3090 pv_assignment_pub.g_la_status_assigned) then
3091
3092 -- do not test for below because we are only validating for existing partner
3093 -- this way we will trap any errors if existing partners were set to the below
3094
3095 -- pv_assignment_pub.g_la_status_cm_added
3096 -- pv_assignment_pub.g_la_status_cm_add_app_for_pt,
3097
3098 null;
3099
3100 elsif p_routing_status = pv_assignment_pub.g_r_status_offered and
3101 p_notify_type = pv_assignment_pub.g_notify_type_offered_to and
3102 p_response_code in (pv_assignment_pub.g_la_status_pt_approved,
3103 pv_assignment_pub.g_la_status_pt_rejected,
3104 pv_assignment_pub.g_la_status_cm_app_for_pt) then
3105
3106 null;
3107
3108 elsif p_routing_status = pv_assignment_pub.g_r_status_active and
3109 p_notify_type = pv_assignment_pub.g_notify_type_offered_to and
3110 p_response_code in (pv_assignment_pub.g_la_status_pt_approved
3111 , pv_assignment_pub.g_la_status_pt_rejected
3112 , pv_assignment_pub.g_la_status_cm_app_for_pt) then
3113
3114 -- in case of joint routing is ACTIVE the moment a single partner accept
3115 null;
3116
3117 else
3118 open lc_code_meaning (pc_lookup_type => 'PV_ROUTING_STAGE',
3119 pc_lookup_code => p_routing_status);
3120
3121 fetch lc_code_meaning into l_routing_status_txt;
3122 close lc_code_meaning;
3123
3124 fnd_message.SET_NAME('PV', 'PV_INVALID_LEAD_RESPONSE');
3125 fnd_message.SET_TOKEN('P_RESPONSE', l_response_txt);
3126 fnd_message.SET_TOKEN('P_LEAD_STATUS', l_routing_status_txt);
3127 fnd_msg_pub.ADD;
3128
3129 raise FND_API.G_EXC_ERROR;
3130 end if;
3131
3132
3133 IF FND_API.To_Boolean ( p_commit ) THEN
3134 COMMIT WORK;
3135 END IF;
3136
3137 -- Standard call to get message count and if count is 1, get message info.
3138 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_TRUE,
3139 p_count => x_msg_count,
3140 p_data => x_msg_data);
3141 EXCEPTION
3142
3143 WHEN FND_API.G_EXC_ERROR THEN
3144
3145 x_return_status := FND_API.G_RET_STS_ERROR ;
3146 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_TRUE,
3147 p_count => x_msg_count,
3148 p_data => x_msg_data);
3149
3150 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3151
3152 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3153 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_TRUE,
3154 p_count => x_msg_count,
3155 p_data => x_msg_data);
3156
3157 WHEN OTHERS THEN
3158
3159 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3160 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
3161 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_TRUE,
3162 p_count => x_msg_count,
3163 p_data => x_msg_data);
3164 end validateResponse;
3165
3166
3167 procedure set_current_routing_flag (
3168 p_api_version_number IN NUMBER,
3169 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
3170 p_commit IN VARCHAR2 := FND_API.G_FALSE,
3171 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
3172 p_itemKey in varchar2,
3173 p_entity IN VARCHAR2,
3174 p_entity_id IN NUMBER,
3175 x_return_status OUT NOCOPY VARCHAR2,
3176 x_msg_count OUT NOCOPY NUMBER,
3177 x_msg_data OUT NOCOPY VARCHAR2) is
3178
3179 l_api_name CONSTANT VARCHAR2(30) := 'set_current_routing_flag';
3180 l_api_version_number CONSTANT NUMBER := 1.0;
3181
3182 begin
3183 -- Standard call to check for call compatibility.
3184
3185 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
3186 p_api_version_number,
3187 l_api_name,
3188 G_PKG_NAME) THEN
3189 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3190
3191 END IF;
3192
3193 -- Initialize message list if p_init_msg_list is set to TRUE.
3194 IF FND_API.to_Boolean( p_init_msg_list )
3195 THEN
3196 fnd_msg_pub.initialize;
3197 END IF;
3198
3199 -- Debug Message
3200 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
3201 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
3202 fnd_message.Set_Token('TEXT', 'In ' || l_api_name);
3203 fnd_msg_pub.Add;
3204 END IF;
3205
3206 x_return_status := FND_API.G_RET_STS_SUCCESS ;
3207
3208 update pv_lead_workflows
3209 set latest_routing_flag = decode(wf_item_key, p_itemkey, 'Y', 'N'),
3210 object_version_number = object_version_number + 1
3211 where lead_id = p_entity_id
3212 and entity = p_entity
3213 and latest_routing_flag = 'Y';
3214
3215 IF FND_API.To_Boolean ( p_commit ) THEN
3216 COMMIT WORK;
3217 END IF;
3218
3219 -- Standard call to get message count and if count is 1, get message info.
3220 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_TRUE,
3221 p_count => x_msg_count,
3222 p_data => x_msg_data);
3223 EXCEPTION
3224
3225 WHEN FND_API.G_EXC_ERROR THEN
3226
3227 x_return_status := FND_API.G_RET_STS_ERROR ;
3228 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_TRUE,
3229 p_count => x_msg_count,
3230 p_data => x_msg_data);
3231
3232 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3233
3234 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3235 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_TRUE,
3236 p_count => x_msg_count,
3237 p_data => x_msg_data);
3238
3239 WHEN OTHERS THEN
3240
3241 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3242 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
3243 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_TRUE,
3244 p_count => x_msg_count,
3245 p_data => x_msg_data);
3246
3247 end set_current_routing_flag;
3248
3249
3250 PROCEDURE Bulk_cr_party_notification(
3251 P_Api_Version_Number IN NUMBER,
3252 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
3253 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
3254 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
3255 P_party_notify_rec_tbl IN party_notify_rec_tbl_type,
3256 X_Return_Status OUT NOCOPY VARCHAR2,
3257 X_Msg_Count OUT NOCOPY NUMBER,
3258 X_Msg_Data OUT NOCOPY VARCHAR2
3259 )
3260 IS
3261 l_api_name CONSTANT VARCHAR2(30) := 'Bulk_Cr_party_notification';
3262 l_api_version_number CONSTANT NUMBER := 1.0;
3263
3264 l_party_notification_id number;
3265 l_party_notify_id_tbl pv_assignment_pub.g_number_table_type := pv_assignment_pub.g_number_table_type();
3266
3267 cursor lc_get_ids (pc_count number) is
3268 select pv_party_notifications_s.nextval
3269 from fnd_tables where rownum <= pc_count;
3270
3271 BEGIN
3272
3273 -- Standard call to check for call compatibility.
3274 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
3275 p_api_version_number,
3276 l_api_name,
3277 G_PKG_NAME)
3278 THEN
3279 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3280 END IF;
3281
3282 -- Initialize message list if p_init_msg_list is set to TRUE.
3283 IF FND_API.to_Boolean( p_init_msg_list )
3284 THEN
3285 FND_MSG_PUB.initialize;
3286 END IF;
3287
3288 -- Debug Message
3289 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
3290 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
3291 fnd_message.Set_Token('TEXT', 'In ' || l_api_name);
3292 fnd_msg_pub.Add;
3293 END IF;
3294
3295 -- Initialize API return status to SUCCESS
3296 x_return_status := FND_API.G_RET_STS_SUCCESS;
3297
3298 --
3299 -- API body
3300 --
3301
3302 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
3303 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
3304 fnd_message.Set_Token('TEXT', 'Bulk adding ' || p_party_Notify_rec_tbl.lead_assignment_id.count || ' rows');
3305 fnd_msg_pub.Add;
3306 END IF;
3307
3308 if p_party_Notify_rec_tbl.lead_assignment_id.count > 0 then
3309
3310 open lc_get_ids(pc_count => p_party_notify_rec_tbl.lead_assignment_id.count);
3311
3312 loop
3313 fetch lc_get_ids into l_party_notification_id;
3314 exit when lc_get_ids%notfound;
3315 l_party_notify_id_tbl.extend;
3316 l_party_notify_id_tbl(l_party_notify_id_tbl.last) := l_party_notification_id;
3317 end loop;
3318
3319 close lc_get_ids;
3320
3321 FORALL i in 1 .. p_party_notify_rec_tbl.lead_assignment_id.count
3322
3323 INSERT into pv_party_notifications (
3324 PARTY_NOTIFICATION_ID,
3325 LAST_UPDATE_DATE,
3326 LAST_UPDATED_BY,
3327 CREATION_DATE,
3328 CREATED_BY,
3329 OBJECT_VERSION_NUMBER,
3330 LAST_UPDATE_LOGIN,
3331 WF_ITEM_TYPE,
3332 WF_ITEM_KEY,
3333 NOTIFICATION_TYPE,
3334 LEAD_ASSIGNMENT_ID,
3335 USER_ID,
3336 --USER_NAME,
3337 RESOURCE_ID,
3338 RESOURCE_RESPONSE,
3339 RESPONSE_DATE,
3340 DECISION_MAKER_FLAG
3341 ) values (
3342 l_party_notify_id_tbl(i),
3343 sysdate,
3344 fnd_global.user_id,
3345 sysdate,
3346 1,
3347 fnd_global.user_id,
3348 fnd_global.conc_login_id,
3349 p_party_notify_rec_tbl.wf_item_type(i),
3350 p_party_notify_rec_tbl.wf_item_key(i),
3351 p_party_notify_rec_tbl.notification_type(i),
3352 p_party_notify_rec_tbl.lead_assignment_id(i),
3353 p_party_notify_rec_tbl.user_id(i),
3354 --p_party_notify_rec_tbl.user_name(i),
3355 p_party_notify_rec_tbl.resource_id(i),
3356 p_party_notify_rec_tbl.resource_response(i),
3357 p_party_notify_rec_tbl.response_date(i),
3358 p_party_notify_rec_tbl.decision_maker_flag(i)
3359 );
3360
3361 end if;
3362 --
3363 -- End of API body
3364 --
3365
3366 IF FND_API.To_Boolean ( p_commit ) THEN
3367 COMMIT WORK;
3368 END IF;
3369
3370 -- Standard call to get message count and if count is 1, get message info.
3371 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_TRUE,
3372 p_count => x_msg_count,
3373 p_data => x_msg_data);
3374 EXCEPTION
3375
3376 WHEN FND_API.G_EXC_ERROR THEN
3377
3378 x_return_status := FND_API.G_RET_STS_ERROR ;
3379 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_TRUE,
3380 p_count => x_msg_count,
3381 p_data => x_msg_data);
3382
3383 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3384
3385 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3386 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_TRUE,
3387 p_count => x_msg_count,
3388 p_data => x_msg_data);
3389
3390 WHEN OTHERS THEN
3391
3392 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3393 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
3394 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_FALSE,
3395 p_count => x_msg_count,
3396 p_data => x_msg_data);
3397
3398 END Bulk_cr_party_notification;
3399
3400
3401 PROCEDURE send_notification(
3402 P_Api_Version_Number IN NUMBER,
3403 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
3404 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
3405 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
3406 p_itemtype IN VARCHAR2,
3407 p_itemkey IN VARCHAR2,
3408 p_activity_id IN NUMBER,
3409 p_route_stage IN VARCHAR2,
3410 p_partner_id IN NUMBER,
3411 X_Return_Status OUT NOCOPY VARCHAR2,
3412 X_Msg_Count OUT NOCOPY NUMBER,
3413 X_Msg_Data OUT NOCOPY VARCHAR2
3414 )
3415 IS
3416 l_api_name CONSTANT VARCHAR2(30) := 'send_notification';
3417 l_api_version_number CONSTANT NUMBER := 1.0;
3418
3419 l_count pls_integer := 0;
3420 l_notify_profile varchar2(30);
3421 l_lead_id number;
3422 l_notify_pt_flag varchar2(1);
3423 l_notify_cm_flag varchar2(1);
3424 l_notify_am_flag varchar2(1);
3425 l_notify_ot_flag varchar2(1);
3426
3427 l_ignore_pt_flag varchar2(1); -- if Y, only 1 email is sent regardless of # of partners
3428 l_notify_enabled_flag varchar2(1);
3429 l_email_enabled_flag varchar2(1);
3430
3431 l_user_id number;
3432 l_resource_id number;
3433 l_partner_id number;
3434 l_partner_org varchar2(100);
3435
3436 l_assignment_type varchar2(30);
3437 l_assignment_status varchar2(30);
3438 l_username varchar2(100);
3439 l_responding_cm varchar2(100);
3440 l_reason varchar2(200);
3441 l_usertype varchar2(30);
3442 l_profile_flag varchar2(10);
3443
3444 l_role_list wf_directory.usertable;
3445 empty_role_list wf_directory.usertable;
3446 l_role_list_index number :=1;
3447
3448 l_adhoc_role varchar2(50);
3449 l_context varchar2(30);
3450 l_msg_name varchar2(30);
3451 l_group_notify_id number;
3452 l_exit_loop boolean;
3453 l_selected_pt_only boolean;
3454 l_rank number;
3455 l_assign_sequence number;
3456
3457 l_username_tbl pv_assignment_pub.g_varchar_table_type := pv_assignment_pub.g_varchar_table_type();
3458 l_usertype_tbl pv_assignment_pub.g_varchar_table_type := pv_assignment_pub.g_varchar_table_type();
3459 l_assign_status_tbl pv_assignment_pub.g_varchar_table_type := pv_assignment_pub.g_varchar_table_type();
3460 l_userid_tbl pv_assignment_pub.g_number_table_type := pv_assignment_pub.g_number_table_type();
3461 l_resourceid_tbl pv_assignment_pub.g_number_table_type := pv_assignment_pub.g_number_table_type();
3462 l_partner_id_tbl pv_assignment_pub.g_number_table_type := pv_assignment_pub.g_number_table_type();
3463
3464 cursor lc_get_notify_flags (pc_route_stage varchar2) is
3465 select
3466 nvl(b.notify_pt_flag, 'N'),
3467 nvl(b.notify_cm_flag, 'N'),
3468 nvl(b.notify_am_flag, 'N'),
3469 nvl(b.notify_others_flag, 'N'),
3470 b.enabled_flag
3471 from pv_status_notifications b
3472 where
3473 b.status_type = 'ROUTING'
3474 and b.status_code = pc_route_stage;
3475
3476 cursor lc_get_people (pc_notify_am_flag varchar2,
3477 pc_notify_cm_flag varchar2,
3478 pc_notify_pt_flag varchar2,
3479 pc_notify_ot_flag varchar2,
3480 pc_ignore_pt_flag varchar2,
3481 pc_lead_id number)
3482 is
3483 SELECT pn.user_id, pn.resource_id, usr.user_name,
3484 decode(pn.notification_type, 'MATCHED_TO', 'CM', 'PT') user_type,
3485 decode(pc_ignore_pt_flag, 'Y', 0, pa.partner_id) partner_id, pa.status
3486 FROM pv_lead_assignments pa, pv_party_notifications pn, pv_lead_workflows pw, fnd_user usr
3487 WHERE
3488 ((pn.notification_type = 'MATCHED_TO' and 'Y' = pc_notify_cm_flag)
3489 or (pn.notification_type = 'OFFERED_TO' and 'Y' = pc_notify_pt_flag))
3490 and pw.lead_id = pc_lead_id
3491 and pw.entity = 'OPPORTUNITY'
3492 and pw.latest_routing_flag = 'Y'
3493 AND pa.lead_assignment_id = pn.lead_assignment_id
3494 and pw.wf_item_type = pa.wf_item_type
3495 and pw.wf_item_key = pa.wf_item_key
3496 AND pn.user_id = usr.user_id
3497 AND sysdate between usr.start_date and nvl(usr.end_date,sysdate)
3498 union
3499 SELECT js.user_id, js.resource_id, fu.user_name,
3500 decode(pw.created_by - js.user_id,0,'AM','OT') user_type,
3501 decode(pc_ignore_pt_flag, 'Y', 0, pl.partner_id) partner_id, pl.status
3502 FROM as_accesses_all ac, jtf_rs_resource_extns js, fnd_user fu,
3503 pv_lead_workflows pw, pv_lead_assignments pl
3504 WHERE (('Y' = pc_notify_ot_flag and pw.created_by <> js.user_id)
3505 or ('Y' = pc_notify_am_flag and pw.created_by = js.user_id))
3506 AND ac.lead_id = pc_lead_id
3507 and ac.lead_id = pw.lead_id
3508 and ac.salesforce_id = js.resource_id
3509 AND js.user_id = fu.user_id
3510 and pw.entity = 'OPPORTUNITY'
3511 and pw.latest_routing_flag = 'Y'
3512 and pl.wf_item_type = pw.wf_item_type
3513 and pl.wf_item_key = pw.wf_item_key
3514 and sysdate between js.start_date_active and nvl(js.end_date_active,sysdate)
3515 AND sysdate between fu.start_date and nvl(fu.end_date,sysdate)
3516 and not exists
3517 (select 1
3518 from pv_lead_assignments pa, pv_party_notifications pv
3519 where pa.wf_item_type = pw.wf_item_type
3520 and pv.user_id <> pw.created_by
3521 and pa.wf_item_key = pw.wf_item_key
3522 AND pa.lead_assignment_id = pv.lead_assignment_id
3523 and pv.resource_id = ac.salesforce_id)
3524 order by 5,4;
3525
3526 cursor lc_get_pt_org_name (pc_partner_id number) is
3527 select pt.party_name
3528 from hz_relationships pr,
3529 hz_organization_profiles op,
3530 hz_parties pt
3531 where pr.party_id = pc_partner_id
3532 and pr.subject_table_name = 'HZ_PARTIES'
3533 and pr.object_table_name = 'HZ_PARTIES'
3534 and pr.status in ('A', 'I')
3535 and pr.object_id = op.party_id
3536 and op.internal_flag = 'Y'
3537 and op.effective_end_date is null
3538 and pr.subject_id = pt.party_id
3539 and pt.status in ('A', 'I');
3540
3541 cursor lc_get_responding_cm (pc_partner_id number,
3542 pc_itemtype varchar2,
3543 pc_itemkey varchar2,
3544 pc_response varchar2) is
3545 select c.resource_name
3546 from pv_lead_assignments a,
3547 pv_party_notifications b,
3548 jtf_rs_resource_extns_vl c
3549 where a.wf_item_type = pc_itemtype
3550 and a.wf_item_key = pc_itemkey
3551 and a.partner_id = pc_partner_id
3552 and a.lead_assignment_id = b.lead_assignment_id
3553 and b.resource_response = pc_response
3554 and b.user_id = c.user_id;
3555
3556 cursor lc_get_reason (pc_partner_id number,
3557 pc_itemtype varchar2,
3558 pc_itemkey varchar2) is
3559 select b.meaning
3560 from pv_lead_assignments a,
3561 pv_lookups b
3562 where a.wf_item_type = pc_itemtype
3563 and a.wf_item_key = pc_itemkey
3564 and a.partner_id = pc_partner_id
3565 and a.reason_code = b.lookup_code
3566 and b.lookup_type = 'PV_REASON_CODES';
3567
3568
3569 BEGIN
3570
3571 -- Standard call to check for call compatibility.
3572
3573 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
3574 p_api_version_number,
3575 l_api_name,
3576 G_PKG_NAME) THEN
3577 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3578
3579 END IF;
3580
3581 -- Initialize message list if p_init_msg_list is set to TRUE.
3582 IF FND_API.to_Boolean( p_init_msg_list )
3583 THEN
3584 fnd_msg_pub.initialize;
3585 END IF;
3586
3587 -- Debug Message
3588 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
3589 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
3590 fnd_message.Set_Token('TEXT', 'In ' || l_api_name || '. itemkey = ' || p_itemkey);
3591 fnd_msg_pub.Add;
3592 END IF;
3593
3594 x_return_status := FND_API.G_RET_STS_SUCCESS ;
3595
3596 l_assignment_type := wf_engine.GetItemAttrText(itemtype => p_itemtype,
3597 itemkey => p_itemkey,
3598 aname => pv_workflow_pub.g_wf_attr_assignment_type);
3599
3600 l_lead_id := wf_engine.GetItemAttrNumber( itemtype => p_itemtype,
3601 itemkey => p_itemkey,
3602 aname => pv_workflow_pub.g_wf_attr_opportunity_id);
3603
3604 l_email_enabled_flag := nvl(fnd_profile.value('PV_EMAIL_NOTIFICATION_FLAG'), pv_workflow_pub.g_wf_lkup_yes);
3605
3606 l_rank := wf_engine.GetItemAttrNumber( itemtype => p_itemtype,
3607 itemkey => p_itemkey,
3608 aname => pv_workflow_pub.g_wf_attr_current_serial_rank);
3609
3610
3611 if l_email_enabled_flag = 'Y' then
3612
3613 open lc_get_notify_flags(pc_route_stage => p_route_stage);
3614 fetch lc_get_notify_flags into l_notify_pt_flag, l_notify_cm_flag,
3615 l_notify_am_flag, l_notify_ot_flag, l_notify_enabled_flag;
3616
3617 close lc_get_notify_flags;
3618
3619 if l_notify_enabled_flag is NULL then
3620
3621 fnd_message.SET_NAME('PV', 'PV_DEBUG_MESSAGE');
3622 fnd_message.SET_TOKEN('TEXT', 'Cannot find routing stage: ' || p_route_stage || ' in pv_status_notifications');
3623 fnd_msg_pub.ADD;
3624 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3625
3626 end if;
3627
3628 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
3629 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
3630 fnd_message.Set_Token('TEXT', 'Lead id: ' || l_lead_id ||
3631 '. Notification for routing: ' || p_route_stage ||
3632 '. Enabled: ' || l_notify_enabled_flag);
3633 fnd_msg_pub.Add;
3634 END IF;
3635
3636 if l_notify_enabled_flag = 'Y' then
3637
3638 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
3639 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
3640 fnd_message.Set_Token('TEXT', ' Notify AM: ' || l_notify_am_flag ||
3641 ' Notify PT: ' || l_notify_pt_flag ||
3642 ' Notify CM: ' || l_notify_cm_flag ||
3643 ' Notify other: ' || l_notify_ot_flag);
3644 fnd_msg_pub.Add;
3645 END IF;
3646
3647 if p_partner_id is not null then
3648 l_selected_pt_only := true;
3649 end if;
3650
3651 if p_route_stage in (pv_assignment_pub.g_r_status_matched, pv_assignment_pub.g_r_status_recycled) then
3652 l_notify_pt_flag := 'N';
3653 end if;
3654
3655 if p_route_stage in (pv_assignment_pub.g_r_status_recycled, pv_assignment_pub.g_r_status_withdrawn) then
3656
3657 IF p_route_stage = pv_assignment_pub.g_r_status_withdrawn
3658 AND l_assignment_type = pv_workflow_pub.g_wf_lkup_serial THEN
3659 l_ignore_pt_flag := 'N';
3660 ELSE
3661 l_ignore_pt_flag := 'Y';
3662 END IF;
3663 else
3664 l_ignore_pt_flag := 'N';
3665 end if;
3666
3667
3668
3669
3670 open lc_get_people (pc_notify_am_flag => l_notify_am_flag,
3671 pc_notify_cm_flag => l_notify_cm_flag,
3672 pc_notify_pt_flag => l_notify_pt_flag,
3673 pc_notify_ot_flag => l_notify_ot_flag,
3674 pc_ignore_pt_flag => l_ignore_pt_flag,
3675 pc_lead_id => l_lead_id
3676 );
3677 loop
3678
3679 fetch lc_get_people into l_user_id, l_resource_id, l_username, l_usertype, l_partner_id, l_assignment_status;
3680 exit when lc_get_people%notfound;
3681
3682 -- bypass usertype based on some combinations. Eg. if MATCHED, notify pt should be N
3683
3684
3685
3686 loop
3687
3688 if l_assignment_status = pv_assignment_pub.g_la_status_cm_rejected and l_usertype = 'PT' then
3689 -- p_route_stage is OFFERED, we only want to send email to PTs if not cm_rejected
3690 exit;
3691
3692 elsif l_assignment_status = pv_assignment_pub.g_la_status_match_withdrawn and l_usertype = 'PT' then
3693 exit;
3694
3695 elsif l_assignment_status = pv_assignment_pub.g_la_status_lost_chance and l_usertype = 'PT' and
3696 l_assignment_type = pv_workflow_pub.g_wf_lkup_serial then
3697 exit;
3698
3699 elsif l_assignment_type = pv_workflow_pub.g_wf_lkup_serial
3700 and p_route_stage = pv_assignment_pub.g_r_status_withdrawn then
3701
3702 IF l_partner_id <> p_partner_id
3703 OR l_assignment_status = pv_assignment_pub.g_la_status_cm_rejected THEN
3704 exit;
3705 END IF;
3706
3707 elsif l_assignment_type in (pv_workflow_pub.g_wf_lkup_broadcast,
3708 pv_workflow_pub.g_wf_lkup_joint)
3709 and l_assignment_status in (pv_assignment_pub.g_la_status_cm_rejected,
3710 pv_assignment_pub.g_la_status_pt_rejected,
3711 pv_assignment_pub.g_la_status_lost_chance
3712 )
3713 and p_route_stage = pv_assignment_pub.g_r_status_withdrawn then
3714
3715 exit;
3716
3717 end if;
3718
3719 if l_selected_pt_only and l_partner_id <> p_partner_id then
3720 exit;
3721 end if;
3722
3723 if l_count <> 0 then
3724
3725 -- this works together with the l_ignore_pt_flag
3726
3727 if l_username = l_username_tbl(l_count) and
3728 l_usertype = l_usertype_tbl(l_count) and
3729 l_partner_id = l_partner_id_tbl(l_count) then
3730
3731 exit;
3732
3733 end if;
3734
3735 end if;
3736
3737 l_profile_flag := nvl(fnd_profile.value_specific(name => 'PV_' || p_route_stage || '_NOTIFY_FLAG',
3738 user_id => l_user_id), 'Y');
3739
3740 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
3741 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
3742 fnd_message.Set_Token('TEXT', 'User: ' || l_username || '. Usertype: ' || l_usertype ||
3743 '. Profile notify flag: ' || l_profile_flag || '. partner id: ' || l_partner_id);
3744 fnd_msg_pub.Add;
3745 end if;
3746
3747 if l_profile_flag = 'Y' then
3748
3749 l_count := l_count + 1;
3750
3751 l_userid_tbl.extend;
3752 l_username_tbl.extend;
3753 l_resourceid_tbl.extend;
3754 l_usertype_tbl.extend;
3755 l_partner_id_tbl.extend;
3756 l_assign_status_tbl.extend;
3757
3758 l_userid_tbl (l_count) := l_user_id;
3759 l_username_tbl (l_count) := l_username;
3760 l_resourceid_tbl (l_count) := l_resource_id;
3761 l_usertype_tbl (l_count) := l_usertype;
3762 l_partner_id_tbl (l_count) := l_partner_id;
3763 l_assign_status_tbl (l_count) := l_assignment_status;
3764
3765 end if;
3766 exit;
3767
3768 end loop;
3769
3770 end loop;
3771
3772 close lc_get_people;
3773
3774 if l_username_tbl.count > 0 then
3775
3776 l_usertype := l_usertype_tbl(1);
3777 l_partner_id := l_partner_id_tbl(1);
3778 l_assignment_status := l_assign_status_tbl(1);
3779
3780 end if;
3781 debug('Displaying user name table');
3782 for userindex in 1 .. l_username_tbl.count loop
3783 debug('l_username_tbl(' || userindex || ')::' || l_username_tbl(userindex));
3784 end loop;
3785
3786 debug('before outer loop : l_username_tbl.count = ' || l_username_tbl.count );
3787 l_role_list_index := 1;
3788
3789 for i in 1 .. l_username_tbl.count loop
3790
3791 debug('outer loop : i = ' || i || '::l_username_tbl(i)::' || l_username_tbl(i));
3792 debug('outer loop : i = ' || i || '::l_usertype_tbl(i)::' || l_usertype_tbl(i));
3793
3794 if (l_usertype_tbl(i) <> l_usertype) or (i = l_username_tbl.count) or (l_partner_id_tbl(i) <> l_partner_id) then
3795 debug(' in if (l_usertype_tbl(i) <> l_usertype) or (i = l_username_tbl.count) or (l_partner_id_tbl(i) <> l_partner_id) then' );
3796 -- when usertype changes or partner changes or at last username
3797 -- send notification for prior usertype or partner
3798
3799 l_exit_loop := true;
3800
3801 if (i = l_username_tbl.count and l_partner_id_tbl(i) = l_partner_id and l_usertype_tbl(i) = l_usertype) then
3802 debug(' in if (i = l_username_tbl.count and l_partner_id_tbl(i) = l_partner_id and l_usertype_tbl(i) = l_usertype) then ');
3803 -- last username belongs to the current partner
3804
3805 l_role_list(l_role_list_index) := l_username_tbl(i);
3806 l_role_list_index := l_role_list_index+1;
3807
3808 elsif (i = l_username_tbl.count and l_partner_id_tbl(i) <> l_partner_id) or
3809 (i = l_username_tbl.count and l_usertype_tbl(i) <> l_usertype) then
3810 debug('elsif (i = l_username_tbl.count and l_partner_id_tbl(i) <> l_partner_id) or (i = l_username_tbl.count and l_usertype_tbl(i) <> l_usertype) then ' );
3811 -- last username happens to be for a new partner or a new usertype.
3812 -- send notification for prior usertype or partner
3813 -- loop around and send notification to current partner or usertype
3814
3815 l_exit_loop := false;
3816
3817 end if;
3818
3819 loop
3820
3821 debug('innner loop : i = ' || i || '::' || l_username_tbl(i));
3822
3823 if p_route_stage = pv_assignment_pub.g_r_status_matched and
3824 l_assignment_status = pv_assignment_pub.g_la_status_assigned then
3825
3826 l_adhoc_role := 'PV' || p_itemkey || 'MATCH' || l_usertype || '_' || l_partner_id;
3827 l_msg_name := 'PV_MATCH_' || l_usertype || '_MSG';
3828
3829 elsif p_route_stage = pv_assignment_pub.g_r_status_matched and
3830 l_assignment_status = pv_assignment_pub.g_la_status_cm_rejected then
3831
3832 l_adhoc_role := 'PV' || p_itemkey || 'CMREJECT' || l_usertype || '_' || l_partner_id;
3833 l_msg_name := 'PV_CMREJECT_' || l_usertype || '_MSG';
3834
3835 elsif p_route_stage = pv_assignment_pub.g_r_status_active and
3836 l_assignment_status = pv_assignment_pub.g_la_status_lost_chance then
3837
3838 -- only for broadcast and serial
3839
3840 l_adhoc_role := 'PV' || p_itemkey || 'LOSTCHNCE' || l_usertype || '_' || l_partner_id;
3841 l_msg_name := 'PV_LOSTCHANCE_' || l_usertype || '_MSG';
3842
3843
3844 elsif p_route_stage = pv_assignment_pub.g_r_status_offered and
3845 l_assignment_status in (pv_assignment_pub.g_la_status_cm_approved,
3846 pv_assignment_pub.g_la_status_cm_bypassed,
3847 pv_assignment_pub.g_la_status_cm_timeout) then
3848
3849 l_adhoc_role := 'PV' || p_itemkey || 'OFFER' || l_usertype || '_' || l_partner_id;
3850 l_msg_name := 'PV_OFFER_' || l_usertype || '_MSG';
3851
3852 elsif p_route_stage = pv_assignment_pub.g_r_status_active and
3853 l_assignment_status in (pv_assignment_pub.g_la_status_pt_approved,
3854 pv_assignment_pub.g_la_status_cm_app_for_pt) then
3855
3856 l_adhoc_role := 'PV' || p_itemkey || 'PTAPPRVE' || l_usertype || '_' || l_partner_id;
3857 l_msg_name := 'PV_PTAPPROVE_' || l_usertype || '_MSG';
3858
3859 elsif p_route_stage = pv_assignment_pub.g_r_status_offered and
3860 l_assignment_status = pv_assignment_pub.g_la_status_pt_rejected then
3861
3862 l_adhoc_role := 'PV' || p_itemkey || 'PTREJECT' || l_usertype || '_' || l_partner_id;
3863 l_msg_name := 'PV_PTREJECT_' || l_usertype || '_MSG';
3864
3865 elsif p_route_stage = pv_assignment_pub.g_r_status_offered and
3866 l_assignment_status = pv_assignment_pub.g_la_status_pt_timeout then
3867
3868 l_adhoc_role := 'PV' || p_itemkey || 'PTTMEOUT' || l_usertype || '_' || l_partner_id;
3869 l_msg_name := 'PV_PTTIMEOUT_' || l_usertype || '_MSG';
3870
3871 elsif p_route_stage = pv_assignment_pub.g_r_status_offered and
3872 l_assignment_status = pv_assignment_pub.g_la_status_lost_chance then
3873
3874 -- only for broadcast and serial
3875
3876 l_adhoc_role := 'PV' || p_itemkey || 'LOSTCHNCE' || l_usertype || '_' || l_partner_id;
3877 l_msg_name := 'PV_LOSTCHANCE_' || l_usertype || '_MSG';
3878
3879
3880 elsif p_route_stage = pv_assignment_pub.g_r_status_withdrawn and
3881 l_assignment_status = pv_assignment_pub.g_la_status_match_withdrawn then
3882
3883 l_adhoc_role := 'PV' || p_itemkey || 'MTCHWHDRW' || l_usertype || '_' || l_partner_id;
3884 l_msg_name := 'PV_MTCHWITHDRAW_' || l_usertype || '_MSG';
3885
3886 elsif p_route_stage = pv_assignment_pub.g_r_status_withdrawn and
3887 l_assignment_status = pv_assignment_pub.g_la_status_offer_withdrawn then
3888
3889 l_adhoc_role := 'PV' || p_itemkey || 'OFFRWHDRW' || l_usertype || '_' || l_partner_id;
3890 l_msg_name := 'PV_OFFRWITHDRAW_' || l_usertype || '_MSG';
3891 -- check
3892 elsif p_route_stage = pv_assignment_pub.g_r_status_withdrawn and
3893 l_assignment_status = pv_assignment_pub.g_la_status_active_withdrawn then
3894
3895 l_adhoc_role := 'PV' || p_itemkey || 'ACTIVEWHDRW' || l_usertype || '_' || l_partner_id;
3896 l_msg_name := 'PV_ACTIVEWHDRW_' || l_usertype || '_MSG';
3897
3898 elsif p_route_stage = pv_assignment_pub.g_r_status_recycled and
3899 l_assignment_status = pv_assignment_pub.g_la_status_cm_rejected then
3900
3901 l_adhoc_role := 'PV' || p_itemkey || 'MTCHRYCLE' || l_usertype || '_' || l_partner_id;
3902 l_msg_name := 'PV_MTCHRECYCLE_' || l_usertype || '_MSG';
3903
3904
3905 elsif p_route_stage = pv_assignment_pub.g_r_status_recycled and
3906 l_assignment_status in (pv_assignment_pub.g_la_status_pt_rejected,
3907 pv_assignment_pub.g_la_status_pt_timeout) then
3908
3909 l_adhoc_role := 'PV' || p_itemkey || 'OFFRRYCLE' || l_usertype || '_' || l_partner_id;
3910 l_msg_name := 'PV_OFFRRECYCLE_' || l_usertype || '_MSG';
3911
3912 elsif p_route_stage = pv_assignment_pub.g_r_status_abandoned and
3913 l_assignment_status = pv_assignment_pub.g_la_status_pt_abandoned then
3914
3915 l_adhoc_role := 'PV' || p_itemkey || 'PTABNDN' || l_usertype || '_' || l_partner_id;
3916 l_msg_name := 'PV_PTABANDON_' || l_usertype || '_MSG';
3917
3918
3919 else
3920
3921 l_msg_name := null;
3922
3923 end if;
3924
3925 debug ('l_role_list.count ::' ||l_role_list.count);
3926
3927 if l_msg_name is not null then
3928
3929 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
3930 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
3931 fnd_message.Set_token('TEXT', 'Creating role: '||l_adhoc_role||' with members--- ');
3932 fnd_msg_pub.Add;
3933 END IF;
3934
3935 FOR ind in 1 .. l_role_list.count
3936 LOOP
3937 debug('roleindex:' || ind || '::' || l_role_list(ind) );
3938 END LOOP;
3939
3940 debug ('after printing role list');
3941
3942 -- Bug fix 2981795
3943 -- There is a chance under certain conditions that a role being created already exists
3944 -- In such cases this call will error out. If this call throws any error just exit out
3945 -- of the current loop and continue with creation of the other roles.
3946 BEGIN
3947 wf_directory.CreateAdHocRole2(role_name => l_adhoc_role,
3948 role_display_name => l_adhoc_role,
3949 role_users => l_role_list);
3950 EXCEPTION
3951 WHEN OTHERS THEN
3952 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
3953 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
3954 fnd_message.Set_token('TEXT', 'Did not create the role as it already exists');
3955 fnd_msg_pub.Add;
3956 END IF;
3957 EXIT;
3958 END;
3959
3960 l_context := p_itemtype || ':' || p_itemkey || ':';
3961
3962 if l_partner_org is null and l_ignore_pt_flag = 'N' then
3963
3964 open lc_get_pt_org_name ( pc_partner_id => l_partner_id);
3965 fetch lc_get_pt_org_name into l_partner_org;
3966 close lc_get_pt_org_name;
3967
3968 if l_partner_org is null then
3969
3970 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
3971 fnd_message.Set_token('TEXT', 'Cannot find partner id: ' || l_partner_id);
3972 fnd_msg_pub.Add;
3973 raise FND_API.G_EXC_ERROR;
3974
3975 end if;
3976
3977 wf_engine.SetItemAttrText( itemtype => p_itemtype,
3978 itemkey => p_itemKey,
3979 aname => pv_workflow_pub.g_wf_attr_partner_org,
3980 avalue => l_partner_org);
3981
3982 if l_assignment_status = pv_assignment_pub.g_la_status_cm_rejected then
3983
3984 open lc_get_responding_cm (pc_itemtype => p_itemtype,
3985 pc_itemkey => p_itemkey,
3986 pc_partner_id => l_partner_id,
3987 pc_response => pv_assignment_pub.g_la_status_cm_rejected);
3988
3989 fetch lc_get_responding_cm into l_responding_cm;
3990 close lc_get_responding_cm;
3991
3992 wf_engine.SetItemAttrText( itemtype => p_itemtype,
3993 itemkey => p_itemKey,
3994 aname => pv_workflow_pub.g_wf_attr_responding_cm,
3995 avalue => l_responding_cm);
3996
3997 elsif l_assignment_status = pv_assignment_pub.g_la_status_pt_rejected then
3998
3999 open lc_get_reason (pc_itemtype => p_itemtype,
4000 pc_itemkey => p_itemkey,
4001 pc_partner_id => l_partner_id);
4002
4003 fetch lc_get_reason into l_reason;
4004 close lc_get_reason;
4005
4006 wf_engine.SetItemAttrText( itemtype => p_itemtype,
4007 itemkey => p_itemKey,
4008 aname => pv_workflow_pub.g_wf_attr_action_reason,
4009 avalue => l_reason);
4010 end if;
4011
4012 end if;
4013
4014 -- for joint assignment, where there is potentially multiple partners that accepted
4015 -- we need to set partner_id for the current partner so that if the current notification
4016 -- requires this information, it will have it
4017
4018 wf_engine.SetItemAttrNumber( itemtype => p_itemtype,
4019 itemkey => p_itemkey,
4020 aname => 'PV_NOTIFY_PT_ID_ATTR',
4021 avalue => l_partner_id);
4022
4023 debug('calling wf_notification.sendGroup');
4024 l_group_notify_id := wf_notification.sendGroup(
4025 role => l_adhoc_role,
4026 msg_type => 'PVASGNMT',
4027 msg_name => l_msg_name,
4028 due_date => null,
4029 callback => 'wf_engine.cb',
4030 context => l_context,
4031 send_comment => NULL,
4032 priority => NULL );
4033
4034 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
4035 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
4036 fnd_message.Set_Token('TEXT', 'Sent notification to role: ' || l_adhoc_role ||
4037 ' using message: ' || l_msg_name || '. Notify id: ' || l_group_notify_id );
4038 fnd_msg_pub.Add;
4039 end if;
4040
4041 end if;
4042
4043 if l_partner_id <> l_partner_id_tbl(i) then
4044 l_partner_org := null;
4045 end if;
4046
4047 l_usertype := l_usertype_tbl(i);
4048 l_partner_id := l_partner_id_tbl(i);
4049 l_assignment_status := l_assign_status_tbl(i);
4050
4051 l_role_list := empty_role_list;
4052 l_role_list_index := 1;
4053 l_role_list(l_role_list_index) := l_username_tbl(i);
4054 l_role_list_index := l_role_list_index + 1;
4055
4056 if l_exit_loop then
4057 exit;
4058 else
4059 l_exit_loop := true;
4060 end if;
4061
4062 end loop;
4063
4064 else
4065 debug( 'else clause' );
4066 l_role_list(l_role_list_index) := l_username_tbl(i);
4067 l_role_list_index := l_role_list_index +1;
4068 end if;
4069
4070 end loop;
4071
4072 end if; -- l_notify_enabled_flag
4073
4074 else
4075
4076 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
4077 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
4078 fnd_message.Set_Token('TEXT', 'Email is diabled at site level');
4079 fnd_msg_pub.Add;
4080 end if;
4081
4082 end if; --l_email_enabled_flag
4083
4084 IF FND_API.To_Boolean ( p_commit ) THEN
4085 COMMIT WORK;
4086 END IF;
4087
4088 -- Standard call to get message count and if count is 1, get message info.
4089 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_TRUE,
4090 p_count => x_msg_count,
4091 p_data => x_msg_data);
4092 EXCEPTION
4093
4094 WHEN FND_API.G_EXC_ERROR THEN
4095
4096 x_return_status := FND_API.G_RET_STS_ERROR ;
4097 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_TRUE,
4098 p_count => x_msg_count,
4099 p_data => x_msg_data);
4100
4101 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4102
4103 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4104 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_TRUE,
4105 p_count => x_msg_count,
4106 p_data => x_msg_data);
4107
4108 WHEN OTHERS THEN
4109 IF sqlcode = -20002 THEN
4110
4111 fnd_message.Set_Name('PV', 'PV_WF_COMP_ACTY_ERR');
4112 fnd_msg_pub.Add;
4113
4114 ELSE
4115
4116 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
4117
4118 END IF;
4119
4120 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4121
4122 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_TRUE,
4123 p_count => x_msg_count,
4124 p_data => x_msg_data);
4125
4126 end send_notification;
4127
4128 procedure AbandonWorkflow (
4129 p_api_version_number IN NUMBER,
4130 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
4131 p_commit IN VARCHAR2 := FND_API.G_FALSE,
4132 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
4133 p_creating_username IN VARCHAR2,
4134 p_attrib_values_rec IN attrib_values_rec_type,
4135 p_partner_org_name IN VARCHAR2,
4136 p_action_reason IN VARCHAR2,
4137 x_return_status OUT NOCOPY VARCHAR2,
4138 x_msg_count OUT NOCOPY NUMBER,
4139 x_msg_data OUT NOCOPY VARCHAR2) is
4140
4141 l_api_name CONSTANT VARCHAR2(30) := 'AbandonWorkflow';
4142 l_api_version_number CONSTANT NUMBER := 1.0;
4143
4144 l_role_name varchar2(30);
4145 l_email_enabled varchar2(30);
4146 l_vendor_respond_URL varchar2(100);
4147 l_pt_respond_URL varchar2(100);
4148 l_itemKey VARCHAR2(30);
4149 l_itemType VARCHAR2(30) := pv_workflow_pub.g_wf_itemtype_pvasgnmt;
4150
4151
4152 begin
4153 -- Standard call to check for call compatibility.
4154
4155 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
4156 p_api_version_number,
4157 l_api_name,
4158 G_PKG_NAME) THEN
4159 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4160
4161 END IF;
4162
4163 -- Initialize message list if p_init_msg_list is set to TRUE.
4164 IF FND_API.to_Boolean( p_init_msg_list )
4165 THEN
4166 fnd_msg_pub.initialize;
4167 END IF;
4168
4169 -- Debug Message
4170 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
4171 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
4172 fnd_message.Set_Token('TEXT', 'In ' || l_api_name || '. Itemtype: ' || l_itemtype);
4173 fnd_msg_pub.Add;
4174 END IF;
4175
4176 -- Initialize API return status to success
4177 x_return_status := FND_API.G_RET_STS_SUCCESS;
4178
4179 select pv_lead_workflows_s.nextval into l_itemkey from dual;
4180
4181
4182 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
4183 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
4184 fnd_message.Set_Token('TEXT', 'Before Creating the workflow process with Itemtype: ' || l_itemtype);
4185 fnd_msg_pub.Add;
4186 END IF;
4187
4188
4189 wf_engine.CreateProcess ( ItemType => l_itemtype,
4190 ItemKey => l_itemkey,
4191 process => pv_workflow_pub.g_wf_pcs_abandon_fyi);
4192
4193 wf_engine.SetItemAttrNumber( itemtype => l_itemtype,
4194 itemkey => l_itemkey,
4195 aname => pv_workflow_pub.g_wf_attr_opportunity_id,
4196 avalue => p_attrib_values_rec.lead_id);
4197
4198 wf_engine.SetItemAttrText ( itemtype => l_itemtype,
4199 itemkey => l_itemkey,
4200 aname => pv_workflow_pub.g_wf_attr_entity_name,
4201 avalue => p_attrib_values_rec.entity_name);
4202
4203 wf_engine.SetItemAttrText( itemtype => l_itemtype,
4204 itemkey => l_itemkey,
4205 aname => pv_workflow_pub.g_wf_attr_entity_amount,
4206 avalue => p_attrib_values_rec.entity_amount);
4207
4208 wf_engine.SetItemAttrText ( itemtype => l_itemtype,
4209 itemkey => l_itemkey,
4210 aname => pv_workflow_pub.g_wf_attr_ext_org_party_id,
4211 avalue => p_attrib_values_rec.pt_org_party_id);
4212
4213 wf_engine.SetItemAttrText ( itemtype => l_itemtype,
4214 itemkey => l_itemkey,
4215 aname => pv_workflow_pub.g_wf_attr_opp_number,
4216 avalue => p_attrib_values_rec.lead_number);
4217
4218 wf_engine.SetItemAttrText ( itemtype => l_itemtype,
4219 itemkey => l_itemkey,
4220 aname => pv_workflow_pub.g_wf_attr_customer_name,
4221 avalue => p_attrib_values_rec.customer_name);
4222
4223 wf_engine.SetItemAttrText (itemtype => l_itemtype,
4224 itemkey => l_itemkey,
4225 aname => pv_workflow_pub.g_wf_attr_assignment_type,
4226 avalue => p_attrib_values_rec.assignment_type);
4227
4228 l_vendor_respond_url := fnd_profile.value('PV_WORKFLOW_RESPOND_SELF_SERVICE_URL');
4229 l_pt_respond_url := fnd_profile.value('PV_WORKFLOW_ISTORE_URL');
4230
4231 wf_engine.SetItemAttrText ( itemtype => l_itemtype,
4232 itemkey => l_itemkey,
4233 aname => pv_workflow_pub.g_wf_attr_cm_respond_url,
4234 avalue => l_vendor_respond_URL);
4235
4236 wf_engine.SetItemAttrText ( itemtype => l_itemType,
4237 itemkey => l_itemKey,
4238 aname => 'PV_PT_RESPOND_URL_ATTR',
4239 avalue => l_pt_respond_URL);
4240
4241 wf_engine.SetItemAttrText (itemtype => l_itemtype,
4242 itemkey => l_itemkey,
4243 aname => pv_workflow_pub.g_wf_attr_action_reason,
4244 avalue => p_action_reason);
4245
4246 wf_engine.SetItemAttrText ( itemtype => l_itemtype,
4247 itemkey => l_itemkey,
4248 aname => pv_workflow_pub.g_wf_attr_partner_org,
4249 avalue => p_partner_org_name);
4250
4251 wf_engine.StartProcess( itemtype => l_itemtype,
4252 itemkey => l_itemkey);
4253
4254
4255 PV_ASSIGN_UTIL_PVT.checkforErrors ( p_api_version_number => 1.0
4256 ,p_init_msg_list => FND_API.G_FALSE
4257 ,p_commit => FND_API.G_FALSE
4258 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
4259 ,p_itemtype => l_itemtype
4260 ,p_itemkey => l_itemkey
4261 ,x_return_status => x_return_status
4262 ,x_msg_count => x_msg_count
4263 ,x_msg_data => x_msg_data);
4264
4265 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
4266 raise FND_API.G_EXC_ERROR;
4267 end if;
4268
4269 IF FND_API.To_Boolean ( p_commit ) THEN
4270 COMMIT WORK;
4271 END IF;
4272
4273 -- Standard call to get message count and if count is 1, get message info.
4274 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_TRUE,
4275 p_count => x_msg_count,
4276 p_data => x_msg_data);
4277 EXCEPTION
4278
4279 WHEN FND_API.G_EXC_ERROR THEN
4280
4281 x_return_status := FND_API.G_RET_STS_ERROR ;
4282 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_TRUE,
4283 p_count => x_msg_count,
4284 p_data => x_msg_data);
4285
4286 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4287
4288 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4289 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_TRUE,
4290 p_count => x_msg_count,
4291 p_data => x_msg_data);
4292
4293 WHEN OTHERS THEN
4294
4295 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4296 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
4297 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_TRUE,
4298 p_count => x_msg_count,
4299 p_data => x_msg_data);
4300 end AbandonWorkflow;
4301
4302
4303 --=============================================================================+
4304 --| Public Procedure |
4305 --| |
4306 --| Debug |
4307 --| |
4308 --| Parameters |
4309 --| IN |
4310 --| OUT |
4311 --| |
4312 --| |
4313 --| NOTES: |
4314 --| |
4315 --| HISTORY |
4316 --| |
4317 --==============================================================================
4318 PROCEDURE Debug(
4319 p_msg_string IN VARCHAR2
4320 )
4321 IS
4322
4323 BEGIN
4324 FND_MESSAGE.Set_Name('PV', 'PV_DEBUG_MESSAGE');
4325 FND_MESSAGE.Set_Token('TEXT', p_msg_string);
4326 FND_MSG_PUB.Add;
4327 END Debug;
4328 -- =================================End of Debug================================
4329
4330
4331 --=============================================================================+
4332 --| Public Procedure |
4333 --| |
4334 --| Set_Message |
4335 --| |
4336 --| Parameters |
4337 --| IN |
4338 --| OUT |
4339 --| |
4340 --| |
4341 --| NOTES: |
4342 --| |
4343 --| HISTORY |
4344 --| |
4345 --==============================================================================
4346 PROCEDURE Set_Message(
4347 p_msg_level IN NUMBER,
4348 p_msg_name IN VARCHAR2,
4349 p_token1 IN VARCHAR2,
4350 p_token1_value IN VARCHAR2,
4351 p_token2 IN VARCHAR2 := NULL ,
4352 p_token2_value IN VARCHAR2 := NULL,
4353 p_token3 IN VARCHAR2 := NULL,
4354 p_token3_value IN VARCHAR2 := NULL
4355 )
4356 IS
4357 BEGIN
4358 IF FND_MSG_PUB.Check_Msg_Level(p_msg_level) THEN
4359 FND_MESSAGE.Set_Name('PV', p_msg_name);
4360 FND_MESSAGE.Set_Token(p_token1, p_token1_value);
4361
4362 IF (p_token2 IS NOT NULL) THEN
4363 FND_MESSAGE.Set_Token(p_token2, p_token2_value);
4364 END IF;
4365
4366 IF (p_token3 IS NOT NULL) THEN
4367 FND_MESSAGE.Set_Token(p_token3, p_token3_value);
4368 END IF;
4369
4370 FND_MSG_PUB.Add;
4371 END IF;
4372 END Set_Message;
4373 -- ==============================End of Set_Message==============================
4374
4375 End PV_ASSIGNMENT_PVT;