[Home] [Help]
780: -- HECS payment_option and associated fields.
781: DECLARE
782: CURSOR c_ghpo_details IS
783: SELECT ghpo.s_hecs_payment_type
784: FROM IGS_FI_HECS_PAY_OPTN hpo,
785: IGS_FI_GOV_HEC_PA_OP ghpo
786: WHERE hpo.hecs_payment_option = p_hecs_payment_option AND
787: ghpo.govt_hecs_payment_option = hpo.govt_hecs_payment_option;
788: v_ghpo_rec c_ghpo_details%ROWTYPE;
1052: p_return_type OUT NOCOPY VARCHAR2 )
1053: RETURN BOOLEAN AS
1054: BEGIN
1055: DECLARE
1056: v_govt_hecs_payment_option IGS_FI_HECS_PAY_OPTN.govt_hecs_payment_option%TYPE;
1057: v_govt_citizenship_cd IGS_ST_CITIZENSHP_CD.govt_citizenship_cd%TYPE;
1058: v_govt_perm_resident_cd
1059: IGS_PE_PERM_RES_CD.govt_perm_resident_cd%TYPE DEFAULT NULL;
1060: v_outside_aus_res_ind IGS_EN_STDNT_PS_HECS_OPTION_V.outside_aus_res_ind%TYPE;
1060: v_outside_aus_res_ind IGS_EN_STDNT_PS_HECS_OPTION_V.outside_aus_res_ind%TYPE;
1061: v_exit BOOLEAN DEFAULT FALSE;
1062: CURSOR c_ghpo IS
1063: SELECT govt_hecs_payment_option
1064: FROM IGS_FI_HECS_PAY_OPTN
1065: WHERE hecs_payment_option = p_hecs_payment_option AND
1066: govt_hecs_payment_option IS NOT NULL;
1067: CURSOR c_gcitiz IS
1068: SELECT PS.start_dt,
1090: SCHOV.end_dt,
1091: SCHOV.outside_aus_res_ind,
1092: HPO.govt_hecs_payment_option
1093: FROM IGS_EN_STDNT_PS_HECS_OPTION_V SCHOV,
1094: IGS_FI_HECS_PAY_OPTN HPO
1095: WHERE SCHOV.person_id = p_person_id AND
1096: SCHOV.hecs_payment_option =
1097: HPO.hecs_payment_option AND
1098: HPO.govt_hecs_payment_option IS NOT NULL
1130: END;
1131: END enrpl_chk_whether_to_validate;
1132: FUNCTION enrpl_do_validations (
1133: p_outside_aus_res_ind IGS_EN_STDNT_PS_HECS_OPTION_V.outside_aus_res_ind%TYPE,
1134: p_govt_hecs_payment_option IGS_FI_HECS_PAY_OPTN.govt_hecs_payment_option%TYPE,
1135: p_govt_citizenship_cd IGS_ST_CITIZENSHP_CD.govt_citizenship_cd%TYPE,
1136: p_govt_perm_resident_cd IGS_PE_PERM_RES_CD.govt_perm_resident_cd%TYPE)
1137: RETURN BOOLEAN AS
1138: BEGIN
1168: END;
1169: END enrpl_do_validations;
1170: BEGIN
1171: -- This is the main module which validates the student_course_hecs_
1172: -- option_v.IGS_FI_HECS_PAY_OPTN against the IGS_PE_STATISTICS.
1173: -- IGS_ST_CITIZENSHP_CD and the IGS_PE_STATISTICS.IGS_PE_PERM_RES_CD.
1174: p_message_name := null;
1175: -- validating the input parameters
1176: IF p_person_id IS NULL THEN
1181: IF p_scho_start_dt IS NULL THEN
1182: RETURN TRUE;
1183: END IF;
1184: -- retrieving the govt. value for the student_course_
1185: -- hecs_option_v.IGS_FI_HECS_PAY_OPTN
1186: OPEN c_ghpo;
1187: FETCH c_ghpo INTO v_govt_hecs_payment_option;
1188: -- exit successfully if a record isn't found
1189: -- therefore, no validation is required
1241: FETCH c_gprcd INTO v_govt_perm_resident_cd;
1242: CLOSE c_gprcd;
1243: -- retrieving the visa indicators for the student_course_
1244: -- hecs_option_v and the govt. value for the
1245: -- IGS_EN_STDNT_PS_HECS_OPTION_V.IGS_FI_HECS_PAY_OPTN
1246: -- in which more than one record may be found
1247: FOR v_scho_record IN c_hpo LOOP
1248: -- execute the rountine which checks whether
1249: -- furhter validation is required.
1415: END;
1416: END enrpl_do_validations;
1417: BEGIN
1418: -- This is the main module which validates the student_course_hecs_
1419: -- option_v.IGS_FI_HECS_PAY_OPTN against the IGS_PE_STATISTICS.
1420: -- IGS_ST_CITIZENSHP_CD.
1421: p_message_name := null;
1422: -- validating the input parameters
1423: IF p_person_id IS NULL THEN
1479: FETCH c_prc INTO v_govt_perm_resident_cd;
1480: CLOSE c_prc;
1481: -- retrieving the visa indicators for the student_course_
1482: -- hecs_option_v and the govt. value for the
1483: -- IGS_EN_STDNT_PS_HECS_OPTION_V.IGS_FI_HECS_PAY_OPTN
1484: -- in which more than one record may be found
1485: FOR v_schov_rec IN c_schov LOOP
1486: -- execute the rountine which checks whether
1487: -- further validation is required.
1546: p_return_type OUT NOCOPY VARCHAR2 )
1547: RETURN BOOLEAN AS
1548: BEGIN
1549: DECLARE
1550: v_govt_hecs_payment_option IGS_FI_HECS_PAY_OPTN.govt_hecs_payment_option%TYPE;
1551: v_govt_course_type IGS_PS_TYPE.govt_course_type%TYPE;
1552: CURSOR c_ghpo IS
1553: SELECT hpo.govt_hecs_payment_option
1554: FROM IGS_FI_HECS_PAY_OPTN hpo
1550: v_govt_hecs_payment_option IGS_FI_HECS_PAY_OPTN.govt_hecs_payment_option%TYPE;
1551: v_govt_course_type IGS_PS_TYPE.govt_course_type%TYPE;
1552: CURSOR c_ghpo IS
1553: SELECT hpo.govt_hecs_payment_option
1554: FROM IGS_FI_HECS_PAY_OPTN hpo
1555: WHERE hpo.hecs_payment_option = p_hecs_payment_option;
1556: CURSOR c_gct IS
1557: SELECT cty.govt_course_type
1558: FROM IGS_EN_STDNT_PS_ATT sca,
1564: crv.version_number = sca.version_number AND
1565: crv.course_type = cty.course_type;
1566: BEGIN
1567: -- This module validates the student_course_hecs_
1568: -- option_v.IGS_FI_HECS_PAY_OPTN against the IGS_PS_VER.
1569: -- IGS_PS_TYPE.
1570: -- Validations are :
1571: -- 2.If the government vaue for the
1572: -- IGS_EN_STDNT_PS_HECS_OPTION_V.IGS_FI_HECS_PAY_OPTN is 26,
1568: -- option_v.IGS_FI_HECS_PAY_OPTN against the IGS_PS_VER.
1569: -- IGS_PS_TYPE.
1570: -- Validations are :
1571: -- 2.If the government vaue for the
1572: -- IGS_EN_STDNT_PS_HECS_OPTION_V.IGS_FI_HECS_PAY_OPTN is 26,
1573: -- then the government value for the IGS_PS_VER.IGS_PS_TYPE for the
1574: -- IGS_EN_STDNT_PS_HECS_OPTION_V.course_cd must be 50 (DEETYA validation 1294).
1575: -- 3.If the government value for the IGS_PS_VER.IGS_PS_TYPE for the
1576: -- IGS_EN_STDNT_PS_HECS_OPTION_V.course_cd is 50, then the government value for
1573: -- then the government value for the IGS_PS_VER.IGS_PS_TYPE for the
1574: -- IGS_EN_STDNT_PS_HECS_OPTION_V.course_cd must be 50 (DEETYA validation 1294).
1575: -- 3.If the government value for the IGS_PS_VER.IGS_PS_TYPE for the
1576: -- IGS_EN_STDNT_PS_HECS_OPTION_V.course_cd is 50, then the government value for
1577: -- the IGS_EN_STDNT_PS_HECS_OPTION_V.IGS_FI_HECS_PAY_OPTN then must be 22,
1578: -- 23, 24, 26, 27, 30, 33, 70or 99 (DEETYA validation 1655).
1579: -- set the default message number
1580: p_message_name := null;
1581: -- retrieving the govt. value for the student_course_
1578: -- 23, 24, 26, 27, 30, 33, 70or 99 (DEETYA validation 1655).
1579: -- set the default message number
1580: p_message_name := null;
1581: -- retrieving the govt. value for the student_course_
1582: -- hecs_option_v.IGS_FI_HECS_PAY_OPTN
1583: OPEN c_ghpo;
1584: FETCH c_ghpo INTO v_govt_hecs_payment_option;
1585: -- exit successfully if a record isn't found
1586: -- therefore, no validation is required
1703: p_return_type OUT NOCOPY VARCHAR2 )
1704: RETURN BOOLEAN AS
1705: BEGIN
1706: DECLARE
1707: v_govt_hecs_payment_option IGS_FI_HECS_PAY_OPTN.govt_hecs_payment_option%TYPE;
1708: v_govt_special_course_type IGS_PS_VER.govt_special_course_type%TYPE;
1709: v_institution_2239 BOOLEAN DEFAULT FALSE;
1710: v_dummy VARCHAR2(1);
1711: CURSOR c_hpo IS
1709: v_institution_2239 BOOLEAN DEFAULT FALSE;
1710: v_dummy VARCHAR2(1);
1711: CURSOR c_hpo IS
1712: SELECT hpo.govt_hecs_payment_option
1713: FROM IGS_FI_HECS_PAY_OPTN hpo
1714: WHERE hpo.hecs_payment_option = p_hecs_payment_option;
1715: CURSOR c_crv IS
1716: SELECT crv.govt_special_course_type
1717: FROM IGS_EN_STDNT_PS_ATT sca,
1819: p_return_type OUT NOCOPY VARCHAR2 )
1820: RETURN BOOLEAN AS
1821: BEGIN
1822: DECLARE
1823: v_govt_hecs_payment_option IGS_FI_HECS_PAY_OPTN.govt_hecs_payment_option%TYPE;
1824: v_govt_citizenship_cd IGS_ST_CITIZENSHP_CD.govt_citizenship_cd%TYPE;
1825: v_return_false BOOLEAN DEFAULT FALSE;
1826: CURSOR c_hpo IS
1827: SELECT hpo.govt_hecs_payment_option
1824: v_govt_citizenship_cd IGS_ST_CITIZENSHP_CD.govt_citizenship_cd%TYPE;
1825: v_return_false BOOLEAN DEFAULT FALSE;
1826: CURSOR c_hpo IS
1827: SELECT hpo.govt_hecs_payment_option
1828: FROM IGS_FI_HECS_PAY_OPTN hpo
1829: WHERE hpo.hecs_payment_option = p_hecs_payment_option AND
1830: hpo.govt_hecs_payment_option IS NOT NULL;
1831: CURSOR c_ps_cic IS
1832: SELECT ps.start_dt,
1851: scho.nz_citizen_less2yr_ind,
1852: scho.nz_citizen_not_res_ind,
1853: hpo.govt_hecs_payment_option
1854: FROM IGS_EN_STDNT_PS_HECS_OPTION_V scho,
1855: IGS_FI_HECS_PAY_OPTN hpo
1856: WHERE scho.person_id = p_person_id AND
1857: scho.hecs_payment_option = hpo.hecs_payment_option AND
1858: hpo.govt_hecs_payment_option IS NOT NULL
1859: ORDER BY scho.start_dt,
1897: IGS_EN_STDNT_PS_HECS_OPTION_V.nz_citizen_less2yr_ind%TYPE,
1898: p_nz_citizen_not_res_ind
1899: IGS_EN_STDNT_PS_HECS_OPTION_V.nz_citizen_not_res_ind%TYPE,
1900: p_govt_hecs_payment_option
1901: IGS_FI_HECS_PAY_OPTN.govt_hecs_payment_option%TYPE,
1902: p_govt_citizenship_cd IGS_ST_CITIZENSHP_CD.govt_citizenship_cd%TYPE,
1903: p_yr_arrival IGS_PE_STATISTICS.yr_arrival%TYPE)
1904: RETURN BOOLEAN
1905: AS
1968: App_Exception.Raise_Exception;
1969: END enrpl_do_validations;
1970: BEGIN
1971: -- This is the main module which validates the student_course_hecs_
1972: -- option_v.IGS_FI_HECS_PAY_OPTN against the IGS_PE_STATISTICS.
1973: -- IGS_ST_CITIZENSHP_CD.
1974: p_message_name := null;
1975: -- validating the input parameters
1976: IF p_person_id IS NULL THEN
1981: IF p_scho_start_dt IS NULL THEN
1982: RETURN TRUE;
1983: END IF;
1984: -- retrieving the govt. value for the student_course_
1985: -- hecs_option_v.IGS_FI_HECS_PAY_OPTN
1986: OPEN c_hpo;
1987: FETCH c_hpo INTO v_govt_hecs_payment_option;
1988: -- exit successfully if a record isn't found
1989: -- therefore, no validation is required
2040: v_govt_citizenship_cd := NULL;
2041: END IF;
2042: -- retrieving the visa indicators for the student_course_
2043: -- hecs_option_v and the govt. value for the
2044: -- IGS_EN_STDNT_PS_HECS_OPTION_V.IGS_FI_HECS_PAY_OPTN
2045: -- in which more than one record may be found
2046: FOR v_scho_rec IN c_scho_hpo LOOP
2047: -- execute the rountine which checks whether
2048: -- furhter validation is required
2112: p_return_type OUT NOCOPY VARCHAR2 )
2113: RETURN BOOLEAN AS
2114: BEGIN
2115: DECLARE
2116: v_govt_hecs_payment_option IGS_FI_HECS_PAY_OPTN.govt_hecs_payment_option%TYPE;
2117: v_govt_citizenship_cd IGS_ST_CITIZENSHP_CD.govt_citizenship_cd%TYPE;
2118: v_govt_perm_resident_cd
2119: IGS_PE_PERM_RES_CD.govt_perm_resident_cd%TYPE DEFAULT NULL;
2120: v_rec_found BOOLEAN DEFAULT FALSE;
2122: v_scho_rec_found BOOLEAN DEFAULT FALSE;
2123: v_false_val BOOLEAN DEFAULT FALSE;
2124: CURSOR c_ghpo IS
2125: SELECT govt_hecs_payment_option
2126: FROM IGS_FI_HECS_PAY_OPTN
2127: WHERE hecs_payment_option = p_hecs_payment_option AND
2128: govt_hecs_payment_option IS NOT NULL;
2129: CURSOR c_gcitiz_rec IS
2130: SELECT PS.start_dt,
2151: SELECT SCHOV.start_dt,
2152: SCHOV.end_dt,
2153: HPO.govt_hecs_payment_option
2154: FROM IGS_EN_STDNT_PS_HECS_OPTION_V SCHOV,
2155: IGS_FI_HECS_PAY_OPTN HPO
2156: WHERE SCHOV.person_id = p_person_id AND
2157: SCHOV.hecs_payment_option = HPO.hecs_payment_option AND
2158: HPO.govt_hecs_payment_option IS NOT NULL
2159: ORDER BY SCHOV.start_dt,
2190: App_Exception.Raise_Exception;
2191: END;
2192: END enrpl_chk_whether_to_validate;
2193: FUNCTION enrpl_do_validations (
2194: p_govt_hecs_payment_option IGS_FI_HECS_PAY_OPTN.govt_hecs_payment_option%TYPE,
2195: p_govt_citizenship_cd IGS_ST_CITIZENSHP_CD.govt_citizenship_cd%TYPE,
2196: p_govt_perm_resident_cd IGS_PE_PERM_RES_CD.govt_perm_resident_cd%TYPE,
2197: p_message_name OUT NOCOPY VARCHAR2,
2198: p_return_type OUT NOCOPY VARCHAR)
2202: v_other_detail VARCHAR2(255);
2203: BEGIN
2204: -- this module performs the validation that is
2205: -- required, in that if the govt. value for
2206: -- the IGS_FI_HECS_PAY_OPTN = 10 or 11, then
2207: -- the govt. value for the IGS_ST_CITIZENSHP_CD must be
2208: -- 1 OR the govt. value for the IGS_PE_PERM_RES_CD must
2209: -- be 1
2210: -- OR
2207: -- the govt. value for the IGS_ST_CITIZENSHP_CD must be
2208: -- 1 OR the govt. value for the IGS_PE_PERM_RES_CD must
2209: -- be 1
2210: -- OR
2211: -- if the govt value for the IGS_FI_HECS_PAY_OPTN is 10,
2212: -- 11, 19, 20, 25, 26, 27, 32, or 40, then the govt. value for
2213: -- the IGS_ST_CITIZENSHP_CD must be 1, 2, or 3, and if it is
2214: -- 3, then the govt. value for the IGS_PE_PERM_RES_CD must
2215: -- be 1 or 3
2248: END;
2249: END enrpl_do_validations;
2250: BEGIN
2251: -- This is the main module which validates the student_course_hecs_
2252: -- option_v.IGS_FI_HECS_PAY_OPTN against the IGS_PE_STATISTICS.
2253: -- IGS_ST_CITIZENSHP_CD and the IGS_PE_STATISTICS.IGS_PE_PERM_RES_CD.
2254: p_message_name := null;
2255: p_return_type := NULL;
2256: -- validating the input parameters
2262: IF p_scho_start_dt IS NULL THEN
2263: RETURN TRUE;
2264: END IF;
2265: -- retrieving the govt. value for the student_course_
2266: -- hecs_option_v.IGS_FI_HECS_PAY_OPTN
2267: OPEN c_ghpo;
2268: FETCH c_ghpo INTO v_govt_hecs_payment_option;
2269: -- exit successfully if a record isn't found
2270: -- therefore, no validation is required
2322: OPEN c_gprcd;
2323: FETCH c_gprcd INTO v_govt_perm_resident_cd;
2324: CLOSE c_gprcd;
2325: -- retrieving govt. value for the
2326: -- IGS_EN_STDNT_PS_HECS_OPTION_V.IGS_FI_HECS_PAY_OPTN
2327: -- in which more than one record may be found
2328: FOR v_scho IN c_hpo_rec LOOP
2329: -- set that a record was found
2330: v_scho_rec_found := TRUE;
2396: p_message_name OUT NOCOPY VARCHAR2,
2397: p_return_type OUT NOCOPY VARCHAR2 )
2398: RETURN BOOLEAN AS
2399: BEGIN
2400: -- validate the IGS_EN_STDNT_PS_HECS_OPTION_V.IGS_FI_HECS_PAY_OPTN against
2401: -- the IGS_PS_VER.IGS_PS_TYPE and the IGS_PE_STATISTICS.citizenship_cd
2402: DECLARE
2403: v_govt_hecs_payment_option IGS_FI_HECS_PAY_OPTN.govt_hecs_payment_option%TYPE;
2404: v_govt_course_type IGS_PS_TYPE.govt_course_type%TYPE;
2399: BEGIN
2400: -- validate the IGS_EN_STDNT_PS_HECS_OPTION_V.IGS_FI_HECS_PAY_OPTN against
2401: -- the IGS_PS_VER.IGS_PS_TYPE and the IGS_PE_STATISTICS.citizenship_cd
2402: DECLARE
2403: v_govt_hecs_payment_option IGS_FI_HECS_PAY_OPTN.govt_hecs_payment_option%TYPE;
2404: v_govt_course_type IGS_PS_TYPE.govt_course_type%TYPE;
2405: v_govt_citizenship_cd IGS_ST_CITIZENSHP_CD.govt_citizenship_cd%TYPE;
2406: v_validation_failed BOOLEAN DEFAULT FALSE;
2407: CURSOR c_hpo IS
2405: v_govt_citizenship_cd IGS_ST_CITIZENSHP_CD.govt_citizenship_cd%TYPE;
2406: v_validation_failed BOOLEAN DEFAULT FALSE;
2407: CURSOR c_hpo IS
2408: SELECT hpo.govt_hecs_payment_option
2409: FROM IGS_FI_HECS_PAY_OPTN hpo
2410: WHERE hpo.hecs_payment_option = p_hecs_payment_option;
2411: CURSOR c_ct IS
2412: SELECT ct.govt_course_type
2413: FROM IGS_EN_STDNT_PS_ATT sca,
2434: scho.end_dt,
2435: hpo.govt_hecs_payment_option,
2436: ct.govt_course_type
2437: FROM IGS_EN_STDNT_PS_HECS_OPTION_V scho,
2438: IGS_FI_HECS_PAY_OPTN hpo,
2439: IGS_EN_STDNT_PS_ATT sca,
2440: IGS_PS_VER cv,
2441: IGS_PS_TYPE ct
2442: WHERE scho.person_id = p_person_id AND
2499: App_Exception.Raise_Exception;
2500: END;
2501: END enrpl_chk_dates_overlap;
2502: FUNCTION enrpl_perform_validation (
2503: p_govt_hecs_payment_option IGS_FI_HECS_PAY_OPTN.govt_hecs_payment_option%TYPE,
2504: p_govt_course_type IGS_PS_TYPE.govt_course_type%TYPE,
2505: p_govt_citizenship_cd IGS_ST_CITIZENSHP_CD.govt_citizenship_cd%TYPE,
2506: p_message_name OUT NOCOPY VARCHAR2,
2507: p_return_type OUT NOCOPY VARCHAR2)
2813: IGS_PE_GOVCITIZEN_CD IGS_ST_CITIZENSHP_CD.govt_citizenship_cd%TYPE );
2814: TYPE t_hpo_details IS RECORD (
2815: start_dt IGS_PE_STATISTICS.start_dt%TYPE,
2816: end_dt IGS_PE_STATISTICS.end_dt%TYPE,
2817: govt_hpo IGS_FI_HECS_PAY_OPTN.govt_hecs_payment_option%TYPE );
2818: v_govt_hecs_payment_option IGS_FI_HECS_PAY_OPTN.govt_hecs_payment_option%TYPE;
2819: v_govt_citizenship_cd IGS_ST_CITIZENSHP_CD.govt_citizenship_cd%TYPE;
2820: v_cit_cd_details_rec t_govt_cit_cd_details;
2821: v_hpo_details_rec t_hpo_details;
2814: TYPE t_hpo_details IS RECORD (
2815: start_dt IGS_PE_STATISTICS.start_dt%TYPE,
2816: end_dt IGS_PE_STATISTICS.end_dt%TYPE,
2817: govt_hpo IGS_FI_HECS_PAY_OPTN.govt_hecs_payment_option%TYPE );
2818: v_govt_hecs_payment_option IGS_FI_HECS_PAY_OPTN.govt_hecs_payment_option%TYPE;
2819: v_govt_citizenship_cd IGS_ST_CITIZENSHP_CD.govt_citizenship_cd%TYPE;
2820: v_cit_cd_details_rec t_govt_cit_cd_details;
2821: v_hpo_details_rec t_hpo_details;
2822: v_institution_3037 BOOLEAN DEFAULT FALSE;
2829: ist.institution_status = ins.institution_status AND
2830: ist.s_institution_status = 'ACTIVE';
2831: CURSOR c_govt_hpo IS
2832: SELECT hpo.govt_hecs_payment_option
2833: FROM IGS_FI_HECS_PAY_OPTN hpo
2834: WHERE hpo.hecs_payment_option = p_hecs_payment_option AND
2835: hpo.govt_hecs_payment_option IS NOT NULL;
2836: CURSOR c_ps IS
2837: SELECT ps.start_dt,
2848: SELECT schov.start_dt,
2849: schov.end_dt,
2850: hpo.govt_hecs_payment_option
2851: FROM IGS_EN_STDNT_PS_HECS_OPTION_V schov,
2852: IGS_FI_HECS_PAY_OPTN hpo
2853: WHERE schov.person_id = p_person_id AND
2854: schov.hecs_payment_option = hpo.hecs_payment_option AND
2855: hpo.govt_hecs_payment_option IS NOT NULL
2856: ORDER BY
2874: -- a IGS_PE_STATISTICS record, logic is required to loop through all of the
2875: -- IGS_EN_STDNT_PS_HECS_OPTION_V records that may exist for the person.
2876: -- Looping is required for the different course codes that may exist.
2877: -- Therefor, this module validates the IGS_EN_STDNT_PS_HECS_OPTION_V.
2878: -- IGS_FI_HECS_PAY_OPTN against the IGS_PE_STATISTICS.citizenship_cd.
2879: -- Validations are :
2880: -- 1. If the government value for the IGS_PE_STATISTICS.citizenship_cd is
2881: -- 1, 2 or 3, then the government value for the
2882: -- IGS_EN_STDNT_PS_HECS_OPTION_V.hecs_payment_option must be
2888: -- 24, 26, 27, 30, 70 or 99 (DEETYA validation 1574).
2889: -- 3. If the local INSTITUTION is not 3037 (Open Learning Agency of Australia)
2890: -- and the government value for the IGS_PE_STATISTICS.citizenship_cd is 1,
2891: -- then the government value for the IGS_EN_STDNT_PS_HECS_OPTION_V.
2892: -- IGS_FI_HECS_PAY_OPTN must be 10, 11, 19, 20, 25, 26, 27, 31, 32, 33, 40,
2893: -- 70
2894: -- or 99 (DEETYA validation 1675).
2895: --- Set the default message number
2896: p_message_name := null;
2995: p_message_name OUT NOCOPY VARCHAR2)
2996: RETURN BOOLEAN AS
2997: BEGIN
2998: DECLARE
2999: gv_closed_ind IGS_FI_HECS_PAY_OPTN.closed_ind%TYPE;
3000: CURSOR gc_hecs_payment_option(
3001: cp_hecs_payment_option IGS_FI_HECS_PAY_OPTN.hecs_payment_option%TYPE) IS
3002: SELECT IGS_FI_HECS_PAY_OPTN.closed_ind
3003: FROM IGS_FI_HECS_PAY_OPTN
2997: BEGIN
2998: DECLARE
2999: gv_closed_ind IGS_FI_HECS_PAY_OPTN.closed_ind%TYPE;
3000: CURSOR gc_hecs_payment_option(
3001: cp_hecs_payment_option IGS_FI_HECS_PAY_OPTN.hecs_payment_option%TYPE) IS
3002: SELECT IGS_FI_HECS_PAY_OPTN.closed_ind
3003: FROM IGS_FI_HECS_PAY_OPTN
3004: WHERE IGS_FI_HECS_PAY_OPTN.hecs_payment_option = cp_hecs_payment_option;
3005: BEGIN
2998: DECLARE
2999: gv_closed_ind IGS_FI_HECS_PAY_OPTN.closed_ind%TYPE;
3000: CURSOR gc_hecs_payment_option(
3001: cp_hecs_payment_option IGS_FI_HECS_PAY_OPTN.hecs_payment_option%TYPE) IS
3002: SELECT IGS_FI_HECS_PAY_OPTN.closed_ind
3003: FROM IGS_FI_HECS_PAY_OPTN
3004: WHERE IGS_FI_HECS_PAY_OPTN.hecs_payment_option = cp_hecs_payment_option;
3005: BEGIN
3006: -- This module validates if IGS_FI_HECS_PAY_OPTN.hecs_payment_option
2999: gv_closed_ind IGS_FI_HECS_PAY_OPTN.closed_ind%TYPE;
3000: CURSOR gc_hecs_payment_option(
3001: cp_hecs_payment_option IGS_FI_HECS_PAY_OPTN.hecs_payment_option%TYPE) IS
3002: SELECT IGS_FI_HECS_PAY_OPTN.closed_ind
3003: FROM IGS_FI_HECS_PAY_OPTN
3004: WHERE IGS_FI_HECS_PAY_OPTN.hecs_payment_option = cp_hecs_payment_option;
3005: BEGIN
3006: -- This module validates if IGS_FI_HECS_PAY_OPTN.hecs_payment_option
3007: -- is closed
3000: CURSOR gc_hecs_payment_option(
3001: cp_hecs_payment_option IGS_FI_HECS_PAY_OPTN.hecs_payment_option%TYPE) IS
3002: SELECT IGS_FI_HECS_PAY_OPTN.closed_ind
3003: FROM IGS_FI_HECS_PAY_OPTN
3004: WHERE IGS_FI_HECS_PAY_OPTN.hecs_payment_option = cp_hecs_payment_option;
3005: BEGIN
3006: -- This module validates if IGS_FI_HECS_PAY_OPTN.hecs_payment_option
3007: -- is closed
3008: p_message_name := null;
3002: SELECT IGS_FI_HECS_PAY_OPTN.closed_ind
3003: FROM IGS_FI_HECS_PAY_OPTN
3004: WHERE IGS_FI_HECS_PAY_OPTN.hecs_payment_option = cp_hecs_payment_option;
3005: BEGIN
3006: -- This module validates if IGS_FI_HECS_PAY_OPTN.hecs_payment_option
3007: -- is closed
3008: p_message_name := null;
3009: OPEN gc_hecs_payment_option(
3010: p_hecs_payment_option);
3050: -- values, including IGS_PE_STATISTICS.yr_arrival and
3051: -- IGS_PE_STATISTICS.term_location_[country|postcode]
3052: -- The IGS_EN_STDNT_PS_ATT.commencement_dt is also used in validations.
3053: DECLARE
3054: v_govt_hecs_payment_option IGS_FI_HECS_PAY_OPTN.govt_hecs_payment_option%TYPE;
3055: v_commencement_dt IGS_EN_STDNT_PS_ATT.commencement_dt%TYPE;
3056: v_govt_citizenship_cd IGS_ST_CITIZENSHP_CD.govt_citizenship_cd%TYPE;
3057: v_validation_failed BOOLEAN DEFAULT FALSE;
3058: CURSOR c_hpo IS
3056: v_govt_citizenship_cd IGS_ST_CITIZENSHP_CD.govt_citizenship_cd%TYPE;
3057: v_validation_failed BOOLEAN DEFAULT FALSE;
3058: CURSOR c_hpo IS
3059: SELECT hpo.govt_hecs_payment_option
3060: FROM IGS_FI_HECS_PAY_OPTN hpo
3061: WHERE hpo.hecs_payment_option = p_hecs_payment_option;
3062: CURSOR c_sca IS
3063: SELECT sca.commencement_dt
3064: FROM IGS_EN_STDNT_PS_ATT sca
3087: scho.end_dt,
3088: hpo.govt_hecs_payment_option,
3089: sca.commencement_dt
3090: FROM IGS_EN_STDNT_PS_HECS_OPTION_V scho,
3091: IGS_FI_HECS_PAY_OPTN hpo,
3092: IGS_EN_STDNT_PS_ATT sca
3093: WHERE scho.person_id = p_person_id AND
3094: scho.hecs_payment_option = hpo.hecs_payment_option AND
3095: sca.person_id = scho.person_id AND
3427: RETURN BOOLEAN AS
3428: BEGIN
3429: DECLARE
3430: v_expire_aftr_acdmc_perd_ind
3431: IGS_FI_HECS_PAY_OPTN.expire_aftr_acdmc_perd_ind%TYPE;
3432: v_end_dt IGS_CA_INST.end_dt%TYPE;
3433: v_cal_type IGS_CA_INST.cal_type%TYPE;
3434: v_sequence_number IGS_CA_INST.sequence_number%TYPE;
3435: CURSOR c_hpo(
3434: v_sequence_number IGS_CA_INST.sequence_number%TYPE;
3435: CURSOR c_hpo(
3436: cp_hpo IGS_EN_STDNTPSHECSOP.hecs_payment_option%TYPE) IS
3437: SELECT expire_aftr_acdmc_perd_ind
3438: FROM IGS_FI_HECS_PAY_OPTN
3439: WHERE hecs_payment_option= cp_hpo;
3440: CURSOR c_cal_instance (
3441: cp_cal_type IGS_CA_INST.cal_type%TYPE,
3442: cp_cal_sequence_number IGS_CA_INST.sequence_number%TYPE) IS