[Home] [Help]
70: OPEN cur_old_ref_values;
71: FETCH cur_old_ref_values INTO old_references;
72: IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
73: CLOSE cur_old_ref_values;
74: fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
75: igs_ge_msg_stack.add;
76: app_exception.raise_exception;
77: RETURN;
78: END IF;
143: NULL;
144: ELSIF NOT igs_ad_panel_dtls_pkg.get_pk_for_validation (
145: new_references.panel_dtls_id
146: ) THEN
147: fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
148: igs_ge_msg_stack.add;
149: app_exception.raise_exception;
150: END IF;
151:
158: new_references.member_decision_code,
159: new_references.member_decision_type ,
160: 'N'
161: ) THEN
162: fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
163: igs_ge_msg_stack.add;
164: app_exception.raise_exception;
165: END IF;
166:
170: ELSIF NOT igs_ad_location_pkg.get_pk_for_validation (
171: new_references.location_cd ,
172: 'N'
173: ) THEN
174: fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
175: igs_ge_msg_stack.add;
176: app_exception.raise_exception;
177: END IF;
178:
182: ELSIF NOT igs_ad_room_pkg.get_pk_for_validation (
183: new_references.room_id ,
184: 'N'
185: ) THEN
186: fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
187: igs_ge_msg_stack.add;
188: app_exception.raise_exception;
189: END IF;
190:
194: ELSE
195: IF NOT IGS_PE_PERSON_PKG.Get_PK_For_Validation (
196: new_references.member_person_id
197: )THEN
198: FND_MESSAGE.SET_NAME('FND','FORM_RECORD_DELETED');
199: IGS_AD_GEN_001.SET_TOKEN('From IGS_PE_PERSON ->Parameter: Person_Id ');
200: IGS_GE_MSG_STACK.ADD;
201: APP_EXCEPTION.RAISE_EXCEPTION;
202: END IF;
285: OPEN cur_rowid;
286: FETCH cur_rowid INTO lv_rowid;
287: IF (cur_rowid%FOUND) THEN
288: CLOSE cur_rowid;
289: fnd_message.set_name ('IGS', 'IGS_AD_PNLMBMR_PNDTLS_FK');
290: igs_ge_msg_stack.add;
291: app_exception.raise_exception;
292: RETURN;
293: END IF;
322: OPEN cur_rowid;
323: FETCH cur_rowid INTO lv_rowid;
324: IF (cur_rowid%FOUND) THEN
325: CLOSE cur_rowid;
326: fnd_message.set_name ('IGS', 'IGS_AD_PNLMBMR_CODE_CLS_FK');
327: igs_ge_msg_stack.add;
328: app_exception.raise_exception;
329: RETURN;
330: END IF;
357: OPEN cur_rowid;
358: FETCH cur_rowid INTO lv_rowid;
359: IF (cur_rowid%FOUND) THEN
360: CLOSE cur_rowid;
361: fnd_message.set_name ('IGS', 'IGS_AD_PNLMBMR_LOC_FK');
362: igs_ge_msg_stack.add;
363: app_exception.raise_exception;
364: RETURN;
365: END IF;
392: OPEN cur_rowid;
393: FETCH cur_rowid INTO lv_rowid;
394: IF (cur_rowid%FOUND) THEN
395: CLOSE cur_rowid;
396: fnd_message.set_name ('IGS', 'IGS_AD_PNLMBMR_ROOM_FK');
397: igs_ge_msg_stack.add;
398: app_exception.raise_exception;
399: RETURN;
400: END IF;
527: OPEN c_interviewer_exist;
528: FETCH c_interviewer_exist INTO l_interviewer_exist;
529: IF c_interviewer_exist%NOTFOUND THEN
530: CLOSE c_interviewer_exist;
531: fnd_message.set_name('IGS','IGS_AD_NO_INTERVIEWER_PTY'); -- Message: The person you are adding does not have the person type of Interviewer.
532: igs_ge_msg_stack.add;
533: app_exception.raise_exception;
534: END IF;
535: IF c_interviewer_exist%ISOPEN THEN
561: OPEN c_lkup_cd_mean('INTR_DECSN', l_dec_type);
562: FETCH c_lkup_cd_mean INTO l_sys_stat_meaning;
563: CLOSE c_lkup_cd_mean;
564:
565: fnd_message.set_name('IGS','IGS_AD_NO_DECISION_CD_SETUP'); -- Message: Unable to assign panel members to the application instance.
566: fnd_message.set_token('CLASS_MEANING', l_class_meaning);
567: fnd_message.set_token('SYS_STAT_MEANING', l_sys_stat_meaning);
568: igs_ge_msg_stack.add;
569: app_exception.raise_exception;
562: FETCH c_lkup_cd_mean INTO l_sys_stat_meaning;
563: CLOSE c_lkup_cd_mean;
564:
565: fnd_message.set_name('IGS','IGS_AD_NO_DECISION_CD_SETUP'); -- Message: Unable to assign panel members to the application instance.
566: fnd_message.set_token('CLASS_MEANING', l_class_meaning);
567: fnd_message.set_token('SYS_STAT_MEANING', l_sys_stat_meaning);
568: igs_ge_msg_stack.add;
569: app_exception.raise_exception;
570: ELSE
563: CLOSE c_lkup_cd_mean;
564:
565: fnd_message.set_name('IGS','IGS_AD_NO_DECISION_CD_SETUP'); -- Message: Unable to assign panel members to the application instance.
566: fnd_message.set_token('CLASS_MEANING', l_class_meaning);
567: fnd_message.set_token('SYS_STAT_MEANING', l_sys_stat_meaning);
568: igs_ge_msg_stack.add;
569: app_exception.raise_exception;
570: ELSE
571: --defaulting final_decision_type, final_decision_type
585:
586: IF ((new_references.member_decision_date IS NULL AND new_references.member_decision_code IS NOT NULL) OR
587: (new_references.member_decision_date IS NOT NULL AND new_references.member_decision_code IS NULL)) THEN
588: -- Decision Date must be entered if a Decision is entered. If the Decision is saved without Decision Date then raise an error message.
589: fnd_message.set_name('IGS','IGS_AD_MAND_DECISION_INFO'); -- Message: Decision and Decision Date must be entered.
590: igs_ge_msg_stack.add;
591: app_exception.raise_exception;
592: END IF;
593: ELSE
591: app_exception.raise_exception;
592: END IF;
593: ELSE
594: IF new_references.member_decision_date IS NOT NULL THEN
595: fnd_message.set_name('IGS','IGS_AD_MAND_DECISION_INFO'); -- Message: Decision and Decision Date must be entered.
596: igs_ge_msg_stack.add;
597: app_exception.raise_exception;
598: END IF;
599: END IF;
605: FETCH c_appl_dt INTO l_appl_dt;
606: CLOSE c_appl_dt;
607:
608: IF NVL(new_references.interview_date,sysdate) < l_appl_dt THEN
609: fnd_message.set_name('IGS','IGS_AD_APPL_DATE_ERROR'); -- NAME cannot be less than Application Date
610: fnd_message.set_token ('NAME',fnd_message.get_string('IGS','IGS_AD_INTVW_DATE')); -- Message: Decision Date
611: igs_ge_msg_stack.add;
612: app_exception.raise_exception;
613: END IF;
606: CLOSE c_appl_dt;
607:
608: IF NVL(new_references.interview_date,sysdate) < l_appl_dt THEN
609: fnd_message.set_name('IGS','IGS_AD_APPL_DATE_ERROR'); -- NAME cannot be less than Application Date
610: fnd_message.set_token ('NAME',fnd_message.get_string('IGS','IGS_AD_INTVW_DATE')); -- Message: Decision Date
611: igs_ge_msg_stack.add;
612: app_exception.raise_exception;
613: END IF;
614:
614:
615: IF new_references.member_decision_date IS NOT NULL THEN
616: -- Decision Date entered must be greater than or equal to the Application Date. Else raise an Error message.
617: IF new_references.member_decision_date < l_appl_dt OR new_references.member_decision_date > SYSDATE THEN
618: fnd_message.set_name('IGS','IGS_AD_DECISION_DATE'); -- Decision Date Can Neither be greater than System Date nor be less than Application Date
619: igs_ge_msg_stack.add;
620: app_exception.raise_exception;
621: END IF;
622: END IF;
628: l_final_decision := igs_ad_gen_013.get_sys_code_status(rec_chk_final_decision.final_decision_code,
629: rec_chk_final_decision.final_decision_type);
630:
631: IF NVL(l_final_decision,'PENDING') <> 'PENDING' THEN
632: fnd_message.set_name('IGS','IGS_AD_PNL_FNL_DECISION_EXITS'); -- Message: Cannot update/insert panel member's information when the panel final decision is already recorded.
633: igs_ge_msg_stack.add;
634: app_exception.raise_exception;
635: END IF;
636: END IF;
644: OPEN c_intvw_pnl_exsts(instance_dets_rec);
645: FETCH c_intvw_pnl_exsts INTO intvw_pnl_exsts_rec;
646: IF c_intvw_pnl_exsts%FOUND THEN
647: CLOSE c_intvw_pnl_exsts;
648: fnd_message.set_name('IGS','IGS_AD_INTVW_PNL_EXITS'); -- Message: Cannot update screening information when interview panel is already associated to the application instance.
649: igs_ge_msg_stack.add;
650: app_exception.raise_exception;
651: END IF;
652: CLOSE c_intvw_pnl_exsts;
657: FETCH c_chk_final_decision INTO rec_chk_final_decision;
658: CLOSE c_chk_final_decision;
659:
660: IF NVL(rec_chk_final_decision.closed_flag,'N') <> 'N' THEN
661: fnd_message.set_name('IGS','IGS_AD_PNL_IS_CLOSED'); -- Message: Cannot update or delete a closed panel.
662: igs_ge_msg_stack.add;
663: app_exception.raise_exception;
664: END IF;
665: END IF;
674: -- Check the old member's decision associated with panel member.
675: l_old_member_decision := igs_ad_gen_013.get_sys_code_status(old_references.member_decision_code,
676: old_references.member_decision_type);
677: IF l_old_member_decision = 'VOIDED' AND l_member_decision <> 'VOIDED' THEN
678: fnd_message.set_name('IGS','IGS_AD_NO_UPD_VOID_DECISION'); -- Message: Cannot update the reviewer's decision as it is mapped to a status of Voided.
679: igs_ge_msg_stack.add;
680: app_exception.raise_exception;
681: END IF;
682:
697: l_member_decision := igs_ad_gen_013.get_sys_code_status(old_references.member_decision_code,
698: old_references.member_decision_type);
699:
700: IF NVL(l_final_decision,'*') <> 'PENDING' OR NVL(l_member_decision,'*') <> 'PENDING' THEN
701: fnd_message.set_name('IGS','IGS_AD_PNL_DECISION_EXITS'); -- Message: Cannot delete the panel member when the decision is already recorded.
702: igs_ge_msg_stack.add;
703: app_exception.raise_exception;
704: END IF;
705: END IF;
804: new_references.panel_dtls_id,
805: new_references.member_person_id
806: )
807: ) THEN
808: fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
809: igs_ge_msg_stack.add;
810: app_exception.raise_exception;
811: END IF;
812: beforerowinsertupdatedelete1( p_inserting => TRUE , p_updating => FALSE, p_deleting=> FALSE);
825: new_references.panel_dtls_id,
826: new_references.member_person_id
827: )
828: ) THEN
829: fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
830: igs_ge_msg_stack.add;
831: app_exception.raise_exception;
832: END IF;
833: beforerowinsertupdatedelete1( p_inserting => TRUE , p_updating => FALSE, p_deleting=> FALSE);
904: IF (x_last_update_login IS NULL) THEN
905: x_last_update_login := -1;
906: END IF;
907: ELSE
908: fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
909: fnd_message.set_token ('ROUTINE', 'IGS_AD_PNMEMBR_DTLS_PKG.INSERT_ROW');
910: igs_ge_msg_stack.add;
911: app_exception.raise_exception;
912: END IF;
905: x_last_update_login := -1;
906: END IF;
907: ELSE
908: fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
909: fnd_message.set_token ('ROUTINE', 'IGS_AD_PNMEMBR_DTLS_PKG.INSERT_ROW');
910: igs_ge_msg_stack.add;
911: app_exception.raise_exception;
912: END IF;
913:
1045: -- 1) ORA-28115 (policy with check option violation) which is raised when Policy predicate was evaluated to FALSE with the updated values.
1046: -- 2) ORA-28113 (policy predicate has error) which is raised when Policy function generates invalid predicate.
1047: -- 3) ORA-28111 (insufficient privilege to evaluate policy predicate) which is raised when Predicate has a subquery which contains objects
1048: -- that the ownerof policy function does not have privilege to access.
1049: FND_MESSAGE.SET_NAME ('IGS', 'IGS_SC_POLICY_EXCEPTION');
1050: FND_MESSAGE.SET_TOKEN('ERR_CD',SQLCODE);
1051: IGS_GE_MSG_STACK.ADD;
1052: app_exception.raise_exception;
1053: ELSE
1046: -- 2) ORA-28113 (policy predicate has error) which is raised when Policy function generates invalid predicate.
1047: -- 3) ORA-28111 (insufficient privilege to evaluate policy predicate) which is raised when Predicate has a subquery which contains objects
1048: -- that the ownerof policy function does not have privilege to access.
1049: FND_MESSAGE.SET_NAME ('IGS', 'IGS_SC_POLICY_EXCEPTION');
1050: FND_MESSAGE.SET_TOKEN('ERR_CD',SQLCODE);
1051: IGS_GE_MSG_STACK.ADD;
1052: app_exception.raise_exception;
1053: ELSE
1054: RAISE;
1140:
1141: OPEN c1;
1142: FETCH c1 INTO tlinfo;
1143: IF (c1%notfound) THEN
1144: fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
1145: igs_ge_msg_stack.add;
1146: CLOSE c1;
1147: app_exception.raise_exception;
1148: RETURN;
1181: AND ((tlinfo.attribute20 = x_attribute20) OR ((tlinfo.attribute20 IS NULL) AND (X_attribute20 IS NULL)))
1182: ) THEN
1183: NULL;
1184: ELSE
1185: fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
1186: igs_ge_msg_stack.add;
1187: app_exception.raise_exception;
1188: END IF;
1189:
1256: IF (x_last_update_login IS NULL) THEN
1257: x_last_update_login := -1;
1258: END IF;
1259: ELSE
1260: fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
1261: fnd_message.set_token ('ROUTINE', 'IGS_AD_PNMEMBR_DTLS_PKG.UPDATE_ROW');
1262: igs_ge_msg_stack.add;
1263: app_exception.raise_exception;
1264: END IF;
1257: x_last_update_login := -1;
1258: END IF;
1259: ELSE
1260: fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
1261: fnd_message.set_token ('ROUTINE', 'IGS_AD_PNMEMBR_DTLS_PKG.UPDATE_ROW');
1262: igs_ge_msg_stack.add;
1263: app_exception.raise_exception;
1264: END IF;
1265:
1343: last_update_login = x_last_update_login
1344: WHERE rowid = x_rowid;
1345:
1346: IF (SQL%NOTFOUND) THEN
1347: fnd_message.set_name ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
1348: igs_ge_msg_stack.add;
1349: IF (x_mode = 'S') THEN
1350: igs_sc_gen_001.unset_ctx('R');
1351: END IF;
1366: -- 1) ORA-28115 (policy with check option violation) which is raised when Policy predicate was evaluated to FALSE with the updated values.
1367: -- 2) ORA-28113 (policy predicate has error) which is raised when Policy function generates invalid predicate.
1368: -- 3) ORA-28111 (insufficient privilege to evaluate policy predicate) which is raised when Predicate has a subquery which contains objects
1369: -- that the ownerof policy function does not have privilege to access.
1370: FND_MESSAGE.SET_NAME ('IGS', 'IGS_SC_UPD_POLICY_EXCP');
1371: FND_MESSAGE.SET_TOKEN('ERR_CD',SQLCODE);
1372: IGS_GE_MSG_STACK.ADD;
1373: app_exception.raise_exception;
1374: ELSE
1367: -- 2) ORA-28113 (policy predicate has error) which is raised when Policy function generates invalid predicate.
1368: -- 3) ORA-28111 (insufficient privilege to evaluate policy predicate) which is raised when Predicate has a subquery which contains objects
1369: -- that the ownerof policy function does not have privilege to access.
1370: FND_MESSAGE.SET_NAME ('IGS', 'IGS_SC_UPD_POLICY_EXCP');
1371: FND_MESSAGE.SET_TOKEN('ERR_CD',SQLCODE);
1372: IGS_GE_MSG_STACK.ADD;
1373: app_exception.raise_exception;
1374: ELSE
1375: RAISE;
1539: DELETE FROM igs_ad_pnmembr_dtls
1540: WHERE rowid = x_rowid;
1541:
1542: IF (SQL%NOTFOUND) THEN
1543: fnd_message.set_name ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
1544: igs_ge_msg_stack.add;
1545: IF (x_mode = 'S') THEN
1546: igs_sc_gen_001.unset_ctx('R');
1547: END IF;
1562: -- 1) ORA-28115 (policy with check option violation) which is raised when Policy predicate was evaluated to FALSE with the updated values.
1563: -- 2) ORA-28113 (policy predicate has error) which is raised when Policy function generates invalid predicate.
1564: -- 3) ORA-28111 (insufficient privilege to evaluate policy predicate) which is raised when Predicate has a subquery which contains objects
1565: -- that the ownerof policy function does not have privilege to access.
1566: FND_MESSAGE.SET_NAME ('IGS', 'IGS_SC_POLICY_EXCEPTION');
1567: FND_MESSAGE.SET_TOKEN('ERR_CD',SQLCODE);
1568: IGS_GE_MSG_STACK.ADD;
1569: app_exception.raise_exception;
1570: ELSE
1563: -- 2) ORA-28113 (policy predicate has error) which is raised when Policy function generates invalid predicate.
1564: -- 3) ORA-28111 (insufficient privilege to evaluate policy predicate) which is raised when Predicate has a subquery which contains objects
1565: -- that the ownerof policy function does not have privilege to access.
1566: FND_MESSAGE.SET_NAME ('IGS', 'IGS_SC_POLICY_EXCEPTION');
1567: FND_MESSAGE.SET_TOKEN('ERR_CD',SQLCODE);
1568: IGS_GE_MSG_STACK.ADD;
1569: app_exception.raise_exception;
1570: ELSE
1571: RAISE;