[Home] [Help]
1: PACKAGE BODY Igs_En_Val_Susa AS
2: /* $Header: IGSEN69B.pls 120.3 2006/05/03 23:59:19 smaddali noship $ */
3:
4: -------------------------------------------------------------------------------------------
5: --Change History:
4: -------------------------------------------------------------------------------------------
5: --Change History:
6: --Who When What
7: --smadathi 28-AUG-2001 Bug No. 1956374 .The function genp_val_staff_prsn removed
8: -- Also the call to function igs_en_val_susa.genp_val_staff_prsn is
9: -- is replaced by igs_ad_val_acai.genp_val_staff_prsn
10: --smadathi 24-AUG-2001 Bug No. 1956374 .The call to igs_en_val_susa.genp_val_sdtt_sess
11: -- is changed to igs_as_val_suaap.genp_val_sdtt_sess.Also
12: -- Function genp_val_sdtt_sess Removed
6: --Who When What
7: --smadathi 28-AUG-2001 Bug No. 1956374 .The function genp_val_staff_prsn removed
8: -- Also the call to function igs_en_val_susa.genp_val_staff_prsn is
9: -- is replaced by igs_ad_val_acai.genp_val_staff_prsn
10: --smadathi 24-AUG-2001 Bug No. 1956374 .The call to igs_en_val_susa.genp_val_sdtt_sess
11: -- is changed to igs_as_val_suaap.genp_val_sdtt_sess.Also
12: -- Function genp_val_sdtt_sess Removed
13: --msrinivi 27-Aug-2001 Function genp_val_prsn_id removed
14: --prraj 15-Nov-2002 Added p_legacy parameter to functions enrp_val_susa_ins, enrp_val_susa_auth,
126: END;
127: EXCEPTION
128: WHEN OTHERS THEN
129: Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
130: FND_MESSAGE.SET_TOKEN('NAME','IGS_EN_VAL_SUSA.enrp_val_susa_auth');
131: IGS_GE_MSG_STACK.ADD;
132: App_Exception.Raise_Exception;
133:
134: END enrp_val_susa_auth;
207: END;
208: EXCEPTION
209: WHEN OTHERS THEN
210: Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
211: FND_MESSAGE.SET_TOKEN('NAME','IGS_EN_VAL_SUSA.enrp_val_susa_cmplt');
212: IGS_GE_MSG_STACK.ADD;
213: App_Exception.Raise_Exception;
214:
215: END enrp_val_susa_cmplt;
397: END;
398: EXCEPTION
399: WHEN OTHERS THEN
400: Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
401: FND_MESSAGE.SET_TOKEN('NAME','IGS_EN_VAL_SUSA.enrp_val_susa_cousr');
402: IGS_GE_MSG_STACK.ADD;
403: App_Exception.Raise_Exception;
404:
405: END enrp_val_susa_cousr;
492: END;
493: EXCEPTION
494: WHEN OTHERS THEN
495: Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
496: FND_MESSAGE.SET_TOKEN('NAME','IGS_EN_VAL_SUSA.enrp_val_susa_del');
497: IGS_GE_MSG_STACK.ADD;
498: App_Exception.Raise_Exception;
499:
500:
573:
574: EXCEPTION
575: WHEN OTHERS THEN
576: Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
577: FND_MESSAGE.SET_TOKEN('NAME','IGS_EN_VAL_SUSA.enrp_val_susa_dts');
578: IGS_GE_MSG_STACK.ADD;
579: App_Exception.Raise_Exception;
580:
581: END enrp_val_susa_dts;
742: END;
743: EXCEPTION
744: WHEN OTHERS THEN
745: Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
746: FND_MESSAGE.SET_TOKEN('NAME','IGS_EN_VAL_SUSA.enrp_val_susa_end_dt');
747: IGS_GE_MSG_STACK.ADD;
748: App_Exception.Raise_Exception;
749:
750: END enrp_val_susa_end_dt;
777: END;
778: EXCEPTION
779: WHEN OTHERS THEN
780: Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
781: FND_MESSAGE.SET_TOKEN('NAME','IGS_EN_VAL_SUSA.enrp_val_susa_end_vi');
782: IGS_GE_MSG_STACK.ADD;
783: App_Exception.Raise_Exception;
784: END enrp_val_susa_end_vi;
785: --
924: END;
925: EXCEPTION
926: WHEN OTHERS THEN
927: Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
928: FND_MESSAGE.SET_TOKEN('NAME','IGS_EN_VAL_SUSA.enrp_val_susa_ins');
929: IGS_GE_MSG_STACK.ADD;
930: App_Exception.Raise_Exception;
931: END enrp_val_susa_ins;
932:
1128: END;
1129: EXCEPTION
1130: WHEN OTHERS THEN
1131: Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
1132: FND_MESSAGE.SET_TOKEN('NAME','IGS_EN_VAL_SUSA.enrp_val_susa_parent');
1133: IGS_GE_MSG_STACK.ADD;
1134: App_Exception.Raise_Exception;
1135:
1136: END enrp_val_susa_parent;
1231: END;
1232: EXCEPTION
1233: WHEN OTHERS THEN
1234: Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
1235: FND_MESSAGE.SET_TOKEN('NAME','IGS_EN_VAL_SUSA.enrp_val_susa_prmry');
1236: IGS_GE_MSG_STACK.ADD;
1237: App_Exception.Raise_Exception;
1238:
1239: END enrp_val_susa_prmry;
1285: END;
1286: EXCEPTION
1287: WHEN OTHERS THEN
1288: Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
1289: FND_MESSAGE.SET_TOKEN('NAME','IGS_EN_VAL_SUSA.enrp_val_susa_sca');
1290: IGS_GE_MSG_STACK.ADD;
1291:
1292: App_Exception.Raise_Exception;
1293: END enrp_val_susa_sca;
1407: END IF;
1408: CLOSE c_susa;
1409:
1410: -- Check that the relationship is still valid within the course offering
1411: IF (Igs_En_Val_Susa.enrp_val_susa_cousr(
1412: p_person_id,
1413: p_course_cd,
1414: p_unit_set_cd,
1415: p_us_version_number,
1430: -- done when operating in legacy mode
1431: IF (p_legacy <> 'Y') THEN
1432:
1433: -- Check that the unit set is still active
1434: IF (Igs_En_Val_Susa.enrp_val_susa_us_act(
1435: p_person_id,
1436: p_course_cd,
1437: p_unit_set_cd,
1438: p_sequence_number,
1502: END;
1503: EXCEPTION
1504: WHEN OTHERS THEN
1505: Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
1506: FND_MESSAGE.SET_TOKEN('NAME','IGS_EN_VAL_SUSA.enrp_val_susa_sci');
1507: IGS_GE_MSG_STACK.ADD;
1508: App_Exception.Raise_Exception;
1509:
1510: END enrp_val_susa_sci;
1577: END;
1578: EXCEPTION
1579: WHEN OTHERS THEN
1580: Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
1581: FND_MESSAGE.SET_TOKEN('NAME','IGS_EN_VAL_SUSA.enrp_val_susa_sci_rl');
1582: IGS_GE_MSG_STACK.ADD;
1583: App_Exception.Raise_Exception;
1584:
1585: END enrp_val_susa_sci_rl;
1639: END;
1640: EXCEPTION
1641: WHEN OTHERS THEN
1642: Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
1643: FND_MESSAGE.SET_TOKEN('NAME','IGS_EN_VAL_SUSA.enrp_val_susa_sci_sd');
1644: IGS_GE_MSG_STACK.ADD;
1645: App_Exception.Raise_Exception;
1646:
1647: END enrp_val_susa_sci_sd;
1719: END;
1720: EXCEPTION
1721: WHEN OTHERS THEN
1722: Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
1723: FND_MESSAGE.SET_TOKEN('NAME','IGS_EN_VAL_SUSA.enrp_val_susa_us_act');
1724: IGS_GE_MSG_STACK.ADD;
1725: App_Exception.Raise_Exception;
1726: END enrp_val_susa_us_act;
1727: --
1773: END;
1774: EXCEPTION
1775: WHEN OTHERS THEN
1776: Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
1777: FND_MESSAGE.SET_TOKEN('NAME','IGS_EN_VAL_SUSA.enrp_val_susa_us_ath');
1778: IGS_GE_MSG_STACK.ADD;
1779:
1780: App_Exception.Raise_Exception;
1781: END enrp_val_susa_us_ath;
1855: END IF;
1856: IF v_inserting THEN
1857: -- Validate the the unit set is able to be created.
1858: -- against the student course attempt.
1859: IF Igs_En_Val_Susa.enrp_val_susa_sca(
1860: p_person_id,
1861: p_course_cd,
1862: v_message_name) = FALSE THEN
1863: p_message_name := v_message_name;
1866: -- Validate the the unit set is able to be created.
1867: -- The student cannot have completed it previously,
1868: -- no encumbrances must exist and it must be applicable
1869: -- to the course offering.
1870: IF Igs_En_Val_Susa.enrp_val_susa_ins(
1871: p_person_id,
1872: p_course_cd,
1873: p_unit_set_cd,
1874: p_sequence_number,
1887: NVL(v_old_susa_rec.authorised_person_id, 0)) OR
1888: (NVL(p_authorised_on,IGS_GE_DATE.IGSDATE('1900/01/01'))
1889: <> NVL(v_old_susa_rec.authorised_on,
1890: IGS_GE_DATE.IGSDATE('1900/01/01'))))) THEN
1891: IF Igs_En_Val_Susa.enrp_val_susa_auth(
1892: p_unit_set_cd,
1893: p_us_version_number,
1894: p_end_dt,
1895: p_authorised_person_id,
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,
1917: p_us_version_number,
1918: p_authorised_person_id,
1919:
1932: <> NVL(v_old_susa_rec.rqrmnts_complete_ind, 'x')) OR
1933: (NVL(p_rqrmnts_complete_dt,IGS_GE_DATE.IGSDATE('1900/01/01'))
1934: <> NVL(v_old_susa_rec.rqrmnts_complete_dt,
1935: IGS_GE_DATE.IGSDATE('1900/01/01')))))THEN
1936: IF Igs_En_Val_Susa.enrp_val_susa_cmplt(
1937: p_rqrmnts_complete_dt,
1938: p_rqrmnts_complete_ind,
1939: p_student_confirmed_ind,
1940: v_message_name,
1954: <> NVL(v_old_susa_rec.s_completed_source_type, 'x')) OR
1955: (NVL(p_rqrmnts_complete_dt,IGS_GE_DATE.IGSDATE('1900/01/01'))
1956: <> NVL(v_old_susa_rec.rqrmnts_complete_dt,
1957: IGS_GE_DATE.IGSDATE('1900/01/01')))))THEN
1958: IF Igs_En_Val_Susa.enrp_val_susa_scst(
1959: p_rqrmnts_complete_dt,
1960: p_rqrmnts_complete_ind,
1961: p_s_completed_source_type,
1962: v_message_name) = FALSE THEN
1975: <> NVL(v_old_susa_rec.end_dt, IGS_GE_DATE.IGSDATE('1900/01/01'))) OR
1976: (NVL(p_rqrmnts_complete_dt,IGS_GE_DATE.IGSDATE('1900/01/01'))
1977: <> NVL(v_old_susa_rec.rqrmnts_complete_dt,
1978: IGS_GE_DATE.IGSDATE('1900/01/01'))))) THEN
1979: IF Igs_En_Val_Susa.enrp_val_susa_dts(
1980: p_selection_dt,
1981: p_end_dt,
1982: p_rqrmnts_complete_dt,
1983: v_message_name) = FALSE THEN
1993: ((p_student_confirmed_ind <> v_old_susa_rec.student_confirmed_ind) OR
1994: (NVL(p_selection_dt,IGS_GE_DATE.IGSDATE('1900/01/01'))
1995: <> NVL(v_old_susa_rec.selection_dt,
1996: IGS_GE_DATE.IGSDATE('1900/01/01'))))) THEN
1997: IF Igs_En_Val_Susa.enrp_val_susa_sci_sd(
1998: p_student_confirmed_ind,
1999: p_selection_dt,
2000: v_message_name,
2001: 'N') = FALSE THEN
2009: (v_updating AND
2010: ((p_voluntary_end_ind <> v_old_susa_rec.voluntary_end_ind) OR
2011: (NVL(p_end_dt,IGS_GE_DATE.IGSDATE('1900/01/01'))
2012: <> NVL(v_old_susa_rec.end_dt, IGS_GE_DATE.IGSDATE('1900/01/01'))))) THEN
2013: IF Igs_En_Val_Susa.enrp_val_susa_end_vi(
2014: p_voluntary_end_ind,
2015: p_end_dt,
2016: v_message_name) = FALSE THEN
2017:
2026: RETURN FALSE;
2027: END IF;
2028: IF v_deleting THEN
2029: -- Validate that the records can be deleted.
2030: IF Igs_En_Val_Susa.enrp_val_susa_del(
2031: p_person_id,
2032: p_course_cd,
2033: p_unit_set_cd,
2034: p_sequence_number,
2046: -- Validate the the unit set is able to be created
2047: -- with the unit set status being valid and the
2048: -- expiry date not set. If set then person must have
2049: -- previously selected it.
2050: IF Igs_En_Val_Susa.enrp_val_susa_us_act(
2051: p_person_id,
2052: p_course_cd,
2053: p_unit_set_cd,
2054: p_sequence_number,
2067: (NVL(p_parent_sequence_number, 0)
2068: <> NVL(v_old_susa_rec.parent_sequence_number, 0)))) THEN
2069: -- Validate if the unit set is to be defined as a subordinate or if
2070: -- relationship specified, that it is valid within the course offering.
2071: IF Igs_En_Val_Susa.enrp_val_susa_cousr(
2072: p_person_id,
2073: p_course_cd,
2074: p_unit_set_cd,
2075: p_us_version_number,
2084: -- Validate if the parent unit set has a null end date, unit set is
2085: -- not being linked to itself (directly or indirectly). Cannot be
2086: -- confirmed if parent is unconfirmed.
2087:
2088: IF Igs_En_Val_Susa.enrp_val_susa_parent(
2089: p_person_id,
2090: p_course_cd,
2091: p_unit_set_cd,
2092: p_sequence_number,
2106: -- need to be set or if more than one open end dated instance
2107: -- of the unit set exists. Also cannot be cleared if parent ended.
2108: -- If part of the admissions offer, authorisation required to end
2109: -- the unit set.
2110: IF Igs_En_Val_Susa.enrp_val_susa_end_dt(
2111: p_person_id,
2112: p_course_cd,
2113: p_unit_set_cd,
2114: p_sequence_number,
2133: IF v_updating AND
2134:
2135: p_end_dt IS NOT NULL THEN
2136:
2137: IF Igs_En_Val_Susa.enrp_val_susa_ed_upd(
2138: p_person_id,
2139: p_course_cd,
2140: p_unit_set_cd,
2141: p_sequence_number,
2157: -- Also check that not unset one end date or complete date set. Cannot be
2158:
2159: -- confirmed and linked to a parent that is unconfirmed. Cannot be
2160: -- confirmed if encumbrances exist.
2161: IF Igs_En_Val_Susa.enrp_val_susa_sci(
2162: p_person_id,
2163: p_course_cd,
2164: p_unit_set_cd,
2165: p_sequence_number,
2182:
2183: ELSE
2184: v_old_student_confirmed_ind := v_old_susa_rec.student_confirmed_ind;
2185: END IF;
2186: IF Igs_En_Val_Susa.enrp_val_susa_sci_rl(
2187: p_person_id,
2188: p_course_cd,
2189: p_unit_set_cd,
2190: p_us_version_number,
2201: -- creation).
2202: IF v_updating AND
2203: p_student_confirmed_ind = 'N' THEN
2204:
2205: IF Igs_En_Val_Susa.enrp_val_susa_sci_up(
2206: p_person_id,
2207: p_course_cd,
2208: p_unit_set_cd,
2209: p_sequence_number,
2220: (p_primary_set_ind <> v_old_susa_rec.primary_set_ind)) THEN
2221: -- Validate the primary set indicator is only set for
2222: -- non-administrative sets and that there does not already
2223: -- exist a unit set that has a higher rank.
2224: IF Igs_En_Val_Susa.enrp_val_susa_prmry(
2225: p_person_id,
2226: p_course_cd,
2227:
2228: p_unit_set_cd,
2239: END;
2240: EXCEPTION
2241: WHEN OTHERS THEN
2242: Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
2243: FND_MESSAGE.SET_TOKEN('NAME','Igs_En_Val_Susa.enrp_val_susa');
2244: IGS_GE_MSG_STACK.ADD;
2245: App_Exception.Raise_Exception;
2246:
2247: END enrp_val_susa;
2348: v_authorised_person_id := p_authorised_person_id;
2349: v_authorised_on := p_authorised_on;
2350: END IF;
2351: -- Validate that able to update the record.
2352: IF Igs_En_Val_Susa.enrp_val_susa(
2353: p_person_id,
2354: p_course_cd,
2355: v_unit_set_cd,
2356: v_sequence_number,
2408: END;
2409: EXCEPTION
2410: WHEN OTHERS THEN
2411: Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
2412: FND_MESSAGE.SET_TOKEN('NAME','Igs_En_Val_Susa.enrp_val_susa_ed_upd');
2413: IGS_GE_MSG_STACK.ADD;
2414: App_Exception.Raise_Exception;
2415: END enrp_val_susa_ed_upd;
2416: --
2483: FOR v_susa_rec IN c_susa LOOP
2484: v_susa_rec_found := TRUE;
2485: IF v_susa_rec.student_confirmed_ind = 'Y' THEN
2486: -- Validate that able to update the record.
2487: IF Igs_En_Val_Susa.enrp_val_susa(
2488: p_person_id,
2489: p_course_cd,
2490: v_susa_rec.unit_set_cd,
2491: v_susa_rec.sequence_number,
2529: END;
2530: EXCEPTION
2531: WHEN NO_DATA_FOUND THEN
2532: Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
2533: FND_MESSAGE.SET_TOKEN('NAME','Igs_En_Val_Susa.enrp_val_susa_sci_up');
2534: IGS_GE_MSG_STACK.ADD;
2535: App_Exception.Raise_Exception;
2536: END enrp_val_susa_sci_up;
2537: --
2564:
2565: EXCEPTION
2566: WHEN OTHERS THEN
2567: Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
2568: FND_MESSAGE.SET_TOKEN('NAME','Igs_En_Val_Susa.enrp_val_susa_scst');
2569: IGS_GE_MSG_STACK.ADD;
2570: App_Exception.Raise_Exception;
2571: END enrp_val_susa_scst;
2572: --
2572: --
2573: -- Validate a person id.
2574: --
2575:
2576: END Igs_En_Val_Susa;