[Home] [Help]
PACKAGE BODY: APPS.IGS_UC_APPLICANTS_PKG
Source
1 PACKAGE BODY igs_uc_applicants_pkg AS
2 /* $Header: IGSXI01B.pls 120.3 2006/08/21 03:36:34 jbaber ship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igs_uc_applicants%ROWTYPE;
6 new_references igs_uc_applicants%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2 ,
11 x_app_id IN NUMBER ,
12 x_app_no IN NUMBER ,
13 x_check_digit IN NUMBER ,
14 x_enquiry_no IN NUMBER ,
15 x_oss_person_id IN NUMBER ,
16 x_application_source IN VARCHAR2 ,
17 x_name_change_date IN DATE ,
18 x_student_support IN VARCHAR2 ,
19 x_address_area IN VARCHAR2 ,
20 x_application_date IN DATE ,
21 x_application_sent_date IN DATE ,
22 x_application_sent_run IN NUMBER ,
23 x_lea_code IN NUMBER ,
24 x_fee_payer_code IN NUMBER ,
25 x_fee_text IN VARCHAR2 ,
26 x_domicile_apr IN NUMBER ,
27 x_code_changed_date IN DATE ,
28 x_school IN NUMBER ,
29 x_withdrawn IN VARCHAR2 ,
30 x_withdrawn_date IN DATE ,
31 x_rel_to_clear_reason IN VARCHAR2 ,
32 x_route_b IN VARCHAR2 ,
33 x_exam_change_date IN DATE ,
34 x_a_levels IN NUMBER ,
35 x_as_levels IN NUMBER ,
36 x_highers IN NUMBER ,
37 x_csys IN NUMBER ,
38 x_winter IN NUMBER ,
39 x_previous IN NUMBER ,
40 x_gnvq IN VARCHAR2 ,
41 x_btec IN VARCHAR2 ,
42 x_ilc IN VARCHAR2 ,
43 x_ailc IN VARCHAR2 ,
44 x_ib IN VARCHAR2 ,
45 x_manual IN VARCHAR2 ,
46 x_reg_num IN VARCHAR2 ,
47 x_oeq IN VARCHAR2 ,
48 x_eas IN VARCHAR2 ,
49 x_roa IN VARCHAR2 ,
50 x_status IN VARCHAR2 ,
51 x_firm_now IN NUMBER ,
52 x_firm_reply IN NUMBER ,
53 x_insurance_reply IN NUMBER ,
54 x_conf_hist_firm_reply IN NUMBER ,
55 x_conf_hist_ins_reply IN NUMBER ,
56 x_residential_category IN VARCHAR2 ,
57 x_personal_statement IN LONG ,
58 x_match_prev IN VARCHAR2 ,
59 x_match_prev_date IN DATE ,
60 x_match_winter IN VARCHAR2 ,
61 x_match_summer IN VARCHAR2 ,
62 x_gnvq_date IN DATE ,
63 x_ib_date IN DATE ,
64 x_ilc_date IN DATE ,
65 x_ailc_date IN DATE ,
66 x_gcseqa_date IN DATE ,
67 x_uk_entry_date IN DATE ,
68 x_prev_surname IN VARCHAR2 ,
69 x_criminal_convictions IN VARCHAR2 ,
70 x_sent_to_hesa IN VARCHAR2 ,
71 x_sent_to_oss IN VARCHAR2 ,
72 x_batch_identifier IN NUMBER ,
73 x_creation_date IN DATE ,
74 x_created_by IN NUMBER ,
75 x_last_update_date IN DATE ,
76 x_last_updated_by IN NUMBER ,
77 x_last_update_login IN NUMBER ,
78 -- Added following 8 Columns as part of UCCR002 Build. Bug NO: 2278817 by rbezawad
79 x_gce IN NUMBER ,
80 x_vce IN NUMBER ,
81 x_sqa IN VARCHAR2 ,
82 x_previousas IN NUMBER ,
83 x_keyskills IN VARCHAR2 ,
84 x_vocational IN VARCHAR2 ,
85 x_scn IN VARCHAR2 ,
86 x_prevoeq IN VARCHAR2 ,
87 -- Added the following 5 columns as part of UCFD06 Build: Bug#2574566
88 x_choices_transparent_ind IN VARCHAR2,
89 x_extra_status IN NUMBER ,
90 x_extra_passport_no IN VARCHAR2,
91 x_request_app_dets_ind IN VARCHAR2,
92 x_request_copy_app_frm_ind IN VARCHAR2,
93 x_cef_no IN NUMBER,
94 -- Added the following columns as part of UCFD102 Build: Bug#2643048
95 x_system_code IN VARCHAR2 ,
96 x_gcse_eng IN VARCHAR2 ,
97 x_gcse_math IN VARCHAR2 ,
98 x_degree_subject IN VARCHAR2 ,
99 x_degree_status IN VARCHAR2 ,
100 x_degree_class IN VARCHAR2 ,
101 x_gcse_sci IN VARCHAR2 ,
102 x_welshspeaker IN VARCHAR2 ,
103 x_ni_number IN VARCHAR2,
104 x_earliest_start IN VARCHAR2,
105 x_near_inst IN VARCHAR2,
106 x_pref_reg IN NUMBER ,
107 x_qual_eng IN VARCHAR2,
108 x_qual_math IN VARCHAR2,
109 x_qual_sci IN VARCHAR2,
110 x_main_qual IN VARCHAR2,
111 x_qual_5 IN VARCHAR2,
112 x_future_serv IN VARCHAR2,
113 x_future_set IN VARCHAR2,
114 x_present_serv IN VARCHAR2,
115 x_present_set IN VARCHAR2,
116 x_curr_employment IN VARCHAR2,
117 x_edu_qualification IN VARCHAR2,
118 -- smaddali added these columns for ucfd203 - multiple cycles bug#2669208
119 x_ad_batch_id IN NUMBER,
120 x_ad_interface_id IN NUMBER,
121 x_nationality IN NUMBER,
122 x_dual_nationality IN NUMBER,
123 x_special_needs IN VARCHAR2,
124 x_country_birth IN NUMBER,
125 x_personal_id IN VARCHAR2
126 ) AS
127 /*
128 || Created By : [email protected]
129 || Created On : 21-FEB-2002
130 || Purpose : Initialises the Old and New references for the columns of the table.
131 || Known limitations, enhancements or remarks :
132 || Change History :
133 || Who When What
134 || smaddali added columns for ucfd203 - multiple cycles bug#2669208
135 || jchin 10-Mar-05 Modified for bug 4083559/4124006 convert null to 'n' for routeb
136 || (reverse chronological order - newest change first)
137 */
138
139 CURSOR cur_old_ref_values IS
140 SELECT *
141 FROM IGS_UC_APPLICANTS
142 WHERE rowid = x_rowid;
143
144 BEGIN
145
146 l_rowid := x_rowid;
147
148 -- Code for setting the Old and New Reference Values.
149 -- Populate Old Values.
150 OPEN cur_old_ref_values;
151 FETCH cur_old_ref_values INTO old_references;
152 IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
153 CLOSE cur_old_ref_values;
154 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
155 igs_ge_msg_stack.add;
156 app_exception.raise_exception;
157 RETURN;
158 END IF;
159 CLOSE cur_old_ref_values;
160
161 -- Populate New Values.
162 new_references.app_id := x_app_id;
163 new_references.app_no := x_app_no;
164 new_references.check_digit := x_check_digit;
165 new_references.enquiry_no := x_enquiry_no;
166 new_references.oss_person_id := x_oss_person_id;
167 new_references.application_source := x_application_source;
168 new_references.name_change_date := x_name_change_date;
169 new_references.student_support := x_student_support;
170 new_references.address_area := x_address_area;
171 new_references.application_date := x_application_date;
172 new_references.application_sent_date := x_application_sent_date;
173 new_references.application_sent_run := x_application_sent_run;
174 new_references.lea_code := x_lea_code;
175 new_references.fee_payer_code := x_fee_payer_code;
176 new_references.fee_text := x_fee_text;
177 new_references.domicile_apr := x_domicile_apr;
178 new_references.code_changed_date := x_code_changed_date;
179 new_references.school := x_school;
180 new_references.withdrawn := x_withdrawn;
181 new_references.withdrawn_date := x_withdrawn_date;
182 new_references.rel_to_clear_reason := x_rel_to_clear_reason;
183 new_references.route_b := NVL(x_route_b, 'N');
184 new_references.exam_change_date := x_exam_change_date;
185 new_references.a_levels := x_a_levels;
186 new_references.as_levels := x_as_levels;
187 new_references.highers := x_highers;
188 new_references.csys := x_csys;
189 new_references.winter := x_winter;
190 new_references.previous := x_previous;
191 new_references.gnvq := x_gnvq;
192 new_references.btec := x_btec;
193 new_references.ilc := x_ilc;
194 new_references.ailc := x_ailc;
195 new_references.ib := x_ib;
196 new_references.manual := x_manual;
197 new_references.reg_num := x_reg_num;
198 new_references.oeq := x_oeq;
199 new_references.eas := x_eas;
200 new_references.roa := x_roa;
201 new_references.status := x_status;
202 new_references.firm_now := x_firm_now;
203 new_references.firm_reply := x_firm_reply;
204 new_references.insurance_reply := x_insurance_reply;
205 new_references.conf_hist_firm_reply := x_conf_hist_firm_reply;
206 new_references.conf_hist_ins_reply := x_conf_hist_ins_reply;
207 new_references.residential_category := x_residential_category;
208 new_references.personal_statement := x_personal_statement;
209 new_references.match_prev := x_match_prev;
210 new_references.match_prev_date := x_match_prev_date;
211 new_references.match_winter := x_match_winter;
212 new_references.match_summer := x_match_summer;
213 new_references.gnvq_date := x_gnvq_date;
214 new_references.ib_date := x_ib_date;
215 new_references.ilc_date := x_ilc_date;
216 new_references.ailc_date := x_ailc_date;
217 new_references.gcseqa_date := x_gcseqa_date;
218 new_references.uk_entry_date := x_uk_entry_date;
219 new_references.prev_surname := x_prev_surname;
220 new_references.criminal_convictions := x_criminal_convictions;
221 new_references.sent_to_hesa := x_sent_to_hesa;
222 new_references.sent_to_oss := x_sent_to_oss;
223 new_references.batch_identifier := x_batch_identifier;
224 new_references.gce := x_gce;
225 new_references.vce := x_vce;
226 new_references.sqa := x_sqa;
227 new_references.previousas := x_previousas;
228 new_references.keyskills := x_keyskills;
229 new_references.vocational := x_vocational;
230 new_references.scn := x_scn;
231 new_references.prevoeq := x_prevoeq;
232
233 new_references.choices_transparent_ind := x_choices_transparent_ind;
234 new_references.extra_status := x_extra_status;
235 new_references.extra_passport_no := x_extra_passport_no;
236 new_references.request_app_dets_ind := x_request_app_dets_ind;
237 new_references.request_copy_app_frm_ind := x_request_copy_app_frm_ind;
238 new_references.cef_no := x_cef_no;
239 new_references.system_code := x_system_code ;
240 new_references.gcse_eng := x_gcse_eng ;
241 new_references.gcse_math := x_gcse_math ;
242 new_references.degree_subject := x_degree_subject ;
243 new_references.degree_status := x_degree_status ;
244 new_references.degree_class := x_degree_class ;
245 new_references.gcse_sci := x_gcse_sci ;
246 new_references.welshspeaker := x_welshspeaker ;
247 new_references.ni_number := x_ni_number ;
248 new_references.earliest_start := x_earliest_start ;
249 new_references.near_inst := x_near_inst ;
250 new_references.pref_reg := x_pref_reg ;
251 new_references.qual_eng := x_qual_eng ;
252 new_references.qual_math := x_qual_math ;
253 new_references.qual_sci := x_qual_sci ;
254 new_references.main_qual := x_main_qual ;
255 new_references.qual_5 := x_qual_5 ;
256 new_references.future_serv := x_future_serv ;
257 new_references.future_set := x_future_set ;
258 new_references.present_serv := x_present_serv ;
259 new_references.present_set := x_present_set ;
260 new_references.curr_employment := x_curr_employment ;
261 new_references.edu_qualification := x_edu_qualification ;
262 -- smaddali added these columns for ucfd203 - multiple cycles bug#2669208
263 new_references.ad_batch_id := x_ad_batch_id ;
264 new_references.ad_interface_id := x_ad_interface_id ;
265 new_references.nationality := x_nationality ;
266 new_references.dual_nationality := x_dual_nationality ;
267 new_references.special_needs := x_special_needs ;
268 new_references.country_birth := x_country_birth ;
269 new_references.personal_id := x_personal_id ;
270
271
272
273
274
275 IF (p_action = 'UPDATE') THEN
279 new_references.creation_date := x_creation_date;
276 new_references.creation_date := old_references.creation_date;
277 new_references.created_by := old_references.created_by;
278 ELSE
280 new_references.created_by := x_created_by;
281 END IF;
282
283 new_references.last_update_date := x_last_update_date;
284 new_references.last_updated_by := x_last_updated_by;
285 new_references.last_update_login := x_last_update_login;
286
287 END set_column_values;
288
289
290
291 PROCEDURE check_child_existance IS
292 /*
293 || Created By : [email protected]
294 || Created On : 21-FEB-2002
295 || Purpose : Checks for the existance of Child records.
296 || Known limitations, enhancements or remarks :
297 || Change History :
298 || Who When What
299 || smaddali 6-jun-03 added new child checks for bug#2669208 UCFD203 - multiple cycles build
300 || (reverse chronological order - newest change first)
301 */
302 BEGIN
303
304 igs_uc_app_choices_pkg.get_fk_igs_uc_applicants (
305 old_references.app_id
306 );
307
308 igs_uc_app_clearing_pkg.get_fk_igs_uc_applicants (
309 old_references.app_id
310 );
311
312 igs_uc_app_stats_pkg.get_fk_igs_uc_applicants (
313 old_references.app_id
314 );
315
316 igs_uc_app_results_pkg.get_fk_igs_uc_applicants(
317 old_references.app_id
318 );
319
320 -- smaddali added these new child checks as part of bug#2669208 - ucfd203 multiple cycles build
321 igs_uc_app_addreses_pkg.get_ufk_igs_uc_applicants(
322 old_references.app_no
323 );
324
325 igs_uc_app_names_pkg.get_ufk_igs_uc_applicants(
326 old_references.app_no
327 );
328
329 igs_uc_app_referees_pkg.get_ufk_igs_uc_applicants(
330 old_references.app_no
331 );
332
333 igs_uc_form_quals_pkg.get_ufk_igs_uc_applicants(
334 old_references.app_no
335 );
336
337 END check_child_existance;
338
339
340
341 PROCEDURE check_parent_existance AS
342 /*
343 || Created By : bayadav
344 || Created On : 11-NOV-2002
345 || Purpose : Checks for the existance of Parent records.
346 || Known limitations, enhancements or remarks :
347 || Change History :
348 || Who When What
349 || (reverse chronological order - newest change first)
350 || smaddali 6-jun-03 modified igs_uc_adm_systems_pkg.get_uk_for_validation with
351 || igs_uc_defaults_pkg.get_pk_for_validation for UCFD203 - bug#2669208
352 */
353 BEGIN
354
355 IF ((old_references.system_code = new_references.system_code) OR
356 (new_references.system_code IS NULL)) THEN
357 NULL;
358 ELSIF NOT igs_uc_defaults_pkg.get_pk_for_validation (
359 new_references.system_code
360 ) THEN
361 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
362 igs_ge_msg_stack.add;
363 app_exception.raise_exception;
364 END IF;
365
366 IF (((old_references.oss_person_id = new_references.oss_person_id)) OR
367 ((new_references.oss_person_id IS NULL))) THEN
368 NULL;
369 ELSIF NOT igs_pe_person_pkg.get_pk_for_validation (
370 new_references.oss_person_id
371 ) THEN
372 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
373 igs_ge_msg_stack.add;
374 app_exception.raise_exception;
375 END IF;
376
377 END check_parent_existance;
378
379
380 PROCEDURE check_uniqueness AS
381 /*
382 || Created By : [email protected]
383 || Created On : 17-SEP-2002
384 || Purpose : Handles the Unique Constraint logic defined for the columns.
385 || Known limitations, enhancements or remarks :
386 || Change History :
387 || Who When What
388 || (reverse chronological order - newest change first)
389 */
390 BEGIN
391
392 IF ( get_uk_for_validation(new_references.app_no)
393 ) THEN
394 fnd_message.set_name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
395 igs_ge_msg_stack.add;
396 app_exception.raise_exception;
397 END IF;
398
399
400 END check_uniqueness;
401
402
403
404
405 PROCEDURE get_fk_igs_uc_defaults (
406 x_system_code IN VARCHAR2
407 ) AS
408 /*
409 || Created By : smaddali
410 || Created On : 6-jun-03
411 || Purpose : Validates the Foreign Keys for the table.
412 || Known limitations, enhancements or remarks :
413 || Change History :
414 || Who When What
415 || (reverse chronological order - newest change first)
416 */
417 CURSOR cur_rowid IS
418 SELECT rowid
419 FROM igs_uc_applicants
420 WHERE ((system_code = x_system_code));
421
422 lv_rowid cur_rowid%RowType;
423
424 BEGIN
425
426 OPEN cur_rowid;
427 FETCH cur_rowid INTO lv_rowid;
428 IF (cur_rowid%FOUND) THEN
429 CLOSE cur_rowid;
430 fnd_message.set_name ('IGS', 'IGS_UC_UCAP_UAS_FK');
431 igs_ge_msg_stack.add;
435 CLOSE cur_rowid;
432 app_exception.raise_exception;
433 RETURN;
434 END IF;
436
437 END get_fk_igs_uc_defaults;
438
439 PROCEDURE get_fk_igs_pe_person (
440 x_oss_person_id IN NUMBER
441 ) AS
442 /*
443 || Created By : rbezawad
444 || Created On : 12-FEB-2002
445 || Purpose : Validates the Foreign Keys for the table.
446 || Known limitations, enhancements or remarks :
447 || Change History :
448 || Who When What
449 || (reverse chronological order - newest change first)
450 */
451 CURSOR cur_rowid IS
452 SELECT rowid
453 FROM igs_uc_applicants
454 WHERE ((oss_person_id = x_oss_person_id));
455
456 lv_rowid cur_rowid%RowType;
457
458 BEGIN
459
460 OPEN cur_rowid;
461 FETCH cur_rowid INTO lv_rowid;
462 IF (cur_rowid%FOUND) THEN
463 CLOSE cur_rowid;
464 fnd_message.set_name ('IGS', 'IGS_UC_UCAP_PE_FK');
465 igs_ge_msg_stack.add;
466 app_exception.raise_exception;
467 RETURN;
468 END IF;
469 CLOSE cur_rowid;
470
471 END get_fk_igs_pe_person;
472
473 FUNCTION get_pk_for_validation (
474 x_app_id IN NUMBER
475 ) RETURN BOOLEAN AS
476 /*
477 || Created By : [email protected]
478 || Created On : 21-FEB-2002
479 || Purpose : Validates the Primary Key of the table.
480 || Known limitations, enhancements or remarks :
481 || Change History :
482 || Who When What
483 || (reverse chronological order - newest change first)
484 */
485 CURSOR cur_rowid IS
486 SELECT rowid
487 FROM igs_uc_applicants
488 WHERE app_id = x_app_id ;
489
490 lv_rowid cur_rowid%RowType;
491
492 BEGIN
493
494 OPEN cur_rowid;
495 FETCH cur_rowid INTO lv_rowid;
496 IF (cur_rowid%FOUND) THEN
497 CLOSE cur_rowid;
498 RETURN(TRUE);
499 ELSE
500 CLOSE cur_rowid;
501 RETURN(FALSE);
502 END IF;
503
504 END get_pk_for_validation;
505
506
507 FUNCTION get_uk_for_validation (
508 x_app_no IN NUMBER
509 ) RETURN BOOLEAN AS
510 /*
511 || Created By : bayadav
512 || Created On : 23-OCT-2002
513 || Purpose : Validates the Primary Key of the table.
514 || Known limitations, enhancements or remarks :
515 || Change History :
516 || Who When What
517 || Pmarada 17-dec-02 Added the rowid where condition in the cursor
518 || (reverse chronological order - newest change first)
519 */
520 CURSOR cur_rowid IS
521 SELECT rowid
522 FROM igs_uc_applicants
523 WHERE app_no = x_app_no
524 AND ((l_rowid IS NULL) OR (rowid <> l_rowid)) ;
525
526 lv_rowid cur_rowid%RowType;
527
528 BEGIN
529
530 OPEN cur_rowid;
531 FETCH cur_rowid INTO lv_rowid;
532 IF (cur_rowid%FOUND) THEN
533 CLOSE cur_rowid;
534 RETURN(TRUE);
535 ELSE
536 CLOSE cur_rowid;
537 RETURN(FALSE);
538 END IF;
539
540 END get_uk_for_validation;
541
542
543 PROCEDURE before_dml (
544 p_action IN VARCHAR2,
545 x_rowid IN VARCHAR2 ,
546 x_app_id IN NUMBER ,
547 x_app_no IN NUMBER ,
548 x_check_digit IN NUMBER ,
549 x_enquiry_no IN NUMBER ,
550 x_oss_person_id IN NUMBER ,
551 x_application_source IN VARCHAR2 ,
552 x_name_change_date IN DATE ,
553 x_student_support IN VARCHAR2 ,
554 x_address_area IN VARCHAR2 ,
555 x_application_date IN DATE ,
556 x_application_sent_date IN DATE ,
557 x_application_sent_run IN NUMBER ,
558 x_lea_code IN NUMBER ,
559 x_fee_payer_code IN NUMBER ,
560 x_fee_text IN VARCHAR2 ,
561 x_domicile_apr IN NUMBER ,
562 x_code_changed_date IN DATE ,
563 x_school IN NUMBER ,
564 x_withdrawn IN VARCHAR2 ,
565 x_withdrawn_date IN DATE ,
566 x_rel_to_clear_reason IN VARCHAR2 ,
567 x_route_b IN VARCHAR2 ,
568 x_exam_change_date IN DATE ,
569 x_a_levels IN NUMBER ,
570 x_as_levels IN NUMBER ,
571 x_highers IN NUMBER ,
572 x_csys IN NUMBER ,
573 x_winter IN NUMBER ,
574 x_previous IN NUMBER ,
575 x_gnvq IN VARCHAR2 ,
579 x_ib IN VARCHAR2 ,
576 x_btec IN VARCHAR2 ,
577 x_ilc IN VARCHAR2 ,
578 x_ailc IN VARCHAR2 ,
580 x_manual IN VARCHAR2 ,
581 x_reg_num IN VARCHAR2 ,
582 x_oeq IN VARCHAR2 ,
583 x_eas IN VARCHAR2 ,
584 x_roa IN VARCHAR2 ,
585 x_status IN VARCHAR2 ,
586 x_firm_now IN NUMBER ,
587 x_firm_reply IN NUMBER ,
588 x_insurance_reply IN NUMBER ,
589 x_conf_hist_firm_reply IN NUMBER ,
590 x_conf_hist_ins_reply IN NUMBER ,
591 x_residential_category IN VARCHAR2 ,
592 x_personal_statement IN LONG ,
593 x_match_prev IN VARCHAR2 ,
594 x_match_prev_date IN DATE ,
595 x_match_winter IN VARCHAR2 ,
596 x_match_summer IN VARCHAR2 ,
597 x_gnvq_date IN DATE ,
598 x_ib_date IN DATE ,
599 x_ilc_date IN DATE ,
600 x_ailc_date IN DATE ,
601 x_gcseqa_date IN DATE ,
602 x_uk_entry_date IN DATE ,
603 x_prev_surname IN VARCHAR2 ,
604 x_criminal_convictions IN VARCHAR2 ,
605 x_sent_to_hesa IN VARCHAR2 ,
606 x_sent_to_oss IN VARCHAR2 ,
607 x_batch_identifier IN NUMBER ,
608 x_creation_date IN DATE ,
609 x_created_by IN NUMBER ,
610 x_last_update_date IN DATE ,
611 x_last_updated_by IN NUMBER ,
612 x_last_update_login IN NUMBER ,
613 -- Added following 8 Columns as part of UCCR002 Build. Bug NO: 2278817 by rbezawad
614 x_gce IN NUMBER ,
615 x_vce IN NUMBER ,
616 x_sqa IN VARCHAR2 ,
617 x_previousas IN NUMBER ,
618 x_keyskills IN VARCHAR2 ,
619 x_vocational IN VARCHAR2 ,
620 x_scn IN VARCHAR2 ,
621 x_prevoeq IN VARCHAR2 ,
622 x_choices_transparent_ind IN VARCHAR2,
623 x_extra_status IN NUMBER,
624 x_extra_passport_no IN VARCHAR2,
625 x_request_app_dets_ind IN VARCHAR2,
626 x_request_copy_app_frm_ind IN VARCHAR2,
627 x_cef_no IN NUMBER,
628 -- Added the following columns as part of UCFD102 Build: Bug#2643048
629 x_system_code IN VARCHAR2 ,
630 x_gcse_eng IN VARCHAR2 ,
631 x_gcse_math IN VARCHAR2 ,
632 x_degree_subject IN VARCHAR2 ,
633 x_degree_status IN VARCHAR2 ,
634 x_degree_class IN VARCHAR2 ,
635 x_gcse_sci IN VARCHAR2 ,
636 x_welshspeaker IN VARCHAR2 ,
637 x_ni_number IN VARCHAR2,
638 x_earliest_start IN VARCHAR2,
639 x_near_inst IN VARCHAR2,
640 x_pref_reg IN NUMBER ,
641 x_qual_eng IN VARCHAR2,
642 x_qual_math IN VARCHAR2,
643 x_qual_sci IN VARCHAR2,
644 x_main_qual IN VARCHAR2,
645 x_qual_5 IN VARCHAR2,
646 x_future_serv IN VARCHAR2,
647 x_future_set IN VARCHAR2,
648 x_present_serv IN VARCHAR2,
649 x_present_set IN VARCHAR2,
650 x_curr_employment IN VARCHAR2,
651 x_edu_qualification IN VARCHAR2,
652 -- smaddali added these columns for ucfd203 - multiple cycles bug#2669208
653 x_ad_batch_id IN NUMBER,
654 x_ad_interface_id IN NUMBER,
655 x_nationality IN NUMBER,
656 x_dual_nationality IN NUMBER,
657 x_special_needs IN VARCHAR2,
658 x_country_birth IN NUMBER,
659 x_personal_id IN VARCHAR2
660
661 ) AS
662 /*
663 || Created By : [email protected]
664 || Created On : 21-FEB-2002
665 || Purpose : Initialises the columns, Checks Constraints, Calls the
666 || Trigger Handlers for the table, before any DML operation.
667 || Known limitations, enhancements or remarks :
671 || (reverse chronological order - newest change first)
668 || Change History :
669 || Who When What
670 || smaddali added columns for ucfd203 - multiple cycles bug#2669208
672 || dsridhar 27-AUG-2003 Bug No: 3087784. Resetting the value of l_rowid to NULL.
673 */
674 BEGIN
675
676 set_column_values (
677 p_action,
678 x_rowid,
679 x_app_id,
680 x_app_no,
681 x_check_digit,
682 x_enquiry_no,
683 x_oss_person_id,
684 x_application_source,
685 x_name_change_date,
686 x_student_support,
687 x_address_area,
688 x_application_date,
689 x_application_sent_date,
690 x_application_sent_run,
691 x_lea_code,
692 x_fee_payer_code,
693 x_fee_text,
694 x_domicile_apr,
695 x_code_changed_date,
696 x_school,
697 x_withdrawn,
698 x_withdrawn_date,
699 x_rel_to_clear_reason,
700 x_route_b,
701 x_exam_change_date,
702 x_a_levels,
703 x_as_levels,
704 x_highers,
705 x_csys,
706 x_winter,
707 x_previous,
708 x_gnvq,
709 x_btec,
710 x_ilc,
711 x_ailc,
712 x_ib,
713 x_manual,
714 x_reg_num,
715 x_oeq,
716 x_eas,
717 x_roa,
718 x_status,
719 x_firm_now,
720 x_firm_reply,
721 x_insurance_reply,
722 x_conf_hist_firm_reply,
723 x_conf_hist_ins_reply,
724 x_residential_category,
725 x_personal_statement,
726 x_match_prev,
727 x_match_prev_date,
728 x_match_winter,
729 x_match_summer,
730 x_gnvq_date,
731 x_ib_date,
732 x_ilc_date,
733 x_ailc_date,
734 x_gcseqa_date,
735 x_uk_entry_date,
736 x_prev_surname,
737 x_criminal_convictions,
738 x_sent_to_hesa,
739 x_sent_to_oss,
740 x_batch_identifier,
741 x_creation_date,
742 x_created_by,
743 x_last_update_date,
744 x_last_updated_by,
745 x_last_update_login,
746 x_gce,
747 x_vce,
748 x_sqa,
749 x_previousas,
750 x_keyskills,
751 x_vocational,
752 x_scn,
753 x_prevoeq,
754 x_choices_transparent_ind,
755 x_extra_status ,
756 x_extra_passport_no ,
757 x_request_app_dets_ind ,
758 x_request_copy_app_frm_ind,
759 x_cef_no,
760 x_system_code ,
761 x_gcse_eng ,
762 x_gcse_math ,
763 x_degree_subject ,
764 x_degree_status ,
765 x_degree_class ,
766 x_gcse_sci ,
767 x_welshspeaker ,
768 x_ni_number ,
769 x_earliest_start ,
770 x_near_inst ,
771 x_pref_reg ,
772 x_qual_eng ,
773 x_qual_math ,
774 x_qual_sci ,
775 x_main_qual ,
776 x_qual_5 ,
777 x_future_serv ,
778 x_future_set ,
779 x_present_serv ,
780 x_present_set ,
781 x_curr_employment ,
782 x_edu_qualification ,
783 -- smaddali added these columns for ucfd203 - multiple cycles bug#2669208
784 x_ad_batch_id ,
785 x_ad_interface_id ,
786 x_nationality ,
787 x_dual_nationality ,
788 x_special_needs ,
789 x_country_birth ,
790 x_personal_id
791 );
792
793 IF (p_action = 'INSERT') THEN
794 -- Call all the procedures related to Before Insert.
795 IF ( get_pk_for_validation(
796 new_references.app_id
797 )
798 ) THEN
799 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
800 igs_ge_msg_stack.add;
801 app_exception.raise_exception;
802 END IF;
803 check_parent_existance;
804 check_uniqueness;
805 ELSIF (p_action = 'UPDATE') THEN
806 -- Call all the procedures related to Before Update.
807 check_parent_existance;
808 check_uniqueness;
809 ELSIF (p_action = 'DELETE') THEN
810 -- Call all the procedures related to Before Delete.
811 check_child_existance;
812 ELSIF (p_action = 'VALIDATE_INSERT') THEN
813 -- Call all the procedures related to Before Insert.
814 IF ( get_pk_for_validation (
815 new_references.app_id
816 )
817 ) THEN
818 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
819 igs_ge_msg_stack.add;
820 app_exception.raise_exception;
821 END IF;
822 ELSIF (p_action = 'VALIDATE_DELETE') THEN
823 check_child_existance;
824 END IF;
825
826 -- Bug No: 3087784. Resetting the value of l_rowid to NULL.
827 IF (p_action IN ('VALIDATE_INSERT', 'VALIDATE_UPDATE', 'VALIDATE_DELETE')) THEN
828 l_rowid := NULL;
829 END IF;
830
831 END before_dml;
835 x_app_id IN OUT NOCOPY NUMBER,
832
833 PROCEDURE insert_row (
834 x_rowid IN OUT NOCOPY VARCHAR2,
836 x_app_no IN NUMBER,
837 x_check_digit IN NUMBER,
838 x_enquiry_no IN OUT NOCOPY NUMBER,
839 x_oss_person_id IN NUMBER,
840 x_application_source IN VARCHAR2,
841 x_name_change_date IN DATE,
842 x_student_support IN VARCHAR2,
843 x_address_area IN VARCHAR2,
844 x_application_date IN DATE,
845 x_application_sent_date IN DATE,
846 x_application_sent_run IN NUMBER,
847 x_lea_code IN NUMBER,
848 x_fee_payer_code IN NUMBER,
849 x_fee_text IN VARCHAR2,
850 x_domicile_apr IN NUMBER,
851 x_code_changed_date IN DATE,
852 x_school IN NUMBER,
853 x_withdrawn IN VARCHAR2,
854 x_withdrawn_date IN DATE,
855 x_rel_to_clear_reason IN VARCHAR2,
856 x_route_b IN VARCHAR2,
857 x_exam_change_date IN DATE,
858 x_a_levels IN NUMBER,
859 x_as_levels IN NUMBER,
860 x_highers IN NUMBER,
861 x_csys IN NUMBER,
862 x_winter IN NUMBER,
863 x_previous IN NUMBER,
864 x_gnvq IN VARCHAR2,
865 x_btec IN VARCHAR2,
866 x_ilc IN VARCHAR2,
867 x_ailc IN VARCHAR2,
868 x_ib IN VARCHAR2,
869 x_manual IN VARCHAR2,
870 x_reg_num IN VARCHAR2,
871 x_oeq IN VARCHAR2,
872 x_eas IN VARCHAR2,
873 x_roa IN VARCHAR2,
874 x_status IN VARCHAR2,
875 x_firm_now IN NUMBER,
876 x_firm_reply IN NUMBER,
877 x_insurance_reply IN NUMBER,
878 x_conf_hist_firm_reply IN NUMBER,
879 x_conf_hist_ins_reply IN NUMBER,
880 x_residential_category IN VARCHAR2,
881 x_personal_statement IN LONG,
882 x_match_prev IN VARCHAR2,
883 x_match_prev_date IN DATE,
884 x_match_winter IN VARCHAR2,
885 x_match_summer IN VARCHAR2,
886 x_gnvq_date IN DATE,
887 x_ib_date IN DATE,
888 x_ilc_date IN DATE,
889 x_ailc_date IN DATE,
890 x_gcseqa_date IN DATE,
891 x_uk_entry_date IN DATE,
892 x_prev_surname IN VARCHAR2,
893 x_criminal_convictions IN VARCHAR2,
894 x_sent_to_hesa IN VARCHAR2,
895 x_sent_to_oss IN VARCHAR2,
896 x_batch_identifier IN NUMBER,
897 x_mode IN VARCHAR2 ,
898 -- Added following 8 Columns as part of UCCR002 Build. Bug NO: 2278817 by rbezawad
899 x_gce IN NUMBER ,
900 x_vce IN NUMBER ,
901 x_sqa IN VARCHAR2 ,
902 x_previousas IN NUMBER ,
903 x_keyskills IN VARCHAR2 ,
904 x_vocational IN VARCHAR2 ,
905 x_scn IN VARCHAR2 ,
906 x_prevoeq IN VARCHAR2 ,
907 x_choices_transparent_ind IN VARCHAR2,
908 x_extra_status IN NUMBER,
909 x_extra_passport_no IN VARCHAR2,
910 x_request_app_dets_ind IN VARCHAR2,
911 x_request_copy_app_frm_ind IN VARCHAR2,
912 x_cef_no IN NUMBER,
913 -- Added the following columns as part of UCFD102 Build: Bug#2643048
914 x_system_code IN VARCHAR2 ,
915 x_gcse_eng IN VARCHAR2 ,
916 x_gcse_math IN VARCHAR2 ,
917 x_degree_subject IN VARCHAR2 ,
918 x_degree_status IN VARCHAR2 ,
919 x_degree_class IN VARCHAR2 ,
920 x_gcse_sci IN VARCHAR2 ,
921 x_welshspeaker IN VARCHAR2 ,
922 x_ni_number IN VARCHAR2,
923 x_earliest_start IN VARCHAR2,
924 x_near_inst IN VARCHAR2,
925 x_pref_reg IN NUMBER ,
926 x_qual_eng IN VARCHAR2,
927 x_qual_math IN VARCHAR2,
928 x_qual_sci IN VARCHAR2,
932 x_future_set IN VARCHAR2,
929 x_main_qual IN VARCHAR2,
930 x_qual_5 IN VARCHAR2,
931 x_future_serv IN VARCHAR2,
933 x_present_serv IN VARCHAR2,
934 x_present_set IN VARCHAR2,
935 x_curr_employment IN VARCHAR2,
936 x_edu_qualification IN VARCHAR2,
937 -- smaddali added these columns for ucfd203 - multiple cycles bug#2669208
938 x_ad_batch_id IN NUMBER,
939 x_ad_interface_id IN NUMBER,
940 x_nationality IN NUMBER,
941 x_dual_nationality IN NUMBER,
942 x_special_needs IN VARCHAR2,
943 x_country_birth IN NUMBER,
944 x_personal_id IN VARCHAR2
945 ) AS
946 /*
947 || Created By : [email protected]
948 || Created On : 21-FEB-2002
949 || Purpose : Handles the INSERT DML logic for the table.
950 || Known limitations, enhancements or remarks :
951 || Change History :
952 || Who When What
953 || Nishikant 24SEP2002 The value for the column enquiry_no was being populated from a sequence
954 || igs_uc_applicants_s2 which is removed now. UCFD06-Bug#2574566.
955 || smaddali added columns for ucfd203 - multiple cycles bug#2669208
956 || (reverse chronological order - newest change first)
957 || dsridhar 27-AUG-2003 Bug No: 3087784. Resetting the value of l_rowid to NULL.
958 */
959 CURSOR c IS
960 SELECT rowid
961 FROM igs_uc_applicants
962 WHERE app_id = x_app_id;
963
964 x_last_update_date DATE;
965 x_last_updated_by NUMBER;
966 x_last_update_login NUMBER;
967
968 BEGIN
969
970 x_last_update_date := SYSDATE;
971 IF (x_mode = 'I') THEN
972 x_last_updated_by := 1;
973 x_last_update_login := 0;
974 ELSIF (X_MODE IN ('R', 'S')) THEN
975 x_last_updated_by := fnd_global.user_id;
976 IF (x_last_updated_by IS NULL) THEN
977 x_last_updated_by := -1;
978 END IF;
979 x_last_update_login := fnd_global.login_id;
980 IF (x_last_update_login IS NULL) THEN
981 x_last_update_login := -1;
982 END IF;
983 ELSE
984 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
985 igs_ge_msg_stack.add;
986 app_exception.raise_exception;
987 END IF;
988
989 SELECT igs_uc_applicants_s1.NEXTVAL
990 INTO x_app_id
991 FROM dual;
992
993 before_dml(
994 p_action => 'INSERT',
995 x_rowid => x_rowid,
996 x_app_id => x_app_id,
997 x_app_no => x_app_no,
998 x_check_digit => x_check_digit,
999 x_enquiry_no => x_enquiry_no,
1000 x_oss_person_id => x_oss_person_id,
1001 x_application_source => x_application_source,
1002 x_name_change_date => x_name_change_date,
1003 x_student_support => x_student_support,
1004 x_address_area => x_address_area,
1005 x_application_date => x_application_date,
1006 x_application_sent_date => x_application_sent_date,
1007 x_application_sent_run => x_application_sent_run,
1008 x_lea_code => x_lea_code,
1009 x_fee_payer_code => x_fee_payer_code,
1010 x_fee_text => x_fee_text,
1011 x_domicile_apr => x_domicile_apr,
1012 x_code_changed_date => x_code_changed_date,
1013 x_school => x_school,
1014 x_withdrawn => x_withdrawn,
1015 x_withdrawn_date => x_withdrawn_date,
1016 x_rel_to_clear_reason => x_rel_to_clear_reason,
1017 x_route_b => x_route_b,
1018 x_exam_change_date => x_exam_change_date,
1019 x_a_levels => x_a_levels,
1020 x_as_levels => x_as_levels,
1021 x_highers => x_highers,
1022 x_csys => x_csys,
1023 x_winter => x_winter,
1024 x_previous => x_previous,
1025 x_gnvq => x_gnvq,
1026 x_btec => x_btec,
1027 x_ilc => x_ilc,
1028 x_ailc => x_ailc,
1029 x_ib => x_ib,
1030 x_manual => x_manual,
1031 x_reg_num => x_reg_num,
1032 x_oeq => x_oeq,
1033 x_eas => x_eas,
1034 x_roa => x_roa,
1035 x_status => x_status,
1036 x_firm_now => x_firm_now,
1037 x_firm_reply => x_firm_reply,
1038 x_insurance_reply => x_insurance_reply,
1039 x_conf_hist_firm_reply => x_conf_hist_firm_reply,
1040 x_conf_hist_ins_reply => x_conf_hist_ins_reply,
1041 x_residential_category => x_residential_category,
1045 x_match_winter => x_match_winter,
1042 x_personal_statement => x_personal_statement,
1043 x_match_prev => x_match_prev,
1044 x_match_prev_date => x_match_prev_date,
1046 x_match_summer => x_match_summer,
1047 x_gnvq_date => x_gnvq_date,
1048 x_ib_date => x_ib_date,
1049 x_ilc_date => x_ilc_date,
1050 x_ailc_date => x_ailc_date,
1051 x_gcseqa_date => x_gcseqa_date,
1052 x_uk_entry_date => x_uk_entry_date,
1053 x_prev_surname => x_prev_surname,
1054 x_criminal_convictions => x_criminal_convictions,
1055 x_sent_to_hesa => x_sent_to_hesa,
1056 x_sent_to_oss => x_sent_to_oss,
1057 x_batch_identifier => x_batch_identifier,
1058 x_creation_date => x_last_update_date,
1059 x_created_by => x_last_updated_by,
1060 x_last_update_date => x_last_update_date,
1061 x_last_updated_by => x_last_updated_by,
1062 x_last_update_login => x_last_update_login,
1063 x_gce => x_gce,
1064 x_vce => x_vce,
1065 x_sqa => x_sqa,
1066 x_previousas => x_previousas,
1067 x_keyskills => x_keyskills,
1068 x_vocational => x_vocational,
1069 x_scn => x_scn,
1070 x_prevoeq => x_prevoeq ,
1071 x_choices_transparent_ind => x_choices_transparent_ind ,
1072 x_extra_status => x_extra_status ,
1073 x_extra_passport_no => x_extra_passport_no ,
1074 x_request_app_dets_ind => x_request_app_dets_ind ,
1075 x_request_copy_app_frm_ind => x_request_copy_app_frm_ind,
1076 x_cef_no => x_cef_no,
1077 x_system_code => x_system_code ,
1078 x_gcse_eng => x_gcse_eng ,
1079 x_gcse_math => x_gcse_math ,
1080 x_degree_subject => x_degree_subject ,
1081 x_degree_status => x_degree_status ,
1082 x_degree_class => x_degree_class ,
1083 x_gcse_sci => x_gcse_sci ,
1084 x_welshspeaker => x_welshspeaker ,
1085 x_ni_number => x_ni_number ,
1086 x_earliest_start => x_earliest_start ,
1087 x_near_inst => x_near_inst ,
1088 x_pref_reg => x_pref_reg ,
1089 x_qual_eng => x_qual_eng ,
1090 x_qual_math => x_qual_math ,
1091 x_qual_sci => x_qual_sci ,
1092 x_main_qual => x_main_qual ,
1093 x_qual_5 => x_qual_5 ,
1094 x_future_serv => x_future_serv ,
1095 x_future_set => x_future_set ,
1096 x_present_serv => x_present_serv ,
1097 x_present_set => x_present_set ,
1098 x_curr_employment => x_curr_employment ,
1099 x_edu_qualification => x_edu_qualification ,
1100 -- smaddali added these columns for ucfd203 - multiple cycles bug#2669208
1101 x_ad_batch_id => x_ad_batch_id ,
1102 x_ad_interface_id => x_ad_interface_id ,
1103 x_nationality => x_nationality ,
1104 x_dual_nationality => x_dual_nationality ,
1105 x_special_needs => x_special_needs ,
1106 x_country_birth => x_country_birth ,
1107 x_personal_id => x_personal_id
1108 );
1109
1110 IF (x_mode = 'S') THEN
1111 igs_sc_gen_001.set_ctx('R');
1112 END IF;
1113 INSERT INTO igs_uc_applicants (
1114 app_id,
1115 app_no,
1116 check_digit,
1117 enquiry_no,
1118 oss_person_id,
1119 application_source,
1120 name_change_date,
1121 student_support,
1122 address_area,
1123 application_date,
1124 application_sent_date,
1125 application_sent_run,
1126 lea_code,
1127 fee_payer_code,
1128 fee_text,
1129 domicile_apr,
1130 code_changed_date,
1131 school,
1132 withdrawn,
1133 withdrawn_date,
1134 rel_to_clear_reason,
1135 route_b,
1136 exam_change_date,
1137 a_levels,
1138 as_levels,
1139 highers,
1140 csys,
1141 winter,
1142 previous,
1143 gnvq,
1144 btec,
1145 ilc,
1146 ailc,
1147 ib,
1148 manual,
1149 reg_num,
1150 oeq,
1151 eas,
1152 roa,
1153 status,
1154 firm_now,
1155 firm_reply,
1156 insurance_reply,
1157 conf_hist_firm_reply,
1158 conf_hist_ins_reply,
1159 residential_category,
1160 personal_statement,
1161 match_prev,
1162 match_prev_date,
1163 match_winter,
1164 match_summer,
1165 gnvq_date,
1166 ib_date,
1167 ilc_date,
1168 ailc_date,
1169 gcseqa_date,
1170 uk_entry_date,
1171 prev_surname,
1175 batch_identifier,
1172 criminal_convictions,
1173 sent_to_hesa,
1174 sent_to_oss,
1176 creation_date,
1177 created_by,
1178 last_update_date,
1179 last_updated_by,
1180 last_update_login,
1181 gce,
1182 vce,
1183 sqa,
1184 previousas,
1185 keyskills,
1186 vocational,
1187 scn,
1188 prevoeq ,
1189 choices_transparent_ind,
1190 extra_status,
1191 extra_passport_no,
1192 request_app_dets_ind,
1193 request_copy_app_frm_ind,
1194 cef_no,
1195 system_code ,
1196 gcse_eng ,
1197 gcse_math ,
1198 degree_subject ,
1199 degree_status ,
1200 degree_class ,
1201 gcse_sci ,
1202 welshspeaker ,
1203 ni_number ,
1204 earliest_start ,
1205 near_inst ,
1206 pref_reg ,
1207 qual_eng ,
1208 qual_math ,
1209 qual_sci ,
1210 main_qual ,
1211 qual_5 ,
1212 future_serv ,
1213 future_set ,
1214 present_serv ,
1215 present_set ,
1216 curr_employment ,
1217 edu_qualification ,
1218 -- smaddali added these columns for ucfd203 - multiple cycles bug#2669208
1219 ad_batch_id ,
1220 ad_interface_id ,
1221 nationality ,
1222 dual_nationality ,
1223 special_needs ,
1224 country_birth ,
1225 personal_id
1226 ) VALUES (
1227 new_references.app_id,
1228 new_references.app_no,
1229 new_references.check_digit,
1230 new_references.enquiry_no,
1231 new_references.oss_person_id,
1232 new_references.application_source,
1233 new_references.name_change_date,
1234 new_references.student_support,
1235 new_references.address_area,
1236 new_references.application_date,
1237 new_references.application_sent_date,
1238 new_references.application_sent_run,
1239 new_references.lea_code,
1240 new_references.fee_payer_code,
1241 new_references.fee_text,
1242 new_references.domicile_apr,
1243 new_references.code_changed_date,
1244 new_references.school,
1245 new_references.withdrawn,
1246 new_references.withdrawn_date,
1247 new_references.rel_to_clear_reason,
1248 new_references.route_b,
1249 new_references.exam_change_date,
1250 new_references.a_levels,
1251 new_references.as_levels,
1252 new_references.highers,
1253 new_references.csys,
1254 new_references.winter,
1255 new_references.previous,
1256 new_references.gnvq,
1257 new_references.btec,
1258 new_references.ilc,
1259 new_references.ailc,
1260 new_references.ib,
1261 new_references.manual,
1262 new_references.reg_num,
1263 new_references.oeq,
1264 new_references.eas,
1265 new_references.roa,
1266 new_references.status,
1267 new_references.firm_now,
1268 new_references.firm_reply,
1269 new_references.insurance_reply,
1270 new_references.conf_hist_firm_reply,
1271 new_references.conf_hist_ins_reply,
1272 new_references.residential_category,
1273 new_references.personal_statement,
1274 new_references.match_prev,
1275 new_references.match_prev_date,
1276 new_references.match_winter,
1277 new_references.match_summer,
1278 new_references.gnvq_date,
1279 new_references.ib_date,
1280 new_references.ilc_date,
1281 new_references.ailc_date,
1282 new_references.gcseqa_date,
1283 new_references.uk_entry_date,
1284 new_references.prev_surname,
1285 new_references.criminal_convictions,
1286 new_references.sent_to_hesa,
1287 new_references.sent_to_oss,
1288 new_references.batch_identifier,
1289 x_last_update_date,
1290 x_last_updated_by,
1291 x_last_update_date,
1292 x_last_updated_by,
1293 x_last_update_login,
1294 new_references.gce,
1295 new_references.vce,
1296 new_references.sqa,
1297 new_references.previousas,
1298 new_references.keyskills,
1299 new_references.vocational,
1300 new_references.scn,
1301 new_references.prevoeq,
1302 new_references.choices_transparent_ind,
1303 new_references.extra_status,
1304 new_references.extra_passport_no,
1305 new_references.request_app_dets_ind,
1306 new_references.request_copy_app_frm_ind,
1307 new_references.cef_no,
1308 new_references.system_code ,
1309 new_references.gcse_eng ,
1310 new_references.gcse_math ,
1311 new_references.degree_subject ,
1312 new_references.degree_status ,
1313 new_references.degree_class ,
1314 new_references.gcse_sci ,
1315 new_references.welshspeaker ,
1316 new_references.ni_number ,
1317 new_references.earliest_start ,
1321 new_references.qual_math ,
1318 new_references.near_inst ,
1319 new_references.pref_reg ,
1320 new_references.qual_eng ,
1322 new_references.qual_sci ,
1323 new_references.main_qual ,
1324 new_references.qual_5 ,
1325 new_references.future_serv ,
1326 new_references.future_set ,
1327 new_references.present_serv ,
1328 new_references.present_set ,
1329 new_references.curr_employment ,
1330 new_references.edu_qualification ,
1331 -- smaddali added these columns for ucfd203 - multiple cycles bug#2669208
1332 new_references.ad_batch_id ,
1333 new_references.ad_interface_id ,
1334 new_references.nationality ,
1335 new_references.dual_nationality ,
1336 new_references.special_needs ,
1337 new_references.country_birth ,
1338 new_references.personal_id
1339 );
1340 IF (x_mode = 'S') THEN
1341 igs_sc_gen_001.unset_ctx('R');
1342 END IF;
1343
1344
1345 OPEN c;
1346 FETCH c INTO x_rowid;
1347 IF (c%NOTFOUND) THEN
1348 CLOSE c;
1349 RAISE NO_DATA_FOUND;
1350 END IF;
1351 CLOSE c;
1352
1353 -- Bug No: 3087784. Resetting the value of l_rowid to NULL.
1354 l_rowid := NULL;
1355
1356
1357 EXCEPTION
1358 WHEN OTHERS THEN
1359 IF (SQLCODE IN (-28115, -28113, -28111)) THEN
1360 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_EXCEPTION');
1361 fnd_message.set_token ('ERR_CD', SQLCODE);
1362 igs_ge_msg_stack.add;
1363 igs_sc_gen_001.unset_ctx('R');
1364 app_exception.raise_exception;
1365 ELSE
1366 igs_sc_gen_001.unset_ctx('R');
1367 RAISE;
1368 END IF;
1369 END insert_row;
1370
1371
1372 PROCEDURE lock_row (
1373 x_rowid IN VARCHAR2,
1374 x_app_id IN NUMBER,
1375 x_app_no IN NUMBER,
1376 x_check_digit IN NUMBER,
1377 x_enquiry_no IN NUMBER,
1378 x_oss_person_id IN NUMBER,
1379 x_application_source IN VARCHAR2,
1380 x_name_change_date IN DATE,
1381 x_student_support IN VARCHAR2,
1382 x_address_area IN VARCHAR2,
1383 x_application_date IN DATE,
1384 x_application_sent_date IN DATE,
1385 x_application_sent_run IN NUMBER,
1386 x_lea_code IN NUMBER,
1387 x_fee_payer_code IN NUMBER,
1388 x_fee_text IN VARCHAR2,
1389 x_domicile_apr IN NUMBER,
1390 x_code_changed_date IN DATE,
1391 x_school IN NUMBER,
1392 x_withdrawn IN VARCHAR2,
1393 x_withdrawn_date IN DATE,
1394 x_rel_to_clear_reason IN VARCHAR2,
1395 x_route_b IN VARCHAR2,
1396 x_exam_change_date IN DATE,
1397 x_a_levels IN NUMBER,
1398 x_as_levels IN NUMBER,
1399 x_highers IN NUMBER,
1400 x_csys IN NUMBER,
1401 x_winter IN NUMBER,
1402 x_previous IN NUMBER,
1403 x_gnvq IN VARCHAR2,
1404 x_btec IN VARCHAR2,
1405 x_ilc IN VARCHAR2,
1406 x_ailc IN VARCHAR2,
1407 x_ib IN VARCHAR2,
1408 x_manual IN VARCHAR2,
1409 x_reg_num IN VARCHAR2,
1410 x_oeq IN VARCHAR2,
1411 x_eas IN VARCHAR2,
1412 x_roa IN VARCHAR2,
1413 x_status IN VARCHAR2,
1414 x_firm_now IN NUMBER,
1415 x_firm_reply IN NUMBER,
1416 x_insurance_reply IN NUMBER,
1417 x_conf_hist_firm_reply IN NUMBER,
1418 x_conf_hist_ins_reply IN NUMBER,
1419 x_residential_category IN VARCHAR2,
1420 x_personal_statement IN LONG,
1421 x_match_prev IN VARCHAR2,
1422 x_match_prev_date IN DATE,
1423 x_match_winter IN VARCHAR2,
1424 x_match_summer IN VARCHAR2,
1425 x_gnvq_date IN DATE,
1426 x_ib_date IN DATE,
1427 x_ilc_date IN DATE,
1428 x_ailc_date IN DATE,
1429 x_gcseqa_date IN DATE,
1430 x_uk_entry_date IN DATE,
1431 x_prev_surname IN VARCHAR2,
1432 x_criminal_convictions IN VARCHAR2,
1433 x_sent_to_hesa IN VARCHAR2,
1437 x_gce IN NUMBER ,
1434 x_sent_to_oss IN VARCHAR2,
1435 x_batch_identifier IN NUMBER,
1436 -- Added following 8 Columns as part of UCCR002 Build. Bug NO: 2278817 by rbezawad
1438 x_vce IN NUMBER ,
1439 x_sqa IN VARCHAR2 ,
1440 x_previousas IN NUMBER ,
1441 x_keyskills IN VARCHAR2 ,
1442 x_vocational IN VARCHAR2 ,
1443 x_scn IN VARCHAR2 ,
1444 x_prevoeq IN VARCHAR2 ,
1445 x_choices_transparent_ind IN VARCHAR2,
1446 x_extra_status IN NUMBER,
1447 x_extra_passport_no IN VARCHAR2,
1448 x_request_app_dets_ind IN VARCHAR2,
1449 x_request_copy_app_frm_ind IN VARCHAR2,
1450 x_cef_no IN NUMBER,
1451 -- Added the following columns as part of UCFD102 Build: Bug#2643048
1452 x_system_code IN VARCHAR2 ,
1453 x_gcse_eng IN VARCHAR2 ,
1454 x_gcse_math IN VARCHAR2 ,
1455 x_degree_subject IN VARCHAR2 ,
1456 x_degree_status IN VARCHAR2 ,
1457 x_degree_class IN VARCHAR2 ,
1458 x_gcse_sci IN VARCHAR2 ,
1459 x_welshspeaker IN VARCHAR2 ,
1460 x_ni_number IN VARCHAR2,
1461 x_earliest_start IN VARCHAR2,
1462 x_near_inst IN VARCHAR2,
1463 x_pref_reg IN NUMBER ,
1464 x_qual_eng IN VARCHAR2,
1465 x_qual_math IN VARCHAR2,
1466 x_qual_sci IN VARCHAR2,
1467 x_main_qual IN VARCHAR2,
1468 x_qual_5 IN VARCHAR2,
1469 x_future_serv IN VARCHAR2,
1470 x_future_set IN VARCHAR2,
1471 x_present_serv IN VARCHAR2,
1472 x_present_set IN VARCHAR2,
1473 x_curr_employment IN VARCHAR2,
1474 x_edu_qualification IN VARCHAR2,
1475 -- smaddali added these columns for ucfd203 - multiple cycles bug#2669208
1476 x_ad_batch_id IN NUMBER,
1477 x_ad_interface_id IN NUMBER,
1478 x_nationality IN NUMBER,
1479 x_dual_nationality IN NUMBER,
1480 x_special_needs IN VARCHAR2,
1481 x_country_birth IN NUMBER,
1482 x_personal_id IN VARCHAR2
1483 ) AS
1484 /*
1485 || Created By : [email protected]
1486 || Created On : 21-FEB-2002
1487 || Purpose : Handles the LOCK mechanism for the table.
1488 || Known limitations, enhancements or remarks :
1489 || Change History :
1490 || Who When What
1491 || smaddali added columns for ucfd203 - multiple cycles bug#2669208
1492 || (reverse chronological order - newest change first)
1493 */
1494 CURSOR c1 IS
1495 SELECT
1496 app_no,
1497 check_digit,
1498 enquiry_no,
1499 oss_person_id,
1500 application_source,
1501 name_change_date,
1502 student_support,
1503 address_area,
1504 application_date,
1505 application_sent_date,
1506 application_sent_run,
1507 lea_code,
1508 fee_payer_code,
1509 fee_text,
1510 domicile_apr,
1511 code_changed_date,
1512 school,
1513 withdrawn,
1514 withdrawn_date,
1515 rel_to_clear_reason,
1516 route_b,
1517 exam_change_date,
1518 a_levels,
1519 as_levels,
1520 highers,
1521 csys,
1522 winter,
1523 previous,
1524 gnvq,
1525 btec,
1526 ilc,
1527 ailc,
1528 ib,
1529 manual,
1530 reg_num,
1531 oeq,
1532 eas,
1533 roa,
1534 status,
1535 firm_now,
1536 firm_reply,
1537 insurance_reply,
1538 conf_hist_firm_reply,
1539 conf_hist_ins_reply,
1540 residential_category,
1541 personal_statement,
1542 match_prev,
1543 match_prev_date,
1544 match_winter,
1545 match_summer,
1546 gnvq_date,
1547 ib_date,
1548 ilc_date,
1549 ailc_date,
1550 gcseqa_date,
1551 uk_entry_date,
1552 prev_surname,
1553 criminal_convictions,
1554 sent_to_hesa,
1555 sent_to_oss,
1556 batch_identifier,
1557 gce,
1558 vce,
1559 sqa,
1560 previousas,
1564 prevoeq ,
1561 keyskills,
1562 vocational,
1563 scn,
1565 choices_transparent_ind,
1566 extra_status,
1567 extra_passport_no,
1568 request_app_dets_ind,
1569 request_copy_app_frm_ind,
1570 cef_no,
1571 system_code ,
1572 gcse_eng ,
1573 gcse_math ,
1574 degree_subject ,
1575 degree_status ,
1576 degree_class ,
1577 gcse_sci ,
1578 welshspeaker ,
1579 ni_number ,
1580 earliest_start ,
1581 near_inst ,
1582 pref_reg ,
1583 qual_eng ,
1584 qual_math ,
1585 qual_sci ,
1586 main_qual ,
1587 qual_5 ,
1588 future_serv ,
1589 future_set ,
1590 present_serv ,
1591 present_set ,
1592 curr_employment ,
1593 edu_qualification,
1594 -- smaddali added these columns for ucfd203 - multiple cycles bug#2669208
1595 ad_batch_id ,
1596 ad_interface_id ,
1597 nationality ,
1598 dual_nationality ,
1599 special_needs ,
1600 country_birth ,
1601 personal_id
1602 FROM igs_uc_applicants
1603 WHERE rowid = x_rowid
1604 FOR UPDATE NOWAIT;
1605
1606 tlinfo c1%ROWTYPE;
1607
1608 BEGIN
1609
1610 OPEN c1;
1611 FETCH c1 INTO tlinfo;
1612 IF (c1%notfound) THEN
1613 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
1614 igs_ge_msg_stack.add;
1615 CLOSE c1;
1616 app_exception.raise_exception;
1617 RETURN;
1618 END IF;
1619 CLOSE c1;
1620
1621 IF (
1622 ((tlinfo.app_no = x_app_no) OR ((tlinfo.app_no IS NULL) AND (X_app_no IS NULL)))
1623 AND ((tlinfo.check_digit = x_check_digit) OR ((tlinfo.check_digit IS NULL) AND (X_check_digit IS NULL)))
1624 AND ((tlinfo.enquiry_no = x_enquiry_no) OR ((tlinfo.enquiry_no IS NULL) AND (X_enquiry_no IS NULL)))
1625 AND ((tlinfo.oss_person_id = x_oss_person_id) OR ((tlinfo.oss_person_id IS NULL) AND (X_oss_person_id IS NULL)))
1626 AND (tlinfo.application_source = x_application_source)
1627 AND ((tlinfo.name_change_date = x_name_change_date) OR ((tlinfo.name_change_date IS NULL) AND (X_name_change_date IS NULL)))
1628 AND ((tlinfo.student_support = x_student_support) OR ((tlinfo.student_support IS NULL) AND (X_student_support IS NULL)))
1629 AND ((tlinfo.address_area = x_address_area) OR ((tlinfo.address_area IS NULL) AND (X_address_area IS NULL)))
1630 AND ((tlinfo.application_date = x_application_date) OR ((tlinfo.application_date IS NULL) AND (X_application_date IS NULL)))
1631 AND ((tlinfo.application_sent_date = x_application_sent_date) OR ((tlinfo.application_sent_date IS NULL) AND (X_application_sent_date IS NULL)))
1632 AND ((tlinfo.application_sent_run = x_application_sent_run) OR ((tlinfo.application_sent_run IS NULL) AND (X_application_sent_run IS NULL)))
1633 AND ((tlinfo.lea_code = x_lea_code) OR ((tlinfo.lea_code IS NULL) AND (X_lea_code IS NULL)))
1634 AND ((tlinfo.fee_payer_code = x_fee_payer_code) OR ((tlinfo.fee_payer_code IS NULL) AND (X_fee_payer_code IS NULL)))
1635 AND ((tlinfo.fee_text = x_fee_text) OR ((tlinfo.fee_text IS NULL) AND (X_fee_text IS NULL)))
1636 AND ((tlinfo.domicile_apr = x_domicile_apr) OR ((tlinfo.domicile_apr IS NULL) AND (X_domicile_apr IS NULL)))
1637 AND ((tlinfo.code_changed_date = x_code_changed_date) OR ((tlinfo.code_changed_date IS NULL) AND (X_code_changed_date IS NULL)))
1638 AND ((tlinfo.school = x_school) OR ((tlinfo.school IS NULL) AND (X_school IS NULL)))
1639 AND ((tlinfo.withdrawn = x_withdrawn) OR ((tlinfo.withdrawn IS NULL) AND (X_withdrawn IS NULL)))
1640 AND ((tlinfo.withdrawn_date = x_withdrawn_date) OR ((tlinfo.withdrawn_date IS NULL) AND (X_withdrawn_date IS NULL)))
1641 AND ((tlinfo.rel_to_clear_reason = x_rel_to_clear_reason) OR ((tlinfo.rel_to_clear_reason IS NULL) AND (X_rel_to_clear_reason IS NULL)))
1642 AND (tlinfo.route_b = x_route_b)
1643 AND ((tlinfo.exam_change_date = x_exam_change_date) OR ((tlinfo.exam_change_date IS NULL) AND (X_exam_change_date IS NULL)))
1644 AND ((tlinfo.a_levels = x_a_levels) OR ((tlinfo.a_levels IS NULL) AND (X_a_levels IS NULL)))
1645 AND ((tlinfo.as_levels = x_as_levels) OR ((tlinfo.as_levels IS NULL) AND (X_as_levels IS NULL)))
1646 AND ((tlinfo.highers = x_highers) OR ((tlinfo.highers IS NULL) AND (X_highers IS NULL)))
1647 AND ((tlinfo.csys = x_csys) OR ((tlinfo.csys IS NULL) AND (X_csys IS NULL)))
1648 AND ((tlinfo.winter = x_winter) OR ((tlinfo.winter IS NULL) AND (X_winter IS NULL)))
1649 AND ((tlinfo.previous = x_previous) OR ((tlinfo.previous IS NULL) AND (X_previous IS NULL)))
1650 AND ((tlinfo.gnvq = x_gnvq) OR ((tlinfo.gnvq IS NULL) AND (X_gnvq IS NULL)))
1651 AND ((tlinfo.btec = x_btec) OR ((tlinfo.btec IS NULL) AND (X_btec IS NULL)))
1652 AND ((tlinfo.ilc = x_ilc) OR ((tlinfo.ilc IS NULL) AND (X_ilc IS NULL)))
1653 AND ((tlinfo.ailc = x_ailc) OR ((tlinfo.ailc IS NULL) AND (X_ailc IS NULL)))
1654 AND ((tlinfo.ib = x_ib) OR ((tlinfo.ib IS NULL) AND (X_ib IS NULL)))
1655 AND ((tlinfo.manual = x_manual) OR ((tlinfo.manual IS NULL) AND (X_manual IS NULL)))
1656 AND ((tlinfo.reg_num = x_reg_num) OR ((tlinfo.reg_num IS NULL) AND (X_reg_num IS NULL)))
1657 AND ((tlinfo.oeq = x_oeq) OR ((tlinfo.oeq IS NULL) AND (X_oeq IS NULL)))
1661 AND ((tlinfo.firm_now = x_firm_now) OR ((tlinfo.firm_now IS NULL) AND (X_firm_now IS NULL)))
1658 AND ((tlinfo.eas = x_eas) OR ((tlinfo.eas IS NULL) AND (X_eas IS NULL)))
1659 AND ((tlinfo.roa = x_roa) OR ((tlinfo.roa IS NULL) AND (X_roa IS NULL)))
1660 AND ((tlinfo.status = x_status) OR ((tlinfo.status IS NULL) AND (X_status IS NULL)))
1662 AND ((tlinfo.firm_reply = x_firm_reply) OR ((tlinfo.firm_reply IS NULL) AND (X_firm_reply IS NULL)))
1663 AND ((tlinfo.insurance_reply = x_insurance_reply) OR ((tlinfo.insurance_reply IS NULL) AND (X_insurance_reply IS NULL)))
1664 AND ((tlinfo.conf_hist_firm_reply = x_conf_hist_firm_reply) OR ((tlinfo.conf_hist_firm_reply IS NULL) AND (X_conf_hist_firm_reply IS NULL)))
1665 AND ((tlinfo.conf_hist_ins_reply = x_conf_hist_ins_reply) OR ((tlinfo.conf_hist_ins_reply IS NULL) AND (X_conf_hist_ins_reply IS NULL)))
1666 AND ((tlinfo.residential_category = x_residential_category) OR ((tlinfo.residential_category IS NULL) AND (X_residential_category IS NULL)))
1667 AND ((tlinfo.personal_statement = x_personal_statement) OR ((tlinfo.personal_statement IS NULL) AND (X_personal_statement IS NULL)))
1668 AND ((tlinfo.match_prev = x_match_prev) OR ((tlinfo.match_prev IS NULL) AND (X_match_prev IS NULL)))
1669 AND ((tlinfo.match_prev_date = x_match_prev_date) OR ((tlinfo.match_prev_date IS NULL) AND (X_match_prev_date IS NULL)))
1670 AND ((tlinfo.match_winter = x_match_winter) OR ((tlinfo.match_winter IS NULL) AND (X_match_winter IS NULL)))
1671 AND ((tlinfo.match_summer = x_match_summer) OR ((tlinfo.match_summer IS NULL) AND (X_match_summer IS NULL)))
1672 AND ((tlinfo.gnvq_date = x_gnvq_date) OR ((tlinfo.gnvq_date IS NULL) AND (X_gnvq_date IS NULL)))
1673 AND ((tlinfo.ib_date = x_ib_date) OR ((tlinfo.ib_date IS NULL) AND (X_ib_date IS NULL)))
1674 AND ((tlinfo.ilc_date = x_ilc_date) OR ((tlinfo.ilc_date IS NULL) AND (X_ilc_date IS NULL)))
1675 AND ((tlinfo.ailc_date = x_ailc_date) OR ((tlinfo.ailc_date IS NULL) AND (X_ailc_date IS NULL)))
1676 AND ((tlinfo.gcseqa_date = x_gcseqa_date) OR ((tlinfo.gcseqa_date IS NULL) AND (X_gcseqa_date IS NULL)))
1677 AND ((tlinfo.uk_entry_date = x_uk_entry_date) OR ((tlinfo.uk_entry_date IS NULL) AND (X_uk_entry_date IS NULL)))
1678 AND ((tlinfo.prev_surname = x_prev_surname) OR ((tlinfo.prev_surname IS NULL) AND (X_prev_surname IS NULL)))
1679 AND ((tlinfo.criminal_convictions = x_criminal_convictions) OR ((tlinfo.criminal_convictions IS NULL) AND (x_criminal_convictions IS NULL)))
1680 AND (tlinfo.sent_to_hesa = x_sent_to_hesa)
1681 AND ((tlinfo.sent_to_oss = x_sent_to_oss) OR ((tlinfo.sent_to_oss IS NULL) AND (X_sent_to_oss IS NULL)))
1682 AND ((tlinfo.batch_identifier = x_batch_identifier) OR ((tlinfo.batch_identifier IS NULL) AND (X_batch_identifier IS NULL)))
1683 AND ((tlinfo.gce = x_gce ) OR ((tlinfo.gce IS NULL) AND ( x_gce IS NULL)))
1684 AND ((tlinfo.vce = x_vce ) OR ((tlinfo.vce IS NULL) AND ( x_vce IS NULL)))
1685 AND ((tlinfo.sqa = x_sqa ) OR ((tlinfo.sqa IS NULL) AND ( x_sqa IS NULL)))
1686 AND ((tlinfo.previousas = x_previousas) OR ((tlinfo.previousas IS NULL) AND ( x_previousas IS NULL)))
1687 AND ((tlinfo.keyskills = x_keyskills ) OR ((tlinfo.keyskills IS NULL) AND ( x_keyskills IS NULL)))
1688 AND ((tlinfo.vocational = x_vocational) OR ((tlinfo.vocational IS NULL) AND ( x_vocational IS NULL)))
1689 AND ((tlinfo.scn = x_scn ) OR ((tlinfo.scn IS NULL) AND ( x_scn IS NULL)))
1690 AND ((tlinfo.prevoeq = x_prevoeq ) OR ((tlinfo.prevoeq IS NULL) AND ( x_prevoeq IS NULL)))
1691 AND ((tlinfo.choices_transparent_ind = x_choices_transparent_ind ) OR ((tlinfo.choices_transparent_ind IS NULL) AND ( x_choices_transparent_ind IS NULL)))
1692 AND ((tlinfo.extra_status = x_extra_status ) OR ((tlinfo.extra_status IS NULL) AND ( x_extra_status IS NULL)))
1693 AND ((tlinfo.extra_passport_no = x_extra_passport_no ) OR ((tlinfo.extra_passport_no IS NULL) AND ( x_extra_passport_no IS NULL)))
1694 AND ((tlinfo.request_app_dets_ind = x_request_app_dets_ind ) OR ((tlinfo.request_app_dets_ind IS NULL) AND ( x_request_app_dets_ind IS NULL)))
1695 AND ((tlinfo.request_copy_app_frm_ind = x_request_copy_app_frm_ind ) OR ((tlinfo.request_copy_app_frm_ind IS NULL) AND ( x_request_copy_app_frm_ind IS NULL)))
1696 AND ((tlinfo.cef_no = x_cef_no ) OR ((tlinfo.cef_no IS NULL) AND ( x_cef_no IS NULL)))
1697 AND (tlinfo.system_code = x_system_code )
1698 AND ((tlinfo.gcse_eng = x_gcse_eng ) OR (( tlinfo.gcse_eng IS NULL) AND ( x_gcse_eng IS NULL)))
1699 AND ((tlinfo.gcse_math = x_gcse_math ) OR (( tlinfo.gcse_math IS NULL) AND ( x_gcse_math IS NULL)))
1700 AND ((tlinfo.degree_subject = x_degree_subject ) OR (( tlinfo.degree_subject IS NULL) AND ( x_degree_subject IS NULL)))
1701 AND ((tlinfo.degree_status = x_degree_status ) OR (( tlinfo.degree_status IS NULL) AND ( x_degree_status IS NULL)))
1702 AND ((tlinfo.degree_class = x_degree_class ) OR (( tlinfo.degree_class IS NULL) AND ( x_degree_class IS NULL)))
1703 AND ((tlinfo.gcse_sci = x_gcse_sci ) OR (( tlinfo.gcse_sci IS NULL) AND ( x_gcse_sci IS NULL)))
1704 AND ((tlinfo.welshspeaker = x_welshspeaker ) OR (( tlinfo.welshspeaker IS NULL) AND ( x_welshspeaker IS NULL)))
1705 AND ((tlinfo.ni_number = x_ni_number ) OR (( tlinfo.ni_number IS NULL) AND ( x_ni_number IS NULL)))
1709 AND ((tlinfo.qual_eng = x_qual_eng ) OR (( tlinfo.qual_eng IS NULL) AND ( x_qual_eng IS NULL)))
1706 AND ((tlinfo.earliest_start = x_earliest_start ) OR (( tlinfo.earliest_start IS NULL) AND ( x_earliest_start IS NULL)))
1707 AND ((tlinfo.near_inst = x_near_inst ) OR (( tlinfo.near_inst IS NULL) AND ( x_near_inst IS NULL)))
1708 AND ((tlinfo.pref_reg = x_pref_reg ) OR (( tlinfo.pref_reg IS NULL) AND ( x_pref_reg IS NULL)))
1710 AND ((tlinfo.qual_math = x_qual_math ) OR (( tlinfo.qual_math IS NULL) AND ( x_qual_math IS NULL)))
1711 AND ((tlinfo.qual_sci = x_qual_sci ) OR (( tlinfo.qual_sci IS NULL) AND ( x_qual_sci IS NULL)))
1712 AND ((tlinfo.main_qual = x_main_qual ) OR (( tlinfo.main_qual IS NULL) AND ( x_main_qual IS NULL)))
1713 AND ((tlinfo.qual_5 = x_qual_5 ) OR (( tlinfo.qual_5 IS NULL) AND ( x_qual_5 IS NULL)))
1714 AND ((tlinfo.future_serv = x_future_serv ) OR (( tlinfo.future_serv IS NULL) AND ( x_future_serv IS NULL)))
1715 AND ((tlinfo.future_set = x_future_set ) OR (( tlinfo.future_set IS NULL) AND ( x_future_set IS NULL)))
1716 AND ((tlinfo.present_serv = x_present_serv ) OR (( tlinfo.present_serv IS NULL) AND ( x_present_serv IS NULL)))
1717 AND ((tlinfo.present_set = x_present_set ) OR (( tlinfo.present_set IS NULL) AND ( x_present_set IS NULL)))
1718 AND ((tlinfo.curr_employment = x_curr_employment ) OR (( tlinfo.curr_employment IS NULL) AND ( x_curr_employment IS NULL)))
1719 AND ((tlinfo.edu_qualification = x_edu_qualification ) OR (( tlinfo.edu_qualification IS NULL) AND ( x_edu_qualification IS NULL)))
1720 -- smaddali added these columns for ucfd203 - multiple cycles bug#2669208
1721 AND ((tlinfo.ad_batch_id = x_ad_batch_id ) OR (( tlinfo.ad_batch_id IS NULL) AND ( x_ad_batch_id IS NULL)))
1722 AND ((tlinfo.ad_interface_id = x_ad_interface_id ) OR (( tlinfo.ad_interface_id IS NULL) AND ( x_ad_interface_id IS NULL)))
1723 AND ((tlinfo.nationality = x_nationality ) OR (( tlinfo.nationality IS NULL) AND ( x_nationality IS NULL)))
1724 AND ((tlinfo.dual_nationality = x_dual_nationality ) OR (( tlinfo.dual_nationality IS NULL) AND ( x_edu_qualification IS NULL)))
1725 AND ((tlinfo.special_needs = x_special_needs ) OR (( tlinfo.special_needs IS NULL) AND ( x_special_needs IS NULL)))
1726 AND ((tlinfo.country_birth = x_country_birth ) OR (( tlinfo.country_birth IS NULL) AND ( x_country_birth IS NULL)))
1727 AND ((tlinfo.personal_id = x_personal_id ) OR (( tlinfo.personal_id IS NULL) AND ( x_personal_id IS NULL)))
1728 ) THEN
1729 NULL;
1730 ELSE
1731 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
1732 igs_ge_msg_stack.add;
1733 app_exception.raise_exception;
1734 END IF;
1735
1736 RETURN;
1737
1738 END lock_row;
1739
1740
1741 PROCEDURE update_row (
1742 x_rowid IN VARCHAR2,
1743 x_app_id IN NUMBER,
1744 x_app_no IN NUMBER,
1745 x_check_digit IN NUMBER,
1746 x_enquiry_no IN NUMBER,
1747 x_oss_person_id IN NUMBER,
1748 x_application_source IN VARCHAR2,
1749 x_name_change_date IN DATE,
1750 x_student_support IN VARCHAR2,
1751 x_address_area IN VARCHAR2,
1752 x_application_date IN DATE,
1753 x_application_sent_date IN DATE,
1754 x_application_sent_run IN NUMBER,
1755 x_lea_code IN NUMBER,
1756 x_fee_payer_code IN NUMBER,
1757 x_fee_text IN VARCHAR2,
1758 x_domicile_apr IN NUMBER,
1759 x_code_changed_date IN DATE,
1760 x_school IN NUMBER,
1761 x_withdrawn IN VARCHAR2,
1762 x_withdrawn_date IN DATE,
1763 x_rel_to_clear_reason IN VARCHAR2,
1764 x_route_b IN VARCHAR2,
1765 x_exam_change_date IN DATE,
1766 x_a_levels IN NUMBER,
1767 x_as_levels IN NUMBER,
1768 x_highers IN NUMBER,
1769 x_csys IN NUMBER,
1770 x_winter IN NUMBER,
1771 x_previous IN NUMBER,
1772 x_gnvq IN VARCHAR2,
1773 x_btec IN VARCHAR2,
1774 x_ilc IN VARCHAR2,
1775 x_ailc IN VARCHAR2,
1776 x_ib IN VARCHAR2,
1780 x_eas IN VARCHAR2,
1777 x_manual IN VARCHAR2,
1778 x_reg_num IN VARCHAR2,
1779 x_oeq IN VARCHAR2,
1781 x_roa IN VARCHAR2,
1782 x_status IN VARCHAR2,
1783 x_firm_now IN NUMBER,
1784 x_firm_reply IN NUMBER,
1785 x_insurance_reply IN NUMBER,
1786 x_conf_hist_firm_reply IN NUMBER,
1787 x_conf_hist_ins_reply IN NUMBER,
1788 x_residential_category IN VARCHAR2,
1789 x_personal_statement IN LONG,
1790 x_match_prev IN VARCHAR2,
1791 x_match_prev_date IN DATE,
1792 x_match_winter IN VARCHAR2,
1793 x_match_summer IN VARCHAR2,
1794 x_gnvq_date IN DATE,
1795 x_ib_date IN DATE,
1796 x_ilc_date IN DATE,
1797 x_ailc_date IN DATE,
1798 x_gcseqa_date IN DATE,
1799 x_uk_entry_date IN DATE,
1800 x_prev_surname IN VARCHAR2,
1801 x_criminal_convictions IN VARCHAR2,
1802 x_sent_to_hesa IN VARCHAR2,
1803 x_sent_to_oss IN VARCHAR2,
1804 x_batch_identifier IN NUMBER,
1805 x_mode IN VARCHAR2 ,
1806 -- Added following 8 Columns as part of UCCR002 Build. Bug NO: 2278817 by rbezawad
1807 x_gce IN NUMBER ,
1808 x_vce IN NUMBER ,
1809 x_sqa IN VARCHAR2 ,
1810 x_previousas IN NUMBER ,
1811 x_keyskills IN VARCHAR2 ,
1812 x_vocational IN VARCHAR2 ,
1813 x_scn IN VARCHAR2 ,
1814 x_prevoeq IN VARCHAR2 ,
1815 x_choices_transparent_ind IN VARCHAR2,
1816 x_extra_status IN NUMBER,
1817 x_extra_passport_no IN VARCHAR2,
1818 x_request_app_dets_ind IN VARCHAR2,
1819 x_request_copy_app_frm_ind IN VARCHAR2,
1820 x_cef_no IN NUMBER,
1821 -- Added the following columns as part of UCFD102 Build: Bug#2643048
1822 x_system_code IN VARCHAR2 ,
1823 x_gcse_eng IN VARCHAR2 ,
1824 x_gcse_math IN VARCHAR2 ,
1825 x_degree_subject IN VARCHAR2 ,
1826 x_degree_status IN VARCHAR2 ,
1827 x_degree_class IN VARCHAR2 ,
1828 x_gcse_sci IN VARCHAR2 ,
1829 x_welshspeaker IN VARCHAR2 ,
1830 x_ni_number IN VARCHAR2,
1831 x_earliest_start IN VARCHAR2,
1832 x_near_inst IN VARCHAR2,
1833 x_pref_reg IN NUMBER ,
1834 x_qual_eng IN VARCHAR2,
1835 x_qual_math IN VARCHAR2,
1836 x_qual_sci IN VARCHAR2,
1837 x_main_qual IN VARCHAR2,
1838 x_qual_5 IN VARCHAR2,
1839 x_future_serv IN VARCHAR2,
1840 x_future_set IN VARCHAR2,
1841 x_present_serv IN VARCHAR2,
1842 x_present_set IN VARCHAR2,
1843 x_curr_employment IN VARCHAR2,
1844 x_edu_qualification IN VARCHAR2,
1845 -- smaddali added these columns for ucfd203 - multiple cycles bug#2669208
1846 x_ad_batch_id IN NUMBER,
1847 x_ad_interface_id IN NUMBER,
1848 x_nationality IN NUMBER,
1849 x_dual_nationality IN NUMBER,
1850 x_special_needs IN VARCHAR2,
1851 x_country_birth IN NUMBER,
1852 x_personal_id IN VARCHAR2
1853 ) AS
1854 /*
1855 || Created By : [email protected]
1856 || Created On : 21-FEB-2002
1857 || Purpose : Handles the UPDATE DML logic for the table.
1858 || Known limitations, enhancements or remarks :
1859 || Change History :
1860 || Who When What
1861 || smaddali added columns for ucfd203 - multiple cycles bug#2669208
1862 || (reverse chronological order - newest change first)
1863 || dsridhar 27-AUG-2003 Bug No: 3087784. Resetting the value of l_rowid to NULL.
1864 */
1865 x_last_update_date DATE ;
1866 x_last_updated_by NUMBER;
1867 x_last_update_login NUMBER;
1868
1869 BEGIN
1870
1871 x_last_update_date := SYSDATE;
1872 IF (X_MODE = 'I') THEN
1873 x_last_updated_by := 1;
1874 x_last_update_login := 0;
1875 ELSIF (X_MODE IN ('R', 'S')) THEN
1876 x_last_updated_by := fnd_global.user_id;
1880 x_last_update_login := fnd_global.login_id;
1877 IF x_last_updated_by IS NULL THEN
1878 x_last_updated_by := -1;
1879 END IF;
1881 IF (x_last_update_login IS NULL) THEN
1882 x_last_update_login := -1;
1883 END IF;
1884 ELSE
1885 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
1886 igs_ge_msg_stack.add;
1887 app_exception.raise_exception;
1888 END IF;
1889
1890 before_dml(
1891 p_action => 'UPDATE',
1892 x_rowid => x_rowid,
1893 x_app_id => x_app_id,
1894 x_app_no => x_app_no,
1895 x_check_digit => x_check_digit,
1896 x_enquiry_no => x_enquiry_no,
1897 x_oss_person_id => x_oss_person_id,
1898 x_application_source => x_application_source,
1899 x_name_change_date => x_name_change_date,
1900 x_student_support => x_student_support,
1901 x_address_area => x_address_area,
1902 x_application_date => x_application_date,
1903 x_application_sent_date => x_application_sent_date,
1904 x_application_sent_run => x_application_sent_run,
1905 x_lea_code => x_lea_code,
1906 x_fee_payer_code => x_fee_payer_code,
1907 x_fee_text => x_fee_text,
1908 x_domicile_apr => x_domicile_apr,
1909 x_code_changed_date => x_code_changed_date,
1910 x_school => x_school,
1911 x_withdrawn => x_withdrawn,
1912 x_withdrawn_date => x_withdrawn_date,
1913 x_rel_to_clear_reason => x_rel_to_clear_reason,
1914 x_route_b => x_route_b,
1915 x_exam_change_date => x_exam_change_date,
1916 x_a_levels => x_a_levels,
1917 x_as_levels => x_as_levels,
1918 x_highers => x_highers,
1919 x_csys => x_csys,
1920 x_winter => x_winter,
1921 x_previous => x_previous,
1922 x_gnvq => x_gnvq,
1923 x_btec => x_btec,
1924 x_ilc => x_ilc,
1925 x_ailc => x_ailc,
1926 x_ib => x_ib,
1927 x_manual => x_manual,
1928 x_reg_num => x_reg_num,
1929 x_oeq => x_oeq,
1930 x_eas => x_eas,
1931 x_roa => x_roa,
1932 x_status => x_status,
1933 x_firm_now => x_firm_now,
1934 x_firm_reply => x_firm_reply,
1935 x_insurance_reply => x_insurance_reply,
1936 x_conf_hist_firm_reply => x_conf_hist_firm_reply,
1937 x_conf_hist_ins_reply => x_conf_hist_ins_reply,
1938 x_residential_category => x_residential_category,
1939 x_personal_statement => x_personal_statement,
1940 x_match_prev => x_match_prev,
1941 x_match_prev_date => x_match_prev_date,
1942 x_match_winter => x_match_winter,
1943 x_match_summer => x_match_summer,
1944 x_gnvq_date => x_gnvq_date,
1945 x_ib_date => x_ib_date,
1946 x_ilc_date => x_ilc_date,
1947 x_ailc_date => x_ailc_date,
1948 x_gcseqa_date => x_gcseqa_date,
1949 x_uk_entry_date => x_uk_entry_date,
1950 x_prev_surname => x_prev_surname,
1951 x_criminal_convictions => x_criminal_convictions,
1952 x_sent_to_hesa => x_sent_to_hesa,
1953 x_sent_to_oss => x_sent_to_oss,
1954 x_batch_identifier => x_batch_identifier,
1955 x_creation_date => x_last_update_date,
1956 x_created_by => x_last_updated_by,
1957 x_last_update_date => x_last_update_date,
1958 x_last_updated_by => x_last_updated_by,
1959 x_last_update_login => x_last_update_login,
1960 x_gce => x_gce,
1961 x_vce => x_vce,
1962 x_sqa => x_sqa,
1963 x_previousas => x_previousas,
1964 x_keyskills => x_keyskills,
1965 x_vocational => x_vocational,
1966 x_scn => x_scn,
1967 x_prevoeq => x_prevoeq ,
1968 x_choices_transparent_ind => x_choices_transparent_ind ,
1969 x_extra_status => x_extra_status ,
1970 x_extra_passport_no => x_extra_passport_no ,
1971 x_request_app_dets_ind => x_request_app_dets_ind ,
1972 x_request_copy_app_frm_ind => x_request_copy_app_frm_ind,
1973 x_cef_no => x_cef_no,
1974 x_system_code => x_system_code ,
1975 x_gcse_eng => x_gcse_eng ,
1976 x_gcse_math => x_gcse_math ,
1980 x_gcse_sci => x_gcse_sci ,
1977 x_degree_subject => x_degree_subject ,
1978 x_degree_status => x_degree_status ,
1979 x_degree_class => x_degree_class ,
1981 x_welshspeaker => x_welshspeaker ,
1982 x_ni_number => x_ni_number ,
1983 x_earliest_start => x_earliest_start ,
1984 x_near_inst => x_near_inst ,
1985 x_pref_reg => x_pref_reg ,
1986 x_qual_eng => x_qual_eng ,
1987 x_qual_math => x_qual_math ,
1988 x_qual_sci => x_qual_sci ,
1989 x_main_qual => x_main_qual ,
1990 x_qual_5 => x_qual_5 ,
1991 x_future_serv => x_future_serv ,
1992 x_future_set => x_future_set ,
1993 x_present_serv => x_present_serv ,
1994 x_present_set => x_present_set ,
1995 x_curr_employment => x_curr_employment ,
1996 x_edu_qualification => x_edu_qualification ,
1997 -- smaddali added these columns for ucfd203 - multiple cycles bug#2669208
1998 x_ad_batch_id => x_ad_batch_id ,
1999 x_ad_interface_id => x_ad_interface_id ,
2000 x_nationality => x_nationality ,
2001 x_dual_nationality => x_dual_nationality ,
2002 x_special_needs => x_special_needs ,
2003 x_country_birth => x_country_birth ,
2004 x_personal_id => x_personal_id
2005 );
2006
2007 IF (x_mode = 'S') THEN
2008 igs_sc_gen_001.set_ctx('R');
2009 END IF;
2010 UPDATE igs_uc_applicants
2011 SET
2012 app_no = new_references.app_no,
2013 check_digit = new_references.check_digit,
2014 enquiry_no = new_references.enquiry_no,
2015 oss_person_id = new_references.oss_person_id,
2016 application_source = new_references.application_source,
2017 name_change_date = new_references.name_change_date,
2018 student_support = new_references.student_support,
2019 address_area = new_references.address_area,
2020 application_date = new_references.application_date,
2021 application_sent_date = new_references.application_sent_date,
2022 application_sent_run = new_references.application_sent_run,
2023 lea_code = new_references.lea_code,
2024 fee_payer_code = new_references.fee_payer_code,
2025 fee_text = new_references.fee_text,
2026 domicile_apr = new_references.domicile_apr,
2027 code_changed_date = new_references.code_changed_date,
2028 school = new_references.school,
2029 withdrawn = new_references.withdrawn,
2030 withdrawn_date = new_references.withdrawn_date,
2031 rel_to_clear_reason = new_references.rel_to_clear_reason,
2032 route_b = new_references.route_b,
2033 exam_change_date = new_references.exam_change_date,
2034 a_levels = new_references.a_levels,
2035 as_levels = new_references.as_levels,
2036 highers = new_references.highers,
2037 csys = new_references.csys,
2038 winter = new_references.winter,
2039 previous = new_references.previous,
2040 gnvq = new_references.gnvq,
2041 btec = new_references.btec,
2042 ilc = new_references.ilc,
2043 ailc = new_references.ailc,
2044 ib = new_references.ib,
2045 manual = new_references.manual,
2046 reg_num = new_references.reg_num,
2047 oeq = new_references.oeq,
2048 eas = new_references.eas,
2049 roa = new_references.roa,
2050 status = new_references.status,
2051 firm_now = new_references.firm_now,
2052 firm_reply = new_references.firm_reply,
2053 insurance_reply = new_references.insurance_reply,
2054 conf_hist_firm_reply = new_references.conf_hist_firm_reply,
2055 conf_hist_ins_reply = new_references.conf_hist_ins_reply,
2056 residential_category = new_references.residential_category,
2057 personal_statement = new_references.personal_statement,
2058 match_prev = new_references.match_prev,
2059 match_prev_date = new_references.match_prev_date,
2060 match_winter = new_references.match_winter,
2061 match_summer = new_references.match_summer,
2062 gnvq_date = new_references.gnvq_date,
2063 ib_date = new_references.ib_date,
2064 ilc_date = new_references.ilc_date,
2065 ailc_date = new_references.ailc_date,
2066 gcseqa_date = new_references.gcseqa_date,
2067 uk_entry_date = new_references.uk_entry_date,
2068 prev_surname = new_references.prev_surname,
2069 criminal_convictions = new_references.criminal_convictions,
2070 sent_to_hesa = new_references.sent_to_hesa,
2074 last_updated_by = x_last_updated_by,
2071 sent_to_oss = new_references.sent_to_oss,
2072 batch_identifier = new_references.batch_identifier,
2073 last_update_date = x_last_update_date,
2075 last_update_login = x_last_update_login,
2076 gce = new_references.gce,
2077 vce = new_references.vce,
2078 sqa = new_references.sqa,
2079 previousas = new_references.previousas,
2080 keyskills = new_references.keyskills,
2081 vocational = new_references.vocational,
2082 scn = new_references.scn,
2083 prevoeq = new_references.prevoeq ,
2084 choices_transparent_ind = new_references.choices_transparent_ind,
2085 extra_status = new_references.extra_status ,
2086 extra_passport_no = new_references.extra_passport_no ,
2087 request_app_dets_ind = new_references.request_app_dets_ind ,
2088 request_copy_app_frm_ind = new_references.request_copy_app_frm_ind,
2089 cef_no = new_references.cef_no,
2090 system_code = new_references.system_code ,
2091 gcse_eng = new_references.gcse_eng ,
2092 gcse_math = new_references.gcse_math ,
2093 degree_subject = new_references.degree_subject ,
2094 degree_status = new_references.degree_status ,
2095 degree_class = new_references.degree_class ,
2096 gcse_sci = new_references.gcse_sci ,
2097 welshspeaker = new_references.welshspeaker ,
2098 ni_number = new_references.ni_number ,
2099 earliest_start = new_references.earliest_start ,
2100 near_inst = new_references.near_inst ,
2101 pref_reg = new_references.pref_reg ,
2102 qual_eng = new_references.qual_eng ,
2103 qual_math = new_references.qual_math ,
2104 qual_sci = new_references.qual_sci ,
2105 main_qual = new_references.main_qual ,
2106 qual_5 = new_references.qual_5 ,
2107 future_serv = new_references.future_serv ,
2108 future_set = new_references.future_set ,
2109 present_serv = new_references.present_serv ,
2110 present_set = new_references.present_set ,
2111 curr_employment = new_references.curr_employment ,
2112 edu_qualification = new_references.edu_qualification ,
2113 -- smaddali added these columns for ucfd203 - multiple cycles bug#2669208
2114 ad_batch_id = new_references.ad_batch_id ,
2115 ad_interface_id = new_references.ad_interface_id ,
2116 nationality = new_references.nationality ,
2117 dual_nationality = new_references.dual_nationality ,
2118 special_needs = new_references.special_needs ,
2119 country_birth = new_references.country_birth ,
2120 personal_id = new_references.personal_id
2121 WHERE rowid = x_rowid;
2122
2123 IF (SQL%NOTFOUND) THEN
2124 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
2125 igs_ge_msg_stack.add;
2126 igs_sc_gen_001.unset_ctx('R');
2127 app_exception.raise_exception;
2128 END IF;
2129 IF (x_mode = 'S') THEN
2130 igs_sc_gen_001.unset_ctx('R');
2131 END IF;
2132
2133
2134 -- Bug No: 3087784. Resetting the value of l_rowid to NULL.
2135 l_rowid := NULL;
2136
2137 EXCEPTION
2138 WHEN OTHERS THEN
2139 IF (SQLCODE = (-28115)) THEN
2140 fnd_message.set_name ('IGS', 'IGS_SC_UPD_POLICY_EXCP');
2141 fnd_message.set_token ('ERR_CD', SQLCODE);
2142 igs_ge_msg_stack.add;
2143 igs_sc_gen_001.unset_ctx('R');
2144 app_exception.raise_exception;
2145 ELSE
2146 igs_sc_gen_001.unset_ctx('R');
2147 RAISE;
2148 END IF;
2149
2150 END update_row;
2151
2152
2153 PROCEDURE add_row (
2154 x_rowid IN OUT NOCOPY VARCHAR2,
2155 x_app_id IN OUT NOCOPY NUMBER,
2156 x_app_no IN NUMBER,
2157 x_check_digit IN NUMBER,
2158 x_enquiry_no IN OUT NOCOPY NUMBER,
2159 x_oss_person_id IN NUMBER,
2160 x_application_source IN VARCHAR2,
2161 x_name_change_date IN DATE,
2162 x_student_support IN VARCHAR2,
2163 x_address_area IN VARCHAR2,
2164 x_application_date IN DATE,
2165 x_application_sent_date IN DATE,
2166 x_application_sent_run IN NUMBER,
2167 x_lea_code IN NUMBER,
2168 x_fee_payer_code IN NUMBER,
2169 x_fee_text IN VARCHAR2,
2170 x_domicile_apr IN NUMBER,
2171 x_code_changed_date IN DATE,
2172 x_school IN NUMBER,
2173 x_withdrawn IN VARCHAR2,
2174 x_withdrawn_date IN DATE,
2178 x_a_levels IN NUMBER,
2175 x_rel_to_clear_reason IN VARCHAR2,
2176 x_route_b IN VARCHAR2,
2177 x_exam_change_date IN DATE,
2179 x_as_levels IN NUMBER,
2180 x_highers IN NUMBER,
2181 x_csys IN NUMBER,
2182 x_winter IN NUMBER,
2183 x_previous IN NUMBER,
2184 x_gnvq IN VARCHAR2,
2185 x_btec IN VARCHAR2,
2186 x_ilc IN VARCHAR2,
2187 x_ailc IN VARCHAR2,
2188 x_ib IN VARCHAR2,
2189 x_manual IN VARCHAR2,
2190 x_reg_num IN VARCHAR2,
2191 x_oeq IN VARCHAR2,
2192 x_eas IN VARCHAR2,
2193 x_roa IN VARCHAR2,
2194 x_status IN VARCHAR2,
2195 x_firm_now IN NUMBER,
2196 x_firm_reply IN NUMBER,
2197 x_insurance_reply IN NUMBER,
2198 x_conf_hist_firm_reply IN NUMBER,
2199 x_conf_hist_ins_reply IN NUMBER,
2200 x_residential_category IN VARCHAR2,
2201 x_personal_statement IN LONG,
2202 x_match_prev IN VARCHAR2,
2203 x_match_prev_date IN DATE,
2204 x_match_winter IN VARCHAR2,
2205 x_match_summer IN VARCHAR2,
2206 x_gnvq_date IN DATE,
2207 x_ib_date IN DATE,
2208 x_ilc_date IN DATE,
2209 x_ailc_date IN DATE,
2210 x_gcseqa_date IN DATE,
2211 x_uk_entry_date IN DATE,
2212 x_prev_surname IN VARCHAR2,
2213 x_criminal_convictions IN VARCHAR2,
2214 x_sent_to_hesa IN VARCHAR2,
2215 x_sent_to_oss IN VARCHAR2,
2216 x_batch_identifier IN NUMBER,
2217 x_mode IN VARCHAR2 ,
2218 -- Added following 8 Columns as part of UCCR002 Build. Bug NO: 2278817 by rbezawad
2219 x_gce IN NUMBER ,
2220 x_vce IN NUMBER ,
2221 x_sqa IN VARCHAR2 ,
2222 x_previousas IN NUMBER ,
2223 x_keyskills IN VARCHAR2 ,
2224 x_vocational IN VARCHAR2 ,
2225 x_scn IN VARCHAR2 ,
2226 x_prevoeq IN VARCHAR2 ,
2227
2228 x_choices_transparent_ind IN VARCHAR2,
2229 x_extra_status IN NUMBER,
2230 x_extra_passport_no IN VARCHAR2,
2231 x_request_app_dets_ind IN VARCHAR2,
2232 x_request_copy_app_frm_ind IN VARCHAR2,
2233 x_cef_no IN NUMBER,
2234 -- Added the following columns as part of UCFD102 Build: Bug#2643048
2235 x_system_code IN VARCHAR2 ,
2236 x_gcse_eng IN VARCHAR2 ,
2237 x_gcse_math IN VARCHAR2 ,
2238 x_degree_subject IN VARCHAR2 ,
2239 x_degree_status IN VARCHAR2 ,
2240 x_degree_class IN VARCHAR2 ,
2241 x_gcse_sci IN VARCHAR2 ,
2242 x_welshspeaker IN VARCHAR2 ,
2243 x_ni_number IN VARCHAR2,
2244 x_earliest_start IN VARCHAR2,
2245 x_near_inst IN VARCHAR2,
2246 x_pref_reg IN NUMBER ,
2247 x_qual_eng IN VARCHAR2,
2248 x_qual_math IN VARCHAR2,
2249 x_qual_sci IN VARCHAR2,
2250 x_main_qual IN VARCHAR2,
2251 x_qual_5 IN VARCHAR2,
2252 x_future_serv IN VARCHAR2,
2253 x_future_set IN VARCHAR2,
2254 x_present_serv IN VARCHAR2,
2255 x_present_set IN VARCHAR2,
2256 x_curr_employment IN VARCHAR2,
2257 x_edu_qualification IN VARCHAR2,
2258 -- smaddali added these columns for ucfd203 - multiple cycles bug#2669208
2259 x_ad_batch_id IN NUMBER,
2260 x_ad_interface_id IN NUMBER,
2261 x_nationality IN NUMBER,
2262 x_dual_nationality IN NUMBER,
2263 x_special_needs IN VARCHAR2,
2264 x_country_birth IN NUMBER,
2265 x_personal_id IN VARCHAR2
2266 ) AS
2267 /*
2268 || Created By : [email protected]
2269 || Created On : 21-FEB-2002
2273 || Who When What
2270 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
2271 || Known limitations, enhancements or remarks :
2272 || Change History :
2274 || smaddali added columns for ucfd203 - multiple cycles bug#2669208
2275 || (reverse chronological order - newest change first)
2276 */
2277 CURSOR c1 IS
2278 SELECT rowid
2279 FROM igs_uc_applicants
2280 WHERE app_id = x_app_id;
2281
2282 BEGIN
2283
2284 OPEN c1;
2285 FETCH c1 INTO x_rowid;
2286 IF (c1%NOTFOUND) THEN
2287 CLOSE c1;
2288
2289 insert_row (
2290 x_rowid,
2291 x_app_id,
2292 x_app_no,
2293 x_check_digit,
2294 x_enquiry_no,
2295 x_oss_person_id,
2296 x_application_source,
2297 x_name_change_date,
2298 x_student_support,
2299 x_address_area,
2300 x_application_date,
2301 x_application_sent_date,
2302 x_application_sent_run,
2303 x_lea_code,
2304 x_fee_payer_code,
2305 x_fee_text,
2306 x_domicile_apr,
2307 x_code_changed_date,
2308 x_school,
2309 x_withdrawn,
2310 x_withdrawn_date,
2311 x_rel_to_clear_reason,
2312 x_route_b,
2313 x_exam_change_date,
2314 x_a_levels,
2315 x_as_levels,
2316 x_highers,
2317 x_csys,
2318 x_winter,
2319 x_previous,
2320 x_gnvq,
2321 x_btec,
2322 x_ilc,
2323 x_ailc,
2324 x_ib,
2325 x_manual,
2326 x_reg_num,
2327 x_oeq,
2328 x_eas,
2329 x_roa,
2330 x_status,
2331 x_firm_now,
2332 x_firm_reply,
2333 x_insurance_reply,
2334 x_conf_hist_firm_reply,
2335 x_conf_hist_ins_reply,
2336 x_residential_category,
2337 x_personal_statement,
2338 x_match_prev,
2339 x_match_prev_date,
2340 x_match_winter,
2341 x_match_summer,
2342 x_gnvq_date,
2343 x_ib_date,
2344 x_ilc_date,
2345 x_ailc_date,
2346 x_gcseqa_date,
2347 x_uk_entry_date,
2348 x_prev_surname,
2349 x_criminal_convictions,
2350 x_sent_to_hesa,
2351 x_sent_to_oss,
2352 x_batch_identifier,
2353 x_mode,
2354 x_gce,
2355 x_vce,
2356 x_sqa,
2357 x_previousas,
2358 x_keyskills,
2359 x_vocational,
2360 x_scn,
2361 x_prevoeq,
2362 x_choices_transparent_ind,
2363 x_extra_status,
2364 x_extra_passport_no,
2365 x_request_app_dets_ind,
2366 x_request_copy_app_frm_ind,
2367 x_cef_no,
2368 x_system_code ,
2369 x_gcse_eng ,
2370 x_gcse_math ,
2371 x_degree_subject ,
2372 x_degree_status ,
2373 x_degree_class ,
2374 x_gcse_sci ,
2375 x_welshspeaker ,
2376 x_ni_number ,
2377 x_earliest_start ,
2378 x_near_inst ,
2379 x_pref_reg ,
2380 x_qual_eng ,
2381 x_qual_math ,
2382 x_qual_sci ,
2383 x_main_qual ,
2384 x_qual_5 ,
2385 x_future_serv ,
2386 x_future_set ,
2387 x_present_serv ,
2388 x_present_set ,
2389 x_curr_employment ,
2390 x_edu_qualification ,
2391 -- smaddali added these columns for ucfd203 - multiple cycles bug#2669208
2392 x_ad_batch_id ,
2393 x_ad_interface_id ,
2394 x_nationality ,
2395 x_dual_nationality ,
2396 x_special_needs ,
2397 x_country_birth ,
2398 x_personal_id
2399 );
2400 RETURN;
2401 END IF;
2402 CLOSE c1;
2403
2404 update_row (
2405 x_rowid,
2406 x_app_id,
2407 x_app_no,
2408 x_check_digit,
2409 x_enquiry_no,
2410 x_oss_person_id,
2411 x_application_source,
2412 x_name_change_date,
2413 x_student_support,
2414 x_address_area,
2415 x_application_date,
2416 x_application_sent_date,
2417 x_application_sent_run,
2418 x_lea_code,
2419 x_fee_payer_code,
2420 x_fee_text,
2421 x_domicile_apr,
2422 x_code_changed_date,
2423 x_school,
2424 x_withdrawn,
2425 x_withdrawn_date,
2426 x_rel_to_clear_reason,
2427 x_route_b,
2428 x_exam_change_date,
2429 x_a_levels,
2430 x_as_levels,
2431 x_highers,
2432 x_csys,
2433 x_winter,
2434 x_previous,
2435 x_gnvq,
2436 x_btec,
2437 x_ilc,
2438 x_ailc,
2439 x_ib,
2440 x_manual,
2441 x_reg_num,
2442 x_oeq,
2446 x_firm_now,
2443 x_eas,
2444 x_roa,
2445 x_status,
2447 x_firm_reply,
2448 x_insurance_reply,
2449 x_conf_hist_firm_reply,
2450 x_conf_hist_ins_reply,
2451 x_residential_category,
2452 x_personal_statement,
2453 x_match_prev,
2454 x_match_prev_date,
2455 x_match_winter,
2456 x_match_summer,
2457 x_gnvq_date,
2458 x_ib_date,
2459 x_ilc_date,
2460 x_ailc_date,
2461 x_gcseqa_date,
2462 x_uk_entry_date,
2463 x_prev_surname,
2464 x_criminal_convictions,
2465 x_sent_to_hesa,
2466 x_sent_to_oss,
2467 x_batch_identifier,
2468 x_mode,
2469 x_gce,
2470 x_vce,
2471 x_sqa,
2472 x_previousas,
2473 x_keyskills,
2474 x_vocational,
2475 x_scn,
2476 x_prevoeq ,
2477 x_choices_transparent_ind,
2478 x_extra_status,
2479 x_extra_passport_no,
2480 x_request_app_dets_ind,
2481 x_request_copy_app_frm_ind,
2482 x_cef_no,
2483 x_system_code ,
2484 x_gcse_eng ,
2485 x_gcse_math ,
2486 x_degree_subject ,
2487 x_degree_status ,
2488 x_degree_class ,
2489 x_gcse_sci ,
2490 x_welshspeaker ,
2491 x_ni_number ,
2492 x_earliest_start ,
2493 x_near_inst ,
2494 x_pref_reg ,
2495 x_qual_eng ,
2496 x_qual_math ,
2497 x_qual_sci ,
2498 x_main_qual ,
2499 x_qual_5 ,
2500 x_future_serv ,
2501 x_future_set ,
2502 x_present_serv ,
2503 x_present_set ,
2504 x_curr_employment ,
2505 x_edu_qualification ,
2506 -- smaddali added these columns for ucfd203 - multiple cycles bug#2669208
2507 x_ad_batch_id ,
2508 x_ad_interface_id ,
2509 x_nationality ,
2510 x_dual_nationality ,
2511 x_special_needs ,
2512 x_country_birth ,
2513 x_personal_id
2514 );
2515
2516 END add_row;
2517
2518
2519 PROCEDURE delete_row (
2520 x_rowid IN VARCHAR2,
2521 x_mode IN VARCHAR2
2522 ) AS
2523 /*
2524 || Created By : [email protected]
2525 || Created On : 21-FEB-2002
2526 || Purpose : Handles the DELETE DML logic for the table.
2527 || Known limitations, enhancements or remarks :
2528 || Change History :
2529 || Who When What
2530 || (reverse chronological order - newest change first)
2531 || dsridhar 27-AUG-2003 Bug No: 3087784. Resetting the value of l_rowid to NULL.
2532 */
2533 BEGIN
2534
2535 before_dml (
2536 p_action => 'DELETE',
2537 x_rowid => x_rowid
2538 );
2539
2540 IF (x_mode = 'S') THEN
2541 igs_sc_gen_001.set_ctx('R');
2542 END IF;
2543 DELETE FROM igs_uc_applicants
2544 WHERE rowid = x_rowid;
2545
2546 IF (SQL%NOTFOUND) THEN
2547 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
2548 igs_ge_msg_stack.add;
2549 igs_sc_gen_001.unset_ctx('R');
2550 app_exception.raise_exception;
2551 END IF;
2552 IF (x_mode = 'S') THEN
2553 igs_sc_gen_001.unset_ctx('R');
2554 END IF;
2555
2556
2557 -- Bug No: 3087784. Resetting the value of l_rowid to NULL.
2558 l_rowid := NULL;
2559
2560 END delete_row;
2561
2562 END igs_uc_applicants_pkg;