[Home] [Help]
PACKAGE BODY: APPS.AMS_GEN_APPROVAL_PVT
Source
1 PACKAGE BODY ams_gen_approval_pvt as
2 /* $Header: amsvgapb.pls 120.2 2008/01/07 10:36:56 amlal ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'ams_gen_approval_pvt';
5 G_ITEMTYPE CONSTANT varchar2(30) := 'AMSGAPP';
6
7 /*************************** PRIVATE ROUTINES *******************************/
8 -------------------------------------------------------------------------------
9 --
10 -- Attach Notes to Activity
11 -- Update Object Attributes table if notes are added
12 -- Note Added will be of Type Approval
13 --
14 -------------------------------------------------------------------------------
15 PROCEDURE Update_Note(p_activity_type IN VARCHAR2,
16 p_activity_id IN NUMBER,
17 p_note IN VARCHAR2,
18 p_user IN number,
19 x_msg_count OUT NOCOPY NUMBER,
20 x_msg_data OUT NOCOPY VARCHAR2,
21 x_return_status OUT NOCOPY VARCHAR2)
22 IS
23 l_id NUMBER ;
24 l_user NUMBER;
25 CURSOR c_resource IS
26 SELECT user_id user_id
27 FROM ams_jtf_rs_emp_v
28 WHERE resource_id = p_user ;
29
30 BEGIN
31 OPEN c_resource ;
32 FETCH c_resource INTO l_user ;
33 IF c_resource%NOTFOUND THEN
34 FND_MESSAGE.Set_Name('AMS','AMS_API_DEBUG_MESSAGE');
35 FND_MESSAGE.Set_Token('ROW', sqlerrm );
36 FND_MSG_PUB.Add;
37 END IF;
38 CLOSE c_resource ;
39 -- Note API to Update Approval Notes
40 AMS_ObjectAttribute_PVT.modify_object_attribute(
41 p_api_version => 1.0,
42 p_init_msg_list => FND_API.g_false,
43 p_commit => FND_API.g_false,
44 p_validation_level => FND_API.g_valid_level_full,
45 x_return_status => x_return_status,
46 x_msg_count => x_msg_count,
47 x_msg_data => x_msg_data,
48 p_object_type => p_activity_type,
49 p_object_id => p_activity_id ,
50 p_attr => 'NOTE',
51 p_attr_defined_flag => 'Y'
52 );
53 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
54 FND_MESSAGE.Set_Name('AMS','AMS_API_DEBUG_MESSAGE');
55 FND_MESSAGE.Set_Token('ROW', sqlerrm );
56 FND_MSG_PUB.Add;
57 END IF;
58
59 JTF_NOTES_PUB.Create_note(
60 p_api_version => 1.0 ,
61 x_return_status => x_return_status,
62 x_msg_count => x_msg_count,
63 x_msg_data => x_msg_data,
64 p_source_object_id => p_activity_id,
65 p_source_object_code => 'AMS_'||p_activity_type,
66 p_notes => p_note,
67 p_note_status => NULL ,
68 p_entered_by => l_user , -- 1000050 , -- FND_GLOBAL.USER_ID,
69 p_entered_date => sysdate,
70 p_last_updated_by => l_user , -- 1000050 , -- FND_GLOBAL.USER_ID,
71 x_jtf_note_id => l_id ,
72 p_note_type => 'AMS_APPROVAL' ,
73 p_last_update_date => SYSDATE ,
74 p_creation_date => SYSDATE ) ;
75 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
76 FND_MESSAGE.Set_Name('AMS','AMS_API_DEBUG_MESSAGE');
77 FND_MESSAGE.Set_Token('ROW', sqlerrm );
78 FND_MSG_PUB.Add;
79 END IF;
80 END Update_Note;
81
82 -------------------------------------------------------------------------------
83 -- Start of Comments
84 -- NAME
85 -- Get_User_Role
86 --
87 -- PURPOSE
88 -- This Procedure will be return the User role for
89 -- the userid sent
90 -- Called By
91 -- NOTES
92 -- End of Comments
93 -------------------------------------------------------------------------------
94 PROCEDURE Get_User_Role(
95 p_user_id IN NUMBER,
96 x_role_name OUT NOCOPY VARCHAR2,
97 x_role_display_name OUT NOCOPY VARCHAR2 ,
98 x_return_status OUT NOCOPY VARCHAR2)
99 IS
100 CURSOR c_resource IS
101 SELECT employee_id source_id
102 FROM ams_jtf_rs_emp_v
103 WHERE resource_id = p_user_id ;
104 l_person_id number;
105 BEGIN
106 x_return_status := FND_API.G_RET_STS_SUCCESS;
107 OPEN c_resource ;
108 FETCH c_resource INTO l_person_id ;
109 IF c_resource%NOTFOUND THEN
110 x_return_status := FND_API.G_RET_STS_ERROR;
111 FND_MESSAGE.Set_Name('AMS','AMS_APPR_INVALID_RESOURCE_ID');
112 FND_MSG_PUB.Add;
113 END IF;
114 CLOSE c_resource ;
115 -- Pass the Employee ID to get the Role
116 WF_DIRECTORY.getrolename(
117 p_orig_system => 'PER',
118 p_orig_system_id => l_person_id ,
119 p_name => x_role_name,
120 p_display_name => x_role_display_name );
121 IF x_role_name is null then
122 x_return_status := FND_API.G_RET_STS_ERROR;
123 FND_MESSAGE.Set_Name('AMS','AMS_APPR_INVALID_ROLE');
124 FND_MSG_PUB.Add;
125 END IF;
126 END Get_User_Role;
127 -------------------------------------------------------------------
128 -- Get the forward/reassigned resource details
129 -------------------------------------------------------------------
130 PROCEDURE Get_New_Res_Details(p_responder IN VARCHAR2,
131 x_resource_id OUT NOCOPY NUMBER,
132 x_resource_disp_name OUT NOCOPY VARCHAR2,
133 x_return_status OUT NOCOPY VARCHAR2)
134
135 IS
136 l_return_status VARCHAR2(1) := Fnd_Api.G_RET_STS_SUCCESS;
137 l_email VARCHAR2(360);
138 l_notif_pref VARCHAR2(30);
139 l_language VARCHAR2(30);
140 l_territory VARCHAR2(30);
141
142 CURSOR c_res_details(p_user_name IN VARCHAR2) IS
143 SELECT r.resource_id
144 FROM fnd_user f, ams_jtf_rs_emp_v r
145 WHERE r.user_id = f.user_id
146 AND f.user_name = p_user_name;
147
148 BEGIN
149
150 wf_directory.getroleinfo(role => p_responder,
151 display_name => x_resource_disp_name,
152 email_address => l_email,
153 notification_preference => l_notif_pref,
154 language => l_language,
155 territory => l_territory);
156
157
158 OPEN c_res_details(p_responder);
159 FETCH c_res_details INTO x_resource_id;
160 IF c_res_details%NOTFOUND THEN
161 l_return_status := Fnd_Api.G_RET_STS_ERROR;
162 END IF;
163 CLOSE c_res_details;
164
165 x_return_status := l_return_status;
166 END Get_New_Res_Details;
167 -------------------------------------------------------------------------------
168 --
169 -- Checks if there are more approvers
170 --
171 -------------------------------------------------------------------------------
172 PROCEDURE Check_Approval_Required(
173 p_approval_detail_id IN NUMBER,
174 p_current_seq IN NUMBER,
175 x_next_seq OUT NOCOPY NUMBER,
176 x_required_flag OUT NOCOPY VARCHAR2)
177 IS
178
179 CURSOR c_check_app IS
180 SELECT approver_seq
181 FROM ams_approvers
182 WHERE ams_approval_detail_id = p_approval_detail_id
183 AND approver_seq > p_current_seq
184 and TRUNC(sysdate) between TRUNC(nvl(start_date_active,sysdate -1 ))
185 and TRUNC(nvl(end_date_active,sysdate + 1))
186 and active_flag = 'Y'
187 order by approver_seq ;
188
189 BEGIN
190 OPEN c_check_app;
191 FETCH c_check_app
192 INTO x_next_seq;
193 IF c_check_app%NOTFOUND THEN
194 x_required_flag := FND_API.G_FALSE;
195 ELSE
196 x_required_flag := FND_API.G_TRUE;
197 END IF;
198 CLOSE c_check_app;
199 END Check_Approval_Required;
200
201
202 -------------------------------------------------------------------------------
203 --
204 -- Gets approver info
205 -- Approvers Can be user or Role
206 -- If it is role it should of role_type MKTAPPR AMSAPPR
207 -- The Seeded role is AMS_DEFAULT_APPROVER
208 --
209 -------------------------------------------------------------------------------
210 PROCEDURE Get_approver_Info(
211 p_approval_detail_id IN NUMBER,
212 p_current_seq IN NUMBER,
213 x_approver_id OUT NOCOPY VARCHAR2,
214 x_approver_type OUT NOCOPY VARCHAR2,
215 x_role_name OUT NOCOPY VARCHAR2,
216 x_object_approver_id OUT NOCOPY VARCHAR2,
217 x_notification_type OUT NOCOPY VARCHAR2,
218 x_notification_timeout OUT NOCOPY VARCHAR2,
219 x_return_status OUT NOCOPY VARCHAR2)
220 IS
221 l_approver_id number;
222 CURSOR c_approver_info IS
223 SELECT approver_id,
224 approver_type,
225 object_approver_id,
226 notification_type,
227 notification_timeout
228 FROM ams_approvers
229 WHERE ams_approval_detail_id = p_approval_detail_id
230 AND approver_seq = p_current_seq
231 and TRUNC(sysdate) between TRUNC(nvl(start_date_active,sysdate -1 ))
232 and TRUNC(nvl(end_date_active,sysdate + 1))
233 and active_flag ='Y';
234
235 CURSOR c_role_info IS
236 SELECT rr.role_resource_id, rl.role_name
237 FROM jtf_rs_role_relations rr, jtf_rs_roles_vl rl
238 WHERE rr.role_id = rl.role_id
239 AND rr.role_resource_type = 'RS_INDIVIDUAL'
240 AND rr.delete_flag = 'N'
241 AND SYSDATE BETWEEN rr.start_date_active and nvl(rr.end_date_active, SYSDATE)
242 AND rl.role_type_code in ( 'MKTGAPPR', 'AMSAPPR')
243 AND rl.role_id = l_approver_id;
244 -- SQL Repository Fix
245 /*
246 SELECT ROLE_RESOURCE_ID,ROLE_NAME
247 FROM JTF_RS_DEFRESROLES_VL
248 WHERE role_type_code in( 'MKTGAPPR','AMSAPPR')
249 AND ROLE_ID = l_approver_id
250 AND ROLE_RESOURCE_TYPE = 'RS_INDIVIDUAL'
251 AND delete_flag = 'N'
252 AND TRUNC(sysdate) between TRUNC(RES_RL_start_DATE)
253 and TRUNC(nvl(RES_RL_END_DATE,sysdate));
254 */
255 CURSOR c_role_info_count IS
256 SELECT count(1)
257 FROM jtf_rs_role_relations rr, jtf_rs_roles_b rl
258 WHERE rr.role_id = rl.role_id
259 AND rr.role_resource_type = 'RS_INDIVIDUAL'
260 AND rr.delete_flag = 'N'
261 AND SYSDATE BETWEEN rr.start_date_active and nvl(rr.end_date_active, SYSDATE)
262 AND rl.role_type_code in ( 'MKTGAPPR', 'AMSAPPR')
263 AND rl.role_id = l_approver_id;
264 -- SQL Repository Fix
265 /*
266 FROM JTF_RS_DEFRESROLES_VL
267 WHERE role_type_code in ('MKTGAPPR','AMSAPPR')
268 AND ROLE_ID = l_approver_id
269 AND ROLE_RESOURCE_TYPE = 'RS_INDIVIDUAL'
270 AND delete_flag = 'N'
271 AND TRUNC(sysdate) between TRUNC(RES_RL_start_DATE) and TRUNC(nvl(RES_RL_END_DATE,sysdate));
272 */
273 CURSOR c_default_role_info IS
274 SELECT rr.role_id
275 FROM jtf_rs_role_relations rr,
276 jtf_rs_roles_b rl
277 WHERE rr.role_id = rl.role_id
278 and rl.role_type_code in( 'MKTGAPPR','AMSAPPR')
279 AND rl.role_code = 'AMS_DEFAULT_APPROVER'
280 AND rr.ROLE_RESOURCE_TYPE = 'RS_INDIVIDUAL'
281 AND delete_flag = 'N'
282 AND TRUNC(sysdate) between TRUNC(rr.start_date_active)
283 and TRUNC(nvl(rr.end_date_active,sysdate));
284
285 CURSOR c_rule_name IS
286 SELECT name
287 FROM ams_approval_details_vl
288 WHERE approval_detail_id = p_approval_detail_id;
289
290 l_count number;
291 l_pkg_name VARCHAR2(80);
292 l_proc_name VARCHAR2(80);
293 dml_str VARCHAR2(2000);
294 --resultout VARCHAR2(2000);
295 x_msg_count NUMBER;
296 x_msg_data VARCHAR2(2000);
297 l_rule_name VARCHAR2(240);
298
299 CURSOR c_API_Name(id_in IN VARCHAR2) is
300 SELECT package_name, procedure_name
301 FROM ams_object_rules_b
302 WHERE OBJECT_RULE_ID = id_in;
303
304 BEGIN
305 x_return_status := FND_API.G_RET_STS_SUCCESS;
306 OPEN c_approver_info;
307 FETCH c_approver_info
308 INTO x_approver_id,
309 x_approver_type,
310 x_object_approver_id,
311 x_notification_type,
312 x_notification_timeout;
313 IF c_approver_info%NOTFOUND THEN
314 CLOSE c_approver_info;
315
316 OPEN c_rule_name;
317 FETCH c_rule_name INTO l_rule_name;
318 CLOSE c_rule_name;
319 Fnd_Message.Set_Name('AMS','AMS_NO_APPR_FOR_RULE');
320 Fnd_Message.Set_Token('RULE_NAME',l_rule_name);
321 FND_MSG_PUB.Add;
322 x_return_status := FND_API.G_RET_STS_ERROR;
323 return;
324 END IF;
325
326 /*
327 IF x_approver_type = 'FUNCTION' THEN
328 OPEN c_API_Name(x_approver_id);
329 FETCH c_API_Name INTO l_pkg_name, l_proc_name;
330 IF c_API_Name%NOTFOUND THEN
331 CLOSE c_API_Name;
332 --dbms_output.put_line('In Role Check 2');
333 FND_MESSAGE.Set_Name('AMS','AMS_APPR_INVALID_API_NAME');
334 FND_MSG_PUB.Add;
335 x_return_status := FND_API.G_RET_STS_ERROR;
336 return;
337 END IF;
338 CLOSE c_API_Name;
339 dml_str := 'BEGIN ' || l_pkg_name||'.'||l_proc_name||'(:x_approver_id,:x_approver_type,:x_msg_count, x_msg_data,:x_return_status); END;';
340 EXECUTE IMMEDIATE dml_str USING OUT x_approver_id,OUT x_approver_type, OUT x_msg_count, OUT x_msg_data,OUT x_return_status;
341 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
342 FND_MESSAGE.Set_Name('AMS','AMS_APPR_FUNCTION_API_FAIL');
343 FND_MSG_PUB.Add;
344 x_return_status := FND_API.G_RET_STS_ERROR;
345 return;
346 END IF;
347 END IF;
348 */
349 IF x_approver_type = 'ROLE' THEN
350 if x_object_approver_id is null then
351 OPEN c_default_role_info ;
352 FETCH c_default_role_info
353 INTO x_object_approver_id;
354 --dbms_output.put_line('In Role Check 1');
355 IF c_default_role_info%NOTFOUND THEN
356 CLOSE c_default_role_info ;
357 Fnd_Message.Set_Name('AMS','AMS_NO_DEFAULT_ROLE'); -- VMODUR added
358 --FND_MESSAGE.Set_Name('AMS','AMS_APPR_INVALID_ROLE');
359 FND_MSG_PUB.Add;
360 x_return_status := FND_API.G_RET_STS_ERROR;
361 return;
362 END IF;
363 CLOSE c_default_role_info ;
364 end if;
365 l_approver_id := x_object_approver_id;
366 OPEN c_role_info_count;
367 FETCH c_role_info_count
368 INTO l_count;
369 IF l_count > 1 THEN
370 CLOSE c_role_info_count;
371 --dbms_output.put_line('In Role Check 2');
372 Fnd_Message.Set_Name('AMS','AMS_MANY_DEFAULT_ROLE'); -- VMODUR added
373 --FND_MESSAGE.Set_Name('AMS','AMS_APPR_INVALID_ROLE');
374 FND_MSG_PUB.Add;
375 x_return_status := FND_API.G_RET_STS_ERROR;
376 return;
377 END IF;
378 CLOSE c_role_info_count;
379 OPEN c_role_info;
380 FETCH c_role_info
381 INTO x_object_approver_id,x_role_name;
382 IF c_role_info%NOTFOUND THEN
383 CLOSE c_role_info;
384 --dbms_output.put_line('In Role Check 3');
385 FND_MESSAGE.Set_Name('AMS','AMS_APPR_INVALID_ROLE');
386 FND_MSG_PUB.Add;
387 x_return_status := FND_API.G_RET_STS_ERROR;
388 return;
389 END IF;
390 CLOSE c_role_info;
391 END IF; --x_approval_type = ROLE;
392 CLOSE c_approver_info;
393 END Get_approver_Info;
394 -------------------------------------------------------------------------------------
395 FUNCTION Is_Min_Sequence
396 (p_approval_detail_id IN NUMBER,
397 p_sequence IN NUMBER)
398 RETURN BOOLEAN IS
399 CURSOR c_min_seq IS
400 SELECT min(approver_seq)
401 FROM ams_approvers
402 WHERE ams_approval_detail_id = p_approval_detail_id
403 AND active_flag = 'Y'
404 AND TRUNC(sysdate) between TRUNC(nvl(start_date_active,sysdate -1 ))
405 AND TRUNC(nvl(end_date_active,sysdate + 1));
406
407 l_min_seq NUMBER;
408 BEGIN
409 OPEN c_min_seq;
410 FETCH c_min_seq INTO l_min_seq;
411 IF c_min_seq%NOTFOUND THEN
412 CLOSE c_min_seq;
413 return false;
414 END IF;
415 CLOSE c_min_seq;
416 IF l_min_seq = p_sequence THEN
417 RETURN true;
418 ELSE
419 RETURN false;
420 END IF;
421 END Is_Min_Sequence;
422
423
424 /************************END of Private Procedures************************************/
425
426 -- Start of Comments
427 --
428 -- NAME
429 -- HANDLE_ERR
430 --
431 -- PURPOSE
432 -- This Procedure will Get all the Errors from the Message stack and
433 -- set it to the workflow error message attribute
434 -- It also sets the subject for the generic error message wf attribute
435 -- The generic error message body wf attribute is set in the
436 -- ntf_requestor_of_error procedure
437 --
438 -- NOTES
439 --
440 -- End of Comments
441
442
443 PROCEDURE Handle_Err
444 (p_itemtype IN VARCHAR2 ,
445 p_itemkey IN VARCHAR2 ,
446 p_msg_count IN NUMBER , -- Number of error Messages
447 p_msg_data IN VARCHAR2 ,
448 p_attr_name IN VARCHAR2,
449 x_error_msg OUT NOCOPY VARCHAR2
450 )
451 IS
452 l_msg_count NUMBER ;
453 l_msg_data VARCHAR2(2000);
454 l_final_data VARCHAR2(4000);
455 l_msg_index NUMBER ;
456 l_cnt NUMBER := 0 ;
457 l_appr_meaning VARCHAR2(240);
458 l_appr_obj_name VARCHAR2(240);
459 l_gen_err_sub VARCHAR2(240);
460 BEGIN
461 -- Retriveing Error Message from FND_MSG_PUB
462 -- Called by most of the procedures if it encounter error
463 WHILE l_cnt < p_msg_count
464 LOOP
465 FND_MSG_PUB.Get
466 (p_msg_index => l_cnt + 1,
467 p_encoded => FND_API.G_FALSE,
468 p_data => l_msg_data,
469 p_msg_index_out => l_msg_index ) ;
470 l_final_data := l_final_data ||l_msg_index||': '
471 ||l_msg_data||fnd_global.local_chr(10) ;
472 l_cnt := l_cnt + 1 ;
473 END LOOP ;
474 x_error_msg := l_final_data;
475 WF_ENGINE.SetItemAttrText
476 (itemtype => p_itemtype,
477 itemkey => p_itemkey ,
478 aname => p_attr_name,
479 avalue => l_final_data );
480 --
481 l_appr_meaning := wf_engine.GetItemAttrText(
482 itemtype => p_itemtype,
483 itemkey => p_itemkey,
484 aname => 'AMS_APPROVAL_OBJECT_MEANING');
485
486 l_appr_obj_name := wf_engine.GetItemAttrText(
487 itemtype => p_itemtype,
488 itemkey => p_itemkey,
489 aname => 'AMS_APPROVAL_OBJECT_NAME');
490 --
491 fnd_message.set_name ('AMS', 'AMS_GEN_NTF_ERROR_SUB');
492 fnd_message.set_token ('OBJ_MEANING', l_appr_meaning, FALSE);
493 fnd_message.set_token ('OBJ_NAME', l_appr_obj_name, FALSE);
494
495 l_gen_err_sub := SUBSTR(fnd_message.get,1,240);
496
497 Wf_Engine.SetItemAttrText
498 (itemtype => p_itemtype,
499 itemkey => p_itemkey ,
500 aname => 'ERR_SUBJECT',
501 avalue => l_gen_err_sub );
502 END Handle_Err;
503 /*==============================================================================================*/
504
505 -- Start of Comments
506 -- NAME
507 -- Get_Approval_Details
508 -- PURPOSE
509 -- This Procedure get all the approval details
510 --
511 -- Used By Objects
512 -- p_activity_type Activity Type or Objects
513 -- (CAMP,DELV,EVEO,EVEH .. )
514 -- p_activity_id Primary key of the Object
515 -- p_approval_type BUDGET,CONCEPT
516 -- p_act_budget_id If called from header record this field is null not used
517 -- p_object_details Object details contains the detail of objects
518 -- x_approval_detail_id Approval detail Id macthing the criteria
519 -- x_approver_seq Approval Sequence
520 -- x_return_status Return Status
521 -- NOTES
522 -- HISTORY
523 -- 15-SEP-2000 GJOBY CREATED
524 -- End of Comments
525 /*****************************************************************/
526
527
528 PROCEDURE Get_Approval_Details
529 ( p_activity_id IN NUMBER,
530 p_activity_type IN VARCHAR2,
531 p_approval_type IN VARCHAR2 DEFAULT 'BUDGET',
532 -- p_act_budget_id IN NUMBER, -- was default g_miss_num
533 p_object_details IN ObjRecTyp,
534 x_approval_detail_id OUT NOCOPY NUMBER,
535 x_approver_seq OUT NOCOPY NUMBER,
536 x_return_status OUT NOCOPY VARCHAR2)
537 IS
538
539 l_amount NUMBER := 0; -- FND_API.G_MISS_NUM
540 l_business_unit_id NUMBER := -9999; -- FND_API.G_MISS_NUM
541 l_country_code VARCHAR2(30) := '-9999'; -- FND_API.G_MISS_CHAR
542 l_org_id NUMBER := -9999; -- FND_API.G_MISS_NUM
543 l_setup_type_id NUMBER := -9999; -- FND_API.G_MISS_NUM
544 l_object_type VARCHAR2(30) := '-9999'; -- FND_API.G_MISS_CHAR
545 l_priority VARCHAR2(30) := '-9999'; -- FND_API.G_MISS_CHAR
546 l_approver_id NUMBER;
547 l_object_details ObjRecTyp;
548 l_activity_type VARCHAR2(30);
549 l_activity_id NUMBER;
550 l_freq_org_id NUMBER; -- Added for fixing the Bug#6627988
551
552 --l_approver_seq NUMBER;
553 --l_approval_detail_id NUMBER;
554
555 -- Get Approval Detail Id matching the Criteria
556 -- Approval Object (CAMP, DELV.. ) is mandatory
557 -- Approval type (BUDGET .. ) is mandatory
558 -- APPROVAL_LIMIT_FROM is mandatory
559 -- Using Weightage Percentage
560 -- business_unit_id 6
561 -- organization_id 5
562 -- approval_object_type 4
563 -- approval_priority 3
564 -- country_code 2
565 -- custom_setup_id 1
566
567 CURSOR c_approver_detail_id IS
568 SELECT approval_detail_id, seeded_flag
569 FROM ams_approval_details
570 WHERE nvl(business_unit_id,l_business_unit_id) = l_business_unit_id
571 AND nvl(organization_id,l_org_id) = l_org_id
572 AND nvl(custom_setup_id,l_setup_type_id) = l_setup_type_id
573 AND approval_object = p_activity_type
574 AND approval_type = p_approval_type
575 AND nvl(approval_object_type,l_object_type) = l_object_type
576 AND NVL(user_country_code,l_country_code) = l_country_code
577 AND nvl(approval_priority,l_priority) = l_priority
578 AND seeded_flag = 'N'
579 AND active_flag = 'Y'
580 AND l_amount between nvl(approval_limit_from,0) and
581 nvl(approval_limit_to,l_amount)
582 and TRUNC(sysdate) between TRUNC(nvl(start_date_active,sysdate -1 ))
583 and TRUNC(nvl(end_date_active,sysdate + 1))
584 ORDER BY (POWER(2,DECODE(business_unit_id,'',0,6)) +
585 POWER(2,DECODE(organization_id,'',0,5)) +
586 POWER(2,DECODE(custom_setup_id,'',0,1)) +
587 POWER(2,DECODE(user_country_code,'',0,2)) +
588 POWER(2,DECODE(approval_object_type,'',0,3)) +
589 POWER(2,DECODE(approval_priority,'',0,4) )) DESC ;
590 /*
591 order by (power(2,decode(business_unit_id,'',0,5)) +
592 power(2,decode(organization_id,'',0,4)) +
593 power(2,decode(custom_setup_id,'',0,1)) +
594 power(2,decode(approval_object_type,'',0,2)) +
595 power(2,decode(approval_priority,'',0,3) )) desc ;
596 */
597 -- If the there are no matching records it takes the default Rule
598 CURSOR c_approver_def IS
599 SELECT approval_detail_id, seeded_flag
600 FROM ams_approval_details
601 WHERE approval_detail_id = 150;
602 -- WHERE seeded_flag = 'Y'; -- to avoid FTS
603
604 -- Takes Min Approver Sequence From Ams_approvers Once matching records are
605 -- Found from ams_approval_deatils
606 CURSOR c_approver_seq IS
607 SELECT min(approver_seq)
608 FROM ams_approvers
609 WHERE ams_approval_detail_id = x_approval_detail_id
610 AND active_flag = 'Y'
611 AND TRUNC(sysdate) between TRUNC(nvl(start_date_active,sysdate -1 ))
612 AND TRUNC(nvl(end_date_active,sysdate + 1));
613 /*
614 -- For Budgets the priority has to to be taken from the Object priority
615 -- The Following cursor returns the Approval object and Approval Object Id
616 -- for the parent
617
618 CURSOR c_fund_priority IS
619 SELECT ARC_ACT_BUDGET_USED_BY,
620 ACT_BUDGET_USED_BY_ID
621 FROM ams_act_budgets
622 WHERE ACTIVITY_BUDGET_ID = p_act_budget_id;
623 */
624 l_seeded_flag varchar2(1);
625 BEGIN
626 x_return_status := FND_API.G_RET_STS_SUCCESS;
627
628 -- Start: Added for fixing the bug#6340621
629 IF (p_activity_type='FREQ') THEN
630
631 BEGIN
632
633 SELECT distinct organization_id into l_freq_org_id
634 FROM ams_approval_details
635 WHERE approval_object = p_activity_type
636 AND approval_type = p_approval_type
637 AND approval_object_type = p_object_details.object_type;
638
639 EXCEPTION
640 WHEN NO_DATA_FOUND THEN
641
642 l_freq_org_id :=-9999;
643
644 WHEN TOO_MANY_ROWS THEN
645
646 l_freq_org_id :=-9999;
647
648 END;
649
650 END IF;
651 -- End: Added for fixing the bug#6627988
652
653
654 l_business_unit_id :=
655 nvl(p_object_details.business_unit_id,l_business_unit_id) ;
656
657 -- Start: Added for fixing the bug#6627988
658 IF (l_freq_org_id IS NOT NULL) THEN
659 l_org_id := nvl(p_object_details.org_id,l_org_id);
660 END IF;
661 -- End: Added for fixing the bug#6627988
662
663 l_setup_type_id := nvl(p_object_details.setup_type_id,l_setup_type_id);
664 l_object_type := nvl(p_object_details.object_type,l_object_type);
665 l_priority := nvl(p_object_details.priority,l_priority);
666 l_country_code := nvl(p_object_details.country_code,l_country_code);
667 l_amount := nvl(p_object_details.total_header_amount,l_amount);
668
669
670 OPEN c_approver_detail_id ;
671 FETCH c_approver_detail_id INTO x_approval_detail_id, l_seeded_flag;
672 IF c_approver_detail_id%NOTFOUND THEN
673 CLOSE c_approver_detail_id;
674 OPEN c_approver_def ;
675 FETCH c_approver_def INTO x_approval_detail_id, l_seeded_flag;
676 IF c_approver_def%NOTFOUND THEN
677 CLOSE c_approver_def ;
678 FND_MESSAGE.Set_Name('AMS','AMS_NO_APPROVAL_DETAIL_ID');
679 FND_MSG_PUB.Add;
680 x_return_status := FND_API.G_RET_STS_ERROR;
681 return;
682 END IF;
683 CLOSE c_approver_def ;
684 ELSE
685 CLOSE c_approver_detail_id;
686 END IF;
687
688 OPEN c_approver_seq ;
689 FETCH c_approver_seq INTO x_approver_seq ;
690 IF c_approver_seq%NOTFOUND THEN
691 CLOSE c_approver_seq;
692 IF l_seeded_flag = 'Y' THEN
693 FND_MESSAGE.Set_Name('AMS','AMS_NO_APPROVER_SEQUENCE');
694 FND_MSG_PUB.Add;
695 x_return_status := FND_API.G_RET_STS_ERROR;
696 return;
697 else
698 OPEN c_approver_def ;
699 FETCH c_approver_def INTO x_approval_detail_id, l_seeded_flag;
700 IF c_approver_def%NOTFOUND THEN
701 CLOSE c_approver_def ;
702 FND_MESSAGE.Set_Name('AMS','AMS_NO_APPROVAL_DETAIL_ID');
703 FND_MSG_PUB.Add;
704 x_return_status := FND_API.G_RET_STS_ERROR;
705 return;
706 END IF;
707 CLOSE c_approver_def ;
708 OPEN c_approver_seq ;
709 FETCH c_approver_seq INTO x_approver_seq ;
710 IF c_approver_seq%NOTFOUND THEN
711 CLOSE c_approver_seq;
712 FND_MESSAGE.Set_Name('AMS','AMS_NO_APPROVER_SEQUENCE');
713 FND_MSG_PUB.Add;
714 x_return_status := FND_API.G_RET_STS_ERROR;
715 return;
716 END IF;
717 END IF;
718 END IF;
719 CLOSE c_approver_seq;
720
721 END Get_Approval_Details;
722
723 /*****************************************************************
724 -- Start of Comments
725 -- NAME
726 -- StartProcess
727 -- PURPOSE
728 -- This Procedure will Start the flow
729 --
730 -- Used By Objects
731 -- p_activity_type Activity Type or Objects
732 -- (CAMP,DELV,EVEO,EVEH .. )
733 -- p_activity_id Primary key of the Object
734 -- p_approval_type BUDGET,CONCEPT
735 -- p_object_version_number Object Version Number
736 -- p_orig_stat_id The status to which is
737 -- to be reverted if process fails
738 -- p_new_stat_id The status to which it is
739 -- to be updated if the process succeeds
740 -- p_reject_stat_id The status to which is
741 -- to be updated if the process fails
742 -- p_requester_userid The requester who has submitted the
743 -- process
744 -- p_notes_from_requester Notes from the requestor
745 -- p_workflowprocess Name of the workflow process
746 -- AMS_CONCEPT_APPROVAL -- For Concept
747 -- AMS_APPROVAL -- For Budget Approvals
748 -- p_item_type AMSGAPP
749 -- NOTES
750 -- Item key generated as combination of Activity Type, Activity Id, and Object
751 -- Version Number.
752 -- For ex. CAMP100007 where 7 is object version number and 10000 Activity id
753 -- HISTORY
754 -- 15-SEP-2000 GJOBY CREATED
755 -- End of Comments
756 *****************************************************************/
757
758 PROCEDURE StartProcess
759 (p_activity_type IN VARCHAR2,
760 p_activity_id IN NUMBER,
761 p_approval_type IN VARCHAR2, -- DEFAULT NULL -- pass null
762 p_object_version_number IN NUMBER,
763 p_orig_stat_id IN NUMBER,
764 p_new_stat_id IN NUMBER,
765 p_reject_stat_id IN NUMBER,
766 p_requester_userid IN NUMBER,
767 p_notes_from_requester IN VARCHAR2 DEFAULT NULL,
768 p_workflowprocess IN VARCHAR2 DEFAULT NULL,
769 p_item_type IN VARCHAR2 DEFAULT NULL,
770 p_gen_process_flag IN VARCHAR2 DEFAULT NULL
771 )
772 IS
773 itemtype VARCHAR2(30) := nvl(p_item_type,'AMSGAPP');
774 itemkey VARCHAR2(30) := p_approval_type||p_activity_type||p_activity_id||
775 p_object_version_number;
776 itemuserkey VARCHAR2(80) := p_activity_type||p_activity_id||
777 p_object_version_number;
778
779 l_requester_role VARCHAR2(320) ; -- Changed from VARCHAR2(100)
780 l_display_name VARCHAR2(360) ; -- Changed from VARCHAR2(240);
781 l_requester_id NUMBER ;
782 l_person_id NUMBER ;
783 l_appr_for VARCHAR2(240) ;
784 l_appr_meaning VARCHAR2(240);
785 l_return_status VARCHAR2(1);
786 l_msg_count NUMBER;
787 l_msg_data VARCHAR2(4000);
788 l_error_msg VARCHAR2(4000);
789 x_resource_id NUMBER;
790 l_index NUMBER;
791 l_save_threshold NUMBER := wf_engine.threshold;
792 -- [BEGIN OF BUG2631497 FIXING by mchang 23-OCT-2002]
793 l_user_id NUMBER;
794 l_resp_id NUMBER;
795 l_appl_id NUMBER;
796 l_security_group_id NUMBER;
797 -- [END OF BUG2631497 FIXING]
798
799 l_appr_hist_rec AMS_Appr_Hist_Pvt.Appr_Hist_Rec_Type;
800
801 CURSOR c_resource IS
802 SELECT resource_id ,employee_id source_id,full_name resource_name
803 FROM ams_jtf_rs_emp_v
804 WHERE user_id = x_resource_id ;
805 BEGIN
806 FND_MSG_PUB.initialize();
807
808 -- 11.5.9
809 -- Delete any previous approval history
810
811 AMS_Appr_Hist_PVT.Delete_Appr_Hist(
812 p_api_version_number => 1.0,
813 p_init_msg_list => FND_API.G_FALSE,
814 p_commit => FND_API.G_FALSE,
815 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
816 x_return_status => l_return_status,
817 x_msg_count => l_msg_count,
818 x_msg_data => l_msg_data,
819 p_object_id => p_activity_id,
820 p_object_type_code => p_activity_type,
821 p_sequence_num => null,
822 p_action_code => null,
823 p_object_version_num => null,
824 p_approval_type => p_approval_type);
825
826 IF l_return_status <> Fnd_Api.G_RET_STS_SUCCESS THEN
827 RAISE Fnd_Api.G_EXC_ERROR;
828 END IF;
829
830
831 AMS_Utility_PVT.debug_message('Start :Item Type : '||itemtype
832 ||' Item key : '||itemkey);
833
834 -- wf_engine.threshold := -1;
835 WF_ENGINE.CreateProcess (itemtype => itemtype,
836 itemkey => itemkey ,
837 process => p_workflowprocess);
838
839 WF_ENGINE.SetItemUserkey(itemtype => itemtype,
840 itemkey => itemkey ,
841 userkey => itemuserkey);
842
843
844 /*****************************************************************
845 Initialize Workflow Item Attributes
846 *****************************************************************/
847 -- [BEGIN OF BUG2631497 FIXING by mchang 23-OCT-2002]
848 -- mchang: add PL/SQL security context into workfow item attributes. It could be used
849 -- later on for PL/SQL function to initialize the global context when the session
850 -- is established by workflow mailer.
851
852 l_user_id := FND_GLOBAL.user_id;
853 l_resp_id := FND_GLOBAL.resp_id;
854 l_appl_id := FND_GLOBAL.resp_appl_id;
855 l_security_group_id := FND_GLOBAL.security_group_id;
856
857 WF_ENGINE.SetItemAttrNumber(itemtype => itemtype ,
858 itemkey => itemkey,
859 aname => 'USER_ID',
860 avalue => l_user_id
861 );
862
863 WF_ENGINE.SetItemAttrNumber(itemtype => itemtype ,
864 itemkey => itemkey,
865 aname => 'RESPONSIBILITY_ID',
866 avalue => l_resp_id
867 );
868
869 WF_ENGINE.SetItemAttrNumber(itemtype => itemtype ,
870 itemkey => itemkey,
871 aname => 'APPLICATION_ID',
872 avalue => l_appl_id
873 );
874
875 WF_ENGINE.SetItemAttrNumber(itemtype => itemtype ,
876 itemkey => itemkey,
877 aname => 'SECURITY_GROUP_ID',
878 avalue => l_security_group_id -- was l_appl_id
879 );
880 -- [END OF BUG2631497 FIXING]
881
882
883 -- Activity Type (Some of valid values are 'CAMP','DELV','EVEH','EVEO'..);
884 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
885 itemkey => itemkey,
886 aname => 'AMS_ACTIVITY_TYPE',
887 avalue => p_activity_type );
888
889 -- Activity ID (primary Id of Activity Object)
890 WF_ENGINE.SetItemAttrNumber(itemtype => itemtype ,
891 itemkey => itemkey,
892 aname => 'AMS_ACTIVITY_ID',
893 avalue => p_activity_id );
894
895
896 -- Original Status Id (If error occurs we have to revert back to this
897 -- status )
898 WF_ENGINE.SetItemAttrNumber(itemtype => itemtype,
899 itemkey => itemkey,
900 aname => 'AMS_ORIG_STAT_ID',
901 avalue => p_orig_stat_id );
902
903 -- New Status Id (If activity is approved status of activity is updated
904 -- by this status )
905 WF_ENGINE.SetItemAttrNumber(itemtype => itemtype,
906 itemkey => itemkey,
907 aname => 'AMS_NEW_STAT_ID',
908 avalue => p_new_stat_id );
909
910 -- Reject Status Id (If activity is approved status of activity is rejected
911 -- by this status )
912 WF_ENGINE.SetItemAttrNumber(itemtype => itemtype,
913 itemkey => itemkey,
914 aname => 'AMS_REJECT_STAT_ID',
915 avalue => p_reject_stat_id );
916
917
918 -- Object Version Number
919 WF_ENGINE.SetItemAttrNumber(itemtype => itemtype,
920 itemkey => itemkey,
921 aname => 'AMS_OBJECT_VERSION_NUMBER',
922 avalue => p_object_version_number );
923
924 -- Notes from the requester
925 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
926 itemkey => itemkey,
927 aname => 'AMS_NOTES_FROM_REQUESTOR',
928 avalue => nvl(p_notes_from_requester,'') );
929
930 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
931 itemkey => itemkey,
932 aname => 'DOCUMENT_ID',
933 avalue => itemtype || ':' ||itemkey);
934
935 WF_ENGINE.SetItemAttrNumber(itemtype => itemtype,
936 itemkey => itemkey,
937 aname => 'AMS_REQUESTER_ID',
938 avalue => p_requester_userid );
939
940 l_return_status := FND_API.G_RET_STS_SUCCESS;
941
942
943 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
944 itemkey => itemkey,
945 aname => 'AMS_APPROVAL_TYPE',
946 avalue => p_approval_type );
947
948 -- Setting up the role
949 Get_User_Role(p_user_id => p_requester_userid ,
950 x_role_name => l_requester_role,
951 x_role_display_name => l_display_name,
952 x_return_status => l_return_status);
953
954 IF l_return_status <> FND_API.G_RET_STS_SUCCESS then
955 RAISE FND_API.G_EXC_ERROR;
956 END IF;
957
958
959 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
960 itemkey => itemkey,
961 aname => 'AMS_REQUESTER',
962 avalue => l_requester_role );
963
964 /* genric flag requested by mumu.*/
965 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
966 itemkey => itemkey,
967 aname => 'AMS_GENERIC_FLAG',
968 avalue => p_gen_process_flag );
969
970 WF_ENGINE.SetItemOwner(itemtype => itemtype,
971 itemkey => itemkey,
972 owner => l_requester_role);
973
974
975 -- Start the Process
976 WF_ENGINE.StartProcess (itemtype => itemtype,
977 itemkey => itemkey);
978
979
980 -- Create the Submitted history record
981
982 l_appr_hist_rec.object_id := p_activity_id;
983 l_appr_hist_rec.object_type_code := p_activity_type;
984 l_appr_hist_rec.sequence_num := 0;
985 l_appr_hist_rec.object_version_num := p_object_version_number;
986 l_appr_hist_rec.action_code := 'SUBMITTED';
987 l_appr_hist_rec.action_date := sysdate;
988 l_appr_hist_rec.approver_id := p_requester_userid;
989 l_appr_hist_rec.note := p_notes_from_requester;
990 l_appr_hist_rec.approval_type := p_approval_type;
991 l_appr_hist_rec.approver_type := 'USER'; -- User always submits
992 --
993 AMS_Appr_Hist_PVT.Create_Appr_Hist(
994 p_api_version_number => 1.0,
995 p_init_msg_list => FND_API.G_FALSE,
996 p_commit => FND_API.G_FALSE,
997 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
998 x_return_status => l_return_status,
999 x_msg_count => l_msg_count,
1000 x_msg_data => l_msg_data,
1001 p_appr_hist_rec => l_appr_hist_rec
1002 );
1003
1004 IF l_return_status <> Fnd_Api.G_RET_STS_SUCCESS THEN
1005 RAISE Fnd_Api.G_EXC_ERROR;
1006 END IF;
1007 -- wf_engine.threshold := l_save_threshold ;
1008 EXCEPTION
1009
1010 WHEN FND_API.G_EXC_ERROR THEN
1011 -- wf_engine.threshold := l_save_threshold ;
1012 FND_MSG_PUB.Count_And_Get (
1013 p_encoded => FND_API.G_FALSE,
1014 p_count => l_msg_count,
1015 p_data => l_msg_data);
1016
1017 Handle_Err
1018 (p_itemtype => itemtype ,
1019 p_itemkey => itemkey ,
1020 p_msg_count => l_msg_count, -- Number of error Messages
1021 p_msg_data => l_msg_data ,
1022 p_attr_name => 'AMS_ERROR_MSG',
1023 x_error_msg => l_error_msg );
1024
1025 RAISE;
1026 /*
1027 if(l_msg_count > 0)then
1028 for I in 1 .. l_msg_count LOOP
1029 fnd_msg_pub.Get
1030 (p_msg_index => FND_MSG_PUB.G_NEXT,
1031 p_encoded => FND_API.G_FALSE,
1032 p_data => l_msg_data,
1033 p_msg_index_out => l_index);
1034 dbms_output.put_line('message :'||l_msg_data);
1035 end loop;
1036 end if;
1037
1038 wf_core.context ('ams_gen_approval_pvt', 'StartProcess',p_activity_type
1039 ,p_activity_id ,l_error_msg);
1040 RAISE;
1041 WHEN OTHERS THEN
1042 -- wf_engine.threshold := l_save_threshold ;
1043 FND_MSG_PUB.Count_And_Get (
1044 p_encoded => FND_API.G_FALSE,
1045 p_count => l_msg_count,
1046 p_data => l_msg_data);
1047
1048
1049 if(l_msg_count > 0)then
1050 for I in 1 .. l_msg_count LOOP
1051 fnd_msg_pub.Get
1052 (p_msg_index => FND_MSG_PUB.G_NEXT,
1053 p_encoded => FND_API.G_FALSE,
1054 p_data => l_msg_data,
1055 p_msg_index_out => l_index);
1056 dbms_output.put_line('message :'||l_msg_data);
1057 end loop;
1058 end if;
1059 */
1060 /*
1061 Handle_Err
1062 (p_itemtype => itemtype ,
1063 p_itemkey => itemkey ,
1064 p_msg_count => l_msg_count, -- Number of error Messages
1065 p_msg_data => l_msg_data ,
1066 p_attr_name => 'AMS_ERROR_MSG',
1067 x_error_msg => l_error_msg
1068 ) ;
1069 */
1070 WHEN OTHERS THEN
1071 wf_core.context ('ams_gen_approval_pvt', 'StartProcess',p_activity_type
1072 ,p_activity_id ,l_error_msg);
1073 RAISE;
1074
1075 END StartProcess;
1076
1077 /*****************************************************************
1078 -- Start of Comments
1079 --
1080 -- NAME
1081 -- set_activity_details
1082 --
1083 -- PURPOSE
1084 -- NOTES
1085 -- HISTORY
1086 -- End of Comments
1087 *****************************************************************/
1088
1089 PROCEDURE Set_Activity_Details(itemtype IN VARCHAR2,
1090 itemkey IN VARCHAR2,
1091 actid IN NUMBER,
1092 funcmode IN VARCHAR2,
1093 resultout OUT NOCOPY VARCHAR2) IS
1094
1095
1096 l_activity_id NUMBER;
1097 l_activity_type VARCHAR2(30);
1098 l_approval_type VARCHAR2(30);
1099 l_msg_count NUMBER;
1100 l_msg_data VARCHAR2(4000);
1101 l_error_msg VARCHAR2(4000);
1102 l_pkg_name varchar2(80);
1103 l_proc_name varchar2(80);
1104 l_return_stat varchar2(1);
1105 dml_str VARCHAR2(2000);
1106
1107 BEGIN
1108 FND_MSG_PUB.initialize();
1109 IF (funcmode = 'RUN') THEN
1110 -- get the acitvity id
1111 l_activity_id := wf_engine.GetItemAttrNumber(
1112 itemtype => itemtype,
1113 itemkey => itemkey,
1114 aname => 'AMS_ACTIVITY_ID' );
1115
1116 -- get the activity type
1117 l_activity_type := wf_engine.GetItemAttrText(
1118 itemtype => itemtype,
1119 itemkey => itemkey,
1120 aname => 'AMS_ACTIVITY_TYPE' );
1121
1122 l_approval_type := wf_engine.GetItemAttrText(
1123 itemtype => itemtype,
1124 itemkey => itemkey,
1125 aname => 'AMS_APPROVAL_TYPE' );
1126
1127 Get_Api_Name('WORKFLOW', l_activity_type, 'SET_ACTIVITY_DETAILS', l_approval_type, l_pkg_name, l_proc_name, l_return_stat);
1128
1129 IF (l_return_stat = 'S') THEN
1130 dml_str := 'BEGIN ' || l_pkg_name||'.'||l_proc_name||'(:itemtype,:itemkey,:actid,:funcmode,:resultout); END;';
1131 EXECUTE IMMEDIATE dml_str USING IN itemtype,IN itemkey,IN actid,IN funcmode, OUT resultout;
1132
1133 -- [BEGIN OF BUG2540804 FIXING 08/29/2002 - mchnag]
1134 ELSE
1135 RAISE FND_API.G_EXC_ERROR;
1136 END IF;
1137
1138 --resultout := 'COMPLETE:SUCCESS';
1139 -- [END OF BUG2540804 FIXING]
1140
1141
1142 END IF;
1143
1144 --
1145 -- CANCEL mode
1146 --
1147 IF (funcmode = 'CANCEL') THEN
1148 resultout := 'COMPLETE:';
1149 return;
1150 END IF;
1151
1152 --
1153 -- TIMEOUT mode
1154 --
1155 IF (funcmode = 'TIMEOUT') THEN
1156 resultout := 'COMPLETE:';
1157 return;
1158 END IF;
1159 --
1160
1161 EXCEPTION
1162 WHEN FND_API.G_EXC_ERROR THEN
1163 wf_core.context('ams_gen_approval_pvt','Set_Activity_Details',
1164 itemtype,itemkey,actid,funcmode,l_error_msg);
1165 raise;
1166 WHEN OTHERS THEN
1167 FND_MSG_PUB.Count_And_Get (
1168 p_encoded => FND_API.G_FALSE,
1169 p_count => l_msg_count,
1170 p_data => l_msg_data
1171 );
1172 Handle_Err
1173 (p_itemtype => itemtype ,
1174 p_itemkey => itemkey ,
1175 p_msg_count => l_msg_count, -- Number of error Messages
1176 p_msg_data => l_msg_data ,
1177 p_attr_name => 'AMS_ERROR_MSG',
1178 x_error_msg => l_error_msg
1179 ) ;
1180 wf_core.context('ams_approval_pvt','Set_Activity_Details',
1181 itemtype,itemkey,actid,funcmode,l_error_msg);
1182 raise;
1183 END Set_Activity_Details ;
1184
1185
1186 -------------------------------------------------------------------------------
1187 --
1188 -- Prepare_Doc
1189 --
1190 -------------------------------------------------------------------------------
1191
1192 PROCEDURE Prepare_Doc( itemtype in varchar2,
1193 itemkey in varchar2,
1194 actid in number,
1195 funcmode in varchar2,
1196 resultout OUT NOCOPY varchar2 )
1197 IS
1198 BEGIN
1199 FND_MSG_PUB.initialize();
1200 IF (funcmode = 'RUN') THEN
1201 resultout := 'COMPLETE:SUCCESS';
1202 END IF;
1203
1204 --
1205 -- CANCEL mode
1206 --
1207 IF (funcmode = 'CANCEL') THEN
1208 resultout := 'COMPLETE:';
1209 return;
1210 END IF;
1211
1212 --
1213 -- TIMEOUT mode
1214 --
1215 IF (funcmode = 'TIMEOUT') THEN
1216 resultout := 'COMPLETE:';
1217 return;
1218 END IF;
1219 --
1220
1221 END;
1222
1223 -------------------------------------------------------------------------------
1224 --
1225 -- Set_Approver_Details
1226 --
1227 -------------------------------------------------------------------------------
1228 PROCEDURE Set_Approver_Details( itemtype in varchar2,
1229 itemkey in varchar2,
1230 actid in number,
1231 funcmode in varchar2,
1232 resultout OUT NOCOPY varchar2 )
1233 IS
1234 l_activity_type VARCHAR2(80);
1235 l_current_seq NUMBER;
1236 l_approval_detail_id NUMBER;
1237 l_approver_id NUMBER;
1238 l_approver VARCHAR2(320); -- Was VARCHAR2(100);
1239 l_prev_approver VARCHAR2(320); -- Was VARCHAR2(100);
1240 l_approver_display_name VARCHAR2(360); -- Was VARCHAR2(80)
1241 l_notification_type VARCHAR2(30);
1242 l_notification_timeout NUMBER;
1243 l_approver_type VARCHAR2(30);
1244 l_role_name VARCHAR2(100); --l_role_name VARCHAR2(30);
1245 l_prev_role_name VARCHAR2(100); --l_prev_role_name VARCHAR2(30);
1246 l_object_approver_id NUMBER;
1247 l_return_status VARCHAR2(1);
1248 l_msg_count NUMBER;
1249 l_msg_data VARCHAR2(4000);
1250 l_error_msg VARCHAR2(4000);
1251 l_pkg_name varchar2(80);
1252 l_proc_name varchar2(80);
1253 l_appr_id NUMBER;
1254 dml_str VARCHAR2(2000);
1255 -- 11.5.9
1256 l_appr_hist_rec AMS_Appr_Hist_Pvt.Appr_Hist_Rec_Type;
1257 l_version NUMBER;
1258 l_approval_type VARCHAR2(30);
1259 l_activity_id NUMBER;
1260 l_appr_seq NUMBER;
1261 l_appr_type VARCHAR2(30);
1262 l_obj_appr_id NUMBER;
1263 l_prev_approver_disp_name VARCHAR2(360);
1264 l_note VARCHAR2(4000);
1265
1266 CURSOR c_approver(rule_id IN NUMBER) IS
1267 SELECT approver_seq, approver_type, object_approver_id
1268 FROM ams_approvers
1269 WHERE ams_approval_detail_id = rule_id
1270 AND active_flag = 'Y'
1271 -- Bug 3558516 No trunc for start_date_active
1272 AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(start_date_active,SYSDATE -1 ))
1273 AND TRUNC(NVL(end_date_active,SYSDATE + 1));
1274
1275 CURSOR c_API_Name(rule_id_in IN NUMBER) is
1276 SELECT package_name, procedure_name
1277 FROM ams_object_rules_b
1278 WHERE OBJECT_RULE_ID = rule_id_in;
1279
1280 BEGIN
1281 FND_MSG_PUB.initialize();
1282 IF (funcmode = 'RUN') THEN
1283 l_approval_detail_id := wf_engine.GetItemAttrNumber(
1284 itemtype => itemtype,
1285 itemkey => itemkey,
1286 aname => 'AMS_APPROVAL_DETAIL_ID' );
1287
1288 l_current_seq := wf_engine.GetItemAttrText(
1289 itemtype => itemtype,
1290 itemkey => itemkey,
1291 aname => 'AMS_APPROVER_SEQ' );
1292
1293 -- 11.5.9
1294
1295 l_activity_id := Wf_Engine.GetItemAttrNumber(
1296 itemtype => itemtype,
1297 itemkey => itemkey,
1298 aname => 'AMS_ACTIVITY_ID' );
1299
1300 l_activity_type := Wf_Engine.GetItemAttrText(
1301 itemtype => itemtype,
1302 itemkey => itemkey,
1303 aname => 'AMS_ACTIVITY_TYPE' );
1304
1305 l_version := Wf_Engine.GetItemAttrNumber(
1306 itemtype => itemtype,
1307 itemkey => itemkey,
1308 aname => 'AMS_OBJECT_VERSION_NUMBER' );
1309
1310 l_approval_type := Wf_Engine.GetItemAttrText(
1311 itemtype => itemtype,
1312 itemkey => itemkey,
1313 aname => 'AMS_APPROVAL_TYPE' );
1314
1315 Get_approver_Info
1316 ( p_approval_detail_id => l_approval_detail_id,
1317 p_current_seq => l_current_seq ,
1318 x_approver_id => l_approver_id,
1319 x_approver_type => l_approver_type,
1320 x_role_name => l_role_name ,
1321 x_object_approver_id => l_object_approver_id,
1322 x_notification_type => l_notification_type,
1323 x_notification_timeout => l_notification_type,
1324 x_return_status => l_return_status);
1325
1326 IF l_return_status <> Fnd_Api.G_RET_STS_SUCCESS THEN
1327 RAISE FND_API.G_EXC_ERROR;
1328 --RETURN;
1329 END IF;
1330
1331 -- Bug 2729108 Fix
1332 IF l_current_seq = 1 OR
1333 Is_Min_Sequence(l_approval_detail_id, l_current_seq) THEN
1334
1335 -- Get all the obj attributes once for inserts
1336
1337 -- Set Record Attributes that won't change for each approver
1338 l_appr_hist_rec.object_id := l_activity_id;
1339 l_appr_hist_rec.object_type_code := l_activity_type;
1340 l_appr_hist_rec.object_version_num := l_version;
1341 l_appr_hist_rec.action_code := 'OPEN';
1342 l_appr_hist_rec.approval_type := l_approval_type;
1343 l_appr_hist_rec.approval_detail_id := l_approval_detail_id;
1344
1345 OPEN c_approver(l_approval_detail_id);
1346 LOOP
1347 FETCH c_approver INTO l_appr_seq, l_appr_type, l_obj_appr_id;
1348 EXIT WHEN c_approver%NOTFOUND;
1349
1350 -- Set Record Attributes that will change for each approver
1351 l_appr_hist_rec.sequence_num := l_appr_seq;
1352 l_appr_hist_rec.approver_type := l_appr_type;
1353 l_appr_hist_rec.approver_id := l_obj_appr_id;
1354
1355 AMS_Appr_Hist_PVT.Create_Appr_Hist(
1356 p_api_version_number => 1.0,
1357 p_init_msg_list => FND_API.G_FALSE,
1358 p_commit => FND_API.G_FALSE,
1359 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1360 x_return_status => l_return_status,
1361 x_msg_count => l_msg_count,
1362 x_msg_data => l_msg_data,
1363 p_appr_hist_rec => l_appr_hist_rec
1364 );
1365
1366 IF l_return_status <> Fnd_Api.G_RET_STS_SUCCESS THEN
1367 RAISE FND_API.G_EXC_ERROR;
1368 END IF;
1369
1370 END LOOP;
1371 CLOSE c_approver;
1372 END IF;
1373
1374 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
1375 IF (l_approver_type = 'FUNCTION') THEN
1376 open c_API_Name(l_object_approver_id);
1377 fetch c_API_Name into l_pkg_name, l_proc_name;
1378 IF (c_Api_Name%FOUND) THEN
1379 dml_str := 'BEGIN ' || l_pkg_name||'.'||l_proc_name||'(:itemtype,:itemkey,:appr_id, :l_return_stat); END;';
1380 EXECUTE IMMEDIATE dml_str USING IN itemtype,IN itemkey, OUT l_appr_id, OUT l_return_status;
1381
1382 IF (l_return_status = 'S') THEN
1383 l_object_approver_id := l_appr_id;
1384 END IF;
1385
1386 END IF;
1387 close c_API_Name;
1388 END IF;
1389 ELSE
1390 RAISE FND_API.G_EXC_ERROR;
1391 END IF;
1392
1393
1394 Get_User_Role(p_user_id => l_object_approver_id ,
1395 x_role_name => l_approver,
1396 x_role_display_name => l_approver_display_name,
1397 x_return_status => l_return_status);
1398
1399 IF l_return_status <> FND_API.G_RET_STS_SUCCESS then
1400 RAISE FND_API.G_EXC_ERROR;
1401 END IF;
1402 -- Change for Bug 2535600
1403 l_prev_approver_disp_name := wf_engine.GetItemAttrText(
1404 itemtype => itemtype,
1405 itemkey => itemkey,
1406 aname => 'AMS_APPROVER_DISPLAY_NAME' );
1407
1408 -- Added for Bug 2535600
1409 wf_engine.SetItemAttrText( itemtype => itemtype,
1410 itemkey => itemkey,
1411 aname => 'AMS_PREV_APPROVER_DISP_NAME',
1412 avalue => l_prev_approver_disp_name);
1413
1414 l_note := wf_engine.GetItemAttrText(itemtype => itemtype,
1415 itemkey => itemkey,
1416 aname => 'APPROVAL_NOTE');
1417
1418 wf_engine.SetItemAttrText( itemtype => itemtype,
1419 itemkey => itemkey,
1420 aname => 'AMS_PREV_APPROVER_NOTE',
1421 avalue => l_note);
1422
1423 -- Need to be set to null or else it consolidates notes
1424 wf_engine.SetItemAttrText( itemtype => itemtype,
1425 itemkey => itemkey,
1426 aname => 'APPROVAL_NOTE',
1427 avalue => null);
1428
1429 wf_engine.SetItemAttrText( itemtype => itemtype,
1430 itemkey => itemkey,
1431 aname => 'AMS_APPROVER_DISPLAY_NAME',
1432 avalue => l_approver_display_name);
1433 -- End of 2535600
1434
1435
1436 wf_engine.SetItemAttrText( itemtype => itemtype,
1437 itemkey => itemkey,
1438 aname => 'AMS_APPROVER',
1439 avalue => l_approver);
1440
1441 wf_engine.SetItemAttrNumber(itemtype => itemtype,
1442 itemkey => itemkey,
1443 aname => 'AMS_APPROVER_ID',
1444 avalue => l_object_approver_id);
1445
1446 -- 11.5.9 Update the 'Open' row to 'Pending'
1447
1448 l_appr_hist_rec.object_id := l_activity_id;
1449 l_appr_hist_rec.object_type_code := l_activity_type;
1450 l_appr_hist_rec.object_version_num := l_version;
1451 l_appr_hist_rec.action_code := 'PENDING';
1452 l_appr_hist_rec.action_date := sysdate;
1453 l_appr_hist_rec.approval_type := l_approval_type;
1454 l_appr_hist_rec.approver_type := l_approver_type;
1455 l_appr_hist_rec.sequence_num := l_current_seq;
1456 l_appr_hist_rec.approver_id := l_object_approver_id;
1457
1458 AMS_Appr_Hist_PVT.Update_Appr_Hist(
1459 p_api_version_number => 1.0,
1460 p_init_msg_list => FND_API.G_FALSE,
1461 p_commit => FND_API.G_FALSE,
1462 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1463 x_return_status => l_return_status,
1464 x_msg_count => l_msg_count,
1465 x_msg_data => l_msg_data,
1466 p_appr_hist_rec => l_appr_hist_rec
1467 );
1468
1469 IF l_return_status <> Fnd_Api.G_RET_STS_SUCCESS THEN
1470 RAISE Fnd_Api.G_EXC_ERROR;
1471 END IF;
1472
1473 resultout := 'COMPLETE:SUCCESS';
1474 END IF;
1475
1476 --
1477 -- CANCEL mode
1478 --
1479 IF (funcmode = 'CANCEL') THEN
1480 resultout := 'COMPLETE:';
1481 return;
1482 END IF;
1483
1484 --
1485 -- TIMEOUT mode
1486 --
1487 IF (funcmode = 'TIMEOUT') THEN
1488 resultout := 'COMPLETE:';
1489 return;
1490 END IF;
1491
1492 EXCEPTION
1493 WHEN FND_API.G_EXC_ERROR THEN
1494 FND_MSG_PUB.Count_And_Get (
1495 p_encoded => FND_API.G_FALSE,
1496 p_count => l_msg_count,
1497 p_data => l_msg_data
1498 );
1499 Handle_Err
1500 (p_itemtype => itemtype ,
1501 p_itemkey => itemkey ,
1502 p_msg_count => l_msg_count, -- Number of error Messages
1503 p_msg_data => l_msg_data ,
1504 p_attr_name => 'AMS_ERROR_MSG',
1505 x_error_msg => l_error_msg
1506 );
1507 wf_core.context('ams_gen_approval_pvt',
1508 'set_approval_rules',
1509 itemtype, itemkey,to_char(actid),l_error_msg);
1510 resultout := 'COMPLETE:ERROR';
1511 --RAISE;
1512 WHEN OTHERS THEN
1513 FND_MSG_PUB.Count_And_Get (
1514 p_encoded => FND_API.G_FALSE,
1515 p_count => l_msg_count,
1516 p_data => l_msg_data
1517 );
1518 Handle_Err
1519 (p_itemtype => itemtype ,
1520 p_itemkey => itemkey ,
1521 p_msg_count => l_msg_count, -- Number of error Messages
1522 p_msg_data => l_msg_data ,
1523 p_attr_name => 'AMS_ERROR_MSG',
1524 x_error_msg => l_error_msg
1525 ) ;
1526 wf_core.context('ams_gen_approval_pvt',
1527 'set_approver_details',
1528 itemtype, itemkey,to_char(actid),l_error_msg);
1529 RAISE;
1530 --
1531
1532 END;
1533
1534 -------------------------------------------------------------------------------
1535 --
1536 -- Set_Further_Approvals
1537 --
1538 -------------------------------------------------------------------------------
1539 PROCEDURE Set_Further_Approvals( itemtype in varchar2,
1540 itemkey in varchar2,
1541 actid in number,
1542 funcmode in varchar2,
1543 resultout OUT NOCOPY varchar2 )
1544 IS
1545 l_current_seq NUMBER;
1546 l_next_seq NUMBER;
1547 l_approval_detail_id NUMBER;
1548 l_required_flag VARCHAR2(1);
1549 l_approver_id NUMBER;
1550 l_note VARCHAR2(4000);
1551 l_all_note VARCHAR2(4000);
1552 l_activity_type VARCHAR2(30);
1553 l_activity_id NUMBER;
1554 l_msg_count NUMBER;
1555 l_msg_data VARCHAR2(4000);
1556 l_return_status VARCHAR2(1);
1557 l_error_msg VARCHAR2(4000);
1558 -- 11.5.9
1559 l_version NUMBER;
1560 l_approval_type VARCHAR2(30);
1561 l_appr_hist_rec AMS_Appr_Hist_Pvt.Appr_Hist_Rec_Type;
1562 --
1563 l_forward_nid NUMBER;
1564 l_responder VARCHAR2(320);
1565 l_appr_display_name VARCHAR2(360);
1566 BEGIN
1567 FND_MSG_PUB.initialize();
1568 IF (funcmode = 'RUN') THEN
1569 l_approval_detail_id := wf_engine.GetItemAttrNumber(
1570 itemtype => itemtype,
1571 itemkey => itemkey,
1572 aname => 'AMS_APPROVAL_DETAIL_ID' );
1573
1574 l_current_seq := wf_engine.GetItemAttrText(
1575 itemtype => itemtype,
1576 itemkey => itemkey,
1577 aname => 'AMS_APPROVER_SEQ' );
1578
1579 l_approver_id := wf_engine.GetItemAttrNumber(
1580 itemtype => itemtype,
1581 itemkey => itemkey,
1582 aname => 'AMS_APPROVER_ID' );
1583
1584 l_activity_id := wf_engine.GetItemAttrNumber(
1585 itemtype => itemtype,
1586 itemkey => itemkey,
1587 aname => 'AMS_ACTIVITY_ID' );
1588
1589 l_activity_type := wf_engine.GetItemAttrText(
1590 itemtype => itemtype,
1591 itemkey => itemkey,
1592 aname => 'AMS_ACTIVITY_TYPE' );
1593
1594 -- Added for 11.5.9
1595 -- Bug 2535600
1596 wf_engine.SetItemAttrText( itemtype => itemtype,
1597 itemkey => itemkey,
1598 aname => 'AMS_APPROVAL_DATE',
1599 avalue => trunc(sysdate));
1600
1601 l_version := Wf_Engine.GetItemAttrNumber(
1602 itemtype => itemtype,
1603 itemkey => itemkey,
1604 aname => 'AMS_OBJECT_VERSION_NUMBER' );
1605
1606 l_approval_type := Wf_Engine.GetItemAttrText(
1607 itemtype => itemtype,
1608 itemkey => itemkey,
1609 aname => 'AMS_APPROVAL_TYPE' );
1610
1611 l_note := Wf_Engine.GetItemAttrText(
1612 itemtype => itemtype,
1613 itemkey => itemkey,
1614 aname => 'APPROVAL_NOTE' );
1615
1616 -- Start of addition for forward/reassign notification
1617
1618 l_forward_nid := Wf_Engine.GetItemAttrNumber(
1619 itemtype => itemtype,
1620 itemkey => itemkey,
1621 aname => 'AMS_FORWARD_NID' );
1622 -- Commented for 3150550
1623 /*
1624 IF l_forward_nid IS NOT NULL THEN
1625
1626 l_responder := wf_notification.responder(l_forward_nid);
1627
1628 Get_New_Res_Details(p_responder => l_responder,
1629 x_resource_id => l_approver_id,
1630 x_resource_disp_name => l_appr_display_name,
1631 x_return_status => l_return_status);
1632
1633 IF l_return_status <> Fnd_Api.G_RET_STS_SUCCESS THEN
1634 RAISE Fnd_Api.G_EXC_ERROR;
1635 END IF;
1636
1637 -- Set the WF Attributes
1638 wf_engine.SetItemAttrText( itemtype => itemtype,
1639 itemkey => itemkey,
1640 aname => 'AMS_APPROVER',
1641 avalue => l_responder);
1642
1643
1644 wf_engine.SetItemAttrNumber(itemtype => itemtype,
1645 itemkey => itemkey,
1646 aname => 'AMS_APPROVER_ID',
1647 avalue => l_approver_id);
1648
1649
1650 wf_engine.SetItemAttrText( itemtype => itemtype,
1651 itemkey => itemkey,
1652 aname => 'AMS_APPROVER_DISPLAY_NAME',
1653 avalue => l_appr_display_name);
1654
1655 -- Reset the forward_nid wf attribute to null
1656 -- This is a must
1657
1658 wf_engine.SetItemAttrNumber(itemtype => itemtype,
1659 itemkey => itemkey,
1660 aname => 'AMS_FORWARD_NID',
1661 avalue => null);
1662 END IF;
1663 -- End of addition for forward/re-assign notification
1664 */
1665 -- update the record from 'PENDING' to 'APPROVED'
1666 l_appr_hist_rec.object_id := l_activity_id;
1667 l_appr_hist_rec.object_type_code := l_activity_type;
1668 l_appr_hist_rec.object_version_num := l_version;
1669 l_appr_hist_rec.action_code := 'APPROVED';
1670 l_appr_hist_rec.approval_type := l_approval_type;
1671 l_appr_hist_rec.sequence_num := l_current_seq;
1672 l_appr_hist_rec.approver_id := l_approver_id;
1673 l_appr_hist_rec.note := l_note;
1674 l_appr_hist_rec.action_date := sysdate;
1675
1676 AMS_Appr_Hist_PVT.Update_Appr_Hist(
1677 p_api_version_number => 1.0,
1678 p_init_msg_list => FND_API.G_FALSE,
1679 p_commit => FND_API.G_FALSE,
1680 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1681 x_return_status => l_return_status,
1682 x_msg_count => l_msg_count,
1683 x_msg_data => l_msg_data,
1684 p_appr_hist_rec => l_appr_hist_rec
1685 );
1686
1687 IF l_return_status <> Fnd_Api.G_RET_STS_SUCCESS THEN
1688 RAISE Fnd_Api.G_EXC_ERROR;
1689 END IF;
1690
1691 Check_Approval_Required
1692 ( p_approval_detail_id => l_approval_detail_id,
1693 p_current_seq => l_current_seq,
1694 x_next_seq => l_next_seq,
1695 x_required_flag => l_required_flag);
1696 IF l_next_seq is not null THEN
1697 wf_engine.SetItemAttrNumber(itemtype => itemtype,
1698 itemkey => itemkey,
1699 aname => 'AMS_APPROVER_SEQ',
1700 avalue => l_next_seq);
1701 resultout := 'COMPLETE:Y';
1702 ELSE
1703 resultout := 'COMPLETE:N';
1704 END IF;
1705 END IF;
1706
1707 --
1708 -- CANCEL mode
1709 --
1710 IF (funcmode = 'CANCEL') THEN
1711 resultout := 'COMPLETE:';
1712 return;
1713 END IF;
1714
1715 --
1716 -- TIMEOUT mode
1717 --
1718 IF (funcmode = 'TIMEOUT') THEN
1719 resultout := 'COMPLETE:';
1720 return;
1721 END IF;
1722 EXCEPTION
1723 WHEN FND_API.G_EXC_ERROR THEN
1724 FND_MSG_PUB.Count_And_Get (
1725 p_encoded => FND_API.G_FALSE,
1726 p_count => l_msg_count,
1727 p_data => l_msg_data
1728 );
1729 Handle_Err
1730 (p_itemtype => itemtype ,
1731 p_itemkey => itemkey ,
1732 p_msg_count => l_msg_count, -- Number of error Messages
1733 p_msg_data => l_msg_data ,
1734 p_attr_name => 'AMS_ERROR_MSG',
1735 x_error_msg => l_error_msg
1736 ) ;
1737 wf_core.context('ams_gen_approval_pvt',
1738 'set_further_approvals',
1739 itemtype, itemkey,to_char(actid),l_error_msg);
1740 RAISE;
1741 WHEN OTHERS THEN
1742 FND_MSG_PUB.Count_And_Get (
1743 p_encoded => FND_API.G_FALSE,
1744 p_count => l_msg_count,
1745 p_data => l_msg_data
1746 );
1747 Handle_Err
1748 (p_itemtype => itemtype ,
1749 p_itemkey => itemkey ,
1750 p_msg_count => l_msg_count, -- Number of error Messages
1751 p_msg_data => l_msg_data ,
1752 p_attr_name => 'AMS_ERROR_MSG',
1753 x_error_msg => l_error_msg
1754 ) ;
1755 wf_core.context('ams_gen_approval_pvt',
1756 'set_further_approvals',
1757 itemtype, itemkey,to_char(actid),l_error_msg);
1758 RAISE;
1759 --
1760
1761 END;
1762
1763 -------------------------------------------------------------------------------
1764 --
1765 -- Revert_Status
1766 --
1767 -------------------------------------------------------------------------------
1768 PROCEDURE Revert_Status( itemtype in varchar2,
1769 itemkey in varchar2,
1770 actid in number,
1771 funcmode in varchar2,
1772 resultout OUT NOCOPY varchar2 )
1773 IS
1774 l_activity_id NUMBER;
1775 l_activity_type VARCHAR2(30);
1776 l_orig_status_id NUMBER;
1777 l_return_status varchar2(1);
1778 l_msg_count NUMBER;
1779 l_msg_data VARCHAR2(4000);
1780 l_error_msg VARCHAR2(4000);
1781 -- 11.5.9
1782 l_version NUMBER;
1783 l_approval_type VARCHAR2(30);
1784 l_appr_hist_rec AMS_Appr_Hist_Pvt.Appr_Hist_Rec_Type;
1785 BEGIN
1786 FND_MSG_PUB.initialize();
1787 /*
1788 UPDATE AMS_CAMPAIGNS_ALL_B
1789 SET user_status_id = 100,
1790 status_code = 'New',
1791 status_date = sysdate,
1792 object_version_number = object_version_number + 1
1793 WHERE campaign_id = 10112;
1794 */
1795 --
1796 -- RUN mode
1797 --
1798 IF (funcmode = 'RUN') THEN
1799 -- get the acitvity id
1800 l_activity_id := wf_engine.GetItemAttrNumber(
1801 itemtype => itemtype,
1802 itemkey => itemkey,
1803 aname => 'AMS_ACTIVITY_ID' );
1804
1805 -- get the activity type
1806 l_activity_type := wf_engine.GetItemAttrText(
1807 itemtype => itemtype,
1808 itemkey => itemkey,
1809 aname => 'AMS_ACTIVITY_TYPE' );
1810
1811 l_version := wf_engine.GetItemAttrNumber(
1812 itemtype => itemtype,
1813 itemkey => itemkey,
1814 aname => 'AMS_OBJECT_VERSION_NUMBER' );
1815
1816 l_approval_type := wf_engine.GetItemAttrText(
1817 itemtype => itemtype,
1818 itemkey => itemkey,
1819 aname => 'AMS_APPROVAL_TYPE' );
1820
1821 l_orig_status_id := wf_engine.GetItemAttrNumber(
1822 itemtype => itemtype,
1823 itemkey => itemkey,
1824 aname => 'AMS_ORIG_STAT_ID' );
1825
1826 -- Added by VMODUR on July-02-2002
1827 Wf_Engine.SetItemAttrText(itemtype => itemtype,
1828 itemkey => itemkey,
1829 aname => 'UPDATE_GEN_STATUS',
1830 avalue => 'ERROR');
1831
1832 Update_Status(itemtype => itemtype,
1833 itemkey => itemkey,
1834 actid => actid,
1835 funcmode => funcmode,
1836 resultout => resultout);
1837
1838 IF resultout = 'COMPLETE:ERROR' then -- added VMODUR 10-Jun-2002
1839
1840 FND_MSG_PUB.Count_And_Get (
1841 p_encoded => FND_API.G_FALSE,
1842 p_count => l_msg_count,
1843 p_data => l_msg_data
1844 );
1845 Handle_Err
1846 (p_itemtype => itemtype ,
1847 p_itemkey => itemkey ,
1848 p_msg_count => l_msg_count, -- Number of error Messages
1849 p_msg_data => l_msg_data ,
1850 p_attr_name => 'AMS_ERROR_MSG',
1851 x_error_msg => l_error_msg
1852 );
1853 ELSE
1854 -- Delete all rows
1855 AMS_Appr_Hist_PVT.Delete_Appr_Hist(
1856 p_api_version_number => 1.0,
1857 p_init_msg_list => FND_API.G_FALSE,
1858 p_commit => FND_API.G_FALSE,
1859 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1860 x_return_status => l_return_status,
1861 x_msg_count => l_msg_count,
1862 x_msg_data => l_msg_data,
1863 p_object_id => l_activity_id,
1864 p_object_type_code => l_activity_type,
1865 p_sequence_num => null,
1866 p_action_code => null,
1867 p_object_version_num => l_version,
1868 p_approval_type => l_approval_type);
1869
1870 IF l_return_status <> Fnd_Api.G_RET_STS_SUCCESS THEN
1871 RAISE Fnd_Api.G_EXC_ERROR;
1872 END IF;
1873
1874 END IF; -- VM
1875
1876 resultout := 'COMPLETE:';
1877 END IF;
1878
1879 --
1880 -- CANCEL mode
1881 --
1882 IF (funcmode = 'CANCEL') THEN
1883 resultout := 'COMPLETE:';
1884 return;
1885 END IF;
1886
1887 --
1888 -- TIMEOUT mode
1889 --
1890 IF (funcmode = 'TIMEOUT') THEN
1891 resultout := 'COMPLETE:';
1892 return;
1893 END IF;
1894 --
1895
1896 EXCEPTION
1897 WHEN FND_API.G_EXC_ERROR THEN
1898 FND_MSG_PUB.Count_And_Get (
1899 p_encoded => FND_API.G_FALSE,
1900 p_count => l_msg_count,
1901 p_data => l_msg_data
1902 );
1903 Handle_Err
1904 (p_itemtype => itemtype ,
1905 p_itemkey => itemkey ,
1906 p_msg_count => l_msg_count, -- Number of error Messages
1907 p_msg_data => l_msg_data ,
1908 p_attr_name => 'AMS_ERROR_MSG',
1909 x_error_msg => l_error_msg
1910 );
1911 wf_core.context('ams_gen_approval_pvt',
1912 'Revert_Status',
1913 itemtype, itemkey,to_char(actid),l_error_msg);
1914 resultout := 'COMPLETE:ERROR';
1915 --RAISE;
1916 WHEN OTHERS THEN
1917 FND_MSG_PUB.Count_And_Get (
1918 p_encoded => FND_API.G_FALSE,
1919 p_count => l_msg_count,
1920 p_data => l_msg_data
1921 );
1922 Handle_Err
1923 (p_itemtype => itemtype ,
1924 p_itemkey => itemkey ,
1925 p_msg_count => l_msg_count, -- Number of error Messages
1926 p_msg_data => l_msg_data ,
1927 p_attr_name => 'AMS_ERROR_MSG',
1928 x_error_msg => l_error_msg
1929 ) ;
1930 wf_core.context('ams_gen_approval_pvt',
1931 'Revert_status',
1932 itemtype, itemkey,to_char(actid),l_error_msg);
1933 RAISE;
1934 END Revert_Status;
1935
1936 /*****************************************************************
1937 -- Start of Comments
1938 --
1939 -- NAME
1940 -- AbortProcess
1941 -- PURPOSE
1942 -- This Procedure will abort the process of Approvals
1943 -- Used By Activities
1944 --
1945 -- NOTES
1946 -- HISTORY
1947 -- End of Comments
1948 *****************************************************************/
1949
1950 PROCEDURE AbortProcess
1951 (p_itemkey IN VARCHAR2
1952 ,p_workflowprocess IN VARCHAR2 DEFAULT NULL
1953 ,p_itemtype IN VARCHAR2 DEFAULT NULL
1954 )
1955 IS
1956 itemkey VARCHAR2(30) := p_itemkey ;
1957 itemtype VARCHAR2(30) := nvl(p_itemtype,'AMS_APPROVAL') ;
1958 BEGIN
1959 AMS_Utility_PVT.debug_message('Process Abort Process');
1960 WF_ENGINE.AbortProcess (itemtype => itemtype,
1961 itemkey => itemkey ,
1962 process => p_workflowprocess);
1963 EXCEPTION
1964 WHEN OTHERS THEN
1965 wf_core.context('ams_gen_approval_pvt',
1966 'AbortProcess',
1967 itemtype,
1968 itemkey
1969 ,p_workflowprocess);
1970 RAISE;
1971 END AbortProcess;
1972
1973
1974 --------------------------------------------------------------------------------
1975 --
1976 -- Procedure
1977 -- Get_Api_Name
1978 --
1979 ---------------------------------------------------------------------------------
1980 PROCEDURE Get_Api_Name( p_rule_used_by in varchar2,
1981 p_rule_used_by_type in varchar2,
1982 p_rule_type in VARCHAR2,
1983 p_appr_type in VARCHAR2,
1984 x_pkg_name OUT NOCOPY varchar2,
1985 x_proc_name OUT NOCOPY varchar2,
1986 x_return_stat OUT NOCOPY varchar2)
1987 IS
1988 CURSOR c_API_Name(rule_used_by_in IN VARCHAR2,
1989 rule_used_by_type_in IN VARCHAR2,
1990 rule_type_in IN VARCHAR2,
1991 appr_type_in IN VARCHAR2) is
1992 SELECT package_name, procedure_name
1993 FROM ams_object_rules_b
1994 WHERE rule_used_by = rule_used_by_in
1995 AND rule_used_by_type = rule_used_by_type_in
1996 AND rule_type = rule_type_in
1997 AND nvl(APPROVAL_TYPE, 'NIL') = nvl(appr_type_in, 'NIL');
1998
1999 BEGIN
2000 x_return_stat := 'S';
2001 open c_API_Name(p_rule_used_by, p_rule_used_by_type,p_rule_type,p_appr_type);
2002 fetch c_API_Name into x_pkg_name, x_proc_name;
2003 IF c_API_Name%NOTFOUND THEN
2004 x_return_stat := 'E';
2005 END IF;
2006 close c_API_Name;
2007 EXCEPTION
2008 -- This exception will never be raised VMODUR 10-Jun-2002
2009 --WHEN NO_DATA_FOUND THEN
2010 -- x_return_stat := 'E';
2011 WHEN OTHERS THEN
2012 x_return_stat := 'U';
2013 RAISE;
2014 END Get_Api_Name;
2015
2016
2017 --------------------------------------------------------------------------------
2018 --
2019 -- Procedure
2020 -- Ntf_Approval(document_id in varchar2,
2021 -- display_type in varchar2,
2022 -- document in out varchar2,
2023 -- document_type in out varchar2 )
2024 ---------------------------------------------------------------------------------
2025 PROCEDURE Ntf_Approval(document_id in varchar2,
2026 display_type in varchar2,
2027 document in OUT NOCOPY varchar2,
2028 document_type in OUT NOCOPY varchar2 )
2029 IS
2030 l_pkg_name varchar2(80);
2031 l_proc_name varchar2(80);
2032 l_return_stat varchar2(1);
2033 l_activity_type varchar2(80);
2034 l_approval_type varchar2(80);
2035 l_msg_data VARCHAR2(4000);
2036 l_msg_count number;
2037 l_error_msg VARCHAR2(4000);
2038 dml_str varchar2(2000);
2039 l_itemType varchar2(80);
2040 l_itemKey varchar2(80);
2041 BEGIN
2042 l_itemType := nvl(substr(document_id, 1,instr(document_id,':')-1),'AMSGAPP');
2043 l_itemKey := substr(document_id, instr(document_id,':')+1);
2044
2045 l_activity_type := wf_engine.GetItemAttrText(
2046 itemtype => l_itemtype,
2047 itemkey => l_itemkey,
2048 aname => 'AMS_ACTIVITY_TYPE' );
2049
2050 l_approval_type := wf_engine.GetItemAttrText(
2051 itemtype => l_itemtype,
2052 itemkey => l_itemkey,
2053 aname => 'AMS_APPROVAL_TYPE' );
2054
2055 Get_Api_Name('WORKFLOW', l_activity_type, 'NTF_APPROVAL',l_approval_type, l_pkg_name, l_proc_name, l_return_stat);
2056 if (l_return_stat = 'S') then
2057 dml_str := 'BEGIN ' || l_pkg_name||'.'||l_proc_name||'(:document_id,:display_type,:document,:document_type); END;';
2058 EXECUTE IMMEDIATE dml_str USING IN document_id,IN display_type,IN OUT document, IN OUT document_type;
2059 end if;
2060 /*
2061 EXCEPTION
2062 WHEN OTHERS THEN
2063 FND_MSG_PUB.Count_And_Get (
2064 p_encoded => FND_API.G_FALSE,
2065 p_count => l_msg_count,
2066 p_data => l_msg_data
2067 );
2068 Handle_Err
2069 (p_itemtype => itemtype ,
2070 p_itemkey => itemkey ,
2071 p_msg_count => l_msg_count, -- Number of error Messages
2072 p_msg_data => l_msg_data ,
2073 p_attr_name => 'AMS_ERROR_MSG',
2074 x_error_msg => l_error_msg
2075 ) ;
2076 wf_core.context('ams_gen_approval_pvt',
2077 'Reject_activity_status',
2078 itemtype, itemkey,to_char(actid),l_error_msg);
2079 RAISE;
2080 */
2081 END Ntf_Approval;
2082
2083 --------------------------------------------------------------------------------
2084 --
2085 -- Procedure
2086 -- Ntf_Approval_reminder(itemtype in varchar2,
2087 -- itemkey in varchar2,
2088 -- p_object_type in varchar2,
2089 -- actid in number,
2090 -- funcmode in varchar2,
2091 -- resultout out varchar2 )
2092 ---------------------------------------------------------------------------------
2093 PROCEDURE Ntf_Approval_reminder(document_id in varchar2,
2094 display_type in varchar2,
2095 document in OUT NOCOPY varchar2,
2096 document_type in OUT NOCOPY varchar2 )
2097 IS
2098 l_pkg_name varchar2(80);
2099 l_proc_name varchar2(80);
2100 l_return_stat varchar2(1);
2101 l_activity_type varchar2(80);
2102 l_approval_type varchar2(80);
2103 l_msg_data VARCHAR2(4000);
2104 l_msg_count number;
2105 l_error_msg VARCHAR2(4000);
2106 dml_str varchar2(2000);
2107 l_itemType varchar2(80);
2108 l_itemKey varchar2(80);
2109 BEGIN
2110 l_itemType := nvl(substr(document_id, 1,instr(document_id,':')-1),'AMSGAPP');
2111 l_itemKey := substr(document_id, instr(document_id,':')+1);
2112
2113 l_activity_type := wf_engine.GetItemAttrText(
2114 itemtype => l_itemtype,
2115 itemkey => l_itemkey,
2116 aname => 'AMS_ACTIVITY_TYPE' );
2117
2118 l_approval_type := wf_engine.GetItemAttrText(
2119 itemtype => l_itemtype,
2120 itemkey => l_itemkey,
2121 aname => 'AMS_APPROVAL_TYPE' );
2122
2123 Get_Api_Name('WORKFLOW', l_activity_type, 'NTF_APPROVAL_REMINDER',l_approval_type, l_pkg_name, l_proc_name,l_return_stat);
2124 if (l_return_stat = 'S') then
2125 dml_str := 'BEGIN ' || l_pkg_name||'.'||l_proc_name||'(:document_id,:display_type,:document,:document_type); END;';
2126 EXECUTE IMMEDIATE dml_str USING IN document_id,IN display_type,IN OUT document,IN OUT document_type;
2127 end if;
2128 /*
2129 EXCEPTION
2130 WHEN OTHERS THEN
2131 FND_MSG_PUB.Count_And_Get (
2132 p_encoded => FND_API.G_FALSE,
2133 p_count => l_msg_count,
2134 p_data => l_msg_data
2135 );
2136 Handle_Err
2137 (p_itemtype => itemtype ,
2138 p_itemkey => itemkey ,
2139 p_msg_count => l_msg_count, -- Number of error Messages
2140 p_msg_data => l_msg_data ,
2141 p_attr_name => 'AMS_ERROR_MSG',
2142 x_error_msg => l_error_msg
2143 ) ;
2144 wf_core.context('ams_gen_approval_pvt',
2145 'Ntf_Approval_reminder',
2146 itemtype, itemkey,to_char(actid),l_error_msg);
2147 RAISE;
2148 */
2149
2150 END Ntf_Approval_reminder;
2151
2152 --------------------------------------------------------------------------------
2153 --
2154 -- Procedure
2155 -- Ntf_Forward_FYI(itemtype in varchar2,
2156 -- itemkey in varchar2,
2157 -- p_object_type in varchar2,
2158 -- actid in number,
2159 -- funcmode in varchar2,
2160 -- resultout out varchar2 )
2161 ---------------------------------------------------------------------------------
2162 PROCEDURE Ntf_Forward_FYI(document_id in varchar2,
2163 display_type in varchar2,
2164 document in OUT NOCOPY varchar2,
2165 document_type in OUT NOCOPY varchar2 )
2166 IS
2167 l_pkg_name varchar2(80);
2168 l_proc_name varchar2(80);
2169 l_return_stat varchar2(1);
2170 l_activity_type varchar2(80);
2171 l_approval_type varchar2(80);
2172 l_msg_data VARCHAR2(4000);
2173 l_msg_count number;
2174 l_error_msg VARCHAR2(4000);
2175 dml_str varchar2(2000);
2176 l_itemType varchar2(80);
2177 l_itemKey varchar2(80);
2178 BEGIN
2179 l_itemType := nvl(substr(document_id, 1,instr(document_id,':')-1),'AMSGAPP');
2180 l_itemKey := substr(document_id, instr(document_id,':')+1);
2181
2182 l_activity_type := wf_engine.GetItemAttrText(
2183 itemtype => l_itemtype,
2184 itemkey => l_itemkey,
2185 aname => 'AMS_ACTIVITY_TYPE' );
2186
2187 l_approval_type := wf_engine.GetItemAttrText(
2188 itemtype => l_itemtype,
2189 itemkey => l_itemkey,
2190 aname => 'AMS_APPROVAL_TYPE' );
2191
2192 Get_Api_Name('WORKFLOW', l_activity_type, 'NTF_FORWARD_FYI',l_approval_type, l_pkg_name, l_proc_name,l_return_stat);
2193 if (l_return_stat = 'S') then
2194 dml_str := 'BEGIN '|| l_pkg_name||'.'||l_proc_name||'(:document_id,:display_type,:document,:document_type); END;';
2195 EXECUTE IMMEDIATE dml_str USING IN document_id,IN display_type,IN OUT document,IN OUT document_type;
2196 end if;
2197
2198 /*
2199 EXCEPTION
2200 WHEN OTHERS THEN
2201 FND_MSG_PUB.Count_And_Get (
2202 p_encoded => FND_API.G_FALSE,
2203 p_count => l_msg_count,
2204 p_data => l_msg_data
2205 );
2206 Handle_Err
2207 (p_itemtype => itemtype ,
2208 p_itemkey => itemkey ,
2209 p_msg_count => l_msg_count, -- Number of error Messages
2210 p_msg_data => l_msg_data ,
2211 p_attr_name => 'AMS_ERROR_MSG',
2212 x_error_msg => l_error_msg
2213 ) ;
2214 wf_core.context('ams_gen_approval_pvt',
2215 'Ntf_Forward_FYI',
2216 itemtype, itemkey,to_char(actid),l_error_msg);
2217 RAISE;
2218 */
2219
2220 END Ntf_Forward_FYI;
2221
2222 --------------------------------------------------------------------------------
2223 --
2224 -- Procedure
2225 -- Ntf_Approved_FYI(itemtype in varchar2,
2226 -- itemkey in varchar2,
2227 -- actid in number,
2228 -- funcmode in varchar2,
2229 -- resultout out varchar2 )
2230 ---------------------------------------------------------------------------------
2231 PROCEDURE Ntf_Approved_FYI(document_id in varchar2,
2232 display_type in varchar2,
2233 document in OUT NOCOPY varchar2,
2234 document_type in OUT NOCOPY varchar2 )
2235 IS
2236 l_pkg_name varchar2(80);
2237 l_proc_name varchar2(80);
2238 l_return_stat varchar2(1);
2239 l_activity_type varchar2(80);
2240 l_approval_type varchar2(80);
2241 l_msg_data VARCHAR2(4000);
2242 l_msg_count number;
2243 l_error_msg VARCHAR2(4000);
2244 dml_str varchar2(2000);
2245 l_itemType varchar2(80);
2246 l_itemKey varchar2(80);
2247 BEGIN
2248 l_itemType := nvl(substr(document_id, 1,instr(document_id,':')-1),'AMSGAPP');
2249 l_itemKey := substr(document_id, instr(document_id,':')+1);
2250
2251 l_activity_type := wf_engine.GetItemAttrText(
2252 itemtype => l_itemtype,
2253 itemkey => l_itemkey,
2254 aname => 'AMS_ACTIVITY_TYPE' );
2255
2256 l_approval_type := wf_engine.GetItemAttrText(
2257 itemtype => l_itemtype,
2258 itemkey => l_itemkey,
2259 aname => 'AMS_APPROVAL_TYPE' );
2260
2261 Get_Api_Name('WORKFLOW', l_activity_type, 'NTF_APPROVED_FYI',l_approval_type, l_pkg_name, l_proc_name,l_return_stat);
2262 if (l_return_stat = 'S') then
2263 dml_str := 'BEGIN ' || l_pkg_name||'.'||l_proc_name||'(:document_id,:display_type,:document,:document_type); END;';
2264 EXECUTE IMMEDIATE dml_str USING IN document_id,IN display_type,IN OUT document,IN OUT document_type;
2265 end if;
2266
2267 /*
2268 EXCEPTION
2269 WHEN OTHERS THEN
2270 FND_MSG_PUB.Count_And_Get (
2271 p_encoded => FND_API.G_FALSE,
2272 p_count => l_msg_count,
2273 p_data => l_msg_data
2274 );
2275 Handle_Err
2276 (p_itemtype => itemtype ,
2277 p_itemkey => itemkey ,
2278 p_msg_count => l_msg_count, -- Number of error Messages
2279 p_msg_data => l_msg_data ,
2280 p_attr_name => 'AMS_ERROR_MSG',
2281 x_error_msg => l_error_msg
2282 ) ;
2283 wf_core.context('ams_gen_approval_pvt',
2284 'Ntf_Approved_FYI',
2285 itemtype, itemkey,to_char(actid),l_error_msg);
2286 RAISE;
2287 */
2288 END Ntf_Approved_FYI;
2289
2290 -------------------------------------------------------------------------------
2291 --
2292 -- Procedure
2293 -- Ntf_Rejected_FYI(itemtype in varchar2,
2294 -- itemkey in varchar2,
2295 -- actid in number,
2296 -- funcmode in varchar2,
2297 -- resultout out varchar2 )
2298 ---------------------------------------------------------------------------------
2299 PROCEDURE Ntf_Rejected_FYI(document_id in varchar2,
2300 display_type in varchar2,
2301 document in OUT NOCOPY varchar2,
2302 document_type in OUT NOCOPY varchar2 )
2303 IS
2304 l_pkg_name varchar2(80);
2305 l_proc_name varchar2(80);
2306 l_return_stat varchar2(1);
2307 l_activity_type varchar2(80);
2308 l_approval_type varchar2(80);
2309 l_msg_data VARCHAR2(4000);
2310 l_msg_count number;
2311 l_error_msg VARCHAR2(4000);
2312 dml_str varchar2(2000);
2313 l_itemType varchar2(80);
2314 l_itemKey varchar2(80);
2315 BEGIN
2316 l_itemType := nvl(substr(document_id, 1,instr(document_id,':')-1),'AMSGAPP');
2317 l_itemKey := substr(document_id, instr(document_id,':')+1);
2318
2319 l_activity_type := wf_engine.GetItemAttrText(
2320 itemtype => l_itemtype,
2321 itemkey => l_itemkey,
2322 aname => 'AMS_ACTIVITY_TYPE' );
2323
2324 l_approval_type := wf_engine.GetItemAttrText(
2325 itemtype => l_itemtype,
2326 itemkey => l_itemkey,
2327 aname => 'AMS_APPROVAL_TYPE' );
2328
2329 Get_Api_Name('WORKFLOW', l_activity_type, 'NTF_REJECTED_FYI',l_approval_type, l_pkg_name, l_proc_name,l_return_stat);
2330 if (l_return_stat = 'S') then
2331 dml_str := 'BEGIN '|| l_pkg_name||'.'||l_proc_name||'(:document_id,:display_type,:document,:document_type); END;';
2332 EXECUTE IMMEDIATE dml_str USING IN document_id,IN display_type,IN OUT document,IN OUT document_type;
2333 end if;
2334
2335 /*
2336 EXCEPTION
2337 WHEN OTHERS THEN
2338 FND_MSG_PUB.Count_And_Get (
2339 p_encoded => FND_API.G_FALSE,
2340 p_count => l_msg_count,
2341 p_data => l_msg_data
2342 );
2343 Handle_Err
2344 (p_itemtype => itemtype ,
2345 p_itemkey => itemkey ,
2346 p_msg_count => l_msg_count, -- Number of error Messages
2347 p_msg_data => l_msg_data ,
2348 p_attr_name => 'AMS_ERROR_MSG',
2349 x_error_msg => l_error_msg
2350 ) ;
2351 wf_core.context('ams_gen_approval_pvt',
2352 'Ntf_Rejected_FYI',
2353 itemtype, itemkey,to_char(actid),l_error_msg);
2354 RAISE;
2355 */
2356
2357 END Ntf_Rejected_FYI;
2358 -------------------------------------------------------------------------------
2359 --
2360 -- Procedure
2361 -- Ntf_Requestor_Of_Error (itemtype in varchar2,
2362 -- itemkey in varchar2,
2363 -- actid in number,
2364 -- funcmode in varchar2,
2365 -- resultout out varchar2 )
2366 -- If uptaking functionality has an API registered for handling error, that API is
2367 -- used to generate the error message content. If not, this API generates a less
2368 -- meaningful message which will notify the requestor of an error
2369 ---------------------------------------------------------------------------------
2370 PROCEDURE Ntf_Requestor_Of_Error(document_id in varchar2,
2371 display_type in varchar2,
2372 document in OUT NOCOPY varchar2,
2373 document_type in OUT NOCOPY varchar2 )
2374 IS
2375 l_pkg_name varchar2(80);
2376 l_proc_name varchar2(80);
2377 l_return_stat varchar2(1);
2378 l_activity_type varchar2(80);
2379 l_approval_type varchar2(80);
2380 l_msg_data VARCHAR2(10000);
2381 l_msg_count number;
2382 l_error_msg VARCHAR2(4000);
2383 dml_str varchar2(2000);
2384 l_appr_meaning varchar2(240);
2385 l_appr_obj_name varchar2(240);
2386 l_itemType varchar2(80);
2387 l_itemKey varchar2(80);
2388 l_body_string varchar2(2500);
2389 l_errmsg varchar2(4000);
2390 BEGIN
2391 l_itemType := nvl(substr(document_id, 1,instr(document_id,':')-1),'AMSGAPP');
2392 l_itemKey := substr(document_id, instr(document_id,':')+1);
2393
2394 l_activity_type := wf_engine.GetItemAttrText(
2395 itemtype => l_itemtype,
2396 itemkey => l_itemkey,
2397 aname => 'AMS_ACTIVITY_TYPE' );
2398
2399 l_approval_type := wf_engine.GetItemAttrText(
2400 itemtype => l_itemtype,
2401 itemkey => l_itemkey,
2402 aname => 'AMS_APPROVAL_TYPE' );
2403
2404 l_appr_meaning := wf_engine.GetItemAttrText(
2405 itemtype => l_itemtype,
2406 itemkey => l_itemkey,
2407 aname => 'AMS_APPROVAL_OBJECT_MEANING');
2408
2409 l_appr_obj_name := wf_engine.GetItemAttrText(
2410 itemtype => l_itemtype,
2411 itemkey => l_itemkey,
2412 aname => 'AMS_APPROVAL_OBJECT_NAME');
2413
2414
2415 Get_Api_Name('WORKFLOW', l_activity_type, 'NTF_ERROR',l_approval_type, l_pkg_name, l_proc_name, l_return_stat);
2416 if (l_return_stat = 'S') then
2417
2418 dml_str := 'BEGIN ' || l_pkg_name||'.'||l_proc_name||'(:document_id,:display_type,:document,:document_type); END;';
2419 EXECUTE IMMEDIATE dml_str USING IN document_id,IN display_type,IN OUT document, IN OUT document_type;
2420
2421 elsif (l_return_stat = 'E') THEN -- no data found, generate a generic message
2422
2423 l_errmsg := wf_engine.GetItemAttrText(
2424 itemtype => l_itemtype,
2425 itemkey => l_itemkey,
2426 aname => 'AMS_ERROR_MSG');
2427
2428 fnd_message.set_name ('AMS', 'AMS_GEN_NTF_ERROR_BODY');
2429 fnd_message.set_token ('OBJ_MEANING', l_appr_meaning, FALSE);
2430 fnd_message.set_token ('OBJ_NAME', l_appr_obj_name, FALSE);
2431 fnd_message.set_token ('ERR_MSG', l_errmsg, FALSE);
2432 l_body_string := SUBSTR(fnd_message.get,1,10000);
2433
2434 document_type := 'text/plain';
2435 document := l_body_string;
2436 end if;
2437
2438 END Ntf_Requestor_Of_Error;
2439 -------------------------------------------------------------------------------
2440 --
2441 -- Procedure
2442 -- Appr_Update(itemtype in varchar2,
2443 -- itemkey in varchar2,
2444 -- actid in number,
2445 -- funcmode in varchar2,
2446 -- resultout out varchar2 )
2447 ---------------------------------------------------------------------------------
2448 PROCEDURE Update_Status(itemtype IN varchar2,
2449 itemkey IN varchar2,
2450 actid in number,
2451 funcmode in varchar2,
2452 resultout OUT NOCOPY varchar2 )
2453 IS
2454 l_pkg_name varchar2(80);
2455 l_proc_name varchar2(80);
2456 l_return_stat varchar2(1);
2457 l_msg_data VARCHAR2(4000);
2458 l_msg_count number;
2459 l_error_msg VARCHAR2(4000);
2460 dml_str varchar2(2000);
2461 l_activity_type varchar2(80);
2462 l_approval_type varchar2(80);
2463
2464
2465 BEGIN
2466 l_activity_type := wf_engine.GetItemAttrText(
2467 itemtype => itemtype,
2468 itemkey => itemkey,
2469 aname => 'AMS_ACTIVITY_TYPE' );
2470
2471 l_approval_type := wf_engine.GetItemAttrText(
2472 itemtype => itemtype,
2473 itemkey => itemkey,
2474 aname => 'AMS_APPROVAL_TYPE' );
2475
2476 Get_Api_Name('WORKFLOW', l_activity_type, 'UPDATE',l_approval_type, l_pkg_name, l_proc_name,l_return_stat);
2477 if (l_return_stat = 'S') then
2478 dml_str := 'BEGIN ' || l_pkg_name||'.'||l_proc_name||'(:itemtype,:itemkey,:actid,:funcmode,:resultout); END;';
2479 EXECUTE IMMEDIATE dml_str USING IN itemtype,IN itemkey, IN actid,IN funcmode,OUT resultout;
2480 end if;
2481 END Update_Status;
2482
2483 -------------------------------------------------------------------------------
2484 --
2485 -- Procedure
2486 -- Approved_Update_Status(itemtype in varchar2,
2487 -- itemkey in varchar2,
2488 -- actid in number,
2489 -- funcmode in varchar2,
2490 -- resultout out varchar2 )
2491 ---------------------------------------------------------------------------------
2492 PROCEDURE Approved_Update_Status(itemtype IN varchar2,
2493 itemkey IN varchar2,
2494 actid in number,
2495 funcmode in varchar2,
2496 resultout OUT NOCOPY varchar2 )
2497 IS
2498 BEGIN
2499 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
2500 itemkey => itemkey,
2501 aname => 'UPDATE_GEN_STATUS',
2502 avalue => 'APPROVED' );
2503 Update_Status(itemtype => itemtype,
2504 itemkey => itemkey,
2505 actid => actid,
2506 funcmode => funcmode,
2507 resultout => resultout);
2508
2509 END Approved_Update_Status;
2510
2511 -------------------------------------------------------------------------------
2512 --
2513 -- Procedure
2514 -- Reject_Update(itemtype in varchar2,
2515 -- itemkey in varchar2,
2516 -- actid in number,
2517 -- funcmode in varchar2,
2518 -- resultout out varchar2 )
2519 ---------------------------------------------------------------------------------
2520 PROCEDURE Reject_Update_Status(itemtype IN varchar2,
2521 itemkey IN varchar2,
2522 actid in number,
2523 funcmode in varchar2,
2524 resultout OUT NOCOPY varchar2 )
2525 IS
2526 l_appr_hist_rec AMS_Appr_Hist_Pvt.Appr_Hist_Rec_Type;
2527 l_activity_id NUMBER;
2528 l_activity_type VARCHAR2(30);
2529 l_approver_seq NUMBER;
2530 l_version NUMBER;
2531 l_approver_id NUMBER;
2532 l_approval_detail_id NUMBER;
2533 l_approval_type VARCHAR2(30);
2534 l_return_status VARCHAR2(1);
2535 l_msg_count NUMBER;
2536 l_msg_data VARCHAR2(4000);
2537 l_error_msg VARCHAR2(4000);
2538 l_note VARCHAR2(4000);
2539 --
2540 l_responder VARCHAR2(100);
2541 l_appr_display_name VARCHAR2(360);
2542 l_forward_nid NUMBER;
2543 BEGIN
2544 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
2545 itemkey => itemkey,
2546 aname => 'UPDATE_GEN_STATUS',
2547 avalue => 'REJECTED');
2548
2549
2550 -- Added by VM for 11.5.9
2551 l_activity_id := Wf_Engine.GetItemAttrNumber(
2552 itemtype => itemtype,
2553 itemkey => itemkey,
2554 aname => 'AMS_ACTIVITY_ID' );
2555
2556 -- get the activity type
2557 l_activity_type := Wf_Engine.GetItemAttrText(
2558 itemtype => itemtype,
2559 itemkey => itemkey,
2560 aname => 'AMS_ACTIVITY_TYPE' );
2561
2562 l_approver_seq := Wf_Engine.GetItemAttrNumber(
2563 itemtype => itemtype,
2564 itemkey => itemkey,
2565 aname => 'AMS_APPROVER_SEQ' );
2566
2567 l_version := Wf_Engine.GetItemAttrNumber(
2568 itemtype => itemtype,
2569 itemkey => itemkey,
2570 aname => 'AMS_OBJECT_VERSION_NUMBER' );
2571
2572 l_approver_id := Wf_Engine.GetItemAttrNumber(
2573 itemtype => itemtype,
2574 itemkey => itemkey,
2575 aname => 'AMS_APPROVER_ID' );
2576
2577 l_approval_detail_id := Wf_Engine.GetItemAttrNumber(
2578 itemtype => itemtype,
2579 itemkey => itemkey,
2580 aname => 'AMS_APPROVAL_DETAIL_ID' );
2581
2582 l_approval_type := Wf_Engine.GetItemAttrText(
2583 itemtype => itemtype,
2584 itemkey => itemkey,
2585 aname => 'AMS_APPROVAL_TYPE' );
2586
2587 l_note := Wf_Engine.GetItemAttrText(
2588 itemtype => itemtype,
2589 itemkey => itemkey,
2590 aname => 'APPROVAL_NOTE' );
2591 -- End 11.5.9
2592 -- Start of addition for forward/reassign notification
2593
2594 l_forward_nid := Wf_Engine.GetItemAttrNumber(
2595 itemtype => itemtype,
2596 itemkey => itemkey,
2597 aname => 'AMS_FORWARD_NID' );
2598 -- Commented for 3150550
2599 /*
2600 IF l_forward_nid IS NOT NULL THEN
2601
2602 l_responder := wf_notification.responder(l_forward_nid);
2603
2604 Get_New_Res_Details(p_responder => l_responder,
2605 x_resource_id => l_approver_id,
2606 x_resource_disp_name => l_appr_display_name,
2607 x_return_status => l_return_status);
2608
2609 IF l_return_status <> Fnd_Api.G_RET_STS_SUCCESS THEN
2610 RAISE Fnd_Api.G_EXC_ERROR;
2611 END IF;
2612
2613 -- Set the WF Attributes
2614 wf_engine.SetItemAttrText( itemtype => itemtype,
2615 itemkey => itemkey,
2616 aname => 'AMS_APPROVER',
2617 avalue => l_responder);
2618
2619
2620 wf_engine.SetItemAttrNumber(itemtype => itemtype,
2621 itemkey => itemkey,
2622 aname => 'AMS_APPROVER_ID',
2623 avalue => l_approver_id);
2624
2625
2626 wf_engine.SetItemAttrText( itemtype => itemtype,
2627 itemkey => itemkey,
2628 aname => 'AMS_APPROVER_DISPLAY_NAME',
2629 avalue => l_appr_display_name);
2630
2631 -- Reset the forward_nid wf attribute to null
2632 -- This is a must
2633
2634 wf_engine.SetItemAttrNumber(itemtype => itemtype,
2635 itemkey => itemkey,
2636 aname => 'AMS_FORWARD_NID',
2637 avalue => null);
2638
2639 END IF;
2640 -- End of addition for forward/re-assign notification
2641 */
2642 -- update the record from 'PENDING' to 'REJECTED'
2643 l_appr_hist_rec.object_id := l_activity_id;
2644 l_appr_hist_rec.object_type_code := l_activity_type;
2645 l_appr_hist_rec.object_version_num := l_version;
2646 l_appr_hist_rec.action_code := 'REJECTED';
2647 l_appr_hist_rec.approval_type := l_approval_type;
2648 l_appr_hist_rec.sequence_num := l_approver_seq;
2649 l_appr_hist_rec.note := l_note;
2650 l_appr_hist_rec.action_date := sysdate;
2651
2652 -- should i reset approver_id? yes
2653 l_appr_hist_rec.approver_id := l_approver_id;
2654
2655 AMS_Appr_Hist_PVT.Update_Appr_Hist(
2656 p_api_version_number => 1.0,
2657 p_init_msg_list => FND_API.G_FALSE,
2658 p_commit => FND_API.G_FALSE,
2659 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
2660 x_return_status => l_return_status,
2661 x_msg_count => l_msg_count,
2662 x_msg_data => l_msg_data,
2663 p_appr_hist_rec => l_appr_hist_rec
2664 );
2665
2666 IF l_return_status <> Fnd_Api.G_RET_STS_SUCCESS THEN
2667 RAISE Fnd_Api.G_EXC_ERROR;
2668 END IF;
2669
2670 -- Delete any 'OPEN' rows
2671 AMS_Appr_Hist_PVT.Delete_Appr_Hist(
2672 p_api_version_number => 1.0,
2673 p_init_msg_list => FND_API.G_FALSE,
2674 p_commit => FND_API.G_FALSE,
2675 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
2676 x_return_status => l_return_status,
2677 x_msg_count => l_msg_count,
2678 x_msg_data => l_msg_data,
2679 p_object_id => l_activity_id,
2680 p_object_type_code => l_activity_type,
2681 p_sequence_num => null, -- all open rows
2682 p_action_code => 'OPEN',
2683 p_object_version_num => l_version,
2684 p_approval_type => l_approval_type);
2685
2686 IF l_return_status <> Fnd_Api.G_RET_STS_SUCCESS THEN
2687 RAISE Fnd_Api.G_EXC_ERROR;
2688 END IF;
2689
2690 -- This needs to be last as it would change version number
2691 Update_Status(itemtype => itemtype,
2692 itemkey => itemkey,
2693 actid => actid,
2694 funcmode => funcmode,
2695 resultout => resultout);
2696
2697 EXCEPTION
2698 WHEN FND_API.G_EXC_ERROR THEN
2699 FND_MSG_PUB.Count_And_Get (
2700 p_encoded => FND_API.G_FALSE,
2701 p_count => l_msg_count,
2702 p_data => l_msg_data
2703 );
2704 Handle_Err
2705 (p_itemtype => itemtype ,
2706 p_itemkey => itemkey ,
2707 p_msg_count => l_msg_count, -- Number of error Messages
2708 p_msg_data => l_msg_data ,
2709 p_attr_name => 'AMS_ERROR_MSG',
2710 x_error_msg => l_error_msg
2711 );
2712 wf_core.context('ams_gen_approval_pvt',
2713 'set_approval_rules',
2714 itemtype, itemkey,to_char(actid),l_error_msg);
2715 resultout := 'COMPLETE:ERROR';
2716 --RAISE;
2717 WHEN OTHERS THEN
2718 FND_MSG_PUB.Count_And_Get (
2719 p_encoded => FND_API.G_FALSE,
2720 p_count => l_msg_count,
2721 p_data => l_msg_data
2722 );
2723 Handle_Err
2724 (p_itemtype => itemtype ,
2725 p_itemkey => itemkey ,
2726 p_msg_count => l_msg_count, -- Number of error Messages
2727 p_msg_data => l_msg_data ,
2728 p_attr_name => 'AMS_ERROR_MSG',
2729 x_error_msg => l_error_msg
2730 ) ;
2731 wf_core.context('ams_gen_approval_pvt',
2732 'set_approver_details',
2733 itemtype, itemkey,to_char(actid),l_error_msg);
2734 RAISE;
2735 --
2736 END Reject_Update_Status;
2737
2738 -----------------------------------------------------------------------------
2739 --
2740 --PROCEDURE Check_Process_Type
2741 --
2742 -------------------------------------------------------------------------------
2743 PROCEDURE Check_Process_Type( itemtype in varchar2,
2744 itemkey in varchar2,
2745 actid in number,
2746 funcmode in varchar2,
2747 resultout OUT NOCOPY varchar2 )
2748 IS
2749 l_process_type varchar2(80);
2750 l_activity_type varchar2(80);
2751 BEGIN
2752
2753 l_activity_type := wf_engine.GetItemAttrText(
2754 itemtype => itemtype,
2755 itemkey => itemkey,
2756 aname => 'AMS_ACTIVITY_TYPE' );
2757
2758 If (l_activity_type = 'RFRQ'
2759 --OR l_activity_type = 'ROOT_BUDGET'
2760 OR l_activity_type = 'FREQ') THEN
2761 resultout := 'BUDGET';
2762 else
2763 resultout := 'OTHER';
2764 end if;
2765
2766 /* commented because of seed data change aug 17 2001
2767 l_process_type := wf_engine.GetItemAttrText(
2768 itemtype => itemtype,
2769 itemkey => itemkey,
2770 aname => 'AMS_APPROVAL_TYPE' );
2771 if (l_process_type = 'BUDGET_REQUEST'
2772 OR l_process_type = 'ROOT_BUDGET'
2773 OR l_process_type = 'CHILD_BUDGET') THEN
2774 resultout := 'BUDGET';
2775 else
2776 resultout := 'OTHER';
2777 end if;
2778 */
2779 END Check_Process_Type;
2780 PROCEDURE DynTst(itemtype IN varchar2
2781 ,itemkey IN varchar2
2782 ,resultout OUT NOCOPY varchar2
2783 )
2784 IS
2785 x_result VARCHAR2(80) := 'OK';
2786 BEGIN
2787 --dbms_output.put_line( 'Inside test loop' || itemtype || itemkey);
2788 null;
2789 END;
2790
2791 PROCEDURE DynTst1(itemtype IN varchar2
2792 ,itemkey IN varchar2
2793 -- ,resultout out varchar2
2794 )
2795 IS
2796 x_result VARCHAR2(80) := 'OK';
2797 BEGIN
2798 --dbms_output.put_line( 'Inside test loop' || itemtype || itemkey);
2799 null;
2800 END;
2801 /*****************************************************************
2802 -- Start of Comments
2803 -- NAME
2804 -- Approval_Required
2805 -- PURPOSE
2806 -- This Procedure will determine if the requestor of an activity
2807 -- is the same as the approver for that activity. This is used to
2808 -- bypass approvals if requestor is the same as the approver.
2809 -- Used By Activities
2810 -- NOTES
2811 -- HISTORY
2812 -- End of Comments
2813 ****************************************************************/
2814 PROCEDURE Approval_Required(itemtype IN VARCHAR2,
2815 itemkey IN VARCHAR2,
2816 actid IN NUMBER,
2817 funcmode IN VARCHAR2,
2818 resultout OUT NOCOPY VARCHAR2)
2819 IS
2820 --
2821 l_requestor NUMBER;
2822 l_approver NUMBER;
2823
2824
2825 BEGIN
2826 Fnd_Msg_Pub.initialize();
2827 --
2828 -- RUN mode
2829 --
2830 IF (funcmode = 'RUN') THEN
2831
2832 -- Get the Requestor
2833 l_requestor := Wf_Engine.GetItemAttrNumber(itemtype => itemtype,
2834 itemkey => itemkey,
2835 aname => 'AMS_REQUESTER_ID');
2836
2837 -- Get the Approver
2838 l_approver := Wf_Engine.GetItemAttrNumber(itemtype => itemtype,
2839 itemkey => itemkey,
2840 aname => 'AMS_APPROVER_ID');
2841
2842 IF l_requestor = l_approver THEN
2843 resultout := 'COMPLETE:N';
2844 ELSE
2845 resultout := 'COMPLETE:Y';
2846 END IF;
2847
2848 RETURN;
2849
2850 END IF;
2851
2852 IF (funcmode = 'CANCEL') THEN
2853 resultout := 'COMPLETE:';
2854 RETURN;
2855 END IF;
2856
2857 --
2858 -- TIMEOUT mode
2859 --
2860 IF (funcmode = 'TIMEOUT') THEN
2861 resultout := 'COMPLETE:';
2862 RETURN;
2863 END IF;
2864 END Approval_Required;
2865 -----------------------------------------------------------------------
2866 -- Used to Determine if a Fund is a Child Budget.
2867 -----------------------------------------------------------------------
2868 FUNCTION Is_Child_Budget
2869 (p_fund_id IN NUMBER)
2870 RETURN VARCHAR2 IS
2871 CURSOR c_fund IS
2872 SELECT parent_fund_id
2873 FROM ozf_funds_all_b
2874 WHERE fund_id = p_fund_id;
2875
2876 l_parent_fund_id NUMBER;
2877 BEGIN
2878 OPEN c_fund;
2879 FETCH c_fund INTO l_parent_fund_id;
2880 IF c_fund%NOTFOUND THEN
2881 CLOSE c_fund;
2882 return 'N';
2883 END IF;
2884 CLOSE c_fund;
2885 IF l_parent_fund_id IS NOT NULL THEN
2886 RETURN 'Y';
2887 ELSE
2888 RETURN 'N';
2889 END IF;
2890 END Is_Child_Budget;
2891
2892 ----------------------------------------------------------------------
2893 -- for 11.5.9
2894 -- This is called by the Get_Approval_Rule procedure
2895 ----------------------------------------------------------------------
2896 PROCEDURE Get_Generic_Activity_Details(p_activity_id IN NUMBER,
2897 p_activity_type IN VARCHAR2,
2898 x_object_details OUT NOCOPY ObjRecTyp,
2899 x_return_status OUT NOCOPY VARCHAR2 )
2900 IS
2901 TYPE obj_csr_type IS REF CURSOR ;
2902 l_obj_details obj_csr_type;
2903 l_meaning VARCHAR2(80);
2904 l_msg_count NUMBER;
2905 l_msg_data VARCHAR2(4000);
2906 l_error_msg VARCHAR2(4000);
2907
2908 BEGIN
2909 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
2910 IF p_activity_type = 'PRIC' THEN
2911
2912 OPEN l_obj_details FOR
2913 SELECT name -- name
2914 , null -- business unit id
2915 , null -- country code
2916 , custom_setup_id -- set
2917 , null -- amount
2918 , null -- org id
2919 , 'PRIC' -- object_type
2920 , null -- priority
2921 , start_date_active -- start date
2922 , end_date_active -- end date
2923 , null -- purpose
2924 , description -- description
2925 , owner_id -- owner
2926 , currency_code -- currency
2927 , null -- priority desc
2928 FROM ams_price_lists_v
2929 WHERE list_header_id = p_activity_id;
2930
2931 ELSIF p_activity_type = 'CLAM' THEN
2932 OPEN l_obj_details FOR
2933 SELECT c.claim_number -- name
2934 , null -- bus unit id
2935 , null -- country code
2936 , c.custom_setup_id -- set
2937 -- , amount_settled -- tha
2938 , nvl(sum(l.claim_currency_amount),0)
2939 , c.org_id -- org id
2940 , to_char(c.claim_type_id) --obj type
2941 , to_char(c.reason_code_id) -- priority
2942 , c.claim_date -- start date
2943 , c.due_date -- end date
2944 , '' -- purpose
2945 , '' -- desc
2946 , c.owner_id -- owner
2947 , c.currency_code -- currency
2948 , '' -- priority desc
2949 FROM ozf_claims_all c, ozf_claim_lines_all l
2950 WHERE c.claim_id = l.claim_id(+) -- Bug 2848568
2951 AND c.claim_id = p_activity_id
2952 GROUP BY c.claim_number, c.custom_setup_id, c.org_id, c.claim_type_id,
2953 c.reason_code_id, c.claim_date, c.due_date, c.owner_id, c.currency_code;
2954
2955 ELSIF p_activity_type = 'RFRQ' THEN --or FUND
2956 -- Also used for Child Budgets
2957 OPEN l_obj_details FOR
2958 SELECT short_name -- name
2959 , business_unit_id --bus unit id Bug 3368022
2960 , null -- country code
2961 , custom_setup_id -- setup
2962 , original_budget -- amount settled
2963 , org_id -- org id
2964 , to_char(category_id) -- object type
2965 , null -- priority
2966 , start_date_active -- start date
2967 , end_date_active -- end date
2968 , '' -- purpose
2969 , '' -- desc
2970 , owner -- owner
2971 , currency_code_tc --curr code
2972 , '' --prioriy desc
2973 FROM ozf_funds_all_vl
2974 WHERE fund_id = p_activity_id;
2975 ELSIF p_activity_type = 'FREQ' THEN
2976 OPEN l_obj_details FOR
2977 SELECT fund.short_name -- name
2978 , null --bud unit id
2979 , null --country code
2980 , null --fund.custom_setup_id
2981 , act1.request_amount
2982 , fund.org_id
2983 , to_char(fund.category_id) -- object type
2984 , null -- priority
2985 , fund.start_date_active
2986 , fund.end_date_active
2987 , '' -- purpose
2988 , '' -- desc
2989 , act1.requester_id -- owner
2990 , act1.request_currency -- curr code
2991 , '' --priority desc
2992 FROM ams_act_budgets act1
2993 ,ozf_funds_all_vl fund
2994 WHERE activity_budget_id = p_activity_id
2995 AND act1.act_budget_used_by_id = fund.fund_id;
2996 -- Add Offer Adjustments
2997 ELSE
2998 -- add exception
2999 Fnd_Message.Set_Name('AMS','AMS_BAD_APPROVAL_OBJECT_TYPE');
3000 Fnd_Msg_Pub.ADD;
3001 x_return_status := Fnd_Api.G_RET_STS_ERROR;
3002 END IF ;
3003
3004 -- check here
3005 FETCH l_obj_details INTO x_object_details;
3006 IF l_obj_details%NOTFOUND THEN
3007 CLOSE l_obj_details;
3008 Fnd_Message.Set_Name('AMS','AMS_APPR_BAD_DETAILS');
3009 Fnd_Msg_Pub.ADD;
3010 x_return_status := Fnd_Api.G_RET_STS_ERROR;
3011 RETURN;
3012 END IF;
3013 CLOSE l_obj_details;
3014 EXCEPTION
3015 WHEN OTHERS THEN
3016 FND_MSG_PUB.Count_And_Get (
3017 p_encoded => FND_API.G_FALSE,
3018 p_count => l_msg_count,
3019 p_data => l_msg_data
3020 );
3021
3022 END Get_Generic_Activity_Details;
3023 --------------------------------------------------------------
3024 -- 11.5.9
3025 -- Called from ams_approval_pvt for determining approval rule
3026 -- Added to display approval rule name in ApprovalDtailMain.jsp
3027 --------------------------------------------------------------
3028 PROCEDURE Get_Approval_Rule ( p_activity_id IN NUMBER,
3029 p_activity_type IN VARCHAR2,
3030 p_approval_type IN VARCHAR2,
3031 p_act_budget_id IN NUMBER,
3032 x_approval_detail_id OUT NOCOPY NUMBER,
3033 x_return_status OUT NOCOPY VARCHAR2)
3034 IS
3035 l_obj_details ObjRecTyp;
3036 l_approver_seq NUMBER;
3037
3038 BEGIN
3039 IF p_activity_type = 'FREQ' THEN
3040 -- pass activity_budget_id
3041 Get_Generic_Activity_Details(p_activity_id => p_act_budget_id ,
3042 p_activity_type => p_activity_type,
3043 x_object_details => l_obj_details,
3044 x_return_status => x_return_status);
3045 ELSE
3046
3047 Get_Generic_Activity_Details(p_activity_id => p_activity_id ,
3048 p_activity_type => p_activity_type,
3049 x_object_details => l_obj_details,
3050 x_return_status => x_return_status);
3051
3052 END IF;
3053
3054
3055 IF p_activity_type = 'RFRQ'
3056 AND Is_Child_Budget(p_activity_id) = 'Y' THEN
3057
3058 Get_Approval_Details ( p_activity_id => p_activity_id,
3059 p_activity_type => 'FREQ',
3060 p_approval_type => p_approval_type,
3061 -- p_act_budget_id => p_act_budget_id,
3062 p_object_details => l_obj_details,
3063 x_approval_detail_id => x_approval_detail_id,
3064 x_approver_seq => l_approver_seq,
3065 x_return_status => x_return_status);
3066
3067 ELSIF p_activity_type = 'FREQ' THEN
3068 Get_Approval_Details ( p_activity_id => p_act_budget_id,
3069 p_activity_type => 'FREQ',
3070 p_approval_type => p_approval_type,
3071 -- p_act_budget_id => p_act_budget_id,
3072 p_object_details => l_obj_details,
3073 x_approval_detail_id => x_approval_detail_id,
3074 x_approver_seq => l_approver_seq,
3075 x_return_status => x_return_status);
3076 ELSE
3077 Get_Approval_Details ( p_activity_id => p_activity_id,
3078 p_activity_type => p_activity_type,
3079 p_approval_type => p_approval_type,
3080 -- p_act_budget_id => p_act_budget_id,
3081 p_object_details => l_obj_details,
3082 x_approval_detail_id => x_approval_detail_id,
3083 x_approver_seq => l_approver_seq,
3084 x_return_status => x_return_status);
3085 END IF;
3086 END Get_Approval_Rule;
3087 ---------------------------------------------------------------------
3088 -- Called in Approval Notifications
3089 -- Used primarily to capture the new Approver in case of Forward/Re-assign
3090 ---------------------------------------------------------------------
3091
3092 PROCEDURE PostNotif_Update (itemtype IN VARCHAR2,
3093 itemkey IN VARCHAR2,
3094 actid IN NUMBER,
3095 funcmode IN VARCHAR2,
3096 resultout OUT NOCOPY VARCHAR2)
3097 IS
3098 l_nid NUMBER;
3099 l_result VARCHAR2(30);
3100 l_assignee VARCHAR2(320);
3101 l_new_approver_id NUMBER;
3102 l_appr_display_name VARCHAR2(360);
3103 l_activity_type VARCHAR2(30);
3104 l_version NUMBER;
3105 l_activity_id NUMBER;
3106 l_act_budget_id NUMBER;
3107 l_approval_type VARCHAR2(30);
3108 l_current_seq NUMBER;
3109 l_return_status VARCHAR2(1);
3110 l_msg_count NUMBER;
3111 l_msg_data VARCHAR2(4000);
3112 l_error_msg VARCHAR2(4000);
3113
3114 l_appr_hist_rec AMS_Appr_Hist_Pvt.Appr_Hist_Rec_Type;
3115
3116 BEGIN
3117 l_nid := wf_engine.context_nid;
3118
3119 IF (funcmode = 'RESPOND') THEN
3120
3121 l_result := upper(wf_notification.GETATTRTEXT(l_nid, 'RESULT'));
3122
3123 IF l_result = 'APPROVE' then
3124 resultout := 'COMPLETE:APPROVE';
3125 ELSE
3126 resultout := 'COMPLETE:REJECT';
3127 END IF;
3128
3129 ELSIF (funcmode = 'TRANSFER' OR funcmode = 'FORWARD') THEN
3130
3131 -- Set the forwarded/transferred notification id so that
3132 -- we can use it later to see actual approver
3133
3134 l_assignee := WF_ENGINE.context_text;
3135
3136 -- ams_forward_nid is not really needed.
3137 wf_engine.SetItemAttrNumber(itemtype => itemtype,
3138 itemkey => itemkey,
3139 aname => 'AMS_FORWARD_NID',
3140 avalue => l_nid);
3141
3142 Get_New_Res_Details(p_responder => l_assignee,
3143 x_resource_id => l_new_approver_id,
3144 x_resource_disp_name => l_appr_display_name,
3145 x_return_status => l_return_status);
3146
3147 IF l_return_status <> Fnd_Api.G_RET_STS_SUCCESS THEN
3148 RAISE Fnd_Api.G_EXC_ERROR;
3149 END IF;
3150
3151 -- Set the WF Attributes
3152 wf_engine.SetItemAttrText( itemtype => itemtype,
3153 itemkey => itemkey,
3154 aname => 'AMS_APPROVER',
3155 avalue => l_assignee);
3156
3157 wf_engine.SetItemAttrNumber(itemtype => itemtype,
3158 itemkey => itemkey,
3159 aname => 'AMS_APPROVER_ID',
3160 avalue => l_new_approver_id);
3161
3162 wf_engine.SetItemAttrText( itemtype => itemtype,
3163 itemkey => itemkey,
3164 aname => 'AMS_APPROVER_DISPLAY_NAME',
3165 avalue => l_appr_display_name);
3166
3167 -- Update the approver details here
3168
3169 l_activity_type := Wf_Engine.GetItemAttrText(
3170 itemtype => itemtype,
3171 itemkey => itemkey,
3172 aname => 'AMS_ACTIVITY_TYPE' );
3173
3174 l_version := Wf_Engine.GetItemAttrNumber(
3175 itemtype => itemtype,
3176 itemkey => itemkey,
3177 aname => 'AMS_OBJECT_VERSION_NUMBER'
3178 );
3179
3180 l_activity_id := Wf_Engine.GetItemAttrNumber(
3181 itemtype => itemtype,
3182 itemkey => itemkey,
3183 aname => 'AMS_ACTIVITY_ID' );
3184
3185 l_current_seq := Wf_Engine.GetItemAttrText(
3186 itemtype => itemtype,
3187 itemkey => itemkey,
3188 aname => 'AMS_APPROVER_SEQ' );
3189
3190 l_approval_type := Wf_Engine.GetItemAttrText(
3191 itemtype => itemtype,
3192 itemkey => itemkey,
3193 aname => 'AMS_APPROVAL_TYPE' );
3194
3195 l_appr_hist_rec.object_id := l_activity_id;
3196 l_appr_hist_rec.object_type_code := l_activity_type;
3197 l_appr_hist_rec.object_version_num := l_version;
3198 l_appr_hist_rec.approval_type := l_approval_type;
3199 l_appr_hist_rec.approver_type := 'USER'; -- Always
3200 l_appr_hist_rec.sequence_num := l_current_seq;
3201 l_appr_hist_rec.approver_id := l_new_approver_id;
3202
3203 AMS_Appr_Hist_PVT.Update_Appr_Hist(
3204 p_api_version_number => 1.0,
3205 p_init_msg_list => FND_API.G_FALSE,
3206 p_commit => FND_API.G_FALSE,
3207 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
3208 x_return_status => l_return_status,
3209 x_msg_count => l_msg_count,
3210 x_msg_data => l_msg_data,
3211 p_appr_hist_rec => l_appr_hist_rec
3212 );
3213
3214 IF l_return_status <> Fnd_Api.G_RET_STS_SUCCESS THEN
3215 RAISE Fnd_Api.G_EXC_ERROR;
3216 END IF;
3217
3218 resultout := 'COMPLETE';
3219
3220 END IF;
3221 EXCEPTION
3222 WHEN Fnd_Api.G_EXC_ERROR THEN
3223 Fnd_Msg_Pub.Count_And_Get (
3224 p_encoded => Fnd_Api.G_FALSE,
3225 p_count => l_msg_count,
3226 p_data => l_msg_data
3227 );
3228 Handle_Err
3229 (p_itemtype => itemtype ,
3230 p_itemkey => itemkey ,
3231 p_msg_count => l_msg_count,
3232 p_msg_data => l_msg_data ,
3233 p_attr_name => 'AMS_ERROR_MSG',
3234 x_error_msg => l_error_msg
3235 );
3236 wf_core.token('MESSAGE', l_error_msg);
3237 wf_core.raise('WF_PLSQL_ERROR');
3238 WHEN OTHERS THEN
3239 wf_core.context('ams_gen_approval_pvt','PostNotif_Update',
3240 itemtype,itemkey,actid,funcmode,'Error in Post Notif Function');
3241 raise;
3242 END PostNotif_Update;
3243
3244 END ams_gen_approval_pvt;