[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select max(AR.DUE_DATE) last_bill_date, tai.description transaction_type
-- abindal bug 4529600 start --
from okl_cnsld_ar_strms_b cnsld,
AR_PAYMENT_SCHEDULES_ALL AR,
OKL_XTL_SELL_INVS_B XTL,
okl_trx_ar_invoices_tl tai,
okl_txl_ar_inv_lns_b til,
okl_txd_ar_ln_dtls_b tld
-- abindal bug 4529600 end --
where cnsld.receivables_invoice_id = AR.customer_trx_id
and cnsld.khr_id = c_khr_id
and cnsld.id = XTL.lsm_id
and xtl.tld_id = tld.id
and til.tai_id = tai.id
and til.id = tld.til_id_details
and tai.description in ('Regular Stream Billing')
and cnsld.sel_id in (SELECT SEL.id
-- abindal bug 4529600 start --
FROM OKL_STREAMS STM,
OKL_STRM_ELEMENTS SEL,
OKC_K_HEADERS_B KHR,
OKL_STRM_TYPE_B STY
-- abindal bug 4529600 end --
WHERE KHR.id = c_khr_id
AND SEL.stream_element_date <= c_transaction_date
AND KHR.id = STM.khr_id
AND STM.id = SEL.stm_id
AND STM.say_code = 'CURR'
AND STM.active_yn = 'Y'
AND STM.sty_id = STY.id
AND NVL(STY.billable_yn,'N') = 'Y'
AND STY.stream_type_purpose IN ('RENT', 'INTEREST_PAYMENT',
'PRINCIPAL_PAYMENT', 'LOAN_PAYMENT',
'FLOAT_FACTOR_ADJUSTMENT')
AND SEL.amount > 0)
group by tai.description;
SELECT MAX(SEL.stream_element_date) last_bill_date
FROM OKL_STREAMS STM,
OKL_STRM_ELEMENTS SEL,
OKC_K_HEADERS_B KHR,
OKL_STRM_TYPE_V STY
WHERE KHR.id = c_khr_id
AND SEL.stream_element_date <=c_transaction_date
AND KHR.id = STM.khr_id
AND STM.id = SEL.stm_id
AND STM.say_code = 'CURR'
AND STM.active_yn = 'Y'
AND STM.sty_id = STY.id
AND STY.billable_yn = 'Y'
-- AND STY.name = 'RENT'
AND STY.STREAM_TYPE_PURPOSE IN ('RENT', 'INTEREST_PAYMENT',
'PRINCIPAL_PAYMENT', 'LOAN_PAYMENT',
'FLOAT_FACTOR_ADJUSTMENT')
AND SEL.amount > 0
AND SEL.date_billed is not null;
SELECT --SEL.stream_element_date last_bill_date
MAX(SEL.date_billed) last_bill_date
FROM OKL_STREAMS_V STM,
OKL_STRM_ELEMENTS_V SEL,
OKC_K_HEADERS_V KHR,
OKL_STRM_TYPE_V STY
WHERE KHR.id = c_khr_id
AND SEL.stream_element_date <=c_transaction_date
AND KHR.id = STM.khr_id
AND STM.id = SEL.stm_id
AND STM.say_code = 'CURR'
AND STM.active_yn = 'Y'
AND STM.sty_id = STY.id
AND NVL(STY.billable_yn,'N') = 'Y'
-- gkhuntet added for Forward port Bug #5488905 Start
AND STY.STREAM_TYPE_PURPOSE IN ('RENT', 'INTEREST_PAYMENT',
'PRINCIPAL_PAYMENT', 'LOAN_PAYMENT',
'FLOAT_FACTOR_ADJUSTMENT');
SELECT ORIG_SYSTEM_SOURCE_CODE
FROM OKC_K_HEADERS_B
WHERE id = cp_khr_id;
select AR.DUE_DATE last_bill_date
from okl_cnsld_ar_strms_b cnsld,
AR_PAYMENT_SCHEDULES_ALL AR,
OKL_XTL_SELL_INVS_V XTL,
okl_trx_ar_invoices_v tai,
okl_txl_ar_inv_lns_v til,
okl_txd_ar_ln_dtls_v tld
where cnsld.receivables_invoice_id = AR.customer_trx_id
and cnsld.khr_id = c_khr_id
and cnsld.id = XTL.lsm_id
and xtl.tld_id = tld.id
and til.tai_id = tai.id
and til.id = tld.til_id_details
and tai.description in ('Regular Stream Billing')
and cnsld.sel_id = c_sel_id;
select AR.DUE_DATE last_bill_date
from --okl_cnsld_ar_strms_b cnsld,
AR_PAYMENT_SCHEDULES_ALL AR,
--OKL_XTL_SELL_INVS_V XTL,
--okl_trx_ar_invoices_v tai,
--okl_txl_ar_inv_lns_v til,
--okl_txd_ar_ln_dtls_v tld
okl_bpd_tld_ar_lines_v RACTRL
where RACTRL.customer_trx_id = AR.customer_trx_id
and RACTRL.khr_id = c_khr_id
--and cnsld.id = XTL.lsm_id
--and xtl.tld_id = tld.id
--and til.tai_id = tai.id
--and til.id = tld.til_id_details
and RACTRL.description in ('Regular Stream Billing')
and RACTRL.sel_id = c_sel_id;
SELECT distinct STM.LINK_HIST_STREAM_ID
--Bug 5050707:end
FROM OKL_STREAMS_V STM,
OKL_STRM_ELEMENTS_V SEL,
OKC_K_HEADERS_V KHR,
OKL_STRM_TYPE_V STY
WHERE KHR.id = c_khr_id
AND SEL.stream_element_date <= c_transaction_date
AND STM.id = SEL.stm_id
AND KHR.id = STM.khr_id
AND STM.say_code = 'CURR'
AND STM.active_yn = 'Y'
AND STM.sty_id = STY.id
AND NVL(STY.billable_yn,'N') = 'Y'
AND STY.stream_type_purpose IN ('RENT', 'INTEREST_PAYMENT',
'PRINCIPAL_PAYMENT', 'LOAN_PAYMENT',
'FLOAT_FACTOR_ADJUSTMENT')
AND SEL.amount > 0;
SELECT LINK_HIST_STREAM_ID
FROM OKL_STREAMS_V
WHERE ID = c_sel_id;
SELECT distinct(SEL.ID)
FROM OKL_STREAMS_V STM,
OKL_STRM_ELEMENTS_V SEL,
OKC_K_HEADERS_V KHR,
OKL_STRM_TYPE_V STY,
okl_cnsld_ar_strms_b cnsld
WHERE KHR.id = c_khr_id
AND SEL.stream_element_date <= c_transaction_date
AND STM.id = SEL.stm_id
AND KHR.id = STM.khr_id
AND STM.say_code = 'HIST'
AND STM.active_yn = 'N'
AND STM.sty_id = STY.id
AND NVL(STY.billable_yn,'N') = 'Y'
AND STY.stream_type_purpose IN ('RENT', 'INTEREST_PAYMENT',
'PRINCIPAL_PAYMENT', 'LOAN_PAYMENT',
'FLOAT_FACTOR_ADJUSTMENT')
AND SEL.amount > 0
AND SEL.stm_id = c_stm_id
and cnsld.sel_id = sel.id
and cnsld.khr_id = c_khr_id;
SELECT distinct(SEL.ID)
FROM OKL_STREAMS_V STM,
OKL_STRM_ELEMENTS_V SEL,
OKC_K_HEADERS_V KHR,
OKL_STRM_TYPE_V STY,
--okl_cnsld_ar_strms_b cnsld
okl_txd_ar_ln_dtls_b tld
WHERE KHR.id = c_khr_id
AND SEL.stream_element_date <= c_transaction_date
AND STM.id = SEL.stm_id
AND KHR.id = STM.khr_id
AND STM.say_code = 'HIST'
AND STM.active_yn = 'N'
AND STM.sty_id = STY.id
AND NVL(STY.billable_yn,'N') = 'Y'
AND STY.stream_type_purpose IN ('RENT', 'INTEREST_PAYMENT',
'PRINCIPAL_PAYMENT', 'LOAN_PAYMENT',
'FLOAT_FACTOR_ADJUSTMENT')
AND SEL.amount > 0
AND SEL.stm_id = c_stm_id
and tld.sel_id = sel.id
and tld.khr_id = c_khr_id;
SELECT id, lse_id
FROM okc_k_lines_b
WHERE dnz_chr_id = c_khr_id
AND orig_system_source_code <> 'OKL_SPLIT';
SELECT max(AR.DUE_DATE) last_bill_date--, tai.description transaction_type
FROM okl_cnsld_ar_strms_b cnsld,
AR_PAYMENT_SCHEDULES_ALL AR,
OKL_XTL_SELL_INVS_V XTL,
okl_trx_ar_invoices_v tai,
okl_txl_ar_inv_lns_v til,
okl_txd_ar_ln_dtls_v tld
WHERE cnsld.receivables_invoice_id = AR.customer_trx_id
AND cnsld.khr_id = c_khr_id
AND cnsld.id = XTL.lsm_id
AND xtl.tld_id = tld.id
AND til.tai_id = tai.id
AND til.id = tld.til_id_details
AND tai.description IN ('Regular Stream Billing')
AND EXISTS ( SELECT '1'
FROM OKL_STREAMS STM,
OKL_STRM_ELEMENTS SEL,
-- OKC_K_LINES_B KLE,
OKL_STRM_TYPE_B STY
WHERE SEL.ID = cnsld.SEL_ID
AND SEL.stream_element_date <= TRUNC(c_transaction_date)
-- AND KLE.ID = STM.KLE_ID
AND STM.KHR_ID = c_khr_id
AND STM.id = SEL.stm_id
-- AND STM.say_code = 'CURR'
-- AND STM.active_yn = 'Y'
AND STM.sty_id = STY.id
AND NVL(STY.billable_yn,'N') = 'Y'
AND STY.stream_type_purpose IN ('RENT', 'INTEREST_PAYMENT',
'PRINCIPAL_PAYMENT', 'LOAN_PAYMENT',
'FLOAT_FACTOR_ADJUSTMENT')
AND SEL.amount > 0
AND STM.KLE_ID IN ( SELECT kle.id
FROM okc_k_lines_b kle
WHERE kle.dnz_chr_id = c_khr_id
CONNECT BY PRIOR kle.orig_system_id1 = kle.id
START WITH kle.id = c_kle_id ))
GROUP BY tai.description;
SELECT max(AR.DUE_DATE) last_bill_date--, tai.description transaction_type
FROM --okl_cnsld_ar_strms_b cnsld,
AR_PAYMENT_SCHEDULES_ALL AR,
--OKL_XTL_SELL_INVS_V XTL,
--okl_trx_ar_invoices_v tai,
--okl_txl_ar_inv_lns_v til,
--okl_txd_ar_ln_dtls_v tld
okl_bpd_tld_ar_lines_v RACTRL
WHERE RACTRL.CUSTOMER_TRX_ID = AR.customer_trx_id
AND RACTRL.khr_id = c_khr_id
--AND cnsld.id = XTL.lsm_id
--AND xtl.tld_id = tld.id
--AND til.tai_id = tai.id
--AND til.id = tld.til_id_details
AND RACTRL.description IN ('Regular Stream Billing')
AND EXISTS ( SELECT '1'
FROM OKL_STREAMS STM,
OKL_STRM_ELEMENTS SEL,
-- OKC_K_LINES_B KLE,
OKL_STRM_TYPE_B STY
WHERE SEL.ID = RACTRL.SEL_ID
AND SEL.stream_element_date <= TRUNC(c_transaction_date)
-- AND KLE.ID = STM.KLE_ID
AND STM.KHR_ID = c_khr_id
AND STM.id = SEL.stm_id
-- AND STM.say_code = 'CURR'
-- AND STM.active_yn = 'Y'
AND STM.sty_id = STY.id
AND NVL(STY.billable_yn,'N') = 'Y'
AND STY.stream_type_purpose IN ('RENT', 'INTEREST_PAYMENT',
'PRINCIPAL_PAYMENT', 'LOAN_PAYMENT',
'FLOAT_FACTOR_ADJUSTMENT')
AND SEL.amount > 0
AND STM.KLE_ID IN ( SELECT kle.id
FROM okc_k_lines_b kle
WHERE kle.dnz_chr_id = c_khr_id
CONNECT BY PRIOR kle.orig_system_id1 = kle.id
START WITH kle.id = c_kle_id ))
GROUP BY RACTRL.description;
SELECT max(SEL.stream_element_date) last_sche_bill_date, sel.id
FROM OKL_STREAMS_V STM,
OKL_STRM_ELEMENTS_V SEL,
OKC_K_HEADERS_V KHR,
OKL_STRM_TYPE_B STY
WHERE KHR.id = c_khr_id
AND SEL.stream_element_date <= c_transaction_date
AND KHR.id = STM.khr_id
AND STM.id = SEL.stm_id
AND STM.say_code = 'CURR'
AND STM.active_yn = 'Y'
AND SEL.date_billed IS NULL
AND STM.sty_id = STY.id
AND NVL(STY.billable_yn,'N') = 'Y'
AND SEL.amount > 0
AND ROWNUM < 2;
SELECT sel.id stream_id,
sel.stream_element_date last_sche_bill_date
FROM OKL_STREAMS_V STM,
OKL_STRM_ELEMENTS_V SEL,
OKL_STRM_TYPE_V STY
WHERE sel.stream_element_date = (SELECT max(SEL.stream_element_date) last_sche_bill_date
FROM OKL_STREAMS_V STM,
OKL_STRM_ELEMENTS_V SEL,
OKC_K_HEADERS_V KHR,
OKL_STRM_TYPE_V STY
WHERE KHR.id = c_khr_id
AND SEL.stream_element_date <= c_transaction_date
AND KHR.id = STM.khr_id
AND STM.id = SEL.stm_id
AND STM.say_code = 'CURR'
AND STM.active_yn = 'Y'
AND STM.sty_id = STY.id
AND NVL(STY.billable_yn,'N') = 'Y'
AND STY.stream_type_purpose IN ('RENT', 'INTEREST_PAYMENT',
'PRINCIPAL_PAYMENT', 'LOAN_PAYMENT',
'FLOAT_FACTOR_ADJUSTMENT')
AND SEL.amount > 0)
AND STM.id = SEL.stm_id
AND STM.sty_id = STY.id
AND STY.stream_type_purpose IN ('RENT', 'INTEREST_PAYMENT',
'PRINCIPAL_PAYMENT', 'LOAN_PAYMENT',
'FLOAT_FACTOR_ADJUSTMENT')
AND STM.khr_id = c_khr_id
AND STM.say_code = 'CURR'
AND STM.active_yn = 'Y'
AND NVL(STY.billable_yn,'N') = 'Y'
AND ROWNUM < 2;
select max(schd.date_to_interface) last_sche_bill_date
from okc_k_rel_objs rel,
okc_k_headers_b hdr,
okc_k_headers_b oks,
okc_k_lines_b oks_line,
OKS_LEVEL_ELEMENTS_V schd, OKS_STREAM_LEVELS_B strm
where hdr.id = c_khr_id
and rty_code = 'OKLSRV'
and rel.jtot_object1_code = 'OKL_SERVICE'
and rel.cle_id is null
and rel.chr_id = hdr.id
and rel.object1_id1 = oks.id
and oks.id = oks_line.dnz_chr_id
and oks_line.lse_id in (7,8,9,10,11,35)
and oks_line.id = strm.cle_id
and strm.id = schd.rul_id
and schd.date_to_interface <= c_transaction_date;
select max(schd.date_to_interface) last_sche_bill_date
from okc_k_rel_objs rel,
okc_k_headers_b hdr,
okc_k_headers_b oks,
okc_k_lines_b oks_line,
OKS_LEVEL_ELEMENTS_V schd,
okc_rules_b rules,
okc_rule_groups_b rgp
where hdr.id = c_khr_id
and rty_code = 'OKLSRV'
and rel.jtot_object1_code = 'OKL_SERVICE'
and rel.cle_id is null
and rel.chr_id = hdr.id
and rel.object1_id1 = oks.id
and oks.id = oks_line.dnz_chr_id
and oks_line.lse_id in (7,8,9,10,11,35)
and oks_line.id = rgp.cle_id
and rules.rgp_id = rgp.id
and rules.id = schd.rul_id
and rules.rule_information_category = 'SLL'
and schd.date_to_interface <= c_transaction_date;
select max(AR.DUE_DATE) last_bill_date, tai.description transaction_type
from okl_cnsld_ar_strms_b cnsld,
AR_PAYMENT_SCHEDULES_ALL AR,
OKL_XTL_SELL_INVS_V XTL,
okl_trx_ar_invoices_v tai,
okl_txl_ar_inv_lns_v til,
okl_txd_ar_ln_dtls_v tld
where cnsld.receivables_invoice_id = AR.customer_trx_id
and cnsld.khr_id = c_khr_id
and cnsld.id = XTL.lsm_id
and xtl.tld_id = tld.id
and til.tai_id = tai.id
and til.id = tld.til_id_details
and tai.description in ('OKS Billing')
AND tai.date_invoiced <= c_transaction_date
group by tai.description;
select max(AR.DUE_DATE) last_bill_date, RACTRL.description transaction_type
from --okl_cnsld_ar_strms_b cnsld,
AR_PAYMENT_SCHEDULES_ALL AR,
--OKL_XTL_SELL_INVS_V XTL,
--okl_trx_ar_invoices_v tai,
--okl_txl_ar_inv_lns_v til,
--okl_txd_ar_ln_dtls_v tld
okl_bpd_tld_ar_lines_v RACTRL
where RACTRL.CUSTOMER_TRX_id = AR.customer_trx_id
and RACTRL.khr_id = c_khr_id
--and cnsld.id = XTL.lsm_id
--and xtl.tld_id = tld.id
--and til.tai_id = tai.id
--and til.id = tld.til_id_details
and RACTRL.description in ('OKS Billing')
AND RACTRL.date_invoiced <= c_transaction_date
group by RACTRL.description;
SELECT 1
FROM okc_class_operations
WHERE cls_code = 'SERVICE'
AND opn_code = 'CHECK_RULE';