[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
|| c_uoo_ug to select active (not closed) unit classes.
*/
FUNCTION chk_anon_graded (
p_uoo_id IN igs_ps_unit_ofr_opt_all.uoo_id%TYPE,
p_ass_id IN igs_as_assessmnt_itm_all.ass_id%TYPE
) RETURN VARCHAR2 IS
/*
|| Created By : pkpatel
|| Created On : 28-JAN-2002
|| Purpose : This function checks if Anonymously Graded. It returns 'Y'/'N'. If P_UOO_ID
|| is passed as NULL, it returns NULL.
|| Known limitations, enhancements or remarks :
|| Change History :
|| Who When What
|| (reverse chronological order - newest change first)
*/
CURSOR c_uoo_ug
IS
SELECT 'X'
FROM igs_ps_unit_ofr_opt uoo,
igs_as_anon_method anm,
igs_ca_teach_to_load_v ttl
WHERE uoo.uoo_id = p_uoo_id
AND uoo.anon_unit_grading_ind = 'Y'
AND uoo.cal_type = ttl.teach_cal_type
AND uoo.ci_sequence_number = ttl.teach_ci_sequence_number
AND ttl.load_start_dt = (SELECT MIN(ttl2.load_start_dt)
FROM igs_ca_teach_to_load_v ttl2
WHERE uoo.cal_type = ttl2.teach_cal_type
AND uoo.ci_sequence_number = ttl2.teach_ci_sequence_number)
AND anm.load_cal_type = ttl.load_cal_type
AND (anm.method <> 'ASSESSMENT'
OR (anm.method = 'ASSESSMENT'
AND (EXISTS ( SELECT 'X'
FROM igs_ps_unitass_item uooai,
igs_as_assessmnt_itm ai,
igs_as_assessmnt_typ ast
WHERE uooai.uoo_id = uoo.uoo_id
AND uooai.logical_delete_dt IS NULL
AND uooai.ass_id = ai.ass_id
AND ai.assessment_type = anm.assessment_type
AND ai.assessment_type = ast.assessment_type
AND ast.anon_grading_ind = 'Y')
OR EXISTS ( SELECT 'X'
FROM igs_as_unitass_item uoai,
igs_as_assessmnt_itm ai,
igs_as_assessmnt_typ ast,
igs_as_unit_class uc
WHERE uoai.unit_cd = uoo.unit_cd
AND uoai.version_number = uoo.version_number
AND uoai.cal_type = uoo.cal_type
AND uoai.ci_sequence_number = uoo.ci_sequence_number
AND uc.closed_ind = 'N'
--ijeddy, Bug 3201661, Grade Book.
AND uoai.logical_delete_dt IS NULL
AND uoai.ass_id = ai.ass_id
AND ai.assessment_type = anm.assessment_type
AND ai.assessment_type = ast.assessment_type
AND ast.anon_grading_ind = 'Y'
AND NOT EXISTS (SELECT 'X'
FROM igs_ps_unitass_item uooai
WHERE uooai.uoo_id = uoo.uoo_id
AND uooai.logical_delete_dt IS NOT NULL)))));
SELECT 'X'
FROM igs_ps_unit_ofr_opt uoo,
igs_as_anon_method anm,
igs_ca_teach_to_load_v ttl
WHERE uoo.uoo_id = p_uoo_id
AND uoo.anon_assess_grading_ind = 'Y'
AND uoo.cal_type = ttl.teach_cal_type
AND uoo.ci_sequence_number = ttl.teach_ci_sequence_number
AND ttl.load_start_dt = (SELECT MIN(ttl2.load_start_dt)
FROM igs_ca_teach_to_load_v ttl2
WHERE uoo.cal_type = ttl2.teach_cal_type
AND uoo.ci_sequence_number = ttl2.teach_ci_sequence_number)
AND anm.load_cal_type = ttl.load_cal_type
AND (EXISTS ( SELECT 'X'
FROM igs_ps_unitass_item uooai,
igs_as_assessmnt_itm ai,
igs_as_assessmnt_typ ast
WHERE uooai.uoo_id = uoo.uoo_id
AND uooai.ass_id = p_ass_id
AND uooai.logical_delete_dt IS NULL
AND uooai.ass_id = ai.ass_id
AND ai.assessment_type = ast.assessment_type
AND ast.anon_grading_ind = 'Y')
OR EXISTS ( SELECT 'X'
FROM igs_as_unitass_item uoai,
igs_as_assessmnt_itm ai,
igs_as_assessmnt_typ ast,
igs_as_unit_class uc
WHERE uoai.unit_cd = uoo.unit_cd
AND uoai.version_number = uoo.version_number
AND uoai.cal_type = uoo.cal_type
AND uoai.ci_sequence_number = uoo.ci_sequence_number
AND uoai.ass_id = p_ass_id
AND uoo.location_cd = NVL(uoai.location_cd, uoo.location_cd)
AND uoo.unit_class = NVL(uoai.unit_class, uoo.unit_class)
AND uoo.unit_class = uc.unit_class
AND uc.closed_ind = 'N'
AND uc.unit_mode = NVL(uoai.unit_mode, uc.unit_mode)
AND uoai.logical_delete_dt IS NULL
AND uoai.ass_id = ai.ass_id
AND ai.assessment_type = ast.assessment_type
AND ast.anon_grading_ind = 'Y'
AND NOT EXISTS (SELECT 'X'
FROM igs_ps_unitass_item uooai
WHERE uooai.uoo_id = uoo.uoo_id
AND uooai.logical_delete_dt IS NOT NULL)));
PROCEDURE sub_get_insert(
p_method IN igs_as_anon_method.method%TYPE,
p_person_id IN hz_parties.party_id%TYPE,
p_course_cd IN igs_en_su_attempt_all.course_cd%TYPE,
p_unit_cd IN igs_en_su_attempt_all.unit_cd%TYPE,
p_teach_cal_type IN igs_ca_inst_all.cal_type%TYPE,
p_teach_ci_sequence_number IN igs_ca_inst_all.sequence_number%TYPE,
p_uoo_id IN igs_ps_unit_ofr_opt_all.uoo_id%TYPE,
p_assessment_type IN igs_as_assessmnt_typ.assessment_type%TYPE,
p_load_cal_type IN igs_ca_inst_all.cal_type%TYPE,
p_load_ci_sequence_number IN igs_ca_inst_all.sequence_number%TYPE )
IS
/*
|| Created By : [email protected]
|| Created On : 28-JAN-2002
|| Purpose : This is a private procedure to Insert records in the Anonymous ID
|| tables as per the Method
|| Known limitations, enhancements or remarks :
|| Change History :
|| Who When What
|| (reverse chronological order - newest change first)
*/
-- Select the first record from the unallocated anonymous number table IGS_AS_ANON_NUMBER
-- for the different methods. or PROGRAM method the cal_type and ci_sequence_number would be NULL.
CURSOR c_anon_number(cp_load_cal_type igs_as_anon_number.load_cal_type%TYPE,
cp_load_ci_sequence_number igs_as_anon_number.load_ci_sequence_number%TYPE)
IS
SELECT anonymous_number
FROM igs_as_anon_number
WHERE ((load_cal_type = cp_load_cal_type) OR
(load_cal_type IS NULL AND cp_load_cal_type IS NULL))
AND ((load_ci_sequence_number = cp_load_ci_sequence_number) OR
(load_ci_sequence_number IS NULL AND cp_load_ci_sequence_number IS NULL));
CURSOR c_delete_anon_number(cp_anonymous_number igs_as_anon_number.anonymous_number%TYPE,
cp_load_cal_type igs_as_anon_number.load_cal_type%TYPE,
cp_load_ci_sequence_number igs_as_anon_number.load_ci_sequence_number%TYPE)
IS
SELECT ROWID
FROM igs_as_anon_number
WHERE anonymous_number = cp_anonymous_number
AND ((load_cal_type = cp_load_cal_type) OR
(load_cal_type IS NULL AND cp_load_cal_type IS NULL))
AND ((load_ci_sequence_number = cp_load_ci_sequence_number) OR
(load_ci_sequence_number IS NULL AND cp_load_ci_sequence_number IS NULL));
l_rowid_delete ROWID;
OPEN c_delete_anon_number(l_anonymous_number,p_load_cal_type, p_load_ci_sequence_number);
FETCH c_delete_anon_number INTO l_rowid_delete;
CLOSE c_delete_anon_number;
igs_as_anon_number_pkg.delete_row(l_rowid_delete);
igs_as_anon_id_us_pkg.insert_row(
x_rowid => l_rowid,
x_person_id => p_person_id,
x_anonymous_id => l_anonymous_id,
x_system_generated_ind => l_system_generated_ind,
x_course_cd => p_course_cd,
x_unit_cd => p_unit_cd,
x_teach_cal_type => p_teach_cal_type,
x_teach_ci_sequence_number => p_teach_ci_sequence_number,
x_uoo_id => p_uoo_id,
x_load_cal_type => p_load_cal_type,
x_load_ci_sequence_number => p_load_ci_sequence_number,
x_mode => 'R');
igs_as_anon_id_ass_pkg.insert_row(
x_rowid => l_rowid,
x_person_id => p_person_id,
x_anonymous_id => l_anonymous_id,
x_system_generated_ind => l_system_generated_ind,
x_assessment_type => p_assessment_type,
x_load_cal_type => p_load_cal_type,
x_load_ci_sequence_number => p_load_ci_sequence_number,
x_mode =>'R');
igs_as_anon_id_ps_pkg.insert_row(
x_rowid => l_rowid,
x_person_id => p_person_id,
x_anonymous_id => l_anonymous_id,
x_system_generated_ind => l_system_generated_ind,
x_course_cd => p_course_cd,
x_mode => 'R' );
FND_MESSAGE.SET_TOKEN('NAME','IGS_AS_ANON_GRD_PKG.MNT_ANON_ID.SUB_GET_INSERT');
END sub_get_insert; -- END sub_get_insert
SELECT anm.method
FROM igs_as_anon_method anm
WHERE anm.load_cal_type = cp_load_cal_type;
SELECT COUNT(*)
FROM igs_as_anon_number ann
WHERE ann.load_cal_type = cp_load_cal_type
AND ann.load_ci_sequence_number = cp_load_ci_sequence_number;
SELECT COUNT(*)
FROM igs_as_anon_number ann
WHERE ann.load_cal_type IS NULL;
SELECT COUNT(*)
FROM igs_en_stdnt_ps_att spa
WHERE spa.course_attempt_status IN ('ENROLLED', 'UNCONFIRM', 'INACTIVE')
AND NOT EXISTS (SELECT 'X'
FROM igs_as_anon_id_ps anip
WHERE anip.person_id = spa.person_id
AND anip.course_cd = spa.course_cd);
SELECT COUNT(*)
FROM igs_en_su_attempt sua,
igs_ps_unit_ofr_opt uoo,
igs_ca_teach_to_load_v ttl
WHERE sua.unit_attempt_status = 'ENROLLED'
AND ttl.load_cal_type = cp_load_cal_type
AND ttl.load_ci_sequence_number = cp_load_ci_sequence_number
AND uoo.cal_type = ttl.teach_cal_type
AND uoo.ci_sequence_number = ttl.teach_ci_sequence_number
AND ttl.load_start_dt = (SELECT MIN(ttl2.load_start_dt)
FROM igs_ca_teach_to_load_v ttl2
WHERE uoo.cal_type = ttl2.teach_cal_type
AND uoo.ci_sequence_number = ttl2.teach_ci_sequence_number)
AND (uoo.anon_assess_grading_ind = 'Y'
OR uoo.anon_unit_grading_ind = 'Y')
AND uoo.uoo_id = sua.uoo_id
AND NOT EXISTS (SELECT 'X'
FROM igs_as_anon_id_us aniu
WHERE aniu.person_id = sua.person_id
AND aniu.course_cd = sua.course_cd
AND aniu.unit_cd = sua.unit_cd
AND aniu.teach_cal_type = sua.cal_type
AND aniu.teach_ci_sequence_number = sua.ci_sequence_number
AND aniu.uoo_id = sua.uoo_id);
SELECT COUNT(*)
FROM(
SELECT DISTINCT sua.person_id, ast.assessment_type
FROM igs_ca_teach_to_load_v ttl,
igs_en_su_attempt sua,
igs_ps_unit_ofr_opt uoo,
igs_as_assessmnt_typ ast
WHERE sua.unit_attempt_status = 'ENROLLED'
AND ttl.load_cal_type = cp_load_cal_type
AND ttl.load_ci_sequence_number = cp_load_ci_sequence_number
AND ttl.load_start_dt = (SELECT MIN(ttl2.load_start_dt)
FROM igs_ca_teach_to_load_v ttl2
WHERE uoo.cal_type = ttl2.teach_cal_type
AND uoo.ci_sequence_number = ttl2.teach_ci_sequence_number)
AND ttl.teach_cal_type = uoo.cal_type
AND ttl.teach_ci_sequence_number = uoo.ci_sequence_number
AND sua.uoo_id = uoo.uoo_id
AND ((uoo.anon_assess_grading_ind = 'Y'
AND ast.anon_grading_ind = 'Y'
AND EXISTS (SELECT 'X'
FROM igs_as_su_atmpt_itm suaai,
igs_as_assessmnt_itm ai
WHERE sua.person_id = suaai.person_id
AND sua.course_cd = suaai.course_cd
AND sua.unit_cd = suaai.unit_cd
AND sua.cal_type = suaai.cal_type
AND sua.ci_sequence_number = suaai.ci_sequence_number
AND suaai.ass_id = ai.ass_id
AND ai.assessment_type = ast.assessment_type))
OR (uoo.anon_unit_grading_ind = 'Y'
AND EXISTS (SELECT 'X'
FROM igs_as_anon_method anm
WHERE anm.load_cal_type = cp_load_cal_type
AND anm.assessment_type = ast.assessment_type)))
AND NOT EXISTS (SELECT 'X'
FROM igs_as_anon_id_ass ania
WHERE ania.person_id = sua.person_id
AND ania.assessment_type = ast.assessment_type
AND ania.load_cal_type = cp_load_cal_type
AND ania.load_ci_sequence_number = cp_load_ci_sequence_number));
SELECT spa.person_id,
spa.course_cd
FROM igs_en_stdnt_ps_att spa
WHERE spa.course_attempt_status IN ('ENROLLED', 'UNCONFIRM','INACTIVE')
AND NOT EXISTS (SELECT 'X'
FROM igs_as_anon_id_ps anip
WHERE anip.person_id = spa.person_id
AND anip.course_cd = spa.course_cd);
SELECT sua.person_id,
sua.course_cd,
sua.unit_cd,
sua.cal_type,
sua.ci_sequence_number,
sua.uoo_id
FROM igs_ca_teach_to_load_v ttl,
igs_en_su_attempt sua,
igs_ps_unit_ofr_opt uoo
WHERE sua.unit_attempt_status = 'ENROLLED'
AND ttl.load_cal_type = cp_load_cal_type
AND ttl.load_ci_sequence_number = cp_load_ci_sequence_number
AND ttl.load_start_dt = (SELECT MIN(ttl2.load_start_dt)
FROM igs_ca_teach_to_load_v ttl2
WHERE uoo.cal_type = ttl2.teach_cal_type
AND uoo.ci_sequence_number = ttl2.teach_ci_sequence_number)
AND ttl.teach_cal_type = uoo.cal_type
AND ttl.teach_ci_sequence_number = uoo.ci_sequence_number
AND (uoo.anon_unit_grading_ind = 'Y'
OR uoo.anon_assess_grading_ind = 'Y')
AND sua.uoo_id = uoo.uoo_id
AND NOT EXISTS (SELECT 'X'
FROM igs_as_anon_id_us aniu
WHERE aniu.person_id = sua.person_id
AND aniu.course_cd = sua.course_cd
AND aniu.unit_cd = sua.unit_cd
AND aniu.teach_cal_type = sua.cal_type
AND aniu.teach_ci_sequence_number = sua.ci_sequence_number
AND aniu.uoo_id = sua.uoo_id);
SELECT DISTINCT sua.person_id,
ast.assessment_type
FROM igs_ca_teach_to_load_v ttl,
igs_en_su_attempt sua,
igs_ps_unit_ofr_opt uoo,
igs_as_assessmnt_typ ast
WHERE sua.unit_attempt_status = 'ENROLLED'
AND ttl.load_cal_type = cp_load_cal_type
AND ttl.load_ci_sequence_number = cp_load_ci_sequence_number
AND ttl.load_start_dt = (SELECT MIN(ttl2.load_start_dt)
FROM igs_ca_teach_to_load_v ttl2
WHERE uoo.cal_type = ttl2.teach_cal_type
AND uoo.ci_sequence_number = ttl2.teach_ci_sequence_number)
AND ttl.teach_cal_type = uoo.cal_type
AND ttl.teach_ci_sequence_number = uoo.ci_sequence_number
AND sua.uoo_id = uoo.uoo_id
AND ((uoo.anon_assess_grading_ind = 'Y'
AND ast.anon_grading_ind = 'Y'
AND EXISTS (SELECT 'X'
FROM igs_as_su_atmpt_itm suaai,
igs_as_assessmnt_itm ai
WHERE sua.person_id = suaai.person_id
AND sua.course_cd = suaai.course_cd
AND sua.unit_cd = suaai.unit_cd
AND sua.cal_type = suaai.cal_type
AND sua.ci_sequence_number = suaai.ci_sequence_number
AND suaai.ass_id = ai.ass_id
AND ai.assessment_type = ast.assessment_type))
OR (uoo.anon_unit_grading_ind = 'Y'
AND EXISTS (SELECT 'X'
FROM igs_as_anon_method anm
WHERE anm.load_cal_type = cp_load_cal_type
AND anm.assessment_type = ast.assessment_type)))
AND NOT EXISTS (SELECT 'X'
FROM igs_as_anon_id_ass ania
WHERE ania.person_id = sua.person_id
AND ania.assessment_type = ast.assessment_type
AND ania.load_cal_type = cp_load_cal_type
AND ania.load_ci_sequence_number = cp_load_ci_sequence_number) ;
CURSOR c_ass_delete(cp_load_cal_type igs_as_anon_number.load_cal_type%TYPE,
cp_load_ci_sequence_number igs_as_anon_number.load_ci_sequence_number%TYPE)
IS
SELECT ROWID
FROM igs_as_anon_id_ass ania
WHERE ania.load_cal_type = cp_load_cal_type
AND ania.load_ci_sequence_number = cp_load_ci_sequence_number;
CURSOR c_section_delete(cp_load_cal_type igs_as_anon_number.load_cal_type%TYPE,
cp_load_ci_sequence_number igs_as_anon_number.load_ci_sequence_number%TYPE)
IS
SELECT ROWID
FROM igs_as_anon_id_us aniu
WHERE aniu.load_cal_type = cp_load_cal_type
AND aniu.load_ci_sequence_number = cp_load_ci_sequence_number;
CURSOR c_program_delete
IS
SELECT ROWID
FROM igs_as_anon_id_ps;
CURSOR c_anon_num_delete_prog
IS
SELECT ROWID
FROM igs_as_anon_number ann
WHERE ann.load_cal_type IS NULL;
CURSOR c_anon_num_delete(cp_load_cal_type igs_as_anon_number.load_cal_type%TYPE,
cp_load_ci_sequence_number igs_as_anon_number.load_ci_sequence_number%TYPE)
IS
SELECT ROWID
FROM igs_as_anon_number ann
WHERE ann.load_cal_type = cp_load_cal_type
AND ann.load_ci_sequence_number = cp_load_ci_sequence_number;
SELECT meaning
FROM FND_LOOKUP_VALUES
WHERE lookup_type = 'SYS_YES_NO'
AND language = userenv ('LANG')
AND VIEW_APPLICATION_ID = 8405 AND SECURITY_GROUP_ID = 0
AND lookup_code = p_reallocate_anon_id;
SELECT alternate_code,start_dt, end_dt
FROM igs_ca_inst
WHERE cal_type = cp_load_cal_type
AND sequence_number = cp_load_ci_sequence_number;
SELECT party_number
FROM hz_parties
WHERE party_id = cp_person_id;
-- Delete Allocated Anonymous IDs
FOR c_ass_delete_rec IN c_ass_delete(l_ld_cal_type,l_ld_sequence_number) LOOP
igs_as_anon_id_ass_pkg.delete_row(c_ass_delete_rec.ROWID);
-- Delete Allocated Anonymous IDs
FOR c_section_delete_rec IN c_section_delete(l_ld_cal_type,l_ld_sequence_number)
LOOP
igs_as_anon_id_us_pkg.delete_row(c_section_delete_rec.ROWID);
-- Delete Allocated Anonymous IDs
FOR c_program_delete_rec IN c_program_delete
LOOP
igs_as_anon_id_ps_pkg.delete_row(c_program_delete_rec.ROWID);
-- Delete Unallocated Anonymous IDs
FOR c_anon_num_delete_prog_rec IN c_anon_num_delete_prog
LOOP
igs_as_anon_number_pkg.delete_row(c_anon_num_delete_prog_rec.ROWID);
-- Delete Unallocated Anonymous IDs
FOR c_anon_num_delete_rec IN c_anon_num_delete(l_ld_cal_type,l_ld_sequence_number)
LOOP
igs_as_anon_number_pkg.delete_row(c_anon_num_delete_rec.ROWID);
igs_as_anon_number_pkg.insert_row(
x_rowid => l_rowid,
x_anum_id => l_anum_id,
x_anonymous_number => temp_table(l_index),
x_load_cal_type => NULL,
x_load_ci_sequence_number => NULL,
x_mode => 'R');
igs_as_anon_number_pkg.insert_row(
x_rowid => l_rowid,
x_anum_id => l_anum_id,
x_anonymous_number => temp_table(l_index),
x_load_cal_type => l_ld_cal_type,
x_load_ci_sequence_number => l_ld_sequence_number,
x_mode => 'R');
temp_table.DELETE;
sub_get_insert( p_method => l_method,
p_person_id => c_spa_rec.person_id,
p_course_cd => c_spa_rec.course_cd,
p_unit_cd => NULL,
p_teach_cal_type => NULL,
p_teach_ci_sequence_number => NULL,
p_uoo_id => NULL,
p_assessment_type => NULL,
p_load_cal_type => NULL,
p_load_ci_sequence_number => NULL);
sub_get_insert( p_method => l_method,
p_person_id => c_sua_rec.person_id,
p_course_cd => c_sua_rec.course_cd,
p_unit_cd => c_sua_rec.unit_cd,
p_teach_cal_type => c_sua_rec.cal_type,
p_teach_ci_sequence_number => c_sua_rec.ci_sequence_number,
p_uoo_id => c_sua_rec.uoo_id,
p_assessment_type => NULL,
p_load_cal_type => l_ld_cal_type,
p_load_ci_sequence_number => l_ld_sequence_number);
sub_get_insert( p_method => l_method,
p_person_id => c_suaai_rec.person_id,
p_course_cd => NULL,
p_unit_cd => NULL,
p_teach_cal_type => NULL,
p_teach_ci_sequence_number => NULL,
p_uoo_id => NULL,
p_assessment_type => c_suaai_rec.assessment_type,
p_load_cal_type => l_ld_cal_type,
p_load_ci_sequence_number => l_ld_sequence_number);
SELECT anonymous_id
FROM igs_as_anon_id_ps anip,
igs_en_su_attempt sua,
igs_ps_unit_ofr_opt uoo
WHERE sua.person_id = p_person_id
AND sua.course_cd = p_course_cd
AND sua.unit_cd = p_unit_cd
AND sua.cal_type = p_teach_cal_type
AND sua.ci_sequence_number = p_teach_ci_sequence_number
AND sua.uoo_id = p_uoo_id
AND uoo.unit_cd = p_unit_cd
AND uoo.cal_type = p_teach_cal_type
AND uoo.ci_sequence_number = p_teach_ci_sequence_number
AND uoo.version_number = sua.version_number
AND uoo.uoo_id = sua.uoo_id
AND anip.person_id = p_person_id
AND anip.course_cd = p_course_cd
AND ( ( p_unit_grading_ind = 'Y'
AND uoo.anon_unit_grading_ind = 'Y')
OR ( p_unit_grading_ind = 'N'
AND p_ass_id IS NOT NULL
AND uoo.anon_assess_grading_ind = 'Y'
AND 'Y' = ( SELECT ast.anon_grading_ind
FROM igs_as_assessmnt_typ ast,
igs_as_assessmnt_itm ai
WHERE ai.ass_id = p_ass_id
AND ai.assessment_type = ast.assessment_type)));
SELECT anonymous_id
FROM igs_as_anon_id_us aniu,
igs_en_su_attempt sua,
igs_ps_unit_ofr_opt uoo
WHERE sua.person_id = p_person_id
AND sua.course_cd = p_course_cd
AND sua.unit_cd = p_unit_cd
AND sua.cal_type = p_teach_cal_type
AND sua.ci_sequence_number = p_teach_ci_sequence_number
AND sua.uoo_id = p_uoo_id
AND uoo.uoo_id = sua.uoo_id
AND uoo.unit_cd = p_unit_cd
AND uoo.cal_type = p_teach_cal_type
AND uoo.ci_sequence_number = p_teach_ci_sequence_number
AND uoo.version_number = sua.version_number
AND aniu.person_id = p_person_id
AND aniu.course_cd = p_course_cd
AND aniu.unit_cd = p_unit_cd
AND aniu.teach_cal_type = p_teach_cal_type
AND aniu.teach_ci_sequence_number = p_teach_ci_sequence_number
AND aniu.uoo_id = p_uoo_id
AND ( ( p_unit_grading_ind = 'Y'
AND uoo.anon_unit_grading_ind = 'Y')
OR ( p_unit_grading_ind = 'N'
AND p_ass_id IS NOT NULL
AND uoo.anon_assess_grading_ind = 'Y'
AND 'Y' = ( SELECT ast.anon_grading_ind
FROM igs_as_assessmnt_typ ast,
igs_as_assessmnt_itm ai
WHERE ai.ass_id = p_ass_id
AND ai.assessment_type = ast.assessment_type)));
SELECT anonymous_id
FROM igs_as_anon_id_ass ania,
igs_en_su_attempt sua,
igs_ps_unit_ofr_opt uoo,
igs_ca_teach_to_load_v ttl
WHERE sua.person_id = p_person_id
AND sua.course_cd = p_course_cd
AND sua.unit_cd = p_unit_cd
AND sua.cal_type = p_teach_cal_type
AND sua.ci_sequence_number = p_teach_ci_sequence_number
AND sua.uoo_id = p_uoo_id
AND uoo.unit_cd = p_unit_cd
AND uoo.cal_type = p_teach_cal_type
AND uoo.ci_sequence_number = p_teach_ci_sequence_number
AND uoo.version_number = sua.version_number
AND uoo.uoo_id = sua.uoo_id
AND ania.person_id = p_person_id
AND ania.load_cal_type = ttl.load_cal_type
AND ania.load_ci_sequence_number = ttl.load_ci_sequence_number
AND ttl.teach_cal_type = p_teach_cal_type
AND ttl.teach_ci_sequence_number = p_teach_ci_sequence_number
AND ttl.load_start_dt = (SELECT MIN(ttl2.load_start_dt)
FROM igs_ca_teach_to_load_v ttl2
WHERE ttl2.teach_cal_type = p_teach_cal_type
AND ttl2.teach_ci_sequence_number = p_teach_ci_sequence_number)
AND ( ( p_unit_grading_ind = 'Y'
AND uoo.anon_unit_grading_ind = 'Y'
AND ania.assessment_type = (SELECT anm.assessment_type
FROM igs_as_anon_method anm
WHERE anm.method = 'ASSESSMENT'
AND anm.load_cal_type = ttl.load_cal_type))
OR ( p_ass_id IS NOT NULL
AND p_unit_grading_ind = 'N'
AND uoo.anon_assess_grading_ind = 'Y'
AND ania.assessment_type = (SELECT ai.assessment_type
FROM igs_as_assessmnt_typ ast,
igs_as_assessmnt_itm ai
WHERE ai.ass_id = p_ass_id
AND ai.assessment_type = ast.assessment_type
AND ast.anon_grading_ind = 'Y')));
SELECT anm.method
FROM igs_ca_teach_to_load_v ttl,
igs_as_anon_method anm
WHERE ttl.teach_cal_type = p_teach_cal_type
AND ttl.teach_ci_sequence_number = p_teach_ci_sequence_number
AND ttl.load_start_dt = (SELECT MIN(ttl2.load_start_dt)
FROM igs_ca_teach_to_load_v ttl2
WHERE ttl2.teach_cal_type = p_teach_cal_type
AND ttl2.teach_ci_sequence_number = p_teach_ci_sequence_number)
AND ttl.load_cal_type = anm.load_cal_type;
SELECT person_id
FROM igs_as_anon_id_ps anip
WHERE anip.anonymous_id = p_anonymous_id;
SELECT person_id
FROM igs_as_anon_id_us aniu,
igs_ca_teach_to_load_v ttl
WHERE aniu. anonymous_id = p_anonymous_id
AND aniu.load_cal_type = ttl.load_cal_type
AND aniu.load_ci_sequence_number = ttl.load_ci_sequence_number
AND ttl.teach_cal_type = p_teach_cal_type
AND ttl.teach_ci_sequence_number = p_teach_ci_sequence_number
AND ttl.load_start_dt = ( SELECT MIN(ttl2.load_start_dt)
FROM igs_ca_teach_to_load_v ttl2
WHERE ttl2.teach_cal_type = p_teach_cal_type
AND ttl2.teach_ci_sequence_number = p_teach_ci_sequence_number);
SELECT person_id
FROM igs_as_anon_id_ass ania,
igs_ca_teach_to_load_v ttl
WHERE ania. anonymous_id = p_anonymous_id
AND ania.load_cal_type = ttl.load_cal_type
AND ania.load_ci_sequence_number = ttl.load_ci_sequence_number
AND ttl.teach_cal_type = p_teach_cal_type
AND ttl.teach_ci_sequence_number = p_teach_ci_sequence_number
AND ttl.load_start_dt = ( SELECT MIN(ttl2.load_start_dt)
FROM igs_ca_teach_to_load_v ttl2
WHERE ttl2.teach_cal_type = p_teach_cal_type
AND ttl2.teach_ci_sequence_number = p_teach_ci_sequence_number);
SELECT anm.method
FROM igs_ca_teach_to_load_v ttl,
igs_as_anon_method anm
WHERE ttl.teach_cal_type = p_teach_cal_type
AND ttl.teach_ci_sequence_number = p_teach_ci_sequence_number
AND ttl.load_start_dt = (SELECT MIN(ttl2.load_start_dt)
FROM igs_ca_teach_to_load_v ttl2
WHERE ttl2.teach_cal_type = p_teach_cal_type
AND ttl2.teach_ci_sequence_number = p_teach_ci_sequence_number)
AND ttl.load_cal_type = anm.load_cal_type;