[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 distinct oklh.id khr_id,
okch.contract_number,
iex_wf_ban_status_pub.get_turninvoff(hca.party_id,hca.cust_account_id,SYSDATE) bkrpcy_sts
FROM okl_k_headers oklh,
okc_k_headers_b okch,
okc_statuses_b khs,
okl_streams stm,
hz_cust_accounts hca
WHERE oklh.id = okch.id
AND okch.contract_number = NVL (p_contract_number, okch.contract_number)
AND okch.cust_acct_id = hca.cust_account_id
AND hca.status = 'A'
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
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,
iex_wf_ban_status_pub.get_turninvoff(hca.party_id,hca.cust_account_id,SYSDATE);
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);
SELECT kle.name,
kle.id,
kle.fee_type,
ls.lty_code,
kle.sts_code
FROM OKL_K_LINES_FULL_V kle,
OKC_LINE_STYLES_B ls,
OKC_STATUSES_B sts
WHERE kle.lse_id = ls.id
AND ls.lty_code = 'FREE_FORM1'
AND kle.dnz_chr_id = p_chr_id
AND sts.code = kle.sts_code
AND sts.ste_code not in ( 'HOLD', 'EXPIRED', 'CANCELLED', 'TERMINATED');
SELECT kle.name,
kle.id,
kle.fee_type
FROM OKL_K_LINES_FULL_V kle,
OKC_LINE_STYLES_B ls,
OKC_STATUSES_B sts
WHERE kle.lse_id = ls.id
AND ls.lty_code = 'FEE'
AND kle.dnz_chr_id = p_chr_id
AND sts.code = kle.sts_code
AND ((p_kle_id is not null and kle.id = p_kle_id) OR p_kle_id is null)
AND kle.fee_type NOT IN ('FINANCED','ROLLOVER')
AND sts.ste_code not in ( 'HOLD', 'EXPIRED', 'CANCELLED');
select crl.id slh_id,
DECODE(crl.object1_id1,'M',1,'Q',3,'S',6,'A',12) frequency_factor,
FND_DATE.canonical_to_date(crl.RULE_INFORMATION2) start_date,
TO_NUMBER(crl.RULE_INFORMATION3) periods,
crl.RULE_INFORMATION5 structure,
TO_NUMBER(crl.RULE_INFORMATION7) stub_days,
crl.RULE_INFORMATION10 advance_arrears
from OKC_RULE_GROUPS_B crg,
OKC_RULES_B crl
where crl.object2_id1 = p_shid
and crl.rgp_id = crg.id
and crg.RGD_CODE = p_rgcode
and crl.RULE_INFORMATION_CATEGORY = p_rlcat
and crg.dnz_chr_id = p_chrId
and nvl(crg.cle_id,-1) = nvl(p_cleId, -1)
and FND_DATE.canonical_to_date(crl.rule_information2) = (select max(FND_DATE.canonical_to_date(crl.RULE_INFORMATION2))
from okc_rules_b crl2
where crl2.rgp_id = crl.rgp_id
and crl2.RULE_INFORMATION_CATEGORY = crl.RULE_INFORMATION_CATEGORY);
select crl.id slh_id,
crl.object1_id1,
crl.RULE_INFORMATION1,
crl.RULE_INFORMATION2,
crl.RULE_INFORMATION3,
crl.RULE_INFORMATION5,
crl.RULE_INFORMATION6,
crl.RULE_INFORMATION7,
crl.RULE_INFORMATION8,
crl.RULE_INFORMATION10
from OKC_RULE_GROUPS_B crg,
OKC_RULES_B crl,
OKL_STRM_TYPE_B STY
where crl.rgp_id = crg.id
and crg.RGD_CODE = p_rgcode
and crl.RULE_INFORMATION_CATEGORY = p_rlcat
and crg.dnz_chr_id = p_chrId
and nvl(crg.cle_id,-1) = p_cleId
and crl.object1_id1 = sty.id
and sty.stream_type_purpose = 'RENT'
order by crl.RULE_INFORMATION1;
select crl.id slh_id,
crl.object1_id1,
crl.RULE_INFORMATION1,
crl.RULE_INFORMATION2,
crl.RULE_INFORMATION3,
crl.RULE_INFORMATION5,
crl.RULE_INFORMATION6,
crl.RULE_INFORMATION7,
crl.RULE_INFORMATION8,
crl.RULE_INFORMATION10
from OKC_RULE_GROUPS_B crg,
OKC_RULES_B crl
where crl.rgp_id = crg.id
and crg.RGD_CODE = rgcode
and crl.RULE_INFORMATION_CATEGORY = rlcat
and crg.dnz_chr_id = chrId
and nvl(crg.cle_id,-1) = cleId
order by crl.RULE_INFORMATION1;
SELECT tst.isg_arrears_pay_dates_option
FROM okl_st_gen_tmpt_sets tst,
okl_ae_tmpt_sets aes,
okl_products pdt
WHERE pdt.id = p_pdt_id
AND aes.id = pdt.aes_id
AND tst.id = aes.gts_id;
SELECT pdt.id pid,
NVL(pdt.reporting_pdt_id, -1) report_pdt_id
FROM okc_k_headers_v chr,
okl_k_headers khr,
okl_products_v pdt
WHERE khr.id = chr.id
AND chr.id = p_chrId
AND khr.pdt_id = pdt.id(+);
select nvl(rule_information1, 'N')
into l_evergreen_eligible
from okc_rules_b
where rule_information_category = 'LAEVEL'
and dnz_chr_id = p_chr_id
and not exists (select '1'
from okl_k_headers
where deal_type in ('LOAN', 'LOAN-REVOLVING')
and id = p_chr_id);
t_sll_rec_tbl.delete;
t_fee_st_date_tbl.delete;
t_fee_end_date_tbl.delete;