[Home] [Help]
PACKAGE BODY: APPS.OKL_LEASE_QUOTE_SUBPOOL_PVT
Source
1 PACKAGE BODY OKL_LEASE_QUOTE_SUBPOOL_PVT AS
2 /* $Header: OKLRQUYB.pls 120.13 2006/04/25 00:39:55 rravikir noship $ */
3
4 ----------------------------------
5 -- PROCEDURE check_initial_record
6 ----------------------------------
7 FUNCTION check_initial_record (p_object_id IN NUMBER,
8 p_source_object_code IN VARCHAR2,
9 p_subsidy_pool_id IN NUMBER,
10 x_return_status OUT NOCOPY VARCHAR2)
11 RETURN VARCHAR2 IS
12
13 l_program_name CONSTANT VARCHAR2(30) := 'check_initial_record';
14 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
15
16 l_status VARCHAR2(1);
17 lv_obj_code VARCHAR2(30);
18
19 CURSOR c_check_record_exists(p_obj_code IN VARCHAR2) IS
20 SELECT 'Y'
21 FROM okl_quote_subpool_usage
22 WHERE source_object_id = p_object_id
23 AND source_type_code = p_obj_code
24 AND subsidy_pool_id = p_subsidy_pool_id;
25 BEGIN
26 IF (p_source_object_code = 'LEASEOPP') THEN
27 lv_obj_code := 'SALES_QUOTE';
28 ELSE
29 lv_obj_code := 'LEASE_APPLICATION';
30 END IF;
31 OPEN c_check_record_exists(p_obj_code => lv_obj_code);
32 FETCH c_check_record_exists into l_status;
33 CLOSE c_check_record_exists;
34
35 IF (l_status = 'Y') THEN
36 RETURN 'Y';
37 ELSE
38 RETURN 'N';
39 END IF;
40
41 EXCEPTION
42
43 WHEN OKL_API.G_EXCEPTION_ERROR THEN
44
45 x_return_status := G_RET_STS_ERROR;
46
47 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
48
49 x_return_status := G_RET_STS_UNEXP_ERROR;
50
51 WHEN OTHERS THEN
52
53 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
54 p_msg_name => G_DB_ERROR,
55 p_token1 => G_PROG_NAME_TOKEN,
56 p_token1_value => l_api_name,
57 p_token2 => G_SQLCODE_TOKEN,
58 p_token2_value => sqlcode,
59 p_token3 => G_SQLERRM_TOKEN,
60 p_token3_value => sqlerrm);
61
62 x_return_status := G_RET_STS_UNEXP_ERROR;
63
64 END check_initial_record;
65
66 ----------------------------------
67 -- PROCEDURE check_leaseopp_quote
68 ----------------------------------
69 FUNCTION check_leaseopp_quote (p_lease_app_id IN NUMBER,
70 x_return_status OUT NOCOPY VARCHAR2)
71 RETURN VARCHAR2 IS
72
73 l_program_name CONSTANT VARCHAR2(30) := 'check_leaseopp_quote';
74 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
75
76 l_count Number(1);
77
78 BEGIN
79 --Begin -Updated the Select stmt with Count for bug#4723160 - varangan-8-11-2005
80 Select Count(1)
81 Into L_count
82 From OKL_LEASE_APPLICATIONS_B
83 WHERE LEASE_OPPORTUNITY_ID IS NOT NULL
84 AND ID = p_lease_app_id;
85
86 IF (l_count = 0) THEN
87 RETURN 'N';
88 ELSE
89 RETURN 'Y';
90 END IF;
91 --End - Updated the Select stmt with Count for bug#4723160 - varangan-8-11-2005
92
93 EXCEPTION
94
95 WHEN OKL_API.G_EXCEPTION_ERROR THEN
96
97 x_return_status := G_RET_STS_ERROR;
98
99 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
100
101 x_return_status := G_RET_STS_UNEXP_ERROR;
102
103 WHEN OTHERS THEN
104
105 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
106 p_msg_name => G_DB_ERROR,
107 p_token1 => G_PROG_NAME_TOKEN,
108 p_token1_value => l_api_name,
109 p_token2 => G_SQLCODE_TOKEN,
110 p_token2_value => sqlcode,
111 p_token3 => G_SQLERRM_TOKEN,
112 p_token3_value => sqlerrm);
113
114 x_return_status := G_RET_STS_UNEXP_ERROR;
115
116 END check_leaseopp_quote;
117 ----------------------------------
118 -- PROCEDURE check_fresh_lease_app
119 ----------------------------------
120 FUNCTION check_fresh_lease_app (p_lease_app_id IN NUMBER,
121 x_return_status OUT NOCOPY VARCHAR2)
122 RETURN VARCHAR2 IS
123
124 l_program_name CONSTANT VARCHAR2(30) := 'check_fresh_lease_app';
125 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
126
127 l_parent_id Number := NULL;
128 l_is_parent VARCHAR2(3) := 'N';
129 CURSOR get_parent_laps IS
130 Select PARENT_LEASEAPP_ID
131 From OKL_LEASE_APPLICATIONS_B
132 WHERE ID = p_lease_app_id;
133
134 CURSOR get_child_laps IS
135 SELECT 'Y'
136 FROM OKL_LEASE_APPLICATIONS_B
137 WHERE parent_leaseapp_id = p_lease_app_id;
138
139 BEGIN
140 --is the lap having parent ?
141 OPEN get_parent_laps;
142 FETCH get_parent_laps INTO l_parent_id;
143 CLOSE get_parent_laps;
144 --is the lap parent of other lap ?
145 OPEN get_child_laps;
146 FETCH get_child_laps INTO l_is_parent;
147 CLOSE get_child_laps;
148
149 IF (l_parent_id IS NOT NULL OR l_is_parent = 'Y') THEN
150 RETURN 'N';
151 ELSE
152 RETURN 'Y';
153 END IF;
154
155 EXCEPTION
156
157 WHEN OKL_API.G_EXCEPTION_ERROR THEN
158
159 x_return_status := G_RET_STS_ERROR;
160
161 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
162
163 x_return_status := G_RET_STS_UNEXP_ERROR;
164
165 WHEN OTHERS THEN
166
167 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
168 p_msg_name => G_DB_ERROR,
169 p_token1 => G_PROG_NAME_TOKEN,
170 p_token1_value => l_api_name,
171 p_token2 => G_SQLCODE_TOKEN,
172 p_token2_value => sqlcode,
173 p_token3 => G_SQLERRM_TOKEN,
174 p_token3_value => sqlerrm);
175
176 x_return_status := G_RET_STS_UNEXP_ERROR;
177
178 END check_fresh_lease_app;
179
180 ----------------------------------------
181 -- PROCEDURE create_quote_subpool_usage
182 ----------------------------------------
183 PROCEDURE create_quote_subpool_usage(p_api_version IN NUMBER,
184 p_init_msg_list IN VARCHAR2,
185 x_return_status OUT NOCOPY VARCHAR2,
186 x_msg_count OUT NOCOPY NUMBER,
187 x_msg_data OUT NOCOPY VARCHAR2,
188 p_sixv_tbl IN subsidy_pool_tbl_type) IS
189
190 l_program_name CONSTANT VARCHAR2(30) := 'create_quote_subpool_usage';
191 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
192
193 l_quote_sp_usage_tbl quote_sp_usage_tbl_type;
194 lx_quote_sp_usage_tbl quote_sp_usage_tbl_type;
195 l_subpool_tbl subsidy_pool_tbl_type;
196
197 BEGIN
198 l_subpool_tbl := p_sixv_tbl;
199
200 FOR i IN l_subpool_tbl.FIRST .. l_subpool_tbl.LAST LOOP
201 IF l_subpool_tbl.EXISTS(i) THEN
202 l_quote_sp_usage_tbl(i).subpool_trx_id := l_subpool_tbl(i).id;
203 l_quote_sp_usage_tbl(i).source_type_code := l_subpool_tbl(i).source_type_code;
204 l_quote_sp_usage_tbl(i).source_object_id := l_subpool_tbl(i).source_object_id;
205 l_quote_sp_usage_tbl(i).asset_number := l_subpool_tbl(i).dnz_asset_number;
206 l_quote_sp_usage_tbl(i).asset_start_date := l_subpool_tbl(i).source_trx_date;
207 l_quote_sp_usage_tbl(i).subsidy_pool_id := l_subpool_tbl(i).subsidy_pool_id;
208 l_quote_sp_usage_tbl(i).subsidy_pool_amount := l_subpool_tbl(i).subsidy_pool_amount;
209 l_quote_sp_usage_tbl(i).subsidy_pool_currency_code := l_subpool_tbl(i).subsidy_pool_currency_code;
210 l_quote_sp_usage_tbl(i).subsidy_id := l_subpool_tbl(i).subsidy_id;
211 l_quote_sp_usage_tbl(i).subsidy_amount := l_subpool_tbl(i).trx_amount;
212 l_quote_sp_usage_tbl(i).subsidy_currency_code := l_subpool_tbl(i).trx_currency_code;
213 l_quote_sp_usage_tbl(i).vendor_id := l_subpool_tbl(i).vendor_id;
214 l_quote_sp_usage_tbl(i).conversion_rate := l_subpool_tbl(i).conversion_rate;
215 END IF;
216 END LOOP;
217
218 IF (l_quote_sp_usage_tbl.COUNT > 0) THEN
219 okl_qul_pvt.insert_row ( p_api_version => G_API_VERSION
220 ,p_init_msg_list => G_FALSE
221 ,x_return_status => x_return_status
222 ,x_msg_count => x_msg_count
223 ,x_msg_data => x_msg_data
224 ,p_qulv_tbl => l_quote_sp_usage_tbl
225 ,x_qulv_tbl => lx_quote_sp_usage_tbl);
226 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
227 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
228 ELSIF x_return_status = G_RET_STS_ERROR THEN
229 RAISE OKL_API.G_EXCEPTION_ERROR;
230 END IF;
231 END IF;
232
233 x_return_status := G_RET_STS_SUCCESS;
234
235 EXCEPTION
236
237 WHEN OKL_API.G_EXCEPTION_ERROR THEN
238
239 x_return_status := G_RET_STS_ERROR;
240
241 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
242
243 x_return_status := G_RET_STS_UNEXP_ERROR;
244
245 WHEN OTHERS THEN
246
247 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
248 p_msg_name => G_DB_ERROR,
249 p_token1 => G_PROG_NAME_TOKEN,
250 p_token1_value => l_api_name,
251 p_token2 => G_SQLCODE_TOKEN,
252 p_token2_value => sqlcode,
253 p_token3 => G_SQLERRM_TOKEN,
254 p_token3_value => sqlerrm);
255
256 x_return_status := G_RET_STS_UNEXP_ERROR;
257
258 END create_quote_subpool_usage;
259
260 ----------------------------------------
261 -- PROCEDURE delete_quote_subpool_usage
262 ----------------------------------------
263 PROCEDURE delete_quote_subpool_usage(p_api_version IN NUMBER,
264 p_init_msg_list IN VARCHAR2,
265 x_return_status OUT NOCOPY VARCHAR2,
266 x_msg_count OUT NOCOPY NUMBER,
267 x_msg_data OUT NOCOPY VARCHAR2,
268 p_subsidy_pool_id IN NUMBER,
269 p_source_object_id IN NUMBER) IS
270
271 l_program_name CONSTANT VARCHAR2(30) := 'delete_quote_subpool_usage';
272 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
273
274 l_quote_sp_usage_tbl quote_sp_usage_tbl_type;
275 i BINARY_INTEGER := 0;
276
277 CURSOR c_get_quote_subpool_usage IS
278 SELECT ID
279 FROM OKL_QUOTE_SUBPOOL_USAGE
280 WHERE SUBSIDY_POOL_ID = p_subsidy_pool_id
281 AND SOURCE_OBJECT_ID = p_source_object_id;
282 BEGIN
283 FOR l_get_quote_subpool_usage IN c_get_quote_subpool_usage LOOP
284 l_quote_sp_usage_tbl(i).id := l_get_quote_subpool_usage.id;
285 i := i + 1;
286 END LOOP;
287
288 IF (l_quote_sp_usage_tbl.COUNT > 0) THEN
289 okl_qul_pvt.delete_row ( p_api_version => G_API_VERSION
290 ,p_init_msg_list => G_FALSE
291 ,x_return_status => x_return_status
292 ,x_msg_count => x_msg_count
293 ,x_msg_data => x_msg_data
294 ,p_qulv_tbl => l_quote_sp_usage_tbl);
295 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
296 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
297 ELSIF x_return_status = G_RET_STS_ERROR THEN
298 RAISE OKL_API.G_EXCEPTION_ERROR;
299 END IF;
300 END IF;
301
302 x_return_status := G_RET_STS_SUCCESS;
303
304 EXCEPTION
305
306 WHEN OKL_API.G_EXCEPTION_ERROR THEN
307
308 x_return_status := G_RET_STS_ERROR;
309
310 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
311
312 x_return_status := G_RET_STS_UNEXP_ERROR;
313
314 WHEN OTHERS THEN
315
316 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
317 p_msg_name => G_DB_ERROR,
318 p_token1 => G_PROG_NAME_TOKEN,
319 p_token1_value => l_api_name,
320 p_token2 => G_SQLCODE_TOKEN,
321 p_token2_value => sqlcode,
322 p_token3 => G_SQLERRM_TOKEN,
323 p_token3_value => sqlerrm);
324
325 x_return_status := G_RET_STS_UNEXP_ERROR;
326
327 END delete_quote_subpool_usage;
328
329 ----------------------------------------
330 -- PROCEDURE fetch_quote_subpool_usage
331 ----------------------------------------
332 PROCEDURE fetch_quote_subpool_usage(p_subsidy_pool_id IN NUMBER,
333 p_quote_id IN NUMBER,
334 x_subpool_tbl OUT NOCOPY subsidy_pool_tbl_type,
335 x_return_status OUT NOCOPY VARCHAR2) IS
336
337 l_program_name CONSTANT VARCHAR2(30) := 'fetch_quote_subpool_usage';
338 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
339
340 l_subpool_tbl subsidy_pool_tbl_type;
341 i BINARY_INTEGER := 0;
342
343 CURSOR c_get_quote_subpool_usage IS
344 SELECT SOURCE_TYPE_CODE,
345 SOURCE_OBJECT_ID,
346 ASSET_NUMBER,
347 ASSET_START_DATE,
348 SUBSIDY_POOL_ID,
349 SUBSIDY_POOL_AMOUNT,
350 SUBSIDY_POOL_CURRENCY_CODE,
351 SUBSIDY_ID,
352 SUBSIDY_AMOUNT,
353 SUBSIDY_CURRENCY_CODE,
354 VENDOR_ID,
355 CONVERSION_RATE
356 FROM OKL_QUOTE_SUBPOOL_USAGE
357 WHERE SUBSIDY_POOL_ID = p_subsidy_pool_id
358 AND SOURCE_OBJECT_ID = p_quote_id;
359
360 BEGIN
361 FOR l_get_quote_subpool_usage IN c_get_quote_subpool_usage LOOP
362 l_subpool_tbl(i).source_type_code := l_get_quote_subpool_usage.source_type_code;
363 l_subpool_tbl(i).source_object_id := l_get_quote_subpool_usage.source_object_id;
364 l_subpool_tbl(i).dnz_asset_number := l_get_quote_subpool_usage.asset_number;
365 l_subpool_tbl(i).source_trx_date := l_get_quote_subpool_usage.asset_start_date;
366 l_subpool_tbl(i).subsidy_pool_id := l_get_quote_subpool_usage.subsidy_pool_id ;
367 l_subpool_tbl(i).subsidy_pool_amount := l_get_quote_subpool_usage.subsidy_pool_amount;
368 l_subpool_tbl(i).subsidy_pool_currency_code := l_get_quote_subpool_usage.subsidy_pool_currency_code;
369 l_subpool_tbl(i).subsidy_id := l_get_quote_subpool_usage.subsidy_id;
370 l_subpool_tbl(i).trx_amount := l_get_quote_subpool_usage.subsidy_amount;
371 l_subpool_tbl(i).trx_currency_code := l_get_quote_subpool_usage.subsidy_currency_code;
372 l_subpool_tbl(i).vendor_id := l_get_quote_subpool_usage.vendor_id;
373 l_subpool_tbl(i).conversion_rate := l_get_quote_subpool_usage.conversion_rate;
374
375 i := i + 1;
376 END LOOP;
377
378 x_subpool_tbl := l_subpool_tbl;
379
380 x_return_status := G_RET_STS_SUCCESS;
381
382 EXCEPTION
383
384 WHEN OKL_API.G_EXCEPTION_ERROR THEN
385
386 x_return_status := G_RET_STS_ERROR;
387
388 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
389
390 x_return_status := G_RET_STS_UNEXP_ERROR;
391
392 WHEN OTHERS THEN
393
394 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
395 p_msg_name => G_DB_ERROR,
396 p_token1 => G_PROG_NAME_TOKEN,
397 p_token1_value => l_api_name,
398 p_token2 => G_SQLCODE_TOKEN,
399 p_token2_value => sqlcode,
400 p_token3 => G_SQLERRM_TOKEN,
401 p_token3_value => sqlerrm);
402
403 x_return_status := G_RET_STS_UNEXP_ERROR;
404
405 END fetch_quote_subpool_usage;
406 ----------------------------------------
407 -- PROCEDURE get_linked_lop_maxsp_usage
408 ----------------------------------------
409 PROCEDURE get_linked_lop_maxsp_usage( p_subsidy_pool_id IN NUMBER,
410 p_lop_id IN NUMBER,
411 p_quote_id IN NUMBER,
412 x_max_usage_qte_id OUT NOCOPY NUMBER,
413 x_max_usage_amt OUT NOCOPY NUMBER,
414 x_return_status OUT NOCOPY VARCHAR2
415 )IS
416
417 l_program_name CONSTANT VARCHAR2(30) := 'get_linked_lop_maxsp_usage';
418 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
419 l_first BOOLEAN;
420 l_max_amt NUMBER;
421
422 CURSOR c_get_quote_subpool_usage IS
423 SELECT SUB.SUBSIDY_POOL_ID SUBSIDY_POOL_ID,
424 SUM(DECODE(ADJ.VALUE, NULL, ADJ.DEFAULT_SUBSIDY_AMOUNT, ADJ.VALUE)) AMOUNT,
425 QUOTE.ID QUOTE_ID
426 FROM OKL_COST_ADJUSTMENTS_B ADJ,
427 OKL_SUBSIDIES_B SUB,
428 OKL_SUBSIDY_POOLS_B SUB_POOL,
429 OKL_ASSETS_B ASSET,
430 OKL_LEASE_QUOTES_B QUOTE
431 WHERE ADJ.ADJUSTMENT_SOURCE_TYPE = 'SUBSIDY'
432 AND ADJ.PARENT_OBJECT_CODE = 'ASSET'
433 AND ADJ.ADJUSTMENT_SOURCE_ID = SUB.ID
434 AND SUB.SUBSIDY_POOL_ID = SUB_POOL.ID
435 AND ADJ.PARENT_OBJECT_ID = ASSET.ID
436 AND ASSET.PARENT_OBJECT_CODE = 'LEASEQUOTE'
437 AND ASSET.PARENT_OBJECT_ID = QUOTE.ID
438 AND QUOTE.STATUS = 'PR-APPROVED'
439 AND QUOTE.PARENT_OBJECT_ID = p_lop_id
440 AND QUOTE.ID <> p_quote_id
441 AND SUB_POOL.ID = p_subsidy_pool_id
442 group by SUB.SUBSIDY_POOL_ID ,QUOTE.ID;
443 BEGIN
444 x_max_usage_qte_id := null;
445 x_max_usage_amt := null;
446 l_first := true;
447 FOR l_get_quote_subpool_usage IN c_get_quote_subpool_usage LOOP
448 IF l_first THEN
449 x_max_usage_qte_id := l_get_quote_subpool_usage.quote_id;
450 x_max_usage_amt := l_get_quote_subpool_usage.amount;
451 l_max_amt := l_get_quote_subpool_usage.amount;
452 l_first := false;
453 ELSIF l_max_amt < l_get_quote_subpool_usage.amount THEN
454 x_max_usage_qte_id := l_get_quote_subpool_usage.quote_id;
455 x_max_usage_amt := l_get_quote_subpool_usage.amount;
456 l_max_amt := l_get_quote_subpool_usage.amount;
457 END IF;
458 END LOOP;
459
460 x_return_status := G_RET_STS_SUCCESS;
461
462 EXCEPTION
463
464 WHEN OKL_API.G_EXCEPTION_ERROR THEN
465
466 x_return_status := G_RET_STS_ERROR;
467
468 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
469
470 x_return_status := G_RET_STS_UNEXP_ERROR;
471
472 WHEN OTHERS THEN
473
474 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
475 p_msg_name => G_DB_ERROR,
476 p_token1 => G_PROG_NAME_TOKEN,
477 p_token1_value => l_api_name,
478 p_token2 => G_SQLCODE_TOKEN,
479 p_token2_value => sqlcode,
480 p_token3 => G_SQLERRM_TOKEN,
481 p_token3_value => sqlerrm);
482
483 x_return_status := G_RET_STS_UNEXP_ERROR;
484
485 END get_linked_lop_maxsp_usage;
486
487 ----------------------------------------
488 -- PROCEDURE get_linked_lop_maxsp_usage
489 ----------------------------------------
490 PROCEDURE get_linked_lap_maxsp_usage( p_subsidy_pool_id IN NUMBER,
491 p_lap_id IN NUMBER,
492 p_current_qte_id IN NUMBER,
493 p_transaction IN VARCHAR2,
494 x_max_usage_qte_id OUT NOCOPY NUMBER,
495 x_max_usage_amt OUT NOCOPY NUMBER,
496 x_return_status OUT NOCOPY VARCHAR2
497 )IS
498
499 l_program_name CONSTANT VARCHAR2(30) := 'get_linked_lap_maxsp_usage';
500 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
501 l_first BOOLEAN;
502 l_max_amt NUMBER;
503
504 CURSOR c_get_linked_laps
505 IS
506 SELECT ID
507 , REFERENCE_NUMBER
508 , APPLICATION_STATUS
509 FROM OKL_LEASE_APPLICATIONS_B
510 WHERE application_Status NOT IN ('CANCELED','WITHDRAWN')
511 CONNECT BY PARENT_LEASEAPP_ID = PRIOR ID
512 START WITH ID = p_lap_id
513 UNION
514 SELECT ID
515 , REFERENCE_NUMBER
516 , APPLICATION_STATUS
517 FROM OKL_LEASE_APPLICATIONS_B
518 WHERE ID <> p_lap_id
519 AND application_Status NOT IN ('CANCELED','WITHDRAWN')
520 CONNECT BY PRIOR PARENT_LEASEAPP_ID = ID
521 START WITH ID = p_lap_id;
522
523 CURSOR c_get_quotesandoffers_in_lap(p_lease_app_id IN NUMBER) IS
524 SELECT QUOTE.ID QUOTE_ID
525 FROM OKL_LEASE_QUOTES_V QUOTE
526 WHERE QUOTE.STATUS IN ( 'PR-APPROVED','CT-ACCEPTED','CR-RECOMMENDATION')
527 AND QUOTE.ID <> p_current_qte_id
528 AND QUOTE.PARENT_OBJECT_CODE = 'LEASEAPP'
529 AND QUOTE.PARENT_OBJECT_ID = p_lease_app_id;
530
531 CURSOR c_get_quote_subpool_usage(p_qte_id IN NUMBER) IS
532 SELECT SUB.SUBSIDY_POOL_ID SUBSIDY_POOL_ID,
533 SUM(DECODE(ADJ.VALUE, NULL, ADJ.DEFAULT_SUBSIDY_AMOUNT, ADJ.VALUE)) AMOUNT,
534 QUOTE.ID QUOTE_ID
535 FROM OKL_COST_ADJUSTMENTS_B ADJ,
536 OKL_SUBSIDIES_B SUB,
537 OKL_SUBSIDY_POOLS_B SUB_POOL,
538 OKL_ASSETS_B ASSET,
539 OKL_LEASE_QUOTES_V QUOTE,
540 OKL_LEASE_APPLICATIONS_B LAP
541 WHERE ADJ.ADJUSTMENT_SOURCE_TYPE = 'SUBSIDY'
542 AND ADJ.PARENT_OBJECT_CODE = 'ASSET'
543 AND ADJ.ADJUSTMENT_SOURCE_ID = SUB.ID
544 AND SUB.SUBSIDY_POOL_ID = SUB_POOL.ID
545 AND ADJ.PARENT_OBJECT_ID = ASSET.ID
546 AND ASSET.PARENT_OBJECT_CODE = 'LEASEQUOTE'
547 AND ASSET.PARENT_OBJECT_ID = QUOTE.ID
548 AND QUOTE.STATUS IN ( 'PR-APPROVED','CT-ACCEPTED','CR-RECOMMENDATION')
549 AND QUOTE.ID <> p_current_qte_id
550 AND QUOTE.ID = p_qte_id
551 AND QUOTE.PARENT_OBJECT_ID = LAP.ID
552 AND QUOTE.PARENT_OBJECT_CODE = 'LEASEAPP'
553 AND SUB_POOL.ID = p_subsidy_pool_id
554 group by SUB.SUBSIDY_POOL_ID ,QUOTE.ID;
555
556 CURSOR c_wd_get_quotesoffers_in_lap(p_lease_app_id IN NUMBER) IS
557 SELECT QUOTE.ID QUOTE_ID
558 FROM OKL_LEASE_QUOTES_V QUOTE
559 WHERE QUOTE.STATUS IN ( 'PR-APPROVED','CT-ACCEPTED','CR-RECOMMENDATION')
560 AND QUOTE.PARENT_OBJECT_CODE = 'LEASEAPP'
561 AND QUOTE.PARENT_OBJECT_ID = p_lease_app_id;
562
563 CURSOR c_wd_get_quote_subpool_usage(p_qte_id IN NUMBER) IS
564 SELECT SUB.SUBSIDY_POOL_ID SUBSIDY_POOL_ID,
565 SUM(DECODE(ADJ.VALUE, NULL, ADJ.DEFAULT_SUBSIDY_AMOUNT, ADJ.VALUE)) AMOUNT,
566 QUOTE.ID QUOTE_ID
567 FROM OKL_COST_ADJUSTMENTS_B ADJ,
568 OKL_SUBSIDIES_B SUB,
569 OKL_SUBSIDY_POOLS_B SUB_POOL,
570 OKL_ASSETS_B ASSET,
571 OKL_LEASE_QUOTES_V QUOTE,
572 OKL_LEASE_APPLICATIONS_B LAP
573 WHERE ADJ.ADJUSTMENT_SOURCE_TYPE = 'SUBSIDY'
574 AND ADJ.PARENT_OBJECT_CODE = 'ASSET'
575 AND ADJ.ADJUSTMENT_SOURCE_ID = SUB.ID
576 AND SUB.SUBSIDY_POOL_ID = SUB_POOL.ID
577 AND ADJ.PARENT_OBJECT_ID = ASSET.ID
578 AND ASSET.PARENT_OBJECT_CODE = 'LEASEQUOTE'
579 AND ASSET.PARENT_OBJECT_ID = QUOTE.ID
580 AND QUOTE.STATUS IN ( 'PR-APPROVED','CT-ACCEPTED','CR-RECOMMENDATION')
581 AND QUOTE.ID = p_qte_id
582 AND QUOTE.PARENT_OBJECT_ID = LAP.ID
583 AND QUOTE.PARENT_OBJECT_CODE = 'LEASEAPP'
584 AND SUB_POOL.ID = p_subsidy_pool_id
585 group by SUB.SUBSIDY_POOL_ID ,QUOTE.ID;
586
587 BEGIN
588 x_max_usage_qte_id := null;
589 x_max_usage_amt := null;
590 l_first := true;
591 --for all linked laps
592
593 IF (p_transaction = 'WITHDRAW_LEASE_APP') THEN
594 FOR l_linked_lap IN c_get_linked_laps LOOP
595
596 IF p_current_qte_id IS NOT NULL OR
597 (p_current_qte_id IS NULL AND l_linked_lap.ID <> p_lap_id ) THEN
598 -- dbms_output.put_line(' Into If Loop .. ');
599 -- for all quotes and offers in lap
600 FOR l_lap_qtes_offers IN c_wd_get_quotesoffers_in_lap(l_linked_lap.ID) LOOP
601 FOR l_get_quote_subpool_usage IN c_wd_get_quote_subpool_usage(l_lap_qtes_offers.quote_id) LOOP
602
603 IF l_first THEN
604 x_max_usage_qte_id := l_get_quote_subpool_usage.quote_id;
605 x_max_usage_amt := l_get_quote_subpool_usage.amount;
606 l_max_amt := l_get_quote_subpool_usage.amount;
607 l_first := false;
608 ELSIF l_max_amt < l_get_quote_subpool_usage.amount THEN
609 x_max_usage_qte_id := l_get_quote_subpool_usage.quote_id;
610 x_max_usage_amt := l_get_quote_subpool_usage.amount;
611 l_max_amt := l_get_quote_subpool_usage.amount;
612 END IF;
613 END LOOP;
614 END LOOP;
615 END IF;
616 END LOOP;
617 ELSE
618 FOR l_linked_lap IN c_get_linked_laps LOOP
619 IF p_current_qte_id IS NOT NULL OR
620 (p_current_qte_id IS NULL AND l_linked_lap.ID <> p_lap_id ) THEN
621 -- dbms_output.put_line(' Into If Loop .. ');
622 -- for all quotes and offers in lap
623 FOR l_lap_qtes_offers IN c_get_quotesandoffers_in_lap(l_linked_lap.ID) LOOP
624 FOR l_get_quote_subpool_usage IN c_get_quote_subpool_usage(l_lap_qtes_offers.quote_id) LOOP
625
626 IF l_first THEN
627 x_max_usage_qte_id := l_get_quote_subpool_usage.quote_id;
628 x_max_usage_amt := l_get_quote_subpool_usage.amount;
629 l_max_amt := l_get_quote_subpool_usage.amount;
630 l_first := false;
631 ELSIF l_max_amt < l_get_quote_subpool_usage.amount THEN
632 x_max_usage_qte_id := l_get_quote_subpool_usage.quote_id;
633 x_max_usage_amt := l_get_quote_subpool_usage.amount;
634 l_max_amt := l_get_quote_subpool_usage.amount;
635 END IF;
636 END LOOP;
637 END LOOP;
638 END IF;
639 END LOOP;
640 END IF;
641 -- dbms_output.put_line('Max Usage Quote '||x_max_usage_qte_id);
642 -- dbms_output.put_line('Max Usage Amount '||x_max_usage_amt);
643 x_return_status := G_RET_STS_SUCCESS;
644
645 EXCEPTION
646
647 WHEN OKL_API.G_EXCEPTION_ERROR THEN
648
649 x_return_status := G_RET_STS_ERROR;
650
651 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
652
653 x_return_status := G_RET_STS_UNEXP_ERROR;
654
655 WHEN OTHERS THEN
656
657 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
658 p_msg_name => G_DB_ERROR,
659 p_token1 => G_PROG_NAME_TOKEN,
660 p_token1_value => l_api_name,
661 p_token2 => G_SQLCODE_TOKEN,
662 p_token2_value => sqlcode,
663 p_token3 => G_SQLERRM_TOKEN,
664 p_token3_value => sqlerrm);
665
666 x_return_status := G_RET_STS_UNEXP_ERROR;
667
668 END get_linked_lap_maxsp_usage;
669 ----------------------------------------
670 -- PROCEDURE create_subpool_trx_and_usage
671 ----------------------------------------
672 PROCEDURE create_subpool_trx_and_usage(p_api_version IN NUMBER,
673 p_init_msg_list IN VARCHAR2,
674 x_return_status OUT NOCOPY VARCHAR2,
675 x_msg_count OUT NOCOPY NUMBER,
676 x_msg_data OUT NOCOPY VARCHAR2,
677 p_source_object_code IN VARCHAR2,
678 p_quote_id IN NUMBER,
679 p_subsidy_pool_id IN NUMBER,
680 p_transaction_reason IN VARCHAR2) IS
681
682 l_program_name CONSTANT VARCHAR2(30) := 'create_subpool_trx_and_usage';
683 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
684
685 l_sub_pool_tbl subsidy_pool_tbl_type;
686 lx_subpool_tbl subsidy_pool_tbl_type;
687
688 i BINARY_INTEGER := 0;
689
690 CURSOR c_get_quote_subsidy_info(p_subsidy_pool_id IN NUMBER) IS
691 SELECT ADJ.ADJUSTMENT_SOURCE_ID SUBSIDY_ID,
692 DECODE(ADJ.VALUE, NULL, ADJ.DEFAULT_SUBSIDY_AMOUNT, ADJ.VALUE) VALUE,
693 ADJ.SUPPLIER_ID,
694 ASSET.ASSET_NUMBER,
695 QUOTE.EXPECTED_START_DATE,
696 SUB.CURRENCY_CODE SUB_CURRENCY_CODE,
697 SUB_POOL.CURRENCY_CODE SUBPOOL_CURRENCY_CODE,
698 -- SUB_POOL.CURRENCY_CONVERSION_TYPE,
699 SUB_POOL.TOTAL_SUBSIDY_AMOUNT
700 FROM OKL_COST_ADJUSTMENTS_B ADJ,
701 OKL_SUBSIDIES_B SUB,
702 OKL_SUBSIDY_POOLS_B SUB_POOL,
703 OKL_ASSETS_B ASSET,
704 OKL_LEASE_QUOTES_B QUOTE
705 WHERE ADJ.ADJUSTMENT_SOURCE_TYPE = 'SUBSIDY'
706 AND ADJ.PARENT_OBJECT_CODE = 'ASSET'
707 AND ADJ.ADJUSTMENT_SOURCE_ID = SUB.ID
708 AND SUB.SUBSIDY_POOL_ID = SUB_POOL.ID
709 AND ADJ.PARENT_OBJECT_ID = ASSET.ID
710 AND ASSET.PARENT_OBJECT_CODE = 'LEASEQUOTE'
711 AND ASSET.PARENT_OBJECT_ID = QUOTE.ID
712 AND QUOTE.ID = p_quote_id
713 AND SUB_POOL.ID = p_subsidy_pool_id;
714
715 BEGIN
716
717 FOR l_get_quote_subsidy_info IN c_get_quote_subsidy_info(p_subsidy_pool_id => p_subsidy_pool_id) LOOP
718 l_sub_pool_tbl(i).subsidy_id := l_get_quote_subsidy_info.subsidy_id;
719 l_sub_pool_tbl(i).trx_amount := l_get_quote_subsidy_info.value;
720 l_sub_pool_tbl(i).vendor_id := l_get_quote_subsidy_info.supplier_id;
721 l_sub_pool_tbl(i).dnz_asset_number := l_get_quote_subsidy_info.asset_number;
722 l_sub_pool_tbl(i).source_trx_date := l_get_quote_subsidy_info.expected_start_date;
723 l_sub_pool_tbl(i).trx_currency_code := l_get_quote_subsidy_info.sub_currency_code;
724 l_sub_pool_tbl(i).subsidy_pool_currency_code := l_get_quote_subsidy_info.subpool_currency_code;
725 l_sub_pool_tbl(i).subsidy_pool_amount := l_get_quote_subsidy_info.total_subsidy_amount;
726
727 l_sub_pool_tbl(i).trx_type_code := 'REDUCTION';
728 IF (p_source_object_code = 'LEASEOPP') THEN
729 l_sub_pool_tbl(i).source_type_code := 'SALES_QUOTE';
730 ELSE
731 l_sub_pool_tbl(i).source_type_code := 'LEASE_APPLICATION';
732 END IF;
733 l_sub_pool_tbl(i).source_object_id := p_quote_id;
734 l_sub_pool_tbl(i).subsidy_pool_id := p_subsidy_pool_id;
735 l_sub_pool_tbl(i).trx_reason_code := p_transaction_reason;
736
737 i := i + 1;
738 END LOOP;
739
740 IF (l_sub_pool_tbl.COUNT > 0) THEN
741 -- Create the transaction record in the Subsidy pool
742 okl_subsidy_pool_trx_pvt.create_pool_transaction
743 (p_api_version => p_api_version,
744 p_init_msg_list => p_init_msg_list,
745 x_return_status => x_return_status,
746 x_msg_count => x_msg_count,
747 x_msg_data => x_msg_data,
748 p_sixv_tbl => l_sub_pool_tbl,
749 x_sixv_tbl => lx_subpool_tbl);
750 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
751 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
752 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
753 RAISE OKL_API.G_EXCEPTION_ERROR;
754 END IF;
755
756 -- Create the transaction copy record in quote subsidy pool usage table
757 create_quote_subpool_usage(p_api_version => p_api_version,
758 p_init_msg_list => p_init_msg_list,
759 x_return_status => x_return_status,
760 x_msg_count => x_msg_count,
761 x_msg_data => x_msg_data,
762 p_sixv_tbl => lx_subpool_tbl);
763
764 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
765 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
766 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
767 RAISE OKL_API.G_EXCEPTION_ERROR;
768 END IF;
769 END IF;
770
771 x_return_status := G_RET_STS_SUCCESS;
772
773 EXCEPTION
774
775 WHEN OKL_API.G_EXCEPTION_ERROR THEN
776
777 x_return_status := G_RET_STS_ERROR;
778
779 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
780
781 x_return_status := G_RET_STS_UNEXP_ERROR;
782
783 WHEN OTHERS THEN
784
785 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
786 p_msg_name => G_DB_ERROR,
787 p_token1 => G_PROG_NAME_TOKEN,
788 p_token1_value => l_api_name,
789 p_token2 => G_SQLCODE_TOKEN,
790 p_token2_value => sqlcode,
791 p_token3 => G_SQLERRM_TOKEN,
792 p_token3_value => sqlerrm);
793
794 x_return_status := G_RET_STS_UNEXP_ERROR;
795
796 END create_subpool_trx_and_usage;
797
798 ----------------------------------------
799 -- PROCEDURE handle_quote_pools
800 ----------------------------------------
801 PROCEDURE handle_quote_pools (p_api_version IN NUMBER,
802 p_init_msg_list IN VARCHAR2,
803 p_quote_id IN NUMBER,
804 p_transaction_reason IN VARCHAR2,
805 p_parent_object_id IN NUMBER,
806 p_parent_object_code IN VARCHAR2,
807 x_return_status OUT NOCOPY VARCHAR2,
808 x_msg_count OUT NOCOPY NUMBER,
809 x_msg_data OUT NOCOPY VARCHAR2) IS
810
811 l_program_name CONSTANT VARCHAR2(30) := 'handle_quote_pools';
812 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
813
814 CURSOR c_get_leaseopp_max_pool_values(p_subsidy_pool_id IN NUMBER) IS
815 SELECT QUOTE_ID,
816 SUBSIDY_POOL_ID,
817 MAX(AMOUNT) AMOUNT
818 FROM
819 (SELECT SUB.SUBSIDY_POOL_ID SUBSIDY_POOL_ID,
820 SUM(DECODE(ADJ.VALUE, NULL, ADJ.DEFAULT_SUBSIDY_AMOUNT, ADJ.VALUE)) AMOUNT,
821 QUOTE.ID QUOTE_ID
822 FROM OKL_COST_ADJUSTMENTS_B ADJ,
823 OKL_SUBSIDIES_B SUB,
824 OKL_SUBSIDY_POOLS_B SUB_POOL,
825 OKL_ASSETS_B ASSET,
826 OKL_LEASE_QUOTES_B QUOTE
827 WHERE ADJ.ADJUSTMENT_SOURCE_TYPE = 'SUBSIDY'
828 AND ADJ.PARENT_OBJECT_CODE = 'ASSET'
829 AND ADJ.ADJUSTMENT_SOURCE_ID = SUB.ID
830 AND SUB.SUBSIDY_POOL_ID = SUB_POOL.ID
831 AND ADJ.PARENT_OBJECT_ID = ASSET.ID
832 AND ASSET.PARENT_OBJECT_CODE = 'LEASEQUOTE'
833 AND ASSET.PARENT_OBJECT_ID = QUOTE.ID
834 AND QUOTE.STATUS = 'PR-APPROVED'
835 AND QUOTE.PARENT_OBJECT_ID = p_parent_object_id
836 AND QUOTE.ID <> p_quote_id
837 AND SUB_POOL.ID = p_subsidy_pool_id
838 GROUP BY SUB.SUBSIDY_POOL_ID, QUOTE.ID)
839 WHERE (SUBSIDY_POOL_ID, AMOUNT)
840 IN
841 (SELECT SUBSIDY_POOL_ID,
842 MAX(AMOUNT) AMOUNT
843 FROM
844 (SELECT SUB.SUBSIDY_POOL_ID SUBSIDY_POOL_ID,
845 SUM(DECODE(ADJ.VALUE, NULL, ADJ.DEFAULT_SUBSIDY_AMOUNT, ADJ.VALUE)) AMOUNT,
846 QUOTE.ID QUOTE_ID
847 FROM OKL_COST_ADJUSTMENTS_B ADJ,
848 OKL_SUBSIDIES_B SUB,
849 OKL_SUBSIDY_POOLS_B SUB_POOL,
850 OKL_ASSETS_B ASSET,
851 OKL_LEASE_QUOTES_B QUOTE
852 WHERE ADJ.ADJUSTMENT_SOURCE_TYPE = 'SUBSIDY'
853 AND ADJ.PARENT_OBJECT_CODE = 'ASSET'
854 AND ADJ.ADJUSTMENT_SOURCE_ID = SUB.ID
855 AND SUB.SUBSIDY_POOL_ID = SUB_POOL.ID
856 AND ADJ.PARENT_OBJECT_ID = ASSET.ID
857 AND ASSET.PARENT_OBJECT_CODE = 'LEASEQUOTE'
858 AND ASSET.PARENT_OBJECT_ID = QUOTE.ID
859 AND QUOTE.STATUS = 'PR-APPROVED'
860 AND QUOTE.PARENT_OBJECT_ID = p_parent_object_id
861 AND QUOTE.ID <> p_quote_id
862 AND SUB_POOL.ID = p_subsidy_pool_id
863 GROUP BY SUB.SUBSIDY_POOL_ID, QUOTE.ID)
864 GROUP BY SUBSIDY_POOL_ID)
865 GROUP BY SUBSIDY_POOL_ID, QUOTE_ID;
866
867 CURSOR c_get_quote_pool_values IS
868 SELECT SUB.SUBSIDY_POOL_ID SUBSIDY_POOL_ID,
869 SUM(DECODE(ADJ.VALUE, NULL, ADJ.DEFAULT_SUBSIDY_AMOUNT, ADJ.VALUE)) AMOUNT
870 FROM OKL_COST_ADJUSTMENTS_B ADJ,
871 OKL_SUBSIDIES_B SUB,
872 OKL_SUBSIDY_POOLS_B SUB_POOL,
873 OKL_ASSETS_B ASSET,
874 OKL_LEASE_QUOTES_B QUOTE
875 WHERE ADJ.ADJUSTMENT_SOURCE_TYPE = 'SUBSIDY'
876 AND ADJ.PARENT_OBJECT_CODE = 'ASSET'
877 AND ADJ.ADJUSTMENT_SOURCE_ID = SUB.ID
878 AND SUB.SUBSIDY_POOL_ID = SUB_POOL.ID
879 AND ADJ.PARENT_OBJECT_ID = ASSET.ID
880 AND ASSET.PARENT_OBJECT_CODE = 'LEASEQUOTE'
881 AND ASSET.PARENT_OBJECT_ID = QUOTE.ID
882 AND QUOTE.STATUS = 'PR-APPROVED'
883 AND QUOTE.ID = p_quote_id
884 GROUP BY SUB.SUBSIDY_POOL_ID, QUOTE.ID;
885
886 lv_inital_record_exists VARCHAR2(1) := 'N';
887 ln_max_subsidy_amount NUMBER;
888 ln_populated_quote_id NUMBER;
889 lb_is_this_max_amount BOOLEAN := TRUE;
890
891 l_sub_pool_tbl subsidy_pool_tbl_type;
892 lx_subpool_tbl subsidy_pool_tbl_type;
893
894 BEGIN
895
896 FOR l_get_quote_pool_values IN c_get_quote_pool_values LOOP
897 ln_max_subsidy_amount := l_get_quote_pool_values.amount;
898 lb_is_this_max_amount := TRUE;
899
900 FOR l_get_leaseopp_max_pool_values IN c_get_leaseopp_max_pool_values(p_subsidy_pool_id => l_get_quote_pool_values.subsidy_pool_id) LOOP
901 ln_populated_quote_id := l_get_leaseopp_max_pool_values.quote_id;
902 IF (l_get_leaseopp_max_pool_values.amount > ln_max_subsidy_amount) THEN
903 lb_is_this_max_amount := FALSE;
904 EXIT;
905 END IF;
906 END LOOP;
907
908 IF (lb_is_this_max_amount) THEN
909
910 -- Check if this quote is already populated in the quote usage table, by
911 -- comparing against the new quote id
912 -- "lv_inital_record_exists" checks if the quote is approved for the first
913 -- time in the lease opportunity. If so, it directly goes to else loop and
914 -- creates the initial record, otherwise it checks with the new quote being
915 -- approved
916 IF ((ln_populated_quote_id IS NOT NULL) AND (ln_populated_quote_id <> p_quote_id)) THEN
917 lv_inital_record_exists := check_initial_record(p_object_id => ln_populated_quote_id,
918 p_source_object_code => p_parent_object_code,
919 p_subsidy_pool_id => l_get_quote_pool_values.subsidy_pool_id,
920 x_return_status => x_return_status);
921 END IF;
922
923 IF (lv_inital_record_exists = 'Y') THEN
924 -- Record already exists in the transaction record. So, add the previous
925 -- balance, and reduce the new subsidy amount
926
927 -- Fetch the data from Quote Subsidy pool usage
928 l_sub_pool_tbl.delete;
929 fetch_quote_subpool_usage(p_subsidy_pool_id => l_get_quote_pool_values.subsidy_pool_id,
930 p_quote_id => ln_populated_quote_id,
931 x_subpool_tbl => l_sub_pool_tbl,
932 x_return_status => x_return_status);
933
934 FOR i IN l_sub_pool_tbl.FIRST .. l_sub_pool_tbl.LAST LOOP
935 IF l_sub_pool_tbl.EXISTS(i) THEN
936 l_sub_pool_tbl(i).trx_type_code := 'ADDITION';
937 l_sub_pool_tbl(i).trx_reason_code := p_transaction_reason;
938 END IF;
939 END LOOP;
940
941 -- Add the previous balance to the Subsidy pool
942 IF (l_sub_pool_tbl.COUNT > 0) THEN
943 okl_subsidy_pool_trx_pvt.create_pool_transaction
944 (p_api_version => p_api_version,
945 p_init_msg_list => p_init_msg_list,
946 x_return_status => x_return_status,
947 x_msg_count => x_msg_count,
948 x_msg_data => x_msg_data,
949 p_sixv_tbl => l_sub_pool_tbl,
950 x_sixv_tbl => lx_subpool_tbl);
951
952 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
953 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
954 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
955 RAISE OKL_API.G_EXCEPTION_ERROR;
956 END IF;
957 END IF;
958
959 -- Delete the Quote usage data for this Subsidy pool
960 delete_quote_subpool_usage(p_api_version => p_api_version,
961 p_init_msg_list => p_init_msg_list,
962 x_return_status => x_return_status,
963 x_msg_count => x_msg_count,
964 x_msg_data => x_msg_data,
965 p_subsidy_pool_id => l_get_quote_pool_values.subsidy_pool_id,
966 p_source_object_id => ln_populated_quote_id);
967 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
968 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
969 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
970 RAISE OKL_API.G_EXCEPTION_ERROR;
971 END IF;
972
973 -- Reduce the new balance from the Subsidy pool and create the current
974 -- data in Quote Subsidy pool usage
975 create_subpool_trx_and_usage(p_api_version => p_api_version,
976 p_init_msg_list => p_init_msg_list,
977 x_return_status => x_return_status,
978 x_msg_count => x_msg_count,
979 x_msg_data => x_msg_data,
980 p_source_object_code => p_parent_object_code,
981 p_quote_id => p_quote_id,
982 p_subsidy_pool_id => l_get_quote_pool_values.subsidy_pool_id,
983 p_transaction_reason => p_transaction_reason);
984 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
985 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
986 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
987 RAISE OKL_API.G_EXCEPTION_ERROR;
988 END IF;
989
990 ELSE
991 -- This is the initial transaction record. So, reduce the same from the
992 -- Subsidy pool and create the data in Quote Subsidy pool usage
993 create_subpool_trx_and_usage(p_api_version => p_api_version,
994 p_init_msg_list => p_init_msg_list,
995 x_return_status => x_return_status,
996 x_msg_count => x_msg_count,
997 x_msg_data => x_msg_data,
998 p_source_object_code => p_parent_object_code,
999 p_quote_id => p_quote_id,
1000 p_subsidy_pool_id => l_get_quote_pool_values.subsidy_pool_id,
1001 p_transaction_reason => p_transaction_reason);
1002 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1003 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1004 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1005 RAISE OKL_API.G_EXCEPTION_ERROR;
1006 END IF;
1007
1008 END IF;
1009 END IF;
1010
1011 END LOOP;
1012
1013 x_return_status := G_RET_STS_SUCCESS;
1014 EXCEPTION
1015
1016 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1017
1018 x_return_status := G_RET_STS_ERROR;
1019
1020 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1021
1022 x_return_status := G_RET_STS_UNEXP_ERROR;
1023
1024 WHEN OTHERS THEN
1025
1026 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1027 p_msg_name => G_DB_ERROR,
1028 p_token1 => G_PROG_NAME_TOKEN,
1029 p_token1_value => l_api_name,
1030 p_token2 => G_SQLCODE_TOKEN,
1031 p_token2_value => sqlcode,
1032 p_token3 => G_SQLERRM_TOKEN,
1033 p_token3_value => sqlerrm);
1034
1035 x_return_status := G_RET_STS_UNEXP_ERROR;
1036
1037 END handle_quote_pools;
1038
1039 ----------------------------------------
1040 -- PROCEDURE handle_cancel_leaseopp
1041 ----------------------------------------
1042 PROCEDURE handle_cancel_leaseopp (p_api_version IN NUMBER,
1043 p_init_msg_list IN VARCHAR2,
1044 p_transaction_reason IN VARCHAR2,
1045 p_parent_object_id IN NUMBER,
1046 x_return_status OUT NOCOPY VARCHAR2,
1047 x_msg_count OUT NOCOPY NUMBER,
1048 x_msg_data OUT NOCOPY VARCHAR2) IS
1049
1050 l_program_name CONSTANT VARCHAR2(30) := 'handle_cancel_leaseopp';
1051 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
1052
1053 CURSOR c_get_leaseopp_pool_values IS
1054 SELECT SUB.SUBSIDY_POOL_ID SUBSIDY_POOL_ID,
1055 QUOTE.ID QUOTE_ID
1056 FROM OKL_COST_ADJUSTMENTS_B ADJ,
1057 OKL_SUBSIDIES_B SUB,
1058 OKL_SUBSIDY_POOLS_B SUB_POOL,
1059 OKL_ASSETS_B ASSET,
1060 OKL_LEASE_QUOTES_B QUOTE
1061 WHERE ADJ.ADJUSTMENT_SOURCE_TYPE = 'SUBSIDY'
1062 AND ADJ.PARENT_OBJECT_CODE = 'ASSET'
1063 AND ADJ.ADJUSTMENT_SOURCE_ID = SUB.ID
1064 AND SUB.SUBSIDY_POOL_ID = SUB_POOL.ID
1065 AND ADJ.PARENT_OBJECT_ID = ASSET.ID
1066 AND ASSET.PARENT_OBJECT_CODE = 'LEASEQUOTE'
1067 AND ASSET.PARENT_OBJECT_ID = QUOTE.ID
1068 AND QUOTE.STATUS = 'PR-APPROVED'
1069 AND QUOTE.PARENT_OBJECT_ID = p_parent_object_id
1070 GROUP BY SUB.SUBSIDY_POOL_ID, QUOTE.ID;
1071
1072 ln_count NUMBER;
1073
1074 l_sub_pool_tbl subsidy_pool_tbl_type;
1075 lx_subpool_tbl subsidy_pool_tbl_type;
1076
1077 BEGIN
1078
1079 FOR l_get_leaseopp_pool_values IN c_get_leaseopp_pool_values LOOP
1080
1081 SELECT COUNT(*)
1082 INTO ln_count
1083 FROM OKL_QUOTE_SUBPOOL_USAGE
1084 WHERE SUBSIDY_POOL_ID = l_get_leaseopp_pool_values.subsidy_pool_id
1085 AND SOURCE_OBJECT_ID = l_get_leaseopp_pool_values.quote_id;
1086
1087 IF (ln_count > 0) THEN -- The Subsidy pool is highest for a Quote
1088
1089 -- Fetch the data from Quote Subsidy pool usage
1090 l_sub_pool_tbl.delete;
1091 fetch_quote_subpool_usage(p_subsidy_pool_id => l_get_leaseopp_pool_values.subsidy_pool_id,
1092 p_quote_id => l_get_leaseopp_pool_values.quote_id,
1093 x_subpool_tbl => l_sub_pool_tbl,
1094 x_return_status => x_return_status);
1095
1096 FOR i IN l_sub_pool_tbl.FIRST .. l_sub_pool_tbl.LAST LOOP
1097 IF l_sub_pool_tbl.EXISTS(i) THEN
1098 l_sub_pool_tbl(i).trx_type_code := 'ADDITION';
1099 l_sub_pool_tbl(i).trx_reason_code := p_transaction_reason;
1100 END IF;
1101 END LOOP;
1102
1103 -- Add the previous balance to the Subsidy pool
1104 IF (l_sub_pool_tbl.COUNT > 0) THEN
1105 okl_subsidy_pool_trx_pvt.create_pool_transaction
1106 (p_api_version => p_api_version,
1107 p_init_msg_list => p_init_msg_list,
1108 x_return_status => x_return_status,
1109 x_msg_count => x_msg_count,
1110 x_msg_data => x_msg_data,
1111 p_sixv_tbl => l_sub_pool_tbl,
1112 x_sixv_tbl => lx_subpool_tbl);
1113 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1114 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1115 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1116 RAISE OKL_API.G_EXCEPTION_ERROR;
1117 END IF;
1118 END IF;
1119
1120 -- Delete the Quote usage data for this Subsidy pool
1121 delete_quote_subpool_usage(p_api_version => p_api_version,
1122 p_init_msg_list => p_init_msg_list,
1123 x_return_status => x_return_status,
1124 x_msg_count => x_msg_count,
1125 x_msg_data => x_msg_data,
1126 p_subsidy_pool_id => l_get_leaseopp_pool_values.subsidy_pool_id,
1127 p_source_object_id => l_get_leaseopp_pool_values.quote_id);
1128 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1129 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1130 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1131 RAISE OKL_API.G_EXCEPTION_ERROR;
1132 END IF;
1133
1134 END IF;
1135
1136 END LOOP;
1137
1138 x_return_status := G_RET_STS_SUCCESS;
1139 EXCEPTION
1140
1141 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1142
1143 x_return_status := G_RET_STS_ERROR;
1144
1145 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1146
1147 x_return_status := G_RET_STS_UNEXP_ERROR;
1148
1149 WHEN OTHERS THEN
1150
1151 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1152 p_msg_name => G_DB_ERROR,
1153 p_token1 => G_PROG_NAME_TOKEN,
1154 p_token1_value => l_api_name,
1155 p_token2 => G_SQLCODE_TOKEN,
1156 p_token2_value => sqlcode,
1157 p_token3 => G_SQLERRM_TOKEN,
1158 p_token3_value => sqlerrm);
1159
1160 x_return_status := G_RET_STS_UNEXP_ERROR;
1161
1162 END handle_cancel_leaseopp;
1163
1164 ----------------------------------------
1165 -- PROCEDURE handle_quote_contract
1166 ----------------------------------------
1167 PROCEDURE handle_quote_contract (p_api_version IN NUMBER,
1168 p_init_msg_list IN VARCHAR2,
1169 p_transaction_reason IN VARCHAR2,
1170 p_quote_id IN NUMBER,
1171 x_return_status OUT NOCOPY VARCHAR2,
1172 x_msg_count OUT NOCOPY NUMBER,
1173 x_msg_data OUT NOCOPY VARCHAR2) IS
1174
1175 l_program_name CONSTANT VARCHAR2(30) := 'handle_quote_contract';
1176 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
1177
1178 CURSOR c_get_leaseopp_pool_values IS
1179 SELECT SUB.SUBSIDY_POOL_ID SUBSIDY_POOL_ID,
1180 QUOTE.ID QUOTE_ID
1181 FROM OKL_COST_ADJUSTMENTS_B ADJ,
1182 OKL_SUBSIDIES_B SUB,
1183 OKL_SUBSIDY_POOLS_B SUB_POOL,
1184 OKL_ASSETS_B ASSET,
1185 OKL_LEASE_QUOTES_B QUOTE
1186 WHERE ADJ.ADJUSTMENT_SOURCE_TYPE = 'SUBSIDY'
1187 AND ADJ.PARENT_OBJECT_CODE = 'ASSET'
1188 AND ADJ.ADJUSTMENT_SOURCE_ID = SUB.ID
1189 AND SUB.SUBSIDY_POOL_ID = SUB_POOL.ID
1190 AND ADJ.PARENT_OBJECT_ID = ASSET.ID
1191 AND ASSET.PARENT_OBJECT_CODE = 'LEASEQUOTE'
1192 AND ASSET.PARENT_OBJECT_ID = QUOTE.ID
1193 AND QUOTE.STATUS = 'PR-APPROVED'
1194 AND QUOTE.PARENT_OBJECT_ID = (SELECT PARENT_OBJECT_ID
1195 FROM OKL_LEASE_QUOTES_B
1196 WHERE ID = p_quote_id)
1197 GROUP BY SUB.SUBSIDY_POOL_ID, QUOTE.ID;
1198
1199 ln_count NUMBER;
1200
1201 l_sub_pool_tbl subsidy_pool_tbl_type;
1202 lx_subpool_tbl subsidy_pool_tbl_type;
1203
1204 BEGIN
1205
1206 FOR l_get_leaseopp_pool_values IN c_get_leaseopp_pool_values LOOP
1207
1208 SELECT COUNT(*)
1209 INTO ln_count
1210 FROM OKL_QUOTE_SUBPOOL_USAGE
1211 WHERE SUBSIDY_POOL_ID = l_get_leaseopp_pool_values.subsidy_pool_id
1212 AND SOURCE_OBJECT_ID = l_get_leaseopp_pool_values.quote_id;
1213
1214 IF (ln_count > 0) THEN -- The Subsidy pool is highest for a Quote
1215
1216 -- Fetch the data from Quote Subsidy pool usage
1217 fetch_quote_subpool_usage(p_subsidy_pool_id => l_get_leaseopp_pool_values.subsidy_pool_id,
1218 p_quote_id => l_get_leaseopp_pool_values.quote_id,
1219 x_subpool_tbl => l_sub_pool_tbl,
1220 x_return_status => x_return_status);
1221
1222 FOR i IN l_sub_pool_tbl.FIRST .. l_sub_pool_tbl.LAST LOOP
1223 IF l_sub_pool_tbl.EXISTS(i) THEN
1224 l_sub_pool_tbl(i).trx_type_code := 'ADDITION';
1225 l_sub_pool_tbl(i).trx_reason_code := p_transaction_reason;
1226 END IF;
1227 END LOOP;
1228
1229 -- Add the previous balance to the Subsidy pool
1230 IF (l_sub_pool_tbl.COUNT > 0) THEN
1231 okl_subsidy_pool_trx_pvt.create_pool_transaction
1232 (p_api_version => p_api_version,
1233 p_init_msg_list => p_init_msg_list,
1234 x_return_status => x_return_status,
1235 x_msg_count => x_msg_count,
1236 x_msg_data => x_msg_data,
1237 p_sixv_tbl => l_sub_pool_tbl,
1238 x_sixv_tbl => lx_subpool_tbl);
1239 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1240 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1241 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1242 RAISE OKL_API.G_EXCEPTION_ERROR;
1243 END IF;
1244 END IF;
1245
1246 -- Delete the Quote usage data for this Subsidy pool
1247 delete_quote_subpool_usage(p_api_version => p_api_version,
1248 p_init_msg_list => p_init_msg_list,
1249 x_return_status => x_return_status,
1250 x_msg_count => x_msg_count,
1251 x_msg_data => x_msg_data,
1252 p_subsidy_pool_id => l_get_leaseopp_pool_values.subsidy_pool_id,
1253 p_source_object_id => l_get_leaseopp_pool_values.quote_id);
1254 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1255 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1256 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1257 RAISE OKL_API.G_EXCEPTION_ERROR;
1258 END IF;
1259
1260 END IF;
1261
1262 END LOOP;
1263
1264 x_return_status := G_RET_STS_SUCCESS;
1265 EXCEPTION
1266
1267 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1268
1269 x_return_status := G_RET_STS_ERROR;
1270
1271 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1272
1273 x_return_status := G_RET_STS_UNEXP_ERROR;
1274
1275 WHEN OTHERS THEN
1276
1277 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1278 p_msg_name => G_DB_ERROR,
1279 p_token1 => G_PROG_NAME_TOKEN,
1280 p_token1_value => l_api_name,
1281 p_token2 => G_SQLCODE_TOKEN,
1282 p_token2_value => sqlcode,
1283 p_token3 => G_SQLERRM_TOKEN,
1284 p_token3_value => sqlerrm);
1285
1286 x_return_status := G_RET_STS_UNEXP_ERROR;
1287
1288 END handle_quote_contract;
1289
1290 ----------------------------------------
1291 -- PROCEDURE handle_leaseapp_contract
1292 ----------------------------------------
1293 PROCEDURE handle_leaseapp_contract (p_api_version IN NUMBER,
1294 p_init_msg_list IN VARCHAR2,
1295 p_transaction_reason IN VARCHAR2,
1296 p_leaseapp_id IN NUMBER,
1297 x_return_status OUT NOCOPY VARCHAR2,
1298 x_msg_count OUT NOCOPY NUMBER,
1299 x_msg_data OUT NOCOPY VARCHAR2) IS
1300
1301 l_program_name CONSTANT VARCHAR2(30) := 'handle_leaseapp_contract';
1302 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
1303
1304 CURSOR c_get_leaseapp_pool_values IS
1305 SELECT SUB.SUBSIDY_POOL_ID SUBSIDY_POOL_ID,
1306 QUOTE.ID QUOTE_ID
1307 FROM OKL_COST_ADJUSTMENTS_B ADJ,
1308 OKL_SUBSIDIES_B SUB,
1309 OKL_SUBSIDY_POOLS_B SUB_POOL,
1310 OKL_ASSETS_B ASSET,
1311 OKL_LEASE_QUOTES_B QUOTE
1312 WHERE ADJ.ADJUSTMENT_SOURCE_TYPE = 'SUBSIDY'
1313 AND ADJ.PARENT_OBJECT_CODE = 'ASSET'
1314 AND ADJ.ADJUSTMENT_SOURCE_ID = SUB.ID
1315 AND SUB.SUBSIDY_POOL_ID = SUB_POOL.ID
1316 AND ADJ.PARENT_OBJECT_ID = ASSET.ID
1317 AND ASSET.PARENT_OBJECT_CODE = 'LEASEQUOTE'
1318 AND ASSET.PARENT_OBJECT_ID = QUOTE.ID
1319 AND QUOTE.PARENT_OBJECT_ID = p_leaseapp_id
1320 AND QUOTE.PRIMARY_QUOTE = 'Y'
1321 GROUP BY SUB.SUBSIDY_POOL_ID, QUOTE.ID;
1322
1323 CURSOR c_get_leaseopp_quote_id(p_leaseapp_id IN NUMBER) IS
1324 SELECT QUOTE.ID
1325 FROM OKL_LEASE_QUOTES_B QUOTE,
1326 OKL_LEASE_OPPORTUNITIES_B LEASEOPP
1327 WHERE QUOTE.PARENT_OBJECT_ID = LEASEOPP.ID
1328 AND QUOTE.PARENT_OBJECT_CODE = 'LEASEOPP'
1329 AND QUOTE.STATUS = 'CT-ACCEPTED'
1330 AND LEASEOPP.ID = (SELECT LEASE_OPPORTUNITY_ID
1331 FROM OKL_LEASE_APPLICATIONS_B
1332 WHERE ID = p_leaseapp_id);
1333
1334 ln_count NUMBER;
1335 ln_leaseopp_quote_id NUMBER;
1336 lv_leaseopp_quote VARCHAR2(1);
1337
1338 l_sub_pool_tbl subsidy_pool_tbl_type;
1339 lx_subpool_tbl subsidy_pool_tbl_type;
1340
1341 BEGIN
1342
1343 lv_leaseopp_quote := check_leaseopp_quote (p_lease_app_id => p_leaseapp_id,
1344 x_return_status => x_return_status);
1345
1346 IF (lv_leaseopp_quote = 'Y') THEN -- The Leaseapp is sourced from Lease opportunity
1347 OPEN c_get_leaseopp_quote_id(p_leaseapp_id => p_leaseapp_id);
1348 FETCH c_get_leaseopp_quote_id INTO ln_leaseopp_quote_id;
1349 CLOSE c_get_leaseopp_quote_id;
1350
1351 handle_quote_contract (p_api_version => p_api_version,
1352 p_init_msg_list => p_init_msg_list,
1353 p_transaction_reason => p_transaction_reason,
1354 p_quote_id => ln_leaseopp_quote_id,
1355 x_return_status => x_return_status,
1356 x_msg_count => x_msg_count,
1357 x_msg_data => x_msg_data);
1358 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1359 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1360 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1361 RAISE OKL_API.G_EXCEPTION_ERROR;
1362 END IF;
1363 END IF;
1364
1365 FOR l_get_leaseapp_pool_values IN c_get_leaseapp_pool_values LOOP
1366
1367 SELECT COUNT(*)
1368 INTO ln_count
1369 FROM OKL_QUOTE_SUBPOOL_USAGE
1370 WHERE SUBSIDY_POOL_ID = l_get_leaseapp_pool_values.subsidy_pool_id
1371 AND SOURCE_OBJECT_ID = p_leaseapp_id;
1372
1373 IF (ln_count > 0) THEN -- The Subsidy pool is highest for a Quote
1374
1375 -- Fetch the data from Quote Subsidy pool usage
1376 fetch_quote_subpool_usage(p_subsidy_pool_id => l_get_leaseapp_pool_values.subsidy_pool_id,
1377 p_quote_id => l_get_leaseapp_pool_values.quote_id,
1378 x_subpool_tbl => l_sub_pool_tbl,
1379 x_return_status => x_return_status);
1380
1381 FOR i IN l_sub_pool_tbl.FIRST .. l_sub_pool_tbl.LAST LOOP
1382 IF l_sub_pool_tbl.EXISTS(i) THEN
1383 l_sub_pool_tbl(i).trx_type_code := 'ADDITION';
1384 l_sub_pool_tbl(i).trx_reason_code := p_transaction_reason;
1385 END IF;
1386 END LOOP;
1387
1388 -- Add the previous balance to the Subsidy pool
1389 IF (l_sub_pool_tbl.COUNT > 0) THEN
1390 okl_subsidy_pool_trx_pvt.create_pool_transaction
1391 (p_api_version => p_api_version,
1392 p_init_msg_list => p_init_msg_list,
1393 x_return_status => x_return_status,
1394 x_msg_count => x_msg_count,
1395 x_msg_data => x_msg_data,
1396 p_sixv_tbl => l_sub_pool_tbl,
1397 x_sixv_tbl => lx_subpool_tbl);
1398 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1399 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1400 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1401 RAISE OKL_API.G_EXCEPTION_ERROR;
1402 END IF;
1403 END IF;
1404
1405 -- Delete the Quote usage data for this Subsidy pool
1406 delete_quote_subpool_usage(p_api_version => p_api_version,
1407 p_init_msg_list => p_init_msg_list,
1408 x_return_status => x_return_status,
1409 x_msg_count => x_msg_count,
1410 x_msg_data => x_msg_data,
1411 p_subsidy_pool_id => l_get_leaseapp_pool_values.subsidy_pool_id,
1412 p_source_object_id => l_get_leaseapp_pool_values.quote_id);
1413 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1414 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1415 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1416 RAISE OKL_API.G_EXCEPTION_ERROR;
1417 END IF;
1418
1419 END IF;
1420
1421 END LOOP;
1422
1423 x_return_status := G_RET_STS_SUCCESS;
1424 EXCEPTION
1425
1426 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1427
1428 x_return_status := G_RET_STS_ERROR;
1429
1430 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1431
1432 x_return_status := G_RET_STS_UNEXP_ERROR;
1433
1434 WHEN OTHERS THEN
1435
1436 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1437 p_msg_name => G_DB_ERROR,
1438 p_token1 => G_PROG_NAME_TOKEN,
1439 p_token1_value => l_api_name,
1440 p_token2 => G_SQLCODE_TOKEN,
1441 p_token2_value => sqlcode,
1442 p_token3 => G_SQLERRM_TOKEN,
1443 p_token3_value => sqlerrm);
1444
1445 x_return_status := G_RET_STS_UNEXP_ERROR;
1446
1447 END handle_leaseapp_contract;
1448
1449 ----------------------------------------
1450 -- PROCEDURE handle_active_contract
1451 ----------------------------------------
1452 PROCEDURE handle_active_contract (p_api_version IN NUMBER,
1453 p_init_msg_list IN VARCHAR2,
1454 p_transaction_reason IN VARCHAR2,
1455 p_contract_id IN NUMBER,
1456 x_return_status OUT NOCOPY VARCHAR2,
1457 x_msg_count OUT NOCOPY NUMBER,
1458 x_msg_data OUT NOCOPY VARCHAR2) IS
1459
1460 l_program_name CONSTANT VARCHAR2(30) := 'handle_active_contract';
1461 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
1462
1463 ln_source_object_id NUMBER;
1464 lv_source_object_code VARCHAR2(30);
1465
1466 BEGIN
1467
1468 SELECT ORIG_SYSTEM_ID1,
1469 ORIG_SYSTEM_SOURCE_CODE
1470 INTO ln_source_object_id, lv_source_object_code
1471 FROM OKC_K_HEADERS_B
1472 WHERE ID = p_contract_id;
1473
1474 IF (lv_source_object_code = 'OKL_QUOTE') THEN
1475
1476 handle_quote_contract (p_api_version => p_api_version,
1477 p_init_msg_list => p_init_msg_list,
1478 p_transaction_reason => p_transaction_reason,
1479 p_quote_id => ln_source_object_id,
1480 x_return_status => x_return_status,
1481 x_msg_count => x_msg_count,
1482 x_msg_data => x_msg_data);
1483 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1484 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1485 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1486 RAISE OKL_API.G_EXCEPTION_ERROR;
1487 END IF;
1488 ELSIF (lv_source_object_code = 'OKL_LEASE_APP') THEN
1489 handle_leaseapp_contract (p_api_version => p_api_version,
1490 p_init_msg_list => p_init_msg_list,
1491 p_transaction_reason => p_transaction_reason,
1492 p_leaseapp_id => ln_source_object_id,
1493 x_return_status => x_return_status,
1494 x_msg_count => x_msg_count,
1495 x_msg_data => x_msg_data);
1496 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1497 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1498 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1499 RAISE OKL_API.G_EXCEPTION_ERROR;
1500 END IF;
1501 END IF;
1502
1503 x_return_status := G_RET_STS_SUCCESS;
1504 EXCEPTION
1505
1506 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1507
1508 x_return_status := G_RET_STS_ERROR;
1509
1510 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1511
1512 x_return_status := G_RET_STS_UNEXP_ERROR;
1513
1514 WHEN OTHERS THEN
1515
1516 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1517 p_msg_name => G_DB_ERROR,
1518 p_token1 => G_PROG_NAME_TOKEN,
1519 p_token1_value => l_api_name,
1520 p_token2 => G_SQLCODE_TOKEN,
1521 p_token2_value => sqlcode,
1522 p_token3 => G_SQLERRM_TOKEN,
1523 p_token3_value => sqlerrm);
1524
1525 x_return_status := G_RET_STS_UNEXP_ERROR;
1526
1527 END handle_active_contract;
1528
1529 ----------------------------------------
1530 -- PROCEDURE handle_leaseapp_pool
1531 ----------------------------------------
1532 PROCEDURE handle_leaseapp_pool (p_api_version IN NUMBER,
1533 p_init_msg_list IN VARCHAR2,
1534 p_transaction_reason IN VARCHAR2,
1535 p_parent_object_id IN NUMBER,
1536 p_parent_object_code IN VARCHAR2,
1537 p_quote_id IN NUMBER,
1538 x_return_status OUT NOCOPY VARCHAR2,
1539 x_msg_count OUT NOCOPY NUMBER,
1540 x_msg_data OUT NOCOPY VARCHAR2) IS
1541
1542 l_program_name CONSTANT VARCHAR2(30) := 'handle_leaseapp_pool';
1543 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
1544
1545 CURSOR c_get_leaseapp_pool_values IS
1546 SELECT SUB.SUBSIDY_POOL_ID SUBSIDY_POOL_ID,
1547 QUOTE.ID QUOTE_ID,
1548 SUM(DECODE(ADJ.VALUE, NULL, ADJ.DEFAULT_SUBSIDY_AMOUNT, ADJ.VALUE)) AMOUNT
1549 FROM OKL_COST_ADJUSTMENTS_B ADJ,
1550 OKL_SUBSIDIES_B SUB,
1551 OKL_SUBSIDY_POOLS_B SUB_POOL,
1552 OKL_ASSETS_B ASSET,
1553 OKL_LEASE_QUOTES_B QUOTE
1554 WHERE ADJ.ADJUSTMENT_SOURCE_TYPE = 'SUBSIDY'
1555 AND ADJ.PARENT_OBJECT_CODE = 'ASSET'
1556 AND ADJ.ADJUSTMENT_SOURCE_ID = SUB.ID
1557 AND SUB.SUBSIDY_POOL_ID = SUB_POOL.ID
1558 AND ADJ.PARENT_OBJECT_ID = ASSET.ID
1559 AND ASSET.PARENT_OBJECT_CODE = 'LEASEQUOTE'
1560 AND ASSET.PARENT_OBJECT_ID = QUOTE.ID
1561 AND QUOTE.ID = p_quote_id
1562 GROUP BY SUB.SUBSIDY_POOL_ID, QUOTE.ID;
1563
1564 ln_count NUMBER;
1565 lv_leaseopp_quote VARCHAR2(1);
1566
1567 l_sub_pool_tbl subsidy_pool_tbl_type;
1568 lx_subpool_tbl subsidy_pool_tbl_type;
1569
1570 lv_linked_lop_id NUMBER;
1571
1572 ln_this_quote_amount NUMBER;
1573 ln_trans_quote NUMBER;
1574 ln_rollback_quote NUMBER;
1575 lb_this_quote_max_amount BOOLEAN := FALSE;
1576 lb_initial_trans BOOLEAN := FALSE;
1577 lv_inital_record_exists VARCHAR2(1) := 'N';
1578
1579 ln_lap_pool_usage_amount NUMBER;
1580 ln_lap_pool_usage_quote NUMBER;
1581
1582 ln_lop_pool_usage_amount NUMBER;
1583 ln_lop_pool_usage_quote NUMBER;
1584
1585 BEGIN
1586 -- Check if the Lease Application is created from a Quote
1587 lv_leaseopp_quote := check_leaseopp_quote (p_lease_app_id => p_parent_object_id,
1588 x_return_status => x_return_status);
1589
1590 --get the linked lease opportunity id
1591 SELECT LEASE_OPPORTUNITY_ID
1592 INTO lv_linked_lop_id
1593 FROM OKL_LEASE_APPLICATIONS_B
1594 WHERE ID =p_parent_object_id;
1595
1596 --loop over all the subsidy pools used in this quote of lease appplication
1597 FOR l_get_leaseapp_pool_values IN c_get_leaseapp_pool_values LOOP
1598
1599 ln_this_quote_amount := l_get_leaseapp_pool_values.amount;
1600
1601 --if the Lease Application is created from a Quote
1602 IF (lv_leaseopp_quote = 'Y') THEN
1603 --check the Approved quotes under the linked lease opportunity and get the maximum subsidy
1604 -- pool usage
1605 get_linked_lop_maxsp_usage( p_subsidy_pool_id => l_get_leaseapp_pool_values.subsidy_pool_id
1606 ,p_lop_id => lv_linked_lop_id
1607 ,p_quote_id => p_quote_id
1608 ,x_max_usage_qte_id => ln_lop_pool_usage_quote
1609 ,x_max_usage_amt => ln_lop_pool_usage_amount
1610 ,x_return_Status => x_return_Status );
1611
1612 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1613 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1614 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1615 RAISE OKL_API.G_EXCEPTION_ERROR;
1616 END IF;
1617 ELSE
1618 ln_lop_pool_usage_quote := null;
1619 ln_lop_pool_usage_amount := null;
1620 END IF;
1621
1622 --check the Accepted quote and the Credit Recommended counter offers under the linked lease applications
1623 --and get the maximum subsidy pool usage
1624 get_linked_lap_maxsp_usage( p_subsidy_pool_id => l_get_leaseapp_pool_values.subsidy_pool_id
1625 ,p_lap_id => p_parent_object_id
1626 ,p_current_qte_id => p_quote_id
1627 ,p_transaction => p_transaction_reason
1628 ,x_max_usage_qte_id => ln_lap_pool_usage_quote
1629 ,x_max_usage_amt => ln_lap_pool_usage_amount
1630 ,x_return_Status => x_return_Status );
1631
1632 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1633 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1634 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1635 RAISE OKL_API.G_EXCEPTION_ERROR;
1636 END IF;
1637
1638 -- get the maximum of the subsidy pool usage among approved quotes of linked
1639 --lop, accepted quote and Credit Recommendation offers of linked laps and this
1640 --quote/offer of the lap
1641 -- dbms_output.put_line(' Pool Usage Quote'||ln_lap_pool_usage_quote);
1642 -- dbms_output.put_line(' Pool Usage Amount'||ln_lap_pool_usage_amount);
1643 -- dbms_output.put_line(' This Quote Amount'||ln_this_quote_amount);
1644
1645 IF (ln_lap_pool_usage_quote IS NOT NULL AND
1646 ln_lop_pool_usage_quote IS NOT NULL) THEN
1647 IF ln_lap_pool_usage_amount > ln_lop_pool_usage_amount THEN
1648 IF ln_this_quote_amount > ln_lap_pool_usage_amount THEN
1649 lb_this_quote_max_amount := TRUE;
1650 ln_trans_quote := p_quote_id;
1651 ELSE
1652 lb_this_quote_max_amount := FALSE;
1653 ln_rollback_quote := ln_lap_pool_usage_quote;
1654 ln_trans_quote := ln_lap_pool_usage_quote;
1655 END IF;
1656 ELSE
1657 IF ln_this_quote_amount > ln_lop_pool_usage_amount THEN
1658 lb_this_quote_max_amount := TRUE;
1659 ln_trans_quote := p_quote_id;
1660 ELSE
1661 lb_this_quote_max_amount := FALSE;
1662 ln_rollback_quote := ln_lop_pool_usage_quote;
1663 ln_trans_quote := ln_lop_pool_usage_quote;
1664 END IF;
1665 END IF;
1666 ELSIF ln_lap_pool_usage_quote IS NOT NULL THEN
1667 IF ln_this_quote_amount > ln_lap_pool_usage_amount THEN
1668 lb_this_quote_max_amount := TRUE;
1669 ln_trans_quote := p_quote_id;
1670 ELSE
1671 lb_this_quote_max_amount := FALSE;
1672 ln_trans_quote := ln_lap_pool_usage_quote;
1673 END IF;
1674 ln_rollback_quote := ln_lap_pool_usage_quote;
1675 ELSIF ln_lop_pool_usage_quote IS NOT NULL THEN
1676 IF ln_this_quote_amount > ln_lop_pool_usage_amount THEN
1677 lb_this_quote_max_amount := TRUE;
1678 ln_trans_quote := p_quote_id;
1679 ELSE
1680 lb_this_quote_max_amount := FALSE;
1681 ln_trans_quote := ln_lop_pool_usage_quote;
1682 END IF;
1683 ln_rollback_quote := ln_lop_pool_usage_quote;
1684 ELSE
1685 lb_this_quote_max_amount := TRUE;
1686 ln_trans_quote := p_quote_id;
1687 lb_initial_trans := TRUE;
1688 END IF;
1689
1690 -- dbms_output.put_line('Transaction Quote '||ln_trans_quote);
1691 -- dbms_output.put_line('Rollback Quote '||ln_rollback_quote);
1692
1693 IF (lb_initial_trans) THEN -- First Transaction
1694 -- This is the initial transaction record. So, reduce the same from the
1695 -- Subsidy pool and create the data in Quote Subsidy pool usage
1696 -- dbms_output.put_line('Initial Transaction .. ');
1697 create_subpool_trx_and_usage(p_api_version => p_api_version,
1698 p_init_msg_list => p_init_msg_list,
1699 x_return_status => x_return_status,
1700 x_msg_count => x_msg_count,
1701 x_msg_data => x_msg_data,
1702 p_source_object_code => p_parent_object_code,
1703 p_quote_id => ln_trans_quote,
1704 p_subsidy_pool_id => l_get_leaseapp_pool_values.subsidy_pool_id,
1705 p_transaction_reason => p_transaction_reason);
1706 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1707 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1708 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1709 RAISE OKL_API.G_EXCEPTION_ERROR;
1710 END IF;
1711 ELSE
1712 IF (lb_this_quote_max_amount) THEN
1713 -- dbms_output.put_line('This Quote Max Amount .. ');
1714 -- Rollback the amount in the pool and add this to the pool
1715 lv_inital_record_exists := check_initial_record(p_object_id => ln_rollback_quote,
1716 p_source_object_code => p_parent_object_code,
1717 p_subsidy_pool_id => l_get_leaseapp_pool_values.subsidy_pool_id,
1718 x_return_status => x_return_status);
1719 IF (lv_inital_record_exists = 'Y') THEN
1720 -- dbms_output.put_line('Initial Record exists .. so rolling them back .. ');
1721 l_sub_pool_tbl.delete;
1722 fetch_quote_subpool_usage(p_subsidy_pool_id => l_get_leaseapp_pool_values.subsidy_pool_id,
1723 p_quote_id => ln_rollback_quote,
1724 x_subpool_tbl => l_sub_pool_tbl,
1725 x_return_status => x_return_status);
1726
1727 FOR i IN l_sub_pool_tbl.FIRST .. l_sub_pool_tbl.LAST LOOP
1728 IF l_sub_pool_tbl.EXISTS(i) THEN
1729 l_sub_pool_tbl(i).trx_type_code := 'ADDITION';
1730 l_sub_pool_tbl(i).trx_reason_code := p_transaction_reason;
1731 END IF;
1732 END LOOP;
1733
1734 -- Add the previous balance to the Subsidy pool
1735 IF (l_sub_pool_tbl.COUNT > 0) THEN
1736 okl_subsidy_pool_trx_pvt.create_pool_transaction
1737 (p_api_version => p_api_version,
1738 p_init_msg_list => p_init_msg_list,
1739 x_return_status => x_return_status,
1740 x_msg_count => x_msg_count,
1741 x_msg_data => x_msg_data,
1742 p_sixv_tbl => l_sub_pool_tbl,
1743 x_sixv_tbl => lx_subpool_tbl);
1744 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1745 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1746 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1747 RAISE OKL_API.G_EXCEPTION_ERROR;
1748 END IF;
1749 END IF;
1750
1751 -- Delete the Quote usage data for this Subsidy pool
1752 delete_quote_subpool_usage(p_api_version => p_api_version,
1753 p_init_msg_list => p_init_msg_list,
1754 x_return_status => x_return_status,
1755 x_msg_count => x_msg_count,
1756 x_msg_data => x_msg_data,
1757 p_subsidy_pool_id => l_get_leaseapp_pool_values.subsidy_pool_id,
1758 p_source_object_id => ln_rollback_quote);
1759 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1760 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1761 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1762 RAISE OKL_API.G_EXCEPTION_ERROR;
1763 END IF;
1764
1765 -- Reduce this quote amount from pool balance
1766 create_subpool_trx_and_usage(p_api_version => p_api_version,
1767 p_init_msg_list => p_init_msg_list,
1768 x_return_status => x_return_status,
1769 x_msg_count => x_msg_count,
1770 x_msg_data => x_msg_data,
1771 p_source_object_code => p_parent_object_code,
1772 p_quote_id => ln_trans_quote,
1773 p_subsidy_pool_id => l_get_leaseapp_pool_values.subsidy_pool_id,
1774 p_transaction_reason => p_transaction_reason);
1775 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1776 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1777 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1778 RAISE OKL_API.G_EXCEPTION_ERROR;
1779 END IF;
1780
1781 ELSE
1782 -- dbms_output.put_line('Initial Record doesnt exist, so creating fresh trans .. ');
1783 create_subpool_trx_and_usage(p_api_version => p_api_version,
1784 p_init_msg_list => p_init_msg_list,
1785 x_return_status => x_return_status,
1786 x_msg_count => x_msg_count,
1787 x_msg_data => x_msg_data,
1788 p_source_object_code => p_parent_object_code,
1789 p_quote_id => ln_trans_quote,
1790 p_subsidy_pool_id => l_get_leaseapp_pool_values.subsidy_pool_id,
1791 p_transaction_reason => p_transaction_reason);
1792 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1793 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1794 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1795 RAISE OKL_API.G_EXCEPTION_ERROR;
1796 END IF;
1797 END IF;
1798 END IF;
1799 END IF;
1800 END LOOP;
1801
1802
1803 x_return_status := G_RET_STS_SUCCESS;
1804 EXCEPTION
1805
1806 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1807
1808 x_return_status := G_RET_STS_ERROR;
1809
1810 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1811
1812 x_return_status := G_RET_STS_UNEXP_ERROR;
1813
1814 WHEN OTHERS THEN
1815
1816 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1817 p_msg_name => G_DB_ERROR,
1818 p_token1 => G_PROG_NAME_TOKEN,
1819 p_token1_value => l_api_name,
1820 p_token2 => G_SQLCODE_TOKEN,
1821 p_token2_value => sqlcode,
1822 p_token3 => G_SQLERRM_TOKEN,
1823 p_token3_value => sqlerrm);
1824
1825 x_return_status := G_RET_STS_UNEXP_ERROR;
1826
1827 END handle_leaseapp_pool;
1828
1829 ----------------------------------------
1830 -- PROCEDURE handle_leaseapp_update
1831 ----------------------------------------
1832 PROCEDURE handle_leaseapp_update (p_api_version IN NUMBER,
1833 p_init_msg_list IN VARCHAR2,
1834 p_transaction_reason IN VARCHAR2,
1835 p_parent_object_id IN NUMBER,
1836 p_parent_object_code IN VARCHAR2,
1837 p_quote_id IN NUMBER,
1838 x_return_status OUT NOCOPY VARCHAR2,
1839 x_msg_count OUT NOCOPY NUMBER,
1840 x_msg_data OUT NOCOPY VARCHAR2) IS
1841
1842 l_program_name CONSTANT VARCHAR2(30) := 'handle_leaseapp_update';
1843 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
1844
1845 CURSOR c_get_leaseapp_pool_values IS
1846 SELECT SUB.SUBSIDY_POOL_ID SUBSIDY_POOL_ID,
1847 QUOTE.ID QUOTE_ID
1848 FROM OKL_COST_ADJUSTMENTS_B ADJ,
1849 OKL_SUBSIDIES_B SUB,
1850 OKL_SUBSIDY_POOLS_B SUB_POOL,
1851 OKL_ASSETS_B ASSET,
1852 OKL_LEASE_QUOTES_B QUOTE
1853 WHERE ADJ.ADJUSTMENT_SOURCE_TYPE = 'SUBSIDY'
1854 AND ADJ.PARENT_OBJECT_CODE = 'ASSET'
1855 AND ADJ.ADJUSTMENT_SOURCE_ID = SUB.ID
1856 AND SUB.SUBSIDY_POOL_ID = SUB_POOL.ID
1857 AND ADJ.PARENT_OBJECT_ID = ASSET.ID
1858 AND ASSET.PARENT_OBJECT_CODE = 'LEASEQUOTE'
1859 AND ASSET.PARENT_OBJECT_ID = QUOTE.ID
1860 AND QUOTE.ID = p_quote_id
1861 GROUP BY SUB.SUBSIDY_POOL_ID, QUOTE.ID;
1862
1863 CURSOR c_get_quote_subpool_usage(l_subsidy_pool_id IN NUMBER, l_qte_id IN NUMBER) IS
1864 SELECT SOURCE_TYPE_CODE,
1865 SOURCE_OBJECT_ID,
1866 ASSET_NUMBER,
1867 ASSET_START_DATE,
1868 SUBSIDY_POOL_ID,
1869 SUBSIDY_POOL_AMOUNT,
1870 SUBSIDY_POOL_CURRENCY_CODE,
1871 SUBSIDY_ID,
1872 SUBSIDY_AMOUNT,
1873 SUBSIDY_CURRENCY_CODE,
1874 VENDOR_ID,
1875 CONVERSION_RATE
1876 FROM OKL_QUOTE_SUBPOOL_USAGE
1877 WHERE SUBSIDY_POOL_ID = l_subsidy_pool_id
1878 AND SOURCE_OBJECT_ID = l_qte_id;
1879
1880 ln_count NUMBER;
1881 lv_leaseopp_quote VARCHAR2(1);
1882 l_quote_id NUMBER;
1883 i NUMBER :=0;
1884 l_lap_max_subsidy_amount NUMBER;
1885 l_lop_max_subsidy_amount NUMBER;
1886 l_lop_max_usage_qte_id NUMBER;
1887 l_lap_max_usage_qte_id NUMBER;
1888 lb_is_this_max_amount BOOLEAN;
1889 lv_linked_lop_id NUMBER;
1890 lv_fresh_leaseapp VARCHAR2(3);
1891 ln_max_subsidy_amount NUMBER;
1892 l_nxt_max_qte_id NUMBER;
1893 lv_inital_record_exists VARCHAR2(3);
1894 l_max_usage_qte_id NUMBER;
1895 l_other_usage BOOLEAN;
1896
1897 l_sub_pool_tbl subsidy_pool_tbl_type;
1898 lx_subpool_tbl subsidy_pool_tbl_type;
1899
1900 BEGIN
1901
1902 l_quote_id := p_quote_id;
1903
1904 -- Check if this quote exists in the pool, if so add it back and check for
1905 -- the next highest impact
1906 FOR l_get_leaseapp_pool_values IN c_get_leaseapp_pool_values LOOP
1907
1908 lv_inital_record_exists := check_initial_record(p_object_id => l_get_leaseapp_pool_values.quote_id,
1909 p_source_object_code => p_parent_object_code,
1910 p_subsidy_pool_id => l_get_leaseapp_pool_values.subsidy_pool_id,
1911 x_return_status => x_return_status);
1912
1913 -- dbms_output.put_line('This Quote Exists '|| lv_inital_record_exists);
1914 IF (lv_inital_record_exists = 'Y') THEN
1915
1916 i := 0;
1917 -- Fetch the data from Quote Subsidy pool usage
1918 l_sub_pool_tbl.delete;
1919 FOR l_get_quote_subpool_usage IN c_get_quote_subpool_usage(l_get_leaseapp_pool_values.subsidy_pool_id, l_quote_id) LOOP
1920 l_sub_pool_tbl(i).source_type_code := l_get_quote_subpool_usage.source_type_code;
1921 l_sub_pool_tbl(i).source_object_id := l_get_quote_subpool_usage.source_object_id;
1922 l_sub_pool_tbl(i).dnz_asset_number := l_get_quote_subpool_usage.asset_number;
1923 l_sub_pool_tbl(i).source_trx_date := l_get_quote_subpool_usage.asset_start_date;
1924 l_sub_pool_tbl(i).subsidy_pool_id := l_get_quote_subpool_usage.subsidy_pool_id ;
1925 l_sub_pool_tbl(i).subsidy_pool_amount := l_get_quote_subpool_usage.subsidy_pool_amount;
1926 l_sub_pool_tbl(i).subsidy_pool_currency_code := l_get_quote_subpool_usage.subsidy_pool_currency_code;
1927 l_sub_pool_tbl(i).subsidy_id := l_get_quote_subpool_usage.subsidy_id;
1928 l_sub_pool_tbl(i).trx_amount := l_get_quote_subpool_usage.subsidy_amount;
1929 l_sub_pool_tbl(i).trx_currency_code := l_get_quote_subpool_usage.subsidy_currency_code;
1930 l_sub_pool_tbl(i).vendor_id := l_get_quote_subpool_usage.vendor_id;
1931 l_sub_pool_tbl(i).conversion_rate := l_get_quote_subpool_usage.conversion_rate;
1932
1933 i := i + 1;
1934 END LOOP;
1935
1936 IF l_sub_pool_tbl.COUNT > 0 THEN
1937 FOR j IN l_sub_pool_tbl.FIRST .. l_sub_pool_tbl.LAST LOOP
1938 IF l_sub_pool_tbl.EXISTS(j) THEN
1939 l_sub_pool_tbl(j).trx_type_code := 'ADDITION';
1940 l_sub_pool_tbl(j).trx_reason_code := p_transaction_reason;
1941 END IF;
1942 END LOOP;
1943 END IF;
1944
1945 -- Add the previous balance to the Subsidy pool
1946 IF (l_sub_pool_tbl.COUNT > 0) THEN
1947 okl_subsidy_pool_trx_pvt.create_pool_transaction
1948 (p_api_version => p_api_version,
1949 p_init_msg_list => p_init_msg_list,
1950 x_return_status => x_return_status,
1951 x_msg_count => x_msg_count,
1952 x_msg_data => x_msg_data,
1953 p_sixv_tbl => l_sub_pool_tbl,
1954 x_sixv_tbl => lx_subpool_tbl);
1955 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1956 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1957 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1958 RAISE OKL_API.G_EXCEPTION_ERROR;
1959 END IF;
1960 END IF;
1961
1962 -- Delete the Quote usage data for this Subsidy pool
1963 delete_quote_subpool_usage(p_api_version => p_api_version,
1964 p_init_msg_list => p_init_msg_list,
1965 x_return_status => x_return_status,
1966 x_msg_count => x_msg_count,
1967 x_msg_data => x_msg_data,
1968 p_subsidy_pool_id => l_get_leaseapp_pool_values.subsidy_pool_id,
1969 p_source_object_id => l_quote_id);
1970 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1971 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1972 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1973 RAISE OKL_API.G_EXCEPTION_ERROR;
1974 END IF;
1975
1976 --if the Lease Application is created from a Quote
1977 IF (lv_leaseopp_quote = 'Y') THEN
1978 --check the Approved quotes under the linked lease opportunity and get the maximum subsidy
1979 -- pool usage
1980 get_linked_lop_maxsp_usage( p_subsidy_pool_id => l_get_leaseapp_pool_values.subsidy_pool_id
1981 ,p_lop_id => lv_linked_lop_id
1982 ,p_quote_id => l_quote_id
1983 ,x_max_usage_qte_id => l_lop_max_usage_qte_id
1984 ,x_max_usage_amt => l_lop_max_subsidy_amount
1985 ,x_return_Status => x_return_Status
1986 );
1987 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1988 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1989 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1990 RAISE OKL_API.G_EXCEPTION_ERROR;
1991 END IF;
1992 ELSE
1993 l_lop_max_usage_qte_id := null;
1994 l_lop_max_subsidy_amount := null;
1995 END IF;
1996
1997 --check the Accepted quote and the Credit Recommended counter offers under the linked lease applications
1998 --and get the maximum subsidy pool usage
1999 get_linked_lap_maxsp_usage( p_subsidy_pool_id => l_get_leaseapp_pool_values.subsidy_pool_id
2000 ,p_lap_id => p_parent_object_id
2001 ,p_current_qte_id => l_quote_id
2002 ,p_transaction => p_transaction_reason
2003 ,x_max_usage_qte_id => l_lap_max_usage_qte_id
2004 ,x_max_usage_amt => l_lap_max_subsidy_amount
2005 ,x_return_Status => x_return_Status );
2006
2007 -- dbms_output.put_line('##l_lap_max_usage_qte_id '|| l_lap_max_usage_qte_id);
2008 -- dbms_output.put_line('##l_lap_max_subsidy_amount '||l_lap_max_subsidy_amount);
2009 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2010 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2011 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2012 RAISE OKL_API.G_EXCEPTION_ERROR;
2013 END IF;
2014 --debug_proc('l_lap_max_usage_qte_id = '||l_lap_max_usage_qte_id);
2015 --debug_proc('l_lap_max_subsidy_amount = '||l_lap_max_subsidy_amount);
2016
2017 l_other_usage := true;
2018
2019 -- get the maximum of the subsidy pool usage among approved quotes of linked
2020 --lop, accepted quote and Credit Recommendation offers of linked laps and this
2021 --quote/offer of the lap
2022 IF l_lap_max_usage_qte_id IS NOT NULL AND l_lop_max_usage_qte_id IS NOT NULL THEN
2023 IF l_lap_max_subsidy_amount > l_lop_max_subsidy_amount THEN
2024 ln_max_subsidy_amount := l_lap_max_subsidy_amount;
2025 l_max_usage_qte_id := l_lap_max_usage_qte_id;
2026 ELSE
2027 ln_max_subsidy_amount := l_lop_max_subsidy_amount;
2028 l_max_usage_qte_id := l_lop_max_usage_qte_id;
2029 END IF;
2030 ELSIF l_lap_max_usage_qte_id IS NOT NULL THEN
2031 ln_max_subsidy_amount := l_lap_max_subsidy_amount;
2032 l_max_usage_qte_id := l_lap_max_usage_qte_id;
2033 ELSIF l_lop_max_usage_qte_id IS NOT NULL THEN
2034 ln_max_subsidy_amount := l_lop_max_subsidy_amount;
2035 l_max_usage_qte_id := l_lop_max_usage_qte_id;
2036 ELSE
2037 l_other_usage := false;
2038 END IF;
2039
2040 IF (l_other_usage) THEN
2041 create_subpool_trx_and_usage(p_api_version => p_api_version,
2042 p_init_msg_list => p_init_msg_list,
2043 x_return_status => x_return_status,
2044 x_msg_count => x_msg_count,
2045 x_msg_data => x_msg_data,
2046 p_source_object_code => p_parent_object_code,
2047 p_quote_id => l_max_usage_qte_id,
2048 p_subsidy_pool_id => l_get_leaseapp_pool_values.subsidy_pool_id,
2049 p_transaction_reason => 'APPROVE_QUOTE'); -- Fix for bug 4997538
2050 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2051 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2052 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2053 RAISE OKL_API.G_EXCEPTION_ERROR;
2054 END IF;
2055 END IF;
2056 END IF;
2057 END LOOP;
2058
2059 x_return_status := G_RET_STS_SUCCESS;
2060 EXCEPTION
2061
2062 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2063
2064 x_return_status := G_RET_STS_ERROR;
2065
2066 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2067
2068 x_return_status := G_RET_STS_UNEXP_ERROR;
2069
2070 WHEN OTHERS THEN
2071
2072 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
2073 p_msg_name => G_DB_ERROR,
2074 p_token1 => G_PROG_NAME_TOKEN,
2075 p_token1_value => l_api_name,
2076 p_token2 => G_SQLCODE_TOKEN,
2077 p_token2_value => sqlcode,
2078 p_token3 => G_SQLERRM_TOKEN,
2079 p_token3_value => sqlerrm);
2080
2081 x_return_status := G_RET_STS_UNEXP_ERROR;
2082
2083 END handle_leaseapp_update;
2084 ----------------------------------------
2085 -- PROCEDURE handle_leaseapp_price_offer
2086 ----------------------------------------
2087 PROCEDURE handle_leaseapp_price_offer (p_api_version IN NUMBER,
2088 p_init_msg_list IN VARCHAR2,
2089 p_transaction_reason IN VARCHAR2,
2090 p_parent_object_id IN NUMBER,
2091 p_parent_object_code IN VARCHAR2,
2092 x_return_status OUT NOCOPY VARCHAR2,
2093 x_msg_count OUT NOCOPY NUMBER,
2094 x_msg_data OUT NOCOPY VARCHAR2) IS
2095
2096 l_program_name CONSTANT VARCHAR2(30) := 'handle_leaseapp_price_offer';
2097 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
2098
2099 CURSOR c_get_leaseapp_pool_values IS
2100 SELECT SUB.SUBSIDY_POOL_ID SUBSIDY_POOL_ID,
2101 QUOTE.ID QUOTE_ID
2102 FROM OKL_COST_ADJUSTMENTS_B ADJ,
2103 OKL_SUBSIDIES_B SUB,
2104 OKL_SUBSIDY_POOLS_B SUB_POOL,
2105 OKL_ASSETS_B ASSET,
2106 OKL_LEASE_QUOTES_B QUOTE
2107 WHERE ADJ.ADJUSTMENT_SOURCE_TYPE = 'SUBSIDY'
2108 AND ADJ.PARENT_OBJECT_CODE = 'ASSET'
2109 AND ADJ.ADJUSTMENT_SOURCE_ID = SUB.ID
2110 AND SUB.SUBSIDY_POOL_ID = SUB_POOL.ID
2111 AND ADJ.PARENT_OBJECT_ID = ASSET.ID
2112 AND ASSET.PARENT_OBJECT_CODE = 'LEASEQUOTE'
2113 AND ASSET.PARENT_OBJECT_ID = QUOTE.ID
2114 AND QUOTE.PARENT_OBJECT_ID = p_parent_object_id
2115 GROUP BY SUB.SUBSIDY_POOL_ID, QUOTE.ID;
2116
2117 ln_count NUMBER;
2118 lv_leaseopp_quote VARCHAR2(1);
2119
2120 l_sub_pool_tbl subsidy_pool_tbl_type;
2121 lx_subpool_tbl subsidy_pool_tbl_type;
2122
2123 BEGIN
2124 -- Check if the Lease Application is created from a Quote
2125 lv_leaseopp_quote := check_leaseopp_quote (p_lease_app_id => p_parent_object_id,
2126 x_return_status => x_return_status);
2127
2128 IF (lv_leaseopp_quote = 'N') THEN -- Standalone Lease application
2129
2130 FOR l_get_leaseapp_pool_values IN c_get_leaseapp_pool_values LOOP
2131 -- Fetch the data from Quote Subsidy pool usage
2132 fetch_quote_subpool_usage(p_subsidy_pool_id => l_get_leaseapp_pool_values.subsidy_pool_id,
2133 p_quote_id => l_get_leaseapp_pool_values.quote_id,
2134 x_subpool_tbl => l_sub_pool_tbl,
2135 x_return_status => x_return_status);
2136
2137 FOR i IN l_sub_pool_tbl.FIRST .. l_sub_pool_tbl.LAST LOOP
2138 IF l_sub_pool_tbl.EXISTS(i) THEN
2139 l_sub_pool_tbl(i).trx_type_code := 'ADDITION';
2140 l_sub_pool_tbl(i).trx_reason_code := p_transaction_reason;
2141 END IF;
2142 END LOOP;
2143
2144 -- Add the previous balance to the Subsidy pool
2145 IF (l_sub_pool_tbl.COUNT > 0) THEN
2146 okl_subsidy_pool_trx_pvt.create_pool_transaction
2147 (p_api_version => p_api_version,
2148 p_init_msg_list => p_init_msg_list,
2149 x_return_status => x_return_status,
2150 x_msg_count => x_msg_count,
2151 x_msg_data => x_msg_data,
2152 p_sixv_tbl => l_sub_pool_tbl,
2153 x_sixv_tbl => lx_subpool_tbl);
2154 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2155 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2156 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2157 RAISE OKL_API.G_EXCEPTION_ERROR;
2158 END IF;
2159 END IF;
2160
2161 -- Delete the Quote usage data for this Subsidy pool
2162 delete_quote_subpool_usage(p_api_version => p_api_version,
2163 p_init_msg_list => p_init_msg_list,
2164 x_return_status => x_return_status,
2165 x_msg_count => x_msg_count,
2166 x_msg_data => x_msg_data,
2167 p_subsidy_pool_id => l_get_leaseapp_pool_values.subsidy_pool_id,
2168 p_source_object_id => l_get_leaseapp_pool_values.quote_id);
2169 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2170 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2171 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2172 RAISE OKL_API.G_EXCEPTION_ERROR;
2173 END IF;
2174 END LOOP;
2175 END IF;
2176
2177 x_return_status := G_RET_STS_SUCCESS;
2178 EXCEPTION
2179
2180 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2181
2182 x_return_status := G_RET_STS_ERROR;
2183
2184 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2185
2186 x_return_status := G_RET_STS_UNEXP_ERROR;
2187
2188 WHEN OTHERS THEN
2189
2190 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
2191 p_msg_name => G_DB_ERROR,
2192 p_token1 => G_PROG_NAME_TOKEN,
2193 p_token1_value => l_api_name,
2194 p_token2 => G_SQLCODE_TOKEN,
2195 p_token2_value => sqlcode,
2196 p_token3 => G_SQLERRM_TOKEN,
2197 p_token3_value => sqlerrm);
2198
2199 x_return_status := G_RET_STS_UNEXP_ERROR;
2200
2201 END handle_leaseapp_price_offer;
2202
2203 ----------------------------------------
2204 -- PROCEDURE handle_withdraw_leaseapp
2205 ----------------------------------------
2206 PROCEDURE handle_withdraw_leaseapp (p_api_version IN NUMBER,
2207 p_init_msg_list IN VARCHAR2,
2208 p_transaction_reason IN VARCHAR2,
2209 p_parent_object_id IN NUMBER,
2210 p_parent_object_code IN VARCHAR2,
2211 p_quote_id IN NUMBER,
2212 x_return_status OUT NOCOPY VARCHAR2,
2213 x_msg_count OUT NOCOPY NUMBER,
2214 x_msg_data OUT NOCOPY VARCHAR2) IS
2215
2216 l_program_name CONSTANT VARCHAR2(30) := 'handle_withdraw_leaseapp';
2217 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
2218
2219 CURSOR c_get_quotesandoffers_in_lap(p_lease_app_id IN NUMBER) IS
2220 SELECT QUOTE.ID QUOTE_ID
2221 FROM OKL_LEASE_QUOTES_V QUOTE
2222 WHERE QUOTE.STATUS IN ( 'PR-APPROVED','CT-ACCEPTED','CR-RECOMMENDATION')
2223 AND QUOTE.PARENT_OBJECT_CODE = 'LEASEAPP'
2224 AND QUOTE.PARENT_OBJECT_ID = p_lease_app_id;
2225
2226 CURSOR c_get_leaseapp_pool_values(p_lap_quote_id IN NUMBER) IS
2227 SELECT SUB.SUBSIDY_POOL_ID SUBSIDY_POOL_ID,
2228 QUOTE.ID QUOTE_ID
2229 FROM OKL_COST_ADJUSTMENTS_B ADJ,
2230 OKL_SUBSIDIES_B SUB,
2231 OKL_SUBSIDY_POOLS_B SUB_POOL,
2232 OKL_ASSETS_B ASSET,
2233 OKL_LEASE_QUOTES_B QUOTE
2234 WHERE ADJ.ADJUSTMENT_SOURCE_TYPE = 'SUBSIDY'
2235 AND ADJ.PARENT_OBJECT_CODE = 'ASSET'
2236 AND ADJ.ADJUSTMENT_SOURCE_ID = SUB.ID
2237 AND SUB.SUBSIDY_POOL_ID = SUB_POOL.ID
2238 AND ADJ.PARENT_OBJECT_ID = ASSET.ID
2239 AND ASSET.PARENT_OBJECT_CODE = 'LEASEQUOTE'
2240 AND ASSET.PARENT_OBJECT_ID = QUOTE.ID
2241 AND QUOTE.ID = p_lap_quote_id
2242 GROUP BY SUB.SUBSIDY_POOL_ID, QUOTE.ID;
2243
2244 CURSOR c_get_quote_subpool_usage(l_subsidy_pool_id IN NUMBER, l_qte_id IN NUMBER) IS
2245 SELECT SOURCE_TYPE_CODE,
2246 SOURCE_OBJECT_ID,
2247 ASSET_NUMBER,
2248 ASSET_START_DATE,
2249 SUBSIDY_POOL_ID,
2250 SUBSIDY_POOL_AMOUNT,
2251 SUBSIDY_POOL_CURRENCY_CODE,
2252 SUBSIDY_ID,
2253 SUBSIDY_AMOUNT,
2254 SUBSIDY_CURRENCY_CODE,
2255 VENDOR_ID,
2256 CONVERSION_RATE
2257 FROM OKL_QUOTE_SUBPOOL_USAGE
2258 WHERE SUBSIDY_POOL_ID = l_subsidy_pool_id
2259 AND SOURCE_OBJECT_ID = l_qte_id;
2260
2261 ln_count NUMBER;
2262 lv_leaseopp_quote VARCHAR2(1);
2263 l_quote_id NUMBER;
2264 i NUMBER :=0;
2265 l_lap_max_subsidy_amount NUMBER;
2266 l_lop_max_subsidy_amount NUMBER;
2267 l_lop_max_usage_qte_id NUMBER;
2268 l_lap_max_usage_qte_id NUMBER;
2269 lb_is_this_max_amount BOOLEAN;
2270 lv_linked_lop_id NUMBER;
2271 lv_fresh_leaseapp VARCHAR2(3);
2272 ln_max_subsidy_amount NUMBER;
2273 l_nxt_max_qte_id NUMBER;
2274 lv_inital_record_exists VARCHAR2(3);
2275 l_max_usage_qte_id NUMBER;
2276 l_other_usage BOOLEAN;
2277
2278
2279 ln_leaseopp_quote_id NUMBER;
2280 ln_max_subpool_amount NUMBER;
2281 ln_next_max_subpool_amount NUMBER;
2282 ln_next_max_sp_amount_quote_id NUMBER;
2283
2284 l_sub_pool_tbl subsidy_pool_tbl_type;
2285 lx_subpool_tbl subsidy_pool_tbl_type;
2286
2287 BEGIN
2288
2289 -- dbms_output.put_line('Started .. '||p_parent_object_id);
2290
2291 FOR l_lap_qtes_offers IN c_get_quotesandoffers_in_lap(p_parent_object_id) LOOP
2292 -- dbms_output.put_line('First loop .. '||l_lap_qtes_offers.quote_id);
2293 FOR l_get_leaseapp_pool_values IN c_get_leaseapp_pool_values(p_lap_quote_id => l_lap_qtes_offers.quote_id) LOOP
2294 -- dbms_output.put_line('Second loop .. ');
2295 lv_inital_record_exists := check_initial_record(p_object_id => l_get_leaseapp_pool_values.quote_id,
2296 p_source_object_code => p_parent_object_code,
2297 p_subsidy_pool_id => l_get_leaseapp_pool_values.subsidy_pool_id,
2298 x_return_status => x_return_status);
2299
2300 -- dbms_output.put_line('This Quote Exists '|| lv_inital_record_exists);
2301 IF (lv_inital_record_exists = 'Y') THEN
2302 -- Fetch the data from Quote Subsidy pool usage
2303 l_sub_pool_tbl.delete;
2304 fetch_quote_subpool_usage(p_subsidy_pool_id => l_get_leaseapp_pool_values.subsidy_pool_id,
2305 p_quote_id => l_get_leaseapp_pool_values.quote_id,
2306 x_subpool_tbl => l_sub_pool_tbl,
2307 x_return_status => x_return_status);
2308
2309 FOR i IN l_sub_pool_tbl.FIRST .. l_sub_pool_tbl.LAST LOOP
2310 IF l_sub_pool_tbl.EXISTS(i) THEN
2311 l_sub_pool_tbl(i).trx_type_code := 'ADDITION';
2312 l_sub_pool_tbl(i).trx_reason_code := p_transaction_reason;
2313 END IF;
2314 END LOOP;
2315
2316 -- Add the previous balance to the Subsidy pool
2317 IF (l_sub_pool_tbl.COUNT > 0) THEN
2318 okl_subsidy_pool_trx_pvt.create_pool_transaction
2319 (p_api_version => p_api_version,
2320 p_init_msg_list => p_init_msg_list,
2321 x_return_status => x_return_status,
2322 x_msg_count => x_msg_count,
2323 x_msg_data => x_msg_data,
2324 p_sixv_tbl => l_sub_pool_tbl,
2325 x_sixv_tbl => lx_subpool_tbl);
2326 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2327 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2328 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2329 RAISE OKL_API.G_EXCEPTION_ERROR;
2330 END IF;
2331 END IF;
2332
2333 -- Delete the Quote usage data for this Subsidy pool
2334 delete_quote_subpool_usage(p_api_version => p_api_version,
2335 p_init_msg_list => p_init_msg_list,
2336 x_return_status => x_return_status,
2337 x_msg_count => x_msg_count,
2338 x_msg_data => x_msg_data,
2339 p_subsidy_pool_id => l_get_leaseapp_pool_values.subsidy_pool_id,
2340 p_source_object_id => l_get_leaseapp_pool_values.quote_id);
2341 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2342 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2343 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2344 RAISE OKL_API.G_EXCEPTION_ERROR;
2345 END IF;
2346
2347 -- Check if the Lease Application is created from a Quote
2348 lv_leaseopp_quote := check_leaseopp_quote (p_lease_app_id => p_parent_object_id,
2349 x_return_status => x_return_status);
2350
2351 --if the Lease Application is created from a Quote
2352 IF (lv_leaseopp_quote = 'Y') THEN
2353 --check the Approved quotes under the linked lease opportunity and get the maximum subsidy
2354 -- pool usage
2355 get_linked_lop_maxsp_usage( p_subsidy_pool_id => l_get_leaseapp_pool_values.subsidy_pool_id
2356 ,p_lop_id => lv_linked_lop_id
2357 ,p_quote_id => l_quote_id
2358 ,x_max_usage_qte_id => l_lop_max_usage_qte_id
2359 ,x_max_usage_amt => l_lop_max_subsidy_amount
2360 ,x_return_Status => x_return_Status
2361 );
2362 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2363 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2364 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2365 RAISE OKL_API.G_EXCEPTION_ERROR;
2366 END IF;
2367 ELSE
2368 l_lop_max_usage_qte_id := null;
2369 l_lop_max_subsidy_amount := null;
2370 END IF;
2371 --check the Accepted quote and the Credit Recommended counter offers under the linked lease applications
2372 --and get the maximum subsidy pool usage
2373 get_linked_lap_maxsp_usage( p_subsidy_pool_id => l_get_leaseapp_pool_values.subsidy_pool_id
2374 ,p_lap_id => p_parent_object_id
2375 ,p_current_qte_id => null
2376 ,p_transaction => p_transaction_reason
2377 ,x_max_usage_qte_id => l_lap_max_usage_qte_id
2378 ,x_max_usage_amt => l_lap_max_subsidy_amount
2379 ,x_return_Status => x_return_Status
2380 );
2381 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2382 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2383 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2384 RAISE OKL_API.G_EXCEPTION_ERROR;
2385 END IF;
2386 l_other_usage := true;
2387 -- dbms_output.put_line('Next Highest Impact Quote '||l_lap_max_usage_qte_id);
2388 -- dbms_output.put_line('Next Highest Impact Amount '||l_lap_max_subsidy_amount);
2389 -- get the maximum of the subsidy pool usage among approved quotes of linked
2390 --lop, accepted quote and Credit Recommendation offers of linked laps and this
2391 --quote/offer of the lap
2392 IF l_lap_max_usage_qte_id IS NOT NULL AND l_lop_max_usage_qte_id IS NOT NULL THEN
2393 IF l_lap_max_subsidy_amount > l_lop_max_subsidy_amount THEN
2394 ln_max_subsidy_amount := l_lap_max_subsidy_amount;
2395 l_max_usage_qte_id := l_lap_max_usage_qte_id;
2396 ELSE
2397 ln_max_subsidy_amount := l_lop_max_subsidy_amount;
2398 l_max_usage_qte_id := l_lop_max_usage_qte_id;
2399 END IF;
2400 ELSIF l_lap_max_usage_qte_id IS NOT NULL THEN
2401 ln_max_subsidy_amount := l_lap_max_subsidy_amount;
2402 l_max_usage_qte_id := l_lap_max_usage_qte_id;
2403 ELSIF l_lop_max_usage_qte_id IS NOT NULL THEN
2404 ln_max_subsidy_amount := l_lop_max_subsidy_amount;
2405 l_max_usage_qte_id := l_lop_max_usage_qte_id;
2406 ELSE
2407 l_other_usage := false;
2408 END IF;
2409
2410 IF (l_other_usage) THEN
2411 -- create the transaction record
2412 -- dbms_output.put_line('Checked for next highest impact ');
2413
2414 -- Deduct the new impact amount from the pool balance
2415 create_subpool_trx_and_usage(p_api_version => p_api_version,
2416 p_init_msg_list => p_init_msg_list,
2417 x_return_status => x_return_status,
2418 x_msg_count => x_msg_count,
2419 x_msg_data => x_msg_data,
2420 p_source_object_code => p_parent_object_code,
2421 p_quote_id => l_max_usage_qte_id,
2422 p_subsidy_pool_id => l_get_leaseapp_pool_values.subsidy_pool_id,
2423 p_transaction_reason => 'APPROVE_QUOTE'); -- Fix for bug 4997538
2424 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2425 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2426 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2427 RAISE OKL_API.G_EXCEPTION_ERROR;
2428 END IF;
2429 END IF;
2430 END IF;
2431 END LOOP;
2432 END LOOP;
2433 x_return_status := G_RET_STS_SUCCESS;
2434 EXCEPTION
2435
2436 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2437
2438 x_return_status := G_RET_STS_ERROR;
2439
2440 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2441
2442 x_return_status := G_RET_STS_UNEXP_ERROR;
2443
2444 WHEN OTHERS THEN
2445
2446 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
2447 p_msg_name => G_DB_ERROR,
2448 p_token1 => G_PROG_NAME_TOKEN,
2449 p_token1_value => l_api_name,
2450 p_token2 => G_SQLCODE_TOKEN,
2451 p_token2_value => sqlcode,
2452 p_token3 => G_SQLERRM_TOKEN,
2453 p_token3_value => sqlerrm);
2454
2455 x_return_status := G_RET_STS_UNEXP_ERROR;
2456
2457 END handle_withdraw_leaseapp;
2458
2459 ----------------------------------------
2460 -- PROCEDURE handle_approved_quote_update
2461 ----------------------------------------
2462 PROCEDURE handle_approved_quote_update (p_api_version IN NUMBER,
2463 p_init_msg_list IN VARCHAR2,
2464 p_quote_id IN NUMBER,
2465 p_transaction_reason IN VARCHAR2,
2466 p_parent_object_id IN NUMBER,
2467 p_parent_object_code IN VARCHAR2,
2468 x_return_status OUT NOCOPY VARCHAR2,
2469 x_msg_count OUT NOCOPY NUMBER,
2470 x_msg_data OUT NOCOPY VARCHAR2) IS
2471
2472 l_program_name CONSTANT VARCHAR2(30) := 'handle_approved_quote_update';
2473 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
2474
2475 CURSOR c_get_leaseopp_max_pool_values(p_top_object_id IN NUMBER,
2476 p_quote_id IN NUMBER,
2477 p_subsidy_pool_id IN NUMBER ) IS
2478 SELECT QUOTE_ID,
2479 SUBSIDY_POOL_ID,
2480 MAX(AMOUNT) AMOUNT
2481 FROM
2482 (SELECT SUB.SUBSIDY_POOL_ID SUBSIDY_POOL_ID,
2483 SUM(DECODE(ADJ.VALUE, NULL, ADJ.DEFAULT_SUBSIDY_AMOUNT, ADJ.VALUE)) AMOUNT,
2484 QUOTE.ID QUOTE_ID
2485 FROM OKL_COST_ADJUSTMENTS_B ADJ,
2486 OKL_SUBSIDIES_B SUB,
2487 OKL_SUBSIDY_POOLS_B SUB_POOL,
2488 OKL_ASSETS_B ASSET,
2489 OKL_LEASE_QUOTES_B QUOTE
2490 WHERE ADJ.ADJUSTMENT_SOURCE_TYPE = 'SUBSIDY'
2491 AND ADJ.PARENT_OBJECT_CODE = 'ASSET'
2492 AND ADJ.ADJUSTMENT_SOURCE_ID = SUB.ID
2493 AND SUB.SUBSIDY_POOL_ID = SUB_POOL.ID
2494 AND ADJ.PARENT_OBJECT_ID = ASSET.ID
2495 AND ASSET.PARENT_OBJECT_CODE = 'LEASEQUOTE'
2496 AND ASSET.PARENT_OBJECT_ID = QUOTE.ID
2497 AND QUOTE.STATUS = 'PR-APPROVED'
2498 AND QUOTE.PARENT_OBJECT_ID = p_top_object_id
2499 AND QUOTE.ID <> p_quote_id
2500 AND SUB_POOL.ID = p_subsidy_pool_id
2501 GROUP BY SUB.SUBSIDY_POOL_ID, QUOTE.ID)
2502 WHERE (SUBSIDY_POOL_ID, AMOUNT)
2503 IN
2504 (SELECT SUBSIDY_POOL_ID,
2505 MAX(AMOUNT) AMOUNT
2506 FROM
2507 (SELECT SUB.SUBSIDY_POOL_ID SUBSIDY_POOL_ID,
2508 SUM(DECODE(ADJ.VALUE, NULL, ADJ.DEFAULT_SUBSIDY_AMOUNT, ADJ.VALUE)) AMOUNT,
2509 QUOTE.ID QUOTE_ID
2510 FROM OKL_COST_ADJUSTMENTS_B ADJ,
2511 OKL_SUBSIDIES_B SUB,
2512 OKL_SUBSIDY_POOLS_B SUB_POOL,
2513 OKL_ASSETS_B ASSET,
2514 OKL_LEASE_QUOTES_B QUOTE
2515 WHERE ADJ.ADJUSTMENT_SOURCE_TYPE = 'SUBSIDY'
2516 AND ADJ.PARENT_OBJECT_CODE = 'ASSET'
2517 AND ADJ.ADJUSTMENT_SOURCE_ID = SUB.ID
2518 AND SUB.SUBSIDY_POOL_ID = SUB_POOL.ID
2519 AND ADJ.PARENT_OBJECT_ID = ASSET.ID
2520 AND ASSET.PARENT_OBJECT_CODE = 'LEASEQUOTE'
2521 AND ASSET.PARENT_OBJECT_ID = QUOTE.ID
2522 AND QUOTE.STATUS = 'PR-APPROVED'
2523 AND QUOTE.PARENT_OBJECT_ID = p_top_object_id
2524 AND QUOTE.ID <> p_quote_id
2525 AND SUB_POOL.ID = p_subsidy_pool_id
2526 GROUP BY SUB.SUBSIDY_POOL_ID, QUOTE.ID)
2527 GROUP BY SUBSIDY_POOL_ID)
2528 GROUP BY SUBSIDY_POOL_ID, QUOTE_ID;
2529
2530 CURSOR c_get_quote_pool_values IS
2531 SELECT SUB.SUBSIDY_POOL_ID SUBSIDY_POOL_ID,
2532 SUM(DECODE(ADJ.VALUE, NULL, ADJ.DEFAULT_SUBSIDY_AMOUNT, ADJ.VALUE)) AMOUNT
2533 FROM OKL_COST_ADJUSTMENTS_B ADJ,
2534 OKL_SUBSIDIES_B SUB,
2535 OKL_SUBSIDY_POOLS_B SUB_POOL,
2536 OKL_ASSETS_B ASSET,
2537 OKL_LEASE_QUOTES_B QUOTE
2538 WHERE ADJ.ADJUSTMENT_SOURCE_TYPE = 'SUBSIDY'
2539 AND ADJ.PARENT_OBJECT_CODE = 'ASSET'
2540 AND ADJ.ADJUSTMENT_SOURCE_ID = SUB.ID
2541 AND SUB.SUBSIDY_POOL_ID = SUB_POOL.ID
2542 AND ADJ.PARENT_OBJECT_ID = ASSET.ID
2543 AND ASSET.PARENT_OBJECT_CODE = 'LEASEQUOTE'
2544 AND ASSET.PARENT_OBJECT_ID = QUOTE.ID
2545 AND QUOTE.STATUS = 'PR-INCOMPLETE'
2546 AND QUOTE.ID = p_quote_id
2547 GROUP BY SUB.SUBSIDY_POOL_ID, QUOTE.ID;
2548
2549 CURSOR c_get_quote_subpool_usage(l_subsidy_pool_id IN NUMBER) IS
2550 SELECT SOURCE_TYPE_CODE,
2551 SOURCE_OBJECT_ID,
2552 ASSET_NUMBER,
2553 ASSET_START_DATE,
2554 SUBSIDY_POOL_ID,
2555 SUBSIDY_POOL_AMOUNT,
2556 SUBSIDY_POOL_CURRENCY_CODE,
2557 SUBSIDY_ID,
2558 SUBSIDY_AMOUNT,
2559 SUBSIDY_CURRENCY_CODE,
2560 VENDOR_ID,
2561 CONVERSION_RATE
2562 FROM OKL_QUOTE_SUBPOOL_USAGE
2563 WHERE SUBSIDY_POOL_ID = l_subsidy_pool_id
2564 AND SOURCE_OBJECT_ID = p_quote_id;
2565
2566
2567 ln_next_max_subsidy_amount NUMBER;
2568 ln_next_max_sp_amount_quote_id NUMBER;
2569 ln_count NUMBER;
2570 lb_first_value BOOLEAN;
2571 lv_status_code VARCHAR2(30);
2572 lv_reference_number VARCHAR2(150);
2573 i NUMBER := 0;
2574
2575 l_sub_pool_tbl subsidy_pool_tbl_type;
2576 lx_subpool_tbl subsidy_pool_tbl_type;
2577
2578 BEGIN
2579
2580 SELECT REFERENCE_NUMBER, STATUS
2581 INTO lv_reference_number, lv_status_code
2582 FROM OKL_LEASE_QUOTES_B
2583 WHERE ID = p_quote_id;
2584 --debug_proc('Quote: '||lv_reference_number);
2585
2586 IF (lv_status_code = 'PR-APPROVED') THEN
2587 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
2588 p_msg_name => 'OKL_INVALID_SUBPOOL_TRANS',
2589 p_token1 => 'EVENT',
2590 p_token1_value => p_transaction_reason,
2591 p_token3 => 'OBJECT_NAME',
2592 p_token3_value => lv_reference_number);
2593 RAISE OKL_API.G_EXCEPTION_ERROR;
2594 END IF;
2595
2596 FOR l_get_quote_pool_values IN c_get_quote_pool_values LOOP
2597 --debug_proc(' l_get_quote_pool_values.subsidy_pool_id = '||l_get_quote_pool_values.subsidy_pool_id);
2598 --debug_proc(' l_get_quote_pool_values.amount = '||l_get_quote_pool_values.amount);
2599 SELECT COUNT(*) -- Check if the Subsidy pool exists in the usage table
2600 INTO ln_count
2601 FROM OKL_QUOTE_SUBPOOL_USAGE
2602 WHERE SUBSIDY_POOL_ID = l_get_quote_pool_values.subsidy_pool_id
2603 AND SOURCE_OBJECT_ID = p_quote_id;
2604 --debug_proc('ln_count = '||ln_count);
2605 IF (ln_count > 0) THEN -- The Subsidy pool is highest for this Quote
2606
2607 -- Fetch the data from Quote Subsidy pool usage
2608 i := 0;
2609 l_sub_pool_tbl.delete;
2610 FOR l_get_quote_subpool_usage IN c_get_quote_subpool_usage(l_get_quote_pool_values.subsidy_pool_id) LOOP
2611 l_sub_pool_tbl(i).source_type_code := l_get_quote_subpool_usage.source_type_code;
2612 l_sub_pool_tbl(i).source_object_id := l_get_quote_subpool_usage.source_object_id;
2613 l_sub_pool_tbl(i).dnz_asset_number := l_get_quote_subpool_usage.asset_number;
2614 l_sub_pool_tbl(i).source_trx_date := l_get_quote_subpool_usage.asset_start_date;
2615 l_sub_pool_tbl(i).subsidy_pool_id := l_get_quote_subpool_usage.subsidy_pool_id ;
2616 l_sub_pool_tbl(i).subsidy_pool_amount := l_get_quote_subpool_usage.subsidy_pool_amount;
2617 l_sub_pool_tbl(i).subsidy_pool_currency_code := l_get_quote_subpool_usage.subsidy_pool_currency_code;
2618 l_sub_pool_tbl(i).subsidy_id := l_get_quote_subpool_usage.subsidy_id;
2619 l_sub_pool_tbl(i).trx_amount := l_get_quote_subpool_usage.subsidy_amount;
2620 l_sub_pool_tbl(i).trx_currency_code := l_get_quote_subpool_usage.subsidy_currency_code;
2621 l_sub_pool_tbl(i).vendor_id := l_get_quote_subpool_usage.vendor_id;
2622 l_sub_pool_tbl(i).conversion_rate := l_get_quote_subpool_usage.conversion_rate;
2623 --debug_proc('l_sub_pool_tbl(i).dnz_asset_number '||l_sub_pool_tbl(i).dnz_asset_number);
2624 --debug_proc('l_sub_pool_tbl(i).trx_amount '||l_sub_pool_tbl(i).trx_amount);
2625 --debug_proc('l_sub_pool_tbl(i).subsidy_pool_id '||l_sub_pool_tbl(i).subsidy_pool_id);
2626 i := i + 1;
2627 END LOOP;
2628 --debug_proc('l_sub_pool_tbl.count = '||l_sub_pool_tbl.count);
2629
2630 FOR j IN l_sub_pool_tbl.FIRST .. l_sub_pool_tbl.LAST LOOP
2631 IF l_sub_pool_tbl.EXISTS(j) THEN
2632 --debug_proc('j := '||j);
2633 l_sub_pool_tbl(j).trx_type_code := 'ADDITION';
2634 l_sub_pool_tbl(j).trx_reason_code := p_transaction_reason;
2635 --debug_proc('l_sub_pool_tbl(i).dnz_asset_number '||l_sub_pool_tbl(j).dnz_asset_number);
2636 --debug_proc('l_sub_pool_tbl(i).trx_amount '||l_sub_pool_tbl(j).trx_amount);
2637 --debug_proc('l_sub_pool_tbl(i).subsidy_pool_id '||l_sub_pool_tbl(j).subsidy_pool_id);
2638
2639 END IF;
2640 END LOOP;
2641
2642 -- Add the previous balance to the Subsidy pool
2643 IF (l_sub_pool_tbl.COUNT > 0) THEN
2644 okl_subsidy_pool_trx_pvt.create_pool_transaction
2645 (p_api_version => p_api_version,
2646 p_init_msg_list => p_init_msg_list,
2647 x_return_status => x_return_status,
2648 x_msg_count => x_msg_count,
2649 x_msg_data => x_msg_data,
2650 p_sixv_tbl => l_sub_pool_tbl,
2651 x_sixv_tbl => lx_subpool_tbl);
2652 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2653 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2654 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2655 RAISE OKL_API.G_EXCEPTION_ERROR;
2656 END IF;
2657 END IF;
2658
2659 -- Delete the Quote usage data for this Subsidy pool
2660 delete_quote_subpool_usage(p_api_version => p_api_version,
2661 p_init_msg_list => p_init_msg_list,
2662 x_return_status => x_return_status,
2663 x_msg_count => x_msg_count,
2664 x_msg_data => x_msg_data,
2665 p_subsidy_pool_id => l_get_quote_pool_values.subsidy_pool_id,
2666 p_source_object_id => p_quote_id);
2667 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2668 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2669 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2670 RAISE OKL_API.G_EXCEPTION_ERROR;
2671 END IF;
2672
2673 -- Fetch the new highest subsidy pool impact for the 'APPROVED' lease
2674 -- quotes other than the present quote
2675 lb_first_value := TRUE;
2676 FOR l_get_leaseopp_max_pool_values IN c_get_leaseopp_max_pool_values(p_top_object_id => p_parent_object_id,
2677 p_quote_id => p_quote_id,
2678 p_subsidy_pool_id => l_get_quote_pool_values.subsidy_pool_id ) LOOP
2679 IF (lb_first_value) THEN
2680 ln_next_max_subsidy_amount := l_get_leaseopp_max_pool_values.amount;
2681 ln_next_max_sp_amount_quote_id := l_get_leaseopp_max_pool_values.quote_id;
2682 lb_first_value := FALSE;
2683 END IF;
2684
2685 IF (l_get_leaseopp_max_pool_values.amount > ln_next_max_subsidy_amount) THEN
2686 ln_next_max_subsidy_amount := l_get_leaseopp_max_pool_values.amount;
2687 ln_next_max_sp_amount_quote_id := l_get_leaseopp_max_pool_values.quote_id;
2688 END IF;
2689 END LOOP;
2690
2691 IF (NOT lb_first_value) THEN
2692 -- New value found, so create the transaction record
2693 -- Deduct the new impact amount from the pool balance
2694 create_subpool_trx_and_usage(p_api_version => p_api_version,
2695 p_init_msg_list => p_init_msg_list,
2696 x_return_status => x_return_status,
2697 x_msg_count => x_msg_count,
2698 x_msg_data => x_msg_data,
2699 p_source_object_code => p_parent_object_code,
2700 p_quote_id => ln_next_max_sp_amount_quote_id,
2701 p_subsidy_pool_id => l_get_quote_pool_values.subsidy_pool_id,
2702 p_transaction_reason => 'APPROVE_QUOTE'); -- Fix for bug 4997538
2703 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2704 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2705 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2706 RAISE OKL_API.G_EXCEPTION_ERROR;
2707 END IF;
2708 END IF;
2709
2710 END IF;
2711
2712 END LOOP;
2713
2714 x_return_status := G_RET_STS_SUCCESS;
2715 EXCEPTION
2716
2717 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2718
2719 x_return_status := G_RET_STS_ERROR;
2720
2721 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2722
2723 x_return_status := G_RET_STS_UNEXP_ERROR;
2724
2725 WHEN OTHERS THEN
2726
2727 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
2728 p_msg_name => G_DB_ERROR,
2729 p_token1 => G_PROG_NAME_TOKEN,
2730 p_token1_value => l_api_name,
2731 p_token2 => G_SQLCODE_TOKEN,
2732 p_token2_value => sqlcode,
2733 p_token3 => G_SQLERRM_TOKEN,
2734 p_token3_value => sqlerrm);
2735
2736 x_return_status := G_RET_STS_UNEXP_ERROR;
2737
2738 END handle_approved_quote_update;
2739
2740 ------------------------------------
2741 -- PROCEDURE process_quote_subsidy_pool
2742 ------------------------------------
2743 PROCEDURE process_quote_subsidy_pool(p_api_version IN NUMBER,
2744 p_init_msg_list IN VARCHAR2,
2745 p_transaction_control IN VARCHAR2,
2746 p_quote_id IN NUMBER,
2747 p_transaction_reason IN VARCHAR2,
2748 x_return_status OUT NOCOPY VARCHAR2,
2749 x_msg_count OUT NOCOPY NUMBER,
2750 x_msg_data OUT NOCOPY VARCHAR2) IS
2751
2752 l_program_name CONSTANT VARCHAR2(30) := 'process_quote_subsidy_pool';
2753 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
2754
2755 lv_parent_object_code VARCHAR2(30);
2756 ln_parent_object_id NUMBER;
2757
2758 CURSOR c_get_parent_object_info IS
2759 SELECT parent_object_id, parent_object_code
2760 FROM okl_lease_quotes_b
2761 WHERE id = p_quote_id;
2762
2763 BEGIN
2764
2765 IF p_transaction_control = G_TRUE THEN
2766 SAVEPOINT l_program_name;
2767 END IF;
2768
2769 IF p_init_msg_list = G_TRUE THEN
2770 FND_MSG_PUB.initialize;
2771 END IF;
2772
2773 OPEN c_get_parent_object_info;
2774 FETCH c_get_parent_object_info into ln_parent_object_id, lv_parent_object_code;
2775 CLOSE c_get_parent_object_info;
2776
2777 IF (p_transaction_reason = 'APPROVE_QUOTE') THEN -- Transaction --> 'APPROVE_QUOTE'
2778 handle_quote_pools (p_api_version => p_api_version,
2779 p_init_msg_list => p_init_msg_list,
2780 p_quote_id => p_quote_id,
2781 p_transaction_reason => p_transaction_reason,
2782 p_parent_object_id => ln_parent_object_id,
2783 p_parent_object_code => lv_parent_object_code,
2784 x_return_status => x_return_status,
2785 x_msg_count => x_msg_count,
2786 x_msg_data => x_msg_data);
2787 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2788 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2789 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2790 RAISE OKL_API.G_EXCEPTION_ERROR;
2791 END IF;
2792 ELSIF (p_transaction_reason = 'UPDATE_APPROVED_QUOTE') THEN -- OR p_transaction_reason = 'EXPIRE_QUOTE')
2793 -- Transaction --> 'UPDATE_APPROVED_QUOTE', 'EXPIRE_QUOTE'
2794 --debug_proc('UPDATE_APPROVED_QUOTE p_quote_id ='||p_quote_id);
2795 handle_approved_quote_update (p_api_version => p_api_version,
2796 p_init_msg_list => p_init_msg_list,
2797 p_quote_id => p_quote_id,
2798 p_transaction_reason => p_transaction_reason,
2799 p_parent_object_id => ln_parent_object_id,
2800 p_parent_object_code => lv_parent_object_code,
2801 x_return_status => x_return_status,
2802 x_msg_count => x_msg_count,
2803 x_msg_data => x_msg_data);
2804 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2805 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2806 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2807 RAISE OKL_API.G_EXCEPTION_ERROR;
2808 END IF;
2809 END IF;
2810
2811 x_return_status := G_RET_STS_SUCCESS;
2812
2813 EXCEPTION
2814
2815 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2816
2817 IF p_transaction_control = G_TRUE THEN
2818 ROLLBACK TO l_program_name;
2819 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
2820 END IF;
2821
2822 x_return_status := G_RET_STS_ERROR;
2823
2824 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2825
2826 IF p_transaction_control = G_TRUE THEN
2827 ROLLBACK TO l_program_name;
2828 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
2829 END IF;
2830
2831 x_return_status := G_RET_STS_UNEXP_ERROR;
2832
2833 WHEN OTHERS THEN
2834
2835 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
2836 p_msg_name => G_DB_ERROR,
2837 p_token1 => G_PROG_NAME_TOKEN,
2838 p_token1_value => l_api_name,
2839 p_token2 => G_SQLCODE_TOKEN,
2840 p_token2_value => sqlcode,
2841 p_token3 => G_SQLERRM_TOKEN,
2842 p_token3_value => sqlerrm);
2843
2844 IF p_transaction_control = G_TRUE THEN
2845 ROLLBACK TO l_program_name;
2846 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
2847 END IF;
2848
2849 x_return_status := G_RET_STS_UNEXP_ERROR;
2850
2851 END process_quote_subsidy_pool;
2852
2853 ------------------------------------------
2854 -- PROCEDURE process_leaseapp_subsidy_pool
2855 ------------------------------------------
2856 PROCEDURE process_leaseapp_subsidy_pool(p_api_version IN NUMBER,
2857 p_init_msg_list IN VARCHAR2,
2858 p_transaction_control IN VARCHAR2,
2859 p_leaseapp_id IN NUMBER,
2860 p_transaction_reason IN VARCHAR2,
2861 p_quote_id IN NUMBER,
2862 x_return_status OUT NOCOPY VARCHAR2,
2863 x_msg_count OUT NOCOPY NUMBER,
2864 x_msg_data OUT NOCOPY VARCHAR2) IS
2865
2866 l_program_name CONSTANT VARCHAR2(30) := 'process_leaseapp_subsidy_pool';
2867 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
2868
2869
2870 BEGIN
2871
2872 IF p_transaction_control = G_TRUE THEN
2873 SAVEPOINT l_program_name;
2874 END IF;
2875
2876 IF p_init_msg_list = G_TRUE THEN
2877 FND_MSG_PUB.initialize;
2878 END IF;
2879
2880 IF (p_transaction_reason = 'APPROVE_LEASE_APP_PRICING'
2881 OR p_transaction_reason = 'APPROVE_LEASE_APP_PRIC_OFFER') THEN -- Transaction --> 'APPROVE_LEASE_APP_PRICING'
2882 handle_leaseapp_pool (p_api_version => p_api_version,
2883 p_init_msg_list => p_init_msg_list,
2884 p_transaction_reason => p_transaction_reason,
2885 p_parent_object_id => p_leaseapp_id,
2886 p_quote_id => p_quote_id,
2887 p_parent_object_code => 'LEASEAPP',
2888 x_return_status => x_return_status,
2889 x_msg_count => x_msg_count,
2890 x_msg_data => x_msg_data);
2891 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2892 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2893 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2894 RAISE OKL_API.G_EXCEPTION_ERROR;
2895 END IF;
2896 ELSIF (p_transaction_reason = 'UPDATE_LEASE_APP') THEN -- Transaction --> 'UPDATE_LEASE_APP'
2897 handle_leaseapp_update (p_api_version => p_api_version,
2898 p_init_msg_list => p_init_msg_list,
2899 p_transaction_reason => p_transaction_reason,
2900 p_parent_object_id => p_leaseapp_id,
2901 p_parent_object_code => 'LEASEAPP',
2902 p_quote_id => p_quote_id,
2903 x_return_status => x_return_status,
2904 x_msg_count => x_msg_count,
2905 x_msg_data => x_msg_data);
2906 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2907 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2908 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2909 RAISE OKL_API.G_EXCEPTION_ERROR;
2910 END IF;
2911 /* ELSIF (p_transaction_reason = 'APPROVE_LEASE_APP_PRIC_OFFER') THEN -- Transaction --> 'APPROVE_LEASE_APP_PRIC_OFFER'
2912 handle_leaseapp_price_offer (p_api_version => p_api_version,
2913 p_init_msg_list => p_init_msg_list,
2914 p_transaction_reason => p_transaction_reason,
2915 p_parent_object_id => p_leaseapp_id,
2916 p_parent_object_code => 'LEASEAPP',
2917 x_return_status => x_return_status,
2918 x_msg_count => x_msg_count,
2919 x_msg_data => x_msg_data);
2920 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2921 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2922 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2923 RAISE OKL_API.G_EXCEPTION_ERROR;
2924 END IF;
2925 */
2926 ELSIF (p_transaction_reason = 'WITHDRAW_LEASE_APP') THEN -- Transaction --> 'WITHDRAW_LEASE_APP'
2927 handle_withdraw_leaseapp (p_api_version => p_api_version,
2928 p_init_msg_list => p_init_msg_list,
2929 p_transaction_reason => p_transaction_reason,
2930 p_parent_object_id => p_leaseapp_id,
2931 p_parent_object_code => 'LEASEAPP',
2932 p_quote_id => p_quote_id,
2933 x_return_status => x_return_status,
2934 x_msg_count => x_msg_count,
2935 x_msg_data => x_msg_data);
2936 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2937 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2938 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2939 RAISE OKL_API.G_EXCEPTION_ERROR;
2940 END IF;
2941 ELSIF (p_transaction_reason = 'CANCEL_LEASE_APP') THEN -- Transaction --> 'CANCEL_LEASE_APP'
2942 handle_withdraw_leaseapp (p_api_version => p_api_version,
2943 p_init_msg_list => p_init_msg_list,
2944 p_transaction_reason => p_transaction_reason,
2945 p_parent_object_id => p_leaseapp_id,
2946 p_parent_object_code => 'LEASEAPP',
2947 p_quote_id => p_quote_id,
2948 x_return_status => x_return_status,
2949 x_msg_count => x_msg_count,
2950 x_msg_data => x_msg_data);
2951 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2952 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2953 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2954 RAISE OKL_API.G_EXCEPTION_ERROR;
2955 END IF;
2956 END IF;
2957
2958 x_return_status := G_RET_STS_SUCCESS;
2959
2960 EXCEPTION
2961
2962 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2963
2964 IF p_transaction_control = G_TRUE THEN
2965 ROLLBACK TO l_program_name;
2966 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
2967 END IF;
2968
2969 x_return_status := G_RET_STS_ERROR;
2970
2971 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2972
2973 IF p_transaction_control = G_TRUE THEN
2974 ROLLBACK TO l_program_name;
2975 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
2976 END IF;
2977
2978 x_return_status := G_RET_STS_UNEXP_ERROR;
2979
2980 WHEN OTHERS THEN
2981
2982 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
2983 p_msg_name => G_DB_ERROR,
2984 p_token1 => G_PROG_NAME_TOKEN,
2985 p_token1_value => l_api_name,
2986 p_token2 => G_SQLCODE_TOKEN,
2987 p_token2_value => sqlcode,
2988 p_token3 => G_SQLERRM_TOKEN,
2989 p_token3_value => sqlerrm);
2990
2991 IF p_transaction_control = G_TRUE THEN
2992 ROLLBACK TO l_program_name;
2993 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
2994 END IF;
2995
2996 x_return_status := G_RET_STS_UNEXP_ERROR;
2997
2998 END process_leaseapp_subsidy_pool;
2999
3000 ------------------------------------
3001 -- PROCEDURE process_active_contract
3002 ------------------------------------
3003 PROCEDURE process_active_contract (p_api_version IN NUMBER,
3004 p_init_msg_list IN VARCHAR2,
3005 p_transaction_control IN VARCHAR2,
3006 p_contract_id IN NUMBER,
3007 x_return_status OUT NOCOPY VARCHAR2,
3008 x_msg_count OUT NOCOPY NUMBER,
3009 x_msg_data OUT NOCOPY VARCHAR2) IS
3010
3011 l_program_name CONSTANT VARCHAR2(30) := 'process_active_contract';
3012 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
3013
3014 BEGIN
3015 IF p_transaction_control = G_TRUE THEN
3016 SAVEPOINT l_program_name;
3017 END IF;
3018
3019 IF p_init_msg_list = G_TRUE THEN
3020 FND_MSG_PUB.initialize;
3021 END IF;
3022
3023 handle_active_contract (p_api_version => p_api_version,
3024 p_init_msg_list => p_init_msg_list,
3025 p_transaction_reason => 'ACTIVATE_CONTRACT',
3026 p_contract_id => p_contract_id,
3027 x_return_status => x_return_status,
3028 x_msg_count => x_msg_count,
3029 x_msg_data => x_msg_data);
3030 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3031 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3032 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
3033 RAISE OKL_API.G_EXCEPTION_ERROR;
3034 END IF;
3035
3036 x_return_status := G_RET_STS_SUCCESS;
3037
3038 EXCEPTION
3039
3040 WHEN OKL_API.G_EXCEPTION_ERROR THEN
3041
3042 IF p_transaction_control = G_TRUE THEN
3043 ROLLBACK TO l_program_name;
3044 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
3045 END IF;
3046
3047 x_return_status := G_RET_STS_ERROR;
3048
3049 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3050
3051 IF p_transaction_control = G_TRUE THEN
3052 ROLLBACK TO l_program_name;
3053 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
3054 END IF;
3055
3056 x_return_status := G_RET_STS_UNEXP_ERROR;
3057
3058 WHEN OTHERS THEN
3059
3060 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
3061 p_msg_name => G_DB_ERROR,
3062 p_token1 => G_PROG_NAME_TOKEN,
3063 p_token1_value => l_api_name,
3064 p_token2 => G_SQLCODE_TOKEN,
3065 p_token2_value => sqlcode,
3066 p_token3 => G_SQLERRM_TOKEN,
3067 p_token3_value => sqlerrm);
3068
3069 IF p_transaction_control = G_TRUE THEN
3070 ROLLBACK TO l_program_name;
3071 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
3072 END IF;
3073
3074 x_return_status := G_RET_STS_UNEXP_ERROR;
3075
3076 END process_active_contract;
3077
3078
3079 ------------------------------------
3080 -- PROCEDURE process_cancel_leaseopp
3081 ------------------------------------
3082 PROCEDURE process_cancel_leaseopp (p_api_version IN NUMBER,
3083 p_init_msg_list IN VARCHAR2,
3084 p_transaction_control IN VARCHAR2,
3085 p_parent_object_id IN NUMBER,
3086 x_return_status OUT NOCOPY VARCHAR2,
3087 x_msg_count OUT NOCOPY NUMBER,
3088 x_msg_data OUT NOCOPY VARCHAR2) IS
3089
3090 l_program_name CONSTANT VARCHAR2(30) := 'process_cancel_leaseopp';
3091 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
3092
3093 BEGIN
3094 IF p_transaction_control = G_TRUE THEN
3095 SAVEPOINT l_program_name;
3096 END IF;
3097
3098 IF p_init_msg_list = G_TRUE THEN
3099 FND_MSG_PUB.initialize;
3100 END IF;
3101
3102 handle_cancel_leaseopp (p_api_version => p_api_version,
3103 p_init_msg_list => p_init_msg_list,
3104 p_transaction_reason => 'CANCEL_LEASE_OPP',
3105 p_parent_object_id => p_parent_object_id,
3106 x_return_status => x_return_status,
3107 x_msg_count => x_msg_count,
3108 x_msg_data => x_msg_data);
3109 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3110 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3111 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
3112 RAISE OKL_API.G_EXCEPTION_ERROR;
3113 END IF;
3114
3115 x_return_status := G_RET_STS_SUCCESS;
3116
3117 EXCEPTION
3118
3119 WHEN OKL_API.G_EXCEPTION_ERROR THEN
3120
3121 IF p_transaction_control = G_TRUE THEN
3122 ROLLBACK TO l_program_name;
3123 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
3124 END IF;
3125
3126 x_return_status := G_RET_STS_ERROR;
3127
3128 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3129
3130 IF p_transaction_control = G_TRUE THEN
3131 ROLLBACK TO l_program_name;
3132 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
3133 END IF;
3134
3135 x_return_status := G_RET_STS_UNEXP_ERROR;
3136
3137 WHEN OTHERS THEN
3138
3139 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
3140 p_msg_name => G_DB_ERROR,
3141 p_token1 => G_PROG_NAME_TOKEN,
3142 p_token1_value => l_api_name,
3143 p_token2 => G_SQLCODE_TOKEN,
3144 p_token2_value => sqlcode,
3145 p_token3 => G_SQLERRM_TOKEN,
3146 p_token3_value => sqlerrm);
3147
3148 IF p_transaction_control = G_TRUE THEN
3149 ROLLBACK TO l_program_name;
3150 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
3151 END IF;
3152
3153 x_return_status := G_RET_STS_UNEXP_ERROR;
3154
3155 END process_cancel_leaseopp;
3156
3157 END OKL_LEASE_QUOTE_SUBPOOL_PVT;