[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT fixed_quantity fixed_qty
,minimum_quantity minimum_qty
,default_quantity default_qty
,amcv_flag amcv_flag
,usage_period usage_period
,usage_duration usage_duration
,level_yn level_yn
,base_reading base_reading
,usage_type usage_Type
FROM oks_k_lines_b
WHERE cle_id = p_id ;
SELECT Min(bsl.DATE_BILLED_FROM)
,Max(bsl.DATE_BILLED_TO)
FROM oks_bill_cont_lines bcl ,
oks_bill_sub_lines bsl
WHERE bsl.cle_id = p_cle_id
AND bsl.bcl_id = bcl.id
AND bcl.bill_action <> 'AVG';
SELECT NVL(Sum(NVL(bsd.Result,0)),0)
FROM oks_bill_cont_lines bcl,
oks_bill_sub_lines bsl,
oks_bill_sub_line_dtls bsd
WHERE bsl.cle_id = p_cle_id
AND bsl.bcl_id = bcl.id
AND bcl.bill_action <> 'AVG'
AND bsd.bsl_id = bsl.id;
SELECT uom_code
FROM oks_stream_levels_b
WHERE id = p_rul_id;
SELECT item.Object1_id1
,mtl.usage_item_flag
,mtl.service_item_flag
,mtl.primary_uom_code
FROM Okc_K_items Item
,mtl_system_items_b mtl --Okx_system_items_v mtl
WHERE item.cle_id = p_cle_id
--AND mtl.id1 = item.object1_id1
AND mtl.inventory_item_id = item.object1_id1
AND mtl.organization_id = p_org_id;
SELECT usage_type Usage_Type,
usage_period Usage_period,
prorate Prorate,
locked_price_list_id locked_price_list_id,
locked_price_list_line_id locked_price_list_line_id
FROM OKS_K_LINES_B
WHERE cle_id = p_cle_id ;
SELECT okc.Number_of_items
,tl.Unit_of_measure uom_code
FROM okc_k_items okc
,mtl_units_of_measure_tl tl
WHERE okc.cle_id = p_cle_id
AND tl.uom_code = okc.uom_code
AND tl.language = USERENV('LANG');
SELECT Number_of_items
,OKX.Unit_of_measure uom_code
FROM OKC_K_ITEMS OKC
,OKX_UNITS_OF_MEASURE_V OKX
WHERE cle_id = P_cle_id
AND Okx.uom_code = OKC.uom_code ;
SELECT bsl.id bsl_id, bsl.average average, bsd.unit_of_measure uom_code,
bsl.date_billed_from ,bsl.date_billed_to,
bsl.cle_id, rline.prorate,
rline.locked_price_list_id,
rline.locked_price_list_line_id,
rline.dnz_chr_id
FROM
oks_k_lines_b rline,
oks_bill_sub_lines bsl,
oks_bill_sub_line_dtls bsd
WHERE bsl.bcl_id = p_bcl_id
AND bsl.id = bsd.bsl_id
AND rline.cle_id = bsl.cle_id
AND p_prv = 1
UNION
SELECT bsl.id bsl_id, bsl.average average, bsd.unit_of_measure uom_code,
bsl.date_billed_from ,bsl.date_billed_to,
bsl.cle_id , rline.prorate,
rline.LOCKED_PRICE_LIST_ID,
rline.locked_price_list_line_id,
rline.dnz_chr_id
FROM
oks_k_lines_b rline,
oks_bsl_pr bsl,
oks_bsd_pr bsd
WHERE bsl.bcl_id = p_bcl_id
AND bsl.id = bsd.bsl_id
AND rline.cle_id = bsl.cle_id
AND p_prv = 2;
SELECT
sub_line.id id
,sub_line.cle_id cle_id
,sub_line.dnz_chr_id dnz_chr_id
,sub_line.price_negotiated price_negotiated
,sub_line.start_date start_date
,sub_line.end_date end_date
,sub_line.date_terminated date_terminated
,sub_line.line_number line_number
,rul.fixed_quantity fixed_qty
,rul.minimum_quantity minimum_qty
,rul.default_quantity default_qty
,rul.amcv_flag amcv_flag
,rul.usage_period usage_period
,rul.usage_duration usage_duration
,rul.level_yn level_yn
,rul.base_reading base_reading
,rul.usage_type usage_Type
,rul.usage_est_yn usage_est_yn
,rul.usage_est_method usage_est_method
,rul.usage_est_start_date usage_est_start_date
FROM OKC_K_LINES_B sub_line ,
OKS_K_LINES_B rul
WHERE sub_line.cle_id = p_cle_id
AND sub_line.date_cancelled is NULL --[llc]
AND sub_line.id = rul.cle_id
AND sub_line.lse_id in (8,7,9,10,11,13,25,35)
AND not exists ( select 1 from okc_k_rel_objs rel
WHERE rel.cle_id = sub_line.id );
SELECT count(sub_line.id)
FROM OKC_K_LINES_B sub_line
WHERE sub_line.cle_id = p_cle_id
AND sub_line.lse_id in (8,7,9,10,11,13,25,35)
AND sub_line.date_cancelled is NULL --[llc]
AND not exists ( select 1 from okc_k_rel_objs rel
where rel.cle_id = sub_line.id );
select value_timestamp, counter_id
from cs_counter_values, okc_k_items
where cle_id = p_cle_id
and to_char(counter_id) = object1_id1
and counter_reading = p_lock_read;
select to_number(object1_id1)
from okc_k_items
where cle_id = p_cle_id;
SELECT lvl.amount
FROM oks_level_elements lvl
WHERE lvl.cle_id = p_id
And lvl.dnz_chr_id = p_hdr_id
And lvl.date_start = p_date_start
And lvl.date_end = p_date_end;
level_elements_tab.delete;
l_processed_lines_tbl.DELETE(l_pr_tbl_idx) ;
l_cov_tbl.delete;
OKS_BILL_REC_PUB.Insert_bcl
(P_CALLEDFROM => l_called_from,
X_RETURN_STAT => l_return_status,
P_CLE_ID => p_top_line_id,
P_DATE_BILLED_FROM => l_bill_start_date,
P_DATE_BILLED_TO => l_bill_end_date,
P_DATE_NEXT_INVOICE => l_ar_inv_date,
P_BILL_ACTION => 'RI',
P_OKL_FLAG => p_okl_flag,
P_PRV => p_prv,
P_MSG_COUNT => l_msg_count,
P_MSG_DATA => l_msg_data,
X_BCL_ID => l_bcl_id);
FND_FILE.PUT_LINE( FND_FILE.LOG, 'Failed in insert bcl');
UPDATE oks_level_elements
SET date_completed = l_bill_end_date
WHERE id = level_elements_tab(e_ptr).id;
level_coverage.delete;
/*Update date completed in coverage line */
IF (P_PRV <> 2) THEN
UPDATE oks_level_elements
SET date_completed = l_bill_end_date
WHERE id = level_coverage(1).id;
OKS_BILL_REC_PUB.Insert_all_subline
(
P_CALLEDFROM => l_called_from,
X_RETURN_STAT => l_return_status,
P_COVERED_TBL => l_cov_tbl,
P_CURRENCY_CODE => p_currency_code,
P_DNZ_CHR_ID => p_dnz_chr_id,
P_PRV => p_prv,
P_MSG_COUNT => l_msg_count,
P_MSG_DATA => l_msg_data
);
l_price_break_details.delete;
OKS_BILL_REC_PUB.update_bsl
(
X_RET_STAT => l_return_status,
P_DNZ_CHR_ID => p_dnz_chr_id,
P_BSL_ID => bsl_price_rec.bsl_id,
P_BCL_ID => l_bcl_id,
P_AMOUNT => l_price_rec.PROD_EXT_AMOUNT,
P_CURRENCY_CODE=> p_currency_code,
P_PRV => p_prv
);
FND_FILE.PUT_LINE( FND_FILE.LOG, 'Error update bsl'||' '||l_return_status);
/* Populate Price Break Record here and Insert Price Breaks
Details
*/
--FOR i in l_price_break_details.first..l_price_break_details.last
IF (l_price_break_details.COUNT) > 0 THEN
i := l_price_break_details.FIRST;
OKS_PBR_PVT.insert_row(
P_API_VERSION => 1,
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_PBRV_REC => l_pbr_rec_in,
X_PBRV_REC => l_pbr_rec_out) ;
SELECT sub_line.id id
,sub_line.cle_id cle_id
,sub_line.price_negotiated price_negotiated
,sub_line.start_date start_date
,sub_line.end_date end_date
,sub_line.date_terminated date_terminated
,sub_line.line_number line_number /* Report */
FROM OKC_K_LINES_B sub_line
WHERE sub_line.cle_id = p_cle_id
AND sub_line.date_cancelled is NULL -- [llc]
AND sub_line.lse_id in (8,7,9,10,11,13,25,35)
AND not exists ( select 1 from okc_k_rel_objs rel
where rel.cle_id = sub_line.id );
SELECT okc.Number_of_items
,tl.Unit_of_measure uom_code
FROM okc_k_items okc
,mtl_units_of_measure_tl tl
WHERE okc.cle_id = p_cle_id
AND tl.uom_code = okc.uom_code
AND tl.language = USERENV('LANG');
Select Number_of_items
,OKX.Unit_of_measure uom_code
From OKC_K_ITEMS OKC
,OKX_UNITS_OF_MEASURE_V OKX
Where cle_id = P_cle_id
And Okx.uom_code = OKC.uom_code ;
SELECT count(sub_line.id)
FROM OKC_K_LINES_B sub_line
WHERE sub_line.cle_id = p_cle_id
AND sub_line.date_cancelled is NULL -- [llc]
AND sub_line.lse_id in (8,7,9,10,11,13,25,35)
AND not exists ( select 1 from okc_k_rel_objs rel
where rel.cle_id = sub_line.id );
Select sum(AMOUNT)
FROM oks_level_elements
where CLE_ID= p_cle_id
AND DATE_TO_INTERFACE <= p_date
AND DATE_COMPLETED IS NULL;
level_elements_tab.delete;
l_processed_sub_lines_tbl.DELETE(l_prs_tbl_idx) ;
l_cov_tbl.delete;
OKS_BILL_REC_PUB.Insert_bcl
(
P_CALLEDFROM => l_called_from,
X_RETURN_STAT => l_return_status,
P_CLE_ID => p_top_line_id,
P_DATE_BILLED_FROM => l_calc_rec.l_calc_sdate,
P_DATE_BILLED_TO => l_calc_rec.l_calc_edate,
P_DATE_NEXT_INVOICE => l_ar_inv_date,
P_BILL_ACTION => 'RI',
P_OKL_FLAG => p_okl_flag,
P_PRV => p_prv,
P_MSG_COUNT => l_msg_count,
P_MSG_DATA => l_msg_data,
X_BCL_ID => l_bcl_id
);
FND_FILE.PUT_LINE(FND_FILE.LOG,'Bill_Service_Item => After calling OKS_BILL_REC_PUB.Insert_bcl l_return_status '||l_return_status);
FND_FILE.PUT_LINE( FND_FILE.LOG,'Bill_Service_Item => Failed in Insert BCL For coverage Id: ' ||l_covlvl_rec.id );
UPDATE oks_level_elements
SET date_Completed = l_calc_rec.l_calc_edate
WHERE id = level_elements_tab(e_ptr).id;
OKS_BILL_REC_PUB.Insert_all_subline
(
P_CALLEDFROM => l_called_from,
X_RETURN_STAT => l_return_status,
P_COVERED_TBL => l_cov_tbl,
P_CURRENCY_CODE => p_currency_code,
P_DNZ_CHR_ID => p_dnz_chr_id,
P_PRV => p_prv,
P_MSG_COUNT => l_msg_count,
P_MSG_DATA => l_msg_data
);
FND_FILE.PUT_LINE(FND_FILE.LOG,'Bill_Service_Item => After calling OKS_BILL_REC_PUB.Insert_all_subline l_return_status '||l_return_status);
FND_FILE.PUT_LINE( FND_FILE.LOG, 'Bill_Service_Item => Insert into sublines table failed Contract line id :'||p_top_line_id);
/* Following code is to update the date_completed field of top line */
IF (p_prv <> 2) THEN
UPDATE oks_level_elements
SET date_completed = l_calc_rec.l_calc_edate
WHERE cle_id = p_top_line_id
AND date_completed is null
AND date_start <= l_calc_rec.l_calc_sdate;
/* ** Delete the record from billing report table if no subline is billed***/
IF (l_level_elements_count <= 0) THEN
l_processed_lines_tbl.DELETE(l_pr_tbl_idx) ;
level_elements_tab.delete;
l_cov_tbl.delete;
/*This procedure insert the into bill_con_lines,
if the record is not already present for the same period.
Since the out table returns the row_id of the inserted row, get_bcl_id
which was present in earliar version is now removed*/
OKS_BILL_REC_PUB.Insert_bcl
(
P_CALLEDFROM => l_called_from,
X_RETURN_STAT => l_return_status,
P_CLE_ID => p_top_line_id,
P_DATE_BILLED_FROM => l_bill_start_date,
P_DATE_BILLED_TO => l_bill_end_date,
P_DATE_NEXT_INVOICE => l_ar_inv_date,
P_BILL_ACTION => 'RI',
P_OKL_FLAG => p_okl_flag,
P_PRV => p_prv,
P_MSG_COUNT => l_msg_count,
P_MSG_DATA => l_msg_data,
X_BCL_ID => l_bcl_id
);
FND_FILE.PUT_LINE( FND_FILE.LOG, 'Failed in insert bcl ');
UPDATE oks_level_elements
SET date_completed = l_bill_end_date
WHERE id = level_elements_tab(e_ptr).id;
OKS_BILL_REC_PUB.Insert_all_subline
(
P_CALLEDFROM => l_called_from,
X_RETURN_STAT => l_return_status,
P_COVERED_TBL => l_cov_tbl,
P_CURRENCY_CODE => p_currency_code,
P_DNZ_CHR_ID => p_dnz_chr_id,
P_PRV => p_prv,
P_MSG_COUNT => l_msg_count,
P_MSG_DATA => l_msg_data
);
FND_FILE.PUT_LINE( FND_FILE.LOG, 'Status after insert into sublines '||l_return_status );
FND_FILE.PUT_LINE( FND_FILE.LOG, 'Insert into sublines table failed Contract line id : '||p_top_line_id);
procedure update_version (
p_dnz_chr_id IN NUMBER
) IS pragma autonomous_transaction;
l_con_update_date date;
Cursor l_contract_update_date(p_chr_id IN NUMBER) is
SELECT last_update_date from okc_k_headers_b
WHERE id = p_chr_id;
OPEN l_contract_update_date(p_dnz_chr_id);
FETCH l_contract_update_date into l_con_update_date;
CLOSE l_contract_update_date;
IF (trunc(l_con_update_date) <> trunc(sysdate)) THEN
okc_cvm_pvt.g_trans_id := 'XXX';
OKC_CVM_PVT.update_contract_version(
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_cnt,
X_MSG_DATA => l_msg_data,
P_CVMV_REC => l_cvmv_rec,
X_CVMV_REC => l_cvmv_out_rec);
FND_FILE.PUT_LINE( FND_FILE.LOG, 'OKS_BILLING_PUB.Calculate_bill => After calling OKC_CVM_PVT.update_contract_version l_return_status '||l_return_status);
l_chrv_rec.last_update_date := sysdate;
OKC_CONTRACT_PUB.update_contract_header(
P_API_VERSION => l_api_version,
X_RETURN_STATUS => l_return_status,
P_INIT_MSG_LIST => OKC_API.G_TRUE,
X_MSG_COUNT => l_msg_cnt,
X_MSG_DATA => l_msg_data,
P_RESTRICTED_UPDATE => OKC_API.G_TRUE,
P_CHRV_REC => l_chrv_rec,
X_CHRV_REC => l_chrv_out_rec);
FND_FILE.PUT_LINE( FND_FILE.LOG, 'OKS_BILLING_PUB.Calculate_bill => After calling OKC_CONTRACT_PUB.update_contract_header l_return_status '||l_return_status);
End update_version;
SELECT chr_id,cle_id,line_no
FROM oks_process_billing
WHERE line_no between p_line_from and p_line_to;
SELECT line.id
,Hdr.Contract_number
,Hdr.Contract_number_modifier
,Hdr.Currency_code
,Hdr.Inv_organization_id
,Hdr.authoring_org_id
,Hdr.org_id
,line.dnz_chr_id
,line.cle_id
,line.lse_id
,line.start_date
,line.end_date
,line.price_negotiated
,line.date_terminated
,okp.object1_id1
,okp.object1_id2
,line.line_number
,rul.ar_interface_yn
,rul.period_start
,rul.period_type
,rul.price_uom
,nvl(rul.summary_trx_yn,'N') summary_yn
,rline.settlement_interval
FROM
OKC_K_PARTY_ROLES_B okp
,OKC_K_LINES_B line
,OKS_K_LINES_B rline
,OKC_K_HEADERS_B Hdr
,OKS_K_HEADERS_B rul
WHERE Hdr.id = p_hdr_id
AND line.id = p_line_id
AND rline.cle_id = line.id
AND rul.chr_id = Hdr.id
AND line.dnz_chr_id = Hdr.id
AND line.lse_id in (1,12,19,46)
AND okp.dnz_chr_id = hdr.id
AND okp.rle_code in ( 'CUSTOMER','SUBSCRIBER');
SELECT item.Object1_id1
,mtl.usage_item_flag
,mtl.service_item_flag
,mtl.primary_uom_code
FROM Okc_K_items Item
,mtl_system_items_b mtl --Okx_system_items_v mtl
WHERE item.cle_id = p_cle_id
--AND mtl.id1 = item.object1_id1
AND mtl.inventory_item_id = item.object1_id1
AND mtl.organization_id = p_org_id;
SELECT okc.Number_of_items
,tl.Unit_of_measure uom_code
FROM okc_k_items okc
,mtl_units_of_measure_tl tl
WHERE okc.cle_id = p_cle_id
AND tl.uom_code = okc.uom_code
AND tl.language = USERENV('LANG');
SELECT Number_of_items
,OKX.Unit_of_measure uom_code
FROM OKC_K_ITEMS OKC
,OKX_UNITS_OF_MEASURE_V OKX
WHERE cle_id = P_cle_id
AND Okx.uom_code = OKC.uom_code ;
SELECT uom_code
FROM Okc_time_code_units_v
WHERE tce_code = 'DAY'
AND quantity = 1
AND active_flag = 'Y';
SELECT 1 from okc_k_rel_objs
WHERE rty_code in ('OKLSRV','OKLUBB')
AND jtot_object1_code = 'OKL_SERVICE'
AND object1_id1 = to_char(p_chr_id);
Cursor l_contract_update_date(p_chr_id IN NUMBER) is
SELECT last_update_date from okc_k_headers_b
WHERE id = p_chr_id;
l_select_counter NUMBER := 0;
l_con_update_date DATE ;
l_select_counter := l_select_counter + 1;
/* *** Insert the lines to a PL/SQL table ** */
-- l_pr_tbl_idx := l_pr_tbl_idx + 1;
Contract version updates is done for OKI.
OKI pulls data depending upon minor version change.
Bug# 5637820 - This procedure is made as an autonomous transaction
*/
update_version(p_dnz_chr_id => l_bill_rec.dnz_chr_id);
cle_id.delete;
chr_id.delete;
l_line_no.delete;
INSERT /*+ append */ into oks_process_billing (chr_id,cle_id,line_no,currency_code)
SELECT /*+ leading(lvl) ORDERED USE_HASH(line hdr sts as1)
swap_join_inputs(hdr) swap_join_inputs(sts) */
hdr.id hdr_id, line.id line_id, rownum, hdr.currency_code
from (
Select /*+ FULL (lvl) no_merge */ distinct lvl.parent_cle_id
from oks_level_elements lvl
where lvl.date_completed is null
and trunc(lvl.date_to_interface) <= trunc(p_date)
) lvl,
okc_k_lines_b line,
(
Select /*+ FULL (a) no_merge */ distinct scs_code,sts_code
from okc_assents a
where a.opn_code = 'INVOICE'
and a.allowed_yn = 'Y'
) as1,
okc_k_headers_b hdr,
(
Select /*+ FULL (osb) no_merge */ distinct code
from okc_statuses_b osb
where osb.ste_code <> 'HOLD'
) sts
WHERE Hdr.id = line.dnz_chr_id
AND Hdr.scs_code in ('SERVICE','WARRANTY','SUBSCRIPTION')
AND Hdr.Template_yn = 'N'
AND line.id = lvl.parent_cle_id
AND line.lse_id in (1,12,19,46)
AND line.sts_code = as1.sts_code
AND as1.scs_code = Hdr.scs_code
AND sts.code = Hdr.sts_code
AND line.id not in ( Select /*+ index_ffs (rel) HASH_AJ */ rel.cle_id
From okc_k_rel_objs rel
Where rel.cle_id is not null
);
INSERT /*+ append */ into oks_process_billing (chr_id,cle_id,line_no,currency_code)
SELECT /*+ leading(lvl) ORDERED USE_HASH(line hdr sts as1)
swap_join_inputs(hdr) swap_join_inputs(sts) */
hdr.id hdr_id, line.id line_id, rownum, hdr.currency_code
from (
Select /*+ FULL (lvl) no_merge */ distinct lvl.parent_cle_id
from oks_level_elements lvl
where lvl.date_completed is null
and trunc(lvl.date_to_interface) <= trunc(p_date)
) lvl,
okc_k_lines_b line,
(
Select /*+ FULL (a) no_merge */ distinct scs_code,sts_code
from okc_assents a
where a.opn_code = 'INVOICE'
and a.allowed_yn = 'Y'
) as1,
okc_k_headers_b hdr,
(
Select /*+ FULL (osb) no_merge */ distinct code
from okc_statuses_b osb
where osb.ste_code <> 'HOLD'
) sts
WHERE Hdr.id = line.dnz_chr_id
AND Hdr.scs_code in ('SERVICE','WARRANTY','SUBSCRIPTION')
AND Hdr.Authoring_Org_Id = l_org_id
AND Hdr.Template_yn = 'N'
AND line.id = lvl.parent_cle_id
AND line.lse_id in (1,12,19,46)
AND line.sts_code = as1.sts_code
AND as1.scs_code = Hdr.scs_code
AND sts.code = Hdr.sts_code
AND line.id not in ( Select /*+ index_ffs (rel) HASH_AJ */ rel.cle_id
From okc_k_rel_objs rel
Where rel.cle_id is not null
);
INSERT into oks_process_billing (chr_id,cle_id,line_no,currency_code)
SELECT hdr.id hdr_id,line.id line_id,rownum,hdr.currency_code
from okc_k_headers_b hdr, okc_k_lines_b line
WHERE Hdr.id = line.dnz_chr_id
AND Hdr.scs_code in ('SERVICE','WARRANTY','SUBSCRIPTION')
AND Hdr.Template_yn = 'N'
AND Hdr.id = p_contract_hdr_id
AND line.id IN (Select lvl.parent_cle_id
from oks_level_elements lvl
where lvl.date_completed is null
and trunc(lvl.date_to_interface) <= trunc(p_date) )
AND exists (Select 1 from okc_statuses_b osb
where osb.ste_code <> 'HOLD'
and osb.code = Hdr.sts_code )
AND exists ( SELECT 1 from okc_assents a
where line.sts_code = a.sts_code
and a.scs_code = Hdr.scs_code
and a.opn_code = 'INVOICE'
and a.allowed_yn = 'Y' )
AND line.lse_id in (1,12,19,46)
AND line.id not in ( Select rel.cle_id
From okc_k_rel_objs rel
Where rel.cle_id is not null );
INSERT /*+ append */ into oks_process_billing (chr_id,cle_id,line_no,currency_code)
SELECT /*+ leading(lvl) ORDERED USE_HASH(line hdr sts as1 cgp)
swap_join_inputs(hdr) swap_join_inputs(sts) swap_join_inputs(cgp) */
hdr.id hdr_id, line.id line_id, rownum, hdr.currency_code
from (
Select /*+ FULL (lvl) no_merge */ distinct lvl.parent_cle_id
from oks_level_elements lvl
where lvl.date_completed is null
and trunc(lvl.date_to_interface) <= trunc(p_date)
) lvl,
okc_k_lines_b line,
(
Select /*+ FULL (a) no_merge */ distinct scs_code,sts_code
from okc_assents a
where a.opn_code = 'INVOICE'
and a.allowed_yn = 'Y'
) as1,
okc_k_headers_b hdr,
(
Select /*+ FULL (osb) no_merge */ distinct code
from okc_statuses_b osb
where osb.ste_code <> 'HOLD'
) sts,
(
Select /*+ FULL (grp) no_merge */ distinct included_chr_id
from OKC_K_GRPINGS grp
where grp.cgp_parent_id = p_grp_id
) cgp
WHERE Hdr.id = line.dnz_chr_id
AND Hdr.scs_code in ('SERVICE','WARRANTY','SUBSCRIPTION')
AND Hdr.Template_yn = 'N'
AND Hdr.Authoring_Org_Id = l_org_id
AND line.id = lvl.parent_cle_id
AND line.lse_id in (1,12,19,46)
AND line.sts_code = as1.sts_code
AND as1.scs_code = Hdr.scs_code
AND sts.code = Hdr.sts_code
AND cgp.included_chr_id = Hdr.id
AND line.id not in ( Select /*+ index_ffs (rel) HASH_AJ */ rel.cle_id
From okc_k_rel_objs rel
Where rel.cle_id is not null
);
INSERT into oks_process_billing (chr_id,cle_id,line_no,currency_code)
SELECT hdr.id hdr_id,line.id line_id,rownum,hdr.currency_code
from okc_k_headers_b hdr, okc_k_lines_b line
WHERE Hdr.id = line.dnz_chr_id
AND Hdr.scs_code in ('SERVICE','WARRANTY','SUBSCRIPTION')
AND Hdr.Template_yn = 'N'
AND Hdr.Authoring_Org_Id = nvl(l_org_id, hdr.authoring_org_id)
AND Hdr.Scs_code = nvl(p_category, hdr.scs_code)
AND exists
(select 1 from OKC_K_PARTY_ROLES_B okp
where okp.dnz_chr_id = hdr.id
and okp.rle_code in ('CUSTOMER','SUBSCRIBER')
and okp.object1_id1 = nvl(P_customer_id, okp.object1_id1) )
AND exists
(select 1 from OKC_K_GRPINGS okg
where okg.included_chr_id = hdr.id
and okg.cgp_parent_id = nvl(p_grp_id, okg.cgp_parent_id) )
AND line.id IN (Select lvl.parent_cle_id
from oks_level_elements lvl
where lvl.date_completed is null
and trunc(lvl.date_to_interface) <= trunc(p_date) )
AND exists (Select 1 from okc_statuses_b osb
where osb.ste_code <> 'HOLD'
and osb.code = Hdr.sts_code )
AND exists ( SELECT 1 from okc_assents a
where line.sts_code = a.sts_code
and a.scs_code = Hdr.scs_code
and a.opn_code = 'INVOICE'
and a.allowed_yn = 'Y' )
AND line.lse_id in (1,12,19,46)
AND line.id not in ( Select rel.cle_id
From okc_k_rel_objs rel
Where rel.cle_id is not null );
select count(*) into l_line_no from oks_process_billing;
SELECT bcl.cle_id bcl_cle_id,
bsl.cle_id bsl_cle_id,
abs(bsl.amount) bsl_amount,
line.lse_id bcl_lse_id
FROM okc_k_headers_b hdr,
oks_bill_sub_lines bsl,
oks_bill_cont_lines bcl,
okc_k_lines_b line
WHERE line.id = bcl.cle_id
AND hdr.id = line.dnz_chr_id
AND hdr.id = nvl(p_contract_hdr_id,hdr.id) --Full Table Scan due to nvl condition
AND hdr.scs_code = nvl(p_category,hdr.scs_code)
--AND hdr.authoring_org_id = nvl(p_org_id,hdr.authoring_org_id)
AND hdr.org_id = nvl(p_org_id,hdr.org_id)
AND bcl.cle_id = line.id
AND bsl.bcl_id = bcl.id
AND bcl.bill_action = 'TR'
AND bcl.btn_id = -44;
SELECT bcl.cle_id bcl_cle_id,
bsl.cle_id bsl_cle_id,
abs(bsl.amount) bsl_amount,
line.lse_id bcl_lse_id
FROM okc_k_headers_b hdr,
oks_bill_sub_lines bsl,
oks_bill_cont_lines bcl,
okc_k_lines_b line
WHERE line.id = bcl.cle_id
AND hdr.id = line.dnz_chr_id
AND hdr.id = p_contract_hdr_id
AND hdr.scs_code = nvl(p_category,hdr.scs_code)
AND hdr.org_id = nvl(p_org_id,hdr.org_id)
AND bcl.cle_id = line.id
AND bsl.bcl_id = bcl.id
AND bcl.bill_action = 'TR'
AND bcl.btn_id = -44;
SELECT bcl.cle_id bcl_cle_id,
bsl.cle_id bsl_cle_id,
abs(bsl.amount) bsl_amount,
line.lse_id bcl_lse_id
FROM okc_k_headers_b hdr,
oks_bill_sub_lines bsl,
oks_bill_cont_lines bcl,
okc_k_lines_b line
WHERE line.id = bcl.cle_id
AND hdr.id = line.dnz_chr_id
AND hdr.scs_code = nvl(p_category,hdr.scs_code)
AND hdr.org_id = nvl(p_org_id,hdr.org_id)
AND bcl.cle_id = line.id
AND bsl.bcl_id = bcl.id
AND bcl.bill_action = 'TR'
AND bcl.btn_id = -44;
/*Update subline info*/
IF (cur.bcl_lse_id <> 46) THEN
UPDATE oks_k_lines_b
SET credit_amount = nvl(credit_amount,0) + nvl(cur.bsl_amount,0) ,
suppressed_credit = nvl(suppressed_credit,0) - nvl(cur.bsl_amount,0)
WHERE cle_id = cur.bsl_cle_id;
/*Update topline info*/
UPDATE oks_k_lines_b
SET credit_amount = nvl(credit_amount,0) + nvl(cur.bsl_amount,0) ,
suppressed_credit = nvl(suppressed_credit,0) - nvl(cur.bsl_amount,0)
WHERE cle_id = cur.bcl_cle_id;
BTN_ID is updated to null for Each record which qualifies the user inputs.
Order management orginated line can have btn_id = -44.
To avoid process these records bill_action = 'TR' condition is added
in below where clause
*/
UPDATE oks_bill_cont_lines bcl
SET bcl.btn_id = NULL
WHERE bcl.btn_id = -44
AND bcl.bill_Action = 'TR'
AND EXISTS
(SELECT 1 from okc_k_headers_b hdr,
okc_k_lines_b line
WHERE line.id = bcl.cle_id
AND hdr.id = line.dnz_chr_id
AND hdr.id = p_contract_hdr_id
AND hdr.scs_code = nvl(p_category,hdr.scs_code)
AND hdr.org_id = nvl(l_org_id,hdr.org_id));
/*Update subline info*/
IF (cur.bcl_lse_id <> 46) THEN
UPDATE oks_k_lines_b
SET credit_amount = nvl(credit_amount,0) + nvl(cur.bsl_amount,0) ,
suppressed_credit = nvl(suppressed_credit,0) - nvl(cur.bsl_amount,0)
WHERE cle_id = cur.bsl_cle_id;
/*Update topline info*/
UPDATE oks_k_lines_b
SET credit_amount = nvl(credit_amount,0) + nvl(cur.bsl_amount,0) ,
suppressed_credit = nvl(suppressed_credit,0) - nvl(cur.bsl_amount,0)
WHERE cle_id = cur.bcl_cle_id;
BTN_ID is updated to null for Each record which qualifies the user inputs.
Order management orginated line can have btn_id = -44.
To avoid process these records bill_action = 'TR' condition is added
in below where clause
*/
UPDATE oks_bill_cont_lines bcl
SET bcl.btn_id = NULL
WHERE bcl.btn_id = -44
AND bcl.bill_Action = 'TR'
AND EXISTS
(SELECT 1 from okc_k_headers_b hdr,
okc_k_lines_b line
WHERE line.id = bcl.cle_id
AND hdr.id = line.dnz_chr_id
AND hdr.scs_code = nvl(p_category,hdr.scs_code)
AND hdr.org_id = nvl(l_org_id,hdr.org_id));
UPDATE oks_bill_cont_lines bcl
SET bcl.btn_id = NULL
WHERE bcl.btn_id = -44
AND bcl.bill_Action = 'TR'
AND EXISTS
(SELECT 1 from okc_k_headers_b hdr,
okc_k_lines_b line
WHERE line.id = bcl.cle_id
AND hdr.id = line.dnz_chr_id
AND hdr.id = nvl(p_contract_hdr_id,hdr.id)
AND hdr.scs_code = nvl(p_category,hdr.scs_code)
--AND hdr.authoring_org_id = nvl(l_org_id,hdr.authoring_org_id));