[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select
pos.position_id, pos.job_id, name
from
hr_all_positions_f_vl pos
where
pos.organization_id = p_organization_id
and p_effective_date between pos.effective_start_date and pos.effective_end_date
order by position_id;
select
ver.org_structure_version_id
from
per_organization_structures str
, per_org_structure_versions ver
where
str.position_control_structure_flg = 'Y'
and str.business_group_id = p_business_group_id
and ver.business_group_id = p_business_group_id
and str.organization_structure_id = ver.organization_structure_id
and p_effective_date between ver.date_from and nvl(date_to, hr_general.end_of_time);
SELECT
0 rn,
0 level1,
ORGANIZATION_ID
FROM HR_ALL_ORGANIZATION_UNITS u
WHERE ORGANIZATION_ID = p_start_org_id
and business_group_id = p_business_group_id
and exists
(select null from per_org_structure_elements e
where e.org_structure_version_id = p_org_structure_version_id
and (e.organization_id_child = p_start_org_id
or e.organization_id_parent = p_start_org_id) )
UNION
SELECT
rownum rn,
level level1,
organization_id_child organization_id
FROM PER_ORG_STRUCTURE_ELEMENTS A
start with
organization_id_parent = p_start_org_id
and ORG_STRUCTURE_VERSION_ID = p_org_structure_version_id
connect by
organization_id_parent = prior organization_id_child
and ORG_STRUCTURE_VERSION_ID = p_org_structure_version_id;
select
'x'
from
pqh_process_log
where
log_context=p_batch_name;
select
pqh_process_log_s.nextval
from
dual;
SELECT
table_route_id
from
pqh_table_route
where
table_alias = p_table_alias;
select pqh_wf_notifications_s.nextval
from dual;
select user_name
from fnd_user
where employee_id =
(select psf.supervisor_id
from hr_all_positions_f psf
where psf.position_id = p_position_id
and l_effective_date >= psf.effective_start_date
and l_effective_date <= psf.effective_end_date
);
pqh_process_batch_log.insert_log
(
p_message_type_cd =>l_message_type,
p_message_text =>l_message,
p_information3 =>l_budgeted_sal,
p_information4 =>l_reallocation_sal,
p_information5 =>l_actual_sal,
p_information6 =>l_commitment_sal,
p_information7 =>l_user_name,
p_information13 =>l_currency_code
);
select budget_id, budget_start_date, budget_end_date
from pqh_budgets
where
nvl(position_control_flag,'X') = 'Y'
and budgeted_entity_cd = 'POSITION'
and ((p_start_date <= budget_start_date
and p_end_date >= budget_end_date
) or
(p_start_date between budget_start_date and budget_end_date) or
(p_end_date between budget_start_date and budget_end_date)
)
and (
hr_psf_shd.SYSTEM_AVAILABILITY_STATUS(budget_unit1_id) = 'MONEY'
or hr_psf_shd.SYSTEM_AVAILABILITY_STATUS(budget_unit2_id) = 'MONEY'
or hr_psf_shd.SYSTEM_AVAILABILITY_STATUS(budget_unit3_id) = 'MONEY'
);
select bgt.budget_id, budget_version_id, budget_start_date, budget_end_date
from pqh_budgets bgt, pqh_budget_versions ver
where
bgt.budget_id = ver.budget_id
and (p_effective_date between date_from and date_to)
and nvl(position_control_flag,'X') = 'Y'
and budgeted_entity_cd = 'POSITION'
and ((p_start_date <= budget_start_date
and p_end_date >= budget_end_date
) or
(p_start_date between budget_start_date and budget_end_date) or
(p_end_date between budget_start_date and budget_end_date)
)
and (
hr_psf_shd.SYSTEM_AVAILABILITY_STATUS(budget_unit1_id) = 'MONEY'
or hr_psf_shd.SYSTEM_AVAILABILITY_STATUS(budget_unit2_id) = 'MONEY'
or hr_psf_shd.SYSTEM_AVAILABILITY_STATUS(budget_unit3_id) = 'MONEY'
);
select name
from hr_all_organization_units u
where organization_id = p_org_id;
SELECT
table_route_id
from
pqh_table_route
where
table_alias = p_table_alias;
select pos.position_id, pos.job_id, name
from hr_all_positions_f_vl pos
where pos.organization_id = p_organization_id
and p_effective_date between pos.effective_start_date and pos.effective_end_date
order by position_id;
select user_name
from fnd_user
where employee_id =
(select psf.supervisor_id
from hr_all_positions_f psf
where psf.position_id = p_position_id
and p_effective_date >= psf.effective_start_date
and p_effective_date <= psf.effective_end_date
);
select pqh_wf_notifications_s.nextval
from dual;
pqh_process_batch_log.insert_log
(
p_message_type_cd =>l_message_type,
p_message_text =>l_message,
p_information3 =>round(l_budgeted_val,2),
p_information4 =>round(l_reallocation_val,2),
p_information5 =>round(l_actual_val,2),
p_information6 =>round(l_commitment_val,2),
p_information7 =>l_user_name,
p_information8 =>'POSITION',
p_information9 =>p_unit_of_measure,
p_information10 =>l_under_bgt_date,
p_information11 =>p_batch_name,
p_information12 =>'REPORT',
p_information13 =>l_currency_code
);
pqh_process_batch_log.insert_log
(
p_message_type_cd =>l_message_type,
p_message_text =>l_message,
p_information3 =>round(l_budgeted_val,2),
p_information4 =>round(l_reallocation_val,2),
p_information5 =>round(l_actual_val,2),
p_information7 =>l_user_name,
p_information8 =>'POSITION',
p_information9 =>p_unit_of_measure,
p_information10 =>l_under_bgt_date,
p_information11 =>p_batch_name,
p_information12 =>'REPORT'
);
pqh_process_batch_log.insert_log
(
p_message_type_cd =>l_message_type,
p_message_text =>l_message,
p_information3 =>round(l_budgeted_val,2),
p_information4 =>round(l_reallocation_val,2),
p_information5 =>round(l_actual_val,2),
p_information7 =>l_user_name,
p_information8 =>'POSITION',
p_information9 =>p_unit_of_measure,
p_information10 =>to_char(l_budgeted_fte_date,'YYYY/MM/DD'),
p_information11 =>p_batch_name,
p_information12 =>'REPORT'
);
select ver.org_structure_version_id
from per_organization_structures str
, per_org_structure_versions ver
where str.position_control_structure_flg = 'Y'
and str.organization_structure_id = p_org_structure_id
and str.business_group_id = p_business_group_id
and ver.business_group_id = p_business_group_id
and str.organization_structure_id = ver.organization_structure_id
and p_effective_date between ver.date_from and nvl(date_to, hr_general.end_of_time);
select organization_id_parent organization_id
from per_org_structure_elements a
where org_structure_version_id = p_org_structure_version_id
and not exists (
select organization_id_child organization_id
from per_org_structure_elements b
where org_structure_version_id = p_org_structure_version_id
and b.organization_id_child = a.organization_id_parent
)
and rownum <2;
select 0 rn,
0 level1,
organization_id
from hr_all_organization_units u
where organization_id = p_start_org_id
and business_group_id = p_business_group_id
and exists
(select null from per_org_structure_elements e
where e.org_structure_version_id = p_org_structure_version_id
and (e.organization_id_child = p_start_org_id
or e.organization_id_parent = p_start_org_id ) )
union
select rownum rn,
level level1,
organization_id_child organization_id
from per_org_structure_elements a
start with
organization_id_parent = p_start_org_id
and org_structure_version_id = p_org_structure_version_id
connect by
organization_id_parent = prior organization_id_child
and org_structure_version_id = p_org_structure_version_id;
select rownum rn,
0 level1,
organization_id
from hr_all_organization_units
where business_group_id = p_business_group_id
and INTERNAL_EXTERNAL_FLAG ='INT'
and p_effective_date between date_from and nvl(date_to, hr_general.end_of_time);
select 'x'
from pqh_process_log
where log_context=p_batch_name;
select pqh_process_log_s.nextval
from dual;
select distinct bdet.job_id, job.name
from pqh_budgets bud,
pqh_budget_versions bver,
pqh_budget_details bdet,
per_jobs_tl job
where bud.business_group_id = p_business_group_id
and bud.position_control_flag = 'Y'
and bud.budgeted_entity_cd = 'JOB'
and (p_start_date1 <= bud.budget_end_date
and p_end_date1 >= bud.budget_start_date)
and bver.budget_id = bud.budget_id
and bver.budget_version_id = bdet.budget_version_id
and bdet.job_id = job.job_id
and job.language = userenv('LANG');
select 'x'
from pqh_process_log
where log_context=p_batch_name;
select
pqh_process_log_s.nextval
from
dual;
SELECT
table_route_id
from
pqh_table_route
where
table_alias = p_table_alias;
pqh_process_batch_log.insert_log
(
p_message_type_cd =>l_message_type,
p_message_text =>l_message,
p_information3 =>round(l_budgeted_val,2),
p_information4 =>round(l_reallocation_val,2),
p_information5 =>round(l_actual_val,2),
p_information6 =>round(l_commitment_val,2), -- p_information7 =>l_user_name
p_information8 =>'JOB',
p_information9 =>p_unit_of_measure,
p_information10 =>l_under_bgt_date,
p_information11 =>p_batch_name,
p_information12 =>'REPORT',
p_information13 => l_currency_code
);
pqh_process_batch_log.insert_log
(
p_message_type_cd =>l_message_type,
p_message_text =>l_message,
p_information3 =>round(l_budgeted_val,2),
p_information4 =>round(l_reallocation_val,2),
p_information5 =>round(l_actual_val,2),
p_information7 =>null,
p_information8 =>'JOB',
p_information9 =>p_unit_of_measure,
p_information10 =>l_under_bgt_date,
p_information11 =>p_batch_name,
p_information12 =>'REPORT'
);
pqh_process_batch_log.insert_log
(
p_message_type_cd =>l_message_type,
p_message_text =>l_message,
p_information3 =>round(l_budgeted_val,2),
p_information4 =>round(l_reallocation_val,2),
p_information5 =>round(l_actual_val,2),
p_information7 =>null,
p_information8 =>'JOB',
p_information9 =>p_unit_of_measure,
p_information10 =>to_char(l_budgeted_fte_date,'RRRR/MM/DD'),--l_under_bgt_date,
p_information11 =>p_batch_name,
p_information12 =>'REPORT'
);
select distinct bdet.grade_id, grd.name
from pqh_budgets bud,
pqh_budget_versions bver,
pqh_budget_details bdet,
per_grades_tl grd
where bud.business_group_id = p_business_group_id
and bud.position_control_flag = 'Y'
and bud.budgeted_entity_cd = 'GRADE'
and (p_start_date1 <= bud.budget_end_date
and p_end_date1 >= bud.budget_start_date)
and bver.budget_id = bud.budget_id
and bver.budget_version_id = bdet.budget_version_id
and bdet.grade_id = grd.grade_id
and grd.language = userenv('LANG');
select 'x'
from pqh_process_log
where log_context=p_batch_name;
select
pqh_process_log_s.nextval
from
dual;
SELECT
table_route_id
from
pqh_table_route
where
table_alias = p_table_alias;
pqh_process_batch_log.insert_log
(
p_message_type_cd =>l_message_type,
p_message_text =>l_message,
p_information3 =>round(l_budgeted_val,2),
p_information4 =>round(l_reallocation_val,2),
p_information5 =>round(l_actual_val,2),
p_information6 =>round(l_commitment_val,2),
p_information8 =>'GRADE',
p_information9 =>p_unit_of_measure,
p_information10 =>l_under_bgt_date,
p_information11 =>p_batch_name,
p_information12 =>'REPORT',
p_information13 => l_currency_code
);
pqh_process_batch_log.insert_log
(
p_message_type_cd =>l_message_type,
p_message_text =>l_message,
p_information3 =>round(l_budgeted_val,2),
p_information4 =>round(l_reallocation_val,2),
p_information5 =>round(l_actual_val,2),
p_information7 =>null,
p_information8 =>'GRADE',
p_information9 =>p_unit_of_measure,
p_information10 =>l_under_bgt_date,
p_information11 =>p_batch_name,
p_information12 =>'REPORT'
);
pqh_process_batch_log.insert_log
(
p_message_type_cd =>l_message_type,
p_message_text =>l_message,
p_information3 =>round(l_budgeted_val,2),
p_information4 =>round(l_reallocation_val,2),
p_information5 =>round(l_actual_val,2),
p_information7 =>null,
p_information8 =>'GRADE',
p_information9 =>p_unit_of_measure,
p_information10 =>to_char(l_budgeted_fte_date,'RRRR/MM/DD'),
p_information11 =>p_batch_name,
p_information12 =>'REPORT'
);
select name
from hr_all_organization_units u
where organization_id = p_org_id;
SELECT
table_route_id
from
pqh_table_route
where
table_alias = p_table_alias;
pqh_process_batch_log.insert_log
(
p_message_type_cd =>l_message_type,
p_message_text =>l_message,
p_information3 =>round(l_budgeted_val,2),
p_information4 =>round(l_reallocation_val,2),
p_information5 =>round(l_actual_val,2),
p_information6 =>round(l_commitment_val,2),
p_information7 =>l_user_name,
p_information8 =>'ORGANIZATION',
p_information9 =>p_unit_of_measure,
p_information10 =>l_under_bgt_date,
p_information11 =>p_batch_name,
p_information12 =>'REPORT',
p_information13 => l_currency_code
);
pqh_process_batch_log.insert_log
(
p_message_type_cd =>l_message_type,
p_message_text =>l_message,
p_information3 =>round(l_budgeted_val,2),
p_information4 =>round(l_reallocation_val,2),
p_information5 =>round(l_actual_val,2),
p_information7 =>l_user_name,
p_information8 =>'ORGANIZATION',
p_information9 =>p_unit_of_measure,
p_information10 =>l_under_bgt_date,
p_information11 =>p_batch_name,
p_information12 =>'REPORT'
);
pqh_process_batch_log.insert_log
(
p_message_type_cd =>l_message_type,
p_message_text =>l_message,
p_information3 =>round(l_budgeted_val,2),
p_information4 =>round(l_reallocation_val,2),
p_information5 =>round(l_actual_val,2),
p_information7 =>l_user_name,
p_information8 =>'ORGANIZATION',
p_information9 =>p_unit_of_measure,
p_information10 =>to_char(l_budgeted_fte_date,'YYYY/MM/DD'), --l_under_bgt_date,
p_information11 =>p_batch_name,
p_information12 =>'REPORT'
);
select ver.org_structure_version_id
from per_organization_structures str
, per_org_structure_versions ver
where str.position_control_structure_flg = 'Y'
and str.organization_structure_id = p_org_structure_id
and str.business_group_id = p_business_group_id
and ver.business_group_id = p_business_group_id
and str.organization_structure_id = ver.organization_structure_id
and p_effective_date between ver.date_from and nvl(date_to, hr_general.end_of_time);
select organization_id_parent organization_id
from per_org_structure_elements a
where org_structure_version_id = p_org_structure_version_id
and not exists (
select organization_id_child organization_id
from per_org_structure_elements b
where org_structure_version_id = p_org_structure_version_id
and b.organization_id_child = a.organization_id_parent
)
and rownum <2;
select 0 rn,
0 level1,
organization_id
from hr_all_organization_units u
where organization_id = p_start_org_id
and business_group_id = p_business_group_id
and exists
(select null from per_org_structure_elements e
where e.org_structure_version_id = p_org_structure_version_id
and (e.organization_id_child = p_start_org_id
or e.organization_id_parent = p_start_org_id) )
union
select rownum rn,
level level1,
organization_id_child organization_id
from per_org_structure_elements a
start with
organization_id_parent = p_start_org_id
and org_structure_version_id = p_org_structure_version_id
connect by
organization_id_parent = prior organization_id_child
and org_structure_version_id = p_org_structure_version_id;
select rownum rn,
0 level1,
organization_id
from hr_all_organization_units
where business_group_id = p_business_group_id
and INTERNAL_EXTERNAL_FLAG ='INT'
and p_effective_date between date_from and nvl(date_to, hr_general.end_of_time);
select 'x'
from pqh_process_log
where log_context=p_batch_name;
select
pqh_process_log_s.nextval
from
dual;
select pqh_wf_notifications_s.nextval
from dual;
select bud.currency_code
from
pqh_budgets bud,
pqh_budget_versions bver,
pqh_budget_details bdet,
pqh_budget_periods bper,
per_time_periods stp,
per_time_periods etp,
pqh_budget_sets bsets,
pqh_budget_elements bele,
pqh_bdgt_cmmtmnt_elmnts bcl
where nvl(bud.position_control_flag,'X') = 'Y'
and bud.budgeted_entity_cd = p_budget_entity
and bud.business_group_id = p_business_group_id
and ((p_start_date <= bud.budget_start_date
and p_end_date >= bud.budget_end_date)
or
(p_start_date between bud.budget_start_date and bud.budget_end_date) or
(p_end_date between bud.budget_start_date and bud.budget_end_date)
)
and ( hr_psf_shd.SYSTEM_AVAILABILITY_STATUS(budget_unit1_id) = 'MONEY'
or hr_psf_shd.SYSTEM_AVAILABILITY_STATUS(budget_unit2_id) = 'MONEY'
or hr_psf_shd.SYSTEM_AVAILABILITY_STATUS(budget_unit3_id) = 'MONEY'
)
and bud.budget_id = bver.budget_id
and trunc(p_effective_date) between trunc(bver.date_from) and trunc(bver.date_to)
and nvl(p_organization_id, nvl(bdet.organization_id, -1)) =
nvl(bdet.organization_id, -1)
and nvl(p_job_id, nvl(bdet.job_id, -1)) =
nvl(bdet.job_id, -1)
and nvl(p_position_id, nvl(bdet.position_id, -1)) =
nvl(bdet.position_id, -1)
and nvl(p_grade_id, nvl(bdet.grade_id, -1)) =
nvl(bdet.grade_id, -1)
and bver.budget_version_id = bdet.budget_version_id
and bper.budget_detail_id = bdet.budget_detail_id
and bper.start_time_period_id = stp.time_period_id
and bper.end_time_period_id = etp.time_period_id
and etp.end_date >= p_start_date
and stp.start_date <= p_end_date
and bsets.budget_period_id = bper.budget_period_id
and bele.budget_set_id = bsets.budget_set_id
and bud.budget_id = bcl.budget_id
and bele.element_type_id = bcl.element_type_id;
select currency_code
from per_business_groups
where business_group_id = p_business_group_id;