[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Select
bud.period_set_name,
bud.budget_start_date,
bud.budget_end_date,
bpr.reallocation_amt,
decode(p_system_budget_unit,
PQH_PSF_BUS.GET_SYSTEM_SHARED_TYPE(budget_unit1_id), budget_unit1_aggregate,
PQH_PSF_BUS.GET_SYSTEM_SHARED_TYPE(budget_unit2_id), budget_unit2_aggregate,
PQH_PSF_BUS.GET_SYSTEM_SHARED_TYPE(budget_unit3_id), budget_unit3_aggregate) budget_unit_aggregate
from
pqh_bdgt_pool_realloctions bpr,
pqh_budget_pools bpl,
pqh_budgets bud,
pqh_budget_versions bvr
where
bpl.pool_id = bpr.pool_id
and trunc(p_effective_date) between trunc(bud.budget_start_date) and trunc(bud.budget_end_date)
and bud.budget_id = bvr.budget_id
and trunc(p_effective_date) between trunc(bvr.date_from) and trunc(bvr.date_to)
and bvr.budget_version_id = bpl.budget_version_id
and nvl(bud.position_control_flag,'X') = 'Y'
and bud.budgeted_entity_cd = p_budget_entity
and nvl(p_position_id, nvl(bpr.position_id, -1)) =
nvl(bpr.position_id, -1)
--
-- Commented because no reallocation is possible for entities - job, grade and organization.
--
-- and nvl(p_organization_id, nvl(bpr.organization_id, -1)) =
-- nvl(bpr.organization_id, -1)
-- and nvl(p_job_id, nvl(bpr.job_id, -1)) =
-- nvl(bpr.job_id, -1)
-- and nvl(p_grade_id, nvl(bpr.grade_id, -1)) =
-- nvl(bpr.grade_id, -1)
and PQH_PSF_BUS.GET_SYSTEM_SHARED_TYPE(bpl.budget_unit_id) = p_system_budget_unit
and (p_effective_date between bvr.date_from and bvr.date_to)
and bud.business_group_id = p_business_group_id
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) = p_system_budget_unit
or hr_psf_shd.SYSTEM_AVAILABILITY_STATUS(budget_unit2_id) = p_system_budget_unit
or hr_psf_shd.SYSTEM_AVAILABILITY_STATUS(budget_unit3_id) = p_system_budget_unit
);
Select
bud.period_set_name,
bud.budget_start_date,
bud.budget_end_date,
decode(rec_amt.transaction_type,'DD',-1*rec_amt.reallocation_amt,'RD',rec_amt.reallocation_amt) reallocation_amt,
decode(p_system_budget_unit,
PQH_PSF_BUS.GET_SYSTEM_SHARED_TYPE(budget_unit1_id), budget_unit1_aggregate,
PQH_PSF_BUS.GET_SYSTEM_SHARED_TYPE(budget_unit2_id), budget_unit2_aggregate,
PQH_PSF_BUS.GET_SYSTEM_SHARED_TYPE(budget_unit3_id), budget_unit3_aggregate) budget_unit_aggregate
from pqh_budget_pools fld,
pqh_budget_pools trnx,
pqh_bdgt_pool_realloctions trnx_dtl,
pqh_bdgt_pool_realloctions rec_amt,
pqh_budgets bud,
pqh_budget_versions bvr
where trunc(p_effective_date) between trunc(bud.budget_start_date) and trunc(bud.budget_end_date)
and 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 <= 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) = p_system_budget_unit
or hr_psf_shd.SYSTEM_AVAILABILITY_STATUS(budget_unit2_id) = p_system_budget_unit
or hr_psf_shd.SYSTEM_AVAILABILITY_STATUS(budget_unit3_id) = p_system_budget_unit
)
and bud.budget_id = bvr.budget_id
and trunc(p_effective_date) between trunc(bvr.date_from) and trunc(bvr.date_to)
and bvr.budget_version_id = fld.budget_version_id
and PQH_PSF_BUS.GET_SYSTEM_SHARED_TYPE(fld.budget_unit_id) = p_system_budget_unit
and fld.pool_id = trnx.parent_pool_id
and trnx.pool_id = trnx_dtl.pool_id
and trnx_dtl.reallocation_id = rec_amt.txn_detail_id
and nvl(p_position_id, nvl(rec_amt.entity_id, -1)) =
nvl(rec_amt.entity_id, -1)
and nvl(p_organization_id, nvl(rec_amt.entity_id, -1)) =
nvl(rec_amt.entity_id, -1)
and nvl(p_job_id, nvl(rec_amt.entity_id, -1)) =
nvl(rec_amt.entity_id, -1)
and nvl(p_grade_id, nvl(rec_amt.entity_id, -1)) =
nvl(rec_amt.entity_id, -1);
Select bud.period_set_name,
bud.budget_start_date,
bud.budget_end_date,
rec_amt.reallocation_amt,
rec_amt.reserved_amt
from pqh_budget_pools fld,
pqh_budget_pools trnx,
pqh_bdgt_pool_realloctions trnx_dtl,
pqh_bdgt_pool_realloctions rec_amt,
pqh_budgets bud,
pqh_budget_versions bvr
where trunc(p_effective_date) between bud.budget_start_date and bud.budget_end_date
and bud.position_control_flag = 'Y'
and bud.budgeted_entity_cd = 'POSITION'
and rec_amt.transaction_type = l_txn_type
and bud.business_group_id = p_business_group_id
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')
and bud.budget_id = bvr.budget_id
and trunc(p_effective_date) between bvr.date_from and bvr.date_to
and bvr.budget_version_id = fld.budget_version_id
and PQH_PSF_BUS.GET_SYSTEM_SHARED_TYPE(fld.budget_unit_id) = 'MONEY'
and fld.pool_id = trnx.parent_pool_id
and trnx.pool_id = trnx_dtl.pool_id
and trnx_dtl.reallocation_id = rec_amt.txn_detail_id
and rec_amt.entity_id = p_position_id;