[Home] [Help]
PACKAGE BODY: APPS.PV_BG_PARTNER_MATCHING_PUB
Source
1 PACKAGE BODY PV_BG_PARTNER_MATCHING_PUB as
2 /* $Header: pvxvpmbb.pls 120.5 2006/08/17 20:01:43 amaram ship $ */
3 -- Start of Comments
4 -- Package name : PV_BG_PARTNER_MATCHING_PUB
5 -- Purpose : Background partner matching API's
6 -- NOTE :
7 -- History :
8 -- 01/07/2003 PKLIN Created.
9 --
10 -- END of Comments
11
12
13 /*-------------------------------------------------------------------------*
14 |
15 | PRIVATE CONSTANTS
16 |
17 *-------------------------------------------------------------------------*/
18 G_PKG_NAME CONSTANT VARCHAR2(30):= 'PV_BG_PARTNER_MATCHING_PUB';
19 G_FILE_NAME CONSTANT VARCHAR2(12) := 'pvxvpmbb.pls';
20
21
22 /*-------------------------------------------------------------------------*
23 |
24 | PRIVATE VARIABLES
25 |
26 *-------------------------------------------------------------------------*/
27 AS_DEBUG_HIGH_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);
28 AS_DEBUG_LOW_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
29 AS_DEBUG_MEDIUM_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM);
30 AS_DEBUG_ERROR_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_ERROR);
31
32
33 PROCEDURE Start_Partner_Matching(
34 P_Api_Version_Number IN NUMBER,
35 P_Init_Msg_List IN VARCHAR2,
36 P_Commit IN VARCHAR2,
37 P_Validation_Level IN NUMBER,
38 P_Admin_Group_Id IN NUMBER,
39 P_Identity_Salesforce_Id IN NUMBER,
40 P_Salesgroup_Id IN NUMBER,
41 P_Lead_id IN NUMBER,
42 X_Return_Status OUT NOCOPY VARCHAR2,
43 X_Msg_Count OUT NOCOPY NUMBER,
44 X_Msg_Data OUT NOCOPY VARCHAR2)
45 IS
46 l_api_name CONSTANT VARCHAR2(30)
47 := 'Start_Partner_Matching';
48 l_api_version_number CONSTANT NUMBER := 2.0;
49 l_identity_sales_member_rec AS_SALES_MEMBER_PUB.Sales_member_rec_Type;
50 l_item_type VARCHAR2(8) := 'PVXSLENW';
51 l_item_key VARCHAR2(30);
52 l_status VARCHAR2(80);
53 l_result VARCHAR2(10);
54 l_workflow_process VARCHAR2(30) := 'PV_AUTOMATED_PARTNER_MATCHING';
55 BEGIN
56 -- Standard Start of API savepoint
57 SAVEPOINT START_PARTNER_MATCHING_PVT;
58
59 -- Standard call to check for call compatibility.
60 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
61 p_api_version_number,
62 l_api_name,
63 G_PKG_NAME)
64 THEN
65 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
66 END IF;
67
68 -- Initialize message list IF p_init_msg_list is set to TRUE.
69 IF FND_API.to_Boolean( p_init_msg_list )
70 THEN
71 FND_MSG_PUB.initialize;
72 END IF;
73
74 -- Debug Message
75 IF (AS_DEBUG_LOW_ON) THEN
76 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
77 'PVT:' || l_api_name || ' Start');
78 END IF;
79
80 -- Initialize API return status to SUCCESS
81 x_return_status := FND_API.G_RET_STS_SUCCESS;
82
83 --
84 -- Api body
85 --
86 -- ******************************************************************
87 -- Validate Environment
88 -- ******************************************************************
89
90 IF FND_GLOBAL.User_Id IS NULL
91 THEN
92 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
93 THEN
94 AS_UTILITY_PVT.Set_Message(
95 p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
96 p_msg_name => 'UT_CANNOT_GET_PROFILE_VALUE',
97 p_token1 => 'PROFILE',
98 p_token1_value => 'USER_ID');
99 END IF;
100 RAISE FND_API.G_EXC_ERROR;
101 END IF;
102
103 IF (p_validation_level = fnd_api.g_valid_level_full)
104 THEN
105 AS_SALES_ORG_MANAGER_PVT.Get_CurrentUser(
106 p_api_version_number => 2.0
107 ,p_init_msg_list => p_init_msg_list
108 ,p_salesforce_id => P_Identity_Salesforce_Id
109 ,p_admin_group_id => p_admin_group_id
110 ,x_return_status => x_return_status
111 ,x_msg_count => x_msg_count
112 ,x_msg_data => x_msg_data
113 ,x_sales_member_rec => l_identity_sales_member_rec);
114
115 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
116 RAISE FND_API.G_EXC_ERROR;
117 END IF;
118 END IF;
119
120 -- Start Process :
121 -- If workflowprocess is passed, it will be run.
122 -- If workflowprocess is NOT passed, the selector FUNCTION
123 -- defined in the item type will determine which process to run.
124
125 SELECT TO_CHAR(PV_LEAD_WORKFLOWS_S.nextval) INTO l_item_key
126 FROM dual;
127
128 wf_engine.CreateProcess( ItemType => l_Item_Type,
129 ItemKey => l_Item_Key,
130 process => l_Workflow_process);
131
132 -- Initialize workflow item attributes
133 --
134 wf_engine.SetItemAttrNumber(itemtype => l_Item_Type,
135 itemkey => l_Item_Key,
136 aname => 'LEAD_ID',
137 avalue => p_lead_id);
138
139 wf_engine.SetItemAttrNumber(itemtype => l_Item_Type,
140 itemkey => l_Item_Key,
141 aname => 'IDENTITY_SALESFORCE_ID',
142 avalue => p_identity_salesforce_id);
143
144 wf_engine.SetItemAttrNumber(itemtype => l_Item_Type,
145 itemkey => l_Item_Key,
146 aname => 'SALESGROUP_ID',
147 avalue => p_salesgroup_id);
148
149 wf_engine.StartProcess(itemtype => l_Item_Type,
150 itemkey => l_Item_Key );
151
152 wf_engine.ItemStatus(itemtype => l_Item_Type,
153 itemkey => l_Item_Key,
154 status => l_status,
155 result => l_result);
156
157 IF (AS_DEBUG_LOW_ON) THEN
158 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
159 'l_status:' || l_status);
160 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
161 'l_result:' || l_result);
162 END IF;
163
164 IF l_result <> FND_API.G_RET_STS_SUCCESS AND
165 l_result <> '#NULL'
166 THEN
167 x_return_status := FND_API.G_RET_STS_ERROR;
168 END IF;
169
170 --
171 -- END of API body
172 --
173
174 -- Standard check for p_commit
175 IF FND_API.to_Boolean( p_commit )
176 THEN
177 COMMIT WORK;
178 END IF;
179
180 -- Debug Message
181 IF (AS_DEBUG_LOW_ON) THEN
182 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
183 'PVT: ' || l_api_name || ' End');
184 END IF;
185
186 -- Standard call to get message count and IF count is 1, get message info.
187 FND_MSG_PUB.Count_And_Get
188 ( p_count => x_msg_count,
189 p_data => x_msg_data );
190
191 EXCEPTION
192 WHEN FND_API.G_EXC_ERROR THEN
193 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
194 P_API_NAME => L_API_NAME
195 ,P_PKG_NAME => G_PKG_NAME
196 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
197 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
198 ,X_MSG_COUNT => X_MSG_COUNT
199 ,X_MSG_DATA => X_MSG_DATA
200 ,X_RETURN_STATUS => X_RETURN_STATUS);
201
202 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
203 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
204 P_API_NAME => L_API_NAME
205 ,P_PKG_NAME => G_PKG_NAME
206 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
207 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
208 ,X_MSG_COUNT => X_MSG_COUNT
209 ,X_MSG_DATA => X_MSG_DATA
210 ,X_RETURN_STATUS => X_RETURN_STATUS);
211
212 WHEN OTHERS THEN
213 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
214 P_API_NAME => L_API_NAME
215 ,P_PKG_NAME => G_PKG_NAME
216 ,P_EXCEPTION_LEVEL => AS_UTILITY_PVT.G_EXC_OTHERS
217 ,P_SQLCODE => SQLCODE
218 ,P_SQLERRM => SQLERRM
219 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
220 ,X_MSG_COUNT => X_MSG_COUNT
221 ,X_MSG_DATA => X_MSG_DATA
222 ,X_RETURN_STATUS => X_RETURN_STATUS);
223
224 END Start_Partner_Matching;
225
226 PROCEDURE Partner_Matching(
227 itemtype IN VARCHAR2,
228 itemkey IN VARCHAR2,
229 actid IN NUMBER,
230 funcmode IN VARCHAR2,
231 result OUT NOCOPY VARCHAR2)
232 IS
233 l_api_name CONSTANT VARCHAR2(30) := 'Partner_Matching';
234 l_lead_id NUMBER;
235 l_identity_salesforce_id NUMBER;
236 l_salesgroup_id NUMBER;
237 l_user_name VARCHAR2(100);
238 --l_indirect_channel_flag VARCHAR2(1);
239 --l_routing_status VARCHAR2(30);
240 l_selected_rule_id NUMBER;
241 l_lead_name VARCHAR2(240);
242 l_process_rule_name VARCHAR2(100);
243 l_matched_partner_count NUMBER;
244 l_failure_code VARCHAR2(30);
245 l_lead_workflow_rec pv_assign_util_pvt.lead_workflow_rec_type;
246 l_itemKey VARCHAR2(8);
247 l_Sales_Team_Rec AS_ACCESS_PUB.Sales_Team_Rec_Type;
248 l_access_profile_rec AS_ACCESS_PUB.ACCESS_PROFILE_REC_TYPE;
249 l_access_id NUMBER;
250 l_return_status VARCHAR2(1);
251 l_msg_count NUMBER;
252 l_msg_data VARCHAR2(2000);
253 l_msg_data2 VARCHAR2(2000);
254 l_open_opportunity_flag BOOLEAN;
255 l_routing_exist_flag BOOLEAN;
256
257
258 CURSOR C_Get_User_Name(c_resource_id NUMBER) IS
259 SELECT user_name
260 FROM jtf_rs_resource_extns
261 WHERE resource_id = c_resource_id;
262
263 CURSOR c_get_lead_name(c_lead_id NUMBER) IS
264 SELECT description
265 FROM as_leads_all
266 WHERE lead_id = c_lead_id;
267
268 CURSOR c_get_lead_rule_name(c_lead_id NUMBER, c_process_rule_id NUMBER) IS
269 SELECT opp.description, rule.process_rule_name
270 FROM as_leads_all opp, pv_process_rules_vl rule
271 WHERE opp.lead_id = c_lead_id
272 AND rule.process_rule_id = c_process_rule_id;
273
274 CURSOR c_get_lead_info(c_lead_id NUMBER) IS
275 SELECT customer_id, address_id
276 FROM as_leads_all
277 WHERE lead_id = c_lead_id;
278
279 CURSOR c_get_group_id (c_resource_id NUMBER) IS
280 SELECT grp.group_id
281 FROM JTF_RS_GROUP_MEMBERS mem,
282 JTF_RS_ROLE_RELATIONS rrel,
283 JTF_RS_ROLES_B role,
284 JTF_RS_GROUP_USAGES u,
285 JTF_RS_GROUPS_B grp
286 WHERE mem.group_member_id = rrel.role_resource_id
287 AND rrel.role_resource_type = 'RS_GROUP_MEMBER'
288 AND rrel.role_id = role.role_id
289 AND role.role_type_code in ('SALES','TELESALES','FIELDSALES','PRM')
290 AND mem.delete_flag <> 'Y'
291 AND rrel.delete_flag <> 'Y'
292 AND SYSDATE BETWEEN rrel.start_date_active AND
293 NVL(rrel.end_date_active,SYSDATE)
294 AND mem.resource_id = c_resource_id
295 AND mem.group_id = u.group_id
296 AND u.usage = 'SALES'
297 AND mem.group_id = grp.group_id
298 AND SYSDATE BETWEEN grp.start_date_active AND
299 NVL(grp.end_date_active,SYSDATE)
300 AND ROWNUM < 2;
301
302 CURSOR C_Get_Resource_Id(c_user_name VARCHAR2) IS
303 SELECT resource_id
304 FROM jtf_rs_resource_extns
305 WHERE user_name = c_user_name;
306
307 CURSOR get_person_id_csr(c_salesforce_id NUMBER) is
308 SELECT employee_person_id
309 FROM as_salesforce_v
310 WHERE salesforce_id = c_salesforce_id;
311
312 BEGIN
313 IF (AS_DEBUG_LOW_ON) THEN
314 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
315 'Partner_Matching: Start');
316 END IF;
317 IF funcmode = 'RUN'
318 THEN
319 result := FND_API.G_RET_STS_SUCCESS;
320 l_lead_id := wf_engine.GetItemAttrNumber(
321 itemtype => itemtype,
322 itemkey => itemkey,
323 aname => 'LEAD_ID');
324
325 l_identity_salesforce_id := wf_engine.GetItemAttrNumber(
326 itemtype => itemtype,
327 itemkey => itemkey,
328 aname => 'IDENTITY_SALESFORCE_ID');
329
330 l_salesgroup_id := wf_engine.GetItemAttrNumber(
331 itemtype => itemtype,
332 itemkey => itemkey,
333 aname => 'SALESGROUP_ID');
334
335
336 IF (AS_DEBUG_LOW_ON) THEN
337 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
338 'lead_id?' || l_lead_id);
339 END IF;
340
341 -- ==========================================================================
342 -- Pre-Routing Check
343 --
344 -- Make sure the opportunity is "open" and the routing is unassigned. That
345 -- is, no routing has already been done for this opportunity.
346 -- ==========================================================================
347 l_open_opportunity_flag := TRUE;
348 l_routing_exist_flag := FALSE;
349
350 FOR x IN (
351 SELECT b.opp_open_status_flag, c.lead_id
352 FROM as_leads_all a,
353 as_statuses_b b,
354 pv_lead_workflows c
355 WHERE a.lead_id = l_lead_id AND
356 a.status = b.status_code AND
357 b.opp_flag = 'Y' AND
358 a.lead_id = c.lead_id (+)
359 )
360 LOOP
361 -- -----------------------------------------------------------------------
362 -- This is not an "open" opportunity. It cannot be routed.
363 -- -----------------------------------------------------------------------
364 IF (x.opp_open_status_flag <> 'Y') THEN
365 l_open_opportunity_flag := FALSE;
366
367 FOR x IN (SELECT description FROM as_leads_all WHERE lead_id = l_lead_id) LOOP
368 fnd_message.SET_NAME('PV', 'PV_OPP_ROUTING_CLOSED_OPP');
369 fnd_message.SET_TOKEN('OPPORTUNITY_NAME', x.description);
370 fnd_message.SET_TOKEN('LEAD_ID' , l_lead_id);
371 fnd_msg_pub.ADD;
372 END LOOP;
373 END IF;
374
375 -- -----------------------------------------------------------------------
376 -- This opportunity has already been routed.
377 -- -----------------------------------------------------------------------
378 IF (x.lead_id IS NOT NULL) THEN
379 FOR x IN (SELECT description FROM as_leads_all WHERE lead_id = l_lead_id) LOOP
380 fnd_message.SET_NAME('PV', 'PV_OPP_ROUTING_ALREADY_EXISTS');
381 fnd_message.SET_TOKEN('OPPORTUNITY_NAME', x.description);
382 fnd_message.SET_TOKEN('LEAD_ID' , l_lead_id);
383 fnd_msg_pub.ADD;
384 END LOOP;
385
386 l_routing_exist_flag := TRUE;
387 END IF;
388 END LOOP;
389
390
391 -- --------------------------------------------------------------------
392 -- Routing/Partner Matching is only allowed if there are no previous
393 -- routings. i.e. there should be no record exists in pv_lead_workflows
394 -- for this opportunity (lead_id).
395 -- In addition, the opportunity must be an "open" opportunity.
396 -- --------------------------------------------------------------------
397 IF (l_open_opportunity_flag AND (NOT l_routing_exist_flag)) THEN
398 OPEN c_get_user_name(l_identity_salesforce_id);
399 FETCH c_get_user_name INTO l_user_name;
400 CLOSE c_get_user_name;
401
402 pv_opp_match_pub.Clear_Rules_Cache;
403 pv_opp_match_pub.opportunity_selection(
404 P_Api_Version => 1.0,
405 P_Init_Msg_List => FND_API.G_TRUE,
406 P_Commit => FND_API.G_FALSE,
407 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
408 p_entity_id => l_lead_id,
409 p_entity => 'LEAD',
410 p_user_name => l_user_name,
411 p_resource_id => l_identity_salesforce_id,
412 x_selected_rule_id => l_selected_rule_id,
413 x_matched_partner_count => l_matched_partner_count,
414 x_failure_code => l_failure_code,
415 X_Return_Status => l_return_status,
416 X_Msg_Count => l_msg_count,
417 X_Msg_Data => l_msg_data);
418
419 -- Raise exception when returning from API
420 -- Exception handling will get the last message and set
421 -- it in workflow.
422 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
423 result := FND_API.G_RET_STS_ERROR;
424 RAISE FND_API.G_EXC_ERROR;
425 END IF;
426 IF (AS_DEBUG_LOW_ON) THEN
427 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
428 'selected_rule_id=' || l_selected_rule_id);
429 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
430 'matched_partner_count=' || l_matched_partner_count);
431 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
432 'failure_code=' || l_failure_code);
433 END IF;
434
435 IF l_selected_rule_id IS NULL
436 THEN
437 OPEN c_get_lead_name(l_lead_id);
438 FETCH c_get_lead_name INTO l_lead_name;
439 CLOSE c_get_lead_name;
440
441 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
442 THEN
443 FND_MESSAGE.Set_Name('PV', 'PV_OPP_NOT_MATCH_RULE');
444 FND_MESSAGE.Set_Token('LEAD_NAME', l_lead_name);
445 FND_MSG_PUB.Add;
446 END IF;
447
448 result := 'COMPLETE';
449 RAISE FND_API.G_EXC_ERROR;
450 END IF;
451
452 IF l_matched_partner_count = 0
453 THEN
454 OPEN c_get_lead_rule_name(l_lead_id, l_selected_rule_id);
455 FETCH c_get_lead_rule_name INTO l_lead_name,
456 l_process_rule_name;
457 CLOSE c_get_lead_rule_name;
458
459 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
460 THEN
461 FND_MESSAGE.Set_Name('PV', 'PV_OPP_NOT_MATCH_PARTNER');
462 FND_MESSAGE.Set_Token('LEAD_NAME', l_lead_name);
463 FND_MESSAGE.Set_Token('RULE_NAME', l_process_rule_name);
464 FND_MSG_PUB.Add;
465 END IF;
466
467 result := 'COMPLETE';
468 RAISE FND_API.G_EXC_ERROR;
469 END IF;
470 END IF;
471
472 END IF; -- function mode check
473 IF (AS_DEBUG_LOW_ON) THEN
474 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
475 'Partner_Matching: End');
476 END IF;
477
478 EXCEPTION
479 WHEN FND_API.G_EXC_ERROR THEN
480
481 fnd_msg_pub.Count_And_Get(
482 p_encoded => FND_API.G_TRUE
483 ,p_count => l_msg_count
484 ,p_data => l_msg_data);
485
486 l_msg_data := FND_MSG_PUB.Get(
487 p_msg_index => FND_MSG_PUB.Count_Msg,
488 p_encoded => FND_API.G_FALSE);
489
490 IF l_failure_code IS NOT NULL
491 THEN
492 ROLLBACK;
493 END IF;
494
495 l_Sales_Team_Rec.salesforce_id :=
496 FND_PROFILE.Value('PV_BATCH_ASSIGN_USER_NAME');
497
498 l_lead_workflow_rec.failure_code := l_failure_code;
499 l_lead_workflow_rec.failure_message := l_msg_data;
500
501 -- Create a row in PV Lead Workflow table.
502 l_lead_workflow_rec.last_updated_by := fnd_global.user_id;
503 l_lead_workflow_rec.created_by := fnd_global.user_id;
504 l_lead_workflow_rec.lead_id := l_lead_id;
505 l_lead_workflow_rec.entity := 'OPPORTUNITY';
506 l_lead_workflow_rec.wf_item_type :=
507 pv_workflow_pub.g_wf_itemtype_pvasgnmt;
508 l_lead_workflow_rec.wf_status := pv_assignment_pub.g_wf_status_closed;
509 l_lead_workflow_rec.bypass_cm_ok_flag := NULL;
510 l_lead_workflow_rec.latest_routing_flag := 'Y';
511 -- l_lead_workflow_rec.routing_status := pv_assignment_pub.g_r_status_failed_auto;
512 l_lead_workflow_rec.routing_status := 'FAILED_AUTO_ASSIGN';
513
514 pv_assign_util_pvt.Create_lead_workflow_row
515 (p_api_version_number => 1.0
516 ,p_init_msg_list => FND_API.G_FALSE
517 ,p_commit => FND_API.G_FALSE
518 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
519 ,p_workflow_rec => l_lead_workflow_rec
520 ,x_ItemKey => l_itemKey
521 ,x_return_status => l_return_status
522 ,x_msg_count => l_msg_count
523 ,x_msg_data => l_msg_data);
524
525 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
526 l_msg_data := FND_MSG_PUB.Get(
527 p_msg_index => FND_MSG_PUB.Count_Msg,
528 p_encoded => FND_API.G_FALSE);
529 result := FND_API.G_RET_STS_ERROR;
530 wf_core.token('STACK',l_msg_data);
531 wf_core.raise('WFNTF_ERROR_STACK');
532 wf_core.context(G_PKG_NAME, l_api_name, l_msg_data);
533 RAISE;
534 END IF;
535
536 IF (AS_DEBUG_LOW_ON) THEN
537 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
538 'lwf rt status = ' || l_Return_Status);
539 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
540 'itemKey = ' || l_itemKey);
541 END IF;
542 OPEN c_get_lead_info(l_lead_id);
543 FETCH c_get_lead_info INTO l_Sales_Team_Rec.customer_id,
544 l_Sales_Team_Rec.address_id;
545 CLOSE c_get_lead_info;
546
547 -- Create a sales team member for the opportunity
548 l_Sales_Team_Rec.last_updated_by := FND_GLOBAL.USER_ID;
549 l_Sales_Team_Rec.last_update_date := SYSDATE;
550 l_Sales_Team_Rec.creation_date := SYSDATE;
551 l_Sales_Team_Rec.created_by := FND_GLOBAL.USER_ID;
552 l_Sales_Team_Rec.last_update_login := FND_GLOBAL.CONC_LOGIN_ID;
553
554
555 -- ----------------------------------------------------------------------
556 -- Run Create_Salesteam only when an assignment manager is found in the
557 -- profile PV_BATCH_ASSIGN_USER_NAME ().
558 -- ----------------------------------------------------------------------
559 IF (l_Sales_Team_Rec.salesforce_id IS NOT NULL) THEN
560 --l_Sales_Team_Rec.partner_cont_party_id := p_partner_cont_party_id;
561 l_Sales_Team_Rec.lead_id := l_lead_id;
562 l_Sales_Team_Rec.team_leader_flag := 'Y';
563 l_Sales_Team_Rec.reassign_flag := 'N';
564 l_Sales_Team_Rec.freeze_flag := 'Y';
565 -- obsoleting AS_DEFAULT_FREEZE_FLAG in R12
566 --nvl(FND_PROFILE.Value('AS_DEFAULT_FREEZE_FLAG'), 'Y');
567
568 OPEN c_get_group_id(l_Sales_Team_Rec.salesforce_id);
569 FETCH c_get_group_id INTO l_sales_team_rec.sales_group_id;
570 CLOSE c_get_group_id;
571
572 IF l_sales_team_rec.sales_group_id = FND_API.G_MISS_NUM
573 THEN
574 l_sales_team_rec.sales_group_id := NULL;
575 END IF;
576
577 l_sales_team_rec.salesforce_role_code := null;
578 -- obsoleting AS_DEF_OPP_ST_ROLE in R12
579 --FND_PROFILE.Value('AS_DEF_OPP_ST_ROLE');
580
581 OPEN get_person_id_csr(l_Sales_Team_Rec.salesforce_id);
582 FETCH get_person_id_csr into l_Sales_Team_Rec.person_id;
583
584 IF (get_person_id_csr%NOTFOUND)
585 THEN
586 l_Sales_Team_Rec.person_id := NULL;
587 END IF;
588 CLOSE get_person_id_csr;
589
590 l_Sales_Team_Rec.created_by_TAP_flag := 'N';
591 l_sales_team_rec.owner_flag := 'N';
592
593 IF (AS_DEBUG_LOW_ON) THEN
594 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
595 'sf_id=' || l_Sales_Team_Rec.salesforce_id);
596 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
597 'sg_id=' || l_Sales_Team_Rec.sales_group_id);
598 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
599 'Calling Create_SalesTeam');
600 END IF;
601 AS_ACCESS_PUB.Create_SalesTeam (
602 p_api_version_number => 2.0
603 ,p_init_msg_list => FND_API.G_FALSE
604 ,p_commit => FND_API.G_FALSE
605 ,p_validation_level => FND_API.G_VALID_LEVEL_NONE
606 ,p_access_profile_rec => l_access_profile_rec
607 ,p_check_access_flag => 'N' -- P_Check_Access_flag
608 ,p_admin_flag => 'N'
609 ,p_admin_group_id => NULL
610 ,p_identity_salesforce_id => l_identity_salesforce_id
611 ,p_sales_team_rec => l_Sales_Team_Rec
612 ,X_Return_Status => l_Return_Status
613 ,X_Msg_Count => l_Msg_Count
614 ,X_Msg_Data => l_Msg_Data2
615 ,x_access_id => l_Access_Id
616 );
617
618 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
619 l_msg_data := FND_MSG_PUB.Get(
620 p_msg_index => FND_MSG_PUB.Count_Msg,
621 p_encoded => FND_API.G_FALSE);
622 result := FND_API.G_RET_STS_ERROR;
623 wf_core.token('STACK',l_msg_data);
624
625 -- this is what makes the workflow result turn RED!
626 wf_core.raise('WFNTF_ERROR_STACK');
627 wf_core.context(G_PKG_NAME, l_api_name, l_msg_data);
628 RAISE;
629 END IF;
630
631 IF (AS_DEBUG_LOW_ON) THEN
632 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
633 'acc rt status = ' || l_Return_Status);
634 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
635 'Create_SalesTeam:l_access_id = ' || l_access_id);
636 END IF;
637 END IF;
638
639 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
640
641 fnd_msg_pub.Count_And_Get(
642 p_encoded => FND_API.G_TRUE
643 ,p_count => l_msg_count
644 ,p_data => l_msg_data);
645
646 l_msg_data := FND_MSG_PUB.Get(
647 p_msg_index => FND_MSG_PUB.Count_Msg,
648 p_encoded => FND_API.G_FALSE);
649 wf_core.token('STACK',l_msg_data);
650 wf_core.raise('WFNTF_ERROR_STACK');
651
652 wf_core.context(G_PKG_NAME, l_api_name,l_msg_data);
653 raise;
654
655 WHEN OTHERS THEN
656
657 fnd_msg_pub.Count_And_Get(
658 p_encoded => FND_API.G_TRUE
659 ,p_count => l_msg_count
660 ,p_data => l_msg_data);
661
662 wf_core.token('STACK', SQLERRM);
663 wf_core.raise('WFNTF_ERROR_STACK');
664 wf_core.context(G_PKG_NAME, l_api_name,l_msg_data);
665 raise;
666
667 END Partner_Matching;
668
669
670 PROCEDURE Start_Campaign_Assignment(
671 P_Api_Version_Number IN NUMBER,
672 P_Init_Msg_List IN VARCHAR2,
673 P_Commit IN VARCHAR2,
674 P_Validation_Level IN NUMBER,
675 P_Identity_Salesforce_Id IN NUMBER,
676 P_Lead_id IN NUMBER,
677 X_Return_Status OUT NOCOPY VARCHAR2,
678 X_Msg_Count OUT NOCOPY NUMBER,
679 X_Msg_Data OUT NOCOPY VARCHAR2)
680 IS
681 l_api_name CONSTANT VARCHAR2(30)
682 := 'Start_Campaign_Assignment';
683 l_api_version_number CONSTANT NUMBER := 1.0;
684 l_identity_sales_member_rec AS_SALES_MEMBER_PUB.Sales_member_rec_Type;
685 l_item_type VARCHAR2(8) := 'PVXSLENW';
686 l_item_key VARCHAR2(30);
687 l_flag VARCHAR2(30);
688 l_status VARCHAR2(80);
689 l_result VARCHAR2(10);
690 l_workflow_process VARCHAR2(30) := 'PV_CAMPAIGN_ROUTING';
691
692 CURSOR lc_check ( pc_lead_id NUMBER)
693 IS
694 SELECT 'X' flag
695 FROM pv_lead_workflows
696 WHERE lead_id = pc_lead_id
697 AND latest_routing_flag = 'Y'
698 AND routing_status IN ('ACTIVE','MATCHED','OFFERED');
699
700 BEGIN
701 SAVEPOINT Start_Campaign_Assignment;
702
703 -- Standard call to check for call compatibility.
704 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
705 p_api_version_number,
706 l_api_name,
707 G_PKG_NAME)
708 THEN
709 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
710 END IF;
711
712 -- Initialize message list IF p_init_msg_list is set to TRUE.
713 IF FND_API.to_Boolean( p_init_msg_list )
714 THEN
715 FND_MSG_PUB.initialize;
716 END IF;
717
718 -- Debug Message
719 IF (AS_DEBUG_LOW_ON) THEN
720 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
721 'PVT:' || l_api_name || ' Start');
722 END IF;
723
724 -- Initialize API return status to SUCCESS
725 x_return_status := FND_API.G_RET_STS_SUCCESS;
726
727 --
728 -- Api body
729 --
730 -- ******************************************************************
731 -- Validate Environment
732 -- ******************************************************************
733
734 IF FND_GLOBAL.User_Id IS NULL
735 THEN
736 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
737 THEN
738 AS_UTILITY_PVT.Set_Message(
739 p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
740 p_msg_name => 'UT_CANNOT_GET_PROFILE_VALUE',
741 p_token1 => 'PROFILE',
742 p_token1_value => 'USER_ID');
743 END IF;
744 RAISE FND_API.G_EXC_ERROR;
745 END IF;
746
747 -- Start Process :
748 -- If workflowprocess is passed, it will be run.
749 -- If workflowprocess is NOT passed, the selector FUNCTION
750 -- defined in the item type will determine which process to run.
751
752 OPEN lc_check (p_lead_id);
753 FETCH lc_check INTO l_flag;
754 CLOSE lc_check;
755
756 IF l_flag IS NULL THEN
757 SELECT TO_CHAR(PV_LEAD_WORKFLOWS_S.nextval) INTO l_item_key FROM dual;
758
759 wf_engine.CreateProcess( ItemType => l_Item_Type,
760 ItemKey => l_Item_Key,
761 process => l_Workflow_process);
762
763 -- Initialize workflow item attributes
764 --
765 wf_engine.SetItemAttrNumber(itemtype => l_Item_Type,
766 itemkey => l_Item_Key,
767 aname => 'LEAD_ID',
768 avalue => p_lead_id);
769
770 wf_engine.SetItemAttrNumber(itemtype => l_Item_Type,
771 itemkey => l_Item_Key,
772 aname => 'IDENTITY_SALESFORCE_ID',
773 avalue => p_identity_salesforce_id);
774
775 wf_engine.StartProcess(itemtype => l_Item_Type,
776 itemkey => l_Item_Key );
777
778 wf_engine.ItemStatus(itemtype => l_Item_Type,
779 itemkey => l_Item_Key,
780 status => l_status,
781 result => l_result);
782 END IF;
783 IF (AS_DEBUG_LOW_ON) THEN
784 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'l_status:' || l_status);
785 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'l_result:' || l_result);
786 END IF;
787
788 IF l_result <> FND_API.G_RET_STS_SUCCESS AND l_result <> '#NULL'
789 THEN
790 x_return_status := FND_API.G_RET_STS_ERROR;
791 END IF;
792
793 -- Standard check for p_commit
794 IF FND_API.to_Boolean( p_commit )
795 THEN
796 COMMIT WORK;
797 END IF;
798
799 -- Debug Message
800 IF (AS_DEBUG_LOW_ON) THEN
801 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
802 'PVT: ' || l_api_name || ' End');
803 END IF;
804
805 -- Standard call to get message count and IF count is 1, get message info.
806 FND_MSG_PUB.Count_And_Get
807 ( p_count => x_msg_count,
808 p_data => x_msg_data );
809
810 EXCEPTION
811 WHEN FND_API.G_EXC_ERROR THEN
812 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
813 P_API_NAME => L_API_NAME
814 ,P_PKG_NAME => G_PKG_NAME
815 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
816 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
817 ,X_MSG_COUNT => X_MSG_COUNT
818 ,X_MSG_DATA => X_MSG_DATA
819 ,X_RETURN_STATUS => X_RETURN_STATUS);
820
821 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
822 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
823 P_API_NAME => L_API_NAME
824 ,P_PKG_NAME => G_PKG_NAME
825 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
826 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
827 ,X_MSG_COUNT => X_MSG_COUNT
828 ,X_MSG_DATA => X_MSG_DATA
829 ,X_RETURN_STATUS => X_RETURN_STATUS);
830
831 WHEN OTHERS THEN
832 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
833 P_API_NAME => L_API_NAME
834 ,P_PKG_NAME => G_PKG_NAME
835 ,P_EXCEPTION_LEVEL => AS_UTILITY_PVT.G_EXC_OTHERS
836 ,P_SQLCODE => SQLCODE
837 ,P_SQLERRM => SQLERRM
838 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
839 ,X_MSG_COUNT => X_MSG_COUNT
840 ,X_MSG_DATA => X_MSG_DATA
841 ,X_RETURN_STATUS => X_RETURN_STATUS);
842
843 END Start_Campaign_Assignment;
844
845
846 PROCEDURE Campaign_Routing(
847 itemtype IN VARCHAR2,
848 itemkey IN VARCHAR2,
849 actid IN NUMBER,
850 funcmode IN VARCHAR2,
851 result OUT NOCOPY VARCHAR2)
852 IS
853 l_api_name CONSTANT VARCHAR2(30) := 'Campaign_Routing';
854 l_lead_id NUMBER;
855 l_identity_salesforce_id NUMBER;
856 l_salesgroup_id NUMBER;
857 l_user_name VARCHAR2(100);
858 l_failure_code VARCHAR2(30);
859 l_itemkey VARCHAR2(30);
860 l_lead_workflow_rec pv_assign_util_pvt.lead_workflow_rec_type;
861 l_sales_team_rec as_access_pub.sales_team_rec_type;
862 l_access_profile_rec AS_ACCESS_PUB.ACCESS_PROFILE_REC_TYPE;
863 l_access_id NUMBER;
864 l_return_status VARCHAR2(1);
865 l_msg_count NUMBER;
866 l_msg_data VARCHAR2(2000);
867 l_msg_data2 VARCHAR2(2000);
868 l_open_opportunity_flag BOOLEAN;
869 l_routing_exist_flag BOOLEAN;
870
871 CURSOR C_Get_User_Name(c_resource_id NUMBER) IS
872 SELECT user_name
873 FROM jtf_rs_resource_extns
874 WHERE resource_id = c_resource_id;
875
876 CURSOR c_get_group_id (c_resource_id NUMBER) IS
877 SELECT grp.group_id
878 FROM JTF_RS_GROUP_MEMBERS mem,
879 JTF_RS_ROLE_RELATIONS rrel,
880 JTF_RS_ROLES_B role,
881 JTF_RS_GROUP_USAGES u,
882 JTF_RS_GROUPS_B grp
883 WHERE mem.group_member_id = rrel.role_resource_id
884 AND rrel.role_resource_type = 'RS_GROUP_MEMBER'
885 AND rrel.role_id = role.role_id
886 AND role.role_type_code in ('SALES','TELESALES','FIELDSALES','PRM')
887 AND mem.delete_flag <> 'Y'
888 AND rrel.delete_flag <> 'Y'
889 AND SYSDATE BETWEEN rrel.start_date_active AND
890 NVL(rrel.end_date_active,SYSDATE)
891 AND mem.resource_id = c_resource_id
892 AND mem.group_id = u.group_id
893 AND u.usage = 'SALES'
894 AND mem.group_id = grp.group_id
895 AND SYSDATE BETWEEN grp.start_date_active AND
896 NVL(grp.end_date_active,SYSDATE)
897 AND ROWNUM < 2;
898
899 CURSOR get_person_id_csr(c_salesforce_id NUMBER) is
900 SELECT employee_person_id
901 FROM as_salesforce_v
902 WHERE salesforce_id = c_salesforce_id;
903
904 cursor lc_partners (pc_source_promotion_id number) is
905 SELECT distinct acp.partner_id, rownum
906 FROM ams_source_codes ac, ams_act_partners acp, pv_partner_profiles pp
907 WHERE ac.source_code_id = pc_source_promotion_id
908 AND ac.arc_source_code_for in ('CAMP', 'CSCH') AND ac.source_code_for_id = acp.act_partner_used_by_id
909 AND acp.arc_act_partner_used_by = ac.arc_source_code_for AND acp.partner_id = pp.partner_id;
910
911 cursor lc_get_lead_detail (pc_lead_id number) is
912 select customer_id, address_id, source_promotion_id from as_leads_all where lead_id = pc_lead_id;
913
914 l_partner_id_tbl JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
915 l_partner_rank_tbl JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
916 l_partner_source_tbl JTF_VARCHAR2_TABLE_100 := JTF_VARCHAR2_TABLE_100();
917
918 l_partner_id_tmp NUMBER;
919 l_partner_rank_tmp NUMBER;
920 l_partner_source_tmp VARCHAR2(10);
921 l_countRow NUMBER := 1;
922 l_source_promotion_id NUMBER;
923 l_bypass_cm_ok_flag VARCHAr2(1);
924 l_assignment_type VARCHAR2(30);
925 l_address_id NUMBER;
926 l_customer_id NUMBER;
927 l_partner_resource_id NUMBER;
928
929 BEGIN
930 IF funcmode = 'RUN' THEN
931
932 IF (AS_DEBUG_LOW_ON) THEN
933 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Campaign_Routing: Start');
934 END IF;
935
936 result := FND_API.G_RET_STS_SUCCESS;
937 l_lead_id := wf_engine.GetItemAttrNumber(
938 itemtype => itemtype,
939 itemkey => itemkey,
940 aname => 'LEAD_ID');
941
942 l_identity_salesforce_id := wf_engine.GetItemAttrNumber(
943 itemtype => itemtype,
944 itemkey => itemkey,
945 aname => 'IDENTITY_SALESFORCE_ID');
946
947 -- ==========================================================================
948 -- Pre-Routing Check
949 --
950 -- Make sure the opportunity is "open" and the routing is unassigned. That
951 -- is, no routing has already been done for this opportunity.
952 -- ==========================================================================
953 l_open_opportunity_flag := TRUE;
954 l_routing_exist_flag := FALSE;
955
956 FOR x IN (
957 SELECT b.opp_open_status_flag, c.lead_id , c.routing_status
958 FROM as_leads_all a,
959 as_statuses_b b,
960 pv_lead_workflows c
961 WHERE a.lead_id = l_lead_id AND a.status = b.status_code AND
962 b.opp_flag = 'Y' AND a.lead_id = c.lead_id (+) and c.latest_routing_flag (+) = 'Y')
963 LOOP
964 -- -----------------------------------------------------------------------
965 -- This is not an "open" opportunity. It cannot be routed.
966 -- -----------------------------------------------------------------------
967 IF (x.opp_open_status_flag <> 'Y') THEN
968 l_open_opportunity_flag := FALSE;
969
970 FOR x IN (SELECT description FROM as_leads_all WHERE lead_id = l_lead_id) LOOP
971 fnd_message.SET_NAME('PV', 'PV_OPP_ROUTING_CLOSED_OPP');
972 fnd_message.SET_TOKEN('OPPORTUNITY_NAME', x.description);
973 fnd_message.SET_TOKEN('LEAD_ID' , l_lead_id);
974 fnd_msg_pub.ADD;
975 END LOOP;
976 END IF;
977
978 -- -----------------------------------------------------------------------
979 -- This opportunity has already been routed.
980 -- -----------------------------------------------------------------------
981 IF (x.lead_id IS NOT NULL AND x.routing_status IN ('ACTIVE','MATCHED','OFFERED')) THEN
982 FOR x IN (SELECT description FROM as_leads_all WHERE lead_id = l_lead_id) LOOP
983 fnd_message.SET_NAME('PV', 'PV_OPP_ROUTING_ALREADY_EXISTS');
984 fnd_message.SET_TOKEN('OPPORTUNITY_NAME', x.description);
985 fnd_message.SET_TOKEN('LEAD_ID' , l_lead_id);
986 fnd_msg_pub.ADD;
987 END LOOP;
988
989 l_routing_exist_flag := TRUE;
990 END IF;
991 END LOOP;
992
993 -- --------------------------------------------------------------------
994 -- Routing/Partner Matching is only allowed if there are no previous
995 -- routings. i.e. there should be no record exists in pv_lead_workflows
996 -- for this opportunity (lead_id).
997 -- In addition, the opportunity must be an "open" opportunity.
998 -- --------------------------------------------------------------------
999 IF (l_open_opportunity_flag AND (NOT l_routing_exist_flag)) THEN
1000
1001 IF (AS_DEBUG_LOW_ON) THEN
1002 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1003 'This is an open oppty and is not being routed');
1004 END IF;
1005
1006 OPEN c_get_user_name(l_identity_salesforce_id);
1007 FETCH c_get_user_name INTO l_user_name;
1008 CLOSE c_get_user_name;
1009
1010 open lc_get_lead_detail (pc_lead_id => l_lead_id);
1011 fetch lc_get_lead_detail into l_customer_id, l_address_id, l_source_promotion_id;
1012 close lc_get_lead_detail;
1013
1014 if l_source_promotion_id is not null then
1015
1016 OPEN lc_partners(pc_source_promotion_id => l_source_promotion_id);
1017 LOOP
1018 FETCH lc_partners INTO l_partner_id_tmp, l_partner_rank_tmp;
1019 EXIT WHEN lc_partners%NOTFOUND;
1020
1021 l_partner_id_tbl.extend;
1022 l_partner_id_tbl(l_countRow) := l_partner_id_tmp;
1023
1024 l_partner_rank_tbl.extend;
1025 l_partner_rank_tbl(l_countRow) := l_partner_rank_tmp;
1026
1027 l_partner_source_tbl.extend;
1028 l_partner_source_tbl(l_countRow) := 'CAMPAIGN';
1029
1030 l_countRow := l_countRow + 1;
1031 END LOOP;
1032 close lc_partners;
1033
1034 end if;
1035
1036 IF (AS_DEBUG_LOW_ON) THEN
1037 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1038 'Number of partners attach to campaign: ' || l_partner_id_tbl.count);
1039 end if;
1040
1041 if l_partner_id_tbl.count = 0 then
1042 return;
1043 end if;
1044
1045 IF l_partner_id_tbl.count = 1 THEN
1046 l_assignment_type := 'SINGLE';
1047 ELSE
1048 l_assignment_type := FND_PROFILE.value('PV_DEFAULT_ASSIGNMENT_TYPE');
1049 END IF;
1050
1051 l_bypass_cm_ok_flag := nvl(FND_PROFILE.value('PV_CM_APPROVAL_FOR_CAMPAIGN'),'N');
1052
1053 IF (FND_PROFILE.value('PV_AUTO_ROUTE_FOR_CAMPAIGN') = 'Y') THEN
1054
1055 IF (AS_DEBUG_LOW_ON) THEN
1056 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1057 'Before calling Create Assignment' || l_lead_id || 'user name ' ||
1058 l_user_name || 'assn type ' || l_assignment_type );
1059 END IF;
1060
1061 PV_ASSIGNMENT_PUB.CreateAssignment
1062 (p_api_version_number => 1.0
1063 ,p_init_msg_list => FND_API.G_FALSE
1064 ,p_commit => FND_API.G_FALSE
1065 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
1066 ,p_entity => 'OPPORTUNITY'
1067 ,p_lead_id => l_lead_id
1068 ,p_creating_username => l_user_name
1069 ,p_assignment_type => l_assignment_type
1070 ,p_bypass_cm_ok_flag => l_bypass_cm_ok_flag
1071 ,p_partner_id_tbl => l_partner_id_tbl
1072 ,p_rank_tbl => l_partner_rank_tbl
1073 ,p_partner_source_tbl => l_partner_source_tbl
1074 ,p_process_rule_id => NULL
1075 ,x_return_status => l_return_status
1076 ,x_msg_count => l_msg_count
1077 ,x_msg_data => l_msg_data);
1078
1079 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1080 l_failure_code := 'ROUTING_FAILED';
1081 RAISE FND_API.G_EXC_ERROR;
1082 END IF;
1083
1084 ELSE
1085
1086 IF (AS_DEBUG_LOW_ON) THEN
1087 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1088 'Adding partners to external salesteam');
1089 END IF;
1090
1091 FOR i in 1 .. l_partner_id_tbl.count LOOP
1092
1093 SELECT resource_id INTO l_partner_resource_id
1094 FROM jtf_rs_resource_extns
1095 WHERE sysdate between start_date_active and nvl(end_date_active,sysdate)
1096 and source_id = l_partner_id_tbl(i) and category='PARTNER' and rownum = 1;
1097
1098 l_sales_team_rec.lead_id := l_lead_id;
1099 l_sales_team_rec.customer_id := l_customer_id;
1100 l_sales_team_rec.freeze_flag := 'Y';
1101 l_sales_team_rec.partner_customer_id := l_partner_id_tbl(i);
1102 l_sales_team_rec.salesforce_id := l_partner_resource_id;
1103 l_sales_team_rec.address_id := l_address_id;
1104
1105 l_access_profile_rec := null;
1106
1107 as_access_pub.Create_SalesTeam
1108 (p_api_version_number => 2 -- API Version has been changed
1109 ,p_init_msg_list => FND_API.G_FALSE
1110 ,p_commit => FND_API.G_FALSE
1111 ,p_validation_level => FND_API.G_VALID_LEVEL_NONE
1112 ,p_access_profile_rec => l_access_profile_rec
1113 ,p_check_access_flag => 'N'
1114 ,p_admin_flag => 'N'
1115 ,p_admin_group_id => null
1116 ,p_identity_salesforce_id => l_identity_salesforce_id
1117 ,p_sales_team_rec => l_sales_team_rec
1118 ,x_return_status => l_return_status
1119 ,x_msg_count => l_msg_count
1120 ,x_msg_data => l_msg_data
1121 ,x_access_id => l_access_id);
1122
1123 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1124 l_failure_code := 'OTHER';
1125 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1126 END IF;
1127
1128 END LOOP;
1129
1130 END IF;
1131 END IF;
1132 result := 'COMPLETE';
1133 END IF; -- function mode check
1134
1135 EXCEPTION
1136 WHEN FND_API.G_EXC_ERROR THEN
1137
1138 fnd_msg_pub.Count_And_Get(
1139 p_encoded => FND_API.G_TRUE
1140 ,p_count => l_msg_count
1141 ,p_data => l_msg_data);
1142
1143 l_msg_data := FND_MSG_PUB.Get(
1144 p_msg_index => FND_MSG_PUB.Count_Msg,
1145 p_encoded => FND_API.G_FALSE);
1146
1147 ROLLBACK;
1148
1149 IF (FND_PROFILE.value('PV_AUTO_ROUTE_FOR_CAMPAIGN') = 'Y') THEN
1150 l_Sales_Team_Rec.salesforce_id := FND_PROFILE.Value('PV_BATCH_ASSIGN_USER_NAME');
1151
1152 l_lead_workflow_rec.failure_code := l_failure_code;
1153 l_lead_workflow_rec.failure_message := l_msg_data;
1154
1155 -- Create a row in PV Lead Workflow table.
1156 l_lead_workflow_rec.last_updated_by := fnd_global.user_id;
1157 l_lead_workflow_rec.created_by := fnd_global.user_id;
1158 l_lead_workflow_rec.lead_id := l_lead_id;
1159 l_lead_workflow_rec.entity := 'OPPORTUNITY';
1160 l_lead_workflow_rec.wf_item_type := pv_workflow_pub.g_wf_itemtype_pvasgnmt;
1161 l_lead_workflow_rec.wf_status := pv_assignment_pub.g_wf_status_closed;
1162 l_lead_workflow_rec.bypass_cm_ok_flag := NULL;
1163 l_lead_workflow_rec.latest_routing_flag := 'Y';
1164 l_lead_workflow_rec.routing_status := 'FAILED_AUTO_ASSIGN';
1165
1166 pv_assign_util_pvt.Create_lead_workflow_row
1167 (p_api_version_number => 1.0
1168 ,p_init_msg_list => FND_API.G_FALSE
1169 ,p_commit => FND_API.G_FALSE
1170 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
1171 ,p_workflow_rec => l_lead_workflow_rec
1172 ,x_ItemKey => l_itemKey
1173 ,x_return_status => l_return_status
1174 ,x_msg_count => l_msg_count
1175 ,x_msg_data => l_msg_data);
1176
1177 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1178 l_msg_data := FND_MSG_PUB.Get(
1179 p_msg_index => FND_MSG_PUB.Count_Msg,
1180 p_encoded => FND_API.G_FALSE);
1181 result := FND_API.G_RET_STS_ERROR;
1182 wf_core.token('STACK',l_msg_data);
1183 wf_core.raise('WFNTF_ERROR_STACK');
1184 wf_core.context(G_PKG_NAME, l_api_name, l_msg_data);
1185 RAISE;
1186 END IF;
1187
1188 IF (AS_DEBUG_LOW_ON) THEN
1189 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1190 'lwf rt status = ' || l_Return_Status);
1191 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'itemKey = ' || l_itemKey);
1192 END IF;
1193
1194 -- Create a sales team member for the opportunity
1195 l_Sales_Team_Rec.customer_id := l_customer_id;
1196 l_Sales_Team_Rec.address_id := l_address_id;
1197 l_Sales_Team_Rec.last_updated_by := FND_GLOBAL.USER_ID;
1198 l_Sales_Team_Rec.last_update_date := SYSDATE;
1199 l_Sales_Team_Rec.creation_date := SYSDATE;
1200 l_Sales_Team_Rec.created_by := FND_GLOBAL.USER_ID;
1201 l_Sales_Team_Rec.last_update_login := FND_GLOBAL.CONC_LOGIN_ID;
1202
1203 -- ----------------------------------------------------------------------
1204 -- Run Create_Salesteam only when an assignment manager is found in the
1205 -- profile PV_BATCH_ASSIGN_USER_NAME ().
1206 -- ----------------------------------------------------------------------
1207 IF (l_Sales_Team_Rec.salesforce_id IS NOT NULL) THEN
1208 l_Sales_Team_Rec.lead_id := l_lead_id;
1209 l_Sales_Team_Rec.team_leader_flag := 'Y';
1210 l_Sales_Team_Rec.reassign_flag := 'N';
1211 l_Sales_Team_Rec.freeze_flag := 'Y';
1212
1213 OPEN c_get_group_id(l_Sales_Team_Rec.salesforce_id);
1214 FETCH c_get_group_id INTO l_sales_team_rec.sales_group_id;
1215 CLOSE c_get_group_id;
1216
1217 IF l_sales_team_rec.sales_group_id = FND_API.G_MISS_NUM
1218 THEN
1219 l_sales_team_rec.sales_group_id := NULL;
1220 END IF;
1221
1222 OPEN get_person_id_csr(l_Sales_Team_Rec.salesforce_id);
1223 FETCH get_person_id_csr into l_Sales_Team_Rec.person_id;
1224
1225 IF (get_person_id_csr%NOTFOUND) THEN
1226 l_Sales_Team_Rec.person_id := NULL;
1227 END IF;
1228 CLOSE get_person_id_csr;
1229
1230 l_Sales_Team_Rec.created_by_TAP_flag := 'N';
1231 l_sales_team_rec.owner_flag := 'N';
1232
1233 IF (AS_DEBUG_LOW_ON) THEN
1234 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1235 'sf_id=' || l_Sales_Team_Rec.salesforce_id);
1236 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1237 'sg_id=' || l_Sales_Team_Rec.sales_group_id);
1238 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1239 'Calling Create_SalesTeam');
1240 END IF;
1241 AS_ACCESS_PUB.Create_SalesTeam (
1242 p_api_version_number => 2.0
1243 ,p_init_msg_list => FND_API.G_FALSE
1244 ,p_commit => FND_API.G_FALSE
1245 ,p_validation_level => FND_API.G_VALID_LEVEL_NONE
1246 ,p_access_profile_rec => l_access_profile_rec
1247 ,p_check_access_flag => 'N' -- P_Check_Access_flag
1248 ,p_admin_flag => 'N'
1249 ,p_admin_group_id => NULL
1250 ,p_identity_salesforce_id => l_identity_salesforce_id
1251 ,p_sales_team_rec => l_Sales_Team_Rec
1252 ,X_Return_Status => l_Return_Status
1253 ,X_Msg_Count => l_Msg_Count
1254 ,X_Msg_Data => l_Msg_Data2
1255 ,x_access_id => l_Access_Id
1256 );
1257
1258 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1259 l_msg_data := FND_MSG_PUB.Get(
1260 p_msg_index => FND_MSG_PUB.Count_Msg,
1261 p_encoded => FND_API.G_FALSE);
1262 result := FND_API.G_RET_STS_ERROR;
1263 wf_core.token('STACK',l_msg_data);
1264
1265 -- this is what makes the workflow result turn RED!
1266 wf_core.raise('WFNTF_ERROR_STACK');
1267 wf_core.context(G_PKG_NAME, l_api_name, l_msg_data);
1268 RAISE;
1269 END IF;
1270
1271 IF (AS_DEBUG_LOW_ON) THEN
1272 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1273 'acc rt status = ' || l_Return_Status);
1274 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1275 'Create_SalesTeam:l_access_id = ' || l_access_id);
1276 END IF;
1277 END IF;
1278 END IF;
1279
1280 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1281
1282 fnd_msg_pub.Count_And_Get(
1283 p_encoded => FND_API.G_TRUE
1284 ,p_count => l_msg_count
1285 ,p_data => l_msg_data);
1286
1287 l_msg_data := FND_MSG_PUB.Get(
1288 p_msg_index => FND_MSG_PUB.Count_Msg,
1289 p_encoded => FND_API.G_FALSE);
1290 wf_core.token('STACK',l_msg_data);
1291 wf_core.raise('WFNTF_ERROR_STACK');
1292
1293 wf_core.context(G_PKG_NAME, l_api_name,l_msg_data);
1294 raise;
1295
1296 WHEN OTHERS THEN
1297
1298 fnd_msg_pub.Count_And_Get(
1299 p_encoded => FND_API.G_TRUE
1300 ,p_count => l_msg_count
1301 ,p_data => l_msg_data);
1302
1303 wf_core.token('STACK', SQLERRM);
1304 wf_core.raise('WFNTF_ERROR_STACK');
1305 wf_core.context(G_PKG_NAME, l_api_name,l_msg_data);
1306 raise;
1307
1308 END Campaign_Routing;
1309
1310 END PV_BG_PARTNER_MATCHING_PUB;