[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;
Procedure Insert_usg_qp_dtls_prc
(p_detail_rec in oks_qp_pkg.input_details,
dnz_chr_id in number,
currency_code IN VARCHAR2 ,
prorate IN VARCHAR2,
prv IN number,
p_sign IN NUMBER ,
p_bcl_id IN NUMBER,
level_element_id IN NUMBER,
level_coverage_id IN NUMBER ,
p_prorate IN VARCHAR2 ,
p_amount IN number
) is
pragma autonomous_transaction;
Insert into oks_usg_qp_call_tbl(
price_list ,
price_list_line_id,
chr_id ,
line_id ,
subline_id,
intent ,
currency ,
bcl_id ,
bsl_id ,
usage_qty ,
usage_uom_code,
break_uom_code ,
proration_yn ,
bill_from_date ,
bill_to_date ,
asking_unit_price ,
dnz_chr_id ,
currency_code ,
prorate ,
prv ,
bill_sign ,
usg_bcl_id ,
level_element_id,
level_coverage_id ,
line_prorate ,
amount
)
values
(
p_detail_rec.price_list ,
p_detail_rec.price_list_line_id,
p_detail_rec.chr_id ,
p_detail_rec.line_id ,
p_detail_rec.subline_id,
p_detail_rec.intent ,
p_detail_rec.currency ,
p_detail_rec.bcl_id ,
p_detail_rec.bsl_id ,
p_detail_rec.usage_qty ,
p_detail_rec.usage_uom_code,
p_detail_rec.break_uom_code ,
p_detail_rec.proration_yn ,
p_detail_rec.bill_from_date ,
p_detail_rec.bill_to_date ,
p_detail_rec.asking_unit_price ,
dnz_chr_id ,
currency_code ,
prorate ,
prv ,
p_sign ,
p_bcl_id,
level_element_id,
level_coverage_id,
p_prorate ,
p_amount
) ;
END Insert_usg_qp_dtls_prc;
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 ); Commented for 16039680 */
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 ); Commented for 16039680 */
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;
Insert_usg_qp_dtls_prc(
l_line_rec,
bsl_price_rec.dnz_chr_id ,
p_currency_code ,
bsl_price_rec.prorate ,
p_prv ,
l_sign ,
l_bcl_id ,
level_elements_tab(e_ptr).id,
level_coverage(1).id ,
l_prorate ,
l_amount
);
l_msg_data := 'Error inserting data into oks_usg_qp_call_tbl '||SQLERRM;
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);
Delete from OKS_BILL_SUB_LINE_DTLS
where bsl_id in
(select bsl_id from oks_usg_qp_call_tbl where line_id=Nvl(p_line_id,LINE_ID) and dnz_chr_id=p_dnz_chr_id);
Delete from OKS_BILL_SUB_LINES
where id in
(select bsl_id from oks_usg_qp_call_tbl where line_id=Nvl(p_line_id,LINE_ID) and dnz_chr_id=p_dnz_chr_id);
Delete from OKS_BILL_CONT_LINES
where id in
(select usg_bcl_id from oks_usg_qp_call_tbl where line_id=Nvl(p_line_id,LINE_ID) and dnz_chr_id=p_dnz_chr_id);
UPDATE oks_level_elements
SET date_completed = null
WHERE id in
(select level_element_id from oks_usg_qp_call_tbl where line_id=Nvl(p_line_id,LINE_ID) and dnz_chr_id=p_dnz_chr_id);
UPDATE oks_level_elements
SET date_completed = null
WHERE id in
(select level_coverage_id from oks_usg_qp_call_tbl where line_id=Nvl(p_line_id,LINE_ID) and dnz_chr_id=p_dnz_chr_id);
Delete from OKS_BSD_PR
where bsl_id in
(select bsl_id from oks_usg_qp_call_tbl where line_id=Nvl(p_line_id,LINE_ID) and dnz_chr_id=p_dnz_chr_id);
Delete from OKS_BSL_PR
where id in
(select bsl_id from oks_usg_qp_call_tbl where line_id=Nvl(p_line_id,LINE_ID) and dnz_chr_id=p_dnz_chr_id);
Delete from OKS_BCL_PR
where id in
(select usg_bcl_id from oks_usg_qp_call_tbl where line_id=Nvl(p_line_id,LINE_ID) and dnz_chr_id=p_dnz_chr_id);
FND_FILE.PUT_LINE(FND_FILE.LOG,'Error in procedure delete_billing_tables '||sqlerrm || 'for line_id '||p_line_id ||' for header id '||p_dnz_chr_id);
l_in_tbl (1).update_allowed :=
p_modifier_details (i).override_flag;
l_in_tbl (1).updated_flag := p_modifier_details (i).updated_flag;
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 distinct dnz_chr_id
FROM oks_usg_qp_call_tbl;
SELECT price_list ,
price_list_line_id,
chr_id ,
line_id ,
subline_id,
intent ,
currency ,
bcl_id ,
bsl_id ,
usage_qty ,
usage_uom_code,
break_uom_code ,
proration_yn ,
bill_from_date ,
bill_to_date ,
asking_unit_price,
dnz_chr_id ,
currency_code ,
prorate ,
prv ,
bill_sign ,
usg_bcl_id ,
level_element_id,
level_coverage_id ,
amount
FROM oks_usg_qp_call_tbl
WHERE dnz_chr_id=p_chr_id;
SELECT DISTINCT line_id,currency_code
FROM oks_usg_qp_call_tbl
WHERE dnz_Chr_id=p_dnz_chr_id;
SELECT *
FROM oks_usg_qp_call_tbl
WHERE dnz_Chr_id=p_dnz_chr_id
AND line_id=p_line_id;
g_req_line_tbl_blk.delete;
g_req_line_detail_tbl_blk.delete;
g_req_related_lines_tbl_blk.delete;
g_req_qual_tbl_blk.delete;
g_req_line_attr_tbl_blk.delete;
g_req_line_detail_qual_tbl_blk.delete;
g_req_line_detail_attr_tbl_blk.delete;
gx_req_line_tbl.delete;
gx_req_qual_tbl.delete;
gx_req_line_attr_tbl.delete;
gx_req_line_detail_tbl.delete;
gx_req_line_detail_qual_tbl.delete;
gx_req_line_detail_attr_tbl.delete;
gx_req_related_lines_tbl.delete;
update oks_usg_qp_call_tbl
set line_index=g_line_tbl_ctr+1
WHERE bsl_id= bulk_qp_call_rec.bsl_id;
fnd_log.string (fnd_log.level_statement, g_module || l_api_name,'qp_call: l_control_rec.temp_table_insert_flag : '|| l_control_rec.temp_table_insert_flag
);
fnd_log.string (fnd_log.level_statement, g_module || l_api_name,'qp_call: g_req_line_tbl_blk('|| p ||').updated_adjusted_unit_price : '|| g_req_line_tbl_blk(p).updated_adjusted_unit_price
);
fnd_log.string (fnd_log.level_statement, g_module || l_api_name,'qp_call: g_req_line_detail_tbl_blk('|| p ||').updated_flag : '|| g_req_line_detail_tbl_blk(p).updated_flag
);
fnd_log.string (fnd_log.level_statement, g_module || l_api_name,'qp_call: gx_req_line_tbl('|| p ||').updated_adjusted_unit_price : '|| gx_req_line_tbl(p).updated_adjusted_unit_price
);
fnd_log.string (fnd_log.level_statement, g_module || l_api_name,'qp_call: gx_req_line_detail_tbl('|| p ||').updated_flag : '|| gx_req_line_detail_tbl(p).updated_flag
);
SELECT count(usage_line_rec.line_id) INTO l_subline_count
FROM OKC_K_LINES_B sub_line
WHERE sub_line.cle_id =usage_line_Rec.line_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 ); Commented for 16039680 */
l_price_break_tbl.DELETE;
(qp_preq_grp.g_status_updated,
qp_preq_grp.g_status_system_generated,
qp_preq_grp.g_by_engine,
qp_preq_grp.g_status_unchanged,
qp_preq_grp.g_status_new
)
THEN
l_msg_data:=gx_req_line_tbl(bsl_index).status_text;
l_price_break_tbl.DELETE (l_top_ctr);
l_price_break_tbl.DELETE (l_top_ctr);
OKS_BILL_REC_PUB.update_bsl
(
X_RET_STAT => l_return_status,
P_DNZ_CHR_ID => bsl_rec.dnz_chr_id,
P_BSL_ID => bsl_rec.bsl_id,
P_BCL_ID => bsl_rec.usg_bcl_id,
P_AMOUNT => l_price_rec.PROD_EXT_AMOUNT,
P_CURRENCY_CODE=> bsl_rec.currency_code,
P_PRV => bsl_rec.prv
);
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) ;
update_version(p_dnz_chr_id => bulk_qp_hdr_id_rec.dnz_chr_id);
SELECT Count(DISTINCT line_id) INTO p_reject_counter FROM oks_usg_qp_call_tbl WHERE dnz_chr_id= bulk_qp_hdr_id_rec.DNZ_chr_id;
SELECT Count(DISTINCT line_id) INTO p_reject_counter FROM oks_usg_qp_call_tbl WHERE dnz_chr_id= bulk_qp_hdr_id_rec.DNZ_chr_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 ); Commented for 16039680 */
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 ); Commented for 16039680*/
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 ); Commented for 16039680*/
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 ); Commented for 16039680 */
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);
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,line,as1,hdr,sts)
use_nl(line,hdr) use_hash(as1,sts) swap_join_inputs(as1)
swap_join_inputs(sts) */
hdr.id hdr_id, line.id line_id, rownum, hdr.currency_code
from (
Select distinct lvl.parent_cle_id
from oks_level_elements lvl
where lvl.date_completed is null
and (lvl.date_to_interface > trunc(l_date_interface_start) -1 and lvl.date_to_interface < trunc(p_date)+1)
) lvl,
okc_k_lines_b line,
(
Select 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 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 rel.cle_id
From okc_k_rel_objs rel
Where rel.cle_id is not null
);*/ ---Commented for ER 16039680
INSERT /*+ append */ into oks_process_billing
(chr_id,cle_id,line_no,currency_code)
SELECT /*+ leading(lvl,line,as1,hdr,as1)
use_nl(line,hdr) use_hash(as1,sts,oa) swap_join_inputs(as1) swap_join_inputs(oa)
swap_join_inputs(sts)
NO_PUSH_PRED(lvl) */
hdr.id hdr_id, line.id line_id, rownum, hdr.currency_code
from (
Select /*+ parallel(lvl) no_merge */ distinct
lvl.parent_cle_id
from oks_level_elements lvl
where lvl.date_completed is null
and lvl.date_to_interface < trunc(p_date) + 1
) lvl,
okc_k_lines_b line,
(
Select distinct scs_code,sts_code
from okc_assents a
where a.opn_code = 'INVOICE'
and a.allowed_yn = 'Y'
) as1,
okc_k_headers_all_b hdr,
mo_glob_org_access_tmp oa,
(
Select 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 oa.organization_id = Hdr.authoring_org_id
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 /*+ nl_aj rel.cle_id
From okc_k_rel_objs rel
Where rel.cle_id is not null
);*/ ---Commented for ER 16039680
INSERT /*+ append */ into oks_process_billing
(chr_id,cle_id,line_no,currency_code)
SELECT /*+ leading(lvl,line,as1,hdr,as1)
use_nl(line,hdr) use_hash(as1,sts) swap_join_inputs(as1)
swap_join_inputs(sts)
NO_PUSH_PRED(lvl)
*/
hdr.id hdr_id, line.id line_id, rownum, hdr.currency_code
from (
Select /*+ parallel(lvl) no_merge */ distinct
lvl.parent_cle_id
from oks_level_elements lvl
where lvl.date_completed is null
and lvl.date_to_interface < trunc(p_date) + 1
) lvl,
okc_k_lines_b line,
(
Select 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 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 /*+ nl_aj rel.cle_id
From okc_k_rel_objs rel
Where rel.cle_id is not null
);*/ ---Commented for ER 16039680
INSERT /*+ append */ into oks_process_billing(chr_id,cle_id,line_no,currency_code)
SELECT /*+ leading(lvl,line,as1,hdr,sts) use_nl(line,hdr) use_hash(as1,sts) swap_join_inputs(as1) swap_join_inputs(sts) */
hdr.id hdr_id, line.id line_id, rownum, hdr.currency_code
from (Select /*+ parallel(lvl) no_merge */ distinct lvl.parent_cle_id
from oks_level_elements lvl
where lvl.date_completed is null
and lvl.date_to_interface < trunc(p_date) + 1
) lvl,
okc_k_lines_b line,
(
Select 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 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 rel.cle_id
From okc_k_rel_objs rel
Where rel.cle_id is not null
); */ ---Commented for ER 16039680
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 (
Select distinct lvl.parent_cle_id
from oks_level_elements lvl
where lvl.date_completed is null
and (lvl.date_to_interface > TRUNC(l_date_interface_start) -1 and lvl.date_to_interface < trunc(p_date)+1)
) lvl,
okc_k_lines_b line,
(
Select 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 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 rel.cle_id
From okc_k_rel_objs rel
Where rel.cle_id is not null
);*/ ---Commented for ER 16039680
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 /*(
Select /*+ parallel(lvl) distinct lvl.parent_cle_id
from oks_level_elements lvl
where lvl.date_completed is null
and lvl.date_to_interface < trunc(p_date) + 1
) lvl,*/ /*commented for bug 13932146 */
okc_k_lines_b line,
(
Select 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 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 /*commented for bug 13932146 */
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 rel.cle_id
From okc_k_rel_objs rel
Where rel.cle_id is not null
) */ ---Commented for ER 16039680
/*Added for bug 13932146 */
AND EXISTS (
SELECT /*+ no_unnest push_subq */
'x'
FROM oks_level_elements lvl
WHERE lvl.date_completed IS NULL
AND lvl.date_to_interface < TRUNC (TO_DATE (p_date)) + 1
AND line.ID = lvl.parent_cle_id);
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 lvl.date_to_interface < trunc(p_date) + 1 )
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 );*/ ---Commented for ER 16039680
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 distinct lvl.parent_cle_id
from oks_level_elements lvl
where lvl.date_completed is null
and (lvl.date_to_interface > TRUNC(l_date_interface_start) -1 AND lvl.date_to_interface < trunc(p_date) +1)
) 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
); */ ---Commented for ER 16039680
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 (lvl.date_to_interface) < trunc(p_date)+1
) 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
);*/ ---Commented for ER 16039680
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 = l_org_id
AND Hdr.Scs_code = p_category
AND line.id IN (Select lvl.parent_cle_id
from oks_level_elements lvl
where lvl.date_completed is null
and (lvl.date_to_interface > TRUNC(l_date_interface_start) -1 AND
lvl.date_to_interface < trunc(p_date) +1))
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 );*/ ---Commented for ER 16039680
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 = l_org_id
AND Hdr.Scs_code = p_category
AND line.id IN (Select lvl.parent_cle_id
from oks_level_elements lvl
where lvl.date_completed is null
and lvl.date_to_interface < trunc(p_date) +1)
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 ); */ ---Commented for ER 16039680
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 lvl.date_to_interface < trunc(p_date) +1 ) /*13105205*/
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 ); */ ---Commented for ER 16039680
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));