[Home] [Help]
74: OPEN cur_old_ref_values;
75: FETCH cur_old_ref_values INTO old_references;
76: IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
77: CLOSE cur_old_ref_values;
78: fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
79: igs_ge_msg_stack.add;
80: app_exception.raise_exception;
81: RETURN;
82: END IF;
153: new_references.sequence_number,
154: new_references.panel_code
155: )
156: ) THEN
157: fnd_message.set_name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
158: igs_ge_msg_stack.add;
159: app_exception.raise_exception;
160: END IF;
161:
180: NULL;
181: ELSIF NOT igs_ad_intvw_pnls_pkg.get_pk_for_validation (
182: new_references.panel_code
183: ) THEN
184: fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
185: igs_ge_msg_stack.add;
186: app_exception.raise_exception;
187: END IF;
188:
193: ELSE
194: IF NOT IGS_PE_PERSON_PKG.Get_PK_For_Validation (
195: new_references.person_id
196: )THEN
197: FND_MESSAGE.SET_NAME('FND','FORM_RECORD_DELETED');
198: IGS_AD_GEN_001.SET_TOKEN('From IGS_PE_PERSON ->Parameter: Person_Id ');
199: IGS_GE_MSG_STACK.ADD;
200: APP_EXCEPTION.RAISE_EXCEPTION;
201: END IF;
216: new_references.admission_appl_number,
217: new_references.nominated_course_cd,
218: new_references.sequence_number
219: ) THEN
220: fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
221: igs_ge_msg_stack.add;
222: app_exception.raise_exception;
223: END IF;
224:
228: ELSIF NOT igs_ad_location_pkg.get_pk_for_validation (
229: new_references.location_cd ,
230: 'N'
231: ) THEN
232: fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
233: igs_ge_msg_stack.add;
234: app_exception.raise_exception;
235: END IF;
236:
240: ELSIF NOT igs_ad_room_pkg.get_pk_for_validation (
241: new_references.room_id ,
242: 'N'
243: ) THEN
244: fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
245: igs_ge_msg_stack.add;
246: app_exception.raise_exception;
247: END IF;
248:
255: new_references.final_decision_code,
256: new_references.final_decision_type,
257: 'N'
258: ) THEN
259: fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
260: igs_ge_msg_stack.add;
261: app_exception.raise_exception;
262: END IF;
263:
389: OPEN cur_rowid;
390: FETCH cur_rowid INTO lv_rowid;
391: IF (cur_rowid%FOUND) THEN
392: CLOSE cur_rowid;
393: fnd_message.set_name ('IGS', 'IGS_AD_PNLDTLS_PNL_FK');
394: igs_ge_msg_stack.add;
395: app_exception.raise_exception;
396: RETURN;
397: END IF;
424: OPEN cur_rowid;
425: FETCH cur_rowid INTO lv_rowid;
426: IF (cur_rowid%FOUND) THEN
427: CLOSE cur_rowid;
428: fnd_message.set_name ('IGS', 'IGS_AD_PNLDTLS_LOC_FK');
429: igs_ge_msg_stack.add;
430: app_exception.raise_exception;
431: RETURN;
432: END IF;
459: OPEN cur_rowid;
460: FETCH cur_rowid INTO lv_rowid;
461: IF (cur_rowid%FOUND) THEN
462: CLOSE cur_rowid;
463: fnd_message.set_name ('IGS', 'IGS_AD_PNLDTLS_ROOM_FK');
464: igs_ge_msg_stack.add;
465: app_exception.raise_exception;
466: RETURN;
467: END IF;
496: OPEN cur_rowid;
497: FETCH cur_rowid INTO lv_rowid;
498: IF (cur_rowid%FOUND) THEN
499: CLOSE cur_rowid;
500: fnd_message.set_name ('IGS', 'IGS_AD_PNLDTLS_CODE_CLS_FK');
501: igs_ge_msg_stack.add;
502: app_exception.raise_exception;
503: RETURN;
504: END IF;
676: OPEN c_panel_membr_exist;
677: FETCH c_panel_membr_exist INTO l_panel_membr_exist;
678: IF c_panel_membr_exist%NOTFOUND THEN
679: CLOSE c_panel_membr_exist;
680: FND_MESSAGE.SET_NAME('IGS','IGS_AD_PNL_NO_MBR'); -- Message: The panel inserted has no members.
681: IGS_GE_MSG_STACK.ADD;
682: APP_EXCEPTION.RAISE_EXCEPTION;
683: END IF;
684: IF c_panel_membr_exist%ISOPEN THEN
688: OPEN c_panel_type_code;
689: FETCH c_panel_type_code INTO l_panel_type_Code;
690: IF c_panel_type_code%NOTFOUND THEN
691: CLOSE c_panel_type_code;
692: FND_MESSAGE.SET_NAME('IGS','IGS_AD_PNL_CLSD'); -- Message: The panel is closed or non-existing
693: IGS_GE_MSG_STACK.ADD;
694: APP_EXCEPTION.RAISE_EXCEPTION;
695: END IF;
696: CLOSE c_panel_type_code;
701: FETCH c_apcs_step_exist INTO l_apcs_step_exist;
702: IF c_apcs_step_exist%NOTFOUND THEN
703: CLOSE c_apcs_step_exist;
704: IF l_panel_type_Code = 'SCREENING' THEN
705: FND_MESSAGE.SET_NAME('IGS','IGS_AD_NO_SCRN_STEP'); -- Message: 'Screening required before Interview step
706: igs_ge_msg_stack.add; -- is not selected for this admission category
707: app_exception.raise_exception;
708: END IF;
709: ELSE
712: OPEN c_final_decison;
713: FETCH c_final_decison INTO l_final_decison_rec;
714: IF c_final_decison%NOTFOUND THEN
715: CLOSE c_final_decison;
716: fnd_message.set_name('IGS','IGS_AD_FNLSCRN_DEC_NOT_INTVW'); -- Message: Application cannot be assigned to interview panel
717: -- unless Final Screening decision for at least one panel is INTERVIEW
718: igs_ge_msg_stack.add;
719: app_exception.raise_exception;
720: END IF;
742: OPEN c_lkup_cd_mean('INTR_DECSN', l_dec_type);
743: FETCH c_lkup_cd_mean INTO l_sys_stat_meaning;
744: CLOSE c_lkup_cd_mean;
745:
746: fnd_message.set_name('IGS','IGS_AD_NO_DECISION_CD_SETUP'); -- Message: Unable to assign panel members to the application instance.
747: fnd_message.set_token('CLASS_MEANING', l_class_meaning);
748: fnd_message.set_token('SYS_STAT_MEANING', l_sys_stat_meaning);
749: igs_ge_msg_stack.add;
750: app_exception.raise_exception;
743: FETCH c_lkup_cd_mean INTO l_sys_stat_meaning;
744: CLOSE c_lkup_cd_mean;
745:
746: fnd_message.set_name('IGS','IGS_AD_NO_DECISION_CD_SETUP'); -- Message: Unable to assign panel members to the application instance.
747: fnd_message.set_token('CLASS_MEANING', l_class_meaning);
748: fnd_message.set_token('SYS_STAT_MEANING', l_sys_stat_meaning);
749: igs_ge_msg_stack.add;
750: app_exception.raise_exception;
751: ELSE
744: CLOSE c_lkup_cd_mean;
745:
746: fnd_message.set_name('IGS','IGS_AD_NO_DECISION_CD_SETUP'); -- Message: Unable to assign panel members to the application instance.
747: fnd_message.set_token('CLASS_MEANING', l_class_meaning);
748: fnd_message.set_token('SYS_STAT_MEANING', l_sys_stat_meaning);
749: igs_ge_msg_stack.add;
750: app_exception.raise_exception;
751: ELSE
752: --defaulting final_decision_type, final_decision_type
762: OPEN c_intvw_pnl_exsts;
763: FETCH c_intvw_pnl_exsts INTO intvw_pnl_exsts_rec;
764: IF c_intvw_pnl_exsts%FOUND THEN
765: CLOSE c_intvw_pnl_exsts;
766: 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.
767: igs_ge_msg_stack.add;
768: app_exception.raise_exception;
769: END IF;
770: CLOSE c_intvw_pnl_exsts;
779: OPEN c_mem_dec;
780: FETCH c_mem_dec INTO l_mem_dec_rec;
781: IF c_mem_dec %FOUND THEN
782: CLOSE c_mem_dec;
783: fnd_message.set_name('IGS','IGS_AD_MEM_DEC_PEND');
784: igs_ge_msg_stack.add;
785: app_exception.raise_exception;
786: END IF;
787: CLOSE c_mem_dec;
795: new_references.interview_time IS NOT NULL OR
796: new_references.location_cd IS NOT NULL OR
797: new_references.room_id IS NOT NULL)
798: THEN -- PANEL_MEMBER
799: fnd_message.set_name('IGS','IGS_AD_INVALID_PNL_LVL'); -- Message: Cannot record interview details at panel level as the panel code is mapped to a panel level of Panel Member.
800: igs_ge_msg_stack.add;
801: app_exception.raise_exception;
802: END IF;
803:
809:
810: IF ((new_references.final_decision_date IS NULL AND new_references.final_decision_code IS NOT NULL) OR
811: (new_references.final_decision_date IS NOT NULL AND new_references.final_decision_code IS NULL)) THEN
812: -- Decision Date must be entered if a Decision is entered. If the Decision is saved without Decision Date then raise an error message.
813: fnd_message.set_name('IGS','IGS_AD_MAND_DECISION_INFO'); -- Message: Decision and Decision Date must be entered.
814: igs_ge_msg_stack.add;
815: app_exception.raise_exception;
816: END IF;
817: ELSE
815: app_exception.raise_exception;
816: END IF;
817: ELSE
818: IF new_references.final_decision_date IS NOT NULL THEN
819: fnd_message.set_name('IGS','IGS_AD_MAND_DECISION_INFO'); -- Message: Decision and Decision Date must be entered.
820: igs_ge_msg_stack.add;
821: app_exception.raise_exception;
822: END IF;
823: END IF;
829: FETCH c_appl_dt INTO l_appl_dt;
830: CLOSE c_appl_dt;
831:
832: IF NVL(new_references.interview_date,sysdate) < l_appl_dt THEN
833: fnd_message.set_name('IGS','IGS_AD_APPL_DATE_ERROR'); -- NAME cannot be less than Application Date
834: fnd_message.set_token ('NAME',fnd_message.get_string('IGS','IGS_AD_INTVW_DATE')); -- Message: Decision Date
835: igs_ge_msg_stack.add;
836: app_exception.raise_exception;
837: END IF;
830: CLOSE c_appl_dt;
831:
832: IF NVL(new_references.interview_date,sysdate) < l_appl_dt THEN
833: fnd_message.set_name('IGS','IGS_AD_APPL_DATE_ERROR'); -- NAME cannot be less than Application Date
834: fnd_message.set_token ('NAME',fnd_message.get_string('IGS','IGS_AD_INTVW_DATE')); -- Message: Decision Date
835: igs_ge_msg_stack.add;
836: app_exception.raise_exception;
837: END IF;
838:
838:
839: IF new_references.final_decision_date IS NOT NULL THEN
840: -- Decision Date entered must be greater than or equal to the Application Date. Else raise an Error message.
841: IF new_references.final_decision_date < l_appl_dt OR new_references.final_decision_date > SYSDATE THEN
842: fnd_message.set_name('IGS','IGS_AD_DECISION_DATE'); -- Decision Date Can Neither be greater than System Date nor be less than Application Date
843: igs_ge_msg_stack.add;
844: app_exception.raise_exception;
845: END IF;
846: END IF;
853: OPEN c_memb_exsts;
854: FETCH c_memb_exsts INTO memb_exsts_rec;
855: IF c_memb_exsts%NOTFOUND THEN
856: CLOSE c_memb_exsts;
857: FND_MESSAGE.SET_NAME('IGS','IGS_AD_NO_MBR_APPL_EXTS'); -- Message: Cannot update the final decision when no member exists for this panel.
858: IGS_GE_MSG_STACK.ADD;
859: APP_EXCEPTION.RAISE_EXCEPTION;
860: END IF;
861: CLOSE c_memb_exsts;
869: FETCH c_chk_final_decision INTO rec_chk_final_decision;
870: CLOSE c_chk_final_decision;
871:
872: IF NVL(rec_chk_final_decision.closed_flag,'N') <> 'N' THEN
873: fnd_message.set_name('IGS','IGS_AD_PNL_IS_CLOSED'); -- Message: Closed panel details cannot be updated or deleted.
874: igs_ge_msg_stack.add;
875: app_exception.raise_exception;
876: END IF;
877: END IF;
886: OPEN c_panel_type_code;
887: FETCH c_panel_type_code INTO l_panel_type_Code;
888: IF c_panel_type_code%NOTFOUND THEN
889: CLOSE c_panel_type_code;
890: FND_MESSAGE.SET_NAME('IGS','IGS_AD_PNL_CLSD'); -- Message: The panel is closed or non-existing
891: IGS_GE_MSG_STACK.ADD;
892: APP_EXCEPTION.RAISE_EXCEPTION;
893: END IF;
894: CLOSE c_panel_type_code;
989: OPEN c_lkup_cd_mean('INTR_DECSN', l_panel_type_code);
990: FETCH c_lkup_cd_mean INTO l_sys_stat_meaning;
991: CLOSE c_lkup_cd_mean;
992:
993: fnd_message.set_name('IGS','IGS_AD_NO_DECISION_CD_SETUP'); -- Message: Unable to assign panel members to the application instance.
994: fnd_message.set_token('CLASS_MEANING', l_class_meaning);
995: fnd_message.set_token('SYS_STAT_MEANING', l_sys_stat_meaning);
996: igs_ge_msg_stack.add;
997: app_exception.raise_exception;
990: FETCH c_lkup_cd_mean INTO l_sys_stat_meaning;
991: CLOSE c_lkup_cd_mean;
992:
993: fnd_message.set_name('IGS','IGS_AD_NO_DECISION_CD_SETUP'); -- Message: Unable to assign panel members to the application instance.
994: fnd_message.set_token('CLASS_MEANING', l_class_meaning);
995: fnd_message.set_token('SYS_STAT_MEANING', l_sys_stat_meaning);
996: igs_ge_msg_stack.add;
997: app_exception.raise_exception;
998: END IF;
991: CLOSE c_lkup_cd_mean;
992:
993: fnd_message.set_name('IGS','IGS_AD_NO_DECISION_CD_SETUP'); -- Message: Unable to assign panel members to the application instance.
994: fnd_message.set_token('CLASS_MEANING', l_class_meaning);
995: fnd_message.set_token('SYS_STAT_MEANING', l_sys_stat_meaning);
996: igs_ge_msg_stack.add;
997: app_exception.raise_exception;
998: END IF;
999:
1194: IF ( get_pk_for_validation(
1195: new_references.panel_dtls_id
1196: )
1197: ) THEN
1198: fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
1199: igs_ge_msg_stack.add;
1200: app_exception.raise_exception;
1201: END IF;
1202: beforerowinsertupdatedelete1( p_inserting => TRUE , p_updating => FALSE, p_deleting=> FALSE);
1216: IF ( get_pk_for_validation (
1217: new_references.panel_dtls_id
1218: )
1219: ) THEN
1220: fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
1221: igs_ge_msg_stack.add;
1222: app_exception.raise_exception;
1223: END IF;
1224: beforerowinsertupdatedelete1( p_inserting => TRUE , p_updating => FALSE, p_deleting=> FALSE);
1303: IF (x_last_update_login IS NULL) THEN
1304: x_last_update_login := -1;
1305: END IF;
1306: ELSE
1307: fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
1308: fnd_message.set_token ('ROUTINE', 'IGS_AD_PANEL_DTLS_PKG.INSERT_ROW');
1309: igs_ge_msg_stack.add;
1310: app_exception.raise_exception;
1311: END IF;
1304: x_last_update_login := -1;
1305: END IF;
1306: ELSE
1307: fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
1308: fnd_message.set_token ('ROUTINE', 'IGS_AD_PANEL_DTLS_PKG.INSERT_ROW');
1309: igs_ge_msg_stack.add;
1310: app_exception.raise_exception;
1311: END IF;
1312:
1460: -- 1) ORA-28115 (policy with check option violation) which is raised when Policy predicate was evaluated to FALSE with the updated values.
1461: -- 2) ORA-28113 (policy predicate has error) which is raised when Policy function generates invalid predicate.
1462: -- 3) ORA-28111 (insufficient privilege to evaluate policy predicate) which is raised when Predicate has a subquery which contains objects
1463: -- that the ownerof policy function does not have privilege to access.
1464: FND_MESSAGE.SET_NAME ('IGS', 'IGS_SC_POLICY_EXCEPTION');
1465: FND_MESSAGE.SET_TOKEN('ERR_CD',SQLCODE);
1466: IGS_GE_MSG_STACK.ADD;
1467: app_exception.raise_exception;
1468: ELSE
1461: -- 2) ORA-28113 (policy predicate has error) which is raised when Policy function generates invalid predicate.
1462: -- 3) ORA-28111 (insufficient privilege to evaluate policy predicate) which is raised when Predicate has a subquery which contains objects
1463: -- that the ownerof policy function does not have privilege to access.
1464: FND_MESSAGE.SET_NAME ('IGS', 'IGS_SC_POLICY_EXCEPTION');
1465: FND_MESSAGE.SET_TOKEN('ERR_CD',SQLCODE);
1466: IGS_GE_MSG_STACK.ADD;
1467: app_exception.raise_exception;
1468: ELSE
1469: RAISE;
1564:
1565: OPEN c1;
1566: FETCH c1 INTO tlinfo;
1567: IF (c1%notfound) THEN
1568: fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
1569: igs_ge_msg_stack.add;
1570: CLOSE c1;
1571: app_exception.raise_exception;
1572: RETURN;
1610: AND ((tlinfo.attribute20 = x_attribute20) OR ((tlinfo.attribute20 IS NULL) AND (X_attribute20 IS NULL)))
1611: ) THEN
1612: NULL;
1613: ELSE
1614: fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
1615: igs_ge_msg_stack.add;
1616: app_exception.raise_exception;
1617: END IF;
1618:
1690: IF (x_last_update_login IS NULL) THEN
1691: x_last_update_login := -1;
1692: END IF;
1693: ELSE
1694: fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
1695: fnd_message.set_token ('ROUTINE', 'IGS_AD_PANEL_DTLS_PKG.UPDATE_ROW');
1696: igs_ge_msg_stack.add;
1697: app_exception.raise_exception;
1698: END IF;
1691: x_last_update_login := -1;
1692: END IF;
1693: ELSE
1694: fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
1695: fnd_message.set_token ('ROUTINE', 'IGS_AD_PANEL_DTLS_PKG.UPDATE_ROW');
1696: igs_ge_msg_stack.add;
1697: app_exception.raise_exception;
1698: END IF;
1699:
1786: last_update_login = x_last_update_login
1787: WHERE rowid = x_rowid;
1788:
1789: IF (SQL%NOTFOUND) THEN
1790: fnd_message.set_name ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
1791: igs_ge_msg_stack.add;
1792: IF (x_mode = 'S') THEN
1793: igs_sc_gen_001.unset_ctx('R');
1794: END IF;
1811: -- 1) ORA-28115 (policy with check option violation) which is raised when Policy predicate was evaluated to FALSE with the updated values.
1812: -- 2) ORA-28113 (policy predicate has error) which is raised when Policy function generates invalid predicate.
1813: -- 3) ORA-28111 (insufficient privilege to evaluate policy predicate) which is raised when Predicate has a subquery which contains objects
1814: -- that the ownerof policy function does not have privilege to access.
1815: FND_MESSAGE.SET_NAME ('IGS', 'IGS_SC_UPD_POLICY_EXCP');
1816: FND_MESSAGE.SET_TOKEN('ERR_CD',SQLCODE);
1817: IGS_GE_MSG_STACK.ADD;
1818: app_exception.raise_exception;
1819: ELSE
1812: -- 2) ORA-28113 (policy predicate has error) which is raised when Policy function generates invalid predicate.
1813: -- 3) ORA-28111 (insufficient privilege to evaluate policy predicate) which is raised when Predicate has a subquery which contains objects
1814: -- that the ownerof policy function does not have privilege to access.
1815: FND_MESSAGE.SET_NAME ('IGS', 'IGS_SC_UPD_POLICY_EXCP');
1816: FND_MESSAGE.SET_TOKEN('ERR_CD',SQLCODE);
1817: IGS_GE_MSG_STACK.ADD;
1818: app_exception.raise_exception;
1819: ELSE
1820: RAISE;
1995: DELETE FROM igs_ad_panel_dtls
1996: WHERE rowid = x_rowid;
1997:
1998: IF (SQL%NOTFOUND) THEN
1999: fnd_message.set_name ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
2000: igs_ge_msg_stack.add;
2001: IF (x_mode = 'S') THEN
2002: igs_sc_gen_001.unset_ctx('R');
2003: END IF;
2018: -- 1) ORA-28115 (policy with check option violation) which is raised when Policy predicate was evaluated to FALSE with the updated values.
2019: -- 2) ORA-28113 (policy predicate has error) which is raised when Policy function generates invalid predicate.
2020: -- 3) ORA-28111 (insufficient privilege to evaluate policy predicate) which is raised when Predicate has a subquery which contains objects
2021: -- that the ownerof policy function does not have privilege to access.
2022: FND_MESSAGE.SET_NAME ('IGS', 'IGS_SC_POLICY_EXCEPTION');
2023: FND_MESSAGE.SET_TOKEN('ERR_CD',SQLCODE);
2024: IGS_GE_MSG_STACK.ADD;
2025: app_exception.raise_exception;
2026: ELSE
2019: -- 2) ORA-28113 (policy predicate has error) which is raised when Policy function generates invalid predicate.
2020: -- 3) ORA-28111 (insufficient privilege to evaluate policy predicate) which is raised when Predicate has a subquery which contains objects
2021: -- that the ownerof policy function does not have privilege to access.
2022: FND_MESSAGE.SET_NAME ('IGS', 'IGS_SC_POLICY_EXCEPTION');
2023: FND_MESSAGE.SET_TOKEN('ERR_CD',SQLCODE);
2024: IGS_GE_MSG_STACK.ADD;
2025: app_exception.raise_exception;
2026: ELSE
2027: RAISE;