[Home] [Help]
PACKAGE BODY: APPS.OKL_CNTR_GRP_BILLING_PVT
Source
1 PACKAGE BODY OKL_CNTR_GRP_BILLING_PVT AS
2 /* $Header: OKLRCLBB.pls 120.8 2008/02/25 12:11:14 udhenuko noship $ */
3
4 PROCEDURE counter_grp_billing_calc(
5 p_api_version IN NUMBER
6 ,p_init_msg_list IN VARCHAR2
7 ,x_return_status OUT NOCOPY VARCHAR2
8 ,x_msg_count OUT NOCOPY NUMBER
9 ,x_msg_data OUT NOCOPY VARCHAR2
10 ,p_cntr_bill_rec IN cntr_bill_rec_type
11 ,x_cntr_bill_rec OUT NOCOPY cntr_bill_rec_type
12 ) IS
13 ---------------------------
14 -- DECLARE Local Variables
15 ---------------------------
16
17 l_api_version NUMBER := 1.0;
18 l_init_msg_list VARCHAR2(1) := Okc_Api.g_false;
19
20 l_return_status VARCHAR2(1);
21 l_msg_count NUMBER;
22 l_msg_data VARCHAR2(2000);
23 i NUMBER;
24
25
26 BEGIN
27 NULL ;
28 END;
29
30
31 PROCEDURE counter_grp_billing_calc(
32 p_api_version IN NUMBER
33 ,p_init_msg_list IN VARCHAR2
34 ,x_return_status OUT NOCOPY VARCHAR2
35 ,x_msg_count OUT NOCOPY NUMBER
36 ,x_msg_data OUT NOCOPY VARCHAR2
37 ,p_cntr_bill_tbl IN cntr_bill_tbl_type
38 ,x_cntr_bill_tbl OUT NOCOPY cntr_bill_tbl_type
39 ) IS
40
41 ---------------------------
42 -- DECLARE Local Variables
43 ---------------------------
44
45 l_api_name CONSTANT VARCHAR2(30) := 'OKL_CNTR_GRP_BILLING_PVT';
46 l_api_version NUMBER := 1.0;
47 l_init_msg_list VARCHAR2(1) := Okc_Api.g_false;
48 l_return_status VARCHAR2(1);
49 l_msg_count NUMBER;
50 l_msg_data VARCHAR2(2000);
51 i NUMBER;
52 j NUMBER;
53
54 l_clc_ext EXCEPTION;
55
56 ------------------------------
57 -- DECLARE Record/Table Types
58 ------------------------------
59
60 l_bill_rec bill_rec_type;
61 x_bill_rec bill_rec_type;
62 l_bill_tbl bill_tbl_type;
63 x_bill_tbl bill_tbl_type;
64 l_cntr_bill_rec cntr_bill_rec_type;
65 x_cntr_bill_rec cntr_bill_rec_type;
66 l_cntr_bill_tbl cntr_bill_tbl_type := p_cntr_bill_tbl;
67
68 -------------------
69 -- DECLARE Cursors
70 -------------------
71
72
73 BEGIN
74
75 x_return_status := OKL_API.G_RET_STS_SUCCESS;
76
77 l_return_status := OKL_API.START_ACTIVITY(
78 p_api_name => l_api_name,
79 p_pkg_name => g_pkg_name,
80 p_init_msg_list => p_init_msg_list,
81 l_api_version => l_api_version,
82 p_api_version => p_api_version,
83 p_api_type => '_PVT',
84 x_return_status => l_return_status);
85
86 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
87 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
88 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
89 RAISE OKL_API.G_EXCEPTION_ERROR;
90 END IF;
91
92
93 -- dbms_output.put_line('Count of Rec: '||l_cntr_bill_tbl.COUNT);
94
95 IF (l_cntr_bill_tbl.COUNT > 0) THEN
96 i := l_cntr_bill_tbl.FIRST;
97 --dbms_output.put_line('Count of Rec: I '||i);
98 LOOP
99
100 l_cntr_bill_rec := l_cntr_bill_tbl(i);
101
102 -- Assign the values for columns of bill_rec from cntr_bill_rec record
103 l_bill_rec.Counter_id := l_cntr_bill_rec.counter_number;
104 l_bill_rec.Reading_date := l_cntr_bill_rec.counter_reading_date;
105 l_bill_rec.Meter_reading := l_cntr_bill_rec.counter_reading;
106 l_bill_rec.Last_Meter_reading := null;
107 l_bill_rec.Net_reading := null;
108 l_bill_rec.Level_reading := null;
109 l_bill_rec.Bill_amount := null;
110
111 l_bill_tbl(i) := l_bill_rec;
112
113 --dbms_output.put_line('Value of Counter Id '||l_bill_rec.Counter_id);
114 EXIT WHEN (i = l_cntr_bill_tbl.LAST);
115 i := l_cntr_bill_tbl.NEXT(i);
116
117 END LOOP;
118
119 --Calling the Calculate procedure in OKS
120
121 --dbms_output.put_line('Before Oks Call Status '||x_return_status);
122 x_msg_data := l_msg_data;
123
124 oks_bill_util_pub.Calculate_Bill_Amount (
125 p_api_version => l_api_version,
126 p_init_msg_list => l_init_msg_list,
127 p_bill_tbl => l_bill_tbl,
128 x_return_status => l_return_status,
129 x_msg_count => l_msg_count,
130 x_msg_data => l_msg_data);
131
132 x_return_status := l_return_status;
133 x_msg_data := l_msg_data;
134 x_msg_count := l_msg_count;
135 x_bill_tbl := l_bill_tbl;
136 x_cntr_bill_tbl := p_cntr_bill_tbl;
137 --dbms_output.put_line('After Oks Call Status '||x_return_status);
138
139 --Assign the bill_amount from out record to the counter_bill_amount
140
141 IF (x_bill_tbl.COUNT > 0) THEN
142 i := x_bill_tbl.FIRST;
143 j := x_cntr_bill_tbl.FIRST;
144
145 LOOP
146
147 x_cntr_bill_rec := x_cntr_bill_tbl(j);
148 x_bill_rec := x_bill_tbl(i);
149
150 --dbms_output.put_line('Amount in Bill Rec is ' || x_bill_rec.Bill_amount);
151 -- Assign the values for columns of bill_rec from cntr_bill_rec record
152 x_cntr_bill_rec.counter_bill_amount := x_bill_rec.Bill_amount;
153
154 x_cntr_bill_tbl(j) := x_cntr_bill_rec;
155
156 EXIT WHEN (i = x_cntr_bill_tbl.LAST);
157 i := x_bill_tbl.NEXT(i);
158 j := x_cntr_bill_tbl.NEXT(j);
159
160 END LOOP;
161
162 END IF;
163
164 END IF;
165
166 IF (x_return_status = Fnd_Api.G_RET_STS_ERROR) THEN
167 RAISE l_clc_ext;
168 ELSIF (x_return_status = Fnd_Api.G_RET_STS_UNEXP_ERROR ) THEN
169 RAISE l_clc_ext;
170 END IF;
171 OKL_API.END_ACTIVITY (x_msg_count, x_msg_data);
172
173 EXCEPTION
174 WHEN l_clc_ext THEN
175 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
176 x_msg_count := l_msg_count ;
177 l_msg_data := 'Problems with Calculate';
178 x_msg_data := l_msg_data ;
179
180 WHEN others THEN
181 --dbms_output.put_line('In Exception '||l_msg_count||'data: '||l_msg_data);
182 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
183 x_msg_count := l_msg_count ;
184 l_msg_data := '';
185 x_msg_data := l_msg_data ;
186
187 END counter_grp_billing_calc;
188
189 PROCEDURE counter_grp_billing_insert(
190 p_api_version IN NUMBER
191 ,p_init_msg_list IN VARCHAR2
192 ,x_return_status OUT NOCOPY VARCHAR2
193 ,x_msg_count OUT NOCOPY NUMBER
194 ,x_msg_data OUT NOCOPY VARCHAR2
195 ,p_cntr_bill_rec IN cntr_bill_rec_type
196 ,x_cntr_bill_rec OUT NOCOPY cntr_bill_rec_type
197 ) IS
198 ---------------------------
199 -- DECLARE Local Variables
200 ---------------------------
201
202 BEGIN
203 NULL ;
204 END;
205
206 PROCEDURE counter_grp_billing_insert(
207 p_api_version IN NUMBER
208 ,p_init_msg_list IN VARCHAR2
209 ,x_return_status OUT NOCOPY VARCHAR2
210 ,x_msg_count OUT NOCOPY NUMBER
211 ,x_msg_data OUT NOCOPY VARCHAR2
212 ,p_cntr_bill_tbl IN cntr_bill_tbl_type
213 ,x_cntr_bill_tbl OUT NOCOPY cntr_bill_tbl_type
214 ) IS
215
216 ---------------------------
217 -- DECLARE Local Variables
218 ---------------------------
219 l_api_name CONSTANT VARCHAR2(30) := 'OKL_CNTR_GRP_BILLING_PVT';
220
221 l_api_version NUMBER := 1.0;
222 l_init_msg_list VARCHAR2(1) := Okc_Api.g_false;
223 l_return_status VARCHAR2(1);
224 l_msg_count NUMBER;
225 l_msg_data VARCHAR2(2000);
226
227 l_contract_num OKC_K_HEADERS_B.CONTRACT_NUMBER%TYPE;
228 l_cntr_num NUMBER;
229 l_amount NUMBER;
230 l_khr_id OKC_K_HEADERS_B.ID%TYPE;
231 l_kle_id OKC_K_LINES_B.ID%TYPE;
232 l_try_id okl_trx_types_tl.ID%TYPE;
233 l_clg_id NUMBER;
234 l_asset_name okc_k_lines_v.name%type; --added by dkagrawa for bug#6015684
235
236 --for install base api
237 l_cntr_grp_id NUMBER;
238 l_cntr_prop_id NUMBER;
239 l_cntr_prop_val cs_counter_properties.default_value%TYPE;
240 l_counter_reading NUMBER;
241
242 i NUMBER := 1;
243
244 l_ins_ext EXCEPTION;
245 l_sty_id okl_strm_type_v.id%TYPE;
246
247 ----------------------------
248 -- DECLARE Records/Tables
249 ----------------------------
250
251 l_cntr_bill_tbl cntr_bill_tbl_type := p_cntr_bill_tbl;
252 l_cntr_bill_rec cntr_bill_rec_type;
253
254 l_taiv_rec taiv_rec_type;
255 lx_taiv_rec taiv_rec_type;
256
257 l_tilv_rec tilv_rec_type;
258 lx_tilv_rec tilv_rec_type;
259
260 l_tldv_rec tldv_rec_type;
261 lx_tldv_rec tldv_rec_type;
262
263 l_ctr_grp_log_rec crdg_rec_type;
264 l_ctr_rdg_tbl crdg_tbl_type;
265 l_prop_rdg_tbl prdg_tbl_type;
266
267 ------------------------------------------------------------
268 -- Declare variables to call Accounting Engine.
269 ------------------------------------------------------------
270 p_bpd_acc_rec Okl_Acc_Call_Pub.bpd_acc_rec_type;
271
272
273 ---------------------------
274 -- DECLARE Cursors
275 ---------------------------
276
277
278 CURSOR l_khr_cur(l_contract_num IN VARCHAR2) IS
279 SELECT HDR.ID KHR_ID
280 FROM OKC_K_HEADERS_B HDR
281 WHERE CONTRACT_NUMBER = l_contract_num;
282
283 --dkagrawa modified the following cursor for bug#6015684
284 CURSOR l_lines_cur(l_asset_name IN VARCHAR2,l_chr_id in number) IS
285 SELECT cle.id KLE_ID
286 FROM okc_k_lines_v cle,
287 okc_line_styles_b lse
288 WHERE lse.id =cle.lse_id
289 AND lse.lty_code ='FREE_FORM1'
290 AND cle.name =l_asset_name
291 AND cle.dnz_chr_id=l_chr_id;
292
293 CURSOR l_try_id_cur IS
294 SELECT ID
295 FROM okl_trx_types_tl
296 WHERE NAME = 'Billing' and LANGUAGE = 'US';
297
298 CURSOR l_cgrp_id_cur(l_cntr_num IN NUMBER) IS
299
300 -- Query updated for performance issue #5484903
301 select DEFAULTED_GROUP_ID counter_group_id
302 from CSI_COUNTER_TEMPLATE_B
303 where counter_id=l_cntr_num
304 union all
305 select defaulted_group_id counter_group_id
306 from CSI_COUNTERS_B
307 where counter_id=l_cntr_num;
308
309 /* -- commented for performance issue #5484903
310 SELECT counter_group_id
311 FROM CS_COUNTERS
312 WHERE counter_id = l_cntr_num; */
313
314 CURSOR l_cntr_prop_cur(l_cntr_num IN NUMBER) IS
315
316 -- Query updated for performance issue #5484903
317 SELECT COUNTER_PROPERTY_ID,
318 DEFAULT_VALUE
319 from CSI_CTR_PROPERTY_TEMPLATE_B
320 where counter_id=l_cntr_num
321 UNION ALL
322 SELECT COUNTER_PROPERTY_ID,
323 DEFAULT_VALUE
324 FROM CSI_COUNTER_PROPERTIES_B
325 where counter_id=l_cntr_num;
326
327 /* -- commented for performance issue #5484903
328 SELECT counter_property_id, default_value
329 FROM cs_counter_properties
330 WHERE counter_id = l_cntr_num; */
331
332 BEGIN
333
334 x_return_status := OKL_API.G_RET_STS_SUCCESS;
335
336 l_return_status := OKL_API.START_ACTIVITY(
337 p_api_name => l_api_name,
338 p_pkg_name => g_pkg_name,
339 p_init_msg_list => p_init_msg_list,
340 l_api_version => l_api_version,
341 p_api_version => p_api_version,
342 p_api_type => '_PVT',
343 x_return_status => l_return_status);
344
345
346 IF (l_cntr_bill_tbl.COUNT > 0) THEN
347 i := l_cntr_bill_tbl.FIRST;
348
349 LOOP
350
351 l_cntr_bill_rec := l_cntr_bill_tbl(i);
352 l_contract_num := l_cntr_bill_rec.contract_number;
353 l_cntr_num := l_cntr_bill_rec.counter_number;
354 l_amount := l_cntr_bill_rec.counter_bill_amount;
355 l_clg_id := l_cntr_bill_rec.clg_id;
356 l_asset_name := l_cntr_bill_rec.asset_number; --added by dkagrawa for bug#6015684
357
358 l_counter_reading := l_cntr_bill_rec.counter_reading;
359
360 IF NVL(l_amount,0) > 0 THEN -- Bug 4902775
361
362 -- Two level insertion
363
364 -- Header level
365
366 OPEN l_khr_cur(l_contract_num);
367 FETCH l_khr_cur INTO l_khr_id;
368 CLOSE l_khr_cur;
369
370
371 OPEN l_try_id_cur;
372 FETCH l_try_id_cur INTO l_try_id;
373 CLOSE l_try_id_cur;
374
375 l_taiv_rec.trx_status_code := 'SUBMITTED';
376 l_taiv_rec.sfwt_flag := 'Y';
377 l_taiv_rec.khr_id := l_khr_id;
378 l_taiv_rec.try_id := l_try_id;
379 l_taiv_rec.amount := l_amount;
380 l_taiv_rec.date_invoiced := sysdate;
381 l_taiv_rec.date_entered := sysdate;
382
383 l_taiv_rec.legal_entity_id := l_cntr_bill_rec.legal_entity_id;
384 l_taiv_rec.clg_id := l_clg_id;
385 -- udhenuko Bug 6655198 Start
386 -- We need to assign the Billing Source as Counter.
387 l_taiv_rec.OKL_SOURCE_BILLING_TRX := 'COUNTER';
388 -- udhenuko Bug 6655198 End
389
390 --dbms_output.put_line('Inserting into internal tables in Pvt');
391 --dbms_output.put_line('khr_id is in Pvt ' || l_khr_id);
392 --dbms_output.put_line('try_id is in Pvt ' || l_try_id);
393 --dbms_output.put_line('Inserting into internal tables in Pvt');
394
395 --Header insertion
396 Okl_Trx_Ar_Invoices_Pub.insert_trx_ar_invoices(
397 l_api_version
398 ,l_init_msg_list
399 ,l_return_status
400 ,l_msg_count
401 ,l_msg_data
402 ,l_taiv_rec
403 ,lx_taiv_rec);
404
405 --dbms_output.put_line('return status from tai is ' || l_return_status);
406 --dbms_output.put_line('l_msg_data from tai is ' || l_msg_data);
407
408 IF ( l_return_status = Fnd_Api.G_RET_STS_ERROR ) THEN
409 RAISE Fnd_Api.G_EXC_ERROR;
410 ELSIF (l_return_status = Fnd_Api.G_RET_STS_UNEXP_ERROR ) THEN
411 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
412 END IF;
413
414 --Line Level
415 OPEN l_lines_cur(l_asset_name, l_khr_id );-- for performance issue #5484903
416 FETCH l_lines_cur INTO l_kle_id;
417 CLOSE l_lines_cur;
418
419
420 l_tilv_rec.sfwt_flag := 'Y';
421 l_tilv_rec.amount := l_amount;
422 l_tilv_rec.tai_id := lx_taiv_rec.id;
423 l_tilv_rec.INV_RECEIV_LINE_CODE := 'LINE';
424 l_tilv_rec.LINE_NUMBER := i;
425 l_tilv_rec.KLE_ID := l_kle_id;
426
427 --line level insertion
428 okl_txl_ar_inv_lns_pub.insert_txl_ar_inv_lns(
429 l_api_version
430 ,l_init_msg_list
431 ,l_return_status
432 ,l_msg_count
433 ,l_msg_data
434 ,l_tilv_rec
435 ,lx_tilv_rec);
436
437 --dbms_output.put_line('Successfully inserted into Lines :'||l_return_status);
438
439 IF ( l_return_status = Fnd_Api.G_RET_STS_ERROR ) THEN
440 RAISE Fnd_Api.G_EXC_ERROR;
441 ELSIF (l_return_status = Fnd_Api.G_RET_STS_UNEXP_ERROR ) THEN
442 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
443 END IF;
444
445 -- Get sty_id for the contract
446 Okl_Streams_Util.get_primary_stream_type(
447 p_khr_id => l_khr_id,
448 p_primary_sty_purpose => 'USAGE_PAYMENT',
449 x_return_status => l_return_status,
450 x_primary_sty_id => l_sty_id );
451
452 IF (l_return_status = 'S' ) THEN
453 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -- Stream Id for purpose USAGE_PAYMENT retrieved.');
454 ELSE
455 FND_FILE.PUT_LINE (FND_FILE.LOG, ' -- ERROR: Could not retrieve Stream Id for purpose USAGE_PAYMENT.');
456 END IF;
457
458 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
459 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
460 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
461 RAISE Okl_Api.G_EXCEPTION_ERROR;
462 END IF;
463
464 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'Stream Type => '||l_sty_id);
465
466 l_tldv_rec.sty_id := l_sty_id;
467 l_tldv_rec.sfwt_flag := 'Y';
468 l_tldv_rec.amount := l_amount;
469 l_tldv_rec.til_id_details := lx_tilv_rec.id;
470 l_tldv_rec.line_detail_number := 1;
471 -- udhenuko Bug 6655198 Start
472 -- The accounting packages retreive the contract and asset id from
473 -- the distribution table. So populating these values in the record.
474 l_tldv_rec.khr_id := l_khr_id;
475 l_tldv_rec.KLE_ID := l_kle_id;
476 -- udhenuko Bug 6655198 End
477
478 Okl_Txd_Ar_Ln_Dtls_Pub.insert_txd_ar_ln_dtls(
479 l_api_version
480 ,l_init_msg_list
481 ,l_return_status
482 ,l_msg_count
483 ,l_msg_data
484 ,l_tldv_rec
485 ,lx_tldv_rec);
486
487
488 IF ( l_return_status = Fnd_Api.G_RET_STS_ERROR ) THEN
489 RAISE Fnd_Api.G_EXC_ERROR;
490 ELSIF (l_return_status = Fnd_Api.G_RET_STS_UNEXP_ERROR ) THEN
491 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
492 END IF;
493
494 p_bpd_acc_rec.id := lx_tldv_rec.id;
495 p_bpd_acc_rec.source_table := 'OKL_TXD_AR_LN_DTLS_B';
496 ----------------------------------------------------
497 -- Create Accounting Distributions
498 ----------------------------------------------------
499 Okl_Acc_Call_Pub.CREATE_ACC_TRANS(
500 p_api_version
501 ,p_init_msg_list
502 ,x_return_status
503 ,x_msg_count
504 ,x_msg_data
505 ,p_bpd_acc_rec
506 );
507
508 --dbms_output.put_line('success?'||x_return_status);
509
510 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
511 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
512 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
513 RAISE Okl_Api.G_EXCEPTION_ERROR;
514 END IF;
515
516 END IF; -- Bug 4902775
517
518 --
519 --Insert into Install Base
520
521 OPEN l_cgrp_id_cur(l_cntr_num);
522 FETCH l_cgrp_id_cur INTO l_cntr_grp_id;
523 CLOSE l_cgrp_id_cur;
524
525 OPEN l_cntr_prop_cur(l_cntr_num);
526 FETCH l_cntr_prop_cur INTO l_cntr_prop_id, l_cntr_prop_val;
527
528 --dbms_output.put_line('Calling IB Api in Pvt');
529 l_ctr_grp_log_rec.COUNTER_GROUP_ID := l_cntr_grp_id;
530 -- dbms_output.put_line('Counter Group'||l_cntr_grp_id);
531 l_ctr_grp_log_rec.VALUE_TIMESTAMP := l_cntr_bill_rec.counter_reading_date;
532 l_ctr_grp_log_rec.SOURCE_TRANSACTION_ID := l_clg_id;
533 -- dbms_output.put_line('transaction id'||l_clg_id);
534 l_ctr_grp_log_rec.SOURCE_TRANSACTION_CODE := 'OKL_CNTR_GRP';
535 l_ctr_rdg_tbl(1).COUNTER_ID := l_cntr_num;
536 -- dbms_output.put_line('counter_id'||l_cntr_num);
537 l_ctr_rdg_tbl(1).VALUE_TIMESTAMP := l_cntr_bill_rec.counter_reading_date;
538 l_ctr_rdg_tbl(1).COUNTER_READING := l_counter_reading;
539 -- dbms_output.put_line('counter_reading'||l_counter_reading);
540 l_ctr_rdg_tbl(1).VALID_FLAG := 'Y';
541 -- udhenuko Bug 6655198 Start
542 -- For some reason the CS_CTR_CAPTURE_READING_PUB package has this override flag value assigned to
543 -- disabled flag. So when we set this as 'Y' CSI_COUNTER_READINGS_PUB treats the record as disabled and
544 -- the error message 'CSI_API_CTR_NO_RDG_DISABLE' is thrown. So setting this value
545 -- to 'N'
546 l_ctr_rdg_tbl(1).OVERRIDE_VALID_FLAG := 'N';
547 -- udhenuko Bug 6655198 End
548
549 l_ctr_rdg_tbl(1).COMMENTS := NULL; -- addressing bug 3468630
550
551 IF l_cntr_prop_cur%found THEN
552 l_prop_rdg_tbl(1).COUNTER_PROPERTY_ID := l_cntr_prop_id;
553 l_prop_rdg_tbl(1).VALUE_TIMESTAMP := SYSDATE;
554 l_prop_rdg_tbl(1).PROPERTY_VALUE := l_cntr_prop_val;
555 END IF;
556 CLOSE l_cntr_prop_cur;
557
558
559
560 -- Now call the stored program
561 cs_ctr_capture_reading_pub.capture_counter_reading(
562 1.0
563 ,''
564 ,''
565 -- ,NULL
566 ,FND_API.G_VALID_LEVEL_FULL -- addressing bug 3468630
567 ,l_ctr_grp_log_rec
568 ,l_ctr_rdg_tbl
569 ,l_prop_rdg_tbl
570 ,l_return_status
571 ,l_msg_count
572 ,l_msg_data);
573
574
575 -- dbms_output.put_line('msg_date from install base :'||l_msg_data);
576 IF ( l_return_status = Fnd_Api.G_RET_STS_ERROR ) THEN
577 RAISE Fnd_Api.G_EXC_ERROR;
578 ELSIF (l_return_status = Fnd_Api.G_RET_STS_UNEXP_ERROR ) THEN
579 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
580 END IF;
581
582
583 x_return_status := l_return_status;
584 x_msg_data := l_msg_data;
585 x_msg_count := l_msg_count;
586
587 EXIT WHEN (i = l_cntr_bill_tbl.LAST);
588 i := l_cntr_bill_tbl.NEXT(i);
589 END LOOP;
590
591 END IF;
592 IF (x_return_status = Fnd_Api.G_RET_STS_ERROR) THEN
593 RAISE l_ins_ext;
594 ELSIF (x_return_status = Fnd_Api.G_RET_STS_UNEXP_ERROR ) THEN
595 RAISE l_ins_ext;
596 END IF;
597 OKL_API.END_ACTIVITY (x_msg_count, x_msg_data);
598
599 EXCEPTION
600 WHEN l_ins_ext THEN
601 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
602 x_msg_count := l_msg_count ;
603 l_msg_data := 'Problems with Insert into internal tables';
604 x_msg_data := l_msg_data ;
605
606 WHEN others THEN
607 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
608 x_msg_count := l_msg_count ;
609 l_msg_data := '';
610 x_msg_data := l_msg_data ;
611
612 END counter_grp_billing_insert;
613
614 END OKL_CNTR_GRP_BILLING_PVT;