[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT qual.qual_period_code,
qual.qual_period_desc,
qual.qual_period_type,
qual.qual_period_start_date,
qual.qual_period_end_date,
qual.closed_ind
FROM igs_he_sub_rtn_qual qual,
igs_he_usr_rtn_clas urc
WHERE qual.submission_name = cp_submission_name
AND qual.return_name = cp_return_name
AND qual.qual_period_code = NVL(cp_qual_period, qual.qual_period_code)
AND qual.qual_period_type IN ('L','R')
AND qual.user_return_subclass = urc.user_return_subclass
AND urc.system_return_class_type = 'DLHE'
ORDER BY qual.closed_ind ASC;
SELECT rowid
FROM igs_he_stdnt_dlhe
WHERE submission_name = cp_submission_name
AND return_name = cp_return_name
AND qual_period_code = cp_qual_period
AND dlhe_record_status = 'NST';
SELECT hst.person_id,
hst.course_cd,
sca.version_number
FROM igs_en_stdnt_ps_att sca,
igs_he_st_spa hst,
igs_he_st_prog_all hpg
WHERE sca.person_id = hst.person_id
AND sca.course_cd = hst.course_cd
AND sca.course_cd = hpg.course_cd
AND sca.version_number = hpg.version_number
AND NVL(hpg.exclude_flag, 'N') = 'N'
AND NVL(hst.exclude_flag, 'N') = 'N'
AND ((sca.discontinued_dt BETWEEN cp_qual_start_dt AND cp_qual_end_dt)
OR (sca.course_rqrmnts_complete_dt BETWEEN cp_qual_start_dt AND cp_qual_end_dt))
ORDER BY hst.person_id;
SELECT hst.person_id,
hst.course_cd,
sca.version_number,
hst.student_inst_number,
hst.commencement_dt hst_commencement_dt,
sca.commencement_dt sca_commencement_dt
FROM igs_en_stdnt_ps_att sca,
igs_he_st_spa hst,
igs_he_st_prog_all hpg,
igs_ps_ver pv,
igs_ps_type pt
WHERE hst.person_id = sca.person_id
AND hst.course_cd = sca.course_cd
AND sca.course_cd = hpg.course_cd
AND sca.version_number = hpg.version_number
AND sca.course_cd = pv.course_cd
AND sca.version_number = pv.version_number
AND pt.course_type = pv.course_type
AND pt.research_type_ind = 'Y'
AND NVL(hpg.exclude_flag, 'N') = 'N'
AND NVL(hst.exclude_flag, 'N') = 'N'
ORDER BY hst.person_id;
l_upd_dlhe_cnt NUMBER; -- Holds total student DLHE records updated
l_not_mod_cnt NUMBER; -- Holds the total number of students not required to update/create student DLHE
igs_he_stdnt_dlhe_pkg.delete_row(x_rowid => cur_closed_dlhe_rec.rowid);
ELSIF l_cre_upd_dlhe = 'U' THEN -- l_cre_upd_dlhe returned value U means student DLHE record updated
l_upd_dlhe_cnt := l_upd_dlhe_cnt + 1;
ELSIF l_cre_upd_dlhe = 'U' THEN -- l_cre_upd_dlhe returned value U means student DLHE record updated
l_upd_dlhe_cnt := l_upd_dlhe_cnt + 1;
ELSIF l_cre_upd_dlhe = 'U' THEN -- l_cre_upd_dlhe returned value U means student DLHE record updated
l_upd_dlhe_cnt := l_upd_dlhe_cnt + 1;
ELSIF l_cre_upd_dlhe = 'U' THEN -- l_cre_upd_dlhe returned value U means student DLHE record updated
l_upd_dlhe_cnt := l_upd_dlhe_cnt + 1;
fnd_message.set_name('IGS','IGS_HE_DLHE_REC_UPDATED');
fnd_message.set_token('UPDATED_DLHE', l_upd_dlhe_cnt);
Purpose : For successfull student create/Update the student DLHE table
in this procedure
Known limitations,enhancements,remarks:
Change History
Who When What
*******************************************************************/
-- Cursor to get the Qualification details for validations and update the student dlhe table.
CURSOR cur_qual_dets (cp_submission_name igs_he_sub_rtn_qual.submission_name%TYPE,
cp_return_name igs_he_sub_rtn_qual.return_name%TYPE,
cp_qual_period_code igs_he_sub_rtn_qual.qual_period_code%TYPE) IS
SELECT qual.qual_period_start_date,
qual.qual_period_end_date,
qual.user_return_subclass,
qual.closed_ind
FROM igs_he_sub_rtn_qual qual,
igs_he_usr_rtn_clas urc
WHERE qual.submission_name = cp_submission_name
AND qual.return_name = cp_return_name
AND qual.qual_period_code = cp_qual_period_code
AND qual.user_return_subclass = urc.user_return_subclass
AND urc.system_return_class_type = 'DLHE';
SELECT sdlhe.rowid,
sdlhe.*
FROM igs_he_stdnt_dlhe sdlhe
WHERE sdlhe.submission_name = cp_submission_name
AND sdlhe.return_name = cp_return_name
AND sdlhe.person_id = cp_person_id;
igs_he_stdnt_dlhe_pkg.insert_row(
x_rowid => l_rowid,
x_person_id => p_person_id,
x_submission_name => p_submission_name,
x_user_return_subclass => l_qual_dets.user_return_subclass,
x_return_name => p_return_name,
x_qual_period_code => p_qual_period,
x_dlhe_record_status => 'NST',
x_participant_source => 'I',
x_date_status_changed => NULL,
x_validation_status => NULL,
x_admin_coding => NULL,
x_survey_method => NULL,
x_employment => NULL,
x_further_study => NULL,
x_qualified_teacher => l_qualified_teacher,
x_pt_study => l_pt_study,
x_employer_business => NULL,
x_employer_name => NULL,
x_employer_classification => NULL,
x_employer_location => NULL,
x_employer_postcode => NULL,
x_employer_country => NULL,
x_job_title => NULL,
x_job_duties => NULL,
x_job_classification => NULL,
x_employer_size => NULL,
x_job_duration => NULL,
x_job_salary => NULL,
x_salary_refused => 'N',
x_qualification_requirement => NULL,
x_qualification_importance => NULL,
x_job_reason1 => 'N',
x_job_reason2 => 'N',
x_job_reason3 => 'N',
x_job_reason4 => 'N',
x_job_reason5 => 'N',
x_job_reason6 => 'N',
x_job_reason7 => 'N',
x_job_reason8 => 'N',
x_other_job_reason => NULL,
x_no_other_job_reason => 'N',
x_job_source => NULL,
x_other_job_source => NULL,
x_no_other_job_source => 'N',
x_previous_job => NULL,
x_previous_jobtype1 => 'N',
x_previous_jobtype2 => 'N',
x_previous_jobtype3 => 'N',
x_previous_jobtype4 => 'N',
x_previous_jobtype5 => 'N',
x_previous_jobtype6 => 'N',
x_further_study_type => NULL,
x_course_name => NULL,
x_course_training_subject => NULL,
x_research_subject => NULL,
x_research_training_subject => NULL,
x_further_study_provider => NULL,
x_further_study_qualaim => NULL,
x_professional_qualification => NULL,
x_study_reason1 => NULL,
x_study_reason2 => 'N',
x_study_reason3 => 'N',
x_study_reason4 => 'N',
x_study_reason5 => 'N',
x_study_reason6 => 'N',
x_study_reason7 => 'N',
x_other_study_reason => NULL,
x_no_other_study_reason => 'N',
x_employer_sponsored => 'N',
x_funding_source => NULL,
x_teacher_teaching => 'N',
x_teacher_seeking => 'N',
x_teaching_sector => NULL,
x_teaching_level => NULL,
x_reason_for_ptcourse => NULL,
x_job_while_studying => 'N',
x_employer_support1 => 'N',
x_employer_support2 => 'N',
x_employer_support3 => 'N',
x_employer_support4 => 'N',
x_employer_support5 => 'N',
x_popdlhe_flag => 'N'
);
igs_he_stdnt_dlhe_pkg.update_row(
x_rowid => l_stdnt_dlhe_rec.rowid,
x_person_id => l_stdnt_dlhe_rec.person_id,
x_submission_name => l_stdnt_dlhe_rec.submission_name,
x_user_return_subclass => l_stdnt_dlhe_rec.user_return_subclass ,
x_return_name => l_stdnt_dlhe_rec.return_name,
x_qual_period_code => p_qual_period,
x_dlhe_record_status => l_stdnt_dlhe_rec.dlhe_record_status,
x_participant_source => l_stdnt_dlhe_rec.participant_source,
x_date_status_changed => l_stdnt_dlhe_rec.date_status_changed,
x_validation_status => l_stdnt_dlhe_rec.validation_status,
x_admin_coding => l_stdnt_dlhe_rec.admin_coding,
x_survey_method => l_stdnt_dlhe_rec.survey_method,
x_employment => l_stdnt_dlhe_rec.employment,
x_further_study => l_stdnt_dlhe_rec.further_study,
x_qualified_teacher => l_stdnt_dlhe_rec.qualified_teacher,
x_pt_study => l_stdnt_dlhe_rec.pt_study,
x_employer_business => l_stdnt_dlhe_rec.employer_business,
x_employer_name => l_stdnt_dlhe_rec.employer_name,
x_employer_classification => l_stdnt_dlhe_rec.employer_classification,
x_employer_location => l_stdnt_dlhe_rec.employer_location,
x_employer_postcode => l_stdnt_dlhe_rec.employer_postcode,
x_employer_country => l_stdnt_dlhe_rec.employer_country,
x_job_title => l_stdnt_dlhe_rec.job_title,
x_job_duties => l_stdnt_dlhe_rec.job_duties,
x_job_classification => l_stdnt_dlhe_rec.job_classification,
x_employer_size => l_stdnt_dlhe_rec.employer_size,
x_job_duration => l_stdnt_dlhe_rec.job_duration,
x_job_salary => l_stdnt_dlhe_rec.job_salary,
x_salary_refused => l_stdnt_dlhe_rec.salary_refused,
x_qualification_requirement => l_stdnt_dlhe_rec.qualification_requirement,
x_qualification_importance => l_stdnt_dlhe_rec.qualification_importance,
x_job_reason1 => l_stdnt_dlhe_rec.job_reason1,
x_job_reason2 => l_stdnt_dlhe_rec.job_reason2,
x_job_reason3 => l_stdnt_dlhe_rec.job_reason3,
x_job_reason4 => l_stdnt_dlhe_rec.job_reason4,
x_job_reason5 => l_stdnt_dlhe_rec.job_reason5,
x_job_reason6 => l_stdnt_dlhe_rec.job_reason6,
x_job_reason7 => l_stdnt_dlhe_rec.job_reason7,
x_job_reason8 => l_stdnt_dlhe_rec.job_reason8,
x_other_job_reason => l_stdnt_dlhe_rec.other_job_reason,
x_no_other_job_reason => l_stdnt_dlhe_rec.no_other_job_reason,
x_job_source => l_stdnt_dlhe_rec.job_source,
x_other_job_source => l_stdnt_dlhe_rec.other_job_source,
x_no_other_job_source => l_stdnt_dlhe_rec.no_other_job_source,
x_previous_job => l_stdnt_dlhe_rec.previous_job,
x_previous_jobtype1 => l_stdnt_dlhe_rec.previous_jobtype1,
x_previous_jobtype2 => l_stdnt_dlhe_rec.previous_jobtype2,
x_previous_jobtype3 => l_stdnt_dlhe_rec.previous_jobtype3,
x_previous_jobtype4 => l_stdnt_dlhe_rec.previous_jobtype4,
x_previous_jobtype5 => l_stdnt_dlhe_rec.previous_jobtype5,
x_previous_jobtype6 => l_stdnt_dlhe_rec.previous_jobtype6,
x_further_study_type => l_stdnt_dlhe_rec.further_study_type,
x_course_name => l_stdnt_dlhe_rec.course_name,
x_course_training_subject => l_stdnt_dlhe_rec.course_training_subject,
x_research_subject => l_stdnt_dlhe_rec.research_subject,
x_research_training_subject => l_stdnt_dlhe_rec.research_training_subject,
x_further_study_provider => l_stdnt_dlhe_rec.further_study_provider,
x_further_study_qualaim => l_stdnt_dlhe_rec.further_study_qualaim,
x_professional_qualification=> l_stdnt_dlhe_rec.professional_qualification,
x_study_reason1 => l_stdnt_dlhe_rec.study_reason1,
x_study_reason2 => l_stdnt_dlhe_rec.study_reason2,
x_study_reason3 => l_stdnt_dlhe_rec.study_reason3,
x_study_reason4 => l_stdnt_dlhe_rec.study_reason4,
x_study_reason5 => l_stdnt_dlhe_rec.study_reason5,
x_study_reason6 => l_stdnt_dlhe_rec.study_reason6,
x_study_reason7 => l_stdnt_dlhe_rec.study_reason7,
x_other_study_reason => l_stdnt_dlhe_rec.other_study_reason,
x_no_other_study_reason => l_stdnt_dlhe_rec.no_other_study_reason,
x_employer_sponsored => l_stdnt_dlhe_rec.employer_sponsored,
x_funding_source => l_stdnt_dlhe_rec.funding_source,
x_teacher_teaching => l_stdnt_dlhe_rec.teacher_teaching,
x_teacher_seeking => l_stdnt_dlhe_rec.teacher_seeking,
x_teaching_sector => l_stdnt_dlhe_rec.teaching_sector,
x_teaching_level => l_stdnt_dlhe_rec.teaching_level,
x_reason_for_ptcourse => l_stdnt_dlhe_rec.reason_for_ptcourse,
x_job_while_studying => l_stdnt_dlhe_rec.job_while_studying,
x_employer_support1 => l_stdnt_dlhe_rec.employer_support1,
x_employer_support2 => l_stdnt_dlhe_rec.employer_support2,
x_employer_support3 => l_stdnt_dlhe_rec.employer_support3,
x_employer_support4 => l_stdnt_dlhe_rec.employer_support4,
x_employer_support5 => l_stdnt_dlhe_rec.employer_support5,
x_popdlhe_flag => l_stdnt_dlhe_rec.popdlhe_flag
);
SELECT enrolment_start_date,
enrolment_end_date
FROM igs_he_submsn_header
WHERE submission_name = cp_submission_name ;
SELECT location_of_study
FROM igs_he_st_prog
WHERE course_cd = cp_course_cd
AND version_number = cp_version_number;
SELECT DISTINCT susa.unit_set_cd,
susa.us_version_number,
susa.sequence_number,
susa.rqrmnts_complete_dt,
susa.selection_dt,
susa.end_dt,
husa.study_mode,
husa.study_location ,
husa.student_fee
FROM igs_as_su_setatmpt susa,
igs_he_en_susa husa,
igs_en_unit_set us,
igs_en_unit_set_cat susc,
igs_en_stdnt_ps_att sca
WHERE susa.person_id = sca.person_id
AND susa.course_cd = sca.course_cd
AND susa.person_id = cp_person_id
AND susa.course_cd = cp_course_cd
AND susa.unit_set_cd = husa.unit_set_cd
AND susa.us_version_number = husa.us_version_number
AND susa.person_id = husa.person_id
AND susa.course_cd = husa.course_cd
AND susa.sequence_number = husa.sequence_number
AND susa.unit_set_cd = us.unit_set_cd
AND susa.us_version_number = us.version_number
AND us.unit_set_cat = susc.unit_set_cat
AND susc.s_unit_set_cat = 'PRENRL_YR'
AND (susa.selection_dt <= cp_enrl_end_dt AND
(susa.end_dt IS NULL OR susa.end_dt >= cp_enrl_start_dt ) AND
(susa.rqrmnts_complete_dt IS NULL OR susa.rqrmnts_complete_dt >= cp_enrl_start_dt))
ORDER BY susa.rqrmnts_complete_dt DESC, susa.end_dt DESC, susa.selection_dt DESC;
SELECT sca.version_number,
sca.cal_type,
sca.location_cd ,
sca.attendance_mode,
sca.attendance_type,
sca.commencement_dt sca_commencement_dt,
sca.discontinued_dt,
sca.discontinuation_reason_cd,
sca.course_rqrmnt_complete_ind,
sca.course_rqrmnts_complete_dt,
sca.adm_admission_appl_number,
sca.adm_nominated_course_cd,
sca.adm_sequence_number,
hspa.domicile_cd,
hspa.commencement_dt hspa_commencement_dt,
hspa.special_student,
hspa.student_qual_aim,
hspa.student_inst_number
FROM igs_en_stdnt_ps_att sca,
igs_he_st_spa hspa
WHERE sca.person_id = cp_person_id
AND sca.course_cd = cp_course_cd
AND sca.person_id = hspa.person_id
AND sca.course_cd = hspa.course_cd;
SELECT location_of_study,
mode_of_study
FROM igs_he_poous
WHERE course_cd = cp_course_cd
AND crv_version_number = cp_version_number
AND cal_type = cp_cal_type
AND location_cd = cp_location_cd
AND attendance_mode = cp_attendance_mode
AND attendance_type = cp_attendance_type
AND unit_set_cd = cp_unit_set_cd
AND us_version_number = cp_us_version_number;
SELECT domicile_cd,
special_student_cd
FROM igs_he_ad_dtl
WHERE person_id = cp_person_id
AND admission_appl_number = cp_admission_appl_number
AND nominated_course_cd = cp_nominated_course_cd
AND sequence_number = cp_sequence_number;
SELECT person_number
FROM igs_pe_person_base_v
WHERE person_id = cp_person_id;
SELECT tr.program_version , tr.acad_cal_type, tr.location_cd, tr.attendance_mode, tr.attendance_type
FROM igs_en_spa_terms tr , igs_ca_inst_all ca
WHERE tr.term_cal_type = ca.cal_type AND
tr.term_sequence_number = ca.sequence_number AND
tr.person_id = cp_person_id AND
tr.program_cd = cp_course_cd AND
cp_lev_dt BETWEEN ca.start_dt AND ca.end_dt
ORDER BY ca.start_dt DESC;
SELECT tr.program_version , tr.acad_cal_type, tr.location_cd, tr.attendance_mode, tr.attendance_type
FROM igs_en_spa_terms tr , igs_ca_inst_all ca
WHERE tr.term_cal_type = ca.cal_type AND
tr.term_sequence_number = ca.sequence_number AND
tr.person_id = cp_person_id AND
tr.program_cd = cp_course_cd AND
ca.start_dt BETWEEN cp_sub_start_dt AND cp_sub_end_dt
ORDER BY ca.start_dt DESC;