[Home] [Help]
PACKAGE BODY: APPS.ASO_APR_APPROVALS_PKG
Source
1 PACKAGE BODY aso_apr_approvals_pkg AS
2 /* $Header: asotappb.pls 120.1 2005/06/29 12:38:34 appldev 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,
410 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
407 p_requester_group_id NUMBER
408 ) IS
409 BEGIN
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,
465 p_last_update_date
466 ),
467 last_updated_by = DECODE (
468 p_last_updated_by,
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,
538 ),
535 fnd_api.g_miss_char,
536 attribute10,
537 p_attribute10
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,
608 security_group_id,
609 p_security_group_id
610 ),
611 object_version_number = DECODE (
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
675 'N'
672 aso_debug_pub.ADD (
673 'End Header_Delete_Row procedure ',
674 1,
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
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)
756 )
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,
805 p_attribute13 VARCHAR2,
802 p_attribute10 VARCHAR2,
803 p_attribute11 VARCHAR2,
804 p_attribute12 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 ) IS
817 CURSOR c2 IS
818 SELECT aso_apr_obj_det_s.NEXTVAL
819 FROM sys.DUAL;
820 BEGIN
821 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
822 aso_debug_pub.ADD (
823 'Begin Detail_Insert_Row procedure ',
824 1,
825 'N'
826 );
827 END IF;
828
829 IF (px_approval_det_id IS NULL)
830 OR (px_approval_det_id = fnd_api.g_miss_num)
831 THEN
832 OPEN c2;
833 FETCH c2 INTO px_approval_det_id;
834 CLOSE c2;
835 END IF;
836
837 INSERT INTO aso_apr_approval_details
838 (approval_det_id,
839 object_approval_id,
840 approver_person_id,
841 approver_user_id,
842 approver_sequence,
843 approver_status,
844 approver_comments,
845 date_sent,
846 date_received,
847 creation_date,
848 last_update_date,
849 created_by,
850 last_updated_by,
851 last_update_login,
852 attribute1,
853 attribute2,
854 attribute3,
855 attribute4,
856 attribute5,
857 attribute6,
858 attribute7,
859 attribute8,
860 attribute9,
861 attribute10,
862 attribute11,
863 attribute12,
864 attribute13,
865 attribute14,
866 attribute15,
867 attribute16,
868 attribute17,
869 attribute18,
870 attribute19,
871 attribute20,
872 CONTEXT,
873 security_group_id,
874 object_version_number)
875 VALUES (px_approval_det_id,
876 DECODE (
877 p_object_approval_id,
878 fnd_api.g_miss_num,
879 NULL,
880 p_object_approval_id
881 ),
882 DECODE (
883 p_approver_person_id,
884 fnd_api.g_miss_num,
885 NULL,
886 p_approver_person_id
887 ),
888 DECODE (
889 p_approver_user_id,
890 fnd_api.g_miss_num,
891 NULL,
892 p_approver_user_id
893 ),
894 DECODE (
895 p_approver_sequence,
896 fnd_api.g_miss_num,
897 NULL,
898 p_approver_sequence
899 ),
900 DECODE (
901 p_approver_status,
902 fnd_api.g_miss_char,
903 NULL,
904 p_approver_status
905 ),
906 DECODE (
907 p_approver_comments,
908 fnd_api.g_miss_char,
909 NULL,
910 p_approver_comments
911 ),
912 aso_utility_pvt.DECODE (
913 p_date_sent,
914 fnd_api.g_miss_date,
915 NULL,
916 p_date_sent
917 ),
918 aso_utility_pvt.DECODE (
919 p_date_received,
920 fnd_api.g_miss_date,
921 NULL,
922 p_date_received
923 ),
924 aso_utility_pvt.DECODE (
925 p_creation_date,
926 fnd_api.g_miss_date,
927 NULL,
928 p_creation_date
929 ),
930 aso_utility_pvt.DECODE (
931 p_last_update_date,
932 fnd_api.g_miss_date,
933 NULL,
934 p_last_update_date
935 ),
936 DECODE (
937 p_created_by,
938 fnd_api.g_miss_num,
939 NULL,
940 p_created_by
941 ),
942 DECODE (
943 p_last_updated_by,
944 fnd_api.g_miss_num,
945 NULL,
946 p_last_updated_by
947 ),
948 DECODE (
949 p_last_update_login,
950 fnd_api.g_miss_num,
951 NULL,
952 p_last_update_login
953 ),
954 DECODE (
955 p_attribute1,
956 fnd_api.g_miss_char,
957 NULL,
958 p_attribute1
959 ),
960 DECODE (
961 p_attribute2,
962 fnd_api.g_miss_char,
963 NULL,
964 p_attribute2
968 fnd_api.g_miss_char,
965 ),
966 DECODE (
967 p_attribute3,
969 NULL,
970 p_attribute3
971 ),
972 DECODE (
973 p_attribute4,
974 fnd_api.g_miss_char,
975 NULL,
976 p_attribute4
977 ),
978 DECODE (
979 p_attribute5,
980 fnd_api.g_miss_char,
981 NULL,
982 p_attribute5
983 ),
984 DECODE (
985 p_attribute6,
986 fnd_api.g_miss_char,
987 NULL,
988 p_attribute6
989 ),
990 DECODE (
991 p_attribute7,
992 fnd_api.g_miss_char,
993 NULL,
994 p_attribute7
995 ),
996 DECODE (
997 p_attribute8,
998 fnd_api.g_miss_char,
999 NULL,
1000 p_attribute8
1001 ),
1002 DECODE (
1003 p_attribute9,
1004 fnd_api.g_miss_char,
1005 NULL,
1006 p_attribute9
1007 ),
1008 DECODE (
1009 p_attribute10,
1010 fnd_api.g_miss_char,
1011 NULL,
1012 p_attribute10
1013 ),
1014 DECODE (
1015 p_attribute11,
1016 fnd_api.g_miss_char,
1017 NULL,
1018 p_attribute11
1019 ),
1020 DECODE (
1021 p_attribute12,
1022 fnd_api.g_miss_char,
1023 NULL,
1024 p_attribute12
1025 ),
1026 DECODE (
1027 p_attribute13,
1028 fnd_api.g_miss_char,
1029 NULL,
1030 p_attribute13
1031 ),
1032 DECODE (
1033 p_attribute14,
1034 fnd_api.g_miss_char,
1035 NULL,
1036 p_attribute14
1037 ),
1038 DECODE (
1039 p_attribute15,
1040 fnd_api.g_miss_char,
1041 NULL,
1042 p_attribute15
1043 ),
1044 DECODE (
1045 p_attribute16,
1046 fnd_api.g_miss_char,
1047 NULL,
1048 p_attribute16
1049 ),
1050 DECODE (
1051 p_attribute17,
1052 fnd_api.g_miss_char,
1053 NULL,
1054 p_attribute17
1055 ),
1056 DECODE (
1057 p_attribute18,
1058 fnd_api.g_miss_char,
1059 NULL,
1060 p_attribute18
1061 ),
1062 DECODE (
1063 p_attribute19,
1064 fnd_api.g_miss_char,
1065 NULL,
1066 p_attribute19
1067 ),
1068 DECODE (
1069 p_attribute20,
1070 fnd_api.g_miss_char,
1071 NULL,
1072 p_attribute20
1073 ),
1074 DECODE (
1075 p_context,
1076 fnd_api.g_miss_char,
1077 NULL,
1078 p_context
1079 ),
1080 DECODE (
1081 p_security_group_id,
1082 fnd_api.g_miss_num,
1083 NULL,
1084 p_security_group_id
1085 ),
1086 DECODE (
1087 p_object_version_number,
1088 fnd_api.g_miss_num,
1089 NULL,
1090 p_object_version_number
1091 ));
1092
1093 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1094 aso_debug_pub.ADD (
1095 'End Detail_Insert_Row procedure ',
1096 1,
1097 'N'
1098 );
1099 END IF;
1100 -- EXCEPTION
1101 -- WHEN OTHERS THEN
1102 -- aso_debug_pub.add('Exception in Detail_Insert_Row procedure ', 1, 'N');
1103 -- aso_debug_pub.add('errmsg is '||substr(SQLERRM,1,250), 1, 'N');
1104
1105 END detail_insert_row;
1106
1107 PROCEDURE detail_update_row (
1108 p_approval_det_id NUMBER,
1109 p_object_approval_id NUMBER,
1110 p_approver_person_id NUMBER,
1111 p_approver_user_id NUMBER,
1112 p_approver_sequence NUMBER,
1113 p_approver_status VARCHAR2,
1114 p_approver_comments VARCHAR2,
1115 p_date_sent DATE,
1116 p_date_received DATE,
1117 p_creation_date DATE,
1118 p_last_update_date DATE,
1119 p_created_by NUMBER,
1120 p_last_updated_by NUMBER,
1121 p_last_update_login NUMBER,
1122 p_attribute1 VARCHAR2,
1126 p_attribute5 VARCHAR2,
1123 p_attribute2 VARCHAR2,
1124 p_attribute3 VARCHAR2,
1125 p_attribute4 VARCHAR2,
1127 p_attribute6 VARCHAR2,
1128 p_attribute7 VARCHAR2,
1129 p_attribute8 VARCHAR2,
1130 p_attribute9 VARCHAR2,
1131 p_attribute10 VARCHAR2,
1132 p_attribute11 VARCHAR2,
1133 p_attribute12 VARCHAR2,
1134 p_attribute13 VARCHAR2,
1135 p_attribute14 VARCHAR2,
1136 p_attribute15 VARCHAR2,
1137 p_attribute16 VARCHAR2,
1138 p_attribute17 VARCHAR2,
1139 p_attribute18 VARCHAR2,
1140 p_attribute19 VARCHAR2,
1141 p_attribute20 VARCHAR2,
1142 p_context VARCHAR2,
1143 p_security_group_id NUMBER,
1144 p_object_version_number NUMBER
1145 ) IS
1146 BEGIN
1147 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1148 aso_debug_pub.ADD (
1149 'Begin Detail_Update_Row procedure ',
1150 1,
1151 'N'
1152 );
1153 END IF;
1154
1155 UPDATE aso_apr_approval_details
1156 SET approval_det_id = DECODE (
1157 p_approval_det_id,
1158 fnd_api.g_miss_num,
1159 approval_det_id,
1160 p_approval_det_id
1161 ),
1162 object_approval_id = DECODE (
1163 p_object_approval_id,
1164 fnd_api.g_miss_num,
1165 object_approval_id,
1166 p_object_approval_id
1167 ),
1168 approver_person_id = DECODE (
1169 p_approver_person_id,
1170 fnd_api.g_miss_num,
1171 approver_person_id,
1172 p_approver_person_id
1173 ),
1174 approver_user_id = DECODE (
1175 p_approver_user_id,
1176 fnd_api.g_miss_num,
1177 approver_user_id,
1178 p_approver_user_id
1179 ),
1180 approver_sequence = DECODE (
1181 p_approver_sequence,
1182 fnd_api.g_miss_num,
1183 approver_sequence,
1184 p_approver_sequence
1185 ),
1186 approver_status = DECODE (
1187 p_approver_status,
1188 fnd_api.g_miss_char,
1189 approver_status,
1190 p_approver_status
1191 ),
1192 approver_comments = DECODE (
1193 p_approver_comments,
1194 fnd_api.g_miss_char,
1195 approver_comments,
1196 p_approver_comments
1197 ),
1198 date_sent = aso_utility_pvt.DECODE (
1199 p_date_sent,
1200 fnd_api.g_miss_date,
1201 date_sent,
1202 p_date_sent
1203 ),
1204 date_received = aso_utility_pvt.DECODE (
1205 p_date_received,
1206 fnd_api.g_miss_date,
1207 date_received,
1208 p_date_received
1209 ),
1210 creation_date = aso_utility_pvt.DECODE (
1211 p_creation_date,
1212 fnd_api.g_miss_date,
1213 creation_date,
1214 p_creation_date
1215 ),
1216 last_update_date = aso_utility_pvt.DECODE (
1217 p_last_update_date,
1218 fnd_api.g_miss_date,
1219 last_update_date,
1220 p_last_update_date
1221 ),
1222 created_by = DECODE (
1223 p_created_by,
1224 fnd_api.g_miss_num,
1225 created_by,
1226 p_created_by
1227 ),
1228 last_updated_by = DECODE (
1229 p_last_updated_by,
1230 fnd_api.g_miss_num,
1231 last_updated_by,
1232 p_last_updated_by
1233 ),
1234 last_update_login = DECODE (
1235 p_last_update_login,
1236 fnd_api.g_miss_num,
1237 last_update_login,
1238 p_last_update_login
1239 ),
1243 attribute1,
1240 attribute1 = DECODE (
1241 p_attribute1,
1242 fnd_api.g_miss_char,
1244 p_attribute1
1245 ),
1246 attribute2 = DECODE (
1247 p_attribute2,
1248 fnd_api.g_miss_char,
1249 attribute2,
1250 p_attribute2
1251 ),
1252 attribute3 = DECODE (
1253 p_attribute3,
1254 fnd_api.g_miss_char,
1255 attribute3,
1256 p_attribute3
1257 ),
1258 attribute4 = DECODE (
1259 p_attribute4,
1260 fnd_api.g_miss_char,
1261 attribute4,
1262 p_attribute4
1263 ),
1264 attribute5 = DECODE (
1265 p_attribute5,
1266 fnd_api.g_miss_char,
1267 attribute5,
1268 p_attribute5
1269 ),
1270 attribute6 = DECODE (
1271 p_attribute6,
1272 fnd_api.g_miss_char,
1273 attribute6,
1274 p_attribute6
1275 ),
1276 attribute7 = DECODE (
1277 p_attribute7,
1278 fnd_api.g_miss_char,
1279 attribute7,
1280 p_attribute7
1281 ),
1282 attribute8 = DECODE (
1283 p_attribute8,
1284 fnd_api.g_miss_char,
1285 attribute8,
1286 p_attribute8
1287 ),
1288 attribute9 = DECODE (
1289 p_attribute9,
1290 fnd_api.g_miss_char,
1291 attribute9,
1292 p_attribute9
1293 ),
1294 attribute10 = DECODE (
1295 p_attribute10,
1296 fnd_api.g_miss_char,
1297 attribute10,
1298 p_attribute10
1299 ),
1300 attribute11 = DECODE (
1301 p_attribute11,
1302 fnd_api.g_miss_char,
1303 attribute11,
1304 p_attribute11
1305 ),
1306 attribute12 = DECODE (
1307 p_attribute12,
1308 fnd_api.g_miss_char,
1309 attribute12,
1310 p_attribute12
1311 ),
1312 attribute13 = DECODE (
1313 p_attribute13,
1314 fnd_api.g_miss_char,
1315 attribute13,
1316 p_attribute13
1317 ),
1318 attribute14 = DECODE (
1319 p_attribute14,
1320 fnd_api.g_miss_char,
1321 attribute14,
1322 p_attribute14
1323 ),
1324 attribute15 = DECODE (
1325 p_attribute15,
1326 fnd_api.g_miss_char,
1327 attribute15,
1328 p_attribute15
1329 ),
1330 attribute16 = DECODE (
1331 p_attribute16,
1332 fnd_api.g_miss_char,
1333 attribute16,
1334 p_attribute16
1335 ),
1336 attribute17 = DECODE (
1337 p_attribute17,
1338 fnd_api.g_miss_char,
1339 attribute17,
1340 p_attribute17
1341 ),
1342 attribute18 = DECODE (
1343 p_attribute18,
1344 fnd_api.g_miss_char,
1345 attribute18,
1346 p_attribute18
1347 ),
1348 attribute19 = DECODE (
1349 p_attribute19,
1350 fnd_api.g_miss_char,
1351 attribute19,
1352 p_attribute19
1353 ),
1354 attribute20 = DECODE (
1355 p_attribute20,
1356 fnd_api.g_miss_char,
1357 attribute20,
1358 p_attribute20
1359 ),
1360 CONTEXT = DECODE (
1361 p_context,
1362 fnd_api.g_miss_char,
1363 CONTEXT,
1364 p_context
1365 ),
1366 security_group_id = DECODE (
1367 p_security_group_id,
1368 fnd_api.g_miss_num,
1369 security_group_id,
1370 p_security_group_id
1371 ),
1372 object_version_number = DECODE (
1376 p_object_version_number
1373 p_object_version_number,
1374 fnd_api.g_miss_num,
1375 object_version_number,
1377 )
1378 WHERE approval_det_id = p_approval_det_id;
1379
1380 IF (SQL%NOTFOUND)
1381 THEN
1382 RAISE NO_DATA_FOUND;
1383 END IF;
1384
1385 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1386 aso_debug_pub.ADD (
1387 'End Detail_Update_Row procedure ',
1388 1,
1389 'N'
1390 );
1391 END IF;
1392 END detail_update_row;
1393
1394 PROCEDURE detail_lock_row (
1395 p_approval_det_id NUMBER,
1396 p_object_approval_id NUMBER,
1397 p_approver_person_id NUMBER,
1398 p_approver_user_id NUMBER,
1399 p_approver_sequence NUMBER,
1400 p_approver_status VARCHAR2,
1401 p_approver_comments VARCHAR2,
1402 p_date_sent DATE,
1403 p_date_received DATE,
1404 p_creation_date DATE,
1405 p_last_update_date DATE,
1406 p_created_by NUMBER,
1407 p_last_updated_by NUMBER,
1408 p_last_update_login NUMBER,
1409 p_attribute1 VARCHAR2,
1410 p_attribute2 VARCHAR2,
1411 p_attribute3 VARCHAR2,
1412 p_attribute4 VARCHAR2,
1413 p_attribute5 VARCHAR2,
1414 p_attribute6 VARCHAR2,
1415 p_attribute7 VARCHAR2,
1416 p_attribute8 VARCHAR2,
1417 p_attribute9 VARCHAR2,
1418 p_attribute10 VARCHAR2,
1419 p_attribute11 VARCHAR2,
1420 p_attribute12 VARCHAR2,
1421 p_attribute13 VARCHAR2,
1422 p_attribute14 VARCHAR2,
1423 p_attribute15 VARCHAR2,
1424 p_context VARCHAR2,
1425 p_security_group_id NUMBER,
1426 p_object_version_number NUMBER
1427 ) IS
1428 CURSOR c IS
1429 SELECT approval_det_id, object_approval_id, approver_person_id,
1430 approver_user_id, approver_sequence, approver_status,
1431 approver_comments, date_sent, date_received, creation_date,
1432 last_update_date, created_by, last_updated_by, last_update_login,
1433 attribute1, attribute2, attribute3, attribute4, attribute5, attribute6,
1434 attribute7, attribute8, attribute9, attribute10, attribute11,
1435 attribute12, attribute13, attribute14, attribute15, CONTEXT,
1436 security_group_id, object_version_number
1437 FROM aso_apr_approval_details
1438 WHERE approval_det_id = p_approval_det_id
1439 FOR UPDATE OF approval_det_id NOWAIT;
1440
1441 recinfo c%ROWTYPE;
1442 BEGIN
1443 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1444 aso_debug_pub.ADD (
1445 'Begin Detail_Lock_Row procedure ',
1446 1,
1447 'N'
1448 );
1449 END IF;
1450 OPEN c;
1451 FETCH c INTO recinfo;
1452
1453 IF (c%NOTFOUND)
1454 THEN
1455 CLOSE c;
1456 fnd_message.set_name (
1457 'FND',
1458 'FORM_RECORD_DELETED'
1459 );
1460 app_exception.raise_exception;
1461 END IF;
1462
1463 CLOSE c;
1464
1465 IF (((recinfo.last_update_date = p_last_update_date)
1466 OR ((recinfo.last_update_date IS NULL)
1467 AND (p_last_update_date IS NULL)
1468 )
1469 )
1470 )
1471 THEN
1472 RETURN;
1473 ELSE
1474 fnd_message.set_name (
1475 'FND',
1476 'FORM_RECORD_CHANGED'
1477 );
1478 app_exception.raise_exception;
1479 END IF;
1480
1481 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1482 aso_debug_pub.ADD (
1483 'End Detail_Lock_Row procedure ',
1484 1,
1485 'N'
1486 );
1487 END IF;
1488 END detail_lock_row;
1489
1490 PROCEDURE detail_delete_row (
1491 p_approval_det_id NUMBER
1492 ) IS
1493 BEGIN
1494 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1495 aso_debug_pub.ADD (
1496 'Begin Detail_Delete_Row procedure ',
1497 1,
1498 'N'
1499 );
1500 END IF;
1501
1502 DELETE FROM aso_apr_approval_details
1503 WHERE approval_det_id = p_approval_det_id;
1504
1505 IF (SQL%NOTFOUND)
1506 THEN
1507 RAISE NO_DATA_FOUND;
1508 END IF;
1509
1510 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1511 aso_debug_pub.ADD (
1512 'End Detail_Delete_Row procedure ',
1513 1,
1514 'N'
1515 );
1516 END IF;
1517 END detail_delete_row;
1518 -- Procedures for the Rule Table
1519
1523 p_rule_action_id NUMBER,
1520 PROCEDURE rule_insert_row (
1521 px_rule_id IN OUT NOCOPY /* file.sql.39 change */ NUMBER,
1522 p_oam_rule_id NUMBER,
1524 p_creation_date DATE,
1525 p_created_by NUMBER,
1526 p_last_update_date DATE,
1527 p_last_updated_by NUMBER,
1528 p_last_update_login NUMBER,
1529 p_object_approval_id NUMBER,
1530 p_attribute1 VARCHAR2,
1531 p_attribute2 VARCHAR2,
1532 p_attribute3 VARCHAR2,
1533 p_attribute4 VARCHAR2,
1534 p_attribute5 VARCHAR2,
1535 p_attribute6 VARCHAR2,
1536 p_attribute7 VARCHAR2,
1537 p_attribute8 VARCHAR2,
1538 p_attribute9 VARCHAR2,
1539 p_attribute10 VARCHAR2,
1540 p_attribute11 VARCHAR2,
1541 p_attribute12 VARCHAR2,
1542 p_attribute13 VARCHAR2,
1543 p_attribute14 VARCHAR2,
1544 p_attribute15 VARCHAR2,
1545 p_attribute16 VARCHAR2,
1546 p_attribute17 VARCHAR2,
1547 p_attribute18 VARCHAR2,
1548 p_attribute19 VARCHAR2,
1549 p_attribute20 VARCHAR2,
1550 p_context VARCHAR2,
1551 p_security_group_id NUMBER,
1552 p_object_version_number NUMBER
1553 ) IS
1554 CURSOR c2 IS
1555 SELECT aso_apr_rule_s.NEXTVAL
1556 FROM sys.DUAL;
1557 BEGIN
1558 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1559 aso_debug_pub.ADD (
1560 'Begin Rule_Insert_Row procedure ',
1561 1,
1562 'N'
1563 );
1564 END IF;
1565 --If (px_RULE_ID IS NULL) OR (px_RULE_ID = FND_API.G_MISS_NUM) then
1566 OPEN c2;
1567 FETCH c2 INTO px_rule_id;
1568 CLOSE c2;
1569 --End If;
1570 INSERT INTO aso_apr_rules
1571 (rule_id,
1572 oam_rule_id,
1573 rule_action_id,
1574 creation_date,
1575 created_by,
1576 last_update_date,
1577 last_updated_by,
1578 last_update_login,
1579 object_approval_id,
1580 attribute1,
1581 attribute2,
1582 attribute3,
1583 attribute4,
1584 attribute5,
1585 attribute6,
1586 attribute7,
1587 attribute8,
1588 attribute9,
1589 attribute10,
1590 attribute11,
1591 attribute12,
1592 attribute13,
1593 attribute14,
1594 attribute15,
1595 attribute16,
1596 attribute17,
1597 attribute18,
1598 attribute19,
1599 attribute20,
1600 CONTEXT,
1601 security_group_id,
1602 object_version_number)
1603 VALUES (px_rule_id,
1604 DECODE (
1605 p_oam_rule_id,
1606 fnd_api.g_miss_num,
1607 NULL,
1608 p_oam_rule_id
1609 ),
1610 DECODE (
1611 p_rule_action_id,
1612 fnd_api.g_miss_num,
1613 NULL,
1614 p_rule_action_id
1615 ),
1616 aso_utility_pvt.DECODE (
1617 p_creation_date,
1618 fnd_api.g_miss_date,
1619 NULL,
1620 p_creation_date
1621 ),
1622 DECODE (
1623 p_created_by,
1624 fnd_api.g_miss_num,
1625 NULL,
1626 p_created_by
1627 ),
1628 aso_utility_pvt.DECODE (
1629 p_last_update_date,
1630 fnd_api.g_miss_date,
1631 NULL,
1632 p_last_update_date
1633 ),
1634 DECODE (
1635 p_last_updated_by,
1636 fnd_api.g_miss_num,
1637 NULL,
1638 p_last_updated_by
1639 ),
1640 DECODE (
1641 p_last_update_login,
1642 fnd_api.g_miss_num,
1643 NULL,
1644 p_last_update_login
1645 ),
1646 DECODE (
1647 p_object_approval_id,
1648 fnd_api.g_miss_num,
1649 NULL,
1650 p_object_approval_id
1651 ),
1652 DECODE (
1653 p_attribute1,
1654 fnd_api.g_miss_char,
1655 NULL,
1656 p_attribute1
1657 ),
1658 DECODE (
1659 p_attribute2,
1660 fnd_api.g_miss_char,
1661 NULL,
1662 p_attribute2
1663 ),
1664 DECODE (
1665 p_attribute3,
1666 fnd_api.g_miss_char,
1667 NULL,
1668 p_attribute3
1669 ),
1670 DECODE (
1674 p_attribute4
1671 p_attribute4,
1672 fnd_api.g_miss_char,
1673 NULL,
1675 ),
1676 DECODE (
1677 p_attribute5,
1678 fnd_api.g_miss_char,
1679 NULL,
1680 p_attribute5
1681 ),
1682 DECODE (
1683 p_attribute6,
1684 fnd_api.g_miss_char,
1685 NULL,
1686 p_attribute6
1687 ),
1688 DECODE (
1689 p_attribute7,
1690 fnd_api.g_miss_char,
1691 NULL,
1692 p_attribute7
1693 ),
1694 DECODE (
1695 p_attribute8,
1696 fnd_api.g_miss_char,
1697 NULL,
1698 p_attribute8
1699 ),
1700 DECODE (
1701 p_attribute9,
1702 fnd_api.g_miss_char,
1703 NULL,
1704 p_attribute9
1705 ),
1706 DECODE (
1707 p_attribute10,
1708 fnd_api.g_miss_char,
1709 NULL,
1710 p_attribute10
1711 ),
1712 DECODE (
1713 p_attribute11,
1714 fnd_api.g_miss_char,
1715 NULL,
1716 p_attribute11
1717 ),
1718 DECODE (
1719 p_attribute12,
1720 fnd_api.g_miss_char,
1721 NULL,
1722 p_attribute12
1723 ),
1724 DECODE (
1725 p_attribute13,
1726 fnd_api.g_miss_char,
1727 NULL,
1728 p_attribute13
1729 ),
1730 DECODE (
1731 p_attribute14,
1732 fnd_api.g_miss_char,
1733 NULL,
1734 p_attribute14
1735 ),
1736 DECODE (
1737 p_attribute15,
1738 fnd_api.g_miss_char,
1739 NULL,
1740 p_attribute15
1741 ),
1742 DECODE (
1743 p_attribute16,
1744 fnd_api.g_miss_char,
1745 NULL,
1746 p_attribute16
1747 ),
1748 DECODE (
1749 p_attribute17,
1750 fnd_api.g_miss_char,
1751 NULL,
1752 p_attribute17
1753 ),
1754 DECODE (
1755 p_attribute18,
1756 fnd_api.g_miss_char,
1757 NULL,
1758 p_attribute18
1759 ),
1760 DECODE (
1761 p_attribute19,
1762 fnd_api.g_miss_char,
1763 NULL,
1764 p_attribute19
1765 ),
1766 DECODE (
1767 p_attribute20,
1768 fnd_api.g_miss_char,
1769 NULL,
1770 p_attribute20
1771 ),
1772 DECODE (
1773 p_context,
1774 fnd_api.g_miss_char,
1775 NULL,
1776 p_context
1777 ),
1778 DECODE (
1779 p_security_group_id,
1780 fnd_api.g_miss_num,
1781 NULL,
1782 p_security_group_id
1783 ),
1784 DECODE (
1785 p_object_version_number,
1786 fnd_api.g_miss_num,
1787 NULL,
1788 p_object_version_number
1789 ));
1790
1791 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1792 aso_debug_pub.ADD (
1793 'End Rule_Insert_Row procedure ',
1794 1,
1795 'N'
1796 );
1797 END IF;
1798 END rule_insert_row;
1799
1800 PROCEDURE rule_update_row (
1801 p_rule_id NUMBER,
1802 p_oam_rule_id NUMBER,
1803 p_rule_action_id NUMBER,
1804 p_creation_date DATE,
1805 p_created_by NUMBER,
1806 p_last_update_date DATE,
1807 p_last_updated_by NUMBER,
1808 p_last_update_login NUMBER,
1809 p_object_approval_id NUMBER,
1810 p_attribute1 VARCHAR2,
1811 p_attribute2 VARCHAR2,
1812 p_attribute3 VARCHAR2,
1813 p_attribute4 VARCHAR2,
1814 p_attribute5 VARCHAR2,
1815 p_attribute6 VARCHAR2,
1816 p_attribute7 VARCHAR2,
1817 p_attribute8 VARCHAR2,
1818 p_attribute9 VARCHAR2,
1819 p_attribute10 VARCHAR2,
1820 p_attribute11 VARCHAR2,
1821 p_attribute12 VARCHAR2,
1822 p_attribute13 VARCHAR2,
1823 p_attribute14 VARCHAR2,
1827 p_attribute18 VARCHAR2,
1824 p_attribute15 VARCHAR2,
1825 p_attribute16 VARCHAR2,
1826 p_attribute17 VARCHAR2,
1828 p_attribute19 VARCHAR2,
1829 p_attribute20 VARCHAR2,
1830 p_context VARCHAR2,
1831 p_security_group_id NUMBER,
1832 p_object_version_number NUMBER
1833 ) IS
1834 BEGIN
1835 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1836 aso_debug_pub.ADD (
1837 'Begin Rule_Update_Row procedure ',
1838 1,
1839 'N'
1840 );
1841 END IF;
1842
1843 UPDATE aso_apr_rules
1844 SET rule_id = p_rule_id,
1845 oam_rule_id = DECODE (
1846 p_oam_rule_id,
1847 fnd_api.g_miss_num,
1848 rule_id,
1849 p_oam_rule_id
1850 ),
1851 rule_action_id = DECODE (
1852 p_rule_action_id,
1853 fnd_api.g_miss_num,
1854 rule_action_id,
1855 p_rule_action_id
1856 ),
1857 creation_date = aso_utility_pvt.DECODE (
1858 p_creation_date,
1859 fnd_api.g_miss_date,
1860 creation_date,
1861 p_creation_date
1862 ),
1863 created_by = DECODE (
1864 p_created_by,
1865 fnd_api.g_miss_num,
1866 created_by,
1867 p_created_by
1868 ),
1869 last_update_date = aso_utility_pvt.DECODE (
1870 p_last_update_date,
1871 fnd_api.g_miss_date,
1872 last_update_date,
1873 p_last_update_date
1874 ),
1875 last_updated_by = DECODE (
1876 p_last_updated_by,
1877 fnd_api.g_miss_num,
1878 NULL,
1879 p_last_updated_by
1880 ),
1881 last_update_login = DECODE (
1882 p_last_update_login,
1883 fnd_api.g_miss_num,
1884 last_update_login,
1885 p_last_update_login
1886 ),
1887 object_approval_id = DECODE (
1888 p_object_approval_id,
1889 fnd_api.g_miss_num,
1890 object_approval_id,
1891 p_object_approval_id
1892 ),
1893 attribute1 = DECODE (
1894 p_attribute1,
1895 fnd_api.g_miss_char,
1896 attribute1,
1897 p_attribute1
1898 ),
1899 attribute2 = DECODE (
1900 p_attribute2,
1901 fnd_api.g_miss_char,
1902 attribute2,
1903 p_attribute2
1904 ),
1905 attribute3 = DECODE (
1906 p_attribute3,
1907 fnd_api.g_miss_char,
1908 attribute3,
1909 p_attribute3
1910 ),
1911 attribute4 = DECODE (
1912 p_attribute4,
1913 fnd_api.g_miss_char,
1914 attribute4,
1915 p_attribute4
1916 ),
1917 attribute5 = DECODE (
1918 p_attribute5,
1919 fnd_api.g_miss_char,
1920 attribute5,
1921 p_attribute5
1922 ),
1923 attribute6 = DECODE (
1924 p_attribute6,
1925 fnd_api.g_miss_char,
1926 attribute6,
1927 p_attribute6
1928 ),
1929 attribute7 = DECODE (
1930 p_attribute7,
1931 fnd_api.g_miss_char,
1932 attribute7,
1933 p_attribute7
1934 ),
1935 attribute8 = DECODE (
1936 p_attribute8,
1937 fnd_api.g_miss_char,
1938 attribute8,
1939 p_attribute8
1940 ),
1941 attribute9 = DECODE (
1942 p_attribute9,
1943 fnd_api.g_miss_char,
1944 attribute9,
1945 p_attribute9
1946 ),
1947 attribute10 = DECODE (
1948 p_attribute10,
1949 fnd_api.g_miss_char,
1950 attribute10,
1951 p_attribute10
1955 fnd_api.g_miss_char,
1952 ),
1953 attribute11 = DECODE (
1954 p_attribute11,
1956 attribute11,
1957 p_attribute11
1958 ),
1959 attribute12 = DECODE (
1960 p_attribute12,
1961 fnd_api.g_miss_char,
1962 attribute12,
1963 p_attribute12
1964 ),
1965 attribute13 = DECODE (
1966 p_attribute13,
1967 fnd_api.g_miss_char,
1968 attribute13,
1969 p_attribute13
1970 ),
1971 attribute14 = DECODE (
1972 p_attribute14,
1973 fnd_api.g_miss_char,
1974 attribute14,
1975 p_attribute14
1976 ),
1977 attribute15 = DECODE (
1978 p_attribute15,
1979 fnd_api.g_miss_char,
1980 attribute15,
1981 p_attribute15
1982 ),
1983 attribute16 = DECODE (
1984 p_attribute16,
1985 fnd_api.g_miss_char,
1986 attribute16,
1987 p_attribute16
1988 ),
1989 attribute17 = DECODE (
1990 p_attribute17,
1991 fnd_api.g_miss_char,
1992 attribute17,
1993 p_attribute17
1994 ),
1995 attribute18 = DECODE (
1996 p_attribute18,
1997 fnd_api.g_miss_char,
1998 attribute18,
1999 p_attribute18
2000 ),
2001 attribute19 = DECODE (
2002 p_attribute19,
2003 fnd_api.g_miss_char,
2004 attribute19,
2005 p_attribute19
2006 ),
2007 attribute20 = DECODE (
2008 p_attribute20,
2009 fnd_api.g_miss_char,
2010 attribute20,
2011 p_attribute20
2012 ),
2013 CONTEXT = DECODE (
2014 p_context,
2015 fnd_api.g_miss_char,
2016 CONTEXT,
2017 p_context
2018 ),
2019 security_group_id = DECODE (
2020 p_security_group_id,
2021 fnd_api.g_miss_num,
2022 security_group_id,
2023 p_security_group_id
2024 ),
2025 object_version_number = DECODE (
2026 p_object_version_number,
2027 fnd_api.g_miss_num,
2028 object_version_number,
2029 p_object_version_number
2030 )
2031 WHERE rule_id = p_rule_id;
2032
2033 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2034 aso_debug_pub.ADD (
2035 'End Rule_Update_Row procedure ',
2036 1,
2037 'N'
2038 );
2039 END IF;
2040 END rule_update_row;
2041
2042 PROCEDURE rule_lock_row (
2043 p_rule_id NUMBER,
2044 p_oam_rule_id NUMBER,
2045 p_rule_action_id NUMBER,
2046 p_creation_date DATE,
2047 p_created_by NUMBER,
2048 p_last_update_date DATE,
2049 p_last_updated_by NUMBER,
2050 p_last_update_login NUMBER,
2051 p_object_approval_id NUMBER,
2052 p_attribute1 VARCHAR2,
2053 p_attribute2 VARCHAR2,
2054 p_attribute3 VARCHAR2,
2055 p_attribute4 VARCHAR2,
2056 p_attribute5 VARCHAR2,
2057 p_attribute6 VARCHAR2,
2058 p_attribute7 VARCHAR2,
2059 p_attribute8 VARCHAR2,
2060 p_attribute9 VARCHAR2,
2061 p_attribute10 VARCHAR2,
2062 p_attribute11 VARCHAR2,
2063 p_attribute12 VARCHAR2,
2064 p_attribute13 VARCHAR2,
2065 p_attribute14 VARCHAR2,
2066 p_attribute15 VARCHAR2,
2070 ) IS
2067 p_context VARCHAR2,
2068 p_security_group_id NUMBER,
2069 p_object_version_number NUMBER
2071 CURSOR c IS
2072 SELECT rule_id, oam_rule_id, rule_action_id, creation_date, created_by,
2073 last_update_date, last_updated_by, last_update_login,
2074 object_approval_id, attribute1, attribute2, attribute3, attribute4,
2075 attribute5, attribute6, attribute7, attribute8, attribute9,
2076 attribute10, attribute11, attribute12, attribute13, attribute14,
2077 attribute15, CONTEXT, security_group_id, object_version_number
2078 FROM aso_apr_rules
2079 WHERE rule_id = p_rule_id
2080 FOR UPDATE OF rule_id NOWAIT;
2081
2082 recinfo c%ROWTYPE;
2083 BEGIN
2084 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2085 aso_debug_pub.ADD (
2086 'Begin Rule_Lock_Row procedure ',
2087 1,
2088 'N'
2089 );
2090 END IF;
2091 OPEN c;
2092 FETCH c INTO recinfo;
2093
2094 IF (c%NOTFOUND)
2095 THEN
2096 CLOSE c;
2097 fnd_message.set_name (
2098 'FND',
2099 'FORM_RECORD_DELETED'
2100 );
2101 app_exception.raise_exception;
2102 END IF;
2103
2104 CLOSE c;
2105
2106 IF (((recinfo.last_update_date = p_last_update_date)
2107 OR ((recinfo.last_update_date IS NULL)
2108 AND (p_last_update_date IS NULL)
2109 )
2110 )
2111 )
2112 THEN
2113 RETURN;
2114 ELSE
2115 fnd_message.set_name (
2116 'FND',
2117 'FORM_RECORD_CHANGED'
2118 );
2119 app_exception.raise_exception;
2120 END IF;
2121
2122 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2123 aso_debug_pub.ADD (
2124 'End Rule_Lock_Row procedure ',
2125 1,
2126 'N'
2127 );
2128 END IF;
2129 END rule_lock_row;
2130
2131 PROCEDURE rule_delete_row (
2132 p_rule_id NUMBER
2133 ) IS
2134 BEGIN
2135 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2136 aso_debug_pub.ADD (
2137 'Begin Rule_Delete_Row procedure ',
2138 1,
2139 'N'
2140 );
2141 END IF;
2142
2143 DELETE FROM aso_apr_rules
2144 WHERE rule_id = p_rule_id;
2145
2146 IF (SQL%NOTFOUND)
2147 THEN
2148 RAISE NO_DATA_FOUND;
2149 END IF;
2150
2151 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2152 aso_debug_pub.ADD (
2153 'End Rule_Delete_Row procedure ',
2154 1,
2155 'N'
2156 );
2157 END IF;
2158 END rule_delete_row;
2159 END aso_apr_approvals_pkg;