[Home] [Help]
PACKAGE BODY: APPS.IGS_HE_STDNT_DLHE_PKG
Source
1 PACKAGE BODY igs_he_stdnt_dlhe_pkg AS
2 /* $Header: IGSWI38B.pls 120.1 2005/08/31 22:52:29 appldev ship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igs_he_stdnt_dlhe%ROWTYPE;
6 new_references igs_he_stdnt_dlhe%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2,
11 x_person_id IN NUMBER,
12 x_submission_name IN VARCHAR2,
13 x_user_return_subclass IN VARCHAR2,
14 x_return_name IN VARCHAR2,
15 x_qual_period_code IN VARCHAR2,
16 x_dlhe_record_status IN VARCHAR2,
17 x_participant_source IN VARCHAR2,
18 x_date_status_changed IN DATE,
19 x_validation_status IN VARCHAR2,
20 x_admin_coding IN VARCHAR2,
21 x_survey_method IN VARCHAR2,
22 x_employment IN VARCHAR2,
23 x_further_study IN VARCHAR2,
24 x_qualified_teacher IN VARCHAR2,
25 x_pt_study IN VARCHAR2,
26 x_employer_business IN VARCHAR2,
27 x_employer_name IN VARCHAR2,
28 x_employer_classification IN VARCHAR2,
29 x_employer_location IN VARCHAR2,
30 x_employer_postcode IN VARCHAR2,
31 x_employer_country IN VARCHAR2,
32 x_job_title IN VARCHAR2,
33 x_job_duties IN VARCHAR2,
34 x_job_classification IN VARCHAR2,
35 x_employer_size IN VARCHAR2,
36 x_job_duration IN VARCHAR2,
37 x_job_salary IN NUMBER,
38 x_salary_refused IN VARCHAR2,
39 x_qualification_requirement IN VARCHAR2,
40 x_qualification_importance IN VARCHAR2,
41 x_job_reason1 IN VARCHAR2,
42 x_job_reason2 IN VARCHAR2,
43 x_job_reason3 IN VARCHAR2,
44 x_job_reason4 IN VARCHAR2,
45 x_job_reason5 IN VARCHAR2,
46 x_job_reason6 IN VARCHAR2,
47 x_job_reason7 IN VARCHAR2,
48 x_job_reason8 IN VARCHAR2,
49 x_other_job_reason IN VARCHAR2,
50 x_no_other_job_reason IN VARCHAR2,
51 x_job_source IN VARCHAR2,
52 x_other_job_source IN VARCHAR2,
53 x_no_other_job_source IN VARCHAR2,
54 x_previous_job IN VARCHAR2,
55 x_previous_jobtype1 IN VARCHAR2,
56 x_previous_jobtype2 IN VARCHAR2,
57 x_previous_jobtype3 IN VARCHAR2,
58 x_previous_jobtype4 IN VARCHAR2,
59 x_previous_jobtype5 IN VARCHAR2,
60 x_previous_jobtype6 IN VARCHAR2,
61 x_further_study_type IN VARCHAR2,
62 x_course_name IN VARCHAR2,
63 x_course_training_subject IN VARCHAR2,
64 x_research_subject IN VARCHAR2,
65 x_research_training_subject IN VARCHAR2,
66 x_further_study_provider IN VARCHAR2,
67 x_further_study_qualaim IN VARCHAR2,
68 x_professional_qualification IN VARCHAR2,
69 x_study_reason1 IN VARCHAR2,
70 x_study_reason2 IN VARCHAR2,
71 x_study_reason3 IN VARCHAR2,
72 x_study_reason4 IN VARCHAR2,
73 x_study_reason5 IN VARCHAR2,
74 x_study_reason6 IN VARCHAR2,
75 x_study_reason7 IN VARCHAR2,
76 x_other_study_reason IN VARCHAR2,
77 x_no_other_study_reason IN VARCHAR2,
78 x_employer_sponsored IN VARCHAR2,
79 x_funding_source IN VARCHAR2,
80 x_teacher_teaching IN VARCHAR2,
81 x_teacher_seeking IN VARCHAR2,
82 x_teaching_sector IN VARCHAR2,
83 x_teaching_level IN VARCHAR2,
84 x_reason_for_ptcourse IN VARCHAR2,
85 x_job_while_studying IN VARCHAR2,
86 x_employer_support1 IN VARCHAR2,
87 x_employer_support2 IN VARCHAR2,
88 x_employer_support3 IN VARCHAR2,
89 x_employer_support4 IN VARCHAR2,
90 x_employer_support5 IN VARCHAR2,
91 x_popdlhe_flag IN VARCHAR2,
92 x_creation_date IN DATE,
93 x_created_by IN NUMBER,
94 x_last_update_date IN DATE,
95 x_last_updated_by IN NUMBER,
96 x_last_update_login IN NUMBER
97 ) AS
98 /*
99 || Created By : [email protected]
100 || Created On : 17-APR-2003
101 || Purpose : Initialises the Old and New references for the columns of the table.
102 || Known limitations, enhancements or remarks :
103 || Change History :
104 || Who When What
105 || (reverse chronological order - newest change first)
106 */
107
108 CURSOR cur_old_ref_values IS
109 SELECT *
110 FROM igs_he_stdnt_dlhe
111 WHERE rowid = x_rowid;
112
113 BEGIN
114
115 l_rowid := x_rowid;
116
117 -- Code for setting the Old and New Reference Values.
118 -- Populate Old Values.
119 OPEN cur_old_ref_values;
120 FETCH cur_old_ref_values INTO old_references;
121 IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
122 CLOSE cur_old_ref_values;
123 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
124 igs_ge_msg_stack.add;
125 app_exception.raise_exception;
126 RETURN;
127 END IF;
128 CLOSE cur_old_ref_values;
129
130 -- Populate New Values.
131 new_references.person_id := x_person_id;
132 new_references.submission_name := x_submission_name;
133 new_references.user_return_subclass := x_user_return_subclass;
134 new_references.return_name := x_return_name;
135 new_references.qual_period_code := x_qual_period_code;
136 new_references.dlhe_record_status := x_dlhe_record_status;
137 new_references.participant_source := x_participant_source;
138 new_references.date_status_changed := x_date_status_changed;
139 new_references.validation_status := x_validation_status;
140 new_references.admin_coding := x_admin_coding;
141 new_references.survey_method := x_survey_method;
142 new_references.employment := x_employment;
143 new_references.further_study := x_further_study;
144 new_references.qualified_teacher := x_qualified_teacher;
145 new_references.pt_study := x_pt_study;
146 new_references.employer_business := x_employer_business;
147 new_references.employer_name := x_employer_name;
148 new_references.employer_classification := x_employer_classification;
149 new_references.employer_location := x_employer_location;
150 new_references.employer_postcode := x_employer_postcode;
151 new_references.employer_country := x_employer_country;
152 new_references.job_title := x_job_title;
153 new_references.job_duties := x_job_duties;
154 new_references.job_classification := x_job_classification;
155 new_references.employer_size := x_employer_size;
156 new_references.job_duration := x_job_duration;
157 new_references.job_salary := x_job_salary;
158 new_references.salary_refused := x_salary_refused;
159 new_references.qualification_requirement := x_qualification_requirement;
160 new_references.qualification_importance := x_qualification_importance;
161 new_references.job_reason1 := x_job_reason1;
162 new_references.job_reason2 := x_job_reason2;
163 new_references.job_reason3 := x_job_reason3;
164 new_references.job_reason4 := x_job_reason4;
165 new_references.job_reason5 := x_job_reason5;
166 new_references.job_reason6 := x_job_reason6;
167 new_references.job_reason7 := x_job_reason7;
168 new_references.job_reason8 := x_job_reason8;
169 new_references.other_job_reason := x_other_job_reason;
170 new_references.no_other_job_reason := x_no_other_job_reason;
171 new_references.job_source := x_job_source;
172 new_references.other_job_source := x_other_job_source;
173 new_references.no_other_job_source := x_no_other_job_source;
174 new_references.previous_job := x_previous_job;
175 new_references.previous_jobtype1 := x_previous_jobtype1;
176 new_references.previous_jobtype2 := x_previous_jobtype2;
177 new_references.previous_jobtype3 := x_previous_jobtype3;
178 new_references.previous_jobtype4 := x_previous_jobtype4;
179 new_references.previous_jobtype5 := x_previous_jobtype5;
180 new_references.previous_jobtype6 := x_previous_jobtype6;
181 new_references.further_study_type := x_further_study_type;
182 new_references.course_name := x_course_name;
183 new_references.course_training_subject := x_course_training_subject;
184 new_references.research_subject := x_research_subject;
185 new_references.research_training_subject := x_research_training_subject;
186 new_references.further_study_provider := x_further_study_provider;
187 new_references.further_study_qualaim := x_further_study_qualaim;
188 new_references.professional_qualification := x_professional_qualification;
189 new_references.study_reason1 := x_study_reason1;
190 new_references.study_reason2 := x_study_reason2;
191 new_references.study_reason3 := x_study_reason3;
192 new_references.study_reason4 := x_study_reason4;
193 new_references.study_reason5 := x_study_reason5;
194 new_references.study_reason6 := x_study_reason6;
195 new_references.study_reason7 := x_study_reason7;
196 new_references.other_study_reason := x_other_study_reason;
197 new_references.no_other_study_reason := x_no_other_study_reason;
198 new_references.employer_sponsored := x_employer_sponsored;
199 new_references.funding_source := x_funding_source;
200 new_references.teacher_teaching := x_teacher_teaching;
201 new_references.teacher_seeking := x_teacher_seeking;
202 new_references.teaching_sector := x_teaching_sector;
203 new_references.teaching_level := x_teaching_level;
204 new_references.reason_for_ptcourse := x_reason_for_ptcourse;
205 new_references.job_while_studying := x_job_while_studying;
206 new_references.employer_support1 := x_employer_support1;
207 new_references.employer_support2 := x_employer_support2;
208 new_references.employer_support3 := x_employer_support3;
209 new_references.employer_support4 := x_employer_support4;
210 new_references.employer_support5 := x_employer_support5;
211 new_references.popdlhe_flag := x_popdlhe_flag;
212
213 IF (p_action = 'UPDATE') THEN
214 new_references.creation_date := old_references.creation_date;
215 new_references.created_by := old_references.created_by;
216 ELSE
217 new_references.creation_date := x_creation_date;
218 new_references.created_by := x_created_by;
219 END IF;
220
221 new_references.last_update_date := x_last_update_date;
222 new_references.last_updated_by := x_last_updated_by;
223 new_references.last_update_login := x_last_update_login;
224
225 END set_column_values;
226
227
228 PROCEDURE check_parent_existance AS
229 /*
230 || Created By : [email protected]
231 || Created On : 17-APR-2003
232 || Purpose : Checks for the existance of Parent records.
233 || Known limitations, enhancements or remarks :
234 || Change History :
235 || Who When What
236 || (reverse chronological order - newest change first)
237 */
238 BEGIN
239
240 IF (((old_references.submission_name = new_references.submission_name) AND
241 (old_references.user_return_subclass = new_references.user_return_subclass) AND
242 (old_references.return_name = new_references.return_name) AND
243 (old_references.qual_period_code = new_references.qual_period_code)) OR
244 ((new_references.submission_name IS NULL) OR
245 (new_references.user_return_subclass IS NULL) OR
246 (new_references.return_name IS NULL) OR
247 (new_references.qual_period_code IS NULL))) THEN
248 NULL;
249 ELSIF NOT igs_he_sub_rtn_qual_pkg.get_pk_for_validation (
250 new_references.submission_name,
251 new_references.user_return_subclass,
252 new_references.return_name,
253 new_references.qual_period_code
254 ) THEN
255 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
256 igs_ge_msg_stack.add;
257 app_exception.raise_exception;
258 END IF;
259
260 END check_parent_existance;
261
262
263 FUNCTION get_pk_for_validation (
264 x_submission_name IN VARCHAR2,
265 x_return_name IN VARCHAR2,
266 x_person_id IN NUMBER
267 ) RETURN BOOLEAN AS
268 /*
269 || Created By : [email protected]
270 || Created On : 17-APR-2003
271 || Purpose : Validates the Primary Key of the table.
272 || Known limitations, enhancements or remarks :
273 || Change History :
274 || Who When What
275 || (reverse chronological order - newest change first)
276 */
277 CURSOR cur_rowid IS
278 SELECT rowid
279 FROM igs_he_stdnt_dlhe
280 WHERE submission_name = x_submission_name
281 AND return_name = x_return_name
282 AND person_id = x_person_id
283 FOR UPDATE NOWAIT;
284
285 lv_rowid cur_rowid%ROWTYPE;
286
287 BEGIN
288
289 OPEN cur_rowid;
290 FETCH cur_rowid INTO lv_rowid;
291 IF (cur_rowid%FOUND) THEN
292 CLOSE cur_rowid;
293 RETURN(TRUE);
294 ELSE
295 CLOSE cur_rowid;
296 RETURN(FALSE);
297 END IF;
298
299 END get_pk_for_validation;
300
301
302 PROCEDURE get_fk_igs_he_sub_rtn_qual (
303 x_submission_name IN VARCHAR2,
304 x_user_return_subclass IN VARCHAR2,
305 x_return_name IN VARCHAR2,
306 x_qual_period_code IN VARCHAR2
307 ) AS
308 /*
309 || Created By : [email protected]
310 || Created On : 17-APR-2003
311 || Purpose : Validates the Foreign Keys for the table.
312 || Known limitations, enhancements or remarks :
313 || Change History :
314 || Who When What
315 || (reverse chronological order - newest change first)
316 */
317 CURSOR cur_rowid IS
318 SELECT rowid
319 FROM igs_he_stdnt_dlhe
320 WHERE ((qual_period_code = x_qual_period_code) AND
321 (return_name = x_return_name) AND
322 (submission_name = x_submission_name) AND
323 (user_return_subclass = x_user_return_subclass));
324
325 lv_rowid cur_rowid%ROWTYPE;
326
327 BEGIN
328
329 OPEN cur_rowid;
330 FETCH cur_rowid INTO lv_rowid;
331 IF (cur_rowid%FOUND) THEN
332 CLOSE cur_rowid;
333 fnd_message.set_name ('IGS', 'IGS_HE_HDLHE_HSRQ_FK');
334 igs_ge_msg_stack.add;
335 app_exception.raise_exception;
336 RETURN;
337 END IF;
338 CLOSE cur_rowid;
339
340 END get_fk_igs_he_sub_rtn_qual;
341
342
343 PROCEDURE before_dml (
344 p_action IN VARCHAR2,
345 x_rowid IN VARCHAR2,
346 x_person_id IN NUMBER,
347 x_submission_name IN VARCHAR2,
348 x_user_return_subclass IN VARCHAR2,
349 x_return_name IN VARCHAR2,
350 x_qual_period_code IN VARCHAR2,
351 x_dlhe_record_status IN VARCHAR2,
352 x_participant_source IN VARCHAR2,
353 x_date_status_changed IN DATE,
354 x_validation_status IN VARCHAR2,
355 x_admin_coding IN VARCHAR2,
356 x_survey_method IN VARCHAR2,
357 x_employment IN VARCHAR2,
358 x_further_study IN VARCHAR2,
359 x_qualified_teacher IN VARCHAR2,
360 x_pt_study IN VARCHAR2,
361 x_employer_business IN VARCHAR2,
362 x_employer_name IN VARCHAR2,
363 x_employer_classification IN VARCHAR2,
364 x_employer_location IN VARCHAR2,
365 x_employer_postcode IN VARCHAR2,
366 x_employer_country IN VARCHAR2,
367 x_job_title IN VARCHAR2,
368 x_job_duties IN VARCHAR2,
369 x_job_classification IN VARCHAR2,
370 x_employer_size IN VARCHAR2,
371 x_job_duration IN VARCHAR2,
372 x_job_salary IN NUMBER,
373 x_salary_refused IN VARCHAR2,
374 x_qualification_requirement IN VARCHAR2,
375 x_qualification_importance IN VARCHAR2,
376 x_job_reason1 IN VARCHAR2,
377 x_job_reason2 IN VARCHAR2,
378 x_job_reason3 IN VARCHAR2,
379 x_job_reason4 IN VARCHAR2,
380 x_job_reason5 IN VARCHAR2,
381 x_job_reason6 IN VARCHAR2,
382 x_job_reason7 IN VARCHAR2,
383 x_job_reason8 IN VARCHAR2,
384 x_other_job_reason IN VARCHAR2,
385 x_no_other_job_reason IN VARCHAR2,
386 x_job_source IN VARCHAR2,
387 x_other_job_source IN VARCHAR2,
388 x_no_other_job_source IN VARCHAR2,
389 x_previous_job IN VARCHAR2,
390 x_previous_jobtype1 IN VARCHAR2,
391 x_previous_jobtype2 IN VARCHAR2,
392 x_previous_jobtype3 IN VARCHAR2,
393 x_previous_jobtype4 IN VARCHAR2,
394 x_previous_jobtype5 IN VARCHAR2,
395 x_previous_jobtype6 IN VARCHAR2,
396 x_further_study_type IN VARCHAR2,
397 x_course_name IN VARCHAR2,
398 x_course_training_subject IN VARCHAR2,
399 x_research_subject IN VARCHAR2,
400 x_research_training_subject IN VARCHAR2,
401 x_further_study_provider IN VARCHAR2,
402 x_further_study_qualaim IN VARCHAR2,
403 x_professional_qualification IN VARCHAR2,
404 x_study_reason1 IN VARCHAR2,
405 x_study_reason2 IN VARCHAR2,
406 x_study_reason3 IN VARCHAR2,
407 x_study_reason4 IN VARCHAR2,
408 x_study_reason5 IN VARCHAR2,
409 x_study_reason6 IN VARCHAR2,
410 x_study_reason7 IN VARCHAR2,
411 x_other_study_reason IN VARCHAR2,
412 x_no_other_study_reason IN VARCHAR2,
413 x_employer_sponsored IN VARCHAR2,
414 x_funding_source IN VARCHAR2,
415 x_teacher_teaching IN VARCHAR2,
416 x_teacher_seeking IN VARCHAR2,
417 x_teaching_sector IN VARCHAR2,
418 x_teaching_level IN VARCHAR2,
419 x_reason_for_ptcourse IN VARCHAR2,
420 x_job_while_studying IN VARCHAR2,
421 x_employer_support1 IN VARCHAR2,
422 x_employer_support2 IN VARCHAR2,
423 x_employer_support3 IN VARCHAR2,
424 x_employer_support4 IN VARCHAR2,
425 x_employer_support5 IN VARCHAR2,
426 x_popdlhe_flag IN VARCHAR2,
427 x_creation_date IN DATE,
428 x_created_by IN NUMBER,
429 x_last_update_date IN DATE,
430 x_last_updated_by IN NUMBER,
431 x_last_update_login IN NUMBER
432 ) AS
433 /*
434 || Created By : [email protected]
435 || Created On : 17-APR-2003
436 || Purpose : Initialises the columns, Checks Constraints, Calls the
437 || Trigger Handlers for the table, before any DML operation.
438 || Known limitations, enhancements or remarks :
439 || Change History :
440 || Who When What
441 || (reverse chronological order - newest change first)
442 */
443 BEGIN
444
445 set_column_values (
446 p_action,
447 x_rowid,
448 x_person_id,
449 x_submission_name,
450 x_user_return_subclass,
451 x_return_name,
452 x_qual_period_code,
453 x_dlhe_record_status,
454 x_participant_source,
455 x_date_status_changed,
456 x_validation_status,
457 x_admin_coding,
458 x_survey_method,
459 x_employment,
460 x_further_study,
461 x_qualified_teacher,
462 x_pt_study,
463 x_employer_business,
464 x_employer_name,
465 x_employer_classification,
466 x_employer_location,
467 x_employer_postcode,
468 x_employer_country,
469 x_job_title,
470 x_job_duties,
471 x_job_classification,
472 x_employer_size,
473 x_job_duration,
474 x_job_salary,
475 x_salary_refused,
476 x_qualification_requirement,
477 x_qualification_importance,
478 x_job_reason1,
479 x_job_reason2,
480 x_job_reason3,
481 x_job_reason4,
482 x_job_reason5,
483 x_job_reason6,
484 x_job_reason7,
485 x_job_reason8,
486 x_other_job_reason,
487 x_no_other_job_reason,
488 x_job_source,
489 x_other_job_source,
490 x_no_other_job_source,
491 x_previous_job,
492 x_previous_jobtype1,
493 x_previous_jobtype2,
494 x_previous_jobtype3,
495 x_previous_jobtype4,
496 x_previous_jobtype5,
497 x_previous_jobtype6,
498 x_further_study_type,
499 x_course_name,
500 x_course_training_subject,
501 x_research_subject,
502 x_research_training_subject,
503 x_further_study_provider,
504 x_further_study_qualaim,
505 x_professional_qualification,
506 x_study_reason1,
507 x_study_reason2,
508 x_study_reason3,
509 x_study_reason4,
510 x_study_reason5,
511 x_study_reason6,
512 x_study_reason7,
513 x_other_study_reason,
514 x_no_other_study_reason,
515 x_employer_sponsored,
516 x_funding_source,
517 x_teacher_teaching,
518 x_teacher_seeking,
519 x_teaching_sector,
520 x_teaching_level,
521 x_reason_for_ptcourse,
522 x_job_while_studying,
523 x_employer_support1,
524 x_employer_support2,
525 x_employer_support3,
526 x_employer_support4,
527 x_employer_support5,
528 x_popdlhe_flag,
529 x_creation_date,
530 x_created_by,
531 x_last_update_date,
532 x_last_updated_by,
533 x_last_update_login
534 );
535
536 IF (p_action = 'INSERT') THEN
537 -- Call all the procedures related to Before Insert.
538 IF ( get_pk_for_validation(
539 new_references.submission_name,
540 new_references.return_name,
541 new_references.person_id
542 )
543 ) THEN
544 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
545 igs_ge_msg_stack.add;
546 app_exception.raise_exception;
547 END IF;
548 check_parent_existance;
549 ELSIF (p_action = 'UPDATE') THEN
550 -- Call all the procedures related to Before Update.
551 check_parent_existance;
552 ELSIF (p_action = 'VALIDATE_INSERT') THEN
553 -- Call all the procedures related to Before Insert.
554 IF ( get_pk_for_validation (
555 new_references.submission_name,
556 new_references.return_name,
557 new_references.person_id
558 )
559 ) THEN
560 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
561 igs_ge_msg_stack.add;
562 app_exception.raise_exception;
563 END IF;
564 END IF;
565
566 END before_dml;
567
568
569 PROCEDURE insert_row (
570 x_rowid IN OUT NOCOPY VARCHAR2,
571 x_person_id IN NUMBER,
572 x_submission_name IN VARCHAR2,
573 x_user_return_subclass IN VARCHAR2,
574 x_return_name IN VARCHAR2,
575 x_qual_period_code IN VARCHAR2,
576 x_dlhe_record_status IN VARCHAR2,
577 x_participant_source IN VARCHAR2,
578 x_date_status_changed IN DATE,
579 x_validation_status IN VARCHAR2,
580 x_admin_coding IN VARCHAR2,
581 x_survey_method IN VARCHAR2,
582 x_employment IN VARCHAR2,
583 x_further_study IN VARCHAR2,
584 x_qualified_teacher IN VARCHAR2,
585 x_pt_study IN VARCHAR2,
586 x_employer_business IN VARCHAR2,
587 x_employer_name IN VARCHAR2,
588 x_employer_classification IN VARCHAR2,
589 x_employer_location IN VARCHAR2,
590 x_employer_postcode IN VARCHAR2,
591 x_employer_country IN VARCHAR2,
592 x_job_title IN VARCHAR2,
593 x_job_duties IN VARCHAR2,
594 x_job_classification IN VARCHAR2,
595 x_employer_size IN VARCHAR2,
596 x_job_duration IN VARCHAR2,
597 x_job_salary IN NUMBER,
598 x_salary_refused IN VARCHAR2,
599 x_qualification_requirement IN VARCHAR2,
600 x_qualification_importance IN VARCHAR2,
601 x_job_reason1 IN VARCHAR2,
602 x_job_reason2 IN VARCHAR2,
603 x_job_reason3 IN VARCHAR2,
604 x_job_reason4 IN VARCHAR2,
605 x_job_reason5 IN VARCHAR2,
606 x_job_reason6 IN VARCHAR2,
607 x_job_reason7 IN VARCHAR2,
608 x_job_reason8 IN VARCHAR2,
609 x_other_job_reason IN VARCHAR2,
610 x_no_other_job_reason IN VARCHAR2,
611 x_job_source IN VARCHAR2,
612 x_other_job_source IN VARCHAR2,
613 x_no_other_job_source IN VARCHAR2,
614 x_previous_job IN VARCHAR2,
615 x_previous_jobtype1 IN VARCHAR2,
616 x_previous_jobtype2 IN VARCHAR2,
617 x_previous_jobtype3 IN VARCHAR2,
618 x_previous_jobtype4 IN VARCHAR2,
619 x_previous_jobtype5 IN VARCHAR2,
620 x_previous_jobtype6 IN VARCHAR2,
621 x_further_study_type IN VARCHAR2,
622 x_course_name IN VARCHAR2,
623 x_course_training_subject IN VARCHAR2,
624 x_research_subject IN VARCHAR2,
625 x_research_training_subject IN VARCHAR2,
626 x_further_study_provider IN VARCHAR2,
627 x_further_study_qualaim IN VARCHAR2,
628 x_professional_qualification IN VARCHAR2,
629 x_study_reason1 IN VARCHAR2,
630 x_study_reason2 IN VARCHAR2,
631 x_study_reason3 IN VARCHAR2,
632 x_study_reason4 IN VARCHAR2,
633 x_study_reason5 IN VARCHAR2,
634 x_study_reason6 IN VARCHAR2,
635 x_study_reason7 IN VARCHAR2,
636 x_other_study_reason IN VARCHAR2,
637 x_no_other_study_reason IN VARCHAR2,
638 x_employer_sponsored IN VARCHAR2,
639 x_funding_source IN VARCHAR2,
640 x_teacher_teaching IN VARCHAR2,
641 x_teacher_seeking IN VARCHAR2,
642 x_teaching_sector IN VARCHAR2,
643 x_teaching_level IN VARCHAR2,
644 x_reason_for_ptcourse IN VARCHAR2,
645 x_job_while_studying IN VARCHAR2,
646 x_employer_support1 IN VARCHAR2,
647 x_employer_support2 IN VARCHAR2,
648 x_employer_support3 IN VARCHAR2,
649 x_employer_support4 IN VARCHAR2,
650 x_employer_support5 IN VARCHAR2,
651 x_popdlhe_flag IN VARCHAR2,
652 x_mode IN VARCHAR2
653 ) AS
654 /*
655 || Created By : [email protected]
656 || Created On : 17-APR-2003
657 || Purpose : Handles the INSERT DML logic for the table.
658 || Known limitations, enhancements or remarks :
659 || Change History :
660 || Who When What
661 || (reverse chronological order - newest change first)
662 */
663
664 x_last_update_date DATE;
665 x_last_updated_by NUMBER;
666 x_last_update_login NUMBER;
667
668 BEGIN
669
670 x_last_update_date := SYSDATE;
671 IF (x_mode = 'I') THEN
672 x_last_updated_by := 1;
673 x_last_update_login := 0;
674 ELSIF (x_mode = 'R') THEN
675 x_last_updated_by := fnd_global.user_id;
676 IF (x_last_updated_by IS NULL) THEN
677 x_last_updated_by := -1;
678 END IF;
679 x_last_update_login := fnd_global.login_id;
680 IF (x_last_update_login IS NULL) THEN
681 x_last_update_login := -1;
682 END IF;
683 ELSE
684 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
685 fnd_message.set_token ('ROUTINE', 'IGS_HE_STDNT_DLHE_PKG.INSERT_ROW');
686 igs_ge_msg_stack.add;
687 app_exception.raise_exception;
688 END IF;
689
690 before_dml(
691 p_action => 'INSERT',
692 x_rowid => x_rowid,
693 x_person_id => x_person_id,
694 x_submission_name => x_submission_name,
695 x_user_return_subclass => x_user_return_subclass,
696 x_return_name => x_return_name,
697 x_qual_period_code => x_qual_period_code,
698 x_dlhe_record_status => x_dlhe_record_status,
699 x_participant_source => x_participant_source,
700 x_date_status_changed => x_date_status_changed,
701 x_validation_status => x_validation_status,
702 x_admin_coding => x_admin_coding,
703 x_survey_method => x_survey_method,
704 x_employment => x_employment,
705 x_further_study => x_further_study,
706 x_qualified_teacher => x_qualified_teacher,
707 x_pt_study => x_pt_study,
708 x_employer_business => x_employer_business,
709 x_employer_name => x_employer_name,
710 x_employer_classification => x_employer_classification,
711 x_employer_location => x_employer_location,
712 x_employer_postcode => x_employer_postcode,
713 x_employer_country => x_employer_country,
714 x_job_title => x_job_title,
715 x_job_duties => x_job_duties,
716 x_job_classification => x_job_classification,
717 x_employer_size => x_employer_size,
718 x_job_duration => x_job_duration,
719 x_job_salary => x_job_salary,
720 x_salary_refused => x_salary_refused,
721 x_qualification_requirement => x_qualification_requirement,
722 x_qualification_importance => x_qualification_importance,
723 x_job_reason1 => x_job_reason1,
724 x_job_reason2 => x_job_reason2,
725 x_job_reason3 => x_job_reason3,
726 x_job_reason4 => x_job_reason4,
727 x_job_reason5 => x_job_reason5,
728 x_job_reason6 => x_job_reason6,
729 x_job_reason7 => x_job_reason7,
730 x_job_reason8 => x_job_reason8,
731 x_other_job_reason => x_other_job_reason,
732 x_no_other_job_reason => x_no_other_job_reason,
733 x_job_source => x_job_source,
734 x_other_job_source => x_other_job_source,
735 x_no_other_job_source => x_no_other_job_source,
736 x_previous_job => x_previous_job,
737 x_previous_jobtype1 => x_previous_jobtype1,
738 x_previous_jobtype2 => x_previous_jobtype2,
739 x_previous_jobtype3 => x_previous_jobtype3,
740 x_previous_jobtype4 => x_previous_jobtype4,
741 x_previous_jobtype5 => x_previous_jobtype5,
742 x_previous_jobtype6 => x_previous_jobtype6,
743 x_further_study_type => x_further_study_type,
744 x_course_name => x_course_name,
745 x_course_training_subject => x_course_training_subject,
746 x_research_subject => x_research_subject,
747 x_research_training_subject => x_research_training_subject,
748 x_further_study_provider => x_further_study_provider,
749 x_further_study_qualaim => x_further_study_qualaim,
750 x_professional_qualification => x_professional_qualification,
751 x_study_reason1 => x_study_reason1,
752 x_study_reason2 => x_study_reason2,
753 x_study_reason3 => x_study_reason3,
754 x_study_reason4 => x_study_reason4,
755 x_study_reason5 => x_study_reason5,
756 x_study_reason6 => x_study_reason6,
757 x_study_reason7 => x_study_reason7,
758 x_other_study_reason => x_other_study_reason,
759 x_no_other_study_reason => x_no_other_study_reason,
760 x_employer_sponsored => x_employer_sponsored,
761 x_funding_source => x_funding_source,
762 x_teacher_teaching => x_teacher_teaching,
763 x_teacher_seeking => x_teacher_seeking,
764 x_teaching_sector => x_teaching_sector,
765 x_teaching_level => x_teaching_level,
766 x_reason_for_ptcourse => x_reason_for_ptcourse,
767 x_job_while_studying => x_job_while_studying,
768 x_employer_support1 => x_employer_support1,
769 x_employer_support2 => x_employer_support2,
770 x_employer_support3 => x_employer_support3,
771 x_employer_support4 => x_employer_support4,
772 x_employer_support5 => x_employer_support5,
773 x_popdlhe_flag => x_popdlhe_flag,
774 x_creation_date => x_last_update_date,
775 x_created_by => x_last_updated_by,
776 x_last_update_date => x_last_update_date,
777 x_last_updated_by => x_last_updated_by,
778 x_last_update_login => x_last_update_login
779 );
780
781 INSERT INTO igs_he_stdnt_dlhe (
782 person_id,
783 submission_name,
784 user_return_subclass,
785 return_name,
786 qual_period_code,
787 dlhe_record_status,
788 participant_source,
789 date_status_changed,
790 validation_status,
791 admin_coding,
792 survey_method,
793 employment,
794 further_study,
795 qualified_teacher,
796 pt_study,
797 employer_business,
798 employer_name,
799 employer_classification,
800 employer_location,
801 employer_postcode,
802 employer_country,
803 job_title,
804 job_duties,
805 job_classification,
806 employer_size,
807 job_duration,
808 job_salary,
809 salary_refused,
810 qualification_requirement,
811 qualification_importance,
812 job_reason1,
813 job_reason2,
814 job_reason3,
815 job_reason4,
816 job_reason5,
817 job_reason6,
818 job_reason7,
819 job_reason8,
820 other_job_reason,
821 no_other_job_reason,
822 job_source,
823 other_job_source,
824 no_other_job_source,
825 previous_job,
826 previous_jobtype1,
827 previous_jobtype2,
828 previous_jobtype3,
829 previous_jobtype4,
830 previous_jobtype5,
831 previous_jobtype6,
832 further_study_type,
833 course_name,
834 course_training_subject,
835 research_subject,
836 research_training_subject,
837 further_study_provider,
838 further_study_qualaim,
839 professional_qualification,
840 study_reason1,
841 study_reason2,
842 study_reason3,
843 study_reason4,
844 study_reason5,
845 study_reason6,
846 study_reason7,
847 other_study_reason,
848 no_other_study_reason,
849 employer_sponsored,
850 funding_source,
851 teacher_teaching,
852 teacher_seeking,
853 teaching_sector,
854 teaching_level,
855 reason_for_ptcourse,
856 job_while_studying,
857 employer_support1,
858 employer_support2,
859 employer_support3,
860 employer_support4,
861 employer_support5,
862 popdlhe_flag,
863 creation_date,
864 created_by,
865 last_update_date,
866 last_updated_by,
867 last_update_login
868 ) VALUES (
869 new_references.person_id,
870 new_references.submission_name,
871 new_references.user_return_subclass,
872 new_references.return_name,
873 new_references.qual_period_code,
874 new_references.dlhe_record_status,
875 new_references.participant_source,
876 new_references.date_status_changed,
877 new_references.validation_status,
878 new_references.admin_coding,
879 new_references.survey_method,
880 new_references.employment,
881 new_references.further_study,
882 new_references.qualified_teacher,
883 new_references.pt_study,
884 new_references.employer_business,
885 new_references.employer_name,
886 new_references.employer_classification,
887 new_references.employer_location,
888 new_references.employer_postcode,
889 new_references.employer_country,
890 new_references.job_title,
891 new_references.job_duties,
892 new_references.job_classification,
893 new_references.employer_size,
894 new_references.job_duration,
895 new_references.job_salary,
896 new_references.salary_refused,
897 new_references.qualification_requirement,
898 new_references.qualification_importance,
899 new_references.job_reason1,
900 new_references.job_reason2,
901 new_references.job_reason3,
902 new_references.job_reason4,
903 new_references.job_reason5,
904 new_references.job_reason6,
905 new_references.job_reason7,
906 new_references.job_reason8,
907 new_references.other_job_reason,
908 new_references.no_other_job_reason,
909 new_references.job_source,
910 new_references.other_job_source,
911 new_references.no_other_job_source,
912 new_references.previous_job,
913 new_references.previous_jobtype1,
914 new_references.previous_jobtype2,
915 new_references.previous_jobtype3,
916 new_references.previous_jobtype4,
917 new_references.previous_jobtype5,
918 new_references.previous_jobtype6,
919 new_references.further_study_type,
920 new_references.course_name,
921 new_references.course_training_subject,
922 new_references.research_subject,
923 new_references.research_training_subject,
924 new_references.further_study_provider,
925 new_references.further_study_qualaim,
926 new_references.professional_qualification,
927 new_references.study_reason1,
928 new_references.study_reason2,
929 new_references.study_reason3,
930 new_references.study_reason4,
931 new_references.study_reason5,
932 new_references.study_reason6,
933 new_references.study_reason7,
934 new_references.other_study_reason,
935 new_references.no_other_study_reason,
936 new_references.employer_sponsored,
937 new_references.funding_source,
938 new_references.teacher_teaching,
939 new_references.teacher_seeking,
940 new_references.teaching_sector,
941 new_references.teaching_level,
942 new_references.reason_for_ptcourse,
943 new_references.job_while_studying,
944 new_references.employer_support1,
945 new_references.employer_support2,
946 new_references.employer_support3,
947 new_references.employer_support4,
948 new_references.employer_support5,
949 new_references.popdlhe_flag,
950 x_last_update_date,
951 x_last_updated_by,
952 x_last_update_date,
953 x_last_updated_by,
954 x_last_update_login
955 ) RETURNING ROWID INTO x_rowid;
956
957 END insert_row;
958
959
960 PROCEDURE lock_row (
961 x_rowid IN VARCHAR2,
962 x_person_id IN NUMBER,
963 x_submission_name IN VARCHAR2,
964 x_user_return_subclass IN VARCHAR2,
965 x_return_name IN VARCHAR2,
966 x_qual_period_code IN VARCHAR2,
967 x_dlhe_record_status IN VARCHAR2,
968 x_participant_source IN VARCHAR2,
969 x_date_status_changed IN DATE,
970 x_validation_status IN VARCHAR2,
971 x_admin_coding IN VARCHAR2,
972 x_survey_method IN VARCHAR2,
973 x_employment IN VARCHAR2,
974 x_further_study IN VARCHAR2,
975 x_qualified_teacher IN VARCHAR2,
976 x_pt_study IN VARCHAR2,
977 x_employer_business IN VARCHAR2,
978 x_employer_name IN VARCHAR2,
979 x_employer_classification IN VARCHAR2,
980 x_employer_location IN VARCHAR2,
981 x_employer_postcode IN VARCHAR2,
982 x_employer_country IN VARCHAR2,
983 x_job_title IN VARCHAR2,
984 x_job_duties IN VARCHAR2,
985 x_job_classification IN VARCHAR2,
986 x_employer_size IN VARCHAR2,
987 x_job_duration IN VARCHAR2,
988 x_job_salary IN NUMBER,
989 x_salary_refused IN VARCHAR2,
990 x_qualification_requirement IN VARCHAR2,
991 x_qualification_importance IN VARCHAR2,
992 x_job_reason1 IN VARCHAR2,
993 x_job_reason2 IN VARCHAR2,
994 x_job_reason3 IN VARCHAR2,
995 x_job_reason4 IN VARCHAR2,
996 x_job_reason5 IN VARCHAR2,
997 x_job_reason6 IN VARCHAR2,
998 x_job_reason7 IN VARCHAR2,
999 x_job_reason8 IN VARCHAR2,
1000 x_other_job_reason IN VARCHAR2,
1001 x_no_other_job_reason IN VARCHAR2,
1002 x_job_source IN VARCHAR2,
1003 x_other_job_source IN VARCHAR2,
1004 x_no_other_job_source IN VARCHAR2,
1005 x_previous_job IN VARCHAR2,
1006 x_previous_jobtype1 IN VARCHAR2,
1007 x_previous_jobtype2 IN VARCHAR2,
1008 x_previous_jobtype3 IN VARCHAR2,
1009 x_previous_jobtype4 IN VARCHAR2,
1010 x_previous_jobtype5 IN VARCHAR2,
1011 x_previous_jobtype6 IN VARCHAR2,
1012 x_further_study_type IN VARCHAR2,
1013 x_course_name IN VARCHAR2,
1014 x_course_training_subject IN VARCHAR2,
1015 x_research_subject IN VARCHAR2,
1016 x_research_training_subject IN VARCHAR2,
1017 x_further_study_provider IN VARCHAR2,
1018 x_further_study_qualaim IN VARCHAR2,
1019 x_professional_qualification IN VARCHAR2,
1020 x_study_reason1 IN VARCHAR2,
1021 x_study_reason2 IN VARCHAR2,
1022 x_study_reason3 IN VARCHAR2,
1023 x_study_reason4 IN VARCHAR2,
1024 x_study_reason5 IN VARCHAR2,
1025 x_study_reason6 IN VARCHAR2,
1026 x_study_reason7 IN VARCHAR2,
1027 x_other_study_reason IN VARCHAR2,
1028 x_no_other_study_reason IN VARCHAR2,
1029 x_employer_sponsored IN VARCHAR2,
1030 x_funding_source IN VARCHAR2,
1031 x_teacher_teaching IN VARCHAR2,
1032 x_teacher_seeking IN VARCHAR2,
1033 x_teaching_sector IN VARCHAR2,
1034 x_teaching_level IN VARCHAR2,
1035 x_reason_for_ptcourse IN VARCHAR2,
1036 x_job_while_studying IN VARCHAR2,
1037 x_employer_support1 IN VARCHAR2,
1038 x_employer_support2 IN VARCHAR2,
1039 x_employer_support3 IN VARCHAR2,
1040 x_employer_support4 IN VARCHAR2,
1041 x_employer_support5 IN VARCHAR2,
1042 x_popdlhe_flag IN VARCHAR2
1043 ) AS
1044 /*
1045 || Created By : [email protected]
1046 || Created On : 17-APR-2003
1047 || Purpose : Handles the LOCK mechanism for the table.
1048 || Known limitations, enhancements or remarks :
1049 || Change History :
1050 || Who When What
1051 || (reverse chronological order - newest change first)
1052 */
1053 CURSOR c1 IS
1054 SELECT
1055 user_return_subclass,
1056 qual_period_code,
1057 dlhe_record_status,
1058 participant_source,
1059 date_status_changed,
1060 validation_status,
1061 admin_coding,
1062 survey_method,
1063 employment,
1064 further_study,
1065 qualified_teacher,
1066 pt_study,
1067 employer_business,
1068 employer_name,
1069 employer_classification,
1070 employer_location,
1071 employer_postcode,
1072 employer_country,
1073 job_title,
1074 job_duties,
1075 job_classification,
1076 employer_size,
1077 job_duration,
1078 job_salary,
1079 salary_refused,
1080 qualification_requirement,
1081 qualification_importance,
1082 job_reason1,
1083 job_reason2,
1084 job_reason3,
1085 job_reason4,
1086 job_reason5,
1087 job_reason6,
1088 job_reason7,
1089 job_reason8,
1090 other_job_reason,
1091 no_other_job_reason,
1092 job_source,
1093 other_job_source,
1094 no_other_job_source,
1095 previous_job,
1096 previous_jobtype1,
1097 previous_jobtype2,
1098 previous_jobtype3,
1099 previous_jobtype4,
1100 previous_jobtype5,
1101 previous_jobtype6,
1102 further_study_type,
1103 course_name,
1104 course_training_subject,
1105 research_subject,
1106 research_training_subject,
1107 further_study_provider,
1108 further_study_qualaim,
1109 professional_qualification,
1110 study_reason1,
1111 study_reason2,
1112 study_reason3,
1113 study_reason4,
1114 study_reason5,
1115 study_reason6,
1116 study_reason7,
1117 other_study_reason,
1118 no_other_study_reason,
1119 employer_sponsored,
1120 funding_source,
1121 teacher_teaching,
1122 teacher_seeking,
1123 teaching_sector,
1124 teaching_level,
1125 reason_for_ptcourse,
1126 job_while_studying,
1127 employer_support1,
1128 employer_support2,
1129 employer_support3,
1130 employer_support4,
1131 employer_support5,
1132 popdlhe_flag
1133 FROM igs_he_stdnt_dlhe
1134 WHERE rowid = x_rowid
1135 FOR UPDATE NOWAIT;
1136
1137 tlinfo c1%ROWTYPE;
1138
1139 BEGIN
1140
1141 OPEN c1;
1142 FETCH c1 INTO tlinfo;
1143 IF (c1%notfound) THEN
1144 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
1145 igs_ge_msg_stack.add;
1146 CLOSE c1;
1147 app_exception.raise_exception;
1148 RETURN;
1149 END IF;
1150 CLOSE c1;
1151
1152 IF (
1153 (tlinfo.user_return_subclass = x_user_return_subclass)
1154 AND (tlinfo.qual_period_code = x_qual_period_code)
1155 AND (tlinfo.dlhe_record_status = x_dlhe_record_status)
1156 AND (tlinfo.participant_source = x_participant_source)
1157 AND ((tlinfo.date_status_changed = x_date_status_changed) OR ((tlinfo.date_status_changed IS NULL) AND (X_date_status_changed IS NULL)))
1158 AND ((tlinfo.validation_status = x_validation_status) OR ((tlinfo.validation_status IS NULL) AND (X_validation_status IS NULL)))
1159 AND ((tlinfo.admin_coding = x_admin_coding) OR ((tlinfo.admin_coding IS NULL) AND (X_admin_coding IS NULL)))
1160 AND ((tlinfo.survey_method = x_survey_method) OR ((tlinfo.survey_method IS NULL) AND (X_survey_method IS NULL)))
1161 AND ((tlinfo.employment = x_employment) OR ((tlinfo.employment IS NULL) AND (X_employment IS NULL)))
1162 AND ((tlinfo.further_study = x_further_study) OR ((tlinfo.further_study IS NULL) AND (X_further_study IS NULL)))
1163 AND ((tlinfo.qualified_teacher = x_qualified_teacher) OR ((tlinfo.qualified_teacher IS NULL) AND (X_qualified_teacher IS NULL)))
1164 AND ((tlinfo.pt_study = x_pt_study) OR ((tlinfo.pt_study IS NULL) AND (X_pt_study IS NULL)))
1165 AND ((tlinfo.employer_business = x_employer_business) OR ((tlinfo.employer_business IS NULL) AND (X_employer_business IS NULL)))
1166 AND ((tlinfo.employer_name = x_employer_name) OR ((tlinfo.employer_name IS NULL) AND (X_employer_name IS NULL)))
1167 AND ((tlinfo.employer_classification = x_employer_classification) OR ((tlinfo.employer_classification IS NULL) AND (X_employer_classification IS NULL)))
1168 AND ((tlinfo.employer_location = x_employer_location) OR ((tlinfo.employer_location IS NULL) AND (X_employer_location IS NULL)))
1169 AND ((tlinfo.employer_postcode = x_employer_postcode) OR ((tlinfo.employer_postcode IS NULL) AND (X_employer_postcode IS NULL)))
1170 AND ((tlinfo.employer_country = x_employer_country) OR ((tlinfo.employer_country IS NULL) AND (X_employer_country IS NULL)))
1171 AND ((tlinfo.job_title = x_job_title) OR ((tlinfo.job_title IS NULL) AND (X_job_title IS NULL)))
1172 AND ((tlinfo.job_duties = x_job_duties) OR ((tlinfo.job_duties IS NULL) AND (X_job_duties IS NULL)))
1173 AND ((tlinfo.job_classification = x_job_classification) OR ((tlinfo.job_classification IS NULL) AND (X_job_classification IS NULL)))
1174 AND ((tlinfo.employer_size = x_employer_size) OR ((tlinfo.employer_size IS NULL) AND (X_employer_size IS NULL)))
1175 AND ((tlinfo.job_duration = x_job_duration) OR ((tlinfo.job_duration IS NULL) AND (X_job_duration IS NULL)))
1176 AND ((tlinfo.job_salary = x_job_salary) OR ((tlinfo.job_salary IS NULL) AND (X_job_salary IS NULL)))
1177 AND (tlinfo.salary_refused = x_salary_refused)
1178 AND ((tlinfo.qualification_requirement = x_qualification_requirement) OR ((tlinfo.qualification_requirement IS NULL) AND (X_qualification_requirement IS NULL)))
1179 AND ((tlinfo.qualification_importance = x_qualification_importance) OR ((tlinfo.qualification_importance IS NULL) AND (X_qualification_importance IS NULL)))
1180 AND (tlinfo.job_reason1 = x_job_reason1)
1181 AND (tlinfo.job_reason2 = x_job_reason2)
1182 AND (tlinfo.job_reason3 = x_job_reason3)
1183 AND (tlinfo.job_reason4 = x_job_reason4)
1184 AND (tlinfo.job_reason5 = x_job_reason5)
1185 AND (tlinfo.job_reason6 = x_job_reason6)
1186 AND (tlinfo.job_reason7 = x_job_reason7)
1187 AND (tlinfo.job_reason8 = x_job_reason8)
1188 AND ((tlinfo.other_job_reason = x_other_job_reason) OR ((tlinfo.other_job_reason IS NULL) AND (X_other_job_reason IS NULL)))
1189 AND (tlinfo.no_other_job_reason = x_no_other_job_reason)
1190 AND ((tlinfo.job_source = x_job_source) OR ((tlinfo.job_source IS NULL) AND (X_job_source IS NULL)))
1191 AND ((tlinfo.other_job_source = x_other_job_source) OR ((tlinfo.other_job_source IS NULL) AND (X_other_job_source IS NULL)))
1192 AND (tlinfo.no_other_job_source = x_no_other_job_source)
1193 AND ((tlinfo.previous_job = x_previous_job) OR ((tlinfo.previous_job IS NULL) AND (X_previous_job IS NULL)))
1194 AND (tlinfo.previous_jobtype1 = x_previous_jobtype1)
1195 AND (tlinfo.previous_jobtype2 = x_previous_jobtype2)
1196 AND (tlinfo.previous_jobtype3 = x_previous_jobtype3)
1197 AND (tlinfo.previous_jobtype4 = x_previous_jobtype4)
1198 AND (tlinfo.previous_jobtype5 = x_previous_jobtype5)
1199 AND (tlinfo.previous_jobtype6 = x_previous_jobtype6)
1200 AND ((tlinfo.further_study_type = x_further_study_type) OR ((tlinfo.further_study_type IS NULL) AND (X_further_study_type IS NULL)))
1201 AND ((tlinfo.course_name = x_course_name) OR ((tlinfo.course_name IS NULL) AND (X_course_name IS NULL)))
1202 AND ((tlinfo.course_training_subject = x_course_training_subject) OR ((tlinfo.course_training_subject IS NULL) AND (X_course_training_subject IS NULL)))
1203 AND ((tlinfo.research_subject = x_research_subject) OR ((tlinfo.research_subject IS NULL) AND (X_research_subject IS NULL)))
1204 AND ((tlinfo.research_training_subject = x_research_training_subject) OR ((tlinfo.research_training_subject IS NULL) AND (X_research_training_subject IS NULL)))
1205 AND ((tlinfo.further_study_provider = x_further_study_provider) OR ((tlinfo.further_study_provider IS NULL) AND (X_further_study_provider IS NULL)))
1206 AND ((tlinfo.further_study_qualaim = x_further_study_qualaim) OR ((tlinfo.further_study_qualaim IS NULL) AND (X_further_study_qualaim IS NULL)))
1207 AND ((tlinfo.professional_qualification = x_professional_qualification) OR ((tlinfo.professional_qualification IS NULL) AND (X_professional_qualification IS NULL)))
1208 AND ((tlinfo.study_reason1 = x_study_reason1) OR ((tlinfo.study_reason1 IS NULL) AND (X_study_reason1 IS NULL)))
1209 AND (tlinfo.study_reason2 = x_study_reason2)
1210 AND (tlinfo.study_reason3 = x_study_reason3)
1211 AND (tlinfo.study_reason4 = x_study_reason4)
1212 AND (tlinfo.study_reason5 = x_study_reason5)
1213 AND (tlinfo.study_reason6 = x_study_reason6)
1214 AND (tlinfo.study_reason7 = x_study_reason7)
1215 AND ((tlinfo.other_study_reason = x_other_study_reason) OR ((tlinfo.other_study_reason IS NULL) AND (X_other_study_reason IS NULL)))
1216 AND (tlinfo.no_other_study_reason = x_no_other_study_reason)
1217 AND (tlinfo.employer_sponsored = x_employer_sponsored)
1218 AND ((tlinfo.funding_source = x_funding_source) OR ((tlinfo.funding_source IS NULL) AND (X_funding_source IS NULL)))
1219 AND ((tlinfo.teacher_teaching = x_teacher_teaching) OR ((tlinfo.teacher_teaching IS NULL) AND (X_teacher_teaching IS NULL)))
1220 AND ((tlinfo.teacher_seeking = x_teacher_seeking) OR ((tlinfo.teacher_seeking IS NULL) AND (X_teacher_seeking IS NULL)))
1221 AND ((tlinfo.teaching_sector = x_teaching_sector) OR ((tlinfo.teaching_sector IS NULL) AND (X_teaching_sector IS NULL)))
1222 AND ((tlinfo.teaching_level = x_teaching_level) OR ((tlinfo.teaching_level IS NULL) AND (X_teaching_level IS NULL)))
1223 AND ((tlinfo.reason_for_ptcourse = x_reason_for_ptcourse) OR ((tlinfo.reason_for_ptcourse IS NULL) AND (X_reason_for_ptcourse IS NULL)))
1224 AND (tlinfo.job_while_studying = x_job_while_studying)
1225 AND (tlinfo.employer_support1 = x_employer_support1)
1226 AND (tlinfo.employer_support2 = x_employer_support2)
1227 AND (tlinfo.employer_support3 = x_employer_support3)
1228 AND (tlinfo.employer_support4 = x_employer_support4)
1229 AND (tlinfo.employer_support5 = x_employer_support5)
1230 AND (tlinfo.popdlhe_flag = x_popdlhe_flag)
1231 ) THEN
1232 NULL;
1233 ELSE
1234 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
1235 igs_ge_msg_stack.add;
1236 app_exception.raise_exception;
1237 END IF;
1238
1239 RETURN;
1240
1241 END lock_row;
1242
1243
1244 PROCEDURE update_row (
1245 x_rowid IN VARCHAR2,
1246 x_person_id IN NUMBER,
1247 x_submission_name IN VARCHAR2,
1248 x_user_return_subclass IN VARCHAR2,
1249 x_return_name IN VARCHAR2,
1250 x_qual_period_code IN VARCHAR2,
1251 x_dlhe_record_status IN VARCHAR2,
1252 x_participant_source IN VARCHAR2,
1253 x_date_status_changed IN DATE,
1254 x_validation_status IN VARCHAR2,
1255 x_admin_coding IN VARCHAR2,
1256 x_survey_method IN VARCHAR2,
1257 x_employment IN VARCHAR2,
1258 x_further_study IN VARCHAR2,
1259 x_qualified_teacher IN VARCHAR2,
1260 x_pt_study IN VARCHAR2,
1261 x_employer_business IN VARCHAR2,
1262 x_employer_name IN VARCHAR2,
1263 x_employer_classification IN VARCHAR2,
1264 x_employer_location IN VARCHAR2,
1265 x_employer_postcode IN VARCHAR2,
1266 x_employer_country IN VARCHAR2,
1267 x_job_title IN VARCHAR2,
1268 x_job_duties IN VARCHAR2,
1269 x_job_classification IN VARCHAR2,
1270 x_employer_size IN VARCHAR2,
1271 x_job_duration IN VARCHAR2,
1272 x_job_salary IN NUMBER,
1273 x_salary_refused IN VARCHAR2,
1274 x_qualification_requirement IN VARCHAR2,
1275 x_qualification_importance IN VARCHAR2,
1276 x_job_reason1 IN VARCHAR2,
1277 x_job_reason2 IN VARCHAR2,
1278 x_job_reason3 IN VARCHAR2,
1279 x_job_reason4 IN VARCHAR2,
1280 x_job_reason5 IN VARCHAR2,
1281 x_job_reason6 IN VARCHAR2,
1282 x_job_reason7 IN VARCHAR2,
1283 x_job_reason8 IN VARCHAR2,
1284 x_other_job_reason IN VARCHAR2,
1285 x_no_other_job_reason IN VARCHAR2,
1286 x_job_source IN VARCHAR2,
1287 x_other_job_source IN VARCHAR2,
1288 x_no_other_job_source IN VARCHAR2,
1289 x_previous_job IN VARCHAR2,
1290 x_previous_jobtype1 IN VARCHAR2,
1291 x_previous_jobtype2 IN VARCHAR2,
1292 x_previous_jobtype3 IN VARCHAR2,
1293 x_previous_jobtype4 IN VARCHAR2,
1294 x_previous_jobtype5 IN VARCHAR2,
1295 x_previous_jobtype6 IN VARCHAR2,
1296 x_further_study_type IN VARCHAR2,
1297 x_course_name IN VARCHAR2,
1298 x_course_training_subject IN VARCHAR2,
1299 x_research_subject IN VARCHAR2,
1300 x_research_training_subject IN VARCHAR2,
1301 x_further_study_provider IN VARCHAR2,
1302 x_further_study_qualaim IN VARCHAR2,
1303 x_professional_qualification IN VARCHAR2,
1304 x_study_reason1 IN VARCHAR2,
1305 x_study_reason2 IN VARCHAR2,
1306 x_study_reason3 IN VARCHAR2,
1307 x_study_reason4 IN VARCHAR2,
1308 x_study_reason5 IN VARCHAR2,
1309 x_study_reason6 IN VARCHAR2,
1310 x_study_reason7 IN VARCHAR2,
1311 x_other_study_reason IN VARCHAR2,
1312 x_no_other_study_reason IN VARCHAR2,
1313 x_employer_sponsored IN VARCHAR2,
1314 x_funding_source IN VARCHAR2,
1315 x_teacher_teaching IN VARCHAR2,
1316 x_teacher_seeking IN VARCHAR2,
1317 x_teaching_sector IN VARCHAR2,
1318 x_teaching_level IN VARCHAR2,
1319 x_reason_for_ptcourse IN VARCHAR2,
1320 x_job_while_studying IN VARCHAR2,
1321 x_employer_support1 IN VARCHAR2,
1322 x_employer_support2 IN VARCHAR2,
1323 x_employer_support3 IN VARCHAR2,
1324 x_employer_support4 IN VARCHAR2,
1325 x_employer_support5 IN VARCHAR2,
1326 x_popdlhe_flag IN VARCHAR2,
1327 x_mode IN VARCHAR2
1328 ) AS
1329 /*
1330 || Created By : [email protected]
1331 || Created On : 17-APR-2003
1332 || Purpose : Handles the UPDATE DML logic for the table.
1333 || Known limitations, enhancements or remarks :
1334 || Change History :
1335 || Who When What
1336 || (reverse chronological order - newest change first)
1337 */
1338 x_last_update_date DATE ;
1339 x_last_updated_by NUMBER;
1340 x_last_update_login NUMBER;
1341
1342 BEGIN
1343
1344 x_last_update_date := SYSDATE;
1345 IF (X_MODE = 'I') THEN
1346 x_last_updated_by := 1;
1347 x_last_update_login := 0;
1348 ELSIF (x_mode = 'R') THEN
1349 x_last_updated_by := fnd_global.user_id;
1350 IF x_last_updated_by IS NULL THEN
1351 x_last_updated_by := -1;
1352 END IF;
1353 x_last_update_login := fnd_global.login_id;
1354 IF (x_last_update_login IS NULL) THEN
1355 x_last_update_login := -1;
1356 END IF;
1357 ELSE
1358 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
1359 fnd_message.set_token ('ROUTINE', 'IGS_HE_STDNT_DLHE_PKG.UPDATE_ROW');
1360 igs_ge_msg_stack.add;
1361 app_exception.raise_exception;
1362 END IF;
1363
1364 before_dml(
1365 p_action => 'UPDATE',
1366 x_rowid => x_rowid,
1367 x_person_id => x_person_id,
1368 x_submission_name => x_submission_name,
1369 x_user_return_subclass => x_user_return_subclass,
1370 x_return_name => x_return_name,
1371 x_qual_period_code => x_qual_period_code,
1372 x_dlhe_record_status => x_dlhe_record_status,
1373 x_participant_source => x_participant_source,
1374 x_date_status_changed => x_date_status_changed,
1375 x_validation_status => x_validation_status,
1376 x_admin_coding => x_admin_coding,
1377 x_survey_method => x_survey_method,
1378 x_employment => x_employment,
1379 x_further_study => x_further_study,
1380 x_qualified_teacher => x_qualified_teacher,
1381 x_pt_study => x_pt_study,
1382 x_employer_business => x_employer_business,
1383 x_employer_name => x_employer_name,
1384 x_employer_classification => x_employer_classification,
1385 x_employer_location => x_employer_location,
1386 x_employer_postcode => x_employer_postcode,
1387 x_employer_country => x_employer_country,
1388 x_job_title => x_job_title,
1389 x_job_duties => x_job_duties,
1390 x_job_classification => x_job_classification,
1391 x_employer_size => x_employer_size,
1392 x_job_duration => x_job_duration,
1393 x_job_salary => x_job_salary,
1394 x_salary_refused => x_salary_refused,
1395 x_qualification_requirement => x_qualification_requirement,
1396 x_qualification_importance => x_qualification_importance,
1397 x_job_reason1 => x_job_reason1,
1398 x_job_reason2 => x_job_reason2,
1399 x_job_reason3 => x_job_reason3,
1400 x_job_reason4 => x_job_reason4,
1401 x_job_reason5 => x_job_reason5,
1402 x_job_reason6 => x_job_reason6,
1403 x_job_reason7 => x_job_reason7,
1404 x_job_reason8 => x_job_reason8,
1405 x_other_job_reason => x_other_job_reason,
1406 x_no_other_job_reason => x_no_other_job_reason,
1407 x_job_source => x_job_source,
1408 x_other_job_source => x_other_job_source,
1409 x_no_other_job_source => x_no_other_job_source,
1410 x_previous_job => x_previous_job,
1411 x_previous_jobtype1 => x_previous_jobtype1,
1412 x_previous_jobtype2 => x_previous_jobtype2,
1413 x_previous_jobtype3 => x_previous_jobtype3,
1414 x_previous_jobtype4 => x_previous_jobtype4,
1415 x_previous_jobtype5 => x_previous_jobtype5,
1416 x_previous_jobtype6 => x_previous_jobtype6,
1417 x_further_study_type => x_further_study_type,
1418 x_course_name => x_course_name,
1419 x_course_training_subject => x_course_training_subject,
1420 x_research_subject => x_research_subject,
1421 x_research_training_subject => x_research_training_subject,
1422 x_further_study_provider => x_further_study_provider,
1423 x_further_study_qualaim => x_further_study_qualaim,
1424 x_professional_qualification => x_professional_qualification,
1425 x_study_reason1 => x_study_reason1,
1426 x_study_reason2 => x_study_reason2,
1427 x_study_reason3 => x_study_reason3,
1428 x_study_reason4 => x_study_reason4,
1429 x_study_reason5 => x_study_reason5,
1430 x_study_reason6 => x_study_reason6,
1431 x_study_reason7 => x_study_reason7,
1432 x_other_study_reason => x_other_study_reason,
1433 x_no_other_study_reason => x_no_other_study_reason,
1434 x_employer_sponsored => x_employer_sponsored,
1435 x_funding_source => x_funding_source,
1436 x_teacher_teaching => x_teacher_teaching,
1437 x_teacher_seeking => x_teacher_seeking,
1438 x_teaching_sector => x_teaching_sector,
1439 x_teaching_level => x_teaching_level,
1440 x_reason_for_ptcourse => x_reason_for_ptcourse,
1441 x_job_while_studying => x_job_while_studying,
1442 x_employer_support1 => x_employer_support1,
1443 x_employer_support2 => x_employer_support2,
1444 x_employer_support3 => x_employer_support3,
1445 x_employer_support4 => x_employer_support4,
1446 x_employer_support5 => x_employer_support5,
1447 x_popdlhe_flag => x_popdlhe_flag,
1448 x_creation_date => x_last_update_date,
1449 x_created_by => x_last_updated_by,
1450 x_last_update_date => x_last_update_date,
1451 x_last_updated_by => x_last_updated_by,
1452 x_last_update_login => x_last_update_login
1453 );
1454
1455 UPDATE igs_he_stdnt_dlhe
1456 SET
1457 user_return_subclass = new_references.user_return_subclass,
1458 qual_period_code = new_references.qual_period_code,
1459 dlhe_record_status = new_references.dlhe_record_status,
1460 participant_source = new_references.participant_source,
1461 date_status_changed = new_references.date_status_changed,
1462 validation_status = new_references.validation_status,
1463 admin_coding = new_references.admin_coding,
1464 survey_method = new_references.survey_method,
1465 employment = new_references.employment,
1466 further_study = new_references.further_study,
1467 qualified_teacher = new_references.qualified_teacher,
1468 pt_study = new_references.pt_study,
1469 employer_business = new_references.employer_business,
1470 employer_name = new_references.employer_name,
1471 employer_classification = new_references.employer_classification,
1472 employer_location = new_references.employer_location,
1473 employer_postcode = new_references.employer_postcode,
1474 employer_country = new_references.employer_country,
1475 job_title = new_references.job_title,
1476 job_duties = new_references.job_duties,
1477 job_classification = new_references.job_classification,
1478 employer_size = new_references.employer_size,
1479 job_duration = new_references.job_duration,
1480 job_salary = new_references.job_salary,
1481 salary_refused = new_references.salary_refused,
1482 qualification_requirement = new_references.qualification_requirement,
1483 qualification_importance = new_references.qualification_importance,
1484 job_reason1 = new_references.job_reason1,
1485 job_reason2 = new_references.job_reason2,
1486 job_reason3 = new_references.job_reason3,
1487 job_reason4 = new_references.job_reason4,
1488 job_reason5 = new_references.job_reason5,
1489 job_reason6 = new_references.job_reason6,
1490 job_reason7 = new_references.job_reason7,
1491 job_reason8 = new_references.job_reason8,
1492 other_job_reason = new_references.other_job_reason,
1493 no_other_job_reason = new_references.no_other_job_reason,
1494 job_source = new_references.job_source,
1495 other_job_source = new_references.other_job_source,
1496 no_other_job_source = new_references.no_other_job_source,
1497 previous_job = new_references.previous_job,
1498 previous_jobtype1 = new_references.previous_jobtype1,
1499 previous_jobtype2 = new_references.previous_jobtype2,
1500 previous_jobtype3 = new_references.previous_jobtype3,
1501 previous_jobtype4 = new_references.previous_jobtype4,
1502 previous_jobtype5 = new_references.previous_jobtype5,
1503 previous_jobtype6 = new_references.previous_jobtype6,
1504 further_study_type = new_references.further_study_type,
1505 course_name = new_references.course_name,
1506 course_training_subject = new_references.course_training_subject,
1507 research_subject = new_references.research_subject,
1508 research_training_subject = new_references.research_training_subject,
1509 further_study_provider = new_references.further_study_provider,
1510 further_study_qualaim = new_references.further_study_qualaim,
1511 professional_qualification = new_references.professional_qualification,
1512 study_reason1 = new_references.study_reason1,
1513 study_reason2 = new_references.study_reason2,
1514 study_reason3 = new_references.study_reason3,
1515 study_reason4 = new_references.study_reason4,
1516 study_reason5 = new_references.study_reason5,
1517 study_reason6 = new_references.study_reason6,
1518 study_reason7 = new_references.study_reason7,
1519 other_study_reason = new_references.other_study_reason,
1520 no_other_study_reason = new_references.no_other_study_reason,
1521 employer_sponsored = new_references.employer_sponsored,
1522 funding_source = new_references.funding_source,
1523 teacher_teaching = new_references.teacher_teaching,
1524 teacher_seeking = new_references.teacher_seeking,
1525 teaching_sector = new_references.teaching_sector,
1526 teaching_level = new_references.teaching_level,
1527 reason_for_ptcourse = new_references.reason_for_ptcourse,
1528 job_while_studying = new_references.job_while_studying,
1529 employer_support1 = new_references.employer_support1,
1530 employer_support2 = new_references.employer_support2,
1531 employer_support3 = new_references.employer_support3,
1532 employer_support4 = new_references.employer_support4,
1533 employer_support5 = new_references.employer_support5,
1534 popdlhe_flag = new_references.popdlhe_flag,
1535 last_update_date = x_last_update_date,
1536 last_updated_by = x_last_updated_by,
1537 last_update_login = x_last_update_login
1538 WHERE rowid = x_rowid;
1539
1540 IF (SQL%NOTFOUND) THEN
1541 RAISE NO_DATA_FOUND;
1542 END IF;
1543
1544 END update_row;
1545
1546
1547 PROCEDURE add_row (
1548 x_rowid IN OUT NOCOPY VARCHAR2,
1549 x_person_id IN NUMBER,
1550 x_submission_name IN VARCHAR2,
1551 x_user_return_subclass IN VARCHAR2,
1552 x_return_name IN VARCHAR2,
1553 x_qual_period_code IN VARCHAR2,
1554 x_dlhe_record_status IN VARCHAR2,
1555 x_participant_source IN VARCHAR2,
1556 x_date_status_changed IN DATE,
1557 x_validation_status IN VARCHAR2,
1558 x_admin_coding IN VARCHAR2,
1559 x_survey_method IN VARCHAR2,
1560 x_employment IN VARCHAR2,
1561 x_further_study IN VARCHAR2,
1562 x_qualified_teacher IN VARCHAR2,
1563 x_pt_study IN VARCHAR2,
1564 x_employer_business IN VARCHAR2,
1565 x_employer_name IN VARCHAR2,
1566 x_employer_classification IN VARCHAR2,
1567 x_employer_location IN VARCHAR2,
1568 x_employer_postcode IN VARCHAR2,
1569 x_employer_country IN VARCHAR2,
1570 x_job_title IN VARCHAR2,
1571 x_job_duties IN VARCHAR2,
1572 x_job_classification IN VARCHAR2,
1573 x_employer_size IN VARCHAR2,
1574 x_job_duration IN VARCHAR2,
1575 x_job_salary IN NUMBER,
1576 x_salary_refused IN VARCHAR2,
1577 x_qualification_requirement IN VARCHAR2,
1578 x_qualification_importance IN VARCHAR2,
1579 x_job_reason1 IN VARCHAR2,
1580 x_job_reason2 IN VARCHAR2,
1581 x_job_reason3 IN VARCHAR2,
1582 x_job_reason4 IN VARCHAR2,
1583 x_job_reason5 IN VARCHAR2,
1584 x_job_reason6 IN VARCHAR2,
1585 x_job_reason7 IN VARCHAR2,
1586 x_job_reason8 IN VARCHAR2,
1587 x_other_job_reason IN VARCHAR2,
1588 x_no_other_job_reason IN VARCHAR2,
1589 x_job_source IN VARCHAR2,
1590 x_other_job_source IN VARCHAR2,
1591 x_no_other_job_source IN VARCHAR2,
1592 x_previous_job IN VARCHAR2,
1593 x_previous_jobtype1 IN VARCHAR2,
1594 x_previous_jobtype2 IN VARCHAR2,
1595 x_previous_jobtype3 IN VARCHAR2,
1596 x_previous_jobtype4 IN VARCHAR2,
1597 x_previous_jobtype5 IN VARCHAR2,
1598 x_previous_jobtype6 IN VARCHAR2,
1599 x_further_study_type IN VARCHAR2,
1600 x_course_name IN VARCHAR2,
1601 x_course_training_subject IN VARCHAR2,
1602 x_research_subject IN VARCHAR2,
1603 x_research_training_subject IN VARCHAR2,
1604 x_further_study_provider IN VARCHAR2,
1605 x_further_study_qualaim IN VARCHAR2,
1606 x_professional_qualification IN VARCHAR2,
1607 x_study_reason1 IN VARCHAR2,
1608 x_study_reason2 IN VARCHAR2,
1609 x_study_reason3 IN VARCHAR2,
1610 x_study_reason4 IN VARCHAR2,
1611 x_study_reason5 IN VARCHAR2,
1612 x_study_reason6 IN VARCHAR2,
1613 x_study_reason7 IN VARCHAR2,
1614 x_other_study_reason IN VARCHAR2,
1615 x_no_other_study_reason IN VARCHAR2,
1616 x_employer_sponsored IN VARCHAR2,
1617 x_funding_source IN VARCHAR2,
1618 x_teacher_teaching IN VARCHAR2,
1619 x_teacher_seeking IN VARCHAR2,
1620 x_teaching_sector IN VARCHAR2,
1621 x_teaching_level IN VARCHAR2,
1622 x_reason_for_ptcourse IN VARCHAR2,
1623 x_job_while_studying IN VARCHAR2,
1624 x_employer_support1 IN VARCHAR2,
1625 x_employer_support2 IN VARCHAR2,
1626 x_employer_support3 IN VARCHAR2,
1627 x_employer_support4 IN VARCHAR2,
1628 x_employer_support5 IN VARCHAR2,
1629 x_popdlhe_flag IN VARCHAR2,
1630 x_mode IN VARCHAR2
1631 ) AS
1632 /*
1633 || Created By : [email protected]
1634 || Created On : 17-APR-2003
1635 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
1636 || Known limitations, enhancements or remarks :
1637 || Change History :
1638 || Who When What
1639 || (reverse chronological order - newest change first)
1640 */
1641 CURSOR c1 IS
1642 SELECT rowid
1643 FROM igs_he_stdnt_dlhe
1644 WHERE submission_name = x_submission_name
1645 AND return_name = x_return_name
1646 AND person_id = x_person_id;
1647
1648 BEGIN
1649
1650 OPEN c1;
1651 FETCH c1 INTO x_rowid;
1652 IF (c1%NOTFOUND) THEN
1653 CLOSE c1;
1654
1655 insert_row (
1656 x_rowid,
1657 x_person_id,
1658 x_submission_name,
1659 x_user_return_subclass,
1660 x_return_name,
1661 x_qual_period_code,
1662 x_dlhe_record_status,
1663 x_participant_source,
1664 x_date_status_changed,
1665 x_validation_status,
1666 x_admin_coding,
1667 x_survey_method,
1668 x_employment,
1669 x_further_study,
1670 x_qualified_teacher,
1671 x_pt_study,
1672 x_employer_business,
1673 x_employer_name,
1674 x_employer_classification,
1675 x_employer_location,
1676 x_employer_postcode,
1677 x_employer_country,
1678 x_job_title,
1679 x_job_duties,
1680 x_job_classification,
1681 x_employer_size,
1682 x_job_duration,
1683 x_job_salary,
1684 x_salary_refused,
1685 x_qualification_requirement,
1686 x_qualification_importance,
1687 x_job_reason1,
1688 x_job_reason2,
1689 x_job_reason3,
1690 x_job_reason4,
1691 x_job_reason5,
1692 x_job_reason6,
1693 x_job_reason7,
1694 x_job_reason8,
1695 x_other_job_reason,
1696 x_no_other_job_reason,
1697 x_job_source,
1698 x_other_job_source,
1699 x_no_other_job_source,
1700 x_previous_job,
1701 x_previous_jobtype1,
1702 x_previous_jobtype2,
1703 x_previous_jobtype3,
1704 x_previous_jobtype4,
1705 x_previous_jobtype5,
1706 x_previous_jobtype6,
1707 x_further_study_type,
1708 x_course_name,
1709 x_course_training_subject,
1710 x_research_subject,
1711 x_research_training_subject,
1712 x_further_study_provider,
1713 x_further_study_qualaim,
1714 x_professional_qualification,
1715 x_study_reason1,
1716 x_study_reason2,
1717 x_study_reason3,
1718 x_study_reason4,
1719 x_study_reason5,
1720 x_study_reason6,
1721 x_study_reason7,
1722 x_other_study_reason,
1723 x_no_other_study_reason,
1724 x_employer_sponsored,
1725 x_funding_source,
1726 x_teacher_teaching,
1727 x_teacher_seeking,
1728 x_teaching_sector,
1729 x_teaching_level,
1730 x_reason_for_ptcourse,
1731 x_job_while_studying,
1732 x_employer_support1,
1733 x_employer_support2,
1734 x_employer_support3,
1735 x_employer_support4,
1736 x_employer_support5,
1737 x_popdlhe_flag,
1738 x_mode
1739 );
1740 RETURN;
1741 END IF;
1742 CLOSE c1;
1743
1744 update_row (
1745 x_rowid,
1746 x_person_id,
1747 x_submission_name,
1748 x_user_return_subclass,
1749 x_return_name,
1750 x_qual_period_code,
1751 x_dlhe_record_status,
1752 x_participant_source,
1753 x_date_status_changed,
1754 x_validation_status,
1755 x_admin_coding,
1756 x_survey_method,
1757 x_employment,
1758 x_further_study,
1759 x_qualified_teacher,
1760 x_pt_study,
1761 x_employer_business,
1762 x_employer_name,
1763 x_employer_classification,
1764 x_employer_location,
1765 x_employer_postcode,
1766 x_employer_country,
1767 x_job_title,
1768 x_job_duties,
1769 x_job_classification,
1770 x_employer_size,
1771 x_job_duration,
1772 x_job_salary,
1773 x_salary_refused,
1774 x_qualification_requirement,
1775 x_qualification_importance,
1776 x_job_reason1,
1777 x_job_reason2,
1778 x_job_reason3,
1779 x_job_reason4,
1780 x_job_reason5,
1781 x_job_reason6,
1782 x_job_reason7,
1783 x_job_reason8,
1784 x_other_job_reason,
1785 x_no_other_job_reason,
1786 x_job_source,
1787 x_other_job_source,
1788 x_no_other_job_source,
1789 x_previous_job,
1790 x_previous_jobtype1,
1791 x_previous_jobtype2,
1792 x_previous_jobtype3,
1793 x_previous_jobtype4,
1794 x_previous_jobtype5,
1795 x_previous_jobtype6,
1796 x_further_study_type,
1797 x_course_name,
1798 x_course_training_subject,
1799 x_research_subject,
1800 x_research_training_subject,
1801 x_further_study_provider,
1802 x_further_study_qualaim,
1803 x_professional_qualification,
1804 x_study_reason1,
1805 x_study_reason2,
1806 x_study_reason3,
1807 x_study_reason4,
1808 x_study_reason5,
1809 x_study_reason6,
1810 x_study_reason7,
1811 x_other_study_reason,
1812 x_no_other_study_reason,
1813 x_employer_sponsored,
1814 x_funding_source,
1815 x_teacher_teaching,
1816 x_teacher_seeking,
1817 x_teaching_sector,
1818 x_teaching_level,
1819 x_reason_for_ptcourse,
1820 x_job_while_studying,
1821 x_employer_support1,
1822 x_employer_support2,
1823 x_employer_support3,
1824 x_employer_support4,
1825 x_employer_support5,
1826 x_popdlhe_flag,
1827 x_mode
1828 );
1829
1830 END add_row;
1831
1832
1833 PROCEDURE delete_row (
1834 x_rowid IN VARCHAR2
1835 ) AS
1836 /*
1837 || Created By : [email protected]
1838 || Created On : 17-APR-2003
1839 || Purpose : Handles the DELETE DML logic for the table.
1840 || Known limitations, enhancements or remarks :
1841 || Change History :
1842 || Who When What
1843 || (reverse chronological order - newest change first)
1844 */
1845 BEGIN
1846
1847 before_dml (
1848 p_action => 'DELETE',
1849 x_rowid => x_rowid
1850 );
1851
1852 DELETE FROM igs_he_stdnt_dlhe
1853 WHERE rowid = x_rowid;
1854
1855 IF (SQL%NOTFOUND) THEN
1856 RAISE NO_DATA_FOUND;
1857 END IF;
1858
1859 END delete_row;
1860
1861
1862 END igs_he_stdnt_dlhe_pkg;