1 PACKAGE AMS_WFCmpApr_PVT AUTHID CURRENT_USER AS
2 /* $Header: amsvwcas.pls 115.9 2002/12/02 20:30:58 dbiswas ship $*/
3
4
5 -- Start of Comments
6 --
7 -- NAME
8 -- AMS_WFCmpApr_PVT
9 --
10 -- PURPOSE
11 -- This package performs contains the workflow procedures for
12 -- Activity Approvals(Campaign Approvals) in Oracle Marketing
13 --
14 -- HISTORY
15 -- 09/21/1999 ptendulk CREATED
16 -- 02-dec-2002 dbiswas NOCOPY and debug-level changes for performance
17 --
18
19 /*************************** PRIVATE ROUTINES *******************************/
20
21 -- Start of Comments
22 --
23 -- NAME
24 -- StartProcess
25 --
26 -- PURPOSE
27 -- This Procedure will Start the flow for the Approval Process
28 --
29 --
30 -- IN
31 -- p_approval_for IN Approval for Object (i.e. CAMP/EVEH,...)
32 -- p_approval_for_id IN Approval for Objectid (i.e. CAMP_id,...)
33 -- p_object_version_number IN Object Version Number
34 -- p_orig_stat_id IN Original User Status Id(e.g. id for 'NEW')
35 -- p_new_stat_id IN New User Status Id(e.g. id for 'AVAILABLE')
36 -- p_requester_userid IN Userid
37 -- p_workflowprocess IN WF Process Name (Default Null)
38 -- p_item_type IN WF Item type(Default Null)
39 -- OUT
40 --
41 -- Used By Activities
42 --
43 -- NOTES
44 --
45 --
46 -- HISTORY
47 -- 09/21/1999 ptendulk created
48 -- End of Comments
49 PROCEDURE StartProcess
50 (p_approval_for IN VARCHAR2
51 ,p_approval_for_id IN NUMBER
52 ,p_object_version_number IN NUMBER
53 ,p_orig_stat_id IN NUMBER
54 ,p_new_stat_id IN NUMBER
55 ,p_requester_userid IN NUMBER
56 ,p_notes_from_requester IN VARCHAR2 DEFAULT NULL
57 ,p_workflowprocess IN VARCHAR2 DEFAULT NULL
58 ,p_item_type IN VARCHAR2 DEFAULT NULL
59 ) ;
60 -- Start of Comments
61 --
62 -- NAME
63 -- Selector
64 --
65 -- PURPOSE
66 -- This Procedure will determine which process to run
67 --
68 -- IN
69 -- itemtype - A Valid item type from (WF_ITEM_TYPES Table).
70 -- itemkey - A string generated from application object's primary key.
71 -- actid - The function Activity
72 -- funcmode - Run / Cancel
73 --
74 -- OUT
75 -- resultout - Name of workflow process to run
76 --
77 -- Used By Activities
78 --
79 -- NOTES
80 --
81 --
82 -- HISTORY
83 -- 08/13/1999 ptendulk created
84 -- 11/30/1999 ptendulk Modified
85 -- End of Comments
86
87
88 PROCEDURE Selector( itemtype IN VARCHAR2,
89 itemkey IN VARCHAR2,
90 actid IN NUMBER,
91 funcmode IN VARCHAR2,
92 resultout OUT NOCOPY VARCHAR2
93 );
94
95
96 -- Start of Comments
97 --
98 -- NAME
99 -- set_activity_details
100 --
101 -- PURPOSE
102 -- This Procedure will set the workflow attributes for the details of the activity
103 -- These Attributes will be used throught the process espacially in Notifications
104 -- It will also appropriate Approvers are availables for the approvals seeked
105 --
106 --
107 -- It will Return - Success if the process is successful
108 -- - Error If the process is errored out
109 --
110 -- IN
111 -- Itemtype - AMSAPPR
112 -- Itemkey - p_approver_for||p_approval_for_id||to_char(sysdate,'ddmmyyhhmiss')
113 -- Accid - Activity ID
114 -- Funmode - Run/Cancel/Timeout
115 --
116 -- OUT
117 -- Result - 'COMPLETE:AMS_SUCCESS' If the Process is successful
118 -- - 'COMPLETE:AMS_ERROR' If the Process is errored out
119 --
120 -- Used By Activities
121 -- Item Type - AMSAPPR
122 -- Activity - AMS_SET_ACT_DETAILS
123 --
124 -- NOTES
125 --
126 --
127 -- HISTORY
128 -- 08/20/1999 ptendulk created
129 -- 11/30/1999 ptendulk Modified
130 -- End of Comments
131
132 PROCEDURE Set_Activity_Details(itemtype IN VARCHAR2,
133 itemkey IN VARCHAR2,
134 actid IN NUMBER,
135 funcmode IN VARCHAR2,
136 result OUT NOCOPY VARCHAR2) ;
137
138
139 -- Start of Comments
140 --
141 -- NAME
142 -- Appr_Required_Check
143 --
144 -- PURPOSE
145 -- This Procedure will check whether the Approval is required or not
146 --
147 -- Itemtype - AMSAPPR
148 -- Itemkey - p_approver_for||p_approval_for_id||to_char(sysdate,'ddmmyyhhmiss')
149 -- Accid - Activity ID
150 -- Funmode - Run/Cancel/Timeout
151 --
152 -- OUT
153 -- Result - 'COMPLETE:Y' If the approval is required
154 -- - 'COMPLETE:N' If the approval is not required
155 --
156 -- Used By Activities
157 -- Item Type - AMSAPPR
158 -- Activity - AMS_CHECK_APPR
159 --
160 -- NOTES
161 --
162 --
163 -- HISTORY
164 -- 08/20/1999 ptendulk created
165 -- 11/30/1999 ptendulk Modified
166 -- End of Comments
167
168 PROCEDURE Appr_Required_Check (itemtype IN VARCHAR2,
169 itemkey IN VARCHAR2,
170 actid IN NUMBER,
171 funcmode IN VARCHAR2,
172 result OUT NOCOPY VARCHAR2) ;
173 -- Start of Comments
174 --
175 -- NAME
176 -- Update_Status_Na
177 --
178 -- PURPOSE
179 -- This Procedure will Update the Status of the Activity as the
180 -- Approval is not required
181 --
182 -- IN
183 -- Itemtype - AMSAPPR
184 -- Itemkey - p_approver_for||p_approval_for_id||to_char(sysdate,'ddmmyyhhmiss')
185 -- Accid - Activity ID
186 -- Funmode - Run/Cancel/Timeout
187 --
188 -- OUT
189 -- Result - COMPLETE:AMS_SUCCESS If the Process is Success.
190 -- COMPLETE:AMS_ERROR If the Process is errored out.
191 --
192 -- Used By Activities
193 -- Item Type - AMSAPPR
194 -- Activity - AMS_UPDATE_STATUS_NA
195 --
196 -- NOTES
197 --
198 --
199 -- HISTORY
200 -- 08/20/1999 ptendulk created
201 -- 11/30/1999 ptendulk Modified
202 -- End of Comments
203
204 PROCEDURE UPDATE_STATUS_NA (itemtype IN VARCHAR2,
205 itemkey IN VARCHAR2,
206 actid IN NUMBER,
207 funcmode IN VARCHAR2,
208 result OUT NOCOPY VARCHAR2) ;
209
210 -- Start of Comments
211 --
212 -- NAME
213 -- Theme_Appr_Req_Check
214 --
215 -- PURPOSE
216 -- This Procedure will check whether the Theme Approval is required or not
217 --
218 -- Itemtype - AMSAPPR
219 -- Itemkey - p_approver_for||p_approval_for_id||to_char(sysdate,'ddmmyyhhmiss')
220 -- Accid - Activity ID
221 -- Funmode - Run/Cancel/Timeout
222 --
223 -- OUT
224 -- Result - 'COMPLETE:Y' If the approval is required
225 -- - 'COMPLETE:N' If the approval is not required
226 --
227 -- Used By Activities
228 -- Item Type - AMSAPPR
229 -- Activity - AMS_CHECK_MAN_APPR
230 --
231 -- NOTES
232 --
233 --
234 -- HISTORY
235 -- 08/20/1999 ptendulk created
236 -- 11/30/1999 ptendulk Modified
237 -- End of Comments
238
239 PROCEDURE Theme_Appr_Req_Check (itemtype IN VARCHAR2,
240 itemkey IN VARCHAR2,
241 actid IN NUMBER,
242 funcmode IN VARCHAR2,
243 result OUT NOCOPY VARCHAR2) ;
244
245 -- Start of Comments
246 --
247 -- NAME
248 -- Create_Notif_Document
249 --
250 -- PURPOSE
251 -- This Procedure will create the Document to be sent for the Approvals
252 -- it will also Update the Status As the Activity as Submitted for Approvals
253 --
254 -- IN
255 -- Itemtype - AMSAPPR
256 -- Itemkey - p_approver_for||p_approval_for_id||to_char(sysdate,'ddmmyyhhmiss')
257 -- Accid - Activity ID
258 -- Funmode - Run/Cancel/Timeout
259 --
260 -- OUT
261 -- Result - COMPLETE:AMS_SUCCESS If the Process is Success.
262 -- COMPLETE:AMS_ERROR If the Process is errored out.
263 --
264 -- Used By Activities
265 -- Item Type - AMSAPPR
266 -- Activity - AMS_PREPARE_DOC
267 --
268 -- NOTES
269 --
270 --
271 -- HISTORY
272 -- 08/20/1999 ptendulk created
273 -- 11/30/1999 ptendulk Modified
274 -- End of Comments
275
276 PROCEDURE Create_Notif_Document(document_id in varchar2,
277 display_type in varchar2,
278 document in OUT NOCOPY varchar2,
279 document_type in OUT NOCOPY varchar2) ;
280 -- Start of Comments
281 --
282 -- NAME
283 -- Prepare_Doc
284 --
285 -- PURPOSE
286 -- This Procedure will create the Document to be sent for the Approvals
287 -- it will also Update the Status As the Activity as Submitted for Approvals
288 --
289 -- IN
290 -- Itemtype - AMSAPPR
291 -- Itemkey - p_approver_for||p_approval_for_id||to_char(sysdate,'ddmmyyhhmiss')
292 -- Accid - Activity ID
293 -- Funmode - Run/Cancel/Timeout
294 --
295 -- OUT
296 -- Result - COMPLETE:AMS_SUCCESS If the Process is Success.
297 -- COMPLETE:AMS_ERROR If the Process is errored out.
298 --
299 -- Used By Activities
300 -- Item Type - AMSAPPR
301 -- Activity - AMS_PREPARE_DOC
302 --
303 -- NOTES
304 --
305 --
306 -- HISTORY
307 -- 08/20/1999 ptendulk created
308 -- 11/30/1999 ptendulk Modified
309 -- End of Comments
310
311 PROCEDURE Prepare_Doc (itemtype IN VARCHAR2,
312 itemkey IN VARCHAR2,
313 actid IN NUMBER,
314 funcmode IN VARCHAR2,
315 result OUT NOCOPY VARCHAR2) ;
316
317 -- Start of Comments
318 --
319 -- NAME
320 -- Owner_Appr_Check
321 --
322 -- PURPOSE
323 -- This Procedure will check whether the Owner's Approval is required for the Theme
324 --
325 -- IN
326 -- Itemtype - AMSAPPR
327 -- Itemkey - p_approver_for||p_approval_for_id||to_char(sysdate,'ddmmyyhhmiss')
328 -- Accid - Activity ID
329 -- Funmode - Run/Cancel/Timeout
330 --
331 -- OUT
332 -- Result - 'COMPLETE:Y' If the Owner's Approval is required
333 -- - 'COMPLETE:N' If the Owner's Approval is not required
334 --
335 --
336 -- OUT
337 --
338 -- Used By Activities
339 -- Item Type - AMSAPPR
340 -- Activity - AMS_CHECK_OWN_APPR
341 -- NOTES
342 --
343 --
344 -- HISTORY
345 -- 09/13/1999 ptendulk created
346 -- 11/30/1999 ptendulk Modified
347 -- End of Comments
348
349 PROCEDURE Owner_Appr_Check (itemtype IN VARCHAR2,
350 itemkey IN VARCHAR2,
351 actid IN NUMBER,
352 funcmode IN VARCHAR2,
353 result OUT NOCOPY VARCHAR2) ;
354
355 -- Start of Comments
356 --
357 -- NAME
358 -- Update_Stat_ApprTA
359 --
360 -- PURPOSE
361 -- This Procedure will Update the Status of the Activity for Approval
362 --
363 -- IN
364 -- Itemtype - AMSAPPR
365 -- Itemkey - p_approver_for||p_approval_for_id||to_char(sysdate,'ddmmyyhhmiss')
366 -- Accid - Activity ID
367 -- Funmode - Run/Cancel/Timeout
368 --
369 -- OUT
370 -- Result - COMPLETE:AMS_SUCCESS If the Process is Success.
371 -- COMPLETE:AMS_ERROR If the Process is errored out.
372 --
373 -- Used By Activities
374 -- Item Type - AMSAPPR
375 -- Activity - AMS_UPDATE_STATUS_TA
376 --
377 -- NOTES
378 --
379 --
380 -- HISTORY
381 -- 08/20/1999 ptendulk created
382 -- 11/30/1999 ptendulk Modified
383 -- End of Comments
384
385 PROCEDURE Update_Stat_ApprTA (itemtype IN VARCHAR2,
386 itemkey IN VARCHAR2,
387 actid IN NUMBER,
388 funcmode IN VARCHAR2,
389 result OUT NOCOPY VARCHAR2) ;
390
391 -- Start of Comments
392 --
393 -- NAME
394 -- Update_Status_Rej
395 --
396 -- PURPOSE
397 -- This Procedure will Update the Status of the Activity for Rejection
398 --
399 -- IN
400 -- Itemtype - AMSAPPR
401 -- Itemkey - p_approver_for||p_approval_for_id||to_char(sysdate,'ddmmyyhhmiss')
402 -- Accid - Activity ID
403 -- Funmode - Run/Cancel/Timeout
404 --
405 -- OUT
406 -- Result - COMPLETE:AMS_SUCCESS If the Process is Success.
407 -- COMPLETE:AMS_ERROR If the Process is errored out.
408 --
409 -- Used By Activities
410 -- Item Type - AMSAPPR
411 -- Activity - AMS_UPDATE_STATUS_REJ
412 --
413 -- NOTES
414 --
415 --
416 -- HISTORY
417 -- 08/20/1999 ptendulk created
418 -- 11/30/1999 ptendulk Modified
419 -- End of Comments
420
421 PROCEDURE Update_Status_Rej (itemtype IN VARCHAR2,
422 itemkey IN VARCHAR2,
423 actid IN NUMBER,
424 funcmode IN VARCHAR2,
425 result OUT NOCOPY VARCHAR2) ;
426
427 -- Start of Comments
428 --
429 -- NAME
430 -- Revert_Status
431 --
432 -- PURPOSE
433 -- This Procedure will Revert the Status of the Activity Back to Original
434 --
435 -- IN
436 -- Itemtype - AMSAPPR
437 -- Itemkey - p_approver_for||p_approval_for_id||to_char(sysdate,'ddmmyyhhmiss')
438 -- Accid - Activity ID
439 -- Funmode - Run/Cancel/Timeout
440 --
441 -- OUT
442 -- Result - COMPLETE:AMS_SUCCESS If the Process is Success.
443 -- COMPLETE:AMS_ERROR If the Process is errored out.
444 --
445 -- Used By Activities
446 -- Item Type - AMSAPPR
447 -- Activity - AMS_REVERT_STATUS
448 --
449 -- NOTES
450 --
451 --
452 -- HISTORY
453 -- 08/20/1999 ptendulk created
454 -- 11/30/1999 ptendulk Modified
455 -- End of Comments
456
457 PROCEDURE Revert_Status (itemtype IN VARCHAR2,
458 itemkey IN VARCHAR2,
459 actid IN NUMBER,
460 funcmode IN VARCHAR2,
461 result OUT NOCOPY VARCHAR2) ;
462
463 -- Start of Comments
464 --
465 -- NAME
466 -- Fund_Appr_Req_Check
467 --
468 -- PURPOSE
469 -- This Procedure will check whether the Budget Approval is required or not
470 --
471 -- Itemtype - AMSAPPR
472 -- Itemkey - p_approver_for||p_approval_for_id||to_char(sysdate,'ddmmyyhhmiss')
473 -- Accid - Activity ID
474 -- Funmode - Run/Cancel/Timeout
475 --
476 -- OUT
477 -- Result - 'COMPLETE:Y' If the approval is required
478 -- - 'COMPLETE:N' If the approval is not required
479 --
480 -- Used By Activities
481 -- Item Type - AMSAPPR
482 -- Activity - AMS_CHECK_BUD_APPR
483 --
484 -- NOTES
485 --
486 --
487 -- HISTORY
488 -- 08/20/1999 ptendulk created
489 -- 11/30/1999 ptendulk Modified
490 -- End of Comments
491
492 PROCEDURE Fund_Appr_Req_Check (itemtype IN VARCHAR2,
493 itemkey IN VARCHAR2,
494 actid IN NUMBER,
495 funcmode IN VARCHAR2,
496 result OUT NOCOPY VARCHAR2) ;
497 -- Start of Comments
498 --
499 -- NAME
500 -- Ba_Owner_Appr_Check
501 --
502 -- PURPOSE
503 -- This Procedure will check whether the Owner's Approval is required for the Budget
504 --
505 -- IN
506 -- Itemtype - AMSAPPR
507 -- Itemkey - p_approver_for||p_approval_for_id||to_char(sysdate,'ddmmyyhhmiss')
508 -- Accid - Activity ID
509 -- Funmode - Run/Cancel/Timeout
510 --
511 -- OUT
512 -- Result - 'COMPLETE:Y' If the Owner's Approval is required
513 -- - 'COMPLETE:N' If the Owner's Approval is not required
514 --
515 --
516 -- OUT
517 --
518 -- Used By Activities
519 -- Item Type - AMSAPPR
520 -- Activity - AMS_CHECK_BA_OWN_APPR
521 -- NOTES
522 --
523 --
524 -- HISTORY
525 -- 09/13/1999 ptendulk created
526 -- 11/30/1999 ptendulk Modified
527 -- End of Comments
528
529 PROCEDURE Ba_Owner_Appr_Check (itemtype IN VARCHAR2,
530 itemkey IN VARCHAR2,
531 actid IN NUMBER,
532 funcmode IN VARCHAR2,
533 result OUT NOCOPY VARCHAR2);
534 -- Start of Comments
535 --
536 -- NAME
537 -- Update_Stat_ApprBA
538 --
539 -- PURPOSE
540 -- This Procedure will Update the Status of the Activity for Approval
541 --
542 -- IN
543 -- Itemtype - AMSAPPR
544 -- Itemkey - p_approver_for||p_approval_for_id||to_char(sysdate,'ddmmyyhhmiss')
545 -- Accid - Activity ID
546 -- Funmode - Run/Cancel/Timeout
547 --
548 -- OUT
549 -- Result - COMPLETE:AMS_SUCCESS If the Process is Success.
550 -- COMPLETE:AMS_ERROR If the Process is errored out.
551 --
552 -- Used By Activities
553 -- Item Type - AMSAPPR
554 -- Activity - AMS_UPDATE_STATUS_BA
555 --
556 -- NOTES
557 --
558 --
559 -- HISTORY
560 -- 08/20/1999 ptendulk created
561 -- 11/30/1999 ptendulk Modified
562 -- End of Comments
563
564 PROCEDURE Update_Stat_ApprBA (itemtype IN VARCHAR2,
565 itemkey IN VARCHAR2,
566 actid IN NUMBER,
567 funcmode IN VARCHAR2,
568 result OUT NOCOPY VARCHAR2) ;
569
570
571 -- Start of Comments
572 --
573 -- NAME
574 -- AbortProcess
575 --
576 -- PURPOSE
577 -- This Procedure will abort the process of Approvals
578 --
579 -- Used By Activities
580 --
581 -- NOTES
582 --
583 --
584 -- HISTORY
585 -- 09/13/1999 ptendulk created
586 -- 11/30/1999 ptendulk Modified
587 -- End of Comments
588 PROCEDURE AbortProcess
589 (p_itemkey IN VARCHAR2
590 ,p_workflowprocess IN VARCHAR2 DEFAULT NULL
591 ,p_itemtype IN VARCHAR2 DEFAULT NULL
592 );
593 END AMS_WFCmpApr_PVT ;
[Home] [Help]