[Home] [Help]
238: p_v_fee_cal_type IN igs_fi_f_typ_ca_inst.fee_cal_type%TYPE,
239: p_v_fee_cat IN igs_fi_fee_as_items.fee_cat%TYPE,
240: p_n_fee_ci_sequence_number IN igs_fi_f_typ_ca_inst.fee_ci_sequence_number%TYPE,
241: p_v_fee_type IN igs_fi_fee_type.fee_type%TYPE,
242: p_n_uoo_id IN igs_ps_unit_ofr_opt.uoo_id%TYPE,
243: p_v_transaction_type IN igs_fi_inv_int_all.transaction_type%TYPE,
244: p_n_invoice_id IN igs_fi_inv_int_all.invoice_id%TYPE,
245: p_v_invoice_num OUT NOCOPY igs_fi_inv_int_all.invoice_number%TYPE,
246: p_b_chg_decl_rev OUT NOCOPY BOOLEAN
270: cp_fee_cal_type igs_fi_f_typ_ca_inst.fee_cal_type%TYPE,
271: cp_fee_cat igs_fi_fee_as_items.fee_cat%TYPE,
272: cp_fee_ci_sequence_number igs_fi_f_typ_ca_inst.fee_ci_sequence_number%TYPE,
273: cp_fee_type igs_fi_fee_type.fee_type%TYPE,
274: cp_uoo_id igs_ps_unit_ofr_opt.uoo_id%TYPE,
275: cp_transaction_type igs_fi_inv_int_all.transaction_type%TYPE) IS
276: SELECT inv.invoice_number
277: FROM igs_fi_inv_int inv,
278: igs_fi_invln_int invln
639: cp_v_nz_billable_cp_flag igs_fi_f_typ_ca_inst_all.nonzero_billable_cp_flag%TYPE
640: ) IS
641: SELECT sua.*, usec.non_std_usec_ind
642: FROM igs_en_su_attempt sua,
643: igs_ps_unit_ofr_opt usec
644: WHERE sua.person_id = cp_n_person_id
645: AND sua.course_cd = cp_v_course_cd
646: AND usec.uoo_id = sua.uoo_id
647: AND sua.unit_attempt_status NOT IN ('INVALID','DUPLICATE')
672: AND fee_ci_sequence_number IS NULL
673: AND TRUNC(dai_alias_val) <= TRUNC(cp_d_disc_dt);
674:
675: -- Cursor to fetch retention schedules defined at Unit Section + Fee Type level
676: CURSOR cur_ns_ft_ret(cp_n_uoo_id igs_ps_unit_ofr_opt_all.uoo_id%TYPE,
677: cp_v_fee_type igs_fi_fee_type.fee_type%TYPE) IS
678: SELECT dtl.offset_date
679: FROM igs_ps_nsus_rtn_dtl dtl,
680: igs_ps_nsus_rtn rtn
684: ORDER BY dtl.offset_date;
685:
686: -- Cursor to fetch retention schedules defined at Unit Section level.
687: -- This cursor is used when cur_ns_ft_ret does not find any rows
688: CURSOR cur_ns_usec_ret(cp_n_uoo_id igs_ps_unit_ofr_opt_all.uoo_id%TYPE) IS
689: SELECT dtl.offset_date
690: FROM igs_ps_nsus_rtn_dtl dtl,
691: igs_ps_nsus_rtn rtn
692: WHERE rtn.non_std_usec_rtn_id = dtl.non_std_usec_rtn_id
695: ORDER BY dtl.offset_date;
696:
697: -- Cursor to fetch retention schedules defined at Institution level.
698: -- Used if cur_ns_ft_ret and cur_ns_usec_ret both do not return any rows
699: CURSOR cur_ns_inst_ret(cp_n_uoo_id igs_ps_unit_ofr_opt_all.uoo_id%TYPE) IS
700: SELECT igs_ps_gen_004.f_retention_offset_date(cp_n_uoo_id,
701: rtn.formula_method,
702: rtn.round_method,
703: rtn.incl_wkend_duration_flag,
826: END IF;
827:
828: END get_complete_withdr_ret_amt;
829:
830: FUNCTION get_ns_usec_retention(p_n_uoo_id IN igs_ps_unit_ofr_opt_all.uoo_id%TYPE,
831: p_v_fee_type IN igs_fi_fee_type.fee_type%TYPE,
832: p_d_effective_date IN DATE,
833: p_n_diff_amount IN NUMBER) RETURN NUMBER IS
834: /**************************************************************************
848: TYPE ns_usec_retention_tbl_typ IS TABLE OF ns_usec_retention_rec INDEX BY BINARY_INTEGER;
849: ns_usec_retention_tbl ns_usec_retention_tbl_typ;
850:
851: -- Cursor to fetch retention schedules defined at Unit Section + Fee Type level
852: CURSOR cur_ns_ft_ret(cp_n_uoo_id igs_ps_unit_ofr_opt_all.uoo_id%TYPE,
853: cp_v_fee_type igs_fi_fee_type.fee_type%TYPE) IS
854: SELECT dtl.offset_date,
855: dtl.retention_amount,
856: dtl.retention_percent
862: ORDER BY offset_date;
863:
864: -- Cursor to fetch retention schedules defined at Unit Section level.
865: -- This cursor is used when cur_ns_ft_ret does not find any rows
866: CURSOR cur_ns_usec_ret(cp_n_uoo_id igs_ps_unit_ofr_opt_all.uoo_id%TYPE) IS
867: SELECT dtl.offset_date,
868: dtl.retention_amount,
869: dtl.retention_percent
870: FROM igs_ps_nsus_rtn_dtl dtl,
875: ORDER BY offset_date;
876:
877: -- Cursor to fetch retention schedules defined at Institution level.
878: -- Used if cur_ns_ft_ret and cur_ns_usec_ret both do not return any rows
879: CURSOR cur_ns_inst_ret(cp_n_uoo_id igs_ps_unit_ofr_opt_all.uoo_id%TYPE) IS
880: SELECT dtl.retention_amount,
881: dtl.retention_percent,
882: igs_ps_gen_004.f_retention_offset_date(cp_n_uoo_id,
883: rtn.formula_method,
971:
972: END get_ns_usec_retention;
973:
974:
975: FUNCTION get_special_retention_amt(p_n_uoo_id IN igs_ps_unit_ofr_opt_all.uoo_id%TYPE,
976: p_v_fee_cal_type IN igs_fi_f_typ_ca_inst_all.fee_cal_type%TYPE,
977: p_n_fee_ci_sequence_number IN igs_fi_f_typ_ca_inst_all.fee_ci_sequence_number%TYPE,
978: p_v_fee_type IN igs_fi_f_typ_ca_inst_all.fee_type%TYPE,
979: p_d_effective_date IN DATE,
988: Who When What
989: **************************************************************************/
990:
991: -- Cursor to determine if a given Unit Section is Non-Standard
992: CURSOR cur_non_std_usec(cp_n_uoo_id igs_ps_unit_ofr_opt_all.uoo_id%TYPE) IS
993: SELECT cal_type,
994: ci_sequence_number,
995: non_std_usec_ind
996: FROM igs_ps_unit_ofr_opt
992: CURSOR cur_non_std_usec(cp_n_uoo_id igs_ps_unit_ofr_opt_all.uoo_id%TYPE) IS
993: SELECT cal_type,
994: ci_sequence_number,
995: non_std_usec_ind
996: FROM igs_ps_unit_ofr_opt
997: WHERE uoo_id = cp_n_uoo_id;
998:
999: rec_non_std_usec cur_non_std_usec%ROWTYPE;
1000: l_n_retention_amount NUMBER := 0.0;