[Home] [Help]
40: -- - If the authorised_person_id is set, then the authorised_on must also be
41:
42: -- set and visa versa.
43: -- - authorised_person_id and authorised_on fields can only be set if the
44: -- end_dt is also set or the IGS_EN_UNIT_SET.authorisation_ind = 'Y'.
45: -- Validate that the person is a staff member.
46: DECLARE
47: v_authorisation_rqrd_ind IGS_EN_UNIT_SET.authorisation_rqrd_ind%TYPE;
48: CURSOR c_us IS
43: -- - authorised_person_id and authorised_on fields can only be set if the
44: -- end_dt is also set or the IGS_EN_UNIT_SET.authorisation_ind = 'Y'.
45: -- Validate that the person is a staff member.
46: DECLARE
47: v_authorisation_rqrd_ind IGS_EN_UNIT_SET.authorisation_rqrd_ind%TYPE;
48: CURSOR c_us IS
49: SELECT us.authorisation_rqrd_ind
50: FROM IGS_EN_UNIT_SET us
51: WHERE us.unit_set_cd = p_unit_set_cd AND
46: DECLARE
47: v_authorisation_rqrd_ind IGS_EN_UNIT_SET.authorisation_rqrd_ind%TYPE;
48: CURSOR c_us IS
49: SELECT us.authorisation_rqrd_ind
50: FROM IGS_EN_UNIT_SET us
51: WHERE us.unit_set_cd = p_unit_set_cd AND
52: us.version_number = p_us_version_number;
53: BEGIN
54: p_message_name := NULL;
83:
84: END IF;
85:
86: -- authorised_person_id and authorised_on fields can only be set if the
87: -- end_dt is also set or the IGS_EN_UNIT_SET.authorisation_ind = 'Y'.
88: IF (p_authorised_person_id IS NOT NULL AND
89: p_end_dt IS NULL) THEN
90: OPEN c_us;
91: FETCH c_us INTO v_authorisation_rqrd_ind;
228: p_legacy IN VARCHAR2)
229: RETURN BOOLEAN AS
230: gv_other_detail VARCHAR2(255);
231: BEGIN -- enrp_val_susa_cousr
232: -- Validates that the IGS_EN_UNIT_SET being allocated to the IGS_AS_SU_SETATMPT
233: -- is:
234:
235: -- * If the unit set is a subordinate within the IGS_PS_OF_UNT_SET_RL
236: -- table, then it must be specified as a child of one of the superior
303: OPEN c_susa;
304:
305: FETCH c_susa INTO v_susa_us_version_no;
306: IF (c_susa%NOTFOUND) THEN
307: p_message_name := 'IGS_EN_UNIT_SET_NOT_PARENT_EX';
308:
309: IF (p_legacy = 'Y') THEN
310: -- Add excep to stack
311: FND_MESSAGE.Set_Name('IGS',p_message_name);
322: v_cal_type,
323: v_susa_us_version_no);
324: FETCH c_cousr INTO v_check;
325: IF (c_cousr%NOTFOUND) THEN
326: p_message_name := 'IGS_EN_UNIT_SET_RELATIONSHIP';
327:
328: IF (p_legacy = 'Y') THEN
329: -- Add excep to stack
330: FND_MESSAGE.Set_Name('IGS',p_message_name);
337: CLOSE c_cousr;
338:
339: ELSE
340: -- (p_parent_unit_set_cd IS NULL OR p_parent_sequence_number IS NULL)
341: -- Validate that if the IGS_EN_UNIT_SET is defined as a subordinate within
342: -- the IGS_PS_OF_UNT_SET_RL table, then the parent details must be
343:
344: -- specified.
345: OPEN c_cousr2(
361: FND_MSG_PUB.Add;
362: END IF;
363: ELSE
364: -- Return the error message.
365: p_message_name := 'IGS_EN_UNIT_SET_PARENT_UNITSE';
366:
367: IF (p_legacy = 'Y') THEN
368: -- Add excep to stack
369: FND_MESSAGE.Set_Name('IGS',p_message_name);
463: -- Check if validation called from the database trigger. If yes, then
464: -- do not execute this query as will cause mutating trigger. The error
465: -- will be trapped by the RI constraints anyway.
466: IF p_db_trg_call = 'N' THEN
467: -- Cannot be deleted if it is the parent of another IGS_EN_UNIT_SET.
468: OPEN c_susa; -- Althogh handled by RI constraints, required for enrp_val_susa.
469: FETCH c_susa INTO v_dummy;
470: IF (c_susa%FOUND) THEN
471: CLOSE c_susa;
652: -- open end date irrespective of version number.
653: OPEN c_susa;
654: FETCH c_susa INTO v_dummy;
655: IF c_susa%FOUND THEN
656: p_message_name := 'IGS_EN_UNIT_SET_EXISTS';
657:
658: IF (p_legacy = 'Y') THEN
659: -- Add excep to stack
660: FND_MESSAGE.Set_Name('IGS',p_message_name);
671: p_parent_sequence_number IS NOT NULL THEN
672: OPEN c_susa_parent;
673: FETCH c_susa_parent INTO v_dummy;
674: IF c_susa_parent%FOUND THEN
675: p_message_name := 'IGS_EN_UNIT_SET_NO_OPEN';
676:
677: IF (p_legacy = 'Y') THEN
678: -- Add excep to stack
679: FND_MESSAGE.Set_Name('IGS',p_message_name);
813:
814: CURSOR c_uso IS
815: SELECT 'X'
816: FROM IGS_PS_OFR_UNIT_SET cous,
817: igs_en_unit_set_stat uss1,
818: IGS_EN_UNIT_SET_ALL US,
819: IGS_EN_STDNT_PS_ATT spa
820: WHERE spa.person_id = p_person_id
821: AND spa.course_cd = p_course_cd
814: CURSOR c_uso IS
815: SELECT 'X'
816: FROM IGS_PS_OFR_UNIT_SET cous,
817: igs_en_unit_set_stat uss1,
818: IGS_EN_UNIT_SET_ALL US,
819: IGS_EN_STDNT_PS_ATT spa
820: WHERE spa.person_id = p_person_id
821: AND spa.course_cd = p_course_cd
822: AND us.version_number = p_us_version_number
836:
837: CURSOR c_usoo IS
838: SELECT 'X'
839: FROM IGS_PS_OF_OPT_UNT_ST coous,
840: IGS_EN_UNIT_SET_ALL US,
841: igs_en_unit_set_stat uss1,
842: IGS_EN_STDNT_PS_ATT spa
843: WHERE spa.person_id = p_person_id
844: AND spa.course_cd = p_course_cd
837: CURSOR c_usoo IS
838: SELECT 'X'
839: FROM IGS_PS_OF_OPT_UNT_ST coous,
840: IGS_EN_UNIT_SET_ALL US,
841: igs_en_unit_set_stat uss1,
842: IGS_EN_STDNT_PS_ATT spa
843: WHERE spa.person_id = p_person_id
844: AND spa.course_cd = p_course_cd
845: AND coous.coo_id = spa.coo_id
874: OPEN c_uso;
875: FETCH c_uso INTO v_dummy;
876: CLOSE c_uso;
877: IF v_dummy IS NULL THEN -- c_uso is not found
878: p_message_name := 'IGS_EN_UNIT_SETNOT_PERMITTED';
879: IF (p_legacy = 'Y') THEN
880: -- Add excep to stack
881: FND_MESSAGE.Set_Name('IGS',p_message_name);
882: FND_MSG_PUB.Add;
945: p_legacy IN VARCHAR2)
946: RETURN BOOLEAN AS
947:
948: BEGIN -- enrp_val_susa_parent
949: -- This module validates the IGS_EN_UNIT_SET being allocated to the
950: -- IGS_AS_SU_SETATMPT as a parent:
951: -- - Cannot be a parent unit set attempt of itself.
952: -- - Can only be linked to a IGS_AS_SU_SETATMPT record for
953: -- the same person and course where the end date is null.
1004:
1005: -- Validate the unit set is not being specified as a
1006: -- parent of itself irrespective of version.
1007: IF (p_unit_set_cd = p_parent_unit_set_cd) THEN
1008: p_message_name := 'IGS_EN_UNIT_SET_NOTBE_PARENT';
1009:
1010: IF (p_legacy = 'Y') THEN
1011: -- Add excep to stack
1012: FND_MESSAGE.Set_Name('IGS',p_message_name);
1021: OPEN c_susa;
1022: FETCH c_susa INTO v_end_dt, v_student_confirmed_ind;
1023: IF (c_susa%NOTFOUND) THEN
1024: CLOSE c_susa;
1025: p_message_name := 'IGS_EN_UNIT_SET_NOT_PARENT_EX';
1026:
1027: IF (p_legacy = 'Y') THEN
1028: -- Add excep to stack
1029: FND_MESSAGE.Set_Name('IGS',p_message_name);
1034: ELSE
1035: CLOSE c_susa;
1036: -- Check if the end date is set
1037: IF (v_end_dt IS NOT NULL) THEN
1038: p_message_name := 'IGS_EN_UNIT_SET_NOT_ENDDT';
1039:
1040: IF (p_legacy = 'Y') THEN
1041: -- Add excep to stack
1042: FND_MESSAGE.Set_Name('IGS',p_message_name);
1049: -- Validate that if the unit set is confirmed,
1050: -- then the parent must also be confirmed.
1051: IF (v_student_confirmed_ind = 'N' AND -- parent student confirmed ind
1052: p_student_confirmed_ind = 'Y') THEN -- child student confirmed ind
1053: p_message_name := 'IGS_EN_UNIT_SET_PARENTSET_CON';
1054:
1055: IF (p_legacy = 'Y') THEN
1056: -- Add excep to stack
1057: FND_MESSAGE.Set_Name('IGS',p_message_name);
1153: -- unit set. Also, check that the primary indicator is only set for
1154: -- non-administrative files.
1155: DECLARE
1156:
1157: v_rank IGS_EN_UNIT_SET_CAT.rank%TYPE;
1158: v_administrative_ind IGS_EN_UNIT_SET.administrative_ind%TYPE;
1159: v_dummy VARCHAR2(1);
1160:
1161: CURSOR c_us_usc IS
1154: -- non-administrative files.
1155: DECLARE
1156:
1157: v_rank IGS_EN_UNIT_SET_CAT.rank%TYPE;
1158: v_administrative_ind IGS_EN_UNIT_SET.administrative_ind%TYPE;
1159: v_dummy VARCHAR2(1);
1160:
1161: CURSOR c_us_usc IS
1162: SELECT us.administrative_ind,
1160:
1161: CURSOR c_us_usc IS
1162: SELECT us.administrative_ind,
1163: usc.rank
1164: FROM IGS_EN_UNIT_SET us,
1165: IGS_EN_UNIT_SET_CAT usc
1166: WHERE us.unit_set_cd = p_unit_set_cd AND
1167: us.version_number = p_us_version_number AND
1168: us.unit_set_cat = usc.unit_set_cat;
1161: CURSOR c_us_usc IS
1162: SELECT us.administrative_ind,
1163: usc.rank
1164: FROM IGS_EN_UNIT_SET us,
1165: IGS_EN_UNIT_SET_CAT usc
1166: WHERE us.unit_set_cd = p_unit_set_cd AND
1167: us.version_number = p_us_version_number AND
1168: us.unit_set_cat = usc.unit_set_cat;
1169: CURSOR c_susa_us_usc (
1166: WHERE us.unit_set_cd = p_unit_set_cd AND
1167: us.version_number = p_us_version_number AND
1168: us.unit_set_cat = usc.unit_set_cat;
1169: CURSOR c_susa_us_usc (
1170: cp_rank IGS_EN_UNIT_SET_CAT.rank%TYPE) IS
1171: SELECT 'x'
1172: FROM IGS_AS_SU_SETATMPT susa,
1173: IGS_EN_UNIT_SET us,
1174: IGS_EN_UNIT_SET_CAT usc
1169: CURSOR c_susa_us_usc (
1170: cp_rank IGS_EN_UNIT_SET_CAT.rank%TYPE) IS
1171: SELECT 'x'
1172: FROM IGS_AS_SU_SETATMPT susa,
1173: IGS_EN_UNIT_SET us,
1174: IGS_EN_UNIT_SET_CAT usc
1175: WHERE susa.person_id = p_person_id AND
1176: susa.course_cd = p_course_cd AND
1177: susa.unit_set_cd = us.unit_set_cd AND
1170: cp_rank IGS_EN_UNIT_SET_CAT.rank%TYPE) IS
1171: SELECT 'x'
1172: FROM IGS_AS_SU_SETATMPT susa,
1173: IGS_EN_UNIT_SET us,
1174: IGS_EN_UNIT_SET_CAT usc
1175: WHERE susa.person_id = p_person_id AND
1176: susa.course_cd = p_course_cd AND
1177: susa.unit_set_cd = us.unit_set_cd AND
1178: susa.us_version_number = us.version_number AND
1311: p_legacy IN VARCHAR2)
1312: RETURN BOOLEAN AS
1313:
1314: BEGIN -- enrp_val_susa_sci
1315: -- This module validates the confirmation of a IGS_EN_UNIT_SET for a
1316: -- IGS_AS_SU_SETATMPT record. The validations are:
1317: -- - The student confirmed indicator cannot be unset once the
1318:
1319: -- end date has been set.
1379: OPEN c_sca;
1380: FETCH c_sca INTO v_sca_version_number;
1381: IF (c_sca%NOTFOUND) THEN
1382: CLOSE c_sca;
1383: p_message_name := 'IGS_EN_UNIT_SET_SPA_ENR_INACT';
1384: RETURN FALSE;
1385: END IF;
1386: CLOSE c_sca;
1387: END IF;
1393: p_parent_sequence_number IS NOT NULL) THEN
1394: OPEN c_susa;
1395: FETCH c_susa INTO v_dummy;
1396: IF (c_susa%FOUND) THEN
1397: p_message_name := 'IGS_EN_UNIT_SET_PARENTSET_CON';
1398:
1399: IF (p_legacy = 'Y') THEN
1400: -- Add excep to stack
1401: FND_MESSAGE.Set_Name('IGS',p_message_name);
1461: ELSE
1462: -- Validate the confirmed indicator being unset
1463: -- Cannot be unset if the end date has been set
1464: IF (p_end_dt IS NOT NULL) THEN
1465: p_message_name := 'IGS_EN_UNIT_SET_UNCONF_ENDDT';
1466:
1467: IF (p_legacy = 'Y') THEN
1468: -- Add excep to stack
1469: FND_MESSAGE.Set_Name('IGS',p_message_name);
1474: END IF;
1475:
1476: -- Cannot be unset if the requirements complete indicator has been set
1477: IF (p_rqrmnts_complete_ind = 'Y') THEN
1478: p_message_name := 'IGS_EN_UNIT_SET_UNCONF_REQ';
1479:
1480: IF (p_legacy = 'Y') THEN
1481: -- Add excep to stack
1482: FND_MESSAGE.Set_Name('IGS',p_message_name);
1548: OPEN c_sca;
1549: FETCH c_sca INTO v_sca_version_number;
1550: IF (c_sca%NOTFOUND) THEN
1551: CLOSE c_sca;
1552: p_message_name := 'IGS_EN_UNIT_SET_SPA_ENR_INACT';
1553: RETURN FALSE;
1554: END IF;
1555: CLOSE c_sca;
1556: -- Validate unit set rules for enrolment
1607: -- Validate the selection date must be set if the confirmed
1608: -- indicator is set and visa versa.
1609: IF (p_student_confirmed_ind = 'Y' AND
1610: p_selection_dt IS NULL) THEN
1611: p_message_name := 'IGS_EN_UNIT_SET_UNCONF_SETDT';
1612:
1613: IF (p_legacy = 'Y') THEN
1614: -- Add excep to stack
1615: FND_MESSAGE.Set_Name('IGS',p_message_name);
1622: -- Validate the selection date must be unset if the
1623: -- confirmed indicator is set and visa versa.
1624: IF (p_student_confirmed_ind = 'N' AND
1625: p_selection_dt IS NOT NULL) THEN
1626: p_message_name := 'IGS_EN_UNIT_SET_UNCONF_NOTSET';
1627:
1628: IF (p_legacy = 'Y') THEN
1629: -- Add excep to stack
1630: FND_MESSAGE.Set_Name('IGS',p_message_name);
1659: RETURN BOOLEAN AS
1660:
1661:
1662: BEGIN -- enrp_val_susa_us_act
1663: -- This module validates that the IGS_EN_UNIT_SET being allocated to the
1664: -- IGS_AS_SU_SETATMPT is active with a null expiry date or active with
1665: -- expiry date set and the student has previously selected in within the same
1666: -- course attempt.
1667: DECLARE
1665: -- expiry date set and the student has previously selected in within the same
1666: -- course attempt.
1667: DECLARE
1668: cst_active CONSTANT VARCHAR2(6) := 'ACTIVE';
1669: v_s_unit_set_status IGS_EN_UNIT_SET_STAT.s_unit_set_status%TYPE;
1670: v_expiry_dt IGS_EN_UNIT_SET.expiry_dt%TYPE;
1671: v_dummy VARCHAR2(1);
1672: CURSOR c_us_uss IS
1673: SELECT uss.s_unit_set_status,
1666: -- course attempt.
1667: DECLARE
1668: cst_active CONSTANT VARCHAR2(6) := 'ACTIVE';
1669: v_s_unit_set_status IGS_EN_UNIT_SET_STAT.s_unit_set_status%TYPE;
1670: v_expiry_dt IGS_EN_UNIT_SET.expiry_dt%TYPE;
1671: v_dummy VARCHAR2(1);
1672: CURSOR c_us_uss IS
1673: SELECT uss.s_unit_set_status,
1674: us.expiry_dt
1671: v_dummy VARCHAR2(1);
1672: CURSOR c_us_uss IS
1673: SELECT uss.s_unit_set_status,
1674: us.expiry_dt
1675: FROM IGS_EN_UNIT_SET us,
1676: IGS_EN_UNIT_SET_STAT uss
1677: WHERE us.unit_set_status = uss.unit_set_status AND
1678: us.unit_set_cd = p_unit_set_cd AND
1679: us.version_number = p_version_number;
1672: CURSOR c_us_uss IS
1673: SELECT uss.s_unit_set_status,
1674: us.expiry_dt
1675: FROM IGS_EN_UNIT_SET us,
1676: IGS_EN_UNIT_SET_STAT uss
1677: WHERE us.unit_set_status = uss.unit_set_status AND
1678: us.unit_set_cd = p_unit_set_cd AND
1679: us.version_number = p_version_number;
1680: CURSOR c_susa IS
1692: FETCH c_us_uss INTO v_s_unit_set_status,
1693: v_expiry_dt;
1694: IF v_s_unit_set_status <> cst_active THEN
1695: CLOSE c_us_uss;
1696: p_message_name := 'IGS_EN_UNIT_SETST_ACTIVE';
1697: RETURN FALSE;
1698: ELSE
1699: IF v_expiry_dt IS NOT NULL THEN
1700: -- Determine if the student has previously had the version selected
1706: FETCH c_susa INTO v_dummy;
1707: IF (c_susa%NOTFOUND) THEN
1708:
1709: CLOSE c_susa;
1710: p_message_name := 'IGS_EN_UNIT_SET_EXPDT_NOTSET';
1711: RETURN FALSE;
1712: END IF;
1713: CLOSE c_susa;
1714: END IF;
1734: p_message_name OUT NOCOPY VARCHAR2 )
1735: RETURN BOOLEAN AS
1736:
1737: BEGIN -- enrp_val_susa_us_ath
1738: -- This module validates that the IGS_EN_UNIT_SET being allocated to the
1739: -- IGS_AS_SU_SETATMPT requires authorisation.
1740: DECLARE
1741: v_auth_rqrd_ind IGS_EN_UNIT_SET.authorisation_rqrd_ind%TYPE;
1742: CURSOR c_us IS
1737: BEGIN -- enrp_val_susa_us_ath
1738: -- This module validates that the IGS_EN_UNIT_SET being allocated to the
1739: -- IGS_AS_SU_SETATMPT requires authorisation.
1740: DECLARE
1741: v_auth_rqrd_ind IGS_EN_UNIT_SET.authorisation_rqrd_ind%TYPE;
1742: CURSOR c_us IS
1743: SELECT us.authorisation_rqrd_ind
1744: FROM IGS_EN_UNIT_SET us
1745: WHERE unit_set_cd = p_unit_set_cd AND
1740: DECLARE
1741: v_auth_rqrd_ind IGS_EN_UNIT_SET.authorisation_rqrd_ind%TYPE;
1742: CURSOR c_us IS
1743: SELECT us.authorisation_rqrd_ind
1744: FROM IGS_EN_UNIT_SET us
1745: WHERE unit_set_cd = p_unit_set_cd AND
1746: version_number = p_version_number;
1747: BEGIN
1748: -- Set the default message number
1757: IF (v_auth_rqrd_ind = 'Y') AND
1758: (p_authorised_person_id IS NULL OR
1759: p_authorised_on IS NULL) THEN
1760: CLOSE c_us;
1761: p_message_name := 'IGS_EN_UNIT_SET_REQ_AUTHORISA';
1762: RETURN FALSE;
1763: END IF;
1764: END IF;
1765: CLOSE c_us;
1879: RETURN FALSE;
1880: END IF;
1881: END IF;
1882: -- Validate that the authorisation fields can only be set when end date is set
1883: -- or the unit set cd requires authorisation (IGS_EN_UNIT_SET.authorisation_ind = 'Y')
1884: IF v_inserting OR
1885: (v_updating AND
1886: ((NVL(p_authorised_person_id, 0) <>
1887: NVL(v_old_susa_rec.authorised_person_id, 0)) OR
1908: (NVL(p_authorised_on,IGS_GE_DATE.IGSDATE('1900/01/01'))
1909: <> NVL(v_old_susa_rec.authorised_on,
1910: IGS_GE_DATE.IGSDATE('1900/01/01'))))) THEN
1911: -- Validate that the authorisation fields must be set when
1912: -- the unit set cd requires authorisation (IGS_EN_UNIT_SET.authorisation_ind = 'Y')
1913: -- Check required only when the unit set is confirmed.
1914: IF (p_student_confirmed_ind = 'Y') THEN
1915: IF Igs_En_Val_Susa.enrp_val_susa_us_ath(
1916: p_unit_set_cd,
2021: END IF;
2022: -- Validate that the unit set version number cannot be updated.
2023: IF (v_updating AND
2024: (p_us_version_number <> v_old_susa_rec.us_version_number)) THEN
2025: p_message_name := 'IGS_EN_UNIT_SET_VERNUM_NOTUPD';
2026: RETURN FALSE;
2027: END IF;
2028: IF v_deleting THEN
2029: -- Validate that the records can be deleted.