[Home] [Help]
PACKAGE BODY: APPS.OKS_BILLING_PROFILES_PUB
Source
1 PACKAGE BODY OKS_BILLING_PROFILES_PUB AS
2 /* $Header: OKSPBPEB.pls 120.7 2010/09/08 10:25:12 spingali ship $ */
3
4 /*
5 FUNCTION migrate_bpev(p_bpev_rec1 IN bpev_rec_type,
6 p_bpev_rec2 IN bpev_rec_type)
7 RETURN bpev_rec_type IS
8 l_bpev_rec bpev_rec_type;
9 BEGIN
10 l_bpev_rec.id := p_bpev_rec1.id;
11 l_bpev_rec.object_version_number := p_bpev_rec1.object_version_number;
12 l_bpev_rec.created_by := p_bpev_rec1.created_by;
13 l_bpev_rec.creation_date := p_bpev_rec1.creation_date;
14 l_bpev_rec.last_updated_by := p_bpev_rec1.last_updated_by;
15 l_bpev_rec.last_update_date := p_bpev_rec1.last_update_date;
16 l_bpev_rec.last_update_login := p_bpev_rec1.last_update_login;
17 l_bpev_rec.sfwt_flag := p_bpev_rec2.sfwt_flag;
18 l_bpev_rec.mda_code := p_bpev_rec2.mda_code;
19 l_bpev_rec.irc_owned_customer_id := p_bpev_rec2.irc_owned_customer_id;
20 l_bpev_rec.irc_dependent_customer_id := p_bpev_rec2.irc_dependent_customer_id;
21 l_bpev_rec.ira_address_id := p_bpev_rec2.ira_address_id;
22 l_bpev_rec.unit_of_measure := p_bpev_rec2.unit_of_measure;
23 l_bpev_rec.profile_number := p_bpev_rec2.profile_number;
24 l_bpev_rec.message := p_bpev_rec2.message;
25 l_bpev_rec.summarised_yn := p_bpev_rec2.summarised_yn;
26 l_bpev_rec.release_day := p_bpev_rec2.release_day;
27 l_bpev_rec.description := p_bpev_rec2.description;
28 l_bpev_rec.attribute_category := p_bpev_rec2.attribute_category;
29 l_bpev_rec.attribute1 := p_bpev_rec2.attribute1;
30 l_bpev_rec.attribute2 := p_bpev_rec2.attribute2;
31 l_bpev_rec.attribute3 := p_bpev_rec2.attribute3;
32 l_bpev_rec.attribute4 := p_bpev_rec2.attribute4;
33 l_bpev_rec.attribute5 := p_bpev_rec2.attribute5;
34 l_bpev_rec.attribute6 := p_bpev_rec2.attribute6;
35 l_bpev_rec.attribute7 := p_bpev_rec2.attribute7;
36 l_bpev_rec.attribute8 := p_bpev_rec2.attribute8;
37 l_bpev_rec.attribute9 := p_bpev_rec2.attribute9;
38 l_bpev_rec.attribute10 := p_bpev_rec2.attribute10;
39 l_bpev_rec.attribute11 := p_bpev_rec2.attribute11;
40 l_bpev_rec.attribute12 := p_bpev_rec2.attribute12;
41 l_bpev_rec.attribute13 := p_bpev_rec2.attribute13;
42 l_bpev_rec.attribute14 := p_bpev_rec2.attribute14;
43 l_bpev_rec.attribute15 := p_bpev_rec2.attribute15;
44 RETURN (l_bpev_rec);
45 END migrate_bpev;
46 */
47
48 Type sll_prorated_rec_type IS RECORD
49 ( sll_seq_num Number,
50 sll_start_date DATE,
51 sll_end_date DATE,
52 sll_tuom VARCHAR2(40),
53 sll_period Number,
54 sll_uom_per_period Number,
55 sll_amount Number
56 );
57
58 Type sll_prorated_tab_type is Table of sll_prorated_rec_type index by binary_integer;
59
60 FUNCTION Find_Currency_Code
61 ( p_cle_id NUMBER,
62 p_chr_id NUMBER
63 )
64 RETURN VARCHAR2
65 IS
66
67 CURSOR l_line_cur IS
68 SELECT contract.currency_code
69 FROM okc_k_headers_b contract, okc_k_lines_b line
70 WHERE contract.id = line.dnz_chr_id and line.id = p_cle_id;
71
72 CURSOR l_hdr_cur IS
73 SELECT contract.currency_code
74 FROM okc_k_headers_b contract
75 WHERE contract.id = p_chr_id;
76
77
78 l_Currency VARCHAR2(15);
79
80 BEGIN
81
82 IF p_chr_id IS NULL THEN ---called for line
83 OPEN l_line_cur;
84 FETCH l_line_cur INTO l_currency;
85
86 IF l_line_cur%NOTFOUND THEN
87 l_Currency := NULL;
88 END IF;
89
90 Close l_line_cur;
91
92 ELSE ---FOR HEADER
93
94 OPEN l_hdr_cur;
95 FETCH l_hdr_cur INTO l_currency;
96
97 IF l_hdr_cur%NOTFOUND THEN
98 l_Currency := NULL;
99 END IF;
100
101 Close l_hdr_cur;
102
103 END IF;
104
105 RETURN l_Currency;
106
107 EXCEPTION
108 WHEN NO_DATA_FOUND THEN
109 RETURN NULL;
110 WHEN OTHERS THEN
111 RETURN NULL;
112
113 END Find_Currency_Code;
114
115 PROCEDURE Calculate_sll_amount( p_api_version IN NUMBER,
116 p_total_amount IN NUMBER,
117 p_currency_code IN VARCHAR2,
118 p_period_start IN VARCHAR2,
119 p_period_type IN VARCHAR2,
120 p_sll_prorated_tab IN OUT NOCOPY sll_prorated_tab_type,
121 x_return_status OUT NOCOPY VARCHAR2
122
123 )
124 IS
125 l_sll_num NUMBER;
126 i NUMBER;
127 j NUMBER;
128 l_sll_remain_amount NUMBER(20,2);
129 l_currency_code VARCHAR2(15);
130 l_period_sll_amt NUMBER(20,2);
131
132 l_uom_code VARCHAR2(40);
133 l_tce_code VARCHAR2(10);
134 l_uom_quantity NUMBER;
135 l_curr_sll_start_date DATE;
136 l_curr_sll_end_date DATE;
137
138 l_next_sll_start_date DATE;
139 l_next_sll_end_date DATE;
140 l_tot_sll_amount NUMBER(20,2);
141
142 l_curr_frequency NUMBER;
143 l_next_frequency NUMBER;
144 l_tot_frequency NUMBER;
145 l_sll_period NUMBER;
146 l_return_status VARCHAR2(1);
147 l_uom_per_period NUMBER;
148 l_temp NUMBER;
149
150 BEGIN
151 x_return_status := 'S';
152 l_sll_num := p_sll_prorated_tab.count;
153 l_sll_remain_amount := p_total_amount;
154 -------------------------------------------------------------------------
155 -- Begin partial period computation logic
156 -- Developer Mani Choudhary
157 -- Date 31-MAY-2005
158 -- Proration to consider period start and period type
159 -------------------------------------------------------------------------
160 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
161 fnd_log.string(fnd_log.level_statement,G_MODULE_CURRENT||'.Calculate_sll_amount.ppc',
162 'input parameters period start '||p_period_start
163 ||' p_period_type = ' || p_period_type);
164 END IF;
165
166 IF p_period_start is NOT NULL AND
167 p_period_type is NOT NULL
168 THEN
169 FOR i in 1 .. l_sll_num LOOP
170 l_uom_code := p_sll_prorated_tab(i).sll_tuom ;
171 l_uom_per_period := p_sll_prorated_tab(i).sll_uom_per_period ;
172 --errorout_ad('l_uom_code '||l_uom_code);
173 l_next_sll_end_date := NULL;
174 l_curr_sll_start_date := p_sll_prorated_tab(i).sll_start_date;
175 l_curr_sll_end_date := p_sll_prorated_tab(i).sll_end_date;
176
177 For j in i+1 .. l_sll_num Loop
178 l_next_sll_start_date := p_sll_prorated_tab(j).sll_start_date;
179 l_next_sll_end_date := p_sll_prorated_tab(j).sll_end_date;
180 /* l_temp:=NULL;
181 l_temp:= OKS_TIME_MEASURES_PUB.get_quantity (
182 p_start_date => l_next_sll_start_date,
183 p_end_date => l_next_sll_end_date,
184 p_source_uom => l_uom_code,
185 p_period_type => p_period_type,
186 p_period_start => p_period_start
187 );
188 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
189 fnd_log.string(fnd_log.level_statement,G_MODULE_CURRENT||'.Calculate_sll_amount.ppc',
190 'afer calling OKS_TIME_MEASURES_PUB.get_quantity input parameters period start '||p_period_start||' p_period_type = ' || p_period_type
191 ||' result l_temp '||l_temp);
192 END IF;
193
194 IF nvl(l_temp,0) = 0 THEN
195 RAISE G_EXCEPTION_HALT_VALIDATION;
196 END IF;
197
198 l_next_frequency :=l_next_frequency + l_temp;
199
200 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
201 fnd_log.string(fnd_log.level_statement,G_MODULE_CURRENT||'.Calculate_sll_amount.ppc',
202 'afer calling OKS_TIME_MEASURES_PUB.get_quantity input parameters period start '||p_period_start||' p_period_type = ' || p_period_type
203 ||' result l_next_frequency '||l_next_frequency);
204 END IF;
205
206 */
207
208 END LOOP;
209
210 l_curr_frequency := OKS_TIME_MEASURES_PUB.get_quantity (
211 p_start_date => l_curr_sll_start_date,
212 p_end_date => l_curr_sll_end_date,
213 p_source_uom => l_uom_code,
214 p_period_type => p_period_type,
215 p_period_start => p_period_start
216 );
217 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
218 fnd_log.string(fnd_log.level_statement,G_MODULE_CURRENT||'.Calculate_sll_amount.ppc',
219 'afer calling OKS_TIME_MEASURES_PUB.get_quantity input parameters period start '||p_period_start||' p_period_type = ' || p_period_type
220 ||' result l_curr_frequency '||l_curr_frequency);
221 END IF;
222
223 IF nvl(l_curr_frequency,0) = 0 THEN
224 RAISE G_EXCEPTION_HALT_VALIDATION;
225 END IF;
226 l_tot_frequency := 0;
227
228 l_tot_frequency := OKS_TIME_MEASURES_PUB.get_quantity (
229 p_start_date => l_curr_sll_start_date,
230 p_end_date => nvl(l_next_sll_end_date,l_curr_sll_end_date),
231 p_source_uom => l_uom_code,
232 p_period_type => p_period_type,
233 p_period_start => p_period_start
234 );
235
236 IF nvl(l_tot_frequency,0) = 0 THEN
237 RAISE G_EXCEPTION_HALT_VALIDATION;
238 END IF;
239 --errorout_ad('l_curr_frequency '||l_curr_frequency);
240
241 -- l_next_frequency := 0;
242
243
244 -- l_tot_frequency := l_tot_frequency + l_curr_frequency + l_next_frequency;
245
246 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
247 fnd_log.string(fnd_log.level_statement,G_MODULE_CURRENT||'.Calculate_sll_amount.ppc',
248 ' result l_tot_frequency '||l_tot_frequency);
249 END IF;
250
251 --errorout_ad('l_tot_frequency '||l_tot_frequency);
252 -- l_sll_period := p_sll_prorated_tab(i).sll_period;
253 l_sll_period := l_curr_frequency/l_uom_per_period;
254
255 l_period_sll_amt := ( l_sll_remain_amount /( nvl(l_tot_frequency,1) * nvl(l_sll_period,1))) * nvl(l_curr_frequency,0) ;
256
257 l_period_sll_amt := OKS_EXTWAR_UTIL_PVT.round_currency_amt(l_period_sll_amt, l_currency_code);
258
259 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
260 fnd_log.string(fnd_log.level_statement,G_MODULE_CURRENT||'.Calculate_sll_amount.ppc',
261 ' result l_period_sll_amt '||l_period_sll_amt);
262 END IF;
263
264
265 l_sll_remain_amount := l_sll_remain_amount - (l_period_sll_amt * nvl(l_sll_period,1)) ;
266
267 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
268 fnd_log.string(fnd_log.level_statement,G_MODULE_CURRENT||'.Calculate_sll_amount.ppc',
269 ' result l_sll_remain_amount '||l_sll_remain_amount);
270 END IF;
271
272 --errorout_ad('l_period_sll_amt '||l_period_sll_amt);
273 --errorout_ad('l_sll_remain_amount '||l_sll_remain_amount);
274 p_sll_prorated_tab(i).sll_amount := l_period_sll_amt;
275 l_curr_frequency := 0;
276 END LOOP;
277 -------------------------------------------------------------------------
278 -- End partial period computation logic
279 -------------------------------------------------------------------------
280 ELSE
281 For i in 1 .. l_sll_num Loop
282 l_uom_code := p_sll_prorated_tab(i).sll_tuom ;
283 oks_bill_util_pub.get_seeded_timeunit(
284 p_timeunit => l_uom_code ,
285 x_return_status => l_return_status,
286 x_quantity => l_uom_quantity,
287 x_timeunit => l_tce_code);
288
289 l_curr_sll_start_date := p_sll_prorated_tab(i).sll_start_date;
290 l_curr_sll_end_date := p_sll_prorated_tab(i).sll_end_date;
291
292 IF l_tce_code = 'DAY' Then
293 l_curr_frequency := l_curr_sll_end_date - l_curr_sll_start_date + 1;
294 ELSIF l_tce_code = 'MONTH' Then
295 l_curr_frequency := months_between(l_curr_sll_end_date + 1, l_curr_sll_start_date) ;
296 ELSIF l_tce_code = 'YEAR' Then
297 l_curr_frequency := months_between(l_curr_sll_end_date + 1, l_curr_sll_start_date) / 12 ;
298 END IF;
299
300 If NVL(l_uom_quantity,0) > 0 Then
301 l_curr_frequency := l_curr_frequency / NVL(l_uom_quantity,1);
302 END IF;
303 --errorout_ad('l_curr_frequency '||l_curr_frequency);
304 l_tot_frequency := 0;
305 l_next_frequency := 0;
306
307 For j in i+1 .. l_sll_num Loop
308 l_next_sll_start_date := p_sll_prorated_tab(j).sll_start_date;
309 l_next_sll_end_date := p_sll_prorated_tab(j).sll_end_date;
310 IF l_tce_code = 'DAY' Then
311 l_next_frequency := l_next_frequency + (l_next_sll_end_date - l_next_sll_start_date + 1);
312 ELSIF l_tce_code = 'MONTH' Then
313 l_next_frequency := l_next_frequency + (months_between(l_next_sll_end_date + 1, l_next_sll_start_date)) ;
314 ELSIF l_tce_code = 'YEAR' Then
315 l_next_frequency := l_next_frequency + (months_between(l_next_sll_end_date + 1, l_next_sll_start_date) / 12) ;
316 END IF;
317
318
319 END LOOP;
320
321 If NVL(l_uom_quantity,0) > 0 Then
322 l_next_frequency := l_next_frequency / NVL(l_uom_quantity,1);
323 END IF;
324
325 l_tot_frequency := l_tot_frequency + l_curr_frequency + l_next_frequency;
326 --errorout_ad('l_tot_frequency '||l_tot_frequency);
327 l_sll_period := p_sll_prorated_tab(i).sll_period;
328
329
330 l_period_sll_amt := ( l_sll_remain_amount /( nvl(l_tot_frequency,1) * nvl(l_sll_period,1))) * nvl(l_curr_frequency,0) ;
331
332 l_period_sll_amt := OKS_EXTWAR_UTIL_PVT.round_currency_amt(l_period_sll_amt, l_currency_code);
333
334 l_sll_remain_amount := l_sll_remain_amount - (l_period_sll_amt * nvl(l_sll_period,1)) ;
335 --errorout_ad('l_period_sll_amt '||l_period_sll_amt);
336 --errorout_ad('l_sll_remain_amount '||l_sll_remain_amount);
337 p_sll_prorated_tab(i).sll_amount := l_period_sll_amt;
338 l_curr_frequency := 0;
339 END LOOP;
340 END IF;
341
342 EXCEPTION
343 WHEN G_EXCEPTION_HALT_VALIDATION THEN
344 x_return_status := OKC_API.G_RET_STS_ERROR;
345 END Calculate_sll_amount;
346
347 /*cgopinee bugfix for 7596241 end*/
348
349 PROCEDURE add_language IS
350 BEGIN
351 oks_billing_profiles_pvt.add_language;
352 END;
353
354 -- Procedure for insert_row
355 PROCEDURE insert_row(
356 p_api_version IN NUMBER,
357 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
358 x_return_status OUT NOCOPY VARCHAR2,
359 x_msg_count OUT NOCOPY NUMBER,
360 x_msg_data OUT NOCOPY VARCHAR2,
361 p_bpev_tbl IN bpev_tbl_type,
362 x_bpev_tbl OUT NOCOPY bpev_tbl_type) IS
363
364 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
365 i NUMBER := 0;
366
367 BEGIN
368 -- initialize the return status
369 x_return_status := OKC_API.G_RET_STS_SUCCESS;
370
371 IF p_bpev_tbl.COUNT > 0 THEN
372 i := p_bpev_tbl.FIRST;
373 LOOP
374 insert_row(
375 p_api_version
376 ,p_init_msg_list
377 ,l_return_status
378 ,x_msg_count
379 ,x_msg_data
380 ,p_bpev_tbl(i)
381 ,x_bpev_tbl(i));
382
383 IF l_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
384 IF l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
385 x_return_status := l_return_status;
386 RAISE G_EXCEPTION_HALT_VALIDATION;
387 ELSE
388 x_return_status := l_return_status;
389 END IF;
390 END IF;
391
392 EXIT WHEN (i = p_bpev_tbl.LAST);
393 i := p_bpev_tbl.NEXT(i);
394 END LOOP;
395 END IF;
396 EXCEPTION
397 WHEN G_EXCEPTION_HALT_VALIDATION THEN
398 NULL;
399 WHEN OTHERS THEN
400 OKC_API.set_message(p_app_name => g_app_name,
401 p_msg_name => g_unexpected_error,
402 p_token1 => g_sqlcode_token,
403 p_token1_value => sqlcode,
404 p_token2 => g_sqlerrm_token,
405 p_token2_value => sqlerrm);
406 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
407 END insert_row;
408
409 PROCEDURE insert_row(
410 p_api_version IN NUMBER,
411 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
412 x_return_status OUT NOCOPY VARCHAR2,
413 x_msg_count OUT NOCOPY NUMBER,
414 x_msg_data OUT NOCOPY VARCHAR2,
415 p_bpev_rec IN bpev_rec_type,
416 x_bpev_rec OUT NOCOPY bpev_rec_type) IS
417
418 l_api_name CONSTANT VARCHAR2(30) := 'insert_row';
419 l_api_version CONSTANT NUMBER := 1.0;
420 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
421 l_bpev_rec bpev_rec_type := p_bpev_rec;
422
423 BEGIN
424 l_return_status := OKC_API.START_ACTIVITY(l_api_name
425 ,p_init_msg_list
426 ,'_PUB'
427 ,x_return_status
428 );
429 IF l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
430 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
431 ELSIF l_return_status = OKC_API.G_RET_STS_ERROR THEN
432 RAISE OKC_API.G_EXCEPTION_ERROR;
433 END IF;
434
435 -- Call user hook for BEFORE
436 g_bpev_rec := l_bpev_rec;
437 okc_util.call_user_hook(x_return_status, g_pkg_name, l_api_name, 'B');
438 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
439 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
440 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
441 raise OKC_API.G_EXCEPTION_ERROR;
442 END IF;
443
444 -- get values back from hook call
445 --l_bpev_rec := migrate_bpev(l_bpev_rec, g_bpev_rec);
446 l_bpev_rec := g_bpev_rec;
447
448 -- call to complex API procedure
449 oks_billing_profiles_pvt.insert_row(p_api_version
450 ,p_init_msg_list
451 ,x_return_status
452 ,x_msg_count
453 ,x_msg_data
454 ,l_bpev_rec
455 ,x_bpev_rec);
456
457 IF x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
458 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
459 ELSIF x_return_status = OKC_API.G_RET_STS_ERROR THEN
460 RAISE OKC_API.G_EXCEPTION_ERROR;
461 END IF;
462
463 -- USER HOOK CALL FOR AFTER, STARTS
464 g_bpev_rec := x_bpev_rec;
465
466 okc_util.call_user_hook(x_return_status => x_return_status,
467 p_package_name => g_pkg_name,
468 p_procedure_name => l_api_name,
469 p_before_after => 'A');
470
471 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
472 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
473 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
474 RAISE OKC_API.G_EXCEPTION_ERROR;
475 END IF;
476 -- USER HOOK CALL FOR AFTER, ENDS
477
478 OKC_API.END_ACTIVITY(x_msg_count,x_msg_data);
479
480 EXCEPTION
481 WHEN OKC_API.G_EXCEPTION_ERROR THEN
482 x_return_status := OKC_API.HANDLE_EXCEPTIONS
483 (l_api_name,
484 G_PKG_NAME,
485 'OKC_API.G_RET_STS_ERROR',
486 x_msg_count,
487 x_msg_data,
488 '_PUB');
489 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
490 x_return_status := OKC_API.HANDLE_EXCEPTIONS
491 (l_api_name,
492 G_PKG_NAME,
493 'OKC_API.G_RET_STS_UNEXP_ERROR',
494 x_msg_count,
495 x_msg_data,
496 '_PUB');
497 WHEN OTHERS THEN
498 x_return_status := OKC_API.HANDLE_EXCEPTIONS
499 (l_api_name,
500 G_PKG_NAME,
501 'OTHERS',
502 x_msg_count,
503 x_msg_data,
504 '_PUB');
505 END insert_row;
506
507
508 -- Procedure for lock_row
509 PROCEDURE lock_row(
510 p_api_version IN NUMBER,
511 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
512 x_return_status OUT NOCOPY VARCHAR2,
513 x_msg_count OUT NOCOPY NUMBER,
514 x_msg_data OUT NOCOPY VARCHAR2,
515 p_bpev_tbl IN bpev_tbl_type) IS
516
517 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
518 i NUMBER := 0;
519
520 BEGIN
521 -- initialize the return status
522 x_return_status := OKC_API.G_RET_STS_SUCCESS;
523
524 IF p_bpev_tbl.COUNT > 0 THEN
525 i := p_bpev_tbl.FIRST;
526 LOOP
527 lock_row(
528 p_api_version
529 ,p_init_msg_list
530 ,l_return_status
531 ,x_msg_count
532 ,x_msg_data
533 ,p_bpev_tbl(i));
534
535 IF l_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
536 IF l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
537 x_return_status := l_return_status;
538 RAISE G_EXCEPTION_HALT_VALIDATION;
539 ELSE
540 x_return_status := l_return_status;
541 END IF;
542 END IF;
543
544 EXIT WHEN (i = p_bpev_tbl.LAST);
545 i := p_bpev_tbl.NEXT(i);
546 END LOOP;
547 END IF;
548 EXCEPTION
549 WHEN G_EXCEPTION_HALT_VALIDATION THEN
550 NULL;
551 WHEN OTHERS THEN
552 OKC_API.set_message(p_app_name => g_app_name,
553 p_msg_name => g_unexpected_error,
554 p_token1 => g_sqlcode_token,
555 p_token1_value => sqlcode,
556 p_token2 => g_sqlerrm_token,
557 p_token2_value => sqlerrm);
558 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
559 END lock_row;
560
561 PROCEDURE lock_row(
562 p_api_version IN NUMBER,
563 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
564 x_return_status OUT NOCOPY VARCHAR2,
565 x_msg_count OUT NOCOPY NUMBER,
566 x_msg_data OUT NOCOPY VARCHAR2,
567 p_bpev_rec IN bpev_rec_type) IS
568
569 l_api_name CONSTANT VARCHAR2(30) := 'lock_row';
570 l_api_version CONSTANT NUMBER := 1.0;
571 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
572 l_bpev_rec bpev_rec_type := p_bpev_rec;
573
574 BEGIN
575 l_return_status := OKC_API.START_ACTIVITY(l_api_name
576 ,p_init_msg_list
577 ,'_PUB'
578 ,x_return_status
579 );
580 IF l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
581 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
582 ELSIF l_return_status = OKC_API.G_RET_STS_ERROR THEN
583 RAISE OKC_API.G_EXCEPTION_ERROR;
584 END IF;
585
586 -- call to complex API procedure
587 oks_billing_profiles_pvt.lock_row(p_api_version
588 ,p_init_msg_list
589 ,x_return_status
590 ,x_msg_count
591 ,x_msg_data
592 ,p_bpev_rec);
593
594 IF x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
595 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
596 ELSIF x_return_status = OKC_API.G_RET_STS_ERROR THEN
597 RAISE OKC_API.G_EXCEPTION_ERROR;
598 END IF;
599
600 OKC_API.END_ACTIVITY(x_msg_count,x_msg_data);
601
602 EXCEPTION
603 WHEN OKC_API.G_EXCEPTION_ERROR THEN
604 x_return_status := OKC_API.HANDLE_EXCEPTIONS
605 (l_api_name,
606 G_PKG_NAME,
607 'OKC_API.G_RET_STS_ERROR',
608 x_msg_count,
609 x_msg_data,
610 '_PUB');
611 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
612 x_return_status := OKC_API.HANDLE_EXCEPTIONS
613 (l_api_name,
614 G_PKG_NAME,
615 'OKC_API.G_RET_STS_UNEXP_ERROR',
616 x_msg_count,
617 x_msg_data,
618 '_PUB');
619 WHEN OTHERS THEN
620 x_return_status := OKC_API.HANDLE_EXCEPTIONS
621 (l_api_name,
622 G_PKG_NAME,
623 'OTHERS',
624 x_msg_count,
625 x_msg_data,
626 '_PUB');
627 END lock_row;
628
629 -- Procedure for update_row
630 PROCEDURE update_row(
631 p_api_version IN NUMBER,
632 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
633 x_return_status OUT NOCOPY VARCHAR2,
634 x_msg_count OUT NOCOPY NUMBER,
635 x_msg_data OUT NOCOPY VARCHAR2,
636 p_bpev_tbl IN bpev_tbl_type,
637 x_bpev_tbl OUT NOCOPY bpev_tbl_type) IS
638
639 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
640 i NUMBER := 0;
641
642 BEGIN
643 -- initialize the return status
644 x_return_status := OKC_API.G_RET_STS_SUCCESS;
645
646 IF p_bpev_tbl.COUNT > 0 THEN
647 i := p_bpev_tbl.FIRST;
648 LOOP
649 update_row(
650 p_api_version
651 ,p_init_msg_list
652 ,l_return_status
653 ,x_msg_count
654 ,x_msg_data
655 ,p_bpev_tbl(i)
656 ,x_bpev_tbl(i));
657
658 IF l_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
659 IF l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
660 x_return_status := l_return_status;
661 RAISE G_EXCEPTION_HALT_VALIDATION;
662 ELSE
663 x_return_status := l_return_status;
664 END IF;
665 END IF;
666
667 EXIT WHEN (i = p_bpev_tbl.LAST);
668 i := p_bpev_tbl.NEXT(i);
669 END LOOP;
670 END IF;
671 EXCEPTION
672 WHEN G_EXCEPTION_HALT_VALIDATION THEN
673 NULL;
674 WHEN OTHERS THEN
675 OKC_API.set_message(p_app_name => g_app_name,
676 p_msg_name => g_unexpected_error,
677 p_token1 => g_sqlcode_token,
678 p_token1_value => sqlcode,
679 p_token2 => g_sqlerrm_token,
680 p_token2_value => sqlerrm);
681 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
682 END update_row;
683
684 PROCEDURE update_row(
685 p_api_version IN NUMBER,
686 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
687 x_return_status OUT NOCOPY VARCHAR2,
688 x_msg_count OUT NOCOPY NUMBER,
689 x_msg_data OUT NOCOPY VARCHAR2,
690 p_bpev_rec IN bpev_rec_type,
691 x_bpev_rec OUT NOCOPY bpev_rec_type) IS
692
693 l_api_name CONSTANT VARCHAR2(30) := 'update_row';
694 l_api_version CONSTANT NUMBER := 1.0;
695 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
696 l_bpev_rec bpev_rec_type := p_bpev_rec;
697
698 BEGIN
699 l_return_status := OKC_API.START_ACTIVITY(l_api_name
700 ,p_init_msg_list
701 ,'_PUB'
702 ,x_return_status
703 );
704 IF l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
705 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
706 ELSIF l_return_status = OKC_API.G_RET_STS_ERROR THEN
707 RAISE OKC_API.G_EXCEPTION_ERROR;
708 END IF;
709
710 -- Call user hook for BEFORE
711 g_bpev_rec := l_bpev_rec;
712 okc_util.call_user_hook(x_return_status, g_pkg_name, l_api_name, 'B');
713 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
714 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
715 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
716 raise OKC_API.G_EXCEPTION_ERROR;
717 END IF;
718
719 -- get values back from hook call
720 --l_bpev_rec := migrate_bpev(l_bpev_rec, g_bpev_rec);
721 l_bpev_rec := g_bpev_rec;
722
723 -- call to complex API procedure
724 oks_billing_profiles_pvt.update_row(p_api_version
725 ,p_init_msg_list
726 ,x_return_status
727 ,x_msg_count
728 ,x_msg_data
729 ,l_bpev_rec
730 ,x_bpev_rec);
731
732 IF x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
733 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
734 ELSIF x_return_status = OKC_API.G_RET_STS_ERROR THEN
735 RAISE OKC_API.G_EXCEPTION_ERROR;
736 END IF;
737
738 -- USER HOOK CALL FOR AFTER, STARTS
739 g_bpev_rec := x_bpev_rec;
740
741 okc_util.call_user_hook(x_return_status => x_return_status,
742 p_package_name => g_pkg_name,
743 p_procedure_name => l_api_name,
744 p_before_after => 'A');
745
746 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
747 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
748 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
749 RAISE OKC_API.G_EXCEPTION_ERROR;
750 END IF;
751 -- USER HOOK CALL FOR AFTER, ENDS
752
753 OKC_API.END_ACTIVITY(x_msg_count,x_msg_data);
754
755 EXCEPTION
756 WHEN OKC_API.G_EXCEPTION_ERROR THEN
757 x_return_status := OKC_API.HANDLE_EXCEPTIONS
758 (l_api_name,
759 G_PKG_NAME,
760 'OKC_API.G_RET_STS_ERROR',
761 x_msg_count,
762 x_msg_data,
763 '_PUB');
764 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
765 x_return_status := OKC_API.HANDLE_EXCEPTIONS
766 (l_api_name,
767 G_PKG_NAME,
768 'OKC_API.G_RET_STS_UNEXP_ERROR',
769 x_msg_count,
770 x_msg_data,
771 '_PUB');
772 WHEN OTHERS THEN
773 x_return_status := OKC_API.HANDLE_EXCEPTIONS
774 (l_api_name,
775 G_PKG_NAME,
776 'OTHERS',
777 x_msg_count,
778 x_msg_data,
779 '_PUB');
780 END update_row;
781
782 -- Procedure for delete_row
783 PROCEDURE delete_row(
784 p_api_version IN NUMBER,
785 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
786 x_return_status OUT NOCOPY VARCHAR2,
787 x_msg_count OUT NOCOPY NUMBER,
788 x_msg_data OUT NOCOPY VARCHAR2,
789 p_bpev_tbl IN bpev_tbl_type) IS
790
791 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
792 i NUMBER := 0;
793
794 BEGIN
795 -- initialize the return status
796 x_return_status := OKC_API.G_RET_STS_SUCCESS;
797 IF p_bpev_tbl.COUNT > 0 THEN
798 i := p_bpev_tbl.FIRST;
799 LOOP
800 delete_row(
801 p_api_version
802 ,p_init_msg_list
803 ,l_return_status
804 ,x_msg_count
805 ,x_msg_data
806 ,p_bpev_tbl(i));
807
808 IF l_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
809 IF l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
810 x_return_status := l_return_status;
811 RAISE G_EXCEPTION_HALT_VALIDATION;
812 ELSE
813 x_return_status := l_return_status;
814 END IF;
815 END IF;
816
817 EXIT WHEN (i = p_bpev_tbl.LAST);
818 i := p_bpev_tbl.NEXT(i);
819 END LOOP;
820 END IF;
821 EXCEPTION
822 WHEN G_EXCEPTION_HALT_VALIDATION THEN
823 NULL;
824 WHEN OTHERS THEN
825 OKC_API.set_message(p_app_name => g_app_name,
826 p_msg_name => g_unexpected_error,
827 p_token1 => g_sqlcode_token,
828 p_token1_value => sqlcode,
829 p_token2 => g_sqlerrm_token,
830 p_token2_value => sqlerrm);
831 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
832 END delete_row;
833
834 PROCEDURE delete_row(
835 p_api_version IN NUMBER,
836 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
837 x_return_status OUT NOCOPY VARCHAR2,
838 x_msg_count OUT NOCOPY NUMBER,
839 x_msg_data OUT NOCOPY VARCHAR2,
840 p_bpev_rec IN bpev_rec_type) IS
841
842 l_api_name CONSTANT VARCHAR2(30) := 'delete_row';
843 l_api_version CONSTANT NUMBER := 1.0;
844 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
845 l_bpev_rec bpev_rec_type := p_bpev_rec;
846
847 BEGIN
848 l_return_status := OKC_API.START_ACTIVITY(l_api_name
849 ,p_init_msg_list
850 ,'_PUB'
851 ,x_return_status
852 );
853 IF l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
854 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
855 ELSIF l_return_status = OKC_API.G_RET_STS_ERROR THEN
856 RAISE OKC_API.G_EXCEPTION_ERROR;
857 END IF;
858
859 -- Call user hook for BEFORE
860 g_bpev_rec := l_bpev_rec;
861
862 okc_util.call_user_hook(x_return_status, g_pkg_name, l_api_name, 'B');
863 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
864 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
865 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
866 raise OKC_API.G_EXCEPTION_ERROR;
867 END IF;
868
869 -- get values back from hook call
870 --l_bpev_rec := migrate_bpev(l_bpev_rec, g_bpev_rec);
871 l_bpev_rec := g_bpev_rec;
872 -- call to complex API procedure
873 oks_billing_profiles_pvt.delete_row(p_api_version
874 ,p_init_msg_list
875 ,x_return_status
876 ,x_msg_count
877 ,x_msg_data
878 ,l_bpev_rec);
879
880 IF x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
881 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
882 ELSIF x_return_status = OKC_API.G_RET_STS_ERROR THEN
883 RAISE OKC_API.G_EXCEPTION_ERROR;
884 END IF;
885
886 -- USER HOOK CALL FOR AFTER, STARTS
887 g_bpev_rec := l_bpev_rec;
888
889 okc_util.call_user_hook(x_return_status => x_return_status,
890 p_package_name => g_pkg_name,
891 p_procedure_name => l_api_name,
892 p_before_after => 'A');
893
894 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
895 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
896 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
897 RAISE OKC_API.G_EXCEPTION_ERROR;
898 END IF;
899 -- USER HOOK CALL FOR AFTER, ENDS
900
901 OKC_API.END_ACTIVITY(x_msg_count,x_msg_data);
902
903 EXCEPTION
904 WHEN OKC_API.G_EXCEPTION_ERROR THEN
905 x_return_status := OKC_API.HANDLE_EXCEPTIONS
906 (l_api_name,
907 G_PKG_NAME,
908 'OKC_API.G_RET_STS_ERROR',
909 x_msg_count,
910 x_msg_data,
911 '_PUB');
912 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
913 x_return_status := OKC_API.HANDLE_EXCEPTIONS
914 (l_api_name,
915 G_PKG_NAME,
916 'OKC_API.G_RET_STS_UNEXP_ERROR',
917 x_msg_count,
918 x_msg_data,
919 '_PUB');
920 WHEN OTHERS THEN
921 x_return_status := OKC_API.HANDLE_EXCEPTIONS
922 (l_api_name,
923 G_PKG_NAME,
924 'OTHERS',
925 x_msg_count,
926 x_msg_data,
927 '_PUB');
928 END delete_row;
929
930 -- Procedure for validate_row
931 PROCEDURE validate_row(
932 p_api_version IN NUMBER,
933 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
934 x_return_status OUT NOCOPY VARCHAR2,
935 x_msg_count OUT NOCOPY NUMBER,
936 x_msg_data OUT NOCOPY VARCHAR2,
937 p_bpev_tbl IN bpev_tbl_type) IS
938
939 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
940 i NUMBER := 0;
941
942 BEGIN
943 -- initialize the return status
944 x_return_status := OKC_API.G_RET_STS_SUCCESS;
945
946 IF p_bpev_tbl.COUNT > 0 THEN
947 i := p_bpev_tbl.FIRST;
948 LOOP
949 validate_row(
950 p_api_version
951 ,p_init_msg_list
952 ,l_return_status
953 ,x_msg_count
954 ,x_msg_data
955 ,p_bpev_tbl(i));
956
957 IF l_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
958 IF l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
959 x_return_status := l_return_status;
960 RAISE G_EXCEPTION_HALT_VALIDATION;
961 ELSE
962 x_return_status := l_return_status;
963 END IF;
964 END IF;
965
966 EXIT WHEN (i = p_bpev_tbl.LAST);
967 i := p_bpev_tbl.NEXT(i);
968 END LOOP;
969 END IF;
970 EXCEPTION
971 WHEN G_EXCEPTION_HALT_VALIDATION THEN
972 NULL;
973 WHEN OTHERS THEN
974 OKC_API.set_message(p_app_name => g_app_name,
975 p_msg_name => g_unexpected_error,
976 p_token1 => g_sqlcode_token,
977 p_token1_value => sqlcode,
978 p_token2 => g_sqlerrm_token,
979 p_token2_value => sqlerrm);
980 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
981 END validate_row;
982
983 PROCEDURE validate_row(
984 p_api_version IN NUMBER,
985 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
986 x_return_status OUT NOCOPY VARCHAR2,
987 x_msg_count OUT NOCOPY NUMBER,
988 x_msg_data OUT NOCOPY VARCHAR2,
989 p_bpev_rec IN bpev_rec_type) IS
990
991 l_api_name CONSTANT VARCHAR2(30) := 'validate_row';
992 l_api_version CONSTANT NUMBER := 1.0;
993 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
994 l_bpev_rec bpev_rec_type := p_bpev_rec;
995
996 BEGIN
997 l_return_status := OKC_API.START_ACTIVITY(l_api_name
998 ,p_init_msg_list
999 ,'_PUB'
1000 ,x_return_status
1001 );
1002 IF l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
1003 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1004 ELSIF l_return_status = OKC_API.G_RET_STS_ERROR THEN
1005 RAISE OKC_API.G_EXCEPTION_ERROR;
1006 END IF;
1007
1008 -- Call user hook for BEFORE
1009 g_bpev_rec := l_bpev_rec;
1010 okc_util.call_user_hook(x_return_status, g_pkg_name, l_api_name, 'B');
1011 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1012 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1013 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1014 raise OKC_API.G_EXCEPTION_ERROR;
1015 END IF;
1016
1017 -- get values back from hook call
1018 --l_bpev_rec := migrate_bpev(l_bpev_rec, g_bpev_rec);
1019 l_bpev_rec := g_bpev_rec;
1020
1021 -- call to complex API procedure
1022 oks_billing_profiles_pvt.validate_row(p_api_version
1023 ,p_init_msg_list
1024 ,x_return_status
1025 ,x_msg_count
1026 ,x_msg_data
1027 ,l_bpev_rec);
1028
1029 IF x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
1030 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1031 ELSIF x_return_status = OKC_API.G_RET_STS_ERROR THEN
1032 RAISE OKC_API.G_EXCEPTION_ERROR;
1033 END IF;
1034
1035 -- USER HOOK CALL FOR AFTER, STARTS
1036 g_bpev_rec := l_bpev_rec;
1037
1038 okc_util.call_user_hook(x_return_status => x_return_status,
1039 p_package_name => g_pkg_name,
1040 p_procedure_name => l_api_name,
1041 p_before_after => 'A');
1042
1043 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1044 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1045 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1046 RAISE OKC_API.G_EXCEPTION_ERROR;
1047 END IF;
1048 -- USER HOOK CALL FOR AFTER, ENDS
1049
1050 OKC_API.END_ACTIVITY(x_msg_count,x_msg_data);
1051
1052 EXCEPTION
1053 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1054 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1055 (l_api_name,
1056 G_PKG_NAME,
1057 'OKC_API.G_RET_STS_ERROR',
1058 x_msg_count,
1059 x_msg_data,
1060 '_PUB');
1061 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1062 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1063 (l_api_name,
1064 G_PKG_NAME,
1065 'OKC_API.G_RET_STS_UNEXP_ERROR',
1066 x_msg_count,
1067 x_msg_data,
1068 '_PUB');
1069 WHEN OTHERS THEN
1070 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1071 (l_api_name,
1072 G_PKG_NAME,
1073 'OTHERS',
1074 x_msg_count,
1075 x_msg_data,
1076 '_PUB');
1077 END validate_row;
1078
1079
1080 FUNCTION round_quantity(
1081 f_target_qty IN NUMBER
1082 ) RETURN NUMBER
1083 IS
1084
1085 l_round_quantity NUMBER;
1086
1087 BEGIN
1088 -- commented and changed select statement for bug 3497141
1089 -- SELECT (f_target_qty - ROUND(f_target_qty)) INTO l_round_quantity FROM DUAL ;
1090
1091 SELECT (f_target_qty - f_target_qty) INTO l_round_quantity FROM DUAL ;
1092 -- end comment and change for bug 3497141
1093 RETURN l_round_quantity;
1094
1095 END round_quantity;
1096
1097 --to get time value....
1098
1099 FUNCTION Create_Timevalue
1100 (
1101 l_start_date IN DATE,
1102 l_chr_id IN NUMBER
1103 ) RETURN NUMBER Is
1104
1105 l_p_tavv_tbl OKC_TIME_PUB.TAVV_TBL_TYPE;
1106 l_x_tavv_tbl OKC_TIME_PUB.TAVV_TBL_TYPE;
1107 l_api_version NUMBER := 1.0;
1108 l_init_msg_list VARCHAR2(1) := 'T';
1109 l_return_status VARCHAR2(200);
1110 l_msg_count NUMBER;
1111 l_msg_data VARCHAR2(2000);
1112 BEGIN
1113 l_p_tavv_tbl(1).id := NULL;
1114 l_p_tavv_tbl(1).object_version_number := NULL;
1115 l_p_tavv_tbl(1).sfwt_flag := 'N';
1116 l_p_tavv_tbl(1).spn_id := NULL;
1117 l_p_tavv_tbl(1).tve_id_generated_by := NULL;
1118 l_p_tavv_tbl(1).dnz_chr_id := NULL;
1119 l_p_tavv_tbl(1).tze_id := NULL;
1120 l_p_tavv_tbl(1).tve_id_limited := NULL;
1121 l_p_tavv_tbl(1).description := '';
1122 l_p_tavv_tbl(1).short_description := '';
1123 l_p_tavv_tbl(1).comments := '';
1124 l_p_tavv_tbl(1).datetime := to_date(NULL);
1125 l_p_tavv_tbl(1).attribute_category := '';
1126 l_p_tavv_tbl(1).attribute1 := '';
1127 l_p_tavv_tbl(1).attribute2 := '';
1128 l_p_tavv_tbl(1).attribute3 := '';
1129 l_p_tavv_tbl(1).attribute4 := '';
1130 l_p_tavv_tbl(1).attribute5 := '';
1131 l_p_tavv_tbl(1).attribute6 := '';
1132 l_p_tavv_tbl(1).attribute7 := '';
1133 l_p_tavv_tbl(1).attribute8 := '';
1134 l_p_tavv_tbl(1).attribute9 := '';
1135 l_p_tavv_tbl(1).attribute10 := '';
1136 l_p_tavv_tbl(1).attribute11 := '';
1137 l_p_tavv_tbl(1).attribute12 := '';
1138 l_p_tavv_tbl(1).attribute13 := '';
1139 l_p_tavv_tbl(1).attribute14 := '';
1140 l_p_tavv_tbl(1).attribute15 := '';
1141 l_p_tavv_tbl(1).created_by := NULL;
1142 l_p_tavv_tbl(1).creation_date := TO_DATE(NULL);
1143 l_p_tavv_tbl(1).last_updated_by := NULL;
1144 l_p_tavv_tbl(1).last_update_date := TO_DATE(NULL);
1145 l_p_tavv_tbl(1).last_update_login := NULL;
1146 l_p_tavv_tbl(1).datetime := l_start_date;
1147 l_p_tavv_tbl(1).dnz_chr_id := l_chr_id;
1148
1149 okc_time_pub.create_tpa_value
1150 (p_api_version => l_api_version,
1151 p_init_msg_list => l_init_msg_list,
1152 x_return_status => l_return_status,
1153 x_msg_count => l_msg_count,
1154 x_msg_data => l_msg_data,
1155 p_tavv_tbl => l_p_tavv_tbl,
1156 x_tavv_tbl => l_x_tavv_tbl) ;
1157 If l_return_status <> 'S' then
1158 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'Create TPA Value ');
1159 Raise G_EXCEPTION_HALT_VALIDATION;
1160 End If;
1161
1162 RETURN(l_x_tavv_tbl(1).id);
1163
1164 End Create_Timevalue;
1165 -------------
1166
1167 PROCEDURE Get_Billing_Schedule(
1168 p_api_version IN NUMBER,
1169 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1170 p_billing_profile_rec IN Billing_profile_rec,
1171 x_sll_tbl_out OUT NOCOPY Stream_Level_tbl,
1172 x_return_status OUT NOCOPY VARCHAR2,
1173 x_msg_count OUT NOCOPY NUMBER,
1174 x_msg_data OUT NOCOPY VARCHAR2 )
1175
1176 IS
1177 CURSOR l_billing_profile_csr(l_billing_profile_id NUMBER) IS
1178 SELECT BILLING_LEVEL,
1179 BILLING_TYPE,
1180 INTERVAL,
1181 INTERFACE_OFFSET,
1182 INVOICE_OFFSET,
1183 INVOICE_OBJECT1_ID1,
1184 --sum, jul,01
1185 ACCOUNT_OBJECT1_ID1
1186 --sum, jul,01
1187
1188
1189 FROM OKS_BILLING_PROFILES_V
1190 WHERE ID = l_billing_profile_Id;
1191
1192 Cursor get_day_uom_code IS
1193 select uom_code
1194 from okc_time_code_units_v
1195 where tce_code='DAY'
1196 and quantity=1;
1197
1198 /* cgopinee bugfix for 7596241*/
1199 CURSOR l_line_amt_csr(p_cle_id NUMBER) IS
1200 SELECT (nvl(line.price_negotiated,0) + nvl(dtl.ubt_amount,0) +
1201 nvl(dtl.credit_amount,0) + nvl(dtl.suppressed_credit,0)) line_amt
1202 FROM okc_k_lines_b line, oks_k_lines_b dtl
1203 WHERE line.id = dtl.cle_id AND line.Id = p_cle_id ;
1204
1205 l_api_version CONSTANT NUMBER := 1.0;
1206 l_return_status VARCHAR2(200);
1207 l_billing_profile_Csr_Rec l_billing_profile_csr%Rowtype;
1208 l_start_date p_billing_profile_rec.Start_Date%TYPE;
1209 l_end_date p_billing_profile_rec.End_Date%TYPE;
1210 l_billing_profile_id NUMBER;
1211 l_duration NUMBER := 0;
1212 l_timeunit VARCHAR2(10);
1213 l_source_uom VARCHAR2(100) := NULL;
1214 l_target_qty NUMBER;
1215 f_target_qty NUMBER; --used in function
1216 r_target_qty NUMBER; --used for rounded target quantity
1217 l_sll_index NUMBER := 1;
1218 l_chr_id NUMBER;
1219 l_timevalue_id NUMBER;
1220 l_billing_type VARCHAR2 (450);
1221 --22-NOV-2005 mchoudha
1222 --variable declaration for partial periods
1223 l_price_uom OKS_K_HEADERS_B.PRICE_UOM%TYPE;
1224 l_period_start OKS_K_HEADERS_B.PERIOD_START%TYPE;
1225 l_period_type OKS_K_HEADERS_B.PERIOD_TYPE%TYPE;
1226 l_quantity NUMBER;
1227 l_uom_code VARCHAR2(10);
1228
1229
1230 --new variables for bugfix7596241
1231 l_amount NUMBER;
1232 l_currency_code VARCHAR2(15);
1233 l_sll_prorate_tbl sll_prorated_tab_type;
1234
1235 -- Bug 5202220
1236
1237 Cursor csr_get_lse_id (p_cle_id number, p_chr_id number) IS
1238 select lse_id
1239 from okc_k_lines_b
1240 where id = p_cle_id
1241 and dnz_chr_id = p_chr_id;
1242
1243 l_lse_id number;
1244
1245 --End Bug 5202220
1246
1247 BEGIN
1248 l_billing_profile_id := p_billing_profile_rec.Billing_Profile_Id;
1249 l_start_date := p_billing_profile_rec.Start_Date;
1250 l_end_date := p_billing_profile_rec.End_Date;
1251 l_chr_id := p_billing_profile_rec.chr_id;
1252 --22-NOV-2005 commented by mchoudha. This call is not required due to Rules/Timevalues
1253 --rearchitecture
1254 --l_timevalue_id := create_timevalue(l_start_date,l_chr_id);
1255 --Partial period changes
1256 --get the partial period defaults for this contract
1257
1258 OKS_RENEW_UTIL_PUB.get_period_defaults(p_hdr_id => l_chr_id,
1259 p_org_id => NULL,
1260 x_period_type => l_period_type,
1261 x_period_start => l_period_start,
1262 x_price_uom => l_price_uom,
1263 x_return_status => l_return_status);
1264 IF l_return_status <> 'S' THEN
1265 Raise G_EXCEPTION_HALT_VALIDATION;
1266 END IF;
1267 -- to get Periods
1268 --l_source_uom is the Interval of oks_billing_profiles_v
1269 OPEN l_billing_profile_csr(l_billing_profile_id );
1270 FETCH l_billing_profile_csr INTO l_billing_profile_Csr_Rec;
1271 CLOSE l_billing_profile_csr;
1272
1273 --sll
1274 x_sll_tbl_out(1).seq_no := '1';
1275 x_sll_tbl_out(1).Start_Date := l_start_date;
1276 x_sll_tbl_out(1).amount := NULL;
1277 x_sll_tbl_out(1).sll_Rule_Information_Category := 'SLL';
1278 x_sll_tbl_out(1).sll_Object1_Id1 := NULL;
1279 x_sll_tbl_out(1).sll_Object1_Id2 := '#';
1280 x_sll_tbl_out(1).sll_Jtot_Object1_Code := 'OKS_TUOM';
1281
1282
1283 --slh
1284 x_sll_tbl_out(1).chr_id := p_billing_profile_rec.chr_id;
1285 x_sll_tbl_out(1).cle_id := p_billing_profile_rec.cle_id;
1286 x_sll_tbl_out(1).Billing_type := l_billing_profile_Csr_Rec.BILLING_LEVEL;
1287 x_sll_tbl_out(1).stream_type_id1 := '1';
1288 x_sll_tbl_out(1).stream_type_id2 := '#';
1289 x_sll_tbl_out(1).stream_tp_code := 'OKS_STRM_TYPE';
1290 x_sll_tbl_out(1).slh_timeval_id1 := l_timevalue_id;
1291 x_sll_tbl_out(1).slh_timeval_id2 := '#';
1292 x_sll_tbl_out(1).slh_timeval_code := 'OKS_TIMEVAL';
1293 x_sll_tbl_out(1).Rule_Information_Category := 'SLH';
1294
1295 l_source_uom := l_billing_profile_Csr_Rec.INTERVAL;
1296
1297 x_sll_tbl_out(1).interface_offset := l_billing_profile_Csr_Rec.INTERFACE_OFFSET;
1298 --- x_sll_tbl_out(1).invoice_offset := l_billing_profile_Csr_Rec.INVOICE_OFFSET;
1299 x_sll_tbl_out(1).Invoice_Rule_Id := l_billing_profile_Csr_Rec.INVOICE_OBJECT1_ID1;
1300 --sum, jul,01
1301 x_sll_tbl_out(1).Account_Rule_Id := l_billing_profile_Csr_Rec.ACCOUNT_OBJECT1_ID1;
1302 --sum, jul,01
1303
1304
1305 --Bug 5202220
1306
1307 Open csr_get_lse_id ( p_billing_profile_rec.cle_id, p_billing_profile_rec.chr_id);
1308 Fetch csr_get_lse_id into l_lse_id;
1309 Close csr_get_lse_id;
1310
1311 /*for bug 10029566*/
1312 IF l_billing_profile_Csr_Rec.INVOICE_OBJECT1_ID1 =-3 THEN
1313 IF l_lse_id=12 THEN
1314 x_sll_tbl_out(1).invoice_offset :=NULL;
1315 ELSE
1316 x_sll_tbl_out(1).invoice_offset := l_billing_profile_Csr_Rec.INVOICE_OFFSET;
1317 END IF;
1318 ELSE
1319 x_sll_tbl_out(1).invoice_offset := l_billing_profile_Csr_Rec.INVOICE_OFFSET;
1320 END IF;
1321 /*for bug 10029566*/
1322
1323 IF l_period_start IS NOT NULL AND
1324 l_period_type IS NOT NULL AND
1325 l_lse_id = 12
1326 THEN
1327 l_period_start := 'SERVICE';
1328 END IF;
1329
1330 --End Bug 5202220
1331
1332 IF l_billing_profile_Csr_Rec.BILLING_TYPE = 'ONETIME' THEN
1333 --partial periods changes
1334 IF l_period_start ='CALENDAR' AND l_period_type is not null THEN
1335 Open get_day_uom_code;
1336 Fetch get_day_uom_code into l_uom_code;
1337 Close get_day_uom_code;
1338 x_sll_tbl_out(1).target_quantity := 1;
1339 x_sll_tbl_out(1).duration := l_end_date-l_start_date+1; --UOM/PERIOD
1340 x_sll_tbl_out(1).timeunit := l_uom_code; -- UOM
1341
1342 ELSE
1343 x_sll_tbl_out(1).target_quantity := 1;
1344 OKC_TIME_UTIL_PUB.get_duration(
1345 p_start_date => l_start_date
1346 , p_end_date => l_end_date
1347 , x_duration => l_duration
1348 , x_timeunit => l_timeunit
1349 , x_return_status => x_return_status);
1350
1351 x_sll_tbl_out(1).duration := l_duration; --UOM/PERIOD
1352 x_sll_tbl_out(1).timeunit := l_timeunit; -- UOM
1353 END IF;
1354 ELSE
1355 IF l_billing_profile_Csr_Rec.BILLING_TYPE = 'RECURRING' THEN
1356 --partial periods changes
1357 IF l_period_start ='CALENDAR' AND l_period_type is not null THEN
1358 IF l_source_uom is not null THEN
1359 l_quantity:=OKS_BILL_UTIL_PUB.Get_Periods
1360 (
1361 p_start_date => l_start_date,
1362 p_end_date => l_end_date,
1363 p_uom_code => l_source_uom,
1364 p_period_start => l_period_start
1365 );
1366 x_sll_tbl_out(1).target_quantity := l_quantity;
1367 x_sll_tbl_out(1).duration := 1; --UOM/PERIOD
1368 x_sll_tbl_out(1).timeunit := l_source_uom; -- UOM
1369
1370 END IF;
1371 ELSE
1372 l_target_qty := OKS_TIME_MEASURES_PUB.get_quantity(
1373 l_start_date
1374 , l_end_date
1375 , l_source_uom);
1376
1377 r_target_qty := round_quantity( f_target_qty => l_target_qty);
1378 IF r_target_qty = 0 THEN -- i.e get_quantity returns a whole number
1379 -- added function ceil to the variable l_target_qty for bug 3497141
1380 x_sll_tbl_out(1).target_quantity := ceil(l_target_qty);
1381 x_sll_tbl_out(1).duration := 1; --UOM/PERIOD
1382 x_sll_tbl_out(1).timeunit := l_source_uom; -- UOM
1383
1384 ELSE
1385 x_sll_tbl_out(1).target_quantity := 1;
1386 OKC_TIME_UTIL_PUB.get_duration(
1387 p_start_date => l_start_date
1388 , p_end_date => l_end_date
1389 , x_duration => l_duration
1390 , x_timeunit => l_timeunit
1391 , x_return_status => x_return_status);
1392
1393
1394 x_sll_tbl_out(1).duration := l_duration; --UOM/PERIOD
1395 x_sll_tbl_out(1).timeunit := l_timeunit; -- UOM
1396 END IF;
1397 END IF; -- l_period_start check
1398 END IF;
1399 END IF;
1400
1401 --slh record
1402 /*CGOPINEE Bugfix for 7596241 start*/
1403
1404 l_sll_prorate_tbl.DELETE;
1405
1406 l_sll_prorate_tbl(1).sll_seq_num := 1;
1407 l_sll_prorate_tbl(1).sll_start_date := l_start_date;
1408 l_sll_prorate_tbl(1).sll_end_date := l_end_date;
1409 l_sll_prorate_tbl(1).sll_tuom := x_sll_tbl_out(1).timeunit;
1410 l_sll_prorate_tbl(1).sll_period := x_sll_tbl_out(1).target_quantity;
1411 l_sll_prorate_tbl(1).sll_uom_per_period := x_sll_tbl_out(1).duration;
1412
1413 l_currency_code := Find_Currency_Code(
1414 p_cle_id => p_billing_profile_rec.cle_id,
1415 p_chr_id => p_billing_profile_rec.chr_id);
1416
1417 IF (p_billing_profile_rec.cle_id IS NOT NULL) THEN
1418 OPEN l_line_amt_csr(p_billing_profile_rec.cle_id);
1419 FETCH l_line_amt_csr INTO l_amount;
1420 CLOSE l_line_amt_csr;
1421 ELSE
1422 RAISE G_EXCEPTION_HALT_VALIDATION;
1423 END IF;
1424
1425 CALCULATE_SLL_AMOUNT(
1426 P_API_VERSION => l_api_version,
1427 P_TOTAL_AMOUNT => l_amount,
1428 P_CURRENCY_CODE => l_currency_code,
1429 p_period_start => l_period_start,
1430 p_period_type => l_period_type,
1431 P_SLL_PRORATED_TAB => l_sll_prorate_tbl,
1432 X_RETURN_STATUS => X_RETURN_STATUS);
1433
1434 IF X_RETURN_STATUS='S' THEN
1435 x_sll_tbl_out(1).amount := l_sll_prorate_tbl(1).sll_amount;
1436 END IF;
1437 /*CGOPINEE Bugfix for 7596241 end*/
1438
1439 EXCEPTION
1440 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1441 NULL;
1442 WHEN OTHERS THEN
1443 OKC_API.SET_MESSAGE(p_app_name => G_APP_NAME_OKC,
1444 p_msg_name => G_UNEXPECTED_ERROR,
1445 p_token1 => G_SQLCODE_TOKEN,
1446 p_token1_value => sqlcode,
1447 p_token2 => G_SQLERRM_TOKEN,
1448 p_token2_value => sqlerrm);
1449
1450 x_return_status := G_RET_STS_UNEXP_ERROR;
1451 END;
1452
1453 END OKS_BILLING_PROFILES_PUB;