[Home] [Help]
PACKAGE BODY: APPS.AMS_WFTRIG_PVT
Source
1 PACKAGE BODY AMS_WFTrig_PVT AS
2 /* $Header: amsvwftb.pls 120.3 2005/09/13 16:00:40 soagrawa ship $*/
3
4 G_PKG_NAME CONSTANT VARCHAR2(30):='AMS_WfTrig_PVT';
5 G_FILE_NAME CONSTANT VARCHAR2(12):='amsvwtrb.pls';
6
7 -- Start of Comments
8 --
9 -- NAME
10 -- AMS_WFTrig_PVT
11 --
12 -- PURPOSE
13 -- This package performs contains the workflow procedures for
14 -- Continuous Campaigning in Oracle Marketing
15 --
16 -- HISTORY
17 -- 22-MAR-2001 julou created
18 -- 31-May-2001 ptendulk modified Start_process process.
19 -- 29-aug-2002 soagrawa Fixed bug# 2535736
20 -- 25-nov-2002 soagrawa In execute_schedule : added generation of Target group before CSCH activation
21 -- 28-apr-2003 soagrawa Modified/Added APIs for trigger redesign
22 -- 25-aug-2003 soagrawa modified code to fix bug# 3111622 in execute_schedule
23 -- 26-aug-2003 soagrawa modified code to fix bug# 3114609 in get_Aval_repeat_sch
24 -- 26-sep-2003 soagrawa modified code to replace execution of schedule API with WF Bus Event in execute_schedule
25 -- 23-feb-2004 soagrawa bug fix 3452264
26 -- 13-may-2004 soagrawa Fixed bug# 3621786 in get_aval_repeat_Csch
27 -- 09-nov-2004 anchaudh Fixed setting of WF item owners for triggers WF
28 -- 20-aug-2005 soagrawa Added code for Conc Program that will purge monitor history
29 -- 26-aug-2005 soagrawa Fixes for R12
30
31 -- End of Comments
32
33 /*************************** PRIVATE ROUTINES *******************************/
34
35 -- Start of Comments
36 --
37 -- NAME
38 -- Find_Owner
39 --
40 -- PURPOSE
41 -- This Procedure will be called by Initialize_Var to find
42 -- username of the Owner of the Activity
43 --
44 -- Called By
45 -- Initialize_Var
46 --
47 -- NOTES
48 -- When the process is started , all the variables are extracted
49 -- from database using trigger id passed to the Start Process
50 --
51 -- HISTORY
52 -- 22-MAR-2001 julou created
53 -- 26 aug-2005 soagrawa Modified to add flexibility around initiative being monitored for R12
54 -- End of Comments
55
56 AMS_DEBUG_HIGH_ON CONSTANT boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);
57 AMS_DEBUG_LOW_ON CONSTANT boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
58 AMS_DEBUG_MEDIUM_ON CONSTANT boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM);
59
60 PROCEDURE Find_Owner
61 (p_activity_id IN NUMBER ,
62 p_activity_type IN VARCHAR2 ,
63 x_owner_user_name OUT NOCOPY VARCHAR2
64 )
65 IS
66
67 CURSOR c_camp_det IS
68 SELECT owner_user_id
69 FROM ams_campaigns_vl
70 WHERE campaign_id = p_activity_id ;
71
72 CURSOR c_csch_det IS
73 SELECT owner_user_id
74 FROM ams_campaign_schedules_vl
75 WHERE schedule_id = p_activity_id ;
76
77 CURSOR c_deli_det IS
78 SELECT owner_user_id
79 FROM ams_deliverables_vl
80 WHERE deliverable_id = p_activity_id ;
81
82 CURSOR c_eveh_det IS
83 SELECT owner_user_id
84 FROM ams_event_headers_vl
85 WHERE event_header_id = p_activity_id ;
86
87 CURSOR c_eveo_det IS
88 SELECT owner_user_id
89 FROM ams_event_offers_vl
90 WHERE event_offer_id = p_activity_id ;
91
92 CURSOR c_emp_dtl(l_res_id IN NUMBER) IS
93 SELECT employee_id
94 FROM ams_jtf_rs_emp_v
95 WHERE resource_id = l_res_id ;
96
97 l_user_id NUMBER ;
98 l_emp_id NUMBER ;
99 l_display_name VARCHAR2(100);
100
101 BEGIN
102
103 IF p_activity_type in ('CAMP','RCAM') THEN
104 OPEN c_camp_det;
105 FETCH c_camp_det INTO l_user_id;
106 CLOSE c_camp_det ;
107 ELSIF p_activity_type = 'DELI' THEN
108 OPEN c_deli_det;
109 FETCH c_deli_det INTO l_user_id;
110 CLOSE c_deli_det ;
111 ELSIF p_activity_type = 'EVEH' THEN
112 OPEN c_eveh_det;
113 FETCH c_eveh_det INTO l_user_id;
114 CLOSE c_eveh_det ;
115 ELSIF p_activity_type in ('EVEO','EONE') THEN
116 OPEN c_eveo_det;
117 FETCH c_eveo_det INTO l_user_id;
118 CLOSE c_eveo_det ;
119 ELSIF p_activity_type = 'CSCH' THEN
120 OPEN c_csch_det;
121 FETCH c_csch_det INTO l_user_id;
122 CLOSE c_csch_det ;
123 ELSIF p_activity_type = 'JTF' THEN
124 l_user_id := p_activity_id ;
125 END IF ;
126
127 OPEN c_emp_dtl(l_user_id);
128 FETCH c_emp_dtl INTO l_emp_id ;
129 CLOSE c_emp_dtl ;
130
131 -- x_owner_user_name := l_resource_name ;
132
133 -- IF (l_emp_id IS NOT NULL) THEN
134 WF_DIRECTORY.getrolename
135 ( p_orig_system => 'PER',
136 p_orig_system_id => l_emp_id ,
137 p_name => x_owner_user_name,
138 p_display_name => l_display_name );
139
140 -- used for testing
141 -- x_owner_user_name := 'PTENDULK' ;
142 END Find_Owner ;
143
144
145 -- Start of Comments
146 --
147 -- NAME
148 -- Handle_Err
149 --
150 -- PURPOSE
151 -- This Procedure will Get all the Errors from the Message stack and
152 -- Set the Workflow item attribut with the Error Messages
153 --
154 -- Used By Activities
155 --
156 --
157 -- NOTES
158 --
159 -- HISTORY
160 -- 11/05/1999 ptendulk created
161 -- End of Comments
162 PROCEDURE Handle_Err
163 (p_itemtype IN VARCHAR2 ,
164 p_itemkey IN VARCHAR2 ,
165 p_msg_count IN NUMBER , -- Number of error Messages
166 p_msg_data IN VARCHAR2 ,
167 p_attr_name IN VARCHAR2
168 )
169 IS
170 l_msg_count NUMBER ;
171 l_msg_data VARCHAR2(2000);
172 l_final_data VARCHAR2(4000);
173 l_msg_index NUMBER ;
174 l_cnt NUMBER := 0 ;
175 l_trigger_id NUMBER ;
176 l_return_status VARCHAR2(1);
177 l_obj_type VARCHAR2(30);
178 BEGIN
179
180 IF p_itemtype = 'AMS_CSCH'
181 THEN
182 l_trigger_id := WF_ENGINE.GetItemAttrText(
183 itemtype => p_itemtype,
184 itemkey => p_itemkey ,
185 aname => 'SCHEDULE_ID');
186 l_obj_type := 'CSCH';
187 ELSE
188 l_trigger_id := WF_ENGINE.GetItemAttrText(
189 itemtype => p_itemtype,
190 itemkey => p_itemkey ,
191 aname => 'AMS_TRIGGER_ID');
192 l_obj_type := 'TRIG';
193 END IF;
194
195 AMS_Utility_PVT.Create_Log (
196 x_return_status => l_return_status,
197 p_arc_log_used_by => l_obj_type,
198 p_log_used_by_id => l_trigger_id,
199 p_msg_data => 'Error msgs from handle_err for attribute '||p_attr_name,
200 p_msg_type => 'DEBUG'
201 );
202
203 AMS_Utility_PVT.Create_Log (
204 x_return_status => l_return_status,
205 p_arc_log_used_by => l_obj_type,
206 p_log_used_by_id => l_trigger_id,
207 p_msg_data => 'Total messages '||p_msg_count,
208 p_msg_type => 'DEBUG'
209 );
210
211 WHILE l_cnt < p_msg_count
212 LOOP
213 FND_MSG_PUB.Get(p_msg_index => l_cnt + 1,
214 p_encoded => FND_API.G_FALSE,
215 p_data => l_msg_data,
216 p_msg_index_out => l_msg_index ) ;
217 l_final_data := l_final_data ||l_msg_index||': '||l_msg_data||fnd_global.local_chr(10);
218 l_cnt := l_cnt + 1 ;
219
220 AMS_Utility_PVT.Create_Log (
221 x_return_status => l_return_status,
222 p_arc_log_used_by => l_obj_type,
223 p_log_used_by_id => l_trigger_id,
224 p_msg_data => l_msg_index||': '||l_msg_data ,
225 p_msg_type => 'DEBUG'
226 );
227
228 END LOOP ;
229
230 WF_ENGINE.SetItemAttrText(itemtype => p_itemtype,
231 itemkey => p_itemkey ,
232 aname => p_attr_name,
233 avalue => l_final_data );
234
235 END Handle_Err;
236
237 -- Start of Comments
238 --
239 -- NAME
240 -- Action_performed
241 --
242 -- PURPOSE
243 -- This Function will return the meaning of the Action performed
244 -- Used to create the Check Message
245 -- Used By Activities
246 --
247 --
248 -- NOTES
249 --
250 -- HISTORY
251 -- 22-MAR-2001 julou created
252 -- End of Comments
253 FUNCTION Action_performed
254 (p_lookup_code IN VARCHAR2 )
255 RETURN VARCHAR2
256 IS
257 l_meaning VARCHAR2(80);
258 CURSOR c_lookup_det
259 IS
260 SELECT meaning
261 FROM ams_lookups
262 WHERE lookup_type = 'AMS_TRIGGER_ACTION_TYPE'
263 AND lookup_code = p_lookup_code
264 AND enabled_flag = 'Y' ;
265
266 BEGIN
267 OPEN c_lookup_det ;
268 FETCH c_lookup_det INTO l_meaning ;
269 CLOSE c_lookup_det ;
270
271 RETURN l_meaning ;
272
273 END Action_performed;
274
275 -- Start of Comments
276 --
277 -- NAME
278 -- StartProcess
279 --
280 -- PURPOSE
281 -- This Procedure will Start the flow
282 --
283 -- IN
284 -- p_trigger_id Trigger id
285 -- p_trigger_name Trigger Name
286 -- processowner Owner Of the Process
287 -- workflowprocess Work Flaow Process Name (AMS_TRIGGERS)
288 -- item_type Item type DEFAULT NULL(AMS_TRIG)
289
290 --
291 -- OUT
292 --
293 -- Used By Activities
294 --
295 -- NOTES
296 --
297 --
298 -- HISTORY
299 -- 22-MAR-2001 julou created
300 -- 31-May-2001 ptendulk Commented out the trigger action table update call.
301 -- End of Comments
302
303 PROCEDURE StartProcess
304 (p_trigger_id IN NUMBER -- Trigger id
305 -- ,p_user_id IN NUMBER
306 -- ,p_trigger_name IN VARCHAR2
307
308 ,processowner IN VARCHAR2 DEFAULT NULL
309 ,workflowprocess IN VARCHAR2 DEFAULT NULL
310 ,item_type IN VARCHAR2 DEFAULT NULL
311 )
312 IS
313 itemtype VARCHAR2(30) := nvl(item_type,'AMS_TRIG');
314 itemkey VARCHAR2(30) := p_trigger_id || TO_CHAR(SYSDATE,'DDMMRRRRHH24MISS');
315 itemuserkey VARCHAR2(80) ;
316 BEGIN
317 -- Start Process :
318 -- If workflowprocess is passed, it will be run.
319 -- If workflowprocess is NOT passed, the selector function
320 -- defined in the item type will determine which process to run.
321 IF (AMS_DEBUG_HIGH_ON) THEN
322
323 Ams_Utility_pvt.debug_message('Start');
324 END IF;
325 IF (AMS_DEBUG_HIGH_ON) THEN
326
327 Ams_Utility_pvt.debug_message('Item Type : ' || itemtype);
328 END IF;
329 IF (AMS_DEBUG_HIGH_ON) THEN
330
331 Ams_Utility_pvt.debug_message('Item key : ' || itemkey);
332 END IF;
333
334 -- dbms_output.put_line('Creating process');
335 WF_ENGINE.CreateProcess (itemtype => 'AMS_TRIG', --itemtype,
336 itemkey => itemkey ,
337 process => workflowprocess);
338 --dbms_output.put_line('Created process');
339 -- Call a Proc to Initialize the Variables
340 -- dbms_output.put_line('Calling initialazion');
341 /* Initialize_Var
342 (p_trigger_id => p_trigger_id , -- Trigger ID
343 p_itemtype => itemtype ,
344 p_itemkey => itemkey
345 ) ;
346 */
347 /*
348 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
349 itemkey => itemkey,
350 aname => 'AMS_USER_ID',
351 avalue => p_user_id );
352 */
353 itemuserkey := WF_ENGINE.getItemAttrText(itemtype => itemtype,
354 itemkey => itemkey ,
355 aname => 'AMS_TRIGGER_NAME');
356
357 IF (AMS_DEBUG_HIGH_ON) THEN
358 Ams_Utility_pvt.debug_message('After create desc wf_itemsItem key : ' || itemkey);
359
360 END IF;
361
362 WF_ENGINE.SetItemUserkey(itemtype => itemtype,
363 itemkey => itemkey ,
364 userkey => itemuserkey);
365
366 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
367 itemkey => itemkey,
368 aname => 'MONITOR_URL',
369 avalue => wf_monitor.geturl(wf_core.TRANSLATE('WF_WEB_AGENT'), itemtype, itemkey, 'NO'));
370
371 --dbms_output.put_line('Calling WF_ENGINE to start process');
372 --dbms_output.put_line('itemtype: '||itemtype);
373 --dbms_output.put_line('itemkey: '||itemkey);
374 WF_ENGINE.StartProcess (itemtype => itemtype,
375 itemkey => itemkey);
376 --dbms_output.put_line('done');
377 -- Following lines of code is modified by ptendulk on 31-May-2001
378 -- As the trigger action table is no longer required.
379 -- DECLARE
380 -- CURSOR c_trigger_action(l_my_trigger_id NUMBER) IS
381 -- SELECT trigger_id
382 -- FROM ams_trigger_actions
383 -- WHERE trigger_id = l_my_trigger_id ;
384 -- l_dummy NUMBER;
385 -- BEGIN
386 UPDATE ams_triggers
387 SET process_id = TO_NUMBER(itemkey)
388 WHERE trigger_id = p_trigger_id ;
389
390 -- OPEN c_trigger_action(p_trigger_id);
391 -- FETCH c_trigger_action INTO l_dummy ;
392 -- IF c_trigger_action%FOUND THEN
393 -- UPDATE ams_trigger_actions
394 -- SET process_id = TO_NUMBER(itemkey)
395 -- WHERE trigger_id = p_trigger_id ;
396 -- END IF;
397 -- CLOSE c_trigger_action;
398 --END;
399
400 EXCEPTION
401 WHEN OTHERS
402 THEN
403 wf_core.context (G_PKG_NAME, 'StartProcess', p_trigger_id, itemuserkey, workflowprocess);
404 RAISE;
405
406 END StartProcess;
407
408 -- Start of Comments
409 --
410 -- NAME
411 -- Selector
412 --
413 -- PURPOSE
414 -- This Procedure will determine which process to run
415 --
416 -- IN
417 -- itemtype - A Valid item type from (WF_ITEM_TYPES Table).
418 -- itemkey - A string generated from application object's primary key.
419 -- actid - The function Activity
420 -- funcmode - Run / Cancel
421 --
422 -- OUT
423 -- resultout - Name of workflow process to run
424 --
425 -- Used By Activities
426 --
427 -- NOTES
428 --
429 --
430 -- HISTORY
431 -- 22-MAR-2001 julou created
432 -- End of Comments
433
434 PROCEDURE Selector( itemtype IN VARCHAR2,
435 itemkey IN VARCHAR2,
436 actid IN NUMBER,
437 funcmode IN VARCHAR2,
438 resultout OUT NOCOPY VARCHAR2
439 )
440 IS
441 -- PL/SQL Block
442 BEGIN
443 -- dbms_output.put_line('In Selector Function');
444 --
445 -- RUN mode - normal process execution
446 --
447 IF (funcmode = 'RUN')
448 THEN
449 -- Return process to run
450 resultout := 'AMS_TRIGGERS';
451 RETURN;
452 END IF;
453 -- CANCEL mode - activity 'compensation'
454 IF (funcmode = 'CANCEL')
455 THEN
456 -- Return process to run
457 resultout := 'AMS_TRIGGERS';
458 RETURN;
459 END IF;
460 -- TIMEOUT mode
461 IF (funcmode = 'TIMEOUT')
462 THEN
463 resultout := 'AMS_TRIGGERS';
464 RETURN;
465 END IF;
466
467 EXCEPTION
468 WHEN OTHERS
469 THEN
470 wf_core.context (G_PKG_NAME, 'Selector', itemtype, itemkey, actid, funcmode);
471 RAISE;
472 END Selector;
473
474 -- Start of Comments
475 --
476 -- NAME
477 -- Check_Repeat
478 --
479 -- PURPOSE
480 -- This Procedure will return Yes if there the Trigger is repeating
481 -- or it will return No
482 --
483 -- IN
484 -- Itemtype - AMS_TRIG
485 -- Itemkey - Trigger ID
486 -- Accid - Activity ID
487 -- Funmode - Run/Cancel/Timeout
488 --
489 -- OUT
490 -- Result - 'COMPLETE:Y' If the trigger is repeating
491 -- - 'COMPLETE:N' If the trigger is not repeating
492 --
493 -- Used By Activities
494 -- Item Type - AMS_TRIG
495 -- Activity - AMS_CHECK_REPEAT
496 --
497 -- NOTES
498 --
499 --
500 -- HISTORY
501 -- 22-MAR-2001 julou created
502 -- End of Comments
503
504 PROCEDURE Check_Repeat (itemtype IN VARCHAR2,
505 itemkey IN VARCHAR2,
506 actid IN NUMBER,
507 funcmode IN VARCHAR2,
508 result OUT NOCOPY VARCHAR2) IS
509 l_repeat_check VARCHAR2(30) ;
510 BEGIN
511 -- dbms_output.put_line('Process Check_Repeat');
512 -- RUN mode - Normal Process Execution
513 IF (funcmode = 'RUN')
514 THEN
515 l_repeat_check := WF_ENGINE.GetItemAttrText(
516 itemtype => itemtype,
517 itemkey => itemkey ,
518 aname => 'AMS_REPEAT_FREQUENCY_TYPE' );
519
520 IF l_repeat_check = 'NONE' THEN
521 result := 'COMPLETE:N' ;
522 ELSE
523 result := 'COMPLETE:Y' ;
524 END IF ;
525 END IF;
526
527 -- CANCEL mode - Normal Process Execution
528 IF (funcmode = 'CANCEL')
529 THEN
530 result := 'COMPLETE:N' ;
531 RETURN;
532 END IF;
533
534 -- TIMEOUT mode - Normal Process Execution
535 IF (funcmode = 'TIMEOUT')
536 THEN
537 result := 'COMPLETE:N' ;
538 RETURN;
539 END IF;
540 EXCEPTION
541 WHEN OTHERS THEN
542 wf_core.context(G_PKG_NAME,'Check_Repeat',itemtype,itemkey,actid,funcmode);
543 raise ;
544 END Check_Repeat ;
545
546 -- Start of Comments
547 --
548 -- NAME
549 -- Schedule_Trig_Run
550 --
551 -- PURPOSE
552 -- This Procedure will Calculate the next schedule date for Trigger to fire
553 --
554 -- It will Return - Success if the check is successful
555 -- - Error If there is an error in the Check Process
556 --
557 -- IN
558 -- Itemtype - AMS_TRIG
559 -- Itemkey - Trigger ID
560 -- Accid - Activity ID
561 -- Funmode - Run/Cancel/Timeout
562 --
563 -- OUT
564 -- Result - 'COMPLETE:SUCCESS' If the Scheduling is successful
565 -- - 'COMPLETE:ERROR' If the scheduling is errored out
566 --
567 -- Used By Activities
568 -- Item Type - AMS_TRIG
569 -- Activity - AMS_SCHEDULE_TRIG_RUN
570 --
571 -- NOTES
572 --
573 --
574 -- HISTORY
575 -- 22-MAR-2001 julou created
576 -- End of Comments
577
578 PROCEDURE Schedule_Trig_Run (itemtype IN VARCHAR2,
579 itemkey IN VARCHAR2,
580 actid IN NUMBER,
581 funcmode IN VARCHAR2,
582 result OUT NOCOPY VARCHAR2) IS
583 l_msg_count NUMBER ;
584 l_msg_data VARCHAR2(2000);
585 l_final_data VARCHAR2(4000);
586 l_msg_index NUMBER ;
587 l_cnt NUMBER := 0 ;
588 l_trigger_id NUMBER ;
589 l_return_status VARCHAR2(1);
590 l_sch_date DATE;
591 -- l_trigger_date DATE;
592 l_trigger_date VARCHAR2(30);
593 --l_repeat_frequency_type VARCHAR2(30);
594
595 l_user_last_run_date_time DATE ;
596 l_user_next_run_date_time DATE ;
597
598 -- Store the Ref. Date from which to calculate next date
599 l_cur_date DATE ;
600 l_last_run_date DATE;
601
602 -- Temp. Variables
603 l_tmp VARCHAR2(2) ;
604 l_str VARCHAR2(30) ;
605
606 -- Start :anchaudh: 15 Oct'03 : added the following cursor and the variable.
607 CURSOR c_triggers(l_my_trigger_id NUMBER) IS
608 SELECT *
609 FROM ams_triggers
610 WHERE trigger_id = l_my_trigger_id ;
611
612 l_trigger c_triggers%rowtype ;
613 -- End :anchaudh: 15 Oct'03 : added the following cursor and the variable.
614
615 /*
616 CURSOR c_sch_date(l_trigger_id NUMBER) IS
617 SELECT next_run_date_time
618 FROM ams_triggers
619 WHERE trigger_id = l_trigger_id;
620 l_trig_date c_sch_date%ROWTYPE;
621 */
622 BEGIN
623 /*
624 l_trigger_id := WF_ENGINE.GetItemAttrText (itemtype => itemtype,
625 itemkey => itemkey ,
626 aname => 'AMS_TRIGGER_ID');
627
628 UPDATE ams_triggers SET next_run_date_time = (next_run_date_time+1/360) WHERE trigger_id = l_trigger_id;
629 COMMIT;
630 OPEN c_sch_date(l_trigger_id);
631 FETCH c_sch_date INTO l_sch_date;
632 CLOSE c_sch_date;
633 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
634 itemkey => itemkey ,
635 aname => 'AMS_TRIGGER_SCHEDULE_DATE',
636 avalue => to_char(l_sch_date,'DD-MON-RRRR HH:MI:SS AM'));
637
638 result := 'COMPLETE:ERROR' ;
639 */
640 -- dbms_output.put_line('Process Schedule_Trig_Run');
641 IF (funcmode = 'RUN')
642 THEN
643
644 FND_MSG_PUB.initialize;
645
646 l_trigger_id := WF_ENGINE.GetItemAttrText(
647 itemtype => itemtype,
648 itemkey => itemkey ,
649 aname => 'AMS_TRIGGER_ID');
650
651 l_trigger_date := WF_ENGINE.GetItemAttrText(
652 itemtype => itemtype,
653 itemkey => itemkey ,
654 aname => 'AMS_TRIGGER_SCHEDULE_DATE');
655
656 -- dbms_output.put_line('l_trigger_date '|| l_trigger_date);
657 -- dbms_output.put_line('l_trigger_date '||to_char(l_trigger_date,'DD-MON-RRRR HH:MI:SS AM'));
658
659 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
660 itemkey => itemkey ,
661 aname => 'AMS_TRIGGER_DATE',
662 avalue => l_trigger_date
663 );
664
665 -- dbms_output.put_line('Before Condi '||l_trigger_id);
666 -- Call Schedule Procedure only if the trigger is repeating
667
668
669 -- Start :anchaudh: 15 Oct'03 : uncommented the call to "Schedule_Next_Trigger_Run" and calling "Schedule_Repeat" instead.
670
671 /* AMS_ContCampaign_PVT.Schedule_Next_Trigger_Run
672 (p_api_version => 1.0,
673 p_init_msg_list => FND_API.G_FALSE,
674 p_commit => FND_API.G_FALSE,
675 p_trigger_id => l_trigger_id,
676 x_msg_count => l_msg_count,
677 x_msg_data => l_msg_data,
678 x_return_status => l_return_status,
679 x_sch_date => l_sch_date) ;*/
680
681 OPEN c_triggers(l_trigger_id) ;
682 FETCH c_triggers INTO l_trigger ;
683 CLOSE c_triggers ;
684
685 -- First Mark the Last Run Date Time (Update AMS_TRIGGERS with this date
686 -- at the end )
687 IF l_trigger.last_run_date_time IS NULL THEN
688 l_cur_date := l_trigger.start_date_time ;
689 l_last_run_date := l_trigger.start_date_time ;
690 ELSE
691 l_cur_date := l_trigger.next_run_date_time ;
692 l_last_run_date := l_trigger.next_run_date_time ;
693 END IF;
694
695 IF SYSDATE > l_cur_date
696 THEN
697 l_cur_date := sysdate;
698 l_last_run_date := sysdate;
699 END IF;
700
701 AMS_SCHEDULER_PVT.Schedule_Repeat( p_last_run_date => l_cur_date,
702 p_frequency => l_trigger.repeat_every_x_frequency,
703 p_frequency_type => l_trigger.repeat_frequency_type ,
704 x_next_run_date => l_sch_date,
705 x_return_status => l_return_status,
706 x_msg_count => l_msg_count,
707 x_msg_data => l_msg_data);
708
709 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
710 Handle_Err
711 (p_itemtype => itemtype ,
712 p_itemkey => itemkey ,
713 p_msg_count => l_msg_count, -- Number of error Messages
714 p_msg_data => l_msg_data ,
715 p_attr_name => 'AMS_SCH_ERROR_MSG'
716 );
717
718 result := 'COMPLETE:ERROR' ;
719 RETURN;
720 END IF;
721
722 -- The calls added to calculate the time in User's timezone
723
724 AMS_Utility_PVT.Convert_Timezone(
725 p_init_msg_list => FND_API.G_FALSE,
726 x_return_status => l_return_status,
727 x_msg_count => l_msg_count,
728 x_msg_data => l_msg_data,
729
730 p_user_tz_id => l_trigger.timezone_id,
731 p_in_time => l_cur_date ,
732 p_convert_type => 'USER' ,
733
734 x_out_time => l_user_last_run_date_time
735 ) ;
736
737 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
738 Handle_Err
739 (p_itemtype => itemtype ,
740 p_itemkey => itemkey ,
741 p_msg_count => l_msg_count, -- Number of error Messages
742 p_msg_data => l_msg_data ,
743 p_attr_name => 'AMS_SCH_ERROR_MSG'
744 );
745
746 result := 'COMPLETE:ERROR' ;
747 RETURN;
748 END IF;
749
750 AMS_Utility_PVT.Convert_Timezone(
751 p_init_msg_list => FND_API.G_FALSE,
752 x_return_status => l_return_status,
753 x_msg_count => l_msg_count,
754 x_msg_data => l_msg_data,
755
756 p_user_tz_id => l_trigger.timezone_id,
757 p_in_time => l_sch_date ,
758 p_convert_type => 'USER' ,
759
760 x_out_time => l_user_next_run_date_time
761 );
762
763 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
764 Handle_Err
765 (p_itemtype => itemtype ,
766 p_itemkey => itemkey ,
767 p_msg_count => l_msg_count, -- Number of error Messages
768 p_msg_data => l_msg_data ,
769 p_attr_name => 'AMS_SCH_ERROR_MSG'
770 );
771
772 result := 'COMPLETE:ERROR' ;
773 RETURN;
774 END IF;
775
776 UPDATE ams_triggers
777 SET last_run_date_time = l_cur_date,
778 next_run_date_time = l_sch_date,
779 user_last_run_date_time = l_user_last_run_date_time,
780 user_next_run_date_time = l_user_next_run_date_time
781 WHERE trigger_id = l_trigger_id ;
782
783
784 -- End :anchaudh: 15 Oct'03 : uncommented the call to "Schedule_Next_Trigger_Run" and calling "Schedule_Repeat" instead.
785
786 -- dbms_OUTPUT.PUT_LINE('Next Scheduled Date is : '||to_char(l_sch_date,'DD-MON-RRRR:HH-MI-SS AM'));
787
788 --IF (l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
789 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
790 itemkey => itemkey ,
791 aname => 'AMS_TRIGGER_SCHEDULE_DATE',
792 avalue => to_char(l_sch_date,'DD-MON-RRRR HH:MI:SS AM'));
793
794 result := 'COMPLETE:SUCCESS' ;
795 /* ELSE
796 Handle_Err
797 (p_itemtype => itemtype ,
798 p_itemkey => itemkey ,
799 p_msg_count => l_msg_count, -- Number of error Messages
800 p_msg_data => l_msg_data ,
801 p_attr_name => 'AMS_SCH_ERROR_MSG'
802 ) ;
803
804 result := 'COMPLETE:ERROR' ;*/
805 --END IF ;
806
807 END IF;
808
809 IF (funcmode = 'CANCEL')
810 THEN
811 result := 'COMPLETE:' ;
812 RETURN;
813 END IF;
814
815 IF (funcmode = 'TIMEOUT')
816 THEN
817 result := 'COMPLETE:' ;
818 RETURN;
819 END IF;
820 EXCEPTION
821 WHEN OTHERS THEN
822 wf_core.context(G_PKG_NAME,'Schedule_Trig_Run',itemtype,itemkey,actid,funcmode);
823 raise ;
824
825 END Schedule_Trig_Run ;
826
827 -- Start of Comments
828 --
829 -- NAME
830 -- Check_Trigger_Status
831 --
832 -- PURPOSE
833 -- This Procedure will check whether the Trigger is Active or Expired
834 -- It will Return - Yes if the trigger is Active
835 -- - No If the trigger is Expired
836 --
837 -- IN
838 -- Itemtype - AMS_TRIG
839 -- Itemkey - Trigger ID
840 -- Accid - Activity ID
841 -- Funmode - Run/Cancel/Timeout
842 --
843 -- OUT
844 -- Result - 'COMPLETE:Y' If the trigger is Active
845 -- - 'COMPLETE:N' If the trigger is Expired
846 --
847 -- Used By Activities
848 -- Item Type - AMS_TRIG
849 -- Activity - AMS_CHECK_TRIGGER_STATUS
850 --
851 -- NOTES
852 --
853 --
854 -- HISTORY
855 -- 28-apr-2003 soagrawa Modified for retrieval of date thru database
856 --
857 -- End of Comments
858
859 PROCEDURE Check_Trigger_Status (itemtype IN VARCHAR2,
860 itemkey IN VARCHAR2,
861 actid IN NUMBER,
862 funcmode IN VARCHAR2,
863 result OUT NOCOPY VARCHAR2) IS
864
865 l_end_date DATE; -- VARCHAR2(30) ;
866 l_sch_date VARCHAR2(30) ;
867 l_trigger_id NUMBER;
868 itemuserkey VARCHAR2(80) ;
869
870 -- soagrawa 28-apr-2003 added this cursor to get end date of trigger
871 CURSOR c_trig_end_dt(p_trig_id NUMBER) IS
872 SELECT repeat_stop_date_time --to_char(repeat_stop_date_time,'DD-MM-RRRR HH:MI:SS AM')
873 FROM ams_triggers
874 WHERE trigger_id = p_trig_id;
875
876 BEGIN
877 -- dbms_output.put_line('Process Check_Trigger_Status');
878 -- RUN mode - Normal Process Execution
879 IF (funcmode = 'RUN') THEN
880
881 l_trigger_id := WF_ENGINE.GetItemAttrText(
882 itemtype => itemtype,
883 itemkey => itemkey ,
884 aname => 'AMS_TRIGGER_ID');
885
886 OPEN c_trig_end_dt(l_trigger_id);
887 FETCH c_trig_end_dt INTO l_end_date;
888 CLOSE c_trig_end_dt;
889
890 /*l_end_date := WF_ENGINE.GetItemAttrText(
891 itemtype => itemtype,
892 itemkey => itemkey ,
893 aname => 'AMS_TRIGGER_REPEAT_END_DATE');*/
894
895 /*l_sch_date := WF_ENGINE.GetItemAttrText(
896 itemtype => itemtype,
897 itemkey => itemkey ,
898 aname => 'AMS_TRIGGER_SCHEDULE_DATE');*/
899
900 IF (l_end_date IS NOT NULL) THEN
901 --cgoyal removed date formatting on 30/may/03 to fix GSCC errors
902 IF (sysdate < l_end_date) THEN
903 result := 'COMPLETE:Y' ;
904 ELSE
905 result := 'COMPLETE:N' ;
906 END IF ;
907 ELSE
908 result := 'COMPLETE:Y' ;
909 END IF;
910
911 END IF;
912
913 -- CANCEL mode - Normal Process Execution
914 IF (funcmode = 'CANCEL')
915 THEN
916 result := 'COMPLETE:' ;
917 RETURN;
918 END IF;
919
920 -- TIMEOUT mode - Normal Process Execution
921 IF (funcmode = 'TIMEOUT')
922 THEN
923 result := 'COMPLETE:' ;
924 RETURN;
925 END IF;
926 -- dbms_output.put_line('End Check Trigger stat :'||result);
927 EXCEPTION
928 WHEN OTHERS THEN
929 wf_core.context(G_PKG_NAME,'Check_Trigger_Status',itemtype,itemkey,actid,funcmode);
930 raise ;
931 END Check_Trigger_Status ;
932
933 -- Start of Comments
934 --
935 -- NAME
936 -- Perform_Check
937 --
938 -- PURPOSE
939 -- This Procedure will perform the check on standard item and Comparison Item
940 -- with the operator provided
941 -- It will Return - Success if the check is successful
942 -- - Failure If the check is not successful
943 -- - Error If there is an error in the Check Process
944 --
945 -- IN
946 -- Itemtype - AMS_TRIG
947 -- Itemkey - Trigger ID
948 -- Accid - Activity ID
949 -- Funmode - Run/Cancel/Timeout
950 --
951 -- OUT
952 -- Result - 'COMPLETE:SUCCESS' If the check is successful
953 -- - 'COMPLETE:FAILURE' If the check is Failure
954 -- - 'COMPLETE:ERROR' If there is an Error in the check Process
955 --
956 -- Used By Activities
957 -- Item Type - AMS_TRIG
958 -- Activity - AMS_PERFORM_CHECK
959 --
960 -- NOTES
961 --
962 -- HISTORY
963 -- 22-MAR-2001 julou Created
964 -- 28-APR-2003 soagrawa Modified
965 --
966 -- End of Comments
967
968 PROCEDURE Perform_check (itemtype IN VARCHAR2,
969 itemkey IN VARCHAR2,
970 actid IN NUMBER,
971 funcmode IN VARCHAR2,
972 result OUT NOCOPY VARCHAR2) IS
973 l_msg_count NUMBER ;
974 l_msg_data VARCHAR2(2000);
975 l_final_data VARCHAR2(4000);
976 l_msg_index NUMBER ;
977 l_cnt NUMBER := 0 ;
978 l_trigger_id NUMBER ;
979 l_return_status VARCHAR2(1);
980 l_chk_success NUMBER(1) ;
981 l_message VARCHAR2(1000);
982
983 l_metric_name VARCHAR2(120) ;
984 l_camp_name VARCHAR2(120) ;
985 l_operator VARCHAR2(30) ;
986 l_value NUMBER;
987 l_high_value NUMBER;
988 l_uom VARCHAR2(3) ;
989 l_currency VARCHAR2(15) ;
990 l_result_id NUMBER ;
991 -- l_errbuf VARCHAR2;
992 l_retcode NUMBER;
993
994 CURSOR c_check_det (p_trigger_id NUMBER) IS
995 SELECT chk1_type, chk1_source_code_metric_id,
996 chk2_type, chk2_source_code_metric_id
997 FROM ams_trigger_checks
998 WHERE trigger_id = p_trigger_id;
999
1000 l_chk1_type VARCHAR2(30);
1001 l_chk2_type VARCHAR2(30);
1002 l_chk1_metric_id NUMBER;
1003 l_chk2_metric_id NUMBER;
1004
1005 -- CURSOR c_metric_det(p_metric_id NUMBER) IS
1006 -- SELECT arc_act_metric_used_by, arc_metric_used_by_id
1007 -- FROM ams_act_metrics_all
1008 -- WHERE activity_metric_id = p_metric_id;
1009
1010 l_chk1_source_type VARCHAR2(30);
1011 l_chk2_source_type VARCHAR2(30);
1012 l_chk1_source_id NUMBER;
1013 l_chk2_source_id NUMBER;
1014
1015 BEGIN
1016 --result := 'COMPLETE:SUCCESS';
1017 -- dbms_output.put_line('Process Perform_check');
1018 -- RUN mode - Normal Process Execution
1019 IF (funcmode = 'RUN')
1020 THEN
1021
1022 FND_MSG_PUB.initialize;
1023 l_trigger_id := WF_ENGINE.GetItemAttrText(itemtype => itemtype,
1024 itemkey => itemkey ,
1025 aname => 'AMS_TRIGGER_ID'
1026 );
1027 /*
1028 OPEN c_check_det(l_trigger_id);
1029 FETCH c_check_det INTO l_chk1_type, l_chk1_metric_id,
1030 l_chk2_type, l_chk2_metric_id;
1031 CLOSE c_check_det;
1032
1033 IF l_chk1_type = 'METRIC'
1034 THEN
1035 OPEN c_metric_det(l_chk1_metric_id);
1036 FETCH c_metric_det INTO l_chk1_source_type, l_chk1_source_id;
1037 CLOSE c_metric_det;
1038
1039 ams_actmetrics_engine_pvt.Refresh_Act_Metrics_Engine
1040 (p_arc_act_metric_used_by => l_chk1_source_type,
1041 p_act_metric_used_by_id => l_chk1_source_id,
1042 x_return_status => l_return_status,
1043 x_msg_count => l_msg_count,
1044 x_msg_data => l_msg_data,
1045 p_commit => Fnd_Api.G_FALSE);
1046
1047 IF l_return_status <> FND_API.g_ret_sts_success THEN
1048 result := 'COMPLETE:FAILURE';
1049 return;
1050 END IF;
1051 END IF;
1052
1053 IF l_chk2_type = 'METRIC'
1054 THEN
1055 OPEN c_metric_det(l_chk2_metric_id);
1056 FETCH c_metric_det INTO l_chk2_source_type, l_chk2_source_id;
1057 CLOSE c_metric_det;
1058
1059 IF (l_chk1_source_type = l_chk2_source_type)
1060 AND (l_chk2_source_id = l_chk1_source_id)
1061 THEN
1062 NULL;
1063 ELSE
1064 ams_actmetrics_engine_pvt.Refresh_Act_Metrics_Engine
1065 (p_arc_act_metric_used_by => l_chk2_source_type,
1066 p_act_metric_used_by_id => l_chk2_source_id,
1067 x_return_status => l_return_status,
1068 x_msg_count => l_msg_count,
1069 x_msg_data => l_msg_data,
1070 p_commit => Fnd_Api.G_FALSE);
1071
1072 IF l_return_status <> FND_API.g_ret_sts_success THEN
1073 result := 'COMPLETE:FAILURE';
1074 return;
1075 END IF;
1076 END IF;
1077 END IF;
1078 */
1079 AMS_ContCampaign_PVT.Perform_Checks
1080 (p_api_version => 1.0 ,
1081 p_init_msg_list => FND_API.G_FALSE,
1082 x_return_status => l_return_status,
1083 x_msg_count => l_msg_count,
1084 x_msg_data => l_msg_data,
1085 p_trigger_id => l_trigger_id,
1086 x_chk_success => l_chk_success,
1087 x_check_val => l_value,
1088 x_check_high_val => l_high_value,
1089 x_result_id => l_result_id
1090 ) ;
1091
1092 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
1093 IF l_chk_success = '0' THEN
1094 result := 'COMPLETE:FAILURE' ;
1095 ELSIF l_chk_success = '1' THEN
1096 l_camp_name := WF_ENGINE.GetItemAttrText(
1097 itemtype => itemtype,
1098 itemkey => itemkey ,
1099 aname => 'AMS_CAMPAIGN_NAME');
1100
1101 l_operator := WF_ENGINE.GetItemAttrText(
1102 itemtype => itemtype,
1103 itemkey => itemkey ,
1104 aname => 'AMS_OPERATOR');
1105
1106 -- Create Message to Define the Check Condition met
1107 IF l_operator = 'BETWEEN' THEN
1108 FND_MESSAGE.Set_Name('AMS','AMS_TRIG_CHECK_BET_EXIST');
1109 FND_MESSAGE.Set_Token('CAMP_NAME', l_camp_name, FALSE);
1110 FND_MESSAGE.Set_Token('OPERATOR', l_operator, FALSE);
1111 FND_MESSAGE.Set_Token('VALUE', l_value, FALSE);
1112 FND_MESSAGE.Set_Token('HIGH_VALUE', l_high_value, FALSE);
1113 ELSE
1114 FND_MESSAGE.Set_Name('AMS','AMS_TRIG_CHECK_EXIST');
1115 FND_MESSAGE.Set_Token('CAMP_NAME', l_camp_name, FALSE);
1116 FND_MESSAGE.Set_Token('OPERATOR', l_operator, FALSE);
1117 FND_MESSAGE.Set_Token('VALUE', l_value, FALSE);
1118 END IF ;
1119
1120 l_message := FND_MESSAGE.Get;
1121
1122 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
1123 itemkey => itemkey,
1124 aname => 'AMS_NTF_MESSAGE',
1125 avalue => l_message);
1126
1127 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
1128 itemkey => itemkey,
1129 aname => 'AMS_TRIG_RESULT_ID',
1130 avalue => l_result_id);
1131
1132 result := 'COMPLETE:SUCCESS' ;
1133 END IF ;
1134 ELSE
1135 Handle_Err
1136 (p_itemtype => itemtype ,
1137 p_itemkey => itemkey ,
1138 p_msg_count => l_msg_count, -- Number of error Messages
1139 p_msg_data => l_msg_data ,
1140 p_attr_name => 'AMS_CHECK_ERROR_MSG'
1141 ) ;
1142 result := 'COMPLETE:ERROR' ;
1143 END IF ;
1144
1145 END IF;
1146
1147 -- CANCEL mode - Normal Process Execution
1148 IF (funcmode = 'CANCEL')
1149 THEN
1150 result := 'COMPLETE:' ;
1151 RETURN;
1152 END IF;
1153
1154 -- TIMEOUT mode - Normal Process Execution
1155 IF (funcmode = 'TIMEOUT')
1156 THEN
1157 result := 'COMPLETE:' ;
1158 RETURN;
1159 END IF;
1160 EXCEPTION
1161 WHEN OTHERS THEN
1162 wf_core.context(G_PKG_NAME,'Perform_check',itemtype,itemkey,actid,funcmode);
1163 raise ;
1164
1165 END Perform_check ;
1166
1167 -- Start of Comments
1168 --
1169 -- NAME
1170 -- Notify_Chk_Met
1171 --
1172 -- PURPOSE
1173 -- This Procedure will return Yes if Notification is required for the Trigger
1174 -- or it will return No if No Notification required
1175 --
1176 -- IN
1177 -- Itemtype - AMS_TRIG
1178 -- Itemkey - Trigger ID
1179 -- Accid - Activity ID
1180 -- Funmode - Run/Cancel/Timeout
1181 --
1182 -- OUT
1183 -- Result - 'COMPLETE:Y' If the notification is required
1184 -- - 'COMPLETE:N' If the notification is not required
1185 --
1186 -- Used By Activities
1187 -- Item Type - AMS_TRIG
1188 -- Activity - AMS_Notify_Chk_Met
1189 --
1190 -- NOTES
1191 --
1192 --
1193 -- HISTORY
1194 -- 22-MAR-2001 julou created
1195 -- End of Comments
1196
1197 PROCEDURE Notify_Chk_Met (itemtype IN VARCHAR2,
1198 itemkey IN VARCHAR2,
1199 actid IN NUMBER,
1200 funcmode IN VARCHAR2,
1201 result OUT NOCOPY VARCHAR2) IS
1202 l_notify_chk VARCHAR2(30) ;
1203 l_action_performed VARCHAR2(80);
1204 l_return_status VARCHAR2(1);
1205 l_trigger_id NUMBER;
1206 BEGIN
1207 IF (funcmode = 'RUN') THEN
1208
1209 l_notify_chk := WF_ENGINE.GetItemAttrText(
1210 itemtype => itemtype,
1211 itemkey => itemkey ,
1212 aname => 'AMS_NOTIF_TO_USER_NAME');
1213
1214 IF l_notify_chk = '' OR l_notify_chk IS NULL THEN
1215 result := 'COMPLETE:N' ;
1216 ELSE
1217 result := 'COMPLETE:Y' ;
1218 END IF ;
1219
1220 END IF;
1221
1222 IF (funcmode = 'CANCEL')
1223 THEN
1224 result := 'COMPLETE:' ;
1225 RETURN;
1226 END IF;
1227
1228 IF (funcmode = 'TIMEOUT')
1229 THEN
1230 result := 'COMPLETE:' ;
1231 RETURN;
1232 END IF;
1233 EXCEPTION
1234 WHEN OTHERS THEN
1235 wf_core.context(G_PKG_NAME,'Notify_Chk_Met',itemtype,itemkey,actid,funcmode);
1236 raise ;
1237
1238 END Notify_Chk_Met ;
1239
1240
1241 -- Start of Comments
1242 --
1243 -- NAME
1244 -- Require_Approval
1245 --
1246 -- PURPOSE
1247 -- This Procedure will return Y if there is an Approval is required for the List
1248 -- or it will return N if there is no Approval is required
1249 --
1250 -- IN
1251 -- Itemtype - AMS_TRIG
1252 -- Itemkey - Trigger ID
1253 -- Accid - Activity ID
1254 -- Funmode - Run/Cancel/Timeout
1255 --
1256 -- OUT
1257 -- Result - 'COMPLETE:Y' If the notification is required
1258 -- - 'COMPLETE:N' If the notification is not required
1259 --
1260 -- Used By Activities
1261 -- Item Type - AMS_TRIG
1262 -- Activity - AMS_REQUIRE_APPROVAL
1263 --
1264 -- NOTES
1265 --
1266 --
1267 -- HISTORY
1268 -- 22-MAR-2001 julou created
1269 -- End of Comments
1270
1271 PROCEDURE Require_Approval (itemtype IN VARCHAR2,
1272 itemkey IN VARCHAR2,
1273 actid IN NUMBER,
1274 funcmode IN VARCHAR2,
1275 result OUT NOCOPY VARCHAR2) IS
1276 l_req_approval VARCHAR2(30) ;
1277 BEGIN
1278 -- dbms_output.put_line('Process Require Approval');
1279 IF (funcmode = 'RUN')
1280 THEN
1281 l_req_approval := WF_ENGINE.GetItemAttrText(
1282 itemtype => itemtype,
1283 itemkey => itemkey ,
1284 aname => 'AMS_APPROVER_NAME');
1285
1286 IF l_req_approval = '' OR l_req_approval IS NULL THEN
1287 result := 'COMPLETE:N' ;
1288 ELSE
1289 result := 'COMPLETE:Y' ;
1290 END IF ;
1291
1292 END IF;
1293
1294 IF (funcmode = 'CANCEL')
1295 THEN
1296 result := 'COMPLETE:' ;
1297 RETURN;
1298 END IF;
1299
1300 IF (funcmode = 'TIMEOUT')
1301 THEN
1302 result := 'COMPLETE:' ;
1303 RETURN;
1304 END IF;
1305 EXCEPTION
1306 WHEN OTHERS THEN
1307 wf_core.context(G_PKG_NAME,'Require_Approval',itemtype,itemkey,actid,funcmode);
1308 raise ;
1309 END Require_Approval ;
1310
1311 -- Start of Comments
1312 --
1313 -- NAME
1314 -- Record_Result
1315 --
1316 -- PURPOSE
1317 -- This Procedure will record the the Actions taken
1318 -- when the trigger was filed
1319 --
1320 -- IN
1321 -- Itemtype - AMS_TRIG
1322 -- Itemkey - Trigger ID
1323 -- Accid - Activity ID
1324 -- Funmode - Run/Cancel/Timeout
1325 --
1326 -- OUT
1327 -- Result - 'COMPLETE:'
1328 --
1329 -- Used By Activities
1330 -- Item Type - AMS_TRIG
1331 -- Activity - AMS_RECORD_RESULT
1332 --
1333 -- NOTES
1334 --
1335 --
1336 -- HISTORY
1337 -- 22-MAR-2001 julou created
1338 -- End of Comments
1339
1340 PROCEDURE Record_Result (itemtype IN VARCHAR2,
1341 itemkey IN VARCHAR2,
1342 actid IN NUMBER,
1343 funcmode IN VARCHAR2,
1344 result OUT NOCOPY VARCHAR2) IS
1345 l_action_taken VARCHAR2(80);
1346 l_trigger_id NUMBER ;
1347 l_return_status VARCHAR2(1);
1348 l_process_id NUMBER ;
1349 BEGIN
1350 -- dbms_output.put_line('Process Record Result');
1351 -- RUN mode - Normal Process Execution
1352 IF (funcmode = 'RUN')
1353 THEN
1354 l_trigger_id := WF_ENGINE.GetItemAttrText(
1355 itemtype => itemtype,
1356 itemkey => itemkey ,
1357 aname => 'AMS_TRIGGER_ID');
1358 /*
1359 l_action_taken := WF_ENGINE.GetItemAttrText(
1360 itemtype => itemtype,
1361 itemkey => itemkey ,
1362 aname => 'AMS_ACTION_TAKEN');
1363 */
1364 l_process_id := WF_ENGINE.GetItemAttrText(
1365 itemtype => itemtype,
1366 itemkey => itemkey ,
1367 aname => 'AMS_TRIG_RESULT_ID');
1368
1369 l_action_taken := 'Trigger ' || l_trigger_id;
1370 l_process_id := l_trigger_id;
1371 -- Here the Pass the Result ID set by Check Process. So, If there is no check with
1372 -- this trigger(Process ID will be null) , create new row in Result table and Record Action
1373 -- If there is Check associated with trigger, modify the row created for check with Actions performed
1374
1375 AMS_ContCampaign_PVT.Record_Result (p_result_for_id => l_trigger_id,
1376 p_process_id => l_process_id,
1377 p_action_taken => l_action_taken,
1378 x_return_status => l_return_status,
1379 x_result_id => l_process_id
1380 );
1381
1382 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
1383 itemkey => itemkey,
1384 aname => 'AMS_TRIG_RESULT_ID',
1385 avalue => NULL
1386 );
1387 /*
1388 UPDATE ams_triggers
1389 SET process_id = null
1390 WHERE trigger_id = l_trigger_id;
1391 */
1392 result := 'COMPLETE:' ;
1393 END IF;
1394
1395 IF (funcmode = 'CANCEL')
1396 THEN
1397 result := 'COMPLETE:' ;
1398 RETURN;
1399 END IF;
1400
1401 IF (funcmode = 'TIMEOUT')
1402 THEN
1403 result := 'COMPLETE:' ;
1404 RETURN;
1405 END IF;
1406 EXCEPTION
1407 WHEN OTHERS THEN
1408 wf_core.context(G_PKG_NAME,'Record_Result',itemtype,itemkey,actid,funcmode);
1409 raise ;
1410 END Record_Result ;
1411
1412 -- Start of Comments
1413 --
1414 -- NAME
1415 -- Execute_Schedule
1416 --
1417 -- PURPOSE
1418 -- This Procedure will execute the schedule
1419 -- Action will be implemented later.
1420 --
1421 -- IN
1422 -- Itemtype - AMS_TRIG
1423 -- Itemkey - Trigger ID
1424 -- Accid - Activity ID
1425 -- Funmode - Run/Cancel/Timeout
1426 --
1427 -- OUT
1428 -- Result - 'COMPLETE:'
1429 --
1430 -- Used By Activities
1431 -- Item Type - AMS_TRIG
1432 -- Activity - EXECUTE_SCHEDULE
1433 --
1434 -- NOTES
1435 --
1436 --
1437 -- HISTORY
1438 -- 22-MAR-2001 julou created
1439 -- 06-APR-2001 julou change schedule id to null
1440 -- 25-nov-2002 soagrawa added generation of Target group before CSCH activation
1441 -- 25-aug-2003 soagrawa modified code to fix bug# 3111622
1442 -- 26-sep-2003 soagrawa modified code to replace execution of schedule API with WF Bus Event
1443 -- End of Comments
1444
1445 PROCEDURE Execute_Schedule (itemtype IN VARCHAR2,
1446 itemkey IN VARCHAR2,
1447 actid IN NUMBER,
1448 funcmode IN VARCHAR2,
1449 result OUT NOCOPY VARCHAR2) IS
1450
1451 l_return_status VARCHAR2(1);
1452 l_log_return_status VARCHAR2(1);
1453 l_msg_count NUMBER ;
1454 l_msg_data VARCHAR2(2000);
1455 l_sch_id NUMBER;
1456 l_tgrp_name VARCHAR2(240);
1457
1458 CURSOR c_det_tgrp_name (p_sch_id NUMBER) IS
1459 select list.list_name
1460 from ams_list_headers_vl list, ams_Act_lists act
1461 where list.list_header_id = act.list_header_id
1462 and act.list_used_by_id = p_sch_id
1463 and act.list_Act_Type = 'TARGET';
1464
1465 -- soagrawa added following code on 26-sep-2003 for replacing execute schedule with raising business event
1466 l_parameter_list WF_PARAMETER_LIST_T;
1467 l_new_item_key VARCHAR2(30);
1468 l_start_time DATE;
1469 l_sys_start_time DATE;
1470 l_timezone NUMBER;
1471
1472 CURSOR c_sch_det (p_schedule_id NUMBER) IS
1473 SELECT start_date_time, timezone_id
1474 FROM ams_campaign_schedules_b
1475 WHERE schedule_id = p_schedule_id;
1476 -- end soagrawa 26-sep-2003
1477
1478 BEGIN
1479 -- 06-APR-2001 julou Comment out schedule_id
1480 -- Pass the schedule id which is null by default for now.
1481
1482
1483
1484 l_sch_id := WF_ENGINE.GetItemAttrText(
1485 itemtype => itemtype,
1486 itemkey => itemkey ,
1487 aname => 'AMS_SCHEDULE_ID');
1488
1489 AMS_Utility_PVT.Create_Log (
1490 x_return_status => l_log_return_status,
1491 p_arc_log_used_by => 'CSCH',
1492 p_log_used_by_id => l_sch_id,
1493 p_msg_data => 'Activate Schedule through triggers: schedule id is '||l_sch_id,
1494 p_msg_type => 'DEBUG'
1495 );
1496
1497 l_tgrp_name := NULL;
1498
1499 OPEN c_det_tgrp_name(l_sch_id);
1500 FETCH c_det_tgrp_name INTO l_tgrp_name;
1501 CLOSE c_det_tgrp_name;
1502
1503 AMS_Utility_PVT.Create_Log (
1504 x_return_status => l_log_return_status,
1505 p_arc_log_used_by => 'CSCH',
1506 p_log_used_by_id => l_sch_id,
1507 p_msg_data => 'Target group name is '||l_tgrp_name,
1508 p_msg_type => 'DEBUG'
1509 );
1510
1511 AMS_Utility_PVT.Create_Log (
1512 x_return_status => l_log_return_status,
1513 p_arc_log_used_by => 'CSCH',
1514 p_log_used_by_id => l_sch_id,
1515 p_msg_data => 'Item type is '||itemtype,
1516 p_msg_type => 'DEBUG'
1517 );
1518
1519 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
1520 itemkey => itemkey ,
1521 aname => 'AMS_NEW_LIST_NAME',
1522 avalue => l_tgrp_name);
1523 IF (l_tgrp_name is not NULL) THEN
1524
1525 Ams_Utility_pvt.debug_message('Before generating tgrp');
1526 AMS_Utility_PVT.Create_Log (
1527 x_return_status => l_log_return_status,
1528 p_arc_log_used_by => 'CSCH',
1529 p_log_used_by_id => l_sch_id,
1530 p_msg_data => 'Before generating tgrp ' || itemkey ,
1531 p_msg_type => 'DEBUG'
1532 );
1533 Ams_Utility_pvt.debug_message('Schedule id: '||l_sch_id);
1534
1535 AMS_Act_List_PVT.generate_target_group_list_old
1536 ( p_api_version => 1.0,
1537 p_init_msg_list => FND_API.G_FALSE,
1538 p_commit => FND_API.G_FALSE,
1539 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1540 p_list_used_by => 'CSCH',
1541 p_list_used_by_id => l_sch_id,
1542 x_return_status => l_return_status,
1543 x_msg_count => l_msg_count,
1544 x_msg_data => l_msg_data
1545 ) ;
1546
1547 Ams_Utility_pvt.debug_message('return status from generate target group: '||l_return_status);
1548 AMS_Utility_PVT.Create_Log (
1549 x_return_status => l_log_return_status,
1550 p_arc_log_used_by => 'CSCH',
1551 p_log_used_by_id => l_sch_id,
1552 p_msg_data => 'return status from generate target group: ' || l_return_status ,
1553 p_msg_type => 'DEBUG'
1554 );
1555
1556 IF l_return_status = FND_API.g_ret_sts_success THEN
1557 result := 'COMPLETE:SUCCESS';
1558 ELSE
1559 result := 'COMPLETE:ERROR';
1560 /* WF_ENGINE.SetItemAttrText(itemtype => itemtype,
1561 itemkey => itemkey,
1562 aname => 'AMS_LIST_ERROR_MSG',
1563 avalue => l_msg_data);*/
1564 Handle_Err
1565 (p_itemtype => itemtype ,
1566 p_itemkey => itemkey ,
1567 p_msg_count => l_msg_count, -- Number of error Messages
1568 p_msg_data => l_msg_data ,
1569 p_attr_name => 'AMS_LIST_ERROR_MSG'
1570 ) ;
1571 RETURN;
1572 END IF;
1573 ELSE
1574 -- soagrawa 13-sep-2005 , added for when target group is not there
1575 result := 'COMPLETE:SUCCESS';
1576 END IF;
1577
1578 -- soagrawa 26-sep-2003 commented the activation code and replaced it with raising business event
1579 /*
1580 Ams_Utility_pvt.debug_message('Before activating csch');
1581 AMS_Utility_PVT.Create_Log (
1582 x_return_status => l_log_return_status,
1583 p_arc_log_used_by => 'CSCH',
1584 p_log_used_by_id => l_sch_id,
1585 p_msg_data => 'Before activating csch: ' ,
1586 p_msg_type => 'DEBUG'
1587 );
1588
1589 AMS_ScheduleRules_PVT.Activate_Schedule(p_api_version => 1.0
1590 ,p_init_msg_list => FND_API.G_FALSE
1591 ,p_commit => FND_API.G_False
1592 ,x_return_status => l_return_status
1593 ,x_msg_count => l_msg_count
1594 ,x_msg_data => l_msg_data
1595 ,p_schedule_id => l_sch_id
1596 );
1597 -- end of the change 06-APR-2001
1598 Ams_Utility_pvt.debug_message('return status from activate schedule: '||l_return_status);
1599
1600 AMS_Utility_PVT.Create_Log (
1601 x_return_status => l_log_return_status,
1602 p_arc_log_used_by => 'CSCH',
1603 p_log_used_by_id => l_sch_id,
1604 p_msg_data => 'return status from activate schedule: ' || l_return_status ,
1605 p_msg_type => 'DEBUG'
1606 );
1607 IF l_return_status = FND_API.g_ret_sts_success THEN
1608 result := 'COMPLETE:SUCCESS';
1609 ELSE
1610 result := 'COMPLETE:ERROR';
1611 --WF_ENGINE.SetItemAttrText(itemtype => itemtype,
1612 -- itemkey => itemkey,
1613 -- aname => 'AMS_LIST_ERROR_MSG',
1614 -- avalue => l_msg_data);
1615 Handle_Err
1616 (p_itemtype => itemtype ,
1617 p_itemkey => itemkey ,
1618 p_msg_count => l_msg_count, -- Number of error Messages
1619 p_msg_data => l_msg_data ,
1620 p_attr_name => 'AMS_LIST_ERROR_MSG'
1621 ) ;
1622 END IF;
1623 */
1624
1625 l_new_item_key := l_sch_id || TO_CHAR(SYSDATE,'DDMMRRRRHH24MISS');
1626 l_parameter_list := WF_PARAMETER_LIST_T();
1627 wf_event.AddParameterToList(p_name => 'SCHEDULE_ID',
1628 p_value => l_sch_id,
1629 p_parameterlist => l_parameter_list);
1630
1631 OPEN c_sch_det(l_sch_id);
1632 FETCH c_sch_det INTO l_start_time, l_timezone;
1633 CLOSE c_sch_det;
1634
1635 AMS_UTILITY_PVT.Convert_Timezone(
1636 p_init_msg_list => FND_API.G_TRUE,
1637 x_return_status => l_return_status,
1638 x_msg_count => l_msg_count,
1639 x_msg_data => l_msg_data,
1640
1641 p_user_tz_id => l_timezone,
1642 p_in_time => l_start_time,
1643 p_convert_type => 'SYS',
1644
1645 x_out_time => l_sys_start_time
1646 );
1647
1648 -- If any errors happen let start time be sysdate
1649 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1650 l_sys_start_time := SYSDATE;
1651 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1652 l_sys_start_time := SYSDATE;
1653 END IF;
1654
1655 --AMS_Utility_PVT.debug_message('Raise Business event for schedule execution');
1656 WF_EVENT.Raise
1657 ( p_event_name => 'oracle.apps.ams.campaign.ExecuteSchedule',
1658 p_event_key => l_new_item_key,
1659 p_parameters => l_parameter_list,
1660 p_send_date => l_sys_start_time);
1661
1662
1663 END Execute_Schedule;
1664
1665 -- Start of Comments
1666 --
1667 -- NAME
1668 -- Get_Aval_Sch
1669 --
1670 -- PURPOSE
1671 -- This Procedure will get the available schedule that is attached to
1672 -- the given trigger. Schedule id, notify flag and require approveal flag
1673 -- will be set based on the status_code, notify_user_id and approver_user_id,
1674 -- respectively.
1675 --
1676 -- IN
1677 -- Itemtype - AMS_TRIG
1678 -- Itemkey - Trigger ID
1679 -- Accid - Activity ID
1680 -- Funmode - Run/Cancel/Timeout
1681 --
1682 -- OUT
1683 -- Result - 'COMPLETE:'
1684 --
1685 -- Used By Activities
1686 -- Item Type - AMS_TRIG
1687 -- Activity - GET_AVAL_SCH
1688 --
1689 -- NOTES
1690 --
1691 --
1692 -- HISTORY
1693 -- 22-MAR-2001 julou created
1694 -- 28-apr-2003 soagrawa Modified for redesign
1695 --
1696 -- End of Comments
1697
1698 PROCEDURE Get_Aval_Sch(itemtype IN VARCHAR2,
1699 itemkey IN VARCHAR2,
1700 actid IN NUMBER,
1701 funcmode IN VARCHAR2,
1702 result OUT NOCOPY VARCHAR2)
1703 IS
1704
1705 l_trigger_id NUMBER;
1706 l_return_status VARCHAR2(1);
1707 l_return_log_status VARCHAR2(1);
1708 l_msg_count NUMBER ;
1709 l_msg_data VARCHAR2(2000);
1710 l_item_key VARCHAR2(30);
1711 l_approver VARCHAR2(30);
1712 l_notify VARCHAR2(30);
1713 l_owner VARCHAR2(30);
1714 l_display_name VARCHAR2(30);
1715
1716 CURSOR c_sch_det(l_trig_id NUMBER) IS
1717 SELECT schedule_id, notify_user_id, approver_user_id, owner_user_id, schedule_name
1718 FROM ams_campaign_schedules_vl
1719 WHERE trigger_id = l_trig_id
1720 AND status_code = 'AVAILABLE'
1721 -- soagrawa 28-apr-2003 added the following to this cursor
1722 AND (trig_repeat_flag IS NULL OR trig_repeat_flag = 'N');
1723
1724 BEGIN
1725
1726 IF (funcmode = 'RUN')
1727 THEN
1728 l_trigger_id := WF_ENGINE.GetItemAttrText(
1729 itemtype => itemtype,
1730 itemkey => itemkey ,
1731 aname => 'AMS_TRIGGER_ID');
1732
1733 AMS_Utility_PVT.Create_Log (
1734 x_return_status => l_return_log_status,
1735 p_arc_log_used_by => 'TRIG',
1736 p_log_used_by_id => l_trigger_id,
1737 p_msg_data => 'Get_Aval_Sch : 1. Started ' || to_char(sysdate,'DD-MON-RRRR HH:MI:SS AM') ,
1738 p_msg_type => 'DEBUG'
1739 );
1740
1741 -- dbms_output.put_line('master itemkey: ' || itemkey);
1742 -- dbms_output.put_line('trigger id: ' || l_trigger_id);
1743
1744 FOR l_sch_det_rec IN c_sch_det(l_trigger_id)
1745 LOOP
1746
1747 AMS_Utility_PVT.Create_Log (
1748 x_return_status => l_return_log_status,
1749 p_arc_log_used_by => 'TRIG',
1750 p_log_used_by_id => l_trigger_id,
1751 p_msg_data => 'Get_Aval_Sch : Looping for schedule id ' ||l_sch_det_rec.schedule_id ,
1752 p_msg_type => 'DEBUG'
1753 );
1754
1755 l_item_key := l_sch_det_rec.schedule_id || TO_CHAR(SYSDATE,'DDMMRRRRHH24MISS');
1756 -- dbms_output.put_line('detail itemkey: ' || l_item_key);
1757
1758
1759 --dbms_output.put_line('Creating process');
1760 WF_ENGINE.CreateProcess (itemtype => itemtype, --itemtype,
1761 itemkey => l_item_key ,
1762 user_key => l_sch_det_rec.schedule_name,
1763 process => 'AMS_EXEC_ATTCH_SCH'); -- name of the process
1764
1765 -- set schedule owner
1766 Get_User_Role(p_user_id => l_sch_det_rec.owner_user_id,
1767 x_role_name => l_owner,
1768 x_role_display_name => l_display_name,
1769 x_return_status => l_return_status);
1770 IF l_return_status <> FND_API.G_RET_STS_SUCCESS then
1771 RAISE FND_API.G_EXC_ERROR;
1772 END IF;
1773
1774 WF_ENGINE.SetItemAttrText(itemtype => itemtype
1775 ,itemkey => l_item_key
1776 ,aname => 'AMS_SCHEDULE_OWNER'
1777 ,avalue => l_owner);
1778
1779 WF_ENGINE.SetItemAttrText(itemtype => itemtype
1780 ,itemkey => l_item_key
1781 ,aname => 'AMS_REQUESTOR_USERNAME'
1782 ,avalue => l_owner);
1783
1784 -- set schedule id
1785 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
1786 itemkey => l_item_key,
1787 aname => 'AMS_SCHEDULE_ID',
1788 avalue => l_sch_det_rec.schedule_id);
1789
1790 -- set schedule name
1791 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
1792 itemkey => l_item_key,
1793 aname => 'AMS_SCHEDULE_NAME',
1794 avalue => l_sch_det_rec.schedule_name);
1795
1796 -- set notify user
1797 IF l_sch_det_rec.notify_user_id IS NOT NULL
1798 THEN
1799 Get_User_Role(p_user_id => l_sch_det_rec.notify_user_id,
1800 x_role_name => l_notify,
1801 x_role_display_name => l_display_name,
1802 x_return_status => l_return_status);
1803 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
1804 THEN
1805 RAISE FND_API.G_EXC_ERROR;
1806 END IF;
1807 WF_ENGINE.SetItemAttrText(itemtype => itemtype
1808 ,itemkey => l_item_key
1809 ,aname => 'AMS_NOTIF_TO_USER_NAME'
1810 ,avalue => l_notify);
1811 ELSE
1812 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
1813 itemkey => l_item_key,
1814 aname => 'AMS_NOTIF_TO_USER_NAME',
1815 avalue => '' );
1816 END IF;
1817
1818 -- set approver
1819 IF l_sch_det_rec.approver_user_id IS NOT NULL
1820 THEN
1821 Get_User_Role(p_user_id => l_sch_det_rec.approver_user_id,
1822 x_role_name => l_approver,
1823 x_role_display_name => l_display_name,
1824 x_return_status => l_return_status);
1825 IF l_return_status <> FND_API.G_RET_STS_SUCCESS then
1826 RAISE FND_API.G_EXC_ERROR;
1827 END IF;
1828 WF_ENGINE.SetItemAttrText(itemtype => itemtype
1829 ,itemkey => l_item_key
1830 ,aname => 'AMS_APPROVER_NAME'
1831 ,avalue => l_approver);
1832 ELSE
1833 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
1834 itemkey => l_item_key,
1835 aname => 'AMS_APPROVER_NAME',
1836 avalue => '' );
1837 END IF;
1838
1839 -- soagrawa added setting all the other values...
1840 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
1841 itemkey => l_item_key,
1842 aname => 'AMS_TRIGGER_ID',
1843 avalue => WF_ENGINE.getItemAttrText(itemtype => itemtype,
1844 itemkey => itemkey ,
1845 aname => 'AMS_TRIGGER_ID') );
1846
1847 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
1848 itemkey => l_item_key,
1849 aname => 'AMS_TRIGGER_NAME',
1850 avalue => WF_ENGINE.getItemAttrText(itemtype => itemtype,
1851 itemkey => itemkey ,
1852 aname => 'AMS_TRIGGER_NAME') );
1853
1854
1855 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
1856 itemkey => l_item_key,
1857 aname => 'AMS_TRIGGER_SCHEDULE_DATE',
1858 avalue => WF_ENGINE.getItemAttrText(itemtype => itemtype,
1859 itemkey => itemkey ,
1860 aname => 'AMS_TRIGGER_SCHEDULE_DATE') );
1861
1862
1863 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
1864 itemkey => l_item_key,
1865 aname => 'AMS_TRIGGER_DATE',
1866 avalue => WF_ENGINE.getItemAttrText(itemtype => itemtype,
1867 itemkey => itemkey ,
1868 aname => 'AMS_TRIGGER_DATE') );
1869
1870
1871 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
1872 itemkey => l_item_key,
1873 aname => 'AMS_REPEAT_FREQUENCY_TYPE',
1874 avalue => WF_ENGINE.getItemAttrText(itemtype => itemtype,
1875 itemkey => itemkey ,
1876 aname => 'AMS_REPEAT_FREQUENCY_TYPE') );
1877
1878
1879 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
1880 itemkey => l_item_key,
1881 aname => 'AMS_TRIGGER_REPEAT_END_DATE',
1882 avalue => WF_ENGINE.getItemAttrText(itemtype => itemtype,
1883 itemkey => itemkey ,
1884 aname => 'AMS_TRIGGER_REPEAT_END_DATE') );
1885
1886
1887 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
1888 itemkey => l_item_key,
1889 aname => 'AMS_CAMPAIGN_NAME',
1890 avalue => WF_ENGINE.getItemAttrText(itemtype => itemtype,
1891 itemkey => itemkey ,
1892 aname => 'AMS_CAMPAIGN_NAME') );
1893
1894 -- WF_ENGINE.SetItemAttrText(itemtype => itemtype,
1895 -- itemkey => l_item_key,
1896 -- aname => 'AMS_CHK_MET_MSG',
1897 -- avalue => WF_ENGINE.getItemAttrText(itemtype => itemtype,
1898 -- itemkey => itemkey ,
1899 -- aname => 'AMS_CHK_MET_MSG') );
1900
1901
1902 -- WF_ENGINE.SetItemAttrText(itemtype => itemtype,
1903 -- itemkey => l_item_key,
1904 -- aname => 'AMS_NTF_MESSAGE',
1905 -- avalue => WF_ENGINE.getItemAttrText(itemtype => itemtype,
1906 -- itemkey => itemkey ,
1907 -- aname => 'AMS_NTF_MESSAGE') );
1908
1909 -- end soagrawa setting values
1910
1911 -- set the parent process
1912 WF_ENGINE.SetItemParent(itemtype => itemtype
1913 ,itemkey => l_item_key
1914 ,parent_itemtype => itemtype
1915 ,parent_itemkey => itemkey
1916 ,parent_context => NULL
1917 );
1918
1919 WF_ENGINE.StartProcess (itemtype => itemtype,
1920 itemkey => l_item_key);
1921
1922 END LOOP;
1923 RETURN;
1924 END IF;
1925
1926 -- CANCEL mode - Normal Process Execution
1927 IF (funcmode = 'CANCEL')
1928 THEN
1929 RETURN;
1930 END IF;
1931
1932 -- TIMEOUT mode - Normal Process Execution
1933 IF (funcmode = 'TIMEOUT')
1934 THEN
1935 RETURN;
1936 END IF;
1937
1938 END Get_Aval_Sch;
1939
1940
1941 /*
1942 -- Start of Comments
1943 --
1944 -- NAME
1945 -- CHECK_ACTIVE_SCH
1946 --
1947 -- PURPOSE
1948 -- This Procedure will check if there are more active schedules available,
1949 -- by counting the number of active schedules.
1950 -- It will return 'COMPLETE:Y' if there are active schedules available
1951 -- and 'COMPLETE:N' if no active schedules.
1952 --
1953 -- IN
1954 -- Itemtype - AMS_TRIG
1955 -- Itemkey - Trigger ID
1956 -- Accid - Activity ID
1957 -- Funmode - Run/Cancel/Timeout
1958 --
1959 -- OUT
1960 -- Result - 'COMPLETE:'
1961 --
1962 -- Used By Activities
1963 -- Item Type - AMS_TRIG
1964 -- Activity - CHECK_ACTIVE_SCH
1965 --
1966 -- NOTES
1967 --
1968 --
1969 -- HISTORY
1970 -- 22-MAR-2001 julou created
1971 -- End of Comments
1972
1973 PROCEDURE Check_Active_Sch (itemtype IN VARCHAR2,
1974 itemkey IN VARCHAR2,
1975 actid IN NUMBER,
1976 funcmode IN VARCHAR2,
1977 result OUT NOCOPY VARCHAR2)
1978 IS
1979 CURSOR c_sch_count(l_trig_id NUMBER) IS
1980 SELECT count(1)
1981 FROM ams_campaign_schedules_vl
1982 WHERE trigger_id = l_trig_id
1983 AND status_code = 'AVAILABLE';
1984
1985
1986 -- l_status VARCHAR2(30);
1987 l_sch_count NUMBER;
1988 l_trigger_id NUMBER;
1989 -- l_notify_id NUMBER;
1990 -- l_approver_id NUMBER;
1991
1992 BEGIN
1993 l_trigger_id := WF_ENGINE.GetItemAttrText(
1994 itemtype => itemtype,
1995 itemkey => itemkey ,
1996 aname => 'AMS_TRIGGER_ID');
1997
1998 OPEN c_sch_count(l_trigger_id);
1999 FETCH c_sch_count INTO l_sch_count;
2000 CLOSE c_sch_count;
2001
2002 IF l_sch_count > 0 THEN
2003 result := 'COMPLETE:Y';
2004 ELSE
2005 result := 'COMPLETE:N';
2006 END IF;
2007 END Check_Active_Sch;
2008 */
2009
2010 -- Start of Comments
2011 -- NAME
2012 -- Get_User_Role
2013 --
2014 -- PURPOSE
2015 -- This procedure returns the User role for the userid sent
2016 -- Called By
2017 -- NOTES
2018 -- End of Comments
2019
2020 PROCEDURE Get_User_Role
2021 ( p_user_id IN NUMBER,
2022 x_role_name OUT NOCOPY VARCHAR2,
2023 x_role_display_name OUT NOCOPY VARCHAR2 ,
2024 x_return_status OUT NOCOPY VARCHAR2)
2025 IS
2026 CURSOR c_resource IS
2027 SELECT employee_id source_id
2028 FROM ams_jtf_rs_emp_v
2029 WHERE resource_id = p_user_id ;
2030 l_person_id number;
2031 BEGIN
2032 x_return_status := FND_API.G_RET_STS_SUCCESS;
2033 OPEN c_resource ;
2034 FETCH c_resource INTO l_person_id ;
2035 IF c_resource%NOTFOUND THEN
2036 x_return_status := FND_API.G_RET_STS_ERROR;
2037 FND_MESSAGE.Set_Name('AMS','AMS_APPR_INVALID_RESOURCE_ID');
2038 FND_MSG_PUB.Add;
2039 END IF;
2040 CLOSE c_resource ;
2041 -- Pass the Employee ID to get the Role
2042 WF_DIRECTORY.getrolename
2043 ( p_orig_system => 'PER',
2044 p_orig_system_id => l_person_id ,
2045 p_name => x_role_name,
2046 p_display_name => x_role_display_name );
2047 IF x_role_name is null then
2048 x_return_status := FND_API.G_RET_STS_ERROR;
2049 FND_MESSAGE.Set_Name('AMS','AMS_APPR_INVALID_ROLE');
2050 FND_MSG_PUB.Add;
2051 END IF;
2052 END Get_User_Role;
2053
2054 -- Start of Comments
2055 --
2056 -- NAME
2057 -- AbortProcess
2058 --
2059 -- PURPOSE
2060 -- This Procedure will abort the process of trigger
2061 --
2062
2063 --
2064 -- IN
2065 --
2066 -- Used By Activities
2067 --
2068 -- NOTES
2069 --
2070 --
2071 -- HISTORY
2072 -- 24-MAR-2001 julou created
2073 -- End of Comments
2074
2075 PROCEDURE AbortProcess
2076 (p_trigger_id IN NUMBER,
2077 p_itemtype IN VARCHAR2 DEFAULT NULL,
2078 p_workflow_process IN VARCHAR2 DEFAULT NULL )
2079 IS
2080 l_itemtype VARCHAR2(30) := nvl(p_itemtype,'AMS_TRIG');
2081 l_itemkey VARCHAR2(30) ;
2082 l_process VARCHAR2(30) := nvl(p_workflow_process,'AMS_TRIGGERS');
2083 CURSOR c_trig_det IS
2084 SELECT process_id
2085 FROM ams_triggers
2086 WHERE trigger_id = p_trigger_id ;
2087
2088 l_status VARCHAR2(30);
2089 l_result VARCHAR2(30);
2090
2091 BEGIN
2092 -- dbms_output.put_line('Abort Process');
2093 -- dbms_output.put_line('Item key : '||l_itemkey);
2094
2095 OPEN c_trig_det ;
2096 FETCH c_trig_det INTO l_itemkey;
2097 IF c_trig_det%FOUND THEN
2098
2099 WF_ENGINE.ItemStatus(itemtype => l_itemtype,
2100 itemkey => l_itemkey,
2101 status => l_status,
2102 result => l_result);
2103
2104 --dbms_output.put_line('Item key: ' || l_itemkey);
2105 --dbms_output.put_line('Status: ' || l_status);
2106 IF l_status <> 'COMPLETE' THEN
2107 WF_ENGINE.AbortProcess (itemtype => l_itemtype,
2108 itemkey => l_itemkey ,
2109 process => l_process);
2110 END IF;
2111 END IF;
2112 CLOSE c_trig_det ;
2113 -- dbms_output.put_line('After Aborting Process ');
2114 EXCEPTION
2115 WHEN OTHERS
2116 THEN
2117 wf_core.context(G_PKG_NAME,'AbortProcess',l_itemtype,l_itemkey);
2118 RAISE;
2119
2120 END AbortProcess;
2121
2122
2123 -- Start of Comments
2124 --
2125 -- NAME
2126 -- Trig_Type_Date
2127 --
2128 -- PURPOSE
2129 -- This Procedure will check whether the Trigger isof type date
2130 -- It will Return - Yes if the trigger is of type date
2131 -- - No Otherwise
2132 --
2133 -- IN
2134 -- Itemtype - AMS_TRIG
2135 -- Itemkey - Trigger ID
2136 -- Accid - Activity ID
2137 -- Funmode - Run/Cancel/Timeout
2138 --
2139 -- OUT
2140 -- Result - 'COMPLETE:Y' If the trigger is Active
2141 -- - 'COMPLETE:N' If the trigger is Expired
2142 --
2143 -- Used By Activities
2144 -- Item Type - AMS_TRIG
2145 -- Activity - AMS_CHECK_TRIG_TYPE
2146 --
2147 -- NOTES
2148 --
2149 --
2150 -- HISTORY
2151 -- 28-apr-2003 soagrawa Created
2152 --
2153 -- End of Comments
2154
2155 PROCEDURE Trig_Type_Date (itemtype IN VARCHAR2,
2156 itemkey IN VARCHAR2,
2157 actid IN NUMBER,
2158 funcmode IN VARCHAR2,
2159 result OUT NOCOPY VARCHAR2) IS
2160
2161 l_trig_type VARCHAR2(30);
2162 l_trigger_id NUMBER;
2163
2164 -- soagrawa 28-apr-2003 added this cursor to get type of trigger
2165 CURSOR c_trig_type(p_trig_id NUMBER) IS
2166 SELECT triggering_type
2167 FROM ams_triggers
2168 WHERE trigger_id = p_trig_id;
2169
2170 BEGIN
2171 -- dbms_output.put_line('Process Trig_Type_Date');
2172 -- RUN mode - Normal Process Execution
2173 IF (funcmode = 'RUN')
2174 THEN
2175 l_trigger_id := WF_ENGINE.GetItemAttrText(
2176 itemtype => itemtype,
2177 itemkey => itemkey ,
2178 aname => 'AMS_TRIGGER_ID');
2179
2180 OPEN c_trig_type(l_trigger_id);
2181 FETCH c_trig_type INTO l_trig_type;
2182 CLOSE c_trig_type;
2183
2184 IF l_trig_type = 'DATE'
2185 THEN
2186 result := 'COMPLETE:Y' ;
2187 ELSE
2188 result := 'COMPLETE:N' ;
2189 END IF;
2190 END IF;
2191
2192 -- CANCEL mode - Normal Process Execution
2193 IF (funcmode = 'CANCEL')
2194 THEN
2195 result := 'COMPLETE:' ;
2196 RETURN;
2197 END IF;
2198
2199 -- TIMEOUT mode - Normal Process Execution
2200 IF (funcmode = 'TIMEOUT')
2201 THEN
2202 result := 'COMPLETE:' ;
2203 RETURN;
2204 END IF;
2205 -- dbms_output.put_line('End Trig_Type_Date :'||result);
2206 EXCEPTION
2207 WHEN OTHERS THEN
2208 wf_core.context(G_PKG_NAME,'Trig_Type_Date',itemtype,itemkey,actid,funcmode);
2209 raise ;
2210 END Trig_Type_Date ;
2211
2212
2213 -- Start of Comments
2214 --
2215 -- NAME
2216 -- ACTION_NOTIFICATION
2217 --
2218 -- PURPOSE
2219 -- This Procedure will check whether the Trigger needs to send notification as one of its actions
2220 -- It will Return - Yes if the trigger needs to send notifications
2221 -- - No Otherwise
2222 --
2223 -- IN
2224 -- Itemtype - AMS_TRIG
2225 -- Itemkey - Trigger ID
2226 -- Accid - Activity ID
2227 -- Funmode - Run/Cancel/Timeout
2228 --
2229 -- OUT
2230 -- Result - 'COMPLETE:Y' If the trigger is Active
2231 -- - 'COMPLETE:N' If the trigger is Expired
2232 --
2233 -- Used By Activities
2234 -- Item Type - AMS_TRIG
2235 -- Activity - AMS_ACTION_NOTIFICATION
2236 --
2237 -- NOTES
2238 --
2239 --
2240 -- HISTORY
2241 -- 28-apr-2003 soagrawa Created
2242 --
2243 -- End of Comments
2244
2245 PROCEDURE Action_Notification (itemtype IN VARCHAR2,
2246 itemkey IN VARCHAR2,
2247 actid IN NUMBER,
2248 funcmode IN VARCHAR2,
2249 result OUT NOCOPY VARCHAR2) IS
2250
2251 l_notify_flag VARCHAR2(1);
2252 l_trigger_id NUMBER;
2253
2254 -- soagrawa 28-apr-2003 added this cursor to get end date of trigger
2255 CURSOR c_trig_notify (p_trig_id NUMBER) IS
2256 SELECT notify_flag
2257 FROM ams_triggers
2258 WHERE trigger_id = p_trig_id;
2259
2260 CURSOR c_notify_action_det (p_trig_id NUMBER) IS
2261 SELECT *
2262 FROM ams_trigger_actions
2263 WHERE trigger_id = p_trig_id
2264 AND execute_action_type = 'NOTIFY';
2265
2266 l_notify_action_det c_notify_action_det%ROWTYPE;
2267 l_display_name VARCHAR2(100);
2268 l_return_status VARCHAR2(1);
2269 l_notify VARCHAR2(30);
2270
2271
2272
2273 BEGIN
2274 -- dbms_output.put_line('Process Trig_Type_Date');
2275 -- RUN mode - Normal Process Execution
2276 IF (funcmode = 'RUN')
2277 THEN
2278
2279 -- soagrawa 28-apr-2003 modified retrieval of end date via database as
2280 -- end date can now be changed even if workflow process is there for the trigger
2281 l_trigger_id := WF_ENGINE.GetItemAttrText(
2282 itemtype => itemtype,
2283 itemkey => itemkey ,
2284 aname => 'AMS_TRIGGER_ID');
2285
2286 AMS_Utility_PVT.Create_Log (
2287 x_return_status => l_return_status,
2288 p_arc_log_used_by => 'TRIG',
2289 p_log_used_by_id => l_trigger_id,
2290 p_msg_data => 'Action_Notification : trigger in RUN mode' ,
2291 p_msg_type => 'DEBUG'
2292 );
2293
2294 OPEN c_trig_notify(l_trigger_id);
2295 FETCH c_trig_notify INTO l_notify_flag;
2296 CLOSE c_trig_notify;
2297
2298 AMS_Utility_PVT.Create_Log (
2299 x_return_status => l_return_status,
2300 p_arc_log_used_by => 'TRIG',
2301 p_log_used_by_id => l_trigger_id,
2302 p_msg_data => 'Action_Notification : notification flag is ' ||l_notify_flag,
2303 p_msg_type => 'DEBUG'
2304 );
2305
2306 IF l_notify_flag = 'Y'
2307 THEN
2308 OPEN c_notify_action_det(l_trigger_id);
2309 FETCH c_notify_action_det INTO l_notify_action_det;
2310 IF c_notify_action_det%FOUND
2311 THEN
2312 -- set notify user
2313 IF l_notify_action_det.action_for_id IS NOT NULL
2314 THEN
2315 Get_User_Role(p_user_id => l_notify_action_det.action_for_id,
2316 x_role_name => l_notify,
2317 x_role_display_name => l_display_name,
2318 x_return_status => l_return_status);
2319 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
2320 THEN
2321 RAISE FND_API.G_EXC_ERROR;
2322 END IF;
2323
2324 WF_ENGINE.SetItemAttrText(itemtype => itemtype
2325 ,itemkey => itemkey
2326 ,aname => 'AMS_TRIG_NOTIFTIER'
2327 ,avalue => l_notify);
2328 result := 'COMPLETE:Y' ;
2329 ELSE
2330 WF_ENGINE.SetItemAttrText(itemtype => itemtype
2331 ,itemkey => itemkey
2332 ,aname => 'AMS_TRIG_NOTIFTIER'
2333 ,avalue => '');
2334 result := 'COMPLETE:N' ;
2335 END IF;
2336
2337 AMS_Utility_PVT.Create_Log (
2338 x_return_status => l_return_status,
2339 p_arc_log_used_by => 'TRIG',
2340 p_log_used_by_id => l_trigger_id,
2341 p_msg_data => 'Action_notification : AMS_NOTIF_TO_USER_NAME = ' || l_notify || ', '||l_display_name,
2342 p_msg_type => 'DEBUG'
2343 );
2344
2345 END IF;
2346 CLOSE c_notify_action_det;
2347 --cgoyal 26 May 03, fixed else condition
2348 ELSE
2349 result := 'COMPLETE:N' ;
2350 END IF;
2351
2352 END IF;
2353
2354 -- CANCEL mode - Normal Process Execution
2355 IF (funcmode = 'CANCEL')
2356 THEN
2357 result := 'COMPLETE:' ;
2358 RETURN;
2359 END IF;
2360
2361 -- TIMEOUT mode - Normal Process Execution
2362 IF (funcmode = 'TIMEOUT')
2363 THEN
2364 result := 'COMPLETE:' ;
2365 RETURN;
2366 END IF;
2367 -- dbms_output.put_line('End Trig_Type_Date :'||result);
2368 EXCEPTION
2369 WHEN OTHERS THEN
2370 wf_core.context(G_PKG_NAME,'ACTION_NOTIFICATION',itemtype,itemkey,actid,funcmode);
2371 raise ;
2372 END Action_Notification ;
2373
2374
2375 -- Start of Comments
2376 --
2377 -- NAME
2378 -- Action_Execute
2379 --
2380 -- PURPOSE
2381 -- This Procedure will check whether the Trigger needs to send notification as one of its actions
2382 -- It will Return - Yes if the trigger needs to send notifications
2383 -- - No Otherwise
2384 --
2385 -- IN
2386 -- Itemtype - AMS_TRIG
2387 -- Itemkey - Trigger ID
2388 -- Accid - Activity ID
2389 -- Funmode - Run/Cancel/Timeout
2390 --
2391 -- OUT
2392 -- Result - 'COMPLETE:Y' If the trigger is Active
2393 -- - 'COMPLETE:N' If the trigger is Expired
2394 --
2395 -- Used By Activities
2396 -- Item Type - AMS_TRIG
2397 -- Activity - AMS_ACTION_EX_CSCH
2398 --
2399 -- NOTES
2400 --
2401 --
2402 -- HISTORY
2403 -- 28-apr-2003 soagrawa Created
2404 --
2405 -- End of Comments
2406
2407 PROCEDURE Action_Execute (itemtype IN VARCHAR2,
2408 itemkey IN VARCHAR2,
2409 actid IN NUMBER,
2410 funcmode IN VARCHAR2,
2411 result OUT NOCOPY VARCHAR2) IS
2412
2413 l_exec_csch_flag VARCHAR2(1);
2414 l_trigger_id NUMBER;
2415
2416 -- soagrawa 28-apr-2003 added this cursor to get end date of trigger
2417 CURSOR c_trig_exec_csch (p_trig_id NUMBER) IS
2418 SELECT execute_schedule_flag
2419 FROM ams_triggers
2420 WHERE trigger_id = p_trig_id;
2421
2422 BEGIN
2423 -- dbms_output.put_line('Process Trig_Type_Date');
2424 -- RUN mode - Normal Process Execution
2425 IF (funcmode = 'RUN')
2426 THEN
2427
2428 -- soagrawa 28-apr-2003 modified retrieval of end date via database as
2429 -- end date can now be changed even if workflow process is there for the trigger
2430 l_trigger_id := WF_ENGINE.GetItemAttrText(
2431 itemtype => itemtype,
2432 itemkey => itemkey ,
2433 aname => 'AMS_TRIGGER_ID');
2434
2435 OPEN c_trig_exec_csch(l_trigger_id);
2436 FETCH c_trig_exec_csch INTO l_exec_csch_flag;
2437 CLOSE c_trig_exec_csch;
2438
2439 IF l_exec_csch_flag = 'Y'
2440 THEN
2441 result := 'COMPLETE:Y' ;
2442 ELSE
2443 result := 'COMPLETE:N' ;
2444 END IF;
2445 END IF;
2446
2447 -- CANCEL mode - Normal Process Execution
2448 IF (funcmode = 'CANCEL')
2449 THEN
2450 result := 'COMPLETE:' ;
2451 RETURN;
2452 END IF;
2453
2454 -- TIMEOUT mode - Normal Process Execution
2455 IF (funcmode = 'TIMEOUT')
2456 THEN
2457 result := 'COMPLETE:' ;
2458 RETURN;
2459 END IF;
2460 -- dbms_output.put_line('End Trig_Type_Date :'||result);
2461 EXCEPTION
2462 WHEN OTHERS THEN
2463 wf_core.context(G_PKG_NAME,'ACTION_NOTIFICATION',itemtype,itemkey,actid,funcmode);
2464 raise ;
2465 END Action_Execute ;
2466
2467
2468 -- Start of Comments
2469 --
2470 -- NAME
2471 -- Get_Aval_Repeat_Sch
2472 --
2473 -- PURPOSE
2474 -- This Procedure will get the available/active repeat-execute schedules that are attached to
2475 -- the given trigger. Schedule id, notify flag and require approveal flag
2476 -- will be set based on the status_code, notify_user_id and approver_user_id,
2477 -- respectively.
2478 --
2479 -- IN
2480 -- Itemtype - AMS_TRIG
2481 -- Itemkey - Trigger ID
2482 -- Accid - Activity ID
2483 -- Funmode - Run/Cancel/Timeout
2484 --
2485 -- OUT
2486 -- Result - 'COMPLETE:'
2487 --
2488 -- Used By Activities
2489 -- Item Type - AMS_TRIG
2490 -- Activity - AMS_GENERATE_EXEC_CSCH
2491 --
2492 -- NOTES
2493 --
2494 --
2495 -- HISTORY
2496 -- 28-apr-2003 soagrawa Created
2497 -- 18-feb-2004 soagrawa Fixed bug# 3452264
2498 -- 13-may-2004 soagrawa Fixed bug# 3621786
2499
2500 -- End of Comments
2501
2502 PROCEDURE Get_Aval_Repeat_Sch(itemtype IN VARCHAR2,
2503 itemkey IN VARCHAR2,
2504 actid IN NUMBER,
2505 funcmode IN VARCHAR2,
2506 result OUT NOCOPY VARCHAR2)
2507 IS
2508
2509 l_trigger_id NUMBER;
2510 l_return_status VARCHAR2(1);
2511 l_msg_count NUMBER ;
2512 l_msg_data VARCHAR2(2000);
2513 l_item_key VARCHAR2(30);
2514 l_approver VARCHAR2(30);
2515 l_notify VARCHAR2(30);
2516 l_owner VARCHAR2(30);
2517 l_display_name VARCHAR2(30);
2518
2519 l_child_sched_st_date DATE;
2520 l_child_sched_start_date DATE;
2521 l_child_sched_end_date_time DATE;
2522
2523 --cgoyal added on 6/may/03
2524 l_trigger_sched_date DATE;
2525 l_trigger_end_date DATE;
2526 l_new_sched_id NUMBER := NULL;
2527 l_schedule_id NUMBER;
2528 l_schedule_rec AMS_Camp_Schedule_PVT.schedule_rec_type;
2529 /*
2530 CURSOR c_sched_seq IS
2531 SELECT ams_campaign_schedules_s.NEXTVAL
2532 FROM dual;
2533 */
2534 CURSOR c_sched_rec (l_sched_id IN NUMBER) IS
2535 SELECT *
2536 from ams_campaign_schedules_vl
2537 where schedule_id = l_sched_id;
2538
2539 l_csch_rec c_sched_rec%ROWTYPE;
2540 --end additions by cgoyal on 6/may/03
2541
2542 CURSOR c_sch_det(p_schedule_id NUMBER) IS
2543 SELECT schedule_id, notify_user_id, approver_user_id, owner_user_id, schedule_name
2544 FROM ams_campaign_schedules_vl
2545 WHERE schedule_id = p_schedule_id;
2546
2547 l_sch_det_rec c_sch_det%ROWTYPE;
2548
2549 CURSOR c_sch_all_det(l_trig_id NUMBER) IS
2550 select distinct nvl(orig_csch_id, schedule_id) AS id, count(*) AS total
2551 from ams_Campaign_schedules_b
2552 where trigger_id = l_trig_id
2553 and status_code = 'AVAILABLE'
2554 and trig_repeat_flag = 'Y'
2555 group by nvl(orig_csch_id, schedule_id);
2556
2557 l_sch_all_det_rec c_sch_all_det%ROWTYPE;
2558
2559 CURSOR c_max_sch_det(p_schedule_id NUMBER) IS
2560 select schedule_id
2561 from ams_campaign_schedules_b
2562 where creation_date = (select max(creation_date)
2563 from ams_campaign_schedules_b
2564 where nvl(orig_csch_id, schedule_id) = p_schedule_id);
2565
2566 -- 18-feb-2004 soagrawa Fixed bug# 3452264
2567 CURSOR c_orig_csch_name(p_Schedule_id NUMBER) IS
2568 select schedule_name
2569 from ams_Campaign_schedules_vl
2570 where schedule_id = ( select nvl(orig_csch_id, schedule_id)
2571 From ams_campaign_Schedules_b
2572 where schedule_id = p_schedule_id);
2573
2574 l_new_cover_letter_id NUMBER;
2575 l_ci_base_lang VARCHAR2(4);
2576 l_ci_version NUMBER;
2577 l_ci_obj_ver_num NUMBER;
2578 l_ver_obj_ver_num NUMBER;
2579 l_ci_id NUMBER;
2580 l_html_file_id NUMBER;
2581 l_text_file_id NUMBER;
2582 l_new_html_file_id NUMBER;
2583 l_new_text_file_id NUMBER;
2584 l_ci_ver_name_det VARCHAR2(240);
2585 l_new_ci_ver_name_det VARCHAR2(240);
2586 l_date_suffix VARCHAR2(25);
2587 l_orig_schedule_name VARCHAR2(240); -- 18-feb-2004 soagrawa Fixed bug# 3452264
2588
2589 l_def_avail_status NUMBER;
2590 l_def_new_status NUMBER;
2591
2592 l_file_name VARCHAR2(256);
2593 l_file_content_type VARCHAR2(256);
2594 l_file_data BLOB;
2595 l_text_file_data BLOB;
2596 l_html_file_data BLOB;
2597 l_program_name VARCHAR2(32);
2598 l_program_tag VARCHAR2(32);
2599 l_file_format VARCHAR2(10);
2600 l_file_language VARCHAR2(4);
2601 l_file_charset VARCHAR2(30);
2602
2603 l_attribute_type_codes JTF_VARCHAR2_TABLE_100 := JTF_VARCHAR2_TABLE_100() ;
2604 l_attribute_type_names JTF_VARCHAR2_TABLE_300 := JTF_VARCHAR2_TABLE_300() ;
2605 l_attributes JTF_VARCHAR2_TABLE_4000 := JTF_VARCHAR2_TABLE_4000() ;
2606 l_schedules_to_update JTF_NUMBER_TABLE := JTF_NUMBER_TABLE() ;
2607
2608
2609 CURSOR c_trigger_det (p_trigger_id NUMBER) IS
2610 SELECT repeat_frequency_type, repeat_every_x_frequency
2611 FROM ams_Triggers
2612 WHERE trigger_id = p_trigger_id;
2613
2614
2615 CURSOR c_camp_det (p_campaign_id IN NUMBER) IS
2616 SELECT actual_exec_end_date
2617 FROM ams_campaigns_all_b
2618 WHERE campaign_id = p_campaign_id ;
2619
2620 l_camp_end_dt DATE;
2621 l_dummy NUMBER;
2622 l_trig_freq_type VARCHAR2(30);
2623 l_trig_freq_every_x NUMBER;
2624 l_repeat_check VARCHAR2(30);
2625 l_csch_count NUMBER := 1;
2626 l_failed NUMBER := 0;
2627 l_query_id VARcHAR2(30);
2628 l_query_id_num NUMBER;
2629
2630 l_return_log_status VARCHAR2(1);
2631
2632 BEGIN
2633 IF (funcmode = 'RUN')
2634 THEN
2635 l_trigger_id := WF_ENGINE.GetItemAttrText(
2636 itemtype => itemtype,
2637 itemkey => itemkey ,
2638 aname => 'AMS_TRIGGER_ID');
2639
2640 AMS_Utility_PVT.Create_Log (
2641 x_return_status => l_return_log_status,
2642 p_arc_log_used_by => 'TRIG',
2643 p_log_used_by_id => l_trigger_id,
2644 p_msg_data => 'Get_Aval_Repeat_Sch : 1. Started '|| to_char(sysdate,'DD-MON-RRRR HH:MI:SS AM') ,
2645 p_msg_type => 'DEBUG'
2646 );
2647
2648 l_repeat_check := WF_ENGINE.GetItemAttrText(itemtype => itemtype,
2649 itemkey => itemkey ,
2650 aname => 'AMS_REPEAT_FREQUENCY_TYPE'
2651 );
2652 IF l_repeat_check <> 'NONE' THEN
2653
2654 --cgoyal - added following code on 06/may/03 for new instance creation for repeat schedules
2655 l_trigger_sched_date := WF_ENGINE.getItemAttrDate(itemtype => itemtype,
2656 itemkey => itemkey ,
2657 aname => 'AMS_TRIGGER_SCHEDULE_DATE') ;
2658
2659 OPEN c_trigger_det(l_trigger_id);
2660 FETCH c_trigger_det INTO l_trig_freq_type, l_trig_freq_every_x;
2661 CLOSE c_trigger_det;
2662
2663 /* IF l_trig_freq_type = 'DAILY' THEN
2664 l_trigger_sched_date := l_trigger_sched_date + l_trig_freq_every_x ;
2665 ELSIF l_trig_freq_type = 'WEEKLY' THEN
2666 l_trigger_sched_date := l_trigger_sched_date + (7 * l_trig_freq_every_x) ;
2667 ELSIF l_trig_freq_type = 'MONTHLY' THEN
2668 l_trigger_sched_date := add_months(l_trigger_sched_date , l_trig_freq_every_x) ;
2669 ELSIF l_trig_freq_type = 'YEARLY' THEN
2670 l_trigger_sched_date := add_months(l_trigger_sched_date , (12*l_trig_freq_every_x)) ;
2671 ElSIF l_trig_freq_type = 'HOURLY' THEN
2672 l_trigger_sched_date := l_trigger_sched_date + (l_trig_freq_every_x/24) ;
2673 END IF;*/
2674
2675 -- Start: anchaudh: commented out the above portion and instead calling api below.
2676
2677 AMS_SCHEDULER_PVT.Schedule_Repeat( p_last_run_date => l_trigger_sched_date,
2678 p_frequency => l_trig_freq_every_x,
2679 p_frequency_type => l_trig_freq_type,
2680 x_next_run_date => l_child_sched_start_date,
2681 x_return_status => l_return_status,
2682 x_msg_count => l_msg_count,
2683 x_msg_data => l_msg_data);
2684
2685 IF l_return_status <> FND_API.g_ret_sts_success THEN
2686 l_failed := l_failed + 1;
2687 result := 'COMPLETE:ERROR';
2688 END IF;
2689
2690 l_trigger_sched_date := l_child_sched_start_date;
2691
2692 -- End: anchaudh: commented out the above portion and instead calling api below.
2693
2694
2695 l_trigger_end_date := WF_ENGINE.getItemAttrDate(itemtype => itemtype,
2696 itemkey => itemkey ,
2697 aname => 'AMS_TRIGGER_REPEAT_END_DATE');
2698
2699 AMS_Utility_PVT.Create_Log (
2700 x_return_status => l_return_log_status,
2701 p_arc_log_used_by => 'TRIG',
2702 p_log_used_by_id => l_trigger_id,
2703 p_msg_data => 'Get_Aval_Repeat_Sch : Trigger Sched Date is '|| to_char(l_trigger_sched_date,'DD-MON-RRRR HH:MI:SS AM') || 'Trigger End Date is '|| to_char(l_trigger_end_date,'DD-MON-RRRR HH:MI:SS AM'),
2704 p_msg_type => 'DEBUG'
2705 );
2706
2707 END IF;
2708
2709 FOR l_sch_all_det_rec IN c_sch_all_det(l_trigger_id)
2710 LOOP
2711 -- pick the latest available one
2712 OPEN c_max_sch_det(l_sch_all_det_rec.id);
2713 FETCH c_max_sch_det INTO l_schedule_id;
2714 CLOSE c_max_sch_det;
2715
2716 OPEN c_sch_det(l_schedule_id);
2717 FETCH c_sch_det INTO l_sch_det_rec;
2718 CLOSE c_sch_det;
2719
2720 AMS_Utility_PVT.Create_Log (
2721 x_return_status => l_return_log_status,
2722 p_arc_log_used_by => 'TRIG',
2723 p_log_used_by_id => l_trigger_id,
2724 p_msg_data => 'Get_Aval_Repeat_Sch : 2.Processing for schedule_id '|| l_sch_det_rec.schedule_id,
2725 p_msg_type => 'DEBUG'
2726 );
2727
2728 l_item_key := l_sch_det_rec.schedule_id || TO_CHAR(SYSDATE,'DDMMRRRRHH24MISS');
2729
2730 AMS_Utility_PVT.Create_Log (
2731 x_return_status => l_return_log_status,
2732 p_arc_log_used_by => 'TRIG',
2733 p_log_used_by_id => l_trigger_id,
2734 p_msg_data => 'Get_Aval_Repeat_Sch : 3.detail itemkey: '|| l_item_key,
2735 p_msg_type => 'DEBUG'
2736 );
2737
2738 AMS_Utility_PVT.Create_Log (
2739 x_return_status => l_return_log_status,
2740 p_arc_log_used_by => 'TRIG',
2741 p_log_used_by_id => l_trigger_id,
2742 p_msg_data => 'Get_Aval_Repeat_Sch : 4.Creating Process',
2743 p_msg_type => 'DEBUG'
2744 );
2745
2746 WF_ENGINE.CreateProcess (itemtype => itemtype, --itemtype,
2747 itemkey => l_item_key ,
2748 user_key => l_sch_det_rec.schedule_name,
2749 process => 'AMS_EXEC_ATTCH_SCH'); -- name of the process
2750
2751 AMS_Utility_PVT.Create_Log (
2752 x_return_status => l_return_log_status,
2753 p_arc_log_used_by => 'TRIG',
2754 p_log_used_by_id => l_trigger_id,
2755 p_msg_data => 'Get_Aval_Repeat_Sch : 5.Created Process',
2756 p_msg_type => 'DEBUG'
2757 );
2758
2759 -- set schedule owner
2760 Get_User_Role(p_user_id => l_sch_det_rec.owner_user_id,
2761 x_role_name => l_owner,
2762 x_role_display_name => l_display_name,
2763 x_return_status => l_return_status);
2764 IF l_return_status <> FND_API.g_ret_sts_success THEN
2765 result := 'COMPLETE:ERROR';
2766 RETURN;
2767 END IF;
2768
2769 AMS_Utility_PVT.Create_Log (
2770 x_return_status => l_return_log_status,
2771 p_arc_log_used_by => 'TRIG',
2772 p_log_used_by_id => l_trigger_id,
2773 p_msg_data => 'Get_Aval_Repeat_Sch : Setting WF Engine params',
2774 p_msg_type => 'DEBUG'
2775 );
2776 WF_ENGINE.SetItemAttrText(itemtype => itemtype
2777 ,itemkey => l_item_key
2778 ,aname => 'AMS_SCHEDULE_OWNER'
2779 ,avalue => l_owner);
2780
2781 WF_ENGINE.SetItemAttrText(itemtype => itemtype
2782 ,itemkey => l_item_key
2783 ,aname => 'AMS_REQUESTOR_USERNAME'
2784 ,avalue => l_owner);
2785
2786
2787 -- set schedule id
2788 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
2789 itemkey => l_item_key,
2790 aname => 'AMS_SCHEDULE_ID',
2791 avalue => l_sch_det_rec.schedule_id);
2792
2793 -- set schedule name
2794 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
2795 itemkey => l_item_key,
2796 aname => 'AMS_SCHEDULE_NAME',
2797 avalue => l_sch_det_rec.schedule_name);
2798
2799 -- set notify user
2800 IF l_sch_det_rec.notify_user_id IS NOT NULL
2801 THEN
2802 Get_User_Role(p_user_id => l_sch_det_rec.notify_user_id,
2803 x_role_name => l_notify,
2804 x_role_display_name => l_display_name,
2805 x_return_status => l_return_status);
2806 IF l_return_status <> FND_API.g_ret_sts_success THEN
2807 result := 'COMPLETE:ERROR';
2808 RETURN;
2809 END IF;
2810
2811 WF_ENGINE.SetItemAttrText(itemtype => itemtype
2812 ,itemkey => l_item_key
2813 ,aname => 'AMS_NOTIF_TO_USER_NAME'
2814 ,avalue => l_notify);
2815 ELSE
2816 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
2817 itemkey => l_item_key,
2818 aname => 'AMS_NOTIF_TO_USER_NAME',
2819 avalue => '' );
2820 END IF;
2821
2822 -- set approver
2823 IF l_sch_det_rec.approver_user_id IS NOT NULL
2824 THEN
2825 Get_User_Role(p_user_id => l_sch_det_rec.approver_user_id,
2826 x_role_name => l_approver,
2827 x_role_display_name => l_display_name,
2828 x_return_status => l_return_status);
2829 IF l_return_status <> FND_API.g_ret_sts_success THEN
2830 result := 'COMPLETE:ERROR';
2831 RETURN;
2832 END IF;
2833
2834 WF_ENGINE.SetItemAttrText(itemtype => itemtype
2835 ,itemkey => l_item_key
2836 ,aname => 'AMS_APPROVER_NAME'
2837 ,avalue => l_approver);
2838 ELSE
2839 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
2840 itemkey => l_item_key,
2841 aname => 'AMS_APPROVER_NAME',
2842 avalue => '' );
2843 END IF;
2844
2845 -- soagrawa added setting all the other values...
2846 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
2847 itemkey => l_item_key,
2848 aname => 'AMS_TRIGGER_ID',
2849 avalue => WF_ENGINE.getItemAttrText(itemtype => itemtype,
2850 itemkey => itemkey ,
2851 aname => 'AMS_TRIGGER_ID') );
2852
2853 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
2854 itemkey => l_item_key,
2855 aname => 'AMS_TRIGGER_NAME',
2856 avalue => WF_ENGINE.getItemAttrText(itemtype => itemtype,
2857 itemkey => itemkey ,
2858 aname => 'AMS_TRIGGER_NAME') );
2859
2860 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
2861 itemkey => l_item_key,
2862 aname => 'AMS_TRIGGER_SCHEDULE_DATE',
2863 avalue => WF_ENGINE.getItemAttrText(itemtype => itemtype,
2864 itemkey => itemkey ,
2865 aname => 'AMS_TRIGGER_SCHEDULE_DATE') );
2866
2867 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
2868 itemkey => l_item_key,
2869 aname => 'AMS_TRIGGER_DATE',
2870 avalue => WF_ENGINE.getItemAttrText(itemtype => itemtype,
2871 itemkey => itemkey ,
2872 aname => 'AMS_TRIGGER_DATE') );
2873
2874 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
2875 itemkey => l_item_key,
2876 aname => 'AMS_REPEAT_FREQUENCY_TYPE',
2877 avalue => WF_ENGINE.getItemAttrText(itemtype => itemtype,
2878 itemkey => itemkey ,
2879 aname => 'AMS_REPEAT_FREQUENCY_TYPE') );
2880
2881 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
2882 itemkey => l_item_key,
2883 aname => 'AMS_TRIGGER_REPEAT_END_DATE',
2884 avalue => WF_ENGINE.getItemAttrText(itemtype => itemtype,
2885 itemkey => itemkey ,
2886 aname => 'AMS_TRIGGER_REPEAT_END_DATE') );
2887
2888 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
2889 itemkey => l_item_key,
2890 aname => 'AMS_CAMPAIGN_NAME',
2891 avalue => WF_ENGINE.getItemAttrText(itemtype => itemtype,
2892 itemkey => itemkey ,
2893 aname => 'AMS_CAMPAIGN_NAME') );
2894
2895 -- WF_ENGINE.SetItemAttrText(itemtype => itemtype,
2896 -- itemkey => l_item_key,
2897 -- aname => 'AMS_CHK_MET_MSG',
2898 -- avalue => WF_ENGINE.getItemAttrText(itemtype => itemtype,
2899 -- itemkey => itemkey ,
2900 -- aname => 'AMS_CHK_MET_MSG') );
2901
2902
2903 -- WF_ENGINE.SetItemAttrText(itemtype => itemtype,
2904 -- itemkey => l_item_key,
2905 -- aname => 'AMS_NTF_MESSAGE',
2906 -- avalue => WF_ENGINE.getItemAttrText(itemtype => itemtype,
2907 -- itemkey => itemkey ,
2908 -- aname => 'AMS_NTF_MESSAGE') );
2909
2910 -- end soagrawa setting values
2911
2912 AMS_Utility_PVT.Create_Log (
2913 x_return_status => l_return_log_status,
2914 p_arc_log_used_by => 'TRIG',
2915 p_log_used_by_id => l_trigger_id,
2916 p_msg_data => 'Get_Aval_Repeat_Sch : 6.Finished setting all params',
2917 p_msg_type => 'DEBUG'
2918 );
2919
2920
2921 -- set the parent process
2922 WF_ENGINE.SetItemParent(itemtype => itemtype
2923 ,itemkey => l_item_key
2924 ,parent_itemtype => itemtype
2925 ,parent_itemkey => itemkey
2926 ,parent_context => NULL
2927 );
2928
2929 WF_ENGINE.StartProcess (itemtype => itemtype,
2930 itemkey => l_item_key);
2931
2932
2933 AMS_Utility_PVT.Create_Log (
2934 x_return_status => l_return_log_status,
2935 p_arc_log_used_by => 'TRIG',
2936 p_log_used_by_id => l_trigger_id,
2937 p_msg_data => 'Get_Aval_Repeat_Sch : 7.Started Process',
2938 p_msg_type => 'DEBUG'
2939 );
2940
2941
2942 IF (l_trigger_sched_date < l_trigger_end_date) THEN
2943
2944 AMS_Utility_PVT.Create_Log (
2945 x_return_status => l_return_log_status,
2946 p_arc_log_used_by => 'TRIG',
2947 p_log_used_by_id => l_trigger_id,
2948 p_msg_data => 'Get_Aval_Repeat_Sch : 8.Yes gotta create new csch',
2949 p_msg_type => 'DEBUG'
2950 );
2951
2952 open c_sched_rec(l_sch_det_rec.schedule_id);
2953 FETCH c_sched_rec INTO l_csch_rec;
2954 CLOSE c_sched_rec;
2955
2956 /* l_schedule_rec.schedule_id := FND_API.G_MISS_NUM;
2957 l_schedule_rec.campaign_id := l_csch_rec.campaign_id;
2958
2959 l_schedule_rec.use_parent_code_flag := l_csch_rec.use_parent_code_flag;
2960 l_schedule_rec.source_code := null; */
2961
2962
2963 -- set start and end dates
2964 /* IF l_trig_freq_type = 'DAILY' THEN
2965 l_schedule_rec.start_date_time := l_csch_rec.start_date_time + l_trig_freq_every_x ;
2966 ELSIF l_trig_freq_type = 'WEEKLY' THEN
2967 l_schedule_rec.start_date_time := l_csch_rec.start_date_time + (7 * l_trig_freq_every_x) ;
2968 ELSIF l_trig_freq_type = 'MONTHLY' THEN
2969 l_schedule_rec.start_date_time := add_months(l_csch_rec.start_date_time , l_trig_freq_every_x) ;
2970 ELSIF l_trig_freq_type = 'YEARLY' THEN
2971 l_schedule_rec.start_date_time := add_months(l_csch_rec.start_date_time , (12*l_trig_freq_every_x)) ;
2972 ElSIF l_trig_freq_type = 'HOURLY' THEN
2973 l_schedule_rec.start_date_time := l_csch_rec.start_date_time + (l_trig_freq_every_x/24) ;
2974 END IF;*/
2975
2976 -- Start: anchaudh:15 Oct'03: Calling central api directly to get the start date of the child schedule.
2977
2978 AMS_SCHEDULER_PVT.Schedule_Repeat( p_last_run_date => l_csch_rec.start_date_time ,
2979 p_frequency => l_trig_freq_every_x,
2980 p_frequency_type => l_trig_freq_type,
2981 x_next_run_date => l_child_sched_st_date,
2982 x_return_status => l_return_status,
2983 x_msg_count => l_msg_count,
2984 x_msg_data => l_msg_data);
2985
2986 IF l_return_status <> FND_API.g_ret_sts_success THEN
2987 l_failed := l_failed + 1;
2988 result := 'COMPLETE:ERROR';
2989 END IF;
2990
2991 -- End: anchaudh:15 Oct'03: Calling central api directly to get the start date of the child schedule.
2992
2993
2994 --cgoyal fixed bug#3063816
2995 OPEN c_camp_det(l_csch_rec.campaign_id);
2996 FETCH c_camp_det INTO l_camp_end_dt; -- campaign's end date
2997 CLOSE c_camp_det;
2998
2999 AMS_Utility_PVT.Create_Log (
3000 x_return_status => l_return_log_status,
3001 p_arc_log_used_by => 'TRIG',
3002 p_log_used_by_id => l_trigger_id,
3003 p_msg_data => 'l_csch_rec.campaign_id = ' || l_csch_rec.campaign_id || ' l_camp_end_dt = ' || l_camp_end_dt,
3004 p_msg_type => 'DEBUG'
3005 );
3006
3007 -- IF (l_schedule_rec.start_date_time <= l_camp_end_dt) THEN
3008 IF (l_child_sched_st_date <= l_camp_end_dt) THEN
3009 -- end fix#3063816 by cgoyal
3010
3011 IF l_csch_rec.end_date_time IS null THEN
3012 -- l_schedule_rec.end_date_time := null;
3013 l_child_sched_end_date_time := null;
3014 ELSE
3015 l_child_sched_end_date_time := l_child_sched_st_date + (l_csch_rec.end_date_time - l_csch_rec.start_date_time);
3016
3017 AMS_Utility_PVT.Create_Log (
3018 x_return_status => l_return_log_status,
3019 p_arc_log_used_by => 'TRIG',
3020 p_log_used_by_id => l_trigger_id,
3021 p_msg_data => 'l_csch_rec.schedule_id = ' || l_csch_rec.schedule_id || ' l_camp_end_dt = ' || l_camp_end_dt || 'l_csch_rec.orig_csch_id = ' || l_csch_rec.orig_csch_id ,
3022 p_msg_type => 'DEBUG'
3023 );
3024
3025 IF l_child_sched_end_date_time > l_camp_end_dt
3026 THEN
3027 l_child_sched_end_date_time := l_camp_end_dt;
3028 END IF;
3029 END IF;
3030 -- end fix#3063816 by cgoyal
3031
3032 -- soagrawa added on 18-feb-2004 for bug# 3452264
3033 OPEN c_orig_csch_name(l_csch_rec.schedule_id);
3034 FETCH c_orig_csch_name INTO l_orig_schedule_name;
3035 CLOSE c_orig_csch_name;
3036
3037 AMS_Utility_PVT.Create_Log (
3038 x_return_status => l_return_log_status,
3039 p_arc_log_used_by => 'TRIG',
3040 p_log_used_by_id => l_trigger_id,
3041 p_msg_data => 'l_csch_rec.schedule_id used for getting ORIG CSCH NAME is '||l_csch_rec.schedule_id,
3042 p_msg_type => 'DEBUG'
3043 );
3044
3045 AMS_Utility_PVT.Create_Log (
3046 x_return_status => l_return_log_status,
3047 p_arc_log_used_by => 'TRIG',
3048 p_log_used_by_id => l_trigger_id,
3049 p_msg_data => 'ORIG CSCH NAME is '||l_orig_schedule_name,
3050 p_msg_type => 'DEBUG'
3051 );
3052
3053 -- Start: anchaudh:15 Oct'03: Calling central api directly to create the schedule and copy its various components.
3054 -- soagrawa modified on 13-may-2004 to fix bug# 3621786
3055 AMS_SCHEDULER_PVT.Create_Next_Schedule( p_parent_sched_id => nvl(l_csch_rec.orig_csch_id,l_csch_rec.schedule_id),
3056 -- AMS_SCHEDULER_PVT.Create_Next_Schedule( p_parent_sched_id => l_csch_rec.schedule_id,
3057 p_child_sched_st_date => l_child_sched_st_date,
3058 p_child_sched_en_date => l_child_sched_end_date_time,
3059 x_child_sched_id => l_new_sched_id,
3060 -- soagrawa added on 18-feb-2004 for bug# 3452264
3061 p_orig_sch_name => l_orig_schedule_name,
3062 p_trig_repeat_flag => 'Y',
3063 x_msg_count => l_msg_count,
3064 x_msg_data => l_msg_data,
3065 x_return_status => l_return_status
3066 );
3067
3068 IF l_return_status <> FND_API.g_ret_sts_success THEN
3069 l_failed := l_failed + 1;
3070 result := 'COMPLETE:ERROR';
3071 AMS_Utility_PVT.Create_Log (
3072 x_return_status => l_return_log_status,
3073 p_arc_log_used_by => 'TRIG',
3074 p_log_used_by_id => l_trigger_id,
3075 p_msg_data => 'Get_Aval_Repeat_Sch : 9.After AMS_SCHEDULER_PVT.Create_Next_Schedule call'||l_return_status,
3076 p_msg_type => 'DEBUG'
3077 );
3078 END IF;
3079 -- End: anchaudh:15 Oct'03: Calling central api directly to create the schedule and copy its various components.
3080
3081 --fix#3063816 by cgoyal
3082 END IF;
3083 --end fix#3063816 by cgoyal
3084 END IF;
3085 END LOOP;
3086
3087 AMS_Utility_PVT.Create_Log (
3088 x_return_status => l_return_log_status,
3089 p_arc_log_used_by => 'TRIG',
3090 p_log_used_by_id => l_trigger_id,
3091 p_msg_data => 'Get_Aval_Repeat_Sch : Loop ends',
3092 p_msg_type => 'DEBUG' );
3093
3094
3095 IF l_failed > 0
3096 THEN
3097 result := 'COMPLETE:ERROR';
3098 ELSE
3099 result := 'COMPLETE:SUCCESS';
3100 END IF;
3101
3102
3103 RETURN;
3104 END IF; -- if funcmode is run
3105
3106 -- CANCEL mode - Normal Process Execution
3107 IF (funcmode = 'CANCEL')
3108 THEN
3109 result := 'COMPLETE:' ;
3110 RETURN;
3111 END IF;
3112
3113 -- TIMEOUT mode - Normal Process Execution
3114 IF (funcmode = 'TIMEOUT')
3115 THEN
3116 result := 'COMPLETE:' ;
3117 RETURN;
3118 END IF;
3119
3120 END Get_Aval_Repeat_Sch;
3121
3122
3123 -- Start of Comments
3124 --
3125 -- NAME
3126 -- Event_Custom_action
3127 --
3128 -- PURPOSE
3129 -- This Procedure will check whether the Trigger is Active or Expired
3130 -- It will Return - Yes if the trigger is Active
3131 -- - No If the trigger is Expired
3132 --
3133 -- IN
3134 -- Itemtype - AMS_TRIG
3135 -- Itemkey - Trigger ID
3136 -- Accid - Activity ID
3137 -- Funmode - Run/Cancel/Timeout
3138 --
3139 -- OUT
3140 -- Result - 'COMPLETE:Y' If the trigger is Active
3141 -- - 'COMPLETE:N' If the trigger is Expired
3142 --
3143 -- Used By Activities
3144 -- Item Type - AMS_TRIG
3145 -- Activity - AMS_CHECK_TRIGGER_STATUS
3146 --
3147 -- NOTES
3148 --
3149 --
3150 -- HISTORY
3151 -- 07-may-2003 soagrawa Added to programatically raise a WF event for custom action
3152 --
3153 -- End of Comments
3154
3155 PROCEDURE Event_Custom_action (itemtype IN VARCHAR2,
3156 itemkey IN VARCHAR2,
3157 actid IN NUMBER,
3158 funcmode IN VARCHAR2,
3159 result OUT NOCOPY VARCHAR2) IS
3160
3161 l_trigger_id NUMBER;
3162 l_parameter_list WF_PARAMETER_LIST_T;
3163
3164 BEGIN
3165 IF (funcmode = 'RUN')
3166 THEN
3167 l_trigger_id := WF_ENGINE.GetItemAttrText(
3168 itemtype => itemtype,
3169 itemkey => itemkey ,
3170 aname => 'AMS_TRIGGER_ID');
3171
3172 l_parameter_list := WF_PARAMETER_LIST_T();
3173
3174 wf_event.AddParameterToList(p_name => 'AMS_TRIGGER_ID',
3175 p_value => l_trigger_id,
3176 p_parameterlist => l_parameter_list);
3177
3178
3179 Wf_Event.Raise
3180 ( p_event_name => 'oracle.apps.ams.trigger.TriggerCustomActionEvent',
3181 p_event_key => l_trigger_id || 'CUST' || TO_CHAR(SYSDATE,'DDMMRRRRHH24MISS'),
3182 p_parameters => l_parameter_list
3183 );
3184 END IF;
3185
3186 -- CANCEL mode - Normal Process Execution
3187 IF (funcmode = 'CANCEL')
3188 THEN
3189 RETURN;
3190 END IF;
3191
3192 -- TIMEOUT mode - Normal Process Execution
3193 IF (funcmode = 'TIMEOUT')
3194 THEN
3195 RETURN;
3196 END IF;
3197 -- dbms_output.put_line('End Check Trigger stat :'||result);
3198 EXCEPTION
3199 WHEN OTHERS THEN
3200 wf_core.context(G_PKG_NAME,'Event_Custom_action',itemtype,itemkey,actid,funcmode);
3201 raise ;
3202 END Event_Custom_action ;
3203
3204
3205 -- Start of Comments
3206 --
3207 -- NAME
3208 -- Event_Custom_action
3209 --
3210 -- PURPOSE
3211 -- This Procedure will check whether the Trigger is Active or Expired
3212 -- It will Return - Yes if the trigger is Active
3213 -- - No If the trigger is Expired
3214 --
3215 -- IN
3216 -- Itemtype - AMS_TRIG
3217 -- Itemkey - Trigger ID
3218 -- Accid - Activity ID
3219 -- Funmode - Run/Cancel/Timeout
3220 --
3221 -- OUT
3222 -- Result - 'COMPLETE:Y' If the trigger is Active
3223 -- - 'COMPLETE:N' If the trigger is Expired
3224 --
3225 -- Used By Activities
3226 -- Item Type - AMS_TRIG
3227 -- Activity - AMS_CHECK_TRIGGER_STATUS
3228 --
3229 -- NOTES
3230 --
3231 --
3232 -- HISTORY
3233 -- 07-may-2003 soagrawa Added to programatically raise a WF event for custom action
3234 --
3235 -- End of Comments
3236
3237 PROCEDURE Event_trig_next(itemtype IN VARCHAR2,
3238 itemkey IN VARCHAR2,
3239 actid IN NUMBER,
3240 funcmode IN VARCHAR2,
3241 result OUT NOCOPY VARCHAR2) IS
3242
3243 l_trigger_id NUMBER;
3244 l_parameter_list WF_PARAMETER_LIST_T;
3245 l_sch_date DATE;
3246 l_sch_text VARCHAR2(100);
3247 l_new_item_key VARCHAR2(30);
3248
3249 BEGIN
3250 IF (funcmode = 'RUN')
3251 THEN
3252 l_trigger_id := WF_ENGINE.GetItemAttrText(
3253 itemtype => itemtype,
3254 itemkey => itemkey ,
3255 aname => 'AMS_TRIGGER_ID');
3256
3257 l_parameter_list := WF_PARAMETER_LIST_T();
3258
3259 wf_event.AddParameterToList(p_name => 'AMS_TRIGGER_ID',
3260 p_value => l_trigger_id,
3261 p_parameterlist => l_parameter_list);
3262
3263 l_sch_date := WF_ENGINE.GetItemAttrDate(itemtype => itemtype,
3264 itemkey => itemkey ,
3265 aname => 'AMS_TRIGGER_SCHEDULE_DATE'
3266 );
3267
3268 l_new_item_key := l_trigger_id || TO_CHAR(SYSDATE,'DDMMRRRRHH24MISS');
3269
3270 Wf_Event.Raise
3271 ( p_event_name => 'oracle.apps.ams.trigger.TriggerEvent',
3272 p_event_key => l_new_item_key,
3273 p_parameters => l_parameter_list,
3274 p_send_date => l_sch_date
3275 );
3276
3277 UPDATE ams_triggers
3278 SET process_id = to_number(l_new_item_key)
3279 WHERE trigger_id = l_trigger_id;
3280
3281 END IF;
3282
3283 -- CANCEL mode - Normal Process Execution
3284 IF (funcmode = 'CANCEL')
3285 THEN
3286 RETURN;
3287 END IF;
3288
3289 -- TIMEOUT mode - Normal Process Execution
3290 IF (funcmode = 'TIMEOUT')
3291 THEN
3292 RETURN;
3293 END IF;
3294 -- dbms_output.put_line('End Check Trigger stat :'||result);
3295 EXCEPTION
3296 WHEN OTHERS THEN
3297 wf_core.context(G_PKG_NAME,'Event_trig_next',itemtype,itemkey,actid,funcmode);
3298 raise ;
3299 END Event_trig_next ;
3300
3301
3302 -- Start of Comments
3303 --
3304 -- NAME
3305 -- Wf_Init_var
3306 --
3307 -- PURPOSE
3308 -- This Procedure will check whether the Trigger is Active or Expired
3309 -- It will Return - Yes if the trigger is Active
3310 -- - No If the trigger is Expired
3311 --
3312 -- IN
3313 -- Itemtype - AMS_TRIG
3314 -- Itemkey - Trigger ID
3315 -- Accid - Activity ID
3316 -- Funmode - Run/Cancel/Timeout
3317 --
3318 -- OUT
3319 -- Result - 'COMPLETE:Y' If the trigger is Active
3320 -- - 'COMPLETE:N' If the trigger is Expired
3321 --
3322 -- Used By Activities
3323 -- Item Type - AMS_TRIG
3324 -- Activity - AMS_CHECK_TRIGGER_STATUS
3325 --
3326 -- NOTES
3327 --
3328 --
3329 -- HISTORY
3330 -- 07-may-2003 soagrawa Added to programatically raise a WF event for custom action
3331 -- 09 Nov-2004 anchaudh Fixed setting of WF owner for triggers WF
3332 -- 26 aug-2005 soagrawa Modified to add flexibility around initiative being monitored for R12
3333 -- End of Comments
3334
3335 PROCEDURE Wf_Init_var(itemtype IN VARCHAR2,
3336 itemkey IN VARCHAR2,
3337 actid IN NUMBER,
3338 funcmode IN VARCHAR2,
3339 result OUT NOCOPY VARCHAR2) IS
3340
3341 l_trigger_id NUMBER;
3342 l_username VARCHAR2(100);
3343 l_approver_username VARCHAR2(100);
3344 l_list_name VARCHAR2(240);
3345 l_timeout_days NUMBER ;
3346 l_priority NUMBER ;
3347
3348 l_emp_id NUMBER;
3349 l_user_name VARCHAR2(100);
3350 l_disp_name VARCHAR2(100);
3351
3352 -- anchaudh defining cursor for setting item owner along with bug fix for bug# 3799053
3353 CURSOR c_emp_dtl(l_res_id IN NUMBER) IS
3354 SELECT employee_id
3355 FROM ams_jtf_rs_emp_v
3356 WHERE resource_id = l_res_id ;
3357
3358 CURSOR c_trig_det IS
3359 SELECT *
3360 -- modified by soagrawa
3361 -- FROM ams_triggers
3362 FROM ams_triggers_vl
3363 WHERE trigger_id = l_trigger_id ;
3364 l_trig_rec c_trig_det%ROWTYPE;
3365
3366 CURSOR c_check_det IS
3367 SELECT *
3368 FROM ams_trigger_checks
3369 WHERE trigger_id = l_trigger_id ;
3370 l_check_rec c_check_det%ROWTYPE;
3371
3372 CURSOR c_notify_action_det IS
3373 SELECT *
3374 FROM ams_trigger_actions
3375 WHERE trigger_id = l_trigger_id
3376 AND execute_action_type = 'NOTIFY';
3377 l_notify_action_det c_notify_action_det%ROWTYPE;
3378
3379 -- The Campaign_owner Username has tobe selected from jtf_resource_extn_vl
3380 -- Has to be changed once the view is modified
3381 CURSOR c_camp_det IS
3382 SELECT campaign_name, priority, owner_user_id
3383 FROM ams_campaigns_vl
3384 WHERE campaign_id = l_trig_rec.trigger_created_for_id ;
3385
3386 CURSOR c_csch_det IS
3387 SELECT schedule_name, priority, owner_user_id
3388 FROM ams_campaign_schedules_vl
3389 WHERE schedule_id = l_trig_rec.trigger_created_for_id ;
3390
3391 CURSOR c_eveo_eone_det IS
3392 SELECT event_offer_name, 'STANDARD', owner_user_id
3393 FROM ams_event_offers_vl
3394 WHERE event_offer_id = l_trig_rec.trigger_created_for_id ;
3395
3396 CURSOR c_eveh_det IS
3397 SELECT event_header_name, 'STANDARD', owner_user_id
3398 FROM ams_event_headers_vl
3399 WHERE event_header_id = l_trig_rec.trigger_created_for_id ;
3400
3401 l_monitor_obj_name VARCHAR2(240);
3402 l_monitor_obj_priority VARCHAR2(30);
3403 l_monitor_obj_owner NUMBER;
3404
3405 -- Cursor to get metric details if the trigger is created for Metric
3406 CURSOR c_metric_det(l_metric_id NUMBER) IS
3407 SELECT met.metrics_name metrics_name,act.act_metric_used_by_id metric_used_by_id,
3408 act.arc_act_metric_used_by metric_used_by
3409 FROM ams_metrics_vl met,ams_act_metrics_all act
3410 WHERE met.metric_id = act.metric_id
3411 AND act.activity_metric_id = l_metric_id ;
3412 l_metric_rec c_metric_det%ROWTYPE;
3413
3414 CURSOR c_timeout_det IS
3415 SELECT timeout_days_low_prio,
3416 timeout_days_std_prio,
3417 timeout_days_high_prio,
3418 timeout_days_medium_prio
3419 FROM ams_approval_rules
3420 WHERE arc_approval_for_object = l_trig_rec.arc_trigger_created_for;
3421 l_timeout_rec c_timeout_det%ROWTYPE;
3422
3423 l_display_name VARCHAR2(100);
3424 l_return_status VARCHAR2(1);
3425 l_notify VARCHAR2(30);
3426
3427 BEGIN
3428 IF (funcmode = 'RUN')
3429 THEN
3430
3431 l_trigger_id := WF_ENGINE.GetItemAttrText(
3432 itemtype => itemtype,
3433 itemkey => itemkey ,
3434 aname => 'AMS_TRIGGER_ID');
3435
3436 AMS_Utility_PVT.Create_Log (
3437 x_return_status => l_return_status,
3438 p_arc_log_used_by => 'TRIG',
3439 p_log_used_by_id => l_trigger_id,
3440 p_msg_data => 'WF_INIT_VAR: started',
3441 p_msg_type => 'DEBUG'
3442 );
3443
3444 /*
3445 UPDATE ams_triggers
3446 SET process_id = TO_NUMBER(itemkey)
3447 WHERE trigger_id = l_trigger_id ;
3448 */
3449 OPEN c_trig_det;
3450 FETCH c_trig_det INTO l_trig_rec ;
3451 CLOSE c_trig_det;
3452
3453 -- soagrawa modified logic for R12
3454 IF l_trig_rec.arc_trigger_Created_for in ('CAMP','RCAM')
3455 THEN
3456 OPEN c_camp_det;
3457 FETCH c_camp_det INTO l_monitor_obj_name, l_monitor_obj_priority, l_monitor_obj_owner ;
3458 CLOSE c_camp_det;
3459 ELSIF l_trig_rec.arc_trigger_Created_for in ('CSCH')
3460 THEN
3461 OPEN c_csch_det;
3462 FETCH c_csch_det INTO l_monitor_obj_name, l_monitor_obj_priority, l_monitor_obj_owner ;
3463 CLOSE c_csch_det;
3464 ELSIF l_trig_rec.arc_trigger_Created_for in ('EVEH')
3465 THEN
3466 OPEN c_eveh_det;
3467 FETCH c_eveh_det INTO l_monitor_obj_name, l_monitor_obj_priority, l_monitor_obj_owner ;
3468 CLOSE c_eveh_det;
3469 ELSIF l_trig_rec.arc_trigger_Created_for in ('EONE','EVEO')
3470 THEN
3471 OPEN c_eveo_eone_det;
3472 FETCH c_eveo_eone_det INTO l_monitor_obj_name, l_monitor_obj_priority, l_monitor_obj_owner ;
3473 CLOSE c_eveo_eone_det;
3474 END IF;
3475
3476
3477 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
3478 itemkey => itemkey,
3479 aname => 'AMS_CAMPAIGN_NAME',
3480 avalue => l_monitor_obj_name );
3481
3482 -- end soagrawa
3483
3484
3485 OPEN c_emp_dtl(l_monitor_obj_owner);
3486 FETCH c_emp_dtl INTO l_emp_id;
3487 -- anchaudh setting item owner along with bug fix for bug# 3799053
3488 IF c_emp_dtl%FOUND
3489 THEN
3490 WF_DIRECTORY.getrolename
3491 ( p_orig_system => 'PER',
3492 p_orig_system_id => l_emp_id ,
3493 p_name => l_user_name,
3494 p_display_name => l_disp_name );
3495
3496 IF l_user_name IS NOT NULL THEN
3497 Wf_Engine.SetItemOwner(itemtype => itemtype,
3498 itemkey => itemkey,
3499 owner => l_user_name);
3500 END IF;
3501 END IF;
3502 CLOSE c_emp_dtl;
3503
3504 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
3505 itemkey => itemkey,
3506 aname => 'AMS_TRIGGER_NAME',
3507 avalue => l_trig_rec.trigger_name );
3508
3509 WF_ENGINE.SetItemUserkey(itemtype => itemtype,
3510 itemkey => itemkey ,
3511 userkey => l_trig_rec.trigger_name);
3512
3513 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
3514 itemkey => itemkey,
3515 aname => 'AMS_TRIGGER_SCHEDULE_DATE',
3516 avalue => to_char(l_trig_rec.start_date_time,'DD-MON-RRRR HH:MI:SS AM') );
3517
3518 -- soagrawa set this value as other the first time around it would be empty
3519 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
3520 itemkey => itemkey,
3521 aname => 'AMS_TRIGGER_DATE',
3522 avalue => to_char(l_trig_rec.start_date_time,'DD-MON-RRRR HH:MI:SS AM') );
3523
3524 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
3525 itemkey => itemkey,
3526 aname => 'AMS_REPEAT_FREQUENCY_TYPE',
3527 avalue => l_trig_rec.repeat_frequency_type );
3528
3529 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
3530 itemkey => itemkey,
3531 aname => 'AMS_TRIGGER_REPEAT_END_DATE',
3532 avalue => to_char(l_trig_rec.repeat_stop_date_time,'DD-MON-RRRR HH:MI:SS AM') );
3533
3534 IF l_trig_rec.notify_flag = 'Y'
3535 THEN
3536 OPEN c_notify_action_det;
3537 FETCH c_notify_action_det INTO l_notify_action_det;
3538 IF c_notify_action_det%FOUND
3539 THEN
3540 -- set notify user
3541 IF l_notify_action_det.action_for_id IS NOT NULL
3542 THEN
3543 Get_User_Role(p_user_id => l_notify_action_det.action_for_id,
3544 x_role_name => l_notify,
3545 x_role_display_name => l_display_name,
3546 x_return_status => l_return_status);
3547 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
3548 THEN
3549 RAISE FND_API.G_EXC_ERROR;
3550 END IF;
3551 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
3552 itemkey => itemkey,
3553 aname => 'AMS_TRIG_NOTIFTIER',
3554 avalue => l_notify);
3555 ELSE
3556 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
3557 itemkey => itemkey,
3558 aname => 'AMS_TRIG_NOTIFTIER',
3559 avalue => '');
3560 END IF;
3561 END IF;
3562 CLOSE c_notify_action_det;
3563
3564 END IF;
3565
3566
3567 -- Fetch the username of the Owner of Trigger (Campaign Owner)
3568 Find_Owner (p_activity_id => l_trig_rec.trigger_created_for_id,
3569 p_activity_type => l_trig_rec.arc_trigger_created_for,
3570 x_owner_user_name => l_username
3571 );
3572
3573 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
3574 itemkey => itemkey,
3575 aname => 'AMS_REQUESTOR_USERNAME',
3576 avalue => l_username );
3577
3578 -- Initialize Check Attributes
3579 OPEN c_check_det;
3580 FETCH c_check_det INTO l_check_rec;
3581 IF (c_check_det%FOUND)
3582 THEN
3583 OPEN c_metric_det(l_check_rec.chk1_source_code_metric_id);
3584 FETCH c_metric_det INTO l_metric_rec ;
3585 CLOSE c_metric_det;
3586
3587 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
3588 itemkey => itemkey,
3589 aname => 'AMS_OPERATOR',
3590 avalue => l_check_rec.chk1_to_chk2_operator_type);
3591 END IF;
3592 CLOSE c_check_det;
3593
3594 OPEN c_timeout_det;
3595 FETCH c_timeout_det INTO l_timeout_rec ;
3596 CLOSE c_timeout_det ;
3597
3598 IF l_monitor_obj_priority = 'STANDARD' THEN
3599 l_timeout_days := l_timeout_rec.timeout_days_std_prio ;
3600 l_priority := 50 ; -- Standard
3601 ELSIF l_monitor_obj_priority = 'LOW' THEN
3602 l_timeout_days := l_timeout_rec.timeout_days_low_prio ;
3603 l_priority := 99 ; -- Low
3604 ELSIF l_monitor_obj_priority = 'HIGH' THEN
3605 l_timeout_days := l_timeout_rec.timeout_days_high_prio ;
3606 l_priority := 1 ; -- High
3607 ELSIF l_monitor_obj_priority = 'MEDIUM' THEN
3608 l_timeout_days := l_timeout_rec.timeout_days_medium_prio ;
3609 l_priority := 50 ; -- standard
3610 ELSE
3611 l_timeout_days := l_timeout_rec.timeout_days_medium_prio ;
3612 l_priority := 50 ; -- standard
3613 END IF;
3614
3615
3616 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
3617 itemkey => itemkey,
3618 aname => 'AMS_TIMEOUT',
3619 avalue => l_timeout_days );
3620
3621
3622 END IF;
3623
3624 -- CANCEL mode - Normal Process Execution
3625 IF (funcmode = 'CANCEL')
3626 THEN
3627 RETURN;
3628 END IF;
3629
3630 -- TIMEOUT mode - Normal Process Execution
3631 IF (funcmode = 'TIMEOUT')
3632 THEN
3633 RETURN;
3634 END IF;
3635 -- dbms_output.put_line('End Check Trigger stat :'||result);
3636 EXCEPTION
3637 WHEN OTHERS THEN
3638 wf_core.context(G_PKG_NAME,'Wf_Init_var',itemtype,itemkey,actid,funcmode);
3639 raise ;
3640 END Wf_Init_var ;
3641
3642
3643 -- Start of Comments
3644 --
3645 -- NAME
3646 -- Check_Trig_Exist
3647 --
3648 -- PURPOSE
3649 -- This Procedure will check whether the Trigger id exists or not
3650 -- It will Return - Yes if the trigger does exist
3651 -- - No If the trigger does not exist
3652 --
3653 -- HISTORY
3654 -- 16-may-2003 soagrawa Created to support delete trigger
3655 --
3656 -- End of Comments
3657
3658 PROCEDURE Check_Trig_Exist (itemtype IN VARCHAR2,
3659 itemkey IN VARCHAR2,
3660 actid IN NUMBER,
3661 funcmode IN VARCHAR2,
3662 result OUT NOCOPY VARCHAR2) IS
3663
3664 l_trigger_id NUMBER;
3665 l_dummy NUMBER;
3666 l_return_status VARCHAR2(1);
3667 CURSOR c_trig_exist_det(p_trig_id NUMBER) IS
3668 SELECT 1
3669 FROM ams_triggers
3670 WHERE trigger_id = p_trig_id;
3671
3672 BEGIN
3673 -- RUN mode - Normal Process Execution
3674 IF (funcmode = 'RUN')
3675 THEN
3676 l_trigger_id := WF_ENGINE.GetItemAttrText(
3677 itemtype => itemtype,
3678 itemkey => itemkey ,
3679 aname => 'AMS_TRIGGER_ID');
3680
3681 OPEN c_trig_exist_det(l_trigger_id);
3682 FETCH c_trig_exist_det INTO l_dummy;
3683 CLOSE c_trig_exist_det;
3684
3685 IF l_dummy IS NULL THEN
3686 result := 'COMPLETE:N' ;
3687 AMS_Utility_PVT.Create_Log (
3688 x_return_status => l_return_status,
3689 p_arc_log_used_by => 'TRIG',
3690 p_log_used_by_id => l_trigger_id,
3691 p_msg_data => 'Check_Trig_Exist : 1. For Trigger ID = ' || itemkey || result,
3692 p_msg_type => 'DEBUG'
3693 );
3694 RETURN;
3695 ELSE
3696 result := 'COMPLETE:Y' ;
3697 AMS_Utility_PVT.Create_Log (
3698 x_return_status => l_return_status,
3699 p_arc_log_used_by => 'TRIG',
3700 p_log_used_by_id => l_trigger_id,
3701 p_msg_data => 'Check_Trig_Exist : 1. For Trigger ID = ' || itemkey || result,
3702 p_msg_type => 'DEBUG'
3703 );
3704 RETURN;
3705 END IF;
3706 END IF;
3707
3708 -- CANCEL mode - Normal Process Execution
3709 IF (funcmode = 'CANCEL')
3710 THEN
3711 result := 'COMPLETE:' ;
3712 RETURN;
3713 END IF;
3714
3715 -- TIMEOUT mode - Normal Process Execution
3716 IF (funcmode = 'TIMEOUT')
3717 THEN
3718 result := 'COMPLETE:' ;
3719 RETURN;
3720 END IF;
3721 -- dbms_output.put_line('End Check Trigger stat :'||result);
3722 EXCEPTION
3723 WHEN OTHERS THEN
3724 wf_core.context(G_PKG_NAME,'Check_Trig_Exist',itemtype,itemkey,actid,funcmode);
3725 raise ;
3726 END Check_Trig_Exist ;
3727
3728
3729 --========================================================================
3730 -- PROCEDURE
3731 -- write_msg
3732 -- Purpose
3733 -- API to write concurrent program debugs
3734 -- HISTORY
3735 -- 20-Aug-2005 soagrawa Created.
3736 --
3737 --========================================================================
3738
3739 PROCEDURE write_msg(p_procedure IN VARCHAR2, p_message IN VARCHAR2)
3740 IS
3741 BEGIN
3742 Ams_Utility_Pvt.Write_Conc_Log(TO_CHAR(DBMS_UTILITY.get_time)||': '||
3743 G_PKG_NAME||'.'||p_procedure||': '||p_message);
3744 END;
3745
3746
3747 --========================================================================
3748 -- PROCEDURE
3749 -- write_error
3750 -- Purpose
3751 -- API to write concurrent program error logs
3752 -- HISTORY
3753 -- 20-Aug-2005 soagrawa Created.
3754 --
3755 --========================================================================
3756
3757 PROCEDURE write_error(p_procedure IN varchar2)
3758 IS
3759 l_msg varchar2(4000);
3760 BEGIN
3761 LOOP
3762 l_msg := fnd_msg_pub.get(p_encoded => FND_API.G_FALSE);
3763 EXIT WHEN l_msg IS NULL;
3764 write_msg(p_procedure, 'ERROR: '||l_msg);
3765 END LOOP;
3766 END;
3767
3768
3769 --========================================================================
3770 -- PROCEDURE
3771 -- purge_history
3772 -- Purpose
3773 -- Purges monitor history. Called by Concurrent Program.
3774 -- HISTORY
3775 -- 20-Aug-2005 soagrawa Created.
3776 --
3777 --========================================================================
3778
3779 PROCEDURE purge_history(
3780 errbuf OUT NOCOPY VARCHAR2,
3781 retcode OUT NOCOPY NUMBER,
3782 trig_end_date_days IN NUMBER := 60
3783 ) IS
3784
3785 BEGIN
3786 FND_MSG_PUB.initialize;
3787
3788 IF AMS_DEBUG_HIGH_ON THEN
3789 write_msg('purge_history','Starting to purge Monitor history...');
3790 write_msg('purge_history','History for all triggers that have ended more than '||trig_end_date_days||' days is being deleted...');
3791 END IF;
3792
3793 Delete from ams_trigger_results
3794 where trigger_result_for_id in
3795 (SELECT trigger_id
3796 FROM ams_triggers
3797 WHERE (repeat_stop_date_time + trig_end_date_days) < sysdate);
3798
3799 retcode :=0;
3800
3801 IF AMS_DEBUG_HIGH_ON THEN
3802 write_msg('purge_history','Done deleting Monitor History with status : '||TO_CHAR(retcode));
3803 END IF;
3804
3805 EXCEPTION
3806 WHEN OTHERS THEN
3807 retcode := 2;
3808 write_msg('purge_history','Done deleting Monitor History with status : '||TO_CHAR(retcode));
3809
3810 END purge_history ;
3811
3812
3813
3814
3815 END AMS_WFTrig_PVT ;