[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select h.contract_number,
h.contract_number_modifier,
h.description,
h.estimated_amount,
h.estimated_amount_renewed,
h.currency_code,
h.authoring_org_id,
h.orig_system_source_code,
h.orig_system_id1,
rel.object1_id1 lead_id
from okc_k_headers_v h,
okc_k_rel_objs rel
where h.id = p_contract_id
and rel.chr_id(+) = h.orig_system_id1
and rel.rty_code(+) = 'OPPEXPSCONTRACT'
and rel.jtot_object1_code(+) = 'OKX_OPPHEAD';
select object1_id1
from okc_k_party_roles_b
where dnz_chr_id = p_contract_id
and cle_id is null
and rle_code = p_rle_code;
select resource_id
from jtf_rs_salesreps
where salesrep_id in (select object1_id1
from okc_contacts
where dnz_chr_id = p_contract_id
and jtot_object1_code = p_object_code);
select sales_group_id
from as_fc_salesforce_v
where salesforce_id = (select resource_id
from jtf_rs_salesreps
where salesrep_id = g_sales_rep); */
select rule_information1,
rule_information2,
rule_information3,
rule_information4,
rule_information5,
jtot_object1_code,
object1_id1
from okc_rules_b
where dnz_chr_id = p_contract_id
and rule_information_category = p_rule_information_category;
SELECT party_site_id
FROM okx_cust_site_uses_v
WHERE id1 = b_id1
AND site_use_code = p_use_code -- ship..to_party_site_id
AND status = 'A' -- Active Status
AND nvl(ORG_ID,-99) = SYS_CONTEXT('OKC_CONTEXT', 'ORG_ID');
select sales_stage_id
---name, min_win_probability, max_win_probability,
---min_win_probability || ' - ' || max_win_probability probability_range,
---description
from OKX_OPP_SALES_STAGES_V ---as_sales_stages_all_vl
where enabled_flag = 'Y' and
( ( (sysdate > start_date_active) and (end_date_active is null) ) or
(sysdate between start_date_active and end_date_active) )
and b_win_probability between min_win_probability and max_win_probability;
p_last_update_date => SYSDATE,
p_last_updated_by => FND_GLOBAL.USER_ID,
p_creation_date => SYSDATE,
p_created_by => FND_GLOBAL.USER_ID,
p_last_update_login => FND_GLOBAL.LOGIN_ID
);
select SUM(DECODE(p_context, 'RENEW', cle.price_negotiated_renewed, cle.price_negotiated))
from okc_k_lines_b cle
where level = 1
start with cle.id in (select cle2.id
from okc_k_lines_b cle2,
okc_k_items itm,
okc_statuses_b sts,
jtf_object_usages jou
where cle2.dnz_chr_id = p_contract_id
and cle2.date_renewed is null
and cle2.sts_code = sts.code
and itm.cle_id = cle.id
-- and itm.jtot_object1_code = 'OKX_LICPROD'
and itm.jtot_object1_code = jou.object_code
and jou.object_user_code = 'OKX_MTL_SYSTEM_ITEM'
and sts.ste_code <> 'TERMINATED'
and ((p_context = 'EXPIRE'
and sts.ste_code in ('ACTIVE', 'SIGNED'))
or (p_context in ('AUTHORING', 'RENEW')
and sts.ste_code = 'ENTERED'))
and not exists (select 'x'
from okc_k_rel_objs rel
where (rel.cle_id = cle2.id
or rel.cle_id = cle2.cle_id)
and rel.rty_code = g_rty_code))
connect by prior cle.id = cle.cle_id;
select cle.id,
cle.cle_id,
cle.price_unit,
cle.price_negotiated,
cle.currency_code,
cle.orig_system_source_code,
cle.orig_system_id1,
itm.object1_id1,
itm.object1_id2,
itm.uom_code,
itm.number_of_items
from okc_k_lines_b cle,
okc_k_items itm,
okc_statuses_b sts,
jtf_object_usages jou
where cle.chr_id = p_contract_id
and cle.date_renewed is null
and cle.sts_code = sts.code
and itm.cle_id = cle.id
-- and itm.jtot_object1_code = 'OKX_LICPROD'
and itm.jtot_object1_code = jou.object_code
and jou.object_user_code = 'OKX_MTL_SYSTEM_ITEM'
and sts.ste_code <> 'TERMINATED'
and ((p_context = 'EXPIRE' and sts.ste_code in ('ACTIVE', 'SIGNED'))
or (p_context in ('AUTHORING', 'RENEW') and sts.ste_code = 'ENTERED'))
and not exists (select 'x' from okc_k_rel_objs rel
where (rel.cle_id = cle.id
or rel.cle_id = cle.cle_id)
and rel.rty_code = g_rty_code)
;
select cle.id,
cle.cle_id,
cle.price_unit,
cle.price_negotiated,
cle.currency_code
from okc_k_lines_b cle
where level = 1
start with cle.id in (select cle2.id
from okc_k_lines_b cle2,
okc_k_items itm,
okc_statuses_b sts,
jtf_object_usages jou
where cle2.dnz_chr_id = p_contract_id
and cle2.date_renewed is null
and cle2.sts_code = sts.code
and itm.cle_id = cle.id
-- and itm.jtot_object1_code = 'OKX_LICPROD'
and itm.jtot_object1_code = jou.object_code
and jou.object_user_code = 'OKX_MTL_SYSTEM_ITEM'
and sts.ste_code <> 'TERMINATED'
and ((p_context = 'EXPIRE'
and sts.ste_code in ('ACTIVE', 'SIGNED'))
or (p_context in ('AUTHORING', 'RENEW')
and sts.ste_code = 'ENTERED'))
and not exists (select 'x'
from okc_k_rel_objs rel
where (rel.cle_id = cle2.id
or rel.cle_id = cle2.cle_id)
and rel.rty_code = g_rty_code))
connect by prior cle.id = cle.cle_id;
select object1_id1, object1_id2
from okc_k_rel_objs rel
where rel.cle_id = p_cle_id
and rel.rty_code = 'OPPEXPSCONTRACT';
select itm.object1_id1,
itm.object1_id2,
itm.uom_code,
itm.number_of_items
from okc_k_items itm,
jtf_object_usages jou
where itm.cle_id = p_cle_id
-- and itm.jtot_object1_code = 'OKX_LICPROD'
-- and itm.jtot_object1_code = 'OKX_MTL_SYSTEM_ITEMS'
and itm.jtot_object1_code = jou.object_code
and jou.object_user_code = 'OKX_MTL_SYSTEM_ITEM'
and rownum = 1; */
SELECT interest_type_id,
primary_interest_code_id,
secondary_interest_code_id
-- FROM AST_INV_ITEM_LOV_V
FROM (
SELECT
mic.organization_id,
mic.inventory_item_id,
mc.segment1 interest_type_id,
mc.segment2 primary_interest_code_id,
mc.segment3 secondary_interest_code_id
FROM fnd_id_flex_structures fifs,
mtl_item_categories mic,
MTL_CATEGORIES_B MC
WHERE fifs.id_flex_code = 'MCAT' AND fifs.application_id = 401
AND fifs.id_flex_structure_code = 'SALES_CATEGORIES'
AND mc.structure_id = fifs.id_flex_num
and mc.SEGMENT1 < 'A' AND mic.category_set_id = 5
AND mic.category_id = mc.category_id
)
WHERE organization_id = p_organization_id
and inventory_item_id=p_inv_item_id;
AS_OPPORTUNITY_PUB.Update_Opp_Header
( p_api_version_number => 2.0,
p_init_msg_list => p_init_msg_list,
p_commit => fnd_api.g_false,
p_validation_level => fnd_api.g_valid_level_full,
p_header_rec => l_header_rec,
p_check_access_flag => 'Y',
p_admin_flag => 'N',
p_admin_group_id => Null,
p_identity_salesforce_id => g_sales_rep,
p_partner_cont_party_id => Null,
p_profile_tbl => as_utility_pub.g_miss_profile_tbl,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
x_lead_id => l_lead_id );
okc_debug.log('5060: There are not lines to be inserted into Oppurtunity');
okc_debug.log('5063: Before calling as_opportunity_pub.Delete_Opp_Header');
AS_OPPORTUNITY_PUB.Delete_Opp_Header(
p_api_version_number => 2.0, --p_api_version,--2.0,
p_init_msg_list => p_init_msg_list, --fnd_api.g_false,
p_commit => fnd_api.g_false,
p_validation_level => fnd_api.g_valid_level_full,
p_header_rec => l_header_rec,
p_check_access_flag => 'Y',
p_admin_flag => 'N',
p_admin_group_id => Null,
p_identity_salesforce_id => g_sales_rep,
p_partner_cont_party_id => Null,
p_profile_tbl => as_utility_pub.g_miss_profile_tbl,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
x_lead_id => l_lead_id
);
okc_debug.log('5068: After calling as_opportunity_pub.Delete_Opp_Header');
okc_debug.log('5100: No lines selected for opp creation');
select chrb.buy_or_sell,
chrb.template_yn,
scs.create_opp_yn,
sts.ste_code
from okc_k_headers_b chrb,
okc_subclasses_b scs,
okc_statuses_b sts
where chrb.id = p_contract_id
and scs.code = chrb.scs_code
and sts.code = chrb.sts_code;
select 'x'
from okc_k_party_roles_b
where dnz_chr_id = p_contract_id
and cle_id is null
and rle_code = p_rle_code;
select 'x'
from okc_contacts
where dnz_chr_id = p_contract_id
and jtot_object1_code = p_object_code
and object1_id1 is not null; --bug 2071104
select 'x'
from okc_k_items itm, jtf_object_usages jou
where itm.dnz_chr_id = p_contract_id
and itm.jtot_object1_code = jou.object_code
and jou.object_user_code = 'OKX_MTL_SYSTEM_ITEM';
select 'x'
from okc_k_items
where dnz_chr_id = p_contract_id
-- and jtot_object1_code = 'OKX_LICPROD';
select 'x'
from okc_k_lines_b cle,
okc_statuses_b sts
-- where cle.dnz_chr_id = p_contract_id
where cle.chr_id = p_contract_id
and cle.sts_code = sts.code
and sts.ste_code in ('ACTIVE', 'SIGNED');
select 'x'
from okc_k_lines_b cle
-- where cle.dnz_chr_id = p_contract_id
where cle.chr_id = p_contract_id
and cle.date_renewed is null
and not exists (select 'x'
from okc_k_rel_objs rel
where (rel.cle_id = cle.id
or rel.cle_id = cle.cle_id)
and rel.rty_code = g_rty_code);
select 'x'
from okc_rules_b
where dnz_chr_id = p_contract_id
and rule_information_category = p_rule
and rule_information1 = p_renewal_type;
select rule_information1,
rule_information2,
rule_information3,
rule_information4,
rule_information5
from okc_rules_b
where dnz_chr_id = p_contract_id
and rule_information_category = p_rule_information_category;