[Home] [Help]
PACKAGE BODY: APPS.OKL_VAR_INT_REAMORT_PVT
Source
1 PACKAGE BODY OKL_VAR_INT_REAMORT_PVT AS
2 /* $Header: OKLRVRMB.pls 120.0.12020000.6 2013/02/22 23:04:27 rpillay noship $ */
3
4
5 TYPE line_principal_rec_type IS RECORD (
6 kle_id OKL_K_LINES.id%TYPE,
7 principal_balance OKL_STRM_ELEMENTS.amount%TYPE);
8
9 TYPE line_principal_tbl_type is TABLE of line_principal_rec_type INDEX BY BINARY_INTEGER;
10
11
12 FUNCTION set_value_null(p_value_in IN VARCHAR2) RETURN VARCHAR2 IS
13 BEGIN
14 IF (p_value_in = OKL_API.G_MISS_CHAR) THEN
15 RETURN NULL;
16 END IF;
17 RETURN p_value_in;
18 END;
19
20
21 FUNCTION set_value_null(p_value_in IN DATE) RETURN DATE IS
22 BEGIN
23 IF (p_value_in = OKL_API.G_MISS_DATE) THEN
24 RETURN NULL;
25 END IF;
26 RETURN p_value_in;
27 END;
28
29
30 FUNCTION set_value_null(p_value_in IN NUMBER) RETURN NUMBER IS
31 BEGIN
32 IF (p_value_in = OKL_API.G_MISS_NUM) THEN
33 RETURN NULL;
34 END IF;
35 RETURN p_value_in;
36 END;
37
38
39 --get the payment start date of skip and stub payments
40 FUNCTION get_pay_level_start_date(p_kle_id IN NUMBER,
41 p_sequence IN NUMBER) RETURN DATE IS
42 l_start_date DATE := NULL;
43
44 cursor l_start_date_csr(cp_kle_id IN NUMBER, cp_sequence IN NUMBER) IS select start_date from (
45 select rownum sequence, --this can replace the seq since seq num is not stored by authoring
46 A.* from
47 (Select sll.id,
48 styp.code payment_type,
49 sll.object1_id1 Pay_freq,
50 sll.rule_information1 seq,
51 fnd_date.canonical_to_date(sll.rule_information2) start_date,
52 sll.rule_information3 number_periods,
53 sll.rule_information4 tuoms_per_period,
54 sll.rule_information6 amount,
55 sll.rule_information7 stub_days,
56 sll.rule_information8 stub_amount,
57 sll.rule_information10 advance_or_arrears,
58 sll.rule_information13 rate,
59 rgp.cle_id cle_id
60 from okc_rules_b sll,
61 okc_rules_b slh,
62 okl_strm_type_v styp,
63 okc_rule_groups_b rgp
64 where to_number(sll.object2_id1) = slh.id
65 and sll.rule_information_category = 'LASLL'
66 and sll.dnz_chr_id = rgp.dnz_chr_id
67 and sll.rgp_id = rgp.id
68 and slh.rule_information_category = 'LASLH'
69 and slh.dnz_chr_id = rgp.dnz_chr_id
70 and slh.rgp_id = rgp.id
71 and slh.object1_id1 = styp.id
72 and rgp.rgd_code = 'LALEVL'
73 and rgp.cle_id = cp_kle_id
74 order by rgp.cle_id
75 , fnd_date.canonical_to_date(sll.rule_information2)) A
76 )
77 where sequence = cp_sequence;
78
79 BEGIN
80 OPEN l_start_date_csr (p_kle_id, p_sequence);
81 FETCH l_start_date_csr INTO l_start_date;
82 CLOSE l_start_date_csr;
83
84 return l_start_date;
85 END;
86
87
88
89 PROCEDURE print_loan_tables(p_rent_tbl IN OKL_VARIABLE_INTEREST_PVT.csm_periodic_expenses_tbl_type,
90 p_csm_loan_level_tbl IN OKL_VARIABLE_INTEREST_PVT.csm_loan_level_tbl_type) IS
91 l_api_name CONSTANT VARCHAR2(30) := 'print_loan_tables';
92 i NUMBER;
93 l_source Number;
94 l_rent_tbl OKL_VARIABLE_INTEREST_PVT.csm_periodic_expenses_tbl_type;
95 l_csm_loan_level_tbl OKL_VARIABLE_INTEREST_PVT.csm_loan_level_tbl_type;
96 BEGIN
97 l_rent_tbl := p_rent_tbl;
98 l_csm_loan_level_tbl := p_csm_loan_level_tbl;
99
100 OKL_VARIABLE_INTEREST_PVT.print_debug('*****************************************');
101 OKL_VARIABLE_INTEREST_PVT.print_debug('*******START CONTENTS OF P_RENT_TBL******');
102 i := l_rent_tbl.first;
103 loop
104 exit when i is null;
105 OKL_VARIABLE_INTEREST_PVT.print_debug('l_rent_tbl element # '|| i);
106 OKL_VARIABLE_INTEREST_PVT.print_debug('----------------------------------------------------');
107 OKL_VARIABLE_INTEREST_PVT.print_debug('sequence number of the payment level: '|| set_value_null(l_rent_tbl(i).level_index_number));
108 OKL_VARIABLE_INTEREST_PVT.print_debug('number of payments for a payment level: '|| set_value_null(l_rent_tbl(i).number_of_periods));
109 OKL_VARIABLE_INTEREST_PVT.print_debug('foreign key to table OKL_SIF_RETS: '||set_value_null(l_rent_tbl(i).sir_id));
110 OKL_VARIABLE_INTEREST_PVT.print_debug('reference to the asset index number: '||set_value_null(l_rent_tbl(i).index_number));
111 OKL_VARIABLE_INTEREST_PVT.print_debug('payment type: '||set_value_null(l_rent_tbl(i).level_type));
112 OKL_VARIABLE_INTEREST_PVT.print_debug('amount: '||set_value_null(l_rent_tbl(i).amount));
113 OKL_VARIABLE_INTEREST_PVT.print_debug('advance_or_arrears: '||set_value_null(l_rent_tbl(i).advance_or_arrears));
114 OKL_VARIABLE_INTEREST_PVT.print_debug('frequency: '||set_value_null(l_rent_tbl(i).period));
115 OKL_VARIABLE_INTEREST_PVT.print_debug('lock_level_step: '||set_value_null(l_rent_tbl(i).lock_level_step));
116 OKL_VARIABLE_INTEREST_PVT.print_debug('days in a payment period: '||set_value_null(l_rent_tbl(i).days_in_period));
117 OKL_VARIABLE_INTEREST_PVT.print_debug('first payment date for a payment level: '||set_value_null(l_rent_tbl(i).first_payment_date));
118 OKL_VARIABLE_INTEREST_PVT.print_debug('rate: '||set_value_null(l_rent_tbl(i).rate));
119 i := l_rent_tbl.next(i);
120 end loop;
121 OKL_VARIABLE_INTEREST_PVT.print_debug('*******END CONTENTS OF P_RENT_TBL********');
122 OKL_VARIABLE_INTEREST_PVT.print_debug('*****************************************');
123
124
125 OKL_VARIABLE_INTEREST_PVT.print_debug('*****************************************');
126 OKL_VARIABLE_INTEREST_PVT.print_debug('**START CONTENTS OF P_CSM_LOAN_LEVEL_TBL*');
127 i := l_csm_loan_level_tbl.first;
128 loop
129 exit when i is null;
130 OKL_VARIABLE_INTEREST_PVT.print_debug('l_csm_loan_level_tbl element # '|| i);
131 OKL_VARIABLE_INTEREST_PVT.print_debug('----------------------------------------------------');
132 OKL_VARIABLE_INTEREST_PVT.print_debug('description: '||set_value_null(l_csm_loan_level_tbl(i).description));
133 OKL_VARIABLE_INTEREST_PVT.print_debug('first payment date for a payment level: '||set_value_null(l_csm_loan_level_tbl(i).date_start));
134 OKL_VARIABLE_INTEREST_PVT.print_debug('asset line id: '||set_value_null(l_csm_loan_level_tbl(i).kle_loan_id));
135 OKL_VARIABLE_INTEREST_PVT.print_debug('sequence number of the payment level: '||set_value_null(l_csm_loan_level_tbl(i).level_index_number));
136 OKL_VARIABLE_INTEREST_PVT.print_debug('payment type: '||set_value_null(l_csm_loan_level_tbl(i).level_type));
137
138 --IF (l_csm_loan_level_tbl(i).level_type <> 'FUNDING') THEN
139 OKL_VARIABLE_INTEREST_PVT.print_debug('number of payments for a payment level: '||set_value_null(l_csm_loan_level_tbl(i).number_of_periods));
140 OKL_VARIABLE_INTEREST_PVT.print_debug('lock_level_step: '||set_value_null(l_csm_loan_level_tbl(i).lock_level_step));
141 OKL_VARIABLE_INTEREST_PVT.print_debug('frequency: '||set_value_null(l_csm_loan_level_tbl(i).period));
142 OKL_VARIABLE_INTEREST_PVT.print_debug('advance_or_arrears: '||set_value_null(l_csm_loan_level_tbl(i).advance_or_arrears));
143 OKL_VARIABLE_INTEREST_PVT.print_debug('income_or_expense: '||set_value_null(l_csm_loan_level_tbl(i).income_or_expense));
144 OKL_VARIABLE_INTEREST_PVT.print_debug('query_level_yn: '||set_value_null(l_csm_loan_level_tbl(i).query_level_yn));
145 OKL_VARIABLE_INTEREST_PVT.print_debug('payment_type: '||set_value_null(l_csm_loan_level_tbl(i).payment_type));
146 --END IF;
147 OKL_VARIABLE_INTEREST_PVT.print_debug('amount: '||set_value_null(l_csm_loan_level_tbl(i).amount));
148 OKL_VARIABLE_INTEREST_PVT.print_debug('rate: '||set_value_null(l_csm_loan_level_tbl(i).rate));
149 i := l_csm_loan_level_tbl.next(i);
150 end loop;
151 OKL_VARIABLE_INTEREST_PVT.print_debug('**END CONTENTS OF P_CSM_LOAN_LEVEL_TBL***');
152 OKL_VARIABLE_INTEREST_PVT.print_debug('*****************************************');
153 Exception
154 WHEN OTHERS THEN
155 OKL_VARIABLE_INTEREST_PVT.print_debug('error in procedure print_loan_tables');
156 OKL_VARIABLE_INTEREST_PVT.print_debug('sqlcode : ' || sqlcode || ' $ sqlerrm : ' || sqlerrm);
157 END print_loan_tables;
158
159
160
161 PROCEDURE print_lease_tables(p_rents_tbl_in IN Okl_Create_Streams_Pub.csm_periodic_expenses_tbl_type,
162 p_csm_line_details_tbl IN okl_create_streams_pvt.csm_line_details_tbl_type) IS
163 l_api_name CONSTANT VARCHAR2(30) := 'print_lease_tables';
164 i NUMBER;
165 l_source Number;
166 l_rents_tbl_in Okl_Create_Streams_Pub.csm_periodic_expenses_tbl_type;
167 l_csm_line_details_tbl okl_create_streams_pvt.csm_line_details_tbl_type;
168 BEGIN
169 l_rents_tbl_in := p_rents_tbl_in;
170 l_csm_line_details_tbl := p_csm_line_details_tbl;
171 OKL_VARIABLE_INTEREST_PVT.print_debug('******************************************');
172 OKL_VARIABLE_INTEREST_PVT.print_debug('*****START CONTENTS OF P_RENTS_TBL_IN*****');
173 i := l_rents_tbl_in.first;
174 loop
175 exit when i is null;
176 OKL_VARIABLE_INTEREST_PVT.print_debug('l_rents_tbl_in element # '|| i);
177 OKL_VARIABLE_INTEREST_PVT.print_debug('----------------------------------------------------');
178 OKL_VARIABLE_INTEREST_PVT.print_debug('description: '||set_value_null(l_rents_tbl_in(i).description));
179 OKL_VARIABLE_INTEREST_PVT.print_debug('first payment date for a payment level: '||set_value_null(l_rents_tbl_in(i).date_start));
180 OKL_VARIABLE_INTEREST_PVT.print_debug('kle_asset_id: '||set_value_null(l_rents_tbl_in(i).kle_asset_id));
181 OKL_VARIABLE_INTEREST_PVT.print_debug('sequence number of the payment level: '||set_value_null(l_rents_tbl_in(i).level_index_number));
182 OKL_VARIABLE_INTEREST_PVT.print_debug('payment type: '||set_value_null(l_rents_tbl_in(i).level_type));
183 OKL_VARIABLE_INTEREST_PVT.print_debug('number of payments for a payment level: '||set_value_null(l_rents_tbl_in(i).number_of_periods));
184 OKL_VARIABLE_INTEREST_PVT.print_debug('amount: '||set_value_null(l_rents_tbl_in(i).amount));
185 OKL_VARIABLE_INTEREST_PVT.print_debug('rate: '||set_value_null(l_rents_tbl_in(i).rate));
186 OKL_VARIABLE_INTEREST_PVT.print_debug('lock_level_step: '||set_value_null(l_rents_tbl_in(i).lock_level_step));
187 OKL_VARIABLE_INTEREST_PVT.print_debug('frequency: '||set_value_null(l_rents_tbl_in(i).period));
188 OKL_VARIABLE_INTEREST_PVT.print_debug('advance_or_arrears: '||set_value_null(l_rents_tbl_in(i).advance_or_arrears));
189 OKL_VARIABLE_INTEREST_PVT.print_debug('income_or_expense: '||set_value_null(l_rents_tbl_in(i).income_or_expense));
190 i := l_rents_tbl_in.next(i);
191 end loop;
192 OKL_VARIABLE_INTEREST_PVT.print_debug('******END CONTENTS OF P_RENTS_TBL_IN******');
193 OKL_VARIABLE_INTEREST_PVT.print_debug('******************************************');
194
195 OKL_VARIABLE_INTEREST_PVT.print_debug('*START CONTENTS OF P_CSM_LINE_DETAILS_TBL*');
196 OKL_VARIABLE_INTEREST_PVT.print_debug('******************************************');
197 i := l_csm_line_details_tbl.first;
198 loop
199 exit when i is null;
200 OKL_VARIABLE_INTEREST_PVT.print_debug('kle_asset_id: '||set_value_null(l_csm_line_details_tbl(i).kle_asset_id));
201 OKL_VARIABLE_INTEREST_PVT.print_debug('----------------------------------------------------');
202 OKL_VARIABLE_INTEREST_PVT.print_debug('asset_cost: '||set_value_null(l_csm_line_details_tbl(i).asset_cost));
203 OKL_VARIABLE_INTEREST_PVT.print_debug('residual_amount: '||set_value_null(l_csm_line_details_tbl(i).residual_amount));
204 OKL_VARIABLE_INTEREST_PVT.print_debug('residual_date: '||set_value_null(l_csm_line_details_tbl(i).residual_date));
205 OKL_VARIABLE_INTEREST_PVT.print_debug('description: '||set_value_null(l_csm_line_details_tbl(i).description));
206 i := l_csm_line_details_tbl.next(i);
207 end loop;
208 OKL_VARIABLE_INTEREST_PVT.print_debug('**END CONTENTS OF P_CSM_LINE_DETAILS_TBL***');
209 OKL_VARIABLE_INTEREST_PVT.print_debug('*******************************************');
210 Exception
211 WHEN OTHERS THEN
212 OKL_VARIABLE_INTEREST_PVT.print_debug('error in procedure print_lease_tables');
213 OKL_VARIABLE_INTEREST_PVT.print_debug('sqlcode : ' || sqlcode || ' $ sqlerrm : ' || sqlerrm);
214 END print_lease_tables;
215
216
217
218 PROCEDURE print_var_int_tables(p_rbk_tbl IN OKL_VARIABLE_INTEREST_PVT.rbk_tbl,
219 p_strm_lalevl_tbl IN OKL_VARIABLE_INTEREST_PVT.strm_lalevl_tbl) IS
220
221 l_api_name CONSTANT VARCHAR2(30) := 'print_var_int_tables';
222 i NUMBER;
223 d NUMBER;
224 l_rbk_tbl OKL_VARIABLE_INTEREST_PVT.rbk_tbl;
225 l_strm_lalevl_tbl OKL_VARIABLE_INTEREST_PVT.strm_lalevl_tbl;
226 BEGIN
227 l_rbk_tbl := p_rbk_tbl;
228 l_strm_lalevl_tbl := p_strm_lalevl_tbl;
229
230 OKL_VARIABLE_INTEREST_PVT.print_debug('*******START CONTENTS OF P_RBK_TBL********');
231 OKL_VARIABLE_INTEREST_PVT.print_debug('******************************************');
232 FOR i IN 1..l_rbk_tbl.COUNT
233 LOOP
234 OKL_VARIABLE_INTEREST_PVT.print_debug('Rec#: '||i);
235 OKL_VARIABLE_INTEREST_PVT.print_debug('KHR ID: '||l_rbk_tbl(i).khr_id);
236 OKL_VARIABLE_INTEREST_PVT.print_debug('KLE ID: '||l_rbk_tbl(i).kle_id);
237 END LOOP;
238 OKL_VARIABLE_INTEREST_PVT.print_debug('*********END CONTENTS OF P_RBK_TBL*********');
239 OKL_VARIABLE_INTEREST_PVT.print_debug('*******************************************');
240
241 OKL_VARIABLE_INTEREST_PVT.print_debug('***START CONTENTS OF P_STRM_LALEVL_TBL****');
242 OKL_VARIABLE_INTEREST_PVT.print_debug('******************************************');
243 for d in 1..l_strm_lalevl_tbl.COUNT
244 loop
245 OKL_VARIABLE_INTEREST_PVT.print_debug('Rec#: '||d);
246 OKL_VARIABLE_INTEREST_PVT.print_debug('-->Chr_Id :'||set_value_null(l_strm_lalevl_tbl(d).chr_id));
247 OKL_VARIABLE_INTEREST_PVT.print_debug('-->Cle_Id :'||set_value_null(l_strm_lalevl_tbl(d).cle_id));
248 OKL_VARIABLE_INTEREST_PVT.print_debug('-->Rule_Information_Category:'||set_value_null(l_strm_lalevl_tbl(d).rule_information_category));
249
250 IF (l_strm_lalevl_tbl(d).rule_information_category = 'LASLH') THEN
251 OKL_VARIABLE_INTEREST_PVT.print_debug('-->Jtot_Object1_Code - stream_type_source :'||set_value_null(l_strm_lalevl_tbl(d).jtot_object1_code));
252 OKL_VARIABLE_INTEREST_PVT.print_debug('-->Jtot_Object2_Code - time_value :'||set_value_null(l_strm_lalevl_tbl(d).jtot_object2_code));
253 OKL_VARIABLE_INTEREST_PVT.print_debug('-->Object1_Id1 - sty_id :'||set_value_null(l_strm_lalevl_tbl(d).object1_id1));
254 OKL_VARIABLE_INTEREST_PVT.print_debug('-->Rule_Information1 - billing_schedule_type :'||set_value_null(l_strm_lalevl_tbl(d).Rule_Information1));
255 OKL_VARIABLE_INTEREST_PVT.print_debug('-->Rule_Information2 - rate_type :'||set_value_null(l_strm_lalevl_tbl(d).rule_information2));
256 ELSE
257 OKL_VARIABLE_INTEREST_PVT.print_debug('-->Jtot_Object1_Code - time_unit_of_measure :'||set_value_null(l_strm_lalevl_tbl(d).jtot_object1_code));
258 OKL_VARIABLE_INTEREST_PVT.print_debug('-->Jtot_Object2_Code - stream_level_header :'||set_value_null(l_strm_lalevl_tbl(d).jtot_object2_code));
259 OKL_VARIABLE_INTEREST_PVT.print_debug('-->Object1_Id1 - Pay_freq :'||set_value_null(l_strm_lalevl_tbl(d).object1_id1));
260 OKL_VARIABLE_INTEREST_PVT.print_debug('-->Rule_Information1 - seq :'||set_value_null(l_strm_lalevl_tbl(d).Rule_Information1));
261 OKL_VARIABLE_INTEREST_PVT.print_debug('-->Rule_Information2 - start_date :'||set_value_null(l_strm_lalevl_tbl(d).rule_information2));
262 END IF;
263
264 OKL_VARIABLE_INTEREST_PVT.print_debug('-->Rule_Information3 - number_periods :'||set_value_null(l_strm_lalevl_tbl(d).rule_information3));
265 OKL_VARIABLE_INTEREST_PVT.print_debug('-->Rule_Information4 - tuoms_per_period:'||set_value_null(l_strm_lalevl_tbl(d).rule_information4));
266 OKL_VARIABLE_INTEREST_PVT.print_debug('-->Rule_Information5 - structure:'||set_value_null(l_strm_lalevl_tbl(d).rule_information5));
267 OKL_VARIABLE_INTEREST_PVT.print_debug('-->Rule_Information6 - amount:'||set_value_null(l_strm_lalevl_tbl(d).rule_information6));
268 OKL_VARIABLE_INTEREST_PVT.print_debug('-->Rule_Information7 - stub_days:'||set_value_null(l_strm_lalevl_tbl(d).rule_information7));
269 OKL_VARIABLE_INTEREST_PVT.print_debug('-->Rule_Information8 - stub_amount:'||set_value_null(l_strm_lalevl_tbl(d).rule_information8));
270 OKL_VARIABLE_INTEREST_PVT.print_debug('-->Rule_Information10 - advance_or_arrears:'||set_value_null(l_strm_lalevl_tbl(d).rule_information10));
271 OKL_VARIABLE_INTEREST_PVT.print_debug('-->Rule_Information13 - rate:'||set_value_null(l_strm_lalevl_tbl(d).rule_information13));
272
273 OKL_VARIABLE_INTEREST_PVT.print_debug('-->Object1_Id2 :'||set_value_null(l_strm_lalevl_tbl(d).object1_id2));
274 OKL_VARIABLE_INTEREST_PVT.print_debug('-->Object2_Id1 :'||set_value_null(l_strm_lalevl_tbl(d).object2_id1));
275 OKL_VARIABLE_INTEREST_PVT.print_debug('-->Object2_Id2 :'||set_value_null(l_strm_lalevl_tbl(d).object2_id2));
276 end loop;
277 OKL_VARIABLE_INTEREST_PVT.print_debug('***END CONTENTS OF P_STRM_LALEVL_TBL****');
278 OKL_VARIABLE_INTEREST_PVT.print_debug('******************************************');
279
280 Exception
281 WHEN OTHERS THEN
282 OKL_VARIABLE_INTEREST_PVT.print_debug('error in procedure print_var_int_tables');
283 OKL_VARIABLE_INTEREST_PVT.print_debug('sqlcode : ' || sqlcode || ' $ sqlerrm : ' || sqlerrm);
284 END print_var_int_tables;
285
286
287
288 --get the start date, stub amount and stub days for a stub payment
289 FUNCTION get_stub_info(p_kle_id IN NUMBER,
290 p_start_date IN DATE,
291 x_stub_start_date OUT NOCOPY DATE,
292 x_stub_days OUT NOCOPY NUMBER,
293 x_stub_amount OUT NOCOPY NUMBER) RETURN VARCHAR2 IS
294 l_stub_start_date DATE;
295 l_stub_days NUMBER;
296 l_stub_amount NUMBER;
297
298 CURSOR l_stub_csr(cp_kle_id IN NUMBER, cp_start_date IN DATE) IS
299 Select fnd_date.canonical_to_date(sll.rule_information2) start_date,
300 sll.rule_information7 stub_days,
301 sll.rule_information8 stub_amount
302 from okc_rules_b sll,
303 okc_rules_b slh,
304 okl_strm_type_v styp,
305 okc_rule_groups_b rgp
306 where to_number(sll.object2_id1) = slh.id
307 and sll.rule_information_category = 'LASLL'
308 and sll.dnz_chr_id = rgp.dnz_chr_id
309 and sll.rgp_id = rgp.id
310 and slh.rule_information_category = 'LASLH'
311 and slh.dnz_chr_id = rgp.dnz_chr_id
312 and slh.rgp_id = rgp.id
313 and slh.object1_id1 = styp.id
314 and rgp.rgd_code = 'LALEVL'
315 and rgp.cle_id = cp_kle_id
316 and sll.rule_information7 IS NOT NULL
317 and sll.rule_information8 IS NOT NULL
318 --and fnd_date.canonical_to_date(sll.rule_information2) <= cp_start_date
319 order by abs(fnd_date.canonical_to_date(sll.rule_information2) - cp_start_date) asc;
320 BEGIN
321 OPEN l_stub_csr(p_kle_id, p_start_date);
322 FETCH l_stub_csr INTO l_stub_start_date, l_stub_days, l_stub_amount;
323 CLOSE l_stub_csr;
324
325 x_stub_start_date := l_stub_start_date;
326 x_stub_days := l_stub_days;
327 x_stub_amount := l_stub_amount;
328
329 return '';
330 END;
331
332
333
334 ------------------------------------------------------------------------------
335 -- Start of Comments
336 -- Created By: Sanjeev Ahuja
337 -- Function Name contract_future rent
338 -- Description: returns the sum of rent
339 -- Dependencies:
340 -- Parameters: contract id, date.
341 -- Version: 1.0
342 -- End of Comments
343 ------------------------------------------------------------------------------
344 FUNCTION contract_future_rents(
345 p_chr_id IN NUMBER,
346 p_kle_id IN NUMBER,
347 p_date IN DATE,
348 p_advance_or_arrears IN VARCHAR2) RETURN NUMBER IS
349
350 l_rents NUMBER := 0;
351
352 Cursor ln_future_rents_csr (p_chr_id NUMBER, p_kle_id NUMBER, p_date DATE, p_advance_or_arrears VARCHAR2 ) IS
353 select NVL(sum(sel.amount), 0) amount
354 from
355 okl_K_lines_full_v kle,
356 okc_statuses_b sts,
357 okl_strm_elements sel,
358 okl_streams stm,
359 okl_strm_type_b sty
360 WHERE kle.dnz_chr_id = p_chr_id
361 AND kle.id = p_kle_id
362 AND kle.sts_code = sts.code
363 AND sts.ste_code NOT IN ('TERMINATED', 'EXPIRED', 'CANCELLED', 'HOLD')
364 AND kle.dnz_chr_id = stm.khr_id
365 AND kle.id = stm.kle_id
366 AND stm.say_code = 'CURR'
367 AND stm.active_yn = 'Y'
368 AND stm.sty_id = sty.id
369 AND sty.stream_type_purpose = 'RENT'
370 AND stm.id = sel.stm_id
371 AND ((p_advance_or_arrears = 'ARREARS' and sel.stream_element_date > p_date)
372 OR (p_advance_or_arrears <> 'ARREARS' and sel.stream_element_date >= p_date));
373
374 ln_future_rents_rec ln_future_rents_csr%ROWTYPE;
375
376 Cursor l_chr_rents_csr (p_chr_id NUMBER, p_kle_id NUMBER, p_date DATE, p_advance_or_arrears VARCHAR2) IS
377 SELECT NVL(SUM(sele.amount),0) amount
378 FROM okl_strm_elements sele,
379 okl_streams str,
380 okl_strm_type_b sty
381 WHERE sele.stm_id = str.id
382 AND str.sty_id = sty.id
383 AND sty.stream_type_purpose = 'RENT'
384 AND str.say_code = 'CURR'
385 AND str.active_yn = 'Y'
386 AND nvl( str.purpose_code, 'XXXX' ) = 'XXXX'
387 AND str.khr_id = p_chr_id
388 AND str.kle_id = p_kle_id
389 AND nvl(str.kle_id, -1) = -1
390 AND ((p_advance_or_arrears = 'ARREARS' and sele.stream_element_date > p_date)
391 OR (p_advance_or_arrears <> 'ARREARS' and sele.stream_element_date >= p_date));
392
393 l_chr_rents_rec l_chr_rents_csr%ROWTYPE;
394
395 BEGIN
396
397 IF ( p_chr_id IS NULL OR p_kle_id IS NULL) THEN
398 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
399 END IF;
400
401 OPEN ln_future_rents_csr(p_chr_id, p_kle_id, p_date, p_advance_or_arrears);
402 FETCH ln_future_rents_csr INTO ln_future_rents_rec;
403 CLOSE ln_future_rents_csr;
404
405 OPEN l_chr_rents_csr(p_chr_id, p_kle_id, p_date, p_advance_or_arrears);
406 FETCH l_chr_rents_csr INTO l_chr_rents_rec;
407 CLOSE l_chr_rents_csr;
408
409 l_rents := ln_future_rents_rec.amount + l_chr_rents_rec.amount;
410
411 OKL_VARIABLE_INTEREST_PVT.print_debug('Contract future rent :' || l_rents);
412 RETURN l_rents;
413
414 EXCEPTION
415 WHEN OTHERS THEN
416 Okl_Api.SET_MESSAGE(
417 p_app_name => G_APP_NAME,
418 p_msg_name => G_UNEXPECTED_ERROR,
419 p_token1 => G_SQLCODE_TOKEN,
420 p_token1_value => SQLCODE,
421 p_token2 => G_SQLERRM_TOKEN,
422 p_token2_value => SQLERRM);
423 RETURN NULL;
424
425 END contract_future_rents;
426
427
428 ------------------------------------------------------------------------------
429 -- Start of Comments
430 -- Created By: Sanjeev Ahuja
431 -- Function Name contract_future_income
432 -- Description: returns sum of all future incomes of financial asset lines of a contract
433 -- Dependencies:
434 -- Parameters: contract id, date.
435 -- Version: 1.0
436 -- End of Comments
437 ------------------------------------------------------------------------------
438 FUNCTION contract_future_income(
439 p_chr_id IN NUMBER,
440 p_kle_id IN NUMBER,
441 p_date IN DATE) RETURN NUMBER IS
442
443 l_income NUMBER := 0;
444
445 Cursor l_chr_income_csr (p_chr_id NUMBER, p_kle_id NUMBER, p_date DATE) IS
446 select NVL(sum(sel.amount), 0) amount
447 from
448 okl_K_lines_full_v kle,
449 okc_statuses_b sts,
450 okl_strm_elements sel,
451 okl_streams stm,
452 okl_strm_type_b sty
453 WHERE kle.dnz_chr_id = p_chr_id
454 AND kle.id = p_kle_id
455 AND kle.sts_code = sts.code
456 AND sts.ste_code NOT IN ('TERMINATED', 'EXPIRED', 'CANCELLED', 'HOLD')
457 AND kle.dnz_chr_id = stm.khr_id
458 AND kle.id = stm.kle_id
459 AND stm.say_code = 'CURR'
460 AND stm.active_yn = 'Y'
461 AND stm.sty_id = sty.id
462 AND sty.stream_type_purpose = 'LEASE_INCOME'
463 AND stm.id = sel.stm_id
464 AND sel.stream_element_date >= TRUNC(p_date, 'MONTH');
465
466 l_chr_income_rec l_chr_income_csr%ROWTYPE;
467 BEGIN
468
469 IF ( p_chr_id IS NULL OR p_kle_id IS NULL) THEN
470 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
471 END IF;
472
473 OPEN l_chr_income_csr(p_chr_id, p_kle_id, p_date);
474 FETCH l_chr_income_csr INTO l_chr_income_rec;
475 CLOSE l_chr_income_csr;
476
477 l_income := l_chr_income_rec.amount;
478
479 OKL_VARIABLE_INTEREST_PVT.print_debug('Contract future income :' || l_income);
480 RETURN l_income;
481
482 EXCEPTION
483
484 WHEN OTHERS THEN
485 Okl_Api.SET_MESSAGE(
486 p_app_name => G_APP_NAME,
487 p_msg_name => G_UNEXPECTED_ERROR,
488 p_token1 => G_SQLCODE_TOKEN,
489 p_token1_value => SQLCODE,
490 p_token2 => G_SQLERRM_TOKEN,
491 p_token2_value => SQLERRM);
492 RETURN NULL;
493 END contract_future_income;
494
495
496 ------------------------------------------------------------------------------
497 -- Start of Comments
498 -- Created By: Sanjeev Ahuja
499 -- Function Name contract_residual_value
500 -- Description: returns the residual value for a contract line
501 -- Dependencies:
502 -- Parameters: contract id, date.
503 -- Version: 1.0
504 -- End of Comments
505 ------------------------------------------------------------------------------
506 FUNCTION contract_residual_value(
507 p_chr_id IN NUMBER,
508 p_kle_id IN NUMBER) RETURN NUMBER IS
509
510 l_residual_value NUMBER := 0;
511
512 Cursor residual_value_csr (p_chr_id IN NUMBER, p_kle_id IN NUMBER) IS
513 SELECT nvl(kle.residual_value,0) Value
514 FROM OKC_LINE_STYLES_B LS,
515 okl_K_lines_full_v kle,
516 okc_statuses_b sts
517 WHERE LS.ID = KLE.LSE_ID
518 AND LS.LTY_CODE ='FREE_FORM1'
519 AND KLE.DNZ_CHR_ID = p_chr_id --289326506849179644190030423574805590144
520 AND KLE.id = p_kle_id
521 AND kle.sts_code = sts.code
522 AND sts.ste_code NOT IN ('TERMINATED', 'EXPIRED', 'CANCELLED', 'HOLD');
523
524 residual_value_rec residual_value_csr%ROWTYPE;
525 BEGIN
526
527 IF ( p_chr_id IS NULL OR p_kle_id IS NULL ) THEN
528 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
529 END IF;
530
531 OPEN residual_value_csr(p_chr_id, p_kle_id);
532 FETCH residual_value_csr INTO residual_value_rec;
533 CLOSE residual_value_csr;
534
535 l_residual_value := residual_value_rec.Value;
536
537 OKL_VARIABLE_INTEREST_PVT.print_debug('Contract residual value :' || l_residual_value);
538 RETURN l_residual_value;
539
540 EXCEPTION
541 WHEN OTHERS THEN
542 Okl_Api.SET_MESSAGE(
543 p_app_name => G_APP_NAME,
544 p_msg_name => G_UNEXPECTED_ERROR,
545 p_token1 => G_SQLCODE_TOKEN,
546 p_token1_value => SQLCODE,
547 p_token2 => G_SQLERRM_TOKEN,
548 p_token2_value => SQLERRM);
549 RETURN NULL;
550 END contract_residual_value;
551
552
553 ----------------------------------------------------------------------------------------------------
554 -- Start of Comments
555 -- Created By: Sanjeev Ahuja
556 -- Function Name prin_bal_OP_lease
557 -- Description: returns Principal Balance for a Operating Lease contract
558 -- Dependencies:
559 -- Parameters: contract id.
560 -- Version: 1.0
561 -- End of Comments
562 ----------------------------------------------------------------------------------------------------
563 FUNCTION prin_bal_OP_lease(
564 p_chr_id IN NUMBER,
565 p_kle_id IN NUMBER) RETURN NUMBER IS
566
567 l_net_book_value NUMBER := 0;
568
569 Cursor lease_asset_cost_csr (p_chr_id IN NUMBER, p_kle_id IN NUMBER) IS
570 Select FA_BOOKS.ASSET_ID ASSET_ID,
571 FA_BOOKS.cost ASSET_COST,
572 FA_BOOKS.book_type_code BOOK_TYPE_CODE
573 from FA_BOOKS ,
574 FA_BOOK_CONTROLS,
575 OKC_K_LINES_B LINES,
576 OKC_LINE_STYLES_B STYLE,
577 OKC_K_ITEMS KITEM
578 where FA_BOOKS.asset_id = KITEM.OBJECT1_ID1
579 and LINES.DNZ_CHR_ID = p_chr_id --291511068054787299132375269533568315520
580 and LINES.cle_id = p_kle_id
581 and LINES.ID = KITEM.CLE_ID
582 and LINES.LSE_ID = STYLE.ID
583 and STYLE.LTY_CODE = 'FIXED_ASSET'
584 and FA_BOOKS.book_type_code = FA_BOOK_CONTROLS.book_type_code
585 and FA_BOOK_CONTROLS.book_class = 'CORPORATE'
586 and FA_BOOKS.transaction_header_id_out is null;
587
588 Cursor Deprn_csr (p_asset_id IN NUMBER,
589 p_book_type_code IN VARCHAR2) IS
590 select sum(deprn_amount) ACCUMULATED_DEPRECIATION
591 from fa_deprn_summary
592 where Asset_id = p_asset_id
593 and book_type_code = p_book_type_code;
594
595 Deprn_rec Deprn_csr%ROWTYPE;
596
597 BEGIN
598
599 IF ( p_chr_id = NULL ) THEN
600 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
601 END IF;
602
603 OKL_VARIABLE_INTEREST_PVT.print_debug('Asset id :' || p_kle_id);
604 FOR l_lease_asset_cost_csr IN lease_asset_cost_csr(p_chr_id, p_kle_id)
605 LOOP
606
607 OPEN Deprn_csr(l_lease_asset_cost_csr.ASSET_ID,l_lease_asset_cost_csr.BOOK_TYPE_CODE);
608 FETCH Deprn_csr INTO Deprn_rec.ACCUMULATED_DEPRECIATION;
609 CLOSE Deprn_csr;
610
611 OKL_VARIABLE_INTEREST_PVT.print_debug('Asset cost :' || l_lease_asset_cost_csr.ASSET_COST);
612 OKL_VARIABLE_INTEREST_PVT.print_debug('Accumulated depreciation :' || Deprn_rec.ACCUMULATED_DEPRECIATION);
613
614 l_net_book_value := l_net_book_value + (l_lease_asset_cost_csr.ASSET_COST -
615 Deprn_rec.ACCUMULATED_DEPRECIATION);
616
617 OKL_VARIABLE_INTEREST_PVT.print_debug('Net book value :' || l_net_book_value);
618 END LOOP;
619
620 RETURN l_net_book_value;
621
622 EXCEPTION
623 WHEN OTHERS THEN
624 Okl_Api.SET_MESSAGE(
625 p_app_name => G_APP_NAME,
626 p_msg_name => G_UNEXPECTED_ERROR,
627 p_token1 => G_SQLCODE_TOKEN,
628 p_token1_value => SQLCODE,
629 p_token2 => G_SQLERRM_TOKEN,
630 p_token2_value => SQLERRM);
631 RETURN NULL;
632 END prin_bal_OP_lease;
633
634
635
636 ----------------------------------------------------------------------------------
637 -- Start of comments
638 --
639 -- Procedure Name : get_tot_principal_amt
640 -- Description : get principal balance amount
641 -- Business Rules :
642 -- Parameters :
643 -- Version : 1.0
644 -- End of comments
645 ----------------------------------------------------------------------------------
646 FUNCTION get_tot_principal_amt(
647 p_khr_id IN NUMBER,
648 p_kle_id IN NUMBER,
649 p_date IN DATE,
650 p_advance_or_arrears IN VARCHAR2) RETURN NUMBER IS
651
652 l_principal_balance NUMBER := 0;
653
654 l_api_version NUMBER := 1;
655 l_init_msg_list VARCHAR2(100) := OKC_API.G_FALSE;
656 x_return_status VARCHAR2(100);
657 x_msg_count NUMBER;
658 x_msg_data VARCHAR2(1999);
659 x_value NUMBER := 0;
660 l_formula_name VARCHAR2(100);
661 lx_rulv_rec Okl_Rule_Apis_Pvt.rulv_rec_type;
662 p_bal_date DATE;
663
664 CURSOR c_khr_type (p_khr_id NUMBER) IS
665 SELECT khr.deal_type deal_type
666 FROM okc_k_headers_b CHR,
667 okl_k_headers khr
668 WHERE khr.id = CHR.id
669 AND khr.id = p_khr_id;
670
671 c_khr_type_rec c_khr_type%ROWTYPE;
672
673 BEGIN
674 IF ( p_khr_id IS NULL OR p_kle_id IS NULL ) THEN
675 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
676 END IF;
677
678 OPEN c_khr_type(p_khr_id);
679 FETCH c_khr_type INTO c_khr_type_rec.deal_type;
680 CLOSE c_khr_type;
681
682 OKL_VARIABLE_INTEREST_PVT.print_debug('get_tot_principal_amt : p_advance_or_arrears => ' || p_advance_or_arrears);
683 if(c_khr_type_rec.deal_type = 'LEASEDF' or c_khr_type_rec.deal_type = 'LEASEST') THEN
684 l_principal_balance := contract_future_rents(p_khr_id, p_kle_id, p_date, p_advance_or_arrears) +
685 contract_residual_value(p_khr_id, p_kle_id) -
686 contract_future_income(p_khr_id, p_kle_id, p_date);
687 elsif(c_khr_type_rec.deal_type = 'LEASEOP') THEN
688 l_principal_balance := prin_bal_OP_lease(p_khr_id, p_kle_id);
689 end if;
690
691 RETURN l_principal_balance;
692
693 EXCEPTION
694 WHEN OTHERS THEN
695 OKL_API.Set_Message(p_app_name => OKL_API.G_APP_NAME,
696 p_msg_name => 'OKL_UNEXPECTED_ERROR',
697 p_token1 => 'OKL_SQLCODE',
698 p_token1_value => SQLCODE,
699 p_token2 => 'OKL_SQLERRM',
700 p_token2_value => SQLERRM);
701 RETURN NULL;
702 END get_tot_principal_amt;
703
704
705
706
707 PROCEDURE var_int_rent_level(
708 p_api_version IN NUMBER,
709 p_init_msg_list IN VARCHAR2,
710 x_return_status OUT NOCOPY VARCHAR2,
711 x_msg_count OUT NOCOPY NUMBER,
712 x_msg_data OUT NOCOPY VARCHAR2,
713 p_chr_id IN NUMBER,
714 p_trx_id IN NUMBER,
715 p_trx_status IN VARCHAR2,
716 p_rent_tbl IN OKL_VARIABLE_INTEREST_PVT.csm_periodic_expenses_tbl_type,
717 p_csm_loan_level_tbl IN OKL_VARIABLE_INTEREST_PVT.csm_loan_level_tbl_type,
718 x_child_trx_id OUT NOCOPY NUMBER) IS
719
720 ------------------------------------------------------------
721 -- Declare Process variables
722 ------------------------------------------------------------
723
724 l_api_name VARCHAR2(35) := 'var_int_rent_level';
725 l_proc_name VARCHAR2(35) := 'var_int_rent_level';
726 l_api_version CONSTANT NUMBER := 1;
727 l_msg_index_out NUMBER;
728
729 l_payment_type VARCHAR2(100) := 'RENT';
730
731 i NUMBER := 0;
732 l_rent_count NUMBER := 0;
733 l_split_count NUMBER := 0;
734 --l_sequence NUMBER := 0;
735
736 l_strm_lalevl_tbl OKL_VARIABLE_INTEREST_PVT.strm_lalevl_tbl;
737 l_strm_lalevl_tbl_cntr NUMBER := 0;
738 l_rbk_tbl OKL_VARIABLE_INTEREST_PVT.rbk_tbl;
739 l_rbk_tbl_cntr NUMBER := 0;
740 l_rent_tbl OKL_VARIABLE_INTEREST_PVT.csm_periodic_expenses_tbl_type;
741 l_csm_loan_level_tbl OKL_VARIABLE_INTEREST_PVT.csm_loan_level_tbl_type;
742 --l_split_asset_tbl strm_lalevl_tbl;
743 l_frequency okl_time_units_v.name%type;
744 l_adder_months NUMBER;
745 l_payment_level_start_date DATE;
746 l_reamort_date DATE;
747 l_date_last_int_cal DATE;
748 l_vipv_rec OKL_VARIABLE_INTEREST_PVT.vipv_rec;
749 x_vipv_rec OKL_VARIABLE_INTEREST_PVT.vipv_rec;
750 x_strm_trx_tbl OKL_VARIABLE_INTEREST_PVT.strm_trx_tbl;
751 l_child_trx_id NUMBER := NULL;
752 l_strm_lalevl_tbl_out OKL_VARIABLE_INTEREST_PVT.strm_lalevl_tbl;
753 l_next_reamort_date DATE; --Added by bkatraga for bug 13693187
754
755 CURSOR l_vip_csr (p_trx_id in NUMBER) IS
756 SELECT id
757 FROM okl_var_int_process_b
758 WHERE PARENT_TRX_ID = p_trx_id;
759
760 --cursor to get existing payments
761 Cursor l_pmt_csr (p_khr_id in number, p_cle_id in number) is
762 select sll_rulb.dnz_chr_id khr_id,
763 rgpb.cle_id cle_id,
764 --LASLL values
765 sll_rulb.rule_information_category sll_rule_information_category,
766 to_number(sll_rulb.rule_information1) seq, -- 4899594
767 sll_rulb.rule_information2 start_date,
768 sll_rulb.rule_information3 number_periods,
769 sll_rulb.rule_information4 tuoms_per_period,
770 sll_rulb.object1_id1 Pay_freq,
771 sll_rulb.rule_information5 structure,
772 nvl( sll_rulb.rule_information10,'N') advance_or_arrears,
773 sll_rulb.rule_information6 amount,
774 sll_rulb.rule_information7 stub_days,
775 sll_rulb.rule_information8 stub_amount,
776 sll_rulb.rule_information13 rate,
777 sll_rulb.jtot_object1_code time_unit_of_measure,
778 sll_rulb.jtot_object2_code stream_level_header,
779 --LASLH values
780 slh_rulb.rule_information_category slh_rule_information_category,
781 slh_rulb.jtot_object1_code stream_type_source,
782 slh_rulb.jtot_object2_code time_value,
783 slh_rulb.object1_id1 sty_id,
784 slh_rulb.rule_information1 billing_schedule_type,
785 slh_rulb.rule_information2 rate_type
786 from okc_rules_b sll_rulb,
787 okc_rules_b slh_rulb,
788 okl_strm_type_b styb,
789 okc_rule_groups_b rgpb
790 where sll_rulb.rgp_id = rgpb.id
791 and sll_rulb.rule_information_category = 'LASLL'
792 and sll_rulb.dnz_chr_id = rgpb.dnz_chr_id
793 and sll_rulb.object2_id1 = to_char(slh_rulb.id)
794 and slh_rulb.rgp_id = rgpb.id
795 and slh_rulb.rule_information_category = 'LASLH'
796 and slh_rulb.dnz_chr_id = rgpb.dnz_chr_id
797 and styb.id = slh_rulb.object1_id1
798 and styb.stream_type_purpose IN ('RENT', 'PRINCIPAL_PAYMENT')
799 and rgpb.dnz_chr_id = p_khr_id
800 and rgpb.cle_id = p_cle_id
801 and rgpb.rgd_code = 'LALEVL'
802 order by to_number(sll_rulb.rule_information1); -- 4899594
803
804 Cursor get_pmt_freq (l_freq in okl_time_units_v.name%type) is
805 select id1 from okl_time_units_v
806 where name = l_freq;
807
808 CURSOR c_last_int_cur (p_contract_id NUMBER) IS
809 SELECT NVL(date_last_interim_interest_cal, start_date) reamort_date
810 ,currency_code
811 FROM okl_k_headers_full_v
812 WHERE id = p_chr_id;
813
814 -- 10367844: Modified Cursor to fetch Frequency of
815 -- Payment Levels of Rent/Principal Payment/Loan Payment of Assets only
816 CURSOR c_freq_cur(p_contract_id NUMBER, p_cle_id IN NUMBER)
817 IS
818 SELECT rul2.object1_id1
819 FROM okc_rule_groups_b rgp,
820 okc_rules_b rul1,
821 okc_rules_b rul2,
822 okl_strm_type_b sty
823 WHERE rul2.dnz_chr_id = p_contract_id
824 AND rul2.rule_information_category = 'LASLL'
825 AND rul2.rgp_id = rgp.id
826 AND TO_NUMBER(rul2.object2_id1) = rul1.id
827 AND rgp.cle_id = p_cle_id
828 AND sty.id = rul1.object1_id1
829 AND sty.stream_type_purpose IN ( 'RENT', 'LOAN_PAYMENT', 'PRINCIPAL_PAYMENT' );
830
831 l_pymt_rec l_pmt_csr%RowType;
832 l_diff_in_periods NUMBER;
833
834 line_index Number := 0;
835 l_source Number;
836 l_kle_id NUMBER := NULL;
837 l_prev_kle_id NUMBER := NULL;
838 l_rent_tbl_cntr NUMBER := 0;
839 l_csm_loan_level_tbl_cntr NUMBER := 0;
840 l_index_number NUMBER;
841 l_prev_index_number NUMBER;
842 l_fetch_prior_periods BOOLEAN := TRUE;
843 l_prior_periods NUMBER := 0;
844 l_period_cntr NUMBER := 0;
845 l_prior_level_date_start DATE;
846 l_level_date_start DATE;
847 l_sequence NUMBER := 0;
848 l_time_unit_of_measure okc_rules_b.jtot_object1_code%type;
849 l_stream_level_header okc_rules_b.jtot_object2_code%type;
850 l_tuoms_per_period okc_rules_b.rule_information4%type;
851 l_sll_rule_information_cat okc_rules_b.rule_information_category%type;
852 l_structure okc_rules_b.rule_information5%type;
853
854 l_stub_start_date DATE;
855 l_stub_days NUMBER;
856 l_stub_amount NUMBER;
857 l_ret_val VARCHAR2(1);
858 l_currency_code okc_k_headers_b.currency_code%type;
859
860 --get the line id for the repriced rent from supertrump
861 FUNCTION get_kle_id(p_trx_number IN NUMBER,
862 p_index_number IN NUMBER,
863 x_return_status OUT NOCOPY VARCHAR2) RETURN NUMBER IS
864 l_kle_id NUMBER := -9999;
865 BEGIN
866 OKL_STREAMS_UTIL.get_line_id(p_trx_number => p_trx_number
867 ,p_index_number => p_index_number
868 ,x_kle_id => l_kle_id
869 ,x_return_status => x_return_status);
870 RETURN l_kle_id;
871 EXCEPTION
872 WHEN OTHERS THEN
873 OKL_VARIABLE_INTEREST_PVT.print_debug('Error deriving the asset line id for Trx Number : ' || p_trx_number || ' and Index Number : ' || p_index_number);
874 RETURN l_kle_id;
875 END get_kle_id;
876
877 PROCEDURE print_pmt_csr(p_pmt_rec IN l_pmt_csr%RowType) IS
878
879 l_api_name CONSTANT VARCHAR2(30) := 'print_pmt_csr';
880 l_pymt_rec l_pmt_csr%RowType;
881 BEGIN
882 l_pymt_rec := p_pmt_rec;
883 OKL_VARIABLE_INTEREST_PVT.print_debug('*****************************************');
884 OKL_VARIABLE_INTEREST_PVT.print_debug('*******START CONTENTS OF P_PMT_REC******');
885 OKL_VARIABLE_INTEREST_PVT.print_debug('khr_id : ' || set_value_null(l_pymt_rec.khr_id));
886 OKL_VARIABLE_INTEREST_PVT.print_debug('cle_id : ' || set_value_null(l_pymt_rec.cle_id));
887 OKL_VARIABLE_INTEREST_PVT.print_debug('LASLL VALUES');
888 OKL_VARIABLE_INTEREST_PVT.print_debug('============');
889 OKL_VARIABLE_INTEREST_PVT.print_debug('cle_id : ' || set_value_null(l_pymt_rec.cle_id));
890 OKL_VARIABLE_INTEREST_PVT.print_debug('seq : ' || set_value_null(l_pymt_rec.seq));
891 OKL_VARIABLE_INTEREST_PVT.print_debug('start_date : ' || set_value_null(l_pymt_rec.start_date));
892 OKL_VARIABLE_INTEREST_PVT.print_debug('number_periods : ' || set_value_null(l_pymt_rec.number_periods));
893 OKL_VARIABLE_INTEREST_PVT.print_debug('tuoms_per_period : ' || set_value_null(l_pymt_rec.tuoms_per_period));
894 OKL_VARIABLE_INTEREST_PVT.print_debug('Pay_freq : ' || set_value_null(l_pymt_rec.Pay_freq));
895 OKL_VARIABLE_INTEREST_PVT.print_debug('structure : ' || set_value_null(l_pymt_rec.structure));
896 OKL_VARIABLE_INTEREST_PVT.print_debug('advance_or_arrears : ' || set_value_null(l_pymt_rec.advance_or_arrears));
897 OKL_VARIABLE_INTEREST_PVT.print_debug('amount : ' || set_value_null(l_pymt_rec.amount));
898 OKL_VARIABLE_INTEREST_PVT.print_debug('stub_days : ' || set_value_null(l_pymt_rec.stub_days));
899 OKL_VARIABLE_INTEREST_PVT.print_debug('stub_amount : ' || set_value_null(l_pymt_rec.stub_amount));
900 OKL_VARIABLE_INTEREST_PVT.print_debug('rate : ' || set_value_null(l_pymt_rec.rate));
901 OKL_VARIABLE_INTEREST_PVT.print_debug('time_unit_of_measure : ' || set_value_null(l_pymt_rec.time_unit_of_measure));
902 OKL_VARIABLE_INTEREST_PVT.print_debug('stream_level_header : ' || set_value_null(l_pymt_rec.stream_level_header));
903 OKL_VARIABLE_INTEREST_PVT.print_debug('LASLH VALUES');
904 OKL_VARIABLE_INTEREST_PVT.print_debug('============');
905 OKL_VARIABLE_INTEREST_PVT.print_debug('slh_rule_information_category : ' || set_value_null(l_pymt_rec.slh_rule_information_category));
906 OKL_VARIABLE_INTEREST_PVT.print_debug('stream_type_source : ' || set_value_null(l_pymt_rec.stream_type_source));
907 OKL_VARIABLE_INTEREST_PVT.print_debug('time_value : ' || set_value_null(l_pymt_rec.time_value));
908 OKL_VARIABLE_INTEREST_PVT.print_debug('sty_id : ' || set_value_null(l_pymt_rec.sty_id));
909 OKL_VARIABLE_INTEREST_PVT.print_debug('billing_schedule_type : ' || set_value_null(l_pymt_rec.billing_schedule_type));
910 OKL_VARIABLE_INTEREST_PVT.print_debug('rate_type : ' || set_value_null(l_pymt_rec.rate_type));
911 OKL_VARIABLE_INTEREST_PVT.print_debug('*******END CONTENTS OF P_PMT_REC********');
912 OKL_VARIABLE_INTEREST_PVT.print_debug('*****************************************');
913
914 Exception
915 WHEN OTHERS THEN
916 OKL_VARIABLE_INTEREST_PVT.print_debug('error in procedure print_pmt_csr');
917 OKL_VARIABLE_INTEREST_PVT.print_debug('sqlcode : ' || sqlcode || ' $ sqlerrm : ' || sqlerrm);
918 END print_pmt_csr;
919
920 --procedure to consolidate consecutive sll records if the the amounts are same incase of RENT
921 --or if amounts and rates are same incase of PRINCIPAL PAYMENT
922 PROCEDURE consolidate_sll(p_strm_lalevl_tbl IN OKL_VARIABLE_INTEREST_PVT.strm_lalevl_tbl,
923 x_strm_lalevl_tbl OUT NOCOPY OKL_VARIABLE_INTEREST_PVT.strm_lalevl_tbl) IS
924
925 l_strm_lalevl_tbl OKL_VARIABLE_INTEREST_PVT.strm_lalevl_tbl;
926 l_strm_lalevl_tbl_cntr NUMBER := 0;
927 l_prev_strm_lalevl_tbl_cntr NUMBER := 0;
928 l_strm_lalevl_tbl_out_cntr NUMBER := 0;
929 BEGIN
930 OKL_VARIABLE_INTEREST_PVT.print_debug('*******START CONSOLIDATE_SLL********');
931
932 l_strm_lalevl_tbl := p_strm_lalevl_tbl;
933 l_strm_lalevl_tbl_cntr := l_strm_lalevl_tbl.first;
934 OKL_VARIABLE_INTEREST_PVT.print_debug('table count before consolidation :' || l_strm_lalevl_tbl.count);
935
936 loop
937 exit when l_strm_lalevl_tbl_cntr IS NULL;
938 IF (l_strm_lalevl_tbl(l_strm_lalevl_tbl_cntr).rule_information_category = 'LASLH') THEN
939 l_strm_lalevl_tbl_out_cntr := l_strm_lalevl_tbl_out_cntr + 1;
940 x_strm_lalevl_tbl(l_strm_lalevl_tbl_out_cntr) := l_strm_lalevl_tbl(l_strm_lalevl_tbl_cntr);
941 l_prev_strm_lalevl_tbl_cntr := NULL;
942 ELSIF (l_strm_lalevl_tbl(l_strm_lalevl_tbl_cntr).rule_information_category = 'LASLL') THEN
943 IF (l_prev_strm_lalevl_tbl_cntr IS NULL) THEN
944 l_strm_lalevl_tbl_out_cntr := l_strm_lalevl_tbl_out_cntr + 1;
945 x_strm_lalevl_tbl(l_strm_lalevl_tbl_out_cntr) := l_strm_lalevl_tbl(l_strm_lalevl_tbl_cntr);
946 l_prev_strm_lalevl_tbl_cntr := l_strm_lalevl_tbl_cntr;
947 ELSE
948
949 IF (l_strm_lalevl_tbl(l_strm_lalevl_tbl_cntr).rule_information6 IS NOT NULL AND l_strm_lalevl_tbl(l_strm_lalevl_tbl_cntr).rule_information13 IS NULL) THEN
950 --for RENT
951 --for payment line (not a STUB)
952 IF (l_strm_lalevl_tbl(l_prev_strm_lalevl_tbl_cntr).rule_information6 = l_strm_lalevl_tbl(l_strm_lalevl_tbl_cntr).rule_information6) THEN
953 --amounts are the same
954 x_strm_lalevl_tbl(l_strm_lalevl_tbl_out_cntr).rule_information3 := x_strm_lalevl_tbl(l_strm_lalevl_tbl_out_cntr).rule_information3 + l_strm_lalevl_tbl(l_strm_lalevl_tbl_cntr).rule_information3;
955 ELSE
956 --amounts are not the same
957 l_strm_lalevl_tbl_out_cntr := l_strm_lalevl_tbl_out_cntr + 1;
958 x_strm_lalevl_tbl(l_strm_lalevl_tbl_out_cntr) := l_strm_lalevl_tbl(l_strm_lalevl_tbl_cntr);
959 l_prev_strm_lalevl_tbl_cntr := l_strm_lalevl_tbl_cntr;
960 END IF;
961 ELSIF (l_strm_lalevl_tbl(l_strm_lalevl_tbl_cntr).rule_information6 IS NOT NULL AND l_strm_lalevl_tbl(l_strm_lalevl_tbl_cntr).rule_information13 IS NOT NULL) THEN
962 --for PRINCIPAL PAYMENT
963 --for payment line (not a STUB)
964
965 --compare amount and rate
966 IF (l_strm_lalevl_tbl(l_prev_strm_lalevl_tbl_cntr).rule_information6 = l_strm_lalevl_tbl(l_strm_lalevl_tbl_cntr).rule_information6 AND
967 l_strm_lalevl_tbl(l_prev_strm_lalevl_tbl_cntr).rule_information13 = l_strm_lalevl_tbl(l_strm_lalevl_tbl_cntr).rule_information13) THEN
968 --amounts are the same
969 x_strm_lalevl_tbl(l_strm_lalevl_tbl_out_cntr).rule_information3 := x_strm_lalevl_tbl(l_strm_lalevl_tbl_out_cntr).rule_information3 + l_strm_lalevl_tbl(l_strm_lalevl_tbl_cntr).rule_information3;
970 ELSE
971 --amounts are not the same
972 l_strm_lalevl_tbl_out_cntr := l_strm_lalevl_tbl_out_cntr + 1;
973 x_strm_lalevl_tbl(l_strm_lalevl_tbl_out_cntr) := l_strm_lalevl_tbl(l_strm_lalevl_tbl_cntr);
974 l_prev_strm_lalevl_tbl_cntr := l_strm_lalevl_tbl_cntr;
975 END IF;
976 ELSIF (l_strm_lalevl_tbl(l_strm_lalevl_tbl_cntr).rule_information6 IS NULL) THEN
977 --line is a stub
978 l_strm_lalevl_tbl_out_cntr := l_strm_lalevl_tbl_out_cntr + 1;
979 x_strm_lalevl_tbl(l_strm_lalevl_tbl_out_cntr) := l_strm_lalevl_tbl(l_strm_lalevl_tbl_cntr);
980 l_prev_strm_lalevl_tbl_cntr := l_strm_lalevl_tbl_cntr;
981 ELSE
982 OKL_VARIABLE_INTEREST_PVT.print_debug('neither Rent nor Principal Payment');
983 END IF;
984
985 END IF;
986
987 END IF;
988
989 l_strm_lalevl_tbl_cntr := l_strm_lalevl_tbl.next(l_strm_lalevl_tbl_cntr);
990 end loop;
991
992 OKL_VARIABLE_INTEREST_PVT.print_debug('table count after consolidation :' || x_strm_lalevl_tbl.count);
993 OKL_VARIABLE_INTEREST_PVT.print_debug('*******END CONSOLIDATE_SLL********');
994 Exception
995 WHEN OTHERS THEN
996 OKL_VARIABLE_INTEREST_PVT.print_debug('error in procedure consolidate_sll');
997 OKL_VARIABLE_INTEREST_PVT.print_debug('sqlcode : ' || sqlcode || ' $ sqlerrm : ' || sqlerrm);
998 END consolidate_sll;
999
1000 BEGIN -- main process begins here
1001
1002 OKL_VARIABLE_INTEREST_PVT.print_debug(p_trx_id || '****Entering procedure VAR_INT_RENT_LEVEL****');
1003 OKL_VARIABLE_INTEREST_PVT.print_debug(p_trx_id || '****Start-Creating Rent Levels and ReBooking.');
1004
1005 x_return_status := OKL_API.G_RET_STS_SUCCESS;
1006
1007 x_return_status := OKL_API.START_ACTIVITY(
1008 p_api_name => l_api_name,
1009 p_init_msg_list => p_init_msg_list,
1010 p_api_type => '_PVT',
1011 x_return_status => x_return_status);
1012
1013 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1014 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1015 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1016 RAISE OKL_API.G_EXCEPTION_ERROR;
1017 END IF;
1018
1019 OKL_VARIABLE_INTEREST_PVT.print_debug(p_trx_id || 'Contract ID: '||p_chr_id);
1020 OKL_VARIABLE_INTEREST_PVT.print_debug(p_trx_id || 'Trans Id: ' || p_trx_id);
1021 OKL_VARIABLE_INTEREST_PVT.print_debug(p_trx_id || 'Trans Status: ' || p_trx_status);
1022
1023 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1024
1025 --which is the kle id in this table
1026 l_rent_tbl := p_rent_tbl;
1027 l_csm_loan_level_tbl := p_csm_loan_level_tbl;
1028
1029 OKL_VARIABLE_INTEREST_PVT.print_debug('=======>Start - Input from Super Trump or initiate_request (for Principal Payment).');
1030 print_loan_tables(p_rent_tbl => l_rent_tbl,
1031 p_csm_loan_level_tbl => l_csm_loan_level_tbl);
1032 OKL_VARIABLE_INTEREST_PVT.print_debug('=======>End - Input from Super Trump or initiate_request (for Principal Payment).');
1033
1034 OPEN c_last_int_cur (p_chr_id);
1035 FETCH c_last_int_cur INTO l_reamort_date, l_currency_code;
1036 CLOSE c_last_int_cur;
1037
1038 OKL_VARIABLE_INTEREST_PVT.print_debug(p_trx_id || 'Reamort Date: '||l_reamort_date || ' Currency Code: ' || l_currency_code);
1039
1040 l_rent_tbl_cntr := l_rent_tbl.first;
1041 LOOP
1042 EXIT WHEN l_rent_tbl_cntr IS NULL;
1043 --round the amount
1044 IF (l_rent_tbl(l_rent_tbl_cntr).amount IS NOT NULL) THEN
1045 l_rent_tbl(l_rent_tbl_cntr).amount := OKL_ACCOUNTING_UTIL.round_amount(l_rent_tbl(l_rent_tbl_cntr).amount, l_currency_code);
1046 END IF;
1047
1048 l_rent_tbl_cntr := l_rent_tbl.next(l_rent_tbl_cntr);
1049 END LOOP;
1050
1051 l_index_number := NULL;
1052 l_prev_index_number := NULL;
1053 l_kle_id := NULL;
1054 l_prev_kle_id := NULL;
1055 l_rbk_tbl_cntr := 0;
1056 l_strm_lalevl_tbl_cntr := 0;
1057 l_sequence := 0;
1058 l_fetch_prior_periods := TRUE;
1059
1060 l_rent_tbl_cntr := l_rent_tbl.first;
1061 LOOP
1062 EXIT WHEN l_rent_tbl_cntr IS NULL;
1063
1064 --get kle_id
1065 l_index_number := l_rent_tbl(l_rent_tbl_cntr).index_number;
1066 OKL_VARIABLE_INTEREST_PVT.print_debug(p_trx_id || 'l_index_number: '||l_index_number);
1067
1068 IF (p_trx_id IS NOT NULL AND l_index_number IS NOT NULL) THEN
1069 --for supertrump request
1070 IF (NVL(l_prev_index_number, -99) <> l_index_number) THEN
1071 OKL_VARIABLE_INTEREST_PVT.print_debug(p_trx_id || 'deriving kle id');
1072 l_kle_id := get_kle_id(p_trx_number => p_trx_id,
1073 p_index_number => l_index_number,
1074 x_return_status => x_return_status);
1075
1076 IF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
1077 OKL_VARIABLE_INTEREST_PVT.print_debug(p_trx_id || 'Unable to derive kle_id from inbound supertrump call.');
1078 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1079 ELSE
1080 OKL_VARIABLE_INTEREST_PVT.print_debug('l_kle_id: '||l_kle_id);
1081 END IF;
1082 l_sequence := 0;
1083 OKL_VARIABLE_INTEREST_PVT.print_debug(p_trx_id || 'l_sequence: '||l_sequence);
1084 l_prev_index_number := l_index_number;
1085 END IF;
1086 ELSE
1087 --for initiate request for Principal Payment
1088 l_kle_id := l_csm_loan_level_tbl(l_rent_tbl_cntr).kle_loan_id;
1089 l_sequence := 0;
1090 OKL_VARIABLE_INTEREST_PVT.print_debug(p_trx_id || 'l_sequence: '||l_sequence);
1091 OKL_VARIABLE_INTEREST_PVT.print_debug(p_trx_id || 'l_kle_id: '||l_kle_id);
1092 END IF;
1093
1094 IF (NVL(l_prev_kle_id, -99) <> l_kle_id) THEN
1095 l_fetch_prior_periods := TRUE;
1096 OKL_VARIABLE_INTEREST_PVT.print_debug(p_trx_id || 'setting the l_fetch_prior_periods to true');
1097 END IF;
1098
1099 IF (l_fetch_prior_periods) THEN
1100 OKL_VARIABLE_INTEREST_PVT.print_debug('l_fetch_prior_periods is true: fecthing LASLL/LASLH info for kle : ' || l_kle_id);
1101 FOR l_pmt_cur IN l_pmt_csr (p_chr_id, l_kle_id) LOOP
1102 print_pmt_csr(p_pmt_rec => l_pmt_cur);
1103
1104 -- 10367844: Fetch frequency from Rent/Principal Payment/Loan Payment SLLs of Asset Only
1105 OPEN c_freq_cur(p_chr_id, l_kle_id);
1106 FETCH c_freq_cur INTO l_frequency;
1107 CLOSE c_freq_cur;
1108
1109 OKL_VARIABLE_INTEREST_PVT.print_debug(p_trx_id || 'l_frequency: '||l_frequency);
1110
1111 if(l_frequency = 'M') THEN
1112 l_adder_months := 1;
1113 elsif(l_frequency = 'Q') THEN
1114 l_adder_months := 3;
1115 elsif(l_frequency = 'S') THEN
1116 l_adder_months := 6;
1117 elsif(l_frequency = 'A') THEN
1118 l_adder_months := 12;
1119 end if;
1120 OKL_VARIABLE_INTEREST_PVT.print_debug(p_trx_id || 'l_frequency: '||l_frequency || ' l_adder_months: ' || l_adder_months);
1121
1122 --populate l_rbk_tbl table and LASLH information
1123 OKL_VARIABLE_INTEREST_PVT.print_debug(p_trx_id || 'previous kle_id : ' || l_prev_kle_id || '<-> current kle_id : ' || l_kle_id);
1124 IF (NVL(l_prev_kle_id, -99) <> l_kle_id) THEN
1125 OKL_VARIABLE_INTEREST_PVT.print_debug(p_trx_id || 'populating l_rbk_tbl');
1126 l_rbk_tbl_cntr := l_rbk_tbl_cntr + 1;
1127 l_rbk_tbl(l_rbk_tbl_cntr).KHR_ID := p_chr_id;
1128 l_rbk_tbl(l_rbk_tbl_cntr).KLE_ID := l_kle_id;
1129 OKL_VARIABLE_INTEREST_PVT.print_debug(p_trx_id || 'done populating l_rbk_tbl');
1130
1131 OKL_VARIABLE_INTEREST_PVT.print_debug(p_trx_id || 'populating l_strm_lalevl_tbl with LASLH information');
1132 l_strm_lalevl_tbl_cntr := l_strm_lalevl_tbl_cntr + 1;
1133 l_strm_lalevl_tbl(l_strm_lalevl_tbl_cntr).Chr_Id := p_chr_id;
1134 l_strm_lalevl_tbl(l_strm_lalevl_tbl_cntr).Cle_Id := l_kle_id;
1135 l_strm_lalevl_tbl(l_strm_lalevl_tbl_cntr).jtot_object1_code := l_pmt_cur.stream_type_source;
1136 l_strm_lalevl_tbl(l_strm_lalevl_tbl_cntr).jtot_object2_code := l_pmt_cur.time_value;
1137 l_strm_lalevl_tbl(l_strm_lalevl_tbl_cntr).rule_information1 := l_pmt_cur.billing_schedule_type;
1138 l_strm_lalevl_tbl(l_strm_lalevl_tbl_cntr).rule_information2 := l_pmt_cur.rate_type;
1139 l_strm_lalevl_tbl(l_strm_lalevl_tbl_cntr).Rule_Information_category := l_pmt_cur.slh_rule_information_category;
1140 l_strm_lalevl_tbl(l_strm_lalevl_tbl_cntr).object1_id1 := l_pmt_cur.sty_id;
1141 l_prev_kle_id := l_kle_id;
1142 l_time_unit_of_measure := l_pmt_cur.time_unit_of_measure;
1143 l_stream_level_header := l_pmt_cur.stream_level_header;
1144 l_sll_rule_information_cat := l_pmt_cur.sll_rule_information_category;
1145 l_tuoms_per_period := l_pmt_cur.tuoms_per_period;
1146 l_structure := l_pmt_cur.structure;
1147 OKL_VARIABLE_INTEREST_PVT.print_debug(p_trx_id || 'done populating l_strm_lalevl_tbl with LASLH information');
1148 END IF;
1149
1150 l_prior_level_date_start := FND_DATE.canonical_to_date(l_pmt_cur.start_date);
1151 OKL_VARIABLE_INTEREST_PVT.print_debug(p_trx_id || 'l_prior_level_date_start : ' || l_prior_level_date_start);
1152 l_level_date_start := NULL;
1153 l_prior_periods := 0;
1154
1155 --Added by bkatraga for bug 13693187
1156 IF(l_pmt_cur.number_periods IS NULL AND l_pmt_cur.amount IS NULL) THEN
1157 IF (l_prior_level_date_start < l_reamort_date) THEN
1158 IF (l_level_date_start IS NULL) THEN
1159 l_level_date_start := l_prior_level_date_start;
1160 END IF;
1161
1162 l_strm_lalevl_tbl_cntr := l_strm_lalevl_tbl_cntr + 1;
1163 l_strm_lalevl_tbl(l_strm_lalevl_tbl_cntr).Chr_Id := p_chr_id;
1164 l_strm_lalevl_tbl(l_strm_lalevl_tbl_cntr).Cle_Id := l_kle_id;
1165 l_strm_lalevl_tbl(l_strm_lalevl_tbl_cntr).Rule_Information3 := NULL;
1166 l_strm_lalevl_tbl(l_strm_lalevl_tbl_cntr).Jtot_Object1_Code := l_pmt_cur.time_unit_of_measure;
1167 l_strm_lalevl_tbl(l_strm_lalevl_tbl_cntr).Jtot_Object2_Code := l_pmt_cur.stream_level_header;
1168 l_stream_level_header := l_pmt_cur.stream_level_header;
1169 l_strm_lalevl_tbl(l_strm_lalevl_tbl_cntr).Object1_id1 := l_pmt_cur.Pay_freq;
1170 l_sequence := l_sequence + 1;
1171 l_strm_lalevl_tbl(l_strm_lalevl_tbl_cntr).Rule_Information1 := l_sequence;
1172 l_strm_lalevl_tbl(l_strm_lalevl_tbl_cntr).Rule_Information2 := fnd_date.date_to_canonical(l_level_date_start);
1173 l_strm_lalevl_tbl(l_strm_lalevl_tbl_cntr).Rule_Information4 := l_pmt_cur.tuoms_per_period;
1174 l_strm_lalevl_tbl(l_strm_lalevl_tbl_cntr).Rule_Information5 := l_structure;
1175 l_strm_lalevl_tbl(l_strm_lalevl_tbl_cntr).Rule_Information6 := NULL;
1176 l_strm_lalevl_tbl(l_strm_lalevl_tbl_cntr).Rule_Information7 := l_pmt_cur.stub_days;
1177 l_strm_lalevl_tbl(l_strm_lalevl_tbl_cntr).Rule_Information8 := l_pmt_cur.stub_amount;
1178 l_strm_lalevl_tbl(l_strm_lalevl_tbl_cntr).Rule_Information10 := l_pmt_cur.advance_or_arrears;
1179 l_strm_lalevl_tbl(l_strm_lalevl_tbl_cntr).Rule_Information_category := l_pmt_cur.sll_rule_information_category;
1180 l_strm_lalevl_tbl(l_strm_lalevl_tbl_cntr).Rule_Information13 := l_pmt_cur.rate;
1181 END IF;
1182 l_prior_level_date_start := l_prior_level_date_start + to_number(l_pmt_cur.stub_days);
1183 ELSE
1184 --end bkatraga
1185 FOR l_period_cntr IN 1..NVL(l_pmt_cur.number_periods, 1) LOOP
1186 IF (l_prior_level_date_start < l_reamort_date) THEN
1187 l_prior_periods := l_prior_periods + 1;
1188
1189 IF (l_level_date_start IS NULL) THEN
1190 l_level_date_start := l_prior_level_date_start;
1191 OKL_VARIABLE_INTEREST_PVT.print_debug(p_trx_id || 'l_level_date_start : ' || l_level_date_start);
1192 END IF;
1193 END IF;
1194
1195 --Added by bkatraga for bug 15946001
1196 l_prior_level_date_start := OKL_VARIABLE_INTEREST_PVT.get_next_period_start_date(p_chr_id, l_prior_level_date_start);
1197 IF(l_prior_level_date_start IS NULL) THEN
1198 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1199 END IF;
1200 --end bkatraga
1201
1202 OKL_VARIABLE_INTEREST_PVT.print_debug(p_trx_id || 'l_period_cntr :' || l_period_cntr || ' l_prior_level_date_start : ' || l_prior_level_date_start);
1203 END LOOP;
1204 OKL_VARIABLE_INTEREST_PVT.print_debug(p_trx_id || 'l_prior_level_date_start : ' || l_prior_level_date_start);
1205 OKL_VARIABLE_INTEREST_PVT.print_debug(p_trx_id || 'l_prior_periods : ' || l_prior_periods);
1206
1207 IF (l_prior_periods > 0) THEN
1208 l_strm_lalevl_tbl_cntr := l_strm_lalevl_tbl_cntr + 1;
1209 l_strm_lalevl_tbl(l_strm_lalevl_tbl_cntr).Chr_Id := p_chr_id;
1210 l_strm_lalevl_tbl(l_strm_lalevl_tbl_cntr).Cle_Id := l_kle_id;
1211 l_strm_lalevl_tbl(l_strm_lalevl_tbl_cntr).Rule_Information3 := l_prior_periods;
1212 l_strm_lalevl_tbl(l_strm_lalevl_tbl_cntr).Jtot_Object1_Code := l_pmt_cur.time_unit_of_measure;
1213 l_strm_lalevl_tbl(l_strm_lalevl_tbl_cntr).Jtot_Object2_Code := l_pmt_cur.stream_level_header;
1214 l_stream_level_header := l_pmt_cur.stream_level_header;
1215 l_strm_lalevl_tbl(l_strm_lalevl_tbl_cntr).Object1_id1 := l_pmt_cur.Pay_freq;
1216 l_sequence := l_sequence + 1;
1217 l_strm_lalevl_tbl(l_strm_lalevl_tbl_cntr).Rule_Information1 := l_sequence;
1218 l_strm_lalevl_tbl(l_strm_lalevl_tbl_cntr).Rule_Information2 := fnd_date.date_to_canonical(l_level_date_start);
1219 l_strm_lalevl_tbl(l_strm_lalevl_tbl_cntr).Rule_Information4 := l_pmt_cur.tuoms_per_period;
1220 l_strm_lalevl_tbl(l_strm_lalevl_tbl_cntr).Rule_Information5 := l_structure;
1221 l_strm_lalevl_tbl(l_strm_lalevl_tbl_cntr).Rule_Information6 := l_pmt_cur.amount;
1222 l_strm_lalevl_tbl(l_strm_lalevl_tbl_cntr).Rule_Information7 := l_pmt_cur.stub_days;
1223 l_strm_lalevl_tbl(l_strm_lalevl_tbl_cntr).Rule_Information8 := l_pmt_cur.stub_amount;
1224 l_strm_lalevl_tbl(l_strm_lalevl_tbl_cntr).Rule_Information10 := l_pmt_cur.advance_or_arrears;
1225 l_strm_lalevl_tbl(l_strm_lalevl_tbl_cntr).Rule_Information_category := l_pmt_cur.sll_rule_information_category;
1226 l_strm_lalevl_tbl(l_strm_lalevl_tbl_cntr).Rule_Information13 := l_pmt_cur.rate;
1227
1228 OKL_VARIABLE_INTEREST_PVT.print_debug(p_trx_id || '=>Start - Prior Periods Information in -> l_strm_lalevl_tbl.');
1229 print_var_int_tables(p_rbk_tbl => l_rbk_tbl,
1230 p_strm_lalevl_tbl => l_strm_lalevl_tbl);
1231 OKL_VARIABLE_INTEREST_PVT.print_debug(p_trx_id || '=>End - Prior Periods Information in -> l_strm_lalevl_tbl.');
1232 END IF;
1233 END IF; --Added by bkatraga for bug 13693187
1234 END LOOP; --l_pmt_cur
1235 l_fetch_prior_periods := FALSE;
1236 END IF;
1237
1238 l_strm_lalevl_tbl_cntr := l_strm_lalevl_tbl_cntr + 1;
1239 l_sequence := l_sequence + 1;
1240 l_strm_lalevl_tbl(l_strm_lalevl_tbl_cntr).Chr_Id := p_chr_id;
1241 l_strm_lalevl_tbl(l_strm_lalevl_tbl_cntr).Cle_Id := l_kle_id;
1242 l_strm_lalevl_tbl(l_strm_lalevl_tbl_cntr).Jtot_Object1_Code := l_time_unit_of_measure;
1243 l_strm_lalevl_tbl(l_strm_lalevl_tbl_cntr).Jtot_Object2_Code := l_stream_level_header;
1244 l_strm_lalevl_tbl(l_strm_lalevl_tbl_cntr).Rule_Information_category := l_sll_rule_information_cat;
1245 l_strm_lalevl_tbl(l_strm_lalevl_tbl_cntr).Rule_Information1 := l_sequence;
1246 l_strm_lalevl_tbl(l_strm_lalevl_tbl_cntr).Rule_Information3 := l_rent_tbl(l_rent_tbl_cntr).number_of_periods;
1247 l_strm_lalevl_tbl(l_strm_lalevl_tbl_cntr).Rule_Information4 := l_tuoms_per_period;
1248 l_strm_lalevl_tbl(l_strm_lalevl_tbl_cntr).Rule_Information5 := l_structure;
1249 l_strm_lalevl_tbl(l_strm_lalevl_tbl_cntr).Rule_Information6 := l_rent_tbl(l_rent_tbl_cntr).amount;
1250
1251 --if the payment level is a stub
1252 IF (UPPER(l_rent_tbl(l_rent_tbl_cntr).period) IN ('STUB', 'T')) THEN
1253 OKL_VARIABLE_INTEREST_PVT.print_debug(p_trx_id || 'calling get_stub_info with p_kle_id => ' || l_kle_id || ' and p_start_date => ' || l_rent_tbl(l_rent_tbl_cntr).first_payment_date);
1254 l_ret_val := get_stub_info(p_kle_id => l_kle_id,
1255 p_start_date => l_rent_tbl(l_rent_tbl_cntr).first_payment_date,
1256 x_stub_start_date => l_stub_start_date,
1257 x_stub_days => l_stub_days,
1258 x_stub_amount => l_stub_amount);
1259 OKL_VARIABLE_INTEREST_PVT.print_debug(p_trx_id || ' output of get_stub_info :');
1260 OKL_VARIABLE_INTEREST_PVT.print_debug(p_trx_id || ' l_stub_start_date => ' || l_stub_start_date);
1261 OKL_VARIABLE_INTEREST_PVT.print_debug(p_trx_id || ' l_stub_days => ' || l_stub_days);
1262 OKL_VARIABLE_INTEREST_PVT.print_debug(p_trx_id || ' l_stub_amount => ' || l_stub_amount);
1263
1264 l_strm_lalevl_tbl(l_strm_lalevl_tbl_cntr).Rule_Information2 := fnd_date.date_to_canonical(l_stub_start_date);
1265 l_strm_lalevl_tbl(l_strm_lalevl_tbl_cntr).Rule_Information3 := NULL;
1266 l_strm_lalevl_tbl(l_strm_lalevl_tbl_cntr).Rule_Information6 := NULL;
1267 l_strm_lalevl_tbl(l_strm_lalevl_tbl_cntr).Rule_Information7 := l_stub_days;
1268 l_strm_lalevl_tbl(l_strm_lalevl_tbl_cntr).Rule_Information8 := l_rent_tbl(l_rent_tbl_cntr).amount;
1269 ELSE
1270 l_strm_lalevl_tbl(l_strm_lalevl_tbl_cntr).Rule_Information2 := fnd_date.date_to_canonical(l_rent_tbl(l_rent_tbl_cntr).first_payment_date);
1271 END IF;
1272
1273 IF (p_trx_id IS NOT NULL) THEN
1274 l_strm_lalevl_tbl(l_strm_lalevl_tbl_cntr).Rule_Information10 := l_rent_tbl(l_rent_tbl_cntr).advance_or_arrears;
1275 ELSE
1276 IF (NVL(l_rent_tbl(l_rent_tbl_cntr).advance_or_arrears, 'DODDLES') = 'ARREARS') THEN
1277 l_strm_lalevl_tbl(l_strm_lalevl_tbl_cntr).Rule_Information10 := 'Y';
1278 ELSE
1279 l_strm_lalevl_tbl(l_strm_lalevl_tbl_cntr).Rule_Information10 := 'N';
1280 END IF;
1281 END IF;
1282
1283 l_strm_lalevl_tbl(l_strm_lalevl_tbl_cntr).Rule_Information13 := l_rent_tbl(l_rent_tbl_cntr).rate;
1284 l_strm_lalevl_tbl(l_strm_lalevl_tbl_cntr).Object1_id1 := l_frequency;
1285
1286 OKL_VARIABLE_INTEREST_PVT.print_debug(p_trx_id || '=>Start - Current and prior Periods Information in -> l_strm_lalevl_tbl.');
1287 print_var_int_tables(p_rbk_tbl => l_rbk_tbl,
1288 p_strm_lalevl_tbl => l_strm_lalevl_tbl);
1289 OKL_VARIABLE_INTEREST_PVT.print_debug(p_trx_id || '=>End - Current and prior Periods Information in -> l_strm_lalevl_tbl.');
1290
1291 l_rent_tbl_cntr := l_rent_tbl.next(l_rent_tbl_cntr);
1292 END LOOP; --l_rent_tbl
1293
1294 OKL_VARIABLE_INTEREST_PVT.print_debug(p_trx_id || '');
1295 OKL_VARIABLE_INTEREST_PVT.print_debug(p_trx_id || '=>Start - Before consolidating SLL, contents of l_rbk_tbl and l_strm_lalevl_tbl.');
1296
1297 print_var_int_tables(p_rbk_tbl => l_rbk_tbl,
1298 p_strm_lalevl_tbl => l_strm_lalevl_tbl);
1299 OKL_VARIABLE_INTEREST_PVT.print_debug('=>End - Before consolidating SLL, contents of l_rbk_tbl and l_strm_lalevl_tbl.');
1300
1301 consolidate_sll(p_strm_lalevl_tbl => l_strm_lalevl_tbl,
1302 x_strm_lalevl_tbl => l_strm_lalevl_tbl_out);
1303
1304 l_strm_lalevl_tbl := l_strm_lalevl_tbl_out;
1305
1306 OKL_VARIABLE_INTEREST_PVT.print_debug(p_trx_id || '');
1307 OKL_VARIABLE_INTEREST_PVT.print_debug(p_trx_id || '=>Start - After consolidation of SLL, before passing to mass rebook, contents of l_rbk_tbl and l_strm_lalevl_tbl.');
1308
1309 print_var_int_tables(p_rbk_tbl => l_rbk_tbl,
1310 p_strm_lalevl_tbl => l_strm_lalevl_tbl);
1311
1312 OKL_VARIABLE_INTEREST_PVT.print_debug(p_trx_id || '=>End - After consolidation of SLL, before passing to mass rebook, contents of l_rbk_tbl and l_strm_lalevl_tbl.');
1313
1314
1315 OKL_MASS_REBOOK_PVT.apply_mass_rebook(
1316 p_api_version => p_api_version,
1317 p_init_msg_list => p_init_msg_list,
1318 x_return_status => x_return_status,
1319 x_msg_count => x_msg_count,
1320 x_msg_data => x_msg_data,
1321 p_rbk_tbl => l_rbk_tbl,
1322 p_deprn_method_code => NULL,
1323 p_in_service_date => NULL,
1324 p_life_in_months => NULL,
1325 p_basic_rate => NULL,
1326 p_adjusted_rate => NULL,
1327 p_residual_value => NULL,
1328 p_strm_lalevl_tbl => l_strm_lalevl_tbl,
1329 p_transaction_date => l_reamort_date,
1330 x_stream_trx_tbl => x_strm_trx_tbl
1331 );
1332
1333 OKL_VARIABLE_INTEREST_PVT.print_debug(p_trx_id || 'After Mass Rebook status: '||x_return_status||' Error: '||x_msg_data);
1334 OKL_VARIABLE_INTEREST_PVT.print_debug(p_trx_id || 'After Mass Rebook x_strm_trx_tbl.count: '|| x_strm_trx_tbl.count);
1335 FND_FILE.PUT_LINE (FND_FILE.LOG,p_trx_id || p_trx_id || 'After mass rebook Stream Table count: '|| x_strm_trx_tbl.count);
1336
1337 IF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
1338 FOR i in 1..x_msg_count
1339 LOOP
1340 FND_MSG_PUB.GET(
1341 p_msg_index => i,
1342 p_encoded => FND_API.G_FALSE,
1343 p_data => x_msg_data,
1344 p_msg_index_out => l_msg_index_out
1345 );
1346 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,p_trx_id || 'Error: '||to_char(i)||': '||x_msg_data);
1347 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,p_trx_id || 'Message Index: '||l_msg_index_out);
1348 OKL_VARIABLE_INTEREST_PVT.print_debug(p_trx_id || 'Error '||to_char(i)||': '||x_msg_data);
1349 OKL_VARIABLE_INTEREST_PVT.print_debug(p_trx_id || 'Message Index: '||l_msg_index_out);
1350 END LOOP;
1351
1352 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1353 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1354 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1355 RAISE OKL_API.G_EXCEPTION_ERROR;
1356 END IF;
1357 ELSE
1358 i := x_strm_trx_tbl.first;
1359 l_child_trx_id := x_strm_trx_tbl(i).trx_number;
1360 x_child_trx_id :=l_child_trx_id;
1361
1362 OKL_VARIABLE_INTEREST_PVT.print_debug(p_trx_id || 'child trx number :' || l_child_trx_id);
1363 loop
1364 exit when i is null;
1365 OKL_VARIABLE_INTEREST_PVT.print_debug(p_trx_id || 'x_strm_trx_tbl - element # ' || i || ' : ' || x_strm_trx_tbl(i).trx_number);
1366 i := x_strm_trx_tbl.next(i);
1367 end loop;
1368
1369 -- Updating the Interest Calculation Date
1370
1371 OKL_VARIABLE_INTEREST_PVT.print_debug(p_trx_id || 'Khr id : ' || p_chr_id || ' Frequency : ' || l_frequency);
1372 OKL_VARIABLE_INTEREST_PVT.print_debug(p_trx_id || 'Before updating date_last_interim_interest_cal');
1373
1374 --Added by bkatraga for bug 13693187
1375 l_next_reamort_date := OKL_VARIABLE_INTEREST_PVT.get_next_period_start_date(p_chr_id, l_reamort_date);
1376 IF(l_next_reamort_date IS NULL) THEN
1377 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1378 END IF;
1379
1380 OKL_VR_DATA_HANDLER_PVT.UPDATE_LAST_INT_CAL_DATE(
1381 p_api_version => p_api_version,
1382 p_init_msg_list => p_init_msg_list,
1383 x_return_status => x_return_status,
1384 x_msg_count => x_msg_count,
1385 x_msg_data => x_msg_data,
1386 p_contract_id => p_chr_id,
1387 p_last_interest_cal_date => l_next_reamort_date);
1388 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1389 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1390 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1391 RAISE OKL_API.G_EXCEPTION_ERROR;
1392 END IF;
1393 --end bkatraga
1394
1395 OKL_VARIABLE_INTEREST_PVT.print_debug(p_trx_id || 'After updating date_last_interim_interest_cal');
1396
1397 --only for inbound supertrump call
1398 IF (p_trx_id IS NOT NULL) THEN
1399 OKL_VARIABLE_INTEREST_PVT.print_debug(p_trx_id || 'Fetching Var Int Process Id for trx number: ' || p_trx_id);
1400 OPEN l_vip_csr(p_trx_id);
1401 FETCH l_vip_csr INTO l_vipv_rec.id;
1402 IF l_vip_csr%NOTFOUND THEN
1403 OKL_VARIABLE_INTEREST_PVT.print_debug(p_trx_id || 'Var Int Process Id not found for trx number: ' || p_trx_id);
1404 ELSE
1405 OKL_VARIABLE_INTEREST_PVT.print_debug(p_trx_id || 'Var Int Process Id found for trx number: ' || p_trx_id);
1406 END IF;
1407 CLOSE l_vip_csr;
1408
1409 i := x_strm_trx_tbl.first;
1410 l_vipv_rec.child_trx_id := x_strm_trx_tbl(i).trx_number;
1411 i := null;
1412
1413 OKL_VARIABLE_INTEREST_PVT.print_debug(p_trx_id || 'Before updating okl_var_int_process_b');
1414 OKL_VARIABLE_INTEREST_PVT.print_debug(p_trx_id|| 'l_vipv_rec.id =' || l_vipv_rec.id );
1415
1416 -- Bug 10324172 : If ESG is behaving asynchronous, then only update it
1417 -- Asynchronous means, insertion has already happened before Update
1418 IF l_vipv_rec.ID IS NOT NULL AND l_vipv_rec.ID <> OKL_API.G_MISS_NUM
1419 THEN
1420 OKL_VARIABLE_INTEREST_PVT.print_debug(p_trx_id || 'Before call to okl_var_int_process_b.update_row' );
1421 OKL_VIP_PVT.update_row(
1422 p_api_version => p_api_version,
1423 p_init_msg_list => p_init_msg_list,
1424 x_return_status => x_return_status,
1425 x_msg_count => x_msg_count,
1426 x_msg_data => x_msg_data,
1427 p_vipv_rec => l_vipv_rec,
1428 x_vipv_rec => x_vipv_rec);
1429 OKL_VARIABLE_INTEREST_PVT.print_debug(p_trx_id || 'After updating okl_var_int_process_b, Status => ' || x_return_status);
1430 OKL_VARIABLE_INTEREST_PVT.print_debug(p_trx_id || '****End-Creating Rent Levels and ReBooking.');
1431 OKL_VARIABLE_INTEREST_PVT.print_debug(p_trx_id || '****Exiting procedure VAR_INT_RENT_LEVEL****');
1432 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1433 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1434 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1435 RAISE OKL_API.G_EXCEPTION_ERROR;
1436 END IF;
1437 END IF;
1438 -- Bug 10324172 : End
1439 END IF;
1440 END IF;
1441 Exception
1442 WHEN OTHERS THEN
1443 OKL_VARIABLE_INTEREST_PVT.print_debug(p_trx_id || 'sqlcode : ' || sqlcode || ' $ sqlerrm : ' || sqlerrm);
1444 x_return_status := OKL_API.HANDLE_EXCEPTIONS (
1445 p_api_name => l_api_name,
1446 p_pkg_name => G_PKG_NAME,
1447 p_exc_name => 'OTHERS',
1448 x_msg_count => x_msg_count,
1449 x_msg_data => x_msg_data,
1450 p_api_type => '_PVT');
1451 END var_int_rent_level;
1452
1453
1454
1455 -----------------------------------------------------------------------------------------
1456 PROCEDURE initiate_request(p_api_version IN NUMBER,
1457 p_init_msg_list IN VARCHAR2,
1458 p_khr_id IN NUMBER,
1459 x_return_status OUT NOCOPY VARCHAR2,
1460 x_msg_count OUT NOCOPY NUMBER,
1461 x_msg_data OUT NOCOPY VARCHAR2)
1462 IS
1463
1464
1465 l_vipv_rec OKL_VARIABLE_INTEREST_PVT.vipv_rec;
1466 x_vipv_rec OKL_VARIABLE_INTEREST_PVT.vipv_rec;
1467 l_skip_prc_engine VARCHAR2(1) := OKL_API.G_FALSE;
1468 --l_rents_tbl and l_rents_tbl_in used for lease processing
1469 l_rents_tbl Okl_Create_Streams_Pub.csm_periodic_expenses_tbl_type;
1470 l_rents_tbl_in Okl_Create_Streams_Pub.csm_periodic_expenses_tbl_type;
1471 --l_rents_prin_tbl used for loan processing
1472 l_rents_prin_tbl OKL_VARIABLE_INTEREST_PVT.csm_periodic_expenses_tbl_type;
1473 l_csm_loan_header okl_create_streams_pvt.csm_loan_rec_type;
1474 l_csm_loan_lines_tbl okl_create_streams_pvt.csm_loan_line_tbl_type;
1475 l_csm_loan_levels_tbl okl_create_streams_pvt.csm_loan_level_tbl_type;
1476 l_csm_one_off_fee_tbl Okl_Create_Streams_Pub.csm_one_off_fee_tbl_type;
1477 l_csm_periodic_expenses_tbl Okl_Create_Streams_Pub.csm_periodic_expenses_tbl_type;
1478 l_csm_yields_tbl Okl_Create_Streams_Pub.csm_yields_tbl_type;
1479 l_csm_stream_types_tbl Okl_Create_Streams_Pub.csm_stream_types_tbl_type;
1480 l_csm_loan_levels_tbl_in OKL_VARIABLE_INTEREST_PVT.csm_loan_level_tbl_type;
1481 l_csm_lease_header okl_create_streams_pvt.csm_lease_rec_type;
1482 l_csm_line_details_tbl okl_create_streams_pvt.csm_line_details_tbl_type;
1483 l_req_stream_types_tbl Okl_Create_Streams_Pub.csm_stream_types_tbl_type;
1484
1485 l_index NUMBER := 0;
1486 l_return_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
1487 l_api_name CONSTANT VARCHAR2(30) := 'initiate_request';
1488 l_api_version CONSTANT NUMBER := 1;
1489 l_start_date DATE;
1490 amount NUMBER;
1491 tot_amount NUMBER;
1492 l_no_of_periods NUMBER;
1493 l_reamort_date DATE;
1494 chr_start_date DATE;
1495 lx_interest_rec OKL_VARIABLE_INTEREST_PVT.interest_rec;
1496 l_deal_type OKL_K_HEADERS.DEAL_TYPE%TYPE;
1497 l_msg_index_out NUMBER;
1498 l_term_duration NUMBER;
1499 l_tot_principal_amount NUMBER := 0;
1500 l_frequency okc_rules_b.object1_id1%type;
1501 l_csm_line_details_ctr NUMBER;
1502 l_first_row NUMBER;
1503 l_loan_levels_cntr NUMBER;
1504 l_loan_levels_date_start DATE;
1505 l_period_cntr NUMBER := 0;
1506 l_number_of_periods NUMBER := 0;
1507 l_adder_months NUMBER := 0;
1508 l_interest_rate_tbl OKL_VARIABLE_INTEREST_PVT.interest_rate_tbl_type;
1509 l_interest_rate_tbl_count NUMBER;
1510 l_interest_rate_tbl_index NUMBER;
1511 l_contract_number okc_k_headers_b.contract_number%type;
1512 l_principal_balance_tbl okl_variable_interest_pvt.principal_balance_tbl_typ;
1513 l_total_lending NUMBER;
1514
1515 l_rent_date_start DATE;
1516 l_rent_cntr NUMBER;
1517
1518 l_level_date_start DATE;
1519 l_stub_level_date_start DATE;
1520
1521 l_super_trump_request_id NUMBER;
1522 l_trans_status OKL_STREAM_INTERFACES.SIS_CODE%TYPE;
1523
1524 l_request_id NUMBER;
1525 l_program_application_id NUMBER;
1526 l_program_id NUMBER;
1527 l_program_update_date DATE;
1528 l_sequence NUMBER := 0;
1529 l_prev_kle_id NUMBER := NULL;
1530 l_remaining_term_in_months NUMBER;
1531 l_child_trx_id NUMBER := NULL;
1532 l_advance_or_arrears VARCHAR2(100) := NULL;
1533 initiate_request_failed EXCEPTION;
1534 -- Added by prasjain bug# 6142095
1535 l_interest_rate okl_var_int_params.INTEREST_RATE%type;
1536 l_rebook_flag BOOLEAN := TRUE;
1537 l_interest_calc_end_date DATE;
1538 -- End by prasjain bug# 6142095
1539 l_next_reamort_date DATE; --Added by bkatraga for bug 13693187
1540 l_line_prin_tbl line_principal_tbl_type;
1541 l_line_prin_counter NUMBER := 0;
1542 l_asset_prin_bal NUMBER := 0; --Added by bkatraga for bug 16303285
1543
1544 --Added by bkatraga for bug 16303285
1545 CURSOR get_asset_prin_bal (p_trx_number NUMBER,
1546 p_kle_id NUMBER)
1547 IS
1548 SELECT SRLB.REAMORT_BALANCE
1549 FROM OKL_SIF_RET_LEVELS SRLB,
1550 OKL_SIF_RETS SIRB,
1551 OKL_STREAM_INTERFACES SIFB,
1552 OKL_SIF_LINES SILB
1553 WHERE SIFB.TRANSACTION_NUMBER = p_trx_number
1554 AND SIRB.TRANSACTION_NUMBER = SIFB.TRANSACTION_NUMBER
1555 AND SIRB.INDEX_NUMBER = 0
1556 AND SILB.SIF_ID = SIFB.ID
1557 AND SRLB.SIR_ID = SIRB.ID
1558 AND SRLB.INDEX_NUMBER = SILB.INDEX_NUMBER
1559 AND SRLB.LEVEL_TYPE IN ('Payment','Principal')
1560 AND SRLB.LOCK_LEVEL_STEP = 'N'
1561 AND SRLB.LEVEL_INDEX_NUMBER = 0
1562 AND SILB.KLE_ID = p_kle_id;
1563
1564 --Added by bkatraga for bug 14811647
1565 CURSOR check_asset_term_csr(p_kle_id OKC_K_LINES_B.ID%TYPE) IS
1566 SELECT 'Y'
1567 FROM OKC_K_LINES_B CLE
1568 , OKC_STATUSES_B STS
1569 WHERE CLE.ID = p_kle_id
1570 AND CLE.LSE_ID = 33
1571 AND STS.CODE = CLE.STS_CODE
1572 AND STS.STE_CODE = 'TERMINATED';
1573
1574 l_asset_term_flag VARCHAR2(1):= 'N';
1575 l_line_count NUMBER;
1576 l_tmp_line_details_tbl okl_create_streams_pvt.csm_line_details_tbl_type;
1577 j NUMBER;
1578 l_tmp_loan_lines_tbl okl_create_streams_pvt.csm_loan_line_tbl_type;
1579 l_tmp_loan_levels_tbl okl_create_streams_pvt.csm_loan_level_tbl_type;
1580 --end bkatraga for bug 14811647
1581
1582 --dkagrawa changed query to use view okl_prod_qlty_val_uv than okl_product_parameters_v for performance
1583 CURSOR c_chr_id (cp_khr_id VARCHAR2) IS
1584 SELECT okc.contract_number
1585 , NVL(okl.date_last_interim_interest_cal, okc.start_date) start_date
1586 , okl.deal_type deal_type
1587 , ppm.quality_val interest_calculation_basis
1588 , okc.authoring_org_id
1589 , round(months_between(okc.end_date, okl.date_last_interim_interest_cal)) remaining_term_in_months
1590 FROM okc_k_headers_b okc
1591 , okl_k_headers okl
1592 , okl_prod_qlty_val_uv ppm
1593 WHERE okc.id = cp_khr_id
1594 AND okl.id = okc.id
1595 AND okl.pdt_id = ppm.pdt_id
1596 AND ppm.quality_name = 'INTEREST_CALCULATION_BASIS';
1597
1598 CURSOR c_periods_cur(p_contract_id NUMBER, p_period_date DATE) IS
1599 SELECT (KHR.TERM_DURATION - round(months_between(nvl(p_period_date,K.START_DATE), K.START_DATE))) MTH,
1600 KHR.TERM_DURATION term_duration
1601 FROM OKC_K_HEADERS_B K, OKL_K_HEADERS KHR
1602 WHERE K.id = p_contract_id
1603 and K.id = KHR.id;
1604
1605 -- 10367844: Modified Cursor to fetch Frequency of
1606 -- Payment Levels of Rent/Principal Payment/Loan Payment of Assets only
1607 CURSOR c_freq_cur(p_contract_id NUMBER) IS
1608 SELECT rul2.object1_id1
1609 FROM okc_rule_groups_b rgp,
1610 okc_rules_b rul1,
1611 okc_rules_b rul2,
1612 okl_strm_type_b sty
1613 WHERE rul2.dnz_chr_id = p_contract_id
1614 AND rul2.rule_information_category = 'LASLL'
1615 AND rul2.rgp_id = rgp.id
1616 AND TO_NUMBER(rul2.object2_id1) = rul1.id
1617 --AND rgp.cle_id = p_cle_id
1618 AND sty.id = rul1.object1_id1
1619 AND sty.stream_type_purpose IN ( 'RENT', 'LOAN_PAYMENT', 'PRINCIPAL_PAYMENT' )
1620 ORDER BY decode(rul2.object1_id1,'M', 1, 'Q', 3, 'S', 6, 'A', 12 );
1621
1622 -- Added by prasjain bug# 6142095
1623 CURSOR var_int_params_csr (p_contract_id NUMBER) IS
1624 SELECT interest_rate,interest_calc_end_date
1625 FROM okl_var_int_params
1626 WHERE khr_id = p_contract_id
1627 AND INTEREST_CALC_END_DATE = (SELECT max(INTEREST_CALC_END_DATE) FROM okl_var_int_params WHERE khr_id = p_contract_id);
1628
1629 CURSOR c_last_int_cur (p_contract_id NUMBER) IS
1630 SELECT NVL(date_last_interim_interest_cal, start_date) reamort_date
1631 FROM okl_k_headers_full_v
1632 WHERE id = p_contract_id;
1633 -- End by prasjain bug# 6142095
1634 --start | 19-May-08 cklee fixed Bug 7043360 |
1635 l_line_id_buf okl_k_lines.id%type := -1;
1636 --end | 19-May-08 cklee fixed Bug 7043360 |
1637
1638 CURSOR c_get_child_esg_trx_number( p_contract_id IN NUMBER
1639 ,p_trx_number IN NUMBER )
1640 IS
1641 SELECT max(transaction_number) max_transaction_number
1642 FROM okl_stream_interfaces
1643 WHERE khr_id = p_contract_id
1644 AND to_number(transaction_number) > p_trx_number
1645 AND orp_code = 'AUTH';
1646
1647 --Added TERMINATED status by bkatraga for bug 14811647
1648 --Bug# 14559654
1649 CURSOR c_get_asset_lines(p_contract_id IN NUMBER) IS
1650 SELECT id
1651 FROM okc_k_lines_b
1652 WHERE chr_id = p_contract_id
1653 AND dnz_chr_id = p_contract_id
1654 AND lse_id = 33
1655 AND sts_code NOT IN('ABANDONED', 'TERMINATED');
1656
1657 BEGIN
1658
1659 x_return_status := OKL_API.G_RET_STS_SUCCESS;
1660
1661 OKL_VARIABLE_INTEREST_PVT.print_debug('****Entering procedure INITIATE_REQUEST****');
1662 FND_FILE.PUT_LINE (FND_FILE.LOG,'Initiating Super Trump Request');
1663
1664 OPEN c_chr_id(p_khr_id);
1665 FETCH c_chr_id INTO l_contract_number, l_start_date, OKL_VARIABLE_INTEREST_PVT.G_DEAL_TYPE, OKL_VARIABLE_INTEREST_PVT.G_INTEREST_CALCULATION_BASIS, OKL_VARIABLE_INTEREST_PVT.G_AUTHORING_ORG_ID, l_remaining_term_in_months;
1666 CLOSE c_chr_id;
1667
1668 --Bug# 8756653
1669 -- Check if contract has been upgraded for effective dated rebook
1670 -- for all mass rebooks other than partial termination
1671 OKL_LLA_UTIL_PVT.check_rebook_upgrade
1672 (p_api_version => p_api_version,
1673 p_init_msg_list => p_init_msg_list,
1674 x_return_status => x_return_status,
1675 x_msg_count => x_msg_count,
1676 x_msg_data => x_msg_data,
1677 p_chr_id => p_khr_id);
1678
1679 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
1680 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1681 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
1682 RAISE initiate_request_failed;
1683 END IF;
1684
1685 --Bug# 13146882
1686 OKL_VARIABLE_INT_UTIL_PVT.check_accounting_setups(
1687 p_chr_id => p_khr_id,
1688 p_transaction_type => 'Rebook',
1689 p_transaction_date => l_start_date,
1690 x_return_status => x_return_status);
1691
1692 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
1693 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1694 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
1695 RAISE initiate_request_failed;
1696 END IF;
1697
1698 OPEN c_periods_cur(p_khr_id, l_start_date);
1699 FETCH c_periods_cur INTO l_no_of_periods, l_term_duration;
1700 CLOSE c_periods_cur;
1701
1702 OPEN c_freq_cur(p_khr_id);
1703 FETCH c_freq_cur INTO l_frequency;
1704 CLOSE c_freq_cur;
1705
1706 OKL_VARIABLE_INTEREST_PVT.print_debug('From Date :'||l_start_date||' Interest Start Date: '||l_start_date||
1707 ' Periods: '||l_no_of_periods);
1708 if(l_frequency = 'M') THEN
1709 l_no_of_periods := l_no_of_periods;
1710 l_adder_months := 1;
1711 elsif(l_frequency = 'Q') THEN
1712 l_no_of_periods := ROUND(l_no_of_periods/3);
1713 l_adder_months := 3;
1714 elsif(l_frequency = 'S') THEN
1715 l_no_of_periods := ROUND(l_no_of_periods/6);
1716 l_adder_months := 6;
1717 elsif(l_frequency = 'A') THEN
1718 l_no_of_periods := ROUND(l_no_of_periods/12);
1719 l_adder_months := 12;
1720 end if;
1721
1722 OKL_VARIABLE_INTEREST_PVT.interest_date_range (
1723 p_api_version => p_api_version,
1724 p_init_msg_list => p_init_msg_list,
1725 x_return_status => x_return_status,
1726 x_msg_count => x_msg_count,
1727 x_msg_data => x_msg_data,
1728 p_contract_id => p_khr_id,
1729 p_start_date => l_start_date,
1730 p_end_date => l_start_date,
1731 p_process_flag => OKL_VARIABLE_INTEREST_PVT.G_INTEREST_CALCULATION_BASIS, /* value is set in Calculate_total_interest_due */
1732 x_interest_rate_tbl => l_interest_rate_tbl);
1733
1734 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
1735 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1736 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
1737 RAISE initiate_request_failed;
1738 END IF;
1739
1740 l_interest_rate_tbl_count := l_interest_rate_tbl.COUNT;
1741
1742 OKL_VARIABLE_INTEREST_PVT.print_debug('No. of records in Interest Date Range TAble : '|| l_interest_rate_tbl_count);
1743
1744 IF (l_interest_rate_tbl_count = 0) THEN
1745 OKL_VARIABLE_INTEREST_PVT.print_error_message('Interest rate unavailable.');
1746 RAISE initiate_request_failed;
1747 END IF;
1748 l_interest_rate_tbl_index := l_interest_rate_tbl.FIRST;
1749
1750 -- Added by prasjain bug# 6142095
1751 OPEN var_int_params_csr(p_khr_id);
1752 FETCH var_int_params_csr INTO l_interest_rate,l_interest_calc_end_date;
1753 CLOSE var_int_params_csr;
1754
1755 OKL_VARIABLE_INTEREST_PVT.print_debug('Last interest rate calculated as : '|| l_interest_rate);
1756 OKL_VARIABLE_INTEREST_PVT.print_debug('Interest rate in Index : '|| l_interest_rate_tbl(l_interest_rate_tbl_index).rate);
1757
1758 --start | 30-Apr-08 cklee fixed Bug 6994233 |
1759 OPEN c_last_int_cur (p_khr_id);
1760 FETCH c_last_int_cur INTO l_reamort_date;
1761 CLOSE c_last_int_cur;
1762 --end | 30-Apr-08 cklee fixed Bug 6994233 |
1763
1764 If l_interest_rate_tbl(l_interest_rate_tbl_index).rate = l_interest_rate then
1765 -- if l_interest_calc_end_date is equal to l_reamort_date
1766 -- means that ESG got failed last time and we need to rebook the contract
1767 If TRUNC(l_interest_calc_end_date) <> TRUNC(l_reamort_date) then
1768 OKL_VARIABLE_INTEREST_PVT.print_debug('Both interest rates are same hence rebooking is not required');
1769 l_rebook_flag := FALSE;
1770 End if;
1771 End if;
1772 -- End by prasjain bug# 6142095
1773
1774 If l_rebook_flag Then -- Added by prasjian bug# 6142095
1775 If (OKL_VARIABLE_INTEREST_PVT.G_DEAL_TYPE = 'LOAN') THEN
1776 OKL_LA_STREAM_PUB.extract_params_loan_reamort(p_api_version => p_api_version,
1777 p_init_msg_list => p_init_msg_list,
1778 p_chr_id => p_khr_id,
1779 x_return_status => x_return_status,
1780 x_msg_count => x_msg_count,
1781 x_msg_data => x_msg_data,
1782 x_csm_loan_header => l_csm_loan_header,
1783 x_csm_loan_lines_tbl => l_csm_loan_lines_tbl,
1784 x_csm_loan_levels_tbl => l_csm_loan_levels_tbl,
1785 x_csm_one_off_fee_tbl => l_csm_one_off_fee_tbl,
1786 x_csm_periodic_expenses_tbl => l_csm_periodic_expenses_tbl,
1787 x_csm_yields_tbl => l_csm_yields_tbl,
1788 x_csm_stream_types_tbl => l_csm_stream_types_tbl);
1789 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1790 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1791 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1792 RAISE initiate_request_failed;
1793 END IF;
1794
1795 --Added by bkatraga for bug 14811647
1796 l_line_count := l_csm_loan_lines_tbl.COUNT;
1797 j := 0;
1798 FOR i IN 1..l_line_count
1799 LOOP
1800 l_asset_term_flag := 'N';
1801 IF(l_csm_loan_lines_tbl.EXISTS(i)) THEN
1802 OPEN check_asset_term_csr(l_csm_loan_lines_tbl(i).kle_loan_id);
1803 FETCH check_asset_term_csr INTO l_asset_term_flag;
1804 CLOSE check_asset_term_csr;
1805 IF(l_asset_term_flag <> 'Y') THEN
1806 j := j + 1;
1807 l_tmp_loan_lines_tbl(j) := l_csm_loan_lines_tbl(i);
1808 END IF;
1809 END IF;
1810 END LOOP;
1811 l_csm_loan_lines_tbl := l_tmp_loan_lines_tbl;
1812
1813 l_line_count := l_csm_loan_levels_tbl.COUNT;
1814 j := 0;
1815 FOR i IN 1..l_line_count
1816 LOOP
1817 l_asset_term_flag := 'N';
1818 IF(l_csm_loan_levels_tbl.EXISTS(i)) THEN
1819 OPEN check_asset_term_csr(l_csm_loan_levels_tbl(i).kle_loan_id);
1820 FETCH check_asset_term_csr INTO l_asset_term_flag;
1821 CLOSE check_asset_term_csr;
1822
1823 IF(l_asset_term_flag <> 'Y') THEN
1824 j := j + 1;
1825 l_tmp_loan_levels_tbl(j) := l_csm_loan_levels_tbl(i);
1826 END IF;
1827 END IF;
1828 END LOOP;
1829 l_csm_loan_levels_tbl := l_tmp_loan_levels_tbl;
1830 --end bkatraga for bug 14811647
1831
1832 OKL_VARIABLE_INTEREST_PVT.print_debug('Contents of l_csm_loan_levels_tbl after call to OKL_LA_STREAM_PUB.extract_params_loan.');
1833 print_loan_tables(p_rent_tbl => l_rents_prin_tbl,
1834 p_csm_loan_level_tbl => l_csm_loan_levels_tbl);
1835
1836 l_csm_loan_header.orp_code := Okl_Create_Streams_Pub.G_ORP_CODE_VARIABLE_INTEREST;
1837 l_csm_stream_types_tbl.DELETE;
1838 l_total_lending := 0;
1839 l_rents_prin_tbl.delete;
1840 l_sequence := 0;
1841
1842 l_loan_levels_cntr := l_csm_loan_levels_tbl.first;
1843 LOOP
1844 EXIT WHEN l_loan_levels_cntr IS NULL;
1845
1846 --CHECK FOR level_type = Okl_Create_Streams_Pub.G_SFE_LEVEL_PRINCIPAL
1847 --DO NOT MAKE A PRICING CALL IN THIS CASE
1848 --for Principal Payments only
1849 IF(l_csm_loan_levels_tbl(l_loan_levels_cntr).level_type = Okl_Create_Streams_Pub.G_SFE_LEVEL_PRINCIPAL)
1850 AND (l_csm_loan_levels_tbl(l_loan_levels_cntr).lock_level_step = 'false' OR l_csm_loan_levels_tbl(l_loan_levels_cntr).lock_level_step = OKC_API.G_MISS_CHAR) THEN
1851 --call var_int_rent_level passing the level payments and interest rate
1852
1853 IF (l_csm_loan_levels_tbl(l_loan_levels_cntr).period = 'T') THEN
1854 l_loan_levels_date_start := get_pay_level_start_date(l_csm_loan_levels_tbl(l_loan_levels_cntr).kle_loan_id,
1855 l_csm_loan_levels_tbl(l_loan_levels_cntr).level_index_number - 1);
1856 OKL_VARIABLE_INTEREST_PVT.print_debug('Fetched start date from SLL - l_loan_levels_date_start: ' || l_loan_levels_date_start);
1857 ELSE
1858 l_loan_levels_date_start := l_csm_loan_levels_tbl(l_loan_levels_cntr).date_start;
1859 END IF;
1860 l_level_date_start := NULL;
1861 l_number_of_periods := 0;
1862 FOR l_period_cntr IN 1..l_csm_loan_levels_tbl(l_loan_levels_cntr).number_of_periods LOOP
1863 IF (l_loan_levels_date_start >= l_start_date) THEN
1864 l_number_of_periods := l_number_of_periods + 1;
1865
1866 IF (l_level_date_start IS NULL) THEN
1867 IF (l_csm_loan_levels_tbl(l_loan_levels_cntr).period = 'T') THEN
1868 --l_level_date_start := l_csm_loan_levels_tbl(l_loan_levels_cntr).date_start;
1869 --when PRINCIPAL_PAYMENT is defined on a contract
1870 --the payment levels are sent from initiate_request to var_int_rent_levels
1871 --without making a pricing call
1872 l_level_date_start := l_loan_levels_date_start;
1873 ELSE
1874 l_level_date_start := l_loan_levels_date_start;
1875 END IF;
1876 END IF;
1877 END IF;
1878
1879 --Added by bkatraga for bug 15946001
1880 l_loan_levels_date_start := OKL_VARIABLE_INTEREST_PVT.get_next_period_start_date(p_khr_id, l_loan_levels_date_start);
1881 IF(l_loan_levels_date_start IS NULL) THEN
1882 RAISE initiate_request_failed;
1883 END IF;
1884 --end bkatraga
1885 END LOOP;
1886
1887 IF (l_number_of_periods > 0) THEN
1888 --WHEN I MAKE A CALL TO var_int_rent_level, PASSING l_rents_prin_tbl AS AN INPUT PARAMETER,
1889 --WILL THIS INFORMATION BE SUFFICIENT TO MAKE A REBOOK CALL
1890 --HOW DO I IDENTIFY THE LINE ID FROM TEH BELOW DATA?
1891 --CHECK WITH DEBDIP or ASHISH
1892 IF (NVl(l_prev_kle_id, -99) <> l_csm_loan_levels_tbl(l_loan_levels_cntr).kle_loan_id) THEN
1893 --reset the sequence for the level_index_number for each asset
1894 l_sequence := 1;
1895 l_prev_kle_id := l_csm_loan_levels_tbl(l_loan_levels_cntr).kle_loan_id;
1896 ELSE
1897 l_sequence := l_sequence + 1;
1898 END IF;
1899 l_rents_prin_tbl(l_loan_levels_cntr).level_index_number := l_sequence;
1900 l_rents_prin_tbl(l_loan_levels_cntr).number_of_periods := l_number_of_periods;
1901 l_rents_prin_tbl(l_loan_levels_cntr).level_type := l_csm_loan_levels_tbl(l_loan_levels_cntr).level_type;
1902 l_rents_prin_tbl(l_loan_levels_cntr).amount := l_csm_loan_levels_tbl(l_loan_levels_cntr).amount;
1903 l_rents_prin_tbl(l_loan_levels_cntr).advance_or_arrears := l_csm_loan_levels_tbl(l_loan_levels_cntr).advance_or_arrears;
1904 l_rents_prin_tbl(l_loan_levels_cntr).period := l_csm_loan_levels_tbl(l_loan_levels_cntr).period;
1905 l_rents_prin_tbl(l_loan_levels_cntr).lock_level_step := Okl_Create_Streams_Pub.G_LOCK_RATE;
1906 l_rents_prin_tbl(l_loan_levels_cntr).first_payment_date := l_level_date_start;
1907 l_rents_prin_tbl(l_loan_levels_cntr).rate := l_interest_rate_tbl(l_interest_rate_tbl_index).rate;
1908 END IF;
1909 --CHECK FOR RENT PAYMENT
1910 --for Rent and Unscheduled Principal Paydown
1911 ELSIF(l_csm_loan_levels_tbl(l_loan_levels_cntr).level_type = Okl_Create_Streams_Pub.G_SFE_LEVEL_PAYMENT)
1912 OR (l_csm_loan_levels_tbl(l_loan_levels_cntr).level_type = Okl_Create_Streams_Pub.G_SFE_LEVEL_PRINCIPAL AND
1913 l_csm_loan_levels_tbl(l_loan_levels_cntr).lock_level_step = Okl_Create_Streams_Pub.G_LOCK_AMOUNT) THEN
1914 IF (l_csm_loan_levels_tbl(l_loan_levels_cntr).period = 'T') THEN
1915 l_loan_levels_date_start := get_pay_level_start_date(l_csm_loan_levels_tbl(l_loan_levels_cntr).kle_loan_id,
1916 l_csm_loan_levels_tbl(l_loan_levels_cntr).level_index_number - 1);
1917 OKL_VARIABLE_INTEREST_PVT.print_debug('Fetched start date from SLL - l_loan_levels_date_start: ' || l_loan_levels_date_start);
1918 ELSE
1919 l_loan_levels_date_start := l_csm_loan_levels_tbl(l_loan_levels_cntr).date_start;
1920 END IF;
1921
1922 l_level_date_start := NULL;
1923 l_number_of_periods := 0;
1924 FOR l_period_cntr IN 1..l_csm_loan_levels_tbl(l_loan_levels_cntr).number_of_periods LOOP
1925 IF (l_loan_levels_date_start >= l_start_date) THEN
1926 l_number_of_periods := l_number_of_periods + 1;
1927
1928 IF (l_level_date_start IS NULL) THEN
1929 --l_level_date_start := l_loan_levels_date_start;
1930 IF (l_csm_loan_levels_tbl(l_loan_levels_cntr).period = 'T') THEN
1931 l_level_date_start := l_csm_loan_levels_tbl(l_loan_levels_cntr).date_start;
1932 ELSE
1933 l_level_date_start := l_loan_levels_date_start;
1934 END IF;
1935 END IF;
1936 END IF;
1937
1938 --Added by bkatraga for bug 15946001
1939 l_loan_levels_date_start := OKL_VARIABLE_INTEREST_PVT.get_next_period_start_date(p_khr_id, l_loan_levels_date_start);
1940 IF(l_loan_levels_date_start IS NULL) THEN
1941 RAISE initiate_request_failed;
1942 END IF;
1943 --end bkatraga
1944 END LOOP;
1945
1946 IF (l_number_of_periods > 0) THEN
1947 IF (NVl(l_prev_kle_id, -99) <> l_csm_loan_levels_tbl(l_loan_levels_cntr).kle_loan_id) THEN
1948 --reset the sequence for the level_index_number for each asset
1949 l_sequence := 1;
1950 l_prev_kle_id := l_csm_loan_levels_tbl(l_loan_levels_cntr).kle_loan_id;
1951 ELSE
1952 l_sequence := l_sequence + 1;
1953 END IF;
1954
1955 l_csm_loan_levels_tbl_in(l_loan_levels_cntr) := l_csm_loan_levels_tbl(l_loan_levels_cntr);
1956 l_csm_loan_levels_tbl_in(l_loan_levels_cntr).level_index_number := l_sequence;
1957 l_csm_loan_levels_tbl_in(l_loan_levels_cntr).query_level_yn := Okl_Create_Streams_Pub.G_FND_YES;
1958 l_csm_loan_levels_tbl_in(l_loan_levels_cntr).rate := l_interest_rate_tbl(l_interest_rate_tbl_index).rate;
1959 IF (l_csm_loan_levels_tbl(l_loan_levels_cntr).level_type = Okl_Create_Streams_Pub.G_SFE_LEVEL_PAYMENT) THEN
1960 l_csm_loan_levels_tbl_in(l_loan_levels_cntr).lock_level_step := Okl_Create_Streams_Pub.G_LOCK_RATE;
1961 ELSIF (l_csm_loan_levels_tbl(l_loan_levels_cntr).level_type = Okl_Create_Streams_Pub.G_SFE_LEVEL_PRINCIPAL) THEN
1962 l_csm_loan_levels_tbl_in(l_loan_levels_cntr).lock_level_step := Okl_Create_Streams_Pub.G_LOCK_BOTH;
1963 END IF;
1964 l_csm_loan_levels_tbl_in(l_loan_levels_cntr).date_start := l_level_date_start;
1965 l_csm_loan_levels_tbl_in(l_loan_levels_cntr).number_of_periods := l_number_of_periods;
1966 END IF;
1967 ELSIF(l_csm_loan_levels_tbl(l_loan_levels_cntr).level_type = Okl_Create_Streams_Pub.G_SFE_LEVEL_FUNDING) THEN
1968 --is the below code OK
1969 --will this table, l_csm_loan_levels_tbl, have more than 1 row
1970 --looks like each row is being populated with the principal at the contract level and
1971 --not at the asset level
1972 --since I am changing the above code for PAYMENTS, does this code need to channge also?
1973 --THIS TABLE MUST BE POPULATED WITH ASSET LEVEL PRINCIPAL BALANCES
1974 --THERE WILL BE 1 ROW FOR EACH ASSET
1975 --ASSET IDENTIFIER kle_loan_id
1976 IF (NVl(l_prev_kle_id, -99) <> l_csm_loan_levels_tbl(l_loan_levels_cntr).kle_loan_id) THEN
1977 --reset the sequence for the level_index_number for each asset
1978 l_sequence := 1;
1979 l_prev_kle_id := l_csm_loan_levels_tbl(l_loan_levels_cntr).kle_loan_id;
1980 ELSE
1981 l_sequence := l_sequence + 1;
1982 END IF;
1983
1984 l_csm_loan_levels_tbl_in(l_loan_levels_cntr) := l_csm_loan_levels_tbl(l_loan_levels_cntr);
1985 l_csm_loan_levels_tbl_in(l_loan_levels_cntr).level_index_number := l_sequence;
1986 l_csm_loan_levels_tbl_in(l_loan_levels_cntr).query_level_yn := Okl_Create_Streams_Pub.G_FND_YES;
1987
1988 --if the level type is not Principal Paydown then
1989 --get the principal balance
1990 IF (NVL(l_csm_loan_levels_tbl_in(l_loan_levels_cntr).period, 'DMF') <> 'T') THEN
1991 l_csm_loan_levels_tbl_in(l_loan_levels_cntr).number_of_periods := l_no_of_periods;
1992 l_csm_loan_levels_tbl_in(l_loan_levels_cntr).date_start := l_start_date;
1993
1994 --start | 19-May-08 cklee fixed Bug 7043360 |
1995 -- note: Based on the API: prin_date_range_var_rate_ctr, we need to pass p_line_id only once to get the
1996 -- the total principal balance. So we limit one p_line_id to pass to the following API.
1997 -- Additionally, we assume the l_csm_loan_levels_tbl_in is group by kle_loan_id (p_line_id), otherwise
1998 -- the follow if statement won't work properly to get the nly one p_line_id.
1999 IF l_line_id_buf <> l_csm_loan_levels_tbl_in(l_loan_levels_cntr).kle_loan_id then
2000 --end | 19-May-08 cklee fixed Bug 7043360 |
2001 OKL_VARIABLE_INTEREST_PVT.prin_date_range_var_rate_ctr (
2002 p_api_version => p_api_version,
2003 p_init_msg_list => p_init_msg_list,
2004 x_return_status => x_return_status,
2005 x_msg_count => x_msg_count,
2006 x_msg_data => x_msg_data,
2007 p_contract_id => p_khr_id,
2008 p_line_id => l_csm_loan_levels_tbl_in(l_loan_levels_cntr).kle_loan_id,
2009 p_start_date => l_start_date,
2010 p_due_date => l_start_date,
2011 p_principal_basis => NULL,
2012 x_principal_balance_tbl => l_principal_balance_tbl);
2013
2014 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR)THEN
2015 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2016 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR)THEN
2017 RAISE initiate_request_failed;
2018 END IF;
2019
2020 l_csm_loan_levels_tbl_in(l_loan_levels_cntr).amount := l_principal_balance_tbl(l_principal_balance_tbl.COUNT).principal_balance;
2021 l_total_lending := l_total_lending + l_csm_loan_levels_tbl_in(l_loan_levels_cntr).amount;
2022
2023 l_line_prin_counter := l_line_prin_counter + 1;
2024 l_line_prin_tbl(l_line_prin_counter).kle_id := l_csm_loan_levels_tbl_in(l_loan_levels_cntr).kle_loan_id;
2025 l_line_prin_tbl(l_line_prin_counter).principal_balance := l_csm_loan_levels_tbl_in(l_loan_levels_cntr).amount;
2026
2027 OKL_VARIABLE_INTEREST_PVT.print_debug('Kle Id : ' || l_csm_loan_levels_tbl_in(l_loan_levels_cntr).kle_loan_id || 'Principal :'||l_csm_loan_levels_tbl_in(l_loan_levels_cntr).amount);
2028 --start | 19-May-08 cklee fixed Bug 7043360 |
2029 END IF;
2030 l_line_id_buf := l_csm_loan_levels_tbl_in(l_loan_levels_cntr).kle_loan_id;
2031 --end | 19-May-08 cklee fixed Bug 7043360 |
2032 END IF;
2033 END IF;
2034
2035 l_loan_levels_cntr := l_csm_loan_levels_tbl.next(l_loan_levels_cntr);
2036 END LOOP;
2037
2038 l_tot_principal_amount := l_total_lending;
2039 l_csm_loan_header.date_start := l_start_date;
2040 l_csm_loan_header.date_payments_commencement := l_start_date;
2041 l_csm_loan_header.total_lending := l_total_lending;
2042
2043 OKL_VARIABLE_INTEREST_PVT.print_debug('==========================================');
2044 OKL_VARIABLE_INTEREST_PVT.print_debug('Before pricing call or var_int_rent_level.');
2045 OKL_VARIABLE_INTEREST_PVT.print_debug('==========================================');
2046 OKL_VARIABLE_INTEREST_PVT.print_debug('Contents of l_csm_loan_header before pricing call or var_int_rent_level.');
2047 OKL_VARIABLE_INTEREST_PVT.print_debug('Contract Principal : '||l_csm_loan_header.total_lending);
2048 l_csm_loan_header.lending_rate := l_interest_rate_tbl(l_interest_rate_tbl_index).rate;
2049 OKL_VARIABLE_INTEREST_PVT.print_debug('Contract Rate : '|| l_csm_loan_header.lending_rate);
2050
2051 OKL_VARIABLE_INTEREST_PVT.print_debug('date_start :'||l_csm_loan_header.date_start);
2052 OKL_VARIABLE_INTEREST_PVT.print_debug('date_payments_commencement :'||l_csm_loan_header.date_payments_commencement);
2053 OKL_VARIABLE_INTEREST_PVT.print_debug('total_lending :'||l_csm_loan_header.total_lending);
2054
2055 OKL_VARIABLE_INTEREST_PVT.print_debug('# of rows in l_rents_prin_tbl :' || l_rents_prin_tbl.count);
2056 OKL_VARIABLE_INTEREST_PVT.print_debug('# of rows in l_csm_loan_levels_tbl_in :' || l_csm_loan_levels_tbl_in.count);
2057 OKL_VARIABLE_INTEREST_PVT.print_debug('# of rows in l_csm_loan_levels_tbl :' || l_csm_loan_levels_tbl.count);
2058
2059 IF(l_csm_loan_header.lending_rate <> 0) THEN
2060 IF (NVL(l_rents_prin_tbl.count, 0) = 0) THEN
2061 --RENT is defined on the contract payments
2062
2063 OKL_VARIABLE_INTEREST_PVT.print_debug('Before pricing call.');
2064 print_loan_tables(p_rent_tbl => l_rents_prin_tbl,
2065 p_csm_loan_level_tbl => l_csm_loan_levels_tbl_in);
2066
2067 Okl_Create_Streams_Pub.Create_Streams_Loan_Restr(p_api_version => p_api_version,
2068 p_init_msg_list => p_init_msg_list,
2069 p_skip_prc_engine => l_skip_prc_engine,
2070 p_csm_loan_header => l_csm_loan_header,
2071 p_csm_loan_lines_tbl => l_csm_loan_lines_tbl,
2072 p_csm_loan_levels_tbl => l_csm_loan_levels_tbl_in,
2073 p_csm_one_off_fee_tbl => l_csm_one_off_fee_tbl,
2074 p_csm_periodic_expenses_tbl => l_csm_periodic_expenses_tbl,
2075 p_csm_yields_tbl => l_csm_yields_tbl,
2076 p_csm_stream_types_tbl => l_csm_stream_types_tbl,
2077 x_trans_id => l_super_trump_request_id,
2078 x_trans_status => l_trans_status,
2079 x_return_status => x_return_status,
2080 x_msg_count => x_msg_count,
2081 x_msg_data => x_msg_data);
2082 OKL_VARIABLE_INTEREST_PVT.print_debug('After pricing call, status :' || l_trans_status);
2083 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR)THEN
2084 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2085 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR)THEN
2086 RAISE initiate_request_failed;
2087 END IF;
2088
2089 OKL_VARIABLE_INTEREST_PVT.print_debug('Super trump request id:' || l_super_trump_request_id);
2090 OKL_VARIABLE_INTEREST_PVT.print_debug('super trump transaction status:'|| l_trans_status);
2091 OKL_VARIABLE_INTEREST_PVT.print_debug('x return :'||x_return_status);
2092
2093 ELSE
2094 --PRINCIPAL PAYMENT is defined on the contract payments
2095
2096 OKL_VARIABLE_INTEREST_PVT.print_debug('Before calling var_int_rent_level.');
2097 print_loan_tables(p_rent_tbl => l_rents_prin_tbl,
2098 p_csm_loan_level_tbl => l_csm_loan_levels_tbl);
2099
2100 var_int_rent_level(
2101 p_api_version => p_api_version,
2102 p_init_msg_list => p_init_msg_list,
2103 x_return_status => x_return_status,
2104 x_msg_count => x_msg_count,
2105 x_msg_data => x_msg_data,
2106 p_chr_id => p_khr_id,
2107 p_trx_id => NULL,
2108 p_trx_status => NULL,
2109 p_rent_tbl => l_rents_prin_tbl,
2110 p_csm_loan_level_tbl => l_csm_loan_levels_tbl,
2111 x_child_trx_id => l_child_trx_id);
2112
2113 OKL_VARIABLE_INTEREST_PVT.print_debug('After calling var_int_rent_level, status :' || x_return_status);
2114 OKL_VARIABLE_INTEREST_PVT.print_debug('Rebook child request id:' || l_child_trx_id);
2115
2116 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR)THEN
2117 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2118 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR)THEN
2119 RAISE initiate_request_failed;
2120 END IF;
2121 END IF;
2122 END IF;
2123 ELSE
2124 --G_DEAL_TYPE = LEASE
2125
2126 --principal_amount := get_tot_principal_amt(p_khr_id, null);
2127 -- CAll Extraction API
2128 OKL_LA_STREAM_PUB.EXTRACT_PARAMS_LEASE(
2129 p_api_version => p_api_version,
2130 p_init_msg_list => p_init_msg_list,
2131 p_chr_id => p_khr_id,
2132 x_return_status => x_return_status,
2133 x_msg_count => x_msg_count,
2134 x_msg_data => x_msg_data,
2135 x_csm_lease_header => l_csm_lease_header,
2136 x_csm_one_off_fee_tbl => l_csm_one_off_fee_tbl,
2137 x_csm_periodic_expenses_tbl => l_csm_periodic_expenses_tbl,
2138 x_csm_yields_tbl => l_csm_yields_tbl,
2139 x_req_stream_types_tbl => l_req_stream_types_tbl,
2140 x_csm_line_details_tbl => l_csm_line_details_tbl,
2141 x_rents_tbl => l_rents_tbl);
2142 FOR i in 1..x_msg_count
2143 LOOP
2144 FND_MSG_PUB.GET(
2145 p_msg_index => i,
2146 p_encoded => FND_API.G_FALSE,
2147 p_data => x_msg_data,
2148 p_msg_index_out => l_msg_index_out
2149 );
2150 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,'Error '||to_char(i)||': '||x_msg_data);
2151 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,'Message Index: '||l_msg_index_out);
2152 OKL_VARIABLE_INTEREST_PVT.print_debug('Error '||to_char(i)||': '||x_msg_data);
2153 OKL_VARIABLE_INTEREST_PVT.print_debug('Message Index: '||l_msg_index_out);
2154 END LOOP;
2155
2156 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2157 OKL_VARIABLE_INTEREST_PVT.print_error_message('Status after Extract params: '||x_return_status);
2158 OKL_VARIABLE_INTEREST_PVT.print_error_message('Message after Extract params: '||x_msg_data);
2159 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2160 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2161 OKL_VARIABLE_INTEREST_PVT.print_error_message('Status after Extract params: '||x_return_status);
2162 RAISE initiate_request_failed;
2163 END IF;
2164
2165 --Added by bkatraga for bug 14811647
2166 l_line_count := l_csm_line_details_tbl.COUNT;
2167 j := 0;
2168 FOR i IN 1..l_line_count
2169 LOOP
2170 l_asset_term_flag := 'N';
2171 IF(l_csm_line_details_tbl.EXISTS(i)) THEN
2172 OPEN check_asset_term_csr(l_csm_line_details_tbl(i).kle_asset_id);
2173 FETCH check_asset_term_csr INTO l_asset_term_flag;
2174 CLOSE check_asset_term_csr;
2175
2176 IF(l_asset_term_flag <> 'Y') THEN
2177 j := j + 1;
2178 l_tmp_line_details_tbl(j) := l_csm_line_details_tbl(i);
2179 END IF;
2180 END IF;
2181 END LOOP;
2182
2183 l_csm_line_details_tbl := l_tmp_line_details_tbl;
2184 --end bkatraga for bug 14811647
2185
2186 --Fine tune the params
2187 --x_csm_lease_header
2188 OKL_VARIABLE_INTEREST_PVT.print_debug('Status after Extract params: '||x_return_status);
2189
2190 OKL_VARIABLE_INTEREST_PVT.print_debug('Contents of l_rents_tbl and l_csm_line_details_tbl after call to Extract params.');
2191 print_lease_tables(p_rents_tbl_in => l_rents_tbl,
2192 p_csm_line_details_tbl => l_csm_line_details_tbl);
2193
2194 l_csm_lease_header.orp_code := OKL_CREATE_STREAMS_PUB.G_ORP_CODE_VARIABLE_INTEREST;
2195 l_csm_lease_header.implicit_interest_rate := l_interest_rate_tbl(l_interest_rate_tbl_index).rate;
2196 l_csm_lease_header.date_payments_commencement := l_start_date;
2197 l_csm_lease_header.date_delivery := l_start_date;
2198 l_csm_lease_header.term := l_remaining_term_in_months;
2199
2200 --Check the following with Susan.
2201 --KEERTHI WILL CHECK THE VALIDITY OF BELOW 2 FIELDS
2202 --FIND OUT FROM PM'S, ALVARO/SUSAN
2203 l_csm_lease_header.adjust := 'Rent';
2204 l_csm_lease_header.adjustment_method := 'Proportional';
2205
2206 OKL_VARIABLE_INTEREST_PVT.print_debug('----------------------------------------------------');
2207 OKL_VARIABLE_INTEREST_PVT.print_debug('l_csm_lease_header information');
2208 OKL_VARIABLE_INTEREST_PVT.print_debug('orp_code :'||l_csm_lease_header.orp_code);
2209 OKL_VARIABLE_INTEREST_PVT.print_debug('implicit_interest_rate :'||l_csm_lease_header.implicit_interest_rate);
2210 OKL_VARIABLE_INTEREST_PVT.print_debug('adjust :'||l_csm_lease_header.adjust);
2211 OKL_VARIABLE_INTEREST_PVT.print_debug('adjustment_method :'||l_csm_lease_header.adjustment_method);
2212 OKL_VARIABLE_INTEREST_PVT.print_debug('date_payments_commencement :'||l_csm_lease_header.date_payments_commencement);
2213 OKL_VARIABLE_INTEREST_PVT.print_debug('date_delivery :'||l_csm_lease_header.date_delivery);
2214 OKL_VARIABLE_INTEREST_PVT.print_debug('term :'||l_csm_lease_header.term);
2215
2216 --we are appending to the existing table below
2217 --is this correct?
2218 --CHECK WITH PM'S
2219 l_index := l_csm_yields_tbl.COUNT + 1;
2220 l_csm_yields_tbl(l_index).siy_type := OKL_SIY_PVT.G_SIY_TYPE_INTEREST_RATE;
2221 l_csm_yields_tbl(l_index).yield_name := 'Full term with residual';
2222 l_csm_yields_tbl(l_index).target_value := l_interest_rate_tbl(l_interest_rate_tbl_index).rate;
2223 OKL_VARIABLE_INTEREST_PVT.print_debug('l_csm_yields_tbl information');
2224 OKL_VARIABLE_INTEREST_PVT.print_debug('siy_type :'||l_csm_yields_tbl(l_index).siy_type);
2225 OKL_VARIABLE_INTEREST_PVT.print_debug('yield_name :'||l_csm_yields_tbl(l_index).yield_name);
2226 OKL_VARIABLE_INTEREST_PVT.print_debug('target_value :'||l_csm_yields_tbl(l_index).target_value);
2227
2228 --Are the values being passed to the l_rents_tbl correct? specifically lock_level_step
2229 l_rents_tbl_in.delete;
2230 l_rent_cntr := l_rents_tbl.first;
2231 LOOP
2232 EXIT WHEN l_rent_cntr IS NULL;
2233 IF (l_rents_tbl(l_rent_cntr).period = 'T') THEN
2234 l_rent_date_start := get_pay_level_start_date(l_rents_tbl(l_rent_cntr).kle_asset_id,
2235 l_rents_tbl(l_rent_cntr).level_index_number);
2236 OKL_VARIABLE_INTEREST_PVT.print_debug('Fetched start date from SLL - l_rent_date_start: ' || l_rent_date_start);
2237 ELSE
2238 l_rent_date_start := l_rents_tbl(l_rent_cntr).date_start;
2239 END IF;
2240
2241 l_number_of_periods := 0;
2242 l_level_date_start := NULL;
2243 FOR l_period_cntr IN 1..l_rents_tbl(l_rent_cntr).number_of_periods LOOP
2244 IF (l_rent_date_start >= l_start_date) THEN
2245 l_number_of_periods := l_number_of_periods + 1;
2246 IF (l_level_date_start IS NULL) THEN
2247 --l_level_date_start := l_rent_date_start;
2248 IF (l_rents_tbl(l_rent_cntr).period = 'T') THEN
2249 l_level_date_start := l_rents_tbl(l_rent_cntr).date_start;
2250 ELSE
2251 l_level_date_start := l_rent_date_start;
2252 END IF;
2253 END IF;
2254 END IF;
2255
2256 --Added by bkatraga for bug 15946001
2257 l_rent_date_start := OKL_VARIABLE_INTEREST_PVT.get_next_period_start_date(p_khr_id, l_rent_date_start);
2258 IF(l_rent_date_start IS NULL) THEN
2259 RAISE initiate_request_failed;
2260 END IF;
2261 --end bkatraga
2262 END LOOP;
2263
2264 IF (l_number_of_periods > 0) THEN
2265 l_rents_tbl_in(l_rent_cntr) := l_rents_tbl(l_rent_cntr);
2266 l_rents_tbl_in(l_rent_cntr).number_of_periods := l_number_of_periods;
2267 -- RGOOTY: 9972860: Fintec Enhancement: For Reamort, we need to pass the RateLocked as False in the XML
2268 --Check the following with Susan
2269 --IN THE TABLE l_rents_tbl, OVERWRITE ONLY number_of_periods
2270 --AND DATE_START FOR THE FIRST PERIOD FROM WHICH REAMORT WILL
2271 --BE EFFECTIVE
2272 l_rents_tbl_in(l_rent_cntr).rate := l_interest_rate_tbl(l_interest_rate_tbl_index).rate;
2273 l_rents_tbl_in(l_rent_cntr).date_start := l_level_date_start;
2274 --BELOW VALUE MUST BE SET TO 'Y'
2275 l_rents_tbl_in(l_rent_cntr).query_level_yn := Okl_Create_Streams_Pub.G_FND_YES;
2276 IF (NVl(l_prev_kle_id, -99) <> l_rents_tbl(l_rent_cntr).kle_asset_id) THEN
2277 --reset the sequence for the level_index_number for each asset
2278 l_sequence := 1;
2279 l_prev_kle_id := l_rents_tbl(l_rent_cntr).kle_asset_id;
2280 ELSE
2281 l_sequence := l_sequence + 1;
2282 END IF;
2283 l_rents_tbl_in(l_rent_cntr).level_index_number := l_sequence;
2284 END IF;
2285
2286 --check to see if contract is in Advance or Arrears
2287 IF (l_advance_or_arrears IS NULL) THEN
2288 IF (l_rents_tbl(l_rent_cntr).advance_or_arrears = 'ARREARS') THEN
2289 l_advance_or_arrears := 'ARREARS';
2290 ELSE
2291 l_advance_or_arrears := 'ADVANCE';
2292 END IF;
2293 END IF;
2294
2295 l_rent_cntr := l_rents_tbl.next(l_rent_cntr);
2296 END LOOP;
2297
2298 --not required
2299 l_req_stream_types_tbl.delete;
2300
2301 OKL_VARIABLE_INTEREST_PVT.print_debug('====================================================');
2302 OKL_VARIABLE_INTEREST_PVT.print_debug('Before Super Trump pricing call');
2303 l_csm_line_details_ctr := l_csm_line_details_tbl.first;
2304 LOOP
2305 EXIT WHEN l_csm_line_details_ctr IS NULL;
2306 l_csm_line_details_tbl(l_csm_line_details_ctr).asset_cost := get_tot_principal_amt(p_khr_id, l_csm_line_details_tbl(l_csm_line_details_ctr).kle_asset_id,l_start_date, l_advance_or_arrears);
2307 l_tot_principal_amount := l_tot_principal_amount + l_csm_line_details_tbl(l_csm_line_details_ctr).asset_cost;
2308
2309 l_line_prin_counter := l_line_prin_counter + 1;
2310 l_line_prin_tbl(l_line_prin_counter).kle_id := l_csm_line_details_tbl(l_csm_line_details_ctr).kle_asset_id;
2311 l_line_prin_tbl(l_line_prin_counter).principal_balance := l_csm_line_details_tbl(l_csm_line_details_ctr).asset_cost;
2312
2313 l_csm_line_details_tbl(l_csm_line_details_ctr).date_delivery := l_start_date;
2314 l_csm_line_details_tbl(l_csm_line_details_ctr).date_funding := l_start_date;
2315
2316 OKL_VARIABLE_INTEREST_PVT.print_debug('Asset id: ' || l_csm_line_details_tbl(l_csm_line_details_ctr).kle_asset_id || ' Asset principal balance: ' ||l_csm_line_details_tbl(l_csm_line_details_ctr).asset_cost);
2317 OKL_VARIABLE_INTEREST_PVT.print_debug('Residual amount : ' || l_csm_line_details_tbl(l_csm_line_details_ctr).residual_amount);
2318 OKL_VARIABLE_INTEREST_PVT.print_debug('Date delivery : ' || l_csm_line_details_tbl(l_csm_line_details_ctr).date_delivery);
2319 OKL_VARIABLE_INTEREST_PVT.print_debug('Date funding : ' || l_csm_line_details_tbl(l_csm_line_details_ctr).date_funding);
2320
2321 l_csm_line_details_ctr := l_csm_line_details_tbl.next(l_csm_line_details_ctr);
2322 END LOOP;
2323
2324 print_lease_tables(p_rents_tbl_in => l_rents_tbl_in,
2325 p_csm_line_details_tbl => l_csm_line_details_tbl);
2326
2327 --Call Supertrump API to submit request.
2328 Okl_Create_Streams_Pub.CREATE_STREAMS_LEASE_RESTR(
2329 p_api_version => p_api_version,
2330 p_init_msg_list => p_init_msg_list,
2331 x_return_status => x_return_status,
2332 x_msg_count => x_msg_count,
2333 x_msg_data => x_msg_data,
2334 p_csm_lease_header => l_csm_lease_header,
2335 p_csm_one_off_fee_tbl => l_csm_one_off_fee_tbl,
2336 p_csm_periodic_expenses_tbl => l_csm_periodic_expenses_tbl,
2337 p_csm_yields_tbl => l_csm_yields_tbl,
2338 p_csm_stream_types_tbl => l_csm_stream_types_tbl,
2339 p_csm_line_details_tbl => l_csm_line_details_tbl,
2340 p_rents_tbl => l_rents_tbl_in,
2341 x_trans_id => l_super_trump_request_id,
2342 x_trans_status => l_trans_status);
2343
2344 OKL_VARIABLE_INTEREST_PVT.print_debug('Super trump request id:' || l_super_trump_request_id);
2345 OKL_VARIABLE_INTEREST_PVT.print_debug('super trump transaction status:'|| l_trans_status);
2346 OKL_VARIABLE_INTEREST_PVT.print_debug('x return :'||x_return_status);
2347
2348 IF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
2349 OKL_VARIABLE_INTEREST_PVT.print_error_message('Printing message stack.');
2350 FOR i in 1..x_msg_count
2351 LOOP
2352 FND_MSG_PUB.GET(
2353 p_msg_index => i,
2354 p_encoded => FND_API.G_FALSE,
2355 p_data => x_msg_data,
2356 p_msg_index_out => l_msg_index_out
2357 );
2358 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,'Error '||to_char(i)||': '||x_msg_data);
2359 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,'Message Index: '||l_msg_index_out);
2360 OKL_VARIABLE_INTEREST_PVT.print_error_message('Error '||to_char(i)||': '||x_msg_data);
2361 OKL_VARIABLE_INTEREST_PVT.print_error_message('Message Index: '||l_msg_index_out);
2362 END LOOP;
2363 END IF;
2364 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2365 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2366 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2367 RAISE initiate_request_failed;
2368 END IF;
2369 END IF;
2370 End IF; -- Added by prasjain bug# 6142095
2371
2372 IF (l_super_trump_request_id IS NOT NULL OR l_child_trx_id IS NOT NULL) AND
2373 l_rebook_flag = TRUE THEN -- l_rebook_flag = TRUE added by prasjain bug# 6142095
2374 IF (l_super_trump_request_id IS NOT NULL) THEN
2375 l_vipv_rec.parent_trx_id := l_super_trump_request_id;
2376 ELSIF (l_child_trx_id IS NOT NULL) THEN
2377 l_vipv_rec.parent_trx_id := l_child_trx_id;
2378 ELSE
2379 l_vipv_rec.parent_trx_id := null;
2380 END IF;
2381
2382 l_vipv_rec.contract_number := l_contract_number;
2383
2384 -- Bug 10324172 : If ESG is behaving synchronous, derieve the Transaction Number
2385 -- and stamp it in the child_trx_id.
2386 OKL_VARIABLE_INTEREST_PVT.print_debug('l_super_trump_request_id= '|| l_super_trump_request_id );
2387 IF l_super_trump_request_id IS NOT NULL
2388 THEN
2389 OKL_VARIABLE_INTEREST_PVT.print_debug('p_khr_id= '|| p_khr_id || ' l_super_trump_request_id=' || l_super_trump_request_id );
2390 FOR t_rec IN c_get_child_esg_trx_number(p_contract_id => p_khr_id
2391 ,p_trx_number => l_super_trump_request_id)
2392 LOOP
2393 l_vipv_rec.child_trx_id := t_rec.max_transaction_number;
2394 OKL_VARIABLE_INTEREST_PVT.print_debug('l_vipv_rec.child_trx_id= '|| l_vipv_rec.child_trx_id );
2395 END LOOP;
2396 END IF;
2397 -- Bug 10324172 : End
2398 OKL_VARIABLE_INTEREST_PVT.print_debug('Updating OKL_VAR_INT_PROCESS_V');
2399 OKL_VIP_PVT.insert_row(
2400 p_api_version => p_api_version,
2401 p_init_msg_list => p_init_msg_list,
2402 x_return_status => x_return_status,
2403 x_msg_count => x_msg_count,
2404 x_msg_data => x_msg_data,
2405 p_vipv_rec => l_vipv_rec,
2406 x_vipv_rec => x_vipv_rec);
2407
2408 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2409 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2410 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2411 RAISE initiate_request_failed;
2412 END IF;
2413 OKL_VARIABLE_INTEREST_PVT.print_debug('Updating OKL_VAR_INT_PROCESS_V Successful');
2414
2415 END IF; -- Added by prasjain bug# 6142095
2416
2417 --Added by bkatraga for bug 13693187
2418 l_next_reamort_date := OKL_VARIABLE_INTEREST_PVT.get_next_period_start_date(p_khr_id, l_reamort_date);
2419 IF(l_next_reamort_date IS NULL) THEN
2420 RAISE initiate_request_failed;
2421 END IF;
2422
2423 OKL_VR_DATA_HANDLER_PVT.UPDATE_LAST_INT_CAL_DATE(
2424 p_api_version => p_api_version,
2425 p_init_msg_list => p_init_msg_list,
2426 x_return_status => x_return_status,
2427 x_msg_count => x_msg_count,
2428 x_msg_data => x_msg_data,
2429 p_contract_id => p_khr_id,
2430 p_last_interest_cal_date => l_next_reamort_date);
2431 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2432 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2433 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2434 RAISE initiate_request_failed;
2435 END IF;
2436 --end bkatraga
2437
2438 -- Added by prasjain bug# 6142095
2439 IF (l_super_trump_request_id IS NOT NULL OR l_child_trx_id IS NOT NULL OR l_rebook_flag = FALSE) THEN
2440 -- End by prasjain bug# 6142095
2441
2442 IF (l_interest_rate_tbl(l_interest_rate_tbl.first).derived_flag = 'Y')THEN
2443 OKL_VARIABLE_INTEREST_PVT.print_debug('Updating OKL_VAR_INT_PARAMS');
2444
2445 IF (NVL(OKL_VARIABLE_INTEREST_PVT.g_vir_tbl_counter, 0) = 0) THEN
2446 OKL_VARIABLE_INTEREST_PVT.g_vir_tbl_counter := NVL(OKL_VARIABLE_INTEREST_PVT.g_vir_tbl_counter, 0) + 1;
2447 END IF;
2448
2449 SELECT
2450 DECODE(Fnd_Global.CONC_REQUEST_ID,-1,NULL,Fnd_Global.CONC_REQUEST_ID),
2451 DECODE(Fnd_Global.PROG_APPL_ID,-1,NULL,Fnd_Global.CONC_REQUEST_ID),
2452 DECODE(Fnd_Global.CONC_PROGRAM_ID,-1,NULL,Fnd_Global.CONC_PROGRAM_ID),
2453 DECODE(Fnd_Global.CONC_REQUEST_ID,-1,NULL,SYSDATE)
2454 INTO
2455 l_request_id,
2456 l_program_application_id,
2457 l_program_id,
2458 l_program_update_date
2459 FROM dual;
2460
2461 OKL_VARIABLE_INTEREST_PVT.g_vir_tbl.delete;
2462
2463 --Bug# 14559654
2464 IF l_line_prin_tbl.COUNT = 0 THEN
2465
2466 FOR r_get_asset_lines IN c_get_asset_lines(p_contract_id => p_khr_id)
2467 LOOP
2468 OKL_VARIABLE_INTEREST_PVT.g_vir_tbl_counter := OKL_VARIABLE_INTEREST_PVT.g_vir_tbl_counter + 1;
2469 OKL_VARIABLE_INTEREST_PVT.g_vir_tbl(OKL_VARIABLE_INTEREST_PVT.g_vir_tbl_counter).id := okc_p_util.raw_to_number(sys_guid());
2470 OKL_VARIABLE_INTEREST_PVT.g_vir_tbl(OKL_VARIABLE_INTEREST_PVT.g_vir_tbl_counter).khr_id := p_khr_id;
2471 OKL_VARIABLE_INTEREST_PVT.g_vir_tbl(OKL_VARIABLE_INTEREST_PVT.g_vir_tbl_counter).source_table := 'OKL_VAR_INT_PROCESS_B';
2472 OKL_VARIABLE_INTEREST_PVT.g_vir_tbl(OKL_VARIABLE_INTEREST_PVT.g_vir_tbl_counter).source_id := x_vipv_rec.id;
2473 OKL_VARIABLE_INTEREST_PVT.g_vir_tbl(OKL_VARIABLE_INTEREST_PVT.g_vir_tbl_counter).interest_rate := l_interest_rate_tbl(l_interest_rate_tbl.first).rate;
2474 OKL_VARIABLE_INTEREST_PVT.g_vir_tbl(OKL_VARIABLE_INTEREST_PVT.g_vir_tbl_counter).interest_calc_start_date := l_start_date;
2475 OKL_VARIABLE_INTEREST_PVT.g_vir_tbl(OKL_VARIABLE_INTEREST_PVT.g_vir_tbl_counter).interest_calc_end_date := l_start_date;
2476 OKL_VARIABLE_INTEREST_PVT.g_vir_tbl(OKL_VARIABLE_INTEREST_PVT.g_vir_tbl_counter).calc_method_code := OKL_VARIABLE_INTEREST_PVT.G_CALC_METHOD_CODE;
2477 OKL_VARIABLE_INTEREST_PVT.g_vir_tbl(OKL_VARIABLE_INTEREST_PVT.g_vir_tbl_counter).principal_balance := 0;
2478 OKL_VARIABLE_INTEREST_PVT.g_vir_tbl(OKL_VARIABLE_INTEREST_PVT.g_vir_tbl_counter).valid_yn := 'Y';
2479 OKL_VARIABLE_INTEREST_PVT.g_vir_tbl(OKL_VARIABLE_INTEREST_PVT.g_vir_tbl_counter).object_version_number := 1.0;
2480 OKL_VARIABLE_INTEREST_PVT.g_vir_tbl(OKL_VARIABLE_INTEREST_PVT.g_vir_tbl_counter).org_id := OKL_VARIABLE_INTEREST_PVT.g_authoring_org_id;
2481 OKL_VARIABLE_INTEREST_PVT.g_vir_tbl(OKL_VARIABLE_INTEREST_PVT.g_vir_tbl_counter).request_id := l_request_id;
2482 OKL_VARIABLE_INTEREST_PVT.g_vir_tbl(OKL_VARIABLE_INTEREST_PVT.g_vir_tbl_counter).program_application_id := l_program_application_id;
2483 OKL_VARIABLE_INTEREST_PVT.g_vir_tbl(OKL_VARIABLE_INTEREST_PVT.g_vir_tbl_counter).program_id := l_program_id;
2484 OKL_VARIABLE_INTEREST_PVT.g_vir_tbl(OKL_VARIABLE_INTEREST_PVT.g_vir_tbl_counter).program_update_date := SYSDATE;
2485 OKL_VARIABLE_INTEREST_PVT.g_vir_tbl(OKL_VARIABLE_INTEREST_PVT.g_vir_tbl_counter).attribute_category := NULL;
2486 OKL_VARIABLE_INTEREST_PVT.g_vir_tbl(OKL_VARIABLE_INTEREST_PVT.g_vir_tbl_counter).attribute1 := NULL;
2487 OKL_VARIABLE_INTEREST_PVT.g_vir_tbl(OKL_VARIABLE_INTEREST_PVT.g_vir_tbl_counter).attribute2 := NULL;
2488 OKL_VARIABLE_INTEREST_PVT.g_vir_tbl(OKL_VARIABLE_INTEREST_PVT.g_vir_tbl_counter).attribute3 := NULL;
2489 OKL_VARIABLE_INTEREST_PVT.g_vir_tbl(OKL_VARIABLE_INTEREST_PVT.g_vir_tbl_counter).attribute4 := NULL;
2490 OKL_VARIABLE_INTEREST_PVT.g_vir_tbl(OKL_VARIABLE_INTEREST_PVT.g_vir_tbl_counter).attribute5 := NULL;
2491 OKL_VARIABLE_INTEREST_PVT.g_vir_tbl(OKL_VARIABLE_INTEREST_PVT.g_vir_tbl_counter).attribute6 := NULL;
2492 OKL_VARIABLE_INTEREST_PVT.g_vir_tbl(OKL_VARIABLE_INTEREST_PVT.g_vir_tbl_counter).attribute7 := NULL;
2493 OKL_VARIABLE_INTEREST_PVT.g_vir_tbl(OKL_VARIABLE_INTEREST_PVT.g_vir_tbl_counter).attribute8 := NULL;
2494 OKL_VARIABLE_INTEREST_PVT.g_vir_tbl(OKL_VARIABLE_INTEREST_PVT.g_vir_tbl_counter).attribute9 := NULL;
2495 OKL_VARIABLE_INTEREST_PVT.g_vir_tbl(OKL_VARIABLE_INTEREST_PVT.g_vir_tbl_counter).attribute10 := NULL;
2496 OKL_VARIABLE_INTEREST_PVT.g_vir_tbl(OKL_VARIABLE_INTEREST_PVT.g_vir_tbl_counter).attribute11 := NULL;
2497 OKL_VARIABLE_INTEREST_PVT.g_vir_tbl(OKL_VARIABLE_INTEREST_PVT.g_vir_tbl_counter).attribute12 := NULL;
2498 OKL_VARIABLE_INTEREST_PVT.g_vir_tbl(OKL_VARIABLE_INTEREST_PVT.g_vir_tbl_counter).attribute13 := NULL;
2499 OKL_VARIABLE_INTEREST_PVT.g_vir_tbl(OKL_VARIABLE_INTEREST_PVT.g_vir_tbl_counter).attribute14 := NULL;
2500 OKL_VARIABLE_INTEREST_PVT.g_vir_tbl(OKL_VARIABLE_INTEREST_PVT.g_vir_tbl_counter).attribute15 := NULL;
2501 OKL_VARIABLE_INTEREST_PVT.g_vir_tbl(OKL_VARIABLE_INTEREST_PVT.g_vir_tbl_counter).created_by := FND_GLOBAL.USER_ID;
2502 OKL_VARIABLE_INTEREST_PVT.g_vir_tbl(OKL_VARIABLE_INTEREST_PVT.g_vir_tbl_counter).creation_date := SYSDATE;
2503 OKL_VARIABLE_INTEREST_PVT.g_vir_tbl(OKL_VARIABLE_INTEREST_PVT.g_vir_tbl_counter).last_updated_by := FND_GLOBAL.USER_ID;
2504 OKL_VARIABLE_INTEREST_PVT.g_vir_tbl(OKL_VARIABLE_INTEREST_PVT.g_vir_tbl_counter).last_update_date := SYSDATE;
2505 OKL_VARIABLE_INTEREST_PVT.g_vir_tbl(OKL_VARIABLE_INTEREST_PVT.g_vir_tbl_counter).last_update_login := FND_GLOBAL.LOGIN_ID;
2506 OKL_VARIABLE_INTEREST_PVT.g_vir_tbl(OKL_VARIABLE_INTEREST_PVT.g_vir_tbl_counter).interest_amt := NULL;
2507 OKL_VARIABLE_INTEREST_PVT.g_vir_tbl(OKL_VARIABLE_INTEREST_PVT.g_vir_tbl_counter).interest_calc_days := NULL;
2508 OKL_VARIABLE_INTEREST_PVT.g_vir_tbl(OKL_VARIABLE_INTEREST_PVT.g_vir_tbl_counter).kle_id := r_get_asset_lines.id;
2509 OKL_VARIABLE_INTEREST_PVT.g_vir_tbl(OKL_VARIABLE_INTEREST_PVT.g_vir_tbl_counter).catchup_amt := NULL;
2510 OKL_VARIABLE_INTEREST_PVT.g_vir_tbl(OKL_VARIABLE_INTEREST_PVT.g_vir_tbl_counter).catchup_settlement_code := NULL;
2511 END LOOP;
2512
2513 ELSE
2514
2515 FOR i IN l_line_prin_tbl.FIRST..l_line_prin_tbl.LAST LOOP
2516 OKL_VARIABLE_INTEREST_PVT.g_vir_tbl_counter := OKL_VARIABLE_INTEREST_PVT.g_vir_tbl_counter + 1;
2517 OKL_VARIABLE_INTEREST_PVT.g_vir_tbl(OKL_VARIABLE_INTEREST_PVT.g_vir_tbl_counter).id := okc_p_util.raw_to_number(sys_guid());
2518 OKL_VARIABLE_INTEREST_PVT.g_vir_tbl(OKL_VARIABLE_INTEREST_PVT.g_vir_tbl_counter).khr_id := p_khr_id;
2519 OKL_VARIABLE_INTEREST_PVT.g_vir_tbl(OKL_VARIABLE_INTEREST_PVT.g_vir_tbl_counter).source_table := 'OKL_VAR_INT_PROCESS_B';
2520 OKL_VARIABLE_INTEREST_PVT.g_vir_tbl(OKL_VARIABLE_INTEREST_PVT.g_vir_tbl_counter).source_id := x_vipv_rec.id;
2521 OKL_VARIABLE_INTEREST_PVT.g_vir_tbl(OKL_VARIABLE_INTEREST_PVT.g_vir_tbl_counter).interest_rate := l_interest_rate_tbl(l_interest_rate_tbl.first).rate;
2522 OKL_VARIABLE_INTEREST_PVT.g_vir_tbl(OKL_VARIABLE_INTEREST_PVT.g_vir_tbl_counter).interest_calc_start_date := l_start_date;
2523 OKL_VARIABLE_INTEREST_PVT.g_vir_tbl(OKL_VARIABLE_INTEREST_PVT.g_vir_tbl_counter).interest_calc_end_date := l_start_date;
2524 OKL_VARIABLE_INTEREST_PVT.g_vir_tbl(OKL_VARIABLE_INTEREST_PVT.g_vir_tbl_counter).calc_method_code := OKL_VARIABLE_INTEREST_PVT.G_CALC_METHOD_CODE;
2525
2526 --Added by bkatraga for bug 16303285
2527 IF (OKL_VARIABLE_INTEREST_PVT.G_DEAL_TYPE = 'LOAN') THEN
2528 OKL_VARIABLE_INTEREST_PVT.g_vir_tbl(OKL_VARIABLE_INTEREST_PVT.g_vir_tbl_counter).principal_balance := l_line_prin_tbl(i).principal_balance;
2529 ELSE
2530 l_asset_prin_bal := 0;
2531 OPEN get_asset_prin_bal(l_super_trump_request_id, l_line_prin_tbl(i).kle_id);
2532 FETCH get_asset_prin_bal INTO l_asset_prin_bal;
2533 CLOSE get_asset_prin_bal;
2534
2535 OKL_VARIABLE_INTEREST_PVT.print_debug('Principal Balance '||l_asset_prin_bal);
2536
2537 -- SuperTRUMP returns ReamortBalance as a negative number. Changing the sign to display Principal Balance as a positive number
2538 IF (NVL(l_asset_prin_bal,0) < 0) THEN
2539 l_asset_prin_bal := -1 * l_asset_prin_bal;
2540 END IF;
2541 OKL_VARIABLE_INTEREST_PVT.g_vir_tbl(OKL_VARIABLE_INTEREST_PVT.g_vir_tbl_counter).principal_balance := NVL(l_asset_prin_bal,0);
2542 END IF;
2543 --end bkatraga for bug 16303285
2544
2545 OKL_VARIABLE_INTEREST_PVT.g_vir_tbl(OKL_VARIABLE_INTEREST_PVT.g_vir_tbl_counter).valid_yn := 'Y';
2546 OKL_VARIABLE_INTEREST_PVT.g_vir_tbl(OKL_VARIABLE_INTEREST_PVT.g_vir_tbl_counter).object_version_number := 1.0;
2547 OKL_VARIABLE_INTEREST_PVT.g_vir_tbl(OKL_VARIABLE_INTEREST_PVT.g_vir_tbl_counter).org_id := OKL_VARIABLE_INTEREST_PVT.g_authoring_org_id;
2548 OKL_VARIABLE_INTEREST_PVT.g_vir_tbl(OKL_VARIABLE_INTEREST_PVT.g_vir_tbl_counter).request_id := l_request_id;
2549 OKL_VARIABLE_INTEREST_PVT.g_vir_tbl(OKL_VARIABLE_INTEREST_PVT.g_vir_tbl_counter).program_application_id := l_program_application_id;
2550 OKL_VARIABLE_INTEREST_PVT.g_vir_tbl(OKL_VARIABLE_INTEREST_PVT.g_vir_tbl_counter).program_id := l_program_id;
2551 OKL_VARIABLE_INTEREST_PVT.g_vir_tbl(OKL_VARIABLE_INTEREST_PVT.g_vir_tbl_counter).program_update_date := SYSDATE;
2552 OKL_VARIABLE_INTEREST_PVT.g_vir_tbl(OKL_VARIABLE_INTEREST_PVT.g_vir_tbl_counter).attribute_category := NULL;
2553 OKL_VARIABLE_INTEREST_PVT.g_vir_tbl(OKL_VARIABLE_INTEREST_PVT.g_vir_tbl_counter).attribute1 := NULL;
2554 OKL_VARIABLE_INTEREST_PVT.g_vir_tbl(OKL_VARIABLE_INTEREST_PVT.g_vir_tbl_counter).attribute2 := NULL;
2555 OKL_VARIABLE_INTEREST_PVT.g_vir_tbl(OKL_VARIABLE_INTEREST_PVT.g_vir_tbl_counter).attribute3 := NULL;
2556 OKL_VARIABLE_INTEREST_PVT.g_vir_tbl(OKL_VARIABLE_INTEREST_PVT.g_vir_tbl_counter).attribute4 := NULL;
2557 OKL_VARIABLE_INTEREST_PVT.g_vir_tbl(OKL_VARIABLE_INTEREST_PVT.g_vir_tbl_counter).attribute5 := NULL;
2558 OKL_VARIABLE_INTEREST_PVT.g_vir_tbl(OKL_VARIABLE_INTEREST_PVT.g_vir_tbl_counter).attribute6 := NULL;
2559 OKL_VARIABLE_INTEREST_PVT.g_vir_tbl(OKL_VARIABLE_INTEREST_PVT.g_vir_tbl_counter).attribute7 := NULL;
2560 OKL_VARIABLE_INTEREST_PVT.g_vir_tbl(OKL_VARIABLE_INTEREST_PVT.g_vir_tbl_counter).attribute8 := NULL;
2561 OKL_VARIABLE_INTEREST_PVT.g_vir_tbl(OKL_VARIABLE_INTEREST_PVT.g_vir_tbl_counter).attribute9 := NULL;
2562 OKL_VARIABLE_INTEREST_PVT.g_vir_tbl(OKL_VARIABLE_INTEREST_PVT.g_vir_tbl_counter).attribute10 := NULL;
2563 OKL_VARIABLE_INTEREST_PVT.g_vir_tbl(OKL_VARIABLE_INTEREST_PVT.g_vir_tbl_counter).attribute11 := NULL;
2564 OKL_VARIABLE_INTEREST_PVT.g_vir_tbl(OKL_VARIABLE_INTEREST_PVT.g_vir_tbl_counter).attribute12 := NULL;
2565 OKL_VARIABLE_INTEREST_PVT.g_vir_tbl(OKL_VARIABLE_INTEREST_PVT.g_vir_tbl_counter).attribute13 := NULL;
2566 OKL_VARIABLE_INTEREST_PVT.g_vir_tbl(OKL_VARIABLE_INTEREST_PVT.g_vir_tbl_counter).attribute14 := NULL;
2567 OKL_VARIABLE_INTEREST_PVT.g_vir_tbl(OKL_VARIABLE_INTEREST_PVT.g_vir_tbl_counter).attribute15 := NULL;
2568 OKL_VARIABLE_INTEREST_PVT.g_vir_tbl(OKL_VARIABLE_INTEREST_PVT.g_vir_tbl_counter).created_by := FND_GLOBAL.USER_ID;
2569 OKL_VARIABLE_INTEREST_PVT.g_vir_tbl(OKL_VARIABLE_INTEREST_PVT.g_vir_tbl_counter).creation_date := SYSDATE;
2570 OKL_VARIABLE_INTEREST_PVT.g_vir_tbl(OKL_VARIABLE_INTEREST_PVT.g_vir_tbl_counter).last_updated_by := FND_GLOBAL.USER_ID;
2571 OKL_VARIABLE_INTEREST_PVT.g_vir_tbl(OKL_VARIABLE_INTEREST_PVT.g_vir_tbl_counter).last_update_date := SYSDATE;
2572 OKL_VARIABLE_INTEREST_PVT.g_vir_tbl(OKL_VARIABLE_INTEREST_PVT.g_vir_tbl_counter).last_update_login := FND_GLOBAL.LOGIN_ID;
2573 OKL_VARIABLE_INTEREST_PVT.g_vir_tbl(OKL_VARIABLE_INTEREST_PVT.g_vir_tbl_counter).interest_amt := NULL;
2574 OKL_VARIABLE_INTEREST_PVT.g_vir_tbl(OKL_VARIABLE_INTEREST_PVT.g_vir_tbl_counter).interest_calc_days := NULL;
2575 OKL_VARIABLE_INTEREST_PVT.g_vir_tbl(OKL_VARIABLE_INTEREST_PVT.g_vir_tbl_counter).kle_id := l_line_prin_tbl(i).kle_id;
2576 OKL_VARIABLE_INTEREST_PVT.g_vir_tbl(OKL_VARIABLE_INTEREST_PVT.g_vir_tbl_counter).catchup_amt := NULL;
2577 OKL_VARIABLE_INTEREST_PVT.g_vir_tbl(OKL_VARIABLE_INTEREST_PVT.g_vir_tbl_counter).catchup_settlement_code := NULL;
2578 END LOOP;
2579 END IF;
2580 --Bug# 14559654
2581
2582 OKL_VR_DATA_HANDLER_PVT.INSERT_VIR_PARAMS(
2583 p_api_version => p_api_version,
2584 p_init_msg_list => p_init_msg_list,
2585 x_return_status => x_return_status,
2586 x_msg_count => x_msg_count,
2587 x_msg_data => x_msg_data,
2588 p_vir_tbl => OKL_VARIABLE_INTEREST_PVT.g_vir_tbl);
2589
2590 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2591 OKL_VARIABLE_INTEREST_PVT.print_error_message('Unexpected error raised in call to INSERT_VIR_PARAMS');
2592 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2593 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2594 OKL_VARIABLE_INTEREST_PVT.print_error_message('Error raised in call to INSERT_VIR_PARAMS');
2595 RAISE initiate_request_failed;
2596 END IF;
2597 OKL_VARIABLE_INTEREST_PVT.print_debug('Updating OKL_VAR_INT_PARAMS Successful');
2598 OKL_VARIABLE_INTEREST_PVT.g_vir_tbl.delete;
2599
2600 END IF;
2601 END IF;
2602
2603 OKL_VARIABLE_INTEREST_PVT.print_debug('****Exiting procedure INITIATE_REQUEST****');
2604
2605 EXCEPTION
2606 WHEN initiate_request_failed THEN
2607 OKL_VARIABLE_INTEREST_PVT.print_error_message ('Exception initiate_request_failed raised in procedure initiate_request');
2608 x_return_status := OKL_API.G_RET_STS_ERROR;
2609 WHEN OTHERS THEN
2610 OKL_VARIABLE_INTEREST_PVT.print_error_message ('Exception raised in procedure initiate_request');
2611 Okl_Api.SET_MESSAGE(
2612 p_app_name => G_APP_NAME,
2613 p_msg_name => G_UNEXPECTED_ERROR,
2614 p_token1 => G_SQLCODE_TOKEN,
2615 p_token1_value => SQLCODE,
2616 p_token2 => G_SQLERRM_TOKEN,
2617 p_token2_value => SQLERRM);
2618
2619 x_return_status := OKL_API.G_RET_STS_ERROR;
2620 END initiate_request;
2621
2622
2623 -----------------------------------------------------------------
2624 PROCEDURE process_interest_reamort(
2625 p_api_version IN NUMBER,
2626 p_init_msg_list IN VARCHAR2,
2627 x_return_status OUT NOCOPY VARCHAR2,
2628 x_msg_count OUT NOCOPY NUMBER,
2629 x_msg_data OUT NOCOPY VARCHAR2,
2630 p_contract_id IN NUMBER,
2631 p_to_date IN DATE,
2632 p_last_interest_cal_date IN DATE,
2633 p_print_lead_days IN NUMBER
2634 )
2635 IS
2636
2637 process_int_reamort_failed EXCEPTION;
2638 l_next_period_start_date DATE; --Added by bkatraga for bug 13693187
2639
2640 BEGIN
2641 OKL_VARIABLE_INTEREST_PVT.print_debug ('contract id : '|| p_contract_id );
2642
2643 --Added by bkatraga for bug 13693187
2644 l_next_period_start_date := OKL_VARIABLE_INTEREST_PVT.get_next_period_start_date(p_contract_id, NVL(p_last_interest_cal_date, OKL_VARIABLE_INTEREST_PVT.G_CONTRACT_START_DATE));
2645 IF(l_next_period_start_date IS NULL) THEN
2646 OKL_VARIABLE_INTEREST_PVT.print_error_message('Error raised in function get_next_period_start_date');
2647 RAISE process_int_reamort_failed;
2648 END IF;
2649 --end bkatraga
2650
2651 IF ((NVL(p_last_interest_cal_date, OKL_VARIABLE_INTEREST_PVT.G_CONTRACT_START_DATE) > (trunc(sysdate) + p_print_lead_days)) OR
2652 ((NVL(p_last_interest_cal_date, OKL_VARIABLE_INTEREST_PVT.G_CONTRACT_START_DATE) > nvl(p_to_date, trunc(SYSDATE)))) OR
2653 ((NVL(p_last_interest_cal_date, OKL_VARIABLE_INTEREST_PVT.G_CONTRACT_START_DATE) > OKL_VARIABLE_INTEREST_PVT.G_CONTRACT_END_DATE))) THEN
2654 OKL_VARIABLE_INTEREST_PVT.print_error_message('Reamort Date is past the system date (with print lead days included) or is past the To Date or is past the Contract End Date.');
2655 --Added ELSIF by bkatraga for bug 13693187
2656 ELSIF (NVL(p_last_interest_cal_date, OKL_VARIABLE_INTEREST_PVT.G_CONTRACT_START_DATE) = l_next_period_start_date) THEN
2657 OKL_VARIABLE_INTEREST_PVT.print_error_message('No payments to reamort after Last Interest Calculation Date.');
2658 ELSE
2659 initiate_request(
2660 p_api_version => 1.0,
2661 p_init_msg_list => OKL_API.G_TRUE,
2662 p_khr_id => p_contract_id,
2663 x_return_status => x_return_status,
2664 x_msg_count => x_msg_count,
2665 x_msg_data => x_msg_data);
2666
2667 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
2668 OKL_VARIABLE_INTEREST_PVT.print_error_message('Unexpected error raised in call to INITIATE_REQUEST');
2669 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
2670 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
2671 OKL_VARIABLE_INTEREST_PVT.print_error_message('Error raised in call to INITIATE_REQUEST');
2672 RAISE process_int_reamort_failed;
2673 END IF;
2674 OKL_VARIABLE_INTEREST_PVT.print_debug('Variable interest calculation completed successfully for contract id : '|| p_contract_id);
2675 COMMIT;
2676 END IF;
2677
2678 EXCEPTION
2679 WHEN process_int_reamort_failed THEN
2680 OKL_VARIABLE_INTEREST_PVT.print_error_message ('Exception process_int_reamort_failed raised in procedure PROCESS_INTEREST_REAMORT');
2681 x_return_status := OKL_API.G_RET_STS_ERROR;
2682 WHEN OTHERS THEN
2683 OKL_VARIABLE_INTEREST_PVT.print_error_message ('Exception raised in procedure PROCESS_INTEREST_REAMORT');
2684 Okl_Api.SET_MESSAGE(
2685 p_app_name => G_APP_NAME,
2686 p_msg_name => G_UNEXPECTED_ERROR,
2687 p_token1 => G_SQLCODE_TOKEN,
2688 p_token1_value => SQLCODE,
2689 p_token2 => G_SQLERRM_TOKEN,
2690 p_token2_value => SQLERRM);
2691 x_return_status := OKL_API.G_RET_STS_ERROR;
2692 END process_interest_reamort;
2693
2694 END;