[Home] [Help]
PACKAGE BODY: APPS.OKS_IB_UTIL_PVT
Source
1 Package Body OKS_IB_UTIL_PVT As
2 /* $Header: OKSRIBUB.pls 120.46.12000000.2 2007/02/22 21:48:25 dneetha ship $ */
3
4
5
6 -- Constants used for Message Logging
7 G_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
8 G_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
9 G_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
10 G_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
11 G_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
12 G_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
13 G_LEVEL_CURRENT CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
14 G_MODULE_CURRENT CONSTANT VARCHAR2(255) := 'oks.plsql.oks_ib_util_pvt';
15
16
17 FUNCTION party_contact_info(
18 p_object1_code IN VARCHAR2,
19 p_object1_id1 IN VARCHAR2,
20 p_object1_id2 IN VARCHAR2,
21 p_org_id IN NUMBER,
22 p_info_req IN VARCHAR2 --possible values are 'NAME ,PHONE, EMAIL'
23 )
24 RETURN VARCHAR2
25 IS
26
27
28 CURSOR party_contact_kadmin IS
29 SELECT c.last_name || ' ' || c.first_name NAME,
30 c.phone,
31 c.email_address email
32 FROM jtf_rs_resource_extns rsc,
33 ap_supplier_sites_all pvs,
34 hz_party_sites hps,
35 hz_locations hl,
36 po_vendor_contacts c
37 WHERE rsc.CATEGORY = 'SUPPLIER_CONTACT'
38 AND c.vendor_contact_id = rsc.source_id
39 AND pvs.vendor_site_id = c.vendor_site_id
40 AND pvs.org_id = p_org_id
41 AND rsc.resource_id = TO_NUMBER (p_object1_id1)
42 AND '#' = p_object1_id2
43 AND pvs.location_id = hl.location_id(+)
44 AND pvs.party_site_id = hps.party_site_id(+)
45 UNION ALL
46 SELECT emp.full_name NAME,
47 emp.work_telephone phone,
48 emp.email_address email
49 FROM jtf_rs_resource_extns rsc,
50 per_all_people_f emp
51 WHERE rsc.CATEGORY = 'EMPLOYEE'
52 AND emp.person_id = rsc.source_id
53 AND rsc.resource_id = TO_NUMBER (p_object1_id1)
54 AND '#' = p_object1_id2
55 UNION ALL
56 SELECT rsctl.resource_name NAME,
57 NULL phone,
58 srp.email_address email
59 FROM jtf_rs_resource_extns rsc,
60 jtf_rs_resource_extns_tl rsctl, --Bug Fix #5456468 Dneetha
61 jtf_rs_salesreps srp
62 WHERE rsc.CATEGORY = 'OTHER'
63 AND srp.resource_id = rsc.resource_id
64 AND srp.org_id = p_org_id
65 AND rsc.resource_id = TO_NUMBER (p_object1_id1)
66 AND '#' = p_object1_id2
67 AND rsctl.RESOURCE_ID = rsc.RESOURCE_ID -- Bug Fix #5456468 Dneetha
68 AND rsctl.LANGUAGE = USERENV('LANG')
69 AND rsctl.CATEGORY = rsc.CATEGORY
70
71
72 UNION ALL
73 SELECT party.party_name NAME,
74 party.primary_phone_area_code || '-' || party.primary_phone_number
75 phone,
76 party.email_address email
77 FROM jtf_rs_resource_extns rsc,
78 hz_parties party
79 WHERE rsc.CATEGORY IN ('PARTY', 'PARTNER')
80 AND party.party_id = rsc.source_id
81 AND rsc.resource_id = TO_NUMBER (p_object1_id1)
82 AND '#' = p_object1_id2;
83
84
85
86 /* Share memory issue
87 CURSOR party_contact_kadmin IS
88 SELECT C.LAST_NAME ||' '||c.first_name name, c.phone, c.email_address email
89 FROM
90 JTF_RS_RESOURCE_EXTNS RSC ,
91 PO_VENDOR_SITES_ALL S ,
92 PO_VENDOR_CONTACTS C
93 WHERE
94 RSC.CATEGORY = 'SUPPLIER_CONTACT'
95 AND C.VENDOR_CONTACT_ID = RSC.SOURCE_ID
96 AND S.VENDOR_SITE_ID = C.VENDOR_SITE_ID
97 AND S.ORG_ID = p_org_id
98 AND RSC.RESOURCE_ID = to_number(p_object1_id1)
99 AND '#' = p_object1_id2
100 UNION ALL
101 SELECT EMP.FULL_NAME name , emp.work_telephone phone ,emp.email_address email
102 FROM JTF_RS_RESOURCE_EXTNS RSC ,
103 PER_ALL_PEOPLE_F EMP
104 WHERE
105 RSC.CATEGORY = 'EMPLOYEE'
106 AND EMP.PERSON_ID = RSC.SOURCE_ID
107 AND RSC.RESOURCE_ID = to_number(p_object1_id1)
108 AND '#' = p_object1_id2
109 UNION ALL
110 SELECT
111 SRP.NAME name, null phone ,srp.email_address email
112 FROM
113 JTF_RS_RESOURCE_EXTNS RSC ,
114 JTF_RS_SALESREPS SRP
115 WHERE
116 RSC.CATEGORY = 'OTHER'
117 AND SRP.RESOURCE_ID = RSC.RESOURCE_ID
118 AND SRP.ORG_ID = p_org_id
119 AND RSC.RESOURCE_ID = to_number(p_object1_id1)
120 AND '#' = p_object1_id2
121 UNION ALL
122 SELECT party.party_name name, party.primary_phone_area_code ||'-'||party.primary_phone_number phone ,party.email_address email
123 FROM JTF_RS_RESOURCE_EXTNS RSC ,HZ_PARTIES PARTY
124 WHERE RSC.CATEGORY IN ( 'PARTY', 'PARTNER')
125 AND PARTY.PARTY_ID = RSC.SOURCE_ID
126 AND RSC.RESOURCE_ID = to_number(p_object1_id1)
127 AND '#' = p_object1_id2 ;
128 */
129
130 CURSOR party_contact_salesrep IS
131 SELECT
132 RSCTL.Resource_name name, null phone ,srp.email_address email
133 FROM
134 JTF_RS_RESOURCE_EXTNS RSC ,
135 JTF_RS_RESOURCE_EXTNS_tl RSCTL ,-- Bug Fix #5456468 dneetha
136 JTF_RS_SALESREPS SRP
137 WHERE
138 RSC.CATEGORY IN ('EMPLOYEE','OTHER','PARTY','PARTNER','SUPPLIER_CONTACT')
139 AND SRP.RESOURCE_ID = RSC.RESOURCE_ID
140 AND SRP.ORG_ID = p_org_id
141 AND SRP.SALESREP_ID = to_number(p_object1_id1)
142 AND '#' = p_object1_id2
143 AND RSCTL.RESOURCE_ID = RSC.RESOURCE_ID -- Bug Fix #5456468 dneetha
144 AND RSCTL.LANGUAGE = USERENV('LANG')
145 AND RSCTL.CATEGORY = RSC.CATEGORY;
146
147
148
149
150
151
152 l_party_contact_kadmin_rec party_contact_kadmin%rowtype;
153 l_party_contact_salesrep_rec party_contact_salesrep%rowtype;
154 l_party_info VARCHAR2(300);
155
156 BEGIN
157 IF p_object1_code = 'OKX_RESOURCE'
158 THEN
159 OPEN party_contact_kadmin;
160 FETCH party_contact_kadmin
161 INTO
162 l_party_contact_kadmin_rec;
163 CLOSE party_contact_kadmin;
164 If p_info_req = 'NAME' THEN
165 l_party_info:= l_party_contact_kadmin_rec.name;
166 Elsif p_info_req = 'EMAIL' THEN
167 l_party_info:= l_party_contact_kadmin_rec.email;
168 Elsif p_info_req = 'PHONE' THEN
169 l_party_info:= l_party_contact_kadmin_rec.phone;
170 End If;
171 ELSIF p_object1_code = 'OKX_SALEPERS'
172 THEN
173 OPEN party_contact_salesrep;
174 FETCH party_contact_salesrep
175 INTO
176 l_party_contact_salesrep_rec;
177 CLOSE party_contact_salesrep;
178 If p_info_req = 'NAME' THEN
179 l_party_info:= l_party_contact_salesrep_rec.name;
180 Elsif p_info_req = 'EMAIL' THEN
181 l_party_info:= l_party_contact_salesrep_rec.email;
182 Elsif p_info_req = 'PHONE' THEN
183 l_party_info:= l_party_contact_salesrep_rec.phone;
184 End If;
185 END IF;
186 RETURN l_party_info;
187 END party_contact_info;
188
189 FUNCTION check_partial_flag (p_id NUMBER, p_flag VARCHAR2)
190 RETURN VARCHAR2
191 IS
192 CURSOR get_hdr_temp_count
193 IS
194 SELECT COUNT (*)
195 FROM Oks_Instance_k_dtls_temp
196 WHERE contract_id = p_id;
197
198 CURSOR get_line_temp_count
199 IS
200 SELECT COUNT (*)
201 FROM Oks_Instance_k_dtls_temp
202 WHERE topline_id = p_id;
203
204 CURSOR get_hdr_count
205 IS
206 SELECT COUNT (*)
207 FROM okc_k_lines_b
208 WHERE dnz_chr_id = p_id
209 AND lse_id IN (9, 18, 25);
210
211 CURSOR get_line_count
212 IS
213 SELECT COUNT (*)
214 FROM okc_k_lines_b
215 WHERE cle_id = p_id AND lse_id IN (9, 18, 25);
216 l_temp_count NUMBER := 0;
217 l_count NUMBER := 0;
218 BEGIN
219 IF p_flag = 'H'
220 THEN
221 OPEN get_hdr_temp_count;
222 FETCH get_hdr_temp_count INTO l_temp_count;
223 CLOSE get_hdr_temp_count;
224
225 OPEN get_hdr_count;
226 FETCH get_hdr_count INTO l_count;
227 CLOSE get_hdr_count;
228
229 IF l_temp_count < l_count
230 THEN
231 RETURN ('Y');
232 ELSE
233 RETURN ('N');
234 END IF;
235 ELSE
236 OPEN get_line_temp_count;
237 FETCH get_line_temp_count INTO l_temp_count;
238 CLOSE get_line_temp_count;
239
240 OPEN get_line_count;
241 FETCH get_line_count INTO l_count;
242 CLOSE get_line_count;
243
244 IF l_temp_count < l_count
245 THEN
246 RETURN ('Y');
247 ELSE
248 RETURN ('N');
249 END IF;
250 END IF;
251 END;
252
253
254 Procedure Get_prod_name(P_line_id Number, x_prod_name Out NoCopy Varchar2, X_system_name Out NoCopy Varchar2)
255 Is
256
257
258 Cursor l_lse_csr is
259 Select lse_id
260 From OKc_k_lines_b
261 Where id = p_line_id;
262
263 Cursor l_prodSys_name_csr
264 Is
265
266
267 Select mtl.concatenated_segments, sys.name
268 From mtl_system_items_kfv mtl, okc_k_items_v itm
269 ,csi_item_instances csi, csi_systems_tl sys
270 where itm.cle_id = P_line_id
271 And itm.jtot_object1_code = 'OKX_CUSTPROD'
272 And csi.instance_id = itm.object1_id1
273 And csi.inventory_item_id = mtl.inventory_item_id
274 And sys.system_id(+) = csi.system_id
275 And rownum < 2
276 ;
277
278
279
280 Cursor l_counter_csr Is
281 Select mtl.concatenated_segments
282 From mtl_system_items_kfv mtl, csi_counter_associations ctrAsc
283 , okc_k_items_v itm
284 ,csi_item_instances csi
285 Where itm.cle_id = P_line_id
286 And ctrAsc.counter_id = itm.object1_id1
287 And csi.instance_id = ctrAsc.source_object_id
288 And mtl.inventory_item_id = csi.inventory_item_id
289 And rownum < 2
290 ;
291
292 l_prod_name Varchar2(40);
293 l_System_name Varchar2(40);
294
295 l_lse_id Number;
296
297 begin
298 Open l_lse_csr;
299 Fetch l_lse_csr into l_lse_id;
300 Close l_lse_csr;
301
302 If l_lse_id = 13 Then
303 Open l_counter_csr;
304 Fetch l_counter_csr into l_prod_name;
305 Close l_counter_csr;
306 Else
307 Open l_prodSys_name_csr;
308 Fetch l_prodSys_name_csr into l_prod_name, l_system_name;
309 Close l_prodSys_name_csr;
310 End If;
311
312 x_prod_name := l_prod_name;
313 x_system_name := l_system_name;
314
315 End;
316
317
318
319 /* ***********************************************
320 * Get the bill Contact name for the Contract Id. This function
321 * is called in ContractsHdrMasterExpVORowImpl and
322 * ContractHdrTerminateExpVORowImpl
323 */
324
325 Function get_BillContact_name(P_Contract_Id Number) return Varchar2 Is
326
327 Cursor l_bill_contact_csr Is
328 Select Decode(SUBSTR (hz.person_last_name || ','|| hz.person_first_name, 1,255),',',null,SUBSTR (hz.person_last_name || ', '|| hz.person_first_name, 1,255)) Billing_contact
329 From hz_parties hz
330 , okc_contacts oc2
331 ,hz_relationships hr
332 Where oc2.jtot_object1_code = 'OKX_PCONTACT'
333 And oc2.cro_code = 'BILLING'
334 AND oc2.dnz_chr_id = p_contract_id
335 AND hr.party_id = oc2.object1_id1
336 AND hz.party_id = hr.subject_id;
337
338 l_bill_contact Varchar2(240);
339
340 Begin
341 For l_contact_rec in l_bill_contact_csr
342 Loop
343 l_bill_contact := l_contact_rec.Billing_contact;
344 Exit when l_bill_contact is not null;
345 End Loop;
346
347
348
349 return(l_bill_contact);
350
351 End;
352
353 /* ***********************************************
354 * Get the Salesperson name for the Contract Id. This function
355 * is called in ContractsHdrMasterExpVORowImpl and
356 * ContractHdrTerminateExpVORowImpl
357 */
358 Function get_salesrep_name(P_Contract_Id Number) return Varchar2 Is
359
360 Cursor l_salesrep_csr Is
361 Select v.resource_name sales_person
362 From jtf_rs_salesreps jtf
363 , jtf_rs_resource_extns_vl v
364 , okc_contacts oc1
365 ,Okc_k_headers_all_b kh
366
367 Where oc1.dnz_chr_id = P_contract_id
368 And Kh.id = oc1.dnz_chr_id
369 And Kh.authoring_org_id = jtf.org_id
370 AND oc1.jtot_object1_code = 'OKX_SALEPERS'
371 AND oc1.object1_id1 = jtf.salesrep_id
372 And v.resource_id = jtf.resource_id;
373
374
375
376 l_salesperson varchar2(240);
377 Begin
378
379 For l_salesperson_rec in l_salesrep_csr
380 Loop
381
382 l_salesperson := l_salesperson_rec.sales_person;
383 exit when l_salesperson is not null;
384
385 End Loop;
386
387 Return (l_salesperson);
388
389
390 End;
391
392
393
394 FUNCTION round_currency_amt ( P_amount IN NUMBER ,
395 P_currency_code IN Varchar2 ) RETURN NUMBER
396 IS
397
398 Cursor fnd_cur IS
399 SELECT Minimum_Accountable_Unit,
400 Precision,
401 Extended_Precision
402 FROM FND_CURRENCIES
403 WHERE Currency_Code = P_currency_code;
404
405 l_mau FND_CURRENCIES.MINIMUM_ACCOUNTABLE_UNIT%TYPE;
406 l_sp FND_CURRENCIES.PRECISION%TYPE;
407 l_ep FND_CURRENCIES.EXTENDED_PRECISION%TYPE;
408
409 BEGIN
410 open fnd_cur;
411 fetch fnd_cur into l_mau,l_sp,l_ep;
412 close fnd_cur;
413
414 IF l_mau IS NOT NULL THEN
415
416 IF l_mau < 0.00001 THEN
417 RETURN( round(P_Amount, 5));
418 ELSE
419 RETURN( round(P_Amount/l_mau) * l_mau );
420 END IF;
421
422 ELSIF l_sp IS NOT NULL THEN
423
424 IF l_sp > 5 THEN
425 RETURN( round(P_Amount, 5));
426 ELSE
427 RETURN( round(P_Amount, l_sp));
428 END IF;
429
430 ELSE
431
432 RETURN( round(P_Amount, 5));
433
434 END IF;
435
436 END round_currency_amt;
437
438 /* ***********************************************
439 * Get the transferred amount to be diaplayed
440 * in the impacted contracts region for transfer batch
441 **************************************************/
442
443
444 FUNCTION get_transferred_amount (
445 p_line_id IN NUMBER,
446 p_transfer_date IN DATE
447 )
448 RETURN NUMBER
449 IS
450 CURSOR l_covln_detls
451 IS
452 SELECT Kl.start_date,
453 Kl.end_date,
454 nvl(Kl.price_negotiated,0),
455 Kl.currency_code,
456 Ks.price_uom,
457 Kh.period_start,
458 Kh.period_type
459
460 FROM okc_k_lines_b Kl
461 ,Oks_k_headers_b Kh
462 ,oks_k_lines_b Ks
463
464 WHERE Kl.ID = p_line_id
465 And Ks.cle_id = Kl.Id
466 And Kh.chr_id = kl.dnz_chr_id
467
468
469 ;
470
471 l_start_date Date;
472 l_end_date Date;
473 l_amount Number;
474 l_currency Varchar2(30);
475 l_trfdt Date;
476 l_newamt Number;
477 l_price_uom Varchar2(10);
478
479 l_period_start Varchar2(30);
480 l_period_type Varchar2(10);
481 l_duration_xfer Number;
482 l_duration_total Number;
483
484
485 BEGIN
486
487 Open l_covln_detls;
488 Fetch l_covln_detls into l_start_date, l_end_date, l_amount, l_currency,l_price_uom, l_period_start, l_period_type;
489 Close l_covln_detls;
490
491 If trunc(p_transfer_date) > trunc(l_end_date) Then
492
493 l_newamt := 0;
494 Else
495
496 If trunc(l_start_date) > trunc(P_transfer_date) Then
497 l_trfdt := l_start_date;
498 Else
499 l_trfdt := p_transfer_date;
500 End If;
501 If l_price_uom is Null Then
502 l_price_uom := Oks_misc_util_web.duration_unit
503 (
504 trunc(l_start_Date),
505 trunc(l_end_date));
506 End If;
507
508 l_duration_xfer := OKS_TIME_MEASURES_PUB.get_quantity
509 (trunc(l_trfdt) ,
510 trunc(l_end_date),
511 l_price_uom,
512 l_period_type ,
513 l_period_Start );
514
515
516
517 l_duration_total := OKS_TIME_MEASURES_PUB.get_quantity
518 (trunc(l_Start_date) ,
519 trunc(l_end_date),
520 l_price_uom,
521 l_period_type ,
522 l_period_Start );
523
524
525 l_newamt := oks_extwar_util_pvt.round_currency_amt(l_amount * l_duration_xfer/l_duration_total,l_currency);
526
527 End if;
528 return (nvl(l_newamt ,0));
529
530
531
532
533
534
535
536 END;
537
538 /* ***********************************************
539 * Get the actual transferred amount to be diaplayed
540 * in the impacted contracts region for completed transfer batch
541 **************************************************/
542
543 Function Get_actual_transferamount(p_line_id Number, P_batch_id Number, P_line_type Varchar2) return Number Is
544
545 Cursor l_amt_csr Is
546 Select nvl(instance_amt_new,0)
547 From Oks_instance_history ih
548 , Oks_inst_hist_details id
549 Where ih.batch_id = p_batch_id
550 And id.ins_id = ih.id
551 And id.old_subline_id = p_line_id
552 And id.old_subline_id <> id.new_subline_id;
553
554 Cursor l_line_amt_csr Is
555 Select nvl(sum(instance_amt_new),0)
556 From Oks_instance_history ih
557 , Oks_inst_hist_details id
558 Where ih.batch_id = p_batch_id
559 And id.ins_id = ih.id
560 And id.old_service_line_id = p_line_id
561 And id.old_service_line_id <> id.new_service_line_id ;
562
563
564 Cursor l_Hdr_amt_csr Is
565 Select sum(instance_amt_new)
566 From Oks_instance_history ih
567 , Oks_inst_hist_details id
568 Where ih.batch_id = p_batch_id
569 And id.ins_id = ih.id
570 And id.old_contract_id = p_line_id
571 And id.old_contract_id <> id.new_contract_id ;
572
573
574
575 l_transfer_amount Number;
576 Begin
577 If P_line_type = 'SL' Then
578 Open l_amt_csr;
579 Fetch l_amt_csr into l_transfer_amount;
580 Close l_amt_csr;
581
582 Elsif P_line_type = 'TL' Then
583
584 Open l_line_amt_csr;
585 Fetch l_line_amt_csr into l_transfer_amount;
586 Close l_line_amt_csr;
587
588
589 Else
590 Open l_hdr_amt_csr;
591 Fetch l_hdr_amt_csr into l_transfer_amount;
592 Close l_hdr_amt_csr;
593
594 End If;
595 l_transfer_amount := nvl(l_transfer_amount,0);
596
597
598 Return (l_transfer_amount);
599
600 End;
601
602
603 /* ***********************************************
604 * Get the actual credit amount to be diaplayed
605 * in the impacted contracts region for completed
606 * terminate/transfer batch
607 **************************************************/
608 Function Get_actual_creditamount(p_line_id Number, P_batch_id Number, P_line_type Varchar2) return Number Is
609 Cursor l_credit_csr Is
610 Select NVL (SUM (bill.amount), 0)
611 From Oks_bill_sub_lines bill
612 , Oks_instance_history ih
613 , Oks_inst_hist_details id
614 , Oks_bill_cont_lines bcl
615 Where bill.cle_id = id.old_subline_id
616 And ih.batch_id = p_batch_id
617 And id.ins_id = ih.id
618 And id.old_subline_id = p_line_id
619 And id.old_subline_id = id.new_subline_id
620 And bill.bcl_id = bcl.id
621 And bcl.bill_action = 'TR'
622 And nvl(bcl.btn_id,0) <> -44;
623
624 Cursor l_tl_credit_csr Is
625
626 Select nvl(sum(bill.amount),0)
627 From Oks_bill_sub_lines bill
628 , Oks_instance_history ih
629 , Oks_inst_hist_details id
630 , Okc_k_lines_b Kl
631 , Oks_bill_cont_lines bcl
632 Where ih.batch_id = p_batch_id
633 And id.ins_id = ih.id
634 And bill.cle_id = Kl.Id
635 And Kl.cle_Id = id.old_service_line_id
636 And Kl.id = id.old_subline_id
637 And id.old_subline_id = id.new_subline_id
638 And id.old_service_line_id = p_line_id
639 And bill.bcl_id = bcl.id
640 And bcl.bill_action = 'TR'
641 And nvl(bcl.btn_id,0) <> -44;
642
643 Cursor l_Hdr_credit_csr Is
644 Select nvl(sum(bill.amount),0)
645 From Okc_k_lines_b kl
646 , Oks_bill_sub_lines bill
647 , Oks_instance_history ih
648 , Oks_inst_hist_details id
649 , Oks_bill_cont_lines bcl
650 Where ih.batch_id = p_batch_id
651 And id.ins_id = ih.id
652 And bill.cle_id = kl.Id
653 And Kl.dnz_chr_id = id.old_contract_id
654 And Kl.id = id.old_subline_id
655 And id.old_subline_id = id.new_subline_id
656 And id.old_contract_id = p_line_id
657 And bill.bcl_id = bcl.id
658 And bcl.bill_action = 'TR'
659 And nvl(bcl.btn_id,0) <> -44;
660
661
662
663
664 l_credit_amount Number;
665
666 Begin
667
668 If P_line_type = 'SL' Then
669 Open l_Credit_csr;
670 Fetch l_credit_csr into l_credit_amount;
671 Close l_credit_csr;
672
673 return (abs(l_credit_amount));
674 Elsif P_line_type = 'TL' Then
675 Open l_Tl_Credit_csr;
676 Fetch l_Tl_Credit_csr into l_credit_amount;
677 Close l_Tl_Credit_csr;
678
679 return (abs(l_credit_amount));
680
681
682
683 Else
684 Open l_hdr_Credit_csr;
685 Fetch l_hdr_Credit_csr into l_credit_amount;
686 Close l_hdr_Credit_csr;
687
688 return (abs(l_credit_amount));
689
690
691 End If;
692
693 End;
694
695
696 /* ***********************************************
697 * Get the actual billed amount to be displayed
698 * in the impacted contracts region for completed
699 * terminate/transfer batch
700 **************************************************/
701 Function Get_actual_billedamount(p_line_id Number, P_batch_id Number, P_line_type Varchar2) return Number Is
702
703 Cursor l_billed_csr Is
704 Select NVL (SUM (bill.amount), 0)
705 From Oks_bill_sub_lines bill
706 , Oks_instance_history ih
707 , Oks_inst_hist_details id
708 , Oks_bill_cont_lines bcl
709 Where bill.cle_id = id.old_subline_id
710 And ih.batch_id = p_batch_id
711 And id.ins_id = ih.id
712 And id.old_subline_id = p_line_id
713 And bill.bcl_id = bcl.id
714 And bcl.bill_action = 'RI';
715
716 Cursor l_tl_billed_csr Is
717
718 Select nvl(sum(bill.amount),0)
719 From Oks_bill_sub_lines bill
720 , Oks_instance_history ih
721 , Oks_inst_hist_details id
722 , Okc_k_lines_b Kl
723 , Oks_bill_cont_lines bcl
724 Where ih.batch_id = p_batch_id
725 And id.ins_id = ih.id
726 And bill.cle_id = Kl.Id
727 And Kl.cle_Id = id.old_service_line_id
728 And Kl.id = id.old_subline_id
729 And id.old_service_line_id = p_line_id
730 And bill.bcl_id = bcl.id
731 And bcl.bill_action = 'RI';
732
733 Cursor l_Hdr_billed_csr Is
734 Select nvl(sum(bill.amount),0)
735 From Okc_k_lines_b kl
736 , Oks_bill_sub_lines bill
737 , Oks_instance_history ih
738 , Oks_inst_hist_details id
739 , Oks_bill_cont_lines bcl
740 Where ih.batch_id = p_batch_id
741 And id.ins_id = ih.id
742 And bill.cle_id = kl.Id
743 And Kl.dnz_chr_id = id.old_contract_id
744 And Kl.id = id.old_subline_id
745 And id.old_contract_id = p_line_id
746 And bill.bcl_id = bcl.id
747 And bcl.bill_action = 'RI';
748
749
750 l_billed_amount Number;
751
752 Begin
753
754 If P_line_type = 'SL' Then
755 Open l_billed_csr;
756 Fetch l_billed_csr into l_billed_amount;
757 Close l_billed_csr;
758
759 return (l_billed_amount);
760 Elsif P_line_type = 'TL' Then
761 Open l_Tl_billed_csr;
762 Fetch l_Tl_billed_csr into l_billed_amount;
763 Close l_Tl_billed_csr;
764
765 return (l_billed_amount);
766
767
768
769 Else
770 Open l_hdr_billed_csr;
771 Fetch l_hdr_billed_csr into l_billed_amount;
772 Close l_hdr_billed_csr;
773
774 return (l_billed_amount);
775
776
777 End If;
778
779 End;
780
781 /* ***********************************************
782 * Get the billed amount to be displayed
783 * in the impacted contracts region for transfer/terminate
784 * batch
785 **************************************************/
786 FUNCTION get_billed_amount (
787 p_line_id IN NUMBER
788
789 )
790 RETURN NUMBER
791 IS
792 CURSOR l_billed_amount_csr (p_cle_id NUMBER)
793 IS
794 SELECT NVL (SUM (amount), 0)
795 FROM oks_bill_sub_lines_v
796 WHERE cle_id = p_cle_id;
797
798 l_billed_amt NUMBER;
799
800 BEGIN
801 OPEN l_billed_amount_csr (p_line_id);
802 FETCH l_billed_amount_csr INTO l_billed_amt;
803 CLOSE l_billed_amount_csr;
804 RETURN( l_billed_amt );
805 END;
806
807
808
809 Function get_terminate_amount(P_line_id Number, p_termination_date date)
810 return Number is
811
812 l_amount Number;
813 l_return_status Varchar2(1);
814 Cursor l_chr_csr Is
815 Select authoring_org_id,
816 inv_organization_id
817 From okc_k_headers_all_b kh
818 , okc_k_lines_b kl
819 Where kl.id = p_line_id
820 And kh.id = kl.dnz_chr_id;
821
822 l_org_id Number;
823 l_inv_org_id Number;
824
825 Begin
826
827 Open l_chr_csr;
828 Fetch l_chr_csr into l_org_id, l_inv_org_id;
829 Close l_chr_csr;
830 okc_context.set_okc_org_context (l_org_id, l_inv_org_id);
831
832 OKS_BILL_REC_PUB.pre_terminate_amount
833 (
834 p_id => P_line_id,
835 p_terminate_date => trunc(P_termination_date),
836 p_flag => 3,
837 X_Amount => l_amount,
838 X_return_status => l_return_status
839 );
840 l_amount := nvl(l_amount,0);
841 return (l_amount);
842
843 End;
844 /* ***********************************************
845 * Procedure to check if impacted COntracts belong
846 * to multiple operating Units
847 **************************************************/
848
849 Procedure CheckMultipleOU(P_Batch_ID Number, p_new_account_id Number, x_party_id Out NOCOPY Number, x_org_id Out NOCOPY Number) Is
850
851
852
853 Cursor get_ou_csr Is
854 Select distinct(okc.authoring_org_id) org_id
855 From Okc_k_headers_all_b okc, Oks_Instance_k_dtls_temp tmp
856 where tmp.Contract_id = okc.Id
857 And tmp.Parent_id = p_batch_Id;
858
859 Cursor l_party_csr Is
860 Select party_id
861 From HZ_CUST_ACCOUNTS CA
862 Where CA.Cust_account_id = p_new_account_Id;
863 i Number;
864 l_org_id number;
865 l_party_id number;
866 Begin
867
868 Open l_party_csr;
869 Fetch l_party_csr into l_party_id;
870 Close l_party_csr;
871 X_party_Id := l_party_id;
872
873
874
875 i := 0;
876 For ou_rec in get_ou_csr
877 Loop
878
879 l_org_id := ou_rec.org_id;
880 i:= i +1 ;
881 If i > 1 Then
882
883 l_org_id := -1;
884 exit;
885 End If;
886 End Loop;
887
888 If i = 0 Then
889
890 l_org_id := 0;
891 End if;
892
893 x_org_id := l_org_id;
894
895
896 End;
897
898
899 Function Credit_option return Varchar2
900 Is
901
902 Cursor KDefld_Global_CreditOption
903 Is
904 Select Credit_amount
905 From OKS_K_DEFAULTS
906 Where cdt_type = 'MDT'
907 AND segment_id1 IS NULL
908 AND segment_id2 IS NULL
909 AND jtot_object_code IS NULL;
910 l_credit_amount Varchar2(30);
911
912 Begin
913 l_credit_amount := null;
914
915 Open KDefld_Global_CreditOption;
916 Fetch KDefld_Global_CreditOption into l_credit_amount;
917 Close KDefld_Global_CreditOption;
918
919
920
921 If l_credit_amount Is null Then
922
923 If Nvl(fnd_profile.value('OKS_RAISE_CREDIT_MEMO_FOR_IB_INT'),'YES') = 'YES' Then
924 l_credit_amount := 'CALCULATED';
925 Else
926 l_credit_amount := 'NONE';
927 End If;
928
929
930 End If;
931
932 return (l_credit_amount);
933 End;
934
935
936
937
938
939 /* ***********************************************
940 * Procudure to validate the bill to and ship to
941 * details passed by IB in Contract Options page.
942 * Validates if the bill to and ship to belong to the OU
943 **************************************************/
944 Procedure GetBillToShipTo(P_New_account_id Number,
945 P_BillTo_account_Id Number default Null,
946 P_BillTo_Address_Id Number default Null,
947 P_ShipTo_account_Id Number default Null,
948 P_ShipTo_Address_Id Number default Null,
949 P_Operating_unit Number,
950 X_BillTo_account_Number Out NOCOPY VARCHAR2,
951 X_BillTo_account_Id Out NOCOPY Number,
952 X_BillTo_Party Out NOCOPY Varchar2,
953 X_BillTo_PartyId Out NOCOPY Number,
954 X_BillTo_Address_Id Out NOCOPY Number,
955 X_BillTo_Address Out NOCOPY Varchar2,
956 X_ShipTo_account_Number Out NOCOPY VARCHAR2,
957 X_ShipTo_account_Id Out NOCOPY Number,
958 X_ShipTo_Party Out NOCOPY Varchar2,
959 X_ShipTo_PartyId Out NOCOPY Number,
960 X_ShipTo_Address_Id Out NOCOPY Number,
961 X_ShipTo_Address Out NOCOPY Varchar2,
962 X_Contract_status_Code Out NOCOPY Varchar2,
963 X_Contract_status Out NOCOPY Varchar2,
964 X_Party_ID OUT NOCOPY Number,
965 X_Credit_option OUT NOCOPY Varchar2,
966 P_Transaction_date Date default sysdate
967 ) Is
968
969 Cursor Check_Acct_Csr( P_Account_id Number, p_party_id number) Is
970 SELECT CA1.Account_number AccountNumber
971 , CA1.CUST_ACCOUNT_ID AccountId,
972 Party.party_name PartyName
973 , Party.party_id PartyId
974 From HZ_CUST_ACCOUNTS CA1
975 , HZ_PARTIES party
976 WHERE CA1.party_id = P_party_id
977 And CA1. cust_account_id = p_account_id
978 And CA1.party_id = party.party_id
979 And CA1.status = 'A'
980
981 UNION
982
983 SELECT CA2.Account_number AccountNumber
984 , CA2.cust_account_id AccountId
985 , Party1.party_name PartyName
986 , Party1.party_id PartyId
987 FROM HZ_CUST_ACCOUNTS CA2
988 , HZ_PARTIES party1
989 , HZ_CUST_ACCT_RELATE_ALL A
990 , HZ_CUST_ACCOUNTS B
991 WHERE CA2.party_id = party1.party_id
992 And CA2.cust_account_id = A.RELATED_CUST_ACCOUNT_ID
993 And B.CUST_ACCOUNT_ID = A.CUST_ACCOUNT_ID
994 And Ca2.cust_account_id = p_account_id
995 And B.party_id = p_party_id and B.status = 'A'
996 And A.status = 'A'
997 And A.org_id = p_operating_unit
998 And CA2.status = 'A';
999
1000
1001
1002 Cursor l_party_csr Is
1003 Select party_id
1004 From HZ_CUST_ACCOUNTS CA
1005 Where CA.Cust_account_id = p_new_account_Id;
1006
1007
1008
1009 Cursor Check_address_csr(l_site_use_id Number,l_party_id Number, l_account_id number, l_org_id number, l_site_use_code varchar2) Is
1010 Select Cs.Site_Use_Id
1011 , Arp_Addr_Label_Pkg.Format_Address(Null,L.Address1,L.Address2,L.Address3, L.Address4, L.City, L.County, L.State, L.Province, L.Postal_Code, Null, L.Country, Null, Null, Null, Null, Null, Null, Null, 'N', 'N', 300, 1, 1) Address
1012 From Hz_Party_Sites Ps,Hz_Locations L
1013 ,Hz_Cust_Acct_Sites_All Ca,
1014 Hz_Cust_Site_Uses_All Cs
1015 Where Ps.Location_Id = L.Location_Id
1016 And L.Content_Source_Type = 'USER_ENTERED'
1017 And Ps.Party_Site_Id = Ca.Party_Site_Id
1018 And Ca.Cust_Acct_Site_Id = Cs.Cust_Acct_Site_Id
1019 And Ps.Party_Id = l_party_id
1020 And Ca.Cust_Account_Id = l_account_id
1021 And Cs.Site_Use_Code = l_site_use_code
1022 And Nvl (ca.Org_Id, -99) = l_org_id
1023 And Cs.site_use_id = l_site_use_id
1024 And Cs.Status = 'A'
1025 And Trunc(Sysdate) Between Nvl(Trunc(Ps.Start_Date_Active),
1026 Trunc(Sysdate)) And Nvl(Trunc(Ps.End_Date_Active), Trunc(Sysdate)) ;
1027 --And Ca.Cust_Acct_Site_Status = 'A';
1028
1029
1030
1031 Cursor l_contract_status_csr Is
1032 Select code,Meaning
1033 From Okc_statuses_v
1034 Where Ste_code = 'ENTERED'
1035 and default_yn = 'Y';
1036
1037
1038 l_bill_account_rec check_acct_csr%rowtype;
1039 l_ship_account_rec check_acct_csr%rowtype;
1040 l_bill_address_rec check_address_csr%rowtype;
1041 l_ship_address_rec check_address_csr%rowtype;
1042 l_party_id number;
1043
1044
1045 Begin
1046
1047
1048 Open l_party_csr;
1049 Fetch l_party_csr into l_party_id;
1050 Close l_party_csr;
1051 x_credit_option := Credit_option;
1052
1053 X_party_Id := l_party_id;
1054
1055 Open l_contract_status_csr;
1056 Fetch l_contract_status_csr into X_Contract_status_code, X_contract_status;
1057 Close l_contract_status_csr;
1058 If P_Operating_unit Is not null Then
1059 If p_billto_Account_id is not null Then
1060 Open check_acct_csr(P_Billto_account_id, l_party_id);
1061 Fetch check_acct_csr into l_bill_account_rec;
1062 If check_acct_csr%found Then
1063 X_BillTo_account_Number := l_bill_account_rec.AccountNumber;
1064 X_BillTo_account_Id := l_bill_account_rec.AccountId;
1065 X_BillTo_Party := l_bill_account_rec.PartyName;
1066 X_BillTo_PartyId := l_bill_account_rec.PartyId;
1067 End If;
1068 Close check_acct_csr;
1069 Open check_address_csr(P_BillTo_Address_Id, l_bill_account_rec.partyid,l_bill_account_rec.AccountId,p_operating_unit, 'BILL_TO');
1070 Fetch check_address_csr into l_bill_address_rec;
1071 If check_address_csr%found then
1072
1073 X_BillTo_Address_Id := l_bill_address_rec.site_use_id;
1074 X_BillTo_Address := l_bill_address_rec.Address;
1075 End If;
1076 Close check_address_csr;
1077 End If;
1078
1079 If P_Shipto_account_id is not null Then
1080 Open check_acct_csr(P_Shipto_account_id, l_party_id);
1081 Fetch check_acct_csr into l_Ship_account_rec;
1082 If check_acct_csr%found Then
1083 X_ShipTo_account_Number := l_Ship_account_rec.AccountNumber;
1084 X_ShipTo_account_Id := l_Ship_account_rec.AccountId;
1085 X_ShipTo_Party := l_Ship_account_rec.PartyName;
1086 X_ShipTo_PartyId := l_Ship_account_rec.PartyId;
1087 End If;
1088 Close check_acct_csr;
1089
1090
1091 Open check_address_csr(P_ShipTo_Address_Id, l_Ship_account_rec.partyid,l_Ship_account_rec.AccountId,p_operating_unit, 'SHIP_TO');
1092 Fetch check_address_csr into l_Ship_address_rec;
1093 If check_address_csr%found then
1094
1095 X_ShipTo_Address_Id := l_Ship_address_rec.site_use_id;
1096 X_ShipTo_Address := l_Ship_address_rec.Address;
1097 End If;
1098 Close check_address_csr;
1099 End If;
1100 End If;
1101
1102 End;
1103
1104 Function CheckAccount(P_batch_id Number, p_new_account_id Number) return Varchar2 Is
1105 Cursor l_batch_csr Is
1106 Select 'Y'
1107 From Oks_batch_rules
1108 Where new_account_id = P_new_account_id
1109 And batch_id = p_batch_id;
1110
1111 l_account_yn Varchar2(1);
1112
1113 Begin
1114 l_account_yn := 'N';
1115 Open l_batch_csr;
1116 Fetch l_batch_csr into l_account_yn;
1117 Close l_batch_csr;
1118
1119 If l_account_yn = 'Y' Then
1120 return ('N');
1121 Else
1122 return ('Y');
1123 End If;
1124
1125
1126 End;
1127
1128 /* ***********************************************
1129 * FUnction to get the coverage terminate amount
1130 **************************************************/
1131 Function Coverage_terminate_amount
1132 (P_line_id Number
1133 ,P_transfer_option Varchar2
1134 , p_new_account_id Number
1135 , p_transfer_date Date
1136 , p_instance_id Number
1137 ) return number Is
1138
1139 Cursor l_cust_rel_csr
1140 (p_old_customer Number
1141 , p_new_customer Number
1142 , p_relation Varchar2
1143 , p_transfer_date Date)
1144 Is
1145
1146 Select distinct relationship_type
1147 From Hz_relationships
1148 Where ((object_id = p_new_customer And subject_id = p_old_customer)
1149 Or (object_id = p_old_customer And subject_id = p_new_customer))
1150 and relationship_type = p_relation
1151 And status = 'A'
1152 and trunc(p_transfer_date) between trunc(start_date) and trunc(end_date)
1153 ;
1154 Cursor l_party_csr(p_cust_id Number) Is
1155 Select party_id
1156 From OKX_CUSTOMER_ACCOUNTS_V
1157 Where id1 = p_cust_id;
1158
1159 Cursor l_instance_csr Is
1160 Select owner_party_id
1161 From Csi_item_instances
1162 Where instance_id = p_instance_id;
1163
1164 l_new_party_id Number;
1165 l_old_party_id Number;
1166 l_relationship Varchar2(2000);
1167 l_relationship_type Varchar2(2000);
1168
1169 Begin
1170
1171
1172 If P_transfer_option in ('TERM_NO_REL','TRANS_NO_REL') Then
1173
1174 l_relationship_type := fnd_profile.value('OKS_TRF_PARTY_REL');
1175
1176 Open l_party_csr(p_new_account_id);
1177 Fetch l_party_csr into l_new_party_id;
1178 Close l_party_csr;
1179
1180 Open l_instance_csr;
1181 Fetch l_instance_csr into l_old_party_id;
1182 Close l_instance_csr;
1183
1184
1185 Open l_cust_rel_csr(l_old_party_id,l_new_party_id,l_relationship_type,p_transfer_date);
1186 Fetch l_cust_rel_csr into l_relationship;
1187 Close l_cust_rel_csr;
1188
1189 If l_relationship Is Not Null Then
1190 return (0);
1191 Else
1192 return(get_terminate_amount(P_line_id,p_transfer_date));
1193 --return (200);
1194
1195 End If;
1196 Elsif P_transfer_option in ('TERM', 'TRANS') Then
1197 return(get_terminate_amount(P_line_id,p_transfer_date));
1198 -- return (200);
1199
1200 Else
1201
1202 return (0);
1203 End If;
1204
1205 End;
1206
1207
1208 Function get_full_terminate_amount
1209 (P_line_id Number,
1210 P_transaction_date Date,
1211 P_line_End_date Date
1212 ) return Number Is
1213 Cursor l_line_csr Is
1214 Select end_date
1215 From Okc_k_lines_b
1216 Where id = p_line_id;
1217
1218 Begin
1219
1220
1221 If trunc(P_line_End_date) < trunc(P_transaction_date) Then
1222 return(0);
1223 Else
1224
1225 return (get_billed_amount(p_line_id));
1226 End If;
1227 End;
1228
1229
1230 Function Coverage_term_full_amount
1231 (P_line_id Number
1232 ,P_transfer_option Varchar2
1233 , p_new_account_id Number
1234 , p_transfer_date Date
1235 , p_instance_id Number
1236 , P_line_End_date Date
1237
1238 ) return varchar2 Is
1239
1240 Cursor l_cust_rel_csr
1241 (p_old_customer Number
1242 , p_new_customer Number
1243 , p_relation Varchar2
1244 , p_transfer_date Date)
1245 Is
1246
1247 Select distinct relationship_type
1248 From Hz_relationships
1249 Where ((object_id = p_new_customer And subject_id = p_old_customer)
1250 Or (object_id = p_old_customer And subject_id = p_new_customer))
1251 and relationship_type = p_relation
1252 And status = 'A'
1253 and trunc(p_transfer_date) between trunc(start_date) and trunc(end_date)
1254 ;
1255 Cursor l_party_csr(p_cust_id Number) Is
1256 Select party_id
1257 From OKX_CUSTOMER_ACCOUNTS_V
1258 Where id1 = p_cust_id;
1259
1260 Cursor l_instance_csr Is
1261 Select owner_party_id
1262 From Csi_item_instances
1263 Where instance_id = p_instance_id;
1264
1265 l_new_party_id Number;
1266 l_old_party_id Number;
1267 l_relationship Varchar2(2000);
1268 l_relationship_type Varchar2(2000);
1269
1270 Begin
1271
1272 If P_transfer_option in ('TERM_NO_REL','TRANS_NO_REL') Then
1273
1274 l_relationship_type := fnd_profile.value('OKS_TRF_PARTY_REL');
1275
1276 Open l_party_csr(p_new_account_id);
1277 Fetch l_party_csr into l_new_party_id;
1278 Close l_party_csr;
1279
1280 Open l_instance_csr;
1281 Fetch l_instance_csr into l_old_party_id;
1282 Close l_instance_csr;
1283
1284
1285 Open l_cust_rel_csr(l_old_party_id,l_new_party_id,l_relationship_type,p_transfer_date);
1286 Fetch l_cust_rel_csr into l_relationship;
1287 Close l_cust_rel_csr;
1288
1289 If l_relationship Is Not Null Then
1290 return (null);
1291 Else
1292 If trunc(P_line_End_date) < trunc(P_transfer_date) Then
1293 return(null);
1294 Else
1295
1296 return (get_billed_amount(p_line_id));
1297 End If;
1298 End If;
1299 Elsif P_transfer_option in ('TERM','TRANS') Then
1300 If trunc(P_line_End_date) < trunc(P_transfer_date) Then
1301 return(null);
1302 Else
1303
1304 return (get_billed_amount(p_line_id));
1305 End If;
1306
1307 Else
1308
1309 return (null);
1310 End If;
1311
1312 End;
1313 /* ***********************************************
1314 * Function to get the coverage transfer amount
1315 **************************************************/
1316 Function Coverage_transfer_amount(P_line_id Number,P_transfer_option Varchar2, p_new_account_id Number, p_transfer_date Date, p_instance_id Number) return number Is
1317
1318 Cursor l_cust_rel_csr(p_old_customer Number, p_new_customer Number, p_relation Varchar2, p_transfer_date Date)
1319 Is
1320
1321 Select distinct relationship_type
1322 From Hz_relationships
1323 Where ((object_id = p_new_customer And subject_id = p_old_customer)
1324 Or (object_id = p_old_customer And subject_id = p_new_customer))
1325 and relationship_type = p_relation
1326 And status = 'A'
1327 and trunc(p_transfer_date) between trunc(start_date) and trunc(end_date)
1328 ;
1329 Cursor l_party_csr(p_cust_id Number) Is
1330 Select party_id
1331 From OKX_CUSTOMER_ACCOUNTS_V
1332 Where id1 = p_cust_id;
1333
1334 Cursor l_instance_csr Is
1335 Select owner_party_id
1336 From Csi_item_instances
1337 Where instance_id = p_instance_id;
1338
1339 l_new_party_id Number;
1340 l_old_party_id Number;
1341 l_relationship Varchar2(2000);
1342 l_relationship_type Varchar2(2000);
1343 trf_amount Number;
1344
1345 Begin
1346
1347 If P_transfer_option in ('TRANS_NO_REL') Then
1348
1349 l_relationship_type := fnd_profile.value('OKS_TRF_PARTY_REL');
1350
1351 Open l_party_csr(p_new_account_id);
1352 Fetch l_party_csr into l_new_party_id;
1353 Close l_party_csr;
1354
1355 Open l_instance_csr;
1356 Fetch l_instance_csr into l_old_party_id;
1357 Close l_instance_csr;
1358
1359
1360 Open l_cust_rel_csr(l_old_party_id,l_new_party_id,l_relationship_type,p_transfer_date);
1361 Fetch l_cust_rel_csr into l_relationship;
1362 Close l_cust_rel_csr;
1363
1364 If l_relationship Is Not Null Then
1365 return (0);
1366 Else
1367 trf_amount := get_transferred_amount(p_line_id,p_transfer_date);
1368 return (trf_amount);
1369 End If;
1370 Elsif P_transfer_option in ('TRANS') Then
1371 --errorout_n('in cov trf');
1372 trf_amount := get_transferred_amount(p_line_id,p_transfer_date);
1373 return (trf_amount);
1374
1375 Else
1376 return (0);
1377 End If;
1378
1379 End;
1380
1381
1382 Function Get_date_terminated
1383 ( P_sts_code varchar2,
1384 P_Transaction_date Date,
1385 P_Start_date Date,
1386 P_end_date Date)
1387 Return Date Is
1388
1389 begin
1390
1391 If P_sts_code = 'ENTERED' Then
1392 return(Null);
1393 Else
1394 If trunc(p_transaction_date) < trunc(p_start_date) Then
1395 return(P_start_date);
1396 Elsif trunc(p_transaction_date) between trunc(p_start_date) And trunc(p_end_date) Then
1397 return(p_transaction_date);
1398 Elsif trunc(p_transaction_date) > trunc(p_end_date) Then
1399 return(p_end_date+1);
1400 End If;
1401
1402 End If;
1403
1404 End;
1405
1406
1407
1408 /* ***********************************************
1409 * Procedure to populate the Global temporary table
1410 * with the impacted COntracts
1411 **************************************************/
1412 Procedure Populate_GlobalTemp(P_Batch_Id Number, P_Batch_type Varchar2, p_transaction_date Date default sysdate, P_new_account_id Number default null) Is
1413
1414 Cursor Contracts_for_transfer_csr Is
1415 Select Tmp.Instance_Id
1416 , KI.CLE_ID SubLine_id
1417 , KI.Dnz_Chr_Id
1418 , KL.Cle_Id
1419 , nvl(KL.price_negotiated,0)
1420 , get_transferred_amount(KI.CLE_ID,p_transaction_date) Transfer_amount
1421 , get_terminate_amount(KI.CLE_ID, p_transaction_date) Credit_Amount
1422 , get_full_terminate_amount(KI.CLE_ID, p_transaction_date,Kl.end_date) Full_terminate_amount
1423 , get_billed_amount(Ki.cle_id) Billed_Amount
1424 , Coverage_transfer_amount(KI.CLE_ID,Ks1.transfer_option, p_new_account_id,p_transaction_date,tmp.instance_id) --coverage transfer amount
1425 , Coverage_terminate_amount(KI.CLE_ID,Ks1.transfer_option,p_new_account_id,p_transaction_date,tmp.instance_id) Coverage_terminate_amount --coverage terminate amount
1426 , Coverage_term_full_amount(KI.Cle_id,Ks1.transfer_option,p_new_account_id,p_transaction_date,tmp.instance_id,kl.end_date) Coverage_full_amount
1427 , Get_date_terminated(St.ste_code,p_transaction_date,kl.start_date, kl.end_date)
1428
1429 From OKC_K_ITEMS KI
1430 , OKC_K_HEADERS_all_B KH
1431 , OKC_K_LINES_B KL
1432 , OKC_STATUSES_B ST
1433 , Oks_Instance_k_dtls_temp tmp
1434 , OKS_K_LINES_B KS
1435 , OKS_K_LINES_B KS1
1436 ,OKC_STATUSES_B HST
1437
1438 Where tmp.parent_id = p_batch_id
1439 And KI.Object1_id1 = to_char(tmp.instance_id)
1440 And KI.Jtot_Object1_code = 'OKX_CUSTPROD'
1441 And KI.dnz_chr_id = KH.ID
1442 And KH.scs_code in ('WARRANTY', 'SERVICE', 'SUBSCRIPTION')
1443 And KI.Cle_id = KL.id
1444 And KL.sts_code = ST.code
1445 And ST.ste_code not in ('TERMINATED','CANCELLED','HOLD')
1446 And KH.sts_code = HST.code
1447 And HST.ste_code <> 'HOLD'
1448 And KL.date_terminated Is Null
1449 And KH.template_yn = 'N'
1450 And KS.cle_id = KL.cle_Id
1451 And KS1.cle_id = KS.Coverage_id
1452 And ( (trunc(p_transaction_date) <= trunc(KL.end_date)And trunc(p_transaction_date) >= trunc(KL.start_date))
1453 OR (trunc(p_transaction_date) <= trunc(kl.start_date))
1454 OR ( trunc(KL.end_date) < trunc(p_transaction_date) and Kl.lse_id <> 18
1455 And not exists (Select 'x'
1456 from okc_operation_instances ois,
1457 okc_operation_lines opl,
1458 okc_class_operations cls,
1459 okc_subclasses_b sl
1460 where ois.id=opl.oie_id
1461 And cls.opn_code in ('RENEWAL','REN_CON')
1462 And sl.code= 'SERVICE'
1463 And sl.cls_code = cls.cls_code
1464 and ois.cop_id = cls.id
1465 and object_cle_id=kl.id)
1466 )
1467 );
1468
1469
1470 Cursor Contracts_for_terminate_csr Is
1471 Select Tmp.Instance_Id
1472 , KI.CLE_ID SubLine_id
1473 , KI.Dnz_Chr_Id
1474 , KL.Cle_Id
1475 , KL.price_negotiated
1476 , 0 Transfer_amount
1477 , get_terminate_amount(KI.CLE_ID, p_transaction_date) Credit_Amount
1478 , get_full_terminate_amount(KI.CLE_ID, p_transaction_date,Kl.end_date) Full_terminate_amount
1479 , get_billed_amount(KI.CLE_ID) Billed_Amount
1480 , 0
1481 , 0
1482 , 0
1483 , Get_date_terminated(St.ste_code,p_transaction_date,kl.start_date, Kl.end_date)
1484 From OKC_K_ITEMS KI
1485 , OKC_K_HEADERS_ALL_B KH
1486 , OKC_K_LINES_B KL
1487 , OKC_STATUSES_B ST
1488 , Oks_Instance_k_dtls_temp tmp
1489 , OKC_STATUSES_B HST
1490 Where tmp.parent_id = p_batch_id
1491 And KI.Object1_id1 = to_char(tmp.instance_id)
1492 And KI.Jtot_Object1_code = 'OKX_CUSTPROD'
1493 And KI.dnz_chr_id = KH.ID
1494 And KH.scs_code in ('WARRANTY', 'SERVICE', 'SUBSCRIPTION')
1495 And KI.Cle_id = KL.id
1496 And KL.sts_code = ST.code
1497 And ST.ste_code not in ('TERMINATED','CANCELLED','HOLD')
1498 And KH.sts_code = HST.code
1499 And HST.ste_code <> 'HOLD'
1500 And KL.date_terminated Is Null
1501 And KH.template_yn = 'N'
1502 And ( (trunc(p_transaction_date) <= trunc(KL.end_date)And trunc(p_transaction_date) >= trunc(KL.start_date))
1503 OR (trunc(p_transaction_date) <= trunc(kl.start_date))
1504 OR ( trunc(KL.end_date) < trunc(p_transaction_date) and Kl.lse_id <> 18
1505 And not exists (Select 'x'
1506 from okc_operation_instances ois,
1507 okc_operation_lines opl,
1508 okc_class_operations cls,
1509 okc_subclasses_b sl
1510 where ois.id=opl.oie_id
1511 And cls.opn_code in ('RENEWAL','REN_CON')
1512 And sl.code= 'SERVICE'
1513 And sl.cls_code = cls.cls_code
1514 and ois.cop_id = cls.id
1515 and object_cle_id=kl.id
1516 )
1517 )
1518 )
1519
1520
1521 Union
1522
1523 Select Tmp.Instance_Id
1524 , KI.CLE_ID SubLine_id
1525 , KI.Dnz_Chr_Id
1526 , KL.Cle_Id
1527 , nvl(KL.price_negotiated,0)
1528 , 0 Transfer_amount
1529 , get_terminate_amount(KI.CLE_ID, p_transaction_date) Credit_Amount
1530 , get_full_terminate_amount(KI.CLE_ID, p_transaction_date,Kl.end_date) Full_terminate_amount
1531 , get_billed_amount(KI.CLE_ID) Billed_Amount
1532 , 0
1533 , 0
1534 , 0
1535 , Get_date_terminated(St.ste_code,p_transaction_date,kl.start_date, Kl.end_date)
1536 From OKC_K_ITEMS KI
1537 ,OKC_K_HEADERS_ALL_B KH
1538 ,OKC_K_LINES_B KL
1539 ,OKC_STATUSES_B ST
1540 ,csi_counter_associations ctrAsc
1541 , Oks_Instance_k_dtls_temp tmp
1542 , OKC_STATUSES_B HST
1543
1544 Where tmp.parent_id = p_batch_id
1545 And KI.object1_id1 = to_char(ctrAsc.Counter_id)
1546 And ctrAsc.source_object_id = tmp.instance_id
1547 And jtot_object1_code = 'OKX_COUNTER'
1548 And KI.dnz_chr_id = KH.ID
1549 And KH.scs_code in ('SERVICE','SUBSCRIPTION')
1550 And KI.Cle_id = KL.id
1551 And KL.sts_code = ST.code
1552 And ST.ste_code not in ('TERMINATED','CANCELLED','HOLD')
1553 And KH.sts_code = HST.code
1554 And HST.ste_code <> 'HOLD'
1555 And KL.date_terminated Is Null
1556 And KH.template_yn = 'N'
1557
1558 And ( (trunc(p_transaction_date) <= trunc(KL.end_date)And trunc(p_transaction_date) >= trunc(KL.start_date))
1559 OR (trunc(p_transaction_date) <= trunc(kl.start_date))
1560 OR ( trunc(KL.end_date) < trunc(p_transaction_date)
1561 And not exists (Select 'x'
1562 from okc_operation_instances ois,
1563 okc_operation_lines opl,
1564 okc_class_operations cls,
1565 okc_subclasses_b sl
1566 where ois.id=opl.oie_id
1567 And cls.opn_code in ('RENEWAL','REN_CON')
1568 And sl.code= 'SERVICE'
1569 And sl.cls_code = cls.cls_code
1570 and ois.cop_id = cls.id
1571 and object_cle_id=kl.id)
1572 )
1573 )
1574 ;
1575
1576 Cursor Contracts_for_idc_Csr Is
1577 Select Tmp.Instance_Id
1578 , KI.CLE_ID SubLine_id
1579 , KI.Dnz_Chr_Id
1580 , KL.Cle_Id
1581 , nvl(KL.price_negotiated,0)
1582 , 0 Transfer_amount
1583 , 0 Credit_Amount
1584 , 0 Full_terminate_amount
1585 , 0 Billed_Amount
1586 , 0
1587 , 0
1588 , 0
1589 , null
1590 From OKC_K_ITEMS_V KI
1591 , OKC_K_HEADERS_ALL_B KH
1592 , OKC_K_LINES_B KL
1593 , OKC_STATUSES_B ST
1594 , OKS_K_LINES_B KS
1595 , Oks_k_lines_b KS1
1596 , Oks_Instance_k_dtls_temp tmp
1597 Where tmp.parent_id = p_batch_id
1598 And KI.Object1_id1 = to_char(tmp.instance_id)
1599 And KI.Jtot_Object1_code = 'OKX_CUSTPROD'
1600 And KI.dnz_chr_id = KH.ID
1601 And KH.scs_code ='WARRANTY'
1602 And KI.Cle_id = KL.id
1603 And KL.sts_code = ST.code
1604 AND KL.CLE_ID = KS.CLE_ID
1605 AND KS.Coverage_ID = KS1.Cle_id
1606 And ST.ste_code not in ('TERMINATED','CANCELLED')
1607 And KL.date_terminated Is Null
1608 And KH.template_yn = 'N'
1609 AND KL.lse_id = 18
1610 AND nvl(ks1.sync_date_install,'N') = 'Y';
1611 --And (check_sr_exists_yn(tmp.instance_id,Kl.cle_id) = 'N') ;
1612
1613
1614 Type instance_rec is record
1615 (
1616 instance_id number
1617 );
1618 Type item_inst_tbl is table of number index by BINARY_INTEGER ;
1619 l_item_inst_tbl item_inst_tbl;
1620 Type l_num_tbl is table of NUMBER index by BINARY_INTEGER ;
1621 Type l_Date_tbl is table of Date index by BINARY_INTEGER ;
1622
1623 Subline_tbl l_num_tbl ;
1624 Instance_tbl l_num_tbl ;
1625
1626 COntract_tbl l_num_tbl ;
1627 Line_tbl l_num_tbl ;
1628 Amount_tbl l_num_tbl ;
1629 Transfer_amount_tbl l_num_tbl ;
1630 Credit_amount_tbl l_num_tbl ;
1631 full_credit_amt_tbl l_num_tbl;
1632 Date_Terminated_tbl l_Date_tbl ;
1633 billed_amount_tbl l_num_tbl ;
1634 Coverage_trf_amount_tbl l_num_tbl ;
1635 Coverage_credit_amount_tbl l_num_tbl ;
1636 Coverage_credit_fullamt_tbl l_num_tbl ;
1637
1638
1639 -- l_item_inst_tbl item_inst_tbl;
1640 l_instance_id number;
1641 l_batch_id number;
1642
1643 l_txn_rec CSI_UTILITY_GRP.txn_oks_rec;
1644 l_item_instance_tbl CSI_UTILITY_GRP.txn_inst_tbl;
1645 l_return_status Varchar2(1);
1646 l_msg_count Number;
1647 l_msg_data Varchar2(2000);
1648 l_count number;
1649 Begin
1650 --call IB api to return instances
1651
1652 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1653 fnd_log.string(FND_LOG.LEVEL_STATEMENT,G_MODULE_CURRENT||'.POPULATE_GLOBAL_TEMP',
1654 'batch_id= ' ||p_batch_id );
1655 End If;
1656 l_txn_rec.batch_id := p_batch_id;
1657 If p_batch_type = 'XFER' then
1658 l_txn_rec.transaction_type(1) := 'TRF';
1659 Elsif p_batch_type = 'TRM' Then
1660 l_txn_rec.transaction_type(1) := 'TRM';
1661 Else
1662 l_txn_rec.transaction_type(1) := 'IDC';
1663 End If;
1664 CSI_UTILITY_GRP.Get_impacted_item_instances
1665 (
1666 p_api_version => 1.0
1667 ,p_commit => 'F'
1668 ,p_init_msg_list => okc_api.g_false
1669 ,p_validation_level => fnd_api.g_valid_level_full
1670 ,x_txn_inst_tbl => l_item_instance_tbl
1671 ,p_txn_oks_rec => l_txn_rec
1672 ,x_return_status => l_return_status
1673 ,x_msg_count => l_msg_count
1674 ,x_msg_data => l_msg_data
1675 );
1676 /*
1677
1678 l_item_instance_tbl(1).transaction_type := 'TRF';
1679 --l_item_instance_tbl(1).instance_tbl(1) := 148664;
1680 --l_item_instance_tbl(1).instance_tbl(2) := 152662;
1681 l_item_instance_tbl(1).instance_tbl(1) := 236663;
1682
1683 */
1684 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1685 fnd_log.string(FND_LOG.LEVEL_STATEMENT,G_MODULE_CURRENT||'.POPULATE_GLOBAL_TEMP',
1686 'l_item_instance_tbl.count = ' ||l_item_instance_tbl.count );
1687
1688 End If;
1689 If l_item_instance_tbl.count > 0 Then
1690 If p_batch_type = 'XFER' Then
1691
1692 For i in l_item_instance_tbl.first ..l_item_instance_tbl.last
1693 Loop
1694 If l_item_instance_tbl(i).transaction_type = 'TRF' Then
1695
1696 Delete Oks_Instance_k_dtls_temp ;
1697 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1698 fnd_log.string(FND_LOG.LEVEL_STATEMENT,G_MODULE_CURRENT||'.POPULATE_GLOBAL_TEMP',
1699 'Insert inot temp ');
1700
1701 End If;
1702 FORALL j in l_item_instance_tbl(i).instance_tbl.first..l_item_instance_tbl(i).instance_tbl.last
1703 INSERT INTO Oks_Instance_k_dtls_temp
1704 (
1705 parent_id ,
1706 subline_id,
1707 topline_id ,
1708 contract_id,
1709 billed_amount ,
1710 transfer_amount ,
1711 credit_amount ,
1712 amount ,
1713 new_subline_id ,
1714 new_serviceline_id ,
1715 new_contract_id ,
1716 instance_id ,
1717 cov_trf_amt ,
1718 cov_trm_amount ,
1719 cov_billed_amount ,
1720 new_start_date ,
1721 new_end_date ,
1722 date_terminated ,
1723 full_term_amount
1724
1725
1726 )
1727 Values (
1728 p_batch_id,null,null,null,null,null,null,null,null,null,null,l_item_instance_tbl(i).instance_tbl(j) ,NULL,NULL,NULL,NULL,NULL,NULL ,Null);
1729
1730 Open Contracts_for_transfer_csr;
1731 Fetch Contracts_for_transfer_csr bulk collect into
1732 Instance_tbl
1733 , SubLine_tbl
1734 , COntract_tbl
1735 , Line_tbl
1736 , Amount_tbl
1737 , Transfer_amount_tbl
1738 , Credit_amount_tbl
1739 , full_credit_amt_tbl
1740 , billed_amount_tbl
1741 , Coverage_trf_amount_tbl
1742 , Coverage_credit_amount_tbl
1743 , Coverage_credit_fullamt_tbl
1744 , Date_terminated_tbl;
1745 Close Contracts_for_transfer_csr;
1746 End If;
1747 End Loop;
1748
1749 Elsif p_batch_type = 'TRM' Then
1750 For i in l_item_instance_tbl.first ..l_item_instance_tbl.last
1751 Loop
1752 If l_item_instance_tbl(i).transaction_type = 'TRM' Then
1753
1754 Delete Oks_Instance_k_dtls_temp ;
1755
1756 FORALL j in l_item_instance_tbl(i).instance_tbl.first..l_item_instance_tbl(i).instance_tbl.last
1757 INSERT INTO Oks_Instance_k_dtls_temp
1758 (
1759 parent_id ,
1760 subline_id,
1761 topline_id ,
1762 contract_id,
1763 billed_amount ,
1764 transfer_amount ,
1765 credit_amount ,
1766 amount ,
1767 new_subline_id ,
1768 new_serviceline_id ,
1769 new_contract_id ,
1770 instance_id ,
1771 cov_trf_amt ,
1772 cov_trm_amount ,
1773 cov_billed_amount ,
1774 new_start_date ,
1775 new_end_date ,
1776 date_terminated ,
1777 full_term_amount
1778
1779
1780 )
1781 Values (
1782 p_batch_id,null,null,null,null,null,null,null,null,null,null,l_item_instance_tbl(i).instance_tbl(j) ,NULL,NULL,NULL,NULL,NULL,NULL,null );
1783
1784 Open Contracts_for_terminate_csr;
1785 Fetch Contracts_for_terminate_csr bulk collect into
1786 Instance_tbl
1787 , SubLine_tbl
1788 , COntract_tbl
1789 , Line_tbl
1790 , Amount_tbl
1791 , Transfer_amount_tbl
1792 , Credit_amount_tbl
1793 , full_credit_amt_tbl
1794 , billed_amount_tbl
1795 , Coverage_trf_amount_tbl
1796 , Coverage_credit_amount_tbl
1797 , Coverage_credit_fullamt_tbl
1798 , Date_terminated_tbl;
1799 Close Contracts_for_terminate_csr;
1800 End If;
1801 End Loop;
1802 Else
1803 For i in l_item_instance_tbl.first ..l_item_instance_tbl.last
1804 Loop
1805 If l_item_instance_tbl(i).transaction_type = 'IDC' Then
1806 Delete Oks_Instance_k_dtls_temp ;
1807
1808 FORALL j in l_item_instance_tbl(i).instance_tbl.first..l_item_instance_tbl(i).instance_tbl.last
1809 INSERT INTO Oks_Instance_k_dtls_temp
1810 (
1811 parent_id ,
1812 subline_id,
1813 topline_id ,
1814 contract_id,
1815 billed_amount ,
1816 transfer_amount ,
1817 credit_amount ,
1818 amount ,
1819 new_subline_id ,
1820 new_serviceline_id ,
1821 new_contract_id ,
1822 instance_id ,
1823 cov_trf_amt ,
1824 cov_trm_amount ,
1825 cov_billed_amount ,
1826 new_start_date ,
1827 new_end_date ,
1828 date_terminated ,
1829 full_term_amount
1830
1831
1832 )
1833 Values (
1834 p_batch_id,null,null,null,null,null,null,null,null,null,null,l_item_instance_tbl(i).instance_tbl(j) ,NULL,NULL,NULL,NULL,NULL,NULL,null );
1835
1836 Open Contracts_for_idc_csr;
1837 Fetch Contracts_for_idc_csr bulk collect into
1838 Instance_tbl
1839 , SubLine_tbl
1840 , COntract_tbl
1841 , Line_tbl
1842 , Amount_tbl
1843 , Transfer_amount_tbl
1844 , Credit_amount_tbl
1845 ,full_credit_amt_tbl
1846 , billed_amount_tbl
1847 , Coverage_trf_amount_tbl
1848 , Coverage_credit_amount_tbl
1849 , Coverage_credit_fullamt_tbl
1850 , Date_terminated_tbl;
1851 Close Contracts_for_idc_csr;
1852 End If;
1853 End Loop;
1854
1855 End If;
1856
1857 End If;
1858
1859 Delete Oks_Instance_k_dtls_temp;-- where parent_id = p_batch_id;
1860 If Subline_tbl.count > 0 Then
1861 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1862 fnd_log.string(FND_LOG.LEVEL_STATEMENT,G_MODULE_CURRENT||'.POPULATE_GLOBAL_TEMP',
1863 'Subline_tbl.count = ' ||Subline_tbl.count );
1864
1865 End If;
1866
1867 FORALL i in Subline_tbl.FIRST..Subline_tbl.LAST
1868 INSERT INTO Oks_Instance_k_dtls_temp
1869 (
1870 parent_id ,
1871 subline_id,
1872 topline_id ,
1873 contract_id,
1874 billed_amount ,
1875 transfer_amount ,
1876 credit_amount ,
1877 amount ,
1878 new_subline_id ,
1879 new_serviceline_id ,
1880 new_contract_id ,
1881 instance_id ,
1882 cov_trf_amt ,
1883 cov_trm_amount ,
1884 cov_billed_amount ,
1885 new_start_date ,
1886 new_end_date ,
1887 date_terminated ,
1888 full_term_amount
1889
1890
1891 )
1892 Values
1893 ( p_Batch_id
1894 , SubLine_tbl(i)
1895 , Line_tbl(i)
1896 , COntract_tbl(i)
1897 , billed_amount_tbl(i)
1898 , Transfer_amount_tbl(i)
1899 , Credit_amount_tbl(i)
1900 , Amount_tbl(i)
1901 , Null
1902 , Null
1903 , Null
1904 , Instance_tbl(i)
1905 , Coverage_trf_amount_tbl(i)
1906 , Coverage_credit_amount_tbl(i)
1907 , Coverage_credit_fullamt_tbl(i)
1908 , NULL
1909 , Null
1910 ,Date_terminated_tbl(i)
1911 , full_credit_amt_tbl(i)
1912
1913 );
1914
1915
1916 End If;
1917
1918
1919 End;
1920
1921 FUNCTION get_invoice_text (
1922 p_product_item IN NUMBER,
1923 p_start_date IN DATE,
1924 p_end_date IN DATE
1925 )
1926 RETURN VARCHAR2
1927 IS
1928 CURSOR l_inv_csr (p_product_item NUMBER)
1929 IS
1930 SELECT t.description NAME,
1931 b.concatenated_segments description
1932 FROM mtl_system_items_b_kfv b, mtl_system_items_tl t
1933 WHERE b.inventory_item_id = t.inventory_item_id
1934 AND b.organization_id = t.organization_id
1935 AND t.LANGUAGE = USERENV ('LANG')
1936 AND b.inventory_item_id = p_product_item
1937 AND ROWNUM < 2;
1938
1939 l_object_code okc_k_items.jtot_object1_code%TYPE;
1940 l_object1_id1 okc_k_items.object1_id1%TYPE;
1941 l_object1_id2 okc_k_items.object1_id2%TYPE;
1942 l_no_of_items okc_k_items.number_of_items%TYPE;
1943 l_name VARCHAR2 (2000);
1944 l_desc VARCHAR2 (2000);
1945 l_formatted_invoice_text VARCHAR2 (2000);
1946 BEGIN
1947 OPEN l_inv_csr (p_product_item);
1948
1949 FETCH l_inv_csr
1950 INTO l_name,
1951 l_desc;
1952
1953 CLOSE l_inv_csr;
1954
1955 IF fnd_profile.VALUE ('OKS_ITEM_DISPLAY_PREFERENCE') = 'DISPLAY_DESC'
1956 THEN
1957 l_desc := l_name;
1958 ELSE
1959 l_desc := l_desc;
1960 END IF;
1961
1962 l_formatted_invoice_text :=
1963 SUBSTR (l_desc || ':' || p_start_date || ':' || p_end_date,
1964 1,
1965 450
1966 );
1967 RETURN (l_formatted_invoice_text);
1968 END get_invoice_text;
1969
1970
1971
1972 /* ***********************************************
1973 * Procedure to check if all the sublines and top lines
1974 * in a contract are terminated or cancelled.
1975 **************************************************/
1976 Procedure Check_termcancel_lines
1977 (
1978 p_line_id Number -- TOp line id or Header Id
1979 , p_line_type Varchar2 -- 'TL' or 'SL'
1980 , P_txn_type Varchar2 --'T' for termination, 'C' for cancel
1981 , X_date OUT NOCOPY Date
1982 ) Is
1983 Cursor l_Term_subline_csr Is
1984 Select max(date_terminated)
1985 From OKC_K_LINES_B
1986 Where cle_id = p_line_id
1987 and lse_id in (8,7,9,10,11,18,13,25,35)
1988 having Count(*) = count(decode(date_terminated, null, null, 'x')) ;
1989
1990 Cursor l_term_topline_csr Is
1991 Select max(date_terminated)
1992 From OKC_K_LINES_B
1993 Where dnz_chr_id = p_line_id
1994 and lse_id in (1,12,14,19)
1995 having Count(*) = count(decode(date_terminated, null, null, 'x'));
1996
1997 Cursor l_Cancel_subline_csr Is
1998 Select max(date_cancelled)
1999 From OKC_K_LINES_B
2000 Where cle_id = p_line_id
2001 and lse_id in (8,7,9,10,11,18,13,25,35)
2002 having count(*) = Count(decode(term_cancel_source,'IBTRANSFER','x','IBTERMINATE','x','IBRETURN','x',null))
2003 and Count(*) = count(decode(date_cancelled, null, null, 'x')) ;
2004
2005 Cursor l_Cancel_topline_csr Is
2006 Select max(date_cancelled)
2007 From OKC_K_LINES_B
2008 Where dnz_chr_id = p_line_id
2009 and lse_id in (1,12,14,19)
2010 having count(*) = Count(decode(term_cancel_source,'IBTRANSFER','x','IBTERMINATE','x','IBRETURN','x',null))
2011 and Count(*) = count(decode(date_cancelled, null, null, 'x'));
2012
2013 l_date date;
2014 Begin
2015
2016 l_date := null;
2017 If P_txn_type = 'T' Then
2018 If P_line_type = 'SL' Then
2019 Open l_Term_subline_csr;
2020 Fetch l_Term_subline_csr into l_date;
2021 Close l_Term_subline_csr;
2022
2023 Else
2024 Open l_Term_topline_csr;
2025 Fetch l_Term_topline_csr into l_date;
2026 Close l_Term_topline_csr;
2027
2028
2029 End If;
2030
2031 Else
2032 If P_line_type = 'SL' Then
2033 Open l_Cancel_subline_csr;
2034 Fetch l_Cancel_subline_csr into l_date;
2035 Close l_Cancel_subline_csr;
2036
2037 Else
2038
2039 Open l_Cancel_Topline_csr;
2040 Fetch l_Cancel_Topline_csr into l_date;
2041 Close l_Cancel_Topline_csr;
2042
2043 End If;
2044 End If;
2045
2046 X_date := l_date;
2047
2048 End;
2049
2050 FUNCTION get_credit_option (
2051 p_party_id NUMBER,
2052 p_org_id NUMBER,
2053 p_transaction_date DATE
2054 )
2055 RETURN VARCHAR2
2056 IS
2057 CURSOR credit_option_csr
2058 IS
2059 SELECT credit_amount
2060 FROM oks_k_defaults
2061 WHERE ( segment_id1 = p_party_id
2062 AND segment_id2 = '#'
2063 AND jtot_object_code = 'OKX_PARTY'
2064 AND cdt_type = 'SDT'
2065 AND p_transaction_date BETWEEN start_date
2066 AND NVL (end_date,
2067 p_transaction_date)
2068 )
2069 OR ( segment_id1 = p_org_id
2070 AND segment_id2 = '#'
2071 AND jtot_object_code = 'OKX_OPERUNIT'
2072 AND cdt_type = 'SDT'
2073 AND p_transaction_date BETWEEN start_date
2074 AND NVL (end_date,
2075 p_transaction_date)
2076 )
2077 order by jtot_object_code desc;
2078 Cursor l_global_csr Is
2079 Select credit_option
2080 From oks_k_defaults
2081 Where cdt_type = 'MDT'
2082 AND segment_id1 IS NULL
2083 AND segment_id2 IS NULL
2084 AND jtot_object_code IS NULL
2085 ;
2086
2087 l_credit_option VARCHAR2 (30);
2088 BEGIN
2089 FOR credit_option_rec IN credit_option_csr
2090 LOOP
2091 l_credit_option := credit_option_rec.credit_amount;
2092
2093 IF l_credit_option IS NOT NULL
2094 THEN
2095 EXIT;
2096 END IF;
2097 END LOOP;
2098
2099 IF l_credit_option IS NULL
2100 THEN
2101 Open l_global_csr;
2102 Fetch l_global_csr into l_credit_option;
2103 Close l_global_csr;
2104 If l_credit_option Is Null Then
2105
2106
2107 IF NVL (fnd_profile.VALUE ('OKS_RAISE_CREDIT_MEMO_FOR_IB_INT'), 'YES') =
2108 'YES'
2109 THEN
2110 l_credit_option := 'CALCULATED';
2111 ELSE
2112 l_credit_option := 'NONE';
2113 END IF;
2114 End If;
2115 END IF;
2116
2117 RETURN (l_credit_option);
2118 END;
2119
2120
2121 -- Function to get the credit amount based on GCD and pofile.
2122
2123 FUNCTION get_credit_amount_trm (
2124 p_line_id IN NUMBER,
2125 p_termination_date IN DATE DEFAULT NULL
2126 )
2127 RETURN NUMBER
2128 IS
2129 l_amount NUMBER;
2130 l_return_status VARCHAR2 (1);
2131 x_msg_data VARCHAR2 (2000);
2132 x_msg_count NUMBER;
2133 l_rnrl_rec_out oks_renew_util_pvt.rnrl_rec_type;
2134 l_calculated_credit VARCHAR2 (1) := 'N';
2135 l_party_id NUMBER;
2136 l_org_id NUMBER;
2137 l_inv_org_id NUMBER;
2138 CURSOR get_party_csr
2139 IS
2140 SELECT prl.object1_id1 party_id,
2141 kh.authoring_org_id org_id,
2142 kh.inv_organization_id
2143 FROM okc_k_party_roles_b prl,
2144 okc_k_headers_all_b kh,
2145 okc_k_lines_b ksl
2146 WHERE ksl.ID = p_line_id
2147 AND ksl.dnz_chr_id = kh.ID
2148 AND prl.dnz_chr_id = kh.ID
2149 AND prl.chr_id IS NOT NULL
2150 AND prl.cle_id IS NULL
2151 AND prl.rle_code IN ('CUSTOMER', 'SUBSCRIBER')
2152 AND prl.jtot_object1_code = 'OKX_PARTY';
2153
2154
2155 BEGIN
2156 OPEN get_party_csr;
2157
2158 FETCH get_party_csr
2159 INTO l_party_id,
2160 l_org_id,
2161 l_inv_org_id;
2162
2163 CLOSE get_party_csr;
2164
2165 oks_renew_util_pub.get_renew_rules (p_api_version => 1.0,
2166 p_init_msg_list => 'T',
2167 x_return_status => l_return_status,
2168 x_msg_count => x_msg_count,
2169 x_msg_data => x_msg_data,
2170 p_chr_id => NULL,
2171 p_party_id => l_party_id,
2172 p_org_id => l_org_id,
2173 p_date => SYSDATE,
2174 p_rnrl_rec => NULL,
2175 x_rnrl_rec => l_rnrl_rec_out
2176 );
2177
2178 IF (fnd_log.level_event >= fnd_log.g_current_runtime_level)
2179 THEN
2180 fnd_log.STRING
2181 (fnd_log.level_event,
2182 g_module_current || '.get_credit_amount',
2183 'oks_renew_util_pub.get_renew_rules(Return status = '
2184 || l_return_status
2185 || ') Credit amount = ('
2186 || l_rnrl_rec_out.credit_amount
2187 || ')'
2188 );
2189 END IF;
2190
2191 IF NOT l_return_status = okc_api.g_ret_sts_success
2192 THEN
2193 RAISE g_exception_halt_validation;
2194 END IF;
2195
2196 IF UPPER (l_rnrl_rec_out.credit_amount) = 'FULL'
2197 THEN
2198 RETURN (get_billed_amount (p_line_id));
2199 ELSIF UPPER (l_rnrl_rec_out.credit_amount) = 'NONE'
2200 THEN
2201 RETURN (0);
2202 ELSIF l_rnrl_rec_out.credit_amount IS NULL
2203 THEN
2204 -- get the profile value of oks_raise_credit.....
2205 IF NVL (fnd_profile.VALUE ('OKS_RAISE_CREDIT_MEMO_FOR_IB_INT'), 'YES') = 'YES'
2206 THEN
2207 l_calculated_credit := 'Y';
2208 END IF;
2209 ELSIF UPPER (l_rnrl_rec_out.credit_amount) = 'CALCULATED'
2210 THEN
2211 l_calculated_credit := 'Y';
2212 END IF;
2213
2214 IF l_calculated_credit = 'Y'
2215 THEN
2216
2217
2218 okc_context.set_okc_org_context (l_org_id, l_inv_org_id);
2219 oks_bill_rec_pub.pre_terminate_amount
2220 (p_id => p_line_id,
2221 p_terminate_date => trunc(NVL(p_termination_date,SYSDATE)),
2222 p_flag => 3,
2223 x_amount => l_amount,
2224 x_return_status => l_return_status
2225 );
2226 RETURN (l_amount);
2227 ELSE
2228 RETURN (0);
2229 END IF;
2230 END;
2231
2232 FUNCTION get_credit_amount_trf (
2233 p_line_id NUMBER,
2234 p_new_account_id NUMBER,
2235 p_transfer_date DATE
2236 )
2237 RETURN NUMBER
2238 IS
2239
2240 CURSOR l_get_attr_csr IS
2241 SELECT csi.OWNER_PARTY_ID old_party_id,
2242 tls1.TRANSFER_OPTION transfer_option
2243
2244 FROM okc_k_lines_b sl,
2245 oks_k_lines_b tls,
2246 oks_k_lines_b tls1,
2247 okc_k_items im,
2248 csi_item_instances csi
2249
2250 WHERE sl.id = p_line_id
2251 AND sl.id = im.cle_id
2252 AND im.jtot_object1_code = 'OKX_CUSTPROD'
2253 AND im.object1_id1 = csi.instance_id
2254 AND sl.cle_id = tls.cle_id
2255 AND tls.coverage_id = tls1.CLE_ID;
2256
2257 CURSOR l_cust_rel_csr (
2258 p_old_customer NUMBER,
2259 p_new_customer NUMBER,
2260 p_relation VARCHAR2,
2261 p_transfer_date DATE
2262 )
2263 IS
2264 SELECT DISTINCT relationship_type
2265 FROM hz_relationships
2266 WHERE ( ( object_id = p_new_customer
2267 AND subject_id = p_old_customer
2268 )
2269 OR ( object_id = p_old_customer
2270 AND subject_id = p_new_customer
2271 )
2272 )
2273 AND relationship_type = p_relation
2274 AND status = 'A'
2275 AND TRUNC (p_transfer_date) BETWEEN TRUNC (start_date)
2276 AND TRUNC (end_date);
2277
2278 CURSOR l_party_csr (
2279 p_cust_id NUMBER
2280 )
2281 IS
2282 SELECT party_id
2283 FROM okx_customer_accounts_v
2284 WHERE id1 = p_cust_id;
2285
2286 l_new_party_id NUMBER;
2287 l_old_party_id NUMBER;
2288 l_relationship VARCHAR2 (2000);
2289 l_relationship_type VARCHAR2 (2000);
2290 l_get_attr_rec l_get_attr_csr%ROWTYPE;
2291 BEGIN
2292 OPEN l_get_attr_csr;
2293 FETCH l_get_attr_csr INTO l_get_attr_rec;
2294 CLOSE l_get_attr_csr;
2295
2296 IF l_get_attr_rec.transfer_option IN ('TERM_NO_REL', 'TRANS_NO_REL')
2297 THEN
2298 l_relationship_type := fnd_profile.VALUE ('OKS_TRF_PARTY_REL');
2299
2300 OPEN l_party_csr (p_new_account_id);
2301 FETCH l_party_csr INTO l_new_party_id;
2302 CLOSE l_party_csr;
2303
2304 OPEN l_cust_rel_csr (l_get_attr_rec.old_party_id,
2305 l_new_party_id,
2306 l_relationship_type,
2307 p_transfer_date
2308 );
2309
2310 FETCH l_cust_rel_csr
2311 INTO l_relationship;
2312
2313 CLOSE l_cust_rel_csr;
2314
2315 IF l_relationship IS NULL
2316 THEN
2317 RETURN (0);
2318 ELSE
2319 RETURN (get_credit_amount_trm (p_line_id,
2320 p_transfer_date ));
2321
2322 END IF;
2323 ELSIF l_get_attr_rec.transfer_option IN ('TERM', 'TRANS')
2324 THEN
2325 RETURN (get_credit_amount_trm (p_line_id,
2326 p_transfer_date));
2327 ELSE
2328 RETURN (0);
2329 END IF;
2330
2331 END;
2332
2333
2334
2335 Function Check_renewed_Sublines
2336 (
2337 p_line_id Number
2338
2339 ) return Date Is
2340 Cursor l_line_csr Is
2341 Select max(Kl.date_renewed)
2342 From OKC_K_LINES_B Kl, OKc_k_lines_b Kl1
2343 Where Kl1.id = p_line_id
2344 and Kl.cle_id = Kl1.cle_id
2345 And Kl.lse_id in (8,7,9,10,11,13,35, 18, 25)
2346 having Count(*) = count(decode(kl.date_renewed, null, null, 'x')) ;
2347
2348
2349 l_date Date;
2350
2351
2352
2353 Begin
2354
2355 Open l_line_csr;
2356 Fetch l_line_csr into l_date;
2357 Close l_line_csr;
2358
2359 return(l_date);
2360 End ;
2361
2362
2363 Function Check_renewed_lines
2364 (
2365 p_line_id Number
2366
2367 ) return Date Is
2368 Cursor l_line_csr Is
2369 Select max(Kl.date_renewed)
2370 From OKC_K_LINES_B Kl, Okc_k_lines_b Kl1
2371 Where Kl1.Id = p_line_id
2372 And Kl.dnz_chr_id = Kl1.dnz_chr_id
2373 and Kl.lse_id in (1,12,19)
2374 having Count(*) = count(decode(Kl.date_renewed, null, null, 'x')) ;
2375
2376 l_date Date;
2377
2378 Begin
2379
2380 Open l_line_csr;
2381 Fetch l_line_csr into l_date;
2382 Close l_line_csr;
2383
2384 return(l_date);
2385
2386
2387
2388
2389
2390 End;
2391
2392 Function Check_Termination_date
2393 (
2394 p_line_id Number -- TOp line id or Header Id
2395 , P_Line_type Varchar2 --'T' for TopLine, 'H' for Header
2396 ) Return Date Is
2397
2398
2399 CURSOR get_line_term
2400 IS
2401 SELECT max(date_terminated)
2402 FROM oks_Instance_k_dtls_temp temp
2403 where topline_id = p_Line_id
2404 having count(*) = (select count(*) from Okc_k_lines_b
2405 WHERE cle_id = p_line_id AND lse_id IN (8,7,9,10,11,13,35, 18, 25)
2406 );
2407
2408
2409 CURSOR get_max_line_term
2410 IS
2411 SELECT max(date_terminated)
2412 FROM oks_Instance_k_dtls_temp temp
2413 where topline_id = p_Line_id;
2414
2415
2416
2417 Cursor l_term_topline_csr Is
2418 Select max(line.date_terminated)
2419 From OKC_K_LINES_B line
2420 Where line.cle_id= p_line_id
2421 and line.lse_id in (8,7,9,10,11,13,18,25,35)
2422 And line.id not in (select subline_id from oks_instance_k_dtls_temp where topline_id = p_line_id)
2423 having Count(line.id) = count(decode(line.date_terminated, null, null, 'x'));
2424
2425
2426
2427 CURSOR get_Hdr_term
2428 IS
2429 SELECT max(date_terminated)
2430 FROM oks_Instance_k_dtls_temp
2431 where contract_id = p_Line_id
2432 having count(*) = (select count(*) from Okc_k_lines_b
2433 WHERE dnz_chr_id= p_line_id AND lse_id IN (8,7,9,10,11,13,35, 18, 25)
2434 );
2435
2436
2437
2438 CURSOR get_max_Hdr_term
2439 IS
2440 SELECT max(date_terminated)
2441 FROM oks_Instance_k_dtls_temp temp
2442 where Contract_id = p_Line_id;
2443
2444
2445
2446
2447 Cursor l_term_Hdr_csr Is
2448 Select max(line.date_terminated)
2449 From OKC_K_LINES_B line
2450 Where line.dnz_chr_id= p_line_id
2451 and line.lse_id in (8,7,9,10,11,13,18,25,35)
2452 And line.id not in (select subline_id from oks_instance_k_dtls_temp where Contract_id = p_line_id)
2453 having Count(*) = count(decode(line.date_terminated, null, null, 'x'));
2454
2455 l_line_date Date;
2456 l_Hdr_date Date;
2457 l_line_term_dt Date;
2458 l_HDr_term_dt Date;
2459
2460
2461
2462 Begin
2463
2464
2465 If P_line_type = 'T' Then
2466
2467 l_line_term_dt := Null;
2468 Open get_line_term;
2469 Fetch get_line_term into l_line_term_dt;
2470 Close get_line_term;
2471
2472 If l_line_term_dt Is Null Then
2473 Open l_term_topline_csr ;
2474 Fetch l_term_topline_csr into l_line_Date;
2475 Close l_term_topline_csr ;
2476
2477
2478
2479
2480 If l_line_date Is Not Null Then
2481 Open get_max_line_term ;
2482 Fetch get_max_line_term into l_line_term_dt;
2483 Close get_max_line_term ;
2484 l_line_term_dt := greatest(l_line_date,l_line_term_dt);
2485 Else
2486 l_line_term_dt := Null;
2487 End if;
2488 End If;
2489
2490 return(l_line_term_dt);
2491
2492
2493
2494 End If;
2495
2496 If P_line_type = 'H' Then
2497
2498 l_Hdr_term_dt := Null;
2499 Open get_Hdr_term;
2500 Fetch get_Hdr_term into l_Hdr_term_dt;
2501 Close get_Hdr_term;
2502
2503 If l_Hdr_term_dt Is Null Then
2504 Open l_term_Hdr_csr ;
2505 Fetch l_term_Hdr_csr into l_Hdr_Date;
2506 Close l_term_Hdr_csr ;
2507
2508
2509
2510
2511 If l_Hdr_date Is Not Null Then
2512 Open get_max_Hdr_term ;
2513 Fetch get_max_Hdr_term into l_Hdr_term_dt;
2514 Close get_max_Hdr_term ;
2515 l_Hdr_term_dt := greatest(l_Hdr_date,l_Hdr_term_dt);
2516 Else
2517 l_Hdr_term_dt := Null;
2518 End if;
2519 End If;
2520
2521 Return(l_Hdr_term_dt);
2522 End If;
2523
2524
2525
2526
2527 End;
2528
2529 Function Get_address(P_site_use_id Number) return varchar2 Is
2530
2531 Cursor l_get_address Is
2532 Select arp_addr_label_pkg.format_address (NULL,l.address1,l.address2,
2533 l.address3,l.address4,l.city,l.county,
2534 l.state,l.province,l.postal_code,
2535 NULL,l.country,NULL,NULL,NULL,NULL,
2536 NULL,NULL,NULL,'N','N',300,1,1)
2537 From hz_cust_site_uses_all cs
2538 ,hz_party_sites ps
2539 ,hz_locations l
2540 Where cs.site_use_id (+) = p_site_use_id
2541 AND cs.cust_acct_site_id = ps.party_site_id(+)
2542 AND ps.location_id = l.location_id(+);
2543 l_address Varchar2(4000);
2544 Begin
2545 Open l_get_address;
2546 Fetch l_get_address into l_address;
2547 Close l_get_address;
2548
2549 return(l_address);
2550
2551
2552 End;
2553
2554 Procedure get_srv_name(P_line_id Number, x_service_name Out NoCopy Varchar2, x_service_description Out NoCopy varchar2) Is
2555
2556 Cursor get_name_csr Is
2557 Select fnd_flex_server.get_kfv_concat_segs_by_rowid('COMPACT', 401, 'SERV', 101, mtl.rowid), description
2558 From Mtl_system_items_b mtl
2559 , okc_k_items itm
2560 Where mtl.inventory_item_id = itm.object1_id1
2561 and mtl.organization_id = itm.object1_id2
2562 And itm.cle_id = p_line_id;
2563
2564
2565 Begin
2566 Open get_name_csr;
2567 Fetch get_name_csr into x_service_name, x_service_description;
2568 Close get_name_csr;
2569
2570 End;
2571
2572
2573 FUNCTION get_covlvl_name
2574 (
2575 p_jtot_code IN VARCHAR2,
2576 p_object1_id1 IN VARCHAR2,
2577 p_object1_id2 IN VARCHAR2
2578 )
2579 RETURN VARCHAR2
2580
2581 IS
2582 l_name VARCHAR2(2000);
2583 l_chr_id NUMBER;
2584
2585 CURSOR get_prod_name_csr IS
2586 SELECT mtl.concatenated_segments
2587 From mtl_system_items_kfv mtl,
2588 okc_k_items_v itm
2589 ,csi_item_instances csi
2590 where itm.object1_id1 = p_object1_id1
2591 And itm.jtot_object1_code = 'OKX_CUSTPROD'
2592 And csi.instance_id = itm.object1_id1
2593 And csi.inventory_item_id = mtl.inventory_item_id
2594 And rownum < 2;
2595
2596 CURSOR get_site_name_csr IS
2597 SELECT DECODE(site.party_site_name
2598 ,NULL,site.party_site_number
2599 ,site.party_site_number || '-' ||
2600 site.party_site_name ) NAME
2601 FROM hz_party_sites site
2602 WHERE site.party_site_id = p_object1_id1;
2603
2604 BEGIN
2605 l_name := okc_util.get_name_from_jtfv(p_jtot_code,p_object1_id1,p_object1_id2);
2606
2607 IF ( p_jtot_code= ('OKX_COVITEM')
2608 OR p_jtot_code = ('OKX_SERVICE'))
2609 THEN
2610 okc_context.set_okc_org_context(NULL, p_object1_id2);
2611 l_name := okc_util.get_name_from_jtfv(p_jtot_code,p_object1_id1,p_object1_id2);
2612
2613 ELSIF p_jtot_code= ('OKX_PARTYSITE')
2614 THEN
2615 Open get_site_name_csr;
2616 Fetch get_site_name_csr into l_name ;
2617 Close get_site_name_csr;
2618
2619 ELSIF p_jtot_code = ('OKX_CUSTPROD')
2620 THEN
2621 Open get_prod_name_csr;
2622 Fetch get_prod_name_csr into l_name ;
2623 Close get_prod_name_csr;
2624
2625 ELSE
2626 l_name := okc_util.get_name_from_jtfv(p_jtot_code,p_object1_id1,p_object1_id2);
2627 END IF;
2628
2629 RETURN (l_name);
2630 END;
2631
2632
2633 End OKS_IB_UTIL_PVT;