[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT system_type_cd
FROM per_shared_types
WHERE shared_type_id = p_unit_of_measure_id
AND lookup_type = 'BUDGET_MEASUREMENT_TYPE';
PROCEDURE insert_headcount_budgets (p_budget_id IN NUMBER
,p_business_group_id IN NUMBER
,p_budgeted_entity_cd IN VARCHAR2
,p_budget_measurement_type IN VARCHAR2
,p_currency_code IN VARCHAR2
,p_budget_aggregate IN VARCHAR2
,p_budget_start_date IN DATE
,p_budget_end_date IN DATE
,p_budget_version_id IN NUMBER
,p_version_start_date IN DATE
,p_version_end_date IN DATE
,p_unit IN NUMBER)
IS
--
--
-- Variables to populate WHO Columns
--
l_current_time DATE;
dbg('Inside insert_headcount_budgets');
INSERT INTO HRI_MB_BDGTS_CT (
HRI_MB_BDGTS_CT_ID
,BUDGET_ID
,BUSINESS_GROUP_ID
,BUDGETED_ENTITY_CD
,BUDGET_MEASUREMENT_TYPE
,BUDGET_CURRENCY_CODE
,BUDGET_AGGREGATE
,BUDGET_START_DATE
,BUDGET_END_DATE
,BUDGET_VERSION_ID
,VERSION_START_DATE
,VERSION_END_DATE
,BUDGET_DETAIL_ID
,ORGANIZATION_ID
,JOB_ID
,POSITION_ID
,GRADE_ID
,BUDGET_PERIOD_ID
,PERIOD_START_DATE
,PERIOD_END_DATE
,BUDGET_VALUE
--
-- WHO Columns
--
,last_update_date
,last_update_login
,last_updated_by
,created_by
,creation_date)
SELECT
hri_mb_bdgts_ct_s.nextval
,p_budget_id
,p_business_group_id
,p_budgeted_entity_cd
,p_budget_measurement_type
,p_currency_code
,p_budget_aggregate
,p_budget_start_date
,p_budget_end_date
,p_budget_version_id
,p_version_start_date
,p_version_end_date
,det.budget_detail_id
,nvl(det.organization_id, -1)
,nvl(det.job_id, -1)
,nvl(det.position_id, -1)
,nvl(det.grade_id,-1)
,prd.budget_period_id
,ptps.start_date
,ptpe.end_date
,CASE WHEN p_unit = 1 THEN prd.budget_unit1_value
WHEN p_unit = 2 THEN prd.budget_unit2_value
ELSE prd.budget_unit3_value
END budget_value
,SYSDATE
,l_user_id
,l_user_id
,l_user_id
,SYSDATE
FROM pqh_budget_details det,
pqh_budget_periods prd,
per_time_periods ptps,
per_time_periods ptpe
WHERE det.budget_version_id = p_budget_version_id
AND det.budget_detail_id = prd.budget_detail_id
AND prd.start_time_period_id = ptps.time_period_id
AND prd.end_time_period_id = ptpe.time_period_id ;
dbg(SQL%ROWCOUNT||' headcount records inserted into HRI_MB_BDGTS_CT');
dbg('Exiting insert_headcount_budgets');
END insert_headcount_budgets;
PROCEDURE insert_laborcost_budgets (p_budget_id IN NUMBER
,p_business_group_id IN NUMBER
,p_budgeted_entity_cd IN VARCHAR2
,p_budget_measurement_type IN VARCHAR2
,p_currency_code IN VARCHAR2
,p_budget_aggregate IN VARCHAR2
,p_budget_start_date IN DATE
,p_budget_end_date IN DATE
,p_budget_version_id IN NUMBER
,p_version_start_date IN DATE
,p_version_end_date IN DATE
,p_unit IN NUMBER)
IS
--
--
-- Variables to populate WHO Columns
--
l_current_time DATE;
dbg('Inside insert_laborcost_budgets');
INSERT INTO HRI_MB_BDGTS_CT (
HRI_MB_BDGTS_CT_ID
,BUDGET_ID
,BUSINESS_GROUP_ID
,BUDGETED_ENTITY_CD
,BUDGET_MEASUREMENT_TYPE
,BUDGET_CURRENCY_CODE
,BUDGET_AGGREGATE
,BUDGET_START_DATE
,BUDGET_END_DATE
,BUDGET_VERSION_ID
,VERSION_START_DATE
,VERSION_END_DATE
,BUDGET_DETAIL_ID
,ORGANIZATION_ID
,JOB_ID
,POSITION_ID
,GRADE_ID
,BUDGET_PERIOD_ID
,PERIOD_START_DATE
,PERIOD_END_DATE
,BUDGET_SET_ID
,BUDGET_ELEMENT_ID
,ELEMENT_TYPE_ID
,BUDGET_FUND_SRC_ID
,COST_ALLOCATION_KEYFLEX_ID
,BUDGET_VALUE
--
-- WHO Columns
--
,last_update_date
,last_update_login
,last_updated_by
,created_by
,creation_date)
SELECT
hri_mb_bdgts_ct_s.nextval
,p_budget_id
,p_business_group_id
,p_budgeted_entity_cd
,p_budget_measurement_type
,p_currency_code
,p_budget_aggregate
,p_budget_start_date
,p_budget_end_date
,p_budget_version_id
,p_version_start_date
,p_version_end_date
,det.budget_detail_id
,nvl(det.organization_id, -1)
,nvl(det.job_id, -1)
,nvl(det.position_id, -1)
,nvl(det.grade_id,-1)
,prd.budget_period_id
,ptps.start_date
,ptpe.end_date
,bset.budget_set_id
,ele.budget_element_id
,ele.element_type_id
,src.budget_fund_src_id
,src.cost_allocation_keyflex_id
,CASE WHEN p_unit = 1 THEN
(src.distribution_percentage * ( ele.distribution_percentage * bset.budget_unit1_value ) / 100 ) / 100
WHEN p_unit = 2 THEN
(src.distribution_percentage * ( ele.distribution_percentage * bset.budget_unit2_value ) / 100 ) / 100
ELSE (src.distribution_percentage * ( ele.distribution_percentage * bset.budget_unit3_value ) / 100 ) / 100
END budget_value
,SYSDATE
,l_user_id
,l_user_id
,l_user_id
,SYSDATE
FROM pqh_budget_details det,
pqh_budget_periods prd,
per_time_periods ptps,
per_time_periods ptpe,
pqh_budget_sets bset,
pqh_budget_elements ele,
pqh_budget_fund_srcs src
WHERE det.budget_version_id = p_budget_version_id
AND det.budget_detail_id = prd.budget_detail_id
AND prd.budget_period_id = bset.budget_period_id
AND prd.start_time_period_id = ptps.time_period_id
AND prd.end_time_period_id = ptpe.time_period_id
AND bset.budget_set_id = ele.budget_set_id
AND ele.budget_element_id = src.budget_element_id;
dbg(SQL%ROWCOUNT||' labor cost records inserted into HRI_MB_BDGTS_CT');
dbg('Exiting insert_laborcost_budgets');
END insert_laborcost_budgets;
SELECT bdgt.budget_id,
bdgt.budget_name,
bdgt.business_group_id,
bdgt.budgeted_entity_cd,
NVL(bdgt.currency_code, pqh_budget.get_currency_cd(bdgt.budget_id)) CURRENCY_CODE,
bdgt.budget_unit1_id,
bdgt.budget_unit2_id,
bdgt.budget_unit3_id,
bdgt.budget_unit1_aggregate,
bdgt.budget_unit2_aggregate,
bdgt.budget_unit3_aggregate,
bdgt.budget_start_date,
bdgt.budget_end_date,
ver.budget_version_id,
ver.date_from,
ver.date_to
FROM pqh_budgets bdgt,
pqh_budget_versions ver
WHERE bdgt.position_control_flag = 'Y'
AND bdgt.budgeted_entity_cd IN ('ORGANIZATION','POSITION')
AND bdgt.budget_id = ver.budget_id
AND (( bdgt.budget_start_date BETWEEN g_refresh_start_date AND g_refresh_end_date )
OR ( g_refresh_start_date BETWEEN bdgt.budget_start_date AND bdgt.budget_end_date ))
AND ver.budget_version_id = ( SELECT max(budget_version_id)
FROM pqh_budget_versions pbv
WHERE pbv.budget_id = bdgt.budget_id );
insert_headcount_budgets(p_budget_id => csr_budget_rec.budget_id
,p_business_group_id => csr_budget_rec.business_group_id
,p_budgeted_entity_cd => csr_budget_rec.budgeted_entity_cd
,p_budget_measurement_type => l_unit1_measure
,p_currency_code => csr_budget_rec.currency_code
,p_budget_aggregate => csr_budget_rec.budget_unit1_aggregate
,p_budget_start_date => csr_budget_rec.budget_start_date
,p_budget_end_date => csr_budget_rec.budget_end_date
,p_budget_version_id => csr_budget_rec.budget_version_id
,p_version_start_date => csr_budget_rec.date_from
,p_version_end_date => csr_budget_rec.date_to
,p_unit => 1 );
insert_laborcost_budgets(p_budget_id => csr_budget_rec.budget_id
,p_business_group_id => csr_budget_rec.business_group_id
,p_budgeted_entity_cd => csr_budget_rec.budgeted_entity_cd
,p_budget_measurement_type => l_unit1_measure
,p_currency_code => csr_budget_rec.currency_code
,p_budget_aggregate => csr_budget_rec.budget_unit1_aggregate
,p_budget_start_date => csr_budget_rec.budget_start_date
,p_budget_end_date => csr_budget_rec.budget_end_date
,p_budget_version_id => csr_budget_rec.budget_version_id
,p_version_start_date => csr_budget_rec.date_from
,p_version_end_date => csr_budget_rec.date_to
,p_unit => 1 );
insert_headcount_budgets(p_budget_id => csr_budget_rec.budget_id
,p_business_group_id => csr_budget_rec.business_group_id
,p_budgeted_entity_cd => csr_budget_rec.budgeted_entity_cd
,p_budget_measurement_type => l_unit2_measure
,p_currency_code => csr_budget_rec.currency_code
,p_budget_aggregate => csr_budget_rec.budget_unit2_aggregate
,p_budget_start_date => csr_budget_rec.budget_start_date
,p_budget_end_date => csr_budget_rec.budget_end_date
,p_budget_version_id => csr_budget_rec.budget_version_id
,p_version_start_date => csr_budget_rec.date_from
,p_version_end_date => csr_budget_rec.date_to
,p_unit => 2 );
insert_laborcost_budgets(p_budget_id => csr_budget_rec.budget_id
,p_business_group_id => csr_budget_rec.business_group_id
,p_budgeted_entity_cd => csr_budget_rec.budgeted_entity_cd
,p_budget_measurement_type => l_unit2_measure
,p_currency_code => csr_budget_rec.currency_code
,p_budget_aggregate => csr_budget_rec.budget_unit2_aggregate
,p_budget_start_date => csr_budget_rec.budget_start_date
,p_budget_end_date => csr_budget_rec.budget_end_date
,p_budget_version_id => csr_budget_rec.budget_version_id
,p_version_start_date => csr_budget_rec.date_from
,p_version_end_date => csr_budget_rec.date_to
,p_unit => 2 );
insert_headcount_budgets(p_budget_id => csr_budget_rec.budget_id
,p_business_group_id => csr_budget_rec.business_group_id
,p_budgeted_entity_cd => csr_budget_rec.budgeted_entity_cd
,p_budget_measurement_type => l_unit3_measure
,p_currency_code => csr_budget_rec.currency_code
,p_budget_aggregate => csr_budget_rec.budget_unit1_aggregate
,p_budget_start_date => csr_budget_rec.budget_start_date
,p_budget_end_date => csr_budget_rec.budget_end_date
,p_budget_version_id => csr_budget_rec.budget_version_id
,p_version_start_date => csr_budget_rec.date_from
,p_version_end_date => csr_budget_rec.date_to
,p_unit => 3 );
insert_laborcost_budgets(p_budget_id => csr_budget_rec.budget_id
,p_business_group_id => csr_budget_rec.business_group_id
,p_budgeted_entity_cd => csr_budget_rec.budgeted_entity_cd
,p_budget_measurement_type => l_unit3_measure
,p_currency_code => csr_budget_rec.currency_code
,p_budget_aggregate => csr_budget_rec.budget_unit3_aggregate
,p_budget_start_date => csr_budget_rec.budget_start_date
,p_budget_end_date => csr_budget_rec.budget_end_date
,p_budget_version_id => csr_budget_rec.budget_version_id
,p_version_start_date => csr_budget_rec.date_from
,p_version_end_date => csr_budget_rec.date_to
,p_unit => 3 );
dbg('Full Refresh selected - Creating indexes');
dbg('Full Refresh selected - gathering stats');