[Home] [Help]
PACKAGE BODY: APPS.AS_LEAD_ROUTING_WF
Source
1 PACKAGE BODY AS_LEAD_ROUTING_WF AS
2 /* $Header: asxldrtb.pls 115.49 2003/09/11 17:31:08 solin ship $ */
3
4 -- Start of Comments
5 -- Package name : AS_LEAD_ROUTING_WF
6 -- Purpose : Sales Leads Workflow
7 -- NOTE :
8 -- History :
9 -- 11/07/2000 FFANG Created.
10 -- 05/23/2001 SOLIN Change for real time assignment and sales lead
11 -- sales team.
12 -- 07/10/2001 SOLIN Use UPDATE statement directly in UpdateSalesLead
13 -- 07/25/2001 SOLIN Enhancement bug 1732822.
14 -- Set status_code to profile AS_LEAD_ROUTING_STATUS
15 -- and accept_flag to 'N' when assign owner.
16 -- 07/31/2001 SOLIN Add customer user hook and GetOwner function.
17 -- 08/07/2001 SOLIN Add call to JTF_CALENDAR_PUB.
18 -- 09/06/2001 SOLIN Enhancement bug 1963262.
19 -- Owner can decline sales lead.
20 -- 12/10/2001 SOLIN Bug 2102901.
21 -- Add salesgroup_id for current user.
22 -- 02/04/2002 SOLIN Enhancement bug 2098158.
23 -- Add p_PRIMARY_CNT_PERSON_PARTY_ID,
24 -- p_PRIMARY_CONTACT_PHONE_ID when calling sales lead
25 -- update row.
26 -- 11/04/2002 SOLIN Enhancement Bug 2238553
27 -- When owner is changed, don't change status.
28 -- 02/14/2003 SOLIN Bug 2796513
29 -- If owner was on the sales team with freeze_flag='Y'
30 -- owner will still have freeze_flag='Y'
31 -- 02/20/2003 SOLIN Bug 2796503
32 -- Show message if no more available resource can be
33 -- lead owner.
34 -- 03/20/2003 SOLIN Bug 2831426
35 -- Add open_flag in as_accesses_all table.
36 -- 04/28/2003 SOLIN Bug 2926777
37 -- Close_reason should not be changed when
38 -- lead is reassigned.
39 -- 05/01/2003 SOLIN Bug 2928041
40 -- Add open_flag, object_creation_date, and
41 -- lead_rank_score in as_accesses_all table
42 -- 09/11/2003 SOLIN Change for Sales_Lead_Update_Row new columns
43 --
44 -- END of Comments
45
46
47 /*-------------------------------------------------------------------------*
48 |
49 | PRIVATE CONSTANTS
50 |
51 *-------------------------------------------------------------------------*/
52 G_PKG_NAME CONSTANT VARCHAR2(30):= 'AS_LEAD_ROUTING_WF';
53 G_FILE_NAME CONSTANT VARCHAR2(12) := 'asxldrtb.pls';
54
55 /*-------------------------------------------------------------------------*
56 |
57 | PRIVATE DATATYPES
58 |
59 *-------------------------------------------------------------------------*/
60
61
62 /*-------------------------------------------------------------------------*
63 |
64 | PRIVATE VARIABLES
65 |
66 *-------------------------------------------------------------------------*/
67 g_resource_id_tbl NUMBER_TABLE;
68 g_group_id_tbl NUMBER_TABLE;
69 g_person_id_tbl NUMBER_TABLE;
70
71 -- The follwing is the meaning of g_resource_flag_tbl:
72 -- 'D': This resource is the default resource from profile
73 -- AS_DEFAULT_RESOURCE_ID, "OS: Default Resource ID used for Sales
74 -- Lead Assignment".
75 -- 'L': This resource is the login user.
76 -- 'T': This resource is defined in territory.
77 g_resource_flag_tbl FLAG_TABLE;
78
79 -- This id is current user's group_id.
80 g_user_group_id NUMBER;
81
82 /*-------------------------------------------------------------------------*
83 |
84 | PUBLIC ROUTINES
85 |
86 *-------------------------------------------------------------------------*/
87 AS_DEBUG_HIGH_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);
88 AS_DEBUG_LOW_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
89 AS_DEBUG_MEDIUM_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM);
90 AS_DEBUG_ERROR_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_ERROR);
91
92 PROCEDURE StartProcess(
93 p_sales_lead_id IN NUMBER,
94 p_salesgroup_id IN NUMBER,
95 p_reject_reason_code IN VARCHAR2 := NULL,
96 x_return_status IN OUT NOCOPY VARCHAR2,
97 x_item_type OUT NOCOPY VARCHAR2,
98 x_item_key OUT NOCOPY VARCHAR2 )
99 IS
100 Item_Type VARCHAR2(8) := 'ASXSLASW' ;
101 Item_Key VARCHAR2(30);
102 l_status VARCHAR2(80);
103 l_result VARCHAR2(80);
104 l_sequence VARCHAR2(240);
105 l_seqnum NUMBER(38);
106 workflowprocess VARCHAR2(30) := 'SALES_LEAD_ASSIGNMENT';
107 l_profile_rs_id NUMBER;
108 BEGIN
109 -- Start Process :
110 -- If workflowprocess is passed, it will be run.
111 -- If workflowprocess is NOT passed, the selector FUNCTION
112 -- defined in the item type will determine which process to run.
113
114 SELECT TO_CHAR(AS_WORKFLOW_KEYS_S.nextval) INTO Item_Key
115 FROM dual;
116
117 g_user_group_id := p_salesgroup_id;
118
119 wf_engine.CreateProcess( ItemType => Item_Type,
120 ItemKey => Item_Key,
121 process => Workflowprocess);
122
123 -- Initialize workflow item attributes
124 --
125 wf_engine.SetItemAttrNumber(itemtype => Item_Type,
126 itemkey => Item_Key,
127 aname => 'SALES_LEAD_ID',
128 avalue => p_sales_lead_id);
129
130 wf_engine.AddItemAttr(itemtype => Item_Type,
131 itemkey => Item_Key,
132 aname => 'ORIG_RESOURCE_ID',
133 number_value => NULL);
134
135 wf_engine.AddItemAttr(itemtype => Item_Type,
136 itemkey => Item_Key,
137 aname => 'RESOURCE_ID',
138 number_value => 0);
139
140 wf_engine.AddItemAttr(itemtype => Item_Type,
141 itemkey => Item_Key,
142 aname => 'GROUP_ID',
143 number_value => 0);
144
145 wf_engine.AddItemAttr(itemtype => Item_Type,
146 itemkey => Item_Key,
147 aname => 'PERSON_ID',
148 number_value => 0);
149
150 wf_engine.AddItemAttr(itemtype => Item_Type,
151 Itemkey => Item_Key,
152 aname => 'BUSINESS_GROUP_ID',
153 number_value => 0);
154
155 wf_engine.AddItemAttr(itemtype => Item_Type,
156 Itemkey => Item_Key,
157 aname => 'REJECT_REASON_CODE',
158 text_value => p_reject_reason_code);
159
160 -- The following call was added, such that default attribute id
161 -- is added item list. This will be populated, once if the resource id
162 -- from profile as_default_resource_id is used.
163 -- Refer: Bug 1613424
164
165 wf_engine.AddItemAttr(itemtype => Item_Type,
166 Itemkey => Item_Key,
167 aname => 'DEFAULT_RESOURCE_ID',
168 number_value => 0);
169
170 wf_engine.StartProcess(itemtype => Item_Type,
171 itemkey => Item_Key );
172
173 wf_engine.ItemStatus(itemtype => Item_Type,
174 itemkey => Item_Key,
175 status => l_status,
176 result => l_result);
177
178 IF (AS_DEBUG_LOW_ON) THEN
179 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
180 'After ItemStatus:' || l_result);
181 END IF;
182
183 x_item_type := Item_Type;
184 x_item_key := Item_Key;
185 x_return_status := l_result ;
186
187 -- code change for bug 1613424 start
188 -- l_profile_rs_id is used as a temp variable.
189 -- if this has some value other than zero, it means that
190 -- GetAvailableResource either had used profile or login user's
191 -- resource id. In which case, the start process returns
192 -- 'W'arning to the calling program.
193
194 l_profile_rs_id := wf_engine.GetItemAttrNumber(
195 itemtype => Item_Type,
196 itemkey => Item_Key,
197 aname => 'DEFAULT_RESOURCE_ID' );
198
199 IF (l_profile_rs_id <> 0 ) AND ( l_result = 'S')
200 THEN
201 x_return_status := 'W';
202 END IF;
203 -- code change for bug 1613424 end
204
205 EXCEPTION
206 when others then
207 wf_core.context(Item_type, 'StartProcess', p_sales_lead_id,
208 Workflowprocess);
209 x_return_status := 'ERROR';
210 raise;
211 END StartProcess;
212
213 /*******************************/
214 -- Scope: private
215 -- setResource
216 -- Note: sets the resource
217 /*******************************/
218 PROCEDURE SetResource(
219 itemtype IN VARCHAR2,
220 itemkey IN VARCHAR2,
221 resource_id IN NUMBER,
222 group_id IN NUMBER,
223 person_id IN NUMBER) IS
224 BEGIN
225 wf_engine.SetItemAttrNumber (
226 itemtype => itemtype,
227 itemkey => itemkey,
228 aname => 'RESOURCE_ID',
229 avalue => resource_id);
230
231 wf_engine.SetItemAttrNumber (
232 itemtype => itemtype,
233 itemkey => itemkey,
234 aname => 'GROUP_ID',
235 avalue => group_id);
236
237 wf_engine.SetItemAttrNumber (
238 itemtype => itemtype,
239 itemkey => itemkey,
240 aname => 'PERSON_ID',
241 avalue => person_id);
242 END SetResource;
243
244 /*****************************************************/
245 -- Scope: private
246 -- GetAlternateResource
247 -- Note: There's no territory matching this sales lead.
248 -- Get default resource and login user.
249 /*****************************************************/
250 PROCEDURE GetAlternateResource IS
251 l_rs_id NUMBER := NULL;
252
253 CURSOR C_get_current_resource IS
254 SELECT res.resource_id
255 FROM jtf_rs_resource_extns res
256 WHERE res.category = 'EMPLOYEE'
257 AND res.user_id = fnd_global.user_id;
258
259 CURSOR c_get_group_id(c_resource_id NUMBER) IS
260 SELECT grp.group_id
261 FROM JTF_RS_GROUP_MEMBERS mem,
262 JTF_RS_ROLE_RELATIONS rrel,
263 JTF_RS_ROLES_B role,
264 JTF_RS_GROUP_USAGES u,
265 JTF_RS_GROUPS_B grp
266 WHERE mem.group_member_id = rrel.role_resource_id
267 AND rrel.role_resource_type = 'RS_GROUP_MEMBER'
268 AND rrel.role_id = role.role_id
269 AND role.role_type_code IN ('SALES','TELESALES','FIELDSALES','PRM')
270 AND mem.delete_flag <> 'Y'
271 AND rrel.delete_flag <> 'Y'
272 AND SYSDATE BETWEEN rrel.start_date_active AND
273 NVL(rrel.end_date_active,SYSDATE)
274 AND mem.resource_id = c_resource_id
275 AND mem.group_id = u.group_id
276 AND u.usage = 'SALES'
277 AND mem.group_id = grp.group_id
278 AND SYSDATE BETWEEN grp.start_date_active AND
279 NVL(grp.end_date_active,SYSDATE)
280 AND ROWNUM < 2;
281
285 CURSOR c_get_person_id(c_resource_id NUMBER) IS
282 -- A resource may not be in any group. Besides, jtf_rs_group_members
283 -- may not have person_id for all resources. Therefore, get person_id
284 -- in this cursor, instead of in the above cursor.
286 SELECT res.source_id
287 FROM jtf_rs_resource_extns res
288 WHERE res.resource_id = c_resource_id;
289
290 BEGIN
291 l_rs_id := fnd_profile.value('AS_DEFAULT_RESOURCE_ID');
292 IF l_rs_id IS NULL
293 THEN
294 IF (AS_DEBUG_LOW_ON) THEN
295 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
296 'profile not set');
297 END IF;
298 -- Profile is not set. hence going against the logged in user
299
300 OPEN C_get_current_resource;
301 FETCH C_get_current_resource INTO l_rs_id;
302 IF (C_get_current_resource%NOTFOUND)
303 THEN
304 IF (AS_DEBUG_LOW_ON) THEN
305 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
306 'No resource found for login user!');
307 END IF;
308 CLOSE C_get_current_resource;
309 RETURN;
310 END IF;
311 CLOSE C_get_current_resource;
312
313 IF l_rs_id IS NOT NULL
314 THEN
315 IF (AS_DEBUG_LOW_ON) THEN
316 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
317 'User''s resource id is:' || l_rs_id);
318 END IF;
319 IF g_user_group_id = fnd_api.g_miss_num
320 THEN
321 g_group_id_tbl(1) := NULL;
322 OPEN c_get_group_id (l_rs_id);
323 FETCH c_get_group_id INTO g_group_id_tbl(1);
324 CLOSE c_get_group_id;
325 ELSE
326 g_group_id_tbl(1) := g_user_group_id;
327 END IF;
328
329 OPEN c_get_person_id (l_rs_id);
330 FETCH c_get_person_id INTO g_person_id_tbl(1);
331 CLOSE c_get_person_id;
332 IF (AS_DEBUG_LOW_ON) THEN
333 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
334 'Users group id is:' || g_group_id_tbl(1));
335 END IF;
336 g_resource_id_tbl(1) := l_rs_id;
337 g_resource_flag_tbl(1) := 'L';
338 END IF;
339
340 ELSE -- profile resource id is not null
341 IF (AS_DEBUG_LOW_ON) THEN
342 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
343 'Profile resource id :'|| l_rs_id);
344 END IF;
345 g_group_id_tbl(1) := NULL;
346 OPEN c_get_group_id (l_rs_id);
347 FETCH c_get_group_id INTO g_group_id_tbl(1);
348 CLOSE c_get_group_id;
349 OPEN c_get_person_id (l_rs_id);
350 FETCH c_get_person_id INTO g_person_id_tbl(1);
351 CLOSE c_get_person_id;
352 IF (AS_DEBUG_LOW_ON) THEN
353 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
354 'Profile group id :' || g_group_id_tbl(1));
355 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
356 'Profile person id :' || g_person_id_tbl(1));
357 END IF;
358 g_resource_id_tbl(1) := l_rs_id;
359 g_resource_flag_tbl(1) := 'D';
360
361 OPEN C_get_current_resource;
362 FETCH C_get_current_resource INTO l_rs_id;
363 IF (C_get_current_resource%NOTFOUND)
364 THEN
365 CLOSE C_get_current_resource;
366 -- result := 'COMPLETE:ERROR';
367 IF (AS_DEBUG_LOW_ON) THEN
368 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
369 'No resource found!');
370 END IF;
371 RETURN;
372 END IF;
373 CLOSE C_get_current_resource;
374
375 IF l_rs_id IS NOT NULL AND
376 l_rs_id <> g_resource_id_tbl(1)
377 THEN
378 IF (AS_DEBUG_LOW_ON) THEN
379 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
380 'User''s resource id is:' || l_rs_id);
381 END IF;
382 IF g_user_group_id = fnd_api.g_miss_num
383 THEN
384 g_group_id_tbl(2) := NULL;
385 OPEN c_get_group_id (l_rs_id);
386 FETCH c_get_group_id INTO g_group_id_tbl(2);
387 CLOSE c_get_group_id;
388 ELSE
389 g_group_id_tbl(2) := g_user_group_id;
390 END IF;
391
392 OPEN c_get_person_id (l_rs_id);
393 FETCH c_get_person_id INTO g_person_id_tbl(2);
394 CLOSE c_get_person_id;
395 IF (AS_DEBUG_LOW_ON) THEN
396 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
397 'Users group id is:' || g_group_id_tbl(2));
398 END IF;
399 g_resource_id_tbl(2) := l_rs_id;
400 g_resource_flag_tbl(2) := 'L';
401 END IF;
402 END IF; -- resource id from profile check
403
404 END GetAlternateResource;
405
406 /*******************************/
407 -- API: GET RESOUCE
408 /*******************************/
409 PROCEDURE GetAvailableResource (
410 itemtype IN VARCHAR2,
411 itemkey IN VARCHAR2,
415 IS
412 actid IN NUMBER,
413 funcmode IN VARCHAR2,
414 result OUT NOCOPY VARCHAR2 )
416 l_sales_lead_id NUMBER;
417 l_resource_id_tbl NUMBER_TABLE;
418 l_group_id_tbl NUMBER_TABLE;
419 l_person_id_tbl NUMBER_TABLE;
420 l_resource_flag_tbl FLAG_TABLE;
421 l_check_calendar VARCHAR2(1);
422 l_index1 NUMBER; -- point to l_resource_id_tbl
423 l_index2 NUMBER; -- point to g_resource_id_tbl
424 l_last NUMBER; -- total number of rec in l_resource_id_tbl
425 l_return_status VARCHAR2(1);
426 l_msg_count NUMBER;
427 l_msg_data VARCHAR2(2000);
428 l_planned_start_date DATE;
429 l_planned_end_date DATE;
430 l_shift_construct_id NUMBER;
431 l_availability_type VARCHAR2(60);
432
433 -- SOLIN, enhancement for 11.5.9, 11/08/2002
434 -- Leads re-route must not be routed back to a resource that has previously
435 -- owned the lead before.
436 CURSOR c_get_lead_resource(c_sales_lead_id NUMBER) IS
437 SELECT ACC.SALESFORCE_ID, ACC.SALES_GROUP_ID, ACC.PERSON_ID, 'T'
438 FROM AS_ACCESSES_ALL ACC
439 WHERE ACC.SALES_LEAD_ID = c_sales_lead_id
440 AND ACC.CREATED_BY_TAP_FLAG = 'Y'
441 AND NOT EXISTS (
442 SELECT 1
443 FROM AS_SALES_LEADS_LOG LOG
444 WHERE LOG.SALES_LEAD_ID = c_sales_lead_id
445 AND LOG.ASSIGN_TO_SALESFORCE_ID = ACC.SALESFORCE_ID
446 AND (LOG.ASSIGN_SALES_GROUP_ID = ACC.SALES_GROUP_ID
447 OR LOG.ASSIGN_SALES_GROUP_ID IS NULL AND ACC.SALES_GROUP_ID IS NULL))
448 ORDER BY ACC.ACCESS_ID;
449 BEGIN
450 IF (AS_DEBUG_LOW_ON) THEN
451 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
452 'GetAvailableResource: Start');
453 END IF;
454
455 IF funcmode = 'RUN'
456 THEN
457 l_sales_lead_id := wf_engine.GetItemAttrNumber(
458 itemtype => itemtype,
459 itemkey => itemkey,
460 aname => 'SALES_LEAD_ID');
461
462 IF (AS_DEBUG_LOW_ON) THEN
463 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
464 'sl_id:' || l_sales_lead_id);
465 END IF;
466
467 -- Get sales team for the sales lead
468 OPEN c_get_lead_resource(l_sales_lead_id);
469 FETCH c_get_lead_resource BULK COLLECT INTO
470 l_resource_id_tbl, l_group_id_tbl, l_person_id_tbl,
471 l_resource_flag_tbl;
472 CLOSE c_get_lead_resource;
473
474 l_check_calendar :=
475 NVL(FND_PROFILE.Value('AS_SL_ASSIGN_CALENDAR_REQ'),'N');
476 IF (AS_DEBUG_LOW_ON) THEN
477 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
478 'l_resource_id_tbl.count=' || l_resource_id_tbl.count);
479 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
480 'Check calendar?' || l_check_calendar);
481 END IF;
482
483 g_resource_id_tbl.delete;
484 l_last := l_resource_id_tbl.last;
485 IF l_check_calendar = 'Y' AND l_last > 0
486 THEN
487 l_index1 := 1;
488 l_index2 := 0;
489 WHILE l_index1 <= l_last
490 LOOP
491 IF (AS_DEBUG_LOW_ON) THEN
492 AS_UTILITY_PVT.Debug_Message(
493 FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
494 'Check resource ' || l_resource_id_tbl(l_index1));
495 END IF;
496 -- Check the calendar for resource availability
497 -- Call Calendar API
498 JTF_CALENDAR_PUB.GET_AVAILABLE_SLOT(
499 P_API_VERSION => 1.0,
500 P_INIT_MSG_LIST => FND_API.G_FALSE,
501 P_RESOURCE_ID => l_resource_id_tbl(l_index1),
502 P_RESOURCE_TYPE => 'RS_EMPLOYEE',
503 P_START_DATE_TIME => SYSDATE-1,
504 P_END_DATE_TIME => SYSDATE+1,
505 P_DURATION => 8,
506 X_RETURN_STATUS => l_return_status,
507 X_MSG_COUNT => l_msg_count,
508 X_MSG_DATA => l_msg_data,
509 X_SLOT_START_DATE => l_planned_start_date,
510 X_SLOT_END_DATE => l_planned_end_date,
511 X_SHIFT_CONSTRUCT_ID => l_shift_construct_id,
512 X_AVAILABILITY_TYPE => l_availability_type);
513
514 IF l_return_status <> fnd_api.g_ret_sts_success
515 THEN
516 -- Unexpected Execution Error from call to Calendar
517 IF (AS_DEBUG_LOW_ON) THEN
518 AS_UTILITY_PVT.Debug_Message(
519 FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
520 'JTF Calendar failed');
521 END IF;
522 -- RAISE fnd_api.g_exc_unexpected_error;
523 END IF;
524
525 IF (AS_DEBUG_LOW_ON) THEN
526 AS_UTILITY_PVT.Debug_Message(
527 FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
528 'l_shift_construct_id=' || l_shift_construct_id);
529 END IF;
533 g_resource_id_tbl(l_index2) :=
530 IF l_shift_construct_id IS NOT NULL
531 THEN
532 l_index2 := l_index2 + 1;
534 l_resource_id_tbl(l_index1);
535 g_group_id_tbl(l_index2) := l_group_id_tbl(l_index1);
536 g_person_id_tbl(l_index2) := l_person_id_tbl(l_index1);
537 g_resource_flag_tbl(l_index2) :=
538 l_resource_flag_tbl(l_index1);
539 END IF;
540 l_index1 := l_index1 + 1;
541 END LOOP; -- l_index1 <= l_last
542 ELSE
543 g_resource_id_tbl := l_resource_id_tbl;
544 g_group_id_tbl := l_group_id_tbl;
545 g_person_id_tbl := l_person_id_tbl;
546 g_resource_flag_tbl := l_resource_flag_tbl;
547 END IF; -- l_check_calendar = 'Y' AND l_last > 0
548
549 IF (AS_DEBUG_LOW_ON) THEN
550 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
551 'g_resource_id_tbl.count=' || g_resource_id_tbl.count);
552 END IF;
553 result := 'COMPLETE:S';
554 END IF; -- function mode check
555 IF (AS_DEBUG_LOW_ON) THEN
556 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
557 'GetAvailableResource: End');
558 END IF;
559
560 EXCEPTION
561 WHEN OTHERS THEN
562 IF (AS_DEBUG_LOW_ON) THEN
563 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
564 'SQLCODE:' || SQLCODE || ' SQLERRM: ' || SQLERRM);
565 END IF;
566 wf_core.context(
567 itemtype,
568 'GETAVAILABLERESOURCE',
569 itemtype,
570 itemkey, to_char(actid),funcmode);
571 result := 'COMPLETE:ERROR';
572 RAISE;
573 END GetAvailableResource;
574 -------------------------------------------------------------
575
576
577 /*******************************/
578 -- API: GET OWNER
579 /*******************************/
580 PROCEDURE GetOwner(
581 itemtype IN VARCHAR2,
582 itemkey IN VARCHAR2,
583 actid IN NUMBER,
584 funcmode IN VARCHAR2,
585 result OUT NOCOPY VARCHAR2)
586 IS
587 l_rs_id NUMBER := null;
588
589 l_sales_lead_id NUMBER;
590 l_call_user_hook BOOLEAN;
591 l_sales_lead_rec AS_SALES_LEADS_PUB.SALES_LEAD_Rec_Type;
592 l_org_owner_id_tbl NUMBER_TABLE;
593 l_i NUMBER;
594 l_return_status VARCHAR2(15);
595 l_msg_count NUMBER;
596 l_msg_data VARCHAR2(2000);
597
598 l_resource_id NUMBER;
599 l_group_id NUMBER;
600 l_person_id NUMBER;
601 l_resource_avail_flag VARCHAR2(1);
602
603 CURSOR c_get_sales_lead(c_sales_lead_id NUMBER) IS
604 SELECT SALES_LEAD_ID, LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE,
605 CREATED_BY, LAST_UPDATE_LOGIN, REQUEST_ID, PROGRAM_APPLICATION_ID,
606 PROGRAM_ID, PROGRAM_UPDATE_DATE, LEAD_NUMBER, STATUS_CODE,
607 CUSTOMER_ID, ADDRESS_ID, SOURCE_PROMOTION_ID, INITIATING_CONTACT_ID,
608 ORIG_SYSTEM_REFERENCE, CONTACT_ROLE_CODE, CHANNEL_CODE,
609 BUDGET_AMOUNT, CURRENCY_CODE, DECISION_TIMEFRAME_CODE,
610 CLOSE_REASON, LEAD_RANK_ID, LEAD_RANK_CODE, PARENT_PROJECT,
611 DESCRIPTION, ATTRIBUTE_CATEGORY, ATTRIBUTE1, ATTRIBUTE2,
612 ATTRIBUTE3, ATTRIBUTE4, ATTRIBUTE5, ATTRIBUTE6, ATTRIBUTE7,
613 ATTRIBUTE8, ATTRIBUTE9, ATTRIBUTE10, ATTRIBUTE11, ATTRIBUTE12,
614 ATTRIBUTE13, ATTRIBUTE14, ATTRIBUTE15, BUDGET_STATUS_CODE,
615 ACCEPT_FLAG, VEHICLE_RESPONSE_CODE, TOTAL_SCORE, SCORECARD_ID,
616 KEEP_FLAG, URGENT_FLAG, IMPORT_FLAG, REJECT_REASON_CODE,
617 DELETED_FLAG, OFFER_ID, INCUMBENT_PARTNER_PARTY_ID,
618 INCUMBENT_PARTNER_RESOURCE_ID, PRM_EXEC_SPONSOR_FLAG,
619 PRM_PRJ_LEAD_IN_PLACE_FLAG, PRM_SALES_LEAD_TYPE,
620 PRM_IND_CLASSIFICATION_CODE, QUALIFIED_FLAG, ORIG_SYSTEM_CODE,
621 PRM_ASSIGNMENT_TYPE, AUTO_ASSIGNMENT_TYPE, PRIMARY_CONTACT_PARTY_ID,
622 PRIMARY_CNT_PERSON_PARTY_ID, PRIMARY_CONTACT_PHONE_ID,
623 REFERRED_BY, REFERRAL_TYPE, REFERRAL_STATUS, REF_DECLINE_REASON,
624 REF_COMM_LTR_STATUS, REF_ORDER_NUMBER, REF_ORDER_AMT,
625 REF_COMM_AMT, LEAD_DATE, SOURCE_SYSTEM, COUNTRY,
626 TOTAL_AMOUNT, EXPIRATION_DATE, LEAD_ENGINE_RUN_DATE, LEAD_RANK_IND,
627 CURRENT_REROUTES
628 FROM AS_SALES_LEADS
629 WHERE SALES_LEAD_ID = c_sales_lead_id;
630
631 CURSOR c_get_resource_avail(c_sales_lead_id NUMBER) IS
632 SELECT 'Y'
633 FROM AS_ACCESSES_ALL ACC
634 WHERE ACC.SALES_LEAD_ID = c_sales_lead_id
635 AND ACC.CREATED_BY_TAP_FLAG = 'Y';
636 BEGIN
637 IF (AS_DEBUG_LOW_ON) THEN
638 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
639 'GetOwner: Start');
640 END IF;
641
642 IF funcmode = 'RUN'
643 THEN
644 l_sales_lead_id := wf_engine.GetItemAttrNumber(
645 itemtype => itemtype,
646 itemkey => itemkey,
647 aname => 'SALES_LEAD_ID');
648
652 END IF;
649 IF (AS_DEBUG_LOW_ON) THEN
650 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
651 'sl_id:' || l_sales_lead_id);
653
654 IF g_resource_id_tbl.count = 0
655 THEN
656 GetAlternateResource;
657 END IF;
658
659 l_call_user_hook := JTF_USR_HKS.Ok_to_execute('AS_LEAD_ROUTING_WF',
660 'GetOwner','B','C');
661
662 -- USER HOOK standard : customer pre-processing section - mandatory
663 IF l_call_user_hook
664 THEN
665 IF (AS_DEBUG_LOW_ON) THEN
666 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
667 'Call user_hook is true');
668 END IF;
669 OPEN c_get_sales_lead(l_sales_lead_id);
670 FETCH c_get_sales_lead INTO
671 l_sales_lead_rec.SALES_LEAD_ID,
672 l_sales_lead_rec.LAST_UPDATE_DATE,
673 l_sales_lead_rec.LAST_UPDATED_BY,
674 l_sales_lead_rec.CREATION_DATE,
675 l_sales_lead_rec.CREATED_BY,
676 l_sales_lead_rec.LAST_UPDATE_LOGIN,
677 l_sales_lead_rec.REQUEST_ID,
678 l_sales_lead_rec.PROGRAM_APPLICATION_ID,
679 l_sales_lead_rec.PROGRAM_ID,
680 l_sales_lead_rec.PROGRAM_UPDATE_DATE,
681 l_sales_lead_rec.LEAD_NUMBER, l_sales_lead_rec.STATUS_CODE,
682 l_sales_lead_rec.CUSTOMER_ID, l_sales_lead_rec.ADDRESS_ID,
683 l_sales_lead_rec.SOURCE_PROMOTION_ID,
684 l_sales_lead_rec.INITIATING_CONTACT_ID,
685 l_sales_lead_rec.ORIG_SYSTEM_REFERENCE,
686 l_sales_lead_rec.CONTACT_ROLE_CODE,
687 l_sales_lead_rec.CHANNEL_CODE,
688 l_sales_lead_rec.BUDGET_AMOUNT, l_sales_lead_rec.CURRENCY_CODE,
689 l_sales_lead_rec.DECISION_TIMEFRAME_CODE,
690 l_sales_lead_rec.CLOSE_REASON, l_sales_lead_rec.LEAD_RANK_ID,
691 l_sales_lead_rec.LEAD_RANK_CODE,
692 l_sales_lead_rec.PARENT_PROJECT,
693 l_sales_lead_rec.DESCRIPTION,
694 l_sales_lead_rec.ATTRIBUTE_CATEGORY,
695 l_sales_lead_rec.ATTRIBUTE1, l_sales_lead_rec.ATTRIBUTE2,
696 l_sales_lead_rec.ATTRIBUTE3, l_sales_lead_rec.ATTRIBUTE4,
697 l_sales_lead_rec.ATTRIBUTE5, l_sales_lead_rec.ATTRIBUTE6,
698 l_sales_lead_rec.ATTRIBUTE7, l_sales_lead_rec.ATTRIBUTE8,
699 l_sales_lead_rec.ATTRIBUTE9, l_sales_lead_rec.ATTRIBUTE10,
700 l_sales_lead_rec.ATTRIBUTE11, l_sales_lead_rec.ATTRIBUTE12,
701 l_sales_lead_rec.ATTRIBUTE13, l_sales_lead_rec.ATTRIBUTE14,
702 l_sales_lead_rec.ATTRIBUTE15,
703 l_sales_lead_rec.BUDGET_STATUS_CODE,
704 l_sales_lead_rec.ACCEPT_FLAG,
705 l_sales_lead_rec.VEHICLE_RESPONSE_CODE,
706 l_sales_lead_rec.TOTAL_SCORE, l_sales_lead_rec.SCORECARD_ID,
707 l_sales_lead_rec.KEEP_FLAG, l_sales_lead_rec.URGENT_FLAG,
708 l_sales_lead_rec.IMPORT_FLAG,
709 l_sales_lead_rec.REJECT_REASON_CODE,
710 l_sales_lead_rec.DELETED_FLAG, l_sales_lead_rec.OFFER_ID,
711 l_sales_lead_rec.INCUMBENT_PARTNER_PARTY_ID,
712 l_sales_lead_rec.INCUMBENT_PARTNER_RESOURCE_ID,
713 l_sales_lead_rec.PRM_EXEC_SPONSOR_FLAG,
714 l_sales_lead_rec.PRM_PRJ_LEAD_IN_PLACE_FLAG,
715 l_sales_lead_rec.PRM_SALES_LEAD_TYPE,
716 l_sales_lead_rec.PRM_IND_CLASSIFICATION_CODE,
717 l_sales_lead_rec.QUALIFIED_FLAG,
718 l_sales_lead_rec.ORIG_SYSTEM_CODE,
719 l_sales_lead_rec.PRM_ASSIGNMENT_TYPE,
720 l_sales_lead_rec.AUTO_ASSIGNMENT_TYPE,
721 l_sales_lead_rec.PRIMARY_CONTACT_PARTY_ID,
722 l_sales_lead_rec.PRIMARY_CNT_PERSON_PARTY_ID,
723 l_sales_lead_rec.PRIMARY_CONTACT_PHONE_ID,
724 l_sales_lead_rec.REFERRED_BY,
725 l_sales_lead_rec.REFERRAL_TYPE,
726 l_sales_lead_rec.REFERRAL_STATUS,
727 l_sales_lead_rec.REF_DECLINE_REASON,
728 l_sales_lead_rec.REF_COMM_LTR_STATUS,
729 l_sales_lead_rec.REF_ORDER_NUMBER,
730 l_sales_lead_rec.REF_ORDER_AMT,
731 l_sales_lead_rec.REF_COMM_AMT,
732 l_sales_lead_rec.LEAD_DATE,
733 l_sales_lead_rec.SOURCE_SYSTEM,
734 l_sales_lead_rec.COUNTRY,
735 l_sales_lead_rec.TOTAL_AMOUNT,
736 l_sales_lead_rec.EXPIRATION_DATE,
737 l_sales_lead_rec.LEAD_ENGINE_RUN_DATE,
738 l_sales_lead_rec.LEAD_RANK_IND,
739 l_sales_lead_rec.CURRENT_REROUTES;
740 CLOSE c_get_sales_lead;
741 IF (AS_DEBUG_LOW_ON) THEN
742 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
743 'desc:' || l_sales_lead_rec.description);
744 END IF;
745
746 AS_LEAD_ROUTING_WF_CUHK.Get_Owner_Pre(
747 p_api_version_number => 2.0,
748 p_init_msg_list => FND_API.G_FALSE,
749 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
750 p_commit => FND_API.G_FALSE,
754 p_resource_flag_tbl => g_resource_flag_tbl,
751 p_resource_id_tbl => g_resource_id_tbl,
752 p_group_id_tbl => g_group_id_tbl,
753 p_person_id_tbl => g_person_id_tbl,
755 p_sales_lead_rec => l_sales_lead_rec,
756 x_resource_id => l_resource_id,
757 x_group_id => l_group_id,
758 x_person_id => l_person_id,
759 x_return_status => l_return_status,
760 x_msg_count => l_msg_count,
761 x_msg_data => l_msg_data);
762
763 IF l_return_status = fnd_api.g_ret_sts_success THEN
764 result := 'COMPLETE:S';
765 ELSE
766 result := 'COMPLETE:ERROR';
767 END IF;
768 END IF;
769
770 IF (l_call_user_hook AND l_resource_id IS NULL) OR
771 NOT l_call_user_hook
772 THEN
773 IF NOT l_call_user_hook
774 THEN
775 IF (AS_DEBUG_LOW_ON) THEN
776 AS_UTILITY_PVT.Debug_Message(
777 FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
778 'There''s no customer user hook');
779 END IF;
780 ELSE
781 IF (AS_DEBUG_LOW_ON) THEN
782 AS_UTILITY_PVT.Debug_Message(
783 FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
784 'User hook doesn''t return resource');
785 END IF;
786 END IF;
787
788 -- Set the first resource as owner
789 -- If owner decline this sales lead and s/he is the only
790 -- salesforce in the sales team, s/he will be stuck in it.
791 l_resource_id := g_resource_id_tbl(1);
792 l_group_id := g_group_id_tbl(1);
793 l_person_id := g_person_id_tbl(1);
794
795 IF g_resource_flag_tbl(1) = 'D'
796 THEN
797 -- Set default resource will have return status 'W' in
798 -- StartProcess
799 wf_engine.SetItemAttrNumber (
800 itemtype => itemtype,
801 itemkey => itemkey,
802 aname => 'DEFAULT_RESOURCE_ID',
803 avalue => l_resource_id);
804
805 OPEN c_get_resource_avail(l_sales_lead_id);
806 FETCH c_get_resource_avail INTO l_resource_avail_flag;
807 CLOSE c_get_resource_avail;
808 IF (AS_DEBUG_LOW_ON) THEN
809 AS_UTILITY_PVT.Debug_Message(
810 FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
811 'res avail?' || l_resource_avail_flag);
812 END IF;
813
814 IF l_resource_avail_flag = 'Y'
815 THEN
816 -- There are resources available, but they were previous
817 -- lead owners.
818 AS_UTILITY_PVT.Set_Message(
819 p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
820 p_msg_name => 'AS_WARN_DEF_RESOURCE_ID');
821 ELSE
822 AS_UTILITY_PVT.Set_Message(
823 p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
824 p_msg_name => 'AS_WARN_USING_DEF_RESOURCE_ID');
825 END IF;
826 ELSIF g_resource_flag_tbl(1) = 'L'
827 THEN
828 -- Set default resource will have return status 'W' in
829 -- StartProcess
830 wf_engine.SetItemAttrNumber (
831 itemtype => itemtype,
832 itemkey => itemkey,
833 aname => 'DEFAULT_RESOURCE_ID',
834 avalue => l_resource_id);
835
836 AS_UTILITY_PVT.Set_Message(
837 p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
838 p_msg_name => 'AS_WARN_USING_USER_RESOURCE_ID');
839 END IF;
840 END IF;
841 IF (AS_DEBUG_LOW_ON) THEN
842 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
843 'Set owner rs_id=' || l_resource_id);
844 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
845 ' group_id=' || l_group_id);
846 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
847 ' person_id=' || l_person_id);
848 END IF;
849 SetResource( itemtype, itemkey, l_resource_id, l_group_id, l_person_id);
850 result := 'COMPLETE:S';
851 END IF; -- funcmode = 'RUN'
852
853 IF (AS_DEBUG_LOW_ON) THEN
854 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
855 'GetOwner: End');
856 END IF;
857 EXCEPTION
858 WHEN OTHERS THEN
859 IF (AS_DEBUG_LOW_ON) THEN
860 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
861 'SQLCODE:' || SQLCODE || ' SQLERRM: ' || SQLERRM);
862 END IF;
863 wf_core.context(
864 itemtype,
865 'GETOWNER',
866 itemtype,
867 itemkey, to_char(actid),funcmode);
868 result := 'COMPLETE:ERROR';
869 RAISE;
870 END GetOwner;
871
872
876 actid IN NUMBER,
873 PROCEDURE UpdateSalesLeads (
874 itemtype IN VARCHAR2,
875 itemkey IN VARCHAR2,
877 funcmode IN VARCHAR2,
878 result OUT NOCOPY VARCHAR2 )
879 IS
880 l_customer_id NUMBER;
881 l_address_id NUMBER;
882 l_sales_lead_id NUMBER := NULL;
883 l_resource_id NUMBER;
884 l_group_id NUMBER;
885 l_person_id NUMBER;
886 l_access_exist_flag VARCHAR2(1);
887 l_status_code VARCHAR2(30);
888 l_sales_lead_log_id NUMBER;
889 l_reject_reason_code VARCHAR2(30);
890 l_lead_rank_id NUMBER;
891 l_qualified_flag VARCHAR2(1);
892 l_freeze_flag VARCHAR2(1);
893 l_open_status_flag VARCHAR2(1);
894 l_lead_rank_score NUMBER;
895 l_creation_date DATE;
896
897 CURSOR c_access_exist(c_sales_lead_id NUMBER, c_resource_id NUMBER,
898 c_group_id NUMBER) IS
899 SELECT freeze_flag
900 FROM as_accesses_all
901 WHERE sales_lead_id = c_sales_lead_id
902 AND salesforce_id = c_resource_id
903 AND ((sales_group_id = c_group_id) OR
904 (sales_group_id IS NULL AND c_group_id IS NULL));
905
906 CURSOR c_sales_lead(c_sales_lead_id NUMBER) IS
907 SELECT customer_id, address_id, reject_reason_code,
908 lead_rank_id, qualified_flag, NVL(accept_flag, 'N'), status_code
909 FROM as_sales_leads
910 WHERE Sales_lead_id = c_sales_lead_id;
911
912 -- Get whether status is open or not for the lead
913 -- Get lead_rank_score and lead creation_date
914 CURSOR c_get_open_status_flag(c_sales_lead_id NUMBER) IS
915 SELECT DECODE(sta.opp_open_status_flag, 'Y', 'Y', 'N', NULL),
916 rk.min_score, sl.creation_date
917 FROM as_statuses_b sta, as_sales_leads sl, as_sales_lead_ranks_b rk
918 WHERE sl.sales_lead_id = c_sales_lead_id
919 AND sl.status_code = sta.status_code
920 AND sl.lead_rank_id = rk.rank_id(+);
921 BEGIN
922 IF (AS_DEBUG_LOW_ON) THEN
923 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
924 'UpdateSalesLeads: Start');
925 END IF;
926 IF funcmode = 'RUN'
927 THEN
928 l_sales_lead_id := wf_engine.GetItemAttrNumber(
929 itemtype => itemtype,
930 itemkey => itemkey,
931 aname => 'SALES_LEAD_ID' );
932
933 l_resource_id := wf_engine.GetItemAttrNumber(
934 itemtype => itemtype,
935 itemkey => itemkey,
936 aname => 'RESOURCE_ID' );
937
938 l_group_id := wf_engine.GetItemAttrNumber(
939 itemtype => itemtype,
940 itemkey => itemkey,
941 aname => 'GROUP_ID' );
942
943 l_person_id := wf_engine.GetItemAttrNumber(
944 itemtype => itemtype,
945 itemkey => itemkey,
946 aname => 'PERSON_ID' );
947
948 IF (AS_DEBUG_LOW_ON) THEN
949 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
950 'res id in upd=' || l_Resource_Id);
951 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
952 'group id in upd='||l_group_id);
953 END IF;
954
955 OPEN c_sales_lead(l_sales_lead_id);
956 FETCH c_sales_lead INTO l_customer_id, l_address_id,
957 l_reject_reason_code, l_lead_rank_id,
958 l_qualified_flag, l_freeze_flag, l_status_code;
959 CLOSE c_sales_lead;
960
961 -- Call API to create log entry
962 AS_SALES_LEADS_LOG_PKG.Insert_Row(
963 px_log_id => l_sales_lead_log_id ,
964 p_sales_lead_id => l_sales_lead_id,
965 p_created_by => fnd_global.user_id,
966 p_creation_date => SYSDATE,
967 p_last_updated_by => fnd_global.user_id,
968 p_last_update_date => SYSDATE,
969 p_last_update_login => FND_GLOBAL.CONC_LOGIN_ID,
970 p_request_id => FND_GLOBAL.Conc_Request_Id,
971 p_program_application_id => FND_GLOBAL.Prog_Appl_Id,
972 p_program_id => FND_GLOBAL.Conc_Program_Id,
973 p_program_update_date => SYSDATE,
974 p_status_code => l_status_code,
975 p_assign_to_person_id => l_person_id,
976 p_assign_to_salesforce_id => l_resource_id,
977 p_reject_reason_code => l_reject_reason_code,
978 p_assign_sales_group_id => l_group_id,
979 p_lead_rank_id => l_lead_rank_id,
980 p_qualified_flag => l_qualified_flag,
981 p_category => NULL);
982
983 -- Call table handler directly, not calling Update_Sales_Lead,
984 -- in case current user doesn't have update privilege.
985 AS_SALES_LEADS_PKG.Sales_Lead_Update_Row(
986 p_SALES_LEAD_ID => l_SALES_LEAD_ID,
987 p_LAST_UPDATE_DATE => SYSDATE,
988 p_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
992 p_REQUEST_ID => FND_GLOBAL.Conc_Request_Id,
989 p_CREATION_DATE => FND_API.G_MISS_DATE,
990 p_CREATED_BY => FND_API.G_MISS_NUM,
991 p_LAST_UPDATE_LOGIN => FND_API.G_MISS_NUM,
993 p_PROGRAM_APPLICATION_ID => FND_GLOBAL.Prog_Appl_Id,
994 p_PROGRAM_ID => FND_GLOBAL.Conc_Program_Id,
995 p_PROGRAM_UPDATE_DATE => SYSDATE,
996 p_LEAD_NUMBER => FND_API.G_MISS_CHAR,
997 p_STATUS_CODE => FND_API.G_MISS_CHAR,
998 p_CUSTOMER_ID => l_CUSTOMER_ID,
999 p_ADDRESS_ID => l_ADDRESS_ID,
1000 p_SOURCE_PROMOTION_ID => FND_API.G_MISS_NUM,
1001 p_INITIATING_CONTACT_ID => FND_API.G_MISS_NUM,
1002 p_ORIG_SYSTEM_REFERENCE => FND_API.G_MISS_CHAR,
1003 p_CONTACT_ROLE_CODE => FND_API.G_MISS_CHAR,
1004 p_CHANNEL_CODE => FND_API.G_MISS_CHAR,
1005 p_BUDGET_AMOUNT => FND_API.G_MISS_NUM,
1006 p_CURRENCY_CODE => FND_API.G_MISS_CHAR,
1007 p_DECISION_TIMEFRAME_CODE => FND_API.G_MISS_CHAR,
1008 p_CLOSE_REASON => FND_API.G_MISS_CHAR,
1009 p_LEAD_RANK_ID => FND_API.G_MISS_NUM,
1010 p_LEAD_RANK_CODE => FND_API.G_MISS_CHAR,
1011 p_PARENT_PROJECT => FND_API.G_MISS_CHAR,
1012 p_DESCRIPTION => FND_API.G_MISS_CHAR,
1013 p_ATTRIBUTE_CATEGORY => FND_API.G_MISS_CHAR,
1014 p_ATTRIBUTE1 => FND_API.G_MISS_CHAR,
1015 p_ATTRIBUTE2 => FND_API.G_MISS_CHAR,
1016 p_ATTRIBUTE3 => FND_API.G_MISS_CHAR,
1017 p_ATTRIBUTE4 => FND_API.G_MISS_CHAR,
1018 p_ATTRIBUTE5 => FND_API.G_MISS_CHAR,
1019 p_ATTRIBUTE6 => FND_API.G_MISS_CHAR,
1020 p_ATTRIBUTE7 => FND_API.G_MISS_CHAR,
1021 p_ATTRIBUTE8 => FND_API.G_MISS_CHAR,
1022 p_ATTRIBUTE9 => FND_API.G_MISS_CHAR,
1023 p_ATTRIBUTE10 => FND_API.G_MISS_CHAR,
1024 p_ATTRIBUTE11 => FND_API.G_MISS_CHAR,
1025 p_ATTRIBUTE12 => FND_API.G_MISS_CHAR,
1026 p_ATTRIBUTE13 => FND_API.G_MISS_CHAR,
1027 p_ATTRIBUTE14 => FND_API.G_MISS_CHAR,
1028 p_ATTRIBUTE15 => FND_API.G_MISS_CHAR,
1029 p_ASSIGN_TO_PERSON_ID => l_person_id,
1030 p_ASSIGN_TO_SALESFORCE_ID => l_resource_id,
1031 p_ASSIGN_SALES_GROUP_ID => l_group_id,
1032 p_ASSIGN_DATE => SYSDATE,
1033 p_BUDGET_STATUS_CODE => FND_API.G_MISS_CHAR,
1034 p_ACCEPT_FLAG => 'N',
1035 p_VEHICLE_RESPONSE_CODE => FND_API.G_MISS_CHAR,
1036 p_TOTAL_SCORE => FND_API.G_MISS_NUM,
1037 p_SCORECARD_ID => FND_API.G_MISS_NUM,
1038 p_KEEP_FLAG => FND_API.G_MISS_CHAR,
1039 p_URGENT_FLAG => FND_API.G_MISS_CHAR,
1040 p_IMPORT_FLAG => FND_API.G_MISS_CHAR,
1041 p_REJECT_REASON_CODE => NULL,
1042 p_DELETED_FLAG => FND_API.G_MISS_CHAR,
1043 p_OFFER_ID => FND_API.G_MISS_NUM,
1044 p_QUALIFIED_FLAG => l_qualified_flag,
1045 p_ORIG_SYSTEM_CODE => FND_API.G_MISS_CHAR,
1046 p_INC_PARTNER_PARTY_ID => FND_API.G_MISS_NUM,
1047 p_INC_PARTNER_RESOURCE_ID => FND_API.G_MISS_NUM,
1048 p_PRM_EXEC_SPONSOR_FLAG => FND_API.G_MISS_CHAR,
1049 p_PRM_PRJ_LEAD_IN_PLACE_FLAG => FND_API.G_MISS_CHAR,
1050 p_PRM_SALES_LEAD_TYPE => FND_API.G_MISS_CHAR,
1051 p_PRM_IND_CLASSIFICATION_CODE => FND_API.G_MISS_CHAR,
1052 p_PRM_ASSIGNMENT_TYPE => FND_API.G_MISS_CHAR,
1053 p_AUTO_ASSIGNMENT_TYPE => FND_API.G_MISS_CHAR,
1054 p_PRIMARY_CONTACT_PARTY_ID => FND_API.G_MISS_NUM,
1055 p_PRIMARY_CNT_PERSON_PARTY_ID => FND_API.G_MISS_NUM,
1056 p_PRIMARY_CONTACT_PHONE_ID => FND_API.G_MISS_NUM,
1057 p_REFERRED_BY => FND_API.G_MISS_NUM,
1058 p_REFERRAL_TYPE => FND_API.G_MISS_CHAR,
1059 p_REFERRAL_STATUS => FND_API.G_MISS_CHAR,
1060 p_REF_DECLINE_REASON => FND_API.G_MISS_CHAR,
1061 p_REF_COMM_LTR_STATUS => FND_API.G_MISS_CHAR,
1062 p_REF_ORDER_NUMBER => FND_API.G_MISS_NUM,
1063 p_REF_ORDER_AMT => FND_API.G_MISS_NUM,
1064 p_REF_COMM_AMT => FND_API.G_MISS_NUM,
1065 -- bug No.2341515, 2368075
1066 p_LEAD_DATE => FND_API.G_MISS_DATE,
1067 p_SOURCE_SYSTEM => FND_API.G_MISS_CHAR,
1068 p_COUNTRY => FND_API.G_MISS_CHAR,
1069 p_TOTAL_AMOUNT => FND_API.G_MISS_NUM,
1070 p_EXPIRATION_DATE => FND_API.G_MISS_DATE,
1071 p_LEAD_RANK_IND => FND_API.G_MISS_CHAR,
1072 p_LEAD_ENGINE_RUN_DATE => FND_API.G_MISS_DATE,
1073 p_CURRENT_REROUTES => FND_API.G_MISS_NUM,
1074 p_STATUS_OPEN_FLAG => FND_API.G_MISS_CHAR,
1075 p_LEAD_RANK_SCORE => FND_API.G_MISS_NUM,
1076 -- 11.5.10 new columns
1077 p_MARKETING_SCORE => FND_API.G_MISS_NUM,
1078 p_INTERACTION_SCORE => FND_API.G_MISS_NUM,
1079 p_SOURCE_PRIMARY_REFERENCE => FND_API.G_MISS_CHAR,
1080 p_SOURCE_SECONDARY_REFERENCE => FND_API.G_MISS_CHAR,
1081 p_SALES_METHODOLOGY_ID => FND_API.G_MISS_NUM,
1082 p_SALES_STAGE_ID => FND_API.G_MISS_NUM);
1083
1084
1085 OPEN c_access_exist(l_sales_lead_id, l_resource_id, l_group_id);
1086 FETCH c_access_exist INTO l_access_exist_flag;
1087 CLOSE c_access_exist;
1088
1089 -- Clear owner in as_accesses_all.
1090 -- There may be more than one owner_flag='Y' for the lead in
1094 UPDATE as_accesses_all
1091 -- as_accesses_all:
1092 -- 1. When owner rejects the lead
1093 -- 2. When monitoring engine times out
1095 SET owner_flag = 'N'
1096 WHERE sales_lead_id = l_sales_lead_id;
1097
1098 IF l_access_exist_flag IS NOT NULL
1099 THEN
1100 -- If the owner was frozen in the sales team, he is still frozen in
1101 -- the sales team. No matter whether he accept the lead or not.
1102 IF l_access_exist_flag = 'Y'
1103 THEN
1104 l_freeze_flag := 'Y';
1105 END IF;
1106 UPDATE as_accesses_all
1107 SET team_leader_flag = 'Y',
1108 owner_flag = 'Y',
1109 freeze_flag = l_freeze_flag,
1110 created_by_tap_flag = 'Y'
1111 WHERE sales_lead_id = l_sales_lead_id
1112 AND salesforce_id = l_resource_id
1113 AND ((sales_group_id = l_group_id) OR
1114 (sales_group_id IS NULL AND l_group_id IS NULL));
1115 ELSE
1116 OPEN c_get_open_status_flag(l_sales_lead_id);
1117 FETCH c_get_open_status_flag INTO l_open_status_flag,
1118 l_lead_rank_score, l_creation_date;
1119 CLOSE c_get_open_status_flag;
1120
1121 INSERT INTO as_accesses_all
1122 (ACCESS_ID, LAST_UPDATE_DATE, LAST_UPDATED_BY
1123 ,CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN
1124 ,ACCESS_TYPE, FREEZE_FLAG, REASSIGN_FLAG, TEAM_LEADER_FLAG
1125 ,OWNER_FLAG, CREATED_BY_TAP_FLAG
1126 ,CUSTOMER_ID, ADDRESS_ID, SALES_LEAD_ID, SALESFORCE_ID
1127 ,PERSON_ID, SALES_GROUP_ID, OPEN_FLAG, LEAD_RANK_SCORE
1128 ,OBJECT_CREATION_DATE)
1129 SELECT as_accesses_s.nextval, SYSDATE, FND_GLOBAL.USER_ID,
1130 SYSDATE, FND_GLOBAL.USER_ID, FND_GLOBAL.CONC_LOGIN_ID, 'X',
1131 l_freeze_flag ,'N', 'Y', 'Y', 'Y',
1132 l_customer_id, l_address_id, l_sales_lead_id,
1133 l_resource_id, l_person_id, l_group_id, l_open_status_flag,
1134 l_lead_rank_score, l_creation_date
1135 FROM SYS.DUAL;
1136 END IF; -- l_access_exist_flag IS NOT NULL
1137
1138 result := 'COMPLETE:S';
1139 END IF; -- funcmode = 'RUN'
1140 IF (AS_DEBUG_LOW_ON) THEN
1141 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1142 'UpdateSalesLeads: End');
1143 END IF;
1144
1145 EXCEPTION
1146 WHEN OTHERS THEN
1147 wf_core.context(itemtype, 'UpdateSalesLeads', itemtype, itemkey,
1148 to_char(actid), funcmode);
1149 result := 'COMPLETE:ERROR';
1150 RAISE;
1151 END UpdateSalesLeads;
1152
1153 -----------------------------------
1154 -- rest of the code in this file from here on till the end - is not used
1155 -- do not spend any time on it.
1156 -----------------------------------
1157
1158 -------------------------------------------------------------
1159
1160
1161 PROCEDURE GetAvailableResources (
1162 itemtype in VARCHAR2,
1163 itemkey in VARCHAR2,
1164 actid in NUMBER,
1165 funcmode in VARCHAR2,
1166 result OUT NOCOPY VARCHAR2 )
1167 IS
1168
1169 l_available_resources available_resource_table;
1170
1171 l_sales_lead_id NUMBER;
1172 l_return_status VARCHAR2(15);
1173 l_msg_count NUMBER;
1174 l_msg_data VARCHAR2(2000);
1175 l_Assign_Id_tbl AS_SALES_LEADS_PUB.Assign_Id_Tbl_Type;
1176
1177 BEGIN
1178
1179 IF funcmode = 'RUN' THEN
1180
1181 l_sales_lead_id := wf_engine.GetItemAttrNumber(
1182 itemtype => itemtype,
1183 itemkey => itemkey,
1184 aname => 'SALES_LEAD_ID' );
1185
1186 -- AS_SALES_LEADS_PVT.Assign_Sales_Lead (
1187 AS_SALES_LEAD_ASSIGN_PVT.Assign_Sales_Lead (
1188 P_Api_Version_Number => 2.0,
1189 P_Init_Msg_List => FND_API.G_FALSE,
1190 p_commit => FND_API.G_FALSE,
1191 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1192 P_Check_Access_Flag => FND_API.G_MISS_CHAR,
1193 P_Admin_Flag => FND_API.G_MISS_CHAR,
1194 P_Admin_Group_Id => FND_API.G_MISS_NUM,
1195 P_identity_salesforce_id => FND_API.G_MISS_NUM,
1196 P_Sales_Lead_Profile_Tbl => AS_UTILITY_PUB.G_MISS_PROFILE_TBL,
1197 P_resource_type => NULL,
1198 P_role => NULL,
1199 P_no_of_resources => 999,
1200 P_auto_select_flag => NULL,
1201 P_effort_duration => 8,
1202 P_effort_uom => 'HR',
1203 P_start_date => sysdate-1,
1204 P_end_date => sysdate+1,
1205 P_territory_flag => 'Y',
1206 P_calendar_flag => 'Y',
1207 P_Sales_Lead_Id => l_sales_lead_id,
1208 X_Return_Status => l_return_status,
1209 X_Msg_Count => l_msg_count,
1210 X_Msg_Data => l_msg_data,
1214 IF l_Assign_Id_tbl.count = 0 THEN
1211 X_Assign_Id_Tbl => l_Assign_Id_tbl
1212 );
1213
1215 result := 'COMPLETE:NORES';
1216 IF (AS_DEBUG_ERROR_ON) THEN
1217 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_ERROR,
1218 'JTF AM - No resource found!');
1219 END IF;
1220 return;
1221 ELSE
1222 IF l_Assign_Id_tbl.COUNT > 0 THEN
1223 FOR i in l_Assign_Id_tbl.first..l_Assign_Id_tbl.last LOOP
1224 l_available_resources(i).Resource_Id
1225 :=l_Assign_Id_tbl(i).Resource_Id;
1226 l_available_resources(i).Group_id
1227 :=l_Assign_Id_tbl(i).Sales_Group_Id;
1228 END LOOP;
1229 END IF;
1230
1231 g_available_resource_table := l_available_resources;
1232 result := 'COMPLETE:S';
1233 END IF;
1234 END IF;
1235
1236 EXCEPTION
1237 when others then
1238 wf_core.context(itemtype, 'GetAvailableResources', itemtype,
1239 itemkey, to_char(actid),funcmode);
1240 result := 'COMPLETE:ERROR';
1241 raise;
1242 END GetAvailableResources;
1243
1244
1245 PROCEDURE GetResourceWorkload (
1246 itemtype in VARCHAR2,
1247 itemkey in VARCHAR2,
1248 actid in NUMBER,
1249 funcmode in VARCHAR2,
1250 result OUT NOCOPY VARCHAR2 )
1251 IS
1252 CURSOR c_workload (resource_id_in number) IS
1253 SELECT count(sales_lead_id)
1254 FROM as_sales_leads
1255 WHERE assign_to_salesforce_id = resource_id_in;
1256
1257 l_resource_cnt NUMBER := 0;
1258 l_avail_resources available_resource_table;
1259 l_resource_rownum NUMBER;
1260
1261 BEGIN
1262
1263 IF funcmode = 'RUN' THEN
1264 l_avail_resources := g_available_resource_table;
1265
1266 -- solin
1267 -- change to use while loop because l_avail_resources.first may be
1268 -- 0 or 1
1269 l_resource_rownum := l_avail_resources.first;
1270 WHILE l_resource_rownum <= l_avail_resources.last
1271 LOOP
1272 OPEN c_workload(l_avail_resources(l_resource_rownum).resource_id);
1273 FETCH c_workload INTO l_avail_resources(l_resource_rownum).workload;
1274 CLOSE c_workload;
1275 l_resource_rownum := l_resource_rownum + 1;
1276 END LOOP;
1277
1278 g_available_resource_table := l_avail_resources;
1279
1280 result := 'COMPLETE:S';
1281 END IF;
1282
1283 EXCEPTION
1284 when others then
1285 --
1286 wf_core.context(itemtype, 'GetResourceWorkload', itemtype, itemkey,
1287 to_char(actid),funcmode);
1288 result := 'COMPLETE:ERROR';
1289 raise;
1290 END GetResourceWorkload;
1291
1292
1293 PROCEDURE BalanceWorkload (
1294 itemtype in VARCHAR2,
1295 itemkey in VARCHAR2,
1296 actid in NUMBER,
1297 funcmode in VARCHAR2,
1298 result OUT NOCOPY VARCHAR2 )
1299 IS
1300 l_rowcnt INTEGER;
1301 l_available_resources available_resource_table;
1302 l_leastwork_resource resource_record_type;
1303 l_selected_id NUMBER;
1304 l_selected_group_id NUMBER;
1305 l_logcount NUMBER;
1306 l_never_assigned NUMBER := 0;
1307 l_sales_lead_id NUMBER;
1308
1309 CURSOR c_checklog (resource_id_in number, sl_id_in number) IS
1310 SELECT count(log_id)
1311 FROM as_sales_leads_log
1312 WHERE assign_to_salesforce_id = resource_id_in
1313 and sales_lead_id = sl_id_in;
1314
1315 BEGIN
1316 l_sales_lead_id := wf_engine.GetItemAttrNumber(
1317 itemtype => itemtype,
1318 itemkey => itemkey,
1319 aname => 'SALES_LEAD_ID' );
1320
1321 IF funcmode = 'RUN' THEN
1322 l_available_resources := g_available_resource_table;
1323
1324 IF l_available_resources.count > 0
1325 THEN
1326 -- solin
1327 -- change to use while loop because l_available_resources.first may
1328 -- be 0 or 1
1329 l_rowcnt := l_available_resources.first;
1330 l_leastwork_resource := l_available_resources(l_rowcnt);
1331 WHILE l_rowcnt <= l_available_resources.last
1332 LOOP
1333 IF l_available_resources(l_rowcnt).workload <=
1334 l_leastwork_resource.workload
1335 THEN
1336 -- Has it been worked on by that resource before ?
1337
1338 -- 012201 FFANG, sales leads can be assigned back to the
1339 -- sales reps who have worked on it.
1340 /* ***
1341 OPEN c_checklog(l_available_resources(l_rowcnt).resource_id,
1342 l_sales_lead_id);
1343 FETCH c_checklog INTO l_logcount;
1344 CLOSE c_checklog;
1345 IF l_logcount = 0 THEN
1346 *** */
1347 l_leastwork_resource := l_available_resources(l_rowcnt);
1351 *** */
1348 l_never_assigned := l_never_assigned + 1 ;
1349 /* ***
1350 END IF;
1352 END IF;
1353 l_rowcnt := l_rowcnt + 1;
1354 END LOOP;
1355 END IF;
1356
1357 IF l_never_assigned > 0 THEN
1358 l_selected_id := l_leastwork_resource.resource_id;
1359 l_selected_group_id := l_leastwork_resource.group_id;
1360
1361 wf_engine.SetItemAttrNumber(itemtype => itemtype,
1362 itemkey => itemkey,
1363 aname => 'RESOURCE_ID',
1364 avalue => l_selected_id);
1365
1366 wf_engine.SetItemAttrNumber(itemtype => itemtype,
1367 itemkey => itemkey,
1368 aname => 'BUSINESS_GROUP_ID',
1369 avalue => l_selected_group_id);
1370 result := 'COMPLETE:S';
1371 ELSE
1372 -- Escalate it to the manager of the person with least workload
1373 wf_engine.SetItemAttrNumber(itemtype => itemtype,
1374 itemkey => itemkey,
1375 aname => 'BUSINESS_GROUP_ID',
1376 avalue => l_leastwork_resource.group_id);
1377 result := 'COMPLETE:ESCALATE';
1378 END IF;
1379 END IF;
1380
1381 EXCEPTION
1382 when others then
1383 wf_core.context(Itemtype, 'BalanceWorkload', itemtype, itemkey,
1384 to_char(actid),funcmode);
1385 result := 'COMPLETE:ERROR';
1386 raise;
1387 END BalanceWorkload ;
1388
1389
1390 PROCEDURE EscalatetoManager (
1391 itemtype in VARCHAR2,
1392 itemkey in VARCHAR2,
1393 actid in NUMBER,
1394 funcmode in VARCHAR2,
1395 result OUT NOCOPY VARCHAR2 )
1396 IS
1397 CURSOR c_manager ( group_id_in number) IS
1398 SELECT manager_id
1399 FROM jtf_rs_group_dtls_vl
1400 WHERE group_id = group_id_in;
1401
1402 l_sales_lead_rec AS_SALES_LEADS_PUB.sales_lead_rec_type;
1403 l_sales_lead_profile_tbl AS_UTILITY_PUB.Profile_Tbl_Type
1404 := AS_UTILITY_PUB.G_MISS_PROFILE_TBL;
1405
1406 l_api_version_number NUMBER := 2.0;
1407 l_cnt NUMBER := 0;
1408 l_sales_lead_id NUMBER;
1409 l_resource_id NUMBER;
1410 l_status_code VARCHAR2(30);
1411 l_last_update_date DATE := SYSDATE;
1412 l_return_status VARCHAR2(15);
1413 l_msg_count NUMBER;
1414 l_msg_data VARCHAR2(2000);
1415 l_msg_index_out NUMBER;
1416 l_group_id NUMBER;
1417 l_manager_id NUMBER;
1418 l_origowner_id NUMBER;
1419
1420 CURSOR c_sales_lead(x_sales_lead_id NUMBER) IS
1421 SELECT last_update_date,
1422 customer_id,
1423 address_id,
1424 assign_sales_group_id,
1425 sales_lead_id
1426 FROM as_sales_leads
1427 WHERE sales_lead_id = x_sales_lead_id;
1428
1429 BEGIN
1430
1431 IF funcmode = 'RUN' THEN
1432 l_group_id := wf_engine.GetItemAttrNumber(
1433 itemtype => itemtype,
1434 itemkey => itemkey,
1435 aname => 'BUSINESS_GROUP_ID' );
1436
1437
1438 l_sales_lead_id := wf_engine.GetItemAttrNumber(
1439 itemtype => itemtype,
1440 itemkey => itemkey,
1441 aname => 'SALES_LEAD_ID' );
1442
1443 -- get the group manager resource id
1444 OPEN c_manager(l_group_id);
1445 FETCH c_manager INTO l_manager_id;
1446 IF c_manager%notfound THEN
1447 -- ffang 110200, forgot to close cursor?
1448 CLOSE c_manager;
1449 -- end ffang 110200
1450
1451 -- assign it to the original owner
1452
1453 l_origowner_id := wf_engine.GetItemAttrNumber(
1454 itemtype => itemtype,
1455 itemkey => itemkey,
1456 aname => 'ORIG_RESOURCE_ID' );
1457
1458 wf_engine.SetItemAttrNumber ( itemtype => itemtype,
1459 itemkey => itemkey,
1460 aname => 'RESOURCE_ID',
1461 avalue => l_origowner_id);
1462 IF l_origowner_id is NULL THEN
1463 l_group_id := NULL;
1464 wf_engine.SetItemAttrNumber ( itemtype => itemtype,
1465 itemkey => itemkey,
1466 aname => 'BUSINESS_GROUP_ID',
1467 avalue => l_group_id);
1468 END IF;
1469
1470 result := 'COMPLETE:NOMGR';
1471 ELSE
1472 -- escalate to the group manager
1473 OPEN c_sales_lead(l_sales_lead_id);
1474 FETCH c_sales_lead INTO l_sales_lead_rec.last_update_date,
1475 l_sales_lead_rec.customer_id,
1476 l_sales_lead_rec.address_id,
1477 l_sales_lead_rec.assign_sales_group_id,
1478 l_sales_lead_rec.sales_lead_id;
1479 CLOSE c_sales_lead;
1480
1481 -- Now reassign escalated lead to the manager
1482 l_sales_lead_rec.assign_to_salesforce_id := l_manager_id;
1483
1484 AS_SALES_LEADS_PUB.update_sales_lead(
1485 p_api_version_number => l_api_version_number
1486 ,p_init_msg_list => fnd_api.g_FALSE
1487 ,p_commit => fnd_api.g_false
1488 ,p_validation_level => 0 -- fnd_api.g_valid_level_full
1489 ,p_check_access_flag => 'N' -- fnd_api.g_miss_char
1490 ,p_admin_flag => fnd_api.g_miss_char
1491 ,p_admin_group_id => fnd_api.g_miss_num
1492 ,p_identity_salesforce_id => fnd_api.g_miss_num
1493 ,p_sales_lead_profile_tbl => l_sales_lead_profile_tbl
1494 ,p_sales_lead_rec => l_sales_lead_rec
1495 ,x_return_status => l_return_status
1496 ,x_msg_count => l_msg_count
1497 ,x_msg_data => l_msg_data
1498 );
1499
1500 CLOSE c_manager;
1501
1502 IF l_return_status = fnd_api.g_ret_sts_success THEN
1503 result := 'COMPLETE:S';
1504 ELSE
1505 result := 'COMPLETE:ERROR';
1506 END IF;
1507 END IF;
1508 END IF;
1509
1510 EXCEPTION
1511 when others then
1512 wf_core.context(Itemtype, 'EscalatetoManager', itemtype, itemkey,
1513 to_char(actid), funcmode);
1514 result := 'COMPLETE:ERROR';
1515 raise;
1516
1517 END EscalatetoManager;
1518
1519
1520 END AS_LEAD_ROUTING_WF;
1521