[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT gp.end_date
FROM gl_periods gp
WHERE gp.period_set_name = l_period_set_name
AND gp.period_type = l_accounted_period_type
AND gp.start_date >= l_fcst_start_date
AND gp.adjustment_period_flag = 'N'
ORDER BY gp.start_date;
SELECT gp.end_date
FROM gl_periods gp
WHERE gp.period_set_name = l_period_set_name
AND gp.period_type = l_pa_period_type
AND gp.start_date >= l_fcst_start_date
AND gp.adjustment_period_flag = 'N'
ORDER BY gp.start_date;
SELECT nvl(org_id,-99),
set_of_books_id,
org_structure_version_id,
start_organization_id,
pa_period_type
INTO l_org_id,
l_set_of_books_id,
l_org_struc_version_id,
l_start_organization,
l_pa_period_type
FROM pa_implementations;
SELECT org_fcst_period_type,
start_period_name,
number_of_periods,
weighted_or_full_code,
org_fcst_project_template_id
INTO l_fcst_period_type,
l_start_period_name,
l_number_of_periods,
l_weighted_or_full_code,
l_org_proj_template_id
FROM pa_forecasting_options;
SELECT period_set_name
,accounted_period_type
,currency_code
INTO l_period_set_name
,l_accounted_period_type
,l_org_projfunc_currency_code
FROM gl_sets_of_books
WHERE set_of_books_id = l_set_of_books_id;
SELECT start_date, end_date
INTO l_fcst_start_date, l_fcst_end_date
FROM gl_periods
WHERE period_set_name = l_period_set_name
AND period_type = l_accounted_period_type
AND period_name = l_start_period_name
AND adjustment_period_flag = 'N';
SELECT start_date, end_date
INTO l_fcst_start_date, l_fcst_end_date
FROM gl_periods
WHERE period_set_name = l_period_set_name
AND period_type = l_pa_period_type
AND period_name = l_start_period_name
AND adjustment_period_flag = 'N';
SELECT pp.project_id
INTO x_org_project_id
FROM pa_projects pp
WHERE pp.carrying_out_organization_id = p_organization_id
AND pp.project_type in ( SELECT ppt.project_type
FROM pa_project_types ppt
WHERE ppt.org_project_flag = 'Y')
AND pp.template_flag = 'N';
SELECT pt.task_id,
pt.carrying_out_organization_id
FROM pa_tasks pt
WHERE pt.project_id = p_project_id;
SELECT psb.amount_type_id,
nvl(sum(psb.period_balance),0)
FROM pa_objects po,
pa_summ_balances psb
WHERE po.object_type_code = 'ORG'
AND po.expenditure_org_id = p_org_id
AND po.project_org_id = -1
AND po.expenditure_organization_id = p_organization_id
AND po.project_organization_id = -1
AND po.project_id = -1
AND po.task_id = -1
AND po.person_id = -1
AND po.work_type_id = -1
AND po.org_util_category_id = -1
AND po.res_util_category_id = -1
AND po.balance_type_code = 'FORECAST'
AND po.assignment_id = -1
AND psb.object_id = po.object_id
AND psb.period_type = p_period_type
AND psb.object_type_code = 'ORG'
AND psb.version_id = -1
AND psb.period_set_name = p_period_set_name
AND psb.period_name = p_period_name
AND psb.global_exp_period_end_date = trunc(to_date('01/01/1420','MM/DD/YYYY'))
AND psb.amount_type_id in (32,37,38) /* 32-Weighted hours, 37-capacity, 38-Reduced Capacity */
GROUP BY amount_type_id;
SELECT COUNT(*)
FROM per_assignments_f paf
,per_all_people_f ppf
,per_periods_of_service pps
WHERE paf.organization_id = p_organization_id
AND p_effective_date BETWEEN paf.effective_start_date
AND paf.effective_end_date
AND paf.assignment_type in ('E','C') /*Bug#2911451*/
AND paf.primary_flag = 'Y'
AND ppf.person_id = paf.person_id
AND p_effective_date BETWEEN ppf.effective_start_date
AND ppf.effective_end_date
AND pps.person_id = ppf.person_id
AND nvl(pps.actual_termination_date,p_effective_date) >= p_effective_date;
SELECT nvl(probability_member_id,-99)
INTO l_probability_member_id
FROM pa_projects_all
WHERE project_id = p_project_id;
SELECT nvl(probability_percentage,100)
INTO x_prob_percent
FROM pa_probability_members
WHERE probability_member_id = l_probability_member_id;
SELECT ppp.period_profile_id
INTO x_period_profile_id
FROM pa_proj_period_profiles ppp
WHERE ppp.project_id = p_project_id
AND ppp.period_profile_type = p_period_profile_type
AND ppp.plan_period_type = p_plan_period_type
AND ppp.period_set_name = p_period_set_name
AND ppp.gl_period_type = p_act_period_type
AND ppp.period1_start_date = p_start_date
AND ppp.number_of_periods = p_number_of_periods;
SELECT 'Y'
INTO l_org_proj_template_exists
FROM pa_forecasting_options
WHERE org_fcst_project_template_id = p_project_id;
SELECT Tp_Amt_Type_Code
INTO x_tp_amount_type
FROM Pa_Work_Types_B
WHERE Work_Type_Id = p_work_type_id;
SELECT NVL(Org_Id,-99)
INTO l_org_id
FROM Pa_Projects_All
WHERE Project_Id = p_project_id;
SELECT Default_Tp_Amount_Type
INTO x_tp_amount_type
FROM Pa_Forecasting_Options_All
WHERE nvl(Org_Id,-99) = l_org_id;
SELECT 'Y'
INTO l_org_project_exists
FROM pa_projects pp,
pa_project_types pt
WHERE pp.project_id = p_project_id
AND pt.project_type = pp.project_type
AND pt.org_project_flag = 'Y';
SELECT
(
sum(decode(amount_subtype_code,'TP_REVENUE_OUT',nvl(pppd.period_amount1,0)*-1,
'TP_COST_OUT', nvl(pppd.period_amount1,0)*-1,
nvl(pppd.period_amount1,0)
)
)+
sum(decode(amount_subtype_code,'TP_REVENUE_OUT',nvl(pppd.period_amount2,0)*-1,
'TP_COST_OUT', nvl(pppd.period_amount2,0)*-1,
nvl(pppd.period_amount2,0)
)
)+
sum(decode(amount_subtype_code,'TP_REVENUE_OUT',nvl(pppd.period_amount3,0)*-1,
'TP_COST_OUT', nvl(pppd.period_amount3,0)*-1,
nvl(pppd.period_amount3,0)
)
)+
sum(decode(amount_subtype_code,'TP_REVENUE_OUT',nvl(pppd.period_amount4,0)*-1,
'TP_COST_OUT', nvl(pppd.period_amount4,0)*-1,
nvl(pppd.period_amount4,0)
)
)+
sum(decode(amount_subtype_code,'TP_REVENUE_OUT',nvl(pppd.period_amount5,0)*-1,
'TP_COST_OUT', nvl(pppd.period_amount5,0)*-1,
nvl(pppd.period_amount5,0)
)
)+
sum(decode(amount_subtype_code,'TP_REVENUE_OUT',nvl(pppd.period_amount6,0)*-1,
'TP_COST_OUT', nvl(pppd.period_amount6,0)*-1,
nvl(pppd.period_amount6,0)
)
)
) as Total
INTO revenue
FROM pa_resource_assignments pra,
pa_budget_versions pbv,
pa_fp_period_values_v pppd
WHERE pbv.budget_version_id = pa_fin_plan_view_global.Get_Version_ID()
AND pra.project_id = pbv.project_id
AND pra.budget_version_id=pa_fin_plan_view_global.Get_Version_ID()
AND pra.resource_assignment_type='PROJECT'
AND pppd.budget_version_id=pa_fin_plan_view_global.Get_Version_ID()
AND pppd.resource_assignment_id=pra.resource_assignment_id
AND pppd.amount_type_code = 'REVENUE'
ORDER BY pppd.amount_type_id;
SELECT
(
sum(decode(amount_subtype_code,'TP_REVENUE_OUT',nvl(pppd.period_amount1,0)*-1,
'TP_COST_OUT', nvl(pppd.period_amount1,0)*-1,
nvl(pppd.period_amount1,0)
)
)+
sum(decode(amount_subtype_code,'TP_REVENUE_OUT',nvl(pppd.period_amount2,0)*-1,
'TP_COST_OUT', nvl(pppd.period_amount2,0)*-1,
nvl(pppd.period_amount2,0)
)
)+
sum(decode(amount_subtype_code,'TP_REVENUE_OUT',nvl(pppd.period_amount3,0)*-1,
'TP_COST_OUT', nvl(pppd.period_amount3,0)*-1,
nvl(pppd.period_amount3,0)
)
)+
sum(decode(amount_subtype_code,'TP_REVENUE_OUT',nvl(pppd.period_amount4,0)*-1,
'TP_COST_OUT', nvl(pppd.period_amount4,0)*-1,
nvl(pppd.period_amount4,0)
)
)+
sum(decode(amount_subtype_code,'TP_REVENUE_OUT',nvl(pppd.period_amount5,0)*-1,
'TP_COST_OUT', nvl(pppd.period_amount5,0)*-1,
nvl(pppd.period_amount5,0)
)
)+
sum(decode(amount_subtype_code,'TP_REVENUE_OUT',nvl(pppd.period_amount6,0)*-1,
'TP_COST_OUT', nvl(pppd.period_amount6,0)*-1,
nvl(pppd.period_amount6,0)
)
)
) as Total
INTO cost
FROM pa_resource_assignments pra,
pa_budget_versions pbv,
pa_fp_period_values_v pppd
WHERE pbv.budget_version_id = pa_fin_plan_view_global.Get_Version_ID()
AND pra.project_id = pbv.project_id
AND pra.budget_version_id=pa_fin_plan_view_global.Get_Version_ID()
AND pra.resource_assignment_type='PROJECT'
AND pppd.budget_version_id=pa_fin_plan_view_global.Get_Version_ID()
AND pppd.resource_assignment_id=pra.resource_assignment_id
AND pppd.amount_type_code = 'COST'
ORDER BY pppd.amount_type_id;
SELECT
round((sum(nvl(pppd.period_amount1,0))+
sum(nvl(pppd.period_amount2,0))+
sum(nvl(pppd.period_amount3,0))+
sum(nvl(pppd.period_amount4,0))+
sum(nvl(pppd.period_amount5,0))+
sum(nvl(pppd.period_amount6,0)))/6,0)
INTO headcount
FROM pa_resource_assignments pra,
pa_budget_versions pbv,
pa_fp_period_values_v pppd
WHERE pbv.budget_version_id = pa_fin_plan_view_global.Get_Version_ID()
AND pra.project_id = pbv.project_id
AND pra.budget_version_id=pa_fin_plan_view_global.Get_Version_ID()
AND pra.resource_assignment_type='PROJECT'
AND pppd.budget_version_id=pa_fin_plan_view_global.Get_Version_ID()
AND pppd.resource_assignment_id=pra.resource_assignment_id
AND pppd.amount_type_code = 'HEADCOUNT'
ORDER BY pppd.amount_type_id;
SELECT
round((nvl(pppd.period_amount1,0)+
nvl(pppd.period_amount2,0)+
nvl(pppd.period_amount3,0)+
nvl(pppd.period_amount4,0)+
nvl(pppd.period_amount5,0)+
nvl(pppd.period_amount6,0))/6,0)
INTO headcount
FROM pa_resource_assignments pra,
pa_budget_versions pbv,
pa_fp_period_values_v pppd
WHERE pbv.budget_version_id = pa_fin_plan_view_global.Get_Version_ID()
AND pra.project_id = pbv.project_id
AND pra.budget_version_id=pa_fin_plan_view_global.Get_Version_ID()
AND pra.resource_assignment_type='PROJECT'
AND pppd.budget_version_id=pa_fin_plan_view_global.Get_Version_ID()
AND pppd.resource_assignment_id=pra.resource_assignment_id
AND pppd.object_type_code = 'RES_ASSIGNMENT'
AND pppd.object_id = pra.resource_assignment_id
AND pppd.amount_subtype_code = 'HEADCOUNT_ADJUSTMENTS';
SELECT
round((nvl(pppd.period_amount1,0)+
nvl(pppd.period_amount2,0)+
nvl(pppd.period_amount3,0)+
nvl(pppd.period_amount4,0)+
nvl(pppd.period_amount5,0)+
nvl(pppd.period_amount6,0))/6,0)
INTO headcount
FROM pa_resource_assignments pra,
pa_budget_versions pbv,
pa_fp_period_values_v pppd
WHERE pbv.budget_version_id = pa_fin_plan_view_global.Get_Version_ID()
AND pra.project_id = pbv.project_id
AND pra.budget_version_id=pa_fin_plan_view_global.Get_Version_ID()
AND pra.resource_assignment_type='PROJECT'
AND pppd.budget_version_id=pa_fin_plan_view_global.Get_Version_ID()
AND pppd.resource_assignment_id=pra.resource_assignment_id
AND pppd.amount_subtype_code = 'BEGIN_HEADCOUNT';
SELECT
(nvl(pppd.period_amount1,0)+
nvl(pppd.period_amount2,0)+
nvl(pppd.period_amount3,0)+
nvl(pppd.period_amount4,0)+
nvl(pppd.period_amount5,0)+
nvl(pppd.period_amount6,0))/6
INTO util_percent
FROM pa_resource_assignments pra,
pa_budget_versions pbv,
pa_fp_period_values_v pppd
WHERE pbv.budget_version_id = pa_fin_plan_view_global.Get_Version_ID()
AND pra.project_id = pbv.project_id
AND pra.budget_version_id=pa_fin_plan_view_global.Get_Version_ID()
AND pra.resource_assignment_type='PROJECT'
AND pppd.budget_version_id=pa_fin_plan_view_global.Get_Version_ID()
AND pppd.resource_assignment_id=pra.resource_assignment_id
AND pppd.amount_subtype_code = 'UTILIZATION_PERCENT';
SELECT
(nvl(pppd.period_amount1,0)+
nvl(pppd.period_amount2,0)+
nvl(pppd.period_amount3,0)+
nvl(pppd.period_amount4,0)+
nvl(pppd.period_amount5,0)+
nvl(pppd.period_amount6,0))/6
INTO util_percent
FROM pa_resource_assignments pra,
pa_budget_versions pbv,
pa_fp_period_values_v pppd
WHERE pbv.budget_version_id = pa_fin_plan_view_global.Get_Version_ID()
AND pra.project_id = pbv.project_id
AND pra.budget_version_id=pa_fin_plan_view_global.Get_Version_ID()
AND pra.resource_assignment_type='PROJECT'
AND pppd.budget_version_id=pa_fin_plan_view_global.Get_Version_ID()
AND pppd.resource_assignment_id=pra.resource_assignment_id
AND pppd.object_type_code = 'RES_ASSIGNMENT'
AND pppd.object_id = pra.resource_assignment_id
AND pppd.amount_subtype_code = 'UTILIZATION_ADJUSTMENTS';
SELECT
(sum(nvl(pppd.period_amount1,0))+
sum(nvl(pppd.period_amount2,0))+
sum(nvl(pppd.period_amount3,0))+
sum(nvl(pppd.period_amount4,0))+
sum(nvl(pppd.period_amount5,0))+
sum(nvl(pppd.period_amount6,0)))/6
INTO util_percent
FROM pa_resource_assignments pra,
pa_budget_versions pbv,
pa_fp_period_values_v pppd
WHERE pbv.budget_version_id = pa_fin_plan_view_global.Get_Version_ID()
AND pra.project_id = pbv.project_id
AND pra.budget_version_id=pa_fin_plan_view_global.Get_Version_ID()
AND pra.resource_assignment_type='PROJECT'
AND pppd.budget_version_id=pa_fin_plan_view_global.Get_Version_ID()
AND pppd.resource_assignment_id=pra.resource_assignment_id
AND pppd.amount_type_code = 'UTILIZATION';
SELECT
(
sum(decode(amount_subtype_code,'TP_REVENUE_OUT',nvl(pppd.period_amount1,0)*-1,
'TP_COST_OUT', nvl(pppd.period_amount1,0)*-1,
nvl(pppd.period_amount1,0)
)
)+
sum(decode(amount_subtype_code,'TP_REVENUE_OUT',nvl(pppd.period_amount2,0)*-1,
'TP_COST_OUT', nvl(pppd.period_amount2,0)*-1,
nvl(pppd.period_amount2,0)
)
)+
sum(decode(amount_subtype_code,'TP_REVENUE_OUT',nvl(pppd.period_amount3,0)*-1,
'TP_COST_OUT', nvl(pppd.period_amount3,0)*-1,
nvl(pppd.period_amount3,0)
)
)+
sum(decode(amount_subtype_code,'TP_REVENUE_OUT',nvl(pppd.period_amount4,0)*-1,
'TP_COST_OUT', nvl(pppd.period_amount4,0)*-1,
nvl(pppd.period_amount4,0)
)
)+
sum(decode(amount_subtype_code,'TP_REVENUE_OUT',nvl(pppd.period_amount5,0)*-1,
'TP_COST_OUT', nvl(pppd.period_amount5,0)*-1,
nvl(pppd.period_amount5,0)
)
)+
sum(decode(amount_subtype_code,'TP_REVENUE_OUT',nvl(pppd.period_amount6,0)*-1,
'TP_COST_OUT', nvl(pppd.period_amount6,0)*-1,
nvl(pppd.period_amount6,0)
)
)+
sum(decode(amount_subtype_code,'TP_REVENUE_OUT',nvl(pppd.period_amount7,0)*-1,
'TP_COST_OUT', nvl(pppd.period_amount7,0)*-1,
nvl(pppd.period_amount7,0)
)
)+
sum(decode(amount_subtype_code,'TP_REVENUE_OUT',nvl(pppd.period_amount8,0)*-1,
'TP_COST_OUT', nvl(pppd.period_amount8,0)*-1,
nvl(pppd.period_amount8,0)
)
)+
sum(decode(amount_subtype_code,'TP_REVENUE_OUT',nvl(pppd.period_amount9,0)*-1,
'TP_COST_OUT', nvl(pppd.period_amount9,0)*-1,
nvl(pppd.period_amount9,0)
)
)+
sum(decode(amount_subtype_code,'TP_REVENUE_OUT',nvl(pppd.period_amount10,0)*-1,
'TP_COST_OUT', nvl(pppd.period_amount10,0)*-1,
nvl(pppd.period_amount10,0)
)
)+
sum(decode(amount_subtype_code,'TP_REVENUE_OUT',nvl(pppd.period_amount11,0)*-1,
'TP_COST_OUT', nvl(pppd.period_amount11,0)*-1,
nvl(pppd.period_amount11,0)
)
)+
sum(decode(amount_subtype_code,'TP_REVENUE_OUT',nvl(pppd.period_amount12,0)*-1,
'TP_COST_OUT', nvl(pppd.period_amount12,0)*-1,
nvl(pppd.period_amount12,0)
)
)+
sum(decode(amount_subtype_code,'TP_REVENUE_OUT',nvl(pppd.period_amount13,0)*-1,
'TP_COST_OUT', nvl(pppd.period_amount13,0)*-1,
nvl(pppd.period_amount13,0)
)
)
)
INTO revenue
FROM pa_resource_assignments pra,
pa_budget_versions pbv,
pa_fp_period_values_v pppd
WHERE pbv.budget_version_id = pa_fin_plan_view_global.Get_Version_ID()
AND pra.project_id = pbv.project_id
AND pra.budget_version_id=pa_fin_plan_view_global.Get_Version_ID()
AND pra.resource_assignment_type='PROJECT'
AND pppd.budget_version_id=pa_fin_plan_view_global.Get_Version_ID()
AND pppd.resource_assignment_id=pra.resource_assignment_id
AND pppd.amount_type_code = 'REVENUE'
ORDER BY pppd.amount_type_id;
SELECT
(
sum(decode(amount_subtype_code,'TP_REVENUE_OUT',nvl(pppd.period_amount1,0)*-1,
'TP_COST_OUT', nvl(pppd.period_amount1,0)*-1,
nvl(pppd.period_amount1,0)
)
)+
sum(decode(amount_subtype_code,'TP_REVENUE_OUT',nvl(pppd.period_amount2,0)*-1,
'TP_COST_OUT', nvl(pppd.period_amount2,0)*-1,
nvl(pppd.period_amount2,0)
)
)+
sum(decode(amount_subtype_code,'TP_REVENUE_OUT',nvl(pppd.period_amount3,0)*-1,
'TP_COST_OUT', nvl(pppd.period_amount3,0)*-1,
nvl(pppd.period_amount3,0)
)
)+
sum(decode(amount_subtype_code,'TP_REVENUE_OUT',nvl(pppd.period_amount4,0)*-1,
'TP_COST_OUT', nvl(pppd.period_amount4,0)*-1,
nvl(pppd.period_amount4,0)
)
)+
sum(decode(amount_subtype_code,'TP_REVENUE_OUT',nvl(pppd.period_amount5,0)*-1,
'TP_COST_OUT', nvl(pppd.period_amount5,0)*-1,
nvl(pppd.period_amount5,0)
)
)+
sum(decode(amount_subtype_code,'TP_REVENUE_OUT',nvl(pppd.period_amount6,0)*-1,
'TP_COST_OUT', nvl(pppd.period_amount6,0)*-1,
nvl(pppd.period_amount6,0)
)
)+
sum(decode(amount_subtype_code,'TP_REVENUE_OUT',nvl(pppd.period_amount7,0)*-1,
'TP_COST_OUT', nvl(pppd.period_amount7,0)*-1,
nvl(pppd.period_amount7,0)
)
)+
sum(decode(amount_subtype_code,'TP_REVENUE_OUT',nvl(pppd.period_amount8,0)*-1,
'TP_COST_OUT', nvl(pppd.period_amount8,0)*-1,
nvl(pppd.period_amount8,0)
)
)+
sum(decode(amount_subtype_code,'TP_REVENUE_OUT',nvl(pppd.period_amount9,0)*-1,
'TP_COST_OUT', nvl(pppd.period_amount9,0)*-1,
nvl(pppd.period_amount9,0)
)
)+
sum(decode(amount_subtype_code,'TP_REVENUE_OUT',nvl(pppd.period_amount10,0)*-1,
'TP_COST_OUT', nvl(pppd.period_amount10,0)*-1,
nvl(pppd.period_amount10,0)
)
)+
sum(decode(amount_subtype_code,'TP_REVENUE_OUT',nvl(pppd.period_amount11,0)*-1,
'TP_COST_OUT', nvl(pppd.period_amount11,0)*-1,
nvl(pppd.period_amount11,0)
)
)+
sum(decode(amount_subtype_code,'TP_REVENUE_OUT',nvl(pppd.period_amount12,0)*-1,
'TP_COST_OUT', nvl(pppd.period_amount12,0)*-1,
nvl(pppd.period_amount12,0)
)
)+
sum(decode(amount_subtype_code,'TP_REVENUE_OUT',nvl(pppd.period_amount13,0)*-1,
'TP_COST_OUT', nvl(pppd.period_amount13,0)*-1,
nvl(pppd.period_amount13,0)
)
)
)
INTO cost
FROM pa_resource_assignments pra,
pa_budget_versions pbv,
pa_fp_period_values_v pppd
WHERE pbv.budget_version_id = pa_fin_plan_view_global.Get_Version_ID()
AND pra.project_id = pbv.project_id
AND pra.budget_version_id=pa_fin_plan_view_global.Get_Version_ID()
AND pra.resource_assignment_type='PROJECT'
AND pppd.budget_version_id=pa_fin_plan_view_global.Get_Version_ID()
AND pppd.resource_assignment_id=pra.resource_assignment_id
AND pppd.amount_type_code = 'COST'
ORDER BY pppd.amount_type_id;
SELECT
round((sum(nvl(pppd.period_amount1,0))+
sum(nvl(pppd.period_amount2,0))+
sum(nvl(pppd.period_amount3,0))+
sum(nvl(pppd.period_amount4,0))+
sum(nvl(pppd.period_amount5,0))+
sum(nvl(pppd.period_amount6,0))+
sum(nvl(pppd.period_amount7,0))+
sum(nvl(pppd.period_amount8,0))+
sum(nvl(pppd.period_amount9,0))+
sum(nvl(pppd.period_amount10,0))+
sum(nvl(pppd.period_amount11,0))+
sum(nvl(pppd.period_amount12,0))+
sum(nvl(pppd.period_amount13,0)))/13,0)
INTO headcount
FROM pa_resource_assignments pra,
pa_budget_versions pbv,
pa_fp_period_values_v pppd
WHERE pbv.budget_version_id = pa_fin_plan_view_global.Get_Version_ID()
AND pra.project_id = pbv.project_id
AND pra.budget_version_id=pa_fin_plan_view_global.Get_Version_ID()
AND pra.resource_assignment_type='PROJECT'
AND pppd.budget_version_id=pa_fin_plan_view_global.Get_Version_ID()
AND pppd.resource_assignment_id=pra.resource_assignment_id
AND pppd.amount_type_code = 'HEADCOUNT';
SELECT
round((nvl(pppd.period_amount1,0)+
nvl(pppd.period_amount2,0)+
nvl(pppd.period_amount3,0)+
nvl(pppd.period_amount4,0)+
nvl(pppd.period_amount5,0)+
nvl(pppd.period_amount6,0)+
nvl(pppd.period_amount7,0)+
nvl(pppd.period_amount8,0)+
nvl(pppd.period_amount9,0)+
nvl(pppd.period_amount10,0)+
nvl(pppd.period_amount11,0)+
nvl(pppd.period_amount12,0)+
nvl(pppd.period_amount13,0))/13,0)
INTO headcount
FROM pa_resource_assignments pra,
pa_budget_versions pbv,
pa_fp_period_values_v pppd
WHERE pbv.budget_version_id = pa_fin_plan_view_global.Get_Version_ID()
AND pra.project_id = pbv.project_id
AND pra.budget_version_id=pa_fin_plan_view_global.Get_Version_ID()
AND pra.resource_assignment_type='PROJECT'
AND pppd.budget_version_id=pa_fin_plan_view_global.Get_Version_ID()
AND pppd.object_type_code = 'RES_ASSIGNMENT'
AND pppd.object_id = pra.resource_assignment_id
AND pppd.resource_assignment_id=pra.resource_assignment_id
AND pppd.amount_subtype_code = 'HEADCOUNT_ADJUSTMENTS';
SELECT
round((nvl(pppd.period_amount1,0)+
nvl(pppd.period_amount2,0)+
nvl(pppd.period_amount3,0)+
nvl(pppd.period_amount4,0)+
nvl(pppd.period_amount5,0)+
nvl(pppd.period_amount6,0)+
nvl(pppd.period_amount7,0)+
nvl(pppd.period_amount8,0)+
nvl(pppd.period_amount9,0)+
nvl(pppd.period_amount10,0)+
nvl(pppd.period_amount11,0)+
nvl(pppd.period_amount12,0)+
nvl(pppd.period_amount13,0))/13,0)
INTO headcount
FROM pa_resource_assignments pra,
pa_budget_versions pbv,
pa_fp_period_values_v pppd
WHERE pbv.budget_version_id = pa_fin_plan_view_global.Get_Version_ID()
AND pra.project_id = pbv.project_id
AND pra.budget_version_id=pa_fin_plan_view_global.Get_Version_ID()
AND pra.resource_assignment_type='PROJECT'
AND pppd.budget_version_id=pa_fin_plan_view_global.Get_Version_ID()
AND pppd.resource_assignment_id=pra.resource_assignment_id
AND pppd.amount_subtype_code = 'BEGIN_HEADCOUNT'
ORDER BY pppd.amount_type_id;
SELECT
(nvl(pppd.period_amount1,0)+
nvl(pppd.period_amount2,0)+
nvl(pppd.period_amount3,0)+
nvl(pppd.period_amount4,0)+
nvl(pppd.period_amount5,0)+
nvl(pppd.period_amount6,0)+
nvl(pppd.period_amount7,0)+
nvl(pppd.period_amount8,0)+
nvl(pppd.period_amount9,0)+
nvl(pppd.period_amount10,0)+
nvl(pppd.period_amount11,0)+
nvl(pppd.period_amount12,0)+
nvl(pppd.period_amount13,0)) /13
INTO util_percent
FROM pa_resource_assignments pra,
pa_budget_versions pbv,
pa_fp_period_values_v pppd
WHERE pbv.budget_version_id = pa_fin_plan_view_global.Get_Version_ID()
AND pra.project_id = pbv.project_id
AND pra.budget_version_id=pa_fin_plan_view_global.Get_Version_ID()
AND pra.resource_assignment_type='PROJECT'
AND pppd.budget_version_id=pa_fin_plan_view_global.Get_Version_ID()
AND pppd.resource_assignment_id=pra.resource_assignment_id
AND pppd.amount_subtype_code = 'UTILIZATION_PERCENT'
ORDER BY pppd.amount_type_id;
SELECT
(nvl(pppd.period_amount1,0)+
nvl(pppd.period_amount2,0)+
nvl(pppd.period_amount3,0)+
nvl(pppd.period_amount4,0)+
nvl(pppd.period_amount5,0)+
nvl(pppd.period_amount6,0)+
nvl(pppd.period_amount7,0)+
nvl(pppd.period_amount8,0)+
nvl(pppd.period_amount9,0)+
nvl(pppd.period_amount10,0)+
nvl(pppd.period_amount11,0)+
nvl(pppd.period_amount12,0)+
nvl(pppd.period_amount13,0)) /13
INTO util_percent
FROM pa_resource_assignments pra,
pa_budget_versions pbv,
pa_fp_period_values_v pppd
WHERE pbv.budget_version_id = pa_fin_plan_view_global.Get_Version_ID()
AND pra.project_id = pbv.project_id
AND pra.budget_version_id=pa_fin_plan_view_global.Get_Version_ID()
AND pra.resource_assignment_type='PROJECT'
AND pppd.budget_version_id=pa_fin_plan_view_global.Get_Version_ID()
AND pppd.resource_assignment_id=pra.resource_assignment_id
AND pppd.object_type_code = 'RES_ASSIGNMENT'
AND pppd.object_id = pra.resource_assignment_id
AND pppd.amount_subtype_code = 'UTILIZATION_ADJUSTMENTS';
SELECT
(sum(nvl(pppd.period_amount1,0))+
sum(nvl(pppd.period_amount2,0))+
sum(nvl(pppd.period_amount3,0))+
sum(nvl(pppd.period_amount4,0))+
sum(nvl(pppd.period_amount5,0))+
sum(nvl(pppd.period_amount6,0))+
sum(nvl(pppd.period_amount7,0))+
sum(nvl(pppd.period_amount8,0))+
sum(nvl(pppd.period_amount9,0))+
sum(nvl(pppd.period_amount10,0))+
sum(nvl(pppd.period_amount11,0))+
sum(nvl(pppd.period_amount12,0))+
sum(nvl(pppd.period_amount13,0))) /13
INTO util_percent
FROM pa_resource_assignments pra,
pa_budget_versions pbv,
pa_fp_period_values_v pppd
WHERE pbv.budget_version_id = pa_fin_plan_view_global.Get_Version_ID()
AND pra.project_id = pbv.project_id
AND pra.budget_version_id=pa_fin_plan_view_global.Get_Version_ID()
AND pra.resource_assignment_type='PROJECT'
AND pppd.budget_version_id=pa_fin_plan_view_global.Get_Version_ID()
AND pppd.resource_assignment_id=pra.resource_assignment_id
AND pppd.amount_type_code = 'UTILIZATION';
select org_id, USERENV('CLIENT_INFO')
into l_project_org_id, l_login_org_id_data
from pa_projects_all
where
project_id=p_project_id;
SELECT 'Y'
INTO l_org_project_exists
FROM pa_projects_all pp,
pa_project_types_all pt
WHERE pp.project_id = p_project_id
AND pt.project_type = pp.project_type
AND pp.org_id = pt.org_id
AND pt.org_project_flag = 'Y';