[Home] [Help]
Skip to content
PACKAGE BODY: APPS.OKL_UBB_PVT
Source
1 PACKAGE BODY OKL_UBB_PVT AS
2 /* $Header: OKLRUBBB.pls 120.11 2007/08/13 11:53:57 zrehman noship $ */
3
4 G_MODULE VARCHAR2(255) := 'okl.stream.esg.okl_esg_transport_pvt';
5 G_DEBUG_ENABLED CONSTANT VARCHAR2(10) := OKL_DEBUG_PUB.CHECK_LOG_ENABLED;
6 G_IS_DEBUG_STATEMENT_ON BOOLEAN;
7 G_DEBUG NUMBER := 1;
8 ubb_failed EXCEPTION;
9
10 -- Start of wraper code generated automatically by Debug code generator
11
12 L_MODULE VARCHAR2(40) := 'LEASE.RECEIVABLES.BILLING';
13 L_DEBUG_ENABLED CONSTANT VARCHAR2(10) := OKL_DEBUG_PUB.CHECK_LOG_ENABLED;
14 L_LEVEL_PROCEDURE NUMBER;
15 IS_DEBUG_PROCEDURE_ON BOOLEAN;
16 -- End of wraper code generated automatically by Debug code generator
17
18 ------------------------------------------------------------------
19 -- Function GET_TRX_TYPE to extract transaction type
20 ------------------------------------------------------------------
21
22 FUNCTION get_trx_type
23 (p_name VARCHAR2,
24 p_language VARCHAR2)
25 RETURN NUMBER IS
26
27 CURSOR c_trx_type (cp_name VARCHAR2, cp_language VARCHAR2) IS
28 SELECT id
29 FROM okl_trx_types_tl
30 WHERE name = cp_name
31 AND LANGUAGE = cp_language;
32
33 l_trx_type okl_trx_types_v.id%TYPE;
34
35 BEGIN
36
37 l_trx_type := NULL;
38
39 OPEN c_trx_type (p_name, p_language);
40 FETCH c_trx_type INTO l_trx_type;
41 CLOSE c_trx_type;
42
43 RETURN l_trx_type;
44
45 END get_trx_type;
46
47 PROCEDURE calculate_ubb_amount(
48 p_api_version IN NUMBER
49 ,p_init_msg_list IN VARCHAR2
50 ,x_return_status OUT NOCOPY VARCHAR2
51 ,x_msg_count OUT NOCOPY NUMBER
52 ,x_msg_data OUT NOCOPY VARCHAR2
53 ) IS
54
55 l_hd_id NUMBER;
56 i NUMBER;
57 l_found BOOLEAN;
58 l_api_version CONSTANT NUMBER := 1;
59 l_api_name CONSTANT VARCHAR2(30) := 'calculate_ubb_amount';
60 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
61 l_overall_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
62 l_init_msg_list VARCHAR2(1) ;
63 l_msg_count NUMBER ;
64
65 i NUMBER := 0;
66 l_oks_khr_id okc_k_rel_objs.object1_id1%type;
67 l_okl_khr_id okc_k_rel_objs.chr_id%type;
68 l_cle_id okc_k_lines_v.cle_id%type;
69 l_btn_id OKS_BILL_CONT_LINES_V.BTN_ID%type;
70 l_date_billed_from OKS_BILL_CONT_LINES_V.DATE_BILLED_FROM%TYPE;
71 l_date_billed_to OKS_BILL_CONT_LINES_V.DATE_BILLED_TO%TYPE;
72 l_amount OKS_BILL_CONT_LINES_V.AMOUNT%TYPE;
73 l_currency_code OKS_BILL_CONT_LINES_V.CURRENCY_CODE%TYPE;
74 l_cont_id OKS_BILL_CONT_LINES_V.ID%TYPE;
75 l_try_id okl_trx_types_tl.id%TYPE;
76 l_clg_id okl_trx_ar_invoices_v.clg_id%TYPE;
77 l_sty_id okl_strm_type_v.id%TYPE;
78 l_first_line CONSTANT NUMBER := 1;
79 l_line_step CONSTANT NUMBER := 1;
80 l_detail_number okl_txd_ar_ln_dtls_v.line_detail_number%TYPE;
81 l_fin_asset_id NUMBER;
82 l_cov_asset_id NUMBER;
83 l_taiv_rec taiv_rec_type;
84 lx_taiv_rec taiv_rec_type;
85 l_tilv_rec tilv_rec_type;
86 lx_tilv_rec tilv_rec_type;
87 l_tldv_rec tldv_rec_type;
88 lx_tldv_rec tldv_rec_type;
89 ------------------------------------------------------------
90 -- Declare variables to call Billing Engine.
91 ------------------------------------------------------------
92 l_tilv_tbl okl_til_pvt.tilv_tbl_type;
93 l_tldv_tbl okl_tld_pvt.tldv_tbl_type;
94 x_taiv_rec okl_tai_pvt.taiv_rec_type;
95 x_tilv_tbl okl_til_pvt.tilv_tbl_type;
96 x_tldv_tbl okl_tld_pvt.tldv_tbl_type;
97 ------------------------------------------------------------
98 -- Declare variables to call Accounting Engine.
99 ------------------------------------------------------------
100 p_bpd_acc_rec Okl_Acc_Call_Pub.bpd_acc_rec_type;
101 l_def_desc CONSTANT VARCHAR2(30) := 'OKS Usage';
102
103 line_validation_failed exception;
104 l_msg_index_out NUMBER;
105
106
107 CURSOR l_fin_asset_id_cur(c_khr_id in NUMBER, c_kle_id in NUMBER) IS
108 select cle_inst.cle_id financial_asset_id
109 from
110 okc_k_lines_b cle_inst,
111 okc_k_lines_b cle_ib,
112 okc_k_items cim_ib,
113 csi_item_instances cii,
114 cs_csi_counter_groups ccg,
115 csi_counters_vl cc,
116 okc_k_items cim,
117 okc_k_lines_b cleb,
118 okc_line_styles_b lseb,
119 okc_k_headers_b chrb
120 where cle_ib.id = cim_ib.cle_id
121 and cle_ib.dnz_chr_id = cim_ib.dnz_chr_id
122 --
123 and cle_inst.id = cle_ib.cle_id
124 and cle_inst.dnz_chr_id = cle_ib.dnz_chr_id
125 --
126 and cim_ib.object1_id1 = to_char(cii.instance_id)
127 and cim_ib.object1_id2 = '#'
128 and cim_ib.jtot_object1_code = 'OKX_IB_ITEM'
129 --
130 and cii.instance_id = ccg.source_object_id
131 and ccg.counter_group_id = cc.group_id
132 and cc.counter_id = cim.object1_id1
133 and cim.object1_id2 = '#'
134 and cim.jtot_object1_code = 'OKX_COUNTER'
135 and cim.cle_id = cleb.id
136 and cim.dnz_chr_id = cleb.dnz_chr_id
137 and cleb.dnz_chr_id = chrb.id
138 and lseb.id = cleb.lse_id
139 and lseb.lty_code = 'INST_CTR'
140 and chrb.id = c_khr_id
141 and cleb.id = c_kle_id;
142
143 CURSOR l_khr_cur IS
144 select distinct rel.object1_id1 oks_khr_id, rel.chr_id okl_khr_id, cov_asset.id cov_asset_id, lns.id cle_id,
145 oks_cont.id oks_line_id, oks_cont.btn_id BTN_ID, oks_cont.amount LINE_AMOUNT,
146 oks_cont.CURRENCY_CODE, oks_cont.CLE_ID OKS_CLE_ID,
147 oks_lns.bcl_id BCL_ID, OKS_LNS.DATE_BILLED_FROM DATE_BILLED_FROM,
148 OKS_LNS.DATE_BILLED_TO DATE_BILLED_TO, CNTR.CLG_ID,
149 OKS_LNS.AMOUNT ASSET_AMOUNT, OKS_LNS.ID OKS_DETAIL_ID,
150 chr.contract_number contract_number
151 from okc_k_rel_objs rel, okc_k_lines_v lns, oks_bill_cont_lines_v oks_cont,
152 OKS_BILL_SUB_LINES_V OKS_LNS, OKC_K_HEADERS_B chr,
153 OKC_K_ITEMS ITEMS, OKL_CNTR_LVLNG_LNS_V CNTR, okc_k_lines_v cov_asset
154 where rel.rty_code = 'OKLUBB'
155 and lns.chr_id = rel.object1_id1
156 and lns.id = cov_asset.cle_id
157 and lns.id = oks_cont.cle_id
158 and OKS_LNS.BCL_ID = oks_cont.ID
159 AND cov_asset.id = oks_lns.cle_id -- Fix for bug 4659666
160 AND ITEMS.CLE_ID = OKS_LNS.CLE_ID
161 AND ITEMS.OBJECT1_ID1 = CNTR.KLE_ID(+)
162 AND OKS_LNS.amount > 0
163 AND rel.chr_id = chr.id
164 AND OKS_LNS.DATE_BILLED_FROM > (select NVL(max(tai.date_invoiced),add_months(sysdate,-1000))
165 from okl_trx_ar_invoices_v tai where tai.khr_id = rel.chr_id
166 and tai.description = 'OKS Usage')
167 AND not exists(select 'x' from okl_trx_ar_invoices_v tai, OKL_CNTR_LVLNG_LNS_V CNTR
168 where tai.khr_id = rel.chr_id
169 and CNTR.clg_id = tai.clg_id);
170
171
172
173 CURSOR l_try_id_cur IS
174 SELECT ID FROM okl_trx_types_tl WHERE NAME = 'Billing' and LANGUAGE = 'US';
175 /*
176 CURSOR l_sty_id_cur IS
177 SELECT ID FROM okl_strm_type_v WHERE NAME = 'USAGE CHARGE';
178 */
179 BEGIN
180 IF (G_DEBUG_ENABLED = 'Y') THEN
181 G_IS_DEBUG_STATEMENT_ON := OKL_DEBUG_PUB.CHECK_LOG_ON(G_MODULE, FND_LOG.LEVEL_STATEMENT);
182 END IF;
183
184 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
185 G_PKG_NAME,
186 p_init_msg_list,
187 l_api_version,
188 p_api_version,
189 '_PVT',
190 x_return_status);
191
192
193 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
194 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'Error in Starting Activity => '||l_return_status);
195 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
196 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
197 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'Error in Starting Activity => '||l_return_status);
198 RAISE OKL_API.G_EXCEPTION_ERROR;
199 END IF;
200
201 l_init_msg_list := p_init_msg_list ;
202 l_return_status := x_return_status ;
203 l_msg_count := x_msg_count ;
204 l_msg_data := x_msg_data ;
205
206
207 FOR l_okl_khr_cur IN l_khr_cur
208 LOOP
209 BEGIN
210 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'Processing: OKS Contract ID => '||l_okl_khr_cur.oks_khr_id||
211 ' ,OKL Contract ID=> '||l_okl_khr_cur.contract_number
212 ||' ,Contract Line Id=> '||l_okl_khr_cur.cle_id);
213 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
214 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Processing: OKS Contract ID => '||l_okl_khr_cur.oks_khr_id||
215 ' ,OKL Contract ID=> '||l_okl_khr_cur.contract_number
216 ||' ,Contract Line Id=> '||l_okl_khr_cur.cle_id);
217 END IF;
218
219
220 l_oks_khr_id := l_okl_khr_cur.oks_khr_id;
221 l_okl_khr_id := l_okl_khr_cur.okl_khr_id;
222 l_cle_id := l_okl_khr_cur.cle_id;
223 l_cov_asset_id := l_okl_khr_cur.cov_asset_id;
224
225 OPEN l_try_id_cur;
226 FETCH l_try_id_cur INTO l_try_id;
227 CLOSE l_try_id_cur;
228
229 OPEN l_fin_asset_id_cur(l_oks_khr_id, l_cov_asset_id);
230 FETCH l_fin_asset_id_cur INTO l_fin_asset_id;
231 CLOSE l_fin_asset_id_cur;
232
233 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'Transaction Type => '||l_try_id);
234
235 l_taiv_rec.trx_status_code := 'SUBMITTED';
236 l_taiv_rec.sfwt_flag := 'Y';
237 l_taiv_rec.khr_id := l_okl_khr_id;
238 l_taiv_rec.try_id := l_try_id;
239 l_taiv_rec.date_invoiced := l_okl_khr_cur.DATE_BILLED_TO; -- Bug 5077458
240 l_taiv_rec.date_entered := sysdate;
241 l_taiv_rec.amount := 0;
242 l_taiv_rec.description := l_def_desc;
243 l_taiv_rec.CLG_ID := l_okl_khr_cur.CLG_ID;
244
245 --20-jun-07 ansethur added for R12B Billing Architecture project
246 l_taiv_rec.OKL_SOURCE_BILLING_TRX := 'UBB';
247 --20-jun-07 ansethur added for R12B Billing Architecture project
248
249
250 -- Start of wraper code generated automatically by Debug code generator for Okl_Trx_Ar_Invoices_Pub.insert_trx_ar_invoices
251 /* IF(L_DEBUG_ENABLED='Y') THEN
252 L_LEVEL_PROCEDURE :=FND_LOG.LEVEL_PROCEDURE;
253 IS_DEBUG_PROCEDURE_ON := OKL_DEBUG_PUB.Check_Log_On(L_MODULE, L_LEVEL_PROCEDURE);
254 END IF;
255 IF(IS_DEBUG_PROCEDURE_ON) THEN
256 BEGIN
257 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRUBBB.pls call Okl_Trx_Ar_Invoices_Pub.insert_trx_ar_invoices ');
258 END;
259 END IF;
260 Okl_Trx_Ar_Invoices_Pub.insert_trx_ar_invoices(
261 l_api_version
262 ,l_init_msg_list
263 ,l_return_status
264 ,l_msg_count
265 ,l_msg_data
266 ,l_taiv_rec
267 ,lx_taiv_rec);
268 IF(IS_DEBUG_PROCEDURE_ON) THEN
269 BEGIN
270 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRUBBB.pls call Okl_Trx_Ar_Invoices_Pub.insert_trx_ar_invoices ');
271 END;
272 END IF;
273 -- End of wraper code generated automatically by Debug code generator for Okl_Trx_Ar_Invoices_Pub.insert_trx_ar_invoices
274
275 IF (l_return_status = 'S' ) THEN
276 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' ... Internal TXN Header Created.');
277 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
278 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,' ... Internal TXN Header Created.');
279 END IF;
280 END IF;
281
282 IF ( l_return_status = Fnd_Api.G_RET_STS_ERROR ) THEN
283 RAISE ubb_failed;
284 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'Error in creating Transaction => '||l_msg_data);
285 ELSIF (l_return_status = Fnd_Api.G_RET_STS_UNEXP_ERROR ) THEN
286 RAISE ubb_failed;
287 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'Error in creating Transaction => '||l_msg_data);
288 END IF;*/
289 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
290 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Inside Lines Cursor.');
291 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Processing: BTN ID => '||l_okl_khr_cur.BTN_ID
292 ||' ,date billed from=> '||l_okl_khr_cur.DATE_BILLED_FROM
293 ||' ,date billed to=> '||l_okl_khr_cur.DATE_BILLED_TO
294 ||' ,Cont ID=> '||l_okl_khr_cur.oks_line_id
295 ||' ,Currency Code=> '||l_okl_khr_cur.CURRENCY_CODE
296 ||' ,Amount=> '||l_okl_khr_cur.LINE_AMOUNT);
297 END IF;
298 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'Processing: BTN ID => '||l_okl_khr_cur.BTN_ID
299 ||' ,date billed from=> '||l_okl_khr_cur.DATE_BILLED_FROM
300 ||' ,date billed to=> '||l_okl_khr_cur.DATE_BILLED_TO
301 ||' ,Cont ID=> '||l_okl_khr_cur.oks_line_id
302 ||' ,Currency Code=> '||l_okl_khr_cur.CURRENCY_CODE
303 ||' ,Amount=> '||l_okl_khr_cur.LINE_AMOUNT);
304
305 l_btn_id := l_okl_khr_cur.BTN_ID;
306 l_date_billed_from := l_okl_khr_cur.DATE_BILLED_FROM;
307 l_date_billed_to := l_okl_khr_cur.DATE_BILLED_TO;
308 l_amount := l_okl_khr_cur.LINE_AMOUNT;
309 l_currency_code := l_okl_khr_cur.CURRENCY_CODE;
310 l_cont_id := l_okl_khr_cur.oks_line_id;
311
312 l_tilv_rec.sfwt_flag := 'Y';
313 l_tilv_rec.amount := l_okl_khr_cur.LINE_AMOUNT;
314 -- l_tilv_rec.tai_id := lx_taiv_rec.id;
315 l_tilv_rec.INV_RECEIV_LINE_CODE := 'LINE';
316 l_tilv_rec.LINE_NUMBER := 1;
317 l_tilv_rec.KLE_ID := l_fin_asset_id;
318 --20-jun-07 ansethur added for R12B Billing Architecture project
319 l_tilv_rec.TXL_AR_LINE_NUMBER :=1;
320 --20-jun-07 ansethur added for R12B Billing Architecture project
321 /*-- Start of wraper code generated automatically by Debug code generator for okl_txl_ar_inv_lns_pub.insert_txl_ar_inv_lns
322 IF(IS_DEBUG_PROCEDURE_ON) THEN
323 BEGIN
324 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRUBBB.pls call okl_txl_ar_inv_lns_pub.insert_txl_ar_inv_lns ');
325 END;
326 END IF;
327 okl_txl_ar_inv_lns_pub.insert_txl_ar_inv_lns(
328 l_api_version
329 ,l_init_msg_list
330 ,l_return_status
331 ,l_msg_count
332 ,l_msg_data
333 ,l_tilv_rec
334 ,lx_tilv_rec);
335 IF(IS_DEBUG_PROCEDURE_ON) THEN
336 BEGIN
337 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRUBBB.pls call okl_txl_ar_inv_lns_pub.insert_txl_ar_inv_lns ');
338 END;
339 END IF;
340 -- End of wraper code generated automatically by Debug code generator for okl_txl_ar_inv_lns_pub.insert_txl_ar_inv_lns
341
342 IF (l_return_status = 'S' ) THEN
343 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' ... Internal TXN Lines Created.');
344 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
345 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,' ... Internal TXN Lines Created.');
346 END IF;
347 END IF;
348
349 IF ( l_return_status = Fnd_Api.G_RET_STS_ERROR ) THEN
350 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'Error in creating Transaction Lines => '||l_msg_data);
351 RAISE ubb_failed;
352 ELSIF (l_return_status = Fnd_Api.G_RET_STS_UNEXP_ERROR ) THEN
353 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'Error in creating Transaction Lines=> '||l_msg_data);
354 RAISE ubb_failed;
355 END IF;*/
356
357 Okl_Streams_Util.get_primary_stream_type(
358 p_khr_id => l_okl_khr_id,
359 p_primary_sty_purpose => 'USAGE_PAYMENT',
360 x_return_status => l_return_status,
361 x_primary_sty_id => l_sty_id );
362
363 IF (l_return_status = 'S' ) THEN
364 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -- Stream Id for purpose USAGE_PAYMENT retrieved.');
365 ELSE
366 FND_FILE.PUT_LINE (FND_FILE.LOG, ' -- ERROR: Could not retrieve Stream Id for purpose USAGE_PAYMENT.');
367 END IF;
368
369 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
370 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
371 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
372 RAISE Okl_Api.G_EXCEPTION_ERROR;
373 END IF;
374
375 /*
376 OPEN l_sty_id_cur;
377 FETCH l_sty_id_cur INTO l_sty_id;
378 CLOSE l_sty_id_cur;
379 */
380 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'Stream Type => '||l_sty_id);
381
382 l_detail_number := l_first_line;
383
384 l_tldv_rec.sty_id := l_sty_id;
385 l_tldv_rec.sfwt_flag := 'Y';
386 l_tldv_rec.amount := l_okl_khr_cur.ASSET_AMOUNT;
387 -- l_tldv_rec.til_id_details := lx_tilv_rec.id;
388 l_tldv_rec.BSL_ID := l_okl_khr_cur.OKS_DETAIL_ID;
389 l_tldv_rec.BCL_ID := l_cont_id;
390 l_tldv_rec.line_detail_number := l_detail_number;
391 --20-jun-07 ansethur added for R12B Billing Architecture project
392 l_tldv_rec.TXL_AR_LINE_NUMBER := 1;
393 --20-jun-07 ansethur added for R12B Billing Architecture project
394 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'Line Detail Number => '||l_tldv_rec.line_detail_number);
395
396 /*-- Start of wraper code generated automatically by Debug code generator for Okl_Txd_Ar_Ln_Dtls_Pub.insert_txd_ar_ln_dtls
397 IF(IS_DEBUG_PROCEDURE_ON) THEN
398 BEGIN
399 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRUBBB.pls call Okl_Txd_Ar_Ln_Dtls_Pub.insert_txd_ar_ln_dtls ');
400 END;
401 END IF;
402 Okl_Txd_Ar_Ln_Dtls_Pub.insert_txd_ar_ln_dtls(
403 l_api_version
404 ,l_init_msg_list
405 ,l_return_status
406 ,l_msg_count
407 ,l_msg_data
408 ,l_tldv_rec
409 ,lx_tldv_rec);
410 l_detail_number := l_detail_number + l_line_step;
411
412 IF(IS_DEBUG_PROCEDURE_ON) THEN
413 BEGIN
414 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRUBBB.pls call Okl_Txd_Ar_Ln_Dtls_Pub.insert_txd_ar_ln_dtls ');
415 END;
416 END IF;
417 -- End of wraper code generated automatically by Debug code generator for Okl_Txd_Ar_Ln_Dtls_Pub.insert_txd_ar_ln_dtls
418
419 IF (l_return_status = 'S' ) THEN
420 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' ... Details Created.');
421 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
422 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,' ... Details Created.');
423 END IF;
424 END IF;*/
425 ---------------------------------------------------------------------------
426 -- Call to Billing Centralized API
427 ---------------------------------------------------------------------------
428 --Initialize The Table
429 l_tilv_tbl(1) := l_tilv_rec;
430 l_tldv_tbl(1) := l_tldv_rec;
431 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
432 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'++Begin : Call to Billing Centralized API');
433 END IF;
434 okl_internal_billing_pvt.create_billing_trx(p_api_version,
435 p_init_msg_list,
436 x_return_status,
437 x_msg_count,
438 x_msg_data,
439 l_taiv_rec,
440 l_tilv_tbl,
441 l_tldv_tbl,
442 x_taiv_rec,
443 x_tilv_tbl,
444 x_tldv_tbl);
445 IF x_return_status <> 'S' THEN
446 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
447 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,' -- ERROR: Creating Billing Transactions using Billing Engine');
448 END IF;
449 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
450 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
451 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
452 RAISE Okl_Api.G_EXCEPTION_ERROR;
453 END IF;
454 END IF;
455 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
456 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'--End : Call to Billing Centralized API');
457 END IF;
458
459 -- p_bpd_acc_rec.id := lx_tldv_rec.id;
460 -- p_bpd_acc_rec.source_table := 'OKL_TXD_AR_LN_DTLS_B';
461 /* ----------------------------------------------------
462 -- Create Accounting Distributions
463 ----------------------------------------------------
464 -- Start of wraper code generated automatically by Debug code generator for Okl_Acc_Call_Pub.CREATE_ACC_TRANS
465 IF(IS_DEBUG_PROCEDURE_ON) THEN
466 BEGIN
467 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRUBBB.pls call Okl_Acc_Call_Pub.CREATE_ACC_TRANS ');
468 END;
469 END IF;
470 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
471 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,' ... Acc Call Initiated.');
472 END IF;
473 Okl_Acc_Call_Pub.CREATE_ACC_TRANS(
474 p_api_version
475 ,p_init_msg_list
476 ,x_return_status
477 ,x_msg_count
478 ,x_msg_data
479 ,p_bpd_acc_rec
480 );
481 IF(IS_DEBUG_PROCEDURE_ON) THEN
482 BEGIN
483 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRUBBB.pls call Okl_Acc_Call_Pub.CREATE_ACC_TRANS ');
484 END;
485 END IF;
486 -- End of wraper code generated automatically by Debug code generator for Okl_Acc_Call_Pub.CREATE_ACC_TRANS
487
488 IF (x_return_status = 'S' ) THEN
489 commit;
490 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' ... ACCOUNTING Created.');
491 ELSE
492 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
493 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,' ... Accounting Failed.');
494 END IF;
495 END IF;
496 FOR i in 1..x_msg_count
497 LOOP
498 FND_MSG_PUB.GET(
499 p_msg_index => i,
500 p_encoded => FND_API.G_FALSE,
501 p_data => x_msg_data,
502 p_msg_index_out => l_msg_index_out
503 );
504 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,'Error '||to_char(i)||': '||x_msg_data);
505 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,'Message Index: '||l_msg_index_out);
506 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
507 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Error '||to_char(i)||': '||x_msg_data);
508 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Message Index: '||l_msg_index_out);
509 END IF;
510 END LOOP;
511
512 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
513 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'Error in Accounting => '||x_msg_data);
514 RAISE ubb_failed;
515 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
516 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'Error in Accounting => '||x_msg_data);
517 RAISE ubb_failed;
518 END IF;*/
519 EXCEPTION
520 WHEN ubb_failed THEN
521 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'Error In UBB, Processing Next Record ');
522 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'Error (EXCP) => '||SQLERRM);
523 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'Rolling Back Transaction');
524 ROLLBACK;
525 -- DBMS_TRANSACTION.ROLLBACK_SAVEPOINT(l_api_name || '_PVT');
526 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'Error '||x_msg_data);
527 END;
528
529 END LOOP;
530 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
531
532 EXCEPTION
533 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
534 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'Error (EXCP) => '||SQLERRM);
535 x_return_status := Okl_Api.HANDLE_EXCEPTIONS
536 (
537 l_api_name,
538 G_PKG_NAME,
539 'Okl_Api.G_RET_STS_ERROR',
540 x_msg_count,
541 x_msg_data,
542 '_PVT'
543 );
544 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
545 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'Error (UNEXCP) => '||SQLERRM);
546 x_return_status :=Okl_Api.HANDLE_EXCEPTIONS
547 (
548 l_api_name,
549 G_PKG_NAME,
550 'Okl_Api.G_RET_STS_UNEXP_ERROR',
551 x_msg_count,
552 x_msg_data,
553 '_PVT'
554 );
555 WHEN OTHERS THEN
556 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'Error (Others) => '||SQLERRM);
557 x_return_status :=Okl_Api.HANDLE_EXCEPTIONS
558 (
559 l_api_name,
560 G_PKG_NAME,
561 'OTHERS',
562 x_msg_count,
563 x_msg_data,
564 '_PVT'
565 );
566 END calculate_ubb_amount;
567
568 PROCEDURE billing_status(
569 p_api_version IN NUMBER
570 ,p_init_msg_list IN VARCHAR2
571 ,x_return_status OUT NOCOPY VARCHAR2
572 ,x_msg_count OUT NOCOPY NUMBER
573 ,x_msg_data OUT NOCOPY VARCHAR2
574 ,x_bill_stat_tbl OUT NOCOPY bill_stat_tbl_type
575 ,p_khr_id IN NUMBER
576 ,p_transaction_date IN DATE
577 ) IS
578
579 CURSOR c_last_bill_date(c_khr_id in NUMBER, c_transaction_date in DATE) IS
580 select max(AR.DUE_DATE) last_bill_date, tai.description transaction_type
581 from okl_cnsld_ar_strms_b cnsld,
582 AR_PAYMENT_SCHEDULES_ALL AR,
583 OKL_XTL_SELL_INVS_V XTL,
584 okl_trx_ar_invoices_v tai,
585 okl_txl_ar_inv_lns_v til,
586 okl_txd_ar_ln_dtls_v tld
587 where cnsld.receivables_invoice_id = AR.customer_trx_id
588 and cnsld.khr_id = c_khr_id
589 and cnsld.id = XTL.lsm_id
590 and xtl.tld_id = tld.id
591 and til.tai_id = tai.id
592 and til.id = tld.til_id_details
593 and tai.description in ('Regular Stream Billing')
594 and cnsld.sel_id in (SELECT SEL.id
595 FROM OKL_STREAMS_V STM,
596 OKL_STRM_ELEMENTS_V SEL,
597 OKC_K_HEADERS_V KHR,
598 OKL_STRM_TYPE_V STY
599 WHERE KHR.id = c_khr_id
600 AND SEL.stream_element_date <= c_transaction_date
601 AND KHR.id = STM.khr_id
602 AND STM.id = SEL.stm_id
603 AND STM.say_code = 'CURR'
604 AND STM.active_yn = 'Y'
605 AND STM.sty_id = STY.id
606 AND NVL(STY.billable_yn,'N') = 'Y'
607 AND STY.stream_type_purpose = 'RENT'
608 AND SEL.amount > 0)
609 group by tai.description;
610
611 /*
612 CURSOR c_last_sch_bill_date(c_khr_id in NUMBER, c_transaction_date DATE) IS
613 SELECT max(SEL.stream_element_date) last_sche_bill_date, sel.id
614 FROM OKL_STREAMS_V STM,
615 OKL_STRM_ELEMENTS_V SEL,
616 OKC_K_HEADERS_V KHR,
617 OKL_STRM_TYPE_B STY
618 WHERE KHR.id = c_khr_id
619 AND SEL.stream_element_date <= c_transaction_date
620 AND KHR.id = STM.khr_id
621 AND STM.id = SEL.stm_id
622 AND STM.say_code = 'CURR'
623 AND STM.active_yn = 'Y'
624 AND SEL.date_billed IS NULL
625 AND STM.sty_id = STY.id
626 AND NVL(STY.billable_yn,'N') = 'Y'
627 AND SEL.amount > 0
628 AND ROWNUM < 2;
629 */
630
631 CURSOR c_last_sch_bill_date(c_khr_id in NUMBER, c_transaction_date DATE) IS
632 SELECT sel.id stream_id,
633 sel.stream_element_date last_sche_bill_date
634 FROM OKL_STREAMS_V STM,
635 OKL_STRM_ELEMENTS_V SEL,
636 OKL_STRM_TYPE_V STY
637 WHERE sel.stream_element_date = (SELECT max(SEL.stream_element_date) last_sche_bill_date
638 FROM OKL_STREAMS_V STM,
639 OKL_STRM_ELEMENTS_V SEL,
640 OKC_K_HEADERS_V KHR,
641 OKL_STRM_TYPE_V STY
642 WHERE KHR.id = c_khr_id
643 AND SEL.stream_element_date <= c_transaction_date
644 AND KHR.id = STM.khr_id
645 AND STM.id = SEL.stm_id
646 AND STM.say_code = 'CURR'
647 AND STM.active_yn = 'Y'
648 AND STM.sty_id = STY.id
649 AND NVL(STY.billable_yn,'N') = 'Y'
650 AND STY.stream_type_purpose = 'RENT'
651 AND SEL.amount > 0)
652 AND STM.id = SEL.stm_id
653 AND STM.sty_id = STY.id
654 AND STY.stream_type_purpose = 'RENT'
655 AND STM.khr_id = c_khr_id
656 AND STM.say_code = 'CURR'
657 AND STM.active_yn = 'Y'
658 AND NVL(STY.billable_yn,'N') = 'Y'
659 AND ROWNUM < 2;
660
661
662 CURSOR c_oks_last_sch_bill_date_10(c_khr_id in NUMBER, c_transaction_date DATE) IS
663 select max(schd.date_to_interface) last_sche_bill_date
664 from okc_k_rel_objs rel,
665 okc_k_headers_b hdr,
666 okc_k_headers_b oks,
667 okc_k_lines_b oks_line,
668 OKS_LEVEL_ELEMENTS_V schd, OKS_STREAM_LEVELS_B strm
669 where hdr.id = c_khr_id
670 and rty_code = 'OKLSRV'
671 and rel.jtot_object1_code = 'OKL_SERVICE'
672 and rel.cle_id is null
673 and rel.chr_id = hdr.id
674 and rel.object1_id1 = to_char(oks.id)
675 and oks.id = oks_line.dnz_chr_id
676 and oks_line.lse_id in (7,8,9,10,11,35)
677 and oks_line.id = strm.cle_id
678 and strm.id = schd.rul_id
679 and schd.date_to_interface <= c_transaction_date;
680
681 CURSOR c_oks_last_sch_bill_date_9(c_khr_id in NUMBER, c_transaction_date DATE) IS
682 select max(schd.date_to_interface) last_sche_bill_date
683 from okc_k_rel_objs rel,
684 okc_k_headers_b hdr,
685 okc_k_headers_b oks,
686 okc_k_lines_b oks_line,
687 OKS_LEVEL_ELEMENTS_V schd,
688 okc_rules_b rules,
689 okc_rule_groups_b rgp
690 where hdr.id = c_khr_id
691 and rty_code = 'OKLSRV'
692 and rel.jtot_object1_code = 'OKL_SERVICE'
693 and rel.cle_id is null
694 and rel.chr_id = hdr.id
695 and rel.object1_id1 = to_char(oks.id)
696 and oks.id = oks_line.dnz_chr_id
697 and oks_line.lse_id in (7,8,9,10,11,35)
698 and oks_line.id = rgp.cle_id
699 and rules.rgp_id = rgp.id
700 and rules.id = schd.rul_id
701 and rules.rule_information_category = 'SLL'
702 and schd.date_to_interface <= c_transaction_date;
703
704
705 CURSOR check_oks_ver IS
706 SELECT 1
707 FROM okc_class_operations
708 WHERE cls_code = 'SERVICE'
709 AND opn_code = 'CHECK_RULE';
710
711 l_api_version CONSTANT NUMBER := 1;
712 l_api_name CONSTANT VARCHAR2(30) := 'billing_status';
713 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
714 l_stream_id NUMBER;
715
716 -- l_khr_id NUMBER;
717 i NUMBER;
718 l_bill_stat_rec bill_stat_rec_type;
719 l_bill_stat_tbl bill_stat_tbl_type;
720 l_oks_ver VARCHAR2(10);
721 BEGIN
722
723 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
724 G_PKG_NAME,
725 p_init_msg_list,
726 l_api_version,
727 p_api_version,
728 '_PVT',
729 x_return_status);
730
731
732 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
733 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
734 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
735 RAISE OKL_API.G_EXCEPTION_ERROR;
736 END IF;
737
738 -- select id into l_khr_id from okc_k_headers_b where contract_number = p_contract_number;
739
740 i := 0;
741
742 FOR l_last_sch_bill_date IN c_last_sch_bill_date(p_khr_id, p_transaction_date)
743 LOOP
744 l_bill_stat_rec.last_schedule_bill_date := l_last_sch_bill_date.last_sche_bill_date;
745 l_stream_id := l_last_sch_bill_date.stream_id;
746 l_bill_stat_rec.transaction_type := 'RENTAL';
747 -- dbms_output.put_line('Stream :'||l_stream_id);
748 -- dbms_output.put_line('last_schedule_bill_date :'||l_bill_stat_rec.last_schedule_bill_date);
749
750 FOR l_last_bill_date IN c_last_bill_date(p_khr_id, p_transaction_date)
751 LOOP
752
753 l_bill_stat_rec.last_bill_date := l_last_bill_date.last_bill_date;
754 -- l_bill_stat_rec.transaction_type := l_last_bill_date.transaction_type;
755 -- dbms_output.put_line('last_bill_date :'||l_bill_stat_rec.last_bill_date);
756
757 END LOOP;
758
759 l_bill_stat_tbl(i) := l_bill_stat_rec;
760
761 i := i + 1;
762 END LOOP;
763
764
765 l_oks_ver := '?';
766 OPEN check_oks_ver;
767 FETCH check_oks_ver INTO l_oks_ver;
768
769 IF check_oks_ver%NOTFOUND THEN
770 l_oks_ver := '9';
771 ELSE
772 l_oks_ver := '10';
773 END IF;
774
775 CLOSE check_oks_ver;
776
777
778 IF (l_oks_ver = '10') THEN
779 FOR l_oks_last_sch_bill_date IN c_oks_last_sch_bill_date_10(p_khr_id, p_transaction_date)
780 LOOP
781 l_bill_stat_rec.last_schedule_bill_date := l_oks_last_sch_bill_date.last_sche_bill_date;
782 -- l_stream_id := l_last_sch_bill_date.stream_id;
783 l_bill_stat_rec.transaction_type := 'RENTAL';
784 -- dbms_output.put_line('Stream :'||l_stream_id);
785 -- dbms_output.put_line('last_schedule_bill_date :'||l_bill_stat_rec.last_schedule_bill_date);
786
787 FOR l_last_bill_date IN c_last_bill_date(p_khr_id, p_transaction_date)
788 LOOP
789
790 l_bill_stat_rec.last_bill_date := l_last_bill_date.last_bill_date;
791 -- l_bill_stat_rec.transaction_type := l_last_bill_date.transaction_type;
792 -- dbms_output.put_line('last_bill_date :'||l_bill_stat_rec.last_bill_date);
793
794 END LOOP;
795
796 l_bill_stat_tbl(i) := l_bill_stat_rec;
797
798 i := i + 1;
799 END LOOP;
800 ELSE -- oks_ver = 9
801 FOR l_oks_last_sch_bill_date IN c_oks_last_sch_bill_date_9(p_khr_id, p_transaction_date)
802 LOOP
803 l_bill_stat_rec.last_schedule_bill_date := l_oks_last_sch_bill_date.last_sche_bill_date;
804 -- l_stream_id := l_last_sch_bill_date.stream_id;
805 l_bill_stat_rec.transaction_type := 'RENTAL';
806 -- dbms_output.put_line('Stream :'||l_stream_id);
807 -- dbms_output.put_line('last_schedule_bill_date :'||l_bill_stat_rec.last_schedule_bill_date);
808
809 FOR l_last_bill_date IN c_last_bill_date(p_khr_id, p_transaction_date)
810 LOOP
811
812 l_bill_stat_rec.last_bill_date := l_last_bill_date.last_bill_date;
813 -- l_bill_stat_rec.transaction_type := l_last_bill_date.transaction_type;
814 -- dbms_output.put_line('last_bill_date :'||l_bill_stat_rec.last_bill_date);
815
816 END LOOP;
817
818 l_bill_stat_tbl(i) := l_bill_stat_rec;
819
820 i := i + 1;
821 END LOOP;
822 END IF;
823
824 x_bill_stat_tbl := l_bill_stat_tbl;
825 Okl_Api.END_ACTIVITY (
826 x_msg_count => x_msg_count,
827 x_msg_data => x_msg_data);
828
829 EXCEPTION
830 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
831 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'Error (EXCP) => '||SQLERRM);
832 x_return_status := Okl_Api.HANDLE_EXCEPTIONS
833 (
834 l_api_name,
835 G_PKG_NAME,
836 'Okl_Api.G_RET_STS_ERROR',
837 x_msg_count,
838 x_msg_data,
839 '_PVT'
840 );
841 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
842 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'Error (UNEXCP) => '||SQLERRM);
843 x_return_status :=Okl_Api.HANDLE_EXCEPTIONS
844 (
845 l_api_name,
846 G_PKG_NAME,
847 'Okl_Api.G_RET_STS_UNEXP_ERROR',
848 x_msg_count,
849 x_msg_data,
850 '_PVT'
851 );
852 WHEN OTHERS THEN
853 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'Error (Others) => '||SQLERRM);
854 x_return_status :=Okl_Api.HANDLE_EXCEPTIONS
855 (
856 l_api_name,
857 G_PKG_NAME,
858 'OTHERS',
859 x_msg_count,
860 x_msg_data,
861 '_PVT'
862 );
863 END billing_status;
864
865 PROCEDURE bill_service_contract(
866 p_api_version IN NUMBER
867 ,p_init_msg_list IN VARCHAR2
868 ,x_return_status OUT NOCOPY VARCHAR2
869 ,x_msg_count OUT NOCOPY NUMBER
870 ,x_msg_data OUT NOCOPY VARCHAR2
871 ,p_contract_number IN VARCHAR2
872 ) IS
873
874
875 ------------------------------------------------------------
876 -- Initialise constants
877 ------------------------------------------------------------
878
879 l_def_desc CONSTANT VARCHAR2(30) := 'OKS Billing';
880 l_line_code CONSTANT VARCHAR2(30) := 'LINE';
881 -- l_init_status CONSTANT VARCHAR2(30) := 'ENTERED';
882 l_final_status CONSTANT VARCHAR2(30) := 'SUBMITTED';
883 l_trx_type_name CONSTANT VARCHAR2(30) := 'Billing';
884 l_trx_type_lang CONSTANT VARCHAR2(30) := 'US';
885 l_date_entered CONSTANT DATE := SYSDATE;
886 l_zero_amount CONSTANT NUMBER := 0;
887 l_first_line CONSTANT NUMBER := 1;
888 l_line_step CONSTANT NUMBER := 1;
889 l_def_no_val CONSTANT NUMBER := -1;
890 l_null_kle_id CONSTANT NUMBER := -2;
891 l_sty_id okl_strm_type_v.id%TYPE;
892
893 ------------------------------------------------------------
894 -- Declare records: i - insert, u - update, r - result
895 ------------------------------------------------------------
896
897 -- Transaction headers
898 i_taiv_rec Okl_Trx_Ar_Invoices_Pub.taiv_rec_type;
899 u_taiv_rec Okl_Trx_Ar_Invoices_Pub.taiv_rec_type;
900 r_taiv_rec Okl_Trx_Ar_Invoices_Pub.taiv_rec_type;
901
902 -- Transaction lines
903 i_tilv_rec Okl_Txl_Ar_Inv_Lns_Pub.tilv_rec_type;
904 u_tilv_rec Okl_Txl_Ar_Inv_Lns_Pub.tilv_rec_type;
905 r_tilv_rec Okl_Txl_Ar_Inv_Lns_Pub.tilv_rec_type;
906
907 -- Transaction line details
908 i_tldv_rec Okl_Txd_Ar_Ln_Dtls_Pub.tldv_rec_type;
909 u_tldv_rec Okl_Txd_Ar_Ln_Dtls_Pub.tldv_rec_type;
910 l_init_tldv_rec Okl_Txd_Ar_Ln_Dtls_Pub.tldv_rec_type;
911 r_tldv_rec Okl_Txd_Ar_Ln_Dtls_Pub.tldv_rec_type;
912
913 -- Stream elements
914 u_selv_rec Okl_Streams_Pub.selv_rec_type;
915 l_init_selv_rec Okl_Streams_Pub.selv_rec_type;
916 r_selv_rec Okl_Streams_Pub.selv_rec_type;
917
918 ------------------------------------------------------------
919 -- Declare local variables used in the program
920 ------------------------------------------------------------
921
922 l_khr_id okl_trx_ar_invoices_v.khr_id%TYPE;
923 l_bill_date okl_trx_ar_invoices_v.date_invoiced%TYPE;
924 l_trx_type okl_trx_ar_invoices_v.try_id%TYPE;
925 l_kle_id okl_txl_ar_inv_lns_v.kle_id%TYPE;
926
927 l_curr_code okc_k_headers_b.currency_code%TYPE;
928 l_ste_amount okl_strm_elements.amount%type;
929
930
931 l_line_number okl_txl_ar_inv_lns_v.line_number%TYPE;
932 l_detail_number okl_txd_ar_ln_dtls_v.line_detail_number%TYPE;
933
934 l_header_amount okl_trx_ar_invoices_v.amount%TYPE;
935 l_line_amount okl_txl_ar_inv_lns_v.amount%TYPE;
936
937 l_header_id okl_trx_ar_invoices_v.id%TYPE;
938 l_line_id okl_txl_ar_inv_lns_v.id%TYPE;
939
940 ------------------------------------------------------------
941 -- Declare variables required by APIs
942 ------------------------------------------------------------
943
944 l_api_version CONSTANT NUMBER := 1;
945 l_api_name CONSTANT VARCHAR2(30) := 'bill_service_contract';
946 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
947
948 ------------------------------------------------------------
949 -- Declare variables to call Accounting Engine.
950 ------------------------------------------------------------
951 p_bpd_acc_rec Okl_Acc_Call_Pub.bpd_acc_rec_type;
952
953
954 ------------------------------------------------------------
955 -- Variables for Error Processing and Committing Stream Billing
956 -- Transactions
957 ------------------------------------------------------------
958
959 l_error_status VARCHAR2(1);
960 l_error_message VARCHAR2(2000);
961
962
963 ------------------------------------------------------------
964 -- Extract all OKS Covered Product Lines to be billed
965 ------------------------------------------------------------
966
967 CURSOR c_oks_bill(c_contract_number in VARCHAR2) IS
968 select chr.contract_number contract_number, hdr.id khr_id, lns.id kle_id,
969 rel.object1_id1 oks_line_id, OKS_LNS.DATE_BILLED_FROM DATE_BILLED_FROM,
970 OKS_LNS.DATE_BILLED_TO DATE_BILLED_TO, OKS_LNS.AMOUNT asset_amount,
971 OKS_CONT.AMOUNT line_amount, OKS_CONT.CURRENCY_CODE CURRENCY_CODE,
972 okll.sty_id sty_id
973 from okc_k_rel_objs rel, okl_k_headers hdr, okc_k_headers_b chr, okc_k_lines_b lns,
974 okc_line_styles_b lse, okc_k_lines_b lnsb, OKS_BILL_CONT_LINES_V OKS_CONT,
975 OKS_BILL_SUB_LINES_V OKS_LNS, okl_k_lines okll
976 where rty_code = 'OKLSRV'
977 and rel.jtot_object1_code = 'OKL_COV_PROD'
978 and rel.chr_id = hdr.id
979 and hdr.id = chr.id
980 and chr.contract_number = NVL(c_contract_number,chr.contract_number)
981 and lse.lty_code = 'SOLD_SERVICE'
982 and lns.lse_id = lse.id
983 and lns.id = lnsb.cle_id
984 and rel.cle_id = lnsb.id
985 and lns.id = okll.id
986 and OKS_LNS.CLE_ID = rel.object1_id1
987 and OKS_CONT.ID = OKS_LNS.BCL_ID
988 --and OKS_LNS.DATE_BILLED_TO <= sysdate
989 and OKS_LNS.DATE_BILLED_FROM > (select NVL(max(tai.date_invoiced),add_months(sysdate,-1000))
990 from okl_trx_ar_invoices_v tai where tai.khr_id = hdr.id
991 and tai.description = 'OKS Billing')
992 order by chr.contract_number, OKS_LNS.date_billed_from, lns.id;
993 /*
994 CURSOR l_sty_id_cur IS
995 SELECT ID FROM okl_strm_type_v WHERE NAME = 'SERVICE FEE';
996 */
997 BEGIN
998 IF (G_DEBUG_ENABLED = 'Y') THEN
999 G_IS_DEBUG_STATEMENT_ON := OKL_DEBUG_PUB.CHECK_LOG_ON(G_MODULE, FND_LOG.LEVEL_STATEMENT);
1000 END IF;
1001 ------------------------------------------------------------
1002 -- Start processing
1003 ------------------------------------------------------------
1004
1005 FND_FILE.PUT_LINE (FND_FILE.LOG, '=========================================================================================');
1006 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
1007
1008 l_return_status := Okl_Api.START_ACTIVITY(
1009 p_api_name => l_api_name,
1010 p_pkg_name => G_PKG_NAME,
1011 p_init_msg_list => p_init_msg_list,
1012 l_api_version => l_api_version,
1013 p_api_version => p_api_version,
1014 p_api_type => '_PVT',
1015 x_return_status => l_return_status);
1016
1017 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
1018 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1019 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
1020 RAISE Okl_Api.G_EXCEPTION_ERROR;
1021 END IF;
1022
1023 ------------------------------------------------------------
1024 -- Initialise local variables
1025 ------------------------------------------------------------
1026
1027 l_khr_id := l_def_no_val;
1028 l_kle_id := l_def_no_val;
1029 l_trx_type := get_trx_type (l_trx_type_name, l_trx_type_lang);
1030
1031 ------------------------------------------------------------
1032 -- Process every COVERED ASSET LINE to be billed
1033 ------------------------------------------------------------
1034
1035 FND_FILE.PUT_LINE (FND_FILE.LOG, '=========================================================================================');
1036 FND_FILE.PUT_LINE (FND_FILE.LOG, ' ** Start Processing. Please See Error Log for any errored transactions ** ');
1037 FND_FILE.PUT_LINE (FND_FILE.LOG, '=========================================================================================');
1038
1039
1040 FOR l_oks_bill_rec IN c_oks_bill(p_contract_number)
1041 LOOP
1042
1043 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' Processing: Contract Number=> '||l_oks_bill_rec.contract_number
1044 ||' ,for date=> '||l_oks_bill_rec.DATE_BILLED_FROM||' and Amount=> '||l_ste_amount);
1045 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1046 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Contract Number'||l_oks_bill_rec.contract_number);
1047 END IF;
1048
1049 l_ste_amount := l_oks_bill_rec.asset_amount;
1050
1051 FND_FILE.PUT_LINE (FND_FILE.LOG, '===============================================================================');
1052 FND_FILE.PUT_LINE (FND_FILE.LOG, ' Processing: Contract Number=> '||l_oks_bill_rec.contract_number
1053 ||' ,for date=> '||l_oks_bill_rec.DATE_BILLED_FROM||' and Amount=> '||l_ste_amount);
1054
1055 ----------------------------------------------------
1056 -- Create new transaction header for every
1057 -- contract and bill_date combination
1058 ----------------------------------------------------
1059
1060 IF l_khr_id <> l_oks_bill_rec.khr_id
1061 OR l_bill_date <> l_oks_bill_rec.DATE_BILLED_FROM THEN
1062
1063 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1064 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Contract ID '||l_oks_bill_rec.khr_id||'Date Billed '||l_oks_bill_rec.DATE_BILLED_FROM);
1065 END IF;
1066 ---------------------------------------------
1067 -- Save previous header amount except first record
1068 ---------------------------------------------
1069 IF l_khr_id <> l_def_no_val THEN
1070
1071
1072 u_taiv_rec.id := l_header_id;
1073 u_taiv_rec.amount := l_header_amount;
1074
1075 Okl_Trx_Ar_Invoices_Pub.update_trx_ar_invoices
1076 (p_api_version
1077 ,p_init_msg_list
1078 ,l_return_status
1079 ,x_msg_count
1080 ,x_msg_data
1081 ,u_taiv_rec
1082 ,r_taiv_rec);
1083
1084 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
1085 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1086 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
1087 RAISE Okl_Api.G_EXCEPTION_ERROR;
1088 END IF;
1089
1090 END IF;
1091
1092
1093 ---------------------------------------------
1094 -- Populate required columns
1095 ---------------------------------------------
1096 i_taiv_rec.khr_id := l_oks_bill_rec.khr_id;
1097 i_taiv_rec.date_invoiced := l_oks_bill_rec.DATE_BILLED_FROM;
1098 i_taiv_rec.try_id := l_trx_type;
1099 i_taiv_rec.date_entered := l_date_entered;
1100 i_taiv_rec.description := l_def_desc;
1101 i_taiv_rec.trx_status_code := l_final_status;
1102 i_taiv_rec.amount := l_zero_amount;
1103
1104 ---------------------------------------------
1105 -- Columns to be populated later based on CONTRACT_ID
1106 ---------------------------------------------
1107 i_taiv_rec.currency_code := NULL;
1108 i_taiv_rec.set_of_books_id := NULL;
1109 i_taiv_rec.ibt_id := NULL;
1110 i_taiv_rec.ixx_id := NULL;
1111 i_taiv_rec.irm_id := NULL;
1112 i_taiv_rec.irt_id := NULL;
1113 i_taiv_rec.org_id := NULL;
1114
1115
1116 ---------------------------------------------
1117 -- Insert transaction header record
1118 ---------------------------------------------
1119 Okl_Trx_Ar_Invoices_Pub.insert_trx_ar_invoices
1120 (p_api_version
1121 ,p_init_msg_list
1122 ,l_return_status
1123 ,x_msg_count
1124 ,x_msg_data
1125 ,i_taiv_rec
1126 ,r_taiv_rec);
1127
1128 IF (l_return_status = 'S' ) THEN
1129 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -- Internal TXN Header Created.');
1130 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1131 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,' -- Internal TXN Header Created.');
1132 END IF;
1133 ELSE
1134 FND_FILE.PUT_LINE (FND_FILE.LOG, ' -- ERROR: Creating Internal TXN Header.');
1135 END IF;
1136
1137 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
1138 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1139 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
1140 RAISE Okl_Api.G_EXCEPTION_ERROR;
1141 END IF;
1142
1143 ---------------------------------------------
1144 -- Adjust header variables
1145 ---------------------------------------------
1146 l_line_number := l_first_line;
1147 l_header_amount := l_zero_amount;
1148 l_header_id := r_taiv_rec.id;
1149
1150 END IF;
1151
1152
1153 ----------------------------------------------------
1154 -- Create new transaction line for every
1155 -- contract line and bill_date combination
1156 ----------------------------------------------------
1157
1158 IF l_kle_id <> NVL (l_oks_bill_rec.kle_id, l_null_kle_id)
1159 OR l_bill_date <> l_oks_bill_rec.DATE_BILLED_FROM THEN
1160
1161 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' Processing: Contract LINE=> '||l_oks_bill_rec.kle_id);
1162 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1163 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Contract Line'||l_oks_bill_rec.kle_id);
1164 END IF;
1165 ---------------------------------------------
1166 -- Save previous line amount except first record
1167 ---------------------------------------------
1168 IF l_kle_id <> l_def_no_val THEN
1169
1170 u_tilv_rec.id := l_line_id;
1171 u_tilv_rec.amount := l_line_amount;
1172
1173 Okl_Txl_Ar_Inv_Lns_Pub.update_txl_ar_inv_lns
1174 (p_api_version
1175 ,p_init_msg_list
1176 ,l_return_status
1177 ,x_msg_count
1178 ,x_msg_data
1179 ,u_tilv_rec
1180 ,r_tilv_rec);
1181
1182 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
1183 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1184 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
1185 RAISE Okl_Api.G_EXCEPTION_ERROR;
1186 END IF;
1187
1188 END IF;
1189
1190 ---------------------------------------------
1191 -- Populate required columns
1192 ---------------------------------------------
1193 i_tilv_rec.kle_id := l_oks_bill_rec.kle_id;
1194 i_tilv_rec.line_number := l_line_number;
1195 i_tilv_rec.tai_id := l_header_id;
1196 i_tilv_rec.description := l_def_desc;
1197 i_tilv_rec.inv_receiv_line_code := l_line_code;
1198 i_tilv_rec.amount := l_zero_amount;
1199 i_tilv_rec.date_bill_period_end := l_oks_bill_rec.DATE_BILLED_TO;
1200 i_tilv_rec.date_bill_period_start := l_oks_bill_rec.DATE_BILLED_FROM;
1201
1202 ---------------------------------------------
1203 -- Columns which are not used by stream billing
1204 ---------------------------------------------
1205 i_tilv_rec.til_id_reverses := NULL;
1206 i_tilv_rec.tpl_id := NULL;
1207 i_tilv_rec.acn_id_cost := NULL;
1208 i_tilv_rec.sty_id := NULL;
1209 i_tilv_rec.quantity := NULL;
1210 i_tilv_rec.amount_applied := NULL;
1211 i_tilv_rec.org_id := NULL;
1212 i_tilv_rec.receivables_invoice_id := NULL;
1213
1214 ---------------------------------------------
1215 -- Insert transaction line record
1216 ---------------------------------------------
1217 Okl_Txl_Ar_Inv_Lns_Pub.insert_txl_ar_inv_lns
1218 (p_api_version
1219 ,p_init_msg_list
1220 ,l_return_status
1221 ,x_msg_count
1222 ,x_msg_data
1223 ,i_tilv_rec
1224 ,r_tilv_rec);
1225
1226 IF (l_return_status = 'S' ) THEN
1227 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -- Internal TXN Line Created.');
1228 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1229 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,' -- Internal TXN Line Created.');
1230 END IF;
1231 ELSE
1232 FND_FILE.PUT_LINE (FND_FILE.LOG, ' -- ERROR: Creating Internal TXN Line.');
1233 END IF;
1234
1235
1236 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
1237 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1238 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
1239 RAISE Okl_Api.G_EXCEPTION_ERROR;
1240 END IF;
1241
1242 ---------------------------------------------
1243 -- Adjust line variables
1244 ---------------------------------------------
1245 l_detail_number := l_first_line;
1246 l_line_amount := l_zero_amount;
1247 l_line_id := r_tilv_rec.id;
1248 l_line_number := l_line_number + l_line_step;
1249
1250 END IF;
1251
1252 ----------------------------------------------------
1253 -- Create new transaction line detail for every stream
1254 ----------------------------------------------------
1255
1256 ----------------------------------------------------
1257 -- Populate required columns
1258 ----------------------------------------------------
1259 /*
1260 OPEN l_sty_id_cur;
1261 FETCH l_sty_id_cur INTO l_sty_id;
1262 CLOSE l_sty_id_cur;
1263 */
1264
1265 l_sty_id := l_oks_bill_rec.sty_id;
1266
1267 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'Stream Type => '||l_sty_id);
1268
1269 i_tldv_rec.sty_id := l_sty_id;
1270 i_tldv_rec.amount := l_oks_bill_rec.asset_amount;
1271 /* Find the values of these
1272 i_tldv_rec.description := c_streams_rec.comments;
1273 i_tldv_rec.sel_id := c_streams_rec.sel_id;
1274 Find the values of these */
1275 i_tldv_rec.til_id_details := l_line_id;
1276 i_tldv_rec.line_detail_number := l_detail_number;
1277
1278 ----------------------------------------------------
1279 -- Columns which are not used by stream billing
1280 ----------------------------------------------------
1281 i_tldv_rec.tld_id_reverses := NULL;
1282 i_tldv_rec.idx_id := NULL;
1283 i_tldv_rec.late_charge_yn := NULL;
1284 i_tldv_rec.date_calculation := NULL;
1285 i_tldv_rec.fixed_rate_yn := NULL;
1286 i_tldv_rec.receivables_invoice_id := NULL;
1287 i_tldv_rec.amount_applied := NULL;
1288 i_tldv_rec.bch_id := NULL;
1289 i_tldv_rec.bgh_id := NULL;
1290 i_tldv_rec.bcl_id := NULL;
1291 i_tldv_rec.bsl_id := NULL;
1292 i_tldv_rec.org_id := NULL;
1293
1294
1295
1296 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1297 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Amount'||i_tldv_rec.amount);
1298 END IF;
1299 ----------------------------------------------------
1300 -- Insert transaction line detail record
1301 ----------------------------------------------------
1302 Okl_Txd_Ar_Ln_Dtls_Pub.insert_txd_ar_ln_dtls
1303 (p_api_version
1304 ,p_init_msg_list
1305 ,l_return_status
1306 ,x_msg_count
1307 ,x_msg_data
1308 ,i_tldv_rec
1309 ,r_tldv_rec);
1310
1311 IF (l_return_status = 'S' ) THEN
1312 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -- Internal TXN Details Created.');
1313 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1314 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,' -- Internal TXN details Created.');
1315 END IF;
1316 ELSE
1317 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -- ERROR: Creating Internal TXN Details.');
1318 END IF;
1319
1320 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1321 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,' -- Internal TXN details Created.');
1322 END IF;
1323
1324 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
1325 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1326 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
1327 RAISE Okl_Api.G_EXCEPTION_ERROR;
1328 END IF;
1329 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1330 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,' -- Internal TXN details Created.');
1331 END IF;
1332
1333 p_bpd_acc_rec.id := r_tldv_rec.id;
1334 p_bpd_acc_rec.source_table := 'OKL_TXD_AR_LN_DTLS_B';
1335 ----------------------------------------------------
1336 -- Create Accounting Distributions
1337 ----------------------------------------------------
1338 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1339 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,' -- In Accounting Distributions '||p_bpd_acc_rec.id);
1340 END IF;
1341 -- Start of wraper code generated automatically by Debug code generator for Okl_Acc_Call_Pub.CREATE_ACC_TRANS
1342 IF(L_DEBUG_ENABLED='Y') THEN
1343 L_LEVEL_PROCEDURE :=FND_LOG.LEVEL_PROCEDURE;
1344 IS_DEBUG_PROCEDURE_ON := OKL_DEBUG_PUB.Check_Log_On(L_MODULE, L_LEVEL_PROCEDURE);
1345 END IF;
1346 IF(IS_DEBUG_PROCEDURE_ON) THEN
1347 BEGIN
1348 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRUBBB.pls call Okl_Acc_Call_Pub.CREATE_ACC_TRANS ');
1349 END;
1350 END IF;
1351 Okl_Acc_Call_Pub.CREATE_ACC_TRANS(
1352 p_api_version
1353 ,p_init_msg_list
1354 ,x_return_status
1355 ,x_msg_count
1356 ,x_msg_data
1357 ,p_bpd_acc_rec
1358 );
1359 IF(IS_DEBUG_PROCEDURE_ON) THEN
1360 BEGIN
1361 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRUBBB.pls call Okl_Acc_Call_Pub.CREATE_ACC_TRANS ');
1362 END;
1363 END IF;
1364 -- End of wraper code generated automatically by Debug code generator for Okl_Acc_Call_Pub.CREATE_ACC_TRANS
1365
1366 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1367 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,' -- Accounting Distributions Created.');
1368 END IF;
1369 IF (x_return_status = 'S' ) THEN
1370 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -- Accounting Distributions Created.');
1371 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1372 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,' -- Accounting Distributions Created.');
1373 END IF;
1374 ELSE
1375 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1376 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,' -- Accounting Distributions Errored.');
1377 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Error Message'||x_msg_data);
1378 END IF;
1379 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -- ERROR: Accounting Distributions NOT Created.');
1380 END IF;
1381
1382
1383 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
1384 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1385 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
1386 RAISE Okl_Api.G_EXCEPTION_ERROR;
1387 END IF;
1388
1389 ----------------------------------------------------
1390 -- Adjust line variables
1391 ----------------------------------------------------
1392
1393 l_khr_id := l_oks_bill_rec.khr_id;
1394 l_bill_date := l_oks_bill_rec.DATE_BILLED_FROM;
1395 l_kle_id := NVL (l_oks_bill_rec.kle_id, l_null_kle_id);
1396 l_header_amount := l_header_amount + l_ste_amount;
1397 l_line_amount := l_line_amount + l_ste_amount;
1398 l_detail_number := l_detail_number + l_line_step;
1399
1400
1401 ---------------------------------------------------
1402 -- Commit the present record
1403 ---------------------------------------------------
1404 COMMIT;
1405
1406 FND_FILE.PUT_LINE (FND_FILE.LOG, ' DONE Processing: Contract Number=> '||l_oks_bill_rec.contract_number||' ,Stream Name=> '
1407 ||' ,for date=> '||l_oks_bill_rec.DATE_BILLED_FROM||' and Amount=> '||l_ste_amount);
1408
1409 FND_FILE.PUT_LINE (FND_FILE.LOG, '===============================================================================');
1410
1411 END LOOP;
1412
1413 ------------------------------------------------------------
1414 -- Save amount for the last transaction header
1415 ------------------------------------------------------------
1416
1417 IF l_khr_id <> l_def_no_val THEN
1418
1419
1420 u_taiv_rec.id := l_header_id;
1421 u_taiv_rec.amount := l_header_amount;
1422
1423 Okl_Trx_Ar_Invoices_Pub.update_trx_ar_invoices
1424 (p_api_version
1425 ,p_init_msg_list
1426 ,l_return_status
1427 ,x_msg_count
1428 ,x_msg_data
1429 ,u_taiv_rec
1430 ,r_taiv_rec);
1431
1432 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
1433 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1434 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
1435 RAISE Okl_Api.G_EXCEPTION_ERROR;
1436 END IF;
1437
1438 END IF;
1439
1440
1441 ------------------------------------------------------------
1442 -- Save amount for the last transaction line
1443 ------------------------------------------------------------
1444
1445 IF l_kle_id <> l_def_no_val THEN
1446
1447 u_tilv_rec.id := l_line_id;
1448 u_tilv_rec.amount := l_line_amount;
1449
1450 Okl_Txl_Ar_Inv_Lns_Pub.update_txl_ar_inv_lns
1451 (p_api_version
1452 ,p_init_msg_list
1453 ,l_return_status
1454 ,x_msg_count
1455 ,x_msg_data
1456 ,u_tilv_rec
1457 ,r_tilv_rec);
1458
1459 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
1460 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1461 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
1462 RAISE Okl_Api.G_EXCEPTION_ERROR;
1463 END IF;
1464
1465 END IF;
1466
1467 Okl_Api.END_ACTIVITY (
1468 x_msg_count => x_msg_count,
1469 x_msg_data => x_msg_data);
1470
1471 EXCEPTION
1472 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
1473 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'Error (EXCP) => '||SQLERRM);
1474 x_return_status := Okl_Api.HANDLE_EXCEPTIONS
1475 (
1476 l_api_name,
1477 G_PKG_NAME,
1478 'Okl_Api.G_RET_STS_ERROR',
1479 x_msg_count,
1480 x_msg_data,
1481 '_PVT'
1482 );
1483 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
1484 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'Error (UNEXCP) => '||SQLERRM);
1485 x_return_status :=Okl_Api.HANDLE_EXCEPTIONS
1486 (
1487 l_api_name,
1488 G_PKG_NAME,
1489 'Okl_Api.G_RET_STS_UNEXP_ERROR',
1490 x_msg_count,
1491 x_msg_data,
1492 '_PVT'
1493 );
1494 WHEN OTHERS THEN
1495 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'Error (Others) => '||SQLERRM);
1496 x_return_status :=Okl_Api.HANDLE_EXCEPTIONS
1497 (
1498 l_api_name,
1499 G_PKG_NAME,
1500 'OTHERS',
1501 x_msg_count,
1502 x_msg_data,
1503 '_PVT'
1504 );
1505 END bill_service_contract;
1506
1507 END OKL_UBB_PVT;