[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT BPL.Offset_Duration
,BPL.Offset_period
FROM Oks_K_Lines_B BPL
WHERE BPL.Cle_Id = Cx_BPL_Id;
Select 'Y'
From okc_k_lines_b
where id = p_line_id
-- and lse_id = 16;
Select nvl(oksbpl.allow_bt_discount,'N') --'Y' -- fixed the 11.5.9 bug 3612660
From --okc_rules_b rul,
--okc_rule_groups_b rgp,
okc_k_lines_b bpl,
oks_k_lines_b oksbpl, -- 11.5.10 rule rearchitecture changes
okc_k_lines_b cov,
okc_k_lines_b svl,
okc_k_items cimbp
where svl.id = p_line_id
and cov.cle_id = svl.id
and cov.lse_id in (2,15,20)
and bpl.cle_id = cov.id
-- and rgp.dnz_chr_id = bpl.dnz_chr_id
-- and rgp.cle_id = bpl.id
and bpl.id = cimbp.cle_id
and cimbp.object1_id1 = to_char(p_business_process_id)
and trunc(p_request_date) >= trunc(bpl.start_date)
and trunc(p_request_date) <= trunc(get_final_end_date(bpl.dnz_chr_id,bpl.end_date))
and bpl.id = oksbpl.cle_id;
Select nvl(oksbpl.allow_bt_discount,'N') --'Y' -- fixed the 11.5.9 bug 3612660
From --okc_rules_b rul,
--okc_rule_groups_b rgp,
okc_k_lines_b bpl,
oks_k_lines_b oksbpl, -- 11.5.10 rule rearchitecture changes
okc_k_lines_b cov,
okc_k_lines_b svl
where svl.id = p_line_id
and cov.cle_id = svl.id
and cov.lse_id in (2,15,20)
and bpl.cle_id = cov.id
-- and rgp.dnz_chr_id = bpl.dnz_chr_id
-- and rgp.cle_id = bpl.id
and bpl.id = p_txn_grp_id --bug 3141819
and trunc(p_request_date) >= trunc(bpl.start_date)
and trunc(p_request_date) <= trunc(get_final_end_date(bpl.dnz_chr_id,bpl.end_date))
and bpl.id = oksbpl.cle_id;
Select /*+ leading(svl) use_nl(svl ksl bpl cimbp oksbpl) index(cimbp okc_k_items_n1) */
nvl(oksbpl.allow_bt_discount,'N') --'Y' -- fixed the 11.5.9 bug 3612660
From okc_k_lines_b svl,
oks_k_Lines_b ksl,
okc_k_lines_b bpl,
okc_k_items cimbp,
oks_k_lines_b oksbpl -- 11.5.10 rule rearchitecture changes
where svl.id = p_line_id
and svl.lse_id in (1,14,19)
and ksl.cle_id = svl.id
and bpl.cle_id = ksl.coverage_id
and bpl.id = cimbp.cle_id
and cimbp.object1_id1 = to_char(p_business_process_id)
and trunc(p_request_date) >= trunc(Get_BP_Line_Start_Offset(bpl.id,svl.start_date,bpl.start_date,ksl.Standard_Cov_YN))
and trunc(p_request_date) <= trunc(Get_grace_end_Date(ksl.dnz_chr_id,svl.end_date,bpl.end_date,ksl.Standard_Cov_YN))
and bpl.id = oksbpl.cle_id;
Select nvl(oksbpl.allow_bt_discount,'N') --'Y' -- fixed the 11.5.9 bug 3612660
From okc_k_lines_b bpl,
oks_k_lines_b oksbpl, -- 11.5.10 rule rearchitecture changes
okc_k_lines_b svl,
oks_k_Lines_b ksl
where svl.id = p_line_id
and svl.lse_id in (1,14,19)
and ksl.cle_id = svl.id
and bpl.cle_id = ksl.coverage_id
and bpl.id = p_txn_grp_id --bug 3141819
and trunc(p_request_date) >= trunc(Get_BP_Line_Start_Offset(bpl.id,svl.start_date,bpl.start_date,ksl.Standard_Cov_YN))
and trunc(p_request_date) <= trunc(Get_grace_end_Date(ksl.dnz_chr_id,svl.end_date,bpl.end_date,ksl.Standard_Cov_YN))
and bpl.id = oksbpl.cle_id;
select btl.ID BTYPE_ID,
btl.CLE_ID TXNGRP_ID,
oksbtl.discount_amount UPTO_AMT,
oksbtl.discount_percent PER_CVD
from okc_k_lines_b svl,
okc_k_lines_b cov,
okc_k_lines_b bpl,
okc_k_lines_b btl,
oks_k_lines_b oksbtl, --11.5.10 rule reachitecture changes
okc_k_items cimbp,
okc_k_items cimbt
-- okc_rule_groups_b rgp,
-- okc_rules_b rul
where svl.id = p_contract_line_id
and cov.cle_id = svl.id
and cov.lse_id in (2,15,20)
and bpl.cle_id = cov.id
and bpl.id = cimbp.cle_id
and cimbp.object1_id1 = to_char(p_business_process_id)
and trunc(p_request_date) >= trunc(bpl.start_date)
and trunc(p_request_date) <= trunc(get_final_end_date(bpl.dnz_chr_id,bpl.end_date))
and btl.cle_id = bpl.id
and btl.id = cimbt.cle_id
and cimbt.object1_id1 = to_char(p_bill_type_id)
and oksbtl.cle_id = btl.id; --11.5.10 addition
select btl.ID BTYPE_ID,
btl.CLE_ID TXNGRP_ID,
oksbtl.discount_amount UPTO_AMT,
oksbtl.discount_percent PER_CVD
from okc_k_lines_b svl,
okc_k_lines_b cov,
okc_k_lines_b bpl,
okc_k_lines_b btl,
oks_k_lines_b oksbtl, --11.5.10 rule reachitecture changes
okc_k_items cimbt
-- okc_rule_groups_b rgp,
-- okc_rules_b rul
where svl.id = p_contract_line_id
and cov.cle_id = svl.id
and cov.lse_id in (2,15,20)
and bpl.cle_id = cov.id
and bpl.id = p_txngrp_id
and trunc(p_request_date) >= trunc(bpl.start_date)
and trunc(p_request_date) <= trunc(get_final_end_date(bpl.dnz_chr_id,bpl.end_date))
and btl.cle_id = bpl.id
and btl.id = cimbt.cle_id
and cimbt.object1_id1 = to_char(p_bill_type_id)
and oksbtl.cle_id = btl.id; --11.5.10 addition
select btl.ID BTYPE_ID,
btl.CLE_ID TXNGRP_ID,
oksbtl.discount_amount UPTO_AMT,
oksbtl.discount_percent PER_CVD
from okc_k_lines_b svl,
okc_k_lines_b bpl,
okc_k_lines_b btl,
oks_k_lines_b oksbtl, --11.5.10 rule reachitecture changes
okc_k_items cimbp,
okc_k_items cimbt
,OKS_K_LINES_B ksl
where svl.id = p_contract_line_id
and svl.lse_id in (1,14,19)
and ksl.cle_id = svl.id
and bpl.cle_id = ksl.coverage_id
and bpl.id = cimbp.cle_id
and cimbp.object1_id1 = to_char(p_business_process_id)
and trunc(p_request_date) >= trunc(Get_BP_Line_Start_Offset(bpl.id, svl.start_date, bpl.start_date, KSL.Standard_Cov_YN))
and trunc(p_request_date) <= trunc(Get_grace_end_Date(svl.dnz_chr_id, svl.end_date, bpl.end_date,KSL.Standard_Cov_YN))
and btl.cle_id = bpl.id
and btl.id = cimbt.cle_id
and cimbt.object1_id1 = to_char(p_bill_type_id)
and oksbtl.cle_id = btl.id; --11.5.10 addition
select btl.ID BTYPE_ID,
btl.CLE_ID TXNGRP_ID,
oksbtl.discount_amount UPTO_AMT,
oksbtl.discount_percent PER_CVD
from okc_k_lines_b svl,
okc_k_lines_b bpl,
okc_k_lines_b btl,
oks_k_lines_b oksbtl, --11.5.10 rule reachitecture changes
okc_k_items cimbt
,OKS_K_LINES_B ksl
where svl.id = p_contract_line_id
and svl.lse_id in (1,14,19)
and ksl.cle_id = svl.id
and bpl.cle_id = ksl.coverage_id
and bpl.id = p_txngrp_id
and trunc(p_request_date) >= trunc(Get_BP_Line_Start_Offset(bpl.id, svl.start_date, bpl.start_date, KSL.Standard_Cov_YN))
and trunc(p_request_date) <= trunc(Get_grace_end_Date(svl.dnz_chr_id, svl.end_date, bpl.end_date,KSL.Standard_Cov_YN))
and btl.cle_id = bpl.id
and btl.id = cimbt.cle_id
and cimbt.object1_id1 = to_char(p_bill_type_id)
and oksbtl.cle_id = btl.id; --11.5.10 addition
select rul.rule_information_category ,
rul.object1_id1
from okc_rules_b rul,
okc_rule_groups_b rgp
where rgp.id = rul.rgp_id
and rul.rule_information_category in ('PRE','DST')
and rgp.cle_id = p_cle_id;
select rul.rule_information_category ,
rul.object1_id1
from okc_rules_b rul,
okc_rule_groups_b rgp
where rgp.id = rul.rgp_id
and rul.rule_information_category in ('PRE')
and rgp.chr_id = p_chr_id;
select bpl.id,
bpl.dnz_chr_id,
cimbp.object1_id1,
bpl.start_date bpl_start_date,
bpl.end_date bpl_end_date,
bpl.price_list_id bpl_pre,
oksbpl.discount_list oksbpl_dst,
chr.price_list_id chr_pre
from okc_k_lines_b svl,
okc_k_lines_b cov,
okc_k_lines_b bpl,
oks_k_lines_b oksbpl,
okc_k_items cimbp,
okc_k_headers_b chr -- 11.5.10 addition
where svl.id = p_contract_line_id
and svl.chr_id = chr.id -- 11.5.10 addition
and cov.cle_id = svl.id
and cov.lse_id in (2,15,20)
and bpl.cle_id = cov.id
and oksbpl.cle_id = bpl.id
and bpl.id = cimbp.cle_id
and cimbp.object1_id1 = nvl(to_char(p_business_process_id),cimbp.object1_id1)
and trunc(p_request_date) >= trunc(bpl.start_date)
and trunc(p_request_date) <= trunc(get_final_end_date(bpl.dnz_chr_id,bpl.end_date));
select bpl.id,
bpl.dnz_chr_id,
cimbp.object1_id1,
bpl.start_date bpl_start_date,
bpl.end_date bpl_end_date,
bpl.price_list_id bpl_pre,
oksbpl.discount_list oksbpl_dst,
chr.price_list_id chr_pre
from okc_k_lines_b svl,
okc_k_lines_b bpl,
oks_k_lines_b oksbpl,
okc_k_items cimbp,
okc_k_headers_all_b chr -- Modified for 12.0 MOAC project (JVARGHES)
, oks_k_lines_b ksl
where svl.id = p_contract_line_id
and svl.lse_id in (1,14,19)
and ksl.cle_id = svl.id
and svl.chr_id = chr.id -- 11.5.10 addition
and bpl.cle_id = ksl.coverage_id
and oksbpl.cle_id = bpl.id
and bpl.id = cimbp.cle_id
and cimbp.object1_id1 = nvl(to_char(p_business_process_id),cimbp.object1_id1)
and trunc(p_request_date) >= trunc(Get_BP_Line_Start_Offset(bpl.id,svl.start_date,bpl.start_date,ksl.Standard_Cov_YN))
and trunc(p_request_date) <= trunc(Get_grace_end_Date(ksl.dnz_chr_id,svl.end_date,bpl.end_date,ksl.Standard_Cov_YN));
select btl.id btl_id
from okc_k_lines_b svl,
okc_k_lines_b cov,
okc_k_lines_b bpl,
okc_k_items cimbp,
okc_k_lines_b btl,
okc_k_items cimbt
where svl.id = p_cont_line_id
and cov.cle_id = svl.id
and cov.lse_id in (2,15,20)
and bpl.cle_id = cov.id
and bpl.id = cimbp.cle_id
and trunc(nvl(p_request_date,sysdate)) >= trunc(bpl.start_date)
and trunc(nvl(p_request_date,sysdate)) <= trunc(get_final_end_date(bpl.dnz_chr_id,bpl.end_date))
and cimbp.object1_id1 = to_char(p_busi_proc_id)
and btl.cle_id = bpl.id
and btl.lse_id in (5,59,23)
and btl.id = cimbt.cle_id
and cimbt.object1_id1 = to_char(p_txn_bill_type_id);
select btl.id btl_id
from okc_k_lines_b svl,
okc_k_lines_b bpl,
okc_k_items cimbp,
okc_k_lines_b btl,
okc_k_items cimbt,
oks_k_lines_b ksl
where svl.id = p_cont_line_id
and svl.lse_id in (1,14,19)
and ksl.cle_id = svl.id
and bpl.cle_id = ksl.coverage_id
and bpl.id = cimbp.cle_id
and trunc(nvl(p_request_date,sysdate)) >= trunc(Get_BP_Line_Start_Offset(bpl.id,svl.start_date,bpl.start_date,ksl.Standard_Cov_YN))
and trunc(nvl(p_request_date,sysdate)) <= trunc(Get_grace_end_Date(ksl.dnz_chr_id,svl.end_date,bpl.end_date,ksl.Standard_Cov_YN))
and cimbp.object1_id1 = to_char(p_busi_proc_id)
and btl.cle_id = bpl.id
and btl.lse_id in (5,59,23)
and btl.id = cimbt.cle_id
and cimbt.object1_id1 = to_char(p_txn_bill_type_id);
select ID,
CLE_ID,
DNZ_CHR_ID,
START_HOUR,
START_MINUTE,
END_HOUR,
END_MINUTE,
MONDAY_FLAG,
TUESDAY_FLAG,
WEDNESDAY_FLAG,
THURSDAY_FLAG,
FRIDAY_FLAG,
SATURDAY_FLAG,
SUNDAY_FLAG,
OBJECT1_ID1,
OBJECT1_ID2,
JTOT_OBJECT1_CODE,
BILL_RATE_CODE,
UOM,
FLAT_RATE,
PERCENT_OVER_LIST_PRICE,
HOLIDAY_YN,
BT_CLE_ID
from oks_billrate_schedules
where bt_cle_id = p_btl_id
and (
decode(p_sunday_flag,'Y',sunday_flag,'N','#') = decode(p_sunday_flag,'Y','Y','N','#') and
decode(p_monday_flag,'Y',monday_flag,'N','#') = decode(p_monday_flag,'Y','Y','N','#') and
decode(p_tuesday_flag,'Y',tuesday_flag,'N','#') = decode(p_tuesday_flag,'Y','Y','N','#') and
decode(p_wednesday_flag,'Y',wednesday_flag,'N','#') = decode(p_wednesday_flag,'Y','Y','N','#') and
decode(p_thursday_flag,'Y',thursday_flag,'N','#') = decode(p_thursday_flag,'Y','Y','N','#') and
decode(p_friday_flag,'Y',friday_flag,'N','#') = decode(p_friday_flag,'Y','Y','N','#') and
decode(p_saturday_flag,'Y',saturday_flag,'N','#') = decode(p_saturday_flag,'Y','Y','N','#')
)
and nvl(holiday_yn,'N') = p_holiday_flag
order by start_hour,start_minute;
br_sch_tbl.DELETE;
SELECT okh.Grace_Duration Duration
,okh.Grace_Period TimeUnit
,chr.end_date end_date
FROM okc_k_headers_all_b chr, -- Modified for 12.0 MOAC project (JVARGHES)
Oks_K_Headers_B OKH
WHERE chr.Id = Cx_HDR_Id
and okh.chr_id = chr.id;