1 package PA_PROJ_FP_OPTIONS_PUB AUTHID CURRENT_USER as
2 /* $Header: PAFPOPPS.pls 120.5 2012/01/03 14:47:27 a5sharma ship $ */
3
4 /* Record for Project FP Options
5
6 This record will be used to populate the following columns of Project Fin Planning Options
7 as these columns are most frequently used as input or output parameters to many of the APIs. */
8
9 Invalid_Arg_Exc EXCEPTION;
10
11 -- Bug 3362316, 08-JAN-2003: Added New FP.M Columns --------------------------
12
13 TYPE FP_COLS IS RECORD (
14 Fin_Plan_Start_Date PA_PROJ_FP_OPTIONS.FIN_PLAN_START_DATE%TYPE
15 ,Fin_Plan_End_Date PA_PROJ_FP_OPTIONS.FIN_PLAN_END_DATE%TYPE
16 ,Cost_Amount_Set_ID NUMBER -- Bug 3591144 PA_PROJ_FP_OPTIONS.COST_AMOUNT_SET_ID%TYPE
17 ,Revenue_Amount_Set_ID NUMBER -- Bug 3591144 PA_PROJ_FP_OPTIONS.REVENUE_AMOUNT_SET_ID%TYPE
18 ,All_Amount_Set_ID NUMBER -- Bug 3591144 PA_PROJ_FP_OPTIONS.ALL_AMOUNT_SET_ID%TYPE
19 ,Cost_Fin_Plan_Level_Code PA_PROJ_FP_OPTIONS.COST_FIN_PLAN_LEVEL_CODE%TYPE
20 ,Cost_Time_Phased_Code PA_PROJ_FP_OPTIONS.COST_TIME_PHASED_CODE%TYPE
21 ,Cost_Resource_List_ID NUMBER -- Bug 3591144 PA_PROJ_FP_OPTIONS.COST_RESOURCE_LIST_ID%TYPE
22 ,Revenue_Fin_Plan_Level_Code PA_PROJ_FP_OPTIONS.REVENUE_FIN_PLAN_LEVEL_CODE%TYPE
23 ,Revenue_Time_Phased_Code PA_PROJ_FP_OPTIONS.REVENUE_TIME_PHASED_CODE%TYPE
24 ,Revenue_Resource_List_ID NUMBER -- Bug 3591144 PA_PROJ_FP_OPTIONS.REVENUE_RESOURCE_LIST_ID%TYPE
25 ,All_Fin_Plan_Level_Code PA_PROJ_FP_OPTIONS.ALL_FIN_PLAN_LEVEL_CODE%TYPE
26 ,All_Time_Phased_Code PA_PROJ_FP_OPTIONS.ALL_TIME_PHASED_CODE%TYPE
27 ,All_Resource_List_ID NUMBER -- Bug 3591144 PA_PROJ_FP_OPTIONS.ALL_RESOURCE_LIST_ID%TYPE
28 ,Report_Labor_Hrs_From_Code PA_PROJ_FP_OPTIONS.REPORT_LABOR_HRS_FROM_CODE%TYPE
29 ,Plan_In_Multi_Curr_Flag PA_PROJ_FP_OPTIONS.PLAN_IN_MULTI_CURR_FLAG%TYPE
30 ,Factor_By_Code PA_PROJ_FP_OPTIONS.FACTOR_BY_CODE%TYPE
31 ,Default_Amount_Type_Code PA_PROJ_FP_OPTIONS.DEFAULT_AMOUNT_TYPE_CODE%TYPE
32 ,default_amount_subtype_code PA_PROJ_FP_OPTIONS.default_amount_subtype_code%TYPE
33 ,margin_derived_from_code PA_PROJ_FP_OPTIONS.margin_derived_from_code%TYPE
34 /* Bug 2920954 start of columns included for post FP-K oneoff patch*/
35 ,select_cost_res_auto_flag PA_PROJ_FP_OPTIONS.select_cost_res_auto_flag%TYPE
36 ,cost_res_planning_level PA_PROJ_FP_OPTIONS.cost_res_planning_level%TYPE
37 ,select_rev_res_auto_flag PA_PROJ_FP_OPTIONS.select_rev_res_auto_flag%TYPE
38 ,revenue_res_planning_level PA_PROJ_FP_OPTIONS.revenue_res_planning_level%TYPE
39 ,select_all_res_auto_flag PA_PROJ_FP_OPTIONS.select_all_res_auto_flag%TYPE
40 ,all_res_planning_level PA_PROJ_FP_OPTIONS.all_res_planning_level%TYPE
41 /* Bug 2920954 end of columns included for post FP-K oneoff patch*/
42 , use_planning_rates_flag PA_PROJ_FP_OPTIONS.use_planning_rates_flag%TYPE
43 , rbs_version_id NUMBER -- Bug 3591144 PA_PROJ_FP_OPTIONS.rbs_version_id%TYPE
44 , res_class_raw_cost_sch_id NUMBER -- Bug 3591144 PA_PROJ_FP_OPTIONS.res_class_raw_cost_sch_id%TYPE
45 , res_class_bill_rate_sch_id NUMBER -- Bug 3591144 PA_PROJ_FP_OPTIONS.res_class_bill_rate_sch_id%TYPE
46 , cost_emp_rate_sch_id NUMBER -- Bug 3591144 PA_PROJ_FP_OPTIONS.cost_emp_rate_sch_id%TYPE
47 , cost_job_rate_sch_id NUMBER -- Bug 3591144 PA_PROJ_FP_OPTIONS.cost_job_rate_sch_id%TYPE
48 , cost_non_labor_res_rate_sch_id NUMBER -- Bug 3591144 PA_PROJ_FP_OPTIONS.cost_non_labor_res_rate_sch_id%TYPE
49 , cost_res_class_rate_sch_id NUMBER -- Bug 3591144 PA_PROJ_FP_OPTIONS.cost_res_class_rate_sch_id%TYPE
50 , cost_burden_rate_sch_id NUMBER -- Bug 3591144 PA_PROJ_FP_OPTIONS.cost_burden_rate_sch_id%TYPE
51 , cost_current_planning_period PA_PROJ_FP_OPTIONS.cost_current_planning_period%TYPE
52 , cost_period_mask_id PA_PROJ_FP_OPTIONS.cost_period_mask_id%TYPE
53 , rev_emp_rate_sch_id NUMBER -- Bug 3591144 PA_PROJ_FP_OPTIONS.rev_emp_rate_sch_id%TYPE
54 , rev_job_rate_sch_id NUMBER -- Bug 3591144 PA_PROJ_FP_OPTIONS.rev_job_rate_sch_id%TYPE
55 , rev_non_labor_res_rate_sch_id NUMBER -- Bug 3591144 PA_PROJ_FP_OPTIONS.rev_non_labor_res_rate_sch_id%TYPE
56 , rev_res_class_rate_sch_id NUMBER -- Bug 3591144 PA_PROJ_FP_OPTIONS.rev_res_class_rate_sch_id%TYPE
57 , rev_current_planning_period PA_PROJ_FP_OPTIONS.rev_current_planning_period%TYPE
58 , rev_period_mask_id NUMBER -- Bug 3591144 PA_PROJ_FP_OPTIONS.rev_period_mask_id%TYPE
59 , all_current_planning_period PA_PROJ_FP_OPTIONS.all_current_planning_period%TYPE
60 , all_period_mask_id NUMBER -- Bug 3591144 PA_PROJ_FP_OPTIONS.all_period_mask_id%TYPE
61 , gen_cost_src_code PA_PROJ_FP_OPTIONS.gen_cost_src_code%TYPE
62 , gen_cost_etc_src_code PA_PROJ_FP_OPTIONS.gen_cost_etc_src_code%TYPE
63 , gen_cost_incl_change_doc_flag PA_PROJ_FP_OPTIONS.gen_cost_incl_change_doc_flag%TYPE
64 , gen_cost_incl_open_comm_flag PA_PROJ_FP_OPTIONS.gen_cost_incl_open_comm_flag%TYPE
65 , gen_cost_ret_manual_line_flag PA_PROJ_FP_OPTIONS.gen_cost_ret_manual_line_flag%TYPE
66 , gen_cost_incl_unspent_amt_flag PA_PROJ_FP_OPTIONS.gen_cost_incl_unspent_amt_flag%TYPE
67 , gen_rev_src_code PA_PROJ_FP_OPTIONS.gen_rev_src_code%TYPE
68 , gen_rev_etc_src_code PA_PROJ_FP_OPTIONS.gen_rev_etc_src_code%TYPE
69 , gen_rev_incl_change_doc_flag PA_PROJ_FP_OPTIONS.gen_rev_incl_change_doc_flag%TYPE
70 , gen_rev_incl_bill_event_flag PA_PROJ_FP_OPTIONS.gen_rev_incl_bill_event_flag%TYPE
71 , gen_rev_ret_manual_line_flag PA_PROJ_FP_OPTIONS.gen_rev_ret_manual_line_flag%TYPE
72 , gen_src_cost_plan_type_id NUMBER -- Bug 3591144 PA_PROJ_FP_OPTIONS.gen_src_cost_plan_type_id%TYPE
73 , gen_src_cost_plan_version_id NUMBER -- Bug 3591144 PA_PROJ_FP_OPTIONS.gen_src_cost_plan_version_id%TYPE
74 , gen_src_cost_plan_ver_code PA_PROJ_FP_OPTIONS.gen_src_cost_plan_ver_code%TYPE
75 , gen_src_rev_plan_type_id NUMBER -- Bug 3591144 PA_PROJ_FP_OPTIONS.gen_src_rev_plan_type_id%TYPE
76 , gen_src_rev_plan_version_id NUMBER -- Bug 3591144 PA_PROJ_FP_OPTIONS.gen_src_rev_plan_version_id%TYPE
77 , gen_src_rev_plan_ver_code PA_PROJ_FP_OPTIONS.gen_src_rev_plan_ver_code%TYPE
78 , gen_src_all_plan_type_id NUMBER -- Bug 3591144 PA_PROJ_FP_OPTIONS.gen_src_all_plan_type_id%TYPE
79 , gen_src_all_plan_version_id NUMBER -- Bug 3591144 PA_PROJ_FP_OPTIONS.gen_src_all_plan_version_id%TYPE
80 , gen_src_all_plan_ver_code PA_PROJ_FP_OPTIONS.gen_src_all_plan_ver_code%TYPE
81 , gen_all_src_code PA_PROJ_FP_OPTIONS.gen_all_src_code%TYPE
82 , gen_all_etc_src_code PA_PROJ_FP_OPTIONS.gen_all_etc_src_code%TYPE
83 , gen_all_incl_change_doc_flag PA_PROJ_FP_OPTIONS.gen_all_incl_change_doc_flag%TYPE
84 , gen_all_incl_open_comm_flag PA_PROJ_FP_OPTIONS.gen_all_incl_open_comm_flag%TYPE
85 , gen_all_ret_manual_line_flag PA_PROJ_FP_OPTIONS.gen_all_ret_manual_line_flag%TYPE
86 , gen_all_incl_bill_event_flag PA_PROJ_FP_OPTIONS.gen_all_incl_bill_event_flag%TYPE
90 , gen_all_actual_amts_thru_code PA_PROJ_FP_OPTIONS.gen_all_actual_amts_thru_code%TYPE
87 , gen_all_incl_unspent_amt_flag PA_PROJ_FP_OPTIONS.gen_all_incl_unspent_amt_flag%TYPE
88 , gen_cost_actual_amts_thru_code PA_PROJ_FP_OPTIONS.gen_cost_actual_amts_thru_code%TYPE
89 , gen_rev_actual_amts_thru_code PA_PROJ_FP_OPTIONS.gen_rev_actual_amts_thru_code%TYPE
91 , track_workplan_costs_flag PA_PROJ_FP_OPTIONS.track_workplan_costs_flag%TYPE
92 -- bug 3519062 addition of new columns used for workplan generation source setup
93 , gen_src_cost_wp_version_id NUMBER -- Bug 3591144 PA_PROJ_FP_OPTIONS.gen_src_cost_wp_version_id%TYPE
94 , gen_src_cost_wp_ver_code PA_PROJ_FP_OPTIONS.gen_src_cost_wp_ver_code%TYPE
95 , gen_src_rev_wp_version_id NUMBER -- Bug 3591144 PA_PROJ_FP_OPTIONS.gen_src_rev_wp_version_id%TYPE
96 , gen_src_rev_wp_ver_code PA_PROJ_FP_OPTIONS.gen_src_rev_wp_ver_code%TYPE
97 , gen_src_all_wp_version_id NUMBER -- Bug 3591144 PA_PROJ_FP_OPTIONS.gen_src_all_wp_version_id%TYPE
98 , gen_src_all_wp_ver_code PA_PROJ_FP_OPTIONS.gen_src_all_wp_ver_code%TYPE
99 , cost_layout_code PA_PROJ_FP_OPTIONS.cost_layout_code%TYPE
100 , revenue_layout_code PA_PROJ_FP_OPTIONS.revenue_layout_code%TYPE
101 , all_layout_code PA_PROJ_FP_OPTIONS.all_layout_code%TYPE
102 , revenue_derivation_method PA_PROJ_FP_OPTIONS.revenue_derivation_method%TYPE --Bug 5462471
103 , copy_etc_from_plan_flag PA_PROJ_FP_OPTIONS.copy_etc_from_plan_flag%TYPE --bug 8318932
104 , default_raw_cost PA_PROJ_FP_OPTIONS.default_raw_cost%TYPE --CBS
105 , default_bill_rate PA_PROJ_FP_OPTIONS.default_bill_rate%TYPE --CBS
106 , def_markup_percentage PA_PROJ_FP_OPTIONS.DEF_MARKUP_PERCENTAGE%TYPE --CBS
107 , def_raw_cost_currency_code PA_PROJ_FP_OPTIONS.DEF_RAW_COST_CURRENCY_CODE%TYPE --CBS
108 , def_bill_rate_currency_code PA_PROJ_FP_OPTIONS.DEF_BILL_RATE_CURRENCY_CODE%TYPE --CBS
109 );
110
111 -- End: Bug 3362316, 08-JAN-2003: Added New FP.M Columns --------------------------
112
113
114 TYPE FP_MC_COLS IS RECORD (
115 approved_cost_plan_type_flag PA_PROJ_FP_OPTIONS.approved_cost_plan_type_flag%TYPE
116 ,approved_rev_plan_type_flag PA_PROJ_FP_OPTIONS.approved_rev_plan_type_flag%TYPE
117 -- FPM Dev effort bug 3354518 included two new columns
118 ,primary_cost_forecast_flag PA_PROJ_FP_OPTIONS.primary_cost_forecast_flag%TYPE
119 ,primary_rev_forecast_flag PA_PROJ_FP_OPTIONS.primary_rev_forecast_flag%TYPE
120 -- FPM Dev effort bug 3354518 included two new columns
121 ,projfunc_cost_rate_type PA_PROJ_FP_OPTIONS.projfunc_cost_rate_type%TYPE
122 ,projfunc_cost_rate_date_type PA_PROJ_FP_OPTIONS.projfunc_cost_rate_date_type%TYPE
123 ,projfunc_cost_rate_date PA_PROJ_FP_OPTIONS.projfunc_cost_rate_date%TYPE
124 ,projfunc_rev_rate_type PA_PROJ_FP_OPTIONS.projfunc_rev_rate_type%TYPE
125 ,projfunc_rev_rate_date_type PA_PROJ_FP_OPTIONS.projfunc_rev_rate_date_type%TYPE
126 ,projfunc_rev_rate_date PA_PROJ_FP_OPTIONS.projfunc_rev_rate_date%TYPE
127 ,project_cost_rate_type PA_PROJ_FP_OPTIONS.project_cost_rate_type%TYPE
128 ,project_cost_rate_date_type PA_PROJ_FP_OPTIONS.project_cost_rate_date_type%TYPE
129 ,project_cost_rate_date PA_PROJ_FP_OPTIONS.project_cost_rate_date%TYPE
130 ,project_rev_rate_type PA_PROJ_FP_OPTIONS.project_rev_rate_type%TYPE
131 ,project_rev_rate_date_type PA_PROJ_FP_OPTIONS.project_rev_rate_date_type%TYPE
132 ,project_rev_rate_date PA_PROJ_FP_OPTIONS.project_rev_rate_date%TYPE
133 );
134
135 PROCEDURE Create_FP_Option (
136 px_target_proj_fp_option_id IN OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
137 ,p_source_proj_fp_option_id IN NUMBER
138 ,p_target_fp_option_level_code IN VARCHAR2
139 ,p_target_fp_preference_code IN VARCHAR2
140 ,p_target_fin_plan_version_id IN NUMBER
141 ,p_target_project_id IN NUMBER
142 ,p_target_plan_type_id IN NUMBER
143 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
144 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
145 ,x_msg_data OUT NOCOPY VARCHAR2 ); --File.Sql.39 bug 4440895
146
147 PROCEDURE GET_FP_OPTIONS(
148 p_proj_fp_options_id IN NUMBER
149 ,p_target_fp_options_id IN NUMBER DEFAULT NULL
150 ,p_fin_plan_preference_code IN VARCHAR2
151 ,p_target_fp_option_level_code IN VARCHAR2 -- Added for ms-excel options for web adi
152 ,x_fp_cols_rec OUT NOCOPY FP_COLS
153 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
154 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
155 ,x_msg_data OUT NOCOPY VARCHAR2); --File.Sql.39 bug 4440895
156
157 /* Bug # 2618119 */
158 procedure SYNCHRONIZE_BUDGET_VERSION
159 (
160 p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE,
161 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
162 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
163 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
164 );
165
166 FUNCTION GET_PARENT_FP_OPTION_ID(
170 p_project_id IN NUMBER
167 p_proj_fp_options_id IN NUMBER ) RETURN PA_PROJ_FP_OPTIONS.PROJ_FP_OPTIONS_ID%TYPE;
168
169 FUNCTION GET_FP_OPTION_ID(
171 ,p_plan_type_id IN NUMBER
172 ,p_plan_version_id IN NUMBER ) RETURN PA_PROJ_FP_OPTIONS.PROJ_FP_OPTIONS_ID%TYPE;
173
174 FUNCTION GET_DEFAULT_FP_OPTIONS(
175 p_fin_plan_preference_code IN VARCHAR2,
176 p_target_project_id IN pa_projects_all.project_id%TYPE,
177 p_plan_type_id IN pa_proj_fp_options.fin_plan_type_id%TYPE ) RETURN FP_COLS;
178
179 FUNCTION GET_FP_PROJ_MC_OPTIONS (p_proj_fp_options_id IN NUMBER) Return FP_MC_COLS;
180
181 FUNCTION GET_FP_PLAN_TYPE_MC_OPTIONS (p_fin_plan_type_id IN NUMBER) Return FP_MC_COLS;
182
183 /*=====================================================================================
184 This is a private api that would return gen src plan version id for a given option
185 based on project id, target version type, gen src plan type id and gen src plan
186 version code inputs
187
188 23-JAN-2004 rravipat FP M Dev effort Bug 3354518 (IDC)
189 Initial Creation
190 =====================================================================================*/
191 FUNCTION Gen_Src_Plan_Version_Id(
192 p_target_project_id IN pa_projects_all.project_id%TYPE,
193 p_target_version_type IN pa_budget_versions.version_type%TYPE,
194 p_gen_src_plan_type_id IN pa_proj_fp_options.gen_src_cost_plan_type_id%TYPE,
195 p_gen_src_plan_ver_code IN pa_proj_fp_options.gen_src_cost_plan_ver_code%TYPE)
196 RETURN pa_budget_versions.budget_version_id%TYPE;
197
198 /*=====================================================================================
199 This is a private api that would return gen src wokplan budget version id for a given
200 option based on project id and gen src workplan version code inputs
201
202 20-MAR-2004 rravipat FP M Dev effort Phase II changes
203 Initial Creation
204 =====================================================================================*/
205
206 FUNCTION Gen_Src_WP_Version_Id(
207 p_target_project_id IN pa_projects_all.project_id%TYPE,
208 p_gen_src_wp_ver_code IN pa_proj_fp_options.gen_src_cost_wp_ver_code%TYPE)
209 RETURN pa_budget_versions.budget_version_id%TYPE;
210
211 /*==================================================================================
212 This procedure is used to create the seeded view for the periodic budget or forcasts
213 The selected amount types for the layout will be stored using this method.This will
214 also be used to store the seeded amount types for the layouts.
215
216 06-Apr-2005 prachand Created as a part of WebAdi changes.
217 Initial Creation
218 ===================================================================================*/
219 PROCEDURE Create_amt_types (
220 p_project_id IN pa_projects_all.project_id%TYPE
221 ,p_fin_plan_type_id IN pa_fin_plan_types_b.fin_plan_type_id%TYPE
222 ,p_plan_preference_code IN pa_proj_fp_options.fin_plan_preference_code%TYPE
223 ,p_cost_layout_code IN pa_proj_fp_options.cost_layout_code%TYPE
224 ,p_revenue_layout_code IN pa_proj_fp_options.revenue_layout_code%TYPE
225 ,p_all_layout_code IN pa_proj_fp_options.all_layout_code%TYPE
226 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
227 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
228 ,x_msg_data OUT NOCOPY VARCHAR2 ); --File.Sql.39 bug 4440895
229
230 /*==================================================================================
231 This procedure is used to copy the amount types for the periodic budget or forcasts
232 from an existing plan type The existing plan types amount types will be copied to the
233 new project or plan type when a copy is done.
234
235 06-Apr-2005 prachand Created as a part of WebAdi changes.
236 Initial Creation
237 ===================================================================================*/
238
239 PROCEDURE copy_amt_types (
240 p_source_project_id IN pa_projects_all.project_id%TYPE
241 ,p_source_fin_plan_type_id IN pa_fin_plan_types_b.fin_plan_type_id%TYPE
242 ,p_target_project_id IN pa_projects_all.project_id%TYPE
243 ,p_target_fin_plan_type_id IN pa_fin_plan_types_b.fin_plan_type_id%TYPE
244 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
245 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
246 ,x_msg_data OUT NOCOPY VARCHAR2 ); --File.Sql.39 bug 4440895
247
248 /*==================================================================================
249 This procedure is used to update the amount types for the periodic budget or forcasts
250 of an existing plan type The existing plan types amount types will be updated to the
251 new selected amount types.
252
253 06-Apr-2005 prachand Created as a part of WebAdi changes.
254 Initial Creation
255 ===================================================================================*/
256
257
258 PROCEDURE update_amt_types (
259 p_project_id IN pa_projects_all.project_id%TYPE
260 ,p_fin_plan_type_id IN pa_fin_plan_types_b.fin_plan_type_id%TYPE
261 ,p_add_cost_amt_types_tbl IN SYSTEM.pa_varchar2_30_tbl_type
262 ,p_del_cost_amt_types_tbl IN SYSTEM.pa_varchar2_30_tbl_type
263 ,p_add_rev_amt_types_tbl IN SYSTEM.pa_varchar2_30_tbl_type
264 ,p_del_rev_amt_types_tbl IN SYSTEM.pa_varchar2_30_tbl_type
265 ,p_add_all_amt_types_tbl IN SYSTEM.pa_varchar2_30_tbl_type
266 ,p_del_all_amt_types_tbl IN SYSTEM.pa_varchar2_30_tbl_type
267 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
268 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
269 ,x_msg_data OUT NOCOPY VARCHAR2 ) ; --File.Sql.39 bug 4440895
270
271
272 end PA_PROJ_FP_OPTIONS_PUB;
273
[Home] [Help]