[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select
stp.start_date,
etp.end_date, bud.period_set_name,
(decode('MONEY',
pqh_psf_bus.get_system_shared_type(bud.budget_unit1_id),bsets.budget_unit1_value,
pqh_psf_bus.get_system_shared_type(bud.budget_unit2_id),bsets.budget_unit2_value,
pqh_psf_bus.get_system_shared_type(bud.budget_unit3_id),bsets.budget_unit3_value,
0))
* nvl(bele.distribution_percentage ,0)/100 budget_element_value
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 trunc(p_effective_date) between trunc(bud.budget_start_date) and trunc(bud.budget_end_date)
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
stp.start_date,
etp.end_date, bud.period_set_name,
(decode('HOURS',
pqh_psf_bus.get_system_shared_type(bud.budget_unit1_id),bper.budget_unit1_value,
pqh_psf_bus.get_system_shared_type(bud.budget_unit2_id),bper.budget_unit2_value,
pqh_psf_bus.get_system_shared_type(bud.budget_unit3_id),bper.budget_unit3_value,
0)) budget_period_value
from
pqh_budgets bud,
pqh_budget_versions bver,
pqh_budget_details bdet,
pqh_budget_periods bper,
per_time_periods stp,
per_time_periods etp
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) = 'HOURS'
or hr_psf_shd.SYSTEM_AVAILABILITY_STATUS(budget_unit2_id) = 'HOURS'
or hr_psf_shd.SYSTEM_AVAILABILITY_STATUS(budget_unit3_id) = 'HOURS'
)
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;
select
count(*)
from
per_time_periods
where
period_set_name = p_period_set_name
and p_start_date <= start_date
and p_end_date >= end_date;
select
start_date,
end_date
from
per_time_periods
where
p_period_set_name = period_set_name
and ((p_start_date <= start_date
and p_end_date >= end_date
) or
(p_start_date between start_date and end_date) or
(p_end_date between start_date and end_date)
);