[Home] [Help]
353: p_fee_type IN igs_fi_f_cat_fee_lbl_all.fee_type%TYPE,
354: p_s_fee_trigger_cat IN igs_fi_fee_type_all.s_fee_trigger_cat%TYPE,
355: p_trace_on IN VARCHAR2,
356: p_c_career IN igs_ps_ver_all.course_type%TYPE,
357: p_derived_attendance_type OUT NOCOPY igs_fi_fee_as_rate.attendance_type%TYPE,
358: p_derived_att_mode OUT NOCOPY igs_en_atd_mode_all.govt_attendance_mode%TYPE) RETURN BOOLEAN;
359:
360:
361: FUNCTION finpl_check_header_lines(p_n_person_id igs_fi_fee_as_all.person_id%TYPE,
394: p_trace_on IN VARCHAR2 ,
395: p_creation_dt IN OUT NOCOPY DATE ,
396: p_charge_elements IN OUT NOCOPY igs_fi_fee_as_all.chg_elements%TYPE ,
397: p_fee_assessment IN OUT NOCOPY NUMBER ,
398: p_charge_rate OUT NOCOPY IGS_FI_FEE_AS_RATE.chg_rate%TYPE,
399: p_c_career IN igs_ps_ver_all.course_type%TYPE,
400: p_elm_rng_order_name IN igs_fi_f_typ_ca_inst_all.elm_rng_order_name%TYPE,
401: p_n_max_chg_elements IN igs_fi_fee_as_items.max_chg_elements%TYPE,
402: p_n_called IN NUMBER) RETURN BOOLEAN;
466: Who When What
467: shtatiko 30-DEC-2003 Enh# 3167098, Created this function.
468: ***************************************************************/
469:
470: l_v_derived_class_standing igs_fi_fee_as_rate.class_standing%TYPE;
471:
472: BEGIN
473:
474: IF p_n_person_id IS NULL
1076: p_fee_type igs_fi_f_cat_fee_lbl_all.fee_type%TYPE,
1077: p_s_fee_trigger_cat igs_fi_fee_type_all.s_fee_trigger_cat%TYPE,
1078: p_trace_on VARCHAR2,
1079: p_c_career IN igs_ps_ver_all.course_type%TYPE,
1080: p_derived_attendance_type OUT NOCOPY igs_fi_fee_as_rate.attendance_type%TYPE,
1081: p_derived_att_mode OUT NOCOPY igs_en_atd_mode_all.govt_attendance_mode%TYPE) RETURN BOOLEAN AS
1082: /*************************************************************
1083: Created By : abshriva
1084: Date Created By : 1-Jun-2006
1462: p_trace_on IN VARCHAR2 ,
1463: p_creation_dt IN OUT NOCOPY DATE ,
1464: p_charge_elements IN OUT NOCOPY igs_fi_fee_as_all.chg_elements%TYPE ,
1465: p_fee_assessment IN OUT NOCOPY NUMBER,
1466: p_charge_rate OUT NOCOPY IGS_FI_FEE_AS_RATE.chg_rate%TYPE,
1467: p_c_career IN igs_ps_ver_all.course_type%TYPE,
1468: p_elm_rng_order_name IN igs_fi_f_typ_ca_inst_all.elm_rng_order_name%TYPE,
1469: p_n_max_chg_elements IN igs_fi_fee_as_items.max_chg_elements%TYPE,
1470: p_n_called IN NUMBER) RETURN BOOLEAN AS
1520: BEGIN
1521: DECLARE
1522: e_one_record_expected EXCEPTION;
1523: v_message_name VARCHAR2(30);
1524: v_charge_rate IGS_FI_FEE_AS_RATE.chg_rate%TYPE := 0.0;
1525: v_lower_nrml_rate_ovrd_ind IGS_FI_FEE_AS_RT.lower_nrml_rate_ovrd_ind%TYPE;
1526: v_residency_status_cd igs_fi_fee_as_rate.residency_status_cd%TYPE;
1527: v_class_standing igs_fi_fee_as_rate.class_standing%TYPE;
1528: v_cfar_chg_rate IGS_FI_FEE_AS_RT.chg_rate%TYPE;
1522: e_one_record_expected EXCEPTION;
1523: v_message_name VARCHAR2(30);
1524: v_charge_rate IGS_FI_FEE_AS_RATE.chg_rate%TYPE := 0.0;
1525: v_lower_nrml_rate_ovrd_ind IGS_FI_FEE_AS_RT.lower_nrml_rate_ovrd_ind%TYPE;
1526: v_residency_status_cd igs_fi_fee_as_rate.residency_status_cd%TYPE;
1527: v_class_standing igs_fi_fee_as_rate.class_standing%TYPE;
1528: v_cfar_chg_rate IGS_FI_FEE_AS_RT.chg_rate%TYPE;
1529: v_derived_location_cd igs_fi_fee_as_rate.location_cd%TYPE;
1530: v_derived_attendance_type igs_fi_fee_as_rate.attendance_type%TYPE;
1523: v_message_name VARCHAR2(30);
1524: v_charge_rate IGS_FI_FEE_AS_RATE.chg_rate%TYPE := 0.0;
1525: v_lower_nrml_rate_ovrd_ind IGS_FI_FEE_AS_RT.lower_nrml_rate_ovrd_ind%TYPE;
1526: v_residency_status_cd igs_fi_fee_as_rate.residency_status_cd%TYPE;
1527: v_class_standing igs_fi_fee_as_rate.class_standing%TYPE;
1528: v_cfar_chg_rate IGS_FI_FEE_AS_RT.chg_rate%TYPE;
1529: v_derived_location_cd igs_fi_fee_as_rate.location_cd%TYPE;
1530: v_derived_attendance_type igs_fi_fee_as_rate.attendance_type%TYPE;
1531: v_derived_govt_att_mode igs_en_atd_mode_all.govt_attendance_mode%TYPE;
1525: v_lower_nrml_rate_ovrd_ind IGS_FI_FEE_AS_RT.lower_nrml_rate_ovrd_ind%TYPE;
1526: v_residency_status_cd igs_fi_fee_as_rate.residency_status_cd%TYPE;
1527: v_class_standing igs_fi_fee_as_rate.class_standing%TYPE;
1528: v_cfar_chg_rate IGS_FI_FEE_AS_RT.chg_rate%TYPE;
1529: v_derived_location_cd igs_fi_fee_as_rate.location_cd%TYPE;
1530: v_derived_attendance_type igs_fi_fee_as_rate.attendance_type%TYPE;
1531: v_derived_govt_att_mode igs_en_atd_mode_all.govt_attendance_mode%TYPE;
1532: l_v_derived_prog_att_mode igs_en_atd_mode_all.attendance_mode%TYPE;
1533:
1526: v_residency_status_cd igs_fi_fee_as_rate.residency_status_cd%TYPE;
1527: v_class_standing igs_fi_fee_as_rate.class_standing%TYPE;
1528: v_cfar_chg_rate IGS_FI_FEE_AS_RT.chg_rate%TYPE;
1529: v_derived_location_cd igs_fi_fee_as_rate.location_cd%TYPE;
1530: v_derived_attendance_type igs_fi_fee_as_rate.attendance_type%TYPE;
1531: v_derived_govt_att_mode igs_en_atd_mode_all.govt_attendance_mode%TYPE;
1532: l_v_derived_prog_att_mode igs_en_atd_mode_all.attendance_mode%TYPE;
1533:
1534: v_derived_residency_status_cd igs_fi_fee_as_rate.residency_status_cd%TYPE;
1530: v_derived_attendance_type igs_fi_fee_as_rate.attendance_type%TYPE;
1531: v_derived_govt_att_mode igs_en_atd_mode_all.govt_attendance_mode%TYPE;
1532: l_v_derived_prog_att_mode igs_en_atd_mode_all.attendance_mode%TYPE;
1533:
1534: v_derived_residency_status_cd igs_fi_fee_as_rate.residency_status_cd%TYPE;
1535: v_derived_org_unit_cd hz_parties.party_number%TYPE;
1536: v_derived_class_standing igs_fi_fee_as_rate.class_standing%TYPE;
1537:
1538: v_derived_unit_set_cd igs_fi_fee_as_rate.unit_set_cd%TYPE := NULL;
1532: l_v_derived_prog_att_mode igs_en_atd_mode_all.attendance_mode%TYPE;
1533:
1534: v_derived_residency_status_cd igs_fi_fee_as_rate.residency_status_cd%TYPE;
1535: v_derived_org_unit_cd hz_parties.party_number%TYPE;
1536: v_derived_class_standing igs_fi_fee_as_rate.class_standing%TYPE;
1537:
1538: v_derived_unit_set_cd igs_fi_fee_as_rate.unit_set_cd%TYPE := NULL;
1539: v_derived_us_version_num igs_fi_fee_as_rate.us_version_number%TYPE := NULL;
1540:
1534: v_derived_residency_status_cd igs_fi_fee_as_rate.residency_status_cd%TYPE;
1535: v_derived_org_unit_cd hz_parties.party_number%TYPE;
1536: v_derived_class_standing igs_fi_fee_as_rate.class_standing%TYPE;
1537:
1538: v_derived_unit_set_cd igs_fi_fee_as_rate.unit_set_cd%TYPE := NULL;
1539: v_derived_us_version_num igs_fi_fee_as_rate.us_version_number%TYPE := NULL;
1540:
1541: lv_cntrct_rt_apply BOOLEAN;
1542:
1535: v_derived_org_unit_cd hz_parties.party_number%TYPE;
1536: v_derived_class_standing igs_fi_fee_as_rate.class_standing%TYPE;
1537:
1538: v_derived_unit_set_cd igs_fi_fee_as_rate.unit_set_cd%TYPE := NULL;
1539: v_derived_us_version_num igs_fi_fee_as_rate.us_version_number%TYPE := NULL;
1540:
1541: lv_cntrct_rt_apply BOOLEAN;
1542:
1543: /**to check whether override charge method is actioned**/
1552: l_b_elm_ranges_defined BOOLEAN := FALSE;
1553: l_b_elm_range_applies BOOLEAN := FALSE;
1554: l_n_crs_version_num igs_fi_fee_as_items.crs_version_number%TYPE;
1555:
1556: v_derived_unit_type_id igs_fi_fee_as_rate.unit_type_id%TYPE;
1557: v_derived_unit_class igs_fi_fee_as_rate.unit_class%TYPE;
1558: v_derived_unit_mode igs_fi_fee_as_rate.unit_mode%TYPE;
1559: v_derived_unit_cd igs_fi_fee_as_rate.unit_cd%TYPE;
1560: v_derived_unit_version_num igs_fi_fee_as_rate.unit_version_number%TYPE;
1553: l_b_elm_range_applies BOOLEAN := FALSE;
1554: l_n_crs_version_num igs_fi_fee_as_items.crs_version_number%TYPE;
1555:
1556: v_derived_unit_type_id igs_fi_fee_as_rate.unit_type_id%TYPE;
1557: v_derived_unit_class igs_fi_fee_as_rate.unit_class%TYPE;
1558: v_derived_unit_mode igs_fi_fee_as_rate.unit_mode%TYPE;
1559: v_derived_unit_cd igs_fi_fee_as_rate.unit_cd%TYPE;
1560: v_derived_unit_version_num igs_fi_fee_as_rate.unit_version_number%TYPE;
1561: v_derived_unit_level igs_fi_fee_as_rate.unit_level%TYPE;
1554: l_n_crs_version_num igs_fi_fee_as_items.crs_version_number%TYPE;
1555:
1556: v_derived_unit_type_id igs_fi_fee_as_rate.unit_type_id%TYPE;
1557: v_derived_unit_class igs_fi_fee_as_rate.unit_class%TYPE;
1558: v_derived_unit_mode igs_fi_fee_as_rate.unit_mode%TYPE;
1559: v_derived_unit_cd igs_fi_fee_as_rate.unit_cd%TYPE;
1560: v_derived_unit_version_num igs_fi_fee_as_rate.unit_version_number%TYPE;
1561: v_derived_unit_level igs_fi_fee_as_rate.unit_level%TYPE;
1562: l_v_level_code igs_ps_unit_type_lvl.level_code%TYPE;
1555:
1556: v_derived_unit_type_id igs_fi_fee_as_rate.unit_type_id%TYPE;
1557: v_derived_unit_class igs_fi_fee_as_rate.unit_class%TYPE;
1558: v_derived_unit_mode igs_fi_fee_as_rate.unit_mode%TYPE;
1559: v_derived_unit_cd igs_fi_fee_as_rate.unit_cd%TYPE;
1560: v_derived_unit_version_num igs_fi_fee_as_rate.unit_version_number%TYPE;
1561: v_derived_unit_level igs_fi_fee_as_rate.unit_level%TYPE;
1562: l_v_level_code igs_ps_unit_type_lvl.level_code%TYPE;
1563:
1556: v_derived_unit_type_id igs_fi_fee_as_rate.unit_type_id%TYPE;
1557: v_derived_unit_class igs_fi_fee_as_rate.unit_class%TYPE;
1558: v_derived_unit_mode igs_fi_fee_as_rate.unit_mode%TYPE;
1559: v_derived_unit_cd igs_fi_fee_as_rate.unit_cd%TYPE;
1560: v_derived_unit_version_num igs_fi_fee_as_rate.unit_version_number%TYPE;
1561: v_derived_unit_level igs_fi_fee_as_rate.unit_level%TYPE;
1562: l_v_level_code igs_ps_unit_type_lvl.level_code%TYPE;
1563:
1564: l_v_inst_course_cd igs_fi_fai_dtls.course_cd%TYPE;
1557: v_derived_unit_class igs_fi_fee_as_rate.unit_class%TYPE;
1558: v_derived_unit_mode igs_fi_fee_as_rate.unit_mode%TYPE;
1559: v_derived_unit_cd igs_fi_fee_as_rate.unit_cd%TYPE;
1560: v_derived_unit_version_num igs_fi_fee_as_rate.unit_version_number%TYPE;
1561: v_derived_unit_level igs_fi_fee_as_rate.unit_level%TYPE;
1562: l_v_level_code igs_ps_unit_type_lvl.level_code%TYPE;
1563:
1564: l_v_inst_course_cd igs_fi_fai_dtls.course_cd%TYPE;
1565: l_v_inst_unit_att_status igs_fi_fai_dtls.unit_attempt_status%TYPE;
1609: far.unit_cd,
1610: far.unit_version_number,
1611: far.unit_level
1612: FROM igs_fi_elm_range_rt err,
1613: igs_fi_fee_as_rate far
1614: WHERE far.fee_type = err.fee_type
1615: AND far.fee_cal_type = err.fee_cal_type
1616: AND far.fee_ci_sequence_number = err.fee_ci_sequence_number
1617: AND far.rate_number = err.rate_number
1629:
1630: -- This cursor identifies the contact rate based for the person program attempt for the fee type being processed.
1631: -- If the Profile is Nominated, Rate's AM is compared against Nominated Program AM.
1632: -- Derived, Derived Govt AM is compared against Govt AM mapped to Rate's AM.
1633: CURSOR c_cfar ( cp_location_cd igs_fi_fee_as_rate.location_cd%TYPE,
1634: cp_attendance_type igs_fi_fee_as_rate.attendance_type%TYPE,
1635: cp_prog_att_mode igs_fi_fee_as_rate.attendance_mode%TYPE,
1636: cp_govt_att_mode igs_en_atd_mode_all.govt_attendance_mode%TYPE) IS
1637: SELECT cfar.lower_nrml_rate_ovrd_ind,
1630: -- This cursor identifies the contact rate based for the person program attempt for the fee type being processed.
1631: -- If the Profile is Nominated, Rate's AM is compared against Nominated Program AM.
1632: -- Derived, Derived Govt AM is compared against Govt AM mapped to Rate's AM.
1633: CURSOR c_cfar ( cp_location_cd igs_fi_fee_as_rate.location_cd%TYPE,
1634: cp_attendance_type igs_fi_fee_as_rate.attendance_type%TYPE,
1635: cp_prog_att_mode igs_fi_fee_as_rate.attendance_mode%TYPE,
1636: cp_govt_att_mode igs_en_atd_mode_all.govt_attendance_mode%TYPE) IS
1637: SELECT cfar.lower_nrml_rate_ovrd_ind,
1638: cfar.chg_rate
1631: -- If the Profile is Nominated, Rate's AM is compared against Nominated Program AM.
1632: -- Derived, Derived Govt AM is compared against Govt AM mapped to Rate's AM.
1633: CURSOR c_cfar ( cp_location_cd igs_fi_fee_as_rate.location_cd%TYPE,
1634: cp_attendance_type igs_fi_fee_as_rate.attendance_type%TYPE,
1635: cp_prog_att_mode igs_fi_fee_as_rate.attendance_mode%TYPE,
1636: cp_govt_att_mode igs_en_atd_mode_all.govt_attendance_mode%TYPE) IS
1637: SELECT cfar.lower_nrml_rate_ovrd_ind,
1638: cfar.chg_rate
1639: FROM igs_fi_fee_as_rt cfar,
1678: FROM igs_ps_unit_ofr_opt_all
1679: WHERE uoo_id = cp_n_uoo_id;
1680:
1681: FUNCTION finpl_ins_match_chg_rate(
1682: p_rate_location_cd igs_fi_fee_as_rate.location_cd%TYPE,
1683: p_rate_attendance_type igs_fi_fee_as_rate.attendance_type%TYPE,
1684: p_rate_attendance_mode igs_fi_fee_as_rate.attendance_mode%TYPE,
1685: p_rate_class_standing IGS_PR_CLASS_STD.CLASS_STANDING%TYPE,
1686: p_rate_course_cd igs_ps_ver_all.course_cd%TYPE,
1679: WHERE uoo_id = cp_n_uoo_id;
1680:
1681: FUNCTION finpl_ins_match_chg_rate(
1682: p_rate_location_cd igs_fi_fee_as_rate.location_cd%TYPE,
1683: p_rate_attendance_type igs_fi_fee_as_rate.attendance_type%TYPE,
1684: p_rate_attendance_mode igs_fi_fee_as_rate.attendance_mode%TYPE,
1685: p_rate_class_standing IGS_PR_CLASS_STD.CLASS_STANDING%TYPE,
1686: p_rate_course_cd igs_ps_ver_all.course_cd%TYPE,
1687: p_rate_version_number igs_ps_ver_all.version_number%TYPE,
1680:
1681: FUNCTION finpl_ins_match_chg_rate(
1682: p_rate_location_cd igs_fi_fee_as_rate.location_cd%TYPE,
1683: p_rate_attendance_type igs_fi_fee_as_rate.attendance_type%TYPE,
1684: p_rate_attendance_mode igs_fi_fee_as_rate.attendance_mode%TYPE,
1685: p_rate_class_standing IGS_PR_CLASS_STD.CLASS_STANDING%TYPE,
1686: p_rate_course_cd igs_ps_ver_all.course_cd%TYPE,
1687: p_rate_version_number igs_ps_ver_all.version_number%TYPE,
1688: p_rate_org_unit_cd igs_ps_unit_ver_all.owner_org_unit_cd%TYPE,
1686: p_rate_course_cd igs_ps_ver_all.course_cd%TYPE,
1687: p_rate_version_number igs_ps_ver_all.version_number%TYPE,
1688: p_rate_org_unit_cd igs_ps_unit_ver_all.owner_org_unit_cd%TYPE,
1689: p_rate_residency_status_cd igs_pe_res_dtls_all.residency_status_cd%TYPE,
1690: p_derived_location_cd igs_fi_fee_as_rate.location_cd%TYPE,
1691: p_derived_attendance_type igs_fi_fee_as_rate.attendance_type%TYPE,
1692: p_derived_govt_att_mode igs_en_atd_mode_all.govt_attendance_mode%TYPE,
1693: p_derived_prog_att_mode igs_en_atd_mode_all.attendance_mode%TYPE,
1694: p_derived_class_standing IGS_PR_CLASS_STD.CLASS_STANDING%TYPE,
1687: p_rate_version_number igs_ps_ver_all.version_number%TYPE,
1688: p_rate_org_unit_cd igs_ps_unit_ver_all.owner_org_unit_cd%TYPE,
1689: p_rate_residency_status_cd igs_pe_res_dtls_all.residency_status_cd%TYPE,
1690: p_derived_location_cd igs_fi_fee_as_rate.location_cd%TYPE,
1691: p_derived_attendance_type igs_fi_fee_as_rate.attendance_type%TYPE,
1692: p_derived_govt_att_mode igs_en_atd_mode_all.govt_attendance_mode%TYPE,
1693: p_derived_prog_att_mode igs_en_atd_mode_all.attendance_mode%TYPE,
1694: p_derived_class_standing IGS_PR_CLASS_STD.CLASS_STANDING%TYPE,
1695: p_derived_course_cd igs_ps_ver_all.course_cd%TYPE,
1695: p_derived_course_cd igs_ps_ver_all.course_cd%TYPE,
1696: p_derived_version_number igs_ps_ver_all.version_number%TYPE,
1697: p_derived_org_unit_cd igs_ps_unit_ver_all.owner_org_unit_cd%TYPE,
1698: p_derived_residency_status_cd igs_pe_res_dtls_all.residency_status_cd%TYPE,
1699: p_rate_unit_set_cd igs_fi_fee_as_rate.unit_set_cd%TYPE,
1700: p_rate_us_version_num igs_fi_fee_as_rate.us_version_number%TYPE,
1701: p_derived_unit_set_cd igs_fi_fee_as_rate.unit_set_cd%TYPE,
1702: p_derived_us_version_num igs_fi_fee_as_rate.us_version_number%TYPE,
1703: p_rate_unit_type_id igs_fi_fee_as_items.unit_type_id%TYPE,
1696: p_derived_version_number igs_ps_ver_all.version_number%TYPE,
1697: p_derived_org_unit_cd igs_ps_unit_ver_all.owner_org_unit_cd%TYPE,
1698: p_derived_residency_status_cd igs_pe_res_dtls_all.residency_status_cd%TYPE,
1699: p_rate_unit_set_cd igs_fi_fee_as_rate.unit_set_cd%TYPE,
1700: p_rate_us_version_num igs_fi_fee_as_rate.us_version_number%TYPE,
1701: p_derived_unit_set_cd igs_fi_fee_as_rate.unit_set_cd%TYPE,
1702: p_derived_us_version_num igs_fi_fee_as_rate.us_version_number%TYPE,
1703: p_rate_unit_type_id igs_fi_fee_as_items.unit_type_id%TYPE,
1704: p_derived_unit_type_id igs_fi_fee_as_items.unit_type_id%TYPE,
1697: p_derived_org_unit_cd igs_ps_unit_ver_all.owner_org_unit_cd%TYPE,
1698: p_derived_residency_status_cd igs_pe_res_dtls_all.residency_status_cd%TYPE,
1699: p_rate_unit_set_cd igs_fi_fee_as_rate.unit_set_cd%TYPE,
1700: p_rate_us_version_num igs_fi_fee_as_rate.us_version_number%TYPE,
1701: p_derived_unit_set_cd igs_fi_fee_as_rate.unit_set_cd%TYPE,
1702: p_derived_us_version_num igs_fi_fee_as_rate.us_version_number%TYPE,
1703: p_rate_unit_type_id igs_fi_fee_as_items.unit_type_id%TYPE,
1704: p_derived_unit_type_id igs_fi_fee_as_items.unit_type_id%TYPE,
1705: p_rate_unit_class igs_fi_fee_as_items.unit_class%TYPE,
1698: p_derived_residency_status_cd igs_pe_res_dtls_all.residency_status_cd%TYPE,
1699: p_rate_unit_set_cd igs_fi_fee_as_rate.unit_set_cd%TYPE,
1700: p_rate_us_version_num igs_fi_fee_as_rate.us_version_number%TYPE,
1701: p_derived_unit_set_cd igs_fi_fee_as_rate.unit_set_cd%TYPE,
1702: p_derived_us_version_num igs_fi_fee_as_rate.us_version_number%TYPE,
1703: p_rate_unit_type_id igs_fi_fee_as_items.unit_type_id%TYPE,
1704: p_derived_unit_type_id igs_fi_fee_as_items.unit_type_id%TYPE,
1705: p_rate_unit_class igs_fi_fee_as_items.unit_class%TYPE,
1706: p_derived_unit_class igs_fi_fee_as_items.unit_class%TYPE,
1705: p_rate_unit_class igs_fi_fee_as_items.unit_class%TYPE,
1706: p_derived_unit_class igs_fi_fee_as_items.unit_class%TYPE,
1707: p_rate_unit_mode igs_fi_fee_as_items.unit_mode%TYPE,
1708: p_derived_unit_mode igs_fi_fee_as_items.unit_mode%TYPE,
1709: p_rate_unit_cd igs_fi_fee_as_rate.unit_cd%TYPE,
1710: p_derived_unit_cd igs_fi_fee_as_rate.unit_cd%TYPE,
1711: p_rate_unit_version_num igs_fi_fee_as_rate.unit_version_number%TYPE,
1712: p_derived_unit_version_num igs_fi_fee_as_rate.unit_version_number%TYPE,
1713: p_rate_unit_level igs_fi_fee_as_items.unit_level%TYPE,
1706: p_derived_unit_class igs_fi_fee_as_items.unit_class%TYPE,
1707: p_rate_unit_mode igs_fi_fee_as_items.unit_mode%TYPE,
1708: p_derived_unit_mode igs_fi_fee_as_items.unit_mode%TYPE,
1709: p_rate_unit_cd igs_fi_fee_as_rate.unit_cd%TYPE,
1710: p_derived_unit_cd igs_fi_fee_as_rate.unit_cd%TYPE,
1711: p_rate_unit_version_num igs_fi_fee_as_rate.unit_version_number%TYPE,
1712: p_derived_unit_version_num igs_fi_fee_as_rate.unit_version_number%TYPE,
1713: p_rate_unit_level igs_fi_fee_as_items.unit_level%TYPE,
1714: p_derived_unit_level igs_fi_fee_as_items.unit_level%TYPE
1707: p_rate_unit_mode igs_fi_fee_as_items.unit_mode%TYPE,
1708: p_derived_unit_mode igs_fi_fee_as_items.unit_mode%TYPE,
1709: p_rate_unit_cd igs_fi_fee_as_rate.unit_cd%TYPE,
1710: p_derived_unit_cd igs_fi_fee_as_rate.unit_cd%TYPE,
1711: p_rate_unit_version_num igs_fi_fee_as_rate.unit_version_number%TYPE,
1712: p_derived_unit_version_num igs_fi_fee_as_rate.unit_version_number%TYPE,
1713: p_rate_unit_level igs_fi_fee_as_items.unit_level%TYPE,
1714: p_derived_unit_level igs_fi_fee_as_items.unit_level%TYPE
1715: ) RETURN BOOLEAN AS
1708: p_derived_unit_mode igs_fi_fee_as_items.unit_mode%TYPE,
1709: p_rate_unit_cd igs_fi_fee_as_rate.unit_cd%TYPE,
1710: p_derived_unit_cd igs_fi_fee_as_rate.unit_cd%TYPE,
1711: p_rate_unit_version_num igs_fi_fee_as_rate.unit_version_number%TYPE,
1712: p_derived_unit_version_num igs_fi_fee_as_rate.unit_version_number%TYPE,
1713: p_rate_unit_level igs_fi_fee_as_items.unit_level%TYPE,
1714: p_derived_unit_level igs_fi_fee_as_items.unit_level%TYPE
1715: ) RETURN BOOLEAN AS
1716: /*************************************************************
1913: p_fee_ci_sequence_number igs_fi_f_cat_fee_lbl_all.fee_ci_sequence_number%TYPE,
1914: p_fee_type igs_fi_f_cat_fee_lbl_all.fee_type%TYPE,
1915: p_s_fee_trigger_cat igs_fi_fee_type_all.s_fee_trigger_cat%TYPE,
1916: p_trace_on VARCHAR2,
1917: p_derived_attendance_type OUT NOCOPY igs_fi_fee_as_rate.attendance_type%TYPE,
1918: p_derived_govt_att_mode OUT NOCOPY igs_en_atd_mode_all.govt_attendance_mode%TYPE,
1919: p_derived_prog_att_mode OUT NOCOPY igs_en_atd_mode_all.attendance_mode%TYPE, -- Added as part of 3795849
1920: p_derived_residency_status_cd OUT NOCOPY igs_pe_res_dtls_all.residency_status_cd%TYPE,
1921: p_derived_class_standing OUT NOCOPY IGS_PR_CLASS_STD.CLASS_STANDING%TYPE,
2378: p_fee_type igs_fi_f_cat_fee_lbl_all.fee_type%TYPE,
2379: p_location_cd igs_en_stdnt_ps_att_all.location_cd%TYPE,
2380: p_effective_dt DATE,
2381: p_s_fee_trigger_cat igs_fi_fee_type_all.s_fee_trigger_cat%TYPE,
2382: p_derived_location_cd igs_fi_fee_as_rate.location_cd%TYPE,
2383: p_derived_attendance_type igs_fi_fee_as_rate.attendance_type%TYPE,
2384: p_derived_govt_att_mode igs_en_atd_mode_all.govt_attendance_mode%TYPE,
2385: p_derived_prog_att_mode igs_en_atd_mode_all.attendance_mode%TYPE,
2386: p_trace_on VARCHAR2,
2379: p_location_cd igs_en_stdnt_ps_att_all.location_cd%TYPE,
2380: p_effective_dt DATE,
2381: p_s_fee_trigger_cat igs_fi_fee_type_all.s_fee_trigger_cat%TYPE,
2382: p_derived_location_cd igs_fi_fee_as_rate.location_cd%TYPE,
2383: p_derived_attendance_type igs_fi_fee_as_rate.attendance_type%TYPE,
2384: p_derived_govt_att_mode igs_en_atd_mode_all.govt_attendance_mode%TYPE,
2385: p_derived_prog_att_mode igs_en_atd_mode_all.attendance_mode%TYPE,
2386: p_trace_on VARCHAR2,
2387: p_derived_residency_status_cd igs_pe_res_dtls_all.residency_status_cd%TYPE,
2388: p_derived_org_unit_cd igs_ps_unit_ver_all.owner_org_unit_cd%TYPE,
2389: p_derived_course_cd igs_ps_ver_all.course_cd%TYPE,
2390: p_derived_version_number igs_ps_ver_all.version_number%TYPE,
2391: p_derived_class_standing IGS_PR_CLASS_STD.CLASS_STANDING%TYPE,
2392: p_charge_rate OUT NOCOPY IGS_FI_FEE_AS_RATE.chg_rate%TYPE,
2393: p_derived_unit_set_cd IN igs_fi_fee_as_rate.unit_set_cd%TYPE,
2394: p_derived_us_version_num IN igs_fi_fee_as_rate.us_version_number%TYPE,
2395: p_derived_unit_type_id IN igs_fi_fee_as_items.unit_type_id%TYPE,
2396: p_derived_unit_class IN igs_fi_fee_as_items.unit_class%TYPE,
2389: p_derived_course_cd igs_ps_ver_all.course_cd%TYPE,
2390: p_derived_version_number igs_ps_ver_all.version_number%TYPE,
2391: p_derived_class_standing IGS_PR_CLASS_STD.CLASS_STANDING%TYPE,
2392: p_charge_rate OUT NOCOPY IGS_FI_FEE_AS_RATE.chg_rate%TYPE,
2393: p_derived_unit_set_cd IN igs_fi_fee_as_rate.unit_set_cd%TYPE,
2394: p_derived_us_version_num IN igs_fi_fee_as_rate.us_version_number%TYPE,
2395: p_derived_unit_type_id IN igs_fi_fee_as_items.unit_type_id%TYPE,
2396: p_derived_unit_class IN igs_fi_fee_as_items.unit_class%TYPE,
2397: p_derived_unit_mode IN igs_fi_fee_as_items.unit_mode%TYPE,
2390: p_derived_version_number igs_ps_ver_all.version_number%TYPE,
2391: p_derived_class_standing IGS_PR_CLASS_STD.CLASS_STANDING%TYPE,
2392: p_charge_rate OUT NOCOPY IGS_FI_FEE_AS_RATE.chg_rate%TYPE,
2393: p_derived_unit_set_cd IN igs_fi_fee_as_rate.unit_set_cd%TYPE,
2394: p_derived_us_version_num IN igs_fi_fee_as_rate.us_version_number%TYPE,
2395: p_derived_unit_type_id IN igs_fi_fee_as_items.unit_type_id%TYPE,
2396: p_derived_unit_class IN igs_fi_fee_as_items.unit_class%TYPE,
2397: p_derived_unit_mode IN igs_fi_fee_as_items.unit_mode%TYPE,
2398: p_derived_unit_cd IN igs_fi_fee_as_rate.unit_cd%TYPE,
2394: p_derived_us_version_num IN igs_fi_fee_as_rate.us_version_number%TYPE,
2395: p_derived_unit_type_id IN igs_fi_fee_as_items.unit_type_id%TYPE,
2396: p_derived_unit_class IN igs_fi_fee_as_items.unit_class%TYPE,
2397: p_derived_unit_mode IN igs_fi_fee_as_items.unit_mode%TYPE,
2398: p_derived_unit_cd IN igs_fi_fee_as_rate.unit_cd%TYPE,
2399: p_derived_unit_version_num IN igs_fi_fee_as_rate.unit_version_number%TYPE,
2400: p_derived_unit_level IN igs_fi_fee_as_items.unit_level%TYPE
2401: ) AS
2402: /***********************************************************************************************
2395: p_derived_unit_type_id IN igs_fi_fee_as_items.unit_type_id%TYPE,
2396: p_derived_unit_class IN igs_fi_fee_as_items.unit_class%TYPE,
2397: p_derived_unit_mode IN igs_fi_fee_as_items.unit_mode%TYPE,
2398: p_derived_unit_cd IN igs_fi_fee_as_rate.unit_cd%TYPE,
2399: p_derived_unit_version_num IN igs_fi_fee_as_rate.unit_version_number%TYPE,
2400: p_derived_unit_level IN igs_fi_fee_as_items.unit_level%TYPE
2401: ) AS
2402: /***********************************************************************************************
2403: Change History:
2418: smadathi 02-May-2002 Bug 2261649. The reference to function finp_get_additional_charge removed.
2419: **********************************************************************************************/
2420: BEGIN
2421: DECLARE
2422: v_chg_rate igs_fi_fee_as_rate.chg_rate%TYPE;
2423: v_s_relation_type igs_fi_fee_as_rate.s_relation_type%TYPE;
2424: v_location_cd igs_fi_fee_as_rate.location_cd%TYPE;
2425: v_attendance_type igs_fi_fee_as_rate.attendance_type%TYPE;
2426: v_attendance_mode igs_fi_fee_as_rate.attendance_mode%TYPE;
2419: **********************************************************************************************/
2420: BEGIN
2421: DECLARE
2422: v_chg_rate igs_fi_fee_as_rate.chg_rate%TYPE;
2423: v_s_relation_type igs_fi_fee_as_rate.s_relation_type%TYPE;
2424: v_location_cd igs_fi_fee_as_rate.location_cd%TYPE;
2425: v_attendance_type igs_fi_fee_as_rate.attendance_type%TYPE;
2426: v_attendance_mode igs_fi_fee_as_rate.attendance_mode%TYPE;
2427: v_rate_number igs_fi_fee_as_rate.rate_number%TYPE;
2420: BEGIN
2421: DECLARE
2422: v_chg_rate igs_fi_fee_as_rate.chg_rate%TYPE;
2423: v_s_relation_type igs_fi_fee_as_rate.s_relation_type%TYPE;
2424: v_location_cd igs_fi_fee_as_rate.location_cd%TYPE;
2425: v_attendance_type igs_fi_fee_as_rate.attendance_type%TYPE;
2426: v_attendance_mode igs_fi_fee_as_rate.attendance_mode%TYPE;
2427: v_rate_number igs_fi_fee_as_rate.rate_number%TYPE;
2428: v_fee_ass_rate_match BOOLEAN;
2421: DECLARE
2422: v_chg_rate igs_fi_fee_as_rate.chg_rate%TYPE;
2423: v_s_relation_type igs_fi_fee_as_rate.s_relation_type%TYPE;
2424: v_location_cd igs_fi_fee_as_rate.location_cd%TYPE;
2425: v_attendance_type igs_fi_fee_as_rate.attendance_type%TYPE;
2426: v_attendance_mode igs_fi_fee_as_rate.attendance_mode%TYPE;
2427: v_rate_number igs_fi_fee_as_rate.rate_number%TYPE;
2428: v_fee_ass_rate_match BOOLEAN;
2429: l_b_fee_ass_rate_found BOOLEAN;
2422: v_chg_rate igs_fi_fee_as_rate.chg_rate%TYPE;
2423: v_s_relation_type igs_fi_fee_as_rate.s_relation_type%TYPE;
2424: v_location_cd igs_fi_fee_as_rate.location_cd%TYPE;
2425: v_attendance_type igs_fi_fee_as_rate.attendance_type%TYPE;
2426: v_attendance_mode igs_fi_fee_as_rate.attendance_mode%TYPE;
2427: v_rate_number igs_fi_fee_as_rate.rate_number%TYPE;
2428: v_fee_ass_rate_match BOOLEAN;
2429: l_b_fee_ass_rate_found BOOLEAN;
2430: v_residency_status_cd igs_pe_res_dtls_all.residency_status_cd%TYPE;
2423: v_s_relation_type igs_fi_fee_as_rate.s_relation_type%TYPE;
2424: v_location_cd igs_fi_fee_as_rate.location_cd%TYPE;
2425: v_attendance_type igs_fi_fee_as_rate.attendance_type%TYPE;
2426: v_attendance_mode igs_fi_fee_as_rate.attendance_mode%TYPE;
2427: v_rate_number igs_fi_fee_as_rate.rate_number%TYPE;
2428: v_fee_ass_rate_match BOOLEAN;
2429: l_b_fee_ass_rate_found BOOLEAN;
2430: v_residency_status_cd igs_pe_res_dtls_all.residency_status_cd%TYPE;
2431: v_org_unit_cd igs_ps_unit_ver_all.owner_org_unit_cd%TYPE;
2428: v_fee_ass_rate_match BOOLEAN;
2429: l_b_fee_ass_rate_found BOOLEAN;
2430: v_residency_status_cd igs_pe_res_dtls_all.residency_status_cd%TYPE;
2431: v_org_unit_cd igs_ps_unit_ver_all.owner_org_unit_cd%TYPE;
2432: v_course_cd igs_fi_fee_as_rate.course_cd%TYPE;
2433: v_version_number igs_fi_fee_as_rate.version_number%TYPE;
2434: v_class_standing igs_fi_fee_as_rate.class_standing%TYPE;
2435: v_unit_set_cd igs_fi_fee_as_rate.unit_set_cd%TYPE := NULL;
2436: v_us_version_number igs_fi_fee_as_rate.us_version_number%TYPE := NULL;
2429: l_b_fee_ass_rate_found BOOLEAN;
2430: v_residency_status_cd igs_pe_res_dtls_all.residency_status_cd%TYPE;
2431: v_org_unit_cd igs_ps_unit_ver_all.owner_org_unit_cd%TYPE;
2432: v_course_cd igs_fi_fee_as_rate.course_cd%TYPE;
2433: v_version_number igs_fi_fee_as_rate.version_number%TYPE;
2434: v_class_standing igs_fi_fee_as_rate.class_standing%TYPE;
2435: v_unit_set_cd igs_fi_fee_as_rate.unit_set_cd%TYPE := NULL;
2436: v_us_version_number igs_fi_fee_as_rate.us_version_number%TYPE := NULL;
2437:
2430: v_residency_status_cd igs_pe_res_dtls_all.residency_status_cd%TYPE;
2431: v_org_unit_cd igs_ps_unit_ver_all.owner_org_unit_cd%TYPE;
2432: v_course_cd igs_fi_fee_as_rate.course_cd%TYPE;
2433: v_version_number igs_fi_fee_as_rate.version_number%TYPE;
2434: v_class_standing igs_fi_fee_as_rate.class_standing%TYPE;
2435: v_unit_set_cd igs_fi_fee_as_rate.unit_set_cd%TYPE := NULL;
2436: v_us_version_number igs_fi_fee_as_rate.us_version_number%TYPE := NULL;
2437:
2438: CURSOR c_ftfarv1(cp_v_s_relation_type igs_fi_fee_as_rate.s_relation_type%TYPE) IS
2431: v_org_unit_cd igs_ps_unit_ver_all.owner_org_unit_cd%TYPE;
2432: v_course_cd igs_fi_fee_as_rate.course_cd%TYPE;
2433: v_version_number igs_fi_fee_as_rate.version_number%TYPE;
2434: v_class_standing igs_fi_fee_as_rate.class_standing%TYPE;
2435: v_unit_set_cd igs_fi_fee_as_rate.unit_set_cd%TYPE := NULL;
2436: v_us_version_number igs_fi_fee_as_rate.us_version_number%TYPE := NULL;
2437:
2438: CURSOR c_ftfarv1(cp_v_s_relation_type igs_fi_fee_as_rate.s_relation_type%TYPE) IS
2439: SELECT far.chg_rate,
2432: v_course_cd igs_fi_fee_as_rate.course_cd%TYPE;
2433: v_version_number igs_fi_fee_as_rate.version_number%TYPE;
2434: v_class_standing igs_fi_fee_as_rate.class_standing%TYPE;
2435: v_unit_set_cd igs_fi_fee_as_rate.unit_set_cd%TYPE := NULL;
2436: v_us_version_number igs_fi_fee_as_rate.us_version_number%TYPE := NULL;
2437:
2438: CURSOR c_ftfarv1(cp_v_s_relation_type igs_fi_fee_as_rate.s_relation_type%TYPE) IS
2439: SELECT far.chg_rate,
2440: far.s_relation_type,
2434: v_class_standing igs_fi_fee_as_rate.class_standing%TYPE;
2435: v_unit_set_cd igs_fi_fee_as_rate.unit_set_cd%TYPE := NULL;
2436: v_us_version_number igs_fi_fee_as_rate.us_version_number%TYPE := NULL;
2437:
2438: CURSOR c_ftfarv1(cp_v_s_relation_type igs_fi_fee_as_rate.s_relation_type%TYPE) IS
2439: SELECT far.chg_rate,
2440: far.s_relation_type,
2441: far.location_cd,
2442: far.attendance_type,
2454: far.unit_mode,
2455: far.unit_cd,
2456: far.unit_version_number,
2457: far.unit_level
2458: FROM igs_fi_fee_as_rate far,
2459: igs_fi_f_cat_fee_lbl_all fcfl
2460: WHERE far.s_relation_type = cp_v_s_relation_type
2461: AND far.logical_delete_dt is NULL
2462: AND fcfl.fee_cat = far.fee_cat
2468: AND far.fee_ci_sequence_number = p_fee_ci_sequence_number
2469: AND far.fee_cat = p_fee_cat
2470: ORDER BY far.order_of_precedence ASC;
2471:
2472: CURSOR c_ftfarv2(cp_v_s_relation_type igs_fi_fee_as_rate.s_relation_type%TYPE) IS
2473: SELECT far.chg_rate,
2474: far.s_relation_type,
2475: far.location_cd,
2476: far.attendance_type,
2488: far.unit_mode,
2489: far.unit_cd,
2490: far.unit_version_number,
2491: far.unit_level
2492: FROM igs_fi_fee_as_rate far,
2493: igs_fi_f_cat_fee_lbl_all fcfl
2494: WHERE far.s_relation_type = cp_v_s_relation_type
2495: AND far.logical_delete_dt is NULL
2496: AND fcfl.fee_type = far.fee_type
2818:
2819: l_n_record_found NUMBER; /* Used to check whether global element ranges are defined or not*/
2820: l_n_sub_rec_found NUMBER; /* Used to check whether sub ranges are defined or not*/
2821: l_n_match_elm_rng NUMBER; /* Used to check whether global element range matches*/
2822: l_n_charge_rate igs_fi_fee_as_rate.chg_rate%TYPE;
2823: l_b_rate_matched BOOLEAN; /* To check whether rate matches*/
2824: l_v_fee_category igs_fi_f_cat_ca_inst.fee_cat%TYPE;
2825: l_v_derived_location_cd igs_fi_fee_as_rate.location_cd%TYPE;
2826: l_b_elm_range_rate_match BOOLEAN := FALSE; /* To check whether global element range rate matches or not*/
2821: l_n_match_elm_rng NUMBER; /* Used to check whether global element range matches*/
2822: l_n_charge_rate igs_fi_fee_as_rate.chg_rate%TYPE;
2823: l_b_rate_matched BOOLEAN; /* To check whether rate matches*/
2824: l_v_fee_category igs_fi_f_cat_ca_inst.fee_cat%TYPE;
2825: l_v_derived_location_cd igs_fi_fee_as_rate.location_cd%TYPE;
2826: l_b_elm_range_rate_match BOOLEAN := FALSE; /* To check whether global element range rate matches or not*/
2827:
2828: -- Cursor to find all Global Element ranges defined for the input parameter combination.
2829: CURSOR c_elm_ranges ( cp_v_fee_type igs_fi_f_typ_ca_inst_all.fee_type%TYPE,
2852: cp_n_range_number igs_fi_elm_range.range_number%TYPE,
2853: cp_v_relation_type igs_fi_elm_range.s_relation_type%TYPE) IS
2854: SELECT far.*
2855: FROM igs_fi_elm_range_rt err,
2856: igs_fi_fee_as_rate far
2857: WHERE far.fee_type = err.fee_type
2858: AND far.fee_cal_type = err.fee_cal_type
2859: AND far.fee_ci_sequence_number = err.fee_ci_sequence_number
2860: AND far.rate_number = err.rate_number
2880: -- Cursor to find all rates defined under sub element range found in above cursor.
2881: CURSOR cur_sub_elm_rng_rates (cp_n_sub_er_id igs_fi_sub_er_rt.sub_er_id%TYPE) IS
2882: SELECT far.*
2883: FROM igs_fi_sub_er_rt ser,
2884: igs_fi_fee_as_rate far
2885: WHERE ser.sub_er_id = cp_n_sub_er_id
2886: AND far.far_id = ser.far_id
2887: AND ser.logical_delete_date IS NULL
2888: AND far.logical_delete_dt IS NULL
5104: l_v_unit_mode igs_as_unit_class_all.unit_mode%TYPE;
5105: l_v_unit_cd igs_ps_unit_ofr_opt_all.unit_cd%TYPE;
5106: l_n_version_num igs_ps_unit_ofr_opt_all.version_number%TYPE;
5107: l_n_amount igs_fi_fee_as_items.amount%TYPE;
5108: l_v_charge_rate igs_fi_fee_as_rate.chg_rate%TYPE;
5109: l_n_uoo_id igs_fi_fee_as_items.uoo_id%TYPE;
5110: l_n_old_amount igs_fi_fee_as_items.amount%TYPE;
5111: l_d_disc_dt igs_en_su_attempt_all.enrolled_dt%TYPE;
5112: l_d_enr_dt igs_en_su_attempt_all.enrolled_dt%TYPE;
5649: p_fee_ci_sequence_number igs_fi_f_cat_fee_lbl_all.fee_ci_sequence_number%TYPE,
5650: p_s_fee_trigger_cat igs_fi_fee_type_all.s_fee_trigger_cat%TYPE,
5651: p_fee_cat igs_en_stdnt_ps_att_all.fee_cat%TYPE,
5652: p_currency igs_fi_control_all.currency_cd%TYPE,
5653: p_charge_rate igs_fi_fee_as_rate.chg_rate%TYPE,
5654: p_s_fee_type igs_fi_fee_type_all.s_fee_type%TYPE,
5655: p_effective_dt DATE,
5656: p_trace_on VARCHAR2,
5657: p_crs_version_number igs_fi_fee_as_items.crs_version_number%TYPE,
5714: DECLARE
5715: e_one_record_expected EXCEPTION;
5716:
5717: cst_assessment CONSTANT igs_fi_fee_as_all.s_transaction_type%TYPE := 'ASSESSMENT';
5718: v_chg_rate IGS_FI_FEE_AS_RATE.chg_rate%TYPE;
5719: v_chg_elements NUMBER;
5720: v_course_cd igs_en_stdnt_ps_att_all.course_cd%TYPE;
5721: v_message_name VARCHAR2(30);
5722:
6980: l_b_rule BOOLEAN;
6981: l_v_unit_cd igs_ps_unit_ofr_opt_all.unit_cd%TYPE;
6982: l_n_version_num igs_ps_unit_ofr_opt_all.version_number%TYPE;
6983: l_v_ret_level igs_fi_f_typ_ca_inst_all.retention_level_code%TYPE;
6984: l_v_class_standing igs_fi_fee_as_rate.class_standing%TYPE;
6985: l_v_unit_set_cd igs_as_su_setatmpt.unit_set_cd%TYPE;
6986: l_n_us_version_num igs_as_su_setatmpt.us_version_number%TYPE;
6987:
6988:
7759: BEGIN
7760: DECLARE
7761: cst_completed CONSTANT igs_en_stdnt_ps_att_all.course_attempt_status%TYPE := 'COMPLETED';
7762: v_next_fcfl_flag BOOLEAN;
7763: v_charge_rate IGS_FI_FEE_AS_RATE.chg_rate%TYPE;
7764: v_charge_elements igs_fi_fee_as_all.chg_elements%TYPE;
7765: v_fee_assessment NUMBER;
7766: v_fee_cat IGS_FI_F_CAT_CA_INST.FEE_CAT%TYPE;
7767: v_fee_cal_type IGS_FI_F_CAT_CA_INST.fee_cal_type%TYPE;