eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.AHL_APPROVAL_RULES_PKG

Source


1 PACKAGE BODY AHL_APPROVAL_RULES_PKG as
2 /*$Header: AHLLAPRB.pls 115.10 2003/12/01 06:18:24 rroy noship $*/
3 
4 procedure INSERT_ROW
5 
6 (
7   X_ROWID  		     IN OUT NOCOPY   VARCHAR2,
8   X_APPROVAL_RULE_ID         IN        NUMBER,
9   X_OBJECT_VERSION_NUMBER    IN        NUMBER,
10   X_APPROVAL_OBJECT_CODE     IN        VARCHAR2,
11   X_APPROVAL_PRIORITY_CODE   IN        VARCHAR2,
12   X_APPROVAL_TYPE_CODE       IN        VARCHAR2,
13   X_OPERATING_UNIT_ID        IN        NUMBER,
14   X_ACTIVE_START_DATE        IN        DATE,
15   X_ACTIVE_END_DATE          IN        DATE,
16   X_STATUS_CODE              IN        VARCHAR2,
17   X_SEEDED_FLAG              IN        VARCHAR2,
18   X_ATTRIBUTE_CATEGORY       IN        VARCHAR2,
19   X_ATTRIBUTE1               IN        VARCHAR2,
20   X_ATTRIBUTE2               IN        VARCHAR2,
21   X_ATTRIBUTE3               IN        VARCHAR2,
22   X_ATTRIBUTE4               IN        VARCHAR2,
23   X_ATTRIBUTE5               IN        VARCHAR2,
24   X_ATTRIBUTE6               IN        VARCHAR2,
25   X_ATTRIBUTE7               IN        VARCHAR2,
26   X_ATTRIBUTE8               IN        VARCHAR2,
27   X_ATTRIBUTE9               IN        VARCHAR2,
28   X_ATTRIBUTE10              IN        VARCHAR2,
29   X_ATTRIBUTE11              IN        VARCHAR2,
30   X_ATTRIBUTE12              IN        VARCHAR2,
31   X_ATTRIBUTE13              IN        VARCHAR2,
32   X_ATTRIBUTE14              IN        VARCHAR2,
33   X_ATTRIBUTE15              IN        VARCHAR2,
34   X_APPROVAL_RULE_NAME       IN        VARCHAR2,
35   X_DESCRIPTION              IN        VARCHAR2,
36   X_CREATION_DATE            IN        DATE,
37   X_CREATED_BY 		     IN        NUMBER,
38   X_LAST_UPDATE_DATE         IN        DATE,
39   X_LAST_UPDATED_BY          IN        NUMBER,
40   X_LAST_UPDATE_LOGIN        IN        NUMBER,
41   X_APPLICATION_USG_CODE     IN        VARCHAR2
42 
43 )
44 
45 is
46 
47 cursor C is Select ROWID from AHL_APPROVAL_RULES_B where
48 
49             APPROVAL_RULE_ID = X_APPROVAL_RULE_ID ;
50 
51 begin
52 
53 insert into AHL_APPROVAL_RULES_B
54 
55 (
56   APPROVAL_RULE_ID,
57   OBJECT_VERSION_NUMBER,
58   APPROVAL_OBJECT_CODE,
59   APPROVAL_PRIORITY_CODE,
60   APPROVAL_TYPE_CODE,
61   APPLICATION_USG_CODE,
62   OPERATING_UNIT_ID,
63   ACTIVE_START_DATE,
64   ACTIVE_END_DATE,
65   STATUS_CODE,
66   SEEDED_FLAG,
67   ATTRIBUTE_CATEGORY,
68   ATTRIBUTE1,
69   ATTRIBUTE2,
70   ATTRIBUTE3,
71   ATTRIBUTE4,
72   ATTRIBUTE5,
73   ATTRIBUTE6,
74   ATTRIBUTE7,
75   ATTRIBUTE8,
76   ATTRIBUTE9,
77   ATTRIBUTE10,
78   ATTRIBUTE11,
79   ATTRIBUTE12,
80   ATTRIBUTE13,
81   ATTRIBUTE14,
82   ATTRIBUTE15,
83   CREATION_DATE,
84   CREATED_BY,
85   LAST_UPDATE_DATE,
86   LAST_UPDATED_BY,
87   LAST_UPDATE_LOGIN
88 )
89 
90  values
91 
92 (
93   X_APPROVAL_RULE_ID,
94   X_OBJECT_VERSION_NUMBER,
95   X_APPROVAL_OBJECT_CODE,
96   X_APPROVAL_PRIORITY_CODE,
97   X_APPROVAL_TYPE_CODE,
98   X_APPLICATION_USG_CODE,
99   X_OPERATING_UNIT_ID,
100   X_ACTIVE_START_DATE,
101   X_ACTIVE_END_DATE,
102   X_STATUS_CODE,
103   X_SEEDED_FLAG,
104   X_ATTRIBUTE_CATEGORY,
105   X_ATTRIBUTE1,
106   X_ATTRIBUTE2,
107   X_ATTRIBUTE3,
108   X_ATTRIBUTE4,
109   X_ATTRIBUTE5,
110   X_ATTRIBUTE6,
111   X_ATTRIBUTE7,
112   X_ATTRIBUTE8,
113   X_ATTRIBUTE9,
114   X_ATTRIBUTE10,
115   X_ATTRIBUTE11,
116   X_ATTRIBUTE12,
117   X_ATTRIBUTE13,
118   X_ATTRIBUTE14,
119   X_ATTRIBUTE15,
120   X_CREATION_DATE,
121   X_CREATED_BY,
122   X_LAST_UPDATE_DATE,
123   X_LAST_UPDATED_BY,
124   X_LAST_UPDATE_LOGIN
125 );
126 
127 
128 
129 insert into AHL_APPROVAL_RULES_TL
130 
131 (
132     APPROVAL_RULE_NAME,
133     DESCRIPTION,
134     APPROVAL_RULE_ID,
135     LAST_UPDATE_DATE,
136     LAST_UPDATED_BY,
137     CREATION_DATE,
138     CREATED_BY,
139     LAST_UPDATE_LOGIN,
140     LANGUAGE,
141     SOURCE_LANG
142   ) select
143     X_APPROVAL_RULE_NAME,
144     X_DESCRIPTION,
145     X_APPROVAL_RULE_ID,
146     X_LAST_UPDATE_DATE,
147     X_LAST_UPDATED_BY,
148     X_CREATION_DATE,
149     X_CREATED_BY,
150     X_LAST_UPDATE_LOGIN,
151     L.LANGUAGE_CODE,
152     userenv('LANG')
153   from FND_LANGUAGES L
154 
155   where L.INSTALLED_FLAG in ('I', 'B')
156 
157   and not exists
158 
159     (select NULL
160     from AHL_APPROVAL_RULES_TL T
161     where T.APPROVAL_RULE_ID = X_APPROVAL_RULE_ID
162     and T.LANGUAGE = L.LANGUAGE_CODE);
163 
164 
165 
166  open c;
167 
168  fetch c into X_ROWID;
169 
170  if (c%notfound) then
171 
172     close c;
173 
174  raise no_data_found;
175 
176  end if;
177 
178  close c;
179 
180 end INSERT_ROW;
181 
182 
183 
184 procedure LOCK_ROW (
185 
186   X_APPROVAL_RULE_ID in NUMBER,
187 
188   X_OBJECT_VERSION_NUMBER in NUMBER,
189 
190   X_APPROVAL_OBJECT_CODE in VARCHAR2,
191 
192   X_APPROVAL_PRIORITY_CODE in VARCHAR2,
193 
194   X_APPROVAL_TYPE_CODE in VARCHAR2,
195 
196   X_OPERATING_UNIT_ID in NUMBER,
197 
198   X_ACTIVE_START_DATE in DATE,
199 
200   X_ACTIVE_END_DATE in DATE,
201 
202   X_STATUS_CODE in VARCHAR2,
203 
204   X_SEEDED_FLAG in VARCHAR2,
205 
206 --  X_SECURITY_GROUP_ID in NUMBER,
207 
208   X_ATTRIBUTE_CATEGORY in VARCHAR2,
209 
210   X_ATTRIBUTE1 in VARCHAR2,
211 
212   X_ATTRIBUTE2 in VARCHAR2,
213 
214   X_ATTRIBUTE3 in VARCHAR2,
215 
216   X_ATTRIBUTE4 in VARCHAR2,
217 
218   X_ATTRIBUTE5 in VARCHAR2,
219 
220   X_ATTRIBUTE6 in VARCHAR2,
221 
222   X_ATTRIBUTE7 in VARCHAR2,
223 
224   X_ATTRIBUTE8 in VARCHAR2,
225 
226   X_ATTRIBUTE9 in VARCHAR2,
227 
228   X_ATTRIBUTE10 in VARCHAR2,
229 
230   X_ATTRIBUTE11 in VARCHAR2,
231 
232   X_ATTRIBUTE12 in VARCHAR2,
233 
234   X_ATTRIBUTE13 in VARCHAR2,
235 
236   X_ATTRIBUTE14 in VARCHAR2,
237 
238   X_ATTRIBUTE15 in VARCHAR2,
239 
240   X_APPROVAL_RULE_NAME in VARCHAR2,
241 
242   X_DESCRIPTION in VARCHAR2,
243   X_APPLICATION_USG_CODE     IN        VARCHAR2
244 
245   ) is
246 
247   cursor c is select
248 
252 
249       OBJECT_VERSION_NUMBER,
250 
251       APPROVAL_OBJECT_CODE,
253       APPROVAL_PRIORITY_CODE,
254 
255       APPROVAL_TYPE_CODE,
256 
257       APPLICATION_USG_CODE,
258 
259       OPERATING_UNIT_ID,
260 
261       ACTIVE_START_DATE,
262 
263       ACTIVE_END_DATE,
264 
265       STATUS_CODE,
266 
267       SEEDED_FLAG,
268 
269 --      SECURITY_GROUP_ID,
270 
271       ATTRIBUTE_CATEGORY,
272 
273       ATTRIBUTE1,
274 
275       ATTRIBUTE2,
276 
277       ATTRIBUTE3,
278 
279       ATTRIBUTE4,
280 
281       ATTRIBUTE5,
282 
283       ATTRIBUTE6,
284 
285       ATTRIBUTE7,
286 
287       ATTRIBUTE8,
288 
289       ATTRIBUTE9,
290 
291       ATTRIBUTE10,
292 
293       ATTRIBUTE11,
294 
295       ATTRIBUTE12,
296 
297       ATTRIBUTE13,
298 
299       ATTRIBUTE14,
300 
301       ATTRIBUTE15
302 
303     from AHL_APPROVAL_RULES_B
304 
305     where APPROVAL_RULE_ID = X_APPROVAL_RULE_ID
306 
307     for update of APPROVAL_RULE_ID nowait;
308 
309   recinfo c%rowtype;
310 
311   cursor c1 is select
312 
313       APPROVAL_RULE_NAME,
314 
315       DESCRIPTION,
316 
317       decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
318 
319     from AHL_APPROVAL_RULES_TL
320 
321     where APPROVAL_RULE_ID = X_APPROVAL_RULE_ID
322 
323     and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
324 
325     for update of APPROVAL_RULE_ID nowait;
326 
327 begin
328 
329   open c;
330 
334 
331   fetch c into recinfo;
332 
333   if (c%notfound) then
335     close c;
336 
337     fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
338 
339     app_exception.raise_exception;
340 
341   end if;
342 
343   close c;
344 
345   if (    (recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
346 
347       AND (recinfo.APPROVAL_OBJECT_CODE = X_APPROVAL_OBJECT_CODE)
348 
349       AND (recinfo.APPROVAL_PRIORITY_CODE = X_APPROVAL_PRIORITY_CODE)
350 
351       AND (recinfo.APPROVAL_TYPE_CODE = X_APPROVAL_TYPE_CODE)
352 
353       AND (recinfo.APPLICATION_USG_CODE = X_APPLICATION_USG_CODE)
354 
355       AND (recinfo.OPERATING_UNIT_ID = X_OPERATING_UNIT_ID)
356 
357       AND (recinfo.ACTIVE_START_DATE = X_ACTIVE_START_DATE)
358 
359       AND ((recinfo.ACTIVE_END_DATE = X_ACTIVE_END_DATE)
360 
361       OR ((recinfo.ACTIVE_END_DATE is null) AND (X_ACTIVE_END_DATE is null)))
362 
363       AND (recinfo.STATUS_CODE = X_STATUS_CODE)
364 
365       AND (recinfo.SEEDED_FLAG = X_SEEDED_FLAG)
366 
367       /*AND ((recinfo.SECURITY_GROUP_ID = X_SECURITY_GROUP_ID)
368 
369            OR ((recinfo.SECURITY_GROUP_ID is null) AND (X_SECURITY_GROUP_ID is null)))*/
370 
371       AND ((recinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
372 
373            OR ((recinfo.ATTRIBUTE_CATEGORY is null) AND (X_ATTRIBUTE_CATEGORY is null)))
374 
375       AND ((recinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
376 
377            OR ((recinfo.ATTRIBUTE1 is null) AND (X_ATTRIBUTE1 is null)))
378 
379       AND ((recinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
380 
381            OR ((recinfo.ATTRIBUTE2 is null) AND (X_ATTRIBUTE2 is null)))
382 
383       AND ((recinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
384 
385       OR ((recinfo.ATTRIBUTE3 is null) AND (X_ATTRIBUTE3 is null)))
386 
387       AND ((recinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
388 
389            OR ((recinfo.ATTRIBUTE4 is null) AND (X_ATTRIBUTE4 is null)))
390 
391       AND ((recinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
392 
393            OR ((recinfo.ATTRIBUTE5 is null) AND (X_ATTRIBUTE5 is null)))
394 
395       AND ((recinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
396 
397            OR ((recinfo.ATTRIBUTE6 is null) AND (X_ATTRIBUTE6 is null)))
398 
399       AND ((recinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
400 
401            OR ((recinfo.ATTRIBUTE7 is null) AND (X_ATTRIBUTE7 is null)))
402 
403       AND ((recinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
404 
405            OR ((recinfo.ATTRIBUTE8 is null) AND (X_ATTRIBUTE8 is null)))
406 
407       AND ((recinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
408 
409            OR ((recinfo.ATTRIBUTE9 is null) AND (X_ATTRIBUTE9 is null)))
410 
411       AND ((recinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
412 
413            OR ((recinfo.ATTRIBUTE10 is null) AND (X_ATTRIBUTE10 is null)))
414 
415       AND ((recinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
416 
417            OR ((recinfo.ATTRIBUTE11 is null) AND (X_ATTRIBUTE11 is null)))
418 
419       AND ((recinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
420 
421            OR ((recinfo.ATTRIBUTE12 is null) AND (X_ATTRIBUTE12 is null)))
422 
423       AND ((recinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
424 
425            OR ((recinfo.ATTRIBUTE13 is null) AND (X_ATTRIBUTE13 is null)))
426 
427       AND ((recinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
428 
429            OR ((recinfo.ATTRIBUTE14 is null) AND (X_ATTRIBUTE14 is null)))
430 
431       AND ((recinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
432 
433            OR ((recinfo.ATTRIBUTE15 is null) AND (X_ATTRIBUTE15 is null)))
434 
435   ) then
436 
437     null;
438 
439   else
440 
441     fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
442 
443     app_exception.raise_exception;
444 
445   end if;
446 
447   for tlinfo in c1 loop
448 
449     if (tlinfo.BASELANG = 'Y') then
450 
451       if (    (tlinfo.APPROVAL_RULE_NAME = X_APPROVAL_RULE_NAME)
452 
453           AND ((tlinfo.DESCRIPTION = X_DESCRIPTION)
454 
455                OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
456 
457       ) then
458 
459         null;
460 
461       else
462 
463         fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
464 
465         app_exception.raise_exception;
466 
467       end if;
468 
469     end if;
470 
471   end loop;
472 
473   return;
474 
475 end LOCK_ROW;
476 
477 
478 
479 procedure UPDATE_ROW
480 
481 (
482 
483   X_APPROVAL_RULE_ID in NUMBER,
484 
485   X_OBJECT_VERSION_NUMBER in NUMBER,
486 
487   X_APPROVAL_OBJECT_CODE in VARCHAR2,
488 
489   X_APPROVAL_PRIORITY_CODE in VARCHAR2,
490 
491   X_APPROVAL_TYPE_CODE in VARCHAR2,
492 
493   X_OPERATING_UNIT_ID in NUMBER,
494 
495   X_ACTIVE_START_DATE in DATE,
496 
500 
497   X_ACTIVE_END_DATE in DATE,
498 
499   X_STATUS_CODE in VARCHAR2,
501   X_SEEDED_FLAG in VARCHAR2,
502 
503 --  X_SECURITY_GROUP_ID in NUMBER,
504 
505   X_ATTRIBUTE_CATEGORY in VARCHAR2,
506 
507   X_ATTRIBUTE1 IN VARCHAR2,
508 
509   X_ATTRIBUTE2 IN VARCHAR2,
510 
511   X_ATTRIBUTE3 IN VARCHAR2,
512 
513   X_ATTRIBUTE4 IN VARCHAR2,
514 
515   X_ATTRIBUTE5 IN VARCHAR2,
516 
517   X_ATTRIBUTE6 IN VARCHAR2,
518 
519   X_ATTRIBUTE7 IN VARCHAR2,
520 
521   X_ATTRIBUTE8 IN VARCHAR2,
522 
523   X_ATTRIBUTE9 IN VARCHAR2,
524 
525   X_ATTRIBUTE10 IN VARCHAR2,
526 
527   X_ATTRIBUTE11 IN VARCHAR2,
528 
529   X_ATTRIBUTE12 IN VARCHAR2,
530 
531   X_ATTRIBUTE13 IN VARCHAR2,
532 
533   X_ATTRIBUTE14 IN VARCHAR2,
534 
535   X_ATTRIBUTE15 IN VARCHAR2,
536 
537   X_APPROVAL_RULE_NAME in VARCHAR2,
538 
539   X_DESCRIPTION in VARCHAR2,
540 
541   X_LAST_UPDATE_DATE in DATE,
542 
543   X_LAST_UPDATED_BY in NUMBER,
544 
545   X_LAST_UPDATE_LOGIN in NUMBER,
546 
547   X_APPLICATION_USG_CODE IN VARCHAR2
548 
549 ) is
550 
551 begin
552 
553 
554 
555 update AHL_APPROVAL_RULES_B set
556 
557     OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
558 
559     APPROVAL_OBJECT_CODE = X_APPROVAL_OBJECT_CODE,
560 
561     APPROVAL_PRIORITY_CODE = X_APPROVAL_PRIORITY_CODE,
562 
563     APPROVAL_TYPE_CODE = X_APPROVAL_TYPE_CODE,
564 
565     APPLICATION_USG_CODE = X_APPLICATION_USG_CODE,
566 
567     OPERATING_UNIT_ID = X_OPERATING_UNIT_ID,
568 
569     ACTIVE_START_DATE = X_ACTIVE_START_DATE,
570 
571     ACTIVE_END_DATE = X_ACTIVE_END_DATE,
572 
573     STATUS_CODE = X_STATUS_CODE,
574 
575     SEEDED_FLAG = X_SEEDED_FLAG,
576 
577 --    SECURITY_GROUP_ID = X_SECURITY_GROUP_ID,
578 
579     ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
580 
581     ATTRIBUTE1 =  X_ATTRIBUTE1,
582 
583     ATTRIBUTE2 =  X_ATTRIBUTE2,
584 
585     ATTRIBUTE3 =  X_ATTRIBUTE3,
586 
587     ATTRIBUTE4 =  X_ATTRIBUTE4,
588 
589     ATTRIBUTE5 =  X_ATTRIBUTE5,
590 
591     ATTRIBUTE6 =  X_ATTRIBUTE6,
592 
593     ATTRIBUTE7 =  X_ATTRIBUTE7,
594 
595     ATTRIBUTE8 =  X_ATTRIBUTE8,
596 
597     ATTRIBUTE9 =  X_ATTRIBUTE9,
598 
599     ATTRIBUTE10 =  X_ATTRIBUTE10,
600 
601     ATTRIBUTE11 =  X_ATTRIBUTE11,
602 
603     ATTRIBUTE12 =  X_ATTRIBUTE12,
604 
605     ATTRIBUTE13 =  X_ATTRIBUTE13,
606 
607     ATTRIBUTE14 =  X_ATTRIBUTE14,
608 
609     ATTRIBUTE15 =  X_ATTRIBUTE15,
610 
611     LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
612 
613     LAST_UPDATED_BY = X_LAST_UPDATED_BY,
614 
615     LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
616 
617   where APPROVAL_RULE_ID = X_APPROVAL_RULE_ID
618 
619   and OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER-1;
620 
621 
622 
623 if (sql%notfound) then
624 
625   raise no_data_found;
626 
627 end if;
628 
629 
630 
631  update AHL_APPROVAL_RULES_TL set
632 
633     APPROVAL_RULE_NAME = X_APPROVAL_RULE_NAME,
634 
635     DESCRIPTION = X_DESCRIPTION,
636 
637     LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
638 
639     LAST_UPDATED_BY = X_LAST_UPDATED_BY,
640 
641     LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
642 
643     SOURCE_LANG = userenv('LANG')
644 
645   where APPROVAL_RULE_ID = X_APPROVAL_RULE_ID
646 
647   and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
648 
649 
650 
651   if (sql%notfound) then
652 
653     raise no_data_found;
654 
655   end if;
656 
657 end UPDATE_ROW;
658 
659 
660 
661 procedure DELETE_ROW(
662 
663   X_APPROVAL_RULE_ID in NUMBER
664 
665 )
666 
667 is
668 
669 begin
670 
671   delete from AHL_APPROVAL_RULES_TL
672 
673   where APPROVAL_RULE_ID = X_APPROVAL_RULE_ID;
674 
675 
676 
677   if (sql%notfound) then
678 
679     raise no_data_found;
680 
681   end if;
682 
683 
684 
685   delete from AHL_APPROVAL_RULES_B
686 
687   where APPROVAL_RULE_ID = X_APPROVAL_RULE_ID;
688 
689 
690 
691   if (sql%notfound) then
692 
696 
693     raise no_data_found;
694 
695   end if;
697 end DELETE_ROW;
698 
699 
700 
701 procedure ADD_LANGUAGE
702 
703 is
704 
705 begin
706 
707   delete from AHL_APPROVAL_RULES_TL T
708 
709   where not exists
710 
711     (select NULL
712 
713     from AHL_APPROVAL_RULES_B B
714 
715     where B.APPROVAL_RULE_ID = T.APPROVAL_RULE_ID
716 
717     );
718 
719 
720 
721   update AHL_APPROVAL_RULES_TL T set (
722 
723       APPROVAL_RULE_NAME,
724 
725       DESCRIPTION
726 
727     ) = (select
728 
729       B.APPROVAL_RULE_NAME,
730 
731       B.DESCRIPTION
732 
733     from AHL_APPROVAL_RULES_TL B
734 
735     where B.APPROVAL_RULE_ID = T.APPROVAL_RULE_ID
736 
737     and B.LANGUAGE = T.SOURCE_LANG)
738 
739   where (
740 
741       T.APPROVAL_RULE_ID,
742 
743       T.LANGUAGE
744 
745   ) in (select
746 
747       SUBT.APPROVAL_RULE_ID,
748 
749       SUBT.LANGUAGE
753     where SUBB.APPROVAL_RULE_ID = SUBT.APPROVAL_RULE_ID
750 
751     from AHL_APPROVAL_RULES_TL SUBB, AHL_APPROVAL_RULES_TL SUBT
752 
754 
755     and SUBB.LANGUAGE = SUBT.SOURCE_LANG
756 
757     and (SUBB.APPROVAL_RULE_NAME <> SUBT.APPROVAL_RULE_NAME
758 
759       or (SUBB.APPROVAL_RULE_NAME is null and SUBT.APPROVAL_RULE_NAME is not null)
760 
761       or (SUBB.APPROVAL_RULE_NAME is not null and SUBT.APPROVAL_RULE_NAME is null)
762 
763       or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
764 
765       or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
766 
767       or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
768 
769   ));
770 
771 
772 
773   insert into AHL_APPROVAL_RULES_TL (
774 
775     APPROVAL_RULE_NAME,
776 
777     DESCRIPTION,
778 
779 --    SECURITY_GROUP_ID,
780 
781     APPROVAL_RULE_ID,
782 
783     LAST_UPDATE_DATE,
784 
785     LAST_UPDATED_BY,
786 
787     CREATION_DATE,
788 
789     CREATED_BY,
790 
791     LAST_UPDATE_LOGIN,
792 
793     LANGUAGE,
794 
795     SOURCE_LANG
796 
797   ) select
798 
799     B.APPROVAL_RULE_NAME,
800 
801     B.DESCRIPTION,
802 
803 --    B.SECURITY_GROUP_ID,
804 
805     B.APPROVAL_RULE_ID,
806 
807     B.LAST_UPDATE_DATE,
808 
809     B.LAST_UPDATED_BY,
810 
811     B.CREATION_DATE,
812 
813     B.CREATED_BY,
814 
815     B.LAST_UPDATE_LOGIN,
816 
817     L.LANGUAGE_CODE,
818 
819     B.SOURCE_LANG
820 
821   from AHL_APPROVAL_RULES_TL B, FND_LANGUAGES L
822 
823   where L.INSTALLED_FLAG in ('I', 'B')
824 
825   and B.LANGUAGE = userenv('LANG')
826 
827   and not exists
828 
829     (select NULL
830 
831     from AHL_APPROVAL_RULES_TL T
832 
833     where T.APPROVAL_RULE_ID = B.APPROVAL_RULE_ID
834 
835     and T.LANGUAGE = L.LANGUAGE_CODE);
836 
837 end ADD_LANGUAGE;
838 
839 
840 procedure TRANSLATE_ROW(
841           X_APPROVAL_RULE_ID      in NUMBER,
842           X_APPROVAL_RULE_NAME                in VARCHAR2,
843           X_DESCRIPTION         in VARCHAR2,
844           X_OWNER               in VARCHAR2
845  ) IS
846 
847  begin
848     update AHL_APPROVAL_RULES_TL set
849        approval_rule_name = nvl(x_approval_rule_name, approval_rule_name),
850        description = nvl(x_description, description),
851        source_lang = userenv('LANG'),
852        last_update_date = sysdate,
853        last_updated_by = decode(x_owner, 'SEED', 1, 0),
854        last_update_login = 0
855     where  approval_rule_id = x_approval_rule_id
856     and      userenv('LANG') in (language, source_lang);
857 end TRANSLATE_ROW;
858 
859 procedure  LOAD_ROW(
860   X_APPROVAL_RULE_ID in NUMBER,
861 		X_APPLICATION_USG_CODE IN VARCHAR2,
862   X_SEEDED_FLAG in VARCHAR2,
863   X_STATUS_CODE in VARCHAR2,
864   X_APPROVAL_RULE_NAME in VARCHAR2,
865   X_DESCRIPTION in VARCHAR2,
866   X_ACTIVE_START_DATE in DATE,
867   X_OWNER in VARCHAR2,
868   X_ATTRIBUTE_CATEGORY in VARCHAR2,
869   X_ATTRIBUTE1 in VARCHAR2,
870   X_ATTRIBUTE2 in VARCHAR2,
871   X_ATTRIBUTE3 in VARCHAR2,
872   X_ATTRIBUTE4 in VARCHAR2,
876   X_ATTRIBUTE8 in VARCHAR2,
873   X_ATTRIBUTE5 in VARCHAR2,
874   X_ATTRIBUTE6 in VARCHAR2,
875   X_ATTRIBUTE7 in VARCHAR2,
877   X_ATTRIBUTE9 in VARCHAR2,
878   X_ATTRIBUTE10 in VARCHAR2,
879   X_ATTRIBUTE11 in VARCHAR2,
880   X_ATTRIBUTE12 in VARCHAR2,
881   X_ATTRIBUTE13 in VARCHAR2,
882   X_ATTRIBUTE14 in VARCHAR2,
883   X_ATTRIBUTE15 in VARCHAR2
884  )
885 IS
886   l_user_id     number := 0;
887   l_obj_verno   number;
888   l_dummy_char  varchar2(1);
889   l_row_id      varchar2(100);
890   l_rule_id      number;
891   l_approval_object_code  VARCHAR2(30);
892   l_approval_priority_code  VARCHAR2(30);
893   l_approval_type_code     VARCHAR2(30);
894   l_operating_unit_id      NUMBER;
895   l_active_end_date        DATE;
896 
897 
898 cursor  c_obj_verno is
899   select  object_version_number
900   from    AHL_APPROVAL_RULES_B
901   where   approval_rule_id =  X_APPROVAL_RULE_ID;
902 
903 cursor c_chk_rule_exists is
904   select 'x'
905   from   AHL_APPROVAL_RULES_B
906   where  approval_rule_id = X_APPROVAL_RULE_ID;
907 
908 cursor c_get_rule_id is
909    select ahl_approval_rules_b_s.nextval
910    from dual;
911 
912 
913 BEGIN
914 
915   if X_OWNER = 'SEED' then
916      l_user_id := 1;
917  end if;
918 
919  open c_chk_rule_exists;
920  fetch c_chk_rule_exists into l_dummy_char;
921  if c_chk_rule_exists%notfound
922  then
923     close c_chk_rule_exists;
924 
925     if X_APPROVAL_RULE_ID is null then
926         open c_get_rule_id;
927         fetch c_get_rule_id into l_rule_id;
928         close c_get_rule_id;
929     else
930        l_rule_id := X_APPROVAL_RULE_ID;
931     end if ;
932 
933     l_obj_verno := 1;
934 
935  AHL_APPROVAL_RULES_PKG.INSERT_ROW (
936   X_ROWID                       => l_row_id,
937   X_APPROVAL_RULE_ID 		=> l_rule_id,
938   X_OBJECT_VERSION_NUMBER 	=> l_obj_verno,
939   X_APPROVAL_OBJECT_CODE        => l_approval_object_code,
940   X_APPROVAL_PRIORITY_CODE      => l_approval_priority_code,
941   X_APPROVAL_TYPE_CODE          => l_approval_type_code,
942   X_APPLICATION_USG_CODE        => X_APPLICATION_USG_CODE,
943   X_OPERATING_UNIT_ID           => l_operating_unit_id,
944   X_ACTIVE_END_DATE             => l_active_end_date,
945   X_STATUS_CODE 		=> X_STATUS_CODE ,
946   X_ACTIVE_START_DATE 		=> X_ACTIVE_START_DATE ,
947   X_SEEDED_FLAG 		=> X_SEEDED_FLAG ,
948   X_APPROVAL_RULE_NAME 		=> X_APPROVAL_RULE_NAME ,
949   X_ATTRIBUTE_CATEGORY 		=> X_ATTRIBUTE_CATEGORY,
950   X_ATTRIBUTE1			=>  X_ATTRIBUTE1,
951   X_ATTRIBUTE2			=>  X_ATTRIBUTE2,
952   X_ATTRIBUTE3			=>  X_ATTRIBUTE3,
953   X_ATTRIBUTE4			=>  X_ATTRIBUTE4,
954   X_ATTRIBUTE5			=>  X_ATTRIBUTE5,
955   X_ATTRIBUTE6			=>  X_ATTRIBUTE6,
956   X_ATTRIBUTE7			=>  X_ATTRIBUTE7,
957   X_ATTRIBUTE8			=>  X_ATTRIBUTE8,
958   X_ATTRIBUTE9			=>  X_ATTRIBUTE9,
959   X_ATTRIBUTE10			=>  X_ATTRIBUTE10,
960   X_ATTRIBUTE11			=>  X_ATTRIBUTE11,
961   X_ATTRIBUTE12			=>  X_ATTRIBUTE12,
962   X_ATTRIBUTE13			=>  X_ATTRIBUTE13,
963   X_ATTRIBUTE14			=>  X_ATTRIBUTE14,
964   X_ATTRIBUTE15			=>  X_ATTRIBUTE15,
965   X_DESCRIPTION 		=> X_DESCRIPTION ,
966   X_CREATION_DATE 		=> SYSDATE,
967   X_CREATED_BY                  => l_user_id,
971 
968   X_LAST_UPDATE_DATE            => SYSDATE,
969   X_LAST_UPDATED_BY             => l_user_id,
970   X_LAST_UPDATE_LOGIN           => 0
972 );
973 
974 else
975    close c_chk_rule_exists;
976    open c_obj_verno;
977    fetch c_obj_verno into l_obj_verno;
978    close c_obj_verno;
979 
980 AHL_APPROVAL_RULES_PKG.UPDATE_ROW (
981 
982   X_APPROVAL_RULE_ID 		=> X_APPROVAL_RULE_ID,
983   X_OBJECT_VERSION_NUMBER 	=> l_obj_verno + 1,
984   X_STATUS_CODE			=> X_STATUS_CODE ,
985   X_APPLICATION_USG_CODE        => X_APPLICATION_USG_CODE,
986   X_ACTIVE_START_DATE 		=> X_ACTIVE_START_DATE ,
987   X_SEEDED_FLAG 		=> X_SEEDED_FLAG,
988   X_APPROVAL_OBJECT_CODE        => l_approval_object_code,
989   X_APPROVAL_PRIORITY_CODE      => l_approval_priority_code,
990   X_APPROVAL_TYPE_CODE          => l_approval_type_code,
991   X_OPERATING_UNIT_ID           => l_operating_unit_id,
992   X_ACTIVE_END_DATE             => l_active_end_date,
993   X_ATTRIBUTE_CATEGORY 		=> X_ATTRIBUTE_CATEGORY,
994   X_ATTRIBUTE1			=>  X_ATTRIBUTE1,
995   X_ATTRIBUTE2			=>  X_ATTRIBUTE2,
996   X_ATTRIBUTE3			=>  X_ATTRIBUTE3,
997   X_ATTRIBUTE4			=>  X_ATTRIBUTE4,
998   X_ATTRIBUTE5			=>  X_ATTRIBUTE5,
999   X_ATTRIBUTE6			=>  X_ATTRIBUTE6,
1000   X_ATTRIBUTE7			=>  X_ATTRIBUTE7,
1001   X_ATTRIBUTE8			=>  X_ATTRIBUTE8,
1002   X_ATTRIBUTE9			=>  X_ATTRIBUTE9,
1003   X_ATTRIBUTE10			=>  X_ATTRIBUTE10,
1004   X_ATTRIBUTE11			=>  X_ATTRIBUTE11,
1005   X_ATTRIBUTE12			=>  X_ATTRIBUTE12,
1009   X_APPROVAL_RULE_NAME 		=> X_APPROVAL_RULE_NAME ,
1006   X_ATTRIBUTE13			=>  X_ATTRIBUTE13,
1007   X_ATTRIBUTE14			=>  X_ATTRIBUTE14,
1008   X_ATTRIBUTE15			=>  X_ATTRIBUTE15,
1010   X_DESCRIPTION 		=> X_DESCRIPTION ,
1011   X_LAST_UPDATE_DATE            => SYSDATE,
1012   X_LAST_UPDATED_BY             => l_user_id,
1013   X_LAST_UPDATE_LOGIN           => 0
1014 
1015 
1016 
1017 );
1018 
1019 
1020 end if;
1021 
1022 END LOAD_ROW ;
1023 
1024 
1025 end AHL_APPROVAL_RULES_PKG;
1026