1 PACKAGE BODY igs_co_gen_003 AS
2 /* $Header: IGSCO20B.pls 120.1 2006/01/18 22:30:08 skpandey noship $ */
3
4 FUNCTION get_per_addr_for_corr (
5 p_person_id IN NUMBER,
6 p_case_type IN VARCHAR2
7 ) RETURN VARCHAR2 AS
8
9 /*******************************************************************************
10 Created by : rbezawad
11 Date created : 04-Feb-2002
12 Purpose : Function to Get Person Name and Address for Correspondence.
13
14 Known limitations/enhancements/remarks:
15
16 Change History: (who, when, what: NO CREATION RECORDS HERE!)
17 Who When What
18 asbala 15-JAN-2004 3349171: Incorrect usage of fnd_lookup_values view
19 *******************************************************************************/
20 --Local variable to Identify the the Line Break Character.
21 l_line_break VARCHAR2(10);
22
23 --
24 -- Cursor for selection of the person name in seperate parts to allow construction based on the user preferences.
25 --
26 CURSOR cur_person_name ( cp_person_id IN NUMBER,
27 cp_lookup_type fnd_lookup_values.lookup_type%TYPE,
28 cp_view_application_id fnd_lookup_values.view_application_id%TYPE,
29 cp_security_group_id fnd_lookup_values.security_group_id%TYPE) IS
30 SELECT NVL (lkup.meaning, per.person_title) ||
31 DECODE (per.person_first_name, NULL, NULL, ' ' ||
32 per.person_first_name) ||
33 DECODE (per.person_middle_name, NULL, NULL, ' ' ||
34 per.person_middle_name) ||
35 DECODE (per.person_last_name, NULL, NULL, ' ' ||
36 per.person_last_name) ||
37 DECODE (per.person_name_suffix, NULL, NULL, ' ' ||
38 per.person_name_suffix) person_name
39 FROM hz_parties per,
40 fnd_lookup_values lkup
41 WHERE per.party_id = cp_person_id
42 AND per.person_pre_name_adjunct = lkup.lookup_code (+)
43 AND lkup.lookup_type (+) = cp_lookup_type
44 AND lkup.language(+) = USERENV('LANG')
45 AND lkup.view_application_id(+) = cp_view_application_id
46 AND lkup.security_group_id(+) = cp_security_group_id;
47
48 --
49 -- Cursor for selection of the person address for Correspondence from HZ_PARTIES.
50 --
51 CURSOR cur_person_address (
52 cp_person_id IN NUMBER
53 ) IS
54 SELECT addr.address1 ||
55 DECODE (addr.address2, NULL, NULL, l_line_break || addr.address2) ||
56 DECODE (addr.address3, NULL, NULL, l_line_break || addr.address3) ||
57 DECODE (addr.address4, NULL, NULL, l_line_break || addr.address4) ||
58 DECODE (addr.city, NULL, NULL, l_line_break || addr.city) ||
59 DECODE (addr.state, NULL, NULL, l_line_break || addr.state) ||
60 DECODE (addr.postal_code, NULL, NULL, ' ' || addr.postal_code) ||
61 DECODE (tr.territory_short_name, NULL, NULL, l_line_break ||
62 tr.territory_short_name) person_address
63 FROM hz_parties addr,
64 fnd_territories_vl tr
65 WHERE addr.party_id = cp_person_id
66 AND addr.country = tr.territory_code(+);
67
68 --
69 -- Local Variables
70 --
71 l_name VARCHAR2(1000) ;
72 l_address VARCHAR2(1500) ;
73
74 --
75 -- Local Record Variables
76 --
77 l_person_rec cur_person_name%ROWTYPE;
78 l_person_address_rec cur_person_address%ROWTYPE;
79
80 BEGIN
81 l_name := NULL;
82 l_address := NULL;
83 -- Check if the required parameter values are passed as Null. If any of these are null then return NULL value
84 IF (p_person_id IS NULL) THEN
85 RETURN NULL;
86 END IF;
87
88 OPEN cur_person_name (p_person_id,'CONTACT_TITLE',222,0);
89 FETCH cur_person_name INTO l_person_rec;
90
91 IF (cur_person_name%NOTFOUND) THEN
92 CLOSE cur_person_name;
93 RETURN NULL;
94 ELSE
95 CLOSE cur_person_name;
96 -- Get the Name details for the person.
97 l_name := l_person_rec.person_name;
98
99 l_line_break := '<BR>';
100
101 -- Get the Address details of the Student for Correspondence purposes.
102 OPEN cur_person_address (p_person_id);
103 FETCH cur_person_address INTO l_person_address_rec;
104
105 IF (cur_person_address%NOTFOUND) THEN
106 l_address := ' ';
107 ELSE
108 l_address := l_person_address_rec.person_address;
109 END IF;
110 CLOSE cur_person_address;
111
112 END IF;
113
114 -- Return the concatenated value of Name and Address.
115 IF (p_case_type = 'UPPER') THEN
116 RETURN UPPER (l_name || l_line_break || l_address);
117 ELSIF (p_case_type = 'LOWER') THEN
118 RETURN LOWER (l_name || l_line_break || l_address);
119 ELSE
120 RETURN INITCAP (l_name || l_line_break || l_address);
121 END IF;
122
123 EXCEPTION
124 WHEN OTHERS THEN
125 IF (cur_person_name%ISOPEN) THEN
126 CLOSE cur_person_name;
127 ELSIF (cur_person_address%ISOPEN) THEN
128 CLOSE cur_person_address;
129 END IF;
130
131 RETURN NULL;
132
133 END get_per_addr_for_corr;
134
135
136 FUNCTION get_prg_appl_inst_dff_values (
137 p_person_id IN NUMBER,
138 p_admission_appl_number IN NUMBER,
139 p_nominated_course_cd IN VARCHAR2,
140 p_sequence_number IN NUMBER
141 ) RETURN VARCHAR2 IS
142
143 /*******************************************************************************
144 Created by : rbezawad
145 Date created : 04-Feb-2002
146 Purpose : Function to get Application Instance Descriptive Flex-Field values.
147
148 Known limitations/enhancements/remarks:
149
150 Change History: (who, when, what: NO CREATION RECORDS HERE!)
151 Who When What
152 skpandey 12-JAN-2006 Bug#4937960
153 Added application_id in query of cur_enabled_attributes cursor to optimize query
154 *******************************************************************************/
155 --
156 -- Cursor to fetch the Columns and Prompts for the Columns which are
157 -- registered for the Application Instance Descriptive Flex Field
158 --
159 CURSOR cur_enabled_attributes IS
160 SELECT usg.form_left_prompt form_left_prompt,
161 usg.application_column_name application_column_name
162 FROM fnd_descr_flex_col_usage_vl usg
163 WHERE usg.descriptive_flexfield_name = 'IGS_AD_APPL_INST_FLEX'
164 AND usg.enabled_flag = 'Y'
165 AND usg.application_id = 8405;
166
167 l_return_value VARCHAR2(6000);
168 l_column_value igs_ad_ps_appl_inst_all.attribute1%TYPE;
169
170 BEGIN
171
172 -- Check if the required parameter values are passed as Null. If any of these are null then return NULL value
173 IF ( p_person_id IS NULL OR p_admission_appl_number IS NULL OR p_nominated_course_cd IS NULL OR p_sequence_number IS NULL ) THEN
174 RETURN NULL;
175 END IF;
176
177 l_return_value := '';
178
179 FOR rec_cur_enabled_attributes IN cur_enabled_attributes LOOP
180 -- Get the registered flex filed Column values for the Admission Application Instance
181 EXECUTE IMMEDIATE ' SELECT '|| rec_cur_enabled_attributes.application_column_name||
182 ' FROM igs_ad_ps_appl_inst_all '||
183 ' WHERE person_id = :1' ||
184 ' AND admission_appl_number = :2'||
185 ' AND nominated_course_cd = :3' ||
186 ' AND sequence_number = :4'
187 INTO l_column_value
188 USING p_person_id, p_admission_appl_number, p_nominated_course_cd, p_sequence_number;
189
190 IF l_column_value IS NOT NULL THEN
191 -- Concatenate the Flex filed column values populated with the Prompts registered for that columns.
192 l_return_value := l_return_value || rec_cur_enabled_attributes.form_left_prompt || ': ' || l_column_value || ' <BR> ';
193 END IF;
194 END LOOP;
195
196 RETURN l_return_value;
197
198 EXCEPTION
199 WHEN OTHERS THEN
200 IF (cur_enabled_attributes%ISOPEN) THEN
201 CLOSE cur_enabled_attributes;
202 END IF;
203
204 RETURN NULL;
205
206 END get_prg_appl_inst_dff_values;
207
208
209 FUNCTION get_program_completion_dt (
210 p_course_cd IN igs_ad_ps_appl_inst_aplinst_v.course_cd%TYPE,
211 p_version_number IN igs_ad_ps_appl_inst_aplinst_v.crv_version_number%TYPE,
212 p_acad_cal_type IN igs_ad_ps_appl_inst_aplinst_v.acad_cal_type%TYPE,
213 p_adm_cal_type IN igs_ad_ps_appl_inst_aplinst_v.adm_cal_type%TYPE,
214 p_adm_ci_sequence_number IN igs_ad_ps_appl_inst_aplinst_v.adm_ci_sequence_number%TYPE,
215 p_attendance_type IN igs_ad_ps_appl_inst_aplinst_v.attendance_type%TYPE,
216 p_attendance_mode IN igs_ad_ps_appl_inst_aplinst_v.attendance_mode%TYPE,
217 p_location_cd IN igs_ad_ps_appl_inst_aplinst_v.location_cd%TYPE
218 ) RETURN DATE IS
219
220 /*******************************************************************************
221 Created by : rbezawad
222 Date created : 04-Feb-2002
223 Purpose : Function to get Expected Program Completion Date.
224
225 Known limitations/enhancements/remarks:
226
227 Change History: (who, when, what: NO CREATION RECORDS HERE!)
228 Who When What
229 knag 29-OCT-2002 For bug 2647482 Added parameters
230 p_attendance_type and p_location_cd
231 *******************************************************************************/
232
233 l_expected_completion_yr igs_ad_ps_appl_inst.expected_completion_yr%TYPE;
234 l_expected_completion_perd igs_ad_ps_appl_inst.expected_completion_perd%TYPE;
235 l_completion_dt DATE;
236 l_course_start_dt DATE;
237 BEGIN
238
239 -- Check if the required parameter values are passed as Null. If any of these are null then return NULL value
240 IF ( p_course_cd IS NULL OR p_version_number IS NULL OR p_adm_cal_type IS NULL OR p_adm_ci_sequence_number IS NULL) THEN
241 RETURN NULL;
242 END IF;
243
244 --Calculating the Program Version Start Date.
245 l_course_start_dt := igs_ad_gen_005.admp_get_crv_strt_dt ( p_adm_cal_type => p_adm_cal_type,
246 p_adm_ci_sequence_number => p_adm_ci_sequence_number);
247
248 --Calculating the Projected Program Version Completion Date.
249 igs_ad_gen_004.admp_get_crv_comp_dt (p_course_cd => p_course_cd ,
250 p_crv_version_number => p_version_number,
251 p_cal_type => p_acad_cal_type ,
252 p_attendance_type => p_attendance_type,
253 p_start_dt => l_course_start_dt,
254 p_expected_completion_yr => l_expected_completion_yr,
255 p_expected_completion_perd => l_expected_completion_perd,
256 p_completion_dt => l_completion_dt,
257 p_attendance_mode => p_attendance_mode,
258 p_location_cd => p_location_cd);
259
260 RETURN l_completion_dt;
261
262 EXCEPTION
263 WHEN OTHERS THEN
264 RETURN NULL;
265
266 END get_program_completion_dt;
267
268
269 FUNCTION get_residency_dff_values (
270 p_resident_details_id IN NUMBER
271 ) RETURN VARCHAR2 IS
272
273 /*******************************************************************************
274 Created by : rbezawad
275 Date created : 04-Feb-2002
276 Purpose : Function to get the Residency Descriptive Flex-Field values.
277
278 Known limitations/enhancements/remarks:
279
280 Change History: (who, when, what: NO CREATION RECORDS HERE!)
281 Who When What
282
283 *******************************************************************************/
284 --
285 -- Cursor to fetch the Columns and Prompts for the Columns which are
286 -- registered for the Residency Details Descriptive Flex Field
287 --
288 CURSOR cur_enabled_attributes IS
289 SELECT usg.form_left_prompt form_left_prompt,
290 usg.application_column_name application_column_name
291 FROM fnd_descr_flex_col_usage_vl usg
292 WHERE usg.descriptive_flexfield_name = 'IGS_PE_PERS_RESIDENCY_FLEX'
293 AND usg.enabled_flag = 'Y'
294 AND usg.application_id = 8405;
295
296 l_return_value VARCHAR2(6000);
297 l_column_value igs_pe_res_dtls_all.attribute1%TYPE;
298
299 BEGIN
300
301 -- Check if the required parameter values are passed as Null. If any of these are null then return NULL value
302 IF ( p_resident_details_id IS NULL) THEN
303 RETURN NULL;
304 END IF;
305
306 l_return_value := '';
307
308 FOR rec_cur_enabled_attributes IN cur_enabled_attributes LOOP
309 -- Get the registered flex filed Column values for the Resident Details
310 EXECUTE IMMEDIATE ' SELECT ' || rec_cur_enabled_attributes.application_column_name ||
311 ' FROM igs_pe_res_dtls_all '||
312 ' WHERE resident_details_id = :1'
313 INTO l_column_value
314 USING p_resident_details_id;
315
316 IF l_column_value IS NOT NULL THEN
317 -- Concatenate the Flex filed column values populated with the Prompts registered for that columns.
318 l_return_value := l_return_value || rec_cur_enabled_attributes.form_left_prompt || ': ' || l_column_value || ' <BR> ';
319 END IF;
320 END LOOP;
321
322 RETURN l_return_value;
323
324 EXCEPTION
325 WHEN OTHERS THEN
326 IF (cur_enabled_attributes%ISOPEN) THEN
327 CLOSE cur_enabled_attributes;
328 END IF;
329
330 RETURN NULL;
331
332 END get_residency_dff_values;
333
334
335 FUNCTION get_student_citizenship_status(
336 p_person_id IN NUMBER
337 ) RETURN VARCHAR2 IS
338
339 /*******************************************************************************
340 Created by : rbezawad
341 Date created : 04-Feb-2002
342 Purpose : Function to get the Student Citizenship Status.
343
344 Known limitations/enhancements/remarks:
345
346 Change History: (who, when, what: NO CREATION RECORDS HERE!)
347 Who When What
348
349 *******************************************************************************/
350 --
351 --Cursor to get the Student Citizenship Status.
352 --
353 CURSOR cur_citizenship IS
354 SELECT peit.pei_information1 restatus_code, pecc.meaning restatus_desc
355 FROM igs_pe_eit peit,
356 igs_lookups_view pecc
357 WHERE peit.pei_information1 = pecc.lookup_code
358 AND peit.person_id = p_person_id
359 AND peit.start_date <= SYSDATE
360 AND NVL(peit.end_date,SYSDATE) >= SYSDATE
361 AND pecc.lookup_type='PE_CITI_STATUS'
362 AND ENABLED_FLAG = 'Y';
363
364 l_restatus_code igs_pe_eit.pei_information1%TYPE;
365 l_restatus_desc igs_lookups_view.meaning%TYPE;
366
367 BEGIN
368
369 -- Check if the required parameter values are passed as Null. If any of these are null then return NULL value
370 IF (p_person_id IS NULL) THEN
371 RETURN NULL;
372 END IF;
373
374 --Get the Student Citizenship Status and return the status description.
375 OPEN cur_citizenship;
376 FETCH cur_citizenship INTO l_restatus_code, l_restatus_desc;
377
378 IF cur_citizenship%FOUND THEN
379 CLOSE cur_citizenship;
380 RETURN l_restatus_desc;
381 END IF;
382
383 CLOSE cur_citizenship;
384 RETURN NULL;
385
386 EXCEPTION
387 WHEN OTHERS THEN
388 IF (cur_citizenship%ISOPEN) THEN
389 CLOSE cur_citizenship;
390 END IF;
391
392 RETURN NULL;
393
394 END get_student_citizenship_status;
395
396 END igs_co_gen_003;
[Home] [Help]