[Home] [Help]
147: --- Validate the input parameters.
148: IF p_person_id IS NULL OR p_effective_dt IS NULL THEN
149: RETURN TRUE;
150: END IF;
151: --- The requirement of the IGS_PS_COURSE code parameter (p_course_cd) is
152: --- dependant on whether or not the system encumbrance effect should be applied
153: --- to a IGS_PS_COURSE (s_encmb_effect_type.apply_to_course_ind).
154: --- IGS_GE_NOTE: v_validate_sus_srvc and v_validate_rvk_srvc should be
155: --- defaulted to True when defined.
149: RETURN TRUE;
150: END IF;
151: --- The requirement of the IGS_PS_COURSE code parameter (p_course_cd) is
152: --- dependant on whether or not the system encumbrance effect should be applied
153: --- to a IGS_PS_COURSE (s_encmb_effect_type.apply_to_course_ind).
154: --- IGS_GE_NOTE: v_validate_sus_srvc and v_validate_rvk_srvc should be
155: --- defaulted to True when defined.
156: --- Check if a IGS_PS_COURSE code is required for the Suspended Services effect type.
157: OPEN c_chk_crs_for_srv_type( cst_suspend_service );
152: --- dependant on whether or not the system encumbrance effect should be applied
153: --- to a IGS_PS_COURSE (s_encmb_effect_type.apply_to_course_ind).
154: --- IGS_GE_NOTE: v_validate_sus_srvc and v_validate_rvk_srvc should be
155: --- defaulted to True when defined.
156: --- Check if a IGS_PS_COURSE code is required for the Suspended Services effect type.
157: OPEN c_chk_crs_for_srv_type( cst_suspend_service );
158: FETCH c_chk_crs_for_srv_type INTO v_sus_apply_to_course_ind;
159: IF c_chk_crs_for_srv_type%NOTFOUND THEN
160: CLOSE c_chk_crs_for_srv_type;
164: IF v_sus_apply_to_course_ind = 'Y' AND p_course_cd IS NULL THEN
165: v_validate_sus_srvc := FALSE;
166: END IF;
167: END IF;
168: --- Check if a IGS_PS_COURSE code is required for the Revoked Services effect type.
169: OPEN c_chk_crs_for_srv_type( cst_revoke_service );
170: FETCH c_chk_crs_for_srv_type INTO v_rvk_apply_to_course_ind;
171: IF c_chk_crs_for_srv_type%NOTFOUND THEN
172: CLOSE c_chk_crs_for_srv_type;
181:
182: IF v_validate_sus_srvc = TRUE THEN
183:
184: IF v_sus_apply_to_course_ind = 'N' THEN
185: --- Check when the Suspended Services effect type is not applied to a IGS_PS_COURSE.
186: FOR v_pee_dates IN c_get_pee_dates( cst_suspend_service ) LOOP
187: --- Validate if dates of a returned record overlap with the effective date.
188: IF v_pee_dates.expiry_dt IS NULL THEN
189: IF v_pee_dates.pee_start_dt <= p_effective_dt THEN
198: END IF;
199: END IF;
200: END LOOP;
201: ELSE
202: --- Check when the Suspended Services effect type is applied to a IGS_PS_COURSE.
203: FOR v_pee_dates IN c_get_pee_dates_for_crs( cst_suspend_service ) LOOP
204: --- Validate if dates of a returned record overlap with the effective date.
205: IF v_pee_dates.expiry_dt IS NULL THEN
206: IF v_pee_dates.pee_start_dt <= p_effective_dt THEN
221:
222: IF v_validate_rvk_srvc = TRUE THEN
223:
224: IF v_rvk_apply_to_course_ind = 'N' THEN
225: --- Check when the Revoked Services effect type is not applied to a IGS_PS_COURSE.
226: FOR v_pee_dates IN c_get_pee_dates( cst_revoke_service ) LOOP
227: --- Validate if dates of a returned record overlap with the effective date.
228: IF v_pee_dates.expiry_dt IS NULL THEN
229: IF v_pee_dates.pee_start_dt <= p_effective_dt THEN
241: END IF;
242: END IF;
243: END LOOP;
244: ELSE
245: --- Check when the Revoked Services effect type is applied to a IGS_PS_COURSE.
246: FOR v_pee_dates IN c_get_pee_dates_for_crs( cst_revoke_service ) LOOP
247: --- Validate if dates of a returned record overlap with the effective date.
248: IF v_pee_dates.expiry_dt IS NULL THEN
249: IF v_pee_dates.pee_start_dt <= p_effective_dt THEN
276: App_Exception.Raise_Exception;
277: */
278: END enrp_val_excld_prsn;
279: --
280: -- Validate whether a IGS_PE_PERSON is excluded from a IGS_PS_COURSE.
281: FUNCTION enrp_val_excld_crs(
282: p_person_id IN NUMBER ,
283: p_course_cd IN VARCHAR2 ,
284: p_effective_dt IN DATE ,
286: RETURN BOOLEAN AS
287:
288: BEGIN -- enrp_val_excld_crs
289: -- Validate whether or not a IGS_PE_PERSON is excluded from admission or enrolment
290: -- in a specific IGS_PS_COURSE.
291: DECLARE
292: cst_excluded CONSTANT VARCHAR2(10) := 'EXC_COURSE';
293: cst_excluded_grp CONSTANT VARCHAR2(10) := 'EXC_CRS_GP';
294: cst_suspended CONSTANT VARCHAR2(10) := 'SUS_COURSE';
329: v_message_name) THEN
330: p_message_name := v_message_name;
331: RETURN FALSE;
332: END IF;
333: -- Validate for an exclusion from a specific IGS_PS_COURSE.
334: FOR v_pce_rec IN c_pce LOOP
335: -- Validate if the dates of a returned record
336: -- overlap with the effective date.
337: IF v_pce_rec.expiry_dt IS NULL THEN
347: END IF;
348: END IF;
349: END LOOP;
350: -- Validate for an exclusion from
351: -- a IGS_PS_COURSE within a IGS_PS_COURSE group.
352: FOR v_pcge_rec IN c_pcge LOOP
353: -- Validate if the dates of a returned
354: -- record overlap with the effective date.
355: IF v_pcge_rec.expiry_dt IS NULL THEN
526: pur.pee_start_dt = pee.pee_start_dt AND
527: pur.pee_sequence_number = pee.sequence_number;
528: -- Cursor on IGS_EN_SU_ATTEMPT (sua).
529: -- Cursor validates which units a student is enrolled in,
530: -- for a particular IGS_PS_COURSE.
531: CURSOR c_sua (
532: cp_unit_cd IGS_PE_UNT_REQUIRMNT.unit_cd%TYPE,
533: cp_p_person_id IGS_PE_PERSON.person_id%TYPE,
534: cp_p_course_cd IGS_PS_COURSE.course_cd%TYPE) IS
530: -- for a particular IGS_PS_COURSE.
531: CURSOR c_sua (
532: cp_unit_cd IGS_PE_UNT_REQUIRMNT.unit_cd%TYPE,
533: cp_p_person_id IGS_PE_PERSON.person_id%TYPE,
534: cp_p_course_cd IGS_PS_COURSE.course_cd%TYPE) IS
535: SELECT sua.ci_start_dt,
536: sua.ci_end_dt,
537: sua.unit_attempt_status
538: FROM IGS_EN_SU_ATTEMPT sua
772: -- validating the input parameters
773: IF (p_person_id IS NULL OR p_effective_dt IS NULL) THEN
774: RETURN TRUE;
775: END IF;
776: -- check if a IGS_PS_COURSE code is required for the
777: -- Restricted Attendance Type effect type
778: OPEN c_course_ind;
779: FETCH c_course_ind INTO v_apply_to_course_ind;
780: -- check if a record was found
817: END IF;
818: END LOOP;
819: ELSE -- v_apply_to_course_ind = 'Y'
820: -- check whether the restricted type effect
821: -- is applied to a IGS_PS_COURSE
822: FOR c_pee_rec IN c_pee_details_2 LOOP
823: IF (c_pee_rec.expiry_dt IS NULL) THEN
824: IF (c_pee_rec.pee_start_dt <= p_effective_dt) THEN
825: p_restricted_attendance_type :=
922: IF (p_person_id IS NULL OR p_effective_dt IS NULL) THEN
923: p_message_name := v_message_name;
924: RETURN TRUE;
925: END IF;
926: -- The requirement of the IGS_PS_COURSE code parameter (p_course_cd) is dependant on
927: -- whether or not the system encumbrance effect should be applied to a IGS_PS_COURSE.
928: -- (s_encmb_effect_type.apply_to_course_ind).
929: -- IGS_GE_NOTE: v_validate_rstr_ge_cp and v_validate_rstr_le_cp should be defaulted
930: -- to TRUE when defined.
923: p_message_name := v_message_name;
924: RETURN TRUE;
925: END IF;
926: -- The requirement of the IGS_PS_COURSE code parameter (p_course_cd) is dependant on
927: -- whether or not the system encumbrance effect should be applied to a IGS_PS_COURSE.
928: -- (s_encmb_effect_type.apply_to_course_ind).
929: -- IGS_GE_NOTE: v_validate_rstr_ge_cp and v_validate_rstr_le_cp should be defaulted
930: -- to TRUE when defined.
931: -- Check if a IGS_PS_COURSE code is required for the
927: -- whether or not the system encumbrance effect should be applied to a IGS_PS_COURSE.
928: -- (s_encmb_effect_type.apply_to_course_ind).
929: -- IGS_GE_NOTE: v_validate_rstr_ge_cp and v_validate_rstr_le_cp should be defaulted
930: -- to TRUE when defined.
931: -- Check if a IGS_PS_COURSE code is required for the
932: -- Restricted Greater Than Credit Point effect type
933: OPEN c_ge_course_ind;
934: FETCH c_ge_course_ind INTO v_ge_apply_to_course_ind;
935: -- check if a record was found
941: IF (v_ge_apply_to_course_ind = 'Y' AND p_course_cd IS NULL) THEN
942: v_validate_rstr_ge_cp := FALSE;
943: END IF;
944: END IF;
945: -- check if a IGS_PS_COURSE code is required for the
946: -- Restricted Less Than Credit Point effect type
947: OPEN c_le_course_ind;
948: FETCH c_le_course_ind INTO v_le_apply_to_course_ind;
949: -- check if a record was found
987: END IF;
988: END LOOP;
989: ELSE -- v_ge_apply_to_course_ind = 'Y'
990: -- check whether the restricted type effect
991: -- is applied to a IGS_PS_COURSE
992: FOR c_pee_rec IN c_pee_details_2 LOOP
993: IF (c_pee_rec.expiry_dt IS NULL) THEN
994: IF (c_pee_rec.pee_start_dt <= p_effective_dt) THEN
995: p_rstrct_ge_cp_value := c_pee_rec.restricted_enrolment_cp;
1031: END IF;
1032: END LOOP;
1033: ELSE -- v_le_apply_to_course_ind = 'Y'
1034: -- check whether the restricted type effect
1035: -- is applied to a IGS_PS_COURSE
1036: FOR c_pee_rec IN c_pee_details_4 LOOP
1037: IF (c_pee_rec.expiry_dt IS NULL) THEN
1038: IF (c_pee_rec.pee_start_dt <= p_effective_dt) THEN
1039: p_rstrct_le_cp_value :=
1072:
1073: END;
1074: END enrp_val_rstrct_cp;
1075: --
1076: -- Validate whether a IGS_PE_PERSON has an encumbrance blocking IGS_PS_COURSE material.
1077: FUNCTION enrp_val_blk_crsmtrl(
1078: p_person_id IN NUMBER ,
1079: p_course_cd VARCHAR2 ,
1080: p_effective_dt IN DATE ,
1105: course_cd = p_course_cd;
1106: BEGIN
1107: -- This module validates whether or not
1108: -- a IGS_PE_PERSON has an encumbracne blocking the
1109: -- issue of IGS_PS_COURSE materials
1110: p_message_name := null;
1111: -- validating the input parameters
1112: IF (p_person_id IS NULL OR p_effective_dt IS NULL) THEN
1113: RETURN TRUE;
1111: -- validating the input parameters
1112: IF (p_person_id IS NULL OR p_effective_dt IS NULL) THEN
1113: RETURN TRUE;
1114: END IF;
1115: -- check if a IGS_PS_COURSE code is required for the
1116: -- Materials Blocked effect type
1117: OPEN c_course_ind;
1118: FETCH c_course_ind INTO v_apply_to_course_ind;
1119: -- check if a record was found
1134: p_message_name) = FALSE) THEN
1135: RETURN FALSE;
1136: END IF;
1137: -- validate for an encumbrance which blocks the issue
1138: -- of IGS_PS_COURSE materials
1139: IF (v_apply_to_course_ind = 'N') THEN
1140: -- check when the IGS_PS_COURSE Materials Blocked effect
1141: -- type is not appplied to a IGS_PS_COURSE
1142: FOR c_pee_rec IN c_pee_details_1 LOOP
1136: END IF;
1137: -- validate for an encumbrance which blocks the issue
1138: -- of IGS_PS_COURSE materials
1139: IF (v_apply_to_course_ind = 'N') THEN
1140: -- check when the IGS_PS_COURSE Materials Blocked effect
1141: -- type is not appplied to a IGS_PS_COURSE
1142: FOR c_pee_rec IN c_pee_details_1 LOOP
1143: IF (c_pee_rec.expiry_dt IS NULL) THEN
1144: IF (c_pee_rec.pee_start_dt <= p_effective_dt) THEN
1137: -- validate for an encumbrance which blocks the issue
1138: -- of IGS_PS_COURSE materials
1139: IF (v_apply_to_course_ind = 'N') THEN
1140: -- check when the IGS_PS_COURSE Materials Blocked effect
1141: -- type is not appplied to a IGS_PS_COURSE
1142: FOR c_pee_rec IN c_pee_details_1 LOOP
1143: IF (c_pee_rec.expiry_dt IS NULL) THEN
1144: IF (c_pee_rec.pee_start_dt <= p_effective_dt) THEN
1145: p_message_name := 'IGS_EN_ISSUE_OF_COURS_MATEER';
1153: END IF;
1154: END IF;
1155: END LOOP;
1156: ELSE -- v_apply_to_course_ind = 'Y'
1157: -- check when the IGS_PS_COURSE Materials Blocked effect
1158: -- type is appplied to a IGS_PS_COURSE
1159: FOR c_pee_rec IN c_pee_details_2 LOOP
1160: IF (c_pee_rec.expiry_dt IS NULL) THEN
1161: IF (c_pee_rec.pee_start_dt <= p_effective_dt) THEN
1154: END IF;
1155: END LOOP;
1156: ELSE -- v_apply_to_course_ind = 'Y'
1157: -- check when the IGS_PS_COURSE Materials Blocked effect
1158: -- type is appplied to a IGS_PS_COURSE
1159: FOR c_pee_rec IN c_pee_details_2 LOOP
1160: IF (c_pee_rec.expiry_dt IS NULL) THEN
1161: IF (c_pee_rec.pee_start_dt <= p_effective_dt) THEN
1162: p_message_name := 'IGS_EN_ISSUE_OF_COURS_MATEER';
1221: -- validating the input parameters
1222: IF (p_person_id IS NULL OR p_effective_dt IS NULL) THEN
1223: RETURN TRUE;
1224: END IF;
1225: -- check if a IGS_PS_COURSE code is required for the
1226: -- Graduation Blocked effect type
1227: OPEN c_course_ind;
1228: FETCH c_course_ind INTO v_apply_to_course_ind;
1229: -- check if a record was found
1247: -- validate for an encumbrance which blocks
1248: -- graduation
1249: IF (v_apply_to_course_ind = 'N') THEN
1250: -- check when the Graduation Blocked effect
1251: -- type is not applied to a IGS_PS_COURSE
1252: FOR c_pee_rec IN c_pee_details_1 LOOP
1253: IF (c_pee_rec.expiry_dt IS NULL) THEN
1254: IF (c_pee_rec.pee_start_dt <= p_effective_dt) THEN
1255: p_message_name := 'IGS_EN_PRSN_ENCUMB_GRADUATION';
1264: END IF;
1265: END LOOP;
1266: ELSE -- v_apply_to_course_ind = 'Y'
1267: -- check when the Graduation Blocked effect
1268: -- type is applied to a IGS_PS_COURSE
1269: FOR c_pee_rec IN c_pee_details_2 LOOP
1270: IF (c_pee_rec.expiry_dt IS NULL) THEN
1271: IF (c_pee_rec.pee_start_dt <= p_effective_dt) THEN
1272: p_message_name := 'IGS_EN_PRSN_ENCUMB_GRADUATION';
1331: -- validating the input parameters
1332: IF (p_person_id IS NULL OR p_effective_dt IS NULL) THEN
1333: RETURN TRUE;
1334: END IF;
1335: -- check if a IGS_PS_COURSE code is required for the
1336: -- ID Card Blocked effect type
1337: OPEN c_course_ind;
1338: FETCH c_course_ind INTO v_apply_to_course_ind;
1339: -- check if a record was found
1357: -- validate for an encumbrance which blocks the issue
1358: -- of an ID card
1359: IF (v_apply_to_course_ind = 'N') THEN
1360: -- check when the ID Card Blocked effect
1361: -- type is not applied to a IGS_PS_COURSE
1362: FOR c_pee_rec IN c_pee_details_1 LOOP
1363: IF (c_pee_rec.expiry_dt IS NULL) THEN
1364: IF (c_pee_rec.pee_start_dt <= p_effective_dt) THEN
1365: p_message_name := 'IGS_EN_PRSN_ENCUMB_ISS_IDCARD';
1374: END IF;
1375: END LOOP;
1376: ELSE -- v_apply_to_course_ind = 'Y'
1377: -- check when the ID Card Blocked effect
1378: -- type is applied to a IGS_PS_COURSE
1379: FOR c_pee_rec IN c_pee_details_2 LOOP
1380: IF (c_pee_rec.expiry_dt IS NULL) THEN
1381: IF (c_pee_rec.pee_start_dt <= p_effective_dt) THEN
1382: p_message_name := 'IGS_EN_PRSN_ENCUMB_ISS_IDCARD';
1442: -- validating the input parameters
1443: IF (p_person_id IS NULL OR p_effective_dt IS NULL) THEN
1444: RETURN TRUE;
1445: END IF;
1446: -- check if a IGS_PS_COURSE code is required for the
1447: -- Information Booth Blocked effect type
1448: OPEN c_course_ind;
1449: FETCH c_course_ind INTO v_apply_to_course_ind;
1450: -- check if a record was found
1468: -- validate for an encumbrance which blocks the
1469: -- secure services of the Information Booth
1470: IF (v_apply_to_course_ind = 'N') THEN
1471: -- check when the Information Booth Blocked effect
1472: -- type is not applied to a IGS_PS_COURSE
1473: FOR c_pee_rec IN c_pee_details_1 LOOP
1474: IF (c_pee_rec.expiry_dt IS NULL) THEN
1475: IF (c_pee_rec.pee_start_dt <= p_effective_dt) THEN
1476: p_message_name := 'IGS_EN_SECURE_SERV_BLOCKING';
1485: END IF;
1486: END LOOP;
1487: ELSE -- v_apply_to_course_ind = 'Y'
1488: -- check when the Information Booth Blocked effect
1489: -- type is applied to a IGS_PS_COURSE
1490: FOR c_pee_rec IN c_pee_details_2 LOOP
1491: IF (c_pee_rec.expiry_dt IS NULL) THEN
1492: IF (c_pee_rec.pee_start_dt <= p_effective_dt) THEN
1493: p_message_name := 'IGS_EN_SECURE_SERV_BLOCKING';
1553: -- validating the input parameters
1554: IF (p_person_id IS NULL OR p_effective_dt IS NULL) THEN
1555: RETURN TRUE;
1556: END IF;
1557: -- check if a IGS_PS_COURSE code is required for the
1558: -- Result Blocked effect type
1559: OPEN c_course_ind;
1560: FETCH c_course_ind INTO v_apply_to_course_ind;
1561: -- check if a record was found
1579: -- validate for an encumbrance which blocks the issue
1580: -- of results
1581: IF (v_apply_to_course_ind = 'N') THEN
1582: -- check when the Results Blocked effect
1583: -- type is not applied to a IGS_PS_COURSE
1584: FOR c_pee_rec IN c_pee_details_1 LOOP
1585: IF (c_pee_rec.expiry_dt IS NULL) THEN
1586: IF (c_pee_rec.pee_start_dt <= p_effective_dt) THEN
1587: p_message_name := 'IGS_EN_PRSN_ENCUMB_ISS_RESULT';
1596: END IF;
1597: END LOOP;
1598: ELSE -- v_apply_to_course_ind = 'Y'
1599: -- check when the Results Blocked effect
1600: -- type is applied to a IGS_PS_COURSE
1601: FOR c_pee_rec IN c_pee_details_2 LOOP
1602: IF (c_pee_rec.expiry_dt IS NULL) THEN
1603: IF (c_pee_rec.pee_start_dt <= p_effective_dt) THEN
1604: p_message_name := 'IGS_EN_PRSN_ENCUMB_ISS_RESULT';
1662: -- validating the input parameters
1663: IF (p_person_id IS NULL OR p_effective_dt IS NULL) THEN
1664: RETURN TRUE;
1665: END IF;
1666: -- check if a IGS_PS_COURSE code is required for the
1667: -- System Correspondence Blocked effect type
1668: OPEN c_course_ind;
1669: FETCH c_course_ind INTO v_apply_to_course_ind;
1670: -- check if a record was found
1688: -- validate for an encumbrance which blocks the issue
1689: -- of system generated correspondence
1690: IF (v_apply_to_course_ind = 'N') THEN
1691: -- check when the System Correspondence Blocked effect
1692: -- type is not applied to a IGS_PS_COURSE
1693: FOR c_pee_rec IN c_pee_details_1 LOOP
1694: IF (c_pee_rec.expiry_dt IS NULL) THEN
1695: IF (c_pee_rec.pee_start_dt <= p_effective_dt) THEN
1696: p_message_name := 'IGS_EN_PERS_BLK_ISSUE_OF_CORR';
1705: END IF;
1706: END LOOP;
1707: ELSE -- v_apply_to_course_ind = 'Y'
1708: -- check when the System Correspondence Blocked effect
1709: -- type is applied to a IGS_PS_COURSE
1710: FOR c_pee_rec IN c_pee_details_2 LOOP
1711: IF (c_pee_rec.expiry_dt IS NULL) THEN
1712: IF (c_pee_rec.pee_start_dt <= p_effective_dt) THEN
1713: p_message_name := 'IGS_EN_PERS_BLK_ISSUE_OF_CORR';
1772: -- validating the input parameters
1773: IF (p_person_id IS NULL OR p_effective_dt IS NULL) THEN
1774: RETURN TRUE;
1775: END IF;
1776: -- check if a IGS_PS_COURSE code is required for the
1777: -- Transcript Blocked effect type
1778: OPEN c_course_ind;
1779: FETCH c_course_ind INTO v_apply_to_course_ind;
1780: -- check if a record was found
1798: -- validate for an encumbrance which blocks the issue
1799: -- of the academic transcript
1800: IF (v_apply_to_course_ind = 'N') THEN
1801: -- check when the Transcript Blocked effect
1802: -- type is not applied to a IGS_PS_COURSE
1803: FOR c_pee_rec IN c_pee_details_1 LOOP
1804: IF (c_pee_rec.expiry_dt IS NULL) THEN
1805: IF (c_pee_rec.pee_start_dt <= p_effective_dt) THEN
1806: p_message_name := 'IGS_EN_PRSN_ENCUMB_ISS_ACATRN';
1815: END IF;
1816: END LOOP;
1817: ELSE -- v_apply_to_course_ind = 'Y'
1818: -- check whether the restricted type effect
1819: -- is applied to a IGS_PS_COURSE
1820: FOR c_pee_rec IN c_pee_details_2 LOOP
1821: IF (c_pee_rec.expiry_dt IS NULL) THEN
1822: IF (c_pee_rec.pee_start_dt <= p_effective_dt) THEN
1823: p_message_name := 'IGS_EN_PRSN_ENCUMB_ISS_ACATRN';
1937: RETURN TRUE;
1938: END IF;
1939:
1940: -- call the routine to check whether the student
1941: -- IGS_PS_COURSE attempt has a credit point restriction
1942: IF(IGS_EN_VAL_ENCMB.ENRP_VAL_RSTRCT_CP(
1943: p_person_id,
1944: p_course_cd,
1945: l_effective_dt,
1979: END IF;
1980: END IF;
1981: END IF; --NVL(v_period_cp, 0)
1982:
1983: -- call the routine to check whether the student IGS_PS_COURSE
1984: -- attempt has a restricted attendance type
1985: ELSIF(IGS_EN_VAL_ENCMB.ENRP_VAL_RSTRCT_ATYP(
1986: p_person_id,
1987: p_course_cd,