[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT hold_billing
FROM oks_k_headers_b hdr,
okc_k_lines_b line
WHERE line.id = p_cle_id
AND line.dnz_chr_id = hdr.chr_id;
SELECT lvl.date_end FROM oks_level_elements lvl
WHERE lvl.cle_id = p_cle_id
AND trunc(p_termination_date ) BETWEEN trunc(lvl.date_start) AND
trunc(lvl.date_end);
SELECT bcl.id bcl_bcl_id
,bcl.cle_id bcl_cle_id
,bcl.btn_id bcl_btn_id
,bcl.Date_Billed_from bcl_date_billed_from
,bcl.Date_Billed_to bcl_date_billed_to
,bcl.date_next_invoice bcl_date_next_invoice
,bcl.amount bcl_amount
,bcl.bill_action bcl_bill_action
,bcl.Attribute_category bcl_attribute_category
,bcl.Attribute1 bcl_attribute1
,bcl.Attribute2 bcl_attribute2
,bcl.Attribute3 bcl_attribute3
,bcl.Attribute4 bcl_attribute4
,bcl.Attribute5 bcl_attribute5
,bcl.Attribute6 bcl_attribute6
,bcl.Attribute7 bcl_attribute7
,bcl.Attribute8 bcl_attribute8
,bcl.Attribute9 bcl_attribute9
,bcl.Attribute10 bcl_attribute10
,bcl.Attribute11 bcl_attribute11
,bcl.Attribute12 bcl_attribute12
,bcl.Attribute13 bcl_attribute13
,bcl.Attribute14 bcl_attribute14
,bcl.Attribute15 bcl_attribute15
,bsl.id bsl_bsl_id
,bsl.cle_id bsl_cle_id
,bsl.average bsl_average
,bsl.amount bsl_amount
,bsl.Date_Billed_from bsl_date_billed_From
,bsl.Date_Billed_to bsl_date_billed_to
,bsl.date_to_interface bsl_date_to_interface
,bsl.Attribute_category bsl_attribute_category
,bsl.Attribute1 bsl_attribute1
,bsl.Attribute2 bsl_attribute2
,bsl.Attribute3 bsl_attribute3
,bsl.Attribute4 bsl_attribute4
,bsl.Attribute5 bsl_attribute5
,bsl.Attribute6 bsl_attribute6
,bsl.Attribute7 bsl_attribute7
,bsl.Attribute8 bsl_attribute8
,bsl.Attribute9 bsl_attribute9
,bsl.Attribute10 bsl_attribute10
,bsl.Attribute11 bsl_attribute11
,bsl.Attribute12 bsl_attribute12
,bsl.Attribute13 bsl_attribute13
,bsl.Attribute14 bsl_attribute14
,bsl.Attribute15 bsl_attribute15
--,bsl.manual_credit line_existing_credit
,line.start_date cov_start_date
,line.end_date cov_end_date
,line.price_negotiated cov_price_negotiated
FROM
okc_k_lines_b line,
oks_bill_cont_lines bcl,
oks_bill_sub_lines bsl
WHERE bcl.Cle_id = p_top_line_id
AND bsl.cle_id = nvl(p_cov_line_id,bsl.cle_id)
AND bcl.id = bsl.bcl_id
AND bcl.bill_action = 'RI'
AND bsl.amount > 0 -- Added to avoid -ve inv rec generated during settlement cycle
--09-FEB mchoudha changed from bcl to bsl
--For OM contracts if the subline end date is less than the top line end date then the termination records
--were no getting created because of the bcl condition.
AND trunc(bsl.date_billed_to) <= trunc(p_date_to)
AND trunc(bsl.date_billed_to) >= trunc(p_date_from)
AND line.id = bsl.cle_id
AND line.date_Terminated is NULL
/*
and not exists
(select 1 from oks_bill_cont_lines bclsub
where bclsub.cle_id = bcl.cle_id
and trunc(bclsub.date_billed_from) = trunc(bcl.date_billed_from)
and bclsub.bill_action = 'AV')
*/
ORDER by bcl.Date_billed_from, bcl.id desc;
SELECT bsl_id_averaged
,bsd_id
,bsd_id_applied
,ccr_id
,cgr_id
,start_reading
,end_reading
,unit_of_measure
,fixed
,actual
,default_default
,amcv_yn
,adjustment_level
,adjustment_minimum
,result
,amount
,Attribute_category
,Attribute1
,Attribute2
,Attribute3
,Attribute4
,Attribute5
,Attribute6
,Attribute7
,Attribute8
,Attribute9
,Attribute10
,Attribute11
,Attribute12
,Attribute13
,Attribute14
,Attribute15
FROM Oks_bill_sub_line_dtls
WHERE bsl_id = p_bsl_id;
SELECT id
,amount
FROM oks_bill_cont_lines
WHERE cle_id = p_top_line_id
AND bill_Action = p_bill_Action
AND trunc(date_billed_from) = trunc(p_date_billed_from)
AND trunc(date_billed_to ) = trunc(p_date_billed_to)
AND btn_id is null;
SELECT line.Start_date
,line.End_date
,line.Date_Terminated
,line.dnz_chr_id
,line.sts_code
,line.lse_id
,line.line_number
,hdr.currency_code
,hdr.id
FROM okc_k_headers_b hdr ,
okc_k_lines_B line
WHERE line.id = p_cle_id
AND line.dnz_chr_id = hdr.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 primary_uom_code
FROM Okc_K_items Item
,mtl_system_items mtl
,okc_k_lines_b line
,okc_k_headers_b hdr
WHERE item.cle_id = p_cle_id
AND line.id = item.cle_id
AND hdr.id = line.dnz_chr_id
AND mtl.inventory_item_id = item.object1_id1
---AND mtl.organization_id = hdr.authoring_org_id;
SELECT txn.bill_instance_number from oks_bill_txn_lines txn
WHERE bcl_id = p_bcl_id
AND nvl(bsl_id,-1) = decode(bsl_id,NULL,-1,p_bsl_id);
SELECT str.uom_code l_freq
FROM oks_stream_levels_b str,
oks_level_elements lvl
WHERE lvl.cle_id = p_cov_line_id
AND lvl.rul_id = str.id
AND l_termination_date between lvl.date_start and lvl.date_end;
SELECT sum(amount)
FROM oks_bill_sub_lines
WHERE cle_id = p_cle_id
AND date_billed_from = p_date_from;
OKS_BILL_REC_PUB.INSERT_BCL
(
P_CALLEDFROM => 1,
X_RETURN_STAT => l_return_status,
P_CLE_ID => p_top_line_id,
P_DATE_BILLED_FROM => p_date_from,
P_DATE_BILLED_TO => p_date_to,
P_DATE_NEXT_INVOICE => trunc(sysdate),
P_BILL_ACTION => p_bill_action ,
P_OKL_FLAG => 2,
P_PRV => 1,
P_MSG_COUNT => l_msg_cnt,
P_MSG_DATA => l_msg_data,
X_BCL_ID => l_bcl_id
);
OKS_BILL_REC_PUB.Insert_all_subline
(
P_CALLEDFROM => 1,
X_RETURN_STAT => l_return_status,
P_COVERED_TBL => l_cov_tbl,
P_CURRENCY_CODE => hdr_rec.currency_code,
P_DNZ_CHR_ID => hdr_rec.id,
P_PRV => 1,
P_MSG_COUNT => l_msg_cnt,
P_MSG_DATA => l_msg_data
);
UPDATE oks_level_elements
SET date_completed = p_date_to,
amount = p_con_terminate_amount
WHERE cle_id = p_cov_line_id
AND date_completed is null
AND date_start = p_date_from
AND date_end = p_date_to;
UPDATE oks_level_elements
SET date_completed = p_date_to,
amount = amount-p_amount+p_con_terminate_amount
WHERE cle_id = p_top_line_id
AND date_completed is null
AND date_start = p_date_from
AND date_end = p_date_to;
l_bclv_tbl_in.delete;
l_bclv_tbl_out.delete;
l_bslv_tbl_in.delete;
l_bslv_tbl_out.delete;
OKS_BILLCONTLINE_PUB.insert_Bill_Cont_Line
(
p_api_version => 1.0,
p_init_msg_list => 'T',
x_return_status => l_return_status,
x_msg_count => l_msg_cnt,
x_msg_data => l_msg_data,
p_bclv_tbl => l_bclv_tbl_in,
x_bclv_tbl => l_bclv_tbl_out
);
which is inserted in bcl*/
l_bclv_tbl_in(1).ID := l_bclv_tbl_out(1).ID;
OKS_BILLSUBLINE_PUB.insert_Bill_subLine_Pub
(
p_api_version => 1.0,
p_init_msg_list => 'T',
x_return_status => l_return_status,
x_msg_count => l_msg_cnt,
x_msg_data => l_msg_data,
p_bslv_tbl => l_bslv_tbl_in,
x_bslv_tbl => l_bslv_tbl_out
);
l_bsdv_tbl_in.delete;
l_bsdv_tbl_out.delete;
OKS_BSL_det_PUB.insert_bsl_det_Pub
(
p_api_version => 1.0,
p_init_msg_list => 'T',
x_return_status => l_return_status,
x_msg_count => l_msg_cnt,
x_msg_data => l_msg_data,
p_bsdv_tbl => l_bsdv_tbl_in,
x_bsdv_tbl => l_bsdv_tbl_out
);
UPDATE oks_bill_cont_lines
SET amount = nvl(amount,0) + l_bslv_tbl_in(1).AMOUNT
WHERE id = l_temp_bcl_id_tr;
SELECT uom_code
FROM Okc_time_code_units_v
WHERE tce_code = 'DAY'
AND quantity = 1;
SELECT mtl.primary_uom_code,
line.dnz_chr_id
FROM Okc_K_items Item
,mtl_system_items_b mtl --Okx_system_items_v mtl
,okc_k_headers_b hdr
,okc_k_lines_b line
WHERE item.cle_id = line.id --p_cle_id
AND line.id = p_cle_id
AND line.dnz_chr_id = hdr.id
--AND mtl.id1 = item.object1_id1
AND mtl.inventory_item_id = item.object1_id1
AND mtl.organization_id = hdr.inv_organization_id; --p_org_id;
SELECT line.id,
line.start_date,
line.end_date,
rline.usage_est_yn,
rline.usage_est_method,
rline.default_quantity,
rline.usage_est_start_date usage_est_start_date
FROM okc_k_lines_b line,
oks_k_lines_b rline
WHERE line.cle_id = p_top_id
AND line.id = nvl(p_cov_id,line.id)
AND line.lse_id = 13
AND line.date_cancelled is null --LLC BUG FIX 4742661
AND rline.cle_id = line.id;
SELECT period_type,period_start
FROM oks_k_headers_b
WHERE chr_id = p_hdr_id;
SELECT line.id,
line.start_date,
rline.usage_est_yn,
rline.usage_est_method
FROM okc_k_lines_b line,
oks_k_lines_b rline
WHERE line.cle_id = p_cle_id
AND line.lse_id = 13
AND line.id = nvl(p_cp_line_id,line.id)
AND line.date_cancelled is NULL --LLC BUG FIX 4742661
AND rline.cle_id = line.id;
SELECT sum(bsd.result) ,
sum(bsl.amount) ,
max(bsl.date_billed_From),
max(bsl.date_billed_to)
FROM oks_bill_sub_line_dtls bsd,
oks_bill_sub_lines bsl
WHERE bsl.cle_id = p_cle_id
AND bsd.bsl_id = bsl.id;
SELECT bsl.id,
bsl.date_billed_from,
bsl.date_billed_to,
bsl.amount,
bsd.result
FROM oks_bill_cont_lines bcl,
oks_bill_sub_lines bsl,
oks_bill_sub_line_dtls bsd
WHERE bcl.id = bsl.bcl_id
AND bsl.cle_id = p_cle_id
AND bsd.bsl_id = bsl.id
ORDER by bsl.date_billed_to desc;
SELECT okl.id ,
okl.lse_id ,
okl.price_negotiated,
okh.currency_code,
okh.contract_number,
okh.contract_number_modifier
FROM okc_k_lines_b okl,
okc_k_headers_b okh
WHERE okh.id = p_hdr_id
AND okl.dnz_chr_id = okh.id
AND okl.cle_id is null
AND okl.date_cancelled is null --LLC BUG FIX 4742661
AND okl.date_terminated is null;
SELECT okh.currency_code,
okh.id header_id,
okl.lse_id ,
okl.cle_id ,
okl.price_negotiated,
okh.contract_number,
okh.contract_number_modifier,
okh.billed_at_source
FROM okc_k_headers_all_b okh,
okc_k_lines_b okl
WHERE okl.id = p_id
AND okh.id = okl.dnz_chr_id;
SELECT bcl.id ,rline.termn_method,
rline.usage_type, rline.usage_period
FROM oks_bill_cont_lines bcl,
oks_k_lines_b rline
WHERE bcl.cle_id = p_id
AND rline.cle_id = bcl.cle_id
AND bcl.bill_action = 'RI';
SELECT nvl(sum(nvl(amount,0)),0),max(date_billed_To)
FROM oks_bill_cont_lines
WHERE cle_id = p_cle_id
AND bill_action = 'RI';
SELECT nvl(sum(nvl(amount,0)),0),max(end_date)
FROM oks_subscr_elements
WHERE dnz_cle_id = p_id
AND order_header_id is not null;
SELECT bsl.id ,rline.TERMN_METHOD ,bcl.cle_id,
rline.usage_type, rline.usage_period
FROM oks_bill_sub_lines bsl,
oks_bill_cont_lines bcl,
oks_k_lines_b rline
WHERE bsl.cle_id = p_id
AND bsl.bcl_id = bcl.id
AND rline.cle_id = bcl.cle_id
AND bcl.bill_action = 'RI';
SELECT sum(amount)
FROM oks_bill_sub_lines
WHERE cle_id = p_cle_id;
SELECT sum(amount)
FROM oks_bill_cont_lines
WHERE cle_id = p_cle_id;
SELECT sum(amount) FROM
oks_bill_cont_lines
WHERE cle_id = p_line_id ;
SELECT nvl(sum(amount),0) FROM oks_bill_cont_lines
WHERE cle_id = p_line_id
AND bill_action = 'RI';
SELECT nvl(sum(amount),0) FROM oks_subscr_elements
WHERE dnz_cle_id = p_line_id
AND trunc(start_date) < trunc(p_term_date);
SELECT nvl(sum(bsl.amount),0) amount
FROM
okc_k_lines_b okl2,
oks_bill_sub_lines bsl,
oks_bill_cont_lines bcl,
okc_k_lines_b okl1
WHERE okl1.id = p_line_id
AND bcl.cle_id = okl1.id
AND bsl.bcl_id = bcl.id
AND bsl.cle_id = okl2.id
AND okl2.date_terminated is NULL
AND trunc(bsl.date_billed_from) > trunc(p_terminate_date);
SELECT nvl(sum(bsl.amount),0) amount
FROM
okc_k_lines_b okl,
oks_bill_sub_lines bsl
WHERE okl.id = p_line_id
AND bsl.cle_id = okl.id
AND okl.date_terminated is NULL
AND trunc(bsl.date_billed_from) > trunc(p_terminate_date);
SELECT nvl(bsl.amount,0) amount,
bsl.date_billed_from date_billed_from,
bsl.date_billed_to date_billed_to,
okl.start_date start_date,
okl.end_date end_date,
okl.id id
FROM
oks_bill_sub_lines bsl,
okc_k_lines_b okl
WHERE okl.id = p_line_id
AND okl.id = bsl.cle_id
AND okl.date_terminated is null
AND trunc(bsl.date_billed_to) >= trunc(p_terminate_date)
AND trunc(bsl.date_billed_from) <= trunc(p_terminate_date);
SELECT nvl(bsl.amount,0) amount,
bcl.date_billed_from date_billed_from,
bcl.date_billed_to date_billed_to,
okl1.start_date start_date,
okl1.end_date end_date,
okl1.id id
FROM
okc_k_lines_b okl2,
oks_bill_sub_lines bsl,
oks_bill_cont_lines bcl,
okc_k_lines_b okl1
WHERE okl1.id = p_line_id
AND bcl.cle_id = okl1.id
AND bsl.bcl_id = bcl.id
AND okl2.id = bsl.cle_id
AND okl2.date_terminated is null
AND trunc(bcl.date_billed_to) >= trunc(p_terminate_date)
AND trunc(bcl.date_billed_from) <= trunc(p_terminate_date);
SELECT str.uom_code l_freq
FROM oks_stream_levels_b str,
oks_level_elements lvl
WHERE lvl.cle_id = p_line_id
AND p_termination_date BETWEEN lvl.date_start AND lvl.date_end
AND lvl.rul_id = str.id;
SELECT id FROM OKC_K_REL_OBJS_V
WHERE cle_id = p_line_id;
SELECT obj.id FROM OKC_K_REL_OBJS_V obj,
OKC_K_LINES_B ln
WHERE obj.cle_id = ln.id
AND ln.cle_id = p_line_id;
SELECT price_negotiated
,start_date
,end_date
,dnz_chr_id
FROM Okc_k_lines_b
WHERE cle_id = p_line_id
AND lse_id = 25
AND date_cancelled is NULL --LLC BUG FIX 4742661
AND date_terminated is NULL;
SELECT price_negotiated
,start_date
,end_date
,dnz_chr_id
FROM Okc_k_lines_b
WHERE id = p_line_id
AND lse_id = 25
AND date_cancelled is NULL --LLC BUG FIX 4742661
AND date_terminated is NULL;
SELECT lse_id
FROM okc_k_lines_b
WHERE id = p_id;
SELECT bsl.id
,bsl.cle_id
,bsl.average
,bsl.amount
,bsl.Date_Billed_from
,bsl.Date_Billed_to
,bsl.Attribute_category
,bsl.Attribute1
,bsl.Attribute2
,bsl.Attribute3
,bsl.Attribute4
,bsl.Attribute5
,bsl.Attribute6
,bsl.Attribute7
,bsl.Attribute8
,bsl.Attribute9
,bsl.Attribute10
,bsl.Attribute11
,bsl.Attribute12
,bsl.Attribute13
,bsl.Attribute14
,bsl.Attribute15
FROM oks_bill_sub_lines bsl,
okc_k_lines_b okl
WHERE bcl_id = id_in
AND bsl.cle_id = okl.id
AND okl.date_cancelled is null --LLC BUG FIX 4742661
AND okl.date_terminated is null;
SELECT bsl_id_averaged
,bsd_id
,bsd_id_applied
,unit_of_measure
,fixed
,actual
,default_default
,amcv_yn
,adjustment_level
,adjustment_minimum
,result
,amount
,start_reading
,end_reading
,ccr_id
,cgr_id
,Attribute_category
,Attribute1
,Attribute2
,Attribute3
,Attribute4
,Attribute5
,Attribute6
,Attribute7
,Attribute8
,Attribute9
,Attribute10
,Attribute11
,Attribute12
,Attribute13
,Attribute14
,Attribute15
FROM oks_bill_sub_line_dtls
WHERE bsl_id = id_in;
SELECT Id
,OBJECT_VERSION_NUMBER
,CLE_ID
,BTN_ID
,DATE_BILLED_FROM
,DATE_BILLED_TO
,DATE_NEXT_INVOICE
,AMOUNT
,BILL_ACTION
,ATTRIBUTE_CATEGORY
,ATTRIBUTE1
,ATTRIBUTE2
,ATTRIBUTE3
,ATTRIBUTE4
,ATTRIBUTE5
,ATTRIBUTE6
,ATTRIBUTE7
,ATTRIBUTE8
,ATTRIBUTE9
,ATTRIBUTE10
,ATTRIBUTE11
,ATTRIBUTE12
,ATTRIBUTE13
,ATTRIBUTE14
,ATTRIBUTE15
FROM OKS_BILL_CONT_LINES_V
WHERE ID = p_bcl_id_new;
SELECT bill_instance_number ,bsl_id
FROM OKS_BILL_TXN_LINES txn
WHERE bcl_id = p_bcl_id
AND bsl_id is null;
SELECT bill_instance_number ,bsl_id
FROM OKS_BILL_TXN_LINES txn
,OKS_BILL_SUB_LINES bsl
WHERE txn.bcl_id = p_bcl_id
AND bsl.cle_id = p_cle_id
AND bsl.id = txn.bsl_id;
OKS_BILLSUBLINE_PUB.insert_Bill_subLine_Pub
(
p_api_version => 1.0,
p_init_msg_list => 'T',
x_return_status => l_return_status,
x_msg_count => l_msg_cnt,
x_msg_data => l_msg_data,
p_bslv_tbl => l_bslv_tbl_in,
x_bslv_tbl => l_bslv_tbl_out
);
OKS_BSL_det_PUB.insert_bsl_det_Pub
(
p_api_version => 1.0,
p_init_msg_list => 'T',
x_return_status => l_ret_stat,
x_msg_count => l_msg_cnt,
x_msg_data => l_msg_data,
p_bsdv_tbl => l_bsdv_tbl_in,
x_bsdv_tbl => l_bsdv_tbl_out
);
UPDATE oks_bill_cont_lines
SET amount = bcl_rec.amount - nvl(l_round_amt,0)
WHERE id = bcl_rec.id;
SELECT rhdr.cc_no ,
rhdr.cc_expiry_date,
rhdr.cc_bank_acct_id,
hdr.bill_to_site_use_id
FROM oks_k_headers_b rhdr,
okc_k_headers_b hdr
WHERE rhdr.chr_id = hdr.id
AND hdr.id = p_hdr_id;
SELECT line.id line_id,
rline.cc_no line_cc_number,
rline.cc_expiry_date line_cc_exp_date,
rline.cc_bank_acct_id line_bank_number,
line.bill_to_site_use_id site_use_id
FROM
oks_k_lines_b rline,
okc_k_lines_b line
WHERE line.dnz_chr_id = p_hdr_id
AND line.lse_id in (1,12,19)
AND line.date_cancelled is NULL --LLC BUG FIX 4742661
AND line.id = rline.cle_id;
SELECT ca.cust_account_id
FROM hz_cust_acct_sites_all ca,
hz_cust_site_uses_all cs
WHERE ca.cust_acct_site_id = cs.cust_acct_site_id
AND cs.site_use_id = p_object1_id1;
UPDATE oks_k_headers_b
SET cc_bank_acct_id = x_bank_account_id
WHERE chr_id = p_dnz_chr_id;
UPDATE oks_k_lines_b
SET cc_bank_acct_id = x_bank_account_id,
cc_no = line_cur.line_cc_number,
cc_expiry_date = line_Cur.line_cc_exp_date
WHERE cle_id = line_cur.line_id;
SELECT cle.Id
,cle.start_date
,cle.end_date
,cle.price_negotiated
,cle.cle_id
,TO_NUMBER(cim.object1_id1) instance_id
FROM Okc_k_lines_b cle,
okc_k_items cim
WHERE cle.id = p_line_id
AND cle.lse_id = 25
AND cle.id = cim.cle_id;
SELECT Id
,start_date
,end_date
,price_negotiated
,cle_id
FROM Okc_k_lines_b
WHERE (cle_id = p_line_id
And lse_id = 25
AND date_cancelled is null --LLC BUG FIX 4742661
And date_terminated is null) OR
(id = p_line_id
And lse_id = 25
AND date_cancelled is null --LLC BUG FIX 4742661
And date_terminated is null);
SELECT okl1.price_negotiated
,okl1.start_date
,okl1.end_date
,okl1.dnz_chr_id
,okl1.id
FROM Okc_k_lines_b okl1
WHERE okl1.id = p_line_id
AND okl1.lse_id = 19
AND exists (Select 1 from okc_k_lines_b okl2
Where okl2.cle_id = okl1.id
And okl2.lse_id = 25
AND okl2.date_cancelled is null --LLC BUG FIX 4742661
And okl2.date_terminated is null);
SELECT currency_code
FROM okc_k_headers_b
WHERE id = p_chr_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 'x'
FROM CS_INCIDENTS_ALL_B sr
where sr.contract_service_id = p_id
AND sr.customer_product_id = p_cp_id
and sr.status_flag = 'O';
SELECT p.line_number||'.'||s.line_number,
hdr.contract_number
FROM okc_k_lines_b p,
okc_k_lines_b s,
okc_k_headers_b hdr
WHERE s.id=p_id
AND p.id=s.cle_id
AND hdr.id=p.dnz_chr_id;
SELECT dnz_chr_id
FROM OKC_K_LINES_B
WHERE id = p_id;
Select Start_date
,End_date
,Price_negotiated
,Date_terminated
From Okc_k_lines_b
Where cle_id = p_k_line_id
And date_cancelled is null --LLC BUG FIX 4742661
And date_terminated IS NULL
And lse_id in (7,8,9,10,11,13,35,25);
SELECT line.Start_date
,line.End_date
,line.Date_Terminated
,line.dnz_chr_id
,line.lse_id
,hdr.currency_code
FROM Okc_k_lines_B line,
okc_k_headers_b hdr
WHERE line.Cle_id = p_k_line_id
AND line.dnz_chr_id = hdr.id;
SELECT start_date,lse_id
FROM OKC_K_LINES_B
WHERE id = p_k_line_id;
SELECT dnz_chr_id
FROM OKC_K_LINES_B
WHERE id = p_id;
SELECT usage_type
FROM oks_k_lines_b
WHERE cle_id = p_id ;
SELECT nvl(sum(nvl(amount,0)),0)
INTO l_billed_amount
FROM oks_bill_cont_lines
WHERE cle_id = p_k_line_id
GROUP by cle_id;
SELECT id from oks_subscr_elements
where dnz_cle_id = p_id
and order_header_id is null
and start_date >= p_termination_date;
SELECT * from oks_bill_cont_lines
WHERE cle_id = p_id
AND bill_action = 'RI'
--AND date_billed_to >= l_termination_date
ORDER BY DATE_BILLED_FROM DESC;
SELECT * from oks_bill_cont_lines
WHERE cle_id = p_id
AND bill_action = 'RI'
AND (( date_billed_from >= l_termination_date
AND date_billed_to >= nvl(l_next_ship_date,date_billed_to))
OR
(l_termination_date between date_billed_from and date_billed_to
AND nvl(l_next_ship_date,date_billed_from) between date_billed_from and date_billed_to))
ORDER BY DATE_BILLED_FROM DESC;
SELECT bsl.*
FROM oks_bill_sub_lines bsl
WHERE bsl.bcl_id = p_id;
Select * from oks_bill_sub_line_dtls
where bsl_id = p_id;
Select id
from oks_bill_cont_lines
where cle_id = p_id
and bill_Action = 'TR'
and trunc(date_billed_from) = trunc(p_date_billed_from)
and trunc(date_billed_to ) = trunc(p_date_billed_to);
Select nvl(sum(nvl(amount,0)),0)
From oks_subscr_elements
Where dnz_cle_id = p_id
and start_date <= p_termination_date;
Select nvl(sum(amount),0) from oks_bill_cont_lines
Where cle_id = p_line_id
and bill_action = 'RI'
and trunc(date_billed_From) <= trunc(p_term_date);
Select nvl(sum(amount),0) from oks_subscr_elements
Where dnz_cle_id = p_line_id;
select count(*)
from oks_bill_cont_lines
where cle_id = l_line_id
and trunc(date_billed_to) >= trunc(l_next_ship_date)
and trunc(date_billed_to) >= trunc(l_termination_date);
SELECT start_date
FROM okc_k_lines_b
WHERE id = p_id ;
select count(*)
from oks_bill_cont_lines
where cle_id = l_line_id
and trunc(date_billed_to) >= trunc(l_termination_date);
SELECT txn.bill_instance_number from oks_bill_txn_lines txn
WHERE bcl_id = p_bcl_id;
l_bclv_tbl_in.delete;
l_bclv_tbl_out.delete;
l_bslv_tbl_in.delete;
l_bslv_tbl_out.delete;
l_bsdv_tbl_in.delete;
l_bsdv_tbl_out.delete;
OKS_BILLCONTLINE_PUB.insert_Bill_Cont_Line
( p_api_version => 1.0,
p_init_msg_list => 'T',
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_bclv_tbl => l_bclv_tbl_in,
x_bclv_tbl => l_bclv_tbl_out);
OKS_BILLSUBLINE_PUB.insert_Bill_subLine_Pub
(
p_api_version => 1.0,
p_init_msg_list => 'T',
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_bslv_tbl => l_bslv_tbl_in,
x_bslv_tbl => l_bslv_tbl_out
);
OKS_BSL_det_PUB.insert_bsl_det_Pub
(
p_api_version => 1.0,
p_init_msg_list => 'T',
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_bsdv_tbl => l_bsdv_tbl_in,
x_bsdv_tbl => l_bsdv_tbl_out
);
UPDATE oks_bill_cont_lines
SET amount = l_bclv_tbl_in(1).AMOUNT
WHERE id = l_bclv_tbl_in(1).id;
SELECT id,
lse_id
FROM okc_k_lines_b
WHERE dnz_chr_id = p_hdr_id
AND cle_id is null
AND date_terminated is null --Sts_cd can be active for future terminate
AND sts_code in ('ACTIVE','SIGNED');
SELECT lines.id,
lines.lse_id,
/* Start Addition for bug fix 6012384 (FP for 5469820) */
lines.end_date
/* End Addition for bug fix 6012384 (FP for 5469820) */
FROM okc_k_lines_b lines,
okc_k_headers_b hdr
WHERE hdr.id = p_hdr_id
AND lines.dnz_chr_id = hdr.id
AND lines.date_cancelled is null --LLC BUG FIX 4742661
And lines.date_terminated is null
And lines.cle_id is null
And hdr.sts_code <> 'QA_HOLD'
AND (exists ( SELECT 1 from okc_assents a
where Hdr.scs_code = a.scs_code
and lines.sts_code = a.sts_code
and lines.sts_code <> 'HOLD'
and a.opn_code = 'INVOICE'
and a.allowed_yn = 'Y')
OR
(lines.sts_code = 'HOLD'));
SELECT lines.dnz_chr_id,
lines.sts_code,
lines.end_date,
lines.lse_id,
lines.line_number,
lines.date_terminated
FROM okc_k_headers_b hdr,
okc_k_lines_b lines
WHERE lines.id = p_line_id
AND hdr.id = lines.dnz_chr_id
AND hdr.sts_code <> 'QA_HOLD'
AND (exists ( SELECT 1 from okc_assents a
where Hdr.scs_code = a.scs_code
and lines.sts_code = a.sts_code
and lines.sts_code <> 'HOLD'
and a.opn_code = 'INVOICE'
and a.allowed_yn = 'Y')
OR
(lines.sts_code = 'HOLD'));
SELECT end_date,
contract_number,
contract_number_modifier
FROM okc_k_headers_b
WHERE id = p_hdr_id;
Select nvl(sum(nvl(amount,0)),0),max(end_date)
From oks_subscr_elements
Where dnz_cle_id = p_id
And order_header_id is not null;
Select max(end_date)
From oks_subscr_elements
Where dnz_cle_id = p_id
and start_date <= p_termination_date - 1 ;
Select nvl(sum(nvl(bcl.amount,0)),0)
from oks_bill_cont_lines bcl
where bcl.cle_id = p_id
and bcl.bill_action = 'RI';
Select min(om_interface_date)
from oks_subscr_elements
Where dnz_Cle_id = p_id
and order_header_id is null;
Select id from oks_bill_cont_lines
where cle_id = p_id
and bill_action = 'RI';
Select bsl.id from oks_bill_sub_lines bsl,
oks_bill_cont_lines bcl
where bsl.cle_id = p_id
and bsl.bcl_id = bcl.id
and bcl.bill_action = 'RI';
Select fulfillment_channel from oks_subscr_header_b
where cle_id = p_id;
Select nvl(amount,0),cle_id ,btn_id,bill_action
from oks_bill_cont_lines
where id = p_bcl_id;
Select nvl(amount,0),cle_id
from oks_bill_sub_lines
where id = p_bsl_id;
Select tax_amount
from oks_k_lines_b
where cle_id = p_sub_line_id;
SELECT max(bsl.date_billed_to) max_bill_date
FROM oks_bill_cont_lines bcl
, oks_bill_sub_lines bsl
WHERE bcl.cle_id = p_line_id
AND bcl.bill_action = 'RI'
AND bcl.id = bsl.bcl_id ;
SELECT start_date
FROM okc_k_lines_b
WHERE id = p_id ;
SELECT id
FROM okc_k_lines_b
WHERE cle_id = p_cle_id
AND lse_id in (7,8,9,10,11,13,35,25)
/* Start changes For bug fix 6012384 (FP for 5990067) */
AND sts_code IN(SELECT code FROM okc_statuses_b where ste_code NOT IN('TERMINATED','EXPIRED'));
SELECT id
FROM okc_k_lines_b
WHERE dnz_chr_id = p_hdr_id
/* Start changes For bug fix 6012384 (FP for 5990067) */
AND lse_id in (1,12,14,19,46)
--AND lse_id in (1,12,14,19)
/* End changes for bug fix 6012384 (FP for 5990067) */
AND cle_id is null
/* Start changes For bug fix 6012384 (FP for 5990067) */
AND sts_code IN(SELECT code FROM okc_statuses_b where ste_code NOT IN('TERMINATED','EXPIRED'));
SELECT 'x'
FROM CS_INCIDENTS_ALL_B sr
WHERE sr.contract_service_id = p_id
AND sr.status_flag = 'O';
SELECT lin.line_number,hdr.contract_number
FROM okc_k_lines_b lin,
okc_k_headers_b hdr
WHERE lin.id=p_id
AND hdr.id=lin.dnz_chr_id;
SELECT 'x'
FROM CS_INCIDENTS_ALL_B sr
WHERE sr.contract_id = p_id
AND sr.status_flag = 'O';
select contract_number
from okc_k_headers_b
where id=p_id;
Select sum(bsl.amount)
from oks_bill_cont_lines bcl,
oks_bill_sub_lines bsl
where bcl.cle_id = p_cle_id
and bsl.bcl_id = bcl.id
and bcl.bill_action <> 'TR'
and trunc(bcl.date_billed_from) >= trunc(p_termination_date);
Select sum(bsl.amount)
from oks_bill_cont_lines bcl,
oks_bill_sub_lines bsl
where bcl.cle_id = p_cle_id
and bsl.bcl_id = bcl.id
and bcl.bill_action <> 'TR';
Select nvl(sum(decode(sign(trunc(bsl.date_billed_from) - trunc(p_termination_date)) ,-1,
((trunc(bsl.date_billed_to) - trunc(p_termination_date) + 1) * bsl.amount) /
(trunc(bsl.date_billed_to) - trunc(bsl.date_billed_from) + 1) ,bsl.amount )),0)
-- nvl(sum(bsl.amount),0)
from oks_bill_cont_lines bcl,
oks_bill_sub_lines bsl
where bcl.cle_id = p_cle_id
and bsl.bcl_id = bcl.id
and bcl.bill_action <> 'TR'
and trunc(bsl.date_billed_from) >= trunc(p_termination_date)
and bsl.amount < 0;
Select nvl(sum(decode(sign(trunc(bsl.date_billed_from) - trunc(p_termination_date)) ,-1,
((trunc(bsl.date_billed_to) - trunc(p_termination_date) + 1) * bsl.amount) /
(trunc(bsl.date_billed_to) - trunc(bsl.date_billed_from) + 1) ,bsl.amount )),0)
from oks_bill_cont_lines bcl,
oks_bill_sub_lines bsl
where bcl.cle_id = p_cle_id
and bsl.bcl_id = bcl.id
and bcl.bill_action <> 'TR'
and trunc(bsl.date_billed_from) >= trunc(p_termination_date)
and bsl.amount > 0;
Select 1 from oks_bill_cont_lines
where cle_id = p_cle_id
and bill_action = 'AV';
Select nvl(sum(decode(sign(trunc(bsl.date_billed_from) - trunc(p_termination_date)) ,-1,
((trunc(bsl.date_billed_to) - trunc(p_termination_date) + 1) * bsl.amount) /
(trunc(bsl.date_billed_to) - trunc(bsl.date_billed_from) + 1) ,bsl.amount )),0)
from oks_bill_cont_lines bcl,
oks_bill_sub_lines bsl,
okc_k_lines_b line
where bsl.bcl_id = bcl.id
and line.id = bcl.cle_id
and bcl.bill_action <> 'TR'
and line.dnz_chr_id = p_hdr_id
and line.lse_id = 12
and trunc(bsl.date_billed_from) >= trunc(p_termination_date)
and bsl.amount < 0;
Select nvl(sum(decode(sign(trunc(bsl.date_billed_from) - trunc(p_termination_date)) ,-1,
((trunc(bsl.date_billed_to) - trunc(p_termination_date) + 1) * bsl.amount) /
(trunc(bsl.date_billed_to) - trunc(bsl.date_billed_from) + 1) ,bsl.amount )),0)
from oks_bill_cont_lines bcl,
oks_bill_sub_lines bsl,
okc_k_lines_b line
where bsl.bcl_id = bcl.id
and line.id = bcl.cle_id
and bcl.bill_action <> 'TR'
and line.dnz_chr_id = p_hdr_id
and line.lse_id = 12
and trunc(bsl.date_billed_from) >= trunc(p_termination_date)
and bsl.amount > 0;
l_bcl_update_id NUMBER;
l_bsl_update_id NUMBER;
l_bsl_update_id := g_bsl_id;
UPDATE oks_bill_sub_lines
SET amount = l_bsl_credit_amount
WHERE id = l_bsl_update_id ;
l_bcl_update_id := g_bcl_id;
UPDATE oks_bill_cont_lines
SET amount = l_bcl_credit_amount
WHERE id = l_bcl_update_id ;
l_bsl_update_id := g_bsl_id;
UPDATE oks_bill_sub_lines
SET amount = l_bsl_credit_amount
WHERE id = l_bsl_update_id;
l_bcl_update_id := g_bcl_id;
UPDATE oks_bill_cont_lines
SET amount = l_bcl_credit_amount
WHERE id = l_bcl_update_id;
l_bsl_update_id := g_bsl_id;
UPDATE oks_bill_sub_lines
SET amount = l_bsl_credit_amount
WHERE id = l_bsl_update_id ;
l_bcl_update_id := g_bcl_id;
UPDATE oks_bill_cont_lines
SET amount = l_bcl_credit_amount
WHERE id = l_bcl_update_id ;
l_bsl_update_id := g_bsl_id;
UPDATE oks_bill_sub_lines
SET amount = l_bsl_credit_amount
WHERE id = l_bsl_update_id;
l_bcl_update_id := g_bcl_id;
UPDATE oks_bill_cont_lines
SET amount = l_bcl_credit_amount
WHERE id = l_bcl_update_id;
l_bsl_update_id := g_bsl_id;
UPDATE oks_bill_sub_lines
SET amount = l_bsl_credit_amount
WHERE id = l_bsl_update_id ;
l_bcl_update_id := g_bcl_id;
UPDATE oks_bill_cont_lines
SET amount = l_bcl_credit_amount
WHERE id = l_bcl_update_id ;
l_bsl_update_id := g_bsl_id;
UPDATE oks_bill_sub_lines
SET amount = l_bsl_credit_amount
WHERE id = l_bsl_update_id;
l_bcl_update_id := g_bcl_id;
UPDATE oks_bill_cont_lines
SET amount = l_bcl_credit_amount
WHERE id = l_bcl_update_id;
update oks_k_lines_b topline set topline.tax_amount = ( select sum(tax_amount) from
oks_k_lines_b oksline, okc_k_lines_b okcline
where okcline.id = oksline.cle_id
and okcline.cle_id = p_id
and okcline.date_cancelled is null )
where topline.cle_id = p_id;
update oks_k_headers_b hdr set hdr.tax_amount = ( select sum(tax_amount) from
oks_k_lines_b oksline, okc_k_lines_b okcline
where okcline.id = oksline.cle_id
and okcline.dnz_chr_id = l_line_parameter_rec.p_dnz_chr_id
and okcline.date_cancelled is null
and lse_id in (1,12,19,46) )
where hdr.chr_id = l_line_parameter_rec.p_dnz_chr_id;
update oks_k_lines_b topline set topline.tax_amount = ( select sum(tax_amount) from
oks_k_lines_b oksline, okc_k_lines_b okcline
where okcline.id = oksline.cle_id
and okcline.cle_id = line_rec.id
and okcline.date_cancelled is null )
where topline.cle_id = line_rec.id;
l_bsl_update_id := g_bsl_id;
UPDATE oks_bill_sub_lines
SET amount = l_bsl_credit_amount
WHERE id = l_bsl_update_id ;
l_bcl_update_id := g_bcl_id;
UPDATE oks_bill_cont_lines
SET amount = l_bcl_credit_amount
WHERE id = l_bcl_update_id ;
update oks_k_lines_b
set suppressed_credit = suppressed_credit + (l_termination_amount - g_credit_amount)
, override_amount = override_amount - (l_termination_amount - g_credit_amount)
where cle_id = l_bsl_cle_id;
update oks_k_lines_b
set suppressed_credit = suppressed_credit + (l_termination_amount - g_credit_amount)
, override_amount = override_amount - (l_termination_amount - g_credit_amount)
where cle_id = l_bcl_cle_id;
update oks_k_lines_b
set credit_amount = credit_amount + (l_termination_amount - g_credit_amount)
, override_amount = override_amount - (l_termination_amount - g_credit_amount)
where cle_id = l_bsl_cle_id;
update oks_k_lines_b
set credit_amount = credit_amount + (l_termination_amount - g_credit_amount)
, override_amount = override_amount - (l_termination_amount - g_credit_amount)
where cle_id = l_bcl_cle_id;
update okc_k_lines_b
set price_negotiated = price_negotiated - ( l_termination_amount - g_credit_amount )
where id = l_bcl_cle_id
and lse_id <> 12;
update okc_k_lines_b
set price_negotiated = price_negotiated - ( l_termination_amount - g_credit_amount )
where id = l_bsl_cle_id
and lse_id <>13;
update okc_k_headers_b
set estimated_amount = estimated_amount - ( l_termination_amount - g_credit_amount )
where id = p_id;
update oks_k_lines_b
set tax_amount = G_RAIL_REC.tax_value
where cle_id = l_bsl_cle_id;
l_bsl_update_id := g_bsl_id;
UPDATE oks_bill_sub_lines
SET amount = l_bsl_credit_amount
WHERE id = l_bsl_update_id ;
l_bcl_update_id := g_bcl_id;
UPDATE oks_bill_cont_lines
SET amount = l_bcl_credit_amount
WHERE id = l_bcl_update_id ;
update oks_k_lines_b
set suppressed_credit = suppressed_credit + (l_termination_amount - g_credit_amount)
, override_amount = override_amount - (l_termination_amount - g_credit_amount)
where cle_id = l_bsl_cle_id;
update oks_k_lines_b
set suppressed_credit = suppressed_credit + (l_termination_amount - g_credit_amount)
, override_amount = override_amount - (l_termination_amount - g_credit_amount)
where cle_id = l_bcl_cle_id;
update oks_k_lines_b
set credit_amount = credit_amount + (l_termination_amount - g_credit_amount)
, override_amount = override_amount - (l_termination_amount - g_credit_amount)
where cle_id = l_bsl_cle_id;
update oks_k_lines_b
set credit_amount = credit_amount + (l_termination_amount - g_credit_amount)
, override_amount = override_amount - (l_termination_amount - g_credit_amount)
where cle_id = l_bcl_cle_id;
update okc_k_lines_b
set price_negotiated = price_negotiated + ( g_credit_amount - l_termination_amount )
where id = l_bcl_cle_id
and lse_id <> 12;
update okc_k_lines_b
set price_negotiated = price_negotiated + ( g_credit_amount - l_termination_amount )
where id = l_bsl_cle_id
and lse_id <>13;
update okc_k_headers_b
set estimated_amount = estimated_amount + ( g_credit_amount - l_termination_amount )
where id = p_id;
update oks_k_lines_b
set tax_amount = G_RAIL_REC.tax_value
where cle_id = l_bsl_cle_id;
update oks_k_lines_b topline set topline.tax_amount = ( select sum(tax_amount) from
oks_k_lines_b oksline, okc_k_lines_b okcline
where okcline.id = oksline.cle_id
and okcline.cle_id = l_bcl_cle_id
and okcline.date_cancelled is null )
where topline.cle_id = l_bcl_cle_id;
update oks_k_headers_b hdr set hdr.tax_amount = ( select sum(tax_amount) from
oks_k_lines_b oksline, okc_k_lines_b okcline
where okcline.id = oksline.cle_id
and okcline.dnz_chr_id = p_id
and okcline.date_cancelled is null
and lse_id in (1,12,19,46) )
where hdr.chr_id = p_id;
SELECT LIne.lse_id,
rline.termn_method,
rline.usage_type,
rline.usage_period
FROM okc_k_lines_b line,
oks_k_lines_b rline
WHERE line.id = p_k_line_id
AND rline.cle_id = line.id;
SELECT obj.id
From OKC_K_REL_OBJS_V obj,
OKC_K_LINES_B ln
Where obj.cle_id = ln.id
And ln.cle_id = p_k_line_id;
SELECT start_date
FROM okc_k_lines_b
WHERE 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 = 'RI';
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 ='RI'
AND bsd.bsl_id = bsl.id;
SELECT count(*) + 1
FROM oks_bill_sub_lines
WHERE cle_id = p_cle_id;
SELECT sum(result)
FROM oks_bill_sub_line_dtls bsd,
oks_bill_sub_lines bsl
WHERE bsl.cle_id = p_cle_id
AND bsd.bsl_id = bsl.id;
SELECT MAX(date_end)
FROM oks_level_elements
WHERE cle_id = p_cle_id
AND trunc(date_end) < trunc(p_date)
AND date_completed is NOT NULL;
SELECT MIN(date_start)
FROM oks_level_elements
WHERE cle_id = p_cle_id
AND trunc(date_start) > trunc(p_date)
AND date_completed is NOT NULL;
SELECT UOM_CODE
FROM oks_stream_levels_b
WHERE cle_id = p_cle_id
AND trunc(p_date) >= START_DATE
AND trunc(p_date) <= END_DATE;
SELECT net_reading
,value_timestamp
,counter_value_id
,counter_group_id
FROM Cs_ctr_counter_values_v
WHERE counter_id = p_counter_id
AND decode(p_override_valid_flag,'YES',nvl(override_valid_flag,'N'),'1') = decode(p_override_valid_flag,'YES','Y','1')
AND nvl(valid_flag,'N') = 'N'
/* Modified by sjanakir for Bug # 7168765
Order by value_timestamp desc; */
SELECT nvl(end_reading,0) qty
FROM Oks_bill_sub_line_dtls ldtl
,oks_bill_sub_lines line
WHERE line.cle_id = p_cle_id
AND ldtl.bsl_id = line.id
AND nvl(end_reading,0) > 0
AND trunc(date_billed_from) < trunc(p_start_date)
Order By date_billed_to desc ;
SELECT nvl(end_reading,0) qty
FROM Oks_bsd_pr ldtl
,oks_bsl_pr line
WHERE line.cle_id = p_cle_id
AND ldtl.bsl_id = line.id
AND nvl(end_reading,0) > 0
AND trunc(date_billed_from) < trunc(p_start_date)
Order By date_billed_to desc ,ldtl.creation_date desc ;
SELECT NVL(sign(ldtl.amount)*Result,0) + nvl(base_reading,0) qty
FROM Oks_bill_sub_line_dtls ldtl
,oks_bill_sub_lines line
WHERE line.cle_id = p_cle_id
AND ldtl.bsl_id = line.id
ORDER BY date_billed_to desc ;
SELECT NVL(sign(ldtl.amount)*Result,0) + nvl(base_reading,0) qty
FROM Oks_bsd_pr ldtl
,oks_bsl_pr line
WHERE line.cle_id = p_cle_id
AND ldtl.bsl_id = line.id
ORDER BY date_billed_to desc;
SELECT end_reading read,ccr_id,cgr_id
FROM Oks_bill_sub_line_dtls ldtl
,oks_bill_sub_lines line
WHERE line.cle_id = p_cle_id
AND ldtl.bsl_id = line.id
-- Bug#4730007 nechatur 1-Dec-2005
-- Order By ldtl.id desc;
SELECT end_reading read,ccr_id,cgr_id
FROM Oks_bsd_pr ldtl
,oks_bsl_pr line
WHERE line.cle_id = p_cle_id
AND ldtl.bsl_id = line.id
AND trunc(line.date_billed_from) < trunc(p_start_date)
-- Bug#4730007 nechatur 1-Dec-2005
-- Order By ldtl.id desc;
SELECT nvl(base_Reading,0) Base_reading
FROM oks_k_lines_b
WHERE cle_id = p_cle_id;
SELECT Max(ccr.counter_value_id)
FROM Cs_ctr_counter_values_v ccr
WHERE ccr.counter_id = p_counter_id
AND trunc(ccr.Value_timestamp) Between trunc(P_start_date) And trunc(P_end_date)
AND decode(p_override_valid_flag,'YES',ccr.override_valid_flag,'1') =
decode(p_override_valid_flag,'YES','Y','1')
AND nvl(valid_flag,'N') = 'N';
SELECT net_reading
,counter_group_id
,value_timestamp
FROM Cs_ctr_counter_values_v
WHERE counter_value_id = p_max_ctr_id
AND nvl(valid_flag,'N') = 'N';
SELECT Max(ccr.counter_value_id)
FROM Cs_ctr_counter_values_v ccr
WHERE ccr.counter_id = p_counter_id
AND trunc(ccr.Value_timestamp) = trunc(P_start_date)
AND decode(p_override_valid_flag,'YES',ccr.override_valid_flag,'1') =
decode(p_override_valid_flag,'YES','Y','1')
AND nvl(valid_flag,'N') = 'N';
SELECT item.object1_id1
FROM OKC_K_ITEMS item
WHERE item.cle_id = p_cle_id;
SELECT uom_code
FROM Cs_counters_v
WHERE counter_id = p_counter_id;
SELECT NVL(sign(ldtl.amount)*Result,0) + nvl(base_reading,0) qty
FROM Oks_bill_sub_line_dtls ldtl
,oks_bill_sub_lines line
WHERE line.cle_id = p_cle_id
AND ldtl.bsl_id = line.id
AND trunc(date_billed_from) < trunc(p_start_date)
ORDER BY date_billed_to desc;
Select tce_code
,quantity
From Okc_time_code_units_v
Where uom_code = p_code
And active_flag = 'Y';
PROCEDURE update_bsl
(
x_ret_stat OUT NOCOPY VARCHAR2,
p_dnz_chr_id IN NUMBER,
p_bsl_id IN NUMBER,
p_bcl_id IN NUMBER,
P_AMOUNT IN NUMBER,
P_currency_code IN Varchar2,
P_PRV IN NUMBER
) IS
l_amount_holder NUMBER := 0;
SELECT conversion_rate con_rate,
conversion_rate_date con_date,
conversion_type con_type
FROM okc_k_headers_b
WHERE id = p_chr_id;
UPDATE oks_bill_cont_lines
SET amount = nvl(amount,0) + round_amount,
currency_code = l_cur_holder
WHERE id = p_bcl_id;
UPDATE oks_bill_sub_lines
SET amount = round_amount,
average = 0
WHERE id = p_bsl_id;
UPDATE oks_bill_sub_line_dtls
SET amount = round_amount
WHERE bsl_id = p_bsl_id;
UPDATE oks_bcl_pr
SET amount = nvl(amount,0) + round_amount,
currency_code = l_cur_holder
WHERE id = p_bcl_id;
UPDATE oks_bsl_pr
SET amount = round_amount,
average = 0
WHERE id = p_bsl_id;
UPDATE oks_bsd_pr
SET amount = round_amount
WHERE bsl_id = p_bsl_id;
END update_bsl;
PROCEDURE update_bcl
(
P_CALLEDFROM IN NUMBER,
x_ret_stat OUT NOCOPY VARCHAR2,
p_bcl_id IN NUMBER,
P_SENT_YN IN VARCHAR2,
P_BILL_ACTION IN VARCHAR2,
P_AMOUNT IN NUMBER,
P_CURRENCY_CODE IN VARCHAR2,
P_PRV IN NUMBER
) IS
SUBTYPE l_bclv_tbl_type_in is OKS_bcl_PVT.bclv_tbl_type;
UPDATE oks_bill_cont_lines
SET amount = nvl(p_amount,0) + nvl(amount,0),
bill_Action = nvl(p_bill_action,bill_action) ,
currency_code = p_currency_code,
sent_yn = nvl(p_sent_yn , sent_yn)
WHERE id = l_bclv_tbl_in(1).ID ;
UPDATE oks_bcl_pr
SET amount = nvl(p_amount,0) + nvl(amount,0),
bill_Action = nvl(p_bill_action,bill_action) ,
currency_code = p_currency_code,
sent_yn = nvl(p_sent_yn , sent_yn)
WHERE id = l_bcl_pr_tbl_in(1).ID ;
END update_bcl;
PROCEDURE insert_bcl
(
P_CALLEDFROM IN NUMBER,
x_return_stat OUT NOCOPY VARCHAR2,
p_CLE_ID IN NUMBER,
p_DATE_BILLED_FROM IN DATE,
P_DATE_BILLED_TO IN DATE,
P_DATE_NEXT_INVOICE IN DATE,
P_BILL_ACTION IN VARCHAR2 ,
P_OKL_FLAG IN NUMBER,
P_PRV IN NUMBER,
P_MSG_COUNT IN OUT NOCOPY NUMBER,
P_MSG_DATA IN OUT NOCOPY VARCHAR2,
X_BCL_ID IN OUT NOCOPY NUMBER
)IS
SUBTYPE l_bclv_tbl_type_in is OKS_bcl_PVT.bclv_tbl_type;
Select Id
From Oks_bill_cont_lines
Where Cle_id = p_cle_id_in
And trunc(date_billed_from) = trunc(p_date_billed_from_in)
And trunc(date_billed_to) = trunc(p_date_billed_to_in)
And bill_action = p_bill_action
And (btn_id is null OR btn_id = -55)
Order by Date_billed_from desc;
Select Id
From Oks_bcl_pr
Where Cle_id = p_cle_id_in
And trunc(date_billed_from) = trunc(p_date_billed_from_in)
And trunc(date_billed_to) = trunc(p_date_billed_to_in)
And bill_action = p_bill_action
And (btn_id is null OR btn_id = -55)
Order by Date_billed_from desc;
OKS_BILLCONTLINE_PUB.insert_Bill_Cont_Line(
P_API_VERSION => 1.0,
P_INIT_MSG_LIST => 'T',
X_RETURN_STATUS => l_ret_stat,
X_MSG_COUNT => l_msg_cnt,
X_MSG_DATA => l_msg_data,
P_BCLV_TBL => l_bclv_tbl_in,
X_BCLV_TBL => l_bclv_tbl_out
);
l_bcl_pr_tbl_in(1).last_updated_by := FND_GLOBAL.user_id;
l_bcl_pr_tbl_in(1).last_update_date := sysdate;
l_bcl_pr_tbl_in(1).last_update_login := NULL;
OKS_BCL_PRINT_PREVIEW_PUB.insert_bcl_pr(
P_API_VERSION => 1.0,
P_INIT_MSG_LIST => 'T',
X_RETURN_STATUS => l_ret_stat,
X_MSG_COUNT => l_msg_cnt,
X_MSG_DATA => l_msg_data,
P_BCL_PR_TBL => l_bcl_pr_tbl_in,
X_BCL_PR_TBL => l_bcl_pr_tbl_out);
END insert_bcl;
Select Id,amount
From Oks_bill_cont_lines
Where Cle_id = p_cle_id_in
And trunc(date_billed_from) = trunc(p_date_billed_from_in)
And trunc(date_billed_to) = trunc(p_date_billed_to_in)
And bill_Action = p_bill_action
And ( btn_id is null or btn_id = -55)
Order by Date_billed_from desc;
Select Id,amount
From Oks_bcl_pr
Where Cle_id = p_cle_id_in
And trunc(date_billed_from) = trunc(p_date_billed_from_in)
And trunc(date_billed_to) = trunc(p_date_billed_to_in)
And bill_Action = p_bill_action
And ( btn_id is null or btn_id = -55)
Order by Date_billed_from desc;
PROCEDURE insert_all_subline
(
P_CALLEDFROM IN NUMBER,
X_RETURN_STAT OUT NOCOPY VARCHAR2,
P_COVERED_TBL IN OUT NOCOPY COVERED_TBL,
P_CURRENCY_CODE IN VARCHAR2,
P_DNZ_CHR_ID IN NUMBER,
P_PRV IN NUMBER,
P_MSG_COUNT IN OUT NOCOPY NUMBER,
P_MSG_DATA IN OUT NOCOPY VARCHAR2
)
IS
Cursor l_get_conversion_rule (p_chr_id IN NUMBER) is
SELECT conversion_rate con_rate,
conversion_rate_date con_date,
conversion_type con_type
FROM okc_k_headers_b
WHERE id = p_chr_id;
OKS_BILLSUBLINE_PUB.insert_Bill_subLine_Pub
(
P_API_VERSION => 1.0,
P_INIT_MSG_LIST => 'T',
X_RETURN_STATUS => l_ret_stat,
X_MSG_COUNT => l_msg_cnt,
X_MSG_DATA => l_msg_data,
P_BSLV_TBL => l_bslv_tbl_in,
X_BSLV_TBL => l_bslv_tbl_out
);
OKS_BSL_det_PUB.insert_bsl_det_Pub
(
P_API_VERSION => 1.0,
P_INIT_MSG_LIST => 'T',
X_RETURN_STATUS => l_ret_stat,
X_MSG_COUNT => l_msg_cnt,
X_MSG_DATA => l_msg_data,
P_BSDV_TBL => l_bsdv_tbl_in,
X_BSDV_TBL => l_bsdv_tbl_out
);
l_bsl_pr_tbl_in(1).LAST_UPDATED_BY := FND_GLOBAL.user_id;
l_bsl_pr_tbl_in(1).LAST_UPDATE_DATE := sysdate;
l_bsl_pr_tbl_in(1).LAST_UPDATE_LOGIN := NULL;
OKS_BILLSubLINE_PRV_PUB.insert_bsl_pr
(
P_API_VERSION => 1.0,
P_INIT_MSG_LIST => 'T',
X_RETURN_STATUS => l_ret_stat,
X_MSG_COUNT => l_msg_cnt,
X_MSG_DATA => l_msg_data,
P_BSL_PR_TBL => l_bsl_pr_tbl_in,
X_BSL_PR_TBL => l_bsl_pr_tbl_out
);
l_bsd_pr_tbl_in(1).last_updated_by := FND_GLOBAL.user_id;
l_bsd_pr_tbl_in(1).last_update_date := sysdate;
l_bsd_pr_tbl_in(1).last_update_login := NULL;
OKS_BSD_PRV_PUB.insert_bsd_pr
(
P_API_VERSION => 1.0,
P_INIT_MSG_LIST => 'T',
X_RETURN_STATUS => l_ret_stat,
X_MSG_COUNT => l_msg_cnt,
X_MSG_DATA => l_msg_data,
P_BSD_PR_TBL => l_bsd_pr_tbl_in,
X_BSD_PR_TBL => l_bsd_pr_tbl_out
);
Update_bcl
(
P_CALLEDFROM,
l_ret_stat,
p_covered_tbl(1).bcl_id,
Null,
Null,
l_amount,
l_cur_holder,
P_PRV
);
p_covered_tbl.delete;
END insert_all_subline;
Select rule_information1 profile_id
From OKC_RULES_B RL
,OKC_RULE_GROUPS_B RG
Where RG.dnz_chr_id = id_in
And RG.cle_id Is Null
And RG.id = RL.rgp_id
And rule_information_category = 'BPF';
Select Summarised_yn
From OKS_BILLING_PROFILES_V
Where id = id_in;
Select line.id,
line.dnz_chr_id,
line.cle_id,
line.lse_id,
line.start_date,
line.end_date,
line.price_negotiated,
line.date_terminated,
hdr.currency_code,
rules.rule_information2 Billing_freq,
rules.rule_information4 First_inv_dt,
rules.rule_information3 First_billto_dt,
rules.rule_information5 Primary_dur,
rules.rule_information7 Secondary_dur,
rules.rule_information6 Primary_period,
rules.rule_information8 Secondary_period
From OKC_K_LINES_B line,
OKC_K_HEADERS_B Hdr,
OKC_RULES_B rules,
OKC_RULE_GROUPS_B rlgrp
Where line.lse_id in (1,12)
And line.dnz_chr_id = Hdr.id
And rlgrp.cle_id = line.id
And rules.rgp_id = rlgrp.id
And rules.rule_information_category = 'SBG'
And (Hdr.scs_code = 'SERVICE' OR
/* This code is added for warranty lines that are renewed */
(Hdr.scs_code = 'WARRANTY' AND Hdr.chr_id_renewed IS not Null))
And Hdr.Template_yn = 'N'
And OKC_ASSENT_PUB.line_operation_allowed(line.id,'INVOICE')= 'T'
And Hdr.id = p_contract_id
--And Hdr.Contract_number = NVL(p_contract_number,hdr.contract_number)
For Update;
Select id,
cle_id,
price_negotiated,
start_date,
end_date,
date_terminated
From OKC_K_LINES_B
Where cle_id = p_cle_id
And lse_id in (8,7,9,10,11,13,35);
Select *
From oks_bill_cont_lines
where cle_id = p_cle_id
and bill_action in ('RI', 'AD')
order by cle_id, date_billed_from;
Select nvl(sum(NVL(amount,0)),0) amount_billed
From oks_bill_sub_lines
Where cle_id = id_in;
Select item.Object1_id1,
mtl.usage_item_flag,
mtl.service_item_flag
From Okc_K_items Item,
mtl_system_items_b mtl
Where item.cle_id = p_cle_id
And mtl.inventory_item_id = item.object1_id1;
Select Rule_information10 Usage_Type
From OKC_RULES_B RL,
OKC_RULE_GROUPS_B RG
Where RG.cle_id = p_id
And RG.id = RL.rgp_id
And rule_information_category = 'QRE';
Select okc.Number_of_items,
tl.Unit_of_measure uom_code
From OKC_K_ITEMS_V 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 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);
Select id
From OKS_BILL_CONT_LINES
Where cle_id = p_bcl_rec_cle_id
And date_billed_to = p_bcl_rec_date_billed_to
And bill_action = 'AD';
l_cov_tbl.delete;
Select count(*) into l_ad_cnt
From OKS_BILL_CONT_LINES
Where cle_id = l_billed_cle_id
And Bill_Action = 'AD';
OKS_BILL_REC_PUB.Insert_bcl
(
p_calledfrom => p_calledfrom,
x_return_stat => l_return_status,
p_cle_id => l_billed_cle_id,
p_date_billed_from => l_date_billed_from,
p_date_billed_to => l_date_billed_to,
p_date_next_invoice => l_date_next_invoice,
p_bill_action => 'AD',
p_okl_flag => l_okl_flag,
p_prv => 1,
p_msg_count => l_msg_count,
p_msg_data => l_msg_data,
x_bcl_id => l_bcl_id
);
select sum(amount) into l_amount_billed
oks_bill_sub_lines_v
cle_id = l_covlvl_rec.id;
OKS_BILL_REC_PUB.Insert_all_subline
(
p_calledfrom => p_calledfrom,
x_return_stat => l_return_status,
p_covered_tbl => l_cov_tbl,
p_currency_code => l_bill_rec.currency_code,
p_dnz_chr_id => l_bill_rec.dnz_chr_id,
p_prv => 1,
p_msg_count => l_msg_count ,
p_msg_data => l_msg_data
);
FND_FILE.PUT_LINE( FND_FILE.LOG, 'after insert into sublines '||' '||l_return_status );
FND_FILE.PUT_LINE( FND_FILE.LOG, 'insert into table failed Contract line id :'||' '||l_bill_rec.id);
FND_FILE.PUT_LINE( FND_FILE.LOG, 'insert into table failed Contract line id :'||' '||l_bill_rec.id);
SELECT id FROM OKC_K_REL_OBJS_V
WHERE cle_id = p_line_id ;
SELECT bsl.id,
bcl.cle_id top_line_id
FROM oks_bill_cont_lines bcl,
oks_bill_sub_lines bsl
WHERE bsl.bcl_id = bcl.id
AND bcl.bill_action = 'TR'
AND bsl.cle_id = p_cle_id;
SELECT line1.lse_id, line1.cle_id ,rline.termn_method,
rline.usage_type, rline.usage_period,
line1.end_date,line1.sts_code,
hdr.id
FROM okc_k_lines_b line2,
okc_k_lines_b line1,
okc_k_headers_b hdr,
oks_k_lines_b rline
WHERE line1.id = p_cle_id
AND line2.id = line1.cle_id
AND rline.cle_id = line2.id
AND hdr.id = line1.dnz_chr_id
AND hdr.sts_code <> 'QA_HOLD'
AND (exists ( SELECT 1 from okc_assents a
where Hdr.scs_code = a.scs_code
and line1.sts_code = a.sts_code
and line1.sts_code <> 'HOLD'
and a.opn_code = 'INVOICE'
and a.allowed_yn = 'Y')
OR
(line1.sts_code = 'HOLD'));
SELECT bsl.id FROM oks_bill_sub_lines bsl,
oks_bill_cont_lines bcl
WHERE bsl.cle_id = p_id
AND bsl.bcl_id = bcl.id
AND bcl.bill_action = 'RI';
Select amount from oks_bill_cont_lines
where id = p_bcl_id;
Select amount from oks_bill_sub_lines
where id = p_bsl_id;
SELECT ste_code
FROM okc_statuses_b
WHERE code = p_code;
Select nvl(sum(decode(sign(trunc(bsl.date_billed_from) - trunc(p_termination_date)) ,-1,
((trunc(bsl.date_billed_to) - trunc(p_termination_date) + 1) * bsl.amount) /
(trunc(bsl.date_billed_to) - trunc(bsl.date_billed_from) + 1) ,bsl.amount )),0)
-- nvl(sum(bsl.amount),0)
from oks_bill_cont_lines bcl,
oks_bill_sub_lines bsl
where bcl.cle_id = p_cle_id
and bsl.bcl_id = bcl.id
and bcl.bill_action <> 'TR'
--and trunc(bsl.date_billed_from) >= trunc(p_termination_date)
and trunc(bsl.date_billed_to) >= trunc(p_termination_date) --bug#5276678
and bsl.amount < 0;
Select nvl(sum(decode(sign(trunc(bsl.date_billed_from) - trunc(p_termination_date)) ,-1,
((trunc(bsl.date_billed_to) - trunc(p_termination_date) + 1) * bsl.amount) /
(trunc(bsl.date_billed_to) - trunc(bsl.date_billed_from) + 1) ,bsl.amount )),0)
from oks_bill_cont_lines bcl,
oks_bill_sub_lines bsl
where bcl.cle_id = p_cle_id
and bsl.bcl_id = bcl.id
and bcl.bill_action <> 'TR'
--and trunc(bsl.date_billed_from) >= trunc(p_termination_date)
and trunc(bsl.date_billed_to) >= trunc(p_termination_date) --bug#5276678
and bsl.amount > 0;
Select 1 from oks_bill_cont_lines
where cle_id = p_cle_id
and bill_action = 'AV';
UPDATE oks_bill_sub_lines
SET amount = l_bsl_credit_amount
WHERE id = l_bsl_id ;
UPDATE oks_bill_cont_lines
SET amount = l_bcl_credit_amount
WHERE id = l_bcl_id ;
UPDATE oks_bill_sub_lines
SET amount = l_bsl_credit_amount
WHERE id = l_bsl_id ;
UPDATE oks_bill_cont_lines
SET amount = l_bcl_credit_amount
WHERE id = l_bcl_id ;
okc_contract_pub.update_contract_line(
p_api_version => l_api_version,
p_init_msg_list => l_init_msg_list,
p_restricted_update => okc_api.g_true,
x_return_status => l_return_status,
x_msg_count => l_msg_cnt,
x_msg_data => l_msg_data,
p_clev_tbl => l_clev_tbl_in,
x_clev_tbl => l_clev_tbl_out);
update oks_k_lines_b topline set topline.tax_amount = ( select sum(tax_amount) from
oks_k_lines_b oksline, okc_k_lines_b okcline
where okcline.id = oksline.cle_id
and okcline.cle_id = l_top_line_id
and okcline.date_cancelled is null )
where topline.cle_id = l_top_line_id;
update oks_k_headers_b hdr set hdr.tax_amount = ( select sum(tax_amount) from
oks_k_lines_b oksline, okc_k_lines_b okcline
where okcline.id = oksline.cle_id
and okcline.dnz_chr_id = l_hdr_id
and okcline.date_cancelled is null
and lse_id in (1,12,19,46) )
where hdr.chr_id = l_hdr_id;
SELECT max(date_billed_to)
FROM oks_bill_sub_lines
WHERE cle_id = p_cle_id;
SELECT start_date,end_date,sts_code,
lse_id,
dnz_chr_id
FROM okc_k_lines_b
WHERE id = p_id ;
SELECT usage_type
FROM oks_k_lines_b
WHERE cle_id = p_id ;
SELECT 'x'
FROM CS_INCIDENTS_ALL_B sr,
okc_k_items cim
where cim.cle_id = p_id
and sr.customer_product_id = cim.object1_id1
and sr.status_flag = 'O';
SELECT 'x'
FROM okc_k_items cim,
CS_INCIDENTS_ALL_B sr
where cim.cle_id = p_cp_line_id
and sr.contract_service_id = p_top_line_id
and sr.customer_product_id = to_number(cim.object1_id1)
and sr.status_flag = 'O' ;
SELECT p.line_number||'.'||s.line_number,
hdr.contract_number
FROM okc_k_lines_b p,
okc_k_lines_b s,
okc_k_headers_b hdr
WHERE s.id=p_id
AND p.id=s.cle_id
AND hdr.id=p.dnz_chr_id;
Select amount from oks_bill_cont_lines
where id = p_bcl_id;
Select amount from oks_bill_sub_lines
where id = p_bsl_id;
okc_contract_pub.update_contract_line(
p_api_version => l_api_version,
p_init_msg_list => l_init_msg_list,
p_restricted_update => okc_api.g_true,
x_return_status => l_return_status,
x_msg_count => l_msg_cnt,
x_msg_data => l_msg_data,
p_clev_tbl => l_clev_tbl_in,
x_clev_tbl => l_clev_tbl_out);
UPDATE oks_bill_sub_lines
SET amount = l_bsl_credit_amount
WHERE id = l_bsl_id ;
UPDATE oks_bill_cont_lines
SET amount = l_bcl_credit_amount
WHERE id = l_bcl_id ;
UPDATE oks_bill_sub_lines
SET amount = l_bsl_credit_amount
WHERE id = l_bsl_id ;
UPDATE oks_bill_cont_lines
SET amount = l_bcl_credit_amount
WHERE id = l_bcl_id ;
SELECT nvl(SUM(ubt_amount),0) unbilled
,nvl(SUM(credit_amount),0) credited
,nvl(SUM(suppressed_credit),0) suppressed
,nvl(SUM(override_amount),0) overridden
FROM OKC_K_LINES_B OKCL
,OKS_K_LINES_B OKSL
WHERE OKCL.dnz_chr_id = p_id
AND OKCL.lse_id in ( 7,8,9,10,11,13,25,35,46)
AND OKCL.id = OKSL.cle_id
AND NOT exists (select 'x' from okc_k_lines_b cle,
oks_k_lines_b kln
where cle.id = OKCL.cle_id
and kln.cle_id = cle.id
and kln.usage_type in ('VRT','QTY','FRT'));
SELECT nvl(SUM(BSL.amount),0) billed
FROM OKC_K_LINES_B OKCL
,OKS_BILL_CONT_LINES BCL
,OKS_BILL_SUB_LINES BSL
WHERE OKCL.dnz_chr_id = p_id
AND OKCL.lse_id in ( 7,8,9,10,11,13,25,35,46)
AND OKCL.id = BSL.cle_id
AND BSL.bcl_id = BCL.id
AND BCL.bill_action = 'RI'
AND NOT exists (select 'x' from okc_k_lines_b cle,
oks_k_lines_b kln
where cle.id = OKCL.cle_id
and kln.cle_id = cle.id
and kln.usage_type in ('VRT','QTY','FRT'));
SELECT nvl(SUM(ubt_amount),0) unbilled
,nvl(SUM(credit_amount),0) credited
,nvl(SUM(suppressed_credit),0) suppressed
,nvl(SUM(override_amount),0) overridden
FROM OKC_K_LINES_B OKCL
,OKS_K_LINES_B OKSL
WHERE ( OKCL.cle_id = p_id
OR (OKCL.id = p_id and OKCL.lse_id = 46 ))
AND OKCL.lse_id in ( 7,8,9,10,11,13,25,35,46)
AND OKCL.id = OKSL.cle_id
AND NOT exists (select 'x' from okc_k_lines_b cle,
oks_k_lines_b kln
where cle.id = OKCL.cle_id
and kln.cle_id = cle.id
and kln.usage_type in ('VRT','QTY','FRT'));
SELECT nvl(SUM(BSL.amount),0) billed
FROM OKC_K_LINES_B OKCL
,OKS_BILL_CONT_LINES BCL
,OKS_BILL_SUB_LINES BSL
WHERE ( OKCL.cle_id = p_id
OR ( OKCL.ID = p_id and OKCL.lse_id = 46))
AND OKCL.lse_id in ( 7,8,9,10,11,13,25,35,46)
AND OKCL.id = BSL.cle_id
AND BSL.bcl_id = BCL.id
AND BCL.bill_action = 'RI'
AND NOT exists (select 'x' from okc_k_lines_b cle,
oks_k_lines_b kln
where cle.id = OKCL.cle_id
and kln.cle_id = cle.id
and kln.usage_type in ('VRT','QTY','FRT'));
SELECT nvl(SUM(ubt_amount),0) unbilled
,nvl(SUM(credit_amount),0) credited
,nvl(SUM(suppressed_credit),0) suppressed
,nvl(SUM(override_amount),0) overridden
FROM OKS_K_LINES_B OKSL
WHERE OKSL.cle_id = p_id;
SELECT nvl(SUM(BSL.amount),0) billed
FROM OKS_BILL_CONT_LINES BCL
,OKS_BILL_SUB_LINES BSL
WHERE BSL.cle_id = p_id
AND BSL.bcl_id = BCL.id
AND BCL.bill_action = 'RI';
SELECT id top_line_id ,
dnz_chr_id hdr_id,
lse_id lse_id,
price_negotiated price_negotiated
FROM okc_k_lines_b okcl
WHERE dnz_chr_id = p_top_line_id
AND okcl.lse_id in (1,12,14,19,46)
AND okcl.date_cancelled is null --LLC BUG FIX 4742661
AND okcl.date_terminated is null ;
SELECT id sub_line_id ,
dnz_chr_id hdr_id,
start_date start_date
FROM okc_k_lines_b okcl
WHERE cle_id = p_top_line_id
AND okcl.lse_id in (7,8,9,10,11,13,35,25)
AND okcl.date_cancelled is NULL --LLC BUG FIX 4742661
AND okcl.date_terminated is null ;
SELECT Price_negotiated orginal_amount ,
cle_id top_line_id ,
dnz_chr_id hdr_id
FROM okc_k_lines_b okcl
WHERE id = p_sub_line_id ;
SELECT sum(nvl(amount,0)) bill_amount
FROM oks_level_elements
WHERE cle_id = p_sub_line_id ;
SELECT sum(bsl.amount) suppressed_credit
FROM oks_bill_sub_lines bsl,
oks_bill_cont_lines bcl
WHERE bsl.cle_id = p_sub_line_id
AND bcl.id = bsl.bcl_id
AND btn_id is null
AND bcl.bill_action = 'TR';
SELECT sum(bsl.amount) suppressed_credit
FROM oks_bill_sub_lines bsl,
oks_bill_cont_lines bcl
WHERE bsl.cle_id = p_sub_line_id
AND bcl.id = bsl.bcl_id
AND btn_id = -44
AND bcl.bill_action = 'TR';
SELECT lse_id ,
price_negotiated,
dnz_chr_id
FROM OKC_K_LINES_B OKCL
WHERE OKCL.id = p_id ;
SELECT currency_code
FROM okc_k_headers_b
WHERE id = p_id;
SELECT hdr.currency_code ,hdr.id
FROM okc_k_lines_b lines,
okc_k_headers_b hdr
WHERE lines.id = p_id
AND hdr.id = lines.dnz_chr_id;
SELECT count(oks.full_credit)
FROM OKC_K_LINES_B OKC,
OKS_K_LINES_B OKS
WHERE OKC.cle_ID = p_top_line_id
AND OKC.id = OKS.CLE_id
and OKC.lse_id in (7,8,9,10,11,13,18,25,35)
and OKC.date_cancelled is NULL --LLC BUG FIX 4742661
and OKC.date_terminated is not null
and ( oks.full_credit is null
OR oks.full_credit = 'N' );
Select kln.usage_type
from okc_k_lines_b cle1,
okc_k_lines_b cle2,
oks_k_lines_b kln
where cle1.id = p_sub_line_id
and cle2.id = cle1.cle_id
and kln.cle_id = cle2.id;
Select usage_type
from oks_k_lines_b
where cle_id = p_top_line_id;
SELECT ubt_amount
FROM OKS_K_LINES_B
WHERE cle_id = p_id;
UPDATE OKC_K_LINES_B
SET price_negotiated = l_true_value
WHERE id = p_true_value_tbl(1).p_cp_line_id;
UPDATE OKS_K_LINES_B
SET UBT_AMOUNT = l_ubt_amount ,
CREDIT_AMOUNT = l_credit ,
SUPPRESSED_CREDIT = l_suppressed_credit ,
OVERRIDE_AMOUNT = l_overridden ,
TAX_AMOUNT = l_tax_value,
FULL_CREDIT = nvl(p_true_value_tbl(1).p_full_credit,'N')
WHERE cle_id = p_true_value_tbl(1).p_cp_line_id;
UPDATE OKC_K_LINES_B
SET price_negotiated=(SELECT SUM(price_negotiated)
FROM okc_k_lines_b
WHERE cle_id = l_top_line_id
AND date_cancelled is null) --LLC BUG FIX 4742661
WHERE id = l_top_line_id ;
Update OKS_K_lines_b
set ubt_amount = nvl(ubt_amount,0) + nvl(l_ubt_amount,0),
CREDIT_AMOUNT = nvl(CREDIT_AMOUNT,0) + nvl(l_credit,0) ,
SUPPRESSED_CREDIT = nvl(SUPPRESSED_CREDIT,0) + nvl(l_suppressed_credit,0)
WHERE cle_id = l_top_line_id;
UPDATE OKC_K_HEADERS_B
SET estimated_amount=(SELECT SUM(price_negotiated)
FROM OKC_K_LINES_B
WHERE dnz_chr_id = l_hdr_id
AND lse_id in ( 1,12,14,19,46))
WHERE id = l_hdr_id ;
UPDATE OKC_K_LINES_B
SET price_negotiated = l_true_value
WHERE id = l_top_line_rec.sub_line_id;
UPDATE OKS_K_LINES_B
SET UBT_AMOUNT = l_ubt_amount ,
CREDIT_AMOUNT = l_credit ,
SUPPRESSED_CREDIT= l_suppressed_credit ,
OVERRIDE_AMOUNT = l_overridden,
TAX_AMOUNT = l_tax_value,
FULL_CREDIT = nvl(p_true_value_tbl(1).p_full_credit,'N')
WHERE cle_id = l_top_line_rec.sub_line_id;
UPDATE OKC_K_LINES_B
SET price_negotiated=(SELECT SUM(price_negotiated)
FROM okc_k_lines_b
WHERE cle_id = l_top_line_id
AND date_cancelled is null) --LLC BUG FIX 4742661
WHERE id = p_true_value_tbl(1).p_top_line_id ;
UPDATE OKS_K_LINES_B
SET UBT_AMOUNT = nvl(UBT_AMOUNT,0) + nvl(l_tot_ubt_amount,0) ,
CREDIT_AMOUNT = nvl(CREDIT_AMOUNT,0) + nvl(l_tot_credit,0) ,
SUPPRESSED_CREDIT = nvl(SUPPRESSED_CREDIT,0) + nvl(l_tot_suppressed_credit,0) ,
OVERRIDE_AMOUNT = l_tot_overridden,
FULL_CREDIT = l_full_yn
--TAX_AMOUNT = l_tot_tax_value
WHERE cle_id = p_true_value_tbl(1).p_top_line_id ;
UPDATE OKC_K_LINES_B
SET price_negotiated = l_tot_true_value
WHERE id = p_true_value_tbl(1).p_top_line_id;
UPDATE OKS_K_LINES_B
SET UBT_AMOUNT = l_tot_ubt_amount ,
CREDIT_AMOUNT = l_tot_credit ,
SUPPRESSED_CREDIT = l_tot_suppressed_credit ,
OVERRIDE_AMOUNT = l_tot_overridden,
FULL_CREDIT = nvl(p_true_value_tbl(1).p_full_credit,'N'),
TAX_AMOUNT = l_tot_tax_value
WHERE cle_id = p_true_value_tbl(1).p_top_line_id;
UPDATE OKC_K_HEADERS_B
SET estimated_amount=(SELECT SUM(price_negotiated)
FROM OKC_K_LINES_B
WHERE dnz_chr_id = l_hdr_id
AND lse_id in ( 1,12,14,19,46))
WHERE id = l_hdr_id ;
UPDATE OKC_K_HEADERS_B
SET estimated_amount=(SELECT SUM(price_negotiated)
FROM OKC_K_LINES_B
WHERE dnz_chr_id = p_true_value_tbl(1).p_hdr_id
AND lse_id in ( 1,12,14,19,46))
WHERE id = p_true_value_tbl(1).p_hdr_id ;
SELECT pb.id, pb.amount,pb.unit_price , pb.quantity ,
hdr.currency_code
FROM OKS_PRICE_BREAKS pb,
OKC_K_HEADERS_B hdr
WHERE pb.bsl_id = P_BSL_ID
AND pb.chr_id = hdr.id
ORDER BY pb.quantity_from;
UPDATE oks_price_breaks
SET amount = l_amount,
unit_price = l_unit_price
WHERE id = cur.id;
UPDATE oks_price_breaks
SET amount = OKS_EXTWAR_UTIL_PVT.round_currency_amt(amount + (P_TOT_AMOUNT - l_running_total),l_currency_code),
unit_price = round((amount + (P_TOT_AMOUNT - l_running_total)) / quantity,29)
WHERE id = l_id;
SELECT line.id,
line.start_date,
line.end_date,
line.lse_id,
line.cle_id,
line.dnz_chr_id,
line.price_unit*itm.number_of_items unit_price,
rline.price_uom,
line.price_negotiated total_amount,
rline.toplvl_uom_code
FROM okc_k_lines_b line,
oks_k_lines_b rline,
okc_k_items itm
WHERE line.cle_id = p_cle_id
AND line.lse_id in (7,8,9,10,11,13,25,35)
AND line.id = nvl(p_cp_line_id,line.id)
AND rline.cle_id = line.id
AND itm.cle_id = line.id
AND line.date_cancelled is NULL --LLC BUG FIX 4742661
AND line.date_terminated IS NULL;
SELECT sum(bsl.amount) ,
max(bsl.date_billed_From),
max(bsl.date_billed_to)
FROM oks_bill_sub_lines bsl
WHERE bsl.cle_id = p_cle_id;
SELECT bsl.id,
bsl.date_billed_from,
bsl.date_billed_to,
bsl.amount,
bsd.result
FROM oks_bill_cont_lines bcl,
oks_bill_sub_lines bsl,
oks_bill_sub_line_dtls bsd
WHERE bcl.id = bsl.bcl_id
AND bsl.cle_id = p_cle_id
AND bsd.bsl_id = bsl.id
ORDER by bsl.date_billed_to desc;
SELECT max(TRUNC(lvl.date_end))
FROM oks_level_elements lvl
WHERE cle_id = p_cle_id;
SELECT currency_code
FROM okc_k_headers_b
WHERE id = p_id;
SELECT id,trunc(date_start) date_start,
amount,trunc(date_end) date_end
FROM oks_level_elements
WHERE cle_id = p_line_id
AND TRUNC(date_start) > p_date
ORDER BY date_start;
select oel.service_period,oel.service_duration,
oel.ordered_quantity
from okc_k_rel_objs rel,
oe_order_lines_all oel
where rel.cle_id = p_id
and oel.line_id = rel.object1_id1;
UPDATE OKC_K_LINES_B
SET price_negotiated = l_new_price
WHERE id = cur.id;
UPDATE OKS_K_LINES_B
SET UBT_AMOUNT = l_ubt_amount
WHERE cle_id = cur.id;
SELECT line.id,
line.lse_id,
line.start_date,
line.end_date,
line.price_unit*itm.number_of_items unit_price,
rline.price_uom,
rline.toplvl_uom_code,
line.price_negotiated total_amount
FROM okc_k_lines_b line,
oks_k_lines_b rline,
okc_k_items itm
WHERE line.id = p_id
AND rline.cle_id = line.id
AND itm.cle_id = line.id
AND line.date_cancelled is NULL --LLC BUG FIX 4742661
AND line.date_terminated is NULL;
SELECT line.id,
line.lse_id,
line.start_date,
line.end_date,
line.price_unit*itm.number_of_items unit_price,
rline.price_uom,
rline.toplvl_uom_code,
line.price_negotiated total_amount
FROM okc_k_lines_b line,
oks_k_lines_b rline,
okc_k_items itm
WHERE line.cle_id = p_cle_id
AND itm.cle_id = line.id
AND rline.cle_id = line.id
AND line.date_cancelled is NULL --LLC BUG FIX 4742661
AND line.date_terminated is NULL;
SELECT sum(bsl.amount) ,
max(bsl.date_billed_From),
max(bsl.date_billed_to)
FROM oks_bill_sub_lines bsl
WHERE bsl.cle_id = p_cle_id;
select oel.service_period,oel.service_duration,
oel.ordered_quantity
from okc_k_rel_objs rel,
oe_order_lines_all oel
where rel.cle_id = p_id
and oel.line_id = rel.object1_id1;
SELECT max(TRUNC(lvl.date_end))
FROM oks_level_elements lvl
WHERE cle_id = p_cle_id;
SELECT 1
FROM MTL_UNITS_OF_MEASURE_TL TL, okc_time_code_units_v okc
WHERE TL.uom_code = okc.uom_code
AND TL.uom_code = p_uom_code
--AND TL.uom_class = 'Time' commented for bug#5585356
AND okc.active_flag = 'Y'
AND TL.LANGUAGE = USERENV('LANG');
SELECT 1
FROM MTL_UNITS_OF_MEASURE_TL TL, okc_time_code_units_v okc
WHERE TL.uom_code = okc.uom_code
AND TL.uom_code = p_uom_code
--AND TL.uom_class = 'Time' commented for bug#5585356
AND okc.active_flag = 'Y'
AND TL.LANGUAGE = USERENV('LANG');