[Home] [Help]
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 --
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
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
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
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 ...
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
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
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
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
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
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,
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