[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
CREATE_FP_OPTION: This procedure inserts or updates records in 3 table depending
on the Source and the Target FP Option Details passed to this procedure.
-> If the Source and Target are passed, then the Target FP Option is updated with the Source
details.
this case if true in following cases
1. edit plan type. In this case user might change the preference code of the option.
2. copy from a existing version. All values except approved cost/revenue are to be overriden
3. While adding plan type to a project.
4. copy from an existing project.
-> If the Source is passed and Target not passed, then a new Target FP Option is created based
on other Source details.
-> If the Source is not passed and the Target is passed, then Source details are got from the
Parent (if exists) else Default option details are got using the Target Preference Code. A new
Target FP Option is created based on the details got.
-> If the Source and the Target are not passed, then the details of the Parent are got using the
Option Level Code else Default Option details are got using the Target Preference code.
BUG:- 2625872 As part of the Upgrade changes, create_fp_option api has been modified to set multi
currency flag to 'Y' if the project currency isn't equal to project functional currency.
Bug:- 2920954 calls to insert/update table handlers have been chnaged to include new columns.
-- 26-JUN-2003 jwhite - Plannable Task HQ Dev Effort:
-- Make code changes to Create_FP_Option procedure to
-- enable population of new parameters on
-- PA_PROJ_FP_OPTIONS_PKG.Insert_Row table handler.
--
r11.5 FP.M Developement ----------------------------------
08-JAN-2004 jwhite Bug 3362316 (HQ)
Rewrote Create_Fp_Option.
- FP_COL Record specifiation definition
- PA_PROJ_FP_OPTIONS_PKG.update_row parm list
- PA_PROJ_FP_OPTIONS_PKG.insert_row parm list
3/30/2004 Raja FP M Dev Effort Copy Project Impact:
When versions are being copied across projects genration source plan versions
can not be copied as they are. So, all gen source version id columns would be null
4/16/2004 Raja FP M Phase II Dev Effort Copy Plan:
When user chooses 'copy version amounts' from one version to another version, do not copy
"rate schedules" and "generation options" sub tabs related data.
==================================================================================================*/
PROCEDURE Create_FP_Option (
px_target_proj_fp_option_id IN OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
,p_source_proj_fp_option_id IN NUMBER
,p_target_fp_option_level_code IN VARCHAR2
,p_target_fp_preference_code IN VARCHAR2
,p_target_fin_plan_version_id IN NUMBER
,p_target_project_id IN NUMBER
,p_target_plan_type_id IN NUMBER
,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
FP_Cols_Rec PA_PROJ_FP_OPTIONS_PUB.FP_COLS;
SELECT plan_class_code
,nvl(approved_cost_plan_type_flag,'N') approved_cost_plan_type_flag
,nvl(approved_rev_plan_type_flag,'N') approved_rev_plan_type_flag
,nvl(primary_cost_forecast_flag,'N') primary_cost_forecast_flag
,nvl(primary_rev_forecast_flag,'N') primary_rev_forecast_flag
,nvl(use_for_workplan_flag,'N') use_for_workplan_flag
FROM pa_fin_plan_types_b
WHERE fin_plan_type_id = c_fin_plan_type_id;
SELECT
nvl(approved_rev_plan_type_flag,'N') approved_rev_plan_type_flag
FROM pa_proj_fp_options
where proj_fp_options_id = c_proj_fp_options_id;
SELECT ci_id
FROM pa_budget_versions
WHERE budget_version_id = c_budget_version_id;
to the Table Handlers PA_PROJ_FP_OPTIONS_PKG.update_row and insert_row. */
IF P_PA_DEBUG_MODE = 'Y' THEN
pa_debug.g_err_stage := TO_CHAR(l_Stage)||': target option id is not null. Populating from target';
SELECT fin_plan_type_id, fin_plan_version_id, fin_plan_option_level_code,
nvl(l_fp_preference_code, fin_plan_preference_code) /* get only if l_fp_preference_code is not null */
INTO l_plan_type_id, l_plan_version_id, l_target_option_level_code,
l_fp_preference_code
FROM pa_proj_fp_options
WHERE proj_fp_options_id = px_target_proj_fp_option_id;
a new record needs to be inserted into Proj FP Options based on the Target_FP_Pref_Code. Hence
Target_FP_Pref_Code should be NOT NULL for this case. */
l_stage := 360;
/* Selected l_source_fp_preference_code for Bug 3149010 */
SELECT nvl(l_fp_preference_code,fin_plan_preference_code),
fin_plan_preference_code,
fin_plan_option_level_code,
project_id,
pt.plan_class_code,
pfo.fin_plan_type_id
INTO l_fp_preference_code,
l_source_fp_preference_code,
l_source_option_level_code,
l_source_project_id,
l_source_plan_class_code,
l_source_plan_type_id -- Added this to get the source plan type id for copying the amount types
FROM pa_proj_fp_options pfo,
pa_fin_plan_types_b pt
WHERE pfo.proj_fp_options_id = p_source_proj_fp_option_id
AND pfo.fin_plan_type_id = pt.fin_plan_type_id(+);
SELECT NVL(baseline_funding_flag,'N')
,NVL(approved_rev_plan_type_flag,'N')
INTO l_baseline_funding_flag
,l_approved_rev_plan_type_flag
FROM pa_projects_all ppa
,pa_fin_plan_types_b ptb
WHERE ppa.project_id = p_target_project_id
AND ptb.fin_plan_type_id = p_target_plan_type_id;
FP_Cols_Rec.select_rev_res_auto_flag := 'N';
SELECT fin_plan_preference_code,
fin_plan_option_level_code,
pt.plan_class_code
INTO l_source_fp_preference_code,
l_source_option_level_code,
l_source_plan_class_code
FROM pa_proj_fp_options pfo,
pa_fin_plan_types_b pt
WHERE pfo.proj_fp_options_id = l_par_proj_fp_options_id
AND pfo.fin_plan_type_id = pt.fin_plan_type_id(+);
In this case the Target FP option details need to be updated with the
details of the Source FP Option. */
IF P_PA_DEBUG_MODE = 'Y' THEN
pa_debug.g_err_stage := TO_CHAR(l_Stage)||': Calling Table Handler to update row';
PA_PROJ_FP_OPTIONS_PKG.update_row
( p_proj_fp_options_id => px_target_proj_fp_option_id
,p_record_version_number => NULL
,p_project_id => p_target_project_id
,p_fin_plan_option_level_code => l_target_option_level_code
,p_fin_plan_type_id => l_plan_type_id
,p_fin_plan_start_date => FP_Cols_Rec.fin_plan_start_date
,p_fin_plan_end_date => FP_Cols_Rec.fin_plan_end_date
,p_fin_plan_preference_code => l_fp_preference_code
,p_cost_amount_set_id => FP_Cols_Rec.cost_amount_set_iD
,p_revenue_amount_set_id => FP_Cols_Rec.revenue_amount_set_id
,p_all_amount_set_id => FP_Cols_Rec.all_amount_set_id
,p_cost_fin_plan_level_code => FP_Cols_Rec.cost_fin_plan_level_code
,p_cost_time_phased_code => FP_Cols_Rec.cost_time_phased_code
,p_cost_resource_list_id => FP_Cols_Rec.cost_resource_list_id
,p_revenue_fin_plan_level_code => FP_Cols_Rec.revenue_fin_plan_level_code
,p_revenue_time_phased_code => FP_Cols_Rec.revenue_time_phased_code
,p_revenue_resource_list_id => FP_Cols_Rec.revenue_resource_list_id
,p_all_fin_plan_level_code => FP_Cols_Rec.all_fin_plan_level_code
,p_all_time_phased_code => FP_Cols_Rec.all_time_phased_code
,p_all_resource_list_id => FP_Cols_Rec.all_resource_list_id
,p_report_labor_hrs_from_code => FP_Cols_Rec.report_labor_hrs_from_code
,p_fin_plan_version_id => l_plan_version_id
,p_plan_in_multi_curr_flag => FP_Cols_Rec.plan_in_multi_curr_flag
,p_factor_by_code => FP_Cols_Rec.factor_by_code
,p_default_amount_type_code => FP_Cols_Rec.default_amount_type_code
,p_default_amount_subtype_code => FP_Cols_Rec.default_amount_subtype_code
,p_approved_cost_plan_type_flag => FP_Mc_Cols_Rec.approved_cost_plan_type_flag
,p_approved_rev_plan_type_flag => FP_Mc_Cols_Rec.approved_rev_plan_type_flag
,p_projfunc_cost_rate_type => FP_Mc_Cols_Rec.projfunc_cost_rate_type
,p_projfunc_cost_rate_date_type => FP_Mc_Cols_Rec.projfunc_cost_rate_date_type
,p_projfunc_cost_rate_date => FP_Mc_Cols_Rec.projfunc_cost_rate_date
,p_projfunc_rev_rate_type => FP_Mc_Cols_Rec.projfunc_rev_rate_type
,p_projfunc_rev_rate_date_type => FP_Mc_Cols_Rec.projfunc_rev_rate_date_type
,p_projfunc_rev_rate_date => FP_Mc_Cols_Rec.projfunc_rev_rate_date
,p_project_cost_rate_type => FP_Mc_Cols_Rec.project_cost_rate_type
,p_project_cost_rate_date_type => FP_Mc_Cols_Rec.project_cost_rate_date_type
,p_project_cost_rate_date => FP_Mc_Cols_Rec.project_cost_rate_date
,p_project_rev_rate_type => FP_Mc_Cols_Rec.project_rev_rate_type
,p_project_rev_rate_date_type => FP_Mc_Cols_Rec.project_rev_rate_date_type
,p_project_rev_rate_date => FP_Mc_Cols_Rec.project_rev_rate_date
,p_margin_derived_from_code => FP_Cols_Rec.margin_derived_from_code
/* Bug 2920954 start of additional parameters added for post fp_k oneoff*/
,p_select_cost_res_auto_flag => FP_Cols_Rec.select_cost_res_auto_flag
,p_cost_res_planning_level => FP_Cols_Rec.cost_res_planning_level
,p_select_rev_res_auto_flag => FP_Cols_Rec.select_rev_res_auto_flag
,p_revenue_res_planning_level => FP_Cols_Rec.revenue_res_planning_level
,p_select_all_res_auto_flag => FP_Cols_Rec.select_all_res_auto_flag
,p_all_res_planning_level => FP_Cols_Rec.all_res_planning_level
/* Bug 2920954 end of additional parameters added for post fp_k oneoff*/
,p_primary_cost_forecast_flag => FP_Mc_Cols_Rec.primary_cost_forecast_flag
,p_primary_rev_forecast_flag => FP_Mc_Cols_Rec.primary_rev_forecast_flag
,p_use_planning_rates_flag => FP_Cols_Rec.use_planning_rates_flag
,p_rbs_version_id => FP_Cols_Rec.rbs_version_id
,p_res_class_raw_cost_sch_id => FP_Cols_Rec.res_class_raw_cost_sch_id
,p_res_class_bill_rate_sch_id => FP_Cols_Rec.res_class_bill_rate_sch_id
,p_cost_emp_rate_sch_id => FP_Cols_Rec.cost_emp_rate_sch_id
,p_cost_job_rate_sch_id => FP_Cols_Rec.cost_job_rate_sch_id
,P_CST_NON_LABR_RES_RATE_SCH_ID => FP_Cols_Rec.cost_non_labor_res_rate_sch_id
,p_cost_res_class_rate_sch_id => FP_Cols_Rec.cost_res_class_rate_sch_id
,p_cost_burden_rate_sch_id => FP_Cols_Rec.cost_burden_rate_sch_id
,p_cost_current_planning_period => FP_Cols_Rec.cost_current_planning_period
,p_cost_period_mask_id => FP_Cols_Rec.cost_period_mask_id
,p_rev_emp_rate_sch_id => FP_Cols_Rec.rev_emp_rate_sch_id
,p_rev_job_rate_sch_id => FP_Cols_Rec.rev_job_rate_sch_id
,P_REV_NON_LABR_RES_RATE_SCH_ID => FP_Cols_Rec.rev_non_labor_res_rate_sch_id
,p_rev_res_class_rate_sch_id => FP_Cols_Rec.rev_res_class_rate_sch_id
,p_rev_current_planning_period => FP_Cols_Rec.rev_current_planning_period
,p_rev_period_mask_id => FP_Cols_Rec.rev_period_mask_id
/*** Bug 3580727
,p_all_emp_rate_sch_id => FP_Cols_Rec.all_emp_rate_sch_id
,p_all_job_rate_sch_id => FP_Cols_Rec.all_job_rate_sch_id
,P_ALL_NON_LABR_RES_RATE_SCH_ID => FP_Cols_Rec.all_non_labor_res_rate_sch_id
,p_all_res_class_rate_sch_id => FP_Cols_Rec.all_res_class_rate_sch_id
,p_all_burden_rate_sch_id => FP_Cols_Rec.all_burden_rate_sch_id
***/
,p_all_current_planning_period => FP_Cols_Rec.all_current_planning_period
,p_all_period_mask_id => FP_Cols_Rec.all_period_mask_id
,p_gen_cost_src_code => FP_Cols_Rec.gen_cost_src_code
,p_gen_cost_etc_src_code => FP_Cols_Rec.gen_cost_etc_src_code
,P_GN_COST_INCL_CHANGE_DOC_FLAG => FP_Cols_Rec.gen_cost_incl_change_doc_flag
,p_gen_cost_incl_open_comm_flag => FP_Cols_Rec.gen_cost_incl_open_comm_flag
,P_GN_COST_RET_MANUAL_LINE_FLAG => FP_Cols_Rec.gen_cost_ret_manual_line_flag
,P_GN_CST_INCL_UNSPENT_AMT_FLAG => FP_Cols_Rec.gen_cost_incl_unspent_amt_flag
,p_gen_rev_src_code => FP_Cols_Rec.gen_rev_src_code
,p_gen_rev_etc_src_code => FP_Cols_Rec.gen_rev_etc_src_code
,p_gen_rev_incl_change_doc_flag => FP_Cols_Rec.gen_rev_incl_change_doc_flag
,p_gen_rev_incl_bill_event_flag => FP_Cols_Rec.gen_rev_incl_bill_event_flag
,p_gen_rev_ret_manual_line_flag => FP_Cols_Rec.gen_rev_ret_manual_line_flag
/*** Bug 3580727
,P_GN_REV_INCL_UNSPENT_AMT_FLAG => FP_Cols_Rec.gen_rev_incl_unspent_amt_flag
***/
,p_gen_src_cost_plan_type_id => FP_Cols_Rec.gen_src_cost_plan_type_id
,p_gen_src_cost_plan_version_id => FP_Cols_Rec.gen_src_cost_plan_version_id
,p_gen_src_cost_plan_ver_code => FP_Cols_Rec.gen_src_cost_plan_ver_code
,p_gen_src_rev_plan_type_id => FP_Cols_Rec.gen_src_rev_plan_type_id
,p_gen_src_rev_plan_version_id => FP_Cols_Rec.gen_src_rev_plan_version_id
,p_gen_src_rev_plan_ver_code => FP_Cols_Rec.gen_src_rev_plan_ver_code
,p_gen_src_all_plan_type_id => FP_Cols_Rec.gen_src_all_plan_type_id
,p_gen_src_all_plan_version_id => FP_Cols_Rec.gen_src_all_plan_version_id
,p_gen_src_all_plan_ver_code => FP_Cols_Rec.gen_src_all_plan_ver_code
,p_gen_all_src_code => FP_Cols_Rec.gen_all_src_code
,p_gen_all_etc_src_code => FP_Cols_Rec.gen_all_etc_src_code
,p_gen_all_incl_change_doc_flag => FP_Cols_Rec.gen_all_incl_change_doc_flag
,p_gen_all_incl_open_comm_flag => FP_Cols_Rec.gen_all_incl_open_comm_flag
,p_gen_all_ret_manual_line_flag => FP_Cols_Rec.gen_all_ret_manual_line_flag
,p_gen_all_incl_bill_event_flag => FP_Cols_Rec.gen_all_incl_bill_event_flag
,P_GN_ALL_INCL_UNSPENT_AMT_FLAG => FP_Cols_Rec.gen_all_incl_unspent_amt_flag
,P_GN_CST_ACTUAL_AMTS_THRU_CODE => FP_Cols_Rec.gen_cost_actual_amts_thru_code
,P_GN_REV_ACTUAL_AMTS_THRU_CODE => FP_Cols_Rec.gen_rev_actual_amts_thru_code
,P_GN_ALL_ACTUAL_AMTS_THRU_CODE => FP_Cols_Rec.gen_all_actual_amts_thru_code
,p_track_workplan_costs_flag => FP_Cols_Rec.track_workplan_costs_flag
-- Start of FP M phase II dev changes
,p_gen_src_cost_wp_version_id => FP_Cols_Rec.gen_src_cost_wp_version_id
,p_gen_src_cost_wp_ver_code => FP_Cols_Rec.gen_src_cost_wp_ver_code
,p_gen_src_rev_wp_version_id => FP_Cols_Rec.gen_src_rev_wp_version_id
,p_gen_src_rev_wp_ver_code => FP_Cols_Rec.gen_src_rev_wp_ver_code
,p_gen_src_all_wp_version_id => FP_Cols_Rec.gen_src_all_wp_version_id
,p_gen_src_all_wp_ver_code => FP_Cols_Rec.gen_src_all_wp_ver_code
-- End of FP M phase II dev changes
--Adding for webadi Changes
,p_cost_layout_code => FP_Cols_Rec.cost_layout_code
,p_revenue_layout_code => FP_Cols_Rec.revenue_layout_code
,p_all_layout_code => FP_Cols_Rec.all_layout_code
,p_revenue_derivation_method => FP_Cols_Rec.revenue_derivation_method -- bug 5462471
,p_row_id => NULL
,x_return_status => x_return_status);
pa_debug.g_err_stage := TO_CHAR(l_Stage)||': Calling Table Handler to insert row';
pa_debug.g_err_stage := TO_CHAR(l_Stage)||'About to insert the row';
PA_PROJ_FP_OPTIONS_PKG.Insert_Row
( px_proj_fp_options_id => px_target_proj_fp_option_id
,p_project_id => p_target_project_id
,p_fin_plan_option_level_code => l_target_option_level_code
,p_fin_plan_type_id => l_plan_type_id
,p_fin_plan_start_date => FP_Cols_Rec.fin_plan_start_date /* Bug 2798794 */
,p_fin_plan_end_date => FP_Cols_Rec.fin_plan_end_date /* Bug 2798794 */
,p_fin_plan_preference_code => l_fp_preference_code
,p_cost_amount_set_id => FP_Cols_Rec.cost_amount_set_id
,p_revenue_amount_set_id => FP_Cols_Rec.revenue_amount_set_id
,p_all_amount_set_id => FP_Cols_Rec.all_amount_set_id
,p_cost_fin_plan_level_code => FP_Cols_Rec.cost_fin_plan_level_code
,p_cost_time_phased_code => FP_Cols_Rec.cost_time_phased_code
,p_cost_resource_list_id => FP_Cols_Rec.cost_resource_list_id
,p_revenue_fin_plan_level_code => FP_Cols_Rec.revenue_fin_plan_level_code
,p_revenue_time_phased_code => FP_Cols_Rec.revenue_time_phased_code
,p_revenue_resource_list_id => FP_Cols_Rec.revenue_resource_list_id
,p_all_fin_plan_level_code => FP_Cols_Rec.all_fin_plan_level_code
,p_all_time_phased_code => FP_Cols_Rec.all_time_phased_code
,p_all_resource_list_id => FP_Cols_Rec.all_resource_list_id
,p_report_labor_hrs_from_code => FP_Cols_Rec.report_labor_hrs_from_code
,p_fin_plan_version_id => p_target_fin_plan_version_id
,p_plan_in_multi_curr_flag => FP_Cols_Rec.plan_in_multi_curr_flag
,p_factor_by_code => FP_Cols_Rec.factor_by_code
,p_default_amount_type_code => FP_Cols_Rec.default_amount_type_code
,p_default_amount_subtype_code => FP_Cols_Rec.default_amount_subtype_code
,p_approved_cost_plan_type_flag => FP_Mc_Cols_Rec.approved_cost_plan_type_flag
,p_approved_rev_plan_type_flag => FP_Mc_Cols_Rec.approved_rev_plan_type_flag
,p_projfunc_cost_rate_type => FP_Mc_Cols_Rec.projfunc_cost_rate_type
,p_projfunc_cost_rate_date_type => FP_Mc_Cols_Rec.projfunc_cost_rate_date_type
,p_projfunc_cost_rate_date => FP_Mc_Cols_Rec.projfunc_cost_rate_date
,p_projfunc_rev_rate_type => FP_Mc_Cols_Rec.projfunc_rev_rate_type
,p_projfunc_rev_rate_date_type => FP_Mc_Cols_Rec.projfunc_rev_rate_date_type
,p_projfunc_rev_rate_date => FP_Mc_Cols_Rec.projfunc_rev_rate_date
,p_project_cost_rate_type => FP_Mc_Cols_Rec.project_cost_rate_type
,p_project_cost_rate_date_type => FP_Mc_Cols_Rec.project_cost_rate_date_type
,p_project_cost_rate_date => FP_Mc_Cols_Rec.project_cost_rate_date
,p_project_rev_rate_type => FP_Mc_Cols_Rec.project_rev_rate_type
,p_project_rev_rate_date_type => FP_Mc_Cols_Rec.project_rev_rate_date_type
,p_project_rev_rate_date => FP_Mc_Cols_Rec.project_rev_rate_date
/* Bug 2920954 start of additional parameters added for post fp_k oneoff*/
,p_margin_derived_from_code => FP_Cols_Rec.margin_derived_from_code
,p_select_cost_res_auto_flag => FP_Cols_Rec.select_cost_res_auto_flag
,p_cost_res_planning_level => FP_Cols_Rec.cost_res_planning_level
,p_select_rev_res_auto_flag => FP_Cols_Rec.select_rev_res_auto_flag
,p_revenue_res_planning_level => FP_Cols_Rec.revenue_res_planning_level
,p_select_all_res_auto_flag => FP_Cols_Rec.select_all_res_auto_flag
,p_all_res_planning_level => FP_Cols_Rec.all_res_planning_level
,p_refresh_required_flag => l_refresh_required_flag
,p_request_id => NULL -- Always passed in as null by design.
,p_processing_code => NULL -- Always passed in as null by design.
/* Bug 2920954 end of additional parameters added for post fp_k oneoff*/
,p_primary_cost_forecast_flag => FP_Mc_Cols_Rec.primary_cost_forecast_flag
,p_primary_rev_forecast_flag => FP_Mc_Cols_Rec.primary_rev_forecast_flag
,p_use_planning_rates_flag => FP_Cols_Rec.use_planning_rates_flag
,p_rbs_version_id => FP_Cols_Rec.rbs_version_id
,p_res_class_raw_cost_sch_id => FP_Cols_Rec.res_class_raw_cost_sch_id
,p_res_class_bill_rate_sch_id => FP_Cols_Rec.res_class_bill_rate_sch_id
,p_cost_emp_rate_sch_id => FP_Cols_Rec.cost_emp_rate_sch_id
,p_cost_job_rate_sch_id => FP_Cols_Rec.cost_job_rate_sch_id
,P_CST_NON_LABR_RES_RATE_SCH_ID => FP_Cols_Rec.cost_non_labor_res_rate_sch_id
,p_cost_res_class_rate_sch_id => FP_Cols_Rec.cost_res_class_rate_sch_id
,p_cost_burden_rate_sch_id => FP_Cols_Rec.cost_burden_rate_sch_id
,p_cost_current_planning_period => FP_Cols_Rec.cost_current_planning_period
,p_cost_period_mask_id => FP_Cols_Rec.cost_period_mask_id
,p_rev_emp_rate_sch_id => FP_Cols_Rec.rev_emp_rate_sch_id
,p_rev_job_rate_sch_id => FP_Cols_Rec.rev_job_rate_sch_id
,P_REV_NON_LABR_RES_RATE_SCH_ID => FP_Cols_Rec.rev_non_labor_res_rate_sch_id
,p_rev_res_class_rate_sch_id => FP_Cols_Rec.rev_res_class_rate_sch_id
,p_rev_current_planning_period => FP_Cols_Rec.rev_current_planning_period
,p_rev_period_mask_id => FP_Cols_Rec.rev_period_mask_id
/*** Bug 3580727
,p_all_emp_rate_sch_id => FP_Cols_Rec.all_emp_rate_sch_id
,p_all_job_rate_sch_id => FP_Cols_Rec.all_job_rate_sch_id
,P_ALL_NON_LABR_RES_RATE_SCH_ID => FP_Cols_Rec.all_non_labor_res_rate_sch_id
,p_all_res_class_rate_sch_id => FP_Cols_Rec.all_res_class_rate_sch_id
,p_all_burden_rate_sch_id => FP_Cols_Rec.all_burden_rate_sch_id
***/
,p_all_current_planning_period => FP_Cols_Rec.all_current_planning_period
,p_all_period_mask_id => FP_Cols_Rec.all_period_mask_id
,p_gen_cost_src_code => FP_Cols_Rec.gen_cost_src_code
,p_gen_cost_etc_src_code => FP_Cols_Rec.gen_cost_etc_src_code
,P_GN_COST_INCL_CHANGE_DOC_FLAG => FP_Cols_Rec.gen_cost_incl_change_doc_flag
,p_gen_cost_incl_open_comm_flag => FP_Cols_Rec.gen_cost_incl_open_comm_flag
,P_GN_COST_RET_MANUAL_LINE_FLAG => FP_Cols_Rec.gen_cost_ret_manual_line_flag
,P_GN_CST_INCL_UNSPENT_AMT_FLAG => FP_Cols_Rec.gen_cost_incl_unspent_amt_flag
,p_gen_rev_src_code => FP_Cols_Rec.gen_rev_src_code
,p_gen_rev_etc_src_code => FP_Cols_Rec.gen_rev_etc_src_code
,p_gen_rev_incl_change_doc_flag => FP_Cols_Rec.gen_rev_incl_change_doc_flag
,p_gen_rev_incl_bill_event_flag => FP_Cols_Rec.gen_rev_incl_bill_event_flag
,p_gen_rev_ret_manual_line_flag => FP_Cols_Rec.gen_rev_ret_manual_line_flag
/*** Bug 3580727
,P_GN_REV_INCL_UNSPENT_AMT_FLAG => FP_Cols_Rec.gen_rev_incl_unspent_amt_flag
***/
,p_gen_src_cost_plan_type_id => FP_Cols_Rec.gen_src_cost_plan_type_id
,p_gen_src_cost_plan_version_id => FP_Cols_Rec.gen_src_cost_plan_version_id
,p_gen_src_cost_plan_ver_code => FP_Cols_Rec.gen_src_cost_plan_ver_code
,p_gen_src_rev_plan_type_id => FP_Cols_Rec.gen_src_rev_plan_type_id
,p_gen_src_rev_plan_version_id => FP_Cols_Rec.gen_src_rev_plan_version_id
,p_gen_src_rev_plan_ver_code => FP_Cols_Rec.gen_src_rev_plan_ver_code
,p_gen_src_all_plan_type_id => FP_Cols_Rec.gen_src_all_plan_type_id
,p_gen_src_all_plan_version_id => FP_Cols_Rec.gen_src_all_plan_version_id
,p_gen_src_all_plan_ver_code => FP_Cols_Rec.gen_src_all_plan_ver_code
,p_gen_all_src_code => FP_Cols_Rec.gen_all_src_code
,p_gen_all_etc_src_code => FP_Cols_Rec.gen_all_etc_src_code
,p_gen_all_incl_change_doc_flag => FP_Cols_Rec.gen_all_incl_change_doc_flag
,p_gen_all_incl_open_comm_flag => FP_Cols_Rec.gen_all_incl_open_comm_flag
,p_gen_all_ret_manual_line_flag => FP_Cols_Rec.gen_all_ret_manual_line_flag
,p_gen_all_incl_bill_event_flag => FP_Cols_Rec.gen_all_incl_bill_event_flag
,P_GN_ALL_INCL_UNSPENT_AMT_FLAG => FP_Cols_Rec.gen_all_incl_unspent_amt_flag
,P_GN_CST_ACTUAL_AMTS_THRU_CODE => FP_Cols_Rec.gen_cost_actual_amts_thru_code
,P_GN_REV_ACTUAL_AMTS_THRU_CODE => FP_Cols_Rec.gen_rev_actual_amts_thru_code
,P_GN_ALL_ACTUAL_AMTS_THRU_CODE => FP_Cols_Rec.gen_all_actual_amts_thru_code
,p_track_workplan_costs_flag => FP_Cols_Rec.track_workplan_costs_flag
-- Start of FP M phase II dev changes
,p_gen_src_cost_wp_version_id => FP_Cols_Rec.gen_src_cost_wp_version_id
,p_gen_src_cost_wp_ver_code => FP_Cols_Rec.gen_src_cost_wp_ver_code
,p_gen_src_rev_wp_version_id => FP_Cols_Rec.gen_src_rev_wp_version_id
,p_gen_src_rev_wp_ver_code => FP_Cols_Rec.gen_src_rev_wp_ver_code
,p_gen_src_all_wp_version_id => FP_Cols_Rec.gen_src_all_wp_version_id
,p_gen_src_all_wp_ver_code => FP_Cols_Rec.gen_src_all_wp_ver_code
-- End of FP M phase II dev changes
--Adding for webadi Changes
,p_cost_layout_code => FP_Cols_Rec.cost_layout_code
,p_revenue_layout_code => FP_Cols_Rec.revenue_layout_code
,p_all_layout_code => FP_Cols_Rec.all_layout_code
,p_revenue_derivation_method => FP_Cols_Rec.revenue_derivation_method -- bug 5462471
,x_row_id => x_row_id
,x_return_status => x_return_status);
- All SELECTS from pa_proj_fp_options
23-JAN-2004 rravipat Bug 3354518 (IDC)
===========================================================================================*/
PROCEDURE Get_FP_Options (
p_proj_fp_options_id IN NUMBER
,p_target_fp_options_id IN NUMBER
,p_fin_plan_preference_code IN VARCHAR2
,p_target_fp_option_level_code IN VARCHAR2 -- Adding this new parameter as a part of the ms-excel options
,x_fp_cols_rec OUT NOCOPY FP_COLS
,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_debug_mode VARCHAR2(30);
SELECT fin_plan_preference_code,
fin_plan_option_level_code,
project_id
INTO l_source_fin_plan_pref,
l_fin_plan_option_level_code,
l_project_id
FROM pa_proj_fp_options
WHERE proj_fp_options_id = p_proj_fp_options_id;
SELECT all_amount_set_id
INTO l_target_all_amount_set_id
FROM pa_proj_fp_options
WHERE proj_fp_options_id = p_target_fp_options_id;
but the preference code that the target option should be updated with. So,
the target flags which are intialized as Null are reset only if the target option
before updation is having ALL preference code. We are checking the prefrence code
of the target option as it is before updation by checking the nullablility of the
all_amount_set_id column */
IF l_target_all_amount_set_id IS NOT NULL THEN
SELECT raw_cost_flag
, burdened_cost_flag
, revenue_flag
, bill_rate_flag
, cost_rate_flag
, burden_rate_flag
INTO l_target_raw_cost_flag
, l_target_burdened_cost_flag
, l_target_revenue_flag
, l_target_bill_rate_flag
, l_target_cost_rate_flag
, l_target_burd_rate_flag
FROM pa_fin_plan_amount_sets
WHERE fin_plan_amount_set_id = l_target_all_amount_set_id ;
SELECT cost_amount_set_id
,revenue_amount_set_id
,all_amount_set_id
INTO l_cost_amount_set_id
,l_revenue_amount_set_id
,l_all_amount_set_id
FROM pa_proj_fp_options
WHERE proj_fp_options_id = p_proj_fp_options_id;
SELECT raw_cost_flag
, burdened_cost_flag
, cost_qty_flag
, nvl(l_target_revenue_flag,l_revenue_flag) /* Bug 3144283 */
-- bug 3505736 , nvl(l_target_bill_rate_flag,bill_rate_flag) -- FP M Dev effort
-- bug 3505736 , nvl(l_target_cost_rate_flag,cost_rate_flag) -- FP M Dev effort
-- bug 3505736 , nvl(l_target_burd_rate_flag,burden_rate_flag) -- FP M Dev effort
, nvl(l_target_bill_rate_flag,l_bill_rate_flag) -- bug 3505736
, cost_rate_flag -- bug 3505736
, burden_rate_flag -- bug 3505736
INTO l_raw_cost_flag
, l_burdened_cost_flag
, l_cost_qty_flag
, l_revenue_flag
, l_bill_rate_flag -- FP M Dev effort
, l_cost_rate_flag -- FP M Dev effort
, l_burden_rate_flag -- FP M Dev effort
FROM pa_fin_plan_amount_sets
WHERE fin_plan_amount_set_id = l_cost_amount_set_id ;
SELECT revenue_flag
, revenue_qty_flag
, nvl(l_target_raw_cost_flag,l_raw_cost_flag) /* Bug 3144283 */
, nvl(l_target_burdened_cost_flag,l_burdened_cost_flag) /* Bug 3144283 */
-- bug 3505736 , nvl(l_target_bill_rate_flag,bill_rate_flag) -- FP M Dev effort
-- bug 3505736 , nvl(l_target_cost_rate_flag,cost_rate_flag) -- FP M Dev effort
-- bug 3505736 , nvl(l_target_burd_rate_flag,burden_rate_flag) -- FP M Dev effort
, bill_rate_flag -- bug 3505736
, nvl(l_target_cost_rate_flag,l_cost_rate_flag) -- bug 3505736
, nvl(l_target_burd_rate_flag,l_burden_rate_flag) -- bug 3505736
INTO l_revenue_flag
, l_revenue_qty_flag
, l_raw_cost_flag
, l_burdened_cost_flag
, l_bill_rate_flag -- FP M Dev effort
, l_cost_rate_flag -- FP M Dev effort
, l_burden_rate_flag -- FP M Dev effort
FROM pa_fin_plan_amount_sets
WHERE fin_plan_amount_set_id = l_revenue_amount_set_id ;
SELECT raw_cost_flag
, burdened_cost_flag
, all_qty_flag
, revenue_flag
-- bug 3505736 , nvl(l_target_bill_rate_flag,bill_rate_flag) -- FP M Dev effort
-- bug 3505736 , nvl(l_target_cost_rate_flag,cost_rate_flag) -- FP M Dev effort
-- bug 3505736 , nvl(l_target_burd_rate_flag,burden_rate_flag) -- FP M Dev effort
,bill_rate_flag -- bug 3505736
,cost_rate_flag -- bug 3505736
,burden_rate_flag -- bug 3505736
INTO l_raw_cost_flag
, l_burdened_cost_flag
, l_all_qty_flag
, l_revenue_flag
, l_bill_rate_flag -- FP M Dev effort
, l_cost_rate_flag -- FP M Dev effort
, l_burden_rate_flag -- FP M Dev effort
FROM pa_fin_plan_amount_sets
WHERE fin_plan_amount_set_id = l_all_amount_set_id ;
SELECT fin_plan_start_date
,fin_plan_end_date
,cost_amount_set_id
,revenue_amount_set_id
,NULL all_amount_set_id
,cost_fin_plan_level_code
,cost_time_phased_code
,cost_resource_list_id
,revenue_fin_plan_level_code
,revenue_time_phased_code
,revenue_resource_list_id
,NULL all_fin_plan_level_code
,NULL all_time_phased_code
,NULL all_resource_list_id
,nvl(report_labor_hrs_from_code,PA_FP_CONSTANTS_PKG.G_MARGIN_DERIVED_FROM_CODE_B) report_labor_hrs_from_code
,plan_in_multi_curr_flag
,factor_by_code
,default_amount_type_code
,default_amount_subtype_code
,margin_derived_from_code
/* Bug 2920954 start of new record parameters for post fp-k oneoff patch */
,select_cost_res_auto_flag
,NULL cost_res_planning_level
,select_rev_res_auto_flag
,NULL revenue_res_planning_level
,NULL select_all_res_auto_flag
,NULL all_res_planning_level
/* Bug 2920954 end of new record parameters for post fp-k oneoff patch */
,use_planning_rates_flag
,rbs_version_id
,res_class_raw_cost_sch_id
,res_class_bill_rate_sch_id
,cost_emp_rate_sch_id
,cost_job_rate_sch_id
,cost_non_labor_res_rate_sch_id
,cost_res_class_rate_sch_id
,cost_burden_rate_sch_id
,cost_current_planning_period
,cost_period_mask_id
,rev_emp_rate_sch_id
,rev_job_rate_sch_id
,rev_non_labor_res_rate_sch_id
,rev_res_class_rate_sch_id
,rev_current_planning_period
,rev_period_mask_id
/*** Bug 3580727
,NULL all_emp_rate_sch_id
,NULL all_job_rate_sch_id
,NULL all_non_labor_res_rate_sch_id
,NULL all_res_class_rate_sch_id
,NULL all_burden_rate_sch_id
***/
,NULL all_current_planning_period
,NULL all_period_mask_id
,gen_cost_src_code
,gen_cost_etc_src_code
,gen_cost_incl_change_doc_flag
,gen_cost_incl_open_comm_flag
,gen_cost_ret_manual_line_flag
,gen_cost_incl_unspent_amt_flag
,gen_rev_src_code
,gen_rev_etc_src_code
,gen_rev_incl_change_doc_flag
,gen_rev_incl_bill_event_flag
,gen_rev_ret_manual_line_flag
/*** Bug 3580727
,gen_rev_incl_unspent_amt_flag
***/
,gen_src_cost_plan_type_id
,gen_src_cost_plan_version_id
,gen_src_cost_plan_ver_code
,gen_src_rev_plan_type_id
,gen_src_rev_plan_version_id
,gen_src_rev_plan_ver_code
,NULL gen_src_all_plan_type_id
,NULL gen_src_all_plan_version_id
,NULL gen_src_all_plan_ver_code
,NULL gen_all_src_code
,NULL gen_all_etc_src_code
,NULL gen_all_incl_change_doc_flag
,NULL gen_all_incl_open_comm_flag
,NULL gen_all_ret_manual_line_flag
,NULL gen_all_incl_bill_event_flag
,NULL gen_all_incl_unspent_amt_flag
,gen_cost_actual_amts_thru_code
,gen_rev_actual_amts_thru_code
,NULL gen_all_actual_amts_thru_code
,track_workplan_costs_flag
-- start of FP M dev phase II changes
,gen_src_cost_wp_version_id
,gen_src_cost_wp_ver_code
,gen_src_rev_wp_version_id
,gen_src_rev_wp_ver_code
,NULL gen_src_all_wp_version_id
,NULL gen_src_all_wp_ver_code
-- end of FP M dev phase II changes
-- Added for ms-excel options in webadi
,decode(p_target_fp_option_level_code,PA_FP_CONSTANTS_PKG.G_OPTION_LEVEL_PLAN_TYPE,cost_layout_code,null)
,decode(p_target_fp_option_level_code,PA_FP_CONSTANTS_PKG.G_OPTION_LEVEL_PLAN_TYPE,revenue_layout_code,null)
,NULL all_layout_code
,revenue_derivation_method -- Bug 5462471
INTO x_fp_cols_rec
FROM pa_proj_fp_options
WHERE proj_fp_options_id = p_proj_fp_options_id;
SELECT fin_plan_start_date fin_plan_start_date
,fin_plan_end_date fin_plan_end_date
,l_cost_amount_set_id cost_amount_set_id
,NULL revenue_amount_set_id
,NULL all_amount_set_id
,nvl(cost_fin_plan_level_code, all_fin_plan_level_code) cost_fin_plan_level_code
,nvl(cost_time_phased_code, all_time_phased_code) cost_time_phased_code
,nvl(cost_resource_list_id, all_resource_list_id) cost_resource_list_id
,NULL revenue_fin_plan_level_code
,NULL revenue_time_phased_code
,NULL revenue_resource_list_id
,NULL all_fin_plan_level_code
,NULL all_time_phased_code
,NULL all_resource_list_id
,NULL report_labor_hrs_from_code
,plan_in_multi_curr_flag plan_in_multi_curr_flag
,factor_by_code factor_by_code
,default_amount_type_code default_amount_type_code
,default_amount_subtype_code default_amount_subtype_code
,nvl(margin_derived_from_code,PA_FP_CONSTANTS_PKG.G_MARGIN_DERIVED_FROM_CODE_B) margin_derived_from_code
/* Bug 2920954 start of new record parameters for post fp-k oneoff patch */
,nvl(select_cost_res_auto_flag, select_all_res_auto_flag) select_cost_res_auto_flag
,NULL cost_res_planning_level
,NULL select_rev_res_auto_flag
,NULL revenue_res_planning_level
,NULL select_all_res_auto_flag
,NULL all_res_planning_level
/* Bug 2920954 end of new record parameters for post fp-k oneoff patch */
,use_planning_rates_flag
,rbs_version_id
,res_class_raw_cost_sch_id
,res_class_bill_rate_sch_id
/*** Bug 3580727
,nvl(cost_emp_rate_sch_id, all_emp_rate_sch_id) cost_emp_rate_sch_id
,nvl(cost_job_rate_sch_id, all_job_rate_sch_id) cost_job_rate_sch_id
,nvl(cost_non_labor_res_rate_sch_id,all_non_labor_res_rate_sch_id) cost_non_labor_res_rate_sch_id
,nvl(cost_res_class_rate_sch_id, all_res_class_rate_sch_id) cost_res_class_rate_sch_id
,nvl(cost_burden_rate_sch_id, all_burden_rate_sch_id) cost_burden_rate_sch_id
***/
,cost_emp_rate_sch_id
,cost_job_rate_sch_id
,cost_non_labor_res_rate_sch_id
,cost_res_class_rate_sch_id
,cost_burden_rate_sch_id
,nvl(cost_current_planning_period, all_current_planning_period) cost_current_planning_period
,nvl(cost_period_mask_id, all_period_mask_id) cost_period_mask_id
,NULL rev_emp_rate_sch_id
,NULL rev_job_rate_sch_id
,NULL rev_non_labor_res_rate_sch_id
,NULL rev_res_class_rate_sch_id
,NULL rev_current_planning_period
,NULL rev_period_mask_id
/*** Bug 3580727
,NULL all_emp_rate_sch_id
,NULL all_job_rate_sch_id
,NULL all_non_labor_res_rate_sch_id
,NULL all_res_class_rate_sch_id
,NULL all_burden_rate_sch_id
***/
,NULL all_current_planning_period
,NULL all_period_mask_id
,nvl(gen_cost_src_code, gen_all_src_code) gen_cost_src_code
,nvl(gen_cost_etc_src_code, gen_all_etc_src_code) gen_cost_etc_src_code
,nvl(gen_cost_incl_change_doc_flag, gen_all_incl_change_doc_flag) gen_cost_incl_change_doc_flag
,nvl(gen_cost_incl_open_comm_flag, gen_all_incl_open_comm_flag) gen_cost_incl_open_comm_flag
,nvl(gen_cost_ret_manual_line_flag, gen_all_ret_manual_line_flag) gen_cost_ret_manual_line_flag
,nvl(gen_cost_incl_unspent_amt_flag,gen_all_incl_unspent_amt_flag) gen_cost_incl_unspent_amt_flag
,NULL gen_rev_src_code
,NULL gen_rev_etc_src_code
,NULL gen_rev_incl_change_doc_flag
,NULL gen_rev_incl_bill_event_flag
,NULL gen_rev_ret_manual_line_flag
/*** Bug 3580727
,NULL gen_rev_incl_unspent_amt_flag
***/
,nvl(gen_src_cost_plan_type_id, gen_src_all_plan_type_id) gen_src_cost_plan_type_id
,nvl(gen_src_cost_plan_version_id, gen_src_all_plan_version_id) gen_src_cost_plan_version_id
,nvl(gen_src_cost_plan_ver_code, gen_src_all_plan_ver_code) gen_src_cost_plan_ver_code
,NULL gen_src_rev_plan_type_id
,NULL gen_src_rev_plan_version_id
,NULL gen_src_rev_plan_ver_code
,NULL gen_src_all_plan_type_id
,NULL gen_src_all_plan_version_id
,NULL gen_src_all_plan_ver_code
,NULL gen_all_src_code
,NULL gen_all_etc_src_code
,NULL gen_all_incl_change_doc_flag
,NULL gen_all_incl_open_comm_flag
,NULL gen_all_ret_manual_line_flag
,NULL gen_all_incl_bill_event_flag
,NULL gen_all_incl_unspent_amt_flag
,nvl(gen_cost_actual_amts_thru_code, gen_all_actual_amts_thru_code) gen_cost_actual_amts_thru_code
,NULL gen_rev_actual_amts_thru_code
,NULL gen_all_actual_amts_thru_code
,track_workplan_costs_flag
-- start of FP M dev phase II changes
,nvl(gen_src_cost_wp_version_id, gen_src_all_wp_version_id) gen_src_cost_wp_version_id
,nvl(gen_src_cost_wp_ver_code, gen_src_all_wp_ver_code) gen_src_cost_wp_ver_code
,NULL gen_src_rev_wp_version_id
,NULL gen_src_rev_wp_ver_code
,NULL gen_src_all_wp_version_id
,NULL gen_src_all_wp_ver_code
-- end of FP M dev phase II changes
-- Added for ms-excel options in webadi
,decode(p_target_fp_option_level_code,PA_FP_CONSTANTS_PKG.G_OPTION_LEVEL_PLAN_TYPE,nvl(cost_layout_code, all_layout_code ),null) cost_layout_code
,NULL revenue_layout_code
,NULL all_layout_code
,NULL -- Bug 5462471 For cost only version revenue_derivation_method should be null always
INTO x_fp_cols_rec
FROM pa_proj_fp_options
WHERE proj_fp_options_id = p_proj_fp_options_id;
SELECT fin_plan_start_date fin_plan_start_date
,fin_plan_end_date fin_plan_end_date
,NULL cost_amount_set_id
,l_revenue_amount_set_id revenue_amount_set_id
,NULL all_amount_set_id
,NULL cost_fin_plan_level_code
,NULL cost_time_phased_code
,NULL cost_resource_list_id
,nvl(revenue_fin_plan_level_code, all_fin_plan_level_code) revenue_fin_plan_level_code
,nvl(revenue_time_phased_code, all_time_phased_code) revenue_time_phased_code
,nvl(revenue_resource_list_id, all_resource_list_id) revenue_resource_list_id
,NULL all_fin_plan_level_code
,NULL all_time_phased_code
,NULL all_resource_list_id
,NULL report_labor_hrs_from_code
,plan_in_multi_curr_flag plan_in_multi_curr_flag
,factor_by_code factor_by_code
,default_amount_type_code default_amount_type_code
,default_amount_subtype_code default_amount_subtype_code
,null margin_derived_from_code
/* Bug 2920954 start of new record parameters for post fp-k oneoff patch */
,NULL select_cost_res_auto_flag
,NULL cost_res_planning_level
,nvl(select_rev_res_auto_flag, select_all_res_auto_flag) select_rev_res_auto_flag
,NULL revenue_res_planning_level
,NULL select_all_res_auto_flag
,NULL all_res_planning_level
/* Bug 2920954 end of new record parameters for post fp-k oneoff patch */
,use_planning_rates_flag
,rbs_version_id
,res_class_raw_cost_sch_id
,res_class_bill_rate_sch_id
,NULL cost_emp_rate_sch_id
,NULL cost_job_rate_sch_id
,NULL cost_non_labor_res_rate_sch_id
,NULL cost_res_class_rate_sch_id
,NULL cost_burden_rate_sch_id
,NULL cost_current_planning_period
,NULL cost_period_mask_id
/*** Bug 3580727
,nvl(rev_emp_rate_sch_id, all_emp_rate_sch_id) rev_emp_rate_sch_id
,nvl(rev_job_rate_sch_id, all_job_rate_sch_id) rev_job_rate_sch_id
,nvl(rev_non_labor_res_rate_sch_id, all_non_labor_res_rate_sch_id) rev_non_labor_res_rate_sch_id
,nvl(rev_res_class_rate_sch_id, all_res_class_rate_sch_id) rev_res_class_rate_sch_id
***/
,rev_emp_rate_sch_id
,rev_job_rate_sch_id
,rev_non_labor_res_rate_sch_id
,rev_res_class_rate_sch_id
,nvl(rev_current_planning_period, all_current_planning_period) rev_current_planning_period
,nvl(rev_period_mask_id, all_period_mask_id) rev_period_mask_id
/*** Bug 3580727
,NULL all_emp_rate_sch_id
,NULL all_job_rate_sch_id
,NULL all_non_labor_res_rate_sch_id
,NULL all_res_class_rate_sch_id
,NULL all_burden_rate_sch_id
***/
,NULL all_current_planning_period
,NULL all_period_mask_id
,NULL gen_cost_src_code
,NULL gen_cost_etc_src_code
,NULL gen_cost_incl_change_doc_flag
,NULL gen_cost_incl_open_comm_flag
,NULL gen_cost_ret_manual_line_flag
,NULL gen_cost_incl_unspent_amt_flag
,nvl(gen_rev_src_code, gen_all_src_code) gen_rev_src_code
,nvl(gen_rev_etc_src_code, gen_all_etc_src_code) gen_rev_etc_src_code
,nvl(gen_rev_incl_change_doc_flag, gen_all_incl_change_doc_flag) gen_rev_incl_change_doc_flag
,nvl(gen_rev_incl_bill_event_flag, gen_all_incl_bill_event_flag) gen_rev_incl_bill_event_flag
,nvl(gen_rev_ret_manual_line_flag, gen_all_ret_manual_line_flag) gen_rev_ret_manual_line_flag
/*** Bug 3580727
,nvl(gen_rev_incl_unspent_amt_flag, gen_all_incl_unspent_amt_flag) gen_rev_incl_unspent_amt_flag
***/
,NULL gen_src_cost_plan_type_id
,NULL gen_src_cost_plan_version_id
,NULL gen_src_cost_plan_ver_code
,nvl(gen_src_rev_plan_type_id, gen_src_all_plan_type_id) gen_src_rev_plan_type_id
,nvl(gen_src_rev_plan_version_id, gen_src_all_plan_version_id) gen_src_rev_plan_version_id
,nvl(gen_src_rev_plan_ver_code, gen_src_all_plan_ver_code) gen_src_rev_plan_ver_code
,NULL gen_src_all_plan_type_id
,NULL gen_src_all_plan_version_id
,NULL gen_src_all_plan_ver_code
,NULL gen_all_src_code
,NULL gen_all_etc_src_code
,NULL gen_all_incl_change_doc_flag
,NULL gen_all_incl_open_comm_flag
,NULL gen_all_ret_manual_line_flag
,NULL gen_all_incl_bill_event_flag
,NULL gen_all_incl_unspent_amt_flag
,NULL gen_cost_actual_amts_thru_code
,nvl(gen_rev_actual_amts_thru_code, gen_all_actual_amts_thru_code) gen_rev_actual_amts_thru_code
,NULL gen_all_actual_amts_thru_code
,track_workplan_costs_flag
-- start of FP M dev phase II changes
,NULL gen_src_cost_wp_version_id
,NULL gen_src_cost_wp_ver_code
,nvl(gen_src_rev_wp_version_id, gen_src_all_wp_version_id) gen_src_rev_wp_version_id
,nvl(gen_src_rev_wp_ver_code, gen_src_all_wp_ver_code) gen_src_rev_wp_ver_code
,NULL gen_src_all_wp_version_id
,NULL gen_src_all_wp_ver_code
-- end of FP M dev phase II changes
-- Added for ms-excel options in webadi
,NULL cost_layout_code
,decode(p_target_fp_option_level_code,PA_FP_CONSTANTS_PKG.G_OPTION_LEVEL_PLAN_TYPE,nvl(revenue_layout_code, all_layout_code ) ,null) revenue_layout_code
,NULL all_layout_code
,revenue_derivation_method -- Bug 5462471
INTO x_fp_cols_rec
FROM pa_proj_fp_options
WHERE proj_fp_options_id = p_proj_fp_options_id;
SELECT fin_plan_start_date fin_plan_start_date
,fin_plan_end_date fin_plan_end_date
,NULL cost_amount_set_id
,NULL revenue_amount_set_id
,l_all_amount_set_id all_amount_set_id
,NULL cost_fin_plan_level_code
,NULL cost_time_phased_code
,NULL cost_resource_list_id
,NULL revenue_fin_plan_level_code
,NULL revenue_time_phased_code
,NULL revenue_resource_list_id
,nvl(cost_fin_plan_level_code,nvl(revenue_fin_plan_level_code,all_fin_plan_level_code)) all_fin_plan_level_code
,nvl(cost_time_phased_code,nvl(revenue_time_phased_code,all_time_phased_code)) all_time_phased_code
,nvl(cost_resource_list_id,nvl(revenue_resource_list_id,all_resource_list_id)) all_resource_list_id
,NULL report_labor_hrs_from_code
,plan_in_multi_curr_flag plan_in_multi_curr_flag
,factor_by_code factor_by_code
,default_amount_type_code default_amount_type_code
,default_amount_subtype_code default_amount_subtype_code /* manoj */
,nvl(margin_derived_from_code,PA_FP_CONSTANTS_PKG.G_MARGIN_DERIVED_FROM_CODE_B) margin_derived_from_code
/* Bug 2920954 start of new record parameters for post fp-k oneoff patch */
,NULL select_cost_res_auto_flag
,NULL cost_res_planning_level
,NULL select_rev_res_auto_flag
,NULL revenue_res_planning_level
,nvl(select_cost_res_auto_flag,nvl(select_rev_res_auto_flag ,select_all_res_auto_flag)) select_all_res_auto_flag
,NULL all_res_planning_level
/* Bug 2920954 end of new record parameters for post fp-k oneoff patch */
,use_planning_rates_flag
,rbs_version_id
,res_class_raw_cost_sch_id
,res_class_bill_rate_sch_id
/*** Bug 3580727
,NULL cost_emp_rate_sch_id
,NULL cost_job_rate_sch_id
,NULL cost_non_labor_res_rate_sch_id
,NULL cost_res_class_rate_sch_id
,NULL cost_burden_rate_sch_id
***/
,cost_emp_rate_sch_id
,cost_job_rate_sch_id
,cost_non_labor_res_rate_sch_id
,cost_res_class_rate_sch_id
,cost_burden_rate_sch_id
,NULL cost_current_planning_period
,NULL cost_period_mask_id
/*** Bug 3580727
,NULL rev_emp_rate_sch_id
,NULL rev_job_rate_sch_id
,NULL rev_non_labor_res_rate_sch_id
,NULL rev_res_class_rate_sch_id
***/
,rev_emp_rate_sch_id
,rev_job_rate_sch_id
,rev_non_labor_res_rate_sch_id
,rev_res_class_rate_sch_id
,NULL rev_current_planning_period
,NULL rev_period_mask_id
/*** Bug 3580727
,nvl(cost_emp_rate_sch_id,nvl(rev_emp_rate_sch_id, all_emp_rate_sch_id)) all_emp_rate_sch_id
,nvl(cost_job_rate_sch_id,nvl(rev_job_rate_sch_id, all_job_rate_sch_id)) all_job_rate_sch_id
,nvl(cost_non_labor_res_rate_sch_id, nvl(rev_non_labor_res_rate_sch_id, all_non_labor_res_rate_sch_id)) all_non_labor_res_rate_sch_id
,nvl(cost_res_class_rate_sch_id, nvl(rev_res_class_rate_sch_id, all_res_class_rate_sch_id)) all_res_class_rate_sch_id
,nvl(cost_burden_rate_sch_id, all_burden_rate_sch_id) all_burden_rate_sch_id
***/
,nvl(cost_current_planning_period, nvl(rev_current_planning_period, all_current_planning_period)) all_current_planning_period
,nvl(cost_period_mask_id, nvl(rev_period_mask_id, all_period_mask_id)) all_period_mask_id
,NULL gen_cost_src_code
,NULL gen_cost_etc_src_code
,NULL gen_cost_incl_change_doc_flag
,NULL gen_cost_incl_open_comm_flag
,NULL gen_cost_ret_manual_line_flag
,NULL gen_cost_incl_unspent_amt_flag
,NULL gen_rev_src_code
,NULL gen_rev_etc_src_code
,NULL gen_rev_incl_change_doc_flag
,NULL gen_rev_incl_bill_event_flag
,NULL gen_rev_ret_manual_line_flag
/*** Bug 3580727
,NULL gen_rev_incl_unspent_amt_flag
***/
,NULL gen_src_cost_plan_type_id
,NULL gen_src_cost_plan_version_id
,NULL gen_src_cost_plan_ver_code
,NULL gen_src_rev_plan_type_id
,NULL gen_src_rev_plan_version_id
,NULL gen_src_rev_plan_ver_code
,nvl(gen_src_cost_plan_type_id,nvl(gen_src_rev_plan_type_id,gen_src_all_plan_type_id)) gen_src_all_plan_type_id
,nvl(gen_src_cost_plan_version_id,nvl(gen_src_rev_plan_version_id,gen_src_all_plan_version_id)) gen_src_all_plan_version_id
,nvl(gen_src_cost_plan_ver_code,nvl(gen_src_rev_plan_ver_code,gen_src_all_plan_ver_code)) gen_src_all_plan_ver_code
,nvl(gen_cost_src_code, nvl(gen_rev_src_code,gen_all_src_code)) gen_all_src_code
,nvl(gen_cost_etc_src_code, nvl(gen_rev_etc_src_code, gen_all_etc_src_code)) gen_all_etc_src_code
,nvl(gen_cost_incl_change_doc_flag, nvl(gen_rev_incl_change_doc_flag, gen_all_incl_change_doc_flag)) gen_all_incl_change_doc_flag
,nvl(gen_cost_incl_open_comm_flag, gen_all_incl_open_comm_flag) gen_all_incl_open_comm_flag
,nvl(gen_cost_ret_manual_line_flag, nvl(gen_rev_ret_manual_line_flag, gen_all_ret_manual_line_flag)) gen_all_ret_manual_line_flag
,nvl(gen_rev_incl_bill_event_flag, gen_all_incl_bill_event_flag) gen_all_incl_bill_event_flag
/*** Bug 3580727
,nvl(gen_cost_incl_unspent_amt_flag, nvl(gen_rev_incl_unspent_amt_flag, gen_all_incl_unspent_amt_flag)) gen_all_incl_unspent_amt_flag
***/
,nvl(gen_cost_incl_unspent_amt_flag, gen_all_incl_unspent_amt_flag) gen_all_incl_unspent_amt_flag
,NULL gen_cost_actual_amts_thru_code
,NULL gen_rev_actual_amts_thru_code
,nvl(gen_cost_actual_amts_thru_code, nvl(gen_rev_actual_amts_thru_code, gen_all_actual_amts_thru_code)) gen_all_actual_amts_thru_code
,track_workplan_costs_flag
-- start of FP M dev phase II changes
,NULL gen_src_cost_wp_version_id
,NULL gen_src_cost_wp_ver_code
,NULL gen_src_rev_wp_version_id
,NULL gen_src_rev_wp_ver_code
,nvl(gen_src_cost_wp_version_id,nvl(gen_src_rev_wp_version_id,gen_src_all_wp_version_id)) gen_src_all_wp_version_id
,nvl(gen_src_cost_wp_ver_code,nvl(gen_src_rev_wp_ver_code,gen_src_all_wp_ver_code)) gen_src_all_wp_ver_code
-- end of FP M dev phase II changes
-- Added for ms-excel options in webadi
,NULL cost_layout_code
,NULL revenue_layout_code
,decode(p_target_fp_option_level_code,PA_FP_CONSTANTS_PKG.G_OPTION_LEVEL_PLAN_TYPE,nvl(cost_layout_code, nvl(revenue_layout_code ,all_layout_code)) ,null) all_layout_code
,revenue_derivation_method -- Bug 5462471
INTO x_fp_cols_rec
FROM pa_proj_fp_options
WHERE proj_fp_options_id = p_proj_fp_options_id;
SELECT fin_plan_option_level_code, project_id, fin_plan_type_id
INTO l_fp_option_level_code, l_proj_id, l_fp_type_id
FROM pa_proj_fp_options
WHERE proj_fp_options_id = p_proj_fp_options_id;
Project and Plan Type has to be selected. */
IF (l_fp_option_level_code = PA_FP_CONSTANTS_PKG.G_OPTION_LEVEL_PLAN_VERSION) THEN
IF P_PA_DEBUG_MODE = 'Y' THEN
pa_debug.g_err_stage := 'Option Level Code is PLAN_VERSION.';
SELECT proj_fp_options_id
INTO x_proj_fp_options_id
FROM pa_proj_fp_options
WHERE fin_plan_option_level_code = PA_FP_CONSTANTS_PKG.G_OPTION_LEVEL_PLAN_TYPE
AND project_id = l_proj_id
AND fin_plan_type_id = l_fp_type_id;
Project has to be selected. */
IF P_PA_DEBUG_MODE = 'Y' THEN
pa_debug.g_err_stage := 'Option Level Code is PLAN_TYPE.';
SELECT proj_fp_options_id
INTO x_proj_fp_options_id
FROM pa_proj_fp_options
WHERE fin_plan_option_level_code = PA_FP_CONSTANTS_PKG.G_OPTION_LEVEL_PROJECT
AND project_id = l_proj_id;
/* Following are the Select statements to get the Proj_FP_Options_ID depending on the Option Level
Code. If the Option_Level_Code is Project, then only Project_ID has to be checked for. For
'Plan_Type', both Project_ID and Plan_Type_ID have to be checked and for PLAN_VERSION, all the
three - Project_ID,Plan_Type_ID,Plan_Version_ID have to be checked for. */
IF (l_fp_option_level_code = PA_FP_CONSTANTS_PKG.G_OPTION_LEVEL_PROJECT) THEN
SELECT proj_fp_options_id
INTO x_proj_fp_options_id
FROM pa_proj_fp_options
WHERE project_id = p_project_id
AND fin_plan_option_level_code = PA_FP_CONSTANTS_PKG.G_OPTION_LEVEL_PROJECT;
SELECT proj_fp_options_id
INTO x_proj_fp_options_id
FROM pa_proj_fp_options
WHERE project_id = p_project_id
AND fin_plan_type_id = p_plan_type_id
AND fin_plan_option_level_code = PA_FP_CONSTANTS_PKG.G_OPTION_LEVEL_PLAN_TYPE;
SELECT proj_fp_options_id
INTO x_proj_fp_options_id
FROM pa_proj_fp_options
WHERE project_id = p_project_id
AND fin_plan_type_id = p_plan_type_id
AND fin_plan_version_id = p_plan_version_id
AND fin_plan_option_level_code = PA_FP_CONSTANTS_PKG.G_OPTION_LEVEL_PLAN_VERSION;
- All FP_COLS selects from dual.
23-JAN-2004 rravipat FP M Dev effort Bug 3354518 (IDC)
The api has been modified to default values for new set of
columns introduced during FP M.
05-MAY-2004 rravipat Bug 3572548
generation source version code should be set based on the
source plan type's plan class code. If BUDGET, CURRENT_BASELINED
should be used else CURRENT_APPROVED should be used
15-OCT-2004 rravipat Bug 3934574 Oct 31st DHI enhancements
1) Include Commitments checkbox should always be checked by default
2) Default etc generation source for revenue options is
'Financial Plan'
19-Sep-2004 dbora Bug 4599508: R12 Changes. Refered pa_implemenations_all
instead of pa_implementations as a part of MOAC uptake.
=====================================================================================*/
FUNCTION Get_Default_FP_Options(
p_fin_plan_preference_code IN VARCHAR2 ,
p_target_project_id IN pa_projects_all.project_id%TYPE,
p_plan_type_id IN pa_proj_fp_options.fin_plan_type_id%TYPE) RETURN FP_COLS is
/* Declaring Constants */
l_fin_plan_level_code CONSTANT pa_proj_fp_options.ALL_FIN_PLAN_LEVEL_CODE%TYPE := 'L';
SELECT plan_class_code
,nvl(approved_cost_plan_type_flag,'N') approved_cost_plan_type_flag
,nvl(approved_rev_plan_type_flag,'N') approved_rev_plan_type_flag
,nvl(primary_cost_forecast_flag,'N') primary_cost_forecast_flag
,nvl(primary_rev_forecast_flag,'N') primary_rev_forecast_flag
,nvl(use_for_workplan_flag,'N') use_for_workplan_flag
FROM pa_fin_plan_types_b
WHERE fin_plan_type_id = c_plan_type_id;
SELECT hdrtl.name as name,
ver1.rbs_version_id as rbs_version_id,
ver1.rbs_header_id as rbs_header_id
FROM pa_rbs_headers_b hdrb,
pa_rbs_headers_tl hdrtl,
pa_rbs_versions_b ver1
WHERE sysdate between hdrb.effective_from_date and nvl(hdrb.effective_to_date,sysdate)
AND hdrtl.rbs_header_id = hdrb.rbs_header_id
AND hdrtl.language = USERENV('LANG')
AND ver1.rbs_header_id = hdrtl.rbs_header_id
/*** 3711762
AND ver1.version_number = (select max(version_number)
from pa_rbs_versions_b ver2
where ver1.rbs_header_id =
ver2.rbs_header_id
and ver2.status_code = 'FROZEN')
3711762 ***/
AND ver1.current_reporting_flag = 'Y' /*bug 3711762*/
ORDER BY name asc;
SELECT R1.resource_list_id
INTO l_uncategorized_res_id
FROM pa_resource_lists_all_bg R1,
pa_implementations_all pim,
pa_projects_all prj
WHERE prj.project_id = p_target_project_id
AND pim.org_id = prj.org_id
AND R1.uncategorized_flag = 'Y'
AND R1.business_group_id = pim.business_group_id;
SELECT NVL(baseline_funding_flag,'N')
,NVL(approved_rev_plan_type_flag,'N')
INTO l_autobaseline_flag
,l_app_rev_plan_type_flag
FROM pa_projects_all ppa
,pa_fin_plan_types_b ptb
WHERE ppa.project_id = p_target_project_id
AND ptb.fin_plan_type_id = p_plan_type_id;
SELECT pt.fin_plan_type_id
,DECODE(pt.plan_class_code,
'BUDGET','CURRENT_BASELINED',
'FORECAST','CURRENT_APPROVED') -- Bug 3572548
INTO l_fp_cols_rec.gen_src_cost_plan_type_id
,l_fp_cols_rec.gen_src_cost_plan_ver_code -- Bug 3572548
FROM pa_proj_fp_options o
,pa_fin_plan_types_b pt
WHERE o.project_id = p_target_project_id
AND o.fin_plan_option_level_code = 'PLAN_TYPE'
AND o.fin_plan_type_id = pt.fin_plan_type_id
AND nvl(pt.use_for_workplan_flag,'N') = 'N' -- bug 3429026
AND o.approved_cost_plan_type_flag = 'Y'; --bug 5107742
SELECT *
INTO l_fp_cols_rec.gen_src_cost_plan_type_id
,l_fp_cols_rec.gen_src_cost_plan_ver_code -- Bug 3572548
FROM
(SELECT pt.fin_plan_type_id
,DECODE(pt.plan_class_code,
'BUDGET','CURRENT_BASELINED',
'FORECAST','CURRENT_APPROVED') -- Bug 3572548
FROM pa_proj_fp_options o
,pa_fin_plan_types_vl pt
WHERE o.project_id = p_target_project_id
AND o.fin_plan_option_level_code = 'PLAN_TYPE'
AND o.fin_plan_preference_code <> 'REVENUE_ONLY'
AND nvl(pt.use_for_workplan_flag,'N') = 'N' -- bug 3429026
AND o.fin_plan_type_id = pt.fin_plan_type_id
order by pt.name) a
WHERE ROWNUM = 1;
SELECT pt.fin_plan_type_id
,DECODE(pt.plan_class_code,
'BUDGET','CURRENT_BASELINED',
'FORECAST','CURRENT_APPROVED') -- Bug 3572548
INTO l_fp_cols_rec.gen_src_rev_plan_type_id
,l_fp_cols_rec.gen_src_rev_plan_ver_code -- Bug 3572548
FROM pa_proj_fp_options o
,pa_fin_plan_types_b pt
WHERE o.project_id = p_target_project_id
AND o.fin_plan_option_level_code = 'PLAN_TYPE'
AND o.fin_plan_type_id = pt.fin_plan_type_id
AND nvl(pt.use_for_workplan_flag,'N') = 'N' -- bug 3429026
AND pt.approved_cost_plan_type_flag = 'Y';
SELECT *
INTO l_fp_cols_rec.gen_src_rev_plan_type_id
,l_fp_cols_rec.gen_src_rev_plan_ver_code -- Bug 3572548
FROM (
SELECT pt.fin_plan_type_id
,DECODE(pt.plan_class_code,
'BUDGET','CURRENT_BASELINED',
'FORECAST','CURRENT_APPROVED') -- Bug 3572548
FROM pa_proj_fp_options o
,pa_fin_plan_types_vl pt
WHERE o.project_id = p_target_project_id
AND o.fin_plan_option_level_code = 'PLAN_TYPE'
AND nvl(pt.use_for_workplan_flag,'N') = 'N' -- bug 3429026
AND o.fin_plan_preference_code <> 'REVENUE_ONLY' -- bug 3666398 'COST_ONLY'
AND o.fin_plan_type_id = pt.fin_plan_type_id
order by pt.name ) a
WHERE ROWNUM = 1;
SELECT pt.fin_plan_type_id
,DECODE(pt.plan_class_code,
'BUDGET','CURRENT_BASELINED',
'FORECAST','CURRENT_APPROVED') -- Bug 3572548
INTO l_fp_cols_rec.gen_src_all_plan_type_id
,l_fp_cols_rec.gen_src_all_plan_ver_code -- Bug 3572548
FROM pa_proj_fp_options o
,pa_fin_plan_types_b pt
WHERE o.project_id = p_target_project_id
AND o.fin_plan_option_level_code = 'PLAN_TYPE'
AND o.fin_plan_type_id = pt.fin_plan_type_id
AND nvl(pt.use_for_workplan_flag,'N') = 'N' -- bug 3429026
--bug 3724132 AND pt.approved_rev_plan_type_flag = 'Y'
AND pt.approved_cost_plan_type_flag = 'Y';
SELECT *
INTO l_fp_cols_rec.gen_src_all_plan_type_id
,l_fp_cols_rec.gen_src_all_plan_ver_code -- Bug 3572548
FROM
(SELECT pt.fin_plan_type_id
,DECODE(pt.plan_class_code,
'BUDGET','CURRENT_BASELINED',
'FORECAST','CURRENT_APPROVED') -- Bug 3572548
FROM pa_proj_fp_options o
,pa_fin_plan_types_vl pt
WHERE o.project_id = p_target_project_id
AND o.fin_plan_option_level_code = 'PLAN_TYPE'
AND o.fin_plan_type_id = pt.fin_plan_type_id
AND nvl(pt.use_for_workplan_flag,'N') = 'N' -- bug 3429026
AND o.fin_plan_preference_code <> 'REVENUE_ONLY' -- bug 3666398
ORDER BY pt.name ) a
WHERE ROWNUM = 1;
SELECT approved_cost_plan_type_flag
,approved_rev_plan_type_flag
,primary_cost_forecast_flag
,primary_rev_forecast_flag
,projfunc_cost_rate_type
,projfunc_cost_rate_date_type
,projfunc_cost_rate_date
,projfunc_rev_rate_type
,projfunc_rev_rate_date_type
,projfunc_rev_rate_date
,project_cost_rate_type
,project_cost_rate_date_type
,project_cost_rate_date
,project_rev_rate_type
,project_rev_rate_date_type
,project_rev_rate_date
INTO l_fp_mc_cols_rec
FROM pa_proj_fp_options
WHERE proj_fp_options_id = p_proj_fp_options_id;
SELECT approved_cost_plan_type_flag
,approved_rev_plan_type_flag
,primary_cost_forecast_flag
,primary_rev_forecast_flag
,projfunc_cost_rate_type
,projfunc_cost_rate_date_type
,projfunc_cost_rate_date
,projfunc_rev_rate_type
,projfunc_rev_rate_date_type
,projfunc_rev_rate_date
,project_cost_rate_type
,project_cost_rate_date_type
,project_cost_rate_date
,project_rev_rate_type
,project_rev_rate_date_type
,project_rev_rate_date
INTO l_fp_mc_cols_rec
FROM pa_fin_plan_types_b
WHERE fin_plan_type_id = p_fin_plan_type_id;
is changed in the Edit planning options page, it is updated in
the fp options table for the version. More over the resource
list id and the period profile id(if the time phasing is PA/GL)
should be updated in the budget versions table. This procedure
doesnot do anything if amounts exist for the version.
Bug 3425122: From plan settings page the api would be called to synchronise
the columns that are part of both pa_budget_versions and also pa_proj_fp_otions.
They are resource_list_id, period_mask_id and current_planning_period.
actual_amts_thru_period is a column present only in budget versions table. So,
only this column is passed and rest of the values should be read from
pa_proj_fp_options table
Note: In FP M, period profile concept has been changed to period masks. Commenting
all the related existing code
*/
procedure SYNCHRONIZE_BUDGET_VERSION
(
p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE,
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 --File.Sql.39 bug 4440895
)
IS
l_project_id pa_budget_versions.project_id%TYPE;
l_update_flag varchar2(1);
SELECT cost_period_mask_id,
rev_period_mask_id,
all_period_mask_id,
cost_current_planning_period,
rev_current_planning_period,
all_current_planning_period,
fin_plan_preference_code,
cost_resource_list_id,
revenue_resource_list_id,
all_resource_list_id
FROM pa_proj_fp_options
WHERE fin_plan_version_id = p_budget_version_id;
UPDATE pa_budget_versions
SET current_planning_period = version_option_info_rec.cost_current_planning_period
,period_mask_id = version_option_info_rec.cost_period_mask_id
--- ,actual_amts_thru_period = p_actual_amts_thru_period
,resource_list_id = version_option_info_rec.cost_resource_list_id
,record_version_number = record_version_number + 1
WHERE budget_version_id = p_budget_version_id;
UPDATE pa_budget_versions
SET current_planning_period = version_option_info_rec.rev_current_planning_period
,period_mask_id = version_option_info_rec.rev_period_mask_id
--- ,actual_amts_thru_period = p_actual_amts_thru_period
,resource_list_id = version_option_info_rec.revenue_resource_list_id
,record_version_number = record_version_number + 1
WHERE budget_version_id = p_budget_version_id;
UPDATE pa_budget_versions
SET current_planning_period = version_option_info_rec.all_current_planning_period
,period_mask_id = version_option_info_rec.all_period_mask_id
--- ,actual_amts_thru_period = p_actual_amts_thru_period
,resource_list_id = version_option_info_rec.all_resource_list_id
,record_version_number = record_version_number + 1
WHERE budget_version_id = p_budget_version_id;
select decode(l_time_phased_code,
PA_FP_CONSTANTS_PKG.G_TIME_PHASED_CODE_P,PA_FP_CONSTANTS_PKG.G_PERIOD_TYPE_PA,
PA_FP_CONSTANTS_PKG.G_TIME_PHASED_CODE_G,PA_FP_CONSTANTS_PKG.G_PERIOD_TYPE_GL,l_time_phased_code)
into l_curr_period_type
from dual;
select period_profile_id,project_id
into l_period_profile_id,l_project_id
from pa_budget_versions
where budget_version_id = p_budget_version_id;
pa_debug.g_err_stage := 'Determine if period profile to be updated.If yes get the id';
l_update_flag := 'Y';
l_update_flag := 'N';
l_update_flag := 'Y';
l_update_flag := 'N';
l_update_flag := 'Y';
IF l_update_flag = 'Y' THEN
update pa_budget_versions
set period_profile_id = l_curr_period_profile_id
where budget_version_id = p_budget_version_id;
pa_debug.g_err_stage := 'period profile id updated : updated id -> '||to_char(l_curr_period_profile_id);
SELECT fin_plan_preference_code
FROM pa_proj_fp_options
WHERE project_id = c_project_id
AND fin_plan_type_id = c_fin_plan_type_id
AND fin_plan_option_level_code = 'PLAN_TYPE';
select bv.budget_version_id
from pa_budget_versions bv,
pa_proj_elem_ver_structure ver
where bv.project_id = p_target_project_id
and bv.wp_version_flag = 'Y'
and bv.project_id = ver.project_id
and bv.project_structure_version_id = ver.element_version_id
and ver.LATEST_EFF_PUBLISHED_FLAG = 'Y';
select bv.budget_version_id
from pa_budget_versions bv,
pa_proj_elem_ver_structure ver
where bv.project_id = p_target_project_id
and bv.wp_version_flag = 'Y'
and bv.project_id = ver.project_id
and bv.project_structure_version_id = ver.element_version_id
and ver.current_baseline_date is not null ;
select bv.budget_version_id
from pa_budget_versions bv,
pa_proj_elem_ver_structure ver
where bv.project_id = p_target_project_id
and bv.wp_version_flag = 'Y'
and bv.project_id = ver.project_id
and bv.project_structure_version_id = ver.element_version_id
and ver.current_working_flag = 'Y';
The selected amount types for the layout will be stored using this method.This will
also be used to store the seeded amount types for the layouts.
06-Apr-2005 prachand Created as a part of WebAdi changes.
Initial Creation
===================================================================================*/
PROCEDURE Create_amt_types (
p_project_id IN pa_projects_all.project_id%TYPE
,p_fin_plan_type_id IN pa_fin_plan_types_b.fin_plan_type_id%TYPE
,p_plan_preference_code IN pa_proj_fp_options.fin_plan_preference_code%TYPE
,p_cost_layout_code IN pa_proj_fp_options.cost_layout_code%TYPE
,p_revenue_layout_code IN pa_proj_fp_options.revenue_layout_code%TYPE
,p_all_layout_code IN pa_proj_fp_options.all_layout_code%TYPE
,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 --File.Sql.39 bug 4440895
) IS
l_plan_class_code pa_fin_plan_types_b.plan_class_code%TYPE;
l_sql VARCHAR(3000) := 'SELECT '||
' integrator_code ' ||
' FROM bne_layouts_b '||
' WHERE layout_code = :1 ' ||
' AND application_id = (SELECT application_id ' ||
' FROM FND_APPLICATION ' ||
' WHERE APPLICATION_SHORT_NAME = ''PA'') ' ; -- removed user_name as it not being used.
DELETE FROM PA_FP_PROJ_XL_AMT_TYPES
WHERE project_id = p_project_id
AND fin_plan_type_id = p_fin_plan_type_id;
SELECT plan_class_code
INTO l_plan_class_code
FROM pa_fin_plan_types_b
WHERE fin_plan_type_id = p_fin_plan_type_id;
pa_debug.g_err_stage:= 'Inserting cost seed values into pa_proj_fp_xl_amounts ';
INSERT INTO pa_fp_proj_xl_amt_types (
project_id
,fin_plan_type_id
,option_type
,amount_type_code
,record_version_number
,last_update_date
,last_updated_by
,creation_date
,created_by
,last_update_login
)
VALUES
( p_project_id
, p_fin_plan_type_id
, PA_FP_CONSTANTS_PKG.G_ELEMENT_TYPE_COST
, l_cost_amount_types_tbl(j)
, 1
, sysdate
, fnd_global.user_id
, sysdate
, fnd_global.user_id
, fnd_global.login_id );
pa_debug.g_err_stage:= 'Inserting rev seed values into pa_proj_fp_xl_amounts ';
INSERT INTO pa_fp_proj_xl_amt_types (
project_id
,fin_plan_type_id
,option_type
,amount_type_code
,record_version_number
,last_update_date
,last_updated_by
,creation_date
,created_by
,last_update_login
)
VALUES
( p_project_id
, p_fin_plan_type_id
, PA_FP_CONSTANTS_PKG.G_ELEMENT_TYPE_REVENUE
, l_rev_amount_types_tbl(j)
, 1
, sysdate
, fnd_global.user_id
, sysdate
, fnd_global.user_id
, fnd_global.login_id );
pa_debug.g_err_stage:= 'Inserting all seed values into pa_proj_fp_xl_amounts ';
INSERT INTO pa_fp_proj_xl_amt_types (
project_id
,fin_plan_type_id
,option_type
,amount_type_code
,record_version_number
,last_update_date
,last_updated_by
,creation_date
,created_by
,last_update_login
)
VALUES
( p_project_id
, p_fin_plan_type_id
, PA_FP_CONSTANTS_PKG.G_ELEMENT_TYPE_ALL
, l_all_amount_types_tbl(j)
, 1
, sysdate
, fnd_global.user_id
, sysdate
, fnd_global.user_id
, fnd_global.login_id );
pa_debug.g_err_stage:= 'Inserting all seed values ' || l_all_amount_types_tbl(j);
INSERT INTO PA_FP_PROJ_XL_AMT_TYPES(
project_id
,fin_plan_type_id
,option_type
,amount_type_code
,record_version_number
,last_update_date
,last_updated_by
,creation_date
,created_by
,last_update_login)
SELECT p_target_project_id
,p_target_fin_plan_type_id
,option_type
,amount_type_code
,1
,sysdate
,fnd_global.user_id
,sysdate
,fnd_global.user_id
,fnd_global.user_id
FROM PA_FP_PROJ_XL_AMT_TYPES WHERE
project_id = p_source_project_id AND
fin_plan_type_id = p_source_fin_plan_type_id ;
PROCEDURE update_amt_types (
p_project_id IN pa_projects_all.project_id%TYPE
,p_fin_plan_type_id IN pa_fin_plan_types_b.fin_plan_type_id%TYPE
,p_add_cost_amt_types_tbl IN SYSTEM.pa_varchar2_30_tbl_type
,p_del_cost_amt_types_tbl IN SYSTEM.pa_varchar2_30_tbl_type
,p_add_rev_amt_types_tbl IN SYSTEM.pa_varchar2_30_tbl_type
,p_del_rev_amt_types_tbl IN SYSTEM.pa_varchar2_30_tbl_type
,p_add_all_amt_types_tbl IN SYSTEM.pa_varchar2_30_tbl_type
,p_del_all_amt_types_tbl IN SYSTEM.pa_varchar2_30_tbl_type
,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 --File.Sql.39 bug 4440895
) IS
l_stage NUMBER := 100;
pa_debug.g_err_stage:= 'Inserting cost amount types into pa_proj_fp_xl_amounts ';
INSERT into pa_fp_proj_xl_amt_types (
project_id
,fin_plan_type_id
,option_type
,amount_type_code
,record_version_number
,last_update_date
,last_updated_by
,creation_date
,created_by
,last_update_login )
VALUES
( p_project_id
,p_fin_plan_type_id
,PA_FP_CONSTANTS_PKG.G_ELEMENT_TYPE_COST
,p_add_cost_amt_types_tbl(j)
,1
,sysdate
,fnd_global.user_id
,sysdate
,fnd_global.user_id
,fnd_global.user_id );
DELETE FROM pa_fp_proj_xl_amt_types WHERE
project_id = p_project_id AND
fin_plan_type_id = p_fin_plan_type_id AND
option_type = PA_FP_CONSTANTS_PKG.G_ELEMENT_TYPE_COST AND
amount_type_code = p_del_cost_amt_types_tbl(j) ;
pa_debug.g_err_stage:= 'Inserting rev amount types into pa_proj_fp_xl_amounts ';
INSERT into pa_fp_proj_xl_amt_types (
project_id
,fin_plan_type_id
,option_type
,amount_type_code
,record_version_number
,last_update_date
,last_updated_by
,creation_date
,created_by
,last_update_login )
VALUES
( p_project_id
,p_fin_plan_type_id
,PA_FP_CONSTANTS_PKG.G_ELEMENT_TYPE_REVENUE
,p_add_rev_amt_types_tbl(j)
,1
,sysdate
,fnd_global.user_id
,sysdate
,fnd_global.user_id
,fnd_global.user_id );
DELETE FROM pa_fp_proj_xl_amt_types WHERE
project_id = p_project_id AND
fin_plan_type_id = p_fin_plan_type_id AND
option_type = PA_FP_CONSTANTS_PKG.G_ELEMENT_TYPE_REVENUE AND
amount_type_code = p_del_rev_amt_types_tbl(j) ;
pa_debug.g_err_stage:= 'Inserting all amount types into pa_proj_fp_xl_amounts ';
pa_debug.g_err_stage:= 'Inserting value:::: ' || p_add_all_amt_types_tbl(j) ;
INSERT into pa_fp_proj_xl_amt_types (
project_id
,fin_plan_type_id
,option_type
,amount_type_code
,record_version_number
,last_update_date
,last_updated_by
,creation_date
,created_by
,last_update_login )
VALUES
( p_project_id
,p_fin_plan_type_id
,PA_FP_CONSTANTS_PKG.G_ELEMENT_TYPE_ALL
,p_add_all_amt_types_tbl(j)
,1
,sysdate
,fnd_global.user_id
,sysdate
,fnd_global.user_id
,fnd_global.user_id );
DELETE FROM pa_fp_proj_xl_amt_types WHERE
project_id = p_project_id AND
fin_plan_type_id = p_fin_plan_type_id AND
option_type = PA_FP_CONSTANTS_PKG.G_ELEMENT_TYPE_ALL AND
amount_type_code = p_del_all_amt_types_tbl(j) ;
,p_procedure_name => 'update_amt_types');
END update_amt_types;