[Home] [Help]
PACKAGE BODY: APPS.OKL_BILL_STATUS_PVT
Source
1 PACKAGE BODY OKL_BILL_STATUS_PVT AS
2 /* $Header: OKLRBISB.pls 120.15 2007/08/24 12:31:55 gkhuntet noship $ */
3
4
5 PROCEDURE billing_status(
6 p_api_version IN NUMBER
7 ,p_init_msg_list IN VARCHAR2
8 ,x_return_status OUT NOCOPY VARCHAR2
9 ,x_msg_count OUT NOCOPY NUMBER
10 ,x_msg_data OUT NOCOPY VARCHAR2
11 ,x_bill_stat_tbl OUT NOCOPY bill_stat_tbl_type
12 ,p_khr_id IN NUMBER
13 ,p_transaction_date IN DATE
14 ) IS
15
16 /* rmunjulu R12 Fixes -- forward port bug 5474864 fixes -- comment this cursor and redone below
17 CURSOR c_last_bill_date(c_khr_id in NUMBER, c_transaction_date in DATE) IS
18 select max(AR.DUE_DATE) last_bill_date, tai.description transaction_type
19 -- abindal bug 4529600 start --
20 from okl_cnsld_ar_strms_b cnsld,
21 AR_PAYMENT_SCHEDULES_ALL AR,
22 OKL_XTL_SELL_INVS_B XTL,
23 okl_trx_ar_invoices_tl tai,
24 okl_txl_ar_inv_lns_b til,
25 okl_txd_ar_ln_dtls_b tld
26 -- abindal bug 4529600 end --
27 where cnsld.receivables_invoice_id = AR.customer_trx_id
28 and cnsld.khr_id = c_khr_id
29 and cnsld.id = XTL.lsm_id
30 and xtl.tld_id = tld.id
31 and til.tai_id = tai.id
32 and til.id = tld.til_id_details
33 and tai.description in ('Regular Stream Billing')
34 and cnsld.sel_id in (SELECT SEL.id
35 -- abindal bug 4529600 start --
36 FROM OKL_STREAMS STM,
37 OKL_STRM_ELEMENTS SEL,
38 OKC_K_HEADERS_B KHR,
39 OKL_STRM_TYPE_B STY
40 -- abindal bug 4529600 end --
41 WHERE KHR.id = c_khr_id
42 AND SEL.stream_element_date <= c_transaction_date
43 AND KHR.id = STM.khr_id
44 AND STM.id = SEL.stm_id
45 AND STM.say_code = 'CURR'
46 AND STM.active_yn = 'Y'
47 AND STM.sty_id = STY.id
48 AND NVL(STY.billable_yn,'N') = 'Y'
49 AND STY.stream_type_purpose IN ('RENT', 'INTEREST_PAYMENT',
50 'PRINCIPAL_PAYMENT', 'LOAN_PAYMENT',
51 'FLOAT_FACTOR_ADJUSTMENT')
52 AND SEL.amount > 0)
53 group by tai.description;
54 */
55
56 -- rmunjulu R12 Fixes -- forward port bug 5474864 fixes -- redone this cursor
57 CURSOR c_last_bill_date(c_khr_id in NUMBER, c_transaction_date in DATE) IS
58 SELECT MAX(SEL.stream_element_date) last_bill_date
59 FROM OKL_STREAMS STM,
60 OKL_STRM_ELEMENTS SEL,
61 OKC_K_HEADERS_B KHR,
62 OKL_STRM_TYPE_V STY
63 WHERE KHR.id = c_khr_id
64 AND SEL.stream_element_date <=c_transaction_date
65 AND KHR.id = STM.khr_id
66 AND STM.id = SEL.stm_id
67 AND STM.say_code = 'CURR'
68 AND STM.active_yn = 'Y'
69 AND STM.sty_id = STY.id
70 AND STY.billable_yn = 'Y'
71 -- AND STY.name = 'RENT'
72 AND STY.STREAM_TYPE_PURPOSE IN ('RENT', 'INTEREST_PAYMENT',
73 'PRINCIPAL_PAYMENT', 'LOAN_PAYMENT',
74 'FLOAT_FACTOR_ADJUSTMENT')
75 AND SEL.amount > 0
76 AND SEL.date_billed is not null;
77
78 -- abindal added this cursor for bug 4291677 -- start
79 CURSOR c_last_bill_date_import(c_khr_id in NUMBER, c_transaction_date in DATE) IS
80 SELECT --SEL.stream_element_date last_bill_date
81 MAX(SEL.date_billed) last_bill_date
82 FROM OKL_STREAMS_V STM,
83 OKL_STRM_ELEMENTS_V SEL,
84 OKC_K_HEADERS_V KHR,
85 OKL_STRM_TYPE_V STY
86 WHERE KHR.id = c_khr_id
87 AND SEL.stream_element_date <=c_transaction_date
88 AND KHR.id = STM.khr_id
89 AND STM.id = SEL.stm_id
90 AND STM.say_code = 'CURR'
91 AND STM.active_yn = 'Y'
92 AND STM.sty_id = STY.id
93 AND NVL(STY.billable_yn,'N') = 'Y'
94 -- gkhuntet added for Forward port Bug #5488905 Start
95 AND STY.STREAM_TYPE_PURPOSE IN ('RENT', 'INTEREST_PAYMENT',
96 'PRINCIPAL_PAYMENT', 'LOAN_PAYMENT',
97 'FLOAT_FACTOR_ADJUSTMENT');
98 -- gkhuntet added for Forward port Bug #5488905 Start
99
100 CURSOR check_cont_typ (cp_khr_id IN NUMBER) IS
101 SELECT ORIG_SYSTEM_SOURCE_CODE
102 FROM OKC_K_HEADERS_B
103 WHERE id = cp_khr_id;
104
105 -- abindal added this cursor for bug 4291677 -- end
106
107 -----------------------------------------------------------------------------------------
108 -- abindal bug 4396207 start --
109 -- check AR for invoice due date.
110 /* -- rmunjulu R12 Fixes -- commented this cursor out and redone below
111 CURSOR c_last_bill_date_1 (c_sel_id in NUMBER, c_khr_id in NUMBER ) IS
112 select AR.DUE_DATE last_bill_date
113 from okl_cnsld_ar_strms_b cnsld,
114 AR_PAYMENT_SCHEDULES_ALL AR,
115 OKL_XTL_SELL_INVS_V XTL,
116 okl_trx_ar_invoices_v tai,
117 okl_txl_ar_inv_lns_v til,
118 okl_txd_ar_ln_dtls_v tld
119 where cnsld.receivables_invoice_id = AR.customer_trx_id
120 and cnsld.khr_id = c_khr_id
121 and cnsld.id = XTL.lsm_id
122 and xtl.tld_id = tld.id
123 and til.tai_id = tai.id
124 and til.id = tld.til_id_details
125 and tai.description in ('Regular Stream Billing')
126 and cnsld.sel_id = c_sel_id;
127 */
128
129 -- rmunjulu R12 Fixes - redone this cursor
130 CURSOR c_last_bill_date_1 (c_sel_id in NUMBER, c_khr_id in NUMBER ) IS
131 select AR.DUE_DATE last_bill_date
132 from --okl_cnsld_ar_strms_b cnsld,
133 AR_PAYMENT_SCHEDULES_ALL AR,
134 --OKL_XTL_SELL_INVS_V XTL,
135 --okl_trx_ar_invoices_v tai,
136 --okl_txl_ar_inv_lns_v til,
137 --okl_txd_ar_ln_dtls_v tld
138 okl_bpd_tld_ar_lines_v RACTRL
139 where RACTRL.customer_trx_id = AR.customer_trx_id
140 and RACTRL.khr_id = c_khr_id
141 --and cnsld.id = XTL.lsm_id
142 --and xtl.tld_id = tld.id
143 --and til.tai_id = tai.id
144 --and til.id = tld.til_id_details
145 and RACTRL.description in ('Regular Stream Billing')
146 and RACTRL.sel_id = c_sel_id;
147
148
149 -- obtain ALL lastest SEL_ID's for contract
150 CURSOR c_last_bill_date_2 (c_khr_id in NUMBER, c_transaction_date in DATE ) IS
151 --Bug 5050707: SEL.id not being used , putting distinct would restrict the processing of the duplicate link history stream id
152 --SELECT SEL.ID, STM.LINK_HIST_STREAM_ID
153 SELECT distinct STM.LINK_HIST_STREAM_ID
154 --Bug 5050707:end
155 FROM OKL_STREAMS_V STM,
156 OKL_STRM_ELEMENTS_V SEL,
157 OKC_K_HEADERS_V KHR,
158 OKL_STRM_TYPE_V STY
159 WHERE KHR.id = c_khr_id
160 AND SEL.stream_element_date <= c_transaction_date
161 AND STM.id = SEL.stm_id
162 AND KHR.id = STM.khr_id
163 AND STM.say_code = 'CURR'
164 AND STM.active_yn = 'Y'
165 AND STM.sty_id = STY.id
166 AND NVL(STY.billable_yn,'N') = 'Y'
167 AND STY.stream_type_purpose IN ('RENT', 'INTEREST_PAYMENT',
168 'PRINCIPAL_PAYMENT', 'LOAN_PAYMENT',
169 'FLOAT_FACTOR_ADJUSTMENT')
170 AND SEL.amount > 0;
171
172 -- drill down each SEL_ID until original retreived ...
173 CURSOR c_last_bill_date_3 (c_sel_id in NUMBER) IS
174 SELECT LINK_HIST_STREAM_ID
175 FROM OKL_STREAMS_V
176 WHERE ID = c_sel_id;
177
178 /* -- rmunjulu R12 Fixes -- commented this cursor out and redone below
179 -- fetch original sel_ids from stm_id ...
180 CURSOR c_last_bill_date_4 (c_stm_id in NUMBER, c_khr_id IN NUMBER,c_transaction_date in DATE) IS
181 SELECT distinct(SEL.ID)
182 FROM OKL_STREAMS_V STM,
183 OKL_STRM_ELEMENTS_V SEL,
184 OKC_K_HEADERS_V KHR,
185 OKL_STRM_TYPE_V STY,
186 okl_cnsld_ar_strms_b cnsld
187 WHERE KHR.id = c_khr_id
188 AND SEL.stream_element_date <= c_transaction_date
189 AND STM.id = SEL.stm_id
190 AND KHR.id = STM.khr_id
191 AND STM.say_code = 'HIST'
192 AND STM.active_yn = 'N'
193 AND STM.sty_id = STY.id
194 AND NVL(STY.billable_yn,'N') = 'Y'
195 AND STY.stream_type_purpose IN ('RENT', 'INTEREST_PAYMENT',
196 'PRINCIPAL_PAYMENT', 'LOAN_PAYMENT',
197 'FLOAT_FACTOR_ADJUSTMENT')
198 AND SEL.amount > 0
199 AND SEL.stm_id = c_stm_id
200 and cnsld.sel_id = sel.id
201 and cnsld.khr_id = c_khr_id;
202 */
203
204 -- rmunjulu R12 Fixes - redone this cursor
205 CURSOR c_last_bill_date_4 (c_stm_id in NUMBER, c_khr_id IN NUMBER,c_transaction_date in DATE) IS
206 SELECT distinct(SEL.ID)
207 FROM OKL_STREAMS_V STM,
208 OKL_STRM_ELEMENTS_V SEL,
209 OKC_K_HEADERS_V KHR,
210 OKL_STRM_TYPE_V STY,
211 --okl_cnsld_ar_strms_b cnsld
212 okl_txd_ar_ln_dtls_b tld
213 WHERE KHR.id = c_khr_id
214 AND SEL.stream_element_date <= c_transaction_date
215 AND STM.id = SEL.stm_id
216 AND KHR.id = STM.khr_id
217 AND STM.say_code = 'HIST'
218 AND STM.active_yn = 'N'
219 AND STM.sty_id = STY.id
220 AND NVL(STY.billable_yn,'N') = 'Y'
221 AND STY.stream_type_purpose IN ('RENT', 'INTEREST_PAYMENT',
222 'PRINCIPAL_PAYMENT', 'LOAN_PAYMENT',
223 'FLOAT_FACTOR_ADJUSTMENT')
224 AND SEL.amount > 0
225 AND SEL.stm_id = c_stm_id
226 and tld.sel_id = sel.id
227 and tld.khr_id = c_khr_id;
228
229
230 -- abindal bug 4396207 end --
231 -----------------------------------------------------------------------------------------
232
233 -- get contract line id's
234 CURSOR c_okl_line_items(c_khr_id IN NUMBER) IS
235 SELECT id, lse_id
236 FROM okc_k_lines_b
237 WHERE dnz_chr_id = c_khr_id
238 AND orig_system_source_code <> 'OKL_SPLIT';
239
240 -----------------------------------------------------------------------------------------
241 /* -- rmunjulu R12 Fixes -- commented this cursor out and redone below
242 -- get contract line id's
243 CURSOR c_last_split_bill_date( c_khr_id IN NUMBER
244 , c_kle_id IN NUMBER
245 , c_transaction_date IN DATE
246 ) IS
247
248 SELECT max(AR.DUE_DATE) last_bill_date--, tai.description transaction_type
249 FROM okl_cnsld_ar_strms_b cnsld,
250 AR_PAYMENT_SCHEDULES_ALL AR,
251 OKL_XTL_SELL_INVS_V XTL,
252 okl_trx_ar_invoices_v tai,
253 okl_txl_ar_inv_lns_v til,
254 okl_txd_ar_ln_dtls_v tld
255 WHERE cnsld.receivables_invoice_id = AR.customer_trx_id
256 AND cnsld.khr_id = c_khr_id
257 AND cnsld.id = XTL.lsm_id
258 AND xtl.tld_id = tld.id
259 AND til.tai_id = tai.id
260 AND til.id = tld.til_id_details
261 AND tai.description IN ('Regular Stream Billing')
262 AND EXISTS ( SELECT '1'
263 FROM OKL_STREAMS STM,
264 OKL_STRM_ELEMENTS SEL,
265 -- OKC_K_LINES_B KLE,
266 OKL_STRM_TYPE_B STY
267 WHERE SEL.ID = cnsld.SEL_ID
268 AND SEL.stream_element_date <= TRUNC(c_transaction_date)
269 -- AND KLE.ID = STM.KLE_ID
270 AND STM.KHR_ID = c_khr_id
271 AND STM.id = SEL.stm_id
272 -- AND STM.say_code = 'CURR'
273 -- AND STM.active_yn = 'Y'
274 AND STM.sty_id = STY.id
275 AND NVL(STY.billable_yn,'N') = 'Y'
276 AND STY.stream_type_purpose IN ('RENT', 'INTEREST_PAYMENT',
277 'PRINCIPAL_PAYMENT', 'LOAN_PAYMENT',
278 'FLOAT_FACTOR_ADJUSTMENT')
279 AND SEL.amount > 0
280 AND STM.KLE_ID IN ( SELECT kle.id
281 FROM okc_k_lines_b kle
282 WHERE kle.dnz_chr_id = c_khr_id
283 CONNECT BY PRIOR kle.orig_system_id1 = kle.id
284 START WITH kle.id = c_kle_id ))
285 GROUP BY tai.description;
286 */
287
288 -- rmunjulu R12 Fixes - redone this cursor
289 CURSOR c_last_split_bill_date( c_khr_id IN NUMBER
290 , c_kle_id IN NUMBER
291 , c_transaction_date IN DATE
292 ) IS
293
294 SELECT max(AR.DUE_DATE) last_bill_date--, tai.description transaction_type
295 FROM --okl_cnsld_ar_strms_b cnsld,
296 AR_PAYMENT_SCHEDULES_ALL AR,
297 --OKL_XTL_SELL_INVS_V XTL,
298 --okl_trx_ar_invoices_v tai,
299 --okl_txl_ar_inv_lns_v til,
300 --okl_txd_ar_ln_dtls_v tld
301 okl_bpd_tld_ar_lines_v RACTRL
302 WHERE RACTRL.CUSTOMER_TRX_ID = AR.customer_trx_id
303 AND RACTRL.khr_id = c_khr_id
304 --AND cnsld.id = XTL.lsm_id
305 --AND xtl.tld_id = tld.id
306 --AND til.tai_id = tai.id
307 --AND til.id = tld.til_id_details
308 AND RACTRL.description IN ('Regular Stream Billing')
309 AND EXISTS ( SELECT '1'
310 FROM OKL_STREAMS STM,
311 OKL_STRM_ELEMENTS SEL,
312 -- OKC_K_LINES_B KLE,
313 OKL_STRM_TYPE_B STY
314 WHERE SEL.ID = RACTRL.SEL_ID
315 AND SEL.stream_element_date <= TRUNC(c_transaction_date)
316 -- AND KLE.ID = STM.KLE_ID
317 AND STM.KHR_ID = c_khr_id
318 AND STM.id = SEL.stm_id
319 -- AND STM.say_code = 'CURR'
320 -- AND STM.active_yn = 'Y'
321 AND STM.sty_id = STY.id
322 AND NVL(STY.billable_yn,'N') = 'Y'
323 AND STY.stream_type_purpose IN ('RENT', 'INTEREST_PAYMENT',
324 'PRINCIPAL_PAYMENT', 'LOAN_PAYMENT',
325 'FLOAT_FACTOR_ADJUSTMENT')
326 AND SEL.amount > 0
327 AND STM.KLE_ID IN ( SELECT kle.id
328 FROM okc_k_lines_b kle
329 WHERE kle.dnz_chr_id = c_khr_id
330 CONNECT BY PRIOR kle.orig_system_id1 = kle.id
331 START WITH kle.id = c_kle_id ))
332 GROUP BY RACTRL.description;
333
334 -----------------------------------------------------------------------------------------
335
336 /*
337 CURSOR c_last_sch_bill_date(c_khr_id in NUMBER, c_transaction_date DATE) IS
338 SELECT max(SEL.stream_element_date) last_sche_bill_date, sel.id
339 FROM OKL_STREAMS_V STM,
340 OKL_STRM_ELEMENTS_V SEL,
341 OKC_K_HEADERS_V KHR,
342 OKL_STRM_TYPE_B STY
343 WHERE KHR.id = c_khr_id
344 AND SEL.stream_element_date <= c_transaction_date
345 AND KHR.id = STM.khr_id
346 AND STM.id = SEL.stm_id
347 AND STM.say_code = 'CURR'
348 AND STM.active_yn = 'Y'
349 AND SEL.date_billed IS NULL
350 AND STM.sty_id = STY.id
351 AND NVL(STY.billable_yn,'N') = 'Y'
352 AND SEL.amount > 0
353 AND ROWNUM < 2;
354 */
355
356 CURSOR c_last_sch_bill_date(c_khr_id in NUMBER, c_transaction_date DATE) IS
357 SELECT sel.id stream_id,
358 sel.stream_element_date last_sche_bill_date
359 FROM OKL_STREAMS_V STM,
360 OKL_STRM_ELEMENTS_V SEL,
361 OKL_STRM_TYPE_V STY
362 WHERE sel.stream_element_date = (SELECT max(SEL.stream_element_date) last_sche_bill_date
363 FROM OKL_STREAMS_V STM,
364 OKL_STRM_ELEMENTS_V SEL,
365 OKC_K_HEADERS_V KHR,
366 OKL_STRM_TYPE_V STY
367 WHERE KHR.id = c_khr_id
368 AND SEL.stream_element_date <= c_transaction_date
369 AND KHR.id = STM.khr_id
370 AND STM.id = SEL.stm_id
371 AND STM.say_code = 'CURR'
372 AND STM.active_yn = 'Y'
373 AND STM.sty_id = STY.id
374 AND NVL(STY.billable_yn,'N') = 'Y'
375 AND STY.stream_type_purpose IN ('RENT', 'INTEREST_PAYMENT',
376 'PRINCIPAL_PAYMENT', 'LOAN_PAYMENT',
377 'FLOAT_FACTOR_ADJUSTMENT')
378 AND SEL.amount > 0)
379 AND STM.id = SEL.stm_id
380 AND STM.sty_id = STY.id
381 AND STY.stream_type_purpose IN ('RENT', 'INTEREST_PAYMENT',
382 'PRINCIPAL_PAYMENT', 'LOAN_PAYMENT',
383 'FLOAT_FACTOR_ADJUSTMENT')
384 AND STM.khr_id = c_khr_id
385 AND STM.say_code = 'CURR'
386 AND STM.active_yn = 'Y'
387 AND NVL(STY.billable_yn,'N') = 'Y'
388 AND ROWNUM < 2;
389
390 -- nikshah -- Bug # 5484903 Fixed,
391 -- Changed c_oks_last_sch_bill_date_10(c_khr_id in NUMBER, c_transaction_date DATE) SQL definition
392 CURSOR c_oks_last_sch_bill_date_10(c_khr_id in NUMBER, c_transaction_date DATE) IS
393 select max(schd.date_to_interface) last_sche_bill_date
394 from okc_k_rel_objs rel,
395 okc_k_headers_b hdr,
396 okc_k_headers_b oks,
397 okc_k_lines_b oks_line,
398 OKS_LEVEL_ELEMENTS_V schd, OKS_STREAM_LEVELS_B strm
399 where hdr.id = c_khr_id
400 and rty_code = 'OKLSRV'
401 and rel.jtot_object1_code = 'OKL_SERVICE'
402 and rel.cle_id is null
403 and rel.chr_id = hdr.id
404 and rel.object1_id1 = oks.id
405 and oks.id = oks_line.dnz_chr_id
406 and oks_line.lse_id in (7,8,9,10,11,35)
407 and oks_line.id = strm.cle_id
408 and strm.id = schd.rul_id
409 and schd.date_to_interface <= c_transaction_date;
410
411 -- nikshah -- Bug # 5484903 Fixed,
412 -- Changed c_oks_last_sch_bill_date_9(c_khr_id in NUMBER, c_transaction_date DATE) SQL definition
413 CURSOR c_oks_last_sch_bill_date_9(c_khr_id in NUMBER, c_transaction_date DATE) IS
414 select max(schd.date_to_interface) last_sche_bill_date
415 from okc_k_rel_objs rel,
416 okc_k_headers_b hdr,
417 okc_k_headers_b oks,
418 okc_k_lines_b oks_line,
419 OKS_LEVEL_ELEMENTS_V schd,
420 okc_rules_b rules,
421 okc_rule_groups_b rgp
422 where hdr.id = c_khr_id
423 and rty_code = 'OKLSRV'
424 and rel.jtot_object1_code = 'OKL_SERVICE'
425 and rel.cle_id is null
426 and rel.chr_id = hdr.id
427 and rel.object1_id1 = oks.id
428 and oks.id = oks_line.dnz_chr_id
429 and oks_line.lse_id in (7,8,9,10,11,35)
430 and oks_line.id = rgp.cle_id
431 and rules.rgp_id = rgp.id
432 and rules.id = schd.rul_id
433 and rules.rule_information_category = 'SLL'
434 and schd.date_to_interface <= c_transaction_date;
435
436 /* -- rmunjulu R12 Fixes -- commented this cursor out and redone below
437 CURSOR c_oks_last_bill_date(c_khr_id in NUMBER, c_transaction_date DATE) IS
438 select max(AR.DUE_DATE) last_bill_date, tai.description transaction_type
439 from okl_cnsld_ar_strms_b cnsld,
440 AR_PAYMENT_SCHEDULES_ALL AR,
441 OKL_XTL_SELL_INVS_V XTL,
442 okl_trx_ar_invoices_v tai,
443 okl_txl_ar_inv_lns_v til,
444 okl_txd_ar_ln_dtls_v tld
445 where cnsld.receivables_invoice_id = AR.customer_trx_id
446 and cnsld.khr_id = c_khr_id
447 and cnsld.id = XTL.lsm_id
448 and xtl.tld_id = tld.id
449 and til.tai_id = tai.id
450 and til.id = tld.til_id_details
451 and tai.description in ('OKS Billing')
452 AND tai.date_invoiced <= c_transaction_date
453 group by tai.description;
454 */
455
456 -- rmunjulu R12 Fixes - redone this cursor
457 CURSOR c_oks_last_bill_date(c_khr_id in NUMBER, c_transaction_date DATE) IS
458 select max(AR.DUE_DATE) last_bill_date, RACTRL.description transaction_type
459 from --okl_cnsld_ar_strms_b cnsld,
460 AR_PAYMENT_SCHEDULES_ALL AR,
461 --OKL_XTL_SELL_INVS_V XTL,
462 --okl_trx_ar_invoices_v tai,
463 --okl_txl_ar_inv_lns_v til,
464 --okl_txd_ar_ln_dtls_v tld
465 okl_bpd_tld_ar_lines_v RACTRL
466 where RACTRL.CUSTOMER_TRX_id = AR.customer_trx_id
467 and RACTRL.khr_id = c_khr_id
468 --and cnsld.id = XTL.lsm_id
469 --and xtl.tld_id = tld.id
470 --and til.tai_id = tai.id
471 --and til.id = tld.til_id_details
472 and RACTRL.description in ('OKS Billing')
473 AND RACTRL.date_invoiced <= c_transaction_date
474 group by RACTRL.description;
475
476 CURSOR check_oks_ver IS
477 SELECT 1
478 FROM okc_class_operations
479 WHERE cls_code = 'SERVICE'
480 AND opn_code = 'CHECK_RULE';
481
482 l_api_version CONSTANT NUMBER := 1;
483 l_api_name CONSTANT VARCHAR2(30) := 'billing_status';
484 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
485 l_stream_id NUMBER;
486
487 -- l_khr_id NUMBER;
488 i NUMBER;
489 j NUMBER;
490
491 -- abindal bug 4396207 start --
492 k NUMBER;
493 l NUMBER;
494 l_link_hist_stream_id NUMBER DEFAULT NULL;
495 l_link_hist_stream_id_2 NUMBER DEFAULT NULL;
496 l_max_last_bill_date DATE DEFAULT NULL;
497 l_max_last_bill_date_1 DATE DEFAULT NULL;
498
499 L_EXIT_LOOP NUMBER DEFAULT 0;
500 l_flag NUMBER DEFAULT NULL;
501
502 l_valid_stm_tbl valid_stm_tbl_type;
503 l_valid_sel_tbl valid_sel_tbl_type;
504 -- abindal bug 4396207 end --
505
506 -- abindal Added following variables for bug 4291677 start --
507 l_cont_typ VARCHAR2(30);
508 gone_to_AR BOOLEAN := TRUE;
509 -- abindal Added following variables for bug 4291677 end --
510
511 line_items_tbl line_items_tbl_type;
512 l_bill_stat_rec bill_stat_rec_type;
513 l_bill_stat_tbl bill_stat_tbl_type;
514 l_oks_ver VARCHAR2(10);
515
516 l_last_bill_date DATE DEFAULT NULL;
517 l_exit_loop_flag NUMBER DEFAULT 0;
518
519
520 BEGIN
521
522 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
523 G_PKG_NAME,
524 p_init_msg_list,
525 l_api_version,
526 p_api_version,
527 '_PVT',
528 x_return_status);
529
530
531 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
532 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
533 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
534 RAISE OKL_API.G_EXCEPTION_ERROR;
535 END IF;
536
537
538 i := 0;
539 j := 0;
540 -- abindal bug 4396207 start --
541 k := 0;
542 l := 0;
543 -- abindal bug 4396207 end --
544
545
546 FOR l_last_sch_bill_date IN c_last_sch_bill_date(p_khr_id, p_transaction_date)
547 LOOP
548 l_bill_stat_tbl(i).last_schedule_bill_date := l_last_sch_bill_date.last_sche_bill_date;
549 l_stream_id := l_last_sch_bill_date.stream_id;
550 l_bill_stat_tbl(i).transaction_type := 'RENTAL';
551 -- dbms_output.put_line('Stream :'||l_stream_id);
552 -- dbms_output.put_line('last_schedule_bill_date :'||l_bill_stat_tbl(i).last_schedule_bill_date);
553
554 FOR l_last_bill_date IN c_last_bill_date(p_khr_id, p_transaction_date)
555 LOOP
556
557 l_bill_stat_tbl(i).last_bill_date := l_last_bill_date.last_bill_date;
558
559 -- l_bill_stat_tbl(i).last_bill_date := TRUNC(SYSDATE);
560 -- l_bill_stat_tbl(i).transaction_type := l_last_bill_date.transaction_type;
561 -- dbms_output.put_line('last_bill_date :'||l_bill_stat_tbl(i).last_bill_date);
562
563 END LOOP;
564
565 i := i + 1;
566 END LOOP;
567
568 -- abindal bug 4396207 start --
569 FOR l_last_bill_date IN c_last_bill_date(p_khr_id, p_transaction_date)
570 LOOP
571
572 l_bill_stat_tbl(i).last_bill_date := l_last_bill_date.last_bill_date;
573
574
575 END LOOP;
576
577 IF l_bill_stat_tbl.COUNT > 0 THEN -- bug 4599897
578
579 IF l_bill_stat_tbl(0).last_bill_date IS NULL THEN
580 -- possibly because of rebook, lets see ...
581
582 FOR l_last_bill_date_2 IN c_last_bill_date_2(p_khr_id, p_transaction_date)
583 -- pick up all current stream elements for contract
584 LOOP
585
586 l_link_hist_stream_id := l_last_bill_date_2.LINK_HIST_STREAM_ID;
587
588 IF l_link_hist_stream_id IS NOT NULL THEN
589 -- this contract has been through a rebook ...
590
591 L_EXIT_LOOP := 0;
592
593 LOOP
594 -- Bug 5050707 : store all linked stream ids
595 l_valid_stm_tbl(k).STM_ID := l_link_hist_stream_id;
596 k:=k+1;
597 -- Bug 5050707:end
598
599 OPEN c_last_bill_date_3(l_link_hist_stream_id);
600 FETCH c_last_bill_date_3 INTO l_link_hist_stream_id_2;
601 CLOSE c_last_bill_date_3;
602
603 IF l_link_hist_stream_id_2 IS NOT NULL THEN
604 l_link_hist_stream_id := l_link_hist_stream_id_2;
605 l_link_hist_stream_id_2 := NULL;
606 L_EXIT_LOOP := 0;
607 ELSE
608 /* bug 5050707 commented
609 l_valid_stm_tbl(k).STM_ID := l_link_hist_stream_id;
610 */
611 -- l_valid_sel_tbl(k).SEL_ID := l_link_hist_stream_id;
612 l_link_hist_stream_id := NULL;
613 l_link_hist_stream_id_2 := NULL;
614 L_EXIT_LOOP := 1;
615 /* bug 5050707 commented
616 k := k + 1;
617 */
618 END IF;
619
620 EXIT WHEN L_EXIT_LOOP = 1;
621
622 END LOOP;
623 /*Bug 5050707 : wrong assignment . It should always have stream id rather than stream element id so commented the code below
624 ELSE
625 l_valid_stm_tbl(k).STM_ID := l_last_bill_date_2.ID;
626 k := k + 1;
627 */
628 END IF;
629
630
631 END LOOP;
632
633 -- no we have a table of valid sel_id's we can check
634 -- against AR for MAX invoice due_date...
635
636 k := l_valid_stm_tbl.FIRST;
637
638 -- bug 5050707 :Changed to prevent PL/SQL: numeric or value errorif count is zero
639 -- IF l_valid_stm_tbl.COUNT >= 0 THEN
640 IF l_valid_stm_tbl.COUNT > 0 THEN
641 -- bug 5050707 end
642 LOOP
643
644 FOR l_last_bill_date_4 IN c_last_bill_date_4(l_valid_stm_tbl(k).STM_ID, p_khr_id, p_transaction_date)
645 LOOP
646
647 l_valid_sel_tbl(l).SEL_ID := l_last_bill_date_4.ID;
648 l := l + 1;
649
650 END LOOP;
651
652 EXIT WHEN (k = l_valid_stm_tbl.LAST);
653 k := k + 1;
654
655 END LOOP;
656
657 END IF;
658
659 l := l_valid_sel_tbl.FIRST;
660 l_flag := 1;
661
662 IF l_valid_sel_tbl.COUNT > 0 THEN --- ?? why >= 0 any specific reason.. changed to > 0 -- Guru
663
664 LOOP
665
666 OPEN c_last_bill_date_1(l_valid_sel_tbl(l).SEL_ID, p_khr_id);
667 FETCH c_last_bill_date_1 INTO l_max_last_bill_date;
668 CLOSE c_last_bill_date_1;
669
670 IF l_flag = 1 AND l_max_last_bill_date IS NOT NULL THEN
671
672 l_max_last_bill_date_1 := TRUNC(l_max_last_bill_date);
673 l_bill_stat_tbl(0).last_bill_date := TRUNC(l_max_last_bill_date_1);
674 l_flag := 0;
675 END IF;
676
677 IF l_max_last_bill_date IS NOT NULL THEN
678 IF TRUNC(l_max_last_bill_date) > TRUNC(l_max_last_bill_date_1) THEN
679 l_max_last_bill_date_1 := TRUNC(l_max_last_bill_date);
680
681 l_bill_stat_tbl(0).last_bill_date := TRUNC(l_max_last_bill_date_1);
682 END IF;
683 END IF;
684
685 EXIT WHEN (l = l_valid_sel_tbl.LAST);
686 l := l + 1;
687
688 END LOOP;
689
690 END IF;
691
692 END IF;
693
694 END IF;
695 -- abindal bug 4396207 end --
696
697 -----------------------------------------------------------------------------------------
698
699 IF l_bill_stat_tbl.COUNT = 1 AND l_bill_stat_tbl(0).last_bill_date IS NULL THEN
700
701 FOR l_okl_line_items IN c_okl_line_items(p_khr_id)
702 LOOP
703 j := j + 1;
704 line_items_tbl(j).line_id := l_okl_line_items.id;
705 END LOOP;
706
707 IF line_items_tbl.COUNT > 0 THEN
708
709 j := line_items_tbl.FIRST;
710
711 LOOP
712
713 OPEN c_last_split_bill_date( p_khr_id
714 , line_items_tbl(j).line_id
715 , TRUNC(p_transaction_date)
716 );
717 FETCH c_last_split_bill_date INTO l_last_bill_date;
718 CLOSE c_last_split_bill_date;
719
720 IF l_last_bill_date IS NOT NULL THEN
721 l_bill_stat_tbl(0).last_bill_date := TRUNC(l_last_bill_date);
722 l_exit_loop_flag := 1;
723 ELSE
724 l_exit_loop_flag := 0;
725 END IF;
726
727 EXIT WHEN l_exit_loop_flag = 1;
728 EXIT WHEN j = line_items_tbl.LAST;
729
730 j := line_items_tbl.NEXT(j);
731
732 END LOOP;
733
734 END IF;
735
736 END IF;
737
738 -----------------------------------------------------------------------------------------
739
740
741 l_oks_ver := '?';
742 OPEN check_oks_ver;
743 FETCH check_oks_ver INTO l_oks_ver;
744
745 IF check_oks_ver%NOTFOUND THEN
746 l_oks_ver := '9';
747 ELSE
748 l_oks_ver := '10';
749 END IF;
750
751 CLOSE check_oks_ver;
752
753
754 IF (l_oks_ver = '10') THEN
755 FOR l_oks_last_sch_bill_date IN c_oks_last_sch_bill_date_10(p_khr_id, p_transaction_date)
756 LOOP
757 l_bill_stat_tbl(i).last_schedule_bill_date := l_oks_last_sch_bill_date.last_sche_bill_date;
758 l_bill_stat_tbl(i).transaction_type := 'SERVICE';
759 -- dbms_output.put_line('Stream :'||l_stream_id);
760 -- dbms_output.put_line('last_schedule_bill_date :'||l_bill_stat_tbl(i).last_schedule_bill_date);
761
762 FOR l_last_bill_date IN c_oks_last_bill_date(p_khr_id, p_transaction_date)
763 LOOP
764
765 l_bill_stat_tbl(i).last_bill_date := l_last_bill_date.last_bill_date;
766 -- dbms_output.put_line('last_bill_date :'||l_bill_stat_tbl(i).last_bill_date);
767
768 END LOOP;
769
770 i := i + 1;
771 END LOOP;
772 ELSE -- oks_ver = 9
773 -- dbms_output.put_line('I after Rent: '||i);
774 -- dbms_output.put_line('In OKS 9 Cursor');
775 FOR l_oks_last_sch_bill_date IN c_oks_last_sch_bill_date_9(p_khr_id, p_transaction_date)
776 LOOP
777 l_bill_stat_rec.last_schedule_bill_date := l_oks_last_sch_bill_date.last_sche_bill_date;
778 l_bill_stat_rec.transaction_type := 'SERVICE';
779 -- dbms_output.put_line('Stream :'||l_stream_id);
780 -- dbms_output.put_line('last_schedule_bill_date :'||l_bill_stat_rec.last_schedule_bill_date);
781
782 FOR l_last_bill_date IN c_oks_last_bill_date(p_khr_id, p_transaction_date)
783 LOOP
784
785 l_bill_stat_rec.last_bill_date := l_last_bill_date.last_bill_date;
786 -- dbms_output.put_line('last_bill_date :'||l_bill_stat_rec.last_bill_date);
787
788 END LOOP;
789
790 l_bill_stat_tbl(i) := l_bill_stat_rec;
791
792 i := i + 1;
793 END LOOP;
794 END IF;
795
796 -- abindal START bug 4290677 --
797
798 IF l_bill_stat_tbl.COUNT > 0 THEN -- [1]
799
800 -- loop thru the bill_statuses table
801 FOR l_bill_counter IN l_bill_stat_tbl.FIRST..l_bill_stat_tbl.LAST LOOP
802
803 -- For regular stream billing ie RENT billing
804 IF l_bill_stat_tbl(l_bill_counter).transaction_type = 'RENTAL' THEN --[2]
805
806 -- Raise error if the last_bill_date is NULL
807 -- or if the last scheduled billing date > last billing run date
808
809 IF l_bill_stat_tbl(l_bill_counter).last_bill_date IS NOT NULL THEN
810 gone_to_AR := TRUE;
811 ELSIF l_bill_stat_tbl(l_bill_counter).last_bill_date IS NULL
812 OR (TRUNC(l_bill_stat_tbl(l_bill_counter).last_schedule_bill_date) >
813 TRUNC(l_bill_stat_tbl(l_bill_counter).last_bill_date)) THEN --[3]
814 gone_to_AR := FALSE;
815 END IF; --[-3]
816 END IF; --[-2]
817 END LOOP;
818 END IF; --[-1]
819
820 IF (NOT gone_to_AR) THEN -- [1]
821
822
823 OPEN check_cont_typ(p_khr_id);
824 FETCH check_cont_typ INTO l_cont_typ;
825 CLOSE check_cont_typ;
826
827 IF l_cont_typ = 'OKL_IMPORT' THEN
828 i := 0;
829
830
831 FOR l_last_sch_bill_date IN c_last_sch_bill_date(p_khr_id, p_transaction_date)
832 LOOP
833
834
835 FOR l_last_bill_date IN c_last_bill_date_import(p_khr_id, p_transaction_date)
836 LOOP
837 l_bill_stat_tbl(i).last_bill_date := l_last_bill_date.last_bill_date;
838
839 END LOOP;
840
841 i := i + 1;
842
843 END LOOP;
844
845 END IF;
846
847
848 END IF;
849
850 -- abindal START bug 4290677 --
851
852 x_bill_stat_tbl := l_bill_stat_tbl;
853 Okl_Api.END_ACTIVITY (
854 x_msg_count => x_msg_count,
855 x_msg_data => x_msg_data);
856
857 EXCEPTION
858 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
859 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'Error (EXCP) => '||SQLERRM);
860 x_return_status := Okl_Api.HANDLE_EXCEPTIONS
861 (
862 l_api_name,
863 G_PKG_NAME,
864 'Okl_Api.G_RET_STS_ERROR',
865 x_msg_count,
866 x_msg_data,
867 '_PVT'
868 );
869 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
870 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'Error (UNEXCP) => '||SQLERRM);
871 x_return_status :=Okl_Api.HANDLE_EXCEPTIONS
872 (
873 l_api_name,
874 G_PKG_NAME,
875 'Okl_Api.G_RET_STS_UNEXP_ERROR',
876 x_msg_count,
877 x_msg_data,
878 '_PVT'
879 );
880 WHEN OTHERS THEN
881 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'Error (Others) => '||SQLERRM);
882 x_return_status :=Okl_Api.HANDLE_EXCEPTIONS
883 (
884 l_api_name,
885 G_PKG_NAME,
886 'OTHERS',
887 x_msg_count,
888 x_msg_data,
889 '_PVT'
890 );
891 END billing_status;
892
893 END OKL_BILL_STATUS_PVT;