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