1 PACKAGE LNS_FEE_ENGINE AUTHID CURRENT_USER AS
2 /* $Header: LNS_FEE_ENGINE_S.pls 120.9 2011/05/23 22:02:05 scherkas ship $ */
3 /*========================================================================+
4 | Declare PUBLIC Data Types and Variables
5 +========================================================================*/
6
7
8 TYPE FEE_BASIS_REC IS RECORD(FEE_BASIS_NAME VARCHAR2(30)
9 ,FEE_BASIS_AMOUNT NUMBER);
10
11 TYPE FEE_BASIS_TBL IS TABLE OF FEE_BASIS_REC INDEX BY BINARY_INTEGER;
12
13 TYPE FEE_STRUCTURE_REC IS RECORD(FEE_ID NUMBER
14 ,FEE_NAME VARCHAR2(50)
15 ,FEE_DESCRIPTION VARCHAR2(250)
16 ,FEE_CATEGORY VARCHAR2(30)
17 ,FEE_TYPE VARCHAR2(30)
18 ,FEE_AMOUNT NUMBER
19 ,FEE_BASIS VARCHAR2(30) -- amount to calculate from
20 ,START_DATE_ACTIVE DATE
21 ,END_DATE_ACTIVE DATE
22 ,NUMBER_GRACE_DAYS NUMBER
23 ,FEE_BILLING_OPTION VARCHAR2(30)
24 ,FEE_RATE_TYPE VARCHAR2(30) -- fixed / variable
25 ,FEE_FROM_INSTALLMENT NUMBER
26 ,FEE_TO_INSTALLMENT NUMBER
27 ,FEE_WAIVABLE_FLAG VARCHAR2(1)
28 ,FEE_EDITABLE_FLAG VARCHAR2(1)
29 ,FEE_DELETABLE_FLAG VARCHAR2(1)
30 ,MINIMUM_OVERDUE_AMOUNT NUMBER
31 ,FEE_BASIS_RULE VARCHAR2(30)
32 ,CURRENCY_CODE VARCHAR2(15)
33 ,DISB_HEADER_ID NUMBER
34 ,DISBURSEMENT_AMOUNT NUMBER
35 ,DISBURSEMENT_DATE DATE
36 ,PHASE VARCHAR2(30)
37 ,CUSTOM_PROCEDURE VARCHAR2(250)
38 );
39
40 TYPE FEE_STRUCTURE_TBL IS TABLE OF FEE_STRUCTURE_REC INDEX BY BINARY_INTEGER;
41
42 -- LNS.B additions
43 TYPE FEE_CALC_REC IS RECORD(FEE_ID NUMBER
44 ,FEE_NAME VARCHAR2(50)
45 ,FEE_CATEGORY VARCHAR2(30)
46 ,FEE_TYPE VARCHAR2(30)
47 ,FEE_AMOUNT NUMBER
48 ,FEE_INSTALLMENT NUMBER
49 ,FEE_DESCRIPTION VARCHAR2(250)
50 ,FEE_SCHEDULE_ID NUMBER
51 ,FEE_WAIVABLE_FLAG VARCHAR2(1)
52 ,FEE_EDITABLE_FLAG VARCHAR2(1)
53 ,FEE_DELETABLE_FLAG VARCHAR2(1)
54 ,WAIVE_AMOUNT NUMBER
55 ,BILLED_FLAG VARCHAR2(1)
56 ,ACTIVE_FLAG VARCHAR2(1)
57 ,DISB_HEADER_ID NUMBER
58 ,FEE_BILLING_OPTION VARCHAR2(30)
59 ,PHASE VARCHAR2(30)
60 );
61
62 TYPE FEE_CALC_TBL IS TABLE OF FEE_CALC_REC INDEX BY BINARY_INTEGER;
63
64 procedure processDisbursementFees(p_init_msg_list in varchar2
65 ,p_commit in varchar2
66 ,p_phase in varchar2
67 ,p_loan_id in number
68 ,p_disb_head_id in number
69 ,x_return_status out nocopy varchar2
70 ,x_msg_count out nocopy number
71 ,x_msg_data out nocopy varchar2);
72
73
74 -- LNS.B functions
75 procedure reprocessFees(p_init_msg_list in varchar2
76 ,p_commit in varchar2
77 ,p_loan_id in number
78 ,p_installment_number in number
79 ,p_phase in varchar2
80 ,x_return_status out nocopy varchar2
81 ,x_msg_count out nocopy number
82 ,x_msg_data out nocopy varchar2);
83
84 function getFeeStructures(p_loan_id in number
85 ,p_fee_category in varchar2
86 ,p_fee_type in varchar2
87 ,p_installment in number
88 ,p_phase in varchar2
89 ,p_fee_id in number
90 ,p_billing_option in varchar2 DEFAULT NULL) return LNS_FEE_ENGINE.FEE_STRUCTURE_TBL;
91
92 function getDisbursementFeeStructures(p_loan_id in number
93 ,p_installment_no in number
94 ,p_phase in varchar2
95 ,p_disb_header_id in number
96 ,p_fee_id in number) return LNS_FEE_ENGINE.FEE_STRUCTURE_TBL;
97
98 function calculateFee(p_fee_id in number
99 ,p_disb_header_id in number
100 ,p_loan_id in number) return number;
101
102 function calculateFee(p_fee_id IN NUMBER
103 ,p_loan_id IN NUMBER
104 ,p_phase IN VARCHAR2) return number;
105
106 procedure calculateFees(p_loan_id in number
107 ,p_fee_basis_tbl in LNS_FEE_ENGINE.FEE_BASIS_TBL
108 ,p_installment in number
109 ,p_fee_structures IN LNS_FEE_ENGINE.FEE_STRUCTURE_TBL
110 ,x_fees_tbl OUT nocopy LNS_FEE_ENGINE.FEE_CALC_TBL
111 ,x_return_status out nocopy varchar2
112 ,x_msg_count out nocopy number
113 ,x_msg_data out nocopy varchar2);
114
115 procedure writeFeeSchedule(p_init_msg_list in varchar2
116 ,p_commit in varchar2
117 ,p_loan_id in number
118 ,p_fees_tbl IN OUT NOCOPY LNS_FEE_ENGINE.FEE_CALC_TBL
119 ,x_return_status out nocopy varchar2
120 ,x_msg_count out nocopy number
121 ,x_msg_data out nocopy varchar2);
122
123 procedure updateFeeSchedule(p_init_msg_list in varchar2
124 ,p_commit in varchar2
125 ,p_loan_id in number
126 ,p_fees_tbl IN LNS_FEE_ENGINE.FEE_CALC_TBL
127 ,x_return_status out nocopy varchar2
128 ,x_msg_count out nocopy number
129 ,x_msg_data out nocopy varchar2);
130
131 procedure getFeeSchedule(p_init_msg_list in varchar2
132 ,p_loan_id in number
133 ,p_installment_number in number
134 ,p_disb_header_id in number
135 ,p_phase in varchar2
136 ,x_fees_tbl OUT NOCOPY LNS_FEE_ENGINE.FEE_CALC_TBL
137 ,x_return_status out nocopy varchar2
138 ,x_msg_count out nocopy number
139 ,x_msg_data out nocopy varchar2);
140
141 -- karthik UI for fee details
142 procedure getFeeDetails(p_init_msg_list in varchar2
143 ,p_loan_id in number
144 ,p_installment in number
145 ,p_fee_basis_tbl in LNS_FEE_ENGINE.FEE_BASIS_TBL
146 ,p_based_on_terms in varchar2
147 ,p_phase in varchar2
148 ,x_fees_tbl out nocopy LNS_FEE_ENGINE.FEE_CALC_TBL
149 ,x_return_status out nocopy varchar2
150 ,x_msg_count out nocopy number
151 ,x_msg_data out nocopy varchar2);
152
153 procedure processFees(p_init_msg_list in varchar2
154 ,p_commit in varchar2
155 ,p_loan_id in number
156 ,p_installment_number in number
157 ,p_fee_basis_tbl in LNS_FEE_ENGINE.FEE_BASIS_TBL
158 ,p_fee_structures in LNS_FEE_ENGINE.FEE_STRUCTURE_TBL
159 ,x_fees_tbl out NOCOPY LNS_FEE_ENGINE.FEE_CALC_TBL
160 ,x_return_status out nocopy varchar2
161 ,x_msg_count out nocopy number
162 ,x_msg_data out nocopy varchar2);
163
164 procedure processLateFees(p_init_msg_list in varchar2
165 ,p_commit in varchar2
166 ,p_loan_id in number
167 ,p_phase in varchar2
168 ,x_return_status out nocopy varchar2
169 ,x_msg_count out nocopy number
170 ,x_msg_data out nocopy varchar2);
171
172 /*
173 -- do if update
174 procedure updateFee(p_init_msg_list in varchar2
175 ,p_commit in varchar2
176 ,p_loan_id in number
177 ,p_fee_schedule_id in number
178 ,p_update_amount in number
179 ,x_return_status out nocopy varchar2
180 ,x_msg_count out nocopy number
181 ,x_msg_data out nocopy varchar2);
182 */
183 -- do if waive
184 procedure waiveFee(p_init_msg_list in varchar2
185 ,p_commit in varchar2
186 ,p_loan_id in number
187 ,p_fee_schedule_id in number
188 ,p_waive_amount in number
189 ,x_return_status out nocopy varchar2
190 ,x_msg_count out nocopy number
191 ,x_msg_data out nocopy varchar2);
192
193 -- for future use
194 function getFeesTotal(p_loan_id in number
195 ,p_fee_category in varchar2
196 ,p_fee_type in varchar2
197 ,p_billed_flag in varchar2
198 ,p_waived_flag in varchar2) return number;
199
200 PROCEDURE LOAN_LATE_FEES_CONCUR(ERRBUF OUT NOCOPY VARCHAR2
201 ,RETCODE OUT NOCOPY VARCHAR2
202 ,P_BORROWER_ID IN NUMBER
203 ,P_LOAN_ID IN NUMBER);
204
205
206 procedure getSubmitForApprFeeSchedule(p_init_msg_list in varchar2
207 ,p_loan_id in number
208 ,p_billed_flag in varchar2
209 ,x_fees_tbl OUT NOCOPY LNS_FEE_ENGINE.FEE_CALC_TBL
210 ,x_return_status out nocopy varchar2
211 ,x_msg_count out nocopy number
212 ,x_msg_data out nocopy varchar2);
213
214 /*=========================================================================
215 || PUBLIC PROCEDURE SET_DISB_FEES_INSTALL
216 ||
217 || DESCRIPTION
218 || Overview: this procedure will update the feeInstallments(begin and end) for the given disb_header_id
219 ||
220 ||
221 || PSEUDO CODE/LOGIC
222 ||
223 || PARAMETERS
224 || Parameter: p_disb_header_id => disbursement header id
225 ||
226 || Return value:
227 || standard
228 || KNOWN ISSUES
229 ||
230 || NOTES
231 ||
232 || MODIFICATION HISTORY
233 || Date Author Description of Changes
234 || 16-FEB-2010 mbolli Bug#9255294 - Created
235 *=======================================================================*/
236 procedure SET_DISB_FEES_INSTALL(p_init_msg_list in varchar2
237 ,p_disb_header_id in varchar2
238 ,x_return_status out nocopy varchar2
239 ,x_msg_count out nocopy number
240 ,x_msg_data out nocopy varchar2);
241
242
243 end LNS_FEE_ENGINE;