[Home] [Help]
PACKAGE BODY: APPS.PA_FIN_PLAN_PVT
Source
1 PACKAGE BODY pa_fin_plan_pvt AS
2 /* $Header: PAFPPVTB.pls 120.17.12020000.4 2013/04/04 07:24:23 djambhek ship $
3 Start of Comments
4 Package name : PA_FIN_PLAN_UTILS
5 Purpose : utility API's for Org Forecast pages
6 History :
7 NOTE :
8 End of Comments
9 */
10
11 g_module_name VARCHAR2(100) := 'pa.plsql.PA_FIN_PLAN_PVT';
12 Delete_Ver_Exc_PVT EXCEPTION;
13
14 -- PROCEDURE lock_unlock_version
15 -- created 8/27/02
16 -- This procedure locks/unlocks a budget version, based on the value of p_action.
17 -- If p_action = null, then this procedure acts as a lock toggle (if the version was
18 -- originally locked, it would unlock the version)
19 -- If p_person_id is null, then it will find the person id based on p_user_id.
20 -- This procedure assumes that a person can only unlock the versions he/she has locked.
21 P_PA_DEBUG_MODE varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
22
23 PROCEDURE lock_unlock_version
24 (p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE,
25 p_record_version_number IN pa_budget_versions.record_version_number%TYPE,
26 p_action IN VARCHAR2, -- 'L' for lock, 'U' for unlock
27 p_user_id IN NUMBER,
28 p_person_id IN NUMBER, -- can be null
29 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
30 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
31 x_msg_data OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
32 p_unlock_locked_ver_flag IN VARCHAR2) IS
33
34 cursor bv_csr is
35 select locked_by_person_id
36 from pa_budget_versions
37 where budget_version_id = p_budget_version_id;
38 bv_rec bv_csr%ROWTYPE;
39
40 l_locked_by_person_id pa_budget_versions.locked_by_person_id%TYPE; -- for lock/unlock toggle
41 l_person_id NUMBER(15);
42 l_resource_id NUMBER(15);
43 l_resource_name per_all_people_f.full_name%TYPE; -- VARCHAR2(80); for bug # 2933777
44
45 -- error handling variables
46 l_valid_flag VARCHAR2(1);
47 l_debug_mode VARCHAR2(30);
48 l_msg_count NUMBER := 0;
49 l_data VARCHAR2(2000);
50 l_msg_data VARCHAR2(2000);
51 l_error_msg_code VARCHAR2(30);
52 l_msg_index_out NUMBER;
53 l_return_status VARCHAR2(2000);
54
55 -- Error messages are stacked for AMG. Hence it is necessary that the
56 -- error message in the stacke are not cleared. Using the variable
57 -- l_initial_msg_count to do the comparisions and it stores the number
58 -- of error messages on stack at the start of this API.
59 l_initial_msg_count NUMBER;
60
61 BEGIN
62 --FND_MSG_PUB.initialize;
63 l_initial_msg_count := FND_MSG_PUB.count_msg;
64
65 IF P_PA_DEBUG_MODE = 'Y' THEN
66 pa_debug.init_err_stack('PA_FIN_PLAN_PUB.lock_unlock_version');
67 END IF;
68 x_msg_count := 0;
69 SAVEPOINT PA_FP_LOCK_UNLOCK; --bug#10639908
70 /* CHECK FOR BUSINESS RULES VIOLATIONS */
71 /* check for null budget_version_id */
72 if p_budget_version_id is NULL then
73 x_return_status := FND_API.G_RET_STS_ERROR;
74 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
75 p_msg_name => 'PA_FP_NO_PLAN_VERSION');
76 end if;
77 /* check to see if the budget version we're updating has */
78 /* been updated by someone else already */
79 PA_FIN_PLAN_UTILS.Check_Record_Version_Number
80 (p_unique_index => p_budget_version_id,
81 p_record_version_number => p_record_version_number,
82 x_valid_flag => l_valid_flag,
83 x_return_status => l_return_status,
84 x_error_msg_code => l_error_msg_code);
85 if x_return_status = FND_API.G_RET_STS_ERROR then
86 IF P_PA_DEBUG_MODE = 'Y' THEN
87 pa_debug.write_file('lock_unlock_version: ' || 'record version number error ');
88 END IF;
89 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
90 p_msg_name => l_error_msg_code);
91 end if;
92
93 /* if we do not have a person_id, make sure that we have a valid user_id */
94 /* In the following API, l_resource_name is obtained since it is an OUT
95 variable. There is no business logic based on l_resource_name. */
96
97 if p_person_id is null then
98 PA_COMP_PROFILE_PUB.GET_USER_INFO
99 (p_user_id => p_user_id,
100 x_person_id => l_person_id,
101 x_resource_id => l_resource_id,
102 x_resource_name => l_resource_name);
103 if l_person_id is null then
104 x_return_status := FND_API.G_RET_STS_ERROR;
105 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
106 p_msg_name => 'PA_FP_BAD_USER_ID');
107 end if; -- error with p_user_id
108 else
109 l_person_id := p_person_id;
110 end if;
111
112 /* check to see if the user is trying to unlock a file that is currently */
113 /* locked by another user. the user can unlock only those files he/she */
114 /* has locked. */
115 if nvl(p_unlock_locked_ver_flag,'N') = 'N' /* Bug 5179225 User can also unlock those versions which are unlocked by Others, provided that he has a privilege UNLOCK_ANY_STRUCTURE*/
116 then
117 open bv_csr;
118 fetch bv_csr into bv_rec;
119 if not bv_csr%NOTFOUND then
120 --if p_action = 'U' and not (bv_rec.locked_by_person_id = l_person_id) then
121 --Irrespective of the action, if the locked_by_person_id and l_person_id
122 --are different, an error should be thrown. AMG UT2
123 if not ( nvl(bv_rec.locked_by_person_id,l_person_id) = l_person_id ) then
124 x_return_status := FND_API.G_RET_STS_ERROR;
125 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
126 p_msg_name => 'PA_FP_LOCKED_BY_USER',
127 p_token1 => 'USERNAME',
128 p_value1 => pa_fin_plan_utils.get_person_name(bv_rec.locked_by_person_id));
129 end if;
130 end if;
131
132 /* Bug 5349962: R12 Perf Fix: Putting a close cursor statement. Closing the cursor only when it returns any row. */
133 close bv_csr;
134 /* Bug 5349962 */
135
136 end if;
137
138 /* If There are ANY Busines Rules Violations , Then Do NOT Proceed: RETURN */
139 l_msg_count := FND_MSG_PUB.count_msg;
140 --if l_msg_count > 0 then
141 if l_msg_count > l_initial_msg_count then
142 if l_msg_count = 1 then
143 PA_INTERFACE_UTILS_PUB.get_messages
144 (p_encoded => FND_API.G_TRUE,
145 p_msg_index => 1,
146 p_msg_count => l_msg_count,
147 p_msg_data => l_msg_data,
148 p_data => l_data,
149 p_msg_index_out => l_msg_index_out);
150 x_msg_data := l_data;
151 x_msg_count := l_msg_count;
152 else
153 x_msg_count := l_msg_count;
154 end if;
155 IF p_pa_debug_mode = 'Y' THEN
156 pa_debug.reset_err_stack;
157 END IF;
158 return;
159 end if;
160
161 /* IF NO BUSINESS RULES VIOLATIONS, PROCEED WITH LOCK/UNLOCK */
162 IF P_PA_DEBUG_MODE = 'Y' THEN
163 pa_debug.write_file('lock_unlock_version: ' || 'no business rules violations');
164 END IF;
165 x_return_status := FND_API.G_RET_STS_SUCCESS;
166 --if l_msg_count = 0 then
167 if l_msg_count = l_initial_msg_count then
168 --SAVEPOINT PA_FP_LOCK_UNLOCK;
169 -- LOCK the version
170 if p_action = 'L' then
171 update pa_budget_versions
172 set locked_by_person_id = l_person_id,
173 record_version_number = p_record_version_number + 1,
174 last_update_date=SYSDATE,
175 last_updated_by=FND_GLOBAL.user_id,
176 last_update_login=FND_GLOBAL.login_id
177 where budget_version_id = p_budget_version_id;
178 -- UNLOCK the version
179 elsif p_action = 'U' then
180 update pa_budget_versions
181 set locked_by_person_id = null,
182 record_version_number = p_record_version_number + 1,
183 last_update_date=SYSDATE,
184 last_updated_by=FND_GLOBAL.user_id,
185 last_update_login=FND_GLOBAL.login_id
186 where budget_version_id = p_budget_version_id;
187 -- if p_action neither 'L' nor 'U', then we assume it's a lock/unlock toggle
188 else
189 select nvl(locked_by_person_id, -1)
190 into l_locked_by_person_id
191 from pa_budget_versions
192 where budget_version_id = p_budget_version_id;
193 -- it was unlocked before: UNLOCK --> LOCK
194 if l_locked_by_person_id = -1 then
195 update pa_budget_versions
196 set locked_by_person_id = l_person_id,
197 record_version_number = p_record_version_number + 1,
198 last_update_date=SYSDATE,
199 last_updated_by=FND_GLOBAL.user_id,
200 last_update_login=FND_GLOBAL.login_id
201 where budget_version_id = p_budget_version_id;
202 -- it was locked before: LOCK --> UNLOCK
203 else
204 update pa_budget_versions
205 set locked_by_person_id = null,
206 record_version_number = p_record_version_number + 1,
207 last_update_date=SYSDATE,
208 last_updated_by=FND_GLOBAL.user_id,
209 last_update_login=FND_GLOBAL.login_id
210 where budget_version_id = p_budget_version_id;
211 end if;
212 end if; -- p_action value
213 return;
214 end if;
215
216 EXCEPTION
217 when others then
218 rollback to PA_FP_LOCK_UNLOCK;
219 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
220 x_msg_count := 1;
221 x_msg_data := SQLERRM;
222 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_FIN_PLAN_PUB',
223 p_procedure_name => 'lock_unlock_version');
224 IF p_pa_debug_mode = 'Y' THEN
225 pa_debug.reset_err_stack;
226 END IF;
227 raise FND_API.G_EXC_UNEXPECTED_ERROR;
228 END lock_unlock_version;
229
230
231
232 /* ------------------------------------------------------------------------ */
233
234 -- 11/16/02 dlai: bug fix 2668857 - when joining pa_projects_all with
235 -- pa_project_types_all, need to join based on org_id as well
236
237 -- 12-FEB-04 jwhite - Bug 3440026
238 -- Commented obsolete call to
239 -- PA_BUDGET_UTILS.summerize_project_totals
240 -- for Baseline_FinPlan procedure.
241
242 -- 21-Sep-04 Raja - Bug 3841942
243 -- In FP M for creation of none time phased budgets transaction start/end dates
244 -- are not compulsory. So, the logic to synch budget lines data with changed
245 -- transaction start/end dates is not applicable. Commented out the required code
246
247
248 --
249 -- 12-SEP-05 jwhite - Bug 4583454. Restore FP Support
250 -- PSI/summarization.
251 -- Minor Change: Restored insert to
252 -- pa_resource_list_members and
253 -- pa_resource_list_uses.
254
255
256 PROCEDURE Baseline_FinPlan
257 (p_project_id IN pa_budget_versions.project_id%TYPE,
258 p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE,
259 p_record_version_number IN pa_budget_versions.record_version_number%TYPE,
260 p_orig_budget_version_id IN pa_budget_versions.budget_version_id%TYPE,
261 p_orig_record_version_number IN pa_budget_versions.record_version_number%TYPE,
262 p_verify_budget_rules IN VARCHAR2,
263 x_fc_version_created_flag OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
264 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
265 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
266 x_msg_data OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
267
268 -- error handling variables
269 l_valid_flag VARCHAR2(1); -- for PA_FIN_PLAN_UTILS.Check_Record_Version_Number
270 l_msg_count NUMBER := 0;
271 l_err_msg_count NUMBER := 0;
272 l_data VARCHAR2(2000);
273 l_msg_data VARCHAR2(2000);
274 l_error_msg_code VARCHAR2(30);
275 l_msg_index_out NUMBER;
276 l_return_status VARCHAR2(2000);
277 l_err_stage VARCHAR2(200);
278 l_valid1_flag VARCHAR2(1);
279 l_valid2_flag VARCHAR2(1);
280 x_err_code NUMBER;
281 x_err_stage VARCHAR2(2000);
282 x_err_stack VARCHAR2(2000);
283
284 -- local variables
285 l_budget_type_code pa_budget_versions.budget_type_code%TYPE;
286 l_project_type_class_code pa_project_types.project_type_class_code%TYPE;
287 l_version_type pa_budget_versions.version_type%TYPE;
288 l_ac_flag VARCHAR2(1);
289 l_ar_flag VARCHAR2(1);
290 l_resource_list_id pa_budget_versions.resource_list_id%TYPE;
291 l_time_phased_type_code pa_budget_entry_methods.time_phased_type_code%TYPE;
292 l_fin_plan_level_code VARCHAR2(30);
293 l_fin_plan_class_code pa_fin_plan_types_b.plan_class_code%TYPE;
294 l_fin_plan_type_id pa_proj_fp_options.fin_plan_type_id%TYPE;
295 l_budget_entry_method_code pa_budget_entry_methods.budget_entry_method_code%TYPE;
296 l_entry_level_code pa_budget_entry_methods.entry_level_code%TYPE;
297 l_pm_product_code pa_budget_versions.pm_product_code%TYPE;
298 l_workflow_is_used VARCHAR2(1); -- flag to determine if workflow is used
299 l_warnings_only_flag VARCHAR2(1);
300 l_funding_level varchar2(2) default NULL;
301 l_mark_as_original varchar2(30) default 'N';
302 l_created_by pa_budget_versions.created_by%TYPE;
303 x_resource_list_assgmt_id NUMBER;
304 v_emp_id NUMBER; -- employee id
305 v_project_start_date date;
306 v_project_completion_date date;
307 --The following varible is added to make program consistent with the
308 --changed copy_version procedure prototype
309 l_target_version_id PA_BUDGET_VERSIONS.budget_version_id%TYPE;
310 l_fc_version_id1 PA_BUDGET_VERSIONS.budget_version_id%TYPE;
311 l_fc_version_id2 PA_BUDGET_VERSIONS.budget_version_id%TYPE;
312 l_ci_id_tbl pa_plsql_datatypes.idTabTyp;
313 l_fc_version_type PA_BUDGET_VERSIONS.version_type%TYPE;
314 l_version_name PA_BUDGET_VERSIONS.version_name%TYPE;
315 l_base_line_ver_exists VARCHAR2(1);
316 l_curr_work_ver_id pa_budget_versions.budget_version_id%TYPE;
317 l_curr_work_fp_opt_id pa_proj_fp_options.proj_fp_options_id%TYPE;
318 l_fp_options_id pa_proj_fp_options.proj_fp_options_id%TYPE;
319 l_auto_baseline_project pa_projects_all.baseline_funding_flag%TYPE;
320
321 l_fc_plan_type_ids_tbl SYSTEM.pa_num_tbl_type DEFAULT SYSTEM.pa_num_tbl_type();
322 l_fc_pt_pref_code_tbl SYSTEM.pa_varchar2_30_tbl_type DEFAULT SYSTEM.pa_varchar2_30_tbl_type();
323 l_primary_cost_fcst_flag_tbl SYSTEM.pa_varchar2_1_tbl_type DEFAULT SYSTEM.pa_varchar2_1_tbl_type();
324 l_primary_rev_fcst_flag_tbl SYSTEM.pa_varchar2_1_tbl_type DEFAULT SYSTEM.pa_varchar2_1_tbl_type();
325 l_dummy VARCHAR2(1);
326 l_module_name VARCHAR2(100):='PAFPPVTB.Baseline_FinPlan';
327 l_tmp_incl_method_code_tbl SYSTEM.pa_varchar2_30_tbl_type DEFAULT SYSTEM.pa_varchar2_30_tbl_type(); -- Bug 3756079
328 l_temp_ci_id_tbl SYSTEM.pa_num_tbl_type DEFAULT SYSTEM.pa_num_tbl_type(); -- Bug 3756079
329 l_version_type_tbl SYSTEM.pa_varchar2_30_tbl_type DEFAULT SYSTEM.pa_varchar2_30_tbl_type(); -- Bug 3756079
330 l_cw_creation_date_tbl SYSTEM.PA_DATE_TBL_TYPE DEFAULT SYSTEM.PA_DATE_TBL_TYPE(); -- Bug 3756079
331 l_bl_creation_date_tbl SYSTEM.PA_DATE_TBL_TYPE DEFAULT SYSTEM.PA_DATE_TBL_TYPE(); -- Bug 3756079
332
333
334
335 -- Bug 4583454, jwhite, 12-SEP-05 -------------------
336 l_migration_code pa_resource_lists_all_bg.migration_code%TYPE := NULL;
337 l_uncategorized_flag pa_resource_lists_all_bg.uncategorized_flag%TYPE := NULL;
338
339
340
341 CURSOR c_chk_rej_codes
342 IS
343 SELECT 'Y'
344 FROM DUAL
345 WHERE EXISTS (SELECT 1
346 FROM pa_budget_lines pbl
347 WHERE pbl.budget_version_id = p_budget_version_id
348 AND( pbl.cost_rejection_code IS NOT NULL
349 OR pbl.revenue_rejection_code IS NOT NULL
350 OR pbl.burden_rejection_code IS NOT NULL
351 OR pbl.other_rejection_code IS NOT NULL
352 OR pbl.pc_cur_conv_rejection_code IS NOT NULL
353 OR pbl.pfc_cur_conv_rejection_code IS NOT NULL));
354
355 BEGIN
356 FND_MSG_PUB.initialize;
357 IF p_pa_debug_mode = 'Y' THEN
358 pa_debug.init_err_stack('PA_FIN_PLAN_PUB.Baseline_FinPlan');
359 END IF;
360 l_created_by:=FND_GLOBAL.user_id;
361 l_msg_count := 0;
362
363 ------------ CHECK FOR BUSINESS RULES VIOLATIONS --------------
364 -- 1. RECORD VERSION NUMBER -- If record_version_number of p_budget_version_id
365 -- has changed, return error:
366 -- check to see if the old current baselined budget version has
367 -- been updated by someone else already
368 -- if p_orig_budget_version_id = null then there is currently not a baselined version
369 -- in this case, ignore this check
370 if p_orig_budget_version_id IS NOT null then
371 PA_FIN_PLAN_UTILS.Check_Record_Version_Number
372 (p_unique_index => p_orig_budget_version_id,
373 p_record_version_number => p_orig_record_version_number,
374 x_valid_flag => l_valid2_flag,
375 x_return_status => x_return_status, --l_return_status, Bug 2691822
376 x_error_msg_code => l_error_msg_code);
377 if not((l_valid1_flag='Y') and (l_valid2_flag='Y')) then
378 IF P_PA_DEBUG_MODE = 'Y' THEN
379 pa_debug.write_file('Baseline_FinPlan: ' || 'BUSINESS RULE VIOLATION: Check_Record_Version_Number failed');
380 END IF;
381 x_return_status := FND_API.G_RET_STS_ERROR;
382 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
383 p_msg_name => l_error_msg_code);
384 end if;
385 end if;
386
387 ----- IF THERE ARE ANY BUSINESS RULES VIOLATIONS, DO NOT PROCEED ----
388 l_msg_count := FND_MSG_PUB.count_msg;
389 if l_msg_count > 0 then
390 if l_msg_count = 1 then
391 PA_INTERFACE_UTILS_PUB.get_messages
392 (p_encoded => FND_API.G_TRUE,
393 p_msg_index => 1,
394 p_msg_count => l_msg_count,
395 p_msg_data => l_msg_data,
396 p_data => l_data,
397 p_msg_index_out => l_msg_index_out);
398 x_msg_data := l_data;
399 x_msg_count := l_msg_count;
400 else
401 x_msg_count := l_msg_count;
402 end if;
403 IF p_pa_debug_mode = 'Y' THEN
404 pa_debug.reset_err_stack;
405 END IF;
406 return;
407 end if;
408
409 ----- IF NO BUSINESS RULES VIOLATIONS, PROCEED AS USUAL -----
410 --dbms_output.put_line('All violation checks passed');
411
412 -- retrieve all necessary parameters for further processing
413 -- Fix for bug 2640785 approved plan type flags to be got from budget versions
414 -- instead from plan version level fp options.
415 -- budget version version_type to be used instead of preference_code from
416 -- proj_fp_options.
417
418 savepoint before_fp_baseline;
419
420 x_return_status := FND_API.G_RET_STS_SUCCESS;
421 x_fc_version_created_flag := 'N';
422
423 select bv.budget_type_code,
424 bv.resource_list_id,
425 bv.version_type,
426 pt.project_type_class_code,
427 bv.approved_rev_plan_type_flag,
428 bv.approved_cost_plan_type_flag,
429 DECODE(bv.version_type,
430 'COST',opt.cost_time_phased_code,
431 'REVENUE',opt.revenue_time_phased_code,
432 opt.all_time_phased_code),
433 DECODE(bv.version_type,
434 'COST',opt.cost_fin_plan_level_code,
435 'REVENUE',opt.revenue_fin_plan_level_code,
436 opt.all_fin_plan_level_code),
437 bv.budget_entry_method_code,
438 bv.pm_product_code,
439 /* bv.created_by, Commented for bug 6176649 */
440 opt.fin_plan_type_id,
441 pavl.plan_class_code,
442 nvl(pr.baseline_funding_flag,'N')
443 into l_budget_type_code,
444 l_resource_list_id,
445 l_version_type,
446 l_project_type_class_code,
447 l_ar_flag,
448 l_ac_flag,
449 l_time_phased_type_code,
450 l_fin_plan_level_code,
451 l_budget_entry_method_code,
452 l_pm_product_code,
453 /* l_created_by, Commented for bug 6176649 */
454 l_fin_plan_type_id,
455 l_fin_plan_class_code,
456 l_auto_baseline_project
457 from pa_project_types_all pt,
458 pa_projects_all pr,
459 pa_budget_versions bv,
460 pa_proj_fp_options opt,
461 pa_fin_plan_types_b pavl
462 where bv.budget_version_id = p_budget_version_id and
463 opt.fin_plan_version_id = bv.budget_version_id and
464 bv.project_id = pr.project_id and
465 pr.project_type = pt.project_type and
466 --nvl(pr.org_id,-99) = nvl(pt.org_id,-99) and --Bug 5374346
467 pr.org_id = pt.org_id and
468 opt.fin_plan_type_id = pavl.fin_plan_type_id and
469 opt.fin_plan_option_level_code = 'PLAN_VERSION';
470
471 --If the budget version being baselined is an approved revenue version, then the baseline should not happen
472 --if that version has budget lines with rejection codes
473 IF l_ar_flag='Y' THEN
474
475 OPEN c_chk_rej_codes;
476 FETCH c_chk_rej_codes INTO l_dummy;
477 IF c_chk_rej_codes%FOUND THEN
478
479 IF P_PA_DEBUG_MODE = 'Y' THEN
480 pa_debug.g_err_stage:= 'budget lines with rejection codes EXIST in ar version';
481 pa_debug.write(l_module_name,pa_debug.g_err_stage,3);
482 END IF;
483
484 PA_UTILS.ADD_MESSAGE
485 (p_app_short_name => 'PA',
486 p_msg_name => 'PA_FP_AR_BV_REJ_CODES_EXIST');
487
488
489 CLOSE c_chk_rej_codes;
490 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
491
492 END IF;
493
494 CLOSE c_chk_rej_codes;
495
496 END IF;
497
498
499 if l_budget_entry_method_code is not null then
500 select entry_level_code
501 into l_entry_level_code
502 from pa_budget_entry_methods
503 where budget_entry_method_code = l_budget_entry_method_code;
504 end if;
505
506 --dbms_output.put_line('big select statement executed');
507 -- Check whether workflow is being used for this project budget
508 -- If so, get the employee id based on the baselined_by_user_id
509 PA_CLIENT_EXTN_BUDGET_WF.Budget_Wf_Is_Used
510 (p_draft_version_id => p_budget_version_id,
511 p_project_id => p_project_id,
512 p_budget_type_code => NULL,
513 p_pm_product_code => l_pm_product_code,
514 p_fin_plan_type_id => l_fin_plan_type_id,
515 p_version_type => l_version_type,
516 p_result => l_workflow_is_used,
517 p_err_code => x_err_code, --l_return_status, Bug 2691822.
518 p_err_stage => l_err_stage,
519 p_err_stack => l_msg_data);
520 If l_workflow_is_used = 'T' Then
521 v_emp_id := pa_utils.GetEmpIdFromUser(pa_budget_wf.g_baselined_by_user_id);
522 end if;
523 --dbms_output.put_line('PA_CLIENT_EXTN_BUDGET_WF.Budget_Wf_Is_Used executed');
524 -- Verify budget rules if indicated
525 -- This API call is different from in PA_BUDGET_CORE.baseline
526 -- We need to set p_budget_type_code = null so that the verify API will use
527 -- p_fin_plan_type_id and p_version_type for the new budget model
528 if p_verify_budget_rules = 'Y' then
529
530 PA_BUDGET_UTILS.Verify_Budget_Rules
531 (p_draft_version_id => p_budget_version_id,
532 p_mark_as_original => l_mark_as_original,
533 p_event => 'BASELINE',
534 p_project_id => p_project_id,
535 p_budget_type_code => NULL,
536 p_fin_plan_type_id => l_fin_plan_type_id,
537 p_version_type => l_version_type,
538 p_resource_list_id => l_resource_list_id,
539 p_project_type_class_code => l_project_type_class_code,
540 p_created_by => l_created_by,
541 p_calling_module => 'PA_SS_FIN_PLANNING',
542 p_warnings_only_flag => l_warnings_only_flag,
543 p_err_msg_count => l_err_msg_count,
544 p_err_code => x_err_code,
545 p_err_stage => x_err_stage,
546 p_err_stack => x_err_stack);
547
548 -- if the only messages are warnings, we can proceed as usual. Otherwise,
549 -- return with error messages
550
551 if (l_err_msg_count > 0 ) then
552 if (l_warnings_only_flag = 'N') then
553 /*
554 PA_UTILS.Add_Message(p_app_short_name => 'PA',
555 p_msg_name => x_err_code);
556 */
557
558 x_return_status := FND_API.G_RET_STS_ERROR;
559 x_msg_count := FND_MSG_PUB.Count_Msg;
560 if x_msg_count = 1 then
561 PA_INTERFACE_UTILS_PUB.get_messages
562 (p_encoded => FND_API.G_TRUE,
563 p_msg_index => 1,
564 p_data => x_msg_data,
565 p_msg_index_out => l_msg_index_out);
566 end if;
567 return;
568
569 end if;
570 end if;
571 end if; -- verify budget rules
572
573
574 --dbms_output.put_line('proceeding with baselining');
575 --- BUDGET RULES VERIFIED AND OK, PROCEED WITH BASELINING ---
576
577 -- if version is APPROVED REVENUE plan type, and project is CONTRACT project,
578 -- 1. Check funding level (pa_billing_core.check_funding_level)
579 -- 2. Call update funding (pa_billing_core.update_funding)
580
581 IF ((l_ar_flag = 'Y') AND (l_project_type_class_code = 'CONTRACT')) THEN
582 pa_billing_core.check_funding_level (p_project_id,
583 l_funding_level,
584 x_err_code,
585 x_err_stage,
586 x_err_stack);
587 if (x_err_code <> 0) then
588 PA_UTILS.Add_Message(p_app_short_name => 'PA',
589 p_msg_name => x_err_code);
590
591 x_return_status := FND_API.G_RET_STS_ERROR;
592 x_msg_count := FND_MSG_PUB.Count_Msg;
593 if x_msg_count = 1 then
594 PA_INTERFACE_UTILS_PUB.get_messages
595 (p_encoded => FND_API.G_TRUE,
596 p_msg_index => 1,
597 p_data => x_msg_data,
598 p_msg_index_out => l_msg_index_out);
599 end if;
600 return;
601
602 end if;
603
604
605 pa_billing_core.update_funding (p_project_id,
606 l_funding_level,
607 x_err_code,
608 x_err_stage,
609 x_err_stack);
610 if (x_err_code <> 0) then
611 PA_UTILS.Add_Message(p_app_short_name => 'PA',
612 p_msg_name => x_err_code);
613
614 x_return_status := FND_API.G_RET_STS_ERROR;
615 x_msg_count := FND_MSG_PUB.Count_Msg;
616 if x_msg_count = 1 then
617 PA_INTERFACE_UTILS_PUB.get_messages
618 (p_encoded => FND_API.G_TRUE,
619 p_msg_index => 1,
620 p_data => x_msg_data,
621 p_msg_index_out => l_msg_index_out);
622 end if;
623 return;
624
625 end if;
626
627 ELSIF ((l_ac_flag = 'Y') AND (l_project_type_class_code <> 'CONTRACT')) THEN
628 -- if the version is APPROVED COST plan type, and project is NOT CONTRACT,
629 -- 1. Call update funding (pa_billing_core.update_funding)
630 pa_billing_core.update_funding(p_project_id,
631 l_funding_level, -- Funding level
632 x_err_code,
633 x_err_stage,
634 x_err_stack);
635 if (x_err_code <> 0) then
636 PA_UTILS.Add_Message(p_app_short_name => 'PA',
637 p_msg_name => x_err_code);
638
639 x_return_status := FND_API.G_RET_STS_ERROR;
640 x_msg_count := FND_MSG_PUB.Count_Msg;
641 if x_msg_count = 1 then
642 PA_INTERFACE_UTILS_PUB.get_messages
643 (p_encoded => FND_API.G_TRUE,
644 p_msg_index => 1,
645 p_data => x_msg_data,
646 p_msg_index_out => l_msg_index_out);
647 end if;
648 return;
649
650 end if;
651
652 END IF; -- of AR revenue budget
653
654 /* set the status_code back to "Working" from "Submitted" for the version we're baselining */
655 --dbms_output.put_line('updating pa_budget_versions');
656 update pa_budget_versions
657 set last_update_date = SYSDATE,
658 last_updated_by = FND_GLOBAL.user_id,
659 last_update_login = FND_GLOBAL.login_id,
660 budget_status_code = PA_FP_CONSTANTS_PKG.G_BUDGET_STATUS_WORKING, -- bug 3978894 'W',
661 record_version_number = record_version_number+1
662 where budget_version_id = p_budget_version_id;
663
664 --Bug 3882819. The impact of partially implemented change orders should be considered
665 --as fully implemented when the approved revenue version gets baselined.For AutoBaselined projects
666 --the baselining will happen for each and implementation (full/partial also) of financial impact
667 --and hence partial implementation of change orders should be supported even after baselining.
668 IF l_ar_flag ='Y' AND
669 NVL(l_auto_baseline_project,'N')='N' THEN
670
671 IF P_PA_DEBUG_MODE = 'Y' THEN
672 pa_debug.g_err_stage := 'Updating the rev_partially_impl_flag in partially implemented CIs';
673 pa_debug.write('pa_fin_plan_pvt: ' || l_module_name,pa_debug.g_err_stage,5);
674 END IF;
675
676 UPDATE pa_budget_versions
677 SET last_update_date = SYSDATE,
678 last_updated_by = FND_GLOBAL.user_id,
679 last_update_login = FND_GLOBAL.login_id,
680 record_version_number = record_version_number+1,
681 rev_partially_impl_flag='N'
682 WHERE project_id = p_project_id
683 AND ci_id IS NOT NULL
684 AND rev_partially_impl_flag='Y';
685
686 IF P_PA_DEBUG_MODE = 'Y' THEN
687 pa_debug.g_err_stage := 'No of records updated '||SQL%ROWCOUNT;
688 pa_debug.write('pa_fin_plan_pvt: ' || l_module_name,pa_debug.g_err_stage,5);
689 END IF;
690
691 END IF;
692
693 --dbms_output.put_line('Set status code back to working from submitted');
694 -- IF baselined_version_id DOES NOT EXIST:
695 -- 1. Create resource list assignments (pa_res_list_assignments.create_rl_assgmt)
696 -- 2. Create resource list uses (pa_res_list_assignments.create_rl_uses)
697
698
699 if (p_orig_budget_version_id IS NULL) then
700 --dbms_output.put_line('NO ORIG BASELINED VERSION');
701
702
703
704 -- Bug 4583454, jwhite, 12-SEP-05 -------------------------------------------
705 -- Restoration of FP Budget Support in PSI/summrizartion also requires
706 -- restoration of these two procedure calls for the following:
707 -- 1) Resource lists originally created in the Resource List form.
708 -- 2) Uncategorized "None" Resource Lists.
709 --
710
711 SELECT migration_code
712 ,uncategorized_flag
713 INTO l_migration_code, l_uncategorized_flag
714 FROM pa_resource_lists_all_bg
715 WHERE resource_list_id = l_resource_list_id;
716
717
718 IF ( (nvl(l_migration_code,'M') = 'M')
719 OR (l_uncategorized_flag = 'Y') )
720 THEN
721
722
723
724 pa_res_list_assignments.create_rl_assgmt(x_project_id => p_project_id,
725 X_Resource_list_id =>l_resource_list_id,
726 X_Resource_list_Assgmt_id => x_resource_list_assgmt_id,
727 X_err_code => x_err_code,
728 X_err_stage =>x_err_stage,
729 x_err_stack =>x_err_stack);
730 -- if oracle or application error, return
731
732 if (x_err_code <> 0) then
733 PA_UTILS.Add_Message(p_app_short_name => 'PA',
734 p_msg_name => x_err_code);
735
736 x_return_status := FND_API.G_RET_STS_ERROR;
737 x_msg_count := FND_MSG_PUB.Count_Msg;
738 if x_msg_count = 1 then
739 PA_INTERFACE_UTILS_PUB.get_messages
740 (p_encoded => FND_API.G_TRUE,
741 p_msg_index => 1,
742 p_data => x_msg_data,
743 p_msg_index_out => l_msg_index_out);
744 end if;
745 return;
746
747 end if;
748
749 pa_res_list_assignments.create_rl_uses(X_Project_id => p_project_id,
750 X_Resource_list_Assgmt_id => x_resource_list_assgmt_id,
751 --CONFIRM WITH JEFF AS THIS API EXPECTS A BUDGET_TYPE_CODE
752 --Reference update by Ramesh in bug 2622657. Should have use l_fin_plan_type_id
753 --as X_Use_Code
754 X_Use_Code => l_fin_plan_type_id,
755 X_err_code => x_err_code,
756 X_err_stage => x_err_stage,
757 X_err_stack => x_err_stack);
758 -- if oracle or application error, return.
759
760 if (x_err_code <> 0) then
761 PA_UTILS.Add_Message(p_app_short_name => 'PA',
762 p_msg_name => x_err_code);
763
764 x_return_status := FND_API.G_RET_STS_ERROR;
765 x_msg_count := FND_MSG_PUB.Count_Msg;
766 if x_msg_count = 1 then
767 PA_INTERFACE_UTILS_PUB.get_messages
768 (p_encoded => FND_API.G_TRUE,
769 p_msg_index => 1,
770 p_data => x_msg_data,
771 p_msg_index_out => l_msg_index_out);
772 end if;
773 return;
774
775 end if;
776
777 END IF; -- l_migration_code
778
779 -- Bug 4583454, jwhite, 12-SEP-05 -------------------------------------------
780
781
782 else
783 -- IF baselined_version_id EXISTS --> set CURRENT_FLAG to 'N' to remove current baselined
784 -- status
785 update pa_budget_versions
786 set last_update_date = SYSDATE,
787 last_updated_by = FND_GLOBAL.user_id,
788 last_update_login = FND_GLOBAL.login_id,
789 current_flag = 'N',
790 record_version_number = record_version_number+1
791 where budget_version_id=p_orig_budget_version_id;
792 --dbms_output.put_line('THERE IS ORIG BUDGET VERSION ID: set it to be NOT CURRENT BASELINED');
793 end if;
794
795 -- Proceed with the rest of the steps for baselining (see PAFPPUBB.Baseline for all
796 -- the steps for baselining)
797
798 /* create a copy, labeled as 'BASELINED' */
799
800 PA_FIN_PLAN_PUB.Copy_Version
801 (p_project_id => p_project_id,
802 p_source_version_id => p_budget_version_id,
803 p_copy_mode => 'B',
804 p_calling_module => 'FINANCIAL_PLANNING',
805 px_target_version_id => l_target_version_id,
806 x_return_status => x_return_status, --l_return_status, bug 2691822
807 x_msg_count => l_msg_count,
808 x_msg_data => l_msg_data);
809 /* PA_FIN_PLAN_PUB.Copy_Version may have generated errors */
810 --dbms_output.put_line('PA_FIN_PLAN_PUB.Copy_Version executed');
811 if x_return_status <> FND_API.G_RET_STS_SUCCESS then -- bug 2691822
812 PA_UTILS.Add_Message(p_app_short_name => 'PA',
813 p_msg_name => x_err_code);
814
815 x_return_status := FND_API.G_RET_STS_ERROR;
816 x_msg_count := FND_MSG_PUB.Count_Msg;
817 if x_msg_count = 1 then
818 PA_INTERFACE_UTILS_PUB.get_messages
819 (p_encoded => FND_API.G_TRUE,
820 p_msg_index => 1,
821 p_data => x_msg_data,
822 p_msg_index_out => l_msg_index_out);
823 end if;
824 return;
825
826 end if;
827 /* Bug 3756079:-dbora- The following piece of code flips the value of
828 * inclusion_method_code and creation_date in pa_fp_merged_ctrl_items for the existing
829 * working version and the newly created baselined version as this value
830 * would be used in the view included change document page to show various
831 * change documents included in that version
832 */
833 IF P_PA_DEBUG_MODE = 'Y' THEN
834 pa_debug.g_err_stage := 'After Copy Version-- Firing Select';
835 pa_debug.write('pa_fin_plan_pvt: ' || l_module_name,pa_debug.g_err_stage,5);
836 END IF;
837
838 SELECT ci_id, inclusion_method_code, version_type, creation_date
839 BULK COLLECT INTO l_temp_ci_id_tbl, l_tmp_incl_method_code_tbl, l_version_type_tbl, l_cw_creation_date_tbl
840 FROM pa_fp_merged_ctrl_items
841 WHERE plan_version_id = p_budget_version_id
842 AND project_id = p_project_id;
843
844 -- selecting creation_date for the baselined version
845 SELECT creation_date
846 BULK COLLECT INTO l_bl_creation_date_tbl
847 FROM pa_fp_merged_ctrl_items
848 WHERE plan_version_id = l_target_version_id
849 AND project_id = p_project_id;
850
851 IF P_PA_DEBUG_MODE = 'Y' THEN
852 pa_debug.g_err_stage := 'Rows returned in ci_id tbl: ' || l_temp_ci_id_tbl.COUNT;
853 pa_debug.write('pa_fin_plan_pvt: ' || l_module_name,pa_debug.g_err_stage,5);
854 END IF;
855 -- Flipping inclusion_method_code, creation_date, Updating the WHO columns for the baselined version
856 IF l_temp_ci_id_tbl.COUNT > 0 THEN
857 FORALL i in l_temp_ci_id_tbl.FIRST..l_temp_ci_id_tbl.LAST
858 UPDATE pa_fp_merged_ctrl_items
859 SET inclusion_method_code = l_tmp_incl_method_code_tbl(i),
860 creation_date = l_cw_creation_date_tbl(i),
861 last_update_login = FND_GLOBAL.login_id,
862 last_updated_by = FND_GLOBAL.user_id,
863 last_update_date = SYSDATE
864 WHERE plan_version_id = l_target_version_id
865 AND project_id = p_project_id
866 AND ci_id = l_temp_ci_id_tbl(i)
867 AND version_type = l_version_type_tbl(i);
868 END IF;
869
870 -- Flipping inclusion_method_code, creation_date, Updating the WHO columns for the working version
871 IF l_temp_ci_id_tbl.COUNT > 0 THEN
872 FORALL i in l_temp_ci_id_tbl.FIRST..l_temp_ci_id_tbl.LAST
873 UPDATE pa_fp_merged_ctrl_items
874 SET inclusion_method_code = 'COPIED',
875 creation_date = l_bl_creation_date_tbl(i),
876 last_update_login = FND_GLOBAL.login_id,
877 last_updated_by = FND_GLOBAL.user_id,
878 last_update_date = SYSDATE
879 WHERE plan_version_id = p_budget_version_id
880 AND project_id = p_project_id
881 AND ci_id = l_temp_ci_id_tbl(i)
882 AND version_type = l_version_type_tbl(i);
883 END IF;
884 -- Bug 4187704: grouped the update statments into 2 distinct sets for baselined and working versions
885 -- Bug 3756079: Finish
886
887 /* Bug 3841942 Raja 21-Sep-04
888 For none time phased budgets transaction start and end dates are not
889 only criteria. The following logic is not applicable beyond FP M
890
891 -- Handle project date/task date changes
892 -- If the effective dates on Project/Tasks
893 -- has changed for Non Time phased budgets, then update the
894 -- start and end dates on the budget lines.
895
896 if (l_time_phased_type_code = 'N') and (l_entry_level_code = 'P') then -- Project Level
897 select start_date,completion_date
898 into v_project_start_date,
899 v_project_completion_date
900 from pa_projects_all
901 where project_id = p_project_id;
902
903 if (v_project_start_date is null ) or (v_project_completion_date is null) then
904 PA_UTILS.Add_Message(p_app_short_name => 'PA',
905 p_msg_name => 'PA_BU_NO_PROJ_END_DATE');
906 end if;
907
908 update pa_budget_lines
909 set start_date= v_project_start_date,
910 end_date = v_project_completion_date
911 where resource_assignment_id in
912 (select resource_assignment_id
913 from pa_resource_assignments
914 where budget_version_id = l_target_version_id)
915 and ((start_date <> v_project_start_date) OR (end_date <> v_project_completion_date));
916
917 -- Check that rows should be updated only if the project start or end
918 -- dates are different from the budget start and end dates
919
920 elsif (l_time_phased_type_code = 'N') then -- Task Level
921
922 select start_date,completion_date
923 into v_project_start_date,
924 v_project_completion_date
925 from pa_projects_all
926 where project_id = p_project_id;
927
928 for bl_rec in (select start_date,
929 completion_date ,
930 resource_assignment_id
931 from pa_tasks t ,pa_resource_assignments r
932 where t.task_id = r.task_id and
933 r.budget_version_id = l_target_version_id)
934 loop
935 bl_rec.start_date := nvl(bl_rec.start_date,v_project_start_date);
936 bl_rec.completion_date := nvl(bl_rec.completion_date ,v_project_completion_date);
937
938 -- Check that rows should be updated only if the task start or end
939 -- dates are different from the budget start and end dates
940
941 if (bl_rec.start_date is null) or (bl_rec.completion_date is null) then
942 PA_UTILS.Add_Message(p_app_short_name => 'PA',
943 p_msg_name => 'PA_BU_NO_TASK_PROJ_DATE');
944 else
945 update pa_budget_lines
946 set start_date = bl_rec.start_date,
947 end_date = bl_rec.completion_date
948 where resource_assignment_id = bl_rec.resource_assignment_id and
949 ((start_date <> bl_rec.start_date) or (end_date <> bl_rec.completion_date));
950 end if;
951
952 end loop;
953
954 end if;
955
956
957 x_msg_count := FND_MSG_PUB.Count_Msg;
958 if x_msg_count = 1 then
959 PA_INTERFACE_UTILS_PUB.get_messages
960 (p_encoded => FND_API.G_TRUE,
961 p_msg_index => 1,
962 p_data => x_msg_data,
963 p_msg_index_out => l_msg_index_out);
964 end if;
965 if x_msg_count > 0 then
966 x_return_status := FND_API.G_RET_STS_ERROR;
967 end if;
968
969 Bug 3841942 Raja 21-Sep-04 */
970
971 --Bug 4094762. Code that creates the intial forecast version (its there below the PJI API call) assumes that the pji
972 --data exists for the baselined version. Hence the PJI API call should be made before that code gets executed.
973 /* FP M - Reporting lines integration */
974 BEGIN
975 IF p_pa_debug_mode = 'Y' THEN
976 pa_debug.write('Baseline_Finplan','Calling PJI_FM_XBS_ACCUM_MAINT.PLAN_BASELINE ' ,5);
977 pa_debug.write('Baseline_Finplan','p_baseline_version_id '|| p_budget_version_id,5);
978 pa_debug.write('Baseline_Finplan', 'p_new_version_id '|| l_target_version_id,5);
979 END IF;
980 PJI_FM_XBS_ACCUM_MAINT.PLAN_BASELINE (
981 p_baseline_version_id => p_budget_version_id,
982 p_new_version_id => l_target_version_id,
983 x_return_status => l_return_status,
984 x_msg_code => l_error_msg_code);
985 IF l_return_status <> FND_API.G_RET_STS_SUCCESS then
986 PA_UTILS.ADD_MESSAGE(p_app_short_name => PA_FP_CONSTANTS_PKG.G_PERIOD_TYPE_PA,
987 p_msg_name => l_error_msg_code);
988
989 RAISE pa_fin_plan_pub.rollback_on_error;
990 END IF;
991 END;
992
993 /* FP M - If approved budget version is baselined for the first time, forecast version should be created */
994
995 /* Business rules given by PM (JR) - 21st Feb 2004
996
997 # An initial forecast version will be created as per copy from the budget version, including the plan settings and amounts. For example:
998
999 o In the budget version, cost and revenue are planned together, whereas in the forecast plan type, cost and revenue are planned separately
1000 X Two initial forecast versions will be created: cost version and forecast version.
1001
1002 o In the budget version, cost and revenue are planned separately, whereas in the forecast plan type, cost and revenue are planned together:
1003 X An initial forecast version will be created: cost and revenue version.
1004 X Either cost amount or revenue amounts will be copied to that initial forecast version, depending on which budget version is first baselined (cost or revenue).
1005
1006 o In the budget version, cost and revenue are planned separately, as in the forecast plan type.
1007 X When cost budget is baselined, the initial cost forecast version will be created.
1008 X When revenue budget is baselined, the initial revenue forecast version will be created. */
1009
1010 IF l_fin_plan_class_code = 'BUDGET' and
1011 (l_ar_flag = 'Y' or
1012 l_ac_flag = 'Y') THEN /* Only if the version that is being baselined is an AR or AC BUDGET version */
1013
1014 IF P_PA_DEBUG_MODE = 'Y' THEN
1015 pa_debug.write('PAFPPVTB.BASELINE_FINPLAN','BUDGET plan class and ac flag is ' || l_ar_flag || ' and ac flag is ' || l_ac_flag,3);
1016 END IF;
1017
1018 Begin
1019 Select 'Y'
1020 Into l_base_line_ver_exists
1021 From Pa_Budget_Versions
1022 Where Project_id = p_project_id
1023 And budget_type_code is null /* Bug 4200168*/
1024 And Fin_plan_type_id = l_fin_plan_type_id
1025 And Budget_status_code = 'B'
1026 And Version_type = l_version_type
1027 And Ci_Id Is Null
1028 And Budget_version_id <> l_target_version_id
1029 And Rownum < 2;
1030 Exception
1031 When No_Data_Found Then
1032 l_base_line_ver_exists := 'N';
1033 End;
1034
1035 IF P_PA_DEBUG_MODE = 'Y' THEN
1036 pa_debug.write('PAFPPVTB.BASELINE_FINPLAN','l_base_line_ver_exists ' || l_base_line_ver_exists || ' l_version_type ' || l_version_type,3);
1037 END IF;
1038
1039 If l_base_line_ver_exists = 'N' THEN /* No further processing for creation of forecast version is required if an appr baseline version already exists */
1040
1041 Select fin_plan_type_id,fin_plan_preference_code,primary_cost_forecast_flag, primary_rev_forecast_flag
1042 Bulk Collect
1043 Into l_fc_plan_type_ids_tbl,l_fc_pt_pref_code_tbl,l_primary_cost_fcst_flag_tbl,l_primary_rev_fcst_flag_tbl
1044 From Pa_proj_fp_options
1045 Where Project_Id = p_project_id
1046 And Fin_Plan_Option_Level_Code = 'PLAN_TYPE'
1047 And (primary_cost_forecast_flag = 'Y' or
1048 primary_rev_forecast_flag = 'Y');
1049
1050 If l_fc_plan_type_ids_tbl.count > 0 Then /* Only if forecast plan types have been added to the project */
1051
1052 FOR i IN l_fc_plan_type_ids_tbl.FIRST .. l_fc_plan_type_ids_tbl.LAST LOOP
1053
1054 IF P_PA_DEBUG_MODE = 'Y' THEN
1055 pa_debug.write('PAFPPVTB.BASELINE_FINPLAN','l_fc_plan_type_ids_tbl ' || l_fc_plan_type_ids_tbl(i) ||
1056 ' l_fc_pt_pref_code_tbl ' || l_fc_pt_pref_code_tbl(i),3);
1057 END IF;
1058
1059 l_curr_work_ver_id := Null;
1060 l_curr_work_fp_opt_id := Null;
1061 l_fc_version_type := Null;
1062 l_version_name := Null;
1063
1064 If (l_version_type = 'ALL' or l_version_type = 'COST') and
1065 (l_fc_pt_pref_code_tbl(i) <> 'REVENUE_ONLY') and
1066 (l_ac_flag = 'Y') and
1067 (l_primary_cost_fcst_flag_tbl(i) = 'Y') THEN
1068
1069 IF l_fc_pt_pref_code_tbl(i) = 'COST_AND_REV_SAME' THEN
1070 l_fc_version_type := 'ALL';
1071 l_version_name := ' ';
1072 ELSE
1073 l_fc_version_type := 'COST';
1074 l_version_name := ' Cost ';
1075 END IF;
1076
1077 Pa_Fin_Plan_Utils.Get_Curr_Working_Version_Info(
1078 p_project_id => p_project_id
1079 ,p_fin_plan_type_id => l_fc_plan_type_ids_tbl(i)
1080 ,p_version_type => l_fc_version_type
1081 ,x_fp_options_id => l_fp_options_id
1082 ,x_fin_plan_version_id => l_curr_work_ver_id
1083 ,x_return_status => x_return_status
1084 ,x_msg_count => x_msg_count
1085 ,x_msg_data => x_msg_data);
1086
1087 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1088
1089 IF P_PA_DEBUG_MODE = 'Y' THEN
1090 pa_debug.g_err_stage:='Pa_Fin_Plan_Utils.Get_Curr_Working_Version_Info errored: project_id : ' ||
1091 p_project_id || ' : plan type id : ' || l_fc_plan_type_ids_tbl(i) ||
1092 ' : version_type : ' || l_fc_version_type;
1093 pa_debug.write('PAFPPVTB.BASELINE_FINPLAN',pa_debug.g_err_stage,5);
1094 END IF;
1095
1096 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
1097
1098 END IF;
1099
1100 /* FC Version would be created only when one already doesnt exists */
1101 If l_curr_work_ver_id IS NULL THEN
1102
1103 Pa_fin_plan_pub.Create_Version (
1104 p_project_id => p_project_id
1105 ,p_fin_plan_type_id => l_fc_plan_type_ids_tbl(i)
1106 ,p_element_type => l_fc_version_type
1107 ,p_version_name => 'Initial' || l_version_name || 'Forecast Version'
1108 ,p_description => 'Initial' || l_version_name || 'Forecast Version'
1109 ,p_calling_context => PA_FP_CONSTANTS_PKG.G_CREATE_DRAFT
1110 ,px_budget_version_id => l_curr_work_ver_id
1111 ,x_proj_fp_option_id => l_curr_work_fp_opt_id
1112 ,x_return_status => x_return_status
1113 ,x_msg_count => x_msg_count
1114 ,x_msg_data => x_msg_data);
1115
1116 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1117
1118 IF P_PA_DEBUG_MODE = 'Y' THEN
1119 pa_debug.g_err_stage:='Pa_fin_plan_pub.Create_Version errored. p_project_id ' ||
1120 p_project_id || ' : plan type id : ' || l_fc_plan_type_ids_tbl(i) ||
1121 ' : version_type : ' || l_fc_version_type;
1122 pa_debug.write('PAFPPVTB.BASELINE_FINPLAN',pa_debug.g_err_stage,5);
1123 END IF;
1124
1125 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
1126
1127 END IF;
1128
1129 PA_FIN_PLAN_PUB.Copy_Version
1130 (p_project_id => p_project_id,
1131 p_source_version_id => l_target_version_id,
1132 p_copy_mode => 'W',
1133 p_calling_module => 'FINANCIAL_PLANNING',
1134 px_target_version_id => l_curr_work_ver_id,
1135 x_return_status => x_return_status,
1136 x_msg_count => l_msg_count,
1137 x_msg_data => l_msg_data);
1138
1139
1140 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1141
1142 IF P_PA_DEBUG_MODE = 'Y' THEN
1143 pa_debug.g_err_stage:='PA_FIN_PLAN_PUB.Copy_Version errored: project_id : ' ||
1144 p_project_id || ' : l_target_version_id : ' ||
1145 l_target_version_id || ' : l_fc_plan_type_ids_tbl(i) : ' ||
1146 l_fc_plan_type_ids_tbl(i);
1147 pa_debug.write('PAFPPVTB.BASELINE_FINPLAN',pa_debug.g_err_stage,5);
1148 END IF;
1149
1150 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
1151
1152 END IF;
1153
1154 IF P_PA_DEBUG_MODE = 'Y' THEN
1155 pa_debug.write('PAFPPVTB.BASELINE_FINPLAN','l_curr_work_ver_id ' || l_curr_work_ver_id,3);
1156 END IF;
1157
1158 x_fc_version_created_flag := 'Y';
1159
1160 END IF;
1161
1162 END IF;
1163
1164 l_curr_work_ver_id := Null;
1165 l_curr_work_fp_opt_id := Null;
1166 l_fc_version_type := Null;
1167 l_version_name := Null;
1168
1169 If (l_version_type = 'ALL' or l_version_type = 'REVENUE') and
1170 (l_fc_pt_pref_code_tbl(i) <> 'COST_ONLY') and
1171 (l_ar_flag = 'Y') and
1172 (l_primary_rev_fcst_flag_tbl(i) = 'Y') and
1173 /* Bug 4200168: since we already have the current working version info from the previous call*/
1174 (NOT(l_version_type = 'ALL' and l_fc_pt_pref_code_tbl(i) = 'COST_AND_REV_SAME'))THEN
1175
1176 IF l_fc_pt_pref_code_tbl(i) = 'COST_AND_REV_SAME' THEN
1177 l_fc_version_type := 'ALL';
1178 l_version_name := ' ';
1179 ELSE
1180 l_fc_version_type := 'REVENUE';
1181 l_version_name := ' Revenue ';
1182 END IF;
1183
1184
1185 Pa_Fin_Plan_Utils.Get_Curr_Working_Version_Info(
1186 p_project_id => p_project_id
1187 ,p_fin_plan_type_id => l_fc_plan_type_ids_tbl(i)
1188 ,p_version_type => l_fc_version_type
1189 ,x_fp_options_id => l_fp_options_id
1190 ,x_fin_plan_version_id => l_curr_work_ver_id
1191 ,x_return_status => x_return_status
1192 ,x_msg_count => x_msg_count
1193 ,x_msg_data => x_msg_data);
1194
1195 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1196
1197 IF P_PA_DEBUG_MODE = 'Y' THEN
1198 pa_debug.g_err_stage:='Pa_Fin_Plan_Utils.Get_Curr_Working_Version_Info errored: project_id : ' ||
1199 p_project_id || ' : plan type id : ' || l_fc_plan_type_ids_tbl(i) ||
1200 ' : version_type : ' || l_fc_version_type;
1201 pa_debug.write('PAFPPVTB.BASELINE_FINPLAN',pa_debug.g_err_stage,5);
1202 END IF;
1203
1204 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
1205
1206 END IF;
1207
1208 /* FC Version would be created only when one already doesnt exists */
1209 If l_curr_work_ver_id IS NULL THEN
1210
1211 Pa_fin_plan_pub.Create_Version (
1212 p_project_id => p_project_id
1213 ,p_fin_plan_type_id => l_fc_plan_type_ids_tbl(i)
1214 ,p_element_type => l_fc_version_type
1215 ,p_version_name => 'Initial' || l_version_name || 'Forecast Version'
1216 ,p_description => 'Initial' || l_version_name || 'Forecast Version'
1217 ,p_calling_context => PA_FP_CONSTANTS_PKG.G_CREATE_DRAFT
1218 ,px_budget_version_id => l_curr_work_ver_id
1219 ,x_proj_fp_option_id => l_curr_work_fp_opt_id
1220 ,x_return_status => x_return_status
1221 ,x_msg_count => x_msg_count
1222 ,x_msg_data => x_msg_data);
1223
1224 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1225
1226 IF P_PA_DEBUG_MODE = 'Y' THEN
1227 pa_debug.g_err_stage:='Pa_fin_plan_pub.Create_Version errored. p_project_id ' ||
1228 p_project_id || ' : plan type id : ' || l_fc_plan_type_ids_tbl(i) ||
1229 ' : version_type : ' || l_fc_version_type;
1230 pa_debug.write('PAFPPVTB.BASELINE_FINPLAN',pa_debug.g_err_stage,5);
1231 END IF;
1232
1233 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
1234
1235 END IF;
1236
1237 PA_FIN_PLAN_PUB.Copy_Version
1238 (p_project_id => p_project_id,
1239 p_source_version_id => l_target_version_id,
1240 p_copy_mode => 'W',
1241 p_calling_module => 'FINANCIAL_PLANNING',
1242 px_target_version_id => l_curr_work_ver_id,
1243 x_return_status => x_return_status,
1244 x_msg_count => l_msg_count,
1245 x_msg_data => l_msg_data);
1246
1247
1248 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1249
1250 IF P_PA_DEBUG_MODE = 'Y' THEN
1251 pa_debug.g_err_stage:='PA_FIN_PLAN_PUB.Copy_Version errored: project_id : ' ||
1252 p_project_id || ' : l_target_version_id : ' ||
1253 l_target_version_id || ' : l_fc_plan_type_ids_tbl(i) : ' ||
1254 l_fc_plan_type_ids_tbl(i);
1255 pa_debug.write('PAFPPVTB.BASELINE_FINPLAN',pa_debug.g_err_stage,5);
1256 END IF;
1257
1258 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
1259
1260 END IF;
1261
1262 IF P_PA_DEBUG_MODE = 'Y' THEN
1263 pa_debug.write('PAFPPVTB.BASELINE_FINPLAN','l_curr_work_ver_id ' || l_curr_work_ver_id,3);
1264 END IF;
1265
1266 x_fc_version_created_flag := 'Y';
1267
1268 END IF;
1269
1270 END IF;
1271
1272 END LOOP; /* FOR i IN l_fc_plan_type_ids_tbl.FIRST .. l_fc_plan_type_ids_tbl.LAST LOOP */
1273
1274 END IF; /* If l_fc_plan_type_ids_tbl.count > 0 Then */
1275
1276 END IF; /* If l_base_line_ver_exists = 'N' THEN */
1277
1278 END IF; /* IF l_fin_plan_class_code = 'BUDGET' and (l_ar_flag = 'Y' or l_ac_flag = 'Y') THEN */
1279
1280 IF p_pa_debug_mode = 'Y' THEN
1281 pa_debug.reset_err_stack;
1282 END IF;
1283 EXCEPTION
1284
1285 when pa_fin_plan_pvt.baseline_finplan_error then
1286 rollback to before_fp_baseline;
1287 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1288 return;
1289 WHEN PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc THEN
1290 rollback to before_fp_baseline;
1291 --Bug 4044009
1292 x_return_status := FND_API.G_RET_STS_ERROR;
1293 l_msg_count := FND_MSG_PUB.count_msg;
1294 IF l_msg_count = 1 THEN
1295 PA_INTERFACE_UTILS_PUB.get_messages
1296 (p_encoded => FND_API.G_TRUE,
1297 p_msg_index => 1,
1298 p_msg_count => l_msg_count,
1299 p_msg_data => l_msg_data,
1300 p_data => l_data,
1301 p_msg_index_out => l_msg_index_out);
1302 x_msg_data := l_data;
1303 x_msg_count := l_msg_count;
1304 ELSE
1305 x_msg_count := l_msg_count;
1306 END IF;
1307 IF p_pa_debug_mode = 'Y' THEN
1308 pa_debug.reset_err_stack;
1309 END IF;
1310 --Bug 4044009
1311 RETURN;
1312 when others then
1313 rollback to before_fp_baseline;
1314 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1315 x_msg_count := 1;
1316 x_msg_data := SQLERRM;
1317 FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_FIN_PLAN_PVT',
1318 p_procedure_name => 'Baseline_FinPlan');
1319 IF p_pa_debug_mode = 'Y' THEN
1320 pa_debug.reset_err_stack;
1321 END IF;
1322 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1323
1324 END Baseline_FinPlan;
1325 /* ------------------------------------------------------------------------ */
1326
1327 -- HISTORY
1328 -- 11/16/02 dlai: bug fix 2668857 - when joining pa_projects_all with
1329 -- pa_project_types_all, need to join based on org_id as well
1330 PROCEDURE Submit_Current_Working_FinPlan
1331 (p_project_id IN pa_budget_versions.project_id%TYPE,
1332 p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE,
1333 p_record_version_number IN pa_budget_versions.record_version_number%TYPE,
1334 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1335 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
1336 x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
1337 IS
1338
1339 -- error handling variables
1340 l_valid_flag VARCHAR2(1); -- for PA_FIN_PLAN_UTILS.Check_Record_Version_Number
1341 l_msg_count NUMBER := 0;
1342 l_err_msg_count NUMBER := 0;
1343 l_data VARCHAR2(2000);
1344 l_msg_data VARCHAR2(2000);
1345 l_error_msg_code VARCHAR2(30);
1346 l_msg_index_out NUMBER;
1347 l_return_status VARCHAR2(2000);
1348 l_err_code NUMBER;
1349 l_err_stage VARCHAR2(2000);
1350 l_err_stack VARCHAR2(2000);
1351
1352 -- local variables
1353 l_budget_type_code pa_budget_versions.budget_type_code%TYPE;
1354 l_resource_list_id pa_budget_versions.resource_list_id%TYPE;
1355 l_project_type_class_code pa_project_types.project_type_class_code%TYPE;
1356 l_version_type pa_budget_versions.version_type%TYPE;
1357 l_ac_flag VARCHAR2(1);
1358 l_ar_flag VARCHAR2(1);
1359 l_time_phased_type_code pa_budget_entry_methods.time_phased_type_code%TYPE;
1360 l_fin_plan_level_code VARCHAR2(30);
1361 l_fin_plan_type_code VARCHAR2(30);
1362 l_fin_plan_type_id pa_proj_fp_options.fin_plan_type_id%TYPE;
1363 l_entry_level_code pa_budget_entry_methods.entry_level_code%TYPE;
1364 l_pm_product_code pa_budget_versions.pm_product_code%TYPE;
1365 l_workflow_is_used VARCHAR2(1); -- flag to determine if workflow is used
1366 l_warnings_only_flag VARCHAR2(1);
1367 l_mark_as_original varchar2(30) := 'N';
1368 l_created_by pa_budget_versions.created_by%TYPE;
1369 v_emp_id NUMBER; -- employee id
1370
1371 BEGIN
1372 FND_MSG_PUB.initialize;
1373 IF P_PA_DEBUG_MODE = 'Y' THEN
1374 pa_debug.init_err_stack('PA_FIN_PLAN_PUB.Submit_Current_Working_FinPlan');
1375 END IF;
1376 x_msg_count := 0;
1377
1378 /* Added for bug 6176649 */
1379 l_created_by:=FND_GLOBAL.user_id;
1380
1381
1382 ------------ CHECK FOR BUSINESS RULES VIOLATIONS --------------
1383 -- 1. RECORD VERSION NUMBER -- If record_version_number of p_budget_version_id
1384 -- has changed, return error:
1385 PA_FIN_PLAN_UTILS.Check_Record_Version_Number
1386 (p_unique_index => p_budget_version_id,
1387 p_record_version_number => p_record_version_number,
1388 x_valid_flag => l_valid_flag,
1389 x_return_status => l_return_status,
1390 x_error_msg_code => l_error_msg_code);
1391 if x_return_status = FND_API.G_RET_STS_ERROR then
1392 IF P_PA_DEBUG_MODE = 'Y' THEN
1393 pa_debug.write_file('Submit_Current_Working_FinPlan: ' || 'record version number error ');
1394 END IF;
1395 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
1396 p_msg_name => l_error_msg_code);
1397 end if;
1398
1399 ----- IF THERE ARE ANY BUSINESS RULES VIOLATIONS, DO NOT PROCEED ----
1400 l_msg_count := FND_MSG_PUB.count_msg;
1401 if l_msg_count > 0 then
1402 if l_msg_count = 1 then
1403 PA_INTERFACE_UTILS_PUB.get_messages
1404 (p_encoded => FND_API.G_TRUE,
1405 p_msg_index => 1,
1406 p_msg_count => l_msg_count,
1407 p_msg_data => l_msg_data,
1408 p_data => l_data,
1409 p_msg_index_out => l_msg_index_out);
1410 x_msg_data := l_data;
1411 x_msg_count := l_msg_count;
1412 else
1413 x_msg_count := l_msg_count;
1414 end if;
1415 IF p_pa_debug_mode = 'Y' THEN
1416 pa_debug.reset_err_stack;
1417 END IF;
1418 return;
1419 end if;
1420
1421 ----- IF NO BUSINESS RULES VIOLATIONS, PROCEED AS USUAL -----
1422 x_return_status := FND_API.G_RET_STS_SUCCESS;
1423 savepoint before_fp_sbmt_cur_working;
1424 --dbms_output.put_line('passed all violations');
1425 -- retrieve all necessary parameters for further processing
1426 select bv.budget_type_code,
1427 bv.resource_list_id,
1428 bv.version_type,
1429 pt.project_type_class_code,
1430 opt.approved_rev_plan_type_flag,
1431 opt.approved_cost_plan_type_flag,
1432 DECODE
1433 (opt.fin_plan_preference_code,
1434 'COST_ONLY',opt.cost_time_phased_code,
1435 'REVENUE_ONLY',opt.revenue_time_phased_code,
1436 'COST_AND_REV_SAME',opt.all_time_phased_code,
1437 DECODE
1438 (bv.version_type,
1439 'COST',opt.cost_time_phased_code,
1440 'REVENUE',opt.revenue_time_phased_code
1441 )
1442 ),
1443 DECODE
1444 (opt.fin_plan_preference_code,
1445 'COST_ONLY',opt.cost_fin_plan_level_code,
1446 'REVENUE_ONLY',opt.revenue_fin_plan_level_code,
1447 'COST_AND_REV_SAME',opt.all_fin_plan_level_code,
1448 DECODE
1449 (bv.version_type,
1450 'COST',opt.cost_fin_plan_level_code,
1451 'REVENUE',opt.revenue_fin_plan_level_code
1452 )
1453 ),
1454 pavl.fin_plan_type_code,
1455 -- entry_level_code,
1456 bv.pm_product_code,
1457 /* bv.created_by, Commented for bug 6176649 */
1458 opt.fin_plan_type_id
1459 into l_budget_type_code,
1460 l_resource_list_id,
1461 l_version_type,
1462 l_project_type_class_code,
1463 l_ar_flag,
1464 l_ac_flag,
1465 l_time_phased_type_code,
1466 l_fin_plan_level_code,
1467 l_fin_plan_type_code,
1468 -- l_entry_level_code,
1469 l_pm_product_code,
1470 /* l_created_by, Commented for bug 6176649 */
1471 l_fin_plan_type_id
1472 from pa_project_types_all pt,
1473 pa_projects_all pr,
1474 pa_budget_versions bv,
1475 -- pa_budget_entry_methods be,
1476 pa_proj_fp_options opt,
1477 pa_fin_plan_types_b pavl
1478 where bv.budget_version_id = p_budget_version_id and
1479 opt.fin_plan_version_id = bv.budget_version_id and
1480 bv.project_id = pr.project_id and
1481 -- be.budget_entry_method_code = bv.budget_entry_method_code and
1482 pr.project_type = pt.project_type and
1483 --nvl(pr.org_id,-99) = nvl(pt.org_id,-99) and --Bug 5374346
1484 pr.org_id = pt.org_id and
1485 opt.fin_plan_type_id = pavl.fin_plan_type_id and
1486 opt.fin_plan_option_level_code = 'PLAN_VERSION';
1487 --dbms_output.put_line('big select statement passed');
1488 -- Verify budget rules if indicated
1489 -- This API call is different from in PA_BUDGET_CORE.baseline
1490 -- We need to set p_budget_type_code = null so that the verify API will use
1491 -- p_fin_plan_type_id and p_version_type for the new budget model
1492
1493 PA_BUDGET_UTILS.Verify_Budget_Rules
1494 (p_draft_version_id => p_budget_version_id,
1495 p_mark_as_original => l_mark_as_original,
1496 p_event => 'SUBMIT',
1497 p_project_id => p_project_id,
1498 p_budget_type_code => NULL,
1499 p_fin_plan_type_id => l_fin_plan_type_id,
1500 p_version_type => l_version_type,
1501 p_resource_list_id => l_resource_list_id,
1502 p_project_type_class_code => l_project_type_class_code,
1503 p_created_by => l_created_by,
1504 p_calling_module => 'PA_SS_FIN_PLANNING',
1505 p_warnings_only_flag => l_warnings_only_flag,
1506 p_err_msg_count => l_err_msg_count,
1507 p_err_code => l_err_code,
1508 p_err_stage => l_err_stage,
1509 p_err_stack => l_err_stack);
1510 -- if the only messages are warnings, we can proceed as usual. Otherwise,
1511 -- return with error messages
1512
1513 /* Bug 6176649: Modified the error handling to add the message name to the stack in
1514 case l_warnings_only_flag = 'N' */
1515
1516 if (l_err_msg_count > 0 ) then
1517 if (l_warnings_only_flag = 'N') then
1518
1519 x_return_status := FND_API.G_RET_STS_ERROR;
1520 x_msg_count := FND_MSG_PUB.Count_Msg;
1521 if x_msg_count = 1 then
1522 PA_INTERFACE_UTILS_PUB.get_messages
1523 (p_encoded => FND_API.G_TRUE,
1524 p_msg_index => 1,
1525 p_data => x_msg_data,
1526 p_msg_index_out => l_msg_index_out);
1527 end if;
1528 RETURN;
1529 end if;
1530 end if;
1531 --dbms_output.put_line('Verify_Budget_Rules called');
1532 --- BUDGET RULES VERIFIED AND OK, PROCEED WITH SUBMITTING CURRENT WORKING ---
1533 /* set the status_code to "S" for the version we're submitting */
1534 update pa_budget_versions
1535 set last_update_date = SYSDATE,
1536 last_updated_by = FND_GLOBAL.user_id,
1537 last_update_login = FND_GLOBAL.login_id,
1538 budget_status_code = PA_FP_CONSTANTS_PKG.G_BUDGET_STATUS_SUBMITTED, -- bug 3978894 'S',
1539 record_version_number = record_version_number+1
1540 where budget_version_id = p_budget_version_id;
1541 --dbms_output.put_line('update pa_budget_versions statement executed');
1542 -- Check whether workflow is being used for this project budget
1543 -- If so, get the employee id based on the baselined_by_user_id
1544 PA_CLIENT_EXTN_BUDGET_WF.Budget_Wf_Is_Used
1545 (p_draft_version_id => p_budget_version_id,
1546 p_project_id => p_project_id,
1547 p_budget_type_code => NULL,
1548 p_pm_product_code => l_pm_product_code,
1549 p_fin_plan_type_id => l_fin_plan_type_id,
1550 p_version_type => l_version_type,
1551 p_result => l_workflow_is_used,
1552 p_err_code => l_err_code,
1553 p_err_stage => l_err_stage,
1554 p_err_stack => l_err_stack);
1555 --dbms_output.put_line('PA_CLIENT_EXTN_BUDGET_WF.Budget_Wf_Is_Used called');
1556 -- l_err_code = 0 ==> SUCCESS
1557 if l_err_code <> 0 then
1558 -- PA_CLIENT_EXTN_BUDGET_WF.Budget_Wf_Is_Used returned errors
1559 --dbms_output.put_line('PA_CLIENT_EXTN_BUDGET_WF.Budget_Wf_Is_Used: RETURNED ERRORS');
1560 raise pa_fin_plan_pvt.check_wf_error;
1561 else
1562 If l_workflow_is_used = 'T' Then
1563 v_emp_id := pa_utils.GetEmpIdFromUser(pa_budget_wf.g_baselined_by_user_id);
1564
1565 PA_BUDGET_WF.START_BUDGET_WF
1566 (p_draft_version_id => p_budget_version_id,
1567 p_project_id => p_project_id,
1568 p_budget_type_code => NULL,
1569 p_mark_as_original => l_mark_as_original,
1570 p_fck_req_flag => NULL,
1571 p_bgt_intg_flag => NULL,
1572 p_fin_plan_type_id => l_fin_plan_type_id,
1573 p_version_type => l_version_type,
1574 p_err_code => l_err_code,
1575 p_err_stage => l_err_stage,
1576 p_err_stack => l_err_stack);
1577 --dbms_output.put_line('START_BUDGET_WF called');
1578 -- l_err_code = 0 ==> SUCCESS
1579 if l_err_code <> 0 then
1580 -- PA_BUDGET_WF.START_BUDGET_WF returned errors
1581 raise pa_fin_plan_pvt.start_wf_error;
1582 else
1583 -- update pa_budget_versions: set wf_status_code = 'IN_ROUTE'
1584 update pa_budget_versions
1585 set wf_status_code = 'IN_ROUTE'
1586 where budget_version_id = p_budget_version_id;
1587 end if;
1588 end if;
1589 end if;
1590
1591 EXCEPTION
1592 when check_wf_error then
1593 rollback to before_fp_sbmt_cur_working;
1594 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1595
1596 when start_wf_error then
1597 rollback to before_fp_sbmt_cur_working;
1598 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1599
1600 when others then
1601 rollback to before_fp_sbmt_cur_working;
1602 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1603 x_msg_count := 1;
1604 FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_FIN_PLAN_PVT',
1605 p_procedure_name => 'Submit_Current_Working_FinPlan');
1606 IF p_pa_debug_mode = 'Y' THEN
1607 pa_debug.reset_err_stack;
1608 END IF;
1609 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1610
1611 END Submit_Current_Working_FinPlan;
1612 /* ------------------------------------------------------------------------ */
1613
1614 /* get_included_ci is used in different modes as follows:
1615 p_from_bv_id -- Source version from where ci's need to be linked.
1616 p_to_bv_id -- Target version where the ci's need to be linked. This
1617 may be null if only ci's from source is required.
1618 p_impact_status -- can be NULL -- All Ci's,
1619 CI_IMPACT_IMPLEMENTED -- Implemented ci's,
1620 CI_IMPACT_PENDING -- Un-implemented ci's.
1621
1622 When both p_from_bv_id and p_to_bv_id are passed then the resultant
1623 table x_ci_rec_tab is a table of ci's linked in p_from_bv_id version and
1624 not in p_to_bv_id version.
1625
1626 Bug 3106741 For better performance the cursoe has been spilt into c1 and c2.
1627 Cursor c1 would be opened if p_to_bv_id is not null.
1628 Cursor c2 would be opened if p_to_bv_id is null.
1629
1630 Modified for 3550073. Selected the amount columns in pa_fp_merged_ctrl_items in the
1631 cursors
1632 */
1633
1634 PROCEDURE Get_Included_Ci
1635 ( p_from_bv_id IN pa_budget_versions.budget_version_id%TYPE
1636 ,p_to_bv_id IN pa_budget_versions.budget_version_id%TYPE --DEFAULT NULL
1637 ,p_impact_status IN pa_ci_impacts.status_code%TYPE
1638 ,x_ci_rec_tab OUT NOCOPY pa_fin_plan_pvt.ci_rec_tab
1639 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1640 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
1641 ,x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
1642
1643 IS
1644
1645 cursor c1 is
1646 select f.ci_id, f.ci_plan_version_id, im.ci_impact_id ,
1647 f.version_type,
1648 f.impl_proj_func_raw_cost ,
1649 f.impl_proj_func_burdened_cost,
1650 f.impl_proj_func_revenue,
1651 f.impl_proj_raw_cost ,
1652 f.impl_proj_burdened_cost,
1653 f.impl_proj_revenue,
1654 decode(f.version_type,'COST',f.impl_quantity,NULL) impl_cost_ppl_qty,
1655 decode(f.version_type,'COST',f.impl_equipment_quantity,NULL) impl_cost_equip_qty,
1656 decode(f.version_type,'REVENUE',f.impl_quantity,NULL) impl_rev_ppl_qty,
1657 decode(f.version_type,'REVENUE',f.impl_equipment_quantity,NULL) impl_rev_equip_qty,
1658 f.impl_agr_revenue impl_agr_revenue,
1659 pbv.rev_partially_impl_flag rev_partially_impl_flag
1660 from pa_fp_merged_ctrl_items f,
1661 pa_ci_impacts im ,
1662 pa_budget_versions pbv
1663 where f.plan_version_id = p_from_bv_id
1664 and pbv.budget_version_id=f.ci_plan_version_id
1665 and im.ci_id = f.ci_id
1666 and im.impact_type_code IN ('FINPLAN_COST','FINPLAN_REVENUE')
1667 and im.status_code = nvl(p_impact_status,im.status_code)
1668 and decode(im.impact_type_code,
1669 'FINPLAN_COST','COST',
1670 'FINPLAN_REVENUE','REVENUE') = f.version_type
1671 and f.project_id=pbv.project_id
1672 and not exists
1673 (select 'x' from pa_fp_merged_ctrl_items t
1674 where t.plan_version_id = p_to_bv_id
1675 and t.ci_id = f.ci_id
1676 and f.version_type = t.version_type
1677 and t.ci_plan_version_id = f.ci_plan_version_id
1678 and t.project_id=f.project_id);
1679 --and p_to_bv_id IS NOT NULL;
1680 cursor c2 is
1681 select f.ci_id, f.ci_plan_version_id, im.ci_impact_id ,
1682 f.version_type,
1683 f.impl_proj_func_raw_cost ,
1684 f.impl_proj_func_burdened_cost,
1685 f.impl_proj_func_revenue,
1686 f.impl_proj_raw_cost ,
1687 f.impl_proj_burdened_cost,
1688 f.impl_proj_revenue,
1689 decode(f.version_type,'COST',f.impl_quantity,NULL) impl_cost_ppl_qty,
1690 decode(f.version_type,'COST',f.impl_equipment_quantity,NULL) impl_cost_equip_qty,
1691 decode(f.version_type,'REVENUE',f.impl_quantity,NULL) impl_rev_ppl_qty,
1692 decode(f.version_type,'REVENUE',f.impl_equipment_quantity,NULL) impl_rev_equip_qty,
1693 f.impl_agr_revenue impl_agr_revenue,
1694 pbv.rev_partially_impl_flag rev_partially_impl_flag
1695 from pa_fp_merged_ctrl_items f,
1696 pa_ci_impacts im,
1697 pa_budget_versions pbv
1698 where f.plan_version_id = p_from_bv_id
1699 and pbv.budget_version_id=f.ci_plan_version_id
1700 and im.ci_id = f.ci_id
1701 and im.impact_type_code IN ('FINPLAN_COST','FINPLAN_REVENUE')
1702 and decode(im.impact_type_code,
1703 'FINPLAN_COST','COST',
1704 'FINPLAN_REVENUE','REVENUE') = f.version_type
1705 and im.status_code = nvl(p_impact_status,im.status_code)
1706 and f.project_id=pbv.project_id;
1707 --and p_to_bv_id IS NULL;
1708
1709 l_msg_index_out number;
1710
1711 BEGIN
1712
1713 x_return_status := FND_API.G_RET_STS_SUCCESS;
1714
1715 x_ci_rec_tab.delete;
1716
1717 If p_to_bv_id is not null then
1718
1719 Open c1;
1720
1721 Loop
1722 Fetch c1 into x_ci_rec_tab(nvl(x_ci_rec_tab.last,0)+1).ci_id,
1723 x_ci_rec_tab(nvl(x_ci_rec_tab.last,0)+1).ci_plan_version_id,
1724 x_ci_rec_tab(nvl(x_ci_rec_tab.last,0)+1).ci_impact_id,
1725 x_ci_rec_tab(nvl(x_ci_rec_tab.last,0)+1).version_type,
1726 x_ci_rec_tab(nvl(x_ci_rec_tab.last,0)+1).impl_pfc_raw_cost,
1727 x_ci_rec_tab(nvl(x_ci_rec_tab.last,0)+1).impl_pfc_burd_cost,
1728 x_ci_rec_tab(nvl(x_ci_rec_tab.last,0)+1).impl_pfc_revenue,
1729 x_ci_rec_tab(nvl(x_ci_rec_tab.last,0)+1).impl_pc_raw_cost,
1730 x_ci_rec_tab(nvl(x_ci_rec_tab.last,0)+1).impl_pc_burd_cost,
1731 x_ci_rec_tab(nvl(x_ci_rec_tab.last,0)+1).impl_pc_revenue,
1732 x_ci_rec_tab(nvl(x_ci_rec_tab.last,0)+1).impl_cost_ppl_qty,
1733 x_ci_rec_tab(nvl(x_ci_rec_tab.last,0)+1).impl_cost_equip_qty,
1734 x_ci_rec_tab(nvl(x_ci_rec_tab.last,0)+1).impl_rev_ppl_qty,
1735 x_ci_rec_tab(nvl(x_ci_rec_tab.last,0)+1).impl_rev_equip_qty,
1736 x_ci_rec_tab(nvl(x_ci_rec_tab.last,0)+1).impl_agr_revenue,
1737 x_ci_rec_tab(nvl(x_ci_rec_tab.last,0)+1).rev_partially_impl_flag;
1738 Exit when c1%NOTFOUND;
1739 End Loop;
1740
1741 Close c1;
1742 Else
1743 Open c2;
1744
1745 Loop
1746 Fetch c2 into x_ci_rec_tab(nvl(x_ci_rec_tab.last,0)+1).ci_id,
1747 x_ci_rec_tab(nvl(x_ci_rec_tab.last,0)+1).ci_plan_version_id,
1748 x_ci_rec_tab(nvl(x_ci_rec_tab.last,0)+1).ci_impact_id,
1749 x_ci_rec_tab(nvl(x_ci_rec_tab.last,0)+1).version_type,
1750 x_ci_rec_tab(nvl(x_ci_rec_tab.last,0)+1).impl_pfc_raw_cost,
1751 x_ci_rec_tab(nvl(x_ci_rec_tab.last,0)+1).impl_pfc_burd_cost,
1752 x_ci_rec_tab(nvl(x_ci_rec_tab.last,0)+1).impl_pfc_revenue,
1753 x_ci_rec_tab(nvl(x_ci_rec_tab.last,0)+1).impl_pc_raw_cost,
1754 x_ci_rec_tab(nvl(x_ci_rec_tab.last,0)+1).impl_pc_burd_cost,
1755 x_ci_rec_tab(nvl(x_ci_rec_tab.last,0)+1).impl_pc_revenue,
1756 x_ci_rec_tab(nvl(x_ci_rec_tab.last,0)+1).impl_cost_ppl_qty,
1757 x_ci_rec_tab(nvl(x_ci_rec_tab.last,0)+1).impl_cost_equip_qty,
1758 x_ci_rec_tab(nvl(x_ci_rec_tab.last,0)+1).impl_rev_ppl_qty,
1759 x_ci_rec_tab(nvl(x_ci_rec_tab.last,0)+1).impl_rev_equip_qty,
1760 x_ci_rec_tab(nvl(x_ci_rec_tab.last,0)+1).impl_agr_revenue,
1761 x_ci_rec_tab(nvl(x_ci_rec_tab.last,0)+1).rev_partially_impl_flag;
1762
1763 Exit when c2%NOTFOUND;
1764 End Loop;
1765
1766 Close c2;
1767
1768 End if;
1769
1770 x_return_status := FND_API.G_RET_STS_SUCCESS;
1771 x_msg_count := FND_MSG_PUB.Count_Msg;
1772
1773 EXCEPTION WHEN OTHERS THEN
1774 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1775 x_msg_count := 1;
1776 x_msg_data := substr(SQLERRM,1,240);
1777
1778 fnd_msg_pub.add_exc_msg
1779 ( p_pkg_name => 'pa_fin_plan_pvt'
1780 ,p_procedure_name => 'Get_Included_Ci'
1781 ,p_error_text => substr(SQLERRM,1,240));
1782
1783 END Get_Included_Ci;
1784
1785 --Modified for 3550073. Handled the FINPLAN_COST, FINPLAN_REVENUE records in pa_ci_impacts
1786 --Reset the rev_partially_impl flag for the CIs partially implemented in the old current working version
1787 PROCEDURE handle_ci_links
1788 ( p_source_bv_id IN pa_budget_versions.budget_version_id%TYPE
1789 ,p_target_bv_id IN pa_budget_versions.budget_version_id%TYPE
1790 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1791 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
1792 ,x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
1793
1794 IS
1795
1796 cursor c1 is
1797 select 'Y',
1798 bv.project_id,
1799 bv.fin_plan_type_id,
1800 bv.version_type
1801 from pa_fin_plan_types_b pt
1802 ,pa_budget_versions bv
1803 where pt.fin_plan_type_id = bv.fin_plan_type_id
1804 and bv.budget_version_id = p_target_bv_id
1805 and (pt.approved_cost_plan_type_flag = 'Y'
1806 OR
1807 pt.approved_rev_plan_type_flag = 'Y');
1808
1809 l_approved_budget_plan_type varchar2(1) := 'N';
1810 l_project_id pa_projects_all.project_id%TYPE;
1811 l_fin_plan_type_id pa_budget_versions.fin_plan_type_id%TYPE;
1812 l_version_type pa_budget_versions.version_type%TYPE;
1813 l_fp_options_id pa_proj_fp_options.proj_fp_options_id%TYPE;
1814 l_fin_plan_version_id pa_proj_fp_options.fin_plan_version_id%TYPE;
1815 l_ci_rec_tab pa_fin_plan_pvt.ci_rec_tab;
1816 l_return_status VARCHAR2(2000);
1817 l_msg_count NUMBER;
1818 l_msg_data VARCHAR2(2000);
1819 l_msg_index_out number;
1820 l_stage NUMBER := null;
1821 l_impact_type_code pa_ci_impacts.impact_type_code%TYPE;
1822
1823 BEGIN
1824 x_return_status := FND_API.G_RET_STS_SUCCESS;
1825 l_stage := 10;
1826 open c1;
1827 l_stage := 20;
1828 fetch c1 into l_approved_budget_plan_type,
1829 l_project_id,
1830 l_fin_plan_type_id,
1831 l_version_type;
1832
1833 IF c1%notfound then
1834 l_stage := 30;
1835 l_approved_budget_plan_type := 'N';
1836 END IF;
1837 close c1;
1838
1839 l_stage := 40;
1840
1841 IF l_approved_budget_plan_type = 'Y' then
1842 l_stage := 50;
1843 /* Get latest baselined info */
1844 pa_fin_plan_utils.Get_Baselined_Version_Info(
1845 p_project_id => l_project_id
1846 ,p_fin_plan_type_id => l_fin_plan_type_id
1847 ,p_version_type => l_version_type
1848 ,x_fp_options_id => l_fp_options_id
1849 ,x_fin_plan_version_id => l_fin_plan_version_id
1850 ,x_return_status => l_return_status
1851 ,x_msg_count => l_msg_count
1852 ,x_msg_data => l_msg_data);
1853
1854 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1855 fnd_msg_pub.add_exc_msg
1856 ( p_pkg_name => 'pa_fin_plan_pvt'
1857 ,p_procedure_name => 'handle_ci_links'
1858 ,p_error_text => to_char(l_stage)||': '||substr(SQLERRM,1,240));
1859 ELSE
1860 /* Get ci linked to latest baselined versions and not already present
1861 in target version */
1862 l_stage := 60;
1863
1864 IF l_fin_plan_version_id IS NOT NULL THEN
1865
1866 l_stage := 70;
1867 l_ci_rec_tab.delete;
1868
1869 pa_fin_plan_pvt.Get_Included_Ci(
1870 p_from_bv_id => l_fin_plan_version_id /* Baselined Version */
1871 , p_to_bv_id => p_target_bv_id /* New Current Working */
1872 , p_impact_status => NULL
1873 , x_ci_rec_tab => l_ci_rec_tab
1874 , x_return_status => l_return_status
1875 , x_msg_count => l_msg_count
1876 , x_msg_data => l_msg_data);
1877
1878
1879 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1880 fnd_msg_pub.add_exc_msg
1881 ( p_pkg_name => 'pa_fin_plan_pvt'
1882 ,p_procedure_name => 'handle_ci_links'
1883 ,p_error_text => to_char(l_stage)||': '||substr(SQLERRM,1,240));
1884 ELSE
1885 l_stage := 80;
1886 IF l_ci_rec_tab.count > 0 THEN
1887 l_stage := 90;
1888 FOR i in 1..l_ci_rec_tab.count LOOP
1889 l_stage := 100;
1890
1891 pa_fp_ci_merge.FP_CI_LINK_CONTROL_ITEMS(
1892 p_project_id => l_project_id
1893 ,p_s_fp_version_id => l_ci_rec_tab(i).ci_plan_version_id
1894 ,p_t_fp_version_id => p_target_bv_id
1895 ,p_inclusion_method => 'COPIED'
1896 --Added for bug 3550073
1897 ,p_version_type => l_ci_rec_tab(i).version_type
1898 ,p_ci_id => l_ci_rec_tab(i).ci_id
1899 ,p_cost_ppl_qty => l_ci_rec_tab(i).impl_cost_ppl_qty
1900 ,p_rev_ppl_qty => l_ci_rec_tab(i).impl_rev_ppl_qty
1901 ,p_cost_equip_qty => l_ci_rec_tab(i).impl_cost_equip_qty
1902 ,p_rev_equip_qty => l_ci_rec_tab(i).impl_rev_equip_qty
1903 ,p_impl_pfc_raw_cost => l_ci_rec_tab(i).impl_pfc_raw_cost
1904 ,p_impl_pfc_revenue => l_ci_rec_tab(i).impl_pfc_revenue
1905 ,p_impl_pfc_burd_cost => l_ci_rec_tab(i).impl_pfc_burd_cost
1906 ,p_impl_pc_raw_cost => l_ci_rec_tab(i).impl_pc_raw_cost
1907 ,p_impl_pc_revenue => l_ci_rec_tab(i).impl_pc_revenue
1908 ,p_impl_pc_burd_cost => l_ci_rec_tab(i).impl_pc_burd_cost
1909 ,p_impl_agr_revenue => l_ci_rec_tab(i).impl_agr_revenue
1910 ,x_return_status => l_return_status
1911 ,x_msg_count => l_msg_count
1912 ,x_msg_data => l_msg_data
1913 ) ;
1914
1915 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1916 fnd_msg_pub.add_exc_msg
1917 ( p_pkg_name => 'pa_fin_plan_pvt'
1918 ,p_procedure_name => 'handle_ci_links'
1919 ,p_error_text => to_char(l_stage)||': '||substr(SQLERRM,1,240));
1920 END IF; -- l_return_status <> FND_API.G_RET_STS_SUCCESS
1921 END LOOP;
1922 END IF; -- l_ci_rec_tab.count > 0
1923 END IF; -- l_return_status <> FND_API.G_RET_STS_SUCCESS
1924 END IF; -- l_fin_plan_version_id IS NOT NULL
1925 END IF; -- l_return_status <> FND_API.G_RET_STS_SUCCESS
1926
1927 /* Get CI from Old Current Working and not there in New Current Working
1928 and change their status to un-implemented - At this stage New Current Working
1929 contains the links to ci from Latest Baselined version */
1930 l_stage := 110;
1931 l_ci_rec_tab.delete;
1932
1933 pa_fin_plan_pvt.Get_Included_Ci(
1934 p_from_bv_id => p_source_bv_id /* Old Current Working */
1935 , p_to_bv_id => p_target_bv_id /* New Current Working */
1936 , p_impact_status => 'CI_IMPACT_IMPLEMENTED'
1937 , x_ci_rec_tab => l_ci_rec_tab
1938 , x_return_status => l_return_status
1939 , x_msg_count => l_msg_count
1940 , x_msg_data => l_msg_data);
1941
1942
1943 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1944 fnd_msg_pub.add_exc_msg
1945 ( p_pkg_name => 'pa_fin_plan_pvt'
1946 ,p_procedure_name => 'handle_ci_links'
1947 ,p_error_text => to_char(l_stage)||': '||substr(SQLERRM,1,240));
1948 ELSE
1949 l_stage := 120;
1950 IF l_ci_rec_tab.count > 0 THEN
1951 l_stage := 130;
1952 FOR i in 1..l_ci_rec_tab.count LOOP
1953 l_stage := 140;
1954
1955 IF l_ci_rec_tab(i).version_type='COST' THEN
1956 l_impact_type_code:='FINPLAN_COST';
1957 ELSIF l_ci_rec_tab(i).version_type='REVENUE' THEN
1958 l_impact_type_code:='FINPLAN_REVENUE';
1959 END IF;
1960
1961 --If the CI has got partially implemented in the old current working version then the
1962 --reset the rev_partially_impl_flag in the ci version
1963 IF l_ci_rec_tab(i).rev_partially_impl_flag ='Y' THEN
1964
1965 UPDATE pa_budget_versions
1966 SET rev_partially_impl_flag='N',
1967 record_version_number=nvl(record_version_number,0)+1,
1968 last_updated_by=fnd_global.user_id,
1969 last_update_login=fnd_global.login_id,
1970 last_update_date=sysdate
1971 WHERE budget_Version_id=l_ci_rec_tab(i).ci_plan_version_id;
1972
1973 END IF;
1974
1975 pa_fp_ci_merge.fp_ci_update_impact
1976 ( p_ci_id => l_ci_rec_tab(i).ci_id
1977 ,p_status_code => 'CI_IMPACT_PENDING'
1978 ,p_impact_type_code => l_impact_type_code
1979 --,p_record_version_number => l_ci_rec_tab(i).record_version_number
1980 ,x_return_status => l_return_status
1981 ,x_msg_count => l_msg_count
1982 ,x_msg_data => l_msg_data);
1983
1984 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1985 fnd_msg_pub.add_exc_msg
1986 ( p_pkg_name => 'pa_fin_plan_pvt'
1987 ,p_procedure_name => 'handle_ci_links'
1988 ,p_error_text => to_char(l_stage)||': '||substr(SQLERRM,1,240));
1989 END IF; -- l_return_status <> FND_API.G_RET_STS_SUCCESS
1990 END LOOP;
1991 END IF; -- l_ci_rec_tab.count > 0
1992 END IF; -- l_return_status <> FND_API.G_RET_STS_SUCCESS
1993
1994
1995 /* Get CI from New Current Working which have not been implemented
1996 and change their status to implemented */
1997 l_stage := 150;
1998 l_ci_rec_tab.delete;
1999
2000 pa_fin_plan_pvt.Get_Included_Ci(
2001 p_from_bv_id => p_target_bv_id /* New Current Working */
2002 , p_to_bv_id => NULL
2003 , p_impact_status => 'CI_IMPACT_PENDING'
2004 , x_ci_rec_tab => l_ci_rec_tab
2005 , x_return_status => l_return_status
2006 , x_msg_count => l_msg_count
2007 , x_msg_data => l_msg_data);
2008
2009
2010 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2011 fnd_msg_pub.add_exc_msg
2012 ( p_pkg_name => 'pa_fin_plan_pvt'
2013 ,p_procedure_name => 'handle_ci_links'
2014 ,p_error_text => to_char(l_stage)||': '||substr(SQLERRM,1,240));
2015 ELSE
2016 l_stage := 160;
2017 IF l_ci_rec_tab.count > 0 THEN
2018 l_stage := 170;
2019 FOR i in 1..l_ci_rec_tab.count LOOP
2020 l_stage := 180;
2021
2022 IF l_ci_rec_tab(i).version_type='COST' THEN
2023 l_impact_type_code:='FINPLAN_COST';
2024 ELSIF l_ci_rec_tab(i).version_type='REVENUE' THEN
2025 l_impact_type_code:='FINPLAN_REVENUE';
2026 END IF;
2027
2028 pa_fp_ci_merge.fp_ci_update_impact
2029 ( p_ci_id => l_ci_rec_tab(i).ci_id
2030 ,p_status_code => 'CI_IMPACT_IMPLEMENTED'
2031 ,p_impact_type_code => l_impact_type_code
2032 --,p_record_version_number => l_ci_rec_tab(i).record_version_number
2033 ,x_return_status => l_return_status
2034 ,x_msg_count => l_msg_count
2035 ,x_msg_data => l_msg_data);
2036
2037 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2038 fnd_msg_pub.add_exc_msg
2039 ( p_pkg_name => 'pa_fin_plan_pvt'
2040 ,p_procedure_name => 'handle_ci_links'
2041 ,p_error_text => to_char(l_stage)||': '||substr(SQLERRM,1,240));
2042 END IF; -- l_return_status <> FND_API.G_RET_STS_SUCCESS
2043 END LOOP;
2044 END IF; -- l_ci_rec_tab.count > 0
2045 END IF; -- l_return_status <> FND_API.G_RET_STS_SUCCESS
2046
2047 END IF; -- l_approved_budget_plan_type = 'Y'
2048 x_return_status := FND_API.G_RET_STS_SUCCESS;
2049 x_msg_count := FND_MSG_PUB.Count_Msg;
2050
2051 EXCEPTION WHEN NO_DATA_FOUND THEN
2052 l_approved_budget_plan_type := 'N';
2053 WHEN OTHERS THEN
2054 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2055 x_msg_count := FND_MSG_PUB.Count_Msg;
2056 IF x_msg_count = 1 THEN
2057 pa_interface_utils_pub.get_messages ( p_encoded => FND_API.G_TRUE
2058 ,p_msg_index => 1
2059 ,p_data => x_msg_data
2060 ,p_msg_index_out => l_msg_index_out);
2061 END IF;
2062 fnd_msg_pub.add_exc_msg
2063 ( p_pkg_name => 'pa_fin_plan_pvt'
2064 ,p_procedure_name => 'handle_ci_links'
2065 ,p_error_text => to_char(l_stage)||': '||substr(SQLERRM,1,240));
2066
2067 fnd_msg_pub.count_and_get (p_count => x_msg_count,
2068 p_data => x_msg_data);
2069 END handle_ci_links;
2070
2071 /*
2072 This api uses the input parameters to create a project finplanning option for a plan
2073 version and also creates the finplan budget version. If any of the nullable parameters
2074 are not passed, the plan type options are used for the plan version finplan option. This
2075 API is similar to pa_budget_utils.create_draft
2076 */
2077
2078 PROCEDURE CREATE_DRAFT
2079 ( p_project_id IN pa_budget_versions.project_id%TYPE
2080 ,p_fin_plan_type_id IN pa_budget_versions.fin_plan_type_id%TYPE
2081 ,p_version_type IN pa_budget_versions.version_type%TYPE
2082 -- Bug Fix: 4569365. Removed MRC code.
2083 -- ,p_calling_context IN pa_mrc_finplan.g_calling_module%TYPE
2084 ,p_calling_context IN VARCHAR2
2085 ,p_time_phased_code IN pa_proj_fp_options.cost_time_phased_code%TYPE
2086 ,p_resource_list_id IN pa_budget_versions.resource_list_id%TYPE
2087 ,p_fin_plan_level_code IN pa_proj_fp_options.cost_fin_plan_level_code%TYPE
2088 ,p_plan_in_mc_flag IN pa_proj_fp_options.plan_in_multi_curr_flag%TYPE
2089 ,p_version_name IN pa_budget_versions.version_name%TYPE
2090 ,p_description IN pa_budget_versions.description%TYPE
2091 ,p_change_reason_code IN pa_budget_versions.change_reason_code%TYPE
2092 ,p_raw_cost_flag IN pa_fin_plan_amount_sets.raw_cost_flag%TYPE
2093 ,p_burdened_cost_flag IN pa_fin_plan_amount_sets.burdened_cost_flag%TYPE
2094 ,p_revenue_flag IN pa_fin_plan_amount_sets.revenue_flag%TYPE
2095 ,p_cost_qty_flag IN pa_fin_plan_amount_sets.cost_qty_flag%TYPE
2096 ,p_revenue_qty_flag IN pa_fin_plan_amount_sets.revenue_qty_flag%TYPE
2097 ,p_all_qty_flag IN pa_fin_plan_amount_sets.all_qty_flag%TYPE
2098 ,p_attribute_category IN pa_budget_versions.attribute_category%TYPE
2099 ,p_attribute1 IN pa_budget_versions.attribute1%TYPE
2100 ,p_attribute2 IN pa_budget_versions.attribute2%TYPE
2101 ,p_attribute3 IN pa_budget_versions.attribute3%TYPE
2102 ,p_attribute4 IN pa_budget_versions.attribute4%TYPE
2103 ,p_attribute5 IN pa_budget_versions.attribute5%TYPE
2104 ,p_attribute6 IN pa_budget_versions.attribute6%TYPE
2105 ,p_attribute7 IN pa_budget_versions.attribute7%TYPE
2106 ,p_attribute8 IN pa_budget_versions.attribute8%TYPE
2107 ,p_attribute9 IN pa_budget_versions.attribute9%TYPE
2108 ,p_attribute10 IN pa_budget_versions.attribute10%TYPE
2109 ,p_attribute11 IN pa_budget_versions.attribute11%TYPE
2110 ,p_attribute12 IN pa_budget_versions.attribute12%TYPE
2111 ,p_attribute13 IN pa_budget_versions.attribute13%TYPE
2112 ,p_attribute14 IN pa_budget_versions.attribute14%TYPE
2113 ,p_attribute15 IN pa_budget_versions.attribute15%TYPE
2114 ,p_projfunc_cost_rate_type IN pa_proj_fp_options.projfunc_cost_rate_type%TYPE
2115 ,p_projfunc_cost_rate_date_type IN pa_proj_fp_options.projfunc_cost_rate_date_type%TYPE
2116 ,p_projfunc_cost_rate_date IN pa_proj_fp_options.projfunc_cost_rate_date%TYPE
2117 ,p_projfunc_rev_rate_type IN pa_proj_fp_options.projfunc_rev_rate_type%TYPE
2118 ,p_projfunc_rev_rate_date_type IN pa_proj_fp_options.projfunc_rev_rate_date_type%TYPE
2119 ,p_projfunc_rev_rate_date IN pa_proj_fp_options.projfunc_rev_rate_date%TYPE
2120 ,p_project_cost_rate_type IN pa_proj_fp_options.project_cost_rate_type%TYPE
2121 ,p_project_cost_rate_date_type IN pa_proj_fp_options.project_cost_rate_date_type%TYPE
2122 ,p_project_cost_rate_date IN pa_proj_fp_options.project_cost_rate_date%TYPE
2123 ,p_project_rev_rate_type IN pa_proj_fp_options.project_rev_rate_type%TYPE
2124 ,p_project_rev_rate_date_type IN pa_proj_fp_options.project_rev_rate_date_type%TYPE
2125 ,p_project_rev_rate_date IN pa_proj_fp_options.project_rev_rate_date%TYPE
2126 ,p_pm_product_code IN pa_budget_versions.pm_product_code%TYPE
2127 ,p_pm_budget_reference IN pa_budget_versions.pm_budget_reference%TYPE
2128 ,p_budget_lines_tab IN pa_fin_plan_pvt.budget_lines_tab
2129 -- Start of additional columns for Bug :- 2634900 . Commented out the default parameters.
2130 ,p_ci_id IN pa_budget_versions.ci_id%TYPE --:= NULL
2131 ,p_est_proj_raw_cost IN pa_budget_versions.est_project_raw_cost%TYPE --:= NULL
2132 ,p_est_proj_bd_cost IN pa_budget_versions.est_project_burdened_cost%TYPE--:= NULL
2133 ,p_est_proj_revenue IN pa_budget_versions.est_project_revenue%TYPE --:= NULL
2134 ,p_est_qty IN pa_budget_versions.est_quantity%TYPE --:= NULL
2135 ,p_est_equip_qty IN pa_budget_versions.est_equipment_quantity%TYPE --FP.M
2136 ,p_impacted_task_id IN pa_tasks.task_id%TYPE --:= NULL
2137 ,p_agreement_id IN pa_budget_versions.agreement_id%TYPE --:= NULL
2138 -- End of additional columns for Bug :- 2634900
2139 -- Added the two flags below as part of changes to AMG for finplan model
2140 ,p_create_new_curr_working_flag IN VARCHAR2
2141 ,p_replace_current_working_flag IN VARCHAR2
2142 ,x_budget_version_id OUT NOCOPY pa_budget_versions.budget_version_id%TYPE --File.Sql.39 bug 4440895
2143 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2144 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
2145 ,x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
2146 IS
2147
2148 l_msg_count NUMBER := 0;
2149 l_data VARCHAR2(2000);
2150 l_msg_data VARCHAR2(2000);
2151 l_error_msg_code VARCHAR2(30);
2152 l_msg_index_out NUMBER;
2153 l_return_status VARCHAR2(1);
2154 l_debug_mode VARCHAR2(30);
2155
2156 CURSOR impacted_task_cur(c_impacted_task_id pa_tasks.task_id%TYPE) IS
2157 SELECT pt.parent_task_id parent_task_id,
2158 pt.top_task_id top_task_id ,
2159 pelm.element_Version_id element_Version_id
2160 FROM pa_tasks pt,
2161 pa_proj_element_versions pelm
2162 WHERE pt.task_id = c_impacted_task_id
2163 AND pelm.proj_element_id=pt.task_id
2164 AND pelm.parent_structure_version_id=PA_PROJECT_STRUCTURE_UTILS.GET_FIN_STRUC_VER_ID( p_project_id);
2165
2166 impacted_task_rec impacted_task_cur%ROWTYPE;
2167
2168 l_fp_options_id pa_proj_fp_options.proj_fp_options_id%TYPE;
2169 l_baselined_version_id pa_budget_versions.budget_version_id%TYPE;
2170 l_curr_work_version_id pa_budget_versions.budget_version_id%TYPE;
2171 l_amount_set_id pa_fin_plan_amount_sets.fin_plan_amount_set_id%TYPE;
2172
2173 l_cost_amount_set_id pa_fin_plan_amount_sets.fin_plan_amount_set_id%TYPE;
2174 l_rev_amount_set_id pa_fin_plan_amount_sets.fin_plan_amount_set_id%TYPE;
2175 l_all_amount_set_id pa_fin_plan_amount_sets.fin_plan_amount_set_id%TYPE;
2176 l_created_version_id pa_budget_versions.budget_version_id%TYPE;
2177 l_resource_list_id pa_budget_versions.resource_list_id%TYPE;
2178 l_plan_pref_code pa_proj_fp_options.fin_plan_preference_code%TYPE;
2179
2180 l_uncat_rlmid pa_resource_assignments.resource_list_member_id%TYPE;
2181 l_track_as_labor_flag pa_resource_list_members.track_as_labor_flag%TYPE;
2182 l_unit_of_measure pa_resource_assignments.unit_of_measure%TYPE;
2183
2184 l_ci_rec_tab pa_fin_plan_pvt.ci_rec_tab; /* Included for bug 2672654 */
2185
2186 -- Bug Fix: 4569365. Removed MRC code.
2187 -- l_calling_context pa_mrc_finplan.g_calling_module%TYPE; /* Bug# 2674353 */
2188 l_calling_context VARCHAR2(30);
2189 l_record_version_number pa_budget_versions.record_version_number%TYPE; /* Bug 2688610 */
2190
2191 l_mixed_resource_planned_flag VARCHAR2(1);-- Added for Bug:-2625872
2192
2193 l_proj_fp_options_id pa_proj_fp_options.proj_fp_options_id%TYPE;
2194 l_CW_version_id pa_budget_versions.budget_version_id%TYPE;
2195 l_CW_record_version_number pa_budget_versions.record_version_number%TYPE;
2196 l_user_id NUMBER :=0;
2197 l_created_ver_rec_ver_num pa_budget_versions.record_version_number%TYPE;
2198 l_task_elem_version_id_tbl SYSTEM.pa_num_tbl_type:=SYSTEM.pa_num_tbl_type();
2199 l_rlm_id_tbl SYSTEM.pa_num_tbl_type:=SYSTEM.pa_num_tbl_type();
2200 BEGIN
2201
2202 x_msg_count := 0;
2203 x_return_status := FND_API.G_RET_STS_SUCCESS;
2204 IF p_pa_debug_mode = 'Y' THEN
2205 pa_debug.set_err_stack('PA_FIN_PLAN_PVT.CREATE_DRAFT');
2206 END IF;
2207 fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
2208 l_debug_mode := NVL(l_debug_mode, 'Y');
2209 IF P_PA_DEBUG_MODE = 'Y' THEN
2210 pa_debug.set_process('CREATE_DRAFT: ' || 'PLSQL','LOG',l_debug_mode);
2211 END IF;
2212
2213
2214 pa_debug.g_err_stage:='Entering Create_draft';
2215 IF P_PA_DEBUG_MODE = 'Y' THEN
2216 pa_debug.write('CREATE_DRAFT: ' || g_module_name,pa_debug.g_err_stage,2);
2217 END IF;
2218
2219 l_calling_context := nvl(p_calling_context,PA_FP_CONSTANTS_PKG.G_CREATE_DRAFT); /* Bug# 2674353 */
2220 l_resource_list_id := p_resource_list_id; --bug#2831968
2221
2222 IF p_ci_id is NULL THEN -- Bug # 2672654
2223
2224 pa_debug.g_err_stage:='Control item id is null';
2225 IF P_PA_DEBUG_MODE = 'Y' THEN
2226 pa_debug.write('CREATE_DRAFT: ' || g_module_name,pa_debug.g_err_stage,3);
2227 END IF;
2228
2229 /* Validation for the resource list */
2230 /* Get the currently baselined version id */
2231 pa_fin_plan_utils.Get_Baselined_Version_Info(
2232 p_project_id => p_project_id
2233 ,p_fin_plan_type_id => p_fin_plan_type_id
2234 ,p_version_type => p_version_type
2235 ,x_fp_options_id => l_fp_options_id
2236 ,x_fin_plan_version_id => l_baselined_version_id
2237 ,x_return_status => x_return_status
2238 ,x_msg_count => x_msg_count
2239 ,x_msg_data => x_msg_data );
2240
2241 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2242 pa_debug.g_err_stage:= 'Error Calling Get_Baselined_Version_Info';
2243 IF P_PA_DEBUG_MODE = 'Y' THEN
2244 pa_debug.write('CREATE_DRAFT: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL5);
2245 END IF;
2246 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
2247 END IF;
2248
2249 pa_debug.g_err_stage:= 'current Baselined Version id ->' || l_baselined_version_id;
2250 IF P_PA_DEBUG_MODE = 'Y' THEN
2251 pa_debug.write('CREATE_DRAFT: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL5);
2252 END IF;
2253 -- bug#2831968 l_resource_list_id := p_resource_list_id;
2254
2255 /* Commenting for bug 8367755
2256 IF (l_baselined_version_id IS NOT NULL) THEN
2257 IF (pa_fin_plan_utils.Get_Resource_List_Id(l_baselined_version_id) <> l_resource_list_id) THEN
2258 pa_debug.g_err_stage:='Current baselined versions and passed resource list id are not same';
2259 IF P_PA_DEBUG_MODE = 'Y' THEN
2260 pa_debug.write('CREATE_DRAFT: ' || g_module_name,pa_debug.g_err_stage,5);
2261 END IF;
2262
2263 PA_UTILS.ADD_MESSAGE(p_app_short_name=> 'PA',
2264 p_msg_name => 'PA_BU_BASE_RES_LIST_EXISTS');
2265 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
2266 END IF;
2267 END IF;
2268 */
2269
2270 pa_debug.g_err_stage:='Entering of validation for resource list';
2271 IF P_PA_DEBUG_MODE = 'Y' THEN
2272 pa_debug.write('CREATE_DRAFT: ' || g_module_name,pa_debug.g_err_stage,3);
2273 END IF;
2274
2275 /* End of validation for resource list */
2276
2277 /* Get draft version */
2278
2279 pa_fin_plan_utils.Get_Curr_Working_Version_Info(
2280 p_project_id => p_project_id
2281 ,p_fin_plan_type_id => p_fin_plan_type_id
2282 ,p_version_type => p_version_type
2283 ,x_fp_options_id => l_fp_options_id
2284 ,x_fin_plan_version_id => l_curr_work_version_id
2285 ,x_return_status => x_return_status
2286 ,x_msg_count => x_msg_count
2287 ,x_msg_data => x_msg_data );
2288
2289 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2290 Raise PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
2291 END IF;
2292
2293 pa_debug.g_err_stage:='Current working version id -> ' || l_curr_work_version_id;
2294 IF P_PA_DEBUG_MODE = 'Y' THEN
2295 pa_debug.write('CREATE_DRAFT: ' || g_module_name,pa_debug.g_err_stage,3);
2296 END IF;
2297
2298 IF (l_curr_work_version_id is not null) THEN /* Bug 2672654 */
2299 pa_fin_plan_pvt.Get_Included_Ci(
2300 p_from_bv_id => l_curr_work_version_id
2301 ,p_to_bv_id => NULL
2302 ,p_impact_status => NULL
2303 ,x_ci_rec_tab => l_ci_rec_tab /* Bug 2672654 */
2304 ,x_return_status => x_return_status
2305 ,x_msg_count => x_msg_count
2306 ,x_msg_data => x_msg_data );
2307
2308 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2309 pa_debug.g_err_stage:= 'Could not obtain the CI information for the version';
2310 IF P_PA_DEBUG_MODE = 'Y' THEN
2311 pa_debug.write('CREATE_DRAFT: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL5);
2312 END IF;
2313 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
2314 ELSE
2315 pa_debug.g_err_stage:= 'obtained the CI information for the version';
2316 IF P_PA_DEBUG_MODE = 'Y' THEN
2317 pa_debug.write('CREATE_DRAFT: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
2318 END IF;
2319 END IF;
2320
2321 /* Bug 2688610 - should call delete_version rather than delete_version_helper.
2322 pa_fin_plan_pub.Delete_Version_Helper
2323 ( p_budget_version_id => l_curr_work_version_id
2324 ,x_return_status => x_return_status
2325 ,x_msg_count => x_msg_count
2326 ,x_msg_data => x_msg_data );
2327 */
2328
2329 -- Do not delete the version if called from create draft. Changes due to AMG
2330
2331 IF nvl(p_replace_current_working_flag,'N')= 'Y' THEN
2332
2333 l_record_version_number := pa_fin_plan_utils.Retrieve_Record_Version_Number
2334 (p_budget_version_id => l_curr_work_version_id);
2335
2336 --Try to lock the version before deleting the version. This is required so as not to delete
2337 --versions locked by other users. AMG UT2
2338 l_user_id := FND_GLOBAL.User_id;
2339 pa_fin_plan_pvt.lock_unlock_version
2340 (p_budget_version_id => l_curr_work_version_id,
2341 p_record_version_number => l_record_version_number,
2342 p_action => 'L',
2343 p_user_id => l_user_id,
2344 p_person_id => NULL,
2345 x_return_status => x_return_status,
2346 x_msg_count => x_msg_count,
2347 x_msg_data => x_msg_data) ;
2348
2349 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
2350
2351 IF l_debug_mode = 'Y' THEN
2352 pa_debug.g_err_stage := 'Error in lock unlock version - cannot delete working version';
2353 pa_debug.write('CREATE_DRAFT: ' || g_module_name,pa_debug.g_err_stage,5);
2354 END IF;
2355
2356 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
2357
2358 END IF;
2359
2360 l_record_version_number := pa_fin_plan_utils.Retrieve_Record_Version_Number
2361 (p_budget_version_id => l_curr_work_version_id);
2362
2363 pa_fin_plan_pub.delete_version
2364 ( p_project_id => p_project_id
2365 ,p_budget_version_id => l_curr_work_version_id
2366 ,p_record_version_number => l_record_version_number
2367 ,x_return_status => x_return_status
2368 ,x_msg_count => x_msg_count
2369 ,x_msg_data => x_msg_data
2370 );
2371
2372 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2373 pa_debug.g_err_stage:= 'Could not delete the current working version';
2374 IF P_PA_DEBUG_MODE = 'Y' THEN
2375 pa_debug.write('CREATE_DRAFT: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL5);
2376 END IF;
2377 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
2378 ELSE
2379 pa_debug.g_err_stage:= 'Deleted the current working version';
2380 IF P_PA_DEBUG_MODE = 'Y' THEN
2381 pa_debug.write('CREATE_DRAFT: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
2382 END IF;
2383 END IF;
2384
2385 END IF;--IF l_calling_context <> PA_FP_CONSTANTS_PKG.G_CREATE_DRAFT THEN
2386
2387 END IF; --l_curr_work_version_id is not null
2388 ELSE
2389 pa_debug.g_err_stage:= 'p_ci_id is not null - control item version';
2390 IF P_PA_DEBUG_MODE = 'Y' THEN
2391 pa_debug.write('CREATE_DRAFT: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
2392 END IF;
2393 END IF; -- p_ci_id is NULL. Bug 2672654
2394
2395 --Get the preference code
2396 IF(p_version_type = PA_FP_CONSTANTS_PKG.G_ELEMENT_TYPE_COST) THEN
2397 l_plan_pref_code := PA_FP_CONSTANTS_PKG.G_PREF_COST_ONLY;
2398 ELSIF(p_version_type = PA_FP_CONSTANTS_PKG.G_ELEMENT_TYPE_REVENUE) THEN
2399 l_plan_pref_code := PA_FP_CONSTANTS_PKG.G_PREF_REVENUE_ONLY;
2400 ELSIF(p_version_type = PA_FP_CONSTANTS_PKG.G_ELEMENT_TYPE_ALL) THEN
2401 l_plan_pref_code := PA_FP_CONSTANTS_PKG.G_PREF_COST_AND_REV_SAME;
2402 END IF;
2403
2404 pa_debug.g_err_stage:= 'Preference code is -> ' || l_plan_pref_code;
2405 IF P_PA_DEBUG_MODE = 'Y' THEN
2406 pa_debug.write('CREATE_DRAFT: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
2407 END IF;
2408
2409 --Get the amount set id.
2410 pa_fin_plan_utils.GET_OR_CREATE_AMOUNT_SET_ID
2411 (
2412 p_raw_cost_flag => p_raw_cost_flag
2413 ,p_burdened_cost_flag => p_burdened_cost_flag
2414 ,p_revenue_flag => p_revenue_flag
2415 ,p_cost_qty_flag => p_cost_qty_flag
2416 ,p_revenue_qty_flag => p_revenue_qty_flag
2417 ,p_all_qty_flag => p_all_qty_flag
2418 ,p_plan_pref_code => l_plan_pref_code
2419 /* Changes for FP.M, Tracking Bug No - 3354518
2420 Passing three new arguments p_bill_rate_flag,
2421 p_cost_rate_flag, p_burden_rate below for
2422 new columns in pa_fin_plan_amount_sets and changes done in
2423 called API */
2424 ,p_bill_rate_flag => 'Y'
2425 ,p_cost_rate_flag => 'Y'
2426 ,p_burden_rate_flag => 'Y'
2427 ,x_cost_amount_set_id => l_cost_amount_set_id
2428 ,x_revenue_amount_set_id => l_rev_amount_set_id
2429 ,x_all_amount_set_id => l_all_amount_set_id
2430 ,x_message_count => x_msg_count
2431 ,x_return_status => x_return_status
2432 ,x_message_data => x_msg_data
2433 );
2434
2435 IF(p_version_type = PA_FP_CONSTANTS_PKG.G_ELEMENT_TYPE_COST) THEN
2436 l_amount_set_id := l_cost_amount_set_id;
2437 ELSIF(p_version_type = PA_FP_CONSTANTS_PKG.G_ELEMENT_TYPE_REVENUE) THEN
2438 l_amount_set_id := l_rev_amount_set_id;
2439 ELSIF(p_version_type = PA_FP_CONSTANTS_PKG.G_ELEMENT_TYPE_ALL) THEN
2440 l_amount_set_id := l_all_amount_set_id;
2441 END IF;
2442
2443 pa_debug.g_err_stage:= 'amount set id is -> ' || l_amount_set_id;
2444 IF P_PA_DEBUG_MODE = 'Y' THEN
2445 pa_debug.write('CREATE_DRAFT: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
2446 END IF;
2447
2448 l_created_version_id := NULL;
2449 /* Create the plan version */
2450 pa_fin_plan_pub.Create_Version (
2451 p_project_id => p_project_id
2452 ,p_fin_plan_type_id => p_fin_plan_type_id
2453 ,p_element_type => p_version_type
2454 ,p_version_name => p_version_name
2455 ,p_description => p_description
2456 -- change for CI impact start. Bug # 2634900.
2457 ,p_ci_id => p_ci_id --NULL
2458 ,p_est_proj_raw_cost => p_est_proj_raw_cost --NULL
2459 ,p_est_proj_bd_cost => p_est_proj_bd_cost --NULL
2460 ,p_est_proj_revenue => p_est_proj_revenue --NULL
2461 ,p_est_qty => p_est_qty --NULL
2462 ,p_est_equip_qty => p_est_equip_qty --NULL
2463 ,p_impacted_task_id => p_impacted_task_id --NULL
2464 ,p_agreement_id => p_agreement_id --NULL
2465 -- change for CI impact end. Bug # 2634900.
2466 ,p_calling_context => l_calling_context
2467 ,p_plan_in_multi_curr_flag => p_plan_in_mc_flag
2468 ,p_fin_plan_level_code => p_fin_plan_level_code
2469 ,p_resource_list_id => l_resource_list_id
2470 ,p_time_phased_code => p_time_phased_code
2471 ,p_amount_set_id => l_amount_set_id
2472 ,px_budget_version_id => l_created_version_id
2473 ,x_proj_fp_option_id => l_fp_options_id
2474 ,x_return_status => x_return_status
2475 ,x_msg_count => x_msg_count
2476 ,x_msg_data => x_msg_data );
2477
2478 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2479 pa_debug.g_err_stage:= 'Error in calling Create_Version';
2480 IF P_PA_DEBUG_MODE = 'Y' THEN
2481 pa_debug.write('CREATE_DRAFT: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL5);
2482 END IF;
2483 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
2484 END IF;
2485
2486 x_budget_version_id := l_created_version_id;
2487
2488
2489 pa_debug.g_err_stage:= 'Created budget version id is : '||l_created_version_id;
2490 IF P_PA_DEBUG_MODE = 'Y' THEN
2491 pa_debug.write('CREATE_DRAFT: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
2492 END IF;
2493
2494 -- If the created version is a NON-CI version "AND" --- added this condition for bug 2881681
2495 -- If either of the replace current working or Create working version flags is Y then make
2496 -- the newly created version the current working version. Changes due to finplan in AMG
2497
2498 IF (p_ci_id IS NULL) AND
2499 (p_create_new_curr_working_flag = 'Y' OR
2500 p_replace_current_working_flag = 'Y') THEN
2501
2502 -- Get the details of the current working version so as to pass it to the
2503 -- Set Current Working API.
2504
2505 pa_fin_plan_utils.Get_Curr_Working_Version_Info(
2506 p_project_id => p_project_id
2507 ,p_fin_plan_type_id => p_fin_plan_type_id
2508 ,p_version_type => p_version_type
2509 ,x_fp_options_id => l_proj_fp_options_id
2510 ,x_fin_plan_version_id => l_CW_version_id
2511 ,x_return_status => x_return_status
2512 ,x_msg_count => x_msg_count
2513 ,x_msg_data => x_msg_data );
2514
2515 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2516 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
2517 END IF;
2518
2519 -- Further processing is required only if the newly created version is not the current working verion
2520
2521 IF l_created_version_id <> l_CW_version_id THEN
2522
2523 --Get the record version number of the current working version
2524 l_CW_record_version_number := pa_fin_plan_utils.Retrieve_Record_Version_Number(l_CW_version_id);
2525
2526 --Get the record version number of the newly created version
2527 l_created_ver_rec_ver_num := pa_fin_plan_utils.Retrieve_Record_Version_Number(l_created_version_id);
2528 l_user_id := FND_GLOBAL.User_id;
2529 pa_fin_plan_pvt.lock_unlock_version
2530 (p_budget_version_id => l_CW_version_id,
2531 p_record_version_number => l_CW_record_version_number,
2532 p_action => 'L',
2533 p_user_id => l_user_id,
2534 p_person_id => NULL,
2535 x_return_status => x_return_status,
2536 x_msg_count => x_msg_count,
2537 x_msg_data => x_msg_data) ;
2538
2539 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
2540
2541 IF l_debug_mode = 'Y' THEN
2542 pa_debug.g_err_stage := 'Error executing lock unlock version';
2543 pa_debug.write('CREATE_DRAFT: ' || g_module_name,pa_debug.g_err_stage,3);
2544 END IF;
2545
2546 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
2547
2548 END IF;
2549
2550 IF l_debug_mode = 'Y' THEN
2551 pa_debug.g_err_stage := 'About to call set current working version';
2552 pa_debug.write('CREATE_DRAFT: ' || g_module_name,pa_debug.g_err_stage,3);
2553 END IF;
2554
2555 -- Getting the rec ver number again as it will be incremented by the api lock_unlock_version
2556 l_CW_record_version_number := pa_fin_plan_utils.Retrieve_Record_Version_Number(l_CW_version_id);
2557
2558 pa_fin_plan_pub.Set_Current_Working
2559 (p_project_id => p_project_id,
2560 p_budget_version_id => l_created_version_id,
2561 p_record_version_number => l_created_ver_rec_ver_num,
2562 p_orig_budget_version_id => l_CW_version_id,
2563 p_orig_record_version_number => l_CW_record_version_number,
2564 x_return_status => x_return_status,
2565 x_msg_count => x_msg_count,
2566 x_msg_data => x_msg_data);
2567
2568 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2569 IF P_PA_DEBUG_MODE = 'Y' THEN
2570 pa_debug.g_err_stage:= 'Error executing Set_Current_Working ';
2571 pa_debug.write('CREATE_DRAFT: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL5);
2572 END IF;
2573 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
2574 END IF;
2575
2576 END IF; -- IF l_created_version_id <> l_CW_version_id THEN
2577
2578 END IF; --IF (p_create_new_curr_working_flag = 'Y' OR
2579
2580
2581
2582
2583 --The above call to the create version api has not created RAs,elements.
2584 --need to update the options and version table.
2585
2586 --start of changes for options table.
2587 /*
2588 need to update the following in fp_options.
2589 conversion attributes.
2590 */
2591 /*
2592 Bug 2670747 - The MC attributes need to be updated only if MC flag is Y
2593 */
2594 IF (p_plan_in_mc_flag = 'Y') THEN
2595 update pa_proj_fp_options
2596 set projfunc_cost_rate_type = p_projfunc_cost_rate_type
2597 ,projfunc_cost_rate_date_type = p_projfunc_cost_rate_date_type
2598 ,projfunc_cost_rate_date = p_projfunc_cost_rate_date
2599 ,projfunc_rev_rate_type = p_projfunc_rev_rate_type
2600 ,projfunc_rev_rate_date_type = p_projfunc_rev_rate_date_type
2601 ,projfunc_rev_rate_date = p_projfunc_rev_rate_date
2602 ,project_cost_rate_type = p_project_cost_rate_type
2603 ,project_cost_rate_date_type = p_project_cost_rate_date_type
2604 ,project_cost_rate_date = p_project_cost_rate_date
2605 ,project_rev_rate_type = p_project_rev_rate_type
2606 ,project_rev_rate_date_type = p_project_rev_rate_date_type
2607 ,project_rev_rate_date = p_project_rev_rate_date
2608 where proj_fp_options_id = l_fp_options_id;
2609 END IF; --End of Bug fix 2670747
2610
2611 --End of changes corresponding to options table.
2612
2613 --Start of changes for budget versions table.
2614 /*
2615 need to update the following in budget_versions
2616 change reason code,
2617 product code, budget reference.
2618 */
2619 update pa_budget_versions
2620 set change_reason_code = p_change_reason_code,
2621 pm_product_code = p_pm_product_code,
2622 pm_budget_reference = p_pm_budget_reference,
2623 attribute_category = p_attribute_category,
2624 attribute1 = p_attribute1,
2625 attribute2 = p_attribute2,
2626 attribute3 = p_attribute3,
2627 attribute4 = p_attribute4,
2628 attribute5 = p_attribute5,
2629 attribute6 = p_attribute6,
2630 attribute7 = p_attribute7,
2631 attribute8 = p_attribute8,
2632 attribute9 = p_attribute9,
2633 attribute10 = p_attribute10,
2634 attribute11 = p_attribute11,
2635 attribute12 = p_attribute12,
2636 attribute13 = p_attribute13,
2637 attribute14 = p_attribute14,
2638 attribute15 = p_attribute15
2639 where budget_version_id = l_created_version_id;
2640
2641 --End of changes corresponding to budget versions table.
2642
2643 /* Bug# 2676352 - For automatic baselined ci versions, budget_lines_tab is not passed and we should
2644 be creating resource assignments and fp elements based on defaults. It is to be noted that for
2645 autobaseline case, resource list id is always none and hence calling insert_defaults should be fine */
2646
2647 IF (l_calling_context = PA_FP_CONSTANTS_PKG.G_AUTOMATIC_BASELINE AND p_ci_id IS NOT NULL) THEN
2648
2649 IF ( p_impacted_task_id IS NULL OR
2650 p_fin_plan_level_code = PA_FP_CONSTANTS_PKG.G_BUDGET_ENTRY_LEVEL_PROJECT ) THEN
2651
2652 -- <Patchset M: B and F impact changes : AMG:>-- Bug # 3507156
2653 -- References to PA_FP_ELEMENTS table have been commented out (FP M)
2654 -- Comment START.
2655 /*
2656 -- Create fp elements and resource assignments for the budget version and the impacted task id
2657
2658 pa_debug.g_err_stage:='Calling pa_fp_elements_pub.insert_default...';
2659 IF P_PA_DEBUG_MODE = 'Y' THEN
2660 pa_debug.write('CREATE_DRAFT: ' || g_module_name,pa_debug.g_err_stage,3);
2661 END IF;
2662
2663 Pa_Fp_Elements_Pub.Insert_Default (
2664 p_proj_fp_options_id => l_fp_options_id
2665 ,p_element_type => p_version_type
2666 ,p_planning_level => p_fin_plan_level_code
2667 ,p_resource_list_id => l_resource_list_id
2668 -- Bug 2920954 Start of parameters added for post FP-K oneoff patch
2669 ,p_select_res_auto_flag => NULL
2670 ,p_res_planning_level => NULL
2671 --Bug 2920954 End of parameters added for post FP-K oneoff patch
2672 ,x_return_status => x_return_status
2673 ,x_msg_count => x_msg_count
2674 ,x_msg_data => x_msg_data);
2675
2676 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2677 Raise PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
2678 END IF;
2679
2680
2681 pa_debug.g_err_stage:='Calling pa_fp_elements_pub.create_enterable_resources...';
2682 IF P_PA_DEBUG_MODE = 'Y' THEN
2683 pa_debug.write('CREATE_DRAFT: ' || g_module_name,pa_debug.g_err_stage,3);
2684 END IF;
2685
2686 Pa_Fp_Elements_Pub.Create_Enterable_Resources (
2687 p_plan_version_id => l_created_version_id
2688 ,x_return_status => x_return_status
2689 ,x_msg_count => x_msg_count
2690 ,x_msg_data => x_msg_data);
2691
2692 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2693 Raise PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
2694 END IF;
2695
2696 */
2697 --<Patchset M: B and F impact changes : AMG:>-- Bug # 3507156
2698 -- Comment END
2699 -- Added a call to pa_fp_planning_transaction_pub.create_default_task_plan_txns
2700
2701 pa_debug.g_err_stage:='Calling pa_fp_planning_transaction_pub.create_default_task_plan_txns...';
2702
2703 IF P_PA_DEBUG_MODE = 'Y' THEN
2704 pa_debug.write('CREATE_DRAFT: ' || g_module_name,pa_debug.g_err_stage,3);
2705 END IF;
2706 pa_fp_planning_transaction_pub.create_default_task_plan_txns(
2707 P_budget_version_id => l_created_version_id
2708 ,P_version_plan_level_code => p_fin_plan_level_code
2709 ,X_return_status => x_return_status
2710 ,X_msg_count => x_msg_count
2711 ,X_msg_data => x_msg_data);
2712
2713 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2714 Raise PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
2715 END IF;
2716
2717 ELSE
2718
2719 -- Fetching top task id and parent task id of impacted task id
2720
2721 pa_debug.g_err_stage:= 'Fetching impacted task details';
2722 IF P_PA_DEBUG_MODE = 'Y' THEN
2723 pa_debug.write('CREATE_DRAFT: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
2724 END IF;
2725
2726 OPEN impacted_task_cur(p_impacted_task_id);
2727 FETCH impacted_task_cur INTO impacted_task_rec;
2728 CLOSE impacted_task_cur;
2729
2730 --Insert a new record into pa_resoruce_assignments
2731
2732
2733 DECLARE
2734 l_dummy_res_list_id PA_RESOURCE_LISTS_ALL_BG.resource_list_id%TYPE;
2735 l_dummy_row_id ROWID;
2736 l_dummy_ra_id PA_RESOURCE_ASSIGNMENTS.resource_assignment_id%TYPE;
2737 BEGIN
2738
2739 PA_FIN_PLAN_UTILS.Get_Uncat_Resource_List_Info
2740 (x_resource_list_id => l_dummy_res_list_id
2741 ,x_resource_list_member_id => l_uncat_rlmid
2742 ,x_track_as_labor_flag => l_track_as_labor_flag
2743 ,x_unit_of_measure => l_unit_of_measure
2744 ,x_return_status => x_return_status
2745 ,x_msg_count => x_msg_count
2746 ,x_msg_data => x_msg_data);
2747
2748 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2749 pa_debug.g_err_stage := 'Error while fetching uncat res list id info ...';
2750 IF P_PA_DEBUG_MODE = 'Y' THEN
2751 pa_debug.write('CREATE_DRAFT: ' || g_module_name,pa_debug.g_err_stage,5);
2752 END IF;
2753 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
2754 END IF;
2755
2756 l_task_elem_version_id_tbl.extend();
2757 l_task_elem_version_id_tbl(1) := impacted_task_rec.element_version_id;
2758 l_rlm_id_tbl.extend();
2759 l_rlm_id_tbl(1) := l_uncat_rlmid;
2760 Pa_Fp_Planning_Transaction_Pub.Add_Planning_Transactions(
2761 p_context => 'BUDGET',--It will always be budget as CIs can be
2762 --created only for Budgets
2763 p_project_id => p_project_id,
2764 p_budget_version_id => l_created_version_id,
2765 p_task_elem_version_id_tbl => l_task_elem_version_id_tbl,
2766 p_resource_list_member_id_tbl => l_rlm_id_tbl,
2767 x_return_status => x_return_status,
2768 x_msg_count => x_msg_count,
2769 x_msg_data => x_msg_data);
2770
2771 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2772 pa_debug.g_err_stage:= 'Exception while inserting a row into pa_resource_assignments;';
2773 IF P_PA_DEBUG_MODE = 'Y' THEN
2774 pa_debug.write('CREATE_DRAFT: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL5);
2775 END IF;
2776 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
2777 END IF;
2778
2779 END;
2780
2781 END IF;
2782
2783 --call PA_FIN_PLAN_PVT.CRETAE_FINPLAN_LINES. This API will create
2784 --resource assignments, elements and budget lines for the version.
2785 /* Bug# 2672654 - create_fin_plan_lines API needs to be called only if p_budget_lines_tab is not null */
2786
2787 ELSIF ( nvl(p_budget_lines_tab.last,0) > 0 ) THEN
2788
2789 --Bug # 3507156-<Patchset M: B and F impact changes : AMG:>
2790 --Commented the call to PA_FIN_PLAN_PVT.CREATE_FINPLAN_LINES
2791 --Comment START
2792 /*
2793 PA_FIN_PLAN_PVT.CREATE_FINPLAN_LINES
2794 ( p_calling_context => l_calling_context -- Bug# 2674353
2795 ,p_fin_plan_version_id => l_created_version_id
2796 ,p_budget_lines_tab => p_budget_lines_tab
2797 ,x_return_status => x_return_status
2798 ,x_msg_count => x_msg_count
2799 ,x_msg_data => x_msg_data );
2800
2801 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2802 pa_debug.g_err_stage:= 'Error Calling CREATE_FINPLAN_LINES';
2803 IF P_PA_DEBUG_MODE = 'Y' THEN
2804 pa_debug.write('CREATE_DRAFT: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL5);
2805 END IF;
2806 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
2807 END IF;
2808 */
2809 --Bug # 3507156-<Patchset M: B and F impact changes : AMG:>
2810 --Added a call to PA_FIN_PLAN_PVT.ADD_FIN_PLAN_LINES
2811
2812 PA_FIN_PLAN_PVT.ADD_FIN_PLAN_LINES
2813 ( p_calling_context => l_calling_context
2814 ,p_fin_plan_version_id => l_created_version_id
2815 ,p_finplan_lines_tab => p_budget_lines_tab
2816 ,x_return_status => x_return_status
2817 ,x_msg_count => x_msg_count
2818 ,x_msg_data => x_msg_data );
2819
2820 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2821 pa_debug.g_err_stage:= 'Error Calling ADD_FIN_PLAN_LINES';
2822 IF P_PA_DEBUG_MODE = 'Y' THEN
2823 pa_debug.write('CREATE_DRAFT: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL5);
2824 END IF;
2825 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
2826 END IF;
2827 END IF;
2828
2829 pa_debug.g_err_stage:= 'Restoring the Control Item links if any';
2830 IF P_PA_DEBUG_MODE = 'Y' THEN
2831 pa_debug.write('CREATE_DRAFT: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
2832 END IF;
2833
2834 /* Commented out for bug 3550073
2835 IF ( nvl(l_ci_rec_tab.last,0) > 0 ) THEN
2836 FOR i in l_ci_rec_tab.first..l_ci_rec_tab.last LOOP
2837
2838 pa_fp_ci_merge.FP_CI_LINK_CONTROL_ITEMS (
2839 p_project_id => p_project_id
2840 ,p_s_fp_version_id => l_ci_rec_tab(i).ci_plan_version_id
2841 ,p_t_fp_version_id => l_created_version_id
2842 ,x_return_status => x_return_status
2843 ,x_msg_count => x_msg_count
2844 ,x_msg_data => x_msg_data);
2845
2846 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2847 pa_debug.g_err_stage:= 'Error Calling FP_CI_LINK_CONTROL_ITEMS';
2848 IF P_PA_DEBUG_MODE = 'Y' THEN
2849 pa_debug.write('CREATE_DRAFT: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL5);
2850 END IF;
2851 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
2852 END IF; -- l_return_status <> FND_API.G_RET_STS_SUCCESS
2853
2854 END LOOP; --first..last
2855 END IF; -- l_ci_rec_tab is not null */
2856
2857
2858 pa_debug.g_err_stage:= 'Exiting CREATE_DRAFT';
2859 IF P_PA_DEBUG_MODE = 'Y' THEN
2860 pa_debug.write('CREATE_DRAFT: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL2);
2861 pa_debug.reset_err_stack;
2862 END IF;
2863
2864 EXCEPTION
2865
2866 WHEN PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc THEN
2867
2868 x_return_status := FND_API.G_RET_STS_ERROR;
2869 l_msg_count := FND_MSG_PUB.count_msg;
2870 IF l_msg_count = 1 THEN
2871 PA_INTERFACE_UTILS_PUB.get_messages
2872 (p_encoded => FND_API.G_TRUE
2873 ,p_msg_index => 1
2874 ,p_msg_count => l_msg_count
2875 ,p_msg_data => l_msg_data
2876 ,p_data => l_data
2877 ,p_msg_index_out => l_msg_index_out);
2878 x_msg_data := l_data;
2879 x_msg_count := l_msg_count;
2880 ELSE
2881 x_msg_count := l_msg_count;
2882 END IF;
2883
2884 pa_debug.g_err_stage:= 'Invalid Arguments Passed';
2885 IF P_PA_DEBUG_MODE = 'Y' THEN
2886 pa_debug.write('CREATE_DRAFT: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL5);
2887 pa_debug.reset_err_stack;
2888 END IF;
2889 RAISE;
2890
2891 WHEN others THEN
2892
2893 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2894 x_msg_count := 1;
2895 x_msg_data := SQLERRM;
2896 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'pa_fin_plan_pvt'
2897 ,p_procedure_name => 'CREATE_DRAFT');
2898 pa_debug.g_err_stage:= 'Unexpected Error'||SQLERRM;
2899 IF P_PA_DEBUG_MODE = 'Y' THEN
2900 pa_debug.write('CREATE_DRAFT: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL5);
2901 pa_debug.reset_err_stack;
2902 END IF;
2903 RAISE;
2904
2905 END CREATE_DRAFT;
2906
2907 /*
2908 This procedure would use the input budget_line_tbl to insert records into
2909 pa_resource_assignments, pa_budget_lines, pa_mc_budget_lines and also takes
2910 care of rolling up the resource assignments and maintaining the denorm table.
2911 */
2912 PROCEDURE CREATE_FINPLAN_LINES
2913 ( -- Bug Fix: 4569365. Removed MRC code.
2914 -- p_calling_context IN pa_mrc_finplan.g_calling_module%TYPE /* Bug# 2674353 */
2915 p_calling_context IN VARCHAR2
2916 ,p_fin_plan_version_id IN pa_budget_versions.budget_version_id%TYPE
2917 ,p_budget_lines_tab IN pa_fin_plan_pvt.budget_lines_tab
2918 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2919 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
2920 ,x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
2921 IS
2922
2923 l_msg_count NUMBER := 0;
2924 l_data VARCHAR2(2000);
2925 l_msg_data VARCHAR2(2000);
2926 l_error_msg_code VARCHAR2(30);
2927 l_msg_index_out NUMBER;
2928 l_return_status VARCHAR2(1);
2929 l_debug_mode VARCHAR2(30);
2930 l_time_phased_type_code VARCHAR2(30); --8854015
2931
2932 /* Start of table variables for members of p_budget_lines_table */
2933
2934 l_task_id_tab task_id_tab;
2935 l_resource_list_member_id_tab resource_list_member_id_tab;
2936 -- Added for CBS 16598322
2937 l_cbs_element_id_tab cbs_element_id_tab;
2938
2939 l_description_tab description_tab;
2940 l_start_date_tab start_date_tab;
2941 l_end_date_tab end_date_tab;
2942 l_period_name_tab period_name_tab;
2943 l_quantity_tab quantity_tab;
2944 l_unit_of_measure_tab unit_of_measure_tab;
2945 l_track_as_labor_flag_tab track_as_labor_flag_tab;
2946 l_txn_currency_code_tab txn_currency_code_tab;
2947 l_raw_cost_tab raw_cost_tab;
2948 l_burdened_cost_tab burdened_cost_tab;
2949 l_revenue_tab revenue_tab;
2950 l_txn_raw_cost_tab txn_raw_cost_tab;
2951 l_txn_burdened_cost_tab txn_burdened_cost_tab;
2952 l_txn_revenue_tab txn_revenue_tab;
2953 l_project_raw_cost_tab project_raw_cost_tab;
2954 l_project_burdened_cost_tab project_burdened_cost_tab;
2955 l_project_revenue_tab project_revenue_tab;
2956 l_change_reason_code_tab change_reason_code_tab;
2957 l_attribute_category_tab attribute_category_tab;
2958 l_attribute1_tab attribute1_tab;
2959 l_attribute2_tab attribute2_tab;
2960 l_attribute3_tab attribute3_tab;
2961 l_attribute4_tab attribute4_tab;
2962 l_attribute5_tab attribute5_tab;
2963 l_attribute6_tab attribute6_tab;
2964 l_attribute7_tab attribute7_tab;
2965 l_attribute8_tab attribute8_tab;
2966 l_attribute9_tab attribute9_tab;
2967 l_attribute10_tab attribute10_tab;
2968 l_attribute11_tab attribute11_tab;
2969 l_attribute12_tab attribute12_tab;
2970 l_attribute13_tab attribute13_tab;
2971 l_attribute14_tab attribute14_tab;
2972 l_attribute15_tab attribute15_tab;
2973 l_PF_COST_RATE_TYPE_tab PF_COST_RATE_TYPE_tab;
2974 l_PF_COST_RATE_DATE_TYPE_tab PF_COST_RATE_DATE_TYPE_tab;
2975 l_PF_COST_RATE_DATE_tab PF_COST_RATE_DATE_tab;
2976 l_PF_COST_RATE_tab PF_COST_RATE_tab;
2977 l_PF_REV_RATE_TYPE_tab PF_REV_RATE_TYPE_tab;
2978 l_PF_REV_RATE_DATE_TYPE_tab PF_REV_RATE_DATE_TYPE_tab;
2979 l_PF_REV_RATE_DATE_tab PF_REV_RATE_DATE_tab;
2980 l_PF_REV_RATE_tab PF_REV_RATE_tab;
2981 l_PJ_COST_RATE_TYPE_tab PJ_COST_RATE_TYPE_tab;
2982 l_PJ_COST_RATE_DATE_TYPE_tab PJ_COST_RATE_DATE_TYPE_tab;
2983 l_PJ_COST_RATE_DATE_tab PJ_COST_RATE_DATE_tab;
2984 l_PJ_COST_RATE_tab PJ_COST_RATE_tab;
2985 l_PJ_REV_RATE_TYPE_tab PJ_REV_RATE_TYPE_tab;
2986 l_PJ_REV_RATE_DATE_TYPE_tab PJ_REV_RATE_DATE_TYPE_tab;
2987 l_PJ_REV_RATE_DATE_tab PJ_REV_RATE_DATE_tab;
2988 l_PJ_REV_RATE_tab PJ_REV_RATE_tab;
2989 l_pm_product_code_tab pm_product_code_tab;
2990 l_pm_budget_line_reference_tab pm_budget_line_reference_tab;
2991 l_quantity_source_tab quantity_source_tab;
2992 l_raw_cost_source_tab raw_cost_source_tab;
2993 l_burdened_cost_source_tab burdened_cost_source_tab;
2994 l_revenue_source_tab revenue_source_tab;
2995 l_resource_assignment_id_tab resource_assignment_id_tab;
2996
2997 /* End of table variables for members of p_budget_lines_table */
2998 -- Bug Fix: 4569365. Removed MRC code.
2999 -- l_calling_context pa_mrc_finplan.g_calling_module%TYPE;
3000 l_calling_context VARCHAR2(30);
3001
3002 /* #2727304 */
3003 l_proj_currency_code pa_projects_all.project_currency_code%TYPE;
3004 l_projfunc_currency_code pa_projects_all.projfunc_currency_code%TYPE;
3005
3006 BEGIN
3007
3008 x_msg_count := 0;
3009 x_return_status := FND_API.G_RET_STS_SUCCESS;
3010
3011 fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
3012 l_debug_mode := NVL(l_debug_mode, 'Y');
3013 IF p_pa_debug_mode = 'Y' THEN
3014 pa_debug.set_err_stack('pa_fin_plan_pvt.CREATE_FINPLAN_LINES');
3015 END IF;
3016 IF P_PA_DEBUG_MODE = 'Y' THEN
3017 pa_debug.set_process('CREATE_FINPLAN_LINES: ' || 'PLSQL','LOG',l_debug_mode);
3018 END IF;
3019
3020
3021 -- Check for business rules violations
3022
3023 IF P_PA_DEBUG_MODE = 'Y' THEN
3024 pa_debug.g_err_stage:= 'Validating input parameters - CREATE_FINPLAN_LINES';
3025 pa_debug.write('CREATE_FINPLAN_LINES: ' || g_module_name,pa_debug.g_err_stage,3);
3026 END IF;
3027
3028 --Validate plan version id
3029
3030 IF (p_fin_plan_version_id IS NULL)
3031 THEN
3032 IF P_PA_DEBUG_MODE = 'Y' THEN
3033 pa_debug.g_err_stage:= 'fin_plan_version_id = '|| p_fin_plan_version_id;
3034 pa_debug.write('CREATE_FINPLAN_LINES: ' || g_module_name,pa_debug.g_err_stage,5);
3035 END IF;
3036
3037 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
3038 p_msg_name => 'PA_FP_INV_PARAM_PASSED');
3039
3040 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
3041 END IF;
3042
3043 l_calling_context := p_calling_context; /* Bug# 2674353 */
3044
3045 --Populate the individual column tables so that we can do a bulk
3046 --insert into resource assignments table.
3047
3048 IF nvl(p_budget_lines_tab.last,0) > 0 THEN
3049 FOR i in p_budget_lines_tab.first..p_budget_lines_tab.last LOOP
3050
3051 l_task_id_tab(i) := p_budget_lines_tab(i).system_reference1;
3052 l_resource_list_member_id_tab(i) := p_budget_lines_tab(i).system_reference2;
3053 l_cbs_element_id_tab(i) := p_budget_lines_tab(i).system_reference3; -- Added for CBS 16598322
3054 l_description_tab(i) := p_budget_lines_tab(i).description;
3055 l_start_date_tab(i) := p_budget_lines_tab(i).start_date;
3056 l_end_date_tab(i) := p_budget_lines_tab(i).end_date;
3057 l_period_name_tab(i) := p_budget_lines_tab(i).period_name;
3058 l_quantity_tab(i) := p_budget_lines_tab(i).quantity;
3059 l_unit_of_measure_tab(i) := p_budget_lines_tab(i).system_reference4;
3060 l_track_as_labor_flag_tab(i) := p_budget_lines_tab(i).system_reference5;
3061 l_txn_currency_code_tab(i) := p_budget_lines_tab(i).txn_currency_code;
3062 l_raw_cost_tab(i) := p_budget_lines_tab(i).projfunc_raw_cost;
3063 l_burdened_cost_tab(i) := p_budget_lines_tab(i).projfunc_burdened_cost;
3064 l_revenue_tab(i) := p_budget_lines_tab(i).projfunc_revenue;
3065 l_txn_raw_cost_tab(i) := p_budget_lines_tab(i).txn_raw_cost;
3066 l_txn_burdened_cost_tab(i) := p_budget_lines_tab(i).txn_burdened_cost;
3067 l_txn_revenue_tab(i) := p_budget_lines_tab(i).txn_revenue;
3068 l_project_raw_cost_tab(i) := p_budget_lines_tab(i).project_raw_cost;
3069 l_project_burdened_cost_tab(i) := p_budget_lines_tab(i).project_burdened_cost;
3070 l_project_revenue_tab(i) := p_budget_lines_tab(i).project_revenue;
3071 l_change_reason_code_tab(i) := p_budget_lines_tab(i).change_reason_code;
3072 l_attribute_category_tab(i) := p_budget_lines_tab(i).attribute_category;
3073 l_attribute1_tab(i) := p_budget_lines_tab(i).attribute1;
3074 l_attribute2_tab(i) := p_budget_lines_tab(i).attribute2;
3075 l_attribute3_tab(i) := p_budget_lines_tab(i).attribute3;
3076 l_attribute4_tab(i) := p_budget_lines_tab(i).attribute4;
3077 l_attribute5_tab(i) := p_budget_lines_tab(i).attribute5;
3078 l_attribute6_tab(i) := p_budget_lines_tab(i).attribute6;
3079 l_attribute7_tab(i) := p_budget_lines_tab(i).attribute7;
3080 l_attribute8_tab(i) := p_budget_lines_tab(i).attribute8;
3081 l_attribute9_tab(i) := p_budget_lines_tab(i).attribute9;
3082 l_attribute10_tab(i) := p_budget_lines_tab(i).attribute10;
3083 l_attribute11_tab(i) := p_budget_lines_tab(i).attribute11;
3084 l_attribute12_tab(i) := p_budget_lines_tab(i).attribute12;
3085 l_attribute13_tab(i) := p_budget_lines_tab(i).attribute13;
3086 l_attribute14_tab(i) := p_budget_lines_tab(i).attribute14;
3087 l_attribute15_tab(i) := p_budget_lines_tab(i).attribute15;
3088 l_PF_COST_RATE_TYPE_tab(i) := p_budget_lines_tab(i).PROJFUNC_COST_RATE_TYPE;
3089 l_PF_COST_RATE_DATE_TYPE_tab(i) := p_budget_lines_tab(i).PROJFUNC_COST_RATE_DATE_TYPE;
3090 l_PF_COST_RATE_DATE_tab(i) := p_budget_lines_tab(i).PROJFUNC_COST_RATE_DATE;
3091 l_PF_COST_RATE_tab(i) := p_budget_lines_tab(i).PROJFUNC_COST_EXCHANGE_RATE;
3092 l_PF_REV_RATE_TYPE_tab(i) := p_budget_lines_tab(i).PROJFUNC_REV_RATE_TYPE;
3093 l_PF_REV_RATE_DATE_TYPE_tab(i) := p_budget_lines_tab(i).PROJFUNC_REV_RATE_DATE_TYPE;
3094 l_PF_REV_RATE_DATE_tab(i) := p_budget_lines_tab(i).PROJFUNC_REV_RATE_DATE;
3095 l_PF_REV_RATE_tab(i) := p_budget_lines_tab(i).PROJFUNC_REV_EXCHANGE_RATE;
3096 l_PJ_COST_RATE_TYPE_tab(i) := p_budget_lines_tab(i).PROJECT_COST_RATE_TYPE;
3097 l_PJ_COST_RATE_DATE_TYPE_tab(i) := p_budget_lines_tab(i).PROJECT_COST_RATE_DATE_TYPE;
3098 l_PJ_COST_RATE_DATE_tab(i) := p_budget_lines_tab(i).PROJECT_COST_RATE_DATE;
3099 l_PJ_COST_RATE_tab(i) := p_budget_lines_tab(i).PROJECT_COST_EXCHANGE_RATE;
3100 l_PJ_REV_RATE_TYPE_tab(i) := p_budget_lines_tab(i).PROJECT_REV_RATE_TYPE;
3101 l_PJ_REV_RATE_DATE_TYPE_tab(i) := p_budget_lines_tab(i).PROJECT_REV_RATE_DATE_TYPE;
3102 l_PJ_REV_RATE_DATE_tab(i) := p_budget_lines_tab(i).PROJECT_REV_RATE_DATE;
3103 l_PJ_REV_RATE_tab(i) := p_budget_lines_tab(i).PROJECT_REV_EXCHANGE_RATE;
3104 l_pm_product_code_tab(i) := p_budget_lines_tab(i).pm_product_code;
3105 l_pm_budget_line_reference_tab(i) := p_budget_lines_tab(i).pm_budget_line_reference;
3106 l_quantity_source_tab(i) := p_budget_lines_tab(i).quantity_source;
3107 l_raw_cost_source_tab(i) := p_budget_lines_tab(i).raw_cost_source;
3108 l_burdened_cost_source_tab(i) := p_budget_lines_tab(i).burdened_cost_source;
3109 l_revenue_source_tab(i) := p_budget_lines_tab(i).revenue_source;
3110 l_resource_assignment_id_tab(i) := p_budget_lines_tab(i).resource_assignment_id;
3111
3112 END LOOP;
3113 END IF;
3114
3115 IF P_PA_DEBUG_MODE = 'Y' THEN
3116 pa_debug.g_err_stage:= 'populated the plsql tables';
3117 pa_debug.write('CREATE_FINPLAN_LINES: ' || g_module_name,pa_debug.g_err_stage,3);
3118
3119 pa_debug.g_err_stage:= 'Delete records if any from the rollup tmp';
3120 pa_debug.write('CREATE_FINPLAN_LINES: ' || g_module_name,pa_debug.g_err_stage,3);
3121 END IF;
3122
3123 delete from pa_fp_rollup_tmp; /* Included after UT */
3124
3125 /* 2727304: Getting the Proj and Proj Func Currencies of the Fin Plan version's project.
3126 These will be used to populate the Proj and Projfunc currency codes in the
3127 pa_fp_rollup_tmp table in case they are not being passed to this API. */
3128
3129 IF P_PA_DEBUG_MODE = 'Y' THEN
3130 pa_debug.g_err_stage:= 'Getting the proj and projfunc currency codes';
3131 pa_debug.write('CREATE_FINPLAN_LINES: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
3132 END IF;
3133
3134 SELECT project_currency_code
3135 ,projfunc_currency_code
3136 INTO l_proj_currency_code
3137 ,l_projfunc_currency_code
3138 FROM pa_projects_all
3139 WHERE project_id = (SELECT project_id
3140 FROM pa_budget_versions
3141 WHERE budget_version_id = p_fin_plan_version_id);
3142
3143 --Bulk insert into the rollup tmp table.
3144 IF nvl(p_budget_lines_tab.last,0) > 0 THEN
3145 FORALL i in p_budget_lines_tab.first..p_budget_lines_tab.last
3146 Insert into pa_fp_rollup_tmp
3147 (
3148 system_reference1 --task_id
3149 ,system_reference2 --rlmid
3150 ,System_reference3 -- CBS_ELEMENT_ID 16598322
3151 ,description
3152 ,start_date
3153 ,end_date
3154 ,period_name
3155 ,quantity
3156 ,system_reference4 --unit_of_measure
3157 ,system_reference5 --track_as_labor_flag
3158 ,txn_currency_code
3159 ,project_currency_code --added for #2727304
3160 ,projfunc_currency_code --added for #2727304
3161 ,projfunc_raw_cost
3162 ,projfunc_burdened_cost
3163 ,projfunc_revenue
3164 ,txn_raw_cost
3165 ,txn_burdened_cost
3166 ,txn_revenue
3167 ,project_raw_cost
3168 ,project_burdened_cost
3169 ,project_revenue
3170 ,change_reason_code
3171 ,attribute_category
3172 ,attribute1
3173 ,attribute2
3174 ,attribute3
3175 ,attribute4
3176 ,attribute5
3177 ,attribute6
3178 ,attribute7
3179 ,attribute8
3180 ,attribute9
3181 ,attribute10
3182 ,attribute11
3183 ,attribute12
3184 ,attribute13
3185 ,attribute14
3186 ,attribute15
3187 ,PROJFUNC_COST_RATE_TYPE
3188 ,PROJFUNC_COST_RATE_DATE_TYPE
3189 ,PROJFUNC_COST_RATE_DATE
3190 ,PROJFUNC_COST_EXCHANGE_RATE
3191 ,PROJFUNC_REV_RATE_TYPE
3192 ,PROJFUNC_REV_RATE_DATE_TYPE
3193 ,PROJFUNC_REV_RATE_DATE
3194 ,PROJFUNC_REV_EXCHANGE_RATE
3195 ,PROJECT_COST_RATE_TYPE
3196 ,PROJECT_COST_RATE_DATE_TYPE
3197 ,PROJECT_COST_RATE_DATE
3198 ,PROJECT_COST_EXCHANGE_RATE
3199 ,PROJECT_REV_RATE_TYPE
3200 ,PROJECT_REV_RATE_DATE_TYPE
3201 ,PROJECT_REV_RATE_DATE
3202 ,PROJECT_REV_EXCHANGE_RATE
3203 ,pm_product_code
3204 ,pm_budget_line_reference
3205 ,quantity_source
3206 ,raw_cost_source
3207 ,burdened_cost_source
3208 ,revenue_source
3209 ,resource_assignment_id
3210 ,budget_version_id
3211 )
3212 Values
3213 (
3214 l_task_id_tab(i)
3215 ,l_resource_list_member_id_tab(i)
3216 ,l_cbs_element_id_tab(i) -- Added for CBS 16598322
3217 ,l_description_tab(i)
3218 ,l_start_date_tab(i)
3219 ,l_end_date_tab(i)
3220 ,l_period_name_tab(i)
3221 ,l_quantity_tab(i)
3222 ,l_unit_of_measure_tab(i)
3223 ,l_track_as_labor_flag_tab(i)
3224 ,l_txn_currency_code_tab(i)
3225 ,l_proj_currency_code --added for #2727304
3226 ,l_projfunc_currency_code --added for #2727304
3227 ,l_raw_cost_tab(i)
3228 ,l_burdened_cost_tab(i)
3229 ,l_revenue_tab(i)
3230 ,l_txn_raw_cost_tab(i)
3231 ,l_txn_burdened_cost_tab(i)
3232 ,l_txn_revenue_tab(i)
3233 ,l_project_raw_cost_tab(i)
3234 ,l_project_burdened_cost_tab(i)
3235 ,l_project_revenue_tab(i)
3236 ,l_change_reason_code_tab(i)
3237 ,l_attribute_category_tab(i)
3238 ,l_attribute1_tab(i)
3239 ,l_attribute2_tab(i)
3240 ,l_attribute3_tab(i)
3241 ,l_attribute4_tab(i)
3242 ,l_attribute5_tab(i)
3243 ,l_attribute6_tab(i)
3244 ,l_attribute7_tab(i)
3245 ,l_attribute8_tab(i)
3246 ,l_attribute9_tab(i)
3247 ,l_attribute10_tab(i)
3248 ,l_attribute11_tab(i)
3249 ,l_attribute12_tab(i)
3250 ,l_attribute13_tab(i)
3251 ,l_attribute14_tab(i)
3252 ,l_attribute15_tab(i)
3253 ,l_PF_COST_RATE_TYPE_tab(i)
3254 ,l_PF_COST_RATE_DATE_TYPE_tab(i)
3255 ,l_PF_COST_RATE_DATE_tab(i)
3256 ,l_PF_COST_RATE_tab(i)
3257 ,l_PF_REV_RATE_TYPE_tab(i)
3258 ,l_PF_REV_RATE_DATE_TYPE_tab(i)
3259 ,l_PF_REV_RATE_DATE_tab(i)
3260 ,l_PF_REV_RATE_tab(i)
3261 ,l_PJ_COST_RATE_TYPE_tab(i)
3262 ,l_PJ_COST_RATE_DATE_TYPE_tab(i)
3263 ,l_PJ_COST_RATE_DATE_tab(i)
3264 ,l_PJ_COST_RATE_tab(i)
3265 ,l_PJ_REV_RATE_TYPE_tab(i)
3266 ,l_PJ_REV_RATE_DATE_TYPE_tab(i)
3267 ,l_PJ_REV_RATE_DATE_tab(i)
3268 ,l_PJ_REV_RATE_tab(i)
3269 ,l_pm_product_code_tab(i)
3270 ,l_pm_budget_line_reference_tab(i)
3271 ,l_quantity_source_tab(i)
3272 ,l_raw_cost_source_tab(i)
3273 ,l_burdened_cost_source_tab(i)
3274 ,l_revenue_source_tab(i)
3275 ,l_resource_assignment_id_tab(i)
3276 ,p_fin_plan_version_id
3277 );
3278 END IF;
3279
3280 IF P_PA_DEBUG_MODE = 'Y' THEN
3281 pa_debug.g_err_stage:= 'number of records inserted -> ' || sql%ROWCOUNT;
3282 pa_debug.write('CREATE_FINPLAN_LINES: ' || g_module_name,pa_debug.g_err_stage,3);
3283 END IF;
3284
3285 --Call procedure pa_fp_elements_pub.CREATE_ASSGMT_FROM_ROLLUPTMP to create
3286 --resource assignments and elements.
3287
3288 IF P_PA_DEBUG_MODE = 'Y' THEN
3289 pa_debug.g_err_stage:= 'calling create_assgmt_from_rolluptmp';
3290 pa_debug.write('CREATE_FINPLAN_LINES: ' || g_module_name,pa_debug.g_err_stage,3);
3291 END IF;
3292
3293 pa_fp_elements_pub.create_assgmt_from_rolluptmp
3294 ( p_fin_plan_version_id => p_fin_plan_version_id
3295 ,x_return_status => x_return_status
3296 ,x_msg_count => x_msg_count
3297 ,x_msg_data => x_msg_data );
3298
3299 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3300 IF P_PA_DEBUG_MODE = 'Y' THEN
3301 pa_debug.g_err_stage:= 'Error Calling create_assgmt_from_rolluptmp';
3302 pa_debug.write('CREATE_FINPLAN_LINES: ' || g_module_name,pa_debug.g_err_stage,5);
3303 END IF;
3304 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
3305 END IF;
3306
3307 --Bug # 3507156-<Patchset M: B and F impact changes : AMG:>
3308 --Commented the api PROCESS_MODIFIED_LINES
3309 --Comment START
3310 /*
3311 IF P_PA_DEBUG_MODE = 'Y' THEN
3312 pa_debug.g_err_stage:= 'calling PROCESS_MODIFIED_LINES';
3313 pa_debug.write('CREATE_FINPLAN_LINES: ' || g_module_name,pa_debug.g_err_stage,3);
3314 END IF;
3315
3316 --Call process_modified_lines in edit line package.
3317 PA_FP_EDIT_LINE_PKG.PROCESS_MODIFIED_LINES
3318 ( p_calling_context => l_calling_context -- Bug# 2674353
3319 ,p_resource_assignment_id => NULL
3320 ,p_fin_plan_version_id => p_fin_plan_version_id
3321 ,x_return_status => x_return_status
3322 ,x_msg_count => x_msg_count
3323 ,x_msg_data => x_msg_data );
3324
3325 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3326
3327 IF P_PA_DEBUG_MODE = 'Y' THEN
3328 pa_debug.g_err_stage:= 'Error Calling PROCESS_MODIFIED_LINES';
3329 pa_debug.write('CREATE_FINPLAN_LINES: ' || g_module_name,pa_debug.g_err_stage,5);
3330 END IF;
3331 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
3332 END IF;
3333
3334 IF P_PA_DEBUG_MODE = 'Y' THEN
3335 pa_debug.g_err_stage:= 'Exiting CREATE_FINPLAN_LINES';
3336 pa_debug.write('CREATE_FINPLAN_LINES: ' || g_module_name,pa_debug.g_err_stage,3);
3337 END IF;
3338
3339 */
3340 --Bug # 3507156-<Patchset M: B and F impact changes : AMG:>
3341 --Replaced PA_FP_EDIT_LINE_PKG.PROCESS_MODIFIED_LINES with an insert to pa_budget_lines
3342
3343 -- Bug 3825873 17-JUL-2004 Do not insert amounts as calculate first checks existing values
3344 -- and the input values are different and then only acts on the record.
3345
3346 --Bug 4133468. Calculate Api will be called after this insert stmt(call is in add_finplan_lines API).In autobaseline
3347 --flow, to make sure that the project currency amounts stamped by the calculate API are same as the project currency
3348 --amounts in the funding lines, exchange rate is stamped as (pc in funding lines)/(pfc in funding lines) and
3349 --rate type is stamped as User for project revenue conversion attrs.
3350
3351 /* -----------------------------------------------------------------------------------------
3352 * Bug 4221590: commenting out the following code to avoid creation of budget lines
3353 * with null quantities, instead population pa_fp_spread_calc_tmp1, so that calculate
3354 * api can use that to insert/spread the budget lines passed from AMG/MSP
3355 *----------------------------------------------------------------------------------------
3356 INSERT INTO pa_budget_lines(
3357 RESOURCE_ASSIGNMENT_ID
3358 ,BUDGET_LINE_ID
3359 ,BUDGET_VERSION_ID
3360 ,START_DATE
3361 ,LAST_UPDATE_DATE
3362 ,LAST_UPDATED_BY
3363 ,CREATION_DATE
3364 ,CREATED_BY
3365 ,LAST_UPDATE_LOGIN
3366 ,END_DATE
3367 ,PERIOD_NAME
3368 ,QUANTITY
3369 ,RAW_COST
3370 ,BURDENED_COST
3371 ,REVENUE
3372 ,CHANGE_REASON_CODE
3373 ,DESCRIPTION
3374 ,ATTRIBUTE_CATEGORY
3375 ,ATTRIBUTE1
3376 ,ATTRIBUTE2
3377 ,ATTRIBUTE3
3378 ,ATTRIBUTE4
3379 ,ATTRIBUTE5
3380 ,ATTRIBUTE6
3381 ,ATTRIBUTE7
3382 ,ATTRIBUTE8
3383 ,ATTRIBUTE9
3384 ,ATTRIBUTE10
3385 ,ATTRIBUTE11
3386 ,ATTRIBUTE12
3387 ,ATTRIBUTE13
3388 ,ATTRIBUTE14
3389 ,ATTRIBUTE15
3390 ,RAW_COST_SOURCE
3391 ,BURDENED_COST_SOURCE
3392 ,QUANTITY_SOURCE
3393 ,REVENUE_SOURCE
3394 ,PROJFUNC_CURRENCY_CODE
3395 ,PROJFUNC_COST_RATE_TYPE
3396 ,PROJFUNC_COST_EXCHANGE_RATE
3397 ,PROJFUNC_COST_RATE_DATE_TYPE
3398 ,PROJFUNC_COST_RATE_DATE
3399 ,PROJECT_CURRENCY_CODE
3400 ,PROJECT_COST_RATE_TYPE
3401 ,PROJECT_COST_EXCHANGE_RATE
3402 ,PROJECT_COST_RATE_DATE_TYPE
3403 ,PROJECT_COST_RATE_DATE
3404 ,PROJECT_RAW_COST
3405 ,PROJECT_BURDENED_COST
3406 ,PROJECT_REVENUE
3407 ,TXN_RAW_COST
3408 ,TXN_BURDENED_COST
3409 ,TXN_REVENUE
3410 ,TXN_CURRENCY_CODE
3411 ,BUCKETING_PERIOD_CODE
3412 ,PROJFUNC_REV_RATE_DATE_TYPE
3413 ,PROJFUNC_REV_RATE_DATE
3414 ,PROJFUNC_REV_RATE_TYPE
3415 ,PROJFUNC_REV_EXCHANGE_RATE
3416 ,PROJECT_REV_RATE_TYPE
3417 ,PROJECT_REV_EXCHANGE_RATE
3418 ,PROJECT_REV_RATE_DATE_TYPE
3419 ,PROJECT_REV_RATE_DATE
3420 ,PM_PRODUCT_CODE
3421 ,PM_BUDGET_LINE_REFERENCE ) -- Added for bug 3833724
3422
3423 (SELECT
3424 RESOURCE_ASSIGNMENT_ID
3425 ,pa_budget_lines_s.nextval
3426 ,p_fin_plan_version_id
3427 ,START_DATE
3428 ,SYSDATE
3429 ,FND_GLOBAL.USER_ID
3430 ,SYSDATE
3431 ,FND_GLOBAL.USER_ID
3432 ,FND_GLOBAL.LOGIN_ID
3433 ,END_DATE
3434 ,PERIOD_NAME
3435 ,null--QUANTITY
3436 ,null--PROJFUNC_RAW_COST
3437 ,null--PROJFUNC_BURDENED_COST
3438 ,NULL--PROJFUNC_REVENUE
3439 ,CHANGE_REASON_CODE
3440 ,DESCRIPTION
3441 ,ATTRIBUTE_CATEGORY
3442 ,ATTRIBUTE1
3443 ,ATTRIBUTE2
3444 ,ATTRIBUTE3
3445 ,ATTRIBUTE4
3446 ,ATTRIBUTE5
3447 ,ATTRIBUTE6
3448 ,ATTRIBUTE7
3449 ,ATTRIBUTE8
3450 ,ATTRIBUTE9
3451 ,ATTRIBUTE10
3452 ,ATTRIBUTE11
3453 ,ATTRIBUTE12
3454 ,ATTRIBUTE13
3455 ,ATTRIBUTE14
3456 ,ATTRIBUTE15
3457 ,nvl(RAW_COST_SOURCE,decode(PROJFUNC_RAW_COST,null,null,PA_FP_CONSTANTS_PKG.G_AMOUNT_SOURCE_MANUAL_M))
3458 ,nvl(BURDENED_COST_SOURCE,decode(PROJFUNC_BURDENED_COST,null,null,PA_FP_CONSTANTS_PKG.G_AMOUNT_SOURCE_MANUAL_M))
3459 ,nvl(QUANTITY_SOURCE,decode(QUANTITY,null,null,PA_FP_CONSTANTS_PKG.G_AMOUNT_SOURCE_MANUAL_M))
3460 ,nvl(REVENUE_SOURCE,decode(PROJFUNC_REVENUE,null,null,PA_FP_CONSTANTS_PKG.G_AMOUNT_SOURCE_MANUAL_M))
3461 ,PROJFUNC_CURRENCY_CODE
3462 ,PROJFUNC_COST_RATE_TYPE
3463 ,PROJFUNC_COST_EXCHANGE_RATE
3464 ,PROJFUNC_COST_RATE_DATE_TYPE
3465 ,PROJFUNC_COST_RATE_DATE
3466 ,PROJECT_CURRENCY_CODE
3467 ,PROJECT_COST_RATE_TYPE
3468 ,PROJECT_COST_EXCHANGE_RATE
3469 ,PROJECT_COST_RATE_DATE_TYPE
3470 ,PROJECT_COST_RATE_DATE
3471 ,null--PROJECT_RAW_COST
3472 ,null--PROJECT_BURDENED_COST
3473 ,null--PROJECT_REVENUE
3474 ,null--TXN_RAW_COST
3475 ,null--TXN_BURDENED_COST
3476 ,null--TXN_REVENUE
3477 ,TXN_CURRENCY_CODE
3478 ,BUCKETING_PERIOD_CODE
3479 ,PROJFUNC_REV_RATE_DATE_TYPE
3480 ,PROJFUNC_REV_RATE_DATE
3481 ,PROJFUNC_REV_RATE_TYPE
3482 ,PROJFUNC_REV_EXCHANGE_RATE
3483 ,DECODE(p_calling_context,
3484 PA_FP_CONSTANTS_PKG.G_AUTOMATIC_BASELINE,'User',
3485 PROJECT_REV_RATE_TYPE)--Bug 4133468. PROJECT_REV_RATE_TYPE
3486 ,DECODE(p_calling_context,
3487 PA_FP_CONSTANTS_PKG.G_AUTOMATIC_BASELINE,DECODE(PROJFUNC_REVENUE,
3488 NULL,NULL,
3489 0,0,
3490 (PROJECT_REVENUE/PROJFUNC_REVENUE)),
3491 PROJECT_REV_EXCHANGE_RATE)--Bug 4133468. PROJECT_REV_EXCHANGE_RATE
3492 ,DECODE(p_calling_context,
3493 PA_FP_CONSTANTS_PKG.G_AUTOMATIC_BASELINE,NULL,
3494 PROJECT_REV_RATE_DATE_TYPE)--Bug 4133468. PROJECT_REV_RATE_DATE_TYPE
3495 ,DECODE(p_calling_context,
3496 PA_FP_CONSTANTS_PKG.G_AUTOMATIC_BASELINE,NULL,
3497 PROJECT_REV_RATE_DATE)--Bug 4133468. PROJECT_REV_RATE_DATE
3498 ,PM_PRODUCT_CODE -- , l_pm_product_code changed to pm_product_code for bug 3833724
3499 ,PM_BUDGET_LINE_REFERENCE -- Added for bug 3833724
3500 FROM pa_fp_rollup_tmp tmp
3501 WHERE tmp.budget_line_id IS NULL
3502 AND (tmp.txn_raw_cost IS NOT NULL
3503 or tmp.txn_burdened_cost IS NOT NULL
3504 or tmp.quantity IS NOT NULL
3505 or tmp.txn_revenue IS NOT NULL));
3506
3507 IF P_PA_DEBUG_MODE = 'Y' THEN
3508 pa_debug.g_err_stage:= 'number of records inserted -> ' || sql%ROWCOUNT;
3509 pa_debug.write('CREATE_FINPLAN_LINES: ' || g_module_name,pa_debug.g_err_stage,3);
3510 END IF;
3511 ---------------------------------------------------------------------------------------------*/
3512
3513 /* Bug 4221590:inserting into PA_FP_SPREAD_CALC_TMP1 */
3514 DELETE FROM PA_FP_SPREAD_CALC_TMP1;
3515
3516 IF P_PA_DEBUG_MODE = 'Y' THEN
3517 pa_debug.g_err_stage:= 'inserting into pa_fp_spread_calc_tmp1 -> ' || sql%ROWCOUNT;
3518 pa_debug.write('CREATE_FINPLAN_LINES: ' || g_module_name,pa_debug.g_err_stage,3);
3519 END IF;
3520
3521 INSERT INTO PA_FP_SPREAD_CALC_TMP1(
3522 RESOURCE_ASSIGNMENT_ID
3523 ,BUDGET_VERSION_ID
3524 ,START_DATE
3525 ,BL_CREATION_DATE
3526 ,BL_CREATED_BY
3527 ,END_DATE
3528 ,PERIOD_NAME
3529 ,CHANGE_REASON_CODE
3530 ,DESCRIPTION
3531 ,ATTRIBUTE_CATEGORY
3532 ,ATTRIBUTE1
3533 ,ATTRIBUTE2
3534 ,ATTRIBUTE3
3535 ,ATTRIBUTE4
3536 ,ATTRIBUTE5
3537 ,ATTRIBUTE6
3538 ,ATTRIBUTE7
3539 ,ATTRIBUTE8
3540 ,ATTRIBUTE9
3541 ,ATTRIBUTE10
3542 ,ATTRIBUTE11
3543 ,ATTRIBUTE12
3544 ,ATTRIBUTE13
3545 ,ATTRIBUTE14
3546 ,ATTRIBUTE15
3547 ,RAW_COST_SOURCE
3548 ,BURDENED_COST_SOURCE
3549 ,QUANTITY_SOURCE
3550 ,REVENUE_SOURCE
3551 ,PROJFUNC_CURRENCY_CODE
3552 ,PROJFUNC_COST_RATE_TYPE
3553 ,PROJFUNC_COST_EXCHANGE_RATE
3554 ,PROJFUNC_COST_RATE_DATE_TYPE
3555 ,PROJFUNC_COST_RATE_DATE
3556 ,PROJECT_CURRENCY_CODE
3557 ,PROJECT_COST_RATE_TYPE
3558 ,PROJECT_COST_EXCHANGE_RATE
3559 ,PROJECT_COST_RATE_DATE_TYPE
3560 ,PROJECT_COST_RATE_DATE
3561 ,TXN_CURRENCY_CODE
3562 ,BUCKETING_PERIOD_CODE
3563 ,PROJFUNC_REV_RATE_DATE_TYPE
3564 ,PROJFUNC_REV_RATE_DATE
3565 ,PROJFUNC_REV_RATE_TYPE
3566 ,PROJFUNC_REV_EXCHANGE_RATE
3567 ,PROJECT_REV_RATE_TYPE
3568 ,PROJECT_REV_EXCHANGE_RATE
3569 ,PROJECT_REV_RATE_DATE_TYPE
3570 ,PROJECT_REV_RATE_DATE
3571 ,PM_PRODUCT_CODE
3572 ,PM_BUDGET_LINE_REFERENCE ) -- Added for bug 3833724
3573 (SELECT
3574 RESOURCE_ASSIGNMENT_ID
3575 ,p_fin_plan_version_id
3576 ,START_DATE
3577 ,SYSDATE
3578 ,FND_GLOBAL.USER_ID
3579 ,END_DATE
3580 ,PERIOD_NAME
3581 ,CHANGE_REASON_CODE
3582 ,DESCRIPTION
3583 ,ATTRIBUTE_CATEGORY
3584 ,ATTRIBUTE1
3585 ,ATTRIBUTE2
3586 ,ATTRIBUTE3
3587 ,ATTRIBUTE4
3588 ,ATTRIBUTE5
3589 ,ATTRIBUTE6
3590 ,ATTRIBUTE7
3591 ,ATTRIBUTE8
3592 ,ATTRIBUTE9
3593 ,ATTRIBUTE10
3594 ,ATTRIBUTE11
3595 ,ATTRIBUTE12
3596 ,ATTRIBUTE13
3597 ,ATTRIBUTE14
3598 ,ATTRIBUTE15
3599 ,nvl(RAW_COST_SOURCE,decode(PROJFUNC_RAW_COST,null,null,PA_FP_CONSTANTS_PKG.G_AMOUNT_SOURCE_MANUAL_M))
3600 ,nvl(BURDENED_COST_SOURCE,decode(PROJFUNC_BURDENED_COST,null,null,PA_FP_CONSTANTS_PKG.G_AMOUNT_SOURCE_MANUAL_M))
3601 ,nvl(QUANTITY_SOURCE,decode(QUANTITY,null,null,PA_FP_CONSTANTS_PKG.G_AMOUNT_SOURCE_MANUAL_M))
3602 ,nvl(REVENUE_SOURCE,decode(PROJFUNC_REVENUE,null,null,PA_FP_CONSTANTS_PKG.G_AMOUNT_SOURCE_MANUAL_M))
3603 ,PROJFUNC_CURRENCY_CODE
3604 ,PROJFUNC_COST_RATE_TYPE
3605 ,PROJFUNC_COST_EXCHANGE_RATE
3606 ,PROJFUNC_COST_RATE_DATE_TYPE
3607 ,PROJFUNC_COST_RATE_DATE
3608 ,PROJECT_CURRENCY_CODE
3609 ,PROJECT_COST_RATE_TYPE
3610 ,PROJECT_COST_EXCHANGE_RATE
3611 ,PROJECT_COST_RATE_DATE_TYPE
3612 ,PROJECT_COST_RATE_DATE
3613 ,TXN_CURRENCY_CODE
3614 ,BUCKETING_PERIOD_CODE
3615 ,PROJFUNC_REV_RATE_DATE_TYPE
3616 ,PROJFUNC_REV_RATE_DATE
3617 ,PROJFUNC_REV_RATE_TYPE
3618 ,PROJFUNC_REV_EXCHANGE_RATE
3619 ,DECODE(p_calling_context,
3620 PA_FP_CONSTANTS_PKG.G_AUTOMATIC_BASELINE,'User',
3621 PROJECT_REV_RATE_TYPE)--Bug 4133468. PROJECT_REV_RATE_TYPE
3622 ,DECODE(p_calling_context,
3623 PA_FP_CONSTANTS_PKG.G_AUTOMATIC_BASELINE,DECODE(PROJFUNC_REVENUE,
3624 NULL,NULL,
3625 0,0,
3626 (PROJECT_REVENUE/PROJFUNC_REVENUE)),
3627 PROJECT_REV_EXCHANGE_RATE)--Bug 4133468. PROJECT_REV_EXCHANGE_RATE
3628 ,DECODE(p_calling_context,
3629 PA_FP_CONSTANTS_PKG.G_AUTOMATIC_BASELINE,NULL,
3630 PROJECT_REV_RATE_DATE_TYPE)--Bug 4133468. PROJECT_REV_RATE_DATE_TYPE
3631 ,DECODE(p_calling_context,
3632 PA_FP_CONSTANTS_PKG.G_AUTOMATIC_BASELINE,NULL,
3633 PROJECT_REV_RATE_DATE)--Bug 4133468. PROJECT_REV_RATE_DATE
3634 ,PM_PRODUCT_CODE -- , l_pm_product_code changed to pm_product_code for bug 3833724
3635 ,PM_BUDGET_LINE_REFERENCE -- Added for bug 3833724
3636 FROM pa_fp_rollup_tmp tmp
3637 WHERE tmp.budget_line_id IS NULL); /*Changed for bug 4224464. When a budget line is passed for which amounts and quantity
3638 were not passed i.e these values were miss_xxx values then these lines wont get selected here
3639 but in this case our intent should be to not update these columns for these lines and update
3640 the rest of the coulmns*/
3641 /* AND (tmp.txn_raw_cost IS NOT NULL
3642 or tmp.txn_burdened_cost IS NOT NULL
3643 or tmp.quantity IS NOT NULL
3644 or tmp.txn_revenue IS NOT NULL));*/
3645
3646 IF P_PA_DEBUG_MODE = 'Y' THEN
3647 pa_debug.g_err_stage:= 'number of records inserted -> ' || sql%ROWCOUNT;
3648 pa_debug.write('CREATE_FINPLAN_LINES: ' || g_module_name,pa_debug.g_err_stage,3);
3649 END IF;
3650
3651 -- Bug 3861261 Update resource assignments planning start and end date
3652 -- as the min(start_date) and max(end_date) of the corresponding budget lines
3653 -- Start changes for Bug 6432606
3654 -- In the AMG API flow for a scenario when task is having actuals the budget line
3655 -- corresponding that is not getting processed and stored in pa_fp_spread_calc_tmp1
3656 -- coz of that start date of that budget line is not taken in consideration.
3657 -- To avoid this planning_start_date is updated with original value if the original
3658 -- planning_start_date is least.
3659 IF(p_calling_context = PA_FP_CONSTANTS_PKG.G_AMG_API) THEN
3660 -- For scenario if actuals exist then planning start date for resource assignment
3661 -- should be least of the value presnt in pa_fp_spread_calc_tmp1 and pa_budget_lines
3662 -- else it should be least of value present in pa_fp_spread_calc_tmp1.
3663
3664 --bug#8854015 Added condition to directly update the planning_start_date and planning_end_date
3665 -- if the updation is for non-time based.
3666 l_time_phased_type_code := PA_FIN_PLAN_UTILS.Get_Time_Phased_code(p_fin_plan_version_id);
3667 IF l_time_phased_type_code = 'N' THEN
3668
3669 -- Bug 15861188 : added following update statement
3670 UPDATE PA_FP_SPREAD_CALC_TMP1 cache
3671 SET (cache.start_date ,cache.end_date) =
3672 (select planning_start_date,planning_end_date
3673 FROM pa_resource_assignments pra
3674 where pra.resource_assignment_id = cache.resource_assignment_id )
3675 where cache.budget_version_id = p_fin_plan_version_id
3676 AND cache.start_date IS NULL
3677 AND cache.end_date IS NULL;
3678
3679 update pa_resource_assignments pra
3680 set (planning_start_date, planning_end_date)
3681 = (select nvl(tmp.start_date, planning_start_date),
3682 nvl(tmp.end_date, planning_end_date)
3683 from pa_fp_spread_calc_tmp1 tmp
3684 where tmp.resource_assignment_id = pra.resource_assignment_id)
3685 where pra.budget_version_id = p_fin_plan_version_id;
3686
3687 ELSE
3688 update pa_resource_assignments pra
3689 set (planning_start_date, planning_end_date)
3690 = (select decode(min(pbl.start_date),NULL,
3691 nvl(min(tmp.start_date), planning_start_date),
3692 least(nvl(min(tmp.start_date), planning_start_date),
3693 nvl(min(pbl.start_date), planning_start_date))),
3694 greatest(nvl(max(tmp.end_date), planning_end_date), planning_end_date) -- bug#10376078
3695 from pa_fp_spread_calc_tmp1 tmp, pa_budget_lines pbl
3696 where tmp.resource_assignment_id = pra.resource_assignment_id
3697 and pbl.resource_assignment_id (+)= tmp.resource_assignment_id)
3698 where pra.budget_version_id = p_fin_plan_version_id;
3699 END IF;
3700 --bug#14089767 Making sure that sp_fixed_date is in sync with the corresponding planning_start_date for Fixed Date spread curve
3701 update pa_resource_assignments pra
3702 set pra.sp_fixed_date = pra.planning_start_date
3703 where pra.budget_version_id = p_fin_plan_version_id
3704 and pra.spread_curve_id = 6;
3705 ELSE
3706 update pa_resource_assignments pra
3707 set (planning_start_date, planning_end_date)
3708 = (select nvl(min(start_date), planning_start_date),
3709 nvl(max(end_date), planning_end_date)
3710 from pa_fp_spread_calc_tmp1 tmp /* Bug 4221590 */
3711 where tmp.resource_assignment_id = pra.resource_assignment_id)
3712 where pra.budget_version_id = p_fin_plan_version_id;
3713 END IF;
3714 --End changes for Bug 6432606
3715
3716 IF p_pa_debug_mode = 'Y' THEN
3717 pa_debug.reset_err_stack;
3718 END IF;
3719 EXCEPTION
3720 WHEN PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc THEN
3721
3722 x_return_status := FND_API.G_RET_STS_ERROR;
3723 l_msg_count := FND_MSG_PUB.count_msg;
3724 IF l_msg_count = 1 THEN
3725 PA_INTERFACE_UTILS_PUB.get_messages
3726 (p_encoded => FND_API.G_TRUE
3727 ,p_msg_index => 1
3728 ,p_msg_count => l_msg_count
3729 ,p_msg_data => l_msg_data
3730 ,p_data => l_data
3731 ,p_msg_index_out => l_msg_index_out);
3732 x_msg_data := l_data;
3733 x_msg_count := l_msg_count;
3734 ELSE
3735 x_msg_count := l_msg_count;
3736 END IF;
3737
3738 IF P_PA_DEBUG_MODE = 'Y' THEN
3739 pa_debug.g_err_stage:= 'Invalid Arguments Passed';
3740 pa_debug.write('CREATE_FINPLAN_LINES: ' || g_module_name,pa_debug.g_err_stage,5);
3741 pa_debug.reset_err_stack;
3742 END IF;
3743 RAISE;
3744
3745 WHEN others THEN
3746
3747 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3748 x_msg_count := 1;
3749 x_msg_data := SQLERRM;
3750 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'pa_fin_plan_pvt'
3751 ,p_procedure_name => 'CREATE_FINPLAN_LINES');
3752 IF P_PA_DEBUG_MODE = 'Y' THEN
3753 pa_debug.g_err_stage:= 'Unexpected Error'||SQLERRM;
3754 pa_debug.write('CREATE_FINPLAN_LINES: ' || g_module_name,pa_debug.g_err_stage,5);
3755 pa_debug.reset_err_stack;
3756 END IF;
3757 RAISE;
3758
3759 END CREATE_FINPLAN_LINES;
3760
3761 /*Given the name of a plan type this function returns the
3762 Id of that plan type if it exists. Otherwise Null is
3763 returned
3764 */
3765
3766 FUNCTION Fetch_Plan_Type_Id
3767 (p_fin_plan_type_name pa_fin_plan_types_tl.name%TYPE) RETURN NUMBER IS
3768
3769 CURSOR l_get_plan_type_id_csr
3770 (p_fin_plan_type_name pa_fin_plan_types_tl.name%TYPE )
3771 IS
3772 SELECT fin_plan_type_id
3773 FROM pa_fin_plan_types_vl
3774 WHERE name=p_fin_plan_type_name;
3775
3776 x_fin_plan_type_id pa_fin_plan_types_b.fin_plan_type_id%TYPE;
3777
3778 BEGIN
3779
3780 OPEN l_get_plan_type_id_csr(p_fin_plan_type_name);
3781 FETCH l_get_plan_type_id_csr INTO x_fin_plan_type_id;
3782
3783 IF (l_get_plan_type_id_csr%FOUND) THEN
3784
3785 CLOSE l_get_plan_type_id_csr;
3786 RETURN x_fin_plan_type_id;
3787
3788 ELSE
3789
3790 CLOSE l_get_plan_type_id_csr;
3791 RETURN NULL;
3792
3793 END IF;
3794
3795 END Fetch_Plan_Type_Id;
3796
3797 /*This Procudure accepts plan type id and plan type name. If plan type id is not null
3798 its validity is checked and an error message is thrown if the id is invalid.If the name
3799 (and not id) is passed, and if it is valid (case sensitive search is made while trying ot
3800 find the id of the name) tehe Id is passed. Otherwise an error message is thrown
3801 */
3802
3803 PROCEDURE convert_plan_type_name_to_id
3804 ( p_fin_plan_type_id IN pa_fin_plan_types_b.fin_plan_type_id%TYPE
3805 ,p_fin_plan_type_name IN pa_fin_plan_types_tl.name%TYPE
3806 ,x_fin_plan_type_id OUT NOCOPY pa_fin_plan_types_b.fin_plan_type_id%TYPE --File.Sql.39 bug 4440895
3807 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
3808 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
3809 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
3810 ) IS
3811
3812 CURSOR l_fin_plan_type_id_csr
3813 (p_fin_plan_type_id pa_fin_plan_types_b.fin_plan_type_id%TYPE)
3814 IS
3815 SELECT fin_plan_type_id
3816 FROM pa_fin_plan_types_b
3817 WHERE fin_plan_type_id=p_fin_plan_type_id;
3818
3819 l_msg_count NUMBER := 0;
3820 l_data VARCHAR2(2000);
3821 l_msg_data VARCHAR2(2000);
3822 l_msg_index_out NUMBER;
3823 l_debug_mode VARCHAR2(1);
3824
3825 l_debug_level2 CONSTANT NUMBER := 2;
3826 l_debug_level3 CONSTANT NUMBER := 3;
3827 l_debug_level4 CONSTANT NUMBER := 4;
3828 l_debug_level5 CONSTANT NUMBER := 5;
3829
3830 BEGIN
3831
3832 x_msg_count := 0;
3833 x_return_status := FND_API.G_RET_STS_SUCCESS;
3834 l_debug_mode := NVL(FND_PROFILE.value('PA_DEBUG_MODE'),'N');
3835
3836 IF p_pa_debug_mode = 'Y' THEN
3837 pa_debug.set_curr_function( p_function => 'convert_plan_type_name_to_id',
3838 p_debug_mode => l_debug_mode );
3839 END IF;
3840 -- If fin plan type id is passed. validate the fin plan type id. If it is not passed
3841 -- then convert name to fin plan type id
3842 IF (p_fin_plan_type_id IS NOT NULL) THEN
3843
3844 OPEN l_fin_plan_type_id_csr(p_fin_plan_type_id);
3845 FETCH l_fin_plan_type_id_csr INTO x_fin_plan_type_id;
3846
3847 IF(l_fin_plan_type_id_csr%NOTFOUND) THEN
3848
3849 PA_UTILS.ADD_MESSAGE
3850 (p_app_short_name => 'PA',
3851 p_msg_name => 'PA_FP_INVALID_PLAN_TYPE',
3852 p_token1 => 'PLAN_TYPE',
3853 p_value1 => p_fin_plan_type_id);
3854
3855 x_return_status := FND_API.G_RET_STS_ERROR;
3856 x_fin_plan_type_id:=NULL;
3857 CLOSE l_fin_plan_type_id_csr;
3858 IF l_debug_mode='Y' THEN
3859 pa_debug.g_err_stage := 'p_fin_plan_type_id is '||p_fin_plan_type_id ;
3860 pa_debug.write('convert_plan_type_name_to_id: ' || g_module_name
3861 ,pa_debug.g_err_stage,l_debug_level5);
3862 END IF;
3863 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
3864
3865 ELSE
3866
3867 close l_fin_plan_type_id_csr;
3868 x_fin_plan_type_id:=p_fin_plan_type_id;
3869
3870 END IF;
3871
3872 ELSIF ( p_fin_plan_type_name IS NOT NULL) THEN
3873
3874 x_fin_plan_type_id := fetch_plan_type_id(p_fin_plan_type_name);
3875 IF(x_fin_plan_type_id IS NULL) THEN
3876
3877 PA_UTILS.ADD_MESSAGE
3878 (p_app_short_name => 'PA',
3879 p_msg_name => 'PA_FP_INVALID_PLAN_TYPE',
3880 p_token1 => 'PLAN_TYPE',
3881 p_value1 => p_fin_plan_type_name);
3882
3883 x_return_status := FND_API.G_RET_STS_ERROR;
3884 IF l_debug_mode='Y' THEN
3885 pa_debug.g_err_stage := 'p_fin_plan_type_name is '||p_fin_plan_type_name ;
3886 pa_debug.write('convert_plan_type_name_to_id: ' || g_module_name
3887 ,pa_debug.g_err_stage,l_debug_level5);
3888 END IF;
3889 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
3890
3891 END IF;
3892 IF l_debug_mode='Y' THEN
3893 pa_debug.g_err_stage := 'x_fin_plan_type_id derived is '||x_fin_plan_type_id ;
3894 pa_debug.write('convert_plan_type_name_to_id: ' || g_module_name
3895 ,pa_debug.g_err_stage,l_debug_level5);
3896 END IF;
3897
3898
3899 ELSE
3900
3901 PA_UTILS.ADD_MESSAGE
3902 (p_app_short_name => 'PA',
3903 p_msg_name => 'PA_FP_INV_PARAM_PASSED');
3904
3905 x_return_status := FND_API.G_RET_STS_ERROR;
3906 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
3907 END IF;
3908
3909 IF l_debug_mode='Y' THEN
3910 pa_debug.g_err_stage := 'p_fin_plan_type_name is '||p_fin_plan_type_name ;
3911 pa_debug.write('convert_plan_type_name_to_id: ' || g_module_name
3912 ,pa_debug.g_err_stage,l_debug_level5);
3913 pa_debug.reset_curr_function;
3914 END IF;
3915 EXCEPTION
3916 WHEN PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc THEN
3917
3918 IF x_return_status IS NULL OR
3919 x_return_status = FND_API.G_RET_STS_SUCCESS THEN
3920 x_return_status := FND_API.G_RET_STS_ERROR;
3921 END IF;
3922 l_msg_count := FND_MSG_PUB.count_msg;
3923
3924 IF l_msg_count = 1 and x_msg_data IS NULL THEN
3925 PA_INTERFACE_UTILS_PUB.get_messages
3926 (p_encoded => FND_API.G_TRUE
3927 ,p_msg_index => 1
3928 ,p_msg_count => l_msg_count
3929 ,p_msg_data => l_msg_data
3930 ,p_data => l_data
3931 ,p_msg_index_out => l_msg_index_out);
3932 x_msg_data := l_data;
3933 x_msg_count := l_msg_count;
3934 ELSE
3935 x_msg_count := l_msg_count;
3936 END IF;
3937 IF l_debug_mode='Y' THEN
3938 pa_debug.reset_curr_function;
3939 END IF;
3940 RETURN;
3941
3942 WHEN OTHERS THEN
3943
3944 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3945 x_msg_count := 1;
3946 x_msg_data := SQLERRM;
3947
3948
3949 FND_MSG_PUB.add_exc_msg
3950 ( p_pkg_name => 'pa_fin_plan_pvt'
3951 ,p_procedure_name => 'convert_plan_type_name_to_id'
3952 ,p_error_text => x_msg_data);
3953
3954 IF l_debug_mode = 'Y' THEN
3955 pa_debug.g_err_stage:= 'Unexpected Error'||x_msg_data;
3956 pa_debug.write(g_module_name,pa_debug.g_err_stage,
3957 l_debug_level5);
3958 pa_debug.reset_curr_function;
3959 END IF;
3960 RAISE;
3961
3962 END convert_plan_type_name_to_id;
3963
3964 /*=====================================================================
3965 Procedure Name: DELETE_WP_OPTION
3966
3967 This procedure is added as part of FPM Development. Tracking Bug - 3354518.
3968
3969 Purpose: This api Deletes the proj fp options data pertaining
3970 to the workplan type attached to the project for
3971 the passed project id.
3972 Deletes data from the following tables -
3973 1) pa_proj_fp_options
3974 2) pa_fp_txn_currencies
3975 3) pa_proj_period_profiles
3976 4) pa_fp_upgrade_audit
3977
3978 Please note that all validations before calling this API shall be done
3979 in the calling entity.
3980
3981 Parameters:
3982 IN 1) p_project_id - project id.
3983 =======================================================================*/
3984 PROCEDURE Delete_wp_option
3985 (p_project_id IN pa_projects_all.project_id%TYPE
3986 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
3987 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
3988 ,x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
3989 IS
3990
3991 --Start of variables used for debugging
3992 l_msg_count NUMBER :=0;
3993 l_data VARCHAR2(2000);
3994 l_msg_data VARCHAR2(2000);
3995 l_error_msg_code VARCHAR2(30);
3996 l_msg_index_out NUMBER;
3997 l_return_status VARCHAR2(2000);
3998 l_debug_mode VARCHAR2(30);
3999 --End of variables used for debugging
4000
4001 l_proj_fp_options_id pa_proj_fp_options.proj_fp_options_id%TYPE;
4002 l_sv_id_tbl SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
4003 l_sv_id_count NUMBER;
4004
4005 cursor c_struct_ver(c_project_id pa_projects_all.project_id%TYPE) IS
4006 SELECT project_structure_version_id
4007 FROM pa_budget_versions
4008 WHERE project_id = c_project_id
4009 AND nvl(wp_version_flag,'N') = 'Y';
4010
4011 BEGIN
4012
4013 SAVEPOINT DELETE_WP_OPTION_SAVE;
4014
4015 fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
4016 l_debug_mode := NVL(l_debug_mode, 'N');
4017 x_msg_count := 0;
4018 x_return_status := FND_API.G_RET_STS_SUCCESS;
4019 IF p_pa_debug_mode = 'Y' THEN
4020 PA_DEBUG.Set_Curr_Function( p_function => 'PA_FIN_PLAN_PVT.Delete_wp_option',
4021 p_debug_mode => l_debug_mode );
4022 END IF;
4023 ---------------------------------------------------------------
4024 -- validating input parameter p_project_id.
4025 -- p_project_id cannot be passed as null.
4026 ---------------------------------------------------------------
4027 IF l_debug_mode = 'Y' THEN
4028 pa_debug.g_err_stage:='Validating input parameters';
4029 pa_debug.write('Delete_wp_option: ' || g_module_name,pa_debug.g_err_stage,3);
4030 END IF;
4031
4032 IF (p_project_id IS NULL)
4033 THEN
4034
4035 IF l_debug_mode = 'Y' THEN
4036 pa_debug.write('Delete_wp_options Project Id is null: ' || g_module_name,pa_debug.g_err_stage,5);
4037 END IF;
4038
4039 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
4040 p_msg_name => 'PA_FP_INV_PARAM_PASSED');
4041
4042 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
4043
4044 END IF;
4045
4046 ---------------------------------------------------------------
4047 --Fetch proj_fp_options_id
4048 ---------------------------------------------------------------
4049
4050 IF l_debug_mode = 'Y' THEN
4051 pa_debug.g_err_stage:='Fetching proj_fp_options_id ';
4052 pa_debug.write('Delete_wp_option: ' || g_module_name,pa_debug.g_err_stage,3);
4053 END IF;
4054
4055 SELECT pfo.proj_fp_options_id
4056 INTO l_proj_fp_options_id
4057 FROM pa_proj_fp_options pfo
4058 ,pa_fin_plan_types_b pft
4059 WHERE pfo.project_id = p_project_id
4060 AND pfo.fin_plan_type_id = pft.fin_plan_type_id
4061 AND pfo.fin_plan_option_level_code = PA_FP_CONSTANTS_PKG.G_OPTION_LEVEL_PLAN_TYPE
4062 AND nvl(pft.use_for_workplan_flag,'N') = 'Y';
4063
4064 -----------------------------------------------------------------
4065 -- Fetching the workplan structure version ids for the project id
4066 -- passed into the PLSql table l_sv_id_tbl and then calling API
4067 -- Delete_wp_budget_versions
4068 -----------------------------------------------------------------
4069
4070 IF l_debug_mode = 'Y' THEN
4071 pa_debug.g_err_stage:='Fetching the workplan structure ids for the project id';
4072 pa_debug.write('Delete_wp_option: ' || g_module_name,pa_debug.g_err_stage,3);
4073 END IF;
4074
4075 OPEN c_struct_ver(p_project_id);
4076
4077 FETCH c_struct_ver BULK COLLECT INTO l_sv_id_tbl;
4078 IF c_struct_ver%NOTFOUND THEN
4079 IF p_pa_debug_mode = 'Y' THEN
4080 pa_debug.g_err_stage:= 'No Structure versions for the project_id passed - project_id: '||p_project_id;
4081 pa_debug.write(g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL5);
4082 END IF;
4083 END IF;
4084 CLOSE c_struct_ver;
4085
4086 l_sv_id_count := l_sv_id_tbl.count;
4087
4088 IF l_sv_id_count > 0 THEN
4089
4090 IF l_debug_mode = 'Y' THEN
4091 pa_debug.g_err_stage:='Deleting all version data for the wp structure version ids pertaining to project_id:'||p_project_id;
4092 pa_debug.write('Delete_wp_option: ' || g_module_name,pa_debug.g_err_stage,3);
4093 END IF;
4094
4095 Delete_wp_budget_versions
4096 (p_struct_elem_version_id_tbl => l_sv_id_tbl
4097 ,x_return_status => l_return_status
4098 ,x_msg_count => l_msg_count
4099 ,x_msg_data => l_msg_data);
4100
4101 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4102
4103 IF l_debug_mode = 'Y' THEN
4104 pa_debug.g_err_stage:='Call to Delete_wp_budget_versions is returning error status';
4105 pa_debug.write('Delete_wp_option: ' || g_module_name,pa_debug.g_err_stage,3);
4106 END IF;
4107 RAISE Delete_Ver_Exc_PVT;
4108 END IF;
4109
4110 END IF;
4111
4112 -- Bug 5743297: Moved the following DELETE statements which were before the Delete_wp_budget_versions API
4113 -- call to here for avoiding NO_DATA_FOUND.
4114 ---------------------------------------------------------------
4115 --Deleting data from respective tables
4116 ---------------------------------------------------------------
4117
4118 IF l_debug_mode = 'Y' THEN
4119 pa_debug.g_err_stage:='Deleting data from respective tables';
4120 pa_debug.write('Delete_wp_option: ' || g_module_name,pa_debug.g_err_stage,3);
4121 END IF;
4122
4123 -- delete from pa_proj_fp_options table
4124 DELETE FROM pa_proj_fp_options WHERE proj_fp_options_id = l_proj_fp_options_id;
4125
4126 -- delete from pa_fp_txn_currencies
4127 DELETE FROM pa_fp_txn_currencies WHERE proj_fp_options_id = l_proj_fp_options_id;
4128
4129 -- delete from pa_fp_upgrade_audit
4130 DELETE FROM pa_fp_upgrade_audit WHERE proj_fp_options_id = l_proj_fp_options_id;
4131
4132 IF l_debug_mode = 'Y' THEN
4133 pa_debug.reset_curr_function;
4134 END IF;
4135 EXCEPTION
4136 WHEN NO_DATA_FOUND THEN
4137 IF l_debug_mode = 'Y' THEN
4138 pa_debug.reset_curr_function;
4139 END IF;
4140 WHEN Delete_Ver_Exc_PVT THEN
4141 ROLLBACK TO SAVEPOINT PA_FP_PUB_DELETE_VER;
4142 l_msg_count := FND_MSG_PUB.count_msg;
4143 IF l_msg_count = 1 THEN
4144 PA_INTERFACE_UTILS_PUB.get_messages
4145 (p_encoded => FND_API.G_TRUE
4146 ,p_msg_index => 1
4147 ,p_msg_count => l_msg_count
4148 ,p_msg_data => l_msg_data
4149 ,p_data => l_data
4150 ,p_msg_index_out => l_msg_index_out);
4151 x_msg_data := l_data;
4152 x_msg_count := l_msg_count;
4153 ELSE
4154 x_msg_count := l_msg_count;
4155 END IF;
4156
4157 x_return_status := FND_API.G_RET_STS_ERROR;
4158
4159 IF l_debug_mode = 'Y' THEN
4160 pa_debug.g_err_stage:='Delete_wp_budget_versions returned error';
4161 pa_debug.write('Delete_wp_option: ' || g_module_name,pa_debug.g_err_stage,5);
4162 pa_debug.reset_curr_function;
4163 END IF;
4164
4165 WHEN PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc THEN
4166 l_msg_count := FND_MSG_PUB.count_msg;
4167 IF l_msg_count = 1 THEN
4168 PA_INTERFACE_UTILS_PUB.get_messages
4169 (p_encoded => FND_API.G_TRUE
4170 ,p_msg_index => 1
4171 ,p_msg_count => l_msg_count
4172 ,p_msg_data => l_msg_data
4173 ,p_data => l_data
4174 ,p_msg_index_out => l_msg_index_out);
4175 x_msg_data := l_data;
4176 x_msg_count := l_msg_count;
4177 ELSE
4178 x_msg_count := l_msg_count;
4179 END IF;
4180
4181 x_return_status := FND_API.G_RET_STS_ERROR;
4182
4183 IF l_debug_mode = 'Y' THEN
4184 pa_debug.g_err_stage:='Invalid Arguments Passed';
4185 pa_debug.write('Delete_wp_option: ' || g_module_name,pa_debug.g_err_stage,5);
4186 pa_debug.reset_curr_function;
4187 END IF;
4188
4189 WHEN Others THEN
4190 ROLLBACK TO SAVEPOINT DELETE_WP_OPTION_SAVE;
4191 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4192 x_msg_count := 1;
4193 x_msg_data := SQLERRM;
4194 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_FIN_PLAN_PVT'
4195 ,p_procedure_name => 'Delete_wp_option');
4196
4197 IF l_debug_mode = 'Y' THEN
4198 pa_debug.g_err_stage:='Unexpected Error'||SQLERRM;
4199 pa_debug.write('Delete_wp_option: ' || g_module_name,pa_debug.g_err_stage,5);
4200 pa_debug.reset_curr_function;
4201 END IF;
4202 RAISE;
4203
4204 END Delete_wp_option;
4205
4206 /*=====================================================================
4207 * Procedure Name: DELETE_WP_BUDGET_VERSIONS
4208 * This procedure is added as part of FPM Development. Trackinb Bug - 3354518.
4209 * Purpose: This API deletes the budget_versions for all the
4210 * workplan structure version ids passed.
4211 * Parameters: 1) p_struct_elem_version_id_tbl IN SYSTEM.pa_num_tbl_type
4212 *=======================================================================*/
4213 PROCEDURE Delete_wp_budget_versions
4214 (p_struct_elem_version_id_tbl IN SYSTEM.pa_num_tbl_type
4215 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
4216 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
4217 ,x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
4218 IS
4219
4220 --Start of variables used for debugging
4221 l_msg_count NUMBER :=0;
4222 l_data VARCHAR2(2000);
4223 l_msg_data VARCHAR2(2000);
4224 l_error_msg_code VARCHAR2(30);
4225 l_msg_index_out NUMBER;
4226 l_return_status VARCHAR2(2000);
4227 l_debug_mode VARCHAR2(30);
4228 --End of variables used for debugging
4229
4230 l_sv_id_tbl_count NUMBER;
4231
4232 cursor c_budget_ver(c_structure_version_id pa_budget_versions.project_structure_version_id%TYPE) IS
4233 SELECT budget_version_id,record_version_number,project_id
4234 FROM pa_budget_versions
4235 WHERE project_structure_version_id = nvl(c_structure_version_id,-99)
4236 AND nvl(wp_version_flag,'N')='Y';
4237
4238 BEGIN
4239 SAVEPOINT PA_FP_PUB_DELETE_VER;
4240
4241 fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
4242 l_debug_mode := NVL(l_debug_mode, 'N');
4243 x_msg_count := 0;
4244 x_return_status := FND_API.G_RET_STS_SUCCESS;
4245 IF p_pa_debug_mode = 'Y' THEN
4246 PA_DEBUG.Set_Curr_Function( p_function => 'PA_FIN_PLAN_PVT.Delete_wp_bugdet_versions',
4247 p_debug_mode => l_debug_mode );
4248 END IF;
4249
4250 ------------------------------------------------------------------------
4251 -- Check if the PLSql Table p_struct_elem_version_id_tbl has no records.
4252 ------------------------------------------------------------------------
4253
4254 IF l_debug_mode = 'Y' THEN
4255 pa_debug.g_err_stage:='Checking for existence of data in PLsql table p_struct_elem_version_id_tbl';
4256 pa_debug.write('Delete_wp_bugdet_versions: ' || g_module_name,pa_debug.g_err_stage,3);
4257 END IF;
4258
4259 l_sv_id_tbl_count := p_struct_elem_version_id_tbl.COUNT;
4260
4261 IF l_sv_id_tbl_count = 0 THEN
4262 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
4263 END IF;
4264
4265
4266 ------------------------------------------------------------------
4267 -- Derive budget_version_id based on structure_version_id passed
4268 ------------------------------------------------------------------
4269
4270 --------------------------------------------------
4271 -- Loop through all the structure_verion_id passed
4272 --------------------------------------------------
4273 FOR i in p_struct_elem_version_id_tbl.first .. p_struct_elem_version_id_tbl.last LOOP --LoopA
4274
4275 IF l_debug_mode = 'Y' THEN
4276 pa_debug.g_err_stage:='Outer loop : '||i;
4277 pa_debug.write('Delete_wp_bugdet_versions: ' || g_module_name,pa_debug.g_err_stage,3);
4278 END IF;
4279
4280 -----------------------------------------------------------
4281 -- For each stucture_version_id fetch the budget_version_id
4282 -----------------------------------------------------------
4283 FOR c1 IN c_budget_ver(p_struct_elem_version_id_tbl(i)) LOOP --LoopB
4284
4285 ------------------------------------------------------------------
4286 -- If no budget versions exist for the structure_version_id passed
4287 -- iterate through the out loop LoopA
4288 ------------------------------------------------------------------
4289
4290 IF c_budget_ver%NOTFOUND THEN
4291 IF p_pa_debug_mode = 'Y' THEN
4292 pa_debug.g_err_stage:= 'Could not fetch budget_version_id !!!...';
4293 pa_debug.write(g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL5);
4294 END IF;
4295 EXIT; -- Come out of LoopB, Jump to next iteration of LoopA
4296 END IF;
4297
4298 ------------------------------------------------------------------
4299 -- Call Delete_Version for Version_id to delete all version
4300 -- data for the budget_version_id fetched.
4301 ------------------------------------------------------------------
4302 pa_fin_plan_pub.Delete_Version
4303 (p_budget_version_id => c1.budget_version_id,
4304 p_record_version_number => c1.record_version_number,
4305 p_context => PA_FP_CONSTANTS_PKG.G_CALLING_MODULE_WORKPLAN,
4306 p_project_id => c1.project_id,
4307 x_return_Status => l_return_Status,
4308 x_msg_count => l_msg_count,
4309 x_msg_data => l_msg_data);
4310
4311 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4312 RAISE Delete_Ver_Exc_PVT;
4313 END IF;
4314
4315 END LOOP; -- LoopB Closed
4316 END LOOP; -- LoopA Closed
4317 IF p_pa_debug_mode = 'Y' THEN
4318 pa_debug.reset_curr_function;
4319 END IF;
4320 EXCEPTION
4321
4322 WHEN PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc THEN
4323 l_msg_count := FND_MSG_PUB.count_msg;
4324 IF l_msg_count = 1 THEN
4325 PA_INTERFACE_UTILS_PUB.get_messages
4326 (p_encoded => FND_API.G_TRUE
4327 ,p_msg_index => 1
4328 ,p_msg_count => l_msg_count
4329 ,p_msg_data => l_msg_data
4330 ,p_data => l_data
4331 ,p_msg_index_out => l_msg_index_out);
4332 x_msg_data := l_data;
4333 x_msg_count := l_msg_count;
4334 ELSE
4335 x_msg_count := l_msg_count;
4336 END IF;
4337
4338 x_return_status := FND_API.G_RET_STS_ERROR;
4339
4340 IF l_debug_mode = 'Y' THEN
4341 pa_debug.g_err_stage:='Invalid Arguments Passed';
4342 pa_debug.write('Delete_wp_budget_version: ' || g_module_name,pa_debug.g_err_stage,5);
4343 pa_debug.reset_curr_function;
4344 END IF;
4345
4346 WHEN Delete_Ver_Exc_PVT THEN
4347 ROLLBACK TO SAVEPOINT PA_FP_PUB_DELETE_VER;
4348 l_msg_count := FND_MSG_PUB.count_msg;
4349 IF l_msg_count = 1 THEN
4350 PA_INTERFACE_UTILS_PUB.get_messages
4351 (p_encoded => FND_API.G_TRUE
4352 ,p_msg_index => 1
4353 ,p_msg_count => l_msg_count
4354 ,p_msg_data => l_msg_data
4355 ,p_data => l_data
4356 ,p_msg_index_out => l_msg_index_out);
4357 x_msg_data := l_data;
4358 x_msg_count := l_msg_count;
4359 ELSE
4360 x_msg_count := l_msg_count;
4361 END IF;
4362 x_return_status := FND_API.G_RET_STS_ERROR;
4363
4364 IF l_debug_mode = 'Y' THEN
4365 pa_debug.g_err_stage:='Delete_version_helper returned error';
4366 pa_debug.write('Delete_wp_options: ' || g_module_name,pa_debug.g_err_stage,5);
4367 pa_debug.reset_curr_function;
4368 END IF;
4369
4370 WHEN Others THEN
4371 ROLLBACK TO SAVEPOINT DELETE_WP_OPTION_SAVE;
4372 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4373 x_msg_count := 1;
4374 x_msg_data := SQLERRM;
4375 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_FIN_PLAN_PVT'
4376 ,p_procedure_name => 'Delete_wp_budget_versions');
4377
4378 IF l_debug_mode = 'Y' THEN
4379 pa_debug.g_err_stage:='Unexpected Error'||SQLERRM;
4380 pa_debug.write('Delete_wp_budget_versions: ' || g_module_name,pa_debug.g_err_stage,5);
4381 pa_debug.reset_curr_function;
4382 END IF;
4383 RAISE;
4384
4385 END Delete_wp_budget_versions;
4386
4387 PROCEDURE ADD_FIN_PLAN_LINES
4388 ( -- Bug Fix: 4569365. Removed MRC code.
4389 -- p_calling_context IN pa_mrc_finplan.g_calling_module%TYPE
4390 p_calling_context IN VARCHAR2
4391 ,p_fin_plan_version_id IN pa_budget_versions.budget_version_id%TYPE
4392 ,p_finplan_lines_tab IN pa_fin_plan_pvt.budget_lines_tab
4393 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
4394 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
4395 ,x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
4396 IS
4397
4398 l_msg_count NUMBER := 0;
4399 l_data VARCHAR2(2000);
4400 l_msg_data VARCHAR2(2000);
4401 l_error_msg_code VARCHAR2(30);
4402 l_msg_index_out NUMBER;
4403 l_return_status VARCHAR2(1);
4404 l_debug_mode VARCHAR2(30);
4405 l_resource_name VARCHAR2(30);
4406 l_err_code NUMBER:=0;
4407 l_debug_level3 CONSTANT NUMBER := 3;
4408 l_debug_level5 CONSTANT NUMBER := 5;
4409
4410 l_resource_assignment_tab SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
4411 l_delete_budget_lines_tab SYSTEM.pa_varchar2_1_tbl_type := SYSTEM.pa_varchar2_1_tbl_type();
4412 l_spread_amts_flag_tab SYSTEM.pa_varchar2_1_tbl_type := SYSTEM.pa_varchar2_1_tbl_type();
4413 l_line_start_date_tab SYSTEM.pa_date_tbl_type := SYSTEM.pa_date_tbl_type();
4414 l_line_end_date_tab SYSTEM.pa_date_tbl_type := SYSTEM.pa_date_tbl_type();
4415 i NUMBER;
4416 l_lines_count NUMBER; -- Bug 3639983
4417 l_module_name VARCHAR2(30) := 'pa.plsql.PA_FIN_PLAN_PVT';
4418 l_project_id pa_budget_versions.project_id%TYPE;
4419 l_fp_version_ids SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
4420 l_txn_currency_code_tab SYSTEM.pa_varchar2_15_tbl_type := SYSTEM.pa_varchar2_15_tbl_type();
4421 l_txn_currency_override_tab SYSTEM.pa_varchar2_15_tbl_type := SYSTEM.pa_varchar2_15_tbl_type();
4422 l_total_qty_tab SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
4423 l_total_raw_cost_tab SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
4424 l_total_burdened_cost_tab SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
4425 l_total_revenue_tab SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
4426 l_number_null_tab SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type(); --bug 3825873
4427
4428 -- bug 4221650: added the following
4429 l_ver_time_phased_code pa_proj_fp_options.cost_time_phased_code%TYPE;
4430
4431 CURSOR get_proj_id_csr IS
4432 SELECT project_id FROM pa_budget_versions
4433 WHERE budget_version_id = p_fin_plan_version_id ;
4434
4435 BEGIN
4436
4437 x_msg_count := 0;
4438 x_return_status := FND_API.G_RET_STS_SUCCESS;
4439 fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
4440 l_debug_mode := NVL(l_debug_mode, 'Y');
4441
4442 IF p_pa_debug_mode = 'Y' THEN
4443 PA_DEBUG.Set_Curr_Function( p_function => 'PA_FIN_PLAN_PVT.ADD_FIN_PLAN_LINES',
4444 p_debug_mode => l_debug_mode );
4445 END IF;
4446 l_lines_count := p_finplan_lines_tab.COUNT;
4447 IF l_debug_mode = 'Y' THEN
4448 pa_debug.g_err_stage:='Validating input parameter - plan lines count cannot be 0';
4449 pa_debug.write('ADD_FIN_PLAN_LINES: ' || g_module_name,pa_debug.g_err_stage,3);
4450 END IF;
4451
4452 -- Change of Code for Bug 3639983 Starts Here
4453 IF l_lines_count = 0 THEN
4454 IF l_debug_mode = 'Y' THEN
4455 pa_debug.g_err_stage:='No Lines to be added - Returning';
4456 pa_debug.write('ADD_FIN_PLAN_LINES: ' || g_module_name,pa_debug.g_err_stage,3);
4457 pa_debug.reset_curr_function;
4458 END IF;
4459 RETURN;
4460 END IF;
4461 -- Change of Code for Bug 3639983 Ends Here
4462
4463 IF l_debug_mode = 'Y' THEN
4464 pa_debug.g_err_stage:='Calling CREATE_FINPLAN_LINES';
4465 pa_debug.write('ADD_FIN_PLAN_LINES: ' || g_module_name,pa_debug.g_err_stage,3);
4466 END IF;
4467
4468 --Call to api PA_FIN_PLAN_PVT.CREATE_FINPLAN_LINES
4469
4470 IF ( nvl(p_finplan_lines_tab.last,0) > 0 ) THEN
4471
4472 PA_FIN_PLAN_PVT.CREATE_FINPLAN_LINES
4473 ( p_calling_context => p_calling_context
4474 ,p_fin_plan_version_id => p_fin_plan_version_id
4475 ,p_budget_lines_tab => p_finplan_lines_tab
4476 ,x_return_status => x_return_status
4477 ,x_msg_count => x_msg_count
4478 ,x_msg_data => x_msg_data );
4479
4480 IF l_debug_mode = 'Y' THEN
4481 pa_debug.g_err_stage:='Return Status After CREATE_FINPLAN_LINES :'||x_return_status;
4482 pa_debug.write('ADD_FIN_PLAN_LINES: ' || g_module_name,pa_debug.g_err_stage,3);
4483 END IF;
4484
4485 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
4486
4487 IF P_PA_DEBUG_MODE = 'Y' THEN
4488 pa_debug.g_err_stage:= 'Error Calling CREATE_FINPLAN_LINES';
4489 pa_debug.write('CREATE_DRAFT: '||g_module_name,pa_debug.g_err_stage,5);
4490 END IF;
4491
4492 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
4493
4494 END IF;
4495
4496 END IF;
4497
4498 -- Change of Code for Bug 3639983 Starts Here
4499 IF l_debug_mode = 'Y' THEN
4500 pa_debug.g_err_stage:='Extending lenght of all local empty table to l_lines_count';
4501 pa_debug.write('ADD_FIN_PLAN_LINES: ' || g_module_name,pa_debug.g_err_stage,3);
4502 END IF;
4503
4504 l_line_start_date_tab.extend(l_lines_count);
4505 l_line_end_date_tab.extend(l_lines_count);
4506 l_total_qty_tab.extend(l_lines_count);
4507 l_txn_currency_code_tab.extend(l_lines_count);
4508 l_total_raw_cost_tab.extend(l_lines_count);
4509 l_total_burdened_cost_tab.extend(l_lines_count);
4510 l_total_revenue_tab.extend(l_lines_count);
4511 l_resource_assignment_tab.extend(l_lines_count);
4512 l_delete_budget_lines_tab.extend(l_lines_count);
4513 l_spread_amts_flag_tab.extend(l_lines_count);
4514 l_number_null_tab.extend(l_lines_count); -- bug 3825873
4515
4516 -- Change of Code for Bug 3639983 Ends Here
4517 -- Change of Code for Bug 3732414 Starts Here
4518 SELECT start_date,
4519 end_date,
4520 quantity,
4521 txn_currency_code,
4522 txn_raw_cost,
4523 txn_burdened_cost,
4524 txn_revenue,
4525 resource_assignment_id,
4526 'N' delete_budget_lines,
4527 'N' spread_amouts,
4528 NULL
4529 BULK COLLECT INTO
4530 l_line_start_date_tab
4531 ,l_line_end_date_tab
4532 ,l_total_qty_tab
4533 ,l_txn_currency_code_tab
4534 ,l_total_raw_cost_tab
4535 ,l_total_burdened_cost_tab
4536 ,l_total_revenue_tab
4537 ,l_resource_assignment_tab
4538 ,l_delete_budget_lines_tab
4539 ,l_spread_amts_flag_tab
4540 ,l_number_null_tab -- bug 3825873
4541 FROM pa_fp_rollup_tmp;
4542
4543 -- Change of Code for Bug 3732414 Ends Here
4544
4545 IF l_debug_mode = 'Y' THEN
4546 pa_debug.g_err_stage:='Fetching project Id from get_proj_id_csr';
4547 pa_debug.write('ADD_FIN_PLAN_LINES: ' || g_module_name,pa_debug.g_err_stage,3);
4548 END IF;
4549
4550 OPEN get_proj_id_csr ;
4551 FETCH get_proj_id_csr into l_project_id ;
4552 CLOSE get_proj_id_csr;
4553
4554 -- Calling PA_FP_CALC_PLAN_PKG.calculate api
4555 IF l_debug_mode = 'Y' THEN
4556 pa_debug.g_err_stage:='Calling Calculate API';
4557 pa_debug.write('ADD_FIN_PLAN_LINES: ' || g_module_name,pa_debug.g_err_stage,3);
4558
4559 pa_debug.g_err_stage:='Calling Calculate API l_project_id'||l_project_id;
4560 pa_debug.write('ADD_FIN_PLAN_LINES: ' || g_module_name,pa_debug.g_err_stage,3);
4561
4562 pa_debug.g_err_stage:='Calling Calculate API p_fin_plan_version_id'||p_fin_plan_version_id;
4563 pa_debug.write('ADD_FIN_PLAN_LINES: ' || g_module_name,pa_debug.g_err_stage,3);
4564
4565 pa_debug.g_err_stage:='Calling Calculate API l_resource_assignment_tab'||l_resource_assignment_tab.COUNT;
4566 pa_debug.write('ADD_FIN_PLAN_LINES: ' || g_module_name,pa_debug.g_err_stage,3);
4567
4568 pa_debug.g_err_stage:='Calling Calculate API l_delete_budget_lines_tab'||l_delete_budget_lines_tab.COUNT;
4569 pa_debug.write('ADD_FIN_PLAN_LINES: ' || g_module_name,pa_debug.g_err_stage,3);
4570
4571 pa_debug.g_err_stage:='Calling Calculate API l_spread_amts_flag_tab'||l_spread_amts_flag_tab.COUNT;
4572 pa_debug.write('ADD_FIN_PLAN_LINES: ' || g_module_name,pa_debug.g_err_stage,3);
4573
4574 pa_debug.g_err_stage:='Calling Calculate API l_txn_currency_code_tab'||l_txn_currency_code_tab.COUNT;
4575 pa_debug.write('ADD_FIN_PLAN_LINES: ' || g_module_name,pa_debug.g_err_stage,3);
4576
4577 END IF;
4578
4579 -- bug 4221650: checking for the time phased code of the version to call
4580 -- calculate api in either 'RESOURCE_ASSIGNMENT' or in 'BUDGET_LINE' mode
4581
4582 l_ver_time_phased_code := PA_FIN_PLAN_UTILS.get_time_phased_code (p_fin_plan_version_id);
4583
4584 IF NOT l_ver_time_phased_code = 'N' THEN
4585
4586 IF l_debug_mode = 'Y' THEN
4587 pa_debug.g_err_stage:=' Calling Calculate in BUDGET_LINE mode';
4588 pa_debug.write('ADD_FIN_PLAN_LINES: ' || g_module_name,pa_debug.g_err_stage,3);
4589 END IF;
4590
4591 -- bug 3825873 17-JUL-2004 Corrected the input parameters
4592 /*Bug 4224464 Added the if condition to ditinguish the call to calculate API in AMG flow from other flows.
4593 We are passing the parameter p_calling_module as G_AMG_API if its a AMG flow. This parameter would be internally used by
4594 calculate API to skip the call to client extensions for AMG flows.
4595 Also added the parameter so as not to delete the PA_FP_SPREAD_CALC_TMP1 table in calcualte API . This parameter whould be
4596 passed in all the flows so as not to delete the PA_FP_SPREAD_CALC_TMP1 table.*/
4597 IF(p_calling_context = PA_FP_CONSTANTS_PKG.G_AMG_API)
4598 THEN
4599 PA_FP_CALC_PLAN_PKG.calculate
4600 ( p_project_id => l_project_id
4601 ,p_budget_version_id => p_fin_plan_version_id
4602 ,p_source_context => 'BUDGET_LINE'
4603 ,p_refresh_rates_flag => 'N'
4604 ,p_refresh_conv_rates_flag => 'N'
4605 ,p_conv_rates_required_flag => 'Y'
4606 ,p_spread_required_flag => 'Y'
4607 ,p_rollup_required_flag => 'Y'
4608 ,p_mass_adjust_flag => 'N'
4609 ,p_resource_assignment_tab => l_resource_assignment_tab
4610 ,p_delete_budget_lines_tab => l_delete_budget_lines_tab
4611 ,p_spread_amts_flag_tab => l_spread_amts_flag_tab
4612 ,p_txn_currency_code_tab => l_txn_currency_code_tab
4613 ,p_total_qty_tab => l_total_qty_tab
4614 ,p_addl_qty_tab => l_number_null_tab
4615 ,p_total_raw_cost_tab => l_total_raw_cost_tab
4616 ,p_addl_raw_cost_tab => l_number_null_tab
4617 ,p_total_burdened_cost_tab => l_total_burdened_cost_tab
4618 ,p_addl_burdened_cost_tab => l_number_null_tab
4619 ,p_total_revenue_tab => l_total_revenue_tab
4620 ,p_addl_revenue_tab => l_number_null_tab
4621 ,p_line_start_date_tab => l_line_start_date_tab
4622 ,p_line_end_date_tab => l_line_end_date_tab
4623 ,p_raw_cost_rate_tab => l_number_null_tab
4624 ,p_rw_cost_rate_override_tab => l_number_null_tab
4625 ,p_b_cost_rate_tab => l_number_null_tab
4626 ,p_b_cost_rate_override_tab => l_number_null_tab
4627 ,p_bill_rate_tab => l_number_null_tab
4628 ,p_bill_rate_override_tab => l_number_null_tab
4629 ,p_del_spread_calc_tmp1_flg => 'N' /* Bug: 4309290.Added the parameter to identify if
4630 PA_FP_SPREAD_CALC_TMP1 is to be deleted or not. Frm AMG flow
4631 we will pass N and for other calls to calculate api it would
4632 be yes */
4633 ,p_calling_module => PA_FP_CONSTANTS_PKG.G_AMG_API
4634 ,x_return_status => x_return_status
4635 ,x_msg_count => x_msg_count
4636 ,x_msg_data => x_msg_data);
4637 ELSE
4638 PA_FP_CALC_PLAN_PKG.calculate
4639 ( p_project_id => l_project_id
4640 ,p_budget_version_id => p_fin_plan_version_id
4641 ,p_source_context => 'BUDGET_LINE'
4642 ,p_refresh_rates_flag => 'N'
4643 ,p_refresh_conv_rates_flag => 'N'
4644 ,p_conv_rates_required_flag => 'Y'
4645 ,p_spread_required_flag => 'Y'
4646 ,p_rollup_required_flag => 'Y'
4647 ,p_mass_adjust_flag => 'N'
4648 ,p_resource_assignment_tab => l_resource_assignment_tab
4649 ,p_delete_budget_lines_tab => l_delete_budget_lines_tab
4650 ,p_spread_amts_flag_tab => l_spread_amts_flag_tab
4651 ,p_txn_currency_code_tab => l_txn_currency_code_tab
4652 ,p_total_qty_tab => l_total_qty_tab
4653 ,p_addl_qty_tab => l_number_null_tab
4654 ,p_total_raw_cost_tab => l_total_raw_cost_tab
4655 ,p_addl_raw_cost_tab => l_number_null_tab
4656 ,p_total_burdened_cost_tab => l_total_burdened_cost_tab
4657 ,p_addl_burdened_cost_tab => l_number_null_tab
4658 ,p_total_revenue_tab => l_total_revenue_tab
4659 ,p_addl_revenue_tab => l_number_null_tab
4660 ,p_line_start_date_tab => l_line_start_date_tab
4661 ,p_line_end_date_tab => l_line_end_date_tab
4662 ,p_raw_cost_rate_tab => l_number_null_tab
4663 ,p_rw_cost_rate_override_tab => l_number_null_tab
4664 ,p_b_cost_rate_tab => l_number_null_tab
4665 ,p_b_cost_rate_override_tab => l_number_null_tab
4666 ,p_bill_rate_tab => l_number_null_tab
4667 ,p_bill_rate_override_tab => l_number_null_tab
4668 ,p_del_spread_calc_tmp1_flg => 'N'
4669 ,x_return_status => x_return_status
4670 ,x_msg_count => x_msg_count
4671 ,x_msg_data => x_msg_data);
4672 END IF;
4673 ELSE
4674 -- bug 4221650:
4675 IF l_debug_mode = 'Y' THEN
4676 pa_debug.g_err_stage:=' Calling Calculate in RESOURCE_ASSIGNMENT mode';
4677 pa_debug.write('ADD_FIN_PLAN_LINES: ' || g_module_name,pa_debug.g_err_stage,3);
4678 END IF;
4679
4680 /*Bug 4224464 Added the if condition to ditinguish the call to calculate API in AMG flow from other flows.
4681 We are passing the parameter p_calling_module as G_AMG_API if its a AMG flow. This parameter would be internally used by
4682 calculate API to skip the call to client extensions for AMG flows.
4683 Also added the parameter so as not to delete the PA_FP_SPREAD_CALC_TMP1 table in calcualte API . This parameter whould be
4684 passed in all the flows so as not to delete the PA_FP_SPREAD_CALC_TMP1 table.*/
4685 IF(p_calling_context = PA_FP_CONSTANTS_PKG.G_AMG_API)
4686 THEN
4687 PA_FP_CALC_PLAN_PKG.calculate
4688 ( p_project_id => l_project_id
4689 ,p_budget_version_id => p_fin_plan_version_id
4690 ,p_source_context => 'RESOURCE_ASSIGNMENT'
4691 ,p_resource_assignment_tab => l_resource_assignment_tab
4692 ,p_spread_amts_flag_tab => l_spread_amts_flag_tab
4693 ,p_txn_currency_code_tab => l_txn_currency_code_tab
4694 ,p_total_qty_tab => l_total_qty_tab
4695 ,p_total_raw_cost_tab => l_total_raw_cost_tab
4696 ,p_total_burdened_cost_tab => l_total_burdened_cost_tab
4697 ,p_total_revenue_tab => l_total_revenue_tab
4698 ,p_line_start_date_tab => l_line_start_date_tab
4699 ,p_line_end_date_tab => l_line_end_date_tab
4700 ,p_calling_module => PA_FP_CONSTANTS_PKG.G_AMG_API
4701 ,p_del_spread_calc_tmp1_flg => 'N'
4702 ,x_return_status => x_return_status
4703 ,x_msg_count => x_msg_count
4704 ,x_msg_data => x_msg_data);
4705 ELSE
4706 PA_FP_CALC_PLAN_PKG.calculate
4707 ( p_project_id => l_project_id
4708 ,p_budget_version_id => p_fin_plan_version_id
4709 ,p_source_context => 'RESOURCE_ASSIGNMENT'
4710 ,p_resource_assignment_tab => l_resource_assignment_tab
4711 ,p_spread_amts_flag_tab => l_spread_amts_flag_tab
4712 ,p_txn_currency_code_tab => l_txn_currency_code_tab
4713 ,p_total_qty_tab => l_total_qty_tab
4714 ,p_total_raw_cost_tab => l_total_raw_cost_tab
4715 ,p_total_burdened_cost_tab => l_total_burdened_cost_tab
4716 ,p_total_revenue_tab => l_total_revenue_tab
4717 ,p_line_start_date_tab => l_line_start_date_tab
4718 ,p_line_end_date_tab => l_line_end_date_tab
4719 ,p_del_spread_calc_tmp1_flg => 'N'
4720 ,x_return_status => x_return_status
4721 ,x_msg_count => x_msg_count
4722 ,x_msg_data => x_msg_data);
4723 END IF; -- IF(p_calling_context = PA_FP_CONSTANTS_PKG.G_AMG_API)
4724
4725 END IF;
4726
4727 IF l_debug_mode = 'Y' THEN
4728 pa_debug.g_err_stage:='Return Status After CALCULATE :'||x_return_status;
4729 pa_debug.write('ADD_FIN_PLAN_LINES: ' || g_module_name,pa_debug.g_err_stage,3);
4730 END IF;
4731
4732 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4733
4734 IF l_debug_mode = 'Y' THEN
4735 pa_debug.g_err_stage:='Called API PA_FP_CALC_PLAN_PKG.calculate returned error';
4736 pa_debug.write(l_module_name,pa_debug.g_err_stage, l_debug_level3);
4737 END IF;
4738
4739 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
4740
4741 END IF;
4742 IF l_debug_mode = 'Y' THEN
4743 pa_debug.g_err_stage:='Exiting ADD FIN PLAN LINES x_return_status: '||x_return_status;
4744 pa_debug.write('ADD_FIN_PLAN_LINES: ' || g_module_name,pa_debug.g_err_stage,3);
4745 pa_debug.reset_curr_function;
4746 END IF;
4747 EXCEPTION
4748
4749 WHEN PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc THEN
4750
4751 x_return_status := FND_API.G_RET_STS_ERROR;
4752 l_msg_count := FND_MSG_PUB.count_msg;
4753
4754 IF l_msg_count = 1 THEN
4755 PA_INTERFACE_UTILS_PUB.get_messages
4756 (p_encoded => FND_API.G_TRUE
4757 ,p_msg_index => 1
4758 ,p_msg_count => l_msg_count
4759 ,p_msg_data => l_msg_data
4760 ,p_data => l_data
4761 ,p_msg_index_out => l_msg_index_out);
4762 x_msg_data := l_data;
4763 x_msg_count := l_msg_count;
4764 ELSE
4765 x_msg_count := l_msg_count;
4766 END IF;
4767
4768 pa_debug.g_err_stage:= 'Invalid Arguments Passed';
4769
4770 IF P_PA_DEBUG_MODE = 'Y' THEN
4771 pa_debug.write('ADD_FIN_PLAN_LINES: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL5);
4772 pa_debug.reset_curr_function;
4773 END IF;
4774 RETURN;
4775
4776 WHEN others THEN
4777
4778 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4779 x_msg_count := 1;
4780 x_msg_data := SQLERRM;
4781
4782 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'pa_fin_plan_pvt'
4783 ,p_procedure_name => 'ADD_FIN_PLAN_LINES');
4784 pa_debug.g_err_stage:= 'Unexpected Error'||SQLERRM;
4785
4786 IF P_PA_DEBUG_MODE = 'Y' THEN
4787 pa_debug.write('ADD_FIN_PLAN_LINES: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL5);
4788 pa_debug.reset_curr_function;
4789 END IF;
4790 RAISE;
4791
4792 END ADD_FIN_PLAN_LINES;
4793
4794 END pa_fin_plan_pvt;