[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT grading_schema_code , grd_schm_version_number
FROM igs_ps_usec_grd_schm
WHERE uoo_id = p_uoo_id AND
default_flag = 'Y' ;
SELECT grading_schema_code , grd_schm_version_number
FROM igs_ps_unit_grd_schm
WHERE unit_code = p_unit_cd AND
unit_version_number = p_version_number AND
default_flag = 'Y' ;
SELECT 'x'
FROM igs_as_grd_sch_grade
WHERE grading_schema_cd = p_outcome_grading_schema_code AND
version_number = p_outcome_gs_version_number AND
grade = p_grade;
SELECT dcnt_unit_ind
FROM igs_en_dcnt_reasoncd
WHERE discontinuation_reason_cd = p_discontinuation_reason_cd;
SELECT COUNT(*)
FROM igs_en_su_attempt sua
WHERE sua.person_id = p_person_id AND
sua.course_cd = p_program_cd AND
sua.unit_cd = p_unit_cd AND
sua.cal_type = p_cal_type AND
sua.ci_sequence_number = p_ci_sequence_number AND
sua.location_cd = p_location_cd AND
sua.unit_class = p_unit_class;
SELECT sua.unit_attempt_status
FROM igs_en_su_attempt sua
WHERE sua.person_id = p_person_id AND
sua.course_cd= p_program_cd AND
sua.unit_cd = p_unit_cd AND
sua.cal_type = p_cal_type AND
sua.ci_sequence_number = p_ci_sequence_number AND
sua.location_cd = p_location_cd AND
sua.unit_class = p_unit_class ;
SELECT 'x'
FROM igs_en_stdnt_ps_att spa , igs_en_dcnt_reasoncd disc
WHERE spa.person_id = p_person_id AND
spa.course_cd = p_transfer_program_cd AND
disc.discontinuation_reason_cd = spa.discontinuation_reason_cd AND
disc.s_discontinuation_reason_type = 'TRANSFER';
SELECT sca.course_attempt_status
FROM igs_en_stdnt_ps_att sca
WHERE sca.course_cd = p_transfer_program_cd
AND sca.person_id = p_person_id;
SELECT uoo_id , owner_org_unit_cd
FROM IGS_PS_UNIT_OFR_OPT
WHERE unit_cd = p_unit_cd AND
version_number = p_version_number AND
cal_type = p_cal_type AND
ci_sequence_number = p_ci_sequence_number AND
location_cd = p_location_cd AND
unit_class = p_unit_class;
SELECT version_number
FROM igs_ps_unit_ofr_opt
WHERE cal_type = p_cal_type AND
unit_cd = p_unit_cd AND
ci_sequence_number = p_ci_sequence_number AND
location_cd = p_location_cd AND
unit_class = p_unit_class;
SELECT COUNT(*)
FROM igs_ps_unit_ofr_opt
WHERE cal_type = p_cal_type AND
unit_cd = p_unit_cd AND
ci_sequence_number = p_ci_sequence_number AND
location_cd = p_location_cd AND
unit_class = p_unit_class;
SELECT 'x'
FROM IGS_PS_USEC_GRD_SCHM
WHERE uoo_id = p_uoo_id AND
grading_schema_code = p_grading_schema_code AND
grd_schm_version_number = p_gs_version_number;
SELECT 'x'
FROM IGS_PS_UNIT_GRD_SCHM
WHERE unit_code = p_unit_cd AND
unit_version_number = p_version_number AND
grading_schema_code = p_grading_schema_code AND
grd_schm_version_number = p_gs_version_number;
SELECT student_confirmed_ind , course_attempt_status , discontinued_dt , primary_program_type , commencement_dt , version_number
FROM igs_en_stdnt_ps_att
WHERE person_id = p_person_id AND
course_cd = p_course_cd ;
SELECT cal_type,ci_sequence_number,discontinued_dt
FROM IGS_EN_SU_ATTEMPT
WHERE person_id = x_person_id
AND course_cd = x_course_cd
AND discontinued_dt IS NOT NULL
ORDER BY discontinued_dt DESC;
SELECT *
FROM IGS_CA_TEACH_TO_LOAD_V
WHERE teach_cal_type = p_cal_type
AND teach_ci_sequence_number = p_ci_sequence_number
AND load_start_dt <= TRUNC(p_discontinued_dt)
ORDER BY load_start_dt DESC;
SELECT cal_type,ci_sequence_number
FROM IGS_EN_SU_ATTEMPT
WHERE person_id = x_person_id
AND course_cd = x_course_cd
AND unit_attempt_status='COMPLETED';
SELECT *
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;
CURSOR cur_coo_id IS SELECT coo_id FROM igs_ps_ofr_opt
WHERE course_cd = p_course_cd
AND version_number = p_version_number
AND cal_type = p_cal_type
AND location_cd = p_location_cd
AND attendance_mode = p_attendance_mode
AND attendance_type = p_attendance_type;
CURSOR cur_class_std IS SELECT igs_pr_class_std_id FROM IGS_PR_CLASS_STD
WHERE class_standing = p_class_standing;
CURSOR cur_per_type IS SELECT person_type_code FROM igs_pe_person_types_v
WHERE system_type = 'STAFF'
AND closed_ind ='N';
CURSOR cur_cour_typ IS SELECT course_type FROM IGS_PS_VER
WHERE course_cd = p_course_cd
AND version_number = p_version_number;
CURSOR cur_count IS SELECT count(*) FROM igs_en_stdnt_ps_att
WHERE key_program = 'Y'
AND person_id = p_person_id;
CURSOR cur_count IS SELECT count(primary_program_type) FROM igs_en_stdnt_ps_att sca,
igs_ps_ver crv
WHERE crv.course_type = p_course_type
AND sca.course_cd = crv.course_cd
AND sca.version_number = crv.version_number
AND sca.person_id = p_person_id
AND sca.primary_program_type = 'PRIMARY';
SELECT MAX(NVL(end_date,TO_DATE('4712/12/31','YYYY/MM/DD'))) FROM igs_pe_typ_instances_all pti
WHERE pti.person_id = cp_person_id
AND pti.type_instance_id <> cp_type_instance_id
AND SYSDATE BETWEEN pti.start_date and NVL(pti.end_date, SYSDATE)
AND pti.person_type_code IN
(select person_type_code from igs_pe_person_types pt where system_type =cp_system_type) ;
IF p_action = 'INSERT' THEN
-- End date is always passed as NULL, hence raise the Business event without any check
igs_pe_gen_003.RAISE_PERSON_TYPE_EVENT(
p_person_id,
p_person_type_code,
p_action,
p_person_type_end_date
);
ELSIF p_action = 'UPDATE' THEN
-- End date is always passed as TRUNC(SYSDATE). So if there is any other active record for the same person id type then no need to
-- raise the business event.
OPEN get_active_inst_cur(p_person_id, p_system_person_type, p_type_instance_id);
|| pkpatel 30-Sep-2005 Bug 4627888 (Raised the Business event after the Insert/update of the person type)
------------------------------------------------------------------------------*/
--Cursor get the person type instance for a person for a given person type.
CURSOR cur_per_inst(p_person_type igs_pe_person_types.system_type%TYPE)
IS SELECT pti.*
FROM igs_pe_typ_instances_all pti,
igs_pe_person_types pty
WHERE pti.person_id = p_person_id
AND pti.course_cd = p_course_cd
AND pti.end_date IS NULL
AND pty.person_type_code = pti.person_type_code
AND pty.system_type = p_person_type;
IS SELECT person_type_code FROM igs_pe_person_types
WHERE SYSTEM_TYPE = p_system_type
AND CLOSED_IND = 'N';
SELECT user_id
FROM fnd_user
WHERE person_party_id = cp_person_id;
CURSOR cur_pe_seq IS SELECT IGS_PE_TYPE_INSTANCES_S.NEXTVAL FROM DUAL;
UPDATE igs_pe_typ_instances_all
SET end_date = l_sysdate ,
end_method = l_method ,
last_update_date = SYSDATE,
last_updated_by = NVL(fnd_global.user_id,-1) ,
last_update_login = NVL(fnd_global.login_id,-1)
WHERE type_instance_id = rec_per_inst.type_instance_id;
p_action => 'UPDATE'
);
INSERT INTO igs_pe_typ_instances_all(type_instance_id,
person_type_code,
person_id,
course_cd,
cc_version_number,
funnel_status,
admission_appl_number,
nominated_course_cd,
ncc_version_number,
sequence_number,
start_date,
end_date,
create_method,
ended_by,
end_method,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
org_id)VALUES(
l_type_instance_id,
l_person_type_code,
p_person_id,
p_course_cd,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
l_sysdate,
NULL,
l_method,
NULL,
NULL,
NVL(fnd_global.user_id,-1),
SYSDATE,
NVL(fnd_global.user_id,-1),
SYSDATE,
NVL(fnd_global.login_id,-1),
igs_ge_gen_003.get_org_id);
p_action => 'INSERT'
);
UPDATE igs_pe_typ_instances_all
SET end_date = l_sysdate,
end_method = l_method,
last_update_date = SYSDATE ,
last_updated_by = NVL(fnd_global.user_id,-1) ,
last_update_login = NVL(fnd_global.login_id,-1)
WHERE type_instance_id = rec_per_inst.type_instance_id;
p_action => 'UPDATE'
);
UPDATE igs_pe_typ_instances_all
SET end_date = l_sysdate,
end_method = l_method ,
last_update_date = SYSDATE ,
last_updated_by = NVL(fnd_global.user_id,-1) ,
last_update_login = NVL(fnd_global.login_id,-1)
WHERE type_instance_id = rec_per_inst.type_instance_id;
p_action => 'UPDATE'
);
INSERT INTO igs_pe_typ_instances_all(type_instance_id,
person_type_code,
person_id,
course_cd,
cc_version_number,
funnel_status,
admission_appl_number,
nominated_course_cd,
ncc_version_number,
sequence_number,
start_date,
end_date,
create_method,
ended_by,
end_method,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
org_id)VALUES(
l_type_instance_id,
l_person_type_code,
p_person_id,
p_course_cd,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
l_sysdate,
NULL,
l_method,
NULL,
NULL,
NVL(fnd_global.user_id,-1),
SYSDATE,
NVL(fnd_global.user_id,-1),
SYSDATE,
NVL(fnd_global.login_id,-1),
igs_ge_gen_003.get_org_id);
p_action => 'INSERT'
);
SELECT
'x'
FROM
igs_en_unit_set us,
igs_en_unit_set_cat usc
WHERE
us.unit_set_cd = p_unit_set_cd
AND us.version_number = p_us_version_number
AND us.unit_set_cat = usc.unit_set_cat
AND usc.s_unit_set_cat = 'PRENRL_YR';
p_selection_dt IN igs_as_su_setatmpt.selection_dt%TYPE,
p_rqrmnts_complete_dt IN igs_as_su_setatmpt.rqrmnts_complete_dt%TYPE,
p_end_dt IN igs_as_su_setatmpt.end_dt%TYPE,
p_sequence_number IN igs_as_su_setatmpt.sequence_number%TYPE,
p_unit_set_cd IN igs_as_su_setatmpt.unit_set_cd%TYPE,
p_us_version_number IN igs_as_su_setatmpt.us_version_number%TYPE,
p_message_name OUT NOCOPY VARCHAR2)
RETURN BOOLEAN AS
/*----------------------------------------------------------------------------
|| Created By : prraj
|| Created On : 21-Nov-2002
|| Purpose : Check the condition that unit sets with category of 'pre-enrollment year'
|| cannot overlap selection/completion dates
|| Known limitations, enhancements or remarks :
|| Change History :
|| Who When What
|| bdeviset 29-JUL-2004 Added extra parameters p_end_dt,p_sequence_number to
|| function check_usa_overlap for Bug 3149133.
|| Modified cursor c_usa_ovrlp as unit sets with category of
|| 'pre-enrollment year' cannot overlap selection,completion
|| and end dates for 3149133
|| ckasu 28-OCT-2005 Added code to check whether passed unit_set_cd is PRENRL_YR
|| type or not.if so return false else continue
|| stutta 26-APR-2005 Modified c_sua_ovrlp to correct a join conditions bug5070647
------------------------------------------------------------------------------*/
CURSOR c_us_cat (cp_unit_set_cd igs_as_su_setatmpt.unit_set_cd%TYPE,
cp_us_version_number igs_as_su_setatmpt.us_version_number%TYPE) IS
SELECT usc.s_unit_set_cat
FROM igs_en_unit_set us,
igs_en_unit_set_cat usc
WHERE us.unit_set_cd = cp_unit_set_cd
AND us.version_number = cp_us_version_number
AND us.unit_set_cat = usc.unit_set_cat;
SELECT
'x'
FROM
igs_as_su_setatmpt asu,
igs_en_unit_set us,
igs_en_unit_set_cat usc
WHERE
asu.person_id = p_person_id
AND asu.course_cd = p_program_cd
AND asu.unit_set_cd = us.unit_set_cd
AND asu.us_version_number = us.version_number
AND us.unit_set_cat = usc.unit_set_cat
AND usc.s_unit_set_cat = 'PRENRL_YR'
AND ((asu.selection_dt BETWEEN p_selection_dt
AND NVL (p_rqrmnts_complete_dt,NVL(p_end_dt,(TO_DATE('9999/12/31','YYYY/MM/DD')))))
OR (p_selection_dt BETWEEN asu.selection_dt
AND NVL (asu.rqrmnts_complete_dt,NVL(asu.end_dt,(TO_DATE('9999/12/31','YYYY/MM/DD'))))))
AND ((p_sequence_number IS NULL) OR (asu.sequence_number <> p_sequence_number));
p_selection_dt IN igs_as_su_setatmpt.selection_dt%TYPE)
RETURN BOOLEAN AS
/*----------------------------------------------------------------------------
|| Created By : prraj
|| Created On : 21-Nov-2002
|| Purpose : Check for duplicate student unit set attempt
|| Known limitations, enhancements or remarks :
|| Change History :
|| Who When What
------------------------------------------------------------------------------*/
CURSOR c_dup_susa IS
SELECT
'x'
FROM
igs_as_su_setatmpt
WHERE
person_id = p_person_id
AND course_cd = p_program_cd
AND unit_set_cd = p_unit_set_cd
AND us_version_number = p_us_version_number
AND ((selection_dt IS NULL AND p_selection_dt IS NULL)
OR selection_dt = p_selection_dt );
SELECT census_dt_alias
FROM igs_ge_s_gen_cal_con
WHERE s_control_num = 1;
SELECT 'x'
FROM igs_en_su_attempt sua,
igs_ca_da_inst_v da
WHERE sua.person_id = p_person_id
AND sua.course_cd = p_program_cd
AND sua.unit_attempt_status IN ('ENROLLED','COMPLETED')
AND sua.cal_type = da.cal_type
AND sua.ci_sequence_number = da.ci_sequence_number
AND da.dt_alias = l_census_dt_alias
AND da.alias_val IS NOT NULL
AND da.alias_val BETWEEN p_start_dt AND p_end_dt ;
SELECT appr_reqd_ind
FROM igs_en_intm_types
WHERE intermission_type = p_intermission_type;
SELECT study_antr_inst_ind
FROM igs_en_intm_types
WHERE intermission_type = p_intermission_type;
SELECT 'x'
FROM hz_parties hp, igs_pe_hz_parties ihp
WHERE hp.party_id = ihp.party_id AND
ihp.inst_org_ind = 'I' AND
ihp.oi_govt_institution_cd is not null AND
ihp.oss_org_unit_cd = p_institution_name ;
SELECT 'X'
FROM igs_lookup_values lk
WHERE lk.lookup_type = 'OR_INST_EXEMPTIONS' AND
lk.enabled_flag = 'Y' AND
lk.lookup_code = p_institution_name ;
updated or not.
Known limitations,enhancements,remarks:
Change History
Who When What
***********************************************************************************************/
CURSOR c_sca IS
SELECT sca.course_attempt_status,
sca.student_confirmed_ind,
sca.discontinued_dt,
sca.lapsed_dt,
sca.course_rqrmnt_complete_ind,
sca.logical_delete_dt
FROM igs_en_stdnt_ps_att sca
WHERE sca.person_id = p_person_id
AND sca.course_cd = p_program_cd;
l_sca_row.logical_delete_dt
);
SELECT 'X'
FROM igs_en_stdnt_ps_att spa,
igs_ps_award psa
WHERE spa.person_id = p_person_id
AND spa.course_cd = p_program_cd
AND psa.award_cd = p_award_cd
AND spa.course_cd = psa.course_cd
AND spa.version_number = psa.version_number
AND psa.closed_ind = 'N';