[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT DISTINCT opc.khr_id khr_id,
opc.streams_to_date end_date,
khr.deal_type deal_type
FROM okl_pool_contents opc,
okl_pools op,
okl_k_headers khr,
-- Changed for User Defined Streams
--okl_strm_type_tl stytl
okl_strm_type_b stytl
WHERE op.khr_id = p_inv_id
AND opc.khr_id = p_khr_id
AND op.id = opc.pol_id
AND opc.khr_id = khr.id
AND opc.sty_id = stytl.id
--AND stytl.name = 'RENT'
AND stytl.stream_type_purpose IN ('RENT', 'PRINCIPAL_PAYMENT')
AND opc.status_code IN (Okl_Pool_Pvt.G_POC_STS_ACTIVE, Okl_Pool_Pvt.G_POC_STS_NEW);
SELECT opc.kle_id kle_id,
MIN(opc.streams_from_date) start_date
FROM okl_pool_contents opc,
okl_strm_type_b sty
WHERE opc.khr_id = p_khr_id
AND opc.sty_id = sty.id
--AND sty.code = 'RENT'
AND sty.stream_type_purpose IN ('RENT', 'PRINCIPAL_PAYMENT')
AND opc.status_code IN (Okl_Pool_Pvt.G_POC_STS_ACTIVE, Okl_Pool_Pvt.G_POC_STS_NEW)
AND ROWNUM < 2
GROUP BY opc.kle_id;
SELECT DISTINCT opc.khr_id khr_id,
opc.streams_to_date end_date,
khr.deal_type deal_type
FROM okl_pool_contents opc,
okl_pools op,
okl_k_headers khr,
-- Changed for User Defined Streams
--okl_strm_type_tl stytl
okl_strm_type_b stytl
WHERE op.khr_id = p_inv_id
AND opc.khr_id = p_khr_id
AND op.id = opc.pol_id
AND opc.khr_id = khr.id
AND opc.sty_id = stytl.id
--AND stytl.name = 'RENT'
AND stytl.stream_type_purpose IN ('RENT', 'PRINCIPAL_PAYMENT')
AND opc.status_code = G_PC_STS_PENDING; --Okl_Pool_Pvt.G_POC_STS_PENDING;
SELECT opc.kle_id kle_id,
MIN(opc.streams_from_date) start_date
FROM okl_pool_contents opc,
okl_strm_type_b sty
WHERE opc.khr_id = p_khr_id
AND opc.sty_id = sty.id
--AND sty.code = 'RENT'
AND sty.stream_type_purpose IN ('RENT', 'PRINCIPAL_PAYMENT')
AND opc.status_code = G_PC_STS_PENDING --Okl_Pool_Pvt.G_POC_STS_PENDING
AND ROWNUM < 2
GROUP BY opc.kle_id;
SELECT DISTINCT op.khr_id
FROM okl_pool_contents opc,
okl_pools op,
okl_strm_type_b sty
WHERE op.id = opc.pol_id
AND opc.khr_id = p_khr_id
AND opc.sty_id = sty.id
--AND sty.code = 'RENT';
SELECT DECODE(sll.rule_information10, NULL, 'N', 'Y', 'Y', 'N') arrears_yn,
DECODE(sll.object1_id1, 'A',12,'S',6,'Q',3,'M',1) frequency
FROM okc_k_headers_b k,
okc_rule_groups_b rg,
okc_rules_b slh,
okc_rules_b sll,
okl_strm_type_b strm
WHERE slh.rule_information_category = 'LASLH'
AND slh.rgp_id = rg.id
AND sll.object2_id1 = to_char(slh.id)
AND sll.rgp_id = rg.id
AND slh.object1_id1 = to_char(strm.id)
--AND strm.code = 'RENT'
AND strm.stream_type_purpose IN ('RENT', 'PRINCIPAL_PAYMENT')
AND rg.dnz_chr_id = k.id
AND rg.chr_id IS NULL
AND rg.rgd_code = 'LALEVL'
AND rg.cle_id = p_kle_id
AND k.id = p_khr_id;
SELECT id
FROM okl_strm_type_tl
WHERE name = p_sty_name
AND language = USERENV('LANG');
SELECT kleb_rv.percent_stake percent_stake
FROM okl_k_lines kleb_rv,
okc_k_lines_b cles_rv,
okc_line_styles_b lseb_rv
WHERE
cles_rv.dnz_chr_id = p_inv_id
AND cles_rv.lse_id = lseb_rv.id
AND lseb_rv.lty_code = 'REVENUE_SHARE'
AND kleb_rv.id = cles_rv.id
AND kleb_rv.stream_type_subclass = p_stream_type_subclass;
SELECT contract_number
FROM okc_k_headers_b
WHERE id = p_khr_id
AND scs_code = 'LEASE';
SELECT DISTINCT op.khr_id
FROM okl_pool_contents opc,
okl_pools op,
okl_strm_type_b sty
WHERE op.id = opc.pol_id
AND opc.khr_id = p_khr_id
AND opc.sty_id = sty.id
--AND sty.code = 'RENT';
select id from okl_streams
where khr_id=p_khr_id
and sty_id=p_sty_id
and say_code=p_say_code
and nvl(purpose_code,'XXX') = nvl(p_purpose_code,'XXX') ; --sechawla 9-mar-09 MG Impact on IA : added
select ste.stream_element_date stream_element_date,ste.amount amount
FROM okl_strm_type_b sty, okl_streams stm, okl_strm_elements ste
WHERE
stm.sty_id = sty.id
AND ste.stm_id = stm.id
AND stm.active_yn = 'Y'
AND stm.say_code = 'CURR'
AND stm.khr_id = cp_khr_id
AND ste.stream_element_date BETWEEN cp_final_start_date AND cp_end_date
AND sty.stream_type_purpose = cp_stream_type_purpose
ORDER BY ste.stream_element_date;
select ste.stream_element_date stream_element_date,ste.amount amount
FROM okl_strm_type_b sty, okl_streams stm, okl_strm_elements ste
WHERE
stm.sty_id = sty.id
AND ste.stm_id = stm.id
AND stm.active_yn = 'Y'
AND stm.say_code = 'CURR'
AND stm.khr_id = cp_khr_id
AND sty.stream_type_purpose = cp_stream_type_purpose
and exists (
select '1'
FROM okl_strm_type_b sty1, okl_streams stm1, okl_strm_elements ste1
where stm1.sty_id = sty1.id
AND ste1.stm_id = stm1.id
AND stm1.active_yn = 'Y'
AND stm1.say_code = 'CURR'
AND stm1.khr_id = cp_khr_id
and sty1.id = p_sty_id
and ste1.stream_element_date = ste.stream_element_date
)
ORDER BY ste.stream_element_date;
select ste.stream_element_date stream_element_date,ste.amount amount
FROM okl_strm_type_b sty, okl_streams stm, okl_strm_elements ste
WHERE
stm.sty_id = sty.id
AND ste.stm_id = stm.id
AND stm.active_yn = 'N'
AND stm.say_code = 'CURR'
AND stm.purpose_code = 'REPORT'
AND stm.khr_id = cp_khr_id
AND ste.stream_element_date BETWEEN cp_final_start_date AND cp_end_date
AND sty.stream_type_purpose = cp_stream_type_purpose
ORDER BY ste.stream_element_date;
select ste.stream_element_date stream_element_date,ste.amount amount
FROM okl_strm_type_b sty, okl_streams stm, okl_strm_elements ste
WHERE
stm.sty_id = sty.id
AND ste.stm_id = stm.id
AND stm.active_yn = 'N'
AND stm.say_code = 'CURR'
AND stm.purpose_code = 'REPORT'
AND stm.khr_id = cp_khr_id
AND sty.stream_type_purpose = cp_stream_type_purpose
and exists (
select '1'
FROM okl_strm_type_b sty1, okl_streams stm1, okl_strm_elements ste1
where stm1.sty_id = sty1.id
AND ste1.stm_id = stm1.id
AND stm1.active_yn = 'N'
AND stm1.say_code = 'CURR'
AND stm.purpose_code = 'REPORT'
AND stm1.khr_id = cp_khr_id
and sty1.id = p_rep_sty_id
and ste1.stream_element_date = ste.stream_element_date
)
ORDER BY ste.stream_element_date;
SELECT secondary_rep_method
FROM okl_sys_acct_opts;
/* l_stmt := 'SELECT ste.stream_element_date stream_element_date,
ste.amount amount
FROM okl_strm_type_b sty,
okl_streams stm,
okl_strm_elements ste
WHERE 1 = 1
AND stm.sty_id = sty.id
AND ste.stm_id = stm.id
AND stm.active_yn = '||''''||'Y'||''''||'
AND stm.say_code = '||''''||'CURR'||'''' ;
SELECT okl_sif_seq.NEXTVAL INTO l_trx_number FROM dual;
SELECT okl_sif_seq.NEXTVAL INTO l_trx_number FROM dual;
l_id_tbl.delete;
UPDATE OKL_STREAMS
SET say_code = 'HIST',
active_yn = 'N',
date_history = sysdate,
last_updated_by = FND_GLOBAL.USER_ID, -- BUG:14749215 changes start here
last_update_date = sysdate,
last_update_login = FND_GLOBAL.LOGIN_ID -- BUG:14749215 changes end here
WHERE ID = l_id_tbl(i);
l_rep_id_tbl.delete;
UPDATE OKL_STREAMS
SET say_code = 'HIST',
active_yn = 'N',
date_history = sysdate,
last_updated_by = FND_GLOBAL.USER_ID, -- BUG:14749215 changes start here
last_update_date = sysdate,
last_update_login = FND_GLOBAL.LOGIN_ID -- BUG:14749215 changes end here
WHERE ID = l_rep_id_tbl(i);
l_id_tbl.delete;
UPDATE OKL_STREAMS
SET say_code = 'CURR',
active_yn = 'Y',
date_current = sysdate,
last_updated_by = FND_GLOBAL.USER_ID, -- BUG:14749215 changes start here
last_update_date = sysdate,
last_update_login = FND_GLOBAL.LOGIN_ID -- BUG:14749215 changes end here
WHERE ID = l_id_tbl(i);
l_rep_id_tbl.delete;
UPDATE OKL_STREAMS
SET say_code = 'CURR',
active_yn = 'N',
date_current = sysdate,
last_updated_by = FND_GLOBAL.USER_ID, -- BUG:14749215 changes start here
last_update_date = sysdate,
last_update_login = FND_GLOBAL.LOGIN_ID -- BUG:14749215 changes end here
WHERE ID = l_rep_id_tbl(i);
SELECT DISTINCT opc.khr_id khr_id,
opc.streams_to_date end_date,
khr.deal_type deal_type
FROM OKL_POOL_CONTENTS opc,
OKL_POOLS op,
OKL_K_HEADERS khr,
OKL_STRM_TYPE_B stytl
WHERE op.khr_id = p_inv_id
AND op.id = opc.pol_id
AND opc.khr_id = khr.id
AND opc.sty_id = stytl.id
--AND stytl.code = 'RENT'
AND stytl.stream_type_purpose IN ('RENT', 'PRINCIPAL_PAYMENT')
AND opc.status_code IN (Okl_Pool_Pvt.G_POC_STS_ACTIVE, Okl_Pool_Pvt.G_POC_STS_NEW)
GROUP BY opc.khr_id, opc.streams_from_date, opc.streams_to_date, khr.deal_type;
SELECT opc.kle_id kle_id,
MIN(opc.streams_from_date) start_date
FROM OKL_POOL_CONTENTS opc,
OKL_STRM_TYPE_B sty
WHERE opc.khr_id = p_khr_id
AND opc.sty_id = sty.id
--AND sty.code = 'RENT'
AND sty.stream_type_purpose IN ('RENT', 'PRINCIPAL_PAYMENT')
AND opc.status_code IN (Okl_Pool_Pvt.G_POC_STS_ACTIVE, Okl_Pool_Pvt.G_POC_STS_NEW)
AND ROWNUM < 2
GROUP BY opc.kle_id;
SELECT DISTINCT opc.khr_id khr_id,
opc.streams_to_date end_date,
khr.deal_type deal_type
FROM OKL_POOL_CONTENTS opc,
OKL_POOLS op,
OKL_K_HEADERS khr,
OKL_STRM_TYPE_B stytl
WHERE op.khr_id = p_inv_id
AND op.id = opc.pol_id
AND opc.khr_id = khr.id
AND opc.sty_id = stytl.id
--AND stytl.code = 'RENT'
AND stytl.stream_type_purpose IN ('RENT', 'PRINCIPAL_PAYMENT')
AND opc.status_code = G_PC_STS_PENDING --Okl_Pool_Pvt.G_POC_STS_PENDING
GROUP BY opc.khr_id, opc.streams_from_date, opc.streams_to_date, khr.deal_type;
SELECT opc.kle_id kle_id,
MIN(opc.streams_from_date) start_date
FROM OKL_POOL_CONTENTS opc,
OKL_STRM_TYPE_B sty
WHERE opc.khr_id = p_khr_id
AND opc.sty_id = sty.id
--AND sty.code = 'RENT'
AND sty.stream_type_purpose IN ('RENT', 'PRINCIPAL_PAYMENT')
AND opc.status_code = G_PC_STS_PENDING --Okl_Pool_Pvt.G_POC_STS_PENDING
AND ROWNUM < 2
GROUP BY opc.kle_id;
SELECT decode(sll.rule_information10, NULL, 'N', 'Y', 'Y', 'N') arrears_yn,
decode(sll.object1_id1, 'A',12,'S',6,'Q',3,'M',1) frequency
FROM OKC_K_HEADERS_B K,
OKC_RULE_GROUPS_B RG,
OKC_RULES_B SLH,
OKC_RULES_B SLL,
OKL_STRM_TYPE_B STRM
WHERE slh.rule_information_category = 'LASLH'
AND slh.rgp_id = rg.id
AND sll.object2_id1 = to_char(slh.id)
AND sll.rgp_id = rg.id
AND slh.object1_id1 = to_char(strm.id)
--AND strm.code = 'RENT'
AND strm.stream_type_purpose IN ('RENT', 'PRINCIPAL_PAYMENT')
AND rg.dnz_chr_id = k.id
AND rg.chr_id IS NULL
AND rg.rgd_code = 'LALEVL'
AND rg.cle_id = p_kle_id
AND k.id = p_khr_id;
SELECT id
FROM OKL_STRM_TYPE_B
WHERE code = p_sty_name;
SELECT clet.id id
FROM OKC_K_LINES_B clet
,OKC_LINE_STYLES_B lseb
WHERE clet.dnz_chr_id = p_khr_id
AND clet.lse_id = lseb.id
AND lseb.lty_code = 'INVESTMENT';
SELECT kleb.percent_stake percent_stake
FROM OKL_K_LINES kleb
,OKC_K_LINES_B cles
,OKC_LINE_STYLES_B lseb
WHERE kleb.id = cles.id
AND cles.cle_id = p_tl_id
AND cles.lse_id = lseb.id
AND lseb.lty_code = 'REVENUE_SHARE'
AND kleb.stream_type_subclass = p_stream_type_subclass;
SELECT contract_number,
scs_code
FROM OKC_K_HEADERS_B
WHERE id = p_khr_id;
SELECT secondary_rep_method
FROM okl_sys_acct_opts;
select ste.stream_element_date stream_element_date,ste.amount amount
FROM okl_strm_type_b sty, okl_streams stm, okl_strm_elements ste
WHERE
stm.sty_id = sty.id
AND ste.stm_id = stm.id
AND stm.active_yn = 'Y'
AND stm.say_code = 'CURR'
AND stm.khr_id = p_khr_id
AND ste.stream_element_date BETWEEN p_final_start_date AND p_end_date
AND sty.stream_type_purpose = p_stream_type_purpose
ORDER BY ste.stream_element_date;
select ste.stream_element_date stream_element_date,ste.amount amount
FROM okl_strm_type_b sty, okl_streams stm, okl_strm_elements ste
WHERE
stm.sty_id = sty.id
AND ste.stm_id = stm.id
AND stm.active_yn = 'N'
AND stm.say_code = 'CURR'
AND stm.purpose_code = 'REPORT'
AND stm.khr_id = p_khr_id
AND ste.stream_element_date BETWEEN p_final_start_date AND p_end_date
AND sty.stream_type_purpose = p_rep_stream_type_purpose
ORDER BY ste.stream_element_date;
/*l_stmt := 'SELECT ste.stream_element_date stream_element_date
,ste.amount amount
FROM OKL_STRM_TYPE_B sty
,OKL_STREAMS stm
,OKL_STRM_ELEMENTS ste
WHERE 1 = 1
AND stm.sty_id = sty.id
AND ste.stm_id = stm.id
AND stm.active_yn = '||''''||'Y'||''''||'
AND stm.say_code = '||''''||'CURR'||'''' ;
SELECT okl_sif_seq.NEXTVAL INTO l_trx_number FROM dual;
SELECT okl_sif_seq.NEXTVAL INTO l_trx_number FROM dual;
SELECT okl_sif_seq.NEXTVAL INTO l_trx_number FROM dual;
SELECT okl_sif_seq.NEXTVAL INTO l_trx_number FROM dual;
SELECT deal_type
FROM OKL_K_HEADERS
WHERE id = p_khr_id;
SELECT ste.id
FROM OKL_STRM_TYPE_B sty,
OKL_STREAMS stm,
OKL_STRM_ELEMENTS ste
WHERE stm.khr_id = p_khr_id
AND stm.sty_id = sty.id
--AND sty.code = p_sty_code
AND sty.stream_type_purpose = p_sty_code
AND stm.id = ste.stm_id
AND stm.active_yn ='Y'
AND stm.say_code= 'CURR'
AND ste.stream_element_date >= p_date
AND ste.accrued_yn IS NULL;
SELECT ste.id
FROM OKL_STRM_TYPE_B sty,
OKL_STREAMS stm,
OKL_STRM_ELEMENTS ste
WHERE stm.khr_id = p_khr_id
AND stm.sty_id = sty.id
--AND sty.code = p_sty_code
AND sty.stream_type_purpose = p_sty_code
AND stm.id = ste.stm_id
AND stm.active_yn ='N'
AND stm.say_code= 'CURR'
AND stm.purpose_code = 'REPORT'
AND ste.stream_element_date >= p_date
AND ste.accrued_yn IS NULL;
SELECT secondary_rep_method
FROM okl_sys_acct_opts;
SELECT ste.id
FROM OKL_STRM_TYPE_B sty,
OKL_STREAMS stm,
OKL_STRM_ELEMENTS ste
WHERE stm.khr_id = p_khr_id
AND stm.sty_id = sty.id
--AND sty.code = p_sty_code
AND sty.stream_type_purpose = p_sty_code
AND stm.id = ste.stm_id
AND stm.active_yn ='Y'
AND stm.say_code= 'CURR'
AND ste.stream_element_date >= p_date
AND ste.accrued_yn IS NULL;
OKL_STREAMS_PUB.update_stream_elements(
p_api_version => l_api_version
,p_init_msg_list => l_init_msg_list
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
,p_selv_tbl => l_selv_tbl
,x_selv_tbl => x_selv_tbl);
-- call delete stream elements API.
IF l_rep_selv_tbl.COUNT > 0 THEN
OKL_STREAMS_PUB.update_stream_elements(
p_api_version => l_api_version
,p_init_msg_list => l_init_msg_list
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
,p_selv_tbl => l_rep_selv_tbl
,x_selv_tbl => x_rep_selv_tbl);
SELECT kle.id,
kle.amount,
kle.start_date,
kle.end_date,
kle.fee_type,
kle.initial_direct_cost,
tl.item_description,
tl.name,
sts.ste_code
FROM okl_k_lines_full_v kle,
okc_line_styles_b lse,
okc_k_lines_tl tl,
okc_statuses_b sts
WHERE kle.lse_id = lse.id
AND lse.lty_code = lnetype
AND tl.id = kle.id
AND tl.language = userenv('LANG')
AND kle.dnz_chr_id = chrid
AND sts.code = kle.sts_code
AND sts.ste_code not in ('HOLD', 'EXPIRED', 'CANCELLED');
SELECT
kle.amount,
kle.start_date,
kle.end_date,
kle.fee_type,
kle.initial_direct_cost,
tl.item_description,
tl.name,
sts.ste_code,
lse.lty_code
FROM okl_k_lines_full_v kle,
okc_line_styles_b lse,
okc_k_lines_tl tl,
okc_statuses_b sts
WHERE kle.lse_id = lse.id
AND tl.id = kle.id
AND tl.language = userenv('LANG')
AND kle.dnz_chr_id = chrid
AND kle.id = kleid
AND sts.code = kle.sts_code
AND sts.ste_code not in ('HOLD', 'EXPIRED', 'CANCELLED');
SELECT okc.id id,
okc.chr_id chr_id,
okc.cle_id cle_id,
okc.dnz_chr_id dnz_chr_id,
kle.capital_amount capital_amount,
kle.amount amount,
lse.lty_code lty_code
FROM okc_k_lines_b okc,
okl_k_lines kle ,
okc_line_styles_b lse
WHERE okc.cle_id = cleId
AND okc.lse_id = lse.id
AND okc.id = kle.id
AND lty_code = 'LINK_FEE_ASSET';
SELECT
str.sty_id sty_id
--FROM okl_streams str,
FROM okl_streams_rep_v str, -- Bug# 10091594
okl_strm_type_b sty
WHERE str.sty_id = sty.id
AND str.say_code = 'CURR'
AND str.khr_id = chrid
AND str.kle_id = kleid;
SELECT
sum(ste.amount) amount,
str.sty_id sty_id
--FROM okl_streams str,
FROM okl_streams_rep_v str, -- MGAAP 7263041
okl_strm_elements ste,
okl_strm_type_b sty
WHERE ste.stm_id = str.id
AND str.sty_id = sty.id
AND str.say_code = 'CURR'
AND str.khr_id = chrid
AND str.kle_id = kleid
AND ste.stream_element_date <= last_day(tadate)
AND sty.stream_type_purpose = strmPurpose
GROUP BY str.sty_id;
SELECT
sum(ste.amount) amount,
str.sty_id sty_id
--FROM okl_streams str,
FROM okl_streams_rep_v str, -- MGAAP 7263041
okl_strm_elements ste,
okl_strm_type_b sty
WHERE ste.stm_id = str.id
AND str.sty_id = sty.id
AND str.say_code = 'CURR'
AND str.khr_id = chrid
AND str.kle_id = kleid
AND ste.stream_element_date <= last_day(tadate)
AND sty.id = styid
GROUP BY str.sty_id;
SELECT
sum(ste.amount)
--FROM okl_streams str,
FROM okl_streams_rep_v str, -- MGAAP 7263041
okl_strm_elements ste,
okl_strm_type_b sty
WHERE ste.stm_id = str.id
AND str.sty_id = sty.id
AND str.say_code = 'CURR'
AND str.khr_id = chrid
AND str.kle_id = kleid
AND ste.stream_element_date <= tadate
AND sty.stream_type_purpose = strmPurpose;
SELECT
sum(ste.amount)
--FROM okl_streams str,
FROM okl_streams_rep_v str, -- MGAAP 7263041
okl_strm_elements ste,
okl_strm_type_b sty
WHERE ste.stm_id = str.id
AND str.sty_id = sty.id
AND str.say_code = 'CURR'
AND str.khr_id = chrid
AND str.kle_id = kleid
AND sty.id = styid
AND ste.stream_element_date <= tadate;
SELECT crl.id slh_id,
crl.object1_id1
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 crg.cle_id = cleId
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_information13,
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 crg.cle_id = cleId
ORDER BY crl.rule_information1;
SELECT sty.id sty_id
FROM OKL_STRM_TYPE_B sty,
OKL_PROD_STRM_TYPES psty,
OKL_K_HEADERS khr
WHERE khr.id = chrId
--AND khr.pdt_id = psty.pdt_id
AND psty.pdt_id = NVL(p_product_id, khr.pdt_id) -- MGAAP 7263041
AND psty.sty_id = sty.id
AND psty.accrual_yn = 'Y';
SELECT
sum(ste.amount) amount,
str.sty_id sty_id
--FROM okl_streams str,
FROM okl_streams_rep_v str, -- MGAAP 7263041
okl_strm_elements ste,
okl_strm_type_b sty
WHERE ste.stm_id = str.id
AND str.sty_id = sty.id
AND str.say_code = 'CURR'
AND str.khr_id = chrid
AND str.kle_id = kleid
AND ste.stream_element_date >= last_day(tadate) +1
AND sty.stream_type_purpose = strmPurpose
GROUP BY str.sty_id;
PROCEDURE DELETE_STREAMS(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_khr_id IN NUMBER,
p_cancel_date IN DATE)
IS
-- Declare the variables
l_api_version CONSTANT NUMBER := 1.0;
l_api_name CONSTANT VARCHAR2(30) := 'DELETE_STREAMS';
SELECT deal_type FROM OKL_K_HEADERS WHERE id = p_khr_id;
SELECT ste.id
FROM OKL_STRM_TYPE_B sty,
OKL_STREAMS stm,
OKL_STRM_ELEMENTS ste
WHERE stm.khr_id = p_khr_id
AND stm.sty_id = sty.id
--AND sty.code = p_sty_code
AND sty.stream_type_purpose = p_sty_code
AND stm.id = ste.stm_id
AND stm.active_yn ='Y'
AND stm.say_code = 'CURR'
AND ste.stream_element_date >= p_date
AND ste.accrued_yn IS NULL;
SELECT ste.id
FROM OKL_STRM_TYPE_B sty,
OKL_STREAMS stm,
OKL_STRM_ELEMENTS ste
WHERE stm.khr_id = p_khr_id
AND stm.sty_id = sty.id
--AND sty.code = p_sty_code
AND sty.stream_type_purpose = p_sty_code
AND stm.id = ste.stm_id
AND stm.active_yn ='N'
AND stm.say_code = 'CURR'
AND stm.purpose_code = 'REPORT'
AND ste.stream_element_date >= p_date
AND ste.accrued_yn IS NULL;
SELECT secondary_rep_method FROM okl_sys_acct_opts;
SELECT ste.id
FROM OKL_STRM_TYPE_B sty,
OKL_STREAMS stm,
OKL_STRM_ELEMENTS ste
WHERE stm.khr_id = p_khr_id
AND stm.sty_id = sty.id
--AND sty.code = p_sty_code
AND sty.stream_type_purpose = p_sty_code
AND stm.id = ste.stm_id
AND stm.active_yn ='Y'
AND stm.say_code = 'CURR'
AND ste.stream_element_date >= p_date
AND ste.accrued_yn IS NULL;
SELECT opc.kle_id kle_id,
MIN(opc.streams_from_date) start_date
FROM OKL_POOL_CONTENTS opc,
OKL_STRM_TYPE_B sty
WHERE opc.khr_id = p_khr_id
AND opc.sty_id = sty.id
--AND sty.code = 'RENT'
AND sty.stream_type_purpose IN ('RENT', 'PRINCIPAL_PAYMENT')
--AND opc.status_code = G_PC_STS_PENDING --Okl_Pool_Pvt.G_POC_STS_PENDING
AND ROWNUM < 2
GROUP BY opc.kle_id;
SELECT DECODE(sll.rule_information10, NULL, 'N', 'Y', 'Y', 'N') arrears_yn,
DECODE(sll.object1_id1, 'A',12,'S',6,'Q',3,'M',1) frequency
FROM OKC_K_HEADERS_B K,
OKC_RULE_GROUPS_B RG,
OKC_RULES_B SLH,
OKC_RULES_B SLL,
OKL_STRM_TYPE_B STRM
WHERE slh.rule_information_category = 'LASLH'
AND slh.rgp_id = rg.id
AND sll.object2_id1 = TO_CHAR(slh.id)
AND sll.rgp_id = rg.id
AND slh.object1_id1 = TO_CHAR(strm.id)
--AND strm.code = 'RENT'
AND strm.stream_type_purpose IN ('RENT', 'PRINCIPAL_PAYMENT')
AND rg.dnz_chr_id = k.id
AND rg.chr_id IS NULL
AND rg.rgd_code = 'LALEVL'
AND rg.cle_id = p_kle_id
AND k.id = p_khr_id;
SELECT MIN(ste.stream_element_date)
FROM OKL_STRM_TYPE_B sty,
OKL_STREAMS stm,
OKL_STRM_ELEMENTS ste
WHERE stm.khr_id = p_khr_id
AND stm.sty_id = sty.id
AND sty.stream_type_purpose IN ( 'RENT', 'PRINCIPAL_PAYMENT')
AND stm.id = ste.stm_id
AND stm.active_yn ='Y'
AND stm.say_code = 'CURR'
AND ste.stream_element_date >= sysdate
AND sty.billable_yn = 'Y'
AND ste.date_billed IS NULL ;
OKL_STREAMS_PUB.delete_stream_elements(
p_api_version => l_api_version ,
p_init_msg_list => l_init_msg_list ,
x_return_status => l_return_status ,
x_msg_count => l_msg_count ,
x_msg_data => l_msg_data ,
p_selv_tbl => l_selv_tbl);
OKL_STREAMS_PUB.delete_stream_elements(
p_api_version => l_api_version ,
p_init_msg_list => l_init_msg_list ,
x_return_status => l_return_status ,
x_msg_count => l_msg_count ,
x_msg_data => l_msg_data ,
p_selv_tbl => l_rep_selv_tbl);
END delete_STREAMS;