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