[Home] [Help]
PACKAGE BODY: APPS.IGS_OR_VAL_OU
Source
1 PACKAGE BODY IGS_OR_VAL_OU AS
2 /* $Header: IGSOR09B.pls 115.4 2002/11/29 01:48:02 nsidana ship $ */
3
4 /* HISTORY
5 WHO WHEN WHAT
6 pkpatel 27-OCT-2002 Bug NO: 2613704
7 Changed for lookup migration of ORG_TYPE and MEMBER_TYPE
8 */
9 --
10
11
12 -- Validate the organisational IGS_PS_UNIT end date.
13
14
15 FUNCTION orgp_val_ou_end_dt(
16
17
18 p_org_unit_cd IN VARCHAR2 ,
19
20
21 p_start_dt IN DATE ,
22
23
24 p_end_dt IN DATE ,
25
26
27 p_message_name OUT NOCOPY VARCHAR2 )
28
29
30 RETURN Boolean AS
31
32
33 BEGIN
34
35
36 p_message_name := NULL;
37
38
39 -- Perform validation when end date is set.
40
41
42 IF p_end_dt IS NOT NULL THEN
43
44
45 -- Validate end date is less than or equal to the current date.
46
47
48 IF p_end_dt > SYSDATE THEN
49
50
51 p_message_name := 'IGS_OR_UNIT_END_DT_LE_CURR_DT';
52
53
54 RETURN FALSE;
55
56
57 END IF;
58
59
60 -- Validate end date is greater than or equal to the start date.
61
62
63 IF p_end_dt < p_start_dt THEN
64
65
66 p_message_name := 'IGS_OR_UNIT_END_DT_GE_CURR_DT';
67
68
69 RETURN FALSE;
70
71
72 END IF;
73
74
75 ELSE -- Perform validation when end date is not set
76
77
78 -- Validate no other open ended org units.
79
80
81 IF IGS_OR_VAL_OU.orgp_val_open_ou (
82
83
84 p_org_unit_cd,
85
86
87 p_start_dt,
88
89
90 p_message_name) = FALSE THEN
91
92
93 RETURN FALSE;
94
95
96 END IF;
97
98
99 END IF;
100
101
102 RETURN TRUE;
103
104
105 END orgp_val_ou_end_dt;
106
107
108 --
109
110
111 -- Validate if any open ended org units exist for the current org IGS_PS_UNIT.
112
113
114 FUNCTION orgp_val_open_ou(
115
116
117 p_org_unit_cd IN VARCHAR2 ,
118
119
120 p_start_dt IN DATE ,
121
122
123 p_message_name OUT NOCOPY VARCHAR2 )
124
125
126 RETURN BOOLEAN AS
127
128
129 CURSOR c_ou IS
130
131
132 SELECT org_unit_cd
133
134
135 FROM IGS_OR_UNIT
136
137
138 WHERE org_unit_cd = p_org_unit_cd
139
140
141 AND start_dt <> p_start_dt
142
143
144 AND end_dt IS NULL;
145
146
147 v_other_detail VARCHAR2(255);
148
149
150 BEGIN
151
152
153 p_message_name := NULL;
154
155
156 FOR ou IN c_ou LOOP
157
158
159 p_message_name := 'IGS_OR_UNIT_ALREADY_EXISTS';
160
161
162 RETURN FALSE;
163
164
165 END LOOP;
166
167
168 RETURN TRUE;
169
170
171 EXCEPTION
172
173
174 WHEN OTHERS THEN
175
176
177 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXCEPTION');
178 IGS_GE_MSG_STACK.ADD;
179 App_Exception.Raise_Exception ;
180
181
182 END orgp_val_open_ou;
183
184
185 --
186
187
188 -- Validate the organisational status.
189
190
191 FUNCTION orgp_val_org_status(
192
193
194 p_org_status IN VARCHAR2 ,
195
196
197 p_message_name OUT NOCOPY VARCHAR2 )
198
199
200 RETURN BOOLEAN AS
201
202
203 CURSOR c_os IS
204
205
206 SELECT closed_ind
207
208
209 FROM IGS_OR_STATUS
210
211
212 WHERE org_status = p_org_status
213
214
215 AND closed_ind = 'Y';
216
217
218 v_other_detail VARCHAR2(255);
219
220
221 BEGIN
222
223
224 p_message_name := NULL;
225
226
227 FOR os IN c_os LOOP
228
229
230 p_message_name := 'IGS_OR_STAT_CANT_CLOSED';
231
232
233 RETURN FALSE;
234
235
236 END LOOP;
237
238
239 RETURN TRUE;
240
241
242 EXCEPTION
243
244
245 WHEN OTHERS THEN
246
247
248 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXCEPTION');
249 IGS_GE_MSG_STACK.ADD;
250 App_Exception.Raise_Exception ;
251
252
253 END orgp_val_org_status;
254
255
256 --
257
258
259 -- Ensure an organisational IGS_PS_UNIT status change is valid.
260
261
262 FUNCTION orgp_val_ou_sts_chng(
263
264
265 p_org_unit_cd IN VARCHAR2 ,
266
267
268 p_start_dt IN DATE ,
269
270
271 p_org_status IN VARCHAR2 ,
272
273
274 p_message_name OUT NOCOPY VARCHAR2 )
275
276
277 RETURN BOOLEAN AS
278
279
280 -- Fetch the system status.
281
282
283 CURSOR c_os IS
284
285
286 SELECT s_org_status
287
288
289 FROM IGS_OR_STATUS
290
291
292 WHERE org_status = p_org_status;
293
294
295 -- Fetch parent records.
296
297
298 CURSOR c_our_ou_os_parent (
299
300
301 cp_org_unit_cd IGS_OR_UNIT.org_unit_cd%TYPE,
302
303
304 cp_start_dt IGS_OR_UNIT.start_dt%TYPE) IS
305
306
307 SELECT parent_org_unit_cd,
308
309
310 parent_start_dt,
311
312
313 s_org_status
314
315
316 FROM IGS_OR_UNIT_REL,
317
318
319 IGS_OR_UNIT,
320
321
322 IGS_OR_STATUS
323
324
325 WHERE child_org_unit_cd = cp_org_unit_cd
326
327
328 AND child_start_dt = cp_start_dt
329
330
331 AND logical_delete_dt IS NULL
332
333
334 AND org_unit_cd = parent_org_unit_cd
335
336
337 AND start_dt = parent_start_dt
338
339
340 AND IGS_OR_UNIT.org_status = IGS_OR_STATUS.org_status;
341
342
343 -- Fetch active child records.
344
345
346 CURSOR c_our_ou_os_child IS
347
348
349 SELECT child_org_unit_cd,
350
351
352 child_start_dt
353
354
355 FROM IGS_OR_UNIT_REL,
356
357
358 IGS_OR_UNIT,
359
360
361 IGS_OR_STATUS
362
363
364 WHERE parent_org_unit_cd = p_org_unit_cd
365
366
367 AND parent_start_dt = p_start_dt
368
369
370 AND logical_delete_dt IS NULL
371
372
373 AND org_unit_cd = child_org_unit_cd
374
375
376 AND start_dt = child_start_dt
377
378
379 AND IGS_OR_UNIT.org_status = IGS_OR_STATUS.org_status
380
381
382 AND s_org_status = 'ACTIVE';
383
384
385 v_s_org_status IGS_OR_STATUS.s_org_status%TYPE;
386
387
388 v_active_parents BOOLEAN DEFAULT FALSE;
389
390
391 v_active_children BOOLEAN DEFAULT FALSE;
392
393
394 v_parents_exist BOOLEAN DEFAULT FALSE;
395
396
397 v_other_detail VARCHAR(255);
398
399
400 BEGIN
401
402
403 -- Fetch the system status for the p_org_status.
404
405
406 OPEN c_os;
407
408
409 FETCH c_os INTO v_s_org_status;
410
411
412 CLOSE c_os;
413
414
415 IF v_s_org_status = 'ACTIVE' THEN
416
417
418 -- If parents exist then validate at least one is active.
419
420
421 FOR our_ou_os_parent IN c_our_ou_os_parent (p_org_unit_cd, p_start_dt) LOOP
422
423
424 v_parents_exist := TRUE;
425
426
427 IF our_ou_os_parent.s_org_status = 'ACTIVE' THEN
428
429
430 v_active_parents := TRUE;
431
432
433 EXIT;
434
435
436 END IF;
437
438
439 END LOOP;
440
441
442 IF v_parents_exist = TRUE THEN
443
444
445 IF v_active_parents = FALSE THEN
446
447
448 -- No active parents exist.
449
450
451 p_message_name := 'IGS_OR_CANT_UPD_DUE_TO_PARENT';
452
453
454 RETURN FALSE;
455
456
457 END IF;
458
459
460 END IF;
461
462
463 END IF;
464
465
466 IF v_s_org_status = 'INACTIVE' THEN
467
468
469 -- Validate all children are inactive.
470
471
472 FOR our_ou_os_child IN c_our_ou_os_child LOOP
473
474
475 v_active_parents := FALSE;
476
477
478 v_parents_exist := FALSE;
479
480
481 v_active_children := TRUE;
482
483
484 -- Check if the active child has other active parents.
485
486
487 FOR our_ou_os_parent IN c_our_ou_os_parent (
488
489
490 our_ou_os_child.child_org_unit_cd,
491
492
493 our_ou_os_child.child_start_dt) LOOP
494
495
496 IF NOT (our_ou_os_parent.parent_org_unit_cd = p_org_unit_cd AND
497
498
499 our_ou_os_parent.parent_start_dt = p_start_dt) THEN
500
501
502 v_parents_exist := TRUE;
503
504
505 IF our_ou_os_parent.s_org_status = 'ACTIVE' THEN
506
507
508 v_active_parents := TRUE;
509
510
511 EXIT;
512
513
514 END IF;
515
516
517 END IF;
518
519
520 END LOOP;
521
522
526 IF v_active_parents = TRUE THEN
523 IF v_parents_exist = TRUE THEN
524
525
527
528
529 v_active_children := FALSE;
530
531
532 END IF;
533
534
535 END IF;
536
537
538 IF v_active_children = TRUE THEN
539
540
541 -- Acitve children exist.
542
543
544 p_message_name := 'IGS_OR_CANT_UPD_DUE_TO_CHILD';
545
546
547 RETURN FALSE;
548
549
550 END IF;
551
552
553 END LOOP;
554
555
556 END IF;
557
558
559 p_message_name := NULL;
560
561
562 RETURN TRUE;
563
564
565 EXCEPTION
566
567
568 WHEN OTHERS THEN
569
570
571 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXCEPTION');
572 IGS_GE_MSG_STACK.ADD;
573 App_Exception.Raise_Exception ;
574
575
576 END orgp_val_ou_sts_chng;
577
578
579 --
580
581
582 -- Validate the organisational type.
583
584
585 FUNCTION orgp_val_org_type(
586
587
588 p_org_type IN VARCHAR2 ,
589
590
591 p_message_name OUT NOCOPY VARCHAR2 )
592
593
594 RETURN BOOLEAN AS
595
596
597 CURSOR c_ot IS
598
599
600 SELECT enabled_flag
601
602
603 FROM IGS_LOOKUP_VALUES
604
605 WHERE lookup_type = 'OR_ORG_TYPE'
606
607 AND lookup_code = p_org_type
608
609 AND enabled_flag = 'N';
610
611
612 v_other_detail VARCHAR2(255);
613
614
615 BEGIN
616
617
618 p_message_name := NULL;
619
620
621 FOR ot IN c_ot LOOP
622
623
624 p_message_name := 'IGS_OR_TYPE_CANT_CLOSED';
625
626
627 RETURN FALSE;
628
629
630 END LOOP;
631
632
633 RETURN TRUE;
634
635
636 EXCEPTION
637
638
639 WHEN OTHERS THEN
640
641
642 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXCEPTION');
643 IGS_GE_MSG_STACK.ADD;
644 App_Exception.Raise_Exception ;
645
646
647 END orgp_val_org_type;
648
649
650 --
651
652
653 -- Validate the member type.
654
655
656 FUNCTION orgp_val_mbr_type(
657
658
659 p_member_type IN VARCHAR2 ,
660
661
662 p_message_name OUT NOCOPY VARCHAR2 )
663
664
665 RETURN BOOLEAN AS
666
667
668 CURSOR c_mt IS
669
670
671 SELECT enabled_flag
672
673
674 FROM IGS_LOOKUP_VALUES
675
676 WHERE lookup_type = 'OR_MEMBER_TYPE'
677
678
679 AND lookup_code = p_member_type
680
681
682 AND enabled_flag = 'N';
683
684
685 v_other_detail VARCHAR2(255);
686
687
688 BEGIN
689
690
691 p_message_name := NULL;
692
693
694 FOR mt IN c_mt LOOP
695
696
697 p_message_name := 'IGS_OR_MEM_TYPE_CANT_CLOSED';
698
699
700 RETURN FALSE;
701
702
703 END LOOP;
704
705
706 RETURN TRUE;
707
708
709 EXCEPTION
710
711
712 WHEN OTHERS THEN
713
714
715 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXCEPTION');
716 IGS_GE_MSG_STACK.ADD;
717 App_Exception.Raise_Exception ;
718
719 END orgp_val_mbr_type;
720
721
722 --
723
724
725 -- Validate the organisational IGS_PS_UNIT IGS_OR_INSTITUTION code is active.
726
727
728 FUNCTION orgp_val_ou_instn_cd(
729
730
731 p_institution_cd IN VARCHAR2 ,
732
733
734 p_message_name OUT NOCOPY VARCHAR2 )
735
736
737 RETURN BOOLEAN AS
738
739
740 CURSOR c_ins_ist IS
741
742
743 SELECT s_institution_status
744
745
746 FROM IGS_OR_INSTITUTION,
747
748
749 IGS_OR_INST_STAT
750
751
752 WHERE IGS_OR_INSTITUTION.institution_cd = p_institution_cd
753
754
755 AND IGS_OR_INST_STAT.institution_status = IGS_OR_INSTITUTION.institution_status
756
757
758 AND s_institution_status <> 'ACTIVE';
759
760
761 v_other_detail VARCHAR2(255);
762
763
764 BEGIN
765
766
767 p_message_name := NULL;
768
769
770 FOR ins_ist IN c_ins_ist LOOP
771
772
773 p_message_name := 'IGS_OR_CANT_CREATE_ORG_UNIT';
774
775
776 RETURN FALSE;
777
778
779 END LOOP;
780
781
782 RETURN TRUE;
783
784
785 EXCEPTION
786
787
788 WHEN OTHERS THEN
789
790
791 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXCEPTION');
792 IGS_GE_MSG_STACK.ADD;
793 App_Exception.Raise_Exception ;
794
795
796 END orgp_val_ou_instn_cd;
797
798
799 --
800
801
802 -- Validate for date overlaps for a specific organisational IGS_PS_UNIT.
803
804
805 FUNCTION orgp_val_ou_ovrlp(
806
807
808 p_org_unit_cd IN VARCHAR2 ,
809
810
811 p_start_dt IN DATE ,
812
813
814 p_end_dt IN DATE ,
815
816
817 p_message_name OUT NOCOPY VARCHAR2 )
818
819
820 RETURN BOOLEAN AS
821
822
823 CURSOR c_ou IS
824
825
826 SELECT start_dt,
827
828
829 end_dt
830
831
832 FROM IGS_OR_UNIT
833
834
835 WHERE org_unit_cd = p_org_unit_cd
836
837
838 AND start_dt <> p_start_dt;
839
840
841 v_other_detail VARCHAR2(255);
842
843
844 v_end_dt IGS_OR_UNIT.end_dt%TYPE;
845
846
847 BEGIN
848
849
850 p_message_name := NULL;
851
852
853 -- set p_end_dt to a high date if null
854
855
856 IF (p_end_dt IS NULL) THEN
857
858
859 v_end_dt := IGS_GE_DATE.IGSDATE('9999/01/01');
860
861
862 ELSE
863
864
865 v_end_dt := p_end_dt;
866
867
868 END IF;
869
870
871 FOR ou IN c_ou LOOP
872
873
874 -- Validate the start date is not between an existing date range.
875
876
877 IF (p_start_dt >= ou.start_dt) AND
878
879
880 (p_start_dt <= NVL(ou.end_dt, IGS_GE_DATE.IGSDATE('9999/01/01'))) THEN
881
882
883 p_message_name := 'IGS_OR_ORG_UNIT_OVERLAPS';
884
885
886 RETURN FALSE;
887
888
889 END IF;
890
891
892 -- Validate the end date is not between an existing date range.
893
894
895 IF (v_end_dt >= ou.start_dt) AND
896
897
898 (v_end_dt <= NVL(ou.end_dt, IGS_GE_DATE.IGSDATE('9999/01/01'))) THEN
899
900
901 p_message_name := 'IGS_OR_ORG_UNIT_OVERLAPS';
902
903
904 RETURN FALSE;
905
906
907 END IF;
908
909
910 -- Validate the current dates do not overlap and entire exisitng date range.
911
912
913 IF (p_start_dt <= ou.start_dt AND
914
915
919 p_message_name := 'IGS_OR_ORG_UNIT_OVERLAPS';
916 v_end_dt >= NVL(ou.end_dt, IGS_GE_DATE.IGSDATE('9999/01/01'))) THEN
917
918
920
921
922 RETURN FALSE;
923
924
925 END IF;
926
927
928 END LOOP;
929
930
931 RETURN TRUE;
932
933
934 EXCEPTION
935
936
937 WHEN OTHERS THEN
938
939
940 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXCEPTION');
941 IGS_GE_MSG_STACK.ADD;
942 App_Exception.Raise_Exception ;
943
944
945 END orgp_val_ou_ovrlp;
946
947
948 --
949
950
951 -- Cross-field validation of the org IGS_PS_UNIT end date and status.
952
953
954 FUNCTION orgp_val_ou_end_sts(
955
956
957 p_end_dt IN DATE ,
958
959
960 p_org_status IN VARCHAR2 ,
961
962
963 p_message_name OUT NOCOPY VARCHAR2 )
964
965
966 RETURN BOOLEAN AS
967
968
969 CURSOR c_os IS
970
971
972 SELECT s_org_status
973
974
975 FROM IGS_OR_STATUS
976
977
978 WHERE org_status = p_org_status;
979
980
981 cst_inactive CONSTANT VARCHAR2(8) := 'INACTIVE';
982
983
984 v_other_detail VARCHAR2(255);
985
986
987 BEGIN
988
989
990 p_message_name := NULL;
991
992
993 FOR os IN c_os LOOP
994
995
996 IF p_end_dt IS NOT NULL THEN
997
998
999 IF os.s_org_status <> cst_inactive THEN
1000
1001
1002 p_message_name := 'IGS_OR_SYS_STAT_MUST_BE_INACT';
1003
1004
1005 RETURN FALSE;
1006
1007
1008 END IF;
1009
1010
1011 ELSE -- end date is null
1012
1013
1014 IF os.s_org_status = cst_inactive THEN
1015
1016
1017 p_message_name := 'IGS_OR_SYS_STAT_MUST_BE_ACTIV';
1018
1019
1020 RETURN FALSE;
1021
1022
1023 END IF;
1024
1025
1026 END IF;
1027
1028
1029 END LOOP;
1030
1031
1032 RETURN TRUE;
1033
1034
1035 EXCEPTION
1036
1037
1038 WHEN OTHERS THEN
1039
1040
1041 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXCEPTION');
1042 IGS_GE_MSG_STACK.ADD;
1043 App_Exception.Raise_Exception ;
1044
1045 END orgp_val_ou_end_sts;
1046
1047
1048 END IGS_OR_VAL_OU;