[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT cohiv.*
FROM igs_pr_cohort_inst_v cohiv
WHERE cohiv.cohort_name = cp_cohort_name
AND cohiv.load_cal_type = cp_cal_type
AND cohiv.load_ci_sequence_number = cp_ci_sequence_number ;
SELECT cohr.cohort_name
FROM igs_pr_cohort cohr
WHERE cohr.org_unit_cd = cp_org_unit_cd
MINUS
SELECT cohi.cohort_name
FROM igs_pr_cohort coh,
igs_pr_cohort_inst cohi
WHERE coh.cohort_name = cohi.cohort_name
AND coh.org_unit_cd = cp_org_unit_cd
AND cohi.load_cal_type = cp_cal_type
AND cohi.load_ci_sequence_number = cp_ci_sequence_number ;
SELECT cohi.*
FROM igs_pr_cohort coh,
igs_pr_cohort_inst cohi
WHERE coh.cohort_name = cohi.cohort_name
AND coh.org_unit_cd = cp_org_unit_cd
AND cohi.load_cal_type = cp_cal_type
AND cohi.load_ci_sequence_number = cp_ci_sequence_number ;
SELECT cohi.cohort_name
FROM igs_pr_cohort coh,
igs_pr_cohort_inst cohi
WHERE coh.cohort_name = cohi.cohort_name
AND coh.cohort_name = cp_cohort_name
AND coh.org_unit_cd = cp_org_unit_cd
AND cohi.load_cal_type = cp_cal_type
AND cohi.load_ci_sequence_number = cp_ci_sequence_number ;
SELECT coh.cohort_name
FROM igs_pr_cohort coh
WHERE coh.org_unit_cd = cp_org_unit_cd
AND coh.cohort_name = cp_cohort_name;
igs_pr_cohort_inst_pkg.INSERT_ROW(
x_rowid => l_rowid ,
x_cohort_name => p_cohort_name,
x_load_cal_type => p_cal_type ,
x_load_ci_sequence_number => p_ci_sequence_number ,
x_cohort_status => 'WORKING' ,
x_rank_status => 'WORKING' ,
x_run_date => SYSDATE
);
igs_pr_cohort_inst_pkg.insert_row(
x_rowid => l_rowid ,
x_cohort_name => cohort_old_rec.cohort_name,
x_load_cal_type => p_cal_type ,
x_load_ci_sequence_number => p_ci_sequence_number ,
x_cohort_status => 'WORKING' ,
x_rank_status => 'WORKING' ,
x_run_date => SYSDATE
);
igs_pr_cohort_inst_pkg.insert_row(
x_rowid => l_rowid ,
x_cohort_name => cohort_org_rec.cohort_name,
x_load_cal_type => p_cal_type ,
x_load_ci_sequence_number => p_ci_sequence_number ,
x_cohort_status => 'WORKING' ,
x_rank_status => 'WORKING' ,
x_run_date => SYSDATE
);
SELECT *
FROM igs_pr_cohort_inst_v
WHERE cohort_name = cp_cohort_name
AND load_cal_type = cp_cal_type
AND load_ci_sequence_number = cp_ci_sequence_number;
SELECT *
FROM igs_pr_cohort
WHERE cohort_name = cp_cohort_name ;
SELECT s_cal_cat
FROM igs_ca_type
WHERE cal_type = cp_cal_type ;
SELECT sup_cal_type, sup_ci_sequence_number
FROM igs_ca_inst_rel
WHERE
sub_cal_type = cp_cal_type
AND sub_ci_sequence_number = cp_ci_sequence_number
AND sup_cal_type IN (SELECT CAL_TYPE FROM IGS_CA_TYPE WHERE S_CAL_CAT = 'ACADEMIC') ;
SELECT cohi.*
FROM igs_pr_cohort_inst_rank_v cohi
WHERE cohi.cohort_name = cp_cohort_name
AND cohi.load_cal_type = cp_cal_type
AND cohi.load_ci_sequence_number = cp_ci_sequence_number ;
SELECT cohi.*
FROM igs_pr_cohort_inst_rank_v cohi
WHERE cohi.cohort_name = cp_cohort_name
AND cohi.load_cal_type = cp_cal_type
AND cohi.load_ci_sequence_number = cp_ci_sequence_number
AND cohi.person_id = cp_person_id
AND cohi.course_cd = cp_course_cd;
last_updated_by NUMBER(15),
last_update_date DATE,
last_update_login NUMBER(15),
request_id NUMBER(15),
program_application_id NUMBER(15),
program_id NUMBER(15),
program_update_date DATE,
cum_gpa NUMBER,
new_rank NUMBER
) ;
last_updated_by NUMBER(15),
last_update_date DATE,
last_update_login NUMBER(15),
request_id NUMBER(15),
program_application_id NUMBER(15),
program_id NUMBER(15),
program_update_date DATE,
cum_gpa NUMBER,
new_rank NUMBER
) ;
'SELECT res.*,
RANK() OVER (ORDER BY res.cum_gpa DESC) new_rank
FROM (SELECT cohiv.*,
igs_pr_class_rank.get_cum_gpa (
cohiv.person_id,
cohiv.course_cd,
cohiv.cohort_name,
cohiv.load_cal_type,
cohiv.load_ci_sequence_number,
''' || stat_type_rec.stat_type || ''',
'''|| l_cumulative_ind || '''
) cum_gpa
FROM igs_pr_cohort_inst_rank_v cohiv
WHERE cohiv.cohort_name = ''' || p_cohort_name || '''
AND cohiv.load_cal_type = ''' || p_cal_type || '''
AND cohiv.load_ci_sequence_number = ' || p_ci_sequence_number || ') res' ;
-- These records need to updated with the recent Ranking anf the GPA.
FOR cohort_inst_person_rec IN cur_cohort_inst_person(p_cohort_name,p_cal_type,p_ci_sequence_number,frozen_rank_rec.person_id,frozen_rank_rec.course_cd) LOOP -- Loop2
--
-- Updating the Cohert Instance rank table with the recent ranking and the GPA value as of now. Rest of the values are retained.
--
igs_pr_cohinst_rank_pkg.update_row (
x_rowid => cohort_inst_person_rec.row_id ,
x_cohort_name => cohort_inst_person_rec.cohort_name,
x_load_cal_type => cohort_inst_person_rec.load_cal_type ,
x_load_ci_sequence_number => cohort_inst_person_rec.load_ci_sequence_number ,
x_person_id => cohort_inst_person_rec.person_id ,
x_course_cd => cohort_inst_person_rec.course_cd ,
x_as_of_rank_gpa => frozen_rank_rec.cum_gpa ,
x_cohort_rank => frozen_rank_rec.new_rank ,
x_cohort_override_rank => cohort_inst_person_rec.cohort_override_rank ,
x_comments => cohort_inst_person_rec.comments
);
'SELECT res.*,
DENSE_RANK() OVER (ORDER BY res.cum_gpa DESC) new_rank
FROM (SELECT cohiv.*,
igs_pr_class_rank.get_cum_gpa (
cohiv.person_id,
cohiv.course_cd,
cohiv.cohort_name,
cohiv.load_cal_type,
cohiv.load_ci_sequence_number,
''' || stat_type_rec.stat_type || ''',
'''|| l_cumulative_ind || '''
) cum_gpa
FROM igs_pr_cohort_inst_rank_v cohiv
WHERE cohiv.cohort_name = ''' || p_cohort_name || '''
AND cohiv.load_cal_type = ''' || p_cal_type || '''
AND cohiv.load_ci_sequence_number = ' || p_ci_sequence_number || ') res' ;
-- These records need to updated with the recent Ranking anf the GPA.
FOR cohort_inst_person_rec IN cur_cohort_inst_person(p_cohort_name,p_cal_type,p_ci_sequence_number,frozen_denserank_rec.person_id,frozen_denserank_rec.course_cd) LOOP
--
-- Updating the Cohert Instance rank table with the recent ranking and the GPA value as of now. Rest of the values are retained.
--
igs_pr_cohinst_rank_pkg.update_row (
x_rowid => cohort_inst_person_rec.row_id ,
x_cohort_name => cohort_inst_person_rec.cohort_name,
x_load_cal_type => cohort_inst_person_rec.load_cal_type ,
x_load_ci_sequence_number => cohort_inst_person_rec.load_ci_sequence_number ,
x_person_id => cohort_inst_person_rec.person_id ,
x_course_cd => cohort_inst_person_rec.course_cd ,
x_as_of_rank_gpa => frozen_denserank_rec.cum_gpa ,
x_cohort_rank => frozen_denserank_rec.new_rank ,
x_cohort_override_rank => cohort_inst_person_rec.cohort_override_rank ,
x_comments => cohort_inst_person_rec.comments
);
igs_pr_cohort_inst_pkg.update_row(
x_rowid => rank_status_rec.row_id,
x_cohort_name => rank_status_rec.cohort_name,
x_load_cal_type => rank_status_rec.load_cal_type,
x_load_ci_sequence_number => rank_status_rec.load_ci_sequence_number,
x_cohort_status => rank_status_rec.cohort_status,
x_rank_status => rank_status_rec.rank_status,
x_run_date => SYSDATE
);
' SELECT res.* , RANK () OVER (order by res.cum_gpa desc) AS new_rank
FROM
(SELECT person_id, course_cd, igs_pr_class_rank.get_cum_gpa ( sca.person_id,sca.course_cd,''' || p_cohort_name || ''',
''' || p_cal_type || ''',' || p_ci_sequence_number || ',
''' || stat_type_rec.stat_type ||''',
''' || l_cumulative_ind || ''' ) cum_gpa
FROM igs_en_sca_v sca
WHERE
sca.cal_type IN ' || l_acad_cal || ' AND
(sca.person_id, sca.course_cd) IN
(
SELECT sca.person_id, sca.course_cd
FROM igs_en_su_attempt sua, igs_en_sca_v sca
WHERE sua.person_id = sca.person_id
AND sua.course_cd = sca.course_cd
AND unit_attempt_status = ''COMPLETED''
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_cal_type || '''
AND load_ci_sequence_number = ' || p_ci_sequence_number || '
)
)
AND
igs_pr_class_rank.rulp_val_senna_res (
sca.person_id,
sca.course_cd,
sca.version_number,
NULL,
NULL,
''' || p_cal_type || ''',
' || p_ci_sequence_number || ','
|| stat_type_rec.rule_sequence_number || ') = ''true'' ) res';
-- If the students existinf in the old cohort instance list and the new cohort instance then the old rank and the GPA for the student is updated in the
-- Cohort Instance Rank table.
l_old_flag := 'N';
igs_pr_cohinst_rank_pkg.update_row (
x_rowid => l_old_population_table_rec(i).p_rowid ,
x_cohort_name => l_old_population_table_rec(i).p_cohort_name,
x_load_cal_type => l_old_population_table_rec(i).p_load_cal_type ,
x_load_ci_sequence_number => l_old_population_table_rec(i).p_load_ci_sequence_number ,
x_person_id => l_old_population_table_rec(i).p_person_id ,
x_course_cd => l_old_population_table_rec(i).p_course_cd ,
x_as_of_rank_gpa => l_new_population_table_rec(l_new_count).p_as_of_rank_gpa ,
x_cohort_rank => l_new_population_table_rec(l_new_count).p_cohort_rank ,
x_cohort_override_rank => l_old_population_table_rec(i).p_cohort_override_rank ,
x_comments => l_old_population_table_rec(i).p_comments
);
-- records for the Student Cohort Instance rank has to be deleted from the table Cohort Instance Rank,
-- which are not avaliable in the new list.
l_new_population_table_rec(l_new_count).p_deletion_indicator := 'N' ;
igs_pr_cohinst_rank_pkg.insert_row (
x_rowid => l_rowid ,
x_cohort_name => l_new_population_table_rec(l_new_count).p_cohort_name ,
x_load_cal_type => l_new_population_table_rec(l_new_count).p_load_cal_type ,
x_load_ci_sequence_number => l_new_population_table_rec(l_new_count).p_load_ci_sequence_number,
x_person_id => l_new_population_table_rec(l_new_count).p_person_id ,
x_course_cd => l_new_population_table_rec(l_new_count).p_course_cd ,
x_as_of_rank_gpa => l_new_population_table_rec(l_new_count).p_as_of_rank_gpa ,
x_cohort_rank => l_new_population_table_rec(l_new_count).p_cohort_rank ,
x_cohort_override_rank => l_new_population_table_rec(l_new_count).p_cohort_override_rank ,
x_comments => l_new_population_table_rec(l_new_count).p_comments
);
igs_pr_cohinst_rank_pkg.delete_row (
x_rowid => l_old_population_table_rec(j).p_rowid
);
' SELECT res.* , DENSE_RANK () OVER (order by res.cum_gpa desc) AS new_rank
FROM
(SELECT person_id, course_cd, igs_pr_class_rank.get_cum_gpa ( sca.person_id,sca.course_cd,''' || p_cohort_name || ''',
''' || p_cal_type || ''',' || p_ci_sequence_number || ',
''' || stat_type_rec.stat_type ||''',
''' || l_cumulative_ind || ''' ) cum_gpa
FROM igs_en_sca_v sca
WHERE sca.cal_type IN ' || l_acad_cal || '
AND
(sca.person_id, sca.course_cd) IN
(
SELECT sca.person_id, sca.course_cd
FROM igs_en_su_attempt sua, igs_en_sca_v sca
WHERE sua.person_id = sca.person_id
AND sua.course_cd = sca.course_cd
AND unit_attempt_status = ''COMPLETED''
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_cal_type || '''
AND load_ci_sequence_number = ' || p_ci_sequence_number || '
)
)
AND
igs_pr_class_rank.rulp_val_senna_res (
sca.person_id,
sca.course_cd,
sca.version_number,
NULL,
NULL,
''' || p_cal_type || ''',
' || p_ci_sequence_number || ','
|| stat_type_rec.rule_sequence_number || ') = ''true'' ) res';
-- If the students existinf in the old cohort instance list and the new cohort instance then the old rank and the GPA for the student is updated in the
-- Cohort Instance Rank table.
l_old_flag := 'N';
igs_pr_cohinst_rank_pkg.update_row (
x_rowid => l_old_population_table_rec(i).p_rowid ,
x_cohort_name => l_old_population_table_rec(i).p_cohort_name,
x_load_cal_type => l_old_population_table_rec(i).p_load_cal_type ,
x_load_ci_sequence_number => l_old_population_table_rec(i).p_load_ci_sequence_number ,
x_person_id => l_old_population_table_rec(i).p_person_id ,
x_course_cd => l_old_population_table_rec(i).p_course_cd ,
x_as_of_rank_gpa => l_new_population_table_rec(l_new_count).p_as_of_rank_gpa ,
x_cohort_rank => l_new_population_table_rec(l_new_count).p_cohort_rank ,
x_cohort_override_rank => l_old_population_table_rec(i).p_cohort_override_rank ,
x_comments => l_old_population_table_rec(i).p_comments
);
-- records for the Student Cohort Instance rank has to be deleted from the table Cohort Instance Rank,
-- which are not avaliable in the new list.
l_new_population_table_rec(l_new_count).p_deletion_indicator := 'N' ;
-- Records existing in the new list and not in the old list . Those records are inserted in the Cohort Instance Rank table
END LOOP;
igs_pr_cohinst_rank_pkg.insert_row (
x_rowid => l_rowid ,
x_cohort_name => l_new_population_table_rec(l_new_count).p_cohort_name ,
x_load_cal_type => l_new_population_table_rec(l_new_count).p_load_cal_type ,
x_load_ci_sequence_number => l_new_population_table_rec(l_new_count).p_load_ci_sequence_number,
x_person_id => l_new_population_table_rec(l_new_count).p_person_id ,
x_course_cd => l_new_population_table_rec(l_new_count).p_course_cd ,
x_as_of_rank_gpa => l_new_population_table_rec(l_new_count).p_as_of_rank_gpa ,
x_cohort_rank => l_new_population_table_rec(l_new_count).p_cohort_rank ,
x_cohort_override_rank => l_new_population_table_rec(l_new_count).p_cohort_override_rank ,
x_comments => l_new_population_table_rec(l_new_count).p_comments
);
igs_pr_cohinst_rank_pkg.delete_row (
x_rowid => l_old_population_table_rec(j).p_rowid
);
igs_pr_cohort_inst_pkg.update_row(
x_rowid => rank_status_rec.row_id,
x_cohort_name => rank_status_rec.cohort_name,
x_load_cal_type => rank_status_rec.load_cal_type,
x_load_ci_sequence_number => rank_status_rec.load_ci_sequence_number,
x_cohort_status => rank_status_rec.cohort_status,
x_rank_status => rank_status_rec.rank_status,
x_run_date => SYSDATE
);
SELECT dflt_display_type
FROM igs_pr_cohort
WHERE cohort_name = cp_cohort_name;
SELECT NVL(cohort_override_rank, cohort_rank)
FROM igs_pr_cohort_inst_rank_v cohirv
WHERE cohirv.cohort_name = cp_cohort_name
AND cohirv.load_cal_type = cp_cal_type
AND cohirv.load_ci_sequence_number = cp_ci_sequence_number
AND cohirv.person_id = cp_person_id
AND cohirv.course_cd = cp_program_cd;
SELECT COUNT (*)
FROM igs_pr_cohort_inst_rank_v cohirv
WHERE cohirv.cohort_name = cp_cohort_name
AND cohirv.load_cal_type = cp_cal_type
AND cohirv.load_ci_sequence_number = cp_ci_sequence_number;
SELECT LTRIM (TO_CHAR (TO_DATE (cp_rank,'J'),'Jth'),'0') FROM DUAL;
FND_MESSAGE.SET_TOKEN('NAME','Insert_Row : '||SQLERRM);