[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select a.LIMIT_DIM_FLAG limit_dim_flag, a.TO_DIM_CODE to_dim_code,
a.TO_LEVEL_ID to_level_id,
a.TO_VALUE to_value, a.TO_VALUE_DESC to_value_desc,
b.limit_dim_level limit_dim_level,
b.limit_dim_level_value limit_dim_level_value
from qpr_transf_headers_b a, qpr_transf_rules_b b
where a.transf_group_id = p_transf_group
and a.to_dim_code='VLB' and a.transf_header_id = b.transf_header_id
and p_ordered_qty between get_number(b.level_value_from)
and nvl(get_number(b.level_value_to),p_ordered_qty);
l_insert_measure number:=0;
select 1 into l_insert_measure
from qpr_dimension_values
where dim_code='PRD'
and hierarchy_code='PRODUCTCATEGORY' and
level2_value = c_vol_band_rec.limit_dim_level_value
and level1_value = to_char(p_inventory_item_id)
and rownum<2;
select 1 into l_insert_measure
from qpr_dimension_values
where dim_code='PRD'
and hierarchy_code='PRODUCTFAMILY' and
level2_value = c_vol_band_rec.limit_dim_level_value
and level1_value = to_char(p_inventory_item_id)
and rownum<2;
if l_insert_measure = 1 then
log_debug('Volume band: '||c_vol_band_rec.to_value);
select a.level_id level_id, a.operator operator,
a.scope_value scope_value, b.level_ppa_code level_ppa_code,
b.level_seq_num level_seq_no, c.hierarchy_ppa_code hierarchy_code,
c.dim_code dim_code
from qpr_scopes a, qpr_hier_levels b, qpr_hierarchies_v c
where b.price_plan_id= qpr_sr_util.g_datamart_tmpl_id
and b.hierarchy_level_id=a.level_id
and b.hierarchy_id = c.hierarchy_id
and a.scope_id= p_scope_id
and c.dim_code in ('ORG', 'PRD', 'CUS', 'GEO', 'REP', 'CHN', 'PSG')
order by c.dim_code, b.level_seq_num;
select 1
from qpr_dimension_values
where dim_code = p_dim_code
and hierarchy_code = p_hierarchy_code
and instance_id = p_instance_id
and level1_value = p_value
and (decode(p_level_seq_num, 1, level1_value,
2, level2_value,
3, level3_value,
4, level4_value,
5, level5_value,null)=p_scope_value)
and rownum < 2;
select distinct p.price_plan_id, s.scope_id , p.name, p.start_date
from qpr_price_plans_vl p, qpr_scopes s
where p.aw_created_flag = 'Y' and p.aw_status_code = 'PROCESS'
--and p.use_for_deal_flag = 'Y'
and p.aw_type_code = 'DATAMART'
and p.instance_id = p_instance_id
and s.parent_entity_type(+) = 'DATAMART'
and s.parent_id(+) = p.price_plan_id
order by p.start_date desc;
select dim_code,dim_ppa_code from qpr_dimensions
where price_plan_id = p_price_plan_id
and dim_ppa_code in ('PRD', 'TIM', 'PSG');
select cube_code into s_cube_code
from qpr_cubes
where cube_ppa_code ='SALES_DATA'
and price_plan_id = p_price_plan_id
and rownum < 2;
SELECT aw_CODE, currency_code into awName, l_aw_curr
FROM QPR_PRICE_PLANS_VL
WHERE price_plan_id = p_price_plan_id
and rownum < 2;
update qpr_pn_request_hdrs_b
set request_status = 'CANCELLED'
where source_id = p_quote_origin
and source_ref_hdr_id = p_quote_header_id
and instance_id = p_instance_id
and request_status in ('ACTIVE', 'CLOSED');
update_request(p_request_header_id, callback_status);
select req.source_id, req.source_ref_hdr_id, req.instance_id
into quote_origin, quote_header_id, instance_id
from qpr_pn_request_hdrs_b req, qpr_pn_response_hdrs res
where req.request_header_id = res.request_header_id
and req.source_ref_hdr_id = nvl(p_quote_header_id, req.source_ref_hdr_id)
and req.source_id = nvl(p_quote_origin, req.source_id)
and req.request_header_id = nvl(p_request_header_id, req.request_header_id)
and res.response_header_id = nvl(p_response_header_id, res.response_header_id);
l_api_call_st := ' begin OE_DEALS_UTIL.update_OM_with_deal'||
l_dblink || '(:1, :2, :3, :4, :5); end;';
select user_name into l_usr_name
from fnd_user
where user_id = fnd_global.user_id;
l_api_call_st:='select resource_id from jtf_rs_resource_extns';
l_api_call_st := 'select user_id from fnd_user_view';
l_api_call_st := 'select application_id, responsibility_id ';
l_api_call_st := 'begin aso_deal_pub.update_quote_from_deal'||
qpr_sr_util.get_dblink(instance_id)||
'(:1, :2, :3, :4, :5, :6); end;';
procedure update_request(p_request_header_id number,
status varchar2)
is
begin
if status = 'CREATED' then
update qpr_pn_request_hdrs_b
set simulation_flag = 'N'
where request_header_id = p_request_header_id;
update qpr_pn_request_hdrs_b
set request_status = 'CLOSED'
where request_header_id = p_request_header_id;
select 'Y' into l_dummy
from qpr_pn_request_hdrs_b req
where req.source_ref_hdr_id = p_quote_header_id
and req.source_id = p_quote_origin
and req.instance_id = p_instance_id
and nvl(req.request_status, 'ACTIVE') <> 'CANCELLED' ;
select 'Y' into l_dummy
from qpr_pn_request_hdrs_b req
where req.source_ref_hdr_id = p_quote_header_id
and req.source_id = p_quote_origin
and req.instance_id = p_instance_id
and nvl(req.simulation_flag, 'Y') = 'N' ;
select count(*) into l_dummy
from qpr_pn_request_hdrs_b req,
qpr_pn_response_hdrs res
where req.request_header_id = res.request_header_id
and req.source_ref_hdr_id = p_quote_header_id
and req.source_id = p_quote_origin
and req.instance_id = p_instance_id
and nvl(req.request_status, 'ACTIVE') <> 'CANCELLED';
select req.source_id, req.source_ref_hdr_id, req.instance_id
into l_source_id, l_source_header_id, l_instance_id
from qpr_pn_request_hdrs_b req,
qpr_pn_response_hdrs res
where req.request_header_id = res.request_header_id
and res.response_header_id = p_response_hdr_id
and nvl(req.request_status, 'ACTIVE') <> 'CANCELLED';
l_api_call_st:='select resource_id from jtf_rs_resource_extns';
select 'READ' into l_ret
from
(
select view_all_deals_flag
from qpr_usr_assignments
where nvl(view_all_deals_flag, 'N') = 'Y'
and role_id in (fnd_global.user_id, fnd_global.resp_id)
order by role_type_code desc)
where rownum < 2;
when 'UPDATE' then
l_ret := 'EDIT';
select req.source_id, req.source_ref_hdr_id, req.instance_id
into l_source_id, l_source_header_id, l_instance_id
from qpr_pn_request_hdrs_b req,
qpr_pn_response_hdrs res
where req.request_header_id = res.request_header_id
and res.response_header_id = p_response_hdr_id
and nvl(req.request_status, 'ACTIVE') <> 'CANCELLED';
l_api_call_st:='select resource_id from jtf_rs_resource_extns';