[Home] [Help]
PACKAGE BODY: APPS.ASO_APR_INT
Source
1 PACKAGE BODY aso_apr_int AS
2 /* $Header: asoiaprb.pls 120.6.12000000.2 2007/01/31 19:11:18 skulkarn ship $ */
3 g_pkg_name CONSTANT VARCHAR2 (300) := 'ASO_APR_INT';
4 g_file_name CONSTANT VARCHAR2 (1000) := 'ASOIAPRB.PLS';
5 g_user_id NUMBER;
6
7 FUNCTION get_approver_name (
8 p_user_id NUMBER,
9 p_person_id NUMBER
10 )
11 RETURN VARCHAR2 IS
12 l_user_id NUMBER;
13 l_person_id NUMBER;
14 no_user_id EXCEPTION;
15
16 CURSOR get_person_name (
17 c_person_id NUMBER
18 ) IS
19 SELECT full_name
20 FROM per_all_people_f
21 WHERE person_id = c_person_id
22 AND SYSDATE BETWEEN effective_start_date
23 AND NVL (
24 effective_end_date,
25 SYSDATE
26 );
27
28 -- hyang performance fix, bug 2860045
29 CURSOR get_resource_name (
30 c_user_id NUMBER
31 ) IS
32 SELECT source_name
33 FROM jtf_rs_resource_extns
34 WHERE user_id = c_user_id
35 AND SYSDATE BETWEEN start_date_active
36 AND NVL (
37 end_date_active,
38 SYSDATE
39 );
40 BEGIN
41 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
42 aso_debug_pub.ADD (
43 'Begin Get approver name function ',
44 1,
45 'N'
46 );
47 aso_debug_pub.ADD (
48 'p_user_id ' || p_user_id || ' p_person_id ' || p_person_id,
49 1,
50 'N'
51 );
52 END IF;
53
54 IF (p_person_id = fnd_api.g_miss_num)
55 THEN
56 l_person_id := NULL;
57 ELSE
58 l_person_id := p_person_id;
59 END IF;
60
61 IF (p_user_id = fnd_api.g_miss_num)
62 THEN
63 l_user_id := NULL;
64 ELSE
65 l_user_id := p_user_id;
66 END IF;
67
68 IF ((l_person_id IS NULL)
69 AND (l_user_id IS NULL)
70 )
71 THEN
72 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
73 aso_debug_pub.ADD (
74 'The person id and user id are null',
75 1,
76 'N'
77 );
78 END IF;
79 RAISE no_user_id;
80 ELSIF l_person_id IS NOT NULL
81 THEN
82 FOR i IN get_person_name (
83 l_person_id
84 )
85 LOOP
86 IF i.full_name IS NULL
87 THEN
88 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
89 aso_debug_pub.ADD (
90 'full name is null for person_id :' || l_person_id,
91 1,
92 'N'
93 );
94 END IF;
95 RAISE no_user_id;
96 ELSE
97 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
98 aso_debug_pub.ADD (
99 'The approver name is from HR table ' || i.full_name,
100 1,
101 'N'
102 );
103 END IF;
104 RETURN i.full_name;
105 END IF;
106 END LOOP;
107
108 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
109 aso_debug_pub.ADD (
110 'person_id ' || l_person_id || ' does not exist in per_all_people_f',
111 1,
112 'N'
113 );
114 END IF;
115 RAISE no_user_id;
116 ELSE
117 FOR k IN get_resource_name (
118 l_user_id
119 )
120 LOOP
121 IF k.source_name IS NULL
122 THEN
123 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
124 aso_debug_pub.ADD (
125 'Resource name is null for user_id :' || l_user_id,
126 1,
127 'N'
128 );
129 END IF;
130 RAISE no_user_id;
131 ELSE
132 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
133 aso_debug_pub.ADD (
134 'The approver name is from JTF table ' || k.source_name,
135 1,
136 'N'
137 );
138 END IF;
139 RETURN k.source_name;
140 END IF;
141 END LOOP;
142
143 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
144 aso_debug_pub.ADD (
145 'user_id ' || l_user_id || 'does not exist in jtf resources',
146 1,
147 'N'
148 );
149 END IF;
150 RAISE no_user_id;
151 END IF;
152
153 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
154 aso_debug_pub.ADD (
155 'Should not be reached inside the get_approver_name',
156 1,
157 'N'
158 );
159 END IF;
160 EXCEPTION
161 WHEN no_user_id
162 THEN
163 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
164 aso_debug_pub.ADD (
165 'No username found from ids',
166 1,
167 'N'
168 );
169 END IF;
170 fnd_message.set_name (
171 'ASO',
172 'ASO_APR_NO_USER_ID'
173 );
174 fnd_message.set_token (
175 'USER_ID',
176 p_user_id
177 );
178 fnd_message.set_token (
179 'PERSON_ID',
180 p_person_id
181 );
182 fnd_msg_pub.ADD;
183 RAISE fnd_api.g_exc_error;
184 WHEN OTHERS
185 THEN
186 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
187 aso_debug_pub.ADD (
188 'No username found from ids',
189 1,
190 'N'
191 );
192 END IF;
193 fnd_message.set_name (
194 'ASO',
195 'ASO_APR_NO_USER_ID'
196 );
197 fnd_message.set_token (
198 'USER_ID',
199 p_user_id
200 );
201 fnd_message.set_token (
202 'PERSON_ID',
203 p_person_id
204 );
205 fnd_msg_pub.ADD;
206 RAISE fnd_api.g_exc_error;
207 END get_approver_name;
208
209 PROCEDURE get_all_approvers (
210 p_api_version_number IN NUMBER,
211 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
212 p_commit IN VARCHAR2 := fnd_api.g_false,
213 p_object_id IN NUMBER,
214 p_object_type IN VARCHAR2,
215 p_application_id IN NUMBER,
216 p_clear_transaction_flag IN VARCHAR2 := fnd_api.g_true,
217 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
218 x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
219 x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
220 x_approvers_list OUT NOCOPY /* file.sql.39 change */ aso_apr_pub.approvers_list_tbl_type,
221 x_rules_list OUT NOCOPY /* file.sql.39 change */ aso_apr_pub.rules_list_tbl_type
222 ) IS
223 l_api_name CONSTANT VARCHAR2 (30) := 'GET_ALL_APPROVERS';
224 l_api_version CONSTANT NUMBER := 1.0;
225 approvers ame_util.approverstable;
226 ruleids ame_util.idlist;
227 ruledescriptions ame_util.stringlist;
228 l_ruletypeout VARCHAR2 (240);
229 l_conditionidsout ame_util.idlist;
230 l_approvaltypenameout VARCHAR2 (240);
231 l_approvaltypedescriptionout VARCHAR2 (240);
232 m integer;
233 BEGIN
234 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
235 aso_debug_pub.ADD (
236 'Begin get_all_approvers',
237 1,
238 'N'
239 );
240 aso_debug_pub.ADD (
241 'Application ID : ' || p_application_id,
242 1,
243 'N'
244 );
245 aso_debug_pub.ADD (
246 'Object ID : ' || p_object_id,
247 1,
248 'N'
249 );
250 aso_debug_pub.ADD (
251 'Object Type : ' || p_object_type,
252 1,
253 'N'
254 );
255 aso_debug_pub.ADD (
256 'Commit Flag : ' || p_commit,
257 1,
258 'N'
259 );
260 END IF;
261 -- Standard call to establisg savepoint .
262
263 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
264 aso_debug_pub.ADD (
265 'Establishing save point GET_ALL_APPROVERS_INT',
266 1,
267 'N'
268 );
269 END IF;
270 SAVEPOINT get_all_approvers_int;
271
272 -- Standard call to check for call compatibility.
273
274 IF NOT fnd_api.compatible_api_call (
275 l_api_version,
276 p_api_version_number,
277 l_api_name,
278 g_pkg_name
279 )
280 THEN
281 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
282 aso_debug_pub.ADD (
283 'GET_ALL_APROVERS api call was not compatible pls check version ',
284 1,
285 'N'
286 );
287 END IF;
288 RAISE fnd_api.g_exc_unexpected_error;
289 END IF;
290
291 -- Initialize message list if p_init_msg_list is set to TRUE
292
293 IF fnd_api.to_boolean (
294 p_init_msg_list
295 )
296 THEN
297 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
298 aso_debug_pub.ADD (
299 'Initializing the message list ',
300 1,
301 'N'
302 );
303 END IF;
304 fnd_msg_pub.initialize;
305 END IF;
306
307 -- Initialize API return status to success
308
309 x_return_status := fnd_api.g_ret_sts_success;
310
311 -- Clear all transactions if the flag is set to true
312
313 IF fnd_api.to_boolean (
314 p_clear_transaction_flag
315 )
316 THEN
317 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
318 aso_debug_pub.ADD (
319 'clearing all previous transactions in AME ',
320 1,
321 'N'
322 );
323 aso_debug_pub.ADD (
324 'Calling AME clearAllApprovals',
325 1,
326 'N'
327 );
328 END IF;
329 ame_api.clearallapprovals (
330 applicationidin => p_application_id,
331 transactionidin => p_object_id,
332 transactiontypein => p_object_type
333 );
334 END IF;
335
336 -- Calling the OAM API to get all the approvers -----
337
338 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
339 aso_debug_pub.ADD (
340 'Calling AME get All Approvers Procedure',
341 1,
342 'N'
343 );
344 END IF;
345 ame_api.getapproversandrules1 (
346 applicationidin => p_application_id,
347 transactionidin => p_object_id,
348 transactiontypein => p_object_type,
349 approversout => approvers,
350 ruleidsout => ruleids
351 );
352 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
353 aso_debug_pub.ADD (
354 'Approvers Count is ' || approvers.COUNT,
355 1,
356 'N'
357 );
358 END IF;
359
360
361 -- Added code to check if it is a self-approval case
362 IF ((approvers.count = 1) and (approvers(1).approval_status = ame_util.approvedStatus)) THEN
363
364 -- the requester is approver in this case
365 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
366 aso_debug_pub.add('Self approval case -- requester is only approver', 1, 'N');
367 END IF;
368 NULL;
369
370 ELSE
371
372
373
374 -- Looping through the PL/SQL Table and assigning values to be passed OUT NOCOPY /* file.sql.39 change */ as
375 -- list of approvers
376
377 FOR i IN 1 .. approvers.COUNT
378 LOOP
379
380
381 /* Added new logic for checking duplicates */
382
383 /* This logic is necessary as AME has made changes in 11.5.10 due to which
384 duplicate approvers can be returned from ame_api */
385
386 IF i > 1 THEN
387
388
389 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
390 aso_debug_pub.ADD (' Checking for duplicate approvers',1,'N');
391 END IF;
392
393
394 FOR j IN 1..x_approvers_list.COUNT LOOP
395
396 IF (( approvers(i).user_id = x_approvers_list(j).approver_user_id ) OR
397 ( approvers(i).person_id = x_approvers_list(j).approver_person_id )) THEN
398
399
400 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
401 aso_debug_pub.ADD ('Found a duplicate approver ',1,'N');
402 aso_debug_pub.ADD ('Duplicate approver person_id is '|| approvers(i).person_id,1,'N');
403
404
405 END IF;
406
407
408 GOTO end_of_loop;
409
410 END IF;
411 END LOOP;
412
413 END IF;
414
415 m := x_approvers_list.count + 1;
416 x_approvers_list (
417 m
418 ).approver_user_id := approvers (
419 i
420 ).user_id;
421 x_approvers_list (
422 m
423 ).approver_person_id := approvers (
424 i
425 ).person_id;
426 x_approvers_list (
427 m
428 ).approver_sequence := m;
429 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
430 aso_debug_pub.ADD (
431 'The person_id is ' || approvers (
432 i
433 ).person_id,
434 1,
435 'N'
436 );
437 aso_debug_pub.ADD (
438 'The user_id is ' || approvers (
439 i
440 ).user_id,
441 1,
442 'N'
443 );
444 END IF;
445 -- Calling the function to get the approver name
446
447 x_approvers_list (
448 m
449 ).approver_name :=
450 get_approver_name (
451 approvers (
452 i
453 ).user_id,
454 approvers (
455 i
456 ).person_id
457 );
458
459 <<end_of_loop>>
460
461 NULL; -- if duplicate approver do nothing
462
463 END LOOP;
464
465 END IF; -- end if for self approver case
466
467
468 -- Calling the OAM API to get the rules
469
470 -- Looping through the PL/SQL Table to assign values to be passed OUT NOCOPY /* file.sql.39 change */ as list
471 -- of rules and their descriptions
472
473 FOR i IN 1 .. ruleids.COUNT
474 LOOP
475 x_rules_list (
476 i
477 ).rule_id := ruleids (
478 i
479 );
480 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
481 aso_debug_pub.ADD (
482 'Calling AME get applicable rules procedure',
483 1,
484 'N'
485 );
486 END IF;
487 ame_api.getruledetails1 (
488 ruleidin => ruleids (
489 i
490 ),
491 ruletypeout => l_ruletypeout,
492 ruledescriptionout => x_rules_list (
493 i
494 ).rule_description,
495 conditionidsout => l_conditionidsout,
496 approvaltypenameout => l_approvaltypenameout,
497 approvaltypedescriptionout => l_approvaltypedescriptionout,
498 approvaldescriptionout => x_rules_list (
499 i
500 ).approval_level
501 );
502 END LOOP;
503
504 -- commit the work
505
506 IF fnd_api.to_boolean (
507 p_commit
508 )
509 THEN
510 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
511 aso_debug_pub.ADD (
512 'Commiting the work in get_all_approvers procedure ',
513 1,
514 'N'
515 );
516 END IF;
517 COMMIT WORK;
518 END IF;
519
520 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
521 aso_debug_pub.ADD (
522 'End get all approvers procedure ',
523 1,
524 'N'
525 );
526 END IF;
527 EXCEPTION
528 WHEN fnd_api.g_exc_error
529 THEN
530 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
531 aso_debug_pub.ADD (
532 'Exception FND_API.G_EXC_ERROR in get_all_approvers ',
533 1,
534 'N'
535 );
536 END IF;
537 aso_utility_pvt.handle_exceptions (
538 p_api_name => l_api_name,
539 p_pkg_name => g_pkg_name,
540 p_exception_level => fnd_msg_pub.g_msg_lvl_error,
541 p_package_type => aso_utility_pvt.g_int,
542 p_sqlcode => SQLCODE,
543 p_sqlerrm => SQLERRM,
544 x_msg_count => x_msg_count,
545 x_msg_data => x_msg_data,
546 x_return_status => x_return_status
547 );
548 WHEN fnd_api.g_exc_unexpected_error
549 THEN
550 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
551 aso_debug_pub.ADD (
552 'Exception FND_API.G_EXC_UNEXPECTED_ERROR in get_all_approvers ',
553 1,
554 'N'
555 );
556 END IF;
557 aso_utility_pvt.handle_exceptions (
558 p_api_name => l_api_name,
559 p_pkg_name => g_pkg_name,
560 p_exception_level => fnd_msg_pub.g_msg_lvl_unexp_error,
561 p_package_type => aso_utility_pvt.g_int,
562 p_sqlcode => SQLCODE,
563 p_sqlerrm => SQLERRM,
564 x_msg_count => x_msg_count,
565 x_msg_data => x_msg_data,
566 x_return_status => x_return_status
567 );
568 WHEN OTHERS
569 THEN
570 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
571 aso_debug_pub.ADD (
572 'When Others Exception in get_all_approvers ',
573 1,
574 'N'
575 );
576 END IF;
577 aso_utility_pvt.handle_exceptions (
578 p_api_name => l_api_name,
579 p_pkg_name => g_pkg_name,
580 p_exception_level => aso_utility_pvt.g_exc_others,
581 p_package_type => aso_utility_pvt.g_int,
582 p_sqlcode => SQLCODE,
583 p_sqlerrm => SQLERRM,
584 x_msg_count => x_msg_count,
585 x_msg_data => x_msg_data,
586 x_return_status => x_return_status
587 );
588 END get_all_approvers;
589
590 PROCEDURE start_approval_process (
591 p_api_version_number IN NUMBER,
592 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
593 p_commit IN VARCHAR2 := fnd_api.g_false,
594 p_object_id IN NUMBER,
595 p_object_type IN VARCHAR2,
596 p_application_id IN NUMBER,
597 p_approver_sequence IN NUMBER := fnd_api.g_miss_num,
598 p_requester_comments IN VARCHAR2,
599 x_object_approval_id OUT NOCOPY /* file.sql.39 change */ NUMBER,
600 x_approval_instance_id OUT NOCOPY /* file.sql.39 change */ NUMBER,
601 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
602 x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
603 x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
604 ) IS
605 l_api_name CONSTANT VARCHAR2 (30) := 'START_APPROVAL_PROCESS';
606 l_api_version CONSTANT NUMBER := 1.0;
607 p_approval_instance_id NUMBER;
608 p_object_approval_id NUMBER;
609 p_approval_det_id NUMBER;
610 p_rule_id NUMBER;
611 l_return_status VARCHAR2 (10);
612 l_msg_count NUMBER;
613 l_msg_data VARCHAR2 (240);
614 x_approvers_list aso_apr_pub.approvers_list_tbl_type;
615 x_rules_list aso_apr_pub.rules_list_tbl_type;
616 l_approver_status VARCHAR2 (30);
617 l_approver_sequence NUMBER;
618 p_sender_name VARCHAR2 (240);
619 l_requester_group_id NUMBER;
620 l_sales_group_role VARCHAR2(250);
621
622 l_obsolete_status varchar2(1);
623 l_employee_id NUMBER;
624 l_dup_approval NUMBER;
625
626 CURSOR c2 (
627 c_object_id NUMBER
628 ) IS
629 SELECT NVL (
630 (MAX (
631 approval_instance_id
632 ) + 1
633 ),
634 1
635 )
636 FROM aso_apr_obj_approvals
637 WHERE object_id = c_object_id;
638
639 cursor get_employee_id(l_user_id NUMBER) IS
640 select employee_id
641 from fnd_user
642 where user_id = l_user_id;
643
644 CURSOR C_get_duplicate_approval IS
645 SELECT count(*)
646 FROM aso_apr_obj_approvals
647 WHERE object_id = p_object_id
648 AND approval_status = 'PEND';
649
650
651 BEGIN
652 g_user_id := fnd_global.user_id;
653 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
654 aso_debug_pub.ADD (
655 'Begin start_approval_process procedure ',
656 1,
657 'N'
658 );
659 aso_debug_pub.ADD (
660 'Application ID : ' || p_application_id,
661 1,
662 'N'
663 );
664 aso_debug_pub.ADD (
665 'Object ID : ' || p_object_id,
666 1,
667 'N'
668 );
669 aso_debug_pub.ADD (
670 'Object Type : ' || p_object_type,
671 1,
672 'N'
673 );
674 aso_debug_pub.ADD (
675 'Commit Flag : ' || p_commit,
676 1,
677 'N'
678 );
679 END IF;
680 -- Standard call to establisg savepoint .
681
682 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
683 aso_debug_pub.ADD (
684 'Establishing save point START_APPROVAL_PROCESS_INT',
685 1,
686 'N'
687 );
688 END IF;
689 SAVEPOINT start_approval_process_int;
690
691 -- Standard call to check for call compatibility.
692 IF NOT fnd_api.compatible_api_call (
693 l_api_version,
694 p_api_version_number,
695 l_api_name,
696 g_pkg_name
697 )
698 THEN
699 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
700 aso_debug_pub.ADD (
701 'START_APPROVAL_PROCESS_PUB api call was not compatible pls check version ',
702 1,
703 'N'
704 );
705 END IF;
706 RAISE fnd_api.g_exc_unexpected_error;
707 END IF;
708
709 -- Initialize message list if p_init_msg_list is set to TRUE
710
711 IF fnd_api.to_boolean (
712 p_init_msg_list
713 )
714 THEN
715 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
716 aso_debug_pub.ADD (
717 'Initializing the message list ',
718 1,
719 'N'
720 );
721 END IF;
722 fnd_msg_pub.initialize;
723 END IF;
724
725 -- Initialize API return status to success
726 x_return_status := fnd_api.g_ret_sts_success;
727
728
729 OPEN C_get_duplicate_approval;
730 FETCH C_get_duplicate_approval INTO l_dup_approval ;
731 CLOSE C_get_duplicate_approval;
732
733 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
734 aso_debug_pub.ADD('l_dup_approval: '|| l_dup_approval,1,'N');
735 END IF;
736
737 IF l_dup_approval = 0 THEN
738
739 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
740 aso_debug_pub.ADD (
741 'Parameter p_approver sequence is :' || p_approver_sequence,
742 1,
743 'N'
744 );
745 END IF;
746
747 -- Initializing the approver sequnce , used later in skip logic
748 IF (p_approver_sequence IS NULL)
749 OR (p_approver_sequence = fnd_api.g_miss_num)
750 THEN
751 l_approver_sequence := 0;
752 ELSE
753 l_approver_sequence := p_approver_sequence;
754 END IF;
755
756 -- Get the Requester Group id
757 begin
758
759 IF aso_debug_pub.g_debug_flag = 'Y' THEN
760 aso_debug_pub.add('Before calling aso_utility_pvt.Get_Profile_Obsolete_Status', 1, 'N');
761 END IF;
762
763 l_obsolete_status := aso_utility_pvt.Get_Profile_Obsolete_Status(p_profile_name => 'AST_DEFAULT_ROLE_AND_GROUP',
764 p_application_id => 521);
765
766 IF aso_debug_pub.g_debug_flag = 'Y' THEN
767 aso_debug_pub.add('After calling Get_Profile_Obsolete_Status: l_obsolete_status: ' || l_obsolete_status, 1, 'N');
768 END IF;
769
770 if l_obsolete_status = 'T' then
771
772 l_sales_group_role := FND_PROFILE.Value_Specific( 'ASF_DEFAULT_GROUP_ROLE', G_USER_ID, NULL, 522);
773
774 IF aso_debug_pub.g_debug_flag = 'Y' THEN
775 aso_debug_pub.add('l_sales_group_role: ' || l_sales_group_role, 1, 'N');
776 END IF;
777
778 l_requester_group_id := SUBSTR(l_sales_group_role, 1, INSTR(l_sales_group_role,'(')-1);
779
780 IF aso_debug_pub.g_debug_flag = 'Y' THEN
781 aso_debug_pub.add('l_requester_group_id: ' || l_requester_group_id, 1, 'N');
782 END IF;
783
784 if l_requester_group_id is null then
785
786 l_sales_group_role := FND_PROFILE.Value_Specific( 'AST_DEFAULT_GROUP', G_USER_ID, NULL, 521);
787
788 IF aso_debug_pub.g_debug_flag = 'Y' THEN
789 aso_debug_pub.add('l_sales_group_role: ' || l_sales_group_role, 1, 'N');
790 END IF;
791
792 l_requester_group_id := to_number(l_sales_group_role);
793
794 IF aso_debug_pub.g_debug_flag = 'Y' THEN
795 aso_debug_pub.add('l_requester_group_id: ' || l_requester_group_id, 1, 'N');
796 END IF;
797
798 end if;
799
800 else
801
802 l_sales_group_role := FND_PROFILE.Value_Specific( 'ASF_DEFAULT_GROUP_ROLE', G_USER_ID, NULL, 522);
803
804 IF aso_debug_pub.g_debug_flag = 'Y' THEN
805 aso_debug_pub.add('l_sales_group_role: ' || l_sales_group_role, 1, 'N');
806 END IF;
807
808 l_requester_group_id := SUBSTR(l_sales_group_role, 1, INSTR(l_sales_group_role,'(')-1);
809
810 IF aso_debug_pub.g_debug_flag = 'Y' THEN
811 aso_debug_pub.add('l_requester_group_id: ' || l_requester_group_id, 1, 'N');
812 END IF;
813
814 if l_requester_group_id is null then
815
816 l_sales_group_role := FND_PROFILE.Value_Specific( 'AST_DEFAULT_ROLE_AND_GROUP', G_USER_ID, NULL, 521);
817
818 IF aso_debug_pub.g_debug_flag = 'Y' THEN
819 aso_debug_pub.add('l_sales_group_role: ' || l_sales_group_role, 1, 'N');
820 END IF;
821
822 l_requester_group_id := substr(l_sales_group_role, instr(l_sales_group_role,':', -1) + 1, length(l_sales_group_role));
823
824 IF aso_debug_pub.g_debug_flag = 'Y' THEN
825 aso_debug_pub.add('l_requester_group_id: ' || l_requester_group_id, 1, 'N');
826 END IF;
827
828 end if;
829
830 end if;
831
832
833 exception
834 when others then
835 l_requester_group_id := 0;
836 end;
837
838 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
839 aso_debug_pub.ADD (
840 'Requester Group ID is ' || l_requester_group_id,
841 1,
842 'N'
843 );
844 END IF;
845 -- Generate a new value for the approval Instace ID
846 OPEN c2 (
847 p_object_id
848 );
849 FETCH c2 INTO p_approval_instance_id;
850 CLOSE c2;
851 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
852 aso_debug_pub.ADD (
853 'Approval Instance ID is ' || p_approval_instance_id,
854 1,
855 'N'
856 );
857 aso_debug_pub.ADD (
858 'Inserting a row into the header table ',
859 1,
860 'N'
861 );
862 END IF;
863 -- Inserting a Row into the Header table by calling the Table Handler
864
865 aso_apr_approvals_pkg.header_insert_row (
866 p_object_approval_id,
867 p_object_id,
868 p_object_type,
869 p_approval_instance_id,
870 'PEND',
871 p_application_id,
872 SYSDATE -- p_START_DATE
873 ,
874 NULL -- p_END_DATE
875 ,
876 SYSDATE -- p_CREATION_DATE
877 ,
878 g_user_id -- p_CREATED_BY
879 ,
880 SYSDATE -- p_LAST_UPDATE_DATE
881 ,
882 g_user_id -- P_UPDATED_BY
883 ,
884 fnd_global.conc_login_id -- p_LAST_UPDATE_LOGIN
885 ,
886 NULL,
887 NULL,
888 NULL,
889 NULL,
890 NULL,
891 NULL,
892 NULL,
893 NULL,
894 NULL,
895 NULL,
896 NULL,
897 NULL,
898 NULL,
899 NULL,
900 NULL,
901 NULL,
902 NULL,
903 NULL,
904 NULL,
905 NULL,
906 NULL -- p_CONTEXT
907 ,
908 NULL -- p_SECURITY_GROUP_ID
909 ,
910 NULL -- p_OBJECT_VERSION_NUMBER
911 ,
912 g_user_id -- p_REQUESTER_USERID
913 ,
914 p_requester_comments -- p_REQUESTER_COMMENTS
915 ,
916 l_requester_group_id -- p_REQUESTER_GROUP_ID
917 );
918 -- Calling the API to get all the approvers -----
919
920 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
921 aso_debug_pub.ADD (
922 'Calling get All Approvers Procedure',
923 1,
924 'N'
925 );
926 END IF;
927 aso_apr_int.get_all_approvers (
928 p_api_version_number,
929 fnd_api.g_false,
930 fnd_api.g_false,
931 p_object_id,
932 p_object_type,
933 p_application_id,
934 fnd_api.g_true, -- p_clear_transaction_flag
935 l_return_status,
936 l_msg_count,
937 l_msg_data,
938 x_approvers_list,
939 x_rules_list
940 );
941 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
942 aso_debug_pub.ADD (
943 'Return Status from get_all_approvers is ' || l_return_status,
944 1,
945 'N'
946 );
947 END IF;
948
949 -- Checking to find if the call to above API was successfull or not
950
951 IF l_return_status <> fnd_api.g_ret_sts_success
952 THEN
953 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
954 aso_debug_pub.ADD (
955 'Return Status from get_all_approvers is ' || x_return_status,
956 1,
957 'N'
958 );
959 END IF;
960 RAISE fnd_api.g_exc_error;
961 ELSE
962 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
963 aso_debug_pub.ADD (
964 'No of approvers is ' || x_approvers_list.COUNT,
965 1,
966 'N'
967 );
968 aso_debug_pub.ADD (
969 'Object Approval ID is ' || p_object_approval_id,
970 1,
971 'N'
972 );
973 END IF;
974
975 -- Checking to see if the approver to be skipped is the last one
976 FOR i IN 1 .. x_approvers_list.COUNT
977 LOOP
978 -- Checking if any approver is to be skipped or not
979 IF (l_approver_sequence <> 0
980 AND i < l_approver_sequence
981 )
982 THEN
983 l_approver_status := 'SKIP';
984 ELSE
985 l_approver_status := 'NOSUBMIT';
986 END IF;
987
988 -- fix for bug 4590633
989 l_employee_id := x_approvers_list(i).approver_person_id;
990
991 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
992 aso_debug_pub.ADD ('person_id got from AME is: '||to_char(l_employee_id),1,'N');
993 END IF;
994
995 IF ((x_approvers_list(i).approver_person_id is null) or (x_approvers_list(i).approver_person_id = fnd_api.g_miss_num) and
996 (x_approvers_list(i).approver_user_id is not null) and (x_approvers_list(i).approver_user_id <> fnd_api.g_miss_num)) then
997
998 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
999 aso_debug_pub.ADD ('Person_id is null from AME Hence deriving it from user_id',1,'N');
1000 END IF;
1001
1002 open get_employee_id(x_approvers_list(i).approver_user_id);
1003 fetch get_employee_id into l_employee_id;
1004 close get_employee_id;
1005
1006 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1007 aso_debug_pub.ADD ('Derived person_id is: '||to_char(l_employee_id),1,'N');
1008 END IF;
1009
1010 END IF;
1011
1012 -- end of fix for bug 4590633
1013
1014 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1015 aso_debug_pub.ADD (
1016 'Inserting rows into the detail table ',
1017 1,
1018 'N'
1019 );
1020 END IF;
1021 p_approval_det_id := NULL;
1022 aso_apr_approvals_pkg.detail_insert_row (
1023 p_approval_det_id,
1024 p_object_approval_id,
1025 l_employee_id --p_APPROVER_PERSON_ID
1026 ,
1027 x_approvers_list (
1028 i
1029 ).approver_user_id ---p_APPROVER_USER_ID
1030 ,
1031 i -- P_APPROVER_SEQUENCE
1032 ,
1033 l_approver_status -- p_APPROVER_STATUS
1034 ,
1035 NULL -- p_APPROVER_COMMENTS
1036 ,
1037 NULL -- p_DATE_SENT
1038 ,
1039 NULL -- p_DATE_RECEIVED
1040 ,
1041 SYSDATE -- p_CREATION_DATE
1042 ,
1043 SYSDATE -- p_LAST_UPDATE_DATE
1044 ,
1045 g_user_id -- P_CREATED_BY
1046 ,
1047 g_user_id -- P_UPDATED_BY
1048 ,
1049 fnd_global.conc_login_id -- p_LAST_UPDATE_LOGIN
1050 ,
1051 NULL -- p_ATTRIBUTE1
1052 ,
1053 NULL -- p_ATTRIBUTE2
1054 ,
1055 NULL -- p_ATTRIBUTE3
1056 ,
1057 NULL -- p_ATTRIBUTE4
1058 ,
1059 NULL -- p_ATTRIBUTE5
1060 ,
1061 NULL -- p_ATTRIBUTE6
1062 ,
1063 NULL -- p_ATTRIBUTE7
1064 ,
1065 NULL -- p_ATTRIBUTE8
1066 ,
1067 NULL -- p_ATTRIBUTE9
1068 ,
1069 NULL -- p_ATTRIBUTE10
1070 ,
1071 NULL -- p_ATTRIBUTE11
1072 ,
1073 NULL -- p_ATTRIBUTE12
1074 ,
1075 NULL -- p_ATTRIBUTE13
1076 ,
1077 NULL -- p_ATTRIBUTE14
1078 ,
1079 NULL -- p_ATTRIBUTE15
1080 ,
1081 NULL -- p_Attribute16
1082 ,
1083 NULL -- p_Attribute17
1084 ,
1085 NULL -- p_Attribute18
1086 ,
1087 NULL -- p_Attribute19
1088 ,
1089 NULL -- p_Attribute20
1090 ,
1091 NULL -- p_CONTEXT
1092 ,
1093 NULL -- p_SECURITY_GROUP_ID
1094 ,
1095 NULL -- p_OBJECT_VERSION_NUMBER
1096 );
1097 END LOOP;
1098
1099 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1100 aso_debug_pub.ADD (
1101 'Inserting rows into the rule table ',
1102 1,
1103 'N'
1104 );
1105 END IF;
1106
1107 FOR i IN 1 .. x_rules_list.COUNT
1108 LOOP
1109 aso_apr_approvals_pkg.rule_insert_row (
1110 p_rule_id,
1111 x_rules_list (
1112 i
1113 ).rule_id,
1114 x_rules_list (
1115 i
1116 ).rule_action_id,
1117 SYSDATE -- P_CREATION_DATE
1118 ,
1119 g_user_id -- P_CREATED_BY
1120 ,
1121 SYSDATE -- p_LAST_UPDATE_DATE
1122 ,
1123 g_user_id -- P_UPDATED_BY
1124 ,
1125 fnd_global.conc_login_id -- p_LAST_UPDATE_LOGIN
1126 ,
1127 p_object_approval_id,
1128 NULL -- p_ATTRIBUTE1
1129 ,
1130 NULL -- p_ATTRIBUTE2
1131 ,
1132 NULL -- p_ATTRIBUTE3
1133 ,
1134 NULL -- p_ATTRIBUTE4
1135 ,
1136 NULL -- p_ATTRIBUTE5
1137 ,
1138 NULL -- p_ATTRIBUTE6
1139 ,
1140 NULL -- p_ATTRIBUTE7
1141 ,
1142 NULL -- p_ATTRIBUTE8
1143 ,
1144 NULL -- p_ATTRIBUTE9
1145 ,
1146 NULL -- p_ATTRIBUTE10
1147 ,
1148 NULL -- p_ATTRIBUTE11
1149 ,
1150 NULL -- p_ATTRIBUTE12
1151 ,
1152 NULL -- p_ATTRIBUTE13
1153 ,
1154 NULL -- p_ATTRIBUTE14
1155 ,
1156 NULL -- p_ATTRIBUTE15
1157 ,
1158 NULL -- p_Attribute16
1159 ,
1160 NULL -- p_Attribute17
1161 ,
1162 NULL -- p_Attribute18
1163 ,
1164 NULL -- p_Attribute19
1165 ,
1166 NULL -- p_Attribute20
1167 ,
1168 NULL -- p_CONTEXT
1169 ,
1170 NULL -- p_SECURITY_GROUP_ID
1171 ,
1172 NULL -- p_OBJECT_VERSION_NUMBER
1173 );
1174 END LOOP;
1175 END IF;
1176
1177 /*
1178
1179 If the number of approvers is zero ( or no approvers are needed)
1180 then we need to set the status to Approved in the approval instance table
1181 This is the self approval case.
1182 */
1183
1184 IF x_approvers_list.count = 0 then
1185
1186 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1187 aso_debug_pub.ADD ('Self Approval Case: Updating to Approved',1,'N');
1188 END IF;
1189 update aso_apr_obj_approvals
1190 set approval_status = 'APPR',
1191 end_date = sysdate
1192 where object_approval_id = p_object_approval_id;
1193 END IF;
1194
1195 -- Pass back the new approval id
1196 x_object_approval_id := p_object_approval_id;
1197 x_approval_instance_id := p_approval_instance_id;
1198
1199 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1200 aso_debug_pub.ADD (
1201 'Object Approval ID :' || x_approval_instance_id,
1202 1,
1203 'N'
1204 );
1205 aso_debug_pub.ADD (
1206 'Approval Instance ID :' || x_approval_instance_id,
1207 1,
1208 'N'
1209 );
1210 END IF;
1211
1212 ELSE -- l_dup_approval is not 0
1213
1214 -- return a dummy instance id
1215 x_approval_instance_id := -1;
1216 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1217 aso_debug_pub.ADD(' Duplicate approval process FOUND , hence another process NOT Started',1,'N');
1218 END IF;
1219 END IF;
1220
1221 -- commit the work
1222
1223 IF fnd_api.to_boolean (
1224 p_commit
1225 )
1226 THEN
1227 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1228 aso_debug_pub.ADD (
1229 'Commiting the work in START_APPROVAL_PROCESS procedure ',
1230 1,
1231 'N'
1232 );
1233 END IF;
1234 COMMIT WORK;
1235 END IF;
1236
1237 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1238 aso_debug_pub.ADD (
1239 'End Start_approval_process PROCEDURE ',
1240 1,
1241 'N'
1242 );
1243 END IF;
1244 EXCEPTION
1245 WHEN fnd_api.g_exc_error
1246 THEN
1247 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1248 aso_debug_pub.ADD (
1249 'Exception FND_API.G_EXC_ERROR in start_approval_process',
1250 1,
1251 'N'
1252 );
1253 END IF;
1254 aso_utility_pvt.handle_exceptions (
1255 p_api_name => l_api_name,
1256 p_pkg_name => g_pkg_name,
1257 p_exception_level => fnd_msg_pub.g_msg_lvl_error,
1258 p_package_type => aso_utility_pvt.g_int,
1259 p_sqlcode => SQLCODE,
1260 p_sqlerrm => SQLERRM,
1261 x_msg_count => x_msg_count,
1262 x_msg_data => x_msg_data,
1263 x_return_status => x_return_status
1264 );
1265 WHEN fnd_api.g_exc_unexpected_error
1266 THEN
1267 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1268 aso_debug_pub.ADD (
1269 'Exception FND_API.G_EXC_UNEXPECTED_ERROR in start_approval_process',
1270 1,
1271 'N'
1272 );
1273 END IF;
1274 aso_utility_pvt.handle_exceptions (
1275 p_api_name => l_api_name,
1276 p_pkg_name => g_pkg_name,
1277 p_exception_level => fnd_msg_pub.g_msg_lvl_unexp_error,
1278 p_package_type => aso_utility_pvt.g_int,
1279 p_sqlcode => SQLCODE,
1280 p_sqlerrm => SQLERRM,
1281 x_msg_count => x_msg_count,
1282 x_msg_data => x_msg_data,
1283 x_return_status => x_return_status
1284 );
1285
1286 WHEN OTHERS
1287 THEN
1288 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1289 aso_debug_pub.ADD (
1290 'When Others Exception in start_approval_process',
1291 1,
1292 'N'
1293 );
1294 END IF;
1295 aso_utility_pvt.handle_exceptions (
1296 p_api_name => l_api_name,
1297 p_pkg_name => g_pkg_name,
1298 p_exception_level => aso_utility_pvt.g_exc_others,
1299 p_package_type => aso_utility_pvt.g_int,
1300 p_sqlcode => SQLCODE,
1301 p_sqlerrm => SQLERRM,
1302 x_msg_count => x_msg_count,
1303 x_msg_data => x_msg_data,
1304 x_return_status => x_return_status
1305 );
1306 END start_approval_process;
1307
1308 PROCEDURE cancel_approval_process (
1309 p_api_version_number IN NUMBER,
1310 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
1311 p_commit IN VARCHAR2 := fnd_api.g_false,
1312 p_object_id IN NUMBER,
1313 p_object_type IN VARCHAR2,
1314 p_application_id IN NUMBER,
1315 p_itemtype IN VARCHAR2,
1316 p_object_approval_id IN NUMBER,
1317 p_user_id IN NUMBER,
1318 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
1319 x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
1320 x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
1321 ) IS
1322 l_api_name CONSTANT VARCHAR2 (3000) := 'cancel_approval_process';
1323 l_api_version CONSTANT NUMBER := 1.0;
1324 l_cancellor_username VARCHAR2 (240);
1325
1326 CURSOR get_approval_id (
1327 c_object_id NUMBER,
1328 c_object_type VARCHAR2
1329 ) IS
1330 SELECT object_approval_id
1331 FROM aso_apr_obj_approvals
1332 WHERE object_id = c_object_id
1333 AND object_type = c_object_type
1334 AND approval_instance_id = (SELECT MAX (
1335 approval_instance_id
1336 )
1337 FROM aso_apr_obj_approvals
1338 WHERE object_id = c_object_id
1339 AND object_type = c_object_type);
1340 BEGIN
1341 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1342 aso_debug_pub.ADD (
1343 'Begin cancel_approval_process PROCEDURE ',
1344 1,
1345 'N'
1346 );
1347 aso_debug_pub.ADD (
1348 'Application ID : ' || p_application_id,
1349 1,
1350 'N'
1351 );
1352 aso_debug_pub.ADD (
1353 'Object ID : ' || p_object_id,
1354 1,
1355 'N'
1356 );
1357 aso_debug_pub.ADD (
1358 'Object Type : ' || p_object_type,
1359 1,
1360 'N'
1361 );
1362 aso_debug_pub.ADD (
1363 'Commit Flag : ' || p_commit,
1364 1,
1365 'N'
1366 );
1367 aso_debug_pub.ADD (
1368 'Object Approval Id : ' || p_object_approval_id,
1369 1,
1370 'N'
1371 );
1372
1373
1374
1375 END IF;
1376 -- Set a save point
1377 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1378 aso_debug_pub.ADD (
1379 'Establishing save point CANCEL_APPROVAL_PROCESS_INT',
1380 1,
1381 'N'
1382 );
1383 END IF;
1384 SAVEPOINT cancel_approval_process_int;
1385
1386 -- Standard call to check for call compatibility.
1387 IF NOT fnd_api.compatible_api_call (
1388 l_api_version,
1389 p_api_version_number,
1390 l_api_name,
1391 g_pkg_name
1392 )
1393 THEN
1394 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1395 aso_debug_pub.ADD (
1396 'cancel_approval_process api call was not compatible pls check version ',
1397 1,
1398 'N'
1399 );
1400 END IF;
1401 RAISE fnd_api.g_exc_unexpected_error;
1402 END IF;
1403
1404 -- Initialize message list if p_init_msg_list is set to TRUE
1405
1406 IF fnd_api.to_boolean (
1407 p_init_msg_list
1408 )
1409 THEN
1410 fnd_msg_pub.initialize;
1411 END IF;
1412
1413 -- Initialize API return status to success
1414 x_return_status := fnd_api.g_ret_sts_success;
1415
1416 -- Check to see if required parameters are passed
1417 IF ( ((P_Object_approval_id IS NULL) OR (P_Object_approval_id = FND_API.G_MISS_NUM)) AND
1418 ((p_object_id IS NULL) OR (p_object_id = FND_API.G_MISS_NUM)) AND
1419 ((p_object_type IS NULL) OR (p_object_type = FND_API.G_MISS_CHAR)) ) THEN
1420
1421 FND_MESSAGE.Set_Name ('ASO' , 'ASO_API_INVALID_ID' );
1422 FND_MESSAGE.Set_Token ('COLUMN' , 'P_Object_approval_id', FALSE );
1423 FND_MESSAGE.Set_Token ( 'VALUE' , TO_CHAR ( P_Object_approval_id ) , FALSE );
1424 FND_MSG_PUB.ADD;
1425 RAISE FND_API.G_EXC_ERROR;
1426 END IF;
1427
1428 IF (p_object_approval_id IS NULL)
1429 OR (p_object_approval_id = fnd_api.g_miss_num)
1430 THEN
1431 FOR i IN get_approval_id (
1432 p_object_id,
1433 p_object_type
1434 )
1435 LOOP
1436 -- calling the prccedure to cancel the workflow process
1437
1438 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1439 aso_debug_pub.ADD (
1440 'Object approval ID :' || i.object_approval_id,
1441 1,
1442 'N'
1443 );
1444 aso_debug_pub.ADD (
1445 'Calling the wokflow procedure to start the cancellation process ',
1446 1,
1447 'N'
1448 );
1449 END IF;
1450 aso_apr_wf_pvt.cancelapproval (
1451 i.object_approval_id,
1452 p_itemtype,
1453 p_user_id
1454 );
1455 END LOOP;
1456 ELSE
1457 -- calling the prccedure to cancel the workflow process
1458 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1459 aso_debug_pub.ADD (
1460 'Object approval ID :' || p_object_approval_id,
1461 1,
1462 'N'
1463 );
1464 aso_debug_pub.ADD (
1465 'Calling the wokflow procedure to start the cancellation process ',
1466 1,
1467 'N'
1468 );
1469 END IF;
1470 aso_apr_wf_pvt.cancelapproval (
1471 p_object_approval_id,
1472 p_itemtype,
1473 p_user_id
1474 );
1475 END IF;
1476
1477 -- Commit the work
1478
1479 IF fnd_api.to_boolean (
1480 p_commit
1481 )
1482 THEN
1483 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1484 aso_debug_pub.ADD (
1485 'Committing the work in cancel approval procedure ',
1486 1,
1487 'N'
1488 );
1489 END IF;
1490 COMMIT WORK;
1491 END IF;
1492
1493 fnd_msg_pub.count_and_get(p_encoded => 'F',
1494 p_count => x_msg_count,
1495 p_data => x_msg_data);
1496 for k in 1..x_msg_count loop
1497 x_msg_data := fnd_msg_pub.get(p_msg_index => k,
1498 p_encoded => 'F');
1499 end loop;
1500
1501 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1502 aso_debug_pub.ADD (
1503 'End CancelApproval procedure ',
1504 1,
1505 'N'
1506 );
1507 END IF;
1508 EXCEPTION
1509 WHEN fnd_api.g_exc_error
1510 THEN
1511 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1512 aso_debug_pub.ADD (
1513 'Exception FND_API.G_EXC_ERROR in CancelApproval',
1514 1,
1515 'N'
1516 );
1517 END IF;
1518 aso_utility_pvt.handle_exceptions (
1519 p_api_name => l_api_name,
1520 p_pkg_name => g_pkg_name,
1521 p_exception_level => fnd_msg_pub.g_msg_lvl_error,
1522 p_package_type => aso_utility_pvt.g_int,
1523 p_sqlcode => SQLCODE,
1524 p_sqlerrm => SQLERRM,
1525 x_msg_count => x_msg_count,
1526 x_msg_data => x_msg_data,
1527 x_return_status => x_return_status
1528 );
1529 WHEN fnd_api.g_exc_unexpected_error
1530 THEN
1531 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1532 aso_debug_pub.ADD (
1533 'Exception FND_API.G_EXC_UNEXPECTED_ERROR in CancelApproval',
1534 1,
1535 'N'
1536 );
1537 END IF;
1538 aso_utility_pvt.handle_exceptions (
1539 p_api_name => l_api_name,
1540 p_pkg_name => g_pkg_name,
1541 p_exception_level => fnd_msg_pub.g_msg_lvl_unexp_error,
1542 p_package_type => aso_utility_pvt.g_int,
1543 p_sqlcode => SQLCODE,
1544 p_sqlerrm => SQLERRM,
1545 x_msg_count => x_msg_count,
1546 x_msg_data => x_msg_data,
1547 x_return_status => x_return_status
1548 );
1549 WHEN OTHERS
1550 THEN
1551 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1552 aso_debug_pub.ADD (
1553 'When Others Exception in CancelApproval',
1554 1,
1555 'N'
1556 );
1557 END IF;
1558 aso_utility_pvt.handle_exceptions (
1559 p_api_name => l_api_name,
1560 p_pkg_name => g_pkg_name,
1561 p_exception_level => aso_utility_pvt.g_exc_others,
1562 p_package_type => aso_utility_pvt.g_int,
1563 p_sqlcode => SQLCODE,
1564 p_sqlerrm => SQLERRM,
1565 x_msg_count => x_msg_count,
1566 x_msg_data => x_msg_data,
1567 x_return_status => x_return_status
1568 );
1569 END cancel_approval_process;
1570
1571 PROCEDURE skip_approver (
1572 p_api_version_number IN NUMBER,
1573 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
1574 p_commit IN VARCHAR2 := fnd_api.g_false,
1575 p_object_id IN NUMBER,
1576 p_object_type IN VARCHAR2,
1577 p_approver_id IN NUMBER,
1578 p_approval_instance_id IN NUMBER,
1579 p_application_id IN NUMBER,
1580 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
1581 x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
1582 x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
1583 ) IS
1584 l_api_name CONSTANT VARCHAR2 (30) := 'skip_approver';
1585 l_api_version CONSTANT NUMBER := 1.0;
1586 BEGIN
1587 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1588 aso_debug_pub.ADD (
1589 'Start of skip_approver',
1590 1,
1591 'N'
1592 );
1593 END IF;
1594
1595 -- Standard call to check for call compatibility.
1596 IF NOT fnd_api.compatible_api_call (
1597 l_api_version,
1598 p_api_version_number,
1599 l_api_name,
1600 g_pkg_name
1601 )
1602 THEN
1603 RAISE fnd_api.g_exc_unexpected_error;
1604 END IF;
1605
1606 -- Initialize message list if p_init_msg_list is set to TRUE
1607
1608 IF fnd_api.to_boolean (
1609 p_init_msg_list
1610 )
1611 THEN
1612 fnd_msg_pub.initialize;
1613 END IF;
1614
1615 -- Initialize API return status to success
1616 x_return_status := fnd_api.g_ret_sts_success;
1617 EXCEPTION
1618 WHEN fnd_api.g_exc_error
1619 THEN
1620 aso_utility_pvt.handle_exceptions (
1621 p_api_name => l_api_name,
1622 p_pkg_name => g_pkg_name,
1623 p_exception_level => aso_utility_pvt.g_exc_others,
1624 p_package_type => aso_utility_pvt.g_int,
1625 p_sqlcode => SQLCODE,
1626 p_sqlerrm => SQLERRM,
1627 x_msg_count => x_msg_count,
1628 x_msg_data => x_msg_data,
1629 x_return_status => x_return_status
1630 );
1631 WHEN fnd_api.g_exc_unexpected_error
1632 THEN
1633 aso_utility_pvt.handle_exceptions (
1634 p_api_name => l_api_name,
1635 p_pkg_name => g_pkg_name,
1636 p_exception_level => aso_utility_pvt.g_exc_others,
1637 p_package_type => aso_utility_pvt.g_int,
1638 p_sqlcode => SQLCODE,
1639 p_sqlerrm => SQLERRM,
1640 x_msg_count => x_msg_count,
1641 x_msg_data => x_msg_data,
1642 x_return_status => x_return_status
1643 );
1644 WHEN OTHERS
1645 THEN
1646 aso_utility_pvt.handle_exceptions (
1647 p_api_name => l_api_name,
1648 p_pkg_name => g_pkg_name,
1649 p_exception_level => aso_utility_pvt.g_exc_others,
1650 p_package_type => aso_utility_pvt.g_int,
1651 p_sqlcode => SQLCODE,
1652 p_sqlerrm => SQLERRM,
1653 x_msg_count => x_msg_count,
1654 x_msg_data => x_msg_data,
1655 x_return_status => x_return_status
1656 );
1657 END skip_approver;
1658
1659
1660 PROCEDURE get_rule_details (
1661 p_api_version_number IN NUMBER,
1662 p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_false,
1663 p_commit IN VARCHAR2 DEFAULT fnd_api.g_false,
1664 p_object_approval_id IN NUMBER,
1665 x_rules_list OUT NOCOPY /* file.sql.39 change */ aso_apr_pub.rules_list_tbl_type,
1666 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
1667 x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
1668 x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2)
1669 IS
1670 l_api_name CONSTANT VARCHAR2 (30) := 'get_rule_details';
1671 l_api_version CONSTANT NUMBER := 1.0;
1672 ruledescriptions ame_util.stringlist;
1673 l_ruletypeout VARCHAR2 (240);
1674 l_conditionidsout ame_util.idlist;
1675 l_approvaltypenameout VARCHAR2 (240);
1676 l_approvaltypedescriptionout VARCHAR2 (240);
1677 j INTEGER:=1;
1678 CURSOR get_rule_ids( c_obj_app_id NUMBER)
1679 IS
1680 SELECT oam_rule_id,rule_id
1681 FROM aso_apr_rules
1682 WHERE object_approval_id = c_obj_app_id;
1683
1684
1685 BEGIN
1686 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1687 aso_debug_pub.ADD (
1688 'Start of get_rule_details',
1689 1,
1690 'N'
1691 );
1692 END IF;
1693
1694 -- Standard call to check for call compatibility.
1695 IF NOT fnd_api.compatible_api_call (
1696 l_api_version,
1697 p_api_version_number,
1698 l_api_name,
1699 g_pkg_name
1700 )
1701 THEN
1702 RAISE fnd_api.g_exc_unexpected_error;
1703 END IF;
1704
1705 -- Initialize message list if p_init_msg_list is set to TRUE
1706 IF fnd_api.to_boolean (
1707 p_init_msg_list
1708 )
1709 THEN
1710 fnd_msg_pub.initialize;
1711 END IF;
1712
1713 -- Initialize API return status to success
1714 x_return_status := fnd_api.g_ret_sts_success;
1715
1716 FOR i IN get_rule_ids(p_object_approval_id)
1717 LOOP
1718 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1719 aso_debug_pub.ADD (
1720 'Calling AME get applicable rules procedure',
1721 1,
1722 'N'
1723 );
1724 aso_debug_pub.ADD (
1725 'Object_approval_id'||p_object_approval_id,
1726 1,
1727 'N'
1728 );
1729
1730 END IF;
1731 ame_api.getruledetails1 (
1732 ruleidin => i.oam_rule_id,
1733 ruletypeout => l_ruletypeout,
1734 ruledescriptionout => x_rules_list (
1735 j
1736 ).rule_description,
1737 conditionidsout => l_conditionidsout,
1738 approvaltypenameout => l_approvaltypenameout,
1739 approvaltypedescriptionout => l_approvaltypedescriptionout,
1740 approvaldescriptionout => x_rules_list (
1741 j
1742 ).approval_level
1743 );
1744
1745 x_rules_list(j).rule_id := i.rule_id;
1746 j:= j+1;
1747
1748 END LOOP;
1749
1750 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1751 aso_debug_pub.ADD (
1752 'End of get_rule_details',
1753 1,
1754 'N'
1755 );
1756 END IF;
1757
1758
1759
1760
1761 EXCEPTION
1762 WHEN fnd_api.g_exc_error
1763 THEN
1764 aso_utility_pvt.handle_exceptions (
1765 p_api_name => l_api_name,
1766 p_pkg_name => g_pkg_name,
1767 p_exception_level => fnd_msg_pub.g_msg_lvl_error,
1768 p_package_type => aso_utility_pvt.g_int,
1769 p_sqlcode => SQLCODE,
1770 p_sqlerrm => SQLERRM,
1771 x_msg_count => x_msg_count,
1772 x_msg_data => x_msg_data,
1773 x_return_status => x_return_status
1774 );
1775 WHEN fnd_api.g_exc_unexpected_error
1776 THEN
1777 aso_utility_pvt.handle_exceptions (
1778 p_api_name => l_api_name,
1779 p_pkg_name => g_pkg_name,
1780 p_exception_level => fnd_msg_pub.g_msg_lvl_unexp_error,
1781 p_package_type => aso_utility_pvt.g_int,
1782 p_sqlcode => SQLCODE,
1783 p_sqlerrm => SQLERRM,
1784 x_msg_count => x_msg_count,
1785 x_msg_data => x_msg_data,
1786 x_return_status => x_return_status
1787 );
1788 WHEN OTHERS
1789 THEN
1790 aso_utility_pvt.handle_exceptions (
1791 p_api_name => l_api_name,
1792 p_pkg_name => g_pkg_name,
1793 p_exception_level => aso_utility_pvt.g_exc_others,
1794 p_package_type => aso_utility_pvt.g_int,
1795 p_sqlcode => SQLCODE,
1796 p_sqlerrm => SQLERRM,
1797 x_msg_count => x_msg_count,
1798 x_msg_data => x_msg_data,
1799 x_return_status => x_return_status
1800 );
1801 END get_rule_details;
1802
1803 PROCEDURE start_approval_workflow (
1804 p_api_version_number IN NUMBER,
1805 p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_false,
1806 p_commit IN VARCHAR2 DEFAULT fnd_api.g_false,
1807 P_Object_approval_id IN NUMBER,
1808 P_itemtype IN VARCHAR2,
1809 P_sender_name IN VARCHAR2,
1810 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
1811 x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
1812 x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
1813 )
1814 IS
1815
1816 l_api_name CONSTANT VARCHAR2 (30) := 'start_approval_workflow';
1817 l_api_version CONSTANT NUMBER := 1.0;
1818
1819 BEGIN
1820 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1821 aso_debug_pub.ADD (
1822 'Start of start_approval_workflow',
1823 1,
1824 'N'
1825 );
1826 aso_debug_pub.ADD (
1827 'Object Approval ID : ' || P_Object_approval_id,
1828 1,
1829 'N'
1830 );
1831 aso_debug_pub.ADD (
1832 'ItemType : ' || P_itemtype,
1833 1,
1834 'N'
1835 );
1836 aso_debug_pub.ADD (
1837 'Sender name : ' || P_sender_name,
1838 1,
1839 'N'
1840 );
1841 aso_debug_pub.ADD (
1842 'Commit Flag : ' || p_commit,
1843 1,
1844 'N'
1845 );
1846 END IF;
1847
1848 -- Standard call to check for call compatibility.
1849 IF NOT fnd_api.compatible_api_call (
1850 l_api_version,
1851 p_api_version_number,
1852 l_api_name,
1853 g_pkg_name
1854 )
1855 THEN
1856 RAISE fnd_api.g_exc_unexpected_error;
1857 END IF;
1858
1859 -- Initialize message list if p_init_msg_list is set to TRUE
1860 IF fnd_api.to_boolean (
1861 p_init_msg_list
1862 )
1863 THEN
1864 fnd_msg_pub.initialize;
1865 END IF;
1866
1867 -- Initialize API return status to success
1868 x_return_status := fnd_api.g_ret_sts_success;
1869
1870 -- Check to see if required parameters are passed
1871 IF ((P_Object_approval_id IS NULL) OR (P_Object_approval_id = FND_API.G_MISS_NUM)) THEN
1872 FND_MESSAGE.Set_Name ('ASO' , 'ASO_API_INVALID_ID' );
1873 FND_MESSAGE.Set_Token ('COLUMN' , 'P_Object_approval_id', FALSE );
1874 FND_MESSAGE.Set_Token ( 'VALUE' , TO_CHAR ( P_Object_approval_id ) , FALSE );
1875 FND_MSG_PUB.ADD;
1876 RAISE FND_API.G_EXC_ERROR;
1877 ELSIF ((P_itemtype IS NULL) OR (P_itemtype = FND_API.G_MISS_CHAR)) THEN
1878 FND_MESSAGE.Set_Name ('ASO' , 'ASO_API_INVALID_ID' );
1879 FND_MESSAGE.Set_Token ('COLUMN' , 'P_Object_approval_id', FALSE );
1880 FND_MESSAGE.Set_Token ( 'VALUE' , TO_CHAR ( P_Object_approval_id ) , FALSE );
1881 FND_MSG_PUB.ADD;
1882 RAISE FND_API.G_EXC_ERROR;
1883 END IF;
1884
1885 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1886 aso_debug_pub.ADD (
1887 'Calling procedure aso_apr_wf_pvt.start_aso_approvals',
1888 1,
1889 'N'
1890 );
1891 END IF;
1892
1893 aso_apr_wf_pvt.start_aso_approvals (
1894 P_Object_approval_id,
1895 P_itemtype,
1896 P_sender_name);
1897
1898
1899 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1900 aso_debug_pub.ADD (
1901 'After Calling procedure aso_apr_wf_pvt.start_aso_approvals',
1902 1,
1903 'N'
1904 );
1905 END IF;
1906
1907
1908 -- Commit the work
1909
1910 IF fnd_api.to_boolean (
1911 p_commit
1912 )
1913 THEN
1914 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1915 aso_debug_pub.ADD (
1916 'Committing the work in start_approval_workflow ',
1917 1,
1918 'N'
1919 );
1920 END IF;
1921 COMMIT WORK;
1922 END IF;
1923
1924 fnd_msg_pub.count_and_get(p_encoded => 'F',
1925 p_count => x_msg_count,
1926 p_data => x_msg_data);
1927 for k in 1..x_msg_count loop
1928 x_msg_data := fnd_msg_pub.get(p_msg_index => k,
1929 p_encoded => 'F');
1930 end loop;
1931
1932
1933
1934 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1935 aso_debug_pub.ADD (
1936 'End start_approval_workflow procedure ',
1937 1,
1938 'N'
1939 );
1940 END IF;
1941
1942
1943 EXCEPTION
1944 WHEN fnd_api.g_exc_error
1945 THEN
1946 aso_utility_pvt.handle_exceptions (
1947 p_api_name => l_api_name,
1948 p_pkg_name => g_pkg_name,
1949 p_exception_level => fnd_msg_pub.g_msg_lvl_error,
1950 p_package_type => aso_utility_pvt.g_int,
1951 p_sqlcode => SQLCODE,
1952 p_sqlerrm => SQLERRM,
1953 x_msg_count => x_msg_count,
1954 x_msg_data => x_msg_data,
1955 x_return_status => x_return_status
1956 );
1957 WHEN fnd_api.g_exc_unexpected_error
1958 THEN
1959 aso_utility_pvt.handle_exceptions (
1960 p_api_name => l_api_name,
1961 p_pkg_name => g_pkg_name,
1962 p_exception_level => fnd_msg_pub.g_msg_lvl_unexp_error,
1963 p_package_type => aso_utility_pvt.g_int,
1964 p_sqlcode => SQLCODE,
1965 p_sqlerrm => SQLERRM,
1966 x_msg_count => x_msg_count,
1967 x_msg_data => x_msg_data,
1968 x_return_status => x_return_status
1969 );
1970 WHEN OTHERS
1971 THEN
1972 aso_utility_pvt.handle_exceptions (
1973 p_api_name => l_api_name,
1974 p_pkg_name => g_pkg_name,
1975 p_exception_level => aso_utility_pvt.g_exc_others,
1976 p_package_type => aso_utility_pvt.g_int,
1977 p_sqlcode => SQLCODE,
1978 p_sqlerrm => SQLERRM,
1979 x_msg_count => x_msg_count,
1980 x_msg_data => x_msg_data,
1981 x_return_status => x_return_status
1982 );
1983 END start_approval_workflow;
1984
1985
1986 PROCEDURE upd_status_self_appr
1987 ( p_qte_hdr_id IN NUMBER,
1988 p_obj_ver_num IN NUMBER,
1989 p_last_update_date IN DATE,
1990 x_obj_ver_num OUT NOCOPY NUMBER,
1991 x_last_update_date OUT NOCOPY DATE,
1992 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
1993 x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
1994 x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
1995 )
1996 IS
1997
1998 l_api_name CONSTANT VARCHAR2 (240) := 'upd_status_self_appr';
1999 l_last_update_date DATE;
2000 l_object_version_number NUMBER;
2001 l_status_id NUMBER;
2002
2003 cursor c_get_qte_info ( l_qte_hdr_id number) is
2004 select last_update_date,object_version_number
2005 from aso_quote_headers_all
2006 where quote_header_id = l_qte_hdr_id;
2007
2008 CURSOR C_Get_Status IS
2009 SELECT Quote_Status_Id
2010 FROM ASO_QUOTE_STATUSES_B
2011 WHERE Status_Code = 'APPROVAL PENDING';
2012
2013 BEGIN
2014
2015 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2016 aso_debug_pub.add('upd_status_self_appr: BEGIN ', 1, 'Y');
2017 END IF;
2018
2019 -- Initialize API return status to success
2020 x_return_status := fnd_api.g_ret_sts_success;
2021
2022 SAVEPOINT upd_status_self_appr_int;
2023
2024 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2025 aso_debug_pub.add('upd_status_self_appr: p_qte_hdr_id: '|| p_qte_hdr_id , 1, 'Y');
2026 aso_debug_pub.add('upd_status_self_appr: p_obj_ver_num: || p_obj_ver_num ', 1, 'Y');
2027 aso_debug_pub.add('upd_status_self_appr: p_last_update_date: || p_last_update_date ', 1, 'Y');
2028 END IF;
2029
2030 Open c_get_qte_info(p_qte_hdr_id);
2031 Fetch c_get_qte_info into l_LAST_UPDATE_DATE,l_object_version_number;
2032 If ( c_get_qte_info%NOTFOUND) Then
2033 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2034 FND_MESSAGE.Set_Name('ASO', 'ASO_API_MISSING_UPDATE_TARGET');
2035 FND_MESSAGE.Set_Token ('INFO', 'quote', FALSE);
2036 FND_MSG_PUB.Add;
2037 END IF;
2038 raise FND_API.G_EXC_ERROR;
2039 END IF;
2040 Close c_get_qte_info;
2041
2042 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2043 aso_debug_pub.add('upd_status_self_appr: l_LAST_UPDATE_DATE: '|| l_LAST_UPDATE_DATE, 1, 'Y');
2044 aso_debug_pub.add('upd_status_self_appr: l_object_version_number: '|| l_object_version_number,1,'Y');
2045 END IF;
2046
2047 If (l_last_update_date is NULL or l_last_update_date = FND_API.G_MISS_Date ) Then
2048 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2049 FND_MESSAGE.Set_Name('ASO', 'ASO_API_MISSING_COLUMN');
2050 FND_MESSAGE.Set_Token('COLUMN', 'Last_Update_Date', FALSE);
2051 FND_MSG_PUB.ADD;
2052 END IF;
2053 raise FND_API.G_EXC_ERROR;
2054 End if;
2055
2056 -- Check Whether record has been changed by someone else
2057 If ( (l_last_update_date <> p_last_update_date ) OR (l_object_version_number <> p_obj_ver_num ) ) Then
2058 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2059 FND_MESSAGE.Set_Name('ASO', 'ASO_API_RECORD_CHANGED');
2060 FND_MESSAGE.Set_Token('INFO', 'quote', FALSE);
2061 FND_MSG_PUB.ADD;
2062 END IF;
2063 raise FND_API.G_EXC_ERROR;
2064 End if;
2065
2066 OPEN C_Get_Status;
2067 FETCH C_Get_Status into l_status_id;
2068 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2069 aso_debug_pub.add('upd_status_self_appr: l_status_id: '|| l_status_id, 1, 'Y');
2070 END IF;
2071
2072 CLOSE C_Get_Status;
2073 -- set the out variables
2074 x_last_update_date := sysdate;
2075 x_obj_ver_num := l_object_version_number + 1;
2076
2077 -- update the quote status to approval pending
2078 update aso_quote_headers_all
2079 set quote_status_id = l_status_id,
2080 object_version_number = x_obj_ver_num,
2081 last_update_date = x_last_update_date
2082 where quote_header_id = p_qte_hdr_id;
2083
2084
2085 fnd_msg_pub.count_and_get(p_encoded => 'F',
2086 p_count => x_msg_count,
2087 p_data => x_msg_data);
2088 for k in 1..x_msg_count loop
2089 x_msg_data := fnd_msg_pub.get(p_msg_index => k,
2090 p_encoded => 'F');
2091 end loop;
2092
2093 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2094 aso_debug_pub.add('upd_status_self_appr: END ', 1, 'Y');
2095 END IF;
2096
2097 EXCEPTION
2098 WHEN fnd_api.g_exc_error
2099 THEN
2100 aso_utility_pvt.handle_exceptions (
2101 p_api_name => l_api_name,
2102 p_pkg_name => g_pkg_name,
2103 p_exception_level => fnd_msg_pub.g_msg_lvl_error,
2104 p_package_type => aso_utility_pvt.g_int,
2105 p_sqlcode => SQLCODE,
2106 p_sqlerrm => SQLERRM,
2107 x_msg_count => x_msg_count,
2108 x_msg_data => x_msg_data,
2109 x_return_status => x_return_status
2110 );
2111 WHEN fnd_api.g_exc_unexpected_error
2112 THEN
2113 aso_utility_pvt.handle_exceptions (
2114 p_api_name => l_api_name,
2115 p_pkg_name => g_pkg_name,
2116 p_exception_level => fnd_msg_pub.g_msg_lvl_unexp_error,
2117 p_package_type => aso_utility_pvt.g_int,
2118 p_sqlcode => SQLCODE,
2119 p_sqlerrm => SQLERRM,
2120 x_msg_count => x_msg_count,
2121 x_msg_data => x_msg_data,
2122 x_return_status => x_return_status
2123 );
2124 WHEN OTHERS
2125 THEN
2126 aso_utility_pvt.handle_exceptions (
2127 p_api_name => l_api_name,
2128 p_pkg_name => g_pkg_name,
2129 p_exception_level => aso_utility_pvt.g_exc_others,
2130 p_package_type => aso_utility_pvt.g_int,
2131 p_sqlcode => SQLCODE,
2132 p_sqlerrm => SQLERRM,
2133 x_msg_count => x_msg_count,
2134 x_msg_data => x_msg_data,
2135 x_return_status => x_return_status
2136 );
2137 END upd_status_self_appr;
2138
2139
2140 END aso_apr_int;