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