[Home] [Help]
1: PACKAGE BODY igs_ad_val_acai_status AS
2: /* $Header: IGSAD24B.pls 120.8 2005/11/25 04:51:02 appldev ship $ */
3: --bug 1956374 msrinivi Removed duplicate func enrp_val_trnsfr_act 27 aug,01
4: -- Validate the IGS_AD_PS_APPL_INST.adm_entry_qual_status.
5: -- hreddych #2602077 SF Integration Added the FUNCTION admp_val_aods_update
6: --sarakshi 27-Feb-2003 Enh#2797116,modified procedure admp_val_lafs_coo ,added delete_falg check in the where clause
7: -- of the cursor c_coo
8: FUNCTION admp_val_acai_aeqs(
11: p_s_admission_process_type IN VARCHAR2 ,
12: p_message_name OUT NOCOPY VARCHAR2 )
13: RETURN BOOLEAN AS
14: BEGIN -- admp_val_acai_aeqs
15: -- Validate the IGS_AD_PS_APPL_INST.adm_entry_qual_status.
16: DECLARE
17: v_message_name VARCHAR2(30);
18: v_s_adm_entry_qual_status igs_ad_ent_qf_stat.s_adm_entry_qual_status%TYPE;
19: v_s_adm_outcome_status igs_ad_ou_stat.s_adm_outcome_status%TYPE;
53: App_Exception.Raise_Exception;
54:
55: END admp_val_acai_aeqs;
56: --
57: -- Validate the IGS_AD_PS_APPL_INST.adm_entry_qual_status.
58: FUNCTION admp_val_aeqs_item(
59: p_adm_entry_qual_status IN VARCHAR2 ,
60: p_s_admission_process_type IN VARCHAR2 ,
61: p_message_name OUT NOCOPY VARCHAR2 )
60: p_s_admission_process_type IN VARCHAR2 ,
61: p_message_name OUT NOCOPY VARCHAR2 )
62: RETURN BOOLEAN AS
63: BEGIN -- admp_val_aeqs_item
64: -- Validate the IGS_AD_PS_APPL_INST.adm_entry_qual_status.
65: -- It must be open and valid.
66: DECLARE
67: v_message_name VARCHAR2(30);
68: v_s_adm_entry_qual_status igs_ad_ent_qf_stat.s_adm_entry_qual_status%TYPE;
141: p_s_adm_outcome_status IN VARCHAR2 ,
142: p_message_name OUT NOCOPY VARCHAR2 )
143: RETURN BOOLEAN AS
144: BEGIN -- admp_val_aeqs_aos
145: -- This module validates IGS_AD_PS_APPL_INST.adm_entry_qual_status
146: -- against IGS_AD_PS_APPL_INST.adm_outcome_status
147: -- Validations are:
148: -- If the admission entry qualification status is applicable for the
149: -- admission application, then it cannot be pending if an offer has been made.
142: p_message_name OUT NOCOPY VARCHAR2 )
143: RETURN BOOLEAN AS
144: BEGIN -- admp_val_aeqs_aos
145: -- This module validates IGS_AD_PS_APPL_INST.adm_entry_qual_status
146: -- against IGS_AD_PS_APPL_INST.adm_outcome_status
147: -- Validations are:
148: -- If the admission entry qualification status is applicable for the
149: -- admission application, then it cannot be pending if an offer has been made.
150: -- If the admission entry qualification status is applicable for the admission
199: IGS_GE_MSG_STACK.ADD;
200: App_Exception.Raise_Exception;
201: END admp_val_aeqs_aos;
202: --
203: -- Validate the IGS_AD_PS_APPL_INST.adm_doc_status.
204: FUNCTION admp_val_acai_ads(
205: p_adm_doc_status IN VARCHAR2 ,
206: p_adm_outcome_status IN VARCHAR2 ,
207: p_adm_cndtnl_offer_status IN VARCHAR2 ,
209: p_cond_offer_doc_allowed IN VARCHAR2,
210: p_message_name OUT NOCOPY VARCHAR2 )
211: RETURN BOOLEAN AS
212: BEGIN -- admp_val_acai_ads
213: -- Validate the IGS_AD_PS_APPL_INST.adm_doc_status.
214: DECLARE
215: v_message_name VARCHAR2(30);
216: v_s_adm_doc_status igs_ad_doc_stat.s_adm_doc_status%TYPE;
217: v_s_adm_outcome_status igs_ad_ou_stat.s_adm_outcome_status%TYPE;
255: App_Exception.Raise_Exception;
256:
257: END admp_val_acai_ads;
258: --
259: -- Validate the IGS_AD_PS_APPL_INST.adm_doc_status.
260: FUNCTION admp_val_ads_item(
261: p_adm_doc_status IN VARCHAR2 ,
262: p_s_admission_process_type IN VARCHAR2 ,
263: p_message_name OUT NOCOPY VARCHAR2 )
262: p_s_admission_process_type IN VARCHAR2 ,
263: p_message_name OUT NOCOPY VARCHAR2 )
264: RETURN BOOLEAN AS
265: BEGIN -- admp_val_ads_item
266: -- Validate the IGS_AD_PS_APPL_INST.adm_doc_status,
267: -- It must be open and valid
268: DECLARE
269: v_message_name VARCHAR2(30);
270: v_s_adm_doc_status igs_ad_doc_stat.s_adm_doc_status%TYPE;
345: p_cond_offer_doc_allowed IN VARCHAR2,
346: p_message_name OUT NOCOPY VARCHAR2 )
347: RETURN BOOLEAN AS
348: BEGIN -- admp_val_ads_aos
349: -- This module validates IGS_AD_PS_APPL_INST.adm_doc_status against
350: -- IGS_AD_PS_APPL_INST.adm_outcome_status.
351: -- Validations are:
352: -- If the admission documentation status is applicable for the admission
353: -- application, then it cannot be pending if an offer has been made.
346: p_message_name OUT NOCOPY VARCHAR2 )
347: RETURN BOOLEAN AS
348: BEGIN -- admp_val_ads_aos
349: -- This module validates IGS_AD_PS_APPL_INST.adm_doc_status against
350: -- IGS_AD_PS_APPL_INST.adm_outcome_status.
351: -- Validations are:
352: -- If the admission documentation status is applicable for the admission
353: -- application, then it cannot be pending if an offer has been made.
354: -- The admission documentation status cannot be incomplete, unsatisfactory
429: IGS_GE_MSG_STACK.ADD;
430: App_Exception.Raise_Exception;
431: END admp_val_ads_aos;
432: --
433: -- Validate the IGS_AD_PS_APPL_INST.adm_offer_dfrmnt_status.
434: FUNCTION admp_val_acai_aods(
435: p_person_id IN NUMBER ,
436: p_admission_appl_number IN NUMBER ,
437: p_nominated_course_cd IN VARCHAR2 ,
444: p_s_admission_process_type IN VARCHAR2 ,
445: p_message_name OUT NOCOPY VARCHAR2 )
446: RETURN BOOLEAN AS
447: BEGIN -- admp_val_acai_aods
448: -- Validate the IGS_AD_PS_APPL_INST.adm_offer_dfrmnt_status.
449: DECLARE
450: v_s_adm_offer_dfrmnt_status
451: IGS_AD_PS_APPL_INST.adm_offer_dfrmnt_status%TYPE;
452: v_old_s_adm_dfrmnt_status
447: BEGIN -- admp_val_acai_aods
448: -- Validate the IGS_AD_PS_APPL_INST.adm_offer_dfrmnt_status.
449: DECLARE
450: v_s_adm_offer_dfrmnt_status
451: IGS_AD_PS_APPL_INST.adm_offer_dfrmnt_status%TYPE;
452: v_old_s_adm_dfrmnt_status
453: IGS_AD_PS_APPL_INST.adm_offer_dfrmnt_status%TYPE;
454: v_s_adm_offer_resp_status
455: IGS_AD_PS_APPL_INST.adm_offer_resp_status%TYPE;
449: DECLARE
450: v_s_adm_offer_dfrmnt_status
451: IGS_AD_PS_APPL_INST.adm_offer_dfrmnt_status%TYPE;
452: v_old_s_adm_dfrmnt_status
453: IGS_AD_PS_APPL_INST.adm_offer_dfrmnt_status%TYPE;
454: v_s_adm_offer_resp_status
455: IGS_AD_PS_APPL_INST.adm_offer_resp_status%TYPE;
456: v_message_name VARCHAR2(30);
457: BEGIN
451: IGS_AD_PS_APPL_INST.adm_offer_dfrmnt_status%TYPE;
452: v_old_s_adm_dfrmnt_status
453: IGS_AD_PS_APPL_INST.adm_offer_dfrmnt_status%TYPE;
454: v_s_adm_offer_resp_status
455: IGS_AD_PS_APPL_INST.adm_offer_resp_status%TYPE;
456: v_message_name VARCHAR2(30);
457: BEGIN
458: -- Set the default message number
459: p_message_name := NULL;
497: IGS_GE_MSG_STACK.ADD;
498: App_Exception.Raise_Exception;
499: END admp_val_acai_aods;
500: --
501: -- Validate the IGS_AD_PS_APPL_INST.adm_offer_dfrmnt_status.
502: FUNCTION admp_val_aods_item(
503: p_person_id IN NUMBER ,
504: p_admission_appl_number IN NUMBER ,
505: p_nominated_course_cd IN VARCHAR2 ,
510: p_deferral_allowed IN VARCHAR2,
511: p_message_name OUT NOCOPY VARCHAR2 )
512: RETURN BOOLEAN AS
513: BEGIN -- admp_val_aods_item
514: -- This module validates the IGS_AD_PS_APPL_INST.adm_offer_dfrmnt_status.
515: -- Perform item level validations only.
516: -- Validations are:
517: -- ? The adm_offer_dfrmnt_status must be open. (AODS01)
518: -- ? If the deferment is not allowed for the admission application,
653: p_s_adm_offer_resp_status IN VARCHAR2 ,
654: p_message_name OUT NOCOPY VARCHAR2 )
655: RETURN BOOLEAN AS
656: BEGIN -- admp_val_aods_aors
657: -- This module validates IGS_AD_PS_APPL_INST.adm_offer_dfrmnt_status
658: -- against IGS_AD_PS_APPL_INST.adm_offer_resp_status.
659: -- Validations are:
660: -- * The admission offer deferment status must have a system value
661: -- of not-applicable when the admission offer response status is
654: p_message_name OUT NOCOPY VARCHAR2 )
655: RETURN BOOLEAN AS
656: BEGIN -- admp_val_aods_aors
657: -- This module validates IGS_AD_PS_APPL_INST.adm_offer_dfrmnt_status
658: -- against IGS_AD_PS_APPL_INST.adm_offer_resp_status.
659: -- Validations are:
660: -- * The admission offer deferment status must have a system value
661: -- of not-applicable when the admission offer response status is
662: -- pending, lapsed or not-applicable. (AODS05)
709: IGS_GE_MSG_STACK.ADD;
710: App_Exception.Raise_Exception;
711: END admp_val_aods_aors;
712: --
713: -- Validate the IGS_AD_PS_APPL_INST.late_adm_fee_status.
714: FUNCTION admp_val_acai_lafs(
715: p_late_adm_fee_status IN VARCHAR2 ,
716: p_late_appl_allowed IN VARCHAR2,
717: p_late_fees_required IN VARCHAR2,
731: p_adm_cndtnl_offer_status IN VARCHAR2 ,
732: p_message_name OUT NOCOPY VARCHAR2 )
733: RETURN BOOLEAN AS
734: BEGIN -- admp_val_acai_lafs
735: -- Validate the IGS_AD_PS_APPL_INST.late_adm_fee_status.
736: DECLARE
737: v_s_late_adm_fee_status igs_ad_fee_stat.s_adm_fee_status%TYPE;
738: v_s_adm_outcome_status igs_ad_ou_stat.s_adm_outcome_status%TYPE;
739: v_s_adm_cndtnl_offer_status igs_ad_cndnl_ofrstat.s_adm_cndtnl_offer_status%TYPE ;
795: IGS_GE_MSG_STACK.ADD;
796: App_Exception.Raise_Exception;
797: END admp_val_acai_lafs;
798: --
799: -- Validate the IGS_AD_PS_APPL_INST.late_adm_fee_status.
800: FUNCTION admp_val_lafs_item(
801: p_late_adm_fee_status IN VARCHAR2 ,
802: p_late_appl_allowed IN VARCHAR2,
803: p_message_name OUT NOCOPY VARCHAR2 )
802: p_late_appl_allowed IN VARCHAR2,
803: p_message_name OUT NOCOPY VARCHAR2 )
804: RETURN BOOLEAN AS
805: BEGIN -- admp_val_lafs_item
806: -- Validate the IGS_AD_PS_APPL_INST.late_adm_fee_status.
807: -- Perform item level validations only.
808: -- Validations are:
809: -- * The late_adm_fee_status must be open. (LAFS01)
810: -- * If late applications are not allowed the late_adm_fee_status
901: p_message_name OUT NOCOPY VARCHAR2
902: )
903: RETURN BOOLEAN AS
904: BEGIN -- admp_val_lafs_coo
905: -- This module validates the IGS_AD_PS_APPL_INST.late_adm_fee_status
906: -- against the course offering option details of the admission course
907: -- application instance.
908: -- Validations are :
909: -- If late applications are allowed and the application is late and late
1000: p_cond_offer_fee_allowed IN VARCHAR2,
1001: p_message_name OUT NOCOPY VARCHAR2 )
1002: RETURN BOOLEAN AS
1003: BEGIN -- admp_val_lafs_aos
1004: -- This module validates IGS_AD_PS_APPL_INST.adm_late_fee_status
1005: -- against IGS_AD_PS_APPL_INST.adm_outcome_status.
1006: -- Validations are:
1007: -- * The late admission fee status cannot be pending
1008: -- if an offer has been made. (LAFS04)
1001: p_message_name OUT NOCOPY VARCHAR2 )
1002: RETURN BOOLEAN AS
1003: BEGIN -- admp_val_lafs_aos
1004: -- This module validates IGS_AD_PS_APPL_INST.adm_late_fee_status
1005: -- against IGS_AD_PS_APPL_INST.adm_outcome_status.
1006: -- Validations are:
1007: -- * The late admission fee status cannot be pending
1008: -- if an offer has been made. (LAFS04)
1009: -- * An offer cannot be made if late fees have been
1055: IGS_GE_MSG_STACK.ADD;
1056: App_Exception.Raise_Exception;
1057: END admp_val_lafs_aos;
1058: --
1059: -- Validate the IGS_AD_PS_APPL_INST.adm_offer_resp_status.
1060: FUNCTION admp_val_acai_aors(
1061: p_person_id IN NUMBER ,
1062: p_admission_appl_number IN NUMBER ,
1063: p_nominated_course_cd IN VARCHAR2 ,
1086: p_attent_other_inst_cd IN VARCHAR2 -- igsm
1087: )
1088: RETURN BOOLEAN AS
1089: BEGIN -- admp_val_acai_aors
1090: -- Validate the IGS_AD_PS_APPL_INST.adm_offer_resp_status.
1091: DECLARE
1092: cst_accepted CONSTANT VARCHAR2(10) := 'ACCEPTED';
1093: cst_form CONSTANT VARCHAR2(5) := 'FORM';
1094: cst_trg_br CONSTANT VARCHAR2(7) := 'TRG_BR';
1214: IGS_GE_MSG_STACK.ADD;
1215: App_Exception.Raise_Exception;
1216: END admp_val_acai_aors;
1217: --
1218: -- Validate the IGS_AD_PS_APPL_INST.adm_offer_resp_status.
1219: FUNCTION admp_val_aors_item(
1220: p_person_id IN NUMBER ,
1221: p_admission_appl_number IN NUMBER ,
1222: p_nominated_course_cd IN VARCHAR2 ,
1233:
1234: )
1235: RETURN BOOLEAN AS
1236: BEGIN -- admp_val_aors_item
1237: -- This module validates the IGS_AD_PS_APPL_INST.adm_offer_resp_status.
1238: -- Perform item level validations only.
1239: -- Validations are:
1240: -- * The adm_offer_resp_status must be open. (AORS01)
1241: -- * The admission offer response status cannot be set to pending for
1456: p_adm_outcome_status_auth_dt IN DATE ,
1457: p_message_name OUT NOCOPY VARCHAR2 )
1458: RETURN BOOLEAN AS
1459: BEGIN -- admp_val_aors_aos
1460: -- This module validates the IGS_AD_PS_APPL_INST.adm_offer_resp_status
1461: -- against IGS_AD_PS_APPL_INST.adm_outcome_status.
1462: -- Validations are:
1463: -- ? The admission offer response status cannot be pending if an offer
1464: -- has not been made. (AORS11)
1457: p_message_name OUT NOCOPY VARCHAR2 )
1458: RETURN BOOLEAN AS
1459: BEGIN -- admp_val_aors_aos
1460: -- This module validates the IGS_AD_PS_APPL_INST.adm_offer_resp_status
1461: -- against IGS_AD_PS_APPL_INST.adm_outcome_status.
1462: -- Validations are:
1463: -- ? The admission offer response status cannot be pending if an offer
1464: -- has not been made. (AORS11)
1465: -- ? The admission offer response status cannot be accepted, rejected
1538: IGS_GE_MSG_STACK.ADD;
1539: App_Exception.Raise_Exception;
1540: END admp_val_aors_aos;
1541: --
1542: -- Validate the IGS_AD_PS_APPL_INST.adm_outcome_status.
1543: FUNCTION admp_val_acai_aos(
1544: p_person_id IN NUMBER ,
1545: p_admission_appl_number IN NUMBER ,
1546: p_nominated_course_cd IN VARCHAR2 ,
1587: p_called_from IN VARCHAR2 ,
1588: p_message_name OUT NOCOPY VARCHAR2 )
1589: RETURN BOOLEAN AS
1590: BEGIN -- admp_val_acai_aos
1591: -- Validate the IGS_AD_PS_APPL_INST.adm_outcome_status
1592: DECLARE
1593: cst_form CONSTANT VARCHAR2(6) := 'FORM';
1594: cst_trg_br CONSTANT VARCHAR2(6) := 'TRG_BR';
1595: cst_trg_as CONSTANT VARCHAR2(6) := 'TRG_AS';
1770: IGS_GE_MSG_STACK.ADD;
1771: App_Exception.Raise_Exception;
1772: END admp_val_acai_aos;
1773: --
1774: -- Validate the IGS_AD_PS_APPL_INST.adm_outcome_status.
1775: FUNCTION admp_val_aos_item(
1776: p_adm_outcome_status IN VARCHAR2 ,
1777: p_old_adm_outcome_status IN VARCHAR2 ,
1778: p_adm_fee_status IN VARCHAR2 ,
1786: p_acai_sequence_number IN NUMBER ,
1787: p_message_name OUT NOCOPY VARCHAR2 )
1788: RETURN BOOLEAN AS
1789: BEGIN -- admp_val_aos_item
1790: -- This module validates the IGS_AD_PS_APPL_INST.adm_outcome_status.
1791: -- The adm_outcome_status must be open. (AOS01)
1792: -- The admission outcome status may not be set to a value other than pending
1793: -- when setting of the admission outcome status is not allowed for the
1794: -- admission application. (AOS02)
1968: App_Exception.Raise_Exception;
1969: END;
1970: END admp_val_aos_closed;
1971: --
1972: -- Validate the IGS_AD_PS_APPL_INST.adm_outcome_status.
1973: FUNCTION admp_val_aos_status(
1974: p_s_adm_outcome_status IN VARCHAR2 ,
1975: p_old_s_adm_outcome_status IN VARCHAR2 ,
1976: p_s_adm_doc_status IN VARCHAR2 ,
1983: p_fees_required IN VARCHAR2,
1984: p_message_name OUT NOCOPY VARCHAR2 )
1985: RETURN BOOLEAN AS
1986: BEGIN -- admp_val_aos_status
1987: -- This module validates the IGS_AD_PS_APPL_INST.adm_outcome_status
1988: -- against
1989: -- other statuses in IGS_AD_PS_APPL_INST.
1990: -- Validations are:
1991: -- If a documentation conditional offer is being made, then the admission
1985: RETURN BOOLEAN AS
1986: BEGIN -- admp_val_aos_status
1987: -- This module validates the IGS_AD_PS_APPL_INST.adm_outcome_status
1988: -- against
1989: -- other statuses in IGS_AD_PS_APPL_INST.
1990: -- Validations are:
1991: -- If a documentation conditional offer is being made, then the admission
1992: -- documentation status must be incomplete. (AOS24).
1993: -- If a fee conditional offer is being made, then the admission application
2051: IGS_GE_MSG_STACK.ADD;
2052: App_Exception.Raise_Exception;
2053: END admp_val_aos_status;
2054: --
2055: -- Validate the IGS_AD_PS_APPL_INST.adm_outcome_status.
2056: FUNCTION admp_val_acai_otcome(
2057: p_person_id IN NUMBER ,
2058: p_admission_appl_number IN NUMBER ,
2059: p_nominated_course_cd IN VARCHAR2 ,
2085: p_called_from IN VARCHAR2 ,
2086: p_message_name OUT NOCOPY VARCHAR2 )
2087: RETURN BOOLEAN AS
2088: BEGIN -- admp_val_acai_otcome
2089: -- Validate the IGS_AD_PS_APPL_INST.adm_outcome_status.
2090: -- Validations are -
2091: -- * The outcome cannot be set to pending for a non-preference application for
2092: -- which an outcome letter has been created. (This is with the exception of
2093: -- reconsideration and deferment processing). (AOS03)
2154: aal.admission_appl_number = p_admission_appl_number AND
2155: aal.correspondence_type = cst_outcome_lt;
2156: CURSOR c_acai_aos IS
2157: SELECT 'x'
2158: FROM IGS_AD_PS_APPL_INST acai,
2159: IGS_AD_OU_STAT aos
2160: WHERE acai.person_id = p_person_id AND
2161: acai.admission_appl_number = p_admission_appl_number AND
2162: (acai.nominated_course_cd <> p_nominated_course_cd OR
2194: crv.course_type = cty.course_type;
2195:
2196: --------------------------------------------SUB-FUNCTION-----------------------
2197: FUNCTION admpl_chk_res_or_def(
2198: p_person_id IGS_AD_PS_APPL_INST.person_id%TYPE,
2199: p_admission_appl_number IGS_AD_PS_APPL_INST.admission_appl_number%TYPE,
2200: p_nominated_course_cd IGS_AD_PS_APPL_INST.nominated_course_cd%TYPE,
2201: p_acai_sequence_number IGS_AD_PS_APPL_INST.sequence_number%TYPE,
2202: v_defermt_processing IN OUT NOCOPY BOOLEAN,
2195:
2196: --------------------------------------------SUB-FUNCTION-----------------------
2197: FUNCTION admpl_chk_res_or_def(
2198: p_person_id IGS_AD_PS_APPL_INST.person_id%TYPE,
2199: p_admission_appl_number IGS_AD_PS_APPL_INST.admission_appl_number%TYPE,
2200: p_nominated_course_cd IGS_AD_PS_APPL_INST.nominated_course_cd%TYPE,
2201: p_acai_sequence_number IGS_AD_PS_APPL_INST.sequence_number%TYPE,
2202: v_defermt_processing IN OUT NOCOPY BOOLEAN,
2203: v_reconsideratn_processing IN OUT NOCOPY BOOLEAN)
2196: --------------------------------------------SUB-FUNCTION-----------------------
2197: FUNCTION admpl_chk_res_or_def(
2198: p_person_id IGS_AD_PS_APPL_INST.person_id%TYPE,
2199: p_admission_appl_number IGS_AD_PS_APPL_INST.admission_appl_number%TYPE,
2200: p_nominated_course_cd IGS_AD_PS_APPL_INST.nominated_course_cd%TYPE,
2201: p_acai_sequence_number IGS_AD_PS_APPL_INST.sequence_number%TYPE,
2202: v_defermt_processing IN OUT NOCOPY BOOLEAN,
2203: v_reconsideratn_processing IN OUT NOCOPY BOOLEAN)
2204: RETURN BOOLEAN
2197: FUNCTION admpl_chk_res_or_def(
2198: p_person_id IGS_AD_PS_APPL_INST.person_id%TYPE,
2199: p_admission_appl_number IGS_AD_PS_APPL_INST.admission_appl_number%TYPE,
2200: p_nominated_course_cd IGS_AD_PS_APPL_INST.nominated_course_cd%TYPE,
2201: p_acai_sequence_number IGS_AD_PS_APPL_INST.sequence_number%TYPE,
2202: v_defermt_processing IN OUT NOCOPY BOOLEAN,
2203: v_reconsideratn_processing IN OUT NOCOPY BOOLEAN)
2204: RETURN BOOLEAN
2205: AS
2334: END IF;
2335: END IF;
2336: ELSE -- p_pref_allowed = 'N'
2337: -- Cannot have an admission outcome letter if setting this instance to
2338: -- pending means that there are no IGS_AD_PS_APPL_INST with an outcome
2339: -- in the
2340: -- admission application (exclude this instance in select)
2341: OPEN c_acai_aos;
2342: FETCH c_acai_aos INTO v_check;
2650: IGS_GE_MSG_STACK.ADD;
2651: App_Exception.Raise_Exception;
2652: END admp_val_acai_otcome;
2653: --
2654: -- This module validates multiple IGS_AD_PS_APPL_INST offers.
2655: FUNCTION admp_val_offer_mult(
2656: p_person_id IN NUMBER ,
2657: p_admission_appl_number IN NUMBER ,
2658: p_nominated_course_cd IN VARCHAR2 ,
2667: p_multi_offer_allowed IN VARCHAR2,
2668: p_multi_offer_limit IN NUMBER ,
2669: p_message_name OUT NOCOPY VARCHAR2 )
2670: RETURN BOOLEAN AS
2671: BEGIN -- Validates multiple IGS_AD_PS_APPL_INST offers
2672: -- Validations are
2673: -- * If the admission application does not allow multiple offers then only one
2674: -- admission
2675: -- course application instance can be currently offered to the IGS_PE_PERSON within
2694: cst_rejected CONSTANT VARCHAR2(10) := 'REJECTED';
2695: cst_not_applic CONSTANT VARCHAR2(10) := 'NOT-APPLIC';
2696: CURSOR c_acaiv_aos IS
2697: SELECT 'x'
2698: FROM IGS_AD_PS_APPL_INST acaiv, /* References to IGS_AD_PS_APPL_INST_APLINST_V replaced with IGS_AD_PS_APPL_INST Bug 3150054 */
2699: IGS_AD_OU_STAT aos,
2700: IGS_AD_OFR_RESP_STAT aors
2701: WHERE acaiv.person_id = p_person_id AND
2702: (acaiv.admission_appl_number <> p_admission_appl_number OR
2710: acaiv.adm_outcome_status = aos.adm_outcome_status AND
2711: acaiv.adm_offer_resp_status = aors.adm_offer_resp_status;
2712: CURSOR c_cnt_acaiv_aa_aos IS
2713: SELECT count(*)
2714: FROM IGS_AD_PS_APPL_INST acaiv, /* References to IGS_AD_PS_APPL_INST_APLINST_V replaced with IGS_AD_PS_APPL_INST Bug 3150054 */
2715: IGS_AD_APPL aa,
2716: IGS_AD_OU_STAT aos,
2717: IGS_AD_OFR_RESP_STAT aors
2718: WHERE acaiv.person_id = p_person_id AND
2823: v_s_adm_outcome_status igs_ad_ou_stat.s_adm_outcome_status%TYPE;
2824: v_s_adm_offer_resp_status igs_ad_ofr_resp_stat.s_adm_offer_resp_status%TYPE;
2825: CURSOR c_aaaa IS
2826: SELECT 'x'
2827: FROM IGS_AD_PS_APPL_INST acaiv, /* References to IGS_AD_PS_APPL_INST_APLINST_V replaced with IGS_AD_PS_APPL_INST Bug 3150054 */
2828: IGS_AD_APPL aa,
2829: IGS_AD_OU_STAT aos,
2830: IGS_AD_OFR_RESP_STAT aors
2831: WHERE acaiv.person_id = p_person_id AND
2949: IGS_GE_MSG_STACK.ADD;
2950: App_Exception.Raise_Exception;
2951: END admp_val_aos_update;
2952: --
2953: -- Validate the IGS_AD_PS_APPL_INST.adm_cndtnl_offer_status.
2954: FUNCTION admp_val_acai_acos(
2955: p_adm_cndtnl_offer_status IN VARCHAR2 ,
2956: p_old_adm_cndtnl_offer_status IN VARCHAR2 ,
2957: p_adm_outcome_status IN VARCHAR2 ,
2965: p_cond_offer_doc_allowed IN VARCHAR2,
2966: p_message_name OUT NOCOPY VARCHAR2 )
2967: RETURN BOOLEAN AS
2968: BEGIN --admp_val_acai_acos
2969: --Validate the IGS_AD_PS_APPL_INST.adm_cndtnl_offer_status
2970: DECLARE
2971: v_message_name VARCHAR2(30);
2972: v_s_adm_cndtnl_offer_status igs_ad_cndnl_ofrstat.s_adm_cndtnl_offer_status%TYPE;
2973: v_old_s_adm_cndtnl_offer_stat igs_ad_cndnl_ofrstat.s_adm_cndtnl_offer_status%TYPE;
3021: IGS_GE_MSG_STACK.ADD;
3022: App_Exception.Raise_Exception;
3023: END admp_val_acai_acos;
3024: --
3025: -- Validate the IGS_AD_PS_APPL_INST.adm_cndtnl_offer_status.
3026: FUNCTION admp_val_acos_item(
3027: p_adm_cndtnl_offer_status IN VARCHAR2 ,
3028: p_adm_fee_status IN VARCHAR2 ,
3029: p_fees_required IN VARCHAR2 ,
3032: p_cond_offer_doc_allowed IN VARCHAR2 ,
3033: p_message_name OUT NOCOPY VARCHAR2 )
3034: RETURN BOOLEAN AS
3035: BEGIN --admp_val_acos_item
3036: --Validate the IGS_AD_PS_APPL_INST.adm_cndtnl_offer_status.
3037: --The IGS_AD_CNDNL_OFRSTAT must be open. (ACOS01)
3038: --The conditional offer status must be not-applicable when conditional
3039: --offers are not allowed. (ACOS02)
3040: --If fees are required for the application, then fees must not be outstanding
3129: IGS_GE_MSG_STACK.ADD;
3130: App_Exception.Raise_Exception;
3131: END admp_val_acos_closed;
3132: --
3133: -- Validate the IGS_AD_PS_APPL_INST.adm_cndtnl_offer_status.
3134: FUNCTION admp_val_acos_status(
3135: p_s_adm_cndtnl_offer_status IN VARCHAR2 ,
3136: p_old_s_adm_cndtnl_offer_sts IN VARCHAR2 ,
3137: p_s_adm_outcome_status IN VARCHAR2 ,
3140: p_late_appl_allowed IN VARCHAR2,
3141: p_message_name OUT NOCOPY VARCHAR2 )
3142: RETURN BOOLEAN AS
3143: BEGIN --admp_val_acos_status
3144: --This module validates the IGS_AD_PS_APPL_INST.adm_cndtnl_offer_status
3145: --against other IGS_AD_PS_APPL_INST statuses.
3146: DECLARE
3147: BEGIN
3148: --set the default message number
3141: p_message_name OUT NOCOPY VARCHAR2 )
3142: RETURN BOOLEAN AS
3143: BEGIN --admp_val_acos_status
3144: --This module validates the IGS_AD_PS_APPL_INST.adm_cndtnl_offer_status
3145: --against other IGS_AD_PS_APPL_INST statuses.
3146: DECLARE
3147: BEGIN
3148: --set the default message number
3149: p_message_name := NULL;
3237: -- This function validates the change in the offer deferment status
3238: -- The offer deferment status can be moved to CONFIRM only when the
3239: -- status is APPROVED.
3240: DECLARE
3241: CURSOR cur_offer_dfrmnt_status(p_person_id igs_ad_ps_appl_inst.person_id%TYPE ,
3242: p_admission_appl_number igs_ad_ps_appl_inst.admission_appl_number%TYPE ,
3243: p_nominated_course_cd igs_ad_ps_appl_inst.nominated_course_cd%TYPE ,
3244: p_sequence_number igs_ad_ps_appl_inst.sequence_number%TYPE) IS
3245: SELECT adm_offer_dfrmnt_status
3238: -- The offer deferment status can be moved to CONFIRM only when the
3239: -- status is APPROVED.
3240: DECLARE
3241: CURSOR cur_offer_dfrmnt_status(p_person_id igs_ad_ps_appl_inst.person_id%TYPE ,
3242: p_admission_appl_number igs_ad_ps_appl_inst.admission_appl_number%TYPE ,
3243: p_nominated_course_cd igs_ad_ps_appl_inst.nominated_course_cd%TYPE ,
3244: p_sequence_number igs_ad_ps_appl_inst.sequence_number%TYPE) IS
3245: SELECT adm_offer_dfrmnt_status
3246: FROM igs_ad_ps_appl_inst
3239: -- status is APPROVED.
3240: DECLARE
3241: CURSOR cur_offer_dfrmnt_status(p_person_id igs_ad_ps_appl_inst.person_id%TYPE ,
3242: p_admission_appl_number igs_ad_ps_appl_inst.admission_appl_number%TYPE ,
3243: p_nominated_course_cd igs_ad_ps_appl_inst.nominated_course_cd%TYPE ,
3244: p_sequence_number igs_ad_ps_appl_inst.sequence_number%TYPE) IS
3245: SELECT adm_offer_dfrmnt_status
3246: FROM igs_ad_ps_appl_inst
3247: WHERE person_id = p_person_id AND
3240: DECLARE
3241: CURSOR cur_offer_dfrmnt_status(p_person_id igs_ad_ps_appl_inst.person_id%TYPE ,
3242: p_admission_appl_number igs_ad_ps_appl_inst.admission_appl_number%TYPE ,
3243: p_nominated_course_cd igs_ad_ps_appl_inst.nominated_course_cd%TYPE ,
3244: p_sequence_number igs_ad_ps_appl_inst.sequence_number%TYPE) IS
3245: SELECT adm_offer_dfrmnt_status
3246: FROM igs_ad_ps_appl_inst
3247: WHERE person_id = p_person_id AND
3248: admission_appl_number = p_admission_appl_number AND
3242: p_admission_appl_number igs_ad_ps_appl_inst.admission_appl_number%TYPE ,
3243: p_nominated_course_cd igs_ad_ps_appl_inst.nominated_course_cd%TYPE ,
3244: p_sequence_number igs_ad_ps_appl_inst.sequence_number%TYPE) IS
3245: SELECT adm_offer_dfrmnt_status
3246: FROM igs_ad_ps_appl_inst
3247: WHERE person_id = p_person_id AND
3248: admission_appl_number = p_admission_appl_number AND
3249: nominated_course_cd = p_nominated_course_cd AND
3250: sequence_number = p_sequence_number ;
3249: nominated_course_cd = p_nominated_course_cd AND
3250: sequence_number = p_sequence_number ;
3251:
3252: v_new_ofr_dfrmnt_status igs_ad_ofrdfrmt_stat.s_adm_offer_dfrmnt_status%TYPE;
3253: v_ofr_dfrmnt_status igs_ad_ps_appl_inst.adm_offer_dfrmnt_status%TYPE;
3254: v_s_ofr_dfrmnt_status igs_ad_ofrdfrmt_stat.s_adm_offer_dfrmnt_status%TYPE;
3255: BEGIN
3256: v_new_ofr_dfrmnt_status := igs_ad_gen_008.admp_get_saods(p_offer_deferment_status);
3257: IF v_new_ofr_dfrmnt_status = 'CONFIRM' THEN
3288: p_adm_outcome_status IN VARCHAR2 ,
3289: p_message_name OUT NOCOPY VARCHAR2 )
3290: RETURN BOOLEAN AS
3291: BEGIN -- admp_val_acai_ais
3292: -- Validate the IGS_AD_PS_APPL_INST.appl_inst_status
3293: DECLARE
3294: v_s_appl_inst_status IGS_AD_PS_APPL_INST.appl_inst_status%TYPE;
3295: v_s_adm_outcome_status IGS_AD_OU_STAT.s_adm_outcome_status%TYPE;
3296: v_message_name VARCHAR2(30);
3290: RETURN BOOLEAN AS
3291: BEGIN -- admp_val_acai_ais
3292: -- Validate the IGS_AD_PS_APPL_INST.appl_inst_status
3293: DECLARE
3294: v_s_appl_inst_status IGS_AD_PS_APPL_INST.appl_inst_status%TYPE;
3295: v_s_adm_outcome_status IGS_AD_OU_STAT.s_adm_outcome_status%TYPE;
3296: v_message_name VARCHAR2(30);
3297: BEGIN
3298: -- Set the default message number
3343: p_ais_reason IN VARCHAR2,
3344: p_message_name OUT NOCOPY VARCHAR2 )
3345: RETURN BOOLEAN AS
3346: -- admp_val_ais_item
3347: -- This module validates the IGS_AD_PS_APPL_INST.appl_inst_status.
3348: -- Validations are:
3349: -- The appl_inst_status must be open.
3350: -- If the appl_inst_status is present then the ais_reason should also be present
3351: