[Home] [Help]
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