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