[Home] [Help]
PACKAGE BODY: APPS.OKL_LEASE_QUOTE_CASHFLOW_PVT
Source
1 PACKAGE BODY OKL_LEASE_QUOTE_CASHFLOW_PVT AS
2 /* $Header: OKLRQUCB.pls 120.30.12010000.7 2009/11/05 20:02:15 sechawla ship $ */
3
4 -------------------------------
5 -- PROCEDURE populate_level_ids
6 -------------------------------
7 PROCEDURE populate_level_ids (
8 p_cf_header_id IN NUMBER
9 ,x_cf_levels_tbl IN OUT NOCOPY cashflow_level_tbl_type
10 ,x_return_status OUT NOCOPY VARCHAR2
11 ) IS
12
13 l_program_name CONSTANT VARCHAR2(30) := 'populate_level_ids';
14 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
15
16 CURSOR c_levels IS
17 SELECT id
18 FROM okl_cash_flow_levels
19 WHERE caf_id = p_cf_header_id
20 ORDER BY start_date;
21
22 i BINARY_INTEGER;
23 l_count BINARY_INTEGER;
24
25 BEGIN
26
27 i := x_cf_levels_tbl.FIRST;
28 l_count := 0;
29
30 FOR l_level IN c_levels LOOP
31
32 l_count := l_count + 1;
33 x_cf_levels_tbl(i).cashflow_level_id := l_level.id;
34 i := x_cf_levels_tbl.NEXT(i);
35
36 END LOOP;
37
38 IF l_count <> x_cf_levels_tbl.COUNT THEN
39
40 OKL_API.SET_MESSAGE (
41 p_app_name => G_APP_NAME
42 ,p_msg_name => 'OKL_INVALID_VALUE2'
43 ,p_token1 => 'API_NAME'
44 ,p_token1_value => UPPER(l_api_name)
45 ,p_token2 => 'NAME'
46 ,p_token2_value => 'levels_table_count'
47 ,p_token3 => 'VALUE'
48 ,p_token3_value => x_cf_levels_tbl.COUNT
49 );
50 RAISE OKL_API.G_EXCEPTION_ERROR;
51
52 END IF;
53
54 x_return_status := G_RET_STS_SUCCESS;
55
56 EXCEPTION
57
58 WHEN OKL_API.G_EXCEPTION_ERROR THEN
59 x_return_status := G_RET_STS_ERROR;
60
61 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
62 x_return_status := G_RET_STS_UNEXP_ERROR;
63
64 WHEN OTHERS THEN
65 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
66 p_msg_name => G_DB_ERROR,
67 p_token1 => G_PROG_NAME_TOKEN,
68 p_token1_value => l_api_name,
69 p_token2 => G_SQLCODE_TOKEN,
70 p_token2_value => sqlcode,
71 p_token3 => G_SQLERRM_TOKEN,
72 p_token3_value => sqlerrm);
73
74 x_return_status := G_RET_STS_UNEXP_ERROR;
75
76 END populate_level_ids;
77
78
79 -----------------------------
80 -- PROCEDURE get_source_table
81 -----------------------------
82 PROCEDURE get_source_table (
83 p_source_object_code IN VARCHAR2
84 ,x_source_table OUT NOCOPY VARCHAR2
85 ,x_return_status OUT NOCOPY VARCHAR2
86 ) IS
87
88 l_program_name CONSTANT VARCHAR2(30) := 'get_source_table';
89 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
90
91 BEGIN
92 --Bug # 5142940 ssdeshpa start
93 IF (p_source_object_code IN ('QUOTED_ASSET','QUOTED_ASSET_DOWN_PAYMENT','QUOTED_ASSET_PROPERTY_TAX')) THEN
94 x_source_table := 'OKL_ASSETS_B';
95 --Bug # 5142940 ssdeshpa end
96 ELSIF (p_source_object_code = 'QUOTED_FEE') THEN
97 x_source_table := 'OKL_FEES_B';
98 ELSIF (p_source_object_code = 'QUOTED_SERVICE') THEN
99 x_source_table := 'OKL_SERVICES_B';
100 ELSIF (p_source_object_code = 'QUOTED_INSURANCE') THEN
101 x_source_table := 'OKL_INSURANCE_ESTIMATES_B';
102 ELSIF (p_source_object_code = 'LEASE_QUOTE') THEN
103 x_source_table := 'OKL_LEASE_QUOTES_B';
104 ELSIF (p_source_object_code = 'QUICK_QUOTE') THEN
105 x_source_table := 'OKL_QUICK_QUOTES_B';
106 ELSIF (p_source_object_code = 'QUICK_QUOTE_ASSET') THEN
107 x_source_table := 'OKL_QUICK_QUOTE_LINES_B';
108 ELSIF (p_source_object_code = 'QUICK_QUOTE_SERVICE') THEN
109 x_source_table := 'OKL_QUICK_QUOTE_LINES_B';
110 ELSIF (p_source_object_code = 'QUICK_QUOTE_FEE') THEN
111 x_source_table := 'OKL_QUICK_QUOTE_LINES_B';
112 ELSIF (p_source_object_code = 'QUICK_QUOTE_TAX') THEN
113 x_source_table := 'OKL_QUICK_QUOTE_LINES_B';
114 ELSIF (p_source_object_code = 'QUICK_QUOTE_INSURANCE') THEN
115 x_source_table := 'OKL_QUICK_QUOTE_LINES_B';
116 ELSIF (p_source_object_code = 'LEASE_QUOTE_CONSOLIDATED') THEN
117 x_source_table := 'OKL_LEASE_QUOTES_B';
118 ELSE
119 OKL_API.SET_MESSAGE (
120 p_app_name => G_APP_NAME
121 ,p_msg_name => 'OKL_INVALID_VALUE2'
122 ,p_token1 => 'API_NAME'
123 ,p_token1_value => UPPER(l_api_name)
124 ,p_token2 => 'NAME'
125 ,p_token2_value => 'source_object_code'
126 ,p_token3 => 'VALUE'
127 ,p_token3_value => p_source_object_code
128 );
129 RAISE OKL_API.G_EXCEPTION_ERROR;
130 END IF;
131
132 x_return_status := G_RET_STS_SUCCESS;
133
134 EXCEPTION
135
136 WHEN OKL_API.G_EXCEPTION_ERROR THEN
137 x_return_status := G_RET_STS_ERROR;
138
139 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
140 x_return_status := G_RET_STS_UNEXP_ERROR;
141
142 WHEN OTHERS THEN
143 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
144 p_msg_name => G_DB_ERROR,
145 p_token1 => G_PROG_NAME_TOKEN,
146 p_token1_value => l_api_name,
147 p_token2 => G_SQLCODE_TOKEN,
148 p_token2_value => sqlcode,
149 p_token3 => G_SQLERRM_TOKEN,
150 p_token3_value => sqlerrm);
151
152 x_return_status := G_RET_STS_UNEXP_ERROR;
153
154 END get_source_table;
155
156 -------------------------------
157 -- PROCEDURE set_level_end_date
158 -------------------------------
159 PROCEDURE set_level_end_date (
160 p_contract_start_date IN DATE
161 ,p_contract_term IN NUMBER
162 ,p_cashflow_header_rec IN cashflow_header_rec_type
163 ,p_cashflow_level_tbl IN OUT NOCOPY cashflow_level_tbl_type
164 ,x_return_status OUT NOCOPY VARCHAR2
165 ) IS
166
167 l_program_name CONSTANT VARCHAR2(30) := 'set_level_end_date';
168 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
169
170 l_mpp PLS_INTEGER;
171
172 l_contract_end_date DATE;
173 l_next_start_date DATE;
174 l_end_date DATE;
175
176 l_module CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.OKL_LEASE_QUOTE_CASHFLOW_PVT.set_level_end_date';
177 l_debug_enabled VARCHAR2(10);
178 is_debug_procedure_on BOOLEAN;
179 is_debug_statement_on BOOLEAN;
180
181 BEGIN
182
183 l_debug_enabled := NVL(okl_debug_pub.check_log_enabled,'N');
184 is_debug_procedure_on := okl_debug_pub.check_log_on(l_module
185 ,fnd_log.level_procedure);
186
187 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
188 okl_debug_pub.log_debug(fnd_log.level_procedure
189 ,l_module
190 ,'begin debug OKLRQUCB.pls procedure set_level_end_date');
191 END IF;
192 -- check for logging at STATEMENT level
193 is_debug_statement_on := okl_debug_pub.check_log_on(l_module
194 ,fnd_log.level_statement);
195
196 IF p_cashflow_header_rec.frequency_code = 'A' THEN
197 l_mpp := 12;
198 ELSIF p_cashflow_header_rec.frequency_code = 'S' THEN
199 l_mpp := 6;
200 ELSIF p_cashflow_header_rec.frequency_code = 'Q' THEN
201 l_mpp := 3;
202 ELSIF p_cashflow_header_rec.frequency_code = 'M' THEN
203 l_mpp := 1;
204 END IF;
205
206 l_next_start_date := p_contract_start_date;
207
208 FOR i IN p_cashflow_level_tbl.FIRST .. p_cashflow_level_tbl.LAST LOOP
209 IF p_cashflow_level_tbl.EXISTS(i) THEN
210
211 IF p_cashflow_level_tbl(i).stub_days IS NOT NULL THEN
212 l_end_date := l_next_start_date + p_cashflow_level_tbl(i).stub_days - 1;
213 ELSE
214 l_end_date := ADD_MONTHS(l_next_start_date, l_mpp*p_cashflow_level_tbl(i).periods) - 1;
215 END IF;
216
217 p_cashflow_level_tbl(i).start_date := l_next_start_date;
218 l_next_start_date := l_end_date + 1;
219
220 END IF;
221 IF (nvl(l_debug_enabled, 'N') = 'Y' AND is_debug_statement_on) THEN
222 okl_debug_pub.log_debug(fnd_log.level_statement
223 ,l_module
224 ,' l_next_start_date = ' || l_next_start_date ||
225 ' l_end_date = ' || l_end_date
226 );
227 END IF; -- end of NVL(l_debug_enabled,'N')='Y'
228 END LOOP;
229
230 l_contract_end_date := ADD_MONTHS(p_contract_start_date, p_contract_term) - 1;
231
232 IF (nvl(l_debug_enabled, 'N') = 'Y' AND is_debug_statement_on) THEN
233 okl_debug_pub.log_debug(fnd_log.level_statement
234 ,l_module
235 ,' p_contract_start_date = ' || p_contract_start_date ||
236 ' l_contract_end_date = ' || l_contract_end_date ||
237 ' p_contract_term = ' || p_contract_term ||
238 ' l_end_date = ' || l_end_date ||
239 ' l_next_start_date = ' || l_next_start_date ||
240 ' l_mpp = ' || l_mpp ||
241 ' p_cashflow_level_tbl_count = ' || p_cashflow_level_tbl.COUNT
242 );
243 END IF; -- end of NVL(l_debug_enabled,'N')='Y'
244
245 IF l_end_date > l_contract_end_date THEN
246 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME, p_msg_name => 'OKL_LEVEL_EXTENDS_K_END');
247 RAISE OKL_API.G_EXCEPTION_ERROR;
248 END IF;
249
250 x_return_status := G_RET_STS_SUCCESS;
251
252 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
253 okl_debug_pub.log_debug(fnd_log.level_procedure
254 ,l_module
255 ,'end debug OKLRQUCB.pls procedure set_level_end_date');
256 END IF;
257
258 EXCEPTION
259
260 WHEN OKL_API.G_EXCEPTION_ERROR THEN
261 x_return_status := G_RET_STS_ERROR;
262
263 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
264 x_return_status := G_RET_STS_UNEXP_ERROR;
265
266 WHEN OTHERS THEN
267 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
268 p_msg_name => G_DB_ERROR,
269 p_token1 => G_PROG_NAME_TOKEN,
270 p_token1_value => l_api_name,
271 p_token2 => G_SQLCODE_TOKEN,
272 p_token2_value => sqlcode,
273 p_token3 => G_SQLERRM_TOKEN,
274 p_token3_value => sqlerrm);
275
276 x_return_status := G_RET_STS_UNEXP_ERROR;
277
278 END set_level_end_date;
279
280
281 -----------------------------
282 -- PROCEDURE set_level_amount
283 -----------------------------
284 PROCEDURE set_level_amount (
285 p_contract_currency IN VARCHAR2
286 ,p_cashflow_level_tbl IN OUT NOCOPY cashflow_level_tbl_type
287 ,x_return_status OUT NOCOPY VARCHAR2
288 ) IS
289
290 l_program_name CONSTANT VARCHAR2(30) := 'set_level_amount';
291 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
292
293 BEGIN
294
295 FOR i IN p_cashflow_level_tbl.FIRST .. p_cashflow_level_tbl.LAST LOOP
296 IF p_cashflow_level_tbl.EXISTS(i) THEN
297 IF p_cashflow_level_tbl(i).stub_amount IS NOT NULL THEN
298 p_cashflow_level_tbl(i).stub_amount :=
299 okl_accounting_util.round_amount( p_amount => p_cashflow_level_tbl(i).stub_amount
300 ,p_currency_code => p_contract_currency
301 );
302 ELSIF p_cashflow_level_tbl(i).periodic_amount IS NOT NULL THEN
303 p_cashflow_level_tbl(i).periodic_amount :=
304 okl_accounting_util.round_amount( p_amount => p_cashflow_level_tbl(i).periodic_amount
305 ,p_currency_code => p_contract_currency
306 );
307 END IF;
308 END IF;
309 END LOOP;
310
311 x_return_status := G_RET_STS_SUCCESS;
312
313 EXCEPTION
314
315 WHEN OKL_API.G_EXCEPTION_ERROR THEN
316 x_return_status := G_RET_STS_ERROR;
317
318 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
319 x_return_status := G_RET_STS_UNEXP_ERROR;
320
321 WHEN OTHERS THEN
322 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
323 p_msg_name => G_DB_ERROR,
324 p_token1 => G_PROG_NAME_TOKEN,
325 p_token1_value => l_api_name,
326 p_token2 => G_SQLCODE_TOKEN,
327 p_token2_value => sqlcode,
328 p_token3 => G_SQLERRM_TOKEN,
329 p_token3_value => sqlerrm);
330
331 x_return_status := G_RET_STS_UNEXP_ERROR;
332
333 END set_level_amount;
334
335
336 -------------------------
337 -- PROCEDURE sanity_check
338 -------------------------
339 PROCEDURE sanity_check (
340 p_cashflow_header_rec IN cashflow_header_rec_type
341 ,p_cashflow_level_tbl IN cashflow_level_tbl_type
342 ,x_return_status OUT NOCOPY VARCHAR2
343 ) IS
344
345 l_program_name CONSTANT VARCHAR2(30) := 'sanity_check';
346 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
347
348 BEGIN
349
350 IF p_cashflow_header_rec.frequency_code NOT IN ('M','Q','S','A') THEN
351 OKL_API.SET_MESSAGE (
352 p_app_name => G_APP_NAME
353 ,p_msg_name => 'OKL_INVALID_VALUE2'
354 ,p_token1 => 'API_NAME'
355 ,p_token1_value => UPPER(l_api_name)
356 ,p_token2 => 'NAME'
357 ,p_token2_value => 'frequency_code'
358 ,p_token3 => 'VALUE'
359 ,p_token3_value => p_cashflow_header_rec.frequency_code
360 );
361 RAISE OKL_API.G_EXCEPTION_ERROR;
362 END IF;
363
364 IF p_cashflow_header_rec.arrears_flag NOT IN ('Y' , 'N') THEN
365 OKL_API.SET_MESSAGE (
366 p_app_name => G_APP_NAME
367 ,p_msg_name => 'OKL_INVALID_VALUE2'
368 ,p_token1 => 'API_NAME'
369 ,p_token1_value => UPPER(l_api_name)
370 ,p_token2 => 'NAME'
371 ,p_token2_value => 'arrears_flag'
372 ,p_token3 => 'VALUE'
373 ,p_token3_value => p_cashflow_header_rec.arrears_flag
374 );
375 RAISE OKL_API.G_EXCEPTION_ERROR;
376 END IF;
377
378 IF p_cashflow_header_rec.type_code NOT IN ('INFLOW' , 'OUTFLOW') THEN
379 OKL_API.SET_MESSAGE (
380 p_app_name => G_APP_NAME
381 ,p_msg_name => 'OKL_INVALID_VALUE2'
382 ,p_token1 => 'API_NAME'
383 ,p_token1_value => UPPER(l_api_name)
384 ,p_token2 => 'NAME'
385 ,p_token2_value => 'type_code'
386 ,p_token3 => 'VALUE'
387 ,p_token3_value => p_cashflow_header_rec.type_code
388 );
389 RAISE OKL_API.G_EXCEPTION_ERROR;
390 END IF;
391
392 IF p_cashflow_header_rec.quote_type_code NOT IN ('LQ', 'QQ', 'LA') THEN
393 OKL_API.SET_MESSAGE (
394 p_app_name => G_APP_NAME
395 ,p_msg_name => 'OKL_INVALID_VALUE2'
396 ,p_token1 => 'API_NAME'
397 ,p_token1_value => UPPER(l_api_name)
398 ,p_token2 => 'NAME'
399 ,p_token2_value => 'quote_type_code'
400 ,p_token3 => 'VALUE'
401 ,p_token3_value => p_cashflow_header_rec.quote_type_code
402 );
403 RAISE OKL_API.G_EXCEPTION_ERROR;
404 END IF;
405
406 IF p_cashflow_header_rec.quote_id IS NULL THEN
407 OKL_API.SET_MESSAGE (
408 p_app_name => G_APP_NAME
409 ,p_msg_name => 'OKL_INVALID_VALUE2'
410 ,p_token1 => 'API_NAME'
411 ,p_token1_value => UPPER(l_api_name)
412 ,p_token2 => 'NAME'
413 ,p_token2_value => 'quote_id'
414 ,p_token3 => 'VALUE'
415 ,p_token3_value => p_cashflow_header_rec.quote_id
416 );
417 RAISE OKL_API.G_EXCEPTION_ERROR;
418 END IF;
419
420 FOR i IN p_cashflow_level_tbl.FIRST .. p_cashflow_level_tbl.LAST LOOP
421 IF p_cashflow_level_tbl.EXISTS(i) THEN
422 IF UPPER(p_cashflow_level_tbl(i).record_mode) NOT IN ('CREATE', 'UPDATE') OR p_cashflow_level_tbl(i).record_mode IS NULL THEN
423 OKL_API.SET_MESSAGE (
424 p_app_name => G_APP_NAME
425 ,p_msg_name => 'OKL_INVALID_VALUE2'
426 ,p_token1 => 'API_NAME'
427 ,p_token1_value => UPPER(l_api_name)
428 ,p_token2 => 'NAME'
429 ,p_token2_value => '('||i||') record_mode'
430 ,p_token3 => 'VALUE'
431 ,p_token3_value => UPPER(p_cashflow_level_tbl(i).record_mode)
432 );
433 RAISE OKL_API.G_EXCEPTION_ERROR;
434 END IF;
435 END IF;
436 END LOOP;
437
438 x_return_status := G_RET_STS_SUCCESS;
439
440 EXCEPTION
441
442 WHEN OKL_API.G_EXCEPTION_ERROR THEN
443 x_return_status := G_RET_STS_ERROR;
444
445 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
446 x_return_status := G_RET_STS_UNEXP_ERROR;
447
448 WHEN OTHERS THEN
449 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
450 p_msg_name => G_DB_ERROR,
451 p_token1 => G_PROG_NAME_TOKEN,
452 p_token1_value => l_api_name,
453 p_token2 => G_SQLCODE_TOKEN,
454 p_token2_value => sqlcode,
455 p_token3 => G_SQLERRM_TOKEN,
456 p_token3_value => sqlerrm);
457
458 x_return_status := G_RET_STS_UNEXP_ERROR;
459
460 END sanity_check;
461
462
463 --------------------------------------
464 -- PROCEDURE validate_level_attributes
465 --------------------------------------
466 PROCEDURE validate_level_attributes (
467 p_cashflow_level_tbl IN cashflow_level_tbl_type
468 ,p_caf_status IN VARCHAR2
469 ,p_pricing_method IN VARCHAR2
470 ,x_return_status OUT NOCOPY VARCHAR2
471 ) IS
472
473 l_program_name CONSTANT VARCHAR2(30) := 'validate_level_attributes';
474 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
475 l_periods_exists VARCHAR2(3) := 'N';
476
477 BEGIN
478
479 FOR i IN p_cashflow_level_tbl.FIRST .. p_cashflow_level_tbl.LAST LOOP
480
481 IF p_cashflow_level_tbl.EXISTS(i) THEN
482
483 IF p_cashflow_level_tbl(i).periods IS NOT NULL THEN
484 l_periods_exists := 'Y';
485 END IF;
486
487 IF p_cashflow_level_tbl(i).periods <= 0 THEN
488 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME, p_msg_name => 'OKL_LEVEL_PERIOD_ZERO');
489 RAISE OKL_API.G_EXCEPTION_ERROR;
490 END IF;
491
492 IF TRUNC(p_cashflow_level_tbl(i).periods) <> p_cashflow_level_tbl(i).periods THEN
493 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME, p_msg_name => 'OKL_LEVEL_PERIOD_FRACTION');
494 RAISE OKL_API.G_EXCEPTION_ERROR;
495 END IF;
496
497 IF p_cashflow_level_tbl(i).stub_days <= 0 THEN
498 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME, p_msg_name => 'OKL_LEVEL_STUBDAYS_ZERO');
499 RAISE OKL_API.G_EXCEPTION_ERROR;
500 END IF;
501
502 IF TRUNC(p_cashflow_level_tbl(i).stub_days) <> p_cashflow_level_tbl(i).stub_days THEN
503 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME, p_msg_name => 'OKL_LEVEL_STUBDAYS_FRACTION');
504 RAISE OKL_API.G_EXCEPTION_ERROR;
505 END IF;
506 IF p_pricing_method <> 'SP' AND p_pricing_method <> 'SM' THEN
507 IF p_cashflow_level_tbl(i).periodic_amount < 0 THEN
508 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME, p_msg_name => 'OKL_LEVEL_AMOUNT_ZERO');
509 RAISE OKL_API.G_EXCEPTION_ERROR;
510 END IF;
511 END IF;
512 IF p_pricing_method <> 'SP' AND p_pricing_method <> 'SM' THEN
513 IF p_cashflow_level_tbl(i).stub_amount < 0 THEN
514 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME, p_msg_name => 'OKL_LEVEL_STUBAMT_ZERO');
515 RAISE OKL_API.G_EXCEPTION_ERROR;
516 END IF;
517 END IF;
518
519 --04-Nov-2009 sechawla 9001225 : removed the validation
520 /*
521 IF p_caf_status <> 'WORK' AND p_cashflow_level_tbl(i).rate <= 0 THEN
522 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME, p_msg_name => 'OKL_LEVEL_RATE_ZERO');
523 RAISE OKL_API.G_EXCEPTION_ERROR;
524 END IF;
525 */
526 IF (p_cashflow_level_tbl(i).stub_days IS NULL) AND (p_cashflow_level_tbl(i).periods IS NULL) THEN
527 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME, p_msg_name => 'OKL_LEVEL_NO_STUB_AND_PER');
528 RAISE OKL_API.G_EXCEPTION_ERROR;
529 END IF;
530
531 IF (p_cashflow_level_tbl(i).stub_days IS NOT NULL) AND (p_cashflow_level_tbl(i).periods IS NOT NULL) THEN
532 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME, p_msg_name => 'OKL_LEVEL_BOTH_STUB_AND_PER');
533 RAISE OKL_API.G_EXCEPTION_ERROR;
534 END IF;
535
536 IF (p_cashflow_level_tbl(i).stub_amount IS NOT NULL) AND (p_cashflow_level_tbl(i).stub_days IS NULL) THEN
537 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME, p_msg_name => 'OKL_LEVEL_STUBAMT_WO_DAYS');
538 RAISE OKL_API.G_EXCEPTION_ERROR;
539 END IF;
540
541 IF p_pricing_method <> 'SP' AND p_pricing_method <> 'SM' THEN
542 IF (p_cashflow_level_tbl(i).stub_amount IS NULL) AND (p_cashflow_level_tbl(i).stub_days IS NOT NULL) THEN
543 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME, p_msg_name => 'OKL_LEVEL_STUBDAYS_WO_AMT');
544 RAISE OKL_API.G_EXCEPTION_ERROR;
545 END IF;
546 END IF;
547 IF (p_cashflow_level_tbl(i).periodic_amount IS NOT NULL) AND (p_cashflow_level_tbl(i).periods IS NULL) THEN
548 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME, p_msg_name => 'OKL_LEVEL_AMOUNT_WO_PERIODS');
549 RAISE OKL_API.G_EXCEPTION_ERROR;
550 END IF;
551 IF p_pricing_method <> 'SP' AND p_pricing_method <> 'SM' THEN
552 IF (p_cashflow_level_tbl(i).periodic_amount IS NULL) AND (p_cashflow_level_tbl(i).periods IS NOT NULL) THEN
553 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME, p_msg_name => 'OKL_LEVEL_PERIODS_WO_AMOUNT');
554 RAISE OKL_API.G_EXCEPTION_ERROR;
555 END IF;
556 END IF;
557 END IF;
558
559 END LOOP;
560 IF l_periods_exists = 'N' THEN
561 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME, p_msg_name => 'OKL_LEVEL_PERIODS_NOT_PRESENT');
562 RAISE OKL_API.G_EXCEPTION_ERROR;
563 END IF;
564 x_return_status := G_RET_STS_SUCCESS;
565
566 EXCEPTION
567
568 WHEN OKL_API.G_EXCEPTION_ERROR THEN
569 x_return_status := G_RET_STS_ERROR;
570
571 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
572 x_return_status := G_RET_STS_UNEXP_ERROR;
573
574 WHEN OTHERS THEN
575 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
576 p_msg_name => G_DB_ERROR,
577 p_token1 => G_PROG_NAME_TOKEN,
578 p_token1_value => l_api_name,
579 p_token2 => G_SQLCODE_TOKEN,
580 p_token2_value => sqlcode,
581 p_token3 => G_SQLERRM_TOKEN,
582 p_token3_value => sqlerrm);
583
584 x_return_status := G_RET_STS_UNEXP_ERROR;
585
586 END validate_level_attributes;
587
588
589 ---------------------------------
590 -- PROCEDURE get_contract_details
591 ---------------------------------
592 PROCEDURE get_contract_details (
593 p_quote_type IN VARCHAR2
594 ,p_quote_id IN NUMBER
595 ,x_contract_details OUT NOCOPY contract_details_rec_type
596 ,x_return_status OUT NOCOPY VARCHAR2
597 ) IS
598
599 l_program_name CONSTANT VARCHAR2(30) := 'get_contract_details';
600 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
601
602 CURSOR c_lq_hdr IS
603 SELECT
604 lop.currency_code
605 ,lsq.expected_start_date
606 ,lsq.term
607 ,lsq.pricing_method
608 FROM
609 okl_lease_quotes_b lsq
610 ,okl_lease_opportunities_b lop
611 WHERE lsq.id = p_quote_id
612 AND lsq.parent_object_code = 'LEASEOPP'
613 AND lsq.parent_object_id = lop.id;
614
615 CURSOR c_la_hdr IS
616 SELECT
617 lap.currency_code
618 ,lsq.expected_start_date
619 ,lsq.term
620 ,lsq.pricing_method
621 FROM
622 okl_lease_quotes_b lsq
623 ,okl_lease_applications_b lap
624 WHERE lsq.id = p_quote_id
625 AND lsq.parent_object_code = 'LEASEAPP'
626 AND lsq.parent_object_id = lap.id;
627
628 CURSOR c_qq_hdr IS
629 SELECT
630 currency_code
631 ,expected_start_date
632 ,term
633 ,pricing_method
634 FROM
635 okl_quick_quotes_b
636 WHERE id = p_quote_id;
637
638 BEGIN
639
640 IF p_quote_type = 'LQ' THEN
641 OPEN c_lq_hdr;
642 FETCH c_lq_hdr INTO x_contract_details;
643 CLOSE c_lq_hdr;
644 ELSIF p_quote_type = 'LA' THEN
645 OPEN c_la_hdr;
646 FETCH c_la_hdr INTO x_contract_details;
647 CLOSE c_la_hdr;
648 ELSIF p_quote_type = 'QQ' THEN
649 OPEN c_qq_hdr;
650 FETCH c_qq_hdr INTO x_contract_details;
651 CLOSE c_qq_hdr;
652 END IF;
653
654 IF (x_contract_details.currency_code IS NULL) OR (x_contract_details.start_date IS NULL) OR
655 (x_contract_details.term IS NULL) OR (x_contract_details.pricing_method_code IS NULL) THEN
656
657 OKL_API.SET_MESSAGE (
658 p_app_name => G_APP_NAME
659 ,p_msg_name => 'OKL_INVALID_VALUE2'
660 ,p_token1 => 'API_NAME'
661 ,p_token1_value => UPPER(l_api_name)
662 ,p_token2 => 'NAME'
663 ,p_token2_value => 'x_contract_details'
664 ,p_token3 => 'VALUE'
665 ,p_token3_value => 'null'
666 );
667 RAISE OKL_API.G_EXCEPTION_ERROR;
668
669 END IF;
670
671 x_return_status := G_RET_STS_SUCCESS;
672
673 EXCEPTION
674
675 WHEN OKL_API.G_EXCEPTION_ERROR THEN
676 x_return_status := G_RET_STS_ERROR;
677
678 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
679 x_return_status := G_RET_STS_UNEXP_ERROR;
680
681 WHEN OTHERS THEN
682 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
683 p_msg_name => G_DB_ERROR,
684 p_token1 => G_PROG_NAME_TOKEN,
685 p_token1_value => l_api_name,
686 p_token2 => G_SQLCODE_TOKEN,
687 p_token2_value => sqlcode,
688 p_token3 => G_SQLERRM_TOKEN,
689 p_token3_value => sqlerrm);
690
691 x_return_status := G_RET_STS_UNEXP_ERROR;
692
693 END get_contract_details;
694
695
696 ------------------------------
697 -- PROCEDURE process_cashflow
698 ------------------------------
699 PROCEDURE process_cashflow (
700 p_cashflow_header_rec IN OUT NOCOPY cashflow_header_rec_type
701 ,p_cashflow_level_tbl IN OUT NOCOPY cashflow_level_tbl_type
702 ,x_return_status OUT NOCOPY VARCHAR2
703 ) IS
704
705 l_program_name CONSTANT VARCHAR2(30) := 'process_cashflow';
706 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
707
708 l_contract_details contract_details_rec_type;
709
710 BEGIN
711
712 sanity_check (
713 p_cashflow_header_rec => p_cashflow_header_rec
714 ,p_cashflow_level_tbl => p_cashflow_level_tbl
715 ,x_return_status => x_return_status
716 );
717
718 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
719 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
720 ELSIF x_return_status = G_RET_STS_ERROR THEN
721 RAISE OKL_API.G_EXCEPTION_ERROR;
722 END IF;
723
724 get_contract_details (
725 p_quote_type => p_cashflow_header_rec.quote_type_code
726 ,p_quote_id => p_cashflow_header_rec.quote_id
727 ,x_contract_details => l_contract_details
728 ,x_return_status => x_return_status
729 );
730
731 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
732 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
733 ELSIF x_return_status = G_RET_STS_ERROR THEN
734 RAISE OKL_API.G_EXCEPTION_ERROR;
735 END IF;
736
737 validate_level_attributes (
738 p_cashflow_level_tbl => p_cashflow_level_tbl
739 ,p_caf_status => p_cashflow_header_rec.status_code
740 ,p_pricing_method => l_contract_details.pricing_method_code
741 ,x_return_status => x_return_status
742 );
743
744 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
745 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
746 ELSIF x_return_status = G_RET_STS_ERROR THEN
747 RAISE OKL_API.G_EXCEPTION_ERROR;
748 END IF;
749
750 set_level_end_date (
751 p_contract_start_date => l_contract_details.start_date
752 ,p_contract_term => l_contract_details.term
753 ,p_cashflow_header_rec => p_cashflow_header_rec
754 ,p_cashflow_level_tbl => p_cashflow_level_tbl
755 ,x_return_status => x_return_status
756 );
757
758 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
759 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
760 ELSIF x_return_status = G_RET_STS_ERROR THEN
761 RAISE OKL_API.G_EXCEPTION_ERROR;
762 END IF;
763
764 set_level_amount (
765 p_contract_currency => l_contract_details.currency_code
766 ,p_cashflow_level_tbl => p_cashflow_level_tbl
767 ,x_return_status => x_return_status
768 );
769
770 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
771 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
772 ELSIF x_return_status = G_RET_STS_ERROR THEN
773 RAISE OKL_API.G_EXCEPTION_ERROR;
774 END IF;
775
776 x_return_status := G_RET_STS_SUCCESS;
777
778 EXCEPTION
779
780 WHEN OKL_API.G_EXCEPTION_ERROR THEN
781 x_return_status := G_RET_STS_ERROR;
782
783 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
784 x_return_status := G_RET_STS_UNEXP_ERROR;
785
786 WHEN OTHERS THEN
787 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
788 p_msg_name => G_DB_ERROR,
789 p_token1 => G_PROG_NAME_TOKEN,
790 p_token1_value => l_api_name,
791 p_token2 => G_SQLCODE_TOKEN,
792 p_token2_value => sqlcode,
793 p_token3 => G_SQLERRM_TOKEN,
794 p_token3_value => sqlerrm);
795
796 x_return_status := G_RET_STS_UNEXP_ERROR;
797
798 END process_cashflow;
799
800
801 ------------------------
802 -- PROCEDURE insert_rows
803 ------------------------
804 PROCEDURE insert_rows (
805 p_cashflow_header_rec IN OUT NOCOPY cashflow_header_rec_type
806 ,p_cashflow_level_tbl IN OUT NOCOPY cashflow_level_tbl_type
807 ,x_return_status OUT NOCOPY VARCHAR2
808 ,x_msg_count OUT NOCOPY NUMBER
809 ,x_msg_data OUT NOCOPY VARCHAR2
810 ) IS
811
812 l_program_name CONSTANT VARCHAR2(30) := 'insert_rows';
813 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
814
815 l_cafv_rec cafv_rec_type;
816 lx_cafv_rec cafv_rec_type;
817
818 l_cfov_rec cfov_rec_type;
819 lx_cfov_rec cfov_rec_type;
820
821 l_cflv_tbl cflv_tbl_type;
822 lx_cflv_tbl cflv_tbl_type;
823
824 BEGIN
825
826 l_cfov_rec.object_version_number := 1;
827 l_cfov_rec.oty_code := p_cashflow_header_rec.parent_object_code;
828 l_cfov_rec.source_id := p_cashflow_header_rec.parent_object_id;
829
830 --Bug # 5142940 ssdeshpa start
831 IF (p_cashflow_header_rec.parent_object_code IN ('QUOTED_ASSET','QUOTED_ASSET_DOWN_PAYMENT','QUOTED_ASSET_PROPERTY_TAX')) THEN
832 l_cfov_rec.source_table := 'OKL_ASSETS_B';
833 --Bug # 5142940 ssdeshpa end
834 ELSIF (p_cashflow_header_rec.parent_object_code = 'QUOTED_FEE') THEN
835 l_cfov_rec.source_table := 'OKL_FEES_B';
836 ELSIF (p_cashflow_header_rec.parent_object_code = 'QUOTED_SERVICE') THEN
837 l_cfov_rec.source_table := 'OKL_SERVICES_B';
838 ELSIF (p_cashflow_header_rec.parent_object_code = 'QUOTED_INSURANCE') THEN
839 l_cfov_rec.source_table := 'OKL_INSURANCE_ESTIMATES_B';
840 ELSIF (p_cashflow_header_rec.parent_object_code = 'LEASE_QUOTE') THEN
841 l_cfov_rec.source_table := 'OKL_LEASE_QUOTES_B';
842 ELSIF (p_cashflow_header_rec.parent_object_code = 'QUICK_QUOTE') THEN
843 l_cfov_rec.source_table := 'OKL_QUICK_QUOTES_B';
844 ELSIF (p_cashflow_header_rec.parent_object_code = 'QUICK_QUOTE_ASSET') THEN
845 l_cfov_rec.source_table := 'OKL_QUICK_QUOTE_LINES_B';
846 ELSIF (p_cashflow_header_rec.parent_object_code = 'QUICK_QUOTE_SERVICE') THEN
847 l_cfov_rec.source_table := 'OKL_QUICK_QUOTE_LINES_B';
848 ELSIF (p_cashflow_header_rec.parent_object_code = 'QUICK_QUOTE_FEE') THEN
849 l_cfov_rec.source_table := 'OKL_QUICK_QUOTE_LINES_B';
850 ELSIF (p_cashflow_header_rec.parent_object_code = 'QUICK_QUOTE_TAX') THEN
851 l_cfov_rec.source_table := 'OKL_QUICK_QUOTE_LINES_B';
852 ELSIF (p_cashflow_header_rec.parent_object_code = 'QUICK_QUOTE_INSURANCE') THEN
853 l_cfov_rec.source_table := 'OKL_QUICK_QUOTE_LINES_B';
854 ELSIF (p_cashflow_header_rec.parent_object_code = 'LEASE_QUOTE_CONSOLIDATED') THEN
855 l_cfov_rec.source_table := 'OKL_LEASE_QUOTES_B';
856 END IF;
857
858 okl_cfo_pvt.insert_row (
859 p_api_version => G_API_VERSION
860 ,p_init_msg_list => G_FALSE
861 ,x_return_status => x_return_status
862 ,x_msg_count => x_msg_count
863 ,x_msg_data => x_msg_data
864 ,p_cfov_rec => l_cfov_rec
865 ,x_cfov_rec => lx_cfov_rec
866 );
867
868 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
869 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
870 ELSIF x_return_status = G_RET_STS_ERROR THEN
871 RAISE OKL_API.G_EXCEPTION_ERROR;
872 END IF;
873
874 l_cafv_rec.cfo_id := lx_cfov_rec.id;
875 l_cafv_rec.object_version_number := 1;
876 --bug 4898499
877 IF p_cashflow_header_rec.status_code = 'WORK' THEN
878 l_cafv_rec.sts_code := 'WORK';
879 ELSE
880 l_cafv_rec.sts_code := 'CURRENT';
881 END IF;
882 l_cafv_rec.sty_id := p_cashflow_header_rec.stream_type_id;
883 l_cafv_rec.due_arrears_yn := p_cashflow_header_rec.arrears_flag;
884 l_cafv_rec.dnz_qte_id := p_cashflow_header_rec.quote_id;
885 l_cafv_rec.start_date := p_cashflow_level_tbl(p_cashflow_level_tbl.FIRST).start_date;
886
887 IF p_cashflow_header_rec.type_code = 'INFLOW' THEN
888 l_cafv_rec.cft_code := 'PAYMENT_SCHEDULE';
889 ELSIF p_cashflow_header_rec.type_code = 'OUTFLOW' THEN
890 l_cafv_rec.cft_code := 'OUTFLOW_SCHEDULE';
891 END IF;
892
893 okl_caf_pvt.insert_row (
894 p_api_version => G_API_VERSION
895 ,p_init_msg_list => G_FALSE
896 ,x_return_status => x_return_status
897 ,x_msg_count => x_msg_count
898 ,x_msg_data => x_msg_data
899 ,p_cafv_rec => l_cafv_rec
900 ,x_cafv_rec => lx_cafv_rec
901 );
902
903 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
904 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
905 ELSIF x_return_status = G_RET_STS_ERROR THEN
906 RAISE OKL_API.G_EXCEPTION_ERROR;
907 END IF;
908
909 FOR i IN p_cashflow_level_tbl.FIRST .. p_cashflow_level_tbl.LAST LOOP
910 IF p_cashflow_level_tbl.EXISTS(i) THEN
911 l_cflv_tbl(i).caf_id := lx_cafv_rec.id;
912 l_cflv_tbl(i).object_version_number := 1;
913 l_cflv_tbl(i).number_of_periods := p_cashflow_level_tbl(i).periods;
914 l_cflv_tbl(i).amount := p_cashflow_level_tbl(i).periodic_amount;
915 l_cflv_tbl(i).stub_days := p_cashflow_level_tbl(i).stub_days;
916 l_cflv_tbl(i).stub_amount := p_cashflow_level_tbl(i).stub_amount;
917 l_cflv_tbl(i).start_date := p_cashflow_level_tbl(i).start_date;
918 l_cflv_tbl(i).fqy_code := p_cashflow_header_rec.frequency_code;
919 l_cflv_tbl(i).rate := p_cashflow_level_tbl(i).rate;
920 l_cflv_tbl(i).missing_pmt_flag := p_cashflow_level_tbl(i).missing_pmt_flag;
921 END IF;
922 END LOOP;
923
924 okl_cfl_pvt.insert_row (
925 p_api_version => G_API_VERSION
926 ,p_init_msg_list => G_FALSE
927 ,x_return_status => x_return_status
928 ,x_msg_count => x_msg_count
929 ,x_msg_data => x_msg_data
930 ,p_cflv_tbl => l_cflv_tbl
931 ,x_cflv_tbl => lx_cflv_tbl
932 );
933
934 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
935 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
936 ELSIF x_return_status = G_RET_STS_ERROR THEN
937 RAISE OKL_API.G_EXCEPTION_ERROR;
938 END IF;
939
940 p_cashflow_header_rec.cashflow_object_id := lx_cfov_rec.id;
941 p_cashflow_header_rec.cashflow_header_id := lx_cafv_rec.id;
942
943 populate_level_ids (
944 p_cf_header_id => p_cashflow_header_rec.cashflow_header_id
945 ,x_cf_levels_tbl => p_cashflow_level_tbl
946 ,x_return_status => x_return_status
947 );
948
949 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
950 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
951 ELSIF x_return_status = G_RET_STS_ERROR THEN
952 RAISE OKL_API.G_EXCEPTION_ERROR;
953 END IF;
954
955 EXCEPTION
956
957 WHEN OKL_API.G_EXCEPTION_ERROR THEN
958 x_return_status := G_RET_STS_ERROR;
959
960 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
961 x_return_status := G_RET_STS_UNEXP_ERROR;
962
963 WHEN OTHERS THEN
964 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
965 p_msg_name => G_DB_ERROR,
966 p_token1 => G_PROG_NAME_TOKEN,
967 p_token1_value => l_api_name,
968 p_token2 => G_SQLCODE_TOKEN,
969 p_token2_value => sqlcode,
970 p_token3 => G_SQLERRM_TOKEN,
971 p_token3_value => sqlerrm);
972
973 x_return_status := G_RET_STS_UNEXP_ERROR;
974
975 END insert_rows;
976
977
978 -------------------------------
979 -- PROCEDURE insert_update_rows
980 -------------------------------
981 PROCEDURE insert_update_rows (
982 p_cashflow_header_rec IN OUT NOCOPY cashflow_header_rec_type
983 ,p_cashflow_level_tbl IN OUT NOCOPY cashflow_level_tbl_type
984 ,x_return_status OUT NOCOPY VARCHAR2
985 ,x_msg_count OUT NOCOPY NUMBER
986 ,x_msg_data OUT NOCOPY VARCHAR2
987 ) IS
988
989 l_program_name CONSTANT VARCHAR2(30) := 'insert_update_rows';
990 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
991
992 l_cafv_rec cafv_rec_type;
993 lx_cafv_rec cafv_rec_type;
994
995 l_cflv_ins_tbl cflv_tbl_type;
996 l_cflv_upd_tbl cflv_tbl_type;
997 lx_cflv_tbl cflv_tbl_type;
998
999 BEGIN
1000
1001 l_cafv_rec.id := p_cashflow_header_rec.cashflow_header_id;
1002 l_cafv_rec.object_version_number := p_cashflow_header_rec.cashflow_header_ovn;
1003 l_cafv_rec.cfo_id := p_cashflow_header_rec.cashflow_object_id;
1004 l_cafv_rec.sty_id := p_cashflow_header_rec.stream_type_id;
1005 l_cafv_rec.due_arrears_yn := p_cashflow_header_rec.arrears_flag;
1006 l_cafv_rec.start_date := p_cashflow_level_tbl(p_cashflow_level_tbl.FIRST).start_date;
1007
1008 okl_caf_pvt.update_row (
1009 p_api_version => G_API_VERSION
1010 ,p_init_msg_list => G_FALSE
1011 ,x_return_status => x_return_status
1012 ,x_msg_count => x_msg_count
1013 ,x_msg_data => x_msg_data
1014 ,p_cafv_rec => l_cafv_rec
1015 ,x_cafv_rec => lx_cafv_rec
1016 );
1017
1018 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
1019 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1020 ELSIF x_return_status = G_RET_STS_ERROR THEN
1021 RAISE OKL_API.G_EXCEPTION_ERROR;
1022 END IF;
1023
1024 FOR i IN p_cashflow_level_tbl.FIRST .. p_cashflow_level_tbl.LAST LOOP
1025 IF p_cashflow_level_tbl.EXISTS(i) THEN
1026 IF UPPER(p_cashflow_level_tbl(i).record_mode) = 'CREATE' THEN
1027 l_cflv_ins_tbl(i).caf_id := lx_cafv_rec.id;
1028 l_cflv_ins_tbl(i).object_version_number := 1;
1029 l_cflv_ins_tbl(i).number_of_periods := p_cashflow_level_tbl(i).periods;
1030 l_cflv_ins_tbl(i).amount := p_cashflow_level_tbl(i).periodic_amount;
1031 l_cflv_ins_tbl(i).stub_days := p_cashflow_level_tbl(i).stub_days;
1032 l_cflv_ins_tbl(i).stub_amount := p_cashflow_level_tbl(i).stub_amount;
1033 l_cflv_ins_tbl(i).start_date := p_cashflow_level_tbl(i).start_date;
1034 l_cflv_ins_tbl(i).fqy_code := p_cashflow_header_rec.frequency_code;
1035 l_cflv_ins_tbl(i).rate := p_cashflow_level_tbl(i).rate;
1036 l_cflv_ins_tbl(i).missing_pmt_flag := p_cashflow_level_tbl(i).missing_pmt_flag;
1037 ELSIF UPPER(p_cashflow_level_tbl(i).record_mode) = 'UPDATE' THEN
1038 l_cflv_upd_tbl(i).id := p_cashflow_level_tbl(i).cashflow_level_id;
1039 l_cflv_upd_tbl(i).caf_id := lx_cafv_rec.id;
1040 l_cflv_upd_tbl(i).object_version_number := p_cashflow_level_tbl(i).cashflow_level_ovn;
1041 l_cflv_upd_tbl(i).number_of_periods := p_cashflow_level_tbl(i).periods;
1042 l_cflv_upd_tbl(i).amount := p_cashflow_level_tbl(i).periodic_amount;
1043 l_cflv_upd_tbl(i).stub_days := p_cashflow_level_tbl(i).stub_days;
1044 l_cflv_upd_tbl(i).stub_amount := p_cashflow_level_tbl(i).stub_amount;
1045 l_cflv_upd_tbl(i).start_date := p_cashflow_level_tbl(i).start_date;
1046 l_cflv_upd_tbl(i).fqy_code := p_cashflow_header_rec.frequency_code;
1047 l_cflv_upd_tbl(i).rate := p_cashflow_level_tbl(i).rate;
1048 l_cflv_upd_tbl(i).missing_pmt_flag := p_cashflow_level_tbl(i).missing_pmt_flag;
1049 END IF;
1050 END IF;
1051 END LOOP;
1052
1053 IF l_cflv_ins_tbl.COUNT > 0 THEN
1054
1055 okl_cfl_pvt.insert_row (
1056 p_api_version => G_API_VERSION
1057 ,p_init_msg_list => G_FALSE
1058 ,x_return_status => x_return_status
1059 ,x_msg_count => x_msg_count
1060 ,x_msg_data => x_msg_data
1061 ,p_cflv_tbl => l_cflv_ins_tbl
1062 ,x_cflv_tbl => lx_cflv_tbl
1063 );
1064
1065 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
1066 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1067 ELSIF x_return_status = G_RET_STS_ERROR THEN
1068 RAISE OKL_API.G_EXCEPTION_ERROR;
1069 END IF;
1070
1071 END IF;
1072
1073 IF l_cflv_upd_tbl.COUNT > 0 THEN
1074
1075 okl_cfl_pvt.update_row (
1076 p_api_version => G_API_VERSION
1077 ,p_init_msg_list => G_FALSE
1078 ,x_return_status => x_return_status
1079 ,x_msg_count => x_msg_count
1080 ,x_msg_data => x_msg_data
1081 ,p_cflv_tbl => l_cflv_upd_tbl
1082 ,x_cflv_tbl => lx_cflv_tbl
1083 );
1084
1085 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
1086 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1087 ELSIF x_return_status = G_RET_STS_ERROR THEN
1088 RAISE OKL_API.G_EXCEPTION_ERROR;
1089 END IF;
1090
1091 END IF;
1092
1093 populate_level_ids (
1094 p_cf_header_id => p_cashflow_header_rec.cashflow_header_id
1095 ,x_cf_levels_tbl => p_cashflow_level_tbl
1096 ,x_return_status => x_return_status
1097 );
1098
1099 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
1100 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1101 ELSIF x_return_status = G_RET_STS_ERROR THEN
1102 RAISE OKL_API.G_EXCEPTION_ERROR;
1103 END IF;
1104
1105 EXCEPTION
1106
1107 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1108 x_return_status := G_RET_STS_ERROR;
1109
1110 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1111 x_return_status := G_RET_STS_UNEXP_ERROR;
1112
1113 WHEN OTHERS THEN
1114 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1115 p_msg_name => G_DB_ERROR,
1116 p_token1 => G_PROG_NAME_TOKEN,
1117 p_token1_value => l_api_name,
1118 p_token2 => G_SQLCODE_TOKEN,
1119 p_token2_value => sqlcode,
1120 p_token3 => G_SQLERRM_TOKEN,
1121 p_token3_value => sqlerrm);
1122
1123 x_return_status := G_RET_STS_UNEXP_ERROR;
1124
1125 END insert_update_rows;
1126
1127
1128 ----------------------------------------
1129 -- PROCEDURE get_deleted_cashflow_levels
1130 ----------------------------------------
1131 PROCEDURE get_deleted_cashflow_levels (p_cashflow_header_id IN NUMBER,
1132 p_cashflow_level_tbl IN cashflow_level_tbl_type,
1133 x_deleted_cashflow_level_tbl OUT NOCOPY cflv_tbl_type,
1134 x_return_status OUT NOCOPY VARCHAR2) IS
1135
1136 l_program_name CONSTANT VARCHAR2(30) := 'get_deleted_cashflow_levels';
1137 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
1138
1139 CURSOR c_db_cashflow_levels IS
1140 SELECT id
1141 FROM okl_cash_flow_levels
1142 WHERE caf_id = p_cashflow_header_id;
1143
1144 l_cashflow_level_tbl cflv_tbl_type;
1145 l_delete_flag VARCHAR2(1);
1146 i BINARY_INTEGER := 0;
1147
1148 BEGIN
1149
1150 IF (p_cashflow_level_tbl.COUNT > 0) THEN
1151 FOR l_db_cashflow_levels IN c_db_cashflow_levels LOOP
1152 l_delete_flag := 'Y';
1153 FOR j IN p_cashflow_level_tbl.FIRST .. p_cashflow_level_tbl.LAST LOOP
1154 IF p_cashflow_level_tbl.EXISTS(j) THEN
1155 IF l_db_cashflow_levels.id = p_cashflow_level_tbl(j).cashflow_level_id THEN
1156 l_delete_flag := 'N';
1157 END IF;
1158 END IF;
1159 END LOOP;
1160
1161 IF l_delete_flag = 'Y' THEN
1162 l_cashflow_level_tbl(i).id := l_db_cashflow_levels.id;
1163 i := i + 1;
1164 END IF;
1165 END LOOP;
1166 ELSE
1167 FOR l_db_cashflow_levels IN c_db_cashflow_levels LOOP
1168 l_cashflow_level_tbl(i).id := l_db_cashflow_levels.id;
1169 i := i + 1;
1170 END LOOP;
1171 END IF;
1172
1173 x_deleted_cashflow_level_tbl := l_cashflow_level_tbl;
1174 x_return_status := G_RET_STS_SUCCESS;
1175
1176 EXCEPTION
1177
1178 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1179 x_return_status := G_RET_STS_ERROR;
1180
1181 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1182 x_return_status := G_RET_STS_UNEXP_ERROR;
1183
1184 WHEN OTHERS THEN
1185 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1186 p_msg_name => G_DB_ERROR,
1187 p_token1 => G_PROG_NAME_TOKEN,
1188 p_token1_value => l_api_name,
1189 p_token2 => G_SQLCODE_TOKEN,
1190 p_token2_value => sqlcode,
1191 p_token3 => G_SQLERRM_TOKEN,
1192 p_token3_value => sqlerrm);
1193
1194 x_return_status := G_RET_STS_UNEXP_ERROR;
1195
1196 END get_deleted_cashflow_levels;
1197
1198
1199 --------------------------------------
1200 -- PROCEDURE process_cashflow_deletion
1201 --------------------------------------
1202 PROCEDURE process_cashflow_deletion (
1203 p_cashflow_header_rec IN cashflow_header_rec_type
1204 ,p_cashflow_level_tbl IN cashflow_level_tbl_type
1205 ,x_return_status OUT NOCOPY VARCHAR2
1206 ,x_msg_count OUT NOCOPY NUMBER
1207 ,x_msg_data OUT NOCOPY VARCHAR2
1208 ) IS
1209
1210 l_program_name CONSTANT VARCHAR2(30) := 'process_cashflow_deletion';
1211 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
1212
1213 l_deleted_cf_level_tbl cflv_tbl_type;
1214
1215 l_cafv_rec cafv_rec_type;
1216 l_cfov_rec cfov_rec_type;
1217
1218 BEGIN
1219
1220 get_deleted_cashflow_levels (
1221 p_cashflow_header_id => p_cashflow_header_rec.cashflow_header_id
1222 ,p_cashflow_level_tbl => p_cashflow_level_tbl
1223 ,x_deleted_cashflow_level_tbl => l_deleted_cf_level_tbl
1224 ,x_return_status => x_return_status
1225 );
1226
1227 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
1228 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1229 ELSIF x_return_status = G_RET_STS_ERROR THEN
1230 RAISE OKL_API.G_EXCEPTION_ERROR;
1231 END IF;
1232
1233 IF l_deleted_cf_level_tbl.COUNT > 0 THEN
1234
1235 okl_cfl_pvt.delete_row (
1236 p_api_version => G_API_VERSION
1237 ,p_init_msg_list => G_FALSE
1238 ,x_return_status => x_return_status
1239 ,x_msg_count => x_msg_count
1240 ,x_msg_data => x_msg_data
1241 ,p_cflv_tbl => l_deleted_cf_level_tbl
1242 );
1243
1244 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
1245 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1246 ELSIF x_return_status = G_RET_STS_ERROR THEN
1247 RAISE OKL_API.G_EXCEPTION_ERROR;
1248 END IF;
1249
1250 END IF;
1251
1252 IF p_cashflow_level_tbl.COUNT = 0 THEN
1253
1254 l_cafv_rec.id := p_cashflow_header_rec.cashflow_header_id;
1255
1256 okl_caf_pvt.delete_row (
1257 p_api_version => G_API_VERSION
1258 ,p_init_msg_list => G_FALSE
1259 ,x_return_status => x_return_status
1260 ,x_msg_count => x_msg_count
1261 ,x_msg_data => x_msg_data
1262 ,p_cafv_rec => l_cafv_rec
1263 );
1264
1265 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
1266 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1267 ELSIF x_return_status = G_RET_STS_ERROR THEN
1268 RAISE OKL_API.G_EXCEPTION_ERROR;
1269 END IF;
1270
1271 l_cfov_rec.id := p_cashflow_header_rec.cashflow_object_id;
1272
1273 okl_cfo_pvt.delete_row (
1274 p_api_version => G_API_VERSION
1275 ,p_init_msg_list => G_FALSE
1276 ,x_return_status => x_return_status
1277 ,x_msg_count => x_msg_count
1278 ,x_msg_data => x_msg_data
1279 ,p_cfov_rec => l_cfov_rec
1280 );
1281
1282 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
1283 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1284 ELSIF x_return_status = G_RET_STS_ERROR THEN
1285 RAISE OKL_API.G_EXCEPTION_ERROR;
1286 END IF;
1287
1288 END IF;
1289
1290 EXCEPTION
1291
1292 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1293 x_return_status := G_RET_STS_ERROR;
1294
1295 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1296 x_return_status := G_RET_STS_UNEXP_ERROR;
1297
1298 WHEN OTHERS THEN
1299 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1300 p_msg_name => G_DB_ERROR,
1301 p_token1 => G_PROG_NAME_TOKEN,
1302 p_token1_value => l_api_name,
1303 p_token2 => G_SQLCODE_TOKEN,
1304 p_token2_value => sqlcode,
1305 p_token3 => G_SQLERRM_TOKEN,
1306 p_token3_value => sqlerrm);
1307
1308 x_return_status := G_RET_STS_UNEXP_ERROR;
1309
1310 END process_cashflow_deletion;
1311
1312 ----------------------------
1313 -- PROCEDURE create_cashflow
1314 ----------------------------
1315 PROCEDURE create_cashflow (
1316 p_api_version IN NUMBER
1317 ,p_init_msg_list IN VARCHAR2
1318 ,p_transaction_control IN VARCHAR2
1319 ,p_cashflow_header_rec IN OUT NOCOPY cashflow_header_rec_type
1320 ,p_cashflow_level_tbl IN OUT NOCOPY cashflow_level_tbl_type
1321 ,x_return_status OUT NOCOPY VARCHAR2
1322 ,x_msg_count OUT NOCOPY NUMBER
1323 ,x_msg_data OUT NOCOPY VARCHAR2) IS
1324
1325 l_program_name CONSTANT VARCHAR2(30) := 'create_cashflow';
1326 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
1327
1328 BEGIN
1329
1330 IF p_transaction_control = G_TRUE THEN
1331 SAVEPOINT l_program_name;
1332 END IF;
1333
1334 IF p_init_msg_list = G_TRUE THEN
1335 FND_MSG_PUB.initialize;
1336 END IF;
1337
1338 IF p_cashflow_level_tbl.COUNT = 0 THEN
1339 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME, p_msg_name => 'OKL_LEVEL_REQD_LEVELS');
1340 RAISE OKL_API.G_EXCEPTION_ERROR;
1341 END IF;
1342
1343 process_cashflow (
1344 p_cashflow_header_rec => p_cashflow_header_rec
1345 ,p_cashflow_level_tbl => p_cashflow_level_tbl
1346 ,x_return_status => x_return_status
1347 );
1348
1349 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
1350 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1351 ELSIF x_return_status = G_RET_STS_ERROR THEN
1352 RAISE OKL_API.G_EXCEPTION_ERROR;
1353 END IF;
1354
1355 insert_rows (
1356 p_cashflow_header_rec => p_cashflow_header_rec
1357 ,p_cashflow_level_tbl => p_cashflow_level_tbl
1358 ,x_return_status => x_return_status
1359 ,x_msg_count => x_msg_count
1360 ,x_msg_data => x_msg_data
1361 );
1362
1363 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
1364 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1365 ELSIF x_return_status = G_RET_STS_ERROR THEN
1366 RAISE OKL_API.G_EXCEPTION_ERROR;
1367 END IF;
1368
1369 EXCEPTION
1370
1371 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1372
1373 IF p_transaction_control = G_TRUE THEN
1374 ROLLBACK TO l_program_name;
1375 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1376 END IF;
1377
1378 x_return_status := G_RET_STS_ERROR;
1379
1380 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1381
1382 IF p_transaction_control = G_TRUE THEN
1383 ROLLBACK TO l_program_name;
1384 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1385 END IF;
1386
1387 x_return_status := G_RET_STS_UNEXP_ERROR;
1388
1389 WHEN OTHERS THEN
1390
1391 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1392 p_msg_name => G_DB_ERROR,
1393 p_token1 => G_PROG_NAME_TOKEN,
1394 p_token1_value => l_api_name,
1395 p_token2 => G_SQLCODE_TOKEN,
1396 p_token2_value => sqlcode,
1397 p_token3 => G_SQLERRM_TOKEN,
1398 p_token3_value => sqlerrm);
1399
1400 IF p_transaction_control = G_TRUE THEN
1401 ROLLBACK TO l_program_name;
1402 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1403 END IF;
1404
1405 x_return_status := G_RET_STS_UNEXP_ERROR;
1406
1407 END create_cashflow;
1408
1409
1410 ----------------------------
1411 -- PROCEDURE update_cashflow
1412 ----------------------------
1413 PROCEDURE update_cashflow (
1414 p_api_version IN NUMBER
1415 ,p_init_msg_list IN VARCHAR2
1416 ,p_transaction_control IN VARCHAR2
1417 ,p_cashflow_header_rec IN OUT NOCOPY cashflow_header_rec_type
1418 ,p_cashflow_level_tbl IN OUT NOCOPY cashflow_level_tbl_type
1419 ,x_return_status OUT NOCOPY VARCHAR2
1420 ,x_msg_count OUT NOCOPY NUMBER
1421 ,x_msg_data OUT NOCOPY VARCHAR2) IS
1422
1423 l_program_name CONSTANT VARCHAR2(30) := 'update_cashflow';
1424 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
1425
1426 BEGIN
1427
1428 IF p_transaction_control = G_TRUE THEN
1429 SAVEPOINT l_program_name;
1430 END IF;
1431
1432 IF p_init_msg_list = G_TRUE THEN
1433 FND_MSG_PUB.initialize;
1434 END IF;
1435
1436 IF p_cashflow_level_tbl.COUNT = 0 AND p_cashflow_header_rec.stream_type_id IS NOT NULL THEN
1437 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME, p_msg_name => 'OKL_LEVEL_REQD_LEVELS');
1438 RAISE OKL_API.G_EXCEPTION_ERROR;
1439 END IF;
1440
1441 process_cashflow_deletion (
1442 p_cashflow_header_rec => p_cashflow_header_rec
1443 ,p_cashflow_level_tbl => p_cashflow_level_tbl
1444 ,x_return_status => x_return_status
1445 ,x_msg_count => x_msg_count
1446 ,x_msg_data => x_msg_data
1447 );
1448
1449 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
1450 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1451 ELSIF x_return_status = G_RET_STS_ERROR THEN
1452 RAISE OKL_API.G_EXCEPTION_ERROR;
1453 END IF;
1454
1455 process_cashflow (
1456 p_cashflow_header_rec => p_cashflow_header_rec
1457 ,p_cashflow_level_tbl => p_cashflow_level_tbl
1458 ,x_return_status => x_return_status
1459 );
1460
1461 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
1462 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1463 ELSIF x_return_status = G_RET_STS_ERROR THEN
1464 RAISE OKL_API.G_EXCEPTION_ERROR;
1465 END IF;
1466
1467 insert_update_rows (
1468 p_cashflow_header_rec => p_cashflow_header_rec
1469 ,p_cashflow_level_tbl => p_cashflow_level_tbl
1470 ,x_return_status => x_return_status
1471 ,x_msg_count => x_msg_count
1472 ,x_msg_data => x_msg_data
1473 );
1474
1475 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
1476 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1477 ELSIF x_return_status = G_RET_STS_ERROR THEN
1478 RAISE OKL_API.G_EXCEPTION_ERROR;
1479 END IF;
1480
1481 EXCEPTION
1482
1483 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1484
1485 IF p_transaction_control = G_TRUE THEN
1486 ROLLBACK TO l_program_name;
1487 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1488 END IF;
1489
1490 x_return_status := G_RET_STS_ERROR;
1491
1492 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1493
1494 IF p_transaction_control = G_TRUE THEN
1495 ROLLBACK TO l_program_name;
1496 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1497 END IF;
1498
1499 x_return_status := G_RET_STS_UNEXP_ERROR;
1500
1501 WHEN OTHERS THEN
1502
1503 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1504 p_msg_name => G_DB_ERROR,
1505 p_token1 => G_PROG_NAME_TOKEN,
1506 p_token1_value => l_api_name,
1507 p_token2 => G_SQLCODE_TOKEN,
1508 p_token2_value => sqlcode,
1509 p_token3 => G_SQLERRM_TOKEN,
1510 p_token3_value => sqlerrm);
1511
1512 IF p_transaction_control = G_TRUE THEN
1513 ROLLBACK TO l_program_name;
1514 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1515 END IF;
1516
1517 x_return_status := G_RET_STS_UNEXP_ERROR;
1518
1519 END update_cashflow;
1520
1521
1522 --------------------------------
1523 -- PROCEDURE duplicate_cashflows
1524 --------------------------------
1525 PROCEDURE duplicate_cashflows (
1526 p_api_version IN NUMBER
1527 ,p_init_msg_list IN VARCHAR2
1528 ,p_transaction_control IN VARCHAR2
1529 ,p_source_object_code IN VARCHAR2
1530 ,p_source_object_id IN NUMBER
1531 ,p_target_object_id IN NUMBER
1532 ,p_quote_id IN NUMBER
1533 ,x_return_status OUT NOCOPY VARCHAR2
1534 ,x_msg_count OUT NOCOPY NUMBER
1535 ,x_msg_data OUT NOCOPY VARCHAR2
1536 ,p_target_object_code IN VARCHAR2 DEFAULT NULL
1537 ) IS
1538
1539 l_program_name CONSTANT VARCHAR2(30) := 'duplicate_cashflows';
1540 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
1541
1542 l_cashflow_header_rec cashflow_header_rec_type;
1543 l_cashflow_level_tbl cashflow_level_tbl_type;
1544
1545 l_return_status VARCHAR2(1);
1546 l_source_table VARCHAR2(30);
1547 l_cashflow_object_id NUMBER;
1548 l_cft_code VARCHAR2(30);
1549 lv_frq_code VARCHAR2(30);
1550 lv_parent_object_code VARCHAR2(30);
1551 i BINARY_INTEGER := 0;
1552 j BINARY_INTEGER := 0;
1553 --Bug # 5021937 ssdeshpa start
1554 l_qte_price_method VARCHAR2(30);
1555 l_qte_status VARCHAR2(30);
1556 l_caf_status VARCHAR2(30);
1557
1558 CURSOR c_lq_hdr_rec_cur(p_lease_quote_id NUMBER)
1559 IS
1560 SELECT pricing_method,status,parent_object_code
1561 FROM OKL_LEASE_QUOTES_B
1562 where id = p_lease_quote_id;
1563 --Bug # 5021937 ssdeshpa end;
1564 CURSOR c_cf_objects (p_oty_code VARCHAR2, p_source_table VARCHAR2, p_source_id NUMBER)
1565 IS
1566 SELECT id
1567 FROM okl_cash_flow_objects
1568 WHERE
1569 oty_code = p_oty_code
1570 AND source_table = p_source_table
1571 AND source_id = p_source_id;
1572
1573 CURSOR c_cf_headers (p_cfo_id NUMBER)
1574 IS
1575 SELECT id, sty_id, due_arrears_yn, cft_code , sts_code
1576 FROM okl_cash_flows
1577 WHERE cfo_id = p_cfo_id;
1578 --Bug # 5021937 ssdeshpa start
1579 --changed the Cursor
1580 CURSOR c_cf_levels (p_caf_id NUMBER)
1581 IS
1582 SELECT amount, number_of_periods, fqy_code, stub_days, stub_amount, rate , missing_pmt_flag,
1583 start_date --05-Nov-2009 sechawla 9001346 : added
1584 FROM OKL_CASH_FLOW_LEVELS
1585 WHERE caf_id = p_caf_id
1586 order by start_date ;--05-Nov-2009 sechawla 9001346 : added
1587 --Bug # 5021937 ssdeshpa End
1588 BEGIN
1589
1590 IF p_transaction_control = G_TRUE THEN
1591 SAVEPOINT l_program_name;
1592 END IF;
1593
1594 IF p_init_msg_list = G_TRUE THEN
1595 FND_MSG_PUB.initialize;
1596 END IF;
1597
1598 get_source_table (
1599 p_source_object_code => p_source_object_code
1600 ,x_source_table => l_source_table
1601 ,x_return_status => x_return_status
1602 );
1603
1604 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
1605 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1606 ELSIF x_return_status = G_RET_STS_ERROR THEN
1607 RAISE OKL_API.G_EXCEPTION_ERROR;
1608 END IF;
1609 --Bug # 5021937 ssdeshpa start
1610 --This will fetch current Quote Status and Pricing Methods
1611 OPEN c_lq_hdr_rec_cur(p_quote_id);
1612 FETCH c_lq_hdr_rec_cur INTO l_qte_price_method,l_qte_status,lv_parent_object_code;
1613 CLOSE c_lq_hdr_rec_cur;
1614 --Bug # 5021937 ssdeshpa end
1615 FOR l_cf_object IN c_cf_objects (
1616 p_oty_code => p_source_object_code
1617 ,p_source_table => l_source_table
1618 , p_source_id => p_source_object_id) LOOP
1619
1620 OPEN c_cf_headers(p_cfo_id => l_cf_object.id);
1621 --Bug # 5021937 ssdeshpa Start
1622 FETCH c_cf_headers INTO l_cashflow_header_rec.cashflow_header_id,
1623 l_cashflow_header_rec.stream_type_id,
1624 l_cashflow_header_rec.arrears_flag,
1625 l_cft_code,
1626 l_caf_status;
1627 --Bug # 5021937 ssdeshpa end
1628 CLOSE c_cf_headers;
1629
1630 IF ( l_caf_status = 'WORK' AND l_qte_status = 'CT-ACCEPTED' )
1631 OR ( l_caf_status = 'CURRENT' ) THEN
1632 -- gboomina added for bug 7033915 start
1633 l_cashflow_header_rec.status_code := l_caf_status;
1634 -- gboomina added for bug 7033915 end
1635
1636 IF p_target_object_code IS NULL THEN
1637 l_cashflow_header_rec.parent_object_code := p_source_object_code;
1638 ELSE
1639 l_cashflow_header_rec.parent_object_code := p_target_object_code;
1640 END IF;
1641 l_cashflow_header_rec.parent_object_id := p_target_object_id;
1642 l_cashflow_header_rec.quote_id := p_quote_id;
1643 /*
1644 -- Fetch the Quote type
1645 SELECT parent_object_code
1646 INTO lv_parent_object_code
1647 FROM okl_lease_quotes_b
1648 WHERE id = p_quote_id;*/
1649
1650 IF (lv_parent_object_code = 'LEASEOPP') THEN
1651 l_cashflow_header_rec.quote_type_code := 'LQ';
1652 ELSIF (lv_parent_object_code = 'LEASEAPP') THEN
1653 l_cashflow_header_rec.quote_type_code := 'LA';
1654 ELSE
1655 l_cashflow_header_rec.quote_type_code := 'QQ';
1656 END IF;
1657 -- End
1658
1659 IF (l_cft_code = 'PAYMENT_SCHEDULE') THEN
1660 l_cashflow_header_rec.type_code := 'INFLOW';
1661 ELSIF (l_cft_code = 'OUTFLOW_SCHEDULE') THEN
1662 l_cashflow_header_rec.type_code := 'OUTFLOW';
1663 END IF;
1664 --asawanka bug 4936130 changes start
1665 j := 0;
1666 l_cashflow_level_tbl.delete;
1667 --asawanka bug 4936130 changes end
1668 FOR l_cf_level IN c_cf_levels (p_caf_id => l_cashflow_header_rec.cashflow_header_id) LOOP
1669 --Bug # 5021937 ssdeshpa start
1670 /*If pricing Method is solve For Missing Payment then,For Cash Flows which are Missing Payment For Quote
1671 Cash flow Level's Missing Payment Flag ,Periodic Amount ,Stub Amount will be nullified.
1672
1673 If pricing Method is 'Solve for Payment' then,Cash Flow's Periodic Amount field will be nullified.
1674
1675 For 'Solve for Yields' Rate will be nullified.*/
1676
1677 IF(l_qte_status <> 'CT-ACCEPTED') THEN
1678 IF(l_qte_price_method ='SM' AND l_cf_level.missing_pmt_flag = 'Y') THEN
1679 l_cashflow_level_tbl(j).periods := l_cf_level.number_of_periods;
1680 l_cashflow_level_tbl(j).periodic_amount := null;
1681 l_cashflow_level_tbl(j).stub_days := l_cf_level.stub_days;
1682 l_cashflow_level_tbl(j).stub_amount := null;
1683 l_cashflow_level_tbl(j).rate := l_cf_level.rate;
1684 l_cashflow_level_tbl(j).missing_pmt_flag := null;
1685 ELSIF(l_qte_price_method ='SP') THEN
1686 l_cashflow_level_tbl(j).periods := l_cf_level.number_of_periods;
1687 -- Bug#7140398 - Ensure that amounts are copied over for fees/services as
1688 -- rate is not specified for fees/services
1689 IF (p_source_object_code IN ('QUOTED_FEE', 'QUOTED_SERVICE')) THEN
1690 l_cashflow_level_tbl(j).periodic_amount := l_cf_level.amount;
1691 l_cashflow_level_tbl(j).stub_amount := l_cf_level.stub_amount;
1692 ELSE
1693 l_cashflow_level_tbl(j).periodic_amount := null;
1694 l_cashflow_level_tbl(j).stub_amount := null;
1695 END IF;
1696 l_cashflow_level_tbl(j).stub_days := l_cf_level.stub_days;
1697 l_cashflow_level_tbl(j).rate := l_cf_level.rate;
1698
1699 ELSIF(l_qte_price_method ='SY') THEN
1700 l_cashflow_level_tbl(j).periods := l_cf_level.number_of_periods;
1701 l_cashflow_level_tbl(j).periodic_amount := l_cf_level.amount;
1702 l_cashflow_level_tbl(j).stub_days := l_cf_level.stub_days;
1703 l_cashflow_level_tbl(j).stub_amount := l_cf_level.stub_amount;
1704 l_cashflow_level_tbl(j).rate := null;
1705 ELSE
1706 l_cashflow_level_tbl(j).periods := l_cf_level.number_of_periods;
1707 l_cashflow_level_tbl(j).periodic_amount := l_cf_level.amount;
1708 l_cashflow_level_tbl(j).stub_days := l_cf_level.stub_days;
1709 l_cashflow_level_tbl(j).stub_amount := l_cf_level.stub_amount;
1710 l_cashflow_level_tbl(j).rate := l_cf_level.rate;
1711 END IF;
1712 ELSE
1713 l_cashflow_level_tbl(j).periods := l_cf_level.number_of_periods;
1714 l_cashflow_level_tbl(j).periodic_amount := l_cf_level.amount;
1715 l_cashflow_level_tbl(j).stub_days := l_cf_level.stub_days;
1716 l_cashflow_level_tbl(j).stub_amount := l_cf_level.stub_amount;
1717 l_cashflow_level_tbl(j).rate := l_cf_level.rate;
1718
1719 END IF;
1720 --Bug # 5021937 ssdeshpa end;
1721 lv_frq_code := l_cf_level.fqy_code;
1722 j := j + 1;
1723 END LOOP;
1724
1725 l_cashflow_header_rec.frequency_code := lv_frq_code;
1726
1727 -- Duplicate equals to 'create' mode, for sanity check purpose
1728 FOR i IN l_cashflow_level_tbl.FIRST .. l_cashflow_level_tbl.LAST LOOP
1729 IF l_cashflow_level_tbl.EXISTS(i) THEN
1730 l_cashflow_level_tbl(i).record_mode := 'create';
1731 END IF;
1732 END LOOP;
1733
1734 create_cashflow ( p_api_version => G_API_VERSION
1735 ,p_init_msg_list => G_FALSE
1736 ,p_transaction_control => G_FALSE
1737 ,p_cashflow_header_rec => l_cashflow_header_rec
1738 ,p_cashflow_level_tbl => l_cashflow_level_tbl
1739 ,x_return_status => l_return_status
1740 ,x_msg_count => x_msg_count
1741 ,x_msg_data => x_msg_data);
1742
1743 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
1744 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1745 ELSIF l_return_status = G_RET_STS_ERROR THEN
1746 RAISE OKL_API.G_EXCEPTION_ERROR;
1747 END IF;
1748
1749 -- i := i + 1;
1750 END IF;
1751 END LOOP;
1752
1753 x_return_status := G_RET_STS_SUCCESS;
1754
1755 EXCEPTION
1756
1757 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1758
1759 IF p_transaction_control = G_TRUE THEN
1760 ROLLBACK TO l_program_name;
1761 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1762 END IF;
1763
1764 x_return_status := G_RET_STS_ERROR;
1765
1766 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1767
1768 IF p_transaction_control = G_TRUE THEN
1769 ROLLBACK TO l_program_name;
1770 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1771 END IF;
1772
1773 x_return_status := G_RET_STS_UNEXP_ERROR;
1774
1775 WHEN OTHERS THEN
1776
1777 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1778 p_msg_name => G_DB_ERROR,
1779 p_token1 => G_PROG_NAME_TOKEN,
1780 p_token1_value => l_api_name,
1781 p_token2 => G_SQLCODE_TOKEN,
1782 p_token2_value => sqlcode,
1783 p_token3 => G_SQLERRM_TOKEN,
1784 p_token3_value => sqlerrm);
1785
1786 IF p_transaction_control = G_TRUE THEN
1787 ROLLBACK TO l_program_name;
1788 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1789 END IF;
1790
1791 x_return_status := G_RET_STS_UNEXP_ERROR;
1792
1793 END duplicate_cashflows;
1794
1795
1796 -----------------------------
1797 -- PROCEDURE delete_cashflows
1798 -----------------------------
1799 PROCEDURE delete_cashflows (
1800 p_api_version IN NUMBER
1801 ,p_init_msg_list IN VARCHAR2
1802 ,p_transaction_control IN VARCHAR2
1803 ,p_source_object_code IN VARCHAR2
1804 ,p_source_object_id IN NUMBER
1805 ,x_return_status OUT NOCOPY VARCHAR2
1806 ,x_msg_count OUT NOCOPY NUMBER
1807 ,x_msg_data OUT NOCOPY VARCHAR2
1808 ) IS
1809
1810 l_program_name CONSTANT VARCHAR2(30) := 'delete_cashflows';
1811 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
1812
1813 l_source_table VARCHAR2(30);
1814
1815
1816 BEGIN
1817
1818 get_source_table (
1819 p_source_object_code => p_source_object_code
1820 ,x_source_table => l_source_table
1821 ,x_return_status => x_return_status
1822 );
1823
1824 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
1825 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1826 ELSIF x_return_status = G_RET_STS_ERROR THEN
1827 RAISE OKL_API.G_EXCEPTION_ERROR;
1828 END IF;
1829
1830 DELETE FROM okl_cash_flow_levels WHERE caf_id IN
1831 (SELECT id FROM okl_cash_flows WHERE cfo_id IN
1832 (SELECT id
1833 FROM okl_cash_flow_objects
1834 WHERE oty_code = p_source_object_code AND source_table = l_source_table AND source_id = p_source_object_id
1835 )
1836 );
1837
1838 DELETE FROM okl_cash_flows WHERE cfo_id IN
1839 (SELECT id
1840 FROM okl_cash_flow_objects
1841 WHERE oty_code = p_source_object_code AND source_table = l_source_table AND source_id = p_source_object_id
1842 );
1843
1844 DELETE FROM okl_cash_flow_objects
1845 WHERE oty_code = p_source_object_code AND source_table = l_source_table AND source_id = p_source_object_id;
1846
1847 x_return_status := G_RET_STS_SUCCESS;
1848
1849 EXCEPTION
1850
1851 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1852
1853 IF p_transaction_control = G_TRUE THEN
1854 ROLLBACK TO l_program_name;
1855 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1856 END IF;
1857
1858 x_return_status := G_RET_STS_ERROR;
1859
1860 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1861
1862 IF p_transaction_control = G_TRUE THEN
1863 ROLLBACK TO l_program_name;
1864 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1865 END IF;
1866
1867 x_return_status := G_RET_STS_UNEXP_ERROR;
1868
1869 WHEN OTHERS THEN
1870
1871 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1872 p_msg_name => G_DB_ERROR,
1873 p_token1 => G_PROG_NAME_TOKEN,
1874 p_token1_value => l_api_name,
1875 p_token2 => G_SQLCODE_TOKEN,
1876 p_token2_value => sqlcode,
1877 p_token3 => G_SQLERRM_TOKEN,
1878 p_token3_value => sqlerrm);
1879
1880 IF p_transaction_control = G_TRUE THEN
1881 ROLLBACK TO l_program_name;
1882 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1883 END IF;
1884
1885 x_return_status := G_RET_STS_UNEXP_ERROR;
1886
1887 END delete_cashflows;
1888
1889
1890 ----------------------------
1891 -- PROCEDURE delete_cashflow
1892 ----------------------------
1893 PROCEDURE delete_cashflow (
1894 p_api_version IN NUMBER
1895 ,p_init_msg_list IN VARCHAR2
1896 ,p_transaction_control IN VARCHAR2
1897 ,p_cashflow_header_id IN NUMBER
1898 ,x_return_status OUT NOCOPY VARCHAR2
1899 ,x_msg_count OUT NOCOPY NUMBER
1900 ,x_msg_data OUT NOCOPY VARCHAR2
1901 ) IS
1902
1903 l_program_name CONSTANT VARCHAR2(30) := 'delete_cashflow';
1904 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
1905
1906 BEGIN
1907
1908 DELETE FROM okl_cash_flow_levels WHERE caf_id = p_cashflow_header_id;
1909 DELETE FROM okl_cash_flows WHERE id = p_cashflow_header_id;
1910
1911 -- WIP
1912 -- EXISTENCE CHECK REQD FOR ADDl CF ON THE OBJECT
1913
1914 x_return_status := G_RET_STS_SUCCESS;
1915
1916 EXCEPTION
1917
1918 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1919
1920 IF p_transaction_control = G_TRUE THEN
1921 ROLLBACK TO l_program_name;
1922 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1923 END IF;
1924
1925 x_return_status := G_RET_STS_ERROR;
1926
1927 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1928
1929 IF p_transaction_control = G_TRUE THEN
1930 ROLLBACK TO l_program_name;
1931 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1932 END IF;
1933
1934 x_return_status := G_RET_STS_UNEXP_ERROR;
1935
1936 WHEN OTHERS THEN
1937
1938 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1939 p_msg_name => G_DB_ERROR,
1940 p_token1 => G_PROG_NAME_TOKEN,
1941 p_token1_value => l_api_name,
1942 p_token2 => G_SQLCODE_TOKEN,
1943 p_token2_value => sqlcode,
1944 p_token3 => G_SQLERRM_TOKEN,
1945 p_token3_value => sqlerrm);
1946
1947 IF p_transaction_control = G_TRUE THEN
1948 ROLLBACK TO l_program_name;
1949 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1950 END IF;
1951
1952 x_return_status := G_RET_STS_UNEXP_ERROR;
1953
1954 END delete_cashflow;
1955
1956
1957 ----------------------------------
1958 -- PROCEDURE delete_cashflow_level
1959 ----------------------------------
1960 PROCEDURE delete_cashflow_level (
1961 p_api_version IN NUMBER
1962 ,p_init_msg_list IN VARCHAR2
1963 ,p_transaction_control IN VARCHAR2
1964 ,p_cashflow_level_id IN NUMBER
1965 ,x_return_status OUT NOCOPY VARCHAR2
1966 ,x_msg_count OUT NOCOPY NUMBER
1967 ,x_msg_data OUT NOCOPY VARCHAR2
1968 ) IS
1969
1970 l_program_name CONSTANT VARCHAR2(30) := 'delete_cashflow_level';
1971 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
1972
1973 BEGIN
1974
1975 DELETE FROM okl_cash_flow_levels WHERE id = p_cashflow_level_id;
1976
1977 x_return_status := G_RET_STS_SUCCESS;
1978
1979 EXCEPTION
1980
1981 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1982
1983 IF p_transaction_control = G_TRUE THEN
1984 ROLLBACK TO l_program_name;
1985 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1986 END IF;
1987
1988 x_return_status := G_RET_STS_ERROR;
1989
1990 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1991
1992 IF p_transaction_control = G_TRUE THEN
1993 ROLLBACK TO l_program_name;
1994 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1995 END IF;
1996
1997 x_return_status := G_RET_STS_UNEXP_ERROR;
1998
1999 WHEN OTHERS THEN
2000
2001 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
2002 p_msg_name => G_DB_ERROR,
2003 p_token1 => G_PROG_NAME_TOKEN,
2004 p_token1_value => l_api_name,
2005 p_token2 => G_SQLCODE_TOKEN,
2006 p_token2_value => sqlcode,
2007 p_token3 => G_SQLERRM_TOKEN,
2008 p_token3_value => sqlerrm);
2009
2010 IF p_transaction_control = G_TRUE THEN
2011 ROLLBACK TO l_program_name;
2012 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
2013 END IF;
2014
2015 x_return_status := G_RET_STS_UNEXP_ERROR;
2016
2017 END delete_cashflow_level;
2018
2019 ----------------------------------------
2020 -- PROCEDURE process_quote_pricing_reset
2021 ----------------------------------------
2022 PROCEDURE process_quote_pricing_reset (
2023 p_api_version IN NUMBER
2024 ,p_init_msg_list IN VARCHAR2
2025 ,p_transaction_control IN VARCHAR2
2026 ,p_quote_id IN NUMBER
2027 ,x_return_status OUT NOCOPY VARCHAR2
2028 ,x_msg_count OUT NOCOPY NUMBER
2029 ,x_msg_data OUT NOCOPY VARCHAR2
2030 ) IS
2031
2032
2033 l_program_name CONSTANT VARCHAR2(30) := 'process_quote_pricing_reset';
2034 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
2035
2036 l_deleted_cf_level_tbl cflv_tbl_type;
2037 l_sm_cashflow_levels_tbl cflv_tbl_type;
2038 x_sm_cashflow_levels_tbl cflv_tbl_type;
2039
2040 l_cafv_rec cafv_rec_type;
2041 l_cfov_rec cfov_rec_type;
2042 l_lease_qte_rec okl_lsq_pvt.lsqv_rec_type;
2043 x_lease_qte_rec okl_lsq_pvt.lsqv_rec_type;
2044 l_quote_id Number;
2045 i BINARY_INTEGER := 0;
2046 l_pricing_method VARCHAR2(30);
2047 l_cdjv_tbl okl_cdj_pvt.cdjv_tbl_type;
2048
2049 CURSOR csr_db_cashflows IS
2050 SELECT id
2051 FROM okl_cash_flows
2052 WHERE sts_code = 'WORK'
2053 AND DNZ_QTE_ID = p_quote_id;
2054
2055 CURSOR csr_db_cashflow_levels IS
2056 SELECT distinct a.id
2057 FROM okl_cash_flow_levels a,
2058 okl_cash_flows b
2059 WHERE a.caf_id = b.id
2060 AND b.sts_code = 'WORK'
2061 AND B.DNZ_QTE_ID = p_quote_id;
2062
2063 CURSOR csr_db_cashflow_objects IS
2064 SELECT cfo_id
2065 FROM okl_cash_flows
2066 WHERE sts_code = 'WORK'
2067 AND DNZ_QTE_ID = p_quote_id;
2068
2069 CURSOR csr_sm_cashflow_levels IS
2070 SELECT distinct a.id, a.object_version_number
2071 FROM okl_cash_flow_levels a,
2072 okl_cash_flows b,
2073 okl_lease_quotes_b qte
2074 WHERE a.caf_id = b.id
2075 AND a.missing_pmt_flag = 'Y'
2076 AND B.DNZ_QTE_ID = p_quote_id
2077 AND B.DNZ_QTE_ID = qte.id
2078 AND qte.pricing_method = 'SM';
2079
2080 CURSOR csr_qte_adjustments(p_adj_src_type IN VARCHAR2, p_qte_id IN NUMBER)IS
2081 select cdj.id
2082 from okl_cost_adjustments_b cdj,
2083 okl_assets_b ast
2084 where cdj.adjustment_source_type = p_adj_src_type
2085 and cdj.parent_object_code ='ASSET'
2086 and cdj.parent_object_id = ast.id
2087 and ast.parent_object_code = 'LEASEQUOTE'
2088 and ast.parent_object_id = p_qte_id;
2089 BEGIN
2090
2091 FOR l_db_cashflow_levels IN csr_db_cashflow_levels LOOP
2092
2093 l_deleted_cf_level_tbl(i).id := l_db_cashflow_levels.id;
2094 i := i + 1;
2095
2096 END LOOP;
2097
2098 IF l_deleted_cf_level_tbl.COUNT > 0 THEN
2099
2100 okl_cfl_pvt.delete_row (
2101 p_api_version => G_API_VERSION
2102 ,p_init_msg_list => G_FALSE
2103 ,x_return_status => x_return_status
2104 ,x_msg_count => x_msg_count
2105 ,x_msg_data => x_msg_data
2106 ,p_cflv_tbl => l_deleted_cf_level_tbl
2107 );
2108
2109 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
2110 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2111 ELSIF x_return_status = G_RET_STS_ERROR THEN
2112 RAISE OKL_API.G_EXCEPTION_ERROR;
2113 END IF;
2114
2115 END IF;
2116
2117 FOR l_db_cashflow_objects IN csr_db_cashflow_objects LOOP
2118
2119 l_cfov_rec.id := l_db_cashflow_objects.cfo_id;
2120
2121 okl_cfo_pvt.delete_row (
2122 p_api_version => G_API_VERSION
2123 ,p_init_msg_list => G_FALSE
2124 ,x_return_status => x_return_status
2125 ,x_msg_count => x_msg_count
2126 ,x_msg_data => x_msg_data
2127 ,p_cfov_rec => l_cfov_rec
2128 );
2129
2130 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
2131 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2132 ELSIF x_return_status = G_RET_STS_ERROR THEN
2133 RAISE OKL_API.G_EXCEPTION_ERROR;
2134 END IF;
2135
2136 END LOOP;
2137
2138 FOR l_db_cashflows IN csr_db_cashflows LOOP
2139
2140 l_cafv_rec.id := l_db_cashflows.id;
2141
2142 okl_caf_pvt.delete_row (
2143 p_api_version => G_API_VERSION
2144 ,p_init_msg_list => G_FALSE
2145 ,x_return_status => x_return_status
2146 ,x_msg_count => x_msg_count
2147 ,x_msg_data => x_msg_data
2148 ,p_cafv_rec => l_cafv_rec
2149 );
2150
2151 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
2152 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2153 ELSIF x_return_status = G_RET_STS_ERROR THEN
2154 RAISE OKL_API.G_EXCEPTION_ERROR;
2155 END IF;
2156
2157 END LOOP;
2158
2159 i := 0;
2160 FOR l_sm_cashflow_levels IN csr_sm_cashflow_levels LOOP
2161
2162 l_sm_cashflow_levels_tbl(i).id := l_sm_cashflow_levels.id;
2163 l_sm_cashflow_levels_tbl(i).object_version_number := l_sm_cashflow_levels.object_version_number;
2164 l_sm_cashflow_levels_tbl(i).missing_pmt_flag := NULL;
2165 l_sm_cashflow_levels_tbl(i).amount := NULL;
2166 i := i + 1;
2167
2168 END LOOP;
2169
2170 IF l_sm_cashflow_levels_tbl.COUNT > 0 THEN
2171
2172 okl_cfl_pvt.update_row (
2173 p_api_version => G_API_VERSION
2174 ,p_init_msg_list => G_FALSE
2175 ,x_return_status => x_return_status
2176 ,x_msg_count => x_msg_count
2177 ,x_msg_data => x_msg_data
2178 ,p_cflv_tbl => l_sm_cashflow_levels_tbl
2179 ,x_cflv_tbl => x_sm_cashflow_levels_tbl
2180 );
2181 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
2182 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2183 ELSIF x_return_status = G_RET_STS_ERROR THEN
2184 RAISE OKL_API.G_EXCEPTION_ERROR;
2185 END IF;
2186
2187 END IF;
2188 ---------------------------------------------------
2189 --start added abhsaxen for bug#5257890
2190 --if additional lines are available for pricinig method
2191 --ss or si or sd then delete respective adjustments.
2192 SELECT pricing_method
2193 INTO l_pricing_method
2194 FROM okl_lease_quotes_b
2195 WHERE ID = p_quote_id;
2196
2197 i := 0;
2198 IF l_pricing_method = 'SS' THEN
2199 FOR l_cdj_rec IN csr_qte_adjustments('SUBSIDY',p_quote_id) LOOP
2200 l_cdjv_tbl(i).id := l_cdj_rec.id;
2201 i := i + 1;
2202 END LOOP;
2203 ELSIF l_pricing_method = 'SD' THEN
2204 FOR l_cdj_rec IN csr_qte_adjustments('DOWN_PAYMENT',p_quote_id) LOOP
2205 l_cdjv_tbl(i).id := l_cdj_rec.id;
2206 i := i + 1;
2207 END LOOP;
2208 ELSIF l_pricing_method = 'SI' THEN
2209 FOR l_cdj_rec IN csr_qte_adjustments('TRADEIN',p_quote_id) LOOP
2210 l_cdjv_tbl(i).id := l_cdj_rec.id;
2211 i := i + 1;
2212 END LOOP;
2213 END IF;
2214 IF l_cdjv_tbl.count > 0 THEN
2215 okl_cdj_pvt.delete_row (
2216 p_api_version => G_API_VERSION
2217 ,p_init_msg_list => G_FALSE
2218 ,x_return_status => x_return_status
2219 ,x_msg_count => x_msg_count
2220 ,x_msg_data => x_msg_data
2221 ,p_cdjv_tbl => l_cdjv_tbl
2222 );
2223 END IF;
2224 --end added abhsaxen for bug#5257890
2225 ---------------------------------------------------
2226 l_lease_qte_rec.id := p_quote_id;
2227 l_lease_qte_rec.status := 'PR-INCOMPLETE';
2228 l_lease_qte_rec.iir := null;
2229 l_lease_qte_rec.booking_yield := null;
2230 l_lease_qte_rec.pirr := null;
2231 l_lease_qte_rec.airr := null;
2232 l_lease_qte_rec.sub_iir := null;
2233 l_lease_qte_rec.sub_booking_yield := null;
2234 l_lease_qte_rec.sub_pirr := null;
2235 l_lease_qte_rec.sub_airr := null;
2236
2237 OKL_LEASE_QUOTE_PVT.update_lease_qte (
2238 p_api_version => G_API_VERSION
2239 ,p_init_msg_list => G_FALSE
2240 ,p_transaction_control => G_TRUE
2241 ,p_lease_qte_rec => l_lease_qte_rec
2242 ,x_lease_qte_rec => x_lease_qte_rec
2243 ,x_return_status => x_return_status
2244 ,x_msg_count => x_msg_count
2245 ,x_msg_data => x_msg_data);
2246
2247 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
2248 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2249 ELSIF x_return_status = G_RET_STS_ERROR THEN
2250 RAISE OKL_API.G_EXCEPTION_ERROR;
2251 END IF;
2252
2253 EXCEPTION
2254
2255 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2256 x_return_status := G_RET_STS_ERROR;
2257
2258 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2259 x_return_status := G_RET_STS_UNEXP_ERROR;
2260
2261 WHEN OTHERS THEN
2262 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
2263 p_msg_name => G_DB_ERROR,
2264 p_token1 => G_PROG_NAME_TOKEN,
2265 p_token1_value => l_api_name,
2266 p_token2 => G_SQLCODE_TOKEN,
2267 p_token2_value => sqlcode,
2268 p_token3 => G_SQLERRM_TOKEN,
2269 p_token3_value => sqlerrm);
2270
2271 x_return_status := G_RET_STS_UNEXP_ERROR;
2272
2273 END process_quote_pricing_reset;
2274 --------------------------------
2275 -- PROCEDURE copy_pmts_from_est_to_quote
2276 --------------------------------
2277 PROCEDURE copy_pmts_from_est_to_quote (
2278 p_api_version IN NUMBER
2279 ,p_init_msg_list IN VARCHAR2
2280 ,p_transaction_control IN VARCHAR2
2281 ,p_estimate_id IN NUMBER
2282 ,p_quote_id IN NUMBER
2283 ,x_return_status OUT NOCOPY VARCHAR2
2284 ,x_msg_count OUT NOCOPY NUMBER
2285 ,x_msg_data OUT NOCOPY VARCHAR2
2286 ) IS
2287
2288 l_program_name CONSTANT VARCHAR2(30) := 'cpy_pmts';
2289 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
2290
2291 l_lease_qte_rec okl_lsq_pvt.lsqv_rec_type;
2292 x_lease_qte_rec okl_lsq_pvt.lsqv_rec_type;
2293 l_cashflow_header_rec cashflow_header_rec_type;
2294 l_cashflow_level_tbl cashflow_level_tbl_type;
2295
2296
2297 l_return_status VARCHAR2(1);
2298 l_cft_code VARCHAR2(30);
2299 lv_frq_code VARCHAR2(30);
2300 i BINARY_INTEGER := 0;
2301 j BINARY_INTEGER := 0;
2302
2303
2304
2305 CURSOR get_estimate_details_csr IS
2306 SELECT id,
2307 expected_start_date,
2308 term,
2309 end_of_term_option_id,
2310 pricing_method,
2311 structured_pricing,
2312 rate_template_id,
2313 rate_card_id,
2314 target_amount,
2315 target_arrears,
2316 target_frequency,
2317 lease_rate_factor,
2318 target_rate,
2319 target_rate_type,
2320 target_periods
2321 FROM okl_quick_quotes_b where
2322 id = p_estimate_id;
2323 estimate_details_csr_rec get_estimate_details_csr%ROWTYPE;
2324
2325 CURSOR get_quote_details_csr IS
2326 SELECT
2327 attribute_category
2328 ,attribute1
2329 ,attribute2
2330 ,attribute3
2331 ,attribute4
2332 ,attribute5
2333 ,attribute6
2334 ,attribute7
2335 ,attribute8
2336 ,attribute9
2337 ,attribute10
2338 ,attribute11
2339 ,attribute12
2340 ,attribute13
2341 ,attribute14
2342 ,attribute15
2343 ,reference_number
2344 ,object_version_number
2345 ,parent_object_id
2346 ,parent_object_code
2347 ,valid_from
2348 ,valid_to
2349 ,customer_bookclass
2350 ,customer_taxowner
2351 ,expected_start_date
2352 ,expected_funding_date
2353 ,expected_delivery_date
2354 ,pricing_method
2355 ,term
2356 ,product_id
2357 ,end_of_term_option_id
2358 ,usage_category
2359 ,usage_industry_class
2360 ,usage_industry_code
2361 ,usage_amount
2362 ,usage_location_id
2363 ,property_tax_applicable
2364 ,property_tax_billing_type
2365 ,upfront_tax_treatment
2366 ,upfront_tax_stream_type
2367 ,transfer_of_title
2368 ,age_of_equipment
2369 ,purchase_of_lease
2370 ,sale_and_lease_back
2371 ,interest_disclosed
2372 ,target_rate_type
2373 ,target_rate
2374 ,target_amount
2375 ,target_frequency
2376 ,target_arrears_yn
2377 ,target_periods
2378 ,structured_pricing
2379 ,line_level_pricing
2380 ,lease_rate_factor
2381 ,rate_card_id
2382 ,rate_template_id
2383 ,iir
2384 ,booking_yield
2385 ,pirr
2386 ,airr
2387 ,sub_iir
2388 ,sub_booking_yield
2389 ,sub_pirr
2390 ,sub_airr
2391 ,primary_quote
2392 ,short_description
2393 ,description
2394 ,comments
2395 FROM okl_lease_quotes_v
2396 where id= p_quote_id;
2397 quote_details_csr_rec get_quote_details_csr%ROWTYPE;
2398
2399 -- Cursor to fetch the Stream Type
2400 CURSOR c_strm_type (
2401 pdtId NUMBER,
2402 expStartDate DATE,
2403 strm_purpose VARCHAR) IS
2404 SELECT STRM.STY_ID PAYMENT_TYPE_ID,
2405 STRM.STY_NAME PAYMENT_TYPE,
2406 STRM.START_DATE,
2407 STRM.END_DATE,
2408 STRM.STY_PURPOSE
2409 FROM OKL_STRM_TMPT_PRIMARY_UV STRM
2410 WHERE STY_PURPOSE = strm_purpose
2411 AND START_DATE <= expStartDate
2412 AND NVL(END_DATE, expStartDate) >= expStartDate
2413 AND STRM.PDT_ID = pdtId;
2414
2415 CURSOR c_cf_objects (p_oty_code VARCHAR2, p_source_table VARCHAR2, p_source_id NUMBER)
2416 IS
2417 SELECT id
2418 FROM okl_cash_flow_objects
2419 WHERE
2420 oty_code = p_oty_code
2421 AND source_table = p_source_table
2422 AND source_id = p_source_id;
2423
2424 CURSOR c_cf_headers (p_cfo_id NUMBER)
2425 IS
2426 SELECT id, sty_id, due_arrears_yn, cft_code
2427 FROM okl_cash_flows
2428 WHERE cfo_id = p_cfo_id;
2429
2430 CURSOR c_cf_levels (p_caf_id NUMBER)
2431 IS
2432 SELECT amount, number_of_periods, fqy_code, stub_days, stub_amount, rate
2433 FROM OKL_CASH_FLOW_LEVELS
2434 WHERE caf_id = p_caf_id;
2435
2436 BEGIN
2437
2438 IF p_transaction_control = G_TRUE THEN
2439 SAVEPOINT l_program_name;
2440 END IF;
2441
2442 IF p_init_msg_list = G_TRUE THEN
2443 FND_MSG_PUB.initialize;
2444 END IF;
2445
2446 OPEN get_estimate_details_csr;
2447 FETCH get_estimate_details_csr INTO estimate_details_csr_rec;
2448 CLOSE get_estimate_details_csr;
2449
2450 OPEN get_quote_details_csr;
2451 FETCH get_quote_details_csr INTO quote_details_csr_rec;
2452 CLOSE get_quote_details_csr;
2453 --asawanka modified for bug #6790770 start
2454 IF trunc(estimate_details_csr_rec.expected_start_date) = trunc(quote_details_csr_rec.expected_start_date)
2455 --asawanka modified for bug #6790770 end
2456 AND estimate_details_csr_rec.term = quote_details_csr_rec.term
2457 AND estimate_details_csr_rec.end_of_term_option_id = quote_details_csr_rec.end_of_term_option_id
2458 -- added modifying abhsaxen for bug #5257890
2459 --converting pricing method from 'SS' to 'SY',following condition shold not be here
2460 AND ( estimate_details_csr_rec.pricing_method = quote_details_csr_rec.pricing_method
2461 OR ( estimate_details_csr_rec.pricing_method = 'SS'
2462 AND quote_details_csr_rec.pricing_method = 'SY'
2463 )
2464 )
2465 AND ( NOT( estimate_details_csr_rec.pricing_method = 'SS' AND quote_details_csr_rec.pricing_method = 'SS'))
2466 THEN
2467 IF nvl(estimate_details_csr_rec.structured_pricing,'N') = 'N' AND quote_details_csr_rec.pricing_method <> 'SY' THEN
2468 --end modifying abhsaxen for bug#5257890
2469 l_lease_qte_rec.id := p_quote_id;
2470 l_lease_qte_rec.object_version_number := quote_details_csr_rec.object_version_number;
2471 l_lease_qte_rec.rate_template_id := estimate_details_csr_rec.rate_template_id;
2472 l_lease_qte_rec.target_amount := estimate_details_csr_rec.target_amount;
2473 l_lease_qte_rec.structured_pricing := 'N';
2474 l_lease_qte_rec.target_arrears_yn := estimate_details_csr_rec.target_arrears;
2475 l_lease_qte_rec.rate_card_id := estimate_details_csr_rec.rate_card_id;
2476 IF quote_details_csr_rec.pricing_method = 'TR' THEN
2477 l_lease_qte_rec.target_rate := estimate_details_csr_rec.target_rate;
2478 l_lease_qte_rec.target_rate_type := estimate_details_csr_rec.target_rate_type;
2479 l_lease_qte_rec.target_frequency := estimate_details_csr_rec.target_frequency;
2480 l_lease_qte_rec.target_periods := estimate_details_csr_rec.target_periods;
2481 END IF;
2482 ELSE
2483 l_lease_qte_rec.id := p_quote_id;
2484 l_lease_qte_rec.object_version_number := quote_details_csr_rec.object_version_number;
2485 l_lease_qte_rec.structured_pricing := 'Y';
2486 IF quote_details_csr_rec.pricing_method = 'RC' THEN
2487 l_lease_qte_rec.target_arrears_yn := estimate_details_csr_rec.target_arrears;
2488 l_lease_qte_rec.target_frequency := estimate_details_csr_rec.target_frequency;
2489 l_lease_qte_rec.lease_rate_factor := estimate_details_csr_rec.lease_rate_factor;
2490 ELSE
2491
2492 FOR l_cf_object IN c_cf_objects (
2493 p_oty_code => 'QUICK_QUOTE'
2494 ,p_source_table => 'OKL_QUICK_QUOTES_B'
2495 ,p_source_id => p_estimate_id)
2496 LOOP
2497
2498 OPEN c_cf_headers(p_cfo_id => l_cf_object.id);
2499 FETCH c_cf_headers INTO l_cashflow_header_rec.cashflow_header_id,
2500 l_cashflow_header_rec.stream_type_id,
2501 l_cashflow_header_rec.arrears_flag,
2502 l_cft_code;
2503 CLOSE c_cf_headers;
2504
2505 l_cashflow_header_rec.parent_object_code := 'LEASE_QUOTE';
2506 l_cashflow_header_rec.parent_object_id := p_quote_id;
2507 l_cashflow_header_rec.quote_id := p_quote_id;
2508 l_cashflow_header_rec.quote_type_code := 'LQ';
2509
2510 --populate stream type id
2511 FOR t_rec IN c_strm_type (
2512 pdtId => quote_details_csr_rec.product_id,
2513 expStartDate => quote_details_csr_rec.expected_start_date,
2514 strm_purpose => 'RENT')
2515 LOOP
2516 l_cashflow_header_rec.stream_type_id := t_rec.payment_type_id;
2517 END LOOP;
2518
2519 IF (l_cft_code = 'PAYMENT_SCHEDULE') THEN
2520 l_cashflow_header_rec.type_code := 'INFLOW';
2521 ELSIF (l_cft_code = 'OUTFLOW_SCHEDULE') THEN
2522 l_cashflow_header_rec.type_code := 'OUTFLOW';
2523 END IF;
2524 j := 0;
2525 l_cashflow_level_tbl.delete;
2526 FOR l_cf_level IN c_cf_levels (p_caf_id => l_cashflow_header_rec.cashflow_header_id) LOOP
2527 l_cashflow_level_tbl(j).periods := l_cf_level.number_of_periods;
2528 IF quote_details_csr_rec.pricing_method = 'SP' THEN
2529 l_cashflow_level_tbl(j).periodic_amount := NULL;
2530 l_cashflow_level_tbl(j).stub_amount := NULL;
2531 ELSE
2532 l_cashflow_level_tbl(j).periodic_amount := l_cf_level.amount;
2533 END IF;
2534 l_cashflow_level_tbl(j).stub_days := l_cf_level.stub_days;
2535 l_cashflow_level_tbl(j).stub_amount := l_cf_level.stub_amount;
2536 l_cashflow_level_tbl(j).rate := l_cf_level.rate;
2537 lv_frq_code := l_cf_level.fqy_code;
2538 j := j + 1;
2539 END LOOP;
2540
2541 l_cashflow_header_rec.frequency_code := lv_frq_code;
2542
2543 -- Duplicate equals to 'create' mode, for sanity check purpose
2544 FOR i IN l_cashflow_level_tbl.FIRST .. l_cashflow_level_tbl.LAST LOOP
2545 IF l_cashflow_level_tbl.EXISTS(i) THEN
2546 l_cashflow_level_tbl(i).record_mode := 'create';
2547 END IF;
2548 END LOOP;
2549
2550 create_cashflow ( p_api_version => G_API_VERSION
2551 ,p_init_msg_list => G_FALSE
2552 ,p_transaction_control => G_FALSE
2553 ,p_cashflow_header_rec => l_cashflow_header_rec
2554 ,p_cashflow_level_tbl => l_cashflow_level_tbl
2555 ,x_return_status => l_return_status
2556 ,x_msg_count => x_msg_count
2557 ,x_msg_data => x_msg_data);
2558
2559 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
2560 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2561 ELSIF l_return_status = G_RET_STS_ERROR THEN
2562 RAISE OKL_API.G_EXCEPTION_ERROR;
2563 END IF;
2564
2565 i := i + 1;
2566
2567 END LOOP;
2568 END IF;
2569 END IF;
2570 OKL_LEASE_QUOTE_PVT.update_lease_qte (
2571 p_api_version => G_API_VERSION
2572 ,p_init_msg_list => G_FALSE
2573 ,p_transaction_control => G_TRUE
2574 ,p_lease_qte_rec => l_lease_qte_rec
2575 ,x_lease_qte_rec => x_lease_qte_rec
2576 ,x_return_status => x_return_status
2577 ,x_msg_count => x_msg_count
2578 ,x_msg_data => x_msg_data);
2579
2580 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
2581 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2582 ELSIF x_return_status = G_RET_STS_ERROR THEN
2583 RAISE OKL_API.G_EXCEPTION_ERROR;
2584 END IF;
2585
2586 END IF;
2587 x_return_status := G_RET_STS_SUCCESS;
2588
2589 EXCEPTION
2590
2591 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2592
2593 IF p_transaction_control = G_TRUE THEN
2594 ROLLBACK TO l_program_name;
2595 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
2596 END IF;
2597
2598 x_return_status := G_RET_STS_ERROR;
2599
2600 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2601
2602 IF p_transaction_control = G_TRUE THEN
2603 ROLLBACK TO l_program_name;
2604 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
2605 END IF;
2606
2607 x_return_status := G_RET_STS_UNEXP_ERROR;
2608
2609 WHEN OTHERS THEN
2610
2611 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
2612 p_msg_name => G_DB_ERROR,
2613 p_token1 => G_PROG_NAME_TOKEN,
2614 p_token1_value => l_api_name,
2615 p_token2 => G_SQLCODE_TOKEN,
2616 p_token2_value => sqlcode,
2617 p_token3 => G_SQLERRM_TOKEN,
2618 p_token3_value => sqlerrm);
2619
2620 IF p_transaction_control = G_TRUE THEN
2621 ROLLBACK TO l_program_name;
2622 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
2623 END IF;
2624
2625 x_return_status := G_RET_STS_UNEXP_ERROR;
2626
2627 END copy_pmts_from_est_to_quote;
2628
2629 END OKL_LEASE_QUOTE_CASHFLOW_PVT;