1 PACKAGE AMS_WFTrig_PVT as
2 /* $Header: amsvwfts.pls 120.2 2005/08/29 11:34:24 soagrawa ship $*/
3
4 -- Start of Comments
5 --
6 -- NAME
7 -- AMS_WfTrig_PVT
8 --
9 -- PURPOSE
10 -- This package performs the workflow procedures for
11 -- triggers in Oracle Marketing
12 --
13 -- HISTORY
14 -- 28-apr-2003 soagrawa Added APIs for trigger redesign
15 -- 20-aug-2005 soagrawa Added code for Conc Program that will purge monitor history
16 --
17 /*************************** PRIVATE ROUTINES *******************************/
18
19 -- Start of Comments
20 --
21 -- NAME
22 -- StartProcess
23 --
24 -- PURPOSE
25 -- This Procedure will Start the flow
26 --
27 -- IN
28 -- p_trigger_id Trigger id
29 -- p_trigger_name Trigger Name
30 -- processowner Owner Of the Process
31 -- workflowprocess Work Flaow Process Name (AMS_TRIGGERS)
32 -- item_type Item type DEFAULT NULL(AMS_CAMP)
33
34 --
35 -- OUT
36 --
37 -- Used By Activities
38 --
39 -- NOTES
40 --
41 --
42 -- HISTORY
43 -- 22-MAR-2001 julou created
44 -- End of Comments
45 PROCEDURE StartProcess(p_trigger_id IN NUMBER -- Trigger id
46 -- ,p_user_id IN NUMBER -- User id
47
48 ,processowner IN VARCHAR2 DEFAULT NULL
49 ,workflowprocess IN VARCHAR2 DEFAULT NULL
50 ,item_type IN VARCHAR2 DEFAULT NULL
51
52 ) ;
53
54 -- Start of Comments
55 --
56 -- NAME
57 -- Selector
58 --
59 -- PURPOSE
60 -- This Procedure will determine which process to run
61 --
62 -- IN
63 -- itemtype - A Valid item type from (WF_ITEM_TYPES Table).
64 -- itemkey - A string generated from application object's primary key.
65 -- actid - The function Activity
66 -- funcmode - Run / Cancel
67 --
68 -- OUT
69 -- resultout - Name of workflow process to run
70 --
71 -- Used By Activities
72 --
73 -- NOTES
74 --
75 --
76 -- HISTORY
77 -- 22-MAR-2001 julou created
78 -- End of Comments
79 PROCEDURE selector (
80 itemtype IN VARCHAR2,
81 itemkey IN VARCHAR2,
82 actid IN NUMBER,
83 funcmode IN VARCHAR2,
84 resultout OUT NOCOPY VARCHAR2
85 ) ;
86
87 -- Start of Comments
88 --
89 -- NAME
90 -- Check_Trigger_Status
91 --
92 -- PURPOSE
93 -- This Procedure will check whether the Trigger is Active or Expired
94 -- It will Return - Yes if the trigger is Active
95 -- No If the trigger is Expired
96 --
97 -- IN
98 -- Itemtype - AMS_CAMP
99 -- Itemkey - Trigger ID
100 -- Accid - Activity ID
101 -- Funmode - Run/Cancel/Timeout
102 --
103 -- OUT
104 -- Result - 'COMPLETE:Y' If the trigger is Active
105 -- - 'COMPLETE:N' If the trigger is Expired
106 --
107 -- Used By Activities
108 -- Item Type - AMS_CAMP
109 -- Activity - AMS_CHECK_TRIGGER_STATUS
110 --
111 -- NOTES
112 --
113 --
114 -- HISTORY
115 -- 22-MAR-2001 julou created
116 -- End of Comments
117 PROCEDURE Check_Trigger_Status(itemtype IN VARCHAR2,
118 itemkey IN VARCHAR2,
119 actid IN NUMBER,
120 funcmode IN VARCHAR2,
121 result OUT NOCOPY VARCHAR2) ;
122
123 -- Start of Comments
124 --
125 -- NAME
126 -- Perform_Check
127 --
128 -- PURPOSE
129 -- This Procedure will perform the check on standard item and Comparison Item
130 -- with the operator provided
131 -- It will Return - Success if the check is successful
132 -- - Failure If the check is not successful
133 -- - Error If there is an error in the Check Process
134 --
135 -- IN
136 -- Itemtype - AMS_TRIG
137 -- Itemkey - Trigger ID
138 -- Accid - Activity ID
139 -- Funmode - Run/Cancel/Timeout
140 --
141 -- OUT
142 -- Result - 'COMPLETE:SUCCESS' If the check is successful
143 -- - 'COMPLETE:FAILURE' If the check is Failure
144 -- - 'COMPLETE:ERROR' If there is an Error in the check Process
145 --
146 -- Used By Activities
147 -- Item Type - AMS_TRIG
148 -- Activity - AMS_PERFORM_CHECK
149 --
150 -- NOTES
151 --
152 --
153 -- HISTORY
154 -- 22-MAR-2001 julou created
155 -- End of Comments
156 PROCEDURE Perform_check(itemtype IN VARCHAR2,
157 itemkey IN VARCHAR2,
158 actid IN NUMBER,
159 funcmode IN VARCHAR2,
160 result OUT NOCOPY VARCHAR2) ;
161
162 -- Start of Comments
163 --
164 -- NAME
165 -- Record_Result
166 --
167 -- PURPOSE
168 -- This Procedure will record the the result
169 -- after the attached schedules are executed
170 --
171 -- IN
172 -- Itemtype - AMS_TRIG
173 -- Itemkey - Trigger ID
174 -- Accid - Activity ID
175 -- Funmode - Run/Cancel/Timeout
176 --
177 -- OUT
178 -- Result - 'COMPLETE:'
179 --
180 -- Used By Activities
181 -- Item Type - AMS_TRIG
182 -- Activity - AMS_RECORD_RESULT
183 --
184 -- NOTES
185 --
186 --
187 -- HISTORY
188 -- 22-MAR-2001 julou created
189 -- End of Comments
190 PROCEDURE Record_Result(itemtype IN VARCHAR2,
191 itemkey IN VARCHAR2,
192 actid IN NUMBER,
193 funcmode IN VARCHAR2,
194 result OUT NOCOPY VARCHAR2) ;
195
196 -- Start of Comments
197 --
198 -- NAME
199 -- Check_Repeat
200 --
201 -- PURPOSE
202 -- This Procedure will return Yes if there the Trigger is repeating
203 -- or it will return No
204 --
205 -- IN
206 -- Itemtype - AMS_TRIG
207 -- Itemkey - Trigger ID
208 -- Accid - Activity ID
209 -- Funmode - Run/Cancel/Timeout
210 --
211 -- OUT
212 -- Result - 'COMPLETE:Y' If the trigger is repeating
213 -- - 'COMPLETE:N' If the trigger is not repeating
214 --
215 -- Used By Activities
216 -- Item Type - AMS_TRIG
217 -- Activity - AMS_CHECK_REPEAT
218 --
219 -- NOTES
220 --
221 --
222 -- HISTORY
223 -- 22-MAR-2001 julou created
224 -- End of Comments
225 PROCEDURE Check_Repeat(itemtype IN VARCHAR2,
226 itemkey IN VARCHAR2,
227 actid IN NUMBER,
228 funcmode IN VARCHAR2,
229 result OUT NOCOPY VARCHAR2) ;
230
231 -- Start of Comments
232 --
233 -- NAME
234 -- Schedule_Trig_Run
235 --
236 -- PURPOSE
237 -- This Procedure will Calculate the next schedule time for Trigger to fire
238 --
239 -- It will Return - Success if the check is successful
240 -- - Error If there is an error in the Check Process
241 --
242 -- IN
243 -- Itemtype - AMS_TRIG
244 -- Itemkey - Trigger ID
245 -- Accid - Activity ID
246 -- Funmode - Run/Cancel/Timeout
247 --
248 -- OUT
249 -- Result - 'COMPLETE:SUCCESS' If the Scheduling is successful
250 -- - 'COMPLETE:ERROR' If the scheduling is errored out
251 --
252 -- Used By Activities
253 -- Item Type - AMS_TRIG
254 -- Activity - AMS_SCHEDULE_TRIG_RUN
255 --
256 -- NOTES
257 --
258 --
259 -- HISTORY
260 -- 22-MAR-2001 julou created
261 -- End of Comments
262 PROCEDURE Schedule_Trig_Run(itemtype IN VARCHAR2,
263 itemkey IN VARCHAR2,
264 actid IN NUMBER,
265 funcmode IN VARCHAR2,
266 result OUT NOCOPY VARCHAR2) ;
267
268 -- Start of Comments
269 --
270 -- NAME
271 -- Require_Approval
272 --
273 -- PURPOSE
274 -- This Procedure will return Yes if there is an Approval is required for the List
275 -- or it will return No if there is no Approval is required
276 --
277 -- IN
278 -- Itemtype - AMS_TRIG
279 -- Itemkey - Trigger ID
280 -- Accid - Activity ID
281 -- Funmode - Run/Cancel/Timeout
282 --
283 -- OUT
284 -- Result - 'COMPLETE:Y' If the notification is required
285 -- - 'COMPLETE:N' If the notification is not required
286 --
287 -- Used By Activities
288 -- Item Type - AMS_TRIG
289 -- Activity - AMS_REQUIRE_APPROVAL
290 --
291 -- NOTES
292 --
293 --
294 -- HISTORY
295 -- 22-MAR-2001 julou created
296 -- End of Comments
297 PROCEDURE Require_Approval(itemtype IN VARCHAR2,
298 itemkey IN VARCHAR2,
299 actid IN NUMBER,
300 funcmode IN VARCHAR2,
301 result OUT NOCOPY VARCHAR2) ;
302
303 -- Start of Comments
304 --
305 -- NAME
306 -- Notify_Chk_Met
307 --
308 -- PURPOSE
309 -- This Procedure will return Yes if Notification is required for the schedule
310 -- or No if no Notification required
311 --
312 -- IN
313 -- Itemtype - AMS_TRIG
314 -- Itemkey - Trigger ID
315 -- Accid - Activity ID
316 -- Funmode - Run/Cancel/Timeout
317 --
318 -- OUT
319 -- Result - 'COMPLETE:Y' If the notification is required
320 -- - 'COMPLETE:N' If the notification is not required
321 --
322 -- Used By Activities
323 -- Item Type - AMS_TRIG
324 -- Activity - AMS_Notify_Chk_Met
325 --
326 -- NOTES
327 --
328 --
329 -- HISTORY
330 -- 22-MAR-2001 julou created
331 -- End of Comments
332 PROCEDURE Notify_Chk_Met (itemtype IN VARCHAR2,
333 itemkey IN VARCHAR2,
334 actid IN NUMBER,
335 funcmode IN VARCHAR2,
336 result OUT NOCOPY VARCHAR2) ;
337
338 -- Start of Comments
339 --
340 -- NAME
341 -- Execute_Schedule
342 --
343 -- PURPOSE
344 -- This Procedure will execute the schedule. It will return SUCCESS if schedule completes successfully
345 -- or it will return ERROR if there is any error.
346 --
347 -- IN
348 -- Itemtype - AMS_TRIG
349 -- Itemkey - Trigger ID
350 -- Accid - Activity ID
351 -- Funmode - Run/Cancel/Timeout
352 --
353 -- OUT
354 -- Result - 'COMPLETE:SUCCESS' If the schedule is executed successfully
355 -- - 'COMPLETE:ERROR' If the schedule is executed successfully
356 --
357 -- Used By Activities
358 -- Item Type - AMS_TRIG
359 -- Activity - EXECUTE_SCHEDULE
360 --
361 -- NOTES
362 --
363 --
364 -- HISTORY
365 -- End of Comments
366 PROCEDURE Execute_Schedule(itemtype IN VARCHAR2,
367 itemkey IN VARCHAR2,
368 actid IN NUMBER,
369 funcmode IN VARCHAR2,
370 result OUT NOCOPY VARCHAR2) ;
371 /*
372 -- Start of Comments
373 --
374 -- NAME
375 -- Check_Active_Sch
376 --
377 -- PURPOSE
378 -- This Procedure will check if there are more active schedules. It will return Yes
379 -- if more active schedules are found; otherwise it returns No
380 -- IN
381 -- Itemtype - AMS_TRIG
382 -- Itemkey - Trigger ID
383 -- Accid - Activity ID
384 -- Funmode - Run/Cancel/Timeout
385 --
386 -- OUT
387 -- Result - 'COMPLETE:Y' If the there are more schedules are active
388 -- - 'COMPLETE:N' If the no more active schedule
389 --
390 -- Used By Activities
391 -- Item Type - AMS_TRIG
392 -- Activity - EXECUTE_SCHEDULE
393 --
394 -- NOTES
395 --
396 --
397 -- HISTORY
398 -- 22-MAR-2001 julou created
399 -- End of Comments
400 PROCEDURE Check_Active_Sch(itemtype IN VARCHAR2,
401 itemkey IN VARCHAR2,
402 actid IN NUMBER,
403 funcmode IN VARCHAR2,
404 result OUT NOCOPY VARCHAR2) ;
405 */
406
407 -- Start of Comments
408 --
409 -- NAME
410 -- Get_User_Role
411 --
412 -- PURPOSE
413 -- This procedure returns the User role for the userid sent.
414 --
415 -- IN
416 -- p_user_id - user's resource id
417 --
418 -- OUT
419 -- x_role_name -- role name for the userid sent
420 -- x_role_display_name -- displayed name of the role
421 -- x_return_status -- status for searching the role
422 --
423 -- NOTES
424 -- HISTORY
425 -- 24-APR-2001 julou created
426 -- End of Comments
427 PROCEDURE Get_User_Role(p_user_id IN NUMBER,
428 x_role_name OUT NOCOPY VARCHAR2,
429 x_role_display_name OUT NOCOPY VARCHAR2 ,
430 x_return_status OUT NOCOPY VARCHAR2);
431
432 -- Start of Comments
433 --
434 -- NAME
435 -- Check_Active_Sch
436 --
437 -- PURPOSE
438 -- This Procedure will get available schedule that attaches to the trigger.
439 --
440 -- IN
441 -- Itemtype - AMS_TRIG
442 -- Itemkey - Trigger ID
443 -- Accid - Activity ID
444 -- Funmode - Run/Cancel/Timeout
445 --
446 -- OUT
447 -- Result -- 'COMPLETE:'
448 --
449 -- Used By Activities
450 -- Item Type - AMS_TRIG
451 -- Activity - EXECUTE_SCHEDULE
452 --
453 -- NOTES
454 --
455 --
456 -- HISTORY
457 -- 22-MAR-2001 julou created
458 -- End of Comments
459 PROCEDURE Get_Aval_Sch(itemtype IN VARCHAR2,
460 itemkey IN VARCHAR2,
461 actid IN NUMBER,
462 funcmode IN VARCHAR2,
463 result OUT NOCOPY VARCHAR2) ;
464
465 PROCEDURE AbortProcess(p_trigger_id IN NUMBER,
466 p_itemtype IN VARCHAR2 DEFAULT NULL,
467 p_workflow_process IN VARCHAR2 DEFAULT NULL );
468
469 -- soagrawa 28-apr-2003 added the following APIs for redesign
470 PROCEDURE Trig_Type_Date(itemtype IN VARCHAR2,
471 itemkey IN VARCHAR2,
472 actid IN NUMBER,
473 funcmode IN VARCHAR2,
474 result OUT NOCOPY VARCHAR2) ;
475
476 PROCEDURE Action_Notification(itemtype IN VARCHAR2,
477 itemkey IN VARCHAR2,
478 actid IN NUMBER,
479 funcmode IN VARCHAR2,
480 result OUT NOCOPY VARCHAR2) ;
481
482 PROCEDURE Action_Execute(itemtype IN VARCHAR2,
483 itemkey IN VARCHAR2,
484 actid IN NUMBER,
485 funcmode IN VARCHAR2,
486 result OUT NOCOPY VARCHAR2) ;
487
488 PROCEDURE Get_Aval_Repeat_Sch(itemtype IN VARCHAR2,
489 itemkey IN VARCHAR2,
490 actid IN NUMBER,
491 funcmode IN VARCHAR2,
492 result OUT NOCOPY VARCHAR2) ;
493
494 PROCEDURE Event_Custom_action(itemtype IN VARCHAR2,
495 itemkey IN VARCHAR2,
496 actid IN NUMBER,
497 funcmode IN VARCHAR2,
498 result OUT NOCOPY VARCHAR2) ;
499
500 PROCEDURE Event_Trig_Next(itemtype IN VARCHAR2,
501 itemkey IN VARCHAR2,
502 actid IN NUMBER,
503 funcmode IN VARCHAR2,
504 result OUT NOCOPY VARCHAR2) ;
505
506 PROCEDURE Wf_Init_var(itemtype IN VARCHAR2,
507 itemkey IN VARCHAR2,
508 actid IN NUMBER,
509 funcmode IN VARCHAR2,
510 result OUT NOCOPY VARCHAR2) ;
511
512 -- end soagrawa
513
514 PROCEDURE Check_Trig_Exist(itemtype IN VARCHAR2,
515 itemkey IN VARCHAR2,
516 actid IN NUMBER,
517 funcmode IN VARCHAR2,
518 result OUT NOCOPY VARCHAR2) ;
519
520
521 --========================================================================
522 -- PROCEDURE
523 -- purge_history
524 -- Purpose
525 -- Purges monitor history. Called by Concurrent Program.
526 -- HISTORY
527 -- 20-Aug-2005 soagrawa Created.
528 --
529 --========================================================================
530
531 PROCEDURE purge_history(
532 errbuf OUT NOCOPY VARCHAR2,
533 retcode OUT NOCOPY NUMBER,
534 trig_end_date_days IN NUMBER := 60
535 );
536
537 END AMS_WFTrig_PVT ;