The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE update_price_uom (
p_chr_id IN NUMBER,
p_price_uom IN VARCHAR2, -- Bug 5139658
x_return_status OUT NOCOPY VARCHAR2
) IS
l_api_name CONSTANT VARCHAR2 (30)
:= 'update_price_uom';
SELECT oks.ID,
oks.cle_id,
oks.object_version_number -- bug 4638902
FROM okc_k_lines_b okc,
oks_k_lines_b oks
WHERE okc.dnz_chr_id = p_chr_id
AND okc.lse_id IN (1, 7, 9, 25, 19)
AND okc.ID = oks.cle_id;
SELECT price_uom
FROM oks_k_headers_b
WHERE chr_id = p_chr_id;
oks_contract_line_pub.update_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_klnv_rec => l_kln_rec_in,
x_klnv_rec => l_kln_rec_out,
p_validate_yn => 'N'
);
p_token1_value => 'oks_reprice_pvt.update_price_uom',
p_token2 => 'UOM_CODE',
p_token2_value => p_price_uom);
END update_price_uom;
SELECT tce_code
FROM okc_time_code_units_v
WHERE uom_code = l_uom_code
AND quantity = 1;
SELECT tce_code
FROM okc_time_code_units_v
WHERE uom_code = l_uom_code;
SELECT itm.number_of_items,kln.toplvl_uom_code,lin.date_terminated
FROM oks_k_lines_b kln,
okc_k_items itm,
okc_k_lines_b lin
WHERE kln.cle_id = p_line_id
and itm.cle_id = kln.cle_id
and lin.id = p_line_id;
select 'Y'
from okc_k_items itm,
oks_subscr_header_b sub
where itm.cle_id = p_id
and sub.instance_id = itm.object1_id1;
SELECT orig_system_id1
FROM okc_k_lines_b
WHERE ID = p_id;
SELECT start_date,
end_date
FROM okc_k_lines_b
WHERE ID =
(SELECT ool.object_cle_id
FROM okc_operation_lines ool,
okc_operation_instances oie,
okc_class_operations cop
WHERE cop.ID = oie.cop_id
AND cop.opn_code = p_opn_code
AND oie.ID = ool.oie_id
AND ool.subject_cle_id = p_cle_id
AND ROWNUM < 2);
PROCEDURE update_header (
p_chrv_rec IN okc_contract_pub.chrv_rec_type,
x_return_status OUT NOCOPY VARCHAR2
) IS
l_api_name CONSTANT VARCHAR2 (30) := 'update_header';
okc_contract_pub.update_contract_header
(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_count,
x_msg_data => l_msg_data,
p_restricted_update => 'F',
p_chrv_rec => l_chrv_rec,
x_chrv_rec => x_chrv_rec
);
END update_header;
PROCEDURE update_line (
p_clev_rec IN okc_contract_pub.clev_rec_type,
x_return_status OUT NOCOPY VARCHAR2
) IS
l_api_name CONSTANT VARCHAR2 (30) := 'update_line';
okc_contract_pub.update_contract_line (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_count,
x_msg_data => l_msg_data,
p_restricted_update => 'F',
p_clev_rec => l_clev_rec,
x_clev_rec => x_clev_rec
);
END update_line;
PROCEDURE update_amounts (
p_chr_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2
) IS
l_api_name CONSTANT VARCHAR2 (30) := 'update_amounts';
SELECT ID
FROM okc_k_lines_b
WHERE dnz_chr_id = p_chr_id
AND cle_id IS NULL
AND lse_id IN (1, 12, 19);
SELECT SUM (NVL (price_negotiated, 0)) topline_amt
FROM okc_k_lines_b
WHERE dnz_chr_id = p_chr_id
AND cle_id = c_cle_id
AND lse_id IN (7, 8, 9, 10, 11, 18, 25, 35) -- added skekkar
AND date_cancelled IS NULL; -- added skekkar
SELECT SUM (NVL (price_negotiated, 0)) header_amt
FROM okc_k_lines_b
WHERE dnz_chr_id = p_chr_id
AND cle_id IS NULL
AND lse_id IN (1, 12, 19, 46)
AND date_cancelled IS NULL; -- added skekkar
SELECT SUM (NVL (tax_amount, 0)) topline_tax
FROM oks_k_lines_b
WHERE dnz_chr_id = p_chr_id
AND cle_id IN ( SELECT id
FROM okc_k_lines_b
WHERE dnz_chr_id = p_chr_id
AND cle_id = c_cle_id
AND lse_id IN (7, 8, 9, 10, 11, 18, 25, 35)
AND date_cancelled IS NULL
);
SELECT SUM (NVL (tax_amount, 0)) header_tax
FROM oks_k_lines_b
WHERE dnz_chr_id = p_chr_id
AND cle_id IN ( SELECT id
FROM okc_k_lines_b
WHERE dnz_chr_id = p_chr_id
AND cle_id IS NULL
AND lse_id IN (1, 12, 19, 46)
AND date_cancelled IS NULL
);
'106:In Update Amounts: TopLine Id: ' ||
l_line_id
);
'107:In Update Amounts: Total Of Subline Amount: ' ||
l_tot_subline_amt
);
update_line (p_clev_rec => l_clev_rec,
x_return_status => l_return_status);
'108:In Update Amounts After Update Line: Status: ' ||
l_return_status
);
UPDATE oks_k_lines_b
SET tax_amount = l_tot_subline_tax
WHERE cle_id = l_line_id;
update_header (p_chrv_rec => l_chrv_rec,
x_return_status => l_return_status);
'109:In Update Amounts After Update Header: Status: ' ||
l_return_status
);
UPDATE oks_k_headers_b
SET tax_amount = l_tot_topline_tax
WHERE chr_id = p_chr_id;
END update_amounts;
oks_contract_line_pub.update_line (p_api_version => 1.0,
p_init_msg_list => 'F',
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_klnv_rec => l_kln_rec_in,
x_klnv_rec => l_kln_rec_out,
p_validate_yn => 'N'
);
'112:Status AFter update rule in calculate_tax: ' ||
l_return_status
);
'113:Update line details at Calc Tax'
);
PROCEDURE update_price_info (
p_chr_id IN NUMBER,
p_cle_id IN NUMBER,
p_lse_id IN NUMBER,
p_price_type IN VARCHAR2,
p_price_details IN oks_qp_pkg.price_details,
x_return_status OUT NOCOPY VARCHAR2
) IS
l_api_name CONSTANT VARCHAR2 (30)
:= 'update_price_info ';
oks_contract_line_pub.update_line (p_api_version => 1.0,
p_init_msg_list => 'F',
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_klnv_rec => l_kln_rec_in,
x_klnv_rec => l_kln_rec_out,
p_validate_yn => 'N'
);
END update_price_info;
SELECT ID,
osh_id,
dnz_chr_id,
dnz_cle_id,
linked_flag,
seq_no,
om_interface_date,
amount,
start_date,
end_date,
quantity,
uom_code,
order_header_id,
order_line_id,
object_version_number
FROM oks_subscr_elements
WHERE dnz_cle_id = p_line_id;
'124:Before update line in calculate_price: l_clev_rec.id: ' ||
l_clev_rec.ID
);
'125:Before update line in calculate_price: l_clev_rec.price_negotiated: ' ||
l_clev_rec.price_negotiated
);
'126:Before update line in calculate_price: l_clev_rec.price_unit: ' ||
l_clev_rec.price_unit
);
update_line (p_clev_rec => l_clev_rec,
x_return_status => l_return_status);
'127:Status after update line in calculate_price: ' ||
l_return_status
);
update_price_info (p_chr_id => p_chr_id,
p_cle_id => p_line_id,
p_lse_id => p_lse_id,
p_price_type => l_price_type,
p_price_details => l_price_details,
x_return_status => l_return_status
);
oks_subscr_elems_pub.update_row (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_count,
x_msg_data => l_msg_data,
p_scev_rec => l_scev_rec_in,
x_scev_rec => l_scev_rec_out
);
SELECT orig_system_id1
FROM okc_k_headers_all_b
WHERE ID = p_chr_id;
SELECT org_id
FROM okc_k_headers_all_b
WHERE ID = p_hdr_id;
SELECT price_list_id
FROM okc_k_headers_all_b
WHERE ID = p_chr_id;
SELECT ID,
start_date,
end_date,
lse_id,
price_negotiated,
price_unit,
cle_id,
dnz_chr_id
FROM okc_k_lines_b
WHERE dnz_chr_id = p_reprice_rec.contract_id
AND lse_id IN (7, 8, 9, 10, 11, 13, 25, 35, 46);
SELECT usage_type
FROM oks_k_lines_b
WHERE cle_id = p_cle_id;
SELECT ID,
osh_id,
dnz_chr_id,
dnz_cle_id,
linked_flag,
seq_no,
om_interface_date,
amount,
start_date,
end_date,
quantity,
uom_code,
order_header_id,
order_line_id,
object_version_number
FROM oks_subscr_elements
WHERE dnz_cle_id = p_line_id;
SELECT currency_code
FROM okc_k_headers_all_b
WHERE ID = p_hdr_id;
SELECT ID,
start_date,
end_date,
lse_id,
price_negotiated,
price_unit,
cle_id,
dnz_chr_id
FROM okc_k_lines_b
WHERE dnz_chr_id = p_reprice_rec.contract_id
AND lse_id IN (8, 10, 11, 13, 35); -- Bug fix 4769124
update_price_uom (p_chr_id => p_reprice_rec.contract_id,
p_price_uom => l_price_uom_old,
x_return_status => l_return_status);
update_price_uom (
p_chr_id => p_reprice_rec.contract_id,
p_price_uom => l_price_uom_old,
x_return_status => l_return_status);
okc_contract_pub.update_contract_header
(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_count,
x_msg_data => l_msg_data,
p_restricted_update => 'N',
p_chrv_rec => l_chr_rec_in,
x_chrv_rec => l_chr_rec_out
);
p_perf_msg => 'Status After Price List Update: ' ||
l_return_status);
update_line (p_clev_rec => l_clev_rec,
x_return_status => l_return_status);
update_line (p_clev_rec => l_clev_rec,
x_return_status => l_return_status);
update_price_info (p_chr_id => p_reprice_rec.contract_id,
p_cle_id => l_line_rec.ID,
p_lse_id => l_line_rec.lse_id,
p_price_type => p_reprice_rec.price_type,
p_price_details => l_price_details,
x_return_status => l_return_status
);
update_line (p_clev_rec => l_clev_rec,
x_return_status => l_return_status);
update_line (p_clev_rec => l_clev_rec,
x_return_status => l_return_status);
update_price_info (p_chr_id => p_reprice_rec.contract_id,
p_cle_id => l_line_rec.ID,
p_lse_id => l_line_rec.lse_id,
p_price_type => p_reprice_rec.price_type,
p_price_details => l_price_details,
x_return_status => l_return_status
);
oks_subscr_elems_pub.update_row
(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_count,
x_msg_data => l_msg_data,
p_scev_rec => l_scev_rec_in,
x_scev_rec => l_scev_rec_out
);
update_amounts (p_chr_id => p_reprice_rec.contract_id,
x_return_status => l_return_status);
SELECT currency_code,
price_list_id
FROM okc_k_headers_all_b
WHERE ID = p_chr_id;
SELECT renewal_price_list,
'LST' AS renewal_pricing_type, -- Bug 6016714
renewal_markup_percent,
chr_id,
ID
FROM oks_k_headers_b
WHERE chr_id = p_chr_id;
SELECT ID,
start_date,
end_date,
lse_id,
price_negotiated,
price_unit,
cle_id,
dnz_chr_id
FROM okc_k_lines_b
WHERE ID = p_line_id;
SELECT usage_type
FROM oks_k_lines_b
WHERE cle_id = p_cle_id;
SELECT ID,
osh_id,
dnz_chr_id,
dnz_cle_id,
linked_flag,
seq_no,
om_interface_date,
amount,
start_date,
end_date,
quantity,
uom_code,
order_header_id,
order_line_id,
object_version_number
FROM oks_subscr_elements
WHERE dnz_cle_id = p_line_id;
fnd_file.put_line(FND_FILE.LOG,'renewal_pricing_type = LST, Calling okc_contract_pub.update_contract_header');
okc_contract_pub.update_contract_header
(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_count,
x_msg_data => l_msg_data,
p_restricted_update => 'N',
p_chrv_rec => l_chr_rec_in,
x_chrv_rec => l_chr_rec_out
);
fnd_file.put_line(FND_FILE.LOG,'AFTER Calling okc_contract_pub.update_contract_header x_return_status= '||l_return_status);
p_perf_msg => 'UPDATE CONTRACT HDR - RETURN = ' ||
l_return_status);
update_line (p_clev_rec => l_clev_rec,
x_return_status => l_return_status);
fnd_file.put_line(FND_FILE.LOG,'After update_line l_return_status = '||l_return_status);
p_perf_msg => 'UPDATE LINE (MAN) - RETURN = ' ||
l_return_status);
update_price_info
(p_chr_id => l_line_rec.dnz_chr_id,
p_cle_id => l_line_rec.ID,
p_lse_id => l_line_rec.lse_id,
p_price_type => l_srv_k_details_rec.renewal_pricing_type,
p_price_details => l_price_details,
x_return_status => l_return_status
);
p_perf_msg => 'UPDATE PRICE INFO - RETURN = ' ||
l_return_status);
fnd_file.put_line(FND_FILE.LOG,'After update_price_info l_return_status = '||l_return_status);
update_line (p_clev_rec => l_clev_rec,
x_return_status => l_return_status);
fnd_file.put_line(FND_FILE.LOG,'After update_line l_return_status = '||l_return_status);
p_perf_msg => 'UPDATE LINE (PCT) - RETURN = ' ||
l_return_status);
update_line (p_clev_rec => l_clev_rec,
x_return_status => l_return_status);
fnd_file.put_line(FND_FILE.LOG,'AFTER Calling update_line l_return_status = '||l_return_status);
p_perf_msg => 'UPDATE LINE (PCT) WITH NULL PL - RETURN = ' ||
l_return_status);
update_price_info
(p_chr_id => l_line_rec.dnz_chr_id,
p_cle_id => l_line_rec.ID,
p_lse_id => l_line_rec.lse_id,
p_price_type => l_srv_k_details_rec.renewal_pricing_type,
p_price_details => l_price_details,
x_return_status => l_return_status
);
fnd_file.put_line(FND_FILE.LOG,'AFTER Calling update_price_info l_return_status = '||l_return_status);
p_perf_msg => 'UPDATE PRICE INFO (PCT) WITH NULL PL - RETURN = ' ||
l_return_status);
oks_subscr_elems_pub.update_row
(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_count,
x_msg_data => l_msg_data,
p_scev_rec => l_scev_rec_in,
x_scev_rec => l_scev_rec_out
);
fnd_file.put_line(FND_FILE.LOG,'AFTER Calling oks_subscr_elems_pub.update_row l_return_status = '||l_return_status);
p_perf_msg => 'UPDATE SUBSCR ELE - AMOUNT = ' ||
l_return_status);
update_amounts (p_chr_id => p_subject_chr_id,
x_return_status => l_return_status);
p_perf_msg => 'UPDATE CONTRACT AMOUNT = ' ||
l_return_status);