[Home] [Help]
PACKAGE BODY: APPS.ASO_APR_APPROVALS_PKG
Source
1 PACKAGE BODY aso_apr_approvals_pkg AS
2 /* $Header: asotappb.pls 120.1.12020000.2 2013/03/20 09:19:01 rassharm ship $ */
3 -- Start of Comments
4 -- Package name : ASO_APR_APPROVALS_PKG
5 -- Purpose :
6 -- History :
7 -- NOTE :
8 -- End of Comments
9
10
11 g_pkg_name CONSTANT VARCHAR2 (3000) := 'ASO_APR_APPROVALS_PKG';
12 g_file_name CONSTANT VARCHAR2 (1000) := 'asotappb.pls';
13
14 PROCEDURE header_insert_row (
15 px_object_approval_id IN OUT NOCOPY /* file.sql.39 change */ NUMBER,
16 p_object_id NUMBER,
17 p_object_type VARCHAR2,
18 p_approval_instance_id NUMBER,
19 p_approval_status VARCHAR2,
20 p_application_id NUMBER,
21 p_start_date DATE,
22 p_end_date DATE,
23 p_creation_date DATE,
24 p_created_by NUMBER,
25 p_last_update_date DATE,
26 p_last_updated_by NUMBER,
27 p_last_update_login NUMBER,
28 p_attribute1 VARCHAR2,
29 p_attribute2 VARCHAR2,
30 p_attribute3 VARCHAR2,
31 p_attribute4 VARCHAR2,
32 p_attribute5 VARCHAR2,
33 p_attribute6 VARCHAR2,
34 p_attribute7 VARCHAR2,
35 p_attribute8 VARCHAR2,
36 p_attribute9 VARCHAR2,
37 p_attribute10 VARCHAR2,
38 p_attribute11 VARCHAR2,
39 p_attribute12 VARCHAR2,
40 p_attribute13 VARCHAR2,
41 p_attribute14 VARCHAR2,
42 p_attribute15 VARCHAR2,
43 p_attribute16 VARCHAR2,
44 p_attribute17 VARCHAR2,
45 p_attribute18 VARCHAR2,
46 p_attribute19 VARCHAR2,
47 p_attribute20 VARCHAR2,
48 p_context VARCHAR2,
49 p_security_group_id NUMBER,
50 p_object_version_number NUMBER,
51 p_requester_userid NUMBER,
52 p_requester_comments VARCHAR2,
53 p_requester_group_id NUMBER
54 ) IS
55 CURSOR c2 IS
56 SELECT aso_apr_obj_header_s.NEXTVAL
57 FROM sys.DUAL;
58 BEGIN
59 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
60 aso_debug_pub.ADD (
61 'Begin Header_Insert_Row procedure ',
62 1,
63 'N'
64 );
65 END IF;
66 OPEN c2;
67 FETCH c2 INTO px_object_approval_id;
68 CLOSE c2;
69
70 INSERT INTO aso_apr_obj_approvals
71 (object_approval_id,
72 object_id,
73 object_type,
74 approval_instance_id,
75 approval_status,
76 application_id,
77 start_date,
78 end_date,
79 creation_date,
80 created_by,
81 last_update_date,
82 last_updated_by,
83 last_update_login,
84 attribute1,
85 attribute2,
86 attribute3,
87 attribute4,
88 attribute5,
89 attribute6,
90 attribute7,
91 attribute8,
92 attribute9,
93 attribute10,
94 attribute11,
95 attribute12,
96 attribute13,
97 attribute14,
98 attribute15,
99 attribute16,
100 attribute17,
101 attribute18,
102 attribute19,
103 attribute20,
104 CONTEXT,
105 security_group_id,
106 object_version_number,
107 requester_userid,
108 requester_comments,
109 requester_group_id)
110 VALUES (px_object_approval_id,
111 DECODE (
112 p_object_id,
113 fnd_api.g_miss_num,
114 NULL,
115 p_object_id
116 ),
117 DECODE (
118 p_object_type,
119 fnd_api.g_miss_char,
120 NULL,
121 p_object_type
122 ),
123 DECODE (
124 p_approval_instance_id,
125 fnd_api.g_miss_num,
126 NULL,
127 p_approval_instance_id
128 ),
129 DECODE (
130 p_approval_status,
131 fnd_api.g_miss_char,
132 NULL,
133 p_approval_status
134 ),
135 DECODE (
136 p_application_id,
137 fnd_api.g_miss_num,
138 NULL,
139 p_application_id
140 ),
141 aso_utility_pvt.DECODE (
142 p_start_date,
143 fnd_api.g_miss_date,
144 NULL,
145 p_start_date
146 ),
147 aso_utility_pvt.DECODE (
148 p_end_date,
149 fnd_api.g_miss_date,
150 NULL,
151 p_end_date
152 ),
153 aso_utility_pvt.DECODE (
154 p_creation_date,
155 fnd_api.g_miss_date,
156 NULL,
157 p_creation_date
158 ),
159 DECODE (
160 p_created_by,
161 fnd_api.g_miss_num,
162 NULL,
163 p_created_by
164 ),
165 aso_utility_pvt.DECODE (
166 p_last_update_date,
167 fnd_api.g_miss_date,
168 NULL,
169 p_last_update_date
170 ),
171 DECODE (
172 p_last_updated_by,
173 fnd_api.g_miss_num,
174 NULL,
175 p_last_updated_by
176 ),
177 DECODE (
178 p_last_update_login,
179 fnd_api.g_miss_num,
180 NULL,
181 p_last_update_login
182 ),
183 DECODE (
184 p_attribute1,
185 fnd_api.g_miss_char,
186 NULL,
187 p_attribute1
188 ),
189 DECODE (
190 p_attribute2,
191 fnd_api.g_miss_char,
192 NULL,
193 p_attribute2
194 ),
195 DECODE (
196 p_attribute3,
197 fnd_api.g_miss_char,
198 NULL,
199 p_attribute3
200 ),
201 DECODE (
202 p_attribute4,
203 fnd_api.g_miss_char,
204 NULL,
205 p_attribute4
206 ),
207 DECODE (
208 p_attribute5,
209 fnd_api.g_miss_char,
210 NULL,
211 p_attribute5
212 ),
213 DECODE (
214 p_attribute6,
215 fnd_api.g_miss_char,
216 NULL,
217 p_attribute6
218 ),
219 DECODE (
220 p_attribute7,
221 fnd_api.g_miss_char,
222 NULL,
223 p_attribute7
224 ),
225 DECODE (
226 p_attribute8,
227 fnd_api.g_miss_char,
228 NULL,
229 p_attribute8
230 ),
231 DECODE (
232 p_attribute9,
233 fnd_api.g_miss_char,
234 NULL,
235 p_attribute9
236 ),
237 DECODE (
238 p_attribute10,
239 fnd_api.g_miss_char,
240 NULL,
241 p_attribute10
242 ),
243 DECODE (
244 p_attribute11,
245 fnd_api.g_miss_char,
246 NULL,
247 p_attribute11
248 ),
249 DECODE (
250 p_attribute12,
251 fnd_api.g_miss_char,
252 NULL,
253 p_attribute12
254 ),
255 DECODE (
256 p_attribute13,
257 fnd_api.g_miss_char,
258 NULL,
259 p_attribute13
260 ),
261 DECODE (
262 p_attribute14,
263 fnd_api.g_miss_char,
264 NULL,
265 p_attribute14
266 ),
267 DECODE (
268 p_attribute15,
269 fnd_api.g_miss_char,
270 NULL,
271 p_attribute15
272 ),
273 DECODE (
274 p_attribute16,
275 fnd_api.g_miss_char,
276 NULL,
277 p_attribute16
278 ),
279 DECODE (
280 p_attribute17,
281 fnd_api.g_miss_char,
282 NULL,
283 p_attribute17
284 ),
285 DECODE (
286 p_attribute18,
287 fnd_api.g_miss_char,
288 NULL,
289 p_attribute18
290 ),
291 DECODE (
292 p_attribute19,
293 fnd_api.g_miss_char,
294 NULL,
295 p_attribute19
296 ),
297 DECODE (
298 p_attribute20,
299 fnd_api.g_miss_char,
300 NULL,
301 p_attribute20
302 ),
303 DECODE (
304 p_context,
305 fnd_api.g_miss_char,
306 NULL,
307 p_context
308 ),
309 DECODE (
310 p_security_group_id,
311 fnd_api.g_miss_num,
312 NULL,
313 p_security_group_id
314 ),
315 DECODE (
316 p_object_version_number,
317 fnd_api.g_miss_num,
318 NULL,
319 p_object_version_number
320 ),
321 DECODE (
322 p_requester_userid,
323 fnd_api.g_miss_num,
324 NULL,
325 p_requester_userid
326 ),
327 DECODE (
328 p_requester_comments,
329 fnd_api.g_miss_char,
330 NULL,
331 p_requester_comments
332 ),
333 DECODE (
334 p_requester_group_id,
335 fnd_api.g_miss_num,
336 NULL,
337 p_requester_group_id
338 ));
339
340 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
341 aso_debug_pub.ADD (
342 'End Header_Insert_Row procedure ',
343 1,
344 'N'
345 );
346 END IF;
347 EXCEPTION
348 WHEN OTHERS
349 THEN
350 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
351 aso_debug_pub.ADD (
352 'Exception in Header_Insert_Row procedure ',
353 1,
354 'N'
355 );
356 aso_debug_pub.ADD (
357 'errmsg is ' || SUBSTR (
358 SQLERRM,
359 1,
360 250
361 ),
362 1,
363 'N'
364 );
365 END IF;
366 END header_insert_row;
367
368 PROCEDURE header_update_row (
369 p_object_approval_id NUMBER,
370 p_object_id NUMBER,
371 p_object_type VARCHAR2,
372 p_approval_instance_id NUMBER,
373 p_approval_status VARCHAR2,
374 p_application_id NUMBER,
375 p_start_date DATE,
376 p_end_date DATE,
377 p_creation_date DATE,
378 p_created_by NUMBER,
379 p_last_update_date DATE,
380 p_last_updated_by NUMBER,
381 p_last_update_login NUMBER,
382 p_attribute1 VARCHAR2,
383 p_attribute2 VARCHAR2,
384 p_attribute3 VARCHAR2,
385 p_attribute4 VARCHAR2,
386 p_attribute5 VARCHAR2,
387 p_attribute6 VARCHAR2,
388 p_attribute7 VARCHAR2,
389 p_attribute8 VARCHAR2,
390 p_attribute9 VARCHAR2,
391 p_attribute10 VARCHAR2,
392 p_attribute11 VARCHAR2,
393 p_attribute12 VARCHAR2,
394 p_attribute13 VARCHAR2,
395 p_attribute14 VARCHAR2,
396 p_attribute15 VARCHAR2,
397 p_attribute16 VARCHAR2,
398 p_attribute17 VARCHAR2,
399 p_attribute18 VARCHAR2,
400 p_attribute19 VARCHAR2,
401 p_attribute20 VARCHAR2,
402 p_context VARCHAR2,
403 p_security_group_id NUMBER,
404 p_object_version_number NUMBER,
405 p_requester_userid NUMBER,
406 p_requester_comments VARCHAR2,
407 p_requester_group_id NUMBER
408 ) IS
409 BEGIN
410 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
411 aso_debug_pub.ADD (
412 'Begin Header_Update_Row procedure ',
413 1,
414 'N'
415 );
416 END IF;
417
418 UPDATE aso_apr_obj_approvals
419 SET object_id = DECODE (
420 p_object_id,
421 fnd_api.g_miss_num,
422 object_id,
423 p_object_id
424 ),
425 object_type = DECODE (
426 p_object_type,
427 fnd_api.g_miss_char,
428 object_type,
429 p_object_type
430 ),
431 approval_instance_id = DECODE (
432 p_approval_instance_id,
433 fnd_api.g_miss_num,
434 approval_instance_id,
435 p_approval_instance_id
436 ),
437 approval_status = DECODE (
438 p_approval_status,
439 fnd_api.g_miss_char,
440 approval_status,
441 p_approval_status
442 ),
443 application_id = DECODE (
444 p_application_id,
445 fnd_api.g_miss_num,
446 application_id,
447 p_application_id
448 ),
449 start_date = aso_utility_pvt.DECODE (
450 p_start_date,
451 fnd_api.g_miss_date,
452 start_date,
453 p_start_date
454 ),
455 end_date = aso_utility_pvt.DECODE (
456 p_end_date,
457 fnd_api.g_miss_date,
458 end_date,
459 p_end_date
460 ),
461 last_update_date = aso_utility_pvt.DECODE (
462 p_last_update_date,
463 fnd_api.g_miss_date,
464 last_update_date,
468 p_last_updated_by,
465 p_last_update_date
466 ),
467 last_updated_by = DECODE (
469 fnd_api.g_miss_num,
470 last_updated_by,
471 p_last_updated_by
472 ),
473 last_update_login = DECODE (
474 p_last_update_login,
475 fnd_api.g_miss_num,
476 last_update_login,
477 p_last_update_login
478 ),
479 attribute1 = DECODE (
480 p_attribute1,
481 fnd_api.g_miss_char,
482 attribute1,
483 p_attribute1
484 ),
485 attribute2 = DECODE (
486 p_attribute2,
487 fnd_api.g_miss_char,
488 attribute2,
489 p_attribute2
490 ),
491 attribute3 = DECODE (
492 p_attribute3,
493 fnd_api.g_miss_char,
494 attribute3,
495 p_attribute3
496 ),
497 attribute4 = DECODE (
498 p_attribute4,
499 fnd_api.g_miss_char,
500 attribute4,
501 p_attribute4
502 ),
503 attribute5 = DECODE (
504 p_attribute5,
505 fnd_api.g_miss_char,
506 attribute5,
507 p_attribute5
508 ),
509 attribute6 = DECODE (
510 p_attribute6,
511 fnd_api.g_miss_char,
512 attribute6,
513 p_attribute6
514 ),
515 attribute7 = DECODE (
516 p_attribute7,
517 fnd_api.g_miss_char,
518 attribute7,
519 p_attribute7
520 ),
521 attribute8 = DECODE (
522 p_attribute8,
523 fnd_api.g_miss_char,
524 attribute8,
525 p_attribute8
526 ),
527 attribute9 = DECODE (
528 p_attribute9,
529 fnd_api.g_miss_char,
530 attribute9,
531 p_attribute9
532 ),
533 attribute10 = DECODE (
534 p_attribute10,
535 fnd_api.g_miss_char,
536 attribute10,
537 p_attribute10
538 ),
539 attribute11 = DECODE (
540 p_attribute11,
541 fnd_api.g_miss_char,
542 attribute11,
543 p_attribute11
544 ),
545 attribute12 = DECODE (
546 p_attribute12,
547 fnd_api.g_miss_char,
548 attribute12,
549 p_attribute12
550 ),
551 attribute13 = DECODE (
552 p_attribute13,
553 fnd_api.g_miss_char,
554 attribute13,
555 p_attribute13
556 ),
557 attribute14 = DECODE (
558 p_attribute14,
559 fnd_api.g_miss_char,
560 attribute14,
561 p_attribute14
562 ),
563 attribute15 = DECODE (
564 p_attribute15,
565 fnd_api.g_miss_char,
566 attribute15,
567 p_attribute15
568 ),
569 attribute16 = DECODE (
570 p_attribute16,
571 fnd_api.g_miss_char,
572 attribute16,
573 p_attribute16
574 ),
575 attribute17 = DECODE (
576 p_attribute17,
577 fnd_api.g_miss_char,
578 attribute17,
579 p_attribute17
580 ),
581 attribute18 = DECODE (
582 p_attribute18,
583 fnd_api.g_miss_char,
584 attribute18,
585 p_attribute18
586 ),
587 attribute19 = DECODE (
588 p_attribute19,
589 fnd_api.g_miss_char,
590 attribute19,
591 p_attribute19
592 ),
593 attribute20 = DECODE (
594 p_attribute20,
595 fnd_api.g_miss_char,
596 attribute20,
597 p_attribute20
598 ),
599 CONTEXT = DECODE (
600 p_context,
601 fnd_api.g_miss_char,
602 CONTEXT,
603 p_context
604 ),
605 security_group_id = DECODE (
606 p_security_group_id,
607 fnd_api.g_miss_num,
611 object_version_number = DECODE (
608 security_group_id,
609 p_security_group_id
610 ),
612 p_object_version_number,
613 fnd_api.g_miss_num,
614 object_version_number,
615 p_object_version_number
616 ),
617 requester_userid = DECODE (
618 p_requester_userid,
619 fnd_api.g_miss_num,
620 requester_userid,
621 p_requester_userid
622 ),
623 requester_comments = DECODE (
624 p_requester_comments,
625 fnd_api.g_miss_char,
626 requester_comments,
627 p_requester_comments
628 ),
629 requester_group_id = DECODE (
630 p_requester_group_id,
631 fnd_api.g_miss_num,
632 requester_group_id,
633 p_requester_group_id
634 )
635 WHERE object_approval_id = p_object_approval_id;
636
637 IF (SQL%NOTFOUND)
638 THEN
639 RAISE NO_DATA_FOUND;
640 END IF;
641
642 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
643 aso_debug_pub.ADD (
644 'End Header_Update_Row procedure ',
645 1,
646 'N'
647 );
648 END IF;
649 END header_update_row;
650
651 PROCEDURE header_delete_row (
652 p_object_approval_id NUMBER
653 ) IS
654 BEGIN
655 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
656 aso_debug_pub.ADD (
657 'Begin Header_Delete_Row procedure ',
658 1,
659 'N'
660 );
661 END IF;
662
663 DELETE FROM aso_apr_obj_approvals
664 WHERE object_approval_id = p_object_approval_id;
665
666 IF (SQL%NOTFOUND)
667 THEN
668 RAISE NO_DATA_FOUND;
669 END IF;
670
671 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
672 aso_debug_pub.ADD (
673 'End Header_Delete_Row procedure ',
674 1,
675 'N'
676 );
677 END IF;
678 END header_delete_row;
679
680 PROCEDURE header_lock_row (
681 p_object_approval_id NUMBER,
682 p_object_id NUMBER,
683 p_object_type VARCHAR2,
684 p_approval_instance_id NUMBER,
685 p_approval_status VARCHAR2,
686 p_application_id NUMBER,
687 p_start_date DATE,
688 p_end_date DATE,
689 p_creation_date DATE,
690 p_created_by NUMBER,
691 p_last_update_date DATE,
692 p_last_updated_by NUMBER,
693 p_last_update_login NUMBER,
694 p_attribute1 VARCHAR2,
695 p_attribute2 VARCHAR2,
696 p_attribute3 VARCHAR2,
697 p_attribute4 VARCHAR2,
698 p_attribute5 VARCHAR2,
699 p_attribute6 VARCHAR2,
700 p_attribute7 VARCHAR2,
701 p_attribute8 VARCHAR2,
702 p_attribute9 VARCHAR2,
703 p_attribute10 VARCHAR2,
704 p_attribute11 VARCHAR2,
705 p_attribute12 VARCHAR2,
706 p_attribute13 VARCHAR2,
707 p_attribute14 VARCHAR2,
708 p_attribute15 VARCHAR2,
709 p_context VARCHAR2,
710 p_security_group_id NUMBER,
711 p_object_version_number NUMBER,
712 p_requester_userid NUMBER,
713 p_requester_comments VARCHAR2,
714 p_requester_group_id NUMBER
715 ) IS
716 CURSOR c IS
717 SELECT object_id, object_type, approval_instance_id, approval_status,
718 application_id, start_date, end_date, creation_date, created_by,
719 last_update_date, last_updated_by, last_update_login, attribute1,
720 attribute2, attribute3, attribute4, attribute5, attribute6, attribute7,
721 attribute8, attribute9, attribute10, attribute11, attribute12,
722 attribute13, attribute14, attribute15, CONTEXT, security_group_id,
723 object_version_number, requester_userid, requester_comments,
724 requester_group_id
725 FROM aso_apr_obj_approvals
726 WHERE object_approval_id = p_object_approval_id
727 FOR UPDATE OF object_approval_id NOWAIT;
728
729 recinfo c%ROWTYPE;
730 BEGIN
731 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
732 aso_debug_pub.ADD (
733 'Begin Header_Lock_Row procedure ',
734 1,
735 'N'
736 );
737 END IF;
738 OPEN c;
739 FETCH c INTO recinfo;
740
741 IF (c%NOTFOUND)
742 THEN
743 CLOSE c;
744 fnd_message.set_name (
745 'FND',
746 'FORM_RECORD_DELETED'
747 );
748 app_exception.raise_exception;
749 END IF;
750
751 CLOSE c;
752
756 )
753 IF (((recinfo.last_update_date = p_last_update_date)
754 OR ((recinfo.last_update_date IS NULL)
755 AND (p_last_update_date IS NULL)
757 )
758 )
759 THEN
760 RETURN;
761 ELSE
762 fnd_message.set_name (
763 'FND',
764 'FORM_RECORD_CHANGED'
765 );
766 app_exception.raise_exception;
767 END IF;
768
769 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
770 aso_debug_pub.ADD (
771 'End Header_Lock_Row procedure ',
772 1,
773 'N'
774 );
775 END IF;
776 END header_lock_row;
777
778 PROCEDURE detail_insert_row (
779 px_approval_det_id IN OUT NOCOPY /* file.sql.39 change */ NUMBER,
780 p_object_approval_id NUMBER,
781 p_approver_person_id NUMBER,
782 p_approver_user_id NUMBER,
783 p_approver_sequence NUMBER,
784 p_approver_status VARCHAR2,
785 p_approver_comments VARCHAR2,
786 p_date_sent DATE,
787 p_date_received DATE,
788 p_creation_date DATE,
789 p_last_update_date DATE,
790 p_created_by NUMBER,
791 p_last_updated_by NUMBER,
792 p_last_update_login NUMBER,
793 p_attribute1 VARCHAR2,
794 p_attribute2 VARCHAR2,
795 p_attribute3 VARCHAR2,
796 p_attribute4 VARCHAR2,
797 p_attribute5 VARCHAR2,
798 p_attribute6 VARCHAR2,
799 p_attribute7 VARCHAR2,
800 p_attribute8 VARCHAR2,
801 p_attribute9 VARCHAR2,
802 p_attribute10 VARCHAR2,
803 p_attribute11 VARCHAR2,
804 p_attribute12 VARCHAR2,
805 p_attribute13 VARCHAR2,
806 p_attribute14 VARCHAR2,
807 p_attribute15 VARCHAR2,
808 p_attribute16 VARCHAR2,
809 p_attribute17 VARCHAR2,
810 p_attribute18 VARCHAR2,
811 p_attribute19 VARCHAR2,
812 p_attribute20 VARCHAR2,
813 p_context VARCHAR2,
814 p_security_group_id NUMBER,
815 p_object_version_number NUMBER,
816 -- ER 16518522
817 p_name VARCHAR2 Default null, -- rassharm
818 p_item_id VARCHAR2 Default null, --rassharm
819 p_item_class VARCHAR2 Default null, --rassharm
820 p_action_type_id integer Default null, --rassharm
821 p_groupchain_id integer Default null, --rassharm
822 p_occurrence integer Default null,
823 p_authority varchar2 Default null,--rassharm
824 p_approver_cat varchar2 default null--rassharm
825 ) IS
826 CURSOR c2 IS
827 SELECT aso_apr_obj_det_s.NEXTVAL
828 FROM sys.DUAL;
829 BEGIN
830 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
831 aso_debug_pub.ADD (
832 'Begin Detail_Insert_Row procedure ',
833 1,
834 'N'
835 );
836 END IF;
837
838 IF (px_approval_det_id IS NULL)
839 OR (px_approval_det_id = fnd_api.g_miss_num)
840 THEN
841 OPEN c2;
842 FETCH c2 INTO px_approval_det_id;
843 CLOSE c2;
844 END IF;
845
846 INSERT INTO aso_apr_approval_details
847 (approval_det_id,
848 object_approval_id,
849 approver_person_id,
850 approver_user_id,
851 approver_sequence,
852 approver_status,
853 approver_comments,
854 date_sent,
855 date_received,
856 creation_date,
857 last_update_date,
858 created_by,
859 last_updated_by,
860 last_update_login,
861 attribute1,
862 attribute2,
863 attribute3,
864 attribute4,
865 attribute5,
866 attribute6,
867 attribute7,
868 attribute8,
869 attribute9,
870 attribute10,
871 attribute11,
872 attribute12,
873 attribute13,
874 attribute14,
875 attribute15,
876 attribute16,
877 attribute17,
878 attribute18,
879 attribute19,
880 attribute20,
881 CONTEXT,
882 security_group_id,
883 object_version_number,
884 -- PA rassharm
885 name,
886 item_id,
887 item_class,
888 action_type_id,
889 group_or_chain_id,
890 occurrence,
891 authority,
892 approver_category)
893 VALUES (px_approval_det_id,
894 DECODE (
895 p_object_approval_id,
896 fnd_api.g_miss_num,
897 NULL,
898 p_object_approval_id
899 ),
900 DECODE (
901 p_approver_person_id,
902 fnd_api.g_miss_num,
903 NULL,
904 p_approver_person_id
905 ),
906 DECODE (
907 p_approver_user_id,
908 fnd_api.g_miss_num,
909 NULL,
913 p_approver_sequence,
910 p_approver_user_id
911 ),
912 DECODE (
914 fnd_api.g_miss_num,
915 NULL,
916 p_approver_sequence
917 ),
918 DECODE (
919 p_approver_status,
920 fnd_api.g_miss_char,
921 NULL,
922 p_approver_status
923 ),
924 DECODE (
925 p_approver_comments,
926 fnd_api.g_miss_char,
927 NULL,
928 p_approver_comments
929 ),
930 aso_utility_pvt.DECODE (
931 p_date_sent,
932 fnd_api.g_miss_date,
933 NULL,
934 p_date_sent
935 ),
936 aso_utility_pvt.DECODE (
937 p_date_received,
938 fnd_api.g_miss_date,
939 NULL,
940 p_date_received
941 ),
942 aso_utility_pvt.DECODE (
943 p_creation_date,
944 fnd_api.g_miss_date,
945 NULL,
946 p_creation_date
947 ),
948 aso_utility_pvt.DECODE (
949 p_last_update_date,
950 fnd_api.g_miss_date,
951 NULL,
952 p_last_update_date
953 ),
954 DECODE (
955 p_created_by,
956 fnd_api.g_miss_num,
957 NULL,
958 p_created_by
959 ),
960 DECODE (
961 p_last_updated_by,
962 fnd_api.g_miss_num,
963 NULL,
964 p_last_updated_by
965 ),
966 DECODE (
967 p_last_update_login,
968 fnd_api.g_miss_num,
969 NULL,
970 p_last_update_login
971 ),
972 DECODE (
973 p_attribute1,
974 fnd_api.g_miss_char,
975 NULL,
976 p_attribute1
977 ),
978 DECODE (
979 p_attribute2,
980 fnd_api.g_miss_char,
981 NULL,
982 p_attribute2
983 ),
984 DECODE (
985 p_attribute3,
986 fnd_api.g_miss_char,
987 NULL,
988 p_attribute3
989 ),
990 DECODE (
991 p_attribute4,
992 fnd_api.g_miss_char,
993 NULL,
994 p_attribute4
995 ),
996 DECODE (
997 p_attribute5,
998 fnd_api.g_miss_char,
999 NULL,
1000 p_attribute5
1001 ),
1002 DECODE (
1003 p_attribute6,
1004 fnd_api.g_miss_char,
1005 NULL,
1006 p_attribute6
1007 ),
1008 DECODE (
1009 p_attribute7,
1010 fnd_api.g_miss_char,
1011 NULL,
1012 p_attribute7
1013 ),
1014 DECODE (
1015 p_attribute8,
1016 fnd_api.g_miss_char,
1017 NULL,
1018 p_attribute8
1019 ),
1020 DECODE (
1021 p_attribute9,
1022 fnd_api.g_miss_char,
1023 NULL,
1024 p_attribute9
1025 ),
1026 DECODE (
1027 p_attribute10,
1028 fnd_api.g_miss_char,
1029 NULL,
1030 p_attribute10
1031 ),
1032 DECODE (
1033 p_attribute11,
1034 fnd_api.g_miss_char,
1035 NULL,
1036 p_attribute11
1037 ),
1038 DECODE (
1039 p_attribute12,
1040 fnd_api.g_miss_char,
1041 NULL,
1042 p_attribute12
1043 ),
1044 DECODE (
1045 p_attribute13,
1046 fnd_api.g_miss_char,
1047 NULL,
1048 p_attribute13
1049 ),
1050 DECODE (
1051 p_attribute14,
1052 fnd_api.g_miss_char,
1053 NULL,
1054 p_attribute14
1055 ),
1056 DECODE (
1057 p_attribute15,
1058 fnd_api.g_miss_char,
1059 NULL,
1060 p_attribute15
1061 ),
1062 DECODE (
1063 p_attribute16,
1064 fnd_api.g_miss_char,
1065 NULL,
1066 p_attribute16
1067 ),
1068 DECODE (
1069 p_attribute17,
1070 fnd_api.g_miss_char,
1071 NULL,
1072 p_attribute17
1073 ),
1074 DECODE (
1075 p_attribute18,
1076 fnd_api.g_miss_char,
1077 NULL,
1078 p_attribute18
1079 ),
1080 DECODE (
1081 p_attribute19,
1082 fnd_api.g_miss_char,
1083 NULL,
1084 p_attribute19
1085 ),
1086 DECODE (
1087 p_attribute20,
1088 fnd_api.g_miss_char,
1089 NULL,
1093 p_context,
1090 p_attribute20
1091 ),
1092 DECODE (
1094 fnd_api.g_miss_char,
1095 NULL,
1096 p_context
1097 ),
1098 DECODE (
1099 p_security_group_id,
1100 fnd_api.g_miss_num,
1101 NULL,
1102 p_security_group_id
1103 ),
1104 DECODE (
1105 p_object_version_number,
1106 fnd_api.g_miss_num,
1107 NULL,
1108 p_object_version_number
1109 ),
1110 -- ER 16518522
1111 DECODE (
1112 p_name,
1113 fnd_api.g_miss_char,
1114 NULL,
1115 p_name
1116 ),
1117
1118 DECODE (
1119 p_item_id,
1120 fnd_api.g_miss_char,
1121 NULL,
1122 p_item_id
1123 ),
1124
1125 DECODE (
1126 p_item_class,
1127 fnd_api.g_miss_char,
1128 NULL,
1129 p_item_class
1130 ),
1131 DECODE (
1132 p_action_type_id,
1133 fnd_api.g_miss_num,
1134 NULL,
1135 p_action_type_id
1136 ),
1137
1138
1139 DECODE (
1140 p_groupchain_id,
1141 fnd_api.g_miss_num,
1142 NULL,
1143 p_groupchain_id
1144 ),
1145 DECODE (
1146 p_occurrence,
1147 fnd_api.g_miss_num,
1148 NULL,
1149 p_occurrence
1150 ),
1151 DECODE (
1152 p_authority,
1153 fnd_api.g_miss_char,
1154 NULL,
1155 p_authority
1156 ),
1157 DECODE (
1158 p_approver_cat,
1159 fnd_api.g_miss_char,
1160 NULL,
1161 p_approver_cat
1162 )
1163 );
1164
1165 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1166 aso_debug_pub.ADD (
1167 'End Detail_Insert_Row procedure ',
1168 1,
1169 'N'
1170 );
1171 END IF;
1172 -- EXCEPTION
1173 -- WHEN OTHERS THEN
1174 -- aso_debug_pub.add('Exception in Detail_Insert_Row procedure ', 1, 'N');
1175 -- aso_debug_pub.add('errmsg is '||substr(SQLERRM,1,250), 1, 'N');
1176
1177 END detail_insert_row;
1178
1179 PROCEDURE detail_update_row (
1180 p_approval_det_id NUMBER,
1181 p_object_approval_id NUMBER,
1182 p_approver_person_id NUMBER,
1183 p_approver_user_id NUMBER,
1184 p_approver_sequence NUMBER,
1185 p_approver_status VARCHAR2,
1186 p_approver_comments VARCHAR2,
1187 p_date_sent DATE,
1188 p_date_received DATE,
1189 p_creation_date DATE,
1190 p_last_update_date DATE,
1191 p_created_by NUMBER,
1192 p_last_updated_by NUMBER,
1193 p_last_update_login NUMBER,
1194 p_attribute1 VARCHAR2,
1195 p_attribute2 VARCHAR2,
1196 p_attribute3 VARCHAR2,
1197 p_attribute4 VARCHAR2,
1198 p_attribute5 VARCHAR2,
1199 p_attribute6 VARCHAR2,
1200 p_attribute7 VARCHAR2,
1201 p_attribute8 VARCHAR2,
1202 p_attribute9 VARCHAR2,
1203 p_attribute10 VARCHAR2,
1204 p_attribute11 VARCHAR2,
1205 p_attribute12 VARCHAR2,
1206 p_attribute13 VARCHAR2,
1207 p_attribute14 VARCHAR2,
1208 p_attribute15 VARCHAR2,
1209 p_attribute16 VARCHAR2,
1210 p_attribute17 VARCHAR2,
1211 p_attribute18 VARCHAR2,
1212 p_attribute19 VARCHAR2,
1213 p_attribute20 VARCHAR2,
1214 p_context VARCHAR2,
1215 p_security_group_id NUMBER,
1216 p_object_version_number NUMBER,
1217 -- ER 16518522
1218 p_name VARCHAR2 Default null, -- rassharm
1219 p_item_id VARCHAR2 Default null, --rassharm
1220 p_item_class VARCHAR2 Default null, --rassharm
1221 p_action_type_id integer Default null, --rassharm
1222 p_groupchain_id integer Default null, --rassharm
1223 p_occurrence integer Default null,
1224 p_authority varchar2 Default null,--rassharm
1225 p_approver_cat varchar2 default null--rassharm
1226 ) IS
1227 BEGIN
1228 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1229 aso_debug_pub.ADD (
1230 'Begin Detail_Update_Row procedure ',
1231 1,
1232 'N'
1233 );
1234 END IF;
1235
1236 UPDATE aso_apr_approval_details
1237 SET approval_det_id = DECODE (
1238 p_approval_det_id,
1239 fnd_api.g_miss_num,
1240 approval_det_id,
1241 p_approval_det_id
1242 ),
1246 object_approval_id,
1243 object_approval_id = DECODE (
1244 p_object_approval_id,
1245 fnd_api.g_miss_num,
1247 p_object_approval_id
1248 ),
1249 approver_person_id = DECODE (
1250 p_approver_person_id,
1251 fnd_api.g_miss_num,
1252 approver_person_id,
1253 p_approver_person_id
1254 ),
1255 approver_user_id = DECODE (
1256 p_approver_user_id,
1257 fnd_api.g_miss_num,
1258 approver_user_id,
1259 p_approver_user_id
1260 ),
1261 approver_sequence = DECODE (
1262 p_approver_sequence,
1263 fnd_api.g_miss_num,
1264 approver_sequence,
1265 p_approver_sequence
1266 ),
1267 approver_status = DECODE (
1268 p_approver_status,
1269 fnd_api.g_miss_char,
1270 approver_status,
1271 p_approver_status
1272 ),
1273 approver_comments = DECODE (
1274 p_approver_comments,
1275 fnd_api.g_miss_char,
1276 approver_comments,
1277 p_approver_comments
1278 ),
1279 date_sent = aso_utility_pvt.DECODE (
1280 p_date_sent,
1281 fnd_api.g_miss_date,
1282 date_sent,
1283 p_date_sent
1284 ),
1285 date_received = aso_utility_pvt.DECODE (
1286 p_date_received,
1287 fnd_api.g_miss_date,
1288 date_received,
1289 p_date_received
1290 ),
1291 creation_date = aso_utility_pvt.DECODE (
1292 p_creation_date,
1293 fnd_api.g_miss_date,
1294 creation_date,
1295 p_creation_date
1296 ),
1297 last_update_date = aso_utility_pvt.DECODE (
1298 p_last_update_date,
1299 fnd_api.g_miss_date,
1300 last_update_date,
1301 p_last_update_date
1302 ),
1303 created_by = DECODE (
1304 p_created_by,
1305 fnd_api.g_miss_num,
1306 created_by,
1307 p_created_by
1308 ),
1309 last_updated_by = DECODE (
1310 p_last_updated_by,
1311 fnd_api.g_miss_num,
1312 last_updated_by,
1313 p_last_updated_by
1314 ),
1315 last_update_login = DECODE (
1316 p_last_update_login,
1317 fnd_api.g_miss_num,
1318 last_update_login,
1319 p_last_update_login
1320 ),
1321 attribute1 = DECODE (
1322 p_attribute1,
1323 fnd_api.g_miss_char,
1324 attribute1,
1325 p_attribute1
1326 ),
1327 attribute2 = DECODE (
1328 p_attribute2,
1329 fnd_api.g_miss_char,
1330 attribute2,
1331 p_attribute2
1332 ),
1333 attribute3 = DECODE (
1334 p_attribute3,
1335 fnd_api.g_miss_char,
1336 attribute3,
1337 p_attribute3
1338 ),
1339 attribute4 = DECODE (
1340 p_attribute4,
1341 fnd_api.g_miss_char,
1342 attribute4,
1343 p_attribute4
1344 ),
1345 attribute5 = DECODE (
1346 p_attribute5,
1347 fnd_api.g_miss_char,
1348 attribute5,
1349 p_attribute5
1350 ),
1351 attribute6 = DECODE (
1352 p_attribute6,
1353 fnd_api.g_miss_char,
1354 attribute6,
1355 p_attribute6
1356 ),
1357 attribute7 = DECODE (
1358 p_attribute7,
1359 fnd_api.g_miss_char,
1360 attribute7,
1361 p_attribute7
1362 ),
1363 attribute8 = DECODE (
1364 p_attribute8,
1365 fnd_api.g_miss_char,
1366 attribute8,
1367 p_attribute8
1368 ),
1369 attribute9 = DECODE (
1370 p_attribute9,
1371 fnd_api.g_miss_char,
1372 attribute9,
1373 p_attribute9
1374 ),
1375 attribute10 = DECODE (
1376 p_attribute10,
1380 ),
1377 fnd_api.g_miss_char,
1378 attribute10,
1379 p_attribute10
1381 attribute11 = DECODE (
1382 p_attribute11,
1383 fnd_api.g_miss_char,
1384 attribute11,
1385 p_attribute11
1386 ),
1387 attribute12 = DECODE (
1388 p_attribute12,
1389 fnd_api.g_miss_char,
1390 attribute12,
1391 p_attribute12
1392 ),
1393 attribute13 = DECODE (
1394 p_attribute13,
1395 fnd_api.g_miss_char,
1396 attribute13,
1397 p_attribute13
1398 ),
1399 attribute14 = DECODE (
1400 p_attribute14,
1401 fnd_api.g_miss_char,
1402 attribute14,
1403 p_attribute14
1404 ),
1405 attribute15 = DECODE (
1406 p_attribute15,
1407 fnd_api.g_miss_char,
1408 attribute15,
1409 p_attribute15
1410 ),
1411 attribute16 = DECODE (
1412 p_attribute16,
1413 fnd_api.g_miss_char,
1414 attribute16,
1415 p_attribute16
1416 ),
1417 attribute17 = DECODE (
1418 p_attribute17,
1419 fnd_api.g_miss_char,
1420 attribute17,
1421 p_attribute17
1422 ),
1423 attribute18 = DECODE (
1424 p_attribute18,
1425 fnd_api.g_miss_char,
1426 attribute18,
1427 p_attribute18
1428 ),
1429 attribute19 = DECODE (
1430 p_attribute19,
1431 fnd_api.g_miss_char,
1432 attribute19,
1433 p_attribute19
1434 ),
1435 attribute20 = DECODE (
1436 p_attribute20,
1437 fnd_api.g_miss_char,
1438 attribute20,
1439 p_attribute20
1440 ),
1441 CONTEXT = DECODE (
1442 p_context,
1443 fnd_api.g_miss_char,
1444 CONTEXT,
1445 p_context
1446 ),
1447 security_group_id = DECODE (
1448 p_security_group_id,
1449 fnd_api.g_miss_num,
1450 security_group_id,
1451 p_security_group_id
1452 ),
1453 object_version_number = DECODE (
1454 p_object_version_number,
1455 fnd_api.g_miss_num,
1456 object_version_number,
1457 p_object_version_number
1458 )
1459 WHERE approval_det_id = p_approval_det_id;
1460
1461 IF (SQL%NOTFOUND)
1462 THEN
1463 RAISE NO_DATA_FOUND;
1464 END IF;
1465
1466 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1467 aso_debug_pub.ADD (
1468 'End Detail_Update_Row procedure ',
1469 1,
1470 'N'
1471 );
1472 END IF;
1473 END detail_update_row;
1474
1475 PROCEDURE detail_lock_row (
1476 p_approval_det_id NUMBER,
1477 p_object_approval_id NUMBER,
1478 p_approver_person_id NUMBER,
1479 p_approver_user_id NUMBER,
1480 p_approver_sequence NUMBER,
1481 p_approver_status VARCHAR2,
1482 p_approver_comments VARCHAR2,
1483 p_date_sent DATE,
1484 p_date_received DATE,
1485 p_creation_date DATE,
1486 p_last_update_date DATE,
1487 p_created_by NUMBER,
1488 p_last_updated_by NUMBER,
1489 p_last_update_login NUMBER,
1490 p_attribute1 VARCHAR2,
1491 p_attribute2 VARCHAR2,
1492 p_attribute3 VARCHAR2,
1493 p_attribute4 VARCHAR2,
1494 p_attribute5 VARCHAR2,
1495 p_attribute6 VARCHAR2,
1496 p_attribute7 VARCHAR2,
1497 p_attribute8 VARCHAR2,
1498 p_attribute9 VARCHAR2,
1499 p_attribute10 VARCHAR2,
1500 p_attribute11 VARCHAR2,
1501 p_attribute12 VARCHAR2,
1502 p_attribute13 VARCHAR2,
1503 p_attribute14 VARCHAR2,
1504 p_attribute15 VARCHAR2,
1505 p_context VARCHAR2,
1506 p_security_group_id NUMBER,
1507 p_object_version_number NUMBER
1508 ) IS
1509 CURSOR c IS
1510 SELECT approval_det_id, object_approval_id, approver_person_id,
1511 approver_user_id, approver_sequence, approver_status,
1512 approver_comments, date_sent, date_received, creation_date,
1516 attribute12, attribute13, attribute14, attribute15, CONTEXT,
1513 last_update_date, created_by, last_updated_by, last_update_login,
1514 attribute1, attribute2, attribute3, attribute4, attribute5, attribute6,
1515 attribute7, attribute8, attribute9, attribute10, attribute11,
1517 security_group_id, object_version_number
1518 FROM aso_apr_approval_details
1519 WHERE approval_det_id = p_approval_det_id
1520 FOR UPDATE OF approval_det_id NOWAIT;
1521
1522 recinfo c%ROWTYPE;
1523 BEGIN
1524 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1525 aso_debug_pub.ADD (
1526 'Begin Detail_Lock_Row procedure ',
1527 1,
1528 'N'
1529 );
1530 END IF;
1531 OPEN c;
1532 FETCH c INTO recinfo;
1533
1534 IF (c%NOTFOUND)
1535 THEN
1536 CLOSE c;
1537 fnd_message.set_name (
1538 'FND',
1539 'FORM_RECORD_DELETED'
1540 );
1541 app_exception.raise_exception;
1542 END IF;
1543
1544 CLOSE c;
1545
1546 IF (((recinfo.last_update_date = p_last_update_date)
1547 OR ((recinfo.last_update_date IS NULL)
1548 AND (p_last_update_date IS NULL)
1549 )
1550 )
1551 )
1552 THEN
1553 RETURN;
1554 ELSE
1555 fnd_message.set_name (
1556 'FND',
1557 'FORM_RECORD_CHANGED'
1558 );
1559 app_exception.raise_exception;
1560 END IF;
1561
1562 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1563 aso_debug_pub.ADD (
1564 'End Detail_Lock_Row procedure ',
1565 1,
1566 'N'
1567 );
1568 END IF;
1569 END detail_lock_row;
1570
1571 PROCEDURE detail_delete_row (
1572 p_approval_det_id NUMBER
1573 ) IS
1574 BEGIN
1575 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1576 aso_debug_pub.ADD (
1577 'Begin Detail_Delete_Row procedure ',
1578 1,
1579 'N'
1580 );
1581 END IF;
1582
1583 DELETE FROM aso_apr_approval_details
1584 WHERE approval_det_id = p_approval_det_id;
1585
1586 IF (SQL%NOTFOUND)
1587 THEN
1588 RAISE NO_DATA_FOUND;
1589 END IF;
1590
1591 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1592 aso_debug_pub.ADD (
1593 'End Detail_Delete_Row procedure ',
1594 1,
1595 'N'
1596 );
1597 END IF;
1598 END detail_delete_row;
1599 -- Procedures for the Rule Table
1600
1601 PROCEDURE rule_insert_row (
1602 px_rule_id IN OUT NOCOPY /* file.sql.39 change */ NUMBER,
1603 p_oam_rule_id NUMBER,
1604 p_rule_action_id NUMBER,
1605 p_creation_date DATE,
1606 p_created_by NUMBER,
1607 p_last_update_date DATE,
1608 p_last_updated_by NUMBER,
1609 p_last_update_login NUMBER,
1610 p_object_approval_id NUMBER,
1611 p_attribute1 VARCHAR2,
1612 p_attribute2 VARCHAR2,
1613 p_attribute3 VARCHAR2,
1614 p_attribute4 VARCHAR2,
1615 p_attribute5 VARCHAR2,
1616 p_attribute6 VARCHAR2,
1617 p_attribute7 VARCHAR2,
1618 p_attribute8 VARCHAR2,
1619 p_attribute9 VARCHAR2,
1620 p_attribute10 VARCHAR2,
1621 p_attribute11 VARCHAR2,
1622 p_attribute12 VARCHAR2,
1623 p_attribute13 VARCHAR2,
1624 p_attribute14 VARCHAR2,
1625 p_attribute15 VARCHAR2,
1626 p_attribute16 VARCHAR2,
1627 p_attribute17 VARCHAR2,
1628 p_attribute18 VARCHAR2,
1629 p_attribute19 VARCHAR2,
1630 p_attribute20 VARCHAR2,
1631 p_context VARCHAR2,
1632 p_security_group_id NUMBER,
1633 p_object_version_number NUMBER
1634 ) IS
1635 CURSOR c2 IS
1636 SELECT aso_apr_rule_s.NEXTVAL
1637 FROM sys.DUAL;
1638 BEGIN
1639 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1640 aso_debug_pub.ADD (
1641 'Begin Rule_Insert_Row procedure ',
1642 1,
1643 'N'
1644 );
1645 END IF;
1646 --If (px_RULE_ID IS NULL) OR (px_RULE_ID = FND_API.G_MISS_NUM) then
1647 OPEN c2;
1648 FETCH c2 INTO px_rule_id;
1649 CLOSE c2;
1650 --End If;
1651 INSERT INTO aso_apr_rules
1652 (rule_id,
1653 oam_rule_id,
1654 rule_action_id,
1655 creation_date,
1656 created_by,
1657 last_update_date,
1658 last_updated_by,
1659 last_update_login,
1660 object_approval_id,
1661 attribute1,
1662 attribute2,
1663 attribute3,
1664 attribute4,
1665 attribute5,
1666 attribute6,
1667 attribute7,
1668 attribute8,
1669 attribute9,
1670 attribute10,
1671 attribute11,
1672 attribute12,
1673 attribute13,
1674 attribute14,
1675 attribute15,
1676 attribute16,
1677 attribute17,
1678 attribute18,
1679 attribute19,
1680 attribute20,
1684 VALUES (px_rule_id,
1681 CONTEXT,
1682 security_group_id,
1683 object_version_number)
1685 DECODE (
1686 p_oam_rule_id,
1687 fnd_api.g_miss_num,
1688 NULL,
1689 p_oam_rule_id
1690 ),
1691 DECODE (
1692 p_rule_action_id,
1693 fnd_api.g_miss_num,
1694 NULL,
1695 p_rule_action_id
1696 ),
1697 aso_utility_pvt.DECODE (
1698 p_creation_date,
1699 fnd_api.g_miss_date,
1700 NULL,
1701 p_creation_date
1702 ),
1703 DECODE (
1704 p_created_by,
1705 fnd_api.g_miss_num,
1706 NULL,
1707 p_created_by
1708 ),
1709 aso_utility_pvt.DECODE (
1710 p_last_update_date,
1711 fnd_api.g_miss_date,
1712 NULL,
1713 p_last_update_date
1714 ),
1715 DECODE (
1716 p_last_updated_by,
1717 fnd_api.g_miss_num,
1718 NULL,
1719 p_last_updated_by
1720 ),
1721 DECODE (
1722 p_last_update_login,
1723 fnd_api.g_miss_num,
1724 NULL,
1725 p_last_update_login
1726 ),
1727 DECODE (
1728 p_object_approval_id,
1729 fnd_api.g_miss_num,
1730 NULL,
1731 p_object_approval_id
1732 ),
1733 DECODE (
1734 p_attribute1,
1735 fnd_api.g_miss_char,
1736 NULL,
1737 p_attribute1
1738 ),
1739 DECODE (
1740 p_attribute2,
1741 fnd_api.g_miss_char,
1742 NULL,
1743 p_attribute2
1744 ),
1745 DECODE (
1746 p_attribute3,
1747 fnd_api.g_miss_char,
1748 NULL,
1749 p_attribute3
1750 ),
1751 DECODE (
1752 p_attribute4,
1753 fnd_api.g_miss_char,
1754 NULL,
1755 p_attribute4
1756 ),
1757 DECODE (
1758 p_attribute5,
1759 fnd_api.g_miss_char,
1760 NULL,
1761 p_attribute5
1762 ),
1763 DECODE (
1764 p_attribute6,
1765 fnd_api.g_miss_char,
1766 NULL,
1767 p_attribute6
1768 ),
1769 DECODE (
1770 p_attribute7,
1771 fnd_api.g_miss_char,
1772 NULL,
1773 p_attribute7
1774 ),
1775 DECODE (
1776 p_attribute8,
1777 fnd_api.g_miss_char,
1778 NULL,
1779 p_attribute8
1780 ),
1781 DECODE (
1782 p_attribute9,
1783 fnd_api.g_miss_char,
1784 NULL,
1785 p_attribute9
1786 ),
1787 DECODE (
1788 p_attribute10,
1789 fnd_api.g_miss_char,
1790 NULL,
1791 p_attribute10
1792 ),
1793 DECODE (
1794 p_attribute11,
1795 fnd_api.g_miss_char,
1796 NULL,
1797 p_attribute11
1798 ),
1799 DECODE (
1800 p_attribute12,
1801 fnd_api.g_miss_char,
1802 NULL,
1803 p_attribute12
1804 ),
1805 DECODE (
1806 p_attribute13,
1807 fnd_api.g_miss_char,
1808 NULL,
1809 p_attribute13
1810 ),
1811 DECODE (
1812 p_attribute14,
1813 fnd_api.g_miss_char,
1814 NULL,
1815 p_attribute14
1816 ),
1817 DECODE (
1818 p_attribute15,
1819 fnd_api.g_miss_char,
1820 NULL,
1821 p_attribute15
1822 ),
1823 DECODE (
1824 p_attribute16,
1825 fnd_api.g_miss_char,
1826 NULL,
1827 p_attribute16
1828 ),
1829 DECODE (
1830 p_attribute17,
1831 fnd_api.g_miss_char,
1832 NULL,
1833 p_attribute17
1834 ),
1835 DECODE (
1836 p_attribute18,
1837 fnd_api.g_miss_char,
1838 NULL,
1839 p_attribute18
1840 ),
1841 DECODE (
1842 p_attribute19,
1843 fnd_api.g_miss_char,
1844 NULL,
1845 p_attribute19
1846 ),
1847 DECODE (
1848 p_attribute20,
1849 fnd_api.g_miss_char,
1850 NULL,
1851 p_attribute20
1852 ),
1853 DECODE (
1854 p_context,
1855 fnd_api.g_miss_char,
1856 NULL,
1857 p_context
1858 ),
1859 DECODE (
1860 p_security_group_id,
1861 fnd_api.g_miss_num,
1862 NULL,
1866 p_object_version_number,
1863 p_security_group_id
1864 ),
1865 DECODE (
1867 fnd_api.g_miss_num,
1868 NULL,
1869 p_object_version_number
1870 ));
1871
1872 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1873 aso_debug_pub.ADD (
1874 'End Rule_Insert_Row procedure ',
1875 1,
1876 'N'
1877 );
1878 END IF;
1879 END rule_insert_row;
1880
1881 PROCEDURE rule_update_row (
1882 p_rule_id NUMBER,
1883 p_oam_rule_id NUMBER,
1884 p_rule_action_id NUMBER,
1885 p_creation_date DATE,
1886 p_created_by NUMBER,
1887 p_last_update_date DATE,
1888 p_last_updated_by NUMBER,
1889 p_last_update_login NUMBER,
1890 p_object_approval_id NUMBER,
1891 p_attribute1 VARCHAR2,
1892 p_attribute2 VARCHAR2,
1893 p_attribute3 VARCHAR2,
1894 p_attribute4 VARCHAR2,
1895 p_attribute5 VARCHAR2,
1896 p_attribute6 VARCHAR2,
1897 p_attribute7 VARCHAR2,
1898 p_attribute8 VARCHAR2,
1899 p_attribute9 VARCHAR2,
1900 p_attribute10 VARCHAR2,
1901 p_attribute11 VARCHAR2,
1902 p_attribute12 VARCHAR2,
1903 p_attribute13 VARCHAR2,
1904 p_attribute14 VARCHAR2,
1905 p_attribute15 VARCHAR2,
1906 p_attribute16 VARCHAR2,
1907 p_attribute17 VARCHAR2,
1908 p_attribute18 VARCHAR2,
1909 p_attribute19 VARCHAR2,
1910 p_attribute20 VARCHAR2,
1911 p_context VARCHAR2,
1912 p_security_group_id NUMBER,
1913 p_object_version_number NUMBER
1914 ) IS
1915 BEGIN
1916 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1917 aso_debug_pub.ADD (
1918 'Begin Rule_Update_Row procedure ',
1919 1,
1920 'N'
1921 );
1922 END IF;
1923
1924 UPDATE aso_apr_rules
1925 SET rule_id = p_rule_id,
1926 oam_rule_id = DECODE (
1927 p_oam_rule_id,
1928 fnd_api.g_miss_num,
1929 rule_id,
1930 p_oam_rule_id
1931 ),
1932 rule_action_id = DECODE (
1933 p_rule_action_id,
1934 fnd_api.g_miss_num,
1935 rule_action_id,
1936 p_rule_action_id
1937 ),
1938 creation_date = aso_utility_pvt.DECODE (
1939 p_creation_date,
1940 fnd_api.g_miss_date,
1941 creation_date,
1942 p_creation_date
1943 ),
1944 created_by = DECODE (
1945 p_created_by,
1946 fnd_api.g_miss_num,
1947 created_by,
1948 p_created_by
1949 ),
1950 last_update_date = aso_utility_pvt.DECODE (
1951 p_last_update_date,
1952 fnd_api.g_miss_date,
1953 last_update_date,
1954 p_last_update_date
1955 ),
1956 last_updated_by = DECODE (
1957 p_last_updated_by,
1958 fnd_api.g_miss_num,
1959 NULL,
1960 p_last_updated_by
1961 ),
1962 last_update_login = DECODE (
1963 p_last_update_login,
1964 fnd_api.g_miss_num,
1965 last_update_login,
1966 p_last_update_login
1967 ),
1968 object_approval_id = DECODE (
1969 p_object_approval_id,
1970 fnd_api.g_miss_num,
1971 object_approval_id,
1972 p_object_approval_id
1973 ),
1974 attribute1 = DECODE (
1975 p_attribute1,
1976 fnd_api.g_miss_char,
1977 attribute1,
1978 p_attribute1
1979 ),
1980 attribute2 = DECODE (
1981 p_attribute2,
1982 fnd_api.g_miss_char,
1983 attribute2,
1984 p_attribute2
1985 ),
1986 attribute3 = DECODE (
1987 p_attribute3,
1988 fnd_api.g_miss_char,
1989 attribute3,
1990 p_attribute3
1991 ),
1992 attribute4 = DECODE (
1993 p_attribute4,
1994 fnd_api.g_miss_char,
1995 attribute4,
1996 p_attribute4
1997 ),
1998 attribute5 = DECODE (
1999 p_attribute5,
2000 fnd_api.g_miss_char,
2001 attribute5,
2005 p_attribute6,
2002 p_attribute5
2003 ),
2004 attribute6 = DECODE (
2006 fnd_api.g_miss_char,
2007 attribute6,
2008 p_attribute6
2009 ),
2010 attribute7 = DECODE (
2011 p_attribute7,
2012 fnd_api.g_miss_char,
2013 attribute7,
2014 p_attribute7
2015 ),
2016 attribute8 = DECODE (
2017 p_attribute8,
2018 fnd_api.g_miss_char,
2019 attribute8,
2020 p_attribute8
2021 ),
2022 attribute9 = DECODE (
2023 p_attribute9,
2024 fnd_api.g_miss_char,
2025 attribute9,
2026 p_attribute9
2027 ),
2028 attribute10 = DECODE (
2029 p_attribute10,
2030 fnd_api.g_miss_char,
2031 attribute10,
2032 p_attribute10
2033 ),
2034 attribute11 = DECODE (
2035 p_attribute11,
2036 fnd_api.g_miss_char,
2037 attribute11,
2038 p_attribute11
2039 ),
2040 attribute12 = DECODE (
2041 p_attribute12,
2042 fnd_api.g_miss_char,
2043 attribute12,
2044 p_attribute12
2045 ),
2046 attribute13 = DECODE (
2047 p_attribute13,
2048 fnd_api.g_miss_char,
2049 attribute13,
2050 p_attribute13
2051 ),
2052 attribute14 = DECODE (
2053 p_attribute14,
2054 fnd_api.g_miss_char,
2055 attribute14,
2056 p_attribute14
2057 ),
2058 attribute15 = DECODE (
2059 p_attribute15,
2060 fnd_api.g_miss_char,
2061 attribute15,
2062 p_attribute15
2063 ),
2064 attribute16 = DECODE (
2065 p_attribute16,
2066 fnd_api.g_miss_char,
2067 attribute16,
2068 p_attribute16
2069 ),
2070 attribute17 = DECODE (
2071 p_attribute17,
2072 fnd_api.g_miss_char,
2073 attribute17,
2074 p_attribute17
2075 ),
2076 attribute18 = DECODE (
2077 p_attribute18,
2078 fnd_api.g_miss_char,
2079 attribute18,
2080 p_attribute18
2081 ),
2082 attribute19 = DECODE (
2083 p_attribute19,
2084 fnd_api.g_miss_char,
2085 attribute19,
2086 p_attribute19
2087 ),
2088 attribute20 = DECODE (
2089 p_attribute20,
2090 fnd_api.g_miss_char,
2091 attribute20,
2092 p_attribute20
2093 ),
2094 CONTEXT = DECODE (
2095 p_context,
2096 fnd_api.g_miss_char,
2097 CONTEXT,
2098 p_context
2099 ),
2100 security_group_id = DECODE (
2101 p_security_group_id,
2102 fnd_api.g_miss_num,
2103 security_group_id,
2104 p_security_group_id
2105 ),
2106 object_version_number = DECODE (
2107 p_object_version_number,
2108 fnd_api.g_miss_num,
2109 object_version_number,
2110 p_object_version_number
2111 )
2112 WHERE rule_id = p_rule_id;
2113
2114 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2115 aso_debug_pub.ADD (
2116 'End Rule_Update_Row procedure ',
2117 1,
2118 'N'
2119 );
2120 END IF;
2121 END rule_update_row;
2122
2123 PROCEDURE rule_lock_row (
2124 p_rule_id NUMBER,
2125 p_oam_rule_id NUMBER,
2126 p_rule_action_id NUMBER,
2127 p_creation_date DATE,
2128 p_created_by NUMBER,
2129 p_last_update_date DATE,
2130 p_last_updated_by NUMBER,
2131 p_last_update_login NUMBER,
2132 p_object_approval_id NUMBER,
2133 p_attribute1 VARCHAR2,
2134 p_attribute2 VARCHAR2,
2135 p_attribute3 VARCHAR2,
2136 p_attribute4 VARCHAR2,
2137 p_attribute5 VARCHAR2,
2138 p_attribute6 VARCHAR2,
2139 p_attribute7 VARCHAR2,
2140 p_attribute8 VARCHAR2,
2141 p_attribute9 VARCHAR2,
2145 p_attribute13 VARCHAR2,
2142 p_attribute10 VARCHAR2,
2143 p_attribute11 VARCHAR2,
2144 p_attribute12 VARCHAR2,
2146 p_attribute14 VARCHAR2,
2147 p_attribute15 VARCHAR2,
2148 p_context VARCHAR2,
2149 p_security_group_id NUMBER,
2150 p_object_version_number NUMBER
2151 ) IS
2152 CURSOR c IS
2153 SELECT rule_id, oam_rule_id, rule_action_id, creation_date, created_by,
2154 last_update_date, last_updated_by, last_update_login,
2155 object_approval_id, attribute1, attribute2, attribute3, attribute4,
2156 attribute5, attribute6, attribute7, attribute8, attribute9,
2157 attribute10, attribute11, attribute12, attribute13, attribute14,
2158 attribute15, CONTEXT, security_group_id, object_version_number
2159 FROM aso_apr_rules
2160 WHERE rule_id = p_rule_id
2161 FOR UPDATE OF rule_id NOWAIT;
2162
2163 recinfo c%ROWTYPE;
2164 BEGIN
2165 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2166 aso_debug_pub.ADD (
2167 'Begin Rule_Lock_Row procedure ',
2168 1,
2169 'N'
2170 );
2171 END IF;
2172 OPEN c;
2173 FETCH c INTO recinfo;
2174
2175 IF (c%NOTFOUND)
2176 THEN
2177 CLOSE c;
2178 fnd_message.set_name (
2179 'FND',
2180 'FORM_RECORD_DELETED'
2181 );
2182 app_exception.raise_exception;
2183 END IF;
2184
2185 CLOSE c;
2186
2187 IF (((recinfo.last_update_date = p_last_update_date)
2188 OR ((recinfo.last_update_date IS NULL)
2189 AND (p_last_update_date IS NULL)
2190 )
2191 )
2192 )
2193 THEN
2194 RETURN;
2195 ELSE
2196 fnd_message.set_name (
2197 'FND',
2198 'FORM_RECORD_CHANGED'
2199 );
2200 app_exception.raise_exception;
2201 END IF;
2202
2203 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2204 aso_debug_pub.ADD (
2205 'End Rule_Lock_Row procedure ',
2206 1,
2207 'N'
2208 );
2209 END IF;
2210 END rule_lock_row;
2211
2212 PROCEDURE rule_delete_row (
2213 p_rule_id NUMBER
2214 ) IS
2215 BEGIN
2216 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2217 aso_debug_pub.ADD (
2218 'Begin Rule_Delete_Row procedure ',
2219 1,
2220 'N'
2221 );
2222 END IF;
2223
2224 DELETE FROM aso_apr_rules
2225 WHERE rule_id = p_rule_id;
2226
2227 IF (SQL%NOTFOUND)
2228 THEN
2229 RAISE NO_DATA_FOUND;
2230 END IF;
2231
2232 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2233 aso_debug_pub.ADD (
2234 'End Rule_Delete_Row procedure ',
2235 1,
2236 'N'
2237 );
2238 END IF;
2239 END rule_delete_row;
2240 END aso_apr_approvals_pkg;