1 PACKAGE BODY IGS_AD_GEN_009 AS
2 /* $Header: IGSAD09B.pls 115.2 2002/02/12 16:21:09 pkm ship $ */
3
4 Function Admp_Get_Sys_Acos(
5 p_s_adm_cndtnl_offer_status IN VARCHAR2 )
6 RETURN VARCHAR2 IS
7 gv_other_detail VARCHAR2(255);
8 BEGIN
9 -- admp_get_sys_acos
10 -- Routine to return the user-defined system default admission conditional
11 -- offer status for given the system admission conditional offer status.
12 DECLARE
13 v_adm_cndtnl_offer_status IGS_AD_CNDNL_OFRSTAT.adm_cndtnl_offer_status%TYPE;
14 CURSOR c_acos IS
15 SELECT acos.adm_cndtnl_offer_status
16 FROM IGS_AD_CNDNL_OFRSTAT acos
17 WHERE acos.s_adm_cndtnl_offer_status = p_s_adm_cndtnl_offer_status AND
18 acos.system_default_ind = 'Y' AND
19 acos.closed_ind = 'N';
20 BEGIN
21 -- If no record is found, return value is NULL
22 v_adm_cndtnl_offer_status := NULL;
23 FOR v_acos_rec IN c_acos LOOP
24 IF (c_acos%ROWCOUNT>1)THEN
25 v_adm_cndtnl_offer_status := NULL; -- more than one record found
26 exit;
27 END IF;
28 v_adm_cndtnl_offer_status := v_acos_rec.adm_cndtnl_offer_status;
29 END LOOP;
30 RETURN v_adm_cndtnl_offer_status;
31 END;
32 EXCEPTION
33 WHEN OTHERS THEN
34 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
35 Fnd_Message.Set_Token('NAME','IGS_AD_GEN_009.admp_get_sys_acos');
36 IGS_GE_MSG_STACK.ADD;
37 App_Exception.Raise_Exception;
38 END admp_get_sys_acos;
39
40 Function Admp_Get_Sys_Ads(
41 p_s_adm_doc_status IN VARCHAR2 )
42 RETURN VARCHAR2 IS
43 gv_other_detail VARCHAR2(255);
44 BEGIN -- admp_get_sys_ads
45 -- routine to return the user-defined system default admission documentation
46 -- status for given the system admission documentation status
47 DECLARE
48 CURSOR c_ads IS
49 SELECT ads.adm_doc_status
50 FROM IGS_AD_DOC_STAT ads
51 WHERE ads.s_adm_doc_status = p_s_adm_doc_status AND
52 ads.system_default_ind = 'Y' AND
53 ads.closed_ind = 'N';
54 v_adm_doc_status IGS_AD_DOC_STAT.adm_doc_status%TYPE;
55 BEGIN
56 FOR v_ads_rec IN c_ads LOOP
57 IF c_ads%ROWCOUNT > 1 THEN
58 v_adm_doc_status := NULL;
59 exit;
60 END IF;
61 v_adm_doc_status := v_ads_rec.adm_doc_status;
62 END LOOP;
63 -- return null if no records or more than one record found
64 RETURN v_adm_doc_status;
65 END;
66 EXCEPTION
67 WHEN OTHERS THEN
68 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
69 Fnd_Message.Set_Token('NAME','IGS_AD_GEN_009.admp_get_sys_ads');
70 IGS_GE_MSG_STACK.ADD;
71 App_Exception.Raise_Exception;
72 END admp_get_sys_ads;
73
74 Function Admp_Get_Sys_Aeqs(
75 p_s_adm_entry_qual_status IN VARCHAR2 )
76 RETURN VARCHAR2 IS
77 gv_other_detail VARCHAR2(255);
78 BEGIN -- admp_get_sys_aeqs
79 -- routine to return the user-defined system default admission entry status for
80 -- system admission entry status
81 DECLARE
82 CURSOR c_aeqs IS
83 SELECT aeqs.adm_entry_qual_status
84 FROM IGS_AD_ENT_QF_STAT aeqs
85 WHERE aeqs.s_adm_entry_qual_status = p_s_adm_entry_qual_status AND
86 aeqs.system_default_ind = 'Y' AND
87 aeqs.closed_ind = 'N';
88 v_adm_ent_qual_sts IGS_AD_ENT_QF_STAT.adm_entry_qual_status%TYPE;
89 BEGIN
90 FOR v_aeqs_rec IN c_aeqs LOOP
91 IF c_aeqs%ROWCOUNT > 1 THEN
92 v_adm_ent_qual_sts := NULL;
93 exit;
94 END IF;
95 v_adm_ent_qual_sts := v_aeqs_rec.adm_entry_qual_status;
96 END LOOP;
97 -- return null if no records or more than one record found
98 RETURN v_adm_ent_qual_sts;
99 END;
100 EXCEPTION
101 WHEN OTHERS THEN
102 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
103 Fnd_Message.Set_Token('NAME','IGS_AD_GEN_009.admp_get_sys_aeqs');
104 IGS_GE_MSG_STACK.ADD;
105 App_Exception.Raise_Exception;
106 END admp_get_sys_aeqs;
107
108 Function Admp_Get_Sys_Afs(
109 p_s_adm_fee_status IN VARCHAR2 )
110 RETURN VARCHAR2 IS
111 gv_other_detail VARCHAR2(255);
112 BEGIN -- admp_get_sys_afs
113 -- routine to return the user-defined system default admission fee status
114 -- for the given system admission fee status
115 DECLARE
116 CURSOR c_afs IS
117 SELECT afs.adm_fee_status
118 FROM IGS_AD_FEE_STAT afs
119 WHERE afs.s_adm_fee_status = p_s_adm_fee_status AND
120 afs.system_default_ind = 'Y' AND
121 afs.closed_ind = 'N';
122 v_adm_fee_status IGS_AD_FEE_STAT.adm_fee_status%TYPE := NULL;
123 BEGIN
124 FOR v_afs_rec IN c_afs LOOP
125 IF c_afs%ROWCOUNT > 1 THEN
126 v_adm_fee_status := NULL;
127 exit;
128 END IF;
129 v_adm_fee_status := v_afs_rec.adm_fee_status;
130 END LOOP;
131 -- return null if no records or more than one record found
132 RETURN v_adm_fee_status;
133 END;
134 EXCEPTION
135 WHEN OTHERS THEN
136 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
137 Fnd_Message.Set_Token('NAME','IGS_AD_GEN_009.admp_get_sys_afs');
138 IGS_GE_MSG_STACK.ADD;
139 App_Exception.Raise_Exception;
140 END admp_get_sys_afs;
141
142 Function Admp_Get_Sys_Aods(
143 p_s_adm_offer_dfrmnt_status IN VARCHAR2 )
144 RETURN VARCHAR2 IS
145 gv_other_detail VARCHAR2(255);
146 BEGIN
147 -- admp_get_sys_aods
148 -- Routine to return the user-defined system default admission offer
149 -- deferment status for given the system admission offer deferment status.
150 DECLARE
151 v_adm_offer_dfrmnt_status IGS_AD_OFRDFRMT_STAT.adm_offer_dfrmnt_status%TYPE;
152 CURSOR c_aods IS
153 SELECT aods.adm_offer_dfrmnt_status
154 FROM IGS_AD_OFRDFRMT_STAT aods
155 WHERE aods.s_adm_offer_dfrmnt_status = p_s_adm_offer_dfrmnt_status AND
156 aods.system_default_ind = 'Y' AND
157 aods.closed_ind = 'N';
158 BEGIN
159 -- If no record is found, return value is NULL
160 v_adm_offer_dfrmnt_status := NULL;
161 FOR v_aods_rec IN c_aods LOOP
162 IF (c_aods%ROWCOUNT>1)THEN
163 v_adm_offer_dfrmnt_status := NULL; -- more than one record found
164 exit;
165 END IF;
166 v_adm_offer_dfrmnt_status := v_aods_rec.adm_offer_dfrmnt_status;
167 END LOOP;
168 RETURN v_adm_offer_dfrmnt_status;
169 END;
170 EXCEPTION
171 WHEN OTHERS THEN
172 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
173 Fnd_Message.Set_Token('NAME','IGS_AD_GEN_009.admp_get_sys_aods');
174 IGS_GE_MSG_STACK.ADD;
175 App_Exception.Raise_Exception;
176 END admp_get_sys_aods;
177
178 Function Admp_Get_Sys_Aors(
179 p_s_adm_offer_resp_status IN VARCHAR2 )
180 RETURN VARCHAR2 IS
181 gv_other_detail VARCHAR2(255);
182 BEGIN
183 -- admp_get_sys_aors
184 -- Routine to return the user-defined system default admission offer
185 -- response status for given the system admission offer response status.
186 DECLARE
187 v_adm_offer_resp_status IGS_AD_OFR_RESP_STAT.adm_offer_resp_status%TYPE;
188 CURSOR c_aors IS
189 SELECT aors.adm_offer_resp_status
190 FROM IGS_AD_OFR_RESP_STAT aors
191 WHERE aors.s_adm_offer_resp_status = p_s_adm_offer_resp_status AND
192 aors.system_default_ind = 'Y' AND
193 aors.closed_ind = 'N';
194 BEGIN
195 -- If no record is found, return value is NULL
196 v_adm_offer_resp_status := NULL;
197 FOR v_aors_rec IN c_aors LOOP
198 IF (c_aors%ROWCOUNT>1)THEN
199 v_adm_offer_resp_status := NULL; -- more than one record found
200 exit;
201 END IF;
202 v_adm_offer_resp_status := v_aors_rec.adm_offer_resp_status;
203 END LOOP;
204 RETURN v_adm_offer_resp_status;
205 END;
206 EXCEPTION
207 WHEN OTHERS THEN
208 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
209 Fnd_Message.Set_Token('NAME','IGS_AD_GEN_009.admp_get_sys_aors');
210 IGS_GE_MSG_STACK.ADD;
211 App_Exception.Raise_Exception;
212 END admp_get_sys_aors;
213
214 Function Admp_Get_Sys_Aos(
215 p_s_adm_outcome_status IN VARCHAR2 )
216 RETURN VARCHAR2 IS
217 gv_other_detail VARCHAR2(255);
218 BEGIN
219 -- admp_get_sys_aos
220 -- Routine to return the user-defined system default admission outcome
221 -- status for given the system admission outcome status.
222 DECLARE
223 v_adm_outcome_status IGS_AD_OU_STAT.adm_outcome_status%TYPE;
224 CURSOR c_aos IS
225 SELECT aos.adm_outcome_status
226 FROM IGS_AD_OU_STAT aos
227 WHERE aos.s_adm_outcome_status = p_s_adm_outcome_status AND
228 aos.system_default_ind = 'Y' AND
229 aos.closed_ind = 'N';
230 BEGIN
231 -- If no record is found, return value is NULL
232 v_adm_outcome_status := NULL;
233 FOR v_aos_rec IN c_aos LOOP
234 IF (c_aos%ROWCOUNT>1)THEN
235 v_adm_outcome_status := NULL; -- more than one record found
236 exit;
237 END IF;
238 v_adm_outcome_status := v_aos_rec.adm_outcome_status;
239 END LOOP;
240 RETURN v_adm_outcome_status;
241 END;
242 EXCEPTION
243 WHEN OTHERS THEN
244 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
245 Fnd_Message.Set_Token('NAME','IGS_AD_GEN_009.admp_get_sys_aos');
246 IGS_GE_MSG_STACK.ADD;
247 App_Exception.Raise_Exception;
248 END admp_get_sys_aos;
249
250 Function Admp_Get_Sys_Auos(
251 p_s_adm_outcome_status IN VARCHAR2 )
252 RETURN VARCHAR2 IS
253 gv_other_detail VARCHAR2(255);
254 BEGIN --admp_get_sys_auos
255 --return the user-defined system default admission unit outcome status
256 --for a given system admission unit outcome status.
257 DECLARE
258 v_auos_cnt NUMBER DEFAULT 0;
259 v_adm_outcome_status IGS_AD_UNIT_OU_STAT.adm_unit_outcome_status%TYPE;
260 CURSOR c_auos IS
261 SELECT auos.adm_unit_outcome_status
262 FROM IGS_AD_UNIT_OU_STAT auos
263 WHERE s_adm_outcome_status = p_s_adm_outcome_status AND
264 system_default_ind = 'Y' AND
265 closed_ind = 'N';
266 BEGIN
267 FOR v_auos_rec IN c_auos LOOP
268 v_auos_cnt := v_auos_cnt + 1;
269 IF (v_auos_cnt > 1) THEN
270 exit;
271 END IF;
272 v_adm_outcome_status := v_auos_rec.adm_unit_outcome_status;
273 END LOOP;
274 IF (v_auos_cnt <> 1) THEN
275 RETURN NULL;
276 END IF;
277 RETURN v_adm_outcome_status;
278 EXCEPTION
279 WHEN OTHERS THEN
280 IF (c_auos%ISOPEN) THEN
281 CLOSE c_auos;
282 END IF;
283 RAISE;
284 END;
285 EXCEPTION
286 WHEN OTHERS THEN
287 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
288 Fnd_Message.Set_Token('NAME','IGS_AD_GEN_009.admp_get_sys_auos');
289 IGS_GE_MSG_STACK.ADD;
290 App_Exception.Raise_Exception;
291 END admp_get_sys_auos;
292
293 END IGS_AD_GEN_009;
[Home] [Help]