1 PACKAGE igs_uc_applicants_pkg AS
2 /* $Header: IGSXI01S.pls 120.2 2006/08/21 03:36:23 jbaber noship $ */
3
4 PROCEDURE insert_row (
5 x_rowid IN OUT NOCOPY VARCHAR2,
6 x_app_id IN OUT NOCOPY NUMBER,
7 x_app_no IN NUMBER,
8 x_check_digit IN NUMBER,
9 x_enquiry_no IN OUT NOCOPY NUMBER,
10 x_oss_person_id IN NUMBER,
11 x_application_source IN VARCHAR2,
12 x_name_change_date IN DATE,
13 x_student_support IN VARCHAR2,
14 x_address_area IN VARCHAR2,
15 x_application_date IN DATE,
16 x_application_sent_date IN DATE,
17 x_application_sent_run IN NUMBER,
18 x_lea_code IN NUMBER,
19 x_fee_payer_code IN NUMBER,
20 x_fee_text IN VARCHAR2,
21 x_domicile_apr IN NUMBER,
22 x_code_changed_date IN DATE,
23 x_school IN NUMBER,
24 x_withdrawn IN VARCHAR2,
25 x_withdrawn_date IN DATE,
26 x_rel_to_clear_reason IN VARCHAR2,
27 x_route_b IN VARCHAR2,
28 x_exam_change_date IN DATE,
29 x_a_levels IN NUMBER,
30 x_as_levels IN NUMBER,
31 x_highers IN NUMBER,
32 x_csys IN NUMBER,
33 x_winter IN NUMBER,
34 x_previous IN NUMBER,
35 x_gnvq IN VARCHAR2,
36 x_btec IN VARCHAR2,
37 x_ilc IN VARCHAR2,
38 x_ailc IN VARCHAR2,
39 x_ib IN VARCHAR2,
40 x_manual IN VARCHAR2,
41 x_reg_num IN VARCHAR2,
42 x_oeq IN VARCHAR2,
43 x_eas IN VARCHAR2,
44 x_roa IN VARCHAR2,
45 x_status IN VARCHAR2,
46 x_firm_now IN NUMBER,
47 x_firm_reply IN NUMBER,
48 x_insurance_reply IN NUMBER,
49 x_conf_hist_firm_reply IN NUMBER,
50 x_conf_hist_ins_reply IN NUMBER,
51 x_residential_category IN VARCHAR2,
52 x_personal_statement IN LONG,
53 x_match_prev IN VARCHAR2,
54 x_match_prev_date IN DATE,
55 x_match_winter IN VARCHAR2,
56 x_match_summer IN VARCHAR2,
57 x_gnvq_date IN DATE,
58 x_ib_date IN DATE,
59 x_ilc_date IN DATE,
60 x_ailc_date IN DATE,
61 x_gcseqa_date IN DATE,
62 x_uk_entry_date IN DATE,
63 x_prev_surname IN VARCHAR2,
64 x_criminal_convictions IN VARCHAR2,
65 x_sent_to_hesa IN VARCHAR2,
66 x_sent_to_oss IN VARCHAR2,
67 x_batch_identifier IN NUMBER,
68 x_mode IN VARCHAR2 DEFAULT 'R',
69 -- Added following 8 Columns as part of UCCR002 Build. Bug NO: 2278817 by rbezawad
70 x_gce IN NUMBER DEFAULT NULL,
71 x_vce IN NUMBER DEFAULT NULL,
72 x_sqa IN VARCHAR2 DEFAULT NULL,
73 x_previousas IN NUMBER DEFAULT NULL,
74 x_keyskills IN VARCHAR2 DEFAULT NULL,
75 x_vocational IN VARCHAR2 DEFAULT NULL,
76 x_scn IN VARCHAR2 DEFAULT NULL,
77 x_prevoeq IN VARCHAR2 DEFAULT NULL,
78 -- Added the following 5 coluns as part of UCFD06 Build: Bug#2574566
79 x_choices_transparent_ind IN VARCHAR2 DEFAULT NULL,
80 x_extra_status IN NUMBER DEFAULT NULL,
81 x_extra_passport_no IN VARCHAR2 DEFAULT NULL,
82 x_request_app_dets_ind IN VARCHAR2 DEFAULT 'N',
83 x_request_copy_app_frm_ind IN VARCHAR2 DEFAULT 'N',
84 x_cef_no IN NUMBER DEFAULT NULL,
85 -- Added the following columns as part of UCFD102 Build: Bug#2643048
86 x_system_code IN VARCHAR2 DEFAULT NULL,
87 x_gcse_eng IN VARCHAR2 DEFAULT NULL,
88 x_gcse_math IN VARCHAR2 DEFAULT NULL,
89 x_degree_subject IN VARCHAR2 DEFAULT NULL,
90 x_degree_status IN VARCHAR2 DEFAULT NULL,
91 x_degree_class IN VARCHAR2 DEFAULT NULL,
92 x_gcse_sci IN VARCHAR2 DEFAULT NULL,
93 x_welshspeaker IN VARCHAR2 DEFAULT NULL,
94 x_ni_number IN VARCHAR2 DEFAULT NULL,
95 x_earliest_start IN VARCHAR2 DEFAULT NULL,
96 x_near_inst IN VARCHAR2 DEFAULT NULL,
97 x_pref_reg IN NUMBER DEFAULT NULL,
98 x_qual_eng IN VARCHAR2 DEFAULT NULL,
99 x_qual_math IN VARCHAR2 DEFAULT NULL,
100 x_qual_sci IN VARCHAR2 DEFAULT NULL,
101 x_main_qual IN VARCHAR2 DEFAULT NULL,
102 x_qual_5 IN VARCHAR2 DEFAULT NULL,
103 x_future_serv IN VARCHAR2 DEFAULT NULL,
104 x_future_set IN VARCHAR2 DEFAULT NULL,
105 x_present_serv IN VARCHAR2 DEFAULT NULL,
106 x_present_set IN VARCHAR2 DEFAULT NULL,
107 x_curr_employment IN VARCHAR2 DEFAULT NULL,
108 x_edu_qualification IN VARCHAR2 DEFAULT NULL,
109 -- smaddali added these columns for ucfd203 - multiple cycles bug#2669208
110 x_ad_batch_id IN NUMBER DEFAULT NULL,
111 x_ad_interface_id IN NUMBER DEFAULT NULL,
112 x_nationality IN NUMBER DEFAULT NULL,
113 x_dual_nationality IN NUMBER DEFAULT NULL,
114 x_special_needs IN VARCHAR2 DEFAULT NULL,
115 x_country_birth IN NUMBER DEFAULT NULL,
116 x_personal_id IN VARCHAR2 DEFAULT NULL
117 );
118
119 PROCEDURE lock_row (
120 x_rowid IN VARCHAR2,
121 x_app_id IN NUMBER,
122 x_app_no IN NUMBER,
123 x_check_digit IN NUMBER,
124 x_enquiry_no IN NUMBER,
125 x_oss_person_id IN NUMBER,
126 x_application_source IN VARCHAR2,
127 x_name_change_date IN DATE,
128 x_student_support IN VARCHAR2,
129 x_address_area IN VARCHAR2,
130 x_application_date IN DATE,
131 x_application_sent_date IN DATE,
132 x_application_sent_run IN NUMBER,
133 x_lea_code IN NUMBER,
134 x_fee_payer_code IN NUMBER,
135 x_fee_text IN VARCHAR2,
136 x_domicile_apr IN NUMBER,
137 x_code_changed_date IN DATE,
138 x_school IN NUMBER,
139 x_withdrawn IN VARCHAR2,
140 x_withdrawn_date IN DATE,
141 x_rel_to_clear_reason IN VARCHAR2,
142 x_route_b IN VARCHAR2,
143 x_exam_change_date IN DATE,
144 x_a_levels IN NUMBER,
145 x_as_levels IN NUMBER,
146 x_highers IN NUMBER,
147 x_csys IN NUMBER,
148 x_winter IN NUMBER,
149 x_previous IN NUMBER,
150 x_gnvq IN VARCHAR2,
151 x_btec IN VARCHAR2,
152 x_ilc IN VARCHAR2,
153 x_ailc IN VARCHAR2,
154 x_ib IN VARCHAR2,
155 x_manual IN VARCHAR2,
156 x_reg_num IN VARCHAR2,
157 x_oeq IN VARCHAR2,
158 x_eas IN VARCHAR2,
159 x_roa IN VARCHAR2,
160 x_status IN VARCHAR2,
161 x_firm_now IN NUMBER,
162 x_firm_reply IN NUMBER,
163 x_insurance_reply IN NUMBER,
164 x_conf_hist_firm_reply IN NUMBER,
165 x_conf_hist_ins_reply IN NUMBER,
166 x_residential_category IN VARCHAR2,
167 x_personal_statement IN LONG,
168 x_match_prev IN VARCHAR2,
169 x_match_prev_date IN DATE,
170 x_match_winter IN VARCHAR2,
171 x_match_summer IN VARCHAR2,
172 x_gnvq_date IN DATE,
173 x_ib_date IN DATE,
174 x_ilc_date IN DATE,
175 x_ailc_date IN DATE,
176 x_gcseqa_date IN DATE,
177 x_uk_entry_date IN DATE,
178 x_prev_surname IN VARCHAR2,
179 x_criminal_convictions IN VARCHAR2,
180 x_sent_to_hesa IN VARCHAR2,
181 x_sent_to_oss IN VARCHAR2,
182 x_batch_identifier IN NUMBER,
183 -- Added following 8 Columns as part of UCCR002 Build. Bug NO: 2278817 by rbezawad
184 x_gce IN NUMBER DEFAULT NULL,
185 x_vce IN NUMBER DEFAULT NULL,
186 x_sqa IN VARCHAR2 DEFAULT NULL,
187 x_previousas IN NUMBER DEFAULT NULL,
188 x_keyskills IN VARCHAR2 DEFAULT NULL,
189 x_vocational IN VARCHAR2 DEFAULT NULL,
190 x_scn IN VARCHAR2 DEFAULT NULL,
191 x_prevoeq IN VARCHAR2 DEFAULT NULL,
192 -- Added the following 5 coluns as part of UCFD06 Build: Bug#2574566
193 x_choices_transparent_ind IN VARCHAR2 DEFAULT NULL,
194 x_extra_status IN NUMBER DEFAULT NULL,
195 x_extra_passport_no IN VARCHAR2 DEFAULT NULL,
196 x_request_app_dets_ind IN VARCHAR2 DEFAULT 'N',
197 x_request_copy_app_frm_ind IN VARCHAR2 DEFAULT 'N',
198 x_cef_no IN NUMBER DEFAULT NULL,
199 x_system_code IN VARCHAR2 DEFAULT NULL,
200 x_gcse_eng IN VARCHAR2 DEFAULT NULL,
201 x_gcse_math IN VARCHAR2 DEFAULT NULL,
202 x_degree_subject IN VARCHAR2 DEFAULT NULL,
203 x_degree_status IN VARCHAR2 DEFAULT NULL,
204 x_degree_class IN VARCHAR2 DEFAULT NULL,
205 x_gcse_sci IN VARCHAR2 DEFAULT NULL,
206 x_welshspeaker IN VARCHAR2 DEFAULT NULL,
207 x_ni_number IN VARCHAR2 DEFAULT NULL,
208 x_earliest_start IN VARCHAR2 DEFAULT NULL,
209 x_near_inst IN VARCHAR2 DEFAULT NULL,
210 x_pref_reg IN NUMBER DEFAULT NULL,
211 x_qual_eng IN VARCHAR2 DEFAULT NULL,
212 x_qual_math IN VARCHAR2 DEFAULT NULL,
213 x_qual_sci IN VARCHAR2 DEFAULT NULL,
214 x_main_qual IN VARCHAR2 DEFAULT NULL,
215 x_qual_5 IN VARCHAR2 DEFAULT NULL,
216 x_future_serv IN VARCHAR2 DEFAULT NULL,
217 x_future_set IN VARCHAR2 DEFAULT NULL,
218 x_present_serv IN VARCHAR2 DEFAULT NULL,
219 x_present_set IN VARCHAR2 DEFAULT NULL,
220 x_curr_employment IN VARCHAR2 DEFAULT NULL,
221 x_edu_qualification IN VARCHAR2 DEFAULT NULL,
222 -- smaddali added these columns for ucfd203 - multiple cycles bug#2669208
223 x_ad_batch_id IN NUMBER DEFAULT NULL,
224 x_ad_interface_id IN NUMBER DEFAULT NULL,
225 x_nationality IN NUMBER DEFAULT NULL,
226 x_dual_nationality IN NUMBER DEFAULT NULL,
227 x_special_needs IN VARCHAR2 DEFAULT NULL,
228 x_country_birth IN NUMBER DEFAULT NULL,
229 x_personal_id IN VARCHAR2 DEFAULT NULL
230 );
231
232 PROCEDURE update_row (
233 x_rowid IN VARCHAR2,
234 x_app_id IN NUMBER,
235 x_app_no IN NUMBER,
236 x_check_digit IN NUMBER,
237 x_enquiry_no IN NUMBER,
238 x_oss_person_id IN NUMBER,
239 x_application_source IN VARCHAR2,
240 x_name_change_date IN DATE,
241 x_student_support IN VARCHAR2,
242 x_address_area IN VARCHAR2,
243 x_application_date IN DATE,
244 x_application_sent_date IN DATE,
245 x_application_sent_run IN NUMBER,
246 x_lea_code IN NUMBER,
247 x_fee_payer_code IN NUMBER,
248 x_fee_text IN VARCHAR2,
249 x_domicile_apr IN NUMBER,
250 x_code_changed_date IN DATE,
251 x_school IN NUMBER,
252 x_withdrawn IN VARCHAR2,
256 x_exam_change_date IN DATE,
253 x_withdrawn_date IN DATE,
254 x_rel_to_clear_reason IN VARCHAR2,
255 x_route_b IN VARCHAR2,
257 x_a_levels IN NUMBER,
258 x_as_levels IN NUMBER,
259 x_highers IN NUMBER,
260 x_csys IN NUMBER,
261 x_winter IN NUMBER,
262 x_previous IN NUMBER,
263 x_gnvq IN VARCHAR2,
264 x_btec IN VARCHAR2,
265 x_ilc IN VARCHAR2,
266 x_ailc IN VARCHAR2,
267 x_ib IN VARCHAR2,
268 x_manual IN VARCHAR2,
269 x_reg_num IN VARCHAR2,
270 x_oeq IN VARCHAR2,
271 x_eas IN VARCHAR2,
272 x_roa IN VARCHAR2,
273 x_status IN VARCHAR2,
274 x_firm_now IN NUMBER,
275 x_firm_reply IN NUMBER,
276 x_insurance_reply IN NUMBER,
277 x_conf_hist_firm_reply IN NUMBER,
278 x_conf_hist_ins_reply IN NUMBER,
279 x_residential_category IN VARCHAR2,
280 x_personal_statement IN LONG,
281 x_match_prev IN VARCHAR2,
282 x_match_prev_date IN DATE,
283 x_match_winter IN VARCHAR2,
284 x_match_summer IN VARCHAR2,
285 x_gnvq_date IN DATE,
286 x_ib_date IN DATE,
287 x_ilc_date IN DATE,
288 x_ailc_date IN DATE,
289 x_gcseqa_date IN DATE,
290 x_uk_entry_date IN DATE,
291 x_prev_surname IN VARCHAR2,
292 x_criminal_convictions IN VARCHAR2,
293 x_sent_to_hesa IN VARCHAR2,
294 x_sent_to_oss IN VARCHAR2,
295 x_batch_identifier IN NUMBER,
296 x_mode IN VARCHAR2 DEFAULT 'R',
297 -- Added following 8 Columns as part of UCCR002 Build. Bug NO: 2278817 by rbezawad
298 x_gce IN NUMBER DEFAULT NULL,
299 x_vce IN NUMBER DEFAULT NULL,
300 x_sqa IN VARCHAR2 DEFAULT NULL,
301 x_previousas IN NUMBER DEFAULT NULL,
302 x_keyskills IN VARCHAR2 DEFAULT NULL,
303 x_vocational IN VARCHAR2 DEFAULT NULL,
304 x_scn IN VARCHAR2 DEFAULT NULL,
305 x_prevoeq IN VARCHAR2 DEFAULT NULL,
306 -- Added the following 5 coluns as part of UCFD06 Build: Bug#2574566
307 x_choices_transparent_ind IN VARCHAR2 DEFAULT NULL,
308 x_extra_status IN NUMBER DEFAULT NULL,
309 x_extra_passport_no IN VARCHAR2 DEFAULT NULL,
310 x_request_app_dets_ind IN VARCHAR2 DEFAULT 'N',
311 x_request_copy_app_frm_ind IN VARCHAR2 DEFAULT 'N',
312 x_cef_no IN NUMBER DEFAULT NULL,
313 x_system_code IN VARCHAR2 DEFAULT NULL,
314 x_gcse_eng IN VARCHAR2 DEFAULT NULL,
315 x_gcse_math IN VARCHAR2 DEFAULT NULL,
316 x_degree_subject IN VARCHAR2 DEFAULT NULL,
317 x_degree_status IN VARCHAR2 DEFAULT NULL,
318 x_degree_class IN VARCHAR2 DEFAULT NULL,
319 x_gcse_sci IN VARCHAR2 DEFAULT NULL,
320 x_welshspeaker IN VARCHAR2 DEFAULT NULL,
321 x_ni_number IN VARCHAR2 DEFAULT NULL,
322 x_earliest_start IN VARCHAR2 DEFAULT NULL,
323 x_near_inst IN VARCHAR2 DEFAULT NULL,
324 x_pref_reg IN NUMBER DEFAULT NULL,
325 x_qual_eng IN VARCHAR2 DEFAULT NULL,
326 x_qual_math IN VARCHAR2 DEFAULT NULL,
327 x_qual_sci IN VARCHAR2 DEFAULT NULL,
328 x_main_qual IN VARCHAR2 DEFAULT NULL,
329 x_qual_5 IN VARCHAR2 DEFAULT NULL,
330 x_future_serv IN VARCHAR2 DEFAULT NULL,
331 x_future_set IN VARCHAR2 DEFAULT NULL,
332 x_present_serv IN VARCHAR2 DEFAULT NULL,
333 x_present_set IN VARCHAR2 DEFAULT NULL,
334 x_curr_employment IN VARCHAR2 DEFAULT NULL,
338 x_ad_interface_id IN NUMBER DEFAULT NULL,
335 x_edu_qualification IN VARCHAR2 DEFAULT NULL,
336 -- smaddali added these columns for ucfd203 - multiple cycles bug#2669208
337 x_ad_batch_id IN NUMBER DEFAULT NULL,
339 x_nationality IN NUMBER DEFAULT NULL,
340 x_dual_nationality IN NUMBER DEFAULT NULL,
341 x_special_needs IN VARCHAR2 DEFAULT NULL,
342 x_country_birth IN NUMBER DEFAULT NULL,
343 x_personal_id IN VARCHAR2 DEFAULT NULL
344 );
345
346 PROCEDURE add_row (
347 x_rowid IN OUT NOCOPY VARCHAR2,
348 x_app_id IN OUT NOCOPY NUMBER,
349 x_app_no IN NUMBER,
350 x_check_digit IN NUMBER,
351 x_enquiry_no IN OUT NOCOPY NUMBER,
352 x_oss_person_id IN NUMBER,
353 x_application_source IN VARCHAR2,
354 x_name_change_date IN DATE,
355 x_student_support IN VARCHAR2,
356 x_address_area IN VARCHAR2,
357 x_application_date IN DATE,
358 x_application_sent_date IN DATE,
359 x_application_sent_run IN NUMBER,
360 x_lea_code IN NUMBER,
361 x_fee_payer_code IN NUMBER,
362 x_fee_text IN VARCHAR2,
363 x_domicile_apr IN NUMBER,
364 x_code_changed_date IN DATE,
365 x_school IN NUMBER,
366 x_withdrawn IN VARCHAR2,
367 x_withdrawn_date IN DATE,
368 x_rel_to_clear_reason IN VARCHAR2,
369 x_route_b IN VARCHAR2,
370 x_exam_change_date IN DATE,
371 x_a_levels IN NUMBER,
372 x_as_levels IN NUMBER,
373 x_highers IN NUMBER,
374 x_csys IN NUMBER,
375 x_winter IN NUMBER,
376 x_previous IN NUMBER,
377 x_gnvq IN VARCHAR2,
378 x_btec IN VARCHAR2,
379 x_ilc IN VARCHAR2,
380 x_ailc IN VARCHAR2,
381 x_ib IN VARCHAR2,
382 x_manual IN VARCHAR2,
383 x_reg_num IN VARCHAR2,
384 x_oeq IN VARCHAR2,
385 x_eas IN VARCHAR2,
386 x_roa IN VARCHAR2,
387 x_status IN VARCHAR2,
388 x_firm_now IN NUMBER,
389 x_firm_reply IN NUMBER,
390 x_insurance_reply IN NUMBER,
391 x_conf_hist_firm_reply IN NUMBER,
392 x_conf_hist_ins_reply IN NUMBER,
393 x_residential_category IN VARCHAR2,
394 x_personal_statement IN LONG,
395 x_match_prev IN VARCHAR2,
396 x_match_prev_date IN DATE,
397 x_match_winter IN VARCHAR2,
398 x_match_summer IN VARCHAR2,
399 x_gnvq_date IN DATE,
400 x_ib_date IN DATE,
401 x_ilc_date IN DATE,
402 x_ailc_date IN DATE,
403 x_gcseqa_date IN DATE,
404 x_uk_entry_date IN DATE,
405 x_prev_surname IN VARCHAR2,
406 x_criminal_convictions IN VARCHAR2,
407 x_sent_to_hesa IN VARCHAR2,
408 x_sent_to_oss IN VARCHAR2,
409 x_batch_identifier IN NUMBER,
410 x_mode IN VARCHAR2 DEFAULT 'R',
411 -- Added following 8 Columns as part of UCCR002 Build. Bug NO: 2278817 by rbezawad
412 x_gce IN NUMBER DEFAULT NULL,
413 x_vce IN NUMBER DEFAULT NULL,
414 x_sqa IN VARCHAR2 DEFAULT NULL,
415 x_previousas IN NUMBER DEFAULT NULL,
416 x_keyskills IN VARCHAR2 DEFAULT NULL,
417 x_vocational IN VARCHAR2 DEFAULT NULL,
418 x_scn IN VARCHAR2 DEFAULT NULL,
419 x_prevoeq IN VARCHAR2 DEFAULT NULL,
420 -- Added the following 5 coluns as part of UCFD06 Build: Bug#2574566
421 x_choices_transparent_ind IN VARCHAR2 DEFAULT NULL,
422 x_extra_status IN NUMBER DEFAULT NULL,
423 x_extra_passport_no IN VARCHAR2 DEFAULT NULL,
424 x_request_app_dets_ind IN VARCHAR2 DEFAULT 'N',
425 x_request_copy_app_frm_ind IN VARCHAR2 DEFAULT 'N',
426 x_cef_no IN NUMBER DEFAULT NULL,
427 x_system_code IN VARCHAR2 DEFAULT NULL,
431 x_degree_status IN VARCHAR2 DEFAULT NULL,
428 x_gcse_eng IN VARCHAR2 DEFAULT NULL,
429 x_gcse_math IN VARCHAR2 DEFAULT NULL,
430 x_degree_subject IN VARCHAR2 DEFAULT NULL,
432 x_degree_class IN VARCHAR2 DEFAULT NULL,
433 x_gcse_sci IN VARCHAR2 DEFAULT NULL,
434 x_welshspeaker IN VARCHAR2 DEFAULT NULL,
435 x_ni_number IN VARCHAR2 DEFAULT NULL,
436 x_earliest_start IN VARCHAR2 DEFAULT NULL,
437 x_near_inst IN VARCHAR2 DEFAULT NULL,
438 x_pref_reg IN NUMBER DEFAULT NULL,
439 x_qual_eng IN VARCHAR2 DEFAULT NULL,
440 x_qual_math IN VARCHAR2 DEFAULT NULL,
441 x_qual_sci IN VARCHAR2 DEFAULT NULL,
442 x_main_qual IN VARCHAR2 DEFAULT NULL,
443 x_qual_5 IN VARCHAR2 DEFAULT NULL,
444 x_future_serv IN VARCHAR2 DEFAULT NULL,
445 x_future_set IN VARCHAR2 DEFAULT NULL,
446 x_present_serv IN VARCHAR2 DEFAULT NULL,
447 x_present_set IN VARCHAR2 DEFAULT NULL,
448 x_curr_employment IN VARCHAR2 DEFAULT NULL,
449 x_edu_qualification IN VARCHAR2 DEFAULT NULL,
450 -- smaddali added these columns for ucfd203 - multiple cycles bug#2669208
451 x_ad_batch_id IN NUMBER DEFAULT NULL,
452 x_ad_interface_id IN NUMBER DEFAULT NULL,
453 x_nationality IN NUMBER DEFAULT NULL,
454 x_dual_nationality IN NUMBER DEFAULT NULL,
455 x_special_needs IN VARCHAR2 DEFAULT NULL,
456 x_country_birth IN NUMBER DEFAULT NULL,
457 x_personal_id IN VARCHAR2 DEFAULT NULL
458 );
459
460 PROCEDURE delete_row (
461 x_rowid IN VARCHAR2,
462 x_mode IN VARCHAR2 DEFAULT 'R'
463 );
464
465 FUNCTION get_pk_for_validation (
466 x_app_id IN NUMBER
467 ) RETURN BOOLEAN;
468
469 FUNCTION get_uk_for_validation (
470 x_app_no IN NUMBER
471 ) RETURN BOOLEAN;
472
473 -- smaddali added this procedure for ucfd203 - multiple cycles build , bug#2669208
474 PROCEDURE get_fk_igs_uc_defaults ( x_system_code IN VARCHAR2 );
475
476 PROCEDURE get_fk_igs_pe_person (
477 x_oss_person_id IN NUMBER
478 );
479
480
481 PROCEDURE before_dml (
482 p_action IN VARCHAR2,
483 x_rowid IN VARCHAR2 DEFAULT NULL,
484 x_app_id IN NUMBER DEFAULT NULL,
485 x_app_no IN NUMBER DEFAULT NULL,
486 x_check_digit IN NUMBER DEFAULT NULL,
487 x_enquiry_no IN NUMBER DEFAULT NULL,
488 x_oss_person_id IN NUMBER DEFAULT NULL,
489 x_application_source IN VARCHAR2 DEFAULT NULL,
490 x_name_change_date IN DATE DEFAULT NULL,
491 x_student_support IN VARCHAR2 DEFAULT NULL,
492 x_address_area IN VARCHAR2 DEFAULT NULL,
493 x_application_date IN DATE DEFAULT NULL,
494 x_application_sent_date IN DATE DEFAULT NULL,
495 x_application_sent_run IN NUMBER DEFAULT NULL,
496 x_lea_code IN NUMBER DEFAULT NULL,
497 x_fee_payer_code IN NUMBER DEFAULT NULL,
498 x_fee_text IN VARCHAR2 DEFAULT NULL,
499 x_domicile_apr IN NUMBER DEFAULT NULL,
500 x_code_changed_date IN DATE DEFAULT NULL,
501 x_school IN NUMBER DEFAULT NULL,
502 x_withdrawn IN VARCHAR2 DEFAULT NULL,
503 x_withdrawn_date IN DATE DEFAULT NULL,
504 x_rel_to_clear_reason IN VARCHAR2 DEFAULT NULL,
505 x_route_b IN VARCHAR2 DEFAULT NULL,
506 x_exam_change_date IN DATE DEFAULT NULL,
507 x_a_levels IN NUMBER DEFAULT NULL,
508 x_as_levels IN NUMBER DEFAULT NULL,
509 x_highers IN NUMBER DEFAULT NULL,
510 x_csys IN NUMBER DEFAULT NULL,
511 x_winter IN NUMBER DEFAULT NULL,
512 x_previous IN NUMBER DEFAULT NULL,
513 x_gnvq IN VARCHAR2 DEFAULT NULL,
514 x_btec IN VARCHAR2 DEFAULT NULL,
518 x_manual IN VARCHAR2 DEFAULT NULL,
515 x_ilc IN VARCHAR2 DEFAULT NULL,
516 x_ailc IN VARCHAR2 DEFAULT NULL,
517 x_ib IN VARCHAR2 DEFAULT NULL,
519 x_reg_num IN VARCHAR2 DEFAULT NULL,
520 x_oeq IN VARCHAR2 DEFAULT NULL,
521 x_eas IN VARCHAR2 DEFAULT NULL,
522 x_roa IN VARCHAR2 DEFAULT NULL,
523 x_status IN VARCHAR2 DEFAULT NULL,
524 x_firm_now IN NUMBER DEFAULT NULL,
525 x_firm_reply IN NUMBER DEFAULT NULL,
526 x_insurance_reply IN NUMBER DEFAULT NULL,
527 x_conf_hist_firm_reply IN NUMBER DEFAULT NULL,
528 x_conf_hist_ins_reply IN NUMBER DEFAULT NULL,
529 x_residential_category IN VARCHAR2 DEFAULT NULL,
530 x_personal_statement IN LONG DEFAULT NULL,
531 x_match_prev IN VARCHAR2 DEFAULT NULL,
532 x_match_prev_date IN DATE DEFAULT NULL,
533 x_match_winter IN VARCHAR2 DEFAULT NULL,
534 x_match_summer IN VARCHAR2 DEFAULT NULL,
535 x_gnvq_date IN DATE DEFAULT NULL,
536 x_ib_date IN DATE DEFAULT NULL,
537 x_ilc_date IN DATE DEFAULT NULL,
538 x_ailc_date IN DATE DEFAULT NULL,
539 x_gcseqa_date IN DATE DEFAULT NULL,
540 x_uk_entry_date IN DATE DEFAULT NULL,
541 x_prev_surname IN VARCHAR2 DEFAULT NULL,
542 x_criminal_convictions IN VARCHAR2 DEFAULT NULL,
543 x_sent_to_hesa IN VARCHAR2 DEFAULT NULL,
544 x_sent_to_oss IN VARCHAR2 DEFAULT NULL,
545 x_batch_identifier IN NUMBER DEFAULT NULL,
546 x_creation_date IN DATE DEFAULT NULL,
547 x_created_by IN NUMBER DEFAULT NULL,
548 x_last_update_date IN DATE DEFAULT NULL,
549 x_last_updated_by IN NUMBER DEFAULT NULL,
550 x_last_update_login IN NUMBER DEFAULT NULL,
551 -- Added following 8 Columns as part of UCCR002 Build. Bug NO: 2278817 by rbezawad
552 x_gce IN NUMBER DEFAULT NULL,
553 x_vce IN NUMBER DEFAULT NULL,
554 x_sqa IN VARCHAR2 DEFAULT NULL,
555 x_previousas IN NUMBER DEFAULT NULL,
556 x_keyskills IN VARCHAR2 DEFAULT NULL,
557 x_vocational IN VARCHAR2 DEFAULT NULL,
558 x_scn IN VARCHAR2 DEFAULT NULL,
559 x_prevoeq IN VARCHAR2 DEFAULT NULL,
560 -- Added the following 5 coluns as part of UCFD06 Build: Bug#2574566
561 x_choices_transparent_ind IN VARCHAR2 DEFAULT NULL,
562 x_extra_status IN NUMBER DEFAULT NULL,
563 x_extra_passport_no IN VARCHAR2 DEFAULT NULL,
564 x_request_app_dets_ind IN VARCHAR2 DEFAULT 'N',
565 x_request_copy_app_frm_ind IN VARCHAR2 DEFAULT 'N',
566 x_cef_no IN NUMBER DEFAULT NULL,
567 x_system_code IN VARCHAR2 DEFAULT NULL,
568 x_gcse_eng IN VARCHAR2 DEFAULT NULL,
569 x_gcse_math IN VARCHAR2 DEFAULT NULL,
570 x_degree_subject IN VARCHAR2 DEFAULT NULL,
571 x_degree_status IN VARCHAR2 DEFAULT NULL,
572 x_degree_class IN VARCHAR2 DEFAULT NULL,
573 x_gcse_sci IN VARCHAR2 DEFAULT NULL,
574 x_welshspeaker IN VARCHAR2 DEFAULT NULL,
575 x_ni_number IN VARCHAR2 DEFAULT NULL,
576 x_earliest_start IN VARCHAR2 DEFAULT NULL,
577 x_near_inst IN VARCHAR2 DEFAULT NULL,
578 x_pref_reg IN NUMBER DEFAULT NULL,
579 x_qual_eng IN VARCHAR2 DEFAULT NULL,
580 x_qual_math IN VARCHAR2 DEFAULT NULL,
581 x_qual_sci IN VARCHAR2 DEFAULT NULL,
582 x_main_qual IN VARCHAR2 DEFAULT NULL,
583 x_qual_5 IN VARCHAR2 DEFAULT NULL,
584 x_future_serv IN VARCHAR2 DEFAULT NULL,
585 x_future_set IN VARCHAR2 DEFAULT NULL,
586 x_present_serv IN VARCHAR2 DEFAULT NULL,
587 x_present_set IN VARCHAR2 DEFAULT NULL,
591 x_ad_batch_id IN NUMBER DEFAULT NULL ,
588 x_curr_employment IN VARCHAR2 DEFAULT NULL,
589 x_edu_qualification IN VARCHAR2 DEFAULT NULL ,
590 -- smaddali added these columns for ucfd203 - multiple cycles bug#2669208
592 x_ad_interface_id IN NUMBER DEFAULT NULL ,
593 x_nationality IN NUMBER DEFAULT NULL ,
594 x_dual_nationality IN NUMBER DEFAULT NULL ,
595 x_special_needs IN VARCHAR2 DEFAULT NULL ,
596 x_country_birth IN NUMBER DEFAULT NULL ,
597 x_personal_id IN VARCHAR2 DEFAULT NULL
598 );
599
600 END igs_uc_applicants_pkg;