[Home] [Help]
64: -- Populate Old Values.
65: OPEN cur_old_ref_values;
66: FETCH cur_old_ref_values INTO old_references;
67: IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT','VALIDATE_INSERT')) THEN
68: Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
69: CLOSE cur_old_ref_values;
70: App_Exception.Raise_Exception;
71: RETURN;
72: END IF;
154: OPEN c_us_valid;
155: FETCH c_us_valid INTO l_dummy;
156: IF c_us_valid%NOTFOUND THEN
157: CLOSE c_us_valid;
158: Fnd_Message.Set_Name('IGS', 'IGS_EN_UNIT_SETNOT_PERMITTED');
159: IGS_GE_MSG_STACK.ADD;
160: App_Exception.Raise_Exception;
161: END IF;
162: CLOSE c_us_valid;
176: IF IGS_EN_VAL_SUSA.enrp_val_susa_sca(
177: new_references.person_id,
178: new_references.course_cd,
179: v_message_name ) = FALSE THEN
180: Fnd_Message.Set_Name('IGS', v_message_name);
181: IGS_GE_MSG_STACK.ADD;
182: App_Exception.Raise_Exception;
183: END IF;
184: -- Validate the the IGS_PS_UNIT set is able to be created.
193: new_references.sequence_number,
194: new_references.us_version_number,
195: v_message_name,
196: 'N') = FALSE THEN
197: Fnd_Message.Set_Name('IGS', v_message_name);
198: IGS_GE_MSG_STACK.ADD;
199: App_Exception.Raise_Exception;
200: END IF;
201: END IF;
217: new_references.authorised_person_id,
218: new_references.authorised_on,
219: v_message_name,
220: 'N') = FALSE THEN
221: Fnd_Message.Set_Name('IGS', v_message_name);
222: IGS_GE_MSG_STACK.ADD;
223: App_Exception.Raise_Exception;
224: END IF;
225: END IF;
239: new_references.us_version_number,
240: new_references.authorised_person_id,
241: new_references.authorised_on,
242: v_message_name ) = FALSE THEN
243: Fnd_Message.Set_Name('IGS', v_message_name);
244: IGS_GE_MSG_STACK.ADD;
245: App_Exception.Raise_Exception;
246: END IF;
247: END IF;
262: new_references.rqrmnts_complete_ind,
263: new_references.student_confirmed_ind,
264: v_message_name,
265: 'N') = FALSE THEN
266: Fnd_Message.Set_Name('IGS', v_message_name);
267: IGS_GE_MSG_STACK.ADD;
268: App_Exception.Raise_Exception;
269: END IF;
270: END IF;
283: new_references.rqrmnts_complete_dt,
284: new_references.rqrmnts_complete_ind,
285: new_references.s_completed_source_type,
286: v_message_name ) = FALSE THEN
287: Fnd_Message.Set_Name('IGS', v_message_name);
288: IGS_GE_MSG_STACK.ADD;
289: App_Exception.Raise_Exception;
290: END IF;
291: END IF;
305: new_references.student_confirmed_ind,
306: new_references.selection_dt,
307: v_message_name,
308: 'N') = FALSE THEN
309: Fnd_Message.Set_Name('IGS', v_message_name);
310: IGS_GE_MSG_STACK.ADD;
311: App_Exception.Raise_Exception;
312: END IF;
313: END IF;
321: IF IGS_EN_VAL_SUSA.enrp_val_susa_end_vi(
322: new_references.voluntary_end_ind,
323: new_references.end_dt,
324: v_message_name ) = FALSE THEN
325: Fnd_Message.Set_Name('IGS', v_message_name);
326: IGS_GE_MSG_STACK.ADD;
327: App_Exception.Raise_Exception;
328: END IF;
329: END IF;
329: END IF;
330: -- Validate that the IGS_PS_UNIT set version number cannot be updated.
331: IF (p_updating AND
332: (new_references.us_version_number <> old_references.us_version_number)) THEN
333: Fnd_Message.Set_Name('IGS', 'IGS_EN_UNIT_SET_VERNUM_NOTUPD');
334: IGS_GE_MSG_STACK.ADD;
335: App_Exception.Raise_Exception;
336: END IF;
337: IF p_deleting THEN
345: old_references.end_dt,
346: old_references.rqrmnts_complete_ind,
347: 'Y', -- indicating this is called from db trigger.
348: v_message_name ) = FALSE THEN
349: Fnd_Message.Set_Name('IGS', v_message_name);
350: IGS_GE_MSG_STACK.ADD;
351: App_Exception.Raise_Exception;
352: END IF;
353: END IF;
380: new_references.unit_set_cd,
381: new_references.sequence_number,
382: new_references.us_version_number,
383: v_message_name) = FALSE THEN
384: Fnd_Message.Set_Name('IGS', v_message_name);
385: IGS_GE_MSG_STACK.ADD;
386: App_Exception.Raise_Exception;
387: END IF;
388: END IF;
400: new_references.parent_sequence_number,
401: cst_error,
402: v_message_name,
403: 'N') = FALSE THEN
404: Fnd_Message.Set_Name('IGS', v_message_name);
405: IGS_GE_MSG_STACK.ADD;
406: App_Exception.Raise_Exception;
407: END IF;
408: -- Validate if the parent unit set has a null end date, unit set is
418: new_references.parent_sequence_number,
419: new_references.student_confirmed_ind,
420: v_message_name,
421: 'N') = FALSE THEN
422: Fnd_Message.Set_Name('IGS', v_message_name);
423: IGS_GE_MSG_STACK.ADD;
424: App_Exception.Raise_Exception;
425: END IF;
426: END IF;
447: v_message_name,
448: 'N') = FALSE THEN
449: -- Check if warning message returned.
450: IF v_message_name <> 'IGS_EN_UNITSET_REQ_AUTHORISAT' THEN
451: Fnd_Message.Set_Name('IGS', v_message_name);
452: IGS_GE_MSG_STACK.ADD;
453: App_Exception.Raise_Exception;
454: END IF;
455: END IF;
467: new_references.voluntary_end_ind,
468: new_references.authorised_person_id,
469: new_references.authorised_on,
470: v_message_name) = FALSE THEN
471: Fnd_Message.Set_Name('IGS', v_message_name);
472: IGS_GE_MSG_STACK.ADD;
473: App_Exception.Raise_Exception;
474: END IF;
475: END IF;
495: new_references.end_dt,
496: new_references.rqrmnts_complete_ind,
497: v_message_name,
498: 'N') = FALSE THEN
499: Fnd_Message.Set_Name('IGS', v_message_name);
500: IGS_GE_MSG_STACK.ADD;
501: App_Exception.Raise_Exception;
502: END IF;
503: -- If updating and the student confirmed indicator is being unset,
511: new_references.unit_set_cd,
512: new_references.sequence_number,
513: new_references.student_confirmed_ind,
514: v_message_name) = FALSE THEN
515: Fnd_Message.Set_Name('IGS', v_message_name);
516: IGS_GE_MSG_STACK.ADD;
517: App_Exception.Raise_Exception;
518: END IF;
519: END IF;
531: new_references.us_version_number,
532: new_references.primary_set_ind,
533: v_message_name,
534: 'N') = FALSE THEN
535: Fnd_Message.Set_Name('IGS', v_message_name);
536: IGS_GE_MSG_STACK.ADD;
537: App_Exception.Raise_Exception;
538: END IF;
539: END IF;
786: old_references.course_cd,
787: old_references.unit_set_cd,
788: old_references.sequence_number,
789: v_message_name) = FALSE THEN
790: Fnd_Message.Set_Name('IGS', v_message_name);
791: IGS_GE_MSG_STACK.ADD;
792: App_Exception.Raise_Exception;
793: END IF;
794: END IF;
829: OPEN c_parent_rel_exists;
830: FETCH c_parent_rel_exists INTO l_dummy;
831: IF c_parent_rel_exists%NOTFOUND THEN
832: CLOSE c_parent_rel_exists;
833: Fnd_Message.Set_Name ('IGS', 'IGS_EN_UNIT_SET_RELATIONSHIP');
834: IGS_GE_MSG_STACK.ADD;
835: App_Exception.Raise_Exception;
836: END IF;
837: CLOSE c_parent_rel_exists;
847: IF NOT IGS_EN_STDNT_PS_ATT_PKG.Get_PK_For_Validation (
848: new_references.person_id,
849: new_references.course_cd
850: ) THEN
851: Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
852: IGS_GE_MSG_STACK.ADD;
853: App_Exception.Raise_Exception;
854: END IF;
855: END IF;
868: new_references.course_cd,
869: new_references.parent_unit_set_cd,
870: new_references.parent_sequence_number
871: )THEN
872: Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
873: IGS_GE_MSG_STACK.ADD;
874: App_Exception.Raise_Exception;
875: END IF;
876: END IF;
883: IF NOT IGS_EN_UNIT_SET_PKG.Get_PK_For_Validation (
884: new_references.unit_set_cd,
885: new_references.us_version_number
886: )THEN
887: Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
888: IGS_GE_MSG_STACK.ADD;
889: App_Exception.Raise_Exception;
890: END IF;
891: END IF;
928:
929: IF UPPER(column_name) = 'AUTHORISED_PERSON_ID' OR
930: column_name IS NULL THEN
931: IF (new_references.authorised_person_id < 0 AND new_references.authorised_person_id > 9999999999) THEN
932: Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
933: IGS_GE_MSG_STACK.ADD;
934: App_Exception.Raise_Exception;
935: END IF;
936: END IF;
937:
938: IF UPPER(column_name) = 'COURSE_CD' OR
939: column_name IS NULL THEN
940: IF new_references.course_cd <> UPPER(new_references.course_cd) THEN
941: Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
942: IGS_GE_MSG_STACK.ADD;
943: App_Exception.Raise_Exception;
944: END IF;
945: END IF;
946:
947: IF UPPER(column_name) = 'PARENT_SEQUENCE_NUMBER' OR
948: column_name IS NULL THEN
949: IF new_references.parent_sequence_number < 1 AND new_references.parent_sequence_number > 999999 THEN
950: Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
951: IGS_GE_MSG_STACK.ADD;
952: App_Exception.Raise_Exception;
953: END IF;
954: END IF;
954: END IF;
955: IF UPPER(column_name) = 'PARENT_UNIT_SET_CD' OR
956: column_name IS NULL THEN
957: IF new_references.parent_unit_set_cd <> UPPER(new_references.parent_unit_set_cd) THEN
958: Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
959: IGS_GE_MSG_STACK.ADD;
960: App_Exception.Raise_Exception;
961: END IF;
962: END IF;
962: END IF;
963: IF UPPER(column_name) = 'PRIMARY_SET_IND' OR
964: column_name IS NULL THEN
965: IF new_references.primary_set_ind <> UPPER(new_references.primary_set_ind) OR new_references.primary_set_ind NOT IN ( 'Y' , 'N' ) THEN
966: Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
967: IGS_GE_MSG_STACK.ADD;
968: App_Exception.Raise_Exception;
969: END IF;
970: END IF;
970: END IF;
971: IF UPPER(column_name) = 'RQRMNTS_COMPLETE_IND' OR
972: column_name IS NULL THEN
973: IF new_references.rqrmnts_complete_ind <> UPPER(new_references.rqrmnts_complete_ind) OR new_references.rqrmnts_complete_ind NOT IN ( 'Y' , 'N' ) THEN
974: Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
975: IGS_GE_MSG_STACK.ADD;
976: App_Exception.Raise_Exception;
977: END IF;
978: END IF;
978: END IF;
979: IF UPPER(column_name) = 'S_COMPLETED_SOURCE_TYPE' OR
980: column_name IS NULL THEN
981: IF new_references.s_completed_source_type <> UPPER(new_references.s_completed_source_type) OR (new_references.s_completed_source_type NOT IN ( 'SYSTEM' , 'MANUAL' )) THEN
982: Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
983: IGS_GE_MSG_STACK.ADD;
984: App_Exception.Raise_Exception;
985: END IF;
986: END IF;
986: END IF;
987: IF UPPER(column_name) = 'SEQUENCE_NUMBER' OR
988: column_name IS NULL THEN
989: IF new_references.sequence_number < 1 AND new_references.sequence_number > 999999 THEN
990: Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
991: IGS_GE_MSG_STACK.ADD;
992: App_Exception.Raise_Exception;
993: END IF;
994: END IF;
994: END IF;
995: IF UPPER(column_name) = 'STUDENT_CONFIRMED_IND' OR
996: column_name IS NULL THEN
997: IF new_references.student_confirmed_ind <> UPPER(new_references.student_confirmed_ind) OR new_references.student_confirmed_ind NOT IN ( 'Y' , 'N' ) THEN
998: Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
999: IGS_GE_MSG_STACK.ADD;
1000: App_Exception.Raise_Exception;
1001: END IF;
1002: END IF;
1002: END IF;
1003: IF UPPER(column_name) = 'UNIT_SET_CD' OR
1004: column_name IS NULL THEN
1005: IF new_references.unit_set_cd <> UPPER(new_references.unit_set_cd) THEN
1006: Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
1007: IGS_GE_MSG_STACK.ADD;
1008: App_Exception.Raise_Exception;
1009: END IF;
1010: END IF;
1010: END IF;
1011: IF UPPER(column_name) = 'VOLUNTARY_END_IND' OR
1012: column_name IS NULL THEN
1013: IF new_references.voluntary_end_ind <> UPPER(new_references.voluntary_end_ind) OR new_references.voluntary_end_ind NOT IN ( 'Y' , 'N' ) THEN
1014: Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
1015: IGS_GE_MSG_STACK.ADD;
1016: App_Exception.Raise_Exception;
1017: END IF;
1018: END IF;
1067: BEGIN
1068: OPEN cur_rowid;
1069: FETCH cur_rowid INTO lv_rowid;
1070: IF (cur_rowid%FOUND) THEN
1071: Fnd_Message.Set_Name ('IGS', 'IGS_AS_SUSA_SCA_FK');
1072: IGS_GE_MSG_STACK.ADD;
1073: CLOSE cur_rowid;
1074: App_Exception.Raise_Exception;
1075:
1094: BEGIN
1095: OPEN cur_rowid;
1096: FETCH cur_rowid INTO lv_rowid;
1097: IF (cur_rowid%FOUND) THEN
1098: Fnd_Message.Set_Name ('IGS', 'IGS_AS_SUSA_SUSA_PRNT_FK');
1099: IGS_GE_MSG_STACK.ADD;
1100: CLOSE cur_rowid;
1101: App_Exception.Raise_Exception;
1102:
1117: BEGIN
1118: OPEN cur_rowid;
1119: FETCH cur_rowid INTO lv_rowid;
1120: IF (cur_rowid%FOUND) THEN
1121: Fnd_Message.Set_Name ('IGS', 'IGS_AS_SUSA_US_FK');
1122: IGS_GE_MSG_STACK.ADD;
1123: CLOSE cur_rowid;
1124: App_Exception.Raise_Exception;
1125:
1146: BEGIN
1147: OPEN cur_rowid;
1148: FETCH cur_rowid INTO lv_rowid;
1149: IF (cur_rowid%FOUND) THEN
1150: Fnd_Message.Set_Name ('IGS', 'IGS_AS_SUSA_CI_FK');
1151: Igs_Ge_Msg_Stack.ADD;
1152: CLOSE cur_rowid;
1153: App_Exception.Raise_Exception;
1154: RETURN;
1191: Open cur_rowid;
1192: Fetch cur_rowid INTO lv_rowid;
1193: IF (cur_rowid%FOUND) THEN
1194: Close cur_rowid;
1195: Fnd_Message.Set_Name ('IGS', 'IGS_AS_SUSA_US_FK');
1196: IGS_GE_MSG_STACK.ADD;
1197: App_Exception.Raise_Exception;
1198: Return;
1199: END IF;
1311: new_references.course_cd,
1312: new_references.unit_set_cd,
1313: new_references.sequence_number
1314: ) THEN
1315: Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
1316: IGS_GE_MSG_STACK.ADD;
1317: App_Exception.Raise_Exception;
1318: END IF;
1319: Check_Constraints;
1333: new_references.course_cd,
1334: new_references.unit_set_cd,
1335: new_references.sequence_number
1336: ) THEN
1337: Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
1338: IGS_GE_MSG_STACK.ADD;
1339: App_Exception.Raise_Exception;
1340: END IF;
1341: Check_Constraints;
1456: ELSE
1457: X_PROGRAM_UPDATE_DATE := SYSDATE;
1458: END IF;
1459: ELSE
1460: FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
1461: IGS_GE_MSG_STACK.ADD;
1462: app_exception.raise_exception;
1463: END IF;
1464: --
1638: --
1639: EXCEPTION
1640: WHEN OTHERS THEN
1641: IF (SQLCODE IN (-28115, -28113, -28111)) THEN
1642: fnd_message.set_name ('IGS', 'IGS_SC_POLICY_EXCEPTION');
1643: fnd_message.set_token ('ERR_CD', SQLCODE);
1644: igs_ge_msg_stack.add;
1645: igs_sc_gen_001.unset_ctx('R');
1646: app_exception.raise_exception;
1639: EXCEPTION
1640: WHEN OTHERS THEN
1641: IF (SQLCODE IN (-28115, -28113, -28111)) THEN
1642: fnd_message.set_name ('IGS', 'IGS_SC_POLICY_EXCEPTION');
1643: fnd_message.set_token ('ERR_CD', SQLCODE);
1644: igs_ge_msg_stack.add;
1645: igs_sc_gen_001.unset_ctx('R');
1646: app_exception.raise_exception;
1647: ELSE
1738: BEGIN
1739: OPEN c1;
1740: FETCH c1 INTO tlinfo;
1741: IF (c1%NOTFOUND) THEN
1742: fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
1743: IGS_GE_MSG_STACK.ADD;
1744: CLOSE c1;
1745: app_exception.raise_exception;
1746:
1850: AND (X_ATTRIBUTE20 IS NULL)))
1851: ) THEN
1852: NULL;
1853: ELSE
1854: fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
1855: IGS_GE_MSG_STACK.ADD;
1856: app_exception.raise_exception;
1857: END IF;
1858: RETURN;
1923: IF X_LAST_UPDATE_LOGIN IS NULL THEN
1924: X_LAST_UPDATE_LOGIN := -1;
1925: END IF;
1926: ELSE
1927: FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
1928: IGS_GE_MSG_STACK.ADD;
1929: app_exception.raise_exception;
1930: END IF;
1931: Before_DML(
2042: ATTRIBUTE19=NEW_REFERENCES.ATTRIBUTE19,
2043: ATTRIBUTE20=NEW_REFERENCES.ATTRIBUTE20
2044: WHERE ROWID = X_ROWID;
2045: IF (SQL%NOTFOUND) THEN
2046: fnd_message.set_name ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
2047: igs_ge_msg_stack.add;
2048: igs_sc_gen_001.unset_ctx('R');
2049: app_exception.raise_exception;
2050: END IF;
2060: --
2061: EXCEPTION
2062: WHEN OTHERS THEN
2063: IF (SQLCODE = (-28115)) THEN
2064: fnd_message.set_name ('IGS', 'IGS_SC_UPD_POLICY_EXCP');
2065: fnd_message.set_token ('ERR_CD', SQLCODE);
2066: igs_ge_msg_stack.add;
2067: igs_sc_gen_001.unset_ctx('R');
2068: app_exception.raise_exception;
2061: EXCEPTION
2062: WHEN OTHERS THEN
2063: IF (SQLCODE = (-28115)) THEN
2064: fnd_message.set_name ('IGS', 'IGS_SC_UPD_POLICY_EXCP');
2065: fnd_message.set_token ('ERR_CD', SQLCODE);
2066: igs_ge_msg_stack.add;
2067: igs_sc_gen_001.unset_ctx('R');
2068: app_exception.raise_exception;
2069: ELSE
2235: END IF;
2236: DELETE FROM IGS_AS_SU_SETATMPT
2237: WHERE ROWID = X_ROWID;
2238: IF (SQL%NOTFOUND) THEN
2239: fnd_message.set_name ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
2240: igs_ge_msg_stack.add;
2241: igs_sc_gen_001.unset_ctx('R');
2242: app_exception.raise_exception;
2243: END IF;