1 PACKAGE ams_approval_pvt AUTHID CURRENT_USER as
2 /* $Header: amsvapps.pls 120.3 2007/08/09 10:00:45 rsatyava ship $ */
3
4 TYPE appr_cursor is REF CURSOR;
5
6 ---------------------------------------------------------------------------------
7 --
8 -- Procedure
9 -- Start_LineApproval
10 --
11 -- Description
12 -- get details of budget line(s)
13 -- IN
14 -- p_budget_source_id - Budget line source identifier
15 -- p_budget_source_type - Budget line soucre arc qualifier
16 -- p_activity_source_id - Activity source identifier
17 -- p_activity_source_type - Activity source qualifier
18 -- p_parent_process_flag - Indicate if process started from a parent process
19 --
20 ---------------------------------------------------------------------------------
21 PROCEDURE Start_LineApproval(
22 p_api_version IN NUMBER
23 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
24 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
25 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
26 ,x_return_status OUT NOCOPY VARCHAR2
27 ,x_msg_data OUT NOCOPY VARCHAR2
28 ,x_msg_count OUT NOCOPY NUMBER
29 ,p_user_id IN NUMBER
30 ,p_act_budget_id IN NUMBER
31 ,p_orig_status_id IN NUMBER
32 ,p_new_status_id IN NUMBER
33 ,p_rejected_status_id IN NUMBER
34 ,p_parent_process_flag IN VARCHAR2 := FND_API.G_FALSE
35 ,p_parent_process_key IN VARCHAR2 -- was g_miss_char
36 ,p_parent_context IN VARCHAR2 -- was g_miss_char
37 ,p_parent_approval_flag IN VARCHAR2 := FND_API.G_FALSE
38 ,p_continue_flow IN VARCHAR2 := FND_API.G_FALSE );
39 ---------------------------------------------------------------------------------
40 --
41 -- Procedure
42 -- Start_Line_Approval
43 --
44 -- Workflow cover: Get line details for an activity and start process for lines not acted on
45 -- IN
46 -- itemtype - A valid item type from (WF_ITEM_TYPES table).
47 -- itemkey - A string generated from the application object's primary key.
48 -- actid - The function activity(instance id).
49 -- funcmode - Run/Cancel/Timeout
50 -- OUT
51 -- Resultout - 'COMPLETE:Y' If there is a parent process that started this process
52 -- - 'COMPLETE:N' If there is no parent process and this process can end
53 --
54 -- USED BY ACTIVITIES
55 -- <ITEM_TYPE> <ACTIVITY>
56 -- AMS_APPROVAL_PVT AMS_START_LINE_APPROVAL
57 --
58 ---------------------------------------------------------------------------------
59 PROCEDURE Start_Line_Approval( itemtype in varchar2,
60 itemkey in varchar2,
61 actid in number,
62 funcmode in varchar2,
63 resultout OUT NOCOPY varchar2 );
64 ---------------------------------------------------------------------------------
65 --
66 -- Procedure
67 -- Get_Line_Approver_Details
68 --
69 -- Workflow cover: Get and set the details for a budget line to start approval process
70 -- IN
71 -- itemtype - A valid item type from (WF_ITEM_TYPES table).
72 -- itemkey - A string generated from the application object's primary key.
73 -- actid - The function activity(instance id).
74 -- funcmode - Run/Cancel/Timeout
75 -- OUT
76 -- Resultout - 'COMPLETE:' after setting the approver details
77 --
78 -- USED BY ACTIVITIES
79 -- <ITEM_TYPE> <ACTIVITY>
80 -- AMS_APPROVAL_PVT AMS_GET_APPROVER_DETAILS
81 --
82 ---------------------------------------------------------------------------------
83 PROCEDURE Get_Line_Approver_Details( itemtype in varchar2,
84 itemkey in varchar2,
85 actid in number,
86 funcmode in varchar2,
87 resultout OUT NOCOPY varchar2 );
88 --------------------------------------------------------------------------------
89 --
90 -- Procedure
91 -- Check_Line_Further_Approval
92 --
93 -- Workflow cover: Check if line needs further approvals
94 -- IN
95 -- itemtype - A valid item type from (WF_ITEM_TYPES table).
96 -- itemkey - A string generated from the application object's primary key.
97 -- actid - The function activity(instance id).
98 -- funcmode - Run/Cancel/Timeout
99 -- OUT
100 -- Resultout - 'COMPLETE:Y' If line needs further approvals
101 -- - 'COMPLETE:N' If line does not need further approvals
102 --
103 -- USED BY ACTIVITIES
104 -- <ITEM_TYPE> <ACTIVITY>
105 -- AMS_APPROVAL_PVT AMS_GET_APPROVER_DETAILS
106 --
107 ---------------------------------------------------------------------------------
108 PROCEDURE Check_Line_Further_Approval( itemtype in varchar2,
109 itemkey in varchar2,
110 actid in number,
111 funcmode in varchar2,
112 resultout OUT NOCOPY varchar2 );
113 --------------------------------------------------------------------------------
114 --
115 -- Procedure
116 -- Approve_Budget_Line
117 --
118 -- Workflow cover: Approve a budget line
119 -- IN
120 -- itemtype - A valid item type from (WF_ITEM_TYPES table).
121 -- itemkey - A string generated from the application object's primary key.
122 -- actid - The function activity(instance id).
123 -- funcmode - Run/Cancel/Timeout
124 -- OUT
125 -- Resultout - 'COMPLETE:Y' If line needs further approvals
126 -- - 'COMPLETE:N' If line does not need further approvals
127 --
128 -- USED BY ACTIVITIES
129 -- <ITEM_TYPE> <ACTIVITY>
130 -- AMS_APPROVAL_PVT AMS_APPROVE_BUDGET_LINE
131 --
132 ---------------------------------------------------------------------------------
133 PROCEDURE Approve_Budget_Line(itemtype in varchar2,
134 itemkey in varchar2,
135 actid in number,
136 funcmode in varchar2,
137 resultout OUT NOCOPY varchar2 );
138 --------------------------------------------------------------------------------
139 --
140 -- Procedure
141 -- Reject_Budget_Line
142 --
143 -- Workflow cover: Reject a budget line
144 -- IN
145 -- itemtype - A valid item type from (WF_ITEM_TYPES table).
146 -- itemkey - A string generated from the application object's primary key.
147 -- actid - The function activity(instance id).
148 -- funcmode - Run/Cancel/Timeout
149 -- OUT
150 -- Resultout - 'COMPLETE:Y' If line needs further approvals
151 -- - 'COMPLETE:N' If line does not need further approvals
152 --
153 -- USED BY ACTIVITIES
154 -- <ITEM_TYPE> <ACTIVITY>
155 -- AMS_APPROVAL_PVT AMS_REJECT_BUDGET_LINE
156 --
157 ---------------------------------------------------------------------------------
158 PROCEDURE Reject_Budget_Line( itemtype in varchar2,
159 itemkey in varchar2,
160 actid in number,
161 funcmode in varchar2,
162 resultout OUT NOCOPY varchar2 );
163 --------------------------------------------------------------------------------
164 --
165 -- Procedure
166 -- Is_Parent_Waiting
167 --
168 -- Workflow cover: Check if there is a parent procoess waiting for further process
169 -- IN
170 -- itemtype - A valid item type from (WF_ITEM_TYPES table).
171 -- itemkey - A string generated from the application object's primary key.
172 -- actid - The function activity(instance id).
173 -- funcmode - Run/Cancel/Timeout
174 -- OUT
175 -- Resultout - 'COMPLETE:Y' If there is a parent process that started this process
176 -- - 'COMPLETE:N' If there is no parent process and this process can end
177 --
178 -- USED BY ACTIVITIES
179 -- <ITEM_TYPE> <ACTIVITY>
180 -- AMS_APPROVAL_PVT AMS_PARENT_EXISTS
181 --
182 --------------------------------------------------------------------------------
183 PROCEDURE Is_Parent_Waiting (itemtype in varchar2,
184 itemkey in varchar2,
185 actid in number,
186 funcmode in varchar2,
187 resultout OUT NOCOPY varchar2 );
188 --------------------------------------------------------------------------------
189 --
190 -- Procedure
191 -- Check_Line_Approval_Rule
192 --
193 -- Workflow cover: Check if approval rule is met after action on a particular line
194 -- IN
195 -- itemtype - A valid item type from (WF_ITEM_TYPES table).
196 -- itemkey - A string generated from the application object's primary key.
197 -- actid - The function activity(instance id).
198 -- funcmode - Run/Cancel/Timeout
199 -- OUT
200 -- Resultout - 'COMPLETE:Y' If there is a parent process that started this process
201 -- - 'COMPLETE:N' If there is no parent process and this process can end
202 --
203 -- USED BY ACTIVITIES
204 -- <ITEM_TYPE> <ACTIVITY>
205 -- AMS_APPROVAL_PVT AMS_CHECK_LINE_APPROVAL_RULE
206 --
207 --------------------------------------------------------------------------------
208 PROCEDURE Check_Line_Approval_Rule (itemtype in varchar2,
209 itemkey in varchar2,
210 actid in number,
211 funcmode in varchar2,
212 resultout OUT NOCOPY varchar2 );
213 --------------------------------------------------------------------------------
214 --
215 -- Procedure
216 -- Check_More_Line_Remaining
217 --
218 -- Workflow cover: Check if more lines are waiting for approval
219 -- IN
220 -- itemtype - A valid item type from (WF_ITEM_TYPES table).
221 -- itemkey - A string generated from the application object's primary key.
222 -- actid - The function activity(instance id).
223 -- funcmode - Run/Cancel/Timeout
224 -- OUT
225 -- Resultout - 'COMPLETE:Y' If there is a parent process that started this process
226 -- - 'COMPLETE:N' If there is no parent process and this process can end
227 --
228 -- USED BY ACTIVITIES
229 -- <ITEM_TYPE> <ACTIVITY>
230 -- AMS_APPROVAL_PVT AMS_MORE_LINES_REMAINING
231 --
232 --------------------------------------------------------------------------------
233 PROCEDURE Check_More_Lines_Remaining (itemtype in varchar2,
234 itemkey in varchar2,
235 actid in number,
236 funcmode in varchar2,
237 resultout OUT NOCOPY varchar2 );
238 --------------------------------------------------------------------------------
239 --
240 -- Procedure
241 -- Can_Continue_Flow
242 --
243 -- Workflow cover: Check if this process can continue the flow of the main process
244 -- IN
245 -- itemtype - A valid item type from (WF_ITEM_TYPES table).
246 -- itemkey - A string generated from the application object's primary key.
247 -- actid - The function activity(instance id).
248 -- funcmode - Run/Cancel/Timeout
249 -- OUT
250 -- Resultout - 'COMPLETE:Y' If there is a parent process that started this process
251 -- - 'COMPLETE:N' If there is no parent process and this process can end
252 --
253 -- USED BY ACTIVITIES
254 -- <ITEM_TYPE> <ACTIVITY>
255 -- AMS_APPROVAL_PVT AMS_PARENT_EXISTS
256 --
257 --------------------------------------------------------------------------------
258 PROCEDURE Can_Continue_Flow (itemtype in varchar2,
259 itemkey in varchar2,
260 actid in number,
261 funcmode in varchar2,
262 resultout OUT NOCOPY varchar2 );
263 --------------------------------------------------------------------------------
264 --
265 -- Procedure
266 -- Continue_Parent_Process
267 --
268 -- Workflow cover: continues the parent process from the block state
269 -- IN
270 -- itemtype - A valid item type from (WF_ITEM_TYPES table).
271 -- itemkey - A string generated from the application object's primary key.
272 -- actid - The function activity(instance id).
273 -- funcmode - Run/Cancel/Timeout
274 -- OUT
275 -- Resultout - 'COMPLETE:' none
276 --
277 -- USED BY ACTIVITIES
278 -- <ITEM_TYPE> <ACTIVITY>
279 -- AMS_APPROVAL_PVT AMS_CONTINUE_PARENT
280 --
281 --------------------------------------------------------------------------------
282 PROCEDURE Continue_Parent_Process (itemtype in varchar2,
283 itemkey in varchar2,
284 actid in number,
285 funcmode in varchar2,
286 resultout OUT NOCOPY varchar2 );
287 --------------------------------------------------------------------------------
288
289 -- Start of Comments
290 --
291 -- NAME
292 -- StartProcess
293 --
294 -- PURPOSE
295 -- This Procedure will Start the flow for the Approval Process
296 --
297 --
298 -- IN
299 -- p_approval_for IN Approval for Object (i.e. CAMP/EVEH,...)
300 -- p_approval_for_id IN Approval for Objectid (i.e. CAMP_id,...)
301 -- p_object_version_number IN Object Version Number
302 -- p_orig_stat_id IN Original User Status Id(e.g. id for 'NEW')
303 -- p_new_stat_id IN New User Status Id(e.g. id for 'AVAILABLE')
304 -- p_requester_userid IN Userid
305 -- p_workflowprocess IN WF Process Name (Default Null)
306 -- p_item_type IN WF Item type(Default Null)
307 -- OUT
308 --
309 -- Used By Activities
310 --
311 -- NOTES
312 -- HISTORY
313 -- End of Comments
314 PROCEDURE StartProcess
315 (p_activity_type IN VARCHAR2,
316 p_activity_id IN NUMBER,
317 p_approval_type IN VARCHAR2,
318 p_object_version_number IN NUMBER,
319 p_orig_stat_id IN NUMBER,
320 p_new_stat_id IN NUMBER,
321 p_reject_stat_id IN NUMBER,
322 p_requester_userid IN NUMBER,
323 p_notes_from_requester IN VARCHAR2 DEFAULT NULL,
327
324 p_workflowprocess IN VARCHAR2 DEFAULT NULL,
325 p_item_type IN VARCHAR2 DEFAULT NULL
326 );
328 /*****************************************************************
329
330 -- Start of Comments
331 --
332 -- NAME
333 -- set_activity_details
334 --
335 -- PURPOSE
336 -- This Procedure will set the workflow attributes for the
337 -- details of the activity. These Attributes will be used
338 -- throught the process espacially in Notifications.
339 -- It will also appropriate Approvers are availables for
340 -- the approvals seeked
341 --
342 -- Return - Success if the process is successful
343 -- - Error If the process is errored out
344 --
345 -- IN
346 -- Itemtype - AMSAPPR
347 -- Accid - Activity ID
348 -- Funmode - Run/Cancel/Timeout
349 --
350 -- OUT
351 -- Result - 'COMPLETE:AMS_SUCCESS' If the Process is successful
352 -- - 'COMPLETE:AMS_ERROR' If the Process is errored out
353 --
354 -- Used By Activities
355 -- Item Type - AMSAPPR
356 -- Activity - AMS_SET_ACT_DETAILS
357 --
358 -- NOTES
359 --
360 --
361 -- HISTORY
362 -- End of Comments
363 *****************************************************************/
364
365 PROCEDURE Set_Activity_Details(itemtype IN VARCHAR2,
366 itemkey IN VARCHAR2,
367 actid IN NUMBER,
368 funcmode IN VARCHAR2,
369 resultout OUT NOCOPY VARCHAR2) ;
370
371
372 PROCEDURE Revert_Status( itemtype in varchar2,
373 itemkey in varchar2,
374 actid in number,
375 funcmode in varchar2,
376 resultout OUT NOCOPY varchar2 );
377 PROCEDURE Approve_Activity_status( itemtype in varchar2,
378 itemkey in varchar2,
379 actid in number,
380 funcmode in varchar2,
381 resultout OUT NOCOPY varchar2 );
382 PROCEDURE Reject_Activity_status( itemtype in varchar2,
383 itemkey in varchar2,
384 actid in number,
385 funcmode in varchar2,
386 resultout OUT NOCOPY varchar2 );
387 PROCEDURE Check_Budget_Lines( itemtype in varchar2,
388 itemkey in varchar2,
389 actid in number,
390 funcmode in varchar2,
391 resultout OUT NOCOPY varchar2 );
392
393 PROCEDURE Check_Approval_rules( itemtype in varchar2,
394 itemkey in varchar2,
395 actid in number,
396 funcmode in varchar2,
397 resultout OUT NOCOPY varchar2 );
398 PROCEDURE Prepare_Doc( itemtype in varchar2,
399 itemkey in varchar2,
400 actid in number,
401 funcmode in varchar2,
402 resultout OUT NOCOPY varchar2 );
403
404 PROCEDURE Set_Approver_Details( itemtype in varchar2,
405 itemkey in varchar2,
406 actid in number,
407 funcmode in varchar2,
408 resultout OUT NOCOPY varchar2 );
409 PROCEDURE Set_Further_Approvals( itemtype in varchar2,
410 itemkey in varchar2,
411 actid in number,
412 funcmode in varchar2,
413 resultout OUT NOCOPY varchar2 );
414 /*****************************************************************
415 -- Start of Comments
416 --
417 -- NAME
418 -- AbortProcess
419 --
420 -- PURPOSE
421 -- This Procedure will abort the process of Approvals
422 --
423 -- Used By Activities
424 --
425 -- NOTES
426 --
427 --
428 -- HISTORY
429 -- End of Comments
430 *****************************************************************/
431
432 PROCEDURE AbortProcess
433 (p_itemkey IN VARCHAR2
434 ,p_workflowprocess IN VARCHAR2 DEFAULT NULL
435 ,p_itemtype IN VARCHAR2 DEFAULT NULL
436 );
437
438
439 Type ObjRecTyp is RECORD
440 ( name VARCHAR2(240),
441 business_unit_id NUMBER(15),
442 country_code VARCHAR2(30),
443 setup_type_id NUMBER,
444 total_header_amount NUMBER,
445 org_id NUMBER,
446 object_type VARCHAR2(30),
447 priority VARCHAR2(30),
448 start_date DATE,
449 end_date DATE,
450 purpose VARCHAR2(30),
451 description VARCHAR2(4000),
455 source_code varchar2(30),
452 owner_id NUMBER,
453 currency varchar2(10) ,
454 priority_desc varchar2(80),
456 parent_source_code VARCHAR2(30),
457 Parent_name VARCHAR2(240)
458 );
459
460
461 --------------------------------------------------------------------------------
462
463 -- Start of Comments
464 --
465 -- NAME
466 -- DelvStartProcess
467 --
468 -- PURPOSE
469 -- This Procedure will Start the flow for the Delivarable Cancellation Process
470 --
471 --
472 -- IN
473
474 -- p_deliverable_id IN Delivarable id
475 -- p_deliverable_name IN Delivarable Name
476 -- p_object_version_number IN Object Version Number
477 -- p_usedby_object_id IN Used by object id(Ex: Event/Campaign using this deliverable)
478 -- p_usedby_object_name IN Used by object Name(Ex: Event/Campaign using this deliverable)
479 -- p_usedby_object_type_name IN Object Type Name(Ex: Event/Campaign)
480 -- p_requester_userid IN Userid
481 -- p_deliverable_userid IN Deliverable user id(Ex: owner of the Event using deliverable)
482 -- p_workflowprocess IN WF Process Name (AMSAPRV)
483 -- p_item_type IN WF Item type(AMS_DELV_CANCELLATION)
484 -- OUT
485 --
486 -- Used By Activities
487 --
488 -- NOTES
489 -- HISTORY
490 -- End of Comments
491
492 PROCEDURE DelvStartProcess
493 (p_deliverable_id IN NUMBER,
494 p_deliverable_name IN VARCHAR2,
495 p_object_version_number IN NUMBER,
496 p_usedby_object_id IN NUMBER,
497 p_usedby_object_name IN VARCHAR2,
498 p_usedby_object_type_name IN VARCHAR2,
499 p_requester_userid IN NUMBER,
500 p_deliverable_userid IN NUMBER,
501 p_workflowprocess IN VARCHAR2 DEFAULT 'AMSAPRV',
502 p_item_type IN VARCHAR2 DEFAULT 'AMS_DELV_CANCELLATION'
503 );
504
505 PROCEDURE RECONCILE_BUDGET_LINE(itemtype in varchar2,
506 itemkey in varchar2,
507 actid in number,
508 funcmode in varchar2,
509 resultout OUT NOCOPY varchar2 );
510
511 PROCEDURE Revert_Budget_Line(itemtype in varchar2,
512 itemkey in varchar2,
513 actid in number,
514 funcmode in varchar2,
515 resultout OUT NOCOPY varchar2 );
516
517 PROCEDURE Validate_Object_Budget_WF(itemtype in varchar2,
518 itemkey in varchar2,
519 actid in number,
520 funcmode in varchar2,
521 resultout OUT NOCOPY varchar2);
522
523 PROCEDURE Approval_Required(itemtype IN VARCHAR2,
524 itemkey IN VARCHAR2,
525 actid IN NUMBER,
526 funcmode IN VARCHAR2,
527 resultout OUT NOCOPY VARCHAR2);
528
529 PROCEDURE Auto_Approve (itemtype IN VARCHAR2,
530 itemkey IN VARCHAR2,
531 actid IN NUMBER,
532 funcmode IN VARCHAR2,
533 resultout OUT NOCOPY VARCHAR2);
534
535 PROCEDURE Get_Approval_Rule ( p_activity_id IN NUMBER,
536 p_activity_type IN VARCHAR2,
537 p_approval_type IN VARCHAR2,
538 p_act_budget_id IN NUMBER,
539 x_approval_detail_Id OUT NOCOPY NUMBER,
540 x_return_status OUT NOCOPY VARCHAR2);
541
542 PROCEDURE PostNotif_Update (itemtype IN VARCHAR2,
543 itemkey IN VARCHAR2,
544 actid IN NUMBER,
545 funcmode IN VARCHAR2,
546 resultout OUT NOCOPY VARCHAR2);
547
548 PROCEDURE Validate_Object_Budget_All_WF(itemtype in varchar2,
549 itemkey in varchar2,
550 actid in number,
551 funcmode in varchar2,
552 resultout OUT NOCOPY varchar2);
553
554 PROCEDURE Bypass_Approval (itemtype IN VARCHAR2,
555 itemkey IN VARCHAR2,
556 actid IN NUMBER,
557 funcmode IN VARCHAR2,
558 resultout OUT NOCOPY VARCHAR2);
559
560 PROCEDURE must_preview (p_activity_id IN NUMBER,
561 p_activity_type IN VARCHAR2,
562 p_approval_type IN VARCHAR2,
563 p_act_budget_id IN NUMBER,
564 p_requestor_id IN NUMBER,
565 x_must_preview OUT NOCOPY VARCHAR2,
566 x_return_status OUT NOCOPY VARCHAR2);
567
568 PROCEDURE Check_Object_Type( itemtype in varchar2,
569 itemkey in varchar2,
570 actid in number,
571 funcmode in varchar2,
572 resultout OUT NOCOPY varchar2);
573 END ams_approval_pvt;