[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
selected and returns the budget version id and the URL */
/* 07/13/2005 dlai - added parameter p_same_org_id_flag for R12 MOAC effort to remove
* dependency on pa_fp_org_fcst_utils.same_org_id
*/
PROCEDURE identify_plan_version_id(
p_project_id IN pa_projects_all.project_id%TYPE,
p_function_code IN VARCHAR2,
p_context IN VARCHAR2 DEFAULT NULL,
p_user_id IN NUMBER,
p_same_org_id_flag IN VARCHAR2, -- Bug 5276024: Making this field mandatory -- DEFAULT 'N',
px_fin_plan_type_id IN OUT NOCOPY pa_fin_plan_types_b.fin_plan_type_id%TYPE, --File.Sql.39 bug 4440895
x_budget_version_id OUT NOCOPY pa_budget_versions.budget_version_id%TYPE, --File.Sql.39 bug 4440895
x_redirect_url OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
x_request_id OUT NOCOPY pa_budget_versions.request_id%TYPE, --File.Sql.39 bug 4440895
x_plan_processing_code OUT NOCOPY pa_budget_versions.plan_processing_code%TYPE, --File.Sql.39 bug 4440895
x_proj_fp_option_id OUT NOCOPY pa_proj_fp_options.proj_fp_options_id%TYPE, --File.Sql.39 bug 4440895
x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
x_msg_data OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
l_version_type pa_budget_versions.version_type%type;
|| '&pMsg=PA_CROSSOU_NO_UPDATE';
shortcut option is selected in Project Home page, then error should be
raised in the Budgets and Forecasts page as editing the Revenue version is
not allowed for these types or projects. */
l_baseline_funding_flag := 'N';
SELECT NVL(Baseline_Funding_Flag,'N')
INTO
l_baseline_funding_flag
FROM
Pa_Projects_All WHERE Project_Id = p_project_id;
SELECT NVL(approved_rev_plan_type_flag,'N')
INTO l_approved_rev_plan_type_flag
FROM pa_proj_fp_options
WHERE Project_Id = p_project_id
AND fin_plan_type_id = px_fin_plan_type_id
AND fin_plan_version_id IS NULL
AND fin_plan_option_level_code = 'PLAN_TYPE';
SELECT po.fin_plan_preference_code INTO l_fin_plan_preference_code
FROM pa_proj_fp_options po
WHERE po.project_id = p_project_id AND
po.fin_plan_option_level_code = 'PLAN_TYPE' AND
po.fin_plan_type_id = px_fin_plan_type_id;
SELECT fin_plan_preference_code
INTO l_temp_pref_code
FROM pa_proj_fp_options
WHERE project_id = p_project_id
AND fin_plan_type_id = l_cost_fin_plan_type_id
AND fin_plan_option_level_code = PA_FP_CONSTANTS_PKG.G_OPTION_LEVEL_PLAN_TYPE;
option selected in the Project Home. If the approved budget plan type does not
exist, then the URL will be returned with the error message and the information
for page. */
PROCEDURE get_app_budget_pt_id(
p_project_id IN pa_projects_all.project_id%TYPE,
p_version_type IN pa_budget_versions.version_type%TYPE,
p_context IN VARCHAR2,
p_function_code IN VARCHAR2 DEFAULT NULL,
x_fin_plan_type_id OUT NOCOPY pa_fin_plan_types_b.fin_plan_type_id%TYPE, --File.Sql.39 bug 4440895
x_redirect_url OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
x_msg_data OUT NOCOPY VARCHAR2 ) IS --File.Sql.39 bug 4440895
CURSOR approved_budget_csr IS
SELECT pt.fin_plan_type_id,
po.fin_plan_preference_code
FROM pa_proj_fp_options po,
pa_fin_plan_types_b pt
WHERE po.project_id = p_project_id AND
po.fin_plan_option_level_code = 'PLAN_TYPE' AND
po.fin_plan_type_id = pt.fin_plan_type_id AND
(pt.approved_cost_plan_type_flag = 'Y' OR
pt.approved_rev_plan_type_flag = 'Y');
SELECT pt.fin_plan_type_id,
po.fin_plan_preference_code
FROM pa_proj_fp_options po,
pa_fin_plan_types_b pt
WHERE po.project_id = p_project_id AND
po.fin_plan_option_level_code = 'PLAN_TYPE' AND
po.fin_plan_type_id = pt.fin_plan_type_id AND
po.fin_plan_preference_code <> 'REVENUE_ONLY' AND
pt.approved_cost_plan_type_flag = 'Y';
SELECT pt.fin_plan_type_id,
po.fin_plan_preference_code
FROM pa_proj_fp_options po,
pa_fin_plan_types_b pt
WHERE po.project_id = p_project_id AND
po.fin_plan_option_level_code = 'PLAN_TYPE' AND
po.fin_plan_type_id = pt.fin_plan_type_id AND
po.fin_plan_preference_code <> 'COST_ONLY' AND
pt.approved_rev_plan_type_flag = 'Y';
option selected in the Project Home. If the plan type does not exist, then
the URL will be returned with appropriate information. */
PROCEDURE get_fcst_plan_type_id(
p_project_id IN pa_projects_all.project_id%TYPE,
p_version_type IN pa_budget_versions.version_type%TYPE,
p_context IN VARCHAR2,
p_function_code IN VARCHAR2 DEFAULT NULL,
x_fin_plan_type_id OUT NOCOPY pa_fin_plan_types_b.fin_plan_type_id%TYPE, --File.Sql.39 bug 4440895
x_redirect_url OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
x_msg_data OUT NOCOPY VARCHAR2 ) IS --File.Sql.39 bug 4440895
CURSOR primary_budget_csr IS
SELECT pt.fin_plan_type_id,
po.fin_plan_preference_code
FROM pa_proj_fp_options po,
pa_fin_plan_types_b pt
WHERE po.project_id = p_project_id AND
po.fin_plan_option_level_code = 'PLAN_TYPE' AND
po.fin_plan_type_id = pt.fin_plan_type_id AND
(pt.primary_cost_forecast_flag = 'Y' OR
pt.primary_rev_forecast_flag = 'Y');
SELECT pt.fin_plan_type_id,
po.fin_plan_preference_code
FROM pa_proj_fp_options po,
pa_fin_plan_types_b pt
WHERE po.project_id = p_project_id AND
po.fin_plan_option_level_code = 'PLAN_TYPE' AND
po.fin_plan_type_id = pt.fin_plan_type_id AND
po.fin_plan_preference_code <> 'REVENUE_ONLY' AND
pt.primary_cost_forecast_flag = 'Y';
SELECT pt.fin_plan_type_id,
po.fin_plan_preference_code
FROM pa_proj_fp_options po,
pa_fin_plan_types_b pt
WHERE po.project_id = p_project_id AND
po.fin_plan_option_level_code = 'PLAN_TYPE' AND
po.fin_plan_type_id = pt.fin_plan_type_id AND
po.fin_plan_preference_code <> 'COST_ONLY' AND
pt.primary_rev_forecast_flag = 'Y';
/* l_process_wbs_flag pa_budget_versions.process_update_wbs_flag%TYPE; * 3604167 */
SELECT fin_plan_preference_code,
proj_fp_options_id
INTO l_fin_plan_preference_code,l_fp_opt_id
FROM pa_proj_fp_options
WHERE
project_id = p_project_id AND
fin_plan_option_level_code = 'PLAN_TYPE' AND
fin_plan_type_id = p_fin_plan_type_id;
x_msg => 'selecting the CW version',
x_log_level => 3);
Select bv.budget_Version_id,
bv.locked_by_person_id,bv.budget_status_code,record_version_number,bv.request_id,bv.plan_processing_code
-- nvl(bv.process_update_wbs_flag,'N')
INTO x_budget_version_id,
l_locked_by_person_id,
l_budget_status_code,
l_rec_ver_number,
x_request_id,
x_plan_processing_code
-- l_process_wbs_flag
FROM pa_budget_versions bv
WHERE project_id = p_project_id AND
fin_plan_type_id = p_fin_plan_type_id AND
current_working_Flag ='Y' AND
version_type IN (p_version_type,'ALL');
NOTE: use locked_by_person_id=-98 instead of update_wbs_flag=Y so that this
will catch ALL concurrent process in progress scenarios */
if l_locked_by_person_id = -98 then
/* setting url for Edit Plan page */
x_redirect_url := 'OA.jsp?page=/oracle/apps/pa/finplan/webui/FpEditPlanPG'
|| '&paBvId=' || x_budget_version_id
|| '&paContextLevel=VERSION'
|| '&pMsg=-1';