[Home] [Help]
PACKAGE BODY: APPS.OKL_LA_TRADEIN_PVT
Source
1 PACKAGE BODY OKL_LA_TRADEIN_PVT as
2 /* $Header: OKLRTRIB.pls 120.3 2006/09/11 23:21:42 smereddy noship $ */
3
4 --Bug# 5102606
5 G_FORMULA_CAP CONSTANT VARCHAR2(200) := 'LINE_CAP_AMNT';
6
7 FUNCTION GET_AK_PROMPT(p_ak_region IN VARCHAR2, p_ak_attribute IN VARCHAR2)
8 RETURN VARCHAR2 IS
9
10 CURSOR ak_prompt_csr(p_ak_region VARCHAR2, p_ak_attribute VARCHAR2) IS
11 SELECT a.attribute_label_long
12 FROM ak_region_items ri, AK_REGIONS r, AK_ATTRIBUTES_vL a
13 WHERE ri.region_code = r.region_code
14 AND ri.attribute_code = a.attribute_code
15 AND ri.region_code = p_ak_region
16 AND ri.attribute_code = p_ak_attribute;
17
18 l_ak_prompt AK_ATTRIBUTES_VL.attribute_label_long%TYPE;
19 BEGIN
20 OPEN ak_prompt_csr(p_ak_region, p_ak_attribute);
21 FETCH ak_prompt_csr INTO l_ak_prompt;
22 CLOSE ak_prompt_csr;
23 return(l_ak_prompt);
24 END;
25
26 PROCEDURE delete_quote_lines (p_api_version IN NUMBER,
27 p_init_msg_list IN VARCHAR2 DEFAULT G_FALSE,
28 p_transaction_control IN VARCHAR2 DEFAULT G_TRUE,
29 p_cle_id_tbl IN cle_id_tbl_type,
30 x_return_status OUT NOCOPY VARCHAR2,
31 x_msg_count OUT NOCOPY NUMBER,
32 x_msg_data OUT NOCOPY VARCHAR2) IS
33
34 l_program_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||'delete_quote_lines';
35 lx_return_status VARCHAR2(1);
36
37 l_chr_id NUMBER;
38 line_number NUMBER;
39 -- cursor to retrieve the chr_id( contract id) to set the org
40 CURSOR find_chr_id_csr(p_line_id NUMBER) IS
41 SELECT dnz_chr_id chr_id
42 FROM okc_k_lines_b
43 WHERE id = p_line_id;
44
45 BEGIN
46
47 IF p_transaction_control = G_TRUE THEN
48 SAVEPOINT l_program_name;
49 END IF;
50
51 IF p_init_msg_list = G_TRUE THEN
52 FND_MSG_PUB.initialize;
53 END IF;
54
55 -- pass the quote line id (service, fee, covered assets) to delete the line
56 IF(p_cle_id_tbl.COUNT > 0) THEN
57
58 line_number := p_cle_id_tbl.FIRST;
59
60 -- Retrieve the chr_id
61 FOR l_chr_id_csr_rec IN find_chr_id_csr(p_line_id => p_cle_id_tbl(line_number).cle_id)
62 LOOP
63 l_chr_id := l_chr_id_csr_rec.chr_id;
64 END LOOP;
65 -- set the org context
66 OKL_CONTEXT.set_okc_org_context(p_chr_id => l_chr_id );
67 -- run the loop
68 LOOP
69
70 okl_contract_pvt.delete_contract_line(p_api_version => G_API_VERSION,
71 p_init_msg_list => G_FALSE,
72 x_return_status => lx_return_status,
73 x_msg_count => x_msg_count,
74 x_msg_data => x_msg_data,
75 p_line_id => p_cle_id_tbl(line_number).cle_id);
76
77 IF (lx_return_status = G_RET_STS_UNEXP_ERROR) THEN
78 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
79 ELSIF (lx_return_status = G_RET_STS_ERROR) THEN
80 RAISE OKL_API.G_EXCEPTION_ERROR;
81 END IF;
82
83 EXIT WHEN (line_number = p_cle_id_tbl.LAST);
84 line_number := p_cle_id_tbl.NEXT(line_number);
85 END LOOP;
86
87 END IF;
88
89 x_return_status := G_RET_STS_SUCCESS;
90
91 EXCEPTION
92
93 WHEN OKL_API.G_EXCEPTION_ERROR THEN
94
95 IF p_transaction_control = G_TRUE THEN
96 ROLLBACK TO l_program_name;
97 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
98 END IF;
99
100 x_return_status := G_RET_STS_ERROR;
101
102 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
103
104 IF p_transaction_control = G_TRUE THEN
105 ROLLBACK TO l_program_name;
106 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
107 END IF;
108
109 x_return_status := G_RET_STS_UNEXP_ERROR;
110
111 WHEN OTHERS THEN
112
113 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
114 p_msg_name => G_DB_ERROR,
115 p_token1 => G_PROG_NAME_TOKEN,
116 p_token1_value => l_program_name,
117 p_token2 => G_SQLCODE_TOKEN,
118 p_token2_value => sqlcode,
119 p_token3 => G_SQLERRM_TOKEN,
120 p_token3_value => sqlerrm);
121
122 IF p_transaction_control = G_TRUE THEN
123 ROLLBACK TO l_program_name;
124 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
125 END IF;
126
127 x_return_status := G_RET_STS_UNEXP_ERROR;
128
129
130 END delete_quote_lines;
131
132
133 PROCEDURE create_update_link_assets (p_cle_id IN NUMBER,
134 p_chr_id IN NUMBER,
135 p_capitalize_yn IN VARCHAR2,
136 p_link_asset_tbl IN link_asset_tbl_type,
137 p_derive_assoc_amt IN VARCHAR2,
138 x_return_status OUT NOCOPY VARCHAR2,
139 x_msg_count OUT NOCOPY NUMBER,
140 x_msg_data OUT NOCOPY VARCHAR2) IS
141
142 l_program_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||'create_update_link_assets';
143
144 l_create_line_item_tbl okl_contract_line_item_pvt.line_item_tbl_type;
145 l_update_line_item_tbl okl_contract_line_item_pvt.line_item_tbl_type;
146 lx_line_item_tbl okl_contract_line_item_pvt.line_item_tbl_type;
147
148 l_link_asset_tbl link_asset_tbl_type;
149
150 k BINARY_INTEGER := 1; -- create table index
151 m BINARY_INTEGER := 1; -- update table index
152
153 l_line_amount NUMBER;
154 l_asset_oec NUMBER;
155 l_oec_total NUMBER := 0;
156 l_assoc_amount NUMBER;
157 l_assoc_total NUMBER := 0;
158 l_currency_code VARCHAR2(15);
159 l_compare_amt NUMBER;
160 l_diff NUMBER;
161 l_adj_rec BINARY_INTEGER;
162 lx_return_status VARCHAR2(1);
163
164 BEGIN
165
166 SELECT NVL(amount, 0)
167 INTO l_line_amount
168 FROM okl_k_lines
169 WHERE id = p_cle_id;
170
171 SELECT currency_code
172 INTO l_currency_code
173 FROM okc_k_headers_b
174 WHERE id = p_chr_id;
175
176 l_link_asset_tbl := p_link_asset_tbl;
177
178 IF (l_link_asset_tbl.COUNT > 0) THEN
179
180 ------------------------------------------------------------------
181 -- 1. Loop through to get OEC total of all assets being associated
182 ------------------------------------------------------------------
183 FOR i IN l_link_asset_tbl.FIRST .. l_link_asset_tbl.LAST LOOP
184
185 IF l_link_asset_tbl.EXISTS(i) THEN
186
187 SELECT NVL(oec, 0)
188 INTO l_asset_oec
189 FROM okl_k_lines
190 WHERE id = l_link_asset_tbl(i).fin_asset_id;
191
192 l_oec_total := l_oec_total + l_asset_oec;
193
194 END IF;
195
196 END LOOP;
197
198 ----------------------------------------------------------------------------
199 -- 2. Loop through to determine associated amounts and round off the amounts
200 ----------------------------------------------------------------------------
201 FOR i IN l_link_asset_tbl.FIRST .. l_link_asset_tbl.LAST LOOP
202
203 IF l_link_asset_tbl.EXISTS(i) THEN
204
205 IF p_derive_assoc_amt = 'N' THEN
206
207 l_assoc_amount := l_link_asset_tbl(i).amount;
208
209 ELSIF l_oec_total = 0 THEN
210
211 l_assoc_amount := l_line_amount / l_link_asset_tbl.COUNT;
212
213 ELSE
214
215 -- LLA APIs ensure asset OEC and line amount are rounded
216
217 SELECT NVL(oec, 0)
218 INTO l_asset_oec
219 FROM okl_k_lines
220 WHERE id = l_link_asset_tbl(i).fin_asset_id;
221
222 IF l_link_asset_tbl.COUNT = 1 THEN
223
224 l_assoc_amount := l_line_amount;
225
226 ELSE
227
228 l_assoc_amount := l_line_amount * l_asset_oec / l_oec_total;
229
230 END IF;
231 END IF;
232
233 l_assoc_amount := okl_accounting_util.round_amount(p_amount => l_assoc_amount,
234 p_currency_code => l_currency_code);
235
236 l_assoc_total := l_assoc_total + l_assoc_amount;
237
238 l_link_asset_tbl(i).amount := l_assoc_amount;
239 END IF;
240
241 END LOOP;
242
243 ----------------------------------------------------------------------------------------------------
244 -- 3. Adjust associated amount if associated total does not tally up with line amount after rounding
245 ----------------------------------------------------------------------------------------------------
246 IF l_assoc_total <> l_line_amount THEN
247
248 l_diff := ABS(l_assoc_total - l_line_amount);
249
250 FOR i IN l_link_asset_tbl.FIRST .. l_link_asset_tbl.LAST LOOP
251
252 IF l_link_asset_tbl.EXISTS(i) THEN
253
254 -- if the total split amount is less than line amount add the difference amount to the
255 -- asset with less amount and if the total split amount is greater than the line amount
256 -- than subtract the difference amount from the asset with highest amount
257
258 IF i = l_link_asset_tbl.FIRST THEN
259
260 l_adj_rec := i; -- Bug#3404844
261 l_compare_amt := l_link_asset_tbl(i).amount;
262
263 ELSIF (l_assoc_total < l_line_amount) AND (l_link_asset_tbl(i).amount <= l_compare_amt) OR
264 (l_assoc_total > l_line_amount) AND (l_link_asset_tbl(i).amount >= l_compare_amt) THEN
265
266 l_adj_rec := i;
267 l_compare_amt := l_link_asset_tbl(i).amount;
268
269 END IF;
270
271 END IF;
272
273 END LOOP;
274
275 IF l_assoc_total < l_line_amount THEN
276
277 l_link_asset_tbl(l_adj_rec).amount := l_link_asset_tbl(l_adj_rec).amount + l_diff;
278
279 ELSE
280
281 l_link_asset_tbl(l_adj_rec).amount := l_link_asset_tbl(l_adj_rec).amount - l_diff;
282
283 END IF;
284
285 END IF;
286
287 ------------------------------------------------------
288 -- 4. Prepare arrays to pass to create and update APIs
289 ------------------------------------------------------
290 FOR i IN l_link_asset_tbl.FIRST .. l_link_asset_tbl.LAST LOOP
291
292 IF l_link_asset_tbl.EXISTS(i) THEN
293
294 l_assoc_amount := l_link_asset_tbl(i).amount;
295
296 IF l_link_asset_tbl(i).link_line_id IS NULL THEN
297
298 l_create_line_item_tbl(k).chr_id := p_chr_id;
299 l_create_line_item_tbl(k).parent_cle_id := p_cle_id;
300 l_create_line_item_tbl(k).item_id1 := l_link_asset_tbl(i).fin_asset_id;
301 l_create_line_item_tbl(k).item_id2 := '#';
302 l_create_line_item_tbl(k).item_object1_code := 'OKX_COVASST';
303 l_create_line_item_tbl(k).serv_cov_prd_id := NULL;
304
305 -- The linked amount is always passed in as 'capital_amount' even though capital amount
306 -- is applicable only for CAPITALIZED fee types. The LLA API will ensure that
307 -- the linked amount is stored in the appropriate column (AMOUNT vs CAPITAL_AMOUNT)
308 l_create_line_item_tbl(k).capital_amount := l_assoc_amount;
309
310 SELECT txl.asset_number
311 INTO l_create_line_item_tbl(k).name
312 FROM okc_k_lines_b cle,
313 okc_line_styles_b lse,
314 okl_txl_assets_b txl
315 WHERE cle.id = txl.kle_id
316 AND cle.lse_id = lse.id
317 AND lse.lty_code = 'FIXED_ASSET'
318 AND cle.cle_id = l_link_asset_tbl(i).fin_asset_id;
319
320 k := k + 1;
321
322 ELSE
323
324 l_update_line_item_tbl(m).cle_id := l_link_asset_tbl(i).link_line_id;
325 l_update_line_item_tbl(m).item_id := l_link_asset_tbl(i).link_item_id;
326 l_update_line_item_tbl(m).chr_id := p_chr_id;
327 l_update_line_item_tbl(m).parent_cle_id := p_cle_id;
328 l_update_line_item_tbl(m).item_id1 := l_link_asset_tbl(i).fin_asset_id;
329 l_update_line_item_tbl(m).item_id2 := '#';
330 l_update_line_item_tbl(m).item_object1_code := 'OKX_COVASST';
331 l_update_line_item_tbl(m).serv_cov_prd_id := NULL;
332
333 -- The linked amount is always passed in as 'capital_amount' even though capital amount
334 -- is applicable only for CAPITALIZED fee types. The LLA API will ensure that
335 -- the linked amount is stored in the appropriate column (AMOUNT vs CAPITAL_AMOUNT)
336 l_update_line_item_tbl(m).capital_amount := l_assoc_amount;
337
338 SELECT txl.asset_number
339 INTO l_update_line_item_tbl(m).name
340 FROM okc_k_lines_b cle,
341 okc_line_styles_b lse,
342 okl_txl_assets_b txl
343 WHERE cle.id = txl.kle_id
344 AND cle.lse_id = lse.id
345 AND lse.lty_code = 'FIXED_ASSET'
346 AND cle.cle_id = l_link_asset_tbl(i).fin_asset_id;
347
348 m := m + 1;
349
350 END IF;
351
352 END IF;
353
354 END LOOP;
355
356 IF l_create_line_item_tbl.COUNT > 0 THEN
357
358 okl_contract_line_item_pvt.create_contract_line_item( p_api_version => G_API_VERSION,
359 p_init_msg_list => G_FALSE,
360 x_return_status => lx_return_status,
361 x_msg_count => x_msg_count,
362 x_msg_data => x_msg_data,
363 p_line_item_tbl => l_create_line_item_tbl,
364 x_line_item_tbl => lx_line_item_tbl);
365
366 IF lx_return_status = G_RET_STS_UNEXP_ERROR THEN
367 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
368 ELSIF lx_return_status = G_RET_STS_ERROR THEN
369 RAISE OKL_API.G_EXCEPTION_ERROR;
370 END IF;
371
372 END IF;
373
374 IF l_update_line_item_tbl.COUNT > 0 THEN
375
376 okl_contract_line_item_pvt.update_contract_line_item( p_api_version => G_API_VERSION,
377 p_init_msg_list => G_FALSE,
378 x_return_status => lx_return_status,
379 x_msg_count => x_msg_count,
380 x_msg_data => x_msg_data,
381 p_line_item_tbl => l_update_line_item_tbl,
382 x_line_item_tbl => lx_line_item_tbl);
383
384 IF lx_return_status = G_RET_STS_UNEXP_ERROR THEN
385 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
386 ELSIF lx_return_status = G_RET_STS_ERROR THEN
387 RAISE OKL_API.G_EXCEPTION_ERROR;
388 END IF;
389
390 END IF;
391
392 END IF;
393
394 x_return_status := G_RET_STS_SUCCESS;
395
396 EXCEPTION
397
398 WHEN OKL_API.G_EXCEPTION_ERROR THEN
399
400 x_return_status := G_RET_STS_ERROR;
401
402 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
403
404 x_return_status := G_RET_STS_UNEXP_ERROR;
405
406 WHEN OTHERS THEN
407
408 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
409 p_msg_name => G_DB_ERROR,
410 p_token1 => G_PROG_NAME_TOKEN,
411 p_token1_value => l_program_name,
412 p_token2 => G_SQLCODE_TOKEN,
413 p_token2_value => sqlcode,
414 p_token3 => G_SQLERRM_TOKEN,
415 p_token3_value => sqlerrm);
416
417 x_return_status := G_RET_STS_UNEXP_ERROR;
418
419 END create_update_link_assets;
420
421
422
423 PROCEDURE allocate_amount1(p_api_version IN NUMBER,
424 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
425 p_transaction_control IN VARCHAR2 DEFAULT OKC_API.G_TRUE,
426 p_cle_id IN NUMBER,
427 p_chr_id IN NUMBER,
428 p_capitalize_yn IN VARCHAR2,
429 x_cle_id OUT NOCOPY NUMBER,
430 x_chr_id OUT NOCOPY NUMBER,
431 x_return_status OUT NOCOPY VARCHAR2,
432 x_msg_count OUT NOCOPY NUMBER,
433 x_msg_data OUT NOCOPY VARCHAR2) IS
434
435 l_program_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||'allocate_amount';
436
437 CURSOR c_sublines IS
438 SELECT cle.id
439 FROM okc_k_lines_b cle,
440 okc_line_styles_b lse
441 WHERE cle.cle_id = p_cle_id
442 AND cle.lse_id = lse.id
443 AND lse.lty_code = 'LINK_FEE_ASSET';
444
445 CURSOR c_assets IS
446 SELECT cle.cle_id fin_asset_id,
447 txl.asset_number
448 FROM okl_txl_assets_b txl,
449 okc_k_lines_b cle,
450 okc_line_styles_b lse
451 WHERE cle.dnz_chr_id = p_chr_id
452 AND cle.id = txl.kle_id
453 AND cle.lse_id = lse.id
454 AND lse.lty_code = 'FIXED_ASSET'
455 AND cle.sts_code <> 'ABANDONED';
456
457 l_link_asset_tbl link_asset_tbl_type;
458 l_cle_id_tbl cle_id_tbl_type;
459
460 i BINARY_INTEGER;
461
462 lx_return_status VARCHAR2(1);
463
464 BEGIN
465
466 IF p_transaction_control = G_TRUE THEN
467 SAVEPOINT l_program_name;
468 END IF;
469
470 IF p_init_msg_list = G_TRUE THEN
471 FND_MSG_PUB.initialize;
472 END IF;
473
474 i := 0;
475
476 FOR l_subline IN c_sublines LOOP
477
478 i := i + 1;
479
480 l_cle_id_tbl(i).cle_id := l_subline.id;
481
482 END LOOP;
483
484 IF l_cle_id_tbl.COUNT > 0 THEN
485
486 delete_quote_lines (p_api_version => G_API_VERSION,
487 p_init_msg_list => G_FALSE,
488 p_transaction_control => G_TRUE,
489 p_cle_id_tbl => l_cle_id_tbl,
490 x_return_status => lx_return_status,
491 x_msg_count => x_msg_count,
492 x_msg_data => x_msg_data);
493
494 IF lx_return_status = G_RET_STS_UNEXP_ERROR THEN
495 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
496 ELSIF lx_return_status = G_RET_STS_ERROR THEN
497 RAISE OKL_API.G_EXCEPTION_ERROR;
498 END IF;
499
500 END IF;
501
502 i := 0;
503
504 FOR l_asset IN c_assets LOOP
505
506 i := i + 1;
507
508 l_link_asset_tbl(i).fin_asset_id := l_asset.fin_asset_id;
509 l_link_asset_tbl(i).asset_number := l_asset.asset_number;
510
511 END LOOP;
512
513 IF l_link_asset_tbl.COUNT > 0 THEN
514
515 create_update_link_assets (p_cle_id => p_cle_id,
516 p_chr_id => p_chr_id,
517 p_capitalize_yn => p_capitalize_yn,
518 p_link_asset_tbl => l_link_asset_tbl,
519 p_derive_assoc_amt => 'Y',
520 x_return_status => lx_return_status,
521 x_msg_count => x_msg_count,
522 x_msg_data => x_msg_data);
523
524 IF lx_return_status = G_RET_STS_UNEXP_ERROR THEN
525 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
526 ELSIF lx_return_status = G_RET_STS_ERROR THEN
527 RAISE OKL_API.G_EXCEPTION_ERROR;
528 END IF;
529
530 END IF;
531
532 x_return_status := G_RET_STS_SUCCESS;
533
534 EXCEPTION
535
536 WHEN OKL_API.G_EXCEPTION_ERROR THEN
537
538 IF p_transaction_control = G_TRUE THEN
539 ROLLBACK TO l_program_name;
540 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
541 END IF;
542
543 x_return_status := G_RET_STS_ERROR;
544
545 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
546
547 IF p_transaction_control = G_TRUE THEN
548 ROLLBACK TO l_program_name;
549 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
550 END IF;
551
552 x_return_status := G_RET_STS_UNEXP_ERROR;
553
554 WHEN OTHERS THEN
555
556 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
557 p_msg_name => G_DB_ERROR,
558 p_token1 => G_PROG_NAME_TOKEN,
559 p_token1_value => l_program_name,
560 p_token2 => G_SQLCODE_TOKEN,
561 p_token2_value => sqlcode,
562 p_token3 => G_SQLERRM_TOKEN,
563 p_token3_value => sqlerrm);
564
565 IF p_transaction_control = G_TRUE THEN
566 ROLLBACK TO l_program_name;
567 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
568 END IF;
569
570 x_return_status := G_RET_STS_UNEXP_ERROR;
571
572 END allocate_amount1;
573
574
575 PROCEDURE update_contract(
576 p_api_version IN NUMBER,
577 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
578 x_return_status OUT NOCOPY VARCHAR2,
579 x_msg_count OUT NOCOPY NUMBER,
580 x_msg_data OUT NOCOPY VARCHAR2,
581 p_chr_id IN okl_k_headers.id%TYPE,
582 p_tradein_date IN okl_k_headers.date_tradein%TYPE,
583 p_tradein_amount IN okl_k_headers.tradein_amount%TYPE,
584 p_tradein_desc IN okl_k_headers.tradein_description%TYPE
585 )IS
586
587 lp_chrv_rec okl_okc_migration_pvt.chrv_rec_type;
588 lp_khrv_rec okl_khr_pvt.khrv_rec_type;
589
590 lx_chrv_rec okl_okc_migration_pvt.chrv_rec_type;
591 lx_khrv_rec okl_khr_pvt.khrv_rec_type;
592
593 l_chr_id okc_k_headers_b.id%type := p_chr_id;
594 l_tradein_date okl_k_headers.date_tradein%TYPE := p_tradein_date;
595 l_tradein_amount okl_k_headers.tradein_amount%TYPE := p_tradein_amount;
596 l_tradein_desc okl_k_headers.tradein_description%TYPE := p_tradein_desc;
597
598 l_api_name CONSTANT VARCHAR2(30) := 'update_contract';
599 l_api_version CONSTANT NUMBER := 1.0;
600 l_ak_prompt AK_ATTRIBUTES_VL.attribute_label_long%type;
601
602 BEGIN
603
604 -- call START_ACTIVITY to create savepoint, check compatibility
605 -- and initialize message list
606 x_return_status := OKC_API.START_ACTIVITY(
607 p_api_name => l_api_name,
608 p_pkg_name => g_pkg_name,
609 p_init_msg_list => p_init_msg_list,
610 l_api_version => l_api_version,
611 p_api_version => p_api_version,
612 p_api_type => g_api_type,
613 x_return_status => x_return_status);
614
615 -- check if activity started successfully
616 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
617 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
618 Elsif (x_return_status = OKC_API.G_RET_STS_ERROR) then
619 raise OKC_API.G_EXCEPTION_ERROR;
620 End If;
621
622 If(l_chr_id is null or l_chr_id = OKL_API.G_MISS_NUM) Then
623 x_return_status := OKC_API.g_ret_sts_error;
624 OKC_API.SET_MESSAGE( p_app_name => g_app_name
625 , p_msg_name => 'OKL_REQUIRED_VALUE'
626 , p_token1 => 'COL_NAME'
627 , p_token1_value => 'DNZ_CHR_ID'
628 );
629 raise OKC_API.G_EXCEPTION_ERROR;
630 End If;
631
632 If(l_tradein_date is null or l_tradein_date = OKL_API.G_MISS_DATE) Then
633 x_return_status := OKC_API.g_ret_sts_error;
634 l_ak_prompt := GET_AK_PROMPT('OKL_LA_AST_DTLS', 'OKL_TRADEIN_DATE');
635 OKC_API.SET_MESSAGE( p_app_name => g_app_name
636 , p_msg_name => 'OKL_REQUIRED_VALUE'
637 , p_token1 => 'COL_NAME'
638 , p_token1_value => l_ak_prompt
639 );
640 raise OKC_API.G_EXCEPTION_ERROR;
641 End If;
642
643 If(l_tradein_amount is null or l_tradein_amount = OKL_API.G_MISS_NUM) Then
644 x_return_status := OKC_API.g_ret_sts_error;
645 l_ak_prompt := GET_AK_PROMPT('OKL_LA_AST_DTLS', 'OKL_TRADEIN_AMOUNT');
646 OKC_API.SET_MESSAGE( p_app_name => g_app_name
647 , p_msg_name => 'OKL_REQUIRED_VALUE'
648 , p_token1 => 'COL_NAME'
649 , p_token1_value => l_ak_prompt
650 );
651 raise OKC_API.G_EXCEPTION_ERROR;
652 End If;
653
654
655 If okl_context.get_okc_org_id is null then
656 okl_context.set_okc_org_context(p_chr_id => l_chr_id );
657 End If;
658
659 lp_chrv_rec.id := l_chr_id;
660 lp_khrv_rec.id := l_chr_id;
661 lp_khrv_rec.date_tradein := l_tradein_date;
662 lp_khrv_rec.tradein_amount := l_tradein_amount;
663 lp_khrv_rec.tradein_description := l_tradein_desc;
664
665 OKL_CONTRACT_PUB.update_contract_header(
666 p_api_version => p_api_version,
667 p_init_msg_list => p_init_msg_list,
668 x_return_status => x_return_status,
669 x_msg_count => x_msg_count,
670 x_msg_data => x_msg_data,
671 p_restricted_update => 'F',
672 p_chrv_rec => lp_chrv_rec,
673 p_khrv_rec => lp_khrv_rec,
674 x_chrv_rec => lx_chrv_rec,
675 x_khrv_rec => lx_khrv_rec);
676
677 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
678 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
679 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
680 RAISE OKC_API.G_EXCEPTION_ERROR;
681 END IF;
682
683 x_return_status := OKC_API.g_ret_sts_success;
684
685 OKC_API.END_ACTIVITY(x_msg_count => x_msg_count, x_msg_data => x_msg_data);
686
687 EXCEPTION
688 when OKC_API.G_EXCEPTION_ERROR then
689 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
690 p_api_name => l_api_name,
691 p_pkg_name => g_pkg_name,
692 p_exc_name => 'OKC_API.G_RET_STS_ERROR',
693 x_msg_count => x_msg_count,
694 x_msg_data => x_msg_data,
695 p_api_type => g_api_type);
696
697 when OKC_API.G_EXCEPTION_UNEXPECTED_ERROR then
698 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
699 p_api_name => l_api_name,
700 p_pkg_name => g_pkg_name,
701 p_exc_name => 'OKC_API.G_RET_STS_UNEXP_ERROR',
702 x_msg_count => x_msg_count,
703 x_msg_data => x_msg_data,
704 p_api_type => g_api_type);
705
706 when OTHERS then
707 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
708 p_api_name => l_api_name,
709 p_pkg_name => g_pkg_name,
710 p_exc_name => 'OTHERS',
711 x_msg_count => x_msg_count,
712 x_msg_data => x_msg_data,
713 p_api_type => g_api_type);
714
715 END;
716
717 --Bug# 5102606
718 --------------------------------------------------------------------------------
719 --Name : recalculate_costs
720 --Creation : 17-Mar-2006
721 --Purpose : Local procedure to update capital_amount when trade-in is updated
722 --------------------------------------------------------------------------------
723 PROCEDURE recalculate_costs(
724 p_api_version IN NUMBER,
725 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
726 x_return_status OUT NOCOPY VARCHAR2,
727 x_msg_count OUT NOCOPY NUMBER,
728 x_msg_data OUT NOCOPY VARCHAR2,
729 p_chr_id IN OKL_K_HEADERS.id%TYPE,
730 p_cle_id IN OKL_K_LINES.id%TYPE
731 )IS
732
733 l_cap_amount NUMBER;
734
735 l_clev_rec OKL_OKC_MIGRATION_PVT.clev_rec_type;
736 l_klev_rec OKL_CONTRACT_PUB.klev_rec_type;
737 lx_clev_rec OKL_OKC_MIGRATION_PVT.clev_rec_type;
738 lx_klev_rec OKL_CONTRACT_PUB.klev_rec_type;
739
740 l_api_name CONSTANT VARCHAR2(30) := 'RECALCULATE_COSTS';
741 l_api_version CONSTANT NUMBER := 1.0;
742
743 BEGIN
744
745 x_return_status := OKL_API.g_ret_sts_success;
746
747 -- call START_ACTIVITY to create savepoint, check compatibility
748 -- and initialize message list
749 x_return_status := OKL_API.START_ACTIVITY(
750 p_api_name => l_api_name,
751 p_pkg_name => g_pkg_name,
752 p_init_msg_list => p_init_msg_list,
753 l_api_version => l_api_version,
754 p_api_version => p_api_version,
755 p_api_type => g_api_type,
756 x_return_status => x_return_status);
757
758 -- check if activity started successfully
759 If (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) then
760 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
761 Elsif (x_return_status = OKL_API.G_RET_STS_ERROR) then
762 raise OKL_API.G_EXCEPTION_ERROR;
763 End If;
764
765 l_cap_amount := 0;
766 OKL_EXECUTE_FORMULA_PUB.execute(p_api_version => p_api_version,
767 p_init_msg_list => p_init_msg_list,
768 x_return_status => x_return_status,
769 x_msg_count => x_msg_count,
770 x_msg_data => x_msg_data,
771 p_formula_name => G_FORMULA_CAP,
772 p_contract_id => p_chr_id,
773 p_line_id => p_cle_id,
774 x_value => l_cap_amount);
775
776 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
777 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
778 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
779 RAISE OKL_API.G_EXCEPTION_ERROR;
780 END IF;
781
782 ---------------------------------------------------------------------
783 -- call api to update costs on asset line
784 ---------------------------------------------------------------------
785 l_clev_rec.id := p_cle_id;
786 l_klev_rec.id := p_cle_id;
787 l_klev_rec.capital_amount := l_cap_amount;
788
789 okl_contract_pub.update_contract_line
790 (p_api_version => p_api_version,
791 p_init_msg_list => p_init_msg_list,
792 x_return_status => x_return_status,
793 x_msg_count => x_msg_count,
794 x_msg_data => x_msg_data,
795 p_clev_rec => l_clev_rec,
796 p_klev_rec => l_klev_rec,
797 x_clev_rec => lx_clev_rec,
798 x_klev_rec => lx_klev_rec
799 );
800 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
801 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
802 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
803 RAISE OKL_API.G_EXCEPTION_ERROR;
804 END IF;
805
806 -- Recalculate Asset depreciation cost when there
807 -- is a change to Trade-in during On-line Rebook
808 okl_activate_asset_pvt.recalculate_asset_cost
809 (p_api_version => p_api_version,
810 p_init_msg_list => p_init_msg_list,
811 x_return_status => x_return_status,
812 x_msg_count => x_msg_count,
813 x_msg_data => x_msg_data,
814 p_chr_id => p_chr_id,
815 p_cle_id => p_cle_id
816 );
817
818 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
819 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
820 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
821 RAISE OKL_API.G_EXCEPTION_ERROR;
822 END IF;
823
824 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count, x_msg_data => x_msg_data);
825
826 EXCEPTION
827 when OKL_API.G_EXCEPTION_ERROR then
828 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
829 p_api_name => l_api_name,
830 p_pkg_name => g_pkg_name,
831 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
832 x_msg_count => x_msg_count,
833 x_msg_data => x_msg_data,
834 p_api_type => g_api_type);
835
836 when OKL_API.G_EXCEPTION_UNEXPECTED_ERROR then
837 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
838 p_api_name => l_api_name,
839 p_pkg_name => g_pkg_name,
840 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
841 x_msg_count => x_msg_count,
842 x_msg_data => x_msg_data,
843 p_api_type => g_api_type);
844
845 when OTHERS then
846 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
847 p_api_name => l_api_name,
848 p_pkg_name => g_pkg_name,
849 p_exc_name => 'OTHERS',
850 x_msg_count => x_msg_count,
851 x_msg_data => x_msg_data,
852 p_api_type => g_api_type);
853
854 END recalculate_costs;
855 --Bug# 5102606
856
857 PROCEDURE create_tradein(
858 p_api_version IN NUMBER,
859 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
860 x_return_status OUT NOCOPY VARCHAR2,
861 x_msg_count OUT NOCOPY NUMBER,
862 x_msg_data OUT NOCOPY VARCHAR2,
863 p_chr_id IN okl_k_headers.id%TYPE,
864 p_tradein_rec IN tradein_rec_type,
865 x_tradein_rec OUT NOCOPY tradein_rec_type
866 )IS
867
868 lp_tradein_rec OKL_LA_TRADEIN_PVT.tradein_rec_type := p_tradein_rec;
869 lx_tradein_rec OKL_LA_TRADEIN_PVT.tradein_rec_type;
870
871 lp_klev_rec okl_kle_pvt.klev_rec_type;
872 lp_clev_rec okl_okc_migration_pvt.clev_rec_type;
873
874 lx_klev_rec okl_kle_pvt.klev_rec_type;
875 lx_clev_rec okl_okc_migration_pvt.clev_rec_type;
876
877 l_chr_id okc_k_headers_b.id%type := p_chr_id;
878
879 l_api_name CONSTANT VARCHAR2(30) := 'create_tradein';
880 l_api_version CONSTANT NUMBER := 1.0;
881 l_ak_prompt AK_ATTRIBUTES_VL.attribute_label_long%type;
882
883 BEGIN
884
885 -- call START_ACTIVITY to create savepoint, check compatibility
886 -- and initialize message list
887 x_return_status := OKC_API.START_ACTIVITY(
888 p_api_name => l_api_name,
889 p_pkg_name => g_pkg_name,
890 p_init_msg_list => p_init_msg_list,
891 l_api_version => l_api_version,
892 p_api_version => p_api_version,
893 p_api_type => g_api_type,
894 x_return_status => x_return_status);
895
896 -- check if activity started successfully
897 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
898 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
899 Elsif (x_return_status = OKC_API.G_RET_STS_ERROR) then
900 raise OKC_API.G_EXCEPTION_ERROR;
901 End If;
902
903 If( lp_tradein_rec.asset_id is null) Then
904 -- program error
905 x_return_status := OKC_API.g_ret_sts_error;
906 OKC_API.SET_MESSAGE( p_app_name => g_app_name
907 , p_msg_name => 'PROGRAM_ERROR_ASSET_ID'
908 );
909 raise OKC_API.G_EXCEPTION_ERROR;
910 End If;
911
912 -- on update of a asset line, reset the tradein_amount value to null
913 If ( (lp_tradein_rec.id is not null and lp_tradein_rec.id <> OKL_API.G_MISS_NUM)
914 and (lp_tradein_rec.id <> lp_tradein_rec.asset_id)) Then
915
916 lp_clev_rec.id := lp_tradein_rec.id;
917 lp_klev_rec.id := lp_tradein_rec.id;
918 lp_klev_rec.tradein_amount := null;
919
920 okl_contract_pvt.update_contract_line(
921 p_api_version => p_api_version,
922 p_init_msg_list => p_init_msg_list,
923 x_return_status => x_return_status,
924 x_msg_count => x_msg_count,
925 x_msg_data => x_msg_data,
926 p_clev_rec => lp_clev_rec,
927 p_klev_rec => lp_klev_rec,
928 x_clev_rec => lx_clev_rec,
929 x_klev_rec => lx_klev_rec);
930
931
932 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
933 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
934 Elsif (x_return_status = OKC_API.G_RET_STS_ERROR) then
935 raise OKC_API.G_EXCEPTION_ERROR;
936 End If;
937
938 --Bug# 5102606
939 recalculate_costs(
940 p_api_version => p_api_version,
941 p_init_msg_list => p_init_msg_list,
942 x_return_status => x_return_status,
943 x_msg_count => x_msg_count,
944 x_msg_data => x_msg_data,
945 p_chr_id => p_chr_id,
946 p_cle_id => lp_clev_rec.id);
947
948 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
949 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
950 Elsif (x_return_status = OKC_API.G_RET_STS_ERROR) then
951 raise OKC_API.G_EXCEPTION_ERROR;
952 End If;
953 --Bug# 5102606
954
955 End If;
956
957 lp_clev_rec.id := lp_tradein_rec.asset_id;
958 lp_klev_rec.id := lp_tradein_rec.asset_id;
959 lp_klev_rec.tradein_amount := lp_tradein_rec.tradein_amount;
960
961 okl_contract_pvt.update_contract_line(
962 p_api_version => p_api_version,
963 p_init_msg_list => p_init_msg_list,
964 x_return_status => x_return_status,
965 x_msg_count => x_msg_count,
966 x_msg_data => x_msg_data,
967 p_clev_rec => lp_clev_rec,
968 p_klev_rec => lp_klev_rec,
969 x_clev_rec => lx_clev_rec,
970 x_klev_rec => lx_klev_rec);
971
972
973 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
974 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
975 Elsif (x_return_status = OKC_API.G_RET_STS_ERROR) then
976 raise OKC_API.G_EXCEPTION_ERROR;
977 End If;
978
979 --Bug# 5102606
980 recalculate_costs(
981 p_api_version => p_api_version,
982 p_init_msg_list => p_init_msg_list,
983 x_return_status => x_return_status,
984 x_msg_count => x_msg_count,
985 x_msg_data => x_msg_data,
986 p_chr_id => p_chr_id,
987 p_cle_id => lp_clev_rec.id);
988
989 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
990 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
991 Elsif (x_return_status = OKC_API.G_RET_STS_ERROR) then
992 raise OKC_API.G_EXCEPTION_ERROR;
993 End If;
994 --Bug# 5102606
995
996 x_tradein_rec.id := lx_clev_rec.id;
997
998 OKC_API.END_ACTIVITY(x_msg_count => x_msg_count, x_msg_data => x_msg_data);
999
1000 EXCEPTION
1001 when OKC_API.G_EXCEPTION_ERROR then
1002 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
1003 p_api_name => l_api_name,
1004 p_pkg_name => g_pkg_name,
1005 p_exc_name => 'OKC_API.G_RET_STS_ERROR',
1006 x_msg_count => x_msg_count,
1007 x_msg_data => x_msg_data,
1008 p_api_type => g_api_type);
1009
1010 when OKC_API.G_EXCEPTION_UNEXPECTED_ERROR then
1011 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
1012 p_api_name => l_api_name,
1013 p_pkg_name => g_pkg_name,
1014 p_exc_name => 'OKC_API.G_RET_STS_UNEXP_ERROR',
1015 x_msg_count => x_msg_count,
1016 x_msg_data => x_msg_data,
1017 p_api_type => g_api_type);
1018
1019 when OTHERS then
1020 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
1021 p_api_name => l_api_name,
1022 p_pkg_name => g_pkg_name,
1023 p_exc_name => 'OTHERS',
1024 x_msg_count => x_msg_count,
1025 x_msg_data => x_msg_data,
1026 p_api_type => g_api_type);
1027
1028 END;
1029
1030 PROCEDURE create_tradein(
1031 p_api_version IN NUMBER,
1032 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1033 x_return_status OUT NOCOPY VARCHAR2,
1034 x_msg_count OUT NOCOPY NUMBER,
1035 x_msg_data OUT NOCOPY VARCHAR2,
1036 p_chr_id IN okl_k_headers.id%TYPE,
1037 p_tradein_tbl IN tradein_tbl_type,
1038 x_tradein_tbl OUT NOCOPY tradein_tbl_type
1039 )IS
1040
1041 lp_tradein_rec OKL_LA_TRADEIN_PVT.tradein_rec_type;
1042 lx_tradein_rec OKL_LA_TRADEIN_PVT.tradein_rec_type;
1043
1044 lp_chrv_rec OKL_OKC_MIGRATION_PVT.chrv_rec_type;
1045 lp_khrv_rec OKL_CONTRACT_PUB.khrv_rec_type;
1046
1047 lx_chrv_rec OKL_OKC_MIGRATION_PVT.chrv_rec_type;
1048 lx_khrv_rec OKL_CONTRACT_PUB.khrv_rec_type;
1049
1050 lp_clev_tbl OKL_OKC_MIGRATION_PVT.clev_tbl_type;
1051 lp_klev_tbl OKL_KLE_PVT.klev_tbl_type;
1052
1053 lx_clev_tbl OKL_OKC_MIGRATION_PVT.clev_tbl_type;
1054 lx_klev_tbl OKL_KLE_PVT.klev_tbl_type;
1055
1056 l_chr_id okc_k_headers_b.id%type := p_chr_id;
1057
1058 l_api_name CONSTANT VARCHAR2(30) := 'create_tradein';
1059 l_api_version CONSTANT NUMBER := 1.0;
1060 l_ak_prompt AK_ATTRIBUTES_VL.attribute_label_long%type;
1061 i number := 0;
1062
1063
1064 BEGIN
1065
1066 -- call START_ACTIVITY to create savepoint, check compatibility
1067 -- and initialize message list
1068 x_return_status := OKC_API.START_ACTIVITY(
1069 p_api_name => l_api_name,
1070 p_pkg_name => g_pkg_name,
1071 p_init_msg_list => p_init_msg_list,
1072 l_api_version => l_api_version,
1073 p_api_version => p_api_version,
1074 p_api_type => g_api_type,
1075 x_return_status => x_return_status);
1076
1077 -- check if activity started successfully
1078 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
1079 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1080 Elsif (x_return_status = OKC_API.G_RET_STS_ERROR) then
1081 raise OKC_API.G_EXCEPTION_ERROR;
1082 End If;
1083
1084 If okl_context.get_okc_org_id is null then
1085 okl_context.set_okc_org_context(p_chr_id => l_chr_id );
1086 End If;
1087
1088 If (p_tradein_tbl.COUNT > 0) Then
1089
1090 i := p_tradein_tbl.FIRST;
1091
1092 LOOP
1093
1094 lp_tradein_rec.id := p_tradein_tbl(i).id;
1095 lp_tradein_rec.asset_id := p_tradein_tbl(i).asset_id;
1096 lp_tradein_rec.asset_number := p_tradein_tbl(i).asset_number;
1097 lp_tradein_rec.tradein_amount := p_tradein_tbl(i).tradein_amount;
1098 /*
1099 If( (lp_tradein_rec.asset_number is null or lp_tradein_rec.asset_number = OKL_API.G_MISS_CHAR)
1100 and (lp_tradein_rec.tradein_amount is null or lp_tradein_rec.tradein_amount = OKL_API.G_MISS_NUM) ) Then
1101 break;
1102 End If;
1103
1104 If( (lp_tradein_rec.asset_number is null or lp_tradein_rec.asset_number = OKL_API.G_MISS_CHAR)
1105 and (lp_tradein_rec.tradein_amount is not null and lp_tradein_rec.tradein_amount != OKL_API.G_MISS_NUM) ) Then
1106 x_return_status := OKC_API.g_ret_sts_error;
1107 OKC_API.SET_MESSAGE( p_app_name => g_app_name
1108 , p_msg_name => 'OKL_LLA_ASSET_REQUIRED'
1109 );
1110 raise OKC_API.G_EXCEPTION_ERROR;
1111 End If;
1112
1113 If( (lp_tradein_rec.asset_number is not null and lp_tradein_rec.asset_number != OKL_API.G_MISS_CHAR)
1114 and (lp_tradein_rec.tradein_amount is null or lp_tradein_rec.tradein_amount = OKL_API.G_MISS_NUM) ) Then
1115 x_return_status := OKC_API.g_ret_sts_error;
1116 OKC_API.SET_MESSAGE( p_app_name => g_app_name
1117 , p_msg_name => 'OKL_AMOUNT_FORMAT'
1118 );
1119 raise OKC_API.G_EXCEPTION_ERROR;
1120 End If;
1121 */
1122 create_tradein(
1123 p_api_version => p_api_version,
1124 p_init_msg_list => p_init_msg_list,
1125 x_return_status => x_return_status,
1126 x_msg_count => x_msg_count,
1127 x_msg_data => x_msg_data,
1128 p_chr_id => l_chr_id,
1129 p_tradein_rec => lp_tradein_rec,
1130 x_tradein_rec => lx_tradein_rec);
1131
1132 If x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR Then
1133 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1134 Elsif x_return_status = OKC_API.G_RET_STS_ERROR Then
1135 raise OKC_API.G_EXCEPTION_ERROR;
1136 End If;
1137
1138 EXIT WHEN (i = p_tradein_tbl.LAST);
1139 i := p_tradein_tbl.NEXT(i);
1140 END LOOP;
1141
1142 End If;
1143
1144 OKC_API.END_ACTIVITY(x_msg_count => x_msg_count, x_msg_data => x_msg_data);
1145
1146 EXCEPTION
1147 when OKC_API.G_EXCEPTION_ERROR then
1148 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
1149 p_api_name => l_api_name,
1150 p_pkg_name => g_pkg_name,
1151 p_exc_name => 'OKC_API.G_RET_STS_ERROR',
1152 x_msg_count => x_msg_count,
1153 x_msg_data => x_msg_data,
1154 p_api_type => g_api_type);
1155
1156 when OKC_API.G_EXCEPTION_UNEXPECTED_ERROR then
1157 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
1158 p_api_name => l_api_name,
1159 p_pkg_name => g_pkg_name,
1160 p_exc_name => 'OKC_API.G_RET_STS_UNEXP_ERROR',
1161 x_msg_count => x_msg_count,
1162 x_msg_data => x_msg_data,
1163 p_api_type => g_api_type);
1164
1165 when OTHERS then
1166 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
1167 p_api_name => l_api_name,
1168 p_pkg_name => g_pkg_name,
1169 p_exc_name => 'OTHERS',
1170 x_msg_count => x_msg_count,
1171 x_msg_data => x_msg_data,
1172 p_api_type => g_api_type);
1173
1174 END;
1175
1176
1177 PROCEDURE delete_tradein(
1178 p_api_version IN NUMBER,
1179 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1180 x_return_status OUT NOCOPY VARCHAR2,
1181 x_msg_count OUT NOCOPY NUMBER,
1182 x_msg_data OUT NOCOPY VARCHAR2,
1183 p_chr_id IN okl_k_headers.id%TYPE,
1184 p_tradein_rec IN tradein_rec_type,
1185 x_tradein_rec OUT NOCOPY tradein_rec_type
1186 )IS
1187
1188 lp_tradein_rec OKL_LA_TRADEIN_PVT.tradein_rec_type := p_tradein_rec;
1189 lx_tradein_rec OKL_LA_TRADEIN_PVT.tradein_rec_type;
1190
1191 lp_klev_rec okl_kle_pvt.klev_rec_type;
1192 lp_clev_rec okl_okc_migration_pvt.clev_rec_type;
1193
1194 lx_klev_rec okl_kle_pvt.klev_rec_type;
1195 lx_clev_rec okl_okc_migration_pvt.clev_rec_type;
1196
1197 l_chr_id okc_k_headers_b.id%type := p_chr_id;
1198
1199 l_api_name CONSTANT VARCHAR2(30) := 'create_tradein';
1200 l_api_version CONSTANT NUMBER := 1.0;
1201 l_ak_prompt AK_ATTRIBUTES_VL.attribute_label_long%type;
1202
1203 BEGIN
1204
1205 -- call START_ACTIVITY to create savepoint, check compatibility
1206 -- and initialize message list
1207 x_return_status := OKC_API.START_ACTIVITY(
1208 p_api_name => l_api_name,
1209 p_pkg_name => g_pkg_name,
1210 p_init_msg_list => p_init_msg_list,
1211 l_api_version => l_api_version,
1212 p_api_version => p_api_version,
1213 p_api_type => g_api_type,
1214 x_return_status => x_return_status);
1215
1216 -- check if activity started successfully
1217 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
1218 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1219 Elsif (x_return_status = OKC_API.G_RET_STS_ERROR) then
1220 raise OKC_API.G_EXCEPTION_ERROR;
1221 End If;
1222
1223 lp_clev_rec.id := lp_tradein_rec.id;
1224 lp_klev_rec.id := lp_tradein_rec.id;
1225 lp_klev_rec.tradein_amount := null;
1226
1227 okl_contract_pvt.update_contract_line(
1228 p_api_version => p_api_version,
1229 p_init_msg_list => p_init_msg_list,
1230 x_return_status => x_return_status,
1231 x_msg_count => x_msg_count,
1232 x_msg_data => x_msg_data,
1233 p_clev_rec => lp_clev_rec,
1234 p_klev_rec => lp_klev_rec,
1235 x_clev_rec => lx_clev_rec,
1236 x_klev_rec => lx_klev_rec);
1237
1238
1239 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
1240 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1241 Elsif (x_return_status = OKC_API.G_RET_STS_ERROR) then
1242 raise OKC_API.G_EXCEPTION_ERROR;
1243 End If;
1244
1245 --Bug# 5102606
1246 recalculate_costs(
1247 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_chr_id => p_chr_id,
1253 p_cle_id => lp_clev_rec.id);
1254
1255 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
1256 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1257 Elsif (x_return_status = OKC_API.G_RET_STS_ERROR) then
1258 raise OKC_API.G_EXCEPTION_ERROR;
1259 End If;
1260 --Bug# 5102606
1261
1262 OKC_API.END_ACTIVITY(x_msg_count => x_msg_count, x_msg_data => x_msg_data);
1263
1264 EXCEPTION
1265 when OKC_API.G_EXCEPTION_ERROR then
1266 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
1267 p_api_name => l_api_name,
1268 p_pkg_name => g_pkg_name,
1269 p_exc_name => 'OKC_API.G_RET_STS_ERROR',
1270 x_msg_count => x_msg_count,
1271 x_msg_data => x_msg_data,
1272 p_api_type => g_api_type);
1273
1274 when OKC_API.G_EXCEPTION_UNEXPECTED_ERROR then
1275 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
1276 p_api_name => l_api_name,
1277 p_pkg_name => g_pkg_name,
1278 p_exc_name => 'OKC_API.G_RET_STS_UNEXP_ERROR',
1279 x_msg_count => x_msg_count,
1280 x_msg_data => x_msg_data,
1281 p_api_type => g_api_type);
1282
1283 when OTHERS then
1284 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
1285 p_api_name => l_api_name,
1286 p_pkg_name => g_pkg_name,
1287 p_exc_name => 'OTHERS',
1288 x_msg_count => x_msg_count,
1289 x_msg_data => x_msg_data,
1290 p_api_type => g_api_type);
1291
1292 END;
1293
1294 PROCEDURE delete_tradein(
1295 p_api_version IN NUMBER,
1296 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1297 x_return_status OUT NOCOPY VARCHAR2,
1298 x_msg_count OUT NOCOPY NUMBER,
1299 x_msg_data OUT NOCOPY VARCHAR2,
1300 p_chr_id IN okl_k_headers.id%TYPE,
1301 p_tradein_tbl IN tradein_tbl_type,
1302 x_tradein_tbl OUT NOCOPY tradein_tbl_type
1303 )IS
1304
1305 lp_tradein_rec OKL_LA_TRADEIN_PVT.tradein_rec_type;
1306 lx_tradein_rec OKL_LA_TRADEIN_PVT.tradein_rec_type;
1307
1308 lp_chrv_rec OKL_OKC_MIGRATION_PVT.chrv_rec_type;
1309 lp_khrv_rec OKL_CONTRACT_PUB.khrv_rec_type;
1310
1311 lx_chrv_rec OKL_OKC_MIGRATION_PVT.chrv_rec_type;
1312 lx_khrv_rec OKL_CONTRACT_PUB.khrv_rec_type;
1313
1314 lp_clev_tbl OKL_OKC_MIGRATION_PVT.clev_tbl_type;
1315 lp_klev_tbl OKL_KLE_PVT.klev_tbl_type;
1316
1317 lx_clev_tbl OKL_OKC_MIGRATION_PVT.clev_tbl_type;
1318 lx_klev_tbl OKL_KLE_PVT.klev_tbl_type;
1319
1320 l_chr_id okc_k_headers_b.id%type := p_chr_id;
1321
1322 l_api_name CONSTANT VARCHAR2(30) := 'create_tradein';
1323 l_api_version CONSTANT NUMBER := 1.0;
1324 l_ak_prompt AK_ATTRIBUTES_VL.attribute_label_long%type;
1325 i number := 0;
1326
1327
1328 BEGIN
1329
1330 -- call START_ACTIVITY to create savepoint, check compatibility
1331 -- and initialize message list
1332 x_return_status := OKC_API.START_ACTIVITY(
1333 p_api_name => l_api_name,
1334 p_pkg_name => g_pkg_name,
1335 p_init_msg_list => p_init_msg_list,
1336 l_api_version => l_api_version,
1337 p_api_version => p_api_version,
1338 p_api_type => g_api_type,
1339 x_return_status => x_return_status);
1340
1341 -- check if activity started successfully
1342 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
1343 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1344 Elsif (x_return_status = OKC_API.G_RET_STS_ERROR) then
1345 raise OKC_API.G_EXCEPTION_ERROR;
1346 End If;
1347
1348 If (p_tradein_tbl.COUNT > 0) Then
1349
1350 i := p_tradein_tbl.FIRST;
1351
1352 LOOP
1353
1354 lp_tradein_rec.id := p_tradein_tbl(i).id;
1355
1356 delete_tradein(
1357 p_api_version => p_api_version,
1358 p_init_msg_list => p_init_msg_list,
1359 x_return_status => x_return_status,
1360 x_msg_count => x_msg_count,
1361 x_msg_data => x_msg_data,
1362 p_chr_id => l_chr_id,
1363 p_tradein_rec => lp_tradein_rec,
1364 x_tradein_rec => lx_tradein_rec);
1365
1366 If x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR Then
1367 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1368 Elsif x_return_status = OKC_API.G_RET_STS_ERROR Then
1369 raise OKC_API.G_EXCEPTION_ERROR;
1370 End If;
1371
1372 EXIT WHEN (i = p_tradein_tbl.LAST);
1373 i := p_tradein_tbl.NEXT(i);
1374 END LOOP;
1375
1376 End If;
1377
1378 OKC_API.END_ACTIVITY(x_msg_count => x_msg_count, x_msg_data => x_msg_data);
1379
1380 EXCEPTION
1381 when OKC_API.G_EXCEPTION_ERROR then
1382 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
1383 p_api_name => l_api_name,
1384 p_pkg_name => g_pkg_name,
1385 p_exc_name => 'OKC_API.G_RET_STS_ERROR',
1386 x_msg_count => x_msg_count,
1387 x_msg_data => x_msg_data,
1388 p_api_type => g_api_type);
1389
1390 when OKC_API.G_EXCEPTION_UNEXPECTED_ERROR then
1391 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
1392 p_api_name => l_api_name,
1393 p_pkg_name => g_pkg_name,
1394 p_exc_name => 'OKC_API.G_RET_STS_UNEXP_ERROR',
1395 x_msg_count => x_msg_count,
1396 x_msg_data => x_msg_data,
1397 p_api_type => g_api_type);
1398
1399 when OTHERS then
1400 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
1401 p_api_name => l_api_name,
1402 p_pkg_name => g_pkg_name,
1403 p_exc_name => 'OTHERS',
1404 x_msg_count => x_msg_count,
1405 x_msg_data => x_msg_data,
1406 p_api_type => g_api_type);
1407
1408 END;
1409
1410 PROCEDURE allocate_amount(p_api_version IN NUMBER,
1411 p_init_msg_list IN VARCHAR2 DEFAULT G_FALSE,
1412 p_transaction_control IN VARCHAR2 DEFAULT G_TRUE,
1413 p_cle_id IN NUMBER,
1414 p_chr_id IN NUMBER,
1415 p_capitalize_yn IN VARCHAR2,
1416 x_cle_id OUT NOCOPY NUMBER,
1417 x_chr_id OUT NOCOPY NUMBER,
1418 x_return_status OUT NOCOPY VARCHAR2,
1419 x_msg_count OUT NOCOPY NUMBER,
1420 x_msg_data OUT NOCOPY VARCHAR2) IS
1421
1422
1423 l_chr_id okc_k_headers_b.id%type := null;
1424 l_cl_id okc_k_headers_b.id%type := null;
1425
1426 l_api_name CONSTANT VARCHAR2(30) := 'allocate_amount';
1427 l_api_version CONSTANT NUMBER := 1.0;
1428
1429
1430 BEGIN
1431
1432 l_chr_id := p_chr_id;
1433 If okl_context.get_okc_org_id is null then
1434 okl_context.set_okc_org_context(p_chr_id => l_chr_id );
1435 End If;
1436
1437 -- call START_ACTIVITY to create savepoint, check compatibility
1438 -- and initialize message list
1439 x_return_status := OKC_API.START_ACTIVITY(
1440 p_api_name => l_api_name,
1441 p_pkg_name => g_pkg_name,
1442 p_init_msg_list => p_init_msg_list,
1443 l_api_version => l_api_version,
1444 p_api_version => p_api_version,
1445 p_api_type => g_api_type,
1446 x_return_status => x_return_status);
1447
1448 -- check if activity started successfully
1449 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
1450 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1451 Elsif (x_return_status = OKC_API.G_RET_STS_ERROR) then
1452 raise OKC_API.G_EXCEPTION_ERROR;
1453 End If;
1454
1455 allocate_amount(
1456 p_api_version => p_api_version,
1457 p_init_msg_list => p_init_msg_list,
1458 p_transaction_control => p_transaction_control,
1459 p_cle_id => p_cle_id,
1460 p_chr_id => p_chr_id,
1461 p_capitalize_yn => p_capitalize_yn,
1462 x_cle_id => x_cle_id,
1463 x_chr_id => x_chr_id,
1464 x_return_status => x_return_status,
1465 x_msg_count => x_msg_count,
1466 x_msg_data => x_msg_data
1467 );
1468
1469 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1470 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1471 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1472 RAISE OKC_API.G_EXCEPTION_ERROR;
1473 END IF;
1474
1475 OKC_API.END_ACTIVITY(x_msg_count => x_msg_count, x_msg_data => x_msg_data);
1476
1477 EXCEPTION
1478 when OKC_API.G_EXCEPTION_ERROR then
1479 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
1480 p_api_name => l_api_name,
1481 p_pkg_name => g_pkg_name,
1482 p_exc_name => 'OKC_API.G_RET_STS_ERROR',
1483 x_msg_count => x_msg_count,
1484 x_msg_data => x_msg_data,
1485 p_api_type => g_api_type);
1486
1487 when OKC_API.G_EXCEPTION_UNEXPECTED_ERROR then
1488 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
1489 p_api_name => l_api_name,
1490 p_pkg_name => g_pkg_name,
1491 p_exc_name => 'OKC_API.G_RET_STS_UNEXP_ERROR',
1492 x_msg_count => x_msg_count,
1493 x_msg_data => x_msg_data,
1494 p_api_type => g_api_type);
1495
1496 when OTHERS then
1497 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
1498 p_api_name => l_api_name,
1499 p_pkg_name => g_pkg_name,
1500 p_exc_name => 'OTHERS',
1501 x_msg_count => x_msg_count,
1502 x_msg_data => x_msg_data,
1503 p_api_type => g_api_type);
1504
1505 END allocate_amount;
1506
1507 PROCEDURE allocate_amount_tradein (
1508 p_api_version IN NUMBER,
1509 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1510 x_return_status OUT NOCOPY VARCHAR2,
1511 x_msg_count OUT NOCOPY NUMBER,
1512 x_msg_data OUT NOCOPY VARCHAR2,
1513 p_chr_id IN NUMBER,
1514 p_derive_assoc_amt IN VARCHAR2
1515 ) IS
1516
1517 l_api_name CONSTANT VARCHAR2(30) := 'allocate_amount';
1518 l_api_version CONSTANT NUMBER := 1.0;
1519
1520 CURSOR c_assets IS
1521 SELECT cle.cle_id fin_asset_id,
1522 txl.asset_number
1523 FROM okl_txl_assets_b txl,
1524 okc_k_lines_b cle,
1525 okc_line_styles_b lse
1526 WHERE cle.dnz_chr_id = p_chr_id
1527 AND cle.id = txl.kle_id
1528 AND cle.lse_id = lse.id
1529 AND lse.lty_code = 'FIXED_ASSET'
1530 AND cle.sts_code <> 'ABANDONED';
1531
1532 l_asset_tbl asset_tbl_type;
1533
1534 lp_klev_tbl okl_kle_pvt.klev_tbl_type;
1535 lp_clev_tbl okl_okc_migration_pvt.clev_tbl_type;
1536
1537 lx_klev_tbl okl_kle_pvt.klev_tbl_type;
1538 lx_clev_tbl okl_okc_migration_pvt.clev_tbl_type;
1539
1540 k BINARY_INTEGER := 1; -- create table index
1541 m BINARY_INTEGER := 1; -- update table index
1542 i number := 0;
1543 l_chr_id okc_k_headers_b.id%type := null;
1544 l_tradein_amount NUMBER := 0;
1545 l_asset_oec NUMBER;
1546 l_oec_total NUMBER := 0;
1547 l_assoc_amount NUMBER;
1548 l_assoc_total NUMBER := 0;
1549 l_currency_code VARCHAR2(15);
1550 l_compare_amt NUMBER;
1551 l_diff NUMBER;
1552 l_adj_rec BINARY_INTEGER;
1553
1554 BEGIN
1555
1556 -- call START_ACTIVITY to create savepoint, check compatibility
1557 -- and initialize message list
1558 x_return_status := OKC_API.START_ACTIVITY(
1559 p_api_name => l_api_name,
1560 p_pkg_name => g_pkg_name,
1561 p_init_msg_list => p_init_msg_list,
1562 l_api_version => l_api_version,
1563 p_api_version => p_api_version,
1564 p_api_type => g_api_type,
1565 x_return_status => x_return_status);
1566
1567 -- check if activity started successfully
1568 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
1569 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1570 Elsif (x_return_status = OKC_API.G_RET_STS_ERROR) then
1571 raise OKC_API.G_EXCEPTION_ERROR;
1572 End If;
1573
1574 l_chr_id := p_chr_id;
1575 If okl_context.get_okc_org_id is null then
1576 okl_context.set_okc_org_context(p_chr_id => l_chr_id );
1577 End If;
1578
1579 i := 0;
1580
1581 FOR l_asset IN c_assets LOOP
1582
1583 i := i + 1;
1584
1585 l_asset_tbl(i).fin_asset_id := l_asset.fin_asset_id;
1586 l_asset_tbl(i).asset_number := l_asset.asset_number;
1587
1588 END LOOP;
1589
1590
1591 SELECT nvl(tradein_amount,0)
1592 INTO l_tradein_amount
1593 FROM okl_k_headers
1594 WHERE id = p_chr_id;
1595
1596 SELECT currency_code
1597 INTO l_currency_code
1598 FROM okc_k_headers_b
1599 WHERE id = p_chr_id;
1600
1601 IF (l_asset_tbl.COUNT > 0) THEN
1602
1603 ------------------------------------------------------------------
1604 -- 1. Loop through to get OEC total of all assets being associated
1605 ------------------------------------------------------------------
1606 FOR i IN l_asset_tbl.FIRST .. l_asset_tbl.LAST LOOP
1607
1608 IF l_asset_tbl.EXISTS(i) THEN
1609
1610 SELECT NVL(oec, 0)
1611 INTO l_asset_oec
1612 FROM okl_k_lines
1613 WHERE id = l_asset_tbl(i).fin_asset_id;
1614
1615 l_oec_total := l_oec_total + l_asset_oec;
1616
1617 END IF;
1618
1619 END LOOP;
1620
1621 ----------------------------------------------------------------------------
1622 -- 2. Loop through to determine associated amounts and round off the amounts
1623 ----------------------------------------------------------------------------
1624 FOR i IN l_asset_tbl.FIRST .. l_asset_tbl.LAST LOOP
1625
1626 IF l_asset_tbl.EXISTS(i) THEN
1627
1628 -- LLA APIs ensure asset OEC and l_tradein_amount are rounded
1629
1630 SELECT NVL(oec, 0)
1631 INTO l_asset_oec
1632 FROM okl_k_lines
1633 WHERE id = l_asset_tbl(i).fin_asset_id;
1634
1635 IF l_asset_tbl.COUNT = 1 THEN
1636
1637 l_assoc_amount := l_tradein_amount;
1638
1639 ELSE
1640
1641 l_assoc_amount := l_tradein_amount * l_asset_oec / l_oec_total;
1642
1643 END IF;
1644
1645
1646 l_assoc_amount := okl_accounting_util.round_amount(p_amount => l_assoc_amount,
1647 p_currency_code => l_currency_code);
1648
1649 l_assoc_total := l_assoc_total + l_assoc_amount;
1650
1651 lp_klev_tbl(i).tradein_amount := l_assoc_amount;
1652 lp_klev_tbl(i).id := l_asset_tbl(i).fin_asset_id;
1653 lp_clev_tbl(i).id := l_asset_tbl(i).fin_asset_id;
1654 END IF;
1655
1656 END LOOP;
1657
1658 ----------------------------------------------------------------------------------------------------
1659 -- 3. Adjust associated amount if associated total does not tally up with line amount after rounding
1660 ----------------------------------------------------------------------------------------------------
1661 IF l_assoc_total <> l_tradein_amount THEN
1662
1663 l_diff := ABS(l_tradein_amount - l_assoc_total);
1664
1665 lp_klev_tbl(lp_klev_tbl.FIRST).tradein_amount := lp_klev_tbl(lp_klev_tbl.FIRST).tradein_amount + l_diff;
1666
1667 END IF;
1668
1669 /*
1670 ----------------------------------------------------------------------------------------------------
1671 -- 3. Adjust associated amount if associated total does not tally up with line amount after rounding
1672 ----------------------------------------------------------------------------------------------------
1673 IF l_assoc_total <> l_line_amount THEN
1674
1675 l_diff := ABS(l_assoc_total - l_line_amount);
1676
1677 FOR i IN l_link_asset_tbl.FIRST .. l_link_asset_tbl.LAST LOOP
1678
1679 IF l_link_asset_tbl.EXISTS(i) THEN
1680
1681 -- if the total split amount is less than line amount add the difference amount to the
1682 -- asset with less amount and if the total split amount is greater than the line amount
1683 -- than subtract the difference amount from the asset with highest amount
1684
1685 IF i = l_link_asset_tbl.FIRST THEN
1686
1687 l_adj_rec := i; -- Bug#3404844
1688 l_compare_amt := l_link_asset_tbl(i).amount;
1689
1690 ELSIF (l_assoc_total < l_line_amount) AND (l_link_asset_tbl(i).amount <= l_compare_amt) OR
1691 (l_assoc_total > l_line_amount) AND (l_link_asset_tbl(i).amount >= l_compare_amt) THEN
1692
1693 l_adj_rec := i;
1694 l_compare_amt := l_link_asset_tbl(i).amount;
1695
1696 END IF;
1697
1698 END IF;
1699
1700 END LOOP;
1701
1702 IF l_assoc_total < l_line_amount THEN
1703
1704 l_link_asset_tbl(l_adj_rec).amount := l_link_asset_tbl(l_adj_rec).amount + l_diff;
1705
1706 ELSE
1707
1708 l_link_asset_tbl(l_adj_rec).amount := l_link_asset_tbl(l_adj_rec).amount - l_diff;
1709
1710 END IF;
1711
1712 END IF;
1713 */
1714 ------------------------------------------------------
1715 -- 4. Prepare arrays to pass to create and update APIs
1716 ------------------------------------------------------
1717 /*
1718 FOR i IN l_link_asset_tbl.FIRST .. l_link_asset_tbl.LAST LOOP
1719
1720 IF l_link_asset_tbl.EXISTS(i) THEN
1721
1722 l_assoc_amount := l_link_asset_tbl(i).amount;
1723
1724 END IF;
1725
1726 END LOOP;
1727 */
1728
1729 IF lp_klev_tbl.COUNT > 0 THEN
1730
1731 okl_contract_pvt.update_contract_line(
1732 p_api_version => p_api_version,
1733 p_init_msg_list => p_init_msg_list,
1734 x_return_status => x_return_status,
1735 x_msg_count => x_msg_count,
1736 x_msg_data => x_msg_data,
1737 p_clev_tbl => lp_clev_tbl,
1738 p_klev_tbl => lp_klev_tbl,
1739 x_clev_tbl => lx_clev_tbl,
1740 x_klev_tbl => lx_klev_tbl);
1741
1742
1743 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
1744 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1745 Elsif (x_return_status = OKC_API.G_RET_STS_ERROR) then
1746 raise OKC_API.G_EXCEPTION_ERROR;
1747 End If;
1748
1749 --Bug# 5102606
1750 FOR i IN lp_klev_tbl.FIRST .. lp_klev_tbl.LAST
1751 LOOP
1752
1753 recalculate_costs(
1754 p_api_version => p_api_version,
1755 p_init_msg_list => p_init_msg_list,
1756 x_return_status => x_return_status,
1757 x_msg_count => x_msg_count,
1758 x_msg_data => x_msg_data,
1759 p_chr_id => p_chr_id,
1760 p_cle_id => lp_klev_tbl(i).id);
1761
1762 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
1763 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1764 Elsif (x_return_status = OKC_API.G_RET_STS_ERROR) then
1765 raise OKC_API.G_EXCEPTION_ERROR;
1766 End If;
1767 END LOOP;
1768 --Bug# 5102606
1769
1770 END IF;
1771
1772 END IF;
1773
1774 OKC_API.END_ACTIVITY(x_msg_count => x_msg_count, x_msg_data => x_msg_data);
1775
1776 EXCEPTION
1777
1778 when OKC_API.G_EXCEPTION_ERROR then
1779 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
1780 p_api_name => l_api_name,
1781 p_pkg_name => g_pkg_name,
1782 p_exc_name => 'OKC_API.G_RET_STS_ERROR',
1783 x_msg_count => x_msg_count,
1784 x_msg_data => x_msg_data,
1785 p_api_type => g_api_type);
1786
1787 when OKC_API.G_EXCEPTION_UNEXPECTED_ERROR then
1788 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
1789 p_api_name => l_api_name,
1790 p_pkg_name => g_pkg_name,
1791 p_exc_name => 'OKC_API.G_RET_STS_UNEXP_ERROR',
1792 x_msg_count => x_msg_count,
1793 x_msg_data => x_msg_data,
1794 p_api_type => g_api_type);
1795
1796 when OTHERS then
1797 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
1798 p_api_name => l_api_name,
1799 p_pkg_name => g_pkg_name,
1800 p_exc_name => 'OTHERS',
1801 x_msg_count => x_msg_count,
1802 x_msg_data => x_msg_data,
1803 p_api_type => g_api_type);
1804 END allocate_amount_tradein;
1805
1806 --Bug# 5237504 Added the following procedure to remove trade-in info at
1807 -- contract level
1808 PROCEDURE delete_contract(
1809 p_api_version IN NUMBER,
1810 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1811 x_return_status OUT NOCOPY VARCHAR2,
1812 x_msg_count OUT NOCOPY NUMBER,
1813 x_msg_data OUT NOCOPY VARCHAR2,
1814 p_chr_id IN okl_k_headers.id%TYPE
1815 )IS
1816
1817 lp_chrv_rec okl_okc_migration_pvt.chrv_rec_type;
1818 lp_khrv_rec okl_khr_pvt.khrv_rec_type;
1819
1820 lx_chrv_rec okl_okc_migration_pvt.chrv_rec_type;
1821 lx_khrv_rec okl_khr_pvt.khrv_rec_type;
1822
1823 l_chr_id okc_k_headers_b.id%type := p_chr_id;
1824
1825 l_api_name CONSTANT VARCHAR2(30) := 'delete_contract';
1826 l_api_version CONSTANT NUMBER := 1.0;
1827 l_ak_prompt AK_ATTRIBUTES_VL.attribute_label_long%type;
1828
1829
1830 BEGIN
1831
1832 -- call START_ACTIVITY to create savepoint, check compatibility
1833 -- and initialize message list
1834 x_return_status := OKC_API.START_ACTIVITY(
1835 p_api_name => l_api_name,
1836 p_pkg_name => g_pkg_name,
1837 p_init_msg_list => p_init_msg_list,
1838 l_api_version => l_api_version,
1839 p_api_version => p_api_version,
1840 p_api_type => g_api_type,
1841 x_return_status => x_return_status);
1842
1843 -- check if activity started successfully
1844 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
1845 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1846 Elsif (x_return_status = OKC_API.G_RET_STS_ERROR) then
1847 raise OKC_API.G_EXCEPTION_ERROR;
1848 End If;
1849
1850 If(l_chr_id is null or l_chr_id = OKL_API.G_MISS_NUM) Then
1851 x_return_status := OKC_API.g_ret_sts_error;
1852 OKC_API.SET_MESSAGE( p_app_name => g_app_name
1853 , p_msg_name => 'OKL_REQUIRED_VALUE'
1854 , p_token1 => 'COL_NAME'
1855 , p_token1_value => 'DNZ_CHR_ID'
1856 );
1857 raise OKC_API.G_EXCEPTION_ERROR;
1858 End If;
1859
1860 If okl_context.get_okc_org_id is null then
1861 okl_context.set_okc_org_context(p_chr_id => l_chr_id );
1862 End If;
1863
1864 lp_chrv_rec.id := l_chr_id;
1865 lp_khrv_rec.id := l_chr_id;
1866 lp_khrv_rec.date_tradein := null;
1867 lp_khrv_rec.tradein_amount := null;
1868 lp_khrv_rec.tradein_description := null;
1869
1870 OKL_CONTRACT_PUB.update_contract_header(
1871 p_api_version => p_api_version,
1872 p_init_msg_list => p_init_msg_list,
1873 x_return_status => x_return_status,
1874 x_msg_count => x_msg_count,
1875 x_msg_data => x_msg_data,
1876 p_restricted_update => 'F',
1877 p_chrv_rec => lp_chrv_rec,
1878 p_khrv_rec => lp_khrv_rec,
1879 x_chrv_rec => lx_chrv_rec,
1880 x_khrv_rec => lx_khrv_rec);
1881
1882 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1883 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1884 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1885 RAISE OKC_API.G_EXCEPTION_ERROR;
1886 END IF;
1887
1888 x_return_status := OKC_API.g_ret_sts_success;
1889
1890 OKC_API.END_ACTIVITY(x_msg_count => x_msg_count, x_msg_data => x_msg_data);
1891
1892 EXCEPTION
1893 when OKC_API.G_EXCEPTION_ERROR then
1894 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
1895 p_api_name => l_api_name,
1896 p_pkg_name => g_pkg_name,
1897 p_exc_name => 'OKC_API.G_RET_STS_ERROR',
1898 x_msg_count => x_msg_count,
1899 x_msg_data => x_msg_data,
1900 p_api_type => g_api_type);
1901
1902 when OKC_API.G_EXCEPTION_UNEXPECTED_ERROR then
1903 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
1904 p_api_name => l_api_name,
1905 p_pkg_name => g_pkg_name,
1906 p_exc_name => 'OKC_API.G_RET_STS_UNEXP_ERROR',
1907 x_msg_count => x_msg_count,
1908 x_msg_data => x_msg_data,
1909 p_api_type => g_api_type);
1910
1911 when OTHERS then
1912 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
1913 p_api_name => l_api_name,
1914 p_pkg_name => g_pkg_name,
1915 p_exc_name => 'OTHERS',
1916 x_msg_count => x_msg_count,
1917 x_msg_data => x_msg_data,
1918 p_api_type => g_api_type);
1919
1920 END;
1921 --Bug# 5237504:end
1922
1923 END OKL_LA_TRADEIN_PVT;