[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT DISTINCT 1
FROM igs_ad_app_req
WHERE admission_appl_number = p_admission_appl_number
AND person_id = p_person_id
AND applicant_fee_type IN (SELECT code_id
FROM igs_ad_code_classes
WHERE class = 'SYS_FEE_TYPE'
AND system_status IN ('ENROLL_DEPOSIT')
AND CLASS_TYPE_CODE='ADM_CODE_CLASSES')
AND applicant_fee_status IN (SELECT code_id
FROM igs_ad_code_classes
WHERE class = 'SYS_FEE_STATUS'
AND system_status IN ('PAID','WAIVED')
AND CLASS_TYPE_CODE='ADM_CODE_CLASSES');
SELECT DISTINCT 1
FROM igs_pe_credentials pc,
igs_ad_cred_types act,
igs_tr_step ts
WHERE pc.person_id = p_person_id
AND act.credential_type_id = pc.credential_type_id
AND ts.step_catalog_cd = act.step_code
AND ts.tracking_id = p_tracking_id
AND ts.tracking_step_id = p_tracking_step_id;
SELECT DISTINCT 1
FROM igs_ad_test_results atr,
igs_ad_test_type att,
igs_tr_step ts
WHERE ts.tracking_id = p_tracking_id
AND ts.tracking_step_id = p_tracking_step_id
AND att.step_code =ts.step_catalog_cd
AND atr.admission_test_type = att.admission_test_type
AND atr.person_id =p_person_id ;
SELECT DISTINCT institution_code,
degree_attempted,
degree_earned
FROM igs_ad_acad_history_v
WHERE person_id = p_person_id
AND transcript_required = 'Y'
AND status = 'A' ;
SELECT DISTINCT 1
FROM igs_ad_acad_history_v a
WHERE person_id= p_person_id
AND institution_code = p_institution_code
AND NVL(degree_attempted,NVL(p_degree_attempted,'*')) = NVL(p_degree_attempted,'*')
AND NVL(degree_earned,NVL(p_degree_earned,'*')) = NVL(p_degree_earned,'*')
AND status = 'A'
AND exists (
SELECT 'x'
FROM igs_ad_transcript c
WHERE c.education_id = a.education_id
AND c.transcript_status = 'FINAL'
AND c.transcript_type ='OFFICIAL');
SELECT DISTINCT 1
FROM igs_ad_acad_history_v a
WHERE person_id = p_person_id
AND institution_code= p_institution_code
AND NVL(degree_attempted,NVL(p_degree_attempted,'*')) = NVL(p_degree_attempted,'*')
AND NVL(degree_earned,NVL(p_degree_earned,'*')) = NVL(p_degree_earned,'*')
AND status = 'A'
AND exists (
SELECT 'x'
FROM igs_ad_transcript c
WHERE c.education_id = a.education_id
AND c.transcript_status IN ('FINAL','PARTIAL')
AND c.transcript_type ='OFFICIAL');
SELECT DISTINCT 1
FROM igs_ad_acad_history_v a
WHERE person_id = p_person_id
AND institution_code= p_institution_code
AND NVL(degree_attempted,NVL(p_degree_attempted,'*')) = NVL(p_degree_attempted,'*')
AND NVL(degree_earned,NVL(p_degree_earned,'*')) = NVL(p_degree_earned,'*')
AND status = 'A'
AND exists (
SELECT 'x'
FROM igs_ad_transcript c
WHERE c.education_id = a.education_id
AND c.transcript_status = 'FINAL'
AND c.transcript_type ='UNOFFICIAL');
SELECT DISTINCT 1
FROM igs_ad_acad_history_v a
WHERE person_id = p_person_id
AND institution_code= p_institution_code
AND NVL(degree_attempted,NVL(p_degree_attempted,'*')) = NVL(p_degree_attempted,'*')
AND NVL(degree_earned,NVL(p_degree_earned,'*')) = NVL(p_degree_earned,'*')
AND status = 'A'
AND exists (
SELECT 'x'
FROM igs_ad_transcript c
WHERE c.education_id = a.education_id
AND c.transcript_status IN ('FINAL','PARTIAL')
AND c.transcript_type ='UNOFFICIAL');
SELECT DISTINCT 1
FROM igs_ad_appl_perstat aaps,
igs_ad_per_stm_typ apst,
igs_tr_step ts
WHERE ts.tracking_id = p_tracking_id
AND ts.tracking_step_id = p_tracking_step_id
AND apst.step_catalog_cd =ts.step_catalog_cd
AND aaps.persl_stat_type =apst.persl_stat_type
AND aaps.person_id =p_person_id
AND aaps.admission_appl_number = p_admission_appl_number ;
l_need_to_update BOOLEAN := FALSE;
l_need_to_update := FALSE;
l_need_to_update := TRUE;
l_need_to_update := FALSE;
l_need_to_update := FALSE;
IF l_need_to_update THEN
IF NOT upd_trk_step_complete ( p_tracking_id,
p_tracking_step_id,
p_s_tracking_step_type,
p_recipient_id
)
THEN
RAISE tr_stp_fail_exp;
END IF; -- Check whether the Trackings need to get updated or not.
l_need_to_update := FALSE;
l_need_to_update := TRUE;
l_need_to_update := FALSE;
l_need_to_update := FALSE;
IF l_need_to_update THEN
IF NOT upd_trk_step_complete ( p_tracking_id,
p_tracking_step_id,
p_s_tracking_step_type,
p_recipient_id
)
THEN
RAISE tr_stp_fail_exp;
END IF; -- Check whether the Trackings need to get updated or not.
l_need_to_update := FALSE;
l_need_to_update := TRUE;
l_need_to_update := FALSE;
l_need_to_update := FALSE;
IF l_need_to_update THEN
IF NOT upd_trk_step_complete ( p_tracking_id,
p_tracking_step_id,
p_s_tracking_step_type,
p_recipient_id
)
THEN
RAISE tr_stp_fail_exp;
END IF; -- Check whether the Trackings need to get updated or not.
l_need_to_update := FALSE;
l_need_to_update := TRUE;
l_need_to_update := FALSE;
l_need_to_update := FALSE;
IF l_need_to_update THEN
IF NOT upd_trk_step_complete ( p_tracking_id,
p_tracking_step_id,
p_s_tracking_step_type,
p_recipient_id
)
THEN
RAISE tr_stp_fail_exp;
END IF; -- Check whether the Trackings need to get updated or not.
SELECT aa.tracking_id
FROM igs_ad_aplins_admreq aa,
igs_tr_item ti,
igs_tr_status ts
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 -- changes made
AND ti.tracking_status = ts.tracking_status
AND ts.s_tracking_status = 'ACTIVE' ; -- the tracking status should be mapped to system tracking status of 'ACTIVE' ( rghosh, bug#2919317)
SELECT tracking_step_id, s_tracking_step_type, recipient_id
FROM igs_tr_step ts
WHERE ts.tracking_id = p_tracking_id
AND ts.step_completion_ind='N'
AND ts.by_pass_ind = 'N'
AND ts.completion_dt IS NULL
ORDER BY s_tracking_step_type;
SELECT
apai.person_id,
apai.admission_appl_number,
apai.nominated_course_cd,
apai.sequence_number
FROM
igs_ad_ps_appl_inst apai,
igs_ad_ou_stat aos,
igs_ad_doc_stat ads,
igs_ad_appl aa
WHERE
apai.person_id = nvl(p_person_id,apai.person_id) AND
aa.acad_cal_type = nvl(p_acad_cal_type,aa.acad_cal_type) AND
aa.acad_ci_sequence_number = nvl(p_acad_ci_sequence_number,aa.acad_ci_sequence_number) AND
aa.adm_cal_type = nvl(p_adm_cal_type,aa.adm_cal_type) AND
aa.adm_ci_sequence_number = nvl(p_adm_ci_sequence_number,aa.adm_ci_sequence_number) AND
aa.admission_cat = nvl(p_admission_cat,aa.admission_cat) AND
aa.s_admission_process_type = nvl(p_s_admission_process_type,aa.s_admission_process_type) AND
apai.nominated_course_cd = nvl(p_course_cd,apai.nominated_course_cd) AND
apai.admission_appl_number = nvl(p_admission_appl_number,apai.admission_appl_number) AND
apai.sequence_number = nvl(p_sequence_number,apai.sequence_number) AND
aos.s_adm_outcome_status = 'PENDING' AND
apai.adm_outcome_status = aos.adm_outcome_status AND
ads.s_adm_doc_status = 'PENDING' AND
apai.adm_doc_status = ads.adm_doc_status AND
aa.person_id=apai.person_id AND
aa.admission_appl_number = apai.admission_appl_number ;
SELECT 1
FROM igs_ad_aplins_admreq aa,
igs_tr_item ti,
igs_tr_status ts
WHERE aa.person_id = p_person_id
AND aa.admission_appl_number = p_admission_appl_number
AND aa.course_cd = p_course_cd
AND aa.sequence_number = p_sequence_number
AND aa.tracking_id = ti.tracking_id
AND ti.tracking_status = ts.tracking_status
AND ts.s_tracking_status = 'ACTIVE' ; -- the tracking status should be mapped to system tracking status of 'ACTIVE' ( rghosh, bug#2919317)
'SELECT
apai.person_id,
apai.admission_appl_number,
apai.nominated_course_cd,
apai.sequence_number
FROM
igs_ad_ps_appl_inst apai,
igs_ad_ou_stat aos,
igs_ad_doc_stat ads ,
igs_ad_appl aa
WHERE
apai.person_id IN ( '||lv_sql_stmt||') AND
aos.s_adm_outcome_status = ''PENDING'' AND
apai.adm_outcome_status = aos.adm_outcome_status AND
ads.s_adm_doc_status = ''PENDING'' AND
apai.adm_doc_status = ads.adm_doc_status AND
aa.person_id = apai.person_id AND
aa.admission_appl_number = apai.admission_appl_number AND
aa.acad_cal_type = nvl(:1,aa.acad_cal_type) AND
aa.acad_ci_sequence_number = DECODE ( :2, -1,aa.acad_ci_sequence_number, :2 ) AND
aa.adm_cal_type = nvl(:3,aa.adm_cal_type) AND
aa.adm_ci_sequence_number = DECODE ( :4, -1 ,aa.adm_ci_sequence_number , :4 ) AND
aa.admission_cat = nvl( :5,aa.admission_cat) AND
aa.s_admission_process_type = nvl( :6, aa.s_admission_process_type) '
USING p_person_id_group,l_acad_cal_type, l_acad_ci_sequence_number, l_acad_ci_sequence_number, l_adm_cal_type, l_adm_ci_sequence_number, l_adm_ci_sequence_number, l_admission_cat, l_s_admission_process_type;
'SELECT
apai.person_id,
apai.admission_appl_number,
apai.nominated_course_cd,
apai.sequence_number
FROM
igs_ad_ps_appl_inst apai,
igs_ad_ou_stat aos,
igs_ad_doc_stat ads ,
igs_ad_appl aa
WHERE
apai.person_id IN ( '||lv_sql_stmt||') AND
aos.s_adm_outcome_status = ''PENDING'' AND
apai.adm_outcome_status = aos.adm_outcome_status AND
ads.s_adm_doc_status = ''PENDING'' AND
apai.adm_doc_status = ads.adm_doc_status AND
aa.person_id = apai.person_id AND
aa.admission_appl_number = apai.admission_appl_number AND
aa.acad_cal_type = nvl(:1,aa.acad_cal_type) AND
aa.acad_ci_sequence_number = DECODE ( :2, -1,aa.acad_ci_sequence_number, :2 ) AND
aa.adm_cal_type = nvl(:3,aa.adm_cal_type) AND
aa.adm_ci_sequence_number = DECODE ( :4, -1 ,aa.adm_ci_sequence_number , :4 ) AND
aa.admission_cat = nvl( :5,aa.admission_cat) AND
aa.s_admission_process_type = nvl( :6, aa.s_admission_process_type) '
USING l_acad_cal_type, l_acad_ci_sequence_number, l_acad_ci_sequence_number, l_adm_cal_type, l_adm_ci_sequence_number, l_adm_ci_sequence_number, l_admission_cat, l_s_admission_process_type;