[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE Delete_lvl_element(p_cle_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2);
SELECT lin.start_date,
Lin.end_date,
Lin.price_negotiated,
lin.price_unit*itm.number_of_items unit_price,
Lin.lse_id,
--lin.price_unit*itm.number_of_items*kln.toplvl_price_qty total_amount, --bug#5359695 commented
nvl(Lin.price_negotiated,0) total_amount, --bug#5359695
kln.price_uom,
kln.toplvl_uom_code,
nvl(kln.ubt_amount,0) ubt_amount --bug#5359695
FROM okc_k_lines_b lin,
Oks_k_lines_b kln,
Okc_k_items itm
WHERE kln.cle_id = lin.id and
itm.cle_id = lin.id and
lin.id = p_cle_id;
SELECT nvl(sum(bsl.amount),0) amount,
max(bsl.date_billed_From) date_billed_From,
max(bsl.date_billed_to) date_billed_to
FROM oks_bill_sub_lines bsl
WHERE bsl.cle_id = p_cle_id;
SELECT 'Y'
FROM okc_k_items itm,
oks_subscr_header_b sub
WHERE itm.cle_id = P_cle_id
AND sub.instance_id = itm.object1_id1;
select oel.service_period
from okc_k_rel_objs rel,
oe_order_lines_all oel
where rel.cle_id = P_cle_id
and oel.line_id = rel.object1_id1;
SELECT id, TRUNC(start_date) start_dt,
nvl(trunc(date_terminated - 1) ,TRUNC(end_date)) end_dt
FROM okc_k_headers_b
WHERE Id = l_contract_id ;
SELECT line.id id, line.inv_rule_id inv_rule_id, line.lse_id lse_id,
det.usage_type usage_type
FROM OKC_K_LINES_b line, oks_k_lines_b det
WHERE line.dnz_chr_id = l_contract_id
AND line.lse_id IN (1, 12, 14, 19, 46)
AND line.id = det.cle_id;
SELECT khr.acct_rule_id acct_rule_id
FROM oks_k_headers_b khr
WHERE khr.chr_id = c_chr_id;
SELECT cle.id
FROM okc_k_lines_b cle
WHERE cle.cle_id = c_topline_id
AND cle.lse_id IN(7,8,9,10,11,13,25,35);
/*UPDATE OKS_K_HEADERS_B SET billing_schedule_type = p_billing_type
WHERE chr_id = l_Contract_Rec.id;*/
UPDATE okc_k_lines_b SET inv_rule_id = nvl(l_line_inv_id,p_invoice_rule_id)
WHERE id = l_top_line_rec.id;
UPDATE oks_k_lines_b
SET acct_rule_id = l_acct_rule_id
WHERE cle_id = l_top_line_rec.id;
UPDATE oks_k_lines_b
SET acct_rule_id = l_acct_rule_id
WHERE cle_id = c_subline_rec.id;
SELECT line.id subline_id, TRUNC(line.start_date) cp_start_dt,
TRUNC(line.end_date) cp_end_dt, line.dnz_chr_id dnz_chr_id,
TRUNC(line.date_terminated) cp_term_dt,
price_UOM,lse_id cp_lse_id,
dtl.billing_schedule_type billing_schedule_type,
dtl.full_credit full_credit,
(nvl(line.price_negotiated,0) + nvl(dtl.ubt_amount,0) +
nvl(dtl.credit_amount,0) + nvl(dtl.suppressed_credit,0) ) subline_amt
FROM okc_k_lines_b line, oks_k_lines_b dtl
WHERE line.cle_id = l_line_id
AND line.date_cancelled is NULL
AND line.id = dtl.cle_id
AND ((l_style_id = 1 and line.lse_id in (35,7,8,9,10,11))
OR (l_style_id = 12 and line.lse_id = 13)
OR (l_style_id = 14 and line.lse_id = 18)
OR (l_style_id = 19 and line.lse_id = 25));
SELECT line.chr_id chr_id, line.dnz_chr_id dnz_chr_id, line.id id, line.lse_id lse_id,
TRUNC(line.start_date) line_start_dt, TRUNC(line.end_date) line_end_dt,
line.cle_id cle_id,TRUNC(date_terminated) line_term_dt,
dtl.full_credit full_credit,
price_uom,
(nvl(line.price_negotiated,0) + nvl(dtl.ubt_amount,0) +
nvl(dtl.credit_amount,0) + nvl(dtl.suppressed_credit,0) ) line_amt
FROM okc_k_lines_b line, oks_k_lines_b dtl
WHERE line.id = dtl.cle_id AND line.Id = l_line_id
AND line.date_cancelled is NULL;
SELECT id,trunc(date_start) date_start,
amount,trunc(date_end) date_end,object_version_number,
date_to_interface, date_transaction
FROM oks_level_elements
WHERE cle_id = p_line_id
ORDER BY date_start;
Select line.price_negotiated
from okc_k_lines_b line
where line.id = p_id;
l_sll_tbl.delete;
UPDATE oks_k_lines_b set billing_schedule_type = p_billing_type
WHERE cle_id = l_line_csr_rec.id;
DELETE FROM OKS_LEVEL_ELEMENTS
WHERE date_completed IS NULL
AND cle_id = l_Line_Csr_Rec.id;
'unbilled lvl element deleted = ' || sql%rowcount
);
l_top_bs_tbl.DELETE;
/* check the bill type if other then 'P' then delete schedule and sLL*/
---get bill type details
IF nvl(l_subline_csr_rec.billing_schedule_type,'T') <> 'P' THEN
----if line has sll with billtype <> 'P', delete sll and level elements
--- and updates l_sll_count value as in case of 'P' Bs will be cascaded to
---only subline with same effectivity.
Del_line_sll_lvl(p_line_id => l_subline_csr_rec.subline_id,
x_return_status => x_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
/* check the bill type if other then 'P' then delete schedule and sLL*/
---get bill type details
IF nvl(l_subline_csr_rec.billing_schedule_type,'T') <> 'P' THEN
----if line has sll with billtype <> 'P', delete sll and level elements
Del_line_sll_lvl(p_line_id => l_subline_csr_rec.subline_id,
x_return_status => x_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
UPDATE oks_k_lines_b
SET billing_schedule_type = 'P'
WHERE cle_id = l_subline_csr_rec.subline_id;
fnd_log.string(fnd_log.level_event,G_MODULE_CURRENT||'.create_bill_sch_rules.update_billtype',
'sll count >0 and not same effectivity'
|| 'updated sub line billing type to P = ' || sql%rowcount
);
DELETE FROM oks_level_elements WHERE cle_id = l_subline_csr_rec.subline_id;
DELETE FROM oks_stream_levels_b WHERE cle_id = l_subline_csr_rec.subline_id;
OKS_BILL_LEVEL_ELEMENTS_PVT.update_row(
p_api_version => l_api_version,
p_init_msg_list => l_init_msg_list,
x_return_status => x_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_letv_tbl => l_top_bs_tbl,
x_letv_tbl => l_lvl_ele_tbl_out);
fnd_log.string(fnd_log.level_event,G_MODULE_CURRENT||'.create_bill_sch_rules.update_top_bs',
'OKS_BILL_LEVEL_ELEMENTS_PVT.update_row(x_return_status = '||x_return_status
||', tbl count = '||l_lvl_ele_tbl_out.count ||')');
SELECT id , object_version_number FROM oks_stream_levels_b
WHERE sequence_no = p_seq_no
AND cle_id = p_line_id;
select lse_id
from okc_k_lines_b
where id = p_cle_id;
l_strm_lvl_tbl_in.DELETE;
IF p_strm_lvl_tbl(l_tbl_count).Id IS NULL THEN ---------FOR INSERT
-----errorout_ad('null sll id');
ELSE --------FOR UPDATE
-----errorout_ad('sll_id = ' || p_strm_lvl_tbl(l_tbl_count).Id);
IF p_strm_lvl_tbl(l_tbl_count).Id IS NULL THEN -------FOR INSERT
l_sll_id := NULL;
ELSE ----FOR UPDATE
-------FIND OUT SUBLINE sll ID FOR UPDATE MATCH THEM WITH SEQUENCE NUMBER
OPEN l_subline_sll_csr(p_strm_lvl_tbl(l_tbl_count).sequence_no,p_strm_lvl_tbl(l_tbl_count).cle_id);
END IF; ---------INSERT/UPDATE IF
l_strm_lvl_tbl_in(1).last_updated_by := OKC_API.G_MISS_NUM;
l_strm_lvl_tbl_in(1).last_update_date := SYSDATE;
OKS_SLL_PVT.insert_row(
p_api_version => l_api_version,
p_init_msg_list => l_init_msg_list,
x_return_status => x_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_sllv_tbl => l_strm_lvl_tbl_in,
x_sllv_tbl => l_strm_lvl_tbl_out);
fnd_log.string(fnd_log.level_event,G_MODULE_CURRENT||'.create_stream_level.insert',
'OKS_SLL_PVT.insert_row(x_return_status = '||x_return_status
||', sll id created = '||l_strm_lvl_tbl_out(1).id ||')');
OKS_SLL_PVT.update_row(
p_api_version => l_api_version,
p_init_msg_list => l_init_msg_list,
x_return_status => x_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_sllv_tbl => l_strm_lvl_tbl_in,
x_sllv_tbl => l_strm_lvl_tbl_out);
fnd_log.string(fnd_log.level_event,G_MODULE_CURRENT||'.create_stream_level.update',
'OKS_SLL_PVT.update_row(x_return_status = '||x_return_status
||', sll id updated = '||l_strm_lvl_tbl_out(1).id ||')');
l_sll_prorate_tbl.DELETE;
UPDATE oks_stream_levels_b
Set level_amount = l_sll_prorate_tbl(l_index).sll_amount
WHERE id = x_sll_out_tbl(l_index).id;
END LOOP; ---END OF UPDATE LOOP
SELECT id ,object_version_number
FROM oks_stream_levels_b
WHERE ID = p_sll_id;
SELECT (nvl(line.price_negotiated,0) + nvl(dtl.ubt_amount,0) +
nvl(dtl.credit_amount,0) + nvl(dtl.suppressed_credit,0) ) line_amt
FROM okc_k_lines_b line, oks_k_lines_b dtl
WHERE line.id = p_line_id
AND line.id = dtl.cle_id;
UPDATE OKS_K_LINES_B SET billing_schedule_type = p_billing_type
WHERE cle_id = p_subline_rec.cp_id;
SELECT sll.sequence_no,element.cle_id,
element.sequence_number, element.date_transaction,
element.date_start, element.date_to_interface,
element.date_completed, element.amount,element.date_end,
element.rul_id
FROM oks_level_elements element, oks_stream_levels_b sll
WHERE sll.id = element.rul_id
AND sll.cle_id = p_cle_id
ORDER BY sll.sequence_no,to_number(element.sequence_number);
SELECT sll.sequence_no,sll.chr_id,
element.sequence_number, element.date_transaction,
element.date_start, element.date_to_interface,
element.date_completed, element.amount, element.date_end,
element.rul_id
FROM oks_level_elements element, oks_stream_levels_b sll
WHERE sll.id = element.rul_id
AND sll.chr_id = p_chr_id
ORDER BY sll.sequence_no,to_number(element.sequence_number);
SELECT id ,date_start,amount,date_end
FROM oks_level_elements where rul_id = p_sllid
ORDER BY to_number(sequence_number);
select count(a.id) periods from oks_level_elements a, oks_k_lines_b line
where a.cle_id = ( select max(parent_cle_id) from oks_level_elements b where rul_id = p_sll_id )
and a.date_start <= last_start_date
and Line.cle_id = a.cle_id
and Line.billing_schedule_type in ('T','E');
select count(a.id) periods from oks_level_elements a, oks_k_lines_b line,
oks_stream_levels_b sll1,
oks_stream_levels_b sll2
where a.cle_id = ( select max(parent_cle_id) from oks_level_elements b where rul_id = p_sll_id )
and a.date_start <= last_start_date
and a.rul_id = sll1.id
and sll1.sequence_no = sll2.sequence_no
and sll2.id = p_sll_id
and Line.cle_id = a.cle_id
and Line.billing_schedule_type in ('T','E');
select count(a.id) periods from oks_level_elements a, oks_k_lines_b line
where a.cle_id = ( select max(parent_cle_id) from oks_level_elements b where rul_id = p_sll_id )
and a.date_start <= last_start_date
and Line.cle_id = a.cle_id
and Line.billing_schedule_type in ('T','E');
select count(id) from okc_k_lines_b lin
where lin.id = p_sub_cle_id
and lin.cle_id in (select top.id from okc_k_lines_b top
where lin.start_date > top.start_date);
select count(id) from oks_stream_levels_b strm
where strm.cle_id = p_top_cle_id;
x_period_counter := l_billed_count + 1; ---LINE WILL BE INSERTED FROM THIS COUNTER.
select SUM(nvl(line.price_negotiated,0) + nvl(dtl.ubt_amount,0) +
nvl(dtl.credit_amount,0) + nvl(dtl.suppressed_credit,0) ) tot_amt
from oks_k_lines_b dtl, okc_k_lines_b line
where line.id = dtl.cle_id
AND line.dnz_chr_id = p_contract_id
AND lse_id IN(1,12,14,19,46);
SELECT nvl(SUM(amount),0) tot_amt
FROM oks_level_elements
WHERE cle_id = p_line_id;
l_lvl_ele_tbl_in.delete;
Delete_lvl_element(p_cle_id => p_line_rec.id,
x_return_status => x_return_status);
'Delete_lvl_element(x_return_status = '||x_return_status
||', line id passed = '|| p_line_rec.id ||')');
null; ---donot insert record in level element
SELECT nvl(BILLED_AT_SOURCE , 'N')
INTO l_billed_at_source
FROM OKC_K_HEADERS_ALL_B
WHERE id = p_line_rec.dnz_chr_id;
l_lvl_ele_tbl_in(l_tbl_seq).last_updated_by := OKC_API.G_MISS_NUM;
l_lvl_ele_tbl_in(l_tbl_seq).last_update_date := SYSDATE;
OKS_BILL_LEVEL_ELEMENTS_PVT.insert_row(
p_api_version => l_api_version,
p_init_msg_list => l_init_msg_list,
x_return_status => x_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_letv_tbl => l_lvl_ele_tbl_in,
x_letv_tbl => l_lvl_ele_tbl_out);
fnd_log.string(fnd_log.level_event,G_MODULE_CURRENT||'.Create_Level_elements.insert',
'oks_bill_level_elements_pvt.insert_row(x_return_status = '||x_return_status
||', l_lvl_ele_tbl_out = '|| l_lvl_ele_tbl_out.count ||')');
l_lvl_ele_tbl_in.delete;
NULL; ---donot insert record in level element
l_lvl_ele_tbl_in(l_tbl_seq).last_updated_by := OKC_API.G_MISS_NUM;
l_lvl_ele_tbl_in(l_tbl_seq).last_update_date := SYSDATE;
OKS_BILL_LEVEL_ELEMENTS_PVT.insert_row(
p_api_version => l_api_version,
p_init_msg_list => l_init_msg_list,
x_return_status => x_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_letv_tbl => l_lvl_ele_tbl_in,
x_letv_tbl => l_lvl_ele_tbl_out);
fnd_log.string(fnd_log.level_event,G_MODULE_CURRENT||'.create_hdr_level_elements.insert',
'oks_bill_level_elements_pvt.insert_row(x_return_status = '||x_return_status
||', l_lvl_ele_tbl_out = '|| l_lvl_ele_tbl_out.count ||')');
SELECT contract.currency_code
FROM okc_k_headers_b contract, okc_k_lines_b line
WHERE contract.id = line.dnz_chr_id and line.id = p_cle_id;
SELECT contract.currency_code
FROM okc_k_headers_b contract
WHERE contract.id = p_chr_id;
Procedure Update_Sll_Amount
(
p_line_id IN NUMBER,
x_return_status OUT NOCOPY Varchar2
)
IS
Cursor l_Line_Csr Is
SELECT line.chr_id chr_id, line.dnz_chr_id dnz_chr_id, line.lse_id lse_id,
TRUNC(line.start_date) line_start_dt, TRUNC(line.end_date) line_end_dt,
(nvl(line.price_negotiated,0) + nvl(dtl.ubt_amount,0) +
nvl(dtl.credit_amount,0) + nvl(dtl.suppressed_credit,0) ) line_amt
FROM okc_k_lines_b line, oks_k_lines_b dtl
WHERE line.id = dtl.cle_id AND line.Id = p_line_id ;
SELECT sll.Id , sll.sequence_no , sll.start_date, sll.level_periods,
sll.uom_per_period, sll.uom_code, sll.level_amount, sll.invoice_offset_days,
sll.interface_offset_days, sll.cle_id, sll.chr_id,
sll.dnz_chr_id, sll.end_date,sll.object_version_number
FROM oks_stream_levels_b sll
WHERE sll.cle_id = p_line_id
ORDER BY sll.sequence_no;
l_strm_lvl_tbl_in.DELETE;
l_strm_lvl_tbl_in(l_Sll_Counter).last_updated_by := OKC_API.G_MISS_NUM;
l_strm_lvl_tbl_in(l_Sll_Counter).last_update_date := SYSDATE;
UPDATE oks_stream_levels_b
set level_amount = l_strm_lvl_tbl_in(i).level_amount
WHERE id = l_strm_lvl_tbl_in(i).id;
G_MODULE_CURRENT || '.update_sll_amount.update',
'update sll id = ' || l_strm_lvl_tbl_in(i).id
||', amt = ' || l_strm_lvl_tbl_in(i).level_amount
);
fnd_log.string(fnd_log.level_unexpected,G_MODULE_CURRENT||'.update_sll_amount.UNEXPECTED',
'sqlcode = '||sqlcode||', sqlerrm = '||sqlerrm);
END Update_Sll_Amount;
SELECT COUNT(id) INTO l_sll_rule_count
FROM oks_stream_levels_b
WHERE cle_id = p_subline_id ;
PROCEDURE Delete_lvl_element(p_cle_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2)
IS
BEGIN
---it deletes the level elementwhich are not billed for the given line id.
x_return_status := 'S';
DELETE FROM OKS_LEVEL_ELEMENTS
WHERE date_Completed is NULL
AND cle_id = p_cle_id;
END Delete_lvl_element;
DELETE FROM OKS_LEVEL_ELEMENTS
WHERE rul_id IN
(SELECT sll.id
FROM OKS_STREAM_LEVELS_B sll
WHERE sll.dnz_chr_id = p_hdr_id);
DELETE FROM OKS_STREAM_LEVELS_B
WHERE dnz_chr_id = p_hdr_id
AND chr_id IS NULL;
SELECT id, TRUNC(start_date) start_dt, TRUNC(end_date) end_dt
FROM OKC_K_HEADERS_b
WHERE id = p_hdr_id ;
SELECT sll.Id , sll.sequence_no , sll.start_date, sll.level_periods,
sll.uom_per_period, sll.uom_code, sll.level_amount, sll.invoice_offset_days,
sll.interface_offset_days, sll.cle_id, sll.chr_id,
sll.dnz_chr_id, sll.end_date, sll.object_version_number
FROM oks_stream_levels_b sll
WHERE sll.chr_id = p_hdr_id
ORDER BY sll.sequence_no;
l_strm_lvl_tbl_in.DELETE;
l_strm_lvl_tbl_in(l_Sll_Counter).last_updated_by := OKC_API.G_MISS_NUM;
l_strm_lvl_tbl_in(l_Sll_Counter).last_update_date := SYSDATE;
UPDATE oks_stream_levels_b
set level_amount = l_strm_lvl_tbl_in(i).level_amount
WHERE id = l_strm_lvl_tbl_in(i).id;
SELECT NVL(SUM(AMOUNT),0) INTO l_sll_amt
FROM OKS_LEVEL_ELEMENTS
WHERE rul_id = p_Sll_id;
SELECT line.id subline_id, TRUNC(line.start_date) cp_start_dt,
TRUNC(line.end_date) cp_end_dt, TRUNC(line.date_terminated) cp_term_dt,
dtl.full_credit full_credit,price_uom,lse_id cp_lse_id,
(nvl(line.price_negotiated,0) + nvl(dtl.ubt_amount,0) +
nvl(dtl.credit_amount,0) + nvl(dtl.suppressed_credit,0) ) subline_amt
FROM okc_k_lines_b line, oks_k_lines_b dtl
WHERE line.id = p_cp_line_id
AND line.date_cancelled is NULL
AND line.id = dtl.cle_id;
SELECT line.chr_id chr_id, line.dnz_chr_id dnz_chr_id, line.id id, line.lse_id lse_id,
TRUNC(line.start_date) line_start_dt, TRUNC(line.end_date) line_end_dt,
line.inv_rule_id inv_id,
nvl(dtl.billing_schedule_type,'T') billing_schedule_type,
(nvl(line.price_negotiated,0) + nvl(dtl.ubt_amount,0) +
nvl(dtl.credit_amount,0) + nvl(dtl.suppressed_credit,0) ) line_amt
FROM okc_k_lines_b line, oks_k_lines_b dtl
WHERE line.id = dtl.cle_id AND line.Id = p_top_line_id
AND line.date_cancelled is NULL;
SELECT sll.Id , sll.sequence_no , sll.start_date, sll.level_periods,
sll.uom_per_period, sll.uom_code, sll.level_amount, sll.invoice_offset_days,
sll.interface_offset_days, sll.cle_id, sll.chr_id,
sll.dnz_chr_id,sll.end_date
FROM oks_stream_levels_b sll
WHERE sll.cle_id = p_top_line_id
ORDER BY sll.sequence_no;
SELECT id, trunc(date_start) date_start,
amount,trunc(date_end) date_end,object_version_number,
date_to_interface, date_transaction
FROM oks_level_elements
WHERE cle_id = p_line_id
ORDER BY date_start;
Select line.price_negotiated
from okc_k_lines_b line
where line.id = p_id;
l_update_required VARCHAR2(1);
l_update_required := 'N';
l_sll_tbl.DELETE;
l_update_required := 'Y';
IF l_update_required = 'Y' THEN
OKS_BILL_SCH.UPDATE_BS_ENDDATE(p_line_id => p_top_line_id,
p_chr_id => NULL,
x_return_status => x_return_status);
l_top_bs_tbl.DELETE;
OKS_BILL_LEVEL_ELEMENTS_PVT.update_row(
p_api_version => l_api_version,
p_init_msg_list => l_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_letv_tbl => l_top_bs_tbl,
x_letv_tbl => l_lvl_ele_tbl_out);
fnd_log.string(fnd_log.level_event,G_MODULE_CURRENT||'.create_bill_sch_cp.update_top_bs',
'oks_bill_level_elements_pvt.update_row(x_return_status = '||x_return_status ||')');
SELECT sll.Id , sll.sequence_no , sll.start_date, sll.level_periods,
sll.uom_per_period, sll.uom_code, sll.level_amount, sll.invoice_offset_days,
sll.interface_offset_days, sll.cle_id, sll.chr_id,
sll.dnz_chr_id, sll.end_date
FROM oks_stream_levels_b sll
WHERE sll.cle_id = p_top_line_id
ORDER BY sll.sequence_no;
SELECT line.chr_id chr_id, line.dnz_chr_id dnz_chr_id, line.id id, line.lse_id lse_id,
TRUNC(line.start_date) start_dt, TRUNC(line.end_date) end_dt,
line.inv_rule_id inv_id,
nvl(dtl.billing_schedule_type,'T') billing_schedule_type,
(nvl(line.price_negotiated,0) + nvl(dtl.ubt_amount,0) +
nvl(dtl.credit_amount,0) + nvl(dtl.suppressed_credit,0) ) line_amt
FROM okc_k_lines_b line, oks_k_lines_b dtl
WHERE line.id = dtl.cle_id AND line.Id = p_top_line_id ;
SELECT usage_type,usage_period
FROM oks_k_lines_b
WHERE cle_id = p_top_line_id;
SELECT price_uom
FROM OKS_K_LINES_B
WHERE cle_id = p_top_line_id;
l_update_end_date VARCHAR2(1);
l_update_end_date := 'N';
l_sll_tbl.DELETE;
l_update_end_date := 'Y';
IF l_update_end_date = 'Y' THEN ---Migrated
OKS_BILL_SCH.UPDATE_BS_ENDDATE(p_line_id => p_top_line_id,
p_chr_id => NULL,
x_return_status => x_return_status);
l_strm_lvl_tbl_in.delete;
l_sll_tbl.DELETE(l_sll_tbl_index);
G_MODULE_CURRENT || '.cascade_dates_sll.sll_after_delete',
'sll count after deleting sll where sll end date < line start date'
|| ', sll count = ' || l_sll_tbl.count
);
OKS_SLL_PVT.delete_row(
p_api_version => l_api_version,
p_init_msg_list => l_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_sllv_tbl => l_strm_lvl_tbl_in);
'oks_sll_pvt.delete_row(x_return_status = '||x_return_status
||', sll passed for delete = '|| l_strm_lvl_tbl_in.count ||')');
'after sll delete line start date > sll start date'
|| ', sll dt = ' || l_sll_start_date
);
l_strm_lvl_tbl_in.DELETE;
l_sll_tbl.DELETE(l_sll_tbl_index);
OKS_BILL_UTIL_PUB.delete_level_elements (
p_api_version => l_api_version,
p_rule_id => l_strm_lvl_tbl_in(l_sll_ind).id,
p_init_msg_list => l_msg_list,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_return_status => x_return_status );
'oks_bill_util_pub.delete_level_elements(x_return_status = '||x_return_status
||', sll id passed = '|| l_strm_lvl_tbl_in(l_sll_ind).id ||')');
DELETE FROM OKS_LEVEL_ELEMENTS
WHERE cle_id IN (SELECT id
FROM OKC_K_LINES_B cp
WHERE cp.cle_id = p_top_line_id
and cp.lse_id in (35,7,8,9,10,11,13,18,25));
DELETE FROM OKS_LEVEL_ELEMENTS
WHERE cle_id = p_top_line_id;
DELETE FROM OKS_STREAM_LEVELS_B
WHERE cle_id IN ( select id
FROM okc_k_lines_b cp
WHERE cp.cle_id = p_top_line_id
and cp.lse_id in (35,7,8,9,10,11,13,18,25));
SELECT id, trunc(date_start) date_start,
amount, trunc(date_end) date_end
FROM oks_level_elements element
WHERE cle_id = p_cp_id
ORDER by date_start;
l_cp_bs_tbl.DELETE;
Procedure Update_OM_SLL_Date
(
p_top_line_id IN NUMBER,
x_return_status OUT NOCOPY Varchar2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2)
IS
CURSOR l_line_sll_csr IS
SELECT id, cle_id, chr_id, dnz_chr_id , uom_code,
sequence_no, Start_Date, end_Date, level_periods,
uom_per_period, level_amount, invoice_offset_days, interface_offset_days
FROM OKs_stream_levels_b
WHERE cle_id = p_top_line_id
ORDER BY sequence_no;
SELECT line.chr_id chr_id, line.dnz_chr_id dnz_chr_id, line.id id, line.lse_id lse_id,
TRUNC(line.start_date) start_dt,line.inv_rule_id inv_rule_id,
nvl(trunc(line.date_terminated - 1),TRUNC(line.end_date)) end_dt,
nvl(dtl.billing_schedule_type,'T') billing_schedule_type,
(nvl(line.price_negotiated,0) + nvl(dtl.ubt_amount,0) +
nvl(dtl.credit_amount,0) + nvl(dtl.suppressed_credit,0) ) line_amt
FROM okc_k_lines_b line, oks_k_lines_b dtl
WHERE line.id = dtl.cle_id AND line.Id = p_top_line_id ;
l_sll_tbl.DELETE;
fnd_log.string(fnd_log.level_exception,G_MODULE_CURRENT||'.update_om_sll_date.EXCEPTION',
'G_EXCEPTION_HALT_VALIDATION');
fnd_log.string(fnd_log.level_unexpected,G_MODULE_CURRENT||'.update_om_sll_date.UNEXPECTED',
'sqlcode = '||sqlcode||', sqlerrm = '||sqlerrm);
END Update_OM_SLL_Date;
DELETE FROM OKS_LEVEL_ELEMENTS
WHERE cle_id IN (SELECT cp.id
FROM OKC_k_LINES_B cp
WHERE cp.cle_id = p_top_line_id
and cp.lse_id in (35,7,8,9,10,11,13,18,25));
DELETE FROM OKS_LEVEL_ELEMENTS
WHERE cle_id = p_top_line_id;
delete FROM OKS_STREAM_LEVELS_B
WHERE cle_id IN (SELECT id
FROM OKC_k_LINES_B cp
WHERE cp.cle_id = p_top_line_id
and cp.lse_id in (35,7,8,9,10,11,13,18,25));
delete FROM OKS_STREAM_LEVELS_B
WHERE cle_id = p_top_line_id;
UPDATE oks_k_lines_b
set billing_schedule_type = NULL
WHERE cle_id IN (SELECT id
FROM OKC_k_LINES_B cp
WHERE cp.cle_id = p_top_line_id
and cp.lse_id in (35,7,8,9,10,11,13,18,25));
SELECT id, trunc(date_start) date_start,
amount, TRUNC(DATE_end) date_end, object_version_number
FROM oks_level_elements
WHERE cle_id = p_top_line_id
ORDER BY date_start;
SELECT id, trunc(date_start) date_start,
amount
FROM oks_level_elements
WHERE cle_id = p_sub_line_id
ORDER BY date_start;
SELECT nvl(billing_schedule_type,'T') billing_schedule_type
FROM oks_k_lines_b
WHERE cle_id = p_sub_line_id;
l_top_bs_tbl.DELETE;
l_cp_bs_tbl.DELETE;
OKS_BILL_LEVEL_ELEMENTS_PVT.update_row(
p_api_version => l_api_version,
p_init_msg_list => l_init_msg_list,
x_return_status => x_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_letv_tbl => l_top_bs_tbl,
x_letv_tbl => l_lvl_ele_tbl_out);
DELETE FROM OKS_LEVEL_ELEMENTS
WHERE cle_id = p_sub_line_id;
Delete oks_stream_levels_b
where cle_id = p_sub_line_id;
PROCEDURE update_bs_interface_date(p_top_line_id IN NUMBER,
p_invoice_rule_id IN Number,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2)
IS
CURSOR l_line_csr IS
SELECT ln.id id, ln.lse_id lse_id, nvl(TRUNC(ln.date_terminated -1),ln.end_date) line_end_date,
dtl.usage_type usage_type, dtl.billing_schedule_type billing_schedule_type
FROM okc_k_lines_b ln, oks_k_lines_b dtl
WHERE ln.id = p_top_line_id
AND dtl.cle_id = ln.id;
SELECT id ,nvl(TRUNC(date_terminated -1),end_date) cp_end_date, lse_id
FROM okc_k_lines_b
WHERE cle_id = p_top_line_id and lse_id in (35,7,8,9,10,11,13,18,25);
SELECT id, trunc(date_start) date_start,
date_to_interface, date_transaction, date_end
FROM oks_level_elements
WHERE cle_id = p_top_line_id
AND date_completed IS NOT NULL
ORDER BY date_start;
l_update_bs_tbl oks_bill_level_elements_pvt.letv_tbl_type;
l_lvl_ele_tbl_in.DELETE;
x_bs_tbl => l_update_bs_tbl,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
IF l_update_bs_tbl.COUNT <= 0 THEN
RETURN;
FOR L_index IN l_update_bs_tbl.FIRST .. l_update_bs_tbl.LAST
LOOP
l_lvl_ele_tbl_in(l_lvl_index).id := l_update_bs_tbl(l_index).id;
l_lvl_ele_tbl_in(l_lvl_index).date_start := l_update_bs_tbl(l_index).date_start;
l_lvl_ele_tbl_in(l_lvl_index).date_to_interface := l_update_bs_tbl(l_index).date_to_interface;
l_lvl_ele_tbl_in(l_lvl_index).object_version_number := l_update_bs_tbl(l_index).object_version_number;
l_lvl_ele_tbl_in(l_lvl_index).date_to_interface := l_update_bs_tbl(l_index).date_to_interface;
l_lvl_ele_tbl_in(l_lvl_index).date_transaction := l_update_bs_tbl(l_index).date_transaction;
x_bs_tbl => l_update_bs_tbl,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
IF l_update_bs_tbl.COUNT > 0 THEN
FOR L_index IN l_update_bs_tbl.FIRST .. l_update_bs_tbl.LAST
LOOP
l_lvl_ele_tbl_in(l_lvl_index).id := l_update_bs_tbl(l_index).id;
l_lvl_ele_tbl_in(l_lvl_index).date_start := l_update_bs_tbl(l_index).date_start;
l_lvl_ele_tbl_in(l_lvl_index).date_to_interface := l_update_bs_tbl(l_index).date_to_interface;
l_lvl_ele_tbl_in(l_lvl_index).object_version_number := l_update_bs_tbl(l_index).object_version_number;
END IF; ---- l_update_bs_tbl.COUNT > 0
l_line_tbl_in.DELETE;
FOR L_index IN l_update_bs_tbl.FIRST .. l_update_bs_tbl.LAST
LOOP
l_line_tbl_in(l_top_index).id := l_update_bs_tbl(l_index).id;
l_line_tbl_in(l_top_index).date_start := l_update_bs_tbl(l_index).date_start;
l_line_tbl_in(l_top_index).date_to_interface := l_update_bs_tbl(l_index).date_to_interface;
l_line_tbl_in(l_top_index).date_transaction := l_update_bs_tbl(l_index).date_transaction;
OKS_BILL_LEVEL_ELEMENTS_PVT.update_row(
p_api_version => l_api_version,
p_init_msg_list => l_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_letv_tbl => l_lvl_ele_tbl_in,
x_letv_tbl => l_lvl_ele_tbl_out);
END update_bs_interface_date;
SELECT id, cle_id, chr_id, dnz_chr_id , uom_code,
sequence_no, Start_Date, end_Date, level_periods,
uom_per_period, level_amount, invoice_offset_days, interface_offset_days
FROM OKS_STREAM_LEVELS_B
WHERE cle_id = l_line_id
ORDER BY sequence_no;
SELECT id,date_start, date_end, date_to_interface,
date_transaction , object_version_number
FROM oks_level_elements
WHERE rul_id = l_sll_id AND date_completed IS NULL
ORDER BY date_start;
l_bs_tbl.delete;
SELECT id
FROM OKC_K_LINES_b
WHERE chr_id = p_contract_id
AND lse_id IN (1, 12, 14, 19, 46);
SELECT nvl(SUM(amount) ,0) sub_amt
FROM OKS_SUBSCR_ELEMENTS
WHERE dnz_cle_id = p_line_id;
l_lvl_ele_tbl_in.delete;
Delete_lvl_element(p_cle_id => p_line_rec.id,
x_return_status => x_return_status);
null; ---donot insert record in level element
l_lvl_ele_tbl_in(l_tbl_seq).last_updated_by := OKC_API.G_MISS_NUM;
l_lvl_ele_tbl_in(l_tbl_seq).last_update_date := SYSDATE;
OKS_BILL_LEVEL_ELEMENTS_PVT.insert_row(
p_api_version => l_api_version,
p_init_msg_list => l_init_msg_list,
x_return_status => x_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_letv_tbl => l_lvl_ele_tbl_in,
x_letv_tbl => l_lvl_ele_tbl_out);
SELECT id,sequence_no,TRUNC(start_date) start_date, level_periods,
uom_per_period, uom_code, TRUNC(end_date) end_date,
interface_offset_days, invoice_offset_days, cle_id, dnz_chr_id,
chr_id, level_amount
FROM OKS_STREAM_LEVELS_B
WHERE cle_id = p_top_line_id
ORDER BY sequence_no;
SELECT line.chr_id chr_id, line.dnz_chr_id dnz_chr_id, line.id id, line.lse_id lse_id,
TRUNC(line.start_date) line_start_dt, TRUNC(line.end_date) line_end_dt,
TRUNC(line.date_terminated) line_term_dt, line.inv_rule_id inv_rule_id,
nvl(dtl.billing_schedule_type,'E') billing_schedule_type,
(nvl(line.price_negotiated,0) + nvl(dtl.ubt_amount,0) +
nvl(dtl.credit_amount,0) + nvl(dtl.suppressed_credit,0) ) line_amt
FROM okc_k_lines_b line, oks_k_lines_b dtl
WHERE line.id = dtl.cle_id AND line.Id = p_top_line_id ;
l_update_end_date VARCHAR2(1);
l_update_end_date := 'N';
DELETE FROM OKS_LEVEL_ELEMENTS
WHERE date_completed IS NULL
AND cle_id = p_top_line_id;
l_sll_in_tbl.DELETE;
l_update_end_date := 'Y';
IF l_update_end_date = 'Y' THEN ---Migrated
OKS_BILL_SCH.UPDATE_BS_ENDDATE(p_line_id => p_top_line_id,
p_chr_id => NULL,
x_return_status => x_return_status);
SELECT id,sequence_no,TRUNC(start_date) start_date, level_periods,
uom_per_period, uom_code, TRUNC(end_date) end_date,
interface_offset_days, invoice_offset_days, cle_id, dnz_chr_id,
chr_id, level_amount
FROM OKS_STREAM_LEVELS_B
WHERE cle_id = p_line_id
ORDER BY sequence_no;
l_update_end_date VARCHAR2(1) := 'N';
l_update_end_date := 'N';
l_sll_tbl.DELETE;
l_update_end_date := 'Y';
IF l_update_end_date = 'Y' THEN ---Migrated
OKS_BILL_SCH.UPDATE_BS_ENDDATE(p_line_id => p_top_line_id,
p_chr_id => NULL,
x_return_status => x_return_status);
x_sll_tbl.delete ;
SELECT line.chr_id chr_id, line.dnz_chr_id dnz_chr_id, line.id id, line.lse_id lse_id,
TRUNC(line.start_date) line_start_dt, TRUNC(line.end_date) line_end_dt,
line.inv_rule_id inv_rule_id, line.cle_id cle_id,
dtl.billing_schedule_type billing_schedule_type,
(nvl(line.price_negotiated,0) + nvl(dtl.ubt_amount,0) +
nvl(dtl.credit_amount,0) + nvl(dtl.suppressed_credit,0) ) line_amt
FROM okc_k_lines_b line, oks_k_lines_b dtl
WHERE line.id = dtl.cle_id AND line.Id = p_top_line_id
AND line.date_cancelled is null; -- 18-JAN-2006-maanand-Fixed Enhancement#4930700
SELECT line.id subline_id, TRUNC(line.start_date) cp_start_dt,
TRUNC(line.end_date) cp_end_dt, TRUNC(line.date_terminated) cp_prev_term_dt,
dtl.billing_schedule_type billing_schedule_type,
dtl.full_credit full_credit,lse_id cp_lse_id,dtl.price_uom,
(nvl(line.price_negotiated,0) + nvl(dtl.ubt_amount,0) +
nvl(dtl.credit_amount,0) + nvl(dtl.suppressed_credit,0) ) subline_amt
FROM okc_k_lines_b line, oks_k_lines_b dtl
WHERE line.cle_id = l_line_id
AND line.id = dtl.cle_id
AND ((l_style_id = 1 and line.lse_id in (35,7,8,9,10,11))
OR (l_style_id = 12 and line.lse_id = 13)
OR (l_style_id = 14 and line.lse_id = 18)
OR (l_style_id = 19 and line.lse_id = 25))
AND line.date_cancelled is null; -- 18-JAN-2006-maanand-Fixed Enhancement#4930700
Select line.price_negotiated
from okc_k_lines_b line
where line.id = p_id;
SELECT id, trunc(element.date_start) date_start,
amount,trunc(date_end) date_end,
object_version_number,date_transaction,date_to_interface
FROM oks_level_elements element
WHERE cle_id = p_line_id
ORDER BY date_start;
SELECT line.id subline_id, TRUNC(line.start_date) cp_start_dt,
TRUNC(line.end_date) cp_end_dt, dtl.billing_schedule_type billing_schedule_type,dtl.price_uom,
(nvl(line.price_negotiated,0) + nvl(dtl.ubt_amount,0) +
nvl(dtl.credit_amount,0) + nvl(dtl.suppressed_credit,0) ) subline_amt
FROM okc_k_lines_b line, oks_k_lines_b dtl
WHERE line.id = p_sub_line_id
AND line.id = dtl.cle_id
AND line.date_cancelled is null; -- 18-JAN-2006-maanand-Fixed Enhancement#4930700
l_sll_in_tbl.DELETE;
l_top_bs_tbl.DELETE;
l_sll_in_tbl.DELETE;
OKS_BILL_LEVEL_ELEMENTS_PVT.update_row(
p_api_version => l_api_version,
p_init_msg_list => l_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_letv_tbl => l_top_bs_tbl,
x_letv_tbl => l_lvl_ele_tbl_out);
fnd_log.string(fnd_log.level_event,G_MODULE_CURRENT||'.terminate_bill_sch.update_bs',
'oks_bill_level_elements_pvt.update_row(x_return_status = '||x_return_status
||', top bs count = '||l_lvl_ele_tbl_out.count ||')');
l_sll_in_tbl.DELETE;
l_top_bs_tbl.DELETE;
OKS_BILL_LEVEL_ELEMENTS_PVT.update_row(
p_api_version => l_api_version,
p_init_msg_list => l_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_letv_tbl => l_top_bs_tbl,
x_letv_tbl => l_lvl_ele_tbl_out);
fnd_log.string(fnd_log.level_event,G_MODULE_CURRENT||'.terminate_bill_sch.update_bs',
'oks_bill_level_elements_pvt.update_row(x_return_status = '||x_return_status
||', top bs count = '||l_lvl_ele_tbl_out.count ||')');
END IF; ---END OF update of top line sch
l_lvl_ele_tbl_in.delete;
Delete_lvl_element(p_cle_id => p_SubLine_rec.cp_id,
x_return_status => x_return_status);
SELECT nvl(BILLED_AT_SOURCE , 'N')
INTO l_billed_at_source
FROM OKC_K_HEADERS_ALL_B
WHERE id = p_Line_rec.dnz_chr_id;
l_lvl_ele_tbl_in(l_tbl_seq).last_updated_by := OKC_API.G_MISS_NUM;
l_lvl_ele_tbl_in(l_tbl_seq).last_update_date := SYSDATE;
OKS_BILL_LEVEL_ELEMENTS_PVT.insert_row(
p_api_version => l_api_version,
p_init_msg_list => l_init_msg_list,
x_return_status => x_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_letv_tbl => l_lvl_ele_tbl_in,
x_letv_tbl => l_lvl_ele_tbl_out);
SELECT hdr.id, TRUNC(hdr.start_date) start_dt,
nvl(trunc(hdr.date_terminated - 1) ,TRUNC(hdr.end_date)) end_dt,
hdr.inv_rule_id inv_rule_id, dtl.billing_schedule_type billing_schedule_type
FROM okc_k_headers_b hdr, oks_k_headers_b dtl
WHERE hdr.id = dtl.chr_id
AND hdr.Id = p_contract_id ;
SELECT id,sequence_no,TRUNC(start_date) start_date, level_periods,
uom_per_period, uom_code, TRUNC(end_date) end_date,
interface_offset_days, invoice_offset_days, cle_id, dnz_chr_id,
chr_id, level_amount
FROM OKS_STREAM_LEVELS_B
WHERE chr_id = p_contract_id
ORDER BY sequence_no;
l_sll_tbl.DELETE;
DELETE FROM OKS_LEVEL_ELEMENTS
WHERE rul_id IN (SELECT sll.id
FROM OKS_STREAM_LEVELS_B sll
WHERE sll.cle_id = p_line_id);
DELETE FROM OKS_STREAM_LEVELS_B
WHERE cle_id = p_line_id;
UPDATE oks_k_lines_b
SET billing_schedule_type = 'P'
WHERE cle_id = p_line_id;
Procedure Delete_contract_bs_sll
(
p_contract_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2)
IS
BEGIN
x_return_status := 'S';
DELETE FROM OKS_LEVEL_ELEMENTS
WHERE dnz_chr_id = p_contract_id;
DELETE FROM OKS_STREAM_LEVELS_B
WHERE dnz_chr_id = p_contract_id;
END Delete_contract_bs_sll;
SELECT sll.start_date,sll.uom_code, sll.uom_per_period,
element.id,element.sequence_number,element.date_start,
element.date_end,element.date_completed
FROM oks_level_elements element, oks_stream_levels_b sll
WHERE sll.id = element.rul_id
AND sll.cle_id = p_line_id
ORDER BY element.date_start;
SELECT id, start_date, OKC_TIME_UTIL_PUB.get_enddate(
start_date,
uom_code,
uom_per_period * level_periods) sll_end_date
FROM oks_stream_levels_b
WHERE cle_id = p_line_id;
SELECT date_billed_to
FROM oks_bill_cont_lines
WHERE cle_id = p_line_id
AND TRUNC(date_billed_from) = TRUNC(p_start_date)
AND bill_action = 'RI';
SELECT bsl.date_billed_to
FROM oks_bill_sub_lines bsl, oks_bill_cont_lines bcl
WHERE bsl.cle_id = p_line_id
AND TRUNC(bsl.date_billed_from) = TRUNC(p_start_date)
AND bsl.bcl_id = bcl.id
AND bcl.bill_action = 'RI';
l_lvl_ele_tbl_in.DELETE;
update oks_stream_levels_b set end_date = l_line_sll_rec.sll_end_date
WHERE id = l_line_sll_rec.id;
UPDATE oks_level_elements SET date_end = TRUNC(l_lvl_ele_tbl_in(i).date_end)
WHERE id = l_lvl_ele_tbl_in(i).id;
Procedure UPDATE_BS_ENDDATE(p_line_id IN NUMBER,
p_chr_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2)
IS
Cursor l_hdrSch_Csr Is
SELECT sll.uom_code, sll.uom_per_period,
element.id,element.date_start
FROM oks_level_elements element, oks_stream_levels_b sll
WHERE sll.id = element.rul_id
AND sll.chr_id = p_chr_id
ORDER BY element.date_start;
SELECT id, start_date, OKC_TIME_UTIL_PUB.get_enddate(
start_DATE,
uom_code,
uom_per_period * level_periods) sll_end_date
FROM oks_stream_levels_b
WHERE chr_id = p_chr_id;
SELECT TRUNC(end_date) end_date, trunc(date_terminated) date_terminated, lse_id
FROM okc_k_lines_b
WHERE id = p_line_id;
SELECT ID,TRUNC(end_date) end_date, trunc(date_terminated) date_terminated
FROM okc_k_lines_b
WHERE cle_id = p_line_id
AND lse_id in(35,7,8,9,10,11,13,18,25);
l_lvl_ele_tbl_in.DELETE;
update oks_stream_levels_b set end_date = l_hdr_sll_rec.sll_end_date
WHERE id = l_hdr_sll_rec.id;
UPDATE oks_level_elements SET date_end = TRUNC(l_lvl_ele_tbl_in(i).date_end)
WHERE id = l_lvl_ele_tbl_in(i).id;
END update_bs_enddate;
SELECT id, trunc(date_start) date_start,
amount, trunc(date_end) date_end
FROM oks_level_elements element
WHERE cle_id = p_cp_id
ORDER by date_start;
l_cp_bs_tbl.DELETE;
SELECT id, trunc(date_start) date_start,
date_to_interface, date_transaction, object_version_number
FROM oks_level_elements
WHERE cle_id = p_SubLine_id
AND date_completed IS NULL
ORDER BY date_start;
l_cp_bs_tbl.DELETE;
l_sll_prorate_tbl.DELETE;
END LOOP; ---END OF sll tbl UPDATE LOOP
null; ---donot insert record in level element
SELECT id , cle_id, dnz_chr_id ,
sequence_no, uom_code, start_date,
end_date, uom_per_period,advance_periods,level_periods,
level_amount, invoice_offset_days,interface_offset_days,
comments, due_arr_yn,amount,
lines_detailed_yn, security_group_id
FROM OKS_STREAM_LEVELS_B
WHERE cle_id = p_old_cp_id
ORDER BY START_DATE;
SELECT billing_schedule_type
FROM OKS_K_LINES_B
WHERE cle_id = p_old_cp_id;
SELECT id,end_date
FROM okc_k_lines_b
WHERE id = p_new_cp_id;
l_strm_lvl_tbl_in.DELETE;
l_strm_lvl_tbl_in(l_sll_index).last_updated_by := OKC_API.G_MISS_NUM;
l_strm_lvl_tbl_in(l_sll_index).last_update_date := SYSDATE;
OKS_SLL_PVT.insert_row(
p_api_version => l_api_version,
p_init_msg_list => l_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_sllv_tbl => l_strm_lvl_tbl_in,
x_sllv_tbl => l_strm_lvl_tbl_out);
fnd_log.string(fnd_log.level_event,G_MODULE_CURRENT||'.adjust_replace_product_bs.insert_sll',
'OKS_SLL_PVT.insert_row(x_return_status = '||x_return_status
||', sll tbl out count = '||l_strm_lvl_tbl_out.count||')');
fnd_log.string(fnd_log.level_statement,G_MODULE_CURRENT||'.adjust_replace_product_bs.update_lvl_elements',
'sll rule id = ' || l_strm_lvl_tbl_out(l_sll_index).id
||', sll end date = '|| l_sll_end_date);
UPDATE oks_level_elements
SET rul_id = l_strm_lvl_tbl_out(l_sll_index).id,
cle_id = p_new_cp_id
WHERE TRUNC(date_start) <= TRUNC(l_sll_end_date)
AND TRUNC(date_start) >= TRUNC(l_strm_lvl_tbl_out(l_sll_index).start_date)
AND cle_id = p_old_cp_id;
fnd_log.string(fnd_log.level_event,G_MODULE_CURRENT||'.adjust_replace_product_bs.update_lvl_amt',
'updated level elements = ' || sql%rowcount);
UPDATE oks_k_lines_b
SET billing_schedule_type = l_old_bill_type
WHERE cle_id = p_new_cp_id;
fnd_log.string(fnd_log.level_event,G_MODULE_CURRENT||'.adjust_replace_product_bs.update_bill_type',
'update bill schedule type of new line to = ' || l_old_bill_type);
UPDATE oks_stream_levels_b
SET level_amount = 0
WHERE cle_id = p_old_cp_id;
fnd_log.string(fnd_log.level_event,G_MODULE_CURRENT||'.adjust_replace_product_bs.update_oldsll_amt',
'update lvl amt of old line to zero = ' || sql%rowcount );
UPDATE oks_bill_sub_lines
SET cle_id = p_new_cp_id
WHERE cle_id = p_old_cp_id;
fnd_log.string(fnd_log.level_event,G_MODULE_CURRENT||'.adjust_replace_product_bs.update_bcl',
'updated bcl count = ' || sql%rowcount );
SELECT line.id line_id, TRUNC(line.start_date) start_dt,
TRUNC(line.end_date) end_dt, line.dnz_chr_id dnz_chr_id, line.lse_id,
line.cle_id parent_id,line.inv_rule_id inv_rule_id,
(nvl(line.price_negotiated,0) + nvl(dtl.ubt_amount,0) +
nvl(dtl.credit_amount,0) + nvl(dtl.suppressed_credit,0) ) line_amt,
dtl.billing_schedule_type billing_schedule_type
FROM okc_k_lines_b line, oks_k_lines_b dtl
WHERE line.id= p_line_id
AND line.id = dtl.cle_id;
SELECT id,trunc(date_start) date_start,
amount,trunc(date_end) date_end,object_version_number,
date_to_interface, date_transaction,date_completed
FROM oks_level_elements
WHERE cle_id = p_line_id
ORDER BY date_start;
SELECT id
FROM oks_level_elements
WHERE cle_id = p_line_id;
SELECT SUM(NVL(amount,0)) tot_amt
FROM oks_level_elements
WHERE cle_id = p_line_id;
Select line.price_negotiated
from okc_k_lines_b line
where line.id = p_id;
l_top_bs_tbl.DELETE;
l_cp_old_bs_tbl.DELETE;
l_new_sll_tbl.DELETE;
UPDATE OKS_K_LINES_B SET billing_schedule_type = l_billing_type
WHERE cle_id = l_new_cp_rec.cp_id;
fnd_log.string(fnd_log.level_event,G_MODULE_CURRENT||'.adjust_split_bill_sch.update_bill_type',
'updated new sub line billing type = ' || sql%rowcount);
l_strm_lvl_tbl_in.DELETE;
l_strm_lvl_tbl_in(l_index).last_updated_by := OKC_API.G_MISS_NUM;
l_strm_lvl_tbl_in(l_index).last_update_date := SYSDATE;
OKS_SLL_PVT.insert_row(
p_api_version => l_api_version,
p_init_msg_list => l_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_sllv_tbl => l_strm_lvl_tbl_in,
x_sllv_tbl => l_strm_lvl_tbl_out);
fnd_log.string(fnd_log.level_event,G_MODULE_CURRENT||'.adjust_split_bill_sch.insert_sll',
'OKS_SLL_PVT.insert_row(x_return_status = '||x_return_status
||', sll tbl out count = '||l_strm_lvl_tbl_out.count||')');
l_cp_sll_out_tbl.DELETE;
l_cp_sll_out_tbl.DELETE;
UPDATE oks_stream_levels_b
SET level_amount = l_sll_db_tbl(l_index).level_amount
WHERE id = l_sll_db_tbl(l_index).id;
fnd_log.string(fnd_log.level_event,G_MODULE_CURRENT||'.adjust_split_bill_sch.old_sll_amt_update',
'old sll amount update = '|| l_sll_db_tbl(l_index).level_amount
||', sll id = '|| l_sll_db_tbl(l_index).id );
UPDATE OKS_LEVEL_ELEMENTS
SET amount = l_top_bs_tbl(l_index).amount
WHERE id = l_top_bs_tbl(l_index).id;
fnd_log.string(fnd_log.level_event,G_MODULE_CURRENT||'.Adjust_split_bill_sch.update_top_lvl_amt',
'updated level elemnets of top line= ' || sql%rowcount
|| ' , id = ' || l_top_bs_tbl(l_index).id
|| ' , amt = ' || l_top_bs_tbl(l_index).amount );
SELECT id,trunc(date_start) date_start,
amount,trunc(date_end) date_end,date_completed
FROM oks_level_elements
WHERE cle_id = p_line_id
ORDER BY date_start;
x_new_cp_bs_tbl.DELETE;
UPDATE OKS_LEVEL_ELEMENTS
SET amount = p_old_cp_bs_tbl(l_index).amount
WHERE id = p_old_cp_bs_tbl(l_index).id;
fnd_log.string(fnd_log.level_event,G_MODULE_CURRENT||'.Adjust_billed_lvl_element.update_old_lvl_amt',
'updated level elemnets = ' || sql%rowcount
|| ' , id = ' || p_old_cp_bs_tbl(l_index).id
|| ' , amt = ' || p_old_cp_bs_tbl(l_index).amount );
/* This procedure updates the amount on the top line when the status of sub-line is
changed from 'Entered' to 'Cancelled' or 'Cancelled' to 'Entered'.
*/
PROCEDURE Sts_change_subline_lvl_rule(
p_cle_id IN NUMBER,
p_from_ste_code IN VARCHAR2,
p_to_ste_code IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2)
IS
-- to get the top line details
CURSOR l_line_BS_csr(l_top_line_id Number) IS
SELECT id, trunc(date_start) date_start,
amount, TRUNC(DATE_end) date_end, object_version_number
FROM oks_level_elements
WHERE cle_id = l_top_line_id
ORDER BY date_start;
SELECT id, trunc(date_start) date_start,
amount
FROM oks_level_elements
WHERE cle_id = p_cle_id
ORDER BY date_start;
SELECT nvl(billing_schedule_type,'T') billing_schedule_type
FROM oks_k_lines_b
WHERE cle_id = p_cle_id;
select cle_id
from okc_k_lines_b
where id=p_cle_id;
SELECT 1
FROM okc_k_lines_b
WHERE id = p_top_line_id
AND price_negotiated = (select sum(amount) from oks_level_elements ole1
where ole1.parent_cle_id = p_top_line_id
and ole1.object_version_number = ( select max(object_version_number)
from oks_level_elements ole2
where ole2.parent_cle_id = p_top_line_id
)
);
l_top_bs_tbl.DELETE;
l_cp_bs_tbl.DELETE;
OKS_BILL_LEVEL_ELEMENTS_PVT.update_row(
p_api_version => l_api_version,
p_init_msg_list => l_init_msg_list,
x_return_status => x_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_letv_tbl => l_top_bs_tbl,
x_letv_tbl => l_lvl_ele_tbl_out);