[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT ch.ROWID , ch.*
FROM igs_uc_app_choices ch
WHERE ch.app_no = cp_app_no
AND ch.choice_no = cp_choice_no
AND ch.ucas_cycle = cp_ucas_cycle;
l_last_updated_by CONSTANT NUMBER := FND_GLOBAL.LOGIN_ID;
l_update_adm_appl_number igs_ad_apl_int.update_adm_appl_number%TYPE;
l_update_adm_seq_number igs_ad_ps_appl_inst_int.update_adm_seq_number%TYPE;
SELECT c.admission_appl_number, c.person_id
FROM igs_uc_applicants a , igs_uc_app_choices b , igs_ad_appl_all c
WHERE a.app_no = b.app_no AND
TO_CHAR(a.app_no) = c.alt_appl_id AND
a.oss_person_id = c.person_id AND
b.choice_no = c.choice_number AND
c.acad_cal_type = l_aca_cal_type AND
c.acad_ci_sequence_number = l_aca_seq_no AND
c.adm_cal_type = l_adm_cal_type AND
c.adm_ci_sequence_number = l_adm_seq_no AND
b.app_no = cp_app_no AND
b.choice_no = cp_choice_no AND
c.adm_appl_status <> 'COMPLETED';
SELECT acai.sequence_number
FROM igs_ad_ps_appl_inst acai
WHERE acai.person_id = cp_person_id
AND acai.admission_appl_number = cp_admission_appl_number
AND acai.nominated_course_cd = cp_oss_program_code
AND acai.crv_version_number = cp_oss_program_version
AND NVL(acai.location_cd,'X') = NVL( cp_oss_location ,'X')
AND NVL(acai.attendance_mode,'X') = NVL( cp_oss_attendance_mode ,'X')
AND NVL(acai.attendance_type,'X') = NVL( cp_oss_attendance_type ,'X')
AND NVL(acai.unit_set_cd,'X') = NVL( cp_unit_set_cd ,'X')
AND NVL(acai.us_version_number,-1)= NVL( cp_us_version_number ,-1);
SELECT p.party_id person_id, p.party_number person_number, p.person_last_name surname,
p.person_middle_name middle_name, p.person_first_name given_names,
pp.gender sex,p.person_title title, p.person_name_suffix suffix,
p.person_pre_name_adjunct pre_name_adjunct,
Pd.proof_of_ins , pd.proof_of_immu,
pp.date_of_birth birth_dt, p.known_as preferred_given_name,
pd.level_of_qual level_of_qual_id, pd.military_service_reg,
pd.veteran, e.application_date
FROM igs_pe_HZ_parties pd, hz_parties p , hz_person_profiles pp ,
igs_uc_applicants e
WHERE p.party_id = e.oss_person_id AND e.app_no = p_app_no AND
pp.party_id(+)=p.party_id AND p.party_id=pd.party_id(+) AND
SYSDATE BETWEEN NVL(pp.effective_start_date,SYSDATE) AND NVL(pp.effective_end_date,SYSDATE);
SELECT igs_ad_interface_s.NEXTVAL int_id
FROM dual;
SELECT igs_ad_apl_int_s.NEXTVAL int_appl_id
FROM dual;
SELECT igs_ad_ps_appl_inst_int_s.NEXTVAL appl_inst_int_id
FROM dual;
SELECT code_id
FROM igs_ad_code_classes
WHERE class = 'SYS_APPL_SOURCE'
AND name = 'UCAS'
AND class_type_code = 'ADM_CODE_CLASSES';
SELECT DISTINCT a.ucas_program_code, a.campus, a.choice_no, a.oss_program_code, a.oss_program_version,
a.oss_location, a.point_of_entry, a.deferred, a.oss_attendance_type, a.oss_attendance_mode,
a.route_b_pref_round, b.application_source , a.app_no , a.system_code, a.ucas_cycle, a.entry_year, a.entry_month
FROM igs_uc_app_choices a, igs_uc_applicants b
WHERE a.app_no=b.app_no AND
b.app_no=NVL(p_app_no ,b.app_no) AND
a.institute_code = (SELECT df.current_inst_code FROM igs_uc_defaults df
WHERE df.system_code = a.system_code) AND
a.export_to_oss_status = 'OC' AND
a.choice_no = NVL(p_choice_no,a.choice_no)
ORDER BY a.choice_no ;
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.COURSE_CD = P_COURSE_CD
AND COUS.CRV_VERSION_NUMBER = P_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 = P_SEQ_NO
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.COURSE_CD = P_COURSE_CD
AND COOUS.CRV_VERSION_NUMBER = P_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 = P_SEQ_NO;
SELECT cr.oss_program_code, cr.oss_program_version, cr.oss_location, cr.oss_attendance_type,
cr.oss_attendance_mode
FROM igs_uc_crse_dets cr
WHERE cr.ucas_program_code = cp_ucas_program_code
AND cr.ucas_campus = cp_ucas_campus
AND cr.institute = (SELECT current_inst_code FROM igs_uc_defaults df WHERE df.system_code = cr.system_code)
AND cr.system_code = cp_system_code
AND cr.oss_program_code IS NOT NULL
AND cr.oss_location IS NOT NULL;
SELECT a.ROWID, a.*
FROM igs_uc_app_choices a
WHERE a.app_no = cp_appno
AND a.choice_no = cp_choiceno
AND a.ucas_cycle = cp_ucas_cycle;
SELECT *
FROM igs_uc_defaults def
WHERE system_code = cp_system_code;
SELECT sc.aca_cal_type,
sc.aca_cal_seq_no,
sc.adm_cal_type,
sc.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;
INSERT INTO igs_ad_interface(person_number,
interface_id,
batch_id,
org_id,
source_type_id,
surname,
middle_name,
given_names,
sex,
title,
suffix,
pre_name_adjunct,
proof_of_insurance,
proof_of_immun,
birth_dt,
preferred_given_name,
level_of_qual,
military_service_reg,
veteran,
status,
record_status,
match_ind,
person_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
request_id,
program_application_id,
program_id,
program_update_date )
VALUES(c_pe_person_rec.person_number,
c_int_id_rec.int_id,
p_batch_id,
p_orgid,
p_source_type_id,
NVL(c_pe_person_rec.surname,' '),
c_pe_person_rec.middle_name,
NVL(c_pe_person_rec.given_names,' '),
c_pe_person_rec.sex,
c_pe_person_rec.title,
c_pe_person_rec.suffix,
c_pe_person_rec.pre_name_adjunct,
c_pe_person_rec.proof_of_ins,
c_pe_person_rec.proof_of_immu,
c_pe_person_rec.birth_dt,
c_pe_person_rec.preferred_given_name,
c_pe_person_rec.level_of_qual_id,
c_pe_person_rec.military_service_reg,
c_pe_person_rec.veteran,
l_status,
l_record_status,
'15',
c_pe_person_rec.person_id,
l_created_by,
SYSDATE,
l_last_updated_by,
SYSDATE,
l_request_id,
l_program_application_id,
l_program_id,
SYSDATE );
l_update_adm_appl_number := NULL ;
l_update_adm_appl_number := c_match_adm_appl_rec.admission_appl_number ;
INSERT INTO igs_ad_apl_int
( interface_appl_id
,interface_id
,appl_dt
,acad_cal_type
,acad_ci_sequence_number
,adm_cal_type
,adm_ci_sequence_number
,tac_appl_ind
,status
,created_by
,creation_date
,last_updated_by
,last_update_date
,choice_number
,routeb_pref
,update_adm_appl_number
,admission_application_type
,alt_appl_id
)
VALUES( c_int_appl_id_rec.int_appl_id,
c_int_id_rec.int_id,
NVL(c_pe_person_rec.application_date,SYSDATE),
l_aca_cal_type,
l_aca_seq_no,
l_adm_cal_type,
l_adm_seq_no,
'N',
l_status,
l_created_by,
SYSDATE,
l_last_updated_by,
SYSDATE,
j.choice_no,
j.route_b_pref_round,
l_update_adm_appl_number,
c_defaults_rec.application_type,
TO_CHAR(j.app_no)
);
l_update_adm_seq_number := NULL ;
l_update_adm_seq_number := c_match_adm_appl_inst_rec.sequence_number ;
INSERT INTO igs_ad_ps_appl_inst_int
( interface_appl_id
,interface_ps_appl_inst_id
,nominated_course_cd
,req_for_adv_standing_ind
,app_source_id
,crv_version_number
,location_cd
,attendance_mode
,attendance_type
,preference_number
,unit_set_cd
,us_version_number
,status
,created_by
,creation_date
,last_updated_by
,last_update_date
,update_adm_seq_number
)
VALUES ( c_int_appl_id_rec.int_appl_id,
appl_inst_int_id_rec.appl_inst_int_id,
j.oss_program_code,
'N',
DECODE(j.application_source, 'U',l_code_id,NULL),
j.oss_program_version,
j.oss_location,
j.oss_attendance_mode,
j.oss_attendance_type,
1,
c_unit_set_cd_rec.unit_set_cd,
c_unit_set_cd_rec.us_version_number,
l_status,
l_created_by,
SYSDATE,
l_last_updated_by,
SYSDATE,
l_update_adm_seq_number
);
igs_uc_app_choices_pkg.update_row
( x_rowid => app_choices_rec.ROWID
,x_app_choice_id => app_choices_rec.app_choice_id
,x_app_id => app_choices_rec.app_id
,x_app_no => app_choices_rec.app_no
,x_choice_no => app_choices_rec.choice_no
,x_last_change => app_choices_rec.last_change
,x_institute_code => app_choices_rec.institute_code
,x_ucas_program_code => app_choices_rec.ucas_program_code
,x_oss_program_code => j.oss_program_code
,x_oss_program_version => j.oss_program_version
,x_oss_attendance_type => j.oss_attendance_type
,x_oss_attendance_mode => j.oss_attendance_mode
,x_campus => app_choices_rec.campus
,x_oss_location => j.oss_location
,x_faculty => app_choices_rec.faculty
,x_entry_year => app_choices_rec.entry_year
,x_entry_month => app_choices_rec.entry_month
,x_point_of_entry => app_choices_rec.point_of_entry
,x_home => app_choices_rec.home
,x_deferred => app_choices_rec.deferred
,x_route_b_pref_round => app_choices_rec.route_b_pref_round
,x_route_b_actual_round => app_choices_rec.route_b_actual_round
,x_condition_category => app_choices_rec.condition_category
,x_condition_code => app_choices_rec.condition_code
,x_decision => app_choices_rec.decision
,x_decision_date => app_choices_rec.decision_date
,x_decision_number => app_choices_rec.decision_number
,x_reply => app_choices_rec.reply
,x_summary_of_cond => app_choices_rec.summary_of_cond
,x_choice_cancelled => app_choices_rec.choice_cancelled
,x_action => app_choices_rec.action
,x_substitution => app_choices_rec.substitution
,x_date_substituted => app_choices_rec.date_substituted
,x_prev_institution => app_choices_rec.prev_institution
,x_prev_course => app_choices_rec.prev_course
,x_prev_campus => app_choices_rec.prev_campus
,x_ucas_amendment => app_choices_rec.ucas_amendment
,x_withdrawal_reason => app_choices_rec.withdrawal_reason
,x_offer_course => app_choices_rec.offer_course
,x_offer_campus => app_choices_rec.offer_campus
,x_offer_crse_length => app_choices_rec.offer_crse_length
,x_offer_entry_month => app_choices_rec.offer_entry_month
,x_offer_entry_year => app_choices_rec.offer_entry_year
,x_offer_entry_point => app_choices_rec.offer_entry_point
,x_offer_text => app_choices_rec.offer_text
,x_export_to_oss_status => l_export_to_oss_status
,x_error_code => l_ch_error
,x_batch_id => l_ch_batch_id
,x_request_id => l_conc_request_id
,x_mode => 'R'
,x_extra_round_nbr => app_choices_rec.extra_round_nbr
,x_system_code => app_choices_rec.system_code
,x_part_time => app_choices_rec.part_time
,x_interview => app_choices_rec.interview
,x_late_application => app_choices_rec.late_application
,x_modular => app_choices_rec.modular
,x_residential => app_choices_rec.residential
,x_ucas_cycle => app_choices_rec.ucas_cycle);
SELECT match_set_id
FROM igs_pe_match_sets
WHERE source_type_id = p_source_type_id;
smaddali checking if record exists before inserting into igs_uc_old_oustat if so then update ,
else insert. Also added code to get unit set cd for point of entry and use it in finding
matching admissions application, for bug 2630219
ayedubat 24-MAR-2003 Added the Logic to identify the Applications Choices modified by UCAS which already went UF
and institution asking for change in Course, Entry Month, Entry Year or Point of Entry for
the Bug, 2669209
rbezawad 7-Oct-03 Added a validation before setting the export_to_oss status to 'UF' which will check for
ucas decision is 'U' or 'A' and the reply is 'F'. Bug: 3179630
jchakrab 10-Oct-2005 Modified for 4424068 - added CANCEL functionality for prog version change
jchin 20-jan-2006 Modified for R12 Perf improvements - bug 3691277 and 3691250
jchakrab 04-May-2006 Modified for 5203018 - modified to close c_cancel_appl cursor correctly
jchakrab 22-May-2006 Modified for 5165624
***************************************************************** */
l_description igs_ad_batc_def_det_all.description%TYPE ;
SELECT ch.app_no , ch.choice_no , ch.deferred , ch.batch_id , ch.export_to_oss_status ,
ch.point_of_entry , ch.oss_program_code, ch.oss_location ,ch.oss_program_version ,
ch.oss_attendance_type ,ch.oss_attendance_mode , ch.system_code, ch.ucas_cycle,
ch.entry_year, ch.entry_month, ch.decision, ch.reply
FROM igs_uc_app_choices ch
WHERE ch.app_no = NVL(p_app_no,ch.app_no) AND
ch.export_to_oss_status = 'NEW' AND
ch.choice_no = NVL(p_choice_no , ch.choice_no) AND
ch.institute_code = (SELECT df.current_inst_code FROM igs_uc_defaults df
WHERE df.system_code = ch.system_code)
ORDER BY ch.ucas_cycle, ch.app_no, ch.choice_no;
SELECT ch.batch_id, ch.deferred , ch.app_no , ch.choice_no , ch.system_code, ch.ucas_cycle, ch.entry_year, ch.entry_month
FROM igs_uc_app_choices ch
WHERE ch.app_no = NVL(p_app_no,ch.app_no) AND
ch.export_to_oss_status ='OO' AND
ch.choice_no = NVL(p_choice_no , ch.choice_no) AND
ch.institute_code = (SELECT df.current_inst_code FROM igs_uc_defaults df
WHERE df.system_code = ch.system_code)
ORDER BY ch.ucas_cycle, ch.app_no, ch.choice_no;
SELECT c.person_id , c.admission_appl_number
FROM igs_uc_applicants a , igs_uc_app_choices b , igs_ad_appl_all c
WHERE a.app_no = b.app_no AND
a.oss_person_id = c.person_id AND
TO_CHAR(a.app_no) = c.alt_appl_id AND
b.choice_no = c.choice_number AND
b.app_no = cp_app_no AND
b.choice_no = cp_choice_no ;
SELECT c.person_id,c.admission_appl_number ,b.choice_no
FROM igs_uc_applicants a , igs_uc_app_choices b , igs_ad_appl_all c
WHERE a.app_no = b.app_no AND
a.oss_person_id = c.person_id AND
TO_CHAR(a.app_no) = c.alt_appl_id AND
b.choice_no = c.choice_number AND
c.acad_cal_type = l_aca_cal_type AND
c.acad_ci_sequence_number = l_aca_seq_no AND
c.adm_cal_type = l_adm_cal_type AND
c.adm_ci_sequence_number = l_adm_seq_no AND
b.app_no = cp_app_no AND
b.choice_no = cp_choice_no AND
igs_ad_gen_007.Admp_Get_Saas(c.adm_appl_status) <> 'COMPLETED';
SELECT 'X'
FROM igs_uc_applicants a , igs_uc_app_choices b , igs_ad_appl_all c,
igs_ad_ps_appl_all d , igs_ad_ps_appl_inst_all e
WHERE a.app_no = b.app_no AND
a.oss_person_id = c.person_id AND
TO_CHAR(a.app_no) = c.alt_appl_id AND
b.choice_no = c.choice_number AND
c.person_id = d.person_id AND
c.admission_appl_number = cp_adm_appl_number AND
c.admission_appl_number = d.admission_appl_number AND
d.person_id = e.person_id AND
d.admission_appl_number = e.admission_appl_number AND
d.nominated_course_cd = e.nominated_course_cd AND
b.oss_program_code = e.course_cd AND
b.oss_program_version = e.crv_version_number AND
b.oss_location = e.location_cd AND
b.oss_attendance_type = e.attendance_type AND
b.oss_attendance_mode = e.attendance_mode AND
e.unit_set_cd = cp_unit_set_cd AND
e.us_version_number = cp_us_version_number AND
b.app_no = cp_app_no AND
b.choice_no = cp_choice_no
ORDER BY e.preference_number ASC ;
SELECT
c.person_id , c.admission_appl_number ,c.choice_number, e.sequence_number,
e.decision_date , e.decision_reason_id , e.decision_make_id ,e.adm_outcome_status ,
e.nominated_course_cd , c.adm_cal_type , c.adm_ci_sequence_number,
c.acad_cal_type , c.acad_ci_sequence_number ,c.admission_cat ,c.s_admission_process_type
FROM igs_uc_applicants a , igs_uc_app_choices b , igs_ad_appl_all c,
igs_ad_ps_appl_all d , igs_ad_ps_appl_inst_all e
WHERE a.app_no = b.app_no AND
a.oss_person_id = c.person_id AND
TO_CHAR(a.app_no) = c.alt_appl_id AND
b.choice_no = c.choice_number AND
c.person_id = d.person_id AND
c.admission_appl_number = cp_adm_appl_number AND
c.admission_appl_number = d.admission_appl_number AND
d.person_id = e.person_id AND
d.admission_appl_number = e.admission_appl_number AND
d.nominated_course_cd = e.nominated_course_cd AND
b.oss_program_code = e.course_cd AND
b.oss_program_version <> e.crv_version_number AND
b.oss_location = e.location_cd AND
b.oss_attendance_type = e.attendance_type AND
b.oss_attendance_mode = e.attendance_mode AND
e.unit_set_cd = cp_unit_set_cd AND
e.us_version_number = cp_us_version_number AND
b.app_no = cp_app_no AND
b.choice_no = cp_choice_no AND
igs_ad_gen_007.Admp_Get_Saas(c.adm_appl_status) = 'RECEIVED' AND
igs_ad_gen_008.Admp_Get_Saos(e.adm_outcome_status) IN ('PENDING','SUSPEND');
SELECT igs_ad_interface_ctl_s.NEXTVAL
FROM dual ;
SELECT COUNT(*)
FROM igs_uc_applicants a , igs_ad_appl_all c, igs_ad_ps_appl_all d ,
igs_ad_ps_appl_inst_all e , igs_ad_ou_stat ou
WHERE a.app_no = cp_app_no AND
a.oss_person_id = c.person_id AND
TO_CHAR(a.app_no) = c.alt_appl_id AND
c.choice_number = cp_choice_number AND
c.person_id = d.person_id AND
c.admission_appl_number = d.admission_appl_number AND
d.person_id = e.person_id AND
d.admission_appl_number = e.admission_appl_number AND
d.nominated_course_cd = e.nominated_course_cd AND
e.adm_outcome_status = ou.adm_outcome_status AND
ou.s_adm_outcome_status NOT IN ('SUSPEND','VOIDED','WITHDRAWN');
SELECT c.person_id , c.admission_appl_number ,c.choice_number, e.sequence_number,
e.decision_date , e.decision_reason_id , e.decision_make_id ,e.adm_outcome_status ,
e.nominated_course_cd , c.adm_cal_type , c.adm_ci_sequence_number,
c.acad_cal_type , c.acad_ci_sequence_number ,c.admission_cat ,c.s_admission_process_type
FROM igs_uc_applicants a , igs_ad_appl_all c, igs_ad_ps_appl_all d ,
igs_ad_ps_appl_inst_all e , igs_ad_ou_stat ou
WHERE a.app_no = cp_app_no AND
a.oss_person_id = c.person_id AND
TO_CHAR(a.app_no) = c.alt_appl_id AND
c.choice_number = cp_choice_number AND
c.person_id = d.person_id AND
c.admission_appl_number = d.admission_appl_number AND
d.person_id = e.person_id AND
d.admission_appl_number = e.admission_appl_number AND
d.nominated_course_cd = e.nominated_course_cd AND
e.adm_outcome_status = ou.adm_outcome_status AND
ou.s_adm_outcome_status NOT IN ('SUSPEND','VOIDED','WITHDRAWN');
SELECT a.ROWID , a.*
FROM igs_ad_ps_appl_inst_all a
WHERE a.person_id = cp_person_id AND
a.admission_appl_number = cp_admission_appl_number
ORDER BY a.preference_number DESC ;
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 ;
SELECT ou.ROWID , ou.*
FROM igs_uc_old_oustat ou
WHERE app_no = cp_app_no AND
choice_no = cp_choice_no ;
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.COURSE_CD = P_COURSE_CD
AND COUS.CRV_VERSION_NUMBER = P_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 = P_SEQ_NO
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.COURSE_CD = P_COURSE_CD
AND COOUS.CRV_VERSION_NUMBER = P_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 = P_SEQ_NO;
SELECT *
FROM igs_uc_defaults def
WHERE def.system_code = NVL(cp_system_code, def.system_code);
SELECT s_Adm_outcome_status
FROM igs_Ad_ou_stat
WHERE adm_outcome_status = cp_out_stat ;
SELECT transaction_type,program_code,entry_month,entry_year,entry_point
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
ORDER BY tran.uc_tran_id DESC;
SELECT apl.person_id,apl.admission_appl_number
FROM IGS_UC_APP_CHOICES uac,
IGS_UC_APPLICANTS ua,
IGS_AD_APPL_ALL apl,
IGS_AD_PS_APPL_ALL aplps,
IGS_AD_PS_APPL_INST_ALL aplinst
WHERE uac.app_no = p_app_no
AND uac.choice_no = p_choice_no
AND uac.ucas_cycle= p_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 apl.person_id = aplps.person_id
AND apl.admission_appl_number = aplps.admission_appl_number
AND aplps.person_id = aplinst.person_id
AND aplps.admission_appl_number = aplinst.admission_appl_number
AND aplps.nominated_course_cd = aplinst.nominated_course_cd
AND igs_ad_gen_007.admp_get_saas(apl.adm_appl_status) = 'COMPLETED'
AND igs_ad_gen_008.Admp_Get_Saos(aplinst.adm_outcome_status) <> 'CANCELLED';
SELECT
'X'
FROM
igs_uc_app_choices uac,
igs_uc_sys_calndrs ucal,
igs_ad_appl_all apl,
igs_ad_ps_appl_inst_all aplinst,
igs_en_unit_set us,
igs_en_unit_set_cat usc,
igs_ps_us_prenr_cfg cnfg,
igs_uc_map_out_stat mos,
igs_uc_map_off_resp mor
WHERE uac.app_no = cp_app_no
AND uac.choice_no = cp_choice_no
AND uac.ucas_cycle= cp_ucas_cycle
-- Comparing the Entry Year and Entry Month mapping with the Calendars
AND ucal.system_code = uac.system_code
AND uac.entry_year = ucal.entry_year
AND (uac.entry_month = ucal.entry_month OR ucal.entry_month = 0)
AND apl.person_id = cp_person_id
AND apl.admission_appl_number = cp_admission_appl_number
AND apl.acad_cal_type = ucal.aca_cal_type
AND apl.acad_ci_sequence_number = ucal.aca_cal_seq_no
AND apl.adm_cal_type = ucal.adm_cal_type
AND apl.adm_ci_sequence_number = ucal.adm_cal_seq_no
-- Comparing the OSS Program Instance
AND aplinst.person_id = apl.person_id
AND aplinst.admission_appl_number = apl.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
-- Comparing the Final Unit Set
AND aplinst.unit_set_cd = us.unit_set_cd
AND aplinst.us_version_number = 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 = cnfg.unit_set_cd
AND cnfg.sequence_no = NVL(uac.point_of_entry,1)
-- Comparing the Admission Outcome Status
AND mos.system_code = uac.system_code
AND mos.decision_code = uac.decision
AND mos.default_ind = 'Y'
AND mos.closed_ind <> 'Y'
AND mos.adm_outcome_status = aplinst.adm_outcome_status
-- Comparing the Admission Offer Response Status
AND(uac.reply IS NULL OR
(mor.system_code = uac.system_code
AND mor.decision_code = uac.decision
AND mor.reply_code = uac.reply
AND mor.closed_ind <> 'Y'
AND mor.adm_offer_resp_status = aplinst.adm_offer_resp_status ) );
SELECT DISTINCT a.system_code, a.entry_year, a.entry_month
FROM igs_uc_app_choices a
WHERE a.app_no = NVL(p_app_no, a.app_no)
AND a.choice_no = NVL(p_choice_no,a.choice_no)
AND a.export_to_oss_status = 'NEW'
AND a.institute_code IN (SELECT df.current_inst_code FROM igs_uc_defaults df);
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;
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 => c_defaults_rec.decision_make_id,
x_decision_date => SYSDATE,
x_decision_reason_id => c_defaults_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.insert_row (
X_ROWID => l_rowid
,X_APP_NO => c_new_app_ch_rec.app_no
,X_CHOICE_NO => c_new_app_ch_rec.choice_no
,X_OLD_OUTCOME_STATUS => c_cancel_appl_rec.adm_outcome_status
,X_DECISION_DATE => c_cancel_appl_rec.decision_date
,X_DECISION_REASON_ID => c_cancel_appl_rec.decision_reason_id
,X_DECISION_MAKE_ID => c_cancel_appl_rec.decision_make_id
,X_MODE => 'R'
) ;
igs_uc_old_oustat_pkg.update_row (
X_ROWID => c_old_oustat_rec.ROWID
,X_APP_NO => c_old_oustat_rec.app_no
,X_CHOICE_NO => c_old_oustat_rec.choice_no
,X_OLD_OUTCOME_STATUS => c_cancel_appl_rec.adm_outcome_status
,X_DECISION_DATE => c_cancel_appl_rec.decision_date
,X_DECISION_REASON_ID => c_cancel_appl_rec.decision_reason_id
,X_DECISION_MAKE_ID => c_cancel_appl_rec.decision_make_id
,X_MODE => 'R'
) ;
/* call the insert_row of the Admission Decision Import Interface table, IGS_AD_ADMDE_INT_ALL */
l_interface_run_id := NULL ;
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_dec_batch_id,
x_person_id => c_cancel_appl_rec.person_id,
x_admission_appl_number => c_cancel_appl_rec.admission_appl_number,
x_nominated_course_cd => c_cancel_appl_rec.nominated_course_cd,
x_sequence_number => c_cancel_appl_rec.sequence_number,
x_adm_outcome_status => IGS_AD_GEN_009.Admp_Get_Sys_Aos('CANCELLED'),
x_decision_make_id => c_defaults_rec.decision_make_id,
x_decision_date => TRUNC(SYSDATE),
x_decision_reason_id => c_defaults_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 => 'N' );
igs_ad_ps_appl_inst_pkg.update_row (
x_rowid => c_upd_appl_inst_rec.ROWID ,
x_person_id => c_upd_appl_inst_rec.person_id ,
x_admission_appl_number => c_upd_appl_inst_rec.admission_appl_number ,
x_nominated_course_cd => c_upd_appl_inst_rec.nominated_course_cd ,
x_sequence_number => c_upd_appl_inst_rec.sequence_number ,
x_predicted_gpa => c_upd_appl_inst_rec.predicted_gpa ,
x_academic_index => c_upd_appl_inst_rec.academic_index ,
x_adm_cal_type => c_upd_appl_inst_rec.adm_cal_type ,
x_app_file_location => c_upd_appl_inst_rec.app_file_location ,
x_adm_ci_sequence_number => c_upd_appl_inst_rec.adm_ci_sequence_number ,
x_course_cd => c_upd_appl_inst_rec.course_cd ,
x_app_source_id => c_upd_appl_inst_rec.app_source_id ,
x_crv_version_number => c_upd_appl_inst_rec.crv_version_number ,
x_waitlist_rank => c_upd_appl_inst_rec.waitlist_rank ,
x_location_cd => c_upd_appl_inst_rec.location_cd ,
x_attent_other_inst_cd => c_upd_appl_inst_rec.attent_other_inst_cd ,
x_attendance_mode => c_upd_appl_inst_rec.attendance_mode ,
x_edu_goal_prior_enroll_id => c_upd_appl_inst_rec.edu_goal_prior_enroll_id ,
x_attendance_type => c_upd_appl_inst_rec.attendance_type ,
x_decision_make_id => c_upd_appl_inst_rec.decision_make_id ,
x_unit_set_cd => c_upd_appl_inst_rec.unit_set_cd ,
x_decision_date => c_upd_appl_inst_rec.decision_date ,
x_attribute_category => c_upd_appl_inst_rec.attribute_category ,
x_attribute1 => c_upd_appl_inst_rec.attribute1 ,
x_attribute2 => c_upd_appl_inst_rec.attribute2 ,
x_attribute3 => c_upd_appl_inst_rec.attribute3 ,
x_attribute4 => c_upd_appl_inst_rec.attribute4 ,
x_attribute5 => c_upd_appl_inst_rec.attribute5 ,
x_attribute6 => c_upd_appl_inst_rec.attribute6 ,
x_attribute7 => c_upd_appl_inst_rec.attribute7 ,
x_attribute8 => c_upd_appl_inst_rec.attribute8 ,
x_attribute9 => c_upd_appl_inst_rec.attribute9 ,
x_attribute10 => c_upd_appl_inst_rec.attribute10 ,
x_attribute11 => c_upd_appl_inst_rec.attribute11 ,
x_attribute12 => c_upd_appl_inst_rec.attribute12 ,
x_attribute13 => c_upd_appl_inst_rec.attribute13 ,
x_attribute14 => c_upd_appl_inst_rec.attribute14 ,
x_attribute15 => c_upd_appl_inst_rec.attribute15 ,
x_attribute16 => c_upd_appl_inst_rec.attribute16 ,
x_attribute17 => c_upd_appl_inst_rec.attribute17 ,
x_attribute18 => c_upd_appl_inst_rec.attribute18 ,
x_attribute19 => c_upd_appl_inst_rec.attribute19 ,
x_attribute20 => c_upd_appl_inst_rec.attribute20 ,
x_decision_reason_id => c_upd_appl_inst_rec.decision_reason_id ,
x_us_version_number => c_upd_appl_inst_rec.us_version_number ,
x_decision_notes => c_upd_appl_inst_rec.decision_notes ,
x_pending_reason_id => c_upd_appl_inst_rec.pending_reason_id ,
x_preference_number => c_upd_appl_inst_rec.preference_number ,
x_adm_doc_status => c_upd_appl_inst_rec.adm_doc_status ,
x_adm_entry_qual_status => c_upd_appl_inst_rec.adm_entry_qual_status ,
x_deficiency_in_prep => c_upd_appl_inst_rec.deficiency_in_prep ,
x_late_adm_fee_status => c_upd_appl_inst_rec.late_adm_fee_status ,
x_spl_consider_comments => c_upd_appl_inst_rec.spl_consider_comments ,
x_apply_for_finaid => c_upd_appl_inst_rec.apply_for_finaid ,
x_finaid_apply_date => c_upd_appl_inst_rec.finaid_apply_date ,
x_adm_outcome_status => c_upd_appl_inst_rec.adm_outcome_status ,
x_adm_otcm_stat_auth_per_id => c_upd_appl_inst_rec.adm_otcm_status_auth_person_id ,
x_adm_outcome_status_auth_dt => c_upd_appl_inst_rec.adm_outcome_status_auth_dt ,
x_adm_outcome_status_reason => c_upd_appl_inst_rec.adm_outcome_status_reason ,
x_offer_dt => c_upd_appl_inst_rec.offer_dt ,
x_offer_response_dt => c_upd_appl_inst_rec.offer_response_dt ,
x_prpsd_commencement_dt => c_upd_appl_inst_rec.prpsd_commencement_dt,
x_adm_cndtnl_offer_status => c_upd_appl_inst_rec.adm_cndtnl_offer_status ,
x_cndtnl_offer_satisfied_dt => c_upd_appl_inst_rec.cndtnl_offer_satisfied_dt ,
x_cndnl_ofr_must_be_stsfd_ind => c_upd_appl_inst_rec.cndtnl_offer_must_be_stsfd_ind ,
x_adm_offer_resp_status => c_upd_appl_inst_rec.adm_offer_resp_status ,
x_actual_response_dt => c_upd_appl_inst_rec.actual_response_dt,
x_adm_offer_dfrmnt_status => c_upd_appl_inst_rec.adm_offer_dfrmnt_status ,
x_deferred_adm_cal_type => c_upd_appl_inst_rec.deferred_adm_cal_type ,
x_deferred_adm_ci_sequence_num => c_upd_appl_inst_rec.deferred_adm_ci_sequence_num ,
x_deferred_tracking_id => c_upd_appl_inst_rec.deferred_tracking_id ,
x_ass_rank => c_upd_appl_inst_rec.ass_rank ,
x_secondary_ass_rank => c_upd_appl_inst_rec.secondary_ass_rank ,
x_intr_accept_advice_num => c_upd_appl_inst_rec.intrntnl_acceptance_advice_num ,
x_ass_tracking_id => c_upd_appl_inst_rec.ass_tracking_id ,
x_fee_cat => c_upd_appl_inst_rec.fee_cat ,
x_hecs_payment_option => c_upd_appl_inst_rec.hecs_payment_option ,
x_expected_completion_yr => c_upd_appl_inst_rec.expected_completion_yr ,
x_expected_completion_perd => c_upd_appl_inst_rec.expected_completion_perd,
x_correspondence_cat => c_upd_appl_inst_rec.correspondence_cat ,
x_enrolment_cat => c_upd_appl_inst_rec.enrolment_cat ,
x_funding_source => c_upd_appl_inst_rec.funding_source ,
x_applicant_acptnce_cndtn => c_upd_appl_inst_rec.applicant_acptnce_cndtn ,
x_cndtnl_offer_cndtn => c_upd_appl_inst_rec.cndtnl_offer_cndtn ,
x_mode => 'R' ,
x_ss_application_id => c_upd_appl_inst_rec.ss_application_id ,
x_ss_pwd => c_upd_appl_inst_rec.ss_pwd ,
x_authorized_dt => c_upd_appl_inst_rec.authorized_dt ,
x_authorizing_pers_id => c_upd_appl_inst_rec.authorizing_pers_id ,
x_entry_status => c_upd_appl_inst_rec.entry_status ,
x_entry_level => c_upd_appl_inst_rec.entry_level ,
x_sch_apl_to_id => c_upd_appl_inst_rec.sch_apl_to_id ,
x_idx_calc_date => c_upd_appl_inst_rec.idx_calc_date ,
x_waitlist_status => c_upd_appl_inst_rec.waitlist_status ,
x_attribute21 => c_upd_appl_inst_rec.attribute21 ,
x_attribute22 => c_upd_appl_inst_rec.attribute22 ,
x_attribute23 => c_upd_appl_inst_rec.attribute23 ,
x_attribute24 => c_upd_appl_inst_rec.attribute24 ,
x_attribute25 => c_upd_appl_inst_rec.attribute25 ,
x_attribute26 => c_upd_appl_inst_rec.attribute26 ,
x_attribute27 => c_upd_appl_inst_rec.attribute27 ,
x_attribute28 => c_upd_appl_inst_rec.attribute28 ,
x_attribute29 => c_upd_appl_inst_rec.attribute29 ,
x_attribute30 => c_upd_appl_inst_rec.attribute30 ,
x_attribute31 => c_upd_appl_inst_rec.attribute31 ,
x_attribute32 => c_upd_appl_inst_rec.attribute32 ,
x_attribute33 => c_upd_appl_inst_rec.attribute33 ,
x_attribute34 => c_upd_appl_inst_rec.attribute34 ,
x_attribute35 => c_upd_appl_inst_rec.attribute35 ,
x_attribute36 => c_upd_appl_inst_rec.attribute36 ,
x_attribute37 => c_upd_appl_inst_rec.attribute37 ,
x_attribute38 => c_upd_appl_inst_rec.attribute38 ,
x_attribute39 => c_upd_appl_inst_rec.attribute39 ,
x_attribute40 => c_upd_appl_inst_rec.attribute40 ,
x_fut_acad_cal_type => c_upd_appl_inst_rec.future_acad_cal_type ,
x_fut_acad_ci_sequence_number => c_upd_appl_inst_rec.future_acad_ci_sequence_number ,
x_fut_adm_cal_type => c_upd_appl_inst_rec.future_adm_cal_type ,
x_fut_adm_ci_sequence_number => c_upd_appl_inst_rec.future_adm_ci_sequence_number ,
x_prev_term_adm_appl_number => c_upd_appl_inst_rec.previous_term_adm_appl_number ,
x_prev_term_sequence_number => c_upd_appl_inst_rec.previous_term_sequence_number ,
x_fut_term_adm_appl_number => c_upd_appl_inst_rec.future_term_adm_appl_number ,
x_fut_term_sequence_number => c_upd_appl_inst_rec.future_term_sequence_number ,
x_def_acad_cal_type => c_upd_appl_inst_rec.def_acad_cal_type ,
x_def_acad_ci_sequence_num => c_upd_appl_inst_rec.def_acad_ci_sequence_num ,
x_def_prev_term_adm_appl_num => c_upd_appl_inst_rec.def_prev_term_adm_appl_num ,
x_def_prev_appl_sequence_num => c_upd_appl_inst_rec.def_prev_appl_sequence_num ,
x_def_term_adm_appl_num => c_upd_appl_inst_rec.def_term_adm_appl_num ,
x_def_appl_sequence_num => c_upd_appl_inst_rec.def_appl_sequence_num,
X_APPL_INST_STATUS => c_upd_appl_inst_rec.appl_inst_status,
x_ais_reason => c_upd_appl_inst_rec.ais_reason,
x_decline_ofr_reason => c_upd_appl_inst_rec.decline_ofr_reason
) ;
igs_uc_old_oustat_pkg.insert_row (
X_ROWID => l_rowid
,X_APP_NO => c_new_app_ch_rec.app_no
,X_CHOICE_NO => c_new_app_ch_rec.choice_no
,X_OLD_OUTCOME_STATUS => c_obsol_appl_rec.adm_outcome_status
,X_DECISION_DATE => c_obsol_appl_rec.decision_date
,X_DECISION_REASON_ID => c_obsol_appl_rec.decision_reason_id
,X_DECISION_MAKE_ID => c_obsol_appl_rec.decision_make_id
,X_MODE => 'R'
) ;
igs_uc_old_oustat_pkg.update_row (
X_ROWID => c_old_oustat_rec.ROWID
,X_APP_NO => c_old_oustat_rec.app_no
,X_CHOICE_NO => c_old_oustat_rec.choice_no
,X_OLD_OUTCOME_STATUS => c_obsol_appl_rec.adm_outcome_status
,X_DECISION_DATE => c_obsol_appl_rec.decision_date
,X_DECISION_REASON_ID => c_obsol_appl_rec.decision_reason_id
,X_DECISION_MAKE_ID => c_obsol_appl_rec.decision_make_id
,X_MODE => 'R'
) ;
/* call the insert_row of the Admission Decision Import Interface table, IGS_AD_ADMDE_INT_ALL */
l_interface_run_id := NULL ;
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_dec_batch_id,
x_person_id => c_obsol_appl_rec.person_id,
x_admission_appl_number => c_obsol_appl_rec.admission_appl_number,
x_nominated_course_cd => c_obsol_appl_rec.nominated_course_cd,
x_sequence_number => c_obsol_appl_rec.sequence_number,
x_adm_outcome_status => c_defaults_rec.obsolete_outcome_status,
x_decision_make_id => c_defaults_rec.decision_make_id,
x_decision_date => TRUNC(SYSDATE),
x_decision_reason_id => c_defaults_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 => 'N' );
igs_uc_app_choices_pkg.update_row
( x_rowid => c_upd_ch_rec.rowid
,x_app_choice_id => c_upd_ch_rec.app_choice_id
,x_app_id => c_upd_ch_rec.app_id
,x_app_no => c_upd_ch_rec.app_no
,x_choice_no => c_upd_ch_rec.choice_no
,x_last_change => c_upd_ch_rec.last_change
,x_institute_code => c_upd_ch_rec.institute_code
,x_ucas_program_code => c_upd_ch_rec.ucas_program_code
,x_oss_program_code => c_upd_ch_rec.oss_program_code
,x_oss_program_version => c_upd_ch_rec.oss_program_version
,x_oss_attendance_type => c_upd_ch_rec.oss_attendance_type
,x_oss_attendance_mode => c_upd_ch_rec.oss_attendance_mode
,x_campus => c_upd_ch_rec.campus
,x_oss_location => c_upd_ch_rec.oss_location
,x_faculty => c_upd_ch_rec.faculty
,x_entry_year => c_upd_ch_rec.entry_year
,x_entry_month => c_upd_ch_rec.entry_month
,x_point_of_entry => c_upd_ch_rec.point_of_entry
,x_home => c_upd_ch_rec.home
,x_deferred => c_upd_ch_rec.deferred
,x_route_b_pref_round => c_upd_ch_rec.route_b_pref_round
,x_route_b_actual_round => c_upd_ch_rec.route_b_actual_round
,x_condition_category => c_upd_ch_rec.condition_category
,x_condition_code => c_upd_ch_rec.condition_code
,x_decision => c_upd_ch_rec.decision
,x_decision_date => c_upd_ch_rec.decision_date
,x_decision_number => c_upd_ch_rec.decision_number
,x_reply => c_upd_ch_rec.reply
,x_summary_of_cond => c_upd_ch_rec.summary_of_cond
,x_choice_cancelled => c_upd_ch_rec.choice_cancelled
,x_action => c_upd_ch_rec.action
,x_substitution => c_upd_ch_rec.substitution
,x_date_substituted => c_upd_ch_rec.date_substituted
,x_prev_institution => c_upd_ch_rec.prev_institution
,x_prev_course => c_upd_ch_rec.prev_course
,x_prev_campus => c_upd_ch_rec.prev_campus
,x_ucas_amendment => c_upd_ch_rec.ucas_amendment
,x_withdrawal_reason => c_upd_ch_rec.withdrawal_reason
,x_offer_course => c_upd_ch_rec.offer_course
,x_offer_campus => c_upd_ch_rec.offer_campus
,x_offer_crse_length => c_upd_ch_rec.offer_crse_length
,x_offer_entry_month => c_upd_ch_rec.offer_entry_month
,x_offer_entry_year => c_upd_ch_rec.offer_entry_year
,x_offer_entry_point => c_upd_ch_rec.offer_entry_point
,x_offer_text => c_upd_ch_rec.offer_text
,x_export_to_oss_status => l_export_to_oss_status
,x_error_code => l_ch_error
,x_request_id => l_conc_request_id
,x_batch_id => l_ch_batch_id
,x_mode => 'R'
,x_extra_round_nbr => c_upd_ch_rec.extra_round_nbr
,x_system_code => c_upd_ch_rec.system_code
,x_part_time => c_upd_ch_rec.part_time
,x_interview => c_upd_ch_rec.interview
,x_late_application => c_upd_ch_rec.late_application
,x_modular => c_upd_ch_rec.modular
,x_residential => c_upd_ch_rec.residential
,x_ucas_cycle => c_upd_ch_rec.ucas_cycle);
igs_uc_app_choices_pkg.update_row
( x_rowid => c_upd_ch_rec.ROWID
,x_app_choice_id => c_upd_ch_rec.app_choice_id
,x_app_id => c_upd_ch_rec.app_id
,x_app_no => c_upd_ch_rec.app_no
,x_choice_no => c_upd_ch_rec.choice_no
,x_last_change => c_upd_ch_rec.last_change
,x_institute_code => c_upd_ch_rec.institute_code
,x_ucas_program_code => c_upd_ch_rec.ucas_program_code
,x_oss_program_code => c_upd_ch_rec.oss_program_code
,x_oss_program_version => c_upd_ch_rec.oss_program_version
,x_oss_attendance_type => c_upd_ch_rec.oss_attendance_type
,x_oss_attendance_mode => c_upd_ch_rec.oss_attendance_mode
,x_campus => c_upd_ch_rec.campus
,x_oss_location => c_upd_ch_rec.oss_location
,x_faculty => c_upd_ch_rec.faculty
,x_entry_year => c_upd_ch_rec.entry_year
,x_entry_month => c_upd_ch_rec.entry_month
,x_point_of_entry => c_upd_ch_rec.point_of_entry
,x_home => c_upd_ch_rec.home
,x_deferred => c_upd_ch_rec.deferred
,x_route_b_pref_round => c_upd_ch_rec.route_b_pref_round
,x_route_b_actual_round => c_upd_ch_rec.route_b_actual_round
,x_condition_category => c_upd_ch_rec.condition_category
,x_condition_code => c_upd_ch_rec.condition_code
,x_decision => c_upd_ch_rec.decision
,x_decision_date => c_upd_ch_rec.decision_date
,x_decision_number => c_upd_ch_rec.decision_number
,x_reply => c_upd_ch_rec.reply
,x_summary_of_cond => c_upd_ch_rec.summary_of_cond
,x_choice_cancelled => c_upd_ch_rec.choice_cancelled
,x_action => c_upd_ch_rec.action
,x_substitution => c_upd_ch_rec.substitution
,x_date_substituted => c_upd_ch_rec.date_substituted
,x_prev_institution => c_upd_ch_rec.prev_institution
,x_prev_course => c_upd_ch_rec.prev_course
,x_prev_campus => c_upd_ch_rec.prev_campus
,x_ucas_amendment => c_upd_ch_rec.ucas_amendment
,x_withdrawal_reason => c_upd_ch_rec.withdrawal_reason
,x_offer_course => c_upd_ch_rec.offer_course
,x_offer_campus => c_upd_ch_rec.offer_campus
,x_offer_crse_length => c_upd_ch_rec.offer_crse_length
,x_offer_entry_month => c_upd_ch_rec.offer_entry_month
,x_offer_entry_year => c_upd_ch_rec.offer_entry_year
,x_offer_entry_point => c_upd_ch_rec.offer_entry_point
,x_offer_text => c_upd_ch_rec.offer_text
,x_export_to_oss_status => l_export_to_oss_status
,x_error_code => l_ch_error
,x_request_id => l_conc_request_id
,x_batch_id => l_ch_batch_id
,x_mode => 'R'
,x_extra_round_nbr => c_upd_ch_rec.extra_round_nbr
,x_system_code => c_upd_ch_rec.system_code
,x_part_time => c_upd_ch_rec.part_time
,x_interview => c_upd_ch_rec.interview
,x_late_application => c_upd_ch_rec.late_application
,x_modular => c_upd_ch_rec.modular
,x_residential => c_upd_ch_rec.residential
,x_ucas_cycle => c_upd_ch_rec.ucas_cycle);
Purpose : To create new application/instance or update old applications for choices in status OC
Known limitations,enhancements,remarks:
Change History
Who When What
smaddali bug 2643048 UCFD102 build , modified cursors to add check for system_code
rbezawad 25-Feb-03 Modified w.r.t. Bug 2777247. Added code to insert record into IGS_AD_IMP_BATCH_DET table.
dsridhar 02-JUN-03 Modified the cursor 'c_oc_app_ch' in the procedure 'export_applications' to tune the
performance.Bug No: 2913922
dsridhar 27-OCT-03 Bug No: 2898153. Removed the space from the message name IGS_UC_EXPORT_APP_ERR.
jchakrab 20-feb-06 Modified for 3691186 - changed c_oc_app_ch to a ref cursor to execute different queries based on parameter values
***************************************************************** */
--jchakrab added for 3691186
TYPE t_oc_app_ch IS REF CURSOR;
SELECT ch.app_no , ch.choice_no , ch.batch_id, ch.ucas_cycle
FROM igs_uc_app_choices ch
WHERE ch.app_no = NVL(p_app_no,ch.app_no) AND
ch.export_to_oss_status = 'AP' AND
ch.choice_no = NVL(p_choice_no , ch.choice_no) AND
ch.institute_code = (SELECT df.current_inst_code FROM igs_uc_defaults df
WHERE df.system_code = ch.system_code)
ORDER BY ch.app_no , ch.choice_no ;
SELECT igs_ad_interface_batch_id_s.NEXTVAL
FROM dual;
SELECT source_type_id
FROM igs_pe_src_types_all
WHERE source_type LIKE 'UCAS APPL';
SELECT a.interface_id -- if application details import fails
FROM igs_ad_interface a, igs_ad_apl_int b , igs_ad_ps_appl_inst_int c ,
igs_uc_app_choices ch , igs_uc_applicants ap
WHERE a.batch_id = cp_batch_id AND
a.interface_id = b.interface_id AND
b.choice_number = ch.choice_no AND
b.interface_appl_id = c.interface_appl_id AND
( c.status IN ( '2','3') OR b.status IN ('2','3') ) AND
ap.app_no = ch.app_no AND
ap.oss_person_id = a.person_id AND
TO_CHAR(ap.app_no) = b.alt_appl_id AND
ch.app_no = cp_app_no AND
ch.choice_no = cp_choice_no
UNION -- if person or person details import fail
SELECT a.interface_id
FROM igs_ad_interface a, igs_ad_apl_int b , igs_ad_ps_appl_inst_int c ,
igs_uc_app_choices ch , igs_uc_applicants ap
WHERE a.batch_id = cp_batch_id AND
a.interface_id = b.interface_id AND
b.choice_number = ch.choice_no AND
b.interface_appl_id = c.interface_appl_id AND
b.status = '2' AND
c.status='2' AND
( a.record_status = '3' OR a.status IN ('2', '3' ) ) AND
ap.app_no = ch.app_no AND
ap.oss_person_id = a.person_id AND
TO_CHAR(ap.app_no) = b.alt_appl_id AND
ch.app_no = cp_app_no AND
ch.choice_no = cp_choice_no ;
SELECT a.oss_person_id, a.domicile_apr, b.party_number
FROM igs_uc_applicants a, hz_parties b
WHERE a.oss_person_id = b.party_id
AND app_no = cp_app_no;
SELECT admission_appl_number,
nominated_course_cd,
sequence_number
FROM igs_ad_ps_appl_inst_all
WHERE person_id = p_per_id;
SELECT COUNT(*)
FROM igs_he_ad_dtl_all
WHERE person_id = p_per_id;
SELECT had.ROWID ,had.*
FROM igs_he_ad_dtl_all had
WHERE person_id = l_per_id;
SELECT map2
FROM IGS_HE_CODE_MAP_VAL
WHERE association_code = cp_assoc
AND map1 = cp_map1;
SELECT DISTINCT AP.APP_NO
FROM IGS_UC_APP_CHOICES CH , IGS_UC_APPLICANTS AP, IGS_UC_DEFAULTS DF
WHERE AP.APP_NO = CH.APP_NO AND
CH.APP_NO = P_APP_NO AND
DF.SYSTEM_CODE = CH.SYSTEM_CODE AND
CH.EXPORT_TO_OSS_STATUS = 'OC' AND
CH.CHOICE_NO = P_CHOICE_NO AND
CH.INSTITUTE_CODE = DF.CURRENT_INST_CODE
ORDER BY AP.APP_NO;
SELECT DISTINCT AP.APP_NO
FROM IGS_UC_APP_CHOICES CH , IGS_UC_APPLICANTS AP, IGS_UC_DEFAULTS DF
WHERE AP.APP_NO = CH.APP_NO AND
CH.APP_NO = P_APP_NO AND
DF.SYSTEM_CODE = CH.SYSTEM_CODE AND
CH.EXPORT_TO_OSS_STATUS = 'OC' AND
CH.INSTITUTE_CODE = DF.CURRENT_INST_CODE
ORDER BY AP.APP_NO;
SELECT DISTINCT AP.APP_NO
FROM IGS_UC_APP_CHOICES CH , IGS_UC_APPLICANTS AP, IGS_UC_DEFAULTS DF
WHERE AP.APP_NO = CH.APP_NO AND
DF.SYSTEM_CODE = CH.SYSTEM_CODE AND
CH.EXPORT_TO_OSS_STATUS = 'OC' AND
CH.INSTITUTE_CODE = DF.CURRENT_INST_CODE
ORDER BY AP.APP_NO;
INSERT INTO igs_ad_imp_batch_det ( 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_imp_batch_id,
fnd_message.get_string('IGS','IGS_UC_EXP_TO_OSS_BATCH_ID'),
fnd_global.user_id,
SYSDATE,
fnd_global.user_id,
SYSDATE,
fnd_global.login_id,
DECODE(fnd_global.conc_request_id,-1,NULL,fnd_global.conc_request_id),
DECODE(fnd_global.conc_request_id,-1,NULL,fnd_global.prog_appl_id),
DECODE(fnd_global.conc_request_id,-1,NULL,SYSDATE),
DECODE(fnd_global.conc_request_id,-1,NULL,fnd_global.conc_program_id)
);
igs_he_ad_dtl_all_pkg.insert_row(
x_rowid => l_rowid,
x_org_id => NULL,
x_hesa_ad_dtl_id => l_count,
x_person_id => l_app_dtls.oss_person_id,
x_admission_appl_number => lv_ad_appl_inst.admission_appl_number,
x_nominated_course_cd => lv_ad_appl_inst.nominated_course_cd,
x_sequence_number => lv_ad_appl_inst.sequence_number,
x_occupation_cd => NULL,
x_domicile_cd => l_dom_cd,
x_social_class_cd => NULL,
x_special_student_cd => NULL,
x_mode => 'R' );
igs_he_ad_dtl_all_pkg.update_row (
x_mode => 'R',
x_rowid => had_rec.ROWID,
x_org_id => had_rec.org_id,
x_hesa_ad_dtl_id => had_rec.hesa_ad_dtl_id,
x_person_id => had_rec.person_id,
x_admission_appl_number => had_rec.admission_appl_number,
x_nominated_course_cd => had_rec.nominated_course_cd,
x_sequence_number => had_rec.sequence_number,
x_occupation_cd => had_rec.occupation_cd,
x_domicile_cd => NVL(l_dom_cd,had_rec.domicile_cd),
x_social_class_cd => had_rec.social_class_cd,
x_special_student_cd => had_rec.special_student_cd );
igs_uc_app_choices_pkg.update_row
( x_rowid => c_upd_ch_rec.ROWID
,x_app_choice_id => c_upd_ch_rec.app_choice_id
,x_app_id => c_upd_ch_rec.app_id
,x_app_no => c_upd_ch_rec.app_no
,x_choice_no => c_upd_ch_rec.choice_no
,x_last_change => c_upd_ch_rec.last_change
,x_institute_code => c_upd_ch_rec.institute_code
,x_ucas_program_code => c_upd_ch_rec.ucas_program_code
,x_oss_program_code => c_upd_ch_rec.oss_program_code
,x_oss_program_version => c_upd_ch_rec.oss_program_version
,x_oss_attendance_type => c_upd_ch_rec.oss_attendance_type
,x_oss_attendance_mode => c_upd_ch_rec.oss_attendance_mode
,x_campus => c_upd_ch_rec.campus
,x_oss_location => c_upd_ch_rec.oss_location
,x_faculty => c_upd_ch_rec.faculty
,x_entry_year => c_upd_ch_rec.entry_year
,x_entry_month => c_upd_ch_rec.entry_month
,x_point_of_entry => c_upd_ch_rec.point_of_entry
,x_home => c_upd_ch_rec.home
,x_deferred => c_upd_ch_rec.deferred
,x_route_b_pref_round => c_upd_ch_rec.route_b_pref_round
,x_route_b_actual_round => c_upd_ch_rec.route_b_actual_round
,x_condition_category => c_upd_ch_rec.condition_category
,x_condition_code => c_upd_ch_rec.condition_code
,x_decision => c_upd_ch_rec.decision
,x_decision_date => c_upd_ch_rec.decision_date
,x_decision_number => c_upd_ch_rec.decision_number
,x_reply => c_upd_ch_rec.reply
,x_summary_of_cond => c_upd_ch_rec.summary_of_cond
,x_choice_cancelled => c_upd_ch_rec.choice_cancelled
,x_action => c_upd_ch_rec.action
,x_substitution => c_upd_ch_rec.substitution
,x_date_substituted => c_upd_ch_rec.date_substituted
,x_prev_institution => c_upd_ch_rec.prev_institution
,x_prev_course => c_upd_ch_rec.prev_course
,x_prev_campus => c_upd_ch_rec.prev_campus
,x_ucas_amendment => c_upd_ch_rec.ucas_amendment
,x_withdrawal_reason => c_upd_ch_rec.withdrawal_reason
,x_offer_course => c_upd_ch_rec.offer_course
,x_offer_campus => c_upd_ch_rec.offer_campus
,x_offer_crse_length => c_upd_ch_rec.offer_crse_length
,x_offer_entry_month => c_upd_ch_rec.offer_entry_month
,x_offer_entry_year => c_upd_ch_rec.offer_entry_year
,x_offer_entry_point => c_upd_ch_rec.offer_entry_point
,x_offer_text => c_upd_ch_rec.offer_text
,x_export_to_oss_status => l_export_to_oss_status
,x_error_code => l_ch_error
,x_request_id => l_conc_request_id
,x_batch_id => l_ch_batch_id
,x_mode => 'R'
,x_extra_round_nbr => c_upd_ch_rec.extra_round_nbr
,x_system_code => c_upd_ch_rec.system_code
,x_part_time => c_upd_ch_rec.part_time
,x_interview => c_upd_ch_rec.interview
,x_late_application => c_upd_ch_rec.late_application
,x_modular => c_upd_ch_rec.modular
,x_residential => c_upd_ch_rec.residential
,x_ucas_cycle => c_upd_ch_rec.ucas_cycle);
SELECT DISTINCT a.system_code
FROM igs_uc_app_choices a
WHERE a.app_no = NVL(p_app_no, a.app_no) AND
a.choice_no = NVL(p_choice_no,a.choice_no) ;
SELECT *
FROM igs_uc_defaults def
WHERE system_code = cp_system_code;
SELECT DISTINCT entry_year, entry_month
FROM igs_uc_app_choices
WHERE app_no = NVL(p_app_no, app_no)
AND choice_no = NVL(p_choice_no, choice_no)
AND system_code = cp_system_code;
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 OR entry_month = 0)
ORDER BY entry_month DESC;
SELECT 'X'
FROM igs_ad_prcs_cat_step
WHERE admission_cat = cp_admission_cat
AND s_admission_process_type = cp_s_admission_process_type
AND s_admission_step_type = 'RECONSIDER';
SELECT admission_cat, s_admission_process_type
FROM igs_ad_ss_appl_typ
WHERE admission_application_type = cp_application_type
AND closed_ind = 'N';