[Home] [Help]
PACKAGE BODY: APPS.AML_MONITOR_WF
Source
1 PACKAGE BODY aml_monitor_wf AS
2 /* $Header: amlldmnb.pls 115.40 2004/06/02 01:37:25 chchandr ship $ */
3
4 G_FILE_NAME CONSTANT VARCHAR2(12) := 'amlldmnb.pls';
5 AS_DEBUG_LOW_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
6
7
8
9 -- Start of Comments
10 -- Package name : AML_MONITOR_WF
11 -- Purpose : Sales Leads Workflow
12 -- NOTE :
13 -- History :
14 --
15 -- END of Comments
16
17
18 /*-------------------------------------------------------------------------*
19 |
20 | PRIVATE CONSTANTS
21 |
22 *-------------------------------------------------------------------------*/
23 G_PKG_NAME CONSTANT VARCHAR2(30):= 'AML_MONITOR_WF';
24 G_FILE_NAME CONSTANT VARCHAR2(12) := 'amlldmnb.pls';
25
26 /*-------------------------------------------------------------------------*/
27
28
29
30
31 PROCEDURE LAUNCH_MONITOR (
32 P_Api_Version_Number IN NUMBER,
33 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
34 p_commit IN VARCHAR2 := FND_API.G_FALSE,
35 P_Sales_Lead_Id IN NUMBER,
36 P_Changed_From_stage IN VARCHAR2 ,
37 P_Lead_Action IN VARCHAR2 ,
38 P_Attribute_Changed IN VARCHAR2 ,
39 X_Return_Status OUT NOCOPY VARCHAR2,
40 X_Msg_Count OUT NOCOPY NUMBER,
41 X_Msg_Data OUT NOCOPY VARCHAR2
42 )
43 IS
44 l_api_name CONSTANT VARCHAR2(30) := 'LAUNCH_MONITOR';
45 l_api_version_number CONSTANT NUMBER := 2.0;
46
47 l_sales_lead_id NUMBER;
48 l_monitor_condition_id NUMBER;
49 l_lead_country VARCHAR2(60);
50 l_lead_rank_id NUMBER;
51 l_process_rule_id NUMBER;
52 l_monitor_found VARCHAR2(1);
53 l_time_lag_num NUMBER;
54 l_monitor_type_code VARCHAR2(60);
55 l_count NUMBER;
56
57 l_new_itemtype VARCHAR2(8);
58 l_new_itemkey VARCHAR2(8);
59 l_itemtype VARCHAR2(8) := g_item_type; --'ASXSLASW';
60 l_itemkey VARCHAR2(50);
61 l_itemkey_like VARCHAR2(50);
62 l_existing_itemkey VARCHAR2(50);
63 itemtype VARCHAR2(8);
64 itemkey VARCHAR2(50);
65 workflowprocess VARCHAR2(30) := 'SALES_LEAD_ASSIGNMENT';
66 l_return_status VARCHAR2(1);
67 l_msg_count NUMBER;
68 l_msg_data VARCHAR2(2000);
69 l_status VARCHAR2(80);
70 l_result VARCHAR2(80);
71 l_changed_from_stage VARCHAR2(100);
72 l_monitor_launch_date date;
73 l_lead_creation_date date;
74 l_new_lead varchar2(1) := 'N';
75 l_prev_creation_monitor varchar2(1) := 'N';
76 l_start_new_monitor varchar2(1) := 'Y';
77 -- SWKHANNA 9/8/03
78 l_prev_monitor_type VARCHAR2(60);
79 l_attribute_changed VARCHAR2(60);
80 l_prev_process_rule_id NUMBER;
81
82 -- Get Lead Info
83 CURSOR c_get_lead_info (c_sales_lead_id number) IS
84 SELECT hzl.country, asl.lead_rank_id, asl.creation_date
85 FROM as_sales_leads asl,
86 hz_party_sites hzp,
87 hz_locations hzl
88 WHERE hzl.location_id = hzp.location_id
89 AND hzp.party_site_id = asl.address_id
90 AND asl.sales_lead_id = c_sales_lead_id;
91
92 -- Get all matching monitors -
93
94 CURSOR c_get_matching_monitors(c_country VARCHAR2, c_lead_rank VARCHAR2, c_from_stage_changed VARCHAR2) IS
95 SELECT rule.process_rule_id, rule.monitor_condition_id, rule.time_lag_num
96 FROM (
97 -- ------------------------------------------------------------
98 -- Country
99 -- ------------------------------------------------------------
100 SELECT DISTINCT a.process_rule_id, d.monitor_condition_id, d.time_lag_num
101 FROM pv_process_rules_b a,
102 pv_enty_select_criteria b,
103 pv_selected_attr_values c,
104 AML_monitor_conditions d
105 WHERE b.selection_type_code = 'MONITOR_SCOPE'
106 AND b.attribute_id = pv_check_match_pub.g_a_Country_
107 AND a.process_type = 'LEAD_MONITOR'
108 AND a.process_rule_id = b.process_rule_id
109 AND b.selection_criteria_id = c.selection_criteria_id(+)
110 AND (b.operator = 'EQUALS' AND c.attribute_value = c_country)
111 AND a.process_rule_id = d.process_rule_id
112 AND a.status_code = 'ACTIVE'
113 AND d.time_lag_from_stage = c_from_stage_changed
114 -- ------------------------------------------------------------
115 -- Lead Rating
116 -- ------------------------------------------------------------
117 -- INTERSECT
118 UNION ALL
119 SELECT DISTINCT a.process_rule_id, d.monitor_condition_id, d.time_lag_num
120 FROM pv_process_rules_b a,
121 pv_enty_select_criteria b,
122 pv_selected_attr_values c,
123 AML_monitor_conditions d
124 WHERE b.selection_type_code = 'MONITOR_SCOPE'
125 AND b.attribute_id =pv_check_match_pub.g_a_Lead_Rating
126 AND a.process_type = 'LEAD_MONITOR'
127 AND a.process_rule_id = b.process_rule_id
128 AND b.selection_criteria_id = c.selection_criteria_id(+)
129 AND (b.operator = 'EQUALS' AND c.attribute_value = c_lead_rank )
130 AND a.process_rule_id = d.process_rule_id
131 AND a.status_code = 'ACTIVE'
132 AND d.time_lag_from_stage = c_from_stage_changed
133 ) rule
134 GROUP BY rule.process_rule_id, rule.monitor_condition_id,rule.time_lag_num
135 HAVING (rule.process_rule_id, COUNT(*)) IN (
136 SELECT a.process_rule_id, COUNT(*)
137 FROM pv_process_rules_b a,
138 pv_enty_select_criteria b
139 WHERE a.process_rule_id = b.process_rule_id AND
140 b.selection_type_code = 'MONITOR_SCOPE' AND
141 a.status_code = 'ACTIVE' AND
142 a.process_type = 'LEAD_MONITOR' AND
143 SYSDATE >= a.start_date AND SYSDATE <= a.end_date
144 GROUP BY a.process_rule_id)
145 ORDER BY COUNT(*) DESC,
146 rule.time_lag_num ASC;
147
148
149 CURSOR c_get_existing_wf (c_item_type varchar2, c_item_key_like varchar2) is
150 SELECT item_key
151 FROM wf_items
152 WHERE item_type= c_item_type
153 AND item_key like c_item_key_like
154 AND end_date is null
155 ORDER BY item_key desc;
156
157
158 cursor c_chk_item_key (c_itemtype varchar2,c_itemkey_like varchar2) is
159 select item_key
160 from wf_items
161 where item_type= c_itemtype
162 AND item_key like c_itemkey_like
163 ORDER BY to_number (substr(item_key,(instr(item_key,'_')+1) ) ) desc;
164
165 CURSOR c_prev_monitor_type (c_item_type varchar2, c_item_key varchar2) is
166 SELECT text_value from wf_item_attribute_values
167 WHERE item_type = c_item_type
168 AND item_key like c_item_key
169 AND name = 'TIMELAG_FROM_STAGE'
170 -- swkhanna 9/8/03
171 -- AND text_value = 'CREATION_DATE'
172 ORDER BY item_key desc;
173
174
175
176 CURSOR c_monitor_values (c_item_type varchar2, c_item_key varchar2, c_attr_name varchar2) is
177 SELECT number_value from wf_item_attribute_values
178 WHERE item_type = c_item_type
179 AND item_key = c_item_key
180 AND name = c_attr_name
181 ORDER BY item_key desc;
182
183 BEGIN
184 IF (AS_DEBUG_LOW_ON) THEN
185 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Private API' || l_api_name );
186 END IF;
187 -- Standard Start of API savepoint
188 SAVEPOINT LAUNCH_MONITOR_PUB;
189
190 -- Standard call to check for call compatibility.
191 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
192 p_api_version_number,
193 l_api_name,
194 G_PKG_NAME)
195 THEN
196 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
197 END IF;
198
199 -- Initialize message list IF p_init_msg_list is set to TRUE.
200 IF FND_API.to_Boolean( p_init_msg_list )
201 THEN
202 FND_MSG_PUB.initialize;
203 END IF;
204
205 -- Debug Message
206 IF (AS_DEBUG_LOW_ON) THEN
207 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'PVT:' || l_api_name || ' Start');
208 END IF;
209
210 -- Initialize API return status to SUCCESS
211 x_return_status := FND_API.G_RET_STS_SUCCESS;
212
213 --
214 -- Api body
215 --
216 -- ******************************************************************
217 -- Validate Environment
218 -- ******************************************************************
219 IF FND_GLOBAL.User_Id IS NULL
220 THEN
221 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
222 THEN
223 AS_UTILITY_PVT.Set_Message(
224 p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
225 p_msg_name => 'UT_CANNOT_GET_PROFILE_VALUE',
226 p_token1 => 'PROFILE',
227 p_token1_value => 'USER_ID');
228 END IF;
229 RAISE FND_API.G_EXC_ERROR;
230 END IF;
231
232 if fnd_profile.value('AS_RUN_LEAD_MONITOR_ENGINE') = 'Y' then
233 -- ******************************************************************
234 -- Get Lead Country and Rank
235 -- ******************************************************************
236 open c_get_lead_info (P_Sales_Lead_Id);
237 fetch c_get_lead_info into l_lead_country, l_lead_rank_id,l_lead_creation_date;
238 close c_get_lead_info;
239
240 IF (AS_DEBUG_LOW_ON) THEN
241 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'l_lead_country '||l_lead_country );
242 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'l_lead_rank_id '||l_lead_rank_id );
243 END IF;
244 -- See if this is a new lead or old_lead
245 if p_lead_action = 'CREATE' then
246 l_new_lead := 'Y';
247 elsif p_lead_action = 'UPDATE' then
248 l_new_lead := 'N';
249 end if;
250 --
251 l_attribute_changed := P_Attribute_Changed;
252 -- *******************************************************************************
253 -- swkhanna 9/8/03 Monitors 11.5.10
254 -- *******************************************************************************
255 -- If New Lead, look for creation date monitors. If found , start one.
256 -- If not, look for Relative (assignment) monitors. If found, start that one.
257
258 -- If Old Lead Update,
259 -- check previous monitor running
260 -- If owner changed and rank not changed then
261 -- If previous monitor is 'Absolute', then do not stop it.
262 -- if previous monitor is relative, then reevaluate
263 -- If lead rank changed and owner not changed ,then
264 -- Reevaluate existing, stop existing if not valid
265 -- start new one
266 -- If lead ranb changed and owner changed, then rank gets preference.
267 -- In such case, as_sales_lead_engine will pass in 'RANK' as the attribute change
268
269 -- *******************************************************************************
270
271
272 -- if new_lead, then need to try finding a creation date monitor
273 if l_new_lead = 'Y' then
274
275 IF (AS_DEBUG_LOW_ON) THEN
276 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'New Lead ' );
277 END IF;
278
279 open c_get_matching_monitors(l_lead_country, l_lead_rank_id , P_Changed_From_stage);
280 fetch c_get_matching_monitors into l_process_rule_id, l_monitor_condition_id, l_time_lag_num;
281 if c_get_matching_monitors%NOTFOUND then
282 l_monitor_found := 'N';
283 else
284 l_monitor_found:= 'Y';
285 end if;
286 close c_get_matching_monitors;
287
288 IF (AS_DEBUG_LOW_ON) THEN
289 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'l_monitor_found '||l_monitor_found );
290 END IF;
291
292 -- if no creation date monitors found, then look for assigned date monitor
293
294 IF p_changed_from_stage = 'CREATION_DATE' and l_monitor_found = 'N' then
295
296 l_changed_from_stage := 'ASSIGNED_DATE';
297 open c_get_matching_monitors(l_lead_country, l_lead_rank_id , l_Changed_From_stage);
298 fetch c_get_matching_monitors into l_process_rule_id, l_monitor_condition_id,
299 l_time_lag_num;
300 if c_get_matching_monitors%NOTFOUND then
301 l_monitor_found := 'N';
302 else
303 l_monitor_found:= 'Y';
304 end if;
305 close c_get_matching_monitors;
306 END IF;
307
308
309 -- if old_lead, then chk if any creation_date monitors were attached to this lead earlier
310 elsif l_new_lead = 'N' then
311
312 -- reevaluate on which monitor satisfies now
313 l_changed_from_stage := 'ASSIGNED_DATE';
314 open c_get_matching_monitors(l_lead_country, l_lead_rank_id , l_Changed_From_stage);
315 fetch c_get_matching_monitors into l_process_rule_id, l_monitor_condition_id,
316 l_time_lag_num;
317 if c_get_matching_monitors%NOTFOUND then
318 l_monitor_found := 'N';
319 else
320 l_monitor_found:= 'Y';
321 end if;
322 close c_get_matching_monitors;
323
324 -- ******************************************************************
325 -- Find earlier Active Workflow for this lead
326 -- assuming only one active can exist at one time
327 -- ******************************************************************
328 l_itemkey_like := p_sales_lead_id||'%';
329
330 Open c_get_existing_wf(l_itemtype,l_itemkey_like);
331 loop
332 fetch c_get_existing_wf into l_existing_itemkey;
333
334 IF (AS_DEBUG_LOW_ON) THEN
335 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'l_existing_itemkey:' || l_existing_itemkey);
336 END IF;
337 exit when c_get_existing_wf%NOTFOUND;
338
339 IF (AS_DEBUG_LOW_ON) THEN
340 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'l_existing_itemkey:' || l_existing_itemkey);
341 END IF;
342
343 if l_existing_itemkey is not null then
344 -- Find process_rule_id for the current one running
345 open c_monitor_values (l_itemtype, l_existing_itemkey, 'PROCESS_RULE_ID');
346 fetch c_monitor_values into l_prev_process_rule_id;
347 close c_monitor_values;
348
349 -- Find process type for current running one
350 Open c_prev_monitor_type(l_itemtype,l_itemkey_like);
351 fetch c_prev_monitor_type into l_prev_monitor_type;
352 close c_prev_monitor_type;
353
354
355 if (l_monitor_found = 'Y' and l_process_rule_id = l_prev_process_rule_id )
356 or ( l_prev_monitor_type = 'CREATION_DATE' and l_attribute_changed = 'OWNER')then
357 -- no need to stop earlier one
358 null;
359 else
360 -- abort existing process
361 Wf_Engine.AbortProcess(itemtype => l_itemtype,
362 itemkey => l_existing_itemkey) ;
363 end if;
364
365 IF (AS_DEBUG_LOW_ON) THEN
366 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'after aborting the old one:' || l_existing_itemkey);
367 END IF;
368
369
370
371 end if;
372 end loop;
373 close c_get_existing_wf;
374 end if;
375
376
377
378
379
380
381 /* If l_start_new_monitor = 'Y' then
382 -- end 3/17/03 swkhanna
383
384 -- ******************************************************************
385 -- Select Monitors with particular Time_Lag_From_Stage
386 -- Set Monitors_found flag
387 -- Store Monitors in a PL/SQL table
388 -- If Monitors_Found = 'N' then
389 -- Do Nothing, exit procedure with sucesss
390 -- If count of Monitors_found > 1 then
391 -- Pick one based on Tie breaking rules
392 -- Monitor Scope - More no of attributes, better
393 -- Time Lag Number - Lesser number is better
394
395 -- Find earlier Active workflows for this lead
396 -- If found, then abort earlier process
397 -- Start New Process
398 -- ******************************************************************
399 IF (AS_DEBUG_LOW_ON) THEN
400 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Getting Matching Monitor ' );
401 END IF;
402
403 open c_get_matching_monitors(l_lead_country, l_lead_rank_id , P_Changed_From_stage);
404 fetch c_get_matching_monitors into l_process_rule_id, l_monitor_condition_id, l_time_lag_num;
405 if c_get_matching_monitors%NOTFOUND then
406 l_monitor_found := 'N';
407 else
408 l_monitor_found:= 'Y';
409 end if;
410 close c_get_matching_monitors;
411
412 IF (AS_DEBUG_LOW_ON) THEN
413 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'l_monitor_found '||l_monitor_found );
414 END IF;
415
416 -- if no creation date monitors found, then look for assigned date monitor
417
418 IF p_changed_from_stage = 'CREATION_DATE' and l_monitor_found = 'N' then
419
420 l_changed_from_stage := 'ASSIGNED_DATE';
421 open c_get_matching_monitors(l_lead_country, l_lead_rank_id , l_Changed_From_stage);
422 fetch c_get_matching_monitors into l_process_rule_id, l_monitor_condition_id,
423 l_time_lag_num;
424 if c_get_matching_monitors%NOTFOUND then
425 l_monitor_found := 'N';
426 else
427 l_monitor_found:= 'Y';
428 end if;
429 close c_get_matching_monitors;
430 END IF;
431
432 --
433 IF l_monitor_found = 'Y' THEN
434 --
435 IF (AS_DEBUG_LOW_ON) THEN
436 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'inside l_monitor_found = Y :' );
437 END IF;
438 -- ******************************************************************
439 -- Find earlier Active Workflow for this lead
440 -- assuming only one active can exist at one time
441 -- ******************************************************************
442 l_itemkey_like := p_sales_lead_id||'%';
443
444 Open c_get_existing_wf(l_itemtype,l_itemkey_like);
445 loop
446 fetch c_get_existing_wf into l_existing_itemkey;
447
448 IF (AS_DEBUG_LOW_ON) THEN
449 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'l_existing_itemkey:' || l_existing_itemkey);
450 END IF;
451 exit when c_get_existing_wf%NOTFOUND;
452
453 IF (AS_DEBUG_LOW_ON) THEN
454 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'l_existing_itemkey:' || l_existing_itemkey);
455 END IF;
456
457 if l_existing_itemkey is not null then
458 -- abort existing process
459 Wf_Engine.AbortProcess(itemtype => l_itemtype,
460 itemkey => l_existing_itemkey) ;
461
462 IF (AS_DEBUG_LOW_ON) THEN
463 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'after aborting the old one:' || l_existing_itemkey);
464 END IF;
465
466
467
468 end if;
469 end loop;
470 close c_get_existing_wf;
471
472 -- ******************************************************************
473 */
474 /* SELECT TO_CHAR(AS_WORKFLOW_KEYS_S.nextval) INTO itemkey */
475 /* FROM dual; */
476
477 if l_monitor_found = 'Y' then
478 l_existing_itemkey := null;
479 open c_chk_item_key(g_item_type, P_Sales_Lead_Id||'%' );
480 fetch c_chk_item_key into l_existing_itemkey;
481 close c_chk_item_key;
482
483
484 if l_existing_itemkey is null then
485 select p_sales_lead_id||'_'||'1' into itemkey from dual;
486 else
487 select p_sales_lead_id || '_' || (substr(l_existing_itemkey,(instr(l_existing_itemkey,'_')+1) ) + 1 ) into itemkey
488 from dual ;
489 end if;
490
491 IF (AS_DEBUG_LOW_ON) THEN
492 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'itemkey:' || itemkey);
493 END IF;
494
495 itemtype := AML_MONITOR_WF.g_item_type;
496
497 wf_engine.CreateProcess( ItemType => itemtype,
498 ItemKey => itemkey,
499 process => Workflowprocess);
500
501
502 wf_engine.SetItemUserKey( ItemType => itemtype,
503 ItemKey => itemkey,
504 userkey => p_sales_lead_id);
505
506
507 /* procedure SetItemOwner(
508 itemtype in varchar2,
509 itemkey in varchar2,
510 owner in varchar2) */
511
512 -- Initialize workflow item attributes
513 -- l_process_rule_id, l_monitor_condition_id, l_time_lag_num, l_count
514 --
515
516 wf_engine.SetItemAttrNumber(itemtype => itemtype,
517 itemkey => itemkey,
518 aname => 'PROCESS_RULE_ID',
519 avalue => l_process_rule_id);
520
521 IF (AS_DEBUG_LOW_ON) THEN
522 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'l_process_rule_id:' || l_process_rule_id);
523 END IF;
524
525 wf_engine.SetItemAttrNumber(itemtype => itemtype,
526 itemkey => itemkey,
527 aname => 'MONITOR_CONDITION_ID',
528 avalue => l_monitor_condition_id);
529
530
531 wf_engine.SetItemAttrNumber(itemtype => itemtype,
532 itemkey => itemkey,
533 aname => 'TIMELAG_NUM',
534 avalue => l_time_lag_num);
535
536 wf_engine.SetItemAttrNumber(itemtype => itemtype,
537 itemkey => itemkey,
538 aname => 'SALES_LEAD_ID',
539 avalue => p_sales_lead_id);
540
541
542 select to_date(to_char(sysdate,'MM/DD/YYYY HH:MI:SS AM'),'MM/DD/YYYY HH:MI:SS AM')
543 into l_monitor_launch_date from dual;
544
545 wf_engine.SetItemAttrDate(itemtype => itemtype,
546 itemkey => itemkey,
547 aname => 'MONITOR_LAUNCH_DATE',
548 avalue => l_monitor_launch_date);
549
550 IF (AS_DEBUG_LOW_ON) THEN
551 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'l_time_lag_num:' || l_time_lag_num);
552 END IF;
553
554 wf_engine.StartProcess(itemtype => ItemType,
555 itemkey => ItemKey );
556
557 IF (AS_DEBUG_LOW_ON) THEN
558 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'after start process:' );
559 END IF;
560
561 wf_engine.ItemStatus(itemtype => ItemType,
562 itemkey => ItemKey,
563 status => l_status,
564 result => l_result);
565
566 IF (AS_DEBUG_LOW_ON) THEN
567 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'After ItemStatus:' || l_result);
568 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'l_status:' || l_status);
569 END IF;
570
571 l_itemtype := ItemType;
572 l_itemkey := ItemKey;
573 -- swkhanna 4/7/03 Bug2891236 - changed to check l_result instead of x_return_status
574 -- x_return_status := l_result ;
575
576 IF (AS_DEBUG_LOW_ON) THEN
577 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'x_return_status: '|| x_return_status);
578 END IF;
579
580 -- verify the valid values of return_status from WF and handle them
581 IF l_result = '#NULL' THEN
582 x_return_status := FND_API.G_RET_STS_SUCCESS;
583 ELSIF l_result = 'ERROR' THEN
584 IF (AS_DEBUG_LOW_ON) THEN
585 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_ERROR, 'AS_LEAD_MONITOR_START_FAIL');
586 END IF;
587 RAISE FND_API.G_EXC_ERROR;
588 --ELSIF l_result = 'W' THEN
589 -- x_return_status := 'W';
590 ELSE
591 x_return_status := FND_API.G_RET_STS_SUCCESS;
592 END IF;
593 --
594
595 END IF;-- monitor found
596 --
597 /* else -- if l_start_new_monitor = N
598 x_return_status := FND_API.G_RET_STS_SUCCESS;
599 end if; -- l_start_new_monitor = Y
600 */
601
602 end if; --profile run_lead_monitor_engine
603 -- Debug Message
604 IF (AS_DEBUG_LOW_ON) THEN
605 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'PVT: ' || l_api_name || ' End');
606 END IF;
607 -- Standard call to get message count and IF count is 1, get message info.
608 FND_MSG_PUB.Count_And_Get
609 ( p_count => x_msg_count,
610 p_data => x_msg_data );
611
612 EXCEPTION
613 WHEN FND_API.G_EXC_ERROR THEN
614 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
615 P_API_NAME => L_API_NAME
616 ,P_PKG_NAME => G_PKG_NAME
617 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
618 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PUB
619 ,X_MSG_COUNT => X_MSG_COUNT
620 ,X_MSG_DATA => X_MSG_DATA
621 ,X_RETURN_STATUS => X_RETURN_STATUS);
622
623 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
624 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
625 P_API_NAME => L_API_NAME
626 ,P_PKG_NAME => G_PKG_NAME
627 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
628 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PUB
629 ,X_MSG_COUNT => X_MSG_COUNT
630 ,X_MSG_DATA => X_MSG_DATA
631 ,X_RETURN_STATUS => X_RETURN_STATUS);
632
633 WHEN OTHERS THEN
634 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
635 P_API_NAME => L_API_NAME
636 ,P_PKG_NAME => G_PKG_NAME
637 ,P_EXCEPTION_LEVEL => AS_UTILITY_PVT.G_EXC_OTHERS
638 ,P_SQLCODE => SQLCODE
639 ,P_SQLERRM => SQLERRM
640 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PUB
641 ,X_MSG_COUNT => X_MSG_COUNT
642 ,X_MSG_DATA => X_MSG_DATA
643 ,X_RETURN_STATUS => X_RETURN_STATUS);
644 END LAUNCH_MONITOR;
645 -- *****************************************************************************
646 -- Launch Process
647 -- *****************************************************************************
648
649 PROCEDURE GET_MONITOR_DETAILS(
650 itemtype IN VARCHAR2,
651 itemkey IN VARCHAR2,
652 actid IN NUMBER,
653 funcmode IN VARCHAR2,
654 result OUT NOCOPY VARCHAR2 )
655 IS
656
657 l_status VARCHAR2(80);
658 l_result VARCHAR2(80);
659 l_sequence VARCHAR2(240);
660 l_seqnum NUMBER(38);
661
662 l_profile_rs_id NUMBER;
663
664
665 l_sales_lead_id NUMBER;
666 l_process_rule_id NUMBER;
667 l_monitor_type_code VARCHAR2(60);
668 l_object_version_number NUMBER;
669 l_time_lag_uom_code VARCHAR2(30);
670 l_time_lag_num NUMBER;
671 l_time_lag_from_stage VARCHAR2(100);
672 l_time_lag_to_stage VARCHAR2(100);
673 l_max_reroutes NUMBER;
674 l_expiration_relative VARCHAR2(1);
675 l_Reminder_defined VARCHAR2(1);
676 l_total_reminders NUMBER;
677 l_reminder_frequency NUMBER;
678 l_timeout_defined VARCHAR2(1);
679 l_timeout_duration NUMBER;
680 l_timeout_uom_code VARCHAR2(30);
681
682 l_creation_date DATE;
683 l_last_update_date DATE;
684 l_status_code VARCHAR2(30);
685 l_assign_date DATE;
686 l_accept_flag VARCHAR2(1);
687 l_lead_number VARCHAR2(30);
688 l_lead_rank_id NUMBER;
689 l_monitor_condition_id NUMBER;
690 l_monitor_defined VARCHAR2(1);
691 l_recipient_role varchar2(30);
692 l_lead_owner_username varchar2(60);
693 l_lead_owner_fullname varchar2(60);
694 l_monitor_owner_username varchar2(60);
695 l_manager_username varchar2(60);
696 l_notify_owner varchar2(1);
697 l_notify_manager varchar2(1);
698 l_notify_m_owner varchar2(1);
699 l_notify_role_list VARCHAR2(2000);
700 l_notify_role VARCHAR2(80);
701 l_from_stage_changed VARCHAR2(100);
702 l_expiration_date DATE;
703 l_resource_id NUMBER;
704 l_mgr_resource_id NUMBER;
705 l_current_reroutes NUMBER;
706 l_group_id NUMBER;
707 l_customer_id NUMBER;
708 l_customer_name varchar2(500);
709
710 l_return_status VARCHAR2(1);
711 l_msg_count NUMBER;
712 l_msg_data VARCHAR2(2000);
713
714 l_notify_display_name varchar2(100);
715 l_email_address varchar2(100);
716 l_notification_pref varchar2(100);
717 l_language varchar2(100);
718 l_territory varchar2(100);
719
720 TYPE UserRecType IS RECORD (
721 user_name fnd_user.user_name%type
722 );
723 TYPE UserTableType IS TABLE OF UserRecType INDEX BY BINARY_INTEGER;
724
725 l_user_table UserTableType;
726 l_user_count NUMBER := 1;
727 l_description VARCHAR2(500);
728 l_time_lag_to_stage_meaning VARCHAR2(60);
729 l_get_lead_status_meaning VARCHAR2(60);
730 l_status_code_meaning VARCHAR2(60);
731 l_notify_role_name VARCHAR2(80):= '';
732 l_monitor_launch_date Date;
733
734 CURSOR c_get_monitor_details (c_monitor_condition_id number) is
735 SELECT DISTINCT d.monitor_type_code,d.object_version_number,
736 d.time_lag_from_stage, d.time_lag_to_stage,
737 d.time_lag_num, d.time_lag_uom_code,
738 d.expiration_relative, d.Reminder_defined, d.total_reminders,
739 d.reminder_frequency,d.timeout_defined, d.timeout_duration,
740 d.timeout_uom_code, d.notify_owner, d.notify_owner_manager
741 FROM AML_monitor_conditions d
742 WHERE d.monitor_condition_id = c_monitor_condition_id;
743
744 -- Get lead info
745 CURSOR c_get_lead_details (c_sales_lead_id NUMBER) IS
746 SELECT creation_date, last_update_date, lead_number, status_code,
747 assign_date, accept_flag, lead_rank_id, expiration_date,
748 assign_to_salesforce_id, assign_sales_group_id, current_reroutes, description,
749 customer_id
750 FROM as_sales_leads
751 WHERE sales_lead_id = c_sales_lead_id;
752
753 CURSOR c_lead_owner (c_lead_id number) IS
754 SELECT usr.user_name
755 FROM as_sales_leads lead, fnd_user usr
756 WHERE lead.sales_lead_id = c_lead_id
757 and lead.assign_to_person_id = usr.employee_id;
758
759
760 CURSOR c_lead_owner_fullname (c_assign_to_salesforce_id number) is
761 select source_first_name || ' '||source_last_name
762 from jtf_rs_resource_extns
763 where resource_id = c_assign_to_salesforce_id;
764
765
766 /* */
767 /* CURSOR c_monitor_owner (c_process_rule_id number) IS */
768 /* SELECT usr.user_name */
769 /* FROM pv_process_rules_b rule, fnd_user usr, jtf_rs_resource_extns res */
770 /* WHERE rule.process_rule_id = c_process_rule_id */
771 /* AND rule.owner_resource_id = res.resource_id */
772 /* and res.user_id = usr.user_id; */
773
774 Cursor c_get_mgr_username (c_resource_id number, c_group_id number) is
775 select usr.user_name, res.resource_id
776 from jtf_rs_rep_managers mgr, fnd_user usr, jtf_rs_resource_extns res
777 where mgr.manager_person_id = res.source_id
778 and res.user_id = usr.user_id
779 and mgr.resource_id= c_resource_id
780 and mgr.group_id = c_group_id
781 and mgr.start_date_active <= SYSDATE
782 and (mgr.end_date_active IS NULL OR mgr.end_date_active >= SYSDATE)
783 and mgr.reports_to_flag = 'Y';
784
785 cursor c_get_meaning (c_lookup_type varchar2, c_lookup_code varchar2) is
786 select meaning
787 from as_lookups
788 where lookup_type = c_lookup_type
789 and lookup_code = c_lookup_code;
790
791 cursor c_get_lead_status_meaning (c_status_code varchar2) is
792 select meaning
793 from as_statuses_vl
794 where status_code = c_status_code;
795
796 cursor c_get_customer_name (c_customer_id number) is
797 select party_name
798 from hz_parties
799 where party_id = c_customer_id;
800
801 BEGIN
802 IF (AS_DEBUG_LOW_ON) THEN
803 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Get_Monitor_Details: Start');
804 END IF;
805
806 IF funcmode = 'RUN'
807 THEN
808 l_monitor_condition_id := wf_engine.GetItemAttrText(
809 itemtype => itemtype,
810 itemkey => itemkey,
811 aname => 'MONITOR_CONDITION_ID');
812
813 select to_date(to_char(sysdate,'MM/DD/YYYY HH:MI:SS AM'),'MM/DD/YYYY HH:MI:SS AM')
814 into l_monitor_launch_date from dual;
815
816 wf_engine.SetItemAttrDate(itemtype => itemtype,
817 itemkey => itemkey,
818 aname => 'MONITOR_LAUNCH_DATE',
819 avalue => l_monitor_launch_date);
820
821
822 -- Get Monitor Details
823 OPEN c_get_monitor_details (l_monitor_condition_id);
824 FETCH c_get_monitor_details INTO l_monitor_type_code,l_object_version_number,
825 l_time_lag_from_stage, l_time_lag_to_stage,
826 l_time_lag_num, l_time_lag_uom_code,
827 l_expiration_relative, l_Reminder_defined, l_total_reminders,
828 l_reminder_frequency,l_timeout_defined, l_timeout_duration,
829 l_timeout_uom_code, l_notify_owner, l_notify_manager;
830 CLOSE c_get_monitor_details;
831
832 IF (AS_DEBUG_LOW_ON) THEN
833 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'after getting monitor details:' || l_time_lag_from_stage);
834 END IF;
835 l_sales_lead_id := wf_engine.GetItemAttrNumber(
836 itemtype => itemtype,
837 itemkey => itemkey,
838 aname => 'SALES_LEAD_ID');
839 -- Get Lead Details
840 open c_get_lead_details(l_sales_lead_id);
841 fetch c_get_lead_details into l_creation_date, l_last_update_date, l_lead_number, l_status_code, l_assign_date,
842 l_accept_flag, l_lead_rank_id , l_expiration_date,
843 l_resource_id, l_group_id , l_current_reroutes, l_description,l_customer_id;
844 close c_get_lead_details;
845
846 IF (AS_DEBUG_LOW_ON) THEN
847 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'after getting lead details:' || l_creation_date);
848 END IF;
849
850 wf_engine.SetItemAttrDate(itemtype => itemtype,
851 itemkey => itemkey,
852 aname => 'LEAD_CREATION_DATE',
853 avalue => l_creation_date);
854
855 wf_engine.SetItemAttrDate(itemtype => itemtype,
856 itemkey => itemkey,
857 aname => 'EXPIRATION_DATE',
858 avalue => l_expiration_date);
859
860 wf_engine.SetItemAttrDate(itemtype => itemtype,
861 itemkey => itemkey,
862 aname => 'LEAD_UPDATED_DATE',
863 avalue => l_last_update_date);
864
865 wf_engine.SetItemAttrDate(itemtype => itemtype,
866 itemkey => itemkey,
867 aname => 'LEAD_ASSIGNED_DATE',
868 avalue => l_assign_date);
869
870
871 wf_engine.SetItemAttrText(itemtype => itemtype,
872 itemkey => itemkey,
873 aname => 'LEAD_OWNER_REQD',
874 avalue => 'N');
875
876 wf_engine.SetItemAttrText(itemtype => itemtype,
877 itemkey => itemkey,
878 aname => 'LEAD_NAME',
879 avalue => l_description);
880
881 wf_engine.SetItemAttrText(itemtype => itemtype,
882 itemkey => itemkey,
883 aname => 'LEAD_NUMBER',
884 avalue => l_lead_number);
885
886 open c_get_customer_name (l_customer_id);
887 fetch c_get_customer_name into l_customer_name;
888 close c_get_customer_name;
889
890
891 wf_engine.SetItemAttrText(itemtype => itemtype,
892 itemkey => itemkey,
893 aname => 'LEAD_CUSTOMER_NAME',
894 avalue => l_customer_name);
895
896
897
898 wf_engine.SetItemAttrText(itemtype => itemtype,
899 itemkey => itemkey,
900 aname => 'TIMELAG_UOM_CODE',
901 avalue => l_time_lag_uom_code);
902
903
904 wf_engine.SetItemAttrText(itemtype => itemtype,
905 itemkey => itemkey,
906 aname => 'TIMELAG_FROM_STAGE',
907 avalue => l_time_lag_from_stage);
908
909 wf_engine.SetItemAttrText(itemtype => itemtype,
910 itemkey => itemkey,
911 aname => 'TIMELAG_TO_STAGE',
912 avalue => l_time_lag_to_stage);
913
914 open c_get_meaning('TIME_LAG_TO_STAGE',l_time_lag_to_stage);
915 fetch c_get_meaning into l_time_lag_to_stage_meaning;
916 close c_get_meaning;
917
918
919 wf_engine.SetItemAttrText(itemtype => itemtype,
920 itemkey => itemkey,
921 aname => 'TIMELAG_TO_STAGE_MEANING',
922 avalue => l_time_lag_to_stage_meaning);
923
924 open c_get_lead_status_meaning (l_status_code);
925 fetch c_get_lead_status_meaning into l_status_code_meaning;
926 close c_get_lead_status_meaning;
927
928 wf_engine.SetItemAttrText(itemtype => itemtype,
929 itemkey => itemkey,
930 aname => 'LEAD_STATUS_MEANING',
931 avalue => l_status_code_meaning);
932
933
934
935 l_process_rule_id := wf_engine.GetItemAttrNumber(
936 itemtype => itemtype,
937 itemkey => itemkey,
938 aname => 'PROCESS_RULE_ID');
939
940 wf_engine.SetItemAttrText(itemtype => itemtype,
941 itemkey => itemkey,
942 aname => 'EXPIRATION_RELATIVE',
943 avalue => l_expiration_relative);
944
945
946 wf_engine.SetItemAttrText(itemtype => itemtype,
947 itemkey => itemkey,
948 aname => 'REMINDER_DEFINED',
949 avalue => l_Reminder_defined);
950
951 -- swkhanna 3/21
952 wf_engine.SetItemAttrNumber(itemtype => itemtype,
953 itemkey => itemkey,
954 aname => 'CURRENT_REMINDERS',
955 avalue => 1);
956
957 wf_engine.SetItemAttrNumber(itemtype => itemtype,
958 itemkey => itemkey,
959 aname => 'TOTAL_REMINDERS',
960 avalue => l_total_reminders);
961
962
963 wf_engine.SetItemAttrNumber(itemtype => itemtype,
964 itemkey => itemkey,
965 aname => 'REMINDER_FREQUENCY',
966 avalue => l_reminder_frequency);
967
968
969 wf_engine.SetItemAttrText(itemtype => itemtype,
970 itemkey => itemkey,
971 aname => 'TIMEOUT_DEFINED',
972 avalue => l_timeout_defined);
973
974
975 wf_engine.SetItemAttrNumber(itemtype => itemtype,
976 itemkey => itemkey,
977 aname => 'TIMEOUT_DURATION',
978 avalue => l_timeout_duration);
979
980 wf_engine.SetItemAttrText(itemtype => itemtype,
981 itemkey => itemkey,
982 aname => 'TIMEOUT_UOM_CODE',
983 avalue => l_timeout_uom_code);
984
985 IF (AS_DEBUG_LOW_ON) THEN
986 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
987 'l_process_rule_id:' || l_process_rule_id);
988 END IF;
989
990 if l_current_Reroutes is null then
991 l_current_reroutes := 0;
992 end if;
993 wf_engine.SetItemAttrNumber(itemtype => itemtype,
994 itemkey => itemkey,
995 aname => 'TIMEOUT_CURR_REROUTES',
996 avalue => l_current_reroutes);
997
998 wf_engine.SetItemAttrText(itemtype => itemtype,
999 itemkey => itemkey,
1000 aname => 'NOTIFY_LEAD_OWNER',
1001 avalue => l_notify_owner);
1002
1003
1004 wf_engine.SetItemAttrText(itemtype => itemtype,
1005 itemkey => itemkey,
1006 aname => 'NOTIFY_LD_OWNR_MANAGER',
1007 avalue => l_notify_manager);
1008
1009
1010 OPEN c_lead_owner_fullname (l_resource_id);
1011 FETCH c_lead_owner_fullname into l_lead_owner_fullname;
1012 CLOSE c_lead_owner_fullname;
1013
1014
1015
1016
1017 /* wf_engine.SetItemAttrText(itemtype => itemtype,
1018 itemkey => itemkey,
1019 aname => 'LEAD_OWNER',
1020 avalue => l_lead_owner_fullname);*/
1021
1022
1023
1024 if l_notify_owner = 'Y' then
1025 OPEN c_lead_owner (l_sales_lead_id);
1026 FETCH c_lead_owner INTO l_lead_owner_username;
1027 CLOSE c_lead_owner;
1028
1029 l_notify_role_list := l_notify_role_list||','||l_lead_owner_username;
1030 end if;
1031
1032
1033 /* wf_engine.SetItemAttrNumber(itemtype => itemtype,
1034 itemkey => itemkey,
1035 aname => 'LEAD_OWNER_RESOURCE_ID',
1036 avalue => l_resource_id);
1037
1038 wf_engine.SetItemAttrText(itemtype => itemtype,
1039 itemkey => itemkey,
1040 aname => 'LEAD_OWNER_USERNAME',
1041 avalue => l_lead_owner_username);*/
1042
1043
1044 if l_notify_manager = 'Y' then
1045 -- Get manager username
1046
1047 Open c_get_mgr_username (l_resource_id, l_group_id);
1048 loop
1049 fetch c_get_mgr_username into l_manager_username, l_mgr_resource_id;
1050 exit when c_get_mgr_username%NOTFOUND;
1051 if l_manager_username = l_lead_owner_username then
1052 null;
1053 else
1054 l_notify_role_list := l_notify_role_list||','||l_manager_username;
1055 end if;
1056 end loop;
1057 close c_get_mgr_username;
1058 /*
1059 if l_mgr_resource_id is not null and l_mgr_resource_id <> 0 then
1060
1061 wf_engine.SetItemAttrNumber(itemtype => itemtype,
1062 itemkey => itemkey,
1063 aname => 'LEAD_OWNER_MGR_RESOURCE_ID',
1064 avalue => l_mgr_resource_id);
1065
1066
1067 wf_engine.SetItemAttrText(itemtype => itemtype,
1068 itemkey => itemkey,
1069 aname => 'LEAD_OWNER_MGR_USERNAME',
1070 avalue => l_manager_username);
1071
1072 end if;*/
1073 end if;
1074
1075 l_notify_role_list := substr(l_notify_role_list,2);
1076 l_notify_role := 'AML_' || itemKey;
1077
1078 IF (AS_DEBUG_LOW_ON) THEN
1079 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1080 'l_notify_role_list :' || l_notify_role_list);
1081
1082 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1083 'l_notify_role :' || l_notify_role);
1084 END IF;
1085
1086 -- Create Role
1087
1088 wf_directory.GetRoleInfo (Role => l_notify_role,
1089 Display_Name => l_notify_display_name,
1090 Email_Address => l_email_address,
1091 Notification_Preference => l_notification_pref,
1092 Language => l_language,
1093 Territory => l_territory);
1094
1095
1096 if l_notify_display_name = l_notify_role then
1097 -- skip role creation
1098
1099 wf_directory.RemoveUsersFromAdHocRole
1100 (role_name => l_notify_role,
1101 role_users => null);
1102
1103 /* wf_directory.AddUsersToAdHocRole
1104 (role_name => l_notify_role,
1105 role_users => l_notify_role_list);*/
1106 else
1107 wf_directory.CreateAdHocRole(role_name => l_notify_role,
1108 role_display_name => l_notify_role,
1109 --role_users => l_notify_role_list);
1110 role_users => null);
1111 end if;
1112
1113 IF (AS_DEBUG_LOW_ON) THEN
1114 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1115 'After createAdHocRole :');
1116 END IF;
1117
1118 wf_engine.SetItemAttrText ( ItemType => itemType,
1119 ItemKey => itemKey,
1120 aname => 'NOTIFY_ROLE',
1121 avalue => l_notify_role);
1122
1123 IF (AS_DEBUG_LOW_ON) THEN
1124 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1125 'After setting notify role value :');
1126 END IF;
1127
1128
1129 --x_item_type := ItemType;
1130 --x_item_key := ItemKey;
1131 --x_return_status := l_result ;
1132
1133
1134 l_result := 'COMPLETE';
1135
1136 END IF;
1137
1138 IF (AS_DEBUG_LOW_ON) THEN
1139 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1140 'Result:' || l_result);
1141 END IF;
1142
1143 result := l_result;
1144
1145
1146 EXCEPTION
1147 WHEN OTHERS THEN
1148 IF (AS_DEBUG_LOW_ON) THEN
1149 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1150 'SQLCODE:' || SQLCODE || ' SQLERRM: ' || SQLERRM);
1151 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1152 SQLERRM);
1153 END IF;
1154
1155 wf_core.context(
1156 itemtype,
1157 'Get_Monitor_Details',
1158 itemtype,
1159 itemkey, to_char(actid),funcmode);
1160 result := 'COMPLETE:ERROR';
1161 RAISE;
1162 END GET_MONITOR_DETAILS;
1163
1164 /*******************************/
1165 -- API: Owner_Needed
1166 /*******************************/
1167 PROCEDURE OWNER_NEEDED (
1168 itemtype IN VARCHAR2,
1169 itemkey IN VARCHAR2,
1170 actid IN NUMBER,
1171 funcmode IN VARCHAR2,
1172 result OUT NOCOPY VARCHAR2 )
1173 IS
1174
1175 l_lead_owner_reqd VARCHAR2(1):= 'N';
1176 l_api_name CONSTANT VARCHAR2(30) := 'OWNER_NEEDED';
1177
1178
1179 l_return_status varchar2(1);
1180 l_msg_count number;
1181 l_msg_data varchar2(2000);
1182 l_resultout varchar2(50);
1183 BEGIN
1184 IF (AS_DEBUG_LOW_ON) THEN
1185 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1186 'Owner_Needed: Start');
1187 END IF;
1188
1189 IF funcmode = 'RUN'
1190 THEN
1191 l_lead_owner_reqd := wf_engine.GetItemAttrText(
1192 itemtype => itemtype,
1193 itemkey => itemkey,
1194 aname => 'LEAD_OWNER_REQD');
1195
1196 -- if l_lead_owner_reqd = 'N' then
1197 -- l_resultout := 'COMPLETE:'||'N';
1198 -- elsif l_lead_owner_reqd = 'Y' then
1199 l_resultout := 'COMPLETE';
1200 -- end if;
1201
1202
1203
1204 END IF;
1205
1206 IF (AS_DEBUG_LOW_ON) THEN
1207 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1208 'Result:' || l_resultout);
1209 END IF;
1210
1211 result := l_resultout;
1212 EXCEPTION
1213 WHEN OTHERS THEN
1214 IF (AS_DEBUG_LOW_ON) THEN
1215 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1216 'SQLCODE:' || SQLCODE || ' SQLERRM: ' || SQLERRM);
1217 END IF;
1218 wf_core.context(
1219 itemtype,
1220 'Owner_Needed',
1221 itemtype,
1222 itemkey, to_char(actid),funcmode);
1223 result := 'COMPLETE:ERROR';
1224 RAISE;
1225 END OWNER_NEEDED;
1226
1227
1228
1229
1230 /*******************************/
1231 -- API: TIMEOUT_DEFINED
1232 /*******************************/
1233 PROCEDURE TIMEOUT_DEFINED (
1234 itemtype IN VARCHAR2,
1235 itemkey IN VARCHAR2,
1236 actid IN NUMBER,
1237 funcmode IN VARCHAR2,
1238 result OUT NOCOPY VARCHAR2 )
1239 IS
1240 l_timeout_defined VARCHAR2(1);
1241 -- l_notify_monitor_defined VARCHAR2(1);
1242 l_api_name CONSTANT VARCHAR2(30) := 'TIMEOUT_DEFINED';
1243 -- l_api_version_number CONSTANT NUMBER := 1.0;
1244
1245
1246 l_return_status varchar2(1);
1247 l_msg_count number;
1248 l_msg_data varchar2(2000);
1249 l_resultout varchar2(50);
1250 BEGIN
1251 IF (AS_DEBUG_LOW_ON) THEN
1252 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1253 'Timeout_Defined: Start');
1254 END IF;
1255
1256 IF funcmode = 'RUN'
1257 THEN
1258
1259 -- Get item attributes -
1260 l_timeout_defined := wf_engine.GetItemAttrText(
1261 itemtype => itemtype,
1262 itemkey => itemkey,
1263 aname => 'TIMEOUT_DEFINED');
1264
1265
1266 IF (AS_DEBUG_LOW_ON) THEN
1267 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1268 'l_timeout_defined:' || l_timeout_defined);
1269 END IF;
1270
1271 if l_timeout_defined = 'Y' then
1272 l_resultout := 'COMPLETE:'||'Y';
1273 else
1274 l_resultout := 'COMPLETE:'||'N';
1275 end if;
1276 END IF;
1277
1278 IF (AS_DEBUG_LOW_ON) THEN
1279 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1280 'Result:' || l_resultout);
1281 END IF;
1282 result := l_resultout;
1283 EXCEPTION
1284 WHEN OTHERS THEN
1285 IF (AS_DEBUG_LOW_ON) THEN
1286 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1287 SQLERRM);
1288 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1289 'SQLCODE:' || SQLCODE || ' SQLERRM: ' || SQLERRM);
1290 END IF;
1291 wf_core.context(
1292 itemtype,
1293 'Timeout_Defined',
1294 itemtype,
1295 itemkey, to_char(actid),funcmode);
1296 result := 'COMPLETE:ERROR';
1297 RAISE;
1298 END TIMEOUT_DEFINED;
1299
1300
1301 /*******************************/
1302 -- API: SET_NOTIFY_ATTRIBUTES
1303 /*******************************/
1304 PROCEDURE SET_NOTIFY_ATTRIBUTES (
1305 itemtype IN VARCHAR2,
1306 itemkey IN VARCHAR2,
1307 actid IN NUMBER,
1308 funcmode IN VARCHAR2,
1309 result OUT NOCOPY VARCHAR2 )
1310 IS
1311
1312 l_return_status varchar2(1);
1313 l_msg_count number;
1314 l_msg_data varchar2(2000);
1315 l_resultout varchar2(50);
1316 l_timelag_number number;
1317 l_total_timelag number;
1318 l_timelag_minutes number;
1319 l_monitor_launch_date Date;
1320 l_timelag_due_date Date;
1321 l_expiration_date Date;
1322 l_due_date Date;
1323 l_relative_to_expiration varchar2(1);
1324 BEGIN
1325 IF (AS_DEBUG_LOW_ON) THEN
1326 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1327 'SET_NOTIFY_ATTRIBUTES: Start');
1328 END IF;
1329
1330 IF funcmode = 'RUN'
1331 THEN
1332 IF (AS_DEBUG_LOW_ON) THEN
1333 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1334 'SET_NOTIFY_ATTRIBUTES: Start');
1335 END IF;
1336
1337
1338 l_timelag_number := wf_engine.GetItemAttrNumber(
1339 itemtype => itemtype,
1340 itemkey => itemkey,
1341 aname => 'TIMELAG_NUM');
1342
1343 IF (AS_DEBUG_LOW_ON) THEN
1344 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1345 ' l_timelag_number: '||l_timelag_number);
1346 END IF;
1347
1348 l_monitor_launch_date := wf_engine.GetItemAttrDate(
1349 itemtype => itemtype,
1350 itemkey => itemkey,
1351 aname => 'MONITOR_LAUNCH_DATE');
1352
1353 IF (AS_DEBUG_LOW_ON) THEN
1354 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1355 ' l_monitor_launch_date: '||l_monitor_launch_date);
1356 END IF;
1357
1358 l_relative_to_expiration:= wf_engine.GetItemAttrText(
1359 itemtype => itemtype,
1360 itemkey => itemkey,
1361 aname => 'EXPIRATION_RELATIVE');
1362
1363 IF (AS_DEBUG_LOW_ON) THEN
1364 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1365 ' l_relative_to_expiration: '||l_relative_to_expiration);
1366 END IF;
1367
1368 if l_relative_to_expiration is null then
1369 l_relative_to_expiration := 'N' ;
1370 end if;
1371
1372 l_expiration_date:= wf_engine.GetItemAttrDate(
1373 itemtype => itemtype,
1374 itemkey => itemkey,
1375 aname => 'EXPIRATION_DATE');
1376
1377 IF (AS_DEBUG_LOW_ON) THEN
1378 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1379 ' l_expiration_date: '||l_expiration_date);
1380 END IF;
1381
1382 aml_monitor_wf.set_timelag
1383 ( p_start_date => l_monitor_launch_date,
1384 p_timeout => l_timelag_number,
1385 x_due_date => l_due_date,
1386 x_total_timeout => l_total_timelag) ;
1387
1388 if l_relative_to_expiration = 'N' then
1389 -- assuming only days uom are allowed
1390 l_timelag_minutes := l_timelag_number * 24 * 60;
1391
1392 l_timelag_due_date := l_monitor_launch_date + l_total_timelag;
1393 l_resultout := 'COMPLETE';
1394
1395
1396 IF (AS_DEBUG_LOW_ON) THEN
1397 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1398 ' l_timelag_due_date: '||l_timelag_due_date);
1399 END IF;
1400
1401 elsif l_relative_to_expiration = 'Y' and l_expiration_date is not null then
1402
1403 l_timelag_due_date := l_expiration_date - l_total_timelag;
1404 l_resultout := 'COMPLETE';
1405
1406 elsif l_relative_to_expiration = 'Y' and l_expiration_date is null then
1407 -- set the due date to monitor launch date as the chk_timelag condition
1408 -- will fail in this case and no notification should be sentr
1409
1410 l_timelag_due_date := l_monitor_launch_date ;
1411 l_resultout := 'COMPLETE';
1412
1413 end if;
1414
1415
1416 wf_engine.SetItemAttrNumber(itemtype => itemtype,
1417 itemkey => itemkey,
1418 aname => 'TIMELAG_MINUTES',
1419 avalue => l_timelag_minutes);
1420
1421 wf_engine.SetItemAttrDate(itemtype => itemtype,
1422 itemkey => itemkey,
1423 aname => 'TIMELAG_DUE_DATE',
1424 avalue => l_timelag_due_date);
1425
1426
1427 elsif (funcmode = 'CANCEL') then
1428 l_resultout := 'COMPLETE';
1429
1430 elsif (funcmode in ('RESPOND', 'FORWARD', 'TRANSFER')) then
1431 l_resultout := 'COMPLETE';
1432 elsif (funcmode = 'TIMEOUT') then
1433 l_resultout := 'COMPLETE';
1434
1435 END IF;
1436
1437 IF (AS_DEBUG_LOW_ON) THEN
1438 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1439 'Result:' || l_resultout);
1440 END IF;
1441 l_timelag_minutes := wf_engine.GetItemAttrNumber(
1442 itemtype => itemtype,
1443 itemkey => itemkey,
1444 aname => 'TIMELAG_MINUTES');
1445
1446 IF (AS_DEBUG_LOW_ON) THEN
1447 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1448 'l_timelag_minutes:' || l_timelag_minutes);
1449 END IF;
1450 result := l_resultout;
1451 EXCEPTION
1452 WHEN OTHERS THEN
1453 IF (AS_DEBUG_LOW_ON) THEN
1454 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1455 'SQLCODE:' || SQLCODE || ' SQLERRM: ' || SQLERRM);
1456 END IF;
1457 wf_core.context(
1458 itemtype,
1459 'SET_NOTIFY_ATTRIBUTES',
1460 itemtype,
1461 itemkey, to_char(actid),funcmode);
1462 result := 'COMPLETE:ERROR';
1463 RAISE;
1464 END SET_NOTIFY_ATTRIBUTES;
1465
1466
1467 /*******************************/
1468 -- API: LOG_ACTION
1469 /*******************************/
1470 PROCEDURE LOG_ACTION (
1471 itemtype IN VARCHAR2,
1472 itemkey IN VARCHAR2,
1473 actid IN NUMBER,
1474 funcmode IN VARCHAR2,
1475 result OUT NOCOPY VARCHAR2 )
1476 IS
1477
1478 l_monitor_log_rec AML_MONITOR_LOG_PVT.monitor_log_rec_type ;
1479 l_monitor_condition_id NUMBER;
1480 l_sales_lead_id NUMBER;
1481 l_monitor_action VARCHAR2(30);
1482 l_notify_owner VARCHAR2(1);
1483 l_lead_owner_resource_id NUMBER;
1484 l_notify_owner_manager VARCHAR2(1);
1485 l_lead_owner_mgr_resource_id NUMBER;
1486 l_monitor_log_id NUMBER;
1487
1488
1489
1490
1491 l_return_status varchar2(1);
1492 l_msg_count number;
1493 l_msg_data varchar2(2000);
1494 l_resultout varchar2(50);
1495 BEGIN
1496 IF (AS_DEBUG_LOW_ON) THEN
1497 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1498 'LOG_NOTIFICATION_ACTION: Start');
1499 END IF;
1500
1501 IF funcmode = 'RUN'
1502 THEN
1503
1504
1505 l_monitor_condition_id:= wf_engine.GetItemAttrNumber(
1506 itemtype => itemtype,
1507 itemkey => itemkey,
1508 aname => 'MONITOR_CONDITION_ID');
1509
1510 l_sales_lead_id:= wf_engine.GetItemAttrNumber(
1511 itemtype => itemtype,
1512 itemkey => itemkey,
1513 aname => 'SALES_LEAD_ID');
1514
1515
1516 l_monitor_action:= wf_engine.GetActivityAttrText(itemtype => itemtype,
1517 itemkey => itemkey,
1518 actid => actid,
1519 aname => 'MONITOR_ACTION');
1520
1521 /*
1522 l_monitor_action:= wf_engine.GetItemAttrText(
1523 itemtype => itemtype,
1524 itemkey => itemkey,
1525 aname => 'MONITOR_ACTION');
1526 */
1527 -- l_monitor_action := 'NOTIFICATION';
1528
1529
1530 l_notify_owner:= wf_engine.GetItemAttrText(
1531 itemtype => itemtype,
1532 itemkey => itemkey,
1533 aname => 'NOTIFY_LEAD_OWNER');
1534
1535 l_lead_owner_resource_id:= wf_engine.GetItemAttrNumber(
1536 itemtype => itemtype,
1537 itemkey => itemkey,
1538 aname => 'LEAD_OWNER_RESOURCE_ID');
1539
1540
1541 l_notify_owner_manager:= wf_engine.GetItemAttrText(
1542 itemtype => itemtype,
1543 itemkey => itemkey,
1544 aname => 'NOTIFY_LD_OWNR_MANAGER');
1545
1546 l_lead_owner_mgr_resource_id:= wf_engine.GetItemAttrNumber(
1547 itemtype => itemtype,
1548 itemkey => itemkey,
1549 aname => 'LEAD_OWNER_MGR_RESOURCE_ID');
1550
1551
1552 --if l_notify_owner = 'Y' then
1553 l_monitor_log_rec.monitor_condition_id := l_monitor_condition_id;
1554 l_monitor_log_rec.recipient_role := 'LEAD_OWNER';
1555 l_monitor_log_rec.monitor_action := l_monitor_action;
1556 l_monitor_log_rec.recipient_resource_id := l_lead_owner_resource_id;
1557 l_monitor_log_rec.sales_lead_id := l_sales_lead_id;
1558
1559 AML_MONITOR_LOG_PVT.Create_monitor_Log(
1560 p_api_version_number => 2.0,
1561 p_init_msg_list => FND_API.G_FALSE,
1562 p_commit => FND_API.G_FALSE,
1563 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1564 p_monitor_log_rec => l_monitor_log_rec,
1565 x_monitor_log_id => l_monitor_log_id,
1566 X_Return_Status => l_return_status,
1567 X_Msg_Count => l_msg_count,
1568 X_Msg_Data => l_msg_data
1569 ) ;
1570 /*
1571
1572 SELECT aml_MONITOR_LOG_S.nextval into l_monitor_log_id FROM sys.dual;
1573
1574
1575 INSERT INTO aml_MONITOR_LOG(
1576 MONITOR_LOG_ID
1577 ,LAST_UPDATE_DATE
1578 ,LAST_UPDATED_BY
1579 ,CREATION_DATE
1580 ,CREATED_BY
1581 ,LAST_UPDATE_LOGIN
1582 ,OBJECT_VERSION_NUMBER
1583 ,REQUEST_ID
1584 ,PROGRAM_APPLICATION_ID
1585 ,PROGRAM_ID
1586 ,PROGRAM_UPDATE_DATE
1587 ,MONITOR_CONDITION_ID
1588 ,RECIPIENT_ROLE
1589 ,MONITOR_ACTION
1590 ,RECIPIENT_RESOURCE_ID
1591 ,SALES_LEAD_ID
1592 ) VALUES (
1593 l_monitor_log_id
1594 ,sysdate
1595 ,fnd_global.user_id
1596 ,sysdate
1597 ,fnd_global.user_id
1598 ,fnd_global.user_id
1599 ,1
1600 ,null
1601 ,null
1602 ,null
1603 ,null
1604 ,l_monitor_condition_id
1605 ,'OWNER'
1606 ,l_monitor_action
1607 ,l_lead_owner_resource_id
1608 ,l_sales_lead_id
1609 );
1610
1611 */
1612 -- end if;
1613
1614 if l_notify_owner_manager = 'Y' then
1615 l_monitor_log_rec.monitor_condition_id := l_monitor_condition_id;
1616 l_monitor_log_rec.recipient_role := 'LEAD_OWNER_MANAGER';
1617 l_monitor_log_rec.monitor_action := l_monitor_action;
1618 l_monitor_log_rec.recipient_resource_id := l_lead_owner_mgr_resource_id;
1619 l_monitor_log_rec.sales_lead_id := l_sales_lead_id;
1620
1621 AML_MONITOR_LOG_PVT.Create_monitor_Log(
1622 p_api_version_number => 2.0,
1623 p_init_msg_list => FND_API.G_FALSE,
1624 p_commit => FND_API.G_FALSE,
1625 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1626 p_monitor_log_rec => l_monitor_log_rec,
1627 x_monitor_log_id => l_monitor_log_id,
1628 X_Return_Status => l_return_status,
1629 X_Msg_Count => l_msg_count,
1630 X_Msg_Data => l_msg_data
1631 ) ;
1632
1633
1634 end if;
1635
1636
1637
1638 l_resultout := 'COMPLETE';
1639
1640 END IF;
1641 IF (AS_DEBUG_LOW_ON) THEN
1642 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1643 'Result:' || l_resultout);
1644 END IF;
1645
1646 result := l_resultout;
1647 EXCEPTION
1648
1649 WHEN OTHERS THEN
1650 IF (AS_DEBUG_LOW_ON) THEN
1651 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1652 'SQLCODE:' || SQLCODE || ' SQLERRM: ' || SQLERRM);
1653 END IF;
1654 wf_core.context(
1655 itemtype,
1656 'LOG_ACTION',
1657 itemtype,
1658 itemkey, to_char(actid),funcmode);
1659 result := 'COMPLETE:ERROR';
1660 RAISE;
1661 END LOG_ACTION;
1662 /*******************************/
1663 -- API: CHK_MAX_REMINDERS
1664 -- check if reminders defined.
1665 -- if reminders are defined and the timeout has still not happened, then
1666 -- return 'Y' else 'N'
1667 /*******************************/
1668 PROCEDURE CHK_MAX_REMINDERS (
1669 itemtype IN VARCHAR2,
1670 itemkey IN VARCHAR2,
1671 actid IN NUMBER,
1672 funcmode IN VARCHAR2,
1673 result OUT NOCOPY VARCHAR2 )
1674 IS
1675
1676 l_return_status varchar2(1);
1677 l_msg_count number;
1678 l_msg_data varchar2(2000);
1679 l_resultout varchar2(50);
1680 l_current_reminders number;
1681 l_total_reminders number;
1682 l_reminder_defined varchar2(1);
1683 l_timeout_defined varchar2(1);
1684 BEGIN
1685 IF (AS_DEBUG_LOW_ON) THEN
1686 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1687 'CHK_MAX_REMINDERS: Start');
1688 END IF;
1689
1690 IF funcmode = 'RUN'
1691 THEN
1692 -- Get item attributes -
1693 l_reminder_defined := wf_engine.GetItemAttrText(
1694 itemtype => itemtype,
1695 itemkey => itemkey,
1696 aname => 'REMINDER_DEFINED');
1697
1698
1699
1700 l_timeout_defined := wf_engine.GetItemAttrText(
1701 itemtype => itemtype,
1702 itemkey => itemkey,
1703 aname => 'TIMEOUT_DEFINED');
1704
1705
1706 if l_reminder_defined = 'Y' then
1707
1708 l_total_reminders := wf_engine.GetItemAttrText(
1709 itemtype => itemtype,
1710 itemkey => itemkey,
1711 aname => 'TOTAL_REMINDERS');
1712
1713 l_current_reminders := wf_engine.GetItemAttrText(
1714 itemtype => itemtype,
1715 itemkey => itemkey,
1716 aname => 'CURRENT_REMINDERS');
1717 end if;
1718
1719
1720 END IF;
1721
1722 if l_reminder_defined = 'Y' then
1723 if l_current_reminders <= l_total_reminders then
1724 l_resultout := 'COMPLETE:'||'N';
1725 else
1726 l_resultout := 'COMPLETE:'||'Y';
1727 end if;
1728 else
1729 l_resultout := 'COMPLETE:'||'Y';
1730 end if;
1731
1732 IF (AS_DEBUG_LOW_ON) THEN
1733 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1734 'Result:' || l_resultout);
1735 END IF;
1736
1737 result := l_resultout;
1738 EXCEPTION
1739 WHEN OTHERS THEN
1740 IF (AS_DEBUG_LOW_ON) THEN
1741 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1742 'SQLCODE:' || SQLCODE || ' SQLERRM: ' || SQLERRM);
1743 END IF;
1744 wf_core.context(
1745 itemtype,
1746 'CHK_MAX_REMINDERS',
1747 itemtype,
1748 itemkey, to_char(actid),funcmode);
1749 result := 'COMPLETE:ERROR';
1750 RAISE;
1751 END CHK_MAX_REMINDERS;
1752
1753 /*******************************/
1754 -- API: SET_REMINDER_ATTRIBUTES
1755 /*******************************/
1756 PROCEDURE SET_REMINDER_ATTRIBUTES (
1757 itemtype IN VARCHAR2,
1758 itemkey IN VARCHAR2,
1759 actid IN NUMBER,
1760 funcmode IN VARCHAR2,
1761 result OUT NOCOPY VARCHAR2 )
1762 IS
1763
1764 l_return_status varchar2(1);
1765 l_msg_count number;
1766 l_msg_data varchar2(2000);
1767 l_resultout varchar2(50);
1768 l_timelag_number number;
1769 l_current_reminder number;
1770 l_reminder_frequency number;
1771 l_reminder_timelag_minutes number;
1772 l_total_reminder_timelag number;
1773 l_due_date Date;
1774
1775 BEGIN
1776 IF (AS_DEBUG_LOW_ON) THEN
1777 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1778 'SET_REMINDER_ATTRIBUTES: Start');
1779 END IF;
1780
1781 IF funcmode = 'RUN'
1782 THEN
1783
1784 l_timelag_number := wf_engine.GetItemAttrNumber(
1785 itemtype => itemtype,
1786 itemkey => itemkey,
1787 aname => 'TIMELAG_NUM');
1788
1789 IF (AS_DEBUG_LOW_ON) THEN
1790 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1791 'l_timelag_number:' || l_timelag_number);
1792 END IF;
1793
1794 l_current_reminder := wf_engine.GetItemAttrNumber(
1795 itemtype => itemtype,
1796 itemkey => itemkey,
1797 aname => 'CURRENT_REMINDERS');
1798
1799 IF (AS_DEBUG_LOW_ON) THEN
1800 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1801 'l_current_reminder:' || l_current_reminder);
1802 END IF;
1803
1804 l_reminder_frequency := wf_engine.GetItemAttrNumber(
1805 itemtype => itemtype,
1806 itemkey => itemkey,
1807 aname => 'REMINDER_FREQUENCY');
1808
1809 IF (AS_DEBUG_LOW_ON) THEN
1810 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1811 'l_reminder_frequency:' || l_reminder_frequency);
1812 END IF;
1813
1814 aml_monitor_wf.set_timelag
1815 ( p_start_date => sysdate,
1816 p_timeout => l_reminder_frequency,
1817 x_due_date => l_due_date,
1818 x_total_timeout => l_total_reminder_timelag) ;
1819
1820 -- assuming only days uom are allowed
1821 -- l_reminder_timelag_minutes := ( l_timelag_number*24*60 + (l_current_reminder * l_reminder_frequency*24*60));
1822 -- l_reminder_timelag_minutes := l_reminder_frequency*24*60;
1823 l_reminder_timelag_minutes := l_total_reminder_timelag*24*60;
1824
1825 IF (AS_DEBUG_LOW_ON) THEN
1826 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1827 'l_reminder:' || l_reminder_timelag_minutes);
1828 END IF;
1829
1830 wf_engine.SetItemAttrNumber(itemtype => itemtype,
1831 itemkey => itemkey,
1832 aname => 'REMINDER_TIMELAG_MINUTES',
1833 avalue => l_reminder_timelag_minutes);
1834
1835
1836 wf_engine.SetItemAttrText(itemtype => itemtype,
1837 itemkey => itemkey,
1838 aname => 'MONITOR_ACTION',
1839 avalue => 'REMINDER');
1840
1841 l_resultout := 'COMPLETE';
1842
1843 END IF;
1844
1845 IF (AS_DEBUG_LOW_ON) THEN
1846 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1847 'Result:' || l_resultout);
1848 END IF;
1849
1850 result := l_resultout;
1851 EXCEPTION
1852 WHEN OTHERS THEN
1853 IF (AS_DEBUG_LOW_ON) THEN
1854 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1855 'SQLCODE:' || SQLCODE || ' SQLERRM: ' || SQLERRM);
1856 END IF;
1857 wf_core.context(
1858 itemtype,
1859 'SET_REMINDER_ATTRIBUTES',
1860 itemtype,
1861 itemkey, to_char(actid),funcmode);
1862 result := 'COMPLETE:ERROR';
1863 RAISE;
1864 END SET_REMINDER_ATTRIBUTES;
1865
1866
1867
1868 /*******************************/
1869 -- API: INCREMENT_CURR_REMINDER
1870 /*******************************/
1871 PROCEDURE INCREMENT_CURR_REMINDER (
1872 itemtype IN VARCHAR2,
1873 itemkey IN VARCHAR2,
1874 actid IN NUMBER,
1875 funcmode IN VARCHAR2,
1876 result OUT NOCOPY VARCHAR2 )
1877 IS
1878
1879 l_return_status varchar2(1);
1880 l_msg_count number;
1881 l_msg_data varchar2(2000);
1882 l_resultout varchar2(50);
1883 l_timelag_number number;
1884 l_current_reminder number;
1885 l_reminder_frequency number;
1886 l_reminder_timelag_minutes number;
1887
1888 BEGIN
1889
1890 IF (AS_DEBUG_LOW_ON) THEN
1891 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1892 'INCREMENT_CURR_REMINDER: Start');
1893 END IF;
1894
1895 IF funcmode = 'RUN'
1896 THEN
1897
1898
1899
1900 l_current_reminder := wf_engine.GetItemAttrNumber(
1901 itemtype => itemtype,
1902 itemkey => itemkey,
1903 aname => 'CURRENT_REMINDERS');
1904
1905 l_current_reminder := l_current_reminder + 1;
1906
1907 wf_engine.SetItemAttrNumber(itemtype => itemtype,
1908 itemkey => itemkey,
1909 aname => 'CURRENT_REMINDERS',
1910 avalue => l_current_reminder);
1911
1912
1913
1914 l_resultout := 'COMPLETE';
1915
1916 END IF;
1917 IF (AS_DEBUG_LOW_ON) THEN
1918 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1919 'Result:' || l_resultout);
1920 END IF;
1921 result := l_resultout;
1922 EXCEPTION
1923 WHEN OTHERS THEN
1924 IF (AS_DEBUG_LOW_ON) THEN
1925 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1926 'SQLCODE:' || SQLCODE || ' SQLERRM: ' || SQLERRM);
1927 END IF;
1928 wf_core.context(
1929 itemtype,
1930 'INCREMENT_CURR_REMINDER',
1931 itemtype,
1932 itemkey, to_char(actid),funcmode);
1933 result := 'COMPLETE:ERROR';
1934 RAISE;
1935 END INCREMENT_CURR_REMINDER;
1936
1937 /*******************************/
1938 -- API: INCREMENT_CURR_REROUTES
1939 /*******************************/
1940 PROCEDURE INCREMENT_CURR_REROUTES (
1941 itemtype IN VARCHAR2,
1942 itemkey IN VARCHAR2,
1943 actid IN NUMBER,
1944 funcmode IN VARCHAR2,
1945 result OUT NOCOPY VARCHAR2 )
1946 IS
1947
1948 l_return_status varchar2(1);
1949 l_msg_count number;
1950 l_msg_data varchar2(2000);
1951 l_resultout varchar2(50);
1952 l_timelag_number number;
1953 l_current_reroutes number;
1954 l_sales_lead_id number;
1955
1956
1957 BEGIN
1958
1959 IF (AS_DEBUG_LOW_ON) THEN
1960 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1961 'INCREMENT_CURR_REROUTES: Start');
1962 END IF;
1963
1964 IF funcmode = 'RUN'
1965 THEN
1966
1967
1968
1969 l_current_reroutes := wf_engine.GetItemAttrNumber(
1970 itemtype => itemtype,
1971 itemkey => itemkey,
1972 aname => 'TIMEOUT_CURR_REROUTES');
1973
1974 l_sales_lead_id := wf_engine.GetItemAttrNumber(
1975 itemtype => itemtype,
1976 itemkey => itemkey,
1977 aname => 'SALES_LEAD_ID');
1978
1979 l_current_reroutes := l_current_reroutes + 1;
1980
1981 begin
1982 update as_sales_leads
1983 set current_reroutes = l_current_reroutes
1984 where sales_lead_id = l_sales_lead_id;
1985 exception
1986 when others then
1987 IF (AS_DEBUG_LOW_ON) THEN
1988 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1989 'SQLCODE:' || SQLCODE || ' SQLERRM: ' || SQLERRM);
1990 END IF;
1991 raise;
1992 end;
1993
1994 l_resultout := 'COMPLETE';
1995
1996 END IF;
1997
1998 IF (AS_DEBUG_LOW_ON) THEN
1999 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
2000 'Result:' || l_resultout);
2001 END IF;
2002
2003 result := l_resultout;
2004 EXCEPTION
2005 WHEN OTHERS THEN
2006 IF (AS_DEBUG_LOW_ON) THEN
2007 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
2008 'SQLCODE:' || SQLCODE || ' SQLERRM: ' || SQLERRM);
2009 END IF;
2010 wf_core.context(
2011 itemtype,
2012 'INCREMENT_CURR_REROUTES',
2013 itemtype,
2014 itemkey, to_char(actid),funcmode);
2015 result := 'COMPLETE:ERROR';
2016 RAISE;
2017 END INCREMENT_CURR_REROUTES;
2018
2019
2020
2021 /*******************************/
2022 -- API: SET_TIMEOUT
2023 /*******************************/
2024 PROCEDURE SET_TIMEOUT (
2025 itemtype IN VARCHAR2,
2026 itemkey IN VARCHAR2,
2027 actid IN NUMBER,
2028 funcmode IN VARCHAR2,
2029 result OUT NOCOPY VARCHAR2 )
2030 IS
2031
2032 l_return_status varchar2(1);
2033 l_msg_count number;
2034 l_msg_data varchar2(2000);
2035 l_resultout varchar2(50);
2036 l_timeout_duration_minutes number;
2037 l_timeout_duration number;
2038 l_monitor_launch_date Date;
2039 l_timeout_due_date Date;
2040 l_due_date Date;
2041 l_total_timelag number;
2042 BEGIN
2043 IF (AS_DEBUG_LOW_ON) THEN
2044 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
2045 'Set_Timeout: Start');
2046 END IF;
2047
2048 IF funcmode = 'RUN'
2049 THEN
2050
2051 l_timeout_duration := wf_engine.GetItemAttrNumber(
2052 itemtype => itemtype,
2053 itemkey => itemkey,
2054 aname => 'TIMEOUT_DURATION');
2055
2056 IF (AS_DEBUG_LOW_ON) THEN
2057 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
2058 'l_timeout_duration:' || l_timeout_duration);
2059 END IF;
2060
2061 l_monitor_launch_date := wf_engine.GetItemAttrDate(
2062 itemtype => itemtype,
2063 itemkey => itemkey,
2064 aname => 'MONITOR_LAUNCH_DATE');
2065
2066 l_timeout_duration_minutes := l_timeout_duration*24*60;
2067
2068 aml_monitor_wf.set_timelag
2069 ( p_start_date => l_monitor_launch_date,
2070 p_timeout => l_timeout_duration,
2071 x_due_date => l_due_date,
2072 x_total_timeout => l_total_timelag) ;
2073
2074
2075 l_timeout_due_date := l_monitor_launch_date + l_total_timelag;
2076
2077 IF (AS_DEBUG_LOW_ON) THEN
2078 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
2079 ' l_timeout_duration_minutes:' || l_timeout_duration_minutes);
2080 END IF;
2081
2082 wf_engine.SetItemAttrNumber(itemtype => itemtype,
2083 itemkey => itemkey,
2084 aname => 'TIMEOUT_DURATION_MINUTES',
2085 avalue => l_timeout_duration_minutes);
2086
2087 wf_engine.SetItemAttrDate(itemtype => itemtype,
2088 itemkey => itemkey,
2089 aname => 'TIMEOUT_DUE_DATE',
2090 avalue => l_timeout_due_date);
2091
2092
2093 wf_engine.SetItemAttrText(itemtype => itemtype,
2094 itemkey => itemkey,
2095 aname => 'MONITOR_ACTION',
2096 avalue => 'TIMEOUT');
2097
2098 l_resultout := 'COMPLETE';
2099
2100 END IF;
2101 IF (AS_DEBUG_LOW_ON) THEN
2102 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
2103 'Result:' || l_resultout);
2104 END IF;
2105
2106 result := l_resultout;
2107 EXCEPTION
2108 WHEN OTHERS THEN
2109 IF (AS_DEBUG_LOW_ON) THEN
2110 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
2111 'SQLCODE:' || SQLCODE || ' SQLERRM: ' || SQLERRM);
2112 END IF;
2113 wf_core.context(
2114 itemtype,
2115 'Set_Timeout',
2116 itemtype,
2117 itemkey, to_char(actid),funcmode);
2118 result := 'COMPLETE:ERROR';
2119 RAISE;
2120 END SET_TIMEOUT;
2121
2122 /*******************************/
2123 -- API: CHK_MAX_REROUTES
2124 /*******************************/
2125 PROCEDURE CHK_MAX_REROUTES (
2126 itemtype IN VARCHAR2,
2127 itemkey IN VARCHAR2,
2128 actid IN NUMBER,
2129 funcmode IN VARCHAR2,
2130 result OUT NOCOPY VARCHAR2 )
2131 IS
2132
2133 l_return_status varchar2(1);
2134 l_msg_count number;
2135 l_msg_data varchar2(2000);
2136 l_resultout varchar2(50);
2137 l_curr_reroutes number;
2138 l_max_reroutes number;
2139 l_esc_mgr_resource_id number;
2140 l_source_id number;
2141 l_source_name varchar2(360);
2142 l_source_email varchar2(2000);
2143 l_esc_username varchar2(100);
2144
2145 CURSOR c_esc_username (c_esc_mgr_resource_id number) IS
2146 select source_id, source_name, source_email, user_name
2147 from jtf_rs_resource_extns
2148 where resource_id = c_esc_mgr_resource_id;
2149
2150
2151 BEGIN
2152 IF (AS_DEBUG_LOW_ON) THEN
2153 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
2154 'Chk_Max_Reroutes: Start');
2155 END IF;
2156
2157 IF funcmode = 'RUN'
2158 THEN
2159 -- Get item attributes -
2160 l_curr_reroutes := wf_engine.GetItemAttrNumber(
2161 itemtype => itemtype,
2162 itemkey => itemkey,
2163 aname => 'TIMEOUT_CURR_REROUTES');
2164
2165 l_max_reroutes := fnd_profile.value('AS_MAX_WF_LEAD_REROUTES');
2166
2167 -- swkhanna Jun18,03
2168 -- Get escalation Manager profile value
2169 l_esc_mgr_resource_id := fnd_profile.value('AS_LEAD_ESC_MGR_RESOURCE_ID');
2170
2171
2172 OPEN c_esc_username (l_esc_mgr_resource_id);
2173 FETCH c_esc_username INTO l_source_id, l_source_name, l_source_email, l_esc_username;
2174 CLOSE c_esc_username;
2175
2176 if l_esc_username is not null then
2177
2178 wf_engine.SetItemAttrText(itemtype => itemtype,
2179 itemkey => itemkey,
2180 aname => 'ESCALATION_MGR_USERNAME',
2181 avalue => l_esc_username);
2182
2183
2184 end if;
2185
2186
2187
2188 if l_curr_reroutes < l_max_reroutes then
2189 l_resultout := 'COMPLETE:'||'N';
2190 else
2191 l_resultout := 'COMPLETE:'||'Y';
2192 end if;
2193 END IF;
2194 IF (AS_DEBUG_LOW_ON) THEN
2195 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
2196 'Result:' || l_resultout);
2197 END IF;
2198
2199 result := l_resultout;
2200 EXCEPTION
2201 WHEN OTHERS THEN
2202 IF (AS_DEBUG_LOW_ON) THEN
2203 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
2204 'SQLCODE:' || SQLCODE || ' SQLERRM: ' || SQLERRM);
2205 END IF;
2206 wf_core.context(
2207 itemtype,
2208 'Chk_Max_Reroutes',
2209 itemtype,
2210 itemkey, to_char(actid),funcmode);
2211 result := 'COMPLETE:ERROR';
2212 RAISE;
2213 END CHK_MAX_REROUTES;
2214
2215
2216 /*******************************/
2217 -- API: CHK_TIMELAG_CONDITION_TRUE
2218 /*******************************/
2219 PROCEDURE CHK_TIMELAG_CONDITION_TRUE (
2220 itemtype IN VARCHAR2,
2221 itemkey IN VARCHAR2,
2222 actid IN NUMBER,
2223 funcmode IN VARCHAR2,
2224 result OUT NOCOPY VARCHAR2 )
2225 IS
2226
2227 l_return_status varchar2(1);
2228 l_msg_count number;
2229 l_msg_data varchar2(2000);
2230 l_resultout varchar2(50);
2231 l_timelag_to_stage varchar2(30);
2232 l_sales_lead_id number;
2233 l_is_timelag_lead_status varchar2(1);
2234 l_lead_current_status varchar2(30);
2235 l_is_timelag_lookup varchar2(1);
2236 l_condition_true varchar2(1) := 'N';
2237 l_accept_flag varchar2(1);
2238 l_curr_last_update_date date;
2239 l_orig_last_update_date date;
2240 l_opp_open_status_flag varchar2(1);
2241 l_expiration_date Date;
2242 l_relative_to_expiration varchar2(1);
2243
2244 cursor c_chk_is_timelag_lead_status (c_status_code varchar2) is
2245 select 'Y'
2246 from as_statuses_b
2247 where lead_flag = 'Y'
2248 and status_code = c_status_code;
2249
2250 cursor c_chk_lead_current_status (c_sales_lead_id number) is
2251 select status_code
2252 from as_sales_leads
2253 where sales_lead_id = c_sales_lead_id;
2254
2255 cursor c_chk_timelag_to_stage (c_lookup_code varchar2) is
2256 select 'Y'
2257 from as_lookups
2258 where lookup_type = 'TIME_LAG_TO_STAGE'
2259 and lookup_code = c_lookup_code;
2260
2261 cursor c_chk_lead_accepted (c_sales_lead_id number) is
2262 select accept_flag
2263 from as_sales_leads
2264 where sales_lead_id = c_sales_lead_id;
2265
2266 BEGIN
2267
2268 IF (AS_DEBUG_LOW_ON) THEN
2269 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
2270 'CHK_TIMELAG_CONDITION_TRUE: Start');
2271 END IF;
2272
2273 IF funcmode = 'RUN'
2274 THEN
2275 -- Get item attributes -
2276 l_timelag_to_stage := wf_engine.GetItemAttrText(
2277 itemtype => itemtype,
2278 itemkey => itemkey,
2279 aname => 'TIMELAG_TO_STAGE');
2280
2281
2282 l_sales_lead_id := wf_engine.GetItemAttrNumber(
2283 itemtype => itemtype,
2284 itemkey => itemkey,
2285 aname => 'SALES_LEAD_ID');
2286
2287 l_relative_to_expiration:= wf_engine.GetItemAttrText(
2288 itemtype => itemtype,
2289 itemkey => itemkey,
2290 aname => 'EXPIRATION_RELATIVE');
2291
2292 if l_relative_to_expiration is null then
2293 l_relative_to_expiration := 'N' ;
2294 end if;
2295
2296 l_expiration_date:= wf_engine.GetItemAttrDate(
2297 itemtype => itemtype,
2298 itemkey => itemkey,
2299 aname => 'EXPIRATION_DATE');
2300
2301 -- bugfix# 2801435. Closed Lead should not be monitored
2302
2303 -- add code to see if lead id closed
2304 open c_chk_lead_current_status (l_sales_lead_id);
2305 fetch c_chk_lead_current_status into l_lead_current_status;
2306 close c_chk_lead_current_status;
2307
2308 SELECT nvl(opp_open_status_flag, 'Y')
2309 INTO l_opp_open_status_flag
2310 FROM as_statuses_b
2311 WHERE status_code = l_lead_current_status;
2312
2313 if l_opp_open_status_flag = 'Y' then
2314 l_condition_true := 'Y'; --Need to validate other Timelag condition
2315 else
2316 l_condition_true := 'N';--do not validate any Timelag condition. No notification required
2317 end if;
2318
2319 -- chk if it is a new lookup
2320 open c_chk_timelag_to_stage (l_timelag_to_stage);
2321 fetch c_chk_timelag_to_stage into l_is_timelag_lookup;
2322 close c_chk_timelag_to_stage;
2323
2324 if (l_condition_true = 'Y' AND l_is_timelag_lookup = 'Y') then
2325 -- handle each lookup separately
2326 if l_timelag_to_stage = 'ACCEPTED' then
2327
2328 -- chk if the lead has been accepted yet or not
2329 open c_chk_lead_accepted (l_sales_lead_id);
2330 fetch c_chk_lead_accepted into l_accept_flag;
2331 close c_chk_lead_accepted;
2332
2333 if l_accept_flag = 'N' then
2334 l_condition_true := 'Y';
2335 else
2336 l_condition_true := 'N';
2337 end if;
2338
2339 -- bugfix# 2808633. Need to get date by calling GetItemAttrDate.
2340
2341 elsif l_timelag_to_stage = 'LAST_UPDATE_DATE' then
2342 -- chk to see if lead has been updated since the workflow started
2343 l_orig_last_update_date := wf_engine.GetItemAttrDate(
2344 itemtype => itemtype,
2345 itemkey => itemkey,
2346 aname => 'LEAD_UPDATED_DATE');
2347
2348
2349 select last_update_date
2350 into l_curr_last_update_date
2351 from as_sales_leads
2352 where sales_lead_id = l_sales_lead_id;
2353
2354 -- bugfix# 2808633. The date comparasion cannot be done with equals. If the lead is updated , do not send out notification
2355 --if l_orig_last_update_date = l_curr_last_update_date then
2356 if l_orig_last_update_date < l_curr_last_update_date then
2357 l_condition_true := 'N';
2358 else
2359 l_condition_true := 'Y';
2360 end if;
2361
2362 -- bugfix# 2801435. Closed Lead should not be monitored, LAST_UPDATE_DATE is no longer supported in Action param To
2363 /*
2364 elsif l_timelag_to_stage = 'CLOSED' then
2365 -- add code to see if lead id closed
2366 open c_chk_lead_current_status (l_sales_lead_id);
2367 fetch c_chk_lead_current_status into l_lead_current_status;
2368 close c_chk_lead_current_status;
2369
2370 --
2371 SELECT nvl(opp_open_status_flag, 'Y')
2372 INTO l_opp_open_status_flag
2373 FROM as_statuses_b
2374 WHERE status_code = l_lead_current_status;
2375
2376 if l_opp_open_status_flag = 'Y' then
2377 l_condition_true := 'Y';
2378 else
2379 l_condition_true := 'N';
2380 end if;
2381 */
2382 elsif l_timelag_to_stage = 'IN_PROGRESS' then
2383 -- chk to see if lead in 'In_Progress' status
2384
2385 open c_chk_lead_current_status (l_sales_lead_id);
2386 fetch c_chk_lead_current_status into l_lead_current_status;
2387 close c_chk_lead_current_status;
2388
2389 if l_lead_current_status = 'IN_PROGRESS' then
2390 -- monitor condition still holds true
2391 l_condition_true := 'N';
2392 else
2393 l_condition_true := 'Y';
2394 end if;
2395
2396 end if;
2397 end if;
2398 ---
2399 /*
2400 else -- if not the new lookup, chk for lead status
2401
2402 -- chk if timelag_to_stage is a lead status
2403
2404 open c_chk_is_timelag_lead_status(l_timelag_to_stage);
2405 fetch c_chk_is_timelag_lead_status into l_is_timelag_lead_status;
2406 close c_chk_is_timelag_lead_status;
2407
2408 if l_is_timelag_lead_status = 'Y' then
2409 -- check if lead is still in same status
2410
2411 open c_chk_lead_current_status (l_sales_lead_id);
2412 fetch c_chk_lead_current_status into l_lead_current_status;
2413 close c_chk_lead_current_status;
2414
2415 if l_lead_current_status = l_timelag_to_stage then
2416 -- monitor condition still holds true
2417 l_condition_true := 'N';
2418 else
2419 l_condition_true := 'Y';
2420 end if;
2421 end if;
2422
2423 end if;
2424 */
2425
2426 /*
2427 -- chk if timelag_to_stage is a lead status
2428
2429 open c_chk_is_timelag_lead_status(l_timelag_to_stage);
2430 fetch c_chk_is_timelag_lead_status into l_is_timelag_lead_status;
2431 close c_chk_is_timelag_lead_status;
2432
2433 if l_is_timelag_lead_status = 'Y' then
2434 -- check if lead is still in same status
2435
2436 open c_chk_lead_current_status (l_sales_lead_id);
2437 fetch c_chk_lead_current_status into l_lead_current_status;
2438 close c_chk_lead_current_status;
2439
2440 if l_lead_current_status = l_timelag_to_stage then
2441 -- monitor condition still holds true
2442 l_condition_true := 'N';
2443 else
2444 l_condition_true := 'Y';
2445 end if;
2446
2447 else -- if time_lag_to_stage is not a lead status
2448
2449 -- chk if it is a new lookup
2450 open c_chk_timelag_to_stage (l_timelag_to_stage);
2451 fetch c_chk_timelag_to_stage into l_is_timelag_lookup;
2452 close c_chk_timelag_to_stage;
2453
2454 if l_is_timelag_lookup = 'Y' then
2455 -- handle each lookup separately
2456 if l_timelag_to_stage = 'ACCEPTED' then
2457
2458 -- chk if the lead has been accepted yet or not
2459 open c_chk_lead_accepted (l_sales_lead_id);
2460 fetch c_chk_lead_accepted into l_accept_flag;
2461 close c_chk_lead_accepted;
2462
2463 if l_accept_flag = 'N' then
2464 l_condition_true := 'Y';
2465 else
2466 l_condition_true := 'N';
2467 end if;
2468
2469 elsif l_timelag_to_stage = 'LAST_UPDATE_DATE' then
2470 -- chk to see if lead has been updated since the workflow started
2471 l_orig_last_update_date := wf_engine.GetItemAttrText(
2472 itemtype => itemtype,
2473 itemkey => itemkey,
2474 aname => 'LEAD_UPDATED_DATE');
2475
2476
2477 select last_update_date
2478 into l_curr_last_update_date
2479 from as_sales_leads
2480 where sales_lead_id = l_sales_lead_id;
2481
2482 if l_orig_last_update_date = l_curr_last_update_date then
2483 l_condition_true := 'Y';
2484 else
2485 l_condition_true := 'N';
2486 end if;
2487
2488 end if; -- last_update_date
2489 end if; --l_is_timelag_lookup
2490
2491 end if; -- l_is_timelag_lead_status = 'Y'/'N'
2492
2493 if l_condition_true = 'Y' then
2494 wf_engine.SetItemAttrText(itemtype => itemtype,
2495 itemkey => itemkey,
2496 aname => 'MONITOR_ACTION',
2497 avalue => 'NOTIFICATION');
2498 end if;
2499
2500 */
2501
2502
2503 if l_condition_true = 'Y' then
2504 -- expiration date check
2505 if l_relative_to_expiration = 'N' then
2506 l_condition_true := 'Y';
2507 elsif l_relative_to_expiration = 'Y' and l_expiration_date is not null then
2508 l_condition_true := 'Y';
2509 elsif l_relative_to_expiration = 'Y' and l_expiration_date is null then
2510 l_condition_true := 'N';
2511 end if;
2512 end if;
2513 --
2514 -- swkhanna 2/11/03 Bug 2795647
2515 -- if time_lag condition is true, then get the latest lead owner and set the attribute
2516 -- for sending notification
2517
2518 AML_MONITOR_WF.get_lead_owner
2519 ( itemtype => itemtype,
2520 itemkey => itemkey);
2521
2522
2523 l_resultout := 'COMPLETE:'||l_condition_true;
2524
2525 END IF;
2526
2527 IF (AS_DEBUG_LOW_ON) THEN
2528 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
2529 'Result:' || l_resultout);
2530 END IF;
2531
2532 result := l_resultout;
2533 EXCEPTION
2534 WHEN OTHERS THEN
2535 IF (AS_DEBUG_LOW_ON) THEN
2536 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
2537 'SQLCODE:' || SQLCODE || ' SQLERRM: ' || SQLERRM);
2538 END IF;
2539 wf_core.context(
2540 itemtype,
2541 'Chk_Timelag_condition_true',
2542 itemtype,
2543 itemkey, to_char(actid),funcmode);
2544 result := 'COMPLETE:ERROR';
2545 RAISE;
2546 END CHK_TIMELAG_CONDITION_TRUE;
2547
2548
2549 /*******************************/
2550 -- API: SET_DEFAULT_RESOURCE
2551 /*******************************/
2552 PROCEDURE SET_DEFAULT_RESOURCE (
2553 itemtype IN VARCHAR2,
2554 itemkey IN VARCHAR2,
2555 actid IN NUMBER,
2556 funcmode IN VARCHAR2,
2557 result OUT NOCOPY VARCHAR2 )
2558 IS
2559 l_rs_id NUMBER := NULL;
2560 l_group_id NUMBER := NULL;
2561 l_person_id NUMBER := NULL;
2562 l_resultout varchar2(100);
2563
2564 CURSOR C_get_current_resource IS
2565 SELECT res.resource_id
2566 FROM jtf_rs_resource_extns res
2567 WHERE res.category = 'EMPLOYEE'
2568 AND res.user_id = fnd_global.user_id;
2569
2570 CURSOR c_get_group_id(c_resource_id NUMBER) IS
2571 SELECT grp.group_id
2572 FROM JTF_RS_GROUP_MEMBERS mem,
2573 JTF_RS_ROLE_RELATIONS rrel,
2574 JTF_RS_ROLES_B role,
2575 JTF_RS_GROUP_USAGES u,
2576 JTF_RS_GROUPS_B grp
2577 WHERE mem.group_member_id = rrel.role_resource_id
2578 AND rrel.role_resource_type = 'RS_GROUP_MEMBER'
2579 AND rrel.role_id = role.role_id
2580 AND role.role_type_code IN ('SALES','TELESALES','FIELDSALES','PRM')
2581 AND mem.delete_flag <> 'Y'
2582 AND rrel.delete_flag <> 'Y'
2583 AND SYSDATE BETWEEN rrel.start_date_active AND
2584 NVL(rrel.end_date_active,SYSDATE)
2585 AND mem.resource_id = c_resource_id
2586 AND mem.group_id = u.group_id
2587 AND u.usage = 'SALES'
2588 AND mem.group_id = grp.group_id
2589 AND SYSDATE BETWEEN grp.start_date_active AND
2590 NVL(grp.end_date_active,SYSDATE)
2591 AND ROWNUM < 2;
2592
2593 -- A resource may not be in any group. Besides, jtf_rs_group_members
2594 -- may not have person_id for all resources. Therefore, get person_id
2595 -- in this cursor, instead of in the above cursor.
2596 CURSOR c_get_person_id(c_resource_id NUMBER) IS
2597 SELECT res.source_id
2598 FROM jtf_rs_resource_extns res
2599 WHERE res.resource_id = c_resource_id;
2600
2601 BEGIN
2602
2603 IF funcmode = 'RUN'
2604 THEN
2605
2606 l_rs_id := fnd_profile.value('AS_DEFAULT_RESOURCE_ID');
2607 IF l_rs_id IS NULL
2608 THEN
2609
2610 -- Profile is not set. hence going against the logged in user
2611
2612 OPEN C_get_current_resource;
2613 FETCH C_get_current_resource INTO l_rs_id;
2614 IF (C_get_current_resource%NOTFOUND)
2615 THEN
2616
2617 CLOSE C_get_current_resource;
2618 RETURN;
2619 END IF;
2620 CLOSE C_get_current_resource;
2621
2622 IF l_rs_id IS NOT NULL
2623 THEN
2624
2625 OPEN c_get_group_id (l_rs_id);
2626 FETCH c_get_group_id INTO l_group_id;
2627 CLOSE c_get_group_id;
2628 END IF;
2629
2630 OPEN c_get_person_id (l_rs_id);
2631 FETCH c_get_person_id INTO l_person_id;
2632 CLOSE c_get_person_id;
2633
2634
2635
2636 ELSE -- profile resource id is not null
2637
2638 l_group_id := NULL;
2639 OPEN c_get_group_id (l_rs_id);
2640 FETCH c_get_group_id INTO l_group_id;
2641 CLOSE c_get_group_id;
2642
2643 OPEN c_get_person_id (l_rs_id);
2644 FETCH c_get_person_id INTO l_person_id;
2645 CLOSE c_get_person_id;
2646
2647 /*
2648 OPEN C_get_current_resource;
2649 FETCH C_get_current_resource INTO l_rs_id;
2650 IF (C_get_current_resource%NOTFOUND)
2651 THEN
2652 CLOSE C_get_current_resource;
2653 -- result := 'COMPLETE:ERROR';
2654
2655 RETURN;
2656 END IF;
2657 CLOSE C_get_current_resource;
2658 */
2659
2660 END IF; -- resource id from profile check
2661
2662 wf_engine.SetItemAttrNumber (
2663 itemtype => itemtype,
2664 itemkey => itemkey,
2665 aname => 'RESOURCE_ID',
2666 avalue => l_rs_id);
2667
2668 wf_engine.SetItemAttrNumber (
2669 itemtype => itemtype,
2670 itemkey => itemkey,
2671 aname => 'GROUP_ID',
2672 avalue => l_group_id);
2673
2674 wf_engine.SetItemAttrNumber (
2675 itemtype => itemtype,
2676 itemkey => itemkey,
2677 aname => 'PERSON_ID',
2678 avalue => l_person_id);
2679
2680 wf_engine.SetItemAttrText (
2681 itemtype => itemtype,
2682 itemkey => itemkey,
2683 aname => 'DEFAULT_RESOURCE_SET',
2684 avalue => 'Y');
2685
2686 IF (AS_DEBUG_LOW_ON) THEN
2687 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
2688 'Result:' || l_resultout);
2689 END IF;
2690
2691 l_resultout := 'COMPLETE';
2692 end if;
2693 result := l_resultout;
2694 EXCEPTION
2695 WHEN OTHERS THEN
2696 IF (AS_DEBUG_LOW_ON) THEN
2697 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
2698 'SQLCODE:' || SQLCODE || ' SQLERRM: ' || SQLERRM);
2699 END IF;
2700 wf_core.context(
2701 itemtype,
2702 'Set_Default_Resource',
2703 itemtype,
2704 itemkey, to_char(actid),funcmode);
2705 result := 'COMPLETE:ERROR';
2706 RAISE;
2707 END SET_DEFAULT_RESOURCE;
2708 /*******************************/
2709 -- API: CHK_RESTART_REQD
2710 /*******************************/
2711 PROCEDURE CHK_RESTART_REQD (
2712 itemtype IN VARCHAR2,
2713 itemkey IN VARCHAR2,
2714 actid IN NUMBER,
2715 funcmode IN VARCHAR2,
2716 result OUT NOCOPY VARCHAR2 )
2717 IS
2718
2719 l_return_status varchar2(1);
2720 l_msg_count number;
2721 l_msg_data varchar2(2000);
2722 l_resultout varchar2(50);
2723 l_timeout_duration_minutes number;
2724 l_timeout_duration number;
2725 l_monitor_launch_date Date;
2726 l_timeout_due_date Date;
2727 l_default_resource_set varchar2(1);
2728 l_timelag_from_stage varchar2(60);
2729 BEGIN
2730 IF (AS_DEBUG_LOW_ON) THEN
2731 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
2732 'Chk_Restart_Reqd: Start');
2733 END IF;
2734
2735 IF funcmode = 'RUN'
2736 THEN
2737 -- 3/17/03 swkhanna
2738 -- Chk if this is Creation_date monitor, then don't restart
2739 l_timelag_from_stage := wf_engine.GetItemAttrText(
2740 itemtype => itemtype,
2741 itemkey => itemkey,
2742 aname => 'TIMELAG_FROM_STAGE' );
2743 if l_timelag_from_stage = 'CREATION_DATE' then
2744 l_resultout := 'COMPLETE:N';
2745
2746 else
2747 l_default_resource_set := wf_engine.GetItemAttrText(
2748 itemtype => itemtype,
2749 itemkey => itemkey,
2750 aname => 'DEFAULT_RESOURCE_SET' );
2751
2752 if l_default_resource_set = 'N' then
2753 -- if default resource id is already not set, need to restart
2754 l_resultout := 'COMPLETE:Y';
2755 else
2756 l_resultout := 'COMPLETE:N';
2757 end if;
2758 end if;
2759 END IF;
2760 IF (AS_DEBUG_LOW_ON) THEN
2761 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
2762 'Result:' || l_resultout);
2763
2764 END IF;
2765 result := l_resultout;
2766 EXCEPTION
2767 WHEN OTHERS THEN
2768 IF (AS_DEBUG_LOW_ON) THEN
2769 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
2770 'SQLCODE:' || SQLCODE || ' SQLERRM: ' || SQLERRM);
2771 END IF;
2772 wf_core.context(
2773 itemtype,
2774 'chk_restart_reqd',
2775 itemtype,
2776 itemkey, to_char(actid),funcmode);
2777 result := 'COMPLETE:ERROR';
2778 RAISE;
2779 END CHK_RESTART_REQD;
2780 /*******************************/
2781 -- API: SET_RESTART_ATTR
2782 /*******************************/
2783 PROCEDURE SET_RESTART_ATTR (
2784 itemtype IN VARCHAR2,
2785 itemkey IN VARCHAR2,
2786 actid IN NUMBER,
2787 funcmode IN VARCHAR2,
2788 result OUT NOCOPY VARCHAR2 )
2789 IS
2790
2791 l_return_status varchar2(1);
2792 l_msg_count number;
2793 l_msg_data varchar2(2000);
2794
2795 l_sales_lead_id NUMBER;
2796 l_monitor_condition_id NUMBER;
2797 l_lead_country VARCHAR2(60);
2798 l_lead_rank_id NUMBER;
2799 l_process_rule_id NUMBER;
2800 l_monitor_found VARCHAR2(1);
2801 l_time_lag_num NUMBER;
2802 l_monitor_type_code VARCHAR2(60);
2803 l_count NUMBER;
2804
2805 -- SOLIN, 02/25/2003, bug 2801660
2806 l_reminder_defined VARCHAR2(1);
2807 -- SOLIN, end bug 2801660
2808
2809 l_monitor_launch_date date;
2810 -- Get Lead Info
2811 CURSOR c_get_lead_info1 (c_sales_lead_id number) IS
2812 SELECT hzl.country, asl.lead_rank_id
2813 FROM as_sales_leads asl,
2814 hz_party_sites hzp,
2815 hz_locations hzl
2816 WHERE hzl.location_id = hzp.location_id
2817 AND hzp.party_site_id = asl.address_id
2818 AND asl.sales_lead_id = c_sales_lead_id;
2819
2820 -- Get all matching monitors -
2821
2822 CURSOR c_get_matching_monitors1(c_country VARCHAR2, c_lead_rank VARCHAR2, c_from_stage_changed VARCHAR2) IS
2823 SELECT rule.process_rule_id, rule.monitor_condition_id, rule.time_lag_num
2824 FROM (
2825 -- ------------------------------------------------------------
2826 -- Country
2827 -- ------------------------------------------------------------
2828 SELECT DISTINCT a.process_rule_id, d.monitor_condition_id, d.time_lag_num
2829 FROM pv_process_rules_b a,
2830 pv_enty_select_criteria b,
2831 pv_selected_attr_values c,
2832 AML_monitor_conditions d
2833 WHERE b.selection_type_code = 'MONITOR_SCOPE'
2834 AND b.attribute_id = pv_check_match_pub.g_a_Country_
2835 AND a.process_type = 'LEAD_MONITOR'
2836 AND a.process_rule_id = b.process_rule_id
2837 AND b.selection_criteria_id = c.selection_criteria_id(+)
2838 AND (b.operator = 'EQUALS' AND c.attribute_value = c_country)
2839 AND a.process_rule_id = d.process_rule_id
2840 AND a.status_code = 'ACTIVE'
2841 AND d.time_lag_from_stage = c_from_stage_changed
2842 -- ------------------------------------------------------------
2843 -- Lead Rating
2844 -- ------------------------------------------------------------
2845 INTERSECT
2846 SELECT DISTINCT a.process_rule_id, d.monitor_condition_id, d.time_lag_num
2847 FROM pv_process_rules_b a,
2848 pv_enty_select_criteria b,
2849 pv_selected_attr_values c,
2850 AML_monitor_conditions d
2851 WHERE b.selection_type_code = 'MONITOR_SCOPE'
2852 AND b.attribute_id =pv_check_match_pub.g_a_Lead_Rating
2853 AND a.process_type = 'LEAD_MONITOR'
2854 AND a.process_rule_id = b.process_rule_id
2855 AND b.selection_criteria_id = c.selection_criteria_id(+)
2856 AND (b.operator = 'EQUALS' AND c.attribute_value = c_lead_rank )
2857 AND a.process_rule_id = d.process_rule_id
2858 AND a.status_code = 'ACTIVE'
2859 AND d.time_lag_from_stage = c_from_stage_changed
2860 ) rule
2861 GROUP BY rule.process_rule_id, rule.monitor_condition_id,rule.time_lag_num
2862 HAVING (rule.process_rule_id) IN (
2863 SELECT a.process_rule_id
2864 FROM pv_process_rules_b a,
2865 pv_enty_select_criteria b
2866 WHERE a.process_rule_id = b.process_rule_id AND
2867 b.selection_type_code = 'MONITOR_SCOPE' AND
2868 a.status_code = 'ACTIVE' AND
2869 a.process_type = 'LEAD_MONITOR' AND
2870 SYSDATE >= a.start_date AND SYSDATE <= a.end_date
2871 GROUP BY a.process_rule_id)
2872 ORDER BY rule.time_lag_num ASC;
2873
2874
2875
2876
2877
2878 BEGIN
2879
2880 IF funcmode = 'RUN'
2881 THEN
2882 -- ******************************************************************
2883 -- Get Lead Country and Rank
2884 -- ******************************************************************
2885
2886 l_sales_lead_id := wf_engine.GetItemAttrText(
2887 itemtype => itemtype,
2888 itemkey => itemkey,
2889 aname => 'SALES_LEAD_ID' );
2890
2891
2892 open c_get_lead_info1 (l_Sales_Lead_Id);
2893 fetch c_get_lead_info1 into l_lead_country, l_lead_rank_id;
2894 close c_get_lead_info1;
2895
2896
2897 -- ******************************************************************
2898 -- Select Monitors with particular Time_Lag_From_Stage
2899 -- Set Monitors_found flag
2900 -- Store Monitors in a PL/SQL table
2901 -- If Monitors_Found = 'N' then
2902 -- Do Nothing, exit procedure with sucesss
2903 -- If count of Monitors_found > 1 then
2904 -- Pick one based on Tie breaking rules
2905 -- Monitor Scope - More no of attributes, better
2906 -- Time Lag Number - Lesser number is better
2907
2908 -- Find earlier Active workflows for this lead
2909 -- If found, then abort earlier process
2910 -- Start New Process
2911 -- ******************************************************************
2912 IF (AS_DEBUG_LOW_ON) THEN
2913 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Getting Matching Monitor ' );
2914 END IF;
2915 open c_get_matching_monitors1(l_lead_country, l_lead_rank_id , 'ASSIGNED_DATE');
2916 fetch c_get_matching_monitors1 into l_process_rule_id, l_monitor_condition_id, l_time_lag_num;
2917 if c_get_matching_monitors1%NOTFOUND then
2918 l_monitor_found := 'N';
2919 else
2920 l_monitor_found:= 'Y';
2921 end if;
2922 close c_get_matching_monitors1;
2923
2924 IF (AS_DEBUG_LOW_ON) THEN
2925 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'l_monitor_found '||l_monitor_found );
2926 END IF;
2927 --
2928
2929
2930 if l_monitor_found = 'Y' then
2931
2932 -- Initialize workflow item attributes
2933 -- l_process_rule_id, l_monitor_condition_id, l_time_lag_num, l_count
2934 --
2935
2936 wf_engine.SetItemAttrNumber(itemtype => itemtype,
2937 itemkey => itemkey,
2938 aname => 'PROCESS_RULE_ID',
2939 avalue => l_process_rule_id);
2940
2941 IF (AS_DEBUG_LOW_ON) THEN
2942 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'l_process_rule_id:' || l_process_rule_id);
2943 END IF;
2944
2945 wf_engine.SetItemAttrNumber(itemtype => itemtype,
2946 itemkey => itemkey,
2947 aname => 'MONITOR_CONDITION_ID',
2948 avalue => l_monitor_condition_id);
2949
2950
2951 wf_engine.SetItemAttrNumber(itemtype => itemtype,
2952 itemkey => itemkey,
2953 aname => 'TIMELAG_NUM',
2954 avalue => l_time_lag_num);
2955
2956 wf_engine.SetItemAttrNumber(itemtype => itemtype,
2957 itemkey => itemkey,
2958 aname => 'SALES_LEAD_ID',
2959 avalue => l_sales_lead_id);
2960
2961
2962 select to_date(to_char(sysdate,'MM/DD/YYYY HH:MI:SS AM'),'MM/DD/YYYY HH:MI:SS AM')
2963 into l_monitor_launch_date from dual;
2964
2965 wf_engine.SetItemAttrDate(itemtype => itemtype,
2966 itemkey => itemkey,
2967 aname => 'MONITOR_LAUNCH_DATE',
2968 avalue => l_monitor_launch_date);
2969
2970 -- SOLIN, 02/25/2003, bug 2801660
2971 l_reminder_defined := wf_engine.GetItemAttrText(
2972 itemtype => itemtype,
2973 itemkey => itemkey,
2974 aname => 'REMINDER_DEFINED');
2975 /* 3/24/03 swkhanna commented out
2976 IF l_reminder_defined = 'Y'
2977 THEN
2978 wf_engine.SetItemAttrNumber(itemtype => itemtype,
2979 itemkey => itemkey,
2980 aname => 'CURRENT_REMINDERS',
2981 avalue => 1);
2982 END IF;
2983 */
2984 -- SOLIN, end bug 2801660
2985
2986 result := 'COMPLETE:Y' ;
2987
2988 elsif l_monitor_found = 'N' then
2989 result := 'COMPLETE:N';
2990 end if;
2991 END IF;
2992
2993
2994 EXCEPTION
2995 WHEN OTHERS THEN
2996 IF (AS_DEBUG_LOW_ON) THEN
2997 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
2998 'SQLCODE:' || SQLCODE || ' SQLERRM: ' || SQLERRM);
2999 END IF;
3000 wf_core.context(
3001 itemtype,
3002 'SET_RESTART_ATTR',
3003 itemtype,
3004 itemkey, to_char(actid),funcmode);
3005 result := 'COMPLETE:ERROR';
3006 RAISE;
3007 END SET_RESTART_ATTR;
3008
3009 procedure set_timelag
3010 (p_start_date in date,
3011 p_timeout in out NOCOPY number,
3012 x_due_date out NOCOPY date,
3013 x_total_timeout out nocopy number) is
3014
3015 l_timeout number;
3016 l_due_date date;
3017 l_no_of_wkend number;
3018 l_matched_GMT_date date;
3019 l_server_timezone_id number;
3020 l_GMT_timezone_id number;
3021 l_total_timeout number;
3022
3023 begin
3024
3025
3026 l_timeout := p_timeout;
3027 l_total_timeout := p_timeout;
3028
3029 /* 3/20/03 swkhanna - commented out following to fix bug 2832001
3030 l_server_timezone_id := fnd_profile.value('AMS_SYSTEM_TIMEZONE_ID');
3031
3032 select timezone_id
3033 into l_GMT_timezone_id
3034 from hz_timezones_vl
3035 where name = 'GMT';
3036 */
3037 l_due_date := p_start_date + l_timeout;
3038 l_no_of_wkend := trunc(l_timeout/5);
3039
3040 -- Here the timeout means the number of business days excluding weekends
3041 -- If the timeout crosses the weekends the number of weekends will be added
3042 -- to the timeout date
3043
3044 IF l_no_of_wkend <> 0 THEN
3045
3046 l_due_date := l_due_date + l_no_of_wkend*2;
3047 l_total_timeout := l_total_timeout + l_no_of_wkend*2;
3048
3049 ELSE
3050
3051 -- If the timeout does not cross the weekend i.e < 5 and
3052 -- the day of the assignment is on thursday or friday then
3053 -- the weekend will be added to the timeout date
3054
3055 IF (to_char(sysdate, 'D') <> 7) THEN
3056
3057 IF to_char(sysdate,'D') > to_char(sysdate+l_timeout,'D')
3058 AND l_timeout > 0
3059 THEN
3060
3061 l_due_date := l_due_date+2;
3062 l_total_timeout := l_total_timeout + 2;
3063
3064
3065 END IF;
3066
3067 END IF;
3068
3069 END IF;
3070
3071
3072 -- If the assignment is done in the weekend the
3073 -- actual day the timeout starts will
3074 -- be from the following business day
3075
3076 IF to_char(sysdate,'D') = 7 THEN
3077
3078 l_due_date := l_due_date + 2;
3079 l_total_timeout := l_total_timeout + 2;
3080
3081 ELSIF to_char(sysdate,'D') = 1 THEN
3082
3083 l_due_date := l_due_date + 1;
3084 l_total_timeout := l_total_timeout + 1;
3085
3086 END IF;
3087
3088 -- If the timeout falls on the weekend
3089
3090 IF (to_char(l_due_date, 'D') = 1) OR (to_char(l_due_date, 'D') = 7) THEN
3091
3092 l_due_date := l_due_date + 2;
3093 l_total_timeout := l_total_timeout + 2;
3094
3095 END IF;
3096
3097
3098
3099 IF l_timeout > 5 THEN
3100
3101 IF to_char(sysdate,'D') = 5 THEN
3102
3103 IF mod(l_timeout,5) = 4 THEN
3104
3105 l_due_date := l_due_date+2;
3106 l_total_timeout := l_total_timeout + 2;
3107
3108 END IF;
3109
3110 ELSIF to_char(sysdate,'D') = 6 THEN
3111
3112 IF mod(l_timeout,5) = 4 OR mod(l_timeout,5) = 3 THEN
3113
3114 l_due_date := l_due_date+2;
3115 l_total_timeout := l_total_timeout + 2;
3116
3117 END IF;
3118
3119 END IF;
3120
3121
3122 END IF;
3123
3124 x_due_date := l_due_date;
3125 p_timeout := l_timeout;
3126 x_total_timeout := l_total_timeout ;
3127 -- set item attribute - timeout_due_date
3128 /* update pv_lead_workflows set matched_due_date = l_matched_due_date,
3129 object_version_number = object_version_number + 1
3130 where wf_item_type = p_itemtype
3131 and wf_item_key = p_itemkey;*/
3132
3133
3134 /* HZ_TIMEZONE_PUB.get_time(
3135 p_api_version => 1.0,
3136 p_init_msg_list => p_init_msg_list,
3137 p_source_tz_id => l_server_timezone_id ,
3138 p_dest_tz_id => l_GMT_timezone_id ,
3139 p_source_day_time => l_due_date,
3140 x_dest_day_time => l_matched_GMT_date,
3141 x_return_status => x_return_status,
3142 x_msg_count => x_msg_count,
3143 x_msg_data => x_msg_data);
3144
3145
3146 l_matched_GMT_time := to_char(l_matched_GMT_date,'DD-MON-YYYY HH:MI')||' '||'GMT';*/
3147
3148
3149 /* IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
3150 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
3151 fnd_message.Set_Token('TEXT', 'Matched GMT timeout is '|| l_matched_GMT_time);
3152 fnd_msg_pub.Add;
3153 END IF;
3154
3155
3156
3157 wf_engine.SetItemAttrNumber( itemtype => p_itemtype,
3158 itemkey => p_itemkey,
3159 aname => pv_workflow_pub.g_wf_attr_matched_timeout,
3160 avalue => l_timeout*60*24);
3161
3162 wf_engine.SetItemAttrText( itemtype => p_itemtype,
3163 itemkey => p_itemkey,
3164 aname => pv_workflow_pub.g_wf_attr_matched_timeout_dt,
3165 avalue => l_matched_GMT_time);*/
3166 end set_timelag;
3167
3168 Procedure get_lead_owner
3169 ( itemtype IN VARCHAR2,
3170 itemkey IN VARCHAR2)
3171 is
3172
3173 l_sales_lead_id NUMBER;
3174 l_assign_date DATE;
3175 l_resource_id NUMBER;
3176 l_person_id NUMBER;
3177 l_group_id NUMBER;
3178 l_notify_owner VARCHAR2(60);
3179 l_notify_manager VARCHAR2(60);
3180 l_lead_owner_fullname VARCHAR2(60);
3181 l_lead_owner_username VARCHAR2(60);
3182 l_notify_role_list VARCHAR2(500);
3183 l_notify_role VARCHAR2(60);
3184 l_manager_username VARCHAR2(60);
3185 l_mgr_resource_id NUMBER;
3186
3187 l_notify_display_name varchar2(100);
3188 l_email_address varchar2(100);
3189 l_notification_pref varchar2(100);
3190 l_language varchar2(100);
3191 l_territory varchar2(100);
3192 l_number number;
3193
3194
3195 l_own_source_id NUMBER;
3196 l_own_source_name VARCHAR2(360);
3197 l_own_source_email VARCHAR2(2000);
3198 l_own_name VARCHAR2(320);
3199 l_own_display_name VARCHAR2(360);
3200 l_mgr_source_id NUMBER;
3201 l_mgr_source_name VARCHAR2(360);
3202 l_mgr_source_email VARCHAR2(2000);
3203 l_mgr_name VARCHAR2(320);
3204 l_mgr_display_name VARCHAR2(360);
3205 l_default_resource_id NUMBER := fnd_profile.value('AS_DEFAULT_RESOURCE_ID');
3206 l_source_username VARCHAR2(60);
3207
3208
3209 -- Get lead info
3210 CURSOR c_get_lead_details (c_sales_lead_id NUMBER) IS
3211 SELECT assign_date, assign_to_salesforce_id, assign_sales_group_id, assign_to_person_id
3212 FROM as_sales_leads
3213 WHERE sales_lead_id = c_sales_lead_id;
3214
3215 CURSOR c_lead_owner (c_lead_id number) IS
3216 SELECT usr.user_name
3217 FROM as_sales_leads lead, fnd_user usr
3218 WHERE lead.sales_lead_id = c_lead_id
3219 and lead.assign_to_person_id = usr.employee_id;
3220
3221
3222 CURSOR c_lead_owner_fullname (c_assign_to_salesforce_id number) is
3223 select source_first_name || ' '||source_last_name
3224 from jtf_rs_resource_extns
3225 where resource_id = c_assign_to_salesforce_id;
3226
3227 Cursor c_get_mgr_username (c_resource_id number, c_group_id number) is
3228 select usr.user_name, res.resource_id
3229 from jtf_rs_rep_managers mgr, fnd_user usr, jtf_rs_resource_extns res
3230 where mgr.manager_person_id = res.source_id
3231 and res.user_id = usr.user_id
3232 and mgr.resource_id= c_resource_id
3233 and mgr.group_id = c_group_id
3234 and mgr.start_date_active <= SYSDATE
3235 and (mgr.end_date_active IS NULL OR mgr.end_date_active >= SYSDATE)
3236 and mgr.reports_to_flag = 'Y';
3237
3238
3239
3240 -- 5/2/03 add cursor
3241 Cursor c_get_resource_info(c_resource_id number) is
3242 select source_id, source_name, source_email, user_name
3243 from jtf_rs_resource_extns
3244 where resource_id = c_resource_id;
3245
3246 Begin
3247
3248 l_sales_lead_id := wf_engine.GetItemAttrNumber(
3249 itemtype => itemtype,
3250 itemkey => itemkey,
3251 aname => 'SALES_LEAD_ID');
3252
3253
3254 l_notify_owner := wf_engine.GetItemAttrText
3255 (itemtype => itemtype,
3256 itemkey => itemkey,
3257 aname => 'NOTIFY_LEAD_OWNER');
3258
3259
3260 l_notify_manager := wf_engine.GetItemAttrText(itemtype => itemtype,
3261 itemkey => itemkey,
3262 aname => 'NOTIFY_LD_OWNR_MANAGER');
3263
3264 IF (AS_DEBUG_LOW_ON) THEN
3265 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'l_sales_lead_id :' || l_sales_lead_id);
3266 END IF;
3267
3268 -- Get Lead Details
3269 open c_get_lead_details(l_sales_lead_id);
3270 fetch c_get_lead_details into l_assign_date, l_resource_id, l_group_id, l_person_id ;
3271 close c_get_lead_details;
3272
3273 IF (AS_DEBUG_LOW_ON) THEN
3274 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'l_resource_id :' || l_resource_id);
3275 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'l_group_id :' || l_group_id);
3276 END IF;
3277
3278
3279 wf_engine.SetItemAttrText(itemtype => itemtype,
3280 itemkey => itemkey,
3281 aname => 'NOTIFY_LEAD_OWNER',
3282 avalue => l_notify_owner);
3283
3284
3285 wf_engine.SetItemAttrText(itemtype => itemtype,
3286 itemkey => itemkey,
3287 aname => 'NOTIFY_LD_OWNR_MANAGER',
3288 avalue => l_notify_manager);
3289
3290 OPEN c_lead_owner_fullname (l_resource_id);
3291 FETCH c_lead_owner_fullname into l_lead_owner_fullname;
3292 CLOSE c_lead_owner_fullname;
3293
3294
3295 wf_engine.SetItemAttrText(itemtype => itemtype,
3296 itemkey => itemkey,
3297 aname => 'LEAD_OWNER',
3298 avalue => l_lead_owner_fullname);
3299
3300
3301 -- Send Notifications as per following Logic
3302 -- First check if there is an fnd_user entry
3303 -- if yes, add the user_name to adhoc role
3304 -- if not, create adhoc user and add the addhoc user to adhoc role
3305 -- Also in this case a notification to go out to AS_DEFAULT_RESOURCE_ID
3306
3307
3308 if l_notify_owner = 'Y' then
3309
3310 -- Try to get lead owner's username from fnduser
3311 OPEN c_lead_owner (l_sales_lead_id);
3312 FETCH c_lead_owner INTO l_lead_owner_username;
3313 CLOSE c_lead_owner;
3314
3315
3316 if l_lead_owner_username is not null then
3317 l_notify_role_list := l_notify_role_list||','||l_lead_owner_username;
3318
3319 wf_engine.SetItemAttrText(itemtype => itemtype,
3320 itemkey => itemkey,
3321 aname => 'LEAD_OWNER_USERNAME',
3322 avalue => l_lead_owner_username);
3323
3324
3325 else
3326 -- if no fnd_user record, then send email only
3327 -- need to create adhoc role. get per people info
3328 OPEN c_get_resource_info(l_resource_id);
3329 FETCH c_get_resource_info
3330 INTO l_own_source_id, l_own_source_name, l_own_source_email, l_lead_owner_username;
3331 CLOSE c_get_resource_info;
3332
3333 -- create adhocuser
3334 wf_directory.CreateAdHocUser(name => l_own_name,
3335 display_name => l_own_display_name,
3336 language => null,
3337 territory => null,
3338 description => 'Adhoc role for owner for lead:'||l_sales_lead_id,
3339 notification_preference => 'MAILHTML',
3340 email_address => l_own_source_email,
3341 fax => null,
3342 status => 'ACTIVE',
3343 expiration_date => null);
3344
3345
3346 l_notify_role_list := l_notify_role_list||','||l_own_name;
3347
3348 wf_engine.SetItemAttrText(itemtype => itemtype,
3349 itemkey => itemkey,
3350 aname => 'LEAD_OWNER_USERNAME',
3351 avalue => l_own_name);
3352
3353 --
3354 -- in this case also send notification to AS_DEFAULT_RESOURCE_ID
3355
3356 OPEN c_get_resource_info(l_default_resource_id);
3357 FETCH c_get_resource_info
3358 INTO l_own_source_id, l_own_source_name, l_own_source_email, l_source_username;
3359 CLOSE c_get_resource_info;
3360
3361 if l_source_username is not null then
3362 l_notify_role_list := l_notify_role_list||','||l_source_username;
3363 end if;
3364
3365 end if; -- if fnd user
3366
3367 IF (AS_DEBUG_LOW_ON) THEN
3368 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, '1' || l_notify_role_list);
3369 END IF;
3370
3371 end if; -- if notify_owner
3372
3373
3374 wf_engine.SetItemAttrNumber(itemtype => itemtype,
3375 itemkey => itemkey,
3376 aname => 'LEAD_OWNER_RESOURCE_ID',
3377 avalue => l_resource_id);
3378
3379 /* wf_engine.SetItemAttrText(itemtype => itemtype,
3380 itemkey => itemkey,
3381 aname => 'LEAD_OWNER_USERNAME',
3382 --avalue => l_lead_owner_username);
3383 avalue => l_own_name);
3384
3385 */
3386
3387 if l_notify_manager = 'Y' then
3388 -- Get manager username
3389
3390 Open c_get_mgr_username (l_resource_id, l_group_id);
3391 loop
3392 fetch c_get_mgr_username into l_manager_username, l_mgr_resource_id;
3393 exit when c_get_mgr_username%NOTFOUND;
3394
3395 if l_manager_username = l_lead_owner_username then
3396 null;
3397 elsif l_manager_username is not null then
3398 select instr(l_notify_role_list, l_manager_username) into l_number from dual;
3399 if l_number = 0 then
3400 l_notify_role_list := l_notify_role_list||','||l_manager_username;
3401 else
3402 null;
3403 end if;
3404
3405 wf_engine.SetItemAttrText(itemtype => itemtype,
3406 itemkey => itemkey,
3407 aname => 'LEAD_OWNER_MGR_USERNAME',
3408 avalue => l_manager_username);
3409
3410 --
3411 elsif l_manager_username is null then
3412 -- get Mgr resource info
3413 OPEN c_get_resource_info(l_mgr_resource_id);
3414 FETCH c_get_resource_info
3415 into l_mgr_source_id, l_mgr_source_name, l_mgr_source_email,l_manager_username;
3416 CLOSE c_get_resource_info;
3417
3418 -- create adhoc user
3419 wf_directory.CreateAdHocUser(name => l_mgr_name,
3420 display_name => l_mgr_display_name,
3421 language => null,
3422 territory => null,
3423 description => 'Adhoc role for Manager for lead:'||l_sales_lead_id,
3424 notification_preference => 'MAILHTML',
3425 email_address => l_mgr_source_email,
3426 fax => null,
3427 status => 'ACTIVE',
3428 expiration_date => null);
3429
3430
3431 select instr(l_notify_role_list, l_mgr_name) into l_number from dual;
3432 if l_number = 0 then
3433 l_notify_role_list := l_notify_role_list||','||l_mgr_name;
3434 else
3435 null;
3436 end if;
3437
3438 wf_engine.SetItemAttrText(itemtype => itemtype,
3439 itemkey => itemkey,
3440 aname => 'LEAD_OWNER_MGR_USERNAME',
3441 avalue => l_mgr_name);
3442
3443 end if;
3444
3445 end loop;
3446 close c_get_mgr_username;
3447
3448 if l_mgr_resource_id is not null and l_mgr_resource_id <> 0 then
3449
3450 wf_engine.SetItemAttrNumber(itemtype => itemtype,
3451 itemkey => itemkey,
3452 aname => 'LEAD_OWNER_MGR_RESOURCE_ID',
3453 avalue => l_mgr_resource_id);
3454
3455 /*
3456 wf_engine.SetItemAttrText(itemtype => itemtype,
3457 itemkey => itemkey,
3458 aname => 'LEAD_OWNER_MGR_USERNAME',
3459 --avalue => l_manager_username);
3460 avalue => l_mgr_name);
3461 */
3462
3463 end if;
3464 end if; --l_notify_manager = Y
3465
3466 l_notify_role_list := substr(l_notify_role_list,2);
3467 l_notify_role := 'AML_' || itemKey;
3468
3469 IF (AS_DEBUG_LOW_ON) THEN
3470 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, '8' || l_notify_role_list);
3471 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'l_notify_role :' || l_notify_role);
3472 END IF;
3473
3474 -- Create Role
3475
3476 wf_directory.RemoveUsersFromAdHocRole
3477 (role_name => l_notify_role,
3478 role_users => null);
3479
3480
3481 -- add new set of users
3482 wf_directory.AddUsersToAdHocRole
3483 (role_name => l_notify_role,
3484 role_users => l_notify_role_list);
3485
3486 IF (AS_DEBUG_LOW_ON) THEN
3487 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'After calling adduserstoadhocrole :');
3488 END IF;
3489
3490
3491 wf_engine.SetItemAttrText ( ItemType => itemType,
3492 ItemKey => itemKey,
3493 aname => 'NOTIFY_ROLE',
3494 avalue => l_notify_role);
3495
3496 IF (AS_DEBUG_LOW_ON) THEN
3497 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'After setting notify role value :');
3498 END IF;
3499
3500 exception
3501 when others then
3502 IF (AS_DEBUG_LOW_ON) THEN
3503 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
3504 SQLERRM);
3505 END IF;
3506 raise;
3507
3508 end get_lead_owner;
3509
3510 END AML_MONITOR_WF;