1 PACKAGE BODY IGS_AD_VAL_TE AS
2 /* $Header: IGSAD72B.pls 115.3 2002/11/28 21:40:17 nsidana ship $ */
3
4 --
5 -- Validate tertiary education IGS_OR_INSTITUTION details.
6 FUNCTION admp_val_te_inst(
7 p_institution_cd IN VARCHAR2 ,
8 p_institution_name IN VARCHAR2 ,
9 p_message_name OUT NOCOPY VARCHAR2 )
10 RETURN BOOLEAN AS
11 BEGIN -- admp_val_te_inst
12 -- Validate IGS_AD_TER_EDU IGS_OR_INSTITUTION details
13 DECLARE
14 v_local_inst_ind IGS_OR_INSTITUTION.local_institution_ind%TYPE;
15 CURSOR c_institution IS
16 SELECT local_institution_ind
17 FROM IGS_OR_INSTITUTION
18 WHERE institution_cd = p_institution_cd;
19 BEGIN
20 p_message_name := null;
21 IF p_institution_cd IS NULL AND
22 p_institution_name IS NULL THEN
23 p_message_name := 'IGS_AD_INSCD_INSNAME_SPECIFY';
24 RETURN FALSE;
25 END IF;
26 IF p_institution_cd IS NOT NULL AND
27 p_institution_name IS NOT NULL THEN
28 p_message_name := 'IGS_AD_INSCD_OR_INSNAME_REQ';
29 RETURN FALSE;
30 END IF;
31 IF p_institution_cd IS NOT NULL THEN
32 OPEN c_institution;
33 FETCH c_institution INTO v_local_inst_ind;
34 IF (c_institution%FOUND) THEN
35 IF v_local_inst_ind = 'Y' THEN
36 CLOSE c_institution;
37 p_message_name := 'IGS_AD_LOCALINS_INFO_TRTYEDU';
38 RETURN FALSE;
39 END IF;
40 END IF;
41 CLOSE c_institution;
42 END IF;
43 p_message_name := null;
44 RETURN TRUE;
45 END;
46 EXCEPTION
47 WHEN OTHERS THEN
48 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
49 FND_MESSAGE.SET_TOKEN('NAME','IGS_AD_VAL_TE.admp_val_te_inst');
50 IGS_GE_MSG_STACK.ADD;
51 App_Exception.Raise_Exception;
52 END admp_val_te_inst;
53 --
54 -- Validate tertiary education enrolment years.
55 FUNCTION admp_val_te_enr_yr(
56 p_enrolment_first_yr IN NUMBER ,
57 p_enrolment_latest_yr IN NUMBER ,
58 p_message_name OUT NOCOPY VARCHAR2 )
59 RETURN BOOLEAN AS
60 BEGIN -- admp_val_te_enr_yr
61 -- Validate the IGS_AD_TER_EDU enrolment first and latest years.
62 DECLARE
63 BEGIN
64 p_message_name := null;
65 IF p_enrolment_first_yr IS NOT NULL AND
66 p_enrolment_latest_yr IS NOT NULL AND
67 p_enrolment_latest_yr < p_enrolment_first_yr THEN
68 p_message_name := 'IGS_AD_ENR_LY_LE_ENR_FY';
69 RETURN FALSE;
70 END IF;
71 RETURN TRUE;
72 END;
73 EXCEPTION
74 WHEN OTHERS THEN
75 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
76 FND_MESSAGE.SET_TOKEN('NAME','IGS_AD_VAL_TE.admp_val_te_enr_yr');
77 IGS_GE_MSG_STACK.ADD;
78 App_Exception.Raise_Exception;
79 END admp_val_te_enr_yr;
80 --
81 -- Validate if IGS_AD_TER_ED_LV_COM.tertiary_edu_lvl_comp is closed.
82 FUNCTION admp_val_telocclosed(
83 p_tertiary_edu_lvl_comp IN VARCHAR2 ,
84 p_message_name OUT NOCOPY VARCHAR2 )
85 RETURN BOOLEAN AS
86 BEGIN --admp_val_telocclosed
87 --This module validates if IGS_AD_TER_ED_LV_COM.tertiary_edu_lvl_comp
88 -- is closed
89 DECLARE
90 v_teloc_exists VARCHAR2(1);
91 CURSOR c_teloc IS
92 SELECT 'X'
93 FROM IGS_AD_TER_ED_LV_COM teloc
94 WHERE teloc.tertiary_edu_lvl_comp = p_tertiary_edu_lvl_comp AND
95 teloc.closed_ind = 'Y';
96 BEGIN
97 --Set the default message number
98 p_message_name := null;
99 --If the closed indicator is 'Y' then set p_message_name
100 OPEN c_teloc;
101 FETCH c_teloc INTO v_teloc_exists;
102 IF (c_teloc%FOUND) THEN
103 p_message_name := 'IGS_AD_TRTYEDU_LOC_CLOSED';
104 CLOSE c_teloc;
105 RETURN FALSE;
106 END IF;
107 CLOSE c_teloc;
108 RETURN TRUE;
109 END;
110 EXCEPTION
111 WHEN OTHERS THEN
112 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
113 FND_MESSAGE.SET_TOKEN('NAME','IGS_AD_VAL_TE.admp_val_telocclosed');
114 IGS_GE_MSG_STACK.ADD;
115 App_Exception.Raise_Exception;
116 END admp_val_telocclosed;
117 --
118 -- Validate if IGS_AD_TER_ED_LVL_QF.tertiary_edu_lvl_qual is closed.
119 FUNCTION admp_val_teloqclosed(
120 p_tertiary_edu_lvl_qual IN VARCHAR2 ,
121 p_message_name OUT NOCOPY VARCHAR2 )
122 RETURN BOOLEAN AS
123 BEGIN --admp_val_teloqclosed
124 --This module validates if IGS_AD_TER_ED_LVL_QF.IGS_AD_TER_ED_LVL_QF
125 -- is closed
126 DECLARE
127 v_teloq_exists VARCHAR2(1);
128 CURSOR c_teloq IS
129 SELECT 'X'
130 FROM IGS_AD_TER_ED_LVL_QF teloq
131 WHERE teloq.tertiary_edu_lvl_qual = p_tertiary_edu_lvl_qual AND
132 teloq.closed_ind = 'Y';
133 BEGIN
134 --Set the default message number
135 p_message_name := null;
136 --If the closed indicator is 'Y' then set p_message_name
137 OPEN c_teloq;
138 FETCH c_teloq INTO v_teloq_exists;
139 IF (c_teloq%FOUND) THEN
140 p_message_name := 'IGS_AD_TRTYEDU_LOQ_CLOSED';
141 CLOSE c_teloq;
142 RETURN FALSE;
143 END IF;
144 CLOSE c_teloq;
145 RETURN TRUE;
146 END;
147 EXCEPTION
148 WHEN OTHERS THEN
149 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
150 FND_MESSAGE.SET_TOKEN('NAME','IGS_AD_VAL_TE.admp_val_teloqclosed');
151 IGS_GE_MSG_STACK.ADD;
152 App_Exception.Raise_Exception;
153 END admp_val_teloqclosed;
154 END IGS_AD_VAL_TE;