[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT oklh.id khr_id,
okch.contract_number,
nvl(stm.kle_id, -99) kle_id
FROM okl_k_headers oklh,
okc_k_headers_b okch,
okc_statuses_b khs,
okl_streams stm
WHERE oklh.id = okch.id
AND okch.contract_number = NVL (p_contract_number, okch.contract_number)
AND okch.scs_code IN ('LEASE', 'LOAN')
AND okch.sts_code = 'EVERGREEN'
AND khs.code = okch.sts_code
AND oklh.id = stm.khr_id
AND EXISTS (SELECT 1 FROM okl_strm_type_v sty
WHERE stm.sty_id = sty.id
--change for User Defined Streams, by pjgomes, on 18 Oct 2004
--AND sty.name IN ('RENT', 'SERVICE AND MAINTENANCE', 'ESTIMATED PERSONAL PROPERTY TAX'))
AND sty.stream_type_purpose IN ('RENT', 'SERVICE_PAYMENT', 'ESTIMATED_PROPERTY_TAX', 'FEE_PAYMENT'))
AND (stm.kle_id is not null and EXISTS (SELECT 1 FROM OKC_K_LINES_B CLE
WHERE cle.dnz_chr_id = oklh.id
AND cle.id = stm.kle_id
AND cle.sts_code = 'EVERGREEN') OR stm.kle_id IS NULL)
GROUP BY oklh.id,
okch.contract_number,
nvl(stm.kle_id, -99);
SELECT khr.contract_number contract_number,
stm.kle_id, --added by pgomes
stm.id stm_id,
sty.stream_type_purpose sty_name
FROM okl_k_headers_full_v khr,
okl_streams stm,
okl_strm_type_v sty
WHERE khr.id = p_khr_id
AND stm.khr_id = khr.id
AND NVL(stm.kle_id, -99) = NVL(p_kle_id, -99)
AND stm.sty_id = sty.id
--change for User Defined Streams, by pjgomes, on 18 Oct 2004
--AND sty.name IN ('SERVICE AND MAINTENANCE EVERGREEN', 'EVERGREEN RENT', 'ESTIMATED PERSONAL PROPERTY TAX EVERGREEN')
AND sty.stream_type_purpose IN ('SERVICE_RENEWAL', 'RENEWAL_RENT', 'RENEWAL_PROPERTY_TAX', 'FEE_RENEWAL')
AND stm.say_code = 'CURR'
AND stm.active_yn = 'Y';
SELECT count(*)
FROM okl_streams stm,
okl_strm_type_v sty
WHERE stm.khr_id = p_khr_id
AND NVL(stm.kle_id, -99) = NVL(p_kle_id, -99)
AND stm.sty_id = sty.id
--change for User Defined Streams, by pjgomes, on 18 Oct 2004
--AND sty.name IN ('SERVICE AND MAINTENANCE EVERGREEN', 'EVERGREEN RENT', 'ESTIMATED PERSONAL PROPERTY TAX EVERGREEN')
AND sty.stream_type_purpose IN ('SERVICE_RENEWAL', 'RENEWAL_RENT', 'RENEWAL_PROPERTY_TAX', 'FEE_RENEWAL')
AND stm.say_code = 'CURR'
AND stm.active_yn = 'Y';
SELECT count(*)
FROM okl_streams stm,
okl_strm_type_v sty
WHERE stm.khr_id = p_khr_id
AND NVL(stm.kle_id, -99) = NVL(p_kle_id, -99)
AND stm.sty_id = sty.id
AND stm.say_code = 'CURR'
AND stm.active_yn = 'Y'
AND sty.stream_type_purpose = p_sty_purpose;
SELECT stm.kle_id, stm.say_code, stm.active_yn
FROM okl_streams stm,
okl_strm_type_v sty
WHERE stm.khr_id = p_khr_id
AND stm.sty_id = sty.id
AND stm.say_code = 'CURR'
AND stm.active_yn = 'Y'
AND sty.name IN ('RENT');*/
SELECT id
FROM okl_strm_type_v
WHERE stream_type_purpose = p_sty_purpose; */
SELECT stm.sty_id
FROM okl_streams stm,
okl_strm_type_v sty
WHERE stm.khr_id = p_khr_id
AND NVL(stm.kle_id, -99) = NVL(p_kle_id, -99)
AND stm.sty_id = sty.id
AND stm.say_code = 'CURR'
AND stm.active_yn = 'Y'
AND sty.stream_type_purpose = p_sty_purpose;
SELECT stm.kle_id
FROM okl_streams stm,
okl_strm_type_v sty
WHERE stm.khr_id = p_khr_id
AND stm.sty_id = sty.id
AND stm.say_code = 'CURR'
AND stm.active_yn = 'Y'
AND sty.name = p_sty_name; */
SELECT TRUNC(MAX( ste.STREAM_ELEMENT_DATE )) upper_stream_date
FROM okl_strm_elements ste
WHERE ste.stm_id IN (
SELECT stm.id
FROM okl_streams stm,
okl_strm_type_v sty
WHERE stm.khr_id = p_khr_id
AND NVL(stm.kle_id, -99) = NVL(p_kle_id, -99)
AND stm.sty_id = sty.id
AND stm.say_code = 'CURR'
AND stm.active_yn = 'Y'
AND sty.stream_type_purpose = p_sty_purpose);
SELECT TRUNC(MAX( ste.STREAM_ELEMENT_DATE )) lower_stream_date
FROM okl_strm_elements ste
WHERE ste.stream_element_date <= p_max_date
AND ste.stm_id IN (
SELECT stm.id
FROM okl_streams stm,
okl_strm_type_v sty
WHERE stm.khr_id = p_khr_id
AND NVL(stm.kle_id, -99) = NVL(p_kle_id, -99)
AND stm.sty_id = sty.id
AND stm.say_code = 'CURR'
AND stm.active_yn = 'Y'
AND sty.stream_type_purpose = p_sty_purpose);
SELECT ste.amount
FROM okl_strm_elements ste,
(
SELECT stm.id, to_number(rule_information6) amt
FROM okc_rules_b a,
okc_rule_groups_b b,
okl_streams stm,
okl_strm_type_v sty
WHERE a.dnz_chr_id = p_khr_id
AND a.rgp_id = b.id
AND b.rgd_code = 'LALEVL'
AND a.rule_information_category = 'LASLL'
AND stm.kle_id = b.cle_id
AND NVL(stm.kle_id, -99) = NVL(p_kle_id, -99)
AND stm.sty_id = sty.id
AND stm.say_code = 'CURR'
AND stm.active_yn = 'Y'
AND sty.stream_type_purpose = p_sty_purpose
AND rule_information6 IS NOT NULL
) strules
WHERE ste.stm_id = strules.id
AND ste.amount = strules.amt
AND ste.date_billed IS NOT NULL
ORDER BY ste.stream_element_date DESC;
SELECT MIN (ste.amount) amount
FROM okl_strm_elements ste
WHERE ste.stm_id IN (
SELECT stm.id
FROM okl_streams stm,
okl_strm_type_v sty
WHERE stm.khr_id = p_khr_id
AND NVL(stm.kle_id, -99) = NVL(p_kle_id, -99)
AND stm.sty_id = sty.id
AND stm.say_code = 'CURR'
AND stm.active_yn = 'Y'
AND sty.stream_type_purpose = p_sty_purpose);
SELECT MAX( ste.STREAM_ELEMENT_DATE ) evergreen_element_date
FROM okl_strm_elements ste
WHERE ste.stm_id IN (
SELECT stm.id
FROM okl_streams stm,
okl_strm_type_v sty
WHERE stm.khr_id = p_khr_id
AND NVL(stm.kle_id, -99) = NVL(p_kle_id, -99)
AND stm.sty_id = sty.id
AND stm.say_code = 'CURR'
AND stm.active_yn = 'Y'
AND sty.stream_type_purpose = p_sty_purpose);
SELECT okl_sif_seq.nextval
FROM dual;
SELECT object1_id1
FROM OKC_RULES_B rul,
Okc_rule_groups_B rgp
WHERE rul.rgp_id = rgp.id AND
rgp.rgd_code = 'LALEVL' AND
rgp.chr_id IS NULL AND
rul.rule_information_category = 'LASLL' AND
rgp.dnz_chr_id = p_khr_id;
SELECT max(se_line_number)
FROM okl_strm_elements
WHERE stm_id = p_stm_id;
SELECT count(*)
FROM okl_strm_elements
WHERE stm_id = p_stm_id
AND trunc(STREAM_ELEMENT_DATE) = trunc(p_sel_date);
SELECT count(*)
FROM okl_strm_elements
WHERE stm_id = p_stm_id
AND trunc(STREAM_ELEMENT_DATE) <= trunc(p_sel_date)
AND date_billed is NULL;
SELECT * FROM (
SELECT ste.id
,ste.stream_element_date
,stm.khr_id
,stm.kle_id
,stm.sty_id
FROM okl_strm_elements_v ste
,okl_streams_v stm
,okl_strm_type_v sty
WHERE ste.stm_id = stm.id
AND stm.khr_id = p_khr_id
AND NVL(stm.kle_id, -99) = NVL(p_kle_id, -99)
AND stm.sty_id = sty.id
AND sty.stream_type_purpose = p_sty_purpose
ORDER BY ste.stream_element_date DESC
)
WHERE ROWNUM = 1;
select distinct khr.id khr_id
from okl_pool_contents_v pol
, OKL_POOLS pool
,okl_k_headers_full_v khr
where pol.khr_id = p_khr_id
and nvl(pol.kle_id, -99) = nvl(p_kle_id, -99)
and pol.sty_id = p_sty_id
and trunc(p_stream_element_date) between trunc(pol.streams_from_date) and trunc(pol.streams_to_date)
and pol.pol_id = pool.id
and pool.khr_id = khr.id
AND pol.status_code = Okl_Pool_Pvt.G_POC_STS_ACTIVE; --Added by VARANGAN -Pool Contents Impact(Bug#6658065)
SELECT stm.id
FROM okl_streams_v stm
WHERE stm.khr_id = cp_khr_id
AND nvl(stm.kle_id, -99) = nvl(cp_kle_id, -99)
AND stm.sty_id = cp_sty_id
AND stm.say_code = 'CURR'
AND stm.active_yn = 'Y';
SELECT max(se_line_number) se_line_number
FROM okl_strm_elements_v
WHERE stm_id = cp_stm_id;
SELECT 'Y'
FROM dual
WHERE EXISTS(
SELECT 1
FROM OKL_POOLS pool,
okl_pool_contents_v poc,
okl_strm_type_v sty
WHERE pool.khr_id = p_khr_id AND
pool.id = poc.pol_id AND
poc.sty_id = sty.id AND
sty.stream_type_purpose = 'RESIDUAL_VALUE'
AND poc.status_code = Okl_Pool_Pvt.G_POC_STS_ACTIVE ); --Added by VARANGAN -Pool Contents Impact(Bug#6658065)
-- Check and insert Evergreen Rent record
l_evergreen_rent_count := 0;
-- Check and insert Evergreen Service and Maintenance record
l_evergreen_sm_count := 0;
-- Check and insert Estimated Personal Property Tax Evergreen record
l_evergreen_ept_count := 0;
Okl_Sel_Pvt.insert_row(
p_api_version,
p_init_msg_list,
x_return_status,
x_msg_count,
x_msg_data,
p_selv_rec,
x_selv_rec);
Okl_Sel_Pvt.insert_row(
p_api_version,
p_init_msg_list,
x_return_status,
x_msg_count,
x_msg_data,
l_selv_rec,
lx_selv_rec);