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