1 PACKAGE BODY IGS_AD_VAL_SAFT AS
2 /* $Header: IGSAD68B.pls 115.4 2002/11/28 21:39:17 nsidana ship $ */
3 -------------------------------------------------------------------------------------------
4 --Change History:
5 --Who When What
6 --avenkatr 29-AUG-2001 Bug Id : 1956374. Removed Function "crsp_val_am_closed"
7 --avenkatr 29-AUG-2001 Bug Id : 1956374. Removed Function "crsp_val_fs_closed"
8 -------------------------------------------------------------------------------------------
9
10
11 --
12 -- Validate if IGS_FI_FUND_SRC.funding_source is closed.
13 FUNCTION crsp_val_fs_closed(
14 p_funding_source IN VARCHAR2 ,
15 p_message_name OUT NOCOPY VARCHAR2 )
16 RETURN BOOLEAN AS
17 BEGIN -- crsp_val_fs_closed
18 -- Description: Validate if IGS_FI_FUND_SRC.funding_source is closed.
19 DECLARE
20 v_fs_rec IGS_FI_FUND_SRC.closed_ind%TYPE;
21 CURSOR c_fs IS
22 SELECT fs.closed_ind
23 FROM IGS_FI_FUND_SRC fs
24 WHERE fs.funding_source = p_funding_source;
25 BEGIN
26 p_message_name := null;
27 OPEN c_fs;
28 FETCH c_fs INTO v_fs_rec;
29 IF (c_fs%FOUND) THEN
30 IF (v_fs_rec = 'Y') THEN
31 p_message_name := 'IGS_PS_FUND_SOURCE_CLOSED';
32 CLOSE c_fs;
33 RETURN FALSE;
34 END IF;
35 END IF;
36 CLOSE c_fs;
37 RETURN TRUE;
38 EXCEPTION
39 WHEN OTHERS THEN
40 IF (c_fs%ISOPEN) THEN
41 CLOSE c_fs;
42 END IF;
43 App_Exception.Raise_Exception;
44 END;
45 EXCEPTION
46 WHEN OTHERS THEN
47 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
48 FND_MESSAGE.SET_TOKEN('NAME','IGS_AD_VAL_SAFT.crsp_val_fs_closed');
49 IGS_GE_MSG_STACK.ADD;
50 App_Exception.Raise_Exception;
51 END crsp_val_fs_closed;
52 --
53 -- Validate target AOU codes are active and at the local INSTITUTION.
54 FUNCTION admp_val_trgt_aou(
55 p_org_unit_cd IN VARCHAR2 ,
56 p_ou_start_dt IN DATE ,
57 p_message_name OUT NOCOPY VARCHAR2 )
58 RETURN BOOLEAN AS
59 BEGIN -- admp_val_trgt_aou
60 -- This module validates the organisational UNIT is from the local
61 -- INSTITUTION in not inactive.
62 DECLARE
63 cst_inactive CONSTANT VARCHAR2(10) := 'INACTIVE';
64 cst_active CONSTANT VARCHAR2(10) := 'ACTIVE';
65 v_institution_cd IGS_OR_UNIT.institution_cd%TYPE;
66 v_dummy VARCHAR2(1);
67 CURSOR c_ou_os IS
68 SELECT ou.institution_cd
69 FROM IGS_OR_UNIT ou,
70 IGS_OR_STATUS os
71 WHERE ou.org_unit_cd = p_org_unit_cd AND
72 ou.start_dt = p_ou_start_dt AND
73 os.org_status = ou.org_status AND
74 os.s_org_status <> cst_inactive;
75 CURSOR c_ins_ist (cp_institution_cd IGS_OR_UNIT.institution_cd%TYPE) IS
76 SELECT 'X'
77 FROM IGS_OR_INSTITUTION ins,
78 IGS_OR_INST_STAT ist
79 WHERE ins.institution_cd = cp_institution_cd AND
80 ins.local_institution_ind = 'Y' AND
81 ins.institution_status = ist.institution_status AND
82 ist.s_institution_status = cst_active;
83 BEGIN
84 -- Set the default message number
85 p_message_name := null;
86 OPEN c_ou_os;
87 FETCH c_ou_os INTO v_institution_cd;
88 IF c_ou_os%NOTFOUND THEN
89 CLOSE c_ou_os;
90 p_message_name := 'IGS_AD_ORGUNIT_NOT_ACTIVE';
91 RETURN FALSE;
92 ELSE
93 CLOSE c_ou_os;
94 OPEN c_ins_ist(v_institution_cd);
95 FETCH c_ins_ist INTO v_dummy;
96 IF c_ins_ist%NOTFOUND THEN
97 CLOSE c_ins_ist;
98 p_message_name := 'IGS_AD_ORGUNIT_NOTBELONG_INST';
99 RETURN FALSE;
100 END IF;
101 CLOSE c_ins_ist;
102 END IF;
103 RETURN TRUE;
104 EXCEPTION
105 WHEN OTHERS THEN
106 IF c_ou_os%ISOPEN THEN
107 CLOSE c_ou_os;
108 END IF;
109 IF c_ins_ist%ISOPEN THEN
110 CLOSE c_ins_ist;
111 END IF;
112 App_Exception.Raise_Exception;
113 END;
114 EXCEPTION
115 WHEN OTHERS THEN
116 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
117 FND_MESSAGE.SET_TOKEN('NAME','IGS_AD_VAL_SAFT.admp_val_trgt_aou');
118 IGS_GE_MSG_STACK.ADD;
119 App_Exception.Raise_Exception;
120 END admp_val_trgt_aou;
121 --
122 -- Validate if course type group is closed.
123 FUNCTION crsp_val_ctg_closed(
124 p_course_type_group_cd IN VARCHAR2 ,
125 p_message_name OUT NOCOPY VARCHAR2 )
126 RETURN BOOLEAN AS
127 BEGIN -- crsp_val_ctg_closed
128 -- This module checks if a course type group is open.
129 DECLARE
130 v_closed_ind IGS_PS_TYPE_GRP.closed_ind%TYPE;
131 CURSOR c_ctg IS
132 SELECT ctg.closed_ind
133 FROM IGS_PS_TYPE_GRP ctg
134 WHERE ctg.course_type_group_cd = p_course_type_group_cd;
135 BEGIN
136 -- Set the default message number
137 p_message_name := null;
138 OPEN c_ctg;
139 FETCH c_ctg INTO v_closed_ind;
140 IF c_ctg%FOUND THEN
141 CLOSE c_ctg;
142 IF v_closed_ind = 'Y' THEN
143 p_message_name := 'IGS_PS_PRGTYPE_GRP_CLOSED';
144 RETURN FALSE;
145 END IF;
146 ELSE
147 CLOSE c_ctg;
148 END IF;
149 RETURN TRUE ;
150 EXCEPTION
151 WHEN OTHERS THEN
152 IF c_ctg%ISOPEN THEN
153 CLOSE c_ctg;
154 END IF;
155 App_Exception.Raise_Exception;
156 END;
157 EXCEPTION
158 WHEN OTHERS THEN
159 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
160 FND_MESSAGE.SET_TOKEN('NAME','IGS_AD_VAL_SAFT.crsp_val_ctg_closed');
161 IGS_GE_MSG_STACK.ADD;
162 App_Exception.Raise_Exception;
163 END crsp_val_ctg_closed;
164 --
165 -- Validate if unit internal course level is closed.
166 FUNCTION crsp_val_uicl_closed(
167 p_unit_int_course_level_cd IN VARCHAR2 ,
168 p_message_name OUT NOCOPY VARCHAR2 )
169 RETURN BOOLEAN AS
170 BEGIN -- crsp_val_uicl_closed
171 -- This module checks if a course type group is open.
172 DECLARE
173 v_closed_ind IGS_PS_UNIT_INT_LVL.closed_ind%TYPE;
174 CURSOR c_uicl IS
175 SELECT uicl.closed_ind
176 FROM IGS_PS_UNIT_INT_LVL uicl
177 WHERE uicl.unit_int_course_level_cd = p_unit_int_course_level_cd;
178 BEGIN
179 -- Set the default message number
180 p_message_name := null;
181 OPEN c_uicl;
182 FETCH c_uicl INTO v_closed_ind;
183 IF c_uicl%FOUND THEN
184 CLOSE c_uicl;
185 IF v_closed_ind = 'Y' THEN
186 p_message_name := 'IGS_PS_PRGTYPE_GRP_CLOSED';
187 RETURN FALSE;
188 END IF;
189 ELSE
190 CLOSE c_uicl;
191 END IF;
192 RETURN TRUE ;
193 EXCEPTION
194 WHEN OTHERS THEN
195 IF c_uicl%ISOPEN THEN
196 CLOSE c_uicl;
197 END IF;
198 App_Exception.Raise_Exception;
199 END;
200 EXCEPTION
201 WHEN OTHERS THEN
202 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
203 FND_MESSAGE.SET_TOKEN('NAME','IGS_AD_VAL_SAFT.crsp_val_uicl_closed');
204 IGS_GE_MSG_STACK.ADD;
205 App_Exception.Raise_Exception;
206 END crsp_val_uicl_closed;
207 END IGS_AD_VAL_SAFT;