1 PACKAGE pa_fp_copy_from_pkg AUTHID CURRENT_USER AS
2 /* $Header: PAFPCPFS.pls 120.4 2005/09/08 00:55:42 prachand noship $ */
3
4 Invalid_Arg_Exc EXCEPTION ;
5
6 /* PL/SQL table type declaration */
7
8 TYPE l_res_assignment_tbl_typ IS TABLE OF
9 pa_resource_assignments.RESOURCE_ASSIGNMENT_ID%TYPE INDEX BY BINARY_INTEGER;
10
11 TYPE l_tot_labor_hrs_tbl_typ IS TABLE OF
12 pa_txn_accum.TOT_LABOR_HOURS%TYPE INDEX BY BINARY_INTEGER;
13
14 TYPE l_tot_raw_cost_tbl_typ IS TABLE OF
18 pa_txn_accum.TOT_BURDENED_COST%TYPE INDEX BY BINARY_INTEGER;
15 pa_txn_accum.TOT_RAW_COST%TYPE INDEX BY BINARY_INTEGER;
16
17 TYPE l_tot_burdened_cost_tbl_typ IS TABLE OF
19
20 TYPE l_tot_revenue_tbl_typ IS TABLE OF
21 pa_txn_accum.TOT_REVENUE%TYPE INDEX BY BINARY_INTEGER;
22
23 TYPE l_period_name_tbl_typ IS TABLE OF
24 pa_periods.PERIOD_NAME%TYPE INDEX BY BINARY_INTEGER;
25
26 TYPE l_start_date_tbl_typ IS TABLE OF DATE INDEX BY BINARY_INTEGER;
27
28 TYPE l_end_date_tbl_typ IS TABLE OF DATE INDEX BY BINARY_INTEGER;
29
30 TYPE proj_fp_options_id_tbl_typ IS TABLE OF
31 pa_proj_fp_options.proj_fp_options_id%TYPE INDEX BY BINARY_INTEGER;
32
33 /* PL/SQL table type declaration */
34
35 TYPE FP_OPTIONS_COLS IS RECORD (
36 proj_fp_options_id PA_PROJ_FP_OPTIONS.proj_fp_options_id%type
37 ,project_id PA_PROJ_FP_OPTIONS.project_id%type
38 ,fin_plan_type_id PA_PROJ_FP_OPTIONS.fin_plan_type_id%type
39 ,version_type VARCHAR2(30)
40 ,planning_level PA_PROJ_FP_OPTIONS.cost_fin_plan_level_code%type
41 ,resource_list_id PA_PROJ_FP_OPTIONS.cost_resource_list_id%type
42 ,time_phased_code PA_PROJ_FP_OPTIONS.cost_time_phased_code%type
43 ,amount_set_id PA_PROJ_FP_OPTIONS.cost_amount_set_id%type
44 ,projfunc_cost_rate_type PA_PROJ_FP_OPTIONS.projfunc_cost_rate_type%type
45 ,projfunc_cost_rate_date_type PA_PROJ_FP_OPTIONS.projfunc_cost_rate_date_type%type
46 ,projfunc_cost_rate_date PA_PROJ_FP_OPTIONS.projfunc_cost_rate_date%type
47 ,projfunc_rev_rate_type PA_PROJ_FP_OPTIONS.projfunc_rev_rate_type%type
48 ,projfunc_rev_rate_date_type PA_PROJ_FP_OPTIONS.projfunc_rev_rate_date_type%type
49 ,projfunc_rev_rate_date PA_PROJ_FP_OPTIONS.projfunc_rev_rate_date%type
50 ,project_cost_rate_type PA_PROJ_FP_OPTIONS.project_cost_rate_type%type
51 ,project_cost_rate_date_type PA_PROJ_FP_OPTIONS.project_cost_rate_date_type%type
52 ,project_cost_rate_date PA_PROJ_FP_OPTIONS.project_cost_rate_date%type
53 ,project_rev_rate_type PA_PROJ_FP_OPTIONS.project_rev_rate_type%type
54 ,project_rev_rate_date_type PA_PROJ_FP_OPTIONS.project_rev_rate_date_type%type
55 ,project_rev_rate_date PA_PROJ_FP_OPTIONS.project_rev_rate_date%type
56 ,raw_cost_flag PA_FIN_PLAN_AMOUNT_SETS.raw_cost_flag%type
57 ,burdened_cost_flag PA_FIN_PLAN_AMOUNT_SETS.burdened_cost_flag%type
58 ,revenue_flag PA_FIN_PLAN_AMOUNT_SETS.revenue_flag%type
59 ,quantity_flag PA_FIN_PLAN_AMOUNT_SETS.revenue_qty_flag%type
60 ,projfunc_currency_code PA_PROJECTS_ALL.projfunc_currency_code%type
61 ,project_currency_code PA_PROJECTS_ALL.project_currency_code%type
62 );
63
64 PROCEDURE copy_plan(
65 p_source_plan_version_id IN NUMBER
66 ,p_target_plan_version_id IN NUMBER
67 ,p_adj_percentage IN NUMBER
68 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
69 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
70 ,x_msg_data OUT NOCOPY VARCHAR2); --File.Sql.39 bug 4440895
71 --Bug 4290043.Included parameter to indicate whether to copy actual info or not.
72 PROCEDURE Copy_Budget_Version(
73 p_source_project_id IN NUMBER
74 ,p_target_project_id IN NUMBER
75 ,p_source_version_id IN NUMBER
76 ,p_copy_mode IN VARCHAR2
77 ,p_adj_percentage IN NUMBER
78 ,p_calling_module IN VARCHAR2
79 ,p_shift_days IN NUMBER DEFAULT NULL
80 ,p_copy_actuals_flag IN VARCHAR2 DEFAULT 'N'
81 ,px_target_version_id IN OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
82 ,p_struct_elem_version_id IN pa_budget_versions.budget_version_id%TYPE DEFAULT NULL--Bug 3354518
83 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
84 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
85 ,x_msg_data OUT NOCOPY VARCHAR2 ); --File.Sql.39 bug 4440895
86
87
88 --Added parameter p_rbs_map_diff_flag for Bug 3974569. This parameter can be passed as Y if the RBS mapping of
89 --the target resource assignments is different from that of the source resource assignments.If this is passed as Y then
90 ---->1.copy resource assignments will look at pa_rbs_plans_out_tmp table for rbs_element_id and txn_accum_header_id
91 ---->of target resource assignments and it assumes that source_id in pa_rbs_plans_out_tmp corresponds to the
92 ----> resource_assignment_id in the source budget version.
93 PROCEDURE copy_resource_assignments(
94 p_source_plan_version_id IN NUMBER
95 ,p_target_plan_version_id IN NUMBER
96 ,p_adj_percentage IN NUMBER
97 ,p_rbs_map_diff_flag IN VARCHAR2 DEFAULT 'N'
98 ,p_calling_context IN VARCHAR2 DEFAULT NULL --Bug 4065314
99 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
100 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
101 ,x_msg_data OUT NOCOPY VARCHAR2); --File.Sql.39 bug 4440895
102
103 /*===================================================================
104 This api is used to copy budget lines from one plan version to
105 another under the same project
109 --actuals from the source version or not. p_derv_rates_missing_amts_flag indicates whether the
106 ===================================================================*/
107 --Bug 4290043. Introduced the paramters p_copy_actuals_flag and p_derv_rates_missing_amts_flag.
108 --These will be passed from copy_version API. p_copy_actuals_flag indicates whether to copy the
110 --target version contains missing amounts rates which should be derived after copy
111
112 PROCEDURE Copy_Budget_Lines(
113 p_source_plan_version_id IN NUMBER
114 ,p_target_plan_version_id IN NUMBER
115 ,p_adj_percentage IN NUMBER
116 ,p_copy_actuals_flag IN VARCHAR2 DEFAULT 'N'
117 ,p_derv_rates_missing_amts_flag IN VARCHAR2 DEFAULT 'N'
118 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
119 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
120 ,x_msg_data OUT NOCOPY VARCHAR2); --File.Sql.39 bug 4440895
121
122 PROCEDURE Copy_Periods_Denorm(
123 p_source_plan_version_id IN NUMBER
124 ,p_target_plan_version_id IN NUMBER
125 ,p_calling_module IN VARCHAR2
126 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
127 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
128 ,x_msg_data OUT NOCOPY VARCHAR2); --File.Sql.39 bug 4440895
129
130
131 PROCEDURE Copy_Finplans_From_Project(
132 p_source_project_id IN NUMBER
133 ,p_target_project_id IN NUMBER
134 ,p_shift_days IN NUMBER
135 ,p_copy_version_and_elements IN VARCHAR2 DEFAULT 'Y' /* Bug# 2981655 */
136 ,p_agreement_amount IN NUMBER -- Added for bug 2986930
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_To_Be_Copied(
142 p_source_project_id IN NUMBER
143 ,p_copy_versions IN VARCHAR2 DEFAULT 'Y' /* Bug 2981655 */
144 ,x_fp_options_ids_tbl OUT NOCOPY PROJ_FP_OPTIONS_ID_TBL_TYP
145 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
146 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
147 ,x_msg_data OUT NOCOPY VARCHAR2) ; --File.Sql.39 bug 4440895
148
149 PROCEDURE Copy_Budgets_From_Project(
150 p_from_project_id IN NUMBER
151 ,p_to_project_id IN NUMBER
152 ,p_delta IN NUMBER
153 ,p_orig_template_flag IN VARCHAR2
154 ,p_agreement_amount IN NUMBER -- Added for bug 2986930
155 ,p_baseline_funding_flag IN VARCHAR2 -- Added for bug 2986930
156 ,x_err_code OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
157 ,x_err_stage OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
158 ,x_err_stack OUT NOCOPY VARCHAR2); --File.Sql.39 bug 4440895
159
160 /*===================================================================
161 Copy_Budget_lines has been overloaded to copy budget lines from one
162 budget/finplan to another version during copy project
163 ===================================================================*/
164
165 PROCEDURE Copy_Budget_Lines(
166 p_source_project_id IN NUMBER
167 ,p_target_project_id IN NUMBER
168 ,p_source_plan_version_id IN NUMBER
169 ,p_target_plan_version_id IN NUMBER
170 ,p_shift_days IN NUMBER
171 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
172 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
173 ,x_msg_data OUT NOCOPY VARCHAR2); --File.Sql.39 bug 4440895
174
175 /*=======================================================================
176 The api copies current period profiles from the source project to target
177 project.
178 ========================================================================*/
179
180 PROCEDURE Copy_Current_Period_Profiles
181 ( p_target_project_id IN pa_projects.project_id%TYPE
182 ,p_source_project_id IN pa_projects.project_id%TYPE
183 ,p_shift_days IN NUMBER
184 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
185 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
186 ,x_msg_data OUT NOCOPY VARCHAR2); --File.Sql.39 bug 4440895
187
188 /*===================================================================
189 The api shifts the period profile during copy_project.
190 ===================================================================*/
191
192 PROCEDURE Get_Create_Shifted_PD_Profile
193 ( p_target_project_id IN pa_projects.project_id%TYPE
194 ,p_source_period_profile_id IN pa_proj_period_profiles.period_profile_id%TYPE
195 ,p_shift_days IN NUMBER
196 ,x_target_period_profile_id OUT NOCOPY pa_proj_period_profiles.period_profile_id%TYPE --File.Sql.39 bug 4440895
197 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
198 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
199 ,x_msg_data OUT NOCOPY VARCHAR2); --File.Sql.39 bug 4440895
200
201 /*============================================================================================================
202 This procedure should be called to copy a workplan version. Copies budget versions, resource assignments
203 and budget lines as required for the workplan version. This is added for FP M
204
205 bug 3847386 Raja 24-Sep-2004 Added a new parameter p_copy_act_from_str_ids_tbl
206 The table would contain from which version actuals
207 should be copied for the target versions if they need
208 to be copied
209 ============================================================================================================*/
210
211 PROCEDURE copy_wp_budget_versions
212 (
213 p_source_project_id IN pa_proj_element_versions.project_id%TYPE
214 ,p_target_project_id IN pa_proj_element_versions.element_version_id%TYPE
215 ,p_src_sv_ids_tbl IN SYSTEM.pa_num_tbl_type
216 ,p_target_sv_ids_tbl IN SYSTEM.pa_num_tbl_type
217 ,p_copy_act_from_str_ids_tbl IN SYSTEM.pa_num_tbl_type DEFAULT null -- bug 3847386
218 ,p_copy_people_flag IN VARCHAR2 DEFAULT 'Y'
219 ,p_copy_equip_flag IN VARCHAR2 DEFAULT 'Y'
220 ,p_copy_mat_item_flag IN VARCHAR2 DEFAULT 'Y'
221 ,p_copy_fin_elem_flag IN VARCHAR2 DEFAULT 'Y'
222 ,p_copy_mode IN VARCHAR2 DEFAULT 'P' -- bug 4277801
223 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
224 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
225 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
226 );
227
228 /*====================================================================================
229 Bug 3354518 - FP M changes - This is an overloaded API. This API will be called
230 from pa_fp_planning_transaction_pub.copy_planning_transactions.This API will be used
231 to populate the global temporary table PA_FP_RA_MAP_TEMP which will be used for
232 the creation of target resource assignment records.
233 New columns in pa_fp_ra_map_tmp that will be used for FP M are given below
234 planning_start_Date->planning_start_Date for target resource assignment id
235 planning_end_Date->planning_end_Date for target resource assignment id
236 schedule_start_Date -> schedule_start_date for target resource assignment id .. For TA specifically..
237 schedule_end_Date -> schedule_end_date for target resource assignment id .. For TA specifically..
238 system_reference1->source element version id
239 system_reference2->target element version id
240 system_reference3->project assignment id for the target resoruce assignment id
241
242 p_src_ra_id_tbl -> The tbl containing the source ra ids which should be copied into the target
243 p_src_elem_ver_id_tbl->source element version ids which should be copied into the target
244 p_targ_elem_ver_id_tbl->target element version ids corresponding to the source element version ids
245 p_targ_proj_assmt_id_tbl->target project assignment ids corresponding to the source resource assignment ids
246
247 Bug 3615617 - FP M IB2 changes - Raja
248 For workplan context target rlm id would be passed for each source resource assignment
249 p_targ_rlm_id_tbl -> target resource list member ids corresponding to the source resource assignment ids
250 =====================================================================================*/
251 PROCEDURE create_res_task_maps(
252 p_context IN VARCHAR2 --Can be WORKPLAN, BUDGET
253 ,p_src_ra_id_tbl IN SYSTEM.PA_NUM_TBL_TYPE
254 ,p_src_elem_ver_id_tbl IN SYSTEM.PA_NUM_TBL_TYPE
255 ,p_targ_elem_ver_id_tbl IN SYSTEM.PA_NUM_TBL_TYPE
256 ,p_targ_proj_assmt_id_tbl IN SYSTEM.PA_NUM_TBL_TYPE
257 ,p_targ_rlm_id_tbl IN SYSTEM.PA_NUM_TBL_TYPE -- Bug 3615617
258 ,p_planning_start_date_tbl IN SYSTEM.PA_DATE_TBL_TYPE
259 ,p_planning_end_date_tbl IN SYSTEM.PA_DATE_TBL_TYPE
260 ,p_schedule_start_date_tbl IN SYSTEM.PA_DATE_TBL_TYPE
261 ,p_schedule_end_date_tbl IN SYSTEM.PA_DATE_TBL_TYPE
262 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
263 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
264 ,x_msg_data OUT NOCOPY VARCHAR2); --File.Sql.39 bug 4440895
265
266 PROCEDURE Acquire_Locks_For_Copy_Actual(
267 p_plan_version_id IN pa_proj_fp_options.fin_plan_version_id%TYPE
268 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
269 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
270 ,x_msg_data OUT NOCOPY VARCHAR2); --File.Sql.39 bug 4440895
271
272 /*===================================================================
273 3156057: This api is used to copy budget lines from mc enabled plan version to
274 approved revenue budget versions
275 ===================================================================*/
276
277 --Bug 4290043. Added p_derv_rates_missing_amts_flag to indicate whether the missing amounts in the target version
278 --should be derived or not after copy
279
280 PROCEDURE Copy_Budget_Lines_Appr_Rev(
281 p_source_plan_version_id IN NUMBER
282 ,p_target_plan_version_id IN NUMBER
283 ,p_adj_percentage IN NUMBER
284 ,p_derv_rates_missing_amts_flag IN VARCHAR2
285 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
286 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
287 ,x_msg_data OUT NOCOPY VARCHAR2); --File.Sql.39 bug 4440895
288
289 /*=============================================================================
290 Bug 3619687: PJ.M:B5:BF:DEV:TRACKING BUG FOR PLAN SETTINGS CHANGE REQUEST
291 When a new workplan structure version is created from a published version, this
292 api is called to synchronise all the additional workplan settings related data.
293 This api is called from copy_wp_budget_versions api at the end of copying all
294 the budgets related data from source published version.
295 Synchronisation involves:
296 1) pa_fp_txn_currencies
297 2) rate schedules, generation options and plan settings data
298 (pa_proj_fp_options)
299
300 Stating some of the business rules for clarity:
301 i) If there is a published version, time phasing can not be changed
302 ii) Planning resource list can change only if existing resource list is
303 'None'. To handle this case, we would re-map the resource assignments
304 data. Please note that in this case, only 'PEOPLE' resource class assignments
305 would be present.
306 iii) RBS can not be different as RBS change is pushed to
307 ==============================================================================*/
308
309 PROCEDURE Update_Plan_Setup_For_WP_Copy(
310 p_project_id IN pa_projects_all.project_id%TYPE
311 ,p_wp_version_id IN pa_budget_versions.fin_plan_type_id%TYPE
312 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
313 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
314 ,x_msg_data OUT NOCOPY VARCHAR2) ; --File.Sql.39 bug 4440895
315
316 END pa_fp_copy_from_pkg;
[Home] [Help]