[Home] [Help]
PACKAGE BODY: APPS.PV_ASSIGN_UTIL_PVT
Source
1 PACKAGE BODY PV_ASSIGN_UTIL_PVT as
2 /* $Header: pvvautlb.pls 120.9 2006/02/23 14:22:38 amaram ship $ */
3 -- Start of Comments
4
5 -- Package name : PV_ASSIGN_UTIL_PVT
6 -- Purpose :
7 -- History :
8 -- Modified: amaram 01-sep-2001 Removing the reference to ASF_DEFAULT_GROUP_ROLE. Defaulting to one of the groups
9 -- returned by Get_Salesgroup_ID Function.
10 --
11 -- NOTE :
12 -- End of Comments
13 --
14
15
16 G_PKG_NAME CONSTANT VARCHAR2(30):= 'PV_ASSIGN_UTIL_PVT';
17 G_FILE_NAME CONSTANT VARCHAR2(12) := 'pvvautlb.pls';
18
19 -- private API called by get_partner_info only
20
21 g_tap_role_channel_manager CONSTANT VARCHAR2(30) := 'CHANNEL_MANAGER';
22 g_tap_role_partner_contact CONSTANT VARCHAR2(30) := 'PARTNER_CONTACT_MEMBER';
23
24 -- -----------------------------------------------------------------------------------
25 -- Private PRocedure Declaration
26 -- -----------------------------------------------------------------------------------
27 PROCEDURE Debug(
28 p_msg_string IN VARCHAR2
29 );
30
31
32 PROCEDURE Set_Message(
33 p_msg_level IN NUMBER,
34 p_msg_name IN VARCHAR2,
35 p_token1 IN VARCHAR2,
36 p_token1_value IN VARCHAR2,
37 p_token2 IN VARCHAR2 := NULL,
38 p_token2_value IN VARCHAR2 := NULL,
39 p_token3 IN VARCHAR2 := NULL,
40 p_token3_value IN VARCHAR2 := NULL
41 );
42
43 FUNCTION Get_Salesgroup_ID (
44 p_resource_id NUMBER
45 )
46 RETURN NUMBER;
47
48 -- -----------------------------------------------------------------------------------
49 -- Code starts...
50 -- -----------------------------------------------------------------------------------
51
52 PROCEDURE removePreferedPartner (
53 p_api_version_number IN NUMBER
54 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
55 , p_commit IN VARCHAR2 := FND_API.G_FALSE
56 , p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
57 , p_lead_id IN NUMBER
58 , p_item_type IN VARCHAR2
59 , p_item_key IN VARCHAR2
60 , p_partner_id IN NUMBER
61 , x_return_status OUT NOCOPY VARCHAR2
62 , x_msg_count OUT NOCOPY NUMBER
63 , x_msg_data OUT NOCOPY VARCHAR2)
64 IS
65
66 l_api_name CONSTANT VARCHAR2(30) := 'removePreferedPartner';
67 l_api_version_number CONSTANT NUMBER := 1.0;
68
69 l_flag VARCHAR2(10);
70
71 CURSOR lc_chk_pf_pt ( pc_lead_id NUMBER
72 ,pc_partner_id NUMBER)
73 IS
74 SELECT 'X'
75 FROM as_leads_all
76 WHERE lead_id = pc_lead_id
77 AND incumbent_partner_party_id = pc_partner_id;
78
79
80 CURSOR lc_chk_pf_ass_pt ( pc_lead_id NUMBER
81 , pc_item_key VARCHAR2
82 , pc_item_type VARCHAR2)
83 IS
84 SELECT 'X'
85 FROM as_leads_all al
86 , pv_lead_assignments ass
87 WHERE al.lead_id = ass.lead_id
88 AND al.incumbent_partner_party_id = ass.partner_id
89 AND ass.wf_item_type = pc_item_type
90 AND ass.wf_item_key = pc_item_key
91 AND al.lead_id = pc_lead_id ;
92
93 BEGIN
94
95 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
96 p_api_version_number,
97 l_api_name,
98 G_PKG_NAME) THEN
99 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
100
101 END IF;
102
103 -- Initialize message list if p_init_msg_list is set to TRUE.
104 IF FND_API.to_Boolean( p_init_msg_list )
105 THEN
106 fnd_msg_pub.initialize;
107 END IF;
108
109 -- Debug Message
110 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
111 fnd_message.SET_NAME('PV', 'PV_DEBUG_MESSAGE');
112 fnd_message.Set_token('TEXT', 'In ' || l_api_name);
113 fnd_msg_pub.Add;
114 END IF;
115
116 x_return_status := FND_API.G_RET_STS_SUCCESS ;
117
118 IF p_lead_id IS NOT NULL
119 AND p_partner_id IS NOT NULL
120 THEN
121 OPEN lc_chk_pf_pt ( p_lead_id, p_partner_id);
122 FETCH lc_chk_pf_pt INTO l_flag;
123 CLOSE lc_chk_pf_pt;
124 ELSIF p_lead_id IS NOT NULL
125 AND p_item_key IS NOT NULL
126 AND p_item_type IS NOT NULL
127 THEN
128
129 OPEN lc_chk_pf_ass_pt ( p_lead_id
130 , p_item_key
131 , p_item_type);
132 FETCH lc_chk_pf_ass_pt INTO l_flag;
133 CLOSE lc_chk_pf_ass_pt;
134
135 END IF;
136
137 IF l_flag IS NOT NULL
138 AND p_lead_id IS NOT NULL
139 THEN
140
141 UPDATE as_leads_all
142 SET incumbent_partner_party_id = NULL ,
143 incumbent_partner_resource_id = NULL
144 WHERE lead_id = p_lead_id;
145
146 END IF;
147 IF FND_API.To_Boolean ( p_commit ) THEN
148 COMMIT WORK;
149 END IF;
150
151 -- Standard call to get message count and if count is 1, get message info.
152 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_TRUE,
153 p_count => x_msg_count,
154 p_data => x_msg_data);
155 EXCEPTION
156
157 WHEN FND_API.G_EXC_ERROR THEN
158
159 x_return_status := FND_API.G_RET_STS_ERROR ;
160 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_TRUE,
161 p_count => x_msg_count,
162 p_data => x_msg_data);
163
164 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
165
166 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
167 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_TRUE,
168 p_count => x_msg_count,
169 p_data => x_msg_data);
170
171 WHEN OTHERS THEN
172
173 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
174 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
175 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_TRUE,
176 p_count => x_msg_count,
177 p_data => x_msg_data);
178
179 END;
180
181
182 procedure Log_assignment_status (
183 p_api_version_number IN NUMBER
184 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
185 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
186 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
187 ,p_assignment_rec IN ASSIGNMENT_REC_TYPE
188 ,x_return_status OUT NOCOPY VARCHAR2
189 ,x_msg_count OUT NOCOPY NUMBER
190 ,x_msg_data OUT NOCOPY VARCHAR2) is
191
192 l_api_name CONSTANT VARCHAR2(30) := 'log_assignment_status';
193 l_api_version_number CONSTANT NUMBER := 1.0;
194
195 l_access_level varchar2(1) := 'V';
196 l_message_name varchar2(30);
197 l_log_params_tbl pvx_utility_pvt.log_params_tbl_type;
198
199 cursor lc_get_opp_number (pc_lead_id number) is
200 select lead_number from as_leads_all where lead_id = pc_lead_id;
201
202 l_lead_number varchar2(50);
203
204 begin
205 -- Standard call to check for call compatibility.
206
207 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
208 p_api_version_number,
209 l_api_name,
210 G_PKG_NAME) THEN
211 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
212
213 END IF;
214
215 -- Initialize message list if p_init_msg_list is set to TRUE.
216 IF FND_API.to_Boolean( p_init_msg_list )
217 THEN
218 fnd_msg_pub.initialize;
219 END IF;
220
221 -- Debug Message
222 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
223 fnd_message.SET_NAME('PV', 'PV_DEBUG_MESSAGE');
224 fnd_message.Set_token('TEXT', 'In ' || l_api_name);
225 fnd_msg_pub.Add;
226 END IF;
227
228 x_return_status := FND_API.G_RET_STS_SUCCESS ;
229
230 if p_assignment_rec.status in ( 'CM_ADDED','CM_ADD_APP_FOR_PT','UNASSIGNED') then
231 -- not used statuses. Added just in case
232 null;
233 else
234 -- all routing status messages are listed here
235 -- PV_LG_RTNG_ASSIGNED
236 -- PV_LG_RTNG_CM_APPROVED
237 -- PV_LG_RTNG_CM_APP_FOR_PT
238 -- PV_LG_RTNG_CM_BYPASSED
239 -- PV_LG_RTNG_CM_REJECTED
240 -- PV_LG_RTNG_CM_TIMEOUT
241 -- PV_LG_RTNG_LOST_CHANCE
242 -- PV_LG_RTNG_MATCH_WITHDRAWN
243 -- PV_LG_RTNG_OFFER_WITHDRAWN
244 -- PV_LG_RTNG_PT_ABANDONED
245 -- PV_LG_RTNG_PT_APPROVED
246 -- PV_LG_RTNG_PT_CREATED
247 -- PV_LG_RTNG_PT_REJECTED
248 -- PV_LG_RTNG_PT_TIMEOUT
249
250 open lc_get_opp_number(pc_lead_id => p_assignment_rec.lead_id);
251 fetch lc_get_opp_number into l_lead_number;
252 close lc_get_opp_number;
253
254 l_log_params_tbl(1).param_name := 'OPP_NUMBER';
255 l_log_params_tbl(1).param_value := l_lead_number;
256
257 l_message_name := 'PV_LG_RTNG_' || p_assignment_rec.status;
258 end if;
259
260 if l_message_name is not null then
261 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
262 fnd_message.SET_NAME('PV', 'PV_DEBUG_MESSAGE');
263 fnd_message.Set_token('TEXT', 'Logging routing message: ' || l_message_name ||
264 ' for lead_id:' || p_assignment_rec.lead_id || ' for partner_id:' || p_assignment_rec.partner_id);
265 fnd_msg_pub.Add;
266 END IF;
267
268 PVX_Utility_PVT.create_history_log(
269 p_arc_history_for_entity_code => 'OPPORTUNITY',
270 p_history_for_entity_id => p_assignment_rec.lead_id,
271 p_history_category_code => 'GENERAL',
272 p_message_code => l_message_name,
273 p_partner_id => p_assignment_rec.partner_id,
274 p_access_level_flag => l_access_level,
275 p_interaction_level => pvx_utility_pvt.G_INTERACTION_LEVEL_50,
276 p_comments => NULL,
277 p_log_params_tbl => l_log_params_tbl,
278 x_return_status => x_return_status,
279 x_msg_count => x_msg_count,
280 x_msg_data => x_msg_data);
281
282 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
283 raise FND_API.G_EXC_ERROR;
284 end if;
285 end if;
286
287 IF FND_API.To_Boolean ( p_commit ) THEN
288 COMMIT WORK;
289 END IF;
290
291 -- Standard call to get message count and if count is 1, get message info.
292 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_TRUE,
293 p_count => x_msg_count,
294 p_data => x_msg_data);
295 EXCEPTION
296
297 WHEN FND_API.G_EXC_ERROR THEN
298
299 x_return_status := FND_API.G_RET_STS_ERROR ;
300 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_TRUE,
301 p_count => x_msg_count,
302 p_data => x_msg_data);
303
304 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
305
306 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
307 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_TRUE,
308 p_count => x_msg_count,
309 p_data => x_msg_data);
310
311 WHEN OTHERS THEN
312
313 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
314 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
315 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_TRUE,
316 p_count => x_msg_count,
317 p_data => x_msg_data);
318 end;
319
320
321 PROCEDURE Create_party_notification(
322 P_Api_Version_Number IN NUMBER,
323 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
324 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
325 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
326 P_party_notify_Rec IN PV_ASSIGN_UTIL_PVT.PARTY_NOTIFY_REC_TYPE,
327 X_PARTY_NOTIFICATION_ID OUT NOCOPY NUMBER,
328 X_Return_Status OUT NOCOPY VARCHAR2,
329 X_Msg_Count OUT NOCOPY NUMBER,
330 X_Msg_Data OUT NOCOPY VARCHAR2
331 )
332
333 IS
334 l_api_name CONSTANT VARCHAR2(30) := 'Create_party_notification';
335 l_api_version_number CONSTANT NUMBER := 1.0;
336
337 CURSOR C2 IS SELECT PV_PARTY_NOTIFICATIONS_S.nextval FROM sys.dual;
338 l_party_notification_id number;
339
340 BEGIN
341
342 -- Standard call to check for call compatibility.
343 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
344 p_api_version_number,
345 l_api_name,
346 G_PKG_NAME)
347 THEN
348 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
349 END IF;
350
351 -- Initialize message list if p_init_msg_list is set to TRUE.
352 IF FND_API.to_Boolean( p_init_msg_list )
353 THEN
354 FND_MSG_PUB.initialize;
355 END IF;
356
357 -- Debug Message
358 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
359 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
360 fnd_message.Set_Token('TEXT', 'In ' || l_api_name);
361 fnd_msg_pub.Add;
362 END IF;
363
364 -- Initialize API return status to SUCCESS
365 x_return_status := FND_API.G_RET_STS_SUCCESS;
366
367 --
368 -- API body
369 --
370
371 OPEN C2;
372 FETCH C2 INTO l_party_notification_id;
373 CLOSE C2;
374
375
376 INSERT into pv_party_notifications (
377 PARTY_NOTIFICATION_ID,
378 LAST_UPDATE_DATE,
379 LAST_UPDATED_BY,
380 CREATION_DATE,
381 CREATED_BY,
382 LAST_UPDATE_LOGIN,
383 OBJECT_VERSION_NUMBER,
384 REQUEST_ID,
385 PROGRAM_APPLICATION_ID,
386 PROGRAM_ID,
387 PROGRAM_UPDATE_DATE,
388 NOTIFICATION_ID,
389 NOTIFICATION_TYPE,
390 LEAD_ASSIGNMENT_ID,
391 WF_ITEM_TYPE,
392 WF_ITEM_KEY,
393 USER_ID,
394 --USER_NAME,
395 RESOURCE_ID,
396 DECISION_MAKER_FLAG,
397 RESOURCE_RESPONSE,
398 RESPONSE_DATE,
399 ATTRIBUTE_CATEGORY,
400 ATTRIBUTE1,
401 ATTRIBUTE2,
402 ATTRIBUTE3,
403 ATTRIBUTE4,
404 ATTRIBUTE5,
405 ATTRIBUTE6,
406 ATTRIBUTE7,
407 ATTRIBUTE8,
408 ATTRIBUTE9,
409 ATTRIBUTE10,
410 ATTRIBUTE11,
411 ATTRIBUTE12,
412 ATTRIBUTE13,
413 ATTRIBUTE14,
414 ATTRIBUTE15
415 ) values (
416 l_party_notification_id,
417 sysdate,
418 fnd_global.user_id,
419 sysdate,
420 fnd_global.user_id,
421 fnd_global.conc_login_id,
422 1,
423 p_party_notify_rec.REQUEST_ID,
424 p_party_notify_rec.PROGRAM_APPLICATION_ID,
425 p_party_notify_rec.PROGRAM_ID,
426 p_party_notify_rec.PROGRAM_UPDATE_DATE,
427 p_party_notify_rec.NOTIFICATION_ID,
428 p_party_notify_rec.NOTIFICATION_TYPE,
429 p_party_notify_rec.LEAD_ASSIGNMENT_ID,
430 p_party_notify_rec.WF_ITEM_TYPE,
431 p_party_notify_rec.WF_ITEM_KEY,
432 p_party_notify_rec.USER_ID,
433 --p_party_notify_rec.USER_NAME,
434 p_party_notify_rec.RESOURCE_ID,
435 p_party_notify_rec.DECISION_MAKER_FLAG,
436 p_party_notify_rec.RESOURCE_RESPONSE,
437 p_party_notify_rec.RESPONSE_DATE,
438 p_party_notify_rec.ATTRIBUTE_CATEGORY,
439 p_party_notify_rec.ATTRIBUTE1,
440 p_party_notify_rec.ATTRIBUTE2,
441 p_party_notify_rec.ATTRIBUTE3,
442 p_party_notify_rec.ATTRIBUTE4,
443 p_party_notify_rec.ATTRIBUTE5,
444 p_party_notify_rec.ATTRIBUTE6,
445 p_party_notify_rec.ATTRIBUTE7,
446 p_party_notify_rec.ATTRIBUTE8,
447 p_party_notify_rec.ATTRIBUTE9,
448 p_party_notify_rec.ATTRIBUTE10,
449 p_party_notify_rec.ATTRIBUTE11,
450 p_party_notify_rec.ATTRIBUTE12,
451 p_party_notify_rec.ATTRIBUTE13,
452 p_party_notify_rec.ATTRIBUTE14,
453 p_party_notify_rec.ATTRIBUTE15
454 );
455
456 x_party_notification_id := l_party_notification_id;
457
458 --
459 -- End of API body
460 --
461
462 -- Standard check for p_commit
463 IF FND_API.to_Boolean( p_commit )
464 THEN
465 COMMIT WORK;
466 END IF;
467
468 -- Standard call to get message count and if count is 1, get message info.
469 FND_MSG_PUB.Count_And_Get
470 ( p_count => x_msg_count,
471 p_data => x_msg_data
472 );
473
474 EXCEPTION
475
476 WHEN FND_API.G_EXC_ERROR THEN
477
478 x_return_status := FND_API.G_RET_STS_ERROR ;
479 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_TRUE,
480 p_count => x_msg_count,
481 p_data => x_msg_data);
482
483 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
484
485 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
486 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_TRUE,
487 p_count => x_msg_count,
488 p_data => x_msg_data);
489
490 WHEN OTHERS THEN
491
492 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
493 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
494 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_FALSE,
495 p_count => x_msg_count,
496 p_data => x_msg_data);
497
498 End Create_party_notification;
499
500
501 procedure create_lead_assignment_row (
502 p_api_version_number IN NUMBER
503 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
504 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
505 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
506 ,p_assignment_rec IN ASSIGNMENT_REC_TYPE
507 ,x_lead_assignment_id OUT NOCOPY NUMBER
508 ,x_return_status OUT NOCOPY VARCHAR2
509 ,x_msg_count OUT NOCOPY NUMBER
510 ,x_msg_data OUT NOCOPY VARCHAR2) is
511
512 l_api_name CONSTANT VARCHAR2(30) := 'create_lead_assignment_row';
513 l_api_version_number CONSTANT NUMBER := 1.0;
514
515 l_lead_assignment_id number;
516
517 begin
518 -- Standard call to check for call compatibility.
519
520 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
521 p_api_version_number,
522 l_api_name,
523 G_PKG_NAME) THEN
524 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
525
526 END IF;
527
528 -- Initialize message list if p_init_msg_list is set to TRUE.
529 IF FND_API.to_Boolean( p_init_msg_list )
530 THEN
531 fnd_msg_pub.initialize;
532 END IF;
533
534 -- Debug Message
535 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
536 fnd_message.SET_NAME('PV', 'PV_DEBUG_MESSAGE');
537 fnd_message.Set_token('TEXT', 'In ' || l_api_name);
538 fnd_msg_pub.Add;
539 END IF;
540
541 x_return_status := FND_API.G_RET_STS_SUCCESS ;
542
543 if p_assignment_rec.SOURCE_TYPE not in ('CAMPAIGN', 'MATCHING', 'TAP', 'SALESTEAM') then
544 fnd_message.SET_NAME('PV', 'PV_INVALID_SOURCE_TYPE');
545 fnd_msg_pub.ADD;
546
547 raise FND_API.G_EXC_ERROR;
548 end if;
549
550 select pv_lead_assignments_s.nextval into l_Lead_assignment_ID from sys.dual;
551
552 -- Debug Message
553 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
554 fnd_message.SET_NAME('PV', 'PV_DEBUG_MESSAGE');
555 fnd_message.Set_token('TEXT', 'p_assignment_rec.wf_item_type ' || p_assignment_rec.wf_item_type||
556 'p_assignment_rec.wf_item_key ' || p_assignment_rec.wf_item_key||
557 'p_assignment_rec.lead_id ' || p_assignment_rec.lead_id||
558 'p_assignment_rec.partner_id ' || p_assignment_rec.partner_id);
559 fnd_msg_pub.Add;
560 END IF;
561
562 insert into pv_lead_assignments(
563 LEAD_ASSIGNMENT_ID,
564 LAST_UPDATE_DATE,
565 LAST_UPDATED_BY,
566 CREATION_DATE,
567 CREATED_BY,
568 LAST_UPDATE_LOGIN,
569 OBJECT_VERSION_NUMBER,
570 LEAD_ID,
571 PARTNER_ID,
572 PARTNER_ACCESS_CODE,
573 RELATED_PARTY_ID,
574 RELATED_PARTY_ACCESS_CODE,
575 ASSIGN_SEQUENCE,
576 STATUS_DATE,
577 STATUS,
578 REASON_CODE,
579 SOURCE_TYPE,
580 WF_ITEM_TYPE,
581 WF_ITEM_KEY,
582 ERROR_TXT
583 ) values (
584 l_Lead_assignment_ID,
585 sysdate,
586 fnd_global.user_id,
587 sysdate,
588 fnd_global.user_id,
589 fnd_global.conc_login_id,
590 0,
591 p_assignment_rec.LEAD_ID,
592 p_assignment_rec.PARTNER_ID,
593 p_assignment_rec.PARTNER_ACCESS_CODE,
594 p_assignment_rec.RELATED_PARTY_ID,
595 p_assignment_rec.RELATED_PARTY_ACCESS_CODE,
596 p_assignment_rec.ASSIGN_SEQUENCE,
597 p_assignment_rec.STATUS_DATE,
598 p_assignment_rec.STATUS,
599 p_assignment_rec.REASON_CODE,
600 p_assignment_rec.SOURCE_TYPE,
601 p_assignment_rec.WF_ITEM_TYPE,
602 nvl(p_assignment_rec.WF_ITEM_KEY, l_lead_assignment_id),
603 p_assignment_rec.ERROR_TXT
604 );
605
606 -- nvl(p_assignment_rec.WF_ITEM_KEY, l_lead_assignment_id),
607 -- needed for UI saving assignments. Prevents unique
608 -- violation errors. UI does not set itemtype or itemkey
609 -- there is unique index on lead_id,partner_id,wf_item_key
610
611 if p_assignment_rec.wf_item_key is not null then
612
613 Log_assignment_status (
614 p_api_version_number => 1.0,
615 p_init_msg_list => FND_API.G_FALSE,
616 p_commit => FND_API.G_FALSE,
617 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
618 p_assignment_rec => p_assignment_rec,
619 x_return_status => x_return_status,
620 x_msg_count => x_msg_count,
621 x_msg_data => x_msg_data);
622
623 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
624 raise FND_API.G_EXC_ERROR;
625 end if;
626
627 end if;
628
629 x_lead_assignment_id := l_lead_assignment_id;
630
631 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
632 fnd_message.SET_NAME('PV', 'PV_DEBUG_MESSAGE');
633 fnd_message.Set_token('TEXT', 'x_lead_assignment_id ' || x_lead_assignment_id);
634 fnd_msg_pub.Add;
635 END IF;
636
637 IF FND_API.To_Boolean ( p_commit ) THEN
638 COMMIT WORK;
639 END IF;
640
641 -- Standard call to get message count and if count is 1, get message info.
642 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_TRUE,
643 p_count => x_msg_count,
644 p_data => x_msg_data);
645 EXCEPTION
646
647 WHEN FND_API.G_EXC_ERROR THEN
648
649 x_return_status := FND_API.G_RET_STS_ERROR ;
650 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_TRUE,
651 p_count => x_msg_count,
652 p_data => x_msg_data);
653
654 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
655
656 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
657 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_TRUE,
658 p_count => x_msg_count,
659 p_data => x_msg_data);
660
661 WHEN OTHERS THEN
662
663 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
664 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
665 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_TRUE,
666 p_count => x_msg_count,
667 p_data => x_msg_data);
668
669 end create_lead_assignment_row;
670
671
672 procedure create_lead_workflow_row (
673 p_api_version_number IN NUMBER
674 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
675 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
676 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
677 ,p_workflow_rec IN LEAD_WORKFLOW_REC_TYPE
678 ,x_itemkey OUT NOCOPY VARCHAR2
679 ,x_return_status OUT NOCOPY VARCHAR2
680 ,x_msg_count OUT NOCOPY NUMBER
681 ,x_msg_data OUT NOCOPY VARCHAR2) is
682
683 l_api_name CONSTANT VARCHAR2(30) := 'create_lead_workflow_row';
684 l_api_version_number CONSTANT NUMBER := 1.0;
685
686 l_lead_workflow_id number;
687
688 CURSOR lc_get_user_type (pc_user_id NUMBER) IS
689 SELECT extn.category
690 FROM fnd_user fuser,
691 jtf_rs_resource_extns extn
692 WHERE fuser.user_id = pc_user_id
693 AND fuser.user_id = extn.user_id;
694
695 l_oppty_routing_log_rec PV_ASSIGNMENT_PVT.oppty_routing_log_rec_type;
696 l_user_category VARCHAR2(40);
697 l_user_id NUMBER;
698 BEGIN
699 -- Standard call to check for call compatibility.
700
701 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
702 p_api_version_number,
703 l_api_name,
704 G_PKG_NAME) THEN
705 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
706
707 END IF;
708
709 -- Initialize message list if p_init_msg_list is set to TRUE.
710 IF FND_API.to_Boolean( p_init_msg_list )
711 THEN
712 fnd_msg_pub.initialize;
713 END IF;
714
715 -- Debug Message
716 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
717 fnd_message.SET_NAME('PV', 'PV_DEBUG_MESSAGE');
718 fnd_message.Set_token('TEXT', 'In ' || l_api_name);
719 fnd_msg_pub.Add;
720 END IF;
721
722 x_return_status := FND_API.G_RET_STS_SUCCESS ;
723
724 select pv_lead_workflows_s.nextval into l_Lead_Workflow_ID from sys.dual;
725
726 insert into pv_lead_workflows(
727 LEAD_WORKFLOW_ID,
728 LAST_UPDATE_DATE,
729 LAST_UPDATED_BY,
730 CREATION_DATE,
731 CREATED_BY,
732 LAST_UPDATE_LOGIN,
733 OBJECT_VERSION_NUMBER,
734 LEAD_ID,
735 ENTITY,
736 WF_ITEM_TYPE,
737 WF_ITEM_KEY,
738 ROUTING_TYPE,
739 ROUTING_STATUS,
740 WF_STATUS,
741 MATCHED_DUE_DATE,
742 OFFERED_DUE_DATE,
743 BYPASS_CM_OK_FLAG,
744 LATEST_ROUTING_FLAG,
745 FAILURE_CODE,
746 FAILURE_MESSAGE
747 ) values (
748 l_Lead_Workflow_ID,
749 sysdate,
750 nvl(p_workflow_rec.last_updated_by, fnd_global.user_id),
751 sysdate,
752 nvl(p_workflow_rec.created_by, fnd_global.user_id),
753 fnd_global.conc_login_id,
754 0,
755 p_workflow_rec.Lead_ID,
756 p_workflow_rec.Entity,
757 p_workflow_rec.wf_Item_Type,
758 to_char(l_lead_workflow_id),
759 p_workflow_rec.routing_type,
760 p_workflow_rec.routing_status,
761 p_workflow_rec.wf_status,
762 null,
763 null,
764 p_workflow_rec.bypass_cm_ok_flag,
765 p_workflow_rec.latest_routing_flag,
766 p_workflow_rec.failure_code,
767 p_workflow_rec.failure_message
768 );
769
770 x_itemkey := to_char(l_lead_workflow_id);
771
772 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
773 fnd_message.SET_NAME('PV', 'PV_DEBUG_MESSAGE');
774 fnd_message.Set_token('TEXT', 'Row created in pv_lead_workflows ');
775 fnd_msg_pub.Add;
776 END IF;
777
778 IF p_workflow_rec.routing_status = PV_ASSIGNMENT_PUB.g_r_status_failed_auto THEN
779
780 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
781 fnd_message.SET_NAME('PV', 'PV_DEBUG_MESSAGE');
782 fnd_message.Set_token('TEXT', 'Logging in routing history'||p_workflow_rec.routing_type);
783 fnd_msg_pub.Add;
784 END IF;
785 l_user_id := nvl(p_workflow_rec.last_updated_by, fnd_global.user_id);
786
787 OPEN lc_get_user_type (l_user_id);
788 FETCH lc_get_user_type INTO l_user_category;
789 CLOSE lc_get_user_type;
790
791 IF l_user_category = PV_ASSIGNMENT_PUB.g_resource_employee THEN
792 l_oppty_routing_log_rec.vendor_user_id := l_user_id;
793 l_oppty_routing_log_rec.pt_contact_user_id := TO_NUMBER(NULL);
794 ELSIF l_user_category = PV_ASSIGNMENT_PUB.g_resource_party THEN
795 l_oppty_routing_log_rec.vendor_user_id := NULL;
796 l_oppty_routing_log_rec.pt_contact_user_id := l_user_id;
797 END IF;
798 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
799 fnd_message.SET_NAME('PV', 'PV_DEBUG_MESSAGE');
800 fnd_message.Set_token('TEXT', 'Logging in routing history 2');
801 fnd_msg_pub.Add;
802 END IF;
803
804 l_oppty_routing_log_rec.event := 'ASSIGN_FAIL';
805 l_oppty_routing_log_rec.lead_id := p_workflow_rec.Lead_ID;
806 l_oppty_routing_log_rec.lead_workflow_id := l_lead_workflow_id;
807 l_oppty_routing_log_rec.routing_type := p_workflow_rec.routing_type;
808 l_oppty_routing_log_rec.latest_routing_flag := p_workflow_rec.latest_routing_flag;
809 l_oppty_routing_log_rec.bypass_cm_flag := p_workflow_rec.bypass_cm_ok_flag;
810 l_oppty_routing_log_rec.lead_assignment_id := TO_NUMBER(NULL);
811 l_oppty_routing_log_rec.event_date := SYSDATE;
812 l_oppty_routing_log_rec.user_response := NULL;
813 l_oppty_routing_log_rec.reason_code := p_workflow_rec.failure_code;
814 l_oppty_routing_log_rec.user_type := 'LAM';
815
816 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
817 fnd_message.SET_NAME('PV', 'PV_DEBUG_MESSAGE');
818 fnd_message.Set_token('TEXT', 'Logging in routing history 3');
819 fnd_msg_pub.Add;
820 END IF;
821 pv_assignment_pvt.Create_Oppty_Routing_Log_Row (
822 p_api_version_number => 1.0,
823 p_init_msg_list => FND_API.G_FALSE,
824 p_commit => FND_API.G_FALSE,
825 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
826 P_oppty_routing_log_rec => l_oppty_routing_log_rec,
827 x_return_status => x_return_status,
828 x_msg_count => x_msg_count,
829 x_msg_data => x_msg_data);
830 END IF;
831 IF FND_API.To_Boolean ( p_commit ) THEN
832 COMMIT WORK;
833 END IF;
834
835 -- Standard call to get message count and if count is 1, get message info.
836 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_TRUE,
837 p_count => x_msg_count,
838 p_data => x_msg_data);
839 EXCEPTION
840
841 WHEN FND_API.G_EXC_ERROR THEN
842
843 x_return_status := FND_API.G_RET_STS_ERROR ;
844 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_TRUE,
845 p_count => x_msg_count,
846 p_data => x_msg_data);
847
848 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
849
850 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
851 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_TRUE,
852 p_count => x_msg_count,
853 p_data => x_msg_data);
854
855 WHEN OTHERS THEN
856
857 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
858 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
859 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_TRUE,
860 p_count => x_msg_count,
861 p_data => x_msg_data);
862
863 end create_lead_workflow_row;
864
865
866 procedure delete_lead_assignment_row (
867 p_api_version_number IN NUMBER
868 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
869 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
870 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
871 ,p_lead_assignment_id IN NUMBER
872 ,x_return_status OUT NOCOPY VARCHAR2
873 ,x_msg_count OUT NOCOPY NUMBER
874 ,x_msg_data OUT NOCOPY VARCHAR2) is
875
876 l_api_name CONSTANT VARCHAR2(30) := 'delete_lead_assignment_row';
877 l_api_version_number CONSTANT NUMBER := 1.0;
878
879 begin
880 -- Standard call to check for call compatibility.
881
882 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
883 p_api_version_number,
884 l_api_name,
885 G_PKG_NAME) THEN
886 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
887
888 END IF;
889
890 -- Initialize message list if p_init_msg_list is set to TRUE.
891 IF FND_API.to_Boolean( p_init_msg_list )
892 THEN
893 fnd_msg_pub.initialize;
894 END IF;
895
896 -- Debug Message
897 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
898 fnd_message.SET_NAME('PV', 'PV_DEBUG_MESSAGE');
899 fnd_message.Set_token('TEXT', 'In ' || l_api_name);
900 fnd_msg_pub.Add;
901 END IF;
902
903 x_return_status := FND_API.G_RET_STS_SUCCESS ;
904
905 delete from pv_lead_assignments
906 where lead_assignment_id = p_lead_assignment_id;
907
908 if sql%rowcount <> 1 then
909
910 -- happening because submit routing for the same oppty was selected twice before the first
911 -- routing completed and this API
912 -- was called to delete the saved partner list before invoking the createAssignment API
913 -- Do not raise an exception in this case. Bug 3088598
914
915 fnd_message.SET_NAME('PV', 'PV_DEBUG_MESSAGE');
916 fnd_message.SET_token('TEXT', 'Deleted ' || sql%rowcount || ' rows. Should have deleted 1 row');
917 fnd_msg_pub.ADD;
918
919 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
920
921 end if;
922
923 IF FND_API.To_Boolean ( p_commit ) THEN
924 COMMIT WORK;
925 END IF;
926
927 -- Standard call to get message count and if count is 1, get message info.
928 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_TRUE,
929 p_count => x_msg_count,
930 p_data => x_msg_data);
931 EXCEPTION
932
933 WHEN FND_API.G_EXC_ERROR THEN
934
935 x_return_status := FND_API.G_RET_STS_ERROR ;
936 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_TRUE,
937 p_count => x_msg_count,
938 p_data => x_msg_data);
939
940 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
941
942 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
943 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_TRUE,
944 p_count => x_msg_count,
945 p_data => x_msg_data);
946
947 WHEN OTHERS THEN
948
949 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
950 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
951 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_TRUE,
952 p_count => x_msg_count,
953 p_data => x_msg_data);
954
955 end delete_lead_assignment_row;
956
957
958 procedure get_partner_info (
959 p_api_version_number IN NUMBER,
960 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
961 p_commit IN VARCHAR2 := FND_API.G_FALSE,
962 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
963 p_mode IN VARCHAR2, -- VENDOR or EXTERNAL
964 p_partner_id IN NUMBER,
965 p_entity IN VARCHAR2, -- LEAD,OPPORTUNITY or PARTNER
966 p_entity_id IN NUMBER,
967 p_retrieve_mode IN VARCHAR2,
968 x_rs_details_tbl IN OUT NOCOPY RESOURCE_DETAILS_TBL_TYPE,
969 x_vad_id IN OUT NOCOPY NUMBER,
970 x_return_status OUT NOCOPY VARCHAR2,
971 x_msg_count OUT NOCOPY NUMBER,
972 x_msg_data OUT NOCOPY VARCHAR2) is
973
974 l_api_name CONSTANT VARCHAR2(30) := 'get_partner_info';
975 l_api_version_number CONSTANT NUMBER := 1.0;
976
977 l_rs_details_tbl_cnt pls_integer := 0;
978
979 l_pt_user_rs_id_tbl JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
980 l_pt_default_rs_id_tbl pv_assignment_pub.g_number_table_type := pv_assignment_pub.g_number_table_type();
981
982 l_pt_user_rs_id number;
983 l_partner_id number;
984 l_pt_to_vad_id number;
985
986 l_all_cm_rs_id varchar2(1500) := ' ';
987 l_cm_origin varchar2(20);
988 l_usertype varchar2(20);
989 l_person_type varchar2(20);
990 l_cm_rs_id_tbl JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
991 l_cm_origin_tbl pv_assignment_pub.g_varchar_table_type := pv_assignment_pub.g_varchar_table_type();
992
993 l_rs_id number;
994 l_person_id number;
995 l_fnd_user_id number;
996 l_fnd_username varchar2(1000);
997
998 l_indirectly_managed varchar2(1);
999 l_decision_maker_flag varchar2(1);
1000 l_person_name varchar2(1000);
1001 l_id_name varchar2(100);
1002 l_id_type varchar2(100);
1003 l_id_type_meaning varchar2(100);
1004 l_object_id number;
1005 l_pt_ok_flag boolean := TRUE;
1006
1007 cursor lc_get_person_details (pc_rs_id number) is
1008 SELECT
1009 cj.resource_id person_resource_id,
1010 cj.category user_type,
1011 cj.source_id party_id,
1012 cj.source_name name, -- cm name (use in error message)
1013 cu.user_id userid,
1014 cu.user_name logon_user -- cm fnd_user exists
1015 FROM
1016 jtf_rs_resource_extns cj,
1017 fnd_user cu
1018 WHERE
1019 cj.resource_id = pc_rs_id
1020 AND cj.user_id = cu.user_id (+)
1021 AND (cu.end_date > sysdate OR cu.end_date IS NULL);
1022
1023
1024 cursor lc_get_int_cms (pc_partner_id number) is
1025 SELECT distinct -- user could have both CM roles
1026 pt_acc.resource_id rs_id,
1027 'INTERNAL' origin
1028 FROM
1029 pv_partner_accesses PT_ACC,
1030 pv_partner_profiles PT_PROF,
1031 jtf_rs_resource_extns extn,
1032 per_all_people_f per,
1033 jtf_rs_role_relations rel,
1034 jtf_rs_roles_b role,
1035 fnd_user usr
1036 WHERE
1037 pt_acc.partner_id = pc_partner_id and
1038 pt_acc.partner_id = pt_prof.partner_id and
1039 pt_prof.status = 'A' and
1040 pt_acc.resource_id = extn.resource_id and
1041 extn.category = pv_assignment_pub.g_resource_employee and
1042 extn.source_id = per.person_id and
1043 (trunc(sysdate) between per.effective_start_date and per.effective_end_date) and
1044 extn.resource_id = rel.role_resource_id and
1045 rel.role_resource_type = 'RS_INDIVIDUAL' and
1046 (rel.end_date_active is null or rel.end_date_active > sysdate) and
1047 rel.delete_flag = 'N' and
1048 rel.role_id = role.role_id and
1049 role.role_type_code = 'PRM' and
1050 role.role_code in ('CHANNEL_MANAGER', 'CHANNEL_REP') and
1051 extn.user_id = usr.user_id and
1052 (usr.end_date > sysdate OR usr.end_date IS NULL);
1053
1054 cursor lc_get_default_cm is
1055 SELECT res.resource_id
1056 FROM jtf_rs_resource_extns res
1057 where resource_id = to_number(fnd_profile.value('PV_DEFAULT_CM'));
1058
1059 cursor lc_preferred_pt_contact (pc_partner_id number, pc_opportunity_id number) is
1060 select distinct
1061 c.resource_id
1062 from
1063 pv_partner_profiles a,
1064 hz_relationships b,
1065 jtf_rs_resource_extns c,
1066 as_accesses_all d,
1067 fnd_user usr
1068 where
1069 a.partner_id = pc_partner_id and
1070 a.partner_party_id = b.object_id and
1071 b.subject_table_name = 'HZ_PARTIES' and
1072 b.object_table_name = 'HZ_PARTIES' and
1073 b.directional_flag = 'F' and
1074 b.relationship_code = 'EMPLOYEE_OF' and
1075 b.relationship_type = 'EMPLOYMENT' and
1076 (b.end_date is null or b.end_date > sysdate) and
1077 b.status = 'A' and
1078 b.party_id = c.source_id and
1079 c.category = pv_assignment_pub.g_resource_party and
1080 sysdate between c.start_date_active and nvl(c.end_date_active,sysdate) and
1081 c.resource_id = d.salesforce_id and
1082 d.lead_id = pc_opportunity_id and
1083 c.user_id = usr.user_id and
1084 (usr.end_date > sysdate OR usr.end_date IS NULL);
1085
1086
1087 cursor lc_get_default_pt_contact (pc_partner_id number) is
1088 SELECT
1089 pj.resource_id
1090 FROM
1091 pv_partner_profiles prof,
1092 hz_relationships pr2,
1093 jtf_rs_resource_extns pj,
1094 fnd_user usr
1095 WHERE
1096 prof.partner_id = pc_partner_id
1097 and prof.partner_party_id = pr2.object_id
1098 and pr2.subject_table_name = 'HZ_PARTIES'
1099 and pr2.object_table_name = 'HZ_PARTIES'
1100 and pr2.directional_flag = 'F'
1101 and pr2.relationship_code = 'EMPLOYEE_OF'
1102 and pr2.relationship_type = 'EMPLOYMENT'
1103 and (pr2.end_date is null or pr2.end_date > sysdate)
1104 and pr2.status = 'A'
1105 and pr2.party_id = pj.source_id
1106 and pj.category = pv_assignment_pub.g_resource_party
1107 and sysdate between pj.start_date_active and nvl(pj.end_date_active,sysdate)
1108 and pj.user_id = usr.user_id
1109 and (usr.end_date > sysdate OR usr.end_date IS NULL)
1110 and exists(select 1 from jtf_auth_principal_maps jtfpm,
1111 jtf_auth_principals_b jtfp1, jtf_auth_domains_b jtfd,
1112 jtf_auth_principals_b jtfp2, jtf_auth_role_perms jtfrp,
1113 jtf_auth_permissions_b jtfperm
1114 where usr.user_name = jtfp1.principal_name
1115 and jtfp1.is_user_flag=1
1116 and jtfp1.jtf_auth_principal_id=jtfpm.jtf_auth_principal_id
1117 and jtfpm.jtf_auth_parent_principal_id = jtfp2.jtf_auth_principal_id
1118 and jtfp2.is_user_flag=0
1119 and jtfp2.jtf_auth_principal_id = jtfrp.jtf_auth_principal_id
1120 and jtfrp.positive_flag = 1
1121 and jtfrp.jtf_auth_permission_id = jtfperm.jtf_auth_permission_id
1122 and jtfperm.permission_name = 'PV_OPPTY_CONTACT'
1123 and jtfd.jtf_auth_domain_id=jtfpm.jtf_auth_domain_id
1124 and jtfd.domain_name='CRM_DOMAIN' );
1125
1126 cursor lc_id_type (pc_party_rel_id number) is
1127 select pt.party_name,
1128 ar.meaning,
1129 pr.relationship_code,
1130 pr.object_id,
1131 imp.indirectly_managed_flag
1132 from pv_partner_profiles pf,
1133 hz_relationships pr,
1134 hz_organization_profiles op,
1135 ar_lookups ar,
1136 hz_parties pt,
1137 (select distinct a.partner_id, 'Y' indirectly_managed_flag from pv_partner_accesses a, pv_partner_profiles b
1138 where a.partner_id = pc_party_rel_id
1139 and a.vad_partner_id = b.partner_id and b.status = 'A') imp
1140 where pf.partner_id = pc_party_rel_id
1141 and pf.partner_id = imp.partner_id (+)
1142 and pr.party_id = pf.partner_id
1143 and pr.subject_table_name = 'HZ_PARTIES'
1144 and pr.object_table_name = 'HZ_PARTIES'
1145 and (pr.end_date is null or pr.end_date > sysdate)
1146 and pr.status in ('A', 'I')
1147 and pr.object_id = op.party_id
1148 and op.internal_flag = 'Y'
1149 and op.effective_end_date is null
1150 and ar.lookup_type = 'PARTY_RELATIONS_TYPE'
1151 AND AR.lookup_code = pr.relationship_code
1152 and pr.subject_id = pt.party_id
1153 and pt.status in ('A', 'I');
1154
1155
1156 cursor lc_get_ext_cms (pc_partner_id number, pc_vad_id number) is
1157 SELECT distinct -- user could have both CM roles
1158 pt_acc.resource_id rs_id,
1159 'EXTERNAL' origin
1160 FROM
1161 pv_partner_accesses PT_ACC,
1162 pv_partner_profiles PT_PROF,
1163 jtf_rs_resource_extns extn,
1164 hz_relationships emp,
1165 jtf_rs_role_relations rel,
1166 jtf_rs_roles_b role
1167 where
1168 PT_ACC.partner_id = pc_partner_id and
1169 PT_ACC.vad_partner_id = pc_vad_id and
1170 PT_ACC.vad_partner_id = PT_PROF.partner_id and
1171 PT_PROF.status = 'A' and
1172 PT_ACC.resource_id = extn.resource_id and
1173 extn.category = pv_assignment_pub.g_resource_party and
1174 extn.source_id = emp.party_id and
1175 emp.subject_table_name = 'HZ_PARTIES' and
1176 emp.object_table_name = 'HZ_PARTIES' and
1177 emp.directional_flag = 'F' and
1178 emp.relationship_code = 'EMPLOYEE_OF' and
1179 emp.relationship_type = 'EMPLOYMENT' and
1180 (emp.end_date is null or emp.end_date > sysdate) and
1181 emp.status in ('A', 'I') and
1182 emp.object_id = PT_PROF.partner_party_id and
1183 extn.resource_id = rel.role_resource_id and
1184 rel.role_resource_type = 'RS_INDIVIDUAL' and
1185 (rel.end_date_active is null or rel.end_date_active > sysdate) and
1186 rel.delete_flag = 'N' and
1187 rel.role_id = role.role_id and
1188 role.role_type_code = 'PRM' and
1189 role.role_code in ('CHANNEL_MANAGER', 'CHANNEL_REP');
1190
1191
1192 begin
1193 -- Standard call to check for call compatibility.
1194 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1195 p_api_version_number,
1196 l_api_name,
1197 G_PKG_NAME) THEN
1198 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1199 END IF;
1200
1201 -- Initialize message list if p_init_msg_list is set to TRUE.
1202 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1203 fnd_msg_pub.initialize;
1204 END IF;
1205
1206 -- Debug Message
1207 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
1208 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
1209 fnd_message.Set_Token('TEXT', 'In ' || l_api_name || '. Partner id: ' || p_partner_id || '. Mode: ' || p_mode);
1210 fnd_msg_pub.Add;
1211 END IF;
1212
1213 -- Initialize API return status to success
1214 x_return_status := FND_API.G_RET_STS_SUCCESS;
1215
1216 -- check to see if partner relationship exists. Also
1217 -- get partner organization/contact name to be used in error messaging if needed
1218
1219 open lc_id_type(pc_party_rel_id => p_partner_id);
1220 fetch lc_id_type into l_id_name, l_id_type_meaning, l_id_type, l_object_id, l_indirectly_managed;
1221 close lc_id_type;
1222
1223 if l_id_name is null then
1224
1225 fnd_message.SET_NAME('PV', 'PV_BAD_ID');
1226 fnd_message.SET_TOKEN('ID' ,p_partner_id);
1227
1228 fnd_msg_pub.ADD;
1229 raise FND_API.G_EXC_ERROR;
1230
1231 end if;
1232
1233 if l_id_type <> 'PARTNER_OF' then
1234
1235 -- this means that you cannot match a partner who is not PARTNER_OF
1236 -- every indirectly managed partner will have a PARTNER_OF directly with the vendor
1237 -- the user is only allowed to pick the PARTNER_OF instead of the CUSTOMER_INDIRECTLY_MANAGED_BY relationship
1238
1239 fnd_message.SET_NAME('PV', 'PV_INVALID_PARTY_TYPE');
1240 fnd_message.SET_TOKEN('PARTY_NAME', l_id_name);
1241 fnd_message.SET_TOKEN('RELATION_TYPE', l_id_type_meaning);
1242
1243 fnd_msg_pub.ADD;
1244 raise FND_API.G_EXC_ERROR;
1245
1246 end if;
1247
1248 if p_retrieve_mode in ('BOTH', 'CM') then
1249
1250 if l_indirectly_managed = 'Y' and x_vad_id is not null then
1251
1252 -- get VAD CMs for partner first only if VAD is routing to IMP (that is x_vad_id is not null)
1253
1254 for l_rs_rec in lc_get_ext_cms(pc_partner_id => p_partner_id, pc_vad_id => x_vad_id)
1255 loop
1256
1257 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
1258 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
1259 fnd_message.Set_Token('TEXT', 'CM rs id: ' || l_rs_rec.rs_id || ' from ' || l_rs_rec .origin);
1260 fnd_msg_pub.Add;
1261 END IF;
1262
1263 if instr(l_all_cm_rs_id, ' ' || l_rs_rec.rs_id || ' ') = 0 then
1264
1265 l_all_cm_rs_id := l_all_cm_rs_id || ' ' || l_rs_rec.rs_id || ' ';
1266
1267 l_cm_rs_id_tbl.extend;
1268 l_cm_rs_id_tbl(l_cm_rs_id_tbl.last) := l_rs_rec.rs_id;
1269 l_cm_origin_tbl.extend;
1270 l_cm_origin_tbl(l_cm_origin_tbl.last) := l_rs_rec.origin;
1271
1272 else
1273 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
1274 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
1275 fnd_message.Set_Token('TEXT', 'cm resource id: ' || l_rs_rec.rs_id || ' already selected' );
1276 fnd_msg_pub.Add;
1277 END IF;
1278 end if;
1279
1280 end loop;
1281
1282 end if;
1283
1284 for l_rs_rec in lc_get_int_cms(pc_partner_id => p_partner_id)
1285 loop
1286
1287 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
1288 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
1289 fnd_message.Set_Token('TEXT', 'CM rs id: ' || l_rs_rec.rs_id || ' from ' || l_rs_rec .origin);
1290 fnd_msg_pub.Add;
1291 END IF;
1292
1293 if instr(l_all_cm_rs_id, ' ' || l_rs_rec.rs_id || ' ') = 0 then
1294
1295 l_all_cm_rs_id := l_all_cm_rs_id || ' ' || l_rs_rec.rs_id || ' ';
1296
1297 l_cm_rs_id_tbl.extend;
1298 l_cm_rs_id_tbl(l_cm_rs_id_tbl.last) := l_rs_rec.rs_id;
1299 l_cm_origin_tbl.extend;
1300 l_cm_origin_tbl(l_cm_origin_tbl.last) := l_rs_rec.origin;
1301
1302 else
1303 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
1304 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
1305 fnd_message.Set_Token('TEXT', 'cm resource id: ' || l_rs_rec.rs_id || ' already selected' );
1306 fnd_msg_pub.Add;
1307 END IF;
1308 end if;
1309
1310 end loop;
1311
1312
1313 if l_cm_rs_id_tbl.count = 0 then
1314
1315 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
1316 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
1317 fnd_message.Set_Token('TEXT', 'No CM found in PARTNER TEAM, Trying PV_DEFAULT_CM');
1318 fnd_msg_pub.Add;
1319 END IF;
1320
1321 -- no cm found. Look for default cm from profile
1322
1323 l_rs_id := null;
1324
1325 open lc_get_default_cm;
1326 fetch lc_get_default_cm into l_rs_id;
1327 close lc_get_default_cm;
1328
1329 if l_rs_id is null then
1330
1331 fnd_message.SET_NAME('PV', 'PV_NO_CM_FOR_PT');
1332 fnd_message.SET_TOKEN('P_PARTNER' ,l_id_name);
1333
1334 fnd_msg_pub.ADD;
1335 raise FND_API.G_EXC_ERROR;
1336
1337 end if;
1338
1339 l_cm_rs_id_tbl.extend;
1340 l_cm_rs_id_tbl(l_cm_rs_id_tbl.last) := l_rs_id;
1341 l_cm_origin_tbl.extend;
1342 l_cm_origin_tbl(l_cm_origin_tbl.last) := 'DEFAULT';
1343
1344 end if;
1345
1346 end if;
1347
1348 for i in 1 .. l_cm_rs_id_tbl.count loop
1349
1350 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
1351 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
1352 fnd_message.Set_Token('TEXT', 'Validating cm rs id: ' || l_cm_rs_id_tbl(i));
1353 fnd_msg_pub.Add;
1354 END IF;
1355
1356 l_rs_id := null;
1357
1358 open lc_get_person_details ( pc_rs_id => l_cm_rs_id_tbl(i) );
1359 fetch lc_get_person_details into l_rs_id, l_usertype, l_person_id, l_person_name, l_fnd_user_id, l_fnd_username;
1360 close lc_get_person_details;
1361
1362 if l_rs_id is null then
1363
1364 fnd_message.SET_NAME('PV', 'PV_CM_INVALID_RESOURCE_ID');
1365 fnd_message.SET_TOKEN('P_RESOURCE_ID' ,l_cm_rs_id_tbl(i));
1366 fnd_msg_pub.ADD;
1367 l_pt_ok_flag := false;
1368
1369 elsif l_fnd_username is null then
1370
1371 fnd_message.SET_NAME('PV', 'PV_NO_LOGON_ACCT');
1372 fnd_message.SET_TOKEN('P_USER' ,l_person_name);
1373 fnd_msg_pub.ADD;
1374 l_pt_ok_flag := false;
1375
1376 end if;
1377
1378 if l_pt_ok_flag then
1379
1380 if l_cm_origin_tbl(i) = 'DEFAULT' then
1381
1382 l_decision_maker_flag := 'Y';
1383
1384 elsif l_usertype = pv_assignment_pub.g_resource_employee then
1385
1386 -- ER 3028478
1387 -- this is to handle the case where an indirectly managed partner is managed by multiple
1388 -- VADs (possible in 11.5.10). In this case if an oppty is routed to a IMP, we will not
1389 -- know which VAD should approve the routing. So we are changing the behavior so that
1390 -- the vendor CM of the IMP is always the one to approve, not the VAD CM of the IMP
1391
1392 l_decision_maker_flag := 'Y';
1393
1394 else
1395 l_decision_maker_flag := 'N';
1396 end if;
1397
1398 x_rs_details_tbl.extend;
1399 l_rs_details_tbl_cnt := l_rs_details_tbl_cnt + 1;
1400
1401 x_rs_details_tbl(l_rs_details_tbl_cnt).notification_type := pv_assignment_pub.g_notify_type_matched_to;
1402 x_rs_details_tbl(l_rs_details_tbl_cnt).user_id := l_fnd_user_id;
1403 x_rs_details_tbl(l_rs_details_tbl_cnt).person_id := l_person_id;
1404 x_rs_details_tbl(l_rs_details_tbl_cnt).person_type := l_usertype;
1405 x_rs_details_tbl(l_rs_details_tbl_cnt).decision_maker_flag := l_decision_maker_flag;
1406 x_rs_details_tbl(l_rs_details_tbl_cnt).user_name := l_fnd_username;
1407 x_rs_details_tbl(l_rs_details_tbl_cnt).resource_id := l_rs_id;
1408
1409 end if;
1410
1411 end loop;
1412
1413 if p_retrieve_mode in ('BOTH','CM') and fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
1414 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
1415 fnd_message.Set_Token('TEXT', 'CMs found: ' || l_cm_rs_id_tbl.count);
1416 fnd_msg_pub.Add;
1417 END IF;
1418
1419 if p_entity in ('LEAD','OPPORTUNITY') then
1420
1421 if p_retrieve_mode in ('BOTH','PT') then
1422
1423 l_all_cm_rs_id := ' ';
1424
1425 -- see if there are any preferred partner contact
1426
1427 open lc_preferred_pt_contact(pc_partner_id => p_partner_id, pc_opportunity_id => p_entity_id);
1428 loop
1429 fetch lc_preferred_pt_contact into l_pt_user_rs_id;
1430 exit when lc_preferred_pt_contact%notfound;
1431 l_pt_user_rs_id_tbl.extend;
1432 l_pt_user_rs_id_tbl(l_pt_user_rs_id_tbl.last) := l_pt_user_rs_id;
1433 end loop;
1434 close lc_preferred_pt_contact;
1435
1436 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
1437 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
1438 fnd_message.Set_Token('TEXT', 'No. of pt contacts found in oppty salesteam: ' || l_pt_user_rs_id_tbl.count);
1439 fnd_msg_pub.Add;
1440 END IF;
1441
1442 for i in 1 .. l_pt_user_rs_id_tbl.count loop
1443 l_all_cm_rs_id := l_all_cm_rs_id || ' ' || l_pt_user_rs_id_tbl(i) || ' ';
1444 end loop;
1445
1446 -- add default contact for partner also
1447
1448 open lc_get_default_pt_contact (pc_partner_id => p_partner_id);
1449 loop
1450 fetch lc_get_default_pt_contact into l_pt_user_rs_id;
1451 exit when lc_get_default_pt_contact%notfound;
1452 l_pt_default_rs_id_tbl.extend;
1453 l_pt_default_rs_id_tbl(l_pt_default_rs_id_tbl.last) := l_pt_user_rs_id;
1454 end loop;
1455 close lc_get_default_pt_contact;
1456
1457 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
1458 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
1459 fnd_message.Set_Token('TEXT', 'No. of pt contacts with PV_OPPTY_CONTACT permission that has resource and' ||
1460 ' Valid login : ' || l_pt_default_rs_id_tbl.count);
1461 fnd_msg_pub.Add;
1462 END IF;
1463
1464 if l_pt_user_rs_id_tbl.count = 0 and l_pt_default_rs_id_tbl.count = 0 then
1465
1466 -- no partner contacts found
1467
1468 fnd_message.SET_NAME('PV', 'PV_NO_CNTCT_FOR_PT');
1469 fnd_message.SET_TOKEN('P_PARTNER' , l_id_name);
1470 fnd_msg_pub.ADD;
1471 l_pt_ok_flag := false;
1472
1473 end if;
1474
1475 for i in 1 .. l_pt_default_rs_id_tbl.count loop
1476
1477 if instr(l_all_cm_rs_id, ' ' || l_pt_default_rs_id_tbl(i) || ' ') = 0 then
1478
1479 l_all_cm_rs_id := l_all_cm_rs_id || ' ' || l_pt_default_rs_id_tbl(i) || ' ';
1480
1481 l_pt_user_rs_id_tbl.extend;
1482 l_pt_user_rs_id_tbl(l_pt_user_rs_id_tbl.last) := l_pt_default_rs_id_tbl(i);
1483
1484 else
1485
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', 'Default contact: resource id already there: '||l_pt_default_rs_id_tbl(i));
1489 fnd_msg_pub.Add;
1490 END IF;
1491
1492 end if;
1493
1494 end loop;
1495
1496 for i in 1 .. l_pt_user_rs_id_tbl.count loop
1497
1498 l_rs_id := null;
1499
1500 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
1501 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
1502 fnd_message.Set_Token('TEXT', 'before retrieving the person details '|| l_pt_user_rs_id_tbl(i));
1503 fnd_msg_pub.Add;
1504 END IF;
1505
1506 open lc_get_person_details (pc_rs_id => l_pt_user_rs_id_tbl(i) );
1507 fetch lc_get_person_details into l_rs_id, l_usertype, l_person_id, l_person_name, l_fnd_user_id, l_fnd_username;
1508 close lc_get_person_details;
1509
1510 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
1511 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
1512 fnd_message.Set_Token('TEXT', 'Resource ID '||l_rs_id ||' '||l_person_name||' '|| l_fnd_username );
1513 fnd_msg_pub.Add;
1514 END IF;
1515 if l_fnd_username is null then
1516
1517 fnd_message.SET_NAME('PV', 'PV_PT_CONTACT_NO_LOGON');
1518 fnd_message.SET_TOKEN('P_PT_RESOURCE_ID' ,l_pt_user_rs_id_tbl(i));
1519 fnd_msg_pub.ADD;
1520 -- l_pt_ok_flag := false;
1521
1522 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
1523 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
1524 fnd_message.Set_Token('TEXT', 'Resource ID '||l_pt_user_rs_id_tbl(i) ||
1525 ' set to zero since the partner contact does not have login' );
1526 fnd_msg_pub.Add;
1527 END IF;
1528
1529 l_pt_user_rs_id_tbl(i) := 0;
1530
1531 end if;
1532
1533 if l_pt_ok_flag then
1534
1535 if l_pt_user_rs_id_tbl(i) <> 0 then
1536
1537 x_rs_details_tbl.extend;
1538 l_rs_details_tbl_cnt := l_rs_details_tbl_cnt + 1;
1539
1540 x_rs_details_tbl(l_rs_details_tbl_cnt).notification_type := pv_assignment_pub.g_notify_type_offered_to;
1541 x_rs_details_tbl(l_rs_details_tbl_cnt).user_id := l_fnd_user_id;
1542 x_rs_details_tbl(l_rs_details_tbl_cnt).person_id := l_person_id;
1543 x_rs_details_tbl(l_rs_details_tbl_cnt).person_type := l_usertype;
1544 x_rs_details_tbl(l_rs_details_tbl_cnt).decision_maker_flag := 'Y';
1545 x_rs_details_tbl(l_rs_details_tbl_cnt).user_name := l_fnd_username;
1546 x_rs_details_tbl(l_rs_details_tbl_cnt).resource_id := l_pt_user_rs_id_tbl(i);
1547
1548 end if;
1549 end if;
1550
1551 end loop;
1552
1553 IF l_pt_user_rs_id_tbl.count > 0 THEN
1554 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
1555 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
1556 fnd_message.Set_Token('TEXT', 'There are '||l_pt_user_rs_id_tbl.count || ' contacts found for partner '||
1557 l_id_name);
1558 fnd_msg_pub.Add;
1559 END IF;
1560 ELSE
1561 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
1562 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
1563 fnd_message.Set_Token('TEXT', 'No contacts found for partner '||l_id_name);
1564 fnd_msg_pub.Add;
1565 END IF;
1566 END IF;
1567
1568 end if; -- p_entity in 'LEAD','OPPORTUNITY'
1569
1570 end if; -- p_retrieve_mode
1571
1572 if not l_pt_ok_flag then
1573 raise FND_API.G_EXC_ERROR;
1574 end if;
1575
1576 --
1577 -- End of API body.
1578 --
1579
1580 IF FND_API.To_Boolean ( p_commit ) THEN
1581 COMMIT WORK;
1582 END IF;
1583
1584 -- Standard call to get message count and if count is 1, get message info.
1585 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_TRUE,
1586 p_count => x_msg_count,
1587 p_data => x_msg_data);
1588 EXCEPTION
1589
1590 WHEN FND_API.G_EXC_ERROR THEN
1591
1592 x_return_status := FND_API.G_RET_STS_ERROR ;
1593 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_TRUE,
1594 p_count => x_msg_count,
1595 p_data => x_msg_data);
1596
1597 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1598
1599 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1600 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_TRUE,
1601 p_count => x_msg_count,
1602 p_data => x_msg_data);
1603
1604 WHEN OTHERS THEN
1605
1606 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1607 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
1608 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_FALSE,
1609 p_count => x_msg_count,
1610 p_data => x_msg_data);
1611
1612 end get_partner_info;
1613
1614
1615
1616 --=============================================================================+
1617 --| Procedure |
1618 --| |
1619 --| UpdateAccess |
1620 --| |
1621 --| |
1622 --| Parameters |
1623 --| IN |
1624 --| OUT |
1625 --| |
1626 --| |
1627 --| NOTES |
1628 --| |
1629 --| HISTORY |
1630 --| |
1631 --==============================================================================
1632 procedure UpdateAccess
1633 ( p_api_version_number IN NUMBER,
1634 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1635 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1636 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1637 p_itemtype IN VARCHAR2,
1638 p_itemkey IN VARCHAR2,
1639 p_current_username IN VARCHAR2,
1640 p_lead_id IN NUMBER,
1641 p_customer_id IN NUMBER,
1642 p_address_id IN NUMBER,
1643 p_resource_id IN NUMBER,
1644 p_access_type IN NUMBER,
1645 p_access_action IN NUMBER,
1646 x_access_id OUT NOCOPY NUMBER,
1647 x_return_status OUT NOCOPY VARCHAR2,
1648 x_msg_count OUT NOCOPY NUMBER,
1649 x_msg_data OUT NOCOPY VARCHAR2)
1650 as
1651
1652 l_temp varchar2(100);
1653
1654 -- if the person belongs to more than 1 group, we will use the group_id in ASF_DEFAULT_GROUP_ROLE.
1655 -- else we will use the group_id from jtf_rs_group_members
1656
1657 l_get_person_info_sql varchar2(500) :=
1658 'select a.category, b.user_name, a.source_id ' ||
1659 'from jtf_rs_resource_extns a, fnd_user b ' ||
1660 'where a.resource_id = :p_resource_id and a.user_id = b.user_id ' ;
1661
1662 l_get_pt_org_info_sql varchar2(800) :=
1663 'select re.source_id from jtf_rs_resource_extns re where re.resource_id = :p_resource_id ';
1664
1665 l_api_name CONSTANT VARCHAR2(30) := 'UpdateAccess';
1666 l_api_version_number CONSTANT NUMBER := 1.0;
1667
1668 l_access_id_tbl pv_assignment_pub.g_number_table_type := pv_assignment_pub.g_number_table_type();
1669 l_prm_keep_flag_tbl pv_assignment_pub.g_varchar_table_type := pv_assignment_pub.g_varchar_table_type();
1670 l_salesforce_id_tbl pv_assignment_pub.g_number_table_type := pv_assignment_pub.g_number_table_type();
1671
1672 l_person_category varchar2(30);
1673 l_access_Id number;
1674 l_prm_keep_flag varchar2(1);
1675 l_salesforce_id number;
1676 l_sales_credit_count number;
1677 l_profile_value varchar2(50);
1678 l_non_quota_sc_id number;
1679 l_sales_grp_id_str varchar2(50);
1680 l_am_rs_id NUMBER;
1681 l_ld_owner_rs_id NUMBER;
1682 l_person_id NUMBER;
1683 l_emp_person_id NUMBER;
1684 l_pt_party_id NUMBER;
1685 l_sales_group_id NUMBER;
1686 l_pt_org_party_id NUMBER;
1687 l_access_exists_flag BOOLEAN;
1688 l_username VARCHAR2(100);
1689 l_debug_string VARCHAR2(100);
1690 l_pt_full_access_opp VARCHAR2(1);
1691 l_pt_resource_id NUMBER;
1692 l_open_flag VARCHAR2(10);
1693
1694 l_sales_team_rec AS_ACCESS_PUB.Sales_Team_Rec_Type;
1695 lc_cursor pv_assignment_pub.g_ref_cursor_type;
1696
1697 cursor lc_get_access_details (pc_salesforce_id number, pc_lead_id number) is
1698 select access_id , prm_keep_flag, salesforce_id
1699 from as_accesses_all
1700 where salesforce_id = pc_salesforce_id
1701 and lead_id = pc_lead_id;
1702
1703 cursor lc_get_am_and_owner (pc_lead_id number) is
1704 select am.resource_id, owner.resource_id
1705 from pv_lead_workflows wf, jtf_rs_resource_extns am, as_leads_all ld, jtf_rs_resource_extns owner
1706 where wf.lead_id = pc_lead_id
1707 and wf.entity = 'OPPORTUNITY'
1708 and wf.latest_routing_flag = 'Y'
1709 and wf.created_by = am.user_id
1710 and ld.lead_id = pc_lead_id
1711 and ld.created_by = owner.user_id;
1712
1713 -- ----------------------------------------------------------------------------
1714 -- Quota Sales Credits
1715 -- ----------------------------------------------------------------------------
1716 CURSOR lc_get_sales_credit_count(pc_salesforce_id NUMBER, pc_lead_id NUMBER) IS
1717 SELECT COUNT(*) sales_credit_count
1718 FROM as_sales_credits
1719 WHERE lead_id = pc_lead_id AND
1720 salesforce_id = pc_salesforce_id AND
1721 credit_type_id = 1 AND
1722 NVL(credit_amount, 0) > 0;
1723
1724 -- ----------------------------------------------------------------------------
1725 -- Non-Quota Sales Credits
1726 -- ----------------------------------------------------------------------------
1727 CURSOR lc_get_nonq_sales_credit(pc_salesforce_id NUMBER, pc_lead_id NUMBER) IS
1728 SELECT sales_credit_id
1729 FROM as_sales_credits
1730 WHERE lead_id = pc_lead_id AND
1731 salesforce_id = pc_salesforce_id AND
1732 credit_type_id = 2 AND
1733 NVL(credit_amount, 0) > 0;
1734
1735
1736 cursor lc_get_pt_access(pc_lead_id number)
1737 is
1738 SELECT pn.resource_id
1739 FROM pv_lead_workflows pw, pv_lead_assignments pa,
1740 pv_party_notifications pn
1741 WHERE pw.wf_item_key = pa.wf_item_key
1742 AND pa.lead_assignment_id = pn.lead_assignment_id
1743 AND pw.latest_routing_flag = 'Y'
1744 AND pw.lead_id = pc_lead_id
1745 AND pn.notification_type = 'OFFERED_TO';
1746
1747 cursor get_opp_open_flag_csr(pc_lead_id number) is
1748 select decode(st.opp_open_status_flag,'N',NULL,st.opp_open_status_flag)
1749 from as_leads_all ld, as_statuses_b st
1750 where ld.lead_id = pc_lead_id
1751 and ld.status = st.status_code;
1752
1753 l_current_user_rs_id NUMBER; -- resource_id of currently logged in user
1754 l_curr_user_access_profile_rec as_access_pub.access_profile_rec_type;
1755
1756
1757 /*
1758 FOr enhacement# 4092815
1759 Cursor to get sql_text of attrribute# 7,
1760 */
1761
1762 cursor lc_get_sql_text_attr_7 is
1763 SELECT sql_text
1764 FROM pv_entity_Attrs
1765 WHERE entity = 'PARTNER' and
1766 attribute_id = 7
1767 ;
1768
1769 /*
1770 For enhacement# 4092815
1771 Cursor to get partner_id of the partner organisation with given resource_id
1772 */
1773
1774 cursor lc_get_partner_id_org (
1775 pc_salesforce_id number
1776 ) is
1777 select source_id from jtf_rs_resource_extns
1778 where resource_id = pc_salesforce_id
1779 ;
1780
1781 cursor lc_get_partner_id_org_contact (
1782 pc_salesforce_id number
1783 ) is
1784
1785 SELECT pvpp.partner_id
1786 FROM hz_parties PARTNER, hz_relationships HZR_PART_CONT, hz_org_contacts ORG_CONTACT,
1787 hz_contact_points hcp, pv_partner_profiles pvpp, jtf_rs_resource_extns res
1788 WHERE
1789 PARTNER.PARTY_ID = pvpp.partner_party_id and
1790 PARTNER.party_type = 'ORGANIZATION' AND
1791 HZR_PART_CONT.object_id = PARTNER.PARTY_ID AND
1792 HZR_PART_CONT.relationship_type = 'EMPLOYMENT' AND
1793 HZR_PART_CONT.subject_table_name = 'HZ_PARTIES' AND
1794 HZR_PART_CONT.object_table_name = 'HZ_PARTIES' AND
1795 HZR_PART_CONT.PARTY_ID = res.source_id and
1796 res.resource_id = pc_salesforce_id and
1797 HZR_PART_CONT.relationship_id = ORG_CONTACT.party_relationship_id AND
1798 hcp.owner_table_id(+) = HZR_PART_CONT.PARTY_ID AND
1799 hcp.CONTACT_POINT_TYPE(+) = 'PHONE' AND
1800 hcp.owner_table_name(+) = 'HZ_PARTIES' and
1801 hcp.primary_flag(+) ='Y'
1802 ;
1803
1804 cursor lc_get_nature_of_resource (pc_access_id number) is
1805 select 'CM_OR_REP'
1806 from as_accesses_all
1807 where access_id = pc_access_id
1808 and partner_customer_id is null
1809 and partner_cont_party_id is null;
1810
1811 /*
1812 For enhacement# 4092815
1813 */
1814 l_current_partnerid_of_rel NUMBER;
1815 l_sql_text_attr_7 VARCHAR2(2000);
1816 l_enable_full_access_value VARCHAR2(30);
1817 l_resoucre_nature VARCHAR2(30) := 'PART_OR_CONT';
1818
1819 begin
1820
1821 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1822 p_api_version_number,
1823 l_api_name,
1824 G_PKG_NAME)
1825 THEN
1826 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1827 END IF;
1828
1829
1830 -- Initialize message list if p_init_msg_list is set to TRUE.
1831 IF FND_API.to_Boolean( p_init_msg_list )
1832 THEN
1833 fnd_msg_pub.initialize;
1834 END IF;
1835
1836 -- Debug Message
1837 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
1838
1839 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
1840 fnd_message.Set_Token('TEXT', 'In ' || l_api_name || ' for Lead id = ' || p_lead_id || '. Resource ID: ' || p_resource_id);
1841 fnd_msg_pub.Add;
1842
1843 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
1844 fnd_message.Set_Token('TEXT', 'p_access_type is: ' || p_access_type || ': p_access_action is :' || p_access_action || ' :');
1845 fnd_msg_pub.Add;
1846
1847
1848 select 'Access Type: '||decode(p_access_type, 1, 'CM', 2, 'PT', 3, 'PT ORG') ||
1849 ' Access Action: '||decode(p_access_action, 1, 'ADD', 2, 'REMOVE') into l_debug_string from dual;
1850
1851 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
1852 fnd_message.Set_Token('TEXT', l_debug_string);
1853 fnd_msg_pub.Add;
1854
1855 END IF;
1856
1857 -- Initialize API return status to success
1858 x_return_status := FND_API.G_RET_STS_SUCCESS;
1859
1860
1861
1862 -- salesforce may appear multiple times in access for the same opportunity
1863 -- how this happens is one may have salesgroup but the other may not.
1864
1865 open lc_get_access_details (pc_salesforce_id => p_resource_id, pc_lead_id => p_lead_id);
1866 loop
1867
1868 fetch lc_get_access_details into l_access_id, l_prm_keep_flag, l_salesforce_id;
1869 exit when lc_get_access_details%notfound;
1870
1871 l_access_id_tbl.extend;
1872 l_prm_keep_flag_tbl.extend;
1873 l_salesforce_id_tbl.extend;
1874
1875 l_access_id_tbl(l_access_id_tbl.last) := l_access_id;
1876 l_prm_keep_flag_tbl(l_prm_keep_flag_tbl.last) := l_prm_keep_flag;
1877 l_salesforce_id_tbl(l_salesforce_id_tbl.last) := l_salesforce_id;
1878 end loop;
1879 close lc_get_access_details;
1880
1881 if l_access_id_tbl.count > 0 then
1882 l_access_exists_flag := TRUE;
1883 else
1884 l_access_exists_flag := FALSE;
1885 end if;
1886
1887 -- Debug Message
1888 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
1889 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
1890
1891 if l_access_exists_flag then
1892 fnd_message.Set_Token('TEXT', 'Access exist for ' || p_resource_id);
1893 else
1894 fnd_message.Set_Token('TEXT', 'Access does not exist for ' || p_resource_id);
1895 end if;
1896
1897 fnd_msg_pub.Add;
1898
1899 end if;
1900
1901 if p_access_action = pv_assignment_pub.G_ADD_ACCESS and not l_access_exists_flag then
1902
1903 if p_access_type in (pv_assignment_pub.G_CM_ACCESS, pv_assignment_pub.G_PT_ACCESS) then
1904
1905 open lc_cursor for l_get_person_info_sql using p_resource_id;
1906 fetch lc_cursor into l_person_category, l_username, l_person_id ;
1907
1908 if l_person_category = pv_assignment_pub.g_resource_employee then
1909 l_emp_person_id := l_person_id;
1910
1911 elsif l_person_category = pv_assignment_pub.g_resource_party then
1912 l_pt_party_id := l_person_id;
1913
1914 else
1915 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
1916 fnd_message.SET_TOKEN('TEXT', 'Does not recognize person type: ' || l_person_category);
1917 fnd_msg_pub.ADD;
1918
1919 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1920
1921 end if;
1922
1923 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
1924 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
1925 fnd_message.Set_token('TEXT', 'Person is a ' || l_person_category);
1926 fnd_msg_pub.Add;
1927 end if;
1928
1929 elsif p_access_type = pv_assignment_pub.G_PT_ORG_ACCESS then
1930
1931 open lc_cursor for l_get_pt_org_info_sql using p_resource_id;
1932 fetch lc_cursor into l_pt_org_party_id;
1933
1934 end if;
1935
1936 if lc_cursor%NOTFOUND then
1937
1938 fnd_message.SET_NAME ('PV', 'PV_RESOURCE_NOT_FOUND');
1939 fnd_message.SET_TOKEN ('P_RESOURCE_ID' , p_resource_id);
1940 fnd_msg_pub.ADD;
1941
1942 RAISE fnd_api.g_exc_error;
1943
1944 end if;
1945
1946 close lc_cursor;
1947
1948 begin
1949 l_sales_group_id := Get_Salesgroup_ID(p_resource_id);
1950
1951 --if instr(l_sales_grp_id_str, '(') > 0 then
1952 -- l_sales_group_id := to_number(substr(l_sales_grp_id_str, 1, instr(l_sales_grp_id_str, '(') - 1));
1953 --else
1954 -- l_sales_group_id := to_number(l_sales_grp_id_str);
1955 --end if;
1956
1957 exception
1958 when others then
1959 l_sales_group_id := null;
1960 end;
1961
1962 if l_sales_group_id is NULL and p_access_type <> pv_assignment_pub.G_PT_ORG_ACCESS then
1963
1964 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
1965 fnd_message.SET_NAME ('PV', 'PV_NO_DEFAULT_SALESGROUP');
1966 fnd_message.SET_TOKEN ('P_USER' , l_username);
1967 fnd_msg_pub.ADD;
1968 end if;
1969 -- RAISE fnd_api.g_exc_error;
1970
1971 end if;
1972
1973 end if;
1974
1975 select decode(p_access_type, pv_assignment_pub.G_CM_ACCESS, 'CM',
1976 pv_assignment_pub.G_PT_ACCESS, 'PT',
1977 pv_assignment_pub.G_PT_ORG_ACCESS, 'PT_ORG',
1978 'UNKNOWN') into l_temp from dual;
1979
1980 l_pt_full_access_opp := fnd_profile.value('PV_ALLOW_PT_FULL_OPP_ACCESS');
1981
1982
1983 -- ------------------------------------------------------------------------------------------------
1984 -- Remove resource from sales team
1985 -- ------------------------------------------------------------------------------------------------
1986 if p_access_action = pv_assignment_pub.G_REMOVE_ACCESS and l_access_exists_flag then
1987 Debug('Remove resource from sales team...');
1988
1989 open lc_get_am_and_owner (pc_lead_id => p_lead_id);
1990 fetch lc_get_am_and_owner into l_am_rs_id, l_ld_owner_rs_id;
1991 close lc_get_am_and_owner;
1992
1993 if l_am_rs_id is null then
1994 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
1995 fnd_message.SET_TOKEN('TEXT', 'Cannot identify Assignment manager or Opportunity creator');
1996 fnd_msg_pub.ADD;
1997 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1998 end if;
1999
2000
2001 for i in 1 .. l_access_id_tbl.count loop
2002 Debug('l_access_id = ' || l_access_id_tbl(i));
2003
2004 open lc_get_nature_of_resource (pc_access_id => l_access_id_tbl(i));
2005 fetch lc_get_nature_of_resource into l_resoucre_nature;
2006 close lc_get_nature_of_resource;
2007
2008 Debug('l_resoucre_nature : ' || l_resoucre_nature);
2009 Debug('l_prm_keep_flag_tbl(i) : ' || l_prm_keep_flag_tbl(i));
2010
2011 if (l_prm_keep_flag_tbl(i) = 'Y' and l_resoucre_nature = 'CM_OR_REP')
2012 or
2013 (l_resoucre_nature= 'PART_OR_CONT')
2014 then -- means added by WF
2015
2016 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
2017 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
2018 fnd_message.Set_token('TEXT', 'Removing Access for: ' || l_temp);
2019 fnd_msg_pub.Add;
2020 end if;
2021
2022 -- -------------------------------------------------------------------------------
2023 -- Check if this resource has quota sales credit associated with it.
2024 -- -------------------------------------------------------------------------------
2025 FOR x IN lc_get_sales_credit_count(p_resource_id, p_lead_id) LOOP
2026 l_sales_credit_count := x.sales_credit_count;
2027 END LOOP;
2028 Debug('l_sales_credit_count :' || l_sales_credit_count);
2029
2030 -- -------------------------------------------------------------------------------
2031 -- Remove the resource from the sales team of the opportunity if the resource
2032 -- * is not the owner of the opportunity
2033 -- * is not the assignment manager
2034 -- * was put on the sales team by the routing process (prm_keep_flag = 'Y')
2035 -- * does not have any sales credit associated with it
2036 --
2037 -- A resource can also have non-quota sales credits associated with it. Whether
2038 -- these sales credits get deleted or not is depending on the profile
2039 -- PV_REMOVE_NON_QUOTA_SALES_CREDIT:
2040 -- 1). REMOVE_RS_ONLY - only resource will be removed from the sales team but
2041 -- non-quota sales credits will be kept.
2042 -- 2). REMOVE_RS_SALES_CREDIT - the resource will be removed from the sales team
2043 -- and non-quota sales credits will also be deleted.
2044 --
2045 -- If none of the above profile options is selected, the resource won't be
2046 -- removed from the sales team and the sales credits won't be deleted.
2047 -- -------------------------------------------------------------------------------
2048 IF (l_sales_credit_count = 0) THEN
2049 -- ----------------------------------------------------------------------------
2050 -- Retrieve profile value that deals with non-quota sales credit.
2051 -- ----------------------------------------------------------------------------
2052 l_profile_value := FND_PROFILE.VALUE('PV_REMOVE_NON_QUOTA_SALES_CREDIT');
2053 Debug(' Profile Option value of PV_REMOVE_NON_QUOTA_SALES_CREDIT = ' || l_profile_value);
2054
2055 FOR x IN lc_get_nonq_sales_credit(p_resource_id, p_lead_id) LOOP
2056 l_non_quota_sc_id := x.sales_credit_id;
2057 EXIT;
2058 END LOOP;
2059
2060 Debug('l_non_quota_sc_id :'|| l_non_quota_sc_id);
2061
2062 IF (l_non_quota_sc_id IS NOT NULL) THEN
2063 IF (l_profile_value IS NULL) THEN
2064 Debug('Do not remove for: ' || l_temp ||
2065 '. It still has some non-quota sales credits associated with it.');
2066
2067 ELSIF (l_profile_value = 'REMOVE_RS_SALES_CREDIT') THEN
2068 Debug('l_profile_value is REMOVE_RS_SALES_CREDIT. SO deleting access_id:' || l_access_id_tbl(i));
2069 DELETE FROM as_accesses_all acc
2070 WHERE access_id = l_access_id_tbl(i) AND
2071 salesforce_id NOT IN (l_am_rs_id, l_ld_owner_rs_id);
2072
2073 -- ----------------------------------------------------------------
2074 -- Remove non-quota sales crdits
2075 -- ----------------------------------------------------------------
2076 Debug('Deleting Sales Credits from as_sales_credits');
2077 DELETE FROM as_sales_credits
2078 WHERE lead_id = p_lead_id AND
2079 salesforce_id = p_resource_id AND
2080 credit_type_id = 2;
2081
2082 ELSIF (l_profile_value = 'REMOVE_RS_ONLY') THEN
2083 Debug('l_profile_value is REMOVE_RS_ONLY. SO deleting sales credits');
2084 DELETE FROM as_accesses_all acc
2085 WHERE access_id = l_access_id_tbl(i) AND
2086 salesforce_id NOT IN (l_am_rs_id, l_ld_owner_rs_id);
2087 END IF;
2088
2089 -- -----------------------------------------------------------------------
2090 -- If there are no non-quota sales credits
2091 -- -----------------------------------------------------------------------
2092 ELSE
2093 Debug('l_non_quota_sc_id IS NULL.. SO deleting access_id:' || l_access_id_tbl(i) );
2094
2095 DELETE FROM as_accesses_all acc
2096 WHERE access_id = l_access_id_tbl(i) AND
2097 salesforce_id NOT IN (l_am_rs_id, l_ld_owner_rs_id);
2098 END IF;
2099
2100 ELSE
2101 Debug('l_sales_credit_count is not 0');
2102
2103 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
2104 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
2105 fnd_message.Set_token('TEXT', 'Do not remove for: ' || l_temp ||
2106 '. It still has some sales credits associated with it.');
2107 fnd_msg_pub.Add;
2108 END IF;
2109 END IF;
2110
2111 else
2112 Debug('l_prm_keep_flag_tbl(i) is not Y') ;
2113 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
2114 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
2115 fnd_message.Set_token('TEXT', 'Do not remove for: ' || l_temp || '. Prm_keep_flag is not Y');
2116 fnd_msg_pub.Add;
2117 end if;
2118
2119 end if;
2120 end loop;
2121 -- ------------------------------------------------------------------------------------------------ --
2122 -- -------------------------End Removing Resources from Sales Team--------------------------------- --
2123 -- ------------------------------------------------------------------------------------------------ --
2124
2125
2126
2127 elsif p_access_action = pv_assignment_pub.G_ADD_ACCESS and l_access_exists_flag then
2128
2129 open get_opp_open_flag_csr(p_lead_id);
2130 fetch get_opp_open_flag_csr into l_open_flag;
2131 close get_opp_open_flag_csr;
2132
2133 open lc_get_sql_text_attr_7;
2134 fetch lc_get_sql_text_attr_7 into l_sql_text_attr_7;
2135 close lc_get_sql_text_attr_7;
2136
2137 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
2138 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
2139 fnd_message.Set_token('TEXT', ' SQL Text for attribute 7'|| l_sql_text_attr_7);
2140 fnd_msg_pub.Add;
2141 end if;
2142
2143
2144 for i in 1 .. l_access_id_tbl.count loop
2145
2146 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
2147 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
2148 fnd_message.Set_token('TEXT', ' l_pt_ess_opp'|| l_pt_full_access_opp);
2149 fnd_msg_pub.Add;
2150 end if;
2151
2152
2153 IF p_access_type in (pv_assignment_pub.G_CM_ACCESS) THEN
2154
2155 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
2156 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
2157 fnd_message.Set_token('TEXT', 'action type is G_CM_ACCESS and setting the Team_leader_flag, prm_keep_flag to Y for resource id: ' || p_resource_id );
2158 fnd_msg_pub.Add;
2159 end if;
2160
2161 update as_accesses_all set prm_keep_flag = 'Y', freeze_flag = 'Y', team_leader_flag = 'Y', open_flag = l_open_flag
2162 where access_id = l_access_id_tbl(i);
2163
2164
2165 ELSIF p_access_type in (pv_assignment_pub.G_PT_ACCESS,pv_assignment_pub.G_PT_ORG_ACCESS) THEN
2166
2167 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
2168 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
2169 fnd_message.Set_token('TEXT', 'action type is G_PT_ACCESS or G_PT_ORG_ACCESS for resource id: ' || p_resource_id );
2170 fnd_msg_pub.Add;
2171 end if;
2172
2173 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
2174 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
2175 fnd_message.Set_token('TEXT', 'Find the partner id of the resource with resource_id ' || p_resource_id );
2176 fnd_msg_pub.Add;
2177 end if;
2178
2179
2180 IF p_access_type = pv_assignment_pub.G_PT_ORG_ACCESS THEN
2181
2182 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
2183 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
2184 fnd_message.Set_token('TEXT', 'p_access_type is G_PT_ORG_ACCESS and executing lc_get_partner_id_org cursor');
2185 fnd_msg_pub.Add;
2186 end if;
2187
2188 open lc_get_partner_id_org(p_resource_id);
2189 fetch lc_get_partner_id_org into l_current_partnerid_of_rel;
2190 close lc_get_partner_id_org;
2191
2192
2193
2194 ELSIF p_access_type = pv_assignment_pub.G_PT_ACCESS THEN
2195
2196 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
2197 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
2198 fnd_message.Set_token('TEXT', 'p_access_type is G_PT_ACCESS and executing lc_get_partner_id_org_contact cursor');
2199 fnd_msg_pub.Add;
2200 end if;
2201
2202 open lc_get_partner_id_org_contact(p_resource_id);
2203 fetch lc_get_partner_id_org_contact into l_current_partnerid_of_rel;
2204 close lc_get_partner_id_org_contact;
2205
2206
2207 END IF;
2208
2209 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
2210 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
2211 fnd_message.Set_token('TEXT', ' the partner id of the resource with resource_id ' || p_resource_id || ' is ' || l_current_partnerid_of_rel);
2212 fnd_msg_pub.Add;
2213 end if;
2214
2215
2216
2217 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
2218 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
2219 fnd_message.Set_token('TEXT', ' Executing the sql_text to get Allow Access Profile value for partner ' || l_current_partnerid_of_rel);
2220 fnd_msg_pub.Add;
2221 end if;
2222
2223 BEGIN
2224 EXECUTE IMMEDIATE l_sql_text_attr_7 INTO l_enable_full_access_value
2225 USING 7, 'PARTNER', l_current_partnerid_of_rel;
2226 EXCEPTION
2227 WHEN NO_DATA_FOUND THEN
2228 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
2229 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
2230 fnd_message.SET_TOKEN('TEXT', 'No Data found executing the sql_text for attribute id 7 ' || l_sql_text_attr_7);
2231 fnd_msg_pub.ADD;
2232 end if;
2233 END;
2234
2235 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
2236 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
2237 fnd_message.Set_token('TEXT', ' Value of Allow Access Profile value for partner ' || l_current_partnerid_of_rel || ' is ' || l_enable_full_access_value);
2238 fnd_msg_pub.Add;
2239 end if;
2240
2241
2242 IF(l_enable_full_access_value = 'Y') then
2243
2244 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
2245 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
2246 fnd_message.Set_token('TEXT', ' l_enable_full_access_value is Y');
2247 fnd_msg_pub.Add;
2248 end if;
2249
2250
2251 IF p_access_type in (pv_assignment_pub.G_PT_ORG_ACCESS) THEN
2252
2253 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
2254 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
2255 fnd_message.Set_token('TEXT', 'p_access_type is G_PT_ORG_ACCESS and updating team_leader_flag to Y');
2256 fnd_msg_pub.Add;
2257 end if;
2258
2259 update as_accesses_all set prm_keep_flag = 'Y', freeze_flag = 'Y', team_leader_flag = 'Y', open_flag = l_open_flag
2260 where access_id = l_access_id_tbl(i);
2261
2262 ELSIF p_access_type in (pv_assignment_pub.G_PT_ACCESS) THEN
2263 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
2264 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
2265 fnd_message.Set_token('TEXT', 'p_access_type is G_PT_ACCESS and updating team_leader_flag to Y for all partner contact levels');
2266 fnd_msg_pub.Add;
2267 end if;
2268
2269 open lc_get_pt_access (p_lead_id);
2270 loop
2271 fetch lc_get_pt_access into l_pt_resource_id;
2272 exit when lc_get_pt_access%notfound;
2273
2274 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
2275 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
2276 fnd_message.Set_token('TEXT', ' resource ID ' || l_pt_resource_id);
2277 fnd_msg_pub.Add;
2278 END IF;
2279
2280
2281 --For exisitng contatcs, we do nto need to update the team leader flag.
2282 -- We need to leave it the way it was.
2283 -- THats why we are not updating team_leader_flag
2284
2285 update as_accesses_all set prm_keep_flag = 'Y', freeze_flag = 'Y'
2286 --, team_leader_flag = 'Y'
2287 where access_id = l_access_id_tbl(i) and salesforce_id = l_pt_resource_id;
2288
2289
2290 end loop;
2291 close lc_get_pt_access;
2292
2293 END IF;
2294
2295 ELSIF(l_enable_full_access_value = 'N') then
2296
2297 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
2298 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
2299 fnd_message.Set_token('TEXT', ' l_enable_full_access_value is N');
2300 fnd_msg_pub.Add;
2301 end if;
2302
2303 IF p_access_type in (pv_assignment_pub.G_PT_ORG_ACCESS) THEN
2304
2305 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
2306 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
2307 fnd_message.Set_token('TEXT', 'p_access_type is G_PT_ORG_ACCESS and updating team_leader_flag to N');
2308 fnd_msg_pub.Add;
2309 end if;
2310
2311 update as_accesses_all set prm_keep_flag = 'Y', freeze_flag = 'Y', team_leader_flag = 'N', open_flag = l_open_flag
2312 where access_id = l_access_id_tbl(i);
2313
2314 ELSIF p_access_type in (pv_assignment_pub.G_PT_ACCESS) THEN
2315
2316 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
2317 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
2318 fnd_message.Set_token('TEXT', 'p_access_type is G_PT_ACCESS and updating team_leader_flag to N for all partner contact levels');
2319 fnd_msg_pub.Add;
2320 end if;
2321
2322 open lc_get_pt_access (p_lead_id);
2323 loop
2324 fetch lc_get_pt_access into l_pt_resource_id;
2325 exit when lc_get_pt_access%notfound;
2326
2327 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
2328 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
2329 fnd_message.Set_token('TEXT', ' resource ID ' || l_pt_resource_id);
2330 fnd_msg_pub.Add;
2331 END IF;
2332
2333
2334 --For exisitng contatcs, we do nto need to update the team leader flag.
2335 -- We need to leave it the way it was.
2336 -- THats why we are not updating team_leader_flag
2337
2338 update as_accesses_all set prm_keep_flag = 'Y', freeze_flag = 'Y'
2339 --, team_leader_flag = 'N'
2340 where access_id = l_access_id_tbl(i) and salesforce_id = l_pt_resource_id;
2341
2342 end loop;
2343 close lc_get_pt_access;
2344
2345 END IF;
2346
2347
2348 ELSE -- FOr null value and othewr values
2349 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
2350 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
2351 fnd_message.Set_token('TEXT', ' l_enable_full_access_value is null or other values');
2352 fnd_msg_pub.Add;
2353 end if;
2354
2355 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
2356 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
2357 fnd_message.Set_token('TEXT', ' Now the next level of security which is the profile options value would come into picture.');
2358 fnd_msg_pub.Add;
2359 end if;
2360
2361 IF l_pt_full_access_opp = 'Y' THEN
2362
2363 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
2364 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
2365 fnd_message.Set_token('TEXT', 'l_pt_full_access_opp is Y and Setting prm_keep_flag to Y for resource id: ' || p_resource_id || ' if not lead owner');
2366 fnd_msg_pub.Add;
2367 end if;
2368
2369 IF p_access_type in (pv_assignment_pub.G_PT_ORG_ACCESS) THEN
2370
2371 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
2372 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
2373 fnd_message.Set_token('TEXT', 'l_pt_full_access_opp is N and making team_leader_flag to Y of PT ORG: ' || p_resource_id || ' if not lead owner');
2374 fnd_msg_pub.Add;
2375 end if;
2376
2377 update as_accesses_all set prm_keep_flag = 'Y', freeze_flag = 'Y', open_flag = l_open_flag, team_leader_flag = 'Y'
2378 where access_id = l_access_id_tbl(i);
2379
2380 ELSIF p_access_type = pv_assignment_pub.G_PT_ACCESS THEN
2381
2382 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
2383 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
2384 fnd_message.Set_token('TEXT', 'action type is G_PT_ACCESS , l_pt_full_access_opp is Y and not touching the team leader flag of Partner resource ID ' || l_pt_resource_id);
2385 fnd_msg_pub.Add;
2386 END IF;
2387
2388 --For exisitng contatcs, we do nto need to update the team leader flag.
2389 -- We need to leave it the way it was.
2390 -- THats why we are not updating team_leader_flag
2391
2392 update as_accesses_all set prm_keep_flag = 'Y', freeze_flag = 'Y', open_flag = l_open_flag
2393 --,team_leader_flag = 'Y'
2394 where access_id = l_access_id_tbl(i);
2395
2396 END IF;
2397
2398
2399
2400 ELSE
2401 IF p_access_type in (pv_assignment_pub.G_PT_ORG_ACCESS) THEN
2402
2403
2404 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
2405 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
2406 fnd_message.Set_token('TEXT', 'l_pt_full_access_opp is N and making team_leader_flag to N PT ORG: ' || p_resource_id || ' if not lead owner');
2407 fnd_msg_pub.Add;
2408 end if;
2409
2410 update as_accesses_all set prm_keep_flag = 'Y', freeze_flag = 'Y', team_leader_flag = 'N', open_flag = l_open_flag
2411 where access_id = l_access_id_tbl(i);
2412
2413
2414 ELSIF p_access_type = pv_assignment_pub.G_PT_ACCESS THEN
2415
2416 open lc_get_pt_access (p_lead_id);
2417 loop
2418 fetch lc_get_pt_access into l_pt_resource_id;
2419 exit when lc_get_pt_access%notfound;
2420
2421 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
2422 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
2423 fnd_message.Set_token('TEXT', 'action type is G_PT_ACCESS , l_pt_full_access_opp is N and not touching the team leader flag of Partner resource ID ' || l_pt_resource_id);
2424 fnd_msg_pub.Add;
2425 END IF;
2426
2427
2428 --For exisitng contatcs, we do nto need to update the team leader flag.
2429 -- We need to leave it the way it was.
2430 -- THats why we are not updating team_leader_flag
2431
2432
2433 update as_accesses_all set prm_keep_flag = 'Y', freeze_flag = 'Y'
2434 --, team_leader_flag = 'N'
2435 where access_id = l_access_id_tbl(i) and salesforce_id = l_pt_resource_id;
2436
2437
2438 end loop;
2439 close lc_get_pt_access;
2440 END IF; -- end of IF p_access_type in (pv_assignment_pub.G_PT_ORG_ACCESS) THEN
2441
2442 END IF; -- end of IF l_pt_full_access_opp = 'Y' THEN
2443
2444 END IF; -- end of IF(l_enable_full_access_value = 'Y') then else loop
2445
2446 END IF; -- end of ELSIF p_access_type in (pv_assignment_pub.G_PT_ACCESS,pv_assignment_pub.G_PT_ORG_ACCESS) THEN
2447
2448
2449 end loop;
2450
2451
2452 elsif p_access_action = pv_assignment_pub.G_ADD_ACCESS and not l_access_exists_flag then
2453
2454 select as_accesses_s.nextval into l_sales_team_rec.Access_Id from dual;
2455
2456 open get_opp_open_flag_csr(p_lead_id);
2457 fetch get_opp_open_flag_csr into l_open_flag;
2458 close get_opp_open_flag_csr;
2459
2460 --<<<
2461 open lc_get_sql_text_attr_7;
2462 fetch lc_get_sql_text_attr_7 into l_sql_text_attr_7;
2463 close lc_get_sql_text_attr_7;
2464
2465 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
2466 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
2467 fnd_message.Set_token('TEXT', ' SQL Text for attribute 7'|| l_sql_text_attr_7);
2468 fnd_msg_pub.Add;
2469 end if;
2470
2471
2472
2473 IF p_access_type in (pv_assignment_pub.G_CM_ACCESS) THEN
2474
2475 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
2476 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
2477 fnd_message.Set_token('TEXT', 'action type is G_CM_ACCESS and setting the Team_leader_flag to Y for resource id: ' || p_resource_id );
2478 fnd_msg_pub.Add;
2479 end if;
2480
2481 l_sales_team_rec.Team_Leader_Flag := 'Y';
2482
2483 ELSIF p_access_type in (pv_assignment_pub.G_PT_ACCESS,pv_assignment_pub.G_PT_ORG_ACCESS) THEN
2484
2485 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
2486 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
2487 fnd_message.Set_token('TEXT', 'action type is ' || p_access_type || ' for resource id: ' || p_resource_id );
2488 fnd_msg_pub.Add;
2489 end if;
2490
2491 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
2492 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
2493 fnd_message.Set_token('TEXT', 'Find the partner id of the resource with resource_id ' || p_resource_id );
2494 fnd_msg_pub.Add;
2495 end if;
2496
2497 IF p_access_type in (pv_assignment_pub.G_PT_ORG_ACCESS) THEN
2498
2499 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
2500 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
2501 fnd_message.Set_token('TEXT', 'p_access_type is G_PT_ORG_ACCESS and executing lc_get_partner_id_org cursor');
2502 fnd_msg_pub.Add;
2503 end if;
2504
2505 open lc_get_partner_id_org(p_resource_id);
2506 fetch lc_get_partner_id_org into l_current_partnerid_of_rel;
2507 close lc_get_partner_id_org;
2508
2509 ELSIF p_access_type in (pv_assignment_pub.G_PT_ACCESS) THEN
2510 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
2511 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
2512 fnd_message.Set_token('TEXT', 'p_access_type is G_PT_ACCESS and executing lc_get_partner_id_org_contact cursor');
2513 fnd_msg_pub.Add;
2514 end if;
2515
2516 open lc_get_partner_id_org_contact(p_resource_id);
2517 fetch lc_get_partner_id_org_contact into l_current_partnerid_of_rel;
2518 close lc_get_partner_id_org_contact;
2519 END IF;
2520
2521 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
2522 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
2523 fnd_message.Set_token('TEXT', ' the partner id of the resource with resource_id ' || p_resource_id || ' is ' || l_current_partnerid_of_rel);
2524 fnd_msg_pub.Add;
2525 end if;
2526
2527 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
2528 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
2529 fnd_message.Set_token('TEXT', ' Executing the sql_text to get Allow Access Profile value for partner ' || l_current_partnerid_of_rel);
2530 fnd_msg_pub.Add;
2531 end if;
2532
2533 BEGIN
2534 EXECUTE IMMEDIATE l_sql_text_attr_7 INTO l_enable_full_access_value
2535 USING 7, 'PARTNER', l_current_partnerid_of_rel;
2536 EXCEPTION
2537 WHEN NO_DATA_FOUND THEN
2538 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
2539 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
2540 fnd_message.SET_TOKEN('TEXT', 'No Data found executing sql_text for attribute id 7 ' || l_sql_text_attr_7);
2541 fnd_msg_pub.ADD;
2542 end if;
2543 END;
2544
2545 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
2546 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
2547 fnd_message.Set_token('TEXT', ' Value of Allow Access Profile value for partner ' || l_current_partnerid_of_rel || ' is ' || l_enable_full_access_value);
2548 fnd_msg_pub.Add;
2549 end if;
2550
2551
2552 IF(l_enable_full_access_value = 'Y') then
2553
2554 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
2555 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
2556 fnd_message.Set_token('TEXT', ' l_enable_full_access_value is Y and updatign team_leader_flag to Y');
2557 fnd_msg_pub.Add;
2558 end if;
2559
2560 l_sales_team_rec.Team_Leader_Flag := 'Y';
2561
2562 ELSIF(l_enable_full_access_value = 'N') then
2563
2564 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
2565 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
2566 fnd_message.Set_token('TEXT', ' l_enable_full_access_value is N and updating team leader flkag to N');
2567 fnd_msg_pub.Add;
2568 end if;
2569
2570 l_sales_team_rec.Team_Leader_Flag := 'N';
2571
2572 ELSE -- FOr null value and othewr values
2573 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
2574 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
2575 fnd_message.Set_token('TEXT', ' l_enable_full_access_value is null or other values');
2576 fnd_msg_pub.Add;
2577 end if;
2578
2579 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
2580 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
2581 fnd_message.Set_token('TEXT', ' Now the next level of security which is the profile options value would come into picture.');
2582 fnd_msg_pub.Add;
2583 end if;
2584
2585 IF l_pt_full_access_opp = 'Y' THEN
2586
2587 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
2588 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
2589 fnd_message.Set_token('TEXT', 'l_pt_full_access_opp is Y and Setting team_leader_flag to Y');
2590 fnd_msg_pub.Add;
2591 end if;
2592
2593 l_sales_team_rec.Team_Leader_Flag := 'Y';
2594
2595 ELSE
2596
2597 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
2598 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
2599 fnd_message.Set_token('TEXT', 'l_pt_full_access_opp is Y and Setting team_leader_flag to N');
2600 fnd_msg_pub.Add;
2601 end if;
2602
2603 l_sales_team_rec.Team_Leader_Flag := 'N';
2604
2605 END IF; -- end of IF l_pt_full_access_opp = 'Y' THEN
2606
2607 END IF; -- end of IF(l_enable_full_access_value = 'Y') then else loop
2608
2609 END IF; -- end of ELSIF p_access_type in (pv_assignment_pub.G_PT_ACCESS,pv_assignment_pub.G_PT_ORG_ACCESS) THEN
2610
2611
2612
2613
2614 -->>>
2615
2616 -- l_sales_team_rec.Access_type := 'X'; -- obsolete column, always 'X'
2617 l_sales_team_rec.Last_Update_Date := SYSDATE;
2618 l_sales_team_rec.Last_Updated_By := FND_GLOBAL.User_Id;
2619 l_sales_team_rec.Creation_Date := SYSDATE;
2620 l_sales_team_rec.Created_By := FND_GLOBAL.User_Id;
2621 l_sales_team_rec.Last_Update_Login := FND_GLOBAL.Conc_Login_Id;
2622 l_sales_team_rec.Freeze_Flag := 'Y'; -- if Y, not removed by TAP
2623 l_sales_team_rec.Reassign_Flag := 'N';
2624 l_sales_team_rec.Customer_Id := p_customer_id;
2625 l_sales_team_rec.Address_Id := p_address_id;
2626 l_sales_team_rec.Salesforce_id := p_resource_id;
2627 l_sales_team_rec.Person_Id := l_emp_person_id;
2628 l_sales_team_rec.Partner_Customer_id := l_pt_org_party_id; -- party_id of partner relationship
2629 l_sales_team_rec.Partner_Address_id := NULL;
2630 l_sales_team_rec.created_Person_Id := NULL; -- not used
2631 l_sales_team_rec.lead_id := p_lead_id;
2632 l_sales_team_rec.Freeze_Date := NULL;
2633 l_sales_team_rec.Reassign_Reason := NULL;
2634 -- l_sales_team_rec.org_id := NULL; -- not used
2635 l_sales_team_rec.downloadable_flag := NULL;
2636 l_sales_team_rec.Salesforce_Role_Code := NULL; -- if set to account manager, person can view
2637 -- all leads/oppor for the customer_id
2638 l_sales_team_rec.Salesforce_Relationship_Code := NULL;
2639 l_sales_team_rec.Sales_group_id := l_sales_group_id;
2640 -- l_sales_team_rec.Internal_Update_access := 1; -- if team_leader_flag is Y, then 1, else 0
2641 l_sales_team_rec.Sales_lead_id := NULL;
2642 l_sales_team_rec.Partner_Cont_Party_Id := l_pt_party_id; -- party_id of partner contact relation
2643 l_sales_team_rec.owner_flag := 'N'; -- alway N for oppr. Used for sales leads only
2644 l_sales_team_rec.created_by_tap_flag := 'N'; -- set by realtime TAP
2645 -- l_sales_team_rec.prm_keep_flag := 'Y'; -- used exclusively by PRM
2646
2647 insert into as_accesses_all (
2648 ACCESS_ID,
2649 LAST_UPDATE_DATE,
2650 LAST_UPDATED_BY,
2651 CREATION_DATE,
2652 CREATED_BY,
2653 LAST_UPDATE_LOGIN,
2654 ACCESS_TYPE,
2655 FREEZE_FLAG,
2656 REASSIGN_FLAG,
2657 TEAM_LEADER_FLAG,
2658 CUSTOMER_ID,
2659 ADDRESS_ID,
2660 SALESFORCE_ID,
2661 PERSON_ID,
2662 PARTNER_CUSTOMER_ID,
2663 PARTNER_ADDRESS_ID,
2664 LEAD_ID,
2665 FREEZE_DATE,
2666 SALESFORCE_ROLE_CODE,
2667 SALESFORCE_RELATIONSHIP_CODE,
2668 SALES_GROUP_ID,
2669 INTERNAL_UPDATE_ACCESS,
2670 SALES_LEAD_ID,
2671 PARTNER_CONT_PARTY_ID,
2672 OWNER_FLAG,
2673 CREATED_BY_TAP_FLAG,
2674 PRM_KEEP_FLAG,
2675 OPEN_FLAG)
2676 values (
2677 l_sales_team_rec.Access_id,
2678 l_sales_team_rec.Last_Update_Date,
2679 l_sales_team_rec.Last_Updated_By,
2680 l_sales_team_rec.Creation_Date,
2681 l_sales_team_rec.Created_By,
2682 l_sales_team_rec.Last_Update_Login,
2683 'X',
2684 l_sales_team_rec.Freeze_Flag,
2685 l_sales_team_rec.Reassign_Flag,
2686 l_sales_team_rec.Team_Leader_Flag,
2687 l_sales_team_rec.Customer_Id,
2688 l_sales_team_rec.Address_Id,
2689 l_sales_team_rec.Salesforce_id,
2690 l_sales_team_rec.Person_Id,
2691 l_sales_team_rec.Partner_Customer_id,
2692 l_sales_team_rec.Partner_Address_id,
2693 l_sales_team_rec.lead_id,
2694 l_sales_team_rec.Freeze_Date,
2695 l_sales_team_rec.Salesforce_Role_Code,
2696 l_sales_team_rec.Salesforce_Relationship_code,
2697 l_sales_team_rec.Sales_group_id,
2698 1,
2699 l_sales_team_rec.Sales_lead_id,
2700 l_sales_team_rec.Partner_Cont_Party_Id,
2701 l_sales_team_rec.owner_flag,
2702 l_sales_team_rec.created_by_tap_flag,
2703 'Y',
2704 l_open_flag
2705 );
2706
2707 end if;
2708
2709 IF FND_API.To_Boolean ( p_commit ) THEN
2710 COMMIT WORK;
2711 END IF;
2712
2713 -- Debug Message
2714 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
2715 fnd_message.Set_Name('PV', 'API:' || l_api_name || ': End');
2716 fnd_msg_pub.Add;
2717 END IF;
2718
2719 -- Standard call to get message count and if count is 1, get message info.
2720 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_FALSE,
2721 p_count => x_msg_count,
2722 p_data => x_msg_data);
2723
2724 EXCEPTION
2725
2726 WHEN FND_API.G_EXC_ERROR THEN
2727
2728 x_return_status := FND_API.G_RET_STS_ERROR ;
2729
2730 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_FALSE,
2731 p_count => x_msg_count,
2732 p_data => x_msg_data);
2733
2734 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2735
2736 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2737
2738 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_FALSE,
2739 p_count => x_msg_count,
2740 p_data => x_msg_data);
2741
2742 WHEN OTHERS THEN
2743
2744 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2745
2746 fnd_msg_pub.Add_Exc_Msg(G_PKG_NAME, l_api_name);
2747 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_FALSE,
2748 p_count => x_msg_count,
2749 p_data => x_msg_data);
2750
2751 end UpdateAccess;
2752
2753
2754 procedure GetWorkflowID (p_api_version_number IN NUMBER,
2755 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2756 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2757 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2758 p_lead_id IN NUMBER,
2759 p_entity IN VARCHAR2,
2760 x_itemType OUT NOCOPY VARCHAR2,
2761 x_itemKey OUT NOCOPY VARCHAR2,
2762 x_routing_status OUT NOCOPY VARCHAR2,
2763 x_wf_status OUT NOCOPY VARCHAR2,
2764 x_return_status OUT NOCOPY VARCHAR2,
2765 x_msg_count OUT NOCOPY NUMBER,
2766 x_msg_data OUT NOCOPY VARCHAR2) is
2767
2768 l_api_name CONSTANT VARCHAR2(30) := 'GetWorkflowID';
2769 l_api_version_number CONSTANT NUMBER := 1.0;
2770
2771 begin
2772 -- Standard call to check for call compatibility.
2773 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
2774 p_api_version_number,
2775 l_api_name,
2776 G_PKG_NAME)
2777 THEN
2778 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2779 END IF;
2780
2781
2782 -- Initialize message list if p_init_msg_list is set to TRUE.
2783 IF FND_API.to_Boolean( p_init_msg_list )
2784 THEN
2785 fnd_msg_pub.initialize;
2786 END IF;
2787
2788 -- Debug Message
2789 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
2790 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
2791 fnd_message.Set_Token('TEXT', 'In ' || l_api_name || '. Lead id: ' || p_Lead_id);
2792 fnd_msg_pub.Add;
2793 END IF;
2794
2795 -- Initialize API return status to success
2796 x_return_status := FND_API.G_RET_STS_SUCCESS;
2797
2798 begin
2799
2800 select wl.wf_item_type, wl.wf_item_key, wl.routing_status, wl.wf_status
2801 into x_itemType, x_itemKey, x_routing_status, x_wf_status
2802 from pv_lead_workflows wl
2803 where wl.lead_id = p_lead_id
2804 and wl.entity = p_entity
2805 and wl.latest_routing_flag = 'Y';
2806
2807 exception
2808 when TOO_MANY_ROWS then
2809
2810 fnd_message.Set_Name('PV', 'PV_INVALID_ROUTING_ROW');
2811 fnd_message.Set_Token('P_LEAD_ID', p_lead_id);
2812 fnd_msg_pub.Add;
2813
2814 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2815
2816 when NO_DATA_FOUND then
2817 null;
2818 end;
2819
2820 if x_itemkey is NULL then
2821 x_wf_status := 'NEW';
2822
2823 elsif x_routing_status not in (pv_assignment_pub.g_r_status_active,
2824 pv_assignment_pub.g_r_status_matched,
2825 pv_assignment_pub.g_r_status_offered,
2826 pv_assignment_pub.g_r_status_recycled,
2827 pv_assignment_pub.g_r_status_unassigned,
2828 pv_assignment_pub.g_r_status_abandoned,
2829 pv_assignment_pub.g_r_status_failed_auto,
2830 pv_assignment_pub.g_r_status_withdrawn) then
2831
2832 fnd_message.Set_Name('PV', 'PV_UNKNOWN_ROUTING_STAGE');
2833 fnd_message.SET_TOKEN('P_ROUTING', x_routing_status);
2834 fnd_msg_pub.ADD;
2835
2836 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2837
2838 elsif x_wf_status not in (pv_assignment_pub.g_wf_status_open, pv_assignment_pub.g_wf_status_closed) then
2839
2840 fnd_message.Set_Name('PV', 'PV_INVALID_WF_STATUS');
2841 fnd_message.SET_TOKEN('P_WF_STATUS', x_wf_status);
2842 fnd_message.Set_Token('P_LEAD_ID', p_lead_id);
2843 fnd_msg_pub.ADD;
2844
2845 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2846
2847 end if;
2848
2849 --
2850 -- End of API body.
2851 --
2852
2853 IF FND_API.To_Boolean ( p_commit ) THEN
2854 COMMIT WORK;
2855 END IF;
2856
2857 -- Standard call to get message count and if count is 1, get message info.
2858 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_TRUE,
2859 p_count => x_msg_count,
2860 p_data => x_msg_data);
2861 EXCEPTION
2862
2863 WHEN FND_API.G_EXC_ERROR THEN
2864
2865 x_return_status := FND_API.G_RET_STS_ERROR ;
2866 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_TRUE,
2867 p_count => x_msg_count,
2868 p_data => x_msg_data);
2869
2870 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2871
2872 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2873 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_TRUE,
2874 p_count => x_msg_count,
2875 p_data => x_msg_data);
2876
2877 WHEN OTHERS THEN
2878
2879 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2880 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
2881 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_TRUE,
2882 p_count => x_msg_count,
2883 p_data => x_msg_data);
2884 end GetWorkflowID;
2885
2886 --=============================================================================+
2887 --| Private Function |
2888 --| |
2889 --| Get_Salesgroup_ID |
2890 --| |
2891 --| Parameters |
2892 --| IN |
2893 --| OUT |
2894 --| |
2895 --| |
2896 --| NOTES: |
2897 --| |
2898 --| HISTORY |
2899 --| |
2900 --==============================================================================
2901 FUNCTION Get_Salesgroup_ID (
2902 p_resource_id IN NUMBER
2903 )
2904 RETURN NUMBER
2905 IS
2906 l_sales_group_id_str VARCHAR2(100);
2907 l_sales_group_id NUMBER;
2908
2909 -- ------------------------------------------------------------------
2910 -- Retrieves the salesgroup_id of a resource.
2911 -- IF the resource belongs to more than one sales group, get the
2912 -- sales group from the profile: ASF_DEFAULT_GROUP_ROLE.
2913 -- ------------------------------------------------------------------
2914 CURSOR c_salesgroup_id IS
2915 SELECT DECODE(COUNT(*),
2916 0,
2917 null,
2918 1,
2919 TO_CHAR(MAX(grp.group_id)),
2920 FND_PROFILE.VALUE_SPECIFIC('ASF_DEFAULT_GROUP_ROLE',
2921 MAX(RES.user_id))) salesgroup_id
2922 FROM JTF_RS_GROUP_MEMBERS mem,
2923 JTF_RS_ROLE_RELATIONS rrel,
2924 JTF_RS_ROLES_B role,
2925 JTF_RS_GROUP_USAGES u,
2926 JTF_RS_GROUPS_B grp,
2927 JTF_RS_RESOURCE_EXTNS RES
2928 WHERE mem.group_member_id = rrel.role_resource_id AND
2929 rrel.role_resource_type = 'RS_GROUP_MEMBER' AND
2930 rrel.role_id = role.role_id AND
2931 role.role_type_code IN ('SALES','TELESALES','FIELDSALES','PRM') AND
2932 mem.delete_flag <> 'Y' AND
2933 rrel.delete_flag <> 'Y' AND
2934 sysdate BETWEEN rrel.start_date_active AND
2935 NVL(rrel.end_date_active, SYSDATE) AND
2936 mem.group_id = u.group_id AND
2937 u.usage in ('SALES','PRM') AND
2938 mem.group_id = grp.group_id AND
2939 sysdate BETWEEN grp.start_date_active AND
2940 NVL(grp.end_date_active,sysdate) AND
2941 mem.resource_id = RES.resource_id AND
2942 RES.resource_id = p_resource_id;
2943
2944 BEGIN
2945 Debug('Calling Get_Salesgroup_ID function...........');
2946 Debug('resource_id = ' || p_resource_id);
2947
2948 FOR x IN c_salesgroup_id LOOP
2949 BEGIN
2950 l_sales_group_id_str := x.salesgroup_id;
2951
2952 Debug('l_sales_group_id_str = ' || l_sales_group_id_str);
2953
2954 -- -------------------------------------------------------------
2955 -- Parse out the string into an ID.
2956 -- The string could look like this: "100000100(Member)"
2957 -- -------------------------------------------------------------
2958 IF (INSTR(l_sales_group_id_str, ')') > 0) THEN
2959 l_sales_group_id :=
2960 TO_NUMBER(SUBSTR(l_sales_group_id_str, 1,
2961 INSTR(l_sales_group_id_str, '(') - 1));
2962
2963 ELSE
2964 l_sales_group_id := TO_NUMBER(l_sales_group_id_str);
2965 END IF;
2966
2967 EXCEPTION
2968 WHEN OTHERS THEN
2969 l_sales_group_id := null;
2970 END;
2971 END LOOP;
2972
2973 RETURN l_sales_group_id;
2974 END Get_Salesgroup_ID;
2975
2976 procedure checkforErrors (
2977 p_api_version_number IN NUMBER
2978 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
2979 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
2980 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
2981 ,p_itemtype IN VARCHAR2
2982 ,p_itemkey IN VARCHAR2
2983 ,x_return_status OUT NOCOPY VARCHAR2
2984 ,x_msg_count OUT NOCOPY NUMBER
2985 ,x_msg_data OUT NOCOPY VARCHAR2) is
2986
2987 l_api_name CONSTANT VARCHAR2(30) := 'checkforErrors';
2988 l_api_version_number CONSTANT NUMBER := 1.0;
2989 l_wf_error_msg varchar2(2000);
2990 l_wf_error_stack varchar2(2000);
2991
2992
2993 -- check root itemkey and all child itemkeys for any errors
2994 -- initially this cursor was using nid but found out that
2995 -- wf_item_activity_statuses.notification_id is sometimes null when there is an error
2996
2997 -- ignore mailer errors (WFMLRSND_FAILED)
2998
2999 cursor lc_wf_error_message(pc_itemtype varchar2, pc_itemkey varchar2) is
3000 select error_message , error_stack
3001 from wf_item_activity_statuses
3002 where item_type = pc_itemtype
3003 and item_key in
3004 (select item_key from wf_items
3005 start with item_type = pc_itemtype and item_key = pc_itemkey
3006 connect by parent_item_key = prior item_key and parent_item_type = pc_itemtype)
3007 and error_message is not null and error_name <> 'WFMLRSND_FAILED';
3008
3009
3010 begin
3011 -- Standard call to check for call compatibility.
3012
3013 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
3014 p_api_version_number,
3015 l_api_name,
3016 G_PKG_NAME) THEN
3017 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3018
3019 END IF;
3020
3021 -- Initialize message list if p_init_msg_list is set to TRUE.
3022 IF FND_API.to_Boolean( p_init_msg_list ) THEN
3023 fnd_msg_pub.initialize;
3024 END IF;
3025
3026 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
3027 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
3028 fnd_message.Set_Token('TEXT', 'In ' || l_api_name);
3029 fnd_msg_pub.Add;
3030 END IF;
3031
3032 x_return_status := FND_API.G_RET_STS_SUCCESS ;
3033
3034 open lc_wf_error_message(pc_itemtype => p_itemtype, pc_itemkey => p_itemkey);
3035 fetch lc_wf_error_message into l_wf_error_msg, l_wf_error_stack;
3036 close lc_wf_error_message;
3037
3038 if l_wf_error_msg is not null then
3039
3040 fnd_message.SET_NAME('PV', 'PV_DEBUG_MESSAGE');
3041 fnd_message.SET_TOKEN('TEXT' ,l_wf_error_msg);
3042 fnd_msg_pub.ADD;
3043
3044 fnd_message.SET_NAME('PV', 'PV_MSG_FRM_CHK_FOR_ERR');
3045 fnd_message.SET_TOKEN('P_ITEM_TYPE' ,p_itemtype);
3046 fnd_message.SET_TOKEN('P_ITEM_KEY' ,p_itemkey);
3047
3048 if fnd_profile.value('ASF_PROFILE_DEBUG_MSG_ON') = 'Y' then
3049
3050 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
3051 fnd_message.Set_Token('TEXT', l_wf_error_stack);
3052 fnd_msg_pub.Add;
3053
3054 end if;
3055
3056 raise FND_API.G_EXC_ERROR;
3057
3058 end if;
3059 IF FND_API.To_Boolean ( p_commit ) THEN
3060 COMMIT WORK;
3061 END IF;
3062
3063 -- Standard call to get message count and if count is 1, get message info.
3064 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_FALSE,
3065 p_count => x_msg_count,
3066 p_data => x_msg_data);
3067 EXCEPTION
3068
3069 WHEN FND_API.G_EXC_ERROR THEN
3070
3071 x_return_status := FND_API.G_RET_STS_ERROR ;
3072 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_FALSE,
3073 p_count => x_msg_count,
3074 p_data => x_msg_data);
3075
3076 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3077
3078 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3079 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_FALSE,
3080 p_count => x_msg_count,
3081 p_data => x_msg_data);
3082
3083 WHEN OTHERS THEN
3084
3085 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3086 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
3087 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_FALSE,
3088 p_count => x_msg_count,
3089 p_data => x_msg_data);
3090
3091 end checkforErrors;
3092
3093
3094 -- ***************************************************************************
3095
3096 --=============================================================================+
3097 --| Private Procedure |
3098 --| |
3099 --| Set_Message |
3100 --| |
3101 --| Parameters |
3102 --| IN |
3103 --| OUT |
3104 --| |
3105 --| |
3106 --| NOTES: |
3107 --| |
3108 --| HISTORY |
3109 --| |
3110 --==============================================================================
3111 PROCEDURE Debug(
3112 p_msg_string IN VARCHAR2
3113 )
3114 IS
3115
3116 BEGIN
3117 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
3118 FND_MESSAGE.Set_Name('PV', 'PV_DEBUG_MESSAGE');
3119 FND_MESSAGE.Set_Token('TEXT', p_msg_string);
3120 FND_MSG_PUB.Add;
3121 END IF;
3122 END Debug;
3123 -- =================================End of Debug================================
3124
3125
3126
3127 --=============================================================================+
3128 --| Private Procedure |
3129 --| |
3130 --| Set_Message |
3131 --| |
3132 --| Parameters |
3133 --| IN |
3134 --| OUT |
3135 --| |
3136 --| |
3137 --| NOTES: |
3138 --| |
3139 --| HISTORY |
3140 --| |
3141 --==============================================================================
3142 PROCEDURE Set_Message(
3143 p_msg_level IN NUMBER,
3144 p_msg_name IN VARCHAR2,
3145 p_token1 IN VARCHAR2,
3146 p_token1_value IN VARCHAR2,
3147 p_token2 IN VARCHAR2 := NULL ,
3148 p_token2_value IN VARCHAR2 := NULL,
3149 p_token3 IN VARCHAR2 := NULL,
3150 p_token3_value IN VARCHAR2 := NULL
3151 )
3152 IS
3153 BEGIN
3154 IF FND_MSG_PUB.Check_Msg_Level(p_msg_level) THEN
3155 FND_MESSAGE.Set_Name('PV', p_msg_name);
3156 FND_MESSAGE.Set_Token(p_token1, p_token1_value);
3157
3158 IF (p_token2 IS NOT NULL) THEN
3159 FND_MESSAGE.Set_Token(p_token2, p_token2_value);
3160 END IF;
3161
3162 IF (p_token3 IS NOT NULL) THEN
3163 FND_MESSAGE.Set_Token(p_token3, p_token3_value);
3164 END IF;
3165
3166 FND_MSG_PUB.Add;
3167 END IF;
3168 END Set_Message;
3169 -- ==============================End of Set_Message==============================
3170
3171
3172 End PV_ASSIGN_UTIL_PVT;