[Home] [Help]
PACKAGE BODY: APPS.OKS_BILL_UTIL_PUB
Source
1 package body oks_bill_util_pub as
2 /* $Header: OKSBUTLB.pls 120.14 2006/10/06 09:40:07 mchoudha noship $ */
3
4 g_chr_id NUMBER;
5
6 Function CHECK_RULE_Group_EXISTS
7 (
8 p_chr_id IN NUMBER,
9 p_cle_id IN NUMBER
10 ) Return NUMBER
11 Is
12 v_id NUMBER;
13 Begin
14 If (p_chr_id IS NOT NULL) Then
15 SELECT ID INTO V_ID FROM OKC_RULE_GROUPS_V WHERE Dnz_CHR_ID = p_chr_id And cle_id Is Null;
16 If V_ID IS NULL Then
17 return(NULL);
18 Else
19 return(V_ID);
20 End If;
21 End If;
22
23 If (p_cle_id IS NOT NULL) Then
24 SELECT ID INTO V_ID FROM OKC_RULE_GROUPS_V WHERE CLE_ID = p_cle_id;
25 If V_ID IS NULL Then
26 return(NULL);
27 Else
28 return(V_ID);
29 End If;
30 End If;
31
32 Exception
33 When OTHERS Then
34 RETURN(NULL);
35
36 End CHECK_RULE_Group_EXISTS;
37
38
39
40
41 Function Check_Rule_Exists
42 (
43 p_rgp_id IN NUMBER,
44 p_rule_type IN VARCHAR2
45 ) Return NUMBER
46 Is
47 v_id NUMBER;
48 Begin
49 If p_rgp_id is null Then
50 Return(null);
51 Else
52 Select ID Into V_ID From OKC_RULES_V
53 Where rgp_id = p_rgp_id
54 And Rule_information_category = p_rule_type;
55
56 If v_id Is NULL Then
57 return(null);
58 Else
59 return(V_ID);
60 End If;
61 End if;
62
63
64 Exception
65 WHEN No_Data_Found Then
66 Return (null);
67
68 End Check_Rule_Exists;
69
70 -------------------------------------------------------------------------
71 -- Begin partial period computation logic
72 -- Developer Mani Choudhary
73 -- Date 04-MAY-2005
74 -- Description:
75 --This new function will determine number of periods of SLL given
76 --the start date, end date,uom_per_period and uom of the SLL.
77 -------------------------------------------------------------------------
78
79 FUNCTION Get_Periods (p_start_date IN DATE,
80 p_end_date IN DATE,
81 p_uom_code IN VARCHAR2,
82 p_period_start IN VARCHAR2
83 )
84 RETURN NUMBER
85 IS
86 /*Declaration Section*/
87 CURSOR cs_validate_uom(p_uom_code IN VARCHAR2)
88 is
89 SELECT 1
90 FROM MTL_UNITS_OF_MEASURE_TL TL, okc_time_code_units_v okc
91 WHERE TL.uom_code = okc.uom_code
92 AND TL.uom_code = p_uom_code
93 --AND TL.uom_class = 'Time' commented for bug#5585356
94 AND okc.active_flag = 'Y'
95 AND TL.LANGUAGE = USERENV('LANG');
96
97
98 cr_validate_uom cs_validate_uom%ROWTYPE;
99 l_level_periods NUMBER;
100 l_next_start_date DATE;
101 l_temp_periods NUMBER;
102 l_uom_quantity NUMBER;
103 l_tce_code VARCHAR2(30);
104 l_return_status VARCHAR2(20);
105
106 INVALID_PERIOD_START_EXCEPTION EXCEPTION;
107 INVALID_DATE_EXCEPTION EXCEPTION;
108 INVALID_UOM_EXCEPTION EXCEPTION;
109 BEGIN
110 --Begin Validation
111 --1) Validate dates
112 IF (p_start_date IS NULL)OR(p_end_date IS NULL)OR(p_start_date > p_end_date)
113 THEN
114 RAISE INVALID_DATE_EXCEPTION;
115 END IF;
116
117 --2)Validate uom
118 OPEN cs_validate_uom(p_uom_code);
119 FETCH cs_validate_uom INTO cr_validate_uom;
120 IF cs_validate_uom%NOTFOUND
121 THEN
122 RAISE INVALID_UOM_EXCEPTION;
123 END IF;
124 CLOSE cs_validate_uom;
125
126 --3)Validate period start
127 IF upper(p_period_start) NOT IN ('CALENDAR','SERVICE')
128 THEN
129 RAISE INVALID_PERIOD_START_EXCEPTION;
130 END IF;
131
132 --End Validation
133 l_level_periods := 0;
134
135 IF p_uom_code ='DAY' THEN
136 Return(TRUNC(p_end_date)-TRUNC(p_start_date)+1);
137 END IF;
138
139 IF (p_period_start = 'SERVICE') THEN
140 l_next_start_date := TRUNC(p_start_date);
141 ELSE
142 --if the start date is not the start of CALENDAR
143 IF(TRUNC(p_start_date,'MM')<> TRUNC(p_start_date))
144 THEN
145 l_next_start_date := last_day(TRUNC(p_start_date))+1;
146 l_level_periods := l_level_periods + 1;
147 ELSE
148 l_next_start_date := TRUNC(p_start_date);
149 END IF;
150 END IF;
151
152 OKS_BILL_UTIL_PUB.Get_Seeded_Timeunit
153 (p_timeunit => p_uom_code,
154 x_return_status => l_return_status,
155 x_quantity => l_uom_quantity ,
156 x_timeunit => l_tce_code);
157
158 IF (l_tce_code = 'MONTH') THEN
159 l_temp_periods := MONTHS_BETWEEN(p_end_date+1,l_next_start_date);
160
161 l_level_periods:=ceil(l_temp_periods/l_uom_quantity)+l_level_periods;
162 END IF;
163
164 IF (l_tce_code = 'YEAR') THEN
165 l_temp_periods := MONTHS_BETWEEN(p_end_date+1,l_next_start_date)/12;
166 l_level_periods:=ceil(l_temp_periods/l_uom_quantity)+l_level_periods;
167 END IF;
168
169 IF l_tce_code ='DAY' THEN
170 --14-NOV-2005 mchoudha fix for bug#4692372
171 l_level_periods := ceil(((TRUNC(p_end_date)-TRUNC(p_start_date)+1))
172 /l_uom_quantity);
173 END IF;
174
175 RETURN l_level_periods;
176
177 EXCEPTION
178 WHEN INVALID_PERIOD_START_EXCEPTION THEN
179 OKC_API.set_message('OKS','OKS_INVD_PERIOD_START_CODE'); --?? need to seed this message
180 return NULL;
181 WHEN INVALID_UOM_EXCEPTION THEN
182 OKC_API.SET_MESSAGE(p_app_name => 'OKS',
183 p_msg_name => 'OKS_INVD_UOM_CODE',
184 p_token1 => 'OKS_API_NAME',
185 p_token1_value => 'oks_bill_util_pub.Get_Periods',
186 p_token2 => 'UOM_CODE',
187 p_token2_value => p_uom_code);
188 IF cs_validate_uom%ISOPEN THEN
189 CLOSE cs_validate_uom;
190 END IF;
191 return NULL;
192 WHEN INVALID_DATE_EXCEPTION THEN
193 OKC_API.set_message('OKS','OKS_INVALID_START_END_DATES'); --?? need to seed this message
194 return NULL;
195 WHEN OTHERS THEN
196 --set the error message and return with NULL to notify the
197 --caller of error
198 OKC_API.SET_MESSAGE(p_app_name => G_APP_NAME,
199 p_msg_name => G_UNEXPECTED_ERROR,
200 p_token1 => G_SQLCODE_TOKEN,
201 p_token1_value => sqlcode,
202 p_token2 => G_SQLERRM_TOKEN,
203 p_token2_value => sqlerrm);
204
205 RETURN NULL;
206 END Get_Periods;
207
208 -------------------------------------------------------------------------
209 -- Begin partial period computation logic
210 -- Developer Mani Choudhary
211 -- Date 04-MAY-2005
212 -- Description:
213 --This new function will determine the end date of the
214 --SLL in case of "CALENDAR" period start.
215 -------------------------------------------------------------------------
216 FUNCTION Get_Enddate_Cal(p_start_date IN DATE,
217 p_uom_code IN VARCHAR2,
218 p_duration IN NUMBER,
219 p_level_periods IN NUMBER
220 )
221 RETURN DATE
222 IS
223
224 CURSOR cs_validate_uom(p_uom_code IN VARCHAR2)
225 is
226 SELECT 1
227 FROM MTL_UNITS_OF_MEASURE_TL TL, okc_time_code_units_v okc
228 WHERE TL.uom_code = okc.uom_code
229 AND TL.uom_code = p_uom_code
230 -- AND TL.uom_class = 'Time' commednted for bug#5585356
231 AND okc.active_flag = 'Y'
232 AND TL.LANGUAGE = USERENV('LANG') ;
233
234 cr_validate_uom cs_validate_uom%ROWTYPE;
235 l_level_periods NUMBER;
236 l_next_start_date DATE;
237 l_end_date DATE;
238 l_duration NUMBER;
239
240 INVALID_PERIOD_EXCEPTION EXCEPTION;
241 INVALID_DATE_EXCEPTION EXCEPTION;
242 INVALID_UOM_EXCEPTION EXCEPTION;
243
244 BEGIN
245 --Begin Validation
246 --1) Validate start date
247 IF (p_start_date IS NULL)
248 THEN
249 RAISE INVALID_DATE_EXCEPTION;
250 END IF;
251
252 --2)Validate uom
253 OPEN cs_validate_uom(p_uom_code);
254 FETCH cs_validate_uom INTO cr_validate_uom;
255 IF cs_validate_uom%NOTFOUND
256 THEN
257 RAISE INVALID_UOM_EXCEPTION;
258 END IF;
259 CLOSE cs_validate_uom;
260
261 --3)Validate period duration
262 IF nvl(p_level_periods,0) = 0 OR nvl(p_duration,0) = 0
263 THEN
264 RAISE INVALID_PERIOD_EXCEPTION;
265 END IF;
266
267 --End Validation
268 l_level_periods := p_level_periods;
269 l_next_start_date := TRUNC(p_start_date);
270 l_duration:=p_duration;
271
272 --if the start date is not the start of CALENDAR
273
274 IF TRUNC(p_start_date,'MM')<>TRUNC(p_start_date) THEN
275 l_next_start_date := LAST_DAY(TRUNC(p_start_date))+1;
276 l_level_periods := p_level_periods - 1;
277 END IF;
278
279 IF l_level_periods > 0 THEN
280 l_end_date := OKC_TIME_UTIL_PUB.GET_ENDDATE
281 (p_start_date => l_next_start_date,
282 p_timeunit => p_uom_code,
283 p_duration => l_level_periods*l_duration
284 );
285 ELSE
286 l_end_date := l_next_start_date - 1;
287 END IF;
288 RETURN TRUNC(l_end_date);
289
290 EXCEPTION
291 WHEN INVALID_PERIOD_EXCEPTION THEN
292 OKC_API.set_message('OKS','OKS_INVD_PERIOD'); --?? need to seed this message
293 return NULL;
294 WHEN INVALID_UOM_EXCEPTION THEN
295 OKC_API.SET_MESSAGE(p_app_name => 'OKS',
296 p_msg_name => 'OKS_INVD_UOM_CODE',
297 p_token1 => 'OKS_API_NAME',
298 p_token1_value => 'oks_bill_util_pub.Get_Enddate_Cal',
299 p_token2 => 'UOM_CODE',
300 p_token2_value => p_uom_code);
301 IF cs_validate_uom%ISOPEN THEN
302 CLOSE cs_validate_uom;
303 END IF;
304 return NULL;
305 WHEN INVALID_DATE_EXCEPTION THEN
306 OKC_API.set_message('OKS','OKS_INVALID_START_END_DATES');
307 return NULL;
308 WHEN OTHERS THEN
309 --set the error message and return with NULL to notify the
310 --caller of error
311 OKC_API.SET_MESSAGE(p_app_name => G_APP_NAME,
312 p_msg_name => G_UNEXPECTED_ERROR,
313 p_token1 => G_SQLCODE_TOKEN,
314 p_token1_value => sqlcode,
315 p_token2 => G_SQLERRM_TOKEN,
316 p_token2_value => sqlerrm);
317
318 RETURN NULL;
319 END Get_Enddate_Cal;
320 -------------------------------------------------------------------------
321 -- End partial period computation logic
322 -- Date 04-MAY-2005
323 -------------------------------------------------------------------------
324
325
326 /*** This procedure is to get the seeded time unit and quantity, when the UOM Code is given as input ***/
327
328
329 PROCEDURE Get_sll_amount( p_api_version IN NUMBER,
330 p_total_amount IN NUMBER,
331 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
332 x_return_status OUT NOCOPY VARCHAR2 ,
333 x_msg_count OUT NOCOPY NUMBER ,
334 x_msg_data OUT NOCOPY VARCHAR2,
335 p_currency_code IN VARCHAR2,
336 p_sll_prorated_tab IN OUT NOCOPY sll_prorated_tab_type
337 )
338 IS
339 l_sll_num NUMBER;
340 i NUMBER;
341 j NUMBER;
342 l_sll_remain_amount NUMBER;
343 l_currency_code VARCHAR2(15);
344 l_round_sll_amount NUMBER;
345
346 l_tuom_code VARCHAR2(40);
347 l_tce_code VARCHAR2(10);
348 l_uom_quantity NUMBER;
349 l_curr_sll_start_date DATE;
350 l_curr_sll_end_date DATE;
351
352 l_next_sll_start_date DATE;
353 l_next_sll_end_date DATE;
354 l_sll_amount NUMBER;
355
356 l_curr_frequency NUMBER;
357 l_next_frequency NUMBER;
358 l_tot_frequency NUMBER;
359
360 l_return_status VARCHAR2(1);
361
362 BEGIN
363 l_sll_num := p_sll_prorated_tab.count;
364 l_sll_remain_amount := p_total_amount;
365
366 For i in 1 .. l_sll_num Loop
367 l_tuom_code := p_sll_prorated_tab(i).sll_tuom ;
368 oks_bill_util_pub.get_seeded_timeunit(p_timeunit => l_tuom_code ,
369 x_return_status => l_return_status,
370 x_quantity => l_uom_quantity,
371 x_timeunit => l_tce_code
372 );
373
374 l_curr_sll_start_date := p_sll_prorated_tab(i).sll_start_date;
375 l_curr_sll_end_date := p_sll_prorated_tab(i).sll_end_date;
376
377 IF l_tce_code = 'DAY' Then
378 l_curr_frequency := l_curr_sll_end_date - l_curr_sll_start_date + 1;
379 ELSIF l_tce_code = 'MONTH' Then
380 l_curr_frequency := months_between(l_curr_sll_end_date + 1, l_curr_sll_start_date) ;
381 ELSIF l_tce_code = 'YEAR' Then
382 l_curr_frequency := months_between(l_curr_sll_end_date + 1, l_curr_sll_start_date) / 12 ;
383 END IF;
384
385 If NVL(l_uom_quantity,0) > 0 Then
386 l_curr_frequency := l_curr_frequency / NVL(l_uom_quantity,1);
387 END IF;
388 l_tot_frequency := 0;
389 l_next_frequency := 0;
390
391 For j in i+1 .. l_sll_num Loop
392 l_next_sll_start_date := p_sll_prorated_tab(j).sll_start_date;
393 l_next_sll_end_date := p_sll_prorated_tab(j).sll_end_date;
394 IF l_tce_code = 'DAY' Then
395 l_next_frequency := l_next_frequency + (l_next_sll_end_date - l_next_sll_start_date + 1);
396 ELSIF l_tce_code = 'MONTH' Then
397 l_next_frequency := l_next_frequency + (months_between(l_next_sll_end_date + 1, l_next_sll_start_date)) ;
398 ELSIF l_tce_code = 'YEAR' Then
399 l_next_frequency := l_next_frequency + (months_between(l_next_sll_end_date + 1, l_next_sll_start_date) / 12) ;
400 END IF;
401 END LOOP;
402 l_tot_frequency := l_tot_frequency + l_curr_frequency + l_next_frequency;
403
404 l_sll_amount := ( l_sll_remain_amount / nvl(l_tot_frequency,1)) * nvl(l_curr_frequency,0) ;
405 l_round_sll_amount := OKS_EXTWAR_UTIL_PVT.round_currency_amt(l_sll_amount, l_currency_code);
406 l_round_sll_amount := OKS_EXTWAR_UTIL_PVT.round_currency_amt(l_round_sll_amount, l_currency_code);
407
408 l_sll_remain_amount := l_sll_remain_amount - l_round_sll_amount;
409
410 -- l_sll_remain_amount := OKS_EXTWAR_UTIL_PVT.round_currency_amt(l_sll_remain_amount, l_currency_code);
411
412 p_sll_prorated_tab(i).sll_amount := l_round_sll_amount;
413 l_curr_frequency := 0;
414 END LOOP;
415
416
417
418 END;
419
420
421
422
423 PROCEDURE get_seeded_timeunit ( p_timeunit in varchar2,
424 x_return_status out NOCOPY varchar2,
425 x_quantity out NOCOPY number,
426 x_timeunit out NOCOPY varchar2) IS
427
428 CURSOR time_code_unit_csr (p_uom_code IN varchar2) IS
429 SELECT tce_code, quantity
430 FROM okc_time_code_units_b
431 WHERE uom_code = p_uom_code
432 AND active_flag = 'Y';
433
434 l_new_qty NUMBER;
435 time_code_unit_rec time_code_unit_csr%ROWTYPE;
436 Item_not_found_error EXCEPTION;
437
438 BEGIN
439 x_return_status := OKC_API.G_RET_STS_SUCCESS;
440 OPEN time_code_unit_csr(p_timeunit);
441 FETCH time_code_unit_csr into time_code_unit_rec;
442
443 IF time_code_unit_csr%NOTFOUND THEN
444 -- OKC_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'uom_code');
445 CLOSE time_code_unit_csr;
446 RAISE item_not_found_error;
447 END IF;
448
449 CLOSE time_code_unit_csr;
450
451 IF time_code_unit_rec.tce_code = 'HOUR' THEN
452 l_new_qty := nvl(time_code_unit_rec.quantity,0) / 24 ; ---convert it in day
453 IF l_new_qty = ceil(l_new_qty) THEN
454 x_timeunit := 'DAY';
455 x_quantity := l_new_qty;
456 ELSE
457 RAISE item_not_found_error;
458 END IF;
459 ELSE ----not hour
460
461 x_timeunit := time_code_unit_rec.tce_code;
462 x_quantity := time_code_unit_rec.quantity;
463 END IF;
464
465 EXCEPTION
466
467 WHEN item_not_found_error THEN
468 x_return_status := OKC_API.G_RET_STS_ERROR;
469 WHEN OTHERS THEN
470 /* OKC_API.SET_MESSAGE(p_app_name => g_app_name,
471 p_msg_name => g_unexpected_error,
472 p_token1 => g_sqlcode_token,
473 p_token1_value => sqlcode,
474 p_token2 => g_col_name_token,
475 p_token2_value => 'uom_code',
476 p_token3 => g_sqlerrm_token,
477 p_token3_value => sqlerrm);*/
478 -- notify caller of an UNEXPECTED error
479 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
480 END get_seeded_timeunit;
481
482 /**** This procedure is to get prorated amount ****/
483
484 Procedure Get_prorate_amount
485 ( p_api_version IN NUMBER,
486 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
487 x_return_status OUT NOCOPY VARCHAR2,
488 x_msg_count OUT NOCOPY NUMBER,
489 x_msg_data OUT NOCOPY VARCHAR2,
490 p_invoicing_rule_id IN Number,
491 p_bill_sch_detail_rec IN bill_det_inp_rec,
492 x_bill_sch_detail_rec OUT NOCOPY bill_sch_rec
493 )
494 IS
495
496 /** Local input variables ***/
497 l_tuom_code Varchar2(10);
498 l_tce_code Varchar2(10);
499 l_uom_quantity Number;
500 l_total_amount Number;
501 l_start_date DATE;
502 l_end_date DATE;
503 l_cycle_start_date DATE;
504 l_action_offset Number;
505 l_interface_offset Number;
506 l_tuom_per_period Number;
507 l_return_status Varchar2(1) := 'S';
508
509 /*** Local output variables ***/
510 l_bill_amount Number;
511 l_next_billing_date DATE;
512 l_date_transaction DATE;
513 l_date_to_interface DATE;
514
515 /** local programming variables **/
516 l_frequency Number;
517 l_frequency_day Number;
518 l_frequency_mth Number;
519 l_frequency_yr Number;
520 l_frequency_qtr Number;
521 l_freq_descrep Number;
522
523 BEGIN
524 /** Get next billing amount ***/
525 x_return_status := l_return_status;
526
527 l_tuom_code := p_bill_sch_detail_rec.tuom;
528 l_tuom_per_period := p_bill_sch_detail_rec.tuom_per_period;
529 l_cycle_start_date := p_bill_sch_detail_rec.cycle_start_date;
530 l_start_date := p_bill_sch_detail_rec.line_start_date;
531 l_end_date := p_bill_sch_detail_rec.line_end_date;
532 l_total_amount := p_bill_sch_detail_rec.total_amount;
533
534 get_seeded_timeunit(p_timeunit => l_tuom_code,
535 x_return_status => l_return_status,
536 x_quantity => l_uom_quantity ,
537 x_timeunit => l_tce_code);
538
539 If l_end_date IS NULL or l_start_date IS NULL Then
540 x_return_status := 'E';
541 return;
542 End if;
543
544 l_next_billing_date := okc_time_util_pub.get_enddate
545 ( p_start_date => l_cycle_start_date,
546 p_timeunit => l_tuom_code,
547 p_duration => l_tuom_per_period
548 ) + 1 ;
549
550
551 /*** Line Start date is replaced by cycle start date, to get avoid proration of entire amount
552 while calculating Bill amount ***/
553
554 IF x_return_status = 'S' Then
555 If l_next_billing_date <= l_start_date Then
556 l_bill_amount := 0;
557 Else
558 IF l_tce_code = 'DAY' Then
559 l_frequency_day := (l_end_date - l_cycle_start_date) + 1;
560 l_frequency := l_frequency_day;
561 Elsif l_tce_code = 'MONTH' Then
562 l_frequency_mth := months_between(l_end_date + 1,l_cycle_start_date);
563 l_frequency := l_frequency_mth;
564 IF NVL(l_uom_quantity,1) <> 1 Then /* quarterly frequency */
565 l_frequency_qtr := l_frequency/l_uom_quantity;
566 l_frequency := l_frequency_qtr;
567 end if;
568 Elsif l_tce_code = 'YEAR' Then
569 l_frequency_yr := months_between(l_end_date + 1, l_cycle_start_date)/12;
570 l_frequency := l_frequency_yr;
571 End if;
572 l_bill_amount := (nvl(l_total_amount,0)/nvl(l_frequency,1)) * nvl(l_tuom_per_period,0);
573
574 /*** This section has been added to check for cp lines ****/
575
576 If l_start_date > l_cycle_start_date and l_start_date < l_next_billing_date Then
577 If l_tce_code = 'DAY' Then
578 l_freq_descrep := l_next_billing_date - l_start_date;
579 Elsif l_tce_code = 'MONTH' Then
580 l_freq_descrep := months_between(l_next_billIng_date, l_start_date);
581 If nvl(l_uom_quantity,1) <> 1 then
582 l_freq_descrep := l_freq_descrep/l_uom_quantity;
583 End if;
584 Elsif l_tce_code = 'YEAR' Then
585 l_freq_descrep := months_between(l_next_billing_date, l_start_date)/12;
586 End if;
587 l_bill_amount := (nvl(l_total_amount,0)/nvl(l_frequency,1)) * nvl(l_freq_descrep,0);
588 End if;
589
590 End if;
591 End if;
592
593 x_bill_sch_detail_rec.next_cycle_date := l_next_billing_date;
594 -- x_bill_sch_detail_rec.date_transaction := l_date_transaction;
595 -- x_bill_sch_detail_rec.date_to_interface := l_date_to_interface;
596 x_bill_sch_detail_rec.cycle_amount := l_bill_amount;
597
598 x_return_status := 'S';
599
600 EXCEPTION
601 WHEN OTHERS THEN
602 x_return_status := OKC_API.G_RET_STS_ERROR;
603
604 END;
605
606 Function Get_frequency
607 (p_tce_code IN VARCHAR2,
608 p_fr_start_date IN DATE,
609 p_fr_end_date IN DATE,
610 p_uom_quantity IN Number,
611 x_return_status OUT NOCOPY VARCHAR2
612 ) Return NUMBER
613 IS
614
615 l_frequency_day NUMBER;
616 l_frequency_week NUMBER;
617 l_frequency_mth NUMBER;
618 l_frequency_yr NUMBER;
619 l_frequency_qtr NUMBER;
620 l_frequency NUMBER;
621
622 BEGIN
623
624 IF p_tce_code = 'DAY' Then
625 l_frequency_day := (p_fr_end_date - p_fr_start_date) + 1;
626 l_frequency := l_frequency_day;
627 /*** This section has been modified to handle UOM = week ***/
628 /*** -- aiyengar, 10/01/2001 **/
629
630 IF NVL(p_uom_quantity,1) <> 1 Then
631 l_frequency_week := l_frequency / p_uom_quantity;
632 l_frequency := l_frequency_week;
633 END IF;
634 Elsif p_tce_code = 'MONTH' Then
635 l_frequency_mth := months_between(p_fr_end_date + 1,p_fr_start_date);
636 l_frequency := l_frequency_mth;
637
638
639 IF NVL(p_uom_quantity,1) <> 1 Then -- quarterly frequency
640
641 l_frequency_qtr := l_frequency/p_uom_quantity;
642 l_frequency := l_frequency_qtr;
643
644 end if;
645 Elsif p_tce_code = 'YEAR' Then
646 l_frequency_yr := months_between(p_fr_end_date + 1, p_fr_start_date)/12;
647 l_frequency := l_frequency_yr;
648 End if;
649 x_return_status := 'S';
650 RETURN l_frequency;
651
652 EXCEPTION
653 WHEN OTHERS THEN
654 x_return_status := OKC_API.G_RET_STS_ERROR;
655 END ;
656
657 -------------------------------------------------------------------------
658 -- Begin partial period computation logic
659 -- Developer Mani Choudhary
660 -- Date 09-MAY-2005
661 -- Added two new parameters P_period_start,P_period_type
662 -- Changed the logic for deriving l_next_billing_date
663 -------------------------------------------------------------------------
664 Procedure Get_next_bill_sch
665 ( p_api_version IN NUMBER,
666 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
667 x_return_status OUT NOCOPY VARCHAR2,
668 x_msg_count OUT NOCOPY NUMBER,
669 x_msg_data OUT NOCOPY VARCHAR2,
670 p_invoicing_rule_id IN Number,
671 p_bill_sch_detail_rec IN bill_det_inp_rec,
672 x_bill_sch_detail_rec OUT NOCOPY bill_sch_rec,
673 P_period_start IN VARCHAR2,
674 P_period_type IN VARCHAR2,
675 -- Start - Added by PMALLARA - Bug #3992530
676 Strm_Start_Date IN DATE
677 -- End - Added by PMALLARA - Bug #3992530
678 )
679 IS
680
681 /** Local input variables ***/
682 l_tuom_code Varchar2(10);
683 l_tce_code Varchar2(10);
684 l_uom_quantity Number;
685 l_total_amount Number;
686 l_start_date DATE;
687 l_end_date DATE;
688 l_cycle_start_date DATE;
689 l_action_offset Number;
690 l_interface_offset Number;
691 l_tuom_per_period Number;
692 l_return_status Varchar2(1) := 'S';
693
694 /*** Local output variables ***/
695 l_bill_amount Number;
696 l_next_billing_date DATE;
697 l_date_transaction DATE;
698 l_date_to_interface DATE;
699
700 /** local programming variables **/
701 l_frequency Number;
702 l_frequency_day Number;
703 l_frequency_mth Number;
704 l_frequency_yr Number;
705 l_frequency_qtr Number;
706 l_freq_descrep Number;
707 l_frequency_week Number;
708 l_fr_start_date Date;
709 l_fr_end_date Date;
710 l_next_date Date;
711 l_uom_per_period Number;
712 BEGIN
713 /** Get next billing amount for refresh schedule ***/
714 x_return_status := l_return_status;
715
716 l_tuom_code := p_bill_sch_detail_rec.tuom;
717 l_tuom_per_period := p_bill_sch_detail_rec.tuom_per_period;
718 l_cycle_start_date := p_bill_sch_detail_rec.cycle_start_date;
719 l_start_date := p_bill_sch_detail_rec.line_start_date;
720 l_end_date := p_bill_sch_detail_rec.line_end_date;
721 l_total_amount := p_bill_sch_detail_rec.total_amount;
722 l_uom_per_period := p_bill_sch_detail_rec.uom_per_period;
723
724 get_seeded_timeunit(
725 p_timeunit => l_tuom_code,
726 x_return_status => l_return_status,
727 x_quantity => l_uom_quantity ,
728 x_timeunit => l_tce_code);
729
730 If l_end_date IS NULL or l_start_date IS NULL Then
731 x_return_status := 'E';
732 return;
733 End if;
734
735 -------------------------------------------------------------------------
736 -- Begin partial period computation logic
737 -- Developer Mani Choudhary
738 -- Date 09-MAY-2005
739 -------------------------------------------------------------------------
740 IF p_period_start IS NOT NULL AND
741 p_period_type IS NOT NULL AND
742 p_period_start = 'CALENDAR' AND
743 l_tce_code not in ('DAY','HOUR','MINUTE')
744 THEN
745
746 --if stream start date not the start date of CALENDAR and cycle
747 --start date is equal to the stream start date then it is the first
748 --partial period
749
750
751 IF TRUNC(Strm_Start_date,'MM') <> TRUNC(Strm_Start_date) AND
752 TRUNC(l_cycle_start_date) = TRUNC(Strm_Start_date)
753 THEN
754
755 l_next_billing_date := Last_day(TRUNC(Strm_Start_date))+ 1 ;
756
757 ELSE
758 IF TRUNC(Strm_Start_date,'MM') <> TRUNC(Strm_Start_date) THEN
759 l_tuom_per_period := l_tuom_per_period - l_uom_per_period;
760 l_next_date :=Last_day(TRUNC(Strm_Start_date))+ 1 ;
761 ELSE
762 l_next_date := Strm_Start_date;
763 END IF;
764 l_next_billing_date:= OKC_TIME_UTIL_PUB.GET_ENDDATE
765 (p_start_date => l_next_date,
766 p_timeunit => l_tuom_code,
767 p_duration => l_tuom_per_period
768 )+1;
769 END IF;
770 -------------------------------------------------------------------------
771 -- End partial period computation logic
772 -------------------------------------------------------------------------
773 ELSE
774
775 -- Start - Modified by PMALLARA - Bug #3992530
776 l_next_billing_date := okc_time_util_pub.get_enddate
777 ( p_start_date => Strm_Start_Date,
778 p_timeunit => l_tuom_code,
779 p_duration => l_tuom_per_period
780 ) + 1 ;
781 -- End - Modified by PMALLARA - Bug #3992530
782 END IF;
783
784 /** Get transaction offset date ***/
785
786 l_action_offset := NVL(p_bill_sch_detail_rec.invoice_offset_days,0);
787
788 If p_invoicing_rule_id = -2 Then /*** For advance ****/
789
790 l_date_transaction := l_cycle_start_date + l_action_offset;
791
792 If l_date_transaction < SYSDATE Then
793 l_date_transaction := SYSDATE;
794 End if;
795
796 Elsif p_invoicing_rule_id = -3 Then /*** For arrears ****/
797
798 ---if not terminated subcription line .
799 IF l_next_billing_date > l_end_date AND p_bill_sch_detail_rec.bill_type <> 'S' THEN
800 l_date_transaction := l_end_date + l_action_offset;
801 ELSE
802
803 l_date_transaction := (l_next_billing_date - 1 ) + l_action_offset;
804 END IF;
805
806 ----l_date_transaction SHOULD not be less then bill from date and sysdate.
807
808 IF l_date_transaction < l_cycle_start_date THEN
809 l_date_transaction := l_cycle_start_date;
810 END IF;
811
812 If l_date_transaction < SYSDATE Then
813 l_date_transaction := SYSDATE;
814 End if;
815
816 End if;
817
818 /*** Get Interface offset date ***/
819
820 l_interface_offset := nvl(p_bill_sch_detail_rec.interface_offset_days,0);
821
822 If p_invoicing_rule_id = -2 Then /*** advance ***/
823
824 l_date_to_interface := l_cycle_start_date + l_interface_offset;
825 IF l_date_to_interface > LEAST(l_date_transaction, l_cycle_start_date) Then
826 l_date_to_interface := LEAST(l_date_transaction, l_cycle_start_date);
827 End if;
828 /* Commented for bug # 2359734 as told by hari and adas
829 If l_date_to_interface < SYSDATE Then
830 l_date_to_interface := SYSDATE;
831 End If;*/
832
833 ELSIF p_invoicing_rule_id = -3 Then
834
835 ---if not terminated subcription line .
836 If l_next_billing_date > l_end_date AND p_bill_sch_detail_rec.bill_type <> 'S' Then
837 l_date_to_interface := (l_end_date + 1 )+ l_interface_offset ;
838 Else
839 l_date_to_interface := l_next_billing_date + l_interface_offset; /** Bill to date + 1 ***/
840 End if;
841
842 /* Commented for bug # 2359734 as told by hari and adas
843 /* Added for bug 2115578.
844 IF l_date_to_interface < SYSDATE THEN
845 l_date_to_interface := SYSDATE;
846 END IF;*/
847
848 END IF;
849
850 x_bill_sch_detail_rec.next_cycle_date := l_next_billing_date;
851 x_bill_sch_detail_rec.date_transaction := l_date_transaction;
852 x_bill_sch_detail_rec.date_to_interface := l_date_to_interface;
853 x_bill_sch_detail_rec.cycle_amount := l_bill_amount;
854
855 x_return_status := 'S';
856
857 EXCEPTION
858 WHEN OTHERS THEN
859 x_return_status := OKC_API.G_RET_STS_ERROR;
860
861 END;
862
863 Procedure Get_next_level_element
864 ( p_api_version IN NUMBER,
865 p_id IN NUMBER,
866 p_covd_flag IN VARCHAR2,
867 p_date IN DATE,
868 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
869 x_return_status OUT NOCOPY VARCHAR2,
870 x_msg_count OUT NOCOPY NUMBER,
871 x_msg_data OUT NOCOPY VARCHAR2,
872 x_next_level_element OUT NOCOPY LEVEL_ELEMENT_TAB
873 )
874 IS
875
876 l_bill_cycle_end_date DATE := NULL;
877
878 Cursor l_csr_level_elements IS
879 SELECT le.date_start date_start,
880 le.date_end date_end,
881 le.id id,
882 le.amount amount ,
883 le.date_revenue_rule_start date_revenue_rule_start,
884 le.date_receivable_gl date_receivable_gl,
885 le.date_transaction date_transaction,
886 nvl(le.date_to_interface,sysdate) date_to_interface,
887 le.date_due date_due,
888 le.date_completed date_completed,
889 le.rul_id rul_id,
890 le.date_print date_print,
891 le.sequence_number sequence_number,
892 str.uom_code advance_period,
893 str.uom_per_period tuom_per_period,
894 str.start_date tp_start_date
895 FROM oks_stream_levels_b str
896 ,oks_level_elements le
897 WHERE le.cle_id = p_id
898 AND le.rul_id = str.id
899 AND le.date_completed IS NULL
900 AND trunc(nvl(le.date_to_interface,sysdate)) <= trunc(p_date)
901 AND not exists
902 (select 1 from oks_bill_sub_lines bsl
903 where le.cle_id = bsl.cle_id
904 and trunc(le.date_start) >= trunc(bsl.date_billed_from)
905 and trunc(le.date_end) <= trunc(bsl.date_billed_to))
906 ORDER BY le.date_start;
907
908 ----Exists clause is added as part of bug# 4915707 wherein skipped level elements have to be billed
909
910
911 Cursor l_next_level_element(p_cle_id in NUMBER,p_date in DATE) IS
912 SELECT lvl.date_start
913 FROM oks_level_elements lvl
914 WHERE lvl.cle_id = p_cle_id
915 AND lvl.date_start > p_date
916 ORDER BY lvl.date_start;
917
918 Cursor l_csr_get_enddate IS
919 SELECT date_terminated,end_date ,start_date
920 FROM okc_k_lines_b
921 WHERE id = p_id;
922
923 /**********
924 These two selects were included to ensure that
925 there wont be any duplicate bills
926 --- Hari 11/30/2001
927 ********/
928 /*
929 Cursor l_bcl_csr(p_cle_id IN NUMBER) is
930 Select max(date_billed_to)
931 From oks_bill_cont_lines
932 WHERE cle_id = p_cle_id;
933 */
934
935
936 Cursor l_bsl_csr(p_cle_id IN NUMBER) is
937 SELECT max(date_billed_to)
938 FROM oks_bill_sub_lines
939 WHERE cle_id = p_cle_id;
940
941 level_element_rec L_CSR_LEVEL_ELEMENTS%ROWTYPE;
942 i NUMBER := 1;
943 l_advance_period VARCHAR2(3);
944 l_tuom_quantity NUMBER;
945 l_terminated_date OKC_K_LINES_B.DATE_TERMINATED%TYPE;
946 l_end_date OKC_K_LINES_B.END_DATE%TYPE;
947 l_start_date OKC_K_LINES_B.START_DATE%TYPE;
948 l_bill_to_date DATE;
949
950 BEGIN
951
952 OPEN l_csr_level_elements;
953 LOOP
954 FETCH l_csr_level_elements into level_element_rec;
955 IF (l_csr_level_elements%Notfound ) THEN
956 Exit;
957 ELSE
958 l_advance_period := level_element_rec.advance_period;
959 l_tuom_quantity:= level_element_rec.tuom_per_period;
960 x_next_level_element(i).id := level_element_rec.id;
961 x_next_level_element(i).sequence_number := level_element_rec.sequence_number;
962 x_next_level_element(i).bill_from_date := level_element_rec.date_start;
963 x_next_level_element(i).bill_to_date := level_element_rec.date_end;
964 x_next_level_element(i).bill_amount := level_element_rec.amount;
965 x_next_level_element(i).date_to_interface := level_element_rec.date_to_interface;
966 x_next_level_element(i).date_receivable_gl := level_element_rec.date_receivable_gl;
967 x_next_level_element(i).date_transaction := level_element_rec.date_transaction;
968 x_next_level_element(i).date_due := level_element_rec.date_due;
969 x_next_level_element(i).date_print := level_element_rec.date_print;
970 x_next_level_element(i).date_revenue_rule_start := level_element_rec.date_revenue_rule_start;
971 x_next_level_element(i).date_completed := level_element_rec.date_completed;
972 x_next_level_element(i).rule_id := level_element_rec.rul_id;
973
974
975 /* This whole logic is required to get end_date of billing period
976 if end_date is NULL. This is because end_date field is added in
977 11.5.10 . However it was not migrated from previous version
978 due to performance issue.
979 */
980
981 IF (x_next_level_element(i).bill_to_date iS NULL) THEN
982
983 OPEN l_csr_get_enddate;
984 FETCH l_csr_get_enddate INTO l_terminated_date,
985 l_end_date,
986 l_start_date;
987 CLOSE l_csr_get_enddate;
988
989 OPEN l_next_level_element(p_id,level_element_rec.date_start);
990 FETCH l_next_level_element into l_bill_to_date;
991 IF (l_next_level_element%FOUND ) THEN
992 l_bill_to_date := l_bill_to_date - 1;
993 ELSE
994 IF(l_terminated_date is NULL) THEN
995 l_bill_to_date := l_end_date;
996 --ELSIF ((l_terminated_date is NOT NULL) AND
997 -- (trunc(l_start_date) = trunc(level_element_rec.date_start) )) THEN
998 -- l_bill_to_date := okc_time_util_pub.get_enddate(
999 -- to_date(level_element_rec.tp_start_date),
1000 -- l_advance_period ,
1001 -- l_tuom_quantity );
1002 ELSIF (l_terminated_date is NOT NULL) THEN
1003 -- (trunc(l_start_date) <> trunc(level_element_rec.date_start) )) THEN
1004 l_bill_to_date := okc_time_util_pub.get_enddate
1005 (level_element_rec.date_start,
1006 l_advance_period ,
1007 l_tuom_quantity );
1008 END IF;
1009 END IF;
1010 CLOSE l_next_level_element;
1011
1012
1013 IF (l_end_date) < l_bill_to_date Then
1014 l_bill_to_date := l_end_date;
1015 END IF;
1016
1017 x_next_level_element(i).bill_to_date := l_bill_to_date;
1018 END IF;
1019
1020 i := i + 1;
1021 END IF ;
1022
1023 END LOOP;
1024 CLOSE l_csr_level_elements;
1025 x_return_status := OKC_API.G_RET_STS_SUCCESS ;
1026
1027 EXCEPTION
1028 WHEN OTHERS THEN
1029 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1030 END;
1031
1032
1033 Function Get_total_inv_billed(p_api_version IN Varchar2,
1034 p_rule_id IN Number,
1035 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1036 x_return_status OUT NOCOPY Varchar2,
1037 x_msg_count OUT NOCOPY NUMBER,
1038 x_msg_data OUT NOCOPY VARCHAR2)
1039 RETURN NUMBER
1040
1041 IS
1042
1043 l_total_inv_billed Number;
1044
1045 Cursor l_csr_total_inv_billed IS
1046 Select count(id)
1047 From oks_level_elements
1048 Where rul_id = p_rule_id
1049 And date_completed IS NOT NULL;
1050
1051 Begin
1052 Open l_csr_total_inv_billed;
1053 Fetch l_csr_total_inv_billed INTO l_total_inv_billed;
1054 Close l_csr_total_inv_billed;
1055
1056 Return l_total_inv_billed;
1057 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1058
1059 EXCEPTION
1060 WHEN OTHERS THEN
1061 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1062 RAISE G_EXCEPTION_HALT_VALIDATION;
1063 End Get_total_inv_billed;
1064
1065
1066
1067
1068 Procedure delete_row_level_elements( p_rul_id IN Number,
1069 p_seq_no IN Number,
1070 x_return_status OUT NOCOPY Varchar2)
1071 IS
1072
1073
1074
1075 Begin
1076
1077 --delete level elements for given sll id
1078
1079 DELETE from OKS_LEVEL_ELEMENTS
1080 where rul_id = p_rul_id ;
1081
1082 --DELETE SLL
1083 DELETE FROM oks_stream_levels_b
1084 WHERE id = p_rul_id;
1085
1086 ExceptioN
1087 When Others then
1088 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1089 End;
1090
1091 PROCEDURE pre_del_level_elements(
1092 p_api_version IN NUMBER,
1093 p_terminated_date IN DATE,
1094 p_id IN NUMBER , --1 for line ,2 for covered level
1095 p_flag IN NUMBER,
1096 x_return_status OUT NOCOPY VARCHAR2
1097 )IS
1098 CURSOR coverage (p_line_id IN NUMBER) is
1099 SELECT id from okc_k_lines_b
1100 WHERE cle_id = p_line_id
1101 AND lse_id in (7,8,9,10,11,35,18,13,25);
1102
1103 l_msg_count NUMBER;
1104 l_msg_data VARCHAR2(2000);
1105 BEGIN
1106 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1107 IF (p_flag = 1) THEN
1108 oks_bill_util_pub.delete_level_elements
1109 (
1110 p_api_version => 1.0,
1111 p_terminated_date => p_terminated_date,
1112 p_chr_id => NULL,
1113 p_cle_id => p_id,
1114 p_init_msg_list => 'T',
1115 x_return_status => x_return_status,
1116 x_msg_count => l_msg_count,
1117 x_msg_data => l_msg_data
1118 );
1119 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1120 raise G_EXCEPTION_HALT_VALIDATION;
1121 END IF;
1122
1123 FOR cov_cur in coverage(p_id)
1124 LOOP
1125 oks_bill_util_pub.delete_level_elements
1126 (
1127 p_api_version => 1.0,
1128 p_terminated_date => p_terminated_date,
1129 p_chr_id => NULL,
1130 p_cle_id => cov_cur.id,
1131 p_init_msg_list => 'T',
1132 x_return_status => x_return_status,
1133 x_msg_count => l_msg_count,
1134 x_msg_data => l_msg_data
1135 );
1136 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1137 raise G_EXCEPTION_HALT_VALIDATION;
1138 END IF;
1139
1140 END LOOP;
1141
1142
1143 ELSIF (p_flag = 2) THEN
1144 oks_bill_util_pub.delete_level_elements
1145 (
1146 p_api_version => 1.0,
1147 p_terminated_date => p_terminated_date,
1148 p_chr_id => NULL,
1149 p_cle_id => p_id,
1150 p_init_msg_list => 'T',
1151 x_return_status => x_return_status,
1152 x_msg_count => l_msg_count,
1153 x_msg_data => l_msg_data
1154 );
1155 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1156 raise G_EXCEPTION_HALT_VALIDATION;
1157 END IF;
1158
1159 END IF;
1160 EXCEPTION
1161 WHEN OTHERS THEN
1162 x_return_status := OKC_API.G_RET_STS_ERROR;
1163 END pre_del_level_elements;
1164
1165
1166 Procedure delete_level_elements (p_api_version IN NUMBER,
1167 p_rule_id IN Number,
1168 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1169 x_msg_count OUT NOCOPY NUMBER,
1170 x_msg_data OUT NOCOPY VARCHAR2,
1171 x_return_status OUT NOCOPY Varchar2 )
1172 IS
1173
1174 ----modified by upma for rules re-arch.
1175
1176 /*** This cursor will get sll info */
1177
1178
1179 Cursor l_get_line_sll_csr IS
1180 Select id, cle_id , sequence_no
1181 From Oks_stream_levels_b
1182 where id = p_rule_id;
1183
1184
1185 /*** This cursor will get the line_id for all sub lines of Top Line for which Rule id was passed, in
1186 to the above cursor ***/
1187
1188 Cursor l_get_cp_csr(p_cle_id Number) IS
1189 Select id
1190 From Okc_k_lines_b
1191 Where cle_id = p_cle_id
1192 and lse_id in (35,7,8,9,10,11,13,18,25);
1193
1194
1195 /** This cursor gets the rule id for covered products ****/
1196
1197 Cursor l_get_cp_sll_csr(p_cp_line_id Number, l_seq_no NUMBER) IS
1198 Select id , sequence_no
1199 From oks_stream_levels_b
1200 Where cle_id = p_cp_line_id
1201 And sequence_no = l_seq_no;
1202
1203 CURSOR l_bill_type_csr(p_line_id NUMBER) IS
1204 SELECT nvl(billing_schedule_type,'T') billing_schedule_type
1205 FROM oks_k_lines_b
1206 WHERE cle_id = p_line_id;
1207
1208 l_get_line_sll_rec l_get_line_sll_csr%ROWTYPE;
1209 l_get_cp_rec l_get_cp_csr%ROWTYPE;
1210 l_get_cp_sll_rec l_get_cp_sll_csr%ROWTYPE;
1211 l_bill_type_rec l_bill_type_csr%ROWTYPE;
1212
1213
1214 Begin
1215
1216 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1217
1218
1219 /*** get sll info for a given rule id**/
1220
1221 Open l_get_line_sll_csr;
1222 Fetch l_get_line_sll_csr into l_get_line_sll_rec;
1223
1224 IF l_get_line_sll_csr%NOTFOUND Then
1225 CLOSE l_get_line_sll_csr;
1226 RETURN;
1227 END IF;
1228
1229 ---get bill type details
1230 Open l_bill_type_Csr(l_get_line_sll_rec.cle_id);
1231 Fetch l_bill_type_Csr Into l_bill_type_Rec;
1232
1233 If l_bill_type_csr%Notfound then
1234 Close l_bill_type_Csr;
1235 x_return_status := 'E';
1236 OKC_API.set_message(G_PKG_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'BILLING SCHEDULE TYPE NOT FOUND.');
1237 RAISE G_EXCEPTION_HALT_VALIDATION;
1238 End If;
1239 Close l_bill_type_Csr;
1240
1241
1242 IF l_bill_type_rec.billing_schedule_type <> 'P' Then
1243 /** If rule type is 'P' then do not delete covered level rule sll level elements
1244 Else Delete ***/
1245
1246 FOR l_get_cp_rec IN l_get_cp_csr(l_get_line_sll_rec.cle_id)
1247 Loop
1248 Open l_get_cp_sll_csr(l_get_cp_rec.id,l_get_line_sll_rec.sequence_no) ;
1249 Fetch l_get_cp_sll_csr Into l_get_cp_sll_rec;
1250
1251 If l_get_cp_sll_csr%Notfound then
1252 close l_get_cp_sll_csr;
1253
1254 ELSE
1255 /*** Delete level elements ****/
1256 Delete_row_level_elements (l_get_cp_sll_rec.id,
1257 l_get_cp_sll_rec.sequence_no,
1258 x_return_status);
1259
1260
1261 close l_get_cp_sll_csr;
1262 End if;
1263
1264 End loop; ---sub line end loop
1265 END IF; -- billing type <> 'P'
1266
1267
1268
1269 /*** Delete rule and level elements of the rule id that was passed ****/
1270 Delete_row_level_elements (p_rule_id,
1271 l_get_line_sll_rec.cle_id,
1272 x_return_status);
1273
1274 CLOSE l_get_line_sll_csr;
1275
1276 EXCEPTION
1277
1278 WHEN OTHERS THEN
1279 x_return_status := 'E';
1280
1281 End Delete_level_elements;
1282
1283
1284
1285 PROCEDURE delete_level_elements(
1286 p_api_version IN NUMBER,
1287 p_terminated_date IN DATE,
1288 p_chr_id IN NUMBER,
1289 p_cle_id IN NUMBER ,
1290 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1291 x_return_status OUT NOCOPY VARCHAR2,
1292 x_msg_count OUT NOCOPY NUMBER,
1293 x_msg_data OUT NOCOPY VARCHAR2
1294 )
1295
1296 IS
1297
1298 ---Modified by upma for re-arch.
1299 --deltes lvl elements for line and sub line.
1300
1301 Cursor l_subLine_Csr(l_line_id number) Is
1302 SELECT id , TRUNC(date_terminated) cp_term_dt
1303 FROM okc_k_lines_b
1304 WHERE cle_id = l_line_id
1305 AND lse_id in (35,7,8,9,10,11,13,18,25);
1306
1307 l_subline_rec l_subline_csr%ROWTYPE;
1308
1309
1310 BEGIN
1311
1312 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1313
1314 IF p_cle_id is NULL THEN /* when input parm is p_chr_id */
1315 DELETE FROM OKS_LEVEL_ELEMENTS
1316 WHERE Date_Completed is NULL
1317 AND TRUNC(date_start) >= TRUNC(p_terminated_date)
1318 AND dnz_chr_id = p_chr_id;
1319
1320
1321 ELSE
1322
1323 DELETE FROM OKS_LEVEL_ELEMENTS
1324 WHERE Date_Completed is NULL
1325 AND TRUNC(date_start) >= TRUNC(p_terminated_date)
1326 AND cle_id = p_cle_id;
1327
1328 ----DELETE lvl elements for subline
1329
1330 FOR l_subline_rec IN l_subline_csr(p_cle_id)
1331 LOOP
1332
1333 DELETE FROM OKS_LEVEL_ELEMENTS
1334 WHERE Date_Completed is NULL
1335 AND TRUNC(date_start) >= nvl(TRUNC(l_subline_rec.cp_term_dt),TRUNC(p_terminated_date))
1336 AND cle_id = l_subline_rec.id;
1337 END LOOP;
1338
1339
1340 END IF;
1341
1342 EXCEPTION
1343
1344 WHEN OTHERS THEN
1345 x_return_status := 'E';
1346
1347
1348 END delete_level_elements;
1349
1350
1351 /*** deleting rules ***/
1352 PROCEDURE delete_rule(
1353 p_api_version IN NUMBER,
1354 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1355 p_chr_id IN NUMBER,
1356 x_return_status OUT NOCOPY VARCHAR2,
1357 x_msg_count OUT NOCOPY NUMBER,
1358 x_msg_data OUT NOCOPY VARCHAR2) IS
1359
1360
1361 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1362 i NUMBER := 1;
1363
1364 cursor rule_csr Is
1365 select rul.id
1366 from okc_rules_b rul
1367 where rul.rule_information_category in ('IRE') and
1368 dnz_chr_id = p_chr_id;
1369
1370 p_rulv_tbl OKC_RULE_PUB.rulv_tbl_type;
1371 l_rule_id number;
1372
1373 BEGIN
1374
1375 open rule_csr;
1376
1377 LOOP
1378 Fetch rule_csr Into l_rule_id;
1379 EXIT WHEN rule_csr%NOTFOUND;
1380 p_rulv_tbl(i).id := l_rule_id;
1381 i := i + 1;
1382
1383 END LOOP;
1384
1385 close rule_csr;
1386
1387 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1388
1389 IF p_rulv_tbl.COUNT > 0 THEN
1390
1391 OKC_RULE_PUB.delete_rule(
1392 p_api_version => p_api_version,
1393 p_init_msg_list => p_init_msg_list,
1394 x_return_status => l_return_status,
1395 x_msg_count => x_msg_count,
1396 x_msg_data => x_msg_data,
1397 p_rulv_tbl => p_rulv_tbl);
1398
1399 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1400 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1401 x_return_status := l_return_status;
1402 raise G_EXCEPTION_HALT_VALIDATION;
1403 ELSE
1404 x_return_status := l_return_status;
1405 END IF;
1406 END IF;
1407
1408 END IF;
1409
1410
1411 EXCEPTION
1412
1413 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1414 NULL;
1415 WHEN OTHERS THEN
1416 -- store SQL error message on message stack
1417 OKC_API.SET_MESSAGE(
1418 p_app_name => G_APP_NAME,
1419 p_msg_name => G_UNEXPECTED_ERROR,
1420 p_token1 => G_SQLCODE_TOKEN,
1421 p_token1_value => SQLCODE,
1422 p_token2 => G_SQLERRM_TOKEN,
1423 p_token2_value => SQLERRM);
1424 -- notify caller of an error as UNEXPETED error
1425 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1426
1427 END Delete_Rule;
1428
1429
1430 /*** deleting rules ***/
1431
1432 PROCEDURE delete_slh_rule(
1433 p_api_version IN NUMBER,
1434 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1435 p_cle_id IN NUMBER,
1436 x_return_status OUT NOCOPY VARCHAR2,
1437 x_msg_count OUT NOCOPY NUMBER,
1438 x_msg_data OUT NOCOPY VARCHAR2)
1439 IS
1440 BEGIN
1441
1442 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1443
1444 UPDATE oks_k_lines_b SET billing_schedule_type = NULL
1445 WHERE cle_id =p_cle_id;
1446
1447 DELETE FROM oks_stream_levels_b where cle_id = P_CLE_ID;
1448
1449 EXCEPTION
1450
1451 WHEN OTHERS THEN
1452 -- store SQL error message on message stack
1453 OKC_API.SET_MESSAGE(
1454 p_app_name => G_APP_NAME,
1455 p_msg_name => G_UNEXPECTED_ERROR,
1456 p_token1 => G_SQLCODE_TOKEN,
1457 p_token1_value => SQLCODE,
1458 p_token2 => G_SQLERRM_TOKEN,
1459 p_token2_value => SQLERRM);
1460 -- notify caller of an error as UNEXPETED error
1461 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1462
1463 END Delete_SLH_Rule;
1464
1465
1466
1467
1468 /** Code for changing/splitting service lines **/
1469
1470 procedure get_rev_distr(p_cle_id IN NUMBER,
1471 x_rev_tbl OUT NOCOPY OKS_REV_DISTR_PUB.rdsv_tbl_type) IS
1472 cursor rev_cur is
1473 select
1474 chr_id, cle_id,
1475 account_class,
1476 code_combination_id,
1477 percent
1478 from oks_rev_distributions
1479 where cle_id = p_cle_id;
1480 i NUMBER := 1;
1481 begin
1482 for rev_rec in rev_cur
1483 loop
1484 x_rev_tbl(i).id := OKC_API.G_MISS_NUM;
1485 x_rev_tbl(i).chr_id := rev_rec.chr_id;
1486 x_rev_tbl(i).account_class := rev_rec.account_class;
1487 x_rev_tbl(i).code_combination_id := rev_rec.code_combination_id;
1488 x_rev_tbl(i).percent := rev_rec.percent;
1489 x_rev_tbl(i).object_version_number := OKC_API.G_MISS_NUM;
1490 x_rev_tbl(i).created_by := OKC_API.G_MISS_NUM;
1491 x_rev_tbl(i).creation_date := OKC_API.G_MISS_DATE;
1492 x_rev_tbl(i).last_updated_by := OKC_API.G_MISS_NUM;
1493 x_rev_tbl(i).last_update_date := OKC_API.G_MISS_DATE;
1494 x_rev_tbl(i).last_update_login := OKC_API.G_MISS_NUM;
1495 i := i + 1;
1496 end loop;
1497 end get_rev_distr;
1498
1499 procedure create_rev_distr(p_cle_id IN NUMBER,
1500 p_rev_tbl IN OUT NOCOPY OKS_REV_DISTR_PUB.rdsv_tbl_type,
1501 x_status OUT NOCOPY VARCHAR2) IS
1502 l_api_version NUMBER := 1.0;
1503 l_msg_count NUMBER;
1504 l_msg_data VARCHAR2(2000);
1505 l_rev_tbl OKS_REV_DISTR_PUB.rdsv_tbl_type;
1506 i NUMBER;
1507 begin
1508 i := p_rev_tbl.FIRST;
1509 Loop
1510 p_rev_tbl(i).cle_id := p_cle_id;
1511 exit when i = p_rev_tbl.LAST;
1512 i := p_rev_tbl.NEXT(i);
1513 End Loop;
1514 OKS_REV_DISTR_PUB.insert_Revenue_Distr(
1515 p_api_version => l_api_version,
1516 x_return_status => x_status,
1517 x_msg_count => l_msg_count,
1518 x_msg_data => l_msg_data,
1519 p_rdsv_tbl => p_rev_tbl,
1520 x_rdsv_tbl => l_rev_tbl);
1521 end create_rev_distr;
1522
1523 procedure get_sales_cred(p_cle_id IN NUMBER,
1524 x_scrv_tbl OUT NOCOPY OKS_SALES_CREDIT_PUB.scrv_tbl_type) IS
1525 cursor scrv_cur is
1526 select
1527 percent,
1528 chr_id,
1529 ctc_id,
1530 sales_credit_type_id1,
1531 sales_credit_type_id2
1532 from OKS_K_SALES_CREDITS
1533 where cle_id = p_cle_id;
1534 i NUMBER := 1;
1535 begin
1536 for scrv_rec in scrv_cur
1537 loop
1538 x_scrv_tbl(i).id := OKC_API.G_MISS_NUM;
1539 x_scrv_tbl(i).percent := scrv_rec.percent;
1540 x_scrv_tbl(i).chr_id := scrv_rec.chr_id;
1541 x_scrv_tbl(i).ctc_id := scrv_rec.ctc_id;
1542 x_scrv_tbl(i).sales_credit_type_id1 := scrv_rec.sales_credit_type_id1;
1543 x_scrv_tbl(i).sales_credit_type_id2 := scrv_rec.sales_credit_type_id2;
1544 x_scrv_tbl(i).object_version_number := OKC_API.G_MISS_NUM;
1545 x_scrv_tbl(i).created_by := OKC_API.G_MISS_NUM;
1546 x_scrv_tbl(i).creation_date := OKC_API.G_MISS_DATE;
1547 x_scrv_tbl(i).last_updated_by := OKC_API.G_MISS_NUM;
1548 x_scrv_tbl(i).last_update_date := OKC_API.G_MISS_DATE;
1549 i := i + 1;
1550 end loop;
1551 end get_sales_cred;
1552
1553 procedure create_sales_cred(p_cle_id IN NUMBER,
1554 p_scrv_tbl IN OUT NOCOPY OKS_SALES_CREDIT_PUB.scrv_tbl_type,
1555 x_status OUT NOCOPY VARCHAR2) IS
1556 l_api_version NUMBER := 1.0;
1557 l_msg_count NUMBER;
1558 l_msg_data VARCHAR2(2000);
1559 l_scrv_tbl OKS_SALES_CREDIT_PUB.scrv_tbl_type;
1560 i NUMBER;
1561 begin
1562 i := p_scrv_tbl.FIRST;
1563 Loop
1564 p_scrv_tbl(i).cle_id := p_cle_id;
1565 exit when i = p_scrv_tbl.LAST;
1566 i := p_scrv_tbl.NEXT(i);
1567 End Loop;
1568 OKS_SALES_CREDIT_PUB.insert_Sales_credit(
1569 p_api_version => l_api_version,
1570 x_return_status => x_status,
1571 x_msg_count => l_msg_count,
1572 x_msg_data => l_msg_data,
1573 p_scrv_tbl => p_scrv_tbl,
1574 x_scrv_tbl => l_scrv_tbl);
1575 end create_sales_cred;
1576
1577 procedure update_line_item(p_cle_id IN NUMBER,
1578 p_item_id IN VARCHAR2,
1579 x_status OUT NOCOPY VARCHAR2) IS
1580 l_api_version NUMBER := 1.0;
1581 l_msg_count NUMBER;
1582 l_msg_data VARCHAR2(2000);
1583 l_cimv_rec_in OKC_CONTRACT_ITEM_PUB.cimv_rec_type;
1584 l_cimv_rec_out OKC_CONTRACT_ITEM_PUB.cimv_rec_type;
1585 cursor item_cur is
1586 select id
1587 from okc_k_items_v
1588 where cle_id = p_cle_id;
1589 begin
1590 open item_cur;
1591 fetch item_cur into l_cimv_rec_in.id;
1592 close item_cur;
1593 l_cimv_rec_in.object1_id1 := p_item_id;
1594 OKC_CONTRACT_ITEM_PUB.update_contract_item(
1595 p_api_version => l_api_version,
1596 x_return_status => x_status,
1597 x_msg_count => l_msg_count,
1598 x_msg_data => l_msg_data,
1599 p_cimv_rec => l_cimv_rec_in,
1600 x_cimv_rec => l_cimv_rec_out);
1601 end update_line_item;
1602
1603 procedure prorate_amount(p_cle_id IN NUMBER,
1604 p_percent IN NUMBER,
1605 p_amount IN NUMBER,
1606 x_status OUT NOCOPY VARCHAR2) IS
1607 cursor subline_count is
1608 select count(*)
1609 from okc_k_lines_b
1610 where cle_id = p_cle_id
1611 and lse_id in (7,8,9,10,11,35, 18,25);
1612
1613 cursor subline_cur is
1614 select id, price_negotiated
1615 from okc_k_lines_b
1616 where cle_id = p_cle_id
1617 and lse_id in (7,8,9,10,11,35, 18,25);
1618
1619 l_total_amt NUMBER := 0;
1620 l_count NUMBER;
1621 i NUMBER;
1622 l_api_version NUMBER := 1.0;
1623 l_msg_count NUMBER;
1624 l_msg_data VARCHAR2(2000);
1625 l_clev_tbl_in OKC_CONTRACT_PUB.clev_tbl_type;
1626 l_clev_tbl_out OKC_CONTRACT_PUB.clev_tbl_type;
1627 begin
1628 Open subline_count;
1629 Fetch subline_count into l_count;
1630 Close subline_count;
1631 If l_count = 0 Then
1632 x_status := OKC_API.G_RET_STS_SUCCESS;
1633 return;
1634 End If;
1635 i := 1;
1636 For subline in subline_cur
1637 Loop
1638 l_clev_tbl_in(i).id := subline.id;
1639 If i <> l_count Then
1640 l_clev_tbl_in(i).price_negotiated := subline.price_negotiated * p_percent / 100.0;
1641 Else
1642 l_clev_tbl_in(i).price_negotiated := p_amount - l_total_amt;
1643 End If;
1644
1645 l_total_amt := l_total_amt + NVL(l_clev_tbl_in(i).price_negotiated,0);
1646 i := i + 1;
1647 End Loop;
1648 OKC_CONTRACT_PUB.update_contract_line(
1649 p_api_version => l_api_version,
1650 x_return_status => x_status,
1651 x_msg_count => l_msg_count,
1652 x_msg_data => l_msg_data,
1653 p_clev_tbl => l_clev_tbl_in,
1654 x_clev_tbl => l_clev_tbl_out);
1655 end prorate_amount;
1656
1657
1658
1659 procedure refresh_bill_sch(p_cle_id IN NUMBER,
1660 x_rgp_id OUT NOCOPY NUMBER,
1661 x_status OUT NOCOPY VARCHAR2) IS
1662
1663 cursor l_line_csr is
1664 select id, dnz_chr_id, inv_rule_id
1665 from okc_k_lines_b
1666 where id = p_cle_id;
1667
1668 cursor l_sll_csr IS
1669 SELECT *
1670 FROM oks_stream_levels_b
1671 WHERE cle_id = p_cle_id;
1672
1673 CURSOR l_bill_type_csr IS
1674 SELECT nvl(billing_schedule_type,'T') billing_schedule_type
1675 FROM oks_k_lines_b
1676 WHERE cle_id = p_cle_id;
1677
1678
1679 l_sll_tbl OKS_BILL_SCH.StreamLvl_tbl;
1680 l_bill_sch_out_tbl OKS_BILL_SCH.ItemBillSch_tbl;
1681 l_bill_type_rec l_bill_type_csr%ROWTYPE;
1682
1683 l_line_rec l_line_csr%ROWTYPE;
1684 l_sll_rec l_sll_csr%ROWTYPE;
1685 l_index NUMBER;
1686
1687 BEGIN
1688
1689
1690 x_status := 'S';
1691
1692 ---get line details
1693 Open l_Line_Csr;
1694 Fetch l_Line_Csr Into l_Line_Rec;
1695
1696 If l_Line_Csr%Notfound then
1697 Close l_Line_Csr;
1698 x_status := 'E';
1699 OKC_API.set_message(G_PKG_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'LINE NOT FOUND');
1700 RAISE G_EXCEPTION_HALT_VALIDATION;
1701 End If;
1702 Close l_Line_Csr;
1703
1704 ---get bill type details
1705 Open l_bill_type_Csr;
1706 Fetch l_bill_type_Csr Into l_bill_type_Rec;
1707
1708 If l_bill_type_csr%Notfound then
1709 NULL;
1710 End If;
1711 Close l_bill_type_Csr;
1712
1713
1714 l_sll_tbl.DELETE;
1715 l_index := 1;
1716 ----make sll tbl
1717
1718 FOR l_SlL_rec IN l_SlL_Csr
1719 LOOP
1720 l_sll_tbl(l_index).id := l_SlL_rec.id;
1721 l_sll_tbl(l_index).cle_id := l_SlL_rec.cle_id;
1722 l_sll_tbl(l_index).chr_id := l_SlL_rec.chr_id;
1723 l_sll_tbl(l_index).dnz_chr_id := l_SlL_rec.dnz_chr_id;
1724 l_sll_tbl(l_index).uom_code := l_SlL_rec.uom_code;
1725 l_sll_tbl(l_index).sequence_no := l_SlL_rec.sequence_no;
1726 l_sll_tbl(l_index).Start_Date := l_SlL_rec.Start_Date;
1727 l_sll_tbl(l_index).end_Date := l_SlL_rec.end_Date;
1728 l_sll_tbl(l_index).level_periods := l_SlL_rec.level_periods;
1729 l_sll_tbl(l_index).uom_per_period := l_SlL_rec.uom_per_period;
1730 l_sll_tbl(l_index).level_amount := l_SlL_rec.level_amount;
1731 l_sll_tbl(l_index).invoice_offset_days := l_SlL_rec.invoice_offset_days;
1732 l_sll_tbl(l_index).interface_offset_days := l_SlL_rec.interface_offset_days;
1733
1734 l_index := l_index + 1;
1735 END LOOP;
1736
1737 IF l_sll_tbl.COUNT = 0 THEN
1738 RETURN;
1739 END IF;
1740
1741
1742 OKS_BILL_SCH.Create_Bill_Sch_Rules
1743 (p_billing_type => nvl(l_bill_type_rec.billing_schedule_type,'T'),
1744 p_sll_tbl => l_sll_tbl,
1745 p_invoice_rule_id => l_line_rec.inv_rule_id,
1746 x_bil_sch_out_tbl => l_bill_sch_out_tbl,
1747 x_return_status => x_status);
1748
1749
1750 End refresh_bill_sch;
1751
1752
1753 procedure create_USV_rule(p_rgp_id IN NUMBER,
1754 p_info1 IN VARCHAR2,
1755 p_info2 IN VARCHAR2,
1756 p_info3 IN NUMBER,
1757 x_status OUT NOCOPY VARCHAR2) is
1758 cursor rule_cur is
1759 select id
1760 from okc_rules_b
1761 where rgp_id = p_rgp_id
1762 and rule_information_category = 'USV';
1763 l_rule_id NUMBER;
1764 l_rulv_tbl_in okc_rule_pub.rulv_tbl_type;
1765 l_rulv_tbl_out okc_rule_pub.rulv_tbl_type;
1766 l_create BOOLEAN := TRUE;
1767 l_api_version NUMBER := 1.0;
1768 l_msg_count NUMBER;
1769 l_msg_data VARCHAR2(2000);
1770 begin
1771 l_rulv_tbl_in.DELETE;
1772 open rule_cur;
1773 fetch rule_cur into l_rule_id;
1774 If rule_cur%FOUND Then
1775 l_create := FALSE;
1776 End If;
1777 close rule_cur;
1778 If l_create Then
1779 l_rulv_tbl_in(1).rgp_id := p_rgp_id;
1780 l_rulv_tbl_in(1).sfwt_flag := 'N';
1781 l_rulv_tbl_in(1).std_template_yn := 'N';
1782 l_rulv_tbl_in(1).warn_yn := 'N';
1783 l_rulv_tbl_in(1).rule_information_category := 'USV';
1784 l_rulv_tbl_in(1).rule_information1 := p_info1;
1785 l_rulv_tbl_in(1).rule_information2 := p_info2;
1786 l_rulv_tbl_in(1).rule_information3 := p_info3;
1787 l_rulv_tbl_in(1).dnz_chr_id := g_chr_id;
1788 l_rulv_tbl_in(1).object_version_number := OKC_API.G_MISS_NUM;
1789 l_rulv_tbl_in(1).created_by := OKC_API.G_MISS_NUM;
1790 l_rulv_tbl_in(1).creation_date := SYSDATE;
1791 l_rulv_tbl_in(1).last_updated_by := OKC_API.G_MISS_NUM;
1792 l_rulv_tbl_in(1).last_update_date := SYSDATE;
1793 OKC_RULE_PUB.create_rule(
1794 p_api_version => l_api_version,
1795 x_return_status => x_status,
1796 x_msg_count => l_msg_count,
1797 x_msg_data => l_msg_data,
1798 p_rulv_tbl => l_rulv_tbl_in,
1799 x_rulv_tbl => l_rulv_tbl_out);
1800 Else
1801 l_rulv_tbl_in(1).id := l_rule_id;
1802 l_rulv_tbl_in(1).rgp_id := OKC_API.G_MISS_NUM;
1803 l_rulv_tbl_in(1).sfwt_flag := OKC_API.G_MISS_CHAR;
1804 l_rulv_tbl_in(1).std_template_yn := OKC_API.G_MISS_CHAR;
1805 l_rulv_tbl_in(1).warn_yn := OKC_API.G_MISS_CHAR;
1806 l_rulv_tbl_in(1).rule_information_category := OKC_API.G_MISS_CHAR;
1807 l_rulv_tbl_in(1).rule_information1 := p_info1;
1808 l_rulv_tbl_in(1).rule_information2 := p_info2;
1809 l_rulv_tbl_in(1).rule_information3 := p_info3;
1810 l_rulv_tbl_in(1).dnz_chr_id := g_chr_id;
1811 l_rulv_tbl_in(1).object_version_number := OKC_API.G_MISS_NUM;
1812 l_rulv_tbl_in(1).created_by := OKC_API.G_MISS_NUM;
1813 l_rulv_tbl_in(1).creation_date := OKC_API.G_MISS_DATE;
1814 l_rulv_tbl_in(1).last_updated_by := OKC_API.G_MISS_NUM;
1815 l_rulv_tbl_in(1).last_update_date := OKC_API.G_MISS_DATE;
1816 OKC_RULE_PUB.update_rule(
1817 p_api_version => l_api_version,
1818 x_return_status => x_status,
1819 x_msg_count => l_msg_count,
1820 x_msg_data => l_msg_data,
1821 p_rulv_tbl => l_rulv_tbl_in,
1822 x_rulv_tbl => l_rulv_tbl_out);
1823 End If;
1824 end create_USV_rule;
1825
1826 procedure update_header_amount(p_cle_id IN NUMBER,
1827 x_status OUT NOCOPY VARCHAR2) IS
1828 l_api_version CONSTANT NUMBER := 1.0;
1829 l_init_msg_list VARCHAR2(2000) := OKC_API.G_FALSE;
1830 l_return_status VARCHAR2(1);
1831 l_msg_count NUMBER;
1832 l_msg_data VARCHAR2(2000);
1833 l_msg_index_out NUMBER;
1834 l_chrv_tbl_in okc_contract_pub.chrv_tbl_type;
1835 l_chrv_tbl_out okc_contract_pub.chrv_tbl_type;
1836
1837 cursor total_amount(p_chr_id IN NUMBER) IS
1838 select sum(price_negotiated) sum
1839 from okc_k_lines_b
1840 where dnz_chr_id = p_chr_id
1841 and lse_id in (7,8,9,10,11,35,13,18,25);
1842 Begin
1843 x_status := OKC_API.G_RET_STS_SUCCESS;
1844 If p_cle_id IS NOT NULL Then
1845 For cur_total_amount IN total_amount(g_chr_id)
1846 loop
1847 l_chrv_tbl_in(1).id := g_chr_id;
1848 l_chrv_tbl_in(1).estimated_amount := cur_total_amount.sum;
1849 okc_contract_pub.update_contract_header (
1850 p_api_version => l_api_version,
1851 p_init_msg_list => l_init_msg_list,
1852 x_return_status => l_return_status,
1853 x_msg_count => l_msg_count,
1854 x_msg_data => l_msg_data,
1855 p_chrv_tbl => l_chrv_tbl_in,
1856 x_chrv_tbl => l_chrv_tbl_out );
1857 x_status := l_return_status;
1858 end loop;
1859 End If;
1860 End update_header_amount;
1861
1862 PROCEDURE copy_service( p_api_version IN NUMBER,
1863 p_init_msg_list IN VARCHAR2,
1864 p_source_rec IN copy_source_rec,
1865 p_target_tbl IN OUT NOCOPY copy_target_tbl,
1866 x_return_status OUT NOCOPY VARCHAR2,
1867 x_msg_count OUT NOCOPY NUMBER,
1868 x_msg_data OUT NOCOPY VARCHAR2
1869 ) IS
1870
1871 cursor header_cur IS
1872 select dnz_chr_id
1873 from okc_k_lines_b
1874 where id = p_source_rec.cle_id;
1875
1876 idx NUMBER;
1877 l_rgp_id NUMBER;
1878 l_return_status VARCHAR2(20);
1879 l_rev_tbl OKS_REV_DISTR_PUB.rdsv_tbl_type;
1880 l_salescr_tbl OKS_SALES_CREDIT_PUB.scrv_tbl_type;
1881 l_msg_index NUMBER;
1882 l_msg_data VARCHAR2(2000);
1883 l_total_pct NUMBER := 0;
1884 l_rev_found BOOLEAN := FALSE;
1885 l_scr_found BOOLEAN := FALSE;
1886 l_top_line_number NUMBER := 0;
1887 cursor rule_group_cur(p_cle_id IN NUMBER) is
1888 select id
1889 from okc_rule_groups_b
1890 where cle_id = p_cle_id;
1891 G_ERROR EXCEPTION;
1892 Begin
1893 If p_target_tbl.COUNT = 0 Then
1894 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1895 return;
1896 End If;
1897 Open header_cur;
1898 Fetch header_cur into g_chr_id;
1899 Close header_cur;
1900 --errorout('g_chr_id '||g_chr_id);
1901 -- First copy the source line to create all target lines.
1902 idx := p_target_tbl.FIRST;
1903 -- The original source line will become the first target line.(so, don't copy)
1904 p_target_tbl(idx).cle_id := p_source_rec.cle_id;
1905 -- If there are more target lines then make copies of the source line for each
1906 -- and update the p_target_tbl with the new line id.
1907 If idx <> p_target_tbl.LAST Then
1908 --errorout('MORE THAN ONE RECORD PASSED');
1909 idx := p_target_tbl.NEXT(idx);
1910 get_rev_distr(p_source_rec.cle_id, l_rev_tbl);
1911 if l_rev_tbl.COUNT > 0 then
1912 l_rev_found := TRUE;
1913 --errorout('Revenue Distrib. Found');
1914 end if;
1915 get_sales_cred(p_source_rec.cle_id, l_salescr_tbl);
1916 if l_salescr_tbl.COUNT > 0 then
1917 l_scr_found := TRUE;
1918 --errorout('Sales Cred. Found');
1919 end if;
1920
1921 --Fix for bug#2221910 start. Get Max Of Top Lines.
1922
1923 Select nvl(max(to_number(line_number)),0)
1924 INTO l_top_line_number
1925 FROM OKC_K_LINES_B
1926 WHERE dnz_chr_id = g_chr_id
1927 and cle_id is null;
1928 --Fix for bug#2221910 end.
1929
1930 LOOP
1931 OKC_COPY_CONTRACT_PUB.copy_contract_lines(
1932 p_api_version => p_api_version,
1933 p_init_msg_list => p_init_msg_list,
1934 x_return_status => l_return_status,
1935 x_msg_count => x_msg_count,
1936 x_msg_data => x_msg_data,
1937 p_from_cle_id => p_source_rec.cle_id,
1938 p_to_chr_id => g_chr_id,
1939 x_cle_id => p_target_tbl(idx).cle_id);
1940 --errorout('Copy line status: '||x_return_status);
1941 If l_return_status <> OKC_API.G_RET_STS_SUCCESS Then
1942 x_return_status := l_return_status;
1943 Raise G_ERROR;
1944 End If;
1945
1946 --Fix for bug#2221910 start. Update Top Line Sequence number.
1947 l_top_line_number := l_top_line_number + 1;
1948 Update okc_k_lines_b Set line_number = l_top_line_number
1949 Where id = p_target_tbl(idx).cle_id;
1950 --Fix for bug#2221910 end.
1951
1952 OKS_SETUP_UTIL_PUB.update_line_numbers(p_chr_id => g_chr_id,
1953 p_cle_id => p_target_tbl(idx).cle_id,
1954 x_return_status => l_return_status);
1955 If l_return_status <> OKC_API.G_RET_STS_SUCCESS Then
1956 x_return_status := l_return_status;
1957 Raise G_ERROR;
1958 End If;
1959 If l_rev_found Then
1960 create_rev_distr(p_target_tbl(idx).cle_id, l_rev_tbl, l_return_status);
1961 --errorout('Create_rev_distr status: '||l_return_status);
1962 If l_return_status <> OKC_API.G_RET_STS_SUCCESS Then
1963 x_return_status := l_return_status;
1964 Raise G_ERROR;
1965 End If;
1966 End If;
1967 If l_scr_found Then
1968 create_sales_cred(p_target_tbl(idx).cle_id, l_salescr_tbl, l_return_status);
1969 --errorout('create_sales_cred status: '||l_return_status);
1970 If l_return_status <> OKC_API.G_RET_STS_SUCCESS Then
1971 x_return_status := l_return_status;
1972 Raise G_ERROR;
1973 End If;
1974 End If;
1975 EXIT When idx = p_target_tbl.LAST;
1976 idx := p_target_tbl.NEXT(idx);
1977 END LOOP;
1978 End If;
1979 -- Now update each target line with the new item id, amount et cetera.
1980 idx := p_target_tbl.FIRST;
1981 LOOP
1982 --errorout('=====');
1983 --errorout('Target Table('||idx||').cle_id : '||p_target_tbl(idx).cle_id);
1984 --errorout('Target Table('||idx||').item_id: '||p_target_tbl(idx).item_id);
1985 --errorout('Target Table('||idx||').amount : '||p_target_tbl(idx).amount);
1986 --errorout('Target Table('||idx||').percent: '||p_target_tbl(idx).percentage);
1987 --errorout('=====');
1988 update_line_item(p_target_tbl(idx).cle_id, p_target_tbl(idx).item_id, l_return_status);
1989 --errorout('update_line_item status: '||l_return_status);
1990 If l_return_status <> OKC_API.G_RET_STS_SUCCESS Then
1991 x_return_status := l_return_status;
1992 Raise G_ERROR;
1993 End If;
1994 If p_target_tbl(idx).percentage <> 100 Then
1995 prorate_amount(p_target_tbl(idx).cle_id, p_target_tbl(idx).percentage, p_target_tbl(idx).amount, l_return_status);
1996 --errorout('prorate_amount status: '||l_return_status);
1997 If l_return_status <> OKC_API.G_RET_STS_SUCCESS Then
1998 x_return_status := l_return_status;
1999 Raise G_ERROR;
2000 End If;
2001 refresh_bill_sch(p_target_tbl(idx).cle_id, l_rgp_id, l_return_status);
2002 --errorout('refresh_bill_sch status: '||l_return_status);
2003 --errorout('rgp_id: '||l_rgp_id);
2004 If l_return_status <> OKC_API.G_RET_STS_SUCCESS Then
2005 x_return_status := l_return_status;
2006 Raise G_ERROR;
2007 End If;
2008 Else
2009 open rule_group_cur(p_target_tbl(idx).cle_id);
2010 fetch rule_group_cur into l_rgp_id;
2011 close rule_group_cur;
2012 --errorout('rgp_id:(1) '||l_rgp_id);
2013 End If;
2014 create_USV_rule(l_rgp_id,
2015 p_source_rec.item_id,
2016 p_target_tbl(idx).item_id,
2017 p_source_rec.cle_id,
2018 l_return_status);
2019 --errorout('create_USV_rule status: '||l_return_status);
2020 If l_return_status <> OKC_API.G_RET_STS_SUCCESS Then
2021 x_return_status := l_return_status;
2022 Raise G_ERROR;
2023 End If;
2024 l_total_pct := l_total_pct + p_target_tbl(idx).percentage;
2025 EXIT When idx = p_target_tbl.LAST;
2026 idx := p_target_tbl.NEXT(idx);
2027 END LOOP;
2028 If l_total_pct <> 100 Then
2029 null;
2030 update_header_amount(p_source_rec.cle_id, l_return_status);
2031 --errorout('update_header_amount status: '||l_return_status);
2032 If l_return_status <> OKC_API.G_RET_STS_SUCCESS Then
2033 x_return_status := l_return_status;
2034 Raise G_ERROR;
2035 End If;
2036 End If;
2037 x_return_status := OKC_API.G_RET_STS_SUCCESS;
2038 --errorout('SUCCESS');
2039 Exception
2040 When G_ERROR Then
2041 /*
2042 FOR i in 1..fnd_msg_pub.count_msg
2043 Loop
2044 fnd_msg_pub.get(p_msg_index => i,
2045 p_encoded => 'F',
2046 p_data => l_msg_data,
2047 p_msg_index_out => l_msg_index );
2048 ErrorOut ('SCRIPT ERROR ' || l_msg_data );
2049 End Loop;
2050 */
2051 Null;
2052 When Others Then
2053 OKC_API.set_message(OKC_API.G_APP_NAME,
2054 'OKS_UNEXP_ERROR',
2055 'SQLcode',
2056 SQLCODE,
2057 'SQLerrm',
2058 SQLERRM);
2059 -- notify caller of an UNEXPECTED error
2060 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
2061 End copy_service;
2062
2063 /** End of Code for changing/splitting service lines **/
2064
2065
2066 /****FOR USAGE BILLING*********/
2067 Procedure Calculate_Bill_Amount (
2068 p_api_version IN NUMBER,
2069 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2070 p_bill_tbl IN OUT NOCOPY Bill_tbl_type,
2071 x_return_status OUT NOCOPY VARCHAR2,
2072 x_msg_count OUT NOCOPY NUMBER,
2073 x_msg_data OUT NOCOPY VARCHAR2)
2074
2075 IS
2076
2077 Cursor l_ctr_csr(p_counter_id Number) Is
2078 SELECT ccr.net_reading last_reading
2079 FROM Cs_ctr_counter_values_v ccr
2080 WHERE ccr.counter_id = p_counter_id
2081 ORDER BY value_timestamp desc;
2082
2083 Cursor l_init_csr(p_counter_id Number) Is
2084 SELECT ccr.initial_reading last_reading
2085 FROM cs_counters ccr
2086 WHERE ccr.counter_id = p_counter_id;
2087
2088 Cursor l_usage_line_csr(p_counter_id Varchar2) Is
2089 Select itm.uom_code, line.cle_id usage_id
2090 From Okc_K_items Itm, okc_k_lines_b line
2091 Where itm.cle_id = line.id
2092 And itm.object1_id1 = p_counter_id
2093 And itm.jtot_object1_code = 'OKX_COUNTER';
2094
2095
2096
2097 l_usage_line_rec l_usage_line_csr%ROWTYPE;
2098 l_last_reading NUMBER;
2099 l_total_reading NUMBER := 0;
2100 l_lvl_reading NUMBER := 0;
2101 l_act_reading NUMBER := 0;
2102 v_index NUMBER;
2103
2104
2105 BEGIN
2106 x_return_status := OKC_API.G_RET_STS_SUCCESS;
2107
2108 --------errorout_ad('count = ' || p_bill_tbl.COUNT);
2109
2110 IF p_bill_tbl.COUNT <= 0 THEN
2111 x_return_status := OKC_API.G_RET_STS_ERROR;
2112 OKC_API.set_message(G_APP_NAME,G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'NO RECORDS PASSED');
2113 Raise G_EXCEPTION_HALT_VALIDATION;
2114 END IF;
2115
2116 ---FIND OUT LAST READING from the table and clculate net reading for each record
2117
2118 FOR v_index IN p_bill_tbl.FIRST .. p_bill_tbl.LAST
2119 LOOP
2120
2121 l_last_reading := 0 ;
2122 OPEN l_ctr_csr(p_bill_tbl(v_index).counter_id);
2123 FETCH l_ctr_csr INTO l_last_reading;
2124
2125 IF l_ctr_csr%NOTFOUND THEN
2126 OPEN l_init_csr(p_bill_tbl(v_index).counter_id);
2127 FETCH l_init_csr INTO l_last_reading;
2128 Close l_init_csr;
2129 END IF;
2130
2131 CLOSE l_ctr_csr;
2132
2133 p_bill_tbl(v_index).net_reading := nvl(p_bill_tbl(v_index).meter_reading,0) - nvl(l_last_reading,0) ;
2134
2135 --------errorout_ad('net_reading for ' || v_index || 'record = ' || p_bill_tbl(v_index).net_reading);
2136
2137 l_total_reading := l_total_reading + nvl(p_bill_tbl(v_index).net_reading,0);
2138
2139 --------errorout_ad('l_total_reading = ' || l_total_reading);
2140 END LOOP;
2141
2142 ---level all readings.
2143
2144 l_lvl_reading := ROUND(nvl(l_total_reading,0)/(p_bill_tbl.count),0);
2145 l_act_reading := nvl(l_total_reading,0)/(p_bill_tbl.count);
2146
2147 --------errorout_ad('l_lvl_reading = ' || l_lvl_reading);
2148
2149 FOR v_index IN p_bill_tbl.FIRST .. p_bill_tbl.LAST
2150 LOOP
2151
2152 p_bill_tbl(v_index).level_reading := l_lvl_reading;
2153
2154 --------errorout_ad('passed');
2155
2156 OPEN l_usage_line_csr(TO_CHAR(p_bill_tbl(v_index).counter_id));
2157 FETCH l_usage_line_csr INTO l_usage_line_rec;
2158
2159 IF l_usage_line_csr%NOTFOUND THEN
2160 CLOSE l_usage_line_csr;
2161 x_return_status := OKC_API.G_RET_STS_ERROR;
2162 OKC_API.set_message(G_APP_NAME,G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'USAGE LINE NOT FOUND FOR THE COUNTER');
2163 Raise G_EXCEPTION_HALT_VALIDATION;
2164 END IF;
2165 CLOSE l_usage_line_csr;
2166 --------errorout_ad('usage line id for ' || v_index || 'line = ' || l_usage_line_rec.usage_id);
2167 --------errorout_ad('uom code for ' || v_index || 'line = ' || l_usage_line_rec.uom_code);
2168
2169 IF p_bill_tbl(v_index).level_reading IS NOT NULL AND
2170 p_bill_tbl(v_index).level_reading > 0 THEN
2171
2172 l_line_rec.line_id := l_usage_line_rec.usage_id; ----usage line id;
2173 l_line_rec.intent := 'USG';
2174 l_line_rec.usage_qty := p_bill_tbl(v_index).level_reading ; --level reading
2175 l_line_rec.usage_uom_code := l_usage_line_rec.uom_code;
2176 l_line_rec.bsl_id := -99;
2177
2178
2179 /*Pricing API to calculate amount */
2180 OKS_QP_PKG.CALC_PRICE
2181 (
2182 P_DETAIL_REC => l_line_rec,
2183 X_PRICE_DETAILS => l_price_rec,
2184 X_MODIFIER_DETAILS => l_modifier_details,
2185 X_PRICE_BREAK_DETAILS => l_price_break_details,
2186 X_RETURN_STATUS => x_return_status,
2187 X_MSG_COUNT => x_msg_count,
2188 X_MSG_DATA => x_msg_data);
2189
2190
2191 IF x_return_status <> 'S' Then
2192 OKC_API.set_message(G_APP_NAME,'CALCULATE PRICE ERROR');
2193 Raise G_EXCEPTION_HALT_VALIDATION;
2194 End If;
2195
2196 /*****
2197 commented as part of bug 5068589
2198
2199 p_bill_tbl(v_index).bill_amount := l_price_rec.PROD_EXT_AMOUNT;
2200 p_bill_tbl(v_index).bill_amount :=
2201 nvl(l_price_rec.PROD_ADJ_UNIT_PRICE, l_price_rec.PROD_LIST_UNIT_PRICE ) * l_act_reading;
2202 *****/
2203
2204 p_bill_tbl(v_index).bill_amount :=
2205 nvl(l_price_rec.PROD_ADJ_UNIT_PRICE, l_price_rec.PROD_LIST_UNIT_PRICE ) * p_bill_tbl(v_index).net_reading;
2206
2207 --------errorout_ad('amount for ' || v_index || 'line = ' || p_bill_tbl(v_index).bill_amount );
2208 ELSE
2209 p_bill_tbl(v_index).bill_amount := 0;
2210 END IF;
2211
2212
2213
2214 END LOOP;
2215
2216 EXCEPTION
2217
2218 WHEN G_EXCEPTION_HALT_VALIDATION Then
2219 NULL;
2220 WHEN OTHERS THEN
2221 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
2222 OKC_API.set_message(G_APP_NAME, G_UNEXPECTED_ERROR,G_SQLCODE_TOKEN,SQLCODE,G_SQLERRM_TOKEN,SQLERRM);
2223
2224 END Calculate_Bill_Amount;
2225
2226 Function Get_Credit_Amount (p_api_version IN Varchar2,
2227 p_cp_line_id IN Number,
2228 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2229 x_return_status OUT NOCOPY VARCHAR2,
2230 x_msg_count OUT NOCOPY NUMBER,
2231 x_msg_data OUT NOCOPY VARCHAR2)
2232 RETURN NUMBER
2233 IS
2234
2235 CURSOR l_credit_amt_csr IS
2236 SELECT nvl(SUM(bsl.amount),0) tot_credit_amt
2237 FROM Oks_bill_cont_lines bcl, Oks_bill_sub_lines bsl
2238 WHERE bsl.Cle_id = p_cp_line_id
2239 AND bcl.id = bsl.bcl_id
2240 AND bcl.bill_action = 'TR';
2241
2242 l_tot_credit_amt NUMBER := 0;
2243
2244 BEGIN
2245
2246 OPEN l_credit_amt_csr;
2247 FETCH l_credit_amt_csr INTO l_tot_credit_amt;
2248
2249 IF l_credit_amt_csr%NOTFOUND THEN
2250 CLOSE l_credit_amt_csr;
2251 RETURN 0;
2252 END IF;
2253
2254 CLOSE l_credit_amt_csr;
2255
2256 RETURN l_tot_credit_amt;
2257
2258 EXCEPTION
2259
2260 WHEN OTHERS THEN
2261 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
2262 OKC_API.set_message(G_APP_NAME, G_UNEXPECTED_ERROR,G_SQLCODE_TOKEN,SQLCODE,G_SQLERRM_TOKEN,SQLERRM);
2263
2264 END Get_Credit_Amount;
2265
2266 /* ****** ------------------- Procedures for creating Billing report ------------------------ ****** */
2267 /* ************************************************************************************************* */
2268
2269
2270 PROCEDURE delete_duplicate_lines (p_lines_table IN OKS_BILL_REC_PUB.line_report_tbl_type
2271 ,x_lines_table OUT NOCOPY OKS_BILL_REC_PUB.line_report_tbl_type
2272 ,x_return_status OUT NOCOPY Varchar2
2273 ) IS
2274
2275 l_lines_rec_tmp OKS_BILL_REC_PUB.line_report_rec_type;
2276 l_lines_tbl_tmp OKS_BILL_REC_PUB.line_report_tbl_type DEFAULT p_lines_table ;
2277
2278 l_tbl_idx Binary_integer;
2279 l_line_id1 Varchar2(100) ;
2280 l_line_id2 Varchar2(100);
2281
2282 Begin
2283 x_return_status := OKC_API.G_RET_STS_SUCCESS;
2284 l_tbl_idx := l_lines_tbl_tmp.FIRST;
2285
2286 While l_tbl_idx Is Not Null
2287 Loop
2288 l_line_id1 := l_lines_tbl_tmp(l_tbl_idx).Line_id ;
2289 l_line_id2 := l_lines_rec_tmp.Line_Id;
2290
2291 If l_line_id1 = l_line_id2 Then
2292 l_lines_tbl_tmp.DELETE(l_tbl_idx) ;
2293 Else
2294 l_lines_rec_tmp := l_lines_tbl_tmp(l_tbl_idx) ;
2295 End If;
2296
2297 l_tbl_idx := l_lines_tbl_tmp.NEXT(l_tbl_idx) ;
2298 End Loop;
2299
2300 x_lines_table := l_lines_tbl_tmp ;
2301 x_return_status := OKC_API.G_RET_STS_SUCCESS;
2302
2303 Exception When Others Then
2304
2305 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
2306 FND_FILE.PUT_LINE( FND_FILE.LOG, '*** Billing Report:: delete_duplicate_lines Error ' || sqlerrm);
2307
2308 OKC_API.SET_MESSAGE
2309 (P_App_Name => G_APP_NAME
2310 ,P_Msg_Name => G_UNEXPECTED_ERROR
2311 ,P_Token1 => G_SQLCODE_TOKEN
2312 ,P_Token1_Value => SQLCODE
2313 ,P_Token2 => G_SQLERRM_TOKEN
2314 ,P_Token2_Value => SQLERRM);
2315
2316
2317 End delete_duplicate_lines ;
2318
2319 /* *** --------------------------------------------- *** */
2320
2321 PROCEDURE delete_duplicate_sub_lines (p_sub_lines_table IN OKS_BILL_REC_PUB.line_report_tbl_type
2322 ,x_sub_lines_table OUT NOCOPY OKS_BILL_REC_PUB.line_report_tbl_type
2323 ,x_return_status OUT NOCOPY Varchar2
2324 ) IS
2325
2326 l_sub_lines_rec_tmp OKS_BILL_REC_PUB.line_report_rec_type;
2327 l_sub_lines_tbl_tmp OKS_BILL_REC_PUB.line_report_tbl_type DEFAULT p_sub_lines_table ;
2328
2329 l_tbl_idx Binary_integer;
2330 l_line_id1 Varchar2(100) ;
2331 l_line_id2 Varchar2(100);
2332
2333 Begin
2334 x_return_status := OKC_API.G_RET_STS_SUCCESS;
2335 l_tbl_idx := l_sub_lines_tbl_tmp.FIRST;
2336
2337 While l_tbl_idx Is Not Null
2338 Loop
2339 l_line_id1 := l_sub_lines_tbl_tmp(l_tbl_idx).Sub_Line_id ;
2340 l_line_id2 := l_sub_lines_rec_tmp.Sub_Line_Id;
2341
2342 If l_line_id1 = l_line_id2 Then
2343 l_sub_lines_tbl_tmp.DELETE(l_tbl_idx) ;
2344 Else
2345 l_sub_lines_rec_tmp := l_sub_lines_tbl_tmp(l_tbl_idx) ;
2346 End If;
2347
2348 l_tbl_idx := l_sub_lines_tbl_tmp.NEXT(l_tbl_idx) ;
2349 End Loop;
2350
2351 x_sub_lines_table := l_sub_lines_tbl_tmp ;
2352 x_return_status := OKC_API.G_RET_STS_SUCCESS;
2353
2354 Exception When Others Then
2355
2356 FND_FILE.PUT_LINE( FND_FILE.LOG, '*** Billing Report:: delete_duplicate_sub_lines Error ' || sqlerrm);
2357 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
2358
2359 OKC_API.SET_MESSAGE
2360 (P_App_Name => G_APP_NAME
2361 ,P_Msg_Name => G_UNEXPECTED_ERROR
2362 ,P_Token1 => G_SQLCODE_TOKEN
2363 ,P_Token1_Value => SQLCODE
2364 ,P_Token2 => G_SQLERRM_TOKEN
2365 ,P_Token2_Value => SQLERRM);
2366
2367 End delete_duplicate_sub_lines ;
2368
2369 /* *** --------------------------------------------- *** */
2370
2371
2372 PROCEDURE sort_lines_table (p_lines_table IN OKS_BILL_REC_PUB.line_report_tbl_type
2373 ,x_lines_table OUT NOCOPY OKS_BILL_REC_PUB.line_report_tbl_type
2374 ,x_return_status OUT NOCOPY Varchar2
2375 ) IS
2376
2377 l_lines_rec_tmp OKS_BILL_REC_PUB.line_report_rec_type;
2378 l_lines_tbl_tmp OKS_BILL_REC_PUB.line_report_tbl_type DEFAULT p_lines_table ;
2379
2380 l_in_tbl_idx Binary_integer;
2381 l_out_tbl_idx Binary_integer;
2382 l_line_id1 Varchar2(95) ;
2383 l_line_id2 Varchar2(95);
2384
2385 Begin
2386
2387 x_return_status := OKC_API.G_RET_STS_SUCCESS;
2388 l_out_tbl_idx := l_lines_tbl_tmp.FIRST;
2389
2390 While l_out_tbl_idx is Not Null
2391 Loop
2392 l_in_tbl_idx := l_out_tbl_idx ;
2393
2394 While l_in_tbl_idx Is Not Null
2395 Loop
2396 l_line_id1 := lpad(to_char(l_lines_tbl_tmp(l_out_tbl_idx).dnz_chr_id),40,'0')
2397 ||lpad(to_char(l_lines_tbl_tmp(l_out_tbl_idx).Line_Id),40,'0');
2398 l_line_id2 := lpad(to_char(l_lines_tbl_tmp(l_in_tbl_idx).dnz_chr_id),40,'0')
2399 ||lpad(to_char(l_lines_tbl_tmp(l_in_tbl_idx).Line_Id),40,'0');
2400
2401 IF l_line_id1 > l_line_id2 then
2402 l_lines_rec_tmp := l_lines_tbl_tmp(l_out_tbl_idx) ;
2403 l_lines_tbl_tmp(l_out_tbl_idx) := l_lines_tbl_tmp(l_in_tbl_idx);
2404 l_lines_tbl_tmp(l_in_tbl_idx) := l_lines_rec_tmp ;
2405 End If;
2406
2407 l_in_tbl_idx := l_lines_tbl_tmp.NEXT(l_in_tbl_idx) ;
2408 End Loop;
2409
2410 l_out_tbl_idx := l_lines_tbl_tmp.NEXT(l_out_tbl_idx) ;
2411
2412
2413 End Loop ;
2414
2415 x_lines_table := l_lines_tbl_tmp ;
2416 x_return_status := OKC_API.G_RET_STS_SUCCESS;
2417
2418 Exception When Others Then
2419 FND_FILE.PUT_LINE( FND_FILE.LOG, '*** Billing Report:: sort_lines_table Error ' || sqlerrm);
2420 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
2421 OKC_API.SET_MESSAGE
2422 (P_App_Name => G_APP_NAME
2423 ,P_Msg_Name => G_UNEXPECTED_ERROR
2424 ,P_Token1 => G_SQLCODE_TOKEN
2425 ,P_Token1_Value => SQLCODE
2426 ,P_Token2 => G_SQLERRM_TOKEN
2427 ,P_Token2_Value => SQLERRM);
2428
2429
2430 End sort_lines_table ;
2431
2432
2433 /* **** --------------------------------------- *** */
2434
2435
2436 PROCEDURE sort_sub_lines_table (p_sub_lines_table IN OKS_BILL_REC_PUB.line_report_tbl_type
2437 ,x_sub_lines_table OUT NOCOPY OKS_BILL_REC_PUB.line_report_tbl_type
2438 ,x_return_status OUT NOCOPY Varchar2
2439 ) IS
2440
2441 l_sub_lines_rec_tmp OKS_BILL_REC_PUB.line_report_rec_type;
2442 l_sub_lines_tbl_tmp OKS_BILL_REC_PUB.line_report_tbl_type DEFAULT p_sub_lines_table ;
2443
2444 l_in_tbl_idx Binary_integer;
2445 l_out_tbl_idx Binary_integer;
2446 l_sub_line_id1 Varchar2(135);
2447 l_sub_line_id2 Varchar2(135);
2448
2449 Begin
2450
2451 x_return_status := OKC_API.G_RET_STS_SUCCESS;
2452 l_out_tbl_idx := l_sub_lines_tbl_tmp.FIRST;
2453
2454 While l_out_tbl_idx is Not Null
2455 Loop
2456 l_in_tbl_idx := l_out_tbl_idx ;
2457
2458 While l_in_tbl_idx Is Not Null
2459 Loop
2460 l_sub_line_id1 := lpad(to_char(l_sub_lines_tbl_tmp(l_out_tbl_idx).dnz_chr_id),40,'0')
2461 ||lpad(to_char(l_sub_lines_tbl_tmp(l_out_tbl_idx).line_id),40,'0')
2462 ||lpad(to_char(l_sub_lines_tbl_tmp(l_out_tbl_idx).sub_line_id),40,'0');
2463 l_sub_line_id2 := lpad(to_char(l_sub_lines_tbl_tmp(l_in_tbl_idx).dnz_chr_id),40,'0')
2464 ||lpad(to_char(l_sub_lines_tbl_tmp(l_in_tbl_idx).line_id),40,'0')
2465 ||lpad(to_char(l_sub_lines_tbl_tmp(l_in_tbl_idx).sub_line_id),40,'0');
2466
2467 IF l_sub_line_id1 > l_sub_line_id2 then
2468 l_sub_lines_rec_tmp := l_sub_lines_tbl_tmp(l_out_tbl_idx) ;
2469 l_sub_lines_tbl_tmp(l_out_tbl_idx) := l_sub_lines_tbl_tmp(l_in_tbl_idx);
2470 l_sub_lines_tbl_tmp(l_in_tbl_idx) := l_sub_lines_rec_tmp ;
2471 End If;
2472
2473 l_in_tbl_idx := l_sub_lines_tbl_tmp.NEXT(l_in_tbl_idx) ;
2474 End Loop;
2475
2476 l_out_tbl_idx := l_sub_lines_tbl_tmp.NEXT(l_out_tbl_idx) ;
2477
2478
2479 End Loop ;
2480
2481 x_sub_lines_table := l_sub_lines_tbl_tmp ;
2482 x_return_status := OKC_API.G_RET_STS_SUCCESS;
2483
2484 Exception When Others Then
2485 FND_FILE.PUT_LINE( FND_FILE.LOG, '*** Billing Report:: sort_sub_lines_table Error ' || sqlerrm);
2486 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
2487 OKC_API.SET_MESSAGE
2488 (P_App_Name => G_APP_NAME
2489 ,P_Msg_Name => G_UNEXPECTED_ERROR
2490 ,P_Token1 => G_SQLCODE_TOKEN
2491 ,P_Token1_Value => SQLCODE
2492 ,P_Token2 => G_SQLERRM_TOKEN
2493 ,P_Token2_Value => SQLERRM);
2494
2495
2496 End sort_sub_lines_table ;
2497
2498 /* *** --------------------------------------------- *** */
2499
2500 PROCEDURE sub_lines_bill_status (p_sub_lines_table IN OKS_BILL_REC_PUB.line_report_tbl_type
2501 ,x_sub_lines_table OUT NOCOPY OKS_BILL_REC_PUB.line_report_tbl_type
2502 ,x_return_status OUT NOCOPY Varchar2
2503 ) IS
2504
2505 l_sub_lines_tbl_tmp OKS_BILL_REC_PUB.line_report_tbl_type DEFAULT p_sub_lines_table ;
2506
2507 l_tmp_tbl_idx Binary_integer;
2508 l_in_tbl_idx Binary_integer;
2509 l_line_id Number := 0;
2510
2511 Begin
2512
2513 x_return_status := OKC_API.G_RET_STS_SUCCESS;
2514 l_in_tbl_idx := p_sub_lines_table.FIRST ;
2515
2516 While l_in_tbl_idx is Not Null
2517 Loop
2518
2519 If p_sub_lines_table(l_in_tbl_idx).Billed_YN = 'N' then
2520
2521 l_line_id := p_sub_lines_table(l_in_tbl_idx).Line_Id ;
2522 l_tmp_tbl_idx := l_sub_lines_tbl_tmp.FIRST;
2523
2524 While l_tmp_tbl_idx is Not Null
2525 Loop
2526 If l_sub_lines_tbl_tmp(l_tmp_tbl_idx).Line_ID = l_line_id then
2527 l_sub_lines_tbl_tmp(l_tmp_tbl_idx).Billed_YN := 'N' ;
2528 End If;
2529
2530 l_tmp_tbl_idx := l_sub_lines_tbl_tmp.NEXT(l_tmp_tbl_idx) ;
2531 End Loop;
2532
2533 End If;
2534
2535 l_in_tbl_idx := p_sub_lines_table.NEXT(l_in_tbl_idx) ;
2536
2537 End Loop ;
2538
2539 x_sub_lines_table := l_sub_lines_tbl_tmp ;
2540 x_return_status := OKC_API.G_RET_STS_SUCCESS;
2541
2542 Exception When Others Then
2543 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
2544 FND_FILE.PUT_LINE( FND_FILE.LOG, '*** Billing Report:: sub_lines_bill_status Error ' || sqlerrm);
2545 OKC_API.SET_MESSAGE
2546 (P_App_Name => G_APP_NAME
2547 ,P_Msg_Name => G_UNEXPECTED_ERROR
2548 ,P_Token1 => G_SQLCODE_TOKEN
2549 ,P_Token1_Value => SQLCODE
2550 ,P_Token2 => G_SQLERRM_TOKEN
2551 ,P_Token2_Value => SQLERRM);
2552
2553
2554 End sub_lines_bill_status ;
2555
2556 /* *** --------------------------------------------- *** */
2557
2558
2559 Function Get_Billig_Profile (p_dnz_chr_id Number
2560 )Return Varchar2 IS
2561
2562 Cursor l_hdr_sbg_csr(p_dnz_chr_id Number) Is
2563 Select nvl(rul.rule_information13,'N')
2564 From okc_rules_b rul
2565 ,okc_rule_groups_b rgp
2566 Where rgp.dnz_chr_id = p_dnz_chr_id
2567 And rgp.id = rul.rgp_id
2568 And rul.rule_information_category = 'SBG';
2569
2570 l_summary_flag Varchar2(100) := Null;
2571 l_bill_profile Varchar2(100) := Null;
2572
2573 Begin
2574 l_summary_flag := Null;
2575 l_bill_profile := Null;
2576
2577 /* *** This is not required -- honor only FND_PROFILE.VALUE to get billing profile
2578
2579 Open l_hdr_sbg_csr(p_dnz_chr_id);
2580 Fetch l_hdr_sbg_csr into l_summary_flag ;
2581 Close l_hdr_sbg_csr ;
2582 If (l_summary_flag = 'Y') Then
2583 l_bill_profile := 'Y';
2584 Else
2585 l_summary_flag := Null;
2586 OKS_BILL_REC_PUB.Get_Bill_profile(p_dnz_chr_id, l_summary_flag);
2587 If (l_summary_flag = 'N') THEN
2588 l_bill_profile := 'N';
2589 Elsif (l_summary_flag = 'Y') THEN
2590 l_bill_profile := 'Y';
2591 Else
2592 l_summary_flag := Null;
2593 l_summary_flag := FND_PROFILE.VALUE('OKS_AR_TRANSACTIONS_SUBMIT_SUMMARY_YN');
2594 If (l_summary_flag = 'YES') Then
2595 l_bill_profile := 'Y';
2596 Else
2597 l_bill_profile := 'N';
2598 End If;
2599 End If;
2600 End if;
2601 *** */
2602
2603 l_summary_flag := FND_PROFILE.VALUE('OKS_AR_TRANSACTIONS_SUBMIT_SUMMARY_YN');
2604 If (l_summary_flag = 'YES') Then
2605 l_bill_profile := 'Y';
2606 Else
2607 l_bill_profile := 'N';
2608 End If;
2609
2610 Return (l_bill_profile);
2611
2612 Exception When Others then
2613 FND_FILE.PUT_LINE( FND_FILE.LOG, '*** Billing Report:: Get_Billig_Profile Error ' || sqlerrm);
2614 Return(OKC_API.G_RET_STS_UNEXP_ERROR );
2615 OKC_API.SET_MESSAGE
2616 (P_App_Name => G_APP_NAME
2617 ,P_Msg_Name => G_UNEXPECTED_ERROR
2618 ,P_Token1 => G_SQLCODE_TOKEN
2619 ,P_Token1_Value => SQLCODE
2620 ,P_Token2 => G_SQLERRM_TOKEN
2621 ,P_Token2_Value => SQLERRM);
2622
2623 End Get_Billig_Profile ;
2624
2625 /* *** --------------------------------------------- *** */
2626
2627 Procedure Set_Billing_Profile (
2628 p_lines_table IN OKS_BILL_REC_PUB.line_report_tbl_type
2629 ,p_sub_lines_table IN OKS_BILL_REC_PUB.line_report_tbl_type
2630 ,x_lines_table OUT NOCOPY OKS_BILL_REC_PUB.line_report_tbl_type
2631 ,x_sub_lines_table OUT NOCOPY OKS_BILL_REC_PUB.line_report_tbl_type
2632 ,x_return_status OUT NOCOPY Varchar2
2633 ) IS
2634
2635 l_lines_tbl_copy OKS_BILL_REC_PUB.line_report_tbl_type DEFAULT p_lines_table;
2636 l_sub_lines_tbl_copy OKS_BILL_REC_PUB.line_report_tbl_type DEFAULT p_sub_lines_table;
2637 l_lines_tbl_idx Binary_integer;
2638 l_slines_tbl_idx Binary_integer;
2639
2640 l_bill_profile Varchar2(100) := Null;
2641
2642 Begin
2643 x_return_status := OKC_API.G_RET_STS_SUCCESS;
2644
2645
2646 /* **** set the billing profile value for Summary billing **** */
2647
2648 l_lines_tbl_idx := l_lines_tbl_copy.FIRST; /* Main Line Table */
2649 While l_lines_tbl_idx Is Not Null
2650 Loop
2651 l_bill_profile := Null;
2652
2653 l_bill_profile := Get_Billig_Profile(l_lines_tbl_copy(l_lines_tbl_idx).Dnz_Chr_Id );
2654
2655 If l_bill_profile <> OKC_API.G_RET_STS_UNEXP_ERROR then
2656 If l_bill_profile = 'Y' then
2657 l_lines_tbl_copy(l_lines_tbl_idx).Summary_bill_YN := 'Y' ;
2658 Else
2659 l_lines_tbl_copy(l_lines_tbl_idx).Summary_bill_YN := 'N' ;
2660 End If;
2661 End If ;
2662 l_lines_tbl_idx := l_lines_tbl_copy.NEXT(l_lines_tbl_idx) ;
2663
2664 End Loop ;
2665
2666 l_slines_tbl_idx := l_sub_lines_tbl_copy.FIRST; /* Sub Lines Table */
2667 While l_slines_tbl_idx Is Not Null
2668 Loop
2669 l_bill_profile := Null;
2670
2671 l_bill_profile := Get_Billig_Profile(l_sub_lines_tbl_copy(l_slines_tbl_idx).Dnz_Chr_Id );
2672
2673 If l_bill_profile <> OKC_API.G_RET_STS_UNEXP_ERROR then
2674 If l_bill_profile = 'Y' then
2675 l_sub_lines_tbl_copy(l_slines_tbl_idx).Summary_bill_YN := 'Y' ;
2676 Else
2677 l_sub_lines_tbl_copy(l_slines_tbl_idx).Summary_bill_YN := 'N' ;
2678 End If;
2679 End If ;
2680 l_slines_tbl_idx := l_sub_lines_tbl_copy.NEXT(l_slines_tbl_idx) ;
2681
2682 End Loop ;
2683
2684
2685 x_lines_table := l_lines_tbl_copy ;
2686 x_sub_lines_table := l_sub_lines_tbl_copy;
2687
2688
2689 Exception When Others Then
2690 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR ;
2691 FND_FILE.PUT_LINE( FND_FILE.LOG, '*** Billing Report:: Set_Billing_Profile Error ' || sqlerrm);
2692 OKC_API.SET_MESSAGE
2693 (P_App_Name => G_APP_NAME
2694 ,P_Msg_Name => G_UNEXPECTED_ERROR
2695 ,P_Token1 => G_SQLCODE_TOKEN
2696 ,P_Token1_Value => SQLCODE
2697 ,P_Token2 => G_SQLERRM_TOKEN
2698 ,P_Token2_Value => SQLERRM);
2699
2700 End Set_Billing_Profile ;
2701
2702
2703 /* *** --------------------------------------------- *** */
2704
2705 Function Sub_line_Name (p_jtot_object1_code IN Varchar2
2706 ,p_object1_id1 IN Number
2707 ,p_object1_id2 IN Varchar2
2708 ) Return Varchar2 IS
2709
2710 Cursor l_party(p_object1_id1 Number, p_object1_id2 Varchar2) IS
2711 Select name
2712 From OKX_PARTIES_V
2713 Where id1 = p_object1_id1
2714 And id2 = p_object1_id2 ;
2715
2716 Cursor l_cust_acct(p_object1_id1 Number, p_object1_id2 Varchar2) IS
2717 Select name
2718 From OKX_CUSTOMER_ACCOUNTS_V
2719 Where id1 = p_object1_id1
2720 And id2 = p_object1_id2 ;
2721
2722 --start bug#4928081 mchoudha replaced this cursor with the following
2723 /* Cursor l_cust_prod(p_object1_id1 Number, p_object1_id2 Varchar2) IS
2724 Select name
2725 From OKX_CUSTOMER_PRODUCTS_V
2726 Where id1 = p_object1_id1
2727 and id2 = p_object1_id2
2728 and organization_id = SYS_CONTEXT('OKC_CONTEXT','ORGANIZATION_ID') ;*/
2729
2730 Cursor l_cust_prod(p_object1_id1 Number, p_object1_id2 Varchar2) IS
2731 Select SIT.description
2732 FROM CSI_ITEM_INSTANCES cp,
2733 MTL_SYSTEM_ITEMS_TL SIT
2734 WHERE cp.instance_ID=p_object1_id1
2735 and SIT.inventory_item_id = cp.inventory_item_id
2736 and SIT.LANGUAGE = userenv('LANG')
2737 and SIT.organization_id = SYS_CONTEXT('OKC_CONTEXT','ORGANIZATION_ID');
2738
2739 --End bug#4928081
2740
2741 Cursor l_item(p_object1_id1 Number, p_object1_id2 Varchar2) IS
2742 Select name
2743 From OKX_SYSTEM_ITEMS_V
2744 Where id1 = p_object1_id1
2745 and id2 = p_object1_id2
2746 and organization_id = SYS_CONTEXT('OKC_CONTEXT','ORGANIZATION_ID')
2747 and serviceable_product_flag='Y' ;
2748
2749 Cursor l_site(p_object1_id1 Number, p_object1_id2 Varchar2) IS
2750 Select name
2751 From OKX_CUST_SITE_USES_V
2752 Where id1 = p_object1_id1
2753 and id2 = p_object1_id2
2754 and NVL(ORG_ID, -99) = SYS_CONTEXT('OKC_CONTEXT','ORG_ID') ;
2755
2756 Cursor l_system(p_object1_id1 Number, p_object1_id2 Varchar2) IS
2757 Select name
2758 From OKX_SYSTEMS_V
2759 Where id1 = p_object1_id1
2760 and id2 = p_object1_id2
2761 and NVL(ORG_ID, -99) = SYS_CONTEXT('OKC_CONTEXT','ORG_ID') ;
2762
2763
2764 l_name Varchar2(300) := Null;
2765
2766 Begin
2767
2768 l_name := Null;
2769
2770 If p_jtot_object1_code = 'OKX_PARTY' then
2771 Open l_party(p_object1_id1,p_object1_id2) ;
2772 Fetch l_party into l_name ;
2773 Close l_party;
2774 Elsif p_jtot_object1_code = 'OKX_CUSTACCT' then
2775 Open l_cust_acct(p_object1_id1,p_object1_id2) ;
2776 Fetch l_cust_acct into l_name ;
2777 Close l_cust_acct;
2778 Elsif p_jtot_object1_code = 'OKX_CUSTPROD' then
2779 Open l_cust_prod(p_object1_id1,p_object1_id2 );
2780 Fetch l_cust_prod into l_name;
2781 Close l_cust_prod ;
2782 Elsif p_jtot_object1_code = 'OKX_COVITEM' then
2783 Open l_item(p_object1_id1,p_object1_id2);
2784 Fetch l_item into l_name;
2785 Close l_item;
2786 Elsif p_jtot_object1_code = 'OKX_COVSITE' then
2787 Open l_site(p_object1_id1,p_object1_id2);
2788 Fetch l_site into l_name ;
2789 Close l_site ;
2790 Elsif p_jtot_object1_code = 'OKX_COVSYST' then
2791 Open l_system(p_object1_id1,p_object1_id2);
2792 Fetch l_system into l_name;
2793 Close l_system ;
2794 Else
2795 l_name := p_jtot_object1_code ;
2796 End If;
2797
2798 Return(l_name);
2799
2800
2801 Exception When Others then
2802 FND_FILE.PUT_LINE( FND_FILE.LOG, '*** Billing Report:: Sub_Line_Name Error ' || sqlerrm);
2803 Return(OKC_API.G_RET_STS_UNEXP_ERROR );
2804 OKC_API.SET_MESSAGE
2805 (P_App_Name => G_APP_NAME
2806 ,P_Msg_Name => G_UNEXPECTED_ERROR
2807 ,P_Token1 => G_SQLCODE_TOKEN
2808 ,P_Token1_Value => SQLCODE
2809 ,P_Token2 => G_SQLERRM_TOKEN
2810 ,P_Token2_Value => SQLERRM);
2811
2812 End Sub_Line_Name ;
2813
2814
2815 /* *** --------------------------------------------- *** */
2816
2817
2818 PROCEDURE delete_duplicate_currency_code (p_currency_table IN OKS_BILL_REC_PUB.line_report_tbl_type
2819 ,x_currency_table OUT NOCOPY OKS_BILL_REC_PUB.line_report_tbl_type
2820 ,x_return_status OUT NOCOPY Varchar2
2821 ) IS
2822
2823 l_currency_rec_tmp OKS_BILL_REC_PUB.line_report_rec_type;
2824 l_currency_table_tmp OKS_BILL_REC_PUB.line_report_tbl_type DEFAULT p_currency_table ;
2825
2826 l_tbl_idx Binary_integer;
2827 l_currency_cd1 Varchar2(15) ;
2828 l_currency_cd2 Varchar2(15);
2829
2830 Begin
2831 x_return_status := OKC_API.G_RET_STS_SUCCESS;
2832 l_tbl_idx := l_currency_table_tmp.FIRST;
2833
2834 While l_tbl_idx Is Not Null
2835 Loop
2836 l_currency_cd1 := l_currency_table_tmp(l_tbl_idx).currency_code ;
2837 l_currency_cd2 := l_currency_rec_tmp.currency_code;
2838
2839 If l_currency_cd1 = l_currency_cd2 Then
2840 l_currency_table_tmp.DELETE(l_tbl_idx) ;
2841 Else
2842 l_currency_rec_tmp := l_currency_table_tmp(l_tbl_idx) ;
2843 End If;
2844
2845 l_tbl_idx := l_currency_table_tmp.NEXT(l_tbl_idx) ;
2846 End Loop;
2847
2848 x_currency_table := l_currency_table_tmp ;
2849 x_return_status := OKC_API.G_RET_STS_SUCCESS;
2850
2851 Exception When Others Then
2852
2853 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
2854 FND_FILE.PUT_LINE( FND_FILE.LOG, '*** Billing Report:: delete_duplicate_currency_code Error ' || sqlerrm);
2855
2856 OKC_API.SET_MESSAGE
2857 (P_App_Name => G_APP_NAME
2858 ,P_Msg_Name => G_UNEXPECTED_ERROR
2859 ,P_Token1 => G_SQLCODE_TOKEN
2860 ,P_Token1_Value => SQLCODE
2861 ,P_Token2 => G_SQLERRM_TOKEN
2862 ,P_Token2_Value => SQLERRM);
2863
2864
2865 End delete_duplicate_currency_code ;
2866
2867
2868
2869 /* *** --------------------------------------------- *** */
2870
2871
2872 PROCEDURE sort_currency_table (p_currency_table IN OKS_BILL_REC_PUB.line_report_tbl_type
2873 ,x_currency_table OUT NOCOPY OKS_BILL_REC_PUB.line_report_tbl_type
2874 ,x_return_status OUT NOCOPY Varchar2
2875 ) IS
2876
2877 l_currency_rec_tmp OKS_BILL_REC_PUB.line_report_rec_type;
2878 l_currency_tbl_tmp OKS_BILL_REC_PUB.line_report_tbl_type DEFAULT p_currency_table ;
2879
2880 l_in_tbl_idx Binary_integer;
2881 l_out_tbl_idx Binary_integer;
2882 l_currency_cd1 Varchar2(15) ;
2883 l_currency_cd2 Varchar2(15);
2884
2885 Begin
2886
2887 x_return_status := OKC_API.G_RET_STS_SUCCESS;
2888 l_out_tbl_idx := l_currency_tbl_tmp.FIRST;
2889
2890 While l_out_tbl_idx is Not Null
2891 Loop
2892 l_in_tbl_idx := l_out_tbl_idx ;
2893
2894 While l_in_tbl_idx Is Not Null
2895 Loop
2896 l_currency_cd1 := l_currency_tbl_tmp(l_out_tbl_idx).Currency_code ;
2897 l_currency_cd2 := l_currency_tbl_tmp(l_in_tbl_idx).Currency_code ;
2898
2899 IF l_currency_cd1 > l_currency_cd2 then
2900 l_currency_rec_tmp := l_currency_tbl_tmp(l_out_tbl_idx) ;
2901 l_currency_tbl_tmp(l_out_tbl_idx) := l_currency_tbl_tmp(l_in_tbl_idx);
2902 l_currency_tbl_tmp(l_in_tbl_idx) := l_currency_rec_tmp ;
2903 End If;
2904
2905 l_in_tbl_idx := l_currency_tbl_tmp.NEXT(l_in_tbl_idx) ;
2906 End Loop;
2907
2908 l_out_tbl_idx := l_currency_tbl_tmp.NEXT(l_out_tbl_idx) ;
2909
2910
2911 End Loop ;
2912
2913 x_currency_table := l_currency_tbl_tmp ;
2914 x_return_status := OKC_API.G_RET_STS_SUCCESS;
2915
2916 Exception When Others Then
2917 FND_FILE.PUT_LINE( FND_FILE.LOG, '*** Billing Report:: sort_currency_table Error ' || sqlerrm);
2918 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
2919 OKC_API.SET_MESSAGE
2920 (P_App_Name => G_APP_NAME
2921 ,P_Msg_Name => G_UNEXPECTED_ERROR
2922 ,P_Token1 => G_SQLCODE_TOKEN
2923 ,P_Token1_Value => SQLCODE
2924 ,P_Token2 => G_SQLERRM_TOKEN
2925 ,P_Token2_Value => SQLERRM);
2926
2927
2928 End sort_currency_table ;
2929
2930
2931 /* *** --------------------------------------------- *** */
2932 Procedure Get_Currency_code(p_lines_table IN OKS_BILL_REC_PUB.line_report_tbl_type
2933 ,p_sub_lines_table IN OKS_BILL_REC_PUB.line_report_tbl_type
2934 ,x_currency_table_out OUT NOCOPY OKS_BILL_REC_PUB.line_report_tbl_type
2935 ,x_return_status OUT NOCOPY Varchar2
2936 ) IS
2937
2938 l_currency_table_tmp OKS_BILL_REC_PUB.line_report_tbl_type ;
2939 l_currency_table_in OKS_BILL_REC_PUB.line_report_tbl_type ;
2940 l_lines_table_tmp OKS_BILL_REC_PUB.line_report_tbl_type DEFAULT p_lines_table ;
2941 l_sub_lines_table_tmp OKS_BILL_REC_PUB.line_report_tbl_type DEFAULT p_sub_lines_table;
2942
2943 l_line_tbl_idx Binary_Integer ;
2944 l_sub_line_tbl_idx Binary_Integer ;
2945
2946 l_currency_tbl_idx Binary_integer ;
2947
2948 l_curreny_code Varchar(15) := Null;
2949 l_return_status Varchar2(30) ;
2950
2951 Begin
2952 x_return_status := OKC_API.G_RET_STS_SUCCESS;
2953
2954 l_line_tbl_idx := l_lines_table_tmp.FIRST;
2955 l_curreny_code := 'X';
2956 l_currency_tbl_idx := 1 ;
2957
2958 While l_line_tbl_idx IS NOT NULL
2959 Loop
2960 If l_curreny_code <> l_lines_table_tmp(l_line_tbl_idx).Currency_Code then
2961
2962 l_currency_table_tmp(l_currency_tbl_idx).currency_code := l_lines_table_tmp(l_line_tbl_idx).Currency_Code ;
2963 l_currency_tbl_idx := l_currency_tbl_idx + 1;
2964 l_curreny_code := l_lines_table_tmp(l_line_tbl_idx).Currency_Code ;
2965
2966 End If;
2967 l_line_tbl_idx := l_lines_table_tmp.NEXT(l_line_tbl_idx);
2968 End Loop ;
2969
2970 l_sub_line_tbl_idx := l_sub_lines_table_tmp.FIRST;
2971
2972 While l_sub_line_tbl_idx IS NOT NULL
2973 Loop
2974 If l_curreny_code <> l_sub_lines_table_tmp(l_sub_line_tbl_idx).currency_code Then
2975
2976 l_currency_table_tmp(l_currency_tbl_idx).currency_code := l_sub_lines_table_tmp(l_sub_line_tbl_idx).currency_code ;
2977 l_currency_tbl_idx := l_currency_tbl_idx + 1;
2978 l_curreny_code := l_sub_lines_table_tmp(l_sub_line_tbl_idx).currency_code;
2979
2980 End If;
2981 l_sub_line_tbl_idx := l_sub_lines_table_tmp.NEXT(l_sub_line_tbl_idx) ;
2982 End Loop;
2983
2984
2985
2986 /* *** Sort Currency Table *** */
2987
2988 l_currency_table_in := l_currency_table_tmp ;
2989 l_currency_table_tmp.DELETE ;
2990 /*
2991 SORT_CURRENCY_TABLE (p_currency_table => l_currency_table_in
2992 ,x_currency_table => l_currency_table_tmp
2993 ,x_return_status => l_return_status
2994 ) ;
2995
2996 If l_return_status = OKC_API.G_RET_STS_SUCCESS Then
2997
2998
2999 l_currency_table_in := l_currency_table_tmp ;
3000 l_currency_table_tmp.DELETE ;*/
3001
3002 DELETE_DUPLICATE_CURRENCY_CODE (p_currency_table => l_currency_table_in
3003 ,x_currency_table => l_currency_table_tmp
3004 ,x_return_status => l_return_status
3005 ) ;
3006 x_return_status := l_return_status ;
3007 /* If l_return_status <> OKC_API.G_RET_STS_SUCCESS Then
3008 x_return_status := l_return_status ;
3009 End If;
3010 Else
3011 x_return_status := l_return_status ;
3012 End If;*/
3013
3014
3015 x_currency_table_out := l_currency_table_tmp ;
3016
3017 Exception When Others then
3018 FND_FILE.PUT_LINE( FND_FILE.LOG, '*** Billing Report:: Get_Currency_code Error ' || sqlerrm);
3019 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
3020 OKC_API.SET_MESSAGE
3021 (P_App_Name => G_APP_NAME
3022 ,P_Msg_Name => G_UNEXPECTED_ERROR
3023 ,P_Token1 => G_SQLCODE_TOKEN
3024 ,P_Token1_Value => SQLCODE
3025 ,P_Token2 => G_SQLERRM_TOKEN
3026 ,P_Token2_Value => SQLERRM);
3027
3028 End Get_Currency_code;
3029
3030
3031 /* *** --------------------------------------------- *** */
3032
3033
3034 Procedure Print_currency_break ( p_currency_code IN VARCHAR2
3035 ,p_contracts_processed IN NUMBER
3036 ,p_lines_processed IN NUMBER
3037 ,p_lines_total IN NUMBER
3038 ,p_lines_success IN NUMBER
3039 ,p_lines_successtot IN NUMBER
3040 ,p_lines_rejected IN NUMBER
3041 ,p_rejected_lines_total IN NUMBER
3042 ,p_slines_processed IN NUMBER
3043 ,p_slines_total IN NUMBER
3044 ,p_slines_success IN NUMBER
3045 ,p_slines_successtot IN NUMBER
3046 ,p_slines_rejected IN NUMBER
3047 ,p_rejected_slines_total IN NUMBER
3048 ,x_return_status OUT NOCOPY VARCHAR2
3049 ) IS
3050
3051
3052
3053 CURSOR l_currency (p_currency_code IN VARCHAR) is
3054 SELECT name FROM fnd_currencies_tl
3055 WHERE currency_code = p_currency_code
3056 AND language = USERENV('LANG');
3057
3058
3059 l_dnz_chr_id NUMBER := 0;
3060 l_line_id NUMBER := 0;
3061 l_contracts_total NUMBER := 0;
3062 l_rejected_con_total NUMBER := 0;
3063 l_length NUMBER := 0;
3064
3065 l_msg VARCHAR2(2000);
3066 l_cur_msg VARCHAR2(2000);
3067
3068 l_currency_desc VARCHAR2(100);
3069
3070 Begin
3071
3072 x_return_status := OKC_API.G_RET_STS_SUCCESS;
3073
3074 fnd_message.set_name ('OKS','OKS_BILLREP_CURRSUMM');
3075 l_msg := fnd_message.get;
3076 fnd_message.set_name ('OKS','OKS_BILLREP_CURVAL');
3077 l_cur_msg := fnd_message.get;
3078
3079 OPEN l_currency(p_currency_code);
3080 FETCH l_currency into l_currency_desc;
3081 CLOSE l_currency;
3082
3083
3084 l_contracts_total := nvl(p_lines_total,0) + nvl(p_slines_total,0) ;
3085 l_rejected_con_total := nvl(p_rejected_lines_total,0) + nvl(p_rejected_slines_total,0) ;
3086 l_length := length(l_currency_desc||l_msg);
3087
3088 FND_FILE.PUT_LINE( FND_FILE.OUTPUT, ' ' ) ;
3089 FND_FILE.PUT_LINE( FND_FILE.OUTPUT, ' ' ) ;
3090 FND_FILE.PUT_LINE( FND_FILE.OUTPUT, l_msg||' '||l_currency_desc ) ;
3091 FND_FILE.PUT_LINE( FND_FILE.OUTPUT, rpad('====',l_length,'===' )) ;
3092 FND_FILE.PUT_LINE( FND_FILE.OUTPUT, ' ' ) ;
3093
3094
3095 fnd_message.set_name ('OKS','OKS_BILLREP_CON_PROC');
3096 l_msg := fnd_message.get;
3097
3098 fnd_message.set_name ('OKS','OKS_BILLREP_CON_PROC');
3099 l_msg := fnd_message.get;
3100
3101 --bug#4323607 maanand
3102
3103 FND_FILE.PUT_LINE( FND_FILE.OUTPUT, rpad(l_msg||':',30,' ')||rpad(to_char(p_contracts_processed),10,' ')||' '||l_cur_msg||': '|| to_char (l_contracts_total, fnd_currency.get_format_mask(p_currency_code, 50)));
3104
3105
3106
3107 fnd_message.set_name ('OKS','OKS_BILLREP_LINE_PROC');
3108 l_msg := fnd_message.get;
3109 FND_FILE.PUT_LINE( FND_FILE.OUTPUT, ' ') ;
3110 FND_FILE.PUT_LINE( FND_FILE.OUTPUT, ' ') ;
3111 FND_FILE.PUT_LINE( FND_FILE.OUTPUT, rpad(l_msg||':',30,' ')||rpad(to_char(p_lines_processed),10,' ')||' '||l_cur_msg||': '|| to_char (p_lines_total, fnd_currency.get_format_mask(p_currency_code, 50)));
3112 fnd_message.set_name ('OKS','OKS_BILLREP_LINE_SUCC');
3113 l_msg := fnd_message.get;
3114 FND_FILE.PUT_LINE( FND_FILE.OUTPUT, rpad(l_msg||':',30,' ')||rpad(to_char(p_lines_success),10,' ')||' '||l_cur_msg||': '|| to_char (p_lines_successtot, fnd_currency.get_format_mask(p_currency_code, 50)));
3115 fnd_message.set_name ('OKS','OKS_BILLREP_LINE_REJ');
3116 l_msg := fnd_message.get;
3117 FND_FILE.PUT_LINE( FND_FILE.OUTPUT, rpad(l_msg||':',30,' ')||rpad(to_char(p_lines_rejected),10,' ')||' '||l_cur_msg||': '|| to_char (p_rejected_lines_total, fnd_currency.get_format_mask(p_currency_code, 50)));
3118
3119
3120 FND_FILE.PUT_LINE( FND_FILE.OUTPUT, ' ') ;
3121 FND_FILE.PUT_LINE( FND_FILE.OUTPUT, ' ') ;
3122 fnd_message.set_name ('OKS','OKS_BILLREP_SUBLINE_PROC');
3123 l_msg := fnd_message.get;
3124 FND_FILE.PUT_LINE( FND_FILE.OUTPUT, rpad(l_msg||':',30,' ')||rpad(to_char(p_slines_processed),10,' ')||' '||l_cur_msg||': '|| to_char (p_slines_total, fnd_currency.get_format_mask(p_currency_code, 50)));
3125 fnd_message.set_name ('OKS','OKS_BILLREP_SUBLINE_SUCC');
3126 l_msg := fnd_message.get;
3127 FND_FILE.PUT_LINE( FND_FILE.OUTPUT, rpad(l_msg||':',30,' ')||rpad(to_char(p_slines_success),10,' ')||' '||l_cur_msg||': '|| to_char (p_slines_successtot, fnd_currency.get_format_mask(p_currency_code, 50)));
3128 fnd_message.set_name ('OKS','OKS_BILLREP_SUBLINE_REJ');
3129 l_msg := fnd_message.get;
3130 FND_FILE.PUT_LINE( FND_FILE.OUTPUT, rpad(l_msg||':',30,' ')||rpad(to_char(p_slines_rejected),10,' ')||' '||l_cur_msg||': '|| to_char (p_rejected_slines_total, fnd_currency.get_format_mask(p_currency_code, 50)));
3131
3132
3133 FND_FILE.PUT_LINE( FND_FILE.OUTPUT, ' ' ) ;
3134 FND_FILE.PUT_LINE( FND_FILE.OUTPUT, ' ' ) ;
3135 FND_FILE.PUT_LINE( FND_FILE.OUTPUT, ' ' ) ;
3136
3137 Exception When Others Then
3138 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR ;
3139 FND_FILE.PUT_LINE( FND_FILE.LOG, '*** Billing Report:: Print_currency_break Error ' || sqlerrm);
3140 OKC_API.SET_MESSAGE
3141 (P_App_Name => G_APP_NAME
3142 ,P_Msg_Name => G_UNEXPECTED_ERROR
3143 ,P_Token1 => G_SQLCODE_TOKEN
3144 ,P_Token1_Value => SQLCODE
3145 ,P_Token2 => G_SQLERRM_TOKEN
3146 ,P_Token2_Value => SQLERRM);
3147
3148 End Print_currency_break;
3149
3150
3151 /* *** --------------------------------------------- *** */
3152
3153
3154 Procedure Print_Error_Report ( p_billrep_error_tbl IN OKS_BILL_REC_PUB.billrep_error_tbl_type
3155 ,p_lines_rejected IN Number
3156 ,p_slines_rejected IN Number
3157 ,x_return_status OUT NOCOPY Varchar2
3158 ) IS
3159
3160 Cursor l_details_csr(p_line_id IN number) IS
3161 SELECT Hdr.Contract_number
3162 ,Hdr.Contract_number_modifier
3163 ,Hdr.Currency_code
3164 ,Hdr.Inv_organization_id
3165 ,Hdr.authoring_org_id
3166 ,line.dnz_chr_id
3167 ,line.cle_id
3168 ,line.lse_id
3169 ,line.start_date
3170 ,line.end_date
3171 ,line.price_negotiated
3172 ,line.date_terminated
3173 ,okp.object1_id1
3174 ,okp.object1_id2
3175 ,line.line_number
3176 FROM
3177 OKC_K_PARTY_ROLES_B okp
3178 ,OKC_K_LINES_B line
3179 ,OKC_K_HEADERS_B Hdr
3180 WHERE Hdr.id = line.dnz_chr_id
3181 AND line.id = p_line_id
3182 AND okp.dnz_chr_id = hdr.id
3183 AND okp.rle_code in ( 'CUSTOMER','SUBSCRIBER');
3184
3185
3186 Cursor l_customer_csr(p_object1_id1 Number,p_object1_id2 Varchar2) IS
3187 Select cst.PARTY_NUMBER
3188 ,cst.NAME
3189 From OKX_PARTIES_V cst
3190 Where cst.id1 = p_object1_id1
3191 and cst.id2 = p_object1_id2 ;
3192
3193 Cursor l_cont_group_csr(p_dnz_chr_id Number) IS
3194 Select grp.name
3195 From OKC_K_GROUPS_V grp
3196 ,OKC_K_GRPINGS gpg
3197 Where gpg.included_chr_id = p_dnz_chr_id
3198 and grp.id = gpg.cgp_parent_id
3199 and rownum < 2 ;
3200
3201 Cursor l_service_account_csr (p_line_id Number ) IS
3202 Select cst.PARTY_ID||' - '||cst.NAME ServiceAccount
3203 From OKC_RULE_GROUPS_V rgp
3204 ,OKC_RULES_V rul
3205 ,OKX_CUSTOMER_ACCOUNTS_V cst
3206 Where rgp.CLE_ID = p_line_id
3207 and rgp.ID = rul.RGP_ID
3208 and rul.RULE_INFORMATION_CATEGORY = 'CAN'
3209 and cst.ID1 = rul.OBJECT1_ID1
3210 and cst.ID2 = rul.OBJECT1_ID2 ;
3211
3212
3213 Cursor l_line_name_csr (p_line_id Number,p_organization_id Number) IS
3214 Select sys.NAME LineName
3215 From OKX_SYSTEM_ITEMS_V sys
3216 ,OKC_K_ITEMS itm
3217 Where itm.CLE_ID = p_line_id
3218 and sys.ID1 = itm.OBJECT1_ID1
3219 and sys.ID2 = itm.OBJECT1_ID2;
3220
3221
3222 Cursor l_sub_line_name_csr(p_sub_line_id Number) IS
3223 Select decode(itm.JTOT_OBJECT1_CODE,
3224 'OKX_CUSTPROD', 'Covered Product',
3225 'OKX_COVITEM', 'Covered Item',
3226 'OKX_COVSITE', 'Covered Site',
3227 'OKX_COVSYST', 'Covered System',
3228 'OKX_CUSTACCT', 'Customer Account',
3229 'OKX_PARTY', 'Covered Party',
3230 itm.JTOT_OBJECT1_CODE) CoveredLine
3231 ,itm.JTOT_OBJECT1_CODE
3232 ,itm.object1_id1
3233 ,itm.object1_id2
3234 From OKC_K_ITEMS itm
3235 Where itm.CLE_ID = p_sub_line_id;
3236
3237 l_line_idx Binary_Integer;
3238 l_sub_line_idx Binary_Integer;
3239
3240 l_customer_number Varchar2(30) := Null;
3241 l_customer_name Varchar2(360) := Null;
3242 l_group_name Varchar2(150) := Null;
3243 l_service_account Varchar2(360) := Null;
3244 l_line_name Varchar2(360) := Null;
3245 l_covered_level Varchar2(50) := Null;
3246 l_sub_line_name Varchar2(370) := Null;
3247 l_subline_obj1_id1 Varchar2(40) := 0 ;
3248 l_subline_obj1_id2 Varchar2(3) := Null;
3249 l_subline_jtot_code Varchar2(30) := Null;
3250 l_dnz_chr_id Number := 0;
3251 l_line_id Number := 0;
3252 l_contracts_total Number := 0;
3253 l_rejected_con_total Number := 0;
3254
3255 l_cont_num OKC_K_HEADERS_B.CONTRACT_NUMBER%type := Null;
3256 l_cont_num_mod OKC_K_HEADERS_B.CONTRACT_NUMBER_MODIFIER%type := Null ;
3257 l_bil_amt Varchar2(16) := Null ;
3258 l_line_num Varchar2(300) := Null;
3259
3260 l_string Varchar2(2000) := ' ' ;
3261 l_error_string1 Varchar2(2000) := Null;
3262 l_error_string2 Varchar2(2000) := Null;
3263 l_error_string3 Varchar2(2000) := Null;
3264 l_error_string4 Varchar2(2000) := Null;
3265 l_error_string5 Varchar2(2000) := Null;
3266 l_error_string6 Varchar2(2000) := Null;
3267 l_error_string7 Varchar2(2000) := Null;
3268
3269 l_obj_id1 Number := 0;
3270 l_obj_id2 Varchar2(3) := Null;
3271 l_header_id Number;
3272
3273 detail_rec l_details_csr%rowtype;
3274
3275
3276 Begin
3277
3278 x_return_status := OKC_API.G_RET_STS_SUCCESS;
3279
3280
3281 /* *** ---- Print the error message ---- *** */
3282
3283 If ((p_lines_rejected >= 1 AND p_billrep_error_tbl.count > 0 )or (p_slines_rejected >= 1 AND p_billrep_error_tbl.count > 0) ) then
3284
3285 FND_FILE.PUT_LINE( FND_FILE.OUTPUT, ' ') ;
3286 FND_FILE.PUT_LINE( FND_FILE.OUTPUT, ' ') ;
3287 FND_FILE.PUT_LINE( FND_FILE.OUTPUT, ' ') ;
3288 FND_FILE.PUT_LINE( FND_FILE.OUTPUT, ' Following are the Lines Rejected by Billing Program ') ;
3289 FND_FILE.PUT_LINE( FND_FILE.OUTPUT, ' *************************************************** ') ;
3290 FND_FILE.PUT_LINE( FND_FILE.OUTPUT, ' ') ;
3291 FND_FILE.PUT_LINE( FND_FILE.OUTPUT, ' ') ;
3292
3293 End If;
3294
3295
3296 If (p_lines_rejected >= 1 AND p_billrep_error_tbl.count > 0) then
3297
3298 l_line_idx := p_billrep_error_tbl.FIRST;
3299 l_dnz_chr_id := 0;
3300
3301 Loop
3302
3303 If (p_billrep_error_tbl(l_line_idx).Sub_line_id is NULL) then
3304
3305 OPEN l_details_csr(p_billrep_error_tbl(l_line_idx).Top_Line_id);
3306 FETCH l_details_csr INTO detail_rec;
3307 CLOSE l_details_csr;
3308
3309
3310 If l_dnz_chr_id <> detail_rec.dnz_chr_id then
3311
3312 Open l_customer_csr(detail_rec.object1_id1,detail_rec.object1_id2) ;
3313 Fetch l_customer_csr into l_customer_number, l_customer_name ;
3314 Close l_customer_csr ;
3315
3316 Open l_cont_group_csr(detail_rec.dnz_chr_id) ;
3317 Fetch l_cont_group_csr into l_group_name ;
3318 Close l_cont_group_csr ;
3319
3320 l_dnz_chr_id := detail_rec.dnz_chr_id ;
3321 l_cont_num := detail_rec.Contract_number ;
3322 l_cont_num_mod := detail_rec.Contract_number_modifier ;
3323
3324 FND_FILE.PUT_LINE( FND_FILE.OUTPUT,'Contract: '||l_cont_num||' - '||l_cont_num_mod||' Group: '||l_group_name|| ' Customer: '||l_customer_number||' - '||l_customer_name);
3325
3326 End If;
3327
3328 Open l_line_name_csr (p_billrep_error_tbl(l_line_idx).Top_Line_id,detail_rec.Inv_organization_id );
3329 Fetch l_line_name_csr into l_line_name ;
3330 Close l_line_name_csr;
3331 Open l_service_account_csr (p_billrep_error_tbl(l_line_idx).Top_Line_id) ;
3332 Fetch l_service_account_csr into l_service_account ;
3333 Close l_service_account_csr;
3334
3335 l_bil_amt := detail_rec.price_negotiated ;
3336 l_line_num := detail_rec.line_number ;
3337
3338 -- bug#4323607 maanand
3339
3340 FND_FILE.PUT_LINE( FND_FILE.OUTPUT,l_string||'Service Line: '||l_line_num||' '||l_line_name||' Service Account: '||l_service_account||' For Amount: '|| to_char (l_bil_amt, fnd_currency.get_format_mask(detail_rec.currency_code, 50)));
3341
3342 l_error_string1 := 'Reason: '||substr(p_billrep_error_tbl(l_line_idx).Error_Message,1,50);
3343 l_error_string2 := substr(p_billrep_error_tbl(l_line_idx).Error_Message,51,100);
3344 l_error_string3 := substr(p_billrep_error_tbl(l_line_idx).Error_Message,101,150);
3345 l_error_string4 := substr(p_billrep_error_tbl(l_line_idx).Error_Message,151,200);
3346 l_error_string5 := substr(p_billrep_error_tbl(l_line_idx).Error_Message,201,250);
3347 l_error_string6 := substr(p_billrep_error_tbl(l_line_idx).Error_Message,251,300);
3348 l_error_string7 := substr(p_billrep_error_tbl(l_line_idx).Error_Message,301,350);
3349
3350 If length(l_error_string1) > 1 Then
3351 FND_FILE.PUT_LINE( FND_FILE.OUTPUT,l_string||l_string||l_string||l_error_string1 );
3352 End If;
3353 If length(l_error_string2) > 1 Then
3354 FND_FILE.PUT_LINE( FND_FILE.OUTPUT,l_string||l_string||l_string||l_error_string2);
3355 End If;
3356 If length(l_error_string3) > 1 Then
3357 FND_FILE.PUT_LINE( FND_FILE.OUTPUT,l_string||l_string||l_string||l_error_string3);
3358 End If;
3359 If length(l_error_string4) > 1 Then
3360 FND_FILE.PUT_LINE( FND_FILE.OUTPUT,l_string||l_string||l_string||l_error_string4);
3361 End If;
3362 If length(l_error_string5) > 1 Then
3363 FND_FILE.PUT_LINE( FND_FILE.OUTPUT,l_string||l_string||l_string||l_error_string5);
3364 End If;
3365 If length(l_error_string6) > 1 Then
3366 FND_FILE.PUT_LINE( FND_FILE.OUTPUT,l_string||l_string||l_string||l_error_string6);
3367 End If;
3368 If length(l_error_string7) > 1 Then
3369 FND_FILE.PUT_LINE( FND_FILE.OUTPUT,l_string||l_string||l_string||l_error_string7);
3370 End If;
3371
3372 End If;
3373 EXIT WHEN l_line_idx = p_billrep_error_tbl.LAST;
3374 l_line_idx := p_billrep_error_tbl.NEXT(l_line_idx) ;
3375 End Loop ;
3376 End If;
3377
3378 If (p_slines_rejected >= 1 AND p_billrep_error_tbl.count > 0 ) then
3379
3380 l_sub_line_idx := p_billrep_error_tbl.FIRST;
3381 l_dnz_chr_id := 0;
3382 l_line_id := 0;
3383 l_line_name := Null;
3384 l_service_account := Null;
3385 l_group_name := Null;
3386
3387 Loop
3388
3389 If (p_billrep_error_tbl(l_sub_line_idx).Sub_line_id is NOT NULL) then
3390
3391
3392 OPEN l_details_csr(p_billrep_error_tbl(l_sub_line_idx).Sub_line_id);
3393 FETCH l_details_csr INTO detail_rec;
3394 CLOSE l_details_csr;
3395
3396 /* *** Print Contract details *** */
3397
3398 If l_dnz_chr_id <> detail_rec.dnz_chr_id then
3399
3400
3401 Open l_customer_csr(detail_rec.object1_id1 ,detail_rec.object1_id2);
3402 Fetch l_customer_csr into l_customer_number, l_customer_name ;
3403 Close l_customer_csr ;
3404
3405
3406 Open l_cont_group_csr(detail_rec.dnz_chr_id) ;
3407 Fetch l_cont_group_csr into l_group_name ;
3408 Close l_cont_group_csr ;
3409
3410
3411 l_dnz_chr_id := detail_rec.dnz_chr_id ;
3412 l_cont_num := detail_rec.Contract_number ;
3413 l_cont_num_mod := detail_rec.Contract_number_modifier ;
3414
3415 FND_FILE.PUT_LINE( FND_FILE.OUTPUT,'Contract: '||l_cont_num||' - '||l_cont_num_mod||' Group: '||l_group_name||' Customer: '||l_customer_number||' - '||l_customer_name);
3416
3417 End If;
3418
3419
3420 /* *** Print Line details *** */
3421
3422 If l_line_id <> p_billrep_error_tbl(l_sub_line_idx).Top_Line_id then
3423
3424 Open l_line_name_csr (p_billrep_error_tbl(l_sub_line_idx).Top_Line_id, detail_rec.Inv_organization_id);
3425 Fetch l_line_name_csr into l_line_name ;
3426 Close l_line_name_csr;
3427
3428 Open l_service_account_csr (p_billrep_error_tbl(l_sub_line_idx).Top_Line_id) ;
3429 Fetch l_service_account_csr into l_service_account ;
3430 Close l_service_account_csr;
3431
3432 l_bil_amt := Null ;
3433
3434 select line_number into l_line_num from okc_k_lines_b
3435 where id=p_billrep_error_tbl(l_sub_line_idx).Top_Line_id;
3436
3437 FND_FILE.PUT_LINE( FND_FILE.OUTPUT,l_string||'Service Line: '||l_line_num||' '||l_line_name||' Service Account: '||l_service_account ) ;
3438
3439 l_line_id :=p_billrep_error_tbl(l_sub_line_idx).Top_Line_id;
3440
3441
3442 End If;
3443
3444 /* *** Print Sub Line details *** */
3445
3446 Open l_sub_line_name_csr(p_billrep_error_tbl(l_sub_line_idx).Sub_line_id) ;
3447 Fetch l_sub_line_name_csr into l_covered_level
3448 ,l_subline_jtot_code
3449 ,l_subline_obj1_id1
3450 ,l_subline_obj1_id2 ;
3451 Close l_sub_line_name_csr;
3452
3453 l_sub_line_name := ' Name: '||Sub_Line_Name (l_subline_jtot_code,to_number(l_subline_obj1_id1),l_subline_obj1_id2 );
3454 l_bil_amt := to_char(detail_rec.price_negotiated ) ;
3455 l_line_num := detail_rec.line_number;
3456
3457 FND_FILE.PUT_LINE( FND_FILE.OUTPUT,l_string||l_string||'Covered Line: '||l_line_num||' '||l_covered_level||l_sub_line_name||' For Amount: '||l_bil_amt ) ;
3458
3459 l_error_string1 := 'Reason: '||substr(p_billrep_error_tbl(l_sub_line_idx).Error_Message,1,50);
3460 l_error_string2 := substr(p_billrep_error_tbl(l_sub_line_idx).Error_Message,51,100);
3461 l_error_string3 := substr(p_billrep_error_tbl(l_sub_line_idx).Error_Message,101,150);
3462 l_error_string4 := substr(p_billrep_error_tbl(l_sub_line_idx).Error_Message,151,200);
3463 l_error_string5 := substr(p_billrep_error_tbl(l_sub_line_idx).Error_Message,201,250);
3464 l_error_string6 := substr(p_billrep_error_tbl(l_sub_line_idx).Error_Message,251,300);
3465 l_error_string7 := substr(p_billrep_error_tbl(l_sub_line_idx).Error_Message,301,350);
3466
3467 If length(l_error_string1) > 1 Then
3468 FND_FILE.PUT_LINE( FND_FILE.OUTPUT,l_string||l_string||l_string||l_error_string1 );
3469 End If;
3470 If length(l_error_string2) > 1 Then
3471 FND_FILE.PUT_LINE( FND_FILE.OUTPUT,l_string||l_string||l_string||l_error_string2);
3472 End If;
3473 If length(l_error_string3) > 1 Then
3474 FND_FILE.PUT_LINE( FND_FILE.OUTPUT,l_string||l_string||l_string||l_error_string3);
3475 End If;
3476 If length(l_error_string4) > 1 Then
3477 FND_FILE.PUT_LINE( FND_FILE.OUTPUT,l_string||l_string||l_string||l_error_string4);
3478 End If;
3479 If length(l_error_string5) > 1 Then
3480 FND_FILE.PUT_LINE( FND_FILE.OUTPUT,l_string||l_string||l_string||l_error_string5);
3481 End If;
3482 If length(l_error_string6) > 1 Then
3483 FND_FILE.PUT_LINE( FND_FILE.OUTPUT,l_string||l_string||l_string||l_error_string6);
3484 End If;
3485 If length(l_error_string7) > 1 Then
3486 FND_FILE.PUT_LINE( FND_FILE.OUTPUT,l_string||l_string||l_string||l_error_string7);
3487 End If;
3488
3489 End If;
3490 EXIT WHEN l_sub_line_idx = p_billrep_error_tbl.LAST;
3491 l_sub_line_idx := p_billrep_error_tbl.NEXT(l_sub_line_idx) ;
3492 End Loop;
3493 End If;
3494
3495
3496
3497 Exception When Others Then
3498 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR ;
3499 FND_FILE.PUT_LINE( FND_FILE.LOG, '*** Billing Report:: Print_Error_Report ' || sqlerrm);
3500 OKC_API.SET_MESSAGE
3501 (P_App_Name => G_APP_NAME
3502 ,P_Msg_Name => G_UNEXPECTED_ERROR
3503 ,P_Token1 => G_SQLCODE_TOKEN
3504 ,P_Token1_Value => SQLCODE
3505 ,P_Token2 => G_SQLERRM_TOKEN
3506 ,P_Token2_Value => SQLERRM);
3507
3508 End Print_Error_Report;
3509
3510
3511 /* *** --------------------------------------------- *** */
3512
3513 Procedure Create_Report (
3514 p_billrep_table IN OKS_BILL_REC_PUB.bill_report_tbl_type
3515 ,p_billrep_err_tbl IN OKS_BILL_REC_PUB.billrep_error_tbl_type
3516 ,p_line_from IN NUMBER
3517 ,p_line_to IN NUMBER
3518 ,x_return_status OUT NOCOPY Varchar2
3519 ) IS
3520
3521
3522
3523 CURSOR Contract_Cnt_Csr(p_code in Varchar2,p_process_from IN NUMBER,p_process_to IN NUMBER) IS
3524 SELECT Count(Distinct Chr_id)
3525 FROM oks_process_billing
3526 where currency_code= p_code
3527 and line_no between p_process_from and p_process_to;
3528
3529 l_return_status Varchar2(100);
3530
3531 l_Sublines_count Number := 0;
3532 l_Sublines_value Number := 0;
3533
3534 l_lines_value Number := 0;
3535 l_lines_count Number := 0;
3536
3537 l_lines_rejected Number := 0;
3538 l_slines_rejected Number := 0;
3539
3540 l_contracts_processed Number := 0;
3541
3542 i Number := 0;
3543 l_msg Varchar2(2000);
3544 l_curr_break_line Varchar2(2000) := '==============================================================================================================';
3545
3546 Create_Report_Exception Exception ;
3547
3548 Begin
3549 x_return_status := OKC_API.G_RET_STS_SUCCESS;
3550 l_return_status := Null;
3551
3552
3553
3554 fnd_message.set_name('OKS','OKS_BILLREP_TITLE');
3555 l_msg := fnd_message.get;
3556 FND_FILE.PUT_LINE( FND_FILE.OUTPUT, ' ') ;
3557 FND_FILE.PUT_LINE( FND_FILE.OUTPUT, ' ') ;
3558 FND_FILE.PUT_LINE( FND_FILE.OUTPUT, ' ') ;
3559 FND_FILE.PUT_LINE( FND_FILE.OUTPUT, ' '|| l_msg ||' ') ;
3560 FND_FILE.PUT_LINE( FND_FILE.OUTPUT, ' **************************************** ') ;
3561
3562 FND_FILE.PUT_LINE( FND_FILE.OUTPUT, ' ') ;
3563 FND_FILE.PUT_LINE( FND_FILE.OUTPUT, ' ') ;
3564 /* *** Print the currency break up for each currency *** */
3565
3566
3567 --Start mchoudha Bug#3537100 17-APR-04
3568 --For Billing Report
3569
3570
3571
3572 IF (p_billrep_table.count > 0) THEN
3573 i := p_billrep_table.FIRST;
3574 LOOP
3575
3576 OPEN Contract_Cnt_Csr(p_billrep_table(i).Currency_code,p_line_from,p_line_to);
3577 FETCH Contract_Cnt_Csr into l_contracts_processed;
3578 CLOSE Contract_Cnt_Csr;
3579
3580
3581 l_Sublines_count := p_billrep_table(i).Successful_SubLines + p_billrep_table(i).Rejected_SubLines ;
3582
3583
3584 l_Sublines_value := p_billrep_table(i).Successful_SubLines_Value +
3585 p_billrep_table(i).Rejected_SubLines_Value ;
3586
3587 l_lines_value := p_billrep_table(i).Successful_Lines_Value + p_billrep_table(i).Rejected_Lines_Value;
3588
3589 l_lines_count := p_billrep_table(i).Successful_Lines + p_billrep_table(i).Rejected_Lines;
3590
3591 l_slines_rejected := l_slines_rejected + p_billrep_table(i).Rejected_SubLines;
3592 l_lines_rejected := l_lines_rejected + p_billrep_table(i).Rejected_Lines;
3593
3594 PRINT_CURRENCY_BREAK ( p_currency_code => p_billrep_table(i).Currency_code
3595 ,p_contracts_processed => l_contracts_processed
3596 ,p_lines_processed =>l_lines_count
3597 ,p_lines_total => l_lines_value
3598 ,p_lines_success => p_billrep_table(i).Successful_Lines
3599 ,p_lines_successtot => p_billrep_table(i).Successful_Lines_Value
3600 ,p_lines_rejected => p_billrep_table(i).Rejected_Lines
3601 ,p_rejected_lines_total => p_billrep_table(i).Rejected_Lines_Value
3602 ,p_slines_processed => l_Sublines_count
3603 ,p_slines_total => l_Sublines_value
3604 ,p_slines_success => p_billrep_table(i).Successful_SubLines
3605 ,p_slines_successtot => p_billrep_table(i).Successful_SubLines_Value
3606 ,p_slines_rejected => p_billrep_table(i).Rejected_SubLines
3607 ,p_rejected_slines_total => p_billrep_table(i).Rejected_SubLines_Value
3608 ,x_return_status => l_return_status
3609 ) ;
3610
3611 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
3612 FND_FILE.PUT_LINE( FND_FILE.LOG, '*** Billing Report:: PRINT_CURRENCY_BREAK procedure failed for : ' || p_billrep_table(i).Currency_code ||' Error: '||sqlerrm);
3613 x_return_status := l_return_status ;
3614 Raise Create_Report_Exception ;
3615 END IF;
3616
3617 EXIT WHEN i = p_billrep_table.LAST;
3618 i := p_billrep_table.NEXT(i);
3619 END LOOP;
3620 END IF;
3621
3622
3623
3624
3625 FND_FILE.PUT_LINE( FND_FILE.OUTPUT,l_curr_break_line); /* print a line after the currency summary */
3626
3627
3628 /* -- Procedure call to print the error report */
3629
3630 --l_sub_lines_tbl_in := l_sub_lines_tbl_out ;
3631 --l_lines_tbl_in := l_lines_tbl_out ;
3632
3633
3634
3635 PRINT_ERROR_REPORT ( p_billrep_error_tbl => p_billrep_err_tbl
3636 ,p_lines_rejected => l_lines_rejected
3637 ,p_slines_rejected => l_slines_rejected
3638 ,x_return_status => l_return_status
3639 ) ;
3640
3641 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
3642 x_return_status := l_return_status ;
3643 Raise Create_Report_Exception ;
3644 END IF;
3645
3646
3647 EXCEPTION
3648
3649 WHEN Create_Report_Exception Then
3650 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR ;
3651 FND_FILE.PUT_LINE( FND_FILE.LOG, '***** Billing Report has errors :: Report Exception ***** '||sqlerrm) ;
3652 FND_FILE.PUT_LINE( FND_FILE.OUTPUT, '*** Billing Report has errors **** '||sqlerrm);
3653 OKC_API.SET_MESSAGE
3654 (P_App_Name => G_APP_NAME
3655 ,P_Msg_Name => G_UNEXPECTED_ERROR
3656 ,P_Token1 => G_SQLCODE_TOKEN
3657 ,P_Token1_Value => SQLCODE
3658 ,P_Token2 => G_SQLERRM_TOKEN
3659 ,P_Token2_Value => SQLERRM);
3660
3661 WHEN Others Then
3662 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR ;
3663 FND_FILE.PUT_LINE( FND_FILE.LOG, '***** Billing Report has errors :: When Others ***** '||sqlerrm) ;
3664 FND_FILE.PUT_LINE( FND_FILE.OUTPUT, '*** Billing Report has errors **** '||sqlerrm);
3665 OKC_API.SET_MESSAGE
3666 (P_App_Name => G_APP_NAME
3667 ,P_Msg_Name => G_UNEXPECTED_ERROR
3668 ,P_Token1 => G_SQLCODE_TOKEN
3669 ,P_Token1_Value => SQLCODE
3670 ,P_Token2 => G_SQLERRM_TOKEN
3671 ,P_Token2_Value => SQLERRM);
3672
3673 End Create_Report ;
3674
3675
3676 /* *** --------------------------------------------- *** */
3677
3678
3679 PROCEDURE UPDATE_OKS_LEVEL_ELEMENTS
3680 ( p_line_id IN number ,
3681 x_return_status OUT NOCOPY varchar2 ) IS
3682
3683 CURSOR L_OKS_LEVEL_ELEMENTS_CSR ( P_LINE_ID in NUMBER ) IS
3684 SELECT LEVL.ID
3685 FROM OKS_LEVEL_ELEMENTS LEVL ,
3686 OKC_RULES_B RULES ,
3687 OKC_RULE_GROUPS_B RGP
3688 WHERE LEVL.RUL_ID = RULES.ID
3689 AND RULES.RGP_ID = RGP.ID
3690 AND RULE_INFORMATION_CATEGORY = 'SLL'
3691 AND RGP.CLE_ID = P_LINE_ID
3692 AND LEVL.DATE_COMPLETED IS NULL ;
3693
3694 CURSOR L_GET_SUB_LINES_CSR ( P_TOP_LINE_ID IN NUMBER ) IS
3695 SELECT LINES.ID
3696 FROM OKC_K_LINES_V LINES
3697 WHERE LINES.CLE_ID = P_TOP_LINE_ID
3698 AND LINES.LSE_ID in (9, 25 );
3699
3700 L_OKS_LEVEL_ELEMENTS_REC L_OKS_LEVEL_ELEMENTS_CSR%ROWTYPE ;
3701 L_GET_SUB_LINES_REC L_GET_SUB_LINES_CSR%ROWTYPE ;
3702
3703 SUBTYPE LEVEL_ID_TBL IS OKS_BILL_LEVEL_ELEMENTS_PVT.letv_tbl_type ;
3704 L_LEVEL_ID_TBL_IN LEVEL_ID_TBL ;
3705 L_LEVEL_ID_TBL_OUT LEVEL_ID_TBL ;
3706 l_return_status Varchar2(1):= OKC_API.G_RET_STS_SUCCESS;
3707 l_msg_count number;
3708 l_msg_data Varchar2(2000);
3709 COUNTER NUMBER := 0 ;
3710 G_EXCEPTION_HALT_VALIDATION exception ;
3711
3712
3713 BEGIN
3714
3715
3716 /*************************************************************************************
3717 --THIS WILL POPULATE THE TABLE WITH THE LEVEL ELEMENTS OF TOP LINE..
3718 FOR L_OKS_LEVEL_ELEMENTS_REC IN L_OKS_LEVEL_ELEMENTS_CSR ( P_LINE_ID )
3719 LOOP
3720 L_LEVEL_ID_TBL_IN(COUNTER).ID := L_OKS_LEVEL_ELEMENTS_REC.ID ;
3721 L_LEVEL_ID_TBL_IN(COUNTER).DATE_COMPLETED := SYSDATE;
3722 COUNTER := COUNTER+1 ;
3723 END LOOP ;
3724
3725 FOR L_GET_SUB_LINES_REC IN L_GET_SUB_LINES_CSR( P_LINE_ID )
3726 LOOP
3727 FOR L_OKS_LEVEL_ELEMENTS_REC IN L_OKS_LEVEL_ELEMENTS_CSR ( L_GET_SUB_LINES_REC.ID )
3728 LOOP
3729 L_LEVEL_ID_TBL_IN(COUNTER).ID := L_OKS_LEVEL_ELEMENTS_REC.ID ;
3730 L_LEVEL_ID_TBL_IN(COUNTER).DATE_COMPLETED := SYSDATE;
3731 COUNTER := COUNTER+1 ;
3732 END LOOP ;
3733 END LOOP ;
3734
3735
3736
3737 IF ( L_LEVEL_ID_TBL_IN.COUNT > 0 ) THEN
3738
3739 oks_bill_level_elements_pvt.update_row
3740 (p_api_version => 1.0,
3741 p_init_msg_list => 'T',
3742 x_return_status => l_return_status,
3743 x_msg_count => l_msg_count,
3744 x_msg_data => l_msg_data,
3745 p_letv_tbl => L_LEVEL_ID_TBL_IN,
3746 x_letv_tbl => L_LEVEL_ID_TBL_OUT);
3747
3748
3749 IF (l_return_status <> 'S') THEN
3750 x_return_status := l_return_status;
3751 Raise G_EXCEPTION_HALT_VALIDATION;
3752 END IF;
3753 END IF ;
3754 ***************************************************************************************/
3755 Update oks_level_elements
3756 set date_completed = SYSDATE
3757 where parent_cle_id = p_line_id
3758 and date_completed is null;
3759
3760
3761 X_RETURN_STATUS := L_RETURN_STATUS ;
3762
3763 EXCEPTION
3764 WHEN G_EXCEPTION_HALT_VALIDATION THEN
3765 X_RETURN_STATUS := L_RETURN_STATUS ;
3766 WHEN OTHERS THEN
3767 X_RETURN_STATUS := OKC_API.G_RET_STS_UNEXP_ERROR;
3768 OKC_API.SET_MESSAGE(G_APP_NAME, G_UNEXPECTED_ERROR,G_SQLCODE_TOKEN,SQLCODE,G_SQLERRM_TOKEN,SQLERRM);
3769
3770 END UPDATE_OKS_LEVEL_ELEMENTS ;
3771
3772
3773 PROCEDURE CREATE_BCL_FOR_OM ( P_LINE_ID IN NUMBER ,
3774 X_RETURN_STATUS OUT NOCOPY VARCHAR2 ) IS
3775
3776 CURSOR L_GET_OKS_LINES_CSR( P_LINE_ID NUMBER ) IS
3777 SELECT TRUNC(LINE.START_DATE) LINE_START_DATE
3778 ,TRUNC(LINE.END_DATE) LINE_END_DATE
3779 ,LINE.ID
3780 ,LINE.DNZ_CHR_ID
3781 FROM OKC_K_LINES_B LINE
3782 WHERE LINE.ID = P_LINE_ID ;
3783
3784 CURSOR L_GET_BCL_LINES_CSR ( P_LINE_ID NUMBER ) IS
3785 SELECT BCL.ID ,
3786 TRUNC(BCL.DATE_BILLED_FROM) DATE_BILLED_FROM ,
3787 TRUNC(BCL.DATE_BILLED_TO) DATE_BILLED_TO,
3788 AMOUNT BCL_AMOUNT
3789 FROM OKS_BILL_CONT_LINES BCL
3790 WHERE BCL.CLE_ID = P_LINE_ID ;
3791
3792 CURSOR l_hdr_csr(p_hdr_id NUMBER) IS
3793 SELECT currency_code
3794 FROM okc_k_headers_b
3795 WHERE id = p_HDR_id;
3796
3797 L_GET_OKS_LINES_REC L_GET_OKS_LINES_CSR%ROWTYPE ;
3798 L_GET_BCL_LINES_REC L_GET_BCL_LINES_CSR%ROWTYPE ;
3799
3800 SUBTYPE BCLV_REC_TYPE IS OKS_BILLCONT_PVT.BCLV_REC_TYPE;
3801 L_BCLV_REC_IN BCLV_REC_TYPE ;
3802 L_BCLV_REC_OUT BCLV_REC_TYPE;
3803
3804 L_BCLV_REC_UPD_IN BCLV_REC_TYPE ;
3805 L_BCLV_REC_UPD_OUT BCLV_REC_TYPE ;
3806
3807
3808
3809 L_BCL_EXISTS BOOLEAN ;
3810 L_BCL_DATES_UPDATE BOOLEAN ;
3811 L_SUB_LINES_INSERTED NUMBER ;
3812 L_RETURN_STATUS VARCHAR2(4):= OKC_API.G_RET_STS_SUCCESS;
3813 L_MSG_CNT NUMBER ;
3814 L_MSG_DATA VARCHAR2(2000);
3815 L_TOTAL_AMOUNT NUMBER := 0 ;
3816 L_LINE_ID NUMBER ;
3817 L_BCL_ID NUMBER ;
3818 l_Currency VARCHAR2(15);
3819
3820
3821 BEGIN
3822 OPEN L_GET_OKS_LINES_CSR(P_LINE_ID ) ;
3823 FETCH L_GET_OKS_LINES_CSR INTO L_GET_OKS_LINES_REC ;
3824 CLOSE L_GET_OKS_LINES_CSR ;
3825
3826 OPEN L_GET_BCL_LINES_CSR(P_LINE_ID ) ;
3827 FETCH L_GET_BCL_LINES_CSR INTO L_GET_BCL_LINES_REC ;
3828 IF L_GET_BCL_LINES_CSR%FOUND THEN
3829 L_BCL_EXISTS := TRUE;
3830 IF ( L_GET_OKS_LINES_REC.LINE_START_DATE <> L_GET_BCL_LINES_REC.DATE_BILLED_FROM OR
3831 L_GET_OKS_LINES_REC.LINE_END_DATE <> L_GET_BCL_LINES_REC.DATE_BILLED_TO ) THEN
3832 L_BCL_DATES_UPDATE := TRUE ;
3833 ELSE
3834 L_BCL_DATES_UPDATE := FALSE ;
3835 END IF ;
3836 ELSE
3837 L_BCL_EXISTS := FALSE;
3838 END IF ;
3839 CLOSE L_GET_BCL_LINES_CSR ;
3840
3841
3842
3843 --THIS WILL CREATE BCL ENTRY IF ONE DOSENT EXISTS ..
3844 IF NOT L_BCL_EXISTS THEN
3845 OPEN l_hdr_csr(L_GET_OKS_LINES_REC.dnz_chr_id);
3846 FETCH l_hdr_csr INTO l_Currency;
3847
3848 IF l_hdr_csr%NOTFOUND THEN
3849 l_Currency := NULL;
3850 END IF;
3851 CLOSE l_hdr_csr;
3852
3853
3854 L_BCLV_REC_IN.CLE_ID := L_GET_OKS_LINES_REC.ID;
3855 L_BCLV_REC_IN.DATE_BILLED_FROM := L_GET_OKS_LINES_REC.LINE_START_DATE;
3856 L_BCLV_REC_IN.DATE_BILLED_TO := L_GET_OKS_LINES_REC.LINE_END_DATE ;
3857 L_BCLV_REC_IN.DATE_NEXT_INVOICE := NULL;
3858 L_BCLV_REC_IN.BILL_ACTION := 'RI';
3859 L_BCLV_REC_IN.SENT_YN := 'N';
3860 L_BCLV_REC_IN.BTN_ID := -44;
3861 L_BCLV_REC_IN.currency_code := l_Currency;
3862
3863 OKS_BILLCONTLINE_PUB.INSERT_BILL_CONT_LINE(
3864 P_API_VERSION => 1.0,
3865 P_INIT_MSG_LIST => 'T',
3866 X_RETURN_STATUS => L_RETURN_STATUS,
3867 X_MSG_COUNT => L_MSG_CNT,
3868 X_MSG_DATA => L_MSG_DATA,
3869 P_BCLV_REC => L_BCLV_REC_IN,
3870 X_BCLV_REC => L_BCLV_REC_OUT
3871 );
3872
3873 IF NOT L_RETURN_STATUS = OKC_API.G_RET_STS_SUCCESS THEN
3874 X_RETURN_STATUS := L_RETURN_STATUS;
3875 RAISE G_EXCEPTION_HALT_VALIDATION;
3876 END IF;
3877 END IF ;
3878
3879
3880 IF L_BCL_EXISTS THEN
3881 L_BCL_ID := L_GET_BCL_LINES_REC.ID ;
3882 ELSE
3883 L_BCL_ID := L_BCLV_REC_OUT.ID ;
3884 END IF ;
3885
3886
3887 IF L_RETURN_STATUS = OKC_API.G_RET_STS_SUCCESS THEN
3888 L_LINE_ID := P_LINE_ID ;
3889 CREATE_BSL_FOR_OM( P_LINE_ID => L_LINE_ID ,
3890 P_BCL_ID => L_BCL_ID ,
3891 X_RETURN_STATUS => L_RETURN_STATUS,
3892 X_SUB_LINES_INSERTED => L_SUB_LINES_INSERTED,
3893 X_TOTAL_AMOUNT => L_TOTAL_AMOUNT) ;
3894 IF NOT L_RETURN_STATUS = OKC_API.G_RET_STS_SUCCESS THEN
3895 RAISE G_EXCEPTION_HALT_VALIDATION;
3896 END IF ;
3897 END IF ;
3898
3899
3900 --THIS WILL UPDATE THE BCL LINE ENTRY IF THE DATES DIFFER FROM THAT OF THE TOP LINE DATES..
3901 --OR FOR THE UPDATE OF AMOUNT IN BCL ..
3902
3903 L_GET_BCL_LINES_REC.BCL_AMOUNT := NVL(L_GET_BCL_LINES_REC.BCL_AMOUNT , 0 ) ;
3904 L_TOTAL_AMOUNT := NVL(L_TOTAL_AMOUNT , 0 );
3905
3906 IF L_SUB_LINES_INSERTED > 0 OR L_BCL_DATES_UPDATE THEN
3907 IF L_SUB_LINES_INSERTED > 0 THEN
3908 L_BCLV_REC_UPD_IN.ID := L_BCL_ID;
3909 L_BCLV_REC_UPD_IN.AMOUNT := L_TOTAL_AMOUNT + L_GET_BCL_LINES_REC.BCL_AMOUNT ;
3910 L_TOTAL_AMOUNT := 0 ;
3911 END IF ;
3912 IF L_BCL_DATES_UPDATE THEN
3913 L_BCLV_REC_UPD_IN.DATE_BILLED_FROM :=L_GET_OKS_LINES_REC.LINE_START_DATE;
3914 L_BCLV_REC_UPD_IN.DATE_BILLED_TO :=L_GET_OKS_LINES_REC.LINE_END_DATE ;
3915 END IF ;
3916
3917
3918 OKS_BILLCONTLINE_PUB.UPDATE_BILL_CONT_LINE
3919 (
3920 P_API_VERSION => 1.0,
3921 P_INIT_MSG_LIST => 'T',
3922 X_RETURN_STATUS => L_RETURN_STATUS,
3923 X_MSG_COUNT => L_MSG_CNT,
3924 X_MSG_DATA => L_MSG_DATA,
3925 P_BCLV_REC => L_BCLV_REC_UPD_IN,
3926 X_BCLV_REC => L_BCLV_REC_UPD_OUT
3927 );
3928 IF NOT L_RETURN_STATUS = OKC_API.G_RET_STS_SUCCESS THEN
3929 X_RETURN_STATUS := L_RETURN_STATUS;
3930 RAISE G_EXCEPTION_HALT_VALIDATION;
3931 END IF;
3932 END IF ;
3933
3934 IF L_RETURN_STATUS = OKC_API.G_RET_STS_SUCCESS THEN
3935 UPDATE_OKS_LEVEL_ELEMENTS(L_GET_OKS_LINES_REC.id ,
3936 X_RETURN_STATUS );
3937 IF (L_RETURN_STATUS <> 'S') THEN
3938 X_RETURN_STATUS := L_RETURN_STATUS;
3939 RAISE G_EXCEPTION_HALT_VALIDATION;
3940 END IF;
3941 END IF ;
3942
3943 X_RETURN_STATUS := L_RETURN_STATUS;
3944
3945 EXCEPTION
3946 WHEN G_EXCEPTION_HALT_VALIDATION THEN
3947 X_RETURN_STATUS := l_return_status ;
3948 When Others Then
3949 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
3950 OKC_API.set_message(G_APP_NAME, G_UNEXPECTED_ERROR,G_SQLCODE_TOKEN,SQLCODE,G_SQLERRM_TOKEN,SQLERRM);
3951
3952 END CREATE_BCL_FOR_OM ;
3953
3954
3955 PROCEDURE CREATE_BSL_FOR_OM ( P_LINE_ID IN NUMBER ,
3956 P_BCL_ID IN NUMBER ,
3957 X_RETURN_STATUS OUT NOCOPY VARCHAR2 ,
3958 X_SUB_LINES_INSERTED OUT NOCOPY NUMBER ,
3959 X_TOTAL_AMOUNT OUT NOCOPY NUMBER ) IS
3960
3961 CURSOR L_GET_COVERED_LEVELS_CSR (P_LINE_ID NUMBER ) IS
3962 SELECT LINES.ID ,
3963 LINES.START_DATE COVERED_LEVEL_START_DATE,
3964 LINES.END_DATE COVERED_LEVEL_END_DATE,
3965 LINES.PRICE_NEGOTIATED
3966 FROM OKC_K_LINES_B LINES
3967 WHERE LINES.CLE_ID = P_LINE_ID
3968 AND LINES.LSE_ID in (9, 25) ;
3969
3970 CURSOR l_get_itm_uom_csr(p_cp_id NUMBER) IS
3971 SELECT uom_code
3972 FROM okc_k_items
3973 WHERE cle_id = p_cp_id;
3974
3975 CURSOR L_GET_BSL_CSR ( P_ID NUMBER ) IS
3976 SELECT 1
3977 FROM OKS_BILL_SUB_LINES
3978 WHERE CLE_ID = P_ID ;
3979
3980 l_get_itm_uom_rec l_get_itm_uom_csr%ROWTYPE;
3981 L_GET_COVERED_LEVELS_REC L_GET_COVERED_LEVELS_CSR%ROWTYPE ;
3982 L_GET_BSL_REC L_GET_BSL_CSR%ROWTYPE ;
3983
3984 SUBTYPE BSLV_REC_TYPE IS OKS_BILLSUBLINE_PVT.BSLV_REC_TYPE;
3985 L_BSLV_REC_IN BSLV_REC_TYPE ;
3986 L_BSLV_REC_OUT BSLV_REC_TYPE;
3987
3988 ---for bill_sub_line_dtl
3989 SUBTYPE bsdv_rec_type IS OKS_BSL_DET_PVT.bsdv_rec_type;
3990 l_bsdv_rec_in bsdv_rec_type;
3991 l_bsdv_rec_out bsdv_rec_type;
3992
3993 L_RETURN_STATUS VARCHAR2(4);
3994 L_MSG_CNT NUMBER ;
3995 L_MSG_DATA VARCHAR2(2000) ;
3996 L_SUB_LINES_INSERTED NUMBER := 0;
3997 L_TOTAL_AMOUNT NUMBER := 0 ;
3998
3999
4000
4001 BEGIN
4002 X_RETURN_STATUS := OKC_API.G_RET_STS_SUCCESS ;
4003 FOR L_GET_COVERED_LEVELS_REC IN L_GET_COVERED_LEVELS_CSR(P_LINE_ID )
4004 LOOP
4005 OPEN L_GET_BSL_CSR( L_GET_COVERED_LEVELS_REC.ID );
4006 FETCH L_GET_BSL_CSR INTO L_GET_BSL_REC ;
4007 IF L_GET_BSL_CSR%NOTFOUND THEN
4008 L_BSLV_REC_IN.CLE_ID := L_GET_COVERED_LEVELS_REC.ID;
4009 L_BSLV_REC_IN.BCL_ID := P_BCL_ID;
4010 L_BSLV_REC_IN.DATE_BILLED_FROM := L_GET_COVERED_LEVELS_REC.COVERED_LEVEL_START_DATE;
4011 L_BSLV_REC_IN.DATE_BILLED_TO := L_GET_COVERED_LEVELS_REC.COVERED_LEVEL_END_DATE;
4012 L_BSLV_REC_IN.AMOUNT := L_GET_COVERED_LEVELS_REC.PRICE_NEGOTIATED;
4013 L_BSLV_REC_IN.AVERAGE := 0;
4014 ------------------------------------------------------------------------------
4015 --HERE ITS CALLS THE PROCEDURE TO INSERT LINES INTO OKS_BILL_SUB_LINES.
4016 ------------------------------------------------------------------------------
4017 OKS_BILLSUBLINE_PUB.INSERT_BILL_SUBLINE_PUB
4018 (
4019 P_API_VERSION => 1.0,
4020 P_INIT_MSG_LIST => 'T',
4021 X_RETURN_STATUS => L_RETURN_STATUS,
4022 X_MSG_COUNT => L_MSG_CNT,
4023 X_MSG_DATA => L_MSG_DATA,
4024 P_BSLV_REC => L_BSLV_REC_IN,
4025 X_BSLV_REC => L_BSLV_REC_OUT
4026 );
4027
4028 IF NOT L_RETURN_STATUS = OKC_API.G_RET_STS_SUCCESS THEN
4029 RAISE G_EXCEPTION_HALT_VALIDATION;
4030 ELSE
4031 L_SUB_LINES_INSERTED := L_SUB_LINES_INSERTED + 1 ;
4032 L_TOTAL_AMOUNT := L_TOTAL_AMOUNT + L_GET_COVERED_LEVELS_REC.PRICE_NEGOTIATED ;
4033 END IF;
4034
4035 ---create rec in oks_subline_bill_dtl table.
4036
4037
4038 ----Get the item uom from okc_k_items
4039 OPEN L_GET_ITM_UOM_CSR( L_GET_COVERED_LEVELS_REC.ID );
4040 FETCH L_GET_ITM_UOM_CSR INTO L_GET_ITM_UOM_rec ;
4041 IF L_GET_ITM_UOM_CSR%NOTFOUND THEN
4042 CLOSE L_GET_ITM_UOM_CSR;
4043 l_return_status := 'E' ;
4044 RAISE G_EXCEPTION_HALT_VALIDATION;
4045 ELSE
4046 CLOSE L_GET_ITM_UOM_CSR;
4047 END IF; --chk for rec found
4048
4049 l_bsdv_rec_in.bsl_id := L_BSLV_REC_OUT.id;
4050 l_bsdv_rec_in.amount := L_BSLV_REC_IN.amount;
4051 l_bsdv_rec_in.unit_of_measure := L_GET_ITM_UOM_rec.uom_code;
4052 l_bsdv_rec_in.amcv_yn := 'N';
4053 l_bsdv_rec_in.result := 1;
4054
4055
4056 OKS_BSL_det_PUB.insert_bsl_det_Pub
4057 (
4058 P_API_VERSION => 1.0,
4059 P_INIT_MSG_LIST => 'T',
4060 X_RETURN_STATUS => l_RETURN_STATUS,
4061 X_MSG_COUNT => l_msg_cnt,
4062 X_MSG_DATA => l_msg_data,
4063 p_bsdv_rec => l_bsdv_rec_in,
4064 x_bsdv_rec => l_bsdv_rec_out);
4065
4066 IF NOT l_RETURN_STATUS = OKC_API.G_RET_STS_SUCCESS THEN
4067 RAISE G_EXCEPTION_HALT_VALIDATION;
4068 END IF; --chk for status
4069
4070
4071 END IF ;
4072 CLOSE L_GET_BSL_CSR ;
4073 END LOOP ;
4074 X_TOTAL_AMOUNT := L_TOTAL_AMOUNT ;
4075 X_SUB_LINES_INSERTED := L_SUB_LINES_INSERTED ;
4076
4077
4078
4079 EXCEPTION
4080 WHEN G_EXCEPTION_HALT_VALIDATION THEN
4081 X_RETURN_STATUS := L_RETURN_STATUS ;
4082 When Others Then
4083 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
4084 OKC_API.set_message(G_APP_NAME, G_UNEXPECTED_ERROR,G_SQLCODE_TOKEN,SQLCODE,G_SQLERRM_TOKEN,SQLERRM);
4085
4086 END CREATE_BSL_FOR_OM ;
4087
4088 ---This will give the billed qty for subcription line
4089
4090 Function Get_Billed_Qty ( p_line_id IN Number,
4091 x_return_status OUT NOCOPY VARCHAR2)
4092 return Number
4093
4094 IS
4095
4096 l_tot_amt NUMBER;
4097 l_billed_amt NUMBER;
4098 l_tot_qty Number;
4099 l_billed_qty NUMBER;
4100
4101
4102 CURSOR l_line_amt_csr IS
4103 SELECT nvl(price_negotiated,0) amt
4104 FROM okc_k_lines_b
4105 WHERE id = p_line_id ;
4106
4107 CURSOR l_billed_amt_csr IS
4108 SELECT nvl(SUM(AMOUNT),0) tot_amt
4109 FROM OKS_BILL_CONT_LINES
4110 WHERE cle_id = p_line_id ;
4111
4112 CURSOR l_tot_Qty_csr IS
4113 SELECT SUM(QUANTITY) qty
4114 FROM OKS_SUBSCR_ELEMENTS
4115 WHERE dnz_cle_id = p_line_id ;
4116
4117 CURSOR l_subscription_type_csr IS
4118 SELECT item_type
4119 FROM OKS_SUBSCR_HEADER_B
4120 WHERE cle_id = p_line_id;
4121
4122 l_line_amt_rec l_line_amt_csr%ROWTYPE;
4123 l_billed_amt_rec l_billed_amt_csr%ROWTYPE;
4124 l_tot_Qty_rec l_tot_Qty_csr%ROWTYPE;
4125 l_subscription_type_rec l_subscription_type_csr%ROWTYPE;
4126 l_sub_item_type VARCHAR2(10);
4127
4128
4129 BEGIN
4130
4131 x_return_status := OKC_API.G_RET_STS_SUCCESS;
4132
4133 Open l_subscription_type_csr;
4134 Fetch l_subscription_type_csr Into l_subscription_type_rec;
4135
4136 If l_subscription_type_csr%Notfound then
4137 Close l_subscription_type_csr;
4138 l_sub_item_type := 'NA';
4139 End If;
4140 Close l_subscription_type_csr;
4141
4142 l_sub_item_type := l_subscription_type_rec.item_type;
4143
4144 --if item_type <> 'ST' , total qty can not be found.
4145 --for this release it will return null.
4146
4147 IF nvl(l_sub_item_type,'NA') <> 'ST' THEN
4148 RETURN NULL ;
4149 END IF;
4150
4151
4152 Open l_line_amt_csr;
4153 Fetch l_line_amt_csr Into l_line_amt_rec;
4154
4155 If l_line_amt_csr%Notfound then
4156 Close l_line_amt_csr;
4157 x_return_status := 'E';
4158 RAISE G_EXCEPTION_HALT_VALIDATION;
4159 end if;
4160 l_tot_amt := l_line_amt_rec.amt;
4161 Close l_line_amt_csr;
4162
4163 ---ERROROUT_AD('l_tot_amt = '|| l_tot_amt);
4164
4165 Open l_billed_amt_csr;
4166 Fetch l_billed_amt_csr Into l_billed_amt_rec;
4167
4168 If l_billed_amt_csr%Notfound then
4169 Close l_billed_amt_csr;
4170 x_return_status := 'E';
4171 RAISE G_EXCEPTION_HALT_VALIDATION;
4172 end if;
4173
4174 l_billed_amt := l_billed_amt_rec.tot_amt;
4175 Close l_billed_amt_csr;
4176 ---ERROROUT_AD('l_billed_amt = '|| l_billed_amt);
4177
4178 Open l_tot_Qty_csr;
4179 Fetch l_tot_Qty_csr Into l_tot_Qty_rec;
4180
4181 If l_tot_Qty_csr%Notfound then
4182 Close l_tot_Qty_csr;
4183 x_return_status := 'E';
4184 RAISE G_EXCEPTION_HALT_VALIDATION;
4185 end if;
4186 l_tot_Qty := l_tot_Qty_REC.qty;
4187 Close l_tot_Qty_csr;
4188 ---ERROROUT_AD('l_tot_Qty = '|| l_tot_Qty);
4189
4190 IF nvl(l_tot_amt,0) = 0 THEN
4191 l_billed_qty := 0;
4192 ELSE
4193 l_billed_qty := ( nvl(l_tot_qty,0) * nvl(l_billed_amt,0) )/ l_tot_amt ;
4194 END IF;
4195
4196 RETURN l_billed_qty;
4197
4198
4199 EXCEPTION
4200 WHEN G_EXCEPTION_HALT_VALIDATION THEN
4201 RETURN NULL;
4202
4203 WHEN OTHERS THEN
4204 OKC_API.set_message(G_APP_NAME, G_UNEXPECTED_ERROR,G_SQLCODE_TOKEN,SQLCODE,G_SQLERRM_TOKEN,SQLERRM);
4205 RETURN NULL;
4206 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
4207
4208 END Get_Billed_Qty;
4209
4210 Function Get_Billed_Upto ( p_id IN Number,
4211 p_level IN Varchar2
4212 ) Return Date IS
4213 Cursor l_hdr_bill_cont_lines_csr IS
4214 Select Trunc(Max(date_billed_to))
4215 From oks_bill_cont_lines
4216 Where cle_id In
4217 ( Select id
4218 From okc_k_lines_b
4219 Where dnz_chr_id = p_id
4220 And lse_id In(1,12,14,19,46)
4221 );
4222
4223 Cursor l_bill_cont_line_csr Is
4224 Select Trunc(Max(date_billed_to))
4225 From oks_bill_cont_lines
4226 Where cle_id = p_id;
4227
4228 Cursor l_bill_sub_line_csr IS
4229 Select Trunc(Max(date_billed_to))
4230 From oks_bill_sub_lines
4231 Where cle_id = p_id;
4232
4233 l_billed_upto Date := Null;
4234 Begin
4235 IF p_level = 'H' THEN -- HEADER
4236 OPEN l_hdr_bill_cont_lines_csr;
4237 FETCH l_hdr_bill_cont_lines_csr INTO l_billed_upto;
4238 CLOSE l_hdr_bill_cont_lines_csr;
4239 ELSIF p_level = 'T' THEN -- TOP LINE
4240 OPEN l_bill_cont_line_csr;
4241 FETCH l_bill_cont_line_csr INTO l_billed_upto;
4242 CLOSE l_bill_cont_line_csr;
4243 ELSIF p_level = 'S' THEN -- SUB LINE
4244 OPEN l_bill_sub_line_csr;
4245 FETCH l_bill_sub_line_csr INTO l_billed_upto;
4246 CLOSE l_bill_sub_line_csr;
4247 END IF;
4248 return l_billed_upto;
4249 Exception
4250 WHEN OTHERS THEN
4251 Return Null;
4252 End Get_Billed_Upto;
4253
4254
4255
4256 FUNCTION Is_Sc_Allowed (p_org_id Number) RETURN BOOLEAN
4257
4258 IS
4259
4260 CURSOR l_sc_csr IS
4261 select nvl(allow_sales_credit_flag,'N') sc_flag
4262 FROM ra_batch_sources_All
4263 WHERE name = 'OKS_CONTRACTS'
4264 AND org_id = p_org_id;
4265
4266 l_allowed_flag BOOLEAN := FALSE;
4267 l_sc_rec l_sc_csr%ROWTYPE;
4268
4269 BEGIN
4270
4271 OPEN l_sc_csr;
4272 FETCH l_sc_csr INTO l_sc_rec;
4273
4274 IF l_sc_csr%NOTFOUND THEN
4275 l_allowed_flag := FALSE;
4276 ELSE
4277 IF l_sc_rec.sc_flag = 'Y' THEN
4278 l_allowed_flag := TRUE;
4279 ELSE
4280 l_allowed_flag := FALSE;
4281 END IF;
4282 END IF;
4283 CLOSE l_sc_csr;
4284
4285 RETURN l_allowed_flag;
4286
4287
4288 EXCEPTION
4289 WHEN OTHERS THEN
4290 RETURN FALSE;
4291 END Is_Sc_Allowed;
4292
4293
4294 Function IS_Contract_billed (
4295 p_header_id IN Number,
4296 x_return_status OUT NOCOPY VARCHAR2)
4297 return Boolean
4298
4299 IS
4300
4301 CURSOR l_billed_rec_csr IS
4302 SELECT count(id)
4303 FROM oks_level_elements
4304 WHERE dnz_chr_id = p_header_id
4305 AND date_completed IS NOT NULL;
4306
4307 l_billed_count NUMBER;
4308
4309 BEGIN
4310
4311 x_return_status := 'S';
4312
4313 OPEN l_billed_rec_csr;
4314 FETCH l_billed_rec_csr INTO l_billed_count;
4315
4316 IF l_billed_count = 0 THEN
4317 RETURN FALSE;
4318 ELSE
4319 RETURN TRUE;
4320 END IF;
4321 CLOSE l_billed_rec_csr;
4322
4323
4324
4325 EXCEPTION
4326 WHEN OTHERS THEN
4327 x_return_status := 'E';
4328 RETURN FALSE;
4329 END Is_Contract_billed;
4330
4331
4332 PROCEDURE ADJUST_SPLIT_BILL_REC(p_old_cp_id IN NUMBER,
4333 p_new_cp_id IN NUMBER,
4334 p_rgp_id IN NUMBER,
4335 p_currency_code IN VARCHAR2,
4336 p_old_cp_lvl_tbl IN oks_bill_level_elements_pvt.letv_tbl_type,
4337 p_new_cp_lvl_tbl IN oks_bill_level_elements_pvt.letv_tbl_type,
4338 x_return_status OUT NOCOPY VARCHAR2,
4339 x_msg_count OUT NOCOPY NUMBER,
4340 x_msg_data OUT NOCOPY VARCHAR2)
4341
4342 IS
4343
4344 CURSOR l_bsl_csr IS
4345 SELECT id, cle_id, date_billed_from, date_billed_to,
4346 bcl_id, amount, average, date_to_interface,
4347 attribute_category,attribute1,attribute2,attribute3,attribute4 ,
4348 attribute5,attribute6,attribute7,attribute8,attribute9,
4349 attribute10,attribute11,attribute12,attribute13,attribute14,attribute15
4350 FROM oks_bill_sub_lines
4351 WHERE cle_id = p_old_cp_id
4352 ORDER BY date_billed_from;
4353
4354 CURSOR l_bsd_csr (p_bsl_id NUMBER)IS
4355
4356 SELECT id, bsl_id, bsl_id_averaged, bsd_id, bsd_id_applied,
4357 unit_of_measure , amcv_yn, result, amount, fixed, actual,
4358 default_default , adjustment_level ,adjustment_minimum,
4359 start_reading, end_reading,ccr_id,cgr_id,
4360 attribute_category,attribute1,attribute2,attribute3,attribute4 ,
4361 attribute5,attribute6,attribute7,attribute8,attribute9,
4362 attribute10,attribute11,attribute12,attribute13,attribute14,attribute15
4363 FROM oks_bill_sub_line_dtls
4364 WHERE bsl_id = p_bsl_id;
4365
4366 CURSOR l_btl_csr(p_bcl_id NUMBER, p_old_bsl_id number) IS
4367 SELECT id ,btn_id, bsl_id,bcl_id,
4368 bill_instance_number, trx_line_tax_amount,
4369 trx_date, trx_number, trx_class, split_flag,
4370 attribute_category,attribute1,attribute2,attribute3,attribute4,
4371 attribute5,attribute6,attribute7,attribute8,attribute9,
4372 attribute10,attribute11,attribute12,attribute13,attribute14,attribute15,
4373 trx_amount,cycle_refrence
4374 FROM oks_bill_txn_lines
4375 WHERE bcl_id = p_bcl_id
4376 AND bsl_id = p_old_bsl_id;
4377
4378
4379 l_bsl_rec l_bsl_csr%ROWTYPE;
4380 l_bsd_rec l_bsd_csr%ROWTYPE;
4381 l_btl_rec l_btl_csr%ROWTYPE;
4382
4383 SUBTYPE BSLV_REC_TYPE IS OKS_BILLSUBLINE_PVT.BSLV_REC_TYPE;
4384 L_BSLV_REC_IN BSLV_REC_TYPE ;
4385 L_BSLV_REC_OUT BSLV_REC_TYPE;
4386
4387
4388 SUBTYPE l_bsdv_tbl_type_in is OKS_bsd_PVT.bsdv_tbl_type;
4389 l_bsdv_tbl_in l_bsdv_tbl_type_in;
4390 l_bsdv_tbl_out l_bsdv_tbl_type_in;
4391
4392 l_btlv_tbl_in OKS_BTL_PVT.btlv_tbl_type;
4393 l_btlv_tbl_out OKS_BTL_PVT.btlv_tbl_type;
4394
4395 l_index NUMBER;
4396 l_max_billed_dt DATE;
4397 l_tot_amt NUMBER;
4398 l_tot_tax NUMBER;
4399
4400 BEGIN
4401
4402 /****This will add bsl,bsd for newly created cp after split and adjust the amount bsl, bsd for old cp.
4403 ----it will also add rec in btl with same bill_instance_number for new cp (for detail billing) and adjust
4404 ----btl amt for old cp********/
4405
4406 x_return_status := 'S';
4407
4408 l_index := p_new_cp_lvl_tbl.FIRST;
4409
4410 FOR l_bsl_rec IN l_bsl_csr
4411 LOOP
4412
4413 l_max_billed_dt := l_bsl_rec.DATE_BILLED_TO;
4414
4415 IF TRUNC(l_bsl_rec.date_billed_from) = TRUNC(p_new_cp_lvl_tbl(l_index).date_start ) THEN
4416 -----Add record for new line in bsl
4417
4418 L_BSLV_REC_IN.cle_id := p_new_cp_id;
4419 L_BSLV_REC_IN.bcl_id := l_bsl_rec.bcl_id;
4420 L_BSLV_REC_IN.date_billed_from := l_bsl_rec.date_billed_from;
4421 L_BSLV_REC_IN.date_billed_to := l_bsl_rec.date_billed_to;
4422 L_BSLV_REC_IN.amount := p_new_cp_lvl_tbl(l_index).amount;
4423 L_BSLV_REC_IN.average := l_bsl_rec.average;
4424 L_BSLV_REC_IN.date_to_interface := l_bsl_rec.date_to_interface;
4425 L_BSLV_REC_IN.attribute_category := l_bsl_rec.attribute_category;
4426 L_BSLV_REC_IN.attribute1 := l_bsl_rec.attribute1;
4427 L_BSLV_REC_IN.attribute2 := l_bsl_rec.attribute2;
4428 L_BSLV_REC_IN.attribute3 := l_bsl_rec.attribute3;
4429 L_BSLV_REC_IN.attribute4 := l_bsl_rec.attribute4;
4430 L_BSLV_REC_IN.attribute5 := l_bsl_rec.attribute5;
4431 L_BSLV_REC_IN.attribute6 := l_bsl_rec.attribute6;
4432 L_BSLV_REC_IN.attribute7 := l_bsl_rec.attribute7;
4433 L_BSLV_REC_IN.attribute8 := l_bsl_rec.attribute8;
4434 L_BSLV_REC_IN.attribute9 := l_bsl_rec.attribute9;
4435 L_BSLV_REC_IN.attribute10 := l_bsl_rec.attribute10;
4436 L_BSLV_REC_IN.attribute11 := l_bsl_rec.attribute11;
4437 L_BSLV_REC_IN.attribute12 := l_bsl_rec.attribute12;
4438 L_BSLV_REC_IN.attribute13 := l_bsl_rec.attribute13;
4439 L_BSLV_REC_IN.attribute14 := l_bsl_rec.attribute14;
4440 L_BSLV_REC_IN.attribute15 := l_bsl_rec.attribute15;
4441
4442
4443 OKS_BILLSUBLINE_PUB.INSERT_BILL_SUBLINE_PUB
4444 (
4445 P_API_VERSION => 1.0,
4446 P_INIT_MSG_LIST => 'T',
4447 X_RETURN_STATUS => X_RETURN_STATUS,
4448 X_MSG_COUNT => X_MSG_COUNT,
4449 X_MSG_DATA => X_MSG_DATA,
4450 P_BSLV_REC => L_BSLV_REC_IN,
4451 X_BSLV_REC => L_BSLV_REC_OUT
4452 );
4453
4454
4455
4456 IF fnd_log.level_event >= fnd_log.g_current_runtime_level THEN
4457 fnd_log.string(fnd_log.level_event,G_MODULE_CURRENT||'.ADJUST_SPLIT_BILL_REC.create_bsl',
4458 'oks_billsubline_pub.insert_bill_subline_pub(x_return_status = '||x_return_status
4459 ||', bsl id = '|| L_BSLV_REC_OUT.id ||')');
4460 END IF;
4461
4462 IF NVL(x_return_status,'!') <> OKC_API.G_RET_STS_SUCCESS THEN
4463 RAISE G_EXCEPTION_HALT_VALIDATION;
4464 END IF;
4465
4466 ---update old bsl record
4467 UPDATE oks_bill_sub_lines
4468 SET amount = nvl(amount,0) - nvl(p_new_cp_lvl_tbl(l_index).amount, 0)
4469 WHERE id = l_bsl_rec.id;
4470
4471 IF fnd_log.level_event >= fnd_log.g_current_runtime_level THEN
4472 fnd_log.string(fnd_log.level_event,G_MODULE_CURRENT||'.ADJUST_SPLIT_BILL_REC.update_bsl',
4473 'update_old_bsl_amt id = ' || l_bsl_rec.id
4474 );
4475 END IF;
4476
4477
4478 l_bsdv_tbl_in.DELETE;
4479 OPEN l_bsd_csr(l_bsl_rec.ID);
4480 FETCH l_bsd_csr INTO l_bsd_rec;
4481 IF l_bsd_csr%FOUND THEN
4482
4483 ------ADD bsd for new cp
4484
4485
4486 l_bsdv_tbl_in(1).bsl_id := L_BSLV_REC_OUT.id;
4487 l_bsdv_tbl_in(1).bsl_id_averaged := l_bsd_rec.bsl_id_averaged;
4488 l_bsdv_tbl_in(1).bsd_id := l_bsd_rec.bsd_id;
4489 l_bsdv_tbl_in(1).bsd_id_applied := l_bsd_rec.bsd_id_applied;
4490 l_bsdv_tbl_in(1).unit_of_measure := l_bsd_rec.unit_of_measure;
4491 l_bsdv_tbl_in(1).fixed := l_bsd_rec.fixed;
4492 l_bsdv_tbl_in(1).actual := l_bsd_rec.actual;
4493 l_bsdv_tbl_in(1).default_default := l_bsd_rec.default_default;
4494 l_bsdv_tbl_in(1).amcv_yn := l_bsd_rec.amcv_yn;
4495 l_bsdv_tbl_in(1).adjustment_level := l_bsd_rec.adjustment_level;
4496 l_bsdv_tbl_in(1).adjustment_minimum := l_bsd_rec.adjustment_minimum;
4497 l_bsdv_tbl_in(1).result := l_bsd_rec.result;
4498 l_bsdv_tbl_in(1).attribute_category := l_bsd_rec.attribute_category;
4499 l_bsdv_tbl_in(1).attribute1 := l_bsd_rec.attribute1;
4500 l_bsdv_tbl_in(1).attribute2 := l_bsd_rec.attribute2;
4501 l_bsdv_tbl_in(1).attribute3 := l_bsd_rec.attribute3;
4502 l_bsdv_tbl_in(1).attribute4 := l_bsd_rec.attribute4;
4503 l_bsdv_tbl_in(1).attribute5 := l_bsd_rec.attribute5;
4504 l_bsdv_tbl_in(1).attribute6 := l_bsd_rec.attribute6;
4505 l_bsdv_tbl_in(1).attribute7 := l_bsd_rec.attribute7;
4506 l_bsdv_tbl_in(1).attribute8 := l_bsd_rec.attribute8;
4507 l_bsdv_tbl_in(1).attribute9 := l_bsd_rec.attribute9;
4508 l_bsdv_tbl_in(1).attribute10 := l_bsd_rec.attribute10;
4509 l_bsdv_tbl_in(1).attribute11 := l_bsd_rec.attribute11;
4510 l_bsdv_tbl_in(1).attribute12 := l_bsd_rec.attribute12;
4511 l_bsdv_tbl_in(1).attribute13 := l_bsd_rec.attribute13;
4512 l_bsdv_tbl_in(1).attribute14 := l_bsd_rec.attribute14;
4513 l_bsdv_tbl_in(1).attribute15 := l_bsd_rec.attribute15;
4514 l_bsdv_tbl_in(1).start_reading := l_bsd_rec.start_reading;
4515 l_bsdv_tbl_in(1).end_reading := l_bsd_rec.end_reading;
4516 l_bsdv_tbl_in(1).ccr_id := l_bsd_rec.ccr_id;
4517 l_bsdv_tbl_in(1).cgr_id := l_bsd_rec.cgr_id;
4518 l_bsdv_tbl_in(1).amount := p_new_cp_lvl_tbl(l_index).amount;
4519
4520 OKS_BSL_det_PUB.insert_bsl_det_Pub
4521 (
4522 p_api_version => 1.0,
4523 p_init_msg_list => 'T',
4524 x_return_status => x_return_status,
4525 x_msg_count => x_msg_count,
4526 x_msg_data => x_msg_data,
4527 p_bsdv_tbl => l_bsdv_tbl_in,
4528 x_bsdv_tbl => l_bsdv_tbl_out
4529 );
4530
4531 IF fnd_log.level_event >= fnd_log.g_current_runtime_level THEN
4532 fnd_log.string(fnd_log.level_event,G_MODULE_CURRENT||'.ADJUST_SPLIT_BILL_REC.create_bsd',
4533 'OKS_BSL_det_PUB.insert_bsl_det_Pub(x_return_status = '||x_return_status
4534 ||', bsd id = '|| l_bsdv_tbl_out(1).id ||')');
4535 END IF;
4536
4537 IF NVL(x_return_status,'!') <> OKC_API.G_RET_STS_SUCCESS THEN
4538 RAISE G_EXCEPTION_HALT_VALIDATION;
4539 END IF;
4540
4541 -------update old bsd amount
4542 UPDATE oks_bill_sub_line_dtls
4543 set amount = nvl(amount,0) - p_new_cp_lvl_tbl(l_index).amount
4544 where id = l_bsd_rec.id;
4545
4546 IF fnd_log.level_event >= fnd_log.g_current_runtime_level THEN
4547 fnd_log.string(fnd_log.level_event,G_MODULE_CURRENT||'.ADJUST_SPLIT_BILL_REC.update_bsl',
4548 'update_old_bsd_amt id = ' || l_bsd_rec.id
4549 );
4550 END IF;
4551
4552 END IF; ------end of bsd csr found.
4553 CLOSE l_bsd_csr;
4554
4555 l_btlv_tbl_in.DELETE;
4556
4557 OPEN l_btl_csr(l_bsl_rec.BCL_ID, l_bsl_rec.ID);
4558 FETCH l_btl_csr INTO l_btl_rec;
4559 IF l_btl_csr%FOUND THEN
4560
4561 l_tot_amt := nvl(p_new_cp_lvl_tbl(l_index).amount,0) + nvl(p_old_cp_lvl_tbl(l_index).amount,0);
4562 l_tot_tax := l_btl_rec.TRX_LINE_TAX_AMOUNT;
4563
4564
4565
4566 l_btlv_tbl_in(1).btn_id := l_btl_rec.btn_id;
4567 l_btlv_tbl_in(1).bsl_id := l_bslv_rec_out.id;
4568 l_btlv_tbl_in(1).bcl_id := l_btl_rec.bcl_id;
4569 l_btlv_tbl_in(1).bill_instance_number := l_btl_rec.bill_instance_number;
4570
4571 IF l_tot_amt = 0 THEN
4572 l_btlv_tbl_in(1).trx_amount := 0;
4573 ELSE
4574 l_btlv_tbl_in(1).trx_amount := OKS_EXTWAR_UTIL_PVT.round_currency_amt(
4575 ((nvl(l_btl_rec.trx_amount,0)/l_tot_amt ) *
4576 nvl(p_new_cp_lvl_tbl(l_index).amount,0)),
4577 p_currency_code) ;
4578 END IF;
4579
4580 l_btlv_tbl_in(1).trx_line_amount := l_btlv_tbl_in(1).trx_amount;
4581 IF l_tot_amt = 0 THEN
4582 l_btlv_tbl_in(1).trx_line_tax_amount := 0;
4583 ELSIF l_btl_rec.trx_line_tax_amount IS NULL THEN
4584 l_btlv_tbl_in(1).trx_line_tax_amount := NULL;
4585
4586 ELSE
4587 l_btlv_tbl_in(1).trx_line_tax_amount := OKS_EXTWAR_UTIL_PVT.round_currency_amt(
4588 ((nvl(l_btl_rec.trx_line_tax_amount,0)/l_tot_amt ) *
4589 nvl(p_new_cp_lvl_tbl(l_index).amount,0)),
4590 p_currency_code) ;
4591 END IF;
4592
4593
4594 l_btlv_tbl_in(1).split_flag := 'C';
4595 l_btlv_tbl_in(1).trx_number := l_btl_rec.trx_number ;
4596 l_btlv_tbl_in(1).trx_class := l_btl_rec.trx_class ;
4597 l_btlv_tbl_in(1).trx_date := l_btl_rec.trx_date ;
4598
4599 l_btlv_tbl_in(1).attribute_category := l_btl_rec.attribute_category;
4600 l_btlv_tbl_in(1).attribute1 := l_btl_rec.attribute1;
4601 l_btlv_tbl_in(1).attribute2 := l_btl_rec.attribute2;
4602 l_btlv_tbl_in(1).attribute3 := l_btl_rec.attribute3;
4603 l_btlv_tbl_in(1).attribute4 := l_btl_rec.attribute4;
4604 l_btlv_tbl_in(1).attribute5 := l_btl_rec.attribute5;
4605 l_btlv_tbl_in(1).attribute6 := l_btl_rec.attribute6;
4606 l_btlv_tbl_in(1).attribute7 := l_btl_rec.attribute7;
4607 l_btlv_tbl_in(1).attribute8 := l_btl_rec.attribute8;
4608 l_btlv_tbl_in(1).attribute9 := l_btl_rec.attribute9;
4609 l_btlv_tbl_in(1).attribute10 := l_btl_rec.attribute10;
4610 l_btlv_tbl_in(1).attribute11 := l_btl_rec.attribute11;
4611 l_btlv_tbl_in(1).attribute12 := l_btl_rec.attribute12;
4612 l_btlv_tbl_in(1).attribute13 := l_btl_rec.attribute13;
4613 l_btlv_tbl_in(1).attribute14 := l_btl_rec.attribute14;
4614 l_btlv_tbl_in(1).attribute15 := l_btl_rec.attribute15;
4615 l_btlv_tbl_in(1).cycle_refrence := l_btl_rec.cycle_refrence;
4616
4617
4618 OKS_BILLTRAN_LINE_PUB.insert_Bill_Tran_Line_Pub
4619 (
4620 p_api_version => 1.0,
4621 p_init_msg_list => 'T',
4622 x_return_status => x_return_status,
4623 x_msg_count => x_msg_count,
4624 x_msg_data => x_msg_data,
4625 p_btlv_tbl => l_btlv_tbl_in,
4626 x_btlv_tbl => l_btlv_tbl_out
4627 );
4628
4629 IF fnd_log.level_event >= fnd_log.g_current_runtime_level THEN
4630 fnd_log.string(fnd_log.level_event,G_MODULE_CURRENT||'.ADJUST_SPLIT_BILL_REC.create_btl',
4631 'OKS_BILLTRAN_LINE_PUB.insert_Bill_Tran_Line_Pub(x_return_status = '||x_return_status
4632 ||', btl id = '|| l_btlv_tbl_out(1).id ||')');
4633 END IF;
4634
4635 IF NVL(x_return_status,'!') <> OKC_API.G_RET_STS_SUCCESS THEN
4636 RAISE G_EXCEPTION_HALT_VALIDATION;
4637 END IF;
4638
4639 IF l_btl_rec.split_flag IS NULL THEN
4640
4641 UPDATE oks_bill_txn_lines
4642 SET trx_line_tax_amount = l_btl_rec.trx_line_tax_amount - l_btlv_tbl_in(1).trx_line_tax_amount,
4643 trx_amount = NVL(l_btl_rec.trx_amount,0) - NVL(l_btlv_tbl_in(1).trx_amount,0),
4644 trx_line_amount = NVL(l_btl_rec.trx_amount,0) - NVL(l_btlv_tbl_in(1).trx_amount,0),
4645 split_flag = 'P'
4646 WHERE id = l_btl_rec.id;
4647 ELSE
4648 UPDATE oks_bill_txn_lines
4649 SET trx_line_tax_amount = l_btl_rec.trx_line_tax_amount - l_btlv_tbl_in(1).trx_line_tax_amount,
4650 trx_amount = NVL(l_btl_rec.trx_amount,0) - NVL(l_btlv_tbl_in(1).trx_amount,0),
4651 trx_line_amount = NVL(l_btl_rec.trx_amount,0) - NVL(l_btlv_tbl_in(1).trx_amount,0)
4652 WHERE id = l_btl_rec.id;
4653 END IF; ----chk for split_flag IS NULL
4654
4655 IF fnd_log.level_event >= fnd_log.g_current_runtime_level THEN
4656 fnd_log.string(fnd_log.level_event,G_MODULE_CURRENT||'.ADJUST_SPLIT_BILL_REC.update_btl',
4657 'update_old_btl_amt id = ' || l_btl_rec.id
4658 );
4659 END IF;
4660
4661 END IF; ----l_btl_csr%found chk
4662 CLOSE l_btl_csr;
4663
4664 END IF; ---date start chk
4665 l_index := p_new_cp_lvl_tbl.NEXT(l_index);
4666
4667 END LOOP;
4668
4669 IF l_max_billed_dt IS NOT NULL THEN
4670 UPDATE oks_level_elements
4671 SET date_completed = SYSDATE
4672 WHERE TRUNC(date_start) <= TRUNC(l_max_billed_dt)
4673 AND cle_id =p_new_cp_id ;
4674
4675 IF fnd_log.level_event >= fnd_log.g_current_runtime_level THEN
4676 fnd_log.string(fnd_log.level_event,G_MODULE_CURRENT||'.ADJUST_SPLIT_BILL_REC.update_lvl_new',
4677 'update date_completed of new cp level elements up to date = ' || l_max_billed_dt
4678 );
4679 END IF;
4680
4681
4682 END IF;
4683
4684
4685 EXCEPTION
4686
4687 WHEN OTHERS THEN
4688 IF FND_LOG.LEVEL_UNEXPECTED >= fnd_log.g_current_runtime_level THEN
4689 fnd_log.string(fnd_log.level_unexpected,G_MODULE_CURRENT||'.Adjust_billed_lvl_element.UNEXPECTED',
4690 'sqlcode = '||sqlcode||', sqlerrm = '||sqlerrm);
4691 END IF;
4692
4693 OKC_API.SET_MESSAGE(p_app_name => G_APP_NAME,
4694 p_msg_name => G_UNEXPECTED_ERROR,
4695 p_token1 => G_SQLCODE_TOKEN,
4696 p_token1_value => sqlcode,
4697 p_token2 => G_SQLERRM_TOKEN,
4698 p_token2_value => sqlerrm);
4699
4700 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
4701
4702
4703 END ADJUST_SPLIT_BILL_REC;
4704
4705 Procedure Adjust_line_price(p_top_line_id IN NUMBER,
4706 p_sub_line_id IN NUMBER,
4707 p_end_date IN DATE,
4708 p_amount IN NUMBER,
4709 p_dnz_chr_id IN NUMBER,
4710 x_amount OUT NOCOPY NUMBER,
4711 x_return_status OUT NOCOPY VARCHAR2)
4712
4713 IS
4714
4715 CURSOR l_subline_csr IS
4716 SELECT id , TRUNC(end_date) end_dt,
4717 price_negotiated cp_amt
4718 FROM okc_k_lines_b
4719 WHERE cle_id = p_top_line_id
4720 AND lse_id in (35,7,8,9,10,11,13,18,25)
4721 AND date_terminated IS NULL;
4722
4723
4724
4725 CURSOR l_bsl_csr (p_cp_id NUMBER) IS
4726 SELECT max(bsl.date_billed_to) max_billed_to , nvl(SUM(bsl.amount),0) bill_amt
4727 FROM oks_bill_sub_lines bsl, oks_bill_cont_lines bcl
4728 WHERE bsl.cle_id = p_cp_id
4729 AND bsl.bcl_id = bcl.id
4730 AND bcl.bill_action = 'RI';
4731
4732 CURSOR l_top_line_Amt_csr IS
4733 SELECT nvl(SUM(price_negotiated),0) tot_amt
4734 FROM okc_k_lines_b
4735 where cle_id = p_top_line_id
4736 and lse_id in (35,7,8,9,10,11,13,18,25);
4737
4738 CURSOR l_hdr_Amt_csr IS
4739 SELECT nvl(SUM(price_negotiated),0) tot_amount
4740 FROM okc_k_lines_b
4741 where dnz_chr_id = p_dnz_chr_id
4742 and lse_id in (35,7,8,9,10,11,13,18,25,46);
4743
4744 l_bsl_rec l_bsl_csr%ROWTYPE;
4745
4746 l_subline_update NUMBER;
4747 l_top_line_amt NUMBER;
4748 l_hdr_amt NUMBER;
4749
4750
4751
4752 BEGIN
4753
4754 ---This proceudre will check if subline end date <= max billed date and billed amount <> line amt
4755 ---then it will update line amt to billed amt.
4756 --if subline amt gets updated then top line and header amt will also get changed.
4757 ---this will be called from oks_bill_sch (cascade_dates_all and create_bill_sch_cp).
4758
4759 x_return_status := 'S';
4760
4761 l_subline_update := 0;
4762
4763 IF p_sub_line_id IS NULL THEN ---called for top line
4764
4765 FOR l_SubLine_rec IN l_SubLine_Csr
4766 LOOP
4767
4768 OPEN l_bsl_csr(l_SubLine_rec.id);
4769 FETCH l_bsl_csr INTO l_bsl_rec;
4770 CLOSE l_bsl_csr;
4771 IF l_bsl_rec.max_billed_to IS NOT NULL AND TRUNC(l_bsl_rec.max_billed_to) = l_SubLine_rec.end_dt
4772 AND nvl(l_SubLine_rec.cp_amt,0) <> l_bsl_rec.bill_amt THEN
4773
4774 UPDATE okc_k_lines_b SET price_negotiated = l_bsl_rec.bill_amt
4775 WHERE id = l_SubLine_rec.id;
4776
4777 l_subline_update := l_subline_update + 1;
4778 END IF; ---update decision chk
4779
4780 END LOOP; ---subline csr end
4781
4782 ELSIF p_sub_line_id IS NOT NULL THEN
4783
4784 x_amount := nvl(p_amount,0);
4785
4786
4787 OPEN l_bsl_csr(p_sub_line_id);
4788 FETCH l_bsl_csr INTO l_bsl_rec;
4789 CLOSE l_bsl_csr;
4790 IF l_bsl_rec.max_billed_to IS NOT NULL AND TRUNC(l_bsl_rec.max_billed_to) = TRUNC(p_end_date)
4791 AND nvl(p_amount,0) <> l_bsl_rec.bill_amt THEN
4792
4793 UPDATE okc_k_lines_b SET price_negotiated = l_bsl_rec.bill_amt
4794 WHERE id = p_sub_line_id;
4795
4796 x_amount := l_bsl_rec.bill_amt;
4797
4798 l_subline_update := l_subline_update + 1;
4799
4800 END IF; ---update decision chk
4801
4802 END IF; ---chk for p_sub_line_id null
4803
4804
4805
4806 IF l_subline_update > 0 THEN ---sub line updated
4807
4808 OPEN l_top_line_Amt_csr;
4809 FETCH l_top_line_Amt_csr INTO l_top_line_amt ;
4810 CLOSE l_top_line_Amt_csr ;
4811
4812 UPDATE okc_k_lines_b SET price_negotiated = l_top_line_amt
4813 WHERE id = p_top_line_id;
4814
4815 OPEN l_hdr_Amt_csr;
4816 FETCH l_hdr_Amt_csr INTO l_hdr_amt ;
4817 CLOSE l_hdr_Amt_csr ;
4818
4819 UPDATE okc_k_headers_b SET estimated_amount = l_hdr_amt
4820 WHERE id = p_dnz_chr_id;
4821 END IF;
4822
4823 END Adjust_line_price;
4824
4825 End oks_bill_util_pub;