[Home] [Help]
PACKAGE BODY: APPS.OKL_AM_CALC_QUOTE_PYMNT_PVT
Source
1 PACKAGE BODY OKL_AM_CALC_QUOTE_PYMNT_PVT AS
2 /* $Header: OKLRCQPB.pls 120.6.12020000.2 2012/12/05 06:11:30 vsgandhi ship $ */
3
4 --Bug 4299668 PAGARG declared these table types & counters for bulk insert
5 --**START**--
6 --Define it globally, keep populating the table of records and finally make a
7 --call to bulk insert procedure
8 g_cfov_tbl_type okl_cash_flow_objects_pub.cfov_tbl_type;
9 g_cafv_tbl_type okl_cash_flows_pub.cafv_tbl_type;
10 g_cflv_tbl_type okl_cash_flow_levels_pub.cflv_tbl_type;
11 g_qcov_tbl_type okl_trx_qte_cf_objects_pub.qcov_tbl_type;
12 gx_cfov_tbl_type okl_cash_flow_objects_pub.cfov_tbl_type;
13 gx_cafv_tbl_type okl_cash_flows_pub.cafv_tbl_type;
14 gx_cflv_tbl_type okl_cash_flow_levels_pub.cflv_tbl_type;
15 gx_qcov_tbl_type okl_trx_qte_cf_objects_pub.qcov_tbl_type;
16 g_cfov_counter NUMBER := 0;
17 g_cafv_counter NUMBER := 0;
18 g_cflv_counter NUMBER := 0;
19 g_qcov_counter NUMBER := 0;
20 --**END 4299668**--
21
22 -- GLOBAL VARIABLES for debug logging
23 G_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
24 G_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
25 G_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
26 G_MODULE_NAME CONSTANT VARCHAR2(500) := 'okl.am.plsql.Okl_am_calc_quote_pymnt_pvt.';
27
28 TYPE cashflow_rec_type IS RECORD
29 ( p_cfo_id NUMBER,
30 p_sts_code VARCHAR2(30),
31 p_sty_id NUMBER,
32 p_due_arrears_yn VARCHAR2(3),
33 p_start_date DATE,
34 p_advance_periods NUMBER,
35 p_khr_id NUMBER,
36 p_quote_id NUMBER,
37 p_amount NUMBER,
38 p_period_in_months NUMBER,
39 p_frequency VARCHAR2(30),
40 p_seq_num NUMBER DEFAULT NULL,
41 p_stub_days NUMBER,
42 p_stub_amount NUMBER);
43
44 --Bug 4299668 PAGARG Function to obtain sequence id for primary key
45 --It is used for cash flow object & cash flows as their refernce is to be stored
46 --in child records
47 --**START**--
48 FUNCTION get_seq_id RETURN NUMBER IS
49 BEGIN
50 RETURN(okc_p_util.raw_to_number(sys_guid()));
51 END get_seq_id;
52 --**END 4299668**--
53
54 /*========================================================================
55 | PUBLIC PROCEDURE get_payment_summary
56 |
57 | DESCRIPTION
58 | This procedure is used by the first payment screen to display payment
59 | summary information
60 |
61 | CALLED FROM PROCEDURES/FUNCTIONS
62 | This procedure is called directly from the payment details screen
63 |
64 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
65 |
66 |
67 | PARAMETERS
68 | p_qte_id IN Quote ID
69 | x_pymt_smry_tbl OUT Payment Summary Table
70 | x_pymt_smry_tbl_count OUT Payment Summary Table Count
71 | x_total_curr_amt OUT Total Curernt Amount
72 | x_total_prop_amt OUT Total proposed Amount
73 |
74 | KNOWN ISSUES
75 |
76 | NOTES
77 |
78 |
79 | MODIFICATION HISTORY
80 | Date Author Description of Changes
81 | 14-OCT-2003 SECHAWLA Created
82 | 26-Apr-2005 PAGARG Bug 4299668 Populating stream type id also in payment
83 | summary table of records as stream type code may not be
84 | unique after UDS impact.
85 *=======================================================================*/
86 PROCEDURE get_payment_summary(p_api_version IN NUMBER,
87 p_init_msg_list IN VARCHAR2,
88 x_msg_count OUT NOCOPY NUMBER,
89 x_msg_data OUT NOCOPY VARCHAR2,
90 x_return_status OUT NOCOPY VARCHAR2,
91 p_qte_id IN NUMBER,
92 x_pymt_smry_tbl OUT NOCOPY pymt_smry_uv_tbl_type,
93 x_pymt_smry_tbl_count OUT NOCOPY NUMBER,
94 x_total_curr_amt OUT NOCOPY NUMBER,
95 x_total_prop_amt OUT NOCOPY NUMBER) IS
96
97 /*-----------------------------------------------------------------------+
98 | Cursor Declarations |
99 +-----------------------------------------------------------------------*/
100
101 -- This cursor returns the current total and proposed total for each stream type
102 CURSOR l_pymtsummary_csr(cp_qte_id IN NUMBER) IS
103 --PAGARG Bug 4299668: Query Stream type id also.
104 SELECT stm.id STY_ID
105 , stm.code sty_code
106 , caf.sts_code status
107 , caf.dnz_khr_id khr_id
108 , (nvl(sum(amount * number_of_periods),0) + nvl(sum(stub_amount),0)) Total
109 FROM okl_cash_flows caf
110 , okl_strm_type_b stm
111 , okl_cash_flow_levels cfl
112 WHERE dnz_qte_id = cp_qte_id
113 AND caf.sty_id = stm.id
114 AND caf.id = cfl.caf_id
115 AND caf.sts_code IN ( G_CURRENT_STATUS, G_PROPOSED_STATUS)
116 AND caf.cft_code = G_CASH_FLOW_TYPE
117 GROUP BY stm.id
118 , stm.code
119 , caf.sts_code
120 , caf.dnz_khr_id
121 ORDER BY stm.id;
122
123 /*-----------------------------------------------------------------------+
124 | Local Variable Declarations and initializations |
125 +-----------------------------------------------------------------------*/
126
127 l_pymt_smry_tbl pymt_smry_uv_tbl_type;
128 l_tbl_count NUMBER := 0;
129 l_csr_count NUMBER := 0;
130 l_prev_sty_id NUMBER := -1;
131 l_total_curr_amt NUMBER := 0 ;
132 l_total_prop_amt NUMBER := 0 ;
133
134 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
135 l_api_version CONSTANT NUMBER := 1;
136 l_api_name CONSTANT VARCHAR2(30) := 'get_payment_summary';
137
138 L_MODULE_NAME VARCHAR2(500) := G_MODULE_NAME||'get_payment_summary';
139 is_debug_exception_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_EXCEPTION);
140 is_debug_procedure_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_PROCEDURE);
141 is_debug_statement_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_STATEMENT);
142 BEGIN
143
144 IF (is_debug_procedure_on) THEN
145 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'Begin(+)');
146 END IF;
147
148 --Print Input Variables
149 IF (is_debug_statement_on) THEN
150 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
151 'p_qte_id :'||p_qte_id);
152
153 END IF;
154
155 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
156 G_PKG_NAME,
157 p_init_msg_list,
158 l_api_version,
159 p_api_version,
160 '_PVT',
161 x_return_status);
162
163 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
164 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
165 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
166 RAISE OKL_API.G_EXCEPTION_ERROR;
167 END IF;
168
169 IF p_qte_id IS NULL OR p_qte_id = OKL_API.G_MISS_NUM THEN
170
171 -- quote id is required
172 OKL_API.set_message( p_app_name => 'OKC',
173 p_msg_name => G_REQUIRED_VALUE,
174 p_token1 => G_COL_NAME_TOKEN,
175 p_token1_value => 'QUOTE_ID');
176 RAISE OKL_API.G_EXCEPTION_ERROR;
177 END IF;
178
179 -- This cursor will return 2 rows for each stream type
180 FOR l_pymtsummary_rec IN l_pymtsummary_csr(p_qte_id) LOOP
181 l_csr_count := l_csr_count + 1;
182
183 IF l_pymtsummary_rec.sty_id <> l_prev_sty_id THEN
184 l_tbl_count := l_tbl_count + 1;
185
186 --PAGARG Bug 4299668: Populate Stream type id also.
187 l_pymt_smry_tbl(l_tbl_count).p_strm_type_id := l_pymtsummary_rec.sty_id;
188 l_pymt_smry_tbl(l_tbl_count).p_strm_type_code := l_pymtsummary_rec.sty_code;
189 l_pymt_smry_tbl(l_tbl_count).p_curr_total := l_pymtsummary_rec.total;
190
191 l_prev_sty_id := l_pymtsummary_rec.sty_id;
192
193 l_total_curr_amt := l_total_curr_amt + l_pymtsummary_rec.total;
194 ELSE
195 l_pymt_smry_tbl(l_tbl_count).p_prop_total := l_pymtsummary_rec.total;
196
197 l_total_prop_amt := l_total_prop_amt + l_pymtsummary_rec.total ;
198 END IF;
199
200 END LOOP;
201
202 x_pymt_smry_tbl_count := l_tbl_count;
203 x_pymt_smry_tbl := l_pymt_smry_tbl;
204 x_total_curr_amt := l_total_curr_amt ;
205 x_total_prop_amt := l_total_prop_amt ;
206 x_return_status := l_return_status;
207
208 OKL_API.END_ACTIVITY (x_msg_count, x_msg_data);
209
210 IF (is_debug_procedure_on) THEN
211 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'End(-)');
212 END IF;
213
214 EXCEPTION
215 WHEN OKL_API.G_EXCEPTION_ERROR THEN
216 IF (is_debug_exception_on) THEN
217 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_EXCEPTION,L_MODULE_NAME,
218 'EXCEPTION :'||'OKL_API.G_EXCEPTION_ERROR');
219 END IF;
220
221 IF l_pymtsummary_csr%ISOPEN THEN
222 CLOSE l_pymtsummary_csr;
223 END IF;
224 x_return_status := OKL_API.G_RET_STS_ERROR;
225 WHEN OTHERS THEN
226 IF (is_debug_exception_on) THEN
227 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_EXCEPTION,L_MODULE_NAME,
228 'EXCEPTION :'||sqlerrm);
229 END IF;
230 -- Close open cursors
231
232 IF l_pymtsummary_csr%ISOPEN THEN
233 CLOSE l_pymtsummary_csr;
234 END IF;
235
236 -- store SQL error message on message stack for caller
237 OKL_API.SET_MESSAGE (
238 p_app_name => G_APP_NAME
239 ,p_msg_name => G_UNEXPECTED_ERROR
240 ,p_token1 => G_SQLCODE_TOKEN
241 ,p_token1_value => sqlcode
242 ,p_token2 => G_SQLERRM_TOKEN
243 ,p_token2_value => sqlerrm);
244
245 -- notify caller of an UNEXPECTED error
246 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
247
248 END get_payment_summary;
249
250 /*========================================================================
251 | PRIVATE PROCEDURE create_cash_flow_object
252 |
253 | DESCRIPTION
254 | This procedure creates a cash flow object
255 |
256 | CALLED FROM PROCEDURES/FUNCTIONS
257 | get_current_payments, calc_prop_line_payments, calc_proposed_payments
258 |
259 | CALLS PROCEDURES/FUNCTIONS
260 |
261 |
262 | PARAMETERS
263 | p_obj_type_code IN Object type
264 | p_src_table IN Source Table originating the cash flow object
265 | p_src_id IN ID of the row in the source table
266 | p_base_src_id IN ID of the source of the cash flow object
267 | p_sts_code IN cash flow status
268 | x_cfo_id OUT cash flow Object ID
269 |
270 |
271 | KNOWN ISSUES
272 |
273 | NOTES
274 |
275 |
276 | MODIFICATION HISTORY
277 | Date Author Description of Changes
278 | 14-OCT-2003 SECHAWLA Created
279 | 22-Apr-2005 4299668 PAGARG Instead of calling insert for each cash flow object,
280 | store it in table and finally call bulk insert
281 *=======================================================================*/
282
283 PROCEDURE create_cash_flow_object(p_api_version IN NUMBER,
284 x_msg_count OUT NOCOPY NUMBER,
285 x_msg_data OUT NOCOPY VARCHAR2,
286 p_obj_type_code IN VARCHAR2,
287 p_src_table IN VARCHAR2,
288 p_src_id IN NUMBER,
289 p_base_src_id IN NUMBER,
290 p_sts_code IN VARCHAR2,
291 x_cfo_id OUT NOCOPY NUMBER,
292 x_return_status OUT NOCOPY VARCHAR2) IS
293
294
295 /*-----------------------------------------------------------------------+
296 | Cursor Declarations |
297 +-----------------------------------------------------------------------*/
298
299 --This cursor checks if an object already exists
300 CURSOR l_cash_flow_objects_csr(cp_oty_code IN VARCHAR2, cp_source_table IN VARCHAR2,
301 cp_source_id IN NUMBER, cp_sts_code IN VARCHAR2,
302 cp_base_src_id IN NUMBER) IS
303 --SELECT cfo.id
304 SELECT 'x'
305 FROM okl_cash_flow_objects cfo, okl_cash_flows caf, OKL_TRX_QTE_CF_OBJECTS qco
306 WHERE cfo.id = caf.cfo_id
307 AND cfo.id = qco.cfo_id
308 AND cfo.oty_code = cp_oty_code
309 AND cfo.source_table = cp_source_table
310 AND cfo.source_id = cp_source_id
311 AND caf.sts_code = cp_sts_code
312 AND qco.base_source_id = cp_base_src_id;
313
314 /*-----------------------------------------------------------------------+
315 | SubType Declarations
316 +-----------------------------------------------------------------------*/
317
318 SUBTYPE cfov_rec_type IS okl_cash_flow_objects_pub.cfov_rec_type;
319
320
321 /*-----------------------------------------------------------------------+
322 | Local Variable Declarations and initializations |
323 +-----------------------------------------------------------------------*/
324
325 l_cfo_id NUMBER;
326 lp_cfov_rec cfov_rec_type;
327 lx_cfov_rec cfov_rec_type;
328 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
329 l_dummy VARCHAR2(1);
330
331 L_MODULE_NAME VARCHAR2(500) := G_MODULE_NAME||'create_cash_flow_object';
332 is_debug_exception_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_EXCEPTION);
333 is_debug_procedure_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_PROCEDURE);
334 is_debug_statement_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_STATEMENT);
335 BEGIN
336
337 IF (is_debug_procedure_on) THEN
338 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'Begin(+)');
339 END IF;
340
341 --Print Input Variables
342 IF (is_debug_statement_on) THEN
343 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
344 'p_obj_type_code :'||p_obj_type_code);
345 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
346 'p_src_table :'||p_src_table);
347 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
348 'p_src_id :'||p_src_id);
349 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
350 'p_base_src_id :'||p_base_src_id);
351 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
352 'p_sts_code :'||p_sts_code);
353 END IF;
354
355 -- Check if Object already exists
356 OPEN l_cash_flow_objects_csr(p_obj_type_code, p_src_table, p_src_id, p_sts_code, p_base_src_id);
357 FETCH l_cash_flow_objects_csr INTO l_dummy;
358
359 IF l_cash_flow_objects_csr%NOTFOUND THEN -- Object does not exist
360
361 lp_cfov_rec.oty_code := p_obj_type_code;
362 lp_cfov_rec.source_table := p_src_table;
363 lp_cfov_rec.source_id := p_src_id;
364
365 --Bug 4299668 PAGARG Instead of calling the procedure to insert cash flow
366 --object, store the record in the table
367 --**START**--
368 lp_cfov_rec.id := get_seq_id;
369 g_cfov_counter := g_cfov_counter + 1;
370 g_cfov_tbl_type(g_cfov_counter) := lp_cfov_rec;
371
372 x_cfo_id := lp_cfov_rec.id;
373 --**END 4299668**--
374
375 ELSE
376 --OTY_CODE object already exists for STS_CODE payments.
377
378 OKL_API.set_message(p_app_name => 'OKL',
379 p_msg_name => 'OKL_AM_OBJ_EXISTS',
380 p_token1 => 'OTY_CODE',
381 p_token1_value => p_obj_type_code,
382 p_token2 => 'STS_CODE',
383 p_token2_value => p_sts_code);
384 RAISE OKL_API.G_EXCEPTION_ERROR;
385 END IF;
386 CLOSE l_cash_flow_objects_csr;
387
388 x_return_status := l_return_status;
389
390 IF (is_debug_procedure_on) THEN
391 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'end(-)');
392 END IF;
393
394 EXCEPTION
395 WHEN OKL_API.G_EXCEPTION_ERROR THEN
396
397 IF (is_debug_exception_on) THEN
398 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_EXCEPTION,L_MODULE_NAME,
399 'EXCEPTION :'||'OKL_API.G_EXCEPTION_ERROR');
400 END IF;
401
402 IF l_cash_flow_objects_csr%ISOPEN THEN
403 CLOSE l_cash_flow_objects_csr;
404 END IF;
405 x_return_status := OKL_API.G_RET_STS_ERROR;
406 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
407
408 IF (is_debug_exception_on) THEN
409 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_EXCEPTION,L_MODULE_NAME,
410 'EXCEPTION :'||'OKL_API.G_EXCEPTION_UNEXPECTED_ERROR');
411 END IF;
412
413 IF l_cash_flow_objects_csr%ISOPEN THEN
414 CLOSE l_cash_flow_objects_csr;
415 END IF;
416 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
417 WHEN OTHERS THEN
418
419 IF (is_debug_exception_on) THEN
420 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_EXCEPTION,L_MODULE_NAME,
421 'EXCEPTION :'||sqlerrm);
422 END IF;
423
424 IF l_cash_flow_objects_csr%ISOPEN THEN
425 CLOSE l_cash_flow_objects_csr;
426 END IF;
427 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
428 -- unexpected error
429 OKL_API.set_message(p_app_name => g_app_name,
430 p_msg_name => g_unexpected_error,
431 p_token1 => g_sqlcode_token,
432 p_token1_value => sqlcode,
433 p_token2 => g_sqlerrm_token,
434 p_token2_value => sqlerrm);
435 END create_cash_flow_object;
436
437
438 /*========================================================================
439 | PRIVATE PROCEDURE create_cash_flows
440 |
441 | DESCRIPTION
442 | This procedure creates cash flow header and cash flow levels
443 |
444 | CALLED FROM PROCEDURES/FUNCTIONS
445 | get_current_payments, calc_prop_line_payments, calc_proposed_payments
446 |
447 | CALLS PROCEDURES/FUNCTIONS
448 |
449 |
450 | PARAMETERS
451 | p_cashflow_rec IN Cash Flow rec details (cashflow_rec_type)
452 | px_new_cash_flow IN OUT flag to indicate if it is a new cash flow
453 |
454 | KNOWN ISSUES
455 |
456 | NOTES
457 |
458 |
459 | MODIFICATION HISTORY
460 | Date Author Description of Changes
461 | 14-OCT-2003 SECHAWLA Created
462 | 22-Apr-2005 4299668 PAGARG Instead of calling insert for each cash flow
463 | and cash flow level, store it in table and
464 | finally call bulk insert
465 *=======================================================================*/
466 PROCEDURE create_cash_flows(p_api_version IN NUMBER,
467 x_msg_count OUT NOCOPY NUMBER,
468 x_msg_data OUT NOCOPY VARCHAR2,
469 p_cashflow_rec IN cashflow_rec_type,
470 px_new_cash_flow IN OUT NOCOPY VARCHAR2,
471 x_return_status OUT NOCOPY VARCHAR2
472 ) IS
473
474 /*-----------------------------------------------------------------------+
475 | Cursor Declarations |
476 +-----------------------------------------------------------------------*/
477
478 -- This cursor checks if a cash flow header has already been created for a stream type (payment type)
479 CURSOR l_cashflow_csr(cp_cfo_id IN NUMBER, cp_sty_id IN NUMBER) IS
480 SELECT id
481 FROM okl_cash_flows
482 WHERE cfo_id = cp_cfo_id
483 AND sty_id = cp_sty_id;
484
485 -- get the currency code for the contract for which the quote is created
486 CURSOR l_kheaders_csr(cp_khr_id IN NUMBER) IS
487 SELECT currency_code
488 FROM okc_k_headers_b
489 WHERE id = cp_khr_id;
490
491 /*-----------------------------------------------------------------------+
492 | SubType Declarations
493 +-----------------------------------------------------------------------*/
494
495 SUBTYPE cafv_rec_type IS okl_cash_flows_pub.cafv_rec_type;
496 SUBTYPE cflv_rec_type IS okl_cash_flow_levels_pub.cflv_rec_type;
497
498 /*-----------------------------------------------------------------------+
499 | Local Variable Declarations and initializations |
500 +-----------------------------------------------------------------------*/
501
502 lp_cafv_rec cafv_rec_type;
503 lx_cafv_rec cafv_rec_type;
504
505 lp_cflv_rec cflv_rec_type;
506 lx_cflv_rec cflv_rec_type;
507
508 l_dummy VARCHAR2(1) := '?';
509 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
510 l_caf_id NUMBER ;
511 l_currency_code VARCHAR2(15);
512 l_amount NUMBER;
513 l_stub_amount NUMBER;
514
515 L_MODULE_NAME VARCHAR2(500) := G_MODULE_NAME||'create_cash_flows';
516 is_debug_exception_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_EXCEPTION);
517 is_debug_procedure_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_PROCEDURE);
518 is_debug_statement_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_STATEMENT);
519 BEGIN
520
521 IF (is_debug_procedure_on) THEN
522 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'Begin(+)');
523 END IF;
524
525 --Print Input Variables
526 IF (is_debug_statement_on) THEN
527 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
528 'p_cashflow_rec.p_cfo_id :'||p_cashflow_rec.p_cfo_id);
529 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
530 'p_cashflow_rec.p_sts_code :'||p_cashflow_rec.p_sts_code);
531 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
532 'p_cashflow_rec.p_sty_id :'||p_cashflow_rec.p_sty_id);
533 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
534 'p_cashflow_rec.p_sty_id :'||p_cashflow_rec.p_sty_id);
535 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
536 'p_cashflow_rec.p_sty_id :'||p_cashflow_rec.p_sty_id);
537 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
538 'p_cashflow_rec.p_due_arrears_yn :'||p_cashflow_rec.p_due_arrears_yn);
539 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
540 'p_cashflow_rec.p_start_date :'||p_cashflow_rec.p_start_date);
541 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
542 'p_cashflow_rec.p_advance_periods :'||p_cashflow_rec.p_advance_periods);
543 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
544 'p_cashflow_rec.p_khr_id :'||p_cashflow_rec.p_khr_id);
545 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
546 'p_cashflow_rec.p_quote_id :'||p_cashflow_rec.p_quote_id);
547 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
548 'p_cashflow_rec.p_amount :'||p_cashflow_rec.p_amount);
549 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
550 'p_cashflow_rec.p_period_in_months :'||p_cashflow_rec.p_period_in_months);
551 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
552 'p_cashflow_rec.p_frequency :'||p_cashflow_rec.p_frequency);
553 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
554 'p_cashflow_rec.p_seq_num :'||p_cashflow_rec.p_seq_num);
555 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
556 'p_cashflow_rec.p_stub_days :'||p_cashflow_rec.p_stub_days);
557 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
558 'px_new_cash_flow :'||px_new_cash_flow);
559
560 END IF;
561
562 OPEN l_cashflow_csr(p_cashflow_rec.p_cfo_id , p_cashflow_rec.p_sty_id);
563 FETCH l_cashflow_csr INTO l_caf_id;
564 CLOSE l_cashflow_csr;
565
566 IF l_caf_id IS NULL THEN -- Stream type has not been inserted yet in the cash flow header
567
568 lp_cafv_rec.cfo_id := p_cashflow_rec.p_cfo_id;
569 lp_cafv_rec.sts_code := p_cashflow_rec.p_sts_code;
570 lp_cafv_rec.sty_id := p_cashflow_rec.p_sty_id;
571 lp_cafv_rec.cft_code := G_CASH_FLOW_TYPE;
572 lp_cafv_rec.due_arrears_yn := nvl(p_cashflow_rec.p_due_arrears_yn,'N');
573 lp_cafv_rec.start_date := p_cashflow_rec.p_start_date;
574 lp_cafv_rec.number_of_advance_periods := p_cashflow_rec.p_advance_periods;
575 lp_cafv_rec.dnz_khr_id := p_cashflow_rec.p_khr_id;
576 lp_cafv_rec.dnz_qte_id := p_cashflow_rec.p_quote_id;
577
578 --Bug 4299668 PAGARG Instead of calling the procedure to insert cash flow,
579 --store the record in the table
580 --**START**--
581 lp_cafv_rec.id := get_seq_id;
582 g_cafv_counter := g_cafv_counter + 1;
583 g_cafv_tbl_type(g_cafv_counter) := lp_cafv_rec;
584 l_caf_id := lp_cafv_rec.id;
585 --**END 4299668**--
586
587 px_new_cash_flow := 'Y';
588 END IF;
589
590 IF l_caf_id IS NULL THEN
591 l_caf_id := lx_cafv_rec.id;
592 END IF;
593
594 -- Create cash flow level
595 IF px_new_cash_flow = 'Y' THEN
596 -- lp_cflv_rec.caf_id := lx_cafv_rec.id;
597 lp_cflv_rec.caf_id := l_caf_id;
598
599 OPEN l_kheaders_csr(p_cashflow_rec.p_khr_id);
600 FETCH l_kheaders_csr INTO l_currency_code;
601 IF l_kheaders_csr%NOTFOUND THEN
602 -- contract ID is invalid
603 OKL_API.set_message( p_app_name => 'OKC',
604 p_msg_name => G_INVALID_VALUE,
605 p_token1 => G_COL_NAME_TOKEN,
606 p_token1_value => 'CONTRACT_ID');
607
608 RAISE OKL_API.G_EXCEPTION_ERROR;
609 END IF;
610 CLOSE l_kheaders_csr;
611
612 IF p_cashflow_rec.p_stub_days IS NOT NULL THEN
613 okl_accounting_util.round_amount(
614 p_api_version => p_api_version,
615 p_init_msg_list => OKL_API.G_FALSE,
616 x_return_status => l_return_status,
617 x_msg_count => x_msg_count,
618 x_msg_data => x_msg_data,
619 p_amount => p_cashflow_rec.p_stub_amount,
620 p_currency_code => l_currency_code,
621 p_round_option => 'AEL',
622 x_rounded_amount => l_stub_amount);
623
624 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
625 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
626 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
627 RAISE OKL_API.G_EXCEPTION_ERROR;
628 END IF;
629
630 lp_cflv_rec.stub_days := p_cashflow_rec.p_stub_days;
631 lp_cflv_rec.stub_amount := l_stub_amount;
632 ELSE
633
634 okl_accounting_util.round_amount(
635 p_api_version => p_api_version,
636 p_init_msg_list => OKL_API.G_FALSE,
637 x_return_status => l_return_status,
638 x_msg_count => x_msg_count,
639 x_msg_data => x_msg_data,
640 p_amount => p_cashflow_rec.p_amount,
641 p_currency_code => l_currency_code,
642 p_round_option => 'AEL',
643 x_rounded_amount => l_amount);
644
645 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
646 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
647 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
648 RAISE OKL_API.G_EXCEPTION_ERROR;
649 END IF;
650
651 lp_cflv_rec.amount := l_amount;
652 lp_cflv_rec.number_of_periods := p_cashflow_rec.p_period_in_months;
653 lp_cflv_rec.fqy_code := p_cashflow_rec.p_frequency;
654
655 END IF;
656
657 lp_cflv_rec.level_sequence := p_cashflow_rec.p_seq_num;
658 lp_cflv_rec.start_date := p_cashflow_rec.p_start_date;
659
660 --Bug 4299668 PAGARG Instead of calling the procedure to insert cash flow
661 --level, store the record in the table
662 --**START**--
663 g_cflv_counter := g_cflv_counter + 1;
664 g_cflv_tbl_type(g_cflv_counter) := lp_cflv_rec;
665 --**END 4299668**--
666
667 END IF;
668 x_return_status := l_return_status;
669 IF (is_debug_procedure_on) THEN
670 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'End(-)');
671 END IF;
672
673 EXCEPTION
674 WHEN OKL_API.G_EXCEPTION_ERROR THEN
675
676 IF (is_debug_exception_on) THEN
677 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_EXCEPTION,L_MODULE_NAME,
678 'EXCEPTION :'||'OKL_API.G_EXCEPTION_ERROR');
679 END IF;
680
681 IF l_cashflow_csr%ISOPEN THEN
682 CLOSE l_cashflow_csr;
683 END IF;
684 IF l_kheaders_csr%ISOPEN THEN
685 CLOSE l_kheaders_csr;
686 END IF;
687 x_return_status := OKL_API.G_RET_STS_ERROR;
688 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
689
690 IF (is_debug_exception_on) THEN
691 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_EXCEPTION,L_MODULE_NAME,
692 'EXCEPTION :'||'OKL_API.G_EXCEPTION_UNEXPECTED_ERROR');
693 END IF;
694
695 IF l_cashflow_csr%ISOPEN THEN
696 CLOSE l_cashflow_csr;
697 END IF;
698 IF l_kheaders_csr%ISOPEN THEN
699 CLOSE l_kheaders_csr;
700 END IF;
701 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
702 WHEN OTHERS THEN
703
704 IF (is_debug_exception_on) THEN
705 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_EXCEPTION,L_MODULE_NAME,
706 'EXCEPTION :'||sqlerrm);
707 END IF;
708
709 IF l_cashflow_csr%ISOPEN THEN
710 CLOSE l_cashflow_csr;
711 END IF;
712 IF l_kheaders_csr%ISOPEN THEN
713 CLOSE l_kheaders_csr;
714 END IF;
715 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
716 -- unexpecetd error
717 OKL_API.set_message(p_app_name => g_app_name,
718 p_msg_name => g_unexpected_error,
719 p_token1 => g_sqlcode_token,
720 p_token1_value => sqlcode,
721 p_token2 => g_sqlerrm_token,
722 p_token2_value => sqlerrm);
723 END create_cash_flows;
724
725 /*========================================================================
726 | PRIVATE PROCEDURE get_current_payments
727 |
728 | DESCRIPTION
729 | This procedure queries the current payments and populates the payment structures
730 |
731 | CALLED FROM PROCEDURES/FUNCTIONS
732 | calc_quote_payments,
733 |
734 | CALLS PROCEDURES/FUNCTIONS
735 | create_cash_flow_object, create_cash_flows
736 |
737 | PARAMETERS
738 | p_quote_id IN Quote ID
739 | p_khr_id IN Contract ID
740 |
741 | KNOWN ISSUES
742 |
743 | NOTES
744 |
745 |
746 | MODIFICATION HISTORY
747 | Date Author Description of Changes
748 | 14-OCT-2003 SECHAWLA Created
749 | 29-SEP-2004 pagarg Bug #3921591: Added the logic to obtain the
750 | current payments for fee asset line.
751 | 22-Apr-2005 PAGARG Bug 4299668 Instead of calling insert for each quote
752 | cash flow object, prepare table of records and finally
753 | call bulk insert for all four objects
754 *=======================================================================*/
755 PROCEDURE get_current_payments(
756 p_api_version IN NUMBER,
757 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
758 x_return_status OUT NOCOPY VARCHAR2,
759 x_msg_count OUT NOCOPY NUMBER,
760 x_msg_data OUT NOCOPY VARCHAR2,
761 p_quote_id IN NUMBER,
762 p_khr_id IN NUMBER) AS
763
764 /*-----------------------------------------------------------------------+
765 | Cursor Declarations |
766 +-----------------------------------------------------------------------*/
767
768 -- get the current contract level payments
769 CURSOR l_kpayments_csr(cp_chr_id IN NUMBER) IS
770 SELECT rgp.cle_id cle_id,
771 sttyp.id1 sty_id,
772 sttyp.code stream_type,
773 tuom.id1 frequency,
774 sll_rul.rule_information1 seq_num,
775 sll_rul.rule_information2 start_date,
776 sll_rul.rule_information3 period_in_months,
777 sll_rul.rule_information5 advance_periods,
778 sll_rul.rule_information6 amount,
779 sll_rul.rule_information10 due_arrears_yn,
780 sll_rul.rule_information7 stub_days,
781 sll_rul.rule_information8 stub_amount,
782 rgp.dnz_chr_id
783 FROM okl_time_units_v tuom,
784 okc_rules_b sll_rul,
785 okl_strmtyp_source_v sttyp,
786 okc_rules_b slh_rul,
787 okc_rule_groups_b rgp
788 WHERE tuom.id1 = sll_rul.object1_id1
789 AND sll_rul.object2_id1 = to_char(slh_rul.id)
790 AND sll_rul.rgp_id = rgp.id
791 AND sll_rul.rule_information_category = 'LASLL'
792 AND sttyp.id1 = slh_rul.object1_id1
793 AND slh_rul.rgp_id = rgp.id
794 AND slh_rul.rule_information_category = 'LASLH'
795 AND rgp.rgd_code = 'LALEVL'
796 AND rgp.dnz_chr_id = cp_chr_id
797 AND rgp.cle_id IS NULL
798 ORDER BY stream_type, start_date;
799
800 -- Get all the asset, service and fee lines attached to the contract
801 -- These lines may or may not have payments associated with them
802 CURSOR l_okcklines_csr(cp_chr_id IN NUMBER) IS
803 SELECT cle.id, cle.lse_id, lse.lty_code
804 FROM okc_k_lines_b cle, okc_line_styles_b lse
805 WHERE cle.lse_id = lse.id
806 AND cle.sts_code IN ('BOOKED', 'TERMINATED')
807 AND chr_id = cp_chr_id;
808
809 --This cursor returns the payments associated with an Asset/Service/Fee Line (If Any)
810
811 -- Get the current Line Level payments
812 CURSOR l_lpayments_csr(cp_cle_id IN NUMBER) IS
813 SELECT rgp.cle_id cle_id,
814 sttyp.id1 sty_id,
815 sttyp.code stream_type,
816 tuom.id1 frequency,
817 sll_rul.rule_information1 seq_num,
818 sll_rul.rule_information2 start_date,
819 sll_rul.rule_information3 period_in_months,
820 sll_rul.rule_information5 advance_periods,
821 sll_rul.rule_information6 amount,
822 sll_rul.rule_information10 due_arrears_yn,
823 sll_rul.rule_information7 stub_days,
824 sll_rul.rule_information8 stub_amount,
825 rgp.dnz_chr_id
826 FROM okl_time_units_v tuom,
827 okc_rules_b sll_rul,
828 okl_strmtyp_source_v sttyp,
829 okc_rules_b slh_rul,
830 okc_rule_groups_b rgp
831 WHERE tuom.id1 = sll_rul.object1_id1
832 AND sll_rul.object2_id1 = to_char(slh_rul.id)
833 AND sll_rul.rgp_id = rgp.id
834 AND sll_rul.rule_information_category = 'LASLL'
835 AND sttyp.id1 = slh_rul.object1_id1
836 AND slh_rul.rgp_id = rgp.id
837 AND slh_rul.rule_information_category = 'LASLH'
838 AND rgp.rgd_code = 'LALEVL'
839 AND rgp.cle_id = cp_cle_id
840 ORDER BY stream_type, start_date;
841
842 --Bug #3921591: pagarg +++ Rollover +++
843 -- Modified the cursor to get assets for any given line type.
844 -- Get the assets associated with the given line type
845 CURSOR l_lineassets_csr(cp_line_id IN NUMBER, cp_line_type_code IN VARCHAR2) IS
846 SELECT cim.object1_id1, cle.id
847 FROM okc_k_lines_b cle, okc_line_styles_b lse, okc_k_items cim
848 WHERE cle.lse_id = lse.id
849 AND lse.lty_code = cp_line_type_code
850 AND cim.cle_id = cle.id
851 AND cle.cle_id = cp_line_id;
852
853 /*-----------------------------------------------------------------------+
854 | Subype Declarations |
855 +-----------------------------------------------------------------------*/
856
857 SUBTYPE cfov_rec_type IS okl_cash_flow_objects_pub.cfov_rec_type;
858 SUBTYPE cafv_rec_type IS okl_cash_flows_pub.cafv_rec_type;
859 SUBTYPE cflv_rec_type IS okl_cash_flow_levels_pub.cflv_rec_type;
860 SUBTYPE qcov_rec_type IS okl_trx_qte_cf_objects_pub.qcov_rec_type;
861
862 /*-----------------------------------------------------------------------+
863 | Local Variable Declarations and initializations |
864 +-----------------------------------------------------------------------*/
865 lp_cfov_rec cfov_rec_type;
866 lx_cfov_rec cfov_rec_type;
867
868 lp_cafv_rec cafv_rec_type;
869 lx_cafv_rec cafv_rec_type;
870
871 lp_cflv_rec cflv_rec_type;
872 lx_cflv_rec cflv_rec_type;
873
874 lp_qcov_rec qcov_rec_type;
875 lx_qcov_rec qcov_rec_type;
876
877 lp_cashflow_rec cashflow_rec_type;
878
879 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
880
881 l_pymt_count NUMBER := 0;
882 l_sys_date DATE;
883 l_api_version CONSTANT NUMBER := 1;
884 l_cfo_id NUMBER;
885 l_dummy VARCHAR2(1) := '?';
886 l_oty_code VARCHAR2(30);
887
888 lx_new_cash_flow VARCHAR2(1);
889
890 l_prev_sty_id NUMBER := -99;
891 L_MODULE_NAME VARCHAR2(500) := G_MODULE_NAME||'get_current_payments';
892 is_debug_exception_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_EXCEPTION);
893 is_debug_procedure_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_PROCEDURE);
894 is_debug_statement_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_STATEMENT);
895 BEGIN
896
897 IF (is_debug_procedure_on) THEN
898 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'Begin(+)');
899 END IF;
900
901 --Print Input Variables
902 IF (is_debug_statement_on) THEN
903 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
904 'p_quote_id :'||p_quote_id);
905 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
906 'p_khr_id :'||p_khr_id );
907
908 END IF;
909
910 ------------------------- Get contract level payments------------------------------
911 l_pymt_Count := 0;
912
913 --loop thru all the payments for this contract, create one cash flow object, create cash flows for each stream type,
914 -- create cash flow levels for each payment record
915 FOR l_kpayments_rec IN l_kpayments_csr(p_khr_id) LOOP
916
917 l_pymt_count := l_pymt_count + 1;
918
919 IF l_pymt_count = 1 THEN -- K level payments exist, create a K Object
920
921 create_cash_flow_object(p_api_version => p_api_version,
922 x_msg_count => x_msg_count,
923 x_msg_data => x_msg_data,
924 p_obj_type_code => G_CONTRACT_OBJ_TYPE,
925 p_src_table => G_OBJECT_SRC_TABLE,
926 p_src_id => p_quote_id,
927 p_base_src_id => p_khr_id,
928 p_sts_code => G_CURRENT_STATUS,
929 x_cfo_id => l_cfo_id,
930 x_return_status => l_return_status);
931
932 IF (is_debug_statement_on) THEN
933 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
934 'after call to create_cash_flow_object :'||l_return_status);
935 END IF;
936
937 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
938 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
939 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
940 RAISE OKL_API.G_EXCEPTION_ERROR;
941 END IF;
942
943 -- Store Objects in OKL_TRX_QTE_CF_OBJECTS
944
945 lp_qcov_rec.qte_id := p_quote_id;
946 lp_qcov_rec.cfo_id := l_cfo_id;
947 lp_qcov_rec.BASE_SOURCE_ID := p_khr_id;
948 --Bug 4299668 PAGARG Instead of calling the procedure to insert each
949 --quote cash flow object, prepare table of records
950 --**START**--
951 g_qcov_counter := g_qcov_counter + 1;
952 g_qcov_tbl_type(g_qcov_counter) := lp_qcov_rec;
953 --**END 4299668**--
954
955 END IF;
956
957 IF l_kpayments_rec.sty_id <> l_prev_sty_id THEN
958 lx_new_cash_flow := 'N';
959 l_prev_sty_id := l_kpayments_rec.sty_id;
960 END IF;
961
962 lp_cashflow_rec.p_cfo_id := l_cfo_id;
963 lp_cashflow_rec.p_sts_code := G_CURRENT_STATUS;
964 lp_cashflow_rec.p_sty_id := l_kpayments_rec.sty_id;
965 lp_cashflow_rec.p_due_arrears_yn := l_kpayments_rec.due_arrears_yn;
966 lp_cashflow_rec.p_start_date := to_date(l_kpayments_rec.start_date,'yyyy/mm/dd hh24:mi:ss');
967 lp_cashflow_rec.p_advance_periods := to_number(l_kpayments_rec.advance_periods);
968 lp_cashflow_rec.p_khr_id := p_khr_id;
969 lp_cashflow_rec.p_quote_id := p_quote_id;
970 lp_cashflow_rec.p_amount := l_kpayments_rec.amount;
971 lp_cashflow_rec.p_period_in_months := l_kpayments_rec.period_in_months;
972 lp_cashflow_rec.p_frequency := l_kpayments_rec.frequency;
973 lp_cashflow_rec.p_seq_num := to_number(l_kpayments_rec.seq_num);
974 lp_cashflow_rec.p_stub_days := l_kpayments_rec.stub_days;
975 lp_cashflow_rec.p_stub_amount := l_kpayments_rec.stub_amount;
976
977 create_cash_flows( p_api_version => p_api_version,
978 x_msg_count => x_msg_count,
979 x_msg_data => x_msg_data,
980 p_cashflow_rec => lp_cashflow_rec,
981 px_new_cash_flow => lx_new_cash_flow,
982 x_return_status => l_return_status);
983
984 IF (is_debug_statement_on) THEN
985 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
986 'after call to create_cash_flows :'||l_return_status);
987 END IF;
988
989 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
990 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
991 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
992 RAISE OKL_API.G_EXCEPTION_ERROR;
993 END IF;
994
995 END LOOP;
996
997 --------------end current contract level payments ----------------------
998
999
1000 --------------get curent line level payments ---------------------------
1001 -- get all the contract lines
1002 FOR l_okcklines_rec IN l_okcklines_csr(p_khr_id) LOOP
1003
1004 l_prev_sty_id := -99;
1005
1006 l_pymt_Count := 0;
1007
1008 -- get the line level payemnts, create cash flow object, cash flows and cash flow levels
1009 FOR l_lpayments_rec IN l_lpayments_csr(l_okcklines_rec.id) LOOP
1010 l_pymt_Count := l_pymt_Count + 1;
1011
1012
1013 IF l_pymt_count = 1 THEN -- line level payments exist
1014
1015 l_cfo_id := NULL;
1016 IF l_okcklines_rec.lty_code = 'FREE_FORM1' THEN
1017 l_oty_code := G_FIN_ASSET_OBJ_TYPE;
1018 ELSIF l_okcklines_rec.lty_code = 'SOLD_SERVICE' THEN
1019 l_oty_code := G_SERVICE_LINE_OBJ_TYPE;
1020 ELSIF l_okcklines_rec.lty_code = 'FEE' THEN
1021 l_oty_code := G_FEE_LINE_OBJ_TYPE;
1022 END IF;
1023
1024 create_cash_flow_object(
1025 p_api_version => p_api_version,
1026 x_msg_count => x_msg_count,
1027 x_msg_data => x_msg_data,
1028 p_obj_type_code => l_oty_code,
1029 p_src_table => G_OBJECT_SRC_TABLE,
1030 p_src_id => p_quote_id,
1031 p_base_src_id => l_okcklines_rec.id,
1032 p_sts_code => G_CURRENT_STATUS,
1033 x_cfo_id => l_cfo_id,
1034 x_return_status => l_return_status);
1035
1036 IF (is_debug_statement_on) THEN
1037 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
1038 'after call to create_cash_flow_object :'||l_return_status);
1039 END IF;
1040
1041
1042 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1043 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1044 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1045 RAISE OKL_API.G_EXCEPTION_ERROR;
1046 END IF;
1047
1048 lp_qcov_rec.qte_id := p_quote_id;
1049 lp_qcov_rec.cfo_id := l_cfo_id;
1050 lp_qcov_rec.BASE_SOURCE_ID := l_okcklines_rec.id;
1051 --Bug 4299668 PAGARG Instead of calling the procedure to insert
1052 --each quote cash flow object, prepare table of records
1053 --**START**--
1054 g_qcov_counter := g_qcov_counter + 1;
1055 g_qcov_tbl_type(g_qcov_counter) := lp_qcov_rec;
1056 --**END 4299668**--
1057 END IF;
1058
1059 IF l_lpayments_rec.sty_id <> l_prev_sty_id THEN
1060 lx_new_cash_flow := 'N';
1061 l_prev_sty_id := l_lpayments_rec.sty_id;
1062 END IF;
1063
1064 lp_cashflow_rec.p_cfo_id := l_cfo_id;
1065 lp_cashflow_rec.p_sts_code := G_CURRENT_STATUS;
1066 lp_cashflow_rec.p_sty_id := l_lpayments_rec.sty_id;
1067 lp_cashflow_rec.p_due_arrears_yn := l_lpayments_rec.due_arrears_yn;
1068 lp_cashflow_rec.p_start_date := to_date(l_lpayments_rec.start_date,'yyyy/mm/dd hh24:mi:ss');
1069 lp_cashflow_rec.p_advance_periods := to_number(l_lpayments_rec.advance_periods);
1070 lp_cashflow_rec.p_khr_id := p_khr_id;
1071 lp_cashflow_rec.p_quote_id := p_quote_id;
1072 lp_cashflow_rec.p_amount := l_lpayments_rec.amount;
1073 lp_cashflow_rec.p_period_in_months := l_lpayments_rec.period_in_months;
1074 lp_cashflow_rec.p_frequency := l_lpayments_rec.frequency;
1075 lp_cashflow_rec.p_seq_num := to_number(l_lpayments_rec.seq_num);
1076 lp_cashflow_rec.p_stub_days := l_lpayments_rec.stub_days;
1077 lp_cashflow_rec.p_stub_amount := l_lpayments_rec.stub_amount;
1078
1079 create_cash_flows( p_api_version => p_api_version,
1080 x_msg_count => x_msg_count,
1081 x_msg_data => x_msg_data,
1082 p_cashflow_rec => lp_cashflow_rec,
1083 px_new_cash_flow => lx_new_cash_flow,
1084 x_return_status => l_return_status);
1085
1086 IF (is_debug_statement_on) THEN
1087 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
1088 'after call to create_cash_flows :'||l_return_status);
1089 END IF;
1090
1091 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1092 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1093 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1094 RAISE OKL_API.G_EXCEPTION_ERROR;
1095 END IF;
1096 END LOOP;
1097
1098 ------------------------Get Serviced Asset Line level payments --------------------------
1099 -- If the current line is a service line, then also get the subline payments
1100 IF l_okcklines_rec.lty_code = 'SOLD_SERVICE' THEN
1101
1102 l_prev_sty_id := -99;
1103
1104 --Bug #3921591: pagarg +++ Rollover +++
1105 --Modified the cursor call to pass line type also
1106 -- get the financial assets associated with the service line
1107 FOR l_servicelineassets_rec IN l_lineassets_csr(l_okcklines_rec.id, G_LINKED_SERVICE_LINE_TYPE) LOOP
1108 l_pymt_Count := 0;
1109 -- get the payments associated with the sub lines of the service line(serviced assets)
1110 FOR l_lpayments_rec IN l_lpayments_csr(l_servicelineassets_rec.id) LOOP
1111 l_pymt_Count := l_pymt_Count + 1;
1112
1113 IF l_pymt_count = 1 THEN -- line level payments exist
1114 create_cash_flow_object(
1115 p_api_version => p_api_version,
1116 x_msg_count => x_msg_count,
1117 x_msg_data => x_msg_data,
1118 p_obj_type_code => G_SERV_ASSET_OBJ_TYPE,
1119 p_src_table => G_OBJECT_SRC_TABLE,
1120 p_src_id => p_quote_id,
1121 p_base_src_id => l_servicelineassets_rec.id,
1122 p_sts_code => G_CURRENT_STATUS,
1123 x_cfo_id => l_cfo_id,
1124 x_return_status => l_return_status);
1125
1126 IF (is_debug_statement_on) THEN
1127 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
1128 'after call to create_cash_flow_object :'||l_return_status);
1129 END IF;
1130 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1131 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1132 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1133 RAISE OKL_API.G_EXCEPTION_ERROR;
1134 END IF;
1135
1136 lp_qcov_rec.qte_id := p_quote_id;
1137 lp_qcov_rec.cfo_id := l_cfo_id;
1138 lp_qcov_rec.BASE_SOURCE_ID := l_servicelineassets_rec.id;
1139 --Bug 4299668 PAGARG Instead of calling the procedure to
1140 --insert quote cash flow object, store the record in the table
1141 --**START**--
1142 g_qcov_counter := g_qcov_counter + 1;
1143 g_qcov_tbl_type(g_qcov_counter) := lp_qcov_rec;
1144 --**END 4299668**--
1145
1146 END IF;
1147
1148 IF l_lpayments_rec.sty_id <> l_prev_sty_id THEN
1149 lx_new_cash_flow := 'N';
1150 l_prev_sty_id := l_lpayments_rec.sty_id;
1151 END IF;
1152
1153 lp_cashflow_rec.p_cfo_id := l_cfo_id;
1154 lp_cashflow_rec.p_sts_code := G_CURRENT_STATUS;
1155 lp_cashflow_rec.p_sty_id := l_lpayments_rec.sty_id;
1156 lp_cashflow_rec.p_due_arrears_yn := l_lpayments_rec.due_arrears_yn;
1157 lp_cashflow_rec.p_start_date := to_date(l_lpayments_rec.start_date,'yyyy/mm/dd hh24:mi:ss');
1158 lp_cashflow_rec.p_advance_periods := to_number(l_lpayments_rec.advance_periods);
1159 lp_cashflow_rec.p_khr_id := p_khr_id;
1160 lp_cashflow_rec.p_quote_id := p_quote_id;
1161 lp_cashflow_rec.p_amount := l_lpayments_rec.amount;
1162 lp_cashflow_rec.p_period_in_months := l_lpayments_rec.period_in_months;
1163 lp_cashflow_rec.p_frequency := l_lpayments_rec.frequency;
1164 lp_cashflow_rec.p_seq_num := to_number(l_lpayments_rec.seq_num);
1165 lp_cashflow_rec.p_stub_days := l_lpayments_rec.stub_days;
1166 lp_cashflow_rec.p_stub_amount := l_lpayments_rec.stub_amount;
1167
1168 create_cash_flows( p_api_version => p_api_version,
1169 x_msg_count => x_msg_count,
1170 x_msg_data => x_msg_data,
1171 p_cashflow_rec => lp_cashflow_rec,
1172 px_new_cash_flow => lx_new_cash_flow,
1173 x_return_status => l_return_status);
1174
1175 IF (is_debug_statement_on) THEN
1176 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
1177 'after call to create_cash_flows :'||l_return_status);
1178 END IF;
1179 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1180 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1181 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1182 RAISE OKL_API.G_EXCEPTION_ERROR;
1183 END IF;
1184
1185 END LOOP;
1186 END LOOP;
1187 ----
1188 END IF;
1189
1190 --Bug #3921591: pagarg +++ Rollover +++++++ Start ++++++++++
1191 --------------------- Get Fee Asset Line level payments -----------------
1192 -- If the current line is a fee line, then also get the subline payments
1193 IF l_okcklines_rec.lty_code = 'FEE'
1194 THEN
1195 l_prev_sty_id := -99;
1196 -- get the financial assets associated with the fee line
1197 FOR l_feelineassets_rec IN l_lineassets_csr(l_okcklines_rec.id,
1198 G_LINKED_FEE_LINE_TYPE)
1199 LOOP
1200 l_pymt_Count := 0;
1201 -- get the payments associated with the sub lines of the fee line
1202 FOR l_lpayments_rec IN l_lpayments_csr(l_feelineassets_rec.id)
1203 LOOP
1204 l_pymt_Count := l_pymt_Count + 1;
1205 IF l_pymt_count = 1 THEN -- line level payments exist
1206 create_cash_flow_object(
1207 p_api_version => p_api_version,
1208 x_msg_count => x_msg_count,
1209 x_msg_data => x_msg_data,
1210 p_obj_type_code => G_FEE_ASSET_OBJ_TYPE,
1211 p_src_table => G_OBJECT_SRC_TABLE,
1212 p_src_id => p_quote_id,
1213 p_base_src_id => l_feelineassets_rec.id,
1214 p_sts_code => G_CURRENT_STATUS,
1215 x_cfo_id => l_cfo_id,
1216 x_return_status => l_return_status);
1217
1218 IF (is_debug_statement_on) THEN
1219 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
1220 'after call to create_cash_flow_object :'||l_return_status);
1221 END IF;
1222
1223 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR)THEN
1224 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1225 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1226 RAISE OKL_API.G_EXCEPTION_ERROR;
1227 END IF;
1228 lp_qcov_rec.qte_id := p_quote_id;
1229 lp_qcov_rec.cfo_id := l_cfo_id;
1230 lp_qcov_rec.BASE_SOURCE_ID := l_feelineassets_rec.id;
1231 --Bug 4299668 PAGARG Instead of calling the procedure to insert
1232 --quote cash flow object, store the record in the table
1233 --**START**--
1234 g_qcov_counter := g_qcov_counter + 1;
1235 g_qcov_tbl_type(g_qcov_counter) := lp_qcov_rec;
1236 --**END 4299668**--
1237 END IF;
1238 -- End of creation of cash flow object
1239
1240 -- Creation of cash flows for each payment
1241 IF l_lpayments_rec.sty_id <> l_prev_sty_id THEN
1242 lx_new_cash_flow := 'N';
1243 l_prev_sty_id := l_lpayments_rec.sty_id;
1244 END IF;
1245 lp_cashflow_rec.p_cfo_id := l_cfo_id;
1246 lp_cashflow_rec.p_sts_code := G_CURRENT_STATUS;
1247 lp_cashflow_rec.p_sty_id := l_lpayments_rec.sty_id;
1248 lp_cashflow_rec.p_due_arrears_yn := l_lpayments_rec.due_arrears_yn;
1249 lp_cashflow_rec.p_start_date := to_date(l_lpayments_rec.start_date,'yyyy/mm/dd hh24:mi:ss');
1250 lp_cashflow_rec.p_advance_periods := to_number(l_lpayments_rec.advance_periods);
1251 lp_cashflow_rec.p_khr_id := p_khr_id;
1252 lp_cashflow_rec.p_quote_id := p_quote_id;
1253 lp_cashflow_rec.p_amount := l_lpayments_rec.amount;
1254 lp_cashflow_rec.p_period_in_months := l_lpayments_rec.period_in_months;
1255 lp_cashflow_rec.p_frequency := l_lpayments_rec.frequency;
1256 lp_cashflow_rec.p_seq_num := to_number(l_lpayments_rec.seq_num);
1257 lp_cashflow_rec.p_stub_days := l_lpayments_rec.stub_days;
1258 lp_cashflow_rec.p_stub_amount := l_lpayments_rec.stub_amount;
1259 create_cash_flows(
1260 p_api_version => p_api_version,
1261 x_msg_count => x_msg_count,
1262 x_msg_data => x_msg_data,
1263 p_cashflow_rec => lp_cashflow_rec,
1264 px_new_cash_flow => lx_new_cash_flow,
1265 x_return_status => l_return_status);
1266
1267 IF (is_debug_statement_on) THEN
1268 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
1269 'after call to create_cash_flows :'||l_return_status);
1270 END IF;
1271 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1272 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1273 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1274 RAISE OKL_API.G_EXCEPTION_ERROR;
1275 END IF;
1276 END LOOP;
1277 END LOOP;
1278 END IF;
1279 --Bug #3921591: pagarg +++ Rollover +++++++ End ++++++++++
1280
1281 END LOOP;
1282 --------------------end get current line level payments -----------------------
1283
1284 --Bug 4299668 PAGARG All the four object table of records is populated for
1285 --current payment. Now call proceure for bulk insert.
1286 --**START**--
1287 okl_cfo_pvt.insert_row_bulk(p_api_version => p_api_version,
1288 p_init_msg_list => OKL_API.G_FALSE,
1289 x_return_status => l_return_status,
1290 x_msg_count => x_msg_count,
1291 x_msg_data => x_msg_data,
1292 p_cfov_tbl => g_cfov_tbl_type,
1293 x_cfov_tbl => gx_cfov_tbl_type);
1294
1295 IF (is_debug_statement_on) THEN
1296 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
1297 'after call to okl_cfo_pvt.insert_row_bulk :'||l_return_status);
1298 END IF;
1299
1300 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1301 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1302 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1303 RAISE OKL_API.G_EXCEPTION_ERROR;
1304 END IF;
1305
1306 OKL_QCO_PVT.insert_row_bulk(p_api_version => p_api_version,
1307 p_init_msg_list => OKL_API.G_FALSE,
1308 x_return_status => l_return_status,
1309 x_msg_count => x_msg_count,
1310 x_msg_data => x_msg_data,
1311 p_qcov_tbl => g_qcov_tbl_type,
1312 x_qcov_tbl => gx_qcov_tbl_type);
1313
1314 IF (is_debug_statement_on) THEN
1315 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
1316 'after call to okl_cfo_pvt.insert_row_bulk :'||l_return_status);
1317 END IF;
1318
1319 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1320 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1321 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1322 RAISE OKL_API.G_EXCEPTION_ERROR;
1323 END IF;
1324
1325 okl_caf_pvt.insert_row_bulk(p_api_version => p_api_version,
1326 p_init_msg_list => OKL_API.G_FALSE,
1327 x_return_status => l_return_status,
1328 x_msg_count => x_msg_count,
1329 x_msg_data => x_msg_data,
1330 p_cafv_tbl => g_cafv_tbl_type,
1331 x_cafv_tbl => gx_cafv_tbl_type);
1332
1333 IF (is_debug_statement_on) THEN
1334 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
1335 'after call to okl_cfo_pvt.insert_row_bulk :'||l_return_status);
1336 END IF;
1337
1338 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1339 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1340 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1341 RAISE OKL_API.G_EXCEPTION_ERROR;
1342 END IF;
1343
1344 OKL_CFL_PVT.insert_row_bulk(p_api_version => p_api_version,
1345 p_init_msg_list => OKL_API.G_FALSE,
1346 x_return_status => l_return_status,
1347 x_msg_count => x_msg_count,
1348 x_msg_data => x_msg_data,
1349 p_cflv_tbl => g_cflv_tbl_type,
1350 x_cflv_tbl => gx_cflv_tbl_type);
1351
1352 IF (is_debug_statement_on) THEN
1353 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
1354 'after call to okl_cfo_pvt.insert_row_bulk :'||l_return_status);
1355 END IF;
1356
1357 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1358 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1359 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1360 RAISE OKL_API.G_EXCEPTION_ERROR;
1361 END IF;
1362 --**END 4299668**--
1363
1364 -- set the return status and out variables
1365 x_return_status := l_return_status;
1366
1367 IF (is_debug_procedure_on) THEN
1368 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'End(-)');
1369 END IF;
1370 EXCEPTION
1371 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1372 IF (is_debug_exception_on) THEN
1373 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_EXCEPTION,L_MODULE_NAME,
1374 'EXCEPTION :'||'OKL_API.G_EXCEPTION_ERROR');
1375 END IF;
1376
1377
1378 IF l_kpayments_csr%ISOPEN THEN
1379 CLOSE l_kpayments_csr;
1380 END IF;
1381 IF l_okcklines_csr%ISOPEN THEN
1382 CLOSE l_okcklines_csr;
1383 END IF;
1384 IF l_lpayments_csr%ISOPEN THEN
1385 CLOSE l_lpayments_csr;
1386 END IF;
1387
1388 --Bug #3921591: pagarg +++ Rollover +++
1389 -- Changed the cursor name as made it generalised
1390 IF l_lineassets_csr%ISOPEN THEN
1391 CLOSE l_lineassets_csr;
1392 END IF;
1393 x_return_status := OKL_API.G_RET_STS_ERROR;
1394 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1395 IF (is_debug_exception_on) THEN
1396 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_EXCEPTION,L_MODULE_NAME,
1397 'EXCEPTION :'||'OKL_API.G_EXCEPTION_UNEXPECTED_ERROR');
1398 END IF;
1399
1400 IF l_kpayments_csr%ISOPEN THEN
1401 CLOSE l_kpayments_csr;
1402 END IF;
1403 IF l_okcklines_csr%ISOPEN THEN
1404 CLOSE l_okcklines_csr;
1405 END IF;
1406 IF l_lpayments_csr%ISOPEN THEN
1407 CLOSE l_lpayments_csr;
1408 END IF;
1409 --Bug #3921591: pagarg +++ Rollover +++
1410 -- Changed the cursor name as made it generalised
1411 IF l_lineassets_csr%ISOPEN THEN
1412 CLOSE l_lineassets_csr;
1413 END IF;
1414 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
1415 WHEN OTHERS THEN
1416 IF (is_debug_exception_on) THEN
1417 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_EXCEPTION,L_MODULE_NAME,
1418 'EXCEPTION :'||sqlerrm);
1419 END IF;
1420
1421 IF l_kpayments_csr%ISOPEN THEN
1422 CLOSE l_kpayments_csr;
1423 END IF;
1424 IF l_okcklines_csr%ISOPEN THEN
1425 CLOSE l_okcklines_csr;
1426 END IF;
1427 IF l_lpayments_csr%ISOPEN THEN
1428 CLOSE l_lpayments_csr;
1429 END IF;
1430 --Bug #3921591: pagarg +++ Rollover +++
1431 -- Changed the cursor name as made it generalised
1432 IF l_lineassets_csr%ISOPEN THEN
1433 CLOSE l_lineassets_csr;
1434 END IF;
1435 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
1436 -- unexpected error
1437 OKL_API.set_message(p_app_name => g_app_name,
1438 p_msg_name => g_unexpected_error,
1439 p_token1 => g_sqlcode_token,
1440 p_token1_value => sqlcode,
1441 p_token2 => g_sqlerrm_token,
1442 p_token2_value => sqlerrm);
1443
1444 END get_current_payments;
1445
1446 /*========================================================================
1447 | PRIVATE PROCEDURE calc_prop_line_payments
1448 |
1449 | DESCRIPTION
1450 | This procedure calculates the proposed payments of asset, service and service sublines
1451 |
1452 | CALLED FROM PROCEDURES/FUNCTIONS
1453 | calc_proposed_payments,
1454 |
1455 | CALLS PROCEDURES/FUNCTIONS
1456 | create_cash_flow_object, create_cash_flows
1457 |
1458 | PARAMETERS
1459 | p_curr_cfo_id IN Current Cashflow ID
1460 | p_prop_obj_type_code IN Proposed Object Type
1461 | p_prop_base_source_id IN base source ID of the proposed object
1462 | p_prorate_ratio IN Prorate Ratio
1463 | p_date_eff_from IN quote effective date
1464 | p_quote_id IN Quote ID
1465 | p_khr_id IN Contract ID
1466 |
1467 | KNOWN ISSUES
1468 |
1469 | NOTES
1470 |
1471 |
1472 | MODIFICATION HISTORY
1473 | Date Author Description of Changes
1474 | 14-OCT-2003 SECHAWLA Created
1475 | 20-SEP-2004 SECHAWLA 3816891 Modified the payment
1476 | calculation for Arrears
1477 *=======================================================================*/
1478 PROCEDURE calc_prop_line_payments( p_api_version IN NUMBER,
1479 x_msg_count OUT NOCOPY NUMBER,
1480 x_msg_data OUT NOCOPY VARCHAR2,
1481 p_curr_cfo_id IN NUMBER,
1482 p_prop_obj_type_code IN VARCHAR2,
1483 p_prop_base_source_id IN NUMBER,
1484 p_prorate_ratio IN NUMBER,
1485 p_date_eff_from IN DATE,
1486 p_quote_id IN NUMBER,
1487 p_khr_id IN NUMBER,
1488 x_return_status OUT NOCOPY VARCHAR2) IS
1489
1490 /*-----------------------------------------------------------------------+
1491 | Cursor Declarations |
1492 +-----------------------------------------------------------------------*/
1493 -- Get the current cashflows
1494 CURSOR l_cashflows_csr(cp_cfo_id IN NUMBER) IS
1495 -- SECHAWLA 20-SEP-04 3816891 : added nvl for due_arrears_yn
1496 SELECT caf.id, caf.sty_id, nvl(caf.due_arrears_yn, 'N') due_arrears_yn, cfl.start_date, caf.number_of_advance_periods,
1497 cfl.amount, cfl.number_of_periods, cfl.fqy_code, cfl.level_sequence, cfl.stub_days, cfl.stub_amount
1498 FROM okl_cash_flows caf, okl_cash_flow_levels cfl
1499 WHERE cfo_id = cp_cfo_id
1500 AND caf.id = cfl.caf_id
1501 AND caf.sts_code = G_CURRENT_STATUS
1502 AND caf.cft_code = G_CASH_FLOW_TYPE
1503 ORDER BY caf.sty_id, cfl.start_date;
1504
1505
1506 -- Get the start date of the last period
1507 CURSOR l_lastperiodstatdt_csr(cp_firstperiodstartdt IN DATE, cp_number_of_months IN NUMBER) IS
1508 --SELECT add_months(cp_firstperiodstartdt,cp_number_of_months)
1509 --FROM DUAL;
1510 -- vsgandhi bug 14838755 :03-dec-2012
1511 SELECT okl_lla_util_pvt.calculate_end_date
1512 (cp_firstperiodstartdt,
1513 cp_number_of_months,
1514 to_char(cp_firstperiodstartdt, 'DD') ) +1
1515 FROM dual;
1516
1517
1518 -- Get the number of months between period start dt and quote eff date
1519 CURSOR l_monthsuptodate_csr(cp_quote_eff_dt IN DATE, cp_period_start_dt IN DATE) IS
1520 SELECT months_between(cp_quote_eff_dt,cp_period_start_dt)
1521 FROM DUAL;
1522
1523 /*-----------------------------------------------------------------------+
1524 | SubType Declarations
1525 +-----------------------------------------------------------------------*/
1526
1527 SUBTYPE qcov_rec_type IS okl_trx_qte_cf_objects_pub.qcov_rec_type;
1528
1529 /*-----------------------------------------------------------------------+
1530 | Local Variable Declarations and initializations |
1531 +-----------------------------------------------------------------------*/
1532
1533 l_cfo_id NUMBER;
1534 l_pymt_Count NUMBER;
1535 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1536 l_prev_sty_id NUMBER := -99;
1537
1538 l_lowest_level_seq NUMBER;
1539 l_number_of_months NUMBER;
1540 lx_new_cash_flow VARCHAR2(1);
1541 l_split_level VARCHAR2(1);
1542 l_months_between NUMBER;
1543 l_new_periods NUMBER ;
1544 l_new_stub_days NUMBER ;
1545 l_first_period_start_date DATE;
1546 l_remaining_periods NUMBER ;
1547 l_remaining_stub_days NUMBER ;
1548
1549 l_new_amount NUMBER;
1550 l_new_stub_amount NUMBER;
1551 l_new_seq NUMBER;
1552 l_curr_level_start_date DATE;
1553 l_next_level_start_date DATE;
1554
1555 lp_qcov_rec qcov_rec_type;
1556 lx_qcov_rec qcov_rec_type;
1557
1558 lp_cashflow_rec cashflow_rec_type;
1559
1560 --SECHAWLA 20-SEP-04 3816891 : new declaration
1561 l_months_to_check_last_day NUMBER;
1562
1563 L_MODULE_NAME VARCHAR2(500) := G_MODULE_NAME||'calc_prop_line_payments';
1564 is_debug_exception_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_EXCEPTION);
1565 is_debug_procedure_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_PROCEDURE);
1566 is_debug_statement_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_STATEMENT);
1567 BEGIN
1568 IF (is_debug_procedure_on) THEN
1569 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'Begin(+)');
1570 END IF;
1571
1572 --Print Input Variables
1573 IF (is_debug_statement_on) THEN
1574 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
1575 'p_curr_cfo_id :'||p_curr_cfo_id);
1576 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
1577 'p_prop_obj_type_code :'||p_prop_obj_type_code);
1578 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
1579 'p_prop_base_source_id :'||p_prop_base_source_id);
1580 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
1581 'p_prorate_ratio :'||p_prorate_ratio);
1582 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
1583 'p_date_eff_from :'||p_date_eff_from);
1584 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
1585 'p_quote_id :'||p_quote_id);
1586 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
1587 'p_khr_id :'||p_khr_id);
1588 END IF;
1589
1590 l_pymt_Count := 0;
1591 -- Get the cash flows for the line, create cash flows and cash flow levels
1592 FOR l_cashflows_rec IN l_cashflows_csr(p_curr_cfo_id) LOOP
1593 l_pymt_Count := l_pymt_Count + 1;
1594 IF l_pymt_count = 1 THEN -- current asset line level payments exist
1595 -- create new line object
1596 l_cfo_id := NULL;
1597 create_cash_flow_object(
1598 p_api_version => p_api_version,
1599 x_msg_count => x_msg_count,
1600 x_msg_data => x_msg_data,
1601 p_obj_type_code => p_prop_obj_type_code,
1602 p_src_table => G_OBJECT_SRC_TABLE,
1603 p_src_id => p_quote_id,
1604 p_base_src_id => p_prop_base_source_id,
1605 p_sts_code => G_PROPOSED_STATUS,
1606 x_cfo_id => l_cfo_id,
1607 x_return_status => l_return_status);
1608
1609 IF (is_debug_statement_on) THEN
1610 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
1611 'after call to create_cash_flow_object :'||l_return_status);
1612 END IF;
1613
1614 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1615 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1616 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1617 RAISE OKL_API.G_EXCEPTION_ERROR;
1618 END IF;
1619
1620 -- Create quote cf object
1621 lp_qcov_rec.qte_id := p_quote_id;
1622 lp_qcov_rec.cfo_id := l_cfo_id;
1623 lp_qcov_rec.BASE_SOURCE_ID := p_prop_base_source_id;
1624 --Bug 4299668 PAGARG Instead of calling the procedure to insert
1625 --quote cash flow object, store the record in the table
1626 --**START**--
1627 g_qcov_counter := g_qcov_counter + 1;
1628 g_qcov_tbl_type(g_qcov_counter) := lp_qcov_rec;
1629 --**END 4299668**--
1630 END IF;
1631
1632 l_new_stub_amount := NULL;
1633 l_new_amount := NULL;
1634
1635 --SECHAWLA 20-SEP-04 3816891 : Initialize stub days and periods
1636 l_new_periods := NULL;
1637 l_new_stub_days := NULL;
1638 l_remaining_stub_days := NULL;
1639 l_remaining_periods := NULL;
1640
1641 l_curr_level_start_date := l_cashflows_rec.start_date;
1642
1643 IF p_date_eff_from >= l_curr_level_start_date OR l_split_level = 'Y' THEN
1644
1645 IF l_cashflows_rec.sty_id <> l_prev_sty_id THEN
1646 lx_new_cash_flow := 'N';
1647 l_prev_sty_id := l_cashflows_rec.sty_id;
1648 l_split_level := 'N';
1649 --l_curr_level_start_date := l_cashflows_rec.start_date;
1650 END IF;
1651
1652 IF l_split_level = 'Y' THEN
1653 IF l_cashflows_rec.stub_days IS NULL THEN
1654 l_new_amount := l_cashflows_rec.amount * p_prorate_ratio;
1655 ELSE
1656 l_new_stub_amount := l_cashflows_rec.stub_amount * p_prorate_ratio;
1657 END IF;
1658
1659 lp_cashflow_rec.p_cfo_id := l_cfo_id;
1660 lp_cashflow_rec.p_sts_code := G_PROPOSED_STATUS;
1661 lp_cashflow_rec.p_sty_id := l_cashflows_rec.sty_id;
1662 lp_cashflow_rec.p_due_arrears_yn := l_cashflows_rec.due_arrears_yn;
1663 lp_cashflow_rec.p_start_date := l_cashflows_rec.start_date;
1664 lp_cashflow_rec.p_advance_periods := l_cashflows_rec.number_of_advance_periods;
1665 lp_cashflow_rec.p_khr_id := p_khr_id;
1666 lp_cashflow_rec.p_quote_id := p_quote_id;
1667 lp_cashflow_rec.p_amount := l_new_amount;
1668 lp_cashflow_rec.p_period_in_months := l_cashflows_rec.number_of_periods;
1669 lp_cashflow_rec.p_frequency := l_cashflows_rec.fqy_code;
1670 lp_cashflow_rec.p_seq_num := l_cashflows_rec.level_sequence;
1671 lp_cashflow_rec.p_stub_days := l_cashflows_rec.stub_days;
1672 lp_cashflow_rec.p_stub_amount := l_new_stub_amount;
1673
1674 create_cash_flows(
1675 p_api_version => p_api_version,
1676 x_msg_count => x_msg_count,
1677 x_msg_data => x_msg_data,
1678 p_cashflow_rec => lp_cashflow_rec,
1679 px_new_cash_flow => lx_new_cash_flow,
1680 x_return_status => l_return_status);
1681
1682 IF (is_debug_statement_on) THEN
1683 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
1684 'after call to create_cash_flows:'||l_return_status);
1685 END IF;
1686
1687 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1688 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1689 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1690 RAISE OKL_API.G_EXCEPTION_ERROR;
1691 END IF;
1692 ----
1693
1694 ELSE
1695
1696 IF l_cashflows_rec.stub_days IS NULL THEN
1697
1698 -- get the number of months that a payment covers
1699 IF l_cashflows_rec.fqy_code = 'M' THEN
1700 l_number_of_months := (l_cashflows_rec.number_of_periods);
1701 ELSIF l_cashflows_rec.fqy_code = 'Q' THEN
1702 l_number_of_months := (l_cashflows_rec.number_of_periods) * 3;
1703 ELSIF l_cashflows_rec.fqy_code = 'S' THEN
1704 l_number_of_months := (l_cashflows_rec.number_of_periods) * 6;
1705 ELSIF l_cashflows_rec.fqy_code = 'A' THEN
1706 l_number_of_months := (l_cashflows_rec.number_of_periods) * 12;
1707 END IF;
1708
1709 -- add months
1710 -- Get the first date after the last level period ends
1711 OPEN l_lastperiodstatdt_csr(l_curr_level_start_date, l_number_of_months);
1712 FETCH l_lastperiodstatdt_csr INTO l_next_level_start_date;
1713 CLOSE l_lastperiodstatdt_csr;
1714
1715 ELSE
1716 -- sechawla 20-SEP-04 3816891 : still ok, no changes
1717 l_next_level_start_date := l_curr_level_start_date + l_cashflows_rec.stub_days;
1718 END IF;
1719
1720 IF p_date_eff_from >= l_next_level_start_date THEN -- sechawla 20-SEP-04 3816891 : still ok, no changes
1721 -- keep the whole payment
1722 -- Create the payment header with same details as the current payment
1723
1724 lp_cashflow_rec.p_cfo_id := l_cfo_id;
1725 lp_cashflow_rec.p_sts_code := G_PROPOSED_STATUS;
1726 lp_cashflow_rec.p_sty_id := l_cashflows_rec.sty_id;
1727 lp_cashflow_rec.p_due_arrears_yn := l_cashflows_rec.due_arrears_yn;
1728 lp_cashflow_rec.p_start_date := l_cashflows_rec.start_date;
1729 lp_cashflow_rec.p_advance_periods := l_cashflows_rec.number_of_advance_periods;
1730 lp_cashflow_rec.p_khr_id := p_khr_id;
1731 lp_cashflow_rec.p_quote_id := p_quote_id;
1732 lp_cashflow_rec.p_amount := l_cashflows_rec.amount;
1733 lp_cashflow_rec.p_period_in_months := l_cashflows_rec.number_of_periods;
1734 lp_cashflow_rec.p_frequency := l_cashflows_rec.fqy_code;
1735 lp_cashflow_rec.p_seq_num := l_cashflows_rec.level_sequence;
1736 lp_cashflow_rec.p_stub_days := l_cashflows_rec.stub_days;
1737 lp_cashflow_rec.p_stub_amount := l_cashflows_rec.stub_amount;
1738
1739 create_cash_flows(
1740 p_api_version => p_api_version,
1741 x_msg_count => x_msg_count,
1742 x_msg_data => x_msg_data,
1743 p_cashflow_rec => lp_cashflow_rec,
1744 px_new_cash_flow => lx_new_cash_flow,
1745 x_return_status => l_return_status);
1746
1747 IF (is_debug_statement_on) THEN
1748 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
1749 'after call to create_cash_flows :'||l_return_status);
1750 END IF;
1751
1752 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1753 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1754 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1755 RAISE OKL_API.G_EXCEPTION_ERROR;
1756 END IF;
1757
1758 ELSIF p_date_eff_from >= l_curr_level_start_date AND p_date_eff_from < l_next_level_start_date THEN
1759
1760 IF l_cashflows_rec.stub_days is NULL THEN
1761
1762 -- keep the number of months upto the termination quote date
1763 OPEN l_monthsuptodate_csr(p_date_eff_from, l_curr_level_start_date);
1764 FETCH l_monthsuptodate_csr INTO l_months_between;
1765 CLOSE l_monthsuptodate_csr;
1766
1767 IF l_months_between = CEIL(l_months_between) THEN
1768
1769 IF l_cashflows_rec.due_arrears_yn = 'N' THEN -- SECHAWLA 20-SEP-04 3816891: bump up the month count only if payment is in ADV
1770
1771 -- Include payments for the whole month if quote was created on the first day
1772 -- of the period
1773 l_months_between := l_months_between + 1;
1774 ELSE -- SECHAWLA 20-SEP-04 3816891
1775 NULL; -- SECHAWLA 20-SEP-04 3816891 do not bump up the month count. Month count is a whole number here
1776 -- l_months_between can also be zero here if the quote was created on the first pymnt date
1777 END IF; -- SECHAWLA 20-SEP-04 3816891
1778 ELSE
1779
1780 -- quote not created on the first day of the period
1781
1782 -- -- SECHAWLA 20-SEP-04 3816891
1783
1784 IF l_cashflows_rec.due_arrears_yn = 'Y' THEN -- Arrears
1785 -- Get the number of months between quote eff date+1 and the curret level start date
1786 -- This is to check if the quote effective date is the last date of the current level period (M/Q/S/A)
1787 OPEN l_monthsuptodate_csr(p_date_eff_from + 1, l_curr_level_start_date);
1788 FETCH l_monthsuptodate_csr INTO l_months_to_check_last_day;
1789 CLOSE l_monthsuptodate_csr;
1790
1791 IF l_cashflows_rec.fqy_code = 'M' THEN
1792 IF l_months_to_check_last_day <> ceil(l_months_to_check_last_day) THEN
1793 -- SECHAWLA 20-SEP-04 3816891, date effective was not the last day of the current level period
1794 l_months_between := FLOOR(l_months_between);
1795 ELSE
1796 -- SECHAWLA 20-SEP-04 3816891, date effective was the last day of the current level period
1797 l_months_between := CEIL(l_months_between);
1798 END IF;
1799
1800 ELSIF l_cashflows_rec.fqy_code = 'Q' THEN
1801 IF (l_months_to_check_last_day/3) <> ceil(l_months_to_check_last_day/3) THEN
1802 l_months_between := FLOOR(l_months_between); -- SECHAWLA 20-SEP-04 3816891
1803 ELSE
1804 l_months_between := CEIL(l_months_between);
1805 END IF;
1806 ELSIF l_cashflows_rec.fqy_code = 'S' THEN
1807 IF (l_months_to_check_last_day/6) <> ceil(l_months_to_check_last_day/6) THEN
1808 l_months_between := FLOOR(l_months_between); -- SECHAWLA 20-SEP-04 3816891
1809 ELSE
1810 l_months_between := CEIL(l_months_between);
1811 END IF;
1812 ELSIF l_cashflows_rec.fqy_code = 'A' THEN
1813 IF (l_months_to_check_last_day/12) <> ceil(l_months_to_check_last_day/12) THEN
1814 l_months_between := FLOOR(l_months_between); -- SECHAWLA 20-SEP-04 3816891
1815 ELSE
1816 l_months_between := CEIL(l_months_between);
1817 END IF;
1818 END IF;
1819 ELSE -- Advance
1820 l_months_between := CEIL(l_months_between);
1821 END IF;
1822
1823 END IF;
1824
1825 IF l_cashflows_rec.fqy_code = 'M' THEN
1826 l_new_periods := l_months_between;
1827 ELSIF l_cashflows_rec.fqy_code = 'Q' THEN
1828 IF l_cashflows_rec.due_arrears_yn = 'Y' THEN -- SECHAWLA 20-SEP-04 3816891
1829 l_new_periods := floor(l_months_between / 3); -- SECHAWLA 20-SEP-04 3816891
1830 ELSE -- SECHAWLA 20-SEP-04 3816891
1831 l_new_periods := ceil(l_months_between / 3);
1832 END IF; -- SECHAWLA 20-SEP-04 3816891
1833
1834 ELSIF l_cashflows_rec.fqy_code = 'S' THEN
1835 IF l_cashflows_rec.due_arrears_yn = 'Y' THEN -- SECHAWLA 20-SEP-04 3816891
1836 l_new_periods := floor(l_months_between / 6); -- SECHAWLA 20-SEP-04 3816891
1837 ELSE -- SECHAWLA 20-SEP-04 3816891
1838 l_new_periods := ceil(l_months_between / 6);
1839 END IF; -- SECHAWLA 20-SEP-04 3816891
1840
1841 ELSIF l_cashflows_rec.fqy_code = 'A' THEN
1842 IF l_cashflows_rec.due_arrears_yn = 'Y' THEN -- SECHAWLA 20-SEP-04 3816891
1843 l_new_periods := floor(l_months_between / 12);-- SECHAWLA 20-SEP-04 3816891
1844 ELSE -- SECHAWLA 20-SEP-04 3816891
1845 l_new_periods := ceil(l_months_between / 12);
1846 END IF; -- SECHAWLA 20-SEP-04 3816891
1847
1848 END IF;
1849
1850 ELSE
1851 -- -- SECHAWLA 20-SEP-04 3816891 START
1852 -- l_new_stub_days will either be = total stub days of that level (l_cashflows_rec.stub_days)
1853 -- or be 0. Stub payment level will not be split
1854 IF l_cashflows_rec.due_arrears_yn = 'N' THEN -- Advance, -- SECHAWLA 20-SEP-04 3816891
1855 l_new_stub_days := l_cashflows_rec.stub_days; -- SECHAWLA 20-SEP-04 3816891 keep the whole stub period
1856 ELSE -- Arrears, SECHAWLA 20-SEP-04 3816891
1857 IF p_date_eff_from = l_next_level_start_date - 1 THEN -- quote created on the last day of the stub level
1858 l_new_stub_days := l_cashflows_rec.stub_days; -- SECHAWLA 20-SEP-04 3816891 keep the whole stub period
1859 ELSE
1860 l_new_stub_days := 0;
1861 END IF;
1862 END IF;
1863 --l_new_stub_days := (p_date_eff_from - l_curr_level_start_date) + 1 ; -- SECHAWLA 20-SEP-04 3816891 :commneted out
1864 -- SECHAWLA 20-SEP-04 3816891: END
1865
1866 END IF;
1867
1868 IF l_new_periods > 0 OR l_new_stub_days > 0 THEN -- SECHAWLA 20-SEP-04 3816891: added this check
1869 -- new periods /stub days will be 0 in the scenario where payments are in arrears and quote
1870 -- is created before the last date of the first payment period. e.g
1871 -- 01-jan-04 3 100
1872 -- 01-apr-04 2 80
1873 -- 01-jun-04 2 50
1874 -- quote created between 01-jan-04 and 30-jan-04
1875 -- In this case for full line termination, there won't be any payments
1876 -- or partial line termination, new payments would be -
1877 -- 01-jan-04 3 50
1878 -- 01-apr-04 2 40
1879 -- 01-jun-04 2 25
1880
1881 lp_cashflow_rec.p_cfo_id := l_cfo_id;
1882 lp_cashflow_rec.p_sts_code := G_PROPOSED_STATUS;
1883 lp_cashflow_rec.p_sty_id := l_cashflows_rec.sty_id;
1884 lp_cashflow_rec.p_due_arrears_yn := l_cashflows_rec.due_arrears_yn;
1885 lp_cashflow_rec.p_start_date := l_cashflows_rec.start_date;
1886 lp_cashflow_rec.p_advance_periods := l_cashflows_rec.number_of_advance_periods;
1887 lp_cashflow_rec.p_khr_id := p_khr_id;
1888 lp_cashflow_rec.p_quote_id := p_quote_id;
1889 lp_cashflow_rec.p_amount := l_cashflows_rec.amount;
1890 lp_cashflow_rec.p_period_in_months := l_new_periods;
1891 lp_cashflow_rec.p_frequency := l_cashflows_rec.fqy_code;
1892 lp_cashflow_rec.p_seq_num := l_cashflows_rec.level_sequence;
1893 lp_cashflow_rec.p_stub_days := l_new_stub_days;
1894 lp_cashflow_rec.p_stub_amount := l_cashflows_rec.stub_amount;
1895
1896 --create cash flow with new number of periods, same start date, same amount and status = 'PROPOSED'
1897 create_cash_flows(
1898 p_api_version => p_api_version,
1899 x_msg_count => x_msg_count,
1900 x_msg_data => x_msg_data,
1901 p_cashflow_rec => lp_cashflow_rec,
1902 px_new_cash_flow => lx_new_cash_flow,
1903 x_return_status => l_return_status);
1904
1905 IF (is_debug_statement_on) THEN
1906 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
1907 'after call to create_cash_flows :'||l_return_status);
1908 END IF;
1909
1910 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1911 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1912 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1913 RAISE OKL_API.G_EXCEPTION_ERROR;
1914 END IF;
1915 END IF; -- IF l_new_periods > 0 OR l_new_stub_days > 0 THEN -- SECHAWLA 20-SEP-04 3816891: added this check
1916
1917
1918 IF l_cashflows_rec.stub_days IS NULL THEN
1919 l_remaining_periods := l_cashflows_rec.number_of_periods - l_new_periods;
1920
1921 ELSE
1922 l_remaining_stub_days := l_cashflows_rec.stub_days - l_new_stub_days;
1923 END IF;
1924
1925 -- SECHAWLA 20-SEP-04 3816891: l_remaining_stub_days will either be 0 (if whole stub level was retained)
1926 -- OR l_remaining_stub_days will be = total stub days on that level (if whole stub level was excuded, as in Arrears)
1927
1928 IF p_prorate_ratio > 0 THEN -- partial line termination
1929 l_split_level := 'Y';
1930 END IF;
1931 -- create a new split level
1932 IF l_remaining_periods > 0 OR l_remaining_stub_days > 0 THEN
1933
1934 -- In case of termination with full asset qty, there will be no more payments after the
1935 -- quote effective date. In case of partial line termination, when the quote eff dt is
1936 -- between the level start date and end date, the payment level will be split into 2
1937 -- levels.
1938 IF p_prorate_ratio > 0 THEN -- partial line termination
1939 -- Get the start date of the first period after the split happens
1940
1941 IF l_cashflows_rec.stub_days IS NULL THEN
1942 IF l_cashflows_rec.fqy_code = 'M' THEN
1943 -- add months
1944 OPEN l_lastperiodstatdt_csr(l_curr_level_start_date, l_new_periods);
1945 FETCH l_lastperiodstatdt_csr INTO l_first_period_start_date;
1946 CLOSE l_lastperiodstatdt_csr;
1947 ELSIF l_cashflows_rec.fqy_code = 'Q' THEN
1948 -- add months
1949 OPEN l_lastperiodstatdt_csr(l_curr_level_start_date, l_new_periods*3);
1950 FETCH l_lastperiodstatdt_csr INTO l_first_period_start_date;
1951 CLOSE l_lastperiodstatdt_csr;
1952 ELSIF l_cashflows_rec.fqy_code = 'S' THEN
1953 -- add months
1954 OPEN l_lastperiodstatdt_csr(l_curr_level_start_date, l_new_periods*6);
1955 FETCH l_lastperiodstatdt_csr INTO l_first_period_start_date;
1956 CLOSE l_lastperiodstatdt_csr;
1957 ELSIF l_cashflows_rec.fqy_code = 'A' THEN
1958 -- add months
1959 OPEN l_lastperiodstatdt_csr(l_curr_level_start_date, l_new_periods*12);
1960 FETCH l_lastperiodstatdt_csr INTO l_first_period_start_date;
1961 CLOSE l_lastperiodstatdt_csr;
1962 END IF;
1963
1964 ELSE
1965
1966 -- l_first_period_start_date := p_date_eff_from + 1; -- SECHAWLA 20-SEP-04 3816891: commented out
1967
1968 -- SECHAWLA 20-SEP-04 3816891: At this point, l_new_stub_days will always be 0. So the
1969 -- l_first_period_start_date will be = start date of that stub level
1970 l_first_period_start_date := l_curr_level_start_date + l_new_stub_days ;
1971 END IF;
1972
1973 IF l_cashflows_rec.stub_days IS NULL THEN
1974 l_new_amount := l_cashflows_rec.amount * p_prorate_ratio;
1975
1976 ELSE
1977 l_new_stub_amount := l_cashflows_rec.stub_amount * p_prorate_ratio;
1978 END IF;
1979
1980 -- l_new_seq := l_cashflows_rec.level_sequence + l_lowest_level_seq;
1981
1982 -- create cash flow with new number of periods (l_remaining_periods), new start date (l_first_period_start_date)
1983 -- new amount (l_new_amount), new seq number and status = 'PROPOSED'
1984 -- This call to create_cash_flow procedure will just create the new level in cash flow levels
1985 -- for the same header.
1986
1987 lp_cashflow_rec.p_cfo_id := l_cfo_id;
1988 lp_cashflow_rec.p_sts_code := G_PROPOSED_STATUS;
1989 lp_cashflow_rec.p_sty_id := l_cashflows_rec.sty_id;
1990 lp_cashflow_rec.p_due_arrears_yn := l_cashflows_rec.due_arrears_yn;
1991 lp_cashflow_rec.p_start_date := l_first_period_start_date;
1992 lp_cashflow_rec.p_advance_periods := l_cashflows_rec.number_of_advance_periods;
1993 lp_cashflow_rec.p_khr_id := p_khr_id;
1994 lp_cashflow_rec.p_quote_id := p_quote_id;
1995 lp_cashflow_rec.p_amount := l_new_amount;
1996 lp_cashflow_rec.p_period_in_months := l_remaining_periods;
1997 lp_cashflow_rec.p_frequency := l_cashflows_rec.fqy_code;
1998 -- lp_cashflow_rec.p_seq_num := l_cashflows_rec.level_sequence;
1999 lp_cashflow_rec.p_stub_days := l_remaining_stub_days;
2000 lp_cashflow_rec.p_stub_amount := l_new_stub_amount;
2001
2002 create_cash_flows(
2003 p_api_version => p_api_version,
2004 x_msg_count => x_msg_count,
2005 x_msg_data => x_msg_data,
2006 p_cashflow_rec => lp_cashflow_rec,
2007 px_new_cash_flow => lx_new_cash_flow,
2008 x_return_status => l_return_status);
2009
2010 IF (is_debug_statement_on) THEN
2011 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
2012 'after call to create_cash_flows :'||l_return_status);
2013 END IF;
2014
2015 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2016 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2017 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2018 RAISE OKL_API.G_EXCEPTION_ERROR;
2019 END IF;
2020 -----------
2021
2022 END IF;
2023 END IF; -- l_remaining_periods > 0
2024
2025 END IF; -- IF p_date_eff_from >= l_next_level_start_date THEN
2026 END IF; -- p_split_level = 'Y'
2027
2028 END IF; -- IF p_date_eff_from >= l_curr_level_start_date THEN
2029
2030 -- commented because the start date is now stored at the levels
2031 -- l_curr_level_start_date := l_next_level_start_date;
2032 END LOOP;
2033
2034 x_return_status := l_return_status;
2035
2036 IF (is_debug_procedure_on) THEN
2037 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'End(-)');
2038 END IF;
2039
2040 EXCEPTION
2041 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2042
2043 IF (is_debug_exception_on) THEN
2044 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_EXCEPTION,L_MODULE_NAME,
2045 'EXCEPTION :'||'OKL_API.G_EXCEPTION_ERROR');
2046 END IF;
2047
2048 IF l_cashflows_csr%ISOPEN THEN
2049 CLOSE l_cashflows_csr;
2050 END IF;
2051 IF l_lastperiodstatdt_csr%ISOPEN THEN
2052 CLOSE l_lastperiodstatdt_csr;
2053 END IF;
2054 IF l_monthsuptodate_csr%ISOPEN THEN
2055 CLOSE l_monthsuptodate_csr;
2056 END IF;
2057
2058 x_return_status := OKL_API.G_RET_STS_ERROR;
2059 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2060
2061 IF (is_debug_exception_on) THEN
2062 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_EXCEPTION,L_MODULE_NAME,
2063 'EXCEPTION :'||'OKL_API.G_EXCEPTION_UNEXPECTED_ERROR');
2064 END IF;
2065
2066 IF l_cashflows_csr%ISOPEN THEN
2067 CLOSE l_cashflows_csr;
2068 END IF;
2069 IF l_lastperiodstatdt_csr%ISOPEN THEN
2070 CLOSE l_lastperiodstatdt_csr;
2071 END IF;
2072 IF l_monthsuptodate_csr%ISOPEN THEN
2073 CLOSE l_monthsuptodate_csr;
2074 END IF;
2075
2076 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
2077 WHEN OTHERS THEN
2078
2079 IF (is_debug_exception_on) THEN
2080 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_EXCEPTION,L_MODULE_NAME,
2081 'EXCEPTION :'||sqlerrm);
2082 END IF;
2083
2084 IF l_cashflows_csr%ISOPEN THEN
2085 CLOSE l_cashflows_csr;
2086 END IF;
2087 IF l_lastperiodstatdt_csr%ISOPEN THEN
2088 CLOSE l_lastperiodstatdt_csr;
2089 END IF;
2090 IF l_monthsuptodate_csr%ISOPEN THEN
2091 CLOSE l_monthsuptodate_csr;
2092 END IF;
2093
2094 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
2095 -- unexpected error
2096 OKL_API.set_message(p_app_name => g_app_name,
2097 p_msg_name => g_unexpected_error,
2098 p_token1 => g_sqlcode_token,
2099 p_token1_value => sqlcode,
2100 p_token2 => g_sqlerrm_token,
2101 p_token2_value => sqlerrm);
2102 END calc_prop_line_payments;
2103
2104 /*========================================================================
2105 | PRIVATE PROCEDURE calc_proposed_payments
2106 |
2107 | DESCRIPTION
2108 | This is the main procedure to calculate and store revised payments for a termination quote
2109 |
2110 | CALLED FROM PROCEDURES/FUNCTIONS
2111 | calc_quote_payments
2112 |
2113 | CALLS PROCEDURES/FUNCTIONS
2114 | create_cash_flow_object, create_cash_flows, calc_prop_line_payments
2115 |
2116 | PARAMETERS
2117 | p_quote_id IN Quote ID
2118 | p_khr_id IN Contarct ID
2119 | p_date_eff_from IN quote effective date
2120 |
2121 | KNOWN ISSUES
2122 |
2123 | NOTES
2124 |
2125 |
2126 | MODIFICATION HISTORY
2127 | Date Author Description of Changes
2128 | 14-OCT-2003 SECHAWLA Created
2129 | 29-SEP-2004 pagarg Bug #3921591
2130 | Added logic to calculate porposed payments for
2131 | fee asset line and modified the logic for fee line
2132 | to consider the assets associated with the fee,
2133 *=======================================================================*/
2134 PROCEDURE calc_proposed_payments(
2135 p_api_version IN NUMBER,
2136 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2137 x_return_status OUT NOCOPY VARCHAR2,
2138 x_msg_count OUT NOCOPY NUMBER,
2139 x_msg_data OUT NOCOPY VARCHAR2,
2140 p_quote_id IN NUMBER,
2141 p_khr_id IN NUMBER,
2142 p_date_eff_from IN DATE) AS
2143
2144 /*-----------------------------------------------------------------------+
2145 | Cursor Declarations |
2146 +-----------------------------------------------------------------------*/
2147
2148 --Bug #3921591: pagarg +++ Rollover +++
2149 -- Modified the cursor to obtain booked assets with the given line type
2150 -- Get all the booked assets associated with the given line type
2151 CURSOR l_lineassets_csr(cp_line_id IN NUMBER, cp_line_type_code IN VARCHAR2) IS
2152 SELECT cim.object1_id1, cle.id
2153 FROM okc_k_lines_b cle, okc_line_styles_b lse, okc_k_items cim
2154 WHERE cle.lse_id = lse.id
2155 AND lse.lty_code = cp_line_type_code
2156 AND cim.cle_id = cle.id
2157 AND cle.cle_id = cp_line_id
2158 AND cle.sts_code = 'BOOKED';
2159
2160 -- Check if an asset belongs to a quote
2161 CURSOR l_assetinquote_csr(cp_quote_id IN NUMBER, cp_kle_id IN NUMBER) IS
2162 SELECT id,
2163 --kle_id,
2164 asset_quantity, quote_quantity
2165 FROM okl_txl_quote_lines_b
2166 WHERE qte_id = cp_quote_id
2167 AND qlt_code = 'AMCFIA'
2168 AND kle_id = cp_kle_id;
2169
2170 --Bug #3921591: pagarg +++ Rollover +++
2171 -- Modified the cursor to obtain financial assets with the given line type
2172 -- Get the financial asset associated with a given line type asset line (subline)
2173 CURSOR l_finasset_csr(cp_fee_serviced_asset_line_id IN NUMBER, cp_line_type IN VARCHAR2) IS
2174 SELECT cim.object1_id1
2175 FROM okc_k_lines_b cle, okc_line_styles_b lse, okc_k_items cim
2176 WHERE cle.lse_id = lse.id
2177 AND lse.lty_code = cp_line_type
2178 AND cim.cle_id = cle.id
2179 AND cle.id = cp_fee_serviced_asset_line_id;
2180
2181 -- get current payment objects of a particular type
2182 CURSOR l_currpymtobjects_csr(cp_oty_code IN VARCHAR2, cp_quote_id IN NUMBER) IS
2183 SELECT DISTINCT cfo.id, qco.base_source_id
2184 FROM okl_cash_flow_objects cfo, okl_cash_flows caf, OKL_TRX_QTE_CF_OBJECTS qco
2185 WHERE cfo.id = caf.cfo_id
2186 AND cfo.id = qco.cfo_id
2187 AND cfo.oty_code = cp_oty_code
2188 AND cfo.source_table = G_OBJECT_SRC_TABLE
2189 AND cfo.source_id = cp_quote_id
2190 AND caf.sts_code = G_CURRENT_STATUS
2191 AND caf.cft_code = G_CASH_FLOW_TYPE;
2192
2193 -- get the payment lines for a given object
2194 CURSOR l_currpymtlines_csr(cp_obj_id IN NUMBER) IS
2195 SELECT caf.sty_id, caf.due_arrears_yn, cfl.start_date, caf.number_of_advance_periods, cfl.amount,
2196 cfl.number_of_periods, cfl.fqy_code, cfl.level_Sequence, cfl.stub_days, cfl.stub_amount
2197 FROM okl_cash_flows caf, okl_cash_flow_levels cfl
2198 WHERE caf.id = cfl.caf_id
2199 AND caf.cfo_id = cp_obj_id
2200 ORDER BY caf.sty_id, cfl.start_date;
2201
2202 /*-----------------------------------------------------------------------+
2203 | SubType Declarations |
2204 +-----------------------------------------------------------------------*/
2205
2206 SUBTYPE qcov_rec_type IS okl_trx_qte_cf_objects_pub.qcov_rec_type;
2207
2208 /*-----------------------------------------------------------------------+
2209 | Local Variable Declarations and initializations |
2210 +-----------------------------------------------------------------------*/
2211
2212 lp_qcov_rec qcov_rec_type;
2213 lx_qcov_rec qcov_rec_type;
2214
2215 l_cfo_id NUMBER;
2216 l_pymt_Count NUMBER;
2217 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2218 l_prev_sty_id NUMBER := -99;
2219
2220 lx_new_cash_flow VARCHAR2(1);
2221
2222 l_prorate_ratio NUMBER;
2223 l_count NUMBER;
2224 l_total_curr_cost NUMBER;
2225 l_total_new_cost NUMBER;
2226 l_curr_cap_cost NUMBER;
2227 l_new_cap_cost NUMBER;
2228 l_asset_quantity NUMBER;
2229 l_quote_quantity NUMBER;
2230 l_quote_line_id NUMBER;
2231
2232 l_fin_asset_id NUMBER;
2233 lp_cashflow_rec cashflow_rec_type;
2234 L_MODULE_NAME VARCHAR2(500) := G_MODULE_NAME||'calc_proposed_payments';
2235 is_debug_exception_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_EXCEPTION);
2236 is_debug_procedure_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_PROCEDURE);
2237 is_debug_statement_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_STATEMENT);
2238 BEGIN
2239
2240
2241 IF (is_debug_procedure_on) THEN
2242 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'Begin(+)');
2243 END IF;
2244
2245 --Print Input Variables
2246 IF (is_debug_statement_on) THEN
2247 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
2248 'p_quote_id :'||p_quote_id);
2249 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
2250 'p_khr_id :'||p_khr_id);
2251 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
2252 'p_date_eff_from :'||p_date_eff_from);
2253 END IF;
2254
2255 -------------------------- Get proposed K Level payments ----------------------------------
2256
2257 -- Get the current contract object for which the payment exists
2258 -------------
2259 FOR l_currpymtobjects_rec IN l_currpymtobjects_csr(G_CONTRACT_OBJ_TYPE, p_quote_id) LOOP
2260 l_pymt_Count := 0;
2261
2262 -- get the payment lines for contract object, create proposed object, proposed cash flows and proposed
2263 -- cash flow levels
2264 FOR l_currpymtlines_rec IN l_currpymtlines_csr(l_currpymtobjects_rec.id) LOOP
2265
2266 l_pymt_count := l_pymt_count + 1;
2267
2268 IF l_pymt_count = 1 THEN -- K level payments exist, create a K Object
2269
2270 create_cash_flow_object(p_api_version => p_api_version,
2271 x_msg_count => x_msg_count,
2272 x_msg_data => x_msg_data,
2273 p_obj_type_code => G_CONTRACT_OBJ_TYPE,
2274 p_src_table => G_OBJECT_SRC_TABLE,
2275 p_src_id => p_quote_id,
2276 p_base_src_id => p_khr_id,
2277 p_sts_code => G_PROPOSED_STATUS,
2278 x_cfo_id => l_cfo_id,
2279 x_return_status => l_return_status);
2280
2281 IF (is_debug_statement_on) THEN
2282 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
2283 'after call to create_cash_flow_object :'||l_return_status);
2284 END IF;
2285
2286
2287 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2288 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2289 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2290 RAISE OKL_API.G_EXCEPTION_ERROR;
2291 END IF;
2292
2293 lp_qcov_rec.qte_id := p_quote_id;
2294 lp_qcov_rec.cfo_id := l_cfo_id;
2295 lp_qcov_rec.BASE_SOURCE_ID := p_khr_id;
2296 --Bug 4299668 PAGARG Instead of calling the procedure to insert
2297 --quote cash flow object, store the record in the table
2298 --**START**--
2299 g_qcov_counter := g_qcov_counter + 1;
2300 g_qcov_tbl_type(g_qcov_counter) := lp_qcov_rec;
2301 --**END 4299668**--
2302 END IF;
2303
2304 IF l_currpymtlines_rec.sty_id <> l_prev_sty_id THEN
2305 lx_new_cash_flow := 'N';
2306 l_prev_sty_id := l_currpymtlines_rec.sty_id;
2307 END IF;
2308
2309 lp_cashflow_rec.p_cfo_id := l_cfo_id;
2310 lp_cashflow_rec.p_sts_code := G_PROPOSED_STATUS;
2311 lp_cashflow_rec.p_sty_id := l_currpymtlines_rec.sty_id;
2312 lp_cashflow_rec.p_due_arrears_yn := l_currpymtlines_rec.due_arrears_yn;
2313 lp_cashflow_rec.p_start_date := l_currpymtlines_rec.start_date;
2314 lp_cashflow_rec.p_advance_periods := l_currpymtlines_rec.number_of_advance_periods;
2315 lp_cashflow_rec.p_khr_id := p_khr_id;
2316 lp_cashflow_rec.p_quote_id := p_quote_id;
2317 lp_cashflow_rec.p_amount := l_currpymtlines_rec.amount;
2318 lp_cashflow_rec.p_period_in_months := l_currpymtlines_rec.number_of_periods;
2319 lp_cashflow_rec.p_frequency := l_currpymtlines_rec.fqy_code;
2320 lp_cashflow_rec.p_seq_num := l_currpymtlines_rec.level_sequence;
2321 lp_cashflow_rec.p_stub_days := l_currpymtlines_rec.stub_days;
2322 lp_cashflow_rec.p_stub_amount := l_currpymtlines_rec.stub_amount;
2323
2324 create_cash_flows( p_api_version => p_api_version,
2325 x_msg_count => x_msg_count,
2326 x_msg_data => x_msg_data,
2327 p_cashflow_rec => lp_cashflow_rec,
2328 px_new_cash_flow => lx_new_cash_flow,
2329 x_return_status => l_return_status);
2330
2331 IF (is_debug_statement_on) THEN
2332 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
2333 'after call to create_cash_flows:'||l_return_status);
2334 END IF;
2335
2336 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2337 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2338 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2339 RAISE OKL_API.G_EXCEPTION_ERROR;
2340 END IF;
2341
2342 END LOOP;
2343 END LOOP;
2344
2345 ---------------------------end proposed contract level payments ----------------------
2346
2347
2348 -------------------------- Get the proposed asset line payments ------------------------
2349 -- Get the current financial asset lines objects for which the payment exists
2350 FOR l_currpymtobjects_rec IN l_currpymtobjects_csr(G_FIN_ASSET_OBJ_TYPE, p_quote_id) LOOP
2351
2352 -- Check if asset line is included in the quote
2353 OPEN l_assetinquote_csr(p_quote_id, l_currpymtobjects_rec.base_source_id);
2354 FETCH l_assetinquote_csr INTO l_quote_line_id, l_asset_quantity, l_quote_quantity; -- not using thsese ?
2355 IF l_assetinquote_csr%FOUND THEN
2356 l_prorate_ratio := (l_asset_quantity - l_quote_quantity) / l_asset_quantity;
2357 -- Prorate ratio can not be 1 as quote quantity will not be 0
2358 IF l_prorate_ratio >= 0 AND l_prorate_ratio < 1 THEN
2359
2360 -- Get the new asset line level payments
2361 calc_prop_line_payments( p_api_version => p_api_version,
2362 x_msg_count => x_msg_count,
2363 x_msg_data => x_msg_data,
2364 p_curr_cfo_id => l_currpymtobjects_rec.id,
2365 p_prop_obj_type_code => G_FIN_ASSET_OBJ_TYPE,
2366 p_prop_base_source_id => l_currpymtobjects_rec.base_source_id,
2367 p_prorate_ratio => l_prorate_ratio,
2368 p_date_eff_from => p_date_eff_from,
2369 p_quote_id => p_quote_id,
2370 p_khr_id => p_khr_id ,
2371 x_return_status => l_return_status);
2372
2373 IF (is_debug_statement_on) THEN
2374 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
2375 'after call to calc_prop_line_payments :'||l_return_status);
2376 END IF;
2377 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2378 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2379 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2380 RAISE OKL_API.G_EXCEPTION_ERROR;
2381 END IF;
2382 END IF;
2383 ELSE
2384 --
2385 l_prev_sty_id := -99;
2386
2387 l_pymt_Count := 0;
2388 -- get the current payemnts for the financila asset and store them as proposed
2389 FOR l_currpymtlines_rec IN l_currpymtlines_csr(l_currpymtobjects_rec.id) LOOP
2390 l_pymt_Count := l_pymt_Count + 1;
2391 IF l_pymt_count = 1 THEN -- line level payments exist
2392
2393 l_cfo_id := NULL;
2394
2395 create_cash_flow_object(
2396 p_api_version => p_api_version,
2397 x_msg_count => x_msg_count,
2398 x_msg_data => x_msg_data,
2399 p_obj_type_code => G_FIN_ASSET_OBJ_TYPE,
2400 p_src_table => G_OBJECT_SRC_TABLE,
2401 p_src_id => p_quote_id,
2402 p_base_src_id => l_currpymtobjects_rec.base_source_id,
2403 p_sts_code => G_PROPOSED_STATUS,
2404 x_cfo_id => l_cfo_id,
2405 x_return_status => l_return_status);
2406
2407 IF (is_debug_statement_on) THEN
2408 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
2409 'after call to calc_prop_line_payments :'||l_return_status);
2410 END IF;
2411
2412
2413 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2414 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2415 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2416 RAISE OKL_API.G_EXCEPTION_ERROR;
2417 END IF;
2418
2419 lp_qcov_rec.qte_id := p_quote_id;
2420 lp_qcov_rec.cfo_id := l_cfo_id;
2421 lp_qcov_rec.BASE_SOURCE_ID := l_currpymtobjects_rec.base_source_id;
2422 --Bug 4299668 PAGARG Instead of calling the procedure to insert
2423 --quote cash flow object, store the record in the table
2424 --**START**--
2425 g_qcov_counter := g_qcov_counter + 1;
2426 g_qcov_tbl_type(g_qcov_counter) := lp_qcov_rec;
2427 --**END 4299668**--
2428 END IF;
2429
2430 IF l_currpymtlines_rec.sty_id <> l_prev_sty_id THEN
2431 lx_new_cash_flow := 'N';
2432 l_prev_sty_id := l_currpymtlines_rec.sty_id;
2433 END IF;
2434
2435 lp_cashflow_rec.p_cfo_id := l_cfo_id;
2436 lp_cashflow_rec.p_sts_code := G_PROPOSED_STATUS;
2437 lp_cashflow_rec.p_sty_id := l_currpymtlines_rec.sty_id;
2438 lp_cashflow_rec.p_due_arrears_yn := l_currpymtlines_rec.due_arrears_yn;
2439 lp_cashflow_rec.p_start_date := l_currpymtlines_rec.start_date;
2440 lp_cashflow_rec.p_advance_periods := l_currpymtlines_rec.number_of_advance_periods;
2441 lp_cashflow_rec.p_khr_id := p_khr_id;
2442 lp_cashflow_rec.p_quote_id := p_quote_id;
2443 lp_cashflow_rec.p_amount := l_currpymtlines_rec.amount;
2444 lp_cashflow_rec.p_period_in_months := l_currpymtlines_rec.number_of_periods;
2445 lp_cashflow_rec.p_frequency := l_currpymtlines_rec.fqy_code;
2446 lp_cashflow_rec.p_seq_num := l_currpymtlines_rec.level_sequence;
2447 lp_cashflow_rec.p_stub_days := l_currpymtlines_rec.stub_days;
2448 lp_cashflow_rec.p_stub_amount := l_currpymtlines_rec.stub_amount;
2449
2450 create_cash_flows( p_api_version => p_api_version,
2451 x_msg_count => x_msg_count,
2452 x_msg_data => x_msg_data,
2453 p_cashflow_rec => lp_cashflow_rec,
2454 px_new_cash_flow => lx_new_cash_flow,
2455 x_return_status => l_return_status);
2456
2457 IF (is_debug_statement_on) THEN
2458 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
2459 'after call to create_cash_flows :'||l_return_status);
2460 END IF;
2461
2462
2463 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2464 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2465 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2466 RAISE OKL_API.G_EXCEPTION_ERROR;
2467 END IF;
2468
2469 END LOOP;
2470 ---------------
2471
2472 END IF;
2473 CLOSE l_assetinquote_csr;
2474 END LOOP;
2475
2476 -------------------------- Get the proposed service line payments ------------------------
2477 -- get all the service lines for which current payment exists
2478
2479 FOR l_currpymtobjects_rec IN l_currpymtobjects_csr(G_SERVICE_LINE_OBJ_TYPE, p_quote_id) LOOP
2480
2481 l_count := 0;
2482 l_total_curr_cost := 0;
2483 l_total_new_cost := 0;
2484
2485 --Bug #3921591: pagarg +++ Rollover +++
2486 -- Modified the cursor call to pass line type also
2487 -- Get all the booked assets associated with the service line
2488 FOR l_servicelineassets_rec IN l_lineassets_csr(l_currpymtobjects_rec.base_source_id, G_LINKED_SERVICE_LINE_TYPE) LOOP
2489 l_count := l_count + 1 ;
2490
2491 -- get the capitalize cost of the asset
2492
2493 OKL_EXECUTE_FORMULA_PUB.EXECUTE(p_api_version => p_api_version,
2494 p_init_msg_list => OKL_API.G_FALSE,
2495 x_return_status => l_return_status,
2496 x_msg_count => x_msg_count,
2497 x_msg_data => x_msg_data,
2498 p_formula_name => 'LINE_CAP_AMNT',
2499 p_contract_id => p_khr_id,
2500 p_line_id => l_servicelineassets_rec.object1_id1,
2501 x_value => l_curr_cap_cost);
2502
2503
2504 IF (is_debug_statement_on) THEN
2505 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
2506 'after call to OKL_EXECUTE_FORMULA_PUB.EXECUTE :'||l_return_status);
2507 END IF;
2508
2509 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2510 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2511 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2512 RAISE OKL_API.G_EXCEPTION_ERROR;
2513 END IF;
2514
2515 -- Check if asset belongs to the quote
2516 OPEN l_assetinquote_csr(p_quote_id, l_servicelineassets_rec.object1_id1);
2517 FETCH l_assetinquote_csr INTO l_quote_line_id, l_asset_quantity, l_quote_quantity;
2518 IF l_assetinquote_csr%NOTFOUND THEN
2519 l_new_cap_cost := l_curr_cap_cost;
2520 ELSE
2521 IF l_asset_quantity = l_quote_quantity THEN
2522 l_new_cap_cost := 0;
2523 ELSE
2524 l_new_cap_cost := (l_curr_cap_cost / l_asset_quantity) * (l_asset_quantity - l_quote_quantity);
2525 END IF;
2526 END IF;
2527 CLOSE l_assetinquote_csr;
2528
2529 l_total_curr_cost := l_total_curr_cost + l_curr_cap_cost ;
2530 l_total_new_cost := l_total_new_cost + l_new_cap_cost;
2531
2532 END LOOP;
2533
2534 IF l_count > 0 THEN -- assets associated with service line
2535
2536 l_prorate_ratio := l_total_new_cost / l_total_curr_cost;
2537
2538 IF l_prorate_ratio >= 0 AND l_prorate_ratio < 1 THEN
2539
2540 calc_prop_line_payments( p_api_version => p_api_version,
2541 x_msg_count => x_msg_count,
2542 x_msg_data => x_msg_data,
2543 p_curr_cfo_id => l_currpymtobjects_rec.id,
2544 p_prop_obj_type_code => G_SERVICE_LINE_OBJ_TYPE,
2545 p_prop_base_source_id => l_currpymtobjects_rec.base_source_id,
2546 p_prorate_ratio => l_prorate_ratio,
2547 p_date_eff_from => p_date_eff_from,
2548 p_quote_id => p_quote_id,
2549 p_khr_id => p_khr_id ,
2550 x_return_status => l_return_status);
2551
2552 IF (is_debug_statement_on) THEN
2553 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
2554 'after call to calc_prop_line_payments :'||l_return_status);
2555 END IF;
2556
2557 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2558 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2559 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2560 RAISE OKL_API.G_EXCEPTION_ERROR;
2561 END IF;
2562 END IF;
2563 ---
2564 END IF;
2565
2566 -- if no assets are associated with the service line or none of the assets attached to the service line are quoted
2567 IF l_count = 0 OR l_prorate_ratio = 1 THEN
2568 l_prev_sty_id := -99;
2569
2570 l_pymt_Count := 0;
2571 -- get the current payment lines for the service line and store them as proposed payments
2572 FOR l_currpymtlines_rec IN l_currpymtlines_csr(l_currpymtobjects_rec.id) LOOP
2573
2574 l_pymt_Count := l_pymt_Count + 1;
2575 IF l_pymt_count = 1 THEN -- line level payments exist
2576
2577 l_cfo_id := NULL;
2578
2579 create_cash_flow_object(
2580 p_api_version => p_api_version,
2581 x_msg_count => x_msg_count,
2582 x_msg_data => x_msg_data,
2583 p_obj_type_code => G_SERVICE_LINE_OBJ_TYPE,
2584 p_src_table => G_OBJECT_SRC_TABLE,
2585 p_src_id => p_quote_id,
2586 p_base_src_id => l_currpymtobjects_rec.base_source_id,
2587 p_sts_code => G_PROPOSED_STATUS,
2588 x_cfo_id => l_cfo_id,
2589 x_return_status => l_return_status);
2590
2591 IF (is_debug_statement_on) THEN
2592 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
2593 'after call to create_cash_flow_object :'||l_return_status);
2594 END IF;
2595
2596 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2597 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2598 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2599 RAISE OKL_API.G_EXCEPTION_ERROR;
2600 END IF;
2601
2602 lp_qcov_rec.qte_id := p_quote_id;
2603 lp_qcov_rec.cfo_id := l_cfo_id;
2604 lp_qcov_rec.BASE_SOURCE_ID := l_currpymtobjects_rec.base_source_id;
2605 --Bug 4299668 PAGARG Instead of calling the procedure to insert
2606 --quote cash flow object, store the record in the table
2607 --**START**--
2608 g_qcov_counter := g_qcov_counter + 1;
2609 g_qcov_tbl_type(g_qcov_counter) := lp_qcov_rec;
2610 --**END 4299668**--
2611 END IF;
2612
2613 IF l_currpymtlines_rec.sty_id <> l_prev_sty_id THEN
2614 lx_new_cash_flow := 'N';
2615 l_prev_sty_id := l_currpymtlines_rec.sty_id;
2616 END IF;
2617
2618 lp_cashflow_rec.p_cfo_id := l_cfo_id;
2619 lp_cashflow_rec.p_sts_code := G_PROPOSED_STATUS;
2620 lp_cashflow_rec.p_sty_id := l_currpymtlines_rec.sty_id;
2621 lp_cashflow_rec.p_due_arrears_yn := l_currpymtlines_rec.due_arrears_yn;
2622 lp_cashflow_rec.p_start_date := l_currpymtlines_rec.start_date;
2623 lp_cashflow_rec.p_advance_periods := l_currpymtlines_rec.number_of_advance_periods;
2624 lp_cashflow_rec.p_khr_id := p_khr_id;
2625 lp_cashflow_rec.p_quote_id := p_quote_id;
2626 lp_cashflow_rec.p_amount := l_currpymtlines_rec.amount;
2627 lp_cashflow_rec.p_period_in_months := l_currpymtlines_rec.number_of_periods;
2628 lp_cashflow_rec.p_frequency := l_currpymtlines_rec.fqy_code;
2629 lp_cashflow_rec.p_seq_num := l_currpymtlines_rec.level_sequence;
2630 lp_cashflow_rec.p_stub_days := l_currpymtlines_rec.stub_days;
2631 lp_cashflow_rec.p_stub_amount := l_currpymtlines_rec.stub_amount;
2632
2633 create_cash_flows( p_api_version => p_api_version,
2634 x_msg_count => x_msg_count,
2635 x_msg_data => x_msg_data,
2636 p_cashflow_rec => lp_cashflow_rec,
2637 px_new_cash_flow => lx_new_cash_flow,
2638 x_return_status => l_return_status);
2639
2640 IF (is_debug_statement_on) THEN
2641 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
2642 'after call to create_cash_flows :'||l_return_status);
2643 END IF;
2644
2645
2646
2647 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2648 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2649 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2650 RAISE OKL_API.G_EXCEPTION_ERROR;
2651 END IF;
2652
2653 END LOOP;
2654 ---
2655
2656 END IF;
2657 END LOOP;
2658
2659 -------------------------- Get the proposed fee line payments ------------------------
2660 -- Get the current fee lines for which the payment exists
2661 FOR l_currpymtobjects_rec IN l_currpymtobjects_csr(G_FEE_LINE_OBJ_TYPE, p_quote_id) LOOP
2662 --Bug #3921591: pagarg +++ Rollover +++++++ Start ++++++++++
2663 -- Added the code to check for the associated assets and prorate the proposed fee line payments
2664 -- based on the Original Equipment Cost.
2665 l_count := 0;
2666
2667 l_total_curr_cost := 0;
2668 l_total_new_cost := 0;
2669 -- Get all the booked assets associated with the fee line
2670 FOR l_feelineassets_rec IN l_lineassets_csr(l_currpymtobjects_rec.base_source_id,
2671 G_LINKED_FEE_LINE_TYPE)
2672 LOOP
2673 l_count := l_count + 1 ;
2674 -- get the capitalize cost of the asset
2675 OKL_EXECUTE_FORMULA_PUB.EXECUTE(p_api_version => p_api_version,
2676 p_init_msg_list => OKL_API.G_FALSE,
2677 x_return_status => l_return_status,
2678 x_msg_count => x_msg_count,
2679 x_msg_data => x_msg_data,
2680 p_formula_name => 'LINE_CAP_AMNT',
2681 p_contract_id => p_khr_id,
2682 p_line_id => l_feelineassets_rec.object1_id1,
2683 x_value => l_curr_cap_cost);
2684
2685
2686
2687 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2688 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2689 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2690 RAISE OKL_API.G_EXCEPTION_ERROR;
2691 END IF;
2692 -- Check if asset belongs to the quote
2693 OPEN l_assetinquote_csr(p_quote_id, l_feelineassets_rec.object1_id1);
2694 FETCH l_assetinquote_csr INTO l_quote_line_id, l_asset_quantity, l_quote_quantity;
2695 IF l_assetinquote_csr%NOTFOUND THEN
2696 l_new_cap_cost := l_curr_cap_cost;
2697 ELSE
2698 IF l_asset_quantity = l_quote_quantity THEN
2699 l_new_cap_cost := 0;
2700 ELSE
2701 l_new_cap_cost := (l_curr_cap_cost / l_asset_quantity) * (l_asset_quantity - l_quote_quantity);
2702 END IF;
2703 END IF;
2704 CLOSE l_assetinquote_csr;
2705 l_total_curr_cost := l_total_curr_cost + l_curr_cap_cost ;
2706 l_total_new_cost := l_total_new_cost + l_new_cap_cost;
2707 END LOOP;
2708
2709 IF l_count > 0 THEN -- assets associated with fee line
2710 l_prorate_ratio := l_total_new_cost / l_total_curr_cost;
2711 IF l_prorate_ratio >= 0 AND l_prorate_ratio < 1 THEN
2712 calc_prop_line_payments(
2713 p_api_version => p_api_version,
2714 x_msg_count => x_msg_count,
2715 x_msg_data => x_msg_data,
2716 p_curr_cfo_id => l_currpymtobjects_rec.id,
2717 p_prop_obj_type_code => G_FEE_LINE_OBJ_TYPE,
2718 p_prop_base_source_id => l_currpymtobjects_rec.base_source_id,
2719 p_prorate_ratio => l_prorate_ratio,
2720 p_date_eff_from => p_date_eff_from,
2721 p_quote_id => p_quote_id,
2722 p_khr_id => p_khr_id ,
2723 x_return_status => l_return_status);
2724
2725 IF (is_debug_statement_on) THEN
2726 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
2727 'after call to calc_prop_line_payments :'||l_return_status);
2728 END IF;
2729
2730 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2731 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2732 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2733 RAISE OKL_API.G_EXCEPTION_ERROR;
2734 END IF;
2735 END IF;
2736 END IF;
2737
2738 -- if no assets are associated with the rollover fee line or none of the assets attached to the rollover fee line are quoted or all the assets are quoted with full quantity.
2739 IF l_count = 0 OR l_prorate_ratio = 1 THEN
2740 l_prev_sty_id := -99;
2741
2742 --Bug #3921591: pagarg +++ Rollover +++++++ End ++++++++++
2743 -- Following is the existing code i.e. if there is no asset associated to fee line
2744
2745 l_pymt_Count := 0;
2746 -- get the current payments for the fee line and store them as proposed payments
2747 FOR l_currpymtlines_rec IN l_currpymtlines_csr(l_currpymtobjects_rec.id) LOOP
2748 l_pymt_Count := l_pymt_Count + 1;
2749 IF l_pymt_count = 1 THEN -- line level payments exist
2750
2751 l_cfo_id := NULL;
2752
2753 create_cash_flow_object(
2754 p_api_version => p_api_version,
2755 x_msg_count => x_msg_count,
2756 x_msg_data => x_msg_data,
2757 p_obj_type_code => G_FEE_LINE_OBJ_TYPE,
2758 p_src_table => G_OBJECT_SRC_TABLE,
2759 p_src_id => p_quote_id,
2760 p_base_src_id => l_currpymtobjects_rec.base_source_id,
2761 p_sts_code => G_PROPOSED_STATUS,
2762 x_cfo_id => l_cfo_id,
2763 x_return_status => l_return_status);
2764
2765 IF (is_debug_statement_on) THEN
2766 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
2767 'after call to create_cash_flow_object :'||l_return_status);
2768 END IF;
2769
2770 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2771 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2772 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2773 RAISE OKL_API.G_EXCEPTION_ERROR;
2774 END IF;
2775
2776 lp_qcov_rec.qte_id := p_quote_id;
2777 lp_qcov_rec.cfo_id := l_cfo_id;
2778 lp_qcov_rec.BASE_SOURCE_ID := l_currpymtobjects_rec.base_source_id;
2779 --Bug 4299668 PAGARG Instead of calling the procedure to insert
2780 --quote cash flow object, store the record in the table
2781 --**START**--
2782 g_qcov_counter := g_qcov_counter + 1;
2783 g_qcov_tbl_type(g_qcov_counter) := lp_qcov_rec;
2784 --**END 4299668**--
2785 END IF;
2786
2787 IF l_currpymtlines_rec.sty_id <> l_prev_sty_id THEN
2788 lx_new_cash_flow := 'N';
2789 l_prev_sty_id := l_currpymtlines_rec.sty_id;
2790 END IF;
2791
2792 lp_cashflow_rec.p_cfo_id := l_cfo_id;
2793 lp_cashflow_rec.p_sts_code := G_PROPOSED_STATUS;
2794 lp_cashflow_rec.p_sty_id := l_currpymtlines_rec.sty_id;
2795 lp_cashflow_rec.p_due_arrears_yn := l_currpymtlines_rec.due_arrears_yn;
2796 lp_cashflow_rec.p_start_date := l_currpymtlines_rec.start_date;
2797 lp_cashflow_rec.p_advance_periods := l_currpymtlines_rec.number_of_advance_periods;
2798 lp_cashflow_rec.p_khr_id := p_khr_id;
2799 lp_cashflow_rec.p_quote_id := p_quote_id;
2800 lp_cashflow_rec.p_amount := l_currpymtlines_rec.amount;
2801 lp_cashflow_rec.p_period_in_months := l_currpymtlines_rec.number_of_periods;
2802 lp_cashflow_rec.p_frequency := l_currpymtlines_rec.fqy_code;
2803 lp_cashflow_rec.p_seq_num := l_currpymtlines_rec.level_sequence;
2804 lp_cashflow_rec.p_stub_days := l_currpymtlines_rec.stub_days;
2805 lp_cashflow_rec.p_stub_amount := l_currpymtlines_rec.stub_amount;
2806
2807 create_cash_flows( p_api_version => p_api_version,
2808 x_msg_count => x_msg_count,
2809 x_msg_data => x_msg_data,
2810 p_cashflow_rec => lp_cashflow_rec,
2811 px_new_cash_flow => lx_new_cash_flow,
2812 x_return_status => l_return_status);
2813
2814 IF (is_debug_statement_on) THEN
2815 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
2816 'after call to create_cash_flows :'||l_return_status);
2817 END IF;
2818
2819 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2820 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2821 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2822 RAISE OKL_API.G_EXCEPTION_ERROR;
2823 END IF;
2824
2825 END LOOP;
2826 END IF;
2827 END LOOP;
2828 ----------------------------- end proposed fee line payments ---------------------------------
2829
2830 -------------------------- Get the proposed service asset line payments ------------------------
2831 -- get all the service asset lines for which current payment exists
2832 FOR l_currpymtobjects_rec IN l_currpymtobjects_csr(G_SERV_ASSET_OBJ_TYPE, p_quote_id) LOOP
2833
2834 --Bug #3921591: pagarg +++ Rollover +++
2835 -- Modified the cursor call to pass line type also
2836 -- get the financial asset associated with the subline
2837 OPEN l_finasset_csr(l_currpymtobjects_rec.base_source_id, G_LINKED_SERVICE_LINE_TYPE);
2838 FETCH l_finasset_csr INTO l_fin_asset_id;
2839 CLOSE l_finasset_csr;
2840
2841 -- Check if asset belongs to the quote
2842 OPEN l_assetinquote_csr(p_quote_id, l_fin_asset_id);
2843 FETCH l_assetinquote_csr INTO l_quote_line_id, l_asset_quantity, l_quote_quantity;
2844 IF l_assetinquote_csr%FOUND THEN
2845
2846 l_prorate_ratio := ( l_asset_quantity - l_quote_quantity ) / l_asset_quantity;
2847
2848 IF l_prorate_ratio >= 0 AND l_prorate_ratio < 1 THEN
2849
2850 calc_prop_line_payments( p_api_version => p_api_version,
2851 x_msg_count => x_msg_count,
2852 x_msg_data => x_msg_data,
2853 p_curr_cfo_id => l_currpymtobjects_rec.id,
2854 p_prop_obj_type_code => G_SERV_ASSET_OBJ_TYPE,
2855 p_prop_base_source_id => l_currpymtobjects_rec.base_source_id,
2856 p_prorate_ratio => l_prorate_ratio,
2857 p_date_eff_from => p_date_eff_from,
2858 p_quote_id => p_quote_id,
2859 p_khr_id => p_khr_id ,
2860 x_return_status => l_return_status);
2861
2862 IF (is_debug_statement_on) THEN
2863 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
2864 'after call to calc_prop_line_payments :'||l_return_status);
2865 END IF;
2866
2867 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2868 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2869 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2870 RAISE OKL_API.G_EXCEPTION_ERROR;
2871 END IF;
2872
2873 END IF;
2874
2875 ELSE
2876
2877 l_prev_sty_id := -99;
2878 l_pymt_Count := 0;
2879 -- get the current payments for teh service line subline and store them as proposed payments
2880 FOR l_currpymtlines_rec IN l_currpymtlines_csr(l_currpymtobjects_rec.id) LOOP
2881 l_pymt_Count := l_pymt_Count + 1;
2882 IF l_pymt_count = 1 THEN -- line level payments exist
2883
2884 l_cfo_id := NULL;
2885 create_cash_flow_object(
2886 p_api_version => p_api_version,
2887 x_msg_count => x_msg_count,
2888 x_msg_data => x_msg_data,
2889 p_obj_type_code => G_SERV_ASSET_OBJ_TYPE,
2890 p_src_table => G_OBJECT_SRC_TABLE,
2891 p_src_id => p_quote_id,
2892 p_base_src_id => l_currpymtobjects_rec.base_source_id,
2893 p_sts_code => G_PROPOSED_STATUS,
2894 x_cfo_id => l_cfo_id,
2895 x_return_status => l_return_status);
2896
2897 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2898 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2899 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2900 RAISE OKL_API.G_EXCEPTION_ERROR;
2901 END IF;
2902
2903 -- Store Objects in okl_quote_cf_objects
2904 lp_qcov_rec.qte_id := p_quote_id;
2905 lp_qcov_rec.cfo_id := l_cfo_id;
2906 lp_qcov_rec.BASE_SOURCE_ID := l_currpymtobjects_rec.base_source_id;
2907 --Bug 4299668 PAGARG Instead of calling the procedure to insert
2908 --quote cash flow object, store the record in the table
2909 --**START**--
2910 g_qcov_counter := g_qcov_counter + 1;
2911 g_qcov_tbl_type(g_qcov_counter) := lp_qcov_rec;
2912 --**END 4299668**--
2913 END IF;
2914
2915 IF l_currpymtlines_rec.sty_id <> l_prev_sty_id THEN
2916 lx_new_cash_flow := 'N';
2917 l_prev_sty_id := l_currpymtlines_rec.sty_id;
2918 END IF;
2919
2920 lp_cashflow_rec.p_cfo_id := l_cfo_id;
2921 lp_cashflow_rec.p_sts_code := G_PROPOSED_STATUS;
2922 lp_cashflow_rec.p_sty_id := l_currpymtlines_rec.sty_id;
2923 lp_cashflow_rec.p_due_arrears_yn := l_currpymtlines_rec.due_arrears_yn;
2924 lp_cashflow_rec.p_start_date := l_currpymtlines_rec.start_date;
2925 lp_cashflow_rec.p_advance_periods := l_currpymtlines_rec.number_of_advance_periods;
2926 lp_cashflow_rec.p_khr_id := p_khr_id;
2927 lp_cashflow_rec.p_quote_id := p_quote_id;
2928 lp_cashflow_rec.p_amount := l_currpymtlines_rec.amount;
2929 lp_cashflow_rec.p_period_in_months := l_currpymtlines_rec.number_of_periods;
2930 lp_cashflow_rec.p_frequency := l_currpymtlines_rec.fqy_code;
2931 lp_cashflow_rec.p_seq_num := l_currpymtlines_rec.level_sequence;
2932 lp_cashflow_rec.p_stub_days := l_currpymtlines_rec.stub_days;
2933 lp_cashflow_rec.p_stub_amount := l_currpymtlines_rec.stub_amount;
2934
2935 create_cash_flows( p_api_version => p_api_version,
2936 x_msg_count => x_msg_count,
2937 x_msg_data => x_msg_data,
2938 p_cashflow_rec => lp_cashflow_rec,
2939 px_new_cash_flow => lx_new_cash_flow,
2940 x_return_status => l_return_status);
2941
2942 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2943 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2944 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2945 RAISE OKL_API.G_EXCEPTION_ERROR;
2946 END IF;
2947
2948 END LOOP;
2949 ---------------
2950 END IF;
2951 CLOSE l_assetinquote_csr;
2952 END LOOP;
2953
2954 --Bug #3921591: pagarg +++ Rollover +++++++ Start ++++++++++
2955 ------------------ Get the proposed fee asset line payments ------------------
2956 -- get all the fee asset lines for which current payment exists
2957
2958 FOR l_currpymtobjects_rec IN l_currpymtobjects_csr(G_FEE_ASSET_OBJ_TYPE, p_quote_id)
2959 LOOP
2960 -- get the financial asset associated with the subline
2961 OPEN l_finasset_csr(l_currpymtobjects_rec.base_source_id, G_LINKED_FEE_LINE_TYPE);
2962 FETCH l_finasset_csr INTO l_fin_asset_id;
2963 CLOSE l_finasset_csr;
2964 -- Check if asset belongs to the quote
2965 OPEN l_assetinquote_csr(p_quote_id, l_fin_asset_id);
2966 FETCH l_assetinquote_csr INTO l_quote_line_id, l_asset_quantity, l_quote_quantity;
2967 IF l_assetinquote_csr%FOUND THEN
2968 l_prorate_ratio := ( l_asset_quantity - l_quote_quantity ) / l_asset_quantity;
2969 IF l_prorate_ratio >= 0 AND l_prorate_ratio < 1 THEN
2970 calc_prop_line_payments(
2971 p_api_version => p_api_version,
2972 x_msg_count => x_msg_count,
2973 x_msg_data => x_msg_data,
2974 p_curr_cfo_id => l_currpymtobjects_rec.id,
2975 p_prop_obj_type_code => G_FEE_ASSET_OBJ_TYPE,
2976 p_prop_base_source_id => l_currpymtobjects_rec.base_source_id,
2977 p_prorate_ratio => l_prorate_ratio,
2978 p_date_eff_from => p_date_eff_from,
2979 p_quote_id => p_quote_id,
2980 p_khr_id => p_khr_id ,
2981 x_return_status => l_return_status);
2982 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2983 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2984 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2985 RAISE OKL_API.G_EXCEPTION_ERROR;
2986 END IF;
2987 END IF;
2988 ELSE
2989 l_prev_sty_id := -99;
2990 l_pymt_Count := 0;
2991 -- get the current payments for the fee line subline and store them as
2992 -- proposed payments
2993 FOR l_currpymtlines_rec IN l_currpymtlines_csr(l_currpymtobjects_rec.id)
2994 LOOP
2995 l_pymt_Count := l_pymt_Count + 1;
2996 IF l_pymt_count = 1 THEN -- line level payments exist
2997 l_cfo_id := NULL;
2998 create_cash_flow_object(
2999 p_api_version => p_api_version,
3000 x_msg_count => x_msg_count,
3001 x_msg_data => x_msg_data,
3002 p_obj_type_code => G_FEE_ASSET_OBJ_TYPE,
3003 p_src_table => G_OBJECT_SRC_TABLE,
3004 p_src_id => p_quote_id,
3005 p_base_src_id => l_currpymtobjects_rec.base_source_id,
3006 p_sts_code => G_PROPOSED_STATUS,
3007 x_cfo_id => l_cfo_id,
3008 x_return_status => l_return_status);
3009 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3010 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3011 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
3012 RAISE OKL_API.G_EXCEPTION_ERROR;
3013 END IF;
3014 -- Store Objects in okl_quote_cf_objects
3015 lp_qcov_rec.qte_id := p_quote_id;
3016 lp_qcov_rec.cfo_id := l_cfo_id;
3017 lp_qcov_rec.BASE_SOURCE_ID := l_currpymtobjects_rec.base_source_id;
3018 --Bug 4299668 PAGARG Instead of calling the procedure to insert
3019 --quote cash flow object, store the record in the table
3020 --**START**--
3021 g_qcov_counter := g_qcov_counter + 1;
3022 g_qcov_tbl_type(g_qcov_counter) := lp_qcov_rec;
3023 --**END 4299668**--
3024 END IF;
3025 IF l_currpymtlines_rec.sty_id <> l_prev_sty_id THEN
3026 lx_new_cash_flow := 'N';
3027 l_prev_sty_id := l_currpymtlines_rec.sty_id;
3028 END IF;
3029 lp_cashflow_rec.p_cfo_id := l_cfo_id;
3030 lp_cashflow_rec.p_sts_code := G_PROPOSED_STATUS;
3031 lp_cashflow_rec.p_sty_id := l_currpymtlines_rec.sty_id;
3032 lp_cashflow_rec.p_due_arrears_yn := l_currpymtlines_rec.due_arrears_yn;
3033 lp_cashflow_rec.p_start_date := l_currpymtlines_rec.start_date;
3034 lp_cashflow_rec.p_advance_periods := l_currpymtlines_rec.number_of_advance_periods;
3035 lp_cashflow_rec.p_khr_id := p_khr_id;
3036 lp_cashflow_rec.p_quote_id := p_quote_id;
3037 lp_cashflow_rec.p_amount := l_currpymtlines_rec.amount;
3038 lp_cashflow_rec.p_period_in_months := l_currpymtlines_rec.number_of_periods;
3039 lp_cashflow_rec.p_frequency := l_currpymtlines_rec.fqy_code;
3040 lp_cashflow_rec.p_seq_num := l_currpymtlines_rec.level_sequence;
3041 lp_cashflow_rec.p_stub_days := l_currpymtlines_rec.stub_days;
3042 lp_cashflow_rec.p_stub_amount := l_currpymtlines_rec.stub_amount;
3043 create_cash_flows(
3044 p_api_version => p_api_version,
3045 x_msg_count => x_msg_count,
3046 x_msg_data => x_msg_data,
3047 p_cashflow_rec => lp_cashflow_rec,
3048 px_new_cash_flow => lx_new_cash_flow,
3049 x_return_status => l_return_status);
3050 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3051 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3052 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
3053 RAISE OKL_API.G_EXCEPTION_ERROR;
3054 END IF;
3055 END LOOP;
3056 END IF;
3057 CLOSE l_assetinquote_csr;
3058 END LOOP;
3059
3060 --Bug 4299668 PAGARG All the four object table of records is populated for
3061 --current payment. Now call proceure for bulk insert.
3062 --**START**--
3063 okl_cfo_pvt.insert_row_bulk(p_api_version => p_api_version,
3064 p_init_msg_list => OKL_API.G_FALSE,
3065 x_return_status => l_return_status,
3066 x_msg_count => x_msg_count,
3067 x_msg_data => x_msg_data,
3068 p_cfov_tbl => g_cfov_tbl_type,
3069 x_cfov_tbl => gx_cfov_tbl_type);
3070
3071 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3072 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3073 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
3074 RAISE OKL_API.G_EXCEPTION_ERROR;
3075 END IF;
3076
3077 OKL_QCO_PVT.insert_row_bulk(p_api_version => p_api_version,
3078 p_init_msg_list => OKL_API.G_FALSE,
3079 x_return_status => l_return_status,
3080 x_msg_count => x_msg_count,
3081 x_msg_data => x_msg_data,
3082 p_qcov_tbl => g_qcov_tbl_type,
3083 x_qcov_tbl => gx_qcov_tbl_type);
3084
3085 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3086 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3087 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
3088 RAISE OKL_API.G_EXCEPTION_ERROR;
3089 END IF;
3090
3091 okl_caf_pvt.insert_row_bulk(p_api_version => p_api_version,
3092 p_init_msg_list => OKL_API.G_FALSE,
3093 x_return_status => l_return_status,
3094 x_msg_count => x_msg_count,
3095 x_msg_data => x_msg_data,
3096 p_cafv_tbl => g_cafv_tbl_type,
3097 x_cafv_tbl => gx_cafv_tbl_type);
3098
3099 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3100 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3101 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
3102 RAISE OKL_API.G_EXCEPTION_ERROR;
3103 END IF;
3104
3105 OKL_CFL_PVT.insert_row_bulk(p_api_version => p_api_version,
3106 p_init_msg_list => OKL_API.G_FALSE,
3107 x_return_status => l_return_status,
3108 x_msg_count => x_msg_count,
3109 x_msg_data => x_msg_data,
3110 p_cflv_tbl => g_cflv_tbl_type,
3111 x_cflv_tbl => gx_cflv_tbl_type);
3112 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3113 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3114 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
3115 RAISE OKL_API.G_EXCEPTION_ERROR;
3116 END IF;
3117 --**END 4299668**--
3118
3119 -- set the return status and out variables
3120 x_return_status := l_return_status;
3121 --Bug #3921591: pagarg +++ Rollover +++++++ End ++++++++++
3122
3123 IF (is_debug_procedure_on) THEN
3124 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'End(-)');
3125 END IF;
3126
3127 EXCEPTION
3128 WHEN OKL_API.G_EXCEPTION_ERROR THEN
3129
3130 IF (is_debug_exception_on) THEN
3131 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_EXCEPTION,L_MODULE_NAME,
3132 'EXCEPTION :'||'OKL_API.G_EXCEPTION_ERROR');
3133 END IF;
3134
3135 IF l_currpymtlines_csr%ISOPEN THEN
3136 CLOSE l_currpymtlines_csr;
3137 END IF;
3138
3139 --Bug #3921591: pagarg +++ Rollover +++
3140 -- Changed the cursor name as made it generalised
3141 IF l_lineassets_csr%ISOPEN THEN
3142 CLOSE l_lineassets_csr;
3143 END IF;
3144 IF l_assetinquote_csr%ISOPEN THEN
3145 CLOSE l_assetinquote_csr;
3146 END IF;
3147 IF l_finasset_csr%ISOPEN THEN
3148 CLOSE l_finasset_csr;
3149 END IF;
3150 IF l_currpymtobjects_csr%ISOPEN THEN
3151 CLOSE l_currpymtobjects_csr;
3152 END IF;
3153 x_return_status := OKL_API.G_RET_STS_ERROR;
3154 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3155
3156 IF (is_debug_exception_on) THEN
3157 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_EXCEPTION,L_MODULE_NAME,
3158 'EXCEPTION :'||'OKL_API.G_EXCEPTION_UNEXPECTED_ERROR');
3159 END IF;
3160
3161 IF l_currpymtlines_csr%ISOPEN THEN
3162 CLOSE l_currpymtlines_csr;
3163 END IF;
3164
3165 --Bug #3921591: pagarg +++ Rollover +++
3166 -- Changed the cursor name as made it generalised
3167 IF l_lineassets_csr%ISOPEN THEN
3168 CLOSE l_lineassets_csr;
3169 END IF;
3170 IF l_assetinquote_csr%ISOPEN THEN
3171 CLOSE l_assetinquote_csr;
3172 END IF;
3173 IF l_finasset_csr%ISOPEN THEN
3174 CLOSE l_finasset_csr;
3175 END IF;
3176 IF l_currpymtobjects_csr%ISOPEN THEN
3177 CLOSE l_currpymtobjects_csr;
3178 END IF;
3179 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
3180 WHEN OTHERS THEN
3181
3182 IF (is_debug_exception_on) THEN
3183 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_EXCEPTION,L_MODULE_NAME,
3184 'EXCEPTION :'||sqlerrm);
3185 END IF;
3186
3187 IF l_currpymtlines_csr%ISOPEN THEN
3188 CLOSE l_currpymtlines_csr;
3189 END IF;
3190
3191 --Bug #3921591: pagarg +++ Rollover +++
3192 -- Changed the cursor name as made it generalised
3193 IF l_lineassets_csr%ISOPEN THEN
3194 CLOSE l_lineassets_csr;
3195 END IF;
3196 IF l_assetinquote_csr%ISOPEN THEN
3197 CLOSE l_assetinquote_csr;
3198 END IF;
3199 IF l_finasset_csr%ISOPEN THEN
3200 CLOSE l_finasset_csr;
3201 END IF;
3202 IF l_currpymtobjects_csr%ISOPEN THEN
3203 CLOSE l_currpymtobjects_csr;
3204 END IF;
3205 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
3206 -- unexpected error
3207 OKL_API.set_message(p_app_name => g_app_name,
3208 p_msg_name => g_unexpected_error,
3209 p_token1 => g_sqlcode_token,
3210 p_token1_value => sqlcode,
3211 p_token2 => g_sqlerrm_token,
3212 p_token2_value => sqlerrm);
3213 END calc_proposed_payments;
3214
3215
3216 /*========================================================================
3217 | PUBLIC PROCEDURE calc_quote_payments
3218 |
3219 | DESCRIPTION
3220 | This is the public procedure caleld from the quote creation screen to calculate
3221 | revised payments for a partial termination quote
3222 |
3223 | CALLED FROM PROCEDURES/FUNCTIONS
3224 |
3225 |
3226 | CALLS PROCEDURES/FUNCTIONS
3227 | get_current_payments, calc_proposed_payments
3228 |
3229 | PARAMETERS
3230 | p_quote_id IN Quote ID
3231 |
3232 | KNOWN ISSUES
3233 |
3234 | NOTES
3235 |
3236 |
3237 | MODIFICATION HISTORY
3238 | Date Author Description of Changes
3239 | 14-OCT-2003 SECHAWLA Created
3240 |
3241 *=======================================================================*/
3242 PROCEDURE calc_quote_payments(
3243 p_api_version IN NUMBER,
3244 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
3245 x_return_status OUT NOCOPY VARCHAR2,
3246 x_msg_count OUT NOCOPY NUMBER,
3247 x_msg_data OUT NOCOPY VARCHAR2,
3248 p_quote_id IN NUMBER) IS
3249
3250
3251 /*-----------------------------------------------------------------------+
3252 | Cursor Declarations |
3253 +-----------------------------------------------------------------------*/
3254
3255 -- Get the quote effective from date
3256 CURSOR l_quotehdr_csr(cp_id IN NUMBER) IS
3257 SELECT khr_id, trunc(date_effective_from)
3258 FROM okl_trx_quotes_b
3259 WHERE id = cp_id;
3260
3261 /*-----------------------------------------------------------------------+
3262 | Local Variable Declarations and initializations |
3263 +-----------------------------------------------------------------------*/
3264
3265 l_api_version CONSTANT NUMBER := 1;
3266 l_api_name CONSTANT VARCHAR2(30) := 'calc_quote_payments';
3267 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
3268
3269 l_khr_id NUMBER;
3270 l_date_eff_from DATE;
3271 L_MODULE_NAME VARCHAR2(500) := G_MODULE_NAME||'calc_quote_payments';
3272 is_debug_exception_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_EXCEPTION);
3273 is_debug_procedure_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_PROCEDURE);
3274 is_debug_statement_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_STATEMENT);
3275 BEGIN
3276
3277 IF (is_debug_procedure_on) THEN
3278 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'Begin(+)');
3279 END IF;
3280
3281
3282 --Print Input Variables
3283 IF (is_debug_statement_on) THEN
3284 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
3285 'p_quote_id :'||p_quote_id);
3286 END IF;
3287
3288 --Check API version, initialize message list and create savepoint.
3289 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
3290 G_PKG_NAME,
3291 p_init_msg_list,
3292 l_api_version,
3293 p_api_version,
3294 '_PVT',
3295 x_return_status);
3296
3297 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3298 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3299 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
3300 RAISE OKL_API.G_EXCEPTION_ERROR;
3301 END IF;
3302
3303 IF p_quote_id IS NULL OR p_quote_id = OKL_API.G_MISS_NUM THEN
3304 x_return_status := OKL_API.G_RET_STS_ERROR;
3305 -- quote id is required
3306 OKC_API.set_message( p_app_name => 'OKC',
3307 p_msg_name => G_REQUIRED_VALUE,
3308 p_token1 => G_COL_NAME_TOKEN,
3309 p_token1_value => 'QUOTE_ID');
3310 RAISE OKL_API.G_EXCEPTION_ERROR;
3311 END IF;
3312
3313
3314 OPEN l_quotehdr_csr(p_quote_id);
3315 FETCH l_quotehdr_csr INTO l_khr_id, l_date_eff_from;
3316 IF l_quotehdr_csr%NOTFOUND THEN
3317 -- quote ID is invalid
3318 x_return_status := OKL_API.G_RET_STS_ERROR;
3319 OKC_API.set_message( p_app_name => 'OKC',
3320 p_msg_name => G_INVALID_VALUE,
3321 p_token1 => G_COL_NAME_TOKEN,
3322 p_token1_value => 'QUOTE_ID');
3323
3324 RAISE OKL_API.G_EXCEPTION_ERROR;
3325 END IF;
3326 CLOSE l_quotehdr_csr;
3327
3328 --Bug 4299668 PAGARG Reset the global table of records for the four objects
3329 --and reset the Counter
3330 --**START**--
3331 g_cfov_tbl_type.delete;
3332 g_cafv_tbl_type.delete;
3333 g_cflv_tbl_type.delete;
3334 g_qcov_tbl_type.delete;
3335 g_cfov_counter := 0;
3336 g_cafv_counter := 0;
3337 g_cflv_counter := 0;
3338 g_qcov_counter := 0;
3339 --**END 4299668**--
3340
3341 get_current_payments(
3342 p_api_version => p_api_version,
3343 p_init_msg_list => OKL_API.G_FALSE,
3344 x_return_status => l_return_status,
3345 x_msg_count => x_msg_count,
3346 x_msg_data => x_msg_data,
3347 p_quote_id => p_quote_id,
3348 p_khr_id => l_khr_id);
3349
3350 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3351 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3352 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
3353 RAISE OKL_API.G_EXCEPTION_ERROR;
3354 END IF;
3355
3356 --Bug 4299668 PAGARG Reset the global table of records for the four objects
3357 --and reset the Counter
3358 --**START**--
3359 g_cfov_tbl_type.delete;
3360 g_cafv_tbl_type.delete;
3361 g_cflv_tbl_type.delete;
3362 g_qcov_tbl_type.delete;
3363 g_cfov_counter := 0;
3364 g_cafv_counter := 0;
3365 g_cflv_counter := 0;
3366 g_qcov_counter := 0;
3367 --**END 4299668**--
3368
3369 calc_proposed_payments(
3370 p_api_version => p_api_version,
3371 p_init_msg_list => OKL_API.G_FALSE,
3372 x_return_status => l_return_status,
3373 x_msg_count => x_msg_count,
3374 x_msg_data => x_msg_data,
3375 p_quote_id => p_quote_id,
3376 p_khr_id => l_khr_id,
3377 p_date_eff_from => l_date_eff_from);
3378
3379 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3380 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3381 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
3382 RAISE OKL_API.G_EXCEPTION_ERROR;
3383 END IF;
3384
3385 x_return_status := l_return_status;
3386
3387 -- end the transaction
3388 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
3389
3390 IF (is_debug_procedure_on) THEN
3391 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'End(-)');
3392 END IF;
3393
3394 EXCEPTION
3395 WHEN OKL_API.G_EXCEPTION_ERROR THEN
3396
3397 IF (is_debug_exception_on) THEN
3398 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_EXCEPTION,L_MODULE_NAME,
3399 'EXCEPTION :'||'OKL_API.G_EXCEPTION_ERROR');
3400 END IF;
3401
3402 IF l_quotehdr_csr%ISOPEN THEN
3403 CLOSE l_quotehdr_csr;
3404 END IF;
3405 x_return_status := OKL_API.HANDLE_EXCEPTIONS
3406 (
3407 l_api_name,
3408 G_PKG_NAME,
3409 'OKL_API.G_RET_STS_ERROR',
3410 x_msg_count,
3411 x_msg_data,
3412 '_PVT'
3413 );
3414 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3415 IF (is_debug_exception_on) THEN
3416 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_EXCEPTION,L_MODULE_NAME,
3417 'EXCEPTION :'||'OKL_API.G_EXCEPTION_UNEXPECTED_ERROR');
3418 END IF;
3419
3420 IF l_quotehdr_csr%ISOPEN THEN
3421 CLOSE l_quotehdr_csr;
3422 END IF;
3423 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
3424 (
3425 l_api_name,
3426 G_PKG_NAME,
3427 'OKL_API.G_RET_STS_UNEXP_ERROR',
3428 x_msg_count,
3429 x_msg_data,
3430 '_PVT'
3431 );
3432 WHEN OTHERS THEN
3433
3434 IF (is_debug_exception_on) THEN
3435 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_EXCEPTION,L_MODULE_NAME,
3436 'EXCEPTION :'||sqlerrm);
3437 END IF;
3438
3439 IF l_quotehdr_csr%ISOPEN THEN
3440 CLOSE l_quotehdr_csr;
3441 END IF;
3442 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
3443 (
3444 l_api_name,
3445 G_PKG_NAME,
3446 'OTHERS',
3447 x_msg_count,
3448 x_msg_data,
3449 '_PVT'
3450 );
3451
3452 END calc_quote_payments;
3453
3454 END OKL_AM_CALC_QUOTE_PYMNT_PVT;