1 PACKAGE OKL_LOAN_AMORT_SCHEDULE_PVT AUTHID CURRENT_USER AS
2 /* $Header: OKLRLASS.pls 120.3 2008/02/09 00:11:48 sechawla noship $ */
3 ------------------------------------------------------------------------------
4 -- Global Variables
5 ------------------------------------------------------------------------------
6 G_PKG_NAME CONSTANT VARCHAR2(200) := 'OKL_LOAN_AMORT_SCHEDULE_PVT';
7 G_APP_NAME CONSTANT VARCHAR2(3) := OKL_API.G_APP_NAME;
8 G_UNEXPECTED_ERROR CONSTANT VARCHAR2(200) := 'OKL_CONTRACTS_UNEXPECTED_ERROR';
9 G_SQLERRM_TOKEN CONSTANT VARCHAR2(200) := 'SQLERRM';
10 G_SQLCODE_TOKEN CONSTANT VARCHAR2(200) := 'SQLCODE';
11
12 G_REPORT_TYPE_SUMMARY CONSTANT VARCHAR2(30) := 'SUMMARY';
13 G_REPORT_TYPE_DETAIL CONSTANT VARCHAR2(30) := 'DETAIL';
14
15 G_RRM_ACTUAL CONSTANT VARCHAR2(30) := 'ACTUAL';
16 G_RRM_STREAMS CONSTANT VARCHAR2(30) := 'STREAMS';
17 G_RRM_ESTIMATED_AND_BILLED CONSTANT VARCHAR2(30) := 'ESTIMATED_AND_BILLED';
18
19 G_ICB_FIXED CONSTANT VARCHAR2(30) := 'FIXED';
20 G_ICB_FLOAT CONSTANT VARCHAR2(30) := 'FLOAT';
21 G_ICB_REAMORT CONSTANT VARCHAR2(30) := 'REAMORT';
22 G_ICB_CATCHUP_CLEANUP CONSTANT VARCHAR2(30) := 'CATCHUP/CLEANUP';
23
24 G_BILLED CONSTANT VARCHAR2(30) := 'B';
25 G_RECEIVED CONSTANT VARCHAR2(30) := 'R';
26 G_PROJECTED CONSTANT VARCHAR2(30) := 'P';
27 G_REBOOK CONSTANT VARCHAR2(30) := 'RBK';
28 G_PPD CONSTANT VARCHAR2(30) := 'PPD';
29
30 ------------------------------------------------------------------------------
31 -- Record Type
32 ------------------------------------------------------------------------------
33
34 TYPE amort_sched_rec_type IS RECORD (
35 start_date DATE,
36 end_date DATE,
37 loan_payment NUMBER,
38 principal NUMBER,
39 interest NUMBER,
40 principal_balance NUMBER,
41 payment_type VARCHAR2(30)
42 );
43
44 TYPE amort_sched_tbl_type is table of amort_sched_rec_type INDEX BY BINARY_INTEGER;
45
46 ---------------------------------------------------------------------------
47 -- Procedures and Functions
48 ---------------------------------------------------------------------------
49
50 -- Start of comments
51 --
52 -- API name : load_ln_actual_dtl
53 -- Pre-reqs : None
54 -- Function : This procedure loads the Amortization Schedule - Detail report
55 -- based on all receipts that have been processed by the Daily
56 -- Interest Program and projected payments for the remaining
57 -- loan term for the input contract. This schedule applies
58 -- to Loans with Revenue Recognition - ACTUAL
59 --
60 -- Parameters :
61 -- IN : p_api_version - Standard input parameter
62 -- p_init_msg_list - Standard input parameter
63 -- p_chr_id - Contract ID
64 -- Version : 1.0
65 -- History : rpillay created.
66 -- End of comments
67
68 PROCEDURE load_ln_actual_dtl(
69 p_api_version IN NUMBER,
70 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
71 x_return_status OUT NOCOPY VARCHAR2,
72 x_msg_count OUT NOCOPY NUMBER,
73 x_msg_data OUT NOCOPY VARCHAR2,
74 p_chr_id IN NUMBER,
75 x_proj_interest_rate OUT NOCOPY NUMBER,
76 x_amort_sched_tbl OUT NOCOPY amort_sched_tbl_type);
77
78 -- Start of comments
79 -- API name : load_ln_actual_summ
80 -- Pre-reqs : None
81 -- Function : This procedure loads the Amortization Schedule - Summary report
82 -- based on all receipts that have been processed by the Daily
83 -- Interest Program and projected payments for the remaining
84 -- loan term for the input contract. This schedule applies
85 -- to Loans with Revenue Recognition - ACTUAL
86 --
87 -- Parameters :
88 -- IN : p_api_version - Standard input parameter
89 -- p_init_msg_list - Standard input parameter
90 -- p_chr_id - Contract ID
91 -- Version : 1.0
92 -- History : rpillay created.
93 -- End of comments
94
95 PROCEDURE load_ln_actual_summ(
96 p_api_version IN NUMBER,
97 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
98 x_return_status OUT NOCOPY VARCHAR2,
99 x_msg_count OUT NOCOPY NUMBER,
100 x_msg_data OUT NOCOPY VARCHAR2,
101 p_chr_id IN NUMBER,
102 x_proj_interest_rate OUT NOCOPY NUMBER,
103 x_amort_sched_tbl OUT NOCOPY amort_sched_tbl_type);
104
105 -- Start of comments
106 --
107 -- API name : load_ln_streams_dtl
108 -- Pre-reqs : None
109 -- Function : This procedure loads the Amortization Schedule - Detail report- both past and projected,
110 -- based on the billed and unbilled stream elements for the input contract,
111 -- as of the date on which Amortization schedule is requested
112 -- This schedule applies to Loans with Interest Calculation Basis = FIXED' or 'REAMORT' and
113 -- Revenue Recognition - STREAMS
114 --
115 --
116 -- Parameters :
117 -- IN : p_api_version - Standard input parameter
118 -- p_init_msg_list - Standard input parameter
119 -- p_chr_id - Contract ID
120 --
121 -- Version : 1.0
122 -- History : sechawla created.
123 -- End of comments
124
125 PROCEDURE load_ln_streams_dtl(
126 p_api_version IN NUMBER,
127 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
128 x_return_status OUT NOCOPY VARCHAR2,
129 x_msg_count OUT NOCOPY NUMBER,
130 x_msg_data OUT NOCOPY VARCHAR2,
131 p_chr_id IN NUMBER,
132 x_amort_sched_tbl OUT NOCOPY amort_sched_tbl_type);
133
134 -- Start of comments
135 -- API name : load_ln_streams_summ
136 -- Pre-reqs : None
137 -- Function : This procedure loads the Amortization Schedule - Summary report- both past and projected,
138 -- based on the billed and unbilled stream elements for the input contract,
139 -- as of the date on which Amortization schedule is requested
140 -- This schedule applies to Loans with Interest Calculation Basis = FIXED' or 'REAMORT' and
141 -- Revenue Recognition - STREAMS
142 --
143 -- Parameters :
144 -- IN : p_api_version - Standard input parameter
145 -- p_init_msg_list - Standard input parameter
146 -- p_chr_id - Contract ID
147 -- Version : 1.0
148 -- History : sechawla created.
149 -- End of comments
150
151 PROCEDURE load_ln_streams_summ(
152 p_api_version IN NUMBER,
153 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
154 x_return_status OUT NOCOPY VARCHAR2,
155 x_msg_count OUT NOCOPY NUMBER,
156 x_msg_data OUT NOCOPY VARCHAR2,
157 p_chr_id IN NUMBER,
158 x_amort_sched_tbl OUT NOCOPY amort_sched_tbl_type);
159
160
161 -- Start of comments
162 --
163 -- API name : load_ln_float_eb_dtl
164 -- Pre-reqs : None
165 -- Function : This procedure loads the Amortization Schedule - Detail report
166 -- based on the billed and unbilled stream elements for the input contract,
167 -- as of the date on which Amortization schedule is requested.
168 -- This schedule applies to Loans with Interest Calculation Basis - FLOAT and
169 -- Revenue Recognition - ESTIMATED_AND_BILLED
170 --
171 -- Parameters :
172 -- IN : p_api_version - Standard input parameter
173 -- p_init_msg_list - Standard input parameter
174 -- p_chr_id - Contract ID
175 -- Version : 1.0
176 -- History : rpillay created.
177 -- End of comments
178
179 PROCEDURE load_ln_float_eb_dtl(
180 p_api_version IN NUMBER,
181 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
182 x_return_status OUT NOCOPY VARCHAR2,
183 x_msg_count OUT NOCOPY NUMBER,
184 x_msg_data OUT NOCOPY VARCHAR2,
185 p_chr_id IN NUMBER,
186 x_amort_sched_tbl OUT NOCOPY amort_sched_tbl_type);
187
188 -- Start of comments
189 --
190 -- API name : load_ln_float_eb_summ
191 -- Pre-reqs : None
192 -- Function : This procedure loads the Amortization Schedule - Summary report
193 -- based on the billed and unbilled stream elements for the input contract,
194 -- as of the date on which Amortization schedule is requested.
195 -- This schedule applies to Loans with Interest Calculation Basis - FLOAT and
196 -- Revenue Recognition - ESTIMATED_AND_BILLED
197 --
198 -- Parameters :
199 -- IN : p_api_version - Standard input parameter
200 -- p_init_msg_list - Standard input parameter
201 -- p_chr_id - Contract ID
202 -- Version : 1.0
203 -- History : rpillay created.
204 -- End of comments
205
206 PROCEDURE load_ln_float_eb_summ(
207 p_api_version IN NUMBER,
208 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
209 x_return_status OUT NOCOPY VARCHAR2,
210 x_msg_count OUT NOCOPY NUMBER,
211 x_msg_data OUT NOCOPY VARCHAR2,
212 p_chr_id IN NUMBER,
213 x_amort_sched_tbl OUT NOCOPY amort_sched_tbl_type);
214
215 -- Start of comments
216 --
217 -- API name : load_ln_cc_strm_dtl
218 -- Pre-reqs : None
219 -- Function : This procedure loads the Amortization Schedule - Detail report
220 -- based on the billed and unbilled stream elements for the input contract,
221 -- as of the date on which Amortization schedule is requested.
222 -- This schedule applies to Loans with Interest Calculation Basis -
223 -- CATCHUP/CLEANUP and Revenue Recognition - STREAMS
224 --
225 -- Parameters :
226 -- IN : p_api_version - Standard input parameter
227 -- p_init_msg_list - Standard input parameter
228 -- p_chr_id - Contract ID
229 -- Version : 1.0
230 -- History : rpillay created.
231 -- End of comments
232
233 PROCEDURE load_ln_cc_strm_dtl(
234 p_api_version IN NUMBER,
235 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
236 x_return_status OUT NOCOPY VARCHAR2,
237 x_msg_count OUT NOCOPY NUMBER,
238 x_msg_data OUT NOCOPY VARCHAR2,
239 p_chr_id IN NUMBER,
240 x_amort_sched_tbl OUT NOCOPY amort_sched_tbl_type);
241
242 -- Start of comments
243 --
244 -- API name : load_ln_cc_strm_summ
245 -- Pre-reqs : None
246 -- Function : This procedure loads the Amortization Schedule - Summary report
247 -- based on the billed and unbilled stream elements for the input contract,
248 -- as of the date on which Amortization schedule is requested.
249 -- This schedule applies to Loans with Interest Calculation Basis -
250 -- CATCHUP/CLEANUP and Revenue Recognition - STREAMS
251 --
252 -- Parameters :
253 -- IN : p_api_version - Standard input parameter
254 -- p_init_msg_list - Standard input parameter
255 -- p_chr_id - Contract ID
256 -- Version : 1.0
257 -- History : rpillay created.
258 -- End of comments
259
260 PROCEDURE load_ln_cc_strm_summ(
261 p_api_version IN NUMBER,
262 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
263 x_return_status OUT NOCOPY VARCHAR2,
264 x_msg_count OUT NOCOPY NUMBER,
265 x_msg_data OUT NOCOPY VARCHAR2,
266 p_chr_id IN NUMBER,
267 x_amort_sched_tbl OUT NOCOPY amort_sched_tbl_type);
268
269 -- Start of comments
270 -- API name : load_loan_amort_schedule
271 -- Pre-reqs : None
272 -- Function : This procedure loads the Amortization Schedule
273 -- for the input contract based on its Loan Product
274 --
275 -- Parameters :
276 -- IN : p_api_version - Standard input parameter
277 -- p_init_msg_list - Standard input parameter
278 -- p_chr_id - Contract ID
279 -- Version : 1.0
280 -- History : rpillay created.
281 -- End of comments
282
283 PROCEDURE load_loan_amort_schedule(
284 p_api_version IN NUMBER,
285 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
286 x_return_status OUT NOCOPY VARCHAR2,
287 x_msg_count OUT NOCOPY NUMBER,
288 x_msg_data OUT NOCOPY VARCHAR2,
289 p_chr_id IN NUMBER,
290 p_report_type IN VARCHAR2,
291 x_proj_interest_rate OUT NOCOPY NUMBER,
292 x_amort_sched_tbl OUT NOCOPY amort_sched_tbl_type);
293
294 END OKL_LOAN_AMORT_SCHEDULE_PVT;