[Home] [Help]
PACKAGE BODY: APPS.IGS_UC_DAT_IMP_FROM_UCAS
Source
1 PACKAGE BODY igs_uc_dat_imp_from_ucas AS
2 /* $Header: IGSUC19B.pls 120.2 2006/08/21 03:51:35 jbaber noship $ */
3
4 PROCEDURE insert_dat_into_ucas (
5 errbuf OUT NOCOPY VARCHAR2
6 ,retcode OUT NOCOPY NUMBER
7 ,p_report_mode IN VARCHAR2
8 ,p_n_rec_cnt_for_commit IN NUMBER
9 ,p_c_import_appl_data IN VARCHAR2
10 ) IS
11 -- parameter p_c_import_appl added as part of UCCR002 Bug# 2278817
12
13 /******************************************************************
14 Created By : M. S. GARCHA
15 Date Created By : 10-OCT-2001
16 Purpose :
17 Known limitations,enhancements,remarks:
18 Change History
19 Who When What
20 kkillams 24-Sep-2002 Call to proc_cvname_view procedure is removed.
21 New procedure calls proc_cvRefTariff_view
22 proc_cvJointAdmissions_view
23 proc_cvRefSocioEconomic_view
24 proc_cvRefSocialClass_view
25 proc_cvRefPre2000POCC_view are added to
26 populate respective UCAS views.
27 w.r.t. UCFD06 build bug no : 2574566
28 ayedubat 14-NOV-2002 Added two new procedures,proc_ivstarg_view and proc_ivstart_view
29 The procedures proc_uvcontgrp,proc_uvcontact_view,
30 proc_cvcourse_view, proc_uvcourse_view, proc_uvcoursevacancies_view,
31 proc_uvcoursevacoptions_view, proc_uvcoursekeyword_view
32 are changed to execute on every run, rather for p_c_import_appl_data = 'Y'.
33 ***************************************************************** */
34
35 BEGIN
36 NULL;
37 END insert_dat_into_ucas;
38
39
40 PROCEDURE update_ucas_app_with_pers_id (
41 errbuf OUT NOCOPY VARCHAR2,
42 retcode OUT NOCOPY NUMBER
43 ) IS
44 /******************************************************************
45 Created By : rbezawad
46 Date Created By : 10-Jun-2002
47 Purpose : To update the IGS_UC_APPLICANTS.OSS_PERSON_ID column with the IGS_PE_ALT_PERS_ID_V.PE_PERSON_ID column value.
48 This needs to be run after running the "Import data from UCAS" and "Admission Import Process".
49 Known limitations,enhancements,remarks:
50 Change History
51 Who When What
52 rbezawad 2-Oct-2002 6 Columns choices_transparent_ind, extra_status, extra_passport_no, request_app_dets_ind,
53 request_copy_app_frm_ind, cef_no are added to IGS_UC_APPLICANTS TBH call w.r.t. UCFD06, bug 2574566.
54 ayedubat 13-NOV-2002 Columns from System_code to edu_qualification are added to IGS_UC_APPLICANTS TBH call for Enh Bug# 2643048
55 ayedubat 04-DEC-2002 Changed the WHERE clause of the cursor,cur_applicant to add to_char to a.app_no column since the column
56 b.api_person_id is a VARCHAR2 field and changed the comparision of b.person_id_type directly with UCASID to
57 the appropriate id of the UCAS system of the Applicant as for the small systems support
58 for the bug fix: 2670807
59 anwest 18-JAN-2006 Bug# 4950285 R12 Disable OSS Mandate
60 ***************************************************************** */
61
62 CURSOR cur_applicant IS
63 SELECT a.rowid
64 ,a.app_id
65 ,a.app_no
66 ,a.check_digit
67 ,a.personal_id
68 ,a.enquiry_no
69 ,a.oss_person_id
70 ,a.application_source
71 ,a.name_change_date
72 ,a.student_support
73 ,a.address_area
74 ,a.application_date
75 ,a.application_sent_date
76 ,a.application_sent_run
77 ,a.lea_code
78 ,a.fee_payer_code
79 ,a.fee_text
80 ,a.domicile_apr
81 ,a.code_changed_date
82 ,a.school
83 ,a.withdrawn
84 ,a.withdrawn_date
85 ,a.rel_to_clear_reason
86 ,a.route_b
87 ,a.exam_change_date
88 ,a.a_levels
89 ,a.as_levels
90 ,a.highers
91 ,a.csys
92 ,a.winter
93 ,a.previous
94 ,a.gnvq
95 ,a.btec
96 ,a.ilc
97 ,a.ailc
98 ,a.ib
99 ,a.manual
100 ,a.reg_num
101 ,a.oeq
102 ,a.eas
103 ,a.roa
104 ,a.status
105 ,a.firm_now
106 ,a.firm_reply
107 ,a.insurance_reply
108 ,a.conf_hist_firm_reply
109 ,a.conf_hist_ins_reply
110 ,a.residential_category
111 ,a.personal_statement
112 ,a.match_prev
113 ,a.match_prev_date
114 ,a.match_winter
115 ,a.match_summer
116 ,a.gnvq_date
117 ,a.ib_date
118 ,a.ilc_date
119 ,a.ailc_date
120 ,a.gcseqa_date
121 ,a.uk_entry_date
122 ,a.prev_surname
123 ,a.criminal_convictions
124 ,a.sent_to_hesa
125 ,a.sent_to_oss
126 ,a.batch_identifier
127 ,a.gce
128 ,a.vce
129 ,a.sqa
130 ,a.previousas
131 ,a.keyskills
132 ,a.vocational
133 ,a.scn
134 ,a.prevoeq
135 ,b.pe_person_id
136 ,a.choices_transparent_ind
137 ,a.extra_status
138 ,a.extra_passport_no
139 ,a.request_app_dets_ind
140 ,a.request_copy_app_frm_ind
141 ,a.cef_no
142 ,a.system_code
143 ,a.gcse_eng
144 ,a.gcse_math
145 ,a.degree_subject
146 ,a.degree_status
147 ,a.degree_class
148 ,a.gcse_sci
149 ,a.welshspeaker
150 ,a.ni_number
151 ,a.earliest_start
152 ,a.near_inst
153 ,a.pref_reg
154 ,a.qual_eng
155 ,a.qual_math
156 ,a.qual_sci
157 ,a.main_qual
158 ,a.qual_5
159 ,a.future_serv
160 ,a.future_set
161 ,a.present_serv
162 ,a.present_set
163 ,a.curr_employment
164 ,a.edu_qualification
165 ,a.ad_batch_id
166 ,a.ad_interface_id
167 ,a.nationality
168 ,a.dual_nationality
169 ,a.special_needs
170 ,a.country_birth
171 FROM igs_uc_applicants a, igs_pe_alt_pers_id_v b
172 WHERE a.oss_person_id IS NULL
173 AND b.api_person_id = to_char(a.app_no)
174 AND b.person_id_type = DECODE(a.system_code,'U','UCASID','G','GTTRID','N','NMASID','S','SWASID')
175 AND NVL(b.start_dt, SYSDATE) <= SYSDATE
176 AND NVL(b.end_dt, SYSDATE) >= SYSDATE
177 ORDER BY a.app_no;
178
179 l_app_count NUMBER :=0 ;
180
181 BEGIN
182
183 --anwest 18-JAN-2006 Bug# 4950285 R12 Disable OSS Mandate
184 IGS_GE_GEN_003.SET_ORG_ID;
185
186 --Loop through the all the Applicants records that needs to populate the OSS_person_id value.
187 FOR applicant_rec IN cur_applicant
188 LOOP
189 --Updates the IGS_UC_APPLICANTS.OSS_PERSON_ID column with the IGS_PE_ALT_PERS_ID_V.PE_PERSON_ID column value.
190 igs_uc_applicants_pkg.update_row
191 ( x_rowid => applicant_rec.rowid
192 ,x_app_id => applicant_rec.app_id
193 ,x_app_no => applicant_rec.app_no
194 ,x_check_digit => applicant_rec.check_digit
195 ,x_personal_id => applicant_rec.personal_id
196 ,x_enquiry_no => applicant_rec.enquiry_no
197 ,x_oss_person_id => applicant_rec.pe_person_id
198 ,x_application_source => applicant_rec.application_source
199 ,x_name_change_date => applicant_rec.name_change_date
200 ,x_student_support => applicant_rec.student_support
201 ,x_address_area => applicant_rec.address_area
202 ,x_application_date => applicant_rec.application_date
203 ,x_application_sent_date => applicant_rec.application_sent_date
204 ,x_application_sent_run => applicant_rec.application_sent_run
205 ,x_lea_code => applicant_rec.lea_code
206 ,x_fee_payer_code => applicant_rec.fee_payer_code
207 ,x_fee_text => applicant_rec.fee_text
208 ,x_domicile_apr => applicant_rec.domicile_apr
209 ,x_code_changed_date => applicant_rec.code_changed_date
210 ,x_school => applicant_rec.school
211 ,x_withdrawn => applicant_rec.withdrawn
212 ,x_withdrawn_date => applicant_rec.withdrawn_date
213 ,x_rel_to_clear_reason => applicant_rec.rel_to_clear_reason
214 ,x_route_b => applicant_rec.route_b
215 ,x_exam_change_date => applicant_rec.exam_change_date
216 ,x_a_levels => applicant_rec.a_levels
217 ,x_as_levels => applicant_rec.as_levels
218 ,x_highers => applicant_rec.highers
219 ,x_csys => applicant_rec.csys
220 ,x_winter => applicant_rec.winter
221 ,x_previous => applicant_rec.previous
222 ,x_gnvq => applicant_rec.gnvq
223 ,x_btec => applicant_rec.btec
224 ,x_ilc => applicant_rec.ilc
225 ,x_ailc => applicant_rec.ailc
226 ,x_ib => applicant_rec.ib
227 ,x_manual => applicant_rec.manual
228 ,x_reg_num => applicant_rec.reg_num
229 ,x_oeq => applicant_rec.oeq
230 ,x_eas => applicant_rec.eas
231 ,x_roa => applicant_rec.roa
232 ,x_status => applicant_rec.status
233 ,x_firm_now => applicant_rec.firm_now
234 ,x_firm_reply => applicant_rec.firm_reply
235 ,x_insurance_reply => applicant_rec.insurance_reply
236 ,x_conf_hist_firm_reply => applicant_rec.conf_hist_firm_reply
237 ,x_conf_hist_ins_reply => applicant_rec.conf_hist_ins_reply
238 ,x_residential_category => applicant_rec.residential_category
239 ,x_personal_statement => applicant_rec.personal_statement
240 ,x_match_prev => applicant_rec.match_prev
241 ,x_match_prev_date => applicant_rec.match_prev_date
242 ,x_match_winter => applicant_rec.match_winter
243 ,x_match_summer => applicant_rec.match_summer
244 ,x_gnvq_date => applicant_rec.gnvq_date
245 ,x_ib_date => applicant_rec.ib_date
246 ,x_ilc_date => applicant_rec.ilc_date
247 ,x_ailc_date => applicant_rec.ailc_date
248 ,x_gcseqa_date => applicant_rec.gcseqa_date
249 ,x_uk_entry_date => applicant_rec.uk_entry_date
250 ,x_prev_surname => applicant_rec.prev_surname
251 ,x_criminal_convictions => applicant_rec.criminal_convictions
252 ,x_sent_to_hesa => applicant_rec.sent_to_hesa
253 ,x_sent_to_oss => NULL
254 ,x_batch_identifier => applicant_rec.batch_identifier
255 ,x_gce => applicant_rec.gce
256 ,x_vce => applicant_rec.vce
257 ,x_sqa => applicant_rec.sqa
258 ,x_previousas => applicant_rec.previousas
259 ,x_keyskills => applicant_rec.keyskills
260 ,x_vocational => applicant_rec.vocational
261 ,x_scn => applicant_rec.scn
262 ,x_prevoeq => applicant_rec.prevoeq
263 ,x_mode => 'R'
264 ,x_choices_transparent_ind => applicant_rec.choices_transparent_ind
265 ,x_extra_status => applicant_rec.extra_status
266 ,x_extra_passport_no => applicant_rec.extra_passport_no
267 ,x_request_app_dets_ind => applicant_rec.request_app_dets_ind
268 ,x_request_copy_app_frm_ind => applicant_rec.request_copy_app_frm_ind
269 ,x_cef_no => applicant_rec.cef_no
270 ,x_system_code => applicant_rec.system_code
271 ,x_gcse_eng => applicant_rec.gcse_eng
272 ,x_gcse_math => applicant_rec.gcse_math
273 ,x_degree_subject => applicant_rec.degree_subject
274 ,x_degree_status => applicant_rec.degree_status
275 ,x_degree_class => applicant_rec.degree_class
276 ,x_gcse_sci => applicant_rec.gcse_sci
277 ,x_welshspeaker => applicant_rec.welshspeaker
278 ,x_ni_number => applicant_rec.ni_number
279 ,x_earliest_start => applicant_rec.earliest_start
280 ,x_near_inst => applicant_rec.near_inst
281 ,x_pref_reg => applicant_rec.pref_reg
282 ,x_qual_eng => applicant_rec.qual_eng
283 ,x_qual_math => applicant_rec.qual_math
284 ,x_qual_sci => applicant_rec.qual_sci
285 ,x_main_qual => applicant_rec.main_qual
286 ,x_qual_5 => applicant_rec.qual_5
287 ,x_future_serv => applicant_rec.future_serv
288 ,x_future_set => applicant_rec.future_set
289 ,x_present_serv => applicant_rec.present_serv
290 ,x_present_set => applicant_rec.present_set
291 ,x_curr_employment => applicant_rec.curr_employment
292 ,x_edu_qualification => applicant_rec.edu_qualification
293 ,x_ad_batch_id => applicant_rec.ad_batch_id
294 ,x_ad_interface_id => applicant_rec.ad_interface_id
295 ,x_nationality => applicant_rec.nationality
296 ,x_dual_nationality => applicant_rec.dual_nationality
297 ,x_special_needs => applicant_rec.special_needs
298 ,x_country_birth => applicant_rec.country_birth
299 );
300
301 l_app_count := l_app_count + 1;
302
303 fnd_message.set_name('IGS','IGS_UC_UPD_APP_PID_REC');
304 fnd_message.set_token('APP_NO', TO_CHAR(applicant_rec.app_no));
305 fnd_message.set_token('OSS_PID',TO_CHAR(applicant_rec.pe_person_id));
306 fnd_file.put_line(fnd_file.log, fnd_message.get);
307
308 END LOOP;
309
310 fnd_file.put_line(fnd_file.log, ' ');
311 fnd_message.set_name('IGS','IGS_UC_APP_COUNT');
312 fnd_message.set_token('APP_COUNT', TO_CHAR(l_app_count));
313 fnd_file.put_line(fnd_file.log, fnd_message.get);
314
315 EXCEPTION
316 WHEN OTHERS THEN
317 ROLLBACK;
318 retcode := 2;
319 Fnd_Message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
320 fnd_message.set_token('NAME', 'IGS_UC_DAT_IMP_FROM_UCAS.UPDATE_UCAS_APP_WITH_PERS_ID');
321 errbuf := fnd_message.get;
322 Igs_Ge_Msg_Stack.CONC_EXCEPTION_HNDL;
323
324 END update_ucas_app_with_pers_id;
325
326 END igs_uc_dat_imp_from_ucas;