[Home] [Help]
PACKAGE BODY: APPS.OKS_EXTWAR_UTIL_PVT
Source
1 PACKAGE BODY oks_extwar_util_pvt AS
2 /* $Header: OKSRUTLB.pls 120.19 2007/12/24 06:35:52 rriyer ship $ */
3 TYPE War_item_new_rec_type IS RECORD
4 (
5 War_item_id NUMBER,
6 EFFECTIVITY_DATE DATE,
7 DISABLE_DATE DATE
8 );
9
10 TYPE War_item_new_tbl_type IS TABLE OF War_item_new_rec_type INDEX BY BINARY_INTEGER;
11
12
13 TYPE war_item_rec_type IS RECORD (
14 war_item_id NUMBER
15 );
16
17 TYPE war_item_id_tbl_type IS TABLE OF war_item_rec_type
18 INDEX BY BINARY_INTEGER;
19
20 ------------------------------------
21 -- GET SALES REPNAME
22 ------------------------------------
23 /*
24 Function get_repname (p_party_id number, p_org_id number) Return Varchar2 Is
25
26 l_terrkren_rec jtf_territory_pub.jtf_kren_rec_type;
27 l_resource_type varchar2(100) := to_char(null);
28 l_role varchar2(100) := to_char(null);
29 l_return_status varchar2(1);
30 l_msg_count number;
31 l_msg_data varchar2(2000);
32 l_terrresource_tbl jtf_territory_pub.winningterrmember_tbl_type;
33 l_user_id fnd_user.user_id%TYPE;
34 l_index Number;
35 i Number;
36 l_org_id Number;
37 cursor l_sname_csr (p_resid number) is select name from jtf_rs_salesreps where resource_id = p_resid;
38 l_sname Varchar2(240);
39
40 Begin
41 okc_context.set_okc_org_context;
42 l_org_id := okc_context.get_okc_org_id;
43
44 MO_GLOBAL.set_org_context(p_org_id,Null);
45 FND_PROFILE.PUT ('ORG_ID',p_org_id);
46
47 l_terrkren_rec.PARTY_ID := p_party_id;
48
49 jtf_terr_oks_pub.get_winningterrmembers
50 (
51 p_api_version_number => 1.0,
52 p_terrkren_rec => l_terrkren_rec,
53 p_resource_type => l_resource_type,
54 p_role => l_role,
55 x_return_status => l_return_status, -- OUT NOCOPY
56 x_msg_count => l_msg_count, -- OUT NOCOPY
57 x_msg_data => l_msg_data, -- OUT NOCOPY
58 x_terrresource_tbl => l_terrresource_tbl
59 );
60
61 l_sname := Null;
62
63 If l_terrresource_tbl.count > 0 Then
64 open l_sname_csr (l_terrresource_tbl(l_terrresource_tbl.FIRST).resource_id);
65 Fetch l_sname_csr into l_sname;
66 close l_sname_csr;
67
68 End If;
69
70 MO_GLOBAL.set_org_context(l_org_id,Null);
71 FND_PROFILE.PUT ('ORG_ID',l_org_id);
72 Return l_sname;
73
74 Exception
75 When Others Then
76 MO_GLOBAL.set_org_context(l_org_id,Null);
77 FND_PROFILE.PUT ('ORG_ID',l_org_id);
78 Return Null;
79 End;
80 */
81 ----------------------------------------------------------------------
82 ----------------------------------------------------------------------
83 --- GET ORDER HEADER ID
84 ----------------------------------------------------------------------
85 ----------------------------------------------------------------------
86 FUNCTION get_order_header_id (p_order_line_id IN NUMBER)
87 RETURN NUMBER
88 IS
89 -- Cursor for getting the Order header id for a order line
90 CURSOR l_ord_csr
91 IS
92 SELECT header_id
93 FROM okx_order_lines_v ol
94 WHERE ol.id1 = p_order_line_id;
95
96 l_ord_hdr_id NUMBER;
97 BEGIN
98 OPEN l_ord_csr;
99
100 FETCH l_ord_csr
101 INTO l_ord_hdr_id;
102
103 IF l_ord_csr%NOTFOUND
104 THEN
105 CLOSE l_ord_csr;
106
107 RETURN (NULL);
108 END IF;
109
110 CLOSE l_ord_csr;
111
112 RETURN (l_ord_hdr_id);
113 END get_order_header_id;
114
115 ----------------------------------------------------------------------
116 ----------------------------------------------------------------------
117 ------ GET K HEADER ID
118 ----------------------------------------------------------------------
119 ----------------------------------------------------------------------
120 FUNCTION get_k_hdr_id (p_order_hdr_id IN NUMBER)
121 RETURN NUMBER
122 IS
123 -- Cursor for Gettin g Contract hader id for a particular Order Header
124 CURSOR l_kexists_csr
125 IS
126 SELECT chr_id
127 FROM okc_k_rel_objs
128 WHERE object1_id1 = TO_CHAR (p_order_hdr_id);
129
130 -- And JTOT_OBJECT1_CODE = (Select Object_id from JTF_OBJECTS_B where object_code =
131 -- And OBJECT1_NAME = 'OE_ORDER_HEADERS';
132 l_kexists_rec l_kexists_csr%ROWTYPE;
133 BEGIN
134 OPEN l_kexists_csr;
135
136 FETCH l_kexists_csr
137 INTO l_kexists_rec;
138
139 IF l_kexists_csr%NOTFOUND
140 THEN
141 CLOSE l_kexists_csr;
142
143 RETURN (NULL);
144 END IF;
145
146 CLOSE l_kexists_csr;
147
148 RETURN (l_kexists_rec.chr_id);
149 END get_k_hdr_id;
150
151 --------------------------------------------------------------------------------------
152 --------------------------------------------------------------------------------------
153 ------- GET_RULES
154 --------------------------------------------------------------------------------------
155 --------------------------------------------------------------------------------------
156
157 /*
158 FUNCTION GET_RULES
159 (
160 p_cle_id NUMBER,
161 p_Category VARCHAR2,
162 p_object_Code VARCHAR2
163 )
164 Return NUMBER
165 Is
166
167
168 -- Cursor Get_Rule_objectid_Csr Is
169 -- Select object1_id1
170 -- From okc_rules_v rul
171 -- ,okc_rule_groups_v rgp
172 -- Where rul.rgp_id = rgp.id
173 -- And rule_information_category = p_category
174 -- And jtot_object1_Code = p_object_Code
175 -- And cle_id = p_cle_id;
176
177 -- Fixed Bug# 2281008
178 Cursor Get_Rule_objectid_Csr Is
179 Select object1_id1
180 from okc_rules_v
181 where rgp_id = (select id from okc_rule_groups_v where cle_id = p_cle_id)
182 And rule_information_category = p_category;
183
184
185
186 l_object_id VARCHAR2(40);
187
188 BEGIN
189 Open Get_Rule_objectid_Csr;
190 Fetch Get_Rule_objectid_Csr into l_object_id;
191 If Get_Rule_objectid_Csr%notfound Then
192 Close Get_Rule_objectid_Csr;
193 return(null);
194 End If;
195
196 Close Get_Rule_objectid_Csr;
197 return(l_object_id);
198
199 END GET_RULES;
200
201
202 FUNCTION GET_HDR_RULES
203 (
204 p_chr_id NUMBER,
205 p_Category VARCHAR2,
206 p_object_Code VARCHAR2
207 )
208 Return NUMBER
209 Is
210
211 Cursor Get_Rule_objectid_Csr Is
212 Select object1_id1
213 From okc_rules_v rul
214 ,okc_rule_groups_v rgp
215 Where rul.rgp_id = rgp.id
216 And rule_information_category = p_category
217 And jtot_object1_Code = p_object_Code
218 And rgp.dnz_chr_id = p_chr_id
219 And rgp.cle_id Is Null;
220
221 l_object_id VARCHAR2(40);
222
223 BEGIN
224 Open Get_Rule_objectid_Csr;
225 Fetch Get_Rule_objectid_Csr into l_object_id;
226 If Get_Rule_objectid_Csr%notfound Then
227 Close Get_Rule_objectid_Csr;
228 return(null);
229 End If;
230
231 Close Get_Rule_objectid_Csr;
232 return(l_object_id);
233
234 END GET_HDR_RULES;
235 */
236
237 ----------------------------------------------------------------------
238 ----------------------------------------------------------------------
239 --- GET K ITEM ID
240 ----------------------------------------------------------------------
241 ----------------------------------------------------------------------
242 FUNCTION get_k_item_line_id (
243 p_customer_product_id NUMBER,
244 p_service_line_id NUMBER
245 )
246 RETURN NUMBER
247 IS
248 /*
249 Cursor which selects id i.e gets all covered levels for a given
250 Contract line (Warr or ExtWar)from COntract lines.
251 */
252 CURSOR l_serv_csr
253 IS
254 SELECT ID
255 FROM okc_k_lines_v
256 WHERE cle_id = p_service_line_id;
257
258 --ERROR ADD LSL ID TO LIMIT THE QUERY
259
260 -- Cursor for selecting the Customer product id for a Covered level
261 CURSOR l_k_items_csr (p_line_id NUMBER)
262 IS
263 SELECT object1_id1
264 FROM okc_k_items_v
265 WHERE cle_id = p_line_id AND jtot_object1_code = g_jtf_cusprod;
266
267 l_serv_rec l_serv_csr%ROWTYPE;
268 l_item_rec l_k_items_csr%ROWTYPE;
269 BEGIN
270 FOR l_serv_rec IN l_serv_csr
271 LOOP
272 FOR l_item_rec IN l_k_items_csr (l_serv_rec.ID)
273 LOOP
274 IF l_item_rec.object1_id1 = p_customer_product_id
275 THEN
276 RETURN (l_serv_rec.ID);
277 END IF;
278 END LOOP;
279 END LOOP;
280
281 RETURN (NULL);
282 END get_k_item_line_id;
283
284 /*----------------------------------------------------------------------
285
286 PROCEDURE : GET_K_LINE_ID
287 DESCRIPTION :
288 INPUT : service order line id
289 header id
290 service start date
291 service end date
292 customer product id
293 OUTPUT : service line id in x_line_id
294 return status in x_return_status
295
296 ----------------------------------------------------------------------*/
297 PROCEDURE get_k_line_id (
298 p_service_id IN NUMBER,
299 p_k_hdr_id IN NUMBER,
300 p_service_startdate IN DATE,
301 p_service_enddate IN DATE,
302 x_status OUT NOCOPY CHAR,
303 x_line_id OUT NOCOPY NUMBER,
304 p_cust_product_id IN NUMBER,
305 x_return_status OUT NOCOPY VARCHAR2
306 )
307 IS
308 -- Cursor gets all the contract lines for a given contract header
309
310 -- gets all the contract lines for a given contract header
311 CURSOR l_serv_csr (p_object_id NUMBER)
312 IS
313 SELECT ki.cle_id line_id
314 FROM okc_k_items ki
315 WHERE ki.dnz_chr_id = p_k_hdr_id
316 AND ki.object1_id1 = TO_CHAR (p_object_id)
317 AND ki.jtot_object1_code = g_jtf_warr;
318
319 /*Select Kl.id
320 From OKC_K_LINES_B kl
321 ,OKC_K_ITEMS ki
322 Where kl.dnz_chr_id = p_K_hdr_Id
323 And kl.lse_id in (14,19)
324 And ki.cle_id = kl.id
325 And ki.object1_id1 = to_char(p_object_id)
326 And ki.jtot_object1_code in (G_JTF_Warr,G_JTF_Extwar)
327 And trunc(p_service_startdate) >= trunc(kl.Start_Date)
328 And trunc(p_service_enddate) <= trunc(kl.end_date) ;
329 */
330 CURSOR l_cov_lvl_csr (p_line_id NUMBER, p_object_id NUMBER)
331 IS
332 SELECT 'x'
333 FROM okc_k_lines_b kl, okc_k_items ki
334 WHERE ki.cle_id = kl.ID
335 AND ki.jtot_object1_code = 'OKX_CUSTPROD'
336 AND kl.lse_id IN (25, 18)
337 AND kl.cle_id = p_line_id
338 AND ki.object1_id1 = TO_CHAR (p_object_id);
339
340 l_serv_rec l_serv_csr%ROWTYPE;
341 l_found BOOLEAN := FALSE;
342 l_line_id NUMBER;
343 l_return_status VARCHAR2 (1) := okc_api.g_ret_sts_success;
344 l_object_id VARCHAR2 (1);
345 l_object VARCHAR2 (1);
346 BEGIN
347 x_status := 'N';
348 l_return_status := okc_api.g_ret_sts_success;
349
350 FOR l_line_rec IN l_serv_csr (p_service_id)
351 LOOP
352 l_object_id := NULL;
353
354 OPEN l_cov_lvl_csr (l_line_rec.line_id, p_cust_product_id);
355
356 FETCH l_cov_lvl_csr
357 INTO l_object_id;
358
359 CLOSE l_cov_lvl_csr;
360
361 IF l_object_id IS NOT NULL
362 THEN
363 x_status := 'D';
364 RAISE g_exception_halt_validation;
365 END IF;
366 END LOOP;
367 EXCEPTION
368 WHEN g_exception_halt_validation
369 THEN
370 x_return_status := l_return_status;
371 NULL;
372 WHEN OTHERS
373 THEN
374 x_return_status := okc_api.g_ret_sts_unexp_error;
375 okc_api.set_message (g_app_name,
376 g_unexpected_error,
377 g_sqlcode_token,
378 SQLCODE,
379 g_sqlerrm_token,
380 SQLERRM
381 );
382 END get_k_line_id;
383
384 /*----------------------------------------------------------------------
385
386 PROCEDURE : GET_K_ORDER_DETAILS
387 DESCRIPTION : This procedure is to get details from contract details form
388 INPUT : Order line id
389 OUTPUT : renewal info in l_renewal_rec
390
391 ----------------------------------------------------------------------*/
392 PROCEDURE get_k_order_details (
393 p_order_line_id IN NUMBER,
394 l_renewal_rec OUT NOCOPY renewal_rec_type
395 )
396 IS
397 CURSOR l_chr_csr
398 IS
399 SELECT chr_id, renewal_type, po_required_yn, renewal_pricing_type,
400 markup_percent, price_list_id1, line_renewal_type,
401 link_chr_id, contact_id, site_id, email_id, phone_id, fax_id,
402 billing_profile_id --new parameter added -vigandhi (May29-02)
403 ,RENEWAL_APPROVAL_FLAG --Bug# 5173373
404 FROM oks_k_order_details_v
405 WHERE order_line_id1 = TO_CHAR (p_order_line_id);
406
407 -- Fix for bug# 4690982
408
409 CURSOR c_Chr(c_CHR_ID IN NUMBER) IS
410 SELECT Id
411 FROM OKC_K_HEADERS_B CHR
412 ,OKC_STATUSES_B CST
413 WHERE CHR.STS_CODE = CST.CODE
414 AND CHR.DATE_TERMINATED IS NULL
415 AND CST.STE_CODE IN ( 'ACTIVE', 'SIGNED')
416 AND CHR.ID = c_CHR_ID;
417
418 l_link_chr_id NUMBER;
419
420 --Fix for bug# 4690982
421
422 BEGIN
423
424 OPEN l_chr_csr;
425 FETCH l_chr_csr INTO l_renewal_rec;
426 CLOSE l_chr_csr;
427
428 -- Fix for bug# 4690982
429
430 IF l_Renewal_Rec.Link_Chr_Id IS NOT NULL THEN
431
432 OPEN c_Chr(c_CHR_ID => l_Renewal_Rec.Link_Chr_Id);
433 FETCH c_Chr INTO l_link_chr_id;
434 CLOSE c_Chr;
435
436 IF l_link_chr_id IS NULL THEN
437 FND_FILE.PUT_LINE (FND_FILE.LOG, 'OKS_EXTWAR_UTIL_PVT.get_k_order_details : LINK CHR ID - '
438 ||TO_CHAR(l_Renewal_Rec.Link_Chr_Id)
439 ||'is TERMINATED or CANCELLED');
440 END IF;
441
442 l_Renewal_Rec.Link_Chr_Id := l_link_chr_id;
443
444 END IF;
445
446 -- Fix for bug# 4690982
447
448 END;
449
450 /************************************************************************
451 Function to round the amount based on Fnd_currency set up
452 Input parameter -- Amount
453 Output Parameter -- Precision Amount -- Hari 03/07/2001
454 ************************************************************************/
455 FUNCTION round_currency_amt (p_amount IN NUMBER, p_currency_code IN VARCHAR2)
456 RETURN NUMBER
457 IS
458 CURSOR fnd_cur
459 IS
460 SELECT minimum_accountable_unit, PRECISION, extended_precision
461 FROM fnd_currencies
462 WHERE currency_code = p_currency_code;
463
464 l_mau fnd_currencies.minimum_accountable_unit%TYPE;
465 l_sp fnd_currencies.PRECISION%TYPE;
466 l_ep fnd_currencies.extended_precision%TYPE;
467 BEGIN
468 OPEN fnd_cur;
469
470 FETCH fnd_cur
471 INTO l_mau, l_sp, l_ep;
472
473 CLOSE fnd_cur;
474
475 IF l_mau IS NOT NULL
476 THEN
477 IF l_mau < 0.00001
478 THEN
479 RETURN (ROUND (p_amount, 5));
480 ELSE
481 RETURN (ROUND (p_amount / l_mau) * l_mau);
482 END IF;
483 ELSIF l_sp IS NOT NULL
484 THEN
485 IF l_sp > 5
486 THEN
487 RETURN (ROUND (p_amount, 5));
488 ELSE
489 RETURN (ROUND (p_amount, l_sp));
490 END IF;
491 ELSE
492 RETURN (ROUND (p_amount, 5));
493 END IF;
494 END round_currency_amt;
495
496 /************************************************************************
497 Procedure to strip the credit card of Blank spaces
498 Input parameter -- Credit Card
499 Output Parameter -- Stripped Credit card -- Hari 2/22/2001
500 ************************************************************************/
501 PROCEDURE strip_white_spaces (
502 p_credit_card_num IN VARCHAR2,
503 p_stripped_cc_num OUT NOCOPY VARCHAR2
504 )
505 IS
506 TYPE character_tab_typ IS TABLE OF CHAR (1)
507 INDEX BY BINARY_INTEGER;
508
509 len_credit_card_num NUMBER := 0;
510 l_cc_num_char character_tab_typ;
511 BEGIN
512 SELECT LENGTH (p_credit_card_num)
513 INTO len_credit_card_num
514 FROM DUAL;
515
516 FOR i IN 1 .. len_credit_card_num
517 LOOP
518 SELECT SUBSTR (p_credit_card_num, i, 1)
519 INTO l_cc_num_char (i)
520 FROM DUAL;
521
522 IF ((l_cc_num_char (i) >= '0') AND (l_cc_num_char (i) <= '9'))
523 THEN
524 -- Numeric digit. Add to stripped_number and table.
525 p_stripped_cc_num := p_stripped_cc_num || l_cc_num_char (i);
526 END IF;
527 END LOOP;
528 EXCEPTION
529 WHEN OTHERS
530 THEN
531 RAISE;
532 END strip_white_spaces;
533
534 /*----------------------------------------------------------------------
535
536 PROCEDURE : CHECK_SERVICE_DUPLICATE
537 DESCRIPTION : This procedure is to check the duplicate service ordered.
538 INPUT : Order line id
539 service order line id
540 Service start date
541 service end date
542 OUTPUT : return status 'S' if success
543 duplicate service status
544 ----------------------------------------------------------------------*/
545 PROCEDURE check_service_duplicate (
546 p_order_line_id IN NUMBER,
547 p_serv_id IN NUMBER,
548 p_customer_product_id IN NUMBER,
549 p_serv_start_date IN DATE,
550 p_serv_end_date IN DATE,
551 x_return_status OUT NOCOPY VARCHAR2,
552 x_service_status OUT NOCOPY VARCHAR2
553 )
554 IS
555 CURSOR l_khdr_csr (p_order_hdr_id NUMBER)
556 IS
557 SELECT chr_id
558 FROM okc_k_rel_objs
559 WHERE object1_id1 = TO_CHAR (p_order_hdr_id)
560 AND jtot_object1_code = 'OKX_ORDERHEAD';
561
562 --Added for bug# 5382119
563
564 CURSOR c_Service_Dup(c_order_hdr_id IN VARCHAR2
565 ,c_warr_item_id IN VARCHAR2
566 ,c_instance_id IN VARCHAR2) IS
567 SELECT /*+ leading (kii) use_nl (kii rel kl kiw)
568 index(kiw okc_k_items_n1) */ 'D'
569 FROM Okc_k_items kii
570 ,Okc_k_rel_objs rel
571 ,Okc_k_lines_b kl
572 ,Okc_k_items kiw
573 WHERE rel.object1_id1 = c_order_hdr_id
574 AND rel.jtot_object1_code = 'OKX_ORDERHEAD'
575 AND kiw.dnz_chr_id = rel.chr_id
576 AND kiw.object1_id1 = c_warr_item_id
577 AND kiw.jtot_object1_code = G_JTF_WARR
578 AND kl.cle_id = kiw.cle_id
579 AND kl.lse_id IN (18,25)
580 AND kii.cle_id = kl.id
581 AND kii.jtot_object1_code = 'OKX_CUSTPROD'
582 AND kii.object1_id1 = c_instance_id
583 AND kii.dnz_chr_id = rel.chr_id;
584
585 --Added for bug# 5382119
586
587 l_return_status VARCHAR2 (1) := okc_api.g_ret_sts_success;
588 l_order_hdr_id NUMBER := NULL;
589 l_chr_id NUMBER;
590 l_service_line_id NUMBER;
591 l_cle_status CHAR;
592 l_item_cle_id NUMBER;
593 l_warr_flag VARCHAR2 (1);
594 l_object_name VARCHAR2 (40);
595
596 l_service_dup VARCHAR2(30);
597
598 BEGIN
599
600 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level
601 THEN
602 fnd_log.STRING (fnd_log.level_procedure,
603 g_module_current || '.CHECK_SERVICE_DUPLICATE.begin',
604 'Order Line Id = '
605 || p_order_line_id
606 || ',Service Id = '
607 || p_serv_id
608 || ',Customer Product Id = '
609 || p_customer_product_id
610 || ',Service start date = '
611 || p_serv_start_date
612 || ',Service End date = '
613 || p_serv_end_date
614 );
615 END IF;
616
617 x_return_status := l_return_status;
618 l_order_hdr_id := get_order_header_id (p_order_line_id);
619
620 IF l_order_hdr_id IS NULL
621 THEN
622 x_service_status := NULL;
623 l_return_status := okc_api.g_ret_sts_error;
624
625 IF fnd_log.level_error >= fnd_log.g_current_runtime_level
626 THEN
627 fnd_log.STRING (fnd_log.level_error,
628 g_module_current
629 || '.CHECK_SERVICE_DUPLICATE.ERROR',
630 'Order Header Id NULL'
631 );
632 END IF;
633
634 okc_api.set_message (g_app_name,
635 'OKS_INVD_ORD_LINE_ID',
636 'LINE_ID',
637 p_order_line_id
638 );
639 RAISE g_exception_halt_validation;
640 END IF;
641
642 -- Modified for bug# 5382119
643
644 -- x_service_status := 'N';
645 -- l_return_status := okc_api.g_ret_sts_success;
646
647 l_service_dup := 'N';
648
649 OPEN c_service_dup(c_order_hdr_id => to_char(l_Order_hdr_id)
650 ,c_warr_item_id => to_char(p_Serv_Id)
651 ,c_instance_id => to_char(p_customer_product_id));
652 FETCH c_service_dup INTO l_service_dup;
653 CLOSE c_service_dup;
654
655 x_Service_Status := NVL(l_service_dup,'N');
656
657 -- --bug #2317981
658 --
659 -- FOR l_hdr_rec IN l_khdr_csr (l_order_hdr_id)
660 -- LOOP
661 -- get_k_line_id (p_cust_product_id => p_customer_product_id,
662 -- p_service_id => p_serv_id,
663 -- p_k_hdr_id => l_hdr_rec.chr_id,
664 -- p_service_startdate => p_serv_start_date,
665 -- p_service_enddate => p_serv_end_date,
666 -- x_status => l_cle_status,
667 -- x_line_id => l_service_line_id,
668 -- x_return_status => l_return_status
669 -- );
670 --
671 -- -- to be confirmed
672 -- IF (fnd_log.level_event >= fnd_log.g_current_runtime_level)
673 -- THEN
674 -- fnd_log.STRING (fnd_log.level_event,
675 -- g_module_current
676 -- || '.CHECK_SERVICE_DUPLICATE.Internal_Call.after',
677 -- 'Get_K_Line_Id(Return status = '
678 -- || l_return_status
679 -- || ', Duplicate Service Line = '
680 -- || l_cle_status
681 -- || ', Service Line Id = '
682 -- || l_service_line_id
683 -- || ')'
684 -- );
685 -- END IF;
686 --
687 -- x_service_status := l_cle_status;
688 --
689 -- IF l_cle_status = 'D'
690 -- THEN
691 -- l_return_status := okc_api.g_ret_sts_success;
692 -- RAISE g_exception_halt_validation;
693 -- END IF;
694 --
695 -- IF NOT l_return_status = okc_api.g_ret_sts_success
696 -- THEN
697 -- l_return_status := okc_api.g_ret_sts_unexp_error;
698 -- okc_api.set_message (g_app_name, 'OKS_PROC_GET_K_LINE_ID');
699 -- RAISE g_exception_halt_validation;
700 -- END IF;
701 -- END LOOP;
702 --
703
704 EXCEPTION
705 WHEN g_exception_halt_validation
706 THEN
707 x_return_status := l_return_status;
708 NULL;
709 WHEN OTHERS
710 THEN
711 IF fnd_log.level_unexpected >= fnd_log.g_current_runtime_level
712 THEN
713 fnd_log.STRING (fnd_log.level_unexpected,
714 g_module_current
715 || '.CHECK_SERVICE_DUPLICATE.UNEXPECTED',
716 'sqlcode = ' || SQLCODE || ', sqlerrm = '
717 || SQLERRM
718 );
719 END IF;
720
721 x_return_status := okc_api.g_ret_sts_unexp_error;
722 okc_api.set_message (g_app_name,
723 g_unexpected_error,
724 g_sqlcode_token,
725 SQLCODE,
726 g_sqlerrm_token,
727 SQLERRM
728 );
729 END check_service_duplicate;
730
731 /*----------------------------------------------------------------------
732
733 PROCEDURE : GET_WAR_ITEM_ID
734 DESCRIPTION : This procedure is to get the warranty item ids.
735 INPUT : product item id
736 common bill sequence id
737 OUTPUT : return status 'S' if successful
738 warranty item ids in x_war_item_tbl
739 ----------------------------------------------------------------------*/
740 PROCEDURE get_war_item_id (
741 p_inventory_item_id IN NUMBER,
742 p_datec IN DATE,
743 x_common_bill_seq_id OUT NOCOPY NUMBER,
744 x_return_status OUT NOCOPY VARCHAR2,
745 x_war_item_tbl OUT NOCOPY war_item_id_tbl_type
746 )
747 IS
748 l_comm_bill_seq_id NUMBER;
749 l_ptr2 BINARY_INTEGER;
750 l_return_status VARCHAR2 (1) := okc_api.g_ret_sts_success;
751
752 CURSOR c_war_items_csr (c_bill_seq_id NUMBER)
753 IS
754 SELECT bic.component_item_id war_item_id
755 FROM bom_inventory_components bic --OKX_INV_COMPONENTS_V bic
756 WHERE bic.bill_sequence_id = c_bill_seq_id
757 AND EXISTS (
758 SELECT 'Component is a Warranty'
759 FROM okx_system_items_v mtl
760 WHERE mtl.id2 = okc_context.get_okc_organization_id
761 AND mtl.id1 = bic.component_item_id
762 AND mtl.vendor_warranty_flag = 'Y')
763 AND TRUNC (p_datec) >= TRUNC (bic.effectivity_date)
764 AND TRUNC (p_datec) <=
765 NVL (TRUNC (bic.disable_date), TRUNC (p_datec))
766 ORDER BY bic.component_item_id;
767
768 CURSOR bill_seq_csr
769 IS
770 SELECT common_bill_sequence_id
771 FROM bom_bill_of_materials --OKX_BILL_OF_MATERIALS_V
772 WHERE organization_id = okc_context.get_okc_organization_id
773 AND assembly_item_id = p_inventory_item_id
774 AND alternate_bom_designator IS NULL;
775 BEGIN
776 x_return_status := 'S';
777
778 OPEN bill_seq_csr;
779
780 FETCH bill_seq_csr
781 INTO l_comm_bill_seq_id;
782
783 IF bill_seq_csr%NOTFOUND
784 THEN
785 CLOSE bill_seq_csr;
786
787 RAISE g_exception_halt_validation;
788 END IF;
789
790 CLOSE bill_seq_csr;
791
792 x_common_bill_seq_id := l_comm_bill_seq_id;
793
794 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
795 THEN
796 fnd_log.STRING (fnd_log.level_statement,
797 g_module_current
798 || '.GET_WAR_ITEM_ID.after bill_seq_csr',
799 'Common bill Sequence Id =' || x_common_bill_seq_id
800 );
801 END IF;
802
803 l_ptr2 := 1;
804
805 FOR c_war_items_rec IN c_war_items_csr (l_comm_bill_seq_id)
806 LOOP
807 x_war_item_tbl (l_ptr2).war_item_id := c_war_items_rec.war_item_id;
808
809 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
810 THEN
811 fnd_log.STRING (fnd_log.level_statement,
812 g_module_current
813 || '.GET_WAR_ITEM_ID.loop c_war_items_csr',
814 'War Item Id('
815 || l_ptr2
816 || ')'
817 || '='
818 || x_war_item_tbl (l_ptr2).war_item_id
819 );
820 END IF;
821
822 l_ptr2 := l_ptr2 + 1;
823 END LOOP;
824 EXCEPTION
825 WHEN g_exception_halt_validation
826 THEN
827 x_return_status := l_return_status;
828 NULL;
829 WHEN OTHERS
830 THEN
831 x_return_status := okc_api.g_ret_sts_unexp_error;
832 okc_api.set_message (g_app_name,
833 g_unexpected_error,
834 g_sqlcode_token,
835 SQLCODE,
836 g_sqlerrm_token,
837 SQLERRM
838 );
839
840 IF fnd_log.level_unexpected >= fnd_log.g_current_runtime_level
841 THEN
842 fnd_log.STRING (fnd_log.level_unexpected,
843 g_module_current || '.GET_WAR_ITEM_ID.UNEXPECTED',
844 'sqlcode = ' || SQLCODE || ', sqlerrm = '
845 || SQLERRM
846 );
847 END IF;
848 END get_war_item_id;
849
850 /*----------------------------------------------------------------------
851
852 PROCEDURE : GET_WAR_DUR_PER
853 DESCRIPTION : This procedure is to get warranty duration, attached
854 coverage template id.
855 INPUT : product inventory item id,
856 warranty inventory item id,
857 common bill sequence id,
858 transaction date
859 OUTPUT : coverage id,
860 warranty duration, uom code
861 retun status in x_return_status
862
863 ----------------------------------------------------------------------*/
864 PROCEDURE get_war_dur_per (
865 p_prod_inv_item_id IN NUMBER,
866 p_war_inv_item_id IN NUMBER,
867 p_war_date IN DATE,
868 p_comm_bill_seq_id IN NUMBER,
869 x_duration OUT NOCOPY NUMBER,
870 x_uom_code OUT NOCOPY VARCHAR2,
871 x_cov_sch_id OUT NOCOPY NUMBER,
872 x_return_status OUT NOCOPY VARCHAR2
873 )
874 IS
875 l_war_date DATE;
876 l_com_bill_seq_id NUMBER;
877 l_return_status VARCHAR2 (1) := okc_api.g_ret_sts_success;
878
879 CURSOR bom_dtls_csr
880 IS
881 SELECT bic.component_quantity, b.primary_uom_code,
882 b.coverage_schedule_id coverage_template_id
883 FROM mtl_system_items_b_kfv b, --okx_system_items_v mtl,
884 bom_inventory_components bic --okx_inv_components_v bic
885 WHERE bic.component_item_id = b.inventory_item_id
886 AND b.organization_id = okc_context.get_okc_organization_id
887 AND bic.bill_sequence_id = p_comm_bill_seq_id
888 AND bic.component_item_id = p_war_inv_item_id
889 AND TRUNC (l_war_date) >= TRUNC (bic.effectivity_date)
890 AND TRUNC (l_war_date) <=
891 NVL (TRUNC (bic.disable_date), TRUNC (l_war_date) + 1)
892 -- fix bug 2458473
893 AND b.vendor_warranty_flag = 'Y'
894 AND ROWNUM < 2;
895 BEGIN
896 x_return_status := okc_api.g_ret_sts_success;
897 l_war_date := NVL (p_war_date, SYSDATE);
898
899 OPEN bom_dtls_csr;
900
901 FETCH bom_dtls_csr
902 INTO x_duration, x_uom_code, x_cov_sch_id;
903
904 IF bom_dtls_csr%NOTFOUND
905 THEN
906 CLOSE bom_dtls_csr;
907
908 l_return_status := okc_api.g_ret_sts_unexp_error;
909
910 IF fnd_log.level_error >= fnd_log.g_current_runtime_level
911 THEN
912 fnd_log.STRING
913 (fnd_log.level_error,
914 g_module_current || '.GET_WAR_DUR_PER.ERROR',
915 'Bom_dtl_csr not Found: Mising Duration And Coverage '
916 );
917 END IF;
918
919 okc_api.set_message (g_app_name, 'MISSING_DUR_AND_COVERAGE');
920 RAISE g_exception_halt_validation;
921 END IF;
922
923 CLOSE bom_dtls_csr;
924
925 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
926 THEN
927 fnd_log.STRING (fnd_log.level_statement,
928 g_module_current
929 || '.GET_WAR_DUR_PER.After bom_dtls_csr',
930 'Warranty Duration' || x_duration || ':'
931 || x_uom_code
932 );
933 END IF;
934 EXCEPTION
935 WHEN g_exception_halt_validation
936 THEN
937 x_return_status := l_return_status;
938 NULL;
939 WHEN OTHERS
940 THEN
941 x_return_status := okc_api.g_ret_sts_unexp_error;
942 okc_api.set_message (g_app_name,
943 g_unexpected_error,
944 g_sqlcode_token,
945 SQLCODE,
946 g_sqlerrm_token,
947 SQLERRM
948 );
949
950 IF fnd_log.level_unexpected >= fnd_log.g_current_runtime_level
951 THEN
952 fnd_log.STRING (fnd_log.level_unexpected,
953 g_module_current || '.GET_WAR_DUR_PER.UNEXPECTED',
954 'sqlcode = ' || SQLCODE || ', sqlerrm = '
955 || SQLERRM
956 );
957 END IF;
958 END get_war_dur_per;
959
960 /*----------------------------------------------------------------------
961
962 PROCEDURE : GET_WARRANTY_INFO
963 DESCRIPTION : This procedure is to get the information regarding attached
964 warranties to an instance defined in BOM
965 INPUT : Inventory item id
966 Customer product id
967 Shipped date
968 Installation date
969 OUTPUT : Warranty details in x_warranty_tbl
970 retun status 'S' if successful
971
972 ----------------------------------------------------------------------*/
973 PROCEDURE get_warranty_info (
974 p_prod_item_id IN NUMBER,
975 p_customer_product_id IN NUMBER,
976 x_return_status OUT NOCOPY VARCHAR2,
977 p_ship_date IN DATE,
978 p_installation_date IN DATE,
979 x_warranty_tbl OUT NOCOPY war_tbl
980 )
981 IS
982 l_warranty_tbl war_item_new_tbl_type;
983 l_ptr INTEGER;
984 l_comm_bill_seq_id NUMBER;
985 l_return_status VARCHAR2 (1) := okc_api.g_ret_sts_success;
986 l_ship_date DATE;
987 l_ship_flag VARCHAR2 (1);
988 l_start_delay NUMBER;
989 p_date DATE;
990
991 CURSOR get_ship_csr
992 IS
993 SELECT service_starting_delay
994 FROM mtl_system_items_b_kfv
995 WHERE inventory_item_id = p_prod_item_id
996 AND organization_id = okc_context.get_okc_organization_id;
997
998 CURSOR get_ship_flag_csr
999 IS
1000 SELECT DECODE (ol.actual_shipment_date,
1001 NULL, 'N',
1002 'Y'
1003 ) shipped_flag
1004 FROM csi_item_instances csi, oe_order_lines_all ol
1005 WHERE csi.instance_id = p_customer_product_id
1006 AND csi.last_oe_order_line_id = ol.line_id;
1007
1008 /* fix for bug#6047047 -- fp of bug#5939487 */
1009 Cursor C_war_items_csr(C_bill_seq_id NUMBER) Is
1010 Select bic.component_item_id war_item_id,bic.effectivity_date,bic.disable_date
1011 From BOM_INVENTORY_COMPONENTS bic --OKX_INV_COMPONENTS_V bic
1012 Where bic.bill_sequence_id = C_bill_seq_id
1013 And exists
1014 (
1015 Select 'Component is a Warranty'
1016 From OKX_SYSTEM_ITEMS_V mtl
1017 Where mtl.id2 = okc_context.get_okc_organization_id
1018 And mtl.id1 = bic.component_item_id
1019 And mtl.vendor_warranty_flag = 'Y'
1020 )
1021 Order by bic.component_item_id;
1022
1023 Cursor Bill_Seq_Csr Is
1024 Select common_bill_sequence_id
1025 From BOM_BILL_OF_MATERIALS --OKX_BILL_OF_MATERIALS_V
1026 Where organization_id = okc_context.get_okc_organization_id
1027 And assembly_item_id = P_prod_item_id
1028 And alternate_bom_designator is Null;
1029
1030 /* end of fix for bug#6047047-fp of bug#5939487 */
1031
1032 l_get_date_rec get_ship_flag_csr%ROWTYPE;
1033 BEGIN
1034 x_return_status := okc_api.g_ret_sts_success;
1035
1036 /* commented for the bug#6047047-fp of bug#5939487
1037 IF p_prod_item_id IS NULL
1038 THEN
1039 x_return_status := okc_api.g_ret_sts_error;
1040
1041 IF fnd_log.level_error >= fnd_log.g_current_runtime_level
1042 THEN
1043 fnd_log.STRING (fnd_log.level_error,
1044 g_module_current || '.GET_WARRANTY_INFO.ERROR',
1045 'Product Item Id Required'
1046 );
1047 END IF;
1048
1049 okc_api.set_message (g_app_name, 'OKS_ORG_ID_INV_ID REQUIRED');
1050 RAISE g_exception_halt_validation;
1051 END IF;
1052
1053 OPEN get_ship_csr;
1054
1055 FETCH get_ship_csr
1056 INTO l_start_delay;
1057
1058 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
1059 THEN
1060 fnd_log.STRING (fnd_log.level_statement,
1061 g_module_current
1062 || '.GET_WARRANTY_INFO.after get_chip_csr',
1063 'Start Delay = ' || l_start_delay
1064 );
1065 END IF;
1066
1067 IF get_ship_csr%NOTFOUND
1068 THEN
1069 CLOSE get_ship_csr;
1070
1071 l_return_status := okc_api.g_ret_sts_error;
1072
1073 IF fnd_log.level_error >= fnd_log.g_current_runtime_level
1074 THEN
1075 fnd_log.STRING (fnd_log.level_error,
1076 g_module_current || '.GET_WARRANTY_INFO.ERROR',
1077 'Get_Ship_Csr not Found'
1078 );
1079 END IF;
1080
1081 okc_api.set_message (g_app_name,
1082 'OKS_SHIPFLG_STDELAY_NOT_SET',
1083 'P_PROD_ITEM_ID',
1084 p_prod_item_id
1085 );
1086 RAISE g_exception_halt_validation;
1087 END IF;
1088
1089 CLOSE get_ship_csr;
1090
1091 IF p_installation_date IS NULL AND p_ship_date IS NULL
1092 THEN
1093 l_return_status := okc_api.g_ret_sts_error;
1094
1095 IF fnd_log.level_error >= fnd_log.g_current_runtime_level
1096 THEN
1097 fnd_log.STRING (fnd_log.level_error,
1098 g_module_current || '.GET_WARRANTY_INFO.ERROR',
1099 'Installation date and Ship Date not Set'
1100 );
1101 END IF;
1102
1103 okc_api.set_message (g_app_name,
1104 'OKS_SHIPFLG_SHIPDT_NOT_SET',
1105 'P_ITEM_ID',
1106 p_prod_item_id
1107 );
1108 RAISE g_exception_halt_validation;
1109 END IF;
1110
1111 -- Installation date will be considered first -- vigandhi 04-jun-2002
1112 IF p_installation_date IS NOT NULL
1113 THEN
1114 l_ship_date := p_installation_date;
1115 p_date := l_ship_date;
1116 ELSE
1117 l_ship_date := p_ship_date;
1118 p_date := NVL (l_start_delay, 0) + l_ship_date;
1119 END IF;
1120
1121 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
1122 THEN
1123 fnd_log.STRING (fnd_log.level_statement,
1124 g_module_current || '.GET_WARRANTY_INFO.ship dates',
1125 'Ship Date = ' || l_ship_date || 'p_date = '
1126 || p_date
1127 );
1128 END IF;
1129
1130 get_war_item_id (p_inventory_item_id => p_prod_item_id,
1131 p_datec => p_date,
1132 x_return_status => l_return_status,
1133 x_war_item_tbl => l_warranty_tbl,
1134 x_common_bill_seq_id => l_comm_bill_seq_id
1135 );
1136
1137 IF (fnd_log.level_event >= fnd_log.g_current_runtime_level)
1138 THEN
1139 fnd_log.STRING (fnd_log.level_event,
1140 g_module_current
1141 || '.GET_WARRANTY_INFO.Internal_call.after',
1142 ' Get_War_Item_Id(Return status = '
1143 || l_return_status
1144 || 'Count ='
1145 || l_warranty_tbl.COUNT
1146 || ')'
1147 );
1148 END IF;
1149
1150 IF l_warranty_tbl.COUNT = 0
1151 THEN
1152 RAISE g_exception_halt_validation;
1153 END IF;
1154
1155 IF NOT l_warranty_tbl.COUNT = 0
1156 THEN
1157 l_ptr := l_warranty_tbl.FIRST;
1158
1159 LOOP
1160 get_war_dur_per
1161 (p_prod_inv_item_id => p_prod_item_id,
1162 p_comm_bill_seq_id => l_comm_bill_seq_id,
1163 p_war_inv_item_id => l_warranty_tbl (l_ptr).war_item_id,
1164 x_duration => x_warranty_tbl (l_ptr).duration_quantity,
1165 x_uom_code => x_warranty_tbl (l_ptr).duration_period,
1166 x_cov_sch_id => x_warranty_tbl (l_ptr).coverage_schedule_id,
1167 p_war_date => p_date,
1168 x_return_status => l_return_status
1169 );
1170
1171 IF (fnd_log.level_event >= fnd_log.g_current_runtime_level)
1172 THEN
1173 fnd_log.STRING (fnd_log.level_event,
1174 g_module_current
1175 || '.GET_WARRANTY_INFO.Internal_call.after',
1176 ' Get_War_Dur_Per(Return status = '
1177 || l_return_status
1178 || ')'
1179 );
1180 END IF;
1181
1182 IF NOT l_return_status = okc_api.g_ret_sts_success
1183 THEN
1184 RAISE g_exception_halt_validation;
1185 END IF;
1186
1187 x_warranty_tbl (l_ptr).service_item_id :=
1188 l_warranty_tbl (l_ptr).war_item_id;
1189 x_warranty_tbl (l_ptr).warranty_start_date := TRUNC (p_date);
1190 x_warranty_tbl (l_ptr).warranty_end_date :=
1191 TRUNC
1192 (okc_time_util_pub.get_enddate
1193 (p_start_date => x_warranty_tbl (l_ptr).warranty_start_date,
1194 p_duration => x_warranty_tbl (l_ptr).duration_quantity,
1195 p_timeunit => x_warranty_tbl (l_ptr).duration_period
1196 )
1197 );
1198
1199 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
1200 THEN
1201 fnd_log.STRING (fnd_log.level_statement,
1202 g_module_current
1203 || '.GET_WARRANTY_INFO.Warranty details:',
1204 'start Date = '
1205 || x_warranty_tbl (l_ptr).warranty_start_date
1206 || ',End date = '
1207 || x_warranty_tbl (l_ptr).warranty_end_date
1208 || ',Duration = '
1209 || x_warranty_tbl (l_ptr).duration_quantity
1210 || ',Period = '
1211 || x_warranty_tbl (l_ptr).duration_period
1212 );
1213 END IF;
1214
1215 -- Fixed bug# 2414184 -12Jun2002
1216 IF x_warranty_tbl (l_ptr).warranty_end_date IS NULL
1217 THEN
1218 IF fnd_log.level_error >= fnd_log.g_current_runtime_level
1219 THEN
1220 fnd_log.STRING (fnd_log.level_error,
1221 g_module_current
1222 || '.GET_WARRANTY_INFO.ERROR',
1223 'Null Warranty End Date'
1224 );
1225 END IF;
1226
1227 okc_api.set_message (g_app_name, 'OKS_END_DT_DUR_REQUIRED');
1228 l_return_status := okc_api.g_ret_sts_error;
1229 RAISE g_exception_halt_validation;
1230 END IF;
1231
1232 EXIT WHEN (l_ptr = l_warranty_tbl.LAST);
1233 l_ptr := l_warranty_tbl.NEXT (l_ptr);
1234 END LOOP;
1235 END IF;
1236 */
1237
1238 /* New code for the bug#6047047 - fp of bug#5939487 */
1239 -- Added to check the warranty item associated to the item even before checking the start delay in Inventory. --
1240
1241 If p_prod_item_id is Null then
1242 x_return_status := OKC_API.G_RET_STS_ERROR;
1243 IF FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1244 fnd_log.string(FND_LOG.LEVEL_ERROR,G_MODULE_CURRENT||'.GET_WARRANTY_INFO.ERROR','Product Item Id Required');
1245 END IF;
1246 OKC_API.set_message(G_APP_NAME,'OKS_ORG_ID_INV_ID REQUIRED');
1247 Raise G_EXCEPTION_HALT_VALIDATION;
1248 End If;
1249
1250 Open Bill_Seq_Csr;
1251 Fetch Bill_Seq_Csr into l_comm_bill_seq_id;
1252 Close bill_seq_csr;
1253
1254 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1255 fnd_log.string(FND_LOG.LEVEL_STATEMENT,G_MODULE_CURRENT||'.GET_WAR_ITEM_ID.after bill_seq_csr','Common bill Sequence Id ='
1256 || l_Comm_bill_Seq_ID);
1257 END IF;
1258
1259 open c_war_items_csr(l_comm_bill_seq_id);
1260 fetch c_war_items_csr bulk collect into l_warranty_tbl;
1261 close c_war_items_csr;
1262
1263 IF l_warranty_tbl.COUNT > 0 Then
1264
1265 Open Get_Ship_Csr;
1266 Fetch Get_Ship_Csr into l_start_delay;
1267 Close Get_ship_Csr;
1268
1269 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1270 fnd_log.string(FND_LOG.LEVEL_STATEMENT,G_MODULE_CURRENT||'.GET_WARRANTY_INFO.after get_chip_csr',
1271 'Start Delay = '||l_start_delay );
1272 END IF;
1273
1274 If p_installation_date Is Null AND p_ship_date Is Null Then
1275 l_return_status := OKC_API.G_RET_STS_ERROR;
1276 IF FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1277 fnd_log.string(FND_LOG.LEVEL_ERROR,G_MODULE_CURRENT||'.GET_WARRANTY_INFO.ERROR','Installation date and Ship Date not Set');
1278 END IF;
1279 OKC_API.set_message(G_APP_NAME,'OKS_SHIPFLG_SHIPDT_NOT_SET','P_ITEM_ID',p_prod_item_id);
1280 Raise G_EXCEPTION_HALT_VALIDATION;
1281 End If;
1282
1283 -- Installation date will be considered first -- vigandhi 04-jun-2002
1284
1285 If p_installation_date Is Not Null Then
1286 l_ship_date := p_installation_date;
1287 P_date := l_ship_date;
1288 Else
1289 l_ship_date := p_ship_date;
1290 P_date := Nvl(l_start_delay,0) + l_ship_date;
1291
1292 End If;
1293
1294 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1295 fnd_log.string(FND_LOG.LEVEL_STATEMENT,G_MODULE_CURRENT||'.GET_WARRANTY_INFO.ship dates',
1296 'Ship Date = '||l_ship_date || 'p_date = '|| p_date );
1297 END IF;
1298
1299 l_ptr := l_warranty_tbl.FIRST;
1300
1301 Loop
1302
1303 IF (trunc(p_date) >= trunc(l_warranty_tbl(l_ptr).EFFECTIVITY_DATE ))
1304 And (trunc(p_date) <= nvl(trunc(l_warranty_tbl(l_ptr).disable_date),trunc(p_date))) Then
1305
1306 Get_War_Dur_Per
1307 (
1308 P_prod_inv_item_id => P_prod_item_id,
1309 P_comm_bill_seq_id => l_comm_bill_seq_id,
1310 P_war_inv_item_id => l_warranty_tbl(l_ptr).war_item_id,
1311 X_duration => x_warranty_tbl(l_ptr).duration_Quantity,
1312 X_uom_code => x_warranty_tbl(l_ptr).Duration_period,
1313 X_cov_sch_id => x_warranty_tbl(l_ptr).Coverage_schedule_id,
1314 P_war_date => p_date,
1315 X_return_status => l_return_status
1316 ) ;
1317
1318
1319 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1320 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE_CURRENT||'.GET_WARRANTY_INFO.Internal_call.after',
1321 ' Get_War_Dur_Per(Return status = '||l_return_status ||')');
1322 END IF;
1323
1324 If Not l_return_status = OKC_API.G_RET_STS_SUCCESS Then
1325 Raise G_EXCEPTION_HALT_VALIDATION;
1326 End If;
1327
1328 x_warranty_tbl(l_ptr).Service_item_id := l_warranty_tbl(l_ptr).war_item_id;
1329 x_warranty_tbl(l_ptr).warranty_start_date := trunc(p_date);
1330 x_warranty_tbl(l_ptr).warranty_end_date := trunc(okc_time_util_pub.get_enddate
1331 (
1332 p_start_date => X_warranty_tbl(l_ptr).warranty_start_date ,
1333 p_duration => X_warranty_tbl(l_ptr).Duration_Quantity,
1334 p_timeunit => X_warranty_tbl(l_ptr).Duration_period
1335 ));
1336
1337
1338 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1339 fnd_log.string(FND_LOG.LEVEL_STATEMENT,G_MODULE_CURRENT||'.GET_WARRANTY_INFO.Warranty details:',
1340 'start Date = '||X_warranty_tbl(l_ptr).warranty_start_date
1341 || ',End date = '|| X_warranty_tbl(l_ptr).Warranty_end_date
1342 || ',Duration = '|| X_warranty_tbl(l_ptr).Duration_Quantity
1343 || ',Period = '|| X_warranty_tbl(l_ptr).Duration_period);
1344 END IF;
1345
1346 -- Fixed bug# 2414184 -12Jun2002
1347 If x_warranty_tbl(l_ptr).warranty_end_date Is Null Then
1348 IF FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1349 fnd_log.string(FND_LOG.LEVEL_ERROR,G_MODULE_CURRENT||'.GET_WARRANTY_INFO.ERROR','Null Warranty End Date');
1350 END IF;
1351 OKC_API.set_message(G_APP_NAME,'OKS_END_DT_DUR_REQUIRED');
1352 l_return_status := OKC_API.G_RET_STS_ERROR;
1353 RAISE G_EXCEPTION_HALT_VALIDATION;
1354 End If;
1355
1356 END IF;
1357
1358 Exit When (l_ptr = l_warranty_tbl.LAST);
1359
1360 l_ptr := l_Warranty_tbl.NEXT(l_ptr);
1361
1362 End Loop;
1363
1364 END IF;
1365
1366 -- End of New code --
1367
1368 EXCEPTION
1369 WHEN g_exception_halt_validation
1370 THEN
1371 x_return_status := l_return_status;
1372 NULL;
1373 WHEN OTHERS
1374 THEN
1375 x_return_status := okc_api.g_ret_sts_unexp_error;
1376 okc_api.set_message (g_app_name,
1377 g_unexpected_error,
1378 g_sqlcode_token,
1379 SQLCODE,
1380 g_sqlerrm_token,
1381 SQLERRM
1382 );
1383
1384 IF fnd_log.level_unexpected >= fnd_log.g_current_runtime_level
1385 THEN
1386 fnd_log.STRING (fnd_log.level_unexpected,
1387 g_module_current
1388 || '.GET_WARRANTY_INFO.UNEXPECTED',
1389 'sqlcode = ' || SQLCODE || ', sqlerrm = '
1390 || SQLERRM
1391 );
1392 END IF;
1393 END get_warranty_info;
1394
1395 /*----------------------------------------------------------------------
1396
1397 PROCEDURE : GET_CONTRACT_HEADER_INFO
1398 DESCRIPTION : This procedure is to get the header information details
1399 for service/item ordered from OM
1400 INPUT : Order Line id
1401 Customer product id
1402 Caller - IB
1403 OUTPUT : Header details in x_header_rec
1404 retun status 'S' if successful
1405
1406 ----------------------------------------------------------------------*/
1407 PROCEDURE get_contract_header_info (
1408 p_order_line_id IN NUMBER,
1409 p_cp_id IN NUMBER,
1410 p_caller IN VARCHAR2,
1411 x_order_error OUT NOCOPY VARCHAR2,
1412 x_return_status OUT NOCOPY VARCHAR2,
1413 x_header_rec OUT NOCOPY header_rec_type
1414 )
1415 IS
1416 CURSOR l_ord_hdr_csr (p_hdr_id NUMBER)
1417 IS
1418 SELECT oh.order_number,
1419 oh.org_id,
1420 oh.ordered_date,
1421 oh.price_list_id,
1422 oh.agreement_id,
1423 oh.cust_po_number,
1424 oh.invoicing_rule_id,
1425 oh.accounting_rule_id,
1426 oh.payment_term_id,
1427 oh.sold_to_org_id,
1428 oh.ship_to_org_id,
1429 oh.invoice_to_org_id,
1430 oh.invoice_to_contact_id,
1431 oh.sold_to_contact_id,
1432 oh.deliver_to_contact_id,
1433 oh.payment_amount,
1434 oh.transactional_curr_code,
1435 oh.tax_exempt_number,
1436 oh.tax_exempt_flag,
1437 oh.conversion_rate,
1438 oh.conversion_type_code,
1439 oh.conversion_rate_date,
1440 oh.salesrep_id,
1441 -- Modified for 120 CC Extn Projct
1442 NULL credit_card_expiration_date, -- OH.CREDIT_CARD_EXPIRATION_DATE
1443 NULL credit_card_number, -- OH.CREDIT_CARD_NUMBER
1444 --
1445 --Added in R12 by rsu
1446 oh.tax_exempt_reason_code,
1447 oh.tax_point_code
1448 --
1449 FROM oe_order_headers_all oh
1450 WHERE oh.header_id = p_hdr_id;
1451
1452 CURSOR l_party_csr (l_cust_id NUMBER)
1453 IS
1454 SELECT party_id
1455 FROM hz_cust_accounts
1456 WHERE cust_account_id = l_cust_id;
1457
1458 CURSOR l_cust_csr (l_inv_org_id NUMBER)
1459 IS
1460 SELECT ca.cust_account_id
1461 FROM hz_cust_acct_sites_all ca, hz_cust_site_uses_all cs
1462 WHERE ca.cust_acct_site_id = cs.cust_acct_site_id
1463 AND cs.site_use_id = l_inv_org_id;
1464
1465 --
1466 -- Fix for bug# 4756579 (JVARGHES)
1467 --
1468 --Cursor l_tax_csr(p_no Varchar2,p_cust_id Number) Is
1469 -- Select TAX_EXEMPTION_ID id1
1470 -- from RA_TAX_EXEMPTIONS_ALL
1471 -- where CUSTOMER_EXEMPTION_NUMBER = p_no
1472 -- and customer_id = p_cust_id
1473 -- and status in ('MANUAL','PRIMARY','UNAPPROVED');
1474 --
1475 CURSOR l_tax_csr (p_no VARCHAR2, p_cust_id NUMBER)
1476 IS
1477 SELECT tax_exemption_id id1
1478 FROM zx_exemptions
1479 WHERE exempt_certificate_number = p_no
1480 AND cust_account_id = p_cust_id
1481 AND exemption_status_code IN ('MANUAL', 'PRIMARY', 'UNAPPROVED');
1482
1483 --
1484 --
1485 CURSOR l_tax_flag_csr (p_id2 VARCHAR2)
1486 IS
1487 SELECT lv.lookup_code
1488 FROM fnd_lookup_values lv
1489 WHERE lv.LANGUAGE = USERENV ('LANG')
1490 AND security_group_id =
1491 fnd_global.lookup_security_group (lv.lookup_type,
1492 lv.view_application_id
1493 )
1494 AND lv.lookup_type = 'TAX_CONTROL_FLAG'
1495 AND lv.lookup_code = p_id2;
1496
1497 CURSOR l_contact_csr (p_hdr_id NUMBER)
1498 IS
1499 SELECT ship_to_contact_id
1500 FROM oe_order_headers_all
1501 WHERE header_id = p_hdr_id;
1502
1503 l_ord_hdr_rec l_ord_hdr_csr%ROWTYPE;
1504 l_return_status VARCHAR2 (1) := okc_api.g_ret_sts_success;
1505 l_warranty_tbl war_tbl;
1506 p_hdr_id NUMBER;
1507 l_party_id NUMBER;
1508 l_tax_id NUMBER;
1509 -- l_tax_id2 VARCHAR2 (40);
1510 l_tax_status VARCHAR2 (40);
1511 l_cust_id NUMBER;
1512 l_clflag NUMBER;
1513 l_contact_id NUMBER;
1514 l_upd_rec oks_rep_pvt.repv_rec_type;
1515 BEGIN
1516 x_return_status := l_return_status;
1517 p_hdr_id := get_order_header_id (p_order_line_id);
1518
1519 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
1520 THEN
1521 fnd_log.STRING (fnd_log.level_statement,
1522 g_module_current
1523 || '.GET_CONTRACT_HEADER_INFO.order header',
1524 'Order header id = ' || p_hdr_id
1525 );
1526 END IF;
1527
1528 IF p_hdr_id IS NULL
1529 THEN
1530 IF fnd_log.level_error >= fnd_log.g_current_runtime_level
1531 THEN
1532 fnd_log.STRING (fnd_log.level_error,
1533 g_module_current
1534 || '.GET_CONTRACT_HEADER_INFO.ERROR: ',
1535 'Header Id NULL for line ' || p_order_line_id
1536 );
1537 END IF;
1538
1539 l_return_status := okc_api.g_ret_sts_error;
1540 okc_api.set_message (g_app_name,
1541 'OKS_INVD_ORD_LINE_ID',
1542 'LINE_ID',
1543 p_order_line_id
1544 );
1545
1546 --mmadhavi
1547 IF (p_caller = 'OC')
1548 THEN
1549 fnd_message.set_name (g_app_name, 'OKS_INVD_ORD_LINE_ID');
1550 fnd_message.set_token (token => 'LINE_ID',
1551 VALUE => p_order_line_id
1552 );
1553 x_order_error := '#' || fnd_message.get_encoded || '#';
1554 END IF;
1555
1556 --mmadhavi
1557 RAISE g_exception_halt_validation;
1558 END IF;
1559
1560 OPEN l_ord_hdr_csr (p_hdr_id);
1561
1562 FETCH l_ord_hdr_csr
1563 INTO l_ord_hdr_rec;
1564
1565 IF l_ord_hdr_csr%NOTFOUND
1566 THEN
1567 IF fnd_log.level_error >= fnd_log.g_current_runtime_level
1568 THEN
1569 fnd_log.STRING (fnd_log.level_error,
1570 g_module_current
1571 || '.GET_CONTRACT_HEADER_INFO.ERROR: ',
1572 'Header record not found for header ' || p_hdr_id
1573 );
1574 END IF;
1575
1576 CLOSE l_ord_hdr_csr;
1577
1578 l_return_status := okc_api.g_ret_sts_error;
1579 okc_api.set_message (g_app_name,
1580 'OKS_ORD_HDR_DTLS_NOT_FOUND',
1581 'ORDER_HEADER_ID',
1582 p_hdr_id
1583 );
1584
1585 --mmadhavi
1586 IF (p_caller = 'OC')
1587 THEN
1588 fnd_message.set_name (g_app_name, 'OKS_ORD_HDR_DTLS_NOT_FOUND');
1589 fnd_message.set_token (token => 'ORDER_HEADER_ID',
1590 VALUE => p_hdr_id
1591 );
1592 x_order_error := '#' || fnd_message.get_encoded || '#';
1593 END IF;
1594
1595 --mmadhavi
1596 RAISE g_exception_halt_validation;
1597 END IF;
1598
1599 CLOSE l_ord_hdr_csr;
1600
1601 -- Added in R12 by rsu
1602 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
1603 THEN
1604 fnd_log.STRING (fnd_log.level_statement,
1605 g_module_current
1606 || '.GET_CONTRACT_HEADER_INFO.order header',
1607 'After querying the header'
1608 );
1609 fnd_log.STRING (fnd_log.level_statement,
1610 g_module_current
1611 || '.GET_CONTRACT_HEADER_INFO.order header',
1612 'l_Ord_hdr_rec.tax_point_code: '
1613 || l_ord_hdr_rec.tax_point_code
1614 );
1615 fnd_log.STRING (fnd_log.level_statement,
1616 g_module_current
1617 || '.GET_CONTRACT_HEADER_INFO.order header',
1618 'l_Ord_hdr_rec.tax_exempt_reason_code: '
1619 || l_ord_hdr_rec.tax_exempt_reason_code
1620 );
1621 fnd_log.STRING (fnd_log.level_statement,
1622 g_module_current
1623 || '.GET_CONTRACT_HEADER_INFO.order header',
1624 'l_Ord_hdr_rec.tax_exempt_number: '
1625 || l_ord_hdr_rec.tax_exempt_number
1626 );
1627 fnd_log.STRING (fnd_log.level_statement,
1628 g_module_current
1629 || '.GET_CONTRACT_HEADER_INFO.order header',
1630 'l_Ord_hdr_rec.tax_exempt_flag: '
1631 || l_ord_hdr_rec.tax_exempt_flag
1632 );
1633 END IF;
1634
1635 --
1636 l_contact_id := NULL;
1637
1638 OPEN l_contact_csr (p_hdr_id);
1639
1640 FETCH l_contact_csr
1641 INTO l_contact_id;
1642
1643 CLOSE l_contact_csr;
1644
1645 OPEN l_party_csr (l_ord_hdr_rec.sold_to_org_id);
1646
1647 FETCH l_party_csr
1648 INTO l_party_id;
1649
1650 IF l_party_csr%NOTFOUND
1651 THEN
1652 IF fnd_log.level_error >= fnd_log.g_current_runtime_level
1653 THEN
1654 fnd_log.STRING (fnd_log.level_error,
1655 g_module_current
1656 || '.GET_CONTRACT_HEADER_INFO.ERROR: ',
1657 'Party Record Not Found For Cust Id '
1658 || l_ord_hdr_rec.sold_to_org_id
1659 );
1660 END IF;
1661
1662 CLOSE l_party_csr;
1663
1664 l_return_status := okc_api.g_ret_sts_error;
1665 okc_api.set_message (g_app_name,
1666 'OKS_PARTY_ID_NOT_FOUND',
1667 'CUSTOMER_ID',
1668 l_ord_hdr_rec.sold_to_org_id
1669 );
1670 RAISE g_exception_halt_validation;
1671 END IF;
1672
1673 CLOSE l_party_csr;
1674
1675 /* -- Commented out for fixing 120 bug# 4899249.
1676
1677 -- Bug# 1619850 : get tax id using Invoice to org, and then Sold to org and
1678 -- and then Ship to org
1679 -- Invoice and ship to Org are at the site level must
1680 -- access okx_cust_site_uses all
1681 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1682 fnd_log.string(FND_LOG.LEVEL_STATEMENT,G_MODULE_CURRENT||'.GET_CONTRACT_HEADER_INFO.tax calculation',
1683 'Tax Exemption Number = '||l_ord_hdr_rec.tax_exempt_number ||
1684 'Tax Exemption Flag = '||l_ord_hdr_rec.tax_exempt_flag ||
1685 'Invoice To Org Id = ' ||l_ord_hdr_rec.invoice_to_org_id ||
1686 'Sold To Org Id = '||l_ord_hdr_rec.sold_to_org_id ||
1687 'Ship To Org Id = '||l_ord_hdr_rec.ship_to_org_id);
1688 END IF;
1689
1690
1691 If l_ord_hdr_rec.tax_exempt_number Is Not Null Then
1692 l_clflag := 1;
1693
1694 Open l_cust_csr(l_ord_hdr_rec.invoice_to_org_id);
1695 Fetch l_cust_csr into l_cust_id;
1696
1697 If l_cust_csr%found then
1698 Open l_tax_Csr(l_ord_hdr_rec.tax_exempt_number,l_cust_id);
1699 Fetch l_tax_Csr into l_tax_id;
1700
1701 If l_tax_Csr%found then
1702 l_clflag := 0;
1703 end if;
1704
1705 close l_tax_Csr;
1706
1707 End if;
1708
1709 Close l_cust_csr;
1710
1711 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1712 fnd_log.string(FND_LOG.LEVEL_STATEMENT,G_MODULE_CURRENT||'.GET_CONTRACT_HEADER_INFO.clflag status',
1713 'For Invoice Org '|| l_clflag);
1714 END IF;
1715
1716
1717 If l_clflag = 1 THEN
1718
1719 Open l_tax_Csr(l_ord_hdr_rec.tax_exempt_number,l_ord_hdr_rec.Sold_to_Org_id);
1720 Fetch l_tax_Csr into l_tax_id;
1721
1722 If l_tax_Csr%found then
1723 l_clflag := 0;
1724 end if;
1725
1726 close l_tax_csr;
1727
1728 End if;
1729
1730 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1731 fnd_log.string(FND_LOG.LEVEL_STATEMENT,G_MODULE_CURRENT||'.GET_CONTRACT_HEADER_INFO.clflag status',
1732 'For Sold Org '|| l_clflag);
1733 END IF;
1734
1735 l_cust_id := NULL;
1736
1737 If l_clflag = 1 THEN
1738 Open l_cust_csr(l_ord_hdr_rec.ship_to_org_id);
1739 Fetch l_cust_csr into l_cust_id;
1740
1741 If l_cust_csr%found then
1742 Open l_tax_Csr(l_ord_hdr_rec.tax_exempt_number,l_cust_id);
1743 Fetch l_tax_Csr into l_tax_id;
1744
1745 If l_tax_Csr%found then
1746 l_clflag := 0;
1747 end if;
1748
1749 close l_tax_Csr;
1750
1751 End if;
1752
1753 Close l_cust_csr;
1754
1755 End if;
1756
1757 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1758 fnd_log.string(FND_LOG.LEVEL_STATEMENT,G_MODULE_CURRENT||'.GET_CONTRACT_HEADER_INFO.clflag status',
1759 'For hip Org '|| l_clflag);
1760 END IF;
1761
1762 If l_clflag = 1 THEN
1763 IF FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1764 fnd_log.string(FND_LOG.LEVEL_ERROR,G_MODULE_CURRENT||'.GET_CONTRACT_HEADER_INFO.ERROR',
1765 'Tax Exemption Record Not Found');
1766 END IF;
1767 l_return_status := OKC_API.G_RET_STS_ERROR;
1768 OKC_API.set_message(G_APP_NAME,'OKS_TAX_DTLS_NOT_FOUND');
1769 Raise G_EXCEPTION_HALT_VALIDATION;
1770 End if;
1771
1772 End If;
1773
1774 */ -- Commented out for fixing 120 bug# 4899249.
1775
1776 /* Bug 5008188: Commented by vjramali, as the lookup code validation is not required
1777
1778 IF l_ord_hdr_rec.tax_exempt_flag IS NOT NULL
1779 THEN
1780 OPEN l_tax_flag_csr (l_ord_hdr_rec.tax_exempt_flag);
1781
1782 FETCH l_tax_flag_csr
1783 INTO l_tax_id2;
1784
1785 IF l_tax_flag_csr%NOTFOUND
1786 THEN
1787 IF fnd_log.level_error >= fnd_log.g_current_runtime_level
1788 THEN
1789 fnd_log.STRING (fnd_log.level_error,
1790 g_module_current
1791 || '.GET_CONTRACT_HEADER_INFO.ERROR',
1792 ' Tax Exempt Flag Record Not Found '
1793 );
1794 END IF;
1795
1796 CLOSE l_tax_flag_csr;
1797
1798 l_return_status := okc_api.g_ret_sts_error;
1799 --OKC_API.set_message(G_APP_NAME,'OKS_TAX_DTLS_NOT_FOUND');
1800 RAISE g_exception_halt_validation;
1801 END IF;
1802
1803 CLOSE l_tax_flag_csr;
1804 END IF;
1805 */
1806 -- IF l_ord_hdr_rec.tax_exempt_number IS NULL AND l_tax_id2 = 'E'
1807
1808 -- Ensure that a tax_exempt_number is specified if the order is tax exempted (Bug 5008188)
1809 IF l_ord_hdr_rec.tax_exempt_number IS NULL
1810 AND l_ord_hdr_rec.tax_exempt_flag = 'E'
1811 THEN
1812 l_tax_status := NULL;
1813 ELSE
1814 l_tax_status := l_ord_hdr_rec.tax_exempt_flag;
1815 END IF;
1816
1817 x_header_rec.authoring_org_id := l_ord_hdr_rec.org_id;
1818 x_header_rec.agreement_id := l_ord_hdr_rec.agreement_id;
1819 x_header_rec.cust_po_number := l_ord_hdr_rec.cust_po_number;
1820 x_header_rec.invoice_rule_id := l_ord_hdr_rec.invoicing_rule_id;
1821 x_header_rec.accounting_rule_id := l_ord_hdr_rec.accounting_rule_id;
1822 x_header_rec.order_hdr_id := p_hdr_id;
1823 x_header_rec.invoice_to_contact_id := l_ord_hdr_rec.invoice_to_contact_id;
1824 x_header_rec.currency := l_ord_hdr_rec.transactional_curr_code;
1825 x_header_rec.party_id := l_party_id;
1826 x_header_rec.class_code := NULL;
1827 x_header_rec.bill_to_id := l_ord_hdr_rec.invoice_to_org_id;
1828 x_header_rec.ship_to_id := l_ord_hdr_rec.ship_to_org_id;
1829 x_header_rec.price_list_id := l_ord_hdr_rec.price_list_id;
1830 x_header_rec.hdr_payment_term_id := l_ord_hdr_rec.payment_term_id;
1831 x_header_rec.hdr_tax_status_flag := l_tax_status;
1832 x_header_rec.hdr_tax_exemption_id := l_tax_id;
1833 x_header_rec.hdr_cvn_type := l_ord_hdr_rec.conversion_type_code;
1834 x_header_rec.hdr_cvn_rate := l_ord_hdr_rec.conversion_rate;
1835 x_header_rec.hdr_cvn_date := l_ord_hdr_rec.conversion_rate_date;
1836 x_header_rec.ship_to_contact_id := l_contact_id;
1837 x_header_rec.salesrep_id := l_ord_hdr_rec.salesrep_id;
1838 x_header_rec.ccr_number := l_ord_hdr_rec.credit_card_number;
1839 x_header_rec.ccr_exp_date := l_ord_hdr_rec.credit_card_expiration_date;
1840 --Added in R12 by rsu
1841 x_header_rec.tax_classification_code := l_ord_hdr_rec.tax_point_code;
1842 x_header_rec.exemption_certificate_number := l_ord_hdr_rec.tax_exempt_number;
1843 x_header_rec.exemption_reason_code := l_ord_hdr_rec.tax_exempt_reason_code;
1844
1845 EXCEPTION
1846 WHEN g_exception_halt_validation
1847 THEN
1848 x_return_status := l_return_status;
1849 okc_api.set_message (g_app_name,
1850 g_unexpected_error,
1851 g_sqlcode_token,
1852 SQLCODE,
1853 g_sqlerrm_token,
1854 SQLERRM
1855 );
1856 WHEN OTHERS
1857 THEN
1858 x_return_status := okc_api.g_ret_sts_unexp_error;
1859 okc_api.set_message (g_app_name,
1860 g_unexpected_error,
1861 g_sqlcode_token,
1862 SQLCODE,
1863 g_sqlerrm_token,
1864 SQLERRM
1865 );
1866
1867 IF fnd_log.level_unexpected >= fnd_log.g_current_runtime_level
1868 THEN
1869 fnd_log.STRING (fnd_log.level_unexpected,
1870 g_module_current
1871 || '.GET_CONTRACT_HEADER_INFO.UNEXPECTED',
1872 'sqlcode = ' || SQLCODE || ', sqlerrm = '
1873 || SQLERRM
1874 );
1875 END IF;
1876 END get_contract_header_info;
1877
1878 /*----------------------------------------------------------------------
1879
1880 PROCEDURE : GET_CONTRACT_LINE_INFO
1881 DESCRIPTION : This procedure is to get the details to create a warranty
1882 line for an item ordered from OM
1883 INPUT : Order Line id
1884 Customer product id
1885 Inventory item id
1886 OUTPUT : Line details in x_line_rec
1887 retun status 'S' if successful
1888
1889 ----------------------------------------------------------------------*/
1890 PROCEDURE get_contract_line_info (
1891 p_order_line_id IN NUMBER,
1892 p_cp_id IN NUMBER,
1893 p_product_item IN NUMBER,
1894 x_return_status OUT NOCOPY VARCHAR2,
1895 x_line_rec OUT NOCOPY line_rec_type
1896 )
1897 IS
1898 CURSOR l_ord_line_csr (l_line_id NUMBER)
1899 IS
1900 SELECT ol.org_id,
1901 ol.line_number,
1902 ol.sold_to_org_id,
1903 ol.ship_to_org_id,
1904 ol.invoice_to_org_id,
1905 ol.commitment_id,
1906 -- added in R12 by rsu
1907 ol.tax_exempt_number,
1908 ol.tax_exempt_reason_code,
1909 ol.tax_point_code,
1910 ol.tax_exempt_flag,
1911 ol.header_id
1912 --
1913 FROM oe_order_lines_all ol
1914 WHERE ol.line_id = l_line_id;
1915
1916 CURSOR l_segment_csr (p_org_id NUMBER)
1917 IS
1918 SELECT t.description NAME,
1919 b.concatenated_segments description
1920 FROM mtl_system_items_b_kfv b,
1921 mtl_system_items_tl t
1922 WHERE
1923 b.inventory_item_id = t.inventory_item_id
1924 AND b.organization_id = t.organization_id
1925 AND t.LANGUAGE = USERENV ('LANG')
1926 AND b.inventory_item_id = p_product_item
1927 AND b.organization_id = okc_context.get_okc_organization_id;
1928
1929 l_return_status VARCHAR2 (1) := okc_api.g_ret_sts_success;
1930 l_warranty_tbl war_tbl;
1931 l_ptr BINARY_INTEGER;
1932 l_ord_line_rec l_ord_line_csr%ROWTYPE;
1933 l_segment_rec l_segment_csr%ROWTYPE;
1934 BEGIN
1935 x_return_status := l_return_status;
1936
1937 OPEN l_ord_line_csr (p_order_line_id);
1938
1939 FETCH l_ord_line_csr
1940 INTO l_ord_line_rec;
1941
1942 IF l_ord_line_csr%NOTFOUND
1943 THEN
1944 CLOSE l_ord_line_csr;
1945
1946 IF fnd_log.level_error >= fnd_log.g_current_runtime_level
1947 THEN
1948 fnd_log.STRING (fnd_log.level_error,
1949 g_module_current
1950 || '.GET_CONTRACT_LINE_INFO.ERROR',
1951 'l_ord_line_csr not found for line id = '
1952 || p_order_line_id
1953 );
1954 END IF;
1955
1956 l_return_status := okc_api.g_ret_sts_error;
1957 okc_api.set_message (g_app_name,
1958 'OKS_ORD_LINE_DTLS_NOT_FOUND',
1959 'ORDER_LINE',
1960 p_order_line_id
1961 );
1962 RAISE g_exception_halt_validation;
1963 END IF;
1964
1965 CLOSE l_ord_line_csr;
1966
1967 OPEN l_segment_csr (l_ord_line_rec.org_id);
1968
1969 FETCH l_segment_csr
1970 INTO l_segment_rec;
1971
1972 IF l_segment_csr%NOTFOUND
1973 THEN
1974 CLOSE l_segment_csr;
1975
1976 IF fnd_log.level_error >= fnd_log.g_current_runtime_level
1977 THEN
1978 fnd_log.STRING (fnd_log.level_error,
1979 g_module_current
1980 || '.GET_CONTRACT_LINE_INFO.ERROR',
1981 'l_segment_csr not found for org id = '
1982 || l_ord_line_rec.org_id
1983 );
1984 END IF;
1985
1986 l_return_status := okc_api.g_ret_sts_error;
1987 okc_api.set_message (g_app_name,
1988 'OKS_ITEM_DTLS_NOT_FOUND',
1989 'PRODUCT_ITEM',
1990 p_product_item
1991 );
1992 RAISE g_exception_halt_validation;
1993 END IF;
1994
1995 CLOSE l_segment_csr;
1996
1997 x_line_rec.srv_segment1 := l_segment_rec.description;
1998 x_line_rec.srv_desc := l_segment_rec.NAME;
1999 -- X_Line_rec.k_line_NUMBER := FND_PROFILE.VALUE('PREFIX_CONTRACT_NUMBER')||l_ord_Line_rec.line_NUMBER||FND_PROFILE.VALUE('SUFFIX_CONTRACT_NUMBER');
2000 -- X_Line_rec.srv_segment1 := l_segment_rec.name;
2001 -- X_Line_rec.srv_id := l_Warranty_tbl(l_ptr).service_item_id;
2002 -- X_Line_rec.srv_desc := l_segment_rec.description;
2003 x_line_rec.ship_to_id := l_ord_line_rec.ship_to_org_id;
2004 x_line_rec.bill_to_id := l_ord_line_rec.invoice_to_org_id;
2005 x_line_rec.order_line_id := p_order_line_id;
2006 x_line_rec.customer_acct_id := l_ord_line_rec.sold_to_org_id;
2007
2008 -- X_line_rec.commitment_id := l_ord_line_rec.commitment_id;
2009
2010 --Added in R12 by rsu
2011 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
2012 THEN
2013 fnd_log.STRING (fnd_log.level_statement,
2014 g_module_current
2015 || '.GET_CONTRACT_LINE_INFO.order line',
2016 'After querying the line'
2017 );
2018 fnd_log.STRING (fnd_log.level_statement,
2019 g_module_current
2020 || '.GET_CONTRACT_LINE_INFO.order line',
2021 'l_ord_line_rec.tax_point_code: '
2022 || l_ord_line_rec.tax_point_code
2023 );
2024 fnd_log.STRING (fnd_log.level_statement,
2025 g_module_current
2026 || '.GET_CONTRACT_LINE_INFO.order line',
2027 'l_ord_line_rec.tax_exempt_reason_code: '
2028 || l_ord_line_rec.tax_exempt_reason_code
2029 );
2030 fnd_log.STRING (fnd_log.level_statement,
2031 g_module_current
2032 || '.GET_CONTRACT_LINE_INFO.order line',
2033 'l_ord_line_rec.tax_exempt_number: '
2034 || l_ord_line_rec.tax_exempt_number
2035 );
2036 fnd_log.STRING (fnd_log.level_statement,
2037 g_module_current
2038 || '.GET_CONTRACT_LINE_INFO.order line',
2039 'l_ord_line_rec.tax_exempt_flag: '
2040 || l_ord_line_rec.tax_exempt_flag
2041 );
2042 END IF;
2043
2044 x_line_rec.tax_classification_code := l_ord_line_rec.tax_point_code;
2045 x_line_rec.exemption_certificate_number :=
2046 l_ord_line_rec.tax_exempt_number;
2047 x_line_rec.exemption_reason_code :=
2048 l_ord_line_rec.tax_exempt_reason_code;
2049 x_line_rec.tax_status := l_ord_line_rec.tax_exempt_flag;
2050 --
2051 EXCEPTION
2052 WHEN g_exception_halt_validation
2053 THEN
2054 x_return_status := l_return_status;
2055 okc_api.set_message (g_app_name,
2056 g_unexpected_error,
2057 g_sqlcode_token,
2058 SQLCODE,
2059 g_sqlerrm_token,
2060 SQLERRM
2061 );
2062 WHEN OTHERS
2063 THEN
2064 x_return_status := okc_api.g_ret_sts_unexp_error;
2065 okc_api.set_message (g_app_name,
2066 g_unexpected_error,
2067 g_sqlcode_token,
2068 SQLCODE,
2069 g_sqlerrm_token,
2070 SQLERRM
2071 );
2072
2073 IF fnd_log.level_unexpected >= fnd_log.g_current_runtime_level
2074 THEN
2075 fnd_log.STRING (fnd_log.level_unexpected,
2076 g_module_current
2077 || '.GET_CONTRACT_LINE_INFO.UNEXPECTED',
2078 'sqlcode = ' || SQLCODE || ', sqlerrm = '
2079 || SQLERRM
2080 );
2081 END IF;
2082 END get_contract_line_info;
2083
2084 /*----------------------------------------------------------------------
2085
2086 PROCEDURE : GET_K_SERVICE_LINE
2087 DESCRIPTION : This procedure is to get the details to create a service
2088 line for an item ordered from OM
2089 INPUT : Order Line id
2090 Customer product id
2091 Shipped date
2092 Installation date
2093 Caller -IB
2094 OUTPUT : Line details in x_line_rec
2095 retun status 'S' if successful
2096
2097 ----------------------------------------------------------------------*/
2098 PROCEDURE get_k_service_line (
2099 p_order_line_id IN NUMBER,
2100 p_cp_id IN NUMBER,
2101 --new parameter added for extwar enhancment Vigandhi
2102 p_shipped_date IN DATE,
2103 --new parameter added -Vigandhi 04-jun-2002
2104 p_installation_date IN DATE,
2105 --new parameter added -Vigandhi 04-jun-2002
2106 p_caller IN VARCHAR2,
2107 x_order_error OUT NOCOPY VARCHAR2,
2108 x_return_status OUT NOCOPY VARCHAR2,
2109 x_line_rec OUT NOCOPY line_rec_type
2110 )
2111 IS
2112 --Bug#1696511
2113 l_start_date DATE;
2114 l_end_date DATE;
2115
2116 --end bug
2117 CURSOR l_ord_line_csr
2118 IS
2119 SELECT ol.inventory_item_id,
2120 ol.service_start_date,
2121 ol.service_end_date,
2122 ol.service_duration,
2123 ol.service_period,
2124 ol.org_id, ol.line_number,
2125 ol.invoice_to_org_id,
2126 ol.ship_to_org_id,
2127 ol.unit_selling_price,
2128 ol.unit_selling_percent,
2129 ol.fulfilled_quantity,
2130 ol.invoice_to_contact_id, --Bug#1696511
2131 ol.service_reference_type_code,
2132 ol.service_reference_line_id,
2133 --end bug
2134 --Ordered_Quantity
2135 ol.order_quantity_uom,
2136 --22-NOV-2005 mchoudha added for PPC
2137 pricing_quantity,
2138 pricing_quantity_uom, --End PPC
2139 ol.sold_to_org_id,
2140 ol.invoicing_rule_id,
2141 ol.accounting_rule_id,
2142 ol.commitment_id,
2143 ol.tax_value,
2144 ol.price_list_id,
2145 t.description NAME,
2146 b.concatenated_segments description,
2147 b.service_starting_delay,
2148 b.coverage_schedule_id coverage_template_id,
2149 ol.header_id,
2150 ol.tax_exempt_number, -- Bug#5008188 - vjramali
2151 ol.tax_exempt_reason_code,
2152 ol.tax_code, ol.tax_exempt_flag -- End Bug#5008188
2153 FROM oe_order_lines_all ol,
2154 mtl_system_items_b_kfv b,
2155 mtl_system_items_tl t
2156 WHERE ol.line_id = p_order_line_id
2157 AND b.inventory_item_id = ol.inventory_item_id
2158 AND b.inventory_item_id = t.inventory_item_id
2159 AND b.organization_id = t.organization_id
2160 AND t.LANGUAGE = USERENV ('LANG')
2161 AND b.organization_id = okc_context.get_okc_organization_id;
2162
2163 CURSOR get_csr_order_line_id (p_service_line_id NUMBER)
2164 IS
2165 SELECT actual_shipment_date
2166 FROM oe_order_lines_all
2167 WHERE line_id = p_service_line_id;
2168
2169 -- Extwarranty cascading
2170 CURSOR get_warr_dates_csr
2171 IS
2172 SELECT MAX (ol.end_date)
2173 FROM okc_k_items_v ot, okc_k_lines_v ol
2174 WHERE ot.object1_id1 = TO_CHAR (p_cp_id)
2175 AND ol.ID = ot.cle_id
2176 AND ol.lse_id = 18;
2177
2178 l_ord_line_rec l_ord_line_csr%ROWTYPE;
2179 l_return_status VARCHAR2 (1) := okc_api.g_ret_sts_success;
2180 l_inv_id NUMBER;
2181 l_war_edt DATE;
2182 l_curr_code VARCHAR2 (15);
2183 l_line_tax_status VARCHAR2 (40);
2184 BEGIN
2185 x_return_status := l_return_status;
2186
2187 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
2188 THEN
2189 fnd_log.STRING (fnd_log.level_statement,
2190 g_module_current || '.GET_K_SERVICE_LINE.Order line',
2191 ' *************************************************'
2192 );
2193 fnd_log.STRING (fnd_log.level_statement,
2194 g_module_current || '.GET_K_SERVICE_LINE.Order line',
2195 'Processing Order line id ' || p_order_line_id
2196 );
2197 fnd_log.STRING (fnd_log.level_statement,
2198 g_module_current || '.GET_K_SERVICE_LINE.Order line',
2199 ' '
2200 );
2201 END IF;
2202
2203 OPEN l_ord_line_csr;
2204
2205 FETCH l_ord_line_csr
2206 INTO l_ord_line_rec;
2207
2208 IF l_ord_line_csr%NOTFOUND
2209 THEN
2210 IF fnd_log.level_error >= fnd_log.g_current_runtime_level
2211 THEN
2212 fnd_log.STRING (fnd_log.level_error,
2213 g_module_current || '.GET_K_SERVICE_LINE.ERROR',
2214 'l_Ord_line_csr not found for Order Line = '
2215 || p_order_line_id
2216 );
2217 END IF;
2218
2219 CLOSE l_ord_line_csr;
2220
2221 l_return_status := okc_api.g_ret_sts_error;
2222 okc_api.set_message (g_app_name,
2223 'OKS_ORD_LINE_DTLS_NOT_FOUND',
2224 'ORDER_LINE',
2225 p_order_line_id
2226 );
2227
2228 --mmadhavi
2229 IF (p_caller = 'OC')
2230 THEN
2231 fnd_message.set_name (g_app_name, 'OKS_ORD_LINE_DTLS_NOT_FOUND');
2232 fnd_message.set_token (token => 'ORDER_LINE',
2233 VALUE => p_order_line_id
2234 );
2235 x_order_error := '#' || fnd_message.get_encoded || '#';
2236 END IF;
2237
2238 --mmadhavi
2239 RAISE g_exception_halt_validation;
2240 END IF;
2241
2242 CLOSE l_ord_line_csr;
2243
2244 IF l_ord_line_rec.service_end_date IS NULL
2245 AND l_ord_line_rec.service_period IS NULL
2246 AND l_ord_line_rec.service_duration IS NULL
2247 THEN
2248 IF fnd_log.level_error >= fnd_log.g_current_runtime_level
2249 THEN
2250 fnd_log.STRING (fnd_log.level_error,
2251 g_module_current || '.GET_K_SERVICE_LINE.ERROR',
2252 'End Date, Duration, Period Required '
2253 );
2254 END IF;
2255
2256 l_return_status := okc_api.g_ret_sts_error;
2257 x_return_status := l_return_status;
2258 okc_api.set_message (g_app_name, 'OKS_END_DT_DUR_REQUIRED');
2259
2260 --mmadhavi
2261 IF (p_caller = 'OC')
2262 THEN
2263 fnd_message.set_name (g_app_name, 'OKS_END_DT_DUR_REQUIRED');
2264 x_order_error := '#' || fnd_message.get_encoded || '#';
2265 END IF;
2266
2267 --mmadhavi
2268 RAISE g_exception_halt_validation;
2269 END IF;
2270
2271 l_inv_id := l_ord_line_rec.inventory_item_id;
2272 --Bug#1696511
2273 l_start_date := NULL;
2274 l_end_date := NULL;
2275
2276 IF l_ord_line_rec.service_reference_type_code IN
2277 ('ORDER', 'CUSTOMER_PRODUCT')
2278 THEN
2279 l_start_date := l_ord_line_rec.service_start_date;
2280 l_end_date := l_ord_line_rec.service_end_date;
2281
2282 IF l_end_date IS NULL
2283 THEN
2284 l_end_date :=
2285 okc_time_util_pub.get_enddate (l_start_date,
2286 l_ord_line_rec.service_period,
2287 l_ord_line_rec.service_duration
2288 );
2289 END IF;
2290
2291 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
2292 THEN
2293 fnd_log.STRING (fnd_log.level_statement,
2294 g_module_current
2295 || '.GET_K_SERVICE_LINE.Service Dates',
2296 'Start date = '
2297 || l_start_date
2298 || ',End date = '
2299 || l_end_date
2300 );
2301 END IF;
2302 END IF;
2303
2304 -- Extwarr cascading -Vigandhi
2305 -- If service start_date is null Ext warr contract will start from existing warr contract end date, but if no warranty contract
2306 -- is there ext-warr contract will start from installation date/shipped date of the product.
2307 IF l_start_date IS NULL
2308 THEN
2309 OPEN get_warr_dates_csr;
2310
2311 FETCH get_warr_dates_csr
2312 INTO l_war_edt;
2313
2314 --close get_warr_dates_csr;
2315
2316 --If get_warr_dates_csr%found then
2317 IF l_war_edt IS NOT NULL
2318 THEN
2319 l_start_date := TRUNC (l_war_edt) + 1;
2320 l_end_date :=
2321 okc_time_util_pub.get_enddate (l_start_date,
2322 l_ord_line_rec.service_period,
2323 l_ord_line_rec.service_duration
2324 );
2325
2326 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
2327 THEN
2328 fnd_log.STRING
2329 (fnd_log.level_statement,
2330 g_module_current
2331 || '.GET_K_SERVICE_LINE.Service Dates after cascading',
2332 'Start date = '
2333 || l_start_date
2334 || ',End date = '
2335 || l_end_date
2336 );
2337 END IF;
2338 ELSE
2339 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level
2340 THEN
2341 fnd_log.STRING (fnd_log.level_procedure,
2342 g_module_current
2343 || '.GET_K_SERVICE_LINE.Install ship Dates',
2344 'Installation_date= '
2345 || p_installation_date
2346 || ',Ship date = '
2347 || p_shipped_date
2348 );
2349 END IF;
2350
2351 IF p_installation_date IS NOT NULL
2352 THEN
2353 l_start_date := TRUNC (p_installation_date);
2354 ELSE
2355 l_start_date :=
2356 TRUNC (p_shipped_date)
2357 + NVL (l_ord_line_rec.service_starting_delay, 0);
2358 END IF;
2359
2360 l_end_date :=
2361 okc_time_util_pub.get_enddate (l_start_date,
2362 l_ord_line_rec.service_period,
2363 l_ord_line_rec.service_duration
2364 );
2365
2366 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
2367 THEN
2368 fnd_log.STRING
2369 (fnd_log.level_statement,
2370 g_module_current
2371 || '.GET_K_SERVICE_LINE.Service Dates after cascading- no warranty',
2372 'Start date = '
2373 || l_start_date
2374 || ',End date = '
2375 || l_end_date
2376 );
2377 END IF;
2378 END IF;
2379
2380 CLOSE get_warr_dates_csr;
2381 END IF;
2382
2383 IF l_start_date IS NULL
2384 THEN
2385 IF fnd_log.level_error >= fnd_log.g_current_runtime_level
2386 THEN
2387 fnd_log.STRING (fnd_log.level_error,
2388 g_module_current || '.GET_K_SERVICE_LINE.ERROR',
2389 ' Null Start Date'
2390 );
2391 END IF;
2392
2393 okc_api.set_message (g_app_name,
2394 'OKS_NULL_SDT',
2395 'LINE_ID',
2396 p_order_line_id
2397 );
2398 l_return_status := okc_api.g_ret_sts_error;
2399
2400 --mmadhavi
2401 IF (p_caller = 'OC')
2402 THEN
2403 fnd_message.set_name (g_app_name, 'OKS_NULL_SDT');
2404 fnd_message.set_token (token => 'LINE_ID',
2405 VALUE => p_order_line_id
2406 );
2407 x_order_error := '#' || fnd_message.get_encoded || '#';
2408 END IF;
2409
2410 --mmadhavi
2411 RAISE g_exception_halt_validation;
2412 END IF;
2413
2414 IF l_end_date IS NULL
2415 THEN
2416 IF fnd_log.level_error >= fnd_log.g_current_runtime_level
2417 THEN
2418 fnd_log.STRING (fnd_log.level_error,
2419 g_module_current || '.GET_K_SERVICE_LINE.ERROR',
2420 ' Null End Date'
2421 );
2422 END IF;
2423
2424 okc_api.set_message (g_app_name, 'OKS_END_DT_DUR_REQUIRED');
2425 l_return_status := okc_api.g_ret_sts_error;
2426
2427 --mmadhavi
2428 IF (p_caller = 'OC')
2429 THEN
2430 fnd_message.set_name (g_app_name, 'OKS_END_DT_DUR_REQUIRED');
2431 x_order_error := '#' || fnd_message.get_encoded || '#';
2432 END IF;
2433
2434 --mmadhavi
2435 RAISE g_exception_halt_validation;
2436 END IF;
2437
2438 x_line_rec.srv_segment1 := l_ord_line_rec.description;
2439 x_line_rec.srv_desc := l_ord_line_rec.NAME;
2440 x_line_rec.srv_sdt := TRUNC (l_start_date);
2441 x_line_rec.srv_edt := TRUNC (l_end_date);
2442 --end bug
2443 --X_Line_rec.Srv_sdt := l_Ord_Line_rec.Service_Start_Date + NVL(l_Ord_Line_rec.Service_starting_delay,0);
2444 --X_Line_rec.Srv_edt := l_Ord_Line_rec.Service_End_Date;
2445 --X_Line_rec.Srv_segment1 := l_Ord_Line_rec.NAME;
2446 --X_Line_rec.srv_Desc := l_Ord_line_rec.Description;
2447 x_line_rec.srv_id := l_ord_line_rec.inventory_item_id;
2448 x_line_rec.org_id := l_ord_line_rec.org_id;
2449 x_line_rec.order_line_id := p_order_line_id;
2450 x_line_rec.coverage_schd_id := l_ord_line_rec.coverage_template_id;
2451 x_line_rec.bill_to_id := l_ord_line_rec.invoice_to_org_id;
2452 x_line_rec.ship_to_id := l_ord_line_rec.ship_to_org_id;
2453 --Fix for bug 3452190
2454 x_line_rec.amount :=
2455 l_ord_line_rec.unit_selling_price * l_ord_line_rec.fulfilled_quantity;
2456 x_line_rec.unit_selling_price := l_ord_line_rec.unit_selling_price;
2457 x_line_rec.unit_selling_percent := l_ord_line_rec.unit_selling_percent;
2458 x_line_rec.customer_acct_id := l_ord_line_rec.sold_to_org_id;
2459 x_line_rec.invoice_to_contact_id := l_ord_line_rec.invoice_to_contact_id;
2460 x_line_rec.qty := l_ord_line_rec.fulfilled_quantity;
2461 x_line_rec.invoicing_rule_id := l_ord_line_rec.invoicing_rule_id;
2462 x_line_rec.accounting_rule_id := l_ord_line_rec.accounting_rule_id;
2463 x_line_rec.commitment_id := l_ord_line_rec.commitment_id;
2464 x_line_rec.tax_amount := l_ord_line_rec.tax_value;
2465 x_line_rec.ln_price_list_id := l_ord_line_rec.price_list_id;
2466 --22-NOV-2005 mchoudha added for PPC
2467 x_line_rec.pricing_quantity := l_ord_line_rec.pricing_quantity;
2468 x_line_rec.pricing_quantity_uom := l_ord_line_rec.pricing_quantity_uom;
2469 x_line_rec.order_quantity_uom := l_ord_line_rec.order_quantity_uom;
2470 --End PPC
2471
2472 -- Added as part of Bug fix 5008188 by vjramali
2473 -- Ensure that a tax_exempt_number is specified if the order line is tax exempted (Bug 5008188)
2474 IF l_ord_line_rec.tax_exempt_number IS NULL
2475 AND l_ord_line_rec.tax_exempt_flag = 'E'
2476 THEN
2477 l_line_tax_status := NULL;
2478 ELSE
2479 l_line_tax_status := l_ord_line_rec.tax_exempt_flag;
2480 END IF;
2481
2482 x_line_rec.tax_classification_code := l_ord_line_rec.tax_code;
2483 x_line_rec.exemption_certificate_number := l_ord_line_rec.tax_exempt_number;
2484 x_line_rec.exemption_reason_code := l_ord_line_rec.tax_exempt_reason_code;
2485 x_line_rec.tax_status := l_line_tax_status;
2486 --
2487
2488 EXCEPTION
2489 WHEN g_exception_halt_validation
2490 THEN
2491 x_return_status := l_return_status;
2492 okc_api.set_message (g_app_name,
2493 g_unexpected_error,
2494 g_sqlcode_token,
2495 SQLCODE,
2496 g_sqlerrm_token,
2497 SQLERRM
2498 );
2499 WHEN OTHERS
2500 THEN
2501 x_return_status := okc_api.g_ret_sts_unexp_error;
2502 okc_api.set_message (g_app_name,
2503 g_unexpected_error,
2504 g_sqlcode_token,
2505 SQLCODE,
2506 g_sqlerrm_token,
2507 SQLERRM
2508 );
2509
2510 IF fnd_log.level_unexpected >= fnd_log.g_current_runtime_level
2511 THEN
2512 fnd_log.STRING (fnd_log.level_unexpected,
2513 g_module_current
2514 || '.GET_K_SERVICE_LINE.UNEXPECTED',
2515 'sqlcode = ' || SQLCODE || ', sqlerrm = '
2516 || SQLERRM
2517 );
2518 END IF;
2519 END get_k_service_line;
2520
2521 /*----------------------------------------------------------------------
2522
2523 PROCEDURE : CHECK_IMMEDIATE_SERVICE
2524 DESCRIPTION : This procedure is to get all the contract line details
2525 for a services ordered for an instance existing in the
2526 same order
2527 INPUT : Instance id ,Order line id
2528 OUTPUT : service details in x_service_tbl
2529 delayed service status in X_Delayed_Service_Status
2530 retun status 'S' if successful
2531
2532 ----------------------------------------------------------------------*/
2533 PROCEDURE check_immediate_service (
2534 p_order_line_id IN NUMBER,
2535 x_service_tbl OUT NOCOPY service_tbl,
2536 x_immediate_service_status OUT NOCOPY VARCHAR2,
2537 x_return_status OUT NOCOPY VARCHAR2
2538 )
2539 IS
2540 l_ord_hdr_id NUMBER;
2541 l_ptr INTEGER;
2542 l_found BOOLEAN := TRUE;
2543 l_order_header_id NUMBER;
2544 l_return_status VARCHAR2 (1) := okc_api.g_ret_sts_success;
2545
2546 CURSOR l_line_csr (p_ord_hdr_id NUMBER)
2547 IS
2548 SELECT line_id, inventory_item_id, service_start_date,
2549 service_end_date, service_reference_line_id
2550 FROM oe_order_lines_all
2551 WHERE header_id = p_ord_hdr_id
2552 AND service_reference_line_id = p_order_line_id
2553 AND service_reference_type_code IS NOT NULL;
2554 BEGIN
2555 x_return_status := l_return_status;
2556 l_ord_hdr_id := get_order_header_id (p_order_line_id);
2557
2558 IF l_ord_hdr_id IS NULL
2559 THEN
2560 x_immediate_service_status := 'N';
2561 l_return_status := okc_api.g_ret_sts_error;
2562
2563 IF fnd_log.level_error >= fnd_log.g_current_runtime_level
2564 THEN
2565 fnd_log.STRING (fnd_log.level_error,
2566 g_module_current
2567 || '.CHECK_IMMEDIATE_SERVICE.ERROR',
2568 'Order Header Id NULL for Order line Id '
2569 || p_order_line_id
2570 );
2571 END IF;
2572
2573 okc_api.set_message (g_app_name,
2574 'OKS_INVD_ORD_LINE_ID',
2575 'LINE_ID',
2576 p_order_line_id
2577 );
2578 RAISE g_exception_halt_validation;
2579 END IF;
2580
2581 l_ptr := 1;
2582
2583 FOR l_line_rec IN l_line_csr (l_ord_hdr_id)
2584 LOOP
2585 l_order_header_id :=
2586 get_order_header_id (l_line_rec.service_reference_line_id);
2587
2588 IF l_order_header_id = l_ord_hdr_id
2589 THEN
2590 l_found := TRUE;
2591 x_service_tbl (l_ptr).order_line_id := l_line_rec.line_id;
2592 x_service_tbl (l_ptr).service_item_id :=
2593 l_line_rec.inventory_item_id;
2594 x_service_tbl (l_ptr).order_header_id := l_ord_hdr_id;
2595 x_service_tbl (l_ptr).start_date := l_line_rec.service_start_date;
2596 x_service_tbl (l_ptr).end_date := l_line_rec.service_end_date;
2597 END IF;
2598
2599 l_ptr := l_ptr + 1;
2600 END LOOP;
2601
2602 IF l_found = TRUE
2603 THEN
2604 x_immediate_service_status := 'Y';
2605 ELSE
2606 x_immediate_service_status := 'N';
2607 END IF;
2608 EXCEPTION
2609 WHEN g_exception_halt_validation
2610 THEN
2611 x_return_status := l_return_status;
2612 WHEN OTHERS
2613 THEN
2614 x_return_status := okc_api.g_ret_sts_unexp_error;
2615 okc_api.set_message (g_app_name,
2616 g_unexpected_error,
2617 g_sqlcode_token,
2618 SQLCODE,
2619 g_sqlerrm_token,
2620 SQLERRM
2621 );
2622
2623 IF fnd_log.level_unexpected >= fnd_log.g_current_runtime_level
2624 THEN
2625 fnd_log.STRING (fnd_log.level_unexpected,
2626 g_module_current
2627 || '.CHECK_IMMEDIATE_SERVICE.UNEXPECTED',
2628 'sqlcode = ' || SQLCODE || ', sqlerrm = '
2629 || SQLERRM
2630 );
2631 END IF;
2632 END check_immediate_service;
2633
2634 /*----------------------------------------------------------------------
2635
2636 PROCEDURE : CHECK_DELAYED_SERVICE
2637 DESCRIPTION : This procedure is to get all the contract line details
2638 for services ordered for an instance existing in different
2639 order
2640 INPUT : Instance id ,Order line id
2641 OUTPUT : service details in x_service_tbl
2642 delayed service status in X_Delayed_Service_Status
2643 retun status 'S' if successful
2644
2645 ----------------------------------------------------------------------*/
2646 PROCEDURE check_delayed_service (
2647 p_customer_product_id IN NUMBER,
2648 p_order_line_id IN NUMBER,
2649 x_service_tbl OUT NOCOPY service_tbl,
2650 x_delayed_service_status OUT NOCOPY VARCHAR2,
2651 x_return_status OUT NOCOPY VARCHAR2
2652 )
2653 IS
2654 l_ord_hdr_id NUMBER;
2655 l_ptr INTEGER;
2656 l_found BOOLEAN := TRUE;
2657 l_order_header_id NUMBER;
2658 l_return_status VARCHAR2 (1) := okc_api.g_ret_sts_success;
2659
2660 /* --Commented 17-jul-2003
2661 --Delayed services with ref type ORDER can be picked up
2662 --by order capture concurrent prog
2663 Cursor l_line_Csr(p_ord_hdr_id NUMBER) Is
2664 Select line_id
2665 ,Service_Start_Date
2666 ,Service_End_Date
2667 ,Inventory_Item_Id
2668 ,Service_Reference_Line_Id
2669 From OE_ORDER_LINES_ALL
2670 Where not HEADER_ID = p_ord_hdr_id
2671 And Service_reference_type_code = 'ORDER'
2672 And Service_Reference_line_id = p_order_line_id
2673 And Service_Duration is Not Null;
2674 */
2675 CURSOR l_line_serv_csr (p_ord_hdr_id NUMBER)
2676 IS
2677 SELECT line_id, service_start_date, service_end_date,
2678 inventory_item_id, service_reference_line_id
2679 FROM oe_order_lines_all
2680 WHERE header_id = p_ord_hdr_id
2681 AND service_reference_type_code = 'CUSTOMER_PRODUCT'
2682 AND service_reference_line_id = p_customer_product_id
2683 AND service_duration IS NOT NULL;
2684 BEGIN
2685 x_return_status := l_return_status;
2686 x_delayed_service_status := 'N';
2687 l_ord_hdr_id := get_order_header_id (p_order_line_id);
2688
2689 IF l_ord_hdr_id IS NULL
2690 THEN
2691 IF fnd_log.level_error >= fnd_log.g_current_runtime_level
2692 THEN
2693 fnd_log.STRING (fnd_log.level_error,
2694 g_module_current || '.CHECK_DELAYED_SERVICE.ERROR',
2695 'Invalid Order Line Id ' || p_order_line_id
2696 );
2697 END IF;
2698
2699 x_delayed_service_status := 'N';
2700 l_return_status := okc_api.g_ret_sts_error;
2701 okc_api.set_message (g_app_name,
2702 'OKS_INVD_ORD_LINE_ID',
2703 'LINE_ID',
2704 p_order_line_id
2705 );
2706 RAISE g_exception_halt_validation;
2707 END IF;
2708
2709 /* --commented 17-jul-2003
2710 For L_line_rec in L_Line_Csr(l_ord_hdr_id)
2711 Loop
2712 l_order_header_id := get_order_header_id(l_line_rec.Service_reference_line_id);
2713 If l_order_header_id = l_ord_hdr_id then
2714 X_Delayed_Service_Status := 'Y';
2715 X_Service_Tbl(l_ptr).order_Line_id := L_line_rec.line_id;
2716 X_Service_Tbl(l_ptr).Srv_ref_line_id := l_line_rec.Service_reference_line_id;
2717 X_Service_Tbl(l_ptr).Service_item_id := L_line_rec.Inventory_item_id;
2718 X_Service_Tbl(l_ptr).order_header_id := l_ord_hdr_id;
2719 X_Service_Tbl(l_ptr).start_date := l_line_rec.Service_start_date;
2720 X_Service_Tbl(l_ptr).end_date := l_line_rec.Service_End_date;
2721 End If;
2722 l_ptr := l_ptr + 1;
2723 End Loop;
2724 If l_ptr > 1 Then
2725 l_ptr := l_ptr + 1;
2726 Else
2727 l_ptr := 1;
2728 End If;
2729 */
2730 l_ptr := 1;
2731
2732 FOR l_line_serv_rec IN l_line_serv_csr (l_ord_hdr_id)
2733 LOOP
2734 IF l_line_serv_rec.service_reference_line_id = p_customer_product_id
2735 THEN
2736 x_delayed_service_status := 'Y';
2737 x_service_tbl (l_ptr).order_line_id := l_line_serv_rec.line_id;
2738 x_service_tbl (l_ptr).service_item_id :=
2739 l_line_serv_rec.inventory_item_id;
2740 x_service_tbl (l_ptr).order_header_id := l_ord_hdr_id;
2741 x_service_tbl (l_ptr).start_date :=
2742 l_line_serv_rec.service_start_date;
2743 x_service_tbl (l_ptr).end_date :=
2744 l_line_serv_rec.service_end_date;
2745 END IF;
2746
2747 -- to be discussed about the delayed service
2748 l_ptr := l_ptr + 1;
2749 END LOOP;
2750 EXCEPTION
2751 WHEN g_exception_halt_validation
2752 THEN
2753 x_return_status := l_return_status;
2754 WHEN OTHERS
2755 THEN
2756 x_return_status := okc_api.g_ret_sts_unexp_error;
2757
2758 IF fnd_log.level_unexpected >= fnd_log.g_current_runtime_level
2759 THEN
2760 fnd_log.STRING (fnd_log.level_unexpected,
2761 g_module_current
2762 || '.CHECK_DELAYED_SERVICE.UNEXPECTED',
2763 'sqlcode = ' || SQLCODE || ', sqlerrm = '
2764 || SQLERRM
2765 );
2766 END IF;
2767
2768 okc_api.set_message (g_app_name,
2769 g_unexpected_error,
2770 g_sqlcode_token,
2771 SQLCODE,
2772 g_sqlerrm_token,
2773 SQLERRM
2774 );
2775 END check_delayed_service;
2776
2777 /*----------------------------------------------------------------------
2778
2779 PROCEDURE : GET_CONTRACT_DETAILS
2780 DESCRIPTION : This procedure is to get all the contract/covered line
2781 information covering a particular instance
2782 INPUT : Instance id ,Transaction type ,Transaction date
2783 OUTPUT : contract details in x_contract_tbl
2784 sales credit details in x_sales_credit_tbl_hdr
2785 and x_sales_credit_tbl_line
2786 retun status 'S' if successful
2787
2788 ----------------------------------------------------------------------*/
2789 PROCEDURE get_contract_details (
2790 p_id IN VARCHAR2,
2791 p_type IN VARCHAR2,
2792 p_date IN DATE,
2793 p_trxn_type IN VARCHAR2,
2794 x_available_yn OUT NOCOPY VARCHAR2,
2795 x_contract_tbl OUT NOCOPY contract_tbl_type,
2796 x_sales_credit_tbl_hdr OUT NOCOPY oks_extwarprgm_pvt.salescredit_tbl,
2797 --mmadhavi 4174921
2798 x_sales_credit_tbl_line OUT NOCOPY oks_extwarprgm_pvt.salescredit_tbl,
2799 x_return_status OUT NOCOPY VARCHAR2
2800 )
2801 IS
2802 CURSOR get_hdr_csr (p_code VARCHAR2)
2803 IS
2804 SELECT ki.ID, ki.number_of_items, ki.dnz_chr_id, ki.cle_id,
2805 kl.sts_code, kl.start_date, kl.end_date, kl.lse_id,
2806 kl.date_terminated, kl.upg_orig_system_ref,
2807 kl.upg_orig_system_ref_id, kl.price_unit,
2808 kl.price_negotiated, kl.NAME, kl.item_description,
2809 kl.line_renewal_type_code, kh.start_date hdr_sdt,
2810 kh.end_date hdr_edt, kh.sts_code hdr_sts, kh.price_list_id,
2811 kh.payment_term_id, kh.inv_rule_id, ks.acct_rule_id,
2812 kh.inv_organization_id, ks.payment_type, ks.inv_trx_type,
2813 ks.ar_interface_yn, ks.hold_billing, ks.summary_trx_yn,
2814 kh.authoring_org_id, kh.contract_number, kh.cust_po_number,
2815 kh.currency_code, kh.conversion_type, kh.conversion_rate,
2816 kh.conversion_rate_date, kh.conversion_euro_rate,
2817 kh.scs_code, okl.tax_amount -- bug 3736860
2818 ,
2819 party.object1_id1 party_id
2820 FROM okc_k_items_v ki,
2821 okc_k_headers_b kh,
2822 oks_k_headers_b ks,
2823 okc_k_lines_v kl,
2824 oks_k_lines_b okl,
2825 okc_statuses_v st,
2826 okc_k_party_roles_b party
2827 WHERE ki.object1_id1 = p_id
2828 AND ki.jtot_object1_code = p_code
2829 AND ki.dnz_chr_id = kh.ID
2830 AND ks.chr_id(+) = kh.ID -- Vigandhi 06-jan-2004
2831 AND kh.scs_code IN ('WARRANTY', 'SERVICE', 'SUBSCRIPTION')
2832 -- support to subscription category contracts
2833 AND ki.cle_id = kl.ID
2834 AND okl.cle_id(+) = kl.ID
2835 AND kl.sts_code = st.code
2836 AND st.ste_code NOT IN ('TERMINATED', 'CANCELLED')
2837 AND kl.date_terminated IS NULL
2838 AND kh.template_yn = 'N'
2839 -- And PARTY.chr_id = KH.Id
2840 AND party.dnz_chr_id = kh.ID -- vigandhi 16-mar-05
2841 AND party.chr_id IS NOT NULL
2842 -- Added for performance issue 4223824
2843 AND party.cle_id IS NULL
2844 AND party.rle_code IN ('CUSTOMER', 'SUBSCRIBER')
2845 AND party.jtot_object1_code = 'OKX_PARTY';
2846
2847 CURSOR get_serv_id (p_cleid NUMBER)
2848 IS
2849 SELECT object1_id1
2850 FROM okc_k_items_v
2851 WHERE cle_id = p_cleid
2852 AND jtot_object1_code IN (g_jtf_warr, g_jtf_extwar, g_jtf_usage);
2853
2854 --***
2855 CURSOR get_line_det_csr (p_hdr_id NUMBER, p_line_id NUMBER)
2856 IS
2857 SELECT ol.ID, ol.cle_id, ol.start_date, ol.end_date, ol.NAME,
2858 ol.item_description, ol.price_negotiated, ol.currency_code,
2859 ol.line_number, ol.lse_id, ol.inv_rule_id,
2860 ol.bill_to_site_use_id, ol.ship_to_site_use_id,
2861 ol.cust_acct_id
2862 -- ,OL.unit_price
2863 --,OL.cle_id_renewed
2864 , ol.sts_code, oh.contract_number_modifier, kl.acct_rule_id,
2865 kl.tax_code --Bug#4121175
2866 FROM okc_k_lines_v ol, oks_k_lines_b kl, okc_k_headers_b oh
2867 WHERE ol.dnz_chr_id = p_hdr_id
2868 AND oh.ID = ol.dnz_chr_id
2869 AND kl.cle_id(+) = ol.ID -- Vigandhi 06-Jan-2004
2870 AND oh.scs_code IN ('WARRANTY', 'SERVICE', 'SUBSCRIPTION')
2871 -- support to subscription category contracts
2872 AND ol.ID IN (SELECT okl.cle_id
2873 FROM okc_k_lines_v okl
2874 WHERE okl.ID = p_line_id AND okl.cle_id IS NOT NULL);
2875
2876 /*Cursor l_serv_Csr(p_serv_id NUMBER) is
2877 Select COVERAGE_SCHEDULE_ID coverage_template_id
2878 From MTL_SYSTEM_ITEMS_B_KFV -- OKX_SYSTEM_ITEMS_V
2879 WHere INVENTORY_ITEM_ID = p_serv_id;
2880 */
2881 CURSOR l_serv_csr (p_serv_id NUMBER)
2882 IS
2883 SELECT t.description NAME, b.concatenated_segments description,
2884 b.coverage_schedule_id coverage_template_id
2885 FROM mtl_system_items_b_kfv b, mtl_system_items_tl t
2886 WHERE b.inventory_item_id = t.inventory_item_id
2887 AND b.organization_id = t.organization_id
2888 AND t.LANGUAGE = USERENV ('LANG')
2889 AND b.inventory_item_id = p_serv_id
2890 AND ROWNUM < 2;
2891
2892 -- added cursor for sales_credit -govind
2893 CURSOR l_sales_credit_csr (p_cle_id NUMBER)
2894 IS
2895 SELECT ctc_id, sales_credit_type_id1, PERCENT, sales_group_id
2896 FROM oks_k_sales_credits_v
2897 WHERE cle_id = p_cle_id;
2898
2899 CURSOR l_rel_csr (
2900 p_line_id NUMBER,
2901 p_hdr_id NUMBER,
2902 p_object_code VARCHAR2
2903 )
2904 IS
2905 SELECT object1_id1
2906 FROM okc_k_rel_objs_v
2907 WHERE cle_id = p_line_id
2908 AND chr_id = p_hdr_id
2909 AND jtot_object1_code = p_object_code;
2910
2911 CURSOR l_access_csr (p_hdr_id NUMBER)
2912 IS
2913 SELECT resource_id, GROUP_ID, access_level
2914 FROM okc_k_accesses_v
2915 WHERE chr_id = p_hdr_id;
2916
2917 --mmadhavi bug 4174921
2918 CURSOR l_sales_credit_hdr_csr (p_chr_id NUMBER)
2919 IS
2920 SELECT ctc_id, sales_credit_type_id1, PERCENT, sales_group_id
2921 FROM oks_k_sales_credits_v
2922 WHERE chr_id = p_chr_id AND cle_id IS NULL;
2923
2924 --mmadhavi bug 4174921
2925 p_line_id NUMBER;
2926 l_ptr NUMBER;
2927 l_found VARCHAR2 (5);
2928 p_code VARCHAR2 (40);
2929 l_cust_account VARCHAR2 (40);
2930 l_serv_id VARCHAR2 (40);
2931 l_unit_price NUMBER;
2932 l_serv_amount NUMBER;
2933 l_coverage_tempid NUMBER;
2934 l_chrid NUMBER;
2935 l_return_status VARCHAR2 (1) := okc_api.g_ret_sts_success;
2936 warranty_flag VARCHAR2 (1);
2937 l_id NUMBER;
2938 l_org_id NUMBER;
2939 l_contract_number VARCHAR2 (120);
2940 l_sc_ctr NUMBER;
2941 l_flag VARCHAR2 (10);
2942 l_start_delay NUMBER;
2943 l_rel_id NUMBER;
2944 l_cust_ponum VARCHAR2 (50); --07-May-2003
2945 l_hdr_currency VARCHAR2 (15); --07-May-2003
2946 l_ordhdr_id VARCHAR2 (40); --07-May-2003
2947 l_sts_code VARCHAR2 (30); --17-jul-2003
2948 l_ste_code VARCHAR2 (30); --17-jul-2003
2949 l_resource_id NUMBER;
2950 l_group_id NUMBER;
2951 l_access_level VARCHAR2 (3);
2952 l_service_name VARCHAR2 (240);
2953 l_service_description VARCHAR2 (240);
2954 l_sc_hdr_ctr NUMBER;
2955 BEGIN
2956 x_return_status := l_return_status;
2957
2958 IF UPPER (p_type) = 'P'
2959 THEN
2960 p_code := g_jtf_party;
2961 ELSIF UPPER (p_type) = 'S'
2962 THEN
2963 p_code := g_jtf_covlvl;
2964 ELSIF UPPER (p_type) = 'C'
2965 THEN
2966 p_code := g_jtf_custacct;
2967 ELSIF UPPER (p_type) = 'CP'
2968 THEN
2969 p_code := g_jtf_cusprod;
2970 END IF;
2971
2972 l_ptr := 1;
2973
2974 FOR l_chr_rec IN get_hdr_csr (p_code)
2975 LOOP
2976 l_flag := 'F';
2977 oks_extwarprgm_pvt.get_sts_code (NULL,
2978 l_chr_rec.sts_code,
2979 l_ste_code,
2980 l_sts_code
2981 );
2982
2983 IF p_trxn_type <> 'IDC'
2984 THEN
2985 IF l_chr_rec.lse_id IN (9, 18, 25) AND l_ste_code <> 'EXPIRED'
2986 THEN
2987 IF ( TRUNC (p_date) <=
2988 TRUNC (NVL (l_chr_rec.date_terminated,
2989 l_chr_rec.end_date
2990 )
2991 )
2992 AND TRUNC (p_date) >= TRUNC (l_chr_rec.start_date)
2993 )
2994 OR (TRUNC (p_date) <= TRUNC (l_chr_rec.start_date))
2995 THEN
2996 l_flag := 'T';
2997 END IF;
2998 END IF;
2999 ELSE
3000 IF l_chr_rec.lse_id = 18
3001 THEN
3002 l_flag := 'T';
3003 END IF;
3004 END IF;
3005
3006 IF l_flag = 'T'
3007 THEN
3008 l_found := 'T';
3009 l_ordhdr_id := NULL;
3010
3011 OPEN l_rel_csr (NULL, l_chr_rec.dnz_chr_id, 'OKX_ORDERHEAD');
3012
3013 FETCH l_rel_csr
3014 INTO l_ordhdr_id;
3015
3016 CLOSE l_rel_csr;
3017
3018 l_group_id := NULL;
3019 l_resource_id := NULL;
3020 l_access_level := NULL;
3021
3022 OPEN l_access_csr (l_chr_rec.dnz_chr_id);
3023
3024 FETCH l_access_csr
3025 INTO l_resource_id, l_group_id, l_access_level;
3026
3027 CLOSE l_access_csr;
3028
3029 --mmadhavi bug 4174921
3030 l_sc_hdr_ctr := 0;
3031
3032 FOR l_sales_credit_hdr_rec IN
3033 l_sales_credit_hdr_csr (l_chr_rec.dnz_chr_id)
3034 LOOP
3035 l_sc_hdr_ctr := l_sc_hdr_ctr + 1;
3036 x_sales_credit_tbl_hdr (l_sc_hdr_ctr).ctc_id :=
3037 l_sales_credit_hdr_rec.ctc_id;
3038 x_sales_credit_tbl_hdr (l_sc_hdr_ctr).sales_credit_type_id :=
3039 l_sales_credit_hdr_rec.sales_credit_type_id1;
3040 x_sales_credit_tbl_hdr (l_sc_hdr_ctr).PERCENT :=
3041 l_sales_credit_hdr_rec.PERCENT;
3042 x_sales_credit_tbl_hdr (l_sc_hdr_ctr).sales_group_id :=
3043 l_sales_credit_hdr_rec.sales_group_id;
3044 END LOOP; -- For l_sales_credit_rec IN l_sales_credit_csr
3045
3046 --mmadhavi bug 4174921
3047 FOR l_get_line_rec IN get_line_det_csr (l_chr_rec.dnz_chr_id,
3048 l_chr_rec.cle_id
3049 )
3050 LOOP
3051 l_serv_id := NULL;
3052
3053 OPEN get_serv_id (l_get_line_rec.ID);
3054
3055 FETCH get_serv_id
3056 INTO l_serv_id;
3057
3058 CLOSE get_serv_id;
3059
3060 x_contract_tbl (l_ptr).service_inventory_id := l_serv_id;
3061 -- added sales credit loop -govind
3062 l_sc_ctr := 0;
3063
3064 FOR l_sales_credit_rec IN
3065 l_sales_credit_csr (l_get_line_rec.ID)
3066 LOOP
3067 l_sc_ctr := l_sc_ctr + 1;
3068 x_sales_credit_tbl_line (l_sc_ctr).ctc_id :=
3069 l_sales_credit_rec.ctc_id;
3070 x_sales_credit_tbl_line (l_sc_ctr).sales_credit_type_id :=
3071 l_sales_credit_rec.sales_credit_type_id1;
3072 x_sales_credit_tbl_line (l_sc_ctr).PERCENT :=
3073 l_sales_credit_rec.PERCENT;
3074 x_sales_credit_tbl_line (l_sc_ctr).sales_group_id :=
3075 l_sales_credit_rec.sales_group_id;
3076 END LOOP; -- For l_sales_credit_rec IN l_sales_credit_csr
3077
3078 IF l_get_line_rec.lse_id = 19
3079 THEN
3080 x_contract_tbl (l_ptr).warranty_flag := 'E';
3081 --x_contract_tbl(l_ptr).start_delay := 0;
3082
3083 --added Elsif for service -govind
3084 ELSIF l_get_line_rec.lse_id = 1
3085 AND l_chr_rec.scs_code = 'SERVICE'
3086 THEN
3087 x_contract_tbl (l_ptr).warranty_flag := 'S';
3088 --x_contract_tbl(l_ptr).start_delay := 0;
3089 ELSIF l_get_line_rec.lse_id = 14
3090 THEN
3091 x_contract_tbl (l_ptr).warranty_flag := 'W';
3092 --x_contract_tbl(l_ptr).start_delay := NVL(l_start_delay,0);
3093
3094 -- support to subscription category contracts
3095 ELSIF l_get_line_rec.lse_id = 1
3096 AND l_chr_rec.scs_code = 'SUBSCRIPTION'
3097 THEN
3098 x_contract_tbl (l_ptr).warranty_flag := 'SU';
3099 END IF;
3100
3101 l_coverage_tempid := NULL;
3102
3103 OPEN l_serv_csr (x_contract_tbl (l_ptr).service_inventory_id);
3104
3105 FETCH l_serv_csr
3106 INTO l_service_name, l_service_description, l_coverage_tempid;
3107
3108 CLOSE l_serv_csr;
3109
3110 x_contract_tbl (l_ptr).service_cov_id := l_coverage_tempid;
3111 x_contract_tbl (l_ptr).service_name := l_service_description;
3112 x_contract_tbl (l_ptr).service_description := l_service_name;
3113 l_rel_id := NULL;
3114
3115 OPEN l_rel_csr (l_chr_rec.cle_id,
3116 l_chr_rec.dnz_chr_id,
3117 'OKX_ORDERHEAD'
3118 );
3119
3120 FETCH l_rel_csr
3121 INTO l_rel_id;
3122
3123 CLOSE l_rel_csr;
3124
3125 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
3126 THEN
3127 fnd_log.STRING (fnd_log.level_statement,
3128 g_module_current
3129 || '.GET_CONTRACT_DETAILS.After l_rel_csr',
3130 'Service Order line id = ' || l_rel_id
3131 );
3132 END IF;
3133
3134 IF l_get_line_rec.contract_number_modifier IS NOT NULL
3135 THEN
3136 x_contract_tbl (l_ptr).cle_id_renewed :=
3137 l_get_line_rec.contract_number_modifier;
3138 ELSE
3139 IF l_rel_id IS NOT NULL
3140 THEN
3141 x_contract_tbl (l_ptr).cle_id_renewed := NULL;
3142 ELSE
3143 x_contract_tbl (l_ptr).cle_id_renewed := 'Y';
3144 END IF;
3145 END IF;
3146
3147 x_contract_tbl (l_ptr).cust_account :=
3148 l_get_line_rec.cust_acct_id;
3149 x_contract_tbl (l_ptr).service_unit_price :=
3150 l_chr_rec.price_unit;
3151 x_contract_tbl (l_ptr).service_amount :=
3152 l_chr_rec.price_negotiated;
3153 -- bug 3736860
3154 x_contract_tbl (l_ptr).service_tax_amount :=
3155 l_chr_rec.tax_amount;
3156 -- bug 3736860
3157 -- bug 4121175
3158 x_contract_tbl (l_ptr).tax_code := l_get_line_rec.tax_code;
3159 -- bug 4121175
3160 x_contract_tbl (l_ptr).service_order_line_id := l_rel_id;
3161 x_contract_tbl (l_ptr).hdr_id := l_chr_rec.dnz_chr_id;
3162 x_contract_tbl (l_ptr).hdr_sdt := l_chr_rec.hdr_sdt;
3163 x_contract_tbl (l_ptr).hdr_edt := l_chr_rec.hdr_edt;
3164 x_contract_tbl (l_ptr).hdr_sts := l_chr_rec.hdr_sts;
3165 x_contract_tbl (l_ptr).hdr_org_id := l_chr_rec.authoring_org_id;
3166 x_contract_tbl (l_ptr).contract_number :=
3167 l_chr_rec.contract_number;
3168 x_contract_tbl (l_ptr).cust_po_number :=
3169 l_chr_rec.cust_po_number;
3170 x_contract_tbl (l_ptr).header_currency :=
3171 l_chr_rec.currency_code;
3172 x_contract_tbl (l_ptr).ord_hdr_id := l_ordhdr_id;
3173 x_contract_tbl (l_ptr).k_item_id := l_chr_rec.ID;
3174 x_contract_tbl (l_ptr).service_line_number :=
3175 l_get_line_rec.line_number;
3176 x_contract_tbl (l_ptr).cp_qty := l_chr_rec.number_of_items;
3177 x_contract_tbl (l_ptr).object_line_id := l_chr_rec.cle_id;
3178 x_contract_tbl (l_ptr).service_line_id := l_get_line_rec.ID;
3179 x_contract_tbl (l_ptr).service_sdt := l_get_line_rec.start_date;
3180 x_contract_tbl (l_ptr).service_edt := l_get_line_rec.end_date;
3181 x_contract_tbl (l_ptr).service_currency :=
3182 l_get_line_rec.currency_code;
3183 --X_Contract_tbl(l_ptr).Service_Bill_2_id := Get_rules(l_get_line_rec.id,'BTO',G_JTF_Billto);
3184 --X_Contract_tbl(l_ptr).Service_Ship_2_id := Get_rules(l_get_line_rec.id,'STO',G_JTF_Shipto);
3185 x_contract_tbl (l_ptr).service_bill_2_id :=
3186 l_get_line_rec.bill_to_site_use_id;
3187 x_contract_tbl (l_ptr).service_ship_2_id :=
3188 l_get_line_rec.ship_to_site_use_id;
3189 x_contract_tbl (l_ptr).service_line_number :=
3190 l_get_line_rec.line_number;
3191 --X_Contract_tbl(l_ptr).Invoice_rule_id := l_Get_Line_rec.Object1_id1;
3192 x_contract_tbl (l_ptr).invoice_rule_id :=
3193 l_get_line_rec.inv_rule_id;
3194 x_contract_tbl (l_ptr).accounting_rule_id :=
3195 l_get_line_rec.acct_rule_id;
3196 x_contract_tbl (l_ptr).price_list_id := l_chr_rec.price_list_id;
3197 x_contract_tbl (l_ptr).payment_term_id :=
3198 l_chr_rec.payment_term_id;
3199 x_contract_tbl (l_ptr).hdr_acct_rule_id :=
3200 l_chr_rec.acct_rule_id;
3201 x_contract_tbl (l_ptr).hdr_inv_rule_id := l_chr_rec.inv_rule_id;
3202 x_contract_tbl (l_ptr).ar_interface_yn :=
3203 l_chr_rec.ar_interface_yn;
3204 x_contract_tbl (l_ptr).hold_billing := l_chr_rec.hold_billing;
3205 x_contract_tbl (l_ptr).summary_trx_yn :=
3206 l_chr_rec.summary_trx_yn;
3207 x_contract_tbl (l_ptr).inv_trx_type := l_chr_rec.inv_trx_type;
3208 x_contract_tbl (l_ptr).payment_type := l_chr_rec.payment_type;
3209 x_contract_tbl (l_ptr).organization_id :=
3210 l_chr_rec.inv_organization_id;
3211 x_contract_tbl (l_ptr).cvn_type := l_chr_rec.conversion_type;
3212 x_contract_tbl (l_ptr).cvn_rate := l_chr_rec.conversion_rate;
3213 x_contract_tbl (l_ptr).cvn_date :=
3214 l_chr_rec.conversion_rate_date;
3215 x_contract_tbl (l_ptr).cvn_euro_rate :=
3216 l_chr_rec.conversion_euro_rate;
3217 x_contract_tbl (l_ptr).resource_id := l_resource_id;
3218 x_contract_tbl (l_ptr).GROUP_ID := l_group_id;
3219 x_contract_tbl (l_ptr).access_level := l_access_level;
3220 x_contract_tbl (l_ptr).prod_name := l_chr_rec.NAME;
3221 x_contract_tbl (l_ptr).prod_description :=
3222 l_chr_rec.item_description;
3223 x_contract_tbl (l_ptr).cle_id_renewed :=
3224 l_get_line_rec.contract_number_modifier;
3225 x_contract_tbl (l_ptr).sts_code := l_get_line_rec.sts_code;
3226 x_contract_tbl (l_ptr).prod_sts_code := l_chr_rec.sts_code;
3227 x_contract_tbl (l_ptr).prod_sdt := l_chr_rec.start_date;
3228 x_contract_tbl (l_ptr).prod_edt := l_chr_rec.end_date;
3229 x_contract_tbl (l_ptr).prod_term_date :=
3230 l_chr_rec.date_terminated;
3231 --X_Contract_tbl(l_ptr).prod_line_renewal_type := Get_rules(l_chr_rec.id,'LRT',G_JTF_Billto);
3232 x_contract_tbl (l_ptr).prod_line_renewal_type :=
3233 l_chr_rec.line_renewal_type_code;
3234 x_contract_tbl (l_ptr).upg_orig_system_ref :=
3235 l_chr_rec.upg_orig_system_ref;
3236 x_contract_tbl (l_ptr).upg_orig_system_ref_id :=
3237 l_chr_rec.upg_orig_system_ref_id;
3238 x_contract_tbl (l_ptr).party_id := l_chr_rec.party_id;
3239 END LOOP;
3240
3241 -- For l_Get_Line_rec in Get_Line_det_csr (l_chr_rec.dnz_chr_id, l_chr_rec.cle_id)
3242 l_ptr := l_ptr + 1;
3243 END IF;
3244 END LOOP; -- For l_chr_rec in Get_hdr_csr(p_Code)
3245
3246 IF l_found = 'T'
3247 THEN
3248 x_available_yn := 'Y';
3249 ELSE
3250 x_available_yn := 'N';
3251 END IF;
3252 EXCEPTION
3253 WHEN g_exception_halt_validation
3254 THEN
3255 x_return_status := l_return_status;
3256 NULL;
3257 WHEN OTHERS
3258 THEN
3259 x_return_status := okc_api.g_ret_sts_unexp_error;
3260 okc_api.set_message (g_app_name,
3261 g_unexpected_error,
3262 g_sqlcode_token,
3263 SQLCODE,
3264 g_sqlerrm_token,
3265 SQLERRM
3266 );
3267
3268 IF fnd_log.level_unexpected >= fnd_log.g_current_runtime_level
3269 THEN
3270 fnd_log.STRING (fnd_log.level_unexpected,
3271 g_module_current
3272 || '.GET_CONTRACT_DETAILS.UNEXPECTED',
3273 'sqlcode = ' || SQLCODE || ', sqlerrm = '
3274 || SQLERRM
3275 );
3276 END IF;
3277 END get_contract_details;
3278
3279 -----------------------------------------------------------------------------------------------------------
3280 -----------------------------------------------------------------------------------------------------------
3281 -- CREATE_BILLING_SCHEDULE
3282 ----------------------------------------------------------------------------------------------------------
3283 ----------------------------------------------------------------------------------------------------------
3284 PROCEDURE create_billing_schedule (
3285 p_bill_start_date IN DATE,
3286 p_bill_end_date IN DATE,
3287 p_billing_frequency IN VARCHAR2,
3288 p_billing_method IN VARCHAR2,
3289 p_regular_offset_days IN NUMBER,
3290 p_first_bill_to_date IN DATE,
3291 p_first_inv_date IN DATE,
3292 x_return_status OUT NOCOPY VARCHAR2,
3293 x_billing_schedule_tbl OUT NOCOPY billing_schedule_tbl_type,
3294 p_cle_id IN NUMBER
3295 )
3296 IS
3297 l_ptr NUMBER;
3298 l_return_status VARCHAR2 (1) := okc_api.g_ret_sts_success;
3299
3300 CURSOR l_bill_csr
3301 IS
3302 SELECT date_billed_from, date_billed_to, creation_date
3303 FROM oks_bill_cont_lines_v
3304 WHERE cle_id = p_cle_id;
3305
3306 l_bill_rec l_bill_csr%ROWTYPE;
3307 x_bill_from_date DATE;
3308 l_fst_or_bill_start_date DATE;
3309
3310 CURSOR l_uom_csr (p_code VARCHAR2)
3311 IS
3312 SELECT tce_code, quantity
3313 FROM okc_time_code_units_v
3314 WHERE uom_code = p_code;
3315
3316 l_tce_code VARCHAR2 (10);
3317 l_qty NUMBER;
3318 BEGIN
3319 IF p_bill_start_date IS NULL OR p_bill_end_date IS NULL
3320 THEN
3321 l_return_status := okc_api.g_ret_sts_success;
3322 RAISE g_exception_halt_validation;
3323 END IF;
3324
3325 x_return_status := l_return_status;
3326 l_ptr := 1;
3327 x_billing_schedule_tbl (l_ptr).bill_from_date := NULL;
3328 x_billing_schedule_tbl (l_ptr).bill_to_date := NULL;
3329 x_billing_schedule_tbl (l_ptr).invoice_on_date := NULL;
3330
3331 IF p_billing_frequency IS NULL
3332 THEN
3333 x_billing_schedule_tbl (l_ptr).bill_from_date := p_bill_start_date;
3334 x_billing_schedule_tbl (l_ptr).bill_to_date := p_bill_end_date;
3335
3336 IF UPPER (p_billing_method) = 'ADVANCE'
3337 THEN
3338 x_billing_schedule_tbl (l_ptr).invoice_on_date :=
3339 NVL (p_first_inv_date, p_bill_start_date);
3340 ELSIF UPPER (p_billing_method) = 'ARREARS'
3341 THEN
3342 x_billing_schedule_tbl (l_ptr).invoice_on_date := NULL;
3343 END IF;
3344
3345 l_return_status := okc_api.g_ret_sts_success;
3346 RAISE g_exception_halt_validation;
3347 END IF;
3348
3349 /**
3350 If l_ptr = 1 and p_first_bill_to_date Is not Null and p_first_inv_date Is not Null Then
3351 **/
3352 IF l_ptr = 1 AND p_first_bill_to_date IS NOT NULL
3353 THEN
3354 x_billing_schedule_tbl (l_ptr).bill_from_date :=
3355 NVL (x_billing_schedule_tbl (l_ptr).bill_from_date,
3356 p_bill_start_date
3357 );
3358 x_billing_schedule_tbl (l_ptr).bill_to_date :=
3359 NVL (x_billing_schedule_tbl (l_ptr).bill_to_date,
3360 p_first_bill_to_date
3361 );
3362
3363 IF UPPER (p_billing_method) = 'ADVANCE'
3364 THEN
3365 l_fst_or_bill_start_date :=
3366 NVL (p_first_inv_date, p_bill_start_date);
3367 x_billing_schedule_tbl (l_ptr).invoice_on_date :=
3368 NVL (x_billing_schedule_tbl (l_ptr).invoice_on_date,
3369 l_fst_or_bill_start_date
3370 );
3371 ELSE
3372 x_billing_schedule_tbl (l_ptr).invoice_on_date := NULL;
3373 END IF;
3374
3375 x_billing_schedule_tbl (l_ptr).billed_flag := 'F';
3376
3377 IF x_billing_schedule_tbl (l_ptr).bill_to_date = p_bill_end_date
3378 THEN
3379 l_return_status := okc_api.g_ret_sts_success;
3380 RAISE g_exception_halt_validation;
3381 ELSE
3382 l_ptr := l_ptr + 1;
3383 END IF;
3384 END IF;
3385
3386 LOOP
3387 IF l_ptr > 1
3388 THEN
3389 x_billing_schedule_tbl (l_ptr).bill_from_date :=
3390 x_billing_schedule_tbl (l_ptr - 1).bill_to_date + 1;
3391 ELSE
3392 x_billing_schedule_tbl (l_ptr).bill_from_date :=
3393 p_bill_start_date;
3394 END IF;
3395
3396 l_tce_code := NULL;
3397 l_qty := NULL;
3398
3399 OPEN l_uom_csr (p_billing_frequency);
3400
3401 FETCH l_uom_csr
3402 INTO l_tce_code, l_qty;
3403
3404 CLOSE l_uom_csr;
3405
3406 IF UPPER (l_tce_code) = 'DAY' AND l_qty = 1
3407 THEN
3408 x_billing_schedule_tbl (l_ptr).bill_to_date :=
3409 LEAST ((x_billing_schedule_tbl (l_ptr).bill_from_date),
3410 p_bill_end_date
3411 );
3412 ELSIF UPPER (l_tce_code) = 'DAY' AND l_qty = 7
3413 THEN
3414 x_billing_schedule_tbl (l_ptr).bill_to_date :=
3415 LEAST ((x_billing_schedule_tbl (l_ptr).bill_from_date + 6),
3416 p_bill_end_date
3417 );
3418 ELSIF UPPER (l_tce_code) = 'MONTH' AND l_qty = 1
3419 THEN
3420 x_billing_schedule_tbl (l_ptr).bill_to_date :=
3421 LEAST
3422 (( ADD_MONTHS
3423 (x_billing_schedule_tbl (l_ptr).bill_from_date,
3424 1
3425 )
3426 - 1
3427 ),
3428 p_bill_end_date
3429 );
3430 ELSIF (UPPER (l_tce_code) = 'YEAR' AND l_qty = 1)
3431 OR (UPPER (l_tce_code) = 'MONTH' AND l_qty = 12)
3432 THEN
3433 x_billing_schedule_tbl (l_ptr).bill_to_date :=
3434 LEAST
3435 (( ADD_MONTHS
3436 (x_billing_schedule_tbl (l_ptr).bill_from_date,
3437 12
3438 )
3439 - 1
3440 ),
3441 p_bill_end_date
3442 );
3443 ELSIF UPPER (l_tce_code) = 'MONTH' AND l_qty = 3
3444 THEN
3445 x_billing_schedule_tbl (l_ptr).bill_to_date :=
3446 LEAST
3447 (( ADD_MONTHS
3448 (x_billing_schedule_tbl (l_ptr).bill_from_date,
3449 3
3450 )
3451 - 1
3452 ),
3453 p_bill_end_date
3454 );
3455 ELSE
3456 l_return_status := okc_api.g_ret_sts_unexp_error;
3457 okc_api.set_message (g_app_name, 'OKS_WRONG_BILLING_FREQUENCY');
3458 RAISE g_exception_halt_validation;
3459 END IF;
3460
3461 IF UPPER (p_billing_method) = 'ADVANCE'
3462 THEN
3463 x_billing_schedule_tbl (l_ptr).invoice_on_date :=
3464 x_billing_schedule_tbl (l_ptr).bill_from_date
3465 + p_regular_offset_days;
3466 ELSIF UPPER (p_billing_method) = 'ARREARS'
3467 THEN
3468 x_billing_schedule_tbl (l_ptr).invoice_on_date := NULL;
3469 ELSE
3470 l_return_status := okc_api.g_ret_sts_unexp_error;
3471 okc_api.set_message (g_app_name, 'OKS_WRONG_BILLING_METHOD');
3472 END IF;
3473
3474 x_billing_schedule_tbl (l_ptr).billed_flag := 'F';
3475 EXIT WHEN x_billing_schedule_tbl (l_ptr).bill_to_date =
3476 p_bill_end_date;
3477 l_ptr := l_ptr + 1;
3478 END LOOP;
3479 EXCEPTION
3480 WHEN g_exception_halt_validation
3481 THEN
3482 x_return_status := l_return_status;
3483 NULL;
3484 WHEN OTHERS
3485 THEN
3486 x_return_status := okc_api.g_ret_sts_unexp_error;
3487 okc_api.set_message (g_app_name,
3488 g_unexpected_error,
3489 g_sqlcode_token,
3490 SQLCODE,
3491 g_sqlerrm_token,
3492 SQLERRM
3493 );
3494 END create_billing_schedule;
3495
3496 /*----------------------------------------------------------------------
3497
3498 PROCEDURE : GET_WARRANTY_INFO
3499 DESCRIPTION : This procedure is to get the information regarding attached
3500 warranties to an instance defined in BOM
3501 INPUT : Org id
3502 Inventory item id
3503 Date
3504 OUTPUT : Warranty details in x_warranty_tbl
3505 retun status 'S' if successful
3506
3507 ----------------------------------------------------------------------*/
3508 PROCEDURE get_warranty_info (
3509 p_org_id IN NUMBER,
3510 p_prod_item_id IN NUMBER,
3511 p_date IN DATE DEFAULT SYSDATE,
3512 x_return_status OUT NOCOPY VARCHAR2,
3513 x_warranty_tbl OUT NOCOPY war_tbl
3514 )
3515 IS
3516 l_warranty_tbl war_item_id_tbl_type;
3517 l_ptr INTEGER;
3518 l_comm_bill_seq_id NUMBER;
3519 l_return_status VARCHAR2 (1) := okc_api.g_ret_sts_success;
3520 l_ship_date DATE;
3521 l_ship_flag VARCHAR2 (1);
3522 l_start_delay NUMBER;
3523
3524 CURSOR get_ship_csr
3525 IS
3526 SELECT shippable_item_flag, service_starting_delay
3527 FROM okx_system_items_v
3528 WHERE id1 = p_prod_item_id
3529 AND organization_id = okc_context.get_okc_organization_id;
3530 BEGIN
3531 okc_context.set_okc_org_context (p_org_id => p_org_id,
3532 p_organization_id => NULL
3533 );
3534 x_return_status := okc_api.g_ret_sts_success;
3535
3536 IF p_prod_item_id IS NULL
3537 THEN
3538 --or p_customer_product_id is NULL
3539 IF fnd_log.level_error >= fnd_log.g_current_runtime_level
3540 THEN
3541 fnd_log.STRING (fnd_log.level_error,
3542 g_module_current || '.GET_WARRANTY_INFO.ERROR',
3543 'Product Item Id Required'
3544 );
3545 END IF;
3546
3547 x_return_status := okc_api.g_ret_sts_error;
3548 okc_api.set_message (g_app_name, 'OKS_ORG_ID_INV_ID REQUIRED');
3549 RAISE g_exception_halt_validation;
3550 END IF;
3551
3552 get_war_item_id (p_inventory_item_id => p_prod_item_id,
3553 p_datec => p_date,
3554 x_return_status => l_return_status,
3555 x_war_item_tbl => l_warranty_tbl,
3556 x_common_bill_seq_id => l_comm_bill_seq_id
3557 );
3558
3559 IF (fnd_log.level_event >= fnd_log.g_current_runtime_level)
3560 THEN
3561 fnd_log.STRING (fnd_log.level_event,
3562 g_module_current
3563 || '.GET_WARRANTY_INFO.Internal_call.after',
3564 'Get_war_item_id(Return status = '
3565 || l_return_status
3566 || 'Count = '
3567 || l_warranty_tbl.COUNT
3568 || ')'
3569 );
3570 END IF;
3571
3572 IF l_warranty_tbl.COUNT = 0
3573 THEN
3574 RAISE g_exception_halt_validation;
3575 END IF;
3576
3577 IF NOT l_warranty_tbl.COUNT = 0
3578 THEN
3579 l_ptr := l_warranty_tbl.FIRST;
3580
3581 LOOP
3582 get_war_dur_per
3583 (p_prod_inv_item_id => p_prod_item_id,
3584 p_comm_bill_seq_id => l_comm_bill_seq_id,
3585 p_war_inv_item_id => l_warranty_tbl (l_ptr).war_item_id,
3586 x_duration => x_warranty_tbl (l_ptr).duration_quantity,
3587 x_uom_code => x_warranty_tbl (l_ptr).duration_period,
3588 x_cov_sch_id => x_warranty_tbl (l_ptr).coverage_schedule_id,
3589 p_war_date => p_date,
3590 x_return_status => l_return_status
3591 );
3592
3593 IF (fnd_log.level_event >= fnd_log.g_current_runtime_level)
3594 THEN
3595 fnd_log.STRING (fnd_log.level_event,
3596 g_module_current
3597 || '.GET_WARRANTY_INFO.Internal call.after',
3598 'Get_War_Dur_Per(Return status = '
3599 || l_return_status
3600 || ')'
3601 );
3602 END IF;
3603
3604 IF NOT l_return_status = okc_api.g_ret_sts_success
3605 THEN
3606 RAISE g_exception_halt_validation;
3607 END IF;
3608
3609 x_warranty_tbl (l_ptr).service_item_id :=
3610 l_warranty_tbl (l_ptr).war_item_id;
3611 x_warranty_tbl (l_ptr).warranty_start_date := p_date;
3612 x_warranty_tbl (l_ptr).warranty_end_date :=
3613 okc_time_util_pub.get_enddate
3614 (p_start_date => x_warranty_tbl (l_ptr).warranty_start_date,
3615 p_duration => x_warranty_tbl (l_ptr).duration_quantity,
3616 p_timeunit => x_warranty_tbl (l_ptr).duration_period
3617 );
3618 EXIT WHEN (l_ptr = l_warranty_tbl.LAST);
3619 l_ptr := l_warranty_tbl.NEXT (l_ptr);
3620 END LOOP;
3621 END IF;
3622 EXCEPTION
3623 WHEN g_exception_halt_validation
3624 THEN
3625 x_return_status := l_return_status;
3626 NULL;
3627 WHEN OTHERS
3628 THEN
3629 x_return_status := okc_api.g_ret_sts_unexp_error;
3630 okc_api.set_message (g_app_name,
3631 g_unexpected_error,
3632 g_sqlcode_token,
3633 SQLCODE,
3634 g_sqlerrm_token,
3635 SQLERRM
3636 );
3637
3638 IF fnd_log.level_unexpected >= fnd_log.g_current_runtime_level
3639 THEN
3640 fnd_log.STRING (fnd_log.level_unexpected,
3641 g_module_current
3642 || '.GET_WARRANTY_INFO.UNEXPECTED',
3643 'sqlcode = ' || SQLCODE || ', sqlerrm = '
3644 || SQLERRM
3645 );
3646 END IF;
3647 END get_warranty_info;
3648
3649 /*----------------------------------------------------------------------
3650
3651 PROCEDURE : GET_TRANSFER_DETAIL
3652 DESCRIPTION : This procedure is to get header, line and sub line details
3653 of the contracts covering the instance being transfered.
3654 INPUT : Instance id
3655 OUTPUT : header details in x_hdr_rec
3656 Line details in x_line_rec
3657 Covered lvl details in x_covd_rec
3658 retun status 'S' if successful
3659
3660 ----------------------------------------------------------------------*/
3661 PROCEDURE get_transfer_detail (
3662 p_cpid IN NUMBER,
3663 x_hdr_rec OUT NOCOPY oks_extwarprgm_pvt.k_header_rec_type,
3664 x_line_rec OUT NOCOPY oks_extwarprgm_pvt.k_line_service_rec_type,
3665 x_covd_rec OUT NOCOPY oks_extwarprgm_pvt.k_line_covered_level_rec_type,
3666 x_return_status OUT NOCOPY VARCHAR2
3667 )
3668 IS
3669 CURSOR cust_csr
3670 IS
3671 SELECT csi.last_oe_order_line_id original_order_line_id,
3672 csi.inventory_item_id, csi.quantity,
3673 csi.unit_of_measure uom_code, t.description NAME -- mtl.name
3674 ,
3675 b.concatenated_segments description --mtl.description
3676 ,
3677 b.coverage_schedule_id coverage_template_id
3678 -- mtl.coverage_template_id
3679 FROM csi_item_instances csi,
3680 mtl_system_items_b_kfv b,
3681 mtl_system_items_tl t --okx_system_items_v mtl
3682 WHERE csi.instance_id = p_cpid
3683 AND csi.inventory_item_id = b.inventory_item_id
3684 AND b.inventory_item_id = t.inventory_item_id
3685 AND b.organization_id = t.organization_id
3686 AND t.LANGUAGE = USERENV ('LANG')
3687 AND b.organization_id = okc_context.get_okc_organization_id;
3688
3689 CURSOR order_csr (p_line_id NUMBER)
3690 IS
3691 SELECT ol.header_id, oh.transactional_curr_code, oh.cust_po_number,
3692 oh.invoice_to_contact_id, oh.agreement_id,
3693 oh.invoicing_rule_id, oh.accounting_rule_id
3694 FROM oe_order_lines_all ol -- OKX_ORDER_LINES_V OL
3695 ,
3696 oe_order_headers_all oh -- OKX_ORDER_HEADERS_V OH
3697 WHERE ol.line_id = p_line_id AND ol.header_id = oh.header_id;
3698
3699 cust_rec cust_csr%ROWTYPE;
3700 order_rec order_csr%ROWTYPE;
3701 l_return_status VARCHAR2 (1) := okc_api.g_ret_sts_success;
3702 BEGIN
3703 x_return_status := l_return_status;
3704
3705 OPEN cust_csr;
3706
3707 FETCH cust_csr
3708 INTO cust_rec;
3709
3710 IF cust_csr%NOTFOUND
3711 THEN
3712 CLOSE cust_csr;
3713
3714 IF fnd_log.level_error >= fnd_log.g_current_runtime_level
3715 THEN
3716 fnd_log.STRING (fnd_log.level_error,
3717 g_module_current || '.GET_TRANSFER_DETAILS.ERROR',
3718 ' Cust_csr Not Found '
3719 );
3720 END IF;
3721
3722 l_return_status := okc_api.g_ret_sts_error;
3723 okc_api.set_message (g_app_name,
3724 'OKS_CUST_PROD_DTLS_NOT_FOUND',
3725 'CUSTOMER_PRODUCT',
3726 p_cpid
3727 );
3728 RAISE g_exception_halt_validation;
3729 END IF;
3730
3731 CLOSE cust_csr;
3732
3733 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
3734 THEN
3735 fnd_log.STRING (fnd_log.level_statement,
3736 g_module_current || '.GET_TRANSFER_DETAILS',
3737 ' Original order line Id = '
3738 || cust_rec.original_order_line_id
3739 );
3740 END IF;
3741
3742 IF cust_rec.original_order_line_id IS NOT NULL
3743 THEN
3744 OPEN order_csr (cust_rec.original_order_line_id);
3745
3746 FETCH order_csr
3747 INTO order_rec;
3748
3749 CLOSE order_csr;
3750
3751 -- X_hdr_rec.invoice_to_contact_id := Order_rec.invoice_to_contact_id;
3752 x_hdr_rec.cust_po_number := order_rec.cust_po_number;
3753 x_hdr_rec.agreement_id := order_rec.agreement_id;
3754 --X_hdr_rec.currency := Order_rec.Transactional_curr_code;
3755 x_hdr_rec.accounting_rule_id := order_rec.accounting_rule_id;
3756 x_hdr_rec.invoice_rule_id := order_rec.invoicing_rule_id;
3757 x_hdr_rec.order_hdr_id := order_rec.header_id;
3758 END IF;
3759
3760 /*If Order_rec.Transactional_curr_code Is Null Then
3761 X_hdr_rec.currency := OKC_CURRENCY_API.GET_OU_CURRENCY(okc_context.get_okc_org_id);
3762
3763 End If;*/
3764 x_hdr_rec.contract_number := NULL;
3765 x_hdr_rec.start_date := NULL;
3766 x_hdr_rec.end_date := NULL;
3767 x_hdr_rec.sts_code := NULL;
3768 x_hdr_rec.class_code := NULL;
3769 -- X_hdr_rec.authoring_org_id := Cust_rec.org_id;
3770 -- X_hdr_rec.party_id := Cust_rec.Party_id;
3771 -- X_hdr_rec.bill_to_id := Cust_rec.bill_to_id;
3772 -- X_hdr_rec.ship_to_id := Cust_rec.ship_to_id;
3773 x_line_rec.k_id := NULL;
3774 x_line_rec.k_line_number := NULL;
3775 x_line_rec.object_name := NULL;
3776 x_line_rec.srv_segment1 := NULL;
3777 x_line_rec.srv_desc := NULL;
3778 x_line_rec.srv_sdt := NULL;
3779 x_line_rec.srv_edt := NULL;
3780 x_line_rec.coverage_template_id := cust_rec.coverage_template_id;
3781 -- X_line_rec.org_id := Cust_rec.Org_id;
3782 x_line_rec.srv_id := NULL;
3783 -- X_line_rec.bill_to_id := Cust_rec.bill_to_id;
3784 -- X_line_rec.ship_to_id := Cust_rec.ship_to_id;
3785 x_line_rec.order_line_id := cust_rec.original_order_line_id;
3786 x_line_rec.currency := NULL;
3787
3788 -- X_line_rec.cust_account := Cust_rec.customer_id;
3789 IF fnd_profile.VALUE ('OKS_ITEM_DISPLAY_PREFERENCE') = 'DISPLAY_NAME'
3790 THEN
3791 x_covd_rec.product_segment1 := cust_rec.description;
3792 x_covd_rec.product_desc := cust_rec.NAME;
3793 ELSE
3794 x_covd_rec.product_segment1 := cust_rec.NAME;
3795 x_covd_rec.product_desc := cust_rec.description;
3796 END IF;
3797
3798 x_covd_rec.k_id := NULL;
3799 x_covd_rec.attach_2_line_id := NULL;
3800 x_covd_rec.line_number := NULL;
3801 --X_covd_rec.product_segment1 := Cust_rec.Name;
3802 --X_covd_rec.product_desc := Cust_rec.description;
3803 x_covd_rec.product_end_date := NULL;
3804 x_covd_rec.customer_product_id := p_cpid;
3805 x_covd_rec.product_item_id := cust_rec.inventory_item_id;
3806 x_covd_rec.product_start_date := NULL;
3807 x_covd_rec.quantity := cust_rec.quantity;
3808 x_covd_rec.uom_code := cust_rec.uom_code;
3809 x_covd_rec.list_price := NULL;
3810 x_covd_rec.negotiated_amount := NULL;
3811 x_covd_rec.warranty_flag := NULL;
3812 EXCEPTION
3813 WHEN g_exception_halt_validation
3814 THEN
3815 x_return_status := l_return_status;
3816 WHEN OTHERS
3817 THEN
3818 x_return_status := okc_api.g_ret_sts_unexp_error;
3819 okc_api.set_message (g_app_name,
3820 g_unexpected_error,
3821 g_sqlcode_token,
3822 SQLCODE,
3823 g_sqlerrm_token,
3824 SQLERRM
3825 );
3826
3827 IF fnd_log.level_unexpected >= fnd_log.g_current_runtime_level
3828 THEN
3829 fnd_log.STRING (fnd_log.level_unexpected,
3830 g_module_current
3831 || '.GET_TRANSFER_DETAILS.UNEXPECTED',
3832 'sqlcode = ' || SQLCODE || ', sqlerrm = '
3833 || SQLERRM
3834 );
3835 END IF;
3836 END;
3837
3838 PROCEDURE update_timestamp (
3839 p_counter_group_id IN NUMBER,
3840 p_service_start_date IN DATE,
3841 p_service_line_id IN NUMBER,
3842 x_status OUT NOCOPY VARCHAR2
3843 )
3844 IS
3845 --------------------
3846 CURSOR c_ctr_timestamp (cp_counter_group_id NUMBER)
3847 IS
3848 SELECT counter_value_id, counter_id
3849 FROM okx_counters_v
3850 WHERE counter_group_id = cp_counter_group_id AND TYPE = 'TIME';
3851
3852 ---------------------
3853 g_miss_num CONSTANT NUMBER := 9.99e125;
3854 g_miss_char CONSTANT VARCHAR2 (1) := fnd_global.local_chr (0);
3855 g_miss_date CONSTANT DATE := TO_DATE ('1', 'j');
3856 g_false CONSTANT VARCHAR2 (1) := 'F';
3857 x_return_status VARCHAR2 (1);
3858 x_msg_count NUMBER;
3859 x_msg_data VARCHAR2 (500);
3860 l_ctr_rdg_tbl cs_ctr_capture_reading_pub.ctr_rdg_tbl_type;
3861 l_ctr_grp_log_rec cs_ctr_capture_reading_pub.ctr_grp_log_rec_type;
3862 BEGIN
3863 -- Initialise table variables
3864 l_ctr_rdg_tbl (1).counter_value_id := g_miss_num;
3865 l_ctr_rdg_tbl (1).counter_id := g_miss_num;
3866 l_ctr_rdg_tbl (1).value_timestamp := g_miss_date;
3867 l_ctr_rdg_tbl (1).counter_reading := g_miss_num;
3868 l_ctr_rdg_tbl (1).reset_flag := g_false;
3869 l_ctr_rdg_tbl (1).reset_reason := g_miss_char;
3870 l_ctr_rdg_tbl (1).pre_reset_last_rdg := g_miss_num;
3871 l_ctr_rdg_tbl (1).post_reset_first_rdg := g_miss_num;
3872 l_ctr_rdg_tbl (1).misc_reading_type := g_miss_char;
3873 l_ctr_rdg_tbl (1).misc_reading := g_miss_num;
3874 l_ctr_rdg_tbl (1).object_version_number := g_miss_num;
3875 l_ctr_rdg_tbl (1).attribute1 := g_miss_char;
3876 l_ctr_rdg_tbl (1).attribute2 := g_miss_char;
3877 l_ctr_rdg_tbl (1).attribute3 := g_miss_char;
3878 l_ctr_rdg_tbl (1).attribute4 := g_miss_char;
3879 l_ctr_rdg_tbl (1).attribute5 := g_miss_char;
3880 l_ctr_rdg_tbl (1).attribute6 := g_miss_char;
3881 l_ctr_rdg_tbl (1).attribute7 := g_miss_char;
3882 l_ctr_rdg_tbl (1).attribute8 := g_miss_char;
3883 l_ctr_rdg_tbl (1).attribute9 := g_miss_char;
3884 l_ctr_rdg_tbl (1).attribute10 := g_miss_char;
3885 l_ctr_rdg_tbl (1).attribute11 := g_miss_char;
3886 l_ctr_rdg_tbl (1).attribute12 := g_miss_char;
3887 l_ctr_rdg_tbl (1).attribute13 := g_miss_char;
3888 l_ctr_rdg_tbl (1).attribute14 := g_miss_char;
3889 l_ctr_rdg_tbl (1).attribute15 := g_miss_char;
3890 l_ctr_rdg_tbl (1).CONTEXT := g_miss_char;
3891 l_ctr_grp_log_rec.counter_grp_log_id := g_miss_num;
3892 l_ctr_grp_log_rec.counter_group_id := g_miss_num;
3893 l_ctr_grp_log_rec.value_timestamp := g_miss_date;
3894 l_ctr_grp_log_rec.source_transaction_id := g_miss_num;
3895 l_ctr_grp_log_rec.source_transaction_code := g_miss_char;
3896 l_ctr_grp_log_rec.attribute1 := g_miss_char;
3897 l_ctr_grp_log_rec.attribute2 := g_miss_char;
3898 l_ctr_grp_log_rec.attribute3 := g_miss_char;
3899 l_ctr_grp_log_rec.attribute4 := g_miss_char;
3900 l_ctr_grp_log_rec.attribute5 := g_miss_char;
3901 l_ctr_grp_log_rec.attribute6 := g_miss_char;
3902 l_ctr_grp_log_rec.attribute7 := g_miss_char;
3903 l_ctr_grp_log_rec.attribute8 := g_miss_char;
3904 l_ctr_grp_log_rec.attribute9 := g_miss_char;
3905 l_ctr_grp_log_rec.attribute10 := g_miss_char;
3906 l_ctr_grp_log_rec.attribute11 := g_miss_char;
3907 l_ctr_grp_log_rec.attribute12 := g_miss_char;
3908 l_ctr_grp_log_rec.attribute13 := g_miss_char;
3909 l_ctr_grp_log_rec.attribute14 := g_miss_char;
3910 l_ctr_grp_log_rec.attribute15 := g_miss_char;
3911 l_ctr_grp_log_rec.CONTEXT := g_miss_char;
3912
3913 FOR l_ctr_timestamp IN c_ctr_timestamp (p_counter_group_id)
3914 LOOP
3915 l_ctr_rdg_tbl (1).counter_id := l_ctr_timestamp.counter_id;
3916 l_ctr_rdg_tbl (1).counter_reading := 0;
3917 l_ctr_rdg_tbl (1).value_timestamp := p_service_start_date;
3918 l_ctr_grp_log_rec.counter_group_id := p_counter_group_id;
3919 l_ctr_grp_log_rec.value_timestamp := p_service_start_date;
3920 l_ctr_grp_log_rec.source_transaction_id := p_service_line_id;
3921 l_ctr_grp_log_rec.source_transaction_code := 'CONTRACT_LINE';
3922 cs_ctr_capture_reading_pub.capture_counter_reading
3923 (p_api_version_number => 1.0,
3924 p_init_msg_list => 'T',
3925 p_commit => 'F',
3926 p_validation_level => 100,
3927 p_ctr_grp_log_rec => l_ctr_grp_log_rec,
3928 p_ctr_rdg_tbl => l_ctr_rdg_tbl,
3929 x_return_status => x_return_status,
3930 x_msg_count => x_msg_count,
3931 x_msg_data => x_msg_data
3932 );
3933 x_status := x_return_status;
3934 END LOOP;
3935 EXCEPTION
3936 WHEN OTHERS
3937 THEN
3938 x_return_status := okc_api.g_ret_sts_unexp_error;
3939 okc_api.set_message (g_app_name,
3940 g_unexpected_error,
3941 g_sqlcode_token,
3942 SQLCODE,
3943 g_sqlerrm_token,
3944 SQLERRM
3945 );
3946 END update_timestamp;
3947
3948 --==========================================================================================================================
3949 PROCEDURE salescredit (
3950 p_order_line_id IN NUMBER,
3951 x_salescredit_tbl OUT NOCOPY salescredit_tbl,
3952 x_return_status OUT NOCOPY VARCHAR2
3953 )
3954 IS
3955 CURSOR l_scredit_csr
3956 IS
3957 SELECT salesrep_id, sales_credit_type_id, PERCENT, sales_group_id
3958 FROM oe_sales_credits
3959 WHERE line_id = p_order_line_id;
3960
3961 /* Cursor l_salesgroup_csr(p_salesrep_id Number) Is
3962 SELECT DISTINCT grp.group_name, grp.group_id
3963 FROM jtf_rs_group_members mem
3964 ,jtf_rs_groups_vl grp
3965 ,jtf_rs_salesreps srp
3966 ,jtf_rs_group_usages usg
3967 ,jtf_rs_role_relations rrl
3968 WHERE srp.resource_id = mem.resource_id
3969 AND mem.group_id = grp.group_id
3970 AND mem.group_id = usg.group_id
3971 AND usg.usage = 'SALES'
3972 AND mem.delete_flag = 'N'
3973 AND mem.group_member_id = rrl.role_resource_id
3974 AND rrl.role_resource_type = 'RS_GROUP_MEMBER'
3975 AND rrl.delete_flag = 'N'
3976 --AND nvl(rrl.end_date_active,TO_DATE('01/01/4713','MM/DD/RRRR')) >=
3977 --:NAME_IN('OKS_HEADER_CONTACTS.START_DATE')
3978 -- AND rrl.start_date_active <= :NAME_IN('OKS_HEADER_CONTACTS END_DATE ')
3979 AND srp.salesrep_id = p_salesrep_id
3980 AND srp.org_id = okc_context.get_okc_
3981 -- AND :END_DATE BETWEEN grp.start_date_active AND
3982 -- NVL(grp.end_date_active,TO_DATE('01/01/4713','MM/DD/RRRR'))
3983 UNION ALL
3984 SELECT group_name, group_id
3985 FROM jtf_rs_groups_tl
3986 WHERE group_id = -1
3987 AND LANGUAGE = USERENV('LANG');
3988
3989 */
3990 i INTEGER := 0;
3991 l_return_status VARCHAR2 (1) := okc_api.g_ret_sts_success;
3992 BEGIN
3993 x_return_status := l_return_status;
3994 i := 1;
3995
3996 FOR l_scredit_rec IN l_scredit_csr
3997 LOOP
3998 x_salescredit_tbl (i).ctc_id := l_scredit_rec.salesrep_id;
3999 x_salescredit_tbl (i).sales_credit_type_id :=
4000 l_scredit_rec.sales_credit_type_id;
4001 x_salescredit_tbl (i).PERCENT := l_scredit_rec.PERCENT;
4002 x_salescredit_tbl (i).sales_group_id := l_scredit_rec.sales_group_id;
4003 i := i + 1;
4004 END LOOP;
4005 EXCEPTION
4006 WHEN g_exception_halt_validation
4007 THEN
4008 x_return_status := l_return_status;
4009 WHEN OTHERS
4010 THEN
4011 x_return_status := okc_api.g_ret_sts_unexp_error;
4012
4013 IF fnd_log.level_unexpected >= fnd_log.g_current_runtime_level
4014 THEN
4015 fnd_log.STRING (fnd_log.level_unexpected,
4016 g_module_current || '.SALESCREDIT.UNEXPECTED',
4017 'sqlcode = ' || SQLCODE || ', sqlerrm = '
4018 || SQLERRM
4019 );
4020 END IF;
4021
4022 okc_api.set_message (g_app_name,
4023 g_unexpected_error,
4024 g_sqlcode_token,
4025 SQLCODE,
4026 g_sqlerrm_token,
4027 SQLERRM
4028 );
4029 END;
4030
4031 --mmadhavi bug 4174921
4032 PROCEDURE salescredit_header (
4033 p_order_hdr_id IN NUMBER,
4034 x_salescredit_tbl OUT NOCOPY salescredit_tbl,
4035 x_return_status OUT NOCOPY VARCHAR2
4036 )
4037 IS
4038 CURSOR l_scredit_csr
4039 IS
4040 SELECT salesrep_id, sales_credit_type_id, PERCENT, sales_group_id
4041 FROM oe_sales_credits
4042 WHERE header_id = p_order_hdr_id AND line_id IS NULL;
4043
4044 i INTEGER := 0;
4045 l_return_status VARCHAR2 (1) := okc_api.g_ret_sts_success;
4046 BEGIN
4047 x_return_status := l_return_status;
4048 i := 1;
4049
4050 FOR l_scredit_rec IN l_scredit_csr
4051 LOOP
4052 x_salescredit_tbl (i).ctc_id := l_scredit_rec.salesrep_id;
4053 x_salescredit_tbl (i).sales_credit_type_id :=
4054 l_scredit_rec.sales_credit_type_id;
4055 x_salescredit_tbl (i).PERCENT := l_scredit_rec.PERCENT;
4056 x_salescredit_tbl (i).sales_group_id := l_scredit_rec.sales_group_id;
4057 i := i + 1;
4058 END LOOP;
4059 EXCEPTION
4060 WHEN g_exception_halt_validation
4061 THEN
4062 x_return_status := l_return_status;
4063 WHEN OTHERS
4064 THEN
4065 x_return_status := okc_api.g_ret_sts_unexp_error;
4066
4067 IF fnd_log.level_unexpected >= fnd_log.g_current_runtime_level
4068 THEN
4069 fnd_log.STRING (fnd_log.level_unexpected,
4070 g_module_current || '.SALESCREDIT_HDR.UNEXPECTED',
4071 'sqlcode = ' || SQLCODE || ', sqlerrm = '
4072 || SQLERRM
4073 );
4074 END IF;
4075
4076 okc_api.set_message (g_app_name,
4077 g_unexpected_error,
4078 g_sqlcode_token,
4079 SQLCODE,
4080 g_sqlerrm_token,
4081 SQLERRM
4082 );
4083 END;
4084
4085 --mmadhavi bug 4174921
4086 --======================================================================================
4087
4088 ---UPDATE CONTRACT DETAILS
4089 PROCEDURE update_contract_details (
4090 p_hdr_id NUMBER,
4091 p_order_line_id NUMBER,
4092 x_return_status OUT NOCOPY VARCHAR2
4093 )
4094 IS
4095 CURSOR l_link_csr1
4096 IS
4097 SELECT NVL (link_ord_line_id1, order_line_id1)
4098 FROM oks_k_order_details
4099 WHERE order_line_id1 = TO_CHAR (p_order_line_id);
4100
4101 CURSOR l_link_csr_a (l_link_ord_id VARCHAR2)
4102 IS
4103 SELECT ID, order_line_id1, object_version_number
4104 FROM oks_k_order_details
4105 WHERE link_ord_line_id1 = l_link_ord_id AND chr_id IS NULL;
4106
4107 CURSOR l_link_csr_b (l_link_ord_id VARCHAR2)
4108 IS
4109 SELECT ID, order_line_id1, object_version_number
4110 FROM oks_k_order_details
4111 WHERE order_line_id1 = l_link_ord_id AND chr_id IS NULL;
4112
4113 l_link_rec1 l_link_csr_a%ROWTYPE;
4114 l_link_rec2 l_link_csr_b%ROWTYPE;
4115 l_codv_tbl_in oks_cod_pvt.codv_tbl_type;
4116 l_codv_tbl_out oks_cod_pvt.codv_tbl_type;
4117 l_link_to_order_id VARCHAR2 (40);
4118 l_return_status VARCHAR2 (1) := okc_api.g_ret_sts_success;
4119 l_msg_count NUMBER;
4120 l_msg_data VARCHAR2 (2000);
4121 link_flag NUMBER := 0;
4122 BEGIN
4123 x_return_status := l_return_status;
4124
4125 OPEN l_link_csr1;
4126
4127 FETCH l_link_csr1
4128 INTO l_link_to_order_id;
4129
4130 IF l_link_csr1%NOTFOUND
4131 THEN
4132 CLOSE l_link_csr1;
4133
4134 IF fnd_log.level_error >= fnd_log.g_current_runtime_level
4135 THEN
4136 fnd_log.STRING (fnd_log.level_error,
4137 g_module_current
4138 || '.UPDATE_CONTRACT_DETAILS.ERROR',
4139 'l_line_csr1 Not Found for Line Id = '
4140 || p_order_line_id
4141 );
4142 END IF;
4143
4144 okc_api.set_message (g_app_name,
4145 'OKS_ORDER_DETAILS',
4146 'ORDER_DETAILS',
4147 p_order_line_id
4148 );
4149 RAISE g_exception_halt_validation;
4150 END IF;
4151
4152 CLOSE l_link_csr1;
4153
4154 link_flag := 0;
4155
4156 OPEN l_link_csr_a (l_link_to_order_id);
4157
4158 LOOP
4159 FETCH l_link_csr_a
4160 INTO l_link_rec1;
4161
4162 EXIT WHEN l_link_csr_a%NOTFOUND;
4163 l_codv_tbl_in (1).ID := l_link_rec1.ID;
4164 l_codv_tbl_in (1).chr_id := p_hdr_id;
4165 l_codv_tbl_in (1).object_version_number :=
4166 l_link_rec1.object_version_number;
4167 --BugFix 2458874
4168 oks_cod_pvt.update_row (p_api_version => 1.0,
4169 p_init_msg_list => 'T',
4170 x_return_status => l_return_status,
4171 x_msg_count => l_msg_count,
4172 x_msg_data => l_msg_data,
4173 p_codv_tbl => l_codv_tbl_in,
4174 x_codv_tbl => l_codv_tbl_out
4175 );
4176
4177 IF (fnd_log.level_event >= fnd_log.g_current_runtime_level)
4178 THEN
4179 fnd_log.STRING (fnd_log.level_event,
4180 g_module_current
4181 || '.UPDATE_CONTRACT_DETAILS.External_call.after',
4182 'OKS_COD_PVT.update_row(Return Status = '
4183 || l_return_status
4184 || ')'
4185 );
4186 END IF;
4187
4188 IF NOT l_return_status = okc_api.g_ret_sts_success
4189 THEN
4190 x_return_status := l_return_status;
4191 RAISE g_exception_halt_validation;
4192 END IF;
4193
4194 link_flag := 1;
4195 END LOOP;
4196
4197 CLOSE l_link_csr_a;
4198
4199 --If link_flag = 0 Then
4200 OPEN l_link_csr_b (l_link_to_order_id);
4201
4202 LOOP
4203 FETCH l_link_csr_b
4204 INTO l_link_rec2;
4205
4206 EXIT WHEN l_link_csr_b%NOTFOUND;
4207 l_codv_tbl_in (1).ID := l_link_rec2.ID;
4208 l_codv_tbl_in (1).chr_id := p_hdr_id;
4209 l_codv_tbl_in (1).object_version_number :=
4210 l_link_rec2.object_version_number;
4211 --BugFix 2458874
4212 oks_cod_pvt.update_row (p_api_version => 1.0,
4213 p_init_msg_list => 'T',
4214 x_return_status => l_return_status,
4215 x_msg_count => l_msg_count,
4216 x_msg_data => l_msg_data,
4217 p_codv_tbl => l_codv_tbl_in,
4218 x_codv_tbl => l_codv_tbl_out
4219 );
4220
4221 IF (fnd_log.level_event >= fnd_log.g_current_runtime_level)
4222 THEN
4223 fnd_log.STRING (fnd_log.level_event,
4224 g_module_current
4225 || '.UPDATE_CONTRACT_DETAILS.External_call.after',
4226 'OKS_COD_PVT.update_row(Return Status = '
4227 || l_return_status
4228 || ')'
4229 );
4230 END IF;
4231
4232 IF NOT l_return_status = okc_api.g_ret_sts_success
4233 THEN
4234 x_return_status := l_return_status;
4235 RAISE g_exception_halt_validation;
4236 END IF;
4237 END LOOP;
4238
4239 CLOSE l_link_csr_b;
4240 ---End if;
4241 EXCEPTION
4242 WHEN g_exception_halt_validation
4243 THEN
4244 x_return_status := l_return_status;
4245 WHEN OTHERS
4246 THEN
4247 x_return_status := okc_api.g_ret_sts_unexp_error;
4248 okc_api.set_message (g_app_name,
4249 g_unexpected_error,
4250 g_sqlcode_token,
4251 SQLCODE,
4252 g_sqlerrm_token,
4253 SQLERRM
4254 );
4255
4256 IF fnd_log.level_unexpected >= fnd_log.g_current_runtime_level
4257 THEN
4258 fnd_log.STRING (fnd_log.level_unexpected,
4259 g_module_current
4260 || '.UPDATE_CONTRACT_DETAILS.UNEXPECTED',
4261 'sqlcode = ' || SQLCODE || ', sqlerrm = '
4262 || SQLERRM
4263 );
4264 END IF;
4265 END;
4266
4267 PROCEDURE get_pricing_attributes (
4268 p_order_line_id IN NUMBER,
4269 x_pricing_att OUT NOCOPY oks_extwarprgm_pvt.pricing_attributes_type,
4270 x_return_status OUT NOCOPY VARCHAR2
4271 )
4272 IS
4273 CURSOR l_pricing_att_csr
4274 IS
4275 SELECT pricing_context, pricing_attribute1, pricing_attribute2,
4276 pricing_attribute3, pricing_attribute4, pricing_attribute5,
4277 pricing_attribute6, pricing_attribute7, pricing_attribute8,
4278 pricing_attribute9, pricing_attribute10, pricing_attribute11,
4279 pricing_attribute12, pricing_attribute13,
4280 pricing_attribute14, pricing_attribute15,
4281 pricing_attribute16, pricing_attribute17,
4282 pricing_attribute18, pricing_attribute19,
4283 pricing_attribute20, pricing_attribute21,
4284 pricing_attribute22, pricing_attribute23,
4285 pricing_attribute24, pricing_attribute25,
4286 pricing_attribute26, pricing_attribute27,
4287 pricing_attribute28, pricing_attribute29,
4288 pricing_attribute30, pricing_attribute31,
4289 pricing_attribute32, pricing_attribute33,
4290 pricing_attribute34, pricing_attribute35,
4291 pricing_attribute36, pricing_attribute37,
4292 pricing_attribute38, pricing_attribute39,
4293 pricing_attribute40, pricing_attribute41,
4294 pricing_attribute42, pricing_attribute43,
4295 pricing_attribute44, pricing_attribute45,
4296 pricing_attribute46, pricing_attribute47,
4297 pricing_attribute48, pricing_attribute49,
4298 pricing_attribute50, pricing_attribute51,
4299 pricing_attribute52, pricing_attribute53,
4300 pricing_attribute54, pricing_attribute55,
4301 pricing_attribute56, pricing_attribute57,
4302 pricing_attribute58, pricing_attribute59,
4303 pricing_attribute60, pricing_attribute61,
4304 pricing_attribute62, pricing_attribute63,
4305 pricing_attribute64, pricing_attribute65,
4306 pricing_attribute66, pricing_attribute67,
4307 pricing_attribute68, pricing_attribute69,
4308 pricing_attribute70, pricing_attribute71,
4309 pricing_attribute72, pricing_attribute73,
4310 pricing_attribute74, pricing_attribute75,
4311 pricing_attribute76, pricing_attribute77,
4312 pricing_attribute78, pricing_attribute79,
4313 pricing_attribute80, pricing_attribute81,
4314 pricing_attribute82, pricing_attribute83,
4315 pricing_attribute84, pricing_attribute85,
4316 pricing_attribute86, pricing_attribute87,
4317 pricing_attribute88, pricing_attribute89,
4318 pricing_attribute90, pricing_attribute91,
4319 pricing_attribute92, pricing_attribute93,
4320 pricing_attribute94, pricing_attribute95,
4321 pricing_attribute96, pricing_attribute97,
4322 pricing_attribute98, pricing_attribute99,
4323 pricing_attribute100
4324 FROM oe_order_price_attribs_v okx
4325 WHERE okx.line_id = p_order_line_id;
4326 BEGIN
4327 x_return_status := 'S';
4328
4329 OPEN l_pricing_att_csr;
4330
4331 FETCH l_pricing_att_csr
4332 INTO x_pricing_att;
4333
4334 CLOSE l_pricing_att_csr;
4335 EXCEPTION
4336 WHEN OTHERS
4337 THEN
4338 x_return_status := 'U';
4339 okc_api.set_message (g_app_name,
4340 g_unexpected_error,
4341 g_sqlcode_token,
4342 SQLCODE,
4343 g_sqlerrm_token,
4344 SQLERRM
4345 );
4346
4347 IF fnd_log.level_unexpected >= fnd_log.g_current_runtime_level
4348 THEN
4349 fnd_log.STRING (fnd_log.level_unexpected,
4350 g_module_current
4351 || '.GET_PRICING_ATTRIBUTES.UNEXPECTED',
4352 'sqlcode = ' || SQLCODE || ', sqlerrm = '
4353 || SQLERRM
4354 );
4355 END IF;
4356 END;
4357
4358 PROCEDURE get_k_pricing_attributes (
4359 p_k_line_id IN NUMBER,
4360 x_pricing_att OUT NOCOPY oks_extwarprgm_pvt.pricing_attributes_type,
4361 x_return_status OUT NOCOPY VARCHAR2
4362 )
4363 IS
4364 CURSOR l_pricing_att_csr
4365 IS
4366 SELECT pricing_context, pricing_attribute1, pricing_attribute2,
4367 pricing_attribute3, pricing_attribute4, pricing_attribute5,
4368 pricing_attribute6, pricing_attribute7, pricing_attribute8,
4369 pricing_attribute9, pricing_attribute10, pricing_attribute11,
4370 pricing_attribute12, pricing_attribute13,
4371 pricing_attribute14, pricing_attribute15,
4372 pricing_attribute16, pricing_attribute17,
4373 pricing_attribute18, pricing_attribute19,
4374 pricing_attribute20, pricing_attribute21,
4375 pricing_attribute22, pricing_attribute23,
4376 pricing_attribute24, pricing_attribute25,
4377 pricing_attribute26, pricing_attribute27,
4378 pricing_attribute28, pricing_attribute29,
4379 pricing_attribute30, pricing_attribute31,
4380 pricing_attribute32, pricing_attribute33,
4381 pricing_attribute34, pricing_attribute35,
4382 pricing_attribute36, pricing_attribute37,
4383 pricing_attribute38, pricing_attribute39,
4384 pricing_attribute40, pricing_attribute41,
4385 pricing_attribute42, pricing_attribute43,
4386 pricing_attribute44, pricing_attribute45,
4387 pricing_attribute46, pricing_attribute47,
4388 pricing_attribute48, pricing_attribute49,
4389 pricing_attribute50, pricing_attribute51,
4390 pricing_attribute52, pricing_attribute53,
4391 pricing_attribute54, pricing_attribute55,
4392 pricing_attribute56, pricing_attribute57,
4393 pricing_attribute58, pricing_attribute59,
4394 pricing_attribute60, pricing_attribute61,
4395 pricing_attribute62, pricing_attribute63,
4396 pricing_attribute64, pricing_attribute65,
4397 pricing_attribute66, pricing_attribute67,
4398 pricing_attribute68, pricing_attribute69,
4399 pricing_attribute70, pricing_attribute71,
4400 pricing_attribute72, pricing_attribute73,
4401 pricing_attribute74, pricing_attribute75,
4402 pricing_attribute76, pricing_attribute77,
4403 pricing_attribute78, pricing_attribute79,
4404 pricing_attribute80, pricing_attribute81,
4405 pricing_attribute82, pricing_attribute83,
4406 pricing_attribute84, pricing_attribute85,
4407 pricing_attribute86, pricing_attribute87,
4408 pricing_attribute88, pricing_attribute89,
4409 pricing_attribute90, pricing_attribute91,
4410 pricing_attribute92, pricing_attribute93,
4411 pricing_attribute94, pricing_attribute95,
4412 pricing_attribute96, pricing_attribute97,
4413 pricing_attribute98, pricing_attribute99,
4414 pricing_attribute100
4415 FROM okc_price_att_values_v okx
4416 WHERE okx.cle_id = p_k_line_id;
4417 BEGIN
4418 x_return_status := 'S';
4419
4420 OPEN l_pricing_att_csr;
4421
4422 FETCH l_pricing_att_csr
4423 INTO x_pricing_att;
4424
4425 CLOSE l_pricing_att_csr;
4426 EXCEPTION
4427 WHEN OTHERS
4428 THEN
4429 x_return_status := 'U';
4430 okc_api.set_message (g_app_name,
4431 g_unexpected_error,
4432 g_sqlcode_token,
4433 SQLCODE,
4434 g_sqlerrm_token,
4435 SQLERRM
4436 );
4437
4438 IF fnd_log.level_unexpected >= fnd_log.g_current_runtime_level
4439 THEN
4440 fnd_log.STRING (fnd_log.level_unexpected,
4441 g_module_current
4442 || '.GET_K_PRICING_ATTRIBUTES.UNEXPECTED',
4443 'sqlcode = ' || SQLCODE || ', sqlerrm = '
4444 || SQLERRM
4445 );
4446 END IF;
4447 END;
4448
4449 FUNCTION oks_get_party (p_chr_id NUMBER, p_rle_code VARCHAR2)
4450 RETURN NUMBER
4451 IS
4452 CURSOR l_party_csr
4453 IS
4454 SELECT object1_id1
4455 FROM okc_k_party_roles_v
4456 WHERE chr_id = p_chr_id AND rle_code = p_rle_code;
4457
4458 l_party_id NUMBER := NULL;
4459 BEGIN
4460 OPEN l_party_csr;
4461
4462 FETCH l_party_csr
4463 INTO l_party_id;
4464
4465 CLOSE l_party_csr;
4466
4467 RETURN l_party_id;
4468 END;
4469
4470 /*
4471 FUNCTION OKS_GET_HDR_RULES
4472 (
4473 p_chr_id NUMBER,
4474 p_Category VARCHAR2,
4475 p_object_Code VARCHAR2
4476 )
4477 Return NUMBER
4478 Is
4479
4480 Cursor Get_Rule_objectid_Csr Is
4481 Select object1_id1
4482 From okc_rules_v rul
4483 ,okc_rule_groups_v rgp
4484 Where rul.rgp_id = rgp.id
4485 And rule_information_category = p_category
4486 And jtot_object1_Code = p_object_Code
4487 And rgp.dnz_chr_id = p_chr_id
4488 And cle_id Is Null;
4489 l_object_id VARCHAR2(40);
4490
4491 BEGIN
4492 Open Get_Rule_objectid_Csr;
4493 Fetch Get_Rule_objectid_Csr into l_object_id;
4494 If Get_Rule_objectid_Csr%notfound Then
4495 Close Get_Rule_objectid_Csr;
4496 return(null);
4497 End If;
4498
4499 Close Get_Rule_objectid_Csr;
4500 return(l_object_id);
4501
4502 END OKS_GET_HDR_RULES;
4503 */
4504 FUNCTION oks_get_rules (
4505 p_cle_id NUMBER,
4506 p_category VARCHAR2,
4507 p_object_code VARCHAR2
4508 )
4509 RETURN NUMBER
4510 IS
4511 CURSOR get_rule_objectid_csr
4512 IS
4513 SELECT object1_id1
4514 FROM okc_rules_v rul, okc_rule_groups_v rgp
4515 WHERE rul.rgp_id = rgp.ID
4516 AND rule_information_category = p_category
4517 AND jtot_object1_code = p_object_code
4518 AND cle_id = p_cle_id;
4519
4520 l_object_id VARCHAR2 (40);
4521 BEGIN
4522 OPEN get_rule_objectid_csr;
4523
4524 FETCH get_rule_objectid_csr
4525 INTO l_object_id;
4526
4527 IF get_rule_objectid_csr%NOTFOUND
4528 THEN
4529 CLOSE get_rule_objectid_csr;
4530
4531 RETURN (NULL);
4532 END IF;
4533
4534 CLOSE get_rule_objectid_csr;
4535
4536 RETURN (l_object_id);
4537 END oks_get_rules;
4538
4539 FUNCTION oks_get_svc (p_cle_id NUMBER)
4540 RETURN NUMBER
4541 IS
4542 CURSOR get_rule_objectid_csr
4543 IS
4544 SELECT object1_id1
4545 FROM okc_k_items kit
4546 WHERE cle_id = p_cle_id;
4547
4548 l_object_id NUMBER;
4549 BEGIN
4550 OPEN get_rule_objectid_csr;
4551
4552 FETCH get_rule_objectid_csr
4553 INTO l_object_id;
4554
4555 IF get_rule_objectid_csr%NOTFOUND
4556 THEN
4557 CLOSE get_rule_objectid_csr;
4558
4559 RETURN (NULL);
4560 END IF;
4561
4562 CLOSE get_rule_objectid_csr;
4563
4564 RETURN (l_object_id);
4565 END oks_get_svc;
4566
4567 /*----------------------------------------------------------------------
4568
4569 PROCEDURE : CREATE_SALES_CREDITS
4570 DESCRIPTION : This procedure is to create the sales credits
4571 INPUT : header id
4572 line id
4573 OUTPUT : retun status 'S' if successful
4574
4575 ----------------------------------------------------------------------*/
4576 PROCEDURE create_sales_credits (
4577 p_header_id NUMBER,
4578 p_line_id NUMBER,
4579 x_return_status OUT NOCOPY VARCHAR2
4580 )
4581 IS
4582 l_return_status VARCHAR2 (3) := okc_api.g_ret_sts_success;
4583
4584 CURSOR scredit_csr
4585 IS
4586 SELECT PERCENT, ctc_id, sales_credit_type_id1,
4587 sales_credit_type_id2, sales_group_id
4588 FROM oks_k_sales_credits_v
4589 WHERE chr_id = p_header_id AND cle_id IS NULL;
4590
4591 l_scr_rec scredit_csr%ROWTYPE;
4592 l_scrv_tbl_in oks_sales_credit_pub.scrv_tbl_type;
4593 l_scrv_tbl_out oks_sales_credit_pub.scrv_tbl_type;
4594 l_msg_count NUMBER;
4595 l_msg_data VARCHAR2 (2000);
4596 BEGIN
4597 x_return_status := l_return_status;
4598
4599 FOR l_scr_rec IN scredit_csr
4600 LOOP
4601 l_scrv_tbl_in (1).PERCENT := l_scr_rec.PERCENT;
4602 l_scrv_tbl_in (1).ctc_id := l_scr_rec.ctc_id;
4603 l_scrv_tbl_in (1).sales_credit_type_id1 :=
4604 l_scr_rec.sales_credit_type_id1;
4605 l_scrv_tbl_in (1).sales_credit_type_id2 :=
4606 l_scr_rec.sales_credit_type_id2;
4607 l_scrv_tbl_in (1).sales_group_id := l_scr_rec.sales_group_id;
4608 l_scrv_tbl_in (1).cle_id := p_line_id;
4609 l_scrv_tbl_in (1).chr_id := p_header_id;
4610 oks_sales_credit_pub.insert_sales_credit
4611 (p_api_version => 1.0,
4612 p_init_msg_list => 'T',
4613 x_return_status => l_return_status,
4614 x_msg_count => l_msg_count,
4615 x_msg_data => l_msg_data,
4616 p_scrv_tbl => l_scrv_tbl_in,
4617 x_scrv_tbl => l_scrv_tbl_out
4618 );
4619
4620 IF (fnd_log.level_event >= fnd_log.g_current_runtime_level)
4621 THEN
4622 fnd_log.STRING
4623 (fnd_log.level_event,
4624 g_module_current
4625 || '.CREATE_SALES_CREDITS.External_call.after',
4626 'OKS_SALES_CREDIT_PUB.Insert_Sales_credit(x_return_status = '
4627 || x_return_status
4628 || ')'
4629 );
4630 END IF;
4631
4632 IF l_return_status <> 'S'
4633 THEN
4634 l_return_status := 'U';
4635 RAISE g_exception_halt_validation;
4636 END IF;
4637 END LOOP;
4638 EXCEPTION
4639 WHEN g_exception_halt_validation
4640 THEN
4641 x_return_status := l_return_status;
4642 WHEN OTHERS
4643 THEN
4644 x_return_status := okc_api.g_ret_sts_unexp_error;
4645 okc_api.set_message (g_app_name,
4646 g_unexpected_error,
4647 g_sqlcode_token,
4648 SQLCODE,
4649 g_sqlerrm_token,
4650 SQLERRM
4651 );
4652
4653 IF fnd_log.level_unexpected >= fnd_log.g_current_runtime_level
4654 THEN
4655 fnd_log.STRING (fnd_log.level_unexpected,
4656 g_module_current
4657 || '.CREATE_SALES_CREDITS.UNEXPECTED',
4658 'sqlcode = ' || SQLCODE || ', sqlerrm = '
4659 || SQLERRM
4660 );
4661 END IF;
4662 END create_sales_credits;
4663
4664 FUNCTION get_line_name_if_null (
4665 p_inventory_item_id IN NUMBER,
4666 p_organization_id IN NUMBER,
4667 p_code IN VARCHAR2,
4668 x_return_status OUT NOCOPY VARCHAR2
4669 )
4670 RETURN VARCHAR2
4671 IS
4672 CURSOR l_csr
4673 IS
4674 SELECT application_column_name
4675 FROM fnd_id_flex_segments_vl
4676 WHERE application_id = 401 AND id_flex_code = p_code
4677 ORDER BY segment_num;
4678
4679 CURSOR l_delr_csr (l_structure_code VARCHAR2)
4680 IS
4681 SELECT concatenated_segment_delimiter
4682 FROM fnd_id_flex_structures_vl
4683 WHERE application_id = 401
4684 AND id_flex_code = p_code
4685 AND id_flex_structure_code = l_structure_code;
4686
4687 l_structure_code VARCHAR2 (30);
4688 l_sel_column VARCHAR2 (1000);
4689 l_count NUMBER := 1;
4690 l_select_stmt VARCHAR2 (4000);
4691 l_column VARCHAR2 (600);
4692 l_exe_count INTEGER;
4693 l_cursor_id INTEGER;
4694 l_name VARCHAR2 (800) := NULL;
4695 l_delimiter VARCHAR2 (5);
4696 BEGIN
4697 x_return_status := okc_api.g_ret_sts_success;
4698
4699 IF p_code = 'SERV'
4700 THEN
4701 l_structure_code := 'ORACLE_SERVICE_ITEM_FLEXFIELD';
4702 ELSIF p_code = 'MSTK'
4703 THEN
4704 l_structure_code := 'SYSTEM_ITEMS';
4705 END IF;
4706
4707 OPEN l_delr_csr (l_structure_code);
4708
4709 FETCH l_delr_csr
4710 INTO l_delimiter;
4711
4712 CLOSE l_delr_csr;
4713
4714 OPEN l_csr;
4715
4716 FETCH l_csr
4717 INTO l_column;
4718
4719 LOOP
4720 EXIT WHEN l_csr%NOTFOUND;
4721
4722 IF l_count = 1
4723 THEN
4724 l_sel_column := l_column;
4725 ELSE
4726 l_sel_column :=
4727 l_sel_column
4728 || '||'
4729 || ''''
4730 || l_delimiter
4731 || ''''
4732 || '||'
4733 || l_column;
4734 END IF;
4735
4736 FETCH l_csr
4737 INTO l_column;
4738
4739 l_count := 2;
4740 END LOOP;
4741
4742 CLOSE l_csr;
4743
4744 l_select_stmt :=
4745 'Select '
4746 || l_sel_column
4747 || ' From Mtl_system_items_b
4748 Where Inventory_item_id = :d1 And Organization_id = :d2 ';
4749 l_cursor_id := DBMS_SQL.open_cursor;
4750 DBMS_SQL.parse (l_cursor_id, l_select_stmt, DBMS_SQL.native);
4751 DBMS_SQL.bind_variable (l_cursor_id, ':d1', p_inventory_item_id);
4752 DBMS_SQL.bind_variable (l_cursor_id, ':d2', p_organization_id);
4753 l_exe_count := DBMS_SQL.EXECUTE (l_cursor_id);
4754 DBMS_SQL.define_column (l_cursor_id, 1, l_name, 200);
4755
4756 LOOP
4757 IF DBMS_SQL.fetch_rows (l_cursor_id) = 0
4758 THEN
4759 EXIT;
4760 END IF;
4761
4762 DBMS_SQL.column_value (l_cursor_id, 1, l_name);
4763 END LOOP;
4764
4765 DBMS_SQL.close_cursor (l_cursor_id);
4766 --Dbms_Output.Put_Line('Name: ' || l_name);
4767 RETURN (l_name);
4768 EXCEPTION
4769 WHEN OTHERS
4770 THEN
4771 x_return_status := okc_api.g_ret_sts_unexp_error;
4772 okc_api.set_message (g_app_name,
4773 g_unexpected_error,
4774 g_sqlcode_token,
4775 SQLCODE,
4776 g_sqlerrm_token,
4777 SQLERRM
4778 );
4779 RETURN (l_name);
4780 END get_line_name_if_null;
4781
4782 PROCEDURE get_line_name_if_null (
4783 p_inventory_item_id IN NUMBER,
4784 p_organization_id IN NUMBER,
4785 p_code IN VARCHAR2,
4786 x_return_status OUT NOCOPY VARCHAR2,
4787 x_name OUT NOCOPY VARCHAR2,
4788 x_description OUT NOCOPY VARCHAR2
4789 )
4790 IS
4791 CURSOR l_csr
4792 IS
4793 SELECT application_column_name
4794 FROM fnd_id_flex_segments_vl
4795 WHERE application_id = 401 AND id_flex_code = p_code
4796 ORDER BY segment_num;
4797
4798 CURSOR l_delr_csr (l_structure_code VARCHAR2)
4799 IS
4800 SELECT concatenated_segment_delimiter
4801 FROM fnd_id_flex_structures_vl
4802 WHERE application_id = 401
4803 AND id_flex_code = p_code
4804 AND id_flex_structure_code = l_structure_code;
4805
4806 l_structure_code VARCHAR2 (30);
4807 l_sel_column VARCHAR2 (1000);
4808 l_count NUMBER := 1;
4809 l_select_stmt VARCHAR2 (4000);
4810 l_column VARCHAR2 (600);
4811 l_exe_count INTEGER;
4812 l_cursor_id INTEGER;
4813 l_name VARCHAR2 (800) := NULL;
4814 l_desc VARCHAR2 (800) := NULL;
4815 l_delimiter VARCHAR2 (5);
4816 BEGIN
4817 x_return_status := okc_api.g_ret_sts_success;
4818
4819 IF p_code = 'SERV'
4820 THEN
4821 l_structure_code := 'ORACLE_SERVICE_ITEM_FLEXFIELD';
4822 ELSIF p_code = 'MSTK'
4823 THEN
4824 l_structure_code := 'SYSTEM_ITEMS';
4825 END IF;
4826
4827 OPEN l_delr_csr (l_structure_code);
4828
4829 FETCH l_delr_csr
4830 INTO l_delimiter;
4831
4832 CLOSE l_delr_csr;
4833
4834 OPEN l_csr;
4835
4836 FETCH l_csr
4837 INTO l_column;
4838
4839 LOOP
4840 EXIT WHEN l_csr%NOTFOUND;
4841
4842 IF l_count = 1
4843 THEN
4844 l_sel_column := l_column;
4845 ELSE
4846 l_sel_column :=
4847 l_sel_column
4848 || '||'
4849 || ''''
4850 || l_delimiter
4851 || ''''
4852 || '||'
4853 || l_column;
4854 END IF;
4855
4856 FETCH l_csr
4857 INTO l_column;
4858
4859 l_count := 2;
4860 END LOOP;
4861
4862 CLOSE l_csr;
4863
4864 l_select_stmt :=
4865 'Select '
4866 || l_sel_column
4867 || ' ,Description '
4868 || ' From Mtl_system_items_b
4869 Where Inventory_item_id = :d1 And Organization_id = :d2 ';
4870 l_cursor_id := DBMS_SQL.open_cursor;
4871 DBMS_SQL.parse (l_cursor_id, l_select_stmt, DBMS_SQL.native);
4872 DBMS_SQL.bind_variable (l_cursor_id, ':d1', p_inventory_item_id);
4873 DBMS_SQL.bind_variable (l_cursor_id, ':d2', p_organization_id);
4874 l_exe_count := DBMS_SQL.EXECUTE (l_cursor_id);
4875 DBMS_SQL.define_column (l_cursor_id, 1, l_name, 200);
4876 DBMS_SQL.define_column (l_cursor_id, 2, l_desc, 500);
4877
4878 LOOP
4879 IF DBMS_SQL.fetch_rows (l_cursor_id) = 0
4880 THEN
4881 EXIT;
4882 END IF;
4883
4884 DBMS_SQL.column_value (l_cursor_id, 1, l_name);
4885 DBMS_SQL.column_value (l_cursor_id, 2, l_desc);
4886 END LOOP;
4887
4888 DBMS_SQL.close_cursor (l_cursor_id);
4889 --Dbms_Output.Put_Line('Name: ' || l_name);
4890 --Dbms_Output.Put_Line('Description: ' || l_desc);
4891 --RETURN(l_name);
4892 x_name := l_name;
4893 x_description := l_desc;
4894 EXCEPTION
4895 WHEN OTHERS
4896 THEN
4897 x_return_status := okc_api.g_ret_sts_unexp_error;
4898 x_name := NULL;
4899 x_description := NULL;
4900 okc_api.set_message (g_app_name,
4901 g_unexpected_error,
4902 g_sqlcode_token,
4903 SQLCODE,
4904 g_sqlerrm_token,
4905 SQLERRM
4906 );
4907 --return(l_name);
4908 END get_line_name_if_null;
4909
4910 PROCEDURE oks_get_salesrep (
4911 p_contact_id IN NUMBER DEFAULT NULL,
4912 p_contract_id NUMBER,
4913 x_salesdetails OUT NOCOPY salesrec_type,
4914 x_return_status OUT NOCOPY VARCHAR2,
4915 x_msg_count OUT NOCOPY NUMBER,
4916 x_msg_data OUT NOCOPY VARCHAR2
4917 )
4918 IS
4919 CURSOR l_get_source_csr (p_salesrep_id NUMBER, p_authorg_id NUMBER)
4920 IS
4921 SELECT source_id
4922 FROM jtf_rs_resource_extns re, jtf_rs_salesreps sr
4923 WHERE sr.resource_id = re.resource_id
4924 AND sr.salesrep_id = p_salesrep_id
4925 AND (sr.org_id = p_authorg_id OR p_authorg_id IS NULL);
4926
4927 l_source_id NUMBER;
4928
4929 CURSOR l_salesrep_curs (p_source_id NUMBER)
4930 IS
4931 SELECT ppl.employee_number employee_number, ppl.full_name full_name,
4932 ppl.work_telephone phone, NULL fax,
4933 LOWER (ppl.email_address) email, job.NAME job_title,
4934 loc.address_line_1 address1, loc.address_line_2 address2,
4935 loc.address_line_3 address3, NULL concatenated_address,
4936 loc.town_or_city city, loc.postal_code postal_code,
4937 loc.region_2 state, NULL province, NULL county,
4938 loc.country country, asg.supervisor_id mgr_id,
4939 emp.full_name mgr_name, ppl.business_group_id org_id,
4940 org.NAME org_name, ppl.first_name first_name,
4941 ppl.last_name last_name, ppl.middle_names middle_name,
4942 LOWER (ppl.attribute26) new_email
4943 FROM per_all_people_f ppl,
4944 hr_all_organization_units org,
4945 per_all_assignments_f asg,
4946 per_jobs job,
4947 hr_locations loc,
4948 per_all_people_f emp
4949 WHERE ppl.person_id = p_source_id
4950 AND TRUNC (SYSDATE) BETWEEN ppl.effective_start_date
4951 AND ppl.effective_end_date
4952 AND ppl.employee_number IS NOT NULL
4953 AND ppl.business_group_id = org.organization_id
4954 AND ppl.person_id = asg.person_id
4955 AND asg.primary_flag = 'Y'
4956 AND asg.assignment_type = 'E'
4957 AND TRUNC (SYSDATE) BETWEEN asg.effective_start_date
4958 AND asg.effective_end_date
4959 AND asg.job_id = job.job_id(+)
4960 AND asg.location_id = loc.location_id(+)
4961 AND asg.supervisor_id = emp.person_id(+)
4962 AND TRUNC (SYSDATE) BETWEEN emp.effective_start_date(+) AND emp.effective_end_date(+)
4963 AND NOT EXISTS (
4964 SELECT pep.person_id
4965 FROM per_all_people_f pep, per_all_assignments_f asg1
4966 WHERE pep.person_id = ppl.person_id
4967 AND TRUNC (SYSDATE) BETWEEN pep.effective_start_date
4968 AND pep.effective_end_date
4969 AND pep.employee_number IS NOT NULL
4970 AND pep.person_id = asg1.person_id
4971 AND asg1.primary_flag = 'Y'
4972 AND asg1.assignment_type = 'E'
4973 AND TRUNC (SYSDATE) BETWEEN asg1.effective_start_date
4974 AND asg1.effective_end_date
4975 GROUP BY pep.person_id
4976 HAVING COUNT (pep.person_id) > 1);
4977
4978 l_sales_rec salesrec_type;
4979
4980 CURSOR l_phone_csr (p_source_id NUMBER, p_phone_type VARCHAR2)
4981 IS
4982 SELECT phone_number
4983 FROM per_phones
4984 WHERE parent_id = p_source_id
4985 AND phone_type = p_phone_type
4986 AND parent_table = 'PER_ALL_PEOPLE_F'
4987 AND TRUNC (SYSDATE) BETWEEN date_from AND NVL (date_to, SYSDATE);
4988
4989 -- Following cursor definition was modified to add check for JTOT_OBJECT1_CODE
4990 -- and a check for sales person effectivity dates. If there are multiple active
4991 -- salesrep for a single contract, cursor will return the salesrep who has a
4992 -- start date closest to the current date (sysdate). This requirement was a part
4993 -- of territory stamping project.
4994
4995 -- Cursor l_chr_csr Is
4996 -- select vc.object1_id1
4997 -- ,kh.authoring_org_id
4998 -- From okc_k_headers_b kh
4999 -- ,okc_k_party_roles_B pr
5000 -- ,okc_contacts vc
5001 -- Where kh.id = p_contract_id
5002 -- And pr.chr_id = p_contract_id
5003 -- And vc.dnz_chr_id = p_contract_id
5004 -- And vc.cpl_id = pr.id
5005 -- And pr.rle_code = 'VENDOR'
5006 -- And vc.JTOT_OBJECT1_CODE = 'OKX_SALEPERS'
5007 -- And trunc(sysdate) between nvl(vc.start_date,sysdate-1)
5008 -- and nvl(vc.end_date,sysdate) ;
5009 CURSOR l_chr_csr
5010 IS
5011 SELECT vc.object1_id1, kh.authoring_org_id
5012 FROM okc_k_headers_b kh, okc_k_party_roles_b pr, okc_contacts vc
5013 WHERE kh.ID = p_contract_id
5014 AND pr.chr_id = p_contract_id
5015 AND vc.dnz_chr_id = p_contract_id
5016 AND vc.cpl_id = pr.ID
5017 AND pr.rle_code = 'VENDOR'
5018 AND vc.jtot_object1_code = 'OKX_SALEPERS'
5019 AND vc.ID =
5020 oks_extwar_util_pvt.active_salesrep (kh.ID,
5021 pr.ID,
5022 kh.authoring_org_id
5023 );
5024
5025 l_salesrep_id NUMBER;
5026 l_authorg_id NUMBER;
5027 BEGIN
5028 /** Code modified to add parameter p_contact_id, to handle situation if a
5029 contract does not exist -- 08/03/2001 - aiyengar **/
5030 IF p_contact_id IS NOT NULL
5031 THEN
5032 l_salesrep_id := p_contact_id;
5033 ELSE
5034 OPEN l_chr_csr;
5035
5036 FETCH l_chr_csr
5037 INTO l_salesrep_id, l_authorg_id;
5038
5039 CLOSE l_chr_csr;
5040 END IF;
5041
5042 IF l_salesrep_id IS NULL
5043 THEN
5044 okc_api.set_message (g_app_name,
5045 g_required_value,
5046 g_col_name_token,
5047 'NO CONTACT FOUND FOR CONTRACT ID '
5048 || p_contract_id
5049 );
5050 RAISE g_exception_halt_validation;
5051 END IF;
5052
5053 OPEN l_get_source_csr (l_salesrep_id, l_authorg_id);
5054
5055 FETCH l_get_source_csr
5056 INTO l_source_id;
5057
5058 CLOSE l_get_source_csr;
5059
5060 IF l_source_id IS NULL
5061 THEN
5062 okc_api.set_message (g_app_name,
5063 g_required_value,
5064 g_col_name_token,
5065 'NO SOURCE FOUND FOR SALESREP ' || l_salesrep_id
5066 );
5067 RAISE g_exception_halt_validation;
5068 END IF;
5069
5070 OPEN l_salesrep_curs (l_source_id);
5071
5072 FETCH l_salesrep_curs
5073 INTO x_salesdetails;
5074
5075 CLOSE l_salesrep_curs;
5076
5077 IF x_salesdetails.phone IS NULL
5078 THEN
5079 OPEN l_phone_csr (l_source_id, 'W1');
5080
5081 FETCH l_phone_csr
5082 INTO x_salesdetails.phone;
5083
5084 CLOSE l_phone_csr;
5085 END IF;
5086
5087 IF x_salesdetails.fax IS NULL
5088 THEN
5089 OPEN l_phone_csr (l_source_id, 'WF');
5090
5091 FETCH l_phone_csr
5092 INTO x_salesdetails.fax;
5093
5094 CLOSE l_phone_csr;
5095 END IF;
5096
5097 x_salesdetails.concatenated_address :=
5098 x_salesdetails.address1
5099 || ', '
5100 || x_salesdetails.address2
5101 || x_salesdetails.address3
5102 || ', '
5103 || x_salesdetails.city
5104 || ', '
5105 || x_salesdetails.state
5106 || '-'
5107 || x_salesdetails.postal_code;
5108 x_return_status := 'S';
5109 EXCEPTION
5110 WHEN g_exception_halt_validation
5111 THEN
5112 x_return_status := 'E';
5113 WHEN OTHERS
5114 THEN
5115 x_return_status := 'U';
5116 okc_api.set_message (g_app_name,
5117 g_unexpected_error,
5118 g_sqlcode_token,
5119 SQLCODE,
5120 g_sqlerrm_token,
5121 SQLERRM
5122 );
5123 END;
5124
5125 -------------------------------------------------------------------------------------
5126 PROCEDURE calculate_rev_rec (
5127 p_conc_request_id IN NUMBER,
5128 p_contract_group IN NUMBER,
5129 p_orgid IN NUMBER,
5130 p_forfdate IN DATE,
5131 p_fortdate IN DATE,
5132 p_min IN NUMBER,
5133 p_max IN NUMBER,
5134 p_regz_date IN DATE,
5135 p_curr IN VARCHAR2
5136 )
5137 IS
5138 -- /*
5139 -- CURSOR l_kh_csr IS
5140 -- SELECT kh.currency_code,
5141 -- kh.sts_code,
5142 -- kh.id contract_id,
5143 -- Nvl(rul.rule_information1,0) percent,
5144 -- st.ste_code
5145 -- FROM okc_k_headers_b kh,
5146 -- okc_statuses_b st,
5147 -- okc_rules_b rul
5148 -- WHERE kh.authoring_org_id = NVL(p_orgid,kh.authoring_org_id)
5149 -- AND kh.sts_code = st.code
5150 -- AND st.ste_code = 'ENTERED'
5151 -- AND rul.dnz_chr_id (+) = kh.id
5152 -- AND rul.rule_information_category (+) = 'RVE'
5153 -- AND ((nvl(to_date(rul.rule_information2,'YYYY/MM/DD HH24:MI:SS'),p_ForFDate) >= p_ForFDate)
5154 -- OR
5155 -- (p_ForFDate IS NULL))
5156 -- AND ((nvl(to_date(rul.rule_information2,'YYYY/MM/DD HH24:MI:SS'),p_ForTDate) <= p_ForTDate)
5157 -- OR
5158 -- (p_ForTDate IS NULL))
5159 -- AND nvl(to_date(rul.rule_information2,'YYYY/MM/DD HH24:MI:SS'),p_ForFDate)
5160 -- BETWEEN p_ForFDate And p_ForTDate
5161 -- AND kh.currency_code = NVL(p_curr,kh.currency_code)
5162 -- ORDER BY kh.currency_code,kh.sts_code;
5163 -- */
5164
5165 --CURSOR l_kh_csr IS
5166 -- SELECT kh.currency_code,
5167 -- kh.sts_code,
5168 -- kh.id contract_id,
5169 -- Nvl(rul.est_rev_percent,0) percent,
5170 -- st.ste_code
5171 -- FROM okc_k_headers_b kh,
5172 -- okc_statuses_b st,
5173 -- oks_k_headers_b rul
5174 -- WHERE kh.authoring_org_id = NVL(p_orgid,kh.authoring_org_id)
5175 -- AND kh.scs_code in ('SERVICE','WARRANTY')
5176 -- AND kh.sts_code = st.code
5177 -- AND st.ste_code = 'ENTERED'
5178 -- AND rul.chr_id (+) = kh.id
5179 -- AND ((nvl(EST_REV_DATE,p_ForFDate) >= p_ForFDate)
5180 -- OR
5181 -- (p_ForFDate IS NULL))
5182 -- AND ((nvl(EST_REV_DATE,p_ForTDate) <= p_ForTDate)
5183 -- OR
5184 -- (p_ForTDate IS NULL))
5185 -- AND kh.currency_code = NVL(p_curr,kh.currency_code)
5186 -- AND ((p_Contract_Group IS NULL)
5187 -- OR
5188 -- ( kh.id in ( select INCLUDED_CHR_ID from okc_k_grpings
5189 -- start with CGP_PARENT_ID = p_contract_group
5190 -- connect by CGP_PARENT_ID = PRIOR INCLUDED_CGP_ID )))
5191 -- ORDER BY kh.currency_code,kh.sts_code;
5192
5193 -- To resolve bug#3874970, SQL commented above has been split into 4 SQL's
5194 -- 1. All parameters supplied by the user
5195 CURSOR l_kh_csr1
5196 IS
5197 SELECT kh.currency_code, kh.sts_code, kh.ID contract_id,
5198 NVL (rul.est_rev_percent, 0) PERCENT, st.ste_code
5199 FROM okc_k_headers_b kh,
5200 okc_statuses_b st,
5201 oks_k_headers_b rul,
5202 (SELECT included_chr_id
5203 FROM okc_k_grpings
5204 START WITH cgp_parent_id = p_contract_group
5205 CONNECT BY cgp_parent_id = PRIOR included_cgp_id) grp
5206 WHERE kh.authoring_org_id = p_orgid
5207 AND kh.scs_code IN ('SERVICE', 'WARRANTY')
5208 AND kh.sts_code = st.code
5209 AND st.ste_code = 'ENTERED'
5210 AND rul.chr_id(+) = kh.ID
5211 AND kh.ID = grp.included_chr_id
5212 AND rul.est_rev_date >= p_forfdate
5213 AND rul.est_rev_date <= p_fortdate
5214 AND kh.currency_code = p_curr
5215 ORDER BY kh.currency_code, kh.sts_code;
5216
5217 -- 2. Parameter Org ID not supplied
5218 CURSOR l_kh_csr2
5219 IS
5220 SELECT kh.currency_code, kh.sts_code, kh.ID contract_id,
5221 NVL (rul.est_rev_percent, 0) PERCENT, st.ste_code
5222 FROM okc_k_headers_b kh,
5223 okc_statuses_b st,
5224 oks_k_headers_b rul,
5225 (SELECT included_chr_id
5226 FROM okc_k_grpings
5227 START WITH cgp_parent_id = p_contract_group
5228 CONNECT BY cgp_parent_id = PRIOR included_cgp_id) grp
5229 WHERE kh.scs_code IN ('SERVICE', 'WARRANTY')
5230 AND kh.sts_code = st.code
5231 AND st.ste_code = 'ENTERED'
5232 AND rul.chr_id(+) = kh.ID
5233 AND kh.ID = grp.included_chr_id
5234 AND rul.est_rev_date >= p_forfdate
5235 AND rul.est_rev_date <= p_fortdate
5236 AND kh.currency_code = p_curr
5237 ORDER BY kh.currency_code, kh.sts_code;
5238
5239 -- 3. Parameter Currency code not supplied
5240 CURSOR l_kh_csr3
5241 IS
5242 SELECT kh.currency_code, kh.sts_code, kh.ID contract_id,
5243 NVL (rul.est_rev_percent, 0) PERCENT, st.ste_code
5244 FROM okc_k_headers_b kh,
5245 okc_statuses_b st,
5246 oks_k_headers_b rul,
5247 (SELECT included_chr_id
5248 FROM okc_k_grpings
5249 START WITH cgp_parent_id = p_contract_group
5250 CONNECT BY cgp_parent_id = PRIOR included_cgp_id) grp
5251 WHERE kh.authoring_org_id = p_orgid
5252 AND kh.scs_code IN ('SERVICE', 'WARRANTY')
5253 AND kh.sts_code = st.code
5254 AND st.ste_code = 'ENTERED'
5255 AND rul.chr_id(+) = kh.ID
5256 AND kh.ID = grp.included_chr_id
5257 AND rul.est_rev_date >= p_forfdate
5258 AND rul.est_rev_date <= p_fortdate
5259 ORDER BY kh.currency_code, kh.sts_code;
5260
5261 -- 4. Both, currency code and Org ID are not supplied
5262 CURSOR l_kh_csr4
5263 IS
5264 SELECT kh.currency_code, kh.sts_code, kh.ID contract_id,
5265 NVL (rul.est_rev_percent, 0) PERCENT, st.ste_code
5266 FROM okc_k_headers_b kh,
5267 okc_statuses_b st,
5268 oks_k_headers_b rul,
5269 (SELECT included_chr_id
5270 FROM okc_k_grpings
5271 START WITH cgp_parent_id = p_contract_group
5272 CONNECT BY cgp_parent_id = PRIOR included_cgp_id) grp
5273 WHERE kh.scs_code IN ('SERVICE', 'WARRANTY')
5274 AND kh.sts_code = st.code
5275 AND st.ste_code = 'ENTERED'
5276 AND rul.chr_id(+) = kh.ID
5277 AND kh.ID = grp.included_chr_id
5278 AND rul.est_rev_date >= p_forfdate
5279 AND rul.est_rev_date <= p_fortdate
5280 ORDER BY kh.currency_code, kh.sts_code;
5281
5282 -----
5283 CURSOR l_kl_csr (p_kid NUMBER)
5284 IS
5285 SELECT SUM (price_negotiated)
5286 FROM okc_k_lines_b kl
5287 WHERE kl.dnz_chr_id = p_kid
5288 AND kl.lse_id IN (25, 7, 9, 10, 8, 35, 11)
5289 AND kl.price_negotiated BETWEEN 0 AND 90999999
5290 HAVING SUM (price_negotiated) BETWEEN NVL (p_min, 0)
5291 AND NVL (p_max, 99999999999999999);
5292
5293 CURSOR l_klf_csr (p_kid NUMBER)
5294 IS
5295 -- /*
5296 -- Select
5297 -- Sum ((price_negotiated)/(kl.end_date - kl.start_date+1) *
5298 -- (decode(sign(p_regz_date-kl.end_date),1,kl.end_date,p_regz_date) -
5299 -- decode(sign(p_regz_date+1-kl.start_date),1,kl.start_date,p_regz_date+1)
5300 -- + 1)
5301 -- )
5302 -- From okc_k_lines_b kl
5303 -- Where kl.dnz_chr_id = p_kid And
5304 -- kl.lse_id in (25,7,9,10,8,35,11) And
5305 -- kl.price_negotiated between 0 and 90999999
5306 -- Having Sum(price_negotiated) between p_min and p_max;
5307 -- */
5308 SELECT NVL (SUM ( ( kl.price_negotiated
5309 / CEIL (DECODE (SIGN (end_date - start_date),
5310 0, 1,
5311 (MONTHS_BETWEEN (kl.end_date,
5312 kl.start_date
5313 )
5314 )
5315 )
5316 )
5317 )
5318 * CEIL (DECODE (SIGN (p_regz_date - kl.start_date),
5319 0, 1,
5320 MONTHS_BETWEEN (p_regz_date,
5321 kl.start_date
5322 )
5323 )
5324 )
5325 ),
5326 0
5327 )
5328 FROM okc_k_lines_b kl
5329 WHERE kl.dnz_chr_id = p_kid
5330 AND kl.lse_id IN (25, 7, 9, 10, 8, 35, 11)
5331 AND kl.price_negotiated BETWEEN 0 AND 90999999
5332 AND kl.start_date <= p_regz_date;
5333
5334 CURSOR l_party_csr (l_chr_id NUMBER)
5335 IS
5336 SELECT ven.object1_id1, ven.ID, cust.object1_id1
5337 FROM okc_k_party_roles_b ven, okc_k_party_roles_b cust
5338 WHERE ven.dnz_chr_id = cust.dnz_chr_id
5339 AND ven.dnz_chr_id = l_chr_id
5340 AND ven.cle_id IS NULL
5341 AND ven.rle_code = 'VENDOR'
5342 AND cust.dnz_chr_id = l_chr_id
5343 AND cust.cle_id IS NULL
5344 AND cust.rle_code = 'CUSTOMER';
5345
5346 CURSOR l_salesrep_csr (l_cpl_id NUMBER)
5347 IS
5348 SELECT 'Y'
5349 FROM okc_contacts con
5350 WHERE con.cpl_id = l_cpl_id
5351 AND con.jtot_object1_code = 'OKX_SALEPERS'
5352 AND con.cro_code IN ('SUP_SALES', 'SALESPERSON')
5353 AND TRUNC (SYSDATE) BETWEEN NVL (con.start_date, SYSDATE - 1)
5354 AND NVL (con.end_date, SYSDATE);
5355
5356 TYPE kh_rectype IS RECORD (
5357 currency_code okc_k_headers_b.currency_code%TYPE,
5358 sts_code okc_k_headers_b.sts_code%TYPE,
5359 contract_id okc_k_headers_b.ID%TYPE,
5360 PERCENT VARCHAR2 (50),
5361 ste_code okc_statuses_b.ste_code%TYPE
5362 );
5363
5364 l_kh_rec kh_rectype;
5365 l_curncy_status_prev VARCHAR2 (200) := '~#~';
5366 l_curncy_status_curr VARCHAR2 (200) := '~#~';
5367 l_curncy_prev VARCHAR2 (200);
5368 l_curncy_curr VARCHAR2 (200);
5369 l_status_curr VARCHAR2 (200);
5370 l_status_prev VARCHAR2 (200);
5371 l_ste_curr VARCHAR2 (200);
5372 l_ste_prev VARCHAR2 (200);
5373 l_ven_party NUMBER;
5374 l_cpl_id NUMBER;
5375 l_cust_party NUMBER;
5376 l_salesrep_exists VARCHAR2 (1);
5377 l_revrec_amount NUMBER;
5378 l_kh_amount NUMBER;
5379 l_forecast_amount NUMBER;
5380 l_booking_forecast NUMBER;
5381 l_k_amount NUMBER;
5382 l_klf_amount NUMBER;
5383 l_number_k NUMBER;
5384 l_row_count NUMBER;
5385 -- p_sts_code Varchar2(50);
5386 -- p_currency Varchar2(50);
5387 -- l_firsttime Boolean := TRUE;
5388 -- l_fdate Date;
5389 BEGIN
5390 -- Initialize variables
5391 l_revrec_amount := 0;
5392 l_kh_amount := 0;
5393 l_forecast_amount := 0;
5394 l_booking_forecast := 0;
5395 l_k_amount := 0;
5396 l_klf_amount := 0;
5397 l_number_k := 0;
5398 l_row_count := 0;
5399
5400 DELETE FROM oks_status_forecast
5401 WHERE conc_request_id = p_conc_request_id;
5402
5403 COMMIT;
5404
5405 -- fnd_file.put_line(FND_FILE.LOG, 'P_Orgid: '||P_Orgid) ;
5406 -- fnd_file.put_line(FND_FILE.LOG, 'P_Curr: '||P_Curr) ;
5407 IF p_orgid IS NULL AND p_curr IS NULL
5408 THEN
5409 OPEN l_kh_csr4;
5410 -- fnd_file.put_line(FND_FILE.LOG, 'l_kh_csr4') ;
5411 ELSIF p_orgid IS NULL AND p_curr IS NOT NULL
5412 THEN
5413 OPEN l_kh_csr2;
5414 -- fnd_file.put_line(FND_FILE.LOG, 'l_kh_csr2') ;
5415 ELSIF p_orgid IS NOT NULL AND p_curr IS NULL
5416 THEN
5417 OPEN l_kh_csr3;
5418 -- fnd_file.put_line(FND_FILE.LOG, 'l_kh_csr3') ;
5419 ELSIF p_orgid IS NOT NULL AND p_curr IS NOT NULL
5420 THEN
5421 OPEN l_kh_csr1;
5422 -- fnd_file.put_line(FND_FILE.LOG, 'l_kh_csr1') ;
5423 END IF;
5424
5425 -- For l_kh_rec in l_kh_csr
5426 LOOP
5427 IF l_kh_csr1%ISOPEN
5428 THEN
5429 FETCH l_kh_csr1
5430 INTO l_kh_rec.currency_code, l_kh_rec.sts_code,
5431 l_kh_rec.contract_id, l_kh_rec.PERCENT, l_kh_rec.ste_code;
5432
5433 EXIT WHEN l_kh_csr1%NOTFOUND;
5434 l_row_count := l_kh_csr1%ROWCOUNT;
5435 ELSIF l_kh_csr2%ISOPEN
5436 THEN
5437 FETCH l_kh_csr2
5438 INTO l_kh_rec.currency_code, l_kh_rec.sts_code,
5439 l_kh_rec.contract_id, l_kh_rec.PERCENT, l_kh_rec.ste_code;
5440
5441 EXIT WHEN l_kh_csr2%NOTFOUND;
5442 l_row_count := l_kh_csr2%ROWCOUNT;
5443 ELSIF l_kh_csr3%ISOPEN
5444 THEN
5445 FETCH l_kh_csr3
5446 INTO l_kh_rec.currency_code, l_kh_rec.sts_code,
5447 l_kh_rec.contract_id, l_kh_rec.PERCENT, l_kh_rec.ste_code;
5448
5449 EXIT WHEN l_kh_csr3%NOTFOUND;
5450 l_row_count := l_kh_csr3%ROWCOUNT;
5451 ELSIF l_kh_csr4%ISOPEN
5452 THEN
5453 FETCH l_kh_csr4
5454 INTO l_kh_rec.currency_code, l_kh_rec.sts_code,
5455 l_kh_rec.contract_id, l_kh_rec.PERCENT, l_kh_rec.ste_code;
5456
5457 EXIT WHEN l_kh_csr4%NOTFOUND;
5458 l_row_count := l_kh_csr4%ROWCOUNT;
5459 END IF;
5460
5461 l_curncy_status_prev := l_curncy_status_curr;
5462 l_curncy_prev := l_curncy_curr;
5463 l_status_prev := l_status_curr;
5464 l_ste_prev := l_ste_curr;
5465 l_curncy_status_curr :=
5466 LTRIM (RTRIM (l_kh_rec.currency_code))
5467 || LTRIM (RTRIM (l_kh_rec.sts_code));
5468 l_curncy_curr := l_kh_rec.currency_code;
5469 l_status_curr := l_kh_rec.sts_code;
5470 l_ste_curr := l_kh_rec.ste_code;
5471
5472 IF (l_curncy_status_prev <> l_curncy_status_curr)
5473 AND (l_row_count <> 1)
5474 THEN
5475 --Insert into Forecast Summary Table...
5476 INSERT INTO oks_status_forecast
5477 (conc_request_id, run_flag, run_time, status_type,
5478 status_code, number_of_contracts, contract_value,
5479 rev_rec_value, forecast_value,
5480 booking_forecast, currency
5481 )
5482 VALUES (p_conc_request_id, 1, SYSDATE, l_ste_prev,
5483 --'ENTERED',
5484 l_status_prev, l_number_k, l_kh_amount,
5485 l_revrec_amount, l_forecast_amount,
5486 l_booking_forecast, l_curncy_prev
5487 );
5488
5489 -- /*
5490 -- l_Curncy_Status_Prev := l_Curncy_Status_Curr;
5491 -- l_Curncy_Prev := l_Curncy_Curr;
5492 -- l_Status_Prev := l_Status_Curr;
5493 -- l_Ste_Prev := l_Ste_Curr;
5494 -- */
5495 --Initialize Forecast Summary Varibales...
5496 l_revrec_amount := 0;
5497 l_kh_amount := 0;
5498 l_forecast_amount := 0;
5499 l_booking_forecast := 0;
5500 l_number_k := 0;
5501 END IF;
5502
5503 --Initialize Party and Sales rep cursor attributes
5504 l_ven_party := NULL;
5505 l_cust_party := NULL;
5506 l_cpl_id := NULL;
5507 l_salesrep_exists := NULL;
5508
5509 OPEN l_party_csr (l_kh_rec.contract_id);
5510
5511 FETCH l_party_csr
5512 INTO l_ven_party, l_cpl_id, l_cust_party;
5513
5514 CLOSE l_party_csr;
5515
5516 IF l_ven_party IS NOT NULL AND l_cust_party IS NOT NULL
5517 THEN
5518 OPEN l_salesrep_csr (l_cpl_id);
5519
5520 FETCH l_salesrep_csr
5521 INTO l_salesrep_exists;
5522
5523 CLOSE l_salesrep_csr;
5524
5525 IF l_salesrep_exists IS NOT NULL
5526 THEN
5527 l_k_amount := 0;
5528 l_klf_amount := 0;
5529
5530 OPEN l_kl_csr (l_kh_rec.contract_id);
5531
5532 FETCH l_kl_csr
5533 INTO l_k_amount;
5534
5535 CLOSE l_kl_csr;
5536
5537 OPEN l_klf_csr (l_kh_rec.contract_id);
5538
5539 FETCH l_klf_csr
5540 INTO l_klf_amount;
5541
5542 CLOSE l_klf_csr;
5543
5544 l_kh_amount := l_kh_amount + NVL (l_k_amount, 0);
5545 l_booking_forecast :=
5546 l_booking_forecast
5547 + (NVL (l_k_amount, 0) * l_kh_rec.PERCENT / 100);
5548 l_revrec_amount := l_revrec_amount + NVL (l_klf_amount, 0);
5549 l_forecast_amount :=
5550 l_forecast_amount
5551 + (NVL (l_klf_amount, 0) * l_kh_rec.PERCENT / 100);
5552 l_number_k := l_number_k + 1;
5553 END IF;
5554 END IF;
5555 END LOOP;
5556
5557 IF l_kh_csr1%ISOPEN
5558 THEN
5559 CLOSE l_kh_csr1;
5560 ELSIF l_kh_csr2%ISOPEN
5561 THEN
5562 CLOSE l_kh_csr2;
5563 ELSIF l_kh_csr3%ISOPEN
5564 THEN
5565 CLOSE l_kh_csr3;
5566 ELSIF l_kh_csr4%ISOPEN
5567 THEN
5568 CLOSE l_kh_csr4;
5569 END IF;
5570
5571 INSERT INTO oks_status_forecast
5572 (conc_request_id, run_flag, run_time, status_type,
5573 status_code, number_of_contracts, contract_value,
5574 rev_rec_value, forecast_value, booking_forecast,
5575 currency
5576 )
5577 VALUES (p_conc_request_id, 1, SYSDATE, l_ste_curr, --'ENTERED',
5578 l_status_curr, l_number_k, l_kh_amount,
5579 l_revrec_amount, l_forecast_amount, l_booking_forecast,
5580 l_curncy_curr
5581 );
5582
5583 COMMIT;
5584 EXCEPTION
5585 WHEN OTHERS
5586 THEN
5587 ROLLBACK;
5588 END;
5589
5590 ------------------------------------------------------------------------------------
5591 -- Function returns one active sales rep for a contract and
5592 -- a contract party, VENDOR. If there are multiple active sales reps,
5593 -- function filter records first by start date, then by creation date
5594 -- and then by their names.
5595 FUNCTION active_salesrep (
5596 p_contract_id IN NUMBER,
5597 p_party_id IN NUMBER,
5598 p_org_id IN NUMBER
5599 )
5600 RETURN NUMBER
5601 IS
5602 CURSOR c_sales_rep (
5603 p_contract_id IN NUMBER,
5604 p_party_id IN NUMBER,
5605 p_org_id IN NUMBER
5606 )
5607 IS
5608 SELECT con1.ID
5609 FROM okc_contacts con1, jtf_rs_salesreps salesrep
5610 WHERE con1.dnz_chr_id = p_contract_id
5611 AND jtot_object1_code = 'OKX_SALEPERS'
5612 AND con1.cpl_id = p_party_id
5613 AND TRUNC (SYSDATE) BETWEEN NVL (con1.start_date, SYSDATE - 1)
5614 AND NVL (con1.end_date, SYSDATE)
5615 AND NVL (con1.start_date, TO_DATE (1, 'J')) IN (
5616 SELECT MAX (NVL (con2.start_date, TO_DATE (1, 'J')))
5617 FROM okc_contacts con2
5618 WHERE con2.dnz_chr_id = p_contract_id
5619 AND jtot_object1_code = 'OKX_SALEPERS'
5620 AND con2.cpl_id = p_party_id
5621 AND TRUNC (SYSDATE) BETWEEN NVL (con2.start_date,
5622 SYSDATE - 1
5623 )
5624 AND NVL (con2.end_date,
5625 SYSDATE
5626 ))
5627 AND salesrep.salesrep_id = TO_NUMBER (con1.object1_id1)
5628 AND salesrep.org_id = p_org_id
5629 ORDER BY con1.creation_date DESC, salesrep.NAME ASC;
5630
5631 l_salesrep_id NUMBER;
5632 BEGIN
5633 OPEN c_sales_rep (p_contract_id, p_party_id, p_org_id);
5634
5635 FETCH c_sales_rep
5636 INTO l_salesrep_id;
5637
5638 CLOSE c_sales_rep;
5639
5640 RETURN (l_salesrep_id);
5641 EXCEPTION
5642 WHEN OTHERS
5643 THEN
5644 RETURN (NULL);
5645 END active_salesrep;
5646
5647 -------------------------------------------------------------------------------------
5648 FUNCTION check_already_billed (
5649 p_chr_id IN NUMBER,
5650 p_cle_id IN NUMBER,
5651 p_lse_id IN NUMBER,
5652 p_end_date IN DATE
5653 )
5654 RETURN BOOLEAN
5655 IS
5656 CURSOR l_hdr_bill_cont_lines_csr
5657 IS
5658 SELECT TRUNC (MAX (date_billed_to))
5659 FROM oks_bill_cont_lines
5660 WHERE cle_id IN (
5661 SELECT ID
5662 FROM okc_k_lines_b
5663 WHERE dnz_chr_id = p_chr_id
5664 AND lse_id IN (1, 12, 14, 19));
5665
5666 CURSOR l_bill_cont_line_csr
5667 IS
5668 SELECT TRUNC (MAX (date_billed_to))
5669 FROM oks_bill_cont_lines
5670 WHERE cle_id = p_cle_id;
5671
5672 CURSOR l_bill_sub_line_csr
5673 IS
5674 SELECT TRUNC (MAX (date_billed_to))
5675 FROM oks_bill_sub_lines
5676 WHERE cle_id = p_cle_id;
5677
5678 l_max_dt_billed_to DATE;
5679 BEGIN
5680 fnd_msg_pub.initialize;
5681
5682 ---IF RETURNS FALSE That means it is not billed
5683 ---IF RETURNS true That means it is billed and dates can not be changed.
5684
5685 ------ERROROUT NOCOPY _AD('Check_Billed p_chr_id = ' || p_chr_id);
5686 ------ERROROUT NOCOPY _AD('Check_Billed p_cle_id = ' || p_cle_id);
5687 IF p_chr_id IS NULL AND p_cle_id IS NULL
5688 THEN
5689 RETURN NULL;
5690 ELSIF p_cle_id IS NOT NULL AND p_lse_id IS NULL
5691 THEN
5692 RETURN NULL;
5693 END IF;
5694
5695 IF ( p_cle_id IS NOT NULL
5696 AND (p_lse_id = 1 OR p_lse_id = 12 OR p_lse_id = 14 OR p_lse_id = 19
5697 )
5698 )
5699 THEN --TOP LINE
5700 OPEN l_bill_cont_line_csr;
5701
5702 FETCH l_bill_cont_line_csr
5703 INTO l_max_dt_billed_to;
5704
5705 IF l_bill_cont_line_csr%NOTFOUND
5706 THEN
5707 CLOSE l_bill_cont_line_csr;
5708
5709 RETURN FALSE;
5710 END IF;
5711
5712 CLOSE l_bill_cont_line_csr;
5713 ELSIF p_chr_id IS NOT NULL
5714 THEN ---HEADER
5715 OPEN l_hdr_bill_cont_lines_csr;
5716
5717 FETCH l_hdr_bill_cont_lines_csr
5718 INTO l_max_dt_billed_to;
5719
5720 IF l_hdr_bill_cont_lines_csr%NOTFOUND
5721 THEN
5722 CLOSE l_hdr_bill_cont_lines_csr;
5723
5724 RETURN FALSE;
5725 END IF;
5726
5727 CLOSE l_hdr_bill_cont_lines_csr;
5728 ELSE -----PRODUCT LINE
5729 OPEN l_bill_sub_line_csr;
5730
5731 FETCH l_bill_sub_line_csr
5732 INTO l_max_dt_billed_to;
5733
5734 IF l_bill_sub_line_csr%NOTFOUND
5735 THEN
5736 CLOSE l_bill_sub_line_csr;
5737
5738 RETURN FALSE;
5739 END IF;
5740
5741 CLOSE l_bill_sub_line_csr;
5742 END IF;
5743
5744 ---ERROROUT NOCOPY _AD('l_max_dt_billed_to = ' || l_max_dt_billed_to);
5745 IF p_end_date IS NOT NULL
5746 THEN
5747 IF l_max_dt_billed_to IS NULL
5748 THEN
5749 RETURN FALSE;
5750 ELSIF TRUNC (p_end_date) >= l_max_dt_billed_to
5751 THEN
5752 RETURN FALSE;
5753 ELSE ----TRUNC(p_end_date) < TRUNC(l_max_dt_billed_to)
5754 okc_api.set_message (g_app_name,
5755 'OKS_BILLED_CHECK',
5756 'MAX_BILLED_DATE',
5757 l_max_dt_billed_to
5758 );
5759 RETURN TRUE;
5760 END IF;
5761 ELSE ----- p_end_date IS NULL
5762 IF l_max_dt_billed_to IS NULL
5763 THEN
5764 RETURN FALSE;
5765 ELSE
5766 --OKC_API.set_message(G_APP_NAME,'OKS_BILLED_CHECK','MAX_BILLED_DATE',TO_CHAR(l_max_dt_billed_to,'DD-MON-YYYY HH24:MI:SS'));
5767 okc_api.set_message (g_app_name, 'OKS_BA_UPDATE_NOT_ALLOWED');
5768 RETURN TRUE;
5769 END IF;
5770 END IF;
5771 EXCEPTION
5772 WHEN OTHERS
5773 THEN
5774 RETURN (NULL);
5775 END check_already_billed;
5776 END oks_extwar_util_pvt;