[Home] [Help]
54: -- This modules validates IGS_AD_PS_APPL_INST.prpsd_commencement_dt.
55:
56: -- Validations are:
57:
58: -- * IGS_RE_CANDIDATURE must exist for the IGS_AD_PS_APPL_INST.
59:
60: -- * Prpsd_commencement_dt must be greater than the earlier of the IGS_PS_COURSE
61:
62: -- start date or the admission academic period earliest research start date.
66: -- the admission academic period.
67:
68: DECLARE
69:
70: v_ca_sequence_number IGS_RE_CANDIDATURE.sequence_number%TYPE;
71:
72: v_candidature_exists_ind VARCHAR(1);
73:
74: v_message_name VARCHAR2(30);
84: v_ca_sequence_number := p_ca_sequence_number;
85:
86: IF p_prpsd_commencement_dt IS NOT NULL THEN
87:
88: -- Validate research IGS_RE_CANDIDATURE details and commencemnt
89:
90: IF IGS_EN_VAL_SCA.admp_val_ca_comm(
91:
92: p_person_id,
187: END admp_val_acai_comm;
188:
189: --
190:
191: -- Validate IGS_RE_CANDIDATURE proposed commencement date value.
192:
193: --
194:
195: -- Validate IGS_RE_CANDIDATURE proposed commencement date.
191: -- Validate IGS_RE_CANDIDATURE proposed commencement date value.
192:
193: --
194:
195: -- Validate IGS_RE_CANDIDATURE proposed commencement date.
196:
197:
198: -- Validate IGS_RE_CANDIDATURE update.
199:
194:
195: -- Validate IGS_RE_CANDIDATURE proposed commencement date.
196:
197:
198: -- Validate IGS_RE_CANDIDATURE update.
199:
200: FUNCTION resp_val_ca_upd(
201:
202: p_person_id IN NUMBER ,
215: -- a warning message. (Bug #3577988)
216:
217: BEGIN -- resp_val_ca_upd
218:
219: -- This module validates the update of IGS_RE_CANDIDATURE details. Validations are:
220:
221: -- IGS_RE_CANDIDATURE cannot be updated if
222:
223: -- IGS_EN_STDNT_PS_ATT.course_attempt_status is 'COMPLETED' with all its awards completed.
217: BEGIN -- resp_val_ca_upd
218:
219: -- This module validates the update of IGS_RE_CANDIDATURE details. Validations are:
220:
221: -- IGS_RE_CANDIDATURE cannot be updated if
222:
223: -- IGS_EN_STDNT_PS_ATT.course_attempt_status is 'COMPLETED' with all its awards completed.
224:
225: -- If atleast one award is incomplete or no award is associated, update is allowed but a
344: RETURN BOOLEAN AS
345: /*----------------------------------------------------------------------------
346: || Created By :
347: || Created On :
348: || Purpose : This module validates IGS_RE_CANDIDATURE.research_topic.
349: ||
350: || Known limitations, enhancements or remarks :
351: || Change History :
352: || Who When What
437: App_Exception.Raise_Exception;
438: END resp_val_ca_topic;
439:
440: --
441: -- Validate IGS_RE_CANDIDATURE ACAI link.
442: FUNCTION resp_val_ca_acai(
443:
444: p_person_id IN NUMBER ,
445:
464: RETURN BOOLEAN AS
465:
466: BEGIN -- resp_val_ca_acai
467:
468: -- This module validates the IGS_RE_CANDIDATURE link to an IGS_AD_PS_APPL_INST.
469:
470: -- The following is validated.
471:
472: -- * An existing IGS_AD_PS_APPL_INST
504: -- * The new IGS_AD_PS_APPL_INST (person_id/ acai_admission_appl_number/
505:
506: -- acai_nominated_course_cd/ acai_sequence_number) does not already have a
507:
508: -- research IGS_RE_CANDIDATURE.
509:
510: -- * The new IGS_AD_PS_APPL_INST (person_id/ acai_admission_appl_number/
511:
512: -- acai_nominated_course_cd/ acai_sequence_number) must map to an admission
546: IS
547:
548: SELECT 'x'
549:
550: FROM IGS_RE_CANDIDATURE ca
551:
552: WHERE ca.person_id = p_person_id AND
553:
554: (p_ca_sequence_number IS NULL OR
630: (v_s_admission_process_type <>
631:
632: cst_readmit) THEN
633:
634: -- Validate that a required research IGS_RE_CANDIDATURE link is not being broken.
635:
636: IF IGS_RE_VAL_CA.resp_val_ca_acai_del(
637:
638: p_person_id,
658: IF p_acai_admission_appl_number IS NOT NULL THEN
659:
660: -- Validate that admission IGS_PS_COURSE application does not already have a
661:
662: -- research IGS_RE_CANDIDATURE
663:
664: OPEN c_ca;
665:
666: FETCH c_ca INTO v_test;
754: RETURN BOOLEAN AS
755: /*----------------------------------------------------------------------------
756: || Created By :
757: || Created On :
758: || Purpose : Validate IGS_RE_CANDIDATURE maximum submission date.
759: ||
760: || Known limitations, enhancements or remarks :
761: || Change History :
762: || Who When What
770: DECLARE
771:
772: cst_offer CONSTANT VARCHAR2(10) := 'OFFER';
773: cst_cond_offer CONSTANT VARCHAR2(10) := 'COND-OFFER';
774: v_min_submission_dt IGS_RE_CANDIDATURE.min_submission_dt%TYPE;
775: v_max_submission_dt IGS_RE_CANDIDATURE.max_submission_dt%TYPE;
776: v_commencement_dt DATE;
777: v_stdnt_confm_ind IGS_EN_STDNT_PS_ATT.student_confirmed_ind%TYPE;
778: v_s_adm_otcm_status IGS_AD_OU_STAT.s_adm_outcome_status%TYPE;
771:
772: cst_offer CONSTANT VARCHAR2(10) := 'OFFER';
773: cst_cond_offer CONSTANT VARCHAR2(10) := 'COND-OFFER';
774: v_min_submission_dt IGS_RE_CANDIDATURE.min_submission_dt%TYPE;
775: v_max_submission_dt IGS_RE_CANDIDATURE.max_submission_dt%TYPE;
776: v_commencement_dt DATE;
777: v_stdnt_confm_ind IGS_EN_STDNT_PS_ATT.student_confirmed_ind%TYPE;
778: v_s_adm_otcm_status IGS_AD_OU_STAT.s_adm_outcome_status%TYPE;
779:
910: RETURN BOOLEAN AS
911: /*----------------------------------------------------------------------------
912: || Created By :
913: || Created On :
914: || Purpose : Validate IGS_RE_CANDIDATURE minimum submission date.
915: ||
916: || Known limitations, enhancements or remarks :
917: || Change History :
918: || Who When What
925: DECLARE
926:
927: cst_offer CONSTANT VARCHAR2(10) := 'OFFER';
928: cst_cond_offer CONSTANT VARCHAR2(10) := 'COND-OFFER';
929: v_min_submission_dt IGS_RE_CANDIDATURE.min_submission_dt%TYPE;
930: v_max_submission_dt IGS_RE_CANDIDATURE.max_submission_dt%TYPE;
931: v_commencement_dt DATE;
932: v_stdnt_confm_ind IGS_EN_STDNT_PS_ATT.student_confirmed_ind%TYPE;
933: v_s_adm_otcm_status IGS_AD_OU_STAT.s_adm_outcome_status%TYPE;
926:
927: cst_offer CONSTANT VARCHAR2(10) := 'OFFER';
928: cst_cond_offer CONSTANT VARCHAR2(10) := 'COND-OFFER';
929: v_min_submission_dt IGS_RE_CANDIDATURE.min_submission_dt%TYPE;
930: v_max_submission_dt IGS_RE_CANDIDATURE.max_submission_dt%TYPE;
931: v_commencement_dt DATE;
932: v_stdnt_confm_ind IGS_EN_STDNT_PS_ATT.student_confirmed_ind%TYPE;
933: v_s_adm_otcm_status IGS_AD_OU_STAT.s_adm_outcome_status%TYPE;
934: CURSOR c_sca IS
1004: END IF;
1005: END IF;
1006: END IF; --IF v_min_submission_dt IS NULL
1007: ELSE
1008: -- Get IGS_RE_CANDIDATURE commencement date
1009: IF p_commencement_dt IS NULL THEN
1010: v_commencement_dt := IGS_RE_GEN_001.RESP_GET_CA_COMM(
1011: p_person_id,
1012: p_sca_course_cd,
1071: END resp_val_ca_minsbmsn;
1072:
1073: --
1074:
1075: -- Validate IGS_RE_CANDIDATURE SCA link.
1076:
1077: FUNCTION resp_val_ca_sca(
1078: p_person_id IN NUMBER ,
1079: p_ca_sequence_number IN NUMBER ,
1084: p_acai_sequence_number IN NUMBER ,
1085: p_message_name OUT NOCOPY VARCHAR2 )
1086: RETURN BOOLEAN AS
1087: BEGIN -- resp_val_ca_sca
1088: -- This module validates the IGS_RE_CANDIDATURE link to a IGS_EN_STDNT_PS_ATT.
1089: DECLARE
1090: v_message_name VARCHAR2(30);
1091:
1092: v_student_confirmed_ind IGS_EN_STDNT_PS_ATT.student_confirmed_ind%TYPE;
1108: IGS_EN_STDNT_PS_ATT.course_attempt_status%TYPE := 'COMPLETED';
1109:
1110: CURSOR c_sca (
1111:
1112: cp_course_cd IGS_RE_CANDIDATURE.sca_course_cd%TYPE)
1113:
1114: IS
1115:
1116: SELECT sca.student_confirmed_ind,
1128: IS
1129:
1130: SELECT 'x'
1131:
1132: FROM IGS_RE_CANDIDATURE ca
1133:
1134: WHERE ca.person_id = p_person_id AND
1135:
1136: (p_ca_sequence_number IS NULL OR
1146: IF p_sca_course_cd IS NULL OR
1147:
1148: (p_sca_course_cd <> p_old_sca_course_cd) THEN
1149:
1150: -- Validate that a required research IGS_RE_CANDIDATURE link is not being broken
1151:
1152: IF NOT IGS_RE_VAL_CA.resp_val_ca_sca_del(
1153:
1154: p_person_id,
1206: IF p_sca_course_cd IS NOT NULL THEN
1207:
1208: -- Validate that student IGS_PS_COURSE attempt does not already have a
1209:
1210: -- research IGS_RE_CANDIDATURE.
1211:
1212: OPEN c_ca;
1213:
1214: FETCH c_ca INTO v_test;
1299: END resp_val_ca_sca;
1300:
1301: --
1302:
1303: -- Validate IGS_RE_CANDIDATURE deletion and ACAI link.
1304:
1305: FUNCTION resp_val_ca_acai_del(
1306:
1307: p_person_id IN NUMBER ,
1317: RETURN BOOLEAN AS
1318:
1319: BEGIN -- resp_val_ca_acai_del
1320:
1321: -- This module validates the removal of a IGS_RE_CANDIDATURE/
1322:
1323: -- IGS_AD_PS_APPL_INST link. This may be the result of deletion of
1324:
1325: -- IGS_RE_CANDIDATURE, or removal/change of IGS_RE_CANDIDATURE.acai_admission_appl_number/
1321: -- This module validates the removal of a IGS_RE_CANDIDATURE/
1322:
1323: -- IGS_AD_PS_APPL_INST link. This may be the result of deletion of
1324:
1325: -- IGS_RE_CANDIDATURE, or removal/change of IGS_RE_CANDIDATURE.acai_admission_appl_number/
1326:
1327: -- acai_nominated_course_cd/ acai_sequence_number which defines an existing
1328:
1329: -- admission IGS_PS_COURSE application relationship.
1415: v_s_adm_outcome_status = 'COND-OFFER' THEN
1416:
1417: IF v_research_type_ind = 'Y' THEN
1418:
1419: -- Research IGS_RE_CANDIDATURE is required by research only IGS_PS_COURSEs
1420:
1421: p_message_name := 'IGS_RE_CAND_REQ_WHEN_CRS_OFF';
1422:
1423: RETURN FALSE;
1486: END resp_val_ca_acai_del;
1487:
1488: --
1489:
1490: -- Validate IGS_RE_CANDIDATURE SCA link when deleting.
1491:
1492: FUNCTION resp_val_ca_sca_del(
1493:
1494: p_person_id IN NUMBER ,
1500: RETURN BOOLEAN AS
1501:
1502: BEGIN -- resp_val_ca_sca_del
1503:
1504: -- This module validates the removal of a IGS_RE_CANDIDATURE/IGS_EN_STDNT_PS_ATT
1505:
1506: -- link.
1507:
1508: -- This may be the result of deletion of IGS_RE_CANDIDATURE, or removal/change of
1504: -- This module validates the removal of a IGS_RE_CANDIDATURE/IGS_EN_STDNT_PS_ATT
1505:
1506: -- link.
1507:
1508: -- This may be the result of deletion of IGS_RE_CANDIDATURE, or removal/change of
1509:
1510: -- IGS_RE_CANDIDATURE.sca_course_cd which defines an existing IGS_PS_COURSE attempt
1511:
1512: -- relationship.
1506: -- link.
1507:
1508: -- This may be the result of deletion of IGS_RE_CANDIDATURE, or removal/change of
1509:
1510: -- IGS_RE_CANDIDATURE.sca_course_cd which defines an existing IGS_PS_COURSE attempt
1511:
1512: -- relationship.
1513:
1514: DECLARE
1566: IF v_student_confirmed_ind = 'Y' AND
1567:
1568: v_research_type_ind = 'Y' THEN
1569:
1570: -- Research IGS_RE_CANDIDATURE is required by research only courses.
1571:
1572: p_message_name := 'IGS_RE_CAND_REQ_WHEN_CRS_DEF';
1573:
1574: RETURN FALSE;
1668: END resp_val_gtcc_closed;
1669:
1670: --
1671:
1672: -- Validate IGS_RE_CANDIDATURE SCA/ACAI link.
1673:
1674: FUNCTION resp_val_ca_sca_acai(
1675:
1676: p_person_id IN NUMBER ,
1688: RETURN BOOLEAN AS
1689:
1690: BEGIN -- resp_val_ca_sca_acai
1691:
1692: -- This module validates IGS_RE_CANDIDATURE details are linked to either an
1693:
1694: -- admission IGS_PS_COURSE application or IGS_PS_COURSE attempt. Both these are defined
1695:
1696: -- by optional IGS_RE_CANDIDATURE information that is determined by the process
1692: -- This module validates IGS_RE_CANDIDATURE details are linked to either an
1693:
1694: -- admission IGS_PS_COURSE application or IGS_PS_COURSE attempt. Both these are defined
1695:
1696: -- by optional IGS_RE_CANDIDATURE information that is determined by the process
1697:
1698: -- that initiates the research IGS_RE_CANDIDATURE (ADMF3240/ENRF3000). This module
1699:
1700: -- will only be called from the database trigger.
1694: -- admission IGS_PS_COURSE application or IGS_PS_COURSE attempt. Both these are defined
1695:
1696: -- by optional IGS_RE_CANDIDATURE information that is determined by the process
1697:
1698: -- that initiates the research IGS_RE_CANDIDATURE (ADMF3240/ENRF3000). This module
1699:
1700: -- will only be called from the database trigger.
1701:
1702: -- The following is validated:
1706: -- / acai_nominated_course_cd/acai_sequence_number(IGS_AD_PS_APPL_INST)
1707:
1708: -- must be defined.
1709:
1710: -- * If sca_course_cd exists, then IGS_EN_STDNT_PS_ATT and IGS_RE_CANDIDATURE
1711:
1712: -- admission details must match.
1713:
1714: DECLARE
1750: IF p_sca_course_cd IS NOT NULL THEN
1751:
1752: -- Validate that student IGS_PS_COURSE attempt and research
1753:
1754: -- IGS_RE_CANDIDATURE details match.
1755:
1756: OPEN c_sca;
1757:
1758: FETCH c_sca INTO v_sca_rec;