[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 2006/03/28 19:52:02 rravikir noship $ */
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 CURSOR c_get_new_asset_id (p_source_asset_id IN NUMBER) IS
861 SELECT id
862 FROM OKL_ASSETS_B
863 WHERE ORIG_ASSET_ID = p_source_asset_id;
864
865 BEGIN
866 -- Get line relationships table
867 get_lrship_tbl (p_source_service_id => p_source_service_id,
868 p_target_service_id => p_target_service_id,
869 x_lrship_tbl => l_lrship_tbl,
870 x_return_status => x_return_status);
871 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
872 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
873 ELSIF x_return_status = G_RET_STS_ERROR THEN
874 RAISE OKL_API.G_EXCEPTION_ERROR;
875 END IF;
876
877 -- Set the original asset id for the records
878 IF (l_lrship_tbl.COUNT > 0) THEN
879 FOR i IN l_lrship_tbl.FIRST .. l_lrship_tbl.LAST LOOP
880 IF l_lrship_tbl.EXISTS(i) THEN
881 OPEN c_get_new_asset_id(p_source_asset_id => l_lrship_tbl(i).source_line_id);
882 FETCH c_get_new_asset_id INTO l_lrship_tbl(i).source_line_id;
883 CLOSE c_get_new_asset_id;
884 END IF;
885 END LOOP;
886
887 okl_lre_pvt.insert_row (
888 p_api_version => G_API_VERSION
889 ,p_init_msg_list => G_FALSE
890 ,x_return_status => x_return_status
891 ,x_msg_count => x_msg_count
892 ,x_msg_data => x_msg_data
893 ,p_lrev_tbl => l_lrship_tbl
894 ,x_lrev_tbl => lx_lrship_tbl );
895
896 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
897 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
898 ELSIF x_return_status = G_RET_STS_ERROR THEN
899 RAISE OKL_API.G_EXCEPTION_ERROR;
900 END IF;
901 END IF;
902
903 x_return_status := G_RET_STS_SUCCESS;
904
905 EXCEPTION
906
907 WHEN OKL_API.G_EXCEPTION_ERROR THEN
908
909 x_return_status := G_RET_STS_ERROR;
910
911 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
912
913 x_return_status := G_RET_STS_UNEXP_ERROR;
914
915 WHEN OTHERS THEN
916
917 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
918 p_msg_name => G_DB_ERROR,
919 p_token1 => G_PROG_NAME_TOKEN,
920 p_token1_value => l_api_name,
921 p_token2 => G_SQLCODE_TOKEN,
922 p_token2_value => sqlcode,
923 p_token3 => G_SQLERRM_TOKEN,
924 p_token3_value => sqlerrm);
925
926 x_return_status := G_RET_STS_UNEXP_ERROR;
927 END copy_line_associations;
928
929
930 --------------------------
931 -- PROCEDURE create_header
932 --------------------------
933 PROCEDURE create_header (
934 p_service_rec IN okl_svc_pvt.svcv_rec_type
935 ,x_service_id OUT NOCOPY NUMBER
936 ,x_return_status OUT NOCOPY VARCHAR2
937 ,x_msg_count OUT NOCOPY NUMBER
938 ,x_msg_data OUT NOCOPY VARCHAR2
939 ) IS
940
941 l_program_name CONSTANT VARCHAR2(30) := 'create_header';
942 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
943
944 lx_service_rec okl_svc_pvt.svcv_rec_type;
945
946 BEGIN
947
948 okl_svc_pvt.insert_row (
949 p_api_version => G_API_VERSION
950 ,p_init_msg_list => G_FALSE
951 ,x_return_status => x_return_status
952 ,x_msg_count => x_msg_count
953 ,x_msg_data => x_msg_data
954 ,p_svcv_rec => p_service_rec
955 ,x_svcv_rec => lx_service_rec
956 );
957
958 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
959 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
960 ELSIF x_return_status = G_RET_STS_ERROR THEN
961 RAISE OKL_API.G_EXCEPTION_ERROR;
962 END IF;
963
964 x_service_id := lx_service_rec.id;
965
966 EXCEPTION
967
968 WHEN OKL_API.G_EXCEPTION_ERROR THEN
969 x_return_status := G_RET_STS_ERROR;
970
971 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
972 x_return_status := G_RET_STS_UNEXP_ERROR;
973
974 WHEN OTHERS THEN
975 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
976 p_msg_name => G_DB_ERROR,
977 p_token1 => G_PROG_NAME_TOKEN,
978 p_token1_value => l_program_name,
979 p_token2 => G_SQLCODE_TOKEN,
980 p_token2_value => sqlcode,
981 p_token3 => G_SQLERRM_TOKEN,
982 p_token3_value => sqlerrm);
983
984 x_return_status := G_RET_STS_UNEXP_ERROR;
985
986 END create_header;
987
988
989 --------------------------
990 -- PROCEDURE update_header
991 --------------------------
992 PROCEDURE update_header (
993 p_service_rec IN okl_svc_pvt.svcv_rec_type
994 ,x_return_status OUT NOCOPY VARCHAR2
995 ,x_msg_count OUT NOCOPY NUMBER
996 ,x_msg_data OUT NOCOPY VARCHAR2
997 ) IS
998
999 l_program_name CONSTANT VARCHAR2(30) := 'update_header';
1000 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
1001
1002 lx_service_rec okl_svc_pvt.svcv_rec_type;
1003
1004 BEGIN
1005
1006 okl_svc_pvt.update_row (
1007 p_api_version => G_API_VERSION
1008 ,p_init_msg_list => G_FALSE
1009 ,x_return_status => x_return_status
1010 ,x_msg_count => x_msg_count
1011 ,x_msg_data => x_msg_data
1012 ,p_svcv_rec => p_service_rec
1013 ,x_svcv_rec => lx_service_rec
1014 );
1015
1016 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
1017 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1018 ELSIF x_return_status = G_RET_STS_ERROR THEN
1019 RAISE OKL_API.G_EXCEPTION_ERROR;
1020 END IF;
1021
1022 EXCEPTION
1023
1024 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1025 x_return_status := G_RET_STS_ERROR;
1026
1027 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1028 x_return_status := G_RET_STS_UNEXP_ERROR;
1029
1030 WHEN OTHERS THEN
1031 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1032 p_msg_name => G_DB_ERROR,
1033 p_token1 => G_PROG_NAME_TOKEN,
1034 p_token1_value => l_program_name,
1035 p_token2 => G_SQLCODE_TOKEN,
1036 p_token2_value => sqlcode,
1037 p_token3 => G_SQLERRM_TOKEN,
1038 p_token3_value => sqlerrm);
1039
1040 x_return_status := G_RET_STS_UNEXP_ERROR;
1041
1042 END update_header;
1043
1044
1045 ---------------------------
1046 -- PROCEDURE create_payment
1047 ---------------------------
1048 PROCEDURE create_payment (
1049 p_service_id IN NUMBER
1050 ,p_payment_header_rec IN okl_lease_quote_cashflow_pvt.cashflow_header_rec_type
1051 ,p_payment_level_tbl IN okl_lease_quote_cashflow_pvt.cashflow_level_tbl_type
1052 ,x_return_status OUT NOCOPY VARCHAR2
1053 ,x_msg_count OUT NOCOPY NUMBER
1054 ,x_msg_data OUT NOCOPY VARCHAR2
1055 ) IS
1056
1057 l_program_name CONSTANT VARCHAR2(30) := 'create_payment';
1058 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
1059
1060 l_payment_header_rec okl_lease_quote_cashflow_pvt.cashflow_header_rec_type;
1061 l_payment_level_tbl okl_lease_quote_cashflow_pvt.cashflow_level_tbl_type;
1062
1063 BEGIN
1064
1065 l_payment_header_rec := p_payment_header_rec;
1066 l_payment_header_rec.parent_object_id := p_service_id;
1067 l_payment_header_rec.parent_object_code := 'QUOTED_SERVICE';
1068 l_payment_level_tbl := p_payment_level_tbl;
1069
1070 okl_lease_quote_cashflow_pvt.create_cashflow (
1071 p_api_version => G_API_VERSION
1072 ,p_init_msg_list => G_FALSE
1073 ,p_transaction_control => G_FALSE
1074 ,p_cashflow_header_rec => l_payment_header_rec
1075 ,p_cashflow_level_tbl => l_payment_level_tbl
1076 ,x_return_status => x_return_status
1077 ,x_msg_count => x_msg_count
1078 ,x_msg_data => x_msg_data
1079 );
1080
1081 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
1082 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1083 ELSIF x_return_status = G_RET_STS_ERROR THEN
1084 RAISE OKL_API.G_EXCEPTION_ERROR;
1085 END IF;
1086
1087 EXCEPTION
1088
1089 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1090 x_return_status := G_RET_STS_ERROR;
1091
1092 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1093 x_return_status := G_RET_STS_UNEXP_ERROR;
1094
1095 WHEN OTHERS THEN
1096 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1097 p_msg_name => G_DB_ERROR,
1098 p_token1 => G_PROG_NAME_TOKEN,
1099 p_token1_value => l_program_name,
1100 p_token2 => G_SQLCODE_TOKEN,
1101 p_token2_value => sqlcode,
1102 p_token3 => G_SQLERRM_TOKEN,
1103 p_token3_value => sqlerrm);
1104
1105 x_return_status := G_RET_STS_UNEXP_ERROR;
1106
1107 END create_payment;
1108
1109
1110 ---------------------------
1111 -- PROCEDURE update_payment
1112 ---------------------------
1113 PROCEDURE update_payment (
1114 p_service_id IN NUMBER
1115 ,p_payment_header_rec IN okl_lease_quote_cashflow_pvt.cashflow_header_rec_type
1116 ,p_payment_level_tbl IN okl_lease_quote_cashflow_pvt.cashflow_level_tbl_type
1117 ,x_return_status OUT NOCOPY VARCHAR2
1118 ,x_msg_count OUT NOCOPY NUMBER
1119 ,x_msg_data OUT NOCOPY VARCHAR2
1120 ) IS
1121
1122 l_program_name CONSTANT VARCHAR2(30) := 'update_payment';
1123 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
1124
1125 l_payment_header_rec okl_lease_quote_cashflow_pvt.cashflow_header_rec_type;
1126 l_payment_level_tbl okl_lease_quote_cashflow_pvt.cashflow_level_tbl_type;
1127
1128 BEGIN
1129
1130 l_payment_header_rec := p_payment_header_rec;
1131 l_payment_level_tbl := p_payment_level_tbl;
1132
1133 okl_lease_quote_cashflow_pvt.update_cashflow (
1134 p_api_version => G_API_VERSION
1135 ,p_init_msg_list => G_FALSE
1136 ,p_transaction_control => G_FALSE
1137 ,p_cashflow_header_rec => l_payment_header_rec
1138 ,p_cashflow_level_tbl => l_payment_level_tbl
1139 ,x_return_status => x_return_status
1140 ,x_msg_count => x_msg_count
1141 ,x_msg_data => x_msg_data
1142 );
1143
1144 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
1145 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1146 ELSIF x_return_status = G_RET_STS_ERROR THEN
1147 RAISE OKL_API.G_EXCEPTION_ERROR;
1148 END IF;
1149
1150 EXCEPTION
1151
1152 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1153 x_return_status := G_RET_STS_ERROR;
1154
1155 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1156 x_return_status := G_RET_STS_UNEXP_ERROR;
1157
1158 WHEN OTHERS THEN
1159 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1160 p_msg_name => G_DB_ERROR,
1161 p_token1 => G_PROG_NAME_TOKEN,
1162 p_token1_value => l_program_name,
1163 p_token2 => G_SQLCODE_TOKEN,
1164 p_token2_value => sqlcode,
1165 p_token3 => G_SQLERRM_TOKEN,
1166 p_token3_value => sqlerrm);
1167
1168 x_return_status := G_RET_STS_UNEXP_ERROR;
1169
1170 END update_payment;
1171
1172
1173 ---------------------------
1174 -- PROCEDURE create_expense
1175 ---------------------------
1176 PROCEDURE create_expense (
1177 p_service_id IN NUMBER
1178 ,p_expense_header_rec IN okl_lease_quote_cashflow_pvt.cashflow_header_rec_type
1179 ,p_expense_level_tbl IN okl_lease_quote_cashflow_pvt.cashflow_level_tbl_type
1180 ,x_return_status OUT NOCOPY VARCHAR2
1181 ,x_msg_count OUT NOCOPY NUMBER
1182 ,x_msg_data OUT NOCOPY VARCHAR2
1183 ) IS
1184
1185 l_program_name CONSTANT VARCHAR2(30) := 'create_expense';
1186 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
1187
1188 l_expense_header_rec okl_lease_quote_cashflow_pvt.cashflow_header_rec_type;
1189 l_expense_level_tbl okl_lease_quote_cashflow_pvt.cashflow_level_tbl_type;
1190
1191 BEGIN
1192
1193 l_expense_header_rec := p_expense_header_rec;
1194 l_expense_header_rec.parent_object_id := p_service_id;
1195 l_expense_level_tbl := p_expense_level_tbl;
1196
1197 okl_lease_quote_cashflow_pvt.create_cashflow (
1198 p_api_version => G_API_VERSION
1199 ,p_init_msg_list => G_FALSE
1200 ,p_transaction_control => G_FALSE
1201 ,p_cashflow_header_rec => l_expense_header_rec
1202 ,p_cashflow_level_tbl => l_expense_level_tbl
1203 ,x_return_status => x_return_status
1204 ,x_msg_count => x_msg_count
1205 ,x_msg_data => x_msg_data
1206 );
1207
1208 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
1209 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1210 ELSIF x_return_status = G_RET_STS_ERROR THEN
1211 RAISE OKL_API.G_EXCEPTION_ERROR;
1212 END IF;
1213
1214 EXCEPTION
1215
1216 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1217 x_return_status := G_RET_STS_ERROR;
1218
1219 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1220 x_return_status := G_RET_STS_UNEXP_ERROR;
1221
1222 WHEN OTHERS THEN
1223 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1224 p_msg_name => G_DB_ERROR,
1225 p_token1 => G_PROG_NAME_TOKEN,
1226 p_token1_value => l_program_name,
1227 p_token2 => G_SQLCODE_TOKEN,
1228 p_token2_value => sqlcode,
1229 p_token3 => G_SQLERRM_TOKEN,
1230 p_token3_value => sqlerrm);
1231
1232 x_return_status := G_RET_STS_UNEXP_ERROR;
1233
1234 END create_expense;
1235
1236
1237 ---------------------------
1238 -- PROCEDURE update_expense
1239 ---------------------------
1240 PROCEDURE update_expense (
1241 p_service_id IN NUMBER
1242 ,p_expense_header_rec IN okl_lease_quote_cashflow_pvt.cashflow_header_rec_type
1243 ,p_expense_level_tbl IN okl_lease_quote_cashflow_pvt.cashflow_level_tbl_type
1244 ,x_return_status OUT NOCOPY VARCHAR2
1245 ,x_msg_count OUT NOCOPY NUMBER
1246 ,x_msg_data OUT NOCOPY VARCHAR2
1247 ) IS
1248
1249 l_program_name CONSTANT VARCHAR2(30) := 'update_expense';
1250 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
1251
1252 l_expense_header_rec okl_lease_quote_cashflow_pvt.cashflow_header_rec_type;
1253 l_expense_level_tbl okl_lease_quote_cashflow_pvt.cashflow_level_tbl_type;
1254
1255 BEGIN
1256
1257 l_expense_header_rec := p_expense_header_rec;
1258 l_expense_level_tbl := p_expense_level_tbl;
1259
1260 okl_lease_quote_cashflow_pvt.update_cashflow (
1261 p_api_version => G_API_VERSION
1262 ,p_init_msg_list => G_FALSE
1263 ,p_transaction_control => G_FALSE
1264 ,p_cashflow_header_rec => l_expense_header_rec
1265 ,p_cashflow_level_tbl => l_expense_level_tbl
1266 ,x_return_status => x_return_status
1267 ,x_msg_count => x_msg_count
1268 ,x_msg_data => x_msg_data
1269 );
1270
1271 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
1272 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1273 ELSIF x_return_status = G_RET_STS_ERROR THEN
1274 RAISE OKL_API.G_EXCEPTION_ERROR;
1275 END IF;
1276
1277 EXCEPTION
1278
1279 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1280 x_return_status := G_RET_STS_ERROR;
1281
1282 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1283 x_return_status := G_RET_STS_UNEXP_ERROR;
1284
1285 WHEN OTHERS THEN
1286 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1287 p_msg_name => G_DB_ERROR,
1288 p_token1 => G_PROG_NAME_TOKEN,
1289 p_token1_value => l_program_name,
1290 p_token2 => G_SQLCODE_TOKEN,
1291 p_token2_value => sqlcode,
1292 p_token3 => G_SQLERRM_TOKEN,
1293 p_token3_value => sqlerrm);
1294
1295 x_return_status := G_RET_STS_UNEXP_ERROR;
1296
1297 END update_expense;
1298
1299
1300 ---------------------------
1301 -- PROCEDURE create_service
1302 ---------------------------
1303 PROCEDURE create_service (
1304 p_api_version IN NUMBER
1305 ,p_init_msg_list IN VARCHAR2
1306 ,p_transaction_control IN VARCHAR2
1307 ,p_service_rec IN okl_svc_pvt.svcv_rec_type
1308 ,p_assoc_asset_tbl IN line_relation_tbl_type
1309 ,p_payment_header_rec IN okl_lease_quote_cashflow_pvt.cashflow_header_rec_type
1310 ,p_payment_level_tbl IN okl_lease_quote_cashflow_pvt.cashflow_level_tbl_type
1311 ,p_expense_header_rec IN okl_lease_quote_cashflow_pvt.cashflow_header_rec_type
1312 ,p_expense_level_tbl IN okl_lease_quote_cashflow_pvt.cashflow_level_tbl_type
1313 ,x_service_id OUT NOCOPY NUMBER
1314 ,x_return_status OUT NOCOPY VARCHAR2
1315 ,x_msg_count OUT NOCOPY NUMBER
1316 ,x_msg_data OUT NOCOPY VARCHAR2
1317 ) IS
1318
1319 l_program_name CONSTANT VARCHAR2(30) := 'create_service';
1320 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
1321
1322 l_line_relation_tbl line_relation_tbl_type;
1323
1324 l_periodic_amount NUMBER;
1325 l_service_amount NUMBER;
1326 l_currency_code VARCHAR2(15);
1327
1328 l_return_status VARCHAR2(1);
1329 l_derive_assoc_amt VARCHAR2(1);
1330
1331 BEGIN
1332
1333 IF p_transaction_control = G_TRUE THEN
1334 SAVEPOINT l_program_name;
1335 END IF;
1336
1337 IF p_init_msg_list = G_TRUE THEN
1338 FND_MSG_PUB.initialize;
1339 END IF;
1340
1341 get_currency_code (
1342 p_parent_object_id => p_service_rec.parent_object_id
1343 ,x_currency_code => l_currency_code
1344 ,x_return_status => l_return_status
1345 );
1346
1347 IF p_expense_level_tbl(p_expense_level_tbl.FIRST).periods <>
1348 TRUNC (p_expense_level_tbl(p_expense_level_tbl.FIRST).periods) THEN
1349
1350 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME, p_msg_name => 'OKL_RECEXP_PERIODS_INVALID');
1351 RAISE OKL_API.G_EXCEPTION_ERROR;
1352
1353 END IF;
1354
1355 l_periodic_amount :=
1356 okl_accounting_util.round_amount( p_amount => p_expense_level_tbl(p_expense_level_tbl.FIRST).periodic_amount
1357 ,p_currency_code => l_currency_code);
1358
1359 l_service_amount := l_periodic_amount * p_expense_level_tbl(p_expense_level_tbl.FIRST).periods;
1360
1361 validate_header (
1362 p_service_rec => p_service_rec
1363 ,x_return_status => l_return_status
1364 );
1365
1366 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
1367 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1368 ELSIF l_return_status = G_RET_STS_ERROR THEN
1369 RAISE OKL_API.G_EXCEPTION_ERROR;
1370 END IF;
1371
1372 IF p_assoc_asset_tbl.COUNT > 0 THEN
1373
1374 l_line_relation_tbl := p_assoc_asset_tbl;
1375
1376 validate_link_assets (
1377 p_service_amount => l_service_amount
1378 ,p_assoc_assets_tbl => l_line_relation_tbl
1379 ,x_derive_assoc_amt => l_derive_assoc_amt
1380 ,x_return_status => l_return_status
1381 );
1382
1383 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
1384 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1385 ELSIF l_return_status = G_RET_STS_ERROR THEN
1386 RAISE OKL_API.G_EXCEPTION_ERROR;
1387 END IF;
1388
1389 IF (l_derive_assoc_amt = 'Y') THEN
1390
1391 process_link_asset_amounts (
1392 p_quote_id => p_service_rec.parent_object_id
1393 ,p_currency_code => l_currency_code
1394 ,p_service_amount => l_service_amount
1395 ,p_link_asset_tbl => l_line_relation_tbl
1396 ,p_derive_assoc_amt => 'Y'
1397 ,x_return_status => l_return_status
1398 ,x_msg_count => x_msg_count
1399 ,x_msg_data => x_msg_data
1400 );
1401
1402 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
1403 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1404 ELSIF l_return_status = G_RET_STS_ERROR THEN
1405 RAISE OKL_API.G_EXCEPTION_ERROR;
1406 END IF;
1407
1408 END IF;
1409
1410 END IF;
1411
1412 create_header (
1413 p_service_rec => p_service_rec
1414 ,x_service_id => x_service_id
1415 ,x_return_status => l_return_status
1416 ,x_msg_count => x_msg_count
1417 ,x_msg_data => x_msg_data
1418 );
1419
1420 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
1421 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1422 ELSIF l_return_status = G_RET_STS_ERROR THEN
1423 RAISE OKL_API.G_EXCEPTION_ERROR;
1424 END IF;
1425
1426 IF l_line_relation_tbl.COUNT > 0 THEN
1427
1428 create_line_associations (
1429 p_service_id => x_service_id
1430 ,p_assoc_assets_tbl => l_line_relation_tbl
1431 ,x_return_status => l_return_status
1432 ,x_msg_count => x_msg_count
1433 ,x_msg_data => x_msg_data
1434 );
1435
1436 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
1437 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1438 ELSIF l_return_status = G_RET_STS_ERROR THEN
1439 RAISE OKL_API.G_EXCEPTION_ERROR;
1440 END IF;
1441
1442 END IF;
1443
1444 IF (p_payment_level_tbl.COUNT > 0 AND p_payment_header_rec.stream_type_id IS NULL) THEN
1445 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1446 p_msg_name => 'OKL_SALES_NO_PAYMENTHEAD');
1447 RAISE OKL_API.G_EXCEPTION_ERROR;
1448 ELSIF (p_payment_header_rec.stream_type_id IS NOT NULL AND p_payment_level_tbl.COUNT = 0 ) THEN
1449 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1450 p_msg_name => 'OKL_SALES_NO_PAYMENTLINES');
1451 RAISE OKL_API.G_EXCEPTION_ERROR;
1452 END IF;
1453
1454 IF p_payment_level_tbl.COUNT > 0 THEN
1455
1456 create_payment (
1457 p_service_id => x_service_id
1458 ,p_payment_header_rec => p_payment_header_rec
1459 ,p_payment_level_tbl => p_payment_level_tbl
1460 ,x_return_status => l_return_status
1461 ,x_msg_count => x_msg_count
1462 ,x_msg_data => x_msg_data
1463 );
1464
1465 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
1466 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1467 ELSIF l_return_status = G_RET_STS_ERROR THEN
1468 RAISE OKL_API.G_EXCEPTION_ERROR;
1469 END IF;
1470
1471 END IF;
1472
1473 create_expense (
1474 p_service_id => x_service_id
1475 ,p_expense_header_rec => p_expense_header_rec
1476 ,p_expense_level_tbl => p_expense_level_tbl
1477 ,x_return_status => l_return_status
1478 ,x_msg_count => x_msg_count
1479 ,x_msg_data => x_msg_data
1480 );
1481
1482 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
1483 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1484 ELSIF l_return_status = G_RET_STS_ERROR THEN
1485 RAISE OKL_API.G_EXCEPTION_ERROR;
1486 END IF;
1487
1488 x_return_status := G_RET_STS_SUCCESS;
1489
1490 EXCEPTION
1491
1492 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1493
1494 IF p_transaction_control = G_TRUE THEN
1495 ROLLBACK TO l_program_name;
1496 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1497 END IF;
1498
1499 x_return_status := G_RET_STS_ERROR;
1500
1501 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1502
1503 IF p_transaction_control = G_TRUE THEN
1504 ROLLBACK TO l_program_name;
1505 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1506 END IF;
1507
1508 x_return_status := G_RET_STS_UNEXP_ERROR;
1509
1510 WHEN OTHERS THEN
1511
1512 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1513 p_msg_name => G_DB_ERROR,
1514 p_token1 => G_PROG_NAME_TOKEN,
1515 p_token1_value => l_api_name,
1516 p_token2 => G_SQLCODE_TOKEN,
1517 p_token2_value => sqlcode,
1518 p_token3 => G_SQLERRM_TOKEN,
1519 p_token3_value => sqlerrm);
1520
1521 IF p_transaction_control = G_TRUE THEN
1522 ROLLBACK TO l_program_name;
1523 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1524 END IF;
1525
1526 x_return_status := G_RET_STS_UNEXP_ERROR;
1527
1528 END create_service;
1529
1530 -----------------------------------
1531 -- PROCEDURE get_line_relations_tbl
1532 -----------------------------------
1533 PROCEDURE get_line_relations_tbl (
1534 p_service_id IN NUMBER
1535 ,x_line_relation_tbl OUT NOCOPY line_relation_tbl_type
1536 ,x_return_status OUT NOCOPY VARCHAR2 ) IS
1537
1538 l_program_name CONSTANT VARCHAR2(30) := 'get_line_relations_tbl';
1539 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
1540 i BINARY_INTEGER := 0;
1541
1542 CURSOR c_db_line_relations IS
1543 SELECT
1544 id
1545 ,object_version_number
1546 ,source_line_type
1547 ,source_line_id
1548 ,related_line_type
1549 ,related_line_id
1550 ,amount
1551 ,short_description
1552 ,description
1553 ,comments
1554 FROM okl_line_relationships_v
1555 WHERE related_line_id = p_service_id;
1556 BEGIN
1557 FOR l_db_line_relations IN c_db_line_relations LOOP
1558 x_line_relation_tbl(i).id := l_db_line_relations.id;
1559 x_line_relation_tbl(i).object_version_number := l_db_line_relations.object_version_number;
1560 x_line_relation_tbl(i).source_line_type := l_db_line_relations.source_line_type;
1561 x_line_relation_tbl(i).source_line_id := l_db_line_relations.source_line_id;
1562 x_line_relation_tbl(i).related_line_type := l_db_line_relations.related_line_type;
1563 x_line_relation_tbl(i).related_line_id := l_db_line_relations.related_line_id;
1564 x_line_relation_tbl(i).amount := l_db_line_relations.amount;
1565 x_line_relation_tbl(i).short_description := l_db_line_relations.short_description;
1566 x_line_relation_tbl(i).description := l_db_line_relations.description;
1567 x_line_relation_tbl(i).comments := l_db_line_relations.comments;
1568 i := i + 1;
1569 END LOOP;
1570
1571 x_return_status := G_RET_STS_SUCCESS;
1572 EXCEPTION
1573
1574 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1575 x_return_status := G_RET_STS_ERROR;
1576
1577 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1578 x_return_status := G_RET_STS_UNEXP_ERROR;
1579
1580 WHEN OTHERS THEN
1581 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1582 p_msg_name => G_DB_ERROR,
1583 p_token1 => G_PROG_NAME_TOKEN,
1584 p_token1_value => l_program_name,
1585 p_token2 => G_SQLCODE_TOKEN,
1586 p_token2_value => sqlcode,
1587 p_token3 => G_SQLERRM_TOKEN,
1588 p_token3_value => sqlerrm);
1589
1590 x_return_status := G_RET_STS_UNEXP_ERROR;
1591
1592 END get_line_relations_tbl;
1593
1594 ----------------------------------
1595 -- PROCEDURE update_service_assets
1596 ----------------------------------
1597 PROCEDURE update_service_assets (
1598 p_api_version IN NUMBER
1599 ,p_init_msg_list IN VARCHAR2
1600 ,p_transaction_control IN VARCHAR2
1601 ,p_quote_id IN NUMBER
1602 ,p_service_id IN NUMBER
1603 ,x_return_status OUT NOCOPY VARCHAR2
1604 ,x_msg_count OUT NOCOPY NUMBER
1605 ,x_msg_data OUT NOCOPY VARCHAR2 ) IS
1606
1607 l_program_name CONSTANT VARCHAR2(30) := 'update_service_assets';
1608 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
1609
1610 l_copy_relation_tbl line_relation_tbl_type;
1611
1612 l_line_relation_tbl lr_tbl_type;
1613 lx_line_relation_tbl lr_tbl_type;
1614
1615 ln_service_amount NUMBER;
1616 lv_currency_code VARCHAR2(30);
1617
1618 BEGIN
1619
1620 IF p_transaction_control = G_TRUE THEN
1621 SAVEPOINT l_program_name;
1622 END IF;
1623
1624 IF p_init_msg_list = G_TRUE THEN
1625 FND_MSG_PUB.initialize;
1626 END IF;
1627
1628 -- Fetch Line Relationships info
1629 get_line_relations_tbl (p_service_id => p_service_id
1630 ,x_line_relation_tbl => l_copy_relation_tbl
1631 ,x_return_status => x_return_status);
1632 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
1633 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1634 ELSIF x_return_status = G_RET_STS_ERROR THEN
1635 RAISE OKL_API.G_EXCEPTION_ERROR;
1636 END IF;
1637
1638 SELECT LVL.AMOUNT * LVL.NUMBER_OF_PERIODS
1639 INTO ln_service_amount
1640 FROM OKL_CASH_FLOW_OBJECTS CFO,
1641 OKL_CASH_FLOWS CFL,
1642 OKL_CASH_FLOW_LEVELS LVL
1643 WHERE CFO.SOURCE_ID = p_service_id
1644 AND CFO.OTY_CODE = 'QUOTED_SERVICE'
1645 AND CFO.SOURCE_TABLE = 'OKL_SERVICES_B'
1646 AND CFL.CFT_CODE = 'OUTFLOW_SCHEDULE'
1647 AND CFL.CFO_ID = CFO.ID
1648 AND LVL.CAF_ID = CFL.ID;
1649
1650 get_currency_code ( p_parent_object_id => p_quote_id
1651 ,x_currency_code => lv_currency_code
1652 ,x_return_status => x_return_status );
1653
1654 IF (l_copy_relation_tbl.COUNT > 0) THEN
1655 process_link_asset_amounts (
1656 p_quote_id => p_quote_id
1657 ,p_currency_code => lv_currency_code
1658 ,p_service_amount => ln_service_amount
1659 ,p_link_asset_tbl => l_copy_relation_tbl
1660 ,p_derive_assoc_amt => 'Y'
1661 ,p_override_pricing_type => 'Y'
1662 ,x_return_status => x_return_status
1663 ,x_msg_count => x_msg_count
1664 ,x_msg_data => x_msg_data );
1665
1666 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
1667 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1668 ELSIF x_return_status = G_RET_STS_ERROR THEN
1669 RAISE OKL_API.G_EXCEPTION_ERROR;
1670 END IF;
1671
1672 FOR i IN l_copy_relation_tbl.FIRST .. l_copy_relation_tbl.LAST LOOP
1673 IF l_copy_relation_tbl.EXISTS(i) THEN
1674 l_line_relation_tbl(i).id := l_copy_relation_tbl(i).id;
1675 l_line_relation_tbl(i).object_version_number := l_copy_relation_tbl(i).object_version_number;
1676 l_line_relation_tbl(i).source_line_type := l_copy_relation_tbl(i).source_line_type;
1677 l_line_relation_tbl(i).source_line_id := l_copy_relation_tbl(i).source_line_id;
1678 l_line_relation_tbl(i).related_line_type := l_copy_relation_tbl(i).related_line_type;
1679 l_line_relation_tbl(i).related_line_id := l_copy_relation_tbl(i).related_line_id;
1680 l_line_relation_tbl(i).amount := l_copy_relation_tbl(i).amount;
1681 END IF;
1682 END LOOP;
1683
1684 IF (l_line_relation_tbl.COUNT > 0) THEN
1685 okl_lre_pvt.update_row (
1686 p_api_version => G_API_VERSION
1687 ,p_init_msg_list => G_FALSE
1688 ,x_return_status => x_return_status
1689 ,x_msg_count => x_msg_count
1690 ,x_msg_data => x_msg_data
1691 ,p_lrev_tbl => l_line_relation_tbl
1692 ,x_lrev_tbl => lx_line_relation_tbl);
1693
1694 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
1695 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1696 ELSIF x_return_status = G_RET_STS_ERROR THEN
1697 RAISE OKL_API.G_EXCEPTION_ERROR;
1698 END IF;
1699 END IF;
1700 END IF;
1701
1702 x_return_status := G_RET_STS_SUCCESS;
1703
1704 EXCEPTION
1705
1706 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1707
1708 IF p_transaction_control = G_TRUE THEN
1709 ROLLBACK TO l_program_name;
1710 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1711 END IF;
1712
1713 x_return_status := G_RET_STS_ERROR;
1714
1715 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1716
1717 IF p_transaction_control = G_TRUE THEN
1718 ROLLBACK TO l_program_name;
1719 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1720 END IF;
1721
1722 x_return_status := G_RET_STS_UNEXP_ERROR;
1723
1724 WHEN OTHERS THEN
1725
1726 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1727 p_msg_name => G_DB_ERROR,
1728 p_token1 => G_PROG_NAME_TOKEN,
1729 p_token1_value => l_api_name,
1730 p_token2 => G_SQLCODE_TOKEN,
1731 p_token2_value => sqlcode,
1732 p_token3 => G_SQLERRM_TOKEN,
1733 p_token3_value => sqlerrm);
1734
1735 IF p_transaction_control = G_TRUE THEN
1736 ROLLBACK TO l_program_name;
1737 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1738 END IF;
1739
1740 x_return_status := G_RET_STS_UNEXP_ERROR;
1741
1742 END update_service_assets ;
1743
1744 ---------------------------
1745 -- PROCEDURE update_service
1746 ---------------------------
1747 PROCEDURE update_service (
1748 p_api_version IN NUMBER
1749 ,p_init_msg_list IN VARCHAR2
1750 ,p_transaction_control IN VARCHAR2
1751 ,p_service_rec IN okl_svc_pvt.svcv_rec_type
1752 ,p_assoc_asset_tbl IN line_relation_tbl_type
1753 ,p_payment_header_rec IN okl_lease_quote_cashflow_pvt.cashflow_header_rec_type
1754 ,p_payment_level_tbl IN okl_lease_quote_cashflow_pvt.cashflow_level_tbl_type
1755 ,p_expense_header_rec IN okl_lease_quote_cashflow_pvt.cashflow_header_rec_type
1756 ,p_expense_level_tbl IN okl_lease_quote_cashflow_pvt.cashflow_level_tbl_type
1757 ,x_return_status OUT NOCOPY VARCHAR2
1758 ,x_msg_count OUT NOCOPY NUMBER
1759 ,x_msg_data OUT NOCOPY VARCHAR2
1760 ) IS
1761
1762 l_program_name CONSTANT VARCHAR2(30) := 'update_service';
1763 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
1764
1765 l_line_relation_tbl line_relation_tbl_type;
1766
1767 l_periodic_amount NUMBER;
1768 l_service_amount NUMBER;
1769 l_currency_code VARCHAR2(15);
1770
1771 l_return_status VARCHAR2(1);
1772 l_derive_assoc_amt VARCHAR2(1);
1773
1774 BEGIN
1775
1776 IF p_transaction_control = G_TRUE THEN
1777 SAVEPOINT l_program_name;
1778 END IF;
1779
1780 IF p_init_msg_list = G_TRUE THEN
1781 FND_MSG_PUB.initialize;
1782 END IF;
1783
1784 get_currency_code (
1785 p_parent_object_id => p_service_rec.parent_object_id
1786 ,x_currency_code => l_currency_code
1787 ,x_return_status => l_return_status
1788 );
1789
1790 IF p_expense_level_tbl(p_expense_level_tbl.FIRST).periods <>
1791 TRUNC (p_expense_level_tbl(p_expense_level_tbl.FIRST).periods) THEN
1792
1793 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME, p_msg_name => 'OKL_RECEXP_PERIODS_INVALID');
1794 RAISE OKL_API.G_EXCEPTION_ERROR;
1795
1796 END IF;
1797
1798 l_periodic_amount :=
1799 okl_accounting_util.round_amount( p_amount => p_expense_level_tbl(p_expense_level_tbl.FIRST).periodic_amount
1800 ,p_currency_code => l_currency_code);
1801
1802 l_service_amount := l_periodic_amount * p_expense_level_tbl(p_expense_level_tbl.FIRST).periods;
1803
1804 validate_header (
1805 p_service_rec => p_service_rec
1806 ,x_return_status => l_return_status
1807 );
1808
1809 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
1810 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1811 ELSIF l_return_status = G_RET_STS_ERROR THEN
1812 RAISE OKL_API.G_EXCEPTION_ERROR;
1813 END IF;
1814
1815 IF p_assoc_asset_tbl.COUNT > 0 THEN
1816
1817 l_line_relation_tbl := p_assoc_asset_tbl;
1818
1819 validate_link_assets (
1820 p_service_amount => l_service_amount
1821 ,p_assoc_assets_tbl => l_line_relation_tbl
1822 ,x_derive_assoc_amt => l_derive_assoc_amt
1823 ,x_return_status => l_return_status
1824 );
1825
1826 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
1827 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1828 ELSIF l_return_status = G_RET_STS_ERROR THEN
1829 RAISE OKL_API.G_EXCEPTION_ERROR;
1830 END IF;
1831
1832 IF (l_derive_assoc_amt = 'Y') THEN
1833
1834 process_link_asset_amounts (
1835 p_quote_id => p_service_rec.parent_object_id
1836 ,p_currency_code => l_currency_code
1837 ,p_service_amount => l_service_amount
1838 ,p_link_asset_tbl => l_line_relation_tbl
1839 ,p_derive_assoc_amt => 'Y'
1840 ,x_return_status => l_return_status
1841 ,x_msg_count => x_msg_count
1842 ,x_msg_data => x_msg_data
1843 );
1844
1845 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
1846 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1847 ELSIF l_return_status = G_RET_STS_ERROR THEN
1848 RAISE OKL_API.G_EXCEPTION_ERROR;
1849 END IF;
1850
1851 END IF;
1852
1853 END IF;
1854
1855 update_header (
1856 p_service_rec => p_service_rec
1857 ,x_return_status => l_return_status
1858 ,x_msg_count => x_msg_count
1859 ,x_msg_data => x_msg_data
1860 );
1861
1862 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
1863 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1864 ELSIF l_return_status = G_RET_STS_ERROR THEN
1865 RAISE OKL_API.G_EXCEPTION_ERROR;
1866 END IF;
1867
1868 IF l_line_relation_tbl.COUNT > 0 THEN
1869
1870 update_line_associations (
1871 p_service_id => p_service_rec.id
1872 ,p_assoc_assets_tbl => l_line_relation_tbl
1873 ,x_return_status => l_return_status
1874 ,x_msg_count => x_msg_count
1875 ,x_msg_data => x_msg_data
1876 );
1877
1878 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
1879 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1880 ELSIF l_return_status = G_RET_STS_ERROR THEN
1881 RAISE OKL_API.G_EXCEPTION_ERROR;
1882 END IF;
1883
1884 END IF;
1885
1886 IF (p_payment_level_tbl.COUNT > 0 AND p_payment_header_rec.stream_type_id IS NULL) THEN
1887 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1888 p_msg_name => 'OKL_SALES_NO_PAYMENTHEAD');
1889 RAISE OKL_API.G_EXCEPTION_ERROR;
1890 ELSIF (p_payment_header_rec.stream_type_id IS NOT NULL AND p_payment_level_tbl.COUNT = 0 ) THEN
1891 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1892 p_msg_name => 'OKL_SALES_NO_PAYMENTLINES');
1893 RAISE OKL_API.G_EXCEPTION_ERROR;
1894 END IF;
1895
1896 IF p_payment_level_tbl.COUNT > 0 THEN
1897
1898 update_payment (
1899 p_service_id => p_service_rec.id
1900 ,p_payment_header_rec => p_payment_header_rec
1901 ,p_payment_level_tbl => p_payment_level_tbl
1902 ,x_return_status => l_return_status
1903 ,x_msg_count => x_msg_count
1904 ,x_msg_data => x_msg_data
1905 );
1906
1907 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
1908 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1909 ELSIF l_return_status = G_RET_STS_ERROR THEN
1910 RAISE OKL_API.G_EXCEPTION_ERROR;
1911 END IF;
1912
1913 END IF;
1914
1915 update_expense (
1916 p_service_id => p_service_rec.id
1917 ,p_expense_header_rec => p_expense_header_rec
1918 ,p_expense_level_tbl => p_expense_level_tbl
1919 ,x_return_status => l_return_status
1920 ,x_msg_count => x_msg_count
1921 ,x_msg_data => x_msg_data
1922 );
1923
1924 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
1925 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1926 ELSIF l_return_status = G_RET_STS_ERROR THEN
1927 RAISE OKL_API.G_EXCEPTION_ERROR;
1928 END IF;
1929
1930 x_return_status := G_RET_STS_SUCCESS;
1931
1932 EXCEPTION
1933
1934 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1935
1936 IF p_transaction_control = G_TRUE THEN
1937 ROLLBACK TO l_program_name;
1938 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1939 END IF;
1940
1941 x_return_status := G_RET_STS_ERROR;
1942
1943 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1944
1945 IF p_transaction_control = G_TRUE THEN
1946 ROLLBACK TO l_program_name;
1947 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1948 END IF;
1949
1950 x_return_status := G_RET_STS_UNEXP_ERROR;
1951
1952 WHEN OTHERS THEN
1953
1954 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1955 p_msg_name => G_DB_ERROR,
1956 p_token1 => G_PROG_NAME_TOKEN,
1957 p_token1_value => l_api_name,
1958 p_token2 => G_SQLCODE_TOKEN,
1959 p_token2_value => sqlcode,
1960 p_token3 => G_SQLERRM_TOKEN,
1961 p_token3_value => sqlerrm);
1962
1963 IF p_transaction_control = G_TRUE THEN
1964 ROLLBACK TO l_program_name;
1965 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1966 END IF;
1967
1968 x_return_status := G_RET_STS_UNEXP_ERROR;
1969
1970 END update_service;
1971
1972
1973 --------------------------
1974 -- PROCEDURE duplicate_service
1975 --------------------------
1976 PROCEDURE duplicate_service (
1977 p_api_version IN NUMBER
1978 ,p_init_msg_list IN VARCHAR2
1979 ,p_transaction_control IN VARCHAR2
1980 ,p_source_service_id IN NUMBER
1981 ,p_service_rec IN okl_svc_pvt.svcv_rec_type
1982 ,p_assoc_asset_tbl IN line_relation_tbl_type
1983 ,p_payment_header_rec IN okl_lease_quote_cashflow_pvt.cashflow_header_rec_type
1984 ,p_payment_level_tbl IN okl_lease_quote_cashflow_pvt.cashflow_level_tbl_type
1985 ,p_expense_header_rec IN okl_lease_quote_cashflow_pvt.cashflow_header_rec_type
1986 ,p_expense_level_tbl IN okl_lease_quote_cashflow_pvt.cashflow_level_tbl_type
1987 ,x_service_id OUT NOCOPY NUMBER
1988 ,x_return_status OUT NOCOPY VARCHAR2
1989 ,x_msg_count OUT NOCOPY NUMBER
1990 ,x_msg_data OUT NOCOPY VARCHAR2
1991 ) IS
1992
1993 l_program_name CONSTANT VARCHAR2(30) := 'duplicate_service';
1994 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
1995
1996 BEGIN
1997
1998 IF p_transaction_control = G_TRUE THEN
1999 SAVEPOINT l_program_name;
2000 END IF;
2001
2002 IF p_init_msg_list = G_TRUE THEN
2003 FND_MSG_PUB.initialize;
2004 END IF;
2005
2006 create_service (
2007 p_api_version => G_API_VERSION
2008 ,p_init_msg_list => G_FALSE
2009 ,p_transaction_control => G_FALSE
2010 ,p_service_rec => p_service_rec
2011 ,p_assoc_asset_tbl => p_assoc_asset_tbl
2012 ,p_payment_header_rec => p_payment_header_rec
2013 ,p_payment_level_tbl => p_payment_level_tbl
2014 ,p_expense_header_rec => p_expense_header_rec
2015 ,p_expense_level_tbl => p_expense_level_tbl
2016 ,x_service_id => x_service_id
2017 ,x_return_status => x_return_status
2018 ,x_msg_count => x_msg_count
2019 ,x_msg_data => x_msg_data
2020 );
2021
2022 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
2023 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2024 ELSIF x_return_status = G_RET_STS_ERROR THEN
2025 RAISE OKL_API.G_EXCEPTION_ERROR;
2026 END IF;
2027
2028 x_return_status := G_RET_STS_SUCCESS;
2029
2030 EXCEPTION
2031
2032 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2033
2034 IF p_transaction_control = G_TRUE THEN
2035 ROLLBACK TO l_program_name;
2036 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
2037 END IF;
2038
2039 x_return_status := G_RET_STS_ERROR;
2040
2041 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2042
2043 IF p_transaction_control = G_TRUE THEN
2044 ROLLBACK TO l_program_name;
2045 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
2046 END IF;
2047
2048 x_return_status := G_RET_STS_UNEXP_ERROR;
2049
2050 WHEN OTHERS THEN
2051
2052 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
2053 p_msg_name => G_DB_ERROR,
2054 p_token1 => G_PROG_NAME_TOKEN,
2055 p_token1_value => l_api_name,
2056 p_token2 => G_SQLCODE_TOKEN,
2057 p_token2_value => sqlcode,
2058 p_token3 => G_SQLERRM_TOKEN,
2059 p_token3_value => sqlerrm);
2060
2061 IF p_transaction_control = G_TRUE THEN
2062 ROLLBACK TO l_program_name;
2063 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
2064 END IF;
2065
2066 x_return_status := G_RET_STS_UNEXP_ERROR;
2067
2068 END duplicate_service;
2069
2070
2071 --------------------------
2072 -- PROCEDURE duplicate_service
2073 --------------------------
2074 PROCEDURE duplicate_service (
2075 p_api_version IN NUMBER
2076 ,p_init_msg_list IN VARCHAR2
2077 ,p_transaction_control IN VARCHAR2
2078 ,p_source_service_id IN NUMBER
2079 ,p_target_quote_id IN NUMBER
2080 ,x_service_id OUT NOCOPY NUMBER
2081 ,x_return_status OUT NOCOPY VARCHAR2
2082 ,x_msg_count OUT NOCOPY NUMBER
2083 ,x_msg_data OUT NOCOPY VARCHAR2
2084 ) IS
2085
2086 l_program_name CONSTANT VARCHAR2(30) := 'duplicate_service2';
2087 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
2088
2089 l_service_rec okl_svc_pvt.svcv_rec_type;
2090 lx_service_rec okl_svc_pvt.svcv_rec_type;
2091
2092 lb_copy_cashflow BOOLEAN := TRUE;
2093 lb_copy_lr BOOLEAN := TRUE;
2094 ld_src_start_date DATE;
2095 ld_tgt_start_date DATE;
2096 ln_src_pdt_id NUMBER;
2097 ln_tgt_pdt_id NUMBER;
2098 ln_src_eot_id NUMBER;
2099 ln_tgt_eot_id NUMBER;
2100
2101 BEGIN
2102
2103 IF p_transaction_control = G_TRUE THEN
2104 SAVEPOINT l_program_name;
2105 END IF;
2106
2107 IF p_init_msg_list = G_TRUE THEN
2108 FND_MSG_PUB.initialize;
2109 END IF;
2110
2111 get_service_rec (
2112 p_service_id => p_source_service_id
2113 ,x_service_rec => l_service_rec
2114 ,x_return_status => x_return_status);
2115
2116 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
2117 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2118 ELSIF x_return_status = G_RET_STS_ERROR THEN
2119 RAISE OKL_API.G_EXCEPTION_ERROR;
2120 END IF;
2121
2122 l_service_rec.parent_object_id := p_target_quote_id;
2123
2124 create_header (
2125 p_service_rec => l_service_rec
2126 ,x_service_id => x_service_id
2127 ,x_return_status => x_return_status
2128 ,x_msg_count => x_msg_count
2129 ,x_msg_data => x_msg_data
2130 );
2131
2132 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
2133 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2134 ELSIF x_return_status = G_RET_STS_ERROR THEN
2135 RAISE OKL_API.G_EXCEPTION_ERROR;
2136 END IF;
2137
2138 -- Validation to check if the product and expected start date for source
2139 -- and target contracts are equal, if not cash flows are not copied.
2140 SELECT quote.expected_start_date,
2141 quote.product_id,
2142 quote.end_of_term_option_id
2143 INTO ld_src_start_date, ln_src_pdt_id, ln_src_eot_id
2144 FROM
2145 okl_services_b srv,
2146 okl_lease_quotes_b quote
2147 WHERE
2148 srv.id = p_source_service_id
2149 AND srv.parent_object_id = quote.id
2150 AND srv.parent_object_code = 'LEASEQUOTE';
2151
2152 SELECT expected_start_date,
2153 product_id,
2154 end_of_term_option_id
2155 INTO ld_tgt_start_date, ln_tgt_pdt_id, ln_tgt_eot_id
2156 FROM
2157 okl_lease_quotes_b
2158 WHERE
2159 id = p_target_quote_id;
2160
2161 IF ((ld_src_start_date <> ld_tgt_start_date) OR (ln_src_pdt_id <> ln_tgt_pdt_id)) THEN
2162 lb_copy_cashflow := FALSE;
2163 END IF;
2164 -- End
2165
2166 IF (ln_src_eot_id <> ln_tgt_eot_id) THEN
2167 lb_copy_lr := FALSE;
2168 END IF;
2169
2170 IF (lb_copy_cashflow) THEN
2171 copy_line_associations( p_source_service_id => p_source_service_id,
2172 p_target_service_id => x_service_id,
2173 x_return_status => x_return_status,
2174 x_msg_count => x_msg_count,
2175 x_msg_data => x_msg_data );
2176 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
2177 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2178 ELSIF x_return_status = G_RET_STS_ERROR THEN
2179 RAISE OKL_API.G_EXCEPTION_ERROR;
2180 END IF;
2181 END IF;
2182
2183 IF (lb_copy_cashflow) THEN
2184 okl_lease_quote_cashflow_pvt.duplicate_cashflows (
2185 p_api_version => G_API_VERSION
2186 ,p_init_msg_list => G_FALSE
2187 ,p_transaction_control => G_FALSE
2188 ,p_source_object_code => 'QUOTED_SERVICE'
2189 ,p_source_object_id => p_source_service_id
2190 ,p_target_object_id => x_service_id
2191 ,p_quote_id => p_target_quote_id
2192 ,x_return_status => x_return_status
2193 ,x_msg_count => x_msg_count
2194 ,x_msg_data => x_msg_data );
2195
2196 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
2197 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2198 ELSIF x_return_status = G_RET_STS_ERROR THEN
2199 RAISE OKL_API.G_EXCEPTION_ERROR;
2200 END IF;
2201 END IF;
2202
2203 x_return_status := G_RET_STS_SUCCESS;
2204
2205 EXCEPTION
2206
2207 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2208
2209 IF p_transaction_control = G_TRUE THEN
2210 ROLLBACK TO l_program_name;
2211 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
2212 END IF;
2213
2214 x_return_status := G_RET_STS_ERROR;
2215
2216 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2217
2218 IF p_transaction_control = G_TRUE THEN
2219 ROLLBACK TO l_program_name;
2220 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
2221 END IF;
2222
2223 x_return_status := G_RET_STS_UNEXP_ERROR;
2224
2225 WHEN OTHERS THEN
2226
2227 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
2228 p_msg_name => G_DB_ERROR,
2229 p_token1 => G_PROG_NAME_TOKEN,
2230 p_token1_value => l_api_name,
2231 p_token2 => G_SQLCODE_TOKEN,
2232 p_token2_value => sqlcode,
2233 p_token3 => G_SQLERRM_TOKEN,
2234 p_token3_value => sqlerrm);
2235
2236 IF p_transaction_control = G_TRUE THEN
2237 ROLLBACK TO l_program_name;
2238 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
2239 END IF;
2240
2241 x_return_status := G_RET_STS_UNEXP_ERROR;
2242
2243 END duplicate_service;
2244
2245
2246 -----------------------
2247 -- PROCEDURE delete_service
2248 -----------------------
2249 PROCEDURE delete_service (
2250 p_api_version IN NUMBER
2251 ,p_init_msg_list IN VARCHAR2
2252 ,p_transaction_control IN VARCHAR2
2253 ,p_service_id IN NUMBER
2254 ,x_return_status OUT NOCOPY VARCHAR2
2255 ,x_msg_count OUT NOCOPY NUMBER
2256 ,x_msg_data OUT NOCOPY VARCHAR2
2257 ) IS
2258
2259 l_program_name CONSTANT VARCHAR2(30) := 'delete_service';
2260 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
2261
2262 l_lrev_tbl okl_lre_pvt.lrev_tbl_type;
2263 l_svcv_rec okl_svc_pvt.svcv_rec_type;
2264 i BINARY_INTEGER;
2265
2266 CURSOR c_sublines IS
2267 SELECT id
2268 FROM okl_line_relationships_b
2269 WHERE related_line_type = 'SERVICE'
2270 AND related_line_id = p_service_id;
2271
2272 BEGIN
2273
2274 IF p_transaction_control = G_TRUE THEN
2275 SAVEPOINT l_program_name;
2276 END IF;
2277
2278 IF p_init_msg_list = G_TRUE THEN
2279 FND_MSG_PUB.initialize;
2280 END IF;
2281
2282 i := 0;
2283 FOR l_subline IN c_sublines LOOP
2284 l_lrev_tbl(i).id := l_subline.id;
2285 i := i + 1;
2286 END LOOP;
2287
2288 IF l_lrev_tbl.COUNT > 0 THEN
2289
2290 okl_lre_pvt.delete_row (
2291 p_api_version => G_API_VERSION
2292 ,p_init_msg_list => G_FALSE
2293 ,x_return_status => x_return_status
2294 ,x_msg_count => x_msg_count
2295 ,x_msg_data => x_msg_data
2296 ,p_lrev_tbl => l_lrev_tbl
2297 );
2298
2299 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
2300 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2301 ELSIF x_return_status = G_RET_STS_ERROR THEN
2302 RAISE OKL_API.G_EXCEPTION_ERROR;
2303 END IF;
2304
2305 END IF;
2306
2307 okl_lease_quote_cashflow_pvt.delete_cashflows (
2308 p_api_version => G_API_VERSION
2309 ,p_init_msg_list => G_FALSE
2310 ,p_transaction_control => G_FALSE
2311 ,p_source_object_code => 'QUOTED_SERVICE'
2312 ,p_source_object_id => p_service_id
2313 ,x_return_status => x_return_status
2314 ,x_msg_count => x_msg_count
2315 ,x_msg_data => x_msg_data
2316 );
2317
2318 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
2319 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2320 ELSIF x_return_status = G_RET_STS_ERROR THEN
2321 RAISE OKL_API.G_EXCEPTION_ERROR;
2322 END IF;
2323
2324 l_svcv_rec.id := p_service_id;
2325
2326 okl_svc_pvt.delete_row (
2327 p_api_version => G_API_VERSION
2328 ,p_init_msg_list => G_FALSE
2329 ,x_return_status => x_return_status
2330 ,x_msg_count => x_msg_count
2331 ,x_msg_data => x_msg_data
2332 ,p_svcv_rec => l_svcv_rec
2333 );
2334
2335 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
2336 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2337 ELSIF x_return_status = G_RET_STS_ERROR THEN
2338 RAISE OKL_API.G_EXCEPTION_ERROR;
2339 END IF;
2340
2341 x_return_status := G_RET_STS_SUCCESS;
2342
2343 EXCEPTION
2344
2345 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2346
2347 IF p_transaction_control = G_TRUE THEN
2348 ROLLBACK TO l_program_name;
2349 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
2350 END IF;
2351
2352 x_return_status := G_RET_STS_ERROR;
2353
2354 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2355
2356 IF p_transaction_control = G_TRUE THEN
2357 ROLLBACK TO l_program_name;
2358 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
2359 END IF;
2360
2361 x_return_status := G_RET_STS_UNEXP_ERROR;
2362
2363 WHEN OTHERS THEN
2364
2365 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
2366 p_msg_name => G_DB_ERROR,
2367 p_token1 => G_PROG_NAME_TOKEN,
2368 p_token1_value => l_api_name,
2369 p_token2 => G_SQLCODE_TOKEN,
2370 p_token2_value => sqlcode,
2371 p_token3 => G_SQLERRM_TOKEN,
2372 p_token3_value => sqlerrm);
2373
2374 IF p_transaction_control = G_TRUE THEN
2375 ROLLBACK TO l_program_name;
2376 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
2377 END IF;
2378
2379 x_return_status := G_RET_STS_UNEXP_ERROR;
2380
2381 END delete_service;
2382
2383 END OKL_LEASE_QUOTE_SERVICE_PVT;