[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
expand_gsch_set to include DISTINCT in select query as the
rule got modified as per bug 2287084
svenkata 12-Dec-2001 Added prgp_cal_cp(), pr_rul_msg() Functions. Modified turing() Function
w.r.t Progression Rules Enhancement DLD, Bug No: 2146547.
rbezawad 12-Dec-2001 Added prgpl_chk_gsch_exists(), prgp_cal_cp_gsch(), expand_gsch_set() Functions.
Modified expand_set() Function w.r.t Progression Rules Enhancement DLD, Bug No: 2146547.
smadathi 07-JUN-2001 The functions ref_set, perid_chk, plc_chk,stg_set added . The length of
some of the variables were changed . The changes are as per enhancement bug No. 1775394
nalkumar 18-JUN-2001 The function rupl_get_alwd_cp added. The changes are as per enhancement bug No. 1830175
Navin 27-Aug-2001 Added a package variable igs_ru_gen_001.p_evaluated_part
Created a turing function 'ifthen' and modified the logic of
Rulp_Val_Senna as per the requirement of Bug# : 1899513.
(reverse chronological order - newest change first)
***************************************************************/
g_debug_level CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
last_insert BINARY_INTEGER
);
mainly used by the set looping functions (select_set,sum_func,for_expand)
*/
TYPE r_tds IS RECORD (
set_number BINARY_INTEGER,
member_index BINARY_INTEGER, /* added 31/08/99 */
unit_cd VARCHAR2(30),
unit_version VARCHAR2(10),
cal_type VARCHAR2(10),
ci_sequence_number VARCHAR2(10),
rule_outcome IGS_RU_ITEM.value%TYPE,
uoo_id VARCHAR2(10)
);
SELECT rul_sequence_number
INTO v_rule_number
FROM IGS_PS_UNIT_VER_RU
WHERE unit_cd = p_unit_cd
AND version_number = p_version
AND s_rule_call_cd = p_rule_call_name;
SELECT rul_sequence_number
INTO v_rule_number
FROM IGS_PS_VER_RU
WHERE course_cd = p_course_cd
AND version_number = p_version
AND s_rule_call_cd = p_rule_call_name;
SELECT rul_sequence_number
INTO v_rule_number
FROM IGS_EN_UNIT_SET_RULE
WHERE unit_set_cd = p_unit_set_cd
AND version_number = p_version
AND s_rule_call_cd = p_rule_call_name;
SELECT rul_sequence_number
INTO v_rule_number
FROM IGS_PS_STAGE_RU
WHERE course_cd = p_course_cd
AND version_number = p_course_version
AND cst_sequence_number = p_cst_sequence_number
AND s_rule_call_cd = p_rule_call_name;
SELECT nr_rul_sequence_number
INTO v_rule_number
FROM IGS_RU_CALL_RULE
WHERE called_rule_cd = p_called_rule_cd;
SELECT cst.sequence_number
INTO v_cst_sequence_number
FROM IGS_PS_STAGE cst
WHERE cst.course_cd = p_course_cd
AND cst.version_number = p_course_version
AND cst.course_stage_type = p_course_stage_type;
SELECT repeatable_ind
INTO v_repeatable_ind
FROM IGS_PS_UNIT_VER
WHERE unit_cd = p_tds.unit_cd
AND version_number = p_tds.unit_version;
SELECT NVL(achievable_credit_points,enrolled_credit_points)
INTO v_achievable_credit_points
FROM IGS_PS_UNIT_VER
WHERE unit_cd = p_unit_cd
AND version_number = p_unit_version;
SELECT NVL(cps.achievable_credit_points,NVL(uv.achievable_credit_points,NVL(cps.enrolled_credit_points,uv.enrolled_credit_points)))
INTO l_n_credit_points
FROM IGS_PS_UNIT_VER uv,
IGS_PS_UNIT_OFR_OPT uoo,
IGS_PS_USEC_CPS cps
WHERE uoo.uoo_id=cps.uoo_id(+) AND
uoo.uoo_id=p_uoo_id AND
uoo.unit_cd = uv.unit_cd AND
uoo.version_number= uv.version_number;
SELECT SUM(nvl(achievable_credit_points,0))
INTO v_override_achievable_cp
FROM IGS_AV_STND_UNIT
WHERE as_course_cd = p_course_cd
AND person_id = p_person_id
AND unit_cd = gv_member(p_member_index).f1
AND version_number = gv_member(p_member_index).f2
AND s_adv_stnd_granting_status IN ('APPROVED','GRANTED')
AND s_adv_stnd_recognition_type = 'CREDIT'
AND (igs_av_val_asu.granted_adv_standing(person_id,as_course_cd,as_version_number,unit_cd,version_number,'BOTH',NULL) ='TRUE');
SELECT unit_attempt_status,
override_achievable_cp
INTO v_unit_attempt_status,
v_override_achievable_cp
FROM IGS_EN_SU_ATTEMPT
WHERE person_id = p_person_id
AND course_cd = p_course_cd
AND uoo_id = gv_member(p_member_index).f5;
SELECT unit_attempt_status,
override_achievable_cp
INTO v_unit_attempt_status,
v_override_achievable_cp
FROM IGS_EN_SU_ATTEMPT
WHERE person_id = p_person_id
AND course_cd = p_course_cd
AND uoo_id = gv_member(p_member_index).f5;
SELECT av.grading_schema_cd, av.grade
FROM igs_av_stnd_unit av , igs_as_grd_sch_grade grad
WHERE av.as_course_cd = p_course_cd
AND av.person_id = p_person_id
AND av.unit_cd = gv_member(p_member_index).f1
AND av.version_number = gv_member(p_member_index).f2
AND av.s_adv_stnd_granting_status IN ('APPROVED','GRANTED')
AND av.s_adv_stnd_recognition_type = 'CREDIT'
AND (igs_av_val_asu.granted_adv_standing(av.person_id,av.as_course_cd,av.as_version_number,av.unit_cd,av.version_number,'BOTH',NULL) ='TRUE')
AND grad.grading_schema_cd = av.grading_schema_cd
AND grad.version_number = av.grd_sch_version_number
AND grad.grade = av.grade
ORDER BY grad.rank ASC;
all though it trys to resolve cp's by selecting latest (for duplicates)
*/
FUNCTION sua_select_unique(
p_attribute IN VARCHAR2,
p_member_index IN BINARY_INTEGER,
p_set1 IN NUMBER )
RETURN VARCHAR2 IS
/*
'true' or 'false'
*/
v_member BINARY_INTEGER;
log_error('sua_select_unique',
'Invalid attribute ('||p_attribute||')');
END sua_select_unique;
SELECT location_cd,
unit_class
INTO v_location_cd,
v_unit_class
FROM IGS_EN_SU_ATTEMPT
WHERE person_id = p_person_id
AND course_cd = p_course_cd
AND uoo_id = p_tds.uoo_id;
SELECT rank
INTO v_lhs_rank
FROM IGS_AS_GRD_SCH_GRADE
WHERE grading_schema_cd = v_grading_schema
AND version_number = v_gs_version_number
AND grade = p_lhs_grade;
SELECT rank
INTO v_rhs_rank
FROM IGS_AS_GRD_SCH_GRADE
WHERE grading_schema_cd = v_grading_schema
AND version_number = v_gs_version_number
AND grade = p_rhs_grade;
SELECT av.grading_schema_cd, av.grd_sch_version_number
FROM IGS_AV_STND_UNIT av , igs_as_grd_sch_grade grad
WHERE av.as_course_cd = p_course_cd
AND av.person_id = p_person_id
AND av.unit_cd = p_tds.unit_cd
AND av.version_number = p_tds.unit_version
AND av.s_adv_stnd_granting_status IN ('APPROVED','GRANTED')
AND av.s_adv_stnd_recognition_type = 'CREDIT'
AND (igs_av_val_asu.granted_adv_standing(av.person_id,av.as_course_cd,av.as_version_number,av.unit_cd,av.version_number,'BOTH',NULL) ='TRUE')
AND grad.grading_schema_cd = av.grading_schema_cd
AND grad.version_number = av.grd_sch_version_number
AND grad.grade = av.grade
ORDER BY grad.rank ASC;
SELECT rank
INTO v_lhs_rank
FROM IGS_AS_GRD_SCH_GRADE
WHERE grading_schema_cd = v_grading_schema
AND version_number = v_gs_version_number
AND grade = p_lhs_grade;
SELECT rank
INTO v_rhs_rank
FROM IGS_AS_GRD_SCH_GRADE
WHERE grading_schema_cd = v_grading_schema
AND version_number = v_gs_version_number
AND grade = p_rhs_grade;
SELECT student_confirmed_ind,
end_dt,
rqrmnts_complete_ind,
primary_set_ind
INTO v_student_confirmed_ind,
v_end_dt,
v_rqrmnts_complete_ind,
v_primary_set_ind
FROM IGS_AS_SU_SETATMPT
WHERE person_id = p_person_id
AND course_cd = p_course_cd
AND unit_set_cd = p_tds.unit_cd
AND us_version_number = p_tds.unit_version
AND sequence_number = p_tds.cal_type;
RETURN 'SELECTED';
SELECT unit_set_cat
INTO v_unit_set_cat
FROM IGS_EN_UNIT_SET
WHERE unit_set_cd = p_tds.unit_cd
AND version_number = p_tds.unit_version;
SELECT SUM(credit_points)
INTO v_credit_points
FROM IGS_AV_STND_UNIT_LVL
WHERE person_id = p_person_id
AND as_course_cd = p_course_cd
AND s_adv_stnd_granting_status IN ('GRANTED')
/*
AND as_version_number = p_course_version
*/
AND s_adv_stnd_type = p_tds.unit_cd
AND unit_level = p_tds.unit_version
AND crs_group_ind = p_tds.cal_type
AND exemption_institution_cd = p_tds.ci_sequence_number;
SELECT wam_weighting
INTO v_wam_weighting
FROM IGS_PS_UNIT_LVL
WHERE unit_cd = gv_member(p_member_index).f1
AND version_number = gv_member(p_member_index).f2
AND course_cd = p_course_cd
AND version_number = p_course_version
AND unit_level = v_unit_level;
SELECT wam_weighting
INTO v_wam_weighting
FROM IGS_PS_UNIT_LEVEL
WHERE unit_level = v_unit_level;
SELECT message_rule
INTO v_message_rule
FROM IGS_RU_NAMED_RULE
WHERE rul_sequence_number = p_rule_number;
gv_set(gv_set_index).last_insert := NULL;
It is a simple linked list with the exception that a last insert pointer is
maintained so that successive members can be added without searching the
entire structure.
*/
FUNCTION add_member(
p_set_number IN BINARY_INTEGER,
p_f1 IN VARCHAR2,
p_f2 IN VARCHAR2,
p_f3 IN VARCHAR2,
p_f4 IN VARCHAR2,
p_f5 IN VARCHAR2)
RETURN BINARY_INTEGER IS
v_current_ptr BINARY_INTEGER;
v_current_ptr := gv_set(p_set_number).last_insert;
gv_set(p_set_number).last_insert := gv_member_index;
gv_set(p_set_number).last_insert := gv_member_index;
gv_set(p_set_number).last_insert := v_current_ptr;
gv_set(p_set_number).last_insert := gv_member_index;
insert new member
*/
gv_member(gv_member_index).next := gv_member(v_current_ptr).next;
gv_set(p_set_number).last_insert := gv_member_index;
gv_set(p_set_number).last_insert := v_current_ptr;
use compare to do ordered insert of members
*/
v_compare := compare_members(v_l_index,v_r_index);
only used in sua_select_unique call
*/
FUNCTION sua_duplicate_set (
p_tds IN r_tds )
RETURN BINARY_INTEGER IS
v_member BINARY_INTEGER;
Select student units
*/
FUNCTION student
RETURN BINARY_INTEGER IS
BEGIN
IF gv_sua_set IS NOT NULL
THEN
RETURN gv_sua_set;
SELECT unit_cd,
version_number,
cal_type,
ci_sequence_number,
uoo_id
FROM IGS_EN_SU_ATTEMPT
WHERE person_id = p_person_id
AND course_cd = p_course_cd
AND (p_param_8 IS NULL OR p_param_8 <> uoo_id)
-- AND sup_unit_cd IS NULL 99/05/04 hrt
ORDER BY unit_cd,IGS_GE_NUMBER.TO_CANN(version_number),
cal_type,IGS_GE_NUMBER.TO_CANN(ci_sequence_number) )
LOOP
gv_sua_set := add_member(gv_sua_set,
student_unit_attempts.unit_cd,
student_unit_attempts.version_number,
student_unit_attempts.cal_type,
student_unit_attempts.ci_sequence_number,
student_unit_attempts.uoo_id);
SELECT DISTINCT B.NAME GRADE
FROM IGS_AD_TEST_RESULTS A,
IGS_AD_CODE_CLASSES B
WHERE A.PERSON_ID = cp_n_person_id
AND A.ADMISSION_TEST_TYPE = cp_c_grade
AND A.GRADE_ID IS NOT NULL
AND A.ACTIVE_IND = 'Y'
AND A.GRADE_ID = B.CODE_ID ;
Select advanced standing units
*/
-- Added distinct to the query which gets advanced standing unit level so that the same records
-- are not processed later.
FUNCTION advanced_standing
RETURN BINARY_INTEGER IS
v_cal_type IGS_CA_TYPE.cal_type%TYPE;
SELECT DISTINCT unit_cd, version_number
FROM IGS_AV_STND_UNIT
WHERE person_id = p_person_id
AND as_course_cd = p_course_cd
AND s_adv_stnd_granting_status IN ('APPROVED','GRANTED')
AND s_adv_stnd_recognition_type = 'CREDIT'
AND (igs_av_val_asu.granted_adv_standing(person_id,as_course_cd,as_version_number,
unit_cd,version_number,'BOTH',NULL) ='TRUE')
ORDER BY unit_cd,IGS_GE_NUMBER.TO_CANN(version_number) )
LOOP
/*
get ONE earliest calander for student to use in advanced standing set
modified this logic to be based on program offering options instead of unit attempts,bug#4283221
*/
FOR cur_ec IN (
SELECT a.cal_type,
a.ci_sequence_number,
ci.start_dt
FROM IGS_PS_UNIT_OFR_OPT A, igs_ca_inst ci
WHERE a.unit_cd = asu.unit_cd
AND a.version_number = asu.version_number
AND a.cal_type = ci.cal_type
AND a.ci_sequence_number = ci.sequence_number
ORDER BY ci.start_dt )
LOOP
v_cal_type := cur_ec.cal_type;
Select advanced standing unit level set
*/
-- Added distinct to the query which gets advanced standing unit level so that the same records
-- are not processed later.
FUNCTION advanced_standing_unit_level
RETURN BINARY_INTEGER IS
BEGIN
IF gv_asul_set IS NOT NULL
THEN
RETURN gv_asul_set;
SELECT DISTINCT s_adv_stnd_type, unit_level, crs_group_ind, exemption_institution_cd
FROM IGS_AV_STND_UNIT_LVL
WHERE person_id = p_person_id
AND as_course_cd = p_course_cd
AND s_adv_stnd_granting_status IN ('GRANTED')
ORDER BY s_adv_stnd_type,unit_level,crs_group_ind,exemption_institution_cd )
LOOP
gv_asul_set := add_member(gv_asul_set,
ASUL.s_adv_stnd_type, /* VARCHAR2(10) */
ASUL.unit_level, /* VARCHAR2(10) */
ASUL.crs_group_ind, /* VARCHAR2(10) */
ASUL.exemption_institution_cd, /* VARCHAR2(10) */
''); /* VARCHAR2(10) */
SELECT unit_set_cd,
us_version_number,
sequence_number
FROM IGS_AS_SU_SETATMPT
WHERE person_id = p_person_id
AND course_cd = p_course_cd
ORDER BY unit_set_cd,us_version_number,sequence_number )
LOOP
gv_susa_set := add_member(gv_susa_set,
SUSA.unit_set_cd,
SUSA.us_version_number,
SUSA.sequence_number,
'','');
p_select_string IN VARCHAR2 )
RETURN BINARY_INTEGER IS
v_cursor INTEGER;
only select allowed
*/
DBMS_SQL.PARSE(v_cursor,
'SELECT'||SUBSTR(LTRIM(p_select_string),7),
dbms_sql.v7);
SELECT peg.group_cd
FROM igs_pe_persid_group peg,igs_pe_prsid_grp_mem pegm
WHERE peg.group_id = pegm.group_id
AND pegm.person_id = p_person_id ;
SELECT d.unit_cd
FROM igs_ad_up_detail d
WHERE EXISTS (
SELECT 'x'
FROM igs_ad_test_results atr,
igs_ad_up_header h
WHERE atr.person_id = p_person_id
AND atr.admission_test_type = h.admission_test_type
AND h.up_header_id = d.up_header_id);
SELECT admission_test_type,
test_segment_id,
definition_level,
min_score,
max_score
FROM igs_ad_up_detail det,
igs_ad_up_header hed
WHERE det.up_header_id = hed.up_header_id
AND det.unit_cd = cp_unit_cd
AND det.closed_ind ='N';
SELECT '1' FROM IGS_AD_TEST_RESULTS
WHERE person_id = cp_person_id
AND admission_test_type = cp_admission_test_type
AND NVL(comp_test_score,0) BETWEEN cp_min_score AND cp_max_score;
SELECT '1' FROM IGS_AD_TEST_RESULTS TST,
IGS_AD_TST_RSLT_DTLS TDTL
WHERE tst.person_id = cp_person_id
AND tst.admission_test_type = cp_admission_test_type
AND tst.test_results_id = tdtl.test_results_id
AND tdtl.test_segment_id = cp_test_segment_id
AND tdtl.test_score BETWEEN cp_min_score AND cp_max_score;
SELECT cst.sequence_number
FROM igs_ps_stage cst
WHERE cst.course_cd = lc_course_cd
AND cst.version_number = lc_version_number
AND cst.course_stage_type = lc_course_stage_type ;
SELECT igs_ru_gen_003.rulp_get_rule (rul_sequence_number)
FROM igs_ps_stage_ru
WHERE course_cd = lc_course_cd
AND version_number = lc_version_number
AND cst_sequence_number = lc_cst_sequence_number
AND s_rule_call_cd = lc_course_stage_type;
SELECT max_cp_per_teaching_period
FROM IGS_PS_VER
WHERE course_cd = p_course_cd AND
version_number = p_course_version;
SELECT unit_cd,
version_number
FROM IGS_PS_UNIT_VER
WHERE unit_cd LIKE (p_unit_cd)
ORDER BY unit_cd,IGS_GE_NUMBER.TO_CANN(version_number) )
LOOP
IF in_versions(uv.version_number,
p_versions) = TRUE
THEN
IF p_person_id IS NULL OR p_rule_call_name IN ('PREREQ','COREQ')
THEN
/*
no student, no cal_type
*/
v_set1 := add_member(p_set1,
uv.unit_cd,
uv.version_number,
'','','');
SELECT unt_oo.cal_type,unt_oo.ci_sequence_number,unt_oo.uoo_id
FROM IGS_PS_UNIT_OFR_OPT unt_oo
WHERE unt_oo.unit_cd = uv.unit_cd AND
unt_oo.version_number = uv.version_number
ORDER BY unt_oo.cal_type, IGS_GE_NUMBER.TO_CANN(unt_oo.ci_sequence_number)
)
LOOP
v_set1 := add_member(p_set1,
uv.unit_cd,
uv.version_number,
uoo.cal_type,
uoo.ci_sequence_number,
uoo.uoo_id);
SELECT rsm.unit_cd rms_unit_cd,
rsm.versions rms_versions,
uvr.unit_cd uvr_unit_cd,
uvr.version_number uvr_version_number
FROM IGS_PS_UNIT_VER_RU uvr,
igs_ru_item rui,
igs_ru_set_member rsm
WHERE rsm.unit_cd = p_unit_cd
AND uvr.s_rule_call_cd = 'TRANS'
AND rui.rul_sequence_number = uvr.rul_sequence_number
AND rsm.rs_sequence_number = rui.set_number
ORDER BY uvr.unit_cd,IGS_GE_NUMBER.TO_CANN(uvr.version_number) )
LOOP
IF in_versions(p_unit_version,SNART.rms_versions)
THEN
FOR UOO IN (
SELECT UNIQUE
unit_cd,
version_number,
cal_type,
ci_sequence_number,
uoo_id
FROM IGS_PS_UNIT_OFR_OPT
WHERE unit_cd = SNART.uvr_unit_cd
AND version_number = SNART.uvr_version_number
ORDER BY unit_cd,IGS_GE_NUMBER.TO_CANN(version_number),
cal_type,IGS_GE_NUMBER.TO_CANN(ci_sequence_number) )
LOOP
v_set1 := add_member(v_set1,
UOO.unit_cd,
UOO.version_number,
UOO.cal_type,
UOO.ci_sequence_number,
UOO.uoo_id);
SELECT unit_cd,
versions
FROM IGS_RU_SET_MEMBER
WHERE rs_sequence_number = p_set1
ORDER BY unit_cd )
LOOP
/*
expand to all unit offering options (version and cal)
*/
v_set2 := expand_uoo(v_set2,
set_members.unit_cd,
set_members.versions);
SELECT unit_cd,
versions
FROM IGS_RU_SET_MEMBER
WHERE rs_sequence_number = p_set1
ORDER BY unit_cd )
LOOP
FOR US IN (
SELECT unit_set_cd,
version_number
FROM IGS_EN_UNIT_SET
WHERE unit_set_cd LIKE (set_members.unit_cd)
ORDER BY unit_set_cd,IGS_GE_NUMBER.TO_CANN(version_number) )
LOOP
IF in_versions(US.version_number,
set_members.versions) = TRUE
THEN
/*
has big potential for error (sequence_number) hrt
*/
v_set2 := add_member(v_set2,
US.unit_set_cd,
US.version_number,
'','','');
SELECT unit_cd,
versions
FROM IGS_RU_SET_MEMBER
WHERE rs_sequence_number = p_set1
ORDER BY unit_cd )
LOOP
FOR CRS IN (
SELECT course_cd,
version_number
FROM IGS_PS_VER
WHERE course_cd LIKE (set_members.unit_cd)
ORDER BY course_cd,IGS_GE_NUMBER.TO_CANN(version_number) )
LOOP
IF in_versions(CRS.version_number,
set_members.versions) = TRUE
THEN
v_set2 := add_member(v_set2,
CRS.course_cd,
CRS.version_number,
'','','');
SELECT unit_cd
FROM igs_ru_set_member
WHERE rs_sequence_number = p_set1
ORDER BY unit_cd ;
/* added DISTINCT to select query as the rule 34420 has DISTINCT in its query */
CURSOR cur_gscd_set_members(cp_grading_schema_cd igs_as_grd_sch_grade.grading_schema_cd%TYPE,
cp_grade igs_as_grd_sch_grade.grade%TYPE) IS
SELECT DISTINCT grading_schema_cd, grade
FROM igs_as_grd_sch_grade
WHERE grading_schema_cd = cp_grading_schema_cd
AND grade = cp_grade
ORDER BY grading_schema_cd;
SELECT set_type
INTO v_set_type
FROM IGS_RU_SET
WHERE sequence_number = p_set1;
SELECT unit_cd
FROM IGS_RU_SET_MEMBER
WHERE rs_sequence_number = p_set1
ORDER BY unit_cd )
LOOP
v_set2 := add_member(v_set2,
set_members.unit_cd
,'','','','');
Select members which satisfy the attribute rule criteria
*/
FUNCTION select_set(
p_set1 BINARY_INTEGER,
p_where_rule NUMBER )
RETURN BINARY_INTEGER IS
v_member BINARY_INTEGER;
log_error('select_set',
'Invalid condition rule return ('||v_result||')'||Fnd_Global.Local_Chr(10)||
'rule='||p_where_rule);
END select_set;
Select N members from an ordered set
*/
FUNCTION select_N_members (
p_members IN NUMBER,
p_set1 IN BINARY_INTEGER,
p_order_by_rule IN NUMBER )
RETURN BINARY_INTEGER IS /* set number */
v_member BINARY_INTEGER;
log_error('select_N_set',
'Invalid condition rule return ('||v_result||')'||Fnd_Global.Local_Chr(10)||
'rule='||p_order_by_rule);
Remove selected members from target set
*/
v_set3 := set_minus(v_set3,v_set2);
END select_N_members;
SELECT sua.unit_cd,
sua.version_number,
sua.cal_type,
sua.ci_sequence_number,
sua.uoo_id
FROM igs_en_su_attempt sua,
igs_ca_inst_rel cir,
igs_ca_type cat,
igs_ca_inst ci1,
igs_ca_inst ci2
WHERE sua.person_id = p_person_id
AND sua.course_cd = p_course_cd
AND sua.cal_type = cir.sub_cal_type
AND sua.ci_sequence_number = cir.sub_ci_sequence_number
AND ci1.cal_type = cir.sup_cal_type
AND ci1.sequence_number = cir.sup_ci_sequence_number
AND ci1.cal_type = cat.cal_type
AND cat.s_cal_cat = 'PROGRESS'
AND ci2.cal_type = p_cal_type
AND ci2.sequence_number = p_ci_sequence_number
AND ci1.start_dt <= ci2.start_dt;
SELECT unit_attempt_status,
override_achievable_cp
INTO v_unit_attempt_status,
v_override_achievable_cp
FROM igs_en_su_attempt
WHERE person_id = p_person_id
AND course_cd = p_course_cd
AND uoo_id = sua_rec.uoo_id;
SELECT cir.sub_cal_type cal_type, cir.sub_ci_sequence_number ci_sequence_number
FROM IGS_CA_INST ci ,
IGS_CA_INST_REL cir,
IGS_CA_TYPE cat,
IGS_CA_STAT cs
WHERE cir.sup_cal_type = cp_prg_cal_type
AND cir.sup_ci_sequence_number = cp_prg_sequence_number
AND ci.cal_type = cir.sub_cal_type
AND ci.sequence_number = cir.sub_ci_sequence_number
AND cat.cal_type = ci.cal_type
AND cat.s_cal_cat = 'LOAD'
AND cs.CAL_STATUS = ci.CAL_STATUS
AND cs.s_CAL_STATUS = 'ACTIVE';
SELECT pa.progression_status, pv.responsible_org_unit_cd
FROM igs_en_stdnt_ps_att pa, igs_ps_ver pv
WHERE pv.course_cd = pa.course_cd and pv.version_number = pa.version_number and pa.person_id = p_person_id and pa.course_cd = p_course_cd;
CURSOR CUR_PID IS SELECT G.GROUP_CD, PERSON_ID from IGS_PE_PRSID_GRP_MEM M, IGS_PE_PERSID_GROUP G WHERE
G.GROUP_ID = M.GROUP_ID AND SYSDATE BETWEEN M.START_DATE AND NVL(M.END_DATE, SYSDATE+1)
AND M.PERSON_ID = P_PERSON_ID ;
CURSOR USET_CUR IS SELECT UNIT_SET_CD FROM IGS_AS_SU_SETATMPT WHERE PERSON_ID = P_PERSON_ID AND COURSE_CD = P_COURSE_CD;
CURSOR CUR_GRD_RND IS select GRD_CAL_TYPE||GRD_CI_SEQUENCE_NUMBER GRAD_ROUND from IGS_GR_AWD_CRMN WHERE PERSON_ID = P_PERSON_ID ;
SELECT sua.unit_cd,
sua.version_number,
sua.cal_type,
sua.ci_sequence_number,
sua.unit_attempt_status,
uoo_id
FROM igs_en_su_attempt sua,
igs_ca_inst ci_cur,
igs_ca_inst_rel cir,
igs_ca_inst ci,
igs_ca_type ct
WHERE sua.person_id = p_person_id
AND sua.course_cd = p_course_cd
AND sua.unit_attempt_status = 'COMPLETED'
AND sua.cal_type = cir.sub_cal_type
AND sua.ci_sequence_number = cir.sub_ci_sequence_number
AND ci.cal_type = cir.sup_cal_type
AND ci.sequence_number = cir.sup_ci_sequence_number
AND ci.cal_type = ct.cal_type
AND ct.s_cal_cat = 'PROGRESS'
AND ci_cur.cal_type = p_prg_cal_type
AND ci_cur.sequence_number = p_prg_ci_sequence_number
AND ci.end_dt <= ci_cur.end_dt;
SELECT sua.unit_cd,
sua.version_number,
sua.cal_type,
sua.ci_sequence_number,
sua.unit_attempt_status,
uoo_id
FROM igs_en_su_attempt sua,
igs_ca_inst_rel cir
WHERE sua.person_id = p_person_id
AND sua.course_cd = p_course_cd
AND sua.unit_attempt_status = 'COMPLETED'
AND sua.cal_type = cir.sub_cal_type
AND sua.ci_sequence_number = cir.sub_ci_sequence_number
AND cir.sup_cal_type = p_prg_cal_type
AND cir.sup_ci_sequence_number = p_prg_ci_sequence_number;
SELECT sua.unit_cd,
sua.version_number,
sua.cal_type,
sua.ci_sequence_number,
sua.uoo_id
FROM igs_en_su_attempt sua,
igs_ca_inst ci_cur,
igs_ca_inst_rel cir,
igs_ca_inst ci,
igs_ca_type ct
WHERE sua.person_id = p_person_id
AND sua.course_cd = p_course_cd
AND sua.unit_attempt_status = 'COMPLETED'
AND sua.cal_type = cir.sub_cal_type
AND sua.ci_sequence_number = cir.sub_ci_sequence_number
AND ci.cal_type = cir.sup_cal_type
AND ci.sequence_number = cir.sup_ci_sequence_number
AND ci.cal_type = ct.cal_type
AND ct.s_cal_cat = 'PROGRESS'
AND ci_cur.cal_type = p_cal_type
AND ci_cur.sequence_number = p_ci_sequence_number
AND ci.end_dt <= ci_cur.end_dt;
SELECT sua.unit_cd,
sua.version_number,
sua.cal_type,
sua.ci_sequence_number,
sua.uoo_id
FROM igs_en_su_attempt sua,
igs_ca_inst_rel cir
WHERE sua.person_id = p_person_id
AND sua.course_cd = p_course_cd
AND sua.unit_attempt_status = 'COMPLETED'
AND sua.cal_type = cir.sub_cal_type
AND sua.ci_sequence_number = cir.sub_ci_sequence_number
AND p_cal_type = cir.sup_cal_type
AND p_ci_sequence_number = cir.sup_ci_sequence_number;
p_select_string IN VARCHAR2 )
RETURN VARCHAR2 IS
v_cursor INTEGER;
only select allowed
*/
DBMS_SQL.PARSE(v_cursor,
'SELECT'||SUBSTR(LTRIM(p_select_string),7),
dbms_sql.native);
SELECT rul_sequence_number,
item,
turin_function,
named_rule,
rule_number,
derived_rule,
set_number,
value
FROM IGS_RU_ITEM
WHERE rul_sequence_number = p_rule_number
ORDER BY item DESC )
LOOP
IF rule_items.turin_function IS NOT NULL
THEN
/*
turing functions
if then else
*/
IF rule_items.turin_function = 'ifthenelse'
THEN
v_conditional := pop;
ELSIF rule_items.turin_function = 'select'
THEN
v_set := pop;
v_result := select_set(v_set,pop);
ELSIF rule_items.turin_function = 'selectn'
THEN
v_number := pop;
v_result := select_N_members(v_number,v_set,pop);
push(sua_select_unique('last',p_tds.member_index,p_tds.set_number));
push(sua_select_unique('first',p_tds.member_index,p_tds.set_number));
push(sua_select_unique('maxcp',p_tds.member_index,p_tds.set_number));
push(sua_select_unique('mincp',p_tds.member_index,p_tds.set_number));
push(sua_select_unique('last',p_tds.member_index,
sua_duplicate_set(p_tds)));
push(sua_select_unique('first',p_tds.member_index,
sua_duplicate_set(p_tds)));
push(sua_select_unique('maxcp',p_tds.member_index,
sua_duplicate_set(p_tds)));
push(sua_select_unique('mincp',p_tds.member_index,
sua_duplicate_set(p_tds)));
select the first member from a set (returns set with one member)
*/
ELSIF rule_items.turin_function = 'first'
THEN
push(first(pop));
delete any existing messages, reset index
*/
gt_message_stack := gt_empty_message_stack;