[Home] [Help]
PACKAGE BODY: APPS.OKL_LEASE_QUOTE_SERVICE_PVT
Source
1 PACKAGE BODY OKL_LEASE_QUOTE_SERVICE_PVT AS
2 /* $Header: OKLRQUSB.pls 120.7.12010000.2 2009/11/09 23:56:40 sechawla ship $ */
3
4 ----------------------------
5 -- PROCEDURE validate_header
6 ----------------------------
7 PROCEDURE validate_header(
8 p_service_rec IN okl_svc_pvt.svcv_rec_type
9 ,x_return_status OUT NOCOPY VARCHAR2
10 ) IS
11
12 l_program_name CONSTANT VARCHAR2(30) := 'validate_header';
13 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
14
15 BEGIN
16 x_return_status := G_RET_STS_SUCCESS;
17 EXCEPTION
18
19 WHEN OKL_API.G_EXCEPTION_ERROR THEN
20 x_return_status := G_RET_STS_ERROR;
21
22 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
23 x_return_status := G_RET_STS_UNEXP_ERROR;
24
25 WHEN OTHERS THEN
26 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
27 p_msg_name => G_DB_ERROR,
28 p_token1 => G_PROG_NAME_TOKEN,
29 p_token1_value => l_program_name,
30 p_token2 => G_SQLCODE_TOKEN,
31 p_token2_value => sqlcode,
32 p_token3 => G_SQLERRM_TOKEN,
33 p_token3_value => sqlerrm);
34
35 x_return_status := G_RET_STS_UNEXP_ERROR;
36
37 END validate_header;
38
39
40 ------------------------------
41 -- PROCEDURE get_currency_code
42 ------------------------------
43 PROCEDURE get_currency_code (
44 p_parent_object_id IN NUMBER
45 ,x_currency_code OUT NOCOPY VARCHAR2
46 ,x_return_status OUT NOCOPY VARCHAR2
47 ) IS
48
49 l_program_name CONSTANT VARCHAR2(30) := 'get_currency_code';
50 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
51
52 l_parent_object_code VARCHAR2(30);
53
54 BEGIN
55
56 SELECT parent_object_code
57 INTO l_parent_object_code
58 FROM okl_lease_quotes_b
59 WHERE id = p_parent_object_id;
60
61 IF (l_parent_object_code = 'LEASEOPP') THEN
62 SELECT currency_code
63 INTO x_currency_code
64 FROM okl_lease_opportunities_b lop,
65 okl_lease_quotes_b lsq
66 WHERE lsq.parent_object_code = l_parent_object_code
67 AND lsq.parent_object_id = lop.id
68 AND lsq.id = p_parent_object_id;
69 ELSIF (l_parent_object_code = 'LEASEAPP') THEN
70 SELECT currency_code
71 INTO x_currency_code
72 FROM okl_lease_applications_b lap,
73 okl_lease_quotes_b lsq
74 WHERE lsq.parent_object_code = l_parent_object_code
75 AND lsq.parent_object_id = lap.id
76 AND lsq.id = p_parent_object_id;
77 END IF;
78
79 x_return_status := G_RET_STS_SUCCESS;
80
81 EXCEPTION
82
83 WHEN OKL_API.G_EXCEPTION_ERROR THEN
84 x_return_status := G_RET_STS_ERROR;
85
86 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
87 x_return_status := G_RET_STS_UNEXP_ERROR;
88
89 WHEN OTHERS THEN
90 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
91 p_msg_name => G_DB_ERROR,
92 p_token1 => G_PROG_NAME_TOKEN,
93 p_token1_value => l_program_name,
94 p_token2 => G_SQLCODE_TOKEN,
95 p_token2_value => sqlcode,
96 p_token3 => G_SQLERRM_TOKEN,
97 p_token3_value => sqlerrm);
98
99 x_return_status := G_RET_STS_UNEXP_ERROR;
100
101 END get_currency_code;
102
103
104 ----------------------------
105 -- PROCEDURE get_service_rec
106 ----------------------------
107 PROCEDURE get_service_rec (
108 p_service_id IN NUMBER
109 ,x_service_rec OUT NOCOPY okl_svc_pvt.svcv_rec_type
110 ,x_return_status OUT NOCOPY VARCHAR2
111 ) IS
112
113 l_program_name CONSTANT VARCHAR2(30) := 'get_service_rec';
114 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
115
116 BEGIN
117
118 SELECT
119 attribute_category
120 ,attribute1
121 ,attribute2
122 ,attribute3
123 ,attribute4
124 ,attribute5
125 ,attribute6
126 ,attribute7
127 ,attribute8
128 ,attribute9
129 ,attribute10
130 ,attribute11
131 ,attribute12
132 ,attribute13
133 ,attribute14
134 ,attribute15
135 ,inv_item_id
136 ,parent_object_code
137 ,parent_object_id
138 ,effective_from
139 ,supplier_id
140 ,short_description
141 ,description
142 ,comments
143 INTO
144 x_service_rec.attribute_category
145 ,x_service_rec.attribute1
146 ,x_service_rec.attribute2
147 ,x_service_rec.attribute3
148 ,x_service_rec.attribute4
149 ,x_service_rec.attribute5
150 ,x_service_rec.attribute6
151 ,x_service_rec.attribute7
152 ,x_service_rec.attribute8
153 ,x_service_rec.attribute9
154 ,x_service_rec.attribute10
155 ,x_service_rec.attribute11
156 ,x_service_rec.attribute12
157 ,x_service_rec.attribute13
158 ,x_service_rec.attribute14
159 ,x_service_rec.attribute15
160 ,x_service_rec.inv_item_id
161 ,x_service_rec.parent_object_code
162 ,x_service_rec.parent_object_id
163 ,x_service_rec.effective_from
164 ,x_service_rec.supplier_id
165 ,x_service_rec.short_description
166 ,x_service_rec.description
167 ,x_service_rec.comments
168 FROM okl_services_v
169 WHERE id = p_service_id;
170
171 x_return_status := G_RET_STS_SUCCESS;
172
173 EXCEPTION
174
175 WHEN OKL_API.G_EXCEPTION_ERROR THEN
176 x_return_status := G_RET_STS_ERROR;
177
178 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
179 x_return_status := G_RET_STS_UNEXP_ERROR;
180
181 WHEN OTHERS THEN
182 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
183 p_msg_name => G_DB_ERROR,
184 p_token1 => G_PROG_NAME_TOKEN,
185 p_token1_value => l_program_name,
186 p_token2 => G_SQLCODE_TOKEN,
187 p_token2_value => sqlcode,
188 p_token3 => G_SQLERRM_TOKEN,
189 p_token3_value => sqlerrm);
190
191 x_return_status := G_RET_STS_UNEXP_ERROR;
192
193 END get_service_rec;
194
195
196 ---------------------------------
197 -- PROCEDURE validate_link_assets
198 ---------------------------------
199 PROCEDURE validate_link_assets (p_service_amount IN NUMBER,
200 p_assoc_assets_tbl IN line_relation_tbl_type,
201 x_derive_assoc_amt OUT NOCOPY VARCHAR2,
202 x_return_status OUT NOCOPY VARCHAR2) IS
203
204 l_program_name CONSTANT VARCHAR2(30) := 'validate_link_assets';
205 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
206
207 l_link_exists_yn VARCHAR2(1);
208 l_assoc_total NUMBER;
209 l_amt_flag VARCHAR2(1);
210
211 BEGIN
212
213 l_assoc_total := 0;
214 l_amt_flag := 'N';
215
216 FOR i IN p_assoc_assets_tbl.FIRST .. p_assoc_assets_tbl.LAST LOOP
217 IF p_assoc_assets_tbl.EXISTS(i) THEN
218 IF p_assoc_assets_tbl(i).amount IS NOT NULL THEN
219 l_amt_flag := 'Y';
220 l_assoc_total := l_assoc_total + p_assoc_assets_tbl(i).amount;
221 END IF;
222 IF (p_assoc_assets_tbl(i).amount IS NULL) AND l_amt_flag = 'Y' THEN
223 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME, p_msg_name => 'OKL_LINKASSET_NULL_FOUND');
224 RAISE OKL_API.G_EXCEPTION_ERROR;
225 END IF;
226 END IF;
227 END LOOP;
228
229 IF l_amt_flag = 'Y' AND l_assoc_total <> p_service_amount THEN
230 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
231 p_msg_name => 'OKL_LINKASSET_AMT_MISMATCH',
232 p_token1 => 'LINE_COST',
233 p_token1_value => p_service_amount,
234 p_token2 => 'ASSOC_TOTAL',
235 p_token2_value => l_assoc_total);
236 RAISE OKL_API.G_EXCEPTION_ERROR;
237 END IF;
238
239 IF l_amt_flag = 'Y' THEN
240 x_derive_assoc_amt := 'N';
241 ELSE
242 x_derive_assoc_amt := 'Y';
243 END IF;
244
245 x_return_status := G_RET_STS_SUCCESS;
246
247 EXCEPTION
248
249 WHEN OKL_API.G_EXCEPTION_ERROR THEN
250 x_return_status := G_RET_STS_ERROR;
251
252 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
253 x_return_status := G_RET_STS_UNEXP_ERROR;
254
255 WHEN OTHERS THEN
256 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
257 p_msg_name => G_DB_ERROR,
258 p_token1 => G_PROG_NAME_TOKEN,
259 p_token1_value => l_program_name,
260 p_token2 => G_SQLCODE_TOKEN,
261 p_token2_value => sqlcode,
262 p_token3 => G_SQLERRM_TOKEN,
263 p_token3_value => sqlerrm);
264 x_return_status := G_RET_STS_UNEXP_ERROR;
265
266 END validate_link_assets;
267
268
269 ---------------------------------------
270 -- PROCEDURE process_link_asset_amounts
271 ---------------------------------------
272 PROCEDURE process_link_asset_amounts (
273 p_quote_id IN NUMBER
274 ,p_currency_code IN VARCHAR2
275 ,p_service_amount IN NUMBER
276 ,p_link_asset_tbl IN OUT NOCOPY line_relation_tbl_type
277 ,p_derive_assoc_amt IN VARCHAR2
278 ,p_override_pricing_type IN VARCHAR2 DEFAULT 'N'
279 ,x_return_status OUT NOCOPY VARCHAR2
280 ,x_msg_count OUT NOCOPY NUMBER
281 ,x_msg_data OUT NOCOPY VARCHAR2
282 ) IS
283
284 l_program_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||'process_link_asset_amounts';
285
286 l_link_asset_tbl line_relation_tbl_type;
287
288 l_line_amount NUMBER;
289 l_asset_oec NUMBER;
290 l_oec_total NUMBER;
291 l_assoc_amount NUMBER;
292 l_assoc_total NUMBER;
293 l_currency_code VARCHAR2(15);
294 lv_parent_object_code VARCHAR2(30);
295 l_compare_amt NUMBER;
296 l_diff NUMBER;
297 l_adj_rec BINARY_INTEGER;
298 lx_return_status VARCHAR2(1);
299 lv_pricing_method OKL_LEASE_QUOTES_B.PRICING_METHOD%TYPE;
300
301 BEGIN
302
303 l_link_asset_tbl := p_link_asset_tbl;
304 l_line_amount := p_service_amount;
305
306 SELECT PRICING_METHOD
307 INTO lv_pricing_method
308 FROM OKL_LEASE_QUOTES_B
309 WHERE ID = p_quote_id;
310
311 -- Service Asset amount will be null in case of 'Solve for Financed Amount' pricing
312 -- method .. the values will be populated after the Pricing call is made
313 IF (lv_pricing_method = 'SF' AND p_override_pricing_type = 'N') THEN
314 FOR i IN l_link_asset_tbl.FIRST .. l_link_asset_tbl.LAST LOOP
315 IF l_link_asset_tbl.EXISTS(i) THEN
316 l_link_asset_tbl(i).amount := null;
317 END IF;
318 END LOOP;
319
320 p_link_asset_tbl := l_link_asset_tbl;
321 RETURN;
322 END IF;
323
324 ------------------------------------------------------------------
325 -- 1. Loop through to get OEC total of all assets being associated
326 ------------------------------------------------------------------
327 FOR i IN l_link_asset_tbl.FIRST .. l_link_asset_tbl.LAST LOOP
328
329 IF l_link_asset_tbl.EXISTS(i) THEN
330
331 SELECT NVL(OEC, 0)
332 INTO l_asset_oec
333 FROM okl_assets_b
334 WHERE id = l_link_asset_tbl(i).source_line_id;
335
336 l_oec_total := l_oec_total + l_asset_oec;
337
338 END IF;
339
340 END LOOP;
341
342 ----------------------------------------------------------------------------
343 -- 2. Loop through to determine associated amounts and round off the amounts
344 ----------------------------------------------------------------------------
345 FOR i IN l_link_asset_tbl.FIRST .. l_link_asset_tbl.LAST LOOP
346
347 IF l_link_asset_tbl.EXISTS(i) THEN
348
349 IF p_derive_assoc_amt = 'N' THEN
350
351 l_assoc_amount := l_link_asset_tbl(i).amount;
352
353 ELSIF l_oec_total = 0 THEN
354
355 l_assoc_amount := l_line_amount / l_link_asset_tbl.COUNT;
356
357 ELSE
358
359 SELECT NVL(OEC, 0)
360 INTO l_asset_oec
361 FROM okl_assets_b
362 WHERE id = l_link_asset_tbl(i).source_line_id;
363
364 IF l_link_asset_tbl.COUNT = 1 THEN
365
366 l_assoc_amount := l_line_amount;
367
368 ELSE
369
370 l_assoc_amount := l_line_amount * l_asset_oec / l_oec_total;
371
372 END IF;
373 END IF;
374
375 l_assoc_amount := okl_accounting_util.round_amount(p_amount => l_assoc_amount,
376 p_currency_code => p_currency_code);
377
378 l_assoc_total := l_assoc_total + l_assoc_amount;
379
380
381 l_link_asset_tbl(i).amount := l_assoc_amount;
382
383 END IF;
384
385 END LOOP;
386
387 ----------------------------------------------------------------------------------------------------
388 -- 3. Adjust associated amount if associated total does not tally up with line amount after rounding
389 ----------------------------------------------------------------------------------------------------
390 IF l_assoc_total <> l_line_amount THEN
391
392 l_diff := ABS(l_assoc_total - l_line_amount);
393
394 FOR i IN l_link_asset_tbl.FIRST .. l_link_asset_tbl.LAST LOOP
395
396 IF l_link_asset_tbl.EXISTS(i) THEN
397
398 -- if the total split amount is less than line amount add the difference amount to the
399 -- asset with less amount and if the total split amount is greater than the line amount
400 -- than subtract the difference amount from the asset with highest amount
401
402 IF i = l_link_asset_tbl.FIRST THEN
403
404 l_adj_rec := i;
405 l_compare_amt := l_link_asset_tbl(i).amount;
406
407 ELSIF (l_assoc_total < l_line_amount) AND (l_link_asset_tbl(i).amount <= l_compare_amt) OR
408 (l_assoc_total > l_line_amount) AND (l_link_asset_tbl(i).amount >= l_compare_amt) THEN
409
410 l_adj_rec := i;
411 l_compare_amt := l_link_asset_tbl(i).amount;
412
413 END IF;
414
415 END IF;
416
417 END LOOP;
418
419 IF l_assoc_total < l_line_amount THEN
420
421 l_link_asset_tbl(l_adj_rec).amount := l_link_asset_tbl(l_adj_rec).amount + l_diff;
422
423 ELSE
424
425 l_link_asset_tbl(l_adj_rec).amount := l_link_asset_tbl(l_adj_rec).amount - l_diff;
426
427 END IF;
428
429 END IF;
430
431 p_link_asset_tbl := l_link_asset_tbl;
432 x_return_status := G_RET_STS_SUCCESS;
433
434 EXCEPTION
435
436 WHEN OKL_API.G_EXCEPTION_ERROR THEN
437 x_return_status := G_RET_STS_ERROR;
438
439 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
440 x_return_status := G_RET_STS_UNEXP_ERROR;
441
442 WHEN OTHERS THEN
443 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
444 p_msg_name => G_DB_ERROR,
445 p_token1 => G_PROG_NAME_TOKEN,
446 p_token1_value => l_program_name,
447 p_token2 => G_SQLCODE_TOKEN,
448 p_token2_value => sqlcode,
449 p_token3 => G_SQLERRM_TOKEN,
450 p_token3_value => sqlerrm);
451
452 x_return_status := G_RET_STS_UNEXP_ERROR;
453
454 END process_link_asset_amounts;
455
456
457 -------------------------------------
458 -- PROCEDURE get_deleted_assoc_assets
459 -------------------------------------
460 PROCEDURE get_deleted_assoc_assets (p_service_id IN NUMBER,
461 p_assoc_asset_tbl IN line_relation_tbl_type,
462 x_deleted_assoc_asset_tbl OUT NOCOPY okl_lre_pvt.lrev_tbl_type,
463 x_return_status OUT NOCOPY VARCHAR2) IS
464
465 l_program_name CONSTANT VARCHAR2(30) := 'get_deleted_assoc_assets';
466 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
467
468 CURSOR c_db_assoc_assets IS
469 SELECT id
470 FROM okl_line_relationships_b
471 WHERE related_line_type = 'SERVICE'
472 AND related_line_id = p_service_id;
473
474 l_assoc_asset_tbl okl_lre_pvt.lrev_tbl_type;
475 l_delete_flag VARCHAR2(1);
476 i BINARY_INTEGER := 0;
477
478 BEGIN
479 IF (p_assoc_asset_tbl.COUNT > 0) THEN
480 FOR l_db_assoc_assets IN c_db_assoc_assets LOOP
481 l_delete_flag := 'Y';
482 FOR j IN p_assoc_asset_tbl.FIRST .. p_assoc_asset_tbl.LAST LOOP
483 IF p_assoc_asset_tbl.EXISTS(j) THEN
484 IF l_db_assoc_assets.id = p_assoc_asset_tbl(j).id THEN
485 l_delete_flag := 'N';
486 END IF;
487 END IF;
488 END LOOP;
489
490 IF l_delete_flag = 'Y' THEN
491 l_assoc_asset_tbl(i).id := l_db_assoc_assets.id;
492 i := i + 1;
493 END IF;
494 END LOOP;
495 ELSE
496 FOR l_db_assoc_assets IN c_db_assoc_assets LOOP
497 l_assoc_asset_tbl(i).id := l_db_assoc_assets.id;
498 i := i + 1;
499 END LOOP;
500 END IF;
501
502 x_deleted_assoc_asset_tbl := l_assoc_asset_tbl;
503 x_return_status := G_RET_STS_SUCCESS;
504
505 EXCEPTION
506
507 WHEN OKL_API.G_EXCEPTION_ERROR THEN
508 x_return_status := G_RET_STS_ERROR;
509
510 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
511 x_return_status := G_RET_STS_UNEXP_ERROR;
512
513 WHEN OTHERS THEN
514 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
515 p_msg_name => G_DB_ERROR,
516 p_token1 => G_PROG_NAME_TOKEN,
517 p_token1_value => l_program_name,
518 p_token2 => G_SQLCODE_TOKEN,
519 p_token2_value => sqlcode,
520 p_token3 => G_SQLERRM_TOKEN,
521 p_token3_value => sqlerrm);
522
523 x_return_status := G_RET_STS_UNEXP_ERROR;
524
525 END get_deleted_assoc_assets;
526
527
528 -------------------------------------
529 -- PROCEDURE create_line_associations
530 -------------------------------------
531 PROCEDURE create_line_associations (
532 p_service_id IN NUMBER
533 ,p_assoc_assets_tbl IN line_relation_tbl_type
534 ,x_return_status OUT NOCOPY VARCHAR2
535 ,x_msg_count OUT NOCOPY NUMBER
536 ,x_msg_data OUT NOCOPY VARCHAR2) IS
537
538 l_program_name CONSTANT VARCHAR2(30) := 'create_line_associations';
539 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
540
541 l_assoc_assets_tbl okl_lre_pvt.lrev_tbl_type;
542 lx_assoc_assets_tbl okl_lre_pvt.lrev_tbl_type;
543
544 l_line_relation_tbl line_relation_tbl_type;
545
546 BEGIN
547
548 l_line_relation_tbl := p_assoc_assets_tbl;
549
550 FOR i IN l_line_relation_tbl.FIRST .. l_line_relation_tbl.LAST LOOP
551 IF l_line_relation_tbl.EXISTS(i) THEN
552 l_assoc_assets_tbl(i).related_line_id := p_service_id;
553 l_assoc_assets_tbl(i).related_line_type := l_line_relation_tbl(i).related_line_type;
554 l_assoc_assets_tbl(i).source_line_type := l_line_relation_tbl(i).source_line_type;
555 l_assoc_assets_tbl(i).source_line_id := l_line_relation_tbl(i).source_line_id;
556 l_assoc_assets_tbl(i).amount := l_line_relation_tbl(i).amount;
557 l_assoc_assets_tbl(i).short_description := l_line_relation_tbl(i).short_description;
558 l_assoc_assets_tbl(i).description := l_line_relation_tbl(i).description;
559 l_assoc_assets_tbl(i).comments := l_line_relation_tbl(i).comments;
560 END IF;
561 END LOOP;
562
563 okl_lre_pvt.insert_row (
564 p_api_version => G_API_VERSION
565 ,p_init_msg_list => G_FALSE
566 ,x_return_status => x_return_status
567 ,x_msg_count => x_msg_count
568 ,x_msg_data => x_msg_data
569 ,p_lrev_tbl => l_assoc_assets_tbl
570 ,x_lrev_tbl => lx_assoc_assets_tbl
571 );
572
573 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
574 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
575 ELSIF x_return_status = G_RET_STS_ERROR THEN
576 RAISE OKL_API.G_EXCEPTION_ERROR;
577 END IF;
578
579 EXCEPTION
580
581 WHEN OKL_API.G_EXCEPTION_ERROR THEN
582 x_return_status := G_RET_STS_ERROR;
583
584 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
585 x_return_status := G_RET_STS_UNEXP_ERROR;
586
587 WHEN OTHERS THEN
588 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
589 p_msg_name => G_DB_ERROR,
590 p_token1 => G_PROG_NAME_TOKEN,
591 p_token1_value => l_program_name,
592 p_token2 => G_SQLCODE_TOKEN,
593 p_token2_value => sqlcode,
594 p_token3 => G_SQLERRM_TOKEN,
595 p_token3_value => sqlerrm);
596
597 x_return_status := G_RET_STS_UNEXP_ERROR;
598
599 END create_line_associations;
600
601
602 -------------------------------------
603 -- PROCEDURE update_line_associations
604 -------------------------------------
605 PROCEDURE update_line_associations (
606 p_service_id IN NUMBER
607 ,p_assoc_assets_tbl IN line_relation_tbl_type
608 ,x_return_status OUT NOCOPY VARCHAR2
609 ,x_msg_count OUT NOCOPY NUMBER
610 ,x_msg_data OUT NOCOPY VARCHAR2 ) IS
611
612 l_program_name CONSTANT VARCHAR2(30) := 'update_line_associations';
613 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
614
615 l_assoc_create_tbl okl_lre_pvt.lrev_tbl_type;
616 l_assoc_update_tbl okl_lre_pvt.lrev_tbl_type;
617 lx_assoc_assets_tbl okl_lre_pvt.lrev_tbl_type;
618
619 l_line_relation_tbl line_relation_tbl_type;
620 l_deleted_assoc_assets_tbl okl_lre_pvt.lrev_tbl_type;
621
622 BEGIN
623
624 l_line_relation_tbl := p_assoc_assets_tbl;
625
626 get_deleted_assoc_assets (
627 p_service_id => p_service_id
628 ,p_assoc_asset_tbl => l_line_relation_tbl
629 ,x_deleted_assoc_asset_tbl => l_deleted_assoc_assets_tbl
630 ,x_return_status => x_return_status
631 );
632
633 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
634 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
635 ELSIF x_return_status = G_RET_STS_ERROR THEN
636 RAISE OKL_API.G_EXCEPTION_ERROR;
637 END IF;
638
639 IF l_deleted_assoc_assets_tbl.COUNT > 0 THEN
640 okl_lre_pvt.delete_row (
641 p_api_version => G_API_VERSION
642 ,p_init_msg_list => G_FALSE
643 ,x_return_status => x_return_status
644 ,x_msg_count => x_msg_count
645 ,x_msg_data => x_msg_data
646 ,p_lrev_tbl => l_deleted_assoc_assets_tbl );
647 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
648 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
649 ELSIF x_return_status = G_RET_STS_ERROR THEN
650 RAISE OKL_API.G_EXCEPTION_ERROR;
651 END IF;
652 END IF;
653
654 IF l_line_relation_tbl.COUNT > 0 THEN
655
656 FOR i IN l_line_relation_tbl.FIRST .. l_line_relation_tbl.LAST LOOP
657
658 IF l_line_relation_tbl.EXISTS(i) THEN
659
660 IF UPPER(l_line_relation_tbl(i).record_mode) = 'UPDATE' THEN
661
662 l_assoc_update_tbl(i).id := l_line_relation_tbl(i).id;
663 l_assoc_update_tbl(i).object_version_number := l_line_relation_tbl(i).object_version_number;
664 l_assoc_update_tbl(i).related_line_id := p_service_id;
665 l_assoc_update_tbl(i).related_line_type := l_line_relation_tbl(i).related_line_type;
666 l_assoc_update_tbl(i).source_line_type := l_line_relation_tbl(i).source_line_type;
667 l_assoc_update_tbl(i).source_line_id := l_line_relation_tbl(i).source_line_id;
668 l_assoc_update_tbl(i).amount := l_line_relation_tbl(i).amount;
669
670 ELSIF UPPER(l_line_relation_tbl(i).record_mode) = 'CREATE' THEN
671
672 l_assoc_create_tbl(i).related_line_id := p_service_id;
673 l_assoc_create_tbl(i).related_line_type := l_line_relation_tbl(i).related_line_type;
674 l_assoc_create_tbl(i).source_line_type := l_line_relation_tbl(i).source_line_type;
675 l_assoc_create_tbl(i).source_line_id := l_line_relation_tbl(i).source_line_id;
676 l_assoc_create_tbl(i).amount := l_line_relation_tbl(i).amount;
677
678 END IF;
679
680 END IF;
681
682 END LOOP;
683
684 END IF;
685
686 IF l_assoc_update_tbl.COUNT > 0 THEN
687
688 okl_lre_pvt.update_row (
689 p_api_version => G_API_VERSION
690 ,p_init_msg_list => G_FALSE
691 ,x_return_status => x_return_status
692 ,x_msg_count => x_msg_count
693 ,x_msg_data => x_msg_data
694 ,p_lrev_tbl => l_assoc_update_tbl
695 ,x_lrev_tbl => lx_assoc_assets_tbl
696 );
697
698 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
699 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
700 ELSIF x_return_status = G_RET_STS_ERROR THEN
701 RAISE OKL_API.G_EXCEPTION_ERROR;
702 END IF;
703
704 END IF;
705
706 IF l_assoc_create_tbl.COUNT > 0 THEN
707
708 okl_lre_pvt.insert_row (
709 p_api_version => G_API_VERSION
710 ,p_init_msg_list => G_FALSE
711 ,x_return_status => x_return_status
712 ,x_msg_count => x_msg_count
713 ,x_msg_data => x_msg_data
714 ,p_lrev_tbl => l_assoc_create_tbl
715 ,x_lrev_tbl => lx_assoc_assets_tbl
716 );
717
718 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
719 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
720 ELSIF x_return_status = G_RET_STS_ERROR THEN
721 RAISE OKL_API.G_EXCEPTION_ERROR;
722 END IF;
723
724 END IF;
725
726 x_return_status := G_RET_STS_SUCCESS;
727
728 EXCEPTION
729
730 WHEN OKL_API.G_EXCEPTION_ERROR THEN
731 x_return_status := G_RET_STS_ERROR;
732
733 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
734 x_return_status := G_RET_STS_UNEXP_ERROR;
735
736 WHEN OTHERS THEN
737 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
738 p_msg_name => G_DB_ERROR,
739 p_token1 => G_PROG_NAME_TOKEN,
740 p_token1_value => l_program_name,
741 p_token2 => G_SQLCODE_TOKEN,
742 p_token2_value => sqlcode,
743 p_token3 => G_SQLERRM_TOKEN,
744 p_token3_value => sqlerrm);
745
746 x_return_status := G_RET_STS_UNEXP_ERROR;
747
748 END update_line_associations;
749
750
751 -------------------------------
752 -- PROCEDURE get_lrship_tbl
753 -------------------------------
754 PROCEDURE get_lrship_tbl (p_source_service_id IN NUMBER
755 ,p_target_service_id IN NUMBER
756 ,x_lrship_tbl OUT NOCOPY lr_tbl_type
757 ,x_return_status OUT NOCOPY VARCHAR2 ) IS
758
759 l_program_name CONSTANT VARCHAR2(30) := 'get_lrship_tbl';
760 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
761 i BINARY_INTEGER := 0;
762
763 CURSOR c_db_lrships IS
764 SELECT
765 attribute_category
766 ,attribute1
767 ,attribute2
768 ,attribute3
769 ,attribute4
770 ,attribute5
771 ,attribute6
772 ,attribute7
773 ,attribute8
774 ,attribute9
775 ,attribute10
776 ,attribute11
777 ,attribute12
778 ,attribute13
779 ,attribute14
780 ,attribute15
781 ,source_line_type
782 ,source_line_id
783 ,related_line_type
784 ,related_line_id
785 ,amount
786 ,short_description
787 ,description
788 ,comments
789 from okl_line_relationships_v
790 where source_line_type = 'ASSET'
791 and related_line_id = p_source_service_id;
792
793 BEGIN
794 FOR l_db_lrships IN c_db_lrships LOOP
795 x_lrship_tbl(i).attribute_category := l_db_lrships.attribute_category;
796 x_lrship_tbl(i).attribute1 := l_db_lrships.attribute1;
797 x_lrship_tbl(i).attribute2 := l_db_lrships.attribute2;
798 x_lrship_tbl(i).attribute3 := l_db_lrships.attribute3;
799 x_lrship_tbl(i).attribute4 := l_db_lrships.attribute4;
800 x_lrship_tbl(i).attribute5 := l_db_lrships.attribute5;
801 x_lrship_tbl(i).attribute6 := l_db_lrships.attribute6;
802 x_lrship_tbl(i).attribute7 := l_db_lrships.attribute7;
803 x_lrship_tbl(i).attribute8 := l_db_lrships.attribute8;
804 x_lrship_tbl(i).attribute9 := l_db_lrships.attribute9;
805 x_lrship_tbl(i).attribute10 := l_db_lrships.attribute10;
806 x_lrship_tbl(i).attribute11 := l_db_lrships.attribute11;
807 x_lrship_tbl(i).attribute12 := l_db_lrships.attribute12;
808 x_lrship_tbl(i).attribute13 := l_db_lrships.attribute13;
809 x_lrship_tbl(i).attribute14 := l_db_lrships.attribute14;
810 x_lrship_tbl(i).attribute15 := l_db_lrships.attribute15;
811 x_lrship_tbl(i).source_line_type := l_db_lrships.source_line_type;
812 x_lrship_tbl(i).source_line_id := l_db_lrships.source_line_id ;
813 x_lrship_tbl(i).related_line_type := l_db_lrships.related_line_type;
814 x_lrship_tbl(i).related_line_id := p_target_service_id;
815 x_lrship_tbl(i).amount := l_db_lrships.amount;
816 x_lrship_tbl(i).short_description := l_db_lrships.short_description;
817 x_lrship_tbl(i).description := l_db_lrships.description;
818 x_lrship_tbl(i).comments := l_db_lrships.comments;
819 i := i + 1;
820 END LOOP;
821
822 x_return_status := G_RET_STS_SUCCESS;
823 EXCEPTION
824
825 WHEN OKL_API.G_EXCEPTION_ERROR THEN
826 x_return_status := G_RET_STS_ERROR;
827
828 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
829 x_return_status := G_RET_STS_UNEXP_ERROR;
830
831 WHEN OTHERS THEN
832 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
833 p_msg_name => G_DB_ERROR,
834 p_token1 => G_PROG_NAME_TOKEN,
835 p_token1_value => l_program_name,
836 p_token2 => G_SQLCODE_TOKEN,
837 p_token2_value => sqlcode,
838 p_token3 => G_SQLERRM_TOKEN,
839 p_token3_value => sqlerrm);
840
841 x_return_status := G_RET_STS_UNEXP_ERROR;
842
843 END get_lrship_tbl;
844
845 -----------------------------------
846 -- PROCEDURE copy_line_associations
847 -----------------------------------
848 PROCEDURE copy_line_associations( p_source_service_id IN NUMBER,
849 p_target_service_id IN NUMBER,
850 x_return_status OUT NOCOPY VARCHAR2,
851 x_msg_count OUT NOCOPY VARCHAR2,
852 x_msg_data OUT NOCOPY VARCHAR2 ) IS
853
854 l_program_name CONSTANT VARCHAR2(30) := 'copy_line_associations';
855 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
856
857 l_lrship_tbl lr_tbl_type;
858 lx_lrship_tbl lr_tbl_type;
859
860
861 --05-Nov-2009 sechawla 9004863
862 /*
863 CURSOR c_get_new_asset_id (p_source_asset_id IN NUMBER) IS
864 SELECT id
865 FROM OKL_ASSETS_B
866 WHERE ORIG_ASSET_ID = p_source_asset_id;
867 */
868 --05-Nov-2009 sechawla 9004863 : Modified the cursor to get asset id based on the new lease quote
869 CURSOR c_get_new_asset_id (p_source_asset_id IN NUMBER) IS
870 SELECT AST.id
871 FROM OKL_ASSETS_B AST,
872 OKL_SERVICES_B SRV
873 WHERE SRV.PARENT_OBJECT_CODE = 'LEASEQUOTE'
874 AND SRV.PARENT_OBJECT_ID = AST.PARENT_OBJECT_ID
875 AND SRV.ID = p_target_service_id
876 AND AST.ORIG_ASSET_ID = p_source_asset_id;
877
878
879 BEGIN
880 -- Get line relationships table
881 get_lrship_tbl (p_source_service_id => p_source_service_id,
882 p_target_service_id => p_target_service_id,
883 x_lrship_tbl => l_lrship_tbl,
884 x_return_status => x_return_status);
885 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
886 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
887 ELSIF x_return_status = G_RET_STS_ERROR THEN
888 RAISE OKL_API.G_EXCEPTION_ERROR;
889 END IF;
890
891 -- Set the original asset id for the records
892 IF (l_lrship_tbl.COUNT > 0) THEN
893 FOR i IN l_lrship_tbl.FIRST .. l_lrship_tbl.LAST LOOP
894 IF l_lrship_tbl.EXISTS(i) THEN
895 OPEN c_get_new_asset_id(p_source_asset_id => l_lrship_tbl(i).source_line_id);
896 FETCH c_get_new_asset_id INTO l_lrship_tbl(i).source_line_id;
897 CLOSE c_get_new_asset_id;
898 END IF;
899 END LOOP;
900
901 okl_lre_pvt.insert_row (
902 p_api_version => G_API_VERSION
903 ,p_init_msg_list => G_FALSE
904 ,x_return_status => x_return_status
905 ,x_msg_count => x_msg_count
906 ,x_msg_data => x_msg_data
907 ,p_lrev_tbl => l_lrship_tbl
908 ,x_lrev_tbl => lx_lrship_tbl );
909
910 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
911 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
912 ELSIF x_return_status = G_RET_STS_ERROR THEN
913 RAISE OKL_API.G_EXCEPTION_ERROR;
914 END IF;
915 END IF;
916
917 x_return_status := G_RET_STS_SUCCESS;
918
919 EXCEPTION
920
921 WHEN OKL_API.G_EXCEPTION_ERROR THEN
922
923 x_return_status := G_RET_STS_ERROR;
924
925 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
926
927 x_return_status := G_RET_STS_UNEXP_ERROR;
928
929 WHEN OTHERS THEN
930
931 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
932 p_msg_name => G_DB_ERROR,
933 p_token1 => G_PROG_NAME_TOKEN,
934 p_token1_value => l_api_name,
935 p_token2 => G_SQLCODE_TOKEN,
936 p_token2_value => sqlcode,
937 p_token3 => G_SQLERRM_TOKEN,
938 p_token3_value => sqlerrm);
939
940 x_return_status := G_RET_STS_UNEXP_ERROR;
941 END copy_line_associations;
942
943
944 --------------------------
945 -- PROCEDURE create_header
946 --------------------------
947 PROCEDURE create_header (
948 p_service_rec IN okl_svc_pvt.svcv_rec_type
949 ,x_service_id OUT NOCOPY NUMBER
950 ,x_return_status OUT NOCOPY VARCHAR2
951 ,x_msg_count OUT NOCOPY NUMBER
952 ,x_msg_data OUT NOCOPY VARCHAR2
953 ) IS
954
955 l_program_name CONSTANT VARCHAR2(30) := 'create_header';
956 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
957
958 lx_service_rec okl_svc_pvt.svcv_rec_type;
959
960 BEGIN
961
962 okl_svc_pvt.insert_row (
963 p_api_version => G_API_VERSION
964 ,p_init_msg_list => G_FALSE
965 ,x_return_status => x_return_status
966 ,x_msg_count => x_msg_count
967 ,x_msg_data => x_msg_data
968 ,p_svcv_rec => p_service_rec
969 ,x_svcv_rec => lx_service_rec
970 );
971
972 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
973 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
974 ELSIF x_return_status = G_RET_STS_ERROR THEN
975 RAISE OKL_API.G_EXCEPTION_ERROR;
976 END IF;
977
978 x_service_id := lx_service_rec.id;
979
980 EXCEPTION
981
982 WHEN OKL_API.G_EXCEPTION_ERROR THEN
983 x_return_status := G_RET_STS_ERROR;
984
985 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
986 x_return_status := G_RET_STS_UNEXP_ERROR;
987
988 WHEN OTHERS THEN
989 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
990 p_msg_name => G_DB_ERROR,
991 p_token1 => G_PROG_NAME_TOKEN,
992 p_token1_value => l_program_name,
993 p_token2 => G_SQLCODE_TOKEN,
994 p_token2_value => sqlcode,
995 p_token3 => G_SQLERRM_TOKEN,
996 p_token3_value => sqlerrm);
997
998 x_return_status := G_RET_STS_UNEXP_ERROR;
999
1000 END create_header;
1001
1002
1003 --------------------------
1004 -- PROCEDURE update_header
1005 --------------------------
1006 PROCEDURE update_header (
1007 p_service_rec IN okl_svc_pvt.svcv_rec_type
1008 ,x_return_status OUT NOCOPY VARCHAR2
1009 ,x_msg_count OUT NOCOPY NUMBER
1010 ,x_msg_data OUT NOCOPY VARCHAR2
1011 ) IS
1012
1013 l_program_name CONSTANT VARCHAR2(30) := 'update_header';
1014 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
1015
1016 lx_service_rec okl_svc_pvt.svcv_rec_type;
1017
1018 BEGIN
1019
1020 okl_svc_pvt.update_row (
1021 p_api_version => G_API_VERSION
1022 ,p_init_msg_list => G_FALSE
1023 ,x_return_status => x_return_status
1024 ,x_msg_count => x_msg_count
1025 ,x_msg_data => x_msg_data
1026 ,p_svcv_rec => p_service_rec
1027 ,x_svcv_rec => lx_service_rec
1028 );
1029
1030 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
1031 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1032 ELSIF x_return_status = G_RET_STS_ERROR THEN
1033 RAISE OKL_API.G_EXCEPTION_ERROR;
1034 END IF;
1035
1036 EXCEPTION
1037
1038 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1039 x_return_status := G_RET_STS_ERROR;
1040
1041 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1042 x_return_status := G_RET_STS_UNEXP_ERROR;
1043
1044 WHEN OTHERS THEN
1045 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1046 p_msg_name => G_DB_ERROR,
1047 p_token1 => G_PROG_NAME_TOKEN,
1048 p_token1_value => l_program_name,
1049 p_token2 => G_SQLCODE_TOKEN,
1050 p_token2_value => sqlcode,
1051 p_token3 => G_SQLERRM_TOKEN,
1052 p_token3_value => sqlerrm);
1053
1054 x_return_status := G_RET_STS_UNEXP_ERROR;
1055
1056 END update_header;
1057
1058
1059 ---------------------------
1060 -- PROCEDURE create_payment
1061 ---------------------------
1062 PROCEDURE create_payment (
1063 p_service_id IN NUMBER
1064 ,p_payment_header_rec IN okl_lease_quote_cashflow_pvt.cashflow_header_rec_type
1065 ,p_payment_level_tbl IN okl_lease_quote_cashflow_pvt.cashflow_level_tbl_type
1066 ,x_return_status OUT NOCOPY VARCHAR2
1067 ,x_msg_count OUT NOCOPY NUMBER
1068 ,x_msg_data OUT NOCOPY VARCHAR2
1069 ) IS
1070
1071 l_program_name CONSTANT VARCHAR2(30) := 'create_payment';
1072 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
1073
1074 l_payment_header_rec okl_lease_quote_cashflow_pvt.cashflow_header_rec_type;
1075 l_payment_level_tbl okl_lease_quote_cashflow_pvt.cashflow_level_tbl_type;
1076
1077 BEGIN
1078
1079 l_payment_header_rec := p_payment_header_rec;
1080 l_payment_header_rec.parent_object_id := p_service_id;
1081 l_payment_header_rec.parent_object_code := 'QUOTED_SERVICE';
1082 l_payment_level_tbl := p_payment_level_tbl;
1083
1084 okl_lease_quote_cashflow_pvt.create_cashflow (
1085 p_api_version => G_API_VERSION
1086 ,p_init_msg_list => G_FALSE
1087 ,p_transaction_control => G_FALSE
1088 ,p_cashflow_header_rec => l_payment_header_rec
1089 ,p_cashflow_level_tbl => l_payment_level_tbl
1090 ,x_return_status => x_return_status
1091 ,x_msg_count => x_msg_count
1092 ,x_msg_data => x_msg_data
1093 );
1094
1095 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
1096 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1097 ELSIF x_return_status = G_RET_STS_ERROR THEN
1098 RAISE OKL_API.G_EXCEPTION_ERROR;
1099 END IF;
1100
1101 EXCEPTION
1102
1103 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1104 x_return_status := G_RET_STS_ERROR;
1105
1106 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1107 x_return_status := G_RET_STS_UNEXP_ERROR;
1108
1109 WHEN OTHERS THEN
1110 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1111 p_msg_name => G_DB_ERROR,
1112 p_token1 => G_PROG_NAME_TOKEN,
1113 p_token1_value => l_program_name,
1114 p_token2 => G_SQLCODE_TOKEN,
1115 p_token2_value => sqlcode,
1116 p_token3 => G_SQLERRM_TOKEN,
1117 p_token3_value => sqlerrm);
1118
1119 x_return_status := G_RET_STS_UNEXP_ERROR;
1120
1121 END create_payment;
1122
1123
1124 ---------------------------
1125 -- PROCEDURE update_payment
1126 ---------------------------
1127 PROCEDURE update_payment (
1128 p_service_id IN NUMBER
1129 ,p_payment_header_rec IN okl_lease_quote_cashflow_pvt.cashflow_header_rec_type
1130 ,p_payment_level_tbl IN okl_lease_quote_cashflow_pvt.cashflow_level_tbl_type
1131 ,x_return_status OUT NOCOPY VARCHAR2
1132 ,x_msg_count OUT NOCOPY NUMBER
1133 ,x_msg_data OUT NOCOPY VARCHAR2
1134 ) IS
1135
1136 l_program_name CONSTANT VARCHAR2(30) := 'update_payment';
1137 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
1138
1139 l_payment_header_rec okl_lease_quote_cashflow_pvt.cashflow_header_rec_type;
1140 l_payment_level_tbl okl_lease_quote_cashflow_pvt.cashflow_level_tbl_type;
1141
1142 BEGIN
1143
1144 l_payment_header_rec := p_payment_header_rec;
1145 l_payment_level_tbl := p_payment_level_tbl;
1146
1147 okl_lease_quote_cashflow_pvt.update_cashflow (
1148 p_api_version => G_API_VERSION
1149 ,p_init_msg_list => G_FALSE
1150 ,p_transaction_control => G_FALSE
1151 ,p_cashflow_header_rec => l_payment_header_rec
1152 ,p_cashflow_level_tbl => l_payment_level_tbl
1153 ,x_return_status => x_return_status
1154 ,x_msg_count => x_msg_count
1155 ,x_msg_data => x_msg_data
1156 );
1157
1158 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
1159 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1160 ELSIF x_return_status = G_RET_STS_ERROR THEN
1161 RAISE OKL_API.G_EXCEPTION_ERROR;
1162 END IF;
1163
1164 EXCEPTION
1165
1166 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1167 x_return_status := G_RET_STS_ERROR;
1168
1169 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1170 x_return_status := G_RET_STS_UNEXP_ERROR;
1171
1172 WHEN OTHERS THEN
1173 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1174 p_msg_name => G_DB_ERROR,
1175 p_token1 => G_PROG_NAME_TOKEN,
1176 p_token1_value => l_program_name,
1177 p_token2 => G_SQLCODE_TOKEN,
1178 p_token2_value => sqlcode,
1179 p_token3 => G_SQLERRM_TOKEN,
1180 p_token3_value => sqlerrm);
1181
1182 x_return_status := G_RET_STS_UNEXP_ERROR;
1183
1184 END update_payment;
1185
1186
1187 ---------------------------
1188 -- PROCEDURE create_expense
1189 ---------------------------
1190 PROCEDURE create_expense (
1191 p_service_id IN NUMBER
1192 ,p_expense_header_rec IN okl_lease_quote_cashflow_pvt.cashflow_header_rec_type
1193 ,p_expense_level_tbl IN okl_lease_quote_cashflow_pvt.cashflow_level_tbl_type
1194 ,x_return_status OUT NOCOPY VARCHAR2
1195 ,x_msg_count OUT NOCOPY NUMBER
1196 ,x_msg_data OUT NOCOPY VARCHAR2
1197 ) IS
1198
1199 l_program_name CONSTANT VARCHAR2(30) := 'create_expense';
1200 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
1201
1202 l_expense_header_rec okl_lease_quote_cashflow_pvt.cashflow_header_rec_type;
1203 l_expense_level_tbl okl_lease_quote_cashflow_pvt.cashflow_level_tbl_type;
1204
1205 BEGIN
1206
1207 l_expense_header_rec := p_expense_header_rec;
1208 l_expense_header_rec.parent_object_id := p_service_id;
1209 l_expense_level_tbl := p_expense_level_tbl;
1210
1211 okl_lease_quote_cashflow_pvt.create_cashflow (
1212 p_api_version => G_API_VERSION
1213 ,p_init_msg_list => G_FALSE
1214 ,p_transaction_control => G_FALSE
1215 ,p_cashflow_header_rec => l_expense_header_rec
1216 ,p_cashflow_level_tbl => l_expense_level_tbl
1217 ,x_return_status => x_return_status
1218 ,x_msg_count => x_msg_count
1219 ,x_msg_data => x_msg_data
1220 );
1221
1222 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
1223 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1224 ELSIF x_return_status = G_RET_STS_ERROR THEN
1225 RAISE OKL_API.G_EXCEPTION_ERROR;
1226 END IF;
1227
1228 EXCEPTION
1229
1230 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1231 x_return_status := G_RET_STS_ERROR;
1232
1233 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1234 x_return_status := G_RET_STS_UNEXP_ERROR;
1235
1236 WHEN OTHERS THEN
1237 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1238 p_msg_name => G_DB_ERROR,
1239 p_token1 => G_PROG_NAME_TOKEN,
1240 p_token1_value => l_program_name,
1241 p_token2 => G_SQLCODE_TOKEN,
1242 p_token2_value => sqlcode,
1243 p_token3 => G_SQLERRM_TOKEN,
1244 p_token3_value => sqlerrm);
1245
1246 x_return_status := G_RET_STS_UNEXP_ERROR;
1247
1248 END create_expense;
1249
1250
1251 ---------------------------
1252 -- PROCEDURE update_expense
1253 ---------------------------
1254 PROCEDURE update_expense (
1255 p_service_id IN NUMBER
1256 ,p_expense_header_rec IN okl_lease_quote_cashflow_pvt.cashflow_header_rec_type
1257 ,p_expense_level_tbl IN okl_lease_quote_cashflow_pvt.cashflow_level_tbl_type
1258 ,x_return_status OUT NOCOPY VARCHAR2
1259 ,x_msg_count OUT NOCOPY NUMBER
1260 ,x_msg_data OUT NOCOPY VARCHAR2
1261 ) IS
1262
1263 l_program_name CONSTANT VARCHAR2(30) := 'update_expense';
1264 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
1265
1266 l_expense_header_rec okl_lease_quote_cashflow_pvt.cashflow_header_rec_type;
1267 l_expense_level_tbl okl_lease_quote_cashflow_pvt.cashflow_level_tbl_type;
1268
1269 BEGIN
1270
1271 l_expense_header_rec := p_expense_header_rec;
1272 l_expense_level_tbl := p_expense_level_tbl;
1273
1274 okl_lease_quote_cashflow_pvt.update_cashflow (
1275 p_api_version => G_API_VERSION
1276 ,p_init_msg_list => G_FALSE
1277 ,p_transaction_control => G_FALSE
1278 ,p_cashflow_header_rec => l_expense_header_rec
1279 ,p_cashflow_level_tbl => l_expense_level_tbl
1280 ,x_return_status => x_return_status
1281 ,x_msg_count => x_msg_count
1282 ,x_msg_data => x_msg_data
1283 );
1284
1285 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
1286 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1287 ELSIF x_return_status = G_RET_STS_ERROR THEN
1288 RAISE OKL_API.G_EXCEPTION_ERROR;
1289 END IF;
1290
1291 EXCEPTION
1292
1293 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1294 x_return_status := G_RET_STS_ERROR;
1295
1296 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1297 x_return_status := G_RET_STS_UNEXP_ERROR;
1298
1299 WHEN OTHERS THEN
1300 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1301 p_msg_name => G_DB_ERROR,
1302 p_token1 => G_PROG_NAME_TOKEN,
1303 p_token1_value => l_program_name,
1304 p_token2 => G_SQLCODE_TOKEN,
1305 p_token2_value => sqlcode,
1306 p_token3 => G_SQLERRM_TOKEN,
1307 p_token3_value => sqlerrm);
1308
1309 x_return_status := G_RET_STS_UNEXP_ERROR;
1310
1311 END update_expense;
1312
1313
1314 ---------------------------
1315 -- PROCEDURE create_service
1316 ---------------------------
1317 PROCEDURE create_service (
1318 p_api_version IN NUMBER
1319 ,p_init_msg_list IN VARCHAR2
1320 ,p_transaction_control IN VARCHAR2
1321 ,p_service_rec IN okl_svc_pvt.svcv_rec_type
1322 ,p_assoc_asset_tbl IN line_relation_tbl_type
1323 ,p_payment_header_rec IN okl_lease_quote_cashflow_pvt.cashflow_header_rec_type
1324 ,p_payment_level_tbl IN okl_lease_quote_cashflow_pvt.cashflow_level_tbl_type
1325 ,p_expense_header_rec IN okl_lease_quote_cashflow_pvt.cashflow_header_rec_type
1326 ,p_expense_level_tbl IN okl_lease_quote_cashflow_pvt.cashflow_level_tbl_type
1327 ,x_service_id OUT NOCOPY NUMBER
1328 ,x_return_status OUT NOCOPY VARCHAR2
1329 ,x_msg_count OUT NOCOPY NUMBER
1330 ,x_msg_data OUT NOCOPY VARCHAR2
1331 ) IS
1332
1333 l_program_name CONSTANT VARCHAR2(30) := 'create_service';
1334 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
1335
1336 l_line_relation_tbl line_relation_tbl_type;
1337
1338 l_periodic_amount NUMBER;
1339 l_service_amount NUMBER;
1340 l_currency_code VARCHAR2(15);
1341
1342 l_return_status VARCHAR2(1);
1343 l_derive_assoc_amt VARCHAR2(1);
1344
1345 BEGIN
1346
1347 IF p_transaction_control = G_TRUE THEN
1348 SAVEPOINT l_program_name;
1349 END IF;
1350
1351 IF p_init_msg_list = G_TRUE THEN
1352 FND_MSG_PUB.initialize;
1353 END IF;
1354
1355 get_currency_code (
1356 p_parent_object_id => p_service_rec.parent_object_id
1357 ,x_currency_code => l_currency_code
1358 ,x_return_status => l_return_status
1359 );
1360
1361 IF p_expense_level_tbl(p_expense_level_tbl.FIRST).periods <>
1362 TRUNC (p_expense_level_tbl(p_expense_level_tbl.FIRST).periods) THEN
1363
1364 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME, p_msg_name => 'OKL_RECEXP_PERIODS_INVALID');
1365 RAISE OKL_API.G_EXCEPTION_ERROR;
1366
1367 END IF;
1368
1369 l_periodic_amount :=
1370 okl_accounting_util.round_amount( p_amount => p_expense_level_tbl(p_expense_level_tbl.FIRST).periodic_amount
1371 ,p_currency_code => l_currency_code);
1372
1373 l_service_amount := l_periodic_amount * p_expense_level_tbl(p_expense_level_tbl.FIRST).periods;
1374
1375 validate_header (
1376 p_service_rec => p_service_rec
1377 ,x_return_status => l_return_status
1378 );
1379
1380 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
1381 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1382 ELSIF l_return_status = G_RET_STS_ERROR THEN
1383 RAISE OKL_API.G_EXCEPTION_ERROR;
1384 END IF;
1385
1386 IF p_assoc_asset_tbl.COUNT > 0 THEN
1387
1388 l_line_relation_tbl := p_assoc_asset_tbl;
1389
1390 validate_link_assets (
1391 p_service_amount => l_service_amount
1392 ,p_assoc_assets_tbl => l_line_relation_tbl
1393 ,x_derive_assoc_amt => l_derive_assoc_amt
1394 ,x_return_status => l_return_status
1395 );
1396
1397 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
1398 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1399 ELSIF l_return_status = G_RET_STS_ERROR THEN
1400 RAISE OKL_API.G_EXCEPTION_ERROR;
1401 END IF;
1402
1403 IF (l_derive_assoc_amt = 'Y') THEN
1404
1405 process_link_asset_amounts (
1406 p_quote_id => p_service_rec.parent_object_id
1407 ,p_currency_code => l_currency_code
1408 ,p_service_amount => l_service_amount
1409 ,p_link_asset_tbl => l_line_relation_tbl
1410 ,p_derive_assoc_amt => 'Y'
1411 ,x_return_status => l_return_status
1412 ,x_msg_count => x_msg_count
1413 ,x_msg_data => x_msg_data
1414 );
1415
1416 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
1417 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1418 ELSIF l_return_status = G_RET_STS_ERROR THEN
1419 RAISE OKL_API.G_EXCEPTION_ERROR;
1420 END IF;
1421
1422 END IF;
1423
1424 END IF;
1425
1426 create_header (
1427 p_service_rec => p_service_rec
1428 ,x_service_id => x_service_id
1429 ,x_return_status => l_return_status
1430 ,x_msg_count => x_msg_count
1431 ,x_msg_data => x_msg_data
1432 );
1433
1434 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
1435 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1436 ELSIF l_return_status = G_RET_STS_ERROR THEN
1437 RAISE OKL_API.G_EXCEPTION_ERROR;
1438 END IF;
1439
1440 IF l_line_relation_tbl.COUNT > 0 THEN
1441
1442 create_line_associations (
1443 p_service_id => x_service_id
1444 ,p_assoc_assets_tbl => l_line_relation_tbl
1445 ,x_return_status => l_return_status
1446 ,x_msg_count => x_msg_count
1447 ,x_msg_data => x_msg_data
1448 );
1449
1450 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
1451 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1452 ELSIF l_return_status = G_RET_STS_ERROR THEN
1453 RAISE OKL_API.G_EXCEPTION_ERROR;
1454 END IF;
1455
1456 END IF;
1457
1458 IF (p_payment_level_tbl.COUNT > 0 AND p_payment_header_rec.stream_type_id IS NULL) THEN
1459 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1460 p_msg_name => 'OKL_SALES_NO_PAYMENTHEAD');
1461 RAISE OKL_API.G_EXCEPTION_ERROR;
1462 ELSIF (p_payment_header_rec.stream_type_id IS NOT NULL AND p_payment_level_tbl.COUNT = 0 ) THEN
1463 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1464 p_msg_name => 'OKL_SALES_NO_PAYMENTLINES');
1465 RAISE OKL_API.G_EXCEPTION_ERROR;
1466 END IF;
1467
1468 IF p_payment_level_tbl.COUNT > 0 THEN
1469
1470 create_payment (
1471 p_service_id => x_service_id
1472 ,p_payment_header_rec => p_payment_header_rec
1473 ,p_payment_level_tbl => p_payment_level_tbl
1474 ,x_return_status => l_return_status
1475 ,x_msg_count => x_msg_count
1476 ,x_msg_data => x_msg_data
1477 );
1478
1479 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
1480 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1481 ELSIF l_return_status = G_RET_STS_ERROR THEN
1482 RAISE OKL_API.G_EXCEPTION_ERROR;
1483 END IF;
1484
1485 END IF;
1486
1487 create_expense (
1488 p_service_id => x_service_id
1489 ,p_expense_header_rec => p_expense_header_rec
1490 ,p_expense_level_tbl => p_expense_level_tbl
1491 ,x_return_status => l_return_status
1492 ,x_msg_count => x_msg_count
1493 ,x_msg_data => x_msg_data
1494 );
1495
1496 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
1497 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1498 ELSIF l_return_status = G_RET_STS_ERROR THEN
1499 RAISE OKL_API.G_EXCEPTION_ERROR;
1500 END IF;
1501
1502 x_return_status := G_RET_STS_SUCCESS;
1503
1504 EXCEPTION
1505
1506 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1507
1508 IF p_transaction_control = G_TRUE THEN
1509 ROLLBACK TO l_program_name;
1510 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1511 END IF;
1512
1513 x_return_status := G_RET_STS_ERROR;
1514
1515 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1516
1517 IF p_transaction_control = G_TRUE THEN
1518 ROLLBACK TO l_program_name;
1519 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1520 END IF;
1521
1522 x_return_status := G_RET_STS_UNEXP_ERROR;
1523
1524 WHEN OTHERS THEN
1525
1526 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1527 p_msg_name => G_DB_ERROR,
1528 p_token1 => G_PROG_NAME_TOKEN,
1529 p_token1_value => l_api_name,
1530 p_token2 => G_SQLCODE_TOKEN,
1531 p_token2_value => sqlcode,
1532 p_token3 => G_SQLERRM_TOKEN,
1533 p_token3_value => sqlerrm);
1534
1535 IF p_transaction_control = G_TRUE THEN
1536 ROLLBACK TO l_program_name;
1537 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1538 END IF;
1539
1540 x_return_status := G_RET_STS_UNEXP_ERROR;
1541
1542 END create_service;
1543
1544 -----------------------------------
1545 -- PROCEDURE get_line_relations_tbl
1546 -----------------------------------
1547 PROCEDURE get_line_relations_tbl (
1548 p_service_id IN NUMBER
1549 ,x_line_relation_tbl OUT NOCOPY line_relation_tbl_type
1550 ,x_return_status OUT NOCOPY VARCHAR2 ) IS
1551
1552 l_program_name CONSTANT VARCHAR2(30) := 'get_line_relations_tbl';
1553 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
1554 i BINARY_INTEGER := 0;
1555
1556 CURSOR c_db_line_relations IS
1557 SELECT
1558 id
1559 ,object_version_number
1560 ,source_line_type
1561 ,source_line_id
1562 ,related_line_type
1563 ,related_line_id
1564 ,amount
1565 ,short_description
1566 ,description
1567 ,comments
1568 FROM okl_line_relationships_v
1569 WHERE related_line_id = p_service_id;
1570 BEGIN
1571 FOR l_db_line_relations IN c_db_line_relations LOOP
1572 x_line_relation_tbl(i).id := l_db_line_relations.id;
1573 x_line_relation_tbl(i).object_version_number := l_db_line_relations.object_version_number;
1574 x_line_relation_tbl(i).source_line_type := l_db_line_relations.source_line_type;
1575 x_line_relation_tbl(i).source_line_id := l_db_line_relations.source_line_id;
1576 x_line_relation_tbl(i).related_line_type := l_db_line_relations.related_line_type;
1577 x_line_relation_tbl(i).related_line_id := l_db_line_relations.related_line_id;
1578 x_line_relation_tbl(i).amount := l_db_line_relations.amount;
1579 x_line_relation_tbl(i).short_description := l_db_line_relations.short_description;
1580 x_line_relation_tbl(i).description := l_db_line_relations.description;
1581 x_line_relation_tbl(i).comments := l_db_line_relations.comments;
1582 i := i + 1;
1583 END LOOP;
1584
1585 x_return_status := G_RET_STS_SUCCESS;
1586 EXCEPTION
1587
1588 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1589 x_return_status := G_RET_STS_ERROR;
1590
1591 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1592 x_return_status := G_RET_STS_UNEXP_ERROR;
1593
1594 WHEN OTHERS THEN
1595 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1596 p_msg_name => G_DB_ERROR,
1597 p_token1 => G_PROG_NAME_TOKEN,
1598 p_token1_value => l_program_name,
1599 p_token2 => G_SQLCODE_TOKEN,
1600 p_token2_value => sqlcode,
1601 p_token3 => G_SQLERRM_TOKEN,
1602 p_token3_value => sqlerrm);
1603
1604 x_return_status := G_RET_STS_UNEXP_ERROR;
1605
1606 END get_line_relations_tbl;
1607
1608 ----------------------------------
1609 -- PROCEDURE update_service_assets
1610 ----------------------------------
1611 PROCEDURE update_service_assets (
1612 p_api_version IN NUMBER
1613 ,p_init_msg_list IN VARCHAR2
1614 ,p_transaction_control IN VARCHAR2
1615 ,p_quote_id IN NUMBER
1616 ,p_service_id IN NUMBER
1617 ,x_return_status OUT NOCOPY VARCHAR2
1618 ,x_msg_count OUT NOCOPY NUMBER
1619 ,x_msg_data OUT NOCOPY VARCHAR2 ) IS
1620
1621 l_program_name CONSTANT VARCHAR2(30) := 'update_service_assets';
1622 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
1623
1624 l_copy_relation_tbl line_relation_tbl_type;
1625
1626 l_line_relation_tbl lr_tbl_type;
1627 lx_line_relation_tbl lr_tbl_type;
1628
1629 ln_service_amount NUMBER;
1630 lv_currency_code VARCHAR2(30);
1631
1632 BEGIN
1633
1634 IF p_transaction_control = G_TRUE THEN
1635 SAVEPOINT l_program_name;
1636 END IF;
1637
1638 IF p_init_msg_list = G_TRUE THEN
1639 FND_MSG_PUB.initialize;
1640 END IF;
1641
1642 -- Fetch Line Relationships info
1643 get_line_relations_tbl (p_service_id => p_service_id
1644 ,x_line_relation_tbl => l_copy_relation_tbl
1645 ,x_return_status => x_return_status);
1646 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
1647 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1648 ELSIF x_return_status = G_RET_STS_ERROR THEN
1649 RAISE OKL_API.G_EXCEPTION_ERROR;
1650 END IF;
1651
1652 SELECT LVL.AMOUNT * LVL.NUMBER_OF_PERIODS
1653 INTO ln_service_amount
1654 FROM OKL_CASH_FLOW_OBJECTS CFO,
1655 OKL_CASH_FLOWS CFL,
1656 OKL_CASH_FLOW_LEVELS LVL
1657 WHERE CFO.SOURCE_ID = p_service_id
1658 AND CFO.OTY_CODE = 'QUOTED_SERVICE'
1659 AND CFO.SOURCE_TABLE = 'OKL_SERVICES_B'
1660 AND CFL.CFT_CODE = 'OUTFLOW_SCHEDULE'
1661 AND CFL.CFO_ID = CFO.ID
1662 AND LVL.CAF_ID = CFL.ID;
1663
1664 get_currency_code ( p_parent_object_id => p_quote_id
1665 ,x_currency_code => lv_currency_code
1666 ,x_return_status => x_return_status );
1667
1668 IF (l_copy_relation_tbl.COUNT > 0) THEN
1669 process_link_asset_amounts (
1670 p_quote_id => p_quote_id
1671 ,p_currency_code => lv_currency_code
1672 ,p_service_amount => ln_service_amount
1673 ,p_link_asset_tbl => l_copy_relation_tbl
1674 ,p_derive_assoc_amt => 'Y'
1675 ,p_override_pricing_type => 'Y'
1676 ,x_return_status => x_return_status
1677 ,x_msg_count => x_msg_count
1678 ,x_msg_data => x_msg_data );
1679
1680 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
1681 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1682 ELSIF x_return_status = G_RET_STS_ERROR THEN
1683 RAISE OKL_API.G_EXCEPTION_ERROR;
1684 END IF;
1685
1686 FOR i IN l_copy_relation_tbl.FIRST .. l_copy_relation_tbl.LAST LOOP
1687 IF l_copy_relation_tbl.EXISTS(i) THEN
1688 l_line_relation_tbl(i).id := l_copy_relation_tbl(i).id;
1689 l_line_relation_tbl(i).object_version_number := l_copy_relation_tbl(i).object_version_number;
1690 l_line_relation_tbl(i).source_line_type := l_copy_relation_tbl(i).source_line_type;
1691 l_line_relation_tbl(i).source_line_id := l_copy_relation_tbl(i).source_line_id;
1692 l_line_relation_tbl(i).related_line_type := l_copy_relation_tbl(i).related_line_type;
1693 l_line_relation_tbl(i).related_line_id := l_copy_relation_tbl(i).related_line_id;
1694 l_line_relation_tbl(i).amount := l_copy_relation_tbl(i).amount;
1695 END IF;
1696 END LOOP;
1697
1698 IF (l_line_relation_tbl.COUNT > 0) THEN
1699 okl_lre_pvt.update_row (
1700 p_api_version => G_API_VERSION
1701 ,p_init_msg_list => G_FALSE
1702 ,x_return_status => x_return_status
1703 ,x_msg_count => x_msg_count
1704 ,x_msg_data => x_msg_data
1705 ,p_lrev_tbl => l_line_relation_tbl
1706 ,x_lrev_tbl => lx_line_relation_tbl);
1707
1708 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
1709 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1710 ELSIF x_return_status = G_RET_STS_ERROR THEN
1711 RAISE OKL_API.G_EXCEPTION_ERROR;
1712 END IF;
1713 END IF;
1714 END IF;
1715
1716 x_return_status := G_RET_STS_SUCCESS;
1717
1718 EXCEPTION
1719
1720 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1721
1722 IF p_transaction_control = G_TRUE THEN
1723 ROLLBACK TO l_program_name;
1724 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1725 END IF;
1726
1727 x_return_status := G_RET_STS_ERROR;
1728
1729 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1730
1731 IF p_transaction_control = G_TRUE THEN
1732 ROLLBACK TO l_program_name;
1733 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1734 END IF;
1735
1736 x_return_status := G_RET_STS_UNEXP_ERROR;
1737
1738 WHEN OTHERS THEN
1739
1740 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1741 p_msg_name => G_DB_ERROR,
1742 p_token1 => G_PROG_NAME_TOKEN,
1743 p_token1_value => l_api_name,
1744 p_token2 => G_SQLCODE_TOKEN,
1745 p_token2_value => sqlcode,
1746 p_token3 => G_SQLERRM_TOKEN,
1747 p_token3_value => sqlerrm);
1748
1749 IF p_transaction_control = G_TRUE THEN
1750 ROLLBACK TO l_program_name;
1751 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1752 END IF;
1753
1754 x_return_status := G_RET_STS_UNEXP_ERROR;
1755
1756 END update_service_assets ;
1757
1758 ---------------------------
1759 -- PROCEDURE update_service
1760 ---------------------------
1761 PROCEDURE update_service (
1762 p_api_version IN NUMBER
1763 ,p_init_msg_list IN VARCHAR2
1764 ,p_transaction_control IN VARCHAR2
1765 ,p_service_rec IN okl_svc_pvt.svcv_rec_type
1766 ,p_assoc_asset_tbl IN line_relation_tbl_type
1767 ,p_payment_header_rec IN okl_lease_quote_cashflow_pvt.cashflow_header_rec_type
1768 ,p_payment_level_tbl IN okl_lease_quote_cashflow_pvt.cashflow_level_tbl_type
1769 ,p_expense_header_rec IN okl_lease_quote_cashflow_pvt.cashflow_header_rec_type
1770 ,p_expense_level_tbl IN okl_lease_quote_cashflow_pvt.cashflow_level_tbl_type
1771 ,x_return_status OUT NOCOPY VARCHAR2
1772 ,x_msg_count OUT NOCOPY NUMBER
1773 ,x_msg_data OUT NOCOPY VARCHAR2
1774 ) IS
1775
1776 l_program_name CONSTANT VARCHAR2(30) := 'update_service';
1777 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
1778
1779 l_line_relation_tbl line_relation_tbl_type;
1780
1781 l_periodic_amount NUMBER;
1782 l_service_amount NUMBER;
1783 l_currency_code VARCHAR2(15);
1784
1785 l_return_status VARCHAR2(1);
1786 l_derive_assoc_amt VARCHAR2(1);
1787
1788 BEGIN
1789
1790 IF p_transaction_control = G_TRUE THEN
1791 SAVEPOINT l_program_name;
1792 END IF;
1793
1794 IF p_init_msg_list = G_TRUE THEN
1795 FND_MSG_PUB.initialize;
1796 END IF;
1797
1798 get_currency_code (
1799 p_parent_object_id => p_service_rec.parent_object_id
1800 ,x_currency_code => l_currency_code
1801 ,x_return_status => l_return_status
1802 );
1803
1804 IF p_expense_level_tbl(p_expense_level_tbl.FIRST).periods <>
1805 TRUNC (p_expense_level_tbl(p_expense_level_tbl.FIRST).periods) THEN
1806
1807 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME, p_msg_name => 'OKL_RECEXP_PERIODS_INVALID');
1808 RAISE OKL_API.G_EXCEPTION_ERROR;
1809
1810 END IF;
1811
1812 l_periodic_amount :=
1813 okl_accounting_util.round_amount( p_amount => p_expense_level_tbl(p_expense_level_tbl.FIRST).periodic_amount
1814 ,p_currency_code => l_currency_code);
1815
1816 l_service_amount := l_periodic_amount * p_expense_level_tbl(p_expense_level_tbl.FIRST).periods;
1817
1818 validate_header (
1819 p_service_rec => p_service_rec
1820 ,x_return_status => l_return_status
1821 );
1822
1823 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
1824 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1825 ELSIF l_return_status = G_RET_STS_ERROR THEN
1826 RAISE OKL_API.G_EXCEPTION_ERROR;
1827 END IF;
1828
1829 IF p_assoc_asset_tbl.COUNT > 0 THEN
1830
1831 l_line_relation_tbl := p_assoc_asset_tbl;
1832
1833 validate_link_assets (
1834 p_service_amount => l_service_amount
1835 ,p_assoc_assets_tbl => l_line_relation_tbl
1836 ,x_derive_assoc_amt => l_derive_assoc_amt
1837 ,x_return_status => l_return_status
1838 );
1839
1840 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
1841 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1842 ELSIF l_return_status = G_RET_STS_ERROR THEN
1843 RAISE OKL_API.G_EXCEPTION_ERROR;
1844 END IF;
1845
1846 IF (l_derive_assoc_amt = 'Y') THEN
1847
1848 process_link_asset_amounts (
1849 p_quote_id => p_service_rec.parent_object_id
1850 ,p_currency_code => l_currency_code
1851 ,p_service_amount => l_service_amount
1852 ,p_link_asset_tbl => l_line_relation_tbl
1853 ,p_derive_assoc_amt => 'Y'
1854 ,x_return_status => l_return_status
1855 ,x_msg_count => x_msg_count
1856 ,x_msg_data => x_msg_data
1857 );
1858
1859 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
1860 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1861 ELSIF l_return_status = G_RET_STS_ERROR THEN
1862 RAISE OKL_API.G_EXCEPTION_ERROR;
1863 END IF;
1864
1865 END IF;
1866
1867 END IF;
1868
1869 update_header (
1870 p_service_rec => p_service_rec
1871 ,x_return_status => l_return_status
1872 ,x_msg_count => x_msg_count
1873 ,x_msg_data => x_msg_data
1874 );
1875
1876 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
1877 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1878 ELSIF l_return_status = G_RET_STS_ERROR THEN
1879 RAISE OKL_API.G_EXCEPTION_ERROR;
1880 END IF;
1881
1882 IF l_line_relation_tbl.COUNT > 0 THEN
1883
1884 update_line_associations (
1885 p_service_id => p_service_rec.id
1886 ,p_assoc_assets_tbl => l_line_relation_tbl
1887 ,x_return_status => l_return_status
1888 ,x_msg_count => x_msg_count
1889 ,x_msg_data => x_msg_data
1890 );
1891
1892 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
1893 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1894 ELSIF l_return_status = G_RET_STS_ERROR THEN
1895 RAISE OKL_API.G_EXCEPTION_ERROR;
1896 END IF;
1897
1898 END IF;
1899
1900 IF (p_payment_level_tbl.COUNT > 0 AND p_payment_header_rec.stream_type_id IS NULL) THEN
1901 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1902 p_msg_name => 'OKL_SALES_NO_PAYMENTHEAD');
1903 RAISE OKL_API.G_EXCEPTION_ERROR;
1904 ELSIF (p_payment_header_rec.stream_type_id IS NOT NULL AND p_payment_level_tbl.COUNT = 0 ) THEN
1905 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1906 p_msg_name => 'OKL_SALES_NO_PAYMENTLINES');
1907 RAISE OKL_API.G_EXCEPTION_ERROR;
1908 END IF;
1909
1910 IF p_payment_level_tbl.COUNT > 0 THEN
1911
1912 update_payment (
1913 p_service_id => p_service_rec.id
1914 ,p_payment_header_rec => p_payment_header_rec
1915 ,p_payment_level_tbl => p_payment_level_tbl
1916 ,x_return_status => l_return_status
1917 ,x_msg_count => x_msg_count
1918 ,x_msg_data => x_msg_data
1919 );
1920
1921 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
1922 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1923 ELSIF l_return_status = G_RET_STS_ERROR THEN
1924 RAISE OKL_API.G_EXCEPTION_ERROR;
1925 END IF;
1926
1927 END IF;
1928
1929 update_expense (
1930 p_service_id => p_service_rec.id
1931 ,p_expense_header_rec => p_expense_header_rec
1932 ,p_expense_level_tbl => p_expense_level_tbl
1933 ,x_return_status => l_return_status
1934 ,x_msg_count => x_msg_count
1935 ,x_msg_data => x_msg_data
1936 );
1937
1938 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
1939 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1940 ELSIF l_return_status = G_RET_STS_ERROR THEN
1941 RAISE OKL_API.G_EXCEPTION_ERROR;
1942 END IF;
1943
1944 x_return_status := G_RET_STS_SUCCESS;
1945
1946 EXCEPTION
1947
1948 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1949
1950 IF p_transaction_control = G_TRUE THEN
1951 ROLLBACK TO l_program_name;
1952 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1953 END IF;
1954
1955 x_return_status := G_RET_STS_ERROR;
1956
1957 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1958
1959 IF p_transaction_control = G_TRUE THEN
1960 ROLLBACK TO l_program_name;
1961 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1962 END IF;
1963
1964 x_return_status := G_RET_STS_UNEXP_ERROR;
1965
1966 WHEN OTHERS THEN
1967
1968 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1969 p_msg_name => G_DB_ERROR,
1970 p_token1 => G_PROG_NAME_TOKEN,
1971 p_token1_value => l_api_name,
1972 p_token2 => G_SQLCODE_TOKEN,
1973 p_token2_value => sqlcode,
1974 p_token3 => G_SQLERRM_TOKEN,
1975 p_token3_value => sqlerrm);
1976
1977 IF p_transaction_control = G_TRUE THEN
1978 ROLLBACK TO l_program_name;
1979 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1980 END IF;
1981
1982 x_return_status := G_RET_STS_UNEXP_ERROR;
1983
1984 END update_service;
1985
1986
1987 --------------------------
1988 -- PROCEDURE duplicate_service
1989 --------------------------
1990 PROCEDURE duplicate_service (
1991 p_api_version IN NUMBER
1992 ,p_init_msg_list IN VARCHAR2
1993 ,p_transaction_control IN VARCHAR2
1994 ,p_source_service_id IN NUMBER
1995 ,p_service_rec IN okl_svc_pvt.svcv_rec_type
1996 ,p_assoc_asset_tbl IN line_relation_tbl_type
1997 ,p_payment_header_rec IN okl_lease_quote_cashflow_pvt.cashflow_header_rec_type
1998 ,p_payment_level_tbl IN okl_lease_quote_cashflow_pvt.cashflow_level_tbl_type
1999 ,p_expense_header_rec IN okl_lease_quote_cashflow_pvt.cashflow_header_rec_type
2000 ,p_expense_level_tbl IN okl_lease_quote_cashflow_pvt.cashflow_level_tbl_type
2001 ,x_service_id OUT NOCOPY NUMBER
2002 ,x_return_status OUT NOCOPY VARCHAR2
2003 ,x_msg_count OUT NOCOPY NUMBER
2004 ,x_msg_data OUT NOCOPY VARCHAR2
2005 ) IS
2006
2007 l_program_name CONSTANT VARCHAR2(30) := 'duplicate_service';
2008 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
2009
2010 BEGIN
2011
2012 IF p_transaction_control = G_TRUE THEN
2013 SAVEPOINT l_program_name;
2014 END IF;
2015
2016 IF p_init_msg_list = G_TRUE THEN
2017 FND_MSG_PUB.initialize;
2018 END IF;
2019
2020 create_service (
2021 p_api_version => G_API_VERSION
2022 ,p_init_msg_list => G_FALSE
2023 ,p_transaction_control => G_FALSE
2024 ,p_service_rec => p_service_rec
2025 ,p_assoc_asset_tbl => p_assoc_asset_tbl
2026 ,p_payment_header_rec => p_payment_header_rec
2027 ,p_payment_level_tbl => p_payment_level_tbl
2028 ,p_expense_header_rec => p_expense_header_rec
2029 ,p_expense_level_tbl => p_expense_level_tbl
2030 ,x_service_id => x_service_id
2031 ,x_return_status => x_return_status
2032 ,x_msg_count => x_msg_count
2033 ,x_msg_data => x_msg_data
2034 );
2035
2036 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
2037 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2038 ELSIF x_return_status = G_RET_STS_ERROR THEN
2039 RAISE OKL_API.G_EXCEPTION_ERROR;
2040 END IF;
2041
2042 x_return_status := G_RET_STS_SUCCESS;
2043
2044 EXCEPTION
2045
2046 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2047
2048 IF p_transaction_control = G_TRUE THEN
2049 ROLLBACK TO l_program_name;
2050 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
2051 END IF;
2052
2053 x_return_status := G_RET_STS_ERROR;
2054
2055 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2056
2057 IF p_transaction_control = G_TRUE THEN
2058 ROLLBACK TO l_program_name;
2059 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
2060 END IF;
2061
2062 x_return_status := G_RET_STS_UNEXP_ERROR;
2063
2064 WHEN OTHERS THEN
2065
2066 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
2067 p_msg_name => G_DB_ERROR,
2068 p_token1 => G_PROG_NAME_TOKEN,
2069 p_token1_value => l_api_name,
2070 p_token2 => G_SQLCODE_TOKEN,
2071 p_token2_value => sqlcode,
2072 p_token3 => G_SQLERRM_TOKEN,
2073 p_token3_value => sqlerrm);
2074
2075 IF p_transaction_control = G_TRUE THEN
2076 ROLLBACK TO l_program_name;
2077 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
2078 END IF;
2079
2080 x_return_status := G_RET_STS_UNEXP_ERROR;
2081
2082 END duplicate_service;
2083
2084
2085 --------------------------
2086 -- PROCEDURE duplicate_service
2087 --------------------------
2088 PROCEDURE duplicate_service (
2089 p_api_version IN NUMBER
2090 ,p_init_msg_list IN VARCHAR2
2091 ,p_transaction_control IN VARCHAR2
2092 ,p_source_service_id IN NUMBER
2093 ,p_target_quote_id IN NUMBER
2094 ,x_service_id OUT NOCOPY NUMBER
2095 ,x_return_status OUT NOCOPY VARCHAR2
2096 ,x_msg_count OUT NOCOPY NUMBER
2097 ,x_msg_data OUT NOCOPY VARCHAR2
2098 ) IS
2099
2100 l_program_name CONSTANT VARCHAR2(30) := 'duplicate_service2';
2101 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
2102
2103 l_service_rec okl_svc_pvt.svcv_rec_type;
2104 lx_service_rec okl_svc_pvt.svcv_rec_type;
2105
2106 lb_copy_cashflow BOOLEAN := TRUE;
2107 lb_copy_lr BOOLEAN := TRUE;
2108 ld_src_start_date DATE;
2109 ld_tgt_start_date DATE;
2110 ln_src_pdt_id NUMBER;
2111 ln_tgt_pdt_id NUMBER;
2112 ln_src_eot_id NUMBER;
2113 ln_tgt_eot_id NUMBER;
2114
2115 BEGIN
2116
2117 IF p_transaction_control = G_TRUE THEN
2118 SAVEPOINT l_program_name;
2119 END IF;
2120
2121 IF p_init_msg_list = G_TRUE THEN
2122 FND_MSG_PUB.initialize;
2123 END IF;
2124
2125 get_service_rec (
2126 p_service_id => p_source_service_id
2127 ,x_service_rec => l_service_rec
2128 ,x_return_status => x_return_status);
2129
2130 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
2131 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2132 ELSIF x_return_status = G_RET_STS_ERROR THEN
2133 RAISE OKL_API.G_EXCEPTION_ERROR;
2134 END IF;
2135
2136 l_service_rec.parent_object_id := p_target_quote_id;
2137
2138 create_header (
2139 p_service_rec => l_service_rec
2140 ,x_service_id => x_service_id
2141 ,x_return_status => x_return_status
2142 ,x_msg_count => x_msg_count
2143 ,x_msg_data => x_msg_data
2144 );
2145
2146 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
2147 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2148 ELSIF x_return_status = G_RET_STS_ERROR THEN
2149 RAISE OKL_API.G_EXCEPTION_ERROR;
2150 END IF;
2151
2152 -- Validation to check if the product and expected start date for source
2153 -- and target contracts are equal, if not cash flows are not copied.
2154 SELECT quote.expected_start_date,
2155 quote.product_id,
2156 quote.end_of_term_option_id
2157 INTO ld_src_start_date, ln_src_pdt_id, ln_src_eot_id
2158 FROM
2159 okl_services_b srv,
2160 okl_lease_quotes_b quote
2161 WHERE
2162 srv.id = p_source_service_id
2163 AND srv.parent_object_id = quote.id
2164 AND srv.parent_object_code = 'LEASEQUOTE';
2165
2166 SELECT expected_start_date,
2167 product_id,
2168 end_of_term_option_id
2169 INTO ld_tgt_start_date, ln_tgt_pdt_id, ln_tgt_eot_id
2170 FROM
2171 okl_lease_quotes_b
2172 WHERE
2173 id = p_target_quote_id;
2174
2175 IF ((ld_src_start_date <> ld_tgt_start_date) OR (ln_src_pdt_id <> ln_tgt_pdt_id)) THEN
2176 lb_copy_cashflow := FALSE;
2177 END IF;
2178 -- End
2179
2180 IF (ln_src_eot_id <> ln_tgt_eot_id) THEN
2181 lb_copy_lr := FALSE;
2182 END IF;
2183
2184 IF (lb_copy_cashflow) THEN
2185 copy_line_associations( p_source_service_id => p_source_service_id,
2186 p_target_service_id => x_service_id,
2187 x_return_status => x_return_status,
2188 x_msg_count => x_msg_count,
2189 x_msg_data => x_msg_data );
2190 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
2191 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2192 ELSIF x_return_status = G_RET_STS_ERROR THEN
2193 RAISE OKL_API.G_EXCEPTION_ERROR;
2194 END IF;
2195 END IF;
2196
2197 IF (lb_copy_cashflow) THEN
2198 okl_lease_quote_cashflow_pvt.duplicate_cashflows (
2199 p_api_version => G_API_VERSION
2200 ,p_init_msg_list => G_FALSE
2201 ,p_transaction_control => G_FALSE
2202 ,p_source_object_code => 'QUOTED_SERVICE'
2203 ,p_source_object_id => p_source_service_id
2204 ,p_target_object_id => x_service_id
2205 ,p_quote_id => p_target_quote_id
2206 ,x_return_status => x_return_status
2207 ,x_msg_count => x_msg_count
2208 ,x_msg_data => x_msg_data );
2209
2210 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
2211 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2212 ELSIF x_return_status = G_RET_STS_ERROR THEN
2213 RAISE OKL_API.G_EXCEPTION_ERROR;
2214 END IF;
2215 END IF;
2216
2217 x_return_status := G_RET_STS_SUCCESS;
2218
2219 EXCEPTION
2220
2221 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2222
2223 IF p_transaction_control = G_TRUE THEN
2224 ROLLBACK TO l_program_name;
2225 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
2226 END IF;
2227
2228 x_return_status := G_RET_STS_ERROR;
2229
2230 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2231
2232 IF p_transaction_control = G_TRUE THEN
2233 ROLLBACK TO l_program_name;
2234 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
2235 END IF;
2236
2237 x_return_status := G_RET_STS_UNEXP_ERROR;
2238
2239 WHEN OTHERS THEN
2240
2241 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
2242 p_msg_name => G_DB_ERROR,
2243 p_token1 => G_PROG_NAME_TOKEN,
2244 p_token1_value => l_api_name,
2245 p_token2 => G_SQLCODE_TOKEN,
2246 p_token2_value => sqlcode,
2247 p_token3 => G_SQLERRM_TOKEN,
2248 p_token3_value => sqlerrm);
2249
2250 IF p_transaction_control = G_TRUE THEN
2251 ROLLBACK TO l_program_name;
2252 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
2253 END IF;
2254
2255 x_return_status := G_RET_STS_UNEXP_ERROR;
2256
2257 END duplicate_service;
2258
2259
2260 -----------------------
2261 -- PROCEDURE delete_service
2262 -----------------------
2263 PROCEDURE delete_service (
2264 p_api_version IN NUMBER
2265 ,p_init_msg_list IN VARCHAR2
2266 ,p_transaction_control IN VARCHAR2
2267 ,p_service_id IN NUMBER
2268 ,x_return_status OUT NOCOPY VARCHAR2
2269 ,x_msg_count OUT NOCOPY NUMBER
2270 ,x_msg_data OUT NOCOPY VARCHAR2
2271 ) IS
2272
2273 l_program_name CONSTANT VARCHAR2(30) := 'delete_service';
2274 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
2275
2276 l_lrev_tbl okl_lre_pvt.lrev_tbl_type;
2277 l_svcv_rec okl_svc_pvt.svcv_rec_type;
2278 i BINARY_INTEGER;
2279
2280 CURSOR c_sublines IS
2281 SELECT id
2282 FROM okl_line_relationships_b
2283 WHERE related_line_type = 'SERVICE'
2284 AND related_line_id = p_service_id;
2285
2286 BEGIN
2287
2288 IF p_transaction_control = G_TRUE THEN
2289 SAVEPOINT l_program_name;
2290 END IF;
2291
2292 IF p_init_msg_list = G_TRUE THEN
2293 FND_MSG_PUB.initialize;
2294 END IF;
2295
2296 i := 0;
2297 FOR l_subline IN c_sublines LOOP
2298 l_lrev_tbl(i).id := l_subline.id;
2299 i := i + 1;
2300 END LOOP;
2301
2302 IF l_lrev_tbl.COUNT > 0 THEN
2303
2304 okl_lre_pvt.delete_row (
2305 p_api_version => G_API_VERSION
2306 ,p_init_msg_list => G_FALSE
2307 ,x_return_status => x_return_status
2308 ,x_msg_count => x_msg_count
2309 ,x_msg_data => x_msg_data
2310 ,p_lrev_tbl => l_lrev_tbl
2311 );
2312
2313 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
2314 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2315 ELSIF x_return_status = G_RET_STS_ERROR THEN
2316 RAISE OKL_API.G_EXCEPTION_ERROR;
2317 END IF;
2318
2319 END IF;
2320
2321 okl_lease_quote_cashflow_pvt.delete_cashflows (
2322 p_api_version => G_API_VERSION
2323 ,p_init_msg_list => G_FALSE
2324 ,p_transaction_control => G_FALSE
2325 ,p_source_object_code => 'QUOTED_SERVICE'
2326 ,p_source_object_id => p_service_id
2327 ,x_return_status => x_return_status
2328 ,x_msg_count => x_msg_count
2329 ,x_msg_data => x_msg_data
2330 );
2331
2332 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
2333 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2334 ELSIF x_return_status = G_RET_STS_ERROR THEN
2335 RAISE OKL_API.G_EXCEPTION_ERROR;
2336 END IF;
2337
2338 l_svcv_rec.id := p_service_id;
2339
2340 okl_svc_pvt.delete_row (
2341 p_api_version => G_API_VERSION
2342 ,p_init_msg_list => G_FALSE
2343 ,x_return_status => x_return_status
2344 ,x_msg_count => x_msg_count
2345 ,x_msg_data => x_msg_data
2346 ,p_svcv_rec => l_svcv_rec
2347 );
2348
2349 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
2350 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2351 ELSIF x_return_status = G_RET_STS_ERROR THEN
2352 RAISE OKL_API.G_EXCEPTION_ERROR;
2353 END IF;
2354
2355 x_return_status := G_RET_STS_SUCCESS;
2356
2357 EXCEPTION
2358
2359 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2360
2361 IF p_transaction_control = G_TRUE THEN
2362 ROLLBACK TO l_program_name;
2363 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
2364 END IF;
2365
2366 x_return_status := G_RET_STS_ERROR;
2367
2368 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2369
2370 IF p_transaction_control = G_TRUE THEN
2371 ROLLBACK TO l_program_name;
2372 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
2373 END IF;
2374
2375 x_return_status := G_RET_STS_UNEXP_ERROR;
2376
2377 WHEN OTHERS THEN
2378
2379 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
2380 p_msg_name => G_DB_ERROR,
2381 p_token1 => G_PROG_NAME_TOKEN,
2382 p_token1_value => l_api_name,
2383 p_token2 => G_SQLCODE_TOKEN,
2384 p_token2_value => sqlcode,
2385 p_token3 => G_SQLERRM_TOKEN,
2386 p_token3_value => sqlerrm);
2387
2388 IF p_transaction_control = G_TRUE THEN
2389 ROLLBACK TO l_program_name;
2390 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
2391 END IF;
2392
2393 x_return_status := G_RET_STS_UNEXP_ERROR;
2394
2395 END delete_service;
2396
2397 END OKL_LEASE_QUOTE_SERVICE_PVT;