[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select ost.organization_structure_id
from per_organization_structures ost
where ost.organization_structure_id = cp_str_id
and ost.business_group_id = cp_bus_id;
select spr.view_all_organizations_flag
, spr.organization_structure_id
from per_security_profiles spr
, per_sec_profile_assignments spa
where spr.security_profile_id = spa.security_profile_id
and spa.user_id = cp_user_id
and spa.responsibility_id = cp_resp_id
and spa.responsibility_application_id = cp_appl_id
and spa.security_group_id = cp_secg_id;
select view_all_organizations_flag
,organization_structure_id
from per_security_profiles
where security_profile_id = cp_security_profile_id;
select ost.organization_structure_id
from per_organization_structures ost
where ost.business_group_id = cp_bus_id
and ost.primary_structure_flag = 'Y';
select value
from per_assignment_budget_values_f
where assignment_id = p_assignment_id
and unit = p_budget_metric
and p_session_date between effective_start_date and effective_end_date;
select sum(ev.screen_entry_value)
into l_hours_worked
from pay_element_entry_values_f ev,
pay_element_entries_f ee
where ev.element_entry_id = ee.element_entry_id and
ee.assignment_id = p_assign_id and
(ev.input_value_id,ee.element_link_id) in
( select iv.input_value_id,el.element_link_id
from pay_element_types_f et,
pay_input_values_f iv,
pay_element_links_f el
where iv.name = 'Hours' and
et.element_name = 'Overtime' and
iv.element_type_id = et.element_type_id and
et.element_type_id = el.element_type_id and
p_earned_date between et.effective_start_date and et.effective_end_date and
p_earned_date between iv.effective_start_date and iv.effective_end_date and
p_earned_date between el.effective_start_date and el.effective_end_date
) and
p_earned_date between ee.effective_start_date and ee.effective_end_date and
exists (select null
from pay_element_entry_values_f ev2,
pay_element_entries_f ee2
where ev2.element_entry_id = ee2.element_entry_id and
ee2.assignment_id = p_assign_id and
ev2.screen_entry_value = to_char(p_multiple) and
ev2.element_entry_id = ev.element_entry_id and
(ev2.input_value_id,ee2.element_link_id) in
( select iv2.input_value_id,el2.element_link_id
from pay_element_types_f et2,
pay_input_values_f iv2,
pay_element_links_f el2
where iv2.element_type_id = et2.element_type_id and
et2.element_type_id = el2.element_type_id and
iv2.name = 'Multiple' and
et2.element_name = 'Overtime' and
p_earned_date between et2.effective_start_date and et2.effective_end_date and
p_earned_date between iv2.effective_start_date and iv2.effective_end_date and
p_earned_date between el2.effective_start_date and el2.effective_end_date
) and
p_earned_date between ee2.effective_start_date and ee2.effective_end_date
);
select ast.per_system_status status
, asg.effective_start_date
, asg.effective_end_date
, asg.organization_id
from per_assignment_status_types ast
, per_all_assignments_f asg
where asg.assignment_status_type_id = ast.assignment_status_type_id
and asg.assignment_id = p_assignment_id
and asg.effective_start_date <= p_period_end_date
order by asg.effective_start_date desc;
select per.start_date
from per_all_people_f per
, per_all_assignments_f asg
where asg.person_id = per.person_id
and p_period_end_date between per.effective_start_date and per.effective_end_date
and p_period_end_date between asg.effective_start_date and asg.effective_end_date
and asg.assignment_id = p_assignment_id;
select pos.actual_termination_date
, pos.leaving_reason
, decode( pos.actual_termination_date,
null, 'Not Terminated',
decode( floor(
months_between( pos.actual_termination_date, pos.date_start ) / 12 ),
0, '<1 Year',
1, '1-3 Years',
2, '1-3 Years',
3, '3-5 Years',
4, '3-5 Years',
'5 Years+')) service_band
from per_periods_of_service pos
, per_all_assignments_f asg
where asg.period_of_service_id = pos.period_of_service_id
and p_period_start_date-1 between asg.effective_start_date and asg.effective_end_date
and asg.assignment_id = p_assignment_id;
select org_param_id
from hri_org_params
where organization_id = cp_organization_id
and organization_process = 'SINR';
select meaning
from hr_lookups
where lookup_type = 'LEAV_REAS'
and lookup_code = p_leaving_reason;
OrgTable.Delete;
OrgIndex.Delete;
select ose.organization_id_parent
from per_org_structure_elements ose
where ose.org_structure_element_id = GetOrgStructElement;
select TREE.organization_id_start
from hr_organization_units org
, (select ele.organization_id_parent organization_id_start
from per_org_structure_elements ele
where cp_organization_process in ('ISNR', 'ISRO')
connect by prior ele.organization_id_child = ele.organization_id_parent
and ele.org_structure_version_id = cp_org_structure_version_id
start with ele.organization_id_parent = cp_organization_id
and ele.org_structure_version_id = cp_org_structure_version_id) TREE
where TREE.organization_id_start = org.organization_id
UNION
select TREE.organization_id_start
from hr_organization_units org
, (select ele.organization_id_child organization_id_start
from per_org_structure_elements ele
where cp_organization_process in ('ISNR', 'ISRO')
connect by prior ele.organization_id_child = ele.organization_id_parent
and ele.org_structure_version_id = cp_org_structure_version_id
start with ele.organization_id_parent = cp_organization_id
and ele.org_structure_version_id = cp_org_structure_version_id) TREE
where TREE.organization_id_start = org.organization_id
UNION
select org.organization_id organization_id_start
from hr_organization_units org
where org.organization_id = cp_organization_id
order by 1;
select TREE.organization_id_group
, TREE.organization_id_child
from hr_organization_units org
, (select cp_organization_id_start organization_id_group
, ele.organization_id_child organization_id_child
from per_org_structure_elements ele
where cp_organization_process in ('SIRO', 'ISRO')
connect by prior ele.organization_id_child = ele.organization_id_parent
and ele.org_structure_version_id = cp_org_structure_version_id
start with ele.organization_id_parent = cp_organization_id_start
and ele.org_structure_version_id = cp_org_structure_version_id) TREE
where TREE.organization_id_child = org.organization_id
UNION
select org.organization_id organization_id_group
, org.organization_id organization_id_child
from hr_organization_units org
where org.organization_id = cp_organization_id_start
order by 1,2;
OrgTable.Delete;
OrgIndex.Delete;
select ose.org_structure_element_id
from per_organization_structures ost
, per_org_structure_versions osv
, per_org_structure_elements ose
where ost.business_group_id = cp_bus_id
and ost.organization_structure_id = cp_str_id
and ost.organization_structure_id = osv.organization_structure_id
and osv.org_structure_version_id = ose.org_structure_version_id
and trunc(sysdate) between nvl(osv.date_from,trunc(sysdate)) and nvl(osv.date_to,sysdate)
and not exists
( select null
from per_org_structure_elements ose2
where ose2.org_structure_version_id = osv.org_structure_version_id
and ose.organization_id_parent = ose2.organization_id_child );
select osv.org_structure_version_id
from per_organization_structures ost
, per_org_structure_versions osv
where ost.business_group_id = cp_bus_id
and ost.organization_structure_id = cp_str_id
and ost.organization_structure_id = osv.organization_structure_id
and trunc(sysdate) between nvl(osv.date_from,trunc(sysdate)) and nvl(osv.date_to,sysdate);
select v.org_structure_version_id
from per_organization_structures s
, per_org_structure_versions v
where s.business_group_id = cp_bus_id
and s.organization_structure_id = cp_str_id
and s.organization_structure_id = v.organization_structure_id
and trunc(sysdate) between nvl(v.date_from,trunc(sysdate)) and nvl(v.date_to,sysdate);
FUNCTION get_poplist(p_select_statement VARCHAR2
,p_parameter_list VARCHAR2
,p_parameter_name VARCHAR2
,p_parameter_value VARCHAR2
,p_report_name VARCHAR2
,p_report_link VARCHAR2) RETURN VARCHAR2 AS
l_poplist VARCHAR2(32767);
l_new_select_statement VARCHAR2(2000);
l_new_select_statement:=p_select_statement;
l_poplist:='
DBMS_SQL.PARSE(l_cursorID,l_new_select_statement,dbms_sql.v7);
l_poplist:=l_poplist||'SELECTED ';
l_poplist:=l_poplist||'
';
select country
from hr_locations
where location_id = p_location_id;
select pos.location_id
,loc.country
from hr_locations loc
,per_positions pos
where pos.position_id = p_position_id
and pos.location_id = loc.location_id;
select org.location_id
,loc.country
from hr_locations loc
,hr_organization_units org
where org.organization_id = p_organization_id
and org.location_id = loc.location_id;
select bg.location_id
,loc.country
from hr_locations loc
,per_business_groups bg
where bg.business_group_id = p_business_group_id
and bg.location_id = loc.location_id;
'select loc.' || g_region_segment ||
' from hr_locations loc' ||
' where loc.location_id = :p_location_id'
using p_location_id;
'select loc.' || g_region_segment ||
' ,loc.location_id' ||
' from hr_locations loc' ||
' , per_positions pos' ||
' where loc.location_id = pos.location_id' ||
' and pos.position_id = :p_position_id'
using p_position_id;
'select loc.' || g_region_segment ||
' ,loc.location_id' ||
' from hr_locations loc' ||
' , hr_organization_units hou' ||
' where loc.location_id = hou.location_id' ||
' and hou.organization_id = :p_organization_id'
using p_organization_id;
'select loc.' || g_region_segment ||
' ,loc.location_id' ||
' from hr_locations loc' ||
' , per_business_groups bgr' ||
' where loc.location_id = bgr.location_id' ||
' and bgr.organization_id = :p_business_group_id'
using p_business_group_id;
select nvl(ter.parent_territory_code,'Unassigned')
from bis_territory_hierarchies_v ter
, hr_locations loc
where ter.child_territory_code(+) = loc.country
and decode(ter.parent_territory_type,null,'AREA'
,ter.parent_territory_type) = 'AREA'
and loc.location_id = p_location_id;
select nvl(ter.parent_territory_code, 'Unassigned')
from bis_territory_hierarchies_v ter
, hr_locations loc
, per_positions pos
where ter.child_territory_code(+) = loc.country
and decode(ter.parent_territory_type,null,'AREA'
,ter.parent_territory_type) = 'AREA'
and pos.location_id = loc.location_id
and pos.position_id = p_position_id;
select nvl(ter.parent_territory_code, 'Unassigned')
from bis_territory_hierarchies_v ter
, hr_locations loc
, hr_organization_units hou
where ter.child_territory_code(+) = loc.country
and decode(ter.parent_territory_type,null,'AREA'
,ter.parent_territory_type) = 'AREA'
and hou.location_id = loc.location_id
and hou.organization_id = p_organization_id;
select ter.parent_territory_code
from bis_territory_hierarchies_v ter
, hr_locations loc
, per_business_groups bgr
where ter.child_territory_code = loc.country
and ter.parent_territory_type = 'AREA'
and bgr.location_id = loc.location_id
and bgr.business_group_id = p_business_group_id;
select loc.country
from hr_locations loc
where loc.location_id = p_location_id;
select loc.country
from hr_locations loc
, per_positions pos
where loc.location_id = pos.location_id
and pos.position_id = p_position_id;
select loc.country
from hr_locations loc
, hr_organization_units hou
where loc.location_id = hou.location_id
and hou.organization_id = p_organization_id;
select loc.country
from hr_locations loc
, per_business_groups bgr
where loc.location_id = bgr.location_id
and bgr.business_group_id = p_business_group_id;
'select loc.'||g_region_segment||
' from hr_locations loc'||
' where loc.location_id = :p_location_id'
using p_location_id;
'select loc.'||g_region_segment||
' from hr_locations loc'||
' , per_positions pos'||
' where loc.location_id = pos.location_id'||
' and pos.position_id = :p_position_id'
using p_position_id;
'select loc.'||g_region_segment||
' from hr_locations loc'||
' , hr_organization_units hou'||
' where loc.location_id = hou.location_id'||
' and hou.organization_id = :p_organization_id'
using p_organization_id;
'select loc.'||g_region_segment||
' from hr_locations loc'||
' , per_business_groups bgr'||
' where loc.location_id = bgr.location_id'||
' and bgr.organization_id = :p_business_group_id'
using p_business_group_id;
select bfm.application_column_name
from bis_flex_mappings_v bfm
, bis_dimensions bd
where bfm.dimension_id = bd.dimension_id
and bd.short_name = 'GEOGRAPHY'
and bfm.level_short_name = 'REGION'
and bfm.application_id = 800;
select formula_name
from ff_formulas_f
where formula_id = p_formula_id;
select formula_id
from ff_compiled_info_f
where formula_id = p_formula_id;