[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
updates or inserts corresponding records in igs_he_stdnt_dlhe table
Known limitations,enhancements,remarks:
Change History
Who When What
anwest 18-JAN-2006 Bug# 4950285 R12 Disable OSS Mandate
*******************************************************************/
-- Type defs
TYPE c_popdlhe_typ IS REF CURSOR;
SELECT dlhe.rowid, dlhe.*
FROM igs_he_stdnt_dlhe dlhe
WHERE person_id = cp_person_id
AND submission_name = p_submission_name
AND return_name = p_return_name;
SELECT qual_period_type, qual_period_desc, user_return_subclass, closed_ind
FROM igs_he_sub_rtn_qual qual
WHERE qual.submission_name = cp_submission_name
AND qual.return_name = cp_return_name
AND qual.qual_period_code = cp_qual_period;
l_sql_stmt := 'SELECT popdlhe_id, husid, ownstu, xqmode01, ttcid ' ||
'FROM igs_he_popdlhe_ints ' ||
'WHERE popdlhe = ''1'' ' ||
'AND census = :1' ;
l_sql_stmt := 'SELECT popdlhe_id, husid, ownstu, xqmode01, ttcid ' ||
'FROM igs_he_popdlhe_ints ' ||
'WHERE rcident IN (''1'',''2'',''3'',''4'',''5'',''6'',''7'') ';
igs_he_stdnt_dlhe_pkg.update_row(
x_rowid => l_dlhe_rec.rowid,
x_person_id => l_dlhe_rec.person_id,
x_submission_name => l_dlhe_rec.submission_name,
x_user_return_subclass => l_dlhe_rec.user_return_subclass ,
x_return_name => l_dlhe_rec.return_name,
x_qual_period_code => l_qual_period,
x_dlhe_record_status => l_dlhe_rec.dlhe_record_status,
x_participant_source => l_dlhe_rec.participant_source,
x_date_status_changed => l_dlhe_rec.date_status_changed,
x_validation_status => l_dlhe_rec.validation_status,
x_admin_coding => l_dlhe_rec.admin_coding,
x_survey_method => l_dlhe_rec.survey_method,
x_employment => l_dlhe_rec.employment,
x_further_study => l_dlhe_rec.further_study,
x_qualified_teacher => l_dlhe_rec.qualified_teacher,
x_pt_study => l_dlhe_rec.pt_study,
x_employer_business => l_dlhe_rec.employer_business,
x_employer_name => l_dlhe_rec.employer_name,
x_employer_classification => l_dlhe_rec.employer_classification,
x_employer_location => l_dlhe_rec.employer_location,
x_employer_postcode => l_dlhe_rec.employer_postcode,
x_employer_country => l_dlhe_rec.employer_country,
x_job_title => l_dlhe_rec.job_title,
x_job_duties => l_dlhe_rec.job_duties,
x_job_classification => l_dlhe_rec.job_classification,
x_employer_size => l_dlhe_rec.employer_size,
x_job_duration => l_dlhe_rec.job_duration,
x_job_salary => l_dlhe_rec.job_salary,
x_salary_refused => l_dlhe_rec.salary_refused,
x_qualification_requirement => l_dlhe_rec.qualification_requirement,
x_qualification_importance => l_dlhe_rec.qualification_importance,
x_job_reason1 => l_dlhe_rec.job_reason1,
x_job_reason2 => l_dlhe_rec.job_reason2,
x_job_reason3 => l_dlhe_rec.job_reason3,
x_job_reason4 => l_dlhe_rec.job_reason4,
x_job_reason5 => l_dlhe_rec.job_reason5,
x_job_reason6 => l_dlhe_rec.job_reason6,
x_job_reason7 => l_dlhe_rec.job_reason7,
x_job_reason8 => l_dlhe_rec.job_reason8,
x_other_job_reason => l_dlhe_rec.other_job_reason,
x_no_other_job_reason => l_dlhe_rec.no_other_job_reason,
x_job_source => l_dlhe_rec.job_source,
x_other_job_source => l_dlhe_rec.other_job_source,
x_no_other_job_source => l_dlhe_rec.no_other_job_source,
x_previous_job => l_dlhe_rec.previous_job,
x_previous_jobtype1 => l_dlhe_rec.previous_jobtype1,
x_previous_jobtype2 => l_dlhe_rec.previous_jobtype2,
x_previous_jobtype3 => l_dlhe_rec.previous_jobtype3,
x_previous_jobtype4 => l_dlhe_rec.previous_jobtype4,
x_previous_jobtype5 => l_dlhe_rec.previous_jobtype5,
x_previous_jobtype6 => l_dlhe_rec.previous_jobtype6,
x_further_study_type => l_dlhe_rec.further_study_type,
x_course_name => l_dlhe_rec.course_name,
x_course_training_subject => l_dlhe_rec.course_training_subject,
x_research_subject => l_dlhe_rec.research_subject,
x_research_training_subject => l_dlhe_rec.research_training_subject,
x_further_study_provider => l_dlhe_rec.further_study_provider,
x_further_study_qualaim => l_dlhe_rec.further_study_qualaim,
x_professional_qualification=> l_dlhe_rec.professional_qualification,
x_study_reason1 => l_dlhe_rec.study_reason1,
x_study_reason2 => l_dlhe_rec.study_reason2,
x_study_reason3 => l_dlhe_rec.study_reason3,
x_study_reason4 => l_dlhe_rec.study_reason4,
x_study_reason5 => l_dlhe_rec.study_reason5,
x_study_reason6 => l_dlhe_rec.study_reason6,
x_study_reason7 => l_dlhe_rec.study_reason7,
x_other_study_reason => l_dlhe_rec.other_study_reason,
x_no_other_study_reason => l_dlhe_rec.no_other_study_reason,
x_employer_sponsored => l_dlhe_rec.employer_sponsored,
x_funding_source => l_dlhe_rec.funding_source,
x_teacher_teaching => l_dlhe_rec.teacher_teaching,
x_teacher_seeking => l_dlhe_rec.teacher_seeking,
x_teaching_sector => l_dlhe_rec.teaching_sector,
x_teaching_level => l_dlhe_rec.teaching_level,
x_reason_for_ptcourse => l_dlhe_rec.reason_for_ptcourse,
x_job_while_studying => l_dlhe_rec.job_while_studying,
x_employer_support1 => l_dlhe_rec.employer_support1,
x_employer_support2 => l_dlhe_rec.employer_support2,
x_employer_support3 => l_dlhe_rec.employer_support3,
x_employer_support4 => l_dlhe_rec.employer_support4,
x_employer_support5 => l_dlhe_rec.employer_support5,
x_popdlhe_flag => 'Y'
);
igs_he_stdnt_dlhe_pkg.insert_row(
x_rowid => l_rowid,
x_person_id => l_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 => 'P',
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 => 'Y'
);
SELECT party_id
FROM hz_parties
WHERE party_number = cp_ownstu;
SELECT pe_person_id
FROM igs_pe_alt_pers_id
WHERE api_person_id = cp_husid
AND person_id_type= 'HUSID'
AND (end_dt IS NULL OR start_dt <> end_dt)
ORDER BY start_dt DESC;
fnd_message.set_name('IGS','IGS_HE_DLHE_REC_UPDATED');
fnd_message.set_token('UPDATED_DLHE', p_upd_dlhe_cnt);