1 PACKAGE BODY igs_ad_gen_006 AS
2 /* $Header: IGSAD06B.pls 115.10 2003/12/01 13:15:54 rboddu ship $ */
3 FUNCTION admp_get_encmb_dt(
4 p_adm_cal_type IN VARCHAR2 ,
5 p_adm_ci_sequence_number IN NUMBER )
6 RETURN DATE IS
7 BEGIN -- admp_get_encmb_dt
8 -- This module gets the encumbrance check date when passed an admission
9 -- calendar type and sequence number.
10 DECLARE
11 v_encmb_chk_dt IGS_CA_DA_INST_V.alias_val%TYPE;
12 CURSOR c_encmb_dt IS
13 SELECT daiv.alias_val
14 FROM IGS_CA_DA_INST_V daiv,
15 IGS_AD_CAL_CONF sacc
16 WHERE daiv.cal_type = p_adm_cal_type AND
17 daiv.ci_sequence_number = p_adm_ci_sequence_number AND
18 daiv.dt_alias = sacc.adm_appl_encmb_chk_dt_alias
19 ORDER BY daiv.alias_val DESC;
20 BEGIN
21 OPEN c_encmb_dt;
22 FETCH c_encmb_dt INTO v_encmb_chk_dt;
23 IF (c_encmb_dt%FOUND) THEN
24 CLOSE c_encmb_dt;
25 RETURN v_encmb_chk_dt;
26 ELSE
27 CLOSE c_encmb_dt;
28 RETURN IGS_GE_DATE.IGSDATE(NULL);
29 END IF;
30 END;
31 EXCEPTION
32 WHEN OTHERS THEN
33 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
34 Fnd_Message.Set_Token('NAME','IGS_AD_GEN_006.admp_get_encmb_dt');
35 IGS_GE_MSG_STACK.ADD;
36 App_Exception.Raise_Exception;
37 END admp_get_encmb_dt;
38
39 PROCEDURE admp_get_enq_pp(
40 p_oracle_username IN VARCHAR2 ,
41 p_enq_acad_cal_type OUT NOCOPY VARCHAR2 ,
42 p_enq_acad_ci_sequence_number OUT NOCOPY NUMBER ,
43 p_enq_acad_alternate_code OUT NOCOPY VARCHAR2 ,
44 p_enq_acad_abbreviation OUT NOCOPY VARCHAR2 ,
45 p_enq_adm_cal_type OUT NOCOPY VARCHAR2 ,
46 p_enq_adm_ci_sequence_number OUT NOCOPY NUMBER ,
47 p_enq_adm_alternate_code OUT NOCOPY VARCHAR2 ,
48 p_enq_adm_abbreviation OUT NOCOPY VARCHAR2 )
49 IS
50 BEGIN -- admp_get_enq_pp
51 -- Routine to get the admission enquiry person preferences
52 DECLARE
53 CURSOR c_ppenqv (p_person_id IN IGS_PE_PERSON.person_id%TYPE) IS
54 SELECT ppenqv.enq_acad_cal_type,
55 ppenqv.enq_acad_ci_sequence_number,
56 ppenqv.enq_acad_alternate_code,
57 cat1.abbreviation,
58 ppenqv.enq_adm_cal_type,
59 ppenqv.enq_adm_ci_sequence_number,
60 ppenqv.enq_adm_alternate_code,
61 cat2.abbreviation
62 FROM igs_pe_person_prefs_enq_v ppenqv, /* Removed cartesian join with IGS_PE_PERSON. Bug 3150054 */
63 igs_ca_inst ci1,
64 igs_ca_type cat1,
65 igs_ca_inst ci2,
66 igs_ca_type cat2
67 WHERE
68 ppenqv.person_id = p_person_id AND
69 ci1.cal_type (+) = ppenqv.enq_acad_cal_type AND
70 ci1.sequence_number (+) = ppenqv.enq_acad_ci_sequence_number AND
71 cat1.cal_type (+) = ci1.cal_type AND
72 ci2.cal_type (+) = ppenqv.enq_adm_cal_type AND
73 ci2.sequence_number (+) = ppenqv.enq_adm_ci_sequence_number AND
74 cat2.cal_type (+) = ci2.cal_type;
75
76 v_person_id IGS_PE_PERSON.PERSON_ID%TYPE;
77 BEGIN
78 v_person_id := FND_GLOBAL.USER_ID;
79 OPEN c_ppenqv(v_person_id);
80 FETCH c_ppenqv INTO p_enq_acad_cal_type,
81 p_enq_acad_ci_sequence_number,
82 p_enq_acad_alternate_code,
83 p_enq_acad_abbreviation,
84 p_enq_adm_cal_type,
85 p_enq_adm_ci_sequence_number,
86 p_enq_adm_alternate_code,
87 p_enq_adm_abbreviation;
88 IF(c_ppenqv%NOTFOUND) THEN
89 p_enq_acad_cal_type := NULL;
90 p_enq_acad_ci_sequence_number := NULL;
91 p_enq_acad_alternate_code := NULL;
92 p_enq_acad_abbreviation := NULL;
93 p_enq_adm_cal_type := NULL;
94 p_enq_adm_ci_sequence_number := NULL;
95 p_enq_adm_alternate_code := NULL;
96 p_enq_adm_abbreviation := NULL;
97 END IF;
98 CLOSE c_ppenqv;
99 RETURN;
100 END;
101 EXCEPTION
102 WHEN OTHERS THEN
103 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
104 Fnd_Message.Set_Token('NAME','IGS_AD_GEN_006.admp_get_enq_pp');
105 IGS_GE_MSG_STACK.ADD;
106 App_Exception.Raise_Exception;
107 END admp_get_enq_pp;
108
109 FUNCTION admp_get_itt_amttyp(
110 p_intake_target_type IN VARCHAR2 )
111 RETURN VARCHAR2 IS
112 BEGIN -- admp_get_itt_amttyp
113 -- Description: This module retrieves the s_amount_type from
114 -- intake_target_type for a given intake_target_type.
115 DECLARE
116 v_s_amount_type IGS_AD_INTAK_TRG_TYP.s_amount_type%TYPE;
117 CURSOR c_itt IS
118 SELECT itt.s_amount_type
119 FROM IGS_AD_INTAK_TRG_TYP itt
120 WHERE itt.intake_target_type = p_intake_target_type;
121 BEGIN
122 OPEN c_itt;
123 FETCH c_itt INTO v_s_amount_type;
124 IF (c_itt%NOTFOUND) THEN
125 CLOSE c_itt;
126 RETURN NULL;
127 ELSE
128 CLOSE c_itt;
129 RETURN v_s_amount_type;
130 END IF;
131 END;
132 EXCEPTION
133 WHEN OTHERS THEN
134 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
135 Fnd_Message.Set_Token('NAME','IGS_AD_GEN_006.admp_get_itt_amttyp');
136 IGS_GE_MSG_STACK.ADD;
137 App_Exception.Raise_Exception;
138 END admp_get_itt_amttyp;
139
140 FUNCTION admp_get_iv_addr(
141 p_person_id IN NUMBER )
142 RETURN VARCHAR2 IS
143 BEGIN -- admp_get_iv_addr
144 -- This module retrieves name and address information of international agents
145 -- for use on letters.
146 DECLARE
147 cst_case_type CONSTANT VARCHAR2(6) := 'NORMAL';
148 cst_name_style CONSTANT VARCHAR2(5) := 'TITLE';
149 v_agent_person_id IGS_PE_VISA.agent_person_id%TYPE;
150 v_out_string VARCHAR2(2000);
151 CURSOR c_iv IS
152 SELECT agent_person_id
153 FROM IGS_PE_VISA iv
154 WHERE person_id = p_person_id AND
155 agent_person_id IS NOT NULL AND
156 (visa_expiry_date > TRUNC(SYSDATE) OR
157 visa_expiry_date IS NULL);
158 BEGIN
159 OPEN c_iv;
160 FETCH c_iv INTO v_agent_person_id;
161 IF (c_iv%NOTFOUND) THEN
162 CLOSE c_iv;
163 RETURN NULL;
164 END IF;
165 CLOSE c_iv;
166 v_out_string:= IGS_GE_GEN_001.genp_get_addr(v_agent_person_id,
167 NULL,
168 NULL,
169 NULL,
170 NULL,
171 cst_case_type,
172 'N',
173 cst_name_style,
174 'Y');
175 RETURN v_out_string;
176 END;
177 EXCEPTION
178 WHEN OTHERS THEN
179 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
180 Fnd_Message.Set_Token('NAME','IGS_AD_GEN_006.admp_get_iv_addr');
181 IGS_GE_MSG_STACK.ADD;
182 App_Exception.Raise_Exception;
183 END admp_get_iv_addr;
184
185 FUNCTION admp_get_let_resp_dt(
186 p_person_id IN NUMBER ,
187 p_admission_appl_number IN NUMBER ,
188 p_nominated_course_cd IN VARCHAR2 ,
189 p_acai_sequence_number IN NUMBER )
190 RETURN VARCHAR2 IS
191 BEGIN
192 DECLARE
193 v_out_date IGS_AD_PS_APPL_INST.offer_response_dt%TYPE;
194 CURSOR c_acai IS
195 SELECT acai.offer_response_dt
196 FROM IGS_AD_PS_APPL_INST acai
197 WHERE acai.person_id = p_person_id AND
198 acai.admission_appl_number = p_admission_appl_number AND
199 acai.nominated_course_cd = p_nominated_course_cd AND
200 acai.sequence_number = p_acai_sequence_number;
201 BEGIN
202 -- Validate parameters
203 IF (p_person_id IS NULL OR
204 p_admission_appl_number IS NULL OR
205 p_nominated_course_cd IS NULL OR
206 p_acai_sequence_number IS NULL) THEN
207 RETURN NULL;
208 END IF;
209 OPEN c_acai;
210 FETCH c_acai INTO v_out_date;
211 IF (c_acai%NOTFOUND) THEN
212 CLOSE c_acai;
213 RETURN NULL;
214 END IF;
215 CLOSE c_acai;
216 RETURN TO_CHAR(v_out_date, 'DD/MM/YYYY');
217 EXCEPTION
218 WHEN OTHERS THEN
219 IF (c_acai%ISOPEN) THEN
220 CLOSE c_acai;
221 END IF;
222 App_Exception.Raise_Exception;
223 END;
224 EXCEPTION
225 WHEN OTHERS THEN
226 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
227 Fnd_Message.Set_Token('NAME','IGS_AD_GEN_006.admp_get_let_resp_dt');
228 IGS_GE_MSG_STACK.ADD;
229 App_Exception.Raise_Exception;
230 END admp_get_let_resp_dt;
231
232 FUNCTION admp_get_lvl_qual(
233 p_tac_level_of_qual IN VARCHAR2 )
234 RETURN VARCHAR2 IS
235 BEGIN -- admp_get_lvl_qual
236 -- This module finds the user defined tertiary education level of qualification
237 -- from the TAC level of qualification.
238 DECLARE
239 CURSOR c_telq IS
240 SELECT tertiary_edu_lvl_qual
241 FROM IGS_AD_TER_ED_LVL_QF
242 WHERE tac_level_of_qual = p_tac_level_of_qual AND
243 closed_ind = 'N';
244 v_tertiary_edu_lvl_qual
245 IGS_AD_TER_ED_LVL_QF.tertiary_edu_lvl_qual%TYPE DEFAULT NULL;
246 BEGIN
247 -- Cursor handling
248 OPEN c_telq ;
249 FETCH c_telq INTO v_tertiary_edu_lvl_qual;
250 CLOSE c_telq;
251 -- Return the appropriate value, null if record is not found
252 RETURN v_tertiary_edu_lvl_qual;
253 END;
254 EXCEPTION
255 WHEN OTHERS THEN
256 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
257 Fnd_Message.Set_Token('NAME','IGS_AD_GEN_006.admp_get_lvl_qual');
258 IGS_GE_MSG_STACK.ADD;
259 App_Exception.Raise_Exception;
260 END admp_get_lvl_qual;
261
262 END igs_ad_gen_006;
[Home] [Help]