[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select *
from okl_k_lines_full_v line
where line.dnz_chr_id = p_chr_id and
--Bug# 6374869: subquesry was fetching multiple records as 'USAGE' line
--exists for OKS as well as OKL
line.lse_id = 56
--line.lse_id = (
-- select id
-- from okc_line_styles_b
-- where lty_code = 'USAGE'
-- )
-- added to handle abandon line
and not exists (
select 'Y'
from okc_statuses_b okcsts
where okcsts.code = line.sts_code
and okcsts.ste_code in ('EXPIRED','HOLD','CANCELLED','TERMINATED'));
SELECT id,
name ASSET_NUMBER,
line_number
FROM OKL_K_LINES_FULL_V line
WHERE line.dnz_chr_id = p_chr_id
AND line.cle_id = p_usage_line_id
-- added to handle abandon line
AND NOT EXISTS (
SELECT 'Y'
FROM okc_statuses_v okcsts
WHERE okcsts.code = line.sts_code
AND okcsts.ste_code IN ('EXPIRED','HOLD','CANCELLED','TERMINATED'));
SELECT ib_line.id,
ib_line.line_number
FROM okl_k_lines_full_v ib_line,
okl_k_lines_full_v inst_line,
okl_k_lines_full_v top_line
WHERE ib_line.cle_id = inst_line.id
AND inst_line.cle_id = top_line.id
AND ib_line.lse_id = (select id from okc_line_styles_v where lty_code = 'INST_ITEM')
AND inst_line.lse_id = (select id from okc_line_styles_v where lty_code = 'FREE_FORM2')
AND top_line.lse_id = (select id from okc_line_styles_v where lty_code = 'FREE_FORM1')
AND top_line.id = p_top_line_id
AND top_line.dnz_chr_id = p_chr_id;
SELECT cc.counter_id,
cc.uom_code
FROM cs_counter_groups csg,
cs_counters cc,
okc_k_items cim,
okc_k_lines_b cle,
okc_line_styles_b lse
WHERE TO_CHAR(csg.source_object_id) = cim.object1_id1
AND cim.cle_id = cle.id
AND cle.lse_id = lse.id
AND csg.counter_group_id = cc.counter_group_id
AND lse.lty_code = 'INST_ITEM'
AND cc.usage_item_id = p_usage_item_id
AND cle.dnz_chr_id = p_chr_id
AND cle.id = p_ib_line_id;
SELECT
rule.ID,
rule.OBJECT_VERSION_NUMBER,
rule.SFWT_FLAG,
rule.OBJECT1_ID1,
rule.OBJECT2_ID1,
rule.OBJECT3_ID1,
rule.OBJECT1_ID2,
rule.OBJECT2_ID2,
rule.OBJECT3_ID2,
rule.JTOT_OBJECT1_CODE,
rule.JTOT_OBJECT2_CODE,
rule.JTOT_OBJECT3_CODE,
rule.DNZ_CHR_ID,
rule.RGP_ID,
rule.PRIORITY,
rule.STD_TEMPLATE_YN,
rule.COMMENTS,
rule.WARN_YN,
rule.ATTRIBUTE_CATEGORY,
rule.ATTRIBUTE1,
rule.ATTRIBUTE2,
rule.ATTRIBUTE3,
rule.ATTRIBUTE4,
rule.ATTRIBUTE5,
rule.ATTRIBUTE6,
rule.ATTRIBUTE7,
rule.ATTRIBUTE8,
rule.ATTRIBUTE9,
rule.ATTRIBUTE10,
rule.ATTRIBUTE11,
rule.ATTRIBUTE12,
rule.ATTRIBUTE13,
rule.ATTRIBUTE14,
rule.ATTRIBUTE15,
rule.CREATED_BY,
rule.CREATION_DATE,
rule.LAST_UPDATED_BY,
rule.LAST_UPDATE_DATE,
rule.LAST_UPDATE_LOGIN,
rule.RULE_INFORMATION_CATEGORY,
rule.RULE_INFORMATION1,
rule.RULE_INFORMATION2,
rule.RULE_INFORMATION3,
rule.RULE_INFORMATION4,
rule.RULE_INFORMATION5,
rule.RULE_INFORMATION6,
rule.RULE_INFORMATION7,
rule.RULE_INFORMATION8,
rule.RULE_INFORMATION9,
rule.RULE_INFORMATION10,
rule.RULE_INFORMATION11,
rule.RULE_INFORMATION12,
rule.RULE_INFORMATION13,
rule.RULE_INFORMATION14,
rule.RULE_INFORMATION15,
template_yn,
ans_set_jtot_object_code,
ans_set_jtot_object_id1,
ans_set_jtot_object_id2,
display_sequence
FROM okc_rules_v rule,
okc_rule_groups_v grp
WHERE rule_information_category = p_rule_info_catg --'CAN'
AND jtot_object1_code = p_jtot_code --'OKX_CUSTACCT'
AND grp.rgd_code = p_rgd_code --'LACAN'
AND (grp.dnz_chr_id = NVL(p_chr_id, G_INIT_NUMBER)
OR
grp.cle_id = NVL(p_cle_id, G_INIT_NUMBER)
)
AND rule.rgp_id = grp.id;
l_rulv_rec.LAST_UPDATED_BY,
l_rulv_rec.LAST_UPDATE_DATE,
l_rulv_rec.LAST_UPDATE_LOGIN,
l_rulv_rec.RULE_INFORMATION_CATEGORY,
l_rulv_rec.RULE_INFORMATION1,
l_rulv_rec.RULE_INFORMATION2,
l_rulv_rec.RULE_INFORMATION3,
l_rulv_rec.RULE_INFORMATION4,
l_rulv_rec.RULE_INFORMATION5,
l_rulv_rec.RULE_INFORMATION6,
l_rulv_rec.RULE_INFORMATION7,
l_rulv_rec.RULE_INFORMATION8,
l_rulv_rec.RULE_INFORMATION9,
l_rulv_rec.RULE_INFORMATION10,
l_rulv_rec.RULE_INFORMATION11,
l_rulv_rec.RULE_INFORMATION12,
l_rulv_rec.RULE_INFORMATION13,
l_rulv_rec.RULE_INFORMATION14,
l_rulv_rec.RULE_INFORMATION15,
l_rulv_rec.TEMPLATE_YN,
l_rulv_rec.ANS_SET_JTOT_OBJECT_CODE,
l_rulv_rec.ANS_SET_JTOT_OBJECT_ID1,
l_rulv_rec.ANS_SET_JTOT_OBJECT_ID2,
l_rulv_rec.DISPLAY_SEQUENCE;
SELECT object1_id1
FROM okc_k_party_roles_b
WHERE rle_code = p_rle_code
AND dnz_chr_id = p_chr_id
AND dnz_chr_id = chr_id
AND jtot_object1_code = p_jtot_object1_code;
SELECT id,
inv_organization_id,
sts_code,
qcl_id,
scs_code,
contract_number,
currency_code,
cust_po_number,
short_description,
start_date,
end_date,
term_duration,
authoring_org_id
FROM okl_k_headers_full_v
WHERE id = p_chr_id;
SELECT primary_uom_code
FROM mtl_system_items
WHERE inventory_item_id = p_item_id
AND organization_id = p_inv_org_id;
SELECT cust_acct_id
FROM okc_k_headers_b
WHERE id = p_chr_id;
SELECT bill_to_site_use_id
FROM okc_k_headers_b
WHERE id = p_chr_id;
SELECT rule_id
FROM ra_rules
WHERE name = p_name;
SELECT id
FROM okc_qa_check_lists_v
WHERE name = p_name;
SELECT id
FROM okc_process_defs_b
WHERE wf_name = p_name;
SELECT start_date,
end_date,
authoring_org_id
FROM okc_k_headers_v
WHERE id = p_chr_id;
SELECT rule_id
FROM ra_rules
WHERE name = p_name;
SELECT oki.object1_id1
FROM okl_k_lines_full_v oklf,
okc_k_items oki
WHERE oklf.id = p_link_asset_line_id
AND oklf.dnz_chr_id = p_chr_id
AND oklf.id = oki.cle_id;
procedure update_counter_instance (
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_okl_usage_line_id IN OKC_K_LINES_V.ID%TYPE,
p_oks_usage_line_id IN OKC_K_LINES_V.ID%TYPE) is
l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_COUNTER_INSTANCE';
select ct.*
--Bug# 6374869
from --CS_COUNTERS ct,
CSI_COUNTERS_B ct,
OKC_K_ITEMS ct_item,
OKC_K_LINES_B ct_line
where ct.counter_id = to_number(ct_item.object1_id1)
and ct_item.cle_id = ct_line.id
and ct_item.dnz_chr_id = ct_line.dnz_chr_id
and ct_line.cle_id = p_oks_top_line_id;
Select to_number(rul.object1_id1) usage_item_id
,to_number(rul.object1_id2) usage_item_inv_org_id
,to_number(rul.object2_id1) price_list_id
,rul.rule_information1 Miminum_Quantity
,rul.rule_information2 Default_Quantity
,rul.rule_information3 Avg_monthly_Counter_Value
,rul.rule_information4 ct_Level
,rul.rule_information5 Base_Reading
,rul.object3_id1 base_reading_uom
From OKC_RULES_B rul,
OKC_RULE_GROUPS_B rgp,
OKC_K_LINES_B usage_cle
Where rul.rgp_id = rgp.id
and rul.rule_information_category = 'LAUSBB'
and rul.dnz_chr_id = rgp.dnz_chr_id
and rgp.dnz_chr_id = usage_cle.dnz_chr_id
and rgp.cle_id = usage_cle.id
and usage_cle.id = p_okl_top_line_id;
/*--CS_COUNTERS_PUB.UPDATE_COUNTER
--(p_api_version => 1.0,
--p_init_msg_list => OKL_API.G_FALSE,
--p_commit => OKL_API.G_FALSE,
--x_return_status => x_return_status,
--x_msg_count => x_msg_count,
--x_msg_data => x_msg_data,
--p_ctr_id => l_ctr_rec.counter_id,
--p_object_version_number => l_ctr_rec.object_version_number,
--p_ctr_rec => l_csi_ctr_rec,
--p_cascade_upd_to_instances => OKL_API.G_FALSE,
--x_object_version_number => l_object_version_number);
CSI_COUNTER_PUB.update_counter(
p_api_version => 1.0
,p_init_msg_list => OKL_API.G_FALSE
,p_commit => OKL_API.G_FALSE
,p_validation_level => fnd_api.g_valid_level_full
,p_counter_instance_rec => l_counter_instance_rec
,P_ctr_properties_tbl => l_ctr_properties_tbl
,P_counter_relationships_tbl => l_counter_relationships_tbl
,P_ctr_derived_filters_tbl => l_ctr_derived_filters_tbl
,P_counter_associations_tbl => l_counter_associations_tbl
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
End Update_Counter_Instance;
SELECT 1
FROM okc_class_operations
WHERE cls_code = 'SERVICE'
AND opn_code = 'CHECK_RULE';
update_counter_instance(
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_okl_usage_line_id => usage_rec.id,
p_oks_usage_line_id => x_oks_usage_line_id);
SELECT id
FROM okc_k_items_v
WHERE cle_id = p_cle_id
AND dnz_chr_id = p_okl_header_id;
OKC_CONTRACT_ITEM_PUB.update_contract_item(
p_api_version => 1.0,
p_init_msg_list => OKC_API.G_FALSE,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_cimv_rec => l_cimv_rec,
x_cimv_rec => x_cimv_rec
);