[Home] [Help]
PACKAGE BODY: APPS.OKS_BILL_MIGRATION
Source
1 PACKAGE BODY OKS_BILL_MIGRATION AS
2 /* $Header: OKSBMIGB.pls 120.1 2005/10/03 07:52:21 upillai noship $ */
3
4
5 G_DESCRIPTIVE_FLEXFIELD_NAME CONSTANT VARCHAR2(200) := 'OKC Rule Developer DF';
6 G_DF_COUNT CONSTANT NUMBER(2) := 15;
7
8 -- Global vars to hold the min and max hdr_id for each sub-request range
9 type range_rec is record (
10 lo number,
11 hi number,
12 jobno number);
13 type rangeArray is VARRAY(50) of range_rec;
14 range_arr rangeArray;
15 g_instance_id integer := 0;
16 --------------------------------------------------------------------------------
17 -------------- Global Varibale declarations for Rules Migration ----------------
18 --------------------------------------------------------------------------------
19 -- Global constant for the threshold count before splitting into sub-requests
20 MAX_SINGLE_REQUEST NUMBER := 500;
21
22 -- Global constant for the maximum allowed sub-requests (parallel workers)
23 MAX_JOBS NUMBER := 30;
24
25 --------------------------------------------------------------------------------
26 --------------------------------------------------------------------------------------------
27 -- Generate Range and Split Function. --
28 --------------------------------------------------------------------------------------------
29
30 procedure split_range (
31 p_lo number,
32 p_hi number,
33 p_buckets number) is
34 -- splits range (p_lo=>p_hi) into p_buckets pieces and appends to VArrays.
35 l_lo number := p_lo;
36 l_idx1 number := range_arr.count + 1;
37 l_idx2 number := range_arr.count + p_buckets;
38 l_bucket_width integer;
39 begin
40 If p_buckets = 0 then
41 return;
42 End if;
43
44 if range_arr.count > 0 then
45 -- so we don't overlap high value of previous range
46 l_lo := p_lo + 1;
47 end if;
48
49 l_bucket_width := (p_hi - l_lo) / p_buckets;
50
51 range_arr.extend(p_buckets);
52 for idx in l_idx1..l_idx2 loop
53 range_arr(idx).lo := l_lo + ((idx - l_idx1) * l_bucket_width);
54 if idx < l_idx2 then
55 range_arr(idx).hi := range_arr(idx).lo + l_bucket_width -1;
56 else
57 range_arr(idx).hi := p_hi;
58 end if;
59 end loop;
60 end split_range;
61
62
63 function generate_ranges (
64 p_lo number,
65 p_hi number,
66 p_avg number,
67 p_stddev number,
68 p_total number) return integer is
69
70 l_total_buckets integer := 0;
71
72 l_stdlo number := greatest(round(p_avg - p_stddev), p_lo);
73 l_stdhi number := least(round(p_avg + p_stddev), p_hi);
74 l_stddev_percent number := 0.66; -- the area covered by +/-1 stddev
75 l_outlier_buckets integer := 0;
76 l_std_buckets integer := 0;
77 l_lo_buckets integer := 0;
78 l_hi_buckets integer := 0;
79 l_outlier_entries_per_bucket number := 0;
80 modidx integer;
81
82 begin
83 range_arr := rangeArray();
84 -- number of buckets is set to 20
85 l_total_buckets := MAX_JOBS;
86
87 l_outlier_buckets := l_total_buckets * (1 - l_stddev_percent);
88 if l_outlier_buckets > 0 then
89 l_outlier_entries_per_bucket := p_total * (1 - l_stddev_percent)
90 / l_outlier_buckets ;
91 end if;
92 for idx in 1..l_outlier_buckets loop
93 modidx := mod(idx,2);
94 -- alternate assignment between hi and lo buckets
95 if modidx = 1
96 AND (p_hi - (l_hi_buckets+1) * l_outlier_entries_per_bucket)
97 > l_stdhi then
98 -- allocate buckets for positive outliers
99 l_hi_buckets := l_hi_buckets + 1;
100 elsif modidx = 0
101 AND (p_lo + (l_lo_buckets+1) * l_outlier_entries_per_bucket)
102 < l_stdlo then
103 -- allocate buckets for negative outliers
104 l_lo_buckets := l_lo_buckets + 1;
105 -- else min or max has been consumed, save bucket for middle
106 end if;
107 end loop;
108
109 -- compute middle buckets
110 l_std_buckets := l_total_buckets - l_lo_buckets - l_hi_buckets;
111
112 -- in case low-high allocations yielded zero buckets.
113 -- i.e., outliers were folded into middle buckets.
114 if l_lo_buckets = 0 then
115 l_stdlo := p_lo;
116 end if;
117 if l_hi_buckets = 0 then
118 l_stdhi := p_hi;
119 end if;
120
121
122 -- ranges for negative outliers
123 split_range(p_lo, l_stdlo, l_lo_buckets);
124 -- ranges for +/-1 stddev from mean
125 split_range(l_stdlo, l_stdhi, l_std_buckets);
126 -- ranges for positive outliers
127 split_range(l_stdhi, p_hi, l_hi_buckets);
128
129 return l_total_buckets;
130 end generate_ranges;
131
132
133 /***********************function generate_ranges (
134 p_lo IN number,
135 p_hi IN number,
136 p_avg In number,
137 p_stddev IN number,
138 p_total IN number,
139 p_sub_requests IN number) return integer is
140
141 l_total_buckets integer := 0;
142 l_stdlo number := greatest(round(p_avg - p_stddev), p_lo);
143 l_stdhi number := least(round(p_avg + p_stddev), p_hi);
144 l_stddev_percent number := 0.66; -- the area covered by +/-1 stddev
145 l_outlier_buckets integer := 0;
146 l_std_buckets integer := 0;
147 l_lo_buckets integer := 0;
148 l_hi_buckets integer := 0;
149 l_outlier_entries_per_bucket number := 0;
150 modidx integer;
151 begin
152 range_arr := rangeArray();
153
154 l_total_buckets := greatest(nvl(p_sub_requests,3), least(p_total/MAX_SINGLE_REQUEST, MAX_JOBS));
155
156 l_outlier_buckets := l_total_buckets * (1 - l_stddev_percent);
157 if l_outlier_buckets > 0 then
158 l_outlier_entries_per_bucket := p_total * (1 - l_stddev_percent)
159 / l_outlier_buckets ;
160 end if;
161
162 for idx in 1..l_outlier_buckets
163 loop
164 modidx := mod(idx,2);
165 -- alternate assignment between hi and lo buckets
166 if modidx = 1
167 AND (p_hi - (l_hi_buckets+1) * l_outlier_entries_per_bucket)
168 > l_stdhi then
169 -- allocate buckets for positive outliers
170 l_hi_buckets := l_hi_buckets + 1;
171 elsif modidx = 0
172 AND (p_lo + (l_lo_buckets+1) * l_outlier_entries_per_bucket)
173 < l_stdlo then
174 -- allocate buckets for negative outliers
175 l_lo_buckets := l_lo_buckets + 1;
176 -- else min or max has been consumed, save bucket for middle
177 end if;
178 end loop;
179
180 -- compute middle buckets
181 l_std_buckets := l_total_buckets - l_lo_buckets - l_hi_buckets;
182
183 -- in case low-high allocations yielded zero buckets.
184 -- i.e., outliers were folded into middle buckets.
185 if l_lo_buckets = 0 then
186 l_stdlo := p_lo;
187 end if;
188 if l_hi_buckets = 0 then
189 l_stdhi := p_hi;
190 end if;
191
192 -- ranges for negative outliers
193 split_range(p_lo, l_stdlo, l_lo_buckets);
194 -- ranges for +/-1 stddev from mean
195 split_range(l_stdlo, l_stdhi, l_std_buckets);
196 -- ranges for positive outliers
197 split_range(l_stdhi, p_hi, l_hi_buckets);
198
199 return l_total_buckets;
200 end generate_ranges;***************/
201
202
203
204
205 -----------------------------------------------------------------------------------
206
207 FUNCTION get_instance_id
208 return integer is
209 cursor inst_csr is
210 select instance_number from v$instance;
211 BEGIN
212 IF g_instance_id = 0 THEN
213 OPEN inst_csr;
214 FETCH inst_csr into g_instance_id;
215 CLOSE inst_csr;
216 END IF;
217 RETURN g_instance_id;
218 END;
219
220
221 PROCEDURE update_lvl_elements
222 (
223 p_lvl_element_tbl IN oks_bill_level_elements_pvt.letv_tbl_type
224 ,x_lvl_element_tbl OUT NOCOPY oks_bill_level_elements_pvt.letv_tbl_type
225 ,x_return_status OUT NOCOPY Varchar2
226 ,x_msg_count OUT NOCOPY Number
227 ,x_msg_data OUT NOCOPY Varchar2
228 )
229 IS
230 l_lvl_element_tbl_in oks_bill_level_elements_pvt.letv_tbl_type;
231 l_lvl_element_tbl_out oks_bill_level_elements_pvt.letv_tbl_type;
232 l_api_version CONSTANT NUMBER := 1.0;
233 l_init_msg_list CONSTANT VARCHAR2(1) := OKC_API.G_FALSE;
234 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
235 l_index NUMBER;
236 l_module VARCHAR2(50) := 'TBL_RULE.CREATE_ROWS';
237 l_debug BOOLEAN := TRUE;
238
239
240 BEGIN
241 x_return_status := l_return_status;
242 END update_lvl_elements;
243
244
245 FUNCTION Create_Timevalue (p_chr_id IN NUMBER,p_start_date IN DATE) RETURN NUMBER Is
246 l_p_tavv_tbl OKC_TIME_PUB.TAVV_TBL_TYPE;
247 l_x_tavv_tbl OKC_TIME_PUB.TAVV_TBL_TYPE;
248 l_api_version Number := 1.0;
249 l_init_msg_list Varchar2(1) := 'T';
250 l_return_status varchar2(200);
251 l_msg_count NUMBER;
252 l_msg_data VARCHAR2(2000);
253 Begin
254 return(null);
255 End Create_Timevalue;
256
257
258 PROCEDURE BILL_UPGRADATION_ALL
259 (
260 x_return_status OUT NOCOPY VARCHAR2
261 )
262 IS
263
264 cursor l_header_agg_csr is
265 select min(id) minid, max(id) maxid, count(*) total,
266 avg(id) avgid, stddev(id) stdid
267 from okc_statuses_b sta,
268 okc_k_headers_b hdr
269 where hdr.id not in (-2,-1)
270 and sta.ste_code not in ('TERMINATED','ENTERED','CANCELLED')
271 and sta.code = hdr.sts_code
272 and hdr.scs_code in ('SERVICE','WARRANTY');
273
274 cursor l_jobs_csr(l_job number) is
275 select count(*)
276 from user_jobs
277 where job = l_job;
278
279 ---------------------------------------------------------------------
280 --Newly added cursor to check whether migration has already happened.
281 ---------------------------------------------------------------------
282 cursor l_check_mig_csr is
283 SELECT 'x'
284 FROM OKS_STREAM_LEVELS_B;
285
286
287
288 l_agg_rec l_header_agg_csr%ROWTYPE;
289 l_subrequests integer;
290 l_ret integer;
291 l_job_count integer := 0;
292 l_dummy varchar2(10);
293
294 BEGIN
295 X_return_status := 'S';
296 --------------------------------------------------------------------
297 --The following cursor added to check whether migration has occured.
298 --------------------------------------------------------------------
299 open l_check_mig_csr ;
300 fetch l_check_mig_csr into l_dummy;
301 If l_check_mig_csr%FOUND then
302 return;
303 else
304 close l_check_mig_csr;
305 end if;
306
307
308 open l_header_agg_csr;
309 fetch l_header_agg_csr into l_agg_rec;
310 close l_header_agg_csr;
311
312 -- populate lo,hi varrays
313 l_subrequests :=
314 generate_ranges(l_agg_rec.minid, l_agg_rec.maxid, l_agg_rec.avgid,
315 l_agg_rec.stdid, l_agg_rec.total);
316
317 for idx in 1..l_subrequests loop
318 dbms_job.submit(range_arr(idx).jobno,
319 'OKS_BILL_MIGRATION.BILL_UPGRADATION(' ||
320 range_arr(idx).lo ||','|| range_arr(idx).hi ||');',
321 instance => get_instance_id);
322 commit;
323 end loop;
324
325 loop
326 for idx in 1..l_subrequests loop
327 open l_jobs_csr(range_arr(idx).jobno);
328 fetch l_jobs_csr into l_job_count;
329 close l_jobs_csr;
330 if l_job_count > 0 then
331 exit;
332 end if;
333 end loop;
334 if l_job_count > 0 then
335 dbms_lock.sleep(60);
336 else
337 exit;
338 end if;
339 end loop;
340
341 EXCEPTION
342 WHEN OTHERS THEN
343 ---dbms_output.put_line(sqlerrm);
344 X_return_status := 'E';
345
346 END BILL_UPGRADATION_ALL;
347
348
349 PROCEDURE BILL_UPGRADATION_ALL_OM
350 (
351 x_return_status OUT NOCOPY VARCHAR2
352 )
353 IS
354
355 cursor l_header_agg_csr is
356 select min(id) minid, max(id) maxid, count(*) total,
357 avg(id) avgid, stddev(id) stdid
358 from okc_statuses_b sta,
359 okc_k_headers_b hdr
360 where hdr.id not in (-2,-1)
361 and sta.ste_code not in ('TERMINATED','ENTERED','CANCELLED')
362 and sta.code = hdr.sts_code
363 and hdr.scs_code = 'WARRANTY';
364 ----and hdr.scs_code in ('SERVICE','WARRANTY');
365
366 cursor l_jobs_csr(l_job number) is
367 select count(*)
368 from user_jobs
369 where job = l_job;
370
371 cursor l_check_mig_csr is
372 select 'x'
373 FROM okc_k_lines_b lines
374 where lines.lse_id = 19
375 and ( exists ( select 1 from okc_k_rel_objs rel
376 where rel.cle_id = lines.id )
377 or
378 exists ( select 1 from okc_k_rel_objs rel2 ,
379 okc_k_lines_b line2
380 where line2.cle_id = lines.id
381 and rel2.cle_id = line2.id
382 and line2.lse_id = 25 )
383 )
384 AND EXISTS
385 (SELECT 1 FROM OKS_BILL_CONT_LINES BCL
386 WHERE BCL.CLE_ID = LINES.ID );
387
388 l_agg_rec l_header_agg_csr%ROWTYPE;
389 l_subrequests integer;
390 l_ret integer;
391 l_job_count integer := 0;
392 l_dummy varchar2(10);
393
394 BEGIN
395 --dbms_output.put_line('The start Time = '|| to_char(sysdate , 'dd-mm-yy-hh:mi:ss'));
396
397 X_return_status := 'S';
398 open l_check_mig_csr ;
399 fetch l_check_mig_csr into l_dummy;
400 if l_check_mig_csr%FOUND then
401 return;
402 end if;
403 close l_check_mig_csr;
404
405 open l_header_agg_csr;
406 fetch l_header_agg_csr into l_agg_rec;
407 close l_header_agg_csr;
408
409 -- populate lo,hi varrays
410 l_subrequests :=
411 generate_ranges(l_agg_rec.minid, l_agg_rec.maxid, l_agg_rec.avgid,
412 l_agg_rec.stdid, l_agg_rec.total);
413
414 for idx in 1..l_subrequests loop
415 dbms_job.submit(range_arr(idx).jobno,
416 'OKS_BILL_MIGRATION.BILL_UPGRADATION_OM(' ||
417 range_arr(idx).lo ||','|| range_arr(idx).hi ||');',
418 instance => get_instance_id);
419 commit;
420 end loop;
421
422 loop
423 for idx in 1..l_subrequests loop
424 open l_jobs_csr(range_arr(idx).jobno);
425 fetch l_jobs_csr into l_job_count;
426 close l_jobs_csr;
427 if l_job_count > 0 then
428 exit;
429 end if;
430 end loop;
431 if l_job_count > 0 then
432 dbms_lock.sleep(60);
433 else
434 exit;
435 end if;
436 end loop;
437 --dbms_output.put_line('The start Time = '|| to_char(sysdate , 'dd-mm-yy-hh:mi:ss'));
438
439
440 EXCEPTION
441 WHEN OTHERS THEN
442 ---dbms_output.put_line(sqlerrm);
443 X_return_status := 'E';
444
445 END BILL_UPGRADATION_ALL_OM;
446
447
448 PROCEDURE Update_Line_Numbers
449 (
450 p_chr_id_lo IN NUMBER DEFAULT NULL,
451 p_chr_id_hi IN NUMBER DEFAULT NULL
452 )
453 IS
454
455 BEGIN
456 null;
457 END Update_Line_Numbers;
458
459 PROCEDURE migrate_line_numbers
460 (
461 x_return_status OUT NOCOPY VARCHAR2
462 )
463 IS
464 Begin
465 null;
466 END migrate_Line_Numbers;
467
468
469
470 Procedure MIGRATE_CURRENCY
471 IS
472 --- Global Constants -- this will be moved to package header
473 G_RET_STS_SUCCESS CONSTANT VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
474 G_RET_STS_ERROR CONSTANT VARCHAR2(1) := OKC_API.G_RET_STS_ERROR;
475 G_UNEXPECTED_ERROR CONSTANT VARCHAR2(200) := 'OKC_CONTRACTS_UNEXP_ERROR';
476 G_SQLERRM_TOKEN CONSTANT VARCHAR2(200) := 'ERROR_MESSAGE';
477 G_SQLCODE_TOKEN CONSTANT VARCHAR2(200) := 'ERROR_CODE';
478 G_APP_NAME CONSTANT VARCHAR2(3) := 'OKS';
479
480 Cursor l_contract_csr IS
481 Select hdr.ID
482 ,hdr.CONTRACT_NUMBER
483 ,hdr.CURRENCY_CODE
484 ,hdr.AUTHORING_ORG_ID
485 From OKC_K_HEADERS_B hdr
486 Where exists (Select 1
487 From okc_k_lines_b ln
488 ,oks_bill_cont_lines bcl
489 Where hdr.id = ln.dnz_chr_id
490 and ln.id = bcl.cle_id );
491
492 Cursor l_bill_lines_csr(p_dnz_chr_id Number) IS
493 Select bcl.ID BclID
494 ,bcl.BTN_ID BtnID
495 ,bcl.CLE_ID LineID
496 ,bcl.AMOUNT Amount
497 ,bcl.CURRENCY_CODE
498 From OKS_BILL_CONT_LINES bcl
499 ,OKC_K_LINES_B kln
500 Where kln.DNZ_CHR_ID = p_dnz_chr_id
501 and bcl.CLE_ID = kln.ID
502 and bcl.BTN_ID is Null
503 and bcl.BILL_ACTION = 'RI' for update;
504
505 Cursor l_rule_info_csr(p_dnz_chr_id Number) IS
506 Select rul.Rule_Information1 ConvRate
507 ,to_date(rul.rule_information2,'YYYY/MM/DD HH24:MI:SS') ConvDate
508 ,rul.rule_information3 EuroRate
509 ,rul.jtot_object1_code ConvTypeCode
510 ,con.NAME ConvType
511 From OKX_CONVERSION_TYPES_V con
512 ,OKC_RULES_B rul
513 ,OKC_RULE_GROUPS_B rgp
514 Where rgp.DNZ_CHR_ID = p_dnz_chr_id
515 and rul.Rgp_Id = rgp.Id
516 and rul.RULE_INFORMATION_CATEGORY = 'CVN'
517 and con.ID1 = rul.OBJECT1_ID1;
518
519 l_cont_rec l_contract_csr%rowtype ;
520 l_bill_line_rec l_bill_lines_csr%rowtype ;
521 l_rule_info_rec l_rule_info_csr%rowtype ;
522
523 l_migration_req Varchar2(1) ;
524 l_euro_code Varchar2(15);
525 l_euro_amount Number;
526 l_conversion_rate Number ;
527 x_return_status Varchar2(10);
528 BEGIN
529 x_return_status := G_RET_STS_SUCCESS;
530 DBMS_TRANSACTION.SAVEPOINT('MAIN');
531 -- Setting OKC Context
532 okc_context.set_okc_org_context;
533
534 -- Open the contract cursor
535 Open l_contract_csr ;
536 Loop
537 Fetch l_contract_csr into l_cont_rec ;
538 Exit when l_contract_csr%NotFound;
539
540 DBMS_TRANSACTION.SAVEPOINT('CONTRACT_LINE');
541
542 -- Update currency code for all bill cont lines for this contract -BTN ID not null
543 Begin
544 --dbms_output.put_line('Before Updating the bill cont lines for Contract'||l_cont_rec.ID) ;
545
546 Update OKS_BILL_CONT_LINES
547 Set CURRENCY_CODE = l_cont_rec.CURRENCY_CODE
548 Where CLE_ID in (select kln.ID
549 from OKC_K_LINES_B kln
550 Where kln.DNZ_CHR_ID = l_cont_rec.ID )
551 And BTN_ID is Not Null;
552
553
554
555 Exception When Others then
556 DBMS_TRANSACTION.ROLLBACK_SAVEPOINT('CONTRACT_LINE');
557 End ;
558
559 -- Call OKC API to find the contract requires migration or not
560 l_migration_req := OKC_CURRENCY_API.IS_EURO_CONVERSION_NEEDED(l_cont_rec.CURRENCY_CODE) ;
561
562
563
564 If l_migration_req = 'Y' then
565 -- Get Conversion Date and Rate for contract
566 Open l_rule_info_csr(l_cont_rec.ID) ;
567 Fetch l_rule_info_csr into l_rule_info_rec ;
568
569 -- Get Euro currency code
570 l_euro_code := OKC_CURRENCY_API.GET_EURO_CURRENCY_CODE(l_cont_rec.CURRENCY_CODE) ;
571 -- dbms_output.put_line('After Getting the Euro Curr for Contract '||l_euro_code) ;
572
573 -- Update Euro currency code and Euro Amount for all bill cont lines with BTN ID Null
574 Open l_bill_lines_csr(l_cont_rec.ID) ;
575 Loop
576 Fetch l_bill_lines_csr into l_bill_line_rec ;
577 Exit When l_bill_lines_csr%NotFound ;
578
579 -- Get the Euro converted amount for bill cont line
580 l_conversion_rate := l_rule_info_rec.ConvRate ;
581 -- l_bill_line_rec.Amount is Not Null
582 -- and
583 If ( l_rule_info_rec.ConvType is Not Null
584 and l_rule_info_rec.ConvRate is Not Null )
585 then
586 OKC_CURRENCY_API.CONVERT_AMOUNT ( p_FROM_CURRENCY => l_cont_rec.CURRENCY_CODE
587 ,p_TO_CURRENCY => l_euro_code
588 ,p_CONVERSION_DATE => l_rule_info_rec.ConvDate
589 ,p_CONVERSION_TYPE => l_rule_info_rec.ConvType
590 ,p_AMOUNT => l_bill_line_rec.Amount
591 ,x_CONVERSION_RATE => l_conversion_rate
592 ,x_CONVERTED_AMOUNT => l_euro_amount);
593
594 -- Update Bill Cont Lines with Euro Amount
595 Begin
596 --dbms_output.put_line('Before Updating Converted amount '||l_bill_line_rec.BclID) ;
597 Update OKS_BILL_CONT_LINES
598 Set CURRENCY_CODE = l_euro_code
599 ,AMOUNT = l_euro_amount
600 Where ID = l_bill_line_rec.BclID ;
601 --dbms_output.put_line('After Updating Converted amount '||l_bill_line_rec.BclID) ;
602
603 Exception When Others then
604 DBMS_TRANSACTION.ROLLBACK_SAVEPOINT('CONTRACT_LINE');
605 End ;
606
607 End If; -- Conversion type and conversion rate
608
609 End Loop ;
610
611 -- dbms_output.put_line('After After Final Commit for contract '||l_cont_rec.ID) ;
612 Close l_bill_lines_csr ;
613
614 Else -- if migration is not required, update currency code for this for lines with BTN id Null
615
616 -- Update currency code for all bill cont lines for this contract -- BTN ID null
617 Begin
618 --dbms_output.put_line('Before Updating the bill cont lines for Contract- BTN ID null'||l_cont_rec.ID) ;
619
620 Update OKS_BILL_CONT_LINES
621 Set CURRENCY_CODE = l_cont_rec.CURRENCY_CODE
622 Where CLE_ID in (select kln.ID
623 from OKC_K_LINES_B kln
624 Where kln.DNZ_CHR_ID = l_cont_rec.ID )
625 And BTN_ID is Null;
626
627 -- dbms_output.put_line('After Updating the bill cont lines for Contract- BTN ID null'||l_cont_rec.ID) ;
628 Exception When Others then
629 DBMS_TRANSACTION.ROLLBACK_SAVEPOINT('CONTRACT_LINE');
630 End ;
631
632 End If; -- migration req
633
634 ---Commit ;
635 End Loop ; -- Contract cursor
636 Close l_contract_csr ;
637
638 --dbms_output.put_line('Currency migration program has completed with NO errors ') ;
639 EXCEPTION
640 WHEN Others THEN
641 DBMS_TRANSACTION.ROLLBACK_SAVEPOINT('MAIN');
642 x_return_status := OKC_API.G_RET_STS_ERROR;
643 OKC_API.SET_MESSAGE
644 ( p_app_name => G_APP_NAME,
645 p_msg_name => G_UNEXPECTED_ERROR,
646 p_token1 => G_SQLCODE_TOKEN,
647 p_token1_value => SQLCODE,
648 p_token2 => G_SQLERRM_TOKEN,
649 p_token2_value => SQLERRM
650 );
651 END; -- Procedure MIGRATE_CURRENCY
652
653 PROCEDURE one_time_billing
654 (
655 p_invoice_rule_id NUMBER,
656 p_cle_id NUMBER,
657 x_return_status OUT NOCOPY VARCHAR2
658 )
659 IS
660 l_lvl_element_tbl_in oks_bill_level_elements_pvt.letv_tbl_type;
661 l_lvl_element_tbl_out oks_bill_level_elements_pvt.letv_tbl_type;
662 l_SLL_tbl_type OKS_BILL_SCH.StreamLvl_tbl; --stream_lvl_tbl;
663 l_bil_sch_out_tbl OKS_BILL_SCH.ItemBillSch_tbl; --item_bill_sch_tbl;
664 l_lvl_element_tbl oks_bill_level_elements_pvt.letv_tbl_type;
665 l_bill_cont_tbl OKS_BILL_MIGRATION.bill_cont_tbl;
666 l_bill_sub_tbl OKS_BILL_MIGRATION.bill_cont_tbl;
667
668 l_rule_id NUMBER := 0;
669 l_start_date DATE;
670 l_end_date DATE;
671 l_duration NUMBER := 0;
672 l_time VARCHAR2(450) ;
673 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
674 l_ctr NUMBER := 0;
675 l_tbl_ctr NUMBER := 0;
676 l_invoice_rule_id NUMBER :=0;
677 l_lvl_rec_ctr NUMBER := 0;
678 l_bill_count NUMBER := 0;
679 l_bill_ctr NUMBER := 0;
680 l_lvl_ctr NUMBER := 0;
681 l_lvl_count NUMBER := 0;
682 l_lvl_sum NUMBER := 0;
683 l_bill_sum NUMBER := 0;
684 l_msg_data VARCHAR2(2000);
685 l_msg_count NUMBER:=0;
686 l_diff NUMBER :=0;
687 l_flag VARCHAR2(1);
688 l_max_date_billed_to DATE:= NULL;
689
690
691 CURSOR top_line_grp_csr (p_cle_id NUMBER) IS
692 SELECT lines.id,lines.lse_id lse_id,lines.cle_id,lines.dnz_chr_id,
693 lines.start_date,NVL(lines.date_terminated,lines.end_date) end_date,
694 lines.date_terminated,
695 rgp.id rgp_id
696 FROM okc_k_lines_b lines,okc_rule_groups_b rgp
697 WHERE lines.dnz_chr_id = rgp.dnz_chr_id
698 AND lines.id = rgp.cle_id
699 AND lines.lse_id IN (1,12,19)
700 and lines.id = p_cle_id;
701
702 --Lines and group information
703
704 CURSOR line_grp_csr (p_cle_id NUMBER) IS
705 SELECT lines.id,lines.lse_id lse_id,lines.cle_id,lines.dnz_chr_id,
706 lines.start_date,NVL(lines.date_terminated,lines.end_date) end_date,
707 lines.date_terminated
708 FROM okc_k_lines_b lines
709 WHERE lines.cle_id = p_cle_id
710 AND lines.lse_id in (7,8,9,10,11,13,18,25,35);
711
712
713 --Rule information
714
715 CURSOR rules_csr (p_rgp_id NUMBER) IS
716 SELECT a.id,a.object1_id1,a.object2_id1,
717 a.object3_id1,a.jtot_object1_code,a.jtot_object2_code,
718 a.rule_information_category,
719 a.rule_information1,a.rule_information2,
720 a.rule_information3,a.rule_information4,
721 a.rule_information5,a.rule_information6,
722 a.rule_information7,a.rule_information8,
723 a.created_by,a.creation_date,a.last_updated_by,a.last_update_date,
724 a.last_update_login
725 FROM okc_rules_b a
726 WHERE a.rule_information_category = 'SBG'
727 AND a.rgp_id = p_rgp_id;
728
729
730 --Bill cont lines
731 CURSOR bill_cont_csr (p_cle_id NUMBER) IS
732 SELECT cle_id,
733 amount
734 FROM oks_bill_cont_lines
735 WHERE cle_id = p_cle_id
736 AND bill_action = 'RI';
737
738 --Bill sub lines
739 CURSOR bill_sub_csr (p_cle_id NUMBER) IS
740 SELECT cle_id,
741 amount
742 FROM oks_bill_sub_lines
743 WHERE cle_id = p_cle_id;
744
745
746
747 --Level element information
748
749 CURSOR level_elements_csr (p_id NUMBER) IS
750 SELECT lvl.id id, lvl.sequence_number sequence_number,
751 lvl.date_start date_start, lvl.amount amount,
752 lvl.date_completed date_completed
753 FROM oks_level_elements lvl,oks_stream_levels_b rule
754 WHERE lvl.rul_id = rule.id
755 AND rule.cle_id = p_id
756 ORDER BY lvl.date_start;
757
758
759 BEGIN
760 --initialize status
761 x_return_status := l_return_status;
762
763 FOR top_line_grp_rec IN top_line_grp_csr (p_cle_id)
764 LOOP
765 l_bill_count := 0;
766
767 IF top_line_grp_rec.date_terminated IS NOT NULL
768 AND top_line_grp_rec.date_terminated < SYSDATE THEN
769
770
771
772 SELECT COUNT(cle_id)
773 INTO l_bill_count
774 FROM oks_bill_cont_lines
775 WHERE cle_id = top_line_grp_rec.id
776 AND bill_action = 'RI';
777
778 END IF; --IF top_line_grp_rec.date_terminated IS NOT NULL and < sysdate
779
780 IF l_bill_count = 0
781 THEN
782
783 l_tbl_ctr :=0;
784 l_lvl_element_tbl_in.delete;
785 l_lvl_element_tbl_out.delete;
786 l_SLL_tbl_type.delete;
787
788
789 FOR rules_rec IN rules_csr (top_line_grp_rec.rgp_id)
790 LOOP
791
792 -- only for one time billing
793 -- two condition for one time billing
794 -- 1. when rule_information2 is null and rule_information3(bill_upto) is null/not null or less than line_end_date
795 -- 2. when rule_information2 is null and rule_information3(bill_upto) is null/not null or greater/equal than line_end_date
796
797 IF (rules_rec.rule_information2 IS NULL
798 AND (top_line_grp_rec.end_date
799 > NVL(TO_DATE(SUBSTR(rules_rec.rule_information3,1,21),'YYYY/MM/DD HH24:MI:SS'),top_line_grp_rec.end_date-1)))
800 THEN
801
802 l_max_date_billed_to := NULL;
803
804 --get MAX date from oks_bill_cont_lines
805 SELECT MAX(date_billed_to)
806 INTO l_max_date_billed_to
807 FROM oks_bill_cont_lines
808 WHERE cle_id = top_line_grp_rec.id
809 AND bill_action = 'RI';
810
811
812 IF l_max_date_billed_to IS NULL THEN
813 IF rules_rec.rule_information3 IS NOT NULL THEN
814
815 IF top_line_grp_rec.end_date > TO_DATE(SUBSTR(rules_rec.rule_information3,1,21),'YYYY/MM/DD HH24:MI:SS')
816 THEN
817
818 l_max_date_billed_to := TO_DATE(SUBSTR(rules_rec.rule_information3,1,21),'YYYY/MM/DD HH24:MI:SS');
819 END IF; --top_line_grp_rec.end_date > TO_DATE(SUBSTR(rules_rec.rule_information3,1,21),'YYYY/MM/DD HH24:MI:SS')
820
821 ELSE --RI3 NOT NULL
822
823 l_max_date_billed_to := top_line_grp_rec.end_date;
824
825 END IF; --IF rules_rec.rule_information3 IS NOT NULL
826 END IF; --l_max_date_billed_to IS NULL
827
828 IF l_max_date_billed_to IS NOT NULL THEN
829 l_tbl_ctr := 0;
830 l_duration := NULL;
831 l_time := NULL;
832
833 OKC_TIME_UTIL_PUB.get_duration(
834 top_line_grp_rec.start_date
835 ,l_max_date_billed_to
836 ,l_duration
837 ,l_time
838 ,l_return_status
839 );
840
841
842 IF l_return_status = 'S' THEN
843 l_start_date := top_line_grp_rec.start_date;
844
845 --## create rule for one time billing in days
846 l_tbl_ctr := l_tbl_ctr + 1;
847
848
849 l_SLL_tbl_type(l_tbl_ctr).cle_id := p_cle_id;
850 l_SLL_tbl_type(l_tbl_ctr).sequence_no := l_tbl_ctr;
851 l_SLL_tbl_type(l_tbl_ctr).level_periods := 1;
852 l_SLL_tbl_type(l_tbl_ctr).uom_per_period := l_duration;
853 l_SLL_tbl_type(l_tbl_ctr).level_amount := NULL;
854 l_SLL_tbl_type(l_tbl_ctr).invoice_offset_days := NVL(rules_rec.rule_information7,0);
855 l_SLL_tbl_type(l_tbl_ctr).uom_code := l_time;
856
857 END IF;
858
859 IF l_max_date_billed_to + 1 <= top_line_grp_rec.end_date THEN
860 l_duration := NULL;
861 l_time := NULL;
862
863 OKC_TIME_UTIL_PUB.get_duration(
864 l_max_date_billed_to +1
865 ,top_line_grp_rec.end_date
866 ,l_duration
867 ,l_time
868 ,l_return_status
869 );
870
871
872 IF l_return_status = 'S' THEN
873 l_start_date := top_line_grp_rec.start_date;
874
875 --## create rule for one time billing in days
876 l_tbl_ctr := l_tbl_ctr + 1;
877
878
879 l_SLL_tbl_type(l_tbl_ctr).cle_id := p_cle_id;
880 l_SLL_tbl_type(l_tbl_ctr).sequence_no := l_tbl_ctr;
881 l_SLL_tbl_type(l_tbl_ctr).level_periods := 1;
882 l_SLL_tbl_type(l_tbl_ctr).uom_per_period := l_duration;
883 l_SLL_tbl_type(l_tbl_ctr).level_amount := NULL;
884 l_SLL_tbl_type(l_tbl_ctr).invoice_offset_days := NVL(rules_rec.rule_information7,0);
885 l_SLL_tbl_type(l_tbl_ctr).uom_code := l_time;
886
887 END IF;
888
889 END IF; --IF l_max_billed_to <= top_line_grp_rec.end_date
890
891 END IF; --l_max_billed_date is not null
892
893
894 ELSIF (rules_rec.rule_information2 IS NULL AND
895 (top_line_grp_rec.end_date
896 <= TO_DATE(SUBSTR(rules_rec.rule_information3,1,21),'YYYY/MM/DD HH24:MI:SS')))
897 THEN
898 --errorout('here 2');
899 l_tbl_ctr := 0;
900 l_duration := NULL;
901 l_time := NULL;
902
903 OKC_TIME_UTIL_PUB.get_duration(
904 top_line_grp_rec.start_date
905 ,top_line_grp_rec.end_date
906 ,l_duration
907 ,l_time
908 ,l_return_status
909 );
910
911
912 IF l_return_status = 'S' THEN
913 l_start_date := top_line_grp_rec.start_date;
914
915 --## create rule for one time billing in days
916 l_tbl_ctr := l_tbl_ctr + 1;
917
918
919 l_SLL_tbl_type(l_tbl_ctr).cle_id := p_cle_id;
920 l_SLL_tbl_type(l_tbl_ctr).sequence_no := l_tbl_ctr;
921 l_SLL_tbl_type(l_tbl_ctr).level_periods := 1;
922 l_SLL_tbl_type(l_tbl_ctr).uom_per_period := l_duration;
923 l_SLL_tbl_type(l_tbl_ctr).level_amount := NULL;
924 l_SLL_tbl_type(l_tbl_ctr).invoice_offset_days := NVL(rules_rec.rule_information7,0);
925 l_SLL_tbl_type(l_tbl_ctr).uom_code := l_time;
926
927
928 END IF;
929
930 END IF;
931 END LOOP; --end of loop rules_rec
932 x_return_status := l_return_status;
933
934 IF l_SLL_tbl_type.COUNT > 0 THEN
935 -- Call bill API
936 oks_bill_sch.create_bill_sch_rules(p_billing_type => 'T',
937 p_sll_tbl => l_SLL_tbl_type,
938 p_invoice_rule_id => p_invoice_rule_id,
939 x_bil_sch_out_tbl => l_bil_sch_out_tbl,
940 x_return_status => l_return_status);
941
942 --errorout('l_return_status'||l_return_status);
943 --check status of create_bill_sch_rules and call update_lvl_element
944 IF l_return_status = 'S' THEN
945 -- Top line amount will be updated only for usage (lse_id = 12)
946 IF top_line_grp_rec.lse_id = 12 THEN
947
948 l_bill_count := 0;
949
950 SELECT COUNT(cle_id)
951 INTO l_bill_count
952 FROM oks_bill_cont_lines
953 WHERE cle_id = top_line_grp_rec.id
954 AND bill_action = 'RI';
955
956 IF l_bill_count > 0 THEN
957 l_lvl_rec_ctr := 0;
958 l_lvl_element_tbl.delete;
959
960 FOR level_elements_rec IN level_elements_csr (p_cle_id)
961 LOOP
962
963 l_lvl_rec_ctr := l_lvl_rec_ctr + 1;
964 IF l_bill_count > 0 THEN
965 l_lvl_rec_ctr := 1;
966
967 l_lvl_element_tbl(l_lvl_rec_ctr).id := level_elements_rec.id;
968 l_lvl_element_tbl(l_lvl_rec_ctr).sequence_number := level_elements_rec.sequence_number;
969 l_lvl_element_tbl(l_lvl_rec_ctr).amount := level_elements_rec.amount;
970 l_lvl_element_tbl(l_lvl_rec_ctr).date_completed := level_elements_rec.date_completed;
971 END IF; --IF l_bill_count > 0
972
973 END LOOP; -- FOR bill_element_rec IN bill_element_csr
974
975
976 l_tbl_ctr := 0;
977 l_bill_cont_tbl.delete;
978
979 FOR bill_cont_rec IN bill_cont_csr (top_line_grp_rec.id)
980 LOOP
981 l_tbl_ctr := l_tbl_ctr + 1;
982
983 l_bill_cont_tbl(l_tbl_ctr).cle_id := bill_cont_rec.cle_id;
984 l_bill_cont_tbl(l_tbl_ctr).amount := bill_cont_rec.amount;
985
986 END LOOP; -- FOR bill_cont_rec IN bill_cont_csr
987
988
989 l_tbl_ctr :=0;
990 l_diff :=0;
991
992 if l_bill_count > 0 then
993 l_diff := l_diff + (l_lvl_element_tbl(1).amount - l_bill_cont_tbl(1).amount);
994 l_lvl_element_tbl(1).amount := l_bill_cont_tbl(1).amount;
995 l_lvl_element_tbl(1).date_completed := sysdate;
996 end if;
997
998 l_lvl_element_tbl(l_lvl_element_tbl.count).amount := l_lvl_element_tbl(l_lvl_element_tbl.count).amount + l_diff;
999
1000 IF l_lvl_element_tbl.COUNT > 0 THEN
1001
1002 l_bill_ctr:= 0 ;
1003
1004 FOR l_bill_ctr in l_lvl_element_tbl.FIRST .. l_lvl_element_tbl.LAST
1005 LOOP
1006 UPDATE OKS_LEVEL_ELEMENTS
1007 SET amount = l_lvl_element_tbl(l_bill_ctr).amount,
1008 date_completed = l_lvl_element_tbl(l_bill_ctr).date_completed
1009 WHERE id = l_lvl_element_tbl(l_bill_ctr).id;
1010 END LOOP;
1011
1012 END IF; --l_lvl_element_tbl.COUNT >0
1013
1014
1015
1016
1017 END IF;-- IF l_bill_count > 0
1018
1019 END IF; --top_line_grp_rec.lse_id = 12
1020
1021 END IF; -- IF l_return_status = 'S' --status of create_bill_sch_rules
1022 END IF; -----------l_SLL_tbl_type.COUNT
1023
1024 -- check status of top line
1025 IF l_return_status = 'S' THEN
1026 --**********subline loop
1027
1028 FOR line_grp_rec IN line_grp_csr (top_line_grp_rec.id)
1029 LOOP
1030
1031 l_bill_count := 0;
1032
1033 SELECT COUNT(cle_id)
1034 INTO l_bill_count
1035 FROM oks_bill_sub_lines
1036 WHERE cle_id = line_grp_rec.id;
1037
1038
1039 IF l_bill_count > 0 THEN
1040
1041 l_lvl_rec_ctr := 0;
1042 l_lvl_element_tbl.delete;
1043
1044 FOR level_elements_rec IN level_elements_csr (line_grp_rec.id)
1045 LOOP
1046
1047 l_lvl_rec_ctr := l_lvl_rec_ctr + 1;
1048
1049 l_lvl_element_tbl(l_lvl_rec_ctr).id := level_elements_rec.id;
1050 l_lvl_element_tbl(l_lvl_rec_ctr).sequence_number := level_elements_rec.sequence_number;
1051 l_lvl_element_tbl(l_lvl_rec_ctr).amount := level_elements_rec.amount;
1052 l_lvl_element_tbl(l_lvl_rec_ctr).date_completed := level_elements_rec.date_completed;
1053
1054 END LOOP; -- FOR bill_element_rec IN bill_element_csr
1055
1056
1057
1058
1059 l_tbl_ctr := 0;
1060 l_bill_sub_tbl.delete;
1061 FOR bill_sub_rec IN bill_sub_csr (line_grp_rec.id)
1062 LOOP
1063 l_tbl_ctr := l_tbl_ctr + 1;
1064
1065 l_bill_sub_tbl(l_tbl_ctr).cle_id := bill_sub_rec.cle_id;
1066 l_bill_sub_tbl(l_tbl_ctr).amount := bill_sub_rec.amount;
1067
1068
1069 END LOOP; -- FOR bill_sub_rec IN bill_sub_csr
1070
1071
1072 l_bill_ctr :=0;
1073 l_diff :=0;
1074 if l_bill_count >0 then
1075 l_diff := l_diff + (l_lvl_element_tbl(1).amount - l_bill_sub_tbl(1).amount);
1076 l_lvl_element_tbl(1).amount := l_bill_sub_tbl(1).amount;
1077 l_lvl_element_tbl(1).date_completed := sysdate;
1078 end if;
1079
1080 l_lvl_element_tbl(l_lvl_element_tbl.count).amount := l_lvl_element_tbl(l_lvl_element_tbl.count).amount + l_diff;
1081
1082 IF l_lvl_element_tbl.COUNT > 0 THEN
1083
1084 ---------updating directly
1085 l_bill_ctr:= 0 ;
1086
1087 FOR l_bill_ctr in l_lvl_element_tbl.FIRST .. l_lvl_element_tbl.LAST
1088 LOOP
1089 UPDATE OKS_LEVEL_ELEMENTS
1090 SET amount = l_lvl_element_tbl(l_bill_ctr).amount,
1091 date_completed = l_lvl_element_tbl(l_bill_ctr).date_completed
1092 WHERE id = l_lvl_element_tbl(l_bill_ctr).id;
1093 END LOOP;
1094
1095
1096 END IF; -- IF l_lvl_element_tbl.COUNT > 0
1097
1098
1099
1100 END IF; -- IF l_bill_count > 0
1101
1102 END LOOP; --line_grp_rec
1103 --end subline loop
1104 END IF; --lF l_return_status = 'S'
1105
1106 END IF; -- IF l_bill_count = 0
1107
1108 END LOOP; --top_line_grp_rec
1109 x_return_status := l_return_status;
1110
1111 END one_time_billing;
1112
1113
1114
1115
1116 Procedure Create_Billing_Schd
1117 (
1118 P_srv_sdt IN Date
1119 , P_srv_edt IN Date
1120 , P_amount IN Number
1121 , P_chr_id IN Number
1122 , P_rule_id IN Varchar2
1123 , P_line_id IN Number
1124 , P_invoice_rule_id IN Number
1125 , X_msg_data OUT NOCOPY Varchar2
1126 , X_msg_count OUT NOCOPY Number
1127 , X_Return_status OUT NOCOPY Varchar2
1128 )
1129 Is
1130
1131 l_sll_tbl OKS_BILL_SCH.StreamLvl_tbl;
1132 l_bil_sch_out OKS_BILL_SCH.ItemBillSch_tbl;
1133 l_api_version CONSTANT NUMBER := 1.0 ;
1134 l_init_msg_list CONSTANT VARCHAR2(1) := 'T' ;
1135 l_return_status VARCHAR2(1) ;
1136 l_duration Number;
1137 l_timeunits Varchar2(25);
1138 l_top_line_sll_id number ;
1139 l_sub_line_sll_id number ;
1140 l_sub_sll_level_id number ;
1141 l_top_sll_level_id number ;
1142 l_top_sll_amount number ;
1143
1144
1145 cursor l_price_csr(pl_top_line_id in number) is
1146 select sum(nvl(price_negotiated , 0))
1147 from okc_k_lines_b where cle_id = pl_top_line_id and lse_id = 25;
1148
1149
1150 cursor l_sub_line_csr (p_top_line_id in number ) is
1151 select lines.id sub_line_id
1152 ,lines.start_date sub_line_start_date
1153 ,lines.end_date sub_line_end_date
1154 ,lines.date_terminated sub_line_date_terminated
1155 ,lines.price_negotiated sub_line_price_negotiated
1156 from okc_k_lines_b lines
1157 where lines.cle_id = p_top_line_id
1158 AND lines.lse_id = 25;
1159
1160 l_sub_line_rec l_sub_line_csr%rowtype ;
1161
1162 FUNCTION get_seq_id RETURN NUMBER IS
1163 BEGIN
1164 RETURN(okc_p_util.raw_to_number(sys_guid()));
1165 END get_seq_id;
1166
1167 Begin
1168 l_top_line_sll_id := get_seq_id ;
1169
1170 INSERT INTO OKS_STREAM_LEVELS_B
1171 (id, chr_id, cle_id,dnz_chr_id , sequence_no, uom_code , start_date, end_date,
1172 level_periods, uom_per_period, object_version_number,
1173 created_by , creation_date, last_updated_by, last_update_date )
1174 VALUES
1175 (l_top_line_sll_id, null, p_line_id, p_chr_id, 1, 'DAY', p_srv_sdt, p_srv_edt,
1176 1, (p_srv_edt - p_srv_sdt +1), 1 ,
1177 -1 , sysdate , -1 , sysdate);
1178
1179
1180 l_top_sll_level_id := get_seq_id ;
1181
1182 l_top_sll_amount := 0;
1183 open l_price_csr(p_line_id );
1184 fetch l_price_csr into l_top_sll_amount ;
1185 close l_price_csr;
1186
1187 insert into oks_level_elements
1188 (id , sequence_number , date_start , amount , date_transaction, date_to_interface ,date_completed ,
1189 object_version_number , rul_id ,created_by , creation_date , last_updated_by , last_update_date ,
1190 cle_id, parent_cle_id, dnz_chr_id, date_end)
1191 values (l_top_sll_level_id,1 , p_srv_sdt ,l_top_sll_amount , sysdate ,sysdate ,sysdate ,1 ,l_top_line_sll_id ,
1192 -1 , sysdate , -1 , sysdate,
1193 p_line_id, p_line_id, p_chr_id, p_srv_edt );
1194
1195 for l_sub_line_rec in l_sub_line_csr(p_line_id )
1196 loop
1197 l_sub_line_sll_id := get_seq_id ;
1198
1199
1200 INSERT INTO OKS_STREAM_LEVELS_B
1201 (id, chr_id, cle_id,dnz_chr_id , sequence_no, uom_code , start_date, end_date,
1202 level_periods, uom_per_period, object_version_number,
1203 created_by , creation_date, last_updated_by, last_update_date )
1204 VALUES(l_sub_line_sll_id, null, l_sub_line_rec.sub_line_id, p_chr_id, 1, 'DAY', p_srv_sdt, p_srv_edt,
1205 1, (p_srv_edt - p_srv_sdt +1), 1 ,
1206 -1 , sysdate , -1 , sysdate);
1207
1208
1209
1210
1211
1212 l_sub_sll_level_id := get_seq_id ;
1213
1214 insert into oks_level_elements
1215 (id , sequence_number , date_start , amount , date_transaction, date_to_interface ,date_completed ,
1216 object_version_number , rul_id ,created_by , creation_date , last_updated_by , last_update_date ,
1217 cle_id, parent_cle_id, dnz_chr_id, date_end)
1218 values (l_sub_sll_level_id,1 , l_sub_line_rec.sub_line_start_date ,l_sub_line_rec.sub_line_price_negotiated
1219 ,sysdate ,sysdate ,sysdate ,1 ,l_sub_line_sll_id , -1 , sysdate , -1 , sysdate,
1220 l_sub_line_rec.sub_line_id , p_line_id, p_chr_id,l_sub_line_rec.sub_line_end_date );
1221 end loop;
1222
1223 x_return_status := l_return_status;
1224
1225 Exception
1226 When G_EXCEPTION_HALT_VALIDATION Then
1227 x_return_status := l_return_status;
1228 Null;
1229 When Others Then
1230 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1231 OKC_API.set_message(G_APP_NAME, G_UNEXPECTED_ERROR,G_SQLCODE_TOKEN,SQLCODE,G_SQLERRM_TOKEN,SQLERRM);
1232
1233 End Create_Billing_schd ;
1234
1235
1236
1237 /*****************************************************************
1238 This procedure is called to create Billing Schedules for the
1239 Extended warranty lines created from OM. The Date Completed of
1240 the level elements will be updated with the current date.
1241 These contracts will opened up in the Authoring form.
1242 *******************************************************************/
1243
1244 PROCEDURE BILL_UPGRADATION_OM
1245 (
1246 p_chr_id_lo IN NUMBER DEFAULT NULL,
1247 p_chr_id_hi IN NUMBER DEFAULT NULL
1248 )
1249 IS
1250
1251 l_return_status VARCHAR2(1) ;
1252 l_msg_data VARCHAR2(2000);
1253 l_msg_count NUMBER:=0;
1254 i NUMBER ;
1255
1256
1257
1258 --Top Lines and group information
1259
1260 CURSOR top_line_grp_csr IS
1261 SELECT lines.id id,lines.lse_id lse_id,lines.dnz_chr_id dnz_chr_id,
1262 lines.start_date start_date,NVL(lines.date_terminated,lines.end_date) end_date,
1263 lines.date_terminated date_terminated,lines.upg_orig_system_ref upg_orig_system_ref,
1264 lines.upg_orig_system_ref_id upg_orig_system_ref_id
1265 FROM okc_k_lines_b lines,
1266 okc_k_headers_b hdr,
1267 okc_statuses_b sta
1268 where hdr.id BETWEEN p_chr_id_lo AND p_chr_id_hi
1269 and sta.ste_code not in ('TERMINATED','ENTERED','CANCELLED')
1270 and sta.code = hdr.sts_code
1271 and hdr.scs_code = 'WARRANTY'
1272 and lines.lse_id = 19
1273 and lines.dnz_chr_id = hdr.id
1274 and lines.sts_code <> 'TERMINATED'
1275 AND ( nvl(lines.upg_orig_system_ref,'x') <> 'MIG_BILL' )
1276 and ( exists ( select 1 from okc_k_rel_objs rel
1277 where rel.cle_id = lines.id )
1278 or
1279 exists ( select 1 from okc_k_rel_objs rel2 ,
1280 okc_k_lines_b line2
1281 where line2.cle_id = lines.id
1282 and rel2.cle_id = line2.id
1283 and line2.lse_id = 25 ) );
1284
1285 CURSOR l_sll_csr(p_top_line_id NUMBER) IS
1286 SELECT COUNT(id)
1287 FROM OKS_STREAM_LEVELS_B
1288 WHERE CLE_ID = p_top_line_id;
1289
1290 L_SLL_COUNT NUMBER;
1291
1292 Type l_num_tbl is table of NUMBER index by BINARY_INTEGER ;
1293 Type l_date_tbl is table of DATE index by BINARY_INTEGER ;
1294 Type l_chr_tbl is table of Varchar2(2000) index by BINARY_INTEGER ;
1295
1296
1297 l_id l_num_tbl;
1298 l_lse_id l_num_tbl;
1299 l_dnz_chr_id l_num_tbl;
1300 l_start_date l_date_tbl;
1301 l_end_date l_date_tbl;
1302 l_date_terminated l_date_tbl;
1303 l_upg_orig_system_ref l_chr_tbl;
1304 l_upg_orig_system_ref_id l_num_tbl;
1305
1306 Begin
1307
1308 l_return_status := 'S';
1309 DBMS_TRANSACTION.SAVEPOINT('BEFORE_TRANSACTION');
1310
1311 OPEN top_line_grp_csr ;
1312 LOOP
1313 FETCH top_line_grp_csr bulk collect into l_id ,
1314 l_lse_id ,
1315 l_dnz_chr_id ,
1316 l_start_date,
1317 l_end_date ,
1318 l_date_terminated ,
1319 l_upg_orig_system_ref,
1320 l_upg_orig_system_ref_id LIMIT 10000;
1321
1322 IF l_id.COUNT > 0 THEN
1323 FOR I IN l_id.FIRST .. l_id.LAST
1324 LOOP
1325
1326
1327 OPEN l_sll_csr(l_ID(i)) ;
1328 FETCH l_sll_csr INTO L_SLL_COUNT ;
1329 CLOSE l_sll_csr;
1330
1331 IF L_SLL_COUNT = 0 THEN
1332
1333 -- call to create billing schedule
1334 Create_Billing_Schd(
1335 P_srv_sdt => l_start_date(i)
1336 , P_srv_edt => l_end_date(i)
1337 , P_amount => Null
1338 , P_chr_id => l_dnz_chr_id(i)
1339 , P_rule_id => Null
1340 , P_line_id => l_id(i)
1341 , P_invoice_rule_id => -2
1342 , X_msg_data => l_msg_data
1343 , X_msg_count => l_msg_count
1344 , X_Return_status => l_return_status);
1345
1346
1347 IF L_RETURN_STATUS = OKC_API.G_RET_STS_SUCCESS THEN
1348
1349 -- call to create billing records (Bcl / Bsl)
1350 CREATE_BILL_DTLS
1351 ( p_dnz_chr_id => l_dnz_chr_id(i),
1352 P_top_line_id => l_id(i),
1353 p_top_line_start_date => l_start_date(i),
1354 p_top_line_end_date => l_end_date(i),
1355 p_top_line_UPG_ORIG_SYSTEM_REF => l_upg_orig_system_ref(i),
1356 p_top_line_UPG_ORIG_SYSTEM_id => l_upg_orig_system_ref_id(i),
1357 p_top_line_date_terminated => l_date_terminated(i),
1358 X_Return_status => l_return_status );
1359 END IF ;
1360 Else ----sll count > 0
1361 CREATE_BILL_DTLS
1362 ( p_dnz_chr_id => l_dnz_chr_id(i),
1363 p_top_line_id => l_id(i),
1364 p_top_line_start_date => l_start_date(i),
1365 p_top_line_end_date => l_end_date(i),
1366 p_top_line_UPG_ORIG_SYSTEM_REF => l_upg_orig_system_ref(i),
1367 p_top_line_UPG_ORIG_SYSTEM_id => l_upg_orig_system_ref_id(i),
1368 p_top_line_date_terminated => l_date_terminated(i),
1369 X_Return_status => l_return_status );
1370 END IF ;
1371
1372 L_SLL_COUNT := 0 ;
1373 END LOOP; -----l_id tbl loop
1374 END IF; ---l_id tbl count chk
1375
1376 COMMIT;
1377
1378 EXIT WHEN top_line_grp_csr%NOTFOUND ;
1379 END LOOP; --MAIN LOOP END
1380
1381 COMMIT;
1382
1383
1384
1385 EXCEPTION WHEN OTHERS THEN
1386 DBMS_TRANSACTION.ROLLBACK_SAVEPOINT('BEFORE_TRANSACTION');
1387 l_return_status := OKC_API.G_RET_STS_ERROR;
1388 OKC_API.SET_MESSAGE
1389 (
1390 p_app_name => G_APP_NAME,
1391 p_msg_name => G_UNEXPECTED_ERROR,
1392 p_token1 => G_SQLCODE_TOKEN,
1393 p_token1_value => SQLCODE,
1394 p_token2 => G_SQLERRM_TOKEN,
1395 p_token2_value => SQLERRM
1396 );
1397
1398
1399 END BILL_UPGRADATION_OM; -- Procedure Migration for OM contracts
1400
1401
1402
1403 PROCEDURE BILL_UPGRADATION
1404 (
1405 p_chr_id_lo IN NUMBER DEFAULT NULL,
1406 p_chr_id_hi IN NUMBER DEFAULT NULL
1407 )
1408 IS
1409
1410 Type l_num_tbl is table of NUMBER index by BINARY_INTEGER ;
1411 Type l_date_tbl is table of DATE index by BINARY_INTEGER ;
1412 Type l_chr_tbl is table of Varchar2(2000) index by BINARY_INTEGER ;
1413
1414
1415
1416 CURSOR top_line_grp_csr IS
1417 SELECT lines.id id,lines.lse_id lse_id,lines.cle_id cle_id,lines.dnz_chr_id,
1418 lines.start_date start_date,NVL(lines.date_terminated,lines.end_date) end_date,
1419 lines.date_terminated date_terminated, rgp.id rgp_id, rul.object1_id1 inv_rul_id
1420 FROM okc_k_lines_b lines,
1421 okc_rule_groups_b rgp,
1422 okc_rules_b rul
1423 WHERE lines.id = rgp.cle_id
1424 AND lines.lse_id in (1,12,19)
1425 AND lines.sts_code not in ('TERMINATED','ENTERED','CANCELLED')
1426 and lines.dnz_chr_id = rgp.dnz_chr_id
1427 AND lines.dnz_chr_id between P_chr_id_lo and p_chr_id_hi
1428 AND (( lines.upg_orig_system_ref IS NULL
1429 and not exists ( select 1 from okc_k_rel_objs rel
1430 where rel.cle_id = lines.id and rownum < 2 ) )
1431 OR ( nvl(lines.upg_orig_system_ref,'MIG_NOBILL') = 'MIG_BILL'))
1432 AND NOT EXISTS
1433 (SELECT 1 FROM oks_stream_levels_b sll
1434 WHERE sll.cle_id = lines.id )
1435 AND rul.rgp_id(+) = rgp.id
1436 AND rul.rule_information_category(+) = 'IRE';
1437
1438
1439 l_id l_num_tbl;
1440 l_lse_id l_num_tbl;
1441 l_cle_id l_num_tbl;
1442 l_dnz_chr_id l_num_tbl;
1443 l_start_dt l_date_tbl;
1444 l_end_dt l_date_tbl;
1445 l_date_terminated l_date_tbl;
1446 l_rgp_id l_num_tbl;
1447 l_inv_rul_id l_chr_tbl;
1448
1449
1450 L_ERRM VARCHAR2(1000);
1451
1452
1453 --Level element information
1454
1455 CURSOR level_elements_csr (p_id NUMBER) IS
1456 SELECT lvl.id id, lvl.sequence_number sequence_number,
1457 lvl.date_start date_start, lvl.amount amount,
1458 lvl.date_completed date_completed
1459 FROM oks_level_elements lvl, oks_stream_levels_b rule
1460 WHERE lvl.rul_id = rule.id
1461 AND rule.cle_id = p_id
1462 ORDER BY lvl.date_start;
1463
1464
1465 --Lines information
1466
1467 CURSOR line_grp_csr (p_cle_id NUMBER) IS
1468 SELECT lines.id,lines.lse_id lse_id,lines.cle_id,lines.dnz_chr_id,
1469 lines.start_date,NVL(lines.date_terminated,lines.end_date) end_date,
1470 lines.date_terminated
1471 FROM okc_k_lines_b lines
1472 WHERE lines.cle_id = p_cle_id
1473 AND lines.lse_id in (7,8,9,10,11,13,18,25,35)
1474 and not exists ( select 1 from okc_k_rel_objs rel
1475 where rel.cle_id = lines.id );
1476
1477
1478 --Rule information for top line
1479
1480 CURSOR rules_csr (p_rgp_id NUMBER) IS
1481 SELECT a.id,a.object1_id1,a.object2_id1,
1482 a.object3_id1,a.jtot_object1_code,a.jtot_object2_code,
1483 a.rule_information_category,
1484 a.rule_information1,a.rule_information2,
1485 a.rule_information3,a.rule_information4,
1486 a.rule_information5,a.rule_information6,
1487 a.rule_information7,a.rule_information8,
1488 a.created_by,a.creation_date,a.last_updated_by,a.last_update_date,
1489 a.last_update_login
1490 FROM okc_rules_b a
1491 WHERE a.rule_information_category = 'SBG'
1492 AND a.rgp_id = p_rgp_id;
1493
1494
1495 --Bill cont lines
1496 CURSOR bill_cont_csr (p_cle_id NUMBER) IS
1497 SELECT cle_id,
1498 amount
1499 FROM oks_bill_cont_lines
1500 WHERE cle_id = p_cle_id
1501 AND bill_action = 'RI';
1502
1503 --Bill sub lines
1504 CURSOR bill_sub_csr (p_cle_id NUMBER) IS
1505 SELECT cle_id,
1506 amount
1507 FROM oks_bill_sub_lines
1508 WHERE cle_id = p_cle_id;
1509
1510 l_lvl_element_tbl_in oks_bill_level_elements_pvt.letv_tbl_type;
1511 l_lvl_element_tbl_out oks_bill_level_elements_pvt.letv_tbl_type;
1512 l_SLL_tbl_type OKS_BILL_SCH.StreamLvl_tbl; --stream_lvl_tbl;
1513 l_bil_sch_out_tbl OKS_BILL_SCH.ItemBillSch_tbl; --item_bill_sch_tbl;
1514 l_lvl_element_tbl oks_bill_level_elements_pvt.letv_tbl_type;
1515 l_bill_cont_tbl bill_cont_tbl;
1516 l_bill_sub_tbl bill_cont_tbl;
1517
1518 l_adv_arr VARCHAR2(40);
1519 l_rule_id NUMBER := 0;
1520 l_start_date DATE;
1521 l_end_date DATE;
1522 l_duration NUMBER := 0;
1523 l_time VARCHAR2(450) ;
1524 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1525 l_ctr NUMBER := 0;
1526 l_tbl_ctr NUMBER := 0;
1527 l_invoice_rule_id NUMBER :=0;
1528 l_lvl_rec_ctr NUMBER := 0;
1529 l_bill_count NUMBER := 0;
1530 l_bill_ctr NUMBER := 0;
1531 l_lvl_ctr NUMBER := 0;
1532 l_lvl_count NUMBER := 0;
1533 l_lvl_sum NUMBER := 0;
1534 l_bill_sum NUMBER := 0;
1535 l_diff NUMBER :=0;
1536 l_msg_data VARCHAR2(2000);
1537 l_msg_count NUMBER:=0;
1538 l_flag VARCHAR2(1);
1539 l_rl2_flag_null VARCHAR2(1);
1540 l_tbl NUMBER;
1541
1542
1543
1544 BEGIN
1545 l_return_status := OKC_API.G_RET_STS_SUCCESS;
1546 DBMS_TRANSACTION.SAVEPOINT('BEFORE_TRANSACTION');
1547
1548
1549 OPEN top_line_grp_csr ;
1550 LOOP
1551 FETCH top_line_grp_csr bulk collect into l_id ,
1552 l_lse_id ,
1553 l_cle_id ,
1554 l_dnz_chr_id ,
1555 l_start_dt ,
1556 l_end_dt ,
1557 l_date_terminated ,
1558 l_rgp_id ,
1559 l_inv_rul_id LIMIT 10000;
1560
1561
1562
1563
1564
1565 If l_id.count > 0 then
1566 FOR I IN l_id.FIRST .. l_id.LAST
1567 LOOP
1568
1569
1570 IF l_inv_rul_id(i) IS NULL THEN
1571 l_invoice_rule_id := -2;
1572 ELSE
1573 l_invoice_rule_id := to_number(l_inv_rul_id(i));
1574 END IF; -- IRE RULE
1575
1576 l_bill_count := 0;
1577
1578 IF l_date_terminated(i) IS NOT NULL AND
1579 l_date_terminated(i) < sysdate THEN
1580
1581 l_bill_count := 0;
1582
1583 SELECT COUNT(cle_id)
1584 INTO l_bill_count
1585 FROM oks_bill_cont_lines
1586 WHERE cle_id = l_id(i)
1587 AND bill_action = 'RI';
1588
1589 END IF; --IF top_line_date_terminated IS NOT NULL and < sysdate
1590
1591 IF l_bill_count = 0 THEN
1592 l_tbl_ctr :=0;
1593 l_lvl_element_tbl_in.delete;
1594 l_lvl_element_tbl_out.delete;
1595 l_SLL_tbl_type.delete;
1596
1597 FOR rules_rec IN rules_csr (l_rgp_id(i))
1598 LOOP
1599
1600
1601
1602 --Check for one time or reccuring billing
1603 -- check for one time billing
1604
1605 l_rl2_flag_null := 'N';
1606
1607 IF rules_rec.rule_information2 IS NULL THEN
1608 --Call proc one_time_billing
1609 OKS_BILL_MIGRATION.one_time_billing(
1610 p_invoice_rule_id => l_invoice_rule_id
1611 ,p_cle_id => l_id(i)
1612 ,x_return_status => l_return_status);
1613
1614 l_rl2_flag_null := 'Y';
1615
1616
1617 ELSE --for recurring billing
1618 l_ctr := 0;
1619
1620 -- check for bill upto date
1621 IF rules_rec.rule_information3 IS NOT NULL THEN ---billed date not null
1622
1623 l_start_date := l_start_dt(i);
1624 l_end_date := TRUNC(TO_DATE(SUBSTR(rules_rec.rule_information3,1,21),'YYYY/MM/DD HH24:MI:SS'));
1625
1626 okc_time_util_pub.get_duration(
1627 p_start_date => l_start_date,
1628 p_end_date => l_end_date,
1629 x_duration => l_duration,
1630 x_timeunit => l_time,
1631 x_return_status => l_return_status );
1632
1633 IF l_return_status = 'S' THEN
1634
1635 --##create rule1 for bill_upto date in days
1636
1637 l_tbl_ctr := l_tbl_ctr + 1;
1638 --SLL rule
1639
1640
1641 l_SLL_tbl_type(l_tbl_ctr).cle_id := l_id(i);
1642 l_SLL_tbl_type(l_tbl_ctr).sequence_no := l_tbl_ctr;
1643 l_SLL_tbl_type(l_tbl_ctr).level_periods := 1;
1644 l_SLL_tbl_type(l_tbl_ctr).uom_per_period := l_duration;
1645 l_SLL_tbl_type(l_tbl_ctr).level_amount := NULL;
1646 l_SLL_tbl_type(l_tbl_ctr).invoice_offset_days := NVL(rules_rec.rule_information7,0);
1647 l_SLL_tbl_type(l_tbl_ctr).uom_code := l_time;
1648
1649 l_start_date := l_end_date +1;
1650 l_flag := 'F';
1651
1652 END IF;-- IF l_return_status = 'S'
1653
1654 ELSE --if bill upto date is null
1655
1656 l_start_date := l_start_dt(i);
1657 l_end_date := l_start_dt(i);
1658 l_flag := 'T';
1659
1660 END IF; -- IF rules_rec.rule_information3 IS NOT NULL (billed chk)
1661
1662 LOOP
1663
1664 IF l_end_dt(i) <= l_end_date THEN
1665 EXIT;
1666 END IF; -- IF end_dt(i) <= l_end_date
1667
1668 IF l_end_dt(i) > l_end_date THEN
1669
1670 IF l_flag = 'T' THEN
1671
1672 l_end_date := okc_time_util_pub.get_enddate
1673 (l_end_date
1674 ,rules_rec.rule_information2
1675 ,1);
1676 l_end_date := TRUNC(l_end_date);
1677 l_flag := 'F';
1678
1679 ELSE
1680 l_end_date := okc_time_util_pub.get_enddate
1681 (l_end_date + 1
1682 ,rules_rec.rule_information2
1683 ,1);
1684 l_end_date := TRUNC(l_end_date);
1685 END IF; --IF l_flag = 'T'
1686
1687
1688 --(1) check line end date is less than calculate rule end date
1689 IF l_end_dt(i) < l_end_date THEN
1690
1691 l_end_date := l_end_dt(i);
1692 okc_time_util_pub.get_duration(
1693 p_start_date => l_start_date,
1694 p_end_date => l_end_date,
1695 x_duration => l_duration,
1696 x_timeunit => l_time,
1697 x_return_status => l_return_status);
1698
1699 IF l_return_status = 'S' THEN
1700
1701 --## create a rule for a days
1702 l_tbl_ctr := l_tbl_ctr + 1;
1703 --SLL rule
1704
1705 l_SLL_tbl_type(l_tbl_ctr).cle_id := l_id(i);
1706 l_SLL_tbl_type(l_tbl_ctr).sequence_no := l_tbl_ctr;
1707 l_SLL_tbl_type(l_tbl_ctr).level_periods := 1;
1708 l_SLL_tbl_type(l_tbl_ctr).uom_per_period := l_duration;
1709 l_SLL_tbl_type(l_tbl_ctr).level_amount := NULL;
1710 l_SLL_tbl_type(l_tbl_ctr).invoice_offset_days := NVL(rules_rec.rule_information7,0);
1711 l_SLL_tbl_type(l_tbl_ctr).uom_code := l_time;
1712
1713
1714 EXIT;
1715
1716 END IF;-- IF l_return_status = 'S'
1717
1718 END IF; -- IF top_line end_date < l_end_date
1719
1720
1721 --(2) check line end date is equal to calculate rule end date
1722 IF l_end_dt(i) = l_end_date THEN
1723
1724 IF l_ctr = 0 THEN
1725
1726 okc_time_util_pub.get_duration(
1727 p_start_date => l_start_date,
1728 p_end_date => l_end_date,
1729 x_duration => l_duration,
1730 x_timeunit => l_time,
1731 x_return_status => l_return_status);
1732
1733 IF l_return_status = 'S' THEN
1734
1735 --## create a rule for l_ctr period of a UOM : l_start_date to l_end_date
1736 l_tbl_ctr := l_tbl_ctr + 1;
1737 --SLL rule
1738
1739
1740 l_SLL_tbl_type(l_tbl_ctr).cle_id := l_id(i);
1741 l_SLL_tbl_type(l_tbl_ctr).sequence_no := l_tbl_ctr;
1742 l_SLL_tbl_type(l_tbl_ctr).level_periods := 1;
1743 l_SLL_tbl_type(l_tbl_ctr).uom_per_period := l_duration;
1744 l_SLL_tbl_type(l_tbl_ctr).level_amount := NULL;
1745 l_SLL_tbl_type(l_tbl_ctr).invoice_offset_days := NVL(rules_rec.rule_information7,0);
1746 l_SLL_tbl_type(l_tbl_ctr).uom_code := l_time;
1747
1748
1749 END IF;-- IF l_return_status = 'S'
1750 ELSE ---l_ctr <>0
1751
1752 l_ctr := l_ctr +1;
1753 l_SLL_tbl_type(l_tbl_ctr).level_periods := l_ctr;
1754 END IF; --IF l_ctr =0
1755
1756 EXIT;
1757
1758 END IF;-- IF top_line end_date = l_end_date
1759
1760
1761 -- check line end date is greater than rule end date
1762 IF l_end_dt(i) > l_end_date THEN
1763 IF l_ctr = 0 THEN
1764 okc_time_util_pub.get_duration(
1765 p_start_date => l_start_date,
1766 p_end_date => l_end_date,
1767 x_duration => l_duration,
1768 x_timeunit => l_time,
1769 x_return_status => l_return_status);
1770
1771 --##create rule for period of l_ctr time
1772 IF l_return_status = 'S' THEN
1773
1774 IF (l_time = 'QTR' and rules_rec.rule_information2 = 'MTH') THEN
1775
1776 l_duration := l_duration * 3;
1777 l_time := 'MTH';
1778 END IF; -- IF (l_time = 'QTR' and rules_rec.rule_information2 = 'MTH')
1779
1780 l_tbl_ctr := l_tbl_ctr + 1;
1781
1782 l_SLL_tbl_type(l_tbl_ctr).cle_id := l_id(i);
1783 l_SLL_tbl_type(l_tbl_ctr).sequence_no := l_tbl_ctr;
1784 l_SLL_tbl_type(l_tbl_ctr).level_periods := l_duration;
1785 l_SLL_tbl_type(l_tbl_ctr).uom_per_period := 1;
1786 l_SLL_tbl_type(l_tbl_ctr).level_amount := NULL;
1787 l_SLL_tbl_type(l_tbl_ctr).invoice_offset_days := NVL(rules_rec.rule_information7,0);
1788 l_SLL_tbl_type(l_tbl_ctr).uom_code := l_time;
1789
1790
1791 l_ctr := l_ctr + 1;
1792 l_start_date := l_end_date +1;
1793
1794 END IF;-- IF l_return_status = 'S'
1795
1796 ELSE
1797
1798 l_start_date := l_end_date +1;
1799 l_ctr := l_ctr + 1;
1800 l_SLL_tbl_type(l_tbl_ctr).level_periods := l_ctr;
1801
1802 END IF;-- IF l_ctr <=2
1803
1804 END IF; -- IF top_line end_date > l_end_date
1805
1806
1807 END IF; -- IF top_line end_date > l_end_date
1808
1809 END LOOP; --end of loop
1810
1811 END IF; --end if of rules_rec.rule_information2 IS NULL (recurring billing)
1812
1813
1814 --Variable for sub line
1815
1816
1817 END LOOP; --end of loop rules_rec
1818
1819 IF l_rl2_flag_null = 'N' THEN
1820 --check status for create rules then call create_bill_sch_rules
1821
1822 IF l_SLL_tbl_type.COUNT > 0 THEN
1823 -- Call bill API
1824
1825 oks_bill_sch.create_bill_sch_rules(p_billing_type => 'T',
1826 p_sll_tbl => l_SLL_tbl_type,
1827 p_invoice_rule_id => l_invoice_rule_id,
1828 x_bil_sch_out_tbl => l_bil_sch_out_tbl,
1829 x_return_status => l_return_status);
1830
1831
1832 ------check status of create_bill_sch_rules and call update_lvl_element
1833 IF l_return_status = 'S' THEN --status of create_bill_sch_rules
1834
1835 ------ Top line amount will be updated only for usage (lse_id = 12)
1836 IF l_lse_id(i) = 12 THEN
1837
1838 l_bill_count := 0;
1839
1840 SELECT COUNT(cle_id)
1841 INTO l_bill_count
1842 FROM oks_bill_cont_lines
1843 WHERE cle_id = l_id(i)
1844 AND bill_action = 'RI';
1845
1846 IF l_bill_count > 0 THEN
1847 l_lvl_rec_ctr := 0;
1848 l_lvl_element_tbl.delete;
1849
1850 FOR level_elements_rec IN level_elements_csr (l_id(i))
1851 LOOP
1852
1853 l_lvl_rec_ctr := l_lvl_rec_ctr + 1;
1854
1855 l_lvl_element_tbl(l_lvl_rec_ctr).id := level_elements_rec.id;
1856 l_lvl_element_tbl(l_lvl_rec_ctr).sequence_number := level_elements_rec.sequence_number;
1857 l_lvl_element_tbl(l_lvl_rec_ctr).amount := level_elements_rec.amount;
1858 l_lvl_element_tbl(l_lvl_rec_ctr).date_completed := level_elements_rec.date_completed;
1859
1860 END LOOP; -- FOR bill_element_rec IN bill_element_csr
1861
1862
1863 l_tbl_ctr := 0;
1864 l_bill_cont_tbl.delete;
1865
1866 FOR bill_cont_rec IN bill_cont_csr (l_id(i))
1867 LOOP
1868 l_tbl_ctr := l_tbl_ctr + 1;
1869
1870 l_bill_cont_tbl(l_tbl_ctr).cle_id := bill_cont_rec.cle_id;
1871 l_bill_cont_tbl(l_tbl_ctr).amount := bill_cont_rec.amount;
1872
1873 END LOOP; -- FOR bill_cont_rec IN bill_cont_csr
1874
1875
1876 l_tbl_ctr :=0;
1877 l_diff :=0;
1878
1879 IF l_bill_cont_tbl.COUNT <= l_lvl_element_tbl.COUNT THEN
1880
1881 FOR l_bill_ctr IN 1..l_bill_cont_tbl.COUNT
1882 LOOP
1883
1884 l_diff := l_diff + (l_lvl_element_tbl(l_bill_ctr).amount - l_bill_cont_tbl(l_bill_ctr).amount);
1885 l_lvl_element_tbl(l_bill_ctr).amount := l_bill_cont_tbl(l_bill_ctr).amount;
1886 l_lvl_element_tbl(l_bill_ctr).date_completed := sysdate;
1887
1888 END LOOP;-- FOR l_bill_ctr IN 1..l_bill_cont_tbl.COUNT
1889
1890 l_lvl_element_tbl(l_lvl_element_tbl.count).amount := l_lvl_element_tbl(l_lvl_element_tbl.count).amount + l_diff;
1891
1892 END IF; -- IF l_bill_cont_tbl.COUNT < l_lvl_element_tbl.COUNT
1893
1894
1895 IF l_lvl_element_tbl.COUNT > 0 THEN
1896
1897 -----updating records directly
1898
1899 FOR l_tbl in l_lvl_element_tbl.FIRST .. l_lvl_element_tbl.LAST
1900 LOOP
1901 UPDATE OKS_LEVEL_ELEMENTS
1902 SET amount = l_lvl_element_tbl(l_tbl).amount,
1903 date_completed = l_lvl_element_tbl(l_tbl).date_completed
1904 WHERE id = l_lvl_element_tbl(l_tbl).id;
1905 END LOOP;
1906
1907
1908 END IF; --l_lvl_element_tbl.COUNT >0
1909
1910 END IF;-- IF l_bill_count > 0
1911
1912 END IF; --top_line lse_id = 12
1913
1914 ELSE ---create_bill_sch _rules fail
1915
1916 -- x_return_status := l_return_status;
1917 DBMS_TRANSACTION.ROLLBACK_SAVEPOINT('BEFORE_TRANSACTION');
1918
1919 END IF; -- IF l_return_status = 'S' --status of create_bill_sch_rules
1920 END IF;--l_SLL_tbl_type.COUNT > 0
1921
1922 -- check status of top line
1923 IF l_return_status = 'S' THEN
1924 --**********subline loop
1925
1926 FOR line_grp_rec IN line_grp_csr (l_id(i))
1927 LOOP
1928
1929 l_bill_count := 0;
1930
1931 SELECT COUNT(ID)
1932 INTO l_bill_count
1933 FROM oks_bill_sub_lines
1934 WHERE cle_id = line_grp_rec.id;
1935
1936
1937 IF l_bill_count > 0 THEN
1938
1939 l_lvl_rec_ctr := 0;
1940 l_lvl_element_tbl.delete;
1941
1942 FOR level_elements_rec IN level_elements_csr (line_grp_rec.id)
1943 LOOP
1944
1945 l_lvl_rec_ctr := l_lvl_rec_ctr + 1;
1946
1947 l_lvl_element_tbl(l_lvl_rec_ctr).id := level_elements_rec.id;
1948 l_lvl_element_tbl(l_lvl_rec_ctr).sequence_number := level_elements_rec.sequence_number;
1949 l_lvl_element_tbl(l_lvl_rec_ctr).amount := level_elements_rec.amount;
1950 l_lvl_element_tbl(l_lvl_rec_ctr).date_completed := level_elements_rec.date_completed;
1951
1952 END LOOP; -- FOR bill_element_rec IN bill_element_csr
1953
1954
1955 l_tbl_ctr := 0;
1956 l_bill_sub_tbl.delete;
1957 FOR bill_sub_rec IN bill_sub_csr (line_grp_rec.id)
1958 LOOP
1959 l_tbl_ctr := l_tbl_ctr + 1;
1960
1961 l_bill_sub_tbl(l_tbl_ctr).cle_id := bill_sub_rec.cle_id;
1962 l_bill_sub_tbl(l_tbl_ctr).amount := bill_sub_rec.amount;
1963
1964
1965 END LOOP; -- FOR bill_sub_rec IN bill_sub_csr
1966
1967
1968 l_bill_ctr :=0;
1969 l_diff :=0;
1970
1971 IF l_bill_sub_tbl.COUNT <= l_lvl_element_tbl.COUNT THEN
1972
1973 FOR l_bill_ctr IN 1..l_bill_sub_tbl.COUNT
1974 LOOP
1975
1976 l_diff := l_diff + (l_lvl_element_tbl(l_bill_ctr).amount - l_bill_sub_tbl(l_bill_ctr).amount);
1977 l_lvl_element_tbl(l_bill_ctr).amount := l_bill_sub_tbl(l_bill_ctr).amount;
1978 l_lvl_element_tbl(l_bill_ctr).date_completed := sysdate;
1979
1980 END LOOP;-- FOR l_bill_ctr IN 1..l_bill_sub_tbl.COUNT
1981
1982 l_lvl_element_tbl(l_lvl_element_tbl.count).amount := l_lvl_element_tbl(l_lvl_element_tbl.count).amount + l_diff;
1983
1984 END IF; -- IF l_bill_sub_tbl.COUNT < l_lvl_element_tbl.COUNT
1985
1986
1987 IF l_lvl_element_tbl.COUNT > 0 THEN
1988 -----updating records directly
1989
1990 FOR l_tbl in l_lvl_element_tbl.FIRST .. l_lvl_element_tbl.LAST
1991 LOOP
1992 UPDATE OKS_LEVEL_ELEMENTS
1993 SET amount = l_lvl_element_tbl(l_tbl).amount,
1994 date_completed = l_lvl_element_tbl(l_tbl).date_completed
1995 WHERE id = l_lvl_element_tbl(l_tbl).id;
1996 END LOOP;
1997 END IF; -----l_lvl_element_tbl.COUNT > 0
1998
1999 END IF; -- IF l_bill_count > 0
2000
2001 END LOOP; --line_grp_rec
2002 --end subline loop
2003 END IF; --lF l_return_status = 'S'
2004
2005 END IF; -- IF l_bill_count = 0
2006
2007 END IF; --IF l_rl2_flag_null = 'N'
2008
2009 END LOOP; -----main loop end
2010
2011 END IF; ------tbl count chk
2012
2013 EXIT WHEN top_line_grp_csr%NOTFOUND ;
2014 END LOOP; --MAIN LOOP END
2015
2016
2017
2018 COMMIT;
2019
2020
2021
2022 EXCEPTION WHEN OTHERS THEN
2023 DBMS_TRANSACTION.ROLLBACK_SAVEPOINT('BEFORE_TRANSACTION');
2024 l_return_status := OKC_API.G_RET_STS_ERROR;
2025 OKC_API.SET_MESSAGE
2026 (
2027 p_app_name => G_APP_NAME,
2028 p_msg_name => G_UNEXPECTED_ERROR,
2029 p_token1 => G_SQLCODE_TOKEN,
2030 p_token1_value => SQLCODE,
2031 p_token2 => G_SQLERRM_TOKEN,
2032 p_token2_value => SQLERRM
2033 );
2034
2035
2036 END BILL_UPGRADATION;
2037
2038
2039
2040
2041 PROCEDURE CREATE_BILL_DTLS
2042 ( p_dnz_chr_id IN number ,
2043 p_top_line_id in number ,
2044 p_top_line_start_date in date,
2045 p_top_line_end_date in date ,
2046 p_top_line_upg_orig_system_ref in varchar2 ,
2047 p_top_line_upg_orig_system_id in number ,
2048 p_top_line_date_terminated in date ,
2049 x_return_status OUT NOCOPY varchar2 )
2050 IS
2051
2052
2053 CURSOR L_LINES_CSR (P_DNZ_CHR_ID NUMBER )IS
2054 SELECT LINE.ID , LINE.START_DATE , LINE.END_DATE ,UPG_ORIG_SYSTEM_REF , LINE.DATE_TERMINATED
2055 FROM OKC_K_LINES_B LINE
2056 WHERE LINE.DNZ_CHR_ID = P_DNZ_CHR_ID
2057 AND LINE.LSE_ID = 19 ;
2058
2059
2060 CURSOR L_SUB_LINES_CSR (P_CLE_ID NUMBER )IS
2061 SELECT LINE.ID , LINE.START_DATE , LINE.END_DATE , PRICE_NEGOTIATED, UPG_ORIG_SYSTEM_REF , LINE.DATE_TERMINATED
2062 FROM OKC_K_LINES_B LINE
2063 WHERE LINE.CLE_ID = P_CLE_ID
2064 AND LINE.LSE_ID = 25 ;
2065
2066 CURSOR L_GET_BCL_ID_CSR ( P_CLE_ID NUMBER ) IS
2067 SELECT CONT.ID
2068 FROM OKS_BILL_CONT_LINES CONT
2069 WHERE CLE_ID = P_CLE_ID ;
2070
2071 CURSOR L_LINE_AMT_CSR ( P_CLE_ID NUMBER ) IS
2072 SELECT SUM(PRICE_NEGOTIATED)
2073 FROM OKC_K_LINES_B LINE
2074 WHERE LINE.CLE_ID = P_CLE_ID
2075 AND LINE.LSE_ID = 25 ;
2076
2077 CURSOR L_BCL_CONT_LINE_EXISTS_CSR ( P_CLE_ID NUMBER ) IS
2078 SELECT ID
2079 FROM OKS_BILL_CONT_LINES
2080 WHERE CLE_ID = P_CLE_ID ;
2081
2082 CURSOR L_GET_ORDER_NUMBER_CSR ( P_SUB_LINE_ID NUMBER ) IS
2083 SELECT OBJECT1_ID1
2084 FROM OKC_K_REL_OBJS
2085 WHERE CLE_ID = P_SUB_LINE_ID ;
2086
2087
2088 L_LINES_REC L_LINES_CSR%ROWTYPE ;
2089 L_LINE_AMT_REC L_LINE_AMT_CSR%ROWTYPE ;
2090 L_BCL_CONT_LINE_EXISTS_REC L_BCL_CONT_LINE_EXISTS_CSR%ROWTYPE ;
2091 L_GET_ORDER_NUMBER_REC L_GET_ORDER_NUMBER_CSR%ROWTYPE ;
2092
2093 TYPE LEVEL_ID_REC IS RECORD (
2094 L_LEVEL_ID NUMBER );
2095 SUBTYPE LEVEL_ID_TBL IS OKS_BILL_LEVEL_ELEMENTS_PVT.letv_tbl_type ;
2096 L_LEVEL_ID_TBL LEVEL_ID_TBL ;
2097 L_letv_tbl LEVEL_ID_TBL ;
2098
2099
2100
2101 lin_id number;
2102 l_return_status Varchar2(1):= OKC_API.G_RET_STS_SUCCESS;
2103 l_msg_cnt Number;
2104 l_msg_data Varchar2(2000);
2105 l_ar_inv_date Date;
2106 l_line_id number ;
2107 l_calc_sdate Date ;
2108 l_calc_edate Date ;
2109 L_BCL_ID NUMBER ;
2110 bcl_id_sub NUMBER ;
2111 L_LINE_AMOUNT NUMBER := 0 ;
2112 COUNTER NUMBER ;
2113 l_msg_count number;
2114 L_CONTINUE_PROCESSING BOOLEAN :=FALSE ;
2115
2116 INSERT_BCL__EXCEPTION EXCEPTION ;
2117 G_EXCEPTION_HALT_VALIDATION exception ;
2118
2119 SUBTYPE l_bslv_tbl_type_in is OKS_bsl_PVT.bslv_tbl_type;
2120 l_bslv_tbl_in l_bslv_tbl_type_in ;
2121 l_bslv_tbl_out l_bslv_tbl_type_in ;
2122
2123 SUBTYPE l_bclv_tbl_type_in is OKS_bcl_PVT.bclv_tbl_type;
2124 l_bclv_tbl_in l_bclv_tbl_type_in;
2125 l_bclv_tbl_out l_bclv_tbl_type_in;
2126
2127 SUBTYPE l_bcl_tbl_type_in is OKS_bcl_PVT.bclv_tbl_type;
2128 l_bcl_tbl_in l_bcl_tbl_type_in;
2129 l_bcl_tbl_out l_bcl_tbl_type_in;
2130
2131 LEVEL_REC OKS_BILL_LEVEL_ELEMENTS_PVT.LETV_REC_TYPE;
2132 GET_REC OKS_BILL_LEVEL_ELEMENTS_PVT.LETV_REC_TYPE;
2133
2134 TYPE L_TOP_LINE_REC IS RECORD (
2135 TOP_LINE_ID NUMBER
2136 ,UPG_ORIG_SYSTEM_REF VARCHAR2(60)
2137 );
2138
2139 TYPE L_TOP_LINE_TBL IS TABLE OF L_TOP_LINE_REC INDEX BY BINARY_INTEGER ;
2140
2141 TYPE L_SUB_LINE_REC IS RECORD (
2142 SUB_LINE_ID NUMBER
2143 ,UPG_ORIG_SYSTEM_REF VARCHAR2(6)
2144 ,UPG_ORIG_SYSTEM_REF_ID NUMBER
2145 );
2146
2147 TYPE L_SUB_LINE_TBL IS TABLE OF L_SUB_LINE_REC INDEX BY BINARY_INTEGER ;
2148
2149
2150 L_CLEV_TBL_IN OKC_CONTRACT_PUB.CLEV_TBL_TYPE;
2151 L_CLEV_TBL_OUT OKC_CONTRACT_PUB.CLEV_TBL_TYPE;
2152
2153 l_top_line_counter number := 0 ;
2154 l_sub_line_counter number := 0 ;
2155 L_LINE_COUNTER NUMBER := 0 ;
2156
2157 FUNCTION get_seq_id RETURN NUMBER IS
2158 BEGIN
2159 RETURN(okc_p_util.raw_to_number(sys_guid()));
2160 END get_seq_id;
2161
2162
2163 BEGIN
2164 L_BCL_CONT_LINE_EXISTS_REC.ID := NULL ;
2165
2166 OPEN L_BCL_CONT_LINE_EXISTS_CSR ( p_top_line_id ) ;
2167 FETCH L_BCL_CONT_LINE_EXISTS_CSR INTO L_BCL_CONT_LINE_EXISTS_REC ;
2168 CLOSE L_BCL_CONT_LINE_EXISTS_CSR ;
2169
2170 IF L_BCL_CONT_LINE_EXISTS_REC.ID IS NULL THEN
2171
2172 l_bcl_tbl_in(1).CLE_ID := p_top_line_id ;
2173 l_bcl_tbl_in(1).DATE_BILLED_FROM := p_top_line_start_date ;
2174 l_bcl_tbl_in(1).DATE_BILLED_TO := NVL(p_top_line_date_terminated ,p_top_line_end_date ) ;
2175 l_bcl_tbl_in(1).Date_Next_Invoice := NULL;
2176 l_bcl_tbl_in(1).BILL_ACTION := 'RI';
2177 l_bcl_tbl_in(1).sent_yn := 'N';
2178
2179 lin_id := get_seq_id;
2180 bcl_id_sub := lin_id;
2181
2182 insert into oks_bill_cont_lines
2183 (id, cle_id, date_billed_from, date_billed_to, sent_yn, object_version_number,
2184 created_by, creation_date, last_updated_by, last_update_date, bill_action, btn_id)
2185 values
2186 (lin_id, p_top_line_id , p_top_line_start_date, p_top_line_end_date, 'N',
2187 1, 1, sysdate, 1, sysdate, 'RI', -44);
2188
2189 update okc_k_lines_b
2190 set UPG_ORIG_SYSTEM_REF = NVL(p_top_line_UPG_ORIG_SYSTEM_REF, 'ORDER')
2191 where id = p_top_line_id;
2192
2193 FOR L_SUB_LINES_REC IN L_SUB_LINES_CSR ( p_top_line_id )
2194 LOOP
2195
2196 L_LINE_AMOUNT :=L_LINE_AMOUNT + NVL( L_SUB_LINES_REC.PRICE_NEGOTIATED , 0 ) ;
2197 lin_id := get_seq_id;
2198
2199 insert into oks_bill_sub_lines
2200 (id, cle_id, bcl_id, date_billed_from, date_billed_to, amount, object_version_number,
2201 created_by, creation_date, last_updated_by, last_update_date)
2202 values
2203 (lin_id, L_SUB_LINES_REC.ID, bcl_id_sub, L_sub_LINES_REC.START_DATE, L_sub_LINES_REC.END_DATE,
2204 NVL(L_SUB_LINES_REC.PRICE_NEGOTIATED , 0 ),1,1, sysdate,1, sysdate
2205 );
2206
2207 OPEN L_GET_ORDER_NUMBER_CSR ( L_SUB_LINES_REC.ID );
2208 FETCH L_GET_ORDER_NUMBER_CSR INTO L_GET_ORDER_NUMBER_REC ;
2209 CLOSE L_GET_ORDER_NUMBER_CSR ;
2210
2211 update okc_k_lines_b
2212 set UPG_ORIG_SYSTEM_REF = NVL(L_SUB_LINES_REC.UPG_ORIG_SYSTEM_REF, 'ORDER_LINE'),
2213 UPG_ORIG_SYSTEM_REF_ID = L_GET_ORDER_NUMBER_REC.OBJECT1_ID1
2214 where id = L_SUB_LINES_REC.ID;
2215
2216 end loop;
2217
2218 update oks_bill_cont_lines
2219 set amount = l_line_amount
2220 where id = bcl_id_sub;
2221
2222 L_LINE_AMOUNT := 0 ;
2223
2224 L_CONTINUE_PROCESSING := TRUE ;
2225 END IF ;
2226
2227 IF L_CONTINUE_PROCESSING AND L_RETURN_STATUS = OKC_API.G_RET_STS_SUCCESS THEN
2228 UPDATE_OKS_LEVEL_ELEMENTS( P_DNZ_CHR_ID ,
2229 X_RETURN_STATUS );
2230 IF (L_RETURN_STATUS <> 'S') THEN
2231 X_RETURN_STATUS := L_RETURN_STATUS;
2232 Raise G_EXCEPTION_HALT_VALIDATION;
2233 END IF;
2234 END IF ;
2235
2236
2237 X_RETURN_STATUS := L_RETURN_STATUS;
2238
2239 EXCEPTION
2240 WHEN G_EXCEPTION_HALT_VALIDATION THEN
2241 X_RETURN_STATUS := l_return_status ;
2242 WHEN OTHERS THEN
2243 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
2244 OKC_API.set_message(G_APP_NAME, G_UNEXPECTED_ERROR,G_SQLCODE_TOKEN,SQLCODE,G_SQLERRM_TOKEN,SQLERRM);
2245
2246
2247
2248 END CREATE_BILL_DTLS ;
2249
2250
2251
2252
2253 PROCEDURE UPDATE_OKS_LEVEL_ELEMENTS
2254 ( p_dnz_chr_id IN number ,
2255 x_return_status OUT NOCOPY varchar2 ) IS
2256
2257 G_EXCEPTION_HALT_VALIDATION exception ;
2258
2259
2260 BEGIN
2261
2262 update oks_level_elements
2263 set date_completed = sysdate
2264 where dnz_chr_id = p_dnz_chr_id;
2265
2266 X_RETURN_STATUS := 'S' ;
2267
2268 EXCEPTION
2269 WHEN OTHERS THEN
2270 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
2271 OKC_API.set_message(G_APP_NAME, G_UNEXPECTED_ERROR,G_SQLCODE_TOKEN,SQLCODE,G_SQLERRM_TOKEN,SQLERRM);
2272
2273
2274 END UPDATE_OKS_LEVEL_ELEMENTS ;
2275
2276
2277 END OKS_BILL_MIGRATION;