[Home] [Help]
81: Open cur_old_ref_values;
82: Fetch cur_old_ref_values INTO old_references;
83: IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT','VALIDATE_INSERT')) THEN
84: Close cur_old_ref_values;
85: Fnd_Message.Set_Name ('IGS', 'FORM_RECORD_DELETED');
86: IGS_GE_MSG_STACK.ADD;
87: App_Exception.Raise_Exception;
88: Return;
89: END IF;
183: IF Upper(Column_Name) = 'SCORE_REPORT_DATE' OR
184: Column_Name IS NULL THEN
185: IF TRUNC(new_references.score_report_date) > TRUNC(sysdate) OR
186: TRUNC(new_references.score_report_date) < TRUNC(new_references.test_date) THEN
187: FND_MESSAGE.SET_NAME('IGS','IGS_GE_INVALID_VALUE_WITH_CTXT');
188: FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_SCORE_RPT_DATE'));
189: IGS_GE_MSG_STACK.ADD;
190: App_Exception.Raise_Exception;
191: END IF;
184: Column_Name IS NULL THEN
185: IF TRUNC(new_references.score_report_date) > TRUNC(sysdate) OR
186: TRUNC(new_references.score_report_date) < TRUNC(new_references.test_date) THEN
187: FND_MESSAGE.SET_NAME('IGS','IGS_GE_INVALID_VALUE_WITH_CTXT');
188: FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_SCORE_RPT_DATE'));
189: IGS_GE_MSG_STACK.ADD;
190: App_Exception.Raise_Exception;
191: END IF;
192: END IF;
194: -- The following code checks for check constraints on the Columns.
195: IF Upper(Column_Name) = 'NON_STANDARD_ADMIN' OR
196: Column_Name IS NULL THEN
197: IF NOT (new_references.non_standard_admin in ('Y','N')) THEN
198: FND_MESSAGE.SET_NAME('IGS','IGS_GE_INVALID_VALUE_WITH_CTXT');
199: FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_NON_STANDARD_ADMIN'));
200: IGS_GE_MSG_STACK.ADD;
201: App_Exception.Raise_Exception;
202: END IF;
195: IF Upper(Column_Name) = 'NON_STANDARD_ADMIN' OR
196: Column_Name IS NULL THEN
197: IF NOT (new_references.non_standard_admin in ('Y','N')) THEN
198: FND_MESSAGE.SET_NAME('IGS','IGS_GE_INVALID_VALUE_WITH_CTXT');
199: FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_NON_STANDARD_ADMIN'));
200: IGS_GE_MSG_STACK.ADD;
201: App_Exception.Raise_Exception;
202: END IF;
203: END IF;
225: new_references.edu_level_id,
226: 'EDU_LEVEL',
227: 'N'
228: ) THEN
229: FND_MESSAGE.SET_NAME('IGS','IGS_GE_PK_UK_NOT_FOUND_CLOSED');
230: FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_EDU_LEVEL'));
231: IGS_GE_MSG_STACK.ADD;
232: App_Exception.Raise_Exception;
233: END IF;
226: 'EDU_LEVEL',
227: 'N'
228: ) THEN
229: FND_MESSAGE.SET_NAME('IGS','IGS_GE_PK_UK_NOT_FOUND_CLOSED');
230: FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_EDU_LEVEL'));
231: IGS_GE_MSG_STACK.ADD;
232: App_Exception.Raise_Exception;
233: END IF;
234:
238: ELSIF NOT Igs_Ad_Test_Type_Pkg.Get_PK_For_Validation (
239: new_references.admission_test_type,
240: 'N'
241: ) THEN
242: FND_MESSAGE.SET_NAME('IGS','IGS_GE_PK_UK_NOT_FOUND_CLOSED');
243: FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_ADM_TEST'));
244: IGS_GE_MSG_STACK.ADD;
245: App_Exception.Raise_Exception;
246: END IF;
239: new_references.admission_test_type,
240: 'N'
241: ) THEN
242: FND_MESSAGE.SET_NAME('IGS','IGS_GE_PK_UK_NOT_FOUND_CLOSED');
243: FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_ADM_TEST'));
244: IGS_GE_MSG_STACK.ADD;
245: App_Exception.Raise_Exception;
246: END IF;
247:
252: new_references.grade_id,
253: 'TEST_RESULTS_GRADE',
254: 'N'
255: ) THEN
256: FND_MESSAGE.SET_NAME('IGS','IGS_GE_PK_UK_NOT_FOUND_CLOSED');
257: FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_GRADE'));
258: IGS_GE_MSG_STACK.ADD;
259: END IF;
260:
253: 'TEST_RESULTS_GRADE',
254: 'N'
255: ) THEN
256: FND_MESSAGE.SET_NAME('IGS','IGS_GE_PK_UK_NOT_FOUND_CLOSED');
257: FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_GRADE'));
258: IGS_GE_MSG_STACK.ADD;
259: END IF;
260:
261: IF (((old_references.score_source_id = new_references.score_source_id)) OR
265: new_references.score_source_id,
266: 'SYS_SCORE_SOURCE',
267: 'N'
268: ) THEN
269: FND_MESSAGE.SET_NAME('IGS','IGS_GE_PK_UK_NOT_FOUND_CLOSED');
270: FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_SCORE_SOURCE'));
271: IGS_GE_MSG_STACK.ADD;
272: App_Exception.Raise_Exception;
273: END IF;
266: 'SYS_SCORE_SOURCE',
267: 'N'
268: ) THEN
269: FND_MESSAGE.SET_NAME('IGS','IGS_GE_PK_UK_NOT_FOUND_CLOSED');
270: FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_SCORE_SOURCE'));
271: IGS_GE_MSG_STACK.ADD;
272: App_Exception.Raise_Exception;
273: END IF;
274:
277: NULL;
278: ELSIF NOT Igs_Pe_Person_Pkg.Get_PK_For_Validation (
279: new_references.person_id
280: ) THEN
281: FND_MESSAGE.SET_NAME('IGS','IGS_GE_PK_UK_NOT_FOUND');
282: FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_PERSON'));
283: IGS_GE_MSG_STACK.ADD;
284: App_Exception.Raise_Exception;
285: END IF;
278: ELSIF NOT Igs_Pe_Person_Pkg.Get_PK_For_Validation (
279: new_references.person_id
280: ) THEN
281: FND_MESSAGE.SET_NAME('IGS','IGS_GE_PK_UK_NOT_FOUND');
282: FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_PERSON'));
283: IGS_GE_MSG_STACK.ADD;
284: App_Exception.Raise_Exception;
285: END IF;
286:
291: IF NOT IGS_LOOKUPS_VIEW_PKG.Get_PK_For_Validation (
292: 'TEST_SCORE_TYPE',
293: new_references.score_type
294: ) THEN
295: FND_MESSAGE.SET_NAME('IGS','IGS_GE_PK_UK_NOT_FOUND_CLOSED');
296: FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_SCORE_TYPE'));
297: IGS_GE_MSG_STACK.ADD;
298: App_Exception.Raise_Exception;
299: END IF;
292: 'TEST_SCORE_TYPE',
293: new_references.score_type
294: ) THEN
295: FND_MESSAGE.SET_NAME('IGS','IGS_GE_PK_UK_NOT_FOUND_CLOSED');
296: FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_SCORE_TYPE'));
297: IGS_GE_MSG_STACK.ADD;
298: App_Exception.Raise_Exception;
299: END IF;
300: END IF;
306: IF NOT IGS_LOOKUPS_VIEW_PKG.Get_PK_For_Validation (
307: 'TEST_SPECIAL_CODE',
308: new_references.special_code
309: ) THEN
310: FND_MESSAGE.SET_NAME('IGS','IGS_GE_PK_UK_NOT_FOUND_CLOSED');
311: FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_GE_SPECIAL'));
312: IGS_GE_MSG_STACK.ADD;
313: App_Exception.Raise_Exception;
314: END IF;
307: 'TEST_SPECIAL_CODE',
308: new_references.special_code
309: ) THEN
310: FND_MESSAGE.SET_NAME('IGS','IGS_GE_PK_UK_NOT_FOUND_CLOSED');
311: FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_GE_SPECIAL'));
312: IGS_GE_MSG_STACK.ADD;
313: App_Exception.Raise_Exception;
314: END IF;
315: END IF;
364: Open cur_rowid(old_references.test_results_id);
365: Fetch cur_rowid INTO lv_rowid;
366: IF (cur_rowid%FOUND) THEN
367: Close cur_rowid;
368: Fnd_Message.Set_Name ('IGS', 'IGS_AD_ATRD_ATR_UPD_FK');
369: IGS_GE_MSG_STACK.ADD;
370: App_Exception.Raise_Exception;
371: Return;
372: END IF;
378: Fetch cur_rowid INTO lv_rowid;
379: IF (cur_rowid%FOUND) THEN
380: IF (new_references.test_date > TRUNC(SYSDATE)) THEN
381: Close cur_rowid;
382: Fnd_Message.Set_Name ('IGS', 'IGS_SS_AD_SEG_NOT_IN_FUTURE');
383: IGS_GE_MSG_STACK.ADD;
384: App_Exception.Raise_Exception;
385: END IF;
386: END IF;
413: CLOSE get_dob_dt_cur;
414:
415: IF l_birth_dt IS NOT NULL AND new_references.test_date IS NOT NULL THEN
416: IF l_birth_dt > new_references.test_date THEN
417: FND_MESSAGE.SET_NAME ('IGS', 'IGS_AD_STRT_DT_LESS_BIRTH_DT');
418: IGS_GE_MSG_STACK.ADD;
419: APP_EXCEPTION.RAISE_EXCEPTION;
420: END IF;
421: END IF;
495: Open cur_rowid;
496: Fetch cur_rowid INTO lv_rowid;
497: IF (cur_rowid%FOUND) THEN
498: Close cur_rowid;
499: Fnd_Message.Set_Name ('IGS', 'IGS_AD_ATR_ACDC_FK2');
500: IGS_GE_MSG_STACK.ADD;
501: App_Exception.Raise_Exception;
502: Return;
503: END IF;
506: Open cur_rowid2;
507: Fetch cur_rowid2 INTO lv_rowid;
508: IF (cur_rowid2%FOUND) THEN
509: Close cur_rowid2;
510: Fnd_Message.Set_Name ('IGS', 'IGS_AD_ATR_ACDC_FK3');
511: IGS_GE_MSG_STACK.ADD;
512: App_Exception.Raise_Exception;
513: Return;
514: END IF;
517: Open cur_rowid3;
518: Fetch cur_rowid3 INTO lv_rowid;
519: IF (cur_rowid3%FOUND) THEN
520: Close cur_rowid3;
521: Fnd_Message.Set_Name ('IGS', 'IGS_AD_ATR_ACDC_FK');
522: IGS_GE_MSG_STACK.ADD;
523: App_Exception.Raise_Exception;
524: Return;
525: END IF;
553: Open cur_rowid;
554: Fetch cur_rowid INTO lv_rowid;
555: IF (cur_rowid%FOUND) THEN
556: Close cur_rowid;
557: Fnd_Message.Set_Name ('IGS', 'IGS_AD_ATR_ADMTT_FK');
558: IGS_GE_MSG_STACK.ADD;
559: App_Exception.Raise_Exception;
560: Return;
561: END IF;
591: Open cur_rowid;
592: Fetch cur_rowid INTO lv_rowid;
593: IF (cur_rowid%FOUND) THEN
594: Close cur_rowid;
595: Fnd_Message.Set_Name ('IGS', 'IGS_AD_PE_PERSON_FK');
596: IGS_GE_MSG_STACK.ADD;
597: App_Exception.Raise_Exception;
598: Return;
599: END IF;
710: -- Call all the procedures related to Before Insert.
711: beforerowinsertupdate(TRUE, FALSE);
712: IF Get_Pk_For_Validation(
713: new_references.test_results_id) THEN
714: Fnd_Message.Set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
715: IGS_GE_MSG_STACK.ADD;
716: App_Exception.Raise_Exception;
717: END IF;
718: Check_Constraints;
732: ELSIF (p_action = 'VALIDATE_INSERT') THEN
733: -- Call all the procedures related to Before Insert.
734: IF Get_PK_For_Validation (
735: new_references.test_results_id) THEN
736: Fnd_Message.Set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
737: IGS_GE_MSG_STACK.ADD;
738: App_Exception.Raise_Exception;
739: END IF;
740: Check_Constraints;
891: else
892: X_PROGRAM_UPDATE_DATE := SYSDATE;
893: end if;
894: else
895: FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
896: IGS_GE_MSG_STACK.ADD;
897: app_exception.raise_exception;
898: end if;
899:
1053: x_rowid => X_ROWID );
1054: EXCEPTION
1055: WHEN OTHERS THEN
1056: IF (SQLCODE IN (-28115, -28113, -28111)) THEN
1057: fnd_message.set_name ('IGS', 'IGS_SC_POLICY_EXCEPTION');
1058: fnd_message.set_token ('ERR_CD', SQLCODE);
1059: igs_ge_msg_stack.add;
1060: igs_sc_gen_001.unset_ctx('R');
1061: app_exception.raise_exception;
1054: EXCEPTION
1055: WHEN OTHERS THEN
1056: IF (SQLCODE IN (-28115, -28113, -28111)) THEN
1057: fnd_message.set_name ('IGS', 'IGS_SC_POLICY_EXCEPTION');
1058: fnd_message.set_token ('ERR_CD', SQLCODE);
1059: igs_ge_msg_stack.add;
1060: igs_sc_gen_001.unset_ctx('R');
1061: app_exception.raise_exception;
1062: ELSE
1156: begin
1157: open c1;
1158: fetch c1 into tlinfo;
1159: if (c1%notfound) then
1160: fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
1161: IGS_GE_MSG_STACK.ADD;
1162: close c1;
1163: app_exception.raise_exception;
1164: return;
1262: AND (X_ACTIVE_IND is null)))
1263: ) then
1264: null;
1265: else
1266: fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
1267: IGS_GE_MSG_STACK.ADD;
1268: app_exception.raise_exception;
1269: end if;
1270: return;
1349: if X_LAST_UPDATE_LOGIN is NULL then
1350: X_LAST_UPDATE_LOGIN := -1;
1351: end if;
1352: else
1353: FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
1354: IGS_GE_MSG_STACK.ADD;
1355: app_exception.raise_exception;
1356: end if;
1357: Before_DML(
1458: PROGRAM_UPDATE_DATE = X_PROGRAM_UPDATE_DATE,
1459: ACTIVE_IND = NEW_REFERENCES.ACTIVE_IND
1460: where ROWID = X_ROWID;
1461: if (sql%notfound) then
1462: fnd_message.set_name ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
1463: igs_ge_msg_stack.add;
1464: igs_sc_gen_001.unset_ctx('R');
1465: app_exception.raise_exception;
1466: end if;
1475: );
1476: EXCEPTION
1477: WHEN OTHERS THEN
1478: IF (SQLCODE = (-28115)) THEN
1479: fnd_message.set_name ('IGS', 'IGS_SC_UPD_POLICY_EXCP');
1480: fnd_message.set_token ('ERR_CD', SQLCODE);
1481: igs_ge_msg_stack.add;
1482: igs_sc_gen_001.unset_ctx('R');
1483: app_exception.raise_exception;
1476: EXCEPTION
1477: WHEN OTHERS THEN
1478: IF (SQLCODE = (-28115)) THEN
1479: fnd_message.set_name ('IGS', 'IGS_SC_UPD_POLICY_EXCP');
1480: fnd_message.set_token ('ERR_CD', SQLCODE);
1481: igs_ge_msg_stack.add;
1482: igs_sc_gen_001.unset_ctx('R');
1483: app_exception.raise_exception;
1484: ELSE
1655: END IF;
1656: delete from IGS_AD_TEST_RESULTS
1657: where ROWID = X_ROWID;
1658: if (sql%notfound) then
1659: fnd_message.set_name ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
1660: igs_ge_msg_stack.add;
1661: igs_sc_gen_001.unset_ctx('R');
1662: app_exception.raise_exception;
1663: end if;