[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
| To Update Admission Completion Status as Satisfied |
| |
| HISTORY |
| Who When What |
| brajendr 9-Aug-2001 Incorporaetd the review comments |
| brajendr 3-Aug-2001 Creation of the Initial Code |
| cdcruz 18-feb-2002 bug 2217104 Admit to future term Enhancement,updated tbh call for
| new columns being added to IGS_AD_PS_APPL_INST
| hreddych 4-apr-2002 bug 2273789 The function get_cmp_apltritm was returning TRUE
| if one of the tracking status was complete which was modified to
| to return TRUE if all are complete
| nshee 29-Aug-2002 Bug 2395510 added 6 columns as part of deferments build
|hreddych 8-jan-2002 #2740404 Added the logmessages for giving details
| of the application Instance.
|hreddych 25-jun-2003 # 2989257 Altered the cursors cur_tr,cur_tr_itm
| of the Function get_cmp_apltritm
| rghosh 21-Oct-2003 Added the REF CURSOR c_dyn_pig_check and hence the
| logic for supporting dynamic Person ID Group
| (Enh# 3194295 , ADCR043: Person ID Group)
|rbezawad 1-Nov-04 Modified get_cpti_apcmp procedure to display the security error
| message in the log file w.r.t. Bug 3919112.
|apadegal 7-Aug-06 5450345 - Removed the commit statement, as it un-necessarily commits the transaction.
*=======================================================================*/
-- Declare all Global variables and global constants
FUNCTION get_cmp_apltritm(
p_person_id IN igs_ad_ps_appl_inst.person_id%TYPE,
p_admission_appl_number IN igs_ad_ps_appl_inst.admission_appl_number%TYPE,
p_course_cd IN igs_ad_ps_appl_inst.course_cd%TYPE,
p_sequence_number IN igs_ad_ps_appl_inst.sequence_number%TYPE
) RETURN BOOLEAN AS
/*
|| Created By : brajendr
|| Created On :
|| Purpose : This Procedure will return TRUE if all the Tracking Items in the given Application Instance are Complete.
|| Known limitations, enhancements or remarks :
|| Change History :
|| Who When What
|| (reverse chronological order - newest change first)
*/
-- Get all the tracking step details from for each application of the PERSON.
CURSOR cur_tr(
p_person_id IN igs_ad_ps_appl_inst.person_id%TYPE,
p_admission_appl_number IN igs_ad_ps_appl_inst.admission_appl_number%TYPE,
p_course_cd IN igs_ad_ps_appl_inst.course_cd%TYPE,
p_sequence_number IN igs_ad_ps_appl_inst.sequence_number%TYPE
) IS
SELECT ti.tracking_id
FROM igs_ad_aplins_admreq aa,
igs_tr_item ti,
igs_tr_type tt
WHERE aa.person_id = p_person_id
AND aa.admission_appl_number = p_admission_appl_number
AND aa.sequence_number = p_sequence_number
AND aa.course_cd = p_course_cd
AND aa.tracking_id = ti.tracking_id
AND ti.tracking_type = tt.tracking_type
AND tt.s_tracking_type = 'ADM_PROCESSING';
SELECT 'x'
FROM igs_tr_item iti,
igs_tr_status its,
igs_tr_type itt
WHERE iti.tracking_id = p_tracking_id
AND itt.tracking_type = iti.tracking_type
AND itt.s_tracking_type = 'ADM_PROCESSING'
AND its.tracking_status = iti.tracking_status
AND its.s_tracking_status = 'COMPLETE';
|| Purpose : This procedure will update given Application Instance to the COMPLETE all the Tracking Items under this are COMPLTE
|| Known limitations, enhancements or remarks :
|| Change History :
|| Who When What
|| samaresh 02-DEC-2001 Bug # 2097333 : Impact of addition of the waitlist_status field to igs_ad_ps_appl_inst_all
|| (reverse chronological order - newest change first)
*/
-- Get the details of
CURSOR cur_adm_doc_status IS
SELECT adm_doc_status
FROM igs_ad_doc_stat
WHERE s_adm_doc_status = 'SATISFIED'
AND closed_ind = 'N'
AND system_default_ind= 'Y';
SELECT *
FROM igs_ad_ps_appl_inst ain
WHERE ain.person_id = p_person_id
AND ain.admission_appl_number = p_admission_appl_number
AND ain.course_cd = p_course_cd
AND ain.sequence_number = p_sequence_number;
igs_ad_ps_appl_inst_pkg.update_row(
-- cur_adm_doc_status_rec.adm_doc_status
x_rowid => cur_apcmp_rec.row_id,
x_person_id => cur_apcmp_rec.person_id,
x_admission_appl_number => cur_apcmp_rec.admission_appl_number,
x_nominated_course_cd => cur_apcmp_rec.nominated_course_cd,
x_sequence_number => cur_apcmp_rec.sequence_number,
x_predicted_gpa => cur_apcmp_rec.predicted_gpa,
x_academic_index => cur_apcmp_rec.academic_index,
x_adm_cal_type => cur_apcmp_rec.adm_cal_type,
x_app_file_location => cur_apcmp_rec.app_file_location,
x_adm_ci_sequence_number => cur_apcmp_rec.adm_ci_sequence_number,
x_course_cd => cur_apcmp_rec.course_cd,
x_app_source_id => cur_apcmp_rec.app_source_id,
x_crv_version_number => cur_apcmp_rec.crv_version_number,
x_waitlist_rank => cur_apcmp_rec.waitlist_rank,
x_waitlist_status => cur_apcmp_rec.waitlist_status,
x_location_cd => cur_apcmp_rec.location_cd,
x_attent_other_inst_cd => cur_apcmp_rec.attent_other_inst_cd,
x_attendance_mode => cur_apcmp_rec.attendance_mode,
x_edu_goal_prior_enroll_id => cur_apcmp_rec.edu_goal_prior_enroll_id,
x_attendance_type => cur_apcmp_rec.attendance_type,
x_decision_make_id => cur_apcmp_rec.decision_make_id,
x_unit_set_cd => cur_apcmp_rec.unit_set_cd,
x_decision_date => cur_apcmp_rec.decision_date,
x_attribute_category => cur_apcmp_rec.attribute_category,
x_attribute1 => cur_apcmp_rec.attribute1,
x_attribute2 => cur_apcmp_rec.attribute2,
x_attribute3 => cur_apcmp_rec.attribute3,
x_attribute4 => cur_apcmp_rec.attribute4,
x_attribute5 => cur_apcmp_rec.attribute5,
x_attribute6 => cur_apcmp_rec.attribute6,
x_attribute7 => cur_apcmp_rec.attribute7,
x_attribute8 => cur_apcmp_rec.attribute8,
x_attribute9 => cur_apcmp_rec.attribute9,
x_attribute10 => cur_apcmp_rec.attribute10,
x_attribute11 => cur_apcmp_rec.attribute11,
x_attribute12 => cur_apcmp_rec.attribute12,
x_attribute13 => cur_apcmp_rec.attribute13,
x_attribute14 => cur_apcmp_rec.attribute14,
x_attribute15 => cur_apcmp_rec.attribute15,
x_attribute16 => cur_apcmp_rec.attribute16,
x_attribute17 => cur_apcmp_rec.attribute17,
x_attribute18 => cur_apcmp_rec.attribute18,
x_attribute19 => cur_apcmp_rec.attribute19,
x_attribute20 => cur_apcmp_rec.attribute20,
x_decision_reason_id => cur_apcmp_rec.decision_reason_id,
x_us_version_number => cur_apcmp_rec.us_version_number,
x_decision_notes => cur_apcmp_rec.decision_notes,
x_pending_reason_id => cur_apcmp_rec.pending_reason_id,
x_preference_number => cur_apcmp_rec.preference_number,
x_adm_doc_status => cur_adm_doc_status_rec.adm_doc_status,
x_adm_entry_qual_status => cur_apcmp_rec.adm_entry_qual_status,
x_deficiency_in_prep => cur_apcmp_rec.deficiency_in_prep,
x_late_adm_fee_status => cur_apcmp_rec.late_adm_fee_status,
x_spl_consider_comments => cur_apcmp_rec.spl_consider_comments,
x_apply_for_finaid => cur_apcmp_rec.apply_for_finaid,
x_finaid_apply_date => cur_apcmp_rec.finaid_apply_date,
x_adm_outcome_status => cur_apcmp_rec.adm_outcome_status,
x_adm_otcm_stat_auth_per_id => cur_apcmp_rec.adm_otcm_status_auth_person_id,
x_adm_outcome_status_auth_dt => cur_apcmp_rec.adm_outcome_status_auth_dt,
x_adm_outcome_status_reason => cur_apcmp_rec.adm_outcome_status_reason,
x_offer_dt => cur_apcmp_rec.offer_dt,
x_offer_response_dt => cur_apcmp_rec.offer_response_dt,
x_prpsd_commencement_dt => cur_apcmp_rec.prpsd_commencement_dt,
x_adm_cndtnl_offer_status => cur_apcmp_rec.adm_cndtnl_offer_status,
x_cndtnl_offer_satisfied_dt => cur_apcmp_rec.cndtnl_offer_satisfied_dt,
x_cndnl_ofr_must_be_stsfd_ind => cur_apcmp_rec.cndtnl_offer_must_be_stsfd_ind,
x_adm_offer_resp_status => cur_apcmp_rec.adm_offer_resp_status,
x_actual_response_dt => cur_apcmp_rec.actual_response_dt,
x_adm_offer_dfrmnt_status => cur_apcmp_rec.adm_offer_dfrmnt_status,
x_deferred_adm_cal_type => cur_apcmp_rec.deferred_adm_cal_type,
x_deferred_adm_ci_sequence_num => cur_apcmp_rec.deferred_adm_ci_sequence_num,
x_deferred_tracking_id => cur_apcmp_rec.deferred_tracking_id,
x_ass_rank => cur_apcmp_rec.ass_rank,
x_secondary_ass_rank => cur_apcmp_rec.secondary_ass_rank,
x_intr_accept_advice_num => cur_apcmp_rec.intrntnl_acceptance_advice_num,
x_ass_tracking_id => cur_apcmp_rec.ass_tracking_id,
x_fee_cat => cur_apcmp_rec.fee_cat,
x_hecs_payment_option => cur_apcmp_rec.hecs_payment_option,
x_expected_completion_yr => cur_apcmp_rec.expected_completion_yr,
x_expected_completion_perd => cur_apcmp_rec.expected_completion_perd,
x_correspondence_cat => cur_apcmp_rec.correspondence_cat,
x_enrolment_cat => cur_apcmp_rec.enrolment_cat,
x_funding_source => cur_apcmp_rec.funding_source,
x_applicant_acptnce_cndtn => cur_apcmp_rec.applicant_acptnce_cndtn,
x_cndtnl_offer_cndtn => cur_apcmp_rec.cndtnl_offer_cndtn,
x_ss_application_id => cur_apcmp_rec.ss_application_id,
x_ss_pwd => cur_apcmp_rec.ss_pwd,
x_authorized_dt => cur_apcmp_rec.authorized_dt,
x_authorizing_pers_id => cur_apcmp_rec.authorizing_pers_id,
x_entry_status => cur_apcmp_rec.entry_status,
x_entry_level => cur_apcmp_rec.entry_level,
x_sch_apl_to_id => cur_apcmp_rec.sch_apl_to_id ,
x_idx_calc_date => cur_apcmp_rec.idx_calc_date ,
X_FUT_ACAD_CAL_TYPE => cur_apcmp_rec.FUTURE_ACAD_CAL_TYPE, -- Bug # 2217104
X_FUT_ACAD_CI_SEQUENCE_NUMBER => cur_apcmp_rec.FUTURE_ACAD_CI_SEQUENCE_NUMBER,-- Bug # 2217104
X_FUT_ADM_CAL_TYPE => cur_apcmp_rec.FUTURE_ADM_CAL_TYPE, -- Bug # 2217104
X_FUT_ADM_CI_SEQUENCE_NUMBER => cur_apcmp_rec.FUTURE_ADM_CI_SEQUENCE_NUMBER, -- Bug # 2217104
X_PREV_TERM_ADM_APPL_NUMBER => cur_apcmp_rec.PREVIOUS_TERM_ADM_APPL_NUMBER, -- Bug # 2217104
X_PREV_TERM_SEQUENCE_NUMBER => cur_apcmp_rec.PREVIOUS_TERM_SEQUENCE_NUMBER, -- Bug # 2217104
X_FUT_TERM_ADM_APPL_NUMBER => cur_apcmp_rec.FUTURE_TERM_ADM_APPL_NUMBER, -- Bug # 2217104
X_FUT_TERM_SEQUENCE_NUMBER => cur_apcmp_rec.FUTURE_TERM_SEQUENCE_NUMBER, -- Bug # 2217104
X_DEF_ACAD_CAL_TYPE => cur_apcmp_rec.DEF_ACAD_CAL_TYPE, --Bug 2395510
X_DEF_ACAD_CI_SEQUENCE_NUM => cur_apcmp_rec.DEF_ACAD_CI_SEQUENCE_NUM, --Bug 2395510
X_DEF_PREV_TERM_ADM_APPL_NUM => cur_apcmp_rec.DEF_PREV_TERM_ADM_APPL_NUM,--Bug 2395510
X_DEF_PREV_APPL_SEQUENCE_NUM => cur_apcmp_rec.DEF_PREV_APPL_SEQUENCE_NUM,--Bug 2395510
X_DEF_TERM_ADM_APPL_NUM => cur_apcmp_rec.DEF_TERM_ADM_APPL_NUM,--Bug 2395510
X_DEF_APPL_SEQUENCE_NUM => cur_apcmp_rec.DEF_APPL_SEQUENCE_NUM,--Bug 2395510
x_attribute21=> cur_apcmp_rec.attribute21,
x_attribute22=> cur_apcmp_rec.attribute22,
x_attribute23=> cur_apcmp_rec.attribute23,
x_attribute24=> cur_apcmp_rec.attribute24,
x_attribute25=> cur_apcmp_rec.attribute25,
x_attribute26=> cur_apcmp_rec.attribute26,
x_attribute27=> cur_apcmp_rec.attribute27,
x_attribute28=> cur_apcmp_rec.attribute28,
x_attribute29=> cur_apcmp_rec.attribute29,
x_attribute30=> cur_apcmp_rec.attribute30,
x_attribute31=> cur_apcmp_rec.attribute31,
x_attribute32=> cur_apcmp_rec.attribute32,
x_attribute33=> cur_apcmp_rec.attribute33,
x_attribute34=> cur_apcmp_rec.attribute34,
x_attribute35=> cur_apcmp_rec.attribute35,
x_attribute36=> cur_apcmp_rec.attribute36,
x_attribute37=> cur_apcmp_rec.attribute37,
x_attribute38=> cur_apcmp_rec.attribute38,
x_attribute39=> cur_apcmp_rec.attribute39,
x_attribute40=> cur_apcmp_rec.attribute40,
x_appl_inst_status=> cur_apcmp_rec.appl_inst_status,
x_ais_reason=> cur_apcmp_rec.ais_reason,
x_decline_ofr_reason=> cur_apcmp_rec.decline_ofr_reason
);
|| Purpose : This is a main Procedure which will Update the given Application to COMPLETE, if all the Tracking items are COMPLETE.
|| This is getting called as a concurrent Job.
|| Known limitations, enhancements or remarks :
|| Change History :
|| Who When What
|| (reverse chronological order - newest change first)
|| rghosh 21-Oct-2003 Added the REF CURSOR c_dyn_pig_check and hence the
|| logic for supporting dynamic Person ID Group
|| (Enh# 3194295 , ADCR043: Person ID Group)
*/
TYPE c_dyn_pig_checkCurTyp IS REF CURSOR;
SELECT person_id, admission_appl_number, course_cd, sequence_number
FROM igs_ad_ps_appl_inst apai,
igs_ad_ou_stat aos,
igs_ad_doc_stat ads
WHERE apai.person_id = p_person_id
AND apai.admission_appl_number = p_admission_appl_number
AND apai.course_cd = p_course_cd
AND apai.sequence_number = p_sequence_number
AND aos.s_adm_outcome_status IN ('PENDING','COND-OFFER')
AND aos.closed_ind = 'N'
AND apai.adm_outcome_status = aos.adm_outcome_status
AND ads.s_adm_doc_status = 'PENDING'
AND ads.closed_ind = 'N'
AND apai.adm_doc_status = ads.adm_doc_status;
SELECT apai.person_id, apai.admission_appl_number, apai.course_cd, apai.sequence_number
FROM igs_ad_ps_appl_inst apai,
igs_ad_ou_stat aos,
igs_ad_doc_stat ads
WHERE apai.person_id = p_person_id
AND aos.s_adm_outcome_status IN ('PENDING','COND-OFFER')
AND aos.closed_ind = 'N'
AND apai.adm_outcome_status = aos.adm_outcome_status
AND ads.s_adm_doc_status = 'PENDING'
AND ads.closed_ind = 'N'
AND apai.adm_doc_status = ads.adm_doc_status;
SELECT apai.person_id, apai.admission_appl_number, apai. course_cd, apai.sequence_number
FROM igs_ad_ps_appl_inst apai,
igs_ad_appl aa,
igs_ad_ou_stat aos,
igs_ad_doc_stat ads
WHERE apai.person_id = aa.person_id
AND apai.admission_appl_number = aa.admission_appl_number
AND aa.acad_cal_type = p_acad_cal_type
AND aa.acad_ci_sequence_number = p_acad_ci_sequence_number
AND aa.adm_cal_type = p_adm_cal_type
AND aa.adm_ci_sequence_number = p_adm_ci_sequence_number
AND aa.admission_cat = p_admission_cat
AND aa.s_admission_process_type = p_s_admission_process_type
AND aos.s_adm_outcome_status IN ('PENDING','COND-OFFER')
AND aos.closed_ind = 'N'
AND apai.adm_outcome_status = aos.adm_outcome_status
AND ads.s_adm_doc_status = 'PENDING'
AND ads.closed_ind = 'N'
AND apai.adm_doc_status = ads.adm_doc_status;
'SELECT apai.person_id, apai.admission_appl_number, apai.course_cd, apai.sequence_number
FROM igs_ad_ps_appl_inst apai,
igs_ad_ou_stat aos,
igs_ad_doc_stat ads
WHERE apai.person_id IN ( '||lv_sql_stmt||')
AND aos.s_adm_outcome_status IN (''PENDING'',''COND-OFFER'')
AND aos.closed_ind = ''N''
AND apai.adm_outcome_status = aos.adm_outcome_status
AND ads.s_adm_doc_status = ''PENDING''
AND ads.closed_ind = ''N''
AND apai.adm_doc_status = ads.adm_doc_status '
USING p_person_id_group; LOOP
'SELECT apai.person_id, apai.admission_appl_number, apai.course_cd, apai.sequence_number
FROM igs_ad_ps_appl_inst apai,
igs_ad_ou_stat aos,
igs_ad_doc_stat ads
WHERE apai.person_id IN ( '||lv_sql_stmt||')
AND aos.s_adm_outcome_status IN (''PENDING'',''COND-OFFER'')
AND aos.closed_ind = ''N''
AND apai.adm_outcome_status = aos.adm_outcome_status
AND ads.s_adm_doc_status = ''PENDING''
AND ads.closed_ind = ''N''
AND apai.adm_doc_status = ads.adm_doc_status'; LOOP