[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
smaddali 19-oct-2002 added igs_uc_old_oustat_pkg.delete_row call whenever
export_to_oss_status is being set to "DC" and captured record is found ,for bug 2630219
ayedubat 13-DEC-2002 Changed the where clause cursor,cur_oss_appl_inst to replace the local
variables used to compare the academic and admissions calendars for bug:2708981
ayedubat 26-MAR-2003 Changed the procedure to create a new cursor,c_ch_system which is used to
create the Decision Import batch ids only for the Systems for which atleast
one Application Choice Record exist for the bug: 2669209
jchakrab 03-Oct-2005 Modified for 4506750 Impact - added extra filter for IGS_AD_CODE_CLASSES.class_type_code
jchin 20-jan-06 Modified for R12 Perf improvements - Bug 3691277 and 3691250
jchakrab 22-May-06 Modified for 5165624
******************************************************************/
-- Cursor to find the details of default UCAS setup defined in the SYSTEM.
-- smaddali modified this cursor to add check for system_code , for bug 2643048 UCFD102 build
CURSOR cur_ucas_setup( cp_system_code igs_uc_defaults.system_code%TYPE) IS
SELECT *
FROM igs_uc_defaults
WHERE system_code = NVL(cp_system_code,system_code) ;
SELECT uac.*,uac.ROWID
FROM igs_uc_app_choices uac,
igs_uc_defaults ud
WHERE uac.app_no = NVL(p_app_no,uac.app_no)
AND uac.choice_no = NVL( p_choice_number,uac.choice_no )
AND uac.export_to_oss_status = 'AC'
AND ud.system_code = uac.system_code
AND ud.current_inst_code = uac.institute_code
ORDER BY uac.ucas_cycle, uac.app_no, uac.choice_no ;
SELECT APLINST.ADM_OUTCOME_STATUS, APLINST.ADM_OFFER_RESP_STATUS,
APLINST.PERSON_ID, APLINST.ADMISSION_APPL_NUMBER,
APLINST.NOMINATED_COURSE_CD, APLINST.SEQUENCE_NUMBER,
APLINST.CRV_VERSION_NUMBER, APLINST.LOCATION_CD,
APLINST.ATTENDANCE_MODE, APLINST.ATTENDANCE_TYPE, APLINST.UNIT_SET_CD,
APLINST.US_VERSION_NUMBER, APL.ACAD_CAL_TYPE, UAC.POINT_OF_ENTRY
FROM IGS_UC_APP_CHOICES UAC,
IGS_UC_APPLICANTS UA,
IGS_UC_DEFAULTS UD,
IGS_AD_SS_APPL_TYP AAT,
IGS_AD_APPL_ALL APL,
IGS_AD_PS_APPL_INST_ALL APLINST,
IGS_UC_SYS_CALNDRS USC
WHERE UAC.APP_NO = CP_APP_NO
AND UAC.CHOICE_NO = CP_CHOICE_NO
AND UAC.UCAS_CYCLE = CP_UCAS_CYCLE
AND UA.APP_NO = UAC.APP_NO
AND UA.OSS_PERSON_ID = APL.PERSON_ID
AND TO_CHAR (UA.APP_NO) = APL.ALT_APPL_ID
AND APL.CHOICE_NUMBER = UAC.CHOICE_NO
AND UAC.SYSTEM_CODE = UD.SYSTEM_CODE
AND UD.APPLICATION_TYPE = AAT.ADMISSION_APPLICATION_TYPE
AND UAC.SYSTEM_CODE = USC.SYSTEM_CODE
AND UAC.ENTRY_YEAR = USC.ENTRY_YEAR
AND (UAC.ENTRY_MONTH = USC.ENTRY_MONTH OR USC.ENTRY_MONTH = 0)
AND APL.ACAD_CAL_TYPE = USC.ACA_CAL_TYPE
AND APL.ACAD_CI_SEQUENCE_NUMBER = USC.ACA_CAL_SEQ_NO
AND APL.ADM_CAL_TYPE = USC.ADM_CAL_TYPE
AND APL.ADM_CI_SEQUENCE_NUMBER = USC.ADM_CAL_SEQ_NO
AND APL.ADMISSION_CAT = AAT.ADMISSION_CAT
AND APL.S_ADMISSION_PROCESS_TYPE = AAT.S_ADMISSION_PROCESS_TYPE
AND APL.PERSON_ID = APLINST.PERSON_ID
AND APL.ADMISSION_APPL_NUMBER = APLINST.ADMISSION_APPL_NUMBER
AND APLINST.NOMINATED_COURSE_CD = UAC.OSS_PROGRAM_CODE
AND APLINST.CRV_VERSION_NUMBER = UAC.OSS_PROGRAM_VERSION
AND APLINST.LOCATION_CD = UAC.OSS_LOCATION
AND APLINST.ATTENDANCE_MODE = UAC.OSS_ATTENDANCE_MODE
AND APLINST.ATTENDANCE_TYPE = UAC.OSS_ATTENDANCE_TYPE;
SELECT US.UNIT_SET_CD,
US.VERSION_NUMBER US_VERSION_NUMBER
FROM IGS_PS_OFR_UNIT_SET COUS,
IGS_PS_OFR_OPT COO,
IGS_EN_UNIT_SET US,
IGS_EN_UNIT_SET_CAT USC,
IGS_PS_US_PRENR_CFG CFG
WHERE COUS.UNIT_SET_CD = P_UNIT_SET_CD
AND COUS.US_VERSION_NUMBER = P_US_VERSION_NUMBER
AND COUS.COURSE_CD = P_NOMINATED_COURSE_CD
AND COUS.CRV_VERSION_NUMBER = P_CRV_VERSION_NUMBER
AND COUS.CAL_TYPE = P_ACAD_CAL_TYPE
AND COO.LOCATION_CD = P_LOCATION_CD
AND COO.ATTENDANCE_MODE = P_ATTENDANCE_MODE
AND COO.ATTENDANCE_TYPE = P_ATTENDANCE_TYPE
AND COO.COURSE_CD = COUS.COURSE_CD
AND COO.VERSION_NUMBER = COUS.CRV_VERSION_NUMBER
AND COO.CAL_TYPE = COUS.CAL_TYPE
AND US.UNIT_SET_CD = COUS.UNIT_SET_CD
AND US.VERSION_NUMBER = COUS.US_VERSION_NUMBER
AND US.UNIT_SET_CAT = USC.UNIT_SET_CAT
AND USC.S_UNIT_SET_CAT ='PRENRL_YR'
AND US.UNIT_SET_CD = CFG.UNIT_SET_CD
AND CFG.SEQUENCE_NO = NVL(P_POINT_OF_ENTRY,1)
AND NOT EXISTS (SELECT COURSE_CD FROM IGS_PS_OF_OPT_UNT_ST COOUS WHERE COOUS.COO_ID = COO.COO_ID)
UNION ALL
SELECT US.UNIT_SET_CD,
US.VERSION_NUMBER US_VERSION_NUMBER
FROM IGS_PS_OF_OPT_UNT_ST COOUS,
IGS_EN_UNIT_SET US,
IGS_EN_UNIT_SET_CAT USC,
IGS_PS_US_PRENR_CFG CFG
WHERE COOUS.UNIT_SET_CD = P_UNIT_SET_CD
AND COOUS.US_VERSION_NUMBER = P_US_VERSION_NUMBER
AND COOUS.COURSE_CD = P_NOMINATED_COURSE_CD
AND COOUS.CRV_VERSION_NUMBER = P_CRV_VERSION_NUMBER
AND COOUS.CAL_TYPE = P_ACAD_CAL_TYPE
AND COOUS.LOCATION_CD = P_LOCATION_CD
AND COOUS.ATTENDANCE_MODE = P_ATTENDANCE_MODE
AND COOUS.ATTENDANCE_TYPE = P_ATTENDANCE_TYPE
AND US.UNIT_SET_CD = COOUS.UNIT_SET_CD
AND US.VERSION_NUMBER = COOUS.US_VERSION_NUMBER
AND US.UNIT_SET_CAT = USC.UNIT_SET_CAT
AND USC.S_UNIT_SET_CAT ='PRENRL_YR'
AND US.UNIT_SET_CD = CFG.UNIT_SET_CD
AND CFG.SEQUENCE_NO = NVL(P_POINT_OF_ENTRY,1);
SELECT uoc.*, uoc.ROWID
FROM igs_uc_old_oustat uoc
WHERE uoc.app_no = p_app_no
AND uoc.choice_no = p_choice_no;
SELECT *
FROM igs_uc_transactions tran
WHERE tran.app_no = p_app_no
AND tran.choice_no = p_choice_no
AND tran.ucas_cycle = p_ucas_cycle
AND tran.transaction_type IN ( 'LA','LD','RA','RD','RX' )
ORDER BY tran.uc_tran_id DESC;
SELECT 'X'
FROM igs_uc_transactions tran
WHERE tran.app_no = p_app_no
AND tran.choice_no = p_choice_no
AND tran.ucas_cycle = p_ucas_cycle
AND tran.transaction_type IN ( 'LA','LD','RA','RD','RX' )
AND ( NVL(tran.sent_to_ucas,'N') = 'N' OR tran.error_code <> 0 ) ;
SELECT mos.adm_outcome_status
FROM igs_uc_map_out_stat mos
WHERE mos.system_code = p_system_code
AND mos.decision_code = p_decision
AND mos.default_ind = 'Y'
AND mos.closed_ind <> 'Y' ;
SELECT s_adm_outcome_status
FROM igs_ad_ou_stat
WHERE adm_outcome_status = p_adm_out_status;
SELECT req_for_reconsideration_ind
FROM igs_ad_ps_appl_all
WHERE person_id = p_person_id
AND admission_appl_number = p_admission_appl_number
AND nominated_course_cd = p_nominated_course_code;
SELECT uac.*,uac.ROWID
FROM igs_uc_app_choices uac,
igs_uc_defaults ud
WHERE uac.app_no = NVL( p_app_no,uac.app_no)
AND uac.choice_no = NVL( p_choice_number,uac.choice_no )
AND uac.batch_id IS NOT NULL
AND uac.export_to_oss_status = 'DP'
AND uac.institute_code = ud.current_inst_code
AND uac.system_code = ud.system_code ;
SELECT error_code, status
FROM igs_ad_admde_int_all
WHERE batch_id = p_batch_id
AND person_id = p_person_id
AND admission_appl_number = p_admission_appl_number
AND nominated_course_cd = p_nominated_course_cd
AND sequence_number = p_sequence_number
AND ( status IN ('3','2') OR error_code IS NOT NULL ) ;
SELECT *
FROM igs_ad_admde_int_all
WHERE interface_mkdes_id = p_interface_mkdes_id ;
SELECT *
FROM igs_ad_batc_def_det_all
WHERE batch_id = p_batch_id ;
SELECT code_id
FROM igs_ad_code_classes
WHERE class = 'PENDING_REASON'
AND system_default = 'Y'
AND class_type_code = 'ADM_CODE_CLASSES';
SELECT igs_ad_interface_ctl_s.NEXTVAL
FROM DUAL ;
SELECT DISTINCT a.system_code, a.entry_year, a.entry_month
FROM igs_uc_app_choices a,
igs_uc_defaults ud
WHERE a.app_no = NVL(p_app_no, a.app_no)
AND a.choice_no = NVL(p_choice_number,a.choice_no)
AND a.export_to_oss_status = 'AC'
AND ud.system_code = a.system_code
AND ud.current_inst_code = a.institute_code;
SELECT aca_cal_type,
aca_cal_seq_no,
adm_cal_type,
adm_cal_seq_no
FROM igs_uc_sys_calndrs sc
WHERE sc.system_code = cp_system_code
AND sc.entry_year = cp_entry_year
AND sc.entry_month = cp_entry_month;
SELECT admission_cat, s_admission_process_type
FROM igs_ad_ss_appl_typ
WHERE admission_application_type = cp_application_type
AND closed_ind = 'N';
igs_ad_batc_def_det_pkg.insert_row(
x_rowid => l_rowid,
x_batch_id => l_batch_id,
x_description => l_description,
x_acad_cal_type => l_sys_entry_cal_det_rec.aca_cal_type,
x_acad_ci_sequence_number => l_sys_entry_cal_det_rec.aca_cal_seq_no,
x_adm_cal_type => l_sys_entry_cal_det_rec.adm_cal_type,
x_adm_ci_sequence_number => l_sys_entry_cal_det_rec.adm_cal_seq_no,
x_admission_cat => l_apc_det_rec.admission_cat,
x_s_admission_process_type => l_apc_det_rec.s_admission_process_type,
x_decision_make_id => cur_ucas_setup_rec.decision_make_id,
x_decision_date => SYSDATE,
x_decision_reason_id => cur_ucas_setup_rec.decision_reason_id,
x_pending_reason_id => NULL,
x_offer_dt => NULL,
x_offer_response_dt => NULL,
x_mode => 'R' );
igs_uc_old_oustat_pkg.delete_row(
X_ROWID => cur_prev_ou_captured_rec.ROWID ) ;
igs_uc_old_oustat_pkg.delete_row(
x_rowid => cur_prev_ou_captured_rec.ROWID ) ;
igs_ad_admde_int_pkg.insert_row (
x_rowid => l_rowid,
x_interface_mkdes_id => l_interface_mkdes_id,
x_interface_run_id => l_interface_run_id,
x_batch_id => l_batch_id,
x_person_id => cur_oss_appl_inst_rec.person_id,
x_admission_appl_number => cur_oss_appl_inst_rec.admission_appl_number,
x_nominated_course_cd => cur_oss_appl_inst_rec.nominated_course_cd,
x_sequence_number => cur_oss_appl_inst_rec.sequence_number,
x_adm_outcome_status => cur_ou_mapping_rec.adm_outcome_status,
x_decision_make_id => cur_prev_ou_captured_rec.decision_make_id,
x_decision_date => cur_prev_ou_captured_rec.decision_date,
x_decision_reason_id => cur_prev_ou_captured_rec.decision_reason_id,
x_pending_reason_id => NULL,
x_offer_dt => TRUNC(NVL(cur_ucas_app_choice_rec.decision_date, SYSDATE)),
x_offer_response_dt => NULL,
x_status => '2', --Pending Status
x_error_code => NULL,
x_mode => 'R',
x_reconsider_flag => l_reconsideration_flag );
igs_ad_admde_int_pkg.insert_row (
x_rowid => l_rowid,
x_interface_mkdes_id => l_interface_mkdes_id,
x_interface_run_id => l_interface_run_id,
x_batch_id => l_batch_id,
x_person_id => cur_oss_appl_inst_rec.person_id,
x_admission_appl_number => cur_oss_appl_inst_rec.admission_appl_number,
x_nominated_course_cd => cur_oss_appl_inst_rec.nominated_course_cd,
x_sequence_number => cur_oss_appl_inst_rec.sequence_number,
x_adm_outcome_status => cur_ou_mapping_rec.adm_outcome_status,
x_decision_make_id => cur_ucas_setup_rec.decision_make_id,
x_decision_date => TRUNC(NVL(cur_ucas_app_choice_rec.decision_date, SYSDATE)),
x_decision_reason_id => cur_ucas_setup_rec.decision_reason_id,
x_pending_reason_id => NULL,
x_offer_dt => TRUNC(NVL(cur_ucas_app_choice_rec.decision_date, SYSDATE)),
x_offer_response_dt => NULL,
x_status => '2', --Pending Status
x_error_code => NULL,
x_mode => 'R',
x_reconsider_flag => l_reconsideration_flag );
igs_uc_old_oustat_pkg.delete_row(
x_rowid => cur_prev_ou_captured_rec.ROWID ) ;
/* call the insert_row of the Admission Decision Import Interface table, IGS_AD_ADMDE_INT_ALL */
igs_ad_admde_int_pkg.insert_row (
x_rowid => l_rowid,
x_interface_mkdes_id => l_interface_mkdes_id,
x_interface_run_id => l_interface_run_id,
x_batch_id => l_batch_id,
x_person_id => cur_oss_appl_inst_rec.person_id,
x_admission_appl_number => cur_oss_appl_inst_rec.admission_appl_number,
x_nominated_course_cd => cur_oss_appl_inst_rec.nominated_course_cd,
x_sequence_number => cur_oss_appl_inst_rec.sequence_number,
x_adm_outcome_status => cur_ucas_setup_rec.rejected_outcome_status,
x_decision_make_id => cur_ucas_setup_rec.decision_make_id,
x_decision_date => TRUNC(NVL(cur_ucas_app_choice_rec.decision_date, SYSDATE)),
x_decision_reason_id => cur_ucas_setup_rec.decision_reason_id,
x_pending_reason_id => NULL,
x_offer_dt => NULL,
x_offer_response_dt => NULL,
x_status => '2', --Pending Status
x_error_code => NULL,
x_mode => 'R',
x_reconsider_flag => l_reconsideration_flag );
/* call the insert_row of the Admission Decision Import Interface table, IGS_AD_ADMDE_INT_ALL */
igs_ad_admde_int_pkg.insert_row (
x_rowid => l_rowid,
x_interface_mkdes_id => l_interface_mkdes_id,
x_interface_run_id => l_interface_run_id,
x_batch_id => l_batch_id,
x_person_id => cur_oss_appl_inst_rec.person_id,
x_admission_appl_number => cur_oss_appl_inst_rec.admission_appl_number,
x_nominated_course_cd => cur_oss_appl_inst_rec.nominated_course_cd,
x_sequence_number => cur_oss_appl_inst_rec.sequence_number,
x_adm_outcome_status => cur_ucas_setup_rec.pending_outcome_status,
x_decision_make_id => cur_ucas_setup_rec.decision_make_id,
x_decision_date => TRUNC(cur_ucas_app_choice_rec.decision_date),
x_decision_reason_id => cur_ucas_setup_rec.decision_reason_id,
x_pending_reason_id => cur_pending_reason_rec.code_id,
x_offer_dt => NULL,
x_offer_response_dt => NULL,
x_status => '2', --Pending Status
x_error_code => NULL,
x_mode => 'R',
x_reconsider_flag => l_reconsideration_flag );
/* call the insert_row of the Admission Decision Import Interface table, IGS_AD_ADMDE_INT_ALL */
igs_ad_admde_int_pkg.insert_row (
x_rowid => l_rowid,
x_interface_mkdes_id => l_interface_mkdes_id,
x_interface_run_id => l_interface_run_id,
x_batch_id => l_batch_id,
x_person_id => cur_oss_appl_inst_rec.person_id,
x_admission_appl_number => cur_oss_appl_inst_rec.admission_appl_number,
x_nominated_course_cd => cur_oss_appl_inst_rec.nominated_course_cd,
x_sequence_number => cur_oss_appl_inst_rec.sequence_number,
x_adm_outcome_status => l_oss_ou_status_of_app_choice,
x_decision_make_id => cur_ucas_setup_rec.decision_make_id,
x_decision_date => TRUNC(NVL(cur_ucas_app_choice_rec.decision_date, SYSDATE)),
x_decision_reason_id => cur_ucas_setup_rec.decision_reason_id,
x_pending_reason_id => NULL,
x_offer_dt => TRUNC(NVL(cur_ucas_app_choice_rec.decision_date, SYSDATE)),
x_offer_response_dt => NULL,
x_status => '2', --Pending Status
x_error_code => NULL,
x_mode => 'R',
x_reconsider_flag => l_reconsideration_flag );
/* call the insert_row of the Admission Decision Import Interface table, IGS_AD_ADMDE_INT_ALL */
igs_ad_admde_int_pkg.insert_row (
x_rowid => l_rowid,
x_interface_mkdes_id => l_interface_mkdes_id,
x_interface_run_id => l_interface_run_id,
x_batch_id => l_batch_id,
x_person_id => cur_oss_appl_inst_rec.person_id,
x_admission_appl_number => cur_oss_appl_inst_rec.admission_appl_number,
x_nominated_course_cd => cur_oss_appl_inst_rec.nominated_course_cd,
x_sequence_number => cur_oss_appl_inst_rec.sequence_number,
x_adm_outcome_status => cur_ou_mapping_rec.adm_outcome_status,
x_decision_make_id => cur_ucas_setup_rec.decision_make_id,
x_decision_date => TRUNC(NVL(cur_ucas_app_choice_rec.decision_date, SYSDATE)),
x_decision_reason_id => cur_ucas_setup_rec.decision_reason_id,
x_pending_reason_id => NULL,
x_offer_dt => TRUNC(cur_latest_trans_rec.creation_date), --Populate the Transaction Date
x_offer_response_dt => NULL,
x_status => '2', -- pending status
x_error_code => NULL,
x_mode => 'R',
x_reconsider_flag => l_reconsideration_flag );
Import Process which update the outcome decision for an application */
IF l_export_to_oss_status = 'DP' THEN
-- Fetch the populated record from the Import Interface Table
OPEN cur_admde_interface ( l_interface_mkdes_id ) ;
/* Update the Application choice record with the error code, batch_id and status */
igs_uc_app_choices_pkg.update_row
( x_rowid => cur_ucas_app_choice_rec.ROWID
,x_app_choice_id => cur_ucas_app_choice_rec.app_choice_id
,x_app_id => cur_ucas_app_choice_rec.app_id
,x_app_no => cur_ucas_app_choice_rec.app_no
,x_choice_no => cur_ucas_app_choice_rec.choice_no
,x_last_change => cur_ucas_app_choice_rec.last_change
,x_institute_code => cur_ucas_app_choice_rec.institute_code
,x_ucas_program_code => cur_ucas_app_choice_rec.ucas_program_code
,x_oss_program_code => cur_ucas_app_choice_rec.oss_program_code
,x_oss_program_version => cur_ucas_app_choice_rec.oss_program_version
,x_oss_attendance_type => cur_ucas_app_choice_rec.oss_attendance_type
,x_oss_attendance_mode => cur_ucas_app_choice_rec.oss_attendance_mode
,x_campus => cur_ucas_app_choice_rec.campus
,x_oss_location => cur_ucas_app_choice_rec.oss_location
,x_faculty => cur_ucas_app_choice_rec.faculty
,x_entry_year => cur_ucas_app_choice_rec.entry_year
,x_entry_month => cur_ucas_app_choice_rec.entry_month
,x_point_of_entry => cur_ucas_app_choice_rec.point_of_entry
,x_home => cur_ucas_app_choice_rec.home
,x_deferred => cur_ucas_app_choice_rec.deferred
,x_route_b_pref_round => cur_ucas_app_choice_rec.route_b_pref_round
,x_route_b_actual_round => cur_ucas_app_choice_rec.route_b_actual_round
,x_condition_category => cur_ucas_app_choice_rec.condition_category
,x_condition_code => cur_ucas_app_choice_rec.condition_code
,x_decision => cur_ucas_app_choice_rec.decision
,x_decision_date => cur_ucas_app_choice_rec.decision_date
,x_decision_number => cur_ucas_app_choice_rec.decision_number
,x_reply => cur_ucas_app_choice_rec.reply
,x_summary_of_cond => cur_ucas_app_choice_rec.summary_of_cond
,x_choice_cancelled => cur_ucas_app_choice_rec.choice_cancelled
,x_action => cur_ucas_app_choice_rec.action
,x_substitution => cur_ucas_app_choice_rec.substitution
,x_date_substituted => cur_ucas_app_choice_rec.date_substituted
,x_prev_institution => cur_ucas_app_choice_rec.prev_institution
,x_prev_course => cur_ucas_app_choice_rec.prev_course
,x_prev_campus => cur_ucas_app_choice_rec.prev_campus
,x_ucas_amendment => cur_ucas_app_choice_rec.ucas_amendment
,x_withdrawal_reason => cur_ucas_app_choice_rec.withdrawal_reason
,x_offer_course => cur_ucas_app_choice_rec.offer_course
,x_offer_campus => cur_ucas_app_choice_rec.offer_campus
,x_offer_crse_length => cur_ucas_app_choice_rec.offer_crse_length
,x_offer_entry_month => cur_ucas_app_choice_rec.offer_entry_month
,x_offer_entry_year => cur_ucas_app_choice_rec.offer_entry_year
,x_offer_entry_point => cur_ucas_app_choice_rec.offer_entry_point
,x_offer_text => cur_ucas_app_choice_rec.offer_text
,x_export_to_oss_status => l_export_to_oss_status
,x_error_code => l_app_choice_error_code
,x_request_id => l_conc_request_id
,x_batch_id => l_ch_batch_id
,x_mode => 'R'
,x_extra_round_nbr => cur_ucas_app_choice_rec.extra_round_nbr
,x_system_code => cur_ucas_app_choice_rec.system_code
,x_part_time => cur_ucas_app_choice_rec.part_time
,x_interview => cur_ucas_app_choice_rec.interview
,x_late_application => cur_ucas_app_choice_rec.late_application
,x_modular => cur_ucas_app_choice_rec.modular
,x_residential => cur_ucas_app_choice_rec.residential
,x_ucas_cycle => cur_ucas_app_choice_rec.ucas_cycle);
igs_uc_app_choices_pkg.update_row (
x_rowid => cur_dp_app_choice_rec.ROWID
,x_app_choice_id => cur_dp_app_choice_rec.app_choice_id
,x_app_id => cur_dp_app_choice_rec.app_id
,x_app_no => cur_dp_app_choice_rec.app_no
,x_choice_no => cur_dp_app_choice_rec.choice_no
,x_last_change => cur_dp_app_choice_rec.last_change
,x_institute_code => cur_dp_app_choice_rec.institute_code
,x_ucas_program_code => cur_dp_app_choice_rec.ucas_program_code
,x_oss_program_code => cur_dp_app_choice_rec.oss_program_code
,x_oss_program_version => cur_dp_app_choice_rec.oss_program_version
,x_oss_attendance_type => cur_dp_app_choice_rec.oss_attendance_type
,x_oss_attendance_mode => cur_dp_app_choice_rec.oss_attendance_mode
,x_campus => cur_dp_app_choice_rec.campus
,x_oss_location => cur_dp_app_choice_rec.oss_location
,x_faculty => cur_dp_app_choice_rec.faculty
,x_entry_year => cur_dp_app_choice_rec.entry_year
,x_entry_month => cur_dp_app_choice_rec.entry_month
,x_point_of_entry => cur_dp_app_choice_rec.point_of_entry
,x_home => cur_dp_app_choice_rec.home
,x_deferred => cur_dp_app_choice_rec.deferred
,x_route_b_pref_round => cur_dp_app_choice_rec.route_b_pref_round
,x_route_b_actual_round => cur_dp_app_choice_rec.route_b_actual_round
,x_condition_category => cur_dp_app_choice_rec.condition_category
,x_condition_code => cur_dp_app_choice_rec.condition_code
,x_decision => cur_dp_app_choice_rec.decision
,x_decision_date => cur_dp_app_choice_rec.decision_date
,x_decision_number => cur_dp_app_choice_rec.decision_number
,x_reply => cur_dp_app_choice_rec.reply
,x_summary_of_cond => cur_dp_app_choice_rec.summary_of_cond
,x_choice_cancelled => cur_dp_app_choice_rec.choice_cancelled
,x_action => cur_dp_app_choice_rec.action
,x_substitution => cur_dp_app_choice_rec.substitution
,x_date_substituted => cur_dp_app_choice_rec.date_substituted
,x_prev_institution => cur_dp_app_choice_rec.prev_institution
,x_prev_course => cur_dp_app_choice_rec.prev_course
,x_prev_campus => cur_dp_app_choice_rec.prev_campus
,x_ucas_amendment => cur_dp_app_choice_rec.ucas_amendment
,x_withdrawal_reason => cur_dp_app_choice_rec.withdrawal_reason
,x_offer_course => cur_dp_app_choice_rec.offer_course
,x_offer_campus => cur_dp_app_choice_rec.offer_campus
,x_offer_crse_length => cur_dp_app_choice_rec.offer_crse_length
,x_offer_entry_month => cur_dp_app_choice_rec.offer_entry_month
,x_offer_entry_year => cur_dp_app_choice_rec.offer_entry_year
,x_offer_entry_point => cur_dp_app_choice_rec.offer_entry_point
,x_offer_text => cur_dp_app_choice_rec.offer_text
,x_export_to_oss_status => 'DC'
,x_error_code => NULL
,x_request_id => l_conc_request_id
,x_batch_id => NULL
,x_mode => 'R'
,x_extra_round_nbr => cur_dp_app_choice_rec.extra_round_nbr
,x_system_code => cur_dp_app_choice_rec.system_code
,x_part_time => cur_dp_app_choice_rec.part_time
,x_interview => cur_dp_app_choice_rec.interview
,x_late_application => cur_dp_app_choice_rec.late_application
,x_modular => cur_dp_app_choice_rec.modular
,x_residential => cur_dp_app_choice_rec.residential
,x_ucas_cycle => cur_dp_app_choice_rec.ucas_cycle);
l_last_update_login NUMBER(15) ;
l_last_updated_by NUMBER(15) ;
SELECT *
FROM igs_uc_defaults
WHERE system_code = cp_system_code ;
SELECT uac.*,uac.ROWID
FROM IGS_UC_APP_CHOICES uac,
igs_uc_defaults ud
WHERE uac.app_no = NVL(p_app_no,uac.app_no)
AND uac.choice_no = NVL( p_choice_number,uac.choice_no )
AND uac.export_to_oss_status = 'DC'
AND uac.institute_code = ud.current_inst_code
AND uac.system_code = ud.system_code
ORDER BY uac.ucas_cycle, uac.app_no, uac.choice_no;
SELECT APLINST.ADM_OUTCOME_STATUS, APLINST.ADM_OFFER_RESP_STATUS,
APLINST.PERSON_ID, APLINST.ADMISSION_APPL_NUMBER,
APLINST.NOMINATED_COURSE_CD, APLINST.SEQUENCE_NUMBER,
APLINST.CRV_VERSION_NUMBER, APLINST.LOCATION_CD,
APLINST.ATTENDANCE_MODE, APLINST.ATTENDANCE_TYPE, APLINST.UNIT_SET_CD,
APLINST.US_VERSION_NUMBER, APL.ACAD_CAL_TYPE, UAC.POINT_OF_ENTRY
FROM IGS_UC_APP_CHOICES UAC,
IGS_UC_APPLICANTS UA,
IGS_UC_DEFAULTS UD,
IGS_AD_SS_APPL_TYP AAT,
IGS_AD_APPL_ALL APL,
IGS_AD_PS_APPL_INST_ALL APLINST,
IGS_UC_SYS_CALNDRS USC
WHERE UAC.APP_NO = CP_APP_NO
AND UAC.CHOICE_NO = CP_CHOICE_NO
AND UAC.UCAS_CYCLE = CP_UCAS_CYCLE
AND UA.APP_NO = UAC.APP_NO
AND UA.OSS_PERSON_ID = APL.PERSON_ID
AND TO_CHAR (UA.APP_NO) = APL.ALT_APPL_ID
AND APL.CHOICE_NUMBER = UAC.CHOICE_NO
AND UAC.SYSTEM_CODE = UD.SYSTEM_CODE
AND UD.APPLICATION_TYPE = AAT.ADMISSION_APPLICATION_TYPE
AND UAC.SYSTEM_CODE = USC.SYSTEM_CODE
AND UAC.ENTRY_YEAR = USC.ENTRY_YEAR
AND (UAC.ENTRY_MONTH = USC.ENTRY_MONTH OR USC.ENTRY_MONTH = 0)
AND APL.ACAD_CAL_TYPE = USC.ACA_CAL_TYPE
AND APL.ACAD_CI_SEQUENCE_NUMBER = USC.ACA_CAL_SEQ_NO
AND APL.ADM_CAL_TYPE = USC.ADM_CAL_TYPE
AND APL.ADM_CI_SEQUENCE_NUMBER = USC.ADM_CAL_SEQ_NO
AND APL.ADMISSION_CAT = AAT.ADMISSION_CAT
AND APL.S_ADMISSION_PROCESS_TYPE = AAT.S_ADMISSION_PROCESS_TYPE
AND APL.PERSON_ID = APLINST.PERSON_ID
AND APL.ADMISSION_APPL_NUMBER = APLINST.ADMISSION_APPL_NUMBER
AND APLINST.NOMINATED_COURSE_CD = UAC.OSS_PROGRAM_CODE
AND APLINST.CRV_VERSION_NUMBER = UAC.OSS_PROGRAM_VERSION
AND APLINST.LOCATION_CD = UAC.OSS_LOCATION
AND APLINST.ATTENDANCE_MODE = UAC.OSS_ATTENDANCE_MODE
AND APLINST.ATTENDANCE_TYPE = UAC.OSS_ATTENDANCE_TYPE;
SELECT US.UNIT_SET_CD,
US.VERSION_NUMBER US_VERSION_NUMBER
FROM IGS_PS_OFR_UNIT_SET COUS,
IGS_PS_OFR_OPT COO,
IGS_EN_UNIT_SET US,
IGS_EN_UNIT_SET_CAT USC,
IGS_PS_US_PRENR_CFG CFG
WHERE COUS.UNIT_SET_CD = P_UNIT_SET_CD
AND COUS.US_VERSION_NUMBER = P_US_VERSION_NUMBER
AND COUS.COURSE_CD = P_NOMINATED_COURSE_CD
AND COUS.CRV_VERSION_NUMBER = P_CRV_VERSION_NUMBER
AND COUS.CAL_TYPE = P_ACAD_CAL_TYPE
AND COO.LOCATION_CD = P_LOCATION_CD
AND COO.ATTENDANCE_MODE = P_ATTENDANCE_MODE
AND COO.ATTENDANCE_TYPE = P_ATTENDANCE_TYPE
AND COO.COURSE_CD = COUS.COURSE_CD
AND COO.VERSION_NUMBER = COUS.CRV_VERSION_NUMBER
AND COO.CAL_TYPE = COUS.CAL_TYPE
AND US.UNIT_SET_CD = COUS.UNIT_SET_CD
AND US.VERSION_NUMBER = COUS.US_VERSION_NUMBER
AND US.UNIT_SET_CAT = USC.UNIT_SET_CAT
AND USC.S_UNIT_SET_CAT ='PRENRL_YR'
AND US.UNIT_SET_CD = CFG.UNIT_SET_CD
AND CFG.SEQUENCE_NO = NVL(P_POINT_OF_ENTRY,1)
AND NOT EXISTS (SELECT COURSE_CD FROM IGS_PS_OF_OPT_UNT_ST COOUS WHERE COOUS.COO_ID = COO.COO_ID)
UNION ALL
SELECT US.UNIT_SET_CD,
US.VERSION_NUMBER US_VERSION_NUMBER
FROM IGS_PS_OF_OPT_UNT_ST COOUS,
IGS_EN_UNIT_SET US,
IGS_EN_UNIT_SET_CAT USC,
IGS_PS_US_PRENR_CFG CFG
WHERE COOUS.UNIT_SET_CD = P_UNIT_SET_CD
AND COOUS.US_VERSION_NUMBER = P_US_VERSION_NUMBER
AND COOUS.COURSE_CD = P_NOMINATED_COURSE_CD
AND COOUS.CRV_VERSION_NUMBER = P_CRV_VERSION_NUMBER
AND COOUS.CAL_TYPE = P_ACAD_CAL_TYPE
AND COOUS.LOCATION_CD = P_LOCATION_CD
AND COOUS.ATTENDANCE_MODE = P_ATTENDANCE_MODE
AND COOUS.ATTENDANCE_TYPE = P_ATTENDANCE_TYPE
AND US.UNIT_SET_CD = COOUS.UNIT_SET_CD
AND US.VERSION_NUMBER = COOUS.US_VERSION_NUMBER
AND US.UNIT_SET_CAT = USC.UNIT_SET_CAT
AND USC.S_UNIT_SET_CAT ='PRENRL_YR'
AND US.UNIT_SET_CD = CFG.UNIT_SET_CD
AND CFG.SEQUENCE_NO = NVL(P_POINT_OF_ENTRY,1);
SELECT adm_offer_resp_status
FROM igs_uc_map_off_resp
WHERE system_code = p_system_code
AND decision_code = p_decision
AND reply_code = p_reply
AND closed_ind <> 'Y';
SELECT s_adm_outcome_status
FROM igs_ad_ou_stat
WHERE adm_outcome_status = p_adm_out_status;
SELECT uac.*,uac.ROWID
FROM igs_uc_app_choices uac,
igs_uc_defaults ud
WHERE uac.app_no = NVL(p_app_no,uac.app_no)
AND uac.choice_no = NVL( p_choice_number,uac.choice_no )
AND uac.export_to_oss_status = 'RP'
AND uac.institute_code = ud.current_inst_code
AND uac.system_code = ud.system_code
ORDER BY uac.ucas_cycle, uac.app_no, uac.choice_no;
SELECT 'X'
FROM igs_ad_offresp_int
WHERE person_id = p_person_id
AND admission_appl_number = p_admission_appl_number
AND nominated_course_cd = p_nominated_course_cd
AND sequence_number = p_sequence_number
AND status IN ( 2,3 ) ;
SELECT igs_ad_offresp_batch_s.NEXTVAL
FROM dual ;
SELECT aca_cal_type,
aca_cal_seq_no,
adm_cal_type,
adm_cal_seq_no
FROM igs_uc_sys_calndrs
WHERE system_code = cp_system_code
AND entry_year = cp_entry_year
AND entry_month = cp_entry_month;
l_last_updated_by := NVL(FND_GLOBAL.USER_ID,-1) ;
l_last_update_login := NVL(FND_GLOBAL.LOGIN_ID,-1) ;
INSERT INTO igs_ad_offresp_batch
( batch_id,
batch_desc,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
request_id,
program_application_id,
program_update_date,
program_id )
VALUES
( l_batch_id,
l_description,
l_last_updated_by,
SYSDATE,
l_last_updated_by,
SYSDATE,
l_last_update_login,
fnd_global.conc_request_id,
fnd_global.prog_appl_id,
SYSDATE,
fnd_global.conc_program_id );
INSERT INTO igs_ad_offresp_int
( offresp_int_id,
batch_id,
person_id,
admission_appl_number,
nominated_course_cd,
sequence_number,
adm_offer_resp_status,
actual_offer_response_dt,
attent_other_inst_cd,
applicant_acptnce_cndtn,
def_acad_cal_type,
def_acad_ci_sequence_number,
def_adm_cal_type,
def_adm_ci_sequence_number,
status,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
request_id,
program_application_id,
program_update_date,
program_id,
decline_ofr_reason)
VALUES
( igs_ad_offresp_int_s.NEXTVAL,
l_batch_id,
cur_oss_appl_inst_rec.person_id,
cur_oss_appl_inst_rec.admission_appl_number,
cur_oss_appl_inst_rec.nominated_course_cd,
cur_oss_appl_inst_rec.sequence_number,
cur_map_offr_resp_rec.adm_offer_resp_status,
NULL, --ACTUAL_OFFER_RESPONSE_DT
NULL, --ATTENT_OTHER_INST_CD
NULL, --APPLICANT_ACPTNCE_CNDTN
NULL, --DEF_ACAD_CAL_TYPE
NULL, --DEF_ACAD_CI_SEQUENCE_NUMBER
NULL, --DEF_ADM_CAL_TYPE
NULL, --DEF_ADM_CI_SEQUENCE_NUMBER
'2', --Pending Status ( Unprocessed Record)
l_last_updated_by,
SYSDATE,
l_last_updated_by,
SYSDATE,
l_last_update_login,
fnd_global.conc_request_id,
fnd_global.prog_appl_id,
SYSDATE,
fnd_global.conc_program_id,
l_decline_ofr_reason);
/* Update the Application Choice Record with export to OSS status 'COMP' */
l_export_to_oss := 'COMP' ;
/* Update the Application Choice Record with export to OSS status 'COMP' */
l_export_to_oss := 'COMP' ;
/* update the Application choice with the error code ,batch_id and export_to_oss_status*/
igs_uc_app_choices_pkg.update_row
( x_rowid => cur_ucas_app_choice_rec.ROWID
,x_app_choice_id => cur_ucas_app_choice_rec.app_choice_id
,x_app_id => cur_ucas_app_choice_rec.app_id
,x_app_no => cur_ucas_app_choice_rec.app_no
,x_choice_no => cur_ucas_app_choice_rec.choice_no
,x_last_change => cur_ucas_app_choice_rec.last_change
,x_institute_code => cur_ucas_app_choice_rec.institute_code
,x_ucas_program_code => cur_ucas_app_choice_rec.ucas_program_code
,x_oss_program_code => cur_ucas_app_choice_rec.oss_program_code
,x_oss_program_version => cur_ucas_app_choice_rec.oss_program_version
,x_oss_attendance_type => cur_ucas_app_choice_rec.oss_attendance_type
,x_oss_attendance_mode => cur_ucas_app_choice_rec.oss_attendance_mode
,x_campus => cur_ucas_app_choice_rec.campus
,x_oss_location => cur_ucas_app_choice_rec.oss_location
,x_faculty => cur_ucas_app_choice_rec.faculty
,x_entry_year => cur_ucas_app_choice_rec.entry_year
,x_entry_month => cur_ucas_app_choice_rec.entry_month
,x_point_of_entry => cur_ucas_app_choice_rec.point_of_entry
,x_home => cur_ucas_app_choice_rec.home
,x_deferred => cur_ucas_app_choice_rec.deferred
,x_route_b_pref_round => cur_ucas_app_choice_rec.route_b_pref_round
,x_route_b_actual_round => cur_ucas_app_choice_rec.route_b_actual_round
,x_condition_category => cur_ucas_app_choice_rec.condition_category
,x_condition_code => cur_ucas_app_choice_rec.condition_code
,x_decision => cur_ucas_app_choice_rec.decision
,x_decision_date => cur_ucas_app_choice_rec.decision_date
,x_decision_number => cur_ucas_app_choice_rec.decision_number
,x_reply => cur_ucas_app_choice_rec.reply
,x_summary_of_cond => cur_ucas_app_choice_rec.summary_of_cond
,x_choice_cancelled => cur_ucas_app_choice_rec.choice_cancelled
,x_action => cur_ucas_app_choice_rec.action
,x_substitution => cur_ucas_app_choice_rec.substitution
,x_date_substituted => cur_ucas_app_choice_rec.date_substituted
,x_prev_institution => cur_ucas_app_choice_rec.prev_institution
,x_prev_course => cur_ucas_app_choice_rec.prev_course
,x_prev_campus => cur_ucas_app_choice_rec.prev_campus
,x_ucas_amendment => cur_ucas_app_choice_rec.ucas_amendment
,x_withdrawal_reason => cur_ucas_app_choice_rec.withdrawal_reason
,x_offer_course => cur_ucas_app_choice_rec.offer_course
,x_offer_campus => cur_ucas_app_choice_rec.offer_campus
,x_offer_crse_length => cur_ucas_app_choice_rec.offer_crse_length
,x_offer_entry_month => cur_ucas_app_choice_rec.offer_entry_month
,x_offer_entry_year => cur_ucas_app_choice_rec.offer_entry_year
,x_offer_entry_point => cur_ucas_app_choice_rec.offer_entry_point
,x_offer_text => cur_ucas_app_choice_rec.offer_text
,x_export_to_oss_status => l_export_to_oss
,x_error_code => l_app_choice_error_code
,x_request_id => l_conc_request_id
,x_batch_id => l_ch_batch_id
,x_mode => 'R'
,x_extra_round_nbr => cur_ucas_app_choice_rec.extra_round_nbr
,x_system_code => cur_ucas_app_choice_rec.system_code
,x_part_time => cur_ucas_app_choice_rec.part_time
,x_interview => cur_ucas_app_choice_rec.interview
,x_late_application => cur_ucas_app_choice_rec.late_application
,x_modular => cur_ucas_app_choice_rec.modular
,x_residential => cur_ucas_app_choice_rec.residential
,x_ucas_cycle => cur_ucas_app_choice_rec.ucas_cycle
);
igs_uc_app_choices_pkg.update_row
( x_rowid => cur_rp_app_choice_rec.ROWID
,x_app_choice_id => cur_rp_app_choice_rec.app_choice_id
,x_app_id => cur_rp_app_choice_rec.app_id
,x_app_no => cur_rp_app_choice_rec.app_no
,x_choice_no => cur_rp_app_choice_rec.choice_no
,x_last_change => cur_rp_app_choice_rec.last_change
,x_institute_code => cur_rp_app_choice_rec.institute_code
,x_ucas_program_code => cur_rp_app_choice_rec.ucas_program_code
,x_oss_program_code => cur_rp_app_choice_rec.oss_program_code
,x_oss_program_version => cur_rp_app_choice_rec.oss_program_version
,x_oss_attendance_type => cur_rp_app_choice_rec.oss_attendance_type
,x_oss_attendance_mode => cur_rp_app_choice_rec.oss_attendance_mode
,x_campus => cur_rp_app_choice_rec.campus
,x_oss_location => cur_rp_app_choice_rec.oss_location
,x_faculty => cur_rp_app_choice_rec.faculty
,x_entry_year => cur_rp_app_choice_rec.entry_year
,x_entry_month => cur_rp_app_choice_rec.entry_month
,x_point_of_entry => cur_rp_app_choice_rec.point_of_entry
,x_home => cur_rp_app_choice_rec.home
,x_deferred => cur_rp_app_choice_rec.deferred
,x_route_b_pref_round => cur_rp_app_choice_rec.route_b_pref_round
,x_route_b_actual_round => cur_rp_app_choice_rec.route_b_actual_round
,x_condition_category => cur_rp_app_choice_rec.condition_category
,x_condition_code => cur_rp_app_choice_rec.condition_code
,x_decision => cur_rp_app_choice_rec.decision
,x_decision_date => cur_rp_app_choice_rec.decision_date
,x_decision_number => cur_rp_app_choice_rec.decision_number
,x_reply => cur_rp_app_choice_rec.reply
,x_summary_of_cond => cur_rp_app_choice_rec.summary_of_cond
,x_choice_cancelled => cur_rp_app_choice_rec.choice_cancelled
,x_action => cur_rp_app_choice_rec.action
,x_substitution => cur_rp_app_choice_rec.substitution
,x_date_substituted => cur_rp_app_choice_rec.date_substituted
,x_prev_institution => cur_rp_app_choice_rec.prev_institution
,x_prev_course => cur_rp_app_choice_rec.prev_course
,x_prev_campus => cur_rp_app_choice_rec.prev_campus
,x_ucas_amendment => cur_rp_app_choice_rec.ucas_amendment
,x_withdrawal_reason => cur_rp_app_choice_rec.withdrawal_reason
,x_offer_course => cur_rp_app_choice_rec.offer_course
,x_offer_campus => cur_rp_app_choice_rec.offer_campus
,x_offer_crse_length => cur_rp_app_choice_rec.offer_crse_length
,x_offer_entry_month => cur_rp_app_choice_rec.offer_entry_month
,x_offer_entry_year => cur_rp_app_choice_rec.offer_entry_year
,x_offer_entry_point => cur_rp_app_choice_rec.offer_entry_point
,x_offer_text => cur_rp_app_choice_rec.offer_text
,x_export_to_oss_status => l_export_to_oss
,x_error_code => l_app_choice_error_code
,x_request_id => l_conc_request_id
,x_batch_id => l_ch_batch_id
,x_mode => 'R'
,x_extra_round_nbr => cur_rp_app_choice_rec.extra_round_nbr
,x_system_code => cur_rp_app_choice_rec.system_code
,x_part_time => cur_rp_app_choice_rec.part_time
,x_interview => cur_rp_app_choice_rec.interview
,x_late_application => cur_rp_app_choice_rec.late_application
,x_modular => cur_rp_app_choice_rec.modular
,x_residential => cur_rp_app_choice_rec.residential
,x_ucas_cycle => cur_rp_app_choice_rec.ucas_cycle
);