1 PACKAGE BODY IGS_HE_EXTRACT_DLHE_FIELDS_PKG AS
2 /* $Header: IGSHE9DB.pls 120.1 2006/02/13 17:25:48 jbaber noship $ */
3
4
5 PROCEDURE write_to_log(p_message IN VARCHAR2)
6 IS
7 /***************************************************************
8 Created By : smaddali
9 Date Created By : 9-apr-03
10 Purpose : This procedures writes onto the log file
11 Known Limitations,Enhancements or Remarks:
12 Change History :
13 Who When What
14 ***************************************************************/
15 BEGIN
16
17 Fnd_File.Put_Line(Fnd_File.Log, p_message);
18
19 END write_to_log;
20
21
22 PROCEDURE get_survey_method
23 (p_dlhe_method IN igs_he_stdnt_dlhe.survey_method%TYPE,
24 p_hesa_method OUT NOCOPY VARCHAR2)
25 IS
26 /***************************************************************
27 Created By : smaddali
28 Date Created By : 9-apr-03
29 Purpose : This procedure gets the HESA Method of data collection
30 mapped to dlhe record's survey method
31 Known Limitations,Enhancements or Remarks:
32 Change History :
33 Who When What
34 ***************************************************************/
35
36 l_he_code_map_val igs_he_code_map_val%ROWTYPE := NULL;
37
38 BEGIN
39 l_he_code_map_val.association_code := 'OSS_HESA_METHOD_ASSOC';
40 l_he_code_map_val.map2 := p_dlhe_method;
41
42 IF p_dlhe_method IS NOT NULL
43 THEN
44 igs_he_extract2_pkg.get_map_values
45 (p_he_code_map_val => l_he_code_map_val,
46 p_value_from => 'MAP1',
47 p_return_value => p_hesa_method);
48
49 END IF;
50
51 EXCEPTION
52 WHEN OTHERS
53 THEN
54 write_to_log(SQLERRM);
55
56 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
57 Fnd_Message.Set_Token('NAME','IGS_HE_EXTRACT_FIELDS_PKG.get_survey_method');
58 igs_ge_msg_stack.add;
59 App_Exception.Raise_Exception;
60 END get_survey_method;
61
62
63 PROCEDURE get_empcir
64 (p_hesa_method IN igs_he_ex_rn_dat_fd.value%TYPE,
65 p_dlhe_employment IN igs_he_stdnt_dlhe.Employment%TYPE,
66 p_hesa_empcir OUT NOCOPY VARCHAR2)
67 IS
68 /***************************************************************
69 Created By : smaddali
70 Date Created By : 9-apr-03
71 Purpose : This procedure gets the HESA Employment circumstances mapped
72 to the dlhe record's employement
73 Known Limitations,Enhancements or Remarks:
74 Change History :
75 Who When What
76 smaddali 04-jul-03 modified default processing for bug#3036995 to add one more condition
77 ***************************************************************/
78
79 l_he_code_map_val igs_he_code_map_val%ROWTYPE := NULL;
80
81 BEGIN
82 -- if method is 8 or 9 or employment value not found then return Default value.
83 IF p_hesa_method IN ('8','9') OR p_dlhe_employment IS NULL THEN
84 -- default value processing
85 p_hesa_empcir := 'XX' ;
86 ELSIF p_dlhe_employment IS NOT NULL THEN
87 -- Get the hesa code mapped to the dlhe record's employement field
88 l_he_code_map_val.association_code := 'OSS_HESA_EMPCIR_ASSOC';
89 l_he_code_map_val.map2 := p_dlhe_employment;
90
91 igs_he_extract2_pkg.get_map_values
92 (p_he_code_map_val => l_he_code_map_val,
93 p_value_from => 'MAP1',
94 p_return_value => p_hesa_empcir);
95 END IF;
96
97 EXCEPTION
98 WHEN OTHERS
99 THEN
100 write_to_log(SQLERRM);
101
102 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
103 Fnd_Message.Set_Token('NAME','IGS_HE_EXTRACT_FIELDS_PKG.get_empcir');
104 igs_ge_msg_stack.add;
105 App_Exception.Raise_Exception;
106 END get_empcir;
107
108
109 PROCEDURE get_mode_study
110 (p_hesa_method IN igs_he_ex_rn_dat_fd.value%TYPE,
111 p_dlhe_further_study IN igs_he_stdnt_dlhe.Further_study%TYPE,
112 p_hesa_modstudy OUT NOCOPY VARCHAR2)
113 IS
114 /***************************************************************
115 Created By : smaddali
116 Date Created By : 9-apr-03
117 Purpose : This procedure gets the HESA Mode of Study
118 Known Limitations,Enhancements or Remarks:
119 Change History :
120 Who When What
121 smaddali 04-jul-03 modified default processing for bug#3036995 to add one more condition
122 ***************************************************************/
123
124 l_he_code_map_val igs_he_code_map_val%ROWTYPE := NULL;
125
126 BEGIN
127
128 -- if method is 8 or 9 or Further study value not found then return Default value.
129 IF p_hesa_method IN ('8','9') OR p_dlhe_further_study IS NULL THEN
130 -- default value processing
131 p_hesa_modstudy := 'X' ;
132 ELSIF p_dlhe_further_study IS NOT NULL THEN
133 -- get the hesa code mapped to the dlhe record's further study field
134 l_he_code_map_val.association_code := 'OSS_HESA_MODSTUDY_ASSOC';
135 l_he_code_map_val.map2 := p_dlhe_further_study;
136
137 igs_he_extract2_pkg.get_map_values
138 (p_he_code_map_val => l_he_code_map_val,
139 p_value_from => 'MAP1',
140 p_return_value => p_hesa_modstudy);
141 END IF;
142
143 EXCEPTION
144 WHEN OTHERS
145 THEN
146 write_to_log(SQLERRM);
147
148 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
149 Fnd_Message.Set_Token('NAME','IGS_HE_EXTRACT_FIELDS_PKG.get_mode_study');
150 igs_ge_msg_stack.add;
151 App_Exception.Raise_Exception;
152 END get_mode_study;
153
154
155 PROCEDURE get_makedo
156 (p_hesa_method IN igs_he_ex_rn_dat_fd.value%TYPE,
157 p_hesa_empcir IN igs_he_ex_rn_dat_fd.value%TYPE,
158 p_dlhe_Emp_business IN igs_he_stdnt_dlhe.Employer_business%TYPE,
159 p_hesa_makedo OUT NOCOPY VARCHAR2)
160 IS
161 /***************************************************************
162 Created By : smaddali
163 Date Created By : 9-apr-03
164 Purpose : This procedure gets the HESA Nature of employers business
165 Known Limitations,Enhancements or Remarks:
166 Change History :
167 Who When What
168 ***************************************************************/
169
170 BEGIN
171 IF ( p_hesa_method IN ('8','9') OR p_hesa_empcir IN ('06','07','08','09','10','11','12','13','14','XX' )) AND
172 p_hesa_empcir NOT IN ('01','02','03','04','05') THEN
173 -- default value processing
174 p_hesa_makedo := 'XXXX' ;
175 ELSIF p_dlhe_Emp_business IS NOT NULL THEN
176 -- return value of employer_business if present else return default value
177 p_hesa_makedo := p_dlhe_Emp_business ;
178 ELSIF p_hesa_empcir NOT IN ('01','02','03','04','05') THEN
179 p_hesa_makedo := 'XXXX' ;
180 END IF ;
181
182 EXCEPTION
183 WHEN OTHERS
184 THEN
185 write_to_log(SQLERRM);
186
187 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
188 Fnd_Message.Set_Token('NAME','IGS_HE_EXTRACT_FIELDS_PKG.get_makedo');
189 igs_ge_msg_stack.add;
190 App_Exception.Raise_Exception;
191 END get_makedo;
192
193
194 PROCEDURE get_sic
195 (p_hesa_method IN igs_he_ex_rn_dat_fd.value%TYPE,
196 p_hesa_empcir IN igs_he_ex_rn_dat_fd.value%TYPE,
197 p_dlhe_Emp_class IN igs_he_stdnt_dlhe.Employer_classification%TYPE,
198 p_hesa_sic OUT NOCOPY VARCHAR2)
199 IS
200 /***************************************************************
201 Created By : smaddali
202 Date Created By : 9-apr-03
203 Purpose : This procedure gets the HESA employers business classification
204 Known Limitations,Enhancements or Remarks:
205 Change History :
206 Who When What
207 ***************************************************************/
208
209 l_he_code_map_val igs_he_code_map_val%ROWTYPE := NULL;
210
211 BEGIN
212 IF ( p_hesa_method IN ('8','9') OR p_hesa_empcir IN ('06','07','08','09','10','11','12','13','14','XX' )) AND
213 p_hesa_empcir NOT IN ('01','02','03','04','05') THEN
214 -- default value processing
215 p_hesa_sic := 'XXXX' ;
216 ELSIF p_dlhe_Emp_class IS NOT NULL THEN
217 -- if employement classfication is given then return hesa code mapped to that else return default value
218 l_he_code_map_val.association_code := 'OSS_HESA_SIC_ASSOC';
219 l_he_code_map_val.map2 := p_dlhe_Emp_class;
220
221 igs_he_extract2_pkg.get_map_values
222 (p_he_code_map_val => l_he_code_map_val,
223 p_value_from => 'MAP1',
224 p_return_value => p_hesa_sic);
225
226 ELSIF p_hesa_empcir NOT IN ('01','02','03','04','05') THEN
227 p_hesa_sic := 'XXXX' ;
228 END IF ;
229
230 EXCEPTION
231 WHEN OTHERS
232 THEN
233 write_to_log(SQLERRM);
234
235 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
236 Fnd_Message.Set_Token('NAME','IGS_HE_EXTRACT_FIELDS_PKG.get_sic');
237 igs_ge_msg_stack.add;
238 App_Exception.Raise_Exception;
239 END get_sic;
240
241
242 PROCEDURE get_emp_loc
243 (p_hesa_method IN igs_he_ex_rn_dat_fd.value%TYPE,
244 p_hesa_empcir IN igs_he_ex_rn_dat_fd.value%TYPE,
245 p_dlhe_Emp_postcode IN igs_he_stdnt_dlhe.Employer_postcode%TYPE,
246 p_dlhe_emp_country IN igs_he_stdnt_dlhe.Employer_country%TYPE,
247 p_hesa_locemp OUT NOCOPY VARCHAR2)
248 IS
249 /***************************************************************
250 Created By : smaddali
251 Date Created By : 9-apr-03
252 Purpose : This procedure gets the HESA Location of employment
253 Known Limitations,Enhancements or Remarks:
254 Change History :
255 Who When What
256 ***************************************************************/
257
258 BEGIN
259 IF ( p_hesa_method IN ('8','9') OR p_hesa_empcir IN ('06','07','08','09','10','11','12','13','14','XX' )) AND
260 p_hesa_empcir NOT IN ('01','02','03','04','05') THEN
261 -- default value processing
262 p_hesa_locemp := 'XXXXXXXX' ;
263 ELSIF p_dlhe_Emp_postcode IS NOT NULL THEN
264 -- if employer postcode is given then return that
265 p_hesa_locemp := p_dlhe_Emp_postcode ;
266 ELSIF p_dlhe_emp_country IS NOT NULL THEN
267 -- if employer countrycode is given then return that else return default value
268 p_hesa_locemp := p_dlhe_emp_country ;
269 ELSIF p_hesa_empcir NOT IN ('01','02','03','04','05') THEN
270 p_hesa_locemp := 'XXXXXXXX' ;
271 END IF ;
272
273 EXCEPTION
274 WHEN OTHERS
275 THEN
276 write_to_log(SQLERRM);
277
278 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
279 Fnd_Message.Set_Token('NAME','IGS_HE_EXTRACT_FIELDS_PKG.get_emp_loc');
280 igs_ge_msg_stack.add;
281 App_Exception.Raise_Exception;
282 END get_emp_loc;
283
284
285
286 PROCEDURE get_job_title
287 (p_hesa_method IN igs_he_ex_rn_dat_fd.value%TYPE,
288 p_hesa_empcir IN igs_he_ex_rn_dat_fd.value%TYPE,
289 p_dlhe_jobtitle IN igs_he_stdnt_dlhe.Job_title%TYPE,
290 p_hesa_jobtitle OUT NOCOPY VARCHAR2)
291 IS
292 /***************************************************************
293 Created By : smaddali
294 Date Created By : 9-apr-03
295 Purpose : This procedure gets the HESA Job title
296 Known Limitations,Enhancements or Remarks:
297 Change History :
298 Who When What
299 ***************************************************************/
300
301 BEGIN
302 IF ( p_hesa_method IN ('8','9') OR p_hesa_empcir IN ('06','07','08','09','10','11','12','13','14','XX' ) ) AND
303 p_hesa_empcir NOT IN ('01','02','03','04','05') THEN
304 -- default value processing
305 p_hesa_jobtitle := 'XXXX' ;
306 ELSIF p_dlhe_jobtitle IS NOT NULL THEN
307 -- if dlhe job title is given then return that else return default value
308 p_hesa_jobtitle := p_dlhe_jobtitle;
309 ELSIF p_hesa_empcir NOT IN ('01','02','03','04','05') THEN
310 p_hesa_jobtitle := 'XXXX' ;
311 END IF;
312
313 EXCEPTION
314 WHEN OTHERS
315 THEN
316 write_to_log(SQLERRM);
317
318 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
319 Fnd_Message.Set_Token('NAME','IGS_HE_EXTRACT_FIELDS_PKG.get_job_title');
320 igs_ge_msg_stack.add;
321 App_Exception.Raise_Exception;
322 END get_job_title;
323
324
325 PROCEDURE get_occ_class
326 (p_hesa_method IN igs_he_ex_rn_dat_fd.value%TYPE,
327 p_hesa_empcir IN igs_he_ex_rn_dat_fd.value%TYPE,
328 p_dlhe_job_class IN igs_he_stdnt_dlhe.Job_classification%TYPE,
329 p_hesa_soc OUT NOCOPY VARCHAR2)
330 IS
331 /***************************************************************
332 Created By : smaddali
333 Date Created By : 9-apr-03
334 Purpose : This procedure gets the HESA Standard Occupational Classification
335 Known Limitations,Enhancements or Remarks:
336 Change History :
337 Who When What
338 ***************************************************************/
339
340 l_he_code_map_val igs_he_code_map_val%ROWTYPE := NULL;
341
342 BEGIN
343 IF ( p_hesa_method IN ('8','9') OR p_hesa_empcir IN ('06','07','08','09','10','11','12','13','14','XX' )) AND
344 p_hesa_empcir NOT IN ('01','02','03','04','05') THEN
345 -- default value processing
346 p_hesa_soc := 'XXXXX' ;
347 ELSIF p_dlhe_job_class IS NOT NULL THEN
348 -- if dlhe jo classification is given then return its hesa mapped code else return default value
349 l_he_code_map_val.association_code := 'OSS_HESA_SOCDLHE_ASSOC';
350 l_he_code_map_val.map2 := p_dlhe_job_class;
351
352 igs_he_extract2_pkg.get_map_values
353 (p_he_code_map_val => l_he_code_map_val,
354 p_value_from => 'MAP1',
355 p_return_value => p_hesa_soc);
356
357 ELSIF p_hesa_empcir NOT IN ('01','02','03','04','05') THEN
358 p_hesa_soc := 'XXXXX' ;
359 END IF ;
360
361 EXCEPTION
362 WHEN OTHERS
363 THEN
364 write_to_log(SQLERRM);
365
366 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
367 Fnd_Message.Set_Token('NAME','IGS_HE_EXTRACT_FIELDS_PKG.get_occ_class');
368 igs_ge_msg_stack.add;
369 App_Exception.Raise_Exception;
370 END get_occ_class;
371
372
373
374 PROCEDURE get_emp_size
375 (p_hesa_method IN igs_he_ex_rn_dat_fd.value%TYPE,
376 p_hesa_empcir IN igs_he_ex_rn_dat_fd.value%TYPE,
377 p_dlhe_emp_size IN igs_he_stdnt_dlhe.Employer_size%TYPE,
378 p_hesa_empsize OUT NOCOPY VARCHAR2)
379 IS
380 /***************************************************************
381 Created By : smaddali
382 Date Created By : 9-apr-03
383 Purpose : This procedure gets the HESA Employer size
384 Known Limitations,Enhancements or Remarks:
385 Change History :
386 Who When What
387 smaddali 04-jul-03 modified default processing for bug#3036995 to add one more condition
388 ***************************************************************/
389
390 l_he_code_map_val igs_he_code_map_val%ROWTYPE := NULL;
391
392 BEGIN
393
394 -- if method is 8 or 9 or empcir between 06 to 14 or if employer_size is null then return default
395 IF ( p_hesa_method IN ('8','9') OR p_hesa_empcir IN ('06','07','08','09','10','11','12','13','14','XX' )
396 OR p_dlhe_emp_size IS NULL
397 ) THEN
398 -- default value processing
399 p_hesa_empsize := 'X' ;
400 ELSIF p_dlhe_emp_size IS NOT NULL THEN
401 -- return the hesa code mapped to dlhe employement size
402 l_he_code_map_val.association_code := 'OSS_HESA_EMPSIZE_ASSOC';
403 l_he_code_map_val.map2 := p_dlhe_emp_size;
404
405 igs_he_extract2_pkg.get_map_values
406 (p_he_code_map_val => l_he_code_map_val,
407 p_value_from => 'MAP1',
408 p_return_value => p_hesa_empsize);
409 END IF;
410
411 EXCEPTION
412 WHEN OTHERS
413 THEN
414 write_to_log(SQLERRM);
415
416 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
417 Fnd_Message.Set_Token('NAME','IGS_HE_EXTRACT_FIELDS_PKG.get_emp_size');
418 igs_ge_msg_stack.add;
419 App_Exception.Raise_Exception;
420 END get_emp_size;
421
422
423 PROCEDURE get_emp_duration
424 (p_hesa_method IN igs_he_ex_rn_dat_fd.value%TYPE,
425 p_hesa_empcir IN igs_he_ex_rn_dat_fd.value%TYPE,
426 p_dlhe_emp_duration IN igs_he_stdnt_dlhe.Job_duration%TYPE,
427 p_hesa_duration OUT NOCOPY VARCHAR2)
428 IS
429 /***************************************************************
430 Created By : smaddali
431 Date Created By : 9-apr-03
432 Purpose : This procedure gets the HESA Duration of employment
433 Known Limitations,Enhancements or Remarks:
434 Change History :
435 Who When What
436 smaddali 04-jul-03 modified default processing for bug#3036995 to add one more condition
437 ***************************************************************/
438
439 l_he_code_map_val igs_he_code_map_val%ROWTYPE := NULL;
440
441 BEGIN
442
443 -- if method is 9 or empcir between 06 to 14 or if job_duration is null then return default
444 IF ( p_hesa_method IN ('8','9') OR p_hesa_empcir IN ('06','07','08','09','10','11','12','13','14','XX' )
445 OR p_dlhe_emp_duration IS NULL
446 ) THEN
447 -- default value processing
448 p_hesa_duration := 'X' ;
449 ELSIF p_dlhe_emp_duration IS NOT NULL THEN
450 -- return the hesa code mapped to dlhe job duration
451 l_he_code_map_val.association_code := 'OSS_HESA_DURATION_ASSOC';
452 l_he_code_map_val.map2 := p_dlhe_emp_duration;
453
454 igs_he_extract2_pkg.get_map_values
455 (p_he_code_map_val => l_he_code_map_val,
456 p_value_from => 'MAP1',
457 p_return_value => p_hesa_duration);
458 END IF;
459
460 EXCEPTION
461 WHEN OTHERS
462 THEN
463 write_to_log(SQLERRM);
464
465 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
466 Fnd_Message.Set_Token('NAME','IGS_HE_EXTRACT_FIELDS_PKG.get_emp_duration');
467 igs_ge_msg_stack.add;
468 App_Exception.Raise_Exception;
469 END get_emp_duration;
470
471
472 PROCEDURE get_salary
473 (p_hesa_method IN igs_he_ex_rn_dat_fd.value%TYPE,
474 p_hesa_empcir IN igs_he_ex_rn_dat_fd.value%TYPE,
475 p_dlhe_Job_salary IN igs_he_stdnt_dlhe.Job_salary%TYPE,
476 p_hesa_salary OUT NOCOPY VARCHAR2)
477 IS
478 /***************************************************************
479 Created By : smaddali
480 Date Created By : 9-apr-03
481 Purpose : This procedure gets the HESA Salary
482 Known Limitations,Enhancements or Remarks:
483 Change History :
484 Who When What
485 ***************************************************************/
486
487 BEGIN
488 IF ( p_hesa_method IN ('8','9') OR p_hesa_empcir IN ('06','07','08','09','10','11','12','13','14','XX' )) THEN
489 -- default value processing
490 p_hesa_salary := 'XXXXXX' ;
491 ELSIF p_dlhe_Job_salary IS NOT NULL THEN
492 -- if dlhe salary is given then return that else return the default value
493 p_hesa_salary := p_dlhe_Job_salary ;
494 ELSE
495 p_hesa_salary := 'XXXXXX' ;
496 END IF ;
497
498 EXCEPTION
499 WHEN OTHERS
500 THEN
501 write_to_log(SQLERRM);
502
503 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
504 Fnd_Message.Set_Token('NAME','IGS_HE_EXTRACT_FIELDS_PKG.get_salary');
505 igs_ge_msg_stack.add;
506 App_Exception.Raise_Exception;
507 END get_salary;
508
509
510
511 PROCEDURE get_qual_req
512 (p_hesa_method IN igs_he_ex_rn_dat_fd.value%TYPE,
513 p_hesa_empcir IN igs_he_ex_rn_dat_fd.value%TYPE,
514 p_dlhe_qual_req IN igs_he_stdnt_dlhe.Qualification_requirement%TYPE,
515 p_hesa_qualreq OUT NOCOPY VARCHAR2)
516 IS
517 /***************************************************************
518 Created By : smaddali
519 Date Created By : 9-apr-03
520 Purpose : This procedure gets the HESA Qualification required for job
521 Known Limitations,Enhancements or Remarks:
522 Change History :
523 Who When What
524 smaddali 04-jul-03 modified default processing for bug#3036995 to add one more condition
525 ***************************************************************/
526
527 l_he_code_map_val igs_he_code_map_val%ROWTYPE := NULL;
528
529 BEGIN
530
531 -- if method is 9 or empcir between 06 to 14 or if qual_requirement is null then return default
532 IF ( p_hesa_method IN ('8','9') OR p_hesa_empcir IN ('06','07','08','09','10','11','12','13','14','XX' )
533 OR p_dlhe_qual_req IS NULL
534 ) THEN
535 -- default value processing
536 p_hesa_qualreq := 'X' ;
537 ELSIF p_dlhe_qual_req IS NOT NULL THEN
538 -- return the hesa code mapped to dlhe qualification requirement
539 l_he_code_map_val.association_code := 'OSS_HESA_QUALREQ_ASSOC';
540 l_he_code_map_val.map2 := p_dlhe_qual_req;
541
542 igs_he_extract2_pkg.get_map_values
543 (p_he_code_map_val => l_he_code_map_val,
544 p_value_from => 'MAP1',
545 p_return_value => p_hesa_qualreq);
546 END IF;
547
548 EXCEPTION
549 WHEN OTHERS
550 THEN
551 write_to_log(SQLERRM);
552
553 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
554 Fnd_Message.Set_Token('NAME','IGS_HE_EXTRACT_FIELDS_PKG.get_qual_req');
555 igs_ge_msg_stack.add;
556 App_Exception.Raise_Exception;
557 END get_qual_req;
558
559
560 PROCEDURE get_emp_imp
561 (p_hesa_method IN igs_he_ex_rn_dat_fd.value%TYPE,
562 p_hesa_empcir IN igs_he_ex_rn_dat_fd.value%TYPE,
563 p_dlhe_emp_imp IN igs_he_stdnt_dlhe.Qualification_importance%TYPE,
564 p_hesa_empimp OUT NOCOPY VARCHAR2)
565 IS
566 /***************************************************************
567 Created By : smaddali
568 Date Created By : 9-apr-03
569 Purpose : This procedure gets the HESA Qualification is important for job
570 Known Limitations,Enhancements or Remarks:
571 Change History :
572 Who When What
573 smaddali 04-jul-03 modified default processing for bug#3036995 to add one more condition
574 ***************************************************************/
575
576 l_he_code_map_val igs_he_code_map_val%ROWTYPE := NULL;
577
578 BEGIN
579
580 -- if method is 9 or empcir between 06 to 14 or if qual_importance is null then return default
581 IF ( p_hesa_method IN ('3','4','8','9') OR p_hesa_empcir IN ('06','07','08','09','10','11','12','13','14','XX' )
582 OR p_dlhe_emp_imp IS NULL
583 ) THEN
584 -- default value processing
585 p_hesa_empimp := 'X' ;
586 ELSIF p_dlhe_emp_imp IS NOT NULL THEN
587 -- return the hesa code mapped to dlhe qualification importance
588 l_he_code_map_val.association_code := 'OSS_HESA_EMPIMP_ASSOC';
589 l_he_code_map_val.map2 := p_dlhe_emp_imp;
590
591 igs_he_extract2_pkg.get_map_values
592 (p_he_code_map_val => l_he_code_map_val,
593 p_value_from => 'MAP1',
594 p_return_value => p_hesa_empimp);
595 END IF;
596
597 EXCEPTION
598 WHEN OTHERS
599 THEN
600 write_to_log(SQLERRM);
601
602 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
603 Fnd_Message.Set_Token('NAME','IGS_HE_EXTRACT_FIELDS_PKG.get_emp_imp');
604 igs_ge_msg_stack.add;
605 App_Exception.Raise_Exception;
606 END get_emp_imp;
607
608
609 PROCEDURE get_career
610 (p_hesa_reason IN igs_he_stdnt_dlhe.Job_reason1%TYPE,
611 p_hesa_career OUT NOCOPY VARCHAR2)
612 IS
613 /***************************************************************
614 Created By : smaddali
615 Date Created By : 9-apr-03
616 Purpose : This procedure gets the HESA Career related code 1 to 8
617 Known Limitations,Enhancements or Remarks:
618 Change History :
619 Who When What
620 ***************************************************************/
621
622 BEGIN
623 -- if job reason 1 to 8 is Y then return 1 else return 0
624 IF p_hesa_reason ='Y' THEN
625 p_hesa_career := '1' ;
626 ELSE
627 p_hesa_career := '0';
628 END IF ;
629
630 EXCEPTION
631 WHEN OTHERS
632 THEN
633 write_to_log(SQLERRM);
634
635 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
636 Fnd_Message.Set_Token('NAME','IGS_HE_EXTRACT_FIELDS_PKG.get_career');
637 igs_ge_msg_stack.add;
638 App_Exception.Raise_Exception;
639 END get_career;
640
641
642 PROCEDURE get_job_find
643 (p_hesa_method IN igs_he_ex_rn_dat_fd.value%TYPE,
644 p_hesa_empcir IN igs_he_ex_rn_dat_fd.value%TYPE,
645 p_dlhe_job_source IN igs_he_stdnt_dlhe.Job_source%TYPE,
646 p_hesa_jobfnd OUT NOCOPY VARCHAR2)
647 IS
648 /***************************************************************
649 Created By : smaddali
650 Date Created By : 9-apr-03
651 Purpose : This procedure gets the HESA How found job
652 Known Limitations,Enhancements or Remarks:
653 Change History :
654 Who When What
655 smaddali 04-jul-03 modified default processing for bug#3036995 to add one more condition
656 ***************************************************************/
657
658 l_he_code_map_val igs_he_code_map_val%ROWTYPE := NULL;
659
660 BEGIN
661 -- if method is 8 or 9 or empcir between 06 to 14 or if Job_source is null then return default
662 IF ( p_hesa_method IN ('8','9') OR p_hesa_empcir IN ('06','07','08','09','10','11','12','13','14','XX' )
663 OR p_dlhe_job_source IS NULL
664 ) THEN
665 -- default value processing
666 p_hesa_jobfnd := 'X' ;
667 ELSIF p_dlhe_job_source IS NOT NULL THEN
668 -- return the hesa code mapped to dlhe job_source
669 l_he_code_map_val.association_code := 'OSS_HESA_JOBFND_ASSOC';
670 l_he_code_map_val.map2 := p_dlhe_job_source;
671
672 igs_he_extract2_pkg.get_map_values
673 (p_he_code_map_val => l_he_code_map_val,
674 p_value_from => 'MAP1',
675 p_return_value => p_hesa_jobfnd);
676 END IF;
677
678 EXCEPTION
679 WHEN OTHERS
680 THEN
681 write_to_log(SQLERRM);
682
683 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
684 Fnd_Message.Set_Token('NAME','IGS_HE_EXTRACT_FIELDS_PKG.get_job_find');
685 igs_ge_msg_stack.add;
686 App_Exception.Raise_Exception;
687 END get_job_find;
688
689
690 PROCEDURE get_prev_emp
691 (p_hesa_method IN igs_he_ex_rn_dat_fd.value%TYPE,
692 p_hesa_empcir IN igs_he_ex_rn_dat_fd.value%TYPE,
693 p_dlhe_previous_job IN igs_he_stdnt_dlhe.Previous_job%TYPE,
694 p_hesa_prevemp OUT NOCOPY VARCHAR2)
695 IS
696 /***************************************************************
697 Created By : smaddali
698 Date Created By : 9-apr-03
699 Purpose : This procedure gets the HESA Previously employed
700 Known Limitations,Enhancements or Remarks:
701 Change History :
702 Who When What
703 smaddali 04-jul-03 modified default processing for bug#3036995 to add one more condition
704 ***************************************************************/
705
706 l_he_code_map_val igs_he_code_map_val%ROWTYPE := NULL;
707
708 BEGIN
709 -- if method is 8 or 9 or empcir between 06 to 14 or if Previous_job is null then return default
710 IF ( p_hesa_method IN ('8','9') OR p_hesa_empcir IN ('06','07','08','09','10','11','12','13','14','XX' )
711 OR p_dlhe_previous_job IS NULL
712 ) THEN
713 -- default value processing
714 p_hesa_prevemp := 'X' ;
715 ELSIF p_dlhe_previous_job IS NOT NULL THEN
716 -- return the hesa code mapped to dlhe previous job
717 l_he_code_map_val.association_code := 'OSS_HESA_PREVEMP_ASSOC';
718 l_he_code_map_val.map2 := p_dlhe_previous_job;
719
720 igs_he_extract2_pkg.get_map_values
721 (p_he_code_map_val => l_he_code_map_val,
722 p_value_from => 'MAP1',
723 p_return_value => p_hesa_prevemp);
724 END IF;
725
726 EXCEPTION
727 WHEN OTHERS
728 THEN
729 write_to_log(SQLERRM);
730
731 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
732 Fnd_Message.Set_Token('NAME','IGS_HE_EXTRACT_FIELDS_PKG.get_prev_emp');
733 igs_ge_msg_stack.add;
734 App_Exception.Raise_Exception;
735 END get_prev_emp;
736
737
738 PROCEDURE get_prev_emp_cat
739 (p_hesa_method IN igs_he_ex_rn_dat_fd.value%TYPE,
740 p_hesa_empcir IN igs_he_ex_rn_dat_fd.value%TYPE,
741 p_hesa_prevemp IN igs_he_ex_rn_dat_fd.value%TYPE,
742 p_dlhe_previous_jobtype IN igs_he_stdnt_dlhe.Previous_jobtype1%TYPE,
743 p_hesa_prevcat OUT NOCOPY VARCHAR2)
744 IS
745 /***************************************************************
746 Created By : smaddali
747 Date Created By : 9-apr-03
748 Purpose : This procedure gets the HESA Category of previous employment 1 to 6
749 Known Limitations,Enhancements or Remarks:
750 Change History :
751 Who When What
752 ***************************************************************/
753
754 BEGIN
755 IF ( p_hesa_method IN ('8','9') OR p_hesa_empcir IN ('06','07','08','09','10','11','12','13','14','XX' )
756 OR p_hesa_prevemp = '4' ) THEN
757 -- default value processing
758 p_hesa_prevcat := 'X' ;
759 ELSIF p_dlhe_previous_jobtype = 'Y' THEN
760 -- if previous jobtype 1 to 6 ='Y then return 1 else return 0
761 p_hesa_prevcat := '1' ;
762 ELSE
763 p_hesa_prevcat := '0';
764 END IF ;
765
766 EXCEPTION
767 WHEN OTHERS
768 THEN
769 write_to_log(SQLERRM);
770
771 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
772 Fnd_Message.Set_Token('NAME','IGS_HE_EXTRACT_FIELDS_PKG.get_prev_emp_cat');
773 igs_ge_msg_stack.add;
774 App_Exception.Raise_Exception;
775 END get_prev_emp_cat;
776
777
778 PROCEDURE get_nat_study
779 (p_hesa_method IN igs_he_ex_rn_dat_fd.value%TYPE,
780 p_hesa_modstudy IN igs_he_ex_rn_dat_fd.value%TYPE,
781 p_dlhe_study_type IN igs_he_stdnt_dlhe.Further_study_type%TYPE,
782 p_hesa_natstudy OUT NOCOPY VARCHAR2)
783 IS
784 /***************************************************************
785 Created By : smaddali
786 Date Created By : 9-apr-03
787 Purpose : This procedure gets the HESA Nature of study/training
788 Known Limitations,Enhancements or Remarks:
789 Change History :
790 Who When What
791 smaddali 04-jul-03 modified default processing for bug#3036995 to add one more condition
792 ***************************************************************/
793
794 l_he_code_map_val igs_he_code_map_val%ROWTYPE := NULL;
795
796 BEGIN
797
798 -- if method is 8 or 9 or empcir between 06 to 14 or if Further_study_type is null then return default
799 IF ( p_hesa_method IN ('8','9') OR p_hesa_modstudy IN ('3','X' ) OR p_dlhe_study_type IS NULL
800 ) THEN
801 -- default value processing
802 p_hesa_natstudy := 'X' ;
803 ELSIF p_dlhe_study_type IS NOT NULL THEN
804 -- return hesa code mapped to dlhe further study type
805 l_he_code_map_val.association_code := 'OSS_HESA_NATSTUDY_ASSOC';
806 l_he_code_map_val.map2 := p_dlhe_study_type;
807
808 igs_he_extract2_pkg.get_map_values
809 (p_he_code_map_val => l_he_code_map_val,
810 p_value_from => 'MAP1',
811 p_return_value => p_hesa_natstudy);
812 END IF;
813
814 EXCEPTION
815 WHEN OTHERS
816 THEN
817 write_to_log(SQLERRM);
818
819 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
820 Fnd_Message.Set_Token('NAME','IGS_HE_EXTRACT_FIELDS_PKG.get_nat_study');
821 igs_ge_msg_stack.add;
822 App_Exception.Raise_Exception;
823 END get_nat_study;
824
825
826 PROCEDURE get_train_subj
827 (p_hesa_method IN igs_he_ex_rn_dat_fd.value%TYPE,
828 p_hesa_modstudy IN igs_he_ex_rn_dat_fd.value%TYPE,
829 p_hesa_natstudy IN igs_he_ex_rn_dat_fd.value%TYPE,
830 p_dlhe_crse_train_subj IN igs_he_stdnt_dlhe.Course_training_subject%TYPE,
831 p_dlhe_res_train_subj IN igs_he_stdnt_dlhe.Research_training_subject%TYPE,
832 p_hesa_profsoct OUT NOCOPY VARCHAR2)
833 IS
834 /***************************************************************
835 Created By : smaddali
836 Date Created By : 9-apr-03
837 Purpose : This procedure gets the HESA Professional subject of training
838 Known Limitations,Enhancements or Remarks:
839 Change History :
840 Who When What
841 ***************************************************************/
842
843 l_he_code_map_val igs_he_code_map_val%ROWTYPE := NULL;
844
845 BEGIN
846 IF ( p_hesa_method IN ('8','9') OR p_hesa_modstudy IN ('3','X' ) OR p_hesa_natstudy IN ('3','4') ) THEN
847 -- default value processing
848 p_hesa_profsoct := 'XXXXX' ;
849 ELSIF p_dlhe_crse_train_subj IS NOT NULL THEN
850 -- if course training subject is given then return its hesa code
851 l_he_code_map_val.association_code := 'OSS_HESA_SOCDLHE_ASSOC';
852 l_he_code_map_val.map2 := p_dlhe_crse_train_subj;
853
854 igs_he_extract2_pkg.get_map_values
855 (p_he_code_map_val => l_he_code_map_val,
856 p_value_from => 'MAP1',
857 p_return_value => p_hesa_profsoct);
858
859 ELSIF p_dlhe_res_train_subj IS NOT NULL THEN
860 -- if research training subject is given then return its hesa code else return default value
861 l_he_code_map_val.association_code := 'OSS_HESA_SOCDLHE_ASSOC';
862 l_he_code_map_val.map2 := p_dlhe_res_train_subj;
863
864 igs_he_extract2_pkg.get_map_values
865 (p_he_code_map_val => l_he_code_map_val,
866 p_value_from => 'MAP1',
867 p_return_value => p_hesa_profsoct);
868
869 ELSE
870 p_hesa_profsoct := 'XXXXX' ;
871 END IF ;
872
873 EXCEPTION
874 WHEN OTHERS
875 THEN
876 write_to_log(SQLERRM);
877
878 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
879 Fnd_Message.Set_Token('NAME','IGS_HE_EXTRACT_FIELDS_PKG.get_train_subj');
880 igs_ge_msg_stack.add;
881 App_Exception.Raise_Exception;
882 END get_train_subj;
883
884
885 PROCEDURE get_inst_prov
886 (p_hesa_method IN igs_he_ex_rn_dat_fd.value%TYPE,
887 p_hesa_modstudy IN igs_he_ex_rn_dat_fd.value%TYPE,
888 p_hesa_natstudy IN igs_he_ex_rn_dat_fd.value%TYPE,
889 p_dlhe_study_prov IN igs_he_stdnt_dlhe.Further_study_provider%TYPE,
890 p_hesa_instprov OUT NOCOPY VARCHAR2)
891 IS
892 /***************************************************************
893 Created By : smaddali
894 Date Created By : 9-apr-03
895 Purpose : This procedure gets the HESA Institution providing study
896 Known Limitations,Enhancements or Remarks:
897 Change History :
898 Who When What
899 ***************************************************************/
900
901 BEGIN
902 IF ( p_hesa_method IN ('3','4','8','9') OR p_hesa_modstudy IN ('3','X' ) OR p_hesa_natstudy IN ('3','4') ) THEN
903 -- default value processing
904 p_hesa_instprov := 'XXXX' ;
905 ELSIF p_dlhe_study_prov IS NOT NULL THEN
906 -- return dlhe further study provide if given else return default value
907 p_hesa_instprov := p_dlhe_study_prov ;
908 ELSE
909 p_hesa_instprov := 'XXXX' ;
910 END IF ;
911
912 EXCEPTION
913 WHEN OTHERS
914 THEN
915 write_to_log(SQLERRM);
916
917 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
918 Fnd_Message.Set_Token('NAME','IGS_HE_EXTRACT_FIELDS_PKG.get_inst_prov');
919 igs_ge_msg_stack.add;
920 App_Exception.Raise_Exception;
921 END get_inst_prov;
922
923
924 PROCEDURE get_type_qual
925 (p_hesa_method IN igs_he_ex_rn_dat_fd.value%TYPE,
926 p_hesa_modstudy IN igs_he_ex_rn_dat_fd.value%TYPE,
927 p_dlhe_study_qualaim IN igs_he_stdnt_dlhe.Further_study_qualaim%TYPE,
928 p_hesa_typequal OUT NOCOPY VARCHAR2)
929 IS
930 /***************************************************************
931 Created By : smaddali
932 Date Created By : 9-apr-03
933 Purpose : This procedure gets the HESA Type of qualification
934 Known Limitations,Enhancements or Remarks:
935 Change History :
936 Who When What
937 smaddali 04-jul-03 modified default processing for bug#3036995 to add one more condition
938 smaddali 23-sep-03 Removed parameter p_hesa_natstudy for build HECR011 , bug#3051597
939 ***************************************************************/
940 l_he_code_map_val igs_he_code_map_val%ROWTYPE := NULL;
941
942 BEGIN
943 -- if method is 9 or mode study is 3 or if Further_study_qualaim is null then return default
944 IF ( p_hesa_method IN ('8','9') OR p_hesa_modstudy IN ('3','X' )
945 OR p_dlhe_study_qualaim IS NULL
946 ) THEN
947 -- default value processing
948 p_hesa_typequal := 'XX' ;
949 ELSIF p_dlhe_study_qualaim IS NOT NULL THEN
950 -- return the hesa code mapped to dlhe study qualification aim
951 l_he_code_map_val.association_code := 'OSS_HESA_TYPEQUAL_ASSOC';
952 l_he_code_map_val.map2 := p_dlhe_study_qualaim;
953
954 igs_he_extract2_pkg.get_map_values
955 (p_he_code_map_val => l_he_code_map_val,
956 p_value_from => 'MAP1',
957 p_return_value => p_hesa_typequal);
958 END IF ;
959
960 EXCEPTION
961 WHEN OTHERS
962 THEN
963 write_to_log(SQLERRM);
964
965 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
966 Fnd_Message.Set_Token('NAME','IGS_HE_EXTRACT_FIELDS_PKG.get_type_qual');
967 igs_ge_msg_stack.add;
968 App_Exception.Raise_Exception;
969 END get_type_qual;
970
971
972 PROCEDURE get_study_reason2
973 (p_hesa_method IN igs_he_ex_rn_dat_fd.value%TYPE,
974 p_hesa_modstudy IN igs_he_ex_rn_dat_fd.value%TYPE,
975 p_dlhe_study_reason IN igs_he_stdnt_dlhe.Study_reason2%TYPE,
976 p_hesa_secint OUT NOCOPY VARCHAR2)
977 IS
978 /***************************************************************
979 Created By : smaddali
980 Date Created By : 9-apr-03
981 Purpose : This procedure gets the HESA Reason for taking another course 2 to 7
982 Known Limitations,Enhancements or Remarks:
983 Change History :
984 Who When What
985 smaddali 23-sep-03 Removed parameter p_hesa_natstudy, p_hesa_emppaid for build HECR011 , bug#3051597
986 ***************************************************************/
987
988 BEGIN
989 IF ( p_hesa_method IN ('3','4','8','9') OR p_hesa_modstudy IN ('3','X' ) ) THEN
990 -- default value processing
991 p_hesa_secint := 'X' ;
992 ELSIF p_dlhe_study_reason = 'Y' THEN
993 -- if reason for study 2 to 7 is Y then return 1 else return 0
994 p_hesa_secint := '1' ;
995 ELSE
996 p_hesa_secint := '0';
997 END IF ;
998
999 EXCEPTION
1000 WHEN OTHERS
1001 THEN
1002 write_to_log(SQLERRM);
1003
1004 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
1005 Fnd_Message.Set_Token('NAME','IGS_HE_EXTRACT_FIELDS_PKG.get_study_reason2');
1006 igs_ge_msg_stack.add;
1007 App_Exception.Raise_Exception;
1008 END get_study_reason2;
1009
1010
1011 PROCEDURE get_study_reason8
1012 (p_hesa_method IN igs_he_ex_rn_dat_fd.value%TYPE,
1013 p_hesa_modstudy IN igs_he_ex_rn_dat_fd.value%TYPE,
1014 p_dlhe_other_study_reason IN igs_he_stdnt_dlhe.Other_study_reason%TYPE,
1015 p_dlhe_no_study_reason IN igs_he_stdnt_dlhe.No_other_study_reason%TYPE,
1016 p_hesa_secint8 OUT NOCOPY VARCHAR2)
1017 IS
1018 /***************************************************************
1019 Created By : smaddali
1020 Date Created By : 9-apr-03
1021 Purpose : This procedure gets the HESA Reason for taking another course 8
1022 Known Limitations,Enhancements or Remarks:
1023 Change History :
1024 Who When What
1025 smaddali 23-sep-03 Removed parameter p_hesa_natstudy, p_hesa_emppaid for build HECR011 , bug#3051597
1026 ***************************************************************/
1027
1028 BEGIN
1029 IF ( p_hesa_method IN ('3','4','8','9') OR p_hesa_modstudy IN ('3','X' ) ) THEN
1030 -- default value processing
1031 p_hesa_secint8 := 'X' ;
1032 ELSIF p_dlhe_other_study_reason IS NOT NULL THEN
1033 -- if other reason for study given then return 1
1034 p_hesa_secint8 := '1' ;
1035 ELSIF p_dlhe_no_study_reason = 'Y' THEN
1036 -- if no other reason for study is given then return 0 else return default value
1037 p_hesa_secint8 := '0';
1038 ELSE
1039 p_hesa_secint8 := 'X' ;
1040 END IF ;
1041
1042 EXCEPTION
1043 WHEN OTHERS
1044 THEN
1045 write_to_log(SQLERRM);
1046
1047 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
1048 Fnd_Message.Set_Token('NAME','IGS_HE_EXTRACT_FIELDS_PKG.get_study_reason8');
1049 igs_ge_msg_stack.add;
1050 App_Exception.Raise_Exception;
1051 END get_study_reason8;
1052
1053
1054 PROCEDURE get_funding_source
1055 (p_hesa_method IN igs_he_ex_rn_dat_fd.value%TYPE,
1056 p_hesa_modstudy IN igs_he_ex_rn_dat_fd.value%TYPE,
1057 p_dlhe_funding_source IN igs_he_stdnt_dlhe.Funding_source%TYPE,
1058 p_hesa_fundstudy OUT NOCOPY VARCHAR2)
1059 IS
1060 /***************************************************************
1061 Created By : smaddali
1062 Date Created By : 9-apr-03
1063 Purpose : This procedure gets the HESA funding further study
1064 Known Limitations,Enhancements or Remarks:
1065 Change History :
1066 Who When What
1067 smaddali 04-jul-03 modified default processing for bug#3036995 to add one more condition
1068 smaddali 23-sep-03 Removed parameter p_hesa_natstudy, p_hesa_emppaid for build HECR011 , bug#3051597
1069 ***************************************************************/
1070
1071 l_he_code_map_val igs_he_code_map_val%ROWTYPE := NULL;
1072
1073 BEGIN
1074 -- if method is 9 or modestudy is 3 or nature of study is 3,4 or if Funding_source is null then return default
1075 IF ( p_hesa_method IN ('8','9') OR p_hesa_modstudy IN ('3','X' ) OR
1076 p_dlhe_funding_source IS NULL
1077 ) THEN
1078 -- default value processing
1079 p_hesa_fundstudy := 'X' ;
1080 ELSIF p_dlhe_funding_source IS NOT NULL THEN
1081 -- return the hesa code mapped to dlhe funding source
1082 l_he_code_map_val.association_code := 'OSS_HESA_FUNDSTDY_ASSOC';
1083 l_he_code_map_val.map2 := p_dlhe_funding_source;
1084
1085 igs_he_extract2_pkg.get_map_values
1086 (p_he_code_map_val => l_he_code_map_val,
1087 p_value_from => 'MAP1',
1088 p_return_value => p_hesa_fundstudy);
1089 END IF ;
1090
1091 EXCEPTION
1092 WHEN OTHERS
1093 THEN
1094 write_to_log(SQLERRM);
1095
1096 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
1097 Fnd_Message.Set_Token('NAME','IGS_HE_EXTRACT_FIELDS_PKG.get_funding_source');
1098 igs_ge_msg_stack.add;
1099 App_Exception.Raise_Exception;
1100 END get_funding_source;
1101
1102
1103 PROCEDURE get_teaching_emp
1104 (p_hesa_method IN igs_he_ex_rn_dat_fd.value%TYPE,
1105 p_dlhe_qualified IN igs_he_stdnt_dlhe.Qualified_teacher%TYPE,
1106 p_dlhe_teaching IN igs_he_stdnt_dlhe.Teacher_teaching%TYPE ,
1107 p_dlhe_seeking IN igs_he_stdnt_dlhe.Teacher_seeking%TYPE ,
1108 p_hesa_tchemp OUT NOCOPY VARCHAR2)
1109 IS
1110 /***************************************************************
1111 Created By : smaddali
1112 Date Created By : 9-apr-03
1113 Purpose : This procedure gets the HESA Teaching employment marker
1114 Known Limitations,Enhancements or Remarks:
1115 Change History :
1116 Who When What
1117 ***************************************************************/
1118
1119 BEGIN
1120 IF p_hesa_method IN ('8','9') THEN
1121 -- default value processing
1122 p_hesa_tchemp := 'X' ;
1123 ELSIF p_dlhe_qualified ='N' THEN
1124 -- if not a qualified teacher then return 9
1125 p_hesa_tchemp := '9' ;
1126 ELSIF p_dlhe_teaching ='Y' THEN
1127 -- if a qualified teacher and is teaching then return 3
1128 p_hesa_tchemp := '3' ;
1129 ELSIF p_dlhe_seeking = 'Y' THEN
1130 -- if a qualified teacher seeking teaching post then return 1
1131 p_hesa_tchemp := '1' ;
1135 END IF ;
1132 ELSIF p_dlhe_teaching ='N' AND p_dlhe_seeking = 'N' THEN
1133 -- if a qualified teacher not working or seeking job then return 2
1134 p_hesa_tchemp := '2' ;
1136
1137 EXCEPTION
1138 WHEN OTHERS
1139 THEN
1140 write_to_log(SQLERRM);
1141
1142 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
1143 Fnd_Message.Set_Token('NAME','IGS_HE_EXTRACT_FIELDS_PKG.get_teaching_emp');
1144 igs_ge_msg_stack.add;
1145 App_Exception.Raise_Exception;
1146 END get_teaching_emp;
1147
1148
1149
1150 PROCEDURE get_teaching_sector
1151 (p_hesa_method IN igs_he_ex_rn_dat_fd.value%TYPE,
1152 p_hesa_tchemp IN igs_he_ex_rn_dat_fd.value%TYPE,
1153 p_dlhe_teach_sector IN igs_he_stdnt_dlhe.Teaching_sector%TYPE,
1154 p_hesa_teachsct OUT NOCOPY VARCHAR2)
1155 IS
1156 /***************************************************************
1157 Created By : smaddali
1158 Date Created By : 9-apr-03
1159 Purpose : This procedure gets the HESA Teaching sector
1160 Known Limitations,Enhancements or Remarks:
1161 Change History :
1162 Who When What
1163 smaddali 04-jul-03 modified default processing for bug#3036995 to add one more condition
1164 ***************************************************************/
1165
1166 l_he_code_map_val igs_he_code_map_val%ROWTYPE := NULL;
1167
1168 BEGIN
1169 -- if method is 9 or teaching employement in 1,2,9 or if Teaching_sector is null then return default
1170 IF ( p_hesa_method IN ('8','9') OR p_hesa_tchemp IN ('1','2','9','X' ) OR p_dlhe_teach_sector IS NULL
1171 ) THEN
1172 -- default value processing
1173 p_hesa_teachsct := 'X' ;
1174 ELSIF p_dlhe_teach_sector IS NOT NULL THEN
1175 -- return the hesa code mapped to the dlhe teaching sector
1176 l_he_code_map_val.association_code := 'OSS_HESA_TEACHSCT_ASSOC';
1177 l_he_code_map_val.map2 := p_dlhe_teach_sector;
1178
1179 igs_he_extract2_pkg.get_map_values
1180 (p_he_code_map_val => l_he_code_map_val,
1181 p_value_from => 'MAP1',
1182 p_return_value => p_hesa_teachsct);
1183 END IF ;
1184
1185 EXCEPTION
1186 WHEN OTHERS
1187 THEN
1188 write_to_log(SQLERRM);
1189
1190 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
1191 Fnd_Message.Set_Token('NAME','IGS_HE_EXTRACT_FIELDS_PKG.get_teaching_sector');
1192 igs_ge_msg_stack.add;
1193 App_Exception.Raise_Exception;
1194 END get_teaching_sector;
1195
1196
1197 PROCEDURE get_teaching_phase
1198 (p_hesa_method IN igs_he_ex_rn_dat_fd.value%TYPE,
1199 p_hesa_tchemp IN igs_he_ex_rn_dat_fd.value%TYPE,
1200 p_dlhe_teach_level IN igs_he_stdnt_dlhe.Teaching_level%TYPE,
1201 p_hesa_teachphs OUT NOCOPY VARCHAR2)
1202 IS
1203 /***************************************************************
1204 Created By : smaddali
1205 Date Created By : 9-apr-03
1206 Purpose : This procedure gets the HESA Teaching Phase
1207 Known Limitations,Enhancements or Remarks:
1208 Change History :
1209 Who When What
1210 smaddali 04-jul-03 modified default processing for bug#3036995 to add one more condition
1211 ***************************************************************/
1212
1213 l_he_code_map_val igs_he_code_map_val%ROWTYPE := NULL;
1214
1215 BEGIN
1216 -- if method is 9 or teaching employement in 1,2,9 or if Teaching_level is null then return default
1217 IF ( p_hesa_method IN ('8','9') OR p_hesa_tchemp IN ('1','2','9','X' )
1218 OR p_dlhe_teach_level IS NULL
1219 ) THEN
1220 -- default value processing
1221 p_hesa_teachphs := 'X' ;
1222 ELSIF p_dlhe_teach_level IS NOT NULL THEN
1223 -- return the hesa code mapped to dlhe teaching level
1224 l_he_code_map_val.association_code := 'OSS_HESA_TEACHPHS_ASSOC';
1225 l_he_code_map_val.map2 := p_dlhe_teach_level;
1226
1227 igs_he_extract2_pkg.get_map_values
1228 (p_he_code_map_val => l_he_code_map_val,
1229 p_value_from => 'MAP1',
1230 p_return_value => p_hesa_teachphs);
1231 END IF ;
1232
1233 EXCEPTION
1234 WHEN OTHERS
1235 THEN
1236 write_to_log(SQLERRM);
1237
1238 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
1239 Fnd_Message.Set_Token('NAME','IGS_HE_EXTRACT_FIELDS_PKG.get_teaching_phase');
1240 igs_ge_msg_stack.add;
1241 App_Exception.Raise_Exception;
1242 END get_teaching_phase;
1243
1244
1245 PROCEDURE get_intent
1246 (p_hesa_method IN igs_he_ex_rn_dat_fd.value%TYPE,
1247 p_dlhe_pt_study IN igs_he_stdnt_dlhe.PT_Study%TYPE,
1248 p_dlhe_reason_ptcrse IN igs_he_stdnt_dlhe.Reason_for_PTcourse%TYPE,
1249 p_hesa_intent OUT NOCOPY VARCHAR2)
1250 IS
1251 /***************************************************************
1252 Created By : smaddali
1253 Date Created By : 9-apr-03
1254 Purpose : This procedure gets the HESA Reason for taking original course
1255 Known Limitations,Enhancements or Remarks:
1256 Change History :
1257 Who When What
1258 smaddali 04-jul-03 modified default processing for bug#3036995 to add one more condition
1259 ***************************************************************/
1260
1261 l_he_code_map_val igs_he_code_map_val%ROWTYPE := NULL;
1262
1263 BEGIN
1264 -- if method is 9 or PT_Study is N or if Reason_for_PTcourse is null then return default
1265 IF ( p_hesa_method IN ('8','9') OR p_dlhe_pt_study = 'N' OR p_dlhe_reason_ptcrse IS NULL ) THEN
1266 -- default value processing
1267 p_hesa_intent := 'X' ;
1268 ELSIF p_dlhe_reason_ptcrse IS NOT NULL THEN
1269 -- return the hesa code mapped to dlhe reason for ptcourse
1270 l_he_code_map_val.association_code := 'OSS_HESA_INTENT_ASSOC';
1271 l_he_code_map_val.map2 := p_dlhe_reason_ptcrse;
1272
1273 igs_he_extract2_pkg.get_map_values
1274 (p_he_code_map_val => l_he_code_map_val,
1275 p_value_from => 'MAP1',
1276 p_return_value => p_hesa_intent);
1277 END IF ;
1278
1279 EXCEPTION
1280 WHEN OTHERS
1281 THEN
1282 write_to_log(SQLERRM);
1283
1284 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
1285 Fnd_Message.Set_Token('NAME','IGS_HE_EXTRACT_FIELDS_PKG.get_intent');
1286 igs_ge_msg_stack.add;
1287 App_Exception.Raise_Exception;
1288 END get_intent;
1289
1290
1291 PROCEDURE get_job_while_study
1292 (p_hesa_method IN igs_he_ex_rn_dat_fd.value%TYPE,
1293 p_dlhe_pt_study IN igs_he_stdnt_dlhe.PT_Study%TYPE,
1294 p_dlhe_job_while_study IN igs_he_stdnt_dlhe.Job_while_studying%TYPE,
1295 p_hesa_empcrse OUT NOCOPY VARCHAR2)
1296 IS
1297 /***************************************************************
1298 Created By : smaddali
1299 Date Created By : 9-apr-03
1300 Purpose : This procedure identifies wether the student had the job while studying
1301 Known Limitations,Enhancements or Remarks:
1302 Change History :
1303 Who When What
1304 ***************************************************************/
1305
1306 BEGIN
1307 IF ( p_hesa_method IN ('3','4','8','9') OR p_dlhe_pt_study = 'N' ) THEN
1308 -- default value processing
1309 p_hesa_empcrse := 'X' ;
1310 ELSIF p_dlhe_job_while_study = 'Y' THEN
1311 -- if job_while_studying = Y then return 1 else return 0
1312 p_hesa_empcrse := '1';
1313 ELSE
1314 p_hesa_empcrse := '0';
1315 END IF ;
1316
1317 EXCEPTION
1318 WHEN OTHERS
1319 THEN
1320 write_to_log(SQLERRM);
1321
1322 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
1323 Fnd_Message.Set_Token('NAME','IGS_HE_EXTRACT_FIELDS_PKG.get_job_while_study');
1324 igs_ge_msg_stack.add;
1325 App_Exception.Raise_Exception;
1326 END get_job_while_study;
1327
1328
1329 PROCEDURE get_emp_sponsorship
1330 (p_hesa_method IN igs_he_ex_rn_dat_fd.value%TYPE,
1331 p_hesa_empcrse IN igs_he_ex_rn_dat_fd.value%TYPE,
1332 p_dlhe_pt_study IN igs_he_stdnt_dlhe.PT_Study%TYPE,
1333 p_dlhe_emp_support IN igs_he_stdnt_dlhe.Employer_support1%TYPE,
1334 p_hesa_empspns OUT NOCOPY VARCHAR2)
1335 IS
1336 /***************************************************************
1337 Created By : smaddali
1338 Date Created By : 9-apr-03
1339 Purpose : This procedure gets the HESA reason for employer support 1 to 5
1340 Known Limitations,Enhancements or Remarks:
1341 Change History :
1342 Who When What
1343 ***************************************************************/
1344
1345 l_he_code_map_val igs_he_code_map_val%ROWTYPE := NULL;
1346
1347 BEGIN
1348 IF ( p_hesa_method IN ('3','4','8','9') OR p_hesa_empcrse IN ('0','X') OR p_dlhe_pt_study = 'N' ) THEN
1349 -- default value processing
1350 p_hesa_empspns := 'X' ;
1351 ELSIF p_dlhe_emp_support = 'Y' THEN
1352 -- If employer support1 to 5 = Y then return 1 else return 0
1353 p_hesa_empspns := '1' ;
1354 ELSE
1355 p_hesa_empspns := '0' ;
1356 END IF ;
1357
1358 EXCEPTION
1359 WHEN OTHERS
1360 THEN
1361 write_to_log(SQLERRM);
1362
1363 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
1364 Fnd_Message.Set_Token('NAME','IGS_HE_EXTRACT_FIELDS_PKG.get_emp_sponsorship');
1365 igs_ge_msg_stack.add;
1366 App_Exception.Raise_Exception;
1367 END get_emp_sponsorship;
1368
1369
1370 END IGS_HE_EXTRACT_DLHE_FIELDS_PKG;
[Home] [Help]