[Home] [Help]
PACKAGE BODY: APPS.PA_PROJ_FP_OPTIONS_PUB
Source
1 package body PA_PROJ_FP_OPTIONS_PUB as
2 /* $Header: PAFPOPPB.pls 120.15 2012/01/19 08:16:46 a5sharma ship $ */
3
4 l_module_name VARCHAR2(100) := 'pa.plsql.pa_proj_fp_options_pub';
5 P_PA_DEBUG_MODE varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
6
7 /*==================================================================================================
8 CREATE_FP_OPTION: This procedure inserts or updates records in 3 table depending
9 on the Source and the Target FP Option Details passed to this procedure.
10 -> If the Source and Target are passed, then the Target FP Option is updated with the Source
11 details.
12 this case if true in following cases
13 1. edit plan type. In this case user might change the preference code of the option.
14 2. copy from a existing version. All values except approved cost/revenue are to be overriden
15 3. While adding plan type to a project.
16 4. copy from an existing project.
17
18 -> If the Source is passed and Target not passed, then a new Target FP Option is created based
19 on other Source details.
20 -> If the Source is not passed and the Target is passed, then Source details are got from the
21 Parent (if exists) else Default option details are got using the Target Preference Code. A new
22 Target FP Option is created based on the details got.
23 -> If the Source and the Target are not passed, then the details of the Parent are got using the
24 Option Level Code else Default Option details are got using the Target Preference code.
25
26 BUG:- 2625872 As part of the Upgrade changes, create_fp_option api has been modified to set multi
27 currency flag to 'Y' if the project currency isn't equal to project functional currency.
28
29 Bug:- 2920954 calls to insert/update table handlers have been chnaged to include new columns.
30
31 -- 26-JUN-2003 jwhite - Plannable Task HQ Dev Effort:
32 -- Make code changes to Create_FP_Option procedure to
33 -- enable population of new parameters on
34 -- PA_PROJ_FP_OPTIONS_PKG.Insert_Row table handler.
35
36 --
37 r11.5 FP.M Developement ----------------------------------
38
39 08-JAN-2004 jwhite Bug 3362316 (HQ)
40 Rewrote Create_Fp_Option.
41 - FP_COL Record specifiation definition
42 - PA_PROJ_FP_OPTIONS_PKG.update_row parm list
43 - PA_PROJ_FP_OPTIONS_PKG.insert_row parm list
44
45 3/30/2004 Raja FP M Dev Effort Copy Project Impact:
46 When versions are being copied across projects genration source plan versions
47 can not be copied as they are. So, all gen source version id columns would be null
48
49 4/16/2004 Raja FP M Phase II Dev Effort Copy Plan:
50 When user chooses 'copy version amounts' from one version to another version, do not copy
51 "rate schedules" and "generation options" sub tabs related data.
52
53 ==================================================================================================*/
54
55 PROCEDURE Create_FP_Option (
56 px_target_proj_fp_option_id IN OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
57 ,p_source_proj_fp_option_id IN NUMBER
58 ,p_target_fp_option_level_code IN VARCHAR2
59 ,p_target_fp_preference_code IN VARCHAR2
60 ,p_target_fin_plan_version_id IN NUMBER
61 ,p_target_project_id IN NUMBER
62 ,p_target_plan_type_id IN NUMBER
63 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
64 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
65 ,x_msg_data OUT NOCOPY VARCHAR2 ) is --File.Sql.39 bug 4440895
66
67 FP_Cols_Rec PA_PROJ_FP_OPTIONS_PUB.FP_COLS;
68 FP_Mc_Cols_Rec PA_PROJ_FP_OPTIONS_PUB.FP_MC_COLS;
69 l_par_Proj_FP_Options_id pa_proj_fp_options.PROJ_FP_OPTIONS_ID%TYPE;
70 l_source_project_id pa_proj_fp_options.PROJECT_ID%TYPE;
71 l_plan_type_id pa_proj_fp_options.FIN_PLAN_TYPE_ID%TYPE;
72 l_plan_version_id pa_proj_fp_options.FIN_PLAN_VERSION_ID%TYPE;
73 l_target_option_level_code pa_proj_fp_options.FIN_PLAN_OPTION_LEVEL_CODE%TYPE;
74 l_source_option_level_code pa_proj_fp_options.FIN_PLAN_OPTION_LEVEL_CODE%TYPE;
75 l_fp_preference_code pa_proj_fp_options.FIN_PLAN_PREFERENCE_CODE%TYPE;
76 l_copy_project_context VARCHAR2(1);
77 /* Variables added for autobase line Bug#2619022*/
78 l_baseline_funding_flag pa_projects_all.BASELINE_FUNDING_FLAG%TYPE;
79 l_approved_rev_plan_type_flag pa_proj_fp_options.APPROVED_REV_PLAN_TYPE_FLAG%TYPE;
80 l_source_fp_preference_code pa_proj_fp_options.FIN_PLAN_PREFERENCE_CODE%TYPE;
81
82 /* Bug # 2702000 */
83 FP_Cols_Rec_Rev_Def PA_PROJ_FP_OPTIONS_PUB.FP_COLS;
84
85 l_debug_mode VARCHAR2(30);
86 l_msg_count NUMBER := 0;
87 l_data VARCHAR2(2000);
88 l_msg_data VARCHAR2(2000);
89 l_msg_index_out NUMBER;
90 l_return_status VARCHAR2(2000);
91 x_row_id ROWID;
92 l_stage NUMBER := 100;
93
94 -- jwhite, 26-JUN-2003: Added for Plannable Task Dev Effort ------------------
95
96 l_refresh_required_flag VARCHAR2(1) := NULL;
97 l_request_id NUMBER(15) := NULL;
98 l_process_code VARCHAR2(30) := NULL;
99
100 -- -------------------------------------------------------
101
102 -- FP M Dev Effort new variables
103
104 l_default_gen_options_rec PA_PROJ_FP_OPTIONS_PUB.FP_COLS;
105 l_source_plan_class_code pa_fin_plan_types_b.plan_class_code%TYPE;
106
107 -- 3/30/2004 FP M Phase II Dev Effort
108
109 l_gl_start_period gl_periods.period_name%TYPE;
110 l_gl_end_period gl_periods.period_name%TYPE;
111 l_gl_start_Date VARCHAR2(100);
112 l_pa_start_period pa_periods_all.period_name%TYPE;
113 l_pa_end_period pa_periods_all.period_name%TYPE;
114 l_pa_start_date VARCHAR2(100);
115 l_plan_version_exists_flag VARCHAR2(1);
116 l_prj_start_date VARCHAR2(100);
117 l_prj_end_date VARCHAR2(100);
118
119 --Added for webAdi development
120 l_source_plan_type_id pa_proj_fp_options.FIN_PLAN_TYPE_ID%TYPE;
121 -- begin: Bug 5941436: fnd_profile.value_specific('PA_FP_WEBADI_ENABLE'); has been changed with fnd_profile.value('PA_FP_WEBADI_ENABLE'); to perform less sqls and use caching and therefore to improve the performance
122 /* Bug 6413612 : Added substr to fetch only 1 character of profile value */
123 l_webadi_profile VARCHAR(1) := UPPER(SUBSTR(fnd_profile.value_specific('PA_FP_WEBADI_ENABLE'), 1, 1));
124 -- end Bug 5941436:
125 -- FP M Dev Effort new cursor defined to fetch plan type information
126
127 CURSOR plan_type_info_cur (c_fin_plan_type_id NUMBER) IS
128 SELECT plan_class_code
129 ,nvl(approved_cost_plan_type_flag,'N') approved_cost_plan_type_flag
130 ,nvl(approved_rev_plan_type_flag,'N') approved_rev_plan_type_flag
131 ,nvl(primary_cost_forecast_flag,'N') primary_cost_forecast_flag
132 ,nvl(primary_rev_forecast_flag,'N') primary_rev_forecast_flag
133 ,nvl(use_for_workplan_flag,'N') use_for_workplan_flag
134 FROM pa_fin_plan_types_b
135 WHERE fin_plan_type_id = c_fin_plan_type_id;
136
137
138 CURSOR opt_info_Cur (c_proj_fp_options_id NUMBER) IS
139 SELECT
140 nvl(approved_rev_plan_type_flag,'N') approved_rev_plan_type_flag
141 FROM pa_proj_fp_options
142 where proj_fp_options_id = c_proj_fp_options_id;
143
144 opt_info_rec opt_info_Cur%ROWTYPE;
145 plan_type_info_rec plan_type_info_cur%ROWTYPE;
146
147 CURSOR plan_version_info_cur (c_budget_version_id NUMBER) IS
148 SELECT ci_id
149 FROM pa_budget_versions
150 WHERE budget_version_id = c_budget_version_id;
151
152 plan_version_info_rec plan_version_info_cur%ROWTYPE;
153
154
155 BEGIN
156 FND_MSG_PUB.initialize;
157 IF P_PA_DEBUG_MODE = 'Y' THEN
158 pa_debug.init_err_stack('PA_PROJ_FP_OPTIONS_PUB.Create_FP_Option');
159 fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
160 l_debug_mode := NVL(l_debug_mode, 'Y');
161 pa_debug.set_process('PLSQL','LOG',l_debug_mode);
162 END IF;
163
164 x_msg_count := 0;
165 x_return_status := FND_API.G_RET_STS_SUCCESS;
166
167 l_stage := 200;
168 IF P_PA_DEBUG_MODE = 'Y' THEN
169 pa_debug.g_err_stage := TO_CHAR(l_Stage)||': entered create_fp_option';
170 pa_debug.write('Create_FP_Option: ' || l_module_name,pa_debug.g_err_stage,5);
171 END IF;
172
173 IF p_target_fp_preference_code IS NOT NULL THEN
174 l_fp_preference_code := p_target_fp_preference_code;
175 END IF;
176
177 /* Validating for Input parameters to raise error if not passed to the procedure. */
178 IF (px_target_proj_fp_option_id IS NULL) THEN
179 IF (p_target_fp_option_level_code IS NULL) THEN
180 /* Option Level Code should not be NULL when the Target is NULL */
181 l_stage := 340;
182 IF P_PA_DEBUG_MODE = 'Y' THEN
183 pa_debug.g_err_stage := TO_CHAR(l_Stage)||': Err - Option Level Code should not be NULL';
184 pa_debug.write('Create_FP_Option: ' || l_module_name,pa_debug.g_err_stage,5);
185 END IF;
186 x_return_status := FND_API.G_RET_STS_ERROR;
187 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
188 p_msg_name => 'PA_FP_INV_PARAM_PASSED');
189 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
190
191 ELSIF (p_target_fp_option_level_code = PA_FP_CONSTANTS_PKG.G_OPTION_LEVEL_PLAN_TYPE) THEN
192 l_stage := 300;
193 IF (p_target_plan_type_id IS NULL) THEN
194 /* Plan Type ID cannot be NULL when Target Proj FP Option is NULL and Option Level
195 Code is PLAN_TYPE */
196 IF P_PA_DEBUG_MODE = 'Y' THEN
197 pa_debug.g_err_stage := TO_CHAR(l_Stage)||': Err- Plan Type ID cannot be NULL.';
198 pa_debug.write('Create_FP_Option: ' || l_module_name,pa_debug.g_err_stage,5);
199 END IF;
200 x_return_status := FND_API.G_RET_STS_ERROR;
201 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
202 p_msg_name => 'PA_FP_INV_PARAM_PASSED');
203 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
204 END IF;
205 ELSIF (p_target_fp_option_level_code = PA_FP_CONSTANTS_PKG.G_OPTION_LEVEL_PLAN_VERSION) THEN
206 /* Plan Type ID and Pland Version ID cannot be NULL when the Option Level Code is
207 PLAN_VERSION. */
208 l_stage := 320;
209 IF (p_target_plan_type_id IS NULL) THEN
210
211 IF P_PA_DEBUG_MODE = 'Y' THEN
212 pa_debug.g_err_stage := TO_CHAR(l_Stage)||': Err- Plan Type ID cannot be NULL.';
213 pa_debug.write('Create_FP_Option: ' || l_module_name,pa_debug.g_err_stage,5);
214 END IF;
215 x_return_status := FND_API.G_RET_STS_ERROR;
216 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
217 p_msg_name => 'PA_FP_INV_PARAM_PASSED' );
218 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
219 ELSIF (p_target_fin_plan_version_id IS NULL) THEN
220 IF P_PA_DEBUG_MODE = 'Y' THEN
221 pa_debug.g_err_stage := TO_CHAR(l_Stage)||': Err- Plan Version ID cannot be NULL.';
222 pa_debug.write('Create_FP_Option: ' || l_module_name,pa_debug.g_err_stage,5);
223 END IF;
224 x_return_status := FND_API.G_RET_STS_ERROR;
225 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
226 p_msg_name => 'PA_FP_INV_PARAM_PASSED' );
227 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
228 END IF;
229 END IF;
230
231 IF P_PA_DEBUG_MODE = 'Y' THEN
232 pa_debug.g_err_stage := TO_CHAR(l_Stage)||': target option id is null. Populating from inputs';
233 pa_debug.write('Create_FP_Option: ' || l_module_name,pa_debug.g_err_stage,5);
234 END IF;
235
236 /* if target is null then these variables are validated for input values. Hence initialize these */
237 l_target_option_level_code := p_target_fp_option_level_code;
238 l_plan_type_id := p_target_plan_type_id;
239 l_plan_version_id := p_target_fin_plan_version_id;
240 ELSE /* M22-AUG: if target is not null get values from target */
241 /* If the Target Project Option ID is not NULL, then get the Plan_Type_ID
242 and other columns from the database for this Proj FP Option ID so that even if
243 NULL values are passed in the parameters, the database values retrieved are passed
244 to the Table Handlers PA_PROJ_FP_OPTIONS_PKG.update_row and insert_row. */
245
246 IF P_PA_DEBUG_MODE = 'Y' THEN
247 pa_debug.g_err_stage := TO_CHAR(l_Stage)||': target option id is not null. Populating from target';
248 pa_debug.write('Create_FP_Option: ' || l_module_name,pa_debug.g_err_stage,5);
249 END IF;
250
251 SELECT fin_plan_type_id, fin_plan_version_id, fin_plan_option_level_code,
252 nvl(l_fp_preference_code, fin_plan_preference_code) /* get only if l_fp_preference_code is not null */
253 INTO l_plan_type_id, l_plan_version_id, l_target_option_level_code,
254 l_fp_preference_code
255 FROM pa_proj_fp_options
256 WHERE proj_fp_options_id = px_target_proj_fp_option_id;
257
258 END IF;
259
260 /* validate and populate l_fp_preference_code */
261 IF (p_source_proj_fp_option_id IS NULL) THEN
262 /* The control would come to this point when the Source FP Option is NULL. Since the Source is NULL,
263 a new record needs to be inserted into Proj FP Options based on the Target_FP_Pref_Code. Hence
264 Target_FP_Pref_Code should be NOT NULL for this case. */
265 l_stage := 360;
266
267 IF P_PA_DEBUG_MODE = 'Y' THEN
268 pa_debug.g_err_stage := TO_CHAR(l_Stage)||': source option id is null';
269 pa_debug.write('Create_FP_Option: ' || l_module_name,pa_debug.g_err_stage,5);
270 END IF;
271 IF (l_fp_preference_code is NULL) THEN /* by this time if l_fp_preference_code is not null then its error */
272 IF P_PA_DEBUG_MODE = 'Y' THEN
273 pa_debug.g_err_stage := TO_CHAR(l_Stage)||': Err - FP Preference Code should not be NULL';
274 pa_debug.write('Create_FP_Option: ' || l_module_name,pa_debug.g_err_stage,5);
275 END IF;
276 x_return_status := FND_API.G_RET_STS_ERROR;
277 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
278 p_msg_name => 'PA_FP_INV_PARAM_PASSED');
279 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
280 END IF;
281 ELSE
282 /* get this from source option. As the validation is already done source cannot be null
283 at this point */
284
285 IF P_PA_DEBUG_MODE = 'Y' THEN
286 pa_debug.g_err_stage := TO_CHAR(l_Stage)||': source option id is NOT null. Getting preference code';
287 pa_debug.write('Create_FP_Option: ' || l_module_name,pa_debug.g_err_stage,5);
288 END IF;
289 /* Selected l_source_fp_preference_code for Bug 3149010 */
290
291 SELECT nvl(l_fp_preference_code,fin_plan_preference_code),
292 fin_plan_preference_code,
293 fin_plan_option_level_code,
294 project_id,
295 pt.plan_class_code,
296 pfo.fin_plan_type_id
297 INTO l_fp_preference_code,
298 l_source_fp_preference_code,
299 l_source_option_level_code,
300 l_source_project_id,
301 l_source_plan_class_code,
302 l_source_plan_type_id -- Added this to get the source plan type id for copying the amount types
303 FROM pa_proj_fp_options pfo,
304 pa_fin_plan_types_b pt
305 WHERE pfo.proj_fp_options_id = p_source_proj_fp_option_id
306 AND pfo.fin_plan_type_id = pt.fin_plan_type_id(+);
307
308 END IF;
309
310 l_stage := 400;
311 IF (p_target_project_id IS NULL) THEN
312 IF P_PA_DEBUG_MODE = 'Y' THEN
313 pa_debug.g_err_stage := TO_CHAR(l_Stage)||': Err- Project ID cannot be NULL.';
314 pa_debug.write('Create_FP_Option: ' || l_module_name,pa_debug.g_err_stage,5);
315 END IF;
316 x_return_status := FND_API.G_RET_STS_ERROR;
317 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
318 p_msg_name => 'PA_FP_INV_PARAM_PASSED');
319 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
320 END IF;
321
322 IF (l_plan_type_id IS NOT NULL) THEN
323
324 -- Open and fetch plan type info cur into rec
325 OPEN plan_type_info_cur(l_plan_type_id);
326 FETCH plan_type_info_cur INTO plan_type_info_rec;
327 CLOSE plan_type_info_cur;
328
329 END IF;
330
331
332 IF (p_source_proj_fp_option_id IS NOT NULL) THEN
333
334 IF P_PA_DEBUG_MODE = 'Y' THEN
335 pa_debug.g_err_stage := TO_CHAR(l_Stage)||': source option id is NOT null. Getting fp_cols from source';
336 pa_debug.write('Create_FP_Option: ' || l_module_name,pa_debug.g_err_stage,5);
337 END IF;
338 get_fp_options(p_proj_fp_options_id => p_source_proj_fp_option_id
339 ,p_target_fp_options_id => px_target_proj_fp_option_id /* Bug 3144283 */
340 ,p_fin_plan_preference_code => l_fp_preference_code
341 ,p_target_fp_option_level_code => l_target_option_level_code
342 ,x_fp_cols_rec => fp_cols_rec
343 ,x_return_status => x_return_status
344 ,x_msg_count => x_msg_count
345 ,x_msg_data => x_msg_data);
346
347 /* Added the following check for the NOCOPY changes. */
348
349 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
350 raise PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
351 END IF;
352 /* populate fp_cols_rec */
353 ELSIF (p_target_fp_option_level_code = PA_FP_CONSTANTS_PKG.G_OPTION_LEVEL_PLAN_TYPE) AND
354 (p_target_plan_type_id IS NOT NULL) AND
355 (plan_type_info_rec.use_for_workplan_flag = 'Y')
356 THEN
357 -- Control comes here if workplan plan type is being added for the project
358 -- Values should not be defaulted from project level option
359
360 FP_Cols_Rec := get_default_fp_options(l_fp_preference_code,p_target_project_id,p_target_plan_type_id);
361
362 ELSE
363 IF px_target_proj_fp_option_id IS NOT NULL THEN
364 IF P_PA_DEBUG_MODE = 'Y' THEN
365 pa_debug.g_err_stage := TO_CHAR(l_Stage)||': target is not null and source is null. Get parent of target';
366 pa_debug.write('Create_FP_Option: ' || l_module_name,pa_debug.g_err_stage,5);
367 END IF;
368 l_par_Proj_FP_Options_ID := get_parent_fp_option_id(px_target_proj_fp_option_id);
369 ELSE
370 IF P_PA_DEBUG_MODE = 'Y' THEN
371 pa_debug.g_err_stage := TO_CHAR(l_Stage)||': Target is null so Get higher level option';
372 pa_debug.write('Create_FP_Option: ' || l_module_name,pa_debug.g_err_stage,5);
373 END IF;
374
375 IF (l_target_option_level_code = PA_FP_CONSTANTS_PKG.G_OPTION_LEVEL_PLAN_TYPE) THEN
376
377 IF P_PA_DEBUG_MODE = 'Y' THEN
378 pa_debug.g_err_stage := TO_CHAR(l_Stage)||': getting project level option';
379 pa_debug.write('Create_FP_Option: ' || l_module_name,pa_debug.g_err_stage,5);
380 END IF;
381
382 l_par_Proj_FP_Options_ID := get_fp_option_id(p_target_project_id, NULL,NULL);
383 ELSIF (l_target_option_level_code = PA_FP_CONSTANTS_PKG.G_OPTION_LEVEL_PLAN_VERSION) THEN
384 IF P_PA_DEBUG_MODE = 'Y' THEN
385 pa_debug.g_err_stage := TO_CHAR(l_Stage)||': getting plan type level option';
386 pa_debug.write('Create_FP_Option: ' || l_module_name,pa_debug.g_err_stage,5);
387 END IF;
388
389 l_par_Proj_FP_Options_ID := get_fp_option_id(p_target_project_id
390 ,l_plan_type_id
391 ,NULL);
392 END IF;
393 END IF;
394
395 /* Bug# 2619022 changes done for autobaseline. Only at the plan type level option
396 the defaulting of time phasing , resource list and time phasing needs to be
397 done based upon autobaseline rules.
398 */
399 l_baseline_funding_flag := NULL;
400 l_approved_rev_plan_type_flag := NULL;
401
402 IF p_target_project_id IS NOT NULL AND p_target_plan_type_id IS NOT NULL
403 AND p_target_fin_plan_version_id IS NULL
404 THEN
405
406 SELECT NVL(baseline_funding_flag,'N')
407 ,NVL(approved_rev_plan_type_flag,'N')
408 INTO l_baseline_funding_flag
409 ,l_approved_rev_plan_type_flag
410 FROM pa_projects_all ppa
411 ,pa_fin_plan_types_b ptb
412 WHERE ppa.project_id = p_target_project_id
413 AND ptb.fin_plan_type_id = p_target_plan_type_id;
414
415 END IF;
416
417 IF P_PA_DEBUG_MODE = 'Y' THEN
418 pa_debug.g_err_stage := 'Autobaseline flag : '||l_baseline_funding_flag||' Approved Rev flag '||l_approved_rev_plan_type_flag;
419 pa_debug.write('Create_FP_Option: ' || l_module_name,pa_debug.g_err_stage,3);
420 END IF;
421
422 /* Bug#2619022 changes done for autobaseline.
423 In case autobaselining is enabled and approv reve plan type flag is 'Y' then
424 get the default values as per the autobaseline business rules.
425 */
426
427 /* Bug 2702000 - This case is taken care in the condition when
428 l_par_Proj_FP_Options_ID IS NOT NULL
429 */
430 /* IF l_baseline_funding_flag = 'Y' AND l_approved_rev_plan_type_flag = 'Y' THEN
431
432 IF P_PA_DEBUG_MODE = 'Y' THEN
433 pa_debug.g_err_stage := 'inside baseline funding flag and approv rev flag Y';
434 pa_debug.write('Create_FP_Option: ' || l_module_name,pa_debug.g_err_stage,3);
435 END IF;
436
437 FP_Cols_Rec := get_default_fp_options(l_fp_preference_code,
438 p_target_project_id,
439 p_target_plan_type_id);
440
441 ELSIF (l_par_Proj_FP_Options_ID IS NOT NULL) THEN*/
442 IF (l_par_Proj_FP_Options_ID IS NOT NULL) THEN
443 IF P_PA_DEBUG_MODE = 'Y' THEN
444 pa_debug.g_err_stage := TO_CHAR(l_Stage)||': getting options for l_par_Proj_FP_Options_ID = ' || l_par_Proj_FP_Options_ID;
445 pa_debug.write('Create_FP_Option: ' || l_module_name,pa_debug.g_err_stage,5);
446 END IF;
447
448 get_fp_options(p_proj_fp_options_id => l_par_Proj_FP_Options_ID
449 ,p_target_fp_options_id => px_target_proj_fp_option_id /* Bug 3144283 */
450 ,p_fin_plan_preference_code => l_fp_preference_code
451 ,p_target_fp_option_level_code => l_target_option_level_code
452 ,x_fp_cols_rec => fp_cols_rec
453 ,x_return_status => x_return_status
454 ,x_msg_count => x_msg_count
455 ,x_msg_data => x_msg_data);
456
457 /* Added the following check for the NOCOPY changes. */
458
459 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
460 raise PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
461 END IF;
462
463 /* Bug # 2702000 */
464 IF l_baseline_funding_flag = 'Y' AND l_approved_rev_plan_type_flag = 'Y' THEN
465
466 IF P_PA_DEBUG_MODE = 'Y' THEN
467 pa_debug.g_err_stage := 'inside baseline funding flag and approv rev flag Y';
468 pa_debug.write('Create_FP_Option: ' || l_module_name,pa_debug.g_err_stage,3);
469 END IF;
470
471 FP_Cols_Rec_Rev_Def := get_default_fp_options(l_fp_preference_code,
472 p_target_project_id,
473 p_target_plan_type_id);
474
475 FP_Cols_Rec.Revenue_Fin_Plan_Level_Code := FP_Cols_Rec_Rev_Def.Revenue_Fin_Plan_Level_Code;
476 FP_Cols_Rec.Revenue_Time_Phased_Code := FP_Cols_Rec_Rev_Def.Revenue_Time_Phased_Code;
477 FP_Cols_Rec.Revenue_Resource_List_ID := FP_Cols_Rec_Rev_Def.Revenue_Resource_List_ID;
478
479 -- Bug 2959307 , when auto baseline is enabled, auto resource selection property should be disabled
480
481 FP_Cols_Rec.select_rev_res_auto_flag := 'N';
482 FP_Cols_Rec.revenue_res_planning_level := null;
483
484 -- Copy revenue options from FP_Cols_Rec_Rev_Def into FP_Cols_Rec.
485 END IF;
486
487 ELSE
488 IF P_PA_DEBUG_MODE = 'Y' THEN
489 pa_debug.g_err_stage := TO_CHAR(l_Stage)||': could not find parent hence getting default options ';
490 pa_debug.write('Create_FP_Option: ' || l_module_name,pa_debug.g_err_stage,5);
491 pa_debug.g_err_stage := 'Preference Code is - '||l_fp_preference_code;
492 pa_debug.write('Create_FP_Option: ' || l_module_name,pa_debug.g_err_stage,5);
493 END IF;
494
495 FP_Cols_Rec := get_default_fp_options(l_fp_preference_code,p_target_project_id,p_target_plan_type_id);
496 END IF;
497
498 END IF;
499
500 -- FP M Dev effort amount generation columns should be handled based on source option and target option
501 -- if source is project and target is plan type, generation columns should be initialized with default values
502 -- if target is ci version and source is version, genearation columns should be nulled out
503 -- if target is version and source is version, take care that gen src version id for target version is
504 -- not same as the target version if so null out the column (this could happen through copy_plan api)
505
506 IF (l_target_option_level_code <> PA_FP_CONSTANTS_PKG.G_OPTION_LEVEL_PROJECT) AND
507 (nvl(plan_type_info_rec.use_for_workplan_flag,'N') <> 'Y')
508 THEN
509
510 -- Fetch source option level code if null using parent option
511
512 IF l_source_option_level_code IS NULL THEN
513
514 IF l_par_proj_fp_options_id IS NOT NULL THEN
515
516 BEGIN
517 SELECT fin_plan_preference_code,
518 fin_plan_option_level_code,
519 pt.plan_class_code
520 INTO l_source_fp_preference_code,
521 l_source_option_level_code,
522 l_source_plan_class_code
523 FROM pa_proj_fp_options pfo,
524 pa_fin_plan_types_b pt
525 WHERE pfo.proj_fp_options_id = l_par_proj_fp_options_id
526 AND pfo.fin_plan_type_id = pt.fin_plan_type_id(+);
527 EXCEPTION
528 WHEN NO_DATA_FOUND THEN
529 IF P_PA_DEBUG_MODE = 'Y' THEN
530 pa_debug.g_err_stage := 'No data found for parent option in pa_proj_fp_optins when trying to get def gen vals';
531 pa_debug.write('Create_FP_Option: ' || l_module_name,pa_debug.g_err_stage,5);
532 END IF;
533 RAISE;
534 END;
535
536 END IF;
537
538 END IF;
539
540 -- Target option plan type context
541 IF l_target_option_level_code = PA_FP_CONSTANTS_PKG.G_OPTION_LEVEL_PLAN_TYPE THEN
542
543 -- If source option is project
544 IF l_source_option_level_code = PA_FP_CONSTANTS_PKG.G_OPTION_LEVEL_PROJECT THEN
545
546 IF P_PA_DEBUG_MODE = 'Y' THEN
547 pa_debug.g_err_stage := 'inside target plan type and source project case if';
548 pa_debug.write('Create_FP_Option: ' || l_module_name,pa_debug.g_err_stage,5);
549 END IF;
550
551 -- Initialize amount generation columns with default values
552 l_default_gen_options_rec := get_default_fp_options(l_fp_preference_code,p_target_project_id,p_target_plan_type_id);
553
554 FP_Cols_Rec.gen_cost_src_code := l_default_gen_options_rec.gen_cost_src_code ;
555 FP_Cols_Rec.gen_cost_etc_src_code := l_default_gen_options_rec.gen_cost_etc_src_code ;
556 FP_Cols_Rec.gen_cost_incl_change_doc_flag := l_default_gen_options_rec.gen_cost_incl_change_doc_flag ;
557 FP_Cols_Rec.gen_cost_incl_open_comm_flag := l_default_gen_options_rec.gen_cost_incl_open_comm_flag ;
558 FP_Cols_Rec.gen_cost_ret_manual_line_flag := l_default_gen_options_rec.gen_cost_ret_manual_line_flag ;
559 FP_Cols_Rec.gen_cost_incl_unspent_amt_flag := l_default_gen_options_rec.gen_cost_incl_unspent_amt_flag ;
560 FP_Cols_Rec.gen_rev_src_code := l_default_gen_options_rec.gen_rev_src_code ;
561 FP_Cols_Rec.gen_rev_etc_src_code := l_default_gen_options_rec.gen_rev_etc_src_code ;
562 FP_Cols_Rec.gen_rev_incl_change_doc_flag := l_default_gen_options_rec.gen_rev_incl_change_doc_flag ;
563 FP_Cols_Rec.gen_rev_incl_bill_event_flag := l_default_gen_options_rec.gen_rev_incl_bill_event_flag ;
564 FP_Cols_Rec.gen_rev_ret_manual_line_flag := l_default_gen_options_rec.gen_rev_ret_manual_line_flag ;
565 /*** Bug 3580727
566 FP_Cols_Rec.gen_rev_incl_unspent_amt_flag := l_default_gen_options_rec.gen_rev_incl_unspent_amt_flag ;
567 ***/
568 FP_Cols_Rec.gen_src_cost_plan_type_id := l_default_gen_options_rec.gen_src_cost_plan_type_id ;
569 FP_Cols_Rec.gen_src_cost_plan_version_id := l_default_gen_options_rec.gen_src_cost_plan_version_id ;
570 FP_Cols_Rec.gen_src_cost_plan_ver_code := l_default_gen_options_rec.gen_src_cost_plan_ver_code ;
571 FP_Cols_Rec.gen_src_rev_plan_type_id := l_default_gen_options_rec.gen_src_rev_plan_type_id ;
572 FP_Cols_Rec.gen_src_rev_plan_version_id := l_default_gen_options_rec.gen_src_rev_plan_version_id ;
573 FP_Cols_Rec.gen_src_rev_plan_ver_code := l_default_gen_options_rec.gen_src_rev_plan_ver_code ;
574 FP_Cols_Rec.gen_src_all_plan_type_id := l_default_gen_options_rec.gen_src_all_plan_type_id ;
575 FP_Cols_Rec.gen_src_all_plan_version_id := l_default_gen_options_rec.gen_src_all_plan_version_id ;
576 FP_Cols_Rec.gen_src_all_plan_ver_code := l_default_gen_options_rec.gen_src_all_plan_ver_code ;
577 FP_Cols_Rec.gen_all_src_code := l_default_gen_options_rec.gen_all_src_code ;
578 FP_Cols_Rec.gen_all_etc_src_code := l_default_gen_options_rec.gen_all_etc_src_code ;
579 FP_Cols_Rec.gen_all_incl_change_doc_flag := l_default_gen_options_rec.gen_all_incl_change_doc_flag ;
580 FP_Cols_Rec.gen_all_incl_open_comm_flag := l_default_gen_options_rec.gen_all_incl_open_comm_flag ;
581 FP_Cols_Rec.gen_all_ret_manual_line_flag := l_default_gen_options_rec.gen_all_ret_manual_line_flag ;
582 FP_Cols_Rec.gen_all_incl_bill_event_flag := l_default_gen_options_rec.gen_all_incl_bill_event_flag ;
583 FP_Cols_Rec.gen_all_incl_unspent_amt_flag := l_default_gen_options_rec.gen_all_incl_unspent_amt_flag ;
584 FP_Cols_Rec.gen_cost_actual_amts_thru_code := l_default_gen_options_rec.gen_cost_actual_amts_thru_code ;
585 FP_Cols_Rec.gen_rev_actual_amts_thru_code := l_default_gen_options_rec.gen_rev_actual_amts_thru_code ;
586 FP_Cols_Rec.gen_all_actual_amts_thru_code := l_default_gen_options_rec.gen_all_actual_amts_thru_code ;
587 -- start of FP M Phase II Dev changes
588 FP_Cols_Rec.gen_src_cost_wp_version_id := l_default_gen_options_rec.gen_src_cost_wp_version_id ;
589 FP_Cols_Rec.gen_src_cost_wp_ver_code := l_default_gen_options_rec.gen_src_cost_wp_ver_code ;
590 FP_Cols_Rec.gen_src_rev_wp_version_id := l_default_gen_options_rec.gen_src_rev_wp_version_id ;
591 FP_Cols_Rec.gen_src_rev_wp_ver_code := l_default_gen_options_rec.gen_src_rev_wp_ver_code ;
592 FP_Cols_Rec.gen_src_all_wp_version_id := l_default_gen_options_rec.gen_src_all_wp_version_id ;
593 FP_Cols_Rec.gen_src_all_wp_ver_code := l_default_gen_options_rec.gen_src_all_wp_ver_code ;
594 FP_Cols_Rec.revenue_derivation_method := l_default_gen_options_rec.revenue_derivation_method ; --Bug 5462471
595 -- end of FP M Phase II Dev changes
596 FP_Cols_Rec.copy_etc_from_plan_flag := l_default_gen_options_rec.copy_etc_from_plan_flag ; --bug# 8318932
597 -- Added the three colums for ms-excel options tab in web Adi Changes
598 -- CBS
599 FP_Cols_Rec.default_raw_cost := l_default_gen_options_rec.default_raw_cost ;
600 FP_Cols_Rec.default_bill_rate := l_default_gen_options_rec.default_bill_rate ;
601 FP_Cols_Rec.def_markup_percentage := l_default_gen_options_rec.def_markup_percentage ;
602 FP_Cols_Rec.def_raw_cost_currency_code := l_default_gen_options_rec.def_raw_cost_currency_code ;
603 FP_Cols_Rec.def_bill_rate_currency_code := l_default_gen_options_rec.def_bill_rate_currency_code ;
604 --CBS
605 IF NVL(l_webadi_profile,'N') = 'Y' THEN
606
607 IF (l_fp_preference_code = PA_FP_CONSTANTS_PKG.G_PREF_COST_ONLY OR
608 l_fp_preference_code = PA_FP_CONSTANTS_PKG.G_PREF_COST_AND_REV_SEP ) THEN
609
610 IF P_PA_DEBUG_MODE = 'Y' THEN
611 pa_debug.g_err_stage := 'inside cost only preference code FP_Cols_Rec.cost_time_phased_code :: '||FP_Cols_Rec.cost_time_phased_code;
612 pa_debug.write('Create_FP_Option: ' || l_module_name,pa_debug.g_err_stage,5);
613 END IF;
614
615 IF FP_Cols_Rec.cost_time_phased_code in ('G','P') THEN
616
617 IF P_PA_DEBUG_MODE = 'Y' THEN
618 pa_debug.g_err_stage := 'inside cost_time_phased_code is G or p , l_source_plan_class_code '|| plan_type_info_rec.plan_class_code;
619 pa_debug.write('Create_FP_Option: ' || l_module_name,pa_debug.g_err_stage,5);
620 END IF;
621
622 IF plan_type_info_rec.plan_class_code = 'BUDGET' THEN
623
624 FP_Cols_Rec.cost_layout_code := 'PE_BUDGET';
625 ELSIF plan_type_info_rec.plan_class_code = 'FORECAST' THEN
626 FP_Cols_Rec.cost_layout_code := 'PE_FORECAST';
627 END IF;
628 ELSE
629 FP_Cols_Rec.cost_layout_code := l_default_gen_options_rec.cost_layout_code;
630 END IF;
631 END IF;
632
633 IF (l_fp_preference_code = PA_FP_CONSTANTS_PKG.G_PREF_REVENUE_ONLY OR
634 l_fp_preference_code = PA_FP_CONSTANTS_PKG.G_PREF_COST_AND_REV_SEP ) THEN
635
636 IF FP_Cols_Rec.revenue_time_phased_code in ('G','P') THEN
637
638 IF plan_type_info_rec.plan_class_code = 'BUDGET' THEN
639 FP_Cols_Rec.revenue_layout_code := 'PE_BUDGET';
640 ELSIF plan_type_info_rec.plan_class_code = 'FORECAST' THEN
641 FP_Cols_Rec.revenue_layout_code := 'PE_FORECAST';
642 END IF;
643 ELSE
644 FP_Cols_Rec.revenue_layout_code := l_default_gen_options_rec.revenue_layout_code ;
645 END IF;
646 END IF;
647
648 IF l_fp_preference_code = PA_FP_CONSTANTS_PKG.G_PREF_COST_AND_REV_SAME THEN
649
650 IF FP_Cols_Rec.all_time_phased_code in ('G','P') THEN
651 IF plan_type_info_rec.plan_class_code = 'BUDGET' THEN
652 FP_Cols_Rec.all_layout_code := 'PE_BUDGET';
653 ELSIF plan_type_info_rec.plan_class_code = 'FORECAST' THEN
654 FP_Cols_Rec.all_layout_code := 'PE_FORECAST';
655 END IF;
656 ELSE
657 FP_Cols_Rec.all_layout_code := l_default_gen_options_rec.all_layout_code ;
658 END IF;
659 END IF;
660
661 -- FP_Cols_Rec.cost_layout_code := l_default_gen_options_rec.cost_layout_code ;
662 -- FP_Cols_Rec.revenue_layout_code := l_default_gen_options_rec.revenue_layout_code ;
663 -- FP_Cols_Rec.all_layout_code := l_default_gen_options_rec.all_layout_code ;
664 END IF;
665
666 END IF;
667
668 ELSIF l_target_option_level_code = PA_FP_CONSTANTS_PKG.G_OPTION_LEVEL_PLAN_VERSION
669 THEN
670
671 -- Check if target is a control document
672
673 OPEN plan_version_info_cur(l_plan_version_id);
674 FETCH plan_version_info_cur INTO plan_version_info_rec;
675 IF plan_version_info_cur%NOTFOUND THEN
676 RAISE PA_FP_CONSTANTS_PKG.INVALID_ARG_EXC;
677 END IF;
678 CLOSE plan_version_info_cur;
679
680 IF (plan_version_info_rec.ci_id IS NOT NULL) THEN
681 -- We are in the context of ci version
682
683 -- Null out RBS version id
684 FP_Cols_Rec.rbs_version_id := null;
685
686 -- Null out all the genration options and rate schedule columns
687
688 /* Though rate schedules tab is not shown in change order, we
689 * need to copy them from current working version
690 FP_Cols_Rec.use_planning_rates_flag := null ;
691 FP_Cols_Rec.res_class_raw_cost_sch_id := null ;
692 FP_Cols_Rec.res_class_bill_rate_sch_id := null ;
693 FP_Cols_Rec.cost_emp_rate_sch_id := null ;
694 FP_Cols_Rec.cost_job_rate_sch_id := null ;
695 FP_Cols_Rec.cost_non_labor_res_rate_sch_id := null ;
696 FP_Cols_Rec.cost_res_class_rate_sch_id := null ;
697 FP_Cols_Rec.cost_burden_rate_sch_id := null ;
698 FP_Cols_Rec.rev_emp_rate_sch_id := null ;
699 FP_Cols_Rec.rev_job_rate_sch_id := null ;
700 FP_Cols_Rec.rev_non_labor_res_rate_sch_id := null ;
701 FP_Cols_Rec.rev_res_class_rate_sch_id := null ;
702 */
703 /** Bug 3580727
704 FP_Cols_Rec.all_emp_rate_sch_id := null ;
705 FP_Cols_Rec.all_job_rate_sch_id := null ;
706 FP_Cols_Rec.all_non_labor_res_rate_sch_id := null ;
707 FP_Cols_Rec.all_res_class_rate_sch_id := null ;
708 FP_Cols_Rec.all_burden_rate_sch_id := null ;
709 **/
710
711 FP_Cols_Rec.gen_cost_src_code := null ;
712 FP_Cols_Rec.gen_cost_etc_src_code := null ;
713 FP_Cols_Rec.gen_cost_incl_change_doc_flag := null ;
714 FP_Cols_Rec.gen_cost_incl_open_comm_flag := null ;
715 FP_Cols_Rec.gen_cost_ret_manual_line_flag := null ;
716 FP_Cols_Rec.gen_cost_incl_unspent_amt_flag := null ;
717 FP_Cols_Rec.gen_rev_src_code := null ;
718 FP_Cols_Rec.gen_rev_etc_src_code := null ;
719 FP_Cols_Rec.gen_rev_incl_change_doc_flag := null ;
720 FP_Cols_Rec.gen_rev_incl_bill_event_flag := null ;
721 FP_Cols_Rec.gen_rev_ret_manual_line_flag := null ;
722 /** Bug 3580727
723 FP_Cols_Rec.gen_rev_incl_unspent_amt_flag := null ;
724 **/
725 FP_Cols_Rec.gen_src_cost_plan_type_id := null ;
726 FP_Cols_Rec.gen_src_cost_plan_version_id := null ;
727 FP_Cols_Rec.gen_src_cost_plan_ver_code := null ;
728 FP_Cols_Rec.gen_src_rev_plan_type_id := null ;
729 FP_Cols_Rec.gen_src_rev_plan_version_id := null ;
730 FP_Cols_Rec.gen_src_rev_plan_ver_code := null ;
731 FP_Cols_Rec.gen_src_all_plan_type_id := null ;
732 FP_Cols_Rec.gen_src_all_plan_version_id := null ;
733 FP_Cols_Rec.gen_src_all_plan_ver_code := null ;
734 FP_Cols_Rec.gen_all_src_code := null ;
735 FP_Cols_Rec.gen_all_etc_src_code := null ;
736 FP_Cols_Rec.gen_all_incl_change_doc_flag := null ;
737 FP_Cols_Rec.gen_all_incl_open_comm_flag := null ;
738 FP_Cols_Rec.gen_all_ret_manual_line_flag := null ;
739 FP_Cols_Rec.gen_all_incl_bill_event_flag := null ;
740 FP_Cols_Rec.gen_all_incl_unspent_amt_flag := null ;
741 FP_Cols_Rec.gen_cost_actual_amts_thru_code := null ;
742 FP_Cols_Rec.gen_rev_actual_amts_thru_code := null ;
743 FP_Cols_Rec.gen_all_actual_amts_thru_code := null ;
744 -- Start of FP M Phase II Dev changes
745 FP_Cols_Rec.gen_src_cost_wp_version_id := null ;
746 FP_Cols_Rec.gen_src_cost_wp_ver_code := null ;
747 FP_Cols_Rec.gen_src_rev_wp_version_id := null ;
748 FP_Cols_Rec.gen_src_rev_wp_ver_code := null ;
749 FP_Cols_Rec.gen_src_all_wp_version_id := null ;
750 FP_Cols_Rec.gen_src_all_wp_ver_code := null ;
751 -- End of FP M Phase II Dev changes
752 FP_Cols_Rec.copy_etc_from_plan_flag := null ; --bug#8318932
753 ELSE -- target is not ci version
754
755 -- If source option is plan type
756 IF (l_source_option_level_code = PA_FP_CONSTANTS_PKG.G_OPTION_LEVEL_PLAN_TYPE AND
757 nvl(plan_type_info_rec.use_for_workplan_flag,'N') <>'Y' )
758 THEN
759
760 -- Gen Src Version Id should be initialized using Gen Src Version Code
761 IF (l_fp_preference_code = PA_FP_CONSTANTS_PKG.G_PREF_COST_ONLY) AND
762 (FP_Cols_Rec.gen_src_cost_plan_type_id IS NOT NULL ) AND
763 (FP_Cols_Rec.gen_src_cost_plan_ver_code IS NOT NULL)
764 THEN
765
766 -- Call private method fetch Gen_Src_Plan_Version_Id
767 FP_Cols_Rec.gen_src_cost_plan_version_id := Gen_Src_Plan_Version_Id (
768 p_target_project_id => p_target_project_id
769 ,p_target_version_type => 'COST'
770 ,p_gen_src_plan_type_id => FP_Cols_Rec.gen_src_cost_plan_type_id
771 ,p_gen_src_plan_ver_code => FP_Cols_Rec.gen_src_cost_plan_ver_code );
772
773 -- Null out generation source plan version code
774 FP_Cols_Rec.gen_src_cost_plan_ver_code := NULL;
775
776 ELSIF (l_fp_preference_code = PA_FP_CONSTANTS_PKG.G_PREF_REVENUE_ONLY) AND
777 (FP_Cols_Rec.gen_src_rev_plan_type_id IS NOT NULL ) AND
778 (FP_Cols_Rec.gen_src_rev_plan_ver_code IS NOT NULL)
779 THEN
780
781 -- Call private method fetch Gen_Src_Plan_Version_Id
782 FP_Cols_Rec.gen_src_rev_plan_version_id := Gen_Src_Plan_Version_Id (
783 p_target_project_id => p_target_project_id
784 ,p_target_version_type => 'REVENUE'
785 ,p_gen_src_plan_type_id => FP_Cols_Rec.gen_src_rev_plan_type_id
786 ,p_gen_src_plan_ver_code => FP_Cols_Rec.gen_src_rev_plan_ver_code );
787
788 -- Null out generation source plan version code
789 FP_Cols_Rec.gen_src_rev_plan_ver_code := NULL;
790
791 ELSIF (l_fp_preference_code = PA_FP_CONSTANTS_PKG.G_PREF_COST_AND_REV_SAME) AND
792 (FP_Cols_Rec.gen_src_all_plan_type_id IS NOT NULL ) AND
793 (FP_Cols_Rec.gen_src_all_plan_ver_code IS NOT NULL)
794 THEN
795
796 -- Call private method fetch Gen_Src_Plan_Version_Id
797 FP_Cols_Rec.gen_src_all_plan_version_id := Gen_Src_Plan_Version_Id (
798 p_target_project_id => p_target_project_id
799 ,p_target_version_type => 'ALL'
800 ,p_gen_src_plan_type_id => FP_Cols_Rec.gen_src_all_plan_type_id
801 ,p_gen_src_plan_ver_code => FP_Cols_Rec.gen_src_all_plan_ver_code );
802
803 -- Null out generation source plan version code
804 FP_Cols_Rec.gen_src_all_plan_ver_code := NULL;
805 END IF;
806
807 -- Gen Src Workplan Version Id should be initialized using Gen Src Wp version code
808 IF (l_fp_preference_code = PA_FP_CONSTANTS_PKG.G_PREF_COST_ONLY) AND
809 (FP_Cols_Rec.gen_src_cost_wp_ver_code IS NOT NULL)
810 THEN
811
812 -- Call private method fetch Gen_Src_WP_Version_Id
813 FP_Cols_Rec.gen_src_cost_wp_version_id := Gen_Src_WP_Version_Id (
814 p_target_project_id => p_target_project_id
815 ,p_gen_src_wp_ver_code => FP_Cols_Rec.gen_src_cost_wp_ver_code );
816
817 -- Null out generation source workplan version code
818 FP_Cols_Rec.gen_src_cost_wp_ver_code := NULL;
819
820 ELSIF (l_fp_preference_code = PA_FP_CONSTANTS_PKG.G_PREF_REVENUE_ONLY) AND
821 (FP_Cols_Rec.gen_src_rev_wp_ver_code IS NOT NULL)
822 THEN
823
824 -- Call private method fetch Gen_Src_WP_Version_Id
825 FP_Cols_Rec.gen_src_rev_wp_version_id := Gen_Src_WP_Version_Id (
826 p_target_project_id => p_target_project_id
827 ,p_gen_src_wp_ver_code => FP_Cols_Rec.gen_src_rev_wp_ver_code );
828
829 -- Null out generation source workplan version code
830 FP_Cols_Rec.gen_src_rev_wp_ver_code := NULL;
831
832 ELSIF (l_fp_preference_code = PA_FP_CONSTANTS_PKG.G_PREF_COST_AND_REV_SAME) AND
833 (FP_Cols_Rec.gen_src_all_wp_ver_code IS NOT NULL)
834 THEN
835
836 -- Call private method fetch Gen_Src_WP_Version_Id
837 FP_Cols_Rec.gen_src_all_wp_version_id := Gen_Src_WP_Version_Id (
838 p_target_project_id => p_target_project_id
839 ,p_gen_src_wp_ver_code => FP_Cols_Rec.gen_src_all_wp_ver_code );
840
841 -- Null out generation source workplan version code
842 FP_Cols_Rec.gen_src_all_wp_ver_code := NULL;
843 END IF;
844 ELSIF l_source_option_level_code = PA_FP_CONSTANTS_PKG.G_OPTION_LEVEL_PLAN_VERSION
845 THEN
846
847 -- 3/28/2004 FP M Phase II Dev Effort Copy Project Impact
848 IF l_source_project_id <> p_target_project_id
849 THEN
850 -- Null out all gen source version id columns
851 FP_Cols_Rec.gen_src_cost_wp_version_id := null ;
852 FP_Cols_Rec.gen_src_rev_wp_version_id := null ;
853 FP_Cols_Rec.gen_src_all_wp_version_id := null ;
854 FP_Cols_Rec.gen_src_cost_plan_version_id := null ;
855 FP_Cols_Rec.gen_src_rev_plan_version_id := null ;
856 FP_Cols_Rec.gen_src_all_plan_version_id := null ;
857 END IF;
858
859 END IF; -- l_source_option_level_code
860
861 END IF; -- target version is ci/other kind of version
862
863 END IF; -- l_target_option_level_code
864
865 END IF; -- l_target_option_level_code <> PA_FP_CONSTANTS_PKG.G_OPTION_LEVEL_PROJECT
866
867 -- End of FP M Dev effort
868
869 /* MC Options need to be copied only in the context of plan type or plan version.
870 The logic of copying mc options is common and hence done outside all IFs. */
871
872 IF l_target_option_level_code <> PA_FP_CONSTANTS_PKG.G_OPTION_LEVEL_PROJECT THEN
873 /* While copying project, the MC options should be inherited from the source option
874 i.e., from the source project's plan type or plan version */
875 IF p_source_proj_fp_option_id IS NOT NULL THEN
876 IF P_PA_DEBUG_MODE = 'Y' THEN
877 pa_debug.g_err_stage := TO_CHAR(l_Stage)||': getting mc options from source option';
878 pa_debug.write('Create_FP_Option: ' || l_module_name,pa_debug.g_err_stage,5);
879 END IF;
880
881 fp_mc_cols_rec := get_fp_proj_mc_options(p_source_proj_fp_option_id);
882
883 IF P_PA_DEBUG_MODE = 'Y' THEN
884 pa_debug.g_err_stage := TO_CHAR(l_Stage)||'retruned from get_fp_proj_mc_options';
885 pa_debug.write('Create_FP_Option: ' || l_module_name,pa_debug.g_err_stage,3);
886 END IF;
887
888 ELSE
889 /* MC Options are always inherited from the Plan Type MC options. In this case
890 plan type id cannot be null (validation done) */
891 IF l_target_option_level_code = PA_FP_CONSTANTS_PKG.G_OPTION_LEVEL_PLAN_TYPE THEN
892 IF P_PA_DEBUG_MODE = 'Y' THEN
893 pa_debug.g_err_stage := TO_CHAR(l_Stage)||': getting mc options from plan type';
894 pa_debug.write('Create_FP_Option: ' || l_module_name,pa_debug.g_err_stage,5);
895 END IF;
896
897 fp_mc_cols_rec := get_fp_plan_type_mc_options(l_plan_type_id);
898
899 ELSE
900 IF l_par_Proj_FP_Options_ID IS NOT NULL THEN
901 IF P_PA_DEBUG_MODE = 'Y' THEN
902 pa_debug.g_err_stage := TO_CHAR(l_Stage)||': getting mc options from parent';
903 pa_debug.write('Create_FP_Option: ' || l_module_name,pa_debug.g_err_stage,5);
904 END IF;
905
906 /* #2598361: Modified the call from get_fp_plan_type_mc_options to
907 get_fp_proj_mc_options as the option level code is Plan Version. */
908 fp_mc_cols_rec := get_fp_proj_mc_options(l_par_Proj_FP_Options_ID);
909 ELSE
910 /* there is no default value for mc rec */
911 IF P_PA_DEBUG_MODE = 'Y' THEN
912 pa_debug.g_err_stage := TO_CHAR(l_Stage)||': mc options cannot be determined. these are nulls';
913 pa_debug.write('Create_FP_Option: ' || l_module_name,pa_debug.g_err_stage,5);
914 END IF;
915 fp_mc_cols_rec := null;
916 END IF;
917 END IF;
918 END IF;
919 /* Bug# 2637789 */
920 /* Bug 2747255 - Set the Cost conv attributes null if pref cost is REVENUE_ONLY and vice versa */
921 IF l_fp_preference_code = PA_FP_CONSTANTS_PKG.G_PREF_COST_ONLY THEN
922 FP_Mc_Cols_Rec.approved_rev_plan_type_flag := 'N';
923 FP_Mc_Cols_Rec.primary_rev_forecast_flag := 'N';
924 FP_Mc_Cols_Rec.project_rev_rate_type := Null;
925 FP_Mc_Cols_Rec.project_rev_rate_date_type := Null;
926 FP_Mc_Cols_Rec.project_rev_rate_date := Null;
927 FP_Mc_Cols_Rec.projfunc_rev_rate_type := Null;
928 FP_Mc_Cols_Rec.projfunc_rev_rate_date_type := Null;
929 FP_Mc_Cols_Rec.projfunc_rev_rate_date := Null;
930 ELSIF l_fp_preference_code = PA_FP_CONSTANTS_PKG.G_PREF_REVENUE_ONLY THEN
931 FP_Mc_Cols_Rec.approved_cost_plan_type_flag := 'N';
932 FP_Mc_Cols_Rec.primary_cost_forecast_flag := 'N';
933 FP_Mc_Cols_Rec.projfunc_cost_rate_type := Null;
934 FP_Mc_Cols_Rec.projfunc_cost_rate_date_type := Null;
935 FP_Mc_Cols_Rec.projfunc_cost_rate_date := Null;
936 FP_Mc_Cols_Rec.project_cost_rate_type := Null;
937 FP_Mc_Cols_Rec.project_cost_rate_date_type := Null;
938 FP_Mc_Cols_Rec.project_cost_rate_date := Null;
939 END IF;
940 END IF;
941 IF P_PA_DEBUG_MODE = 'Y' THEN
942 pa_debug.g_err_stage := TO_CHAR(l_Stage)||'Done with conv attr settings';
943 pa_debug.write('Create_FP_Option: ' || l_module_name,pa_debug.g_err_stage,3);
944 END IF;
945
946
947 l_stage := 500;
948
949 -- 3/30/2004 Raja FP M Phase II Dev Changes
950 -- If source project and target project are different do not copy
951 -- the current planning periods from souce option. They should be
952 -- defaulted to PA/GL period inwhich nvl(project start date, sysdate)
953 -- falls
954 IF l_source_project_id <> p_target_project_id
955 THEN
956 IF P_PA_DEBUG_MODE = 'Y' THEN
957 pa_debug.g_err_stage := TO_CHAR(l_Stage)||' About to call Pa_Prj_Period_Profile_Utils.Get_Prj_Defaults';
958 pa_debug.write('Create_FP_Option: ' || l_module_name,pa_debug.g_err_stage,3);
959 END IF;
960
961 Pa_Prj_Period_Profile_Utils.Get_Prj_Defaults(
962 p_project_id => p_target_project_id
963 ,p_info_flag => 'ALL'
964 ,p_create_defaults => 'N'
965 ,x_gl_start_period => l_gl_start_period
966 ,x_gl_end_period => l_gl_end_period
967 ,x_gl_start_Date => l_gl_start_Date
968 ,x_pa_start_period => l_pa_start_period
969 ,x_pa_end_period => l_pa_end_period
970 ,x_pa_start_date => l_pa_start_date
971 ,x_plan_version_exists_flag => l_plan_version_exists_flag
972 ,x_prj_start_date => l_prj_start_date
973 ,x_prj_end_date => l_prj_end_date );
974
975 IF P_PA_DEBUG_MODE = 'Y' THEN
976 pa_debug.g_err_stage := TO_CHAR(l_Stage)||' After call to Pa_Prj_Period_Profile_Utils.Get_Prj_Defaults';
977 pa_debug.write('Create_FP_Option: ' || l_module_name,pa_debug.g_err_stage,3);
978 END IF;
979
980
981 IF FP_Cols_Rec.cost_current_planning_period IS NOT NULL THEN
982 IF FP_Cols_Rec.cost_time_phased_code = 'P' THEN
983 FP_Cols_Rec.cost_current_planning_period := l_pa_start_period;
984 ELSIF FP_Cols_Rec.cost_time_phased_code = 'G' THEN
985 FP_Cols_Rec.cost_current_planning_period := l_gl_start_period;
986 END IF;
987 END IF;
988
989 IF FP_Cols_Rec.rev_current_planning_period IS NOT NULL THEN
990 IF FP_Cols_Rec.revenue_time_phased_code = 'P' THEN
991 FP_Cols_Rec.rev_current_planning_period := l_pa_start_period;
992 ELSIF FP_Cols_Rec.revenue_time_phased_code = 'G' THEN
993 FP_Cols_Rec.rev_current_planning_period := l_gl_start_period;
994 END IF;
995 END IF;
996
997 IF FP_Cols_Rec.all_current_planning_period IS NOT NULL THEN
998 IF FP_Cols_Rec.all_time_phased_code = 'P' THEN
999 FP_Cols_Rec.all_current_planning_period := l_pa_start_period;
1000 ELSIF FP_Cols_Rec.all_time_phased_code = 'G' THEN
1001 FP_Cols_Rec.all_current_planning_period := l_gl_start_period;
1002 END IF;
1003 END IF;
1004
1005 END IF;
1006 IF P_PA_DEBUG_MODE = 'Y' THEN
1007 pa_debug.g_err_stage := TO_CHAR(l_Stage)||'Done with Project defaults';
1008 pa_debug.write('Create_FP_Option: ' || l_module_name,pa_debug.g_err_stage,3);
1009 END IF;
1010
1011 l_stage := 600;
1012
1013 IF (px_target_proj_fp_option_id IS NOT NULL) THEN /* Source is not null and Target is not null */
1014 /* Control of the Program would come to this point when the Source FP Option ID
1015 is not null and Target FP Option ID is not null. This case would occur when
1016 'Copying from a Source FP Option to an exisiting Target FP Option' (Copy From page).
1017 In this case the Target FP option details need to be updated with the
1018 details of the Source FP Option. */
1019
1020 IF P_PA_DEBUG_MODE = 'Y' THEN
1021 pa_debug.g_err_stage := TO_CHAR(l_Stage)||': Calling Table Handler to update row';
1022 pa_debug.write('Create_FP_Option: ' || l_module_name,pa_debug.g_err_stage,3);
1023 pa_debug.g_err_stage := 'plan in multi flag = ' || FP_Cols_Rec.plan_in_multi_curr_flag;
1024 pa_debug.write('Create_FP_Option: ' || l_module_name,pa_debug.g_err_stage,3);
1025 END IF;
1026
1027 /* Bug 3149010 - Logic of not overwritting attribs when the target is ALL */
1028
1029 IF l_source_fp_preference_code = PA_FP_CONSTANTS_PKG.G_PREF_REVENUE_ONLY AND
1030 l_fp_preference_code = PA_FP_CONSTANTS_PKG.G_PREF_COST_AND_REV_SAME THEN
1031 FP_Mc_Cols_Rec.projfunc_cost_rate_type := FND_API.G_MISS_CHAR;
1032 FP_Mc_Cols_Rec.projfunc_cost_rate_date_type := FND_API.G_MISS_CHAR;
1033 FP_Mc_Cols_Rec.projfunc_cost_rate_date := FND_API.G_MISS_DATE;
1034 FP_Mc_Cols_Rec.project_cost_rate_type := FND_API.G_MISS_CHAR;
1035 FP_Mc_Cols_Rec.project_cost_rate_date_type := FND_API.G_MISS_CHAR;
1036 FP_Mc_Cols_Rec.project_cost_rate_date := FND_API.G_MISS_DATE;
1037 FP_Mc_Cols_Rec.approved_cost_plan_type_flag := FND_API.G_MISS_CHAR;
1038
1039 -- Bug 3580727 do not over write cost rate schedule columns
1040 FP_Cols_Rec.cost_emp_rate_sch_id := FND_API.G_MISS_NUM ;
1041 FP_Cols_Rec.cost_job_rate_sch_id := FND_API.G_MISS_NUM ;
1042 FP_Cols_Rec.cost_non_labor_res_rate_sch_id := FND_API.G_MISS_NUM ;
1043 FP_Cols_Rec.cost_res_class_rate_sch_id := FND_API.G_MISS_NUM ;
1044 FP_Cols_Rec.cost_burden_rate_sch_id := FND_API.G_MISS_NUM ;
1045
1046 ELSIF l_source_fp_preference_code = PA_FP_CONSTANTS_PKG.G_PREF_COST_ONLY AND
1047 l_fp_preference_code = PA_FP_CONSTANTS_PKG.G_PREF_COST_AND_REV_SAME THEN
1048 FP_Mc_Cols_Rec.projfunc_rev_rate_type := FND_API.G_MISS_CHAR;
1049 FP_Mc_Cols_Rec.projfunc_rev_rate_date_type := FND_API.G_MISS_CHAR;
1050 FP_Mc_Cols_Rec.projfunc_rev_rate_date := FND_API.G_MISS_DATE;
1051 FP_Mc_Cols_Rec.project_rev_rate_type := FND_API.G_MISS_CHAR;
1052 FP_Mc_Cols_Rec.project_rev_rate_date_type := FND_API.G_MISS_CHAR;
1053 FP_Mc_Cols_Rec.project_rev_rate_date := FND_API.G_MISS_DATE;
1054 FP_Mc_Cols_Rec.approved_rev_plan_type_flag := FND_API.G_MISS_CHAR;
1055
1056 -- Bug 3580727 do not over write revenue rate schedule columns
1057 FP_Cols_Rec.rev_emp_rate_sch_id := FND_API.G_MISS_NUM ;
1058 FP_Cols_Rec.rev_job_rate_sch_id := FND_API.G_MISS_NUM ;
1059 FP_Cols_Rec.rev_non_labor_res_rate_sch_id := FND_API.G_MISS_NUM ;
1060 FP_Cols_Rec.rev_res_class_rate_sch_id := FND_API.G_MISS_NUM ;
1061
1062 END IF;
1063
1064 /* Bug 3149010 - If source and target are versions we need not and should not
1065 overwrite the appr plan type flags
1066
1067 FP M Phase II Development changes: While copying one version to another
1068 existing version do not over write primary forecast columns, rate schedule
1069 setup if target is AR version, generation options set up if plan class codes
1070 differ for source and target versions
1071 */
1072 IF l_source_option_level_code = PA_FP_CONSTANTS_PKG.G_OPTION_LEVEL_PLAN_VERSION AND
1073 l_target_option_level_code = PA_FP_CONSTANTS_PKG.G_OPTION_LEVEL_PLAN_VERSION THEN
1074
1075 FP_Mc_Cols_Rec.approved_rev_plan_type_flag := FND_API.G_MISS_CHAR;
1076 FP_Mc_Cols_Rec.approved_cost_plan_type_flag := FND_API.G_MISS_CHAR;
1077
1078 -- Start of FP M Phase II development changes
1079 FP_Mc_Cols_Rec.primary_cost_forecast_flag := FND_API.G_MISS_CHAR;
1080 FP_Mc_Cols_Rec.primary_rev_forecast_flag := FND_API.G_MISS_CHAR;
1081
1082 -- Do not over write rate schedule data if target is AR version
1083 OPEN opt_info_Cur(px_target_proj_fp_option_id);
1084 FETCH opt_info_Cur INTO opt_info_rec;
1085 CLOSE opt_info_Cur;
1086
1087 IF opt_info_rec.approved_rev_plan_type_flag = 'Y' THEN
1088
1089 FP_Cols_Rec.use_planning_rates_flag := FND_API.G_MISS_CHAR ;
1090 FP_Cols_Rec.res_class_raw_cost_sch_id := FND_API.G_MISS_NUM ;
1091 FP_Cols_Rec.res_class_bill_rate_sch_id := FND_API.G_MISS_NUM ;
1092 FP_Cols_Rec.cost_emp_rate_sch_id := FND_API.G_MISS_NUM ;
1093 FP_Cols_Rec.cost_job_rate_sch_id := FND_API.G_MISS_NUM ;
1094 FP_Cols_Rec.cost_non_labor_res_rate_sch_id := FND_API.G_MISS_NUM ;
1095 FP_Cols_Rec.cost_res_class_rate_sch_id := FND_API.G_MISS_NUM ;
1096 FP_Cols_Rec.cost_burden_rate_sch_id := FND_API.G_MISS_NUM ;
1097 FP_Cols_Rec.rev_emp_rate_sch_id := FND_API.G_MISS_NUM ;
1098 FP_Cols_Rec.rev_job_rate_sch_id := FND_API.G_MISS_NUM ;
1099 FP_Cols_Rec.rev_non_labor_res_rate_sch_id := FND_API.G_MISS_NUM ;
1100 FP_Cols_Rec.rev_res_class_rate_sch_id := FND_API.G_MISS_NUM ;
1101 /*** Bug 3580727
1102 FP_Cols_Rec.all_emp_rate_sch_id := FND_API.G_MISS_NUM ;
1103 FP_Cols_Rec.all_job_rate_sch_id := FND_API.G_MISS_NUM ;
1104 FP_Cols_Rec.all_non_labor_res_rate_sch_id := FND_API.G_MISS_NUM ;
1105 FP_Cols_Rec.all_res_class_rate_sch_id := FND_API.G_MISS_NUM ;
1106 FP_Cols_Rec.all_burden_rate_sch_id := FND_API.G_MISS_NUM ;
1107 ***/
1108 END IF;
1109
1110 -- Do not over write generation options data if target and source versions
1111 -- belong to different plan classes.
1112 IF plan_type_info_rec.plan_class_code <> nvl(l_source_plan_class_code, '-9999') THEN
1113 FP_Cols_Rec.gen_cost_src_code := FND_API.G_MISS_CHAR;
1114 FP_Cols_Rec.gen_cost_etc_src_code := FND_API.G_MISS_CHAR;
1115 FP_Cols_Rec.gen_src_cost_plan_type_id := FND_API.G_MISS_NUM;
1116 FP_Cols_Rec.gen_src_cost_plan_version_id := FND_API.G_MISS_NUM;
1117 FP_Cols_Rec.gen_src_cost_plan_ver_code := FND_API.G_MISS_CHAR;
1118 FP_Cols_Rec.gen_src_cost_wp_version_id := FND_API.G_MISS_NUM;
1119 FP_Cols_Rec.gen_src_cost_wp_ver_code := FND_API.G_MISS_CHAR;
1120 FP_Cols_Rec.gen_cost_actual_amts_thru_code := FND_API.G_MISS_CHAR;
1121 FP_Cols_Rec.gen_cost_incl_change_doc_flag := FND_API.G_MISS_CHAR;
1122 FP_Cols_Rec.gen_cost_incl_open_comm_flag := FND_API.G_MISS_CHAR;
1123 FP_Cols_Rec.gen_cost_ret_manual_line_flag := FND_API.G_MISS_CHAR;
1124 FP_Cols_Rec.gen_cost_incl_unspent_amt_flag := FND_API.G_MISS_CHAR;
1125
1126 FP_Cols_Rec.gen_rev_src_code := FND_API.G_MISS_CHAR;
1127 FP_Cols_Rec.gen_rev_etc_src_code := FND_API.G_MISS_CHAR;
1128 FP_Cols_Rec.gen_src_rev_wp_version_id := FND_API.G_MISS_NUM;
1129 FP_Cols_Rec.gen_src_rev_wp_ver_code := FND_API.G_MISS_CHAR;
1130 FP_Cols_Rec.gen_src_rev_plan_type_id := FND_API.G_MISS_NUM;
1131 FP_Cols_Rec.gen_src_rev_plan_version_id := FND_API.G_MISS_NUM;
1132 FP_Cols_Rec.gen_src_rev_plan_ver_code := FND_API.G_MISS_CHAR;
1133 FP_Cols_Rec.gen_rev_incl_change_doc_flag := FND_API.G_MISS_CHAR;
1134 FP_Cols_Rec.gen_rev_incl_bill_event_flag := FND_API.G_MISS_CHAR;
1135 FP_Cols_Rec.gen_rev_ret_manual_line_flag := FND_API.G_MISS_CHAR;
1136 /*** Bug 3580727
1137 FP_Cols_Rec.gen_rev_incl_unspent_amt_flag := FND_API.G_MISS_CHAR;
1138 ***/
1139 FP_Cols_Rec.gen_rev_actual_amts_thru_code := FND_API.G_MISS_CHAR;
1140
1141 FP_Cols_Rec.gen_all_src_code := FND_API.G_MISS_CHAR;
1142 FP_Cols_Rec.gen_all_etc_src_code := FND_API.G_MISS_CHAR;
1143 FP_Cols_Rec.gen_src_all_plan_type_id := FND_API.G_MISS_NUM;
1144 FP_Cols_Rec.gen_src_all_plan_version_id := FND_API.G_MISS_NUM;
1145 FP_Cols_Rec.gen_src_all_plan_ver_code := FND_API.G_MISS_CHAR;
1146 FP_Cols_Rec.gen_src_all_wp_version_id := FND_API.G_MISS_NUM;
1147 FP_Cols_Rec.gen_src_all_wp_ver_code := FND_API.G_MISS_CHAR;
1148 FP_Cols_Rec.gen_all_incl_change_doc_flag := FND_API.G_MISS_CHAR;
1149 FP_Cols_Rec.gen_all_incl_open_comm_flag := FND_API.G_MISS_CHAR;
1150 FP_Cols_Rec.gen_all_ret_manual_line_flag := FND_API.G_MISS_CHAR;
1151 FP_Cols_Rec.gen_all_incl_bill_event_flag := FND_API.G_MISS_CHAR;
1152 FP_Cols_Rec.gen_all_incl_unspent_amt_flag := FND_API.G_MISS_CHAR;
1153 FP_Cols_Rec.gen_all_actual_amts_thru_code := FND_API.G_MISS_CHAR;
1154 FP_Cols_Rec.copy_etc_from_plan_flag := FND_API.G_MISS_CHAR; --bug#8318932
1155 END IF;
1156 -- End of FP M Phase II development changes
1157
1158 END IF;
1159
1160 -- Bug 3362316, 08-JAN-2003: Added New FP.M Columns --------------------------
1161
1162 PA_PROJ_FP_OPTIONS_PKG.update_row
1163 ( p_proj_fp_options_id => px_target_proj_fp_option_id
1164 ,p_record_version_number => NULL
1165 ,p_project_id => p_target_project_id
1166 ,p_fin_plan_option_level_code => l_target_option_level_code
1167 ,p_fin_plan_type_id => l_plan_type_id
1168 ,p_fin_plan_start_date => FP_Cols_Rec.fin_plan_start_date
1169 ,p_fin_plan_end_date => FP_Cols_Rec.fin_plan_end_date
1170 ,p_fin_plan_preference_code => l_fp_preference_code
1171 ,p_cost_amount_set_id => FP_Cols_Rec.cost_amount_set_iD
1172 ,p_revenue_amount_set_id => FP_Cols_Rec.revenue_amount_set_id
1173 ,p_all_amount_set_id => FP_Cols_Rec.all_amount_set_id
1174 ,p_cost_fin_plan_level_code => FP_Cols_Rec.cost_fin_plan_level_code
1175 ,p_cost_time_phased_code => FP_Cols_Rec.cost_time_phased_code
1176 ,p_cost_resource_list_id => FP_Cols_Rec.cost_resource_list_id
1177 ,p_revenue_fin_plan_level_code => FP_Cols_Rec.revenue_fin_plan_level_code
1178 ,p_revenue_time_phased_code => FP_Cols_Rec.revenue_time_phased_code
1179 ,p_revenue_resource_list_id => FP_Cols_Rec.revenue_resource_list_id
1180 ,p_all_fin_plan_level_code => FP_Cols_Rec.all_fin_plan_level_code
1181 ,p_all_time_phased_code => FP_Cols_Rec.all_time_phased_code
1182 ,p_all_resource_list_id => FP_Cols_Rec.all_resource_list_id
1183 ,p_report_labor_hrs_from_code => FP_Cols_Rec.report_labor_hrs_from_code
1184 ,p_fin_plan_version_id => l_plan_version_id
1185 ,p_plan_in_multi_curr_flag => FP_Cols_Rec.plan_in_multi_curr_flag
1186 ,p_factor_by_code => FP_Cols_Rec.factor_by_code
1187 ,p_default_amount_type_code => FP_Cols_Rec.default_amount_type_code
1188 ,p_default_amount_subtype_code => FP_Cols_Rec.default_amount_subtype_code
1189 ,p_approved_cost_plan_type_flag => FP_Mc_Cols_Rec.approved_cost_plan_type_flag
1190 ,p_approved_rev_plan_type_flag => FP_Mc_Cols_Rec.approved_rev_plan_type_flag
1191 ,p_projfunc_cost_rate_type => FP_Mc_Cols_Rec.projfunc_cost_rate_type
1192 ,p_projfunc_cost_rate_date_type => FP_Mc_Cols_Rec.projfunc_cost_rate_date_type
1193 ,p_projfunc_cost_rate_date => FP_Mc_Cols_Rec.projfunc_cost_rate_date
1194 ,p_projfunc_rev_rate_type => FP_Mc_Cols_Rec.projfunc_rev_rate_type
1195 ,p_projfunc_rev_rate_date_type => FP_Mc_Cols_Rec.projfunc_rev_rate_date_type
1196 ,p_projfunc_rev_rate_date => FP_Mc_Cols_Rec.projfunc_rev_rate_date
1197 ,p_project_cost_rate_type => FP_Mc_Cols_Rec.project_cost_rate_type
1198 ,p_project_cost_rate_date_type => FP_Mc_Cols_Rec.project_cost_rate_date_type
1199 ,p_project_cost_rate_date => FP_Mc_Cols_Rec.project_cost_rate_date
1200 ,p_project_rev_rate_type => FP_Mc_Cols_Rec.project_rev_rate_type
1201 ,p_project_rev_rate_date_type => FP_Mc_Cols_Rec.project_rev_rate_date_type
1202 ,p_project_rev_rate_date => FP_Mc_Cols_Rec.project_rev_rate_date
1203 ,p_margin_derived_from_code => FP_Cols_Rec.margin_derived_from_code
1204 /* Bug 2920954 start of additional parameters added for post fp_k oneoff*/
1205 ,p_select_cost_res_auto_flag => FP_Cols_Rec.select_cost_res_auto_flag
1206 ,p_cost_res_planning_level => FP_Cols_Rec.cost_res_planning_level
1207 ,p_select_rev_res_auto_flag => FP_Cols_Rec.select_rev_res_auto_flag
1208 ,p_revenue_res_planning_level => FP_Cols_Rec.revenue_res_planning_level
1209 ,p_select_all_res_auto_flag => FP_Cols_Rec.select_all_res_auto_flag
1210 ,p_all_res_planning_level => FP_Cols_Rec.all_res_planning_level
1211 /* Bug 2920954 end of additional parameters added for post fp_k oneoff*/
1212 ,p_primary_cost_forecast_flag => FP_Mc_Cols_Rec.primary_cost_forecast_flag
1213 ,p_primary_rev_forecast_flag => FP_Mc_Cols_Rec.primary_rev_forecast_flag
1214 ,p_use_planning_rates_flag => FP_Cols_Rec.use_planning_rates_flag
1215 ,p_rbs_version_id => FP_Cols_Rec.rbs_version_id
1216 ,p_res_class_raw_cost_sch_id => FP_Cols_Rec.res_class_raw_cost_sch_id
1217 ,p_res_class_bill_rate_sch_id => FP_Cols_Rec.res_class_bill_rate_sch_id
1218 ,p_cost_emp_rate_sch_id => FP_Cols_Rec.cost_emp_rate_sch_id
1219 ,p_cost_job_rate_sch_id => FP_Cols_Rec.cost_job_rate_sch_id
1220 ,P_CST_NON_LABR_RES_RATE_SCH_ID => FP_Cols_Rec.cost_non_labor_res_rate_sch_id
1221 ,p_cost_res_class_rate_sch_id => FP_Cols_Rec.cost_res_class_rate_sch_id
1222 ,p_cost_burden_rate_sch_id => FP_Cols_Rec.cost_burden_rate_sch_id
1223 ,p_cost_current_planning_period => FP_Cols_Rec.cost_current_planning_period
1224 ,p_cost_period_mask_id => FP_Cols_Rec.cost_period_mask_id
1225 ,p_rev_emp_rate_sch_id => FP_Cols_Rec.rev_emp_rate_sch_id
1226 ,p_rev_job_rate_sch_id => FP_Cols_Rec.rev_job_rate_sch_id
1227 ,P_REV_NON_LABR_RES_RATE_SCH_ID => FP_Cols_Rec.rev_non_labor_res_rate_sch_id
1228 ,p_rev_res_class_rate_sch_id => FP_Cols_Rec.rev_res_class_rate_sch_id
1229 ,p_rev_current_planning_period => FP_Cols_Rec.rev_current_planning_period
1230 ,p_rev_period_mask_id => FP_Cols_Rec.rev_period_mask_id
1231 /*** Bug 3580727
1232 ,p_all_emp_rate_sch_id => FP_Cols_Rec.all_emp_rate_sch_id
1233 ,p_all_job_rate_sch_id => FP_Cols_Rec.all_job_rate_sch_id
1234 ,P_ALL_NON_LABR_RES_RATE_SCH_ID => FP_Cols_Rec.all_non_labor_res_rate_sch_id
1235 ,p_all_res_class_rate_sch_id => FP_Cols_Rec.all_res_class_rate_sch_id
1236 ,p_all_burden_rate_sch_id => FP_Cols_Rec.all_burden_rate_sch_id
1237 ***/
1238 ,p_all_current_planning_period => FP_Cols_Rec.all_current_planning_period
1239 ,p_all_period_mask_id => FP_Cols_Rec.all_period_mask_id
1240 ,p_gen_cost_src_code => FP_Cols_Rec.gen_cost_src_code
1241 ,p_gen_cost_etc_src_code => FP_Cols_Rec.gen_cost_etc_src_code
1242 ,P_GN_COST_INCL_CHANGE_DOC_FLAG => FP_Cols_Rec.gen_cost_incl_change_doc_flag
1243 ,p_gen_cost_incl_open_comm_flag => FP_Cols_Rec.gen_cost_incl_open_comm_flag
1244 ,P_GN_COST_RET_MANUAL_LINE_FLAG => FP_Cols_Rec.gen_cost_ret_manual_line_flag
1245 ,P_GN_CST_INCL_UNSPENT_AMT_FLAG => FP_Cols_Rec.gen_cost_incl_unspent_amt_flag
1246 ,p_gen_rev_src_code => FP_Cols_Rec.gen_rev_src_code
1247 ,p_gen_rev_etc_src_code => FP_Cols_Rec.gen_rev_etc_src_code
1248 ,p_gen_rev_incl_change_doc_flag => FP_Cols_Rec.gen_rev_incl_change_doc_flag
1249 ,p_gen_rev_incl_bill_event_flag => FP_Cols_Rec.gen_rev_incl_bill_event_flag
1250 ,p_gen_rev_ret_manual_line_flag => FP_Cols_Rec.gen_rev_ret_manual_line_flag
1251 /*** Bug 3580727
1252 ,P_GN_REV_INCL_UNSPENT_AMT_FLAG => FP_Cols_Rec.gen_rev_incl_unspent_amt_flag
1253 ***/
1254 ,p_gen_src_cost_plan_type_id => FP_Cols_Rec.gen_src_cost_plan_type_id
1255 ,p_gen_src_cost_plan_version_id => FP_Cols_Rec.gen_src_cost_plan_version_id
1256 ,p_gen_src_cost_plan_ver_code => FP_Cols_Rec.gen_src_cost_plan_ver_code
1257 ,p_gen_src_rev_plan_type_id => FP_Cols_Rec.gen_src_rev_plan_type_id
1258 ,p_gen_src_rev_plan_version_id => FP_Cols_Rec.gen_src_rev_plan_version_id
1259 ,p_gen_src_rev_plan_ver_code => FP_Cols_Rec.gen_src_rev_plan_ver_code
1260 ,p_gen_src_all_plan_type_id => FP_Cols_Rec.gen_src_all_plan_type_id
1261 ,p_gen_src_all_plan_version_id => FP_Cols_Rec.gen_src_all_plan_version_id
1262 ,p_gen_src_all_plan_ver_code => FP_Cols_Rec.gen_src_all_plan_ver_code
1263 ,p_gen_all_src_code => FP_Cols_Rec.gen_all_src_code
1264 ,p_gen_all_etc_src_code => FP_Cols_Rec.gen_all_etc_src_code
1265 ,p_gen_all_incl_change_doc_flag => FP_Cols_Rec.gen_all_incl_change_doc_flag
1266 ,p_gen_all_incl_open_comm_flag => FP_Cols_Rec.gen_all_incl_open_comm_flag
1267 ,p_gen_all_ret_manual_line_flag => FP_Cols_Rec.gen_all_ret_manual_line_flag
1268 ,p_gen_all_incl_bill_event_flag => FP_Cols_Rec.gen_all_incl_bill_event_flag
1269 ,P_GN_ALL_INCL_UNSPENT_AMT_FLAG => FP_Cols_Rec.gen_all_incl_unspent_amt_flag
1270 ,P_GN_CST_ACTUAL_AMTS_THRU_CODE => FP_Cols_Rec.gen_cost_actual_amts_thru_code
1271 ,P_GN_REV_ACTUAL_AMTS_THRU_CODE => FP_Cols_Rec.gen_rev_actual_amts_thru_code
1272 ,P_GN_ALL_ACTUAL_AMTS_THRU_CODE => FP_Cols_Rec.gen_all_actual_amts_thru_code
1273 ,p_track_workplan_costs_flag => FP_Cols_Rec.track_workplan_costs_flag
1274 -- Start of FP M phase II dev changes
1275 ,p_gen_src_cost_wp_version_id => FP_Cols_Rec.gen_src_cost_wp_version_id
1276 ,p_gen_src_cost_wp_ver_code => FP_Cols_Rec.gen_src_cost_wp_ver_code
1277 ,p_gen_src_rev_wp_version_id => FP_Cols_Rec.gen_src_rev_wp_version_id
1278 ,p_gen_src_rev_wp_ver_code => FP_Cols_Rec.gen_src_rev_wp_ver_code
1279 ,p_gen_src_all_wp_version_id => FP_Cols_Rec.gen_src_all_wp_version_id
1280 ,p_gen_src_all_wp_ver_code => FP_Cols_Rec.gen_src_all_wp_ver_code
1281 -- End of FP M phase II dev changes
1282 --Adding for webadi Changes
1283 ,p_cost_layout_code => FP_Cols_Rec.cost_layout_code
1284 ,p_revenue_layout_code => FP_Cols_Rec.revenue_layout_code
1285 ,p_all_layout_code => FP_Cols_Rec.all_layout_code
1286 ,p_revenue_derivation_method => FP_Cols_Rec.revenue_derivation_method -- bug 5462471
1287 ,p_copy_etc_from_plan_flag => FP_Cols_Rec.copy_etc_from_plan_flag --bug#8318932
1288 ,p_default_raw_cost => FP_Cols_Rec.default_raw_cost --CBS
1289 ,p_default_bill_rate => FP_Cols_Rec.default_bill_rate --CBS
1290 ,p_def_markup_percentage => FP_Cols_Rec.def_markup_percentage --CBS
1291 ,p_def_raw_cost_currency_code => FP_Cols_Rec.def_raw_cost_currency_code --CBS
1292 ,p_def_bill_rate_currency_code => FP_Cols_Rec.def_bill_rate_currency_code --CBS
1293 ,p_row_id => NULL
1294 ,x_return_status => x_return_status);
1295
1296 -- END, Bug 3362316, 08-JAN-2003: Added New FP.M Columns --------------------------
1297
1298 ELSE /* px_target_proj_fp_option_id Target IS NULL */
1299
1300 /* Since the Target FP Option is NULL, a new Proj FP Option has to be created
1301 from the Source FP Option. */
1302 l_stage := 600;
1303
1304 IF P_PA_DEBUG_MODE = 'Y' THEN
1305 pa_debug.g_err_stage := TO_CHAR(l_Stage)||': Calling Table Handler to insert row';
1306 pa_debug.write('Create_FP_Option: ' || l_module_name,pa_debug.g_err_stage,3);
1307 END IF;
1308 IF P_PA_DEBUG_MODE = 'Y' THEN
1309 pa_debug.g_err_stage := TO_CHAR(l_Stage)||'About to insert the row';
1310 pa_debug.write('Create_FP_Option: ' || l_module_name,pa_debug.g_err_stage,3);
1311 END IF;
1312
1313 -- Bug 3362316, 08-JAN-2003: Added New FP.M Columns --------------------------
1314
1315 PA_PROJ_FP_OPTIONS_PKG.Insert_Row
1316 ( px_proj_fp_options_id => px_target_proj_fp_option_id
1317 ,p_project_id => p_target_project_id
1318 ,p_fin_plan_option_level_code => l_target_option_level_code
1319 ,p_fin_plan_type_id => l_plan_type_id
1320 ,p_fin_plan_start_date => FP_Cols_Rec.fin_plan_start_date /* Bug 2798794 */
1321 ,p_fin_plan_end_date => FP_Cols_Rec.fin_plan_end_date /* Bug 2798794 */
1322 ,p_fin_plan_preference_code => l_fp_preference_code
1323 ,p_cost_amount_set_id => FP_Cols_Rec.cost_amount_set_id
1324 ,p_revenue_amount_set_id => FP_Cols_Rec.revenue_amount_set_id
1325 ,p_all_amount_set_id => FP_Cols_Rec.all_amount_set_id
1326 ,p_cost_fin_plan_level_code => FP_Cols_Rec.cost_fin_plan_level_code
1327 ,p_cost_time_phased_code => FP_Cols_Rec.cost_time_phased_code
1328 ,p_cost_resource_list_id => FP_Cols_Rec.cost_resource_list_id
1329 ,p_revenue_fin_plan_level_code => FP_Cols_Rec.revenue_fin_plan_level_code
1330 ,p_revenue_time_phased_code => FP_Cols_Rec.revenue_time_phased_code
1331 ,p_revenue_resource_list_id => FP_Cols_Rec.revenue_resource_list_id
1332 ,p_all_fin_plan_level_code => FP_Cols_Rec.all_fin_plan_level_code
1333 ,p_all_time_phased_code => FP_Cols_Rec.all_time_phased_code
1334 ,p_all_resource_list_id => FP_Cols_Rec.all_resource_list_id
1335 ,p_report_labor_hrs_from_code => FP_Cols_Rec.report_labor_hrs_from_code
1336 ,p_fin_plan_version_id => p_target_fin_plan_version_id
1337 ,p_plan_in_multi_curr_flag => FP_Cols_Rec.plan_in_multi_curr_flag
1338 ,p_factor_by_code => FP_Cols_Rec.factor_by_code
1339 ,p_default_amount_type_code => FP_Cols_Rec.default_amount_type_code
1340 ,p_default_amount_subtype_code => FP_Cols_Rec.default_amount_subtype_code
1341 ,p_approved_cost_plan_type_flag => FP_Mc_Cols_Rec.approved_cost_plan_type_flag
1342 ,p_approved_rev_plan_type_flag => FP_Mc_Cols_Rec.approved_rev_plan_type_flag
1343 ,p_projfunc_cost_rate_type => FP_Mc_Cols_Rec.projfunc_cost_rate_type
1344 ,p_projfunc_cost_rate_date_type => FP_Mc_Cols_Rec.projfunc_cost_rate_date_type
1345 ,p_projfunc_cost_rate_date => FP_Mc_Cols_Rec.projfunc_cost_rate_date
1346 ,p_projfunc_rev_rate_type => FP_Mc_Cols_Rec.projfunc_rev_rate_type
1347 ,p_projfunc_rev_rate_date_type => FP_Mc_Cols_Rec.projfunc_rev_rate_date_type
1348 ,p_projfunc_rev_rate_date => FP_Mc_Cols_Rec.projfunc_rev_rate_date
1349 ,p_project_cost_rate_type => FP_Mc_Cols_Rec.project_cost_rate_type
1350 ,p_project_cost_rate_date_type => FP_Mc_Cols_Rec.project_cost_rate_date_type
1351 ,p_project_cost_rate_date => FP_Mc_Cols_Rec.project_cost_rate_date
1352 ,p_project_rev_rate_type => FP_Mc_Cols_Rec.project_rev_rate_type
1353 ,p_project_rev_rate_date_type => FP_Mc_Cols_Rec.project_rev_rate_date_type
1354 ,p_project_rev_rate_date => FP_Mc_Cols_Rec.project_rev_rate_date
1355 /* Bug 2920954 start of additional parameters added for post fp_k oneoff*/
1356 ,p_margin_derived_from_code => FP_Cols_Rec.margin_derived_from_code
1357 ,p_select_cost_res_auto_flag => FP_Cols_Rec.select_cost_res_auto_flag
1358 ,p_cost_res_planning_level => FP_Cols_Rec.cost_res_planning_level
1359 ,p_select_rev_res_auto_flag => FP_Cols_Rec.select_rev_res_auto_flag
1360 ,p_revenue_res_planning_level => FP_Cols_Rec.revenue_res_planning_level
1361 ,p_select_all_res_auto_flag => FP_Cols_Rec.select_all_res_auto_flag
1362 ,p_all_res_planning_level => FP_Cols_Rec.all_res_planning_level
1363 ,p_refresh_required_flag => l_refresh_required_flag
1364 ,p_request_id => NULL -- Always passed in as null by design.
1365 ,p_processing_code => NULL -- Always passed in as null by design.
1366 /* Bug 2920954 end of additional parameters added for post fp_k oneoff*/
1367 ,p_primary_cost_forecast_flag => FP_Mc_Cols_Rec.primary_cost_forecast_flag
1368 ,p_primary_rev_forecast_flag => FP_Mc_Cols_Rec.primary_rev_forecast_flag
1369 ,p_use_planning_rates_flag => FP_Cols_Rec.use_planning_rates_flag
1370 ,p_rbs_version_id => FP_Cols_Rec.rbs_version_id
1371 ,p_res_class_raw_cost_sch_id => FP_Cols_Rec.res_class_raw_cost_sch_id
1372 ,p_res_class_bill_rate_sch_id => FP_Cols_Rec.res_class_bill_rate_sch_id
1373 ,p_cost_emp_rate_sch_id => FP_Cols_Rec.cost_emp_rate_sch_id
1374 ,p_cost_job_rate_sch_id => FP_Cols_Rec.cost_job_rate_sch_id
1375 ,P_CST_NON_LABR_RES_RATE_SCH_ID => FP_Cols_Rec.cost_non_labor_res_rate_sch_id
1376 ,p_cost_res_class_rate_sch_id => FP_Cols_Rec.cost_res_class_rate_sch_id
1377 ,p_cost_burden_rate_sch_id => FP_Cols_Rec.cost_burden_rate_sch_id
1378 ,p_cost_current_planning_period => FP_Cols_Rec.cost_current_planning_period
1379 ,p_cost_period_mask_id => FP_Cols_Rec.cost_period_mask_id
1380 ,p_rev_emp_rate_sch_id => FP_Cols_Rec.rev_emp_rate_sch_id
1381 ,p_rev_job_rate_sch_id => FP_Cols_Rec.rev_job_rate_sch_id
1382 ,P_REV_NON_LABR_RES_RATE_SCH_ID => FP_Cols_Rec.rev_non_labor_res_rate_sch_id
1383 ,p_rev_res_class_rate_sch_id => FP_Cols_Rec.rev_res_class_rate_sch_id
1384 ,p_rev_current_planning_period => FP_Cols_Rec.rev_current_planning_period
1385 ,p_rev_period_mask_id => FP_Cols_Rec.rev_period_mask_id
1386 /*** Bug 3580727
1387 ,p_all_emp_rate_sch_id => FP_Cols_Rec.all_emp_rate_sch_id
1388 ,p_all_job_rate_sch_id => FP_Cols_Rec.all_job_rate_sch_id
1389 ,P_ALL_NON_LABR_RES_RATE_SCH_ID => FP_Cols_Rec.all_non_labor_res_rate_sch_id
1390 ,p_all_res_class_rate_sch_id => FP_Cols_Rec.all_res_class_rate_sch_id
1391 ,p_all_burden_rate_sch_id => FP_Cols_Rec.all_burden_rate_sch_id
1392 ***/
1393 ,p_all_current_planning_period => FP_Cols_Rec.all_current_planning_period
1394 ,p_all_period_mask_id => FP_Cols_Rec.all_period_mask_id
1395 ,p_gen_cost_src_code => FP_Cols_Rec.gen_cost_src_code
1396 ,p_gen_cost_etc_src_code => FP_Cols_Rec.gen_cost_etc_src_code
1397 ,P_GN_COST_INCL_CHANGE_DOC_FLAG => FP_Cols_Rec.gen_cost_incl_change_doc_flag
1398 ,p_gen_cost_incl_open_comm_flag => FP_Cols_Rec.gen_cost_incl_open_comm_flag
1399 ,P_GN_COST_RET_MANUAL_LINE_FLAG => FP_Cols_Rec.gen_cost_ret_manual_line_flag
1400 ,P_GN_CST_INCL_UNSPENT_AMT_FLAG => FP_Cols_Rec.gen_cost_incl_unspent_amt_flag
1401 ,p_gen_rev_src_code => FP_Cols_Rec.gen_rev_src_code
1402 ,p_gen_rev_etc_src_code => FP_Cols_Rec.gen_rev_etc_src_code
1403 ,p_gen_rev_incl_change_doc_flag => FP_Cols_Rec.gen_rev_incl_change_doc_flag
1404 ,p_gen_rev_incl_bill_event_flag => FP_Cols_Rec.gen_rev_incl_bill_event_flag
1405 ,p_gen_rev_ret_manual_line_flag => FP_Cols_Rec.gen_rev_ret_manual_line_flag
1406 /*** Bug 3580727
1407 ,P_GN_REV_INCL_UNSPENT_AMT_FLAG => FP_Cols_Rec.gen_rev_incl_unspent_amt_flag
1408 ***/
1409 ,p_gen_src_cost_plan_type_id => FP_Cols_Rec.gen_src_cost_plan_type_id
1410 ,p_gen_src_cost_plan_version_id => FP_Cols_Rec.gen_src_cost_plan_version_id
1411 ,p_gen_src_cost_plan_ver_code => FP_Cols_Rec.gen_src_cost_plan_ver_code
1412 ,p_gen_src_rev_plan_type_id => FP_Cols_Rec.gen_src_rev_plan_type_id
1413 ,p_gen_src_rev_plan_version_id => FP_Cols_Rec.gen_src_rev_plan_version_id
1414 ,p_gen_src_rev_plan_ver_code => FP_Cols_Rec.gen_src_rev_plan_ver_code
1415 ,p_gen_src_all_plan_type_id => FP_Cols_Rec.gen_src_all_plan_type_id
1416 ,p_gen_src_all_plan_version_id => FP_Cols_Rec.gen_src_all_plan_version_id
1417 ,p_gen_src_all_plan_ver_code => FP_Cols_Rec.gen_src_all_plan_ver_code
1418 ,p_gen_all_src_code => FP_Cols_Rec.gen_all_src_code
1419 ,p_gen_all_etc_src_code => FP_Cols_Rec.gen_all_etc_src_code
1420 ,p_gen_all_incl_change_doc_flag => FP_Cols_Rec.gen_all_incl_change_doc_flag
1421 ,p_gen_all_incl_open_comm_flag => FP_Cols_Rec.gen_all_incl_open_comm_flag
1422 ,p_gen_all_ret_manual_line_flag => FP_Cols_Rec.gen_all_ret_manual_line_flag
1423 ,p_gen_all_incl_bill_event_flag => FP_Cols_Rec.gen_all_incl_bill_event_flag
1424 ,P_GN_ALL_INCL_UNSPENT_AMT_FLAG => FP_Cols_Rec.gen_all_incl_unspent_amt_flag
1425 ,P_GN_CST_ACTUAL_AMTS_THRU_CODE => FP_Cols_Rec.gen_cost_actual_amts_thru_code
1426 ,P_GN_REV_ACTUAL_AMTS_THRU_CODE => FP_Cols_Rec.gen_rev_actual_amts_thru_code
1427 ,P_GN_ALL_ACTUAL_AMTS_THRU_CODE => FP_Cols_Rec.gen_all_actual_amts_thru_code
1428 ,p_track_workplan_costs_flag => FP_Cols_Rec.track_workplan_costs_flag
1429 -- Start of FP M phase II dev changes
1430 ,p_gen_src_cost_wp_version_id => FP_Cols_Rec.gen_src_cost_wp_version_id
1431 ,p_gen_src_cost_wp_ver_code => FP_Cols_Rec.gen_src_cost_wp_ver_code
1432 ,p_gen_src_rev_wp_version_id => FP_Cols_Rec.gen_src_rev_wp_version_id
1433 ,p_gen_src_rev_wp_ver_code => FP_Cols_Rec.gen_src_rev_wp_ver_code
1434 ,p_gen_src_all_wp_version_id => FP_Cols_Rec.gen_src_all_wp_version_id
1435 ,p_gen_src_all_wp_ver_code => FP_Cols_Rec.gen_src_all_wp_ver_code
1436 -- End of FP M phase II dev changes
1437 --Adding for webadi Changes
1438 ,p_cost_layout_code => FP_Cols_Rec.cost_layout_code
1439 ,p_revenue_layout_code => FP_Cols_Rec.revenue_layout_code
1440 ,p_all_layout_code => FP_Cols_Rec.all_layout_code
1441 ,p_revenue_derivation_method => FP_Cols_Rec.revenue_derivation_method -- bug 5462471
1442 ,p_copy_etc_from_plan_flag => FP_Cols_Rec.copy_etc_from_plan_flag --bug#8318932
1443 ,p_default_raw_cost => FP_Cols_Rec.default_raw_cost --CBS
1444 ,p_default_bill_rate => FP_Cols_Rec.default_bill_rate --CBS
1445 ,p_def_markup_percentage => FP_Cols_Rec.def_markup_percentage --CBS
1446 ,p_def_raw_cost_currency_code => FP_Cols_Rec.def_raw_cost_currency_code --CBS
1447 ,p_def_bill_rate_currency_code => FP_Cols_Rec.def_bill_rate_currency_code --CBS
1448 ,x_row_id => x_row_id
1449 ,x_return_status => x_return_status);
1450
1451 -- End, Bug 3362316, 08-JAN-2003: Added New FP.M Columns --------------------------
1452
1453 END IF;
1454
1455 -- End, jwhite, 26-JUN-2003: Plannable Task Effort --------------------------------
1456
1457 --Adding the code for attaching the layout codes to the plan type if they are being attached from a project or from a plan type.
1458 --For the ms-excel options tab
1459 IF l_target_option_level_code = PA_FP_CONSTANTS_PKG.G_OPTION_LEVEL_PLAN_TYPE
1460 AND (nvl(plan_type_info_rec.use_for_workplan_flag,'N') <> 'Y') THEN
1461
1462 IF NVL(l_webadi_profile,'N') = 'Y' THEN
1463
1464 IF p_source_proj_fp_option_id IS NULL THEN
1465 create_amt_types(
1466 p_project_id => p_target_project_id
1467 ,p_fin_plan_type_id => l_plan_type_id
1468 ,p_plan_preference_code => l_fp_preference_code
1469 ,p_cost_layout_code => FP_Cols_Rec.cost_layout_code
1470 ,p_revenue_layout_code => FP_Cols_Rec.revenue_layout_code
1471 ,p_all_layout_code => FP_Cols_Rec.all_layout_code
1472 ,x_return_status => x_return_status
1473 ,x_msg_count => x_msg_count
1474 ,x_msg_data => x_msg_data);
1475
1476 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1477 raise PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
1478 END IF;
1479
1480 ELSE
1481 copy_amt_types (
1482 p_source_project_id => l_source_project_id
1483 ,p_source_fin_plan_type_id => l_source_plan_type_id
1484 ,p_target_project_id => p_target_project_id
1485 ,p_target_fin_plan_type_id => l_plan_type_id
1486 ,x_return_status => x_return_status
1487 ,x_msg_count => x_msg_count
1488 ,x_msg_data => x_msg_data );
1489
1490
1491 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1492 raise PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
1493 END IF;
1494 END IF; -- source project is null
1495 END IF; -- web adi profile
1496 END IF;
1497
1498 IF P_PA_DEBUG_MODE = 'Y' THEN
1499 pa_debug.g_err_stage := TO_CHAR(l_stage)||': End of Create_FP_Option';
1500 pa_debug.write('Create_FP_Option: ' || l_module_name,pa_debug.g_err_stage,3);
1501 pa_debug.reset_err_stack;
1502 END IF;
1503
1504 EXCEPTION
1505 WHEN PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc THEN
1506 l_msg_count := FND_MSG_PUB.count_msg;
1507 IF l_msg_count = 1 THEN
1508 PA_INTERFACE_UTILS_PUB.get_messages
1509 (p_encoded => FND_API.G_TRUE,
1510 p_msg_index => 1,
1511 p_msg_count => l_msg_count,
1512 p_msg_data => l_msg_data,
1513 p_data => l_data,
1514 p_msg_index_out => l_msg_index_out);
1515 x_msg_data := l_data;
1516 x_msg_count := l_msg_count;
1517 ELSE
1518 x_msg_count := l_msg_count;
1519 END IF;
1520 IF P_PA_DEBUG_MODE = 'Y' THEN
1521 pa_debug.reset_err_stack;
1522 END IF;
1523 RAISE;
1524 WHEN OTHERS THEN
1525 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1526 x_msg_count := 1;
1527 x_msg_data := SQLERRM;
1528 FND_MSG_PUB.add_exc_msg
1529 ( p_pkg_name => 'PA_PROJ_FP_OPTIONS_PUB.Create_FP_Option'
1530 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
1531 IF P_PA_DEBUG_MODE = 'Y' THEN
1532 pa_debug.write('Create_FP_Option: ' || l_module_name,SQLERRM,5);
1533 pa_debug.write('Create_FP_Option: ' || l_module_name,pa_debug.G_Err_Stack,5);
1534 pa_debug.reset_err_stack;
1535 END IF;
1536 RAISE ;
1537 END Create_FP_Option;
1538
1539 /*===========================================================================================
1540 GET_FP_OPTIONS: This procedure returns the details of FP Option ID passed to this procedure
1541 based on the Fin_Plan_Preference_Code passed. p_proj_fp_options_id is the Source FP Option
1542 ID and p_fin_plan_preference_code is the Target Preference code. Hence the details of the
1543 Source FP Option are passed based on the Target and the Source Preference Codes. There are
1544 certain combinations which are invalid and exceptions are raised in these cases. For all
1545 other cases, details are passed based on the combination.
1546
1547 10-AUG-2002 - added logic to derive values in mc attributes and approved cost/revenue plan
1548 types flags.
1549 24-AUG-2002 - it needs to be taken care that while copying from a cost and revenue sep
1550 to another option appropriate values should be copied.
1551 Also amount_set_id derivation needs to be changed.
1552 23-Apr-2003 - Bug 2920954 Modified to set the values of the new columns in the ouput
1553 plsql record type parameter x_fp_cols_rec
1554
1555
1556 r11.5 FP.M Developement ----------------------------------
1557
1558 08-JAN-2004 jwhite Bug 3362316 (HQ)
1559 Extensively rewrote Get_Fp_Options
1560 - All SELECTS from pa_proj_fp_options
1561
1562 23-JAN-2004 rravipat Bug 3354518 (IDC)
1563 ===========================================================================================*/
1564 PROCEDURE Get_FP_Options (
1565 p_proj_fp_options_id IN NUMBER
1566 ,p_target_fp_options_id IN NUMBER
1567 ,p_fin_plan_preference_code IN VARCHAR2
1568 ,p_target_fp_option_level_code IN VARCHAR2 -- Adding this new parameter as a part of the ms-excel options
1569 ,x_fp_cols_rec OUT NOCOPY FP_COLS
1570 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1571 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
1572 ,x_msg_data OUT NOCOPY VARCHAR2) is --File.Sql.39 bug 4440895
1573
1574 l_debug_mode VARCHAR2(30);
1575 l_msg_count NUMBER := 0;
1576 l_data VARCHAR2(2000);
1577 l_msg_data VARCHAR2(2000);
1578 l_msg_index_out NUMBER;
1579
1580 l_source_fin_plan_pref pa_proj_fp_options.FIN_PLAN_PREFERENCE_CODE%TYPE;
1581 l_target_fin_plan_pref pa_proj_fp_options.FIN_PLAN_PREFERENCE_CODE%TYPE;
1582
1583 l_fin_plan_option_level_code pa_proj_fp_options.FIN_PLAN_OPTION_LEVEL_CODE%TYPE;
1584 l_project_id pa_proj_fp_options.PROJECT_ID%TYPE;
1585
1586 l_cost_amount_set_id pa_fin_plan_amount_sets.fin_plan_amount_set_id%TYPE;
1587 l_revenue_amount_set_id pa_fin_plan_amount_sets.fin_plan_amount_set_id%TYPE;
1588 l_all_amount_set_id pa_fin_plan_amount_sets.fin_plan_amount_set_id%TYPE;
1589
1590 l_target_all_amount_set_id pa_fin_plan_amount_sets.fin_plan_amount_set_id%TYPE;
1591
1592 l_raw_cost_flag pa_fin_plan_amount_sets.raw_cost_flag%TYPE ;
1593 l_burdened_cost_flag pa_fin_plan_amount_sets.burdened_cost_flag%TYPE;
1594 l_revenue_flag pa_fin_plan_amount_sets.revenue_flag%TYPE ;
1595 l_cost_qty_flag pa_fin_plan_amount_sets.cost_qty_flag%TYPE ;
1596 l_revenue_qty_flag pa_fin_plan_amount_sets.revenue_qty_flag%TYPE ;
1597 l_all_qty_flag pa_fin_plan_amount_sets.all_qty_flag%TYPE ;
1598
1599 l_target_raw_cost_flag pa_fin_plan_amount_sets.raw_cost_flag%TYPE ;
1600 l_target_burdened_cost_flag pa_fin_plan_amount_sets.burdened_cost_flag%TYPE;
1601 l_target_revenue_flag pa_fin_plan_amount_sets.revenue_flag%TYPE ;
1602
1603 -- FP M dev effort Defined new variables
1604 l_bill_rate_flag pa_fin_plan_amount_sets.bill_rate_flag%TYPE ;
1605 l_cost_rate_flag pa_fin_plan_amount_sets.cost_rate_flag%TYPE;
1606 l_burden_rate_flag pa_fin_plan_amount_sets.burden_rate_flag%TYPE;
1607 l_target_bill_rate_flag pa_fin_plan_amount_sets.bill_rate_flag%TYPE ;
1608 l_target_cost_rate_flag pa_fin_plan_amount_sets.cost_rate_flag%TYPE;
1609 l_target_burd_rate_flag pa_fin_plan_amount_sets.burden_rate_flag%TYPE;
1610
1611 BEGIN
1612 IF P_PA_DEBUG_MODE = 'Y' THEN
1613 pa_debug.set_err_stack('PA_PROJ_FP_OPTIONS_PUB.Get_FP_Options');
1614 fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
1615 l_debug_mode := NVL(l_debug_mode, 'Y');
1616 pa_debug.set_process('PLSQL','LOG',l_debug_mode);
1617 END IF;
1618
1619 x_return_status := FND_API.G_RET_STS_SUCCESS;
1620
1621 SELECT fin_plan_preference_code,
1622 fin_plan_option_level_code,
1623 project_id
1624 INTO l_source_fin_plan_pref,
1625 l_fin_plan_option_level_code,
1626 l_project_id
1627 FROM pa_proj_fp_options
1628 WHERE proj_fp_options_id = p_proj_fp_options_id;
1629
1630 l_target_fin_plan_pref := Nvl(p_fin_plan_preference_code, l_source_fin_plan_pref);
1631
1632 /* For Invalid Combinations of the Source and Target Fin_Plan_Preference_Code,
1633 raise the invalid parameters exception.
1634 The Invalid Combinations are:
1635 =======================================
1636 Source Target
1637 =======================================
1638 COST_AND_REV_SAME COST_AND_REV_SEP
1639 COST_ONLY REVENUE_ONLY
1640 REVENUE_ONLY COST_ONLY
1641 */
1642
1643 /* M22-AUG: these validations are not required. These are not invalid combinations.
1644 IF ((l_source_fin_plan_pref = PA_FP_CONSTANTS_PKG.G_PREF_COST_AND_REV_SAME AND
1645 l_target_fin_plan_pref = PA_FP_CONSTANTS_PKG.G_PREF_COST_AND_REV_SEP) OR
1646
1647 (l_source_fin_plan_pref = PA_FP_CONSTANTS_PKG.G_PREF_COST_AND_REV_SEP AND
1648 l_target_fin_plan_pref IN (PA_FP_CONSTANTS_PKG.G_PREF_COST_ONLY,
1649 PA_FP_CONSTANTS_PKG.G_PREF_REVENUE_ONLY,
1650 PA_FP_CONSTANTS_PKG.G_PREF_COST_AND_REV_SAME))
1651 OR
1652 */
1653 IF (l_source_fin_plan_pref = PA_FP_CONSTANTS_PKG.G_PREF_REVENUE_ONLY AND
1654 l_target_fin_plan_pref = PA_FP_CONSTANTS_PKG.G_PREF_COST_ONLY) OR
1655 (l_source_fin_plan_pref = PA_FP_CONSTANTS_PKG.G_PREF_COST_ONLY AND
1656 l_target_fin_plan_pref = PA_FP_CONSTANTS_PKG.G_PREF_REVENUE_ONLY) THEN
1657
1658 IF P_PA_DEBUG_MODE = 'Y' THEN
1659 pa_debug.g_err_stage := 'Err- Invalid Combination of Source and Target Preference code';
1660 pa_debug.write('Get_FP_Options: ' || l_module_name,pa_debug.g_err_stage,5);
1661 END IF;
1662 x_return_status := FND_API.G_RET_STS_ERROR;
1663 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
1664 p_msg_name => 'PA_FP_INV_PARAM_PASSED');
1665 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
1666
1667 END IF;
1668
1669 -- Initializing all the cost/quantity flags to 'N'
1670 -- for bug#2708782 .This values will be reset to
1671 -- proper values in the below mentioned sqls
1672
1673 l_raw_cost_flag := 'N' ;
1674 l_burdened_cost_flag := 'N' ;
1675 l_revenue_flag := 'N' ;
1676
1677 /* Bug # 2717297 - These variables should not be initialized to N as this
1678 is breaking the nvl chain logic to derive the all amount set id in the case of
1679 cost and revenue together to cost and revenue together option.
1680
1681 l_cost_qty_flag := 'N' ;
1682 l_revenue_qty_flag := 'N' ;
1683 l_all_qty_flag := 'N' ;
1684 */
1685 /* Included p_target_fp_options_id and target amount set ids for bug 3144283.
1686 This was done basically for the case when COST or REVENUE version is copied on to a
1687 COST_AND_REV_SAME version. In this case, we should not overwrite the REVENUE or COST
1688 amount set flags as the case may be for the target option which is having the pref
1689 code as Null */
1690
1691 IF p_target_fp_options_id IS NOT NULL AND
1692 l_target_fin_plan_pref = PA_FP_CONSTANTS_PKG.G_PREF_COST_AND_REV_SAME THEN
1693
1694 IF P_PA_DEBUG_MODE = 'Y' THEN
1695 pa_debug.g_err_stage := 'Inside target opt id not null ' || p_target_fp_options_id ||
1696 ' and target pref code is ALL';
1697 pa_debug.write('Get_FP_Options: ' || l_module_name,pa_debug.g_err_stage,3);
1698 END IF;
1699
1700 SELECT all_amount_set_id
1701 INTO l_target_all_amount_set_id
1702 FROM pa_proj_fp_options
1703 WHERE proj_fp_options_id = p_target_fp_options_id;
1704
1705 /* We need to use the ALL amount set id only if the target preference code is ALL.
1706 l_target_fin_plan_pref doesnt indicate the preference code of the target option,
1707 but the preference code that the target option should be updated with. So,
1708 the target flags which are intialized as Null are reset only if the target option
1709 before updation is having ALL preference code. We are checking the prefrence code
1710 of the target option as it is before updation by checking the nullablility of the
1711 all_amount_set_id column */
1712
1713 IF l_target_all_amount_set_id IS NOT NULL THEN
1714
1715 SELECT raw_cost_flag
1716 , burdened_cost_flag
1717 , revenue_flag
1718 , bill_rate_flag
1719 , cost_rate_flag
1720 , burden_rate_flag
1721 INTO l_target_raw_cost_flag
1722 , l_target_burdened_cost_flag
1723 , l_target_revenue_flag
1724 , l_target_bill_rate_flag
1725 , l_target_cost_rate_flag
1726 , l_target_burd_rate_flag
1727 FROM pa_fin_plan_amount_sets
1728 WHERE fin_plan_amount_set_id = l_target_all_amount_set_id ;
1729 END IF;
1730
1731 END IF;
1732
1733 SELECT cost_amount_set_id
1734 ,revenue_amount_set_id
1735 ,all_amount_set_id
1736 INTO l_cost_amount_set_id
1737 ,l_revenue_amount_set_id
1738 ,l_all_amount_set_id
1739 FROM pa_proj_fp_options
1740 WHERE proj_fp_options_id = p_proj_fp_options_id;
1741
1742
1743 IF l_cost_amount_set_id IS NOT NULL THEN
1744 SELECT raw_cost_flag
1745 , burdened_cost_flag
1746 , cost_qty_flag
1747 , nvl(l_target_revenue_flag,l_revenue_flag) /* Bug 3144283 */
1748 -- bug 3505736 , nvl(l_target_bill_rate_flag,bill_rate_flag) -- FP M Dev effort
1749 -- bug 3505736 , nvl(l_target_cost_rate_flag,cost_rate_flag) -- FP M Dev effort
1750 -- bug 3505736 , nvl(l_target_burd_rate_flag,burden_rate_flag) -- FP M Dev effort
1751 , nvl(l_target_bill_rate_flag,l_bill_rate_flag) -- bug 3505736
1752 , cost_rate_flag -- bug 3505736
1753 , burden_rate_flag -- bug 3505736
1754 INTO l_raw_cost_flag
1755 , l_burdened_cost_flag
1756 , l_cost_qty_flag
1757 , l_revenue_flag
1758 , l_bill_rate_flag -- FP M Dev effort
1759 , l_cost_rate_flag -- FP M Dev effort
1760 , l_burden_rate_flag -- FP M Dev effort
1761 FROM pa_fin_plan_amount_sets
1762 WHERE fin_plan_amount_set_id = l_cost_amount_set_id ;
1763 END IF;
1764
1765 IF l_revenue_amount_set_id IS NOT NULL THEN
1766 SELECT revenue_flag
1767 , revenue_qty_flag
1768 , nvl(l_target_raw_cost_flag,l_raw_cost_flag) /* Bug 3144283 */
1769 , nvl(l_target_burdened_cost_flag,l_burdened_cost_flag) /* Bug 3144283 */
1770 -- bug 3505736 , nvl(l_target_bill_rate_flag,bill_rate_flag) -- FP M Dev effort
1771 -- bug 3505736 , nvl(l_target_cost_rate_flag,cost_rate_flag) -- FP M Dev effort
1772 -- bug 3505736 , nvl(l_target_burd_rate_flag,burden_rate_flag) -- FP M Dev effort
1773 , bill_rate_flag -- bug 3505736
1774 , nvl(l_target_cost_rate_flag,l_cost_rate_flag) -- bug 3505736
1775 , nvl(l_target_burd_rate_flag,l_burden_rate_flag) -- bug 3505736
1776 INTO l_revenue_flag
1777 , l_revenue_qty_flag
1778 , l_raw_cost_flag
1779 , l_burdened_cost_flag
1780 , l_bill_rate_flag -- FP M Dev effort
1781 , l_cost_rate_flag -- FP M Dev effort
1782 , l_burden_rate_flag -- FP M Dev effort
1783 FROM pa_fin_plan_amount_sets
1784 WHERE fin_plan_amount_set_id = l_revenue_amount_set_id ;
1785 END IF;
1786
1787 IF l_all_amount_set_id IS NOT NULL THEN
1788 SELECT raw_cost_flag
1789 , burdened_cost_flag
1790 , all_qty_flag
1791 , revenue_flag
1792 -- bug 3505736 , nvl(l_target_bill_rate_flag,bill_rate_flag) -- FP M Dev effort
1793 -- bug 3505736 , nvl(l_target_cost_rate_flag,cost_rate_flag) -- FP M Dev effort
1794 -- bug 3505736 , nvl(l_target_burd_rate_flag,burden_rate_flag) -- FP M Dev effort
1795 ,bill_rate_flag -- bug 3505736
1796 ,cost_rate_flag -- bug 3505736
1797 ,burden_rate_flag -- bug 3505736
1798 INTO l_raw_cost_flag
1799 , l_burdened_cost_flag
1800 , l_all_qty_flag
1801 , l_revenue_flag
1802 , l_bill_rate_flag -- FP M Dev effort
1803 , l_cost_rate_flag -- FP M Dev effort
1804 , l_burden_rate_flag -- FP M Dev effort
1805 FROM pa_fin_plan_amount_sets
1806 WHERE fin_plan_amount_set_id = l_all_amount_set_id ;
1807 END IF;
1808
1809 /* reset all the amount set ids as their role is over */
1810 l_cost_amount_set_id := null;
1811 l_revenue_amount_set_id := null;
1812 l_all_amount_set_id := null;
1813
1814 IF ( l_target_fin_plan_pref = PA_FP_CONSTANTS_PKG.G_PREF_COST_AND_REV_SEP) THEN
1815
1816 /* If the FP Preference Code is Cost and Revenue Separately, then the
1817 COST and REVENUE columns have to be returned for all the valid combinations
1818 of Source and Target Fin_Plan_Preference_Code.
1819 manokuma: This can happen only in case of copy project when source and
1820 target both will be sep. Hence no change required here
1821 */
1822
1823 IF P_PA_DEBUG_MODE = 'Y' THEN
1824 pa_debug.g_err_stage := 'Target Fin Plan Pref Code is Cost and Revenue separately.';
1825 pa_debug.write('Get_FP_Options: ' || l_module_name,pa_debug.g_err_stage,3);
1826 END IF;
1827
1828 -- Bug 3362316, 08-JAN-2003: Added New FP.M Columns --------------------------
1829
1830 SELECT fin_plan_start_date
1831 ,fin_plan_end_date
1832 ,cost_amount_set_id
1833 ,revenue_amount_set_id
1834 ,NULL all_amount_set_id
1835 ,cost_fin_plan_level_code
1836 ,cost_time_phased_code
1837 ,cost_resource_list_id
1838 ,revenue_fin_plan_level_code
1839 ,revenue_time_phased_code
1840 ,revenue_resource_list_id
1841 ,NULL all_fin_plan_level_code
1842 ,NULL all_time_phased_code
1843 ,NULL all_resource_list_id
1844 ,nvl(report_labor_hrs_from_code,PA_FP_CONSTANTS_PKG.G_MARGIN_DERIVED_FROM_CODE_B) report_labor_hrs_from_code
1845 ,plan_in_multi_curr_flag
1846 ,factor_by_code
1847 ,default_amount_type_code
1848 ,default_amount_subtype_code
1849 ,margin_derived_from_code
1850 /* Bug 2920954 start of new record parameters for post fp-k oneoff patch */
1851 ,select_cost_res_auto_flag
1852 ,NULL cost_res_planning_level
1853 ,select_rev_res_auto_flag
1854 ,NULL revenue_res_planning_level
1855 ,NULL select_all_res_auto_flag
1856 ,NULL all_res_planning_level
1857 /* Bug 2920954 end of new record parameters for post fp-k oneoff patch */
1858 ,use_planning_rates_flag
1859 ,rbs_version_id
1860 ,res_class_raw_cost_sch_id
1861 ,res_class_bill_rate_sch_id
1862 ,cost_emp_rate_sch_id
1863 ,cost_job_rate_sch_id
1864 ,cost_non_labor_res_rate_sch_id
1865 ,cost_res_class_rate_sch_id
1866 ,cost_burden_rate_sch_id
1867 ,cost_current_planning_period
1868 ,cost_period_mask_id
1869 ,rev_emp_rate_sch_id
1870 ,rev_job_rate_sch_id
1871 ,rev_non_labor_res_rate_sch_id
1872 ,rev_res_class_rate_sch_id
1873 ,rev_current_planning_period
1874 ,rev_period_mask_id
1875 /*** Bug 3580727
1876 ,NULL all_emp_rate_sch_id
1877 ,NULL all_job_rate_sch_id
1878 ,NULL all_non_labor_res_rate_sch_id
1879 ,NULL all_res_class_rate_sch_id
1880 ,NULL all_burden_rate_sch_id
1881 ***/
1882 ,NULL all_current_planning_period
1883 ,NULL all_period_mask_id
1884 ,gen_cost_src_code
1885 ,gen_cost_etc_src_code
1886 ,gen_cost_incl_change_doc_flag
1887 ,gen_cost_incl_open_comm_flag
1888 ,gen_cost_ret_manual_line_flag
1889 ,gen_cost_incl_unspent_amt_flag
1890 ,gen_rev_src_code
1891 ,gen_rev_etc_src_code
1892 ,gen_rev_incl_change_doc_flag
1893 ,gen_rev_incl_bill_event_flag
1894 ,gen_rev_ret_manual_line_flag
1895 /*** Bug 3580727
1896 ,gen_rev_incl_unspent_amt_flag
1897 ***/
1898 ,gen_src_cost_plan_type_id
1899 ,gen_src_cost_plan_version_id
1900 ,gen_src_cost_plan_ver_code
1901 ,gen_src_rev_plan_type_id
1902 ,gen_src_rev_plan_version_id
1903 ,gen_src_rev_plan_ver_code
1904 ,NULL gen_src_all_plan_type_id
1905 ,NULL gen_src_all_plan_version_id
1906 ,NULL gen_src_all_plan_ver_code
1907 ,NULL gen_all_src_code
1908 ,NULL gen_all_etc_src_code
1909 ,NULL gen_all_incl_change_doc_flag
1910 ,NULL gen_all_incl_open_comm_flag
1911 ,NULL gen_all_ret_manual_line_flag
1912 ,NULL gen_all_incl_bill_event_flag
1913 ,NULL gen_all_incl_unspent_amt_flag
1914 ,gen_cost_actual_amts_thru_code
1915 ,gen_rev_actual_amts_thru_code
1916 ,NULL gen_all_actual_amts_thru_code
1917 ,track_workplan_costs_flag
1918 -- start of FP M dev phase II changes
1919 ,gen_src_cost_wp_version_id
1920 ,gen_src_cost_wp_ver_code
1921 ,gen_src_rev_wp_version_id
1922 ,gen_src_rev_wp_ver_code
1923 ,NULL gen_src_all_wp_version_id
1924 ,NULL gen_src_all_wp_ver_code
1925 -- end of FP M dev phase II changes
1926 -- Added for ms-excel options in webadi
1927 ,decode(p_target_fp_option_level_code,PA_FP_CONSTANTS_PKG.G_OPTION_LEVEL_PLAN_TYPE,cost_layout_code,null)
1928 ,decode(p_target_fp_option_level_code,PA_FP_CONSTANTS_PKG.G_OPTION_LEVEL_PLAN_TYPE,revenue_layout_code,null)
1929 ,NULL all_layout_code
1930 ,revenue_derivation_method -- Bug 5462471
1931 ,copy_etc_from_plan_flag -- bug#8318932
1932 ,default_raw_cost --CBS
1933 ,default_bill_rate --CBS
1934 ,def_markup_percentage --CBS
1935 ,def_raw_cost_currency_code --CBS
1936 ,def_bill_rate_currency_code --CBS
1937 INTO x_fp_cols_rec
1938 FROM pa_proj_fp_options
1939 WHERE proj_fp_options_id = p_proj_fp_options_id;
1940
1941 -- END: Bug 3362316, 08-JAN-2003: Added New FP.M Columns --------------------------
1942
1943 ELSIF (l_target_fin_plan_pref = PA_FP_CONSTANTS_PKG.G_PREF_COST_ONLY) THEN
1944
1945 /* If the FP Preference Code is COST_ONLY, only the Cost Columns have to be sent
1946 and the other columns have to be returned as NULL. */
1947
1948 /* Source Target Action
1949 ---------------------------------------------------------------------------
1950 COST_AND_REV_SAME COST_ONLY Copy "all" columns into "cost" columns.
1951 COST_ONLY COST_ONLY Copy "cost" to "cost"
1952 COST_AND_REV_SEP COST_ONLY Copy "cost" to "cost"
1953 */
1954
1955 IF P_PA_DEBUG_MODE = 'Y' THEN
1956 pa_debug.g_err_stage := 'calling PA_FIN_PLAN_UTILS.GET_OR_CREATE_AMOUNT_SET_ID.';
1957 pa_debug.write('Get_FP_Options: ' || l_module_name,pa_debug.g_err_stage,3);
1958 END IF;
1959
1960 PA_FIN_PLAN_UTILS.GET_OR_CREATE_AMOUNT_SET_ID
1961 (
1962 p_raw_cost_flag => l_raw_cost_flag
1963 ,p_burdened_cost_flag => l_burdened_cost_flag
1964 ,p_revenue_flag => 'N'
1965 ,p_cost_qty_flag => nvl(l_cost_qty_flag,l_all_qty_flag)
1966 ,p_revenue_qty_flag => 'N'
1967 ,p_all_qty_flag => 'N'
1968 ,p_bill_rate_flag => 'N'
1969 ,p_cost_rate_flag => l_cost_rate_flag
1970 ,p_burden_rate_flag => l_burden_rate_flag
1971 ,p_plan_pref_code => l_target_fin_plan_pref
1972 ,x_cost_amount_set_id => l_cost_amount_set_id
1973 ,x_revenue_amount_set_id => l_revenue_amount_set_id
1974 ,x_all_amount_set_id => l_all_amount_set_id
1975 ,x_message_count => l_msg_count
1976 ,x_return_status => x_return_status
1977 ,x_message_data => l_msg_data);
1978
1979 --added for bug 2708782
1980 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1981 raise PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
1982 END IF;
1983
1984 IF P_PA_DEBUG_MODE = 'Y' THEN
1985 pa_debug.g_err_stage := 'Target Fin Plan Pref Code is Cost Only.';
1986 pa_debug.write('Get_FP_Options: ' || l_module_name,pa_debug.g_err_stage,3);
1987 END IF;
1988
1989
1990 -- Bug 3362316, 08-JAN-2003: Added New FP.M Columns --------------------------
1991
1992 SELECT fin_plan_start_date fin_plan_start_date
1993 ,fin_plan_end_date fin_plan_end_date
1994 ,l_cost_amount_set_id cost_amount_set_id
1995 ,NULL revenue_amount_set_id
1996 ,NULL all_amount_set_id
1997 ,nvl(cost_fin_plan_level_code, all_fin_plan_level_code) cost_fin_plan_level_code
1998 ,nvl(cost_time_phased_code, all_time_phased_code) cost_time_phased_code
1999 ,nvl(cost_resource_list_id, all_resource_list_id) cost_resource_list_id
2000 ,NULL revenue_fin_plan_level_code
2001 ,NULL revenue_time_phased_code
2002 ,NULL revenue_resource_list_id
2003 ,NULL all_fin_plan_level_code
2004 ,NULL all_time_phased_code
2005 ,NULL all_resource_list_id
2006 ,NULL report_labor_hrs_from_code
2007 ,plan_in_multi_curr_flag plan_in_multi_curr_flag
2008 ,factor_by_code factor_by_code
2009 ,default_amount_type_code default_amount_type_code
2010 ,default_amount_subtype_code default_amount_subtype_code
2011 ,nvl(margin_derived_from_code,PA_FP_CONSTANTS_PKG.G_MARGIN_DERIVED_FROM_CODE_B) margin_derived_from_code
2012 /* Bug 2920954 start of new record parameters for post fp-k oneoff patch */
2013 ,nvl(select_cost_res_auto_flag, select_all_res_auto_flag) select_cost_res_auto_flag
2014 ,NULL cost_res_planning_level
2015 ,NULL select_rev_res_auto_flag
2016 ,NULL revenue_res_planning_level
2017 ,NULL select_all_res_auto_flag
2018 ,NULL all_res_planning_level
2019 /* Bug 2920954 end of new record parameters for post fp-k oneoff patch */
2020 ,use_planning_rates_flag
2021 ,rbs_version_id
2022 ,res_class_raw_cost_sch_id
2023 ,res_class_bill_rate_sch_id
2024 /*** Bug 3580727
2025 ,nvl(cost_emp_rate_sch_id, all_emp_rate_sch_id) cost_emp_rate_sch_id
2026 ,nvl(cost_job_rate_sch_id, all_job_rate_sch_id) cost_job_rate_sch_id
2027 ,nvl(cost_non_labor_res_rate_sch_id,all_non_labor_res_rate_sch_id) cost_non_labor_res_rate_sch_id
2028 ,nvl(cost_res_class_rate_sch_id, all_res_class_rate_sch_id) cost_res_class_rate_sch_id
2029 ,nvl(cost_burden_rate_sch_id, all_burden_rate_sch_id) cost_burden_rate_sch_id
2030 ***/
2031 ,cost_emp_rate_sch_id
2032 ,cost_job_rate_sch_id
2033 ,cost_non_labor_res_rate_sch_id
2034 ,cost_res_class_rate_sch_id
2035 ,cost_burden_rate_sch_id
2036 ,nvl(cost_current_planning_period, all_current_planning_period) cost_current_planning_period
2037 ,nvl(cost_period_mask_id, all_period_mask_id) cost_period_mask_id
2038 ,NULL rev_emp_rate_sch_id
2039 ,NULL rev_job_rate_sch_id
2040 ,NULL rev_non_labor_res_rate_sch_id
2041 ,NULL rev_res_class_rate_sch_id
2042 ,NULL rev_current_planning_period
2043 ,NULL rev_period_mask_id
2044 /*** Bug 3580727
2045 ,NULL all_emp_rate_sch_id
2046 ,NULL all_job_rate_sch_id
2047 ,NULL all_non_labor_res_rate_sch_id
2048 ,NULL all_res_class_rate_sch_id
2049 ,NULL all_burden_rate_sch_id
2050 ***/
2051 ,NULL all_current_planning_period
2052 ,NULL all_period_mask_id
2053 ,nvl(gen_cost_src_code, gen_all_src_code) gen_cost_src_code
2054 ,nvl(gen_cost_etc_src_code, gen_all_etc_src_code) gen_cost_etc_src_code
2055 ,nvl(gen_cost_incl_change_doc_flag, gen_all_incl_change_doc_flag) gen_cost_incl_change_doc_flag
2056 ,nvl(gen_cost_incl_open_comm_flag, gen_all_incl_open_comm_flag) gen_cost_incl_open_comm_flag
2057 ,nvl(gen_cost_ret_manual_line_flag, gen_all_ret_manual_line_flag) gen_cost_ret_manual_line_flag
2058 ,nvl(gen_cost_incl_unspent_amt_flag,gen_all_incl_unspent_amt_flag) gen_cost_incl_unspent_amt_flag
2059 ,NULL gen_rev_src_code
2060 ,NULL gen_rev_etc_src_code
2061 ,NULL gen_rev_incl_change_doc_flag
2062 ,NULL gen_rev_incl_bill_event_flag
2063 ,NULL gen_rev_ret_manual_line_flag
2064 /*** Bug 3580727
2065 ,NULL gen_rev_incl_unspent_amt_flag
2066 ***/
2067 ,nvl(gen_src_cost_plan_type_id, gen_src_all_plan_type_id) gen_src_cost_plan_type_id
2068 ,nvl(gen_src_cost_plan_version_id, gen_src_all_plan_version_id) gen_src_cost_plan_version_id
2069 ,nvl(gen_src_cost_plan_ver_code, gen_src_all_plan_ver_code) gen_src_cost_plan_ver_code
2070 ,NULL gen_src_rev_plan_type_id
2071 ,NULL gen_src_rev_plan_version_id
2072 ,NULL gen_src_rev_plan_ver_code
2073 ,NULL gen_src_all_plan_type_id
2074 ,NULL gen_src_all_plan_version_id
2075 ,NULL gen_src_all_plan_ver_code
2076 ,NULL gen_all_src_code
2077 ,NULL gen_all_etc_src_code
2078 ,NULL gen_all_incl_change_doc_flag
2079 ,NULL gen_all_incl_open_comm_flag
2080 ,NULL gen_all_ret_manual_line_flag
2081 ,NULL gen_all_incl_bill_event_flag
2082 ,NULL gen_all_incl_unspent_amt_flag
2083 ,nvl(gen_cost_actual_amts_thru_code, gen_all_actual_amts_thru_code) gen_cost_actual_amts_thru_code
2084 ,NULL gen_rev_actual_amts_thru_code
2085 ,NULL gen_all_actual_amts_thru_code
2086 ,track_workplan_costs_flag
2087 -- start of FP M dev phase II changes
2088 ,nvl(gen_src_cost_wp_version_id, gen_src_all_wp_version_id) gen_src_cost_wp_version_id
2089 ,nvl(gen_src_cost_wp_ver_code, gen_src_all_wp_ver_code) gen_src_cost_wp_ver_code
2090 ,NULL gen_src_rev_wp_version_id
2091 ,NULL gen_src_rev_wp_ver_code
2092 ,NULL gen_src_all_wp_version_id
2093 ,NULL gen_src_all_wp_ver_code
2094 -- end of FP M dev phase II changes
2095 -- Added for ms-excel options in webadi
2096 ,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
2097 ,NULL revenue_layout_code
2098 ,NULL all_layout_code
2099 ,NULL -- Bug 5462471 For cost only version revenue_derivation_method should be null always
2100 ,copy_etc_from_plan_flag --bug#8318932
2101 ,default_raw_cost --CBS
2102 ,default_bill_rate --CBS
2103 ,def_markup_percentage --CBS
2104 ,def_raw_cost_currency_code --CBS
2105 ,def_bill_rate_currency_code --CBS
2106 INTO x_fp_cols_rec
2107 FROM pa_proj_fp_options
2108 WHERE proj_fp_options_id = p_proj_fp_options_id;
2109
2110
2111
2112
2113 -- END: Bug 3362316, 08-JAN-2003: Added New FP.M Columns --------------------------
2114
2115 ELSIF (l_target_fin_plan_pref = PA_FP_CONSTANTS_PKG.G_PREF_REVENUE_ONLY) THEN
2116
2117 /* If the FP Preference Code is REVENUE_ONLY, only the Revenue Columns have to be sent
2118 and the other columns have to be returned as NULL. */
2119
2120 /* Source Target Action
2121 ---------------------------------------------------------------------------------
2122 COST_AND_REV_SAME REVENUE_ONLY Copy "all" columns into "revenue" columns.
2123 REVENUE_ONLY REVENUE_ONLY Copy "revenue" to "revenue"
2124 COST_AND_REV_SEP REVENUE_ONLY Copy "revenue" to "revenue"
2125 */
2126
2127 IF P_PA_DEBUG_MODE = 'Y' THEN
2128 pa_debug.g_err_stage := 'calling PA_FIN_PLAN_UTILS.GET_OR_CREATE_AMOUNT_SET_ID.';
2129 pa_debug.write('Get_FP_Options: ' || l_module_name,pa_debug.g_err_stage,3);
2130 END IF;
2131
2132
2133 PA_FIN_PLAN_UTILS.GET_OR_CREATE_AMOUNT_SET_ID
2134 (
2135 p_raw_cost_flag => 'N'
2136 ,p_burdened_cost_flag => 'N'
2137 ,p_revenue_flag => l_revenue_flag
2138 ,p_cost_qty_flag => 'N'
2139 ,p_revenue_qty_flag => nvl(l_revenue_qty_flag,l_all_qty_flag)
2140 ,p_all_qty_flag => 'N'
2141 ,p_bill_rate_flag => l_bill_rate_flag
2142 ,p_cost_rate_flag => 'N'
2143 ,p_burden_rate_flag => 'N'
2144 ,p_plan_pref_code => l_target_fin_plan_pref
2145 ,x_cost_amount_set_id => l_cost_amount_set_id
2146 ,x_revenue_amount_set_id => l_revenue_amount_set_id
2147 ,x_all_amount_set_id => l_all_amount_set_id
2148 ,x_message_count => l_msg_count
2149 ,x_return_status => x_return_status
2150 ,x_message_data => l_msg_data);
2151
2152 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2153 raise PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
2154 END IF;
2155
2156 -- Bug 3362316, 08-JAN-2003: Added New FP.M Columns --------------------------
2157
2158 SELECT fin_plan_start_date fin_plan_start_date
2159 ,fin_plan_end_date fin_plan_end_date
2160 ,NULL cost_amount_set_id
2161 ,l_revenue_amount_set_id revenue_amount_set_id
2162 ,NULL all_amount_set_id
2163 ,NULL cost_fin_plan_level_code
2164 ,NULL cost_time_phased_code
2165 ,NULL cost_resource_list_id
2166 ,nvl(revenue_fin_plan_level_code, all_fin_plan_level_code) revenue_fin_plan_level_code
2167 ,nvl(revenue_time_phased_code, all_time_phased_code) revenue_time_phased_code
2168 ,nvl(revenue_resource_list_id, all_resource_list_id) revenue_resource_list_id
2169 ,NULL all_fin_plan_level_code
2170 ,NULL all_time_phased_code
2171 ,NULL all_resource_list_id
2172 ,NULL report_labor_hrs_from_code
2173 ,plan_in_multi_curr_flag plan_in_multi_curr_flag
2174 ,factor_by_code factor_by_code
2175 ,default_amount_type_code default_amount_type_code
2176 ,default_amount_subtype_code default_amount_subtype_code
2177 ,null margin_derived_from_code
2178 /* Bug 2920954 start of new record parameters for post fp-k oneoff patch */
2179 ,NULL select_cost_res_auto_flag
2180 ,NULL cost_res_planning_level
2181 ,nvl(select_rev_res_auto_flag, select_all_res_auto_flag) select_rev_res_auto_flag
2182 ,NULL revenue_res_planning_level
2183 ,NULL select_all_res_auto_flag
2184 ,NULL all_res_planning_level
2185 /* Bug 2920954 end of new record parameters for post fp-k oneoff patch */
2186 ,use_planning_rates_flag
2187 ,rbs_version_id
2188 ,res_class_raw_cost_sch_id
2189 ,res_class_bill_rate_sch_id
2190 ,NULL cost_emp_rate_sch_id
2191 ,NULL cost_job_rate_sch_id
2192 ,NULL cost_non_labor_res_rate_sch_id
2193 ,NULL cost_res_class_rate_sch_id
2194 ,NULL cost_burden_rate_sch_id
2195 ,NULL cost_current_planning_period
2196 ,NULL cost_period_mask_id
2197 /*** Bug 3580727
2198 ,nvl(rev_emp_rate_sch_id, all_emp_rate_sch_id) rev_emp_rate_sch_id
2199 ,nvl(rev_job_rate_sch_id, all_job_rate_sch_id) rev_job_rate_sch_id
2200 ,nvl(rev_non_labor_res_rate_sch_id, all_non_labor_res_rate_sch_id) rev_non_labor_res_rate_sch_id
2201 ,nvl(rev_res_class_rate_sch_id, all_res_class_rate_sch_id) rev_res_class_rate_sch_id
2202 ***/
2203 ,rev_emp_rate_sch_id
2204 ,rev_job_rate_sch_id
2205 ,rev_non_labor_res_rate_sch_id
2206 ,rev_res_class_rate_sch_id
2207 ,nvl(rev_current_planning_period, all_current_planning_period) rev_current_planning_period
2208 ,nvl(rev_period_mask_id, all_period_mask_id) rev_period_mask_id
2209 /*** Bug 3580727
2210 ,NULL all_emp_rate_sch_id
2211 ,NULL all_job_rate_sch_id
2212 ,NULL all_non_labor_res_rate_sch_id
2213 ,NULL all_res_class_rate_sch_id
2214 ,NULL all_burden_rate_sch_id
2215 ***/
2216 ,NULL all_current_planning_period
2217 ,NULL all_period_mask_id
2218 ,NULL gen_cost_src_code
2219 ,NULL gen_cost_etc_src_code
2220 ,NULL gen_cost_incl_change_doc_flag
2221 ,NULL gen_cost_incl_open_comm_flag
2222 ,NULL gen_cost_ret_manual_line_flag
2223 ,NULL gen_cost_incl_unspent_amt_flag
2224 ,nvl(gen_rev_src_code, gen_all_src_code) gen_rev_src_code
2225 ,nvl(gen_rev_etc_src_code, gen_all_etc_src_code) gen_rev_etc_src_code
2226 ,nvl(gen_rev_incl_change_doc_flag, gen_all_incl_change_doc_flag) gen_rev_incl_change_doc_flag
2227 ,nvl(gen_rev_incl_bill_event_flag, gen_all_incl_bill_event_flag) gen_rev_incl_bill_event_flag
2228 ,nvl(gen_rev_ret_manual_line_flag, gen_all_ret_manual_line_flag) gen_rev_ret_manual_line_flag
2229 /*** Bug 3580727
2230 ,nvl(gen_rev_incl_unspent_amt_flag, gen_all_incl_unspent_amt_flag) gen_rev_incl_unspent_amt_flag
2231 ***/
2232 ,NULL gen_src_cost_plan_type_id
2233 ,NULL gen_src_cost_plan_version_id
2234 ,NULL gen_src_cost_plan_ver_code
2235 ,nvl(gen_src_rev_plan_type_id, gen_src_all_plan_type_id) gen_src_rev_plan_type_id
2236 ,nvl(gen_src_rev_plan_version_id, gen_src_all_plan_version_id) gen_src_rev_plan_version_id
2237 ,nvl(gen_src_rev_plan_ver_code, gen_src_all_plan_ver_code) gen_src_rev_plan_ver_code
2238 ,NULL gen_src_all_plan_type_id
2239 ,NULL gen_src_all_plan_version_id
2240 ,NULL gen_src_all_plan_ver_code
2241 ,NULL gen_all_src_code
2242 ,NULL gen_all_etc_src_code
2243 ,NULL gen_all_incl_change_doc_flag
2244 ,NULL gen_all_incl_open_comm_flag
2245 ,NULL gen_all_ret_manual_line_flag
2246 ,NULL gen_all_incl_bill_event_flag
2247 ,NULL gen_all_incl_unspent_amt_flag
2248 ,NULL gen_cost_actual_amts_thru_code
2249 ,nvl(gen_rev_actual_amts_thru_code, gen_all_actual_amts_thru_code) gen_rev_actual_amts_thru_code
2250 ,NULL gen_all_actual_amts_thru_code
2251 ,track_workplan_costs_flag
2252 -- start of FP M dev phase II changes
2253 ,NULL gen_src_cost_wp_version_id
2254 ,NULL gen_src_cost_wp_ver_code
2255 ,nvl(gen_src_rev_wp_version_id, gen_src_all_wp_version_id) gen_src_rev_wp_version_id
2256 ,nvl(gen_src_rev_wp_ver_code, gen_src_all_wp_ver_code) gen_src_rev_wp_ver_code
2257 ,NULL gen_src_all_wp_version_id
2258 ,NULL gen_src_all_wp_ver_code
2259 -- end of FP M dev phase II changes
2260 -- Added for ms-excel options in webadi
2261 ,NULL cost_layout_code
2262 ,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
2263 ,NULL all_layout_code
2264 ,revenue_derivation_method -- Bug 5462471
2265 ,NULL copy_etc_from_plan_flag -- bug 8318932
2266 ,default_raw_cost --CBS
2267 ,default_bill_rate --CBS
2268 ,def_markup_percentage --CBS
2269 ,def_raw_cost_currency_code --CBS
2270 ,def_bill_rate_currency_code --CBS
2271 INTO x_fp_cols_rec
2272 FROM pa_proj_fp_options
2273 WHERE proj_fp_options_id = p_proj_fp_options_id;
2274
2275 -- END: Bug 3362316, 08-JAN-2003: Added New FP.M Columns --------------------------
2276
2277 ELSIF (l_target_fin_plan_pref = PA_FP_CONSTANTS_PKG.G_PREF_COST_AND_REV_SAME) THEN
2278
2279 /* If the FP Preference Code is COST_AND_REV_SAME, only the "all" Columns have to be sent
2280 and the other columns have to be returned as NULL. */
2281
2282 /* Source Target Action
2283 ---------------------------------------------------------------------------
2284 COST_ONLY COST_AND_REV_SAME Copy "cost" columns into "all" columns.
2285 REVENUE_ONLY COST_AND_REV_SAME Copy "revenue" to "all"
2286 COST_AND_REV_SAME COST_AND_REV_SAME Copy "all" to "all"
2287 COST_AND_REV_SEP COST_AND_REV_SAME Copy "cost" to "all"
2288 */
2289
2290 IF P_PA_DEBUG_MODE = 'Y' THEN
2291 pa_debug.g_err_stage := 'calling PA_FIN_PLAN_UTILS.GET_OR_CREATE_AMOUNT_SET_ID.';
2292 pa_debug.write('Get_FP_Options: ' || l_module_name,pa_debug.g_err_stage,3);
2293 END IF;
2294
2295 PA_FIN_PLAN_UTILS.GET_OR_CREATE_AMOUNT_SET_ID
2296 (
2297 p_raw_cost_flag => l_raw_cost_flag
2298 ,p_burdened_cost_flag => l_burdened_cost_flag
2299 ,p_revenue_flag => l_revenue_flag
2300 ,p_cost_qty_flag => 'N'
2301 ,p_revenue_qty_flag => 'N'
2302 ,p_all_qty_flag => nvl(l_cost_qty_flag,nvl(l_revenue_qty_flag,l_all_qty_flag))
2303 ,p_bill_rate_flag => l_bill_rate_flag
2304 ,p_cost_rate_flag => l_cost_rate_flag
2305 ,p_burden_rate_flag => l_burden_rate_flag
2306 ,p_plan_pref_code => l_target_fin_plan_pref
2307 ,x_cost_amount_set_id => l_cost_amount_set_id
2308 ,x_revenue_amount_set_id => l_revenue_amount_set_id
2309 ,x_all_amount_set_id => l_all_amount_set_id
2310 ,x_message_count => l_msg_count
2311 ,x_return_status => x_return_status
2312 ,x_message_data => l_msg_data);
2313
2314 --added for bug 2708782
2315 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2316 raise PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
2317 END IF;
2318
2319 IF P_PA_DEBUG_MODE = 'Y' THEN
2320 pa_debug.g_err_stage := 'Target Fin Plan Pref Code is Cost and Revenue together.';
2321 pa_debug.write('Get_FP_Options: ' || l_module_name,pa_debug.g_err_stage,3);
2322 END IF;
2323
2324 SELECT fin_plan_start_date fin_plan_start_date
2325 ,fin_plan_end_date fin_plan_end_date
2326 ,NULL cost_amount_set_id
2327 ,NULL revenue_amount_set_id
2328 ,l_all_amount_set_id all_amount_set_id
2329 ,NULL cost_fin_plan_level_code
2330 ,NULL cost_time_phased_code
2331 ,NULL cost_resource_list_id
2332 ,NULL revenue_fin_plan_level_code
2333 ,NULL revenue_time_phased_code
2334 ,NULL revenue_resource_list_id
2335 ,nvl(cost_fin_plan_level_code,nvl(revenue_fin_plan_level_code,all_fin_plan_level_code)) all_fin_plan_level_code
2336 ,nvl(cost_time_phased_code,nvl(revenue_time_phased_code,all_time_phased_code)) all_time_phased_code
2337 ,nvl(cost_resource_list_id,nvl(revenue_resource_list_id,all_resource_list_id)) all_resource_list_id
2338 ,NULL report_labor_hrs_from_code
2339 ,plan_in_multi_curr_flag plan_in_multi_curr_flag
2340 ,factor_by_code factor_by_code
2341 ,default_amount_type_code default_amount_type_code
2342 ,default_amount_subtype_code default_amount_subtype_code /* manoj */
2343 ,nvl(margin_derived_from_code,PA_FP_CONSTANTS_PKG.G_MARGIN_DERIVED_FROM_CODE_B) margin_derived_from_code
2344 /* Bug 2920954 start of new record parameters for post fp-k oneoff patch */
2345 ,NULL select_cost_res_auto_flag
2346 ,NULL cost_res_planning_level
2347 ,NULL select_rev_res_auto_flag
2348 ,NULL revenue_res_planning_level
2349 ,nvl(select_cost_res_auto_flag,nvl(select_rev_res_auto_flag ,select_all_res_auto_flag)) select_all_res_auto_flag
2350 ,NULL all_res_planning_level
2351 /* Bug 2920954 end of new record parameters for post fp-k oneoff patch */
2352 ,use_planning_rates_flag
2353 ,rbs_version_id
2354 ,res_class_raw_cost_sch_id
2355 ,res_class_bill_rate_sch_id
2356 /*** Bug 3580727
2357 ,NULL cost_emp_rate_sch_id
2358 ,NULL cost_job_rate_sch_id
2359 ,NULL cost_non_labor_res_rate_sch_id
2360 ,NULL cost_res_class_rate_sch_id
2361 ,NULL cost_burden_rate_sch_id
2362 ***/
2363 ,cost_emp_rate_sch_id
2364 ,cost_job_rate_sch_id
2365 ,cost_non_labor_res_rate_sch_id
2366 ,cost_res_class_rate_sch_id
2367 ,cost_burden_rate_sch_id
2368 ,NULL cost_current_planning_period
2369 ,NULL cost_period_mask_id
2370 /*** Bug 3580727
2371 ,NULL rev_emp_rate_sch_id
2372 ,NULL rev_job_rate_sch_id
2373 ,NULL rev_non_labor_res_rate_sch_id
2374 ,NULL rev_res_class_rate_sch_id
2375 ***/
2376 ,rev_emp_rate_sch_id
2377 ,rev_job_rate_sch_id
2378 ,rev_non_labor_res_rate_sch_id
2379 ,rev_res_class_rate_sch_id
2380 ,NULL rev_current_planning_period
2381 ,NULL rev_period_mask_id
2382 /*** Bug 3580727
2383 ,nvl(cost_emp_rate_sch_id,nvl(rev_emp_rate_sch_id, all_emp_rate_sch_id)) all_emp_rate_sch_id
2384 ,nvl(cost_job_rate_sch_id,nvl(rev_job_rate_sch_id, all_job_rate_sch_id)) all_job_rate_sch_id
2385 ,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
2386 ,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
2387 ,nvl(cost_burden_rate_sch_id, all_burden_rate_sch_id) all_burden_rate_sch_id
2388 ***/
2389 ,nvl(cost_current_planning_period, nvl(rev_current_planning_period, all_current_planning_period)) all_current_planning_period
2390 ,nvl(cost_period_mask_id, nvl(rev_period_mask_id, all_period_mask_id)) all_period_mask_id
2391 ,NULL gen_cost_src_code
2392 ,NULL gen_cost_etc_src_code
2393 ,NULL gen_cost_incl_change_doc_flag
2394 ,NULL gen_cost_incl_open_comm_flag
2395 ,NULL gen_cost_ret_manual_line_flag
2396 ,NULL gen_cost_incl_unspent_amt_flag
2397 ,NULL gen_rev_src_code
2398 ,NULL gen_rev_etc_src_code
2399 ,NULL gen_rev_incl_change_doc_flag
2400 ,NULL gen_rev_incl_bill_event_flag
2401 ,NULL gen_rev_ret_manual_line_flag
2402 /*** Bug 3580727
2403 ,NULL gen_rev_incl_unspent_amt_flag
2404 ***/
2405 ,NULL gen_src_cost_plan_type_id
2406 ,NULL gen_src_cost_plan_version_id
2407 ,NULL gen_src_cost_plan_ver_code
2408 ,NULL gen_src_rev_plan_type_id
2409 ,NULL gen_src_rev_plan_version_id
2410 ,NULL gen_src_rev_plan_ver_code
2411 ,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
2412 ,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
2413 ,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
2414 ,nvl(gen_cost_src_code, nvl(gen_rev_src_code,gen_all_src_code)) gen_all_src_code
2415 ,nvl(gen_cost_etc_src_code, nvl(gen_rev_etc_src_code, gen_all_etc_src_code)) gen_all_etc_src_code
2416 ,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
2417 ,nvl(gen_cost_incl_open_comm_flag, gen_all_incl_open_comm_flag) gen_all_incl_open_comm_flag
2418 ,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
2419 ,nvl(gen_rev_incl_bill_event_flag, gen_all_incl_bill_event_flag) gen_all_incl_bill_event_flag
2420 /*** Bug 3580727
2421 ,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
2422 ***/
2423 ,nvl(gen_cost_incl_unspent_amt_flag, gen_all_incl_unspent_amt_flag) gen_all_incl_unspent_amt_flag
2424 ,NULL gen_cost_actual_amts_thru_code
2425 ,NULL gen_rev_actual_amts_thru_code
2426 ,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
2427 ,track_workplan_costs_flag
2428 -- start of FP M dev phase II changes
2429 ,NULL gen_src_cost_wp_version_id
2430 ,NULL gen_src_cost_wp_ver_code
2431 ,NULL gen_src_rev_wp_version_id
2432 ,NULL gen_src_rev_wp_ver_code
2433 ,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
2434 ,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
2435 -- end of FP M dev phase II changes
2436 -- Added for ms-excel options in webadi
2437 ,NULL cost_layout_code
2438 ,NULL revenue_layout_code
2439 ,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
2440 ,revenue_derivation_method -- Bug 5462471
2441 ,NULL copy_etc_from_plan_flag--bug#8318932
2442 ,default_raw_cost --CBS
2443 ,default_bill_rate --CBS
2444 ,def_markup_percentage --CBS
2445 ,def_raw_cost_currency_code --CBS
2446 ,def_bill_rate_currency_code --CBS
2447 INTO x_fp_cols_rec
2448 FROM pa_proj_fp_options
2449 WHERE proj_fp_options_id = p_proj_fp_options_id;
2450
2451 END IF;
2452
2453 IF P_PA_DEBUG_MODE = 'Y' THEN
2454 pa_debug.g_err_stage := 'End of Get_FP_Options';
2455 pa_debug.write('Get_FP_Options: ' || l_module_name,pa_debug.g_err_stage,3);
2456 pa_debug.reset_err_stack;
2457 END IF;
2458
2459 EXCEPTION
2460 WHEN PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc THEN
2461 l_msg_count := FND_MSG_PUB.count_msg;
2462 IF l_msg_count = 1 THEN
2463 PA_INTERFACE_UTILS_PUB.get_messages
2464 (p_encoded => FND_API.G_TRUE,
2465 p_msg_index => 1,
2466 p_msg_count => l_msg_count,
2467 p_msg_data => l_msg_data,
2468 p_data => l_data,
2469 p_msg_index_out => l_msg_index_out);
2470 x_msg_data := l_data;
2471 x_msg_count := l_msg_count;
2472 ELSE
2473 x_msg_count := l_msg_count;
2474 END IF;
2475 IF P_PA_DEBUG_MODE = 'Y' THEN
2476 pa_debug.reset_err_stack;
2477 END IF;
2478 RAISE;
2479 WHEN OTHERS THEN
2480 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2481 x_msg_count := 1;
2482 x_msg_data := SQLERRM;
2483 FND_MSG_PUB.add_exc_msg
2484 ( p_pkg_name => 'PA_PROJ_FP_OPTIONS_PUB.Get_FP_Options'
2485 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
2486 IF P_PA_DEBUG_MODE = 'Y' THEN
2487 pa_debug.write('Get_FP_Options: ' || l_module_name,SQLERRM,5);
2488 pa_debug.write('Get_FP_Options: ' || l_module_name,pa_debug.G_Err_Stack,5);
2489 pa_debug.reset_err_stack;
2490 END IF;
2491
2492 RAISE;
2493 END Get_FP_Options;
2494
2495 /*============================================================================================
2496 GET_PARENT_FP_OPTION_ID: This procedure returns the Parent FP Option ID for the parameter
2497 p_proj_fp_options_id that is passed to this procedure.
2498 -> If the option_level_code of the input proj_fp_option_id is PLAN_VERSION, then the proj fp
2499 option id of it's parent (i.e FP Option ID of the Option Level Code PLAN_TYPE is returned for
2500 the project_id and the plan_type_id of the input).
2501 -> If the option_level_code of the input proj_fp_option_id is PLAN_TYPE, then the proj fp
2502 option id of it's parent (i.e FP Option ID of the Option Level Code PROJECT is returned for
2503 the project_id of the input).
2504 ============================================================================================*/
2505 FUNCTION Get_Parent_FP_Option_ID(
2506 p_proj_fp_options_id IN NUMBER ) RETURN PA_PROJ_FP_OPTIONS.PROJ_FP_OPTIONS_ID%TYPE is
2507
2508 l_fp_option_level_code pa_proj_fp_options.FIN_PLAN_OPTION_LEVEL_CODE%TYPE;
2509 l_proj_id pa_proj_fp_options.PROJECT_ID%TYPE;
2510 l_fp_type_id pa_proj_fp_options.FIN_PLAN_TYPE_ID%TYPE;
2511 x_proj_fp_options_id pa_proj_fp_options.PROJ_FP_OPTIONS_ID%TYPE;
2512 l_debug_mode VARCHAR2(30);
2513
2514 BEGIN
2515 IF P_PA_DEBUG_MODE = 'Y' THEN
2516 pa_debug.set_err_stack('PA_PROJ_FP_OPTIONS_PUB.Get_Parent_FP_Option_ID');
2517 fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
2518 l_debug_mode := NVL(l_debug_mode, 'Y');
2519 pa_debug.set_process('PLSQL','LOG',l_debug_mode);
2520 END IF;
2521
2522 SELECT fin_plan_option_level_code, project_id, fin_plan_type_id
2523 INTO l_fp_option_level_code, l_proj_id, l_fp_type_id
2524 FROM pa_proj_fp_options
2525 WHERE proj_fp_options_id = p_proj_fp_options_id;
2526
2527 /* To get the Parent Option of a PLAN_VERSION, PLAN_TYPE option for the
2528 Project and Plan Type has to be selected. */
2529
2530 IF (l_fp_option_level_code = PA_FP_CONSTANTS_PKG.G_OPTION_LEVEL_PLAN_VERSION) THEN
2531
2532 IF P_PA_DEBUG_MODE = 'Y' THEN
2533 pa_debug.g_err_stage := 'Option Level Code is PLAN_VERSION.';
2534 pa_debug.write('Get_Parent_FP_Option_ID: ' || l_module_name,pa_debug.g_err_stage,3);
2535 END IF;
2536
2537 SELECT proj_fp_options_id
2538 INTO x_proj_fp_options_id
2539 FROM pa_proj_fp_options
2540 WHERE fin_plan_option_level_code = PA_FP_CONSTANTS_PKG.G_OPTION_LEVEL_PLAN_TYPE
2541 AND project_id = l_proj_id
2542 AND fin_plan_type_id = l_fp_type_id;
2543
2544 /* To get the Parent Option of a PLAN_TYPE, PROJECT option for the
2545 Project has to be selected. */
2546
2547 IF P_PA_DEBUG_MODE = 'Y' THEN
2548 pa_debug.g_err_stage := 'Option Level Code is PLAN_TYPE.';
2549 pa_debug.write('Get_Parent_FP_Option_ID: ' || l_module_name,pa_debug.g_err_stage,3);
2550 END IF;
2551
2552 ELSIF (l_fp_option_level_code = PA_FP_CONSTANTS_PKG.G_OPTION_LEVEL_PLAN_TYPE) THEN
2553 SELECT proj_fp_options_id
2554 INTO x_proj_fp_options_id
2555 FROM pa_proj_fp_options
2556 WHERE fin_plan_option_level_code = PA_FP_CONSTANTS_PKG.G_OPTION_LEVEL_PROJECT
2557 AND project_id = l_proj_id;
2558
2559 END IF;
2560
2561 IF P_PA_DEBUG_MODE = 'Y' THEN
2562 pa_debug.g_err_stage := 'End of Get_Parent_FP_Option_ID';
2563 pa_debug.write('Get_Parent_FP_Option_ID: ' || l_module_name,pa_debug.g_err_stage,3);
2564 pa_debug.reset_err_stack;
2565 END IF;
2566
2567 RETURN x_proj_fp_options_id;
2568
2569 EXCEPTION
2570
2571 /* If there is no parent found, then return the FP_Option_ID as NULL so that default
2572 values are created. */
2573 WHEN NO_DATA_FOUND THEN
2574 IF P_PA_DEBUG_MODE = 'Y' THEN
2575 pa_debug.g_err_stage := 'Parent not found, hence returning NULL proj_fp_option_id';
2576 pa_debug.write('Get_Parent_FP_Option_ID: ' || l_module_name,pa_debug.g_err_stage,3);
2577 END IF;
2578 RETURN NULL;
2579
2580 WHEN OTHERS THEN
2581 FND_MSG_PUB.add_exc_msg
2582 ( p_pkg_name => 'PA_PROJ_FP_OPTIONS_PUB.Get_Parent_FP_Option_ID'
2583 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
2584 IF P_PA_DEBUG_MODE = 'Y' THEN
2585 pa_debug.write('Get_Parent_FP_Option_ID: ' || l_module_name,SQLERRM,5);
2586 pa_debug.write('Get_Parent_FP_Option_ID: ' || l_module_name,pa_debug.G_Err_Stack,5);
2587 pa_debug.reset_err_stack;
2588 END IF;
2589 RAISE;
2590 END Get_Parent_FP_Option_ID;
2591
2592 /*============================================================================================
2593 GET_FP_OPTION_ID: This procedure returns the Proj FP Option ID based on the input Project_ID,
2594 Plan_Type_ID and the Plan_Version_ID.
2595 The Option_Level_Code is determined using the input parameters. The Proj FP Option ID is then
2596 got from the table PA_Proj_FP_Options by using the appropriate conditions based on the Option
2597 Level Code. (i.e. if the Option Level Code is PROJECT, only the Project_ID is checked for
2598 in the table etc.)
2599 ============================================================================================*/
2600 FUNCTION Get_FP_Option_ID(
2601 p_project_id IN NUMBER
2602 ,p_plan_type_id IN NUMBER
2603 ,p_plan_version_id IN NUMBER) RETURN PA_PROJ_FP_OPTIONS.PROJ_FP_OPTIONS_ID%TYPE is
2604
2605 l_fp_option_level_code pa_proj_fp_options.FIN_PLAN_OPTION_LEVEL_CODE%TYPE;
2606 x_proj_fp_options_id pa_proj_fp_options.PROJ_FP_OPTIONS_ID%TYPE;
2607 l_debug_mode VARCHAR2(30);
2608
2609 BEGIN
2610
2611 IF P_PA_DEBUG_MODE = 'Y' THEN
2612 pa_debug.set_err_stack('PA_PROJ_FP_OPTIONS_PUB.Get_FP_Option_ID');
2613 fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
2614 l_debug_mode := NVL(l_debug_mode, 'Y');
2615 pa_debug.set_process('PLSQL','LOG',l_debug_mode);
2616 END IF;
2617
2618 /* Depending on the input parameters, we get the Option_Level_Code of the FP Option. */
2619
2620 IF P_PA_DEBUG_MODE = 'Y' THEN
2621 pa_debug.g_err_stage := 'Getting the value of Option Level code.';
2622 pa_debug.write('Get_FP_Option_ID: ' || l_module_name,pa_debug.g_err_stage,3);
2623 END IF;
2624
2625 IF p_project_id IS NOT NULL THEN
2626 IF p_plan_type_id IS NOT NULL THEN
2627 IF p_plan_version_id IS NOT NULL THEN
2628 l_fp_option_level_code := PA_FP_CONSTANTS_PKG.G_OPTION_LEVEL_PLAN_VERSION;
2629 ELSE
2630 l_fp_option_level_code := PA_FP_CONSTANTS_PKG.G_OPTION_LEVEL_PLAN_TYPE;
2631 END IF;
2632 ELSE
2633 l_fp_option_level_code := PA_FP_CONSTANTS_PKG.G_OPTION_LEVEL_PROJECT;
2634 END IF;
2635 END IF;
2636
2637 /* Following are the Select statements to get the Proj_FP_Options_ID depending on the Option Level
2638 Code. If the Option_Level_Code is Project, then only Project_ID has to be checked for. For
2639 'Plan_Type', both Project_ID and Plan_Type_ID have to be checked and for PLAN_VERSION, all the
2640 three - Project_ID,Plan_Type_ID,Plan_Version_ID have to be checked for. */
2641
2642 IF (l_fp_option_level_code = PA_FP_CONSTANTS_PKG.G_OPTION_LEVEL_PROJECT) THEN
2643
2644 SELECT proj_fp_options_id
2645 INTO x_proj_fp_options_id
2646 FROM pa_proj_fp_options
2647 WHERE project_id = p_project_id
2648 AND fin_plan_option_level_code = PA_FP_CONSTANTS_PKG.G_OPTION_LEVEL_PROJECT;
2649
2650 ELSIF (l_fp_option_level_code = PA_FP_CONSTANTS_PKG.G_OPTION_LEVEL_PLAN_TYPE) THEN
2651
2652 SELECT proj_fp_options_id
2653 INTO x_proj_fp_options_id
2654 FROM pa_proj_fp_options
2655 WHERE project_id = p_project_id
2656 AND fin_plan_type_id = p_plan_type_id
2657 AND fin_plan_option_level_code = PA_FP_CONSTANTS_PKG.G_OPTION_LEVEL_PLAN_TYPE;
2658
2659 ELSIF (l_fp_option_level_code = PA_FP_CONSTANTS_PKG.G_OPTION_LEVEL_PLAN_VERSION) THEN
2660
2661 SELECT proj_fp_options_id
2662 INTO x_proj_fp_options_id
2663 FROM pa_proj_fp_options
2664 WHERE project_id = p_project_id
2665 AND fin_plan_type_id = p_plan_type_id
2666 AND fin_plan_version_id = p_plan_version_id
2667 AND fin_plan_option_level_code = PA_FP_CONSTANTS_PKG.G_OPTION_LEVEL_PLAN_VERSION;
2668
2669 END IF;
2670
2671 IF P_PA_DEBUG_MODE = 'Y' THEN
2672 pa_debug.g_err_stage := 'End of Get_FP_Option_ID';
2673 pa_debug.write('Get_FP_Option_ID: ' || l_module_name,pa_debug.g_err_stage,3);
2674 pa_debug.reset_err_stack;
2675 END IF;
2676 RETURN x_proj_fp_options_id;
2677
2678 EXCEPTION
2679
2680 /* If there is no parent found, then return the FP_Option_ID as NULL so that default
2681 values are created. */
2682 WHEN NO_DATA_FOUND THEN
2683 IF P_PA_DEBUG_MODE = 'Y' THEN
2684 pa_debug.g_err_stage := 'Parent not found, hence returning NULL proj_fp_option_id';
2685 pa_debug.write('Get_FP_Option_ID: ' || l_module_name,pa_debug.g_err_stage,3);
2686 END IF;
2687 RETURN NULL;
2688
2689 WHEN OTHERS THEN
2690 FND_MSG_PUB.add_exc_msg
2691 ( p_pkg_name => 'PA_PROJ_FP_OPTIONS_PUB.Get_FP_Option_ID'
2692 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
2693 IF P_PA_DEBUG_MODE = 'Y' THEN
2694 pa_debug.write('Get_FP_Option_ID: ' || l_module_name,SQLERRM,5);
2695 pa_debug.write('Get_FP_Option_ID: ' || l_module_name,pa_debug.G_Err_Stack,5);
2696 pa_debug.reset_err_stack;
2697 END IF;
2698 RAISE;
2699 END Get_FP_Option_ID;
2700
2701 /*=====================================================================================
2702 GET_DEFAULT_FP_OPTIONS: This procedure returns Default FP Option values based on the
2703 input parameter which is the Target FinPlan Preference Code.
2704 The values passed in the FP Options Columns depend on the Preference Code. Constants
2705 are being used for the default values so that it becomes so that if the default values
2706 have to be modified because of the business logic, then the change can be made at one
2707 point.
2708 Bug:- 2625872, If Project_Currency(PC) <> Projfunc Currency (PFC), then multi_curr_flag
2709 should be set to 'Y'.
2710 Bug 2920954 :- Modified the function to return Null as the value for new columns in the
2711 ouput record l_fp_cols_rec.
2712
2713
2714
2715 r11.5 FP.M Developement ----------------------------------
2716
2717 08-JAN-2004 jwhite Bug 3362316 (HQ)
2718 Extensively rewrote Get_Default_Fp_Options
2719 - All FP_COLS selects from dual.
2720 23-JAN-2004 rravipat FP M Dev effort Bug 3354518 (IDC)
2721 The api has been modified to default values for new set of
2722 columns introduced during FP M.
2723 05-MAY-2004 rravipat Bug 3572548
2724 generation source version code should be set based on the
2725 source plan type's plan class code. If BUDGET, CURRENT_BASELINED
2726 should be used else CURRENT_APPROVED should be used
2727
2728 15-OCT-2004 rravipat Bug 3934574 Oct 31st DHI enhancements
2729 1) Include Commitments checkbox should always be checked by default
2730 2) Default etc generation source for revenue options is
2731 'Financial Plan'
2732 19-Sep-2004 dbora Bug 4599508: R12 Changes. Refered pa_implemenations_all
2733 instead of pa_implementations as a part of MOAC uptake.
2734 =====================================================================================*/
2735 FUNCTION Get_Default_FP_Options(
2736 p_fin_plan_preference_code IN VARCHAR2 ,
2737 p_target_project_id IN pa_projects_all.project_id%TYPE,
2738 p_plan_type_id IN pa_proj_fp_options.fin_plan_type_id%TYPE) RETURN FP_COLS is
2739
2740 /* Declaring Constants */
2741 l_fin_plan_level_code CONSTANT pa_proj_fp_options.ALL_FIN_PLAN_LEVEL_CODE%TYPE := 'L';
2742 l_time_phased_code CONSTANT pa_proj_fp_options.ALL_TIME_PHASED_CODE%TYPE := 'N';
2743 l_factor_by_code CONSTANT pa_proj_fp_options.FACTOR_BY_CODE%TYPE := '1';
2744
2745 l_fp_cols_rec PA_PROJ_FP_OPTIONS_PUB.FP_COLS;
2746
2747 l_return_status VARCHAR2(2000);
2748 l_msg_count NUMBER := 0;
2749 l_msg_data VARCHAR2(2000);
2750 l_err_code NUMBER := 0;
2751 l_err_stack VARCHAR2(2000);
2752 l_err_stage VARCHAR2(2000);
2753 l_msg_index_out NUMBER := 0;
2754 l_data VARCHAR2(2000);
2755 l_debug_mode VARCHAR2(30);
2756
2757 l_cost_amount_set_id pa_proj_fp_options.COST_AMOUNT_SET_ID%TYPE;
2758 l_revenue_amount_set_id pa_proj_fp_options.REVENUE_AMOUNT_SET_ID%TYPE;
2759 l_all_amount_set_id pa_proj_fp_options.ALL_AMOUNT_SET_ID%TYPE;
2760 l_uncategorized_res_id pa_resource_lists_all_bg.RESOURCE_LIST_ID%TYPE;
2761
2762 -- Bug :- 2625872, changed l_multi_curr_flag from constant to variable
2763 --l_multi_curr_flag CONSTANT pa_proj_fp_options.PLAN_IN_MULTI_CURR_FLAG%TYPE := 'N';
2764 l_multi_curr_flag pa_proj_fp_options.PLAN_IN_MULTI_CURR_FLAG%TYPE := 'N';
2765 l_projfunc_currency_code pa_projects_all.projfunc_currency_code%TYPE;
2766 l_project_currency_code pa_projects_all.project_currency_code%TYPE;
2767 l_dummy_currency_code pa_projects_all.project_currency_code%TYPE;
2768
2769 /* added following local variables as part of changes due to autobaseline */
2770 l_rev_fin_plan_level_code pa_proj_fp_options.REVENUE_FIN_PLAN_LEVEL_CODE%TYPE := 'L';
2771 l_autobaseline_flag pa_projects_all.BASELINE_FUNDING_FLAG%TYPE := 'N';
2772 l_proj_level_funding pa_projects_all.PROJECT_LEVEL_FUNDING_FLAG%TYPE := 'N';
2773 l_app_rev_plan_type_flag pa_proj_fp_options.APPROVED_REV_PLAN_TYPE_FLAG%TYPE := 'N';
2774
2775 -- FP M Dev Effort Variables used for calling rate schedules util api
2776 l_emp_sch_id pa_proj_fp_options.cost_emp_rate_sch_id%TYPE;
2777 l_cost_job_sch_id pa_proj_fp_options.cost_job_rate_sch_id%TYPE; -- Bug 3619687
2778 l_revenue_job_sch_id pa_proj_fp_options.rev_job_rate_sch_id%TYPE; -- Bug 3619687
2779 l_non_labor_sch_id pa_proj_fp_options.cost_non_labor_res_rate_sch_id%TYPE;
2780 l_burd_sch_id pa_proj_fp_options.cost_burden_rate_sch_id%TYPE;
2781 l_res_class_sch_id pa_proj_fp_options.cost_res_class_rate_sch_id%TYPE;
2782
2783 --Adding the variables to get the default vaules for the seeded webadi layouts
2784 l_non_periodic_budget_layout VARCHAR2(30) := 'NPE_BUDGET';
2785 l_non_periodic_forecast_layout VARCHAR2(30) := 'NPE_FORECAST';
2786 l_webadi_profile VARCHAR(1);
2787
2788 l_revenue_derivation_method pa_proj_fp_options.revenue_derivation_method%TYPE; --Bug 5462471
2789
2790 CURSOR plan_type_info_cur (c_plan_type_id NUMBER) IS
2791 SELECT plan_class_code
2792 ,nvl(approved_cost_plan_type_flag,'N') approved_cost_plan_type_flag
2793 ,nvl(approved_rev_plan_type_flag,'N') approved_rev_plan_type_flag
2794 ,nvl(primary_cost_forecast_flag,'N') primary_cost_forecast_flag
2795 ,nvl(primary_rev_forecast_flag,'N') primary_rev_forecast_flag
2796 ,nvl(use_for_workplan_flag,'N') use_for_workplan_flag
2797 FROM pa_fin_plan_types_b
2798 WHERE fin_plan_type_id = c_plan_type_id;
2799
2800 plan_type_info_rec plan_type_info_cur%ROWTYPE;
2801
2802 CURSOR rbs_version_cur IS
2803 SELECT hdrtl.name as name,
2804 ver1.rbs_version_id as rbs_version_id,
2805 ver1.rbs_header_id as rbs_header_id
2806 FROM pa_rbs_headers_b hdrb,
2807 pa_rbs_headers_tl hdrtl,
2808 pa_rbs_versions_b ver1
2809 WHERE sysdate between hdrb.effective_from_date and nvl(hdrb.effective_to_date,sysdate)
2810 AND hdrtl.rbs_header_id = hdrb.rbs_header_id
2811 AND hdrtl.language = USERENV('LANG')
2812 AND ver1.rbs_header_id = hdrtl.rbs_header_id
2813 /*** 3711762
2814 AND ver1.version_number = (select max(version_number)
2815 from pa_rbs_versions_b ver2
2816 where ver1.rbs_header_id =
2817 ver2.rbs_header_id
2818 and ver2.status_code = 'FROZEN')
2819 3711762 ***/
2820 AND ver1.current_reporting_flag = 'Y' /*bug 3711762*/
2821 ORDER BY name asc;
2822 rbs_version_rec rbs_version_cur%ROWTYPE;
2823
2824 BEGIN
2825
2826 IF P_PA_DEBUG_MODE = 'Y' THEN
2827 pa_debug.set_err_stack('PA_PROJ_FP_OPTIONS_PUB.Get_Default_FP_Options');
2828 fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
2829 l_debug_mode := NVL(l_debug_mode, 'Y');
2830 pa_debug.set_process('PLSQL','LOG',l_debug_mode);
2831 END IF;
2832 l_return_status := FND_API.G_RET_STS_SUCCESS;
2833 -- begin: Bug 5941436: fnd_profile.value_specific('PA_FP_WEBADI_ENABLE'); has been changed with fnd_profile.value('PA_FP_WEBADI_ENABLE'); to perform less sqls and use caching and therefore to improve the performance
2834 -- Bug 6413612 : Added substr to fetch only 1 character of profile value
2835 l_webadi_profile := UPPER(SUBSTR(fnd_profile.value_specific('PA_FP_WEBADI_ENABLE'),1,1));
2836 -- end: Bug 5941436
2837 IF NVL(l_webadi_profile , 'N') <> 'Y' THEN
2838 l_non_periodic_budget_layout := NULL;
2839 l_non_periodic_forecast_layout := NULL;
2840 END IF;
2841
2842 -- FP M Dev Effort open and fetch plan type info cur into a record
2843 IF p_plan_type_id IS NOT NULL THEN
2844 OPEN plan_type_info_cur (p_plan_type_id);
2845 FETCH plan_type_info_cur INTO plan_type_info_rec;
2846 IF plan_type_info_cur%NOTFOUND THEN
2847 Raise PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
2848 END IF;
2849 CLOSE plan_type_info_cur;
2850 END IF;
2851
2852 /* Bug 3106741 pa_implementations has been moved to subquery */
2853
2854 /* Bug 4599508: R12- Getting the business group id into a local
2855 * variable corresponding to the org_id of the target project
2856 * as we need to replace pa_implementations with pa_implementaions_all
2857 * for MOAC uptake.
2858 */
2859
2860 BEGIN
2861 SELECT R1.resource_list_id
2862 INTO l_uncategorized_res_id
2863 FROM pa_resource_lists_all_bg R1,
2864 pa_implementations_all pim,
2865 pa_projects_all prj
2866 WHERE prj.project_id = p_target_project_id
2867 AND pim.org_id = prj.org_id
2868 AND R1.uncategorized_flag = 'Y'
2869 AND R1.business_group_id = pim.business_group_id;
2870 EXCEPTION
2871 WHEN NO_DATA_FOUND THEN
2872 IF P_PA_DEBUG_MODE = 'Y' THEN
2873 pa_debug.g_err_stage := 'No uncat resource list found corresponding to the org_id';
2874 pa_debug.write('Get_Default_FP_Options: ' || l_module_name,pa_debug.g_err_stage,3);
2875 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
2876 END IF;
2877 END;
2878
2879 PA_FIN_PLAN_UTILS.GET_OR_CREATE_AMOUNT_SET_ID(
2880 P_RAW_COST_FLAG => 'Y',
2881 P_BURDENED_COST_FLAG => 'Y',
2882 P_REVENUE_FLAG => 'Y',
2883 P_COST_QTY_FLAG => 'Y',
2884 P_REVENUE_QTY_FLAG => 'Y',
2885 P_ALL_QTY_FLAG => 'Y',
2886 P_BILL_RATE_FLAG => 'Y',
2887 P_COST_RATE_FLAG => 'Y',
2888 P_BURDEN_RATE_FLAG => 'Y',
2889 P_PLAN_PREF_CODE => p_fin_plan_preference_code,
2890 X_COST_AMOUNT_SET_ID => l_cost_amount_set_id,
2891 X_REVENUE_AMOUNT_SET_ID => l_revenue_amount_set_id,
2892 X_ALL_AMOUNT_SET_ID => l_all_amount_set_id,
2893 X_MESSAGE_COUNT => l_msg_count,
2894 X_RETURN_STATUS => l_return_status,
2895 X_MESSAGE_DATA => l_msg_data);
2896
2897 --added for bug 2708782
2898 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2899 raise PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
2900 END IF;
2901
2902 /* Following code is added in context of autobaseline */
2903 /* Bug#2619022 */
2904
2905 IF P_PA_DEBUG_MODE = 'Y' THEN
2906 pa_debug.g_err_stage := 'P-target_project_id : '||TO_CHAR(p_target_project_id)||' p_plan_type_id : '||TO_CHAR(p_plan_type_id);
2907 pa_debug.write('Get_Default_FP_Options: ' || l_module_name,pa_debug.g_err_stage,3);
2908 END IF;
2909
2910 IF p_target_project_id IS NOT NULL AND p_plan_type_id IS NOT NULL THEN
2911
2912 IF P_PA_DEBUG_MODE = 'Y' THEN
2913 pa_debug.g_err_stage := 'Fetching funding level for project ';
2914 pa_debug.write('Get_Default_FP_Options: ' || l_module_name,pa_debug.g_err_stage,3);
2915 END IF;
2916
2917 SELECT NVL(baseline_funding_flag,'N')
2918 ,NVL(approved_rev_plan_type_flag,'N')
2919 INTO l_autobaseline_flag
2920 ,l_app_rev_plan_type_flag
2921 FROM pa_projects_all ppa
2922 ,pa_fin_plan_types_b ptb
2923 WHERE ppa.project_id = p_target_project_id
2924 AND ptb.fin_plan_type_id = p_plan_type_id;
2925
2926 IF P_PA_DEBUG_MODE = 'Y' THEN
2927 pa_debug.g_err_stage := 'Autobaseline flag : '||l_autobaseline_flag||
2928 ' Project level funding : '||l_proj_level_funding;
2929 pa_debug.write('Get_Default_FP_Options: ' || l_module_name,pa_debug.g_err_stage,3);
2930 END IF;
2931
2932 END IF;
2933
2934 IF p_target_project_id IS NOT NULL AND p_plan_type_id IS NOT NULL AND
2935 l_autobaseline_flag = 'Y' AND l_app_rev_plan_type_flag = 'Y' THEN
2936 -- Bug 2702000.
2937 -- Moved this piece of code from the previous if to this if so that the API check_funding_level
2938 -- is called only if the project is AB enabled and the PT is AR.
2939 --Bug#2675335
2940 --Code added to get the project level funding.
2941 pa_billing_core.check_funding_level(x_project_id => p_target_project_id,
2942 x_funding_level => l_proj_level_funding,
2943 x_err_code => l_err_code,
2944 x_err_stage => l_err_stage,
2945 x_err_stack => l_err_stack);
2946
2947
2948 /* #2681045: Exception will be raised only if the error code that is being returned by
2949 the above call is SQL error and not a pre-defined one in the check_funding_level
2950 procedure. */
2951 IF (l_err_code < 0 OR l_err_code = 100) THEN
2952 IF P_PA_DEBUG_MODE = 'Y' THEN
2953 pa_debug.g_err_stage := 'Error returned by pa_billing_core.check_funding_level:Err_code:'
2954 || to_char(l_err_code) || ':Err_stage:' || l_err_stage
2955 || ':Err_stack' || l_err_stack;
2956 pa_debug.write('Get_Default_FP_Options: ' || l_module_name,pa_debug.g_err_stage,5);
2957 END IF;
2958 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
2959 END IF;
2960
2961 IF l_proj_level_funding = 'P' THEN
2962 l_rev_fin_plan_level_code := 'P';
2963 ELSE
2964 l_rev_fin_plan_level_code := 'T';
2965 END IF;
2966 ELSE
2967 l_rev_fin_plan_level_code := 'L'; /* default value */
2968 END IF;
2969
2970 IF P_PA_DEBUG_MODE = 'Y' THEN
2971 pa_debug.g_err_stage := ' l_rev_fin_plan_level_code : '||l_rev_fin_plan_level_code
2972 ||' p_fin_plan_pref_code : '||p_fin_plan_preference_code;
2973 pa_debug.write('Get_Default_FP_Options: ' || l_module_name,pa_debug.g_err_stage,3);
2974 END IF;
2975
2976 --+++ Start of changes for Bug :- 2625872 +++--
2977 -- Fetch the project and project functional currency codes of the project
2978
2979 pa_budget_utils.Get_Project_Currency_Info(
2980 p_project_id => p_target_project_id
2981 , x_projfunc_currency_code => l_projfunc_currency_code
2982 , x_project_currency_code => l_project_currency_code
2983 , x_txn_currency_code => l_dummy_currency_code
2984 , x_msg_count => l_msg_count
2985 , x_msg_data => l_msg_data
2986 , x_return_status => l_return_status);
2987
2988 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2989 pa_debug.g_err_stage:= 'Could not obtain currency info for the project';
2990 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL5);
2991 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
2992 END IF;
2993
2994 -- If the project and project func currencies aren't equal
2995 -- set the multi currency flag to 'Y'
2996
2997 IF l_projfunc_currency_code <> l_project_currency_code THEN
2998 l_multi_curr_flag := 'Y';
2999 ELSE
3000 l_multi_curr_flag := 'N';
3001 END IF;
3002 --+++ End of changes for Bug :- 2625872 +++--
3003
3004 -- Initialize all the attributes that are not preference code dependent
3005 -- Commom for both project and plan type level records
3006
3007 l_fp_cols_rec.fin_plan_start_date := null;
3008 l_fp_cols_rec.fin_plan_end_date := null;
3009 l_fp_cols_rec.plan_in_multi_curr_flag := l_multi_curr_flag; -- MC flag as derived above
3010 l_fp_cols_rec.factor_by_code := l_factor_by_code; -- value '1'
3011
3012 /*** Bug 3731925
3013 -- FP M Phase II dev changes, rbs_version_id should be defaulted to the latest frozen
3014 -- version of first rbs header when available and effective rbs headers are ordered
3015 -- by header name in ascending order
3016
3017 OPEN rbs_version_cur;
3018 FETCH rbs_version_cur INTO rbs_version_rec;
3019 IF rbs_version_cur%NOTFOUND THEN
3020 l_fp_cols_rec.rbs_version_id := null; -- as no RBS have been defined yet
3021 ELSE
3022 l_fp_cols_rec.rbs_version_id := rbs_version_rec.rbs_version_id;
3023 END IF;
3024 CLOSE rbs_version_cur;
3025 ***/
3026
3027 -- Bug 3731925 FP M IB3 changes, default value for RBS would be null
3028 l_fp_cols_rec.rbs_version_id := null;
3029
3030 -- Initialize plan settings tab related values that are preferene code
3031 -- dependent
3032
3033 IF (p_fin_plan_preference_code = PA_FP_CONSTANTS_PKG.G_PREF_COST_ONLY) THEN
3034
3035 IF P_PA_DEBUG_MODE = 'Y' THEN
3036 pa_debug.g_err_stage := 'Fin Plan Preference code is Cost Only.';
3037 pa_debug.write('Get_Default_FP_Options: ' || l_module_name,pa_debug.g_err_stage,3);
3038 END IF;
3039
3040 l_fp_cols_rec.cost_amount_set_id := l_cost_amount_set_id;
3041 l_fp_cols_rec.cost_fin_plan_level_code := l_fin_plan_level_code;
3042 l_fp_cols_rec.cost_time_phased_code := l_time_phased_code;
3043 l_fp_cols_rec.cost_resource_list_id := l_uncategorized_res_id;
3044
3045 l_fp_cols_rec.default_amount_type_code := PA_FP_CONSTANTS_PKG.G_AMOUNT_TYPE_COST;
3046 l_fp_cols_rec.default_amount_subtype_code := PA_FP_CONSTANTS_PKG.G_AMOUNT_TYPE_BURD_COST;
3047 l_fp_cols_rec.report_labor_hrs_from_code := PA_FP_CONSTANTS_PKG.G_LABOR_HRS_FROM_CODE_COST;
3048
3049 IF p_plan_type_id IS NOT NULL THEN
3050 l_fp_cols_rec.margin_derived_from_code := PA_FP_CONSTANTS_PKG.G_MARGIN_DERIVED_FROM_CODE_B;
3051 END IF;
3052
3053 ELSIF (p_fin_plan_preference_code = PA_FP_CONSTANTS_PKG.G_PREF_REVENUE_ONLY) THEN
3054
3055 IF P_PA_DEBUG_MODE = 'Y' THEN
3056 pa_debug.g_err_stage := 'Fin Plan Preference code is Revenue Only.';
3057 pa_debug.write('Get_Default_FP_Options: ' || l_module_name,pa_debug.g_err_stage,3);
3058 END IF;
3059
3060 l_fp_cols_rec.revenue_amount_set_id := l_revenue_amount_set_id;
3061 l_fp_cols_rec.revenue_fin_plan_level_code := l_rev_fin_plan_level_code;
3062 l_fp_cols_rec.revenue_time_phased_code := l_time_phased_code;
3063 l_fp_cols_rec.revenue_resource_list_id := l_uncategorized_res_id;
3064
3065 l_fp_cols_rec.default_amount_type_code := PA_FP_CONSTANTS_PKG.G_AMOUNT_TYPE_REVENUE;
3066 l_fp_cols_rec.default_amount_subtype_code := PA_FP_CONSTANTS_PKG.G_AMOUNT_TYPE_REVENUE;
3067 l_fp_cols_rec.report_labor_hrs_from_code := PA_FP_CONSTANTS_PKG.G_LABOR_HRS_FROM_CODE_REVENUE;
3068
3069 --Bug 5462471
3070 l_revenue_derivation_method := PA_FP_GEN_FCST_PG_PKG.GET_REV_GEN_METHOD(p_target_project_id);
3071
3072 if l_revenue_derivation_method = 'C' then
3073 l_fp_cols_rec.revenue_derivation_method := 'COST';
3074 elsif l_revenue_derivation_method = 'T' then
3075 l_fp_cols_rec.revenue_derivation_method := 'WORK';
3076 elsif l_revenue_derivation_method = 'E' then
3077 l_fp_cols_rec.revenue_derivation_method := 'EVENT';
3078 else
3079 l_fp_cols_rec.revenue_derivation_method := null;
3080 end if;
3081
3082
3083
3084 IF p_plan_type_id IS NOT NULL THEN
3085 l_fp_cols_rec.margin_derived_from_code := null; -- not applicable in this case
3086 END IF;
3087
3088 ELSIF (p_fin_plan_preference_code = PA_FP_CONSTANTS_PKG.G_PREF_COST_AND_REV_SAME) THEN
3089
3090 IF P_PA_DEBUG_MODE = 'Y' THEN
3091 pa_debug.g_err_stage := 'Fin Plan Preference code is Cost and Revenue together.';
3092 pa_debug.write('Get_Default_FP_Options: ' || l_module_name,pa_debug.g_err_stage,3);
3093 END IF;
3094
3095 l_fp_cols_rec.all_amount_set_id := l_all_amount_set_id;
3096 l_fp_cols_rec.all_fin_plan_level_code := l_fin_plan_level_code;
3097 l_fp_cols_rec.all_time_phased_code := l_time_phased_code;
3098 l_fp_cols_rec.all_resource_list_id := l_uncategorized_res_id;
3099
3100 l_fp_cols_rec.default_amount_type_code := PA_FP_CONSTANTS_PKG.G_AMOUNT_TYPE_COST;
3101 l_fp_cols_rec.default_amount_subtype_code := PA_FP_CONSTANTS_PKG.G_AMOUNT_TYPE_BURD_COST;
3102 l_fp_cols_rec.report_labor_hrs_from_code := PA_FP_CONSTANTS_PKG.G_LABOR_HRS_FROM_CODE_COST;
3103
3104 --Bug 5462471
3105 l_revenue_derivation_method := PA_FP_GEN_FCST_PG_PKG.GET_REV_GEN_METHOD(p_target_project_id);
3106
3107 if l_revenue_derivation_method = 'C' then
3108 l_fp_cols_rec.revenue_derivation_method := 'COST';
3109 elsif l_revenue_derivation_method = 'T' then
3110 l_fp_cols_rec.revenue_derivation_method := 'WORK';
3111 elsif l_revenue_derivation_method = 'E' then
3112 l_fp_cols_rec.revenue_derivation_method := 'EVENT';
3113 else
3114 l_fp_cols_rec.revenue_derivation_method := null;
3115 end if;
3116
3117
3118
3119 IF p_plan_type_id IS NOT NULL THEN
3120 l_fp_cols_rec.margin_derived_from_code := PA_FP_CONSTANTS_PKG.G_MARGIN_DERIVED_FROM_CODE_B;
3121 END IF;
3122
3123 ELSIF ( p_fin_plan_preference_code = PA_FP_CONSTANTS_PKG.G_PREF_COST_AND_REV_SEP) THEN
3124
3125 IF P_PA_DEBUG_MODE = 'Y' THEN
3126 pa_debug.g_err_stage := 'Fin Plan Preference code is Cost and Revenue separately.';
3127 pa_debug.write('Get_Default_FP_Options: ' || l_module_name,pa_debug.g_err_stage,3);
3128 END IF;
3129
3130 l_fp_cols_rec.cost_amount_set_id := l_cost_amount_set_id;
3131 l_fp_cols_rec.cost_fin_plan_level_code := l_fin_plan_level_code;
3132 l_fp_cols_rec.cost_time_phased_code := l_time_phased_code;
3133 l_fp_cols_rec.cost_resource_list_id := l_uncategorized_res_id;
3134
3135 l_fp_cols_rec.revenue_amount_set_id := l_revenue_amount_set_id;
3136 l_fp_cols_rec.revenue_fin_plan_level_code := l_rev_fin_plan_level_code;
3137 l_fp_cols_rec.revenue_time_phased_code := l_time_phased_code;
3138 l_fp_cols_rec.revenue_resource_list_id := l_uncategorized_res_id;
3139
3140 l_fp_cols_rec.report_labor_hrs_from_code := PA_FP_CONSTANTS_PKG.G_LABOR_HRS_FROM_CODE_COST;
3141 l_fp_cols_rec.default_amount_type_code := null; /* Open Issue */
3142 l_fp_cols_rec.default_amount_subtype_code := null; /* Open Issue */
3143
3144 --Bug 5462471
3145 l_revenue_derivation_method := PA_FP_GEN_FCST_PG_PKG.GET_REV_GEN_METHOD(p_target_project_id);
3146
3147 if l_revenue_derivation_method = 'C' then
3148 l_fp_cols_rec.revenue_derivation_method := 'COST';
3149 elsif l_revenue_derivation_method = 'T' then
3150 l_fp_cols_rec.revenue_derivation_method := 'WORK';
3151 elsif l_revenue_derivation_method = 'E' then
3152 l_fp_cols_rec.revenue_derivation_method := 'EVENT';
3153 else
3154 l_fp_cols_rec.revenue_derivation_method := null;
3155 end if;
3156
3157
3158 IF p_plan_type_id IS NOT NULL THEN
3159 l_fp_cols_rec.margin_derived_from_code := PA_FP_CONSTANTS_PKG.G_MARGIN_DERIVED_FROM_CODE_B;
3160 END IF;
3161
3162 END IF;
3163
3164 -- FP M Dev effort for workplan plan type case track_workplan_costs_flag should be set to 'N'
3165 -- In all other cases its null
3166
3167 IF (p_plan_type_id IS NOT NULL) AND (plan_type_info_rec.use_for_workplan_flag = 'Y' ) THEN
3168 l_fp_cols_rec.track_workplan_costs_flag := 'N';
3169 ELSE
3170 l_fp_cols_rec.track_workplan_costs_flag := null;
3171 END IF;
3172
3173 -- FP M Dev effort the following code takes care of populating default values for
3174 -- rate schedule related columns for plan type level option
3175
3176 -- Default use_planning_rates_flag to 'N'
3177 l_fp_cols_rec.use_planning_rates_flag := 'N';
3178
3179 -- FP M Dev effort the following code takes care of populating default values for
3180 -- amount generation amounts related columns for plan type level option
3181 -- Note: In project level case, they are not applicable and thus populate as null
3182
3183 IF (p_plan_type_id IS NOT NULL) AND (plan_type_info_rec.use_for_workplan_flag <> 'Y' ) THEN
3184
3185 -- For both cost and cost and rev sep cases, cost generation options should be defaulted
3186
3187 IF ((p_fin_plan_preference_code = PA_FP_CONSTANTS_PKG.G_PREF_COST_ONLY) OR
3188 (p_fin_plan_preference_code = PA_FP_CONSTANTS_PKG.G_PREF_COST_AND_REV_SEP) )
3189 THEN
3190
3191 -- Separate processing based on plan class code
3192 IF (plan_type_info_rec.plan_class_code = PA_FP_CONSTANTS_PKG.G_PLAN_CLASS_BUDGET)
3193 THEN
3194
3195 IF P_PA_DEBUG_MODE = 'Y' THEN
3196 pa_debug.g_err_stage := 'fetching generation columns for cost';
3197 pa_debug.write('Get_Default_FP_Options: ' || l_module_name,pa_debug.g_err_stage,3);
3198 END IF;
3199
3200 -- Estimate to completion source code should be null
3201
3202 l_fp_cols_rec.gen_cost_etc_src_code := null;
3203 l_fp_cols_rec.gen_cost_incl_unspent_amt_flag := null;
3204 l_fp_cols_rec.gen_cost_actual_amts_thru_code := null;
3205
3206 l_fp_cols_rec.gen_cost_incl_change_doc_flag := 'N';
3207 l_fp_cols_rec.gen_cost_incl_open_comm_flag := 'Y'; -- Bug 3934574 (N -> Y)
3208 l_fp_cols_rec.gen_cost_ret_manual_line_flag := 'N';
3209
3210 -- Source code should be defaulted to 'FINANCIAL_PLAN'
3211 -- Plan version code should be set to 'CURRENT_BASELINED'
3212
3213 l_fp_cols_rec.gen_cost_src_code := 'FINANCIAL_PLAN';
3214 -- Bug 3572548 l_fp_cols_rec.gen_src_cost_plan_ver_code := 'CURRENT_BASELINED';
3215 l_fp_cols_rec.gen_src_cost_plan_version_id := null;
3216 l_fp_cols_rec.gen_src_cost_wp_ver_code := null; -- FP M Phase II Dev effort
3217 l_fp_cols_rec.gen_src_cost_wp_version_id := null; -- FP M Phase II Dev effort
3218
3219 -- Population the two fields cost_layout_code in the l_fp_cols_rec for a budget.
3220 l_fp_cols_rec.cost_layout_code := l_non_periodic_budget_layout;
3221
3222 -- plan type defaulting is as follows
3223 -- 1) if approved cost plan type is added for project use it here
3224 -- 2) else
3225 -- the first plan type with pref code involving 'cost'
3226 Begin
3227 SELECT pt.fin_plan_type_id
3228 ,DECODE(pt.plan_class_code,
3229 'BUDGET','CURRENT_BASELINED',
3230 'FORECAST','CURRENT_APPROVED') -- Bug 3572548
3231 INTO l_fp_cols_rec.gen_src_cost_plan_type_id
3232 ,l_fp_cols_rec.gen_src_cost_plan_ver_code -- Bug 3572548
3233 FROM pa_proj_fp_options o
3234 ,pa_fin_plan_types_b pt
3235 WHERE o.project_id = p_target_project_id
3236 AND o.fin_plan_option_level_code = 'PLAN_TYPE'
3237 AND o.fin_plan_type_id = pt.fin_plan_type_id
3238 AND nvl(pt.use_for_workplan_flag,'N') = 'N' -- bug 3429026
3239 AND o.approved_cost_plan_type_flag = 'Y'; --bug 5107742
3240 Exception
3241 When NO_DATA_FOUND Then
3242 Begin
3243 SELECT *
3244 INTO l_fp_cols_rec.gen_src_cost_plan_type_id
3245 ,l_fp_cols_rec.gen_src_cost_plan_ver_code -- Bug 3572548
3246 FROM
3247 (SELECT pt.fin_plan_type_id
3248 ,DECODE(pt.plan_class_code,
3249 'BUDGET','CURRENT_BASELINED',
3250 'FORECAST','CURRENT_APPROVED') -- Bug 3572548
3251 FROM pa_proj_fp_options o
3252 ,pa_fin_plan_types_vl pt
3253 WHERE o.project_id = p_target_project_id
3254 AND o.fin_plan_option_level_code = 'PLAN_TYPE'
3255 AND o.fin_plan_preference_code <> 'REVENUE_ONLY'
3256 AND nvl(pt.use_for_workplan_flag,'N') = 'N' -- bug 3429026
3257 AND o.fin_plan_type_id = pt.fin_plan_type_id
3258 order by pt.name) a
3259 WHERE ROWNUM = 1;
3260 Exception
3261 When NO_DATA_FOUND then
3262 -- When there is no other plan type set plan type id to the same plan type
3263 l_fp_cols_rec.gen_src_cost_plan_type_id := p_plan_type_id; --UT
3264 l_fp_cols_rec.gen_src_cost_plan_ver_code := 'CURRENT_BASELINED'; -- Bug 3572548
3265 When others then
3266 IF P_PA_DEBUG_MODE = 'Y' THEN
3267 pa_debug.g_err_stage := 'execption while fetching default source cost plan type id when approved plan type is not available ';
3268 pa_debug.write('Get_Default_FP_Options: ' || l_module_name,pa_debug.g_err_stage,3);
3269 END IF;
3270 End;
3271 When others then
3272 IF P_PA_DEBUG_MODE = 'Y' THEN
3273 pa_debug.g_err_stage := 'execption while fetching approved default source cost plan type id ';
3274 pa_debug.write('Get_Default_FP_Options: ' || l_module_name,pa_debug.g_err_stage,3);
3275 END IF;
3276 Raise;
3277 End;
3278
3279 ELSE -- Forecast plan type
3280
3281 -- Estimate to completion source code should be 'RESOURCE_SCHEDULE'
3282
3283 l_fp_cols_rec.gen_cost_etc_src_code := 'RESOURCE_SCHEDULE';
3284 l_fp_cols_rec.gen_cost_incl_unspent_amt_flag := 'N';
3285 /*** Bug 3580542 Default value should be Current Period'
3286 --l_fp_cols_rec.gen_cost_actual_amts_thru_code := 'PRIOR_PERIOD';
3287 ***/
3288 l_fp_cols_rec.gen_cost_actual_amts_thru_code := 'CURRENT_PERIOD';
3289
3290 l_fp_cols_rec.gen_cost_incl_change_doc_flag := 'N';
3291 l_fp_cols_rec.gen_cost_incl_open_comm_flag := 'Y'; -- Bug 3934574 (N -> Y)
3292 l_fp_cols_rec.gen_cost_ret_manual_line_flag := 'N';
3293
3294 -- Source related parameters would be null
3295
3296 l_fp_cols_rec.gen_cost_src_code := null;
3297 l_fp_cols_rec.gen_src_cost_plan_type_id := null;
3298 l_fp_cols_rec.gen_src_cost_plan_ver_code := null;
3299 l_fp_cols_rec.gen_src_cost_plan_version_id := null;
3300 l_fp_cols_rec.gen_src_cost_wp_ver_code := null; -- FP M Phase II Dev effort
3301 l_fp_cols_rec.gen_src_cost_wp_version_id := null; -- FP M Phase II Dev effort
3302
3303 -- Population the two fields cost_layout_code in the l_fp_cols_rec for a forecast.
3304 l_fp_cols_rec.cost_layout_code := l_non_periodic_forecast_layout;
3305
3306 END IF; -- End of BUDGET/FORECAST
3307 END IF; -- cost or cost and rev sep case
3308
3309 IF ((p_fin_plan_preference_code = PA_FP_CONSTANTS_PKG.G_PREF_REVENUE_ONLY) OR
3310 (p_fin_plan_preference_code = PA_FP_CONSTANTS_PKG.G_PREF_COST_AND_REV_SEP) )
3311 THEN
3312
3313 -- Separate processing based on plan class code
3314 IF (plan_type_info_rec.plan_class_code = PA_FP_CONSTANTS_PKG.G_PLAN_CLASS_BUDGET)
3315 THEN
3316
3317 -- Estimate to completion source code should be null
3318
3319 l_fp_cols_rec.gen_rev_etc_src_code := null;
3320 l_fp_cols_rec.gen_rev_actual_amts_thru_code := null;
3321
3322 l_fp_cols_rec.gen_rev_incl_change_doc_flag := 'N';
3323 l_fp_cols_rec.gen_rev_incl_bill_event_flag := 'N';
3324 l_fp_cols_rec.gen_rev_ret_manual_line_flag := 'N';
3325
3326 -- Source code should be defaulted to 'FINANCIAL_PLAN'
3327 l_fp_cols_rec.gen_rev_src_code := 'FINANCIAL_PLAN';
3328 l_fp_cols_rec.gen_src_rev_plan_version_id := null;
3329 l_fp_cols_rec.gen_src_rev_wp_ver_code := null; -- FP M Phase II Dev effort
3330 l_fp_cols_rec.gen_src_rev_wp_version_id := null; -- FP M Phase II Dev effort
3331
3332 -- Population the two fields revenue_layout_code in the l_fp_cols_rec for a budget.
3333 l_fp_cols_rec.revenue_layout_code := l_non_periodic_budget_layout;
3334
3335 ELSE -- Forecast plan type
3336
3337 -- Estimate to completion source code should be 'FINANCIAL_PLAN'
3338
3339 l_fp_cols_rec.gen_rev_etc_src_code := 'FINANCIAL_PLAN';
3340 /*** Bug 3580542 Default value should be Current Period'
3341 l_fp_cols_rec.gen_rev_actual_amts_thru_code := 'PRIOR_PERIOD';
3342 ***/
3343 l_fp_cols_rec.gen_rev_actual_amts_thru_code := 'CURRENT_PERIOD';
3344
3345 l_fp_cols_rec.gen_rev_incl_change_doc_flag := 'N';
3346 l_fp_cols_rec.gen_rev_incl_bill_event_flag := 'N';
3347 l_fp_cols_rec.gen_rev_ret_manual_line_flag := 'N';
3348
3349 -- Source related parameters would be null
3350
3351 l_fp_cols_rec.gen_rev_src_code := null;
3352 l_fp_cols_rec.gen_src_rev_plan_version_id := null;
3353 l_fp_cols_rec.gen_src_rev_wp_ver_code := null; -- FP M Phase II Dev effort
3354 l_fp_cols_rec.gen_src_rev_wp_version_id := null; -- FP M Phase II Dev effort
3355
3356 -- Population the two fields revenue_layout_code in the l_fp_cols_rec for a forecast.
3357 l_fp_cols_rec.revenue_layout_code := l_non_periodic_forecast_layout;
3358
3359 END IF; -- End of BUDGET/FORECAST
3360
3361 -- plan type defaulting is as follows
3362 -- 1) if approved cost plan type is added for project use it here
3363 -- 2) else
3364 -- the first plan type with pref code involving 'cost'
3365 Begin
3366 SELECT pt.fin_plan_type_id
3367 ,DECODE(pt.plan_class_code,
3368 'BUDGET','CURRENT_BASELINED',
3369 'FORECAST','CURRENT_APPROVED') -- Bug 3572548
3370 INTO l_fp_cols_rec.gen_src_rev_plan_type_id
3371 ,l_fp_cols_rec.gen_src_rev_plan_ver_code -- Bug 3572548
3372 FROM pa_proj_fp_options o
3373 ,pa_fin_plan_types_b pt
3374 WHERE o.project_id = p_target_project_id
3375 AND o.fin_plan_option_level_code = 'PLAN_TYPE'
3376 AND o.fin_plan_type_id = pt.fin_plan_type_id
3377 AND nvl(pt.use_for_workplan_flag,'N') = 'N' -- bug 3429026
3378 AND pt.approved_cost_plan_type_flag = 'Y';
3379 --Bug 3724132 AND pt.approved_rev_plan_type_flag = 'Y';
3380 Exception
3381 When NO_DATA_FOUND Then
3382 Begin
3383 SELECT *
3384 INTO l_fp_cols_rec.gen_src_rev_plan_type_id
3385 ,l_fp_cols_rec.gen_src_rev_plan_ver_code -- Bug 3572548
3386 FROM (
3387 SELECT pt.fin_plan_type_id
3388 ,DECODE(pt.plan_class_code,
3389 'BUDGET','CURRENT_BASELINED',
3390 'FORECAST','CURRENT_APPROVED') -- Bug 3572548
3391 FROM pa_proj_fp_options o
3392 ,pa_fin_plan_types_vl pt
3393 WHERE o.project_id = p_target_project_id
3394 AND o.fin_plan_option_level_code = 'PLAN_TYPE'
3395 AND nvl(pt.use_for_workplan_flag,'N') = 'N' -- bug 3429026
3396 AND o.fin_plan_preference_code <> 'REVENUE_ONLY' -- bug 3666398 'COST_ONLY'
3397 AND o.fin_plan_type_id = pt.fin_plan_type_id
3398 order by pt.name ) a
3399 WHERE ROWNUM = 1;
3400 Exception
3401 When NO_DATA_FOUND then
3402 -- Bug 3666398 revenue-only plan type can not be generation source
3403 IF p_fin_plan_preference_code = PA_FP_CONSTANTS_PKG.G_PREF_COST_AND_REV_SEP THEN
3404 -- When there is no other plan type set to the same plan type id being created
3405 l_fp_cols_rec.gen_src_rev_plan_type_id := p_plan_type_id;
3406 l_fp_cols_rec.gen_src_rev_plan_ver_code := 'CURRENT_BASELINED'; -- Bug 3572548
3407 END IF;
3408 When others then
3409 IF P_PA_DEBUG_MODE = 'Y' THEN
3410 pa_debug.g_err_stage := 'execption while fetching default source
3411 revenue plan type id when approved plan type is not available ';
3412 pa_debug.write('Get_Default_FP_Options: ' || l_module_name,pa_debug.g_err_stage,3);
3413 END IF;
3414
3415 End;
3416 When others then
3417 IF P_PA_DEBUG_MODE = 'Y' THEN
3418 pa_debug.g_err_stage := 'execption while fetching approved default source
3419 revenue plan type id ';
3420 pa_debug.write('Get_Default_FP_Options: ' || l_module_name,pa_debug.g_err_stage,3);
3421 END IF;
3422 Raise;
3423 End;
3424 END IF; -- revenue or cost and rev sep
3425
3426 IF (p_fin_plan_preference_code = PA_FP_CONSTANTS_PKG.G_PREF_COST_AND_REV_SAME)
3427 THEN
3428
3429 -- Separate processing based on plan class code
3430 IF (plan_type_info_rec.plan_class_code = PA_FP_CONSTANTS_PKG.G_PLAN_CLASS_BUDGET)
3431 THEN
3432
3433 -- Estimate to completion source code should be null
3434
3435 l_fp_cols_rec.gen_all_etc_src_code := null;
3436 l_fp_cols_rec.gen_all_incl_unspent_amt_flag := null;
3437 l_fp_cols_rec.gen_all_actual_amts_thru_code := null;
3438
3439 l_fp_cols_rec.gen_all_incl_change_doc_flag := 'N';
3440 l_fp_cols_rec.gen_all_incl_open_comm_flag := 'Y'; -- Bug 3934574 (N -> Y)
3441 l_fp_cols_rec.gen_all_incl_bill_event_flag := 'N';
3442 l_fp_cols_rec.gen_all_ret_manual_line_flag := 'N';
3443
3444 -- Source code should be defaulted to 'FINANCIAL_PLAN'
3445 -- Plan version code should be set to 'CURRENT_BASELINED'
3446
3447 l_fp_cols_rec.gen_all_src_code := 'FINANCIAL_PLAN';
3448 -- Bug 3572548 l_fp_cols_rec.gen_src_all_plan_ver_code := 'CURRENT_BASELINED';
3449 l_fp_cols_rec.gen_src_all_plan_version_id := null;
3450 l_fp_cols_rec.gen_src_all_wp_ver_code := null; -- FP M Phase II Dev effort
3451 l_fp_cols_rec.gen_src_all_wp_version_id := null; -- FP M Phase II Dev effort
3452
3453 -- Population the two fields all_layout_code in the l_fp_cols_rec for a budget.
3454 l_fp_cols_rec.all_layout_code := l_non_periodic_budget_layout;
3455
3456 -- plan type defaulting is as follows
3457 -- 1) if approved cost plan type is added for project use it here
3458 -- 2) else
3459 -- the first plan type with pref code involving 'cost'
3460 Begin
3461 SELECT pt.fin_plan_type_id
3462 ,DECODE(pt.plan_class_code,
3463 'BUDGET','CURRENT_BASELINED',
3464 'FORECAST','CURRENT_APPROVED') -- Bug 3572548
3465 INTO l_fp_cols_rec.gen_src_all_plan_type_id
3466 ,l_fp_cols_rec.gen_src_all_plan_ver_code -- Bug 3572548
3467 FROM pa_proj_fp_options o
3468 ,pa_fin_plan_types_b pt
3469 WHERE o.project_id = p_target_project_id
3470 AND o.fin_plan_option_level_code = 'PLAN_TYPE'
3471 AND o.fin_plan_type_id = pt.fin_plan_type_id
3472 AND nvl(pt.use_for_workplan_flag,'N') = 'N' -- bug 3429026
3473 --bug 3724132 AND pt.approved_rev_plan_type_flag = 'Y'
3474 AND pt.approved_cost_plan_type_flag = 'Y';
3475 Exception
3476 When NO_DATA_FOUND Then
3477 Begin
3478 SELECT *
3479 INTO l_fp_cols_rec.gen_src_all_plan_type_id
3480 ,l_fp_cols_rec.gen_src_all_plan_ver_code -- Bug 3572548
3481 FROM
3482 (SELECT pt.fin_plan_type_id
3483 ,DECODE(pt.plan_class_code,
3484 'BUDGET','CURRENT_BASELINED',
3485 'FORECAST','CURRENT_APPROVED') -- Bug 3572548
3486 FROM pa_proj_fp_options o
3487 ,pa_fin_plan_types_vl pt
3488 WHERE o.project_id = p_target_project_id
3489 AND o.fin_plan_option_level_code = 'PLAN_TYPE'
3490 AND o.fin_plan_type_id = pt.fin_plan_type_id
3491 AND nvl(pt.use_for_workplan_flag,'N') = 'N' -- bug 3429026
3492 AND o.fin_plan_preference_code <> 'REVENUE_ONLY' -- bug 3666398
3493 ORDER BY pt.name ) a
3494 WHERE ROWNUM = 1;
3495 Exception
3496 When NO_DATA_FOUND then
3497 -- When there is no other plan type set to the same plan type id being created
3498 l_fp_cols_rec.gen_src_all_plan_type_id := p_plan_type_id;
3499 l_fp_cols_rec.gen_src_all_plan_ver_code := 'CURRENT_BASELINED'; -- Bug 3572548
3500 When others then
3501 IF P_PA_DEBUG_MODE = 'Y' THEN
3502 pa_debug.g_err_stage := 'execption while fetching default source
3503 all plan type id when approved plan type is not available ';
3504 pa_debug.write('Get_Default_FP_Options: ' || l_module_name,pa_debug.g_err_stage,3);
3505 END IF;
3506
3507 End;
3508 When others then
3509 IF P_PA_DEBUG_MODE = 'Y' THEN
3510 pa_debug.g_err_stage := 'execption while fetching approved default source
3511 all plan type id ';
3512 pa_debug.write('Get_Default_FP_Options: ' || l_module_name,pa_debug.g_err_stage,3);
3513 END IF;
3514 Raise;
3515 End;
3516 ELSE -- Forecast plan type
3517
3518 -- Estimate to completion source code should be 'RESOURCE_SCHEDULE'
3519
3520 l_fp_cols_rec.gen_all_etc_src_code := 'RESOURCE_SCHEDULE';
3521 l_fp_cols_rec.gen_all_incl_unspent_amt_flag := 'N';
3522 /*** Bug 3580542 Default value should be Current Period'
3523 l_fp_cols_rec.gen_all_actual_amts_thru_code := 'PRIOR_PERIOD';
3524 ***/
3525 l_fp_cols_rec.gen_all_actual_amts_thru_code := 'CURRENT_PERIOD';
3526
3527 l_fp_cols_rec.gen_all_incl_change_doc_flag := 'N';
3528 l_fp_cols_rec.gen_all_incl_open_comm_flag := 'Y'; -- Bug 3934574 (N -> Y)
3529 l_fp_cols_rec.gen_all_incl_bill_event_flag := 'N';
3530 l_fp_cols_rec.gen_all_ret_manual_line_flag := 'N';
3531
3532 -- Source related parameters would be null
3533
3534 l_fp_cols_rec.gen_all_src_code := null;
3535 l_fp_cols_rec.gen_src_all_plan_type_id := null;
3536 l_fp_cols_rec.gen_src_all_plan_ver_code := null;
3537 l_fp_cols_rec.gen_src_all_plan_version_id := null;
3538 l_fp_cols_rec.gen_src_all_wp_ver_code := null; -- FP M Phase II Dev effort
3539 l_fp_cols_rec.gen_src_all_wp_version_id := null; -- FP M Phase II Dev effort
3540
3541 -- Population the two fields all_layout_code in the l_fp_cols_rec for a forecast.
3542 l_fp_cols_rec.all_layout_code := l_non_periodic_forecast_layout;
3543
3544 END IF; -- End of BUDGET/FORECAST
3545
3546 END IF; -- ALL case
3547 ELSE -- workplan plan type
3548
3549 -- generate columns do not have any significance for project level and workplan
3550 -- plan type records
3551 null;
3552
3553 END IF;
3554
3555 IF P_PA_DEBUG_MODE = 'Y' THEN
3556 pa_debug.g_err_stage := 'End of Get_Default_FP_Options';
3557 pa_debug.write('Get_Default_FP_Options: ' || l_module_name,pa_debug.g_err_stage,3);
3558 pa_debug.reset_err_stack;
3559 END IF;
3560
3561 RETURN l_fp_cols_rec;
3562
3563 EXCEPTION
3564 WHEN PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc THEN
3565 l_msg_count := FND_MSG_PUB.count_msg;
3566 IF l_msg_count = 1 THEN
3567 PA_INTERFACE_UTILS_PUB.get_messages
3568 (p_encoded => FND_API.G_TRUE
3569 ,p_msg_index => 1
3570 ,p_msg_count => l_msg_count
3571 ,p_msg_data => l_msg_data
3572 ,p_data => l_data
3573 ,p_msg_index_out => l_msg_index_out);
3574 pa_debug.g_err_stage := l_data;
3575 IF P_PA_DEBUG_MODE = 'Y' THEN
3576 pa_debug.write('Get_Default_FP_Options: ' || l_module_name,pa_debug.g_err_stage,5);
3577 END IF;
3578 END IF;
3579 pa_debug.g_err_stage:='Invalid Arguments Passed';
3580 IF P_PA_DEBUG_MODE = 'Y' THEN
3581 pa_debug.write('Get_Default_FP_Options: ' || l_module_name,pa_debug.g_err_stage,5);
3582 pa_debug.reset_err_stack;
3583 END IF;
3584 RAISE;
3585 WHEN OTHERS THEN
3586 FND_MSG_PUB.add_exc_msg
3587 ( p_pkg_name => 'PA_PROJ_FP_OPTIONS_PUB.Get_Default_FP_Options'
3588 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
3589 IF P_PA_DEBUG_MODE = 'Y' THEN
3590 pa_debug.write('Get_Default_FP_Options: ' || l_module_name,SQLERRM,5);
3591 pa_debug.write('Get_Default_FP_Options: ' || l_module_name,pa_debug.G_Err_Stack,5);
3592 pa_debug.reset_err_stack;
3593 END IF;
3594 RAISE;
3595 END Get_Default_FP_Options;
3596
3597
3598 /*===========================================================================================
3599 GET_FP_PROJ_MC_OPTIONS: This procedure returns Multi curr option details for FP Option ID
3600 passed along with the approved_cost_plan_type_flag and approved_rev_plan_type_flag
3601 ===========================================================================================*/
3602 FUNCTION Get_FP_Proj_Mc_Options (p_proj_fp_options_id IN NUMBER) Return FP_MC_COLS
3603 IS
3604 l_fp_mc_cols_rec PA_PROJ_FP_OPTIONS_PUB.FP_MC_COLS;
3605 BEGIN
3606
3607 SELECT approved_cost_plan_type_flag
3608 ,approved_rev_plan_type_flag
3609 ,primary_cost_forecast_flag
3610 ,primary_rev_forecast_flag
3611 ,projfunc_cost_rate_type
3612 ,projfunc_cost_rate_date_type
3613 ,projfunc_cost_rate_date
3614 ,projfunc_rev_rate_type
3615 ,projfunc_rev_rate_date_type
3616 ,projfunc_rev_rate_date
3617 ,project_cost_rate_type
3618 ,project_cost_rate_date_type
3619 ,project_cost_rate_date
3620 ,project_rev_rate_type
3621 ,project_rev_rate_date_type
3622 ,project_rev_rate_date
3623 INTO l_fp_mc_cols_rec
3624 FROM pa_proj_fp_options
3625 WHERE proj_fp_options_id = p_proj_fp_options_id;
3626
3627 RETURN l_fp_mc_cols_rec;
3628 EXCEPTION
3629 WHEN OTHERS THEN
3630 RETURN Null;
3631 END Get_Fp_Proj_Mc_Options;
3632
3633 /*===========================================================================================
3634 GET_FP_PLAN_TYPE_MC_OPTIONS: This procedure returns Multi currency option details for
3635 FP Option ID passed along with approved_cost_plan_type_flag and approved_rev_plan_type_flag
3636 ===========================================================================================*/
3637 FUNCTION Get_FP_Plan_Type_Mc_Options (p_fin_plan_type_id IN NUMBER) Return FP_MC_COLS
3638 IS
3639 l_fp_mc_cols_rec PA_PROJ_FP_OPTIONS_PUB.FP_MC_COLS;
3640 BEGIN
3641 SELECT approved_cost_plan_type_flag
3642 ,approved_rev_plan_type_flag
3643 ,primary_cost_forecast_flag
3644 ,primary_rev_forecast_flag
3645 ,projfunc_cost_rate_type
3646 ,projfunc_cost_rate_date_type
3647 ,projfunc_cost_rate_date
3648 ,projfunc_rev_rate_type
3649 ,projfunc_rev_rate_date_type
3650 ,projfunc_rev_rate_date
3651 ,project_cost_rate_type
3652 ,project_cost_rate_date_type
3653 ,project_cost_rate_date
3654 ,project_rev_rate_type
3655 ,project_rev_rate_date_type
3656 ,project_rev_rate_date
3657 INTO l_fp_mc_cols_rec
3658 FROM pa_fin_plan_types_b
3659 WHERE fin_plan_type_id = p_fin_plan_type_id;
3660 RETURN l_fp_mc_cols_rec;
3661 EXCEPTION
3662 WHEN OTHERS THEN
3663 RETURN Null;
3664 END Get_Fp_Plan_Type_Mc_Options;
3665
3666 /*
3667 Bug # 2618119. This procedure is called in the context of a plan version.
3668 When the resource list and the time phasing of a plan version
3669 is changed in the Edit planning options page, it is updated in
3670 the fp options table for the version. More over the resource
3671 list id and the period profile id(if the time phasing is PA/GL)
3672 should be updated in the budget versions table. This procedure
3673 doesnot do anything if amounts exist for the version.
3674
3675 Bug 3425122: From plan settings page the api would be called to synchronise
3676 the columns that are part of both pa_budget_versions and also pa_proj_fp_otions.
3677 They are resource_list_id, period_mask_id and current_planning_period.
3678 actual_amts_thru_period is a column present only in budget versions table. So,
3679 only this column is passed and rest of the values should be read from
3680 pa_proj_fp_options table
3681
3682 Note: In FP M, period profile concept has been changed to period masks. Commenting
3683 all the related existing code
3684 */
3685 procedure SYNCHRONIZE_BUDGET_VERSION
3686 (
3687 p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE,
3688 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
3689 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
3690 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
3691 )
3692 IS
3693
3694 l_project_id pa_budget_versions.project_id%TYPE;
3695 l_time_phased_code pa_proj_fp_options.cost_time_phased_code%TYPE;
3696 l_resource_list_id pa_proj_fp_options.all_resource_list_id%TYPE;
3697 /*
3698 l_period_profile_id pa_budget_versions.period_profile_id%TYPE;
3699 l_curr_period_profile_id pa_budget_versions.period_profile_id%TYPE;
3700 l_period_type pa_proj_period_profiles.plan_period_type%TYPE;
3701 l_curr_period_type pa_proj_period_profiles.plan_period_type%TYPE;
3702 l_start_period pa_proj_period_profiles.period_name1%TYPE;
3703 l_end_period pa_proj_period_profiles.period_name1%TYPE;
3704 l_period_profile_type pa_proj_period_profiles.period_profile_type%TYPE;
3705 l_period_set_name pa_proj_period_profiles.period_set_name%TYPE;
3706 l_gl_period_type pa_proj_period_profiles.gl_period_type%TYPE;
3707 l_plan_start_date pa_proj_period_profiles.period1_start_date%TYPE;
3708 l_plan_end_date pa_proj_period_profiles.period1_start_date%TYPE;
3709 l_number_of_periods pa_proj_period_profiles.number_of_periods%TYPE;
3710 */
3711 l_update_flag varchar2(1);
3712
3713 l_msg_count NUMBER := 0;
3714 l_data VARCHAR2(2000);
3715 l_msg_data VARCHAR2(2000);
3716 l_error_msg_code VARCHAR2(30);
3717 l_msg_index_out NUMBER;
3718 l_return_status VARCHAR2(2000);
3719 l_debug_mode VARCHAR2(30);
3720
3721 CURSOR version_option_info_cur IS
3722 SELECT cost_period_mask_id,
3723 rev_period_mask_id,
3724 all_period_mask_id,
3725 cost_current_planning_period,
3726 rev_current_planning_period,
3727 all_current_planning_period,
3728 fin_plan_preference_code,
3729 cost_resource_list_id,
3730 revenue_resource_list_id,
3731 all_resource_list_id
3732 FROM pa_proj_fp_options
3733 WHERE fin_plan_version_id = p_budget_version_id;
3734
3735 version_option_info_rec version_option_info_cur%ROWTYPE;
3736
3737 BEGIN
3738
3739 FND_MSG_PUB.initialize;
3740 IF P_PA_DEBUG_MODE = 'Y' THEN
3741 pa_debug.init_err_stack('PA_PROJ_FP_OPTIONS_PUB.SYNCHRONIZE_BUDGET_VERSION');
3742 fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
3743 l_debug_mode := NVL(l_debug_mode, 'Y');
3744 pa_debug.set_process('PLSQL','LOG',l_debug_mode);
3745 END IF;
3746 x_msg_count := 0;
3747 x_return_status := FND_API.G_RET_STS_SUCCESS;
3748
3749 -- Check if budget version id is null. if yes
3750 -- throw an error.
3751
3752 IF P_PA_DEBUG_MODE = 'Y' THEN
3753 pa_debug.g_err_stage := 'Parameter Validation';
3754 pa_debug.write('SYNCHRONIZE_BUDGET_VERSION: ' || l_module_name,pa_debug.g_err_stage,3);
3755 END IF;
3756
3757 IF (p_budget_version_id IS NULL) THEN
3758
3759 IF P_PA_DEBUG_MODE = 'Y' THEN
3760 pa_debug.g_err_stage := 'budget version id='||p_budget_version_id;
3761 pa_debug.write('SYNCHRONIZE_BUDGET_VERSION: ' || l_module_name,pa_debug.g_err_stage,5);
3762 END IF;
3763
3764 PA_UTILS.ADD_MESSAGE(p_app_short_name=> 'PA',
3765 p_msg_name => 'PA_FP_INV_PARAM_PASSED');
3766
3767 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
3768
3769 END IF;
3770
3771 IF P_PA_DEBUG_MODE = 'Y' THEN
3772 pa_debug.g_err_stage := 'Parameter validation complete';
3773 pa_debug.write('SYNCHRONIZE_BUDGET_VERSION: ' || l_module_name,pa_debug.g_err_stage,3);
3774 pa_debug.g_err_stage := 'Check if amounts exist for this version';
3775 pa_debug.write('SYNCHRONIZE_BUDGET_VERSION: ' || l_module_name,pa_debug.g_err_stage,3);
3776 END IF;
3777
3778 -- Open and fetch fin plan version info cur
3779
3780 OPEN version_option_info_cur;
3781 FETCH version_option_info_cur INTO version_option_info_rec;
3782 IF version_option_info_cur%NOTFOUND THEN
3783 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
3784 END IF;
3785 CLOSE version_option_info_cur;
3786
3787
3788 /*
3789 IF(l_amount_exists = 'Y') THEN
3790 IF P_PA_DEBUG_MODE = 'Y' THEN
3791 pa_debug.g_err_stage := 'Amounts exist for this version - returning';
3792 pa_debug.write('SYNCHRONIZE_BUDGET_VERSION: ' || l_module_name,pa_debug.g_err_stage,3);
3793 END IF;
3794 return;
3795 END IF;
3796 */
3797
3798 -- Updation of current planning period,period mask id and actuals thru period and resource list id is possible always
3799 -- for a working version
3800
3801 IF (version_option_info_rec.fin_plan_preference_code = 'COST_ONLY') THEN
3802 UPDATE pa_budget_versions
3803 SET current_planning_period = version_option_info_rec.cost_current_planning_period
3804 ,period_mask_id = version_option_info_rec.cost_period_mask_id
3805 --- ,actual_amts_thru_period = p_actual_amts_thru_period
3806 ,resource_list_id = version_option_info_rec.cost_resource_list_id
3807 ,record_version_number = record_version_number + 1
3808 WHERE budget_version_id = p_budget_version_id;
3809 ELSIF (version_option_info_rec.fin_plan_preference_code = 'REVENUE_ONLY') THEN
3810 UPDATE pa_budget_versions
3811 SET current_planning_period = version_option_info_rec.rev_current_planning_period
3812 ,period_mask_id = version_option_info_rec.rev_period_mask_id
3813 --- ,actual_amts_thru_period = p_actual_amts_thru_period
3814 ,resource_list_id = version_option_info_rec.revenue_resource_list_id
3815 ,record_version_number = record_version_number + 1
3816 WHERE budget_version_id = p_budget_version_id;
3817 ELSIF (version_option_info_rec.fin_plan_preference_code = 'COST_AND_REV_SAME') THEN
3818 UPDATE pa_budget_versions
3819 SET current_planning_period = version_option_info_rec.all_current_planning_period
3820 ,period_mask_id = version_option_info_rec.all_period_mask_id
3821 --- ,actual_amts_thru_period = p_actual_amts_thru_period
3822 ,resource_list_id = version_option_info_rec.all_resource_list_id
3823 ,record_version_number = record_version_number + 1
3824 WHERE budget_version_id = p_budget_version_id;
3825 END IF;
3826
3827 /* Commented for Bug 3425122 changes
3828 -- Get the updated time phased code for the version.
3829 l_time_phased_code := pa_fin_plan_utils.Get_Time_Phased_code(p_fin_plan_version_id=>
3830 p_budget_version_id);
3831
3832
3833 -- obtain the period type corresponding to the time phased code
3834 select decode(l_time_phased_code,
3835 PA_FP_CONSTANTS_PKG.G_TIME_PHASED_CODE_P,PA_FP_CONSTANTS_PKG.G_PERIOD_TYPE_PA,
3836 PA_FP_CONSTANTS_PKG.G_TIME_PHASED_CODE_G,PA_FP_CONSTANTS_PKG.G_PERIOD_TYPE_GL,l_time_phased_code)
3837 into l_curr_period_type
3838 from dual;
3839
3840 --Select the period profile for this version. Get the period profile period type
3841 -- of this period profile. If the time_phased_code(curr period type) and the period profile period type
3842 -- are different, then obtain the current period profile corresponding to the time
3843 -- phased code and stamp it on the budget versions table.
3844
3845
3846 BEGIN
3847
3848 select period_profile_id,project_id
3849 into l_period_profile_id,l_project_id
3850 from pa_budget_versions
3851 where budget_version_id = p_budget_version_id;
3852
3853 EXCEPTION
3854
3855 WHEN NO_DATA_FOUND THEN
3856 IF P_PA_DEBUG_MODE = 'Y' THEN
3857 pa_debug.g_err_stage := 'Invalid budget version id='||p_budget_version_id;
3858 pa_debug.write('SYNCHRONIZE_BUDGET_VERSION: ' || l_module_name,pa_debug.g_err_stage,5);
3859 END IF;
3860
3861 PA_UTILS.ADD_MESSAGE(p_app_short_name=> 'PA',
3862 p_msg_name => 'PA_FP_INV_PARAM_PASSED');
3863
3864 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
3865 END;
3866
3867 IF P_PA_DEBUG_MODE = 'Y' THEN
3868 pa_debug.g_err_stage := 'Determine if period profile to be updated.If yes get the id';
3869 pa_debug.write('SYNCHRONIZE_BUDGET_VERSION: ' || l_module_name,pa_debug.g_err_stage,3);
3870 END IF;
3871
3872 IF (l_period_profile_id is NULL) THEN
3873 IF (l_curr_period_type in (PA_FP_CONSTANTS_PKG.G_PERIOD_TYPE_PA,
3874 PA_FP_CONSTANTS_PKG.G_PERIOD_TYPE_GL)) THEN
3875
3876 pa_prj_period_profile_utils.get_curr_period_profile_info(
3877 p_project_id => l_project_id
3878 ,p_period_type => l_curr_period_type
3879 ,p_period_profile_type=> PA_FP_CONSTANTS_PKG.G_PD_PROFILE_FIN_PLANNING
3880 ,x_period_profile_id => l_curr_period_profile_id
3881 ,x_start_period => l_start_period
3882 ,x_end_period => l_end_period
3883 ,x_return_status => l_return_status
3884 ,x_msg_count => l_msg_count
3885 ,x_msg_data => l_msg_data);
3886
3887 l_update_flag := 'Y';
3888
3889 ELSE -- period type is date range or none.
3890
3891 l_curr_period_profile_id := NULL;
3892 l_update_flag := 'N';
3893
3894 END IF; -- check for period type.
3895
3896 ELSE -- period profile id is not null
3897
3898 IF (l_curr_period_type not in (PA_FP_CONSTANTS_PKG.G_PERIOD_TYPE_PA,
3899 PA_FP_CONSTANTS_PKG.G_PERIOD_TYPE_GL)) THEN
3900
3901 l_curr_period_profile_id := NULL;
3902 l_update_flag := 'Y';
3903
3904 ELSE -- period type in PA/GL
3905
3906 pa_prj_period_profile_utils.Get_Prj_Period_Profile_Dtls(
3907 p_period_profile_id => l_period_profile_id,
3908 p_debug_mode => 'N',
3909 p_add_msg_in_stack => 'Y',
3910 x_period_profile_type=>l_period_profile_type,
3911 x_plan_period_type =>l_period_type,
3912 x_period_set_name =>l_period_set_name,
3913 x_gl_period_type =>l_gl_period_type ,
3914 x_plan_start_date =>l_plan_start_date,
3915 x_plan_end_date =>l_plan_end_date,
3916 x_number_of_periods =>l_number_of_periods,
3917 x_return_status =>l_return_status,
3918 x_msg_data =>l_msg_data);
3919
3920 IF ( l_period_type = l_curr_period_type ) THEN
3921
3922 l_update_flag := 'N';
3923
3924 ELSE -- time phasing has changed.
3925
3926 pa_prj_period_profile_utils.get_curr_period_profile_info(
3927 p_project_id => l_project_id
3928 ,p_period_type => l_curr_period_type
3929 ,p_period_profile_type=> PA_FP_CONSTANTS_PKG.G_PD_PROFILE_FIN_PLANNING
3930 ,x_period_profile_id => l_curr_period_profile_id
3931 ,x_start_period => l_start_period
3932 ,x_end_period => l_end_period
3933 ,x_return_status => l_return_status
3934 ,x_msg_count => l_msg_count
3935 ,x_msg_data => l_msg_data);
3936
3937 l_update_flag := 'Y';
3938
3939 END IF; -- check for equality of period type.
3940 END IF; -- check for period type not in PA/GL.
3941
3942 END IF; -- check for period profile id being null.
3943
3944 IF l_update_flag = 'Y' THEN
3945 update pa_budget_versions
3946 set period_profile_id = l_curr_period_profile_id
3947 where budget_version_id = p_budget_version_id;
3948
3949 IF P_PA_DEBUG_MODE = 'Y' THEN
3950 pa_debug.g_err_stage := 'period profile id updated : updated id -> '||to_char(l_curr_period_profile_id);
3951 pa_debug.write('SYNCHRONIZE_BUDGET_VERSION: ' || l_module_name,pa_debug.g_err_stage,3);
3952 END IF;
3953 END IF;
3954 */
3955 IF P_PA_DEBUG_MODE = 'Y' THEN
3956 pa_debug.g_err_stage := 'Exit Synchronize_Budget_version';
3957 pa_debug.write('SYNCHRONIZE_BUDGET_VERSION: ' || l_module_name,pa_debug.g_err_stage,2);
3958 pa_debug.reset_err_stack;
3959 END IF;
3960 EXCEPTION
3961 WHEN PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc THEN
3962 l_msg_count := FND_MSG_PUB.count_msg;
3963 IF l_msg_count = 1 THEN
3964 PA_INTERFACE_UTILS_PUB.get_messages
3965 (p_encoded => FND_API.G_TRUE
3966 ,p_msg_index => 1
3967 ,p_msg_count => l_msg_count
3968 ,p_msg_data => l_msg_data
3969 ,p_data => l_data
3970 ,p_msg_index_out => l_msg_index_out);
3971 x_msg_data := l_data;
3972 x_msg_count := l_msg_count;
3973 ELSE
3974 x_msg_count := l_msg_count;
3975 END IF;
3976 x_return_status := FND_API.G_RET_STS_ERROR;
3977 IF P_PA_DEBUG_MODE = 'Y' THEN
3978 pa_debug.g_err_stage:='Invalid Arguments Passed';
3979 pa_debug.write('SYNCHRONIZE_BUDGET_VERSION: ' || l_module_name,pa_debug.g_err_stage,5);
3980 pa_debug.reset_err_stack;
3981 END IF;
3982 RETURN;
3983
3984 WHEN Others THEN
3985 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3986 x_msg_count := 1;
3987 x_msg_data := SQLERRM;
3988 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_PROJ_FP_OPTIONS_PUB'
3989 ,p_procedure_name => 'SYNCHRONIZE_BUDGET_VERSION');
3990 IF P_PA_DEBUG_MODE = 'Y' THEN
3991 pa_debug.g_err_stage:='Unexpected Error'||SQLERRM;
3992 pa_debug.write('SYNCHRONIZE_BUDGET_VERSION: ' || l_module_name,pa_debug.g_err_stage,5);
3993 pa_debug.reset_err_stack;
3994 END IF;
3995 RAISE;
3996 END SYNCHRONIZE_BUDGET_VERSION;
3997
3998 /*=====================================================================================
3999 This is a private api that would return gen src plan version id for a given option
4000 based on project id, target version type, gen src plan type id and gen src plan
4001 version code inputs
4002
4003 23-JAN-2004 rravipat FP M Dev effort Bug 3354518 (IDC)
4004 Initial Creation
4005 =====================================================================================*/
4006 FUNCTION Gen_Src_Plan_Version_Id(
4007 p_target_project_id IN pa_projects_all.project_id%TYPE,
4008 p_target_version_type IN pa_budget_versions.version_type%TYPE,
4009 p_gen_src_plan_type_id IN pa_proj_fp_options.gen_src_cost_plan_type_id%TYPE,
4010 p_gen_src_plan_ver_code IN pa_proj_fp_options.gen_src_cost_plan_ver_code%TYPE)
4011 RETURN pa_budget_versions.budget_version_id%TYPE is
4012
4013 l_return_status VARCHAR2(2000);
4014 l_msg_count NUMBER := 0;
4015 l_msg_data VARCHAR2(2000);
4016 l_err_code NUMBER := 0;
4017 l_err_stack VARCHAR2(2000);
4018 l_err_stage VARCHAR2(2000);
4019 l_msg_index_out NUMBER := 0;
4020 l_data VARCHAR2(2000);
4021 l_debug_mode VARCHAR2(30);
4022
4023 l_dummy_options_id pa_proj_fp_options.proj_fp_options_id%TYPE;
4024 l_gen_src_plan_version_id pa_budget_versions.budget_version_id%TYPE;
4025
4026 CURSOR src_plan_type_info_cur (c_fin_plan_type_id NUMBER, c_project_id NUMBER) IS
4027 SELECT fin_plan_preference_code
4028 FROM pa_proj_fp_options
4029 WHERE project_id = c_project_id
4030 AND fin_plan_type_id = c_fin_plan_type_id
4031 AND fin_plan_option_level_code = 'PLAN_TYPE';
4032
4033 src_plan_type_info_rec src_plan_type_info_cur%ROWTYPE;
4034
4035 BEGIN
4036
4037 IF P_PA_DEBUG_MODE = 'Y' THEN
4038 pa_debug.set_err_stack('PA_PROJ_FP_OPTIONS_PUB.Gen_Src_Plan_Version_Id');
4039 fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
4040 l_debug_mode := NVL(l_debug_mode, 'Y');
4041 pa_debug.set_process('PLSQL','LOG',l_debug_mode);
4042 END IF;
4043
4044 IF P_PA_DEBUG_MODE = 'Y' THEN
4045 pa_debug.g_err_stage:='Opening src_plan_type_info_cur';
4046 pa_debug.write('Gen_Src_Plan_Version_Id: ' || l_module_name,pa_debug.g_err_stage,5);
4047 pa_debug.g_err_stage:='p_target_project_id = '||p_target_project_id;
4048 pa_debug.write('Gen_Src_Plan_Version_Id: ' || l_module_name,pa_debug.g_err_stage,5);
4049 pa_debug.g_err_stage:='p_target_version_type = '|| p_target_version_type;
4050 pa_debug.write('Gen_Src_Plan_Version_Id: ' || l_module_name,pa_debug.g_err_stage,5);
4051 pa_debug.g_err_stage:='p_gen_src_plan_type_id = '|| p_gen_src_plan_type_id;
4052 pa_debug.write('Gen_Src_Plan_Version_Id: ' || l_module_name,pa_debug.g_err_stage,5);
4053 pa_debug.g_err_stage:='p_gen_src_plan_ver_code = '||p_gen_src_plan_ver_code;
4054 pa_debug.write('Gen_Src_Plan_Version_Id: ' || l_module_name,pa_debug.g_err_stage,5);
4055 END IF;
4056
4057 OPEN src_plan_type_info_cur(p_gen_src_plan_type_id, p_target_project_id);
4058 FETCH src_plan_type_info_cur INTO src_plan_type_info_rec;
4059 IF src_plan_type_info_cur%NOTFOUND THEN
4060 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
4061 END IF;
4062 CLOSE src_plan_type_info_cur;
4063
4064 IF P_PA_DEBUG_MODE = 'Y' THEN
4065 pa_debug.g_err_stage:='After Closing src_plan_type_info_cur';
4066 pa_debug.write('Gen_Src_Plan_Version_Id: ' || l_module_name,pa_debug.g_err_stage,5);
4067 pa_debug.g_err_stage:='src_plan_type_info_rec.fin_plan_preference_code = = '||src_plan_type_info_rec.fin_plan_preference_code;
4068 pa_debug.write('Gen_Src_Plan_Version_Id: ' || l_module_name,pa_debug.g_err_stage,5);
4069 END IF;
4070
4071 IF (src_plan_type_info_rec.fin_plan_preference_code = PA_FP_CONSTANTS_PKG.G_PREF_COST_ONLY ) OR
4072 -- (src_plan_type_info_rec.fin_plan_preference_code = PA_FP_CONSTANTS_PKG.G_PREF_REVENUE_ONLY ) OR
4073 --Commented the above condition for bug 4052619. The source plan type can never be REVENUE_ONLY
4074 (src_plan_type_info_rec.fin_plan_preference_code = PA_FP_CONSTANTS_PKG.G_PREF_COST_AND_REV_SAME )
4075 THEN
4076 -- For all these cases, preference code need not be passed as they
4077 -- can be derived in the called util apis
4078
4079 IF p_gen_src_plan_ver_code IN ('CURRENT_BASELINED','CURRENT_APPROVED') THEN
4080
4081 -- call util api to fetch current baselined version
4082 pa_fin_plan_utils.Get_Baselined_Version_Info(
4083 p_project_id => p_target_project_id
4084 ,p_fin_plan_type_id => p_gen_src_plan_type_id
4085 ,p_version_type => null
4086 ,x_fp_options_id => l_dummy_options_id
4087 ,x_fin_plan_version_id => l_gen_src_plan_version_id
4088 ,x_return_status => l_return_status
4089 ,x_msg_count => l_msg_count
4090 ,x_msg_data => l_msg_data );
4091 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4092 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
4093 END IF;
4094
4095 ELSIF p_gen_src_plan_ver_code = 'CURRENT_WORKING' THEN
4096
4097 -- call util api to fetch current working version
4098 pa_fin_plan_utils.Get_Curr_Working_Version_Info(
4099 p_project_id => p_target_project_id
4100 ,p_fin_plan_type_id => p_gen_src_plan_type_id
4101 ,p_version_type => null
4102 ,x_fp_options_id => l_dummy_options_id
4103 ,x_fin_plan_version_id => l_gen_src_plan_version_id
4104 ,x_return_status => l_return_status
4105 ,x_msg_count => l_msg_count
4106 ,x_msg_data => l_msg_data );
4107 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4108 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
4109 END IF;
4110
4111 ELSIF p_gen_src_plan_ver_code IN ('ORIGINAL_APPROVED','ORIGINAL_BASELINED') THEN
4112
4113 -- call util api to fetch current original version
4114 pa_fin_plan_utils.Get_Curr_Original_Version_Info(
4115 p_project_id => p_target_project_id
4116 ,p_fin_plan_type_id => p_gen_src_plan_type_id
4117 ,p_version_type => null
4118 ,x_fp_options_id => l_dummy_options_id
4119 ,x_fin_plan_version_id => l_gen_src_plan_version_id
4120 ,x_return_status => l_return_status
4121 ,x_msg_count => l_msg_count
4122 ,x_msg_data => l_msg_data );
4123 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4124 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
4125 END IF;
4126
4127 END IF; -- p_gen_src_plan_ver_code
4128
4129 IF P_PA_DEBUG_MODE = 'Y' THEN
4130 pa_debug.g_err_stage := 'End of Gen_Src_Plan_Version_Id';
4131 pa_debug.write('Get_Default_FP_Options: ' || l_module_name,pa_debug.g_err_stage,3);
4132 pa_debug.reset_err_stack;
4133 END IF;
4134
4135 RETURN l_gen_src_plan_version_id;
4136
4137 ELSE -- src plan type is cost and rev sep case
4138
4139
4140 --Bug 4052619. If the source is COST_AND_REV_SEP then irrespective of the target version type, only the cost
4141 --source version should be found out since a version of type REVENUE can never be a generation source.
4142
4143 IF p_gen_src_plan_ver_code IN ('CURRENT_BASELINED','CURRENT_APPROVED') THEN
4144
4145 -- call util api to fetch current baselined version
4146 pa_fin_plan_utils.Get_Baselined_Version_Info(
4147 p_project_id => p_target_project_id
4148 ,p_fin_plan_type_id => p_gen_src_plan_type_id
4149 ,p_version_type => 'COST'
4150 ,x_fp_options_id => l_dummy_options_id
4151 ,x_fin_plan_version_id => l_gen_src_plan_version_id
4152 ,x_return_status => l_return_status
4153 ,x_msg_count => l_msg_count
4154 ,x_msg_data => l_msg_data );
4155 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4156 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
4157 END IF;
4158
4159 ELSIF p_gen_src_plan_ver_code = 'CURRENT_WORKING' THEN
4160
4161 -- call util api to fetch current working version
4162 pa_fin_plan_utils.Get_Curr_Working_Version_Info(
4163 p_project_id => p_target_project_id
4164 ,p_fin_plan_type_id => p_gen_src_plan_type_id
4165 ,p_version_type => 'COST'
4166 ,x_fp_options_id => l_dummy_options_id
4167 ,x_fin_plan_version_id => l_gen_src_plan_version_id
4168 ,x_return_status => l_return_status
4169 ,x_msg_count => l_msg_count
4170 ,x_msg_data => l_msg_data );
4171 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4172 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
4173 END IF;
4174
4175 ELSIF p_gen_src_plan_ver_code IN ('ORIGINAL_APPROVED','ORIGINAL_BASELINED') THEN
4176
4177 -- call util api to fetch current original version
4178 pa_fin_plan_utils.Get_Curr_Original_Version_Info(
4179 p_project_id => p_target_project_id
4180 ,p_fin_plan_type_id => p_gen_src_plan_type_id
4181 ,p_version_type => 'COST'
4182 ,x_fp_options_id => l_dummy_options_id
4183 ,x_fin_plan_version_id => l_gen_src_plan_version_id
4184 ,x_return_status => l_return_status
4185 ,x_msg_count => l_msg_count
4186 ,x_msg_data => l_msg_data );
4187 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4188 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
4189 END IF;
4190
4191 END IF; -- p_gen_src_plan_ver_code
4192
4193
4194 RETURN l_gen_src_plan_version_id;
4195
4196
4197 IF P_PA_DEBUG_MODE = 'Y' THEN
4198 pa_debug.g_err_stage := 'End of Gen_Src_Plan_Version_Id';
4199 pa_debug.write('Get_Default_FP_Options: ' || l_module_name,pa_debug.g_err_stage,3);
4200 pa_debug.reset_err_stack;
4201 END IF;
4202
4203
4204 END IF; -- src plan type preference code
4205
4206 IF P_PA_DEBUG_MODE = 'Y' THEN
4207 pa_debug.g_err_stage := 'End of Gen_Src_Plan_Version_Id';
4208 pa_debug.write('Get_Default_FP_Options: ' || l_module_name,pa_debug.g_err_stage,3);
4209 pa_debug.reset_err_stack;
4210 END IF;
4211
4212 EXCEPTION
4213 WHEN PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc THEN
4214 l_msg_count := FND_MSG_PUB.count_msg;
4215 IF l_msg_count = 1 THEN
4216 PA_INTERFACE_UTILS_PUB.get_messages
4217 (p_encoded => FND_API.G_TRUE
4218 ,p_msg_index => 1
4219 ,p_msg_count => l_msg_count
4220 ,p_msg_data => l_msg_data
4221 ,p_data => l_data
4222 ,p_msg_index_out => l_msg_index_out);
4223 pa_debug.g_err_stage := l_data;
4224 IF P_PA_DEBUG_MODE = 'Y' THEN
4225 pa_debug.write('Gen_Src_Plan_Version_Id: ' || l_module_name,pa_debug.g_err_stage,5);
4226 END IF;
4227 END IF;
4228 pa_debug.g_err_stage:='Invalid Arguments Passed';
4229 IF P_PA_DEBUG_MODE = 'Y' THEN
4230 pa_debug.write('Gen_Src_Plan_Version_Id: ' || l_module_name,pa_debug.g_err_stage,5);
4231 pa_debug.reset_err_stack;
4232 END IF;
4233 RAISE;
4234 WHEN OTHERS THEN
4235 FND_MSG_PUB.add_exc_msg
4236 ( p_pkg_name => 'PA_PROJ_FP_OPTIONS_PUB.Gen_Src_Plan_Version_Id'
4237 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
4238 IF P_PA_DEBUG_MODE = 'Y' THEN
4239 pa_debug.write('Gen_Src_Plan_Version_Id: ' || l_module_name,SQLERRM,5);
4240 pa_debug.write('Gen_Src_Plan_Version_Id: ' || l_module_name,pa_debug.G_Err_Stack,5);
4241 pa_debug.reset_err_stack;
4242 END IF;
4243 RAISE;
4244 END Gen_Src_Plan_Version_Id;
4245
4246 /*=====================================================================================
4247 This is a private api that would return gen src wokplan budget version id for a given
4248 option based on project id and gen src workplan version code inputs
4249
4250 20-MAR-2004 rravipat FP M Dev effort Phase II changes
4251 Initial Creation
4252 =====================================================================================*/
4253 FUNCTION Gen_Src_WP_Version_Id(
4254 p_target_project_id IN pa_projects_all.project_id%TYPE,
4255 p_gen_src_wp_ver_code IN pa_proj_fp_options.gen_src_cost_wp_ver_code%TYPE)
4256 RETURN pa_budget_versions.budget_version_id%TYPE is
4257
4258 l_return_status VARCHAR2(2000);
4259 l_msg_count NUMBER := 0;
4260 l_msg_data VARCHAR2(2000);
4261 l_err_code NUMBER := 0;
4262 l_err_stack VARCHAR2(2000);
4263 l_err_stage VARCHAR2(2000);
4264 l_msg_index_out NUMBER := 0;
4265 l_data VARCHAR2(2000);
4266 l_debug_mode VARCHAR2(30);
4267
4268 l_gen_src_wp_ver_id pa_budget_versions.budget_version_id%TYPE;
4269
4270 CURSOR last_published_wp_version_cur IS
4271 select bv.budget_version_id
4272 from pa_budget_versions bv,
4273 pa_proj_elem_ver_structure ver
4274 where bv.project_id = p_target_project_id
4275 and bv.wp_version_flag = 'Y'
4276 and bv.project_id = ver.project_id
4277 and bv.project_structure_version_id = ver.element_version_id
4278 and ver.LATEST_EFF_PUBLISHED_FLAG = 'Y';
4279
4280 CURSOR baselined_wp_version_cur IS
4281 select bv.budget_version_id
4282 from pa_budget_versions bv,
4283 pa_proj_elem_ver_structure ver
4284 where bv.project_id = p_target_project_id
4285 and bv.wp_version_flag = 'Y'
4286 and bv.project_id = ver.project_id
4287 and bv.project_structure_version_id = ver.element_version_id
4288 and ver.current_baseline_date is not null ;
4289
4290 CURSOR current_working_wp_version_cur IS
4291 select bv.budget_version_id
4292 from pa_budget_versions bv,
4293 pa_proj_elem_ver_structure ver
4294 where bv.project_id = p_target_project_id
4295 and bv.wp_version_flag = 'Y'
4296 and bv.project_id = ver.project_id
4297 and bv.project_structure_version_id = ver.element_version_id
4298 and ver.current_working_flag = 'Y';
4299
4300 BEGIN
4301 pa_debug.set_curr_function(
4302 p_function =>'XX.XXXX'
4303 ,p_debug_mode => l_debug_mode );
4304
4305 IF P_PA_DEBUG_MODE = 'Y' THEN
4306 pa_debug.g_err_stage := 'Start of Gen_Src_WP_Version_Id';
4307 pa_debug.write('Get_Default_FP_Options: ' || l_module_name,pa_debug.g_err_stage,3);
4308 END IF;
4309
4310 IF p_gen_src_wp_ver_code = 'LAST_PUBLISHED' THEN
4311
4312 OPEN last_published_wp_version_cur;
4313 FETCH last_published_wp_version_cur INTO l_gen_src_wp_ver_id;
4314 CLOSE last_published_wp_version_cur;
4315
4316 ELSIF p_gen_src_wp_ver_code = 'BASELINED' THEN
4317
4318 OPEN baselined_wp_version_cur;
4319 FETCH baselined_wp_version_cur INTO l_gen_src_wp_ver_id;
4320 CLOSE baselined_wp_version_cur;
4321
4322 ELSIF p_gen_src_wp_ver_code = 'CURRENT_WORKING' THEN
4323
4324 OPEN current_working_wp_version_cur;
4325 FETCH current_working_wp_version_cur INTO l_gen_src_wp_ver_id;
4326 CLOSE current_working_wp_version_cur;
4327
4328 END IF;
4329
4330 IF P_PA_DEBUG_MODE = 'Y' THEN
4331 pa_debug.g_err_stage := 'End of Gen_Src_WP_Version_Id';
4332 pa_debug.write('Get_Default_FP_Options: ' || l_module_name,pa_debug.g_err_stage,3);
4333 END IF;
4334 pa_debug.reset_curr_function();
4335
4336 RETURN l_gen_src_wp_ver_id;
4337
4338 EXCEPTION
4339 WHEN PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc THEN
4340 l_msg_count := FND_MSG_PUB.count_msg;
4341 IF l_msg_count = 1 THEN
4342 PA_INTERFACE_UTILS_PUB.get_messages
4343 (p_encoded => FND_API.G_TRUE
4344 ,p_msg_index => 1
4345 ,p_msg_count => l_msg_count
4346 ,p_msg_data => l_msg_data
4347 ,p_data => l_data
4348 ,p_msg_index_out => l_msg_index_out);
4349 pa_debug.g_err_stage := l_data;
4350 IF P_PA_DEBUG_MODE = 'Y' THEN
4351 pa_debug.write('Gen_Src_WP_Version_Id: ' || l_module_name,pa_debug.g_err_stage,5);
4352 END IF;
4353 END IF;
4354 IF P_PA_DEBUG_MODE = 'Y' THEN
4355 pa_debug.g_err_stage:='Invalid Arguments Passed';
4356 pa_debug.write('Gen_Src_WP_Version_Id: ' || l_module_name,pa_debug.g_err_stage,5);
4357 END IF;
4358 pa_debug.reset_curr_function;
4359 RAISE;
4360 WHEN OTHERS THEN
4361 FND_MSG_PUB.add_exc_msg
4362 ( p_pkg_name => 'PA_PROJ_FP_OPTIONS_PUB.Gen_Src_WP_Version_Id'
4363 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
4364 IF P_PA_DEBUG_MODE = 'Y' THEN
4365 pa_debug.write('Gen_Src_WP_Version_Id: ' || l_module_name,SQLERRM,5);
4366 pa_debug.write('Gen_Src_WP_Version_Id: ' || l_module_name,pa_debug.G_Err_Stack,5);
4367 END IF;
4368 pa_debug.reset_curr_function;
4369 RAISE;
4370 END Gen_Src_WP_Version_Id;
4371
4372
4373 /*==================================================================================
4374 This procedure is used to create the seeded view for the periodic budget or forcasts
4375 The selected amount types for the layout will be stored using this method.This will
4376 also be used to store the seeded amount types for the layouts.
4377
4378 06-Apr-2005 prachand Created as a part of WebAdi changes.
4379 Initial Creation
4380 ===================================================================================*/
4381
4382 PROCEDURE Create_amt_types (
4383 p_project_id IN pa_projects_all.project_id%TYPE
4384 ,p_fin_plan_type_id IN pa_fin_plan_types_b.fin_plan_type_id%TYPE
4385 ,p_plan_preference_code IN pa_proj_fp_options.fin_plan_preference_code%TYPE
4386 ,p_cost_layout_code IN pa_proj_fp_options.cost_layout_code%TYPE
4387 ,p_revenue_layout_code IN pa_proj_fp_options.revenue_layout_code%TYPE
4388 ,p_all_layout_code IN pa_proj_fp_options.all_layout_code%TYPE
4389 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
4390 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
4391 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
4392 ) IS
4393
4394 l_plan_class_code pa_fin_plan_types_b.plan_class_code%TYPE;
4395 l_cost_amount_types_tbl SYSTEM.pa_varchar2_30_tbl_type :=SYSTEM.pa_varchar2_30_tbl_type();
4396 l_rev_amount_types_tbl SYSTEM.pa_varchar2_30_tbl_type :=SYSTEM.pa_varchar2_30_tbl_type();
4397 l_all_amount_types_tbl SYSTEM.pa_varchar2_30_tbl_type :=SYSTEM.pa_varchar2_30_tbl_type();
4398 l_cost_layout_type_code VARCHAR2(30);
4399 l_revenue_layout_type_code VARCHAR2(30);
4400 l_all_layout_type_code VARCHAR2(30);
4401 l_debug_mode VARCHAR2(30);
4402 l_stage NUMBER := 100;
4403 l_module_name VARCHAR2(100) := 'pa.plsql.pa_proj_fp_options_pub';
4404 P_PA_DEBUG_MODE VARCHAR2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
4405 l_msg_count NUMBER :=0;
4406 l_msg_data VARCHAR2(2000);
4407 l_data VARCHAR2(2000);
4408 l_msg_index_out NUMBER;
4409 l_layout_name VARCHAR2(2000);
4410 l_return_status VARCHAR2(2000);
4411
4412 TYPE Dynamic_cur is REF CURSOR;
4413 layout_details_cur Dynamic_cur;
4414 l_sql VARCHAR(3000) := 'SELECT '||
4415 ' integrator_code ' ||
4416 ' FROM bne_layouts_b '||
4417 ' WHERE layout_code = :1 ' ||
4418 ' AND application_id = (SELECT application_id ' ||
4419 ' FROM FND_APPLICATION ' ||
4420 ' WHERE APPLICATION_SHORT_NAME = ''PA'') ' ; -- removed user_name as it not being used.
4421 l_integrator_code VARCHAR2(30);
4422
4423
4424
4425 BEGIN
4426
4427 PA_DEBUG.Set_Curr_Function( p_function => l_module_name,
4428 p_debug_mode => l_debug_mode );
4429
4430 IF P_PA_DEBUG_MODE = 'Y' THEN
4431 pa_debug.set_err_stack('PA_PROJ_FP_OPTIONS_PUB.Create_amt_types');
4432 fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
4433 l_debug_mode := NVL(l_debug_mode, 'Y');
4434 pa_debug.set_process('PLSQL','LOG',l_debug_mode);
4435 END IF;
4436
4437 x_return_status := FND_API.G_RET_STS_SUCCESS;
4438
4439 IF p_project_id IS NULL OR p_fin_plan_type_id IS NULL OR p_plan_preference_code IS NULL THEN
4440
4441 l_stage := 340;
4442 IF P_PA_DEBUG_MODE = 'Y' THEN
4443 pa_debug.g_err_stage := TO_CHAR(l_Stage)||': Err - projectid or plan types id or pref code id is NULL';
4444 pa_debug.write('Create_FP_Option: ' || l_module_name,pa_debug.g_err_stage,5);
4445 END IF;
4446 x_return_status := FND_API.G_RET_STS_ERROR;
4447 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
4448 p_msg_name => 'PA_FP_INV_PARAM_PASSED');
4449 PA_DEBUG.Reset_Curr_Function;
4450 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
4451 END IF;
4452
4453 --Delete the existing amount types for the project/plan types from pa_fp_proj_xl_amt_types
4454 DELETE FROM PA_FP_PROJ_XL_AMT_TYPES
4455 WHERE project_id = p_project_id
4456 AND fin_plan_type_id = p_fin_plan_type_id;
4457 --getting the plan class code
4458
4459 SELECT plan_class_code
4460 INTO l_plan_class_code
4461 FROM pa_fin_plan_types_b
4462 WHERE fin_plan_type_id = p_fin_plan_type_id;
4463
4464 IF l_debug_mode = 'Y' THEN
4465 pa_debug.g_err_stage:= 'p_cost_layout_code:: ' || p_cost_layout_code || '::p_revenue_layout_code::' || p_revenue_layout_code || '::p_all_layout_code::' || p_all_layout_code;
4466 pa_debug.write(l_module_name,pa_debug.g_err_stage,3);
4467 END IF;
4468
4469
4470 --populating the seeded views
4471
4472 IF (p_plan_preference_code = PA_FP_CONSTANTS_PKG.G_PREF_COST_ONLY OR
4473 p_plan_preference_code = PA_FP_CONSTANTS_PKG.G_PREF_COST_AND_REV_SEP) THEN
4474
4475 BEGIN
4476 IF p_cost_layout_code IS NULL THEN
4477
4478 x_return_status := FND_API.G_RET_STS_ERROR;
4479 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
4480 p_msg_name => 'PA_FP_INV_PARAM_PASSED');
4481 PA_DEBUG.Reset_Curr_Function;
4482 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
4483 ELSE
4484 OPEN layout_details_cur FOR l_sql USING p_cost_layout_code;
4485 FETCH layout_details_cur INTO l_integrator_code;
4486
4487 IF l_debug_mode = 'Y' THEN
4488 pa_debug.g_err_stage:= 'l_integrator_code' || l_integrator_code || '::l_layout_name::'|| l_layout_name;
4489 pa_debug.write(l_module_name,pa_debug.g_err_stage,3);
4490 END IF;
4491
4492
4493 IF l_integrator_code = 'FINPLAN_BUDGET_PERIODIC' THEN
4494 l_cost_layout_type_code := 'PERIODIC_BUDGET';
4495 ELSIF l_integrator_code = 'FINPLAN_BUDGET_NON_PERIODIC' THEN
4496 l_cost_layout_type_code := 'NON_PERIODIC_BUDGET';
4497 ELSIF l_integrator_code = 'FINPLAN_FORECAST_PERIODIC' THEN
4498 l_cost_layout_type_code := 'PERIODIC_FORECAST';
4499 ELSIF l_integrator_code = 'FINPLAN_FORECAST_NON_PERIODIC' THEN
4500 l_cost_layout_type_code := 'NON_PERIODIC_FORECAST';
4501 END IF;
4502
4503
4504 -- pa_fp_webadi_utils.get_layout_details
4505 -- (p_layout_code => p_cost_layout_code
4506 -- ,p_integrator_code => NULL
4507 -- ,x_layout_name => l_layout_name
4508 -- ,x_layout_type_code => l_cost_layout_type_code
4509 -- ,x_return_status => l_return_status
4510 -- ,x_msg_count => l_msg_count
4511 -- ,x_msg_data => l_msg_data );
4512 --
4513 --
4514 -- IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4515 -- raise PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
4516 -- END IF;
4517 END IF;
4518 IF l_cost_layout_type_code = 'PERIODIC_BUDGET' THEN
4519
4520 l_cost_amount_types_tbl.extend(3);
4521 l_cost_amount_types_tbl(1) := 'TOTAL_QTY';
4522 l_cost_amount_types_tbl(2) := 'TOTAL_RAW_COST';
4523 l_cost_amount_types_tbl(3) := 'TOTAL_BURDENED_COST';
4524
4525 ELSIF l_cost_layout_type_code = 'PERIODIC_FORECAST' THEN
4526
4527 l_cost_amount_types_tbl.extend(6);
4528 l_cost_amount_types_tbl(1) := 'ETC_QTY';
4529 l_cost_amount_types_tbl(2) := 'FCST_QTY';
4530 l_cost_amount_types_tbl(3) := 'ETC_RAW_COST';
4531 l_cost_amount_types_tbl(4) := 'FCST_RAW_COST';
4532 l_cost_amount_types_tbl(5) := 'ETC_BURDENED_COST';
4533 l_cost_amount_types_tbl(6) := 'FCST_BURDENED_COST';
4534
4535 END IF;
4536 END;
4537 END IF;
4538
4539 IF (p_plan_preference_code = PA_FP_CONSTANTS_PKG.G_PREF_REVENUE_ONLY OR
4540 p_plan_preference_code = PA_FP_CONSTANTS_PKG.G_PREF_COST_AND_REV_SEP) THEN
4541
4542 BEGIN
4543 IF p_revenue_layout_code IS NULL THEN
4544
4545 x_return_status := FND_API.G_RET_STS_ERROR;
4546 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
4547 p_msg_name => 'PA_FP_INV_PARAM_PASSED');
4548 PA_DEBUG.Reset_Curr_Function;
4549 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
4550 ELSE
4551 OPEN layout_details_cur FOR l_sql USING p_revenue_layout_code;
4552 FETCH layout_details_cur INTO l_integrator_code;
4553
4554 IF l_integrator_code = 'FINPLAN_BUDGET_PERIODIC' THEN
4555 l_revenue_layout_type_code := 'PERIODIC_BUDGET';
4556 ELSIF l_integrator_code = 'FINPLAN_BUDGET_NON_PERIODIC' THEN
4557 l_revenue_layout_type_code := 'NON_PERIODIC_BUDGET';
4558 ELSIF l_integrator_code = 'FINPLAN_FORECAST_PERIODIC' THEN
4559 l_revenue_layout_type_code := 'PERIODIC_FORECAST';
4560 ELSIF l_integrator_code = 'FINPLAN_FORECAST_NON_PERIODIC' THEN
4561 l_revenue_layout_type_code := 'NON_PERIODIC_FORECAST';
4562 END IF;
4563
4564 -- pa_fp_webadi_utils.get_layout_details
4565 -- (p_layout_code => p_revenue_layout_code
4566 -- ,p_integrator_code => NULL
4567 -- ,x_layout_name => l_layout_name
4568 -- ,x_layout_type_code => l_cost_layout_type_code
4569 -- ,x_return_status => l_return_status
4570 -- ,x_msg_count => l_msg_count
4571 -- ,x_msg_data => l_msg_data );
4572 --
4573 -- IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4574 -- raise PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
4575 -- END IF;
4576 END IF;
4577 IF l_revenue_layout_type_code = 'PERIODIC_BUDGET' THEN
4578
4579 l_rev_amount_types_tbl.extend(2);
4580 l_rev_amount_types_tbl(1) := 'TOTAL_QTY';
4581 l_rev_amount_types_tbl(2) := 'TOTAL_REV';
4582
4583 ELSIF l_cost_layout_type_code = 'PERIODIC_FORECAST' THEN
4584
4585 l_rev_amount_types_tbl.extend(4);
4586 l_rev_amount_types_tbl(1) := 'ETC_QTY';
4587 l_rev_amount_types_tbl(2) := 'FCST_QTY';
4588 l_rev_amount_types_tbl(3) := 'ETC_REVENUE';
4589 l_rev_amount_types_tbl(4) := 'FCST_REVENUE';
4590
4591 END IF;
4592 END;
4593 END IF;
4594
4595 IF p_plan_preference_code = PA_FP_CONSTANTS_PKG.G_PREF_COST_AND_REV_SAME THEN
4596
4597 BEGIN
4598 IF p_all_layout_code IS NULL THEN
4599
4600
4601 x_return_status := FND_API.G_RET_STS_ERROR;
4602 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
4603 p_msg_name => 'PA_FP_INV_PARAM_PASSED');
4604 PA_DEBUG.Reset_Curr_Function;
4605 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
4606 ELSE
4607
4608 OPEN layout_details_cur FOR l_sql USING p_all_layout_code;
4609 FETCH layout_details_cur INTO l_integrator_code;
4610
4611 IF l_debug_mode = 'Y' THEN
4612 pa_debug.g_err_stage:= 'p_all_layout_code :::::: ' || p_all_layout_code ;
4613 pa_debug.write(l_module_name,pa_debug.g_err_stage,3);
4614 pa_debug.g_err_stage:= 'l_layout_name :::::: ' || l_layout_name;
4615 pa_debug.write(l_module_name,pa_debug.g_err_stage,3);
4616 pa_debug.g_err_stage:= 'l_integrator_code :::::: ' || l_integrator_code;
4617 pa_debug.write(l_module_name,pa_debug.g_err_stage,3);
4618 END IF;
4619
4620
4621 IF l_integrator_code = 'FINPLAN_BUDGET_PERIODIC' THEN
4622 l_all_layout_type_code := 'PERIODIC_BUDGET';
4623 ELSIF l_integrator_code = 'FINPLAN_BUDGET_NON_PERIODIC' THEN
4624 l_all_layout_type_code := 'NON_PERIODIC_BUDGET';
4625 ELSIF l_integrator_code = 'FINPLAN_FORECAST_PERIODIC' THEN
4626 l_all_layout_type_code := 'PERIODIC_FORECAST';
4627 ELSIF l_integrator_code = 'FINPLAN_FORECAST_NON_PERIODIC' THEN
4628 l_all_layout_type_code := 'NON_PERIODIC_FORECAST';
4629 END IF;
4630
4631
4632 -- pa_fp_webadi_utils.get_layout_details
4633 -- (p_layout_code => p_all_layout_code
4634 -- ,p_integrator_code => NULL
4635 -- ,x_layout_name => l_layout_name
4636 -- ,x_layout_type_code => l_cost_layout_type_code
4637 -- ,x_return_status => l_return_status
4638 -- ,x_msg_count => l_msg_count
4639 -- ,x_msg_data => l_msg_data );
4640 --
4641 -- IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4642 -- raise PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
4643 -- END IF;
4644 END IF;
4645 IF l_all_layout_type_code = 'PERIODIC_BUDGET' THEN
4646
4647 l_all_amount_types_tbl.extend(4);
4648 l_all_amount_types_tbl(1) := 'TOTAL_QTY';
4649 l_all_amount_types_tbl(2) := 'TOTAL_RAW_COST';
4650 l_all_amount_types_tbl(3) := 'TOTAL_BURDENED_COST';
4651 l_all_amount_types_tbl(4) := 'TOTAL_REV';
4652
4653 ELSIF l_all_layout_type_code = 'PERIODIC_FORECAST' THEN
4654
4655 l_all_amount_types_tbl.extend(8);
4656 l_all_amount_types_tbl(1) := 'ETC_QTY';
4657 l_all_amount_types_tbl(2) := 'FCST_QTY';
4658 l_all_amount_types_tbl(3) := 'ETC_RAW_COST';
4659 l_all_amount_types_tbl(4) := 'FCST_RAW_COST';
4660 l_all_amount_types_tbl(5) := 'ETC_BURDENED_COST';
4661 l_all_amount_types_tbl(6) := 'FCST_BURDENED_COST';
4662 l_all_amount_types_tbl(7) := 'ETC_REVENUE';
4663 l_all_amount_types_tbl(8) := 'FCST_REVENUE';
4664
4665 END IF;
4666 END;
4667 END IF;
4668
4669
4670
4671 IF l_cost_amount_types_tbl.COUNT > 0 THEN
4672
4673 IF l_debug_mode = 'Y' THEN
4674 pa_debug.g_err_stage:= 'Inserting cost seed values into pa_proj_fp_xl_amounts ';
4675 pa_debug.write(l_module_name,pa_debug.g_err_stage,3);
4676 END IF;
4677 FORALL j IN l_cost_amount_types_tbl.FIRST..l_cost_amount_types_tbl.LAST
4678 INSERT INTO pa_fp_proj_xl_amt_types (
4679 project_id
4680 ,fin_plan_type_id
4681 ,option_type
4682 ,amount_type_code
4683 ,record_version_number
4684 ,last_update_date
4685 ,last_updated_by
4686 ,creation_date
4687 ,created_by
4688 ,last_update_login
4689 )
4690 VALUES
4691 ( p_project_id
4692 , p_fin_plan_type_id
4693 , PA_FP_CONSTANTS_PKG.G_ELEMENT_TYPE_COST
4694 , l_cost_amount_types_tbl(j)
4695 , 1
4696 , sysdate
4697 , fnd_global.user_id
4698 , sysdate
4699 , fnd_global.user_id
4700 , fnd_global.login_id );
4701 ELSE
4702
4703 IF l_debug_mode = 'Y' THEN
4704 pa_debug.g_err_stage:= 'Not a cost plan type';
4705 pa_debug.write(l_module_name,pa_debug.g_err_stage,3);
4706 END IF;
4707 END IF;
4708
4709 IF l_rev_amount_types_tbl.COUNT > 0 THEN
4710
4711 IF l_debug_mode = 'Y' THEN
4712 pa_debug.g_err_stage:= 'Inserting rev seed values into pa_proj_fp_xl_amounts ';
4713 pa_debug.write(l_module_name,pa_debug.g_err_stage,3);
4714 END IF;
4715 FORALL j IN l_rev_amount_types_tbl.FIRST..l_rev_amount_types_tbl.LAST
4716 INSERT INTO pa_fp_proj_xl_amt_types (
4717 project_id
4718 ,fin_plan_type_id
4719 ,option_type
4720 ,amount_type_code
4721 ,record_version_number
4722 ,last_update_date
4723 ,last_updated_by
4724 ,creation_date
4725 ,created_by
4726 ,last_update_login
4727 )
4728 VALUES
4729 ( p_project_id
4730 , p_fin_plan_type_id
4731 , PA_FP_CONSTANTS_PKG.G_ELEMENT_TYPE_REVENUE
4732 , l_rev_amount_types_tbl(j)
4733 , 1
4734 , sysdate
4735 , fnd_global.user_id
4736 , sysdate
4737 , fnd_global.user_id
4738 , fnd_global.login_id );
4739 ELSE
4740
4741 IF l_debug_mode = 'Y' THEN
4742 pa_debug.g_err_stage:= 'Not a rev plan type';
4743 pa_debug.write(l_module_name,pa_debug.g_err_stage,3);
4744 END IF;
4745 END IF;
4746
4747 IF l_all_amount_types_tbl.COUNT > 0 THEN
4748
4749 IF l_debug_mode = 'Y' THEN
4750 pa_debug.g_err_stage:= 'Inserting all seed values into pa_proj_fp_xl_amounts ';
4751 pa_debug.write(l_module_name,pa_debug.g_err_stage,3);
4752 END IF;
4753
4754 FORALL j IN l_all_amount_types_tbl.FIRST..l_all_amount_types_tbl.LAST
4755
4756 INSERT INTO pa_fp_proj_xl_amt_types (
4757 project_id
4758 ,fin_plan_type_id
4759 ,option_type
4760 ,amount_type_code
4761 ,record_version_number
4762 ,last_update_date
4763 ,last_updated_by
4764 ,creation_date
4765 ,created_by
4766 ,last_update_login
4767 )
4768 VALUES
4769 ( p_project_id
4770 , p_fin_plan_type_id
4771 , PA_FP_CONSTANTS_PKG.G_ELEMENT_TYPE_ALL
4772 , l_all_amount_types_tbl(j)
4773 , 1
4774 , sysdate
4775 , fnd_global.user_id
4776 , sysdate
4777 , fnd_global.user_id
4778 , fnd_global.login_id );
4779
4780 IF l_debug_mode = 'Y' THEN
4781 FOR j IN l_all_amount_types_tbl.FIRST..l_all_amount_types_tbl.LAST LOOP
4782 pa_debug.g_err_stage:= 'Inserting all seed values ' || l_all_amount_types_tbl(j);
4783 pa_debug.write(l_module_name,pa_debug.g_err_stage,3);
4784 END LOOP;
4785 END IF;
4786
4787
4788 ELSE
4789
4790 IF l_debug_mode = 'Y' THEN
4791 pa_debug.g_err_stage:= 'Not a cost and rev same plan type';
4792 pa_debug.write(l_module_name,pa_debug.g_err_stage,3);
4793 END IF;
4794 END IF;
4795 pa_debug.Reset_Curr_Function;
4796
4797 EXCEPTION
4798
4799 WHEN PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc THEN
4800 l_msg_count := FND_MSG_PUB.count_msg;
4801 IF l_msg_count = 1 THEN
4802 PA_INTERFACE_UTILS_PUB.get_messages
4803 (p_encoded => FND_API.G_TRUE
4804 ,p_msg_index => 1
4805 ,p_msg_count => l_msg_count
4806 ,p_msg_data => l_msg_data
4807 ,p_data => l_data
4808 ,p_msg_index_out => l_msg_index_out);
4809 pa_debug.g_err_stage := l_data;
4810 x_msg_data := l_data;
4811 x_msg_count := l_msg_count;
4812 ELSE
4813 x_msg_count := l_msg_count;
4814 END IF;
4815 IF p_pa_debug_mode = 'Y' THEN
4816 pa_debug.g_err_stage:='Invalid Arguments Passed';
4817 pa_debug.write('Create_amt_types: ' || l_module_name,pa_debug.g_err_stage,5);
4818 END IF;
4819 x_return_status := FND_API.G_RET_STS_ERROR;
4820 pa_debug.reset_curr_function;
4821 RAISE;
4822
4823 WHEN OTHERS THEN
4824 FND_MSG_PUB.add_exc_msg
4825 ( p_pkg_name => 'PA_PROJ_FP_OPTIONS_PUB' ||
4826 'Create_amt_types'
4827 ,p_procedure_name => PA_DEBUG.G_Err_Stack);
4828 pa_debug.g_err_stage := 'Unexpected error in Create_amt_types:';
4829 pa_debug.write('Create_amt_types: ' || l_module_name,pa_debug.g_err_stage,5);
4830 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4831 pa_debug.Reset_Curr_Function;
4832 RAISE;
4833
4834 END Create_amt_types;
4835
4836 /*==================================================================================
4837 This procedure is used to copy the amount types for the periodic budget or forcasts
4838 from an existing plan type The existing plan types amount types will be copied to the
4839 new project or plan type when a copy is done.
4840
4841 06-Apr-2005 prachand Created as a part of WebAdi changes.
4842 Initial Creation
4843 ===================================================================================*/
4844
4845 PROCEDURE copy_amt_types (
4846 p_source_project_id IN pa_projects_all.project_id%TYPE
4847 ,p_source_fin_plan_type_id IN pa_fin_plan_types_b.fin_plan_type_id%TYPE
4848 ,p_target_project_id IN pa_projects_all.project_id%TYPE
4849 ,p_target_fin_plan_type_id IN pa_fin_plan_types_b.fin_plan_type_id%TYPE
4850 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
4851 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
4852 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
4853 ) IS
4854
4855 l_stage NUMBER := 100;
4856 l_module_name VARCHAR2(100) := 'pa.plsql.pa_proj_fp_options_pub.copy_amt_types';
4857 P_PA_DEBUG_MODE VARCHAR2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
4858 --Start of variables used for debugging
4859 l_return_status VARCHAR2(1);
4860 l_msg_count NUMBER := 0;
4861 l_msg_data VARCHAR2(2000);
4862 l_data VARCHAR2(2000);
4863 l_msg_index_out NUMBER;
4864 l_debug_mode VARCHAR2(30);
4865 l_debug_level3 CONSTANT NUMBER :=3;
4866 l_debug_level5 CONSTANT NUMBER :=5;
4867
4868
4869 BEGIN
4870 PA_DEBUG.Set_Curr_Function( p_function => l_module_name,
4871 p_debug_mode => l_debug_mode );
4872
4873 x_msg_count := 0;
4874 x_return_status := FND_API.G_RET_STS_SUCCESS;
4875
4876 fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
4877 l_debug_mode := NVL(l_debug_mode, 'Y');
4878
4879 IF l_debug_mode = 'Y' THEN
4880 pa_debug.g_err_stage:='Validating input parameters';
4881 pa_debug.write( l_module_name,pa_debug.g_err_stage,l_debug_level3);
4882 END IF;
4883
4884
4885 IF p_source_project_id IS NULL OR
4886 p_source_fin_plan_type_id IS NULL OR
4887 p_target_project_id IS NULL OR
4888 p_target_fin_plan_type_id IS NULL THEN
4889
4890 IF l_debug_mode = 'Y' THEN
4891 pa_debug.g_err_stage:='p_source_project_id is '||p_source_project_id;
4892 pa_debug.write( l_module_name,pa_debug.g_err_stage,l_debug_level5);
4893 pa_debug.g_err_stage:='p_source_fin_plan_type_id is '||p_source_fin_plan_type_id;
4894 pa_debug.write( l_module_name,pa_debug.g_err_stage,l_debug_level5);
4895 pa_debug.g_err_stage:='p_target_project_id is '||p_target_project_id;
4896 pa_debug.write( l_module_name,pa_debug.g_err_stage,l_debug_level5);
4897 pa_debug.g_err_stage:='p_target_fin_plan_type_id is '||p_target_fin_plan_type_id;
4898 pa_debug.write( l_module_name,pa_debug.g_err_stage,l_debug_level5);
4899 END IF;
4900
4901 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
4902 p_msg_name => 'PA_FP_INV_PARAM_PASSED');
4903
4904 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
4905
4906 END IF;
4907
4908 INSERT INTO PA_FP_PROJ_XL_AMT_TYPES(
4909 project_id
4910 ,fin_plan_type_id
4911 ,option_type
4912 ,amount_type_code
4913 ,record_version_number
4914 ,last_update_date
4915 ,last_updated_by
4916 ,creation_date
4917 ,created_by
4918 ,last_update_login)
4919 SELECT p_target_project_id
4920 ,p_target_fin_plan_type_id
4921 ,option_type
4922 ,amount_type_code
4923 ,1
4924 ,sysdate
4925 ,fnd_global.user_id
4926 ,sysdate
4927 ,fnd_global.user_id
4928 ,fnd_global.user_id
4929 FROM PA_FP_PROJ_XL_AMT_TYPES WHERE
4930 project_id = p_source_project_id AND
4931 fin_plan_type_id = p_source_fin_plan_type_id ;
4932
4933 PA_DEBUG.Reset_Curr_Function;
4934
4935 EXCEPTION
4936
4937 WHEN PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc THEN
4938 l_msg_count := FND_MSG_PUB.count_msg;
4939 IF l_msg_count = 1 THEN
4940 PA_INTERFACE_UTILS_PUB.get_messages
4941 (p_encoded => FND_API.G_TRUE
4942 ,p_msg_index => 1
4943 ,p_msg_count => l_msg_count
4944 ,p_msg_data => l_msg_data
4945 ,p_data => l_data
4946 ,p_msg_index_out => l_msg_index_out);
4947
4948 x_msg_data := l_data;
4949 x_msg_count := l_msg_count;
4950 ELSE
4951 x_msg_count := l_msg_count;
4952 END IF;
4953 pa_debug.reset_curr_function();
4954
4955 WHEN OTHERS THEN
4956 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4957 x_msg_count := 1;
4958 x_msg_data := SQLERRM;
4959
4960 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'pa_proj_fp_options_pub'
4961 ,p_procedure_name => 'copy_amt_types');
4962
4963 IF l_debug_mode = 'Y' THEN
4964 pa_debug.g_err_stage:='Unexpected Error'||SQLERRM;
4965 pa_debug.write( l_module_name,pa_debug.g_err_stage,l_debug_level5);
4966 END IF;
4967 -- reset curr function
4968 pa_debug.Reset_Curr_Function();
4969 RAISE;
4970 END copy_amt_types;
4971
4972 -- This procedure is used to update the amount types for the periodic budget or forcasts of an existing plan type
4973 PROCEDURE update_amt_types (
4974 p_project_id IN pa_projects_all.project_id%TYPE
4975 ,p_fin_plan_type_id IN pa_fin_plan_types_b.fin_plan_type_id%TYPE
4976 ,p_add_cost_amt_types_tbl IN SYSTEM.pa_varchar2_30_tbl_type
4977 ,p_del_cost_amt_types_tbl IN SYSTEM.pa_varchar2_30_tbl_type
4978 ,p_add_rev_amt_types_tbl IN SYSTEM.pa_varchar2_30_tbl_type
4979 ,p_del_rev_amt_types_tbl IN SYSTEM.pa_varchar2_30_tbl_type
4980 ,p_add_all_amt_types_tbl IN SYSTEM.pa_varchar2_30_tbl_type
4981 ,p_del_all_amt_types_tbl IN SYSTEM.pa_varchar2_30_tbl_type
4982 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
4983 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
4984 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
4985 ) IS
4986
4987 l_stage NUMBER := 100;
4988 l_module_name VARCHAR2(100) := 'pa.plsql.pa_proj_fp_options_pub';
4989 P_PA_DEBUG_MODE VARCHAR2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
4990 --Start of variables used for debugging
4991 l_return_status VARCHAR2(1);
4992 l_msg_count NUMBER := 0;
4993 l_msg_data VARCHAR2(2000);
4994 l_data VARCHAR2(2000);
4995 l_msg_index_out NUMBER;
4996 l_debug_mode VARCHAR2(30);
4997 l_debug_level3 CONSTANT NUMBER :=3;
4998 l_debug_level5 CONSTANT NUMBER :=5;
4999
5000
5001
5002
5003 BEGIN
5004 PA_DEBUG.Set_Curr_Function( p_function => l_module_name,
5005 p_debug_mode => l_debug_mode );
5006
5007 x_msg_count := 0;
5008 x_return_status := FND_API.G_RET_STS_SUCCESS;
5009
5010 fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
5011 l_debug_mode := NVL(l_debug_mode, 'Y');
5012
5013 IF l_debug_mode = 'Y' THEN
5014 pa_debug.g_err_stage:='Validating input parameters';
5015 pa_debug.write( l_module_name,pa_debug.g_err_stage,l_debug_level3);
5016 END IF;
5017
5018
5019 IF p_project_id IS NULL OR
5020 p_fin_plan_type_id IS NULL THEN
5021
5022 IF l_debug_mode = 'Y' THEN
5023 pa_debug.g_err_stage:='p_project_id is '||p_project_id;
5024 pa_debug.write( l_module_name,pa_debug.g_err_stage,l_debug_level5);
5025 pa_debug.g_err_stage:='p_fin_plan_type_id is '||p_fin_plan_type_id;
5026 pa_debug.write( l_module_name,pa_debug.g_err_stage,l_debug_level5);
5027 END IF;
5028
5029 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
5030 p_msg_name => 'PA_FP_INV_PARAM_PASSED');
5031
5032 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
5033
5034 END IF;
5035
5036 IF p_add_cost_amt_types_tbl.COUNT > 0 THEN
5037
5038 IF l_debug_mode = 'Y' THEN
5039 pa_debug.g_err_stage:= 'Inserting cost amount types into pa_proj_fp_xl_amounts ';
5040 pa_debug.write(l_module_name,pa_debug.g_err_stage,3);
5041 END IF;
5042 FORALL j in p_add_cost_amt_types_tbl.FIRST..p_add_cost_amt_types_tbl.LAST
5043
5044 INSERT into pa_fp_proj_xl_amt_types (
5045 project_id
5046 ,fin_plan_type_id
5047 ,option_type
5048 ,amount_type_code
5049 ,record_version_number
5050 ,last_update_date
5051 ,last_updated_by
5052 ,creation_date
5053 ,created_by
5054 ,last_update_login )
5055 VALUES
5056 ( p_project_id
5057 ,p_fin_plan_type_id
5058 ,PA_FP_CONSTANTS_PKG.G_ELEMENT_TYPE_COST
5059 ,p_add_cost_amt_types_tbl(j)
5060 ,1
5061 ,sysdate
5062 ,fnd_global.user_id
5063 ,sysdate
5064 ,fnd_global.user_id
5065 ,fnd_global.user_id );
5066 END IF;
5067
5068 IF p_del_cost_amt_types_tbl.COUNT > 0 THEN
5069
5070 IF l_debug_mode = 'Y' THEN
5071 pa_debug.g_err_stage:= 'Deleting cost amount types from pa_proj_fp_xl_amounts ';
5072 pa_debug.write(l_module_name,pa_debug.g_err_stage,3);
5073 END IF;
5074 FORALL j in p_del_cost_amt_types_tbl.FIRST..p_del_cost_amt_types_tbl.LAST
5075
5076 DELETE FROM pa_fp_proj_xl_amt_types WHERE
5077 project_id = p_project_id AND
5078 fin_plan_type_id = p_fin_plan_type_id AND
5079 option_type = PA_FP_CONSTANTS_PKG.G_ELEMENT_TYPE_COST AND
5080 amount_type_code = p_del_cost_amt_types_tbl(j) ;
5081 END IF;
5082
5083 IF p_add_rev_amt_types_tbl.COUNT > 0 THEN
5084
5085 IF l_debug_mode = 'Y' THEN
5086 pa_debug.g_err_stage:= 'Inserting rev amount types into pa_proj_fp_xl_amounts ';
5087 pa_debug.write(l_module_name,pa_debug.g_err_stage,3);
5088 END IF;
5089 FORALL j in p_add_rev_amt_types_tbl.FIRST..p_add_rev_amt_types_tbl.LAST
5090
5091 INSERT into pa_fp_proj_xl_amt_types (
5092 project_id
5093 ,fin_plan_type_id
5094 ,option_type
5095 ,amount_type_code
5096 ,record_version_number
5097 ,last_update_date
5098 ,last_updated_by
5099 ,creation_date
5100 ,created_by
5101 ,last_update_login )
5102 VALUES
5103 ( p_project_id
5104 ,p_fin_plan_type_id
5105 ,PA_FP_CONSTANTS_PKG.G_ELEMENT_TYPE_REVENUE
5106 ,p_add_rev_amt_types_tbl(j)
5107 ,1
5108 ,sysdate
5109 ,fnd_global.user_id
5110 ,sysdate
5111 ,fnd_global.user_id
5112 ,fnd_global.user_id );
5113 END IF;
5114
5115 IF p_del_rev_amt_types_tbl.COUNT > 0 THEN
5116 IF l_debug_mode = 'Y' THEN
5117 pa_debug.g_err_stage:= 'Deleting rev amount types from pa_proj_fp_xl_amounts ';
5118 pa_debug.write(l_module_name,pa_debug.g_err_stage,3);
5119 END IF;
5120 FORALL j in p_del_rev_amt_types_tbl.FIRST..p_del_rev_amt_types_tbl.LAST
5121
5122 DELETE FROM pa_fp_proj_xl_amt_types WHERE
5123 project_id = p_project_id AND
5124 fin_plan_type_id = p_fin_plan_type_id AND
5125 option_type = PA_FP_CONSTANTS_PKG.G_ELEMENT_TYPE_REVENUE AND
5126 amount_type_code = p_del_rev_amt_types_tbl(j) ;
5127 END IF;
5128
5129 IF p_add_all_amt_types_tbl.COUNT > 0 THEN
5130 IF l_debug_mode = 'Y' THEN
5131 pa_debug.g_err_stage:= 'Inserting all amount types into pa_proj_fp_xl_amounts ';
5132 pa_debug.write(l_module_name,pa_debug.g_err_stage,3);
5133 END IF;
5134 IF l_debug_mode = 'Y' THEN
5135 FOR j in p_add_all_amt_types_tbl.FIRST..p_add_all_amt_types_tbl.LAST LOOP
5136 pa_debug.g_err_stage:= 'Inserting value:::: ' || p_add_all_amt_types_tbl(j) ;
5137 pa_debug.write(l_module_name,pa_debug.g_err_stage,3);
5138 END LOOP;
5139 END IF;
5140
5141 FORALL j in p_add_all_amt_types_tbl.FIRST..p_add_all_amt_types_tbl.LAST
5142
5143 INSERT into pa_fp_proj_xl_amt_types (
5144 project_id
5145 ,fin_plan_type_id
5146 ,option_type
5147 ,amount_type_code
5148 ,record_version_number
5149 ,last_update_date
5150 ,last_updated_by
5151 ,creation_date
5152 ,created_by
5153 ,last_update_login )
5154 VALUES
5155 ( p_project_id
5156 ,p_fin_plan_type_id
5157 ,PA_FP_CONSTANTS_PKG.G_ELEMENT_TYPE_ALL
5158 ,p_add_all_amt_types_tbl(j)
5159 ,1
5160 ,sysdate
5161 ,fnd_global.user_id
5162 ,sysdate
5163 ,fnd_global.user_id
5164 ,fnd_global.user_id );
5165
5166
5167 END IF;
5168
5169 IF p_del_all_amt_types_tbl.COUNT > 0 THEN
5170 IF l_debug_mode = 'Y' THEN
5171 pa_debug.g_err_stage:= 'Deleting all amount from pa_proj_fp_xl_amounts ';
5172 pa_debug.write(l_module_name,pa_debug.g_err_stage,3);
5173 END IF;
5174 FORALL j in p_del_all_amt_types_tbl.FIRST..p_del_all_amt_types_tbl.LAST
5175
5176 DELETE FROM pa_fp_proj_xl_amt_types WHERE
5177 project_id = p_project_id AND
5178 fin_plan_type_id = p_fin_plan_type_id AND
5179 option_type = PA_FP_CONSTANTS_PKG.G_ELEMENT_TYPE_ALL AND
5180 amount_type_code = p_del_all_amt_types_tbl(j) ;
5181 END IF;
5182 PA_DEBUG.Reset_Curr_Function;
5183 EXCEPTION
5184
5185 WHEN PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc THEN
5186 l_msg_count := FND_MSG_PUB.count_msg;
5187 IF l_msg_count = 1 THEN
5188 PA_INTERFACE_UTILS_PUB.get_messages
5189 (p_encoded => FND_API.G_TRUE
5190 ,p_msg_index => 1
5191 ,p_msg_count => l_msg_count
5192 ,p_msg_data => l_msg_data
5193 ,p_data => l_data
5194 ,p_msg_index_out => l_msg_index_out);
5195
5196 x_msg_data := l_data;
5197 x_msg_count := l_msg_count;
5198 ELSE
5199 x_msg_count := l_msg_count;
5200 END IF;
5201 pa_debug.reset_curr_function();
5202
5203 WHEN OTHERS THEN
5204 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5205 x_msg_count := 1;
5206 x_msg_data := SQLERRM;
5207
5208 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'pa_proj_fp_options_pub'
5209 ,p_procedure_name => 'update_amt_types');
5210
5211 IF l_debug_mode = 'Y' THEN
5212 pa_debug.g_err_stage:='Unexpected Error'||SQLERRM;
5213 pa_debug.write( l_module_name,pa_debug.g_err_stage,l_debug_level5);
5214 END IF;
5215 -- reset curr function
5216 pa_debug.Reset_Curr_Function();
5217 RAISE;
5218
5219 END update_amt_types;
5220
5221 END PA_PROJ_FP_OPTIONS_PUB;