[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
insert into uwq_temp values(l_name, l_value, l_type,n_ctn,sysdate);
cursor team_cursor is select distinct team_mem.team_id,team_tl.team_name
from jtf_rs_team_members team_mem, jtf_rs_teams_tl team_tl
where team_resource_id = p_resource_id
and team_mem.team_id = team_tl.team_id
and team_tl.language = userenv('LANG');
cursor group_cursor is select distinct group_mem.group_id,group_tl.group_name
from jtf_rs_group_members group_mem, jtf_rs_groups_tl group_tl
where group_mem.resource_id = p_resource_id
and group_mem.group_id = group_tl.group_id
and group_tl.language = userenv('LANG');
cursor all_cursor is select node_label,node_view,cursor_key_col,cursor_sql,
data_source
from cs_sr_uwq_nodes_b uwq_b, cs_sr_uwq_nodes_tl uwq_tl
where uwq_b.node_id = uwq_tl.node_id
and uwq_tl.language = userenv('LANG')
and parent_id = l_node_id
and node_query='CURSOR' and enabled_flag='Y';
cursor node_cursor is select 'Node ',
node_view,where_clause,data_source,level,node_query,cursor_sql,
uwq_b.node_id,res_cat_enum_flag
from cs_sr_uwq_nodes_b uwq_b
where node_id > 9999 and node_query='SINGLE' and enabled_flag='Y'
and ( parent_id is null or parent_id > 9999 )
start with uwq_b.parent_id is null
connect by prior uwq_b.node_id = uwq_b.parent_id;
cursor seed_cursor is select 'Node',
node_view,where_clause,data_source,node_query,cursor_sql,
uwq_b.node_id,res_cat_enum_flag,level,nvl(parent_id,-1)
from cs_sr_uwq_nodes_b uwq_b
where enabled_flag='Y'
--where node_id < 1000 and enabled_flag='Y'
and node_query = 'SINGLE'
start with uwq_b.parent_id is null
connect by prior uwq_b.node_id = uwq_b.parent_id;
cursor cur_seed_cursor is select node_label,
node_view,where_clause,data_source,node_query,cursor_sql,
cursor_key_col,node_id,res_cat_enum_flag
from cs_sr_uwq_nodes_vl
where node_id < 1000 and enabled_flag='Y'
and node_query = 'CURSOR';
select name into l_sr_name from jtf_objects_vl
where object_code='SR';
select node_label into l_node_label from cs_sr_uwq_nodes_tl
where node_id=l_node_id and language=userenv('LANG');
select where_clause into l_parent_where_clause from cs_sr_uwq_nodes_b
where node_id=l_parent_id ;
select node_label,node_view,cursor_key_col,data_source,
node_query,cursor_sql,uwq_b.node_id,res_cat_enum_flag
into l_node_label,l_view_name,l_cursor_key_col,l_data_source,
l_node_query,l_cursor_sql,l_node_id,l_res_cat_enum_flag
from cs_sr_uwq_nodes_b uwq_b, cs_sr_uwq_nodes_tl uwq_tl
where uwq_b.node_id = uwq_tl.node_id
and uwq_tl.language = userenv('LANG')
and parent_id = l_node_id and enabled_flag='Y'
and node_query = 'CURSOR' ;
select node_label into l_node_label from cs_sr_uwq_nodes_tl
where node_id=l_node_id and language=userenv('LANG');
select node_label,node_view,cursor_key_col,data_source,
node_query,cursor_sql,uwq_b.node_id
, res_cat_enum_flag
into l_node_label,l_view_name,l_cursor_key_col,l_data_source,
l_node_query,l_cursor_sql,l_node_id
, l_res_cat_enum_flag
from cs_sr_uwq_nodes_b uwq_b, cs_sr_uwq_nodes_tl uwq_tl
where uwq_b.node_id = uwq_tl.node_id
and uwq_tl.language = userenv('LANG')
and parent_id = l_node_id and enabled_flag='Y'
and node_query = 'CURSOR' ;
s_sql_statement := ' begin select count(1) into :n_count from cs_sr_uwq_emp_count_v where resource_id = :owner_id; end; ';
select count(1) into n_count
from cs_sr_uwq_group_count_v
where resource_id in ( select distinct group_id from jtf_rs_group_members a
where a.resource_id = p_resource_id
and a.resource_id = p_resource_id
and a.resource_id is not null
and nvl(a.delete_flag,'N') <> 'Y')
and resource_type='RS_GROUP'
and (owner_id is null or owner_id <> p_resource_id);
select count(1) into n_count
from cs_sr_uwq_team_count_v
where resource_id in ( select distinct team_id from jtf_rs_team_members a
where a.team_resource_id = p_resource_id
and a.team_resource_id = p_resource_id
and a.team_resource_id is not null
and nvl(a.delete_flag,'N') <> 'Y')
and resource_type='RS_TEAM'
and (owner_id is null or owner_id <> p_resource_id);
s_sql_statement := ' begin select count(1) into :n_count from '||l_node_count_view||' where '||l_node_detail_record.complete_where_clause||' ; end;';
select replace(s_sql_statement, to_char(p_resource_id),':OWNER_ID') into s_unbound_stat from dual;
s_sql_statement := ' begin select count(1) into :n_count from '||l_node_detail_record.view_name||' where '||l_node_detail_record.complete_where_clause||' ; end;';
procedure insert_row(
p_node_id in number,
p_node_view in varchar2,
p_node_label in varchar2,
p_data_source in varchar2,
p_media_type_id in number,
p_where_clause in varchar2,
p_res_cat_enum_flag in varchar2,
p_node_type in varchar2,
p_hide_if_empty in varchar2,
p_node_depth in number,
p_parent_id in number,
p_node_query in varchar2,
p_cursor_sql in varchar2,
p_cursor_key_col in varchar2,
p_enabled_flag in varchar2,
p_creation_date in date,
p_created_by in number,
p_last_update_date in date,
p_last_updated_by in number,
p_last_update_login in number,
x_node_id out NOCOPY number,
x_return_status out NOCOPY varchar2) is
l_node_id number;
select cs_sr_uwq_nodes_s.nextval into l_node_id from dual;
insert into cs_sr_uwq_nodes_b
(node_id,
node_view,
data_source,
media_type_id,
where_clause,
res_cat_enum_flag,
node_type,
hide_if_empty,
node_depth,
parent_id,
node_query,
cursor_sql,
cursor_key_col,
enabled_flag,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
object_version_number)
values
(l_node_id,
p_node_view,
p_data_source,
p_media_type_id,
p_where_clause,
p_res_cat_enum_flag,
p_node_type,
p_hide_if_empty,
p_node_depth,
p_parent_id,
p_node_query,
p_cursor_sql,
p_cursor_key_col,
p_enabled_flag,
p_creation_date,
p_created_by,
p_last_update_date,
p_last_updated_by,
p_last_update_login,
1);
insert into cs_sr_uwq_nodes_tl
(node_id,
node_label,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
language,
source_lang)
select
l_node_id,
p_node_label,
p_creation_date,
p_created_by,
p_last_update_date,
p_last_updated_by,
p_last_update_login,
l.language_code,
userenv('LANG')
from fnd_languages l
where l.installed_flag in ('I','B');
end insert_row;
procedure update_row(
p_node_id in number,
p_object_version_number in number,
p_node_view in varchar2,
p_node_label in varchar2,
p_data_source in varchar2,
p_media_type_id in number,
p_where_clause in varchar2,
p_res_cat_enum_flag in varchar2,
p_node_type in varchar2,
p_hide_if_empty in varchar2,
p_node_depth in number,
p_parent_id in number,
p_node_query in varchar2,
p_cursor_sql in varchar2,
p_cursor_key_col in varchar2,
p_enabled_flag in varchar2,
p_creation_date in date,
p_created_by in number,
p_last_update_date in date,
p_last_updated_by in number,
p_last_update_login in number,
x_return_status out NOCOPY varchar2) is
l_object_version_number number :=0 ;
select object_version_number into l_object_version_number
from cs_sr_uwq_nodes_b where node_id = p_node_id;
update cs_sr_uwq_nodes_b set
node_view = p_node_view,
data_source = p_data_source,
media_type_id = p_media_type_id,
where_clause = p_where_clause,
res_cat_enum_flag = p_res_cat_enum_flag,
node_type = p_node_type,
hide_if_empty = p_hide_if_empty,
node_depth = p_node_depth,
parent_id = p_parent_id,
node_query = p_node_query,
cursor_sql = p_cursor_sql,
cursor_key_col = p_cursor_key_col,
enabled_flag = p_enabled_flag,
creation_date = p_creation_date,
created_by = p_created_by,
last_update_date = p_last_update_date,
last_updated_by = p_last_updated_by,
last_update_login = p_last_update_login,
object_version_number = p_object_version_number + 1
where node_id = p_node_id;
update cs_sr_uwq_nodes_tl set
node_label = p_node_label,
creation_date = p_creation_date,
created_by = p_created_by,
last_update_date = p_last_update_date,
last_updated_by = p_last_updated_by,
last_update_login = p_last_update_login
where node_id = p_node_id
and userenv('LANG') in (language, source_lang);
end update_row;
cursor inc_cursor is select incident_id from
cs_incidents_all_b where incident_number = p_parameter_value;
select inc.incident_id,inc.incident_number
from oe_order_headers_all oe,
cs_estimate_details chg,
cs_incidents_all_b inc
where oe.order_number = p_parameter_value
and oe.order_category_code in ('RETURN','MIXED')
and oe.header_id = chg.order_header_id
and chg.incident_id = inc.incident_id;
select item.instance_id, item.owner_party_id,
hzp.party_type
from csi_item_instances item, hz_parties hzp
where item.external_reference = p_parameter_value
and hzp.party_id = item.owner_party_id;
select item.instance_id, item.owner_party_id,
hzp.party_type
from csi_item_instances item, hz_parties hzp
where item.serial_number = p_parameter_value
and hzp.party_id = item.owner_party_id;
select oks.contract_id, oks.party_id,hzp.party_type
from oks_ent_hdr_summary_v oks, hz_parties hzp
where oks.contract_number = p_parameter_value
and oks.party_id = hzp.party_id
and oks.start_date_active <= sysdate
order by oks.start_date_active DESC;
select acc.cust_account_id,acc.party_id,
party.party_type
from hz_cust_accounts acc, hz_parties party
where acc.account_number = p_parameter_value
and acc.status = 'A'
and party.party_id = acc.party_id;
select party.party_id,party.party_id,
party.party_type
from hz_parties party
where party_number = p_parameter_value;
select cont.contact_point_id,cont.phone,
party.party_type
from cs_sr_hz_cust_cont_v party, cs_sr_hz_cont_pts_p_phones_v cont
where cont.transposed_phone_number = v_transposed_phone_number
and cont.owner_table_id = party.party_id
and cont.phone is not null;
select hzc.owner_table_id,hzp.party_number,hzp.party_type
from hz_contact_points hzc, hz_parties hzp
where hzc.transposed_phone_number = v_transposed_phone_number
and hzc.owner_table_id = hzp.party_id
and hzc.owner_table_name = 'HZ_PARTIES';
v_sql_statement := ' begin select reverse(to_char('||p_parameter_value||')) into :v_transposed_phone_number from dual; end; ';
select acc.account_number
from hz_cust_accounts acc
where acc.cust_account_id = p_cust_account_id
and acc.status = 'A';
select party_number
from hz_parties
where party_id = p_cust_id;
cursor sec_value is select sr_agent_security
from cs_system_options where rownum = 1;
cursor sr_cursor is select incident_id from
cs_incidents_all_b where incident_number = p_ivr_data_value;
cursor sr_type_sec_chk is select 'Y' from
cs_sr_type_mapping where incident_type_id = n_incident_id
and responsibility_id= n_resp_id
and sysdate between
nvl(start_date,sysdate) and nvl(end_date,sysdate);
SELECT user_id
INTO n_user_id
FROM jtf_rs_resource_extns
WHERE resource_id = n_agent_id;
Select Inc.Incident_number
from Jtf_tasks_b Tsk, CS_incidents_all_b Inc
where Tsk.Task_id=l_Task_id
and Inc.Incident_Id=Tsk.source_object_id;