[Home] [Help]
PACKAGE BODY: APPS.OKS_MISC_UTIL_WEB
Source
1 PACKAGE BODY OKS_MISC_UTIL_WEB AS
2 /* $Header: OKSJMUTB.pls 120.30 2006/08/29 04:54:42 vjramali noship $ */
3 ----------------------------------------------------------------------------
4 l_api_version CONSTANT NUMBER := 1.0;
5 l_init_msg_list CONSTANT VARCHAR2(1) := 'F';
6
7
8
9 FUNCTION duration_unit(
10 p_start_date IN DATE,
11 p_end_date IN DATE
12 )
13 RETURN VARCHAR2
14 IS
15 l_duration NUMBER;
16 l_timeunit VARCHAR2(25);
17 l_return_status VARCHAR2(100);
18
19 BEGIN
20 OKC_TIME_UTIL_PUB.get_duration(
21 p_start_date,
22 p_end_date,
23 l_duration,
24 l_timeunit,
25 l_return_status
26 );
27
28
29 RETURN l_timeunit;
30 END duration_unit;
31 ---------------------------------------------------------------------
32
33 FUNCTION duration_period(
34 p_start_date IN DATE,
35 p_end_date IN DATE
36 )
37 RETURN NUMBER
38 IS
39 l_duration NUMBER;
40 l_timeunit VARCHAR2(25);
41 l_return_status VARCHAR2(100);
42
43 BEGIN
44 OKC_TIME_UTIL_PUB.get_duration(
45 p_start_date,
46 p_end_date,
47 l_duration,
48 l_timeunit,
49 l_return_status
50 );
51
52
53 RETURN l_duration;
54 END duration_period;
55 ------------------------------------------------------------------------------
56
57 FUNCTION adjusted_discount(
58 p_contract_id IN NUMBER,
59 p_line_id IN NUMBER
60 )
61 RETURN NUMBER
62 IS
63 l_api_version CONSTANT NUMBER := 1.0;
64 l_init_msg_list CONSTANT VARCHAR2(1) := 'F';
65 l_return_status VARCHAR2(100);
66 l_discount NUMBER;
67 l_line_id VARCHAR2(100);
68 l_msg_count NUMBER;
69 l_msg_data VARCHAR2(100);
70 l_modifiers_tbl OKS_QP_INT_PVT.price_modifiers_tbl;
71
72 BEGIN
73 l_line_id := to_char(p_line_id);
74
75 OKS_QP_INT_PVT.GET_MODIFIER_DETAILS(
76 l_api_version,
77 l_init_msg_list,
78 p_contract_id,
79 l_line_id,
80 l_modifiers_tbl,
81 l_return_status,
82 l_msg_count,
83 l_msg_data);
84 IF l_modifiers_tbl.count > 0 THEN
85 l_discount := l_modifiers_tbl(1).discount;
86 END IF;
87
88 RETURN l_discount;
89 END adjusted_discount;
90 ------------------------------------------------------------------------------
91
92 FUNCTION adjusted_surcharge(
93 p_contract_id IN NUMBER,
94 p_line_id IN NUMBER
95 )
96 RETURN NUMBER
97 IS
98 l_api_version CONSTANT NUMBER := 1.0;
99 l_init_msg_list CONSTANT VARCHAR2(1) := 'F';
100 l_return_status VARCHAR2(100);
101 l_surcharge NUMBER;
102 l_line_id VARCHAR2(100);
103 l_msg_count NUMBER;
104 l_msg_data VARCHAR2(100);
105 l_modifiers_tbl OKS_QP_INT_PVT.price_modifiers_tbl;
106
107 BEGIN
108 l_line_id := to_char(p_line_id);
109
110 OKS_QP_INT_PVT.GET_MODIFIER_DETAILS(
111 l_api_version,
112 l_init_msg_list,
113 p_contract_id,
114 l_line_id,
115 l_modifiers_tbl,
116 l_return_status,
117 l_msg_count,
118 l_msg_data);
119 IF l_modifiers_tbl.count > 0 THEN
120 l_surcharge := l_modifiers_tbl(1).surcharge;
121 END IF;
122
123 RETURN l_surcharge;
124 END adjusted_surcharge;
125 ------------------------------------------------------------------------------
126
127 FUNCTION adjusted_total(
128 p_contract_id IN NUMBER,
129 p_line_id IN NUMBER
130 )
131 RETURN NUMBER
132 IS
133 l_api_version CONSTANT NUMBER := 1.0;
134 l_init_msg_list CONSTANT VARCHAR2(1) := 'F';
135 l_return_status VARCHAR2(100);
136 l_total NUMBER;
137 l_line_id VARCHAR2(100);
138 l_msg_count NUMBER;
139 l_msg_data VARCHAR2(100);
140 l_modifiers_tbl OKS_QP_INT_PVT.price_modifiers_tbl;
141
142 BEGIN
143 l_line_id := to_char(p_line_id);
144
145 OKS_QP_INT_PVT.GET_MODIFIER_DETAILS(
146 l_api_version,
147 l_init_msg_list,
148 p_contract_id,
149 l_line_id,
150 l_modifiers_tbl,
151 l_return_status,
152 l_msg_count,
153 l_msg_data);
154 IF l_modifiers_tbl.count > 0 THEN
155 l_total := l_modifiers_tbl(1).total;
156 END IF;
157
158 RETURN l_total;
159 END adjusted_total;
160 ------------------------------------------------------------------------------
161
162 FUNCTION get_terminated_amount(
163 p_level IN VARCHAR2,
164 p_id IN NUMBER
165 ) RETURN NUMBER IS
166 l_unbilled NUMBER;
167 l_credited NUMBER;
168 l_suppressed NUMBER;
169 l_overridden NUMBER;
170 l_billed NUMBER;
171 l_return_status VARCHAR2(20);
172
173 BEGIN
174 oks_bill_rec_pub.get_termination_details( p_level => p_level,
175 p_id => p_id,
176 x_unbilled => l_unbilled,
177 x_credited => l_credited,
178 x_suppressed => l_suppressed,
179 x_overridden => l_overridden,
180 x_billed => l_billed,
181 x_return_status => l_return_status
182 );
183
184 return (l_unbilled + l_credited + l_suppressed);
185 END get_terminated_amount;
186
187 --------------------------------------------------------------------------------
188 FUNCTION get_adjustment_amount(p_chr_id IN NUMBER DEFAULT NULL,
189 p_cle_id IN NUMBER DEFAULT NULL
190 ) RETURN NUMBER IS
191 l_msg_count NUMBER;
192 l_msg_data VARCHAR2(2000);
193 l_return_status VARCHAR2(20);
194 l_amount NUMBER;
195 l_modifiers_tbl oks_qp_int_pvt.price_modifiers_tbl;
196 BEGIN
197 l_amount := 0;
198 oks_qp_int_pvt.get_modifier_details(p_api_version => l_api_version,
199 p_init_msg_list => l_init_msg_list,
200 p_chr_id => p_chr_id,
201 p_cle_id => p_cle_id,
202 x_modifiers_tbl => l_modifiers_tbl,
203 x_return_status => l_return_status,
204 x_msg_count => l_msg_count,
205 x_msg_data => l_msg_data );
206
207 IF (l_modifiers_tbl.count > 0 ) THEN
208 l_amount := l_modifiers_tbl(l_modifiers_tbl.first).total;
209 RETURN l_amount;
210 ELSE
211 RETURN l_amount;
212 END IF;
213
214
215 END get_adjustment_amount;
216
217
218 FUNCTION get_total_amount
219 (
220 p_chr_id IN NUMBER
221 ) RETURN NUMBER AS
222
223 l_api_name CONSTANT VARCHAR2(30) := 'get_total_amount';
224 l_subtotal_amount NUMBER;
225 l_tax_amount NUMBER;
226 l_total_amount NUMBER := 0;
227
228
229 CURSOR c_subtotal_amount IS
230 SELECT nvl(sum(lines.price_negotiated),0)
231 FROM okc_k_lines_b lines
232 WHERE lines.dnz_chr_id = p_chr_id
233 AND lines.cle_id IS NULL;
234
235 CURSOR c_tax_amount IS -- Bug 5490811
236 SELECT nvl(shdr.tax_amount,0) AS tax_amount
237 FROM oks_k_headers_b shdr
238 WHERE shdr.chr_id = p_chr_id;
239
240 BEGIN
241 -- start debug log
242 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
243 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
244 G_MODULE||l_api_name,
245 '100: Entered '||G_PKG_NAME ||'.'||l_api_name);
246 END IF;
247
248 OPEN c_subtotal_amount;
249 FETCH c_subtotal_amount INTO l_subtotal_amount;
250 CLOSE c_subtotal_amount;
251
252 OPEN c_tax_amount;
253 FETCH c_tax_amount INTO l_tax_amount;
254 CLOSE c_tax_amount;
255
256 -- end debug log
257 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
258 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
259 G_MODULE||l_api_name,
260 '1000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
261 END IF;
262
263 l_total_amount := l_subtotal_amount + l_tax_amount;
264
265 RETURN l_total_amount;
266
267
268 EXCEPTION
269 WHEN OTHERS THEN
270 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
271 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
272 G_MODULE||l_api_name,
273 '4000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
274 END IF;
275
276 RETURN l_total_amount;
277
278 END get_total_amount;
279
280
281 /**
282 * Addded function to retrieve Line Billed Amount for HTML Inquiry Line Billing Details Page
283 * @param p_line_id Line or Sub Line Id
284 * @param p_line_level line level character "L" for Line, "SL" for Sub Line
285 * @return total billed amount
286 */
287 FUNCTION get_line_billed_amount
288 (p_line_id IN NUMBER,
289 p_line_level IN VARCHAR2
290 ) RETURN NUMBER AS
291
292 CURSOR line_billed_amount_curr (l_line_id IN NUMBER) IS
293 SELECT
294 NVL(SUM(btl.trx_amount), 0) + NVL(SUM(btl.trx_line_tax_amount), 0) trx_amount
295 FROM
296 oks_bill_transactions btr
297 , oks_bill_txn_lines btl
298 , oks_bill_cont_lines bcl
299 WHERE
300 bcl.cle_id = l_line_id
301 and btr.ID = bcl.BTN_ID
302 and btl.btn_id = btr.id
303 and btl.BCL_ID = bcl.id
304 AND bcl.bill_action = 'RI'
305 and (btl.trx_number <> -99 OR btr.trx_number <> -99)
306 GROUP BY bcl.cle_id
307
308 UNION
309
310 SELECT
311 (nvl (sum (decode(raTrxLineSelect.r, 1, lineamt, 0)), 0) + nvl (sum (taxamt), 0)) trx_amount
312 FROM
313 (
314 select uniqueOrderLineSelect.id,
315 ra.trx_number,
316 ra.trx_date,
317 ratax.extended_amount taxamt,
318 ral.extended_amount lineamt,
319 ral.customer_trx_id,
320 uniqueOrderLineSelect.bill_action,
321 uniqueOrderLineSelect.bill_from_date,
322 uniqueOrderLineSelect.bill_to_date,
323 rank() over (partition by ral.customer_trx_line_id order by ratax.customer_trx_line_id) r
324 from (
325 select /*+ no_merge */ distinct rel.object1_id1, subline.cle_id id,
326 bcl.bill_action,
327 bcl.date_billed_from bill_from_date,
328 bcl.date_billed_to bill_to_date
329 from okc_k_rel_objs rel,
330 okc_k_lines_b subline,
331 oks_bill_cont_lines bcl
332 where subline.cle_id = l_line_id
333 and subline.lse_id in (9,18,25)
334 and subline.cle_id = bcl.cle_id
335 AND bcl.btn_id = - 44
336 AND bcl.bill_action = 'RI'
337 and subline.id = rel.cle_id
338 and rel.jtot_object1_code = 'OKX_ORDERLINE') uniqueOrderLineSelect,
339
340 oe_order_lines_all oel,
341 oe_order_headers_all oe,
342 ra_customer_trx_lines_all ral,
343 ra_customer_trx_all ra,
344 ra_customer_trx_lines_all ratax
345 where
346 uniqueOrderLineSelect.object1_id1 = oel.line_id
347 and oel.header_id = oe.header_id
348 and to_char (oe.order_number) = ral.sales_order
349 and to_char (oel.line_id) = ral.interface_line_attribute6
350 and ral.customer_trx_id = ra.customer_trx_id
351 and ratax.link_to_cust_trx_line_id (+) = ral.customer_trx_line_id
352 and ratax.line_type (+) = 'TAX') raTrxLineSelect
353 GROUP BY raTrxLineSelect.id;
354
355 CURSOR sub_line_billed_amount_curr (l_line_id IN NUMBER) IS
356 SELECT
357 NVL(SUM(btl.trx_amount), 0) + NVL(SUM(btl.trx_line_tax_amount), 0) trx_amount
358 FROM
359 oks_bill_txn_lines btl
360 , oks_bill_cont_lines bcl
361 , oks_bill_sub_lines bsl
362 , oks_bill_transactions btr
363 WHERE
364 bsl.cle_id = l_line_id
365 AND btl.BSL_ID = bsl.id
366 AND btl.btn_id = btr.id
367 AND bcl.id = bsl.bcl_id
368 AND bcl.BTN_ID = btr.ID
369 AND bcl.bill_action = 'RI'
370 AND (btl.trx_number <> -99 OR btr.trx_number <> -99)
371 GROUP BY bsl.cle_id
372
373 UNION
374
375 SELECT (nvl (sum (decode(raTrxLineSelect.r, 1, lineamt, 0)), 0) + nvl (sum (taxamt), 0)) trx_amount
376 FROM
377 (
378 select subline.cle_id id,
379 ra.trx_number,
380 ra.trx_date,
381 ratax.extended_amount taxamt,
382 ral.extended_amount lineamt,
383 ral.customer_trx_id,
384 rank() over (partition by ral.customer_trx_line_id order by ratax.customer_trx_line_id) r
385 from
386 okc_k_lines_b subline,
387 okc_k_rel_objs rel,
388 oe_order_lines_all oel,
389 oe_order_headers_all oe,
390 ra_customer_trx_lines_all ral,
391 ra_customer_trx_all ra,
392 ra_customer_trx_lines_all ratax
393 where subline.id = l_line_id
394 and subline.lse_id in (9,18,25)
395 and subline.id = rel.cle_id
396 and rel.jtot_object1_code = 'OKX_ORDERLINE'
397 and rel.object1_id1 = oel.line_id
398 and oel.header_id = oe.header_id
399 and to_char (oe.order_number) = ral.sales_order
400 and to_char (oel.line_id) = ral.interface_line_attribute6
401 and ral.customer_trx_id = ra.customer_trx_id
402 and ratax.link_to_cust_trx_line_id (+) = ral.customer_trx_line_id
403 and ratax.line_type (+) = 'TAX') raTrxLineSelect,
404
405 oks_bill_cont_lines bcl
406 WHERE raTrxLineSelect.id = bcl.cle_id
407 AND bcl.btn_id = - 44
408 AND bcl.bill_action = 'RI'
409 GROUP BY raTrxLineSelect.id;
410
411 l_api_name CONSTANT VARCHAR2(30) :='get_line_billed_amount';
412 l_amount NUMBER;
413
414 BEGIN
415
416 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
417 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'100: Inside get_line_billed_amount');
418 END IF;
419
420 IF p_line_level = 'L' THEN
421 OPEN line_billed_amount_curr(p_line_id);
422 FETCH line_billed_amount_curr INTO l_amount;
423 CLOSE line_billed_amount_curr;
424 END IF;
425
426 IF p_line_level = 'SL' THEN
427 OPEN sub_line_billed_amount_curr(p_line_id);
428 FETCH sub_line_billed_amount_curr INTO l_amount;
429 CLOSE sub_line_billed_amount_curr;
430 END IF;
431
432
433 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
434 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'101: get_line_billed_amount');
435 END IF;
436
437 return l_amount;
438
439 EXCEPTION
440 WHEN OTHERS THEN
441 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
442 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED ,g_module||l_api_name,'1000: Leaving get_line_billed_amount because of EXCEPTION: '||sqlerrm);
443 END IF;
444 IF line_billed_amount_curr %ISOPEN THEN
445 CLOSE line_billed_amount_curr ;
446 END IF;
447
448 IF sub_line_billed_amount_curr %ISOPEN THEN
449 CLOSE sub_line_billed_amount_curr ;
450 END IF;
451
452 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
453 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
454 END IF;
455
456 return null;
457
458 END get_line_billed_amount;
459
460 /**
461 * Addded function to retrieve Line Billed Amount for HTML Inquiry Line Billing Details Page
462 * @param p_line_id Line or Sub Line Id
463 * @param p_line_level line level character "L" for Line, "SL" for Sub Line
464 * @return pending invoice amount
465 */
466 FUNCTION get_line_unbilled_amount
467 (p_line_id IN NUMBER,
468 p_line_level IN VARCHAR2
469 ) RETURN NUMBER AS
470
471 CURSOR line_unbilled_amount_curr (l_line_id IN NUMBER) IS
472 SELECT
473 (
474 NVL(KLINES.price_negotiated, 0)
475 +
476 NVL(KSLINES.credit_amount, 0)
477 +
478 NVL(KSLINES.suppressed_credit, 0)
479 )
480
481 -
482 (NVL(OKS_BILLED_LINES.BILLED_AMOUNT,0) + NVL(OM_ORIGINATED_BILLED_LINES.BILLED_AMOUNT,0)) unbilled_amount
483
484 FROM
485 OKC_K_LINES_B KLINES
486 ,OKS_K_LINES_B KSLINES
487
488 ,
489 (
490 SELECT
491 NVL(SUM(btl.trx_amount), 0) BILLED_AMOUNT
492 FROM
493 oks_bill_transactions btr
494 ,oks_bill_txn_lines btl
495 ,OKS_BILL_CONT_LINES BCL
496
497 WHERE
498 BCL.cle_id = l_line_id
499 AND BCL.bill_action = 'RI'
500 AND btr.ID = bcl.BTN_ID
501 AND btl.btn_id = btr.id
502 AND btl.BCL_ID = bcl.id
503 AND (btl.trx_number <> -99 OR btr.trx_number <> -99)
504 ) OKS_BILLED_LINES
505
506 ,
507 (
508 select NVL(SUM(ral.extended_amount), 0) BILLED_AMOUNT
509 from (
510 select /*+ no_merge */ distinct rel.object1_id1
511 from okc_k_rel_objs rel,
512 okc_k_lines_b subline,
513 oks_bill_cont_lines bcl
514 where subline.cle_id = l_line_id
515 and subline.lse_id in (9,18,25)
516 and subline.cle_id = bcl.cle_id
517 AND bcl.btn_id = - 44
518 AND bcl.bill_action = 'RI'
519 and subline.id = rel.cle_id
520 and rel.jtot_object1_code = 'OKX_ORDERLINE') uniqueOrderLineSelect,
521
522 oe_order_lines_all oel,
523 oe_order_headers_all oe,
524 ra_customer_trx_lines_all ral
525 where
526 uniqueOrderLineSelect.object1_id1 = oel.line_id
527 and oel.header_id = oe.header_id
528 and to_char (oe.order_number) = ral.sales_order
529 and to_char (oel.line_id) = ral.interface_line_attribute6
530 ) OM_ORIGINATED_BILLED_LINES
531
532 WHERE
533 KLINES.id = l_line_id
534 AND KSLINES.cle_id = KLINES.id
535 AND (KSLINES.usage_type IS NULL OR KSLINES.usage_type = 'NPR');
536
537
538
539 CURSOR sub_line_unbilled_amount_curr (l_line_id IN NUMBER) IS
540 SELECT
541 (
542 NVL(KLINES.price_negotiated, 0)
543 +
544 NVL(KSLINES.credit_amount, 0)
545 +
546 NVL(KSLINES.suppressed_credit, 0)
547 )
548 -
549 (NVL(OKS_BILLED_LINES.BILLED_AMOUNT,0) + NVL(OM_ORIGINATED_BILLED_LINES.BILLED_AMOUNT,0)) unbilled_amount
550
551 FROM
552 OKC_K_LINES_B KLINES
553 ,OKS_K_LINES_B KSLINES
554 ,
555 (
556 SELECT
557 NVL(SUM(btl.trx_amount), 0) BILLED_AMOUNT
558 FROM
559 OKS_BILL_SUB_LINES BSL
560 ,oks_bill_txn_lines btl
561 ,oks_bill_transactions btr
562 ,OKS_BILL_CONT_LINES BCL
563 WHERE
564 BSL.cle_id = l_line_id
565 AND BCL.id = BSL.bcl_id
566 AND BCL.bill_action = 'RI'
567 AND btl.BSL_ID = bsl.id
568 AND btl.btn_id = btr.id
569 AND bcl.BTN_ID = btr.ID
570 AND (btl.trx_number <> -99 OR btr.trx_number <> -99) ) OKS_BILLED_LINES
571 ,
572
573 (
574 SELECT
575 NVL(SUM(ral.extended_amount), 0) BILLED_AMOUNT
576 FROM
577 ra_customer_trx_lines_all ral
578 ,oks_bill_cont_lines bcl
579 ,oe_order_headers_all oe
580 ,oe_order_lines_all oel
581 ,okc_k_rel_objs rel
582 ,okc_k_lines_b subline
583 WHERE
584 subline.id = l_line_id
585 AND subline.lse_id in (9,18,25)
586 AND rel.cle_id = subline.id
587 AND rel.jtot_object1_code = 'OKX_ORDERLINE'
588 AND oel.line_id = rel.object1_id1
589 AND oe.header_id = oel.header_id
590 AND ral.sales_order = TO_CHAR(oe.order_number)
591 AND ral.interface_line_attribute1 = TO_CHAR(oe.order_number)
592 AND ral.interface_line_attribute6 = TO_CHAR(oel.line_id)
593 AND bcl.cle_id = subline.cle_id
594 AND bcl.btn_id = -44
595 AND bcl.bill_action = 'RI'
596 ) OM_ORIGINATED_BILLED_LINES
597
598 WHERE
599 KLINES.id = l_line_id
600 AND KSLINES.cle_id = KLINES.id;
601
602
603 l_api_name CONSTANT VARCHAR2(30) :='get_line_unbilled_amount';
604
605 l_unbilled_amount NUMBER;
606
607 BEGIN
608
609 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
610 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'100: Inside get_line_unbilled_amount');
611 END IF;
612
613
614 IF p_line_level = 'L' THEN
615 OPEN line_unbilled_amount_curr(p_line_id);
616 FETCH line_unbilled_amount_curr INTO l_unbilled_amount;
617 CLOSE line_unbilled_amount_curr;
618 END IF;
619
620 IF p_line_level = 'SL' THEN
621 OPEN sub_line_unbilled_amount_curr(p_line_id);
622 FETCH sub_line_unbilled_amount_curr INTO l_unbilled_amount;
623 CLOSE sub_line_unbilled_amount_curr;
624 END IF;
625
626
627 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
628 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'101: get_line_unbilled_amount');
629 END IF;
630
631 return l_unbilled_amount;
632
633 EXCEPTION
634 WHEN OTHERS THEN
635 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
636 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED ,g_module||l_api_name,'1000: Leaving get_line_unbilled_amount because of EXCEPTION: '||sqlerrm);
637 END IF;
638
639 IF line_unbilled_amount_curr %ISOPEN THEN
640 CLOSE line_unbilled_amount_curr ;
641 END IF;
642
643 IF sub_line_unbilled_amount_curr %ISOPEN THEN
644 CLOSE sub_line_unbilled_amount_curr ;
645 END IF;
646
647 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
648 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
649 END IF;
650
651 return null;
652
653 END get_line_unbilled_amount;
654
655
656
657 -- Function to retrieve unbilled amount for a contract
658 -- Parameters: contract id
659
660 FUNCTION get_header_unbilled_amount (p_chr_id IN NUMBER )
661 RETURN NUMBER AS
662
663 CURSOR C_HEADER_UNBILLED_AMOUNT (P_CHR_ID IN NUMBER) IS
664 SELECT (
665 (SELECT SUM(
666 NVL(KLINES.PRICE_NEGOTIATED, 0) +
667 NVL(KSLINES.CREDIT_AMOUNT, 0) +
668 NVL(KSLINES.SUPPRESSED_CREDIT, 0)
669 )
670 FROM
671 OKC_K_LINES_B KLINES
672 ,OKS_K_LINES_B KSLINES
673 WHERE
674 KLINES.CHR_ID = P_CHR_ID
675 AND KLINES.ID = KSLINES.CLE_ID
676 AND (KSLINES.USAGE_TYPE IS NULL OR KSLINES.USAGE_TYPE = 'NPR')
677 )
678 -
679 (
680 ( SELECT
681 NVL(SUM(BTL.TRX_AMOUNT),0) BILLED_AMOUNT
682 FROM
683 OKC_K_LINES_B LINE
684 ,OKS_K_LINES_B KSLINES
685 ,OKS_BILL_TRANSACTIONS BTR
686 ,OKS_BILL_TXN_LINES BTL
687 ,OKS_BILL_CONT_LINES BCL
688 WHERE
689 LINE.CHR_ID = P_CHR_ID
690 AND LINE.ID = KSLINES.CLE_ID
691 AND (KSLINES.USAGE_TYPE IS NULL OR KSLINES.USAGE_TYPE = 'NPR') -- Bug 5484219 Filter out non NPR lines
692 AND LINE.ID = BCL.CLE_ID
693 AND BCL.bill_action = 'RI'
694 AND BTR.ID = BCL.BTN_ID
695 AND BTL.BTN_ID = BTR.ID
696 AND BTL.BCL_ID = BCL.ID
697 AND (BTL.TRX_NUMBER <> -99 OR BTR.TRX_NUMBER <> -99)
698 )
699 +
700 (
701 SELECT
702 NVL(SUM(RAL.EXTENDED_AMOUNT), 0) BILLED_AMOUNT
703 FROM (
704 SELECT /*+ NO_MERGE */ DISTINCT REL.OBJECT1_ID1, SUBLINE.CHR_ID
705 FROM OKC_K_REL_OBJS REL,
706 OKC_K_LINES_B SUBLINE,
707 OKS_BILL_CONT_LINES BCL
708 WHERE
709 SUBLINE.DNZ_CHR_ID = P_CHR_ID
710 AND SUBLINE.LSE_ID IN (9,18,25)
711 AND SUBLINE.CLE_ID = BCL.CLE_ID
712 AND BCL.BTN_ID = - 44
713 AND BCL.BILL_ACTION = 'RI'
714 AND SUBLINE.ID = REL.CLE_ID
715 AND REL.JTOT_OBJECT1_CODE = 'OKX_ORDERLINE') UNIQUEORDERLINESELECT,
716 OE_ORDER_LINES_ALL OEL,
717 OE_ORDER_HEADERS_ALL OE,
718 RA_CUSTOMER_TRX_LINES_ALL RAL
719 WHERE
720 UNIQUEORDERLINESELECT.OBJECT1_ID1 = OEL.LINE_ID
721 AND OEL.HEADER_ID = OE.HEADER_ID
722 AND TO_CHAR (OE.ORDER_NUMBER) = RAL.SALES_ORDER
723 AND TO_CHAR (OEL.LINE_ID) = RAL.INTERFACE_LINE_ATTRIBUTE6
724 )
725 )
726 ) UNBILLED_AMOUNT FROM DUAL;
727
728 l_api_name CONSTANT VARCHAR2(30) :='get_header_unbilled_amount';
729 l_unbilled_amount NUMBER := 0;
730
731 BEGIN
732
733 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
734 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'100: Inside get_header_unbilled_amount');
735 END IF;
736
737 OPEN c_header_unbilled_amount(p_chr_id);
738 FETCH c_header_unbilled_amount INTO l_unbilled_amount;
739 CLOSE c_header_unbilled_amount;
740
741 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
742 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'101: get_header_unbilled_amount');
743 END IF;
744
745 return l_unbilled_amount;
746
747 EXCEPTION
748 WHEN OTHERS THEN
749 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
750 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED ,g_module||l_api_name,'1000: Leaving due EXCEPTION: '||sqlerrm);
751 END IF;
752 IF c_header_unbilled_amount %ISOPEN THEN
753 CLOSE c_header_unbilled_amount ;
754 END IF;
755
756 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
757 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
758 END IF;
759
760 return null;
761
762 END get_header_unbilled_amount;
763
764
765
766 -- Function to retrieve billed amount for a contract
767 -- Parameters: p_chr_id (Identifier of the contract)
768 -- Returns the billed amount for a contract
769
770
771 FUNCTION get_header_billed_amount (p_chr_id IN NUMBER)
772 RETURN NUMBER AS
773
774 CURSOR C_HEADER_BILLED_AMOUNT (P_CHR_ID IN NUMBER) IS
775 SELECT
776 NVL(SUM(BTL.TRX_AMOUNT), 0) + NVL(SUM(BTL.TRX_LINE_TAX_AMOUNT), 0) TRX_AMOUNT
777 FROM
778 OKC_K_LINES_B LINE
779 ,OKS_BILL_TRANSACTIONS BTR
780 ,OKS_BILL_TXN_LINES BTL
781 ,OKS_BILL_CONT_LINES BCL
782 WHERE
783 LINE.CHR_ID = P_CHR_ID
784 AND LINE.ID = BCL.CLE_ID
785 AND BTR.ID = BCL.BTN_ID
786 AND BTL.BTN_ID = BTR.ID
787 AND BTL.BCL_ID = BCL.ID
788 AND BCL.bill_action = 'RI'
789 AND (BTL.TRX_NUMBER <> -99 OR BTR.TRX_NUMBER <> -99)
790 GROUP BY LINE.CHR_ID
791
792 UNION
793
794 SELECT
795 (NVL (SUM (DECODE(RATRXLINESELECT.R, 1, LINEAMT, 0)), 0) + NVL (SUM (TAXAMT), 0)) TRX_AMOUNT
796 FROM
797 (
798 SELECT UNIQUEORDERLINESELECT.ID,
799 UNIQUEORDERLINESELECT.CHR_ID,
800 RA.TRX_NUMBER,
801 RA.TRX_DATE,
802 RATAX.EXTENDED_AMOUNT TAXAMT,
803 RAL.EXTENDED_AMOUNT LINEAMT,
804 RAL.CUSTOMER_TRX_ID,
805 UNIQUEORDERLINESELECT.BILL_ACTION,
806 UNIQUEORDERLINESELECT.BILL_FROM_DATE,
807 UNIQUEORDERLINESELECT.BILL_TO_DATE,
808 RANK() OVER (PARTITION BY RAL.CUSTOMER_TRX_LINE_ID ORDER BY RATAX.CUSTOMER_TRX_LINE_ID) R
809 FROM (
810 SELECT /*+ NO_MERGE */ DISTINCT REL.OBJECT1_ID1,
811 SUBLINE.CLE_ID ID,
812 SUBLINE.DNZ_CHR_ID CHR_ID,
813 BCL.BILL_ACTION,
814 BCL.DATE_BILLED_FROM BILL_FROM_DATE,
815 BCL.DATE_BILLED_TO BILL_TO_DATE
816 FROM OKC_K_REL_OBJS REL,
817 OKC_K_LINES_B SUBLINE,
818 OKS_BILL_CONT_LINES BCL
819 WHERE
820 SUBLINE.DNZ_CHR_ID = P_CHR_ID
821 AND SUBLINE.LSE_ID IN (9,18,25)
822 AND SUBLINE.CLE_ID = BCL.CLE_ID
823 AND BCL.BTN_ID = - 44
824 AND BCL.BILL_ACTION = 'RI'
825 AND SUBLINE.ID = REL.CLE_ID
826 AND REL.JTOT_OBJECT1_CODE = 'OKX_ORDERLINE') UNIQUEORDERLINESELECT,
827
828 OE_ORDER_LINES_ALL OEL,
829 OE_ORDER_HEADERS_ALL OE,
830 RA_CUSTOMER_TRX_LINES_ALL RAL,
831 RA_CUSTOMER_TRX_ALL RA,
832 RA_CUSTOMER_TRX_LINES_ALL RATAX
833 WHERE
834 UNIQUEORDERLINESELECT.OBJECT1_ID1 = OEL.LINE_ID
835 AND OEL.HEADER_ID = OE.HEADER_ID
836 AND TO_CHAR (OE.ORDER_NUMBER) = RAL.SALES_ORDER
837 AND TO_CHAR (OEL.LINE_ID) = RAL.INTERFACE_LINE_ATTRIBUTE6
838 AND RAL.CUSTOMER_TRX_ID = RA.CUSTOMER_TRX_ID
839 AND RATAX.LINK_TO_CUST_TRX_LINE_ID (+) = RAL.CUSTOMER_TRX_LINE_ID
840 AND RATAX.LINE_TYPE (+) = 'TAX') RATRXLINESELECT
841 GROUP BY RATRXLINESELECT.CHR_ID;
842
843 l_api_name CONSTANT VARCHAR2(30) :='get_header_billed_amount';
844 l_billed_amount NUMBER := 0;
845
846 BEGIN
847
848 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
849 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'100: Inside get_header_billed_amount');
850 END IF;
851
852 OPEN c_header_billed_amount(p_chr_id);
853 FETCH c_header_billed_amount INTO l_billed_amount;
854 CLOSE c_header_billed_amount;
855
856 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
857 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'101: get_header_billed_amount');
858 END IF;
859
860 return l_billed_amount;
861
862 EXCEPTION
863 WHEN OTHERS THEN
864 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
865 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED ,g_module||l_api_name,'1000: Leaving due EXCEPTION: '||sqlerrm);
866 END IF;
867 IF c_header_billed_amount %ISOPEN THEN
868 CLOSE c_header_billed_amount ;
869 END IF;
870
871 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
872 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
873 END IF;
874
875 return null;
876 END get_header_billed_amount;
877
878
879
880 -- Function to get Duration and Period for a given Strat Date and End Date ( ex: 1 Year)
881 FUNCTION get_duration_period (p_start_date DATE,
882 p_end_date DATE)
883 RETURN VARCHAR2 AS
884
885 l_api_name CONSTANT VARCHAR2(30) :='get_duration_period';
886 l_period_meaning VARCHAR2(25);
887
888 CURSOR get_period_maening (p_unit_of_measure VARCHAR2) IS
889 SELECT unit_of_measure_tl
890 FROM mtl_units_of_measure_tl
891 WHERE uom_code = p_unit_of_measure
892 AND language = userenv('LANG');
893
894 BEGIN
895
896 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
897 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'Inside get_duration_period');
898 END IF;
899
900 OPEN get_period_maening(duration_unit(p_start_date,p_end_date));
901 FETCH get_period_maening INTO l_period_meaning;
902 CLOSE get_period_maening;
903
904 return duration_period(p_start_date,p_end_date) || ' ' || l_period_meaning ;
905
906 EXCEPTION
907 WHEN OTHERS THEN
908 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
909 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED ,g_module||l_api_name,'EXCEPTION: '||sqlerrm);
910 END IF;
911 END get_duration_period;
912
913 -- Function to get the covered level name (Service).
914 FUNCTION get_covlvl_name(p_jtot_object1_code IN VARCHAR2,
915 p_object1_id1 IN VARCHAR2,
916 p_object1_id2 IN VARCHAR2)
917 RETURN VARCHAR2 IS
918
919 l_name VARCHAR2(2000);
920 l_chr_id NUMBER;
921 l_api_name CONSTANT VARCHAR2(30) := 'get_covlvl_name';
922
923 CURSOR get_prod_name_csr IS
924 SELECT decode(fnd_profile.value('OKS_ITEM_DISPLAY_PREFERENCE'), 'DISPLAY_DESC'
925 , mtl.description,mtl.concatenated_segments)
926 FROM mtl_system_items_kfv mtl
927 ,okc_k_items itm
928 ,csi_item_instances csi
929 WHERE itm.object1_id1 = p_object1_id1
930 AND itm.jtot_object1_code = p_jtot_object1_code
931 AND csi.instance_id = itm.object1_id1
932 AND csi.inventory_item_id = mtl.inventory_item_id
933 AND mtl.organization_id = csi.inv_master_organization_id
934 AND rownum < 2;
935
936 CURSOR get_item_name_csr IS
937 SELECT decode(fnd_profile.value('OKS_ITEM_DISPLAY_PREFERENCE'), 'DISPLAY_DESC'
938 , mtl.description,mtl.concatenated_segments)
939 FROM mtl_system_items_kfv mtl
940 WHERE mtl.inventory_item_id = to_number(p_object1_id1)
941 AND mtl.organization_id = p_object1_id2;
942
943 CURSOR get_party_name_csr IS
944 SELECT party.party_name
945 FROM hz_parties party
946 WHERE party.party_id = p_object1_id1;
947
948 CURSOR get_system_name_csr IS
949 SELECT systl.name
950 FROM csi_systems_tl systl
951 WHERE systl.system_id = to_number(p_object1_id1)
952 AND systl.language = userenv('LANG');
953
954 CURSOR get_account_name_csr IS
955 SELECT decode (ca.account_name, null, p.party_name,ca.account_name)
956 FROM hz_cust_accounts ca
957 ,hz_parties p
958 WHERE ca.cust_account_id = to_number(p_object1_id1)
959 AND p.party_id = ca.party_id;
960
961 CURSOR get_site_name_csr IS
962 SELECT DECODE(site.party_site_name,NULL,site.party_site_number
963 ,site.party_site_number || '-' ||
964 site.party_site_name ) NAME
965 FROM hz_party_sites site
966 WHERE site.party_site_id = to_number(p_object1_id1);
967
968 BEGIN
969
970 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
971 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'Inside get_covdlvl_name ' ||
972 'p_jtot_object1_code is : ' || p_jtot_object1_code ||
973 'p_object1_id1 is : ' || p_object1_id1 ||
974 'p_object1_id2 is : ' || p_object1_id2);
975 END IF;
976
977 -- Get Covered Item Name
978 IF ( p_jtot_object1_code = 'OKX_COVITEM' ) THEN
979 OPEN get_item_name_csr;
980 FETCH get_item_name_csr INTO l_name;
981 CLOSE get_item_name_csr;
982 -- Get Covered Product Name
983 ELSIF ( p_jtot_object1_code = 'OKX_CUSTPROD' ) THEN
984 OPEN get_prod_name_csr;
985 FETCH get_prod_name_csr INTO l_name;
986 CLOSE get_prod_name_csr;
987 -- Get Covered Party Site Number - Site Name
988 ELSIF ( p_jtot_object1_code = 'OKX_PARTYSITE') THEN
989 OPEN get_site_name_csr;
990 FETCH get_site_name_csr INTO l_name;
991 CLOSE get_site_name_csr;
992 -- Get Covered Party Name
993 ELSIF ( p_jtot_object1_code = 'OKX_PARTY' ) THEN
994 OPEN get_party_name_csr;
995 FETCH get_party_name_csr INTO l_name;
996 CLOSE get_party_name_csr;
997 -- Get Covered Customer Name
998 ELSIF ( p_jtot_object1_code = 'OKX_CUSTACCT' ) THEN
999 OPEN get_account_name_csr;
1000 FETCH get_account_name_csr INTO l_name;
1001 CLOSE get_account_name_csr;
1002 -- Get Covered System Name
1003 ELSIF ( p_jtot_object1_code = 'OKX_COVSYST' ) THEN
1004 OPEN get_system_name_csr;
1005 FETCH get_system_name_csr INTO l_name;
1006 CLOSE get_system_name_csr;
1007 END IF;
1008
1009 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1010 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'Exit get_covdlvl_name ' ||
1011 'l_name is : ' || l_name);
1012 END IF;
1013
1014 RETURN (l_name);
1015
1016 EXCEPTION
1017 WHEN OTHERS THEN
1018 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1019 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED ,g_module||l_api_name,'EXCEPTION: '||sqlerrm);
1020 END IF;
1021 IF get_item_name_csr%ISOPEN THEN
1022 CLOSE get_item_name_csr ;
1023 ELSIF get_account_name_csr%ISOPEN THEN
1024 CLOSE get_account_name_csr;
1025 ELSIF get_site_name_csr%ISOPEN THEN
1026 CLOSE get_site_name_csr;
1027 ELSIF get_system_name_csr%ISOPEN THEN
1028 CLOSE get_system_name_csr;
1029 ELSIF get_party_name_csr%ISOPEN THEN
1030 CLOSE get_party_name_csr;
1031 ELSIF get_prod_name_csr%ISOPEN THEN
1032 CLOSE get_prod_name_csr;
1033 END IF;
1034 return null;
1035
1036 END get_covlvl_name;
1037
1038 FUNCTION get_name (p_line_id IN NUMBER,
1039 p_lse_id IN NUMBER
1040 )
1041 RETURN VARCHAR2 IS
1042
1043 l_name VARCHAR2(2000);
1044 l_jtot_object1_code VARCHAR2(30);
1045 l_object1_id1 VARCHAR2(40);
1046 l_object1_id2 VARCHAR2(200);
1047
1048 l_api_name CONSTANT VARCHAR2(30) := 'get_name';
1049
1050 CURSOR get_objec_rel_csr(p_line_id NUMBER) IS
1051 SELECT jtot_object1_code,
1052 object1_id1,
1053 object1_id2
1054 FROM OKC_K_ITEMS
1055 WHERE CLE_ID = p_line_id;
1056
1057 CURSOR get_name_csr (p_object1_id1 VARCHAR2) IS
1058 SELECT fnd_flex_server.get_kfv_concat_segs_by_rowid('COMPACT', 401, 'SERV', 101, sysitems.rowid)name
1059 FROM MTL_SYSTEM_ITEMS_B sysitems
1060 WHERE sysitems.inventory_item_id = to_number(p_object1_id1)
1061 AND rownum < 2;
1062 BEGIN
1063
1064 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1065 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'Inside get_covdlvl_name ' ||
1066 'p_line_id is : ' || p_line_id ||
1067 'p_lse_id is : ' || p_lse_id);
1068
1069 END IF;
1070
1071 OPEN get_objec_rel_csr(p_line_id);
1072 FETCH get_objec_rel_csr INTO l_jtot_object1_code,l_object1_id1,l_object1_id2;
1073 CLOSE get_objec_rel_csr;
1074
1075 IF p_lse_id IN (1,12,14,19,46) THEN
1076 OPEN get_name_csr(l_object1_id1);
1077 FETCH get_name_csr INTO l_name;
1078 CLOSE get_name_csr;
1079 ELSE
1080 l_name := get_covlvl_name (l_jtot_object1_code,l_object1_id1,l_object1_id2);
1081
1082 END IF;
1083
1084 RETURN (l_name);
1085
1086 EXCEPTION
1087 WHEN OTHERS THEN
1088 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1089 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED ,g_module||l_api_name,'EXCEPTION: '||sqlerrm);
1090 END IF;
1091
1092 IF get_objec_rel_csr%ISOPEN THEN
1093 CLOSE get_objec_rel_csr;
1094 END IF;
1095
1096 IF get_name_csr%ISOPEN THEN
1097 CLOSE get_name_csr ;
1098 END IF;
1099 return null;
1100 END get_name;
1101
1102 -- Function to get the commitment number
1103 -- Parameters: p_commitment_id: Commitment ID
1104 FUNCTION get_commiment_number(p_commitment_id NUMBER
1105 ,p_org_id NUMBER)
1106 RETURN NUMBER IS
1107
1108 l_api_name CONSTANT VARCHAR2(30) := 'get_commiment_number';
1109 l_commitment_number NUMBER;
1110
1111 CURSOR get_commitment_number_csr(p_commitment_id NUMBER,
1112 p_org_id NUMBER) IS
1113 SELECT trx_number
1114 FROM ra_customer_trx_all rah
1115 WHERE rah.customer_trx_id = p_commitment_id
1116 AND nvl(rah.org_id,-99) = p_org_id;
1117
1118 BEGIN
1119
1120 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1121 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'Inside GET_COMMITMENT_NUMBER ' ||
1122 'p_commitment_id is : ' || p_commitment_id ||
1123 ' p_org_id is: ' || p_org_id);
1124
1125 END IF;
1126
1127 OPEN get_commitment_number_csr(p_commitment_id,p_org_id);
1128 FETCH get_commitment_number_csr INTO l_commitment_number;
1129 CLOSE get_commitment_number_csr;
1130 return l_commitment_number;
1131
1132 EXCEPTION
1133 WHEN OTHERS THEN
1134 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1135 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED ,g_module||l_api_name,'EXCEPTION: ' || sqlerrm);
1136 END IF;
1137 IF get_commitment_number_csr %ISOPEN THEN
1138 CLOSE get_commitment_number_csr ;
1139 END IF;
1140
1141 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
1142 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
1143 END IF;
1144
1145 return null;
1146
1147 END get_commiment_number;
1148
1149 -- Function to validate whether the covered level is a standard item or a component.
1150 -- Parameter: p_line_id - Sub line ID.
1151 FUNCTION validate_component_yn(p_line_id IN NUMBER)
1152 RETURN VARCHAR2 AS
1153
1154 l_api_name CONSTANT VARCHAR2(30) := 'validate_component_yn';
1155 l_component_yn VARCHAR2(5);
1156
1157 CURSOR is_component_yn_cur(l_line_id IN NUMBER) IS
1158 SELECT
1159 (CASE WHEN oel1.inventory_item_id = csi.inventory_item_id THEN
1160 'N'
1161 ELSE
1162 'Y'
1163 END) isComponentFlag
1164 FROM okc_k_items itm
1165 ,csi_item_instances csi
1166 ,oe_order_lines_all oel
1167 ,oe_order_lines_all oel1
1168 ,(SELECT rel.object1_id1,
1169 rel.cle_id
1170 FROM okc_k_rel_objs rel
1171 WHERE rel.cle_id = l_line_id
1172 AND rel.jtot_object1_code = 'OKX_ORDERLINE' ) x
1173 WHERE itm.cle_id = x.cle_id
1174 AND itm.object1_id1 = csi.instance_id
1175 AND x.object1_id1 = oel.line_id
1176 AND oel.service_reference_line_id = oel1.line_id (+)
1177 AND oel.service_reference_type_code = 'ORDER';
1178
1179 BEGIN
1180
1181 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1182 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1183 G_MODULE||l_api_name,
1184 'Entered '||G_PKG_NAME ||'.'||l_api_name);
1185 END IF;
1186
1187 OPEN is_component_yn_cur(p_line_id);
1188 FETCH is_component_yn_cur INTO l_component_yn;
1189 IF is_component_yn_cur%NOTFOUND THEN
1190 l_component_yn := 'N';
1191 END IF;
1192 CLOSE is_component_yn_cur;
1193
1194 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1195 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1196 G_MODULE||l_api_name,
1197 'Leaving '||G_PKG_NAME ||'.'||l_api_name);
1198 END IF;
1199
1200 RETURN l_component_yn;
1201
1202 EXCEPTION
1203 WHEN OTHERS THEN
1204 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1205 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1206 G_MODULE||l_api_name,
1207 'Leaving '||G_PKG_NAME ||'.'||l_api_name);
1208 END IF;
1209 IF is_component_yn_cur%ISOPEN THEN
1210 CLOSE is_component_yn_cur ;
1211 END IF;
1212 l_component_yn := 'N';
1213 RETURN l_component_yn;
1214 END validate_component_yn;
1215
1216
1217 END OKS_MISC_UTIL_WEB;