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