[Home] [Help]
PACKAGE BODY: APPS.PV_ASSIGNMENT_PUB
Source
1 package body PV_ASSIGNMENT_PUB as
2 /* $Header: pvxasgnb.pls 120.8 2006/08/24 20:58:04 amaram ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30):='PV_ASSIGNMENT_PUB';
5 G_FILE_NAME CONSTANT VARCHAR2(12):='pvxasgnb.pls';
6
7 -- ----------------------------------------------------------------------------------
8 -- ORA-00054: resource busy and acquire with NOWAIT specified
9 -- ----------------------------------------------------------------------------------
10 g_e_resource_busy EXCEPTION;
11 PRAGMA EXCEPTION_INIT(g_e_resource_busy, -54);
12
13
14 --=============================================================================+
15 --| Procedure |
16 --| |
17 --| CreateAssignment |
18 --| |
19 --| |
20 --| Parameters |
21 --| IN |
22 --| OUT |
23 --| |
24 --| |
25 --| NOTES |
26 --| |
27 --| HISTORY |
28 --| |
29 --==============================================================================
30 procedure CreateAssignment (p_api_version_number IN NUMBER,
31 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
32 p_commit IN VARCHAR2 := FND_API.G_FALSE,
33 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
34 p_entity in VARCHAR2,
35 p_lead_id in NUMBER,
36 p_creating_username IN VARCHAR2,
37 p_assignment_type in VARCHAR2,
38 p_bypass_cm_ok_flag in VARCHAR2,
39 p_partner_id_tbl in JTF_NUMBER_TABLE,
40 p_rank_tbl in JTF_NUMBER_TABLE,
41 p_partner_source_tbl in JTF_VARCHAR2_TABLE_100,
42 p_process_rule_id in NUMBER,
43 x_return_status OUT NOCOPY VARCHAR2,
44 x_msg_count OUT NOCOPY NUMBER,
45 x_msg_data OUT NOCOPY VARCHAR2) is
46
47 l_api_name CONSTANT VARCHAR2(30) := 'CreateAssignment';
48 l_api_version_number CONSTANT NUMBER := 1.0;
49
50 l_itemType varchar2(30);
51 l_itemKey varchar2(30);
52 l_user_category varchar2(30);
53 l_org_category varchar2(30);
54 l_pt_org_name varchar2(100);
55 l_am_org_name varchar2(100);
56 l_assignment_rec pv_assign_util_pvt.ASSIGNMENT_REC_TYPE;
57 l_assignment_id number;
58 l_source_id number;
59 l_user_id number;
60 l_vad_id number;
61 l_pt_org_party_id number;
62 l_routing_status varchar2(30);
63 l_wf_status varchar2(30);
64 l_no_channel_mgrs boolean := TRUE;
65 l_temp_id number;
66 l_lead_number number;
67 l_entity_amount varchar2(100);
68 l_customer_id number;
69 l_customer_name varchar2(360);
70 l_entity_name varchar2(240);
71 l_address_id number;
72 l_lead_contact_id number;
73 l_bulk_running_count pls_integer := 0;
74 l_new_resource_count pls_integer := 0;
75 l_highest_rank_pt_row pls_integer := 1;
76 l_lead_workflow_id number;
77 l_prm_keep_flag varchar2(1);
78 l_access_pt_id number;
79 l_chk_pt_status_id number;
80 l_resource_id NUMBER;
81 l_access_id NUMBER;
82
83 l_has_cm_decision_maker varchar2(1);
84 l_has_pt_decision_maker varchar2(1);
85
86 l_attrib_values_rec pv_assignment_pvt.attrib_values_rec_type;
87 l_partner_id_tbl JTF_NUMBER_TABLE;
88
89 l_party_notify_rec_tbl pv_assignment_pvt.party_notify_rec_tbl_type;
90 l_rs_details_tbl pv_assign_util_pvt.resource_details_tbl_type := pv_assign_util_pvt.resource_details_tbl_type();
91 l_lead_workflow_rec pv_assign_util_pvt.lead_workflow_rec_type;
92 l_ENTYRLS_rec PV_RULE_RECTYPE_PUB.ENTYRLS_Rec_Type := PV_RULE_RECTYPE_PUB.G_MISS_ENTYRLS_REC;
93 x_entity_rule_applied_id NUMBER;
94
95 l_oppty_routing_log_rec PV_ASSIGNMENT_PVT.oppty_routing_log_rec_type;
96
97
98 -- --------------------------------------------------------------------------------
99 -- Checks if the opportunity is "open".
100 -- --------------------------------------------------------------------------------
101 CURSOR lc_check_open_status IS
102 SELECT b.opp_open_status_flag
103 FROM as_leads_all a,
104 as_statuses_b b
105 WHERE a.lead_id = p_lead_id AND
106 a.status = b.status_code AND
107 b.opp_flag = 'Y';
108
109
110 cursor lc_get_assign_type_meaning (pc_assignment_type varchar2) is
111 select meaning from pv_lookups
112 where lookup_type = 'PV_ASSIGNMENT_TYPE'
113 and lookup_code = pc_assignment_type;
114
115 cursor lc_get_pt_org_name (pc_partner_id number) is
116 select pt.party_name
117 from hz_relationships pr,
118 hz_organization_profiles op,
119 hz_parties pt
120 where pr.party_id = pc_partner_id
121 and pr.subject_table_name = 'HZ_PARTIES'
122 and pr.object_table_name = 'HZ_PARTIES'
123 and pr.status in ('A', 'I')
124 and pr.object_id = op.party_id
125 and op.internal_flag = 'Y'
126 and op.effective_end_date is null
127 and pr.subject_id = pt.party_id
128 and pt.status in ('A', 'I');
129
130
131 cursor lc_opportunity (pc_lead_id number) is
132 select ld.customer_id,
133 ld.address_id,
134 ld.lead_number,
135 ld.description,
136 ld.total_amount||' '||ld.currency_code,
137 pt.party_name,
138 lc.lead_contact_id
139 from as_leads_all ld,
140 hz_parties pt,
141 as_lead_contacts lc
142 where ld.lead_id = pc_lead_id
143 and ld.customer_id = pt.party_id (+)
144 and ld.lead_id = lc.lead_id (+) for update of ld.lead_id;
145
146 cursor lc_get_user_type (pc_username varchar2) is
147 select extn.category,
148 extn.source_id,
149 fuser.user_id
150 from fnd_user fuser,
151 jtf_rs_resource_extns extn
152 where fuser.user_name = pc_username
153 and fuser.user_id = extn.user_id;
154
155 cursor lc_get_am_org (pc_user_source_id number) is
156 select otl.name vendor_name
157 from hr_all_organization_units o,
158 hr_all_organization_units_tl otl,
159 per_all_people_f p
160 where o.organization_id = otl.organization_id
161 and otl.language = userenv('lang')
162 and o.organization_id = p.business_group_id
163 and p.person_id = pc_user_source_id;
164
165 cursor lc_get_pt_org_id (pc_user_source_id number) is
166 select emp.object_id pt_org_id,
167 hp.party_name,
168 prof.partner_id
169 from hz_relationships emp,
170 pv_partner_profiles prof,
171 hz_parties hp
172 where emp.party_id = pc_user_source_id
173 and emp.subject_table_name = 'HZ_PARTIES'
174 and emp.object_table_name = 'HZ_PARTIES'
175 and emp.directional_flag = 'F'
176 and emp.relationship_code = 'EMPLOYEE_OF'
177 and emp.relationship_type = 'EMPLOYMENT'
178 and emp.status in ('A', 'I')
179 and emp.object_id = prof.partner_party_id
180 and emp.object_id = hp.party_id
181 and hp.status in ('A', 'I');
182
183
184 cursor lc_get_lead_workflow_id (pc_item_key varchar2)
185 is
186 select lead_workflow_id
187 from pv_lead_workflows
188 where wf_item_type = 'PVASGNMT'
189 and wf_item_key = pc_item_key;
190
191 -- Start : Rivendell changes
192 cursor lc_get_access_details ( pc_lead_id NUMBER)
193 is
194 select partner_customer_id, prm_keep_flag
195 from as_accesses_all
196 where lead_id = pc_lead_id;
197 -- End : Rivendell changes
198
199 -- changin the cursor to check if the partner is inactive or not.
200 -- Checking if any of the partners are inactive.
201 -- for bug# 4325252
202
203 cursor lc_chk_pt_status (pc_partner_id number) is
204 select 1 num
205 from pv_partner_profiles pvpp
206 where pvpp.partner_id = pc_partner_id
207 and nvl(pvpp.status,'I') <> 'A';
208
209 cursor lc_validate_vad_pt (pc_partner_id number, pc_vad_id number) is
210 select PT_ORG.party_name
211 from
212 pv_partner_accesses PT_ACCESS,
213 pv_partner_profiles PT_PROF,
214 hz_parties PT_ORG
215 where
216 PT_ACCESS.partner_id = pc_partner_id
217 and PT_ACCESS.partner_id = PT_PROF.partner_id
218 and PT_PROF.status = 'A'
219 and PT_PROF.partner_party_id = PT_ORG.party_id
220 and PT_ORG.status in ('A', 'I')
221 and PT_ACCESS.vad_partner_id = pc_vad_id;
222
223 -- Start : Rivendell changes
224 CURSOR get_resource_id ( pc_partner_id NUMBER)
225 IS
226 SELECT resource_id
227 FROM jtf_rs_resource_extns
228 WHERE source_id = pc_partner_id
229 AND category = 'PARTNER';
230 -- End : Rivendell changes
231
232 BEGIN
233
234 -- Standard call to check for call compatibility.
235 IF NOT FND_API.Compatible_API_Call(l_api_version_number,
236 p_api_version_number,
237 l_api_name,
238 G_PKG_NAME)
239 THEN
240 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
241 END IF;
242
243
244 -- Initialize message list if p_init_msg_list is set to TRUE.
245 IF FND_API.to_Boolean( p_init_msg_list ) THEN
246 fnd_msg_pub.initialize;
247 END IF;
248
249 /* if fnd_profile.value('ASF_PROFILE_DEBUG_MSG_ON') = 'Y' then
250 FND_MSG_PUB.g_msg_level_threshold := FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW;
251 else
252 FND_MSG_PUB.g_msg_level_threshold := FND_API.G_MISS_NUM;
253 end if; */
254
255 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
256 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
257 fnd_message.Set_Token('TEXT', 'In ' || l_api_name);
258 fnd_msg_pub.Add;
259 END IF;
260
261 -- Initialize API return status to success
262 x_return_status := FND_API.G_RET_STS_SUCCESS;
263
264 if (p_assignment_type is NULL) or
265 (p_assignment_type NOT IN (pv_workflow_pub.g_wf_lkup_single,
266 pv_workflow_pub.g_wf_lkup_serial,
267 pv_workflow_pub.g_wf_lkup_joint,
268 pv_workflow_pub.g_wf_lkup_broadcast)) then
269
270 fnd_message.SET_NAME('PV', 'PV_INVALID_ASSIGN_TYPE');
271 fnd_message.SET_TOKEN('TYPE' , p_assignment_type);
272 fnd_msg_pub.ADD;
273 raise FND_API.G_EXC_ERROR;
274
275 end if;
276
277 if p_bypass_cm_ok_flag is NULL then
278
279 fnd_message.SET_NAME('PV', 'PV_DEBUG_MESSAGE');
280 fnd_message.SET_TOKEN('TEXT' , 'Bypass CM OK Flag Cannot be Null');
281 fnd_msg_pub.ADD;
282 raise FND_API.G_EXC_ERROR;
283
284 end if;
285
286 if (p_entity is NULL) or p_entity not in ('OPPORTUNITY') then
287
288 fnd_message.SET_NAME('PV', 'PV_INVALID_ENTITY_TYPE');
289 fnd_message.SET_TOKEN('TYPE' , p_entity);
290 fnd_msg_pub.ADD;
291 raise FND_API.G_EXC_ERROR;
292
293 end if;
294
295 -- --------------------------------------------------------------------------
296 -- Make sure the opportunity is "open".
297 -- --------------------------------------------------------------------------
298 FOR x IN lc_check_open_status LOOP
299 -- -----------------------------------------------------------------------
300 -- This is not an "open" opportunity. It cannot be routed.
301 -- -----------------------------------------------------------------------
302 IF (x.opp_open_status_flag <> 'Y') THEN
303 FOR x IN lc_opportunity(p_lead_id) LOOP
304 l_entity_name := x.description;
305 END LOOP;
306
307 fnd_message.SET_NAME('PV', 'PV_OPP_ROUTING_CLOSED_OPP');
308 fnd_message.SET_TOKEN('OPPORTUNITY_NAME' , l_entity_name);
309 fnd_message.SET_TOKEN('LEAD_ID' , p_lead_id);
310 fnd_msg_pub.ADD;
311 RAISE FND_API.G_EXC_ERROR;
312 END IF;
313 END LOOP;
314
315
316
317 if (p_partner_id_tbl.count = 0 or p_partner_id_tbl is null) then
318
319 fnd_message.SET_NAME('PV', 'PV_NO_PRTNR_TO_ROUTE');
320 fnd_msg_pub.ADD;
321 raise FND_API.G_EXC_ERROR;
322
323 end if;
324
325 for i in 1..p_partner_id_tbl.count
326 loop
327 l_chk_pt_status_id := null;
328 open lc_chk_pt_status(p_partner_id_tbl(i));
329 fetch lc_chk_pt_status into l_chk_pt_status_id;
330 close lc_chk_pt_status;
331 if l_chk_pt_status_id is not null then exit; end if;
332 end loop;
333
334 if l_chk_pt_status_id is not null then
335 fnd_message.SET_NAME('PV', 'PV_ROUTING_INVALID_PARTNER');
336 --fnd_message.SET_TOKEN('TEXT', 'Status of one or more partner is inactive. Unable to initiate assignment process' );
337 fnd_msg_pub.ADD;
338 raise FND_API.G_EXC_ERROR;
339 end if;
340
341 l_partner_id_tbl := p_partner_id_tbl;
342
343 open lc_get_user_type (pc_username => p_creating_username);
344 fetch lc_get_user_type into l_user_category, l_source_id, l_user_id;
345 close lc_get_user_type;
346
347 if l_user_category is null then
348 fnd_message.SET_NAME('PV', 'PV_INVALID_USER');
349 fnd_message.SET_TOKEN('P_USERNAME', p_creating_username);
350 fnd_msg_pub.ADD;
351 raise FND_API.G_EXC_ERROR;
352 end if;
353
354 open lc_opportunity(pc_lead_id => p_lead_id);
355 fetch lc_opportunity into l_customer_id, l_address_id, l_lead_number, l_entity_name, l_entity_amount,
356 l_customer_name, l_lead_contact_id;
357 close lc_opportunity;
358
359 if l_lead_contact_id is null and fnd_profile.value('PV_OPPTY_CONTACT_REQUIRED') = 'Y' then
360
361 fnd_message.SET_NAME('PV', 'PV_OPPTY_CONTACT_REQD');
362 fnd_msg_pub.ADD;
363 raise FND_API.G_EXC_ERROR;
364
365 end if;
366
367 if l_lead_number is null then
368 fnd_message.SET_NAME('PV', 'PV_LEAD_NOT_FOUND');
369 fnd_message.SET_TOKEN('LEAD_ID', p_lead_id);
370 fnd_msg_pub.ADD;
371 raise FND_API.G_EXC_ERROR;
372 end if;
373
374 -- ---------------------------------------------------------------------------------
375 -- Initialize record of table. This is not necessary prior to Oracle 10g.
376 -- ---------------------------------------------------------------------------------
377
378 if l_user_category = g_resource_employee then
379
380 l_org_category := g_vendor_org;
381
382 open lc_get_am_org (pc_user_source_id => l_source_id);
383 fetch lc_get_am_org into l_am_org_name;
384 close lc_get_am_org;
385
386 l_oppty_routing_log_rec.vendor_user_id := l_user_id;
387 l_oppty_routing_log_rec.pt_contact_user_id := NULL;
388
389 elsif l_user_category = g_resource_party then
390
391 l_org_category := g_external_org;
392
393 open lc_get_pt_org_id (pc_user_source_id => l_source_id);
394 fetch lc_get_pt_org_id into l_pt_org_party_id, l_am_org_name, l_vad_id;
395 close lc_get_pt_org_id;
396
397 l_oppty_routing_log_rec.vendor_user_id := NULL;
398 l_oppty_routing_log_rec.pt_contact_user_id := l_user_id;
399
400
401 if l_pt_org_party_id is null then
402 fnd_message.SET_NAME('PV', 'PV_USER_ORG_NOT_FOUND');
403 fnd_message.SET_TOKEN('P_USER_NAME' ,p_creating_username );
404 fnd_msg_pub.ADD;
405 raise FND_API.G_EXC_ERROR;
406 end if;
407
408 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
409 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
410 fnd_message.Set_token('TEXT', 'p_partner_id_tbl.count:' || p_partner_id_tbl.count);
411 fnd_msg_pub.Add;
412 end if;
413
414
415 if p_partner_id_tbl.count = 1 then
416
417 -- check to see if VAD submitted routing to himself (meaning he wants to work on it)
418 -- we determine this by checking if the partner_id's subject_id passed in is the same
419 -- as the logged in user company party_id
420 -- Routing Status will become Active
421
422 if l_vad_id = p_partner_id_tbl(1) then
423
424 -- VAD wants to work on it themselves
425
426 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
427 fnd_message.SET_NAME('PV', 'PV_DEBUG_MESSAGE');
428 fnd_message.SET_TOKEN('TEXT' , 'VAD submitting routing to themselves');
429 fnd_msg_pub.ADD;
430 end if;
431
432 pv_assign_util_pvt.GetWorkflowID (p_api_version_number => 1.0,
433 p_init_msg_list => FND_API.G_FALSE,
434 p_commit => FND_API.G_FALSE,
435 p_validation_level => p_validation_level,
436 p_lead_id => p_lead_id,
437 p_entity => p_entity,
438 x_itemType => l_itemType,
439 x_itemKey => l_itemKey,
440 x_routing_status => l_routing_status,
441 x_wf_status => l_wf_status,
442 x_return_status => x_return_status,
443 x_msg_count => x_msg_count,
444 x_msg_data => x_msg_data);
445
446 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
447 raise FND_API.G_EXC_ERROR;
448 end if;
449
450 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
451 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
452 fnd_message.Set_token('TEXT', 'Calling pv_assignment_pvt.update_routing_stage' );
453 fnd_msg_pub.Add;
454 end if;
455
456 pv_assignment_pvt.update_routing_stage (
457 p_api_version_number => 1.0,
458 p_init_msg_list => FND_API.G_FALSE,
459 p_commit => FND_API.G_FALSE,
460 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
461 p_itemType => l_itemtype,
462 p_itemKey => l_itemKey,
463 p_routing_stage => pv_assignment_pub.g_r_status_active,
464 p_active_but_open_flag => 'N',
465 x_return_status => x_return_status,
466 x_msg_count => x_msg_count,
467 x_msg_data => x_msg_data);
468
469 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
470 raise FND_API.G_EXC_ERROR;
471 end if;
472 -- Oppty_Routing_Log Row
473
474
475 return;
476
477 end if; -- l_vad_id = p_partner_id_tbl(1)
478 end if; -- p_partner_id_tbl.count = 1
479
480 for i in 1 .. p_partner_id_tbl.count loop
481
482 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
483 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
484 fnd_message.Set_token('TEXT', 'Running the cursor lc_validate_vad_pt for partner id:' || p_partner_id_tbl(i));
485 fnd_msg_pub.Add;
486 end if;
487
488 open lc_validate_vad_pt (pc_partner_id => p_partner_id_tbl(i), pc_vad_id => l_vad_id);
489 fetch lc_validate_vad_pt into l_pt_org_name;
490 close lc_validate_vad_pt;
491
492 if l_pt_org_name is null then
493
494 if l_vad_id = p_partner_id_tbl(i) and p_assignment_type <> pv_workflow_pub.g_wf_lkup_joint then
495
496 fnd_message.SET_NAME('PV', 'PV_SELF_ADD_JOINT_ONLY');
497 fnd_msg_pub.ADD;
498 raise FND_API.G_EXC_ERROR;
499
500 elsif l_vad_id = p_partner_id_tbl(i) and p_assignment_type = pv_workflow_pub.g_wf_lkup_joint then
501 null;
502 else
503
504 open lc_get_pt_org_name (pc_partner_id => l_partner_id_tbl(i));
505 fetch lc_get_pt_org_name into l_pt_org_name;
506 close lc_get_pt_org_name;
507
508 fnd_message.SET_NAME('PV', 'PV_NOT_INDIRECTLY_MANAGED');
509 fnd_message.SET_TOKEN('P_PARTNER_NAME' , l_pt_org_name);
510 fnd_msg_pub.ADD;
511 raise FND_API.G_EXC_ERROR;
512
513 end if;
514 end if;
515
516 end loop;
517
518 else
519 fnd_message.SET_NAME('PV', 'PV_USER_NOT_VALID_CATEGORY');
520 fnd_message.SET_TOKEN('P_USER_NAME' ,p_creating_username);
521 fnd_msg_pub.ADD;
522 raise FND_API.G_EXC_ERROR;
523 end if; -- l_user_category = g_resource_party
524
525 if p_assignment_type = pv_workflow_pub.g_wf_lkup_serial then
526 for v_count IN 1..l_partner_id_tbl.count loop
527
528 if p_rank_tbl(v_count) IS NULL THEN
529 fnd_message.SET_NAME('PV', 'PV_DEBUG_MESSAGE');
530 fnd_message.SET_TOKEN('TEXT' , 'Rank cannot be null for Serial Assignment');
531 fnd_msg_pub.ADD;
532
533 raise FND_API.G_EXC_ERROR;
534 end if;
535 end loop;
536 end if; -- p_assignment_type = pv_workflow_pub.g_wf_lkup_serial
537
538 if (p_assignment_type = pv_workflow_pub.g_wf_lkup_single and p_partner_id_tbl.count > 1) then
539
540 for v_count IN 1..l_partner_id_tbl.count loop
541
542 if p_rank_tbl(v_count) IS NULL THEN
543 fnd_message.SET_NAME('PV', 'PV_DEBUG_MESSAGE');
544 fnd_message.SET_TOKEN('TEXT' , 'Rank cannot be null ');
545 fnd_msg_pub.ADD;
546
547 raise FND_API.G_EXC_ERROR;
548 end if;
549
550 if p_rank_tbl(v_count) < p_rank_tbl(l_highest_rank_pt_row) then
551 l_highest_rank_pt_row := v_count;
552 end if;
553
554 end loop;
555
556 for v_count IN 1..l_partner_id_tbl.count loop
557
558 if v_count <> l_highest_rank_pt_row then
559 l_partner_id_tbl(v_count) := NULL;
560 end if;
561
562 end loop;
563
564 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
565 fnd_message.SET_NAME('PV', 'PV_DEBUG_MESSAGE');
566 fnd_message.SET_TOKEN('TEXT' , 'Only 1 partner allowed in SINGLE assignment. ' ||
567 'Highest ranked partner selected: ' || l_partner_id_tbl(l_highest_rank_pt_row));
568 fnd_msg_pub.ADD;
569 end if;
570
571 end if;
572
573 for i in 1..p_partner_source_tbl.count loop
574
575 if p_partner_source_tbl(i) is NULL OR
576 p_partner_source_tbl(i) not in ('CAMPAIGN', 'MATCHING', 'TAP', 'SALESTEAM') then
577
578 fnd_message.SET_NAME('PV', 'PV_NOT_VALID_SOURCE_TYPE');
579 fnd_message.SET_TOKEN('P_SOURCE_TYPE' ,p_partner_source_tbl(i));
580 fnd_message.SET_TOKEN('P_PARTNER_ID', p_partner_id_tbl(i) );
581 fnd_msg_pub.ADD;
582
583 raise FND_API.G_EXC_ERROR;
584
585 end if;
586
587 end loop;
588
589 -- ----------------------------------------------------------------------
590 -- setting PRM_KEEP_FLAG to 'Y' for sales team partners
591 -- ----------------------------------------------------------------------
592
593 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
594 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
595 fnd_message.Set_token('TEXT', 'Running lc_get_access_details cursor' );
596 fnd_msg_pub.Add;
597 end if;
598
599 open lc_get_access_details(p_lead_id);
600 loop
601 fetch lc_get_access_details into l_access_pt_id, l_prm_keep_flag;
602 exit when lc_get_access_details%NOTFOUND;
603 /*
604 if l_prm_keep_flag is null OR l_prm_keep_flag = 'N'
605 then
606 for i in 1 .. p_partner_id_tbl.count loop
607
608 if p_partner_id_tbl(i) = l_access_pt_id then
609
610 update as_accesses_all set prm_keep_flag = 'Y'
611 where partner_customer_id = l_access_pt_id
612 and lead_id = p_lead_id;
613
614 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
615 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
616 fnd_message.Set_Token('TEXT', 'Setting prm_keep_flag to Yes for the partner org ');
617 fnd_msg_pub.Add;
618 END IF;
619 end if;
620 end loop;
621 end if;
622 */
623 -- Start: Rivendell Changes
624 -- vansub
625
626 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
627 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
628 fnd_message.Set_token('TEXT', 'Navigating through partner id table and call pv_assign_util_pvt.updateaccess' );
629 fnd_msg_pub.Add;
630 end if;
631
632 FOR i IN 1 .. l_partner_id_tbl.count
633 LOOP
634
635 IF l_access_pt_id IS NULL THEN
636
637 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
638 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
639 fnd_message.Set_token('TEXT', 'Getting resource_id for partner id:' || p_partner_id_tbl(i));
640 fnd_msg_pub.Add;
641 end if;
642
643 OPEN get_resource_id ( l_partner_id_tbl(i));
644 FETCH get_resource_id INTO l_resource_id;
645 CLOSE get_resource_id;
646
647 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
648 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
649 fnd_message.Set_token('TEXT', 'CAlling pv_assign_util_pvt.updateaccess for resource_id:' || l_resource_id);
650 fnd_msg_pub.Add;
651 end if;
652
653 pv_assign_util_pvt.UpdateAccess(
654 p_api_version_number => 1.0,
655 p_init_msg_list => FND_API.G_FALSE,
656 p_commit => FND_API.G_FALSE,
657 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
658 p_itemtype => l_itemtype,
659 p_itemkey => l_itemKey,
660 p_current_username => p_creating_username,
661 p_lead_id => p_lead_id,
662 p_customer_id => l_customer_id,
663 p_address_id => l_address_id,
664 p_access_action => pv_assignment_pub.G_ADD_ACCESS,
665 p_resource_id => l_resource_id,
666 p_access_type => pv_assignment_pub.G_PT_ORG_ACCESS,
667 x_access_id => l_access_id,
668 x_return_status => x_return_status,
669 x_msg_count => x_msg_count,
670 x_msg_data => x_msg_data);
671
672 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
673 fnd_message.SET_NAME('PV', 'PV_DEBUG_MESSAGE');
674 fnd_message.SET_TOKEN('TEXT' , 'Added partner to the sales team ..Access Id :'||l_access_id);
675 fnd_msg_pub.ADD;
676 end if;
677 ELSE
678 IF l_partner_id_tbl(i) = l_access_pt_id THEN
679
680 IF l_prm_keep_flag IS NULL OR l_prm_keep_flag = 'N' THEN
681 UPDATE as_accesses_all
682 SET prm_keep_flag = 'Y'
683 WHERE partner_customer_id = l_access_pt_id
684 AND lead_id = p_lead_id;
685
686 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
687 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
688 fnd_message.Set_Token('TEXT', 'Setting prm_keep_flag to Yes for the partner org ');
689 fnd_msg_pub.Add;
690 END IF;
691 END IF;
692 END IF;
693 END IF;
694 END LOOP;
695 -- End: Rivendell Changes
696 END LOOP;
697
698 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
699 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
700 fnd_message.Set_token('TEXT', 'Calling pv_assign_util_pvt.getWorkFlowId API' );
701 fnd_msg_pub.Add;
702 end if;
703
704 pv_assign_util_pvt.GetWorkflowID (p_api_version_number => 1.0,
705 p_init_msg_list => FND_API.G_FALSE,
706 p_commit => FND_API.G_FALSE,
707 p_validation_level => p_validation_level,
708 p_lead_id => p_lead_id,
709 p_entity => p_entity,
710 x_itemType => l_itemType,
711 x_itemKey => l_itemKey,
712 x_routing_status => l_routing_status,
713 x_wf_status => l_wf_status,
714 x_return_status => x_return_status,
715 x_msg_count => x_msg_count,
716 x_msg_data => x_msg_data);
717
718 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
719 raise FND_API.G_EXC_ERROR;
720 end if;
721
722 if l_wf_status = g_wf_status_open or l_routing_status = g_r_status_active then
723
724 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
725 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
726 fnd_message.Set_token('TEXT', 'IN if l_wf_status = g_wf_status_open or l_routing_status = g_r_status_active' );
727 fnd_msg_pub.Add;
728 end if;
729
730 fnd_message.SET_NAME('PV', 'PV_EXISTING_WORKFLOW');
731 fnd_message.SET_TOKEN('P_LEAD_ID' ,p_lead_id);
732 fnd_msg_pub.ADD;
733 raise FND_API.G_EXC_ERROR;
734
735 elsif l_wf_status in ('NEW', g_wf_status_closed) then
736
737 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
738 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
739 fnd_message.Set_token('TEXT', 'IN elsif l_wf_status is NEW or g_wf_status_closed then' );
740 fnd_msg_pub.Add;
741 end if;
742
743 -- the following is executed for new, recycled and abandoned workflows only
744
745 l_itemtype := pv_workflow_pub.g_wf_itemtype_pvasgnmt;
746
747 l_lead_workflow_rec.created_by := l_user_id;
748 l_lead_workflow_rec.last_updated_by := l_user_id;
749 l_lead_workflow_rec.lead_id := p_lead_id;
750 l_lead_workflow_rec.entity := p_entity;
751 l_lead_workflow_rec.wf_item_type := l_itemtype;
752 l_lead_workflow_rec.routing_type := p_assignment_type;
753 l_lead_workflow_rec.routing_status := pv_assignment_pub.g_r_status_matched;
754 l_lead_workflow_rec.wf_status := pv_assignment_pub.g_wf_status_open;
755 l_lead_workflow_rec.bypass_cm_ok_flag := p_bypass_cm_ok_flag;
756 l_lead_workflow_rec.latest_routing_flag := 'Y';
757
758 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
759 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
760 fnd_message.Set_token('TEXT', 'Calling pv_assign_util_pvt.Create_LEad_Workflow_Row' );
761 fnd_msg_pub.Add;
762 end if;
763
764 pv_assign_util_pvt.Create_lead_workflow_row (
765 p_api_version_number => 1.0,
766 p_init_msg_list => FND_API.G_FALSE,
767 p_commit => FND_API.G_FALSE,
768 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
769 p_workflow_rec => l_lead_workflow_rec,
770 x_ItemKey => l_itemkey,
771 x_return_status => x_return_status,
772 x_msg_count => x_msg_count,
773 x_msg_data => x_msg_data);
774
775 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
776 raise FND_API.G_EXC_ERROR;
777 end if;
778
779 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
780 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
781 fnd_message.Set_token('TEXT', 'Calling pv_assignment_pvt.set_current_routing_flag' );
782 fnd_msg_pub.Add;
783 end if;
784 pv_assignment_pvt.set_current_routing_flag (
785 p_api_version_number => 1.0,
786 p_init_msg_list => FND_API.G_FALSE,
787 p_commit => FND_API.G_FALSE,
788 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
789 p_ItemKey => l_itemkey,
790 p_Entity => p_entity,
791 p_entity_id => p_lead_id,
792 x_return_status => x_return_status,
793 x_msg_count => x_msg_count,
794 x_msg_data => x_msg_data);
795
796 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
797 raise FND_API.G_EXC_ERROR;
798 end if;
799
800 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
801 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
802 fnd_message.Set_token('TEXT', 'Update as_leads_all table with auto_assignment_type = PRM, prm_assignment_type = p_assignment_type' );
803 fnd_msg_pub.Add;
804 end if;
805
806 update as_leads_all
807 set auto_assignment_type = 'PRM', prm_assignment_type = p_assignment_type
808 where lead_id = p_lead_id;
809
810 -- Added part of Rivendell Changes
811 -- New Table pv_oppty_routing_logs created to log all the routing changes
812 -- for the Routing History Screen
813
814 l_oppty_routing_log_rec.event := 'OPPTY_ASSIGN';
815 l_oppty_routing_log_rec.lead_id := p_lead_id;
816 l_oppty_routing_log_rec.lead_workflow_id := TO_NUMBER(l_itemkey);
817 l_oppty_routing_log_rec.routing_type := p_assignment_type;
818 l_oppty_routing_log_rec.latest_routing_flag := 'Y';
819 l_oppty_routing_log_rec.bypass_cm_flag := p_bypass_cm_ok_flag;
820 l_oppty_routing_log_rec.lead_assignment_id := NULL;
821 l_oppty_routing_log_rec.event_date := SYSDATE;
822 l_oppty_routing_log_rec.user_response := NULL;
823 l_oppty_routing_log_rec.reason_code := NULL;
824 l_oppty_routing_log_rec.user_type := 'LAM';
825
826 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
827 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
828 fnd_message.Set_token('TEXT', 'Calling pv_assignment_pvt.Create_Oppty_Routing_Log_Row' );
829 fnd_msg_pub.Add;
830 end if;
831
832 pv_assignment_pvt.Create_Oppty_Routing_Log_Row (
833 p_api_version_number => 1.0,
834 p_init_msg_list => FND_API.G_FALSE,
835 p_commit => FND_API.G_FALSE,
836 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
837 P_oppty_routing_log_rec => l_oppty_routing_log_rec,
838 x_return_status => x_return_status,
839 x_msg_count => x_msg_count,
840 x_msg_data => x_msg_data);
841
842 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
843 RAISE FND_API.G_EXC_ERROR;
844 END IF;
845
846 for v_count IN 1..l_partner_id_tbl.count loop
847
848 l_assignment_rec := NULL;
849
850 if l_partner_id_tbl(v_count) IS NOT NULL then
851
852 l_rs_details_tbl.delete; -- since we are using NOCOPY, need to
853 -- blank out before calling get_partner_info
854
855 if nvl(l_vad_id,-9999) <> l_partner_id_tbl(v_count) then
856
857 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
858 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
859 fnd_message.Set_token('TEXT', 'Getting partner info using pv_assign_util_pvt.get_partner_info for partner id:' || l_partner_id_tbl(v_count) );
860 fnd_msg_pub.Add;
861 end if;
862
863 pv_assign_util_pvt.get_partner_info(
864 p_api_version_number => 1.0
865 ,p_init_msg_list => FND_API.G_FALSE
866 ,p_commit => FND_API.G_FALSE
867 ,p_mode => l_org_category
868 ,p_partner_id => l_partner_id_tbl(v_count)
869 ,p_entity => p_entity
870 ,p_entity_id => p_lead_id
871 ,p_retrieve_mode => 'BOTH'
872 ,x_rs_details_tbl => l_rs_details_tbl
873 ,x_vad_id => l_vad_id
874 ,x_return_status => x_return_status
875 ,x_msg_count => x_msg_count
876 ,x_msg_data => x_msg_data);
877
878 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
879 raise FND_API.G_EXC_ERROR;
880 end if;
881
882 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
883 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
884 fnd_message.Set_Token('TEXT', 'Size of l_rs_details_tbl: ' || l_rs_details_tbl.count);
885 fnd_msg_pub.Add;
886 END IF;
887
888 if l_rs_details_tbl.count > 0 then
889
890 l_has_cm_decision_maker := 'N';
891 l_has_pt_decision_maker := 'N';
892
893 for i in 1 .. l_rs_details_tbl.count loop
894
895 if l_rs_details_tbl(i).notification_type = g_notify_type_matched_to and
896 l_rs_details_tbl(i).decision_maker_flag = 'Y' then
897
898 l_has_cm_decision_maker := 'Y';
899
900 elsif l_rs_details_tbl(i).notification_type = g_notify_type_offered_to and
901 l_rs_details_tbl(i).decision_maker_flag = 'Y' then
902
903 l_has_pt_decision_maker := 'Y';
904
905 end if;
906
907 end loop;
908
909 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
910 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
911 fnd_message.Set_Token('TEXT', 'Has CM decision maker: ' || l_has_cm_decision_maker ||
912 ' Has PT decision maker: ' || l_has_pt_decision_maker);
913 fnd_msg_pub.Add;
914 END IF;
915
916 if l_has_cm_decision_maker <> 'Y' or l_has_pt_decision_maker <> 'Y' then
917 open lc_get_pt_org_name (pc_partner_id => l_partner_id_tbl(v_count));
918 fetch lc_get_pt_org_name into l_pt_org_name;
919 close lc_get_pt_org_name;
920 end if;
921
922 if l_has_cm_decision_maker <> 'Y' and p_bypass_cm_ok_flag = 'N' then
923 fnd_message.SET_NAME('PV', 'PV_NO_CM_DECISION_MAKER');
924 fnd_message.SET_TOKEN('P_PARTNER_NAME' , l_pt_org_name);
925 fnd_msg_pub.ADD;
926 raise FND_API.G_EXC_ERROR;
927 end if;
928
929 if l_has_pt_decision_maker <> 'Y' then
930 fnd_message.SET_NAME('PV', 'PV_NO_PT_DECISION_MAKER');
931 fnd_message.SET_TOKEN('P_PARTNER_NAME' , l_pt_org_name);
932 fnd_msg_pub.ADD;
933 raise FND_API.G_EXC_ERROR;
934 end if;
935
936 end if;
937
938 END IF;
939
940 l_assignment_rec.lead_id := p_lead_id;
941 l_assignment_rec.related_party_id := l_vad_id;
942
943 if l_vad_id is not null then
944 l_assignment_rec.related_party_access_code := g_assign_access_update;
945 end if;
946
947 l_assignment_rec.partner_id := l_partner_id_tbl(v_count);
948 l_assignment_rec.assign_sequence := p_rank_tbl(v_count);
949 l_assignment_rec.source_type := p_partner_source_tbl(v_count);
950 l_assignment_rec.object_version_number := 0;
951 l_assignment_rec.status_date := SYSDATE;
952
953 if nvl(l_vad_id, -9999) = l_partner_id_tbl(v_count) then
954
955 l_assignment_rec.status := pv_assignment_pub.g_la_status_pt_created;
956 l_assignment_rec.partner_access_code := g_assign_access_update;
957
958 else
959
960 l_assignment_rec.status := pv_assignment_pub.g_la_status_assigned;
961 l_assignment_rec.partner_access_code := g_assign_access_none;
962
963 end if;
964
965 l_assignment_rec.wf_item_type := l_itemType;
966 l_assignment_rec.wf_item_key := l_itemKey;
967
968 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
969 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
970 fnd_message.Set_token('TEXT', 'Calling pv_assign_util_pvt.Create_LEad_assignment_Row' );
971 fnd_msg_pub.Add;
972 end if;
973
974 pv_assign_util_pvt.Create_lead_assignment_row (
975 p_api_version_number => 1.0,
976 p_init_msg_list => FND_API.G_FALSE,
977 p_commit => FND_API.G_FALSE,
978 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
979 p_assignment_rec => l_assignment_rec,
980 x_lead_assignment_id => l_assignment_id,
981 x_return_status => x_return_status ,
982 x_msg_count => x_msg_count ,
983 x_msg_data => x_msg_data);
984
985 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
986 raise FND_API.G_EXC_ERROR;
987 end if;
988
989 if l_rs_details_tbl.count > 0 then
990
991 l_new_resource_count := l_rs_details_tbl.count;
992
993 l_party_notify_rec_tbl.WF_ITEM_TYPE.extend (l_new_resource_count);
994 l_party_notify_rec_tbl.WF_ITEM_KEY.extend (l_new_resource_count);
995 l_party_notify_rec_tbl.LEAD_ASSIGNMENT_ID.extend (l_new_resource_count);
996 l_party_notify_rec_tbl.NOTIFICATION_TYPE.extend (l_new_resource_count);
997 l_party_notify_rec_tbl.RESOURCE_ID.extend (l_new_resource_count);
998 l_party_notify_rec_tbl.USER_ID.extend (l_new_resource_count);
999 l_party_notify_rec_tbl.USER_NAME.extend (l_new_resource_count);
1000 l_party_notify_rec_tbl.RESOURCE_RESPONSE.extend (l_new_resource_count);
1001 l_party_notify_rec_tbl.RESPONSE_DATE.extend (l_new_resource_count);
1002 l_party_notify_rec_tbl.DECISION_MAKER_FLAG.extend(l_new_resource_count);
1003
1004 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
1005 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
1006 fnd_message.Set_Token('TEXT', 'Adding to pv_party_notifications the following:');
1007 fnd_msg_pub.Add;
1008 END IF;
1009
1010 for i in l_bulk_running_count + 1 .. l_party_notify_rec_tbl.wf_item_type.count loop
1011
1012 l_party_notify_rec_tbl.WF_ITEM_TYPE(i) := l_itemtype;
1013 l_party_notify_rec_tbl.WF_ITEM_KEY(i) := l_itemkey;
1014 l_party_notify_rec_tbl.LEAD_ASSIGNMENT_ID(i) := l_assignment_id;
1015 l_party_notify_rec_tbl.NOTIFICATION_TYPE(i) := l_rs_details_tbl(i - l_bulk_running_count).notification_type;
1016 l_party_notify_rec_tbl.RESOURCE_ID(i) := l_rs_details_tbl(i - l_bulk_running_count).resource_id;
1017 l_party_notify_rec_tbl.USER_ID(i) := l_rs_details_tbl(i - l_bulk_running_count).user_id;
1018 l_party_notify_rec_tbl.USER_NAME(i) := l_rs_details_tbl(i - l_bulk_running_count).user_name;
1019 l_party_notify_rec_tbl.DECISION_MAKER_FLAG(i):= l_rs_details_tbl(i - l_bulk_running_count).decision_maker_flag;
1020
1021 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
1022 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
1023 fnd_message.Set_Token('TEXT', 'Assignment ID: ' || l_assignment_id ||
1024 '. Notification type: ' || l_party_notify_rec_tbl.NOTIFICATION_TYPE(i) ||
1025 '. Decision maker flag: ' || l_party_notify_rec_tbl.decision_maker_flag(i) ||
1026 '. Username: ' || l_party_notify_rec_tbl.USER_NAME(i));
1027 fnd_msg_pub.Add;
1028 END IF;
1029
1030 end loop;
1031
1032 l_bulk_running_count := l_bulk_running_count + l_rs_details_tbl.count;
1033
1034 end if;
1035
1036 end if; -- l_partner_id_tbl(v_count) is not null
1037
1038 end loop; -- l_partner_id_tbl(count)
1039
1040 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
1041 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
1042 fnd_message.Set_token('TEXT', 'Calling pv_assignment_pvt.bulk_cr_party_notification' );
1043 fnd_msg_pub.Add;
1044 end if;
1045
1046 pv_assignment_pvt.bulk_cr_party_notification(
1047 p_api_version_number => 1.0
1048 ,p_init_msg_list => FND_API.G_FALSE
1049 ,p_commit => FND_API.G_FALSE
1050 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
1051 ,P_party_notify_Rec_tbl => l_party_notify_rec_tbl
1052 ,x_return_status => x_return_status
1053 ,x_msg_count => x_msg_count
1054 ,x_msg_data => x_msg_data);
1055
1056 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
1057 raise FND_API.G_EXC_ERROR;
1058 end if;
1059
1060 /************************************************************************/
1061 /* write access records for the channel managers, partners are later */
1062 /************************************************************************/
1063
1064 l_no_channel_mgrs := TRUE;
1065
1066 for i in 1 .. l_party_notify_rec_tbl.RESOURCE_ID.count loop
1067
1068 if l_party_notify_rec_tbl.notification_type(i) = pv_assignment_pub.g_notify_type_matched_to then
1069
1070 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
1071 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
1072 fnd_message.Set_token('TEXT', 'Calling pv_assig_util_pvt.update access for CMs resource id:' || l_party_notify_rec_tbl.resource_id(i) );
1073 fnd_msg_pub.Add;
1074 end if;
1075
1076 pv_assign_util_pvt.updateAccess (
1077 p_api_version_number => l_api_version_number,
1078 p_init_msg_list => FND_API.G_FALSE,
1079 p_commit => FND_API.G_FALSE,
1080 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1081 p_itemtype => l_itemType,
1082 p_itemkey => l_itemKey,
1083 p_current_username => p_creating_username,
1084 p_lead_id => p_lead_id,
1085 p_customer_id => l_customer_id,
1086 p_address_id => l_address_id,
1087 p_access_action => pv_assignment_pub.G_ADD_ACCESS,
1088 p_resource_id => l_party_notify_rec_tbl.resource_id(i),
1089 p_access_type => pv_assignment_pub.G_CM_ACCESS,
1090 x_access_id => l_temp_id,
1091 x_return_status => x_return_status,
1092 x_msg_count => x_msg_count,
1093 x_msg_data => x_msg_data);
1094
1095 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
1096 raise FND_API.G_EXC_ERROR;
1097 end if;
1098
1099 l_no_channel_mgrs := FALSE;
1100
1101 end if;
1102
1103 end loop;
1104
1105 if l_no_channel_mgrs then
1106 fnd_message.Set_Name('PV', 'PV_EMPTY_ROLE');
1107 fnd_msg_pub.Add;
1108 RAISE FND_API.G_EXC_ERROR;
1109 end if;
1110
1111 open lc_get_assign_type_meaning (pc_assignment_type => p_assignment_type);
1112 fetch lc_get_assign_type_meaning into l_attrib_values_rec.assignment_type_mean;
1113 close lc_get_assign_type_meaning;
1114
1115 l_attrib_values_rec.org_type := l_org_category;
1116 l_attrib_values_rec.pt_org_party_id := l_pt_org_party_id;
1117 l_attrib_values_rec.am_org_name := l_am_org_name;
1118 l_attrib_values_rec.lead_id := p_lead_id;
1119 l_attrib_values_rec.lead_number := l_lead_number;
1120 l_attrib_values_rec.entity_name := l_entity_name;
1121 l_attrib_values_rec.entity_amount := l_entity_amount;
1122 l_attrib_values_rec.customer_id := l_customer_id;
1123 l_attrib_values_rec.address_id := l_address_id;
1124 l_attrib_values_rec.customer_name := l_customer_name;
1125 l_attrib_values_rec.assignment_type := p_assignment_type;
1126 l_attrib_values_rec.bypass_cm_ok_flag := p_bypass_cm_ok_flag;
1127 l_attrib_values_rec.process_rule_id := p_process_rule_id;
1128 l_attrib_values_rec.process_name := pv_workflow_pub.g_wf_pcs_initiate_assignment;
1129
1130 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
1131 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
1132 fnd_message.Set_Token('TEXT', 'before calling startworkflow Entity Amount'||l_entity_amount);
1133 fnd_msg_pub.Add;
1134 END IF;
1135
1136
1137 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
1138 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
1139 fnd_message.Set_token('TEXT', 'Calling pv_assignment_pvt.StartWorkflow' );
1140 fnd_msg_pub.Add;
1141 end if;
1142
1143 pv_assignment_pvt.StartWorkflow( p_api_version_number => 1.0,
1144 p_init_msg_list => FND_API.G_FALSE,
1145 p_commit => FND_API.G_FALSE,
1146 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1147 p_itemKey => l_itemKey,
1148 p_itemType => l_itemType,
1149 p_creating_username => p_creating_username,
1150 p_attrib_values_rec => l_attrib_values_rec,
1151 x_return_status => x_return_status,
1152 x_msg_count => x_msg_count,
1153 x_msg_data => x_msg_data);
1154
1155 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
1156 raise FND_API.G_EXC_ERROR;
1157 end if;
1158
1159
1160 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
1161 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
1162 fnd_message.Set_Token('TEXT', 'process rule id from create assignment'|| p_process_rule_id);
1163 fnd_msg_pub.Add;
1164 END IF;
1165
1166 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
1167 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
1168 fnd_message.Set_token('TEXT', 'Calling PV_ASSIGN_UTIL_PVT.checkforErrors ' );
1169 fnd_msg_pub.Add;
1170 end if;
1171
1172
1173 PV_ASSIGN_UTIL_PVT.checkforErrors ( p_api_version_number => 1.0
1174 ,p_init_msg_list => FND_API.G_FALSE
1175 ,p_commit => FND_API.G_FALSE
1176 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
1177 ,p_itemtype => l_itemtype
1178 ,p_itemkey => l_itemkey
1179 ,x_return_status => x_return_status
1180 ,x_msg_count => x_msg_count
1181 ,x_msg_data => x_msg_data);
1182
1183 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
1184 raise FND_API.G_EXC_ERROR;
1185 end if;
1186
1187 --
1188 -- End of API body.
1189 --
1190
1191 else
1192 -- invalid wf_status. Should not happen since getworkflowid already checks for it
1193 null;
1194 end if; -- l_wf_status
1195
1196 IF FND_API.To_Boolean ( p_commit ) THEN
1197 COMMIT WORK;
1198 END IF;
1199
1200 -- Standard call to get message count and if count is 1, get message info.
1201 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_FALSE,
1202 p_count => x_msg_count,
1203 p_data => x_msg_data);
1204
1205 FND_MSG_PUB.g_msg_level_threshold := FND_API.G_MISS_NUM;
1206
1207 EXCEPTION
1208
1209 WHEN FND_API.G_EXC_ERROR THEN
1210
1211 x_return_status := FND_API.G_RET_STS_ERROR ;
1212 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_FALSE,
1213 p_count => x_msg_count,
1214 p_data => x_msg_data);
1215
1216 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1217
1218 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1219 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_FALSE,
1220 p_count => x_msg_count,
1221 p_data => x_msg_data);
1222
1223 WHEN OTHERS THEN
1224
1225 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1226 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
1227 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_FALSE,
1228 p_count => x_msg_count,
1229 p_data => x_msg_data);
1230
1231 end CreateAssignment;
1232
1233
1234 procedure process_match_response (
1235 p_api_version_number IN NUMBER,
1236 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1237 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1238 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1239 p_entity in VARCHAR2,
1240 p_user_name IN VARCHAR2,
1241 p_lead_id IN NUMBER,
1242 p_partyTbl in JTF_NUMBER_TABLE,
1243 p_rank_Tbl in JTF_NUMBER_TABLE,
1244 p_statusTbl in JTF_VARCHAR2_TABLE_100, -- CM_APPROVED,CM_REJECTED,CM_ADDED,NOACTION,CM_APP_FOR_PT,CM_ADD_APP_FOR_PT
1245 x_return_status OUT NOCOPY VARCHAR2,
1246 x_msg_count OUT NOCOPY NUMBER,
1247 x_msg_data OUT NOCOPY VARCHAR2) is
1248
1249 l_api_name CONSTANT VARCHAR2(30) := 'process_match_response';
1250 l_api_version_number CONSTANT NUMBER := 1.0;
1251
1252 l_approve_flag boolean := FALSE;
1253 l_reject_flag boolean := FALSE;
1254 l_no_response_flag boolean := FALSE;
1255 l_rejected_cnt NUMBER := 0;
1256 l_response VARCHAR2(50);
1257
1258 l_assignment_id NUMBER;
1259 l_new_lead_assignment_id NUMBER;
1260 l_user_id NUMBER;
1261 l_vad_id NUMBER;
1262 l_cm_rs_id NUMBER;
1263 l_bulk_size NUMBER;
1264 l_partner_id NUMBER;
1265 l_notify_rowid ROWID;
1266 l_assign_sequence PLS_INTEGER;
1267
1268 l_itemtype VARCHAR2(30);
1269 l_itemkey VARCHAR2(30);
1270 l_mode VARCHAR2(30);
1271 l_routing_status VARCHAR2(30);
1272 l_entity VARCHAR2(30);
1273 l_notify_type VARCHAR2(30);
1274 l_decision_maker_flag VARCHAR2(10);
1275
1276
1277 l_assignment_type varchar2(30);
1278 l_assignment_status varchar2(30);
1279 l_match_outcome varchar2(30);
1280 l_pt_response varchar2(30);
1281
1282 l_assignment_rec pv_assign_util_pvt.ASSIGNMENT_REC_TYPE;
1283 l_party_notify_rec_tbl pv_assignment_pvt.party_notify_rec_tbl_type;
1284 l_rs_details_tbl pv_assign_util_pvt.resource_details_tbl_type := pv_assign_util_pvt.resource_details_tbl_type();
1285 l_pt_response_tbl g_varchar_table_type := g_varchar_table_type();
1286
1287 cursor lc_get_assignment_type (pc_lead_id number, pc_entity varchar2) is
1288 select routing_type from pv_lead_workflows
1289 where lead_id = pc_lead_id and entity = pc_entity and latest_routing_flag = 'Y';
1290
1291 cursor lc_get_assignment (pc_lead_id number,
1292 pc_username varchar2) is
1293 select a.wf_item_type
1294 , a.wf_item_key
1295 , a.routing_status
1296 , a.entity
1297 , b.lead_assignment_id
1298 , b.assign_sequence
1299 , b.partner_id
1300 , b.status
1301 , c.rowid
1302 , c.resource_id
1303 , c.decision_maker_flag
1304 , c.notification_type
1305 , c.user_id
1306 from pv_lead_workflows a, pv_lead_assignments b, pv_party_notifications c, fnd_user usr
1307 where a.lead_id = pc_lead_id
1308 and a.wf_status = g_wf_status_open
1309 and a.wf_item_type = b.wf_item_type
1310 and a.wf_item_key = b.wf_item_key
1311 and b.lead_assignment_id = c.lead_assignment_id
1312 and c.user_id = usr.user_id
1313 and usr.user_name = pc_username;
1314
1315 cursor lc_chk_match_outcome (pc_itemtype varchar2,
1316 pc_itemkey varchar2) is
1317 select status
1318 from pv_lead_assignments
1319 where wf_item_type = pc_itemtype
1320 and wf_item_key = pc_itemkey
1321 and status <> g_la_status_pt_created;
1322
1323 begin
1324
1325 -- Standard call to check for call compatibility.
1326 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1327 p_api_version_number,
1328 l_api_name,
1329 G_PKG_NAME)
1330 THEN
1331 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1332 END IF;
1333
1334
1335 -- Initialize message list if p_init_msg_list is set to TRUE.
1336 IF FND_API.to_Boolean( p_init_msg_list )
1337 THEN
1338 fnd_msg_pub.initialize;
1339 END IF;
1340
1341 if fnd_profile.value('ASF_PROFILE_DEBUG_MSG_ON') = 'Y' then
1342 FND_MSG_PUB.g_msg_level_threshold := FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW;
1343 else
1344 FND_MSG_PUB.g_msg_level_threshold := FND_API.G_MISS_NUM;
1345 end if;
1346
1347 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW)
1348 THEN
1349 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
1350 fnd_message.Set_Token('TEXT', 'In ' || l_api_name );
1351 fnd_msg_pub.Add;
1352 END IF;
1353
1354 -- Initialize API return status to success
1355 x_return_status := FND_API.G_RET_STS_SUCCESS;
1356
1357 open lc_get_assignment_type (pc_lead_id => p_lead_id, pc_entity => p_entity);
1358 fetch lc_get_assignment_type into l_assignment_type;
1359 close lc_get_assignment_type;
1360
1361 if l_assignment_type = pv_workflow_pub.g_wf_lkup_serial then
1362
1363 for i in 1 .. p_rank_tbl.count loop
1364
1365 for j in 1+i .. p_rank_tbl.count loop
1366
1367 if p_rank_tbl(i) = p_rank_tbl(j) then
1368 fnd_message.Set_Name('PV', 'PV_DUPLICATE_RANK');
1369 fnd_msg_pub.ADD;
1370 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1371 end if;
1372
1373 end loop;
1374
1375 end loop;
1376
1377 end if;
1378
1379 if l_assignment_type = pv_workflow_pub.g_wf_lkup_single then
1380
1381 if p_partyTbl.count > 1 then
1382
1383 for i in 1 .. p_statusTbl.count loop
1384
1385 if p_statusTbl(i) = PV_ASSIGNMENT_PUB.g_la_status_cm_rejected then
1386 l_rejected_cnt := l_rejected_cnt + 1;
1387 end if;
1388
1389 end loop;
1390
1391 if p_partyTbl.count - l_rejected_cnt > 1 then
1392
1393 fnd_message.Set_Name('PV', 'PV_MULTIPLE_PRTNR_SINGLE');
1394 fnd_msg_pub.ADD;
1395 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1396
1397 end if;
1398 end if;
1399 end if;
1400
1401 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW)
1402 THEN
1403 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
1404 fnd_message.Set_Token('TEXT', 'Getting Assignment Details' );
1405 fnd_msg_pub.Add;
1406 END IF;
1407
1408 open lc_get_assignment (pc_lead_id => p_lead_id,
1409 pc_username => p_user_name);
1410 loop
1411
1412 fetch lc_get_assignment into l_itemtype
1413 , l_itemkey
1414 , l_routing_status
1415 , l_entity
1416 , l_assignment_id
1417 , l_assign_sequence
1418 , l_partner_id
1419 , l_assignment_status
1420 , l_notify_rowid
1421 , l_cm_rs_id
1422 , l_decision_maker_flag
1423 , l_notify_type
1424 , l_user_id;
1425
1426 exit when lc_get_assignment%notfound;
1427
1428
1429
1430 for i in 1 .. p_partyTbl.last loop
1431
1432 if l_partner_id = p_partyTbl(i) then
1433
1434 if l_assign_sequence <> p_rank_Tbl(i) or
1435 (l_assignment_status <> p_statusTbl(i)) then
1436
1437 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW)
1438 THEN
1439 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
1440 fnd_message.Set_Token('TEXT', 'Calling pv_assignment_pvt.validateResponse' );
1441 fnd_msg_pub.Add;
1442 END IF;
1443
1444 pv_assignment_pvt.validateResponse (
1445 p_api_version_number => 1.0
1446 ,p_init_msg_list => FND_API.G_FALSE
1447 ,p_commit => FND_API.G_FALSE
1448 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
1449 ,p_response_code => p_statusTbl(i)
1450 ,p_routing_status => l_routing_status
1451 ,p_decision_maker_flag => l_decision_maker_flag
1452 ,p_notify_type => l_notify_type
1453 ,x_msg_count => x_msg_count
1454 ,x_msg_data => x_msg_data
1455 ,x_return_status => x_return_status);
1456
1457 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
1458 raise FND_API.G_EXC_ERROR;
1459 end if;
1460
1461 if l_assignment_status <> p_statusTbl(i) then
1462
1463 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW)
1464 THEN
1465 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
1466 fnd_message.Set_Token('TEXT', 'pv_Assignment_pvt.update_party_response' );
1467 fnd_msg_pub.Add;
1468 END IF;
1469
1470 pv_assignment_pvt.update_party_response (
1471 p_api_version_number => 1.0
1472 ,p_init_msg_list => FND_API.G_FALSE
1473 ,p_commit => FND_API.G_FALSE
1474 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
1475 ,P_rowid => l_notify_rowid
1476 ,p_lead_assignment_id => l_assignment_id
1477 ,p_party_resource_id => l_cm_rs_id
1478 ,p_response => p_statusTbl(i)
1479 ,p_reason_code => NULL
1480 ,p_rank => p_rank_Tbl(i)
1481 ,x_msg_count => x_msg_count
1482 ,x_msg_data => x_msg_data
1483 ,x_return_status => x_return_status);
1484
1485 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
1486 raise FND_API.G_EXC_ERROR;
1487 end if;
1488
1489 IF p_statustbl(i) = g_la_status_cm_rejected THEN
1490 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
1491 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
1492 fnd_message.Set_Token('TEXT', 'before removing preferred partner by calling pv_assign_util_pvt.removePreferredPartner');
1493 fnd_msg_pub.Add;
1494 END IF;
1495
1496 PV_ASSIGN_UTIL_PVT.removePreferedPartner
1497 (
1498 p_api_version_number => 1.0,
1499 p_init_msg_list => FND_API.G_FALSE,
1500 p_commit => FND_API.G_FALSE,
1501 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1502 p_lead_id => p_lead_id,
1503 p_item_type => NULL,
1504 p_item_key => NULL,
1505 p_partner_id => p_partyTbl(i),
1506 x_return_status => x_return_status,
1507 x_msg_count => x_msg_count,
1508 x_msg_data => x_msg_data
1509 );
1510 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1511 RAISE FND_API.G_EXC_ERROR;
1512 END IF;
1513
1514 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
1515 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
1516 fnd_message.Set_Token('TEXT', 'after removing preferred partner');
1517 fnd_msg_pub.Add;
1518 END IF;
1519 END IF;
1520 end if;
1521
1522 if l_assign_sequence <> p_rank_Tbl(i) then
1523
1524 if p_statusTbl(i) in (pv_assignment_pub.g_la_status_cm_added, pv_assignment_pub.g_la_status_cm_add_app_for_pt) then
1525 l_response := pv_assignment_pub.g_la_status_cm_approved;
1526 else
1527 l_response := p_statusTbl(i);
1528 end if;
1529
1530 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW)
1531 THEN
1532 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
1533 fnd_message.Set_Token('TEXT', 'Calling pv_assignment_pvt.UpdateAssignment' );
1534 fnd_msg_pub.Add;
1535 END IF;
1536
1537
1538 pv_assignment_pvt.UpdateAssignment (
1539 p_api_version_number => 1.0
1540 ,p_init_msg_list => FND_API.G_FALSE
1541 ,p_commit => FND_API.G_FALSE
1542 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
1543 ,p_action => pv_assignment_pub.g_asgn_action_status_update
1544 ,p_lead_assignment_id => l_assignment_id
1545 ,p_status_date => sysdate
1546 ,p_status => l_response
1547 ,p_reason_code => NULL
1548 ,p_rank => p_rank_Tbl(i)
1549 ,x_msg_count => x_msg_count
1550 ,x_msg_data => x_msg_data
1551 ,x_return_status => x_return_status);
1552
1553 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
1554 raise FND_API.G_EXC_ERROR;
1555 end if;
1556
1557 end if;
1558 end if;
1559 exit;
1560
1561 end if; -- l_partner_id = p_partyTbl(i)
1562
1563 end loop; -- 1 .. p_partyTbl.last
1564
1565 end loop; -- lc_get_assignment
1566
1567 close lc_get_assignment;
1568
1569 if l_itemtype is NULL then
1570 -- the cursor returned no rows, which means that the person is not in pv_party_notifications)
1571 -- because of the way the UI is implemented (partner link), this API get's called whenever anyone
1572 -- changes anything on that page even though the assignment list is not updated
1573 -- so instead of throwing an exception, just return
1574
1575 -- fnd_message.set_name('PV', 'PV_NOT_DECISION_MAKER');
1576 -- fnd_msg_pub.ADD;
1577 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1578 return;
1579 end if;
1580
1581 -- check for new partners
1582 -- Obsoleted for 11.5.10
1583 l_mode := wf_engine.GetItemAttrText( itemtype => l_itemtype,
1584 itemkey => l_itemkey,
1585 aname => pv_workflow_pub.g_wf_attr_organization_type);
1586
1587 for i in 1 .. p_partyTbl.last loop
1588
1589 if p_statusTbl(i) in (g_la_status_cm_added,g_la_status_cm_add_app_for_pt) then
1590
1591 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
1592 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
1593 fnd_message.Set_Token('TEXT', 'Adding new partner: ' || p_partyTbl(i));
1594 fnd_msg_pub.Add;
1595 END IF;
1596
1597 l_rs_details_tbl.delete;
1598 l_vad_id := null;
1599
1600 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW)
1601 THEN
1602 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
1603 fnd_message.Set_Token('TEXT', 'getting partner info of partner id:' || p_partyTbl(i));
1604 fnd_msg_pub.Add;
1605 END IF;
1606
1607
1608 pv_assign_util_pvt.get_partner_info(
1609 p_api_version_number => 1.0
1610 ,p_init_msg_list => FND_API.G_FALSE
1611 ,p_commit => FND_API.G_FALSE
1612 ,p_mode => l_mode
1613 ,p_partner_id => p_partyTbl(i)
1614 ,p_entity => l_entity
1615 ,p_entity_id => p_lead_id
1616 ,p_retrieve_mode => 'PT'
1617 ,x_rs_details_tbl => l_rs_details_tbl
1618 ,x_vad_id => l_vad_id
1619 ,x_return_status => x_return_status
1620 ,x_msg_count => x_msg_count
1621 ,x_msg_data => x_msg_data);
1622
1623 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
1624 raise FND_API.G_EXC_ERROR;
1625 end if;
1626
1627 l_assignment_rec.lead_id := p_lead_id;
1628
1629 l_assignment_rec.related_party_id := l_vad_id;
1630 if l_vad_id is not null then
1631 l_assignment_rec.related_party_access_code := g_assign_access_update;
1632 end if;
1633
1634 l_assignment_rec.partner_id := p_partyTbl(i);
1635 l_assignment_rec.partner_access_code := g_assign_access_none;
1636 l_assignment_rec.assign_sequence := p_rank_tbl(i);
1637 l_assignment_rec.object_version_number := 0;
1638 l_assignment_rec.source_type := g_la_src_type_matching;
1639 l_assignment_rec.status_date := SYSDATE;
1640
1641 if p_statusTbl(i) = g_la_status_cm_added then
1642 l_assignment_rec.status := g_la_status_cm_approved;
1643 elsif p_statusTbl(i) = g_la_status_cm_add_app_for_pt then
1644 l_assignment_rec.status := g_la_status_cm_app_for_pt;
1645 end if;
1646
1647 l_assignment_rec.wf_item_type := l_itemType;
1648 l_assignment_rec.wf_item_key := l_itemKey;
1649
1650 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW)
1651 THEN
1652 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
1653 fnd_message.Set_Token('TEXT', 'Calling pv_assign_util_pvt.Create_lead_assignment_row');
1654 fnd_msg_pub.Add;
1655 END IF;
1656
1657 pv_assign_util_pvt.Create_lead_assignment_row (
1658 p_api_version_number => 1.0,
1659 p_init_msg_list => FND_API.G_FALSE,
1660 p_commit => FND_API.G_FALSE,
1661 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1662 p_assignment_rec => l_assignment_rec,
1663 x_lead_assignment_id => l_new_lead_assignment_id, -- do not overwrite l_assignment_id
1664 x_return_status => x_return_status ,
1665 x_msg_count => x_msg_count ,
1666 x_msg_data => x_msg_data);
1667
1668 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
1669 raise FND_API.G_EXC_ERROR;
1670 end if;
1671
1672 l_party_notify_rec_tbl.WF_ITEM_TYPE.delete;
1673 l_party_notify_rec_tbl.WF_ITEM_KEY.delete;
1674 l_party_notify_rec_tbl.LEAD_ASSIGNMENT_ID.delete;
1675 l_party_notify_rec_tbl.NOTIFICATION_TYPE.delete;
1676 l_party_notify_rec_tbl.RESOURCE_ID.delete;
1677 l_party_notify_rec_tbl.USER_ID.delete;
1678 l_party_notify_rec_tbl.USER_NAME.delete;
1679 l_party_notify_rec_tbl.RESOURCE_RESPONSE.delete;
1680 l_party_notify_rec_tbl.RESPONSE_DATE.delete;
1681 l_party_notify_rec_tbl.DECISION_MAKER_FLAG.delete;
1682
1683 if l_rs_details_tbl.count > 0 then
1684
1685 l_bulk_size := l_rs_details_tbl.last + 1; -- add 1 for the CM
1686
1687 l_party_notify_rec_tbl.WF_ITEM_TYPE.extend (l_bulk_size);
1688 l_party_notify_rec_tbl.WF_ITEM_KEY.extend (l_bulk_size);
1689 l_party_notify_rec_tbl.LEAD_ASSIGNMENT_ID.extend (l_bulk_size);
1690 l_party_notify_rec_tbl.NOTIFICATION_TYPE.extend (l_bulk_size);
1691 l_party_notify_rec_tbl.RESOURCE_ID.extend (l_bulk_size);
1692 l_party_notify_rec_tbl.USER_ID.extend (l_bulk_size);
1693 l_party_notify_rec_tbl.USER_NAME.extend (l_bulk_size);
1694 l_party_notify_rec_tbl.RESOURCE_RESPONSE.extend (l_bulk_size);
1695 l_party_notify_rec_tbl.RESPONSE_DATE.extend (l_bulk_size);
1696 l_party_notify_rec_tbl.DECISION_MAKER_FLAG.extend(l_bulk_size);
1697
1698 for i in 1 .. l_rs_details_tbl.count loop
1699
1700 l_party_notify_rec_tbl.WF_ITEM_TYPE(i) := l_itemtype;
1701 l_party_notify_rec_tbl.WF_ITEM_KEY(i) := l_itemkey;
1702 l_party_notify_rec_tbl.LEAD_ASSIGNMENT_ID(i) := l_new_lead_assignment_id;
1703 l_party_notify_rec_tbl.NOTIFICATION_TYPE(i) := l_rs_details_tbl(i).notification_type;
1704 l_party_notify_rec_tbl.RESOURCE_ID(i) := l_rs_details_tbl(i).resource_id;
1705 l_party_notify_rec_tbl.USER_ID(i) := l_rs_details_tbl(i).user_id;
1706 l_party_notify_rec_tbl.USER_NAME(i) := l_rs_details_tbl(i).user_name;
1707 l_party_notify_rec_tbl.DECISION_MAKER_FLAG(i):= l_rs_details_tbl(i).decision_maker_flag;
1708
1709 end loop;
1710
1711 l_party_notify_rec_tbl.WF_ITEM_TYPE (l_bulk_size) := l_itemtype;
1712 l_party_notify_rec_tbl.WF_ITEM_KEY (l_bulk_size) := l_itemkey;
1713 l_party_notify_rec_tbl.LEAD_ASSIGNMENT_ID (l_bulk_size) := l_new_lead_assignment_id;
1714 l_party_notify_rec_tbl.NOTIFICATION_TYPE (l_bulk_size) := g_notify_type_matched_to;
1715 l_party_notify_rec_tbl.RESOURCE_ID (l_bulk_size) := l_cm_rs_id;
1716 l_party_notify_rec_tbl.USER_ID (l_bulk_size) := l_user_id;
1717 l_party_notify_rec_tbl.USER_NAME (l_bulk_size) := p_user_name;
1718 l_party_notify_rec_tbl.RESOURCE_RESPONSE (l_bulk_size) := p_statusTbl(i); -- CM_ADDED or CM_ADD_APP_FOR_PT
1719 l_party_notify_rec_tbl.RESPONSE_DATE (l_bulk_size) := sysdate;
1720 l_party_notify_rec_tbl.DECISION_MAKER_FLAG(l_bulk_size) := 'Y';
1721
1722 pv_assignment_pvt.bulk_cr_party_notification(
1723 p_api_version_number => 1.0
1724 ,p_init_msg_list => FND_API.G_FALSE
1725 ,p_commit => FND_API.G_FALSE
1726 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
1727 ,P_party_notify_Rec_tbl => l_party_notify_rec_tbl
1728 ,x_return_status => x_return_status
1729 ,x_msg_count => x_msg_count
1730 ,x_msg_data => x_msg_data);
1731
1732 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
1733 raise FND_API.G_EXC_ERROR;
1734 end if;
1735
1736 end if;
1737 end if;
1738 end loop;
1739
1740 open lc_chk_match_outcome( pc_itemtype => l_itemtype,
1741 pc_itemkey => l_itemkey);
1742 loop
1743 fetch lc_chk_match_outcome into l_pt_response;
1744 exit when lc_chk_match_outcome%notfound;
1745 l_pt_response_tbl.extend;
1746 l_pt_response_tbl(l_pt_response_tbl.last) := l_pt_response;
1747 end loop;
1748
1749 close lc_chk_match_outcome;
1750
1751 for i in 1 .. l_pt_response_tbl.count loop
1752
1753 if l_pt_response_tbl(i) in (g_la_status_cm_approved, g_la_status_cm_added, g_la_status_cm_app_for_pt) then
1754
1755 l_approve_flag := true;
1756
1757 elsif l_pt_response_tbl(i) = g_la_status_assigned then
1758
1759 l_no_response_flag := true;
1760
1761 elsif l_pt_response_tbl(i) = g_la_status_cm_rejected then
1762
1763 l_reject_flag := true;
1764
1765 else
1766
1767 fnd_message.set_name('PV', 'PV_NOT_VALID_ASGNMENT_STATUS');
1768 fnd_message.set_token('P_PT_RESPONSE', l_pt_response_tbl(i));
1769 fnd_msg_pub.ADD;
1770
1771 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1772
1773 end if;
1774
1775 end loop;
1776
1777 if not l_no_response_flag then
1778
1779 -- every decision maker has responded
1780
1781 if l_approve_flag then
1782
1783 l_match_outcome := pv_workflow_pub.g_wf_lkup_match_approved;
1784
1785 elsif l_reject_flag then
1786
1787 l_match_outcome := pv_workflow_pub.g_wf_lkup_match_rejected;
1788
1789 end if;
1790
1791 wf_engine.SetItemAttrText (itemtype => l_itemType,
1792 itemkey => l_itemKey,
1793 aname => pv_workflow_pub.g_wf_attr_routing_outcome,
1794 avalue => l_match_outcome);
1795
1796 wf_engine.CompleteActivity( itemtype => l_itemtype,
1797 itemkey => l_itemkey,
1798 activity => pv_workflow_pub.g_wf_fn_cm_response_block,
1799 result => l_match_outcome);
1800
1801 -- For RUN mode errors, you need to check wf_item_activity_statuses
1802
1803 PV_ASSIGN_UTIL_PVT.checkforErrors ( p_api_version_number => 1.0
1804 ,p_init_msg_list => FND_API.G_FALSE
1805 ,p_commit => FND_API.G_FALSE
1806 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
1807 ,p_itemtype => l_itemtype
1808 ,p_itemkey => l_itemkey
1809 ,x_return_status => x_return_status
1810 ,x_msg_count => x_msg_count
1811 ,x_msg_data => x_msg_data);
1812
1813 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
1814 raise FND_API.G_EXC_ERROR;
1815 end if;
1816
1817 end if;
1818
1819 IF FND_API.To_Boolean ( p_commit ) THEN
1820 COMMIT WORK;
1821 END IF;
1822
1823 -- Standard call to get message count and if count is 1, get message info.
1824 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_FALSE,
1825 p_count => x_msg_count,
1826 p_data => x_msg_data);
1827
1828 FND_MSG_PUB.g_msg_level_threshold := FND_API.G_MISS_NUM;
1829
1830 EXCEPTION
1831
1832 WHEN FND_API.G_EXC_ERROR THEN
1833
1834 x_return_status := FND_API.G_RET_STS_ERROR ;
1835 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_FALSE,
1836 p_count => x_msg_count,
1837 p_data => x_msg_data);
1838
1839 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1840
1841 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1842 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_FALSE,
1843 p_count => x_msg_count,
1844 p_data => x_msg_data);
1845
1846 WHEN OTHERS THEN
1847 IF sqlcode = -20002 THEN
1848 fnd_message.Set_Name('PV', 'PV_WF_COMP_ACTY_ERR');
1849 fnd_msg_pub.Add;
1850 ELSE
1851 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
1852 END IF;
1853
1854 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1855 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_FALSE,
1856 p_count => x_msg_count,
1857 p_data => x_msg_data);
1858
1859 end process_match_response;
1860
1861
1862 procedure PROCESS_OFFER_RESPONSE (
1863 p_api_version_number IN NUMBER
1864 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
1865 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
1866 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
1867 ,p_entity in VARCHAR2
1868 ,p_lead_id IN number
1869 ,p_partner_id IN number
1870 ,p_user_name IN varchar2
1871 ,p_pt_response IN varchar2
1872 ,p_reason_code IN varchar2
1873 ,x_return_status OUT NOCOPY VARCHAR2
1874 ,x_msg_count OUT NOCOPY NUMBER
1875 ,x_msg_data OUT NOCOPY VARCHAR2) is
1876
1877 l_api_name CONSTANT VARCHAR2(30) := 'PROCESS_OFFER_RESPONSE';
1878 l_api_version_number CONSTANT NUMBER := 1.0;
1879
1880 l_notify_rowid rowid := NULL;
1881 l_assignment_type varchar2(30);
1882
1883 l_pt_org_name varchar2(100);
1884
1885 l_assignment_id number;
1886 l_access_id number;
1887 l_rank number;
1888 l_current_rank number;
1889 l_user_id number;
1890 l_party_notification_id number;
1891 l_customer_id number;
1892 l_responder_rs_id number;
1893 l_user_is_cm boolean := false;
1894 l_party_notify_rec pv_assign_util_pvt.party_notify_rec_type;
1895 l_partner_org_rs_id number;
1896 l_itemtype varchar2(30);
1897 l_itemkey varchar2(30);
1898 l_routing_status varchar2(30);
1899 l_wf_status varchar2(30);
1900 l_decision_maker_flag varchar2(10);
1901 l_reason_code varchar2(30);
1902 l_wf_activity_id number;
1903
1904 l_assignment_status varchar2(30);
1905
1906 l_username_tab g_varchar_table_type := g_varchar_table_type();
1907 l_response_tab g_varchar_table_type := g_varchar_table_type();
1908 l_resource_id_tab g_number_table_type := g_number_table_type();
1909 l_partner_id_tab g_number_table_type := g_number_table_type();
1910 l_assignment_id_tab g_number_table_type := g_number_table_type();
1911
1912 cursor lc_validate_reason (pc_lookup_type varchar2, pc_reason_code varchar2) is
1913 select lookup_code from pv_lookups
1914 where lookup_type = pc_lookup_type
1915 and lookup_code = pc_reason_code;
1916
1917 cursor lc_get_pt_org_name (pc_partner_id number) is
1918 select pt.party_name
1919 from hz_relationships pr,
1920 hz_organization_profiles op,
1921 hz_parties pt
1922 where pr.party_id = pc_partner_id
1923 and pr.subject_table_name = 'HZ_PARTIES'
1924 and pr.object_table_name = 'HZ_PARTIES'
1925 and pr.status in ('A', 'I')
1926 and pr.object_id = op.party_id
1927 and op.internal_flag = 'Y'
1928 and op.effective_end_date is null
1929 and pr.subject_id = pt.party_id
1930 and pt.status in ('A', 'I');
1931
1932 cursor lc_get_assignment (pc_lead_id number,
1933 pc_entity varchar2,
1934 pc_partner_id number,
1935 pc_notify_type varchar2,
1936 pc_username varchar2)
1937 is
1938 select a.wf_item_type, a.wf_item_key, a.routing_status, a.wf_status,
1939 b.lead_assignment_id, b.status, b.assign_sequence,
1940 c.rowid, c.resource_id, c.decision_maker_flag, c.user_id
1941 from pv_lead_workflows a, pv_lead_assignments b, pv_party_notifications c, fnd_user usr
1942 where a.lead_id = pc_lead_id
1943 and a.entity = pc_entity
1944 and a.wf_item_type = b.wf_item_type
1945 and a.wf_item_key = b.wf_item_key
1946 and a.latest_routing_flag = 'Y'
1947 and b.partner_id = pc_partner_id
1948 and b.lead_assignment_id = c.lead_assignment_id
1949 and c.user_id = usr.user_id
1950 and usr.user_name = pc_username
1951 and c.notification_type = pc_notify_type;
1952
1953 cursor lc_any_pt_not_respond_chk (pc_itemtype varchar2,
1954 pc_itemkey varchar2) is
1955 select rowid
1956 from pv_lead_assignments
1957 where wf_item_type = pc_itemtype
1958 and wf_item_key = pc_itemkey
1959 and status in (g_la_status_cm_timeout,
1960 g_la_status_cm_bypassed,
1961 g_la_status_cm_approved);
1962
1963 cursor lc_joint_offer_approve_chk (pc_itemtype varchar2,
1964 pc_itemkey varchar2) is
1965 select rowid
1966 from pv_lead_assignments
1967 where wf_item_type = pc_itemtype
1968 and wf_item_key = pc_itemkey
1969 and status in (g_la_status_pt_approved, g_la_status_cm_app_for_pt) and rownum < 2;
1970
1971 cursor lc_get_offered_to_for_pt (pc_itemtype varchar2,
1972 pc_itemkey varchar2,
1973 pc_partner_id number,
1974 pc_notify_type varchar2) is
1975 select usr.user_name, pn.resource_id
1976 from pv_lead_assignments la,
1977 pv_party_notifications pn,
1978 fnd_user usr
1979 where la.wf_item_type = pc_itemtype
1980 and la.wf_item_key = pc_itemkey
1981 and la.partner_id = pc_partner_id
1982 and la.lead_assignment_id = pn.lead_assignment_id
1983 and pn.notification_type = pc_notify_type
1984 and pn.user_id = usr.user_id;
1985
1986
1987 -- improve performance, add in join to access
1988
1989 cursor lc_get_uniq_cm_for_pt (pc_itemtype varchar2,
1990 pc_itemkey varchar2,
1991 pc_partner_id number,
1992 pc_notify_type varchar2) is
1993 select usr.user_name, pn.resource_id
1994 from pv_lead_assignments la,
1995 pv_party_notifications pn,
1996 fnd_user usr
1997 where la.wf_item_type = pc_itemtype
1998 and la.wf_item_key = pc_itemkey
1999 and la.partner_id = pc_partner_id
2000 and la.lead_assignment_id = pn.lead_assignment_id
2001 and pn.notification_type = pc_notify_type
2002 and pn.user_id = usr.user_id
2003 and not exists
2004 (select 1
2005 from pv_lead_assignments la2,
2006 pv_party_notifications pn2
2007 where la2.wf_item_type = pc_itemtype
2008 and la2.wf_item_key = pc_itemkey
2009 and la2.partner_id <> la.partner_id
2010 and la2.status in (g_la_status_cm_timeout,
2011 g_la_status_cm_bypassed,
2012 g_la_status_cm_approved,
2013 g_la_status_cm_app_for_pt,
2014 g_la_status_pt_approved)
2015 and la2.lead_assignment_id = pn2.lead_assignment_id
2016 and pn2.notification_type = pc_notify_type
2017 and pn2.user_id = pn.user_id );
2018
2019
2020 cursor lc_get_pt_org (pc_itemtype varchar2,
2021 pc_itemkey varchar2) is
2022 select b.resource_id partner_org_rs_id
2023 from pv_lead_assignments la,
2024 jtf_rs_resource_extns b
2025 where
2026 la.wf_item_type = pc_itemtype
2027 and la.wf_item_key = pc_itemkey
2028 and la.status = pv_assignment_pub.g_la_status_pt_rejected
2029 and la.partner_id = b.source_id
2030 and b.category = 'PARTNER'
2031 and sysdate between b.start_date_active and nvl(b.end_date_active,sysdate);
2032 begin
2033
2034 -- Standard call to check for call compatibility.
2035
2036 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
2037 p_api_version_number,
2038 l_api_name,
2039 G_PKG_NAME) THEN
2040 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2041
2042 END IF;
2043
2044 -- Initialize message list if p_init_msg_list is set to TRUE.
2045 IF FND_API.to_Boolean( p_init_msg_list )
2046 THEN
2047 fnd_msg_pub.initialize;
2048 END IF;
2049
2050 if fnd_profile.value('ASF_PROFILE_DEBUG_MSG_ON') = 'Y' then
2051 FND_MSG_PUB.g_msg_level_threshold := FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW;
2052 else
2053 FND_MSG_PUB.g_msg_level_threshold := FND_API.G_MISS_NUM;
2054 end if;
2055
2056 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
2057 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
2058 fnd_message.Set_Token('TEXT', 'In ' || l_api_name || '. p_pt_response=' || p_pt_response);
2059 fnd_msg_pub.Add;
2060 END IF;
2061
2062 x_return_status := FND_API.G_RET_STS_SUCCESS ;
2063
2064 if p_pt_response = g_la_status_cm_app_for_pt then
2065
2066 l_user_is_cm := true;
2067
2068 open lc_get_assignment (pc_lead_id => p_lead_id,
2069 pc_entity => p_entity,
2070 pc_partner_id => p_partner_id,
2071 pc_notify_type => g_notify_type_matched_to,
2072 pc_username => p_user_name);
2073
2074 else
2075 open lc_get_assignment (pc_lead_id => p_lead_id,
2076 pc_entity => p_entity,
2077 pc_partner_id => p_partner_id,
2078 pc_notify_type => g_notify_type_offered_to,
2079 pc_username => p_user_name);
2080 end if;
2081
2082
2083
2084 fetch lc_get_assignment into l_itemtype, l_itemkey, l_routing_status, l_wf_status, l_assignment_id, l_assignment_status,
2085 l_rank, l_notify_rowid, l_responder_rs_id, l_decision_maker_flag, l_user_id;
2086
2087 close lc_get_assignment;
2088
2089 --start of bug fix 5413239
2090 IF(p_pt_response = g_la_status_cm_app_for_pt and
2091 l_assignment_status in ('CM_APP_FOR_PT','PT_APPROVED')
2092 ) then
2093
2094 fnd_message.Set_Name('PV', 'PV_PARTNER_ALREADY_ACCEPTED');
2095 fnd_msg_pub.ADD;
2096 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2097
2098 end if;
2099
2100 --end of bug fix
2101
2102 -- -----------------------------------------------------------------------------
2103 -- pklin
2104 -- lock the row in pv_lead_assignments so no other user can acquire the lock
2105 -- to this row until the current transaction is completed.
2106 -- -----------------------------------------------------------------------------
2107 FOR x IN (SELECT 1
2108 FROM pv_lead_assignments
2109 WHERE lead_assignment_id = l_assignment_id
2110 FOR UPDATE NOWAIT)
2111 LOOP
2112 null;
2113 END LOOP;
2114
2115
2116
2117 if l_notify_rowid is NULL then
2118
2119 open lc_get_pt_org_name (pc_partner_id => p_partner_id);
2120 fetch lc_get_pt_org_name into l_pt_org_name;
2121 close lc_get_pt_org_name;
2122
2123 if l_user_is_cm then
2124 fnd_message.Set_Name('PV', 'PV_NOT_CM_FOR_PT');
2125 else
2126 fnd_message.Set_Name('PV', 'PV_NOT_CONTACT_FOR_PT');
2127 end if;
2128
2129 fnd_message.set_Token('P_PARTNER_NAME', l_pt_org_name);
2130 fnd_msg_pub.ADD;
2131
2132 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2133
2134 end if;
2135
2136 if l_wf_status = g_wf_status_closed then
2137 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
2138 fnd_message.set_Token('TEXT', 'Routing has already completed.');
2139 fnd_msg_pub.ADD;
2140 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2141 end if;
2142
2143 l_assignment_type := wf_engine.GetItemAttrText( itemtype => l_itemtype,
2144 itemkey => l_itemkey,
2145 aname => pv_workflow_pub.g_wf_attr_assignment_type);
2146
2147 if l_assignment_type not in (pv_workflow_pub.g_wf_lkup_single,
2148 pv_workflow_pub.g_wf_lkup_serial,
2149 pv_workflow_pub.g_wf_lkup_joint,
2150 pv_workflow_pub.g_wf_lkup_broadcast) then
2151
2152 fnd_message.Set_Name('PV', 'PV_NOT_VALID_ASGNMENT_TYPE');
2153 fnd_message.set_Token('P_ASGNMENT_TYPE', l_assignment_type);
2154 fnd_msg_pub.ADD;
2155
2156 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2157
2158 end if;
2159
2160 -- ---------------------------------------------------------------------------
2161 -- pklin
2162 -- After a partner rejected an assignment, the same partner cannot approve
2163 -- the assignment again.
2164 -- ---------------------------------------------------------------------------
2165 IF (p_pt_response = 'PT_APPROVED' AND l_assignment_status = 'PT_REJECTED') THEN
2166 fnd_message.Set_Name('PV', 'PV_CANNOT_APPROVE_AFTER_REJECT');
2167 fnd_msg_pub.ADD;
2168 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2169 END IF;
2170
2171
2172
2173 if l_assignment_type = pv_workflow_pub.g_wf_lkup_serial then
2174
2175 l_current_rank := wf_engine.GetItemAttrNumber( itemtype => l_itemtype,
2176 itemkey => l_itemkey,
2177 aname => pv_workflow_pub.g_wf_attr_current_serial_rank);
2178 if l_rank <> l_current_rank then
2179 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
2180 fnd_message.set_Token('TEXT', 'Not partner''s turn yet. Partner rank is ' || l_rank ||
2181 '. Current rank is ' || l_current_rank);
2182 fnd_msg_pub.ADD;
2183 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2184 end if;
2185
2186 end if;
2187
2188 if l_assignment_type = pv_workflow_pub.g_wf_lkup_joint then
2189
2190 -- this should only happen in joint and if the user has used the browser back button as
2191 -- detailed in bug 3258485
2192
2193 if p_pt_response = 'PT_REJECTED' and l_routing_status = 'ACTIVE' and l_assignment_status = 'PT_APPROVED' then
2194 fnd_message.Set_Name('PV', 'PV_CANNOT_REJECT_AFTER_APPROVE');
2195 fnd_msg_pub.ADD;
2196 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2197 end if;
2198
2199 end if;
2200
2201 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW)
2202 THEN
2203 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
2204 fnd_message.Set_Token('TEXT', 'Calling pv_assignment_pvt.validateResponse');
2205 fnd_msg_pub.Add;
2206 END IF;
2207
2208
2209 pv_assignment_pvt.validateResponse (
2210 p_api_version_number => 1.0
2211 ,p_init_msg_list => FND_API.G_FALSE
2212 ,p_commit => FND_API.G_FALSE
2213 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
2214 ,p_response_code => p_pt_response
2215 ,p_routing_status => l_routing_status
2216 ,p_decision_maker_flag => l_decision_maker_flag
2217 ,p_notify_type => g_notify_type_offered_to
2218 ,x_msg_count => x_msg_count
2219 ,x_msg_data => x_msg_data
2220 ,x_return_status => x_return_status);
2221
2222 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
2223 raise FND_API.G_EXC_ERROR;
2224 end if;
2225
2226 if p_pt_response = g_la_status_pt_rejected then
2227
2228 if p_reason_code is NULL then
2229 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
2230 fnd_message.set_Token('TEXT', 'Must specify decline reason');
2231 fnd_msg_pub.ADD;
2232 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2233 else
2234 open lc_validate_reason (pc_lookup_type => 'PV_REASON_CODES', pc_reason_code => p_reason_code);
2235 fetch lc_validate_reason into l_reason_code;
2236 close lc_validate_reason;
2237
2238 if l_reason_code is NULL then
2239 fnd_message.Set_Name('PV', 'PV_NOT_VALID_REASON_CODE');
2240 fnd_message.set_Token('P_REASON_CODE', p_reason_code);
2241 fnd_msg_pub.ADD;
2242 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2243 end if;
2244
2245 end if;
2246
2247 elsif p_pt_response = g_la_status_pt_approved and p_reason_code is not NULL then
2248
2249 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
2250 fnd_message.set_Token('TEXT', 'Cannot have decline reason when accepting offer');
2251 fnd_msg_pub.ADD;
2252
2253 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2254
2255 end if;
2256
2257 if l_user_is_cm then
2258
2259 l_party_notify_rec.WF_ITEM_TYPE := l_itemtype;
2260 l_party_notify_rec.WF_ITEM_KEY := l_itemkey;
2261 l_party_notify_rec.LEAD_ASSIGNMENT_ID := l_assignment_id;
2262 l_party_notify_rec.NOTIFICATION_TYPE := g_notify_type_behalf_of;
2263 l_party_notify_rec.RESOURCE_ID := l_responder_rs_id;
2264 l_party_notify_rec.USER_ID := l_user_id;
2265 l_party_notify_rec.USER_NAME := p_user_name;
2266 l_party_notify_rec.RESOURCE_RESPONSE := p_pt_response;
2267 l_party_notify_rec.RESPONSE_DATE := sysdate;
2268 l_party_notify_rec.DECISION_MAKER_FLAG := 'Y';
2269
2270 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW)
2271 THEN
2272 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
2273 fnd_message.Set_Token('TEXT', 'Calling pv_assign_util_pvt.create_party_notification');
2274 fnd_msg_pub.Add;
2275 END IF;
2276
2277
2278 pv_assign_util_pvt.create_party_notification(
2279 p_api_version_number => 1.0
2280 ,p_init_msg_list => FND_API.G_FALSE
2281 ,p_commit => FND_API.G_FALSE
2282 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
2283 ,P_party_notify_Rec => l_party_notify_rec
2284 ,x_party_notification_id => l_party_notification_id
2285 ,x_return_status => x_return_status
2286 ,x_msg_count => x_msg_count
2287 ,x_msg_data => x_msg_data);
2288
2289 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
2290 raise FND_API.G_EXC_ERROR;
2291 end if;
2292
2293 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW)
2294 THEN
2295 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
2296 fnd_message.Set_Token('TEXT', 'Calling pv_assignment_pvt.UpdateAssignment');
2297 fnd_msg_pub.Add;
2298 END IF;
2299
2300 pv_assignment_pvt.UpdateAssignment (
2301 p_api_version_number => 1.0
2302 ,p_init_msg_list => FND_API.G_FALSE
2303 ,p_commit => FND_API.G_FALSE
2304 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
2305 ,p_action => g_asgn_action_status_update
2306 ,p_lead_assignment_id => l_assignment_id
2307 ,p_status_date => sysdate
2308 ,p_status => p_pt_response
2309 ,p_reason_code => p_reason_code
2310 ,p_rank => NULL
2311 ,x_msg_count => x_msg_count
2312 ,x_msg_data => x_msg_data
2313 ,x_return_status => x_return_status);
2314
2315 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
2316 raise FND_API.G_EXC_ERROR;
2317 end if;
2318
2319 else
2320
2321 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW)
2322 THEN
2323 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
2324 fnd_message.Set_Token('TEXT', 'Calling pv_assignment_pvt.update_party_response');
2325 fnd_msg_pub.Add;
2326 END IF;
2327
2328 pv_assignment_pvt.update_party_response (
2329 p_api_version_number => 1.0
2330 ,p_init_msg_list => FND_API.G_FALSE
2331 ,p_commit => FND_API.G_FALSE
2332 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
2333 ,P_rowid => l_notify_rowid
2334 ,p_lead_assignment_id => l_assignment_id
2335 ,p_party_resource_id => l_responder_rs_id
2336 ,p_response => p_pt_response
2337 ,p_reason_code => p_reason_code
2338 ,p_rank => NULL
2339 ,x_msg_count => x_msg_count
2340 ,x_msg_data => x_msg_data
2341 ,x_return_status => x_return_status);
2342
2343 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
2344 raise FND_API.G_EXC_ERROR;
2345 end if;
2346 end if;
2347
2348 l_customer_id := wf_engine.GetItemAttrNumber( itemtype => l_itemtype,
2349 itemkey => l_itemkey,
2350 aname => pv_workflow_pub.g_wf_attr_customer_id);
2351
2352 if p_pt_response in (g_la_status_pt_approved, g_la_status_cm_app_for_pt) then
2353
2354 if l_assignment_type = pv_workflow_pub.g_wf_lkup_joint then
2355
2356 -- someone else may have already accepted
2357
2358 if l_routing_status <> pv_assignment_pub.g_r_status_active then
2359
2360
2361 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW)
2362 THEN
2363 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
2364 fnd_message.Set_Token('TEXT', 'IN if l_routing_status <> pv_assignment_pub.g_r_status_active then');
2365 fnd_msg_pub.Add;
2366 END IF;
2367
2368 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW)
2369 THEN
2370 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
2371 fnd_message.Set_Token('TEXT', 'Calling pv_assignment_pvt.update_routing_stage');
2372 fnd_msg_pub.Add;
2373 END IF;
2374
2375 pv_assignment_pvt.update_routing_stage (
2376 p_api_version_number => 1.0,
2377 p_init_msg_list => FND_API.G_FALSE,
2378 p_commit => FND_API.G_FALSE,
2379 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
2380 p_itemType => l_itemtype,
2381 p_itemKey => l_itemKey,
2382 p_routing_stage => pv_assignment_pub.g_r_status_active,
2383 p_active_but_open_flag => 'Y',
2384 x_return_status => x_return_status,
2385 x_msg_count => x_msg_count,
2386 x_msg_data => x_msg_data);
2387
2388 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
2389 raise FND_API.G_EXC_ERROR;
2390 end if;
2391
2392 end if;
2393
2394 end if; -- l_assignment_type
2395
2396 if l_assignment_type in (pv_workflow_pub.g_wf_lkup_single,
2397 pv_workflow_pub.g_wf_lkup_serial,
2398 pv_workflow_pub.g_wf_lkup_broadcast) then
2399
2400 wf_engine.CompleteActivity( itemtype => l_itemtype,
2401 itemkey => l_itemkey,
2402 activity => pv_workflow_pub.g_wf_fn_pt_response_block,
2403 result => pv_workflow_pub.g_wf_lkup_offer_approved );
2404
2405 elsif l_assignment_type = pv_workflow_pub.g_wf_lkup_joint then
2406
2407 open lc_any_pt_not_respond_chk( pc_itemtype => l_itemtype,
2408 pc_itemkey => l_itemkey);
2409
2410 l_notify_rowid := null;
2411 fetch lc_any_pt_not_respond_chk into l_notify_rowid;
2412 close lc_any_pt_not_respond_chk;
2413
2414 if l_notify_rowid is null then
2415
2416 wf_engine.CompleteActivity( itemtype => l_itemtype,
2417 itemkey => l_itemkey,
2418 activity => pv_workflow_pub.g_wf_fn_pt_response_block,
2419 result => pv_workflow_pub.g_wf_lkup_offer_approved );
2420 end if;
2421
2422 end if;
2423
2424 elsif p_pt_response = g_la_status_pt_rejected then
2425
2426 open lc_get_offered_to_for_pt (pc_itemtype => l_itemtype,
2427 pc_itemkey => l_itemkey,
2428 pc_partner_id => p_partner_id,
2429 pc_notify_type => g_notify_type_offered_to);
2430 loop
2431 l_username_tab.extend;
2432 l_resource_id_tab.extend;
2433
2434 fetch lc_get_offered_to_for_pt into l_username_tab(l_username_tab.last),
2435 l_resource_id_tab(l_username_tab.last);
2436 exit when lc_get_offered_to_for_pt%notfound;
2437
2438 end loop;
2439 close lc_get_offered_to_for_pt;
2440 l_username_tab.trim;
2441 l_resource_id_tab.trim;
2442
2443 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
2444 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
2445 fnd_message.Set_Token('TEXT', 'before removing preferred partner');
2446 fnd_msg_pub.Add;
2447 END IF;
2448
2449 PV_ASSIGN_UTIL_PVT.removePreferedPartner
2450 (
2451 p_api_version_number => 1.0,
2452 p_init_msg_list => FND_API.G_FALSE,
2453 p_commit => FND_API.G_FALSE,
2454 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
2455 p_lead_id => p_lead_id,
2456 p_item_type => NULL,
2457 p_item_key => NULL,
2458 p_partner_id => p_partner_id,
2459 x_return_status => x_return_status,
2460 x_msg_count => x_msg_count,
2461 x_msg_data => x_msg_data
2462 );
2463 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2464 RAISE FND_API.G_EXC_ERROR;
2465 END IF;
2466
2467 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
2468 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
2469 fnd_message.Set_Token('TEXT', 'after removing preferred partner');
2470 fnd_msg_pub.Add;
2471 END IF;
2472
2473
2474 for i in 1 .. l_username_tab.count loop
2475
2476 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
2477 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
2478 fnd_message.Set_Token('TEXT', ' calling pv_assign_util_pvt.updateAccess for user name:' || l_username_tab(i));
2479 fnd_msg_pub.Add;
2480 END IF;
2481
2482 pv_assign_util_pvt.updateAccess(
2483 p_api_version_number => 1.0,
2484 p_init_msg_list => FND_API.G_FALSE,
2485 p_commit => FND_API.G_FALSE,
2486 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
2487 p_itemtype => l_itemType,
2488 p_itemkey => l_itemKey,
2489 p_current_username => l_username_tab(i),
2490 p_lead_id => p_lead_id,
2491 p_customer_id => null,
2492 p_address_id => null,
2493 p_access_action => G_REMOVE_ACCESS,
2494 p_resource_id => l_resource_id_tab(i),
2495 p_access_type => g_pt_access,
2496 x_access_id => l_access_id,
2497 x_return_status => x_return_status,
2498 x_msg_count => x_msg_count,
2499 x_msg_data => x_msg_data);
2500
2501 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
2502 raise FND_API.G_EXC_ERROR;
2503 end if;
2504
2505 end loop;
2506
2507 -- All the partners who are added to the salesteam to be removed when the partner contact rejects
2508 -- the opportunity. In case if the routing is done by matching, then the partner do not exist.
2509
2510 open lc_get_pt_org (pc_itemtype => l_itemType, pc_itemkey => l_itemKey);
2511
2512 loop
2513 fetch lc_get_pt_org into l_partner_org_rs_id;
2514 exit when lc_get_pt_org%notfound;
2515
2516 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
2517 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
2518 fnd_message.Set_Token('TEXT', 'partner org rs id for timeout '||l_partner_org_rs_id);
2519 fnd_msg_pub.Add;
2520 END IF;
2521
2522 pv_assign_util_pvt.updateaccess(
2523 p_api_version_number => 1.0,
2524 p_init_msg_list => FND_API.G_FALSE,
2525 p_commit => FND_API.G_FALSE,
2526 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
2527 p_itemtype => l_itemType,
2528 p_itemkey => l_itemKey,
2529 p_current_username => NULL,
2530 p_lead_id => p_lead_id,
2531 p_customer_id => null,
2532 p_address_id => null,
2533 p_access_action => G_REMOVE_ACCESS,
2534 p_resource_id => l_partner_org_rs_id,
2535 p_access_type => G_PT_ORG_ACCESS,
2536 x_access_id => l_access_id,
2537 x_return_status => x_return_status,
2538 x_msg_count => x_msg_count,
2539 x_msg_data => x_msg_data);
2540
2541 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
2542 raise FND_API.G_EXC_ERROR;
2543 end if;
2544
2545 end loop;
2546 close lc_get_pt_org;
2547
2548 -- remove all CMs for partner from access that are not CMs of the approved partner
2549
2550 l_username_tab.delete;
2551 l_resource_id_tab.delete;
2552
2553 open lc_get_uniq_cm_for_pt (pc_itemtype => l_itemtype,
2554 pc_itemkey => l_itemkey,
2555 pc_partner_id => p_partner_id,
2556 pc_notify_type => g_notify_type_matched_to);
2557 loop
2558 l_username_tab.extend;
2559 l_resource_id_tab.extend;
2560
2561 fetch lc_get_uniq_cm_for_pt into l_username_tab(l_username_tab.last),
2562 l_resource_id_tab(l_username_tab.last);
2563 exit when lc_get_uniq_cm_for_pt%notfound;
2564
2565 end loop;
2566 close lc_get_uniq_cm_for_pt;
2567 l_username_tab.trim;
2568 l_resource_id_tab.trim;
2569
2570 for i in 1 .. l_username_tab.count loop
2571
2572 pv_assign_util_pvt.updateAccess(
2573 p_api_version_number => 1.0,
2574 p_init_msg_list => FND_API.G_FALSE,
2575 p_commit => FND_API.G_FALSE,
2576 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
2577 p_itemtype => l_itemType,
2578 p_itemkey => l_itemKey,
2579 p_current_username => l_username_tab(i),
2580 p_lead_id => p_lead_id,
2581 p_customer_id => null,
2582 p_address_id => null,
2583 p_access_action => G_REMOVE_ACCESS,
2584 p_resource_id => l_resource_id_tab(i),
2585 p_access_type => g_cm_access,
2586 x_access_id => l_access_id,
2587 x_return_status => x_return_status,
2588 x_msg_count => x_msg_count,
2589 x_msg_data => x_msg_data);
2590
2591 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
2592 raise FND_API.G_EXC_ERROR;
2593 end if;
2594
2595 end loop;
2596
2597 if l_assignment_type in (pv_workflow_pub.g_wf_lkup_single,
2598 pv_workflow_pub.g_wf_lkup_serial) then
2599
2600 wf_engine.CompleteActivity( itemtype => l_itemtype,
2601 itemkey => l_itemkey,
2602 activity => pv_workflow_pub.g_wf_fn_pt_response_block,
2603 result => pv_workflow_pub.g_wf_lkup_offer_rejected );
2604
2605 elsif l_assignment_type = pv_workflow_pub.g_wf_lkup_broadcast then
2606
2607
2608 open lc_any_pt_not_respond_chk( pc_itemtype => l_itemtype,
2609 pc_itemkey => l_itemkey);
2610
2611 l_notify_rowid := null;
2612 fetch lc_any_pt_not_respond_chk into l_notify_rowid;
2613 close lc_any_pt_not_respond_chk;
2614
2615 if l_notify_rowid is null then
2616
2617 wf_engine.CompleteActivity( itemtype => l_itemtype,
2618 itemkey => l_itemkey,
2619 activity => pv_workflow_pub.g_wf_fn_pt_response_block,
2620 result => pv_workflow_pub.g_wf_lkup_offer_rejected);
2621 end if;
2622
2623 elsif l_assignment_type = pv_workflow_pub.g_wf_lkup_joint then
2624
2625
2626 open lc_any_pt_not_respond_chk( pc_itemtype => l_itemtype,
2627 pc_itemkey => l_itemkey);
2628
2629 l_notify_rowid := null;
2630 fetch lc_any_pt_not_respond_chk into l_notify_rowid;
2631 close lc_any_pt_not_respond_chk;
2632
2633 if l_notify_rowid is null then
2634
2635 l_notify_rowid := null;
2636 open lc_joint_offer_approve_chk (pc_itemtype => l_itemtype,
2637 pc_itemkey => l_itemkey);
2638
2639 fetch lc_joint_offer_approve_chk into l_notify_rowid;
2640 close lc_joint_offer_approve_chk;
2641
2642 if l_notify_rowid is null then
2643
2644 wf_engine.CompleteActivity( itemtype => l_itemtype,
2645 itemkey => l_itemkey,
2646 activity => pv_workflow_pub.g_wf_fn_pt_response_block,
2647 result => pv_workflow_pub.g_wf_lkup_offer_rejected);
2648 else
2649
2650 wf_engine.CompleteActivity( itemtype => l_itemtype,
2651 itemkey => l_itemkey,
2652 activity => pv_workflow_pub.g_wf_fn_pt_response_block,
2653 result => pv_workflow_pub.g_wf_lkup_offer_approved);
2654 end if;
2655
2656 end if;
2657 end if;
2658
2659 end if; -- partner response
2660
2661 l_wf_activity_id := wf_engine.GetItemAttrNumber(itemtype => l_itemtype,
2662 itemkey => l_itemkey,
2663 aname => pv_workflow_pub.g_wf_attr_wf_activity_id);
2664
2665 pv_assignment_pvt.send_notification (
2666 p_api_version_number => 1.0
2667 ,p_init_msg_list => FND_API.G_FALSE
2668 ,p_commit => FND_API.G_FALSE
2669 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
2670 ,p_itemtype => l_itemtype
2671 ,p_itemkey => l_itemkey
2672 ,p_activity_id => l_wf_activity_id
2673 ,P_route_stage => g_r_status_offered
2674 ,p_partner_id => p_partner_id
2675 ,x_return_status => x_return_status
2676 ,x_msg_count => x_msg_count
2677 ,x_msg_data => x_msg_data);
2678
2679 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
2680 raise FND_API.G_EXC_ERROR;
2681 end if;
2682
2683 -- For RUN mode errors, you need to check wf_item_activity_statuses
2684
2685 PV_ASSIGN_UTIL_PVT.checkforErrors ( p_api_version_number => 1.0
2686 ,p_init_msg_list => FND_API.G_FALSE
2687 ,p_commit => FND_API.G_FALSE
2688 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
2689 ,p_itemtype => l_itemtype
2690 ,p_itemkey => l_itemkey
2691 ,x_return_status => x_return_status
2692 ,x_msg_count => x_msg_count
2693 ,x_msg_data => x_msg_data);
2694
2695 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
2696 raise FND_API.G_EXC_ERROR;
2697 end if;
2698
2699
2700 IF FND_API.To_Boolean ( p_commit ) THEN
2701 COMMIT WORK;
2702 END IF;
2703
2704 -- Standard call to get message count and if count is 1, get message info.
2705 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_FALSE,
2706 p_count => x_msg_count,
2707 p_data => x_msg_data);
2708
2709 FND_MSG_PUB.g_msg_level_threshold := FND_API.G_MISS_NUM;
2710
2711 EXCEPTION
2712 WHEN g_e_resource_busy THEN
2713 -- --------------------------------------------------------------------
2714 -- pklin
2715 -- Capture ORA-00054: resource busy and acquire with NOWAIT specified.
2716 -- This means the row in pv_lead_assignments is already being locked
2717 -- by another user/session.
2718 -- --------------------------------------------------------------------
2719 fnd_message.Set_Name('PV', 'PV_REQUERY_THE_RECORD');
2720 fnd_msg_pub.ADD;
2721
2722 x_return_status := FND_API.G_RET_STS_ERROR;
2723 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_TRUE,
2724 p_count => x_msg_count,
2725 p_data => x_msg_data);
2726
2727
2728 WHEN FND_API.G_EXC_ERROR THEN
2729
2730 x_return_status := FND_API.G_RET_STS_ERROR ;
2731 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_FALSE,
2732 p_count => x_msg_count,
2733 p_data => x_msg_data);
2734
2735 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2736 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2737 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_FALSE,
2738 p_count => x_msg_count,
2739 p_data => x_msg_data);
2740
2741 WHEN OTHERS THEN
2742 IF sqlcode = -20002 THEN
2743 fnd_message.Set_Name('PV', 'PV_WF_COMP_ACTY_ERR');
2744 fnd_msg_pub.Add;
2745 ELSE
2746 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
2747 END IF;
2748
2749 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2750 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_FALSE,
2751 p_count => x_msg_count,
2752 p_data => x_msg_data);
2753
2754 end PROCESS_OFFER_RESPONSE;
2755
2756
2757
2758 procedure WITHDRAW_ASSIGNMENT (
2759 p_api_version_number IN NUMBER
2760 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
2761 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
2762 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
2763 ,p_entity in VARCHAR2
2764 ,p_lead_id IN NUMBER
2765 ,p_user_name IN VARCHAR2
2766 ,x_return_status OUT NOCOPY VARCHAR2
2767 ,x_msg_count OUT NOCOPY NUMBER
2768 ,x_msg_data OUT NOCOPY VARCHAR2) is
2769
2770 l_api_name CONSTANT VARCHAR2(30) := 'WITHDRAW_ASSIGNMENT';
2771 l_api_version_number CONSTANT NUMBER := 1.0;
2772
2773 l_routing_stage VARCHAR2(30);
2774 l_activity VARCHAR2(30);
2775 l_result VARCHAR2(30);
2776 l_assignment_status VARCHAR2(30);
2777 l_itemtype VARCHAR2(30);
2778 l_itemkey VARCHAR2(30);
2779 l_assignment_id NUMBER;
2780 l_assignment_id_tbl g_number_table_type := g_number_table_type();
2781 l_assignment_type VARCHAR2(30);
2782 l_rank NUMBER;
2783 l_assign_sequence NUMBER;
2784 l_rank_tbl g_number_table_type := g_number_table_type();
2785 l_status VARCHAR2(100);
2786 l_status_tbl g_varchar_table_type := g_varchar_table_type();
2787
2788 l_temp pls_integer;
2789 l_user_id NUMBER;
2790 l_resource_id NUMBER;
2791 l_partner_id NUMBER;
2792 l_lead_assignment_id NUMBER;
2793 l_party_notification_id NUMBER;
2794
2795 l_opp_number VARCHAR2(30);
2796 l_customer_name VARCHAR2(360);
2797 l_vendor_name VARCHAR2(100);
2798 l_opp_name VARCHAR2(240);
2799 l_assign_type_mean VARCHAR2(100);
2800 l_category VARCHAR2(30);
2801 l_ven_user_id NUMBER;
2802 l_source_id NUMBER;
2803 l_opp_amt VARCHAR2(100);
2804 l_lead_number VARCHAR2(30);
2805
2806 l_party_notify_rec pv_assign_util_pvt.party_notify_rec_type;
2807 l_attrib_values_rec pv_assignment_pvt.attrib_values_rec_type;
2808
2809 cursor lc_get_routing_stage (pc_lead_id number, pc_entity varchar2) is
2810 select a.wf_item_type,
2811 a.wf_item_key,
2812 a.routing_status,
2813 a.routing_type,
2814 b.lead_number,
2815 b.description,
2816 b.total_amount||' '||b.currency_code,
2817 c.party_name
2818 from pv_lead_workflows a
2819 , as_leads_all b
2820 , hz_parties c
2821 where a.lead_id = pc_lead_id
2822 and b.customer_id = c.party_id
2823 and a.latest_routing_flag = 'Y'
2824 and a.lead_id = b.lead_id
2825 and c.status in ('A', 'I')
2826 and a.entity = pc_entity;
2827
2828 cursor lc_get_assignment (pc_lead_id number
2829 , pc_entity varchar2)
2830 is
2831 select b.lead_assignment_id, b.assign_sequence, b.status
2832 from pv_lead_workflows a, pv_lead_assignments b
2833 where a.lead_id = pc_lead_id and a.latest_routing_flag = 'Y' and a.entity = pc_entity
2834 and a.wf_item_type = b.wf_item_type
2835 and a.wf_item_key = b.wf_item_key;
2836
2837
2838 CURSOR lc_get_cm_id (pc_itemtype VARCHAR2,
2839 pc_itemkey VARCHAR2)
2840 IS
2841 SELECT la.lead_assignment_id
2842 from pv_lead_assignments la
2843 where la.wf_item_type = pc_itemtype
2844 and la.wf_item_key = pc_itemkey
2845 and la.status not in (g_la_status_cm_rejected,
2846 g_la_status_pt_rejected,
2847 g_la_status_pt_timeout,
2848 g_la_status_lost_chance,
2849 g_la_status_pt_abandoned
2850 );
2851
2852
2853 CURSOR lc_get_assign_type_meaning (pc_assignment_type varchar2)
2854 IS
2855 SELECT meaning
2856 FROM pv_lookups
2857 WHERE lookup_type = 'PV_ASSIGNMENT_TYPE'
2858 AND lookup_code = pc_assignment_type;
2859
2860 CURSOR lc_get_vendor_cat(pc_lead_id NUMBER)
2861 IS
2862 SELECT extn.category
2863 , extn.source_id
2864 , pwf.created_by
2865 FROM pv_lead_workflows pwf
2866 , jtf_rs_resource_extns extn
2867 WHERE pwf.created_by = extn.user_id
2868 AND pwf.entity = 'OPPORTUNITY'
2869 AND pwf.latest_routing_flag = 'Y'
2870 AND pwf.lead_id = pc_lead_id;
2871
2872
2873 CURSOR lc_get_ven_emp_name(pc_source_id NUMBER)
2874 IS
2875 select otl.name vendor_name
2876 from hr_all_organization_units o,
2877 hr_all_organization_units_tl otl,
2878 per_all_people_f p
2879 where o.organization_id = otl.organization_id
2880 and otl.language = userenv('lang')
2881 and o.organization_id = p.business_group_id
2882 and p.person_id = pc_source_id;
2883
2884 CURSOR lc_get_ven_pty_name(pc_source_id NUMBER)
2885 IS
2886 select hp.party_name
2887 from hz_relationships emp,hz_parties hp
2888 where emp.party_id = pc_source_id
2889 and emp.subject_table_name = 'HZ_PARTIES'
2890 and emp.object_table_name = 'HZ_PARTIES'
2891 and emp.directional_flag = 'F'
2892 and emp.relationship_code = 'EMPLOYEE_OF'
2893 and emp.relationship_type = 'EMPLOYMENT'
2894 and emp.status in ('A', 'I')
2895 and emp.object_id = hp.party_id
2896 and hp.status in ('A', 'I');
2897
2898 begin
2899
2900 -- Standard call to check for call compatibility.
2901
2902 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
2903 p_api_version_number,
2904 l_api_name,
2905 G_PKG_NAME) THEN
2906 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2907
2908 END IF;
2909
2910 -- Initialize message list if p_init_msg_list is set to TRUE.
2911 IF FND_API.to_Boolean( p_init_msg_list )
2912 THEN
2913 fnd_msg_pub.initialize;
2914 END IF;
2915
2916 /* if fnd_profile.value('ASF_PROFILE_DEBUG_MSG_ON') = 'Y' then
2917 FND_MSG_PUB.g_msg_level_threshold := FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW;
2918 else
2919 FND_MSG_PUB.g_msg_level_threshold := FND_API.G_MISS_NUM;
2920 end if; */
2921
2922 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
2923 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
2924 fnd_message.Set_Token('TEXT', 'In ' || l_api_name || '. Lead id =' || p_lead_id);
2925 fnd_msg_pub.Add;
2926 END IF;
2927
2928 x_return_status := FND_API.G_RET_STS_SUCCESS ;
2929
2930
2931 open lc_get_routing_stage (pc_lead_id => p_lead_id, pc_entity => p_entity);
2932
2933 fetch lc_get_routing_stage into l_itemtype
2934 , l_itemkey
2935 , l_routing_stage
2936 , l_assignment_type
2937 , l_lead_number
2938 , l_opp_name
2939 , l_opp_amt
2940 , l_customer_name;
2941 close lc_get_routing_stage;
2942
2943 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
2944 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
2945 fnd_message.Set_Token('TEXT', 'Got the routing stage information: routing stage:'|| l_routing_stage || ': l_itemtype:' || l_itemtype || ':l_itemkey:' || l_itemkey );
2946 fnd_msg_pub.Add;
2947 END IF;
2948
2949 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
2950 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
2951 fnd_message.Set_Token('TEXT', '::l_assignment_type:' || l_assignment_type || '::l_lead_number:' || l_lead_number );
2952 fnd_msg_pub.Add;
2953 END IF;
2954
2955 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
2956 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
2957 fnd_message.Set_Token('TEXT', '::l_opp_name:' || l_opp_name || '::l_opp_amt:' ||l_opp_amt );
2958 fnd_msg_pub.Add;
2959 END IF;
2960
2961 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
2962 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
2963 fnd_message.Set_Token('TEXT', '::l_customer_name::' || l_customer_name);
2964 fnd_msg_pub.Add;
2965 END IF;
2966 if l_routing_stage is null then
2967
2968 fnd_message.Set_Name('PV', 'PV_NO_ASGNMENT');
2969 fnd_message.set_token('P_LEAD_ID', p_lead_id);
2970 fnd_msg_pub.ADD;
2971
2972 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2973
2974 elsif l_routing_stage in (g_r_status_matched, g_r_status_offered) then
2975
2976 l_rank := wf_engine.GetItemAttrNumber( itemtype => l_itemtype,
2977 itemkey => l_itemkey,
2978 aname => pv_workflow_pub.g_wf_attr_current_serial_rank);
2979
2980 open lc_get_assignment (pc_lead_id => p_lead_id, pc_entity => p_entity);
2981 loop
2982 fetch lc_get_assignment into l_assignment_id, l_assign_sequence, l_status;
2983 exit when lc_get_assignment%notfound;
2984
2985 IF l_assignment_type = pv_workflow_pub.g_wf_lkup_serial
2986 AND ( l_assign_sequence < l_rank OR l_status = g_la_status_cm_rejected ) THEN
2987
2988 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
2989 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
2990 fnd_message.Set_Token('TEXT','Partner is not a current partner or the oppty to this partner might have been rejetced by CM');
2991 fnd_msg_pub.Add;
2992 END IF;
2993
2994 ELSIF l_assignment_type in (pv_workflow_pub.g_wf_lkup_broadcast, pv_workflow_pub.g_wf_lkup_joint)
2995 AND l_status not in (pv_assignment_pub.g_la_status_cm_added,
2996 pv_assignment_pub.g_la_status_cm_approved,
2997 pv_assignment_pub.g_la_status_cm_bypassed,
2998 pv_assignment_pub.g_la_status_assigned,
2999 pv_assignment_pub.g_la_status_cm_timeout)
3000 THEN
3001
3002 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
3003 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
3004 fnd_message.Set_Token('TEXT', 'For Joint Selling and Broadcast the status will not be updated to withdrawn ' ||
3005 'for the partners who are not interested in the opp');
3006 fnd_msg_pub.Add;
3007 END IF;
3008
3009 ELSE
3010
3011 l_assignment_id_tbl.extend;
3012 l_rank_tbl.extend;
3013 l_status_tbl.extend;
3014
3015 l_assignment_id_tbl(l_assignment_id_tbl.last) := l_assignment_id;
3016 l_rank_tbl(l_rank_tbl.last) := l_assign_sequence;
3017 l_status_tbl(l_status_tbl.last) := l_status;
3018
3019 END IF; -- l_assignment_type
3020
3021 end loop;
3022 close lc_get_assignment;
3023 -- Match Withdrawn
3024 if l_routing_stage = g_r_status_matched then
3025
3026 for i in 1 .. l_assignment_id_tbl.count loop
3027
3028 IF l_assignment_type = pv_workflow_pub.g_wf_lkup_serial
3029 AND l_status_tbl(i) = g_la_status_cm_rejected THEN
3030
3031 NULL;
3032
3033 ELSE
3034
3035 l_assignment_status := g_la_status_match_withdrawn;
3036 l_activity := pv_workflow_pub.g_wf_fn_cm_response_block;
3037 l_result := pv_workflow_pub.g_wf_lkup_match_withdrawn;
3038
3039 END IF;
3040 end loop;
3041 -- offer withdrawn
3042 elsif l_routing_stage = g_r_status_offered then
3043
3044 for i in 1 .. l_assignment_id_tbl.count loop
3045
3046 IF l_assignment_type = pv_workflow_pub.g_wf_lkup_serial AND l_rank_tbl(i) < l_rank THEN
3047
3048 null;
3049
3050 ELSIF l_assignment_type in (pv_workflow_pub.g_wf_lkup_broadcast, pv_workflow_pub.g_wf_lkup_joint)
3051 AND l_status_tbl(i) in (pv_assignment_pub.g_la_status_pt_rejected,
3052 pv_assignment_pub.g_la_status_cm_rejected,
3053 pv_assignment_pub.g_la_status_lost_chance)
3054 THEN
3055
3056 null;
3057
3058 ELSE
3059
3060 l_assignment_status := g_la_status_offer_withdrawn;
3061 l_activity := pv_workflow_pub.g_wf_fn_pt_response_block;
3062
3063 l_result := pv_workflow_pub.g_wf_lkup_offer_withdrawn;
3064 END IF;
3065
3066 end loop;
3067
3068 end if; -- 2: l_routing_stage
3069
3070 wf_engine.SetItemAttrText (itemtype => l_itemType,
3071 itemkey => l_itemKey,
3072 aname => pv_workflow_pub.g_wf_attr_routing_outcome,
3073 avalue => l_result);
3074
3075 for i in 1 .. l_assignment_id_tbl.count loop
3076
3077 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
3078 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
3079 fnd_message.Set_Token('TEXT','Calling pv_assignment_pvt.UpdateAssignment for assignment id:' || l_assignment_id_tbl(i));
3080 fnd_msg_pub.Add;
3081 END IF;
3082
3083 pv_assignment_pvt.UpdateAssignment (
3084 p_api_version_number => 1.0
3085 ,p_init_msg_list => FND_API.G_FALSE
3086 ,p_commit => FND_API.G_FALSE
3087 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
3088 ,p_action => g_asgn_action_status_update
3089 ,p_lead_assignment_id => l_assignment_id_tbl(i)
3090 ,p_status_date => sysdate
3091 ,p_status => l_assignment_status
3092 ,p_reason_code => NULL
3093 ,p_rank => NULL
3094 ,x_msg_count => x_msg_count
3095 ,x_msg_data => x_msg_data
3096 ,x_return_status => x_return_status);
3097
3098 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
3099 raise FND_API.G_EXC_ERROR;
3100 end if;
3101
3102 end loop;
3103
3104 wf_engine.CompleteActivity( itemtype => l_itemtype,
3105 itemkey => l_itemkey,
3106 activity => l_activity,
3107 result => l_result);
3108
3109 -- vansub:rivendell
3110 -- Active Withdrawn
3111 elsif l_routing_stage = g_r_status_active then
3112
3113 /*
3114 open lc_get_cm_id (pc_itemtype => l_itemtype,
3115 pc_itemkey => l_itemkey);
3116
3117 fetch lc_get_cm_id into l_lead_assignment_id;
3118 close lc_get_cm_id;
3119 */
3120 for x in lc_get_cm_id (pc_itemtype => l_itemtype,
3121 pc_itemkey => l_itemkey)
3122 loop
3123 l_assignment_id_tbl.extend;
3124 l_assignment_id_tbl(l_assignment_id_tbl.last) := x.lead_assignment_id;
3125 end loop;
3126
3127
3128 /* if l_resource_id is null then
3129
3130 fnd_message.Set_Name('PV', 'PV_NO_WITHDRAW_RIGHTS');
3131 fnd_message.Set_Token('P_USERNAME', p_user_name);
3132 fnd_msg_pub.ADD;
3133 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3134
3135 end if;
3136
3137 l_party_notify_rec.WF_ITEM_TYPE := l_itemtype;
3138 l_party_notify_rec.WF_ITEM_KEY := l_itemkey;
3139 l_party_notify_rec.LEAD_ASSIGNMENT_ID := l_lead_assignment_id;
3140 l_party_notify_rec.NOTIFICATION_TYPE := g_notify_type_withdrawn_by;
3141 l_party_notify_rec.RESOURCE_ID := l_resource_id;
3142 l_party_notify_rec.USER_ID := l_user_id;
3143 l_party_notify_rec.USER_NAME := p_user_name;
3144 l_party_notify_rec.RESOURCE_RESPONSE := g_la_status_active_withdrawn;
3145 l_party_notify_rec.RESPONSE_DATE := sysdate;
3146 l_party_notify_rec.DECISION_MAKER_FLAG := 'Y';
3147
3148 pv_assign_util_pvt.create_party_notification(
3149 p_api_version_number => 1.0
3150 ,p_init_msg_list => FND_API.G_FALSE
3151 ,p_commit => FND_API.G_FALSE
3152 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
3153 ,P_party_notify_Rec => l_party_notify_rec
3154 ,x_party_notification_id => l_party_notification_id
3155 ,x_return_status => x_return_status
3156 ,x_msg_count => x_msg_count
3157 ,x_msg_data => x_msg_data);
3158
3159 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
3160 raise FND_API.G_EXC_ERROR;
3161 end if; */
3162
3163 for i in 1 .. l_assignment_id_tbl.count loop
3164
3165 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
3166 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
3167 fnd_message.Set_Token('TEXT','Calling pv_assignment_pvt.UpdateAssignment for assignment id:' || l_assignment_id_tbl(i));
3168 fnd_msg_pub.Add;
3169 END IF;
3170
3171 pv_assignment_pvt.UpdateAssignment (
3172 p_api_version_number => 1.0
3173 ,p_init_msg_list => FND_API.G_FALSE
3174 ,p_commit => FND_API.G_FALSE
3175 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
3176 ,p_action => g_asgn_action_status_update
3177 ,p_lead_assignment_id => l_assignment_id_tbl(i) -- l_lead_assignment_id
3178 ,p_status_date => sysdate
3179 ,p_status => g_la_status_active_withdrawn
3180 ,p_reason_code => NULL
3181 ,p_rank => NULL
3182 ,x_msg_count => x_msg_count
3183 ,x_msg_data => x_msg_data
3184 ,x_return_status => x_return_status);
3185
3186
3187 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
3188 raise FND_API.G_EXC_ERROR;
3189 end if;
3190 end loop;
3191
3192 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
3193 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
3194 fnd_message.Set_Token('TEXT','Removing rejected sales team by Calling pv_assignment_pvt.removeRejectedFromAccess' );
3195 fnd_msg_pub.Add;
3196 END IF;
3197
3198 pv_assignment_pvt.removeRejectedFromAccess (
3199 p_api_version_number => 1.0,
3200 p_init_msg_list => FND_API.G_FALSE,
3201 p_commit => FND_API.G_FALSE,
3202 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
3203 p_itemtype => l_itemType,
3204 p_itemkey => l_itemKey,
3205 p_partner_id => l_partner_id,
3206 x_return_status => x_return_status,
3207 x_msg_count => x_msg_count,
3208 x_msg_data => x_msg_data);
3209
3210 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
3211 raise FND_API.G_EXC_ERROR;
3212 end if;
3213
3214 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
3215 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
3216 fnd_message.Set_Token('TEXT','Removing preferred partner by Calling PV_ASSIGN_UTIL_PVT.removePreferedPartner' );
3217 fnd_msg_pub.Add;
3218 END IF;
3219
3220 PV_ASSIGN_UTIL_PVT.removePreferedPartner
3221 (
3222 p_api_version_number => 1.0,
3223 p_init_msg_list => FND_API.G_FALSE,
3224 p_commit => FND_API.G_FALSE,
3225 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
3226 p_lead_id => p_lead_id,
3227 p_item_type => l_itemType,
3228 p_item_key => l_itemKey,
3229 p_partner_id => l_partner_id,
3230 x_return_status => x_return_status,
3231 x_msg_count => x_msg_count,
3232 x_msg_data => x_msg_data
3233 );
3234 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
3235 raise FND_API.G_EXC_ERROR;
3236 end if;
3237
3238 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
3239 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
3240 fnd_message.Set_Token('TEXT','Update the routing stage by Calling pv_assignment_pvt.update_routing_stage' );
3241 fnd_msg_pub.Add;
3242 END IF;
3243
3244 pv_assignment_pvt.update_routing_stage (
3245 p_api_version_number => 1.0,
3246 p_init_msg_list => FND_API.G_FALSE,
3247 p_commit => FND_API.G_FALSE,
3248 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
3249 p_itemType => l_itemtype,
3250 p_itemKey => l_itemKey,
3251 p_routing_stage => pv_assignment_pub.g_r_status_withdrawn,
3252 p_active_but_open_flag => 'N',
3253 x_return_status => x_return_status,
3254 x_msg_count => x_msg_count,
3255 x_msg_data => x_msg_data);
3256
3257 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
3258 raise FND_API.G_EXC_ERROR;
3259 end if;
3260
3261
3262 /* OPEN lc_get_assign_type_meaning(l_assignment_type);
3263 FETCH lc_get_assign_type_meaning INTO l_assign_type_mean;
3264 CLOSE lc_get_assign_type_meaning;
3265
3266
3267 OPEN lc_get_vendor_cat(p_lead_id);
3268 FETCH lc_get_vendor_cat
3269 INTO l_category, l_source_id, l_ven_user_id;
3270 CLOSE lc_get_vendor_cat;
3271
3272 IF l_category = 'EMPLOYEE' THEN
3273 OPEN lc_get_ven_emp_name(l_source_id);
3274 FETCH lc_get_ven_emp_name INTO l_vendor_name;
3275 CLOSE lc_get_ven_emp_name;
3276 ELSIF l_category = 'PARTY' THEN
3277 OPEN lc_get_ven_pty_name(l_source_id);
3278 FETCH lc_get_ven_pty_name INTO l_vendor_name;
3279 CLOSE lc_get_ven_pty_name;
3280 END IF;
3281
3282
3283 l_attrib_values_rec.am_org_name := l_vendor_name;
3284 l_attrib_values_rec.lead_id := p_lead_id;
3285 l_attrib_values_rec.lead_number := l_lead_number;
3286 l_attrib_values_rec.entity_name := l_opp_name;
3287 l_attrib_values_rec.entity_amount := l_opp_amt;
3288 l_attrib_values_rec.customer_name := l_customer_name;
3289 l_attrib_values_rec.assignment_type := l_assignment_type;
3290 l_attrib_values_rec.assignment_type_mean := l_assign_type_mean;
3291 l_attrib_values_rec.process_name := pv_workflow_pub.g_wf_pcs_withdraw_fyi;
3292
3293 pv_assignment_pvt.StartWorkflow (
3294 p_api_version_number => 1.0,
3295 p_init_msg_list => FND_API.G_FALSE,
3296 p_commit => FND_API.G_FALSE,
3297 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
3298 p_itemKey => l_itemKey,
3299 p_itemType => l_itemType,
3300 p_creating_username => p_user_name,
3301 p_attrib_values_rec => l_attrib_values_rec,
3302 x_return_status => x_return_status,
3303 x_msg_count => x_msg_count,
3304 x_msg_data => x_msg_data); */
3305
3306
3307 -- vansub:rivendell
3308 else
3309
3310 fnd_message.set_name('PV', 'PV_CANNOT_WITHDRAW_ASGNMENT');
3311 fnd_message.set_token('P_ROUTING_STAGE', l_routing_stage);
3312 fnd_msg_pub.ADD;
3313
3314 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3315
3316 end if;
3317
3318
3319
3320 -- For RUN mode errors, you need to check wf_item_activity_statuses
3321
3322 PV_ASSIGN_UTIL_PVT.checkforErrors ( p_api_version_number => 1.0
3323 ,p_init_msg_list => FND_API.G_FALSE
3324 ,p_commit => FND_API.G_FALSE
3325 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
3326 ,p_itemtype => l_itemtype
3327 ,p_itemkey => l_itemkey
3328 ,x_return_status => x_return_status
3329 ,x_msg_count => x_msg_count
3330 ,x_msg_data => x_msg_data);
3331
3332 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
3333 raise FND_API.G_EXC_ERROR;
3334 end if;
3335
3336
3337 IF FND_API.To_Boolean ( p_commit ) THEN
3338 COMMIT WORK;
3339 END IF;
3340
3341 -- Standard call to get message count and if count is 1, get message info.
3342 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_FALSE,
3343 p_count => x_msg_count,
3344 p_data => x_msg_data);
3345
3346 FND_MSG_PUB.g_msg_level_threshold := FND_API.G_MISS_NUM;
3347
3348 EXCEPTION
3349
3350 WHEN FND_API.G_EXC_ERROR THEN
3351
3352 x_return_status := FND_API.G_RET_STS_ERROR ;
3353 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_FALSE,
3354 p_count => x_msg_count,
3355 p_data => x_msg_data);
3356
3357 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3358
3359 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3360 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_FALSE,
3361 p_count => x_msg_count,
3362 p_data => x_msg_data);
3363
3364 WHEN OTHERS THEN
3365
3366 IF sqlcode = -20002 THEN
3367 fnd_message.Set_Name('PV', 'PV_WF_COMP_ACTY_ERR');
3368 fnd_msg_pub.Add;
3369 ELSE
3370 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
3371 END IF;
3372
3373 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3374 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_FALSE,
3375 p_count => x_msg_count,
3376 p_data => x_msg_data);
3377
3378 end WITHDRAW_ASSIGNMENT;
3379
3380
3381 procedure ABANDON_ASSIGNMENT (
3382 p_api_version_number IN NUMBER
3383 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
3384 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
3385 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
3386 ,p_entity in VARCHAR2
3387 ,p_lead_id IN NUMBER
3388 ,p_user_name IN VARCHAR2
3389 ,p_reason_code IN varchar2
3390 ,x_return_status OUT NOCOPY VARCHAR2
3391 ,x_msg_count OUT NOCOPY NUMBER
3392 ,x_msg_data OUT NOCOPY VARCHAR2) is
3393
3394 l_api_name CONSTANT VARCHAR2(30) := 'ABANDON_ASSIGNMENT';
3395 l_api_version_number CONSTANT NUMBER := 1.0;
3396
3397 l_temp pls_integer;
3398 l_user_id NUMBER;
3399 l_resource_id NUMBER;
3400 l_partner_id NUMBER;
3401 l_lead_assignment_id NUMBER;
3402 l_party_notification_id NUMBER;
3403 l_assignment_status VARCHAR2(30);
3404 l_itemtype VARCHAR2(30);
3405 l_itemkey VARCHAR2(30);
3406 l_routing_stage VARCHAR2(30);
3407 l_wf_status VARCHAR2(30);
3408
3409 l_opp_number VARCHAR2(30);
3410 l_customer_name VARCHAR2(360);
3411 l_partner_org VARCHAR2(100);
3412 l_vendor_name VARCHAR2(100);
3413 l_opp_name VARCHAR2(240);
3414 l_action_reason VARCHAR2(100);
3415 l_assign_type_mean VARCHAR2(100);
3416 l_assignment_type VARCHAR2(100);
3417 l_category VARCHAR2(30);
3418 l_ven_user_id NUMBER;
3419 l_source_id NUMBER;
3420 l_opp_amt VARCHAR2(100);
3421 l_customer_id NUMBER;
3422 l_address_id NUMBER;
3423 l_lead_number VARCHAR2(30);
3424
3425 l_user_type_tbl JTF_VARCHAR2_TABLE_100 := JTF_VARCHAR2_TABLE_100();
3426 l_user_name_tbl JTF_VARCHAR2_TABLE_100 := JTF_VARCHAR2_TABLE_100();
3427
3428
3429 l_party_notify_rec pv_assign_util_pvt.party_notify_rec_type;
3430 l_attrib_values_rec pv_assignment_pvt.attrib_values_rec_type;
3431
3432
3433 cursor lc_all_abandon_chk (pc_itemtype varchar2,
3434 pc_itemkey varchar2) is
3435 select 1
3436 from pv_lead_assignments
3437 where wf_item_type = pc_itemtype
3438 and wf_item_key = pc_itemkey
3439 and status in (g_la_status_pt_approved, g_la_status_cm_app_for_pt);
3440
3441 cursor lc_get_partner_id (pc_user_name varchar2,
3442 pc_itemtype varchar2,
3443 pc_itemkey varchar2) is
3444 select la.partner_id,
3445 la.lead_assignment_id,
3446 fu.user_id,
3447 re.resource_id
3448 from fnd_user fu,
3449 jtf_rs_resource_extns re,
3450 hz_relationships emp,
3451 pv_partner_profiles pt,
3452 pv_lead_assignments la
3453 where fu.user_name = pc_user_name
3454 and fu.user_id = re.user_id
3455 and re.category = 'PARTY'
3456 and re.source_id = emp.party_id
3457 and emp.subject_table_name = 'HZ_PARTIES'
3458 and emp.object_table_name = 'HZ_PARTIES'
3459 and emp.directional_flag = 'F'
3460 and emp.relationship_code = 'EMPLOYEE_OF'
3461 and emp.relationship_type = 'EMPLOYMENT'
3462 and emp.status in ('A', 'I')
3463 and emp.object_id = pt.partner_party_id
3464 and pt.partner_id = la.partner_id
3465 and la.wf_item_type = pc_itemtype
3466 and la.wf_item_key = pc_itemkey
3467 and la.status in (g_la_status_pt_approved, g_la_status_cm_app_for_pt);
3468
3469 cursor lc_get_pt_org_name (pc_partner_id number) is
3470 select pt.party_name
3471 from hz_relationships pr,
3472 hz_organization_profiles op,
3473 hz_parties pt
3474 where pr.party_id = pc_partner_id
3475 and pr.subject_table_name = 'HZ_PARTIES'
3476 and pr.object_table_name = 'HZ_PARTIES'
3477 and pr.status in ('A', 'I')
3478 and pr.object_id = op.party_id
3479 and op.internal_flag = 'Y'
3480 and op.effective_end_date is null
3481 and pr.subject_id = pt.party_id
3482 and pt.status in ('A', 'I');
3483
3484 CURSOR lc_get_lead_rec(pc_lead_id NUMBER)
3485 IS
3486 SELECT a.prm_assignment_type
3487 , a.lead_number
3488 , a.description
3489 , a.total_amount||' '||a.currency_code
3490 , b.party_name
3491 FROM as_leads_all a, hz_parties b
3492 WHERE a.lead_id = pc_lead_id
3493 AND a.customer_id = b.party_id
3494 and b.status in ('A', 'I');
3495
3496 CURSOR lc_get_assign_type_meaning (pc_assignment_type varchar2)
3497 IS
3498 SELECT meaning
3499 FROM pv_lookups
3500 WHERE lookup_type = 'PV_ASSIGNMENT_TYPE'
3501 AND lookup_code = pc_assignment_type;
3502
3503 CURSOR lc_get_vendor_cat(pc_lead_id NUMBER)
3504 IS
3505 SELECT extn.category
3506 , extn.source_id
3507 , pwf.created_by
3508 FROM pv_lead_workflows pwf
3509 , jtf_rs_resource_extns extn
3510 WHERE pwf.created_by = extn.user_id
3511 AND pwf.entity = 'OPPORTUNITY'
3512 AND pwf.latest_routing_flag = 'Y'
3513 AND pwf.lead_id = pc_lead_id;
3514
3515 -- performance fix for 11.5.9
3516 /* CURSOR lc_get_ven_emp_name(pc_source_id NUMBER)
3517 IS
3518 select bg.name
3519 from per_people_x px, per_business_groups bg
3520 where px.person_id = pc_source_id
3521 and px.business_group_id = bg.business_group_id; */
3522
3523 CURSOR lc_get_ven_emp_name(pc_source_id NUMBER)
3524 IS
3525 select otl.name vendor_name
3526 from hr_all_organization_units o,
3527 hr_all_organization_units_tl otl,
3528 per_all_people_f p
3529 where o.organization_id = otl.organization_id
3530 and otl.language = userenv('lang')
3531 and o.organization_id = p.business_group_id
3532 and p.person_id = pc_source_id;
3533
3534 CURSOR lc_get_ven_pty_name(pc_source_id NUMBER)
3535 IS
3536 select hp.party_name
3537 from hz_relationships emp,hz_parties hp
3538 where emp.party_id = pc_source_id
3539 and emp.subject_table_name = 'HZ_PARTIES'
3540 and emp.object_table_name = 'HZ_PARTIES'
3541 and emp.directional_flag = 'F'
3542 and emp.relationship_code = 'EMPLOYEE_OF'
3543 and emp.relationship_type = 'EMPLOYMENT'
3544 and emp.status in ('A', 'I')
3545 and emp.object_id = hp.party_id
3546 and hp.status in ('A', 'I');
3547
3548
3549 begin
3550
3551 -- Standard call to check for call compatibility.
3552
3553 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
3554 p_api_version_number,
3555 l_api_name,
3556 G_PKG_NAME) THEN
3557 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3558
3559 END IF;
3560
3561 -- Initialize message list if p_init_msg_list is set to TRUE.
3562 IF FND_API.to_Boolean( p_init_msg_list ) THEN
3563 fnd_msg_pub.initialize;
3564 END IF;
3565
3566 if fnd_profile.value('ASF_PROFILE_DEBUG_MSG_ON') = 'Y' then
3567 FND_MSG_PUB.g_msg_level_threshold := FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW;
3568 else
3569 FND_MSG_PUB.g_msg_level_threshold := FND_API.G_MISS_NUM;
3570 end if;
3571
3572 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
3573 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
3574 fnd_message.Set_Token('TEXT', 'In ' || l_api_name || '. Lead id =' || p_lead_id);
3575 fnd_msg_pub.Add;
3576 END IF;
3577
3578 x_return_status := FND_API.G_RET_STS_SUCCESS ;
3579
3580 pv_assign_util_pvt.GetWorkflowID(p_api_version_number => 1.0,
3581 p_init_msg_list => FND_API.G_FALSE,
3582 p_commit => FND_API.G_FALSE,
3583 p_validation_level => p_validation_level,
3584 p_lead_id => p_lead_id,
3585 p_entity => p_entity,
3586 x_itemType => l_itemType,
3587 x_itemKey => l_itemKey,
3588 x_routing_status => l_routing_stage,
3589 x_wf_status => l_wf_status,
3590 x_return_status => x_return_status,
3591 x_msg_count => x_msg_count,
3592 x_msg_data => x_msg_data);
3593
3594 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
3595 raise FND_API.G_EXC_ERROR;
3596 end if;
3597
3598 if l_routing_stage <> g_r_status_active or l_routing_stage is NULL then
3599
3600 fnd_message.Set_Name('PV', 'PV_CANNOT_ABANDON');
3601 fnd_msg_pub.ADD;
3602 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3603
3604 end if;
3605
3606 open lc_get_partner_id (pc_itemtype => l_itemtype,
3607 pc_itemkey => l_itemkey,
3608 pc_user_name => p_user_name);
3609
3610 fetch lc_get_partner_id into l_partner_id, l_lead_assignment_id, l_user_id, l_resource_id;
3611 close lc_get_partner_id;
3612
3613 if l_partner_id is null then
3614
3615 fnd_message.Set_Name('PV', 'PV_NO_ABANDON_RIGHTS');
3616 fnd_message.Set_Token('P_USERNAME', p_user_name);
3617 fnd_msg_pub.ADD;
3618 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3619
3620 end if;
3621
3622 l_party_notify_rec.WF_ITEM_TYPE := l_itemtype;
3623 l_party_notify_rec.WF_ITEM_KEY := l_itemkey;
3624 l_party_notify_rec.LEAD_ASSIGNMENT_ID := l_lead_assignment_id;
3625 l_party_notify_rec.NOTIFICATION_TYPE := g_notify_type_abandoned_by;
3626 l_party_notify_rec.RESOURCE_ID := l_resource_id;
3627 l_party_notify_rec.USER_ID := l_user_id;
3628 l_party_notify_rec.USER_NAME := p_user_name;
3629 l_party_notify_rec.RESOURCE_RESPONSE := g_la_status_pt_abandoned;
3630 l_party_notify_rec.RESPONSE_DATE := sysdate;
3631 l_party_notify_rec.DECISION_MAKER_FLAG := 'Y';
3632
3633 pv_assign_util_pvt.create_party_notification(
3634 p_api_version_number => 1.0
3635 ,p_init_msg_list => FND_API.G_FALSE
3636 ,p_commit => FND_API.G_FALSE
3637 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
3638 ,P_party_notify_Rec => l_party_notify_rec
3639 ,x_party_notification_id => l_party_notification_id
3640 ,x_return_status => x_return_status
3641 ,x_msg_count => x_msg_count
3642 ,x_msg_data => x_msg_data);
3643
3644 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
3645 raise FND_API.G_EXC_ERROR;
3646 end if;
3647
3648 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
3649 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
3650 fnd_message.Set_Token('TEXT', 'Updating assignment by calling pv_assignment_pvt.UpdateAssignment for assignment id:' || l_lead_assignment_id);
3651 fnd_msg_pub.Add;
3652 END IF;
3653
3654 pv_assignment_pvt.UpdateAssignment (
3655 p_api_version_number => 1.0
3656 ,p_init_msg_list => FND_API.G_FALSE
3657 ,p_commit => FND_API.G_FALSE
3658 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
3659 ,p_action => g_asgn_action_status_update
3660 ,p_lead_assignment_id => l_lead_assignment_id
3661 ,p_status_date => sysdate
3662 ,p_status => g_la_status_pt_abandoned
3663 ,p_reason_code => p_reason_code
3664 ,p_rank => NULL
3665 ,x_msg_count => x_msg_count
3666 ,x_msg_data => x_msg_data
3667 ,x_return_status => x_return_status);
3668
3669 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
3670 raise FND_API.G_EXC_ERROR;
3671 end if;
3672
3673 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
3674 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
3675 fnd_message.Set_Token('TEXT', 'before removing preferred partner');
3676 fnd_msg_pub.Add;
3677 END IF;
3678
3679 PV_ASSIGN_UTIL_PVT.removePreferedPartner
3680 (
3681 p_api_version_number => 1.0,
3682 p_init_msg_list => FND_API.G_FALSE,
3683 p_commit => FND_API.G_FALSE,
3684 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
3685 p_lead_id => p_lead_id,
3686 p_item_type => NULL,
3687 p_item_key => NULL,
3688 p_partner_id => l_partner_id,
3689 x_return_status => x_return_status,
3690 x_msg_count => x_msg_count,
3691 x_msg_data => x_msg_data
3692 );
3693 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
3694 raise FND_API.G_EXC_ERROR;
3695 end if;
3696
3697 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
3698 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
3699 fnd_message.Set_Token('TEXT', 'after removing preferred partner');
3700 fnd_msg_pub.Add;
3701 END IF;
3702
3703 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
3704 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
3705 fnd_message.Set_Token('TEXT', 'Removing the rejected salkes team by calling pv_assignment_pvt.removeRejectedFromAccess ');
3706 fnd_msg_pub.Add;
3707 END IF;
3708
3709
3710 pv_assignment_pvt.removeRejectedFromAccess (
3711 p_api_version_number => 1.0,
3712 p_init_msg_list => FND_API.G_FALSE,
3713 p_commit => FND_API.G_FALSE,
3714 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
3715 p_itemtype => l_itemType,
3716 p_itemkey => l_itemKey,
3717 p_partner_id => l_partner_id,
3718 x_return_status => x_return_status,
3719 x_msg_count => x_msg_count,
3720 x_msg_data => x_msg_data);
3721
3722 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
3723 raise FND_API.G_EXC_ERROR;
3724 end if;
3725
3726 open lc_all_abandon_chk (pc_itemtype => l_itemtype,
3727 pc_itemkey => l_itemkey);
3728
3729 fetch lc_all_abandon_chk into l_temp;
3730 close lc_all_abandon_chk;
3731
3732 if l_temp is null then
3733
3734 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
3735 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
3736 fnd_message.Set_Token('TEXT', 'All partners have abandoned. Update pv_lead_workflow routing to ABANDONED');
3737 fnd_msg_pub.Add;
3738 END IF;
3739 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
3740 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
3741 fnd_message.Set_Token('TEXT', 'Updating the routing stage by calling pv_assignment_pvt.update_routing_stage ');
3742 fnd_msg_pub.Add;
3743 END IF;
3744
3745 pv_assignment_pvt.update_routing_stage (
3746 p_api_version_number => 1.0,
3747 p_init_msg_list => FND_API.G_FALSE,
3748 p_commit => FND_API.G_FALSE,
3749 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
3750 p_itemType => l_itemtype,
3751 p_itemKey => l_itemKey,
3752 p_routing_stage => pv_assignment_pub.g_r_status_abandoned,
3753 p_active_but_open_flag => 'N',
3754 x_return_status => x_return_status,
3755 x_msg_count => x_msg_count,
3756 x_msg_data => x_msg_data);
3757
3758 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
3759 raise FND_API.G_EXC_ERROR;
3760 end if;
3761
3762
3763 OPEN lc_get_pt_org_name(l_partner_id);
3764 FETCH lc_get_pt_org_name into l_partner_org;
3765 CLOSE lc_get_pt_org_name;
3766
3767 OPEN lc_get_lead_rec(p_lead_id);
3768 FETCH lc_get_lead_rec
3769 INTO l_assignment_type, l_lead_number,
3770 l_opp_name, l_opp_amt, l_customer_name;
3771 CLOSE lc_get_lead_rec;
3772
3773 OPEN lc_get_assign_type_meaning(l_assignment_type);
3774 FETCH lc_get_assign_type_meaning INTO l_assign_type_mean;
3775 CLOSE lc_get_assign_type_meaning;
3776
3777
3778 IF p_reason_code IS NOT NULL
3779 THEN
3780 SELECT meaning
3781 INTO l_action_reason
3782 FROM pv_lookups
3783 WHERE lookup_type = 'PV_REASON_CODES'
3784 AND lookup_code = p_reason_code;
3785 END IF;
3786
3787 OPEN lc_get_vendor_cat(p_lead_id);
3788 FETCH lc_get_vendor_cat
3789 INTO l_category, l_source_id, l_ven_user_id;
3790 CLOSE lc_get_vendor_cat;
3791
3792 IF l_category = 'EMPLOYEE' THEN
3793 OPEN lc_get_ven_emp_name(l_source_id);
3794 FETCH lc_get_ven_emp_name
3795 INTO l_vendor_name;
3796 CLOSE lc_get_ven_emp_name;
3797 ELSIF l_category = 'PARTY' THEN
3798 OPEN lc_get_ven_pty_name(l_source_id);
3799 FETCH lc_get_ven_pty_name
3800 INTO l_vendor_name;
3801 CLOSE lc_get_ven_pty_name;
3802 END IF;
3803
3804
3805 l_attrib_values_rec.am_org_name := l_vendor_name;
3806 l_attrib_values_rec.pt_org_party_id := l_partner_id;
3807 l_attrib_values_rec.lead_id := p_lead_id;
3808 l_attrib_values_rec.lead_number := l_lead_number;
3809 l_attrib_values_rec.entity_name := l_opp_name;
3810 l_attrib_values_rec.entity_amount := l_opp_amt;
3811 l_attrib_values_rec.customer_name := l_customer_name;
3812 l_attrib_values_rec.assignment_type := l_assignment_type;
3813 l_attrib_values_rec.assignment_type_mean := l_assign_type_mean;
3814
3815 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
3816 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
3817 fnd_message.Set_Token('TEXT', 'Abandoning the workflow by calling pv_assignment_pvt.AbandonWorkflow ');
3818 fnd_msg_pub.Add;
3819 END IF;
3820
3821
3822
3823 pv_assignment_pvt.AbandonWorkflow (
3824 p_api_version_number => 1.0,
3825 p_init_msg_list => FND_API.G_FALSE,
3826 p_commit => FND_API.G_FALSE,
3827 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
3828 p_creating_username => p_user_name,
3829 p_attrib_values_rec => l_attrib_values_rec,
3830 p_action_reason => l_action_reason,
3831 p_partner_org_name => l_partner_org,
3832 x_return_status => x_return_status,
3833 x_msg_count => x_msg_count,
3834 x_msg_data => x_msg_data);
3835
3836
3837 end if;
3838
3839 IF FND_API.To_Boolean ( p_commit ) THEN
3840 COMMIT WORK;
3841 END IF;
3842
3843 -- Standard call to get message count and if count is 1, get message info.
3844 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_FALSE,
3845 p_count => x_msg_count,
3846 p_data => x_msg_data);
3847
3848 FND_MSG_PUB.g_msg_level_threshold := FND_API.G_MISS_NUM;
3849
3850 EXCEPTION
3851
3852 WHEN FND_API.G_EXC_ERROR THEN
3853
3854 x_return_status := FND_API.G_RET_STS_ERROR ;
3855 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_FALSE,
3856 p_count => x_msg_count,
3857 p_data => x_msg_data);
3858
3859 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3860
3861 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3862 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_FALSE,
3863 p_count => x_msg_count,
3864 p_data => x_msg_data);
3865
3866 WHEN OTHERS THEN
3867
3868 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3869 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
3870 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_FALSE,
3871 p_count => x_msg_count,
3872 p_data => x_msg_data);
3873
3874 end ABANDON_ASSIGNMENT;
3875
3876
3877 end PV_ASSIGNMENT_PUB;