[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT start_dt ,
end_dt ,
alternate_code
FROM igs_ca_inst
WHERE cal_type = l_c_cal_type
AND sequence_number = l_c_seq_number;
This procedure deletes all non-numeric characters from SSN
*/
FUNCTION process_ssn (
p_ssn VARCHAR2
) RETURN VARCHAR2;
SELECT sup_cal_type,
sup_ci_sequence_number
FROM igs_ca_inst_rel
WHERE sub_cal_type = l_load_cal_type
AND sub_ci_sequence_number = l_load_cal_seq
AND sup_cal_type IN
(SELECT cal_type
FROM igs_ca_type_v
WHERE s_cal_cat='ACADEMIC');
SELECT opt_val
FROM igs_en_nsc_options
WHERE opt_type = 'DATE_GRAD';
SELECT DISTINCT spa.course_cd, spa.version_number, hp.party_id org_id, ihp.oss_org_unit_cd org_unit_cd
FROM igs_en_stdnt_ps_Att spa , igs_ps_ver vers, hz_parties hp, igs_pe_hz_parties ihp,
igs_or_status os
WHERE vers.course_cd=spa.course_cd
AND vers.version_number=spa.version_number
AND spa.cal_type = l_acad_cal_type
AND (
(spa.course_attempt_status NOT IN ('DELETED','UNCONFIRM')
AND cp_nslc_condition = '3' )
OR (cp_nslc_condition <> '3' AND spa.course_attempt_status = 'COMPLETED')
)
AND spa.future_dated_trans_flag NOT IN ('Y','C')
AND (vers.generic_course_ind = g_include_gen OR g_include_gen = 'Y')
AND ihp.oss_org_unit_cd = vers.responsible_org_unit_cd
AND ihp.party_id = hp.party_id
AND ihp.institution_cd=g_school_id
AND hp.party_id = ihp.party_id AND ihp.inst_org_ind = 'O'
AND ihp.ou_org_status = os.org_status AND os.s_org_status='ACTIVE';
SELECT spa.person_id FROM igs_en_stdnt_ps_att spa
WHERE course_cd=cp_course_cd
AND version_number=cp_course_ver
AND spa.cal_type = l_acad_cal_type
AND
(
(cp_nslc_condition = '3' AND discontinued_dt BETWEEN l_load_term_s_date AND l_load_term_e_date)
OR
(cp_nslc_condition IN ('1','3') AND l_grd_type = 'CRS_RQMNTS_COMPL_DATE'
AND course_rqrmnts_complete_dt BETWEEN l_load_term_s_date AND l_load_term_e_date)
OR
(cp_nslc_condition = '3' AND lapsed_dt BETWEEN l_load_term_s_date AND l_load_term_e_date)
OR
(cp_nslc_condition = '3' AND course_attempt_status = 'INTERMIT'
And (EXISTS ( SELECT spi.person_id,spi.course_cd
FROM igs_en_stdnt_ps_intm spi
WHERE spi.person_id = spa.person_id
AND spi.course_cd = cp_course_cd
AND spi.logical_delete_date = TO_DATE('31-12-4712','DD-MM-YYYY')
AND(
( spi.start_dt <= l_load_term_e_date AND spi.end_dt >=l_load_term_e_date)
OR
( spi.start_dt <= l_load_term_e_date AND spi.end_dt >= SYSDATE AND spi.end_dt >= l_load_term_s_date)
)
)
)
)
OR
(cp_nslc_condition in( '2','3') AND l_grd_type = 'CONFERRAL_DATE' AND
(EXISTS (select gr.person_id
FROM igs_gr_graduand_all gr,igs_en_spa_awd_aim spaa, igs_gr_stat grs
WHERE gr.graduand_status =grs. graduand_status
AND grs. s_graduand_status = 'GRADUATED'
AND gr.person_id = spaa.person_id AND gr.course_cd = spaa.course_cd
AND spa.person_id = spaa.person_id
AND spa.course_cd = spaa.course_cd
AND gr.award_cd = spaa.award_cd
AND spaa.conferral_date BETWEEN l_load_term_s_date AND l_load_term_e_date
)
)
)
OR
(cp_nslc_condition = '3'
AND course_attempt_status IN('ENROLLED','DISCONTIN', 'LAPSED','COMPLETED', 'INACTIVE')
AND (EXISTS (SELECT 'X' FROM igs_en_su_attempt b, igs_ca_load_to_teach_v lod
WHERE b.person_id = spa.person_id
and b.course_cd = spa.course_cd
and b.cal_type = lod.teach_cal_type
AND b.ci_sequence_number = lod.teach_ci_sequence_number
AND lod.load_cal_type = l_load_cal_type AND lod.load_ci_sequence_number = l_load_cal_seq
and b.unit_Attempt_Status <> 'UNCONFIRM'
)
)
)
);
SELECT cst_valid FROM igs_en_attrib_values
WHERE obj_type_id = 1
AND obj_id = cp_snapshot_id
AND attrib_id = 20
AND version = cp_person_id;
SELECT cst_valid
FROM igs_pe_eit perv
WHERE perv.person_id=cp_person_id
AND information_type = 'PE_STAT_RES_STATUS'
AND perv.pei_information1 NOT IN
(SELECT opt_val
FROM igs_en_nsc_options
WHERE opt_type = 'NC_STAT_CD'
)
AND EXISTS
(SELECT 'X'
FROM igs_pe_alt_pers_id pit,igs_pe_person_id_typ ppit
WHERE cp_person_id=pit.pe_person_id
AND pit.person_id_type = ppit.person_id_type
AND ppit.s_person_id_type = 'SSN'
AND pit.start_dt <= SYSDATE
AND NVL(pit.end_dt,SYSDATE) >= SYSDATE
);
SELECT cst_valid
FROM igs_or_unit_rel
WHERE parent_org_unit_cd=g_branch_id
CONNECT BY child_org_unit_cd=PRIOR parent_org_unit_cd
AND child_org_unit_cd<>g_branch_id AND igs_en_nsc_pkg.org_alt_check(child_org_unit_cd) IS NULL
START WITH child_org_unit_cd=cp_org_unit_cd
AND igs_en_nsc_pkg.org_alt_check(cp_org_unit_cd) IS NULL;
SELECT cst_valid
FROM igs_or_unit_rel
WHERE parent_org_unit_cd IN (SELECT ihp.oss_org_unit_cd org_unit_cd FROM hz_parties hp, igs_pe_hz_parties ihp
WHERE hp.party_id = ihp.party_id AND ihp.inst_org_ind = 'O'
AND ihp.institution_cd = g_school_id)
CONNECT BY child_org_unit_cd=PRIOR parent_org_unit_cd
AND igs_en_nsc_pkg.org_alt_check(child_org_unit_cd) IS NULL
START WITH child_org_unit_cd=cp_org_unit_cd
AND igs_en_nsc_pkg.org_alt_check(cp_org_unit_cd) IS NULL;
SELECT cst_valid FROM hz_parties hp, igs_pe_hz_parties ihp
WHERE hp.party_id = ihp.party_id AND ihp.inst_org_ind = 'O'
AND ihp.oss_org_unit_cd = cp_org_unit_cd
AND ihp.party_id = hp.party_id
AND ihp.institution_cd = g_school_id
AND igs_en_nsc_pkg.org_alt_check(cp_org_unit_cd) IS NULL;
SELECT DISTINCT org_structure_id
FROM igs_or_org_alt_ids alt
WHERE NVL(alt.end_date, SYSDATE) >= SYSDATE
AND NVL(alt.start_date,SYSDATE) <= SYSDATE
AND org_alternate_id = g_branch_id
AND org_alternate_id_type = l_branch_code
AND alt.org_structure_type = 'ORG_UNIT';
SELECT alt.org_structure_id
FROM igs_or_org_alt_ids alt, igs_or_institution inst
WHERE alt.org_structure_id = inst.institution_cd
AND alt.org_structure_type = 'INSTITUTE'
AND NVL(alt.end_date, SYSDATE) >= SYSDATE
AND inst.local_institution_ind = 'Y'
AND alt.org_alternate_id = p_school_id
ORDER BY 1;
SELECT rowid
FROM igs_en_doc_instances
WHERE doc_inst_id = p_inst_id;
savepoint delete_snapshot;
Put_Debug_Msg('calling delete row for ' ||l_rowid);
IGS_EN_DOC_INSTANCES_PKG.Delete_Row(
x_rowid => l_rowid
);
ROLLBACK TO DELETE_SNAPSHOT;
This is the procedure which deletes the snapshot from the database.
*/
PROCEDURE Delete_Snapshot_Request (
/*************************************************************
Change History
Who When What
(reverse chronological order - newest change first)
ckasu 17-JAN-2006 Added igs_ge_gen_003.set_org_id(NULL) as a part of bug#4958173.
***************************************************************/
errbuf OUT NOCOPY VARCHAR2, -- Request standard error string
retcode OUT NOCOPY NUMBER , -- Request standard return status
p_comment IN VARCHAR2, -- Runtime comments
p_inst_id IN igs_en_doc_instances.doc_inst_id%TYPE , -- Snapshot Id to delete
p_debug_mode IN VARCHAR2 := FND_API.G_FALSE
)IS
l_api_name CONSTANT VARCHAR2(30) := 'Delete_Snapshot_Request';
SELECT rowid
FROM igs_en_doc_instances
WHERE doc_inst_id = p_inst_id;
IGS_EN_DOC_INSTANCES_PKG.Delete_Row(
x_rowid => l_rowid
);
fnd_file.put_line(FND_FILE.LOG,'File deleted successfully.');
END Delete_Snapshot_Request;
Put_Debug_Msg('Error during EDS insertion: '||p_code);
IGS_EN_DOC_INSTANCES_PKG.Insert_row (
x_rowid => l_rowid,
x_doc_inst_id => l_inst_id,
x_doc_inst_name => 'NSCL Interface instance' ,
x_doc_inst_params => l_doc_inst_params
);
SELECT cal_type,ci_sequence_number
FROM igs_en_su_attempt
WHERE person_id = p_person_id
AND unit_attempt_status IN ('COMPLETED','ENROLLED','DUPLICATE')
AND ci_start_dt <= SYSDATE
ORDER by ci_end_dt DESC;
SELECT load_end_dt
FROM igs_ca_teach_to_load_v
WHERE teach_cal_type = p_cal_type
AND teach_ci_Sequence_number = p_ci_sequence_number
ORDER by load_end_dt DESC;
SELECT api_person_id
FROM igs_pe_alt_pers_id
WHERE pe_person_id = p_student_data.student_id
AND person_id_type
IN (SELECT person_id_type
FROM igs_pe_person_id_typ
WHERE s_person_id_type = 'SSN')
AND start_dt <= sysdate
AND end_dt IS NULL;
SELECT p.person_last_name surname,
p.person_first_name given_names,
p.person_middle_name middle_name,
p.person_name_suffix suffix,
pp.date_of_birth birth_dt
FROM hz_parties P, hz_person_profiles PP
WHERE p.party_id = p_student_data.student_id
AND p.party_id = pp.party_id
AND pp.content_source_type = 'USER_ENTERED'
AND SYSDATE BETWEEN pp.effective_start_date AND NVL(pp.effective_end_date,SYSDATE);
SELECT api_person_id
FROM igs_pe_alt_pers_id
WHERE pe_person_id = p_student_data.student_id
AND person_id_type
IN (SELECT person_id_type
FROM igs_pe_person_id_typ
WHERE s_person_id_type = 'SSN')
AND start_dt <= sysdate
AND end_dt IS NOT NULL
AND start_dt <> end_dt
ORDER BY end_dt DESC;
SELECT ppa.surname
FROM igs_pe_person_alias ppa,
igs_en_nsc_options eno
WHERE ppa.person_id = p_student_data.student_id
AND ppa.alias_type = eno.opt_val
AND eno.opt_type = 'LAST_NAME'
AND ppa.start_dt <= SYSDATE
AND (ppa.end_dt IS NULL OR ppa.end_dt > SYSDATE )
ORDER BY priority ASC, start_dt DESC;
SELECT addr_line_1 ,
addr_line_2 ,
city ,
decode(country,'US',state,'FO') state,
decode(country,'US',postal_code,' ' ) postal_code,
decode(country,'US','',country)
FROM igs_pe_person_addr
WHERE person_id = p_student_data.student_id
AND addr_type
IN (SELECT opt_val
FROM igs_en_nsc_options
WHERE opt_type = 'ADDR_TYPE')
AND (status = 'A'
AND SYSDATE BETWEEN NVL(start_dt,SYSDATE) AND NVL(end_dt,SYSDATE+1))
ORDER BY start_dt DESC;
SELECT 'Y'
FROM igs_pe_priv_level pl,igs_en_nsc_options op
WHERE pl.person_id = p_student_data.student_id
AND to_char(pl.data_group_id)=op.opt_val
AND op.opt_type = 'BLK_IND'
AND pl.start_date <= SYSDATE
AND pl.end_date IS NULL;
SELECT nominated_completion_yr ,
nominated_completion_perd
FROM igs_en_stdnt_ps_att_all
WHERE person_id = p_student_data.student_id
AND cal_type = p_acad_cal_type
AND course_attempt_status IN ('COMPLETED', 'ENROLLED', 'INACTIVE', 'INTERMIT')
ORDER BY NVL(nominated_completion_yr,-1) desc, decode(nominated_completion_perd,'E',1,'S',2,'M',3,0) desc;
SELECT substr(opt_type,10,1)
FROM igs_en_nsc_options
WHERE opt_val = l_status
AND opt_type IN
('ENR_STAT_F','ENR_STAT_H','ENR_STAT_L');
SELECT NVL(pp.date_of_death,SYSDATE)
FROM igs_pe_hz_parties pd, hz_person_profiles pp
WHERE DECODE(pp.date_of_death,NULL,NVL(pd.deceased_ind,'N'),'Y')='Y'
AND pp.party_id = p_student_id
AND pp.party_id = pd.party_id (+)
AND pp.content_source_type = 'USER_ENTERED'
AND SYSDATE BETWEEN pp.effective_start_date AND NVL(pp.effective_end_date,SYSDATE);
SELECT spi.start_dt,spi.end_dt,spi.intermission_type
FROM igs_en_stdnt_ps_intm spi,igs_en_intm_types spt
WHERE spi.person_id = p_student_id
AND spi.course_cd = l_c_course_cd
AND spi.intermission_type = spt.intermission_type
AND spi.logical_delete_date = TO_DATE('31-12-4712','DD-MM-YYYY')
AND (
(spt.APPR_REQD_IND = 'Y' AND spi.approved = 'Y')
OR
spt.APPR_REQD_IND = 'N'
)
AND(
( spi.start_dt <= p_e_date AND spi.end_dt >= p_e_date)
OR
( spi.start_dt <= p_e_date AND spi.end_dt >= SYSDATE AND spi.end_dt >= p_s_date)
)
ORDER by 2 DESC;
SELECT 'x'
FROM igs_en_intm_types
WHERE intermission_type = l_intm_type
AND study_antr_inst_ind = 'Y'
AND APPR_REQD_IND = 'Y';
SELECT spaa.conferral_date
FROM igs_gr_graduand_all gr,igs_en_spa_awd_aim spaa
WHERE gr.person_id = p_student_id
AND gr.course_cd = p_course_cd
AND gr. graduand_status IN
( SELECT graduand_status
FROM igs_gr_stat
WHERE s_graduand_status = 'GRADUATED'
)
AND gr.person_id = spaa.person_id AND gr.course_cd = spaa.course_cd AND gr.award_cd = spaa.award_cd
AND spaa.conferral_date BETWEEN p_s_date AND p_e_date;
SELECT 'x'
FROM igs_en_nsc_options
WHERE opt_type='GR_LVL_IND'
AND opt_val IN (SELECT course_type_group_cd FROM igs_ps_type
WHERE course_type = ( SELECT course_type
FROM igs_ps_ver_all
WHERE course_cd = p_course_cd
AND version_number = p_version_number
)
);
SELECT 'x'
FROM igs_en_nsc_options
WHERE opt_type='CO_PRG_TG'
AND opt_val IN (SELECT course_type_group_cd FROM igs_ps_type
WHERE course_type = (SELECT course_type
FROM igs_ps_ver_all
WHERE course_cd = p_course_cd
AND version_number=p_version_number
)
);
SELECT DISTINCT asv.outcome_dt
FROM igs_as_su_atmpt_itm asv, igs_en_su_attempt asav,
igs_en_stdnt_ps_att pr,igs_ps_ver vers,igs_ca_load_to_teach_v lt
WHERE asv.person_id = asav.person_id
AND asv.course_cd = asav.course_cd
AND asv.uoo_id = asav.uoo_id
AND asav.person_id = pr.person_id
AND asav.course_cd = pr.course_cd
AND pr.course_cd = vers.course_cd
AND pr.version_number = vers.version_number
AND asv.person_id = p_student_id
AND asav.course_cd = p_corresp_prg
AND asv.logical_delete_dt IS NULL
AND lt.teach_cal_type = asav.cal_type
AND lt.teach_ci_sequence_number = asav.ci_sequence_number
AND lt.load_cal_type = p_load_cal_type
AND lt.load_ci_sequence_number = p_load_cal_seq
AND pr.cal_type = p_acad_cal_type
AND pr.course_attempt_status IN ('COMPLETED', 'ENROLLED', 'INACTIVE', 'INTERMIT','DISCONTIN','LAPSED')
AND asav.unit_attempt_status IN ('DISCONTIN','DROPPED')
AND (vers.generic_course_ind = g_include_gen OR g_include_gen = 'Y')
ORDER by 1 DESC;
SELECT pr.course_cd,
pr.discontinued_dt disc_dt,
pr.course_rqrmnts_complete_dt comp_dt,
pr.version_number vers,
pr.course_attempt_status status,
pr.lapsed_dt lapsed_dt
FROM igs_en_stdnt_ps_att pr,
igs_ps_ver vers
WHERE pr.person_id = p_student_id
AND pr.cal_type = p_acad_cal_type
AND
(
(cp_nslc_condition = '3' AND discontinued_dt BETWEEN p_s_date AND p_e_date)
OR
(cp_nslc_condition IN ('1','3') AND cp_grad_type = 'CRS_RQMNTS_COMPL_DATE'
AND course_rqrmnts_complete_dt BETWEEN p_s_date AND p_e_date)
OR
(cp_nslc_condition = '3' AND lapsed_dt BETWEEN p_s_date AND p_e_date)
OR
(cp_nslc_condition = '3' AND course_attempt_status = 'INTERMIT'
And (EXISTS ( SELECT spi.person_id,spi.course_cd
FROM igs_en_stdnt_ps_intm spi
WHERE spi.person_id = pr.person_id
AND spi.course_cd = pr.course_cd
AND spi.logical_delete_date = TO_DATE('31-12-4712','DD-MM-YYYY')
AND(
( spi.start_dt <= p_e_date AND spi.end_dt >=p_e_date)
OR
( spi.start_dt <= p_e_date AND spi.end_dt >= SYSDATE AND spi.end_dt >= p_s_date)
)
)
)
)
OR
(cp_nslc_condition in( '2','3') AND
( cp_grad_type = 'CONFERRAL_DATE' AND
(EXISTS (select gr.person_id
FROM igs_gr_graduand_all gr,igs_en_spa_awd_aim spaa, igs_gr_stat grs
WHERE gr.person_id = pr.person_id and
gr.graduand_status =grs. graduand_status
AND grs. s_graduand_status = 'GRADUATED'
AND gr.person_id = spaa.person_id AND gr.course_cd = spaa.course_cd
AND pr.person_id = spaa.person_id
AND pr.course_cd = spaa.course_cd
AND gr.award_cd = spaa.award_cd
AND spaa.conferral_date BETWEEN p_s_date AND p_e_date)
)
)
)
OR
(cp_nslc_condition = '3'
AND course_attempt_status IN('ENROLLED','DISCONTIN', 'LAPSED','COMPLETED','INACTIVE')
AND (EXISTS (SELECT 'X' FROM igs_en_su_attempt b, igs_ca_load_to_teach_v lod
WHERE b.person_id = pr.person_id
AND b.course_cd = pr.course_cd
AND b.cal_type = lod.teach_cal_type
AND b.ci_sequence_number = lod.teach_ci_sequence_number
AND lod.load_cal_type = p_load_cal_type
AND lod.load_ci_sequence_number = p_load_cal_seq
and b.unit_attempt_status <> 'UNCONFIRM'
)
)
)
) AND pr.future_dated_trans_flag NOT IN('Y', 'C')
AND pr.course_cd = vers.course_cd
AND vers.version_number = pr.version_number
AND (vers.generic_course_ind = g_include_gen OR g_include_gen = 'Y')
AND (
(cp_career_profile = 'Y' AND igs_en_spa_terms_api.get_spat_primary_prg(p_student_id, pr.course_cd,p_load_cal_type,p_load_cal_seq) = 'PRIMARY')
OR
(cp_career_profile = 'N')
);
SELECT sci.start_dt , sci.end_dt
FROM igs_en_stdnt_ps_intm sci,
IGS_EN_INTM_TYPES eit
WHERE sci.person_id = p_student_id
AND sci.course_cd = cp_course_cd
AND sci.end_dt <= cp_wthdrn_dt
AND sci.logical_delete_date = TO_DATE('31-12-4712','DD-MM-YYYY')
AND sci.approved = eit.appr_reqd_ind
AND eit.intermission_type = sci.intermission_type
ORDER BY end_dt DESC;
SELECT uoo_id, cal_type , ci_sequence_number, discontinued_dt ,administrative_unit_status,
unit_attempt_status, no_assessment_ind
FROM igs_En_Su_Attempt
WHERE person_id = p_student_id
AND course_cd = cp_course_cd
AND unit_attempt_status = 'DISCONTIN'
AND discontinued_dt BETWEEN cp_intrm_end_dt AND cp_wthdrn_dt;
SELECT to_char(start_dt,'mm-dd-yyyy')
FROM igs_ca_inst
WHERE cal_type = cp_cal_type
AND sequence_number = cp_seq_number;
SELECT ci.cal_type, ci.SEQUENCE_NUMBER
FROM igs_ca_type ct,
igs_ca_inst ci,
igs_ca_stat cs
WHERE ci.cal_type = ct.cal_type
AND ct.s_cal_cat = 'ACADEMIC'
AND ct.closed_ind = 'N'
AND ci.cal_status = cs.cal_status
AND cs.s_cal_status = cst_active
AND (
(substr(to_char(start_dt, 'mm-dd-yyyy') ,7) = cp_start_year)
OR(substr(to_char(end_dt, 'mm-dd-yyyy') ,7) = cp_start_year)
);
SELECT ci.cal_type,
ci.sequence_number
FROM igs_ca_type ct,
igs_ca_inst ci,
igs_ca_stat cs,
igs_ca_inst_rel cir
WHERE ct.closed_ind = 'N' AND
cs.s_cal_status = cst_active AND
ci.cal_status = cs.cal_status AND
ct.s_cal_cat = cst_load AND
ci.cal_type = ct.cal_type AND
ci.cal_type = cir.sub_cal_type AND
ci.sequence_number = cir.sub_ci_sequence_number AND
cir.sup_cal_type = cp_cal_type AND
cir.sup_ci_sequence_number = cp_sequence_number;
SELECT DISTINCT spa.course_cd, spa.version_number
FROM igs_en_su_Attempt sua,igs_en_stdnt_ps_Att spa ,
igs_ca_load_to_teach_v lod
WHERE spa.person_id = p_student_id
AND spa.cal_type = cp_acad_cal_type
AND spa.person_id = sua.person_id
AND spa.course_cd = sua.course_cd
AND sua.cal_type = lod.teach_cal_type
AND sua.ci_sequence_number = lod.teach_ci_sequence_number
AND lod.load_cal_type = cp_load_cal_type
AND lod.load_ci_sequence_number = cp_load_cal_seq;
SELECT sua.discontinued_dt
FROM igs_en_su_attempt sua
WHERE sua.person_id = p_student_id
AND sua.course_cd = cp_course_cd
AND sua.unit_attempt_status NOT IN ('DROPPED','DISCONTIN')
AND (sua.cal_type,sua.ci_sequence_number) IN
( SELECT teach_cal_type,teach_ci_sequence_number FROM igs_ca_load_to_teach_v
WHERE load_cal_type = p_load_cal_type AND load_ci_sequence_number = p_load_cal_seq
);
SELECT sua.discontinued_dt
FROM igs_En_Su_Attempt sua
WHERE person_id = p_student_id
AND course_cd = cp_course_cd
AND unit_attempt_status IN ( 'DROPPED','DISCONTIN')
AND sua.no_assessment_ind = 'N'
AND (sua.cal_type,sua.ci_sequence_number) IN
( SELECT teach_cal_type,teach_ci_sequence_number FROM igs_ca_load_to_teach_v
WHERE load_cal_type = p_load_cal_type AND load_ci_sequence_number = p_load_cal_seq )
ORDER BY sua.discontinued_dt DESC ;
SELECT lower_enr_load_range
FROM igs_en_atd_type_load
WHERE
cal_type = p_load_cal_type
AND attendance_type in (select opt_val from
igs_En_nsc_options where opt_type = 'ENR_STAT_'||p_old_status)
order by lower_enr_load_range;
SELECT DISTINCT
ut.course_cd ,
pr.version_number cr_ver_number ,
ut.unit_cd ,
ut.version_number unit_ver_number,
ut.cal_type teach_cal_type,
ut.ci_sequence_number teach_seq_number ,
ut.uoo_id uoo_id,
ut.override_enrolled_cp override_enrolled_cp,
ut.override_eftsu override_eftsu
FROM igs_en_su_attempt ut,
igs_en_stdnt_ps_att pr,
igs_ps_ver vers
WHERE (ut.cal_type,ut.ci_sequence_number) IN
(SELECT teach_cal_type,
teach_ci_sequence_number
FROM igs_ca_load_to_teach_v
WHERE load_cal_type = p_load_cal_type
AND load_ci_sequence_number = p_load_cal_seq
)
AND ut.person_id = p_student_id
AND ut.course_cd = pr.course_cd
AND ut.person_id = pr.person_id
AND pr.cal_type = p_acad_cal_type
AND pr.course_attempt_status IN ('COMPLETED', 'ENROLLED', 'INACTIVE', 'INTERMIT','DISCONTIN')
AND (vers.generic_course_ind = g_include_gen OR g_include_gen = 'Y')
AND pr.course_cd = vers.course_cd
AND vers.version_number = pr.version_number ;
SELECT teach_cal_type,
teach_ci_sequence_number
FROM igs_ca_load_to_teach_v
WHERE load_cal_type = p_load_cal_type
AND load_ci_sequence_number = p_load_cal_seq;
SELECT ut.course_cd ,
pr.version_number cr_ver_number ,
ut.unit_cd ,
ut.version_number unit_ver_number,
ut.cal_type teach_cal_type,
ut.ci_sequence_number teach_seq_number ,
ut.override_enrolled_cp ,
ut.override_eftsu ,
ut.hist_end_dt,
ut.no_assessment_ind,
ut.uoo_id,
ut.administrative_unit_status,
ut.unit_attempt_status,
ut.discontinued_dt
FROM igs_en_su_attempt_h ut,
igs_en_stdnt_ps_att pr,
igs_ps_ver vers
WHERE ut.cal_type = p_teach_cal_type
AND ut.ci_sequence_number = p_teach_ci_sequence_number
AND ut.person_id = p_student_id
AND ut.course_cd = pr.course_cd
AND ut.person_id = pr.person_id
AND pr.cal_type = p_acad_cal_type
AND pr.course_attempt_status IN ('COMPLETED', 'ENROLLED', 'INACTIVE', 'INTERMIT','DISCONTIN')
AND (vers.generic_course_ind = g_include_gen OR g_include_gen = 'Y')
AND pr.course_cd = vers.course_cd
AND vers.version_number = pr.version_number
ORDER BY hist_end_dt DESC;
SELECT ut.override_eftsu,
ut.override_enrolled_cp ,
ut.cal_type,
ut.ci_sequence_number ,
ut.discontinued_dt,
ut.administrative_unit_status,
ut.unit_attempt_status,
ut.no_assessment_ind,
ut.hist_end_dt
FROM igs_en_su_attempt_h ut
WHERE ut.person_id = p_student_id
AND ut.course_cd = p_c_course_cd
AND ut.uoo_id = p_c_uoo_id
AND ut.hist_end_dt > p_c_hist_end_dt
ORDER BY ut.hist_end_dt;
SELECT ut.no_assessment_ind
FROM igs_en_su_attempt_h ut
WHERE ut.person_id = p_student_id
AND ut.course_cd = p_c_course_cd
AND ut.uoo_id = p_c_uoo_id
AND ut.hist_end_dt > p_c_hist_end_dt
AND ut.no_assessment_ind IS NOT NULL
ORDER BY ut.hist_end_dt;
SELECT ut.override_eftsu ,
ut.override_enrolled_cp ,
ut.cal_type,
ut.ci_sequence_number ,
ut.discontinued_dt,
ut.administrative_unit_status,
ut.unit_attempt_status,
ut.no_assessment_ind
FROM igs_en_su_attempt ut
WHERE ut.person_id = p_student_id
AND ut.course_cd = p_c_course_cd
AND ut.uoo_id = p_c_uoo_id;
SELECT NVL(cps.enrolled_credit_points,uv.enrolled_credit_points)
FROM igs_ps_unit_ver uv,
igs_ps_usec_cps cps,
igs_ps_unit_ofr_opt uoo
WHERE uoo.uoo_id = cps.uoo_id(+) AND
uoo.unit_cd = uv.unit_cd AND
uoo.version_number = uv.version_number AND
uoo.uoo_id = cp_uoo_id;
SELECT uoo.version_number
FROM igs_ps_unit_ofr_opt uoo
WHERE uoo.uoo_id = cp_uoo_id;
SELECT org_structure_id
FROM igs_or_org_alt_ids alt, igs_or_org_alt_idtyp types
WHERE alt.org_structure_id=p_org_id
AND NVL(alt.end_date, SYSDATE) >= SYSDATE
AND NVL(alt.start_date,SYSDATE) <= SYSDATE
AND alt.org_alternate_id_type = types.org_alternate_id_type
AND types.system_id_type ='NSC_BRANCH';