1 PACKAGE igs_fi_gen_008 AS
2 /* $Header: IGSFI88S.pls 120.3 2006/05/16 22:58:52 abshriva ship $ */
3 /***********************************************************************************************
4
5 Created By : shtatiko
6 Date Created By: 25-AUG-2003 (Created as part of Enh# 3045007, Payment Plans)
7 Purpose : This package contains number of generic procedures called from various places
8 for Payment Plans Funtionality.
9
10
11 Known limitations,enhancements,remarks:
12 Change History
13 Who When What
14 abshriva 17-May-2006 Bug 5113295 - Added function chk_unit_prg_transfer
15 uudayapr 8-Oct-2005 BUG 4660773 Added the Function mask_card_number for masking the CC Number
16 agairola 27-Sep-2005 Bug # 4625955 Added new PLSQL procedure chk_spa_rec_exists
17 svuppala 16-May-2005 Bug # 4226849 Added New PLSQL function which will return the latest standard
18 balance of the student for the personid provided as input to it.
19 bannamal 14-Apr-2004 Bug#4297359 ER Registration fee issue.
20 Added the parameter p_v_nonzero_billable_cp_flag to the function
21 get_complete_withdr_ret_amt.
22 pathipat 03-Sep-2004 Enh 3880438 - Retention Enhancements
23 Added new functions.
24 rmaddipa 26-Jul-2004 Enh#3787816 Added chk_chg_adj as part of Manual Reversal Build
25 uudayapr 20-oct-2003 Enh#3117341 Added get_invoice_number fuction as a part of
26 audit and special Fees Build.
27 shtatiko 25-AUG-2003 Enh# 3045007, Created this package.
28 ***********************************************************************************************/
29
30 -- Procedure to get Payment Plan Details
31 PROCEDURE get_plan_details( p_n_person_id IN NUMBER, /* Person Id */
32 p_n_act_plan_id OUT NOCOPY NUMBER, /* Active Payment Plan Id */
33 p_v_act_plan_name OUT NOCOPY VARCHAR2 /* Active Payment Plan Name */
34 );
35
36 -- Fuction to get Balance Amount for a given Payment Plan as of given date.
37 FUNCTION get_plan_balance( p_n_act_plan_id IN NUMBER, /* Active Payment Plan Id */
38 p_d_effective_date IN DATE DEFAULT NULL /* Effective Date */
39 ) RETURN NUMBER;
40
41 -- Fucntion to check whether a give Payment Plan is active or not.
42 FUNCTION chk_active_pay_plan( p_n_person_id IN NUMBER ) RETURN VARCHAR2;
43
44 -- Function to get First Installment Date.
45 FUNCTION get_start_date ( p_d_start_date IN DATE, /* Start Date */
46 p_n_due_day IN NUMBER DEFAULT NULL, /* Due Day, if set in the Payment Plan Template */
47 p_v_last_day IN VARCHAR2, /* Last Day of the month, if set in the Payment Plan Template */
48 p_n_offset_days IN NUMBER DEFAULT NULL /* Offset Days as specified in the Payment Plan Template */
49 ) RETURN DATE;
50
51 -- Function that return Party Number for a given Party Id
52 FUNCTION get_party_number ( p_n_party_id IN NUMBER ) RETURN VARCHAR2;
53
54 --function for getting the charge number for the invoice id
55 FUNCTION get_invoice_number ( p_n_invoice_id IN PLS_INTEGER ) RETURN VARCHAR2;
56
57 --Procedure for checking whether a charge is declared or reversed
58 PROCEDURE chk_chg_adj( p_n_person_id IN hz_parties.party_id%TYPE,
59 p_v_location_cd IN igs_fi_fee_as_items.location_cd%TYPE,
60 p_v_course_cd IN igs_ps_ver.course_cd%TYPE,
61 p_v_fee_cal_type IN igs_fi_f_typ_ca_inst.fee_cal_type%TYPE,
62 p_v_fee_cat IN igs_fi_fee_as_items.fee_cat%TYPE,
63 p_n_fee_ci_sequence_number IN igs_fi_f_typ_ca_inst.fee_ci_sequence_number%TYPE,
64 p_v_fee_type IN igs_fi_fee_type.fee_type%TYPE,
65 p_n_uoo_id IN igs_ps_unit_ofr_opt.uoo_id%TYPE,
66 p_v_transaction_type IN igs_fi_inv_int_all.transaction_type%TYPE,
67 p_n_invoice_id IN igs_fi_inv_int_all.invoice_id%TYPE,
68 p_v_invoice_num OUT NOCOPY igs_fi_inv_int_all.invoice_number%TYPE,
69 p_b_chg_decl_rev OUT NOCOPY BOOLEAN
70 );
71
72 -- Procedure to obtain values of columns retention_level_code and complete_ret_flag from table igs_fi_f_typ_ca_inst
73 -- to be used in determing Retention Amount.
74 PROCEDURE get_retention_params( p_v_fee_cal_type IN igs_fi_f_typ_ca_inst_all.fee_cal_type%TYPE,
75 p_n_fee_ci_sequence_number IN igs_fi_f_typ_ca_inst_all.fee_ci_sequence_number%TYPE,
76 p_v_fee_type IN igs_fi_f_typ_ca_inst_all.fee_type%TYPE,
77 p_v_ret_level OUT NOCOPY igs_fi_f_typ_ca_inst_all.retention_level_code%TYPE,
78 p_v_complete_withdr_ret OUT NOCOPY igs_fi_f_typ_ca_inst_all.complete_ret_flag%TYPE);
79
80 -- Function to determine the Retention Amount when the Retention Level is set to Teaching Period
81 FUNCTION get_teach_retention( p_v_fee_cal_type IN igs_fi_tp_ret_schd.fee_cal_type%TYPE,
82 p_n_fee_ci_sequence_number IN igs_fi_tp_ret_schd.fee_ci_sequence_number%TYPE,
83 p_v_fee_type IN igs_fi_tp_ret_schd.fee_type%TYPE,
84 p_v_teach_cal_type IN igs_fi_tp_ret_schd.teach_cal_type%TYPE,
85 p_n_teach_ci_sequence_number IN igs_fi_tp_ret_schd.teach_ci_sequence_number%TYPE,
86 p_d_effective_date IN DATE,
87 p_n_diff_amount IN NUMBER) RETURN NUMBER;
88
89 -- Function to determine the Retention Amount when the Retention Level is set to Fee Period
90 FUNCTION get_fee_retention_amount(p_v_fee_cat IN igs_fi_fee_ret_schd.fee_cat%TYPE,
91 p_v_fee_cal_type IN igs_fi_fee_ret_schd.fee_cal_type%TYPE,
92 p_n_fee_ci_sequence_number IN igs_fi_fee_ret_schd.fee_ci_sequence_number%TYPE,
93 p_v_fee_type IN igs_fi_fee_ret_schd.fee_type%TYPE,
94 p_n_diff_amount IN NUMBER) RETURN NUMBER;
95
96
97 -- Function to determine the Retention Amount when the Complete Withdrawal Retention Checkbox is checked for the FTCI
98 FUNCTION get_complete_withdr_ret_amt( p_n_person_id IN igs_en_su_attempt.person_id%TYPE,
99 p_v_course_cd IN igs_en_su_attempt.course_cd%TYPE,
100 p_v_load_cal_type IN igs_ca_inst.cal_type%TYPE,
101 p_n_load_ci_sequence_number IN igs_ca_inst.sequence_number%TYPE,
102 p_n_diff_amount IN NUMBER,
103 p_v_fee_type IN igs_fi_f_typ_ca_inst_all.fee_type%TYPE,
104 p_v_nonzero_billable_cp_flag IN igs_fi_f_typ_ca_inst_all.nonzero_billable_cp_flag%TYPE ) RETURN NUMBER;
105
106 -- Function to determine the Retention Amount for a Non-Standard Unit Section
107 FUNCTION get_ns_usec_retention(p_n_uoo_id IN igs_ps_unit_ofr_opt_all.uoo_id%TYPE,
108 p_v_fee_type IN igs_fi_fee_type.fee_type%TYPE,
109 p_d_effective_date IN DATE,
110 p_n_diff_amount IN NUMBER) RETURN NUMBER;
111
112 -- Function to determine the Retention Amount for Special Fees
113 FUNCTION get_special_retention_amt(p_n_uoo_id IN igs_ps_unit_ofr_opt_all.uoo_id%TYPE,
114 p_v_fee_cal_type IN igs_fi_f_typ_ca_inst_all.fee_cal_type%TYPE,
115 p_n_fee_ci_sequence_number IN igs_fi_f_typ_ca_inst_all.fee_ci_sequence_number%TYPE,
116 p_v_fee_type IN igs_fi_f_typ_ca_inst_all.fee_type%TYPE,
117 p_d_effective_date IN DATE,
118 p_n_diff_amount IN NUMBER) RETURN NUMBER;
119
120 -- function to return the latest standard balance of the student for the personid provided
121 FUNCTION get_std_balance(p_partyid IN igs_fi_balances.party_id%TYPE) RETURN NUMBER;
122
123 -- Procedure for checking if Term Record exists
124 PROCEDURE chk_spa_rec_exists(p_n_person_id IN igs_en_stdnt_ps_att.person_id%TYPE,
125 p_v_course_cd IN igs_en_stdnt_ps_att.course_cd%TYPE,
126 p_v_load_cal_type IN igs_ca_inst.cal_type%TYPE,
127 p_n_load_ci_seq IN igs_ca_inst.sequence_number%TYPE,
128 p_v_fee_cat IN igs_fi_fee_cat.fee_cat%TYPE,
129 p_v_status OUT NOCOPY VARCHAR2,
130 p_v_message OUT NOCOPY VARCHAR2);
131
132 --Function for masking the Credit Card Number
133 FUNCTION mask_card_number( p_credit_card IN VARCHAR2 ) RETURN VARCHAR2;
134
135 -- Function to check if the Unit in context has been part of a Program Transfer or not.
136 -- Returns Y or N
137 FUNCTION chk_unit_prg_transfer(p_v_disc_reason_code IN igs_en_dcnt_reasoncd.discontinuation_reason_cd%TYPE) RETURN VARCHAR2;
138
139 END igs_fi_gen_008;