[Home] [Help]
PACKAGE BODY: APPS.OKS_CON_COVERAGE_PVT
Source
1 PACKAGE BODY OKS_CON_COVERAGE_PVT AS
2 /* $Header: OKSRACCB.pls 120.4 2006/07/10 20:48:15 hmnair noship $ */
3
4 -----------------------------------------------------------------------------------------------------------------------*
5
6 --
7 -- Added for 12.0 Coverage Rearch project (JVARGHES)
8 --
9
10 FUNCTION Get_BP_Line_Start_Offset
11 (P_BPL_Id IN NUMBER
12 ,P_SVL_Start IN DATE
13 ,P_BPL_Start IN DATE
14 ,p_Std_Cov_YN IN VARCHAR2) RETURN DATE
15 IS
16
17 CURSOR Lx_Csr_BPL(Cx_BPL_Id IN NUMBER) IS
18 SELECT BPL.Offset_Duration
19 ,BPL.Offset_period
20 FROM Oks_K_Lines_B BPL
21 WHERE BPL.Cle_Id = Cx_BPL_Id;
22
23 Lx_BPL_Id CONSTANT NUMBER := P_BPL_Id;
24 Ld_SVL_Start CONSTANT DATE := P_SVL_Start;
25
26 Ld_BPL_OFS_Start DATE;
27 Ln_BPL_OFS_Duration NUMBER;
28 Lv_BPL_OFS_UOM VARCHAR2(100);
29
30 Lx_Return_Status VARCHAR2(10);
31
32 BEGIN
33
34 Lx_Return_Status := G_RET_STS_SUCCESS;
35
36 IF p_Std_Cov_YN = 'Y' THEN
37
38 OPEN Lx_Csr_BPL(Lx_BPL_Id);
39 FETCH Lx_Csr_BPL INTO Ln_BPL_OFS_Duration,Lv_BPL_OFS_UOM;
40
41 CLOSE Lx_Csr_BPL;
42
43 IF (Lv_BPL_OFS_UOM IS NOT NULL) AND (Ln_BPL_OFS_Duration IS NOT NULL) THEN
44
45 Ld_BPL_OFS_Start := OKC_Time_Util_Pub.Get_EndDate(P_Start_Date => Ld_SVL_Start
46 ,P_Timeunit => Lv_BPL_OFS_UOM
47 ,P_Duration => Ln_BPL_OFS_Duration);
48 Ld_BPL_OFS_Start := Ld_BPL_OFS_Start + 1;
49
50 ELSE
51
52 Ld_BPL_OFS_Start := Ld_SVL_Start;
53
54 END IF;
55
56 ELSE
57
58 Ld_BPL_OFS_Start := p_BPL_Start;
59
60 END IF;
61
62 RETURN(Ld_BPL_OFS_Start);
63
64 EXCEPTION
65
66 WHEN OTHERS THEN
67
68 IF Lx_Csr_BPL%ISOPEN THEN
69 CLOSE Lx_Csr_BPL;
70 END IF;
71
72 OKC_API.SET_MESSAGE
73 (P_App_Name => G_APP_NAME
74 ,P_Msg_Name => G_UNEXPECTED_ERROR
75 ,P_Token1 => G_SQLCODE_TOKEN
76 ,P_Token1_Value => SQLCODE
77 ,P_Token2 => G_SQLERRM_TOKEN
78 ,P_Token2_Value => SQLERRM);
79
80
81 END Get_BP_Line_Start_Offset;
82
83 -----------------------------------------------------------------------------------------------------------------------*
84
85 --
86 -- Added for 12.0 Coverage Rearch project (JVARGHES)
87 --
88
89 FUNCTION Get_grace_end_Date
90 (P_dnz_chr_Id IN NUMBER
91 ,P_SVL_end IN DATE
92 ,P_BPL_end IN DATE
93 ,p_Std_Cov_YN IN VARCHAR2) RETURN DATE
94 IS
95
96 ld_grace_end_date DATE;
97 ld_end_date DATE;
98
99 BEGIN
100
101 IF p_Std_Cov_YN = 'Y' THEN
102 ld_end_date := P_SVL_end;
103 ELSE
104 ld_end_date := P_BPL_end;
105 END IF;
106
107 ld_grace_end_date := get_final_end_date(p_dnz_chr_id,ld_end_date);
108
109 RETURN(ld_grace_end_date);
110
111 EXCEPTION
112
113 WHEN OTHERS THEN
114
115 OKC_API.SET_MESSAGE
116 (P_App_Name => G_APP_NAME
117 ,P_Msg_Name => G_UNEXPECTED_ERROR
118 ,P_Token1 => G_SQLCODE_TOKEN
119 ,P_Token1_Value => SQLCODE
120 ,P_Token2 => G_SQLERRM_TOKEN
121 ,P_Token2_Value => SQLERRM);
122
123 END Get_grace_end_Date;
124
125
126 -----------------------------------------------------------------------------------------------------------------------*
127
128 --Function Added For Bug#1409072
129 -- commented ; warranty to be opened up for bill types and bill rates
130
131
132 --Function Added For Bug#1409072
133 -- commented ; warranty to be opened up for bill types and bill rates
134
135 FUNCTION Get_Warranty_Flag(p_line_id IN Number) Return Varchar2 Is
136
137 Cursor l_csr Is
138 Select 'Y'
139 From okc_k_lines_b
140 where id = p_line_id
141 -- and lse_id = 16;
142 and lse_id = 14;
143
144
145 l_warranty_flag Varchar2(1);
146
147 BEGIN
148
149 l_warranty_flag := 'N';
150
151 Open l_csr;
152 Fetch l_csr INTO l_warranty_flag;
153 Close l_csr;
154
155 Return(l_warranty_flag);
156
157 END Get_Warranty_Flag;
158
159 FUNCTION Get_Full_Discount(p_line_id IN Number,p_business_process_id in number,p_request_date in date,p_txn_grp_id in number) Return Varchar2 Is
160
161 --
162 -- Modified for 12.0 Coverage Rearch project (JVARGHES)
163 --
164 /*
165
166 Cursor l_bp_csr Is
167 Select nvl(oksbpl.allow_bt_discount,'N') --'Y' -- fixed the 11.5.9 bug 3612660
168 From --okc_rules_b rul,
169 --okc_rule_groups_b rgp,
170 okc_k_lines_b bpl,
171 oks_k_lines_b oksbpl, -- 11.5.10 rule rearchitecture changes
172 okc_k_lines_b cov,
173 okc_k_lines_b svl,
174 okc_k_items cimbp
175 where svl.id = p_line_id
176 and cov.cle_id = svl.id
177 and cov.lse_id in (2,15,20)
178 and bpl.cle_id = cov.id
179 -- and rgp.dnz_chr_id = bpl.dnz_chr_id
180 -- and rgp.cle_id = bpl.id
181 and bpl.id = cimbp.cle_id
182 and cimbp.object1_id1 = to_char(p_business_process_id)
183 and trunc(p_request_date) >= trunc(bpl.start_date)
184 and trunc(p_request_date) <= trunc(get_final_end_date(bpl.dnz_chr_id,bpl.end_date))
185 and bpl.id = oksbpl.cle_id;
186 -- and oksbpl.allow_bt_discount is not null;
187 -- and rgp.id = rul.rgp_id
188 -- and rul.rule_information_category = 'BTD'
189
190 -- new cursor added as part of bug 3141819 to take care of backward compatibility
191 -- related to p_txn_grp_id
192
193 Cursor l_txn_grp_csr Is
194 Select nvl(oksbpl.allow_bt_discount,'N') --'Y' -- fixed the 11.5.9 bug 3612660
195 From --okc_rules_b rul,
196 --okc_rule_groups_b rgp,
197 okc_k_lines_b bpl,
198 oks_k_lines_b oksbpl, -- 11.5.10 rule rearchitecture changes
199 okc_k_lines_b cov,
200 okc_k_lines_b svl
201 where svl.id = p_line_id
202 and cov.cle_id = svl.id
203 and cov.lse_id in (2,15,20)
204 and bpl.cle_id = cov.id
205 -- and rgp.dnz_chr_id = bpl.dnz_chr_id
206 -- and rgp.cle_id = bpl.id
207 and bpl.id = p_txn_grp_id --bug 3141819
208 and trunc(p_request_date) >= trunc(bpl.start_date)
209 and trunc(p_request_date) <= trunc(get_final_end_date(bpl.dnz_chr_id,bpl.end_date))
210 and bpl.id = oksbpl.cle_id;
211 -- and oksbpl.allow_bt_discount is not null;
212 -- and rgp.id = rul.rgp_id
213 -- and rul.rule_information_category = 'BTD'
214
215 */
216
217 --
218 -- Modified for 12.0 Coverage Rearch project (JVARGHES)
219 --
220 -- Bug Fix #5369536. Added hint.
221 Cursor l_bp_csr Is
222 Select /*+ leading(svl) use_nl(svl ksl bpl cimbp oksbpl) index(cimbp okc_k_items_n1) */
223 nvl(oksbpl.allow_bt_discount,'N') --'Y' -- fixed the 11.5.9 bug 3612660
224 From okc_k_lines_b svl,
225 oks_k_Lines_b ksl,
226 okc_k_lines_b bpl,
227 okc_k_items cimbp,
228 oks_k_lines_b oksbpl -- 11.5.10 rule rearchitecture changes
229 where svl.id = p_line_id
230 and svl.lse_id in (1,14,19)
231 and ksl.cle_id = svl.id
232 and bpl.cle_id = ksl.coverage_id
233 and bpl.id = cimbp.cle_id
234 and cimbp.object1_id1 = to_char(p_business_process_id)
235 and trunc(p_request_date) >= trunc(Get_BP_Line_Start_Offset(bpl.id,svl.start_date,bpl.start_date,ksl.Standard_Cov_YN))
236 and trunc(p_request_date) <= trunc(Get_grace_end_Date(ksl.dnz_chr_id,svl.end_date,bpl.end_date,ksl.Standard_Cov_YN))
237 and bpl.id = oksbpl.cle_id;
238
239 -- new cursor added as part of bug 3141819 to take care of backward compatibility
240 -- related to p_txn_grp_id
241
242 Cursor l_txn_grp_csr Is
243 Select nvl(oksbpl.allow_bt_discount,'N') --'Y' -- fixed the 11.5.9 bug 3612660
244 From okc_k_lines_b bpl,
245 oks_k_lines_b oksbpl, -- 11.5.10 rule rearchitecture changes
246 okc_k_lines_b svl,
247 oks_k_Lines_b ksl
248 where svl.id = p_line_id
249 and svl.lse_id in (1,14,19)
250 and ksl.cle_id = svl.id
251 and bpl.cle_id = ksl.coverage_id
252 and bpl.id = p_txn_grp_id --bug 3141819
253 and trunc(p_request_date) >= trunc(Get_BP_Line_Start_Offset(bpl.id,svl.start_date,bpl.start_date,ksl.Standard_Cov_YN))
254 and trunc(p_request_date) <= trunc(Get_grace_end_Date(ksl.dnz_chr_id,svl.end_date,bpl.end_date,ksl.Standard_Cov_YN))
255 and bpl.id = oksbpl.cle_id;
256
257 --
258 --
259 l_full_discount_flag Varchar2(1);
260
261 BEGIN
262
263 l_full_discount_flag := 'N';
264
265
266 -- new If clause added as part of bug 3141819 to take care of backward compatibility
267 -- related to p_txn_grp_id
268
269 if p_business_process_id is not null and p_request_date is not null then
270
271 Open l_bp_csr;
272 Fetch l_bp_csr INTO l_full_discount_flag;
273 Close l_bp_csr;
274
275 Return(l_full_discount_flag);
276
277 else
278
279 Open l_txn_grp_csr;
280 Fetch l_txn_grp_csr INTO l_full_discount_flag;
281 Close l_txn_grp_csr;
282
283 Return(l_full_discount_flag);
284
285 end if;
286
287 END Get_Full_Discount;
288
289 PROCEDURE populate_return_table(x_return_status OUT NOCOPY Varchar2,
290 p_as_tbl IN g_work_tbl,
291 p_cover_disc OUT NOCOPY cov_tbl_type)
292 IS
293 j Number;
294 BEGIN
295 j := 1;
296
297 x_return_status := G_RET_STS_SUCCESS;
298
299 FOR i in p_as_tbl.FIRST..p_as_tbl.LAST
300 LOOP
301 p_cover_disc(j).charges_line_number := p_as_tbl(i).charges_line_number;
302 p_cover_disc(j).estimate_detail_id := p_as_tbl(i).estimate_detail_id;
303 p_cover_disc(j).contract_line_id := p_as_tbl(i).contract_line_id;
304 p_cover_disc(j).txn_group_id := p_as_tbl(i).txn_group_id;
305 p_cover_disc(j).billing_type_id := p_as_tbl(i).billing_type_id;
306 p_cover_disc(j).discounted_amount := p_as_tbl(i).discounted_amount;
307 p_cover_disc(j).status := p_as_tbl(j).status;
308
309 p_cover_disc(j).business_process_id := p_as_tbl(i).business_process_id;
310 p_cover_disc(j).request_date := p_as_tbl(j).request_date;
311
312 j := j + 1;
313 END LOOP;
314 EXCEPTION
315 WHEN OTHERS THEN
316 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
317 ,p_msg_name => G_UNEXPECTED_ERROR
318 ,p_token1 => G_SQLCODE_TOKEN
319 ,p_token1_value => SQLcode
320 ,p_token2 => G_SQLERRM_TOKEN
321 ,p_token2_value => SQLerrm);
322
323 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
324 END populate_return_table;
325
326 PROCEDURE populate_bt_table(x_return_status OUT NOCOPY Varchar2,
327 p_txngrp_id IN Number,
328 p_bill_type_id IN Number,
329 p_contract_line_id in number,
330 p_business_process_id IN number,
331 p_request_date IN date,
332 x_out_tbl IN OUT NOCOPY g_out_tbl)
333 IS
334
335 --
336 -- Modified for 12.0 Coverage Rearch project (JVARGHES)
337 --
338 /*
339 CURSOR l_bp_bt_csr IS
340 select btl.ID BTYPE_ID,
341 btl.CLE_ID TXNGRP_ID,
342 oksbtl.discount_amount UPTO_AMT,
343 oksbtl.discount_percent PER_CVD
344 from okc_k_lines_b svl,
345 okc_k_lines_b cov,
346 okc_k_lines_b bpl,
347 okc_k_lines_b btl,
348 oks_k_lines_b oksbtl, --11.5.10 rule reachitecture changes
349 okc_k_items cimbp,
350 okc_k_items cimbt
351 -- okc_rule_groups_b rgp,
352 -- okc_rules_b rul
353 where svl.id = p_contract_line_id
354 and cov.cle_id = svl.id
355 and cov.lse_id in (2,15,20)
356 and bpl.cle_id = cov.id
357 and bpl.id = cimbp.cle_id
358 and cimbp.object1_id1 = to_char(p_business_process_id)
359 and trunc(p_request_date) >= trunc(bpl.start_date)
360 and trunc(p_request_date) <= trunc(get_final_end_date(bpl.dnz_chr_id,bpl.end_date))
361 and btl.cle_id = bpl.id
362 and btl.id = cimbt.cle_id
363 and cimbt.object1_id1 = to_char(p_bill_type_id)
364 and oksbtl.cle_id = btl.id; --11.5.10 addition
365 -- and btl.id = rgp.cle_id
366 -- and rgp.id = rul.rgp_id
367 -- and rul.rule_information_category = 'LMT';
368
369 CURSOR l_txngrp_bt_csr IS
370 select btl.ID BTYPE_ID,
371 btl.CLE_ID TXNGRP_ID,
372 oksbtl.discount_amount UPTO_AMT,
373 oksbtl.discount_percent PER_CVD
374 from okc_k_lines_b svl,
375 okc_k_lines_b cov,
376 okc_k_lines_b bpl,
377 okc_k_lines_b btl,
378 oks_k_lines_b oksbtl, --11.5.10 rule reachitecture changes
379 okc_k_items cimbt
380 -- okc_rule_groups_b rgp,
381 -- okc_rules_b rul
382 where svl.id = p_contract_line_id
383 and cov.cle_id = svl.id
384 and cov.lse_id in (2,15,20)
385 and bpl.cle_id = cov.id
386 and bpl.id = p_txngrp_id
387 and trunc(p_request_date) >= trunc(bpl.start_date)
388 and trunc(p_request_date) <= trunc(get_final_end_date(bpl.dnz_chr_id,bpl.end_date))
389 and btl.cle_id = bpl.id
390 and btl.id = cimbt.cle_id
391 and cimbt.object1_id1 = to_char(p_bill_type_id)
392 and oksbtl.cle_id = btl.id; --11.5.10 addition
393 -- and btl.id = rgp.cle_id
394 -- and rgp.id = rul.rgp_id
395 -- and rul.rule_information_category = 'LMT';
396
397 */
398
399 --
400 -- Modified for 12.0 Coverage Rearch project (JVARGHES)
401 --
402
403
404 CURSOR l_bp_bt_csr IS
405 select btl.ID BTYPE_ID,
406 btl.CLE_ID TXNGRP_ID,
407 oksbtl.discount_amount UPTO_AMT,
408 oksbtl.discount_percent PER_CVD
409 from okc_k_lines_b svl,
410 okc_k_lines_b bpl,
411 okc_k_lines_b btl,
412 oks_k_lines_b oksbtl, --11.5.10 rule reachitecture changes
413 okc_k_items cimbp,
414 okc_k_items cimbt
415 ,OKS_K_LINES_B ksl
416
417 where svl.id = p_contract_line_id
418 and svl.lse_id in (1,14,19)
419 and ksl.cle_id = svl.id
420 and bpl.cle_id = ksl.coverage_id
421 and bpl.id = cimbp.cle_id
422 and cimbp.object1_id1 = to_char(p_business_process_id)
423 and trunc(p_request_date) >= trunc(Get_BP_Line_Start_Offset(bpl.id, svl.start_date, bpl.start_date, KSL.Standard_Cov_YN))
424 and trunc(p_request_date) <= trunc(Get_grace_end_Date(svl.dnz_chr_id, svl.end_date, bpl.end_date,KSL.Standard_Cov_YN))
425 and btl.cle_id = bpl.id
426 and btl.id = cimbt.cle_id
427 and cimbt.object1_id1 = to_char(p_bill_type_id)
428 and oksbtl.cle_id = btl.id; --11.5.10 addition
429
430 CURSOR l_txngrp_bt_csr IS
431 select btl.ID BTYPE_ID,
432 btl.CLE_ID TXNGRP_ID,
433 oksbtl.discount_amount UPTO_AMT,
434 oksbtl.discount_percent PER_CVD
435 from okc_k_lines_b svl,
436 okc_k_lines_b bpl,
437 okc_k_lines_b btl,
438 oks_k_lines_b oksbtl, --11.5.10 rule reachitecture changes
439 okc_k_items cimbt
440 ,OKS_K_LINES_B ksl
441
442 where svl.id = p_contract_line_id
443 and svl.lse_id in (1,14,19)
444 and ksl.cle_id = svl.id
445 and bpl.cle_id = ksl.coverage_id
446 and bpl.id = p_txngrp_id
447 and trunc(p_request_date) >= trunc(Get_BP_Line_Start_Offset(bpl.id, svl.start_date, bpl.start_date, KSL.Standard_Cov_YN))
448 and trunc(p_request_date) <= trunc(Get_grace_end_Date(svl.dnz_chr_id, svl.end_date, bpl.end_date,KSL.Standard_Cov_YN))
449 and btl.cle_id = bpl.id
450 and btl.id = cimbt.cle_id
451 and cimbt.object1_id1 = to_char(p_bill_type_id)
452 and oksbtl.cle_id = btl.id; --11.5.10 addition
453
454 --
455 --
456
457 rec_found Varchar2(1);
458 j Number;
459 BEGIN
460 rec_found := 'F';
461
462 x_return_status := G_RET_STS_SUCCESS;
463
464 IF x_out_tbl.COUNT = 0 THEN
465 j := 1;
466 ELSE
467 j := x_out_tbl.COUNT + 1;
468 END IF;
469
470 -- bug 3141819 .. added new cursor to take care of backward compatibility
471 -- related to p_txngrp_id
472
473 IF p_business_process_id is not null and p_request_date is not null then
474
475 FOR i in l_bp_bt_csr
476 LOOP
477
478 rec_found := 'T';
479
480 if ((j =1) or (j = x_out_tbl.COUNT + 1)) then
481 null;
482 else
483 j := j + 1;
484 end if;
485
486 x_out_tbl(j).txngrp_id := i.txngrp_id;
487 x_out_tbl(j).btype_id := i.btype_id;
488
489 x_out_tbl(j).upto_amt := i.upto_amt;
490 x_out_tbl(j).per_cvd := i.per_cvd;
491
492 -- j := j + 1;
493 END LOOP;
494
495 ELSE
496
497 FOR i in l_txngrp_bt_csr
498 LOOP
499
500 rec_found := 'T';
501
502 if ((j =1) or (j = x_out_tbl.COUNT + 1)) then
503 null;
504 else
505 j := j + 1;
506 end if;
507
508 x_out_tbl(j).txngrp_id := i.txngrp_id;
509 x_out_tbl(j).btype_id := i.btype_id;
510
511 x_out_tbl(j).upto_amt := i.upto_amt;
512 x_out_tbl(j).per_cvd := i.per_cvd;
513
514 -- j := j + 1;
515 END LOOP;
516
517 END IF;
518
519 IF rec_found = 'F' THEN
520 --x_out_tbl(j).txngrp_id := 'F';
521 x_out_tbl(j).status := 'F';
522 ELSIF rec_found = 'T' THEN
523 x_out_tbl(j).status := 'T';
524 END IF;
525
526 EXCEPTION
527 WHEN OTHERS THEN
528 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
529 ,p_msg_name => G_UNEXPECTED_ERROR
530 ,p_token1 => G_SQLCODE_TOKEN
531 ,p_token1_value => SQLcode
532 ,p_token2 => G_SQLERRM_TOKEN
533 ,p_token2_value => SQLerrm);
534
535 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
536 END populate_bt_table;
537
538 PROCEDURE populate_work_table(x_return_status OUT NOCOPY Varchar2,
539 p_as_tbl IN ser_tbl_type,
540 x_as_tbl OUT NOCOPY g_work_tbl)
541 IS
542 j Number;
543 BEGIN
544 j := 1;
545
546 x_return_status := G_RET_STS_SUCCESS;
547
548 FOR i in p_as_tbl.FIRST..p_as_tbl.LAST
549 LOOP
550 x_as_tbl(j).seq_no := j;
551 x_as_tbl(j).Charges_line_number := p_as_tbl(i).Charges_line_number;
552 x_as_tbl(j).estimate_detail_id := p_as_tbl(i).estimate_detail_id;
553 x_as_tbl(j).contract_line_id := p_as_tbl(i).contract_line_id;
554 x_as_tbl(j).txn_group_id := p_as_tbl(i).txn_group_id;
555 x_as_tbl(j).billing_type_id := p_as_tbl(i).billing_type_id;
556 x_as_tbl(j).charge_amount := p_as_tbl(i).charge_amount;
557 x_as_tbl(j).business_process_id := p_as_tbl(i).business_process_id;
558 x_as_tbl(j).request_date := nvl(p_as_tbl(i).request_date,sysdate); --p_as_tbl(i).request_date;
559
560 --Warranty Flag Added For Bug#1409072
561 -- commented ; warranty to be opened up for bill types and bill rates
562 -- x_as_tbl(j).warranty_flag := Get_Warranty_Flag(p_line_id => p_as_tbl(i).txn_group_id);
563
564 x_as_tbl(j).warranty_flag := Get_Warranty_Flag(p_line_id => p_as_tbl(i).contract_line_id);
565 if x_as_tbl(j).warranty_flag = 'Y' then
566 x_as_tbl(j).allow_full_discount := Get_Full_discount(p_as_tbl(i).contract_line_id,p_as_tbl(i).business_process_id
567 ,p_as_tbl(i).request_date,p_as_tbl(i).txn_group_id);
568 end if;
569
570 j := j + 1;
571 END LOOP;
572 EXCEPTION
573 WHEN OTHERS THEN
574 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
575 ,p_msg_name => G_UNEXPECTED_ERROR
576 ,p_token1 => G_SQLCODE_TOKEN
577 ,p_token1_value => SQLcode
578 ,p_token2 => G_SQLERRM_TOKEN
579 ,p_token2_value => SQLerrm);
580
581 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
582 END populate_work_table;
583
584 PROCEDURE apply_contract_coverage
585 (p_api_version IN Number
586 ,p_init_msg_list IN Varchar2
587 ,p_est_amt_tbl IN ser_tbl_type
588 ,x_return_status OUT NOCOPY Varchar2
589 ,x_msg_count OUT NOCOPY Number
590 ,x_msg_data OUT NOCOPY Varchar2
591 ,x_est_discounted_amt_tbl OUT NOCOPY cov_tbl_type)
592 IS
593
594 l_as_tbl g_work_tbl;
595 x_as_tbl g_work_tbl;
596 l_old_tgid Number;
597 l_old_btid Number;
598 x_out_tbl g_out_tbl;
599 l_pre_dis Number;
600 l_dis_amt Number;
601 l_serv_disc ser_tbl_type;
602 l_cover_disc cov_tbl_type;
603
604 BEGIN
605
606 l_old_tgid := -99;
607 l_old_btid := -99;
608
609 x_return_status := G_RET_STS_SUCCESS;
610
611 l_serv_disc := p_est_amt_tbl;
612
613 populate_work_table(x_return_status, l_serv_disc, x_as_tbl);
614
615 IF x_return_status <> G_RET_STS_SUCCESS THEN
616 RAISE G_EXCEPTION_HALT_VALIDATION;
617 END IF;
618 l_as_tbl := x_as_tbl;
619
620 For i in l_as_tbl.FIRST..l_as_tbl.LAST LOOP
621 -- commented ; warranty to be opened up for bill types and bill rates
622 -- IF l_as_tbl(i).warranty_flag <> 'Y' then --Added For Bug#1409072
623
624
625 -- following if clause removed because it is possible to send multiple records with
626 -- the same l_as_tbl(i).txn_group_id
627
628 --IF l_old_tgid <> l_as_tbl(i).txn_group_id THEN
629 -- l_old_tgid := l_as_tbl(i).txn_group_id;
630
631 -- 11.5.9 changes
632
633 populate_bt_table(x_return_status,
634 l_as_tbl(i).txn_group_id,
635 l_as_tbl(i).billing_type_id,
636 l_as_tbl(i).contract_line_id,
637 l_as_tbl(i).business_process_id,
638 l_as_tbl(i).request_date,
639 x_out_tbl);
640
641 IF x_return_status <> G_RET_STS_SUCCESS THEN
642 RAISE G_EXCEPTION_HALT_VALIDATION;
643 END IF;
644 -- END IF;
645 -- commented ; warranty to be opened up for bill types and bill rates
646 -- END IF; --Added For Bug#1409072
647 END LOOP;
648
649 FOR i in l_as_tbl.FIRST..l_as_tbl.LAST
650 LOOP
651 -- commented ; warranty to be opened up for bill types and bill rates
652 -- IF l_as_tbl(i).warranty_flag <> 'Y' then -- Added For Bug#1409072
653
654 IF l_as_tbl(i).warranty_flag <> 'Y' OR -- not a warranty
655 l_as_tbl(i).warranty_flag = 'Y' AND x_out_tbl(i).status <> 'F' then -- warranty with records exist
656
657 IF l_old_btid <> l_as_tbl(i).billing_type_id
658 THEN
659 -- #1750003
660 l_old_btid := l_as_tbl(i).billing_type_id;
661
662 if x_out_tbl(i).per_cvd is NULL and x_out_tbl(i).upto_amt is NULL then
663 l_as_tbl(i).discounted_amount := l_as_tbl(i).charge_amount;
664 elsif x_out_tbl(i).per_cvd = 0 then
665 l_as_tbl(i).discounted_amount := l_as_tbl(i).charge_amount;
666 elsif x_out_tbl(i).upto_amt = 0 then
667 l_as_tbl(i).discounted_amount := l_as_tbl(i).charge_amount;
668 elsif x_out_tbl(i).per_cvd is NULL and x_out_tbl(i).upto_amt is not NULL then
669 l_as_tbl(i).discounted_amount := l_as_tbl(i).charge_amount - x_out_tbl(i).upto_amt;
670 elsif x_out_tbl(i).per_cvd is not NULL and x_out_tbl(i).upto_amt is NULL then
671 l_as_tbl(i).discounted_amount := l_as_tbl(i).charge_amount * (1 - x_out_tbl(i).per_cvd/100);
672 elsif x_out_tbl(i).per_cvd is not NULL and x_out_tbl(i).upto_amt is not NULL then
673 l_dis_amt := l_as_tbl(i).charge_amount * x_out_tbl(i).per_cvd / 100;
674 if l_dis_amt < x_out_tbl(i).upto_amt then
675 l_as_tbl(i).discounted_amount := l_as_tbl(i).charge_amount - l_dis_amt;
676 else
677 l_as_tbl(i).discounted_amount := l_as_tbl(i).charge_amount - x_out_tbl(i).upto_amt;
678 end if;
679 end if;
680
681 --#1853256 if l_as_tbl(i).discounted_amount < 0 then
682 -- l_as_tbl(i).discounted_amount := 0;
683 -- end if;
684
685 ELSE
686 -- #1750003
687 if x_out_tbl(i).per_cvd is NULL and x_out_tbl(i).upto_amt is NULL then
688 l_as_tbl(i).discounted_amount := l_as_tbl(i).charge_amount;
689 elsif x_out_tbl(i).per_cvd = 0 then
690 l_as_tbl(i).discounted_amount := l_as_tbl(i).charge_amount;
691 elsif x_out_tbl(i).upto_amt = 0 then
692 l_as_tbl(i).discounted_amount := l_as_tbl(i).charge_amount;
693 elsif x_out_tbl(i).per_cvd is NULL and x_out_tbl(i).upto_amt is not NULL then
694 l_as_tbl(i).discounted_amount := l_as_tbl(i).charge_amount - x_out_tbl(i).upto_amt;
695 elsif x_out_tbl(i).per_cvd is not NULL and x_out_tbl(i).upto_amt is NULL then
696 l_as_tbl(i).discounted_amount := l_as_tbl(i).charge_amount * (1 - x_out_tbl(i).per_cvd/100);
697 elsif x_out_tbl(i).per_cvd is not NULL and x_out_tbl(i).upto_amt is not NULL then
698 l_dis_amt := l_as_tbl(i).charge_amount * x_out_tbl(i).per_cvd / 100;
699 if l_dis_amt < x_out_tbl(i).upto_amt then
700 l_as_tbl(i).discounted_amount := l_as_tbl(i).charge_amount - l_dis_amt;
701 else
702 l_as_tbl(i).discounted_amount := l_as_tbl(i).charge_amount - x_out_tbl(i).upto_amt;
703 end if;
704 end if;
705
706 --#1853256 if l_as_tbl(i).discounted_amount < 0 then
707 -- l_as_tbl(i).discounted_amount := 0;
708 -- end if;
709
710 END IF;
711 l_as_tbl(i).status := x_out_tbl(i).status;
712 -- commented ; warranty to be opened up for bill types and bill rates
713 -- ELSE --Added For Bug#1409072
714 -- l_as_tbl(i).discounted_amount := 0;
715 -- END IF; --Added For Bug#1409072
716
717 ELSIF l_as_tbl(i).warranty_flag = 'Y' AND x_out_tbl(i).status = 'F' then --warranty and no record exists
718 IF l_as_tbl(i).allow_full_discount = 'Y' then
719 l_as_tbl(i).discounted_amount := 0;
720 ELSE
721 l_as_tbl(i).discounted_amount := l_as_tbl(i).charge_amount;
722 END IF;
723 END IF;
724
725 END LOOP;
726
727 populate_return_table(x_return_status, l_as_tbl, l_cover_disc);
728
729 IF x_return_status <> G_RET_STS_SUCCESS THEN
730 RAISE G_EXCEPTION_HALT_VALIDATION;
731 END IF;
732 x_est_discounted_amt_tbl := l_cover_disc;
733
734 EXCEPTION
735 WHEN G_EXCEPTION_HALT_VALIDATION THEN
736 Null;
737 WHEN OTHERS THEN
738 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
739 ,p_msg_name => G_UNEXPECTED_ERROR
740 ,p_token1 => G_SQLCODE_TOKEN
741 ,p_token1_value => SQLcode
742 ,p_token2 => G_SQLERRM_TOKEN
743 ,p_token2_value => SQLerrm);
744
745 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
746 END apply_contract_coverage;
747
748 PROCEDURE get_bp_pricelist
749 (p_api_version IN Number
750 ,p_init_msg_list IN Varchar2
751 ,p_Contract_line_id IN NUMBER
752 ,p_business_process_id IN NUMBER
753 ,p_request_date IN DATE
754 ,x_return_status OUT NOCOPY Varchar2
755 ,x_msg_count OUT NOCOPY Number
756 ,x_msg_data OUT NOCOPY Varchar2
757 ,x_pricing_tbl OUT NOCOPY PRICING_TBL_TYPE )
758 IS
759
760 /*
761 CURSOR get_bp_pre_dst (p_cle_id in number)IS
762 select rul.rule_information_category ,
763 rul.object1_id1
764 from okc_rules_b rul,
765 okc_rule_groups_b rgp
766 where rgp.id = rul.rgp_id
767 and rul.rule_information_category in ('PRE','DST')
768 and rgp.cle_id = p_cle_id;
769
770 CURSOR get_khdr_pre (p_chr_id in number) IS
771 select rul.rule_information_category ,
772 rul.object1_id1
773 from okc_rules_b rul,
774 okc_rule_groups_b rgp
775 where rgp.id = rul.rgp_id
776 and rul.rule_information_category in ('PRE')
777 and rgp.chr_id = p_chr_id;
778 */
779
780 --
781 -- Modified for 12.0 Coverage Rearch project (JVARGHES)
782 --
783 /*
784 CURSOR get_cov_bp_line(p_contract_line_id in number,p_business_process_id in number,
785 p_request_date in date)
786 IS
787 select bpl.id,
788 bpl.dnz_chr_id,
789 cimbp.object1_id1,
790 bpl.start_date bpl_start_date,
791 bpl.end_date bpl_end_date,
792 bpl.price_list_id bpl_pre,
793 oksbpl.discount_list oksbpl_dst,
794 chr.price_list_id chr_pre
795 from okc_k_lines_b svl,
796 okc_k_lines_b cov,
797 okc_k_lines_b bpl,
798 oks_k_lines_b oksbpl,
799 okc_k_items cimbp,
800 okc_k_headers_b chr -- 11.5.10 addition
801 where svl.id = p_contract_line_id
802 and svl.chr_id = chr.id -- 11.5.10 addition
803 and cov.cle_id = svl.id
804 and cov.lse_id in (2,15,20)
805 and bpl.cle_id = cov.id
806 and oksbpl.cle_id = bpl.id
807 and bpl.id = cimbp.cle_id
808 and cimbp.object1_id1 = nvl(to_char(p_business_process_id),cimbp.object1_id1)
809 and trunc(p_request_date) >= trunc(bpl.start_date)
810 and trunc(p_request_date) <= trunc(get_final_end_date(bpl.dnz_chr_id,bpl.end_date));
811 */
812 --
813 -- Modified for 12.0 Coverage Rearch project (JVARGHES)
814 --
815
816 CURSOR get_cov_bp_line(p_contract_line_id in number,p_business_process_id in number,
817 p_request_date in date)
818 IS
819 select bpl.id,
820 bpl.dnz_chr_id,
821 cimbp.object1_id1,
822 bpl.start_date bpl_start_date,
823 bpl.end_date bpl_end_date,
824 bpl.price_list_id bpl_pre,
825 oksbpl.discount_list oksbpl_dst,
826 chr.price_list_id chr_pre
827 from okc_k_lines_b svl,
828 okc_k_lines_b bpl,
829 oks_k_lines_b oksbpl,
830 okc_k_items cimbp,
831 okc_k_headers_all_b chr -- Modified for 12.0 MOAC project (JVARGHES)
832 , oks_k_lines_b ksl
833 where svl.id = p_contract_line_id
834 and svl.lse_id in (1,14,19)
835 and ksl.cle_id = svl.id
836 and svl.chr_id = chr.id -- 11.5.10 addition
837 and bpl.cle_id = ksl.coverage_id
838 and oksbpl.cle_id = bpl.id
839 and bpl.id = cimbp.cle_id
840 and cimbp.object1_id1 = nvl(to_char(p_business_process_id),cimbp.object1_id1)
841 and trunc(p_request_date) >= trunc(Get_BP_Line_Start_Offset(bpl.id,svl.start_date,bpl.start_date,ksl.Standard_Cov_YN))
842 and trunc(p_request_date) <= trunc(Get_grace_end_Date(ksl.dnz_chr_id,svl.end_date,bpl.end_date,ksl.Standard_Cov_YN));
843
844 --
845 --
846
847 l_request_date date;
848 l_contract_line_id number;
849 l_business_process_id number;
850
851 BEGIN
852
853 l_request_date := nvl(p_request_date,trunc(sysdate));
854 l_contract_line_id := p_contract_line_id;
855 l_business_process_id := p_business_process_id;
856
857
858
859 for cov_bp_line_rec in get_cov_bp_line(l_contract_line_id,l_business_process_id,l_request_date) loop
860
861 x_pricing_tbl(1).contract_line_id := p_contract_line_id;
862 x_pricing_tbl(1).business_process_id := cov_bp_line_rec.object1_id1; --p_business_process_id;
863 x_pricing_tbl(1).BP_start_date := cov_bp_line_rec.bpl_start_date;
864 x_pricing_tbl(1).BP_end_date := cov_bp_line_rec.bpl_end_date;
865 x_pricing_tbl(1).BP_Price_list_id := cov_bp_line_rec.bpl_pre;
866 x_pricing_tbl(1).Contract_Price_list_Id := cov_bp_line_rec.chr_pre;
867 x_pricing_tbl(1).BP_Discount_id := cov_bp_line_rec.oksbpl_dst;
868
869
870 end loop;
871
872
873 x_return_status := G_RET_STS_SUCCESS;
874
875 EXCEPTION
876 WHEN G_EXCEPTION_HALT_VALIDATION THEN
877 Null;
878 WHEN OTHERS THEN
879 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
880 ,p_msg_name => G_UNEXPECTED_ERROR
881 ,p_token1 => G_SQLCODE_TOKEN
882 ,p_token1_value => SQLcode
883 ,p_token2 => G_SQLERRM_TOKEN
884 ,p_token2_value => SQLerrm);
885
886 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
887 END get_bp_pricelist;
888
889 PROCEDURE get_bill_rates
890 (p_api_version IN Number
891 ,p_init_msg_list IN Varchar2
892 ,P_input_br_rec IN INPUT_BR_REC
893 ,P_labor_sch_tbl IN LABOR_SCH_TBL_TYPE
894 ,x_return_status OUT NOCOPY Varchar2
895 ,x_msg_count OUT NOCOPY Number
896 ,x_msg_data OUT NOCOPY Varchar2
897 ,X_bill_rate_tbl OUT NOCOPY BILL_RATE_TBL_TYPE )
898 IS
899
900 --
901 -- Modified for 12.0 Coverage Rearch project (JVARGHES)
902 --
903 /*
904
905 cursor get_btl_id (p_cont_line_id in number,p_busi_proc_id in number,
906 p_txn_bill_type_id in number,p_request_date in date) IS
907 select btl.id btl_id
908 from okc_k_lines_b svl,
909 okc_k_lines_b cov,
910 okc_k_lines_b bpl,
911 okc_k_items cimbp,
912 okc_k_lines_b btl,
913 okc_k_items cimbt
914 where svl.id = p_cont_line_id
915 and cov.cle_id = svl.id
916 and cov.lse_id in (2,15,20)
917 and bpl.cle_id = cov.id
918 and bpl.id = cimbp.cle_id
919 and trunc(nvl(p_request_date,sysdate)) >= trunc(bpl.start_date)
920 and trunc(nvl(p_request_date,sysdate)) <= trunc(get_final_end_date(bpl.dnz_chr_id,bpl.end_date))
921 and cimbp.object1_id1 = to_char(p_busi_proc_id)
922 and btl.cle_id = bpl.id
923 and btl.lse_id in (5,59,23)
924 and btl.id = cimbt.cle_id
925 and cimbt.object1_id1 = to_char(p_txn_bill_type_id);
926 */
927
928 --
929 -- Modified for 12.0 Coverage Rearch project (JVARGHES)
930 --
931
932 cursor get_btl_id (p_cont_line_id in number,p_busi_proc_id in number,
933 p_txn_bill_type_id in number,p_request_date in date) IS
934 select btl.id btl_id
935 from okc_k_lines_b svl,
936 okc_k_lines_b bpl,
937 okc_k_items cimbp,
938 okc_k_lines_b btl,
939 okc_k_items cimbt,
940 oks_k_lines_b ksl
941 where svl.id = p_cont_line_id
942 and svl.lse_id in (1,14,19)
943 and ksl.cle_id = svl.id
944 and bpl.cle_id = ksl.coverage_id
945 and bpl.id = cimbp.cle_id
946 and trunc(nvl(p_request_date,sysdate)) >= trunc(Get_BP_Line_Start_Offset(bpl.id,svl.start_date,bpl.start_date,ksl.Standard_Cov_YN))
947 and trunc(nvl(p_request_date,sysdate)) <= trunc(Get_grace_end_Date(ksl.dnz_chr_id,svl.end_date,bpl.end_date,ksl.Standard_Cov_YN))
948 and cimbp.object1_id1 = to_char(p_busi_proc_id)
949 and btl.cle_id = bpl.id
950 and btl.lse_id in (5,59,23)
951 and btl.id = cimbt.cle_id
952 and cimbt.object1_id1 = to_char(p_txn_bill_type_id);
953
954 --
955 --
956 cursor get_br_sch (p_btl_id in number,p_sunday_flag in varchar2,p_monday_flag in varchar2,
957 p_tuesday_flag in varchar2,p_wednesday_flag in varchar2,
958 p_thursday_flag in varchar2,p_friday_flag in varchar2,
959 p_saturday_flag in varchar2,p_holiday_flag in varchar2) IS
960 select ID,
961 CLE_ID,
962 DNZ_CHR_ID,
963 START_HOUR,
964 START_MINUTE,
965 END_HOUR,
966 END_MINUTE,
967 MONDAY_FLAG,
968 TUESDAY_FLAG,
969 WEDNESDAY_FLAG,
970 THURSDAY_FLAG,
971 FRIDAY_FLAG,
972 SATURDAY_FLAG,
973 SUNDAY_FLAG,
974 OBJECT1_ID1,
975 OBJECT1_ID2,
976 JTOT_OBJECT1_CODE,
977 BILL_RATE_CODE,
978 UOM,
979 FLAT_RATE,
980 PERCENT_OVER_LIST_PRICE,
981 HOLIDAY_YN,
982 BT_CLE_ID
983 from oks_billrate_schedules
984 where bt_cle_id = p_btl_id
985 and (
986 decode(p_sunday_flag,'Y',sunday_flag,'N','#') = decode(p_sunday_flag,'Y','Y','N','#') and
987 decode(p_monday_flag,'Y',monday_flag,'N','#') = decode(p_monday_flag,'Y','Y','N','#') and
988 decode(p_tuesday_flag,'Y',tuesday_flag,'N','#') = decode(p_tuesday_flag,'Y','Y','N','#') and
989 decode(p_wednesday_flag,'Y',wednesday_flag,'N','#') = decode(p_wednesday_flag,'Y','Y','N','#') and
990 decode(p_thursday_flag,'Y',thursday_flag,'N','#') = decode(p_thursday_flag,'Y','Y','N','#') and
991 decode(p_friday_flag,'Y',friday_flag,'N','#') = decode(p_friday_flag,'Y','Y','N','#') and
992 decode(p_saturday_flag,'Y',saturday_flag,'N','#') = decode(p_saturday_flag,'Y','Y','N','#')
993 )
994 and nvl(holiday_yn,'N') = p_holiday_flag
995 order by start_hour,start_minute;
996
997 TYPE br_sch_tbl_type IS TABLE OF get_br_sch%ROWTYPE INDEX BY BINARY_INTEGER;
998 br_sch_tbl br_sch_tbl_type;
999
1000 L_labor_sch_tbl LABOR_SCH_TBL_TYPE;
1001 L_input_br_rec INPUT_BR_REC;
1002
1003 l_bt_cle_id number;
1004 l_wkday_start varchar2(30);
1005 l_wkday_end varchar2(30);
1006 l_wkday_searched varchar2(30);
1007 l_wkday_st_searched varchar2(30);
1008 l_wkday_ed_searched varchar2(30);
1009 l_sunday_flag varchar2(1);
1010 l_monday_flag varchar2(1);
1011 l_tuesday_flag varchar2(1);
1012 l_wednesday_flag varchar2(1);
1013 l_thursday_flag varchar2(1);
1014 l_friday_flag varchar2(1);
1015 l_saturday_flag varchar2(1);
1016
1017 l_holiday_flag varchar2(1);
1018
1019 L_bill_rate_tbl BILL_RATE_TBL_TYPE;
1020 L_bill_rate_tbl1 BILL_RATE_TBL_TYPE; --Bug# 4194507 (JVARGHES)
1021 L_bill_ratesorted_tbl BILL_RATE_TBL_TYPE;
1022
1023 br_ctr number;
1024 l_start_numtime number;
1025 l_end_numtime number;
1026 l_rec_start_numtime number;
1027 l_rec_end_numtime number;
1028 l_prevrec_end_numtime number;
1029 l_prevrec_end_hour number;
1030 l_prevrec_end_minute number;
1031 l_datetime_searched date;
1032 wkday_ctr number;
1033 l_calchour number;
1034 l_calcmin number;
1035 j number;
1036 br_sch_st_edctr number;
1037 br_sch_betwkday_ctr number;
1038 Lx_Result Gx_Boolean;
1039 Lx_Return_Status Gx_Ret_Sts;
1040
1041 L_EXCEP_UNEXPECTED_ERR EXCEPTION;
1042
1043 BEGIN
1044
1045 L_labor_sch_tbl := P_labor_sch_tbl;
1046 L_input_br_rec := P_input_br_rec;
1047
1048 l_bt_cle_id := NULL;
1049 l_wkday_start := NULL;
1050 l_wkday_end := NULL;
1051 l_wkday_searched := NULL;
1052 l_wkday_st_searched := NULL;
1053 l_wkday_ed_searched := NULL;
1054 l_sunday_flag := NULL;
1055 l_monday_flag := NULL;
1056 l_tuesday_flag := NULL;
1057 l_wednesday_flag := NULL;
1058 l_thursday_flag := NULL;
1059 l_friday_flag := NULL;
1060 l_saturday_flag := NULL;
1061
1062 l_holiday_flag := NULL;
1063
1064 br_ctr := 0;
1065 l_start_numtime := 0;
1066 l_end_numtime := 0;
1067 l_rec_start_numtime := 0;
1068 l_rec_end_numtime := 0;
1069 l_prevrec_end_numtime := 0;
1070 l_prevrec_end_hour := 0;
1071 l_prevrec_end_minute := 0;
1072
1073 wkday_ctr := 0;
1074 l_calchour := 0;
1075 l_calcmin := 0;
1076 j := 0;
1077 br_sch_st_edctr := 0;
1078 br_sch_betwkday_ctr := 0;
1079 Lx_Result := G_TRUE;
1080 Lx_Return_Status := G_RET_STS_SUCCESS;
1081
1082
1083 for btl_id_rec in get_btl_id(L_input_br_rec.contract_line_id,L_input_br_rec.Business_process_id,
1084 L_input_br_rec.txn_billing_type_id,L_input_br_rec.request_date) loop
1085
1086 l_bt_cle_id := btl_id_rec.btl_id;
1087
1088 end loop;
1089
1090
1091 for i in L_labor_sch_tbl.FIRST..L_labor_sch_tbl.LAST loop
1092
1093 -- taking care of bill rate schedules whose weekdays are same as weekday of the request start/end datetime
1094
1095 l_wkday_st_searched := to_char(L_labor_sch_tbl(i).start_datetime,'DY');
1096 l_wkday_ed_searched := to_char(L_labor_sch_tbl(i).end_datetime,'DY');
1097
1098 l_start_numtime := to_number(to_char(L_labor_sch_tbl(i).start_datetime,'HH24'))*60+
1099 to_number(to_char(L_labor_sch_tbl(i).start_datetime,'MI'));
1100
1101 l_end_numtime := to_number(to_char(L_labor_sch_tbl(i).end_datetime,'HH24'))*60+
1102 to_number(to_char(L_labor_sch_tbl(i).end_datetime,'MI'));
1103
1104
1105
1106 l_sunday_flag := 'N';
1107 l_monday_flag := 'N';
1108 l_tuesday_flag := 'N';
1109 l_wednesday_flag := 'N';
1110 l_thursday_flag := 'N';
1111 l_friday_flag := 'N';
1112 l_saturday_flag := 'N';
1113
1114 l_holiday_flag := L_labor_sch_tbl(i).Holiday_flag;
1115
1116 if l_wkday_st_searched = 'SUN' then
1117 l_sunday_flag := 'Y';
1118 elsif l_wkday_st_searched = 'MON' then
1119 l_monday_flag := 'Y';
1120 elsif l_wkday_st_searched = 'TUE' then
1121 l_tuesday_flag := 'Y';
1122 elsif l_wkday_st_searched = 'WED' then
1123 l_wednesday_flag := 'Y';
1124 elsif l_wkday_st_searched = 'THU' then
1125 l_thursday_flag := 'Y';
1126 elsif l_wkday_st_searched = 'FRI' then
1127 l_friday_flag := 'Y';
1128 elsif l_wkday_st_searched = 'SAT' then
1129 l_saturday_flag := 'Y';
1130 end if;
1131
1132
1133 for br_sch_Rec in get_br_sch (l_bt_cle_id,l_sunday_flag,l_monday_flag,
1134 l_tuesday_flag,l_wednesday_flag,
1135 l_thursday_flag,l_friday_flag,
1136 l_saturday_flag,l_holiday_flag) loop
1137
1138
1139
1140 l_rec_start_numtime := br_sch_Rec.start_hour*60+br_sch_Rec.start_minute;
1141
1142 l_rec_end_numtime := br_sch_Rec.end_hour*60+br_sch_Rec.end_minute;
1143
1144 if l_wkday_st_searched = l_wkday_ed_searched then
1145
1146 if ( l_start_numtime between l_rec_start_numtime and l_rec_end_numtime) OR
1147 ( l_end_numtime between l_rec_start_numtime and l_rec_end_numtime) OR
1148 ( l_start_numtime <= l_rec_start_numtime and l_end_numtime >= l_rec_end_numtime) then
1149
1150 j := j+1;
1151 br_sch_tbl(j) := br_sch_rec;
1152
1153 end if;
1154
1155 else
1156
1157 if ( l_start_numtime between l_rec_start_numtime and l_rec_end_numtime) OR
1158 ( l_start_numtime <= l_rec_start_numtime ) then
1159
1160 j := j+1;
1161 br_sch_tbl(j) := br_sch_rec;
1162
1163 end if;
1164
1165 end if;
1166
1167 end loop;
1168
1169 br_sch_st_edctr := j;
1170
1171 if l_wkday_st_searched <> l_wkday_ed_searched then
1172
1173
1174 l_sunday_flag := 'N';
1175 l_monday_flag := 'N';
1176 l_tuesday_flag := 'N';
1177 l_wednesday_flag := 'N';
1178 l_thursday_flag := 'N';
1179 l_friday_flag := 'N';
1180 l_saturday_flag := 'N';
1181
1182 if l_wkday_ed_searched = 'SUN' then
1183 l_sunday_flag := 'Y';
1184 elsif l_wkday_ed_searched = 'MON' then
1185 l_monday_flag := 'Y';
1186 elsif l_wkday_ed_searched = 'TUE' then
1187 l_tuesday_flag := 'Y';
1188 elsif l_wkday_ed_searched = 'WED' then
1189 l_wednesday_flag := 'Y';
1190 elsif l_wkday_ed_searched = 'THU' then
1191 l_thursday_flag := 'Y';
1192 elsif l_wkday_ed_searched = 'FRI' then
1193 l_friday_flag := 'Y';
1194 elsif l_wkday_ed_searched = 'SAT' then
1195 l_saturday_flag := 'Y';
1196 end if;
1197
1198 for br_sch_Rec in get_br_sch (l_bt_cle_id,l_sunday_flag,l_monday_flag,
1199 l_tuesday_flag,l_wednesday_flag,
1200 l_thursday_flag,l_friday_flag,
1201 l_saturday_flag,l_holiday_flag) loop
1202
1203
1204 l_rec_start_numtime := br_sch_Rec.start_hour*60+br_sch_Rec.start_minute;
1205
1206 l_rec_end_numtime := br_sch_Rec.end_hour*60+br_sch_Rec.end_minute;
1207
1208 if ( l_end_numtime between l_rec_start_numtime and l_rec_end_numtime) OR
1209 ( l_end_numtime >= l_rec_end_numtime) then
1210
1211 j := j+1;
1212 br_sch_tbl(j) := br_sch_rec;
1213
1214 end if;
1215
1216 end loop;
1217
1218 end if;
1219
1220 j := 0;
1221
1222 if br_sch_tbl.count > 0 then
1223 for k in br_sch_tbl.first..br_sch_tbl.last loop
1224
1225 l_rec_start_numtime := br_sch_tbl(k).start_hour*60+br_sch_tbl(k).start_minute;
1226
1227 l_rec_end_numtime := br_sch_tbl(k).end_hour*60+br_sch_tbl(k).end_minute;
1228
1229 br_ctr := br_ctr + 1;
1230
1231 if k = 1 and br_sch_st_edctr <> 0 then
1232
1233 if l_start_numtime between l_rec_start_numtime and l_rec_end_numtime then
1234
1235 L_bill_rate_tbl(br_ctr).start_datetime := L_labor_sch_tbl(i).start_datetime;
1236
1237 elsif l_start_numtime <= l_rec_start_numtime then
1238
1239 L_bill_rate_tbl(br_ctr).start_datetime :=
1240 to_date(to_char(L_labor_sch_tbl(i).start_datetime,'YYYY/MM/DD')||' '||
1241 to_char(br_sch_tbl(k).start_hour,'09')||':'||to_char(br_sch_tbl(k).start_minute,'09'),
1242 'YYYY/MM/DD HH24:MI');
1243
1244 end if;
1245
1246 elsif k between 2 and (br_sch_st_edctr) then
1247
1248 L_bill_rate_tbl(br_ctr).start_datetime :=
1249 to_date(to_char(L_labor_sch_tbl(i).start_datetime,'YYYY/MM/DD')||' '||
1250 to_char(br_sch_tbl(k).start_hour,'09')||':'||to_char(br_sch_tbl(k).start_minute,'09'),
1251 'YYYY/MM/DD HH24:MI');
1252
1253 elsif k between br_sch_st_edctr+1 and (br_sch_tbl.count) then
1254
1255 L_bill_rate_tbl(br_ctr).start_datetime :=
1256 to_date(to_char(L_labor_sch_tbl(i).end_datetime,'YYYY/MM/DD')||' '||
1257 to_char(br_sch_tbl(k).start_hour,'09')||':'||to_char(br_sch_tbl(k).start_minute,'09'),
1258 'YYYY/MM/DD HH24:MI');
1259
1260 end if;
1261
1262 if (k = br_sch_tbl.COUNT and l_wkday_st_searched = l_wkday_ed_searched ) or -- and bug 3092683
1263 (k = br_sch_tbl.COUNT and l_wkday_st_searched <> l_wkday_ed_searched and
1264 br_sch_st_edctr < br_sch_tbl.COUNT) then
1265
1266 if l_end_numtime between l_rec_start_numtime and l_rec_end_numtime then
1267
1268 L_bill_rate_tbl(br_ctr).end_datetime := L_labor_sch_tbl(i).end_datetime;
1269
1270 elsif l_end_numtime >= l_rec_end_numtime then
1271
1272 L_bill_rate_tbl(br_ctr).end_datetime :=
1273 to_date(to_char(L_labor_sch_tbl(i).end_datetime,'YYYY/MM/DD')||' '||
1274 to_char(br_sch_tbl(k).end_hour,'09')||':'||to_char(br_sch_tbl(k).end_minute,'09'),
1275 'YYYY/MM/DD HH24:MI');
1276
1277 end if;
1278
1279 elsif k between 1 and (br_sch_st_edctr ) then
1280
1281 L_bill_rate_tbl(br_ctr).end_datetime :=
1282 to_date(to_char(L_labor_sch_tbl(i).start_datetime,'YYYY/MM/DD')||' '||
1283 to_char(br_sch_tbl(k).end_hour,'09')||':'||to_char(br_sch_tbl(k).end_minute,'09'),
1284 'YYYY/MM/DD HH24:MI');
1285
1286 elsif k between br_sch_st_edctr+1 and (br_sch_tbl.count) then
1287
1288 L_bill_rate_tbl(br_ctr).end_datetime :=
1289 to_date(to_char(L_labor_sch_tbl(i).end_datetime,'YYYY/MM/DD')||' '||
1290 to_char(br_sch_tbl(k).end_hour,'09')||':'||to_char(br_sch_tbl(k).end_minute,'09'),
1291 'YYYY/MM/DD HH24:MI');
1292
1293 end if;
1294
1295 L_bill_rate_tbl(br_ctr).labor_item_id := to_number(br_sch_tbl(k).object1_id1);
1296 L_bill_rate_tbl(br_ctr).labor_item_org_id := to_number(br_sch_tbl(k).object1_id2);
1297 L_bill_rate_tbl(br_ctr).bill_rate_code := br_sch_tbl(k).bill_rate_code;
1298 L_bill_rate_tbl(br_ctr).flat_rate := br_sch_tbl(k).flat_rate;
1299 L_bill_rate_tbl(br_ctr).flat_rate_uom_code := br_sch_tbl(k).uom;
1300 L_bill_rate_tbl(br_ctr).percent_over_listprice := br_sch_tbl(k).percent_over_list_price;
1301
1302 if k = 1 and l_start_numtime < l_rec_start_numtime and br_sch_st_edctr <> 0 then
1303
1304 br_ctr := br_ctr + 1;
1305
1306 L_bill_rate_tbl(br_ctr).start_datetime := L_labor_sch_tbl(i).start_datetime;
1307
1308 -- Bug# 4746221 (JVORUGAN)
1309 -- l_calchour := trunc(((br_sch_tbl(k).start_hour*60+br_sch_tbl(k).start_minute)-1)/60);
1310 -- l_calcmin := mod((br_sch_tbl(k).start_hour*60+br_sch_tbl(k).start_minute)-1,60);
1311 l_calchour := trunc(((br_sch_tbl(k).start_hour*60+br_sch_tbl(k).start_minute))/60);
1312 l_calcmin := mod((br_sch_tbl(k).start_hour*60+br_sch_tbl(k).start_minute),60);
1313 -- Bug#4746221 (JVORUGAN)
1314
1315 L_bill_rate_tbl(br_ctr).end_datetime :=
1316 to_date(to_char(L_labor_sch_tbl(i).start_datetime,'YYYY/MM/DD')||' '||
1317 to_char(l_calchour,'09')||':'||to_char(l_calcmin,'09'),
1318 'YYYY/MM/DD HH24:MI');
1319
1320 L_bill_rate_tbl(br_ctr).labor_item_id := null;
1321 L_bill_rate_tbl(br_ctr).labor_item_org_id := null;
1322 L_bill_rate_tbl(br_ctr).bill_rate_code := null;
1323 L_bill_rate_tbl(br_ctr).flat_rate := null;
1324 L_bill_rate_tbl(br_ctr).flat_rate_uom_code := null;
1325 L_bill_rate_tbl(br_ctr).percent_over_listprice := null;
1326
1327 elsif (k between 2 and (br_sch_st_edctr )) and
1328 -- (l_rec_end_numtime - l_prevrec_end_numtime > 0) then -- bug 3092683
1329 (l_rec_start_numtime - l_prevrec_end_numtime > 0) then
1330
1331 br_ctr := br_ctr + 1;
1332 -- bug fix 3951896
1333 -- l_calchour := trunc(((l_prevrec_end_hour*60+l_prevrec_end_minute)+1)/60);
1334 -- l_calcmin := mod((l_prevrec_end_hour*60+l_prevrec_end_minute)+1,60);
1335 l_calchour := trunc(((l_prevrec_end_hour*60+l_prevrec_end_minute))/60);
1336 l_calcmin := mod((l_prevrec_end_hour*60+l_prevrec_end_minute),60);
1337
1338 L_bill_rate_tbl(br_ctr).start_datetime :=
1339 to_date(to_char(L_labor_sch_tbl(i).start_datetime,'YYYY/MM/DD')||' '||
1340 to_char(l_calchour,'09')||':'||to_char(l_calcmin,'09'),
1341 'YYYY/MM/DD HH24:MI');
1342
1343 -- bug fix 3951896
1344 -- l_calchour := trunc(((br_sch_tbl(k).start_hour*60+br_sch_tbl(k).start_minute)-1)/60);
1345 -- l_calcmin := mod((br_sch_tbl(k).start_hour*60+br_sch_tbl(k).start_minute)-1,60);
1346
1347 l_calchour := trunc(((br_sch_tbl(k).start_hour*60+br_sch_tbl(k).start_minute))/60);
1348 l_calcmin := mod((br_sch_tbl(k).start_hour*60+br_sch_tbl(k).start_minute),60);
1349
1350 L_bill_rate_tbl(br_ctr).end_datetime :=
1351 to_date(to_char(L_labor_sch_tbl(i).start_datetime,'YYYY/MM/DD')||' '||
1352 to_char(l_calchour,'09')||':'||to_char(l_calcmin,'09'),
1353 'YYYY/MM/DD HH24:MI');
1354
1355 L_bill_rate_tbl(br_ctr).labor_item_id := null;
1356 L_bill_rate_tbl(br_ctr).labor_item_org_id := null;
1357 L_bill_rate_tbl(br_ctr).bill_rate_code := null;
1358 L_bill_rate_tbl(br_ctr).flat_rate := null;
1359 L_bill_rate_tbl(br_ctr).flat_rate_uom_code := null;
1360 L_bill_rate_tbl(br_ctr).percent_over_listprice := null;
1361
1362 elsif k = br_sch_st_edctr + 1 and l_rec_start_numtime > 0 then
1363
1364 br_ctr := br_ctr + 1;
1365
1366 L_bill_rate_tbl(br_ctr).start_datetime :=
1367 to_date(to_char(L_labor_sch_tbl(i).end_datetime,'YYYY/MM/DD')||' '||
1368 '00'||':'||'00','YYYY/MM/DD HH24:MI');
1369 --bug#4746221 (JVORUGAN)
1370 -- l_calchour := trunc(((br_sch_tbl(k).start_hour*60+br_sch_tbl(k).start_minute)-1)/60);
1371 -- l_calcmin := mod((br_sch_tbl(k).start_hour*60+br_sch_tbl(k).start_minute)-1,60);
1372 l_calchour := trunc(((br_sch_tbl(k).start_hour*60+br_sch_tbl(k).start_minute))/60);
1373 l_calcmin := mod((br_sch_tbl(k).start_hour*60+br_sch_tbl(k).start_minute),60);
1374 --bug#4746221 (JVORUGAN)
1375
1376
1377 L_bill_rate_tbl(br_ctr).end_datetime :=
1378 to_date(to_char(L_labor_sch_tbl(i).end_datetime,'YYYY/MM/DD')||' '||
1379 to_char(l_calchour,'09')||':'||to_char(l_calcmin,'09'),
1380 'YYYY/MM/DD HH24:MI');
1381
1382 L_bill_rate_tbl(br_ctr).labor_item_id := null;
1383 L_bill_rate_tbl(br_ctr).labor_item_org_id := null;
1384 L_bill_rate_tbl(br_ctr).bill_rate_code := null;
1385 L_bill_rate_tbl(br_ctr).flat_rate := null;
1386 L_bill_rate_tbl(br_ctr).flat_rate_uom_code := null;
1387 L_bill_rate_tbl(br_ctr).percent_over_listprice := null;
1388
1389
1390 elsif (k between (br_sch_st_edctr + 1) and (br_sch_tbl.count)) and
1391 -- (l_rec_end_numtime - l_prevrec_end_numtime > 0) then -- bug 3092683
1392 (l_rec_start_numtime - l_prevrec_end_numtime > 0) then
1393
1394
1395 br_ctr := br_ctr + 1;
1396 --bug#4746221 (JVORUGAN)
1397 -- l_calchour := trunc(((l_prevrec_end_hour*60+l_prevrec_end_minute)+1)/60);
1398 -- l_calcmin := mod((l_prevrec_end_hour*60+l_prevrec_end_minute)+1,60);
1399 l_calchour := trunc(((l_prevrec_end_hour*60+l_prevrec_end_minute))/60);
1400 l_calcmin := mod((l_prevrec_end_hour*60+l_prevrec_end_minute),60);
1401 --bug#4746221 (JVORUGAN)
1402
1403 L_bill_rate_tbl(br_ctr).start_datetime :=
1404 to_date(to_char(L_labor_sch_tbl(i).end_datetime,'YYYY/MM/DD')||' '||
1405 to_char(l_calchour,'09')||':'||to_char(l_calcmin,'09'),
1406 'YYYY/MM/DD HH24:MI');
1407 --bug#4746221 (JVORUGAN)
1408 -- l_calchour := trunc(((br_sch_tbl(k).start_hour*60+br_sch_tbl(k).start_minute)-1)/60);
1409 -- l_calcmin := mod((br_sch_tbl(k).start_hour*60+br_sch_tbl(k).start_minute)-1,60);
1410 l_calchour := trunc(((br_sch_tbl(k).start_hour*60+br_sch_tbl(k).start_minute))/60);
1411 l_calcmin := mod((br_sch_tbl(k).start_hour*60+br_sch_tbl(k).start_minute),60);
1412 --bug#4746221 (JVORUGAN)
1413
1414
1415 L_bill_rate_tbl(br_ctr).end_datetime :=
1416 to_date(to_char(L_labor_sch_tbl(i).end_datetime,'YYYY/MM/DD')||' '||
1417 to_char(l_calchour,'09')||':'||to_char(l_calcmin,'09'),
1418 'YYYY/MM/DD HH24:MI');
1419
1420 L_bill_rate_tbl(br_ctr).labor_item_id := null;
1421 L_bill_rate_tbl(br_ctr).labor_item_org_id := null;
1422 L_bill_rate_tbl(br_ctr).bill_rate_code := null;
1423 L_bill_rate_tbl(br_ctr).flat_rate := null;
1424 L_bill_rate_tbl(br_ctr).flat_rate_uom_code := null;
1425 L_bill_rate_tbl(br_ctr).percent_over_listprice := null;
1426
1427 end if;
1428
1429 if (k = br_sch_tbl.COUNT and l_wkday_st_searched = l_wkday_ed_searched and
1430 l_rec_end_numtime < l_end_numtime ) or -- bug 3092683
1431 (k = br_sch_tbl.COUNT and l_wkday_st_searched <> l_wkday_ed_searched and
1432 br_sch_st_edctr < br_sch_tbl.COUNT) and
1433 l_rec_end_numtime < l_end_numtime then
1434
1435
1436 br_ctr := br_ctr + 1;
1437 --bug#4746221 (JVORUGAN)
1438 -- l_calchour := trunc(((br_sch_tbl(k).end_hour*60+br_sch_tbl(k).end_minute)+1)/60);
1439 -- l_calcmin := mod((br_sch_tbl(k).end_hour*60+br_sch_tbl(k).end_minute)+1,60);
1440 l_calchour := trunc(((br_sch_tbl(k).end_hour*60+br_sch_tbl(k).end_minute))/60);
1441 l_calcmin := mod((br_sch_tbl(k).end_hour*60+br_sch_tbl(k).end_minute),60);
1442 --bug#4746221 (JVORUGAN)
1443
1444
1445
1446 L_bill_rate_tbl(br_ctr).start_datetime :=
1447 to_date(to_char(L_labor_sch_tbl(i).end_datetime,'YYYY/MM/DD')||' '||
1448 to_char(l_calchour,'09')||':'||to_char(l_calcmin,'09'),
1449 'YYYY/MM/DD HH24:MI');
1450
1451 L_bill_rate_tbl(br_ctr).end_datetime := L_labor_sch_tbl(i).end_datetime;
1452
1453 L_bill_rate_tbl(br_ctr).labor_item_id := null;
1454 L_bill_rate_tbl(br_ctr).labor_item_org_id := null;
1455 L_bill_rate_tbl(br_ctr).bill_rate_code := null;
1456 L_bill_rate_tbl(br_ctr).flat_rate := null;
1457 L_bill_rate_tbl(br_ctr).flat_rate_uom_code := null;
1458 L_bill_rate_tbl(br_ctr).percent_over_listprice := null;
1459
1460 end if;
1461
1462 if k = br_sch_st_edctr and
1463 l_wkday_st_searched <> l_wkday_ed_searched and
1464 l_rec_end_numtime < 1439 then
1465
1466 br_ctr := br_ctr + 1;
1467 --bug#4746221 (JVORUGAN)
1468 -- l_calchour := trunc(((br_sch_tbl(k).end_hour*60+br_sch_tbl(k).end_minute)+1)/60);
1469 -- l_calcmin := mod((br_sch_tbl(k).end_hour*60+br_sch_tbl(k).end_minute)+1,60);
1470 l_calchour := trunc(((br_sch_tbl(k).end_hour*60+br_sch_tbl(k).end_minute))/60);
1471 l_calcmin := mod((br_sch_tbl(k).end_hour*60+br_sch_tbl(k).end_minute),60);
1472 --bug#4746221 (JVORUGAN)
1473
1474 L_bill_rate_tbl(br_ctr).start_datetime :=
1475 to_date(to_char(L_labor_sch_tbl(i).start_datetime,'YYYY/MM/DD')||' '||
1476 to_char(l_calchour,'09')||':'||to_char(l_calcmin,'09'),
1477 'YYYY/MM/DD HH24:MI');
1478
1479 L_bill_rate_tbl(br_ctr).end_datetime :=
1480 to_date(to_char(L_labor_sch_tbl(i).start_datetime,'YYYY/MM/DD')||' '||
1481 '23'||':'||'59','YYYY/MM/DD HH24:MI');
1482
1483 L_bill_rate_tbl(br_ctr).labor_item_id := null;
1484 L_bill_rate_tbl(br_ctr).labor_item_org_id := null;
1485 L_bill_rate_tbl(br_ctr).bill_rate_code := null;
1486 L_bill_rate_tbl(br_ctr).flat_rate := null;
1487 L_bill_rate_tbl(br_ctr).flat_rate_uom_code := null;
1488 L_bill_rate_tbl(br_ctr).percent_over_listprice := null;
1489
1490 end if;
1491
1492 l_prevrec_end_numtime := l_rec_end_numtime;
1493 l_prevrec_end_hour := br_sch_tbl(k).end_hour;
1494 l_prevrec_end_minute := br_sch_tbl(k).end_minute;
1495
1496 end loop;
1497
1498
1499 end if;
1500
1501 if br_sch_st_edctr = 0 and l_wkday_st_searched <> l_wkday_ed_searched then
1502
1503 br_ctr := br_ctr + 1;
1504
1505 L_bill_rate_tbl(br_ctr).start_datetime := L_labor_sch_tbl(i).start_datetime;
1506
1507 L_bill_rate_tbl(br_ctr).end_datetime :=
1508 to_date(to_char(L_labor_sch_tbl(i).start_datetime,'YYYY/MM/DD')||' '||
1509 '23'||':'||'59','YYYY/MM/DD HH24:MI');
1510
1511 L_bill_rate_tbl(br_ctr).labor_item_id := null;
1512 L_bill_rate_tbl(br_ctr).labor_item_org_id := null;
1513 L_bill_rate_tbl(br_ctr).bill_rate_code := null;
1514 L_bill_rate_tbl(br_ctr).flat_rate := null;
1515 L_bill_rate_tbl(br_ctr).flat_rate_uom_code := null;
1516 L_bill_rate_tbl(br_ctr).percent_over_listprice := null;
1517
1518 end if;
1519
1520 if br_sch_tbl.count = br_sch_st_edctr and l_wkday_st_searched <> l_wkday_ed_searched then
1521
1522 br_ctr := br_ctr + 1;
1523
1524 L_bill_rate_tbl(br_ctr).start_datetime :=
1525 to_date(to_char(L_labor_sch_tbl(i).end_datetime,'YYYY/MM/DD')||' '||
1526 '00'||':'||'00','YYYY/MM/DD HH24:MI');
1527
1528 L_bill_rate_tbl(br_ctr).end_datetime := L_labor_sch_tbl(i).end_datetime;
1529
1530 L_bill_rate_tbl(br_ctr).labor_item_id := null;
1531 L_bill_rate_tbl(br_ctr).labor_item_org_id := null;
1532 L_bill_rate_tbl(br_ctr).bill_rate_code := null;
1533 L_bill_rate_tbl(br_ctr).flat_rate := null;
1534 L_bill_rate_tbl(br_ctr).flat_rate_uom_code := null;
1535 L_bill_rate_tbl(br_ctr).percent_over_listprice := null;
1536
1537 end if;
1538
1539 if br_sch_tbl.count = 0 and l_wkday_st_searched = l_wkday_ed_searched then
1540
1541 br_ctr := br_ctr + 1;
1542
1543 L_bill_rate_tbl(br_ctr).start_datetime := L_labor_sch_tbl(i).start_datetime;
1544 L_bill_rate_tbl(br_ctr).end_datetime := L_labor_sch_tbl(i).end_datetime;
1545
1546 L_bill_rate_tbl(br_ctr).labor_item_id := null;
1547 L_bill_rate_tbl(br_ctr).labor_item_org_id := null;
1548 L_bill_rate_tbl(br_ctr).bill_rate_code := null;
1549 L_bill_rate_tbl(br_ctr).flat_rate := null;
1550 L_bill_rate_tbl(br_ctr).flat_rate_uom_code := null;
1551 L_bill_rate_tbl(br_ctr).percent_over_listprice := null;
1552
1553
1554 end if;
1555
1556 -- taking care of bill rate schedules whose weekdays fall in between weekdays of the request start/end datetime
1557
1558 if ((to_char(L_labor_sch_tbl(i).start_datetime,'DY') <> to_char(L_labor_sch_tbl(i).end_datetime,'DY')) AND
1559 (get_next_wkday(to_char(L_labor_sch_tbl(i).start_datetime,'DY'))
1560 <> to_char(L_labor_sch_tbl(i).end_datetime,'DY'))) then
1561
1562 wkday_ctr := 1;
1563
1564 WHILE (1=1) LOOP
1565
1566 l_datetime_searched := L_labor_sch_tbl(i).start_datetime + wkday_ctr;
1567
1568 l_wkday_searched := to_char(l_datetime_searched,'DY');
1569
1570 l_sunday_flag := 'N';
1571 l_monday_flag := 'N';
1572 l_tuesday_flag := 'N';
1573 l_wednesday_flag := 'N';
1574 l_thursday_flag := 'N';
1575 l_friday_flag := 'N';
1576 l_saturday_flag := 'N';
1577
1578 l_holiday_flag := L_labor_sch_tbl(i).Holiday_flag;
1579
1580 if l_wkday_searched = 'SUN' then
1581 l_sunday_flag := 'Y';
1582 elsif l_wkday_searched = 'MON' then
1583 l_monday_flag := 'Y';
1584 elsif l_wkday_searched = 'TUE' then
1585 l_tuesday_flag := 'Y';
1586 elsif l_wkday_searched = 'WED' then
1587 l_wednesday_flag := 'Y';
1588 elsif l_wkday_searched = 'THU' then
1589 l_thursday_flag := 'Y';
1590 elsif l_wkday_searched = 'FRI' then
1591 l_friday_flag := 'Y';
1592 elsif l_wkday_searched = 'SAT' then
1593 l_saturday_flag := 'Y';
1594 end if;
1595
1596 j := 0;
1597 br_sch_tbl.DELETE;
1598
1599 for br_sch_Rec in get_br_sch (l_bt_cle_id,l_sunday_flag,l_monday_flag,
1600 l_tuesday_flag,l_wednesday_flag,
1601 l_thursday_flag,l_friday_flag,
1602 l_saturday_flag,l_holiday_flag) loop
1603
1604 j := j+1;
1605 br_sch_tbl(j) := br_sch_rec;
1606
1607 end loop;
1608
1609 if br_sch_tbl.count > 0 then
1610 for k in br_sch_tbl.first..br_sch_tbl.last loop
1611
1612 l_rec_start_numtime := br_sch_tbl(k).start_hour*60+br_sch_tbl(k).start_minute;
1613
1614 l_rec_end_numtime := br_sch_tbl(k).end_hour*60+br_sch_tbl(k).end_minute;
1615
1616 br_ctr := br_ctr + 1;
1617
1618 L_bill_rate_tbl(br_ctr).start_datetime :=
1619 to_date(to_char(l_datetime_searched,'YYYY/MM/DD')||' '||
1620 to_char(br_sch_tbl(k).start_hour,'09')||':'||to_char(br_sch_tbl(k).start_minute,'09'),
1621 'YYYY/MM/DD HH24:MI');
1622
1623 L_bill_rate_tbl(br_ctr).end_datetime :=
1624 to_date(to_char(l_datetime_searched,'YYYY/MM/DD')||' '||
1625 to_char(br_sch_tbl(k).end_hour,'09')||':'||to_char(br_sch_tbl(k).end_minute,'09'),
1626 'YYYY/MM/DD HH24:MI');
1627
1628
1629 L_bill_rate_tbl(br_ctr).labor_item_id := to_number(br_sch_tbl(k).object1_id1);
1630 L_bill_rate_tbl(br_ctr).labor_item_org_id := to_number(br_sch_tbl(k).object1_id2);
1631 L_bill_rate_tbl(br_ctr).bill_rate_code := br_sch_tbl(k).bill_rate_code;
1632 L_bill_rate_tbl(br_ctr).flat_rate := br_sch_tbl(k).flat_rate;
1633 L_bill_rate_tbl(br_ctr).flat_rate_uom_code := br_sch_tbl(k).uom;
1634 L_bill_rate_tbl(br_ctr).percent_over_listprice := br_sch_tbl(k).percent_over_list_price;
1635
1636 if k = 1 and l_rec_start_numtime > 0 then
1637
1638 br_ctr := br_ctr + 1;
1639
1640 L_bill_rate_tbl(br_ctr).start_datetime :=
1641 to_date(to_char(l_datetime_searched,'YYYY/MM/DD')||' '||
1642 '00'||':'||'00','YYYY/MM/DD HH24:MI');
1643 --bug#4746221 (JVORUGAN)
1644 -- l_calchour := trunc(((br_sch_tbl(k).start_hour*60+br_sch_tbl(k).start_minute)-1)/60);
1645 -- l_calcmin := mod((br_sch_tbl(k).start_hour*60+br_sch_tbl(k).start_minute)-1,60);
1646 l_calchour := trunc(((br_sch_tbl(k).start_hour*60+br_sch_tbl(k).start_minute))/60);
1647 l_calcmin := mod((br_sch_tbl(k).start_hour*60+br_sch_tbl(k).start_minute),60);
1648 --bug#4746221 (JVORUGAN)
1649
1650
1651 L_bill_rate_tbl(br_ctr).end_datetime :=
1652 to_date(to_char(l_datetime_searched,'YYYY/MM/DD')||' '||
1653 to_char(l_calchour,'09')||':'||to_char(l_calcmin,'09'),
1654 'YYYY/MM/DD HH24:MI');
1655
1656 L_bill_rate_tbl(br_ctr).labor_item_id := null;
1657 L_bill_rate_tbl(br_ctr).labor_item_org_id := null;
1658 L_bill_rate_tbl(br_ctr).bill_rate_code := null;
1659 L_bill_rate_tbl(br_ctr).flat_rate := null;
1660 L_bill_rate_tbl(br_ctr).flat_rate_uom_code := null;
1661 L_bill_rate_tbl(br_ctr).percent_over_listprice := null;
1662
1663 elsif (k between 2 and (br_sch_tbl.count )) and
1664 (l_rec_end_numtime - l_prevrec_end_numtime > 0) then
1665
1666 br_ctr := br_ctr + 1;
1667 --bug#4746221 (JVORUGAN)
1668
1669 -- l_calchour := trunc(((l_prevrec_end_hour*60+l_prevrec_end_minute)+1)/60);
1670 -- l_calcmin := mod((l_prevrec_end_hour*60+l_prevrec_end_minute)+1,60);
1671 l_calchour := trunc(((l_prevrec_end_hour*60+l_prevrec_end_minute))/60);
1672 l_calcmin := mod((l_prevrec_end_hour*60+l_prevrec_end_minute),60);
1673 --bug#4746221 (JVORUGAN)
1674
1675
1676 L_bill_rate_tbl(br_ctr).start_datetime :=
1677 to_date(to_char(l_datetime_searched,'YYYY/MM/DD')||' '||
1678 to_char(l_calchour,'09')||':'||to_char(l_calcmin,'09'),
1679 'YYYY/MM/DD HH24:MI');
1680
1681 --bug#4746221 (JVORUGAN)
1682 -- l_calchour := trunc(((br_sch_tbl(k).start_hour*60+br_sch_tbl(k).start_minute)-1)/60);
1683 -- l_calcmin := mod((br_sch_tbl(k).start_hour*60+br_sch_tbl(k).start_minute)-1,60);
1684 l_calchour := trunc(((br_sch_tbl(k).start_hour*60+br_sch_tbl(k).start_minute))/60);
1685 l_calcmin := mod((br_sch_tbl(k).start_hour*60+br_sch_tbl(k).start_minute),60);
1686 --bug#4746221 (JVORUGAN)
1687
1688
1689 L_bill_rate_tbl(br_ctr).end_datetime :=
1690 to_date(to_char(l_datetime_searched,'YYYY/MM/DD')||' '||
1691 to_char(l_calchour,'09')||':'||to_char(l_calcmin,'09'),
1692 'YYYY/MM/DD HH24:MI');
1693
1694 L_bill_rate_tbl(br_ctr).labor_item_id := null;
1695 L_bill_rate_tbl(br_ctr).labor_item_org_id := null;
1696 L_bill_rate_tbl(br_ctr).bill_rate_code := null;
1697 L_bill_rate_tbl(br_ctr).flat_rate := null;
1698 L_bill_rate_tbl(br_ctr).flat_rate_uom_code := null;
1699 L_bill_rate_tbl(br_ctr).percent_over_listprice := null;
1700
1701 end if;
1702
1703 if k = br_sch_tbl.count and l_rec_end_numtime < 1439 then
1704
1705 br_ctr := br_ctr + 1;
1706 --bug#4746221 (JVORUGAN)
1707 -- l_calchour := trunc(((br_sch_tbl(k).end_hour*60+br_sch_tbl(k).end_minute)+1)/60);
1708 -- l_calcmin := mod((br_sch_tbl(k).end_hour*60+br_sch_tbl(k).end_minute)+1,60);
1709 l_calchour := trunc(((br_sch_tbl(k).end_hour*60+br_sch_tbl(k).end_minute))/60);
1710 l_calcmin := mod((br_sch_tbl(k).end_hour*60+br_sch_tbl(k).end_minute),60);
1711 --bug#4746221 (JVORUGAN)
1712
1713 L_bill_rate_tbl(br_ctr).start_datetime :=
1714 to_date(to_char(l_datetime_searched,'YYYY/MM/DD')||' '||
1715 to_char(l_calchour,'09')||':'||to_char(l_calcmin,'09'),
1716 'YYYY/MM/DD HH24:MI');
1717
1718 L_bill_rate_tbl(br_ctr).end_datetime :=
1719 to_date(to_char(l_datetime_searched,'YYYY/MM/DD')||' '||
1720 '23'||':'||'59','YYYY/MM/DD HH24:MI');
1721
1722 L_bill_rate_tbl(br_ctr).labor_item_id := null;
1723 L_bill_rate_tbl(br_ctr).labor_item_org_id := null;
1724 L_bill_rate_tbl(br_ctr).bill_rate_code := null;
1725 L_bill_rate_tbl(br_ctr).flat_rate := null;
1726 L_bill_rate_tbl(br_ctr).flat_rate_uom_code := null;
1727 L_bill_rate_tbl(br_ctr).percent_over_listprice := null;
1728
1729 end if;
1730
1731
1732 l_prevrec_end_numtime := l_rec_end_numtime;
1733 l_prevrec_end_hour := br_sch_tbl(k).end_hour;
1734 l_prevrec_end_minute := br_sch_tbl(k).end_minute;
1735
1736
1737 end loop;
1738 end if;
1739
1740 if br_sch_tbl.count = 0 then
1741
1742 br_ctr := br_ctr + 1;
1743
1744 L_bill_rate_tbl(br_ctr).start_datetime :=
1745 to_date(to_char(l_datetime_searched,'YYYY/MM/DD')||' '||
1746 '00'||':'||'00','YYYY/MM/DD HH24:MI');
1747
1748 L_bill_rate_tbl(br_ctr).end_datetime :=
1749 to_date(to_char(l_datetime_searched,'YYYY/MM/DD')||' '||
1750 '23'||':'||'59','YYYY/MM/DD HH24:MI');
1751
1752 L_bill_rate_tbl(br_ctr).labor_item_id := null;
1753 L_bill_rate_tbl(br_ctr).labor_item_org_id := null;
1754 L_bill_rate_tbl(br_ctr).bill_rate_code := null;
1755 L_bill_rate_tbl(br_ctr).flat_rate := null;
1756 L_bill_rate_tbl(br_ctr).flat_rate_uom_code := null;
1757 L_bill_rate_tbl(br_ctr).percent_over_listprice := null;
1758
1759 end if;
1760
1761 if get_next_wkday(to_char(l_datetime_searched,'DY'))
1762 = to_char(L_labor_sch_tbl(i).end_datetime,'DY') then
1763 exit;
1764 else
1765 wkday_ctr := wkday_ctr + 1;
1766 end if;
1767
1768 end loop;
1769
1770 end if;
1771
1772 -- sort by date and time on L_bill_rate_tbl
1773
1774 if L_bill_rate_tbl.count > 0 then
1775
1776 --Bug# 4194507 (JVARGHES)
1777
1778 Remove_Zero_Duration_Billrates
1779 (p_Input_Tab => l_Bill_Rate_Tbl
1780 ,x_Output_Tab => l_Bill_Rate_Tbl1
1781 ,x_Return_Status => lx_Return_Status);
1782
1783 IF lx_Return_Status <> G_RET_STS_SUCCESS THEN
1784 RAISE l_EXCEP_UNEXPECTED_ERR;
1785 END IF;
1786
1787 Sort_Billrates_datetime
1788 (P_Input_Tab => L_bill_rate_tbl1
1789 ,X_Output_Tab => L_bill_ratesorted_tbl
1790 ,X_Result => Lx_Result
1791 ,X_Return_Status => Lx_Return_Status);
1792
1793 IF Lx_Result <> G_TRUE THEN
1794 RAISE L_EXCEP_UNEXPECTED_ERR;
1795 END IF;
1796
1797 X_bill_rate_tbl := L_bill_ratesorted_tbl;
1798
1799 else
1800
1801 X_bill_rate_tbl := L_bill_rate_tbl;
1802
1803 end if;
1804
1805 end loop;
1806
1807 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1808
1809 EXCEPTION
1810 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1811 Null;
1812 WHEN OTHERS THEN
1813 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
1814 ,p_msg_name => G_UNEXPECTED_ERROR
1815 ,p_token1 => G_SQLCODE_TOKEN
1816 ,p_token1_value => SQLcode
1817 ,p_token2 => G_SQLERRM_TOKEN
1818 ,p_token2_value => SQLerrm);
1819
1820 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1821 END get_bill_rates;
1822
1823 --Bug# 4194507 (JVARGHES)
1824
1825 PROCEDURE Remove_Zero_Duration_Billrates
1826 (p_Input_Tab IN BILL_RATE_TBL_TYPE
1827 ,x_Output_Tab OUT NOCOPY BILL_RATE_TBL_TYPE
1828 ,x_Return_Status OUT NOCOPY Gx_Ret_Sts)
1829 IS
1830
1831 lx_Input_Tab BILL_RATE_TBL_TYPE;
1832 lx_Output_Tab BILL_RATE_TBL_TYPE;
1833
1834 li_Input_TabIdx BINARY_INTEGER;
1835 li_Output_TabIdx BINARY_INTEGER;
1836
1837 BEGIN
1838
1839 lx_Input_Tab := p_Input_Tab;
1840 li_Input_TabIdx := lx_Input_Tab.FIRST;
1841 li_Output_TabIdx := 1;
1842
1843 WHILE li_Input_TabIdx IS NOT NULL LOOP
1844
1845 IF lx_Input_Tab(li_Input_TabIdx).Start_DateTime = lx_Input_Tab(li_Input_TabIdx).End_DateTime THEN
1846 NULL;
1847 ELSE
1848 lx_Output_Tab(li_Output_TabIdx) := lx_Input_Tab(li_Input_TabIdx);
1849 li_Output_TabIdx := li_Output_TabIdx + 1;
1850 END IF;
1851
1852 li_Input_TabIdx := lx_Input_Tab.NEXT(li_Input_TabIdx);
1853
1854 END LOOP;
1855
1856 x_Output_Tab := lx_Output_Tab;
1857 x_Return_Status := G_RET_STS_SUCCESS;
1858
1859 EXCEPTION
1860
1861 WHEN OTHERS THEN
1862 OKC_API.SET_MESSAGE(p_App_Name => G_APP_NAME
1863 ,p_Msg_Name => G_UNEXPECTED_ERROR
1864 ,p_Token1 => G_SQLCODE_TOKEN
1865 ,p_Token1_Value => SQLCode
1866 ,p_Token2 => G_SQLERRM_TOKEN
1867 ,p_Token2_Value => SQLErrm);
1868
1869 x_Return_Status := OKC_API.G_RET_STS_UNEXP_ERROR;
1870
1871 END Remove_Zero_Duration_Billrates;
1872
1873 --
1874
1875 FUNCTION get_next_wkday
1876 (p_today IN Varchar2) RETURN Varchar2
1877 IS
1878 x_next_wkday varchar2(30) := null;
1879 BEGIN
1880 null;
1881
1882 if p_today = 'SUN' then
1883 x_next_wkday := 'MON';
1884 elsif p_today = 'MON' then
1885 x_next_wkday := 'TUE';
1886 elsif p_today = 'TUE' then
1887 x_next_wkday := 'WED';
1888 elsif p_today = 'WED' then
1889 x_next_wkday := 'THU';
1890 elsif p_today = 'THU' then
1891 x_next_wkday := 'FRI';
1892 elsif p_today = 'FRI' then
1893 x_next_wkday := 'SAT';
1894 elsif p_today = 'SAT' then
1895 x_next_wkday := 'SUN';
1896 end if;
1897
1898 -- x_return_status := G_RET_STS_SUCCESS;
1899 return x_next_wkday;
1900
1901 EXCEPTION
1902 WHEN OTHERS THEN
1903 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
1904 ,p_msg_name => G_UNEXPECTED_ERROR
1905 ,p_token1 => G_SQLCODE_TOKEN
1906 ,p_token1_value => SQLcode
1907 ,p_token2 => G_SQLERRM_TOKEN
1908 ,p_token2_value => SQLerrm);
1909 return x_next_wkday ;
1910 END get_next_wkday;
1911
1912 PROCEDURE Sort_Billrates_datetime
1913 (P_Input_Tab IN BILL_RATE_TBL_TYPE
1914 ,X_Output_Tab out nocopy BILL_RATE_TBL_TYPE
1915 ,X_Result out nocopy Gx_Boolean
1916 ,X_Return_Status out nocopy Gx_Ret_Sts) IS
1917
1918 Lx_Sort_Tab BILL_RATE_TBL_TYPE;
1919 Lx_Result Gx_Boolean;
1920 Lx_Return_Status Gx_Ret_Sts;
1921
1922 Li_TableIdx_Out BINARY_INTEGER;
1923 Li_TableIdx_In BINARY_INTEGER;
1924
1925 Lx_Temp_ContItem OKS_CON_COVERAGE_PUB.BILL_RATE_REC_TYPE;
1926
1927 Lv_Val1 DATE;
1928 Lv_Val2 DATE;
1929
1930 BEGIN
1931
1932 Lx_Sort_Tab := P_Input_Tab;
1933 Lx_Result := G_TRUE;
1934 Lx_Return_Status := G_RET_STS_SUCCESS;
1935
1936
1937 Li_TableIdx_Out := Lx_Sort_Tab.FIRST;
1938
1939 WHILE Li_TableIdx_Out IS NOT NULL LOOP
1940
1941 Li_TableIdx_In := Li_TableIdx_Out;
1942
1943 WHILE Li_TableIdx_In IS NOT NULL LOOP
1944
1945 Lv_Val1 := Lx_Sort_Tab(Li_TableIdx_Out).Start_datetime;
1946
1947 Lv_Val2 := Lx_Sort_Tab(Li_TableIdx_In).Start_datetime;
1948
1949 IF Lv_Val1 > Lv_Val2 THEN
1950
1951 Lx_Temp_ContItem := Lx_Sort_Tab(Li_TableIdx_Out);
1952 Lx_Sort_Tab(Li_TableIdx_Out) := Lx_Sort_Tab(Li_TableIdx_In);
1953 Lx_Sort_Tab(Li_TableIdx_In) := Lx_Temp_ContItem;
1954
1955 END IF;
1956
1957 Li_TableIdx_In := Lx_Sort_Tab.NEXT(Li_TableIdx_In);
1958
1959 END LOOP;
1960
1961 Li_TableIdx_Out := Lx_Sort_Tab.NEXT(Li_TableIdx_Out);
1962
1963 END LOOP;
1964
1965 X_Output_Tab := Lx_Sort_Tab;
1966 X_Result := Lx_Result;
1967 X_Return_Status := Lx_Return_Status;
1968
1969 EXCEPTION
1970
1971 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1972 Null;
1973 WHEN OTHERS THEN
1974 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
1975 ,p_msg_name => G_UNEXPECTED_ERROR
1976 ,p_token1 => G_SQLCODE_TOKEN
1977 ,p_token1_value => SQLcode
1978 ,p_token2 => G_SQLERRM_TOKEN
1979 ,p_token2_value => SQLerrm);
1980
1981 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1982
1983 END Sort_Billrates_datetime;
1984
1985 FUNCTION Get_Final_End_Date(
1986 P_Contract_Id IN number,
1987 P_Enddate IN DATE) Return Date is
1988
1989
1990 CURSOR Lx_Csr_HDR_Grace(Cx_HDR_Id IN number) IS
1991 SELECT okh.Grace_Duration Duration
1992 ,okh.Grace_Period TimeUnit
1993 ,chr.end_date end_date
1994 FROM okc_k_headers_all_b chr, -- Modified for 12.0 MOAC project (JVARGHES)
1995 Oks_K_Headers_B OKH
1996 WHERE chr.Id = Cx_HDR_Id
1997 and okh.chr_id = chr.id;
1998
1999 L_Date DATE;
2000
2001 BEGIN
2002
2003 L_Date := P_Enddate;
2004
2005 IF G_GRACE_PROFILE_SET = 'Y' then
2006 FOR Idx in Lx_Csr_HDR_Grace(P_Contract_Id) LOOP
2007
2008 if (Idx.end_date = P_Enddate and
2009 Idx.TimeUnit is not null and
2010 Idx.Duration is not null ) then
2011
2012 L_Date := OKC_TIME_UTIL_PVT.get_enddate(
2013 P_Enddate,
2014 Idx.TimeUnit,
2015 Idx.Duration) + 1;
2016
2017 end if;
2018
2019 END LOOP;
2020 END IF;
2021
2022 RETURN L_Date;
2023
2024 END Get_Final_End_Date;
2025
2026 END OKS_CON_COVERAGE_PVT;