1 package PA_PROJ_FP_OPTIONS_PUB as
2 /* $Header: PAFPOPPS.pls 120.3 2007/02/06 10:02:15 dthakker 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
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
90 , gen_all_actual_amts_thru_code PA_PROJ_FP_OPTIONS.gen_all_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 );
104
105 -- End: Bug 3362316, 08-JAN-2003: Added New FP.M Columns --------------------------
106
107
108 TYPE FP_MC_COLS IS RECORD (
109 approved_cost_plan_type_flag PA_PROJ_FP_OPTIONS.approved_cost_plan_type_flag%TYPE
110 ,approved_rev_plan_type_flag PA_PROJ_FP_OPTIONS.approved_rev_plan_type_flag%TYPE
111 -- FPM Dev effort bug 3354518 included two new columns
112 ,primary_cost_forecast_flag PA_PROJ_FP_OPTIONS.primary_cost_forecast_flag%TYPE
113 ,primary_rev_forecast_flag PA_PROJ_FP_OPTIONS.primary_rev_forecast_flag%TYPE
114 -- FPM Dev effort bug 3354518 included two new columns
115 ,projfunc_cost_rate_type PA_PROJ_FP_OPTIONS.projfunc_cost_rate_type%TYPE
116 ,projfunc_cost_rate_date_type PA_PROJ_FP_OPTIONS.projfunc_cost_rate_date_type%TYPE
117 ,projfunc_cost_rate_date PA_PROJ_FP_OPTIONS.projfunc_cost_rate_date%TYPE
118 ,projfunc_rev_rate_type PA_PROJ_FP_OPTIONS.projfunc_rev_rate_type%TYPE
119 ,projfunc_rev_rate_date_type PA_PROJ_FP_OPTIONS.projfunc_rev_rate_date_type%TYPE
120 ,projfunc_rev_rate_date PA_PROJ_FP_OPTIONS.projfunc_rev_rate_date%TYPE
121 ,project_cost_rate_type PA_PROJ_FP_OPTIONS.project_cost_rate_type%TYPE
122 ,project_cost_rate_date_type PA_PROJ_FP_OPTIONS.project_cost_rate_date_type%TYPE
123 ,project_cost_rate_date PA_PROJ_FP_OPTIONS.project_cost_rate_date%TYPE
124 ,project_rev_rate_type PA_PROJ_FP_OPTIONS.project_rev_rate_type%TYPE
125 ,project_rev_rate_date_type PA_PROJ_FP_OPTIONS.project_rev_rate_date_type%TYPE
126 ,project_rev_rate_date PA_PROJ_FP_OPTIONS.project_rev_rate_date%TYPE
127 );
128
129 PROCEDURE Create_FP_Option (
130 px_target_proj_fp_option_id IN OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
131 ,p_source_proj_fp_option_id IN NUMBER
132 ,p_target_fp_option_level_code IN VARCHAR2
133 ,p_target_fp_preference_code IN VARCHAR2
134 ,p_target_fin_plan_version_id IN NUMBER
135 ,p_target_project_id IN NUMBER
136 ,p_target_plan_type_id IN NUMBER
137 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
138 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
139 ,x_msg_data OUT NOCOPY VARCHAR2 ); --File.Sql.39 bug 4440895
140
141 PROCEDURE GET_FP_OPTIONS(
142 p_proj_fp_options_id IN NUMBER
143 ,p_target_fp_options_id IN NUMBER DEFAULT NULL
144 ,p_fin_plan_preference_code IN VARCHAR2
145 ,p_target_fp_option_level_code IN VARCHAR2 -- Added for ms-excel options for web adi
146 ,x_fp_cols_rec OUT NOCOPY FP_COLS
147 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
148 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
149 ,x_msg_data OUT NOCOPY VARCHAR2); --File.Sql.39 bug 4440895
150
151 /* Bug # 2618119 */
152 procedure SYNCHRONIZE_BUDGET_VERSION
153 (
154 p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE,
155 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
156 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
157 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
158 );
159
160 FUNCTION GET_PARENT_FP_OPTION_ID(
161 p_proj_fp_options_id IN NUMBER ) RETURN PA_PROJ_FP_OPTIONS.PROJ_FP_OPTIONS_ID%TYPE;
162
163 FUNCTION GET_FP_OPTION_ID(
164 p_project_id IN NUMBER
165 ,p_plan_type_id IN NUMBER
166 ,p_plan_version_id IN NUMBER ) RETURN PA_PROJ_FP_OPTIONS.PROJ_FP_OPTIONS_ID%TYPE;
167
168 FUNCTION GET_DEFAULT_FP_OPTIONS(
169 p_fin_plan_preference_code IN VARCHAR2,
170 p_target_project_id IN pa_projects_all.project_id%TYPE,
171 p_plan_type_id IN pa_proj_fp_options.fin_plan_type_id%TYPE ) RETURN FP_COLS;
172
173 FUNCTION GET_FP_PROJ_MC_OPTIONS (p_proj_fp_options_id IN NUMBER) Return FP_MC_COLS;
174
175 FUNCTION GET_FP_PLAN_TYPE_MC_OPTIONS (p_fin_plan_type_id IN NUMBER) Return FP_MC_COLS;
176
177 /*=====================================================================================
178 This is a private api that would return gen src plan version id for a given option
179 based on project id, target version type, gen src plan type id and gen src plan
180 version code inputs
181
182 23-JAN-2004 rravipat FP M Dev effort Bug 3354518 (IDC)
183 Initial Creation
184 =====================================================================================*/
185 FUNCTION Gen_Src_Plan_Version_Id(
186 p_target_project_id IN pa_projects_all.project_id%TYPE,
187 p_target_version_type IN pa_budget_versions.version_type%TYPE,
188 p_gen_src_plan_type_id IN pa_proj_fp_options.gen_src_cost_plan_type_id%TYPE,
189 p_gen_src_plan_ver_code IN pa_proj_fp_options.gen_src_cost_plan_ver_code%TYPE)
190 RETURN pa_budget_versions.budget_version_id%TYPE;
191
192 /*=====================================================================================
193 This is a private api that would return gen src wokplan budget version id for a given
194 option based on project id and gen src workplan version code inputs
195
196 20-MAR-2004 rravipat FP M Dev effort Phase II changes
197 Initial Creation
198 =====================================================================================*/
199
200 FUNCTION Gen_Src_WP_Version_Id(
201 p_target_project_id IN pa_projects_all.project_id%TYPE,
202 p_gen_src_wp_ver_code IN pa_proj_fp_options.gen_src_cost_wp_ver_code%TYPE)
203 RETURN pa_budget_versions.budget_version_id%TYPE;
204
205 /*==================================================================================
206 This procedure is used to create the seeded view for the periodic budget or forcasts
207 The selected amount types for the layout will be stored using this method.This will
208 also be used to store the seeded amount types for the layouts.
209
210 06-Apr-2005 prachand Created as a part of WebAdi changes.
211 Initial Creation
212 ===================================================================================*/
213 PROCEDURE Create_amt_types (
214 p_project_id IN pa_projects_all.project_id%TYPE
215 ,p_fin_plan_type_id IN pa_fin_plan_types_b.fin_plan_type_id%TYPE
216 ,p_plan_preference_code IN pa_proj_fp_options.fin_plan_preference_code%TYPE
217 ,p_cost_layout_code IN pa_proj_fp_options.cost_layout_code%TYPE
218 ,p_revenue_layout_code IN pa_proj_fp_options.revenue_layout_code%TYPE
219 ,p_all_layout_code IN pa_proj_fp_options.all_layout_code%TYPE
220 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
221 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
222 ,x_msg_data OUT NOCOPY VARCHAR2 ); --File.Sql.39 bug 4440895
223
224 /*==================================================================================
225 This procedure is used to copy the amount types for the periodic budget or forcasts
226 from an existing plan type The existing plan types amount types will be copied to the
227 new project or plan type when a copy is done.
228
229 06-Apr-2005 prachand Created as a part of WebAdi changes.
230 Initial Creation
231 ===================================================================================*/
232
233 PROCEDURE copy_amt_types (
234 p_source_project_id IN pa_projects_all.project_id%TYPE
235 ,p_source_fin_plan_type_id IN pa_fin_plan_types_b.fin_plan_type_id%TYPE
236 ,p_target_project_id IN pa_projects_all.project_id%TYPE
237 ,p_target_fin_plan_type_id IN pa_fin_plan_types_b.fin_plan_type_id%TYPE
238 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
239 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
240 ,x_msg_data OUT NOCOPY VARCHAR2 ); --File.Sql.39 bug 4440895
241
242 /*==================================================================================
243 This procedure is used to update the amount types for the periodic budget or forcasts
244 of an existing plan type The existing plan types amount types will be updated to the
245 new selected amount types.
246
247 06-Apr-2005 prachand Created as a part of WebAdi changes.
248 Initial Creation
249 ===================================================================================*/
250
251
252 PROCEDURE update_amt_types (
253 p_project_id IN pa_projects_all.project_id%TYPE
254 ,p_fin_plan_type_id IN pa_fin_plan_types_b.fin_plan_type_id%TYPE
255 ,p_add_cost_amt_types_tbl IN SYSTEM.pa_varchar2_30_tbl_type
256 ,p_del_cost_amt_types_tbl IN SYSTEM.pa_varchar2_30_tbl_type
257 ,p_add_rev_amt_types_tbl IN SYSTEM.pa_varchar2_30_tbl_type
258 ,p_del_rev_amt_types_tbl IN SYSTEM.pa_varchar2_30_tbl_type
259 ,p_add_all_amt_types_tbl IN SYSTEM.pa_varchar2_30_tbl_type
260 ,p_del_all_amt_types_tbl IN SYSTEM.pa_varchar2_30_tbl_type
261 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
262 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
263 ,x_msg_data OUT NOCOPY VARCHAR2 ) ; --File.Sql.39 bug 4440895
264
265
266 end PA_PROJ_FP_OPTIONS_PUB;
267
[Home] [Help]