[Home] [Help]
PACKAGE BODY: APPS.PA_FIN_PLAN_PVT
Source
1 PACKAGE BODY pa_fin_plan_pvt AS
2 /* $Header: PAFPPVTB.pls 120.12.12010000.2 2008/08/19 21:32:32 jngeorge 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
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
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 pa_debug.g_err_stage:='Entering of validation for resource list';
2270 IF P_PA_DEBUG_MODE = 'Y' THEN
2271 pa_debug.write('CREATE_DRAFT: ' || g_module_name,pa_debug.g_err_stage,3);
2272 END IF;
2273
2274 /* End of validation for resource list */
2275
2276 /* Get draft version */
2277
2278 pa_fin_plan_utils.Get_Curr_Working_Version_Info(
2279 p_project_id => p_project_id
2280 ,p_fin_plan_type_id => p_fin_plan_type_id
2281 ,p_version_type => p_version_type
2282 ,x_fp_options_id => l_fp_options_id
2283 ,x_fin_plan_version_id => l_curr_work_version_id
2284 ,x_return_status => x_return_status
2285 ,x_msg_count => x_msg_count
2286 ,x_msg_data => x_msg_data );
2287
2288 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2289 Raise PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
2290 END IF;
2291
2292 pa_debug.g_err_stage:='Current working version id -> ' || l_curr_work_version_id;
2293 IF P_PA_DEBUG_MODE = 'Y' THEN
2294 pa_debug.write('CREATE_DRAFT: ' || g_module_name,pa_debug.g_err_stage,3);
2295 END IF;
2296
2297 IF (l_curr_work_version_id is not null) THEN /* Bug 2672654 */
2298 pa_fin_plan_pvt.Get_Included_Ci(
2299 p_from_bv_id => l_curr_work_version_id
2300 ,p_to_bv_id => NULL
2301 ,p_impact_status => NULL
2302 ,x_ci_rec_tab => l_ci_rec_tab /* Bug 2672654 */
2303 ,x_return_status => x_return_status
2304 ,x_msg_count => x_msg_count
2305 ,x_msg_data => x_msg_data );
2306
2307 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2308 pa_debug.g_err_stage:= 'Could not obtain the CI information for the version';
2309 IF P_PA_DEBUG_MODE = 'Y' THEN
2310 pa_debug.write('CREATE_DRAFT: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL5);
2311 END IF;
2312 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
2313 ELSE
2314 pa_debug.g_err_stage:= 'obtained the CI information for the version';
2315 IF P_PA_DEBUG_MODE = 'Y' THEN
2316 pa_debug.write('CREATE_DRAFT: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
2317 END IF;
2318 END IF;
2319
2320 /* Bug 2688610 - should call delete_version rather than delete_version_helper.
2321 pa_fin_plan_pub.Delete_Version_Helper
2322 ( p_budget_version_id => l_curr_work_version_id
2323 ,x_return_status => x_return_status
2324 ,x_msg_count => x_msg_count
2325 ,x_msg_data => x_msg_data );
2326 */
2327
2328 -- Do not delete the version if called from create draft. Changes due to AMG
2329
2330 IF nvl(p_replace_current_working_flag,'N')= 'Y' THEN
2331
2332 l_record_version_number := pa_fin_plan_utils.Retrieve_Record_Version_Number
2333 (p_budget_version_id => l_curr_work_version_id);
2334
2335 --Try to lock the version before deleting the version. This is required so as not to delete
2336 --versions locked by other users. AMG UT2
2337 l_user_id := FND_GLOBAL.User_id;
2338 pa_fin_plan_pvt.lock_unlock_version
2339 (p_budget_version_id => l_curr_work_version_id,
2340 p_record_version_number => l_record_version_number,
2341 p_action => 'L',
2342 p_user_id => l_user_id,
2343 p_person_id => NULL,
2344 x_return_status => x_return_status,
2345 x_msg_count => x_msg_count,
2346 x_msg_data => x_msg_data) ;
2347
2348 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
2349
2350 IF l_debug_mode = 'Y' THEN
2351 pa_debug.g_err_stage := 'Error in lock unlock version - cannot delete working version';
2352 pa_debug.write('CREATE_DRAFT: ' || g_module_name,pa_debug.g_err_stage,5);
2353 END IF;
2354
2355 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
2356
2357 END IF;
2358
2359 l_record_version_number := pa_fin_plan_utils.Retrieve_Record_Version_Number
2360 (p_budget_version_id => l_curr_work_version_id);
2361
2362 pa_fin_plan_pub.delete_version
2363 ( p_project_id => p_project_id
2364 ,p_budget_version_id => l_curr_work_version_id
2365 ,p_record_version_number => l_record_version_number
2366 ,x_return_status => x_return_status
2367 ,x_msg_count => x_msg_count
2368 ,x_msg_data => x_msg_data
2369 );
2370
2371 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2372 pa_debug.g_err_stage:= 'Could not delete the current working version';
2373 IF P_PA_DEBUG_MODE = 'Y' THEN
2374 pa_debug.write('CREATE_DRAFT: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL5);
2375 END IF;
2376 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
2377 ELSE
2378 pa_debug.g_err_stage:= 'Deleted the current working version';
2379 IF P_PA_DEBUG_MODE = 'Y' THEN
2380 pa_debug.write('CREATE_DRAFT: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
2381 END IF;
2382 END IF;
2383
2384 END IF;--IF l_calling_context <> PA_FP_CONSTANTS_PKG.G_CREATE_DRAFT THEN
2385
2386 END IF; --l_curr_work_version_id is not null
2387 ELSE
2388 pa_debug.g_err_stage:= 'p_ci_id is not null - control item version';
2389 IF P_PA_DEBUG_MODE = 'Y' THEN
2390 pa_debug.write('CREATE_DRAFT: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
2391 END IF;
2392 END IF; -- p_ci_id is NULL. Bug 2672654
2393
2394 --Get the preference code
2395 IF(p_version_type = PA_FP_CONSTANTS_PKG.G_ELEMENT_TYPE_COST) THEN
2396 l_plan_pref_code := PA_FP_CONSTANTS_PKG.G_PREF_COST_ONLY;
2397 ELSIF(p_version_type = PA_FP_CONSTANTS_PKG.G_ELEMENT_TYPE_REVENUE) THEN
2398 l_plan_pref_code := PA_FP_CONSTANTS_PKG.G_PREF_REVENUE_ONLY;
2399 ELSIF(p_version_type = PA_FP_CONSTANTS_PKG.G_ELEMENT_TYPE_ALL) THEN
2400 l_plan_pref_code := PA_FP_CONSTANTS_PKG.G_PREF_COST_AND_REV_SAME;
2401 END IF;
2402
2403 pa_debug.g_err_stage:= 'Preference code is -> ' || l_plan_pref_code;
2404 IF P_PA_DEBUG_MODE = 'Y' THEN
2405 pa_debug.write('CREATE_DRAFT: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
2406 END IF;
2407
2408 --Get the amount set id.
2409 pa_fin_plan_utils.GET_OR_CREATE_AMOUNT_SET_ID
2410 (
2411 p_raw_cost_flag => p_raw_cost_flag
2412 ,p_burdened_cost_flag => p_burdened_cost_flag
2413 ,p_revenue_flag => p_revenue_flag
2414 ,p_cost_qty_flag => p_cost_qty_flag
2415 ,p_revenue_qty_flag => p_revenue_qty_flag
2416 ,p_all_qty_flag => p_all_qty_flag
2417 ,p_plan_pref_code => l_plan_pref_code
2418 /* Changes for FP.M, Tracking Bug No - 3354518
2419 Passing three new arguments p_bill_rate_flag,
2420 p_cost_rate_flag, p_burden_rate below for
2421 new columns in pa_fin_plan_amount_sets and changes done in
2422 called API */
2423 ,p_bill_rate_flag => 'Y'
2424 ,p_cost_rate_flag => 'Y'
2425 ,p_burden_rate_flag => 'Y'
2426 ,x_cost_amount_set_id => l_cost_amount_set_id
2427 ,x_revenue_amount_set_id => l_rev_amount_set_id
2428 ,x_all_amount_set_id => l_all_amount_set_id
2429 ,x_message_count => x_msg_count
2430 ,x_return_status => x_return_status
2431 ,x_message_data => x_msg_data
2432 );
2433
2434 IF(p_version_type = PA_FP_CONSTANTS_PKG.G_ELEMENT_TYPE_COST) THEN
2435 l_amount_set_id := l_cost_amount_set_id;
2436 ELSIF(p_version_type = PA_FP_CONSTANTS_PKG.G_ELEMENT_TYPE_REVENUE) THEN
2437 l_amount_set_id := l_rev_amount_set_id;
2438 ELSIF(p_version_type = PA_FP_CONSTANTS_PKG.G_ELEMENT_TYPE_ALL) THEN
2439 l_amount_set_id := l_all_amount_set_id;
2440 END IF;
2441
2442 pa_debug.g_err_stage:= 'amount set id is -> ' || l_amount_set_id;
2443 IF P_PA_DEBUG_MODE = 'Y' THEN
2444 pa_debug.write('CREATE_DRAFT: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
2445 END IF;
2446
2447 l_created_version_id := NULL;
2448 /* Create the plan version */
2449 pa_fin_plan_pub.Create_Version (
2450 p_project_id => p_project_id
2451 ,p_fin_plan_type_id => p_fin_plan_type_id
2452 ,p_element_type => p_version_type
2453 ,p_version_name => p_version_name
2454 ,p_description => p_description
2455 -- change for CI impact start. Bug # 2634900.
2456 ,p_ci_id => p_ci_id --NULL
2457 ,p_est_proj_raw_cost => p_est_proj_raw_cost --NULL
2458 ,p_est_proj_bd_cost => p_est_proj_bd_cost --NULL
2459 ,p_est_proj_revenue => p_est_proj_revenue --NULL
2460 ,p_est_qty => p_est_qty --NULL
2461 ,p_est_equip_qty => p_est_equip_qty --NULL
2462 ,p_impacted_task_id => p_impacted_task_id --NULL
2463 ,p_agreement_id => p_agreement_id --NULL
2464 -- change for CI impact end. Bug # 2634900.
2465 ,p_calling_context => l_calling_context
2466 ,p_plan_in_multi_curr_flag => p_plan_in_mc_flag
2467 ,p_fin_plan_level_code => p_fin_plan_level_code
2468 ,p_resource_list_id => l_resource_list_id
2469 ,p_time_phased_code => p_time_phased_code
2470 ,p_amount_set_id => l_amount_set_id
2471 ,px_budget_version_id => l_created_version_id
2472 ,x_proj_fp_option_id => l_fp_options_id
2473 ,x_return_status => x_return_status
2474 ,x_msg_count => x_msg_count
2475 ,x_msg_data => x_msg_data );
2476
2477 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2478 pa_debug.g_err_stage:= 'Error in calling Create_Version';
2479 IF P_PA_DEBUG_MODE = 'Y' THEN
2480 pa_debug.write('CREATE_DRAFT: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL5);
2481 END IF;
2482 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
2483 END IF;
2484
2485 x_budget_version_id := l_created_version_id;
2486
2487
2488 pa_debug.g_err_stage:= 'Created budget version id is : '||l_created_version_id;
2489 IF P_PA_DEBUG_MODE = 'Y' THEN
2490 pa_debug.write('CREATE_DRAFT: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
2491 END IF;
2492
2493 -- If the created version is a NON-CI version "AND" --- added this condition for bug 2881681
2494 -- If either of the replace current working or Create working version flags is Y then make
2495 -- the newly created version the current working version. Changes due to finplan in AMG
2496
2497 IF (p_ci_id IS NULL) AND
2498 (p_create_new_curr_working_flag = 'Y' OR
2499 p_replace_current_working_flag = 'Y') THEN
2500
2501 -- Get the details of the current working version so as to pass it to the
2502 -- Set Current Working API.
2503
2504 pa_fin_plan_utils.Get_Curr_Working_Version_Info(
2505 p_project_id => p_project_id
2506 ,p_fin_plan_type_id => p_fin_plan_type_id
2507 ,p_version_type => p_version_type
2508 ,x_fp_options_id => l_proj_fp_options_id
2509 ,x_fin_plan_version_id => l_CW_version_id
2510 ,x_return_status => x_return_status
2511 ,x_msg_count => x_msg_count
2512 ,x_msg_data => x_msg_data );
2513
2514 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2515 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
2516 END IF;
2517
2518 -- Further processing is required only if the newly created version is not the current working verion
2519
2520 IF l_created_version_id <> l_CW_version_id THEN
2521
2522 --Get the record version number of the current working version
2523 l_CW_record_version_number := pa_fin_plan_utils.Retrieve_Record_Version_Number(l_CW_version_id);
2524
2525 --Get the record version number of the newly created version
2526 l_created_ver_rec_ver_num := pa_fin_plan_utils.Retrieve_Record_Version_Number(l_created_version_id);
2527 l_user_id := FND_GLOBAL.User_id;
2528 pa_fin_plan_pvt.lock_unlock_version
2529 (p_budget_version_id => l_CW_version_id,
2530 p_record_version_number => l_CW_record_version_number,
2531 p_action => 'L',
2532 p_user_id => l_user_id,
2533 p_person_id => NULL,
2534 x_return_status => x_return_status,
2535 x_msg_count => x_msg_count,
2536 x_msg_data => x_msg_data) ;
2537
2538 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
2539
2540 IF l_debug_mode = 'Y' THEN
2541 pa_debug.g_err_stage := 'Error executing lock unlock version';
2542 pa_debug.write('CREATE_DRAFT: ' || g_module_name,pa_debug.g_err_stage,3);
2543 END IF;
2544
2545 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
2546
2547 END IF;
2548
2549 IF l_debug_mode = 'Y' THEN
2550 pa_debug.g_err_stage := 'About to call set current working version';
2551 pa_debug.write('CREATE_DRAFT: ' || g_module_name,pa_debug.g_err_stage,3);
2552 END IF;
2553
2554 -- Getting the rec ver number again as it will be incremented by the api lock_unlock_version
2555 l_CW_record_version_number := pa_fin_plan_utils.Retrieve_Record_Version_Number(l_CW_version_id);
2556
2557 pa_fin_plan_pub.Set_Current_Working
2558 (p_project_id => p_project_id,
2559 p_budget_version_id => l_created_version_id,
2560 p_record_version_number => l_created_ver_rec_ver_num,
2561 p_orig_budget_version_id => l_CW_version_id,
2562 p_orig_record_version_number => l_CW_record_version_number,
2563 x_return_status => x_return_status,
2564 x_msg_count => x_msg_count,
2565 x_msg_data => x_msg_data);
2566
2567 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2568 IF P_PA_DEBUG_MODE = 'Y' THEN
2569 pa_debug.g_err_stage:= 'Error executing Set_Current_Working ';
2570 pa_debug.write('CREATE_DRAFT: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL5);
2571 END IF;
2572 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
2573 END IF;
2574
2575 END IF; -- IF l_created_version_id <> l_CW_version_id THEN
2576
2577 END IF; --IF (p_create_new_curr_working_flag = 'Y' OR
2578
2579
2580
2581
2582 --The above call to the create version api has not created RAs,elements.
2583 --need to update the options and version table.
2584
2585 --start of changes for options table.
2586 /*
2587 need to update the following in fp_options.
2588 conversion attributes.
2589 */
2590 /*
2591 Bug 2670747 - The MC attributes need to be updated only if MC flag is Y
2592 */
2593 IF (p_plan_in_mc_flag = 'Y') THEN
2594 update pa_proj_fp_options
2595 set projfunc_cost_rate_type = p_projfunc_cost_rate_type
2596 ,projfunc_cost_rate_date_type = p_projfunc_cost_rate_date_type
2597 ,projfunc_cost_rate_date = p_projfunc_cost_rate_date
2598 ,projfunc_rev_rate_type = p_projfunc_rev_rate_type
2599 ,projfunc_rev_rate_date_type = p_projfunc_rev_rate_date_type
2600 ,projfunc_rev_rate_date = p_projfunc_rev_rate_date
2601 ,project_cost_rate_type = p_project_cost_rate_type
2602 ,project_cost_rate_date_type = p_project_cost_rate_date_type
2603 ,project_cost_rate_date = p_project_cost_rate_date
2604 ,project_rev_rate_type = p_project_rev_rate_type
2605 ,project_rev_rate_date_type = p_project_rev_rate_date_type
2606 ,project_rev_rate_date = p_project_rev_rate_date
2607 where proj_fp_options_id = l_fp_options_id;
2608 END IF; --End of Bug fix 2670747
2609
2610 --End of changes corresponding to options table.
2611
2612 --Start of changes for budget versions table.
2613 /*
2614 need to update the following in budget_versions
2615 change reason code,
2616 product code, budget reference.
2617 */
2618 update pa_budget_versions
2619 set change_reason_code = p_change_reason_code,
2620 pm_product_code = p_pm_product_code,
2621 pm_budget_reference = p_pm_budget_reference,
2622 attribute_category = p_attribute_category,
2623 attribute1 = p_attribute1,
2624 attribute2 = p_attribute2,
2625 attribute3 = p_attribute3,
2626 attribute4 = p_attribute4,
2627 attribute5 = p_attribute5,
2628 attribute6 = p_attribute6,
2629 attribute7 = p_attribute7,
2630 attribute8 = p_attribute8,
2631 attribute9 = p_attribute9,
2632 attribute10 = p_attribute10,
2633 attribute11 = p_attribute11,
2634 attribute12 = p_attribute12,
2635 attribute13 = p_attribute13,
2636 attribute14 = p_attribute14,
2637 attribute15 = p_attribute15
2638 where budget_version_id = l_created_version_id;
2639
2640 --End of changes corresponding to budget versions table.
2641
2642 /* Bug# 2676352 - For automatic baselined ci versions, budget_lines_tab is not passed and we should
2643 be creating resource assignments and fp elements based on defaults. It is to be noted that for
2644 autobaseline case, resource list id is always none and hence calling insert_defaults should be fine */
2645
2646 IF (l_calling_context = PA_FP_CONSTANTS_PKG.G_AUTOMATIC_BASELINE AND p_ci_id IS NOT NULL) THEN
2647
2648 IF ( p_impacted_task_id IS NULL OR
2649 p_fin_plan_level_code = PA_FP_CONSTANTS_PKG.G_BUDGET_ENTRY_LEVEL_PROJECT ) THEN
2650
2651 -- <Patchset M: B and F impact changes : AMG:>-- Bug # 3507156
2652 -- References to PA_FP_ELEMENTS table have been commented out (FP M)
2653 -- Comment START.
2654 /*
2655 -- Create fp elements and resource assignments for the budget version and the impacted task id
2656
2657 pa_debug.g_err_stage:='Calling pa_fp_elements_pub.insert_default...';
2658 IF P_PA_DEBUG_MODE = 'Y' THEN
2659 pa_debug.write('CREATE_DRAFT: ' || g_module_name,pa_debug.g_err_stage,3);
2660 END IF;
2661
2662 Pa_Fp_Elements_Pub.Insert_Default (
2663 p_proj_fp_options_id => l_fp_options_id
2664 ,p_element_type => p_version_type
2665 ,p_planning_level => p_fin_plan_level_code
2666 ,p_resource_list_id => l_resource_list_id
2667 -- Bug 2920954 Start of parameters added for post FP-K oneoff patch
2668 ,p_select_res_auto_flag => NULL
2669 ,p_res_planning_level => NULL
2670 --Bug 2920954 End of parameters added for post FP-K oneoff patch
2671 ,x_return_status => x_return_status
2672 ,x_msg_count => x_msg_count
2673 ,x_msg_data => x_msg_data);
2674
2675 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2676 Raise PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
2677 END IF;
2678
2679
2680 pa_debug.g_err_stage:='Calling pa_fp_elements_pub.create_enterable_resources...';
2681 IF P_PA_DEBUG_MODE = 'Y' THEN
2682 pa_debug.write('CREATE_DRAFT: ' || g_module_name,pa_debug.g_err_stage,3);
2683 END IF;
2684
2685 Pa_Fp_Elements_Pub.Create_Enterable_Resources (
2686 p_plan_version_id => l_created_version_id
2687 ,x_return_status => x_return_status
2688 ,x_msg_count => x_msg_count
2689 ,x_msg_data => x_msg_data);
2690
2691 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2692 Raise PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
2693 END IF;
2694
2695 */
2696 --<Patchset M: B and F impact changes : AMG:>-- Bug # 3507156
2697 -- Comment END
2698 -- Added a call to pa_fp_planning_transaction_pub.create_default_task_plan_txns
2699
2700 pa_debug.g_err_stage:='Calling pa_fp_planning_transaction_pub.create_default_task_plan_txns...';
2701
2702 IF P_PA_DEBUG_MODE = 'Y' THEN
2703 pa_debug.write('CREATE_DRAFT: ' || g_module_name,pa_debug.g_err_stage,3);
2704 END IF;
2705 pa_fp_planning_transaction_pub.create_default_task_plan_txns(
2706 P_budget_version_id => l_created_version_id
2707 ,P_version_plan_level_code => p_fin_plan_level_code
2708 ,X_return_status => x_return_status
2709 ,X_msg_count => x_msg_count
2710 ,X_msg_data => x_msg_data);
2711
2712 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2713 Raise PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
2714 END IF;
2715
2716 ELSE
2717
2718 -- Fetching top task id and parent task id of impacted task id
2719
2720 pa_debug.g_err_stage:= 'Fetching impacted task details';
2721 IF P_PA_DEBUG_MODE = 'Y' THEN
2722 pa_debug.write('CREATE_DRAFT: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
2723 END IF;
2724
2725 OPEN impacted_task_cur(p_impacted_task_id);
2726 FETCH impacted_task_cur INTO impacted_task_rec;
2727 CLOSE impacted_task_cur;
2728
2729 --Insert a new record into pa_resoruce_assignments
2730
2731
2732 DECLARE
2733 l_dummy_res_list_id PA_RESOURCE_LISTS_ALL_BG.resource_list_id%TYPE;
2734 l_dummy_row_id ROWID;
2735 l_dummy_ra_id PA_RESOURCE_ASSIGNMENTS.resource_assignment_id%TYPE;
2736 BEGIN
2737
2738 PA_FIN_PLAN_UTILS.Get_Uncat_Resource_List_Info
2739 (x_resource_list_id => l_dummy_res_list_id
2740 ,x_resource_list_member_id => l_uncat_rlmid
2741 ,x_track_as_labor_flag => l_track_as_labor_flag
2742 ,x_unit_of_measure => l_unit_of_measure
2743 ,x_return_status => x_return_status
2744 ,x_msg_count => x_msg_count
2745 ,x_msg_data => x_msg_data);
2746
2747 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2748 pa_debug.g_err_stage := 'Error while fetching uncat res list id info ...';
2749 IF P_PA_DEBUG_MODE = 'Y' THEN
2750 pa_debug.write('CREATE_DRAFT: ' || g_module_name,pa_debug.g_err_stage,5);
2751 END IF;
2752 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
2753 END IF;
2754
2755 l_task_elem_version_id_tbl.extend();
2756 l_task_elem_version_id_tbl(1) := impacted_task_rec.element_version_id;
2757 l_rlm_id_tbl.extend();
2758 l_rlm_id_tbl(1) := l_uncat_rlmid;
2759 Pa_Fp_Planning_Transaction_Pub.Add_Planning_Transactions(
2760 p_context => 'BUDGET',--It will always be budget as CIs can be
2761 --created only for Budgets
2762 p_project_id => p_project_id,
2763 p_budget_version_id => l_created_version_id,
2764 p_task_elem_version_id_tbl => l_task_elem_version_id_tbl,
2765 p_resource_list_member_id_tbl => l_rlm_id_tbl,
2766 x_return_status => x_return_status,
2767 x_msg_count => x_msg_count,
2768 x_msg_data => x_msg_data);
2769
2770 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2771 pa_debug.g_err_stage:= 'Exception while inserting a row into pa_resource_assignments;';
2772 IF P_PA_DEBUG_MODE = 'Y' THEN
2773 pa_debug.write('CREATE_DRAFT: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL5);
2774 END IF;
2775 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
2776 END IF;
2777
2778 END;
2779
2780 END IF;
2781
2782 --call PA_FIN_PLAN_PVT.CRETAE_FINPLAN_LINES. This API will create
2783 --resource assignments, elements and budget lines for the version.
2784 /* Bug# 2672654 - create_fin_plan_lines API needs to be called only if p_budget_lines_tab is not null */
2785
2786 ELSIF ( nvl(p_budget_lines_tab.last,0) > 0 ) THEN
2787
2788 --Bug # 3507156-<Patchset M: B and F impact changes : AMG:>
2789 --Commented the call to PA_FIN_PLAN_PVT.CREATE_FINPLAN_LINES
2790 --Comment START
2791 /*
2792 PA_FIN_PLAN_PVT.CREATE_FINPLAN_LINES
2793 ( p_calling_context => l_calling_context -- Bug# 2674353
2794 ,p_fin_plan_version_id => l_created_version_id
2795 ,p_budget_lines_tab => p_budget_lines_tab
2796 ,x_return_status => x_return_status
2797 ,x_msg_count => x_msg_count
2798 ,x_msg_data => x_msg_data );
2799
2800 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2801 pa_debug.g_err_stage:= 'Error Calling CREATE_FINPLAN_LINES';
2802 IF P_PA_DEBUG_MODE = 'Y' THEN
2803 pa_debug.write('CREATE_DRAFT: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL5);
2804 END IF;
2805 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
2806 END IF;
2807 */
2808 --Bug # 3507156-<Patchset M: B and F impact changes : AMG:>
2809 --Added a call to PA_FIN_PLAN_PVT.ADD_FIN_PLAN_LINES
2810
2811 PA_FIN_PLAN_PVT.ADD_FIN_PLAN_LINES
2812 ( p_calling_context => l_calling_context
2813 ,p_fin_plan_version_id => l_created_version_id
2814 ,p_finplan_lines_tab => p_budget_lines_tab
2815 ,x_return_status => x_return_status
2816 ,x_msg_count => x_msg_count
2817 ,x_msg_data => x_msg_data );
2818
2819 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2820 pa_debug.g_err_stage:= 'Error Calling ADD_FIN_PLAN_LINES';
2821 IF P_PA_DEBUG_MODE = 'Y' THEN
2822 pa_debug.write('CREATE_DRAFT: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL5);
2823 END IF;
2824 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
2825 END IF;
2826 END IF;
2827
2828 pa_debug.g_err_stage:= 'Restoring the Control Item links if any';
2829 IF P_PA_DEBUG_MODE = 'Y' THEN
2830 pa_debug.write('CREATE_DRAFT: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
2831 END IF;
2832
2833 /* Commented out for bug 3550073
2834 IF ( nvl(l_ci_rec_tab.last,0) > 0 ) THEN
2835 FOR i in l_ci_rec_tab.first..l_ci_rec_tab.last LOOP
2836
2837 pa_fp_ci_merge.FP_CI_LINK_CONTROL_ITEMS (
2838 p_project_id => p_project_id
2839 ,p_s_fp_version_id => l_ci_rec_tab(i).ci_plan_version_id
2840 ,p_t_fp_version_id => l_created_version_id
2841 ,x_return_status => x_return_status
2842 ,x_msg_count => x_msg_count
2843 ,x_msg_data => x_msg_data);
2844
2845 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2846 pa_debug.g_err_stage:= 'Error Calling FP_CI_LINK_CONTROL_ITEMS';
2847 IF P_PA_DEBUG_MODE = 'Y' THEN
2848 pa_debug.write('CREATE_DRAFT: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL5);
2849 END IF;
2850 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
2851 END IF; -- l_return_status <> FND_API.G_RET_STS_SUCCESS
2852
2853 END LOOP; --first..last
2854 END IF; -- l_ci_rec_tab is not null */
2855
2856
2857 pa_debug.g_err_stage:= 'Exiting CREATE_DRAFT';
2858 IF P_PA_DEBUG_MODE = 'Y' THEN
2859 pa_debug.write('CREATE_DRAFT: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL2);
2860 pa_debug.reset_err_stack;
2861 END IF;
2862
2863 EXCEPTION
2864
2865 WHEN PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc THEN
2866
2867 x_return_status := FND_API.G_RET_STS_ERROR;
2868 l_msg_count := FND_MSG_PUB.count_msg;
2869 IF l_msg_count = 1 THEN
2870 PA_INTERFACE_UTILS_PUB.get_messages
2871 (p_encoded => FND_API.G_TRUE
2872 ,p_msg_index => 1
2873 ,p_msg_count => l_msg_count
2874 ,p_msg_data => l_msg_data
2875 ,p_data => l_data
2876 ,p_msg_index_out => l_msg_index_out);
2877 x_msg_data := l_data;
2878 x_msg_count := l_msg_count;
2879 ELSE
2880 x_msg_count := l_msg_count;
2881 END IF;
2882
2883 pa_debug.g_err_stage:= 'Invalid Arguments Passed';
2884 IF P_PA_DEBUG_MODE = 'Y' THEN
2885 pa_debug.write('CREATE_DRAFT: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL5);
2886 pa_debug.reset_err_stack;
2887 END IF;
2888 RAISE;
2889
2890 WHEN others THEN
2891
2892 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2893 x_msg_count := 1;
2894 x_msg_data := SQLERRM;
2895 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'pa_fin_plan_pvt'
2896 ,p_procedure_name => 'CREATE_DRAFT');
2897 pa_debug.g_err_stage:= 'Unexpected Error'||SQLERRM;
2898 IF P_PA_DEBUG_MODE = 'Y' THEN
2899 pa_debug.write('CREATE_DRAFT: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL5);
2900 pa_debug.reset_err_stack;
2901 END IF;
2902 RAISE;
2903
2904 END CREATE_DRAFT;
2905
2906 /*
2907 This procedure would use the input budget_line_tbl to insert records into
2908 pa_resource_assignments, pa_budget_lines, pa_mc_budget_lines and also takes
2909 care of rolling up the resource assignments and maintaining the denorm table.
2910 */
2911 PROCEDURE CREATE_FINPLAN_LINES
2912 ( -- Bug Fix: 4569365. Removed MRC code.
2913 -- p_calling_context IN pa_mrc_finplan.g_calling_module%TYPE /* Bug# 2674353 */
2914 p_calling_context IN VARCHAR2
2915 ,p_fin_plan_version_id IN pa_budget_versions.budget_version_id%TYPE
2916 ,p_budget_lines_tab IN pa_fin_plan_pvt.budget_lines_tab
2917 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2918 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
2919 ,x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
2920 IS
2921
2922 l_msg_count NUMBER := 0;
2923 l_data VARCHAR2(2000);
2924 l_msg_data VARCHAR2(2000);
2925 l_error_msg_code VARCHAR2(30);
2926 l_msg_index_out NUMBER;
2927 l_return_status VARCHAR2(1);
2928 l_debug_mode VARCHAR2(30);
2929
2930 /* Start of table variables for members of p_budget_lines_table */
2931
2932 l_task_id_tab task_id_tab;
2933 l_resource_list_member_id_tab resource_list_member_id_tab;
2934 l_description_tab description_tab;
2935 l_start_date_tab start_date_tab;
2936 l_end_date_tab end_date_tab;
2937 l_period_name_tab period_name_tab;
2938 l_quantity_tab quantity_tab;
2939 l_unit_of_measure_tab unit_of_measure_tab;
2940 l_track_as_labor_flag_tab track_as_labor_flag_tab;
2941 l_txn_currency_code_tab txn_currency_code_tab;
2942 l_raw_cost_tab raw_cost_tab;
2943 l_burdened_cost_tab burdened_cost_tab;
2944 l_revenue_tab revenue_tab;
2945 l_txn_raw_cost_tab txn_raw_cost_tab;
2946 l_txn_burdened_cost_tab txn_burdened_cost_tab;
2947 l_txn_revenue_tab txn_revenue_tab;
2948 l_project_raw_cost_tab project_raw_cost_tab;
2949 l_project_burdened_cost_tab project_burdened_cost_tab;
2950 l_project_revenue_tab project_revenue_tab;
2951 l_change_reason_code_tab change_reason_code_tab;
2952 l_attribute_category_tab attribute_category_tab;
2953 l_attribute1_tab attribute1_tab;
2954 l_attribute2_tab attribute2_tab;
2955 l_attribute3_tab attribute3_tab;
2956 l_attribute4_tab attribute4_tab;
2957 l_attribute5_tab attribute5_tab;
2958 l_attribute6_tab attribute6_tab;
2959 l_attribute7_tab attribute7_tab;
2960 l_attribute8_tab attribute8_tab;
2961 l_attribute9_tab attribute9_tab;
2962 l_attribute10_tab attribute10_tab;
2963 l_attribute11_tab attribute11_tab;
2964 l_attribute12_tab attribute12_tab;
2965 l_attribute13_tab attribute13_tab;
2966 l_attribute14_tab attribute14_tab;
2967 l_attribute15_tab attribute15_tab;
2968 l_PF_COST_RATE_TYPE_tab PF_COST_RATE_TYPE_tab;
2969 l_PF_COST_RATE_DATE_TYPE_tab PF_COST_RATE_DATE_TYPE_tab;
2970 l_PF_COST_RATE_DATE_tab PF_COST_RATE_DATE_tab;
2971 l_PF_COST_RATE_tab PF_COST_RATE_tab;
2972 l_PF_REV_RATE_TYPE_tab PF_REV_RATE_TYPE_tab;
2973 l_PF_REV_RATE_DATE_TYPE_tab PF_REV_RATE_DATE_TYPE_tab;
2974 l_PF_REV_RATE_DATE_tab PF_REV_RATE_DATE_tab;
2975 l_PF_REV_RATE_tab PF_REV_RATE_tab;
2976 l_PJ_COST_RATE_TYPE_tab PJ_COST_RATE_TYPE_tab;
2977 l_PJ_COST_RATE_DATE_TYPE_tab PJ_COST_RATE_DATE_TYPE_tab;
2978 l_PJ_COST_RATE_DATE_tab PJ_COST_RATE_DATE_tab;
2979 l_PJ_COST_RATE_tab PJ_COST_RATE_tab;
2980 l_PJ_REV_RATE_TYPE_tab PJ_REV_RATE_TYPE_tab;
2981 l_PJ_REV_RATE_DATE_TYPE_tab PJ_REV_RATE_DATE_TYPE_tab;
2982 l_PJ_REV_RATE_DATE_tab PJ_REV_RATE_DATE_tab;
2983 l_PJ_REV_RATE_tab PJ_REV_RATE_tab;
2984 l_pm_product_code_tab pm_product_code_tab;
2985 l_pm_budget_line_reference_tab pm_budget_line_reference_tab;
2986 l_quantity_source_tab quantity_source_tab;
2987 l_raw_cost_source_tab raw_cost_source_tab;
2988 l_burdened_cost_source_tab burdened_cost_source_tab;
2989 l_revenue_source_tab revenue_source_tab;
2990 l_resource_assignment_id_tab resource_assignment_id_tab;
2991
2992 /* End of table variables for members of p_budget_lines_table */
2993 -- Bug Fix: 4569365. Removed MRC code.
2994 -- l_calling_context pa_mrc_finplan.g_calling_module%TYPE;
2995 l_calling_context VARCHAR2(30);
2996
2997 /* #2727304 */
2998 l_proj_currency_code pa_projects_all.project_currency_code%TYPE;
2999 l_projfunc_currency_code pa_projects_all.projfunc_currency_code%TYPE;
3000
3001 BEGIN
3002
3003 x_msg_count := 0;
3004 x_return_status := FND_API.G_RET_STS_SUCCESS;
3005
3006 fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
3007 l_debug_mode := NVL(l_debug_mode, 'Y');
3008 IF p_pa_debug_mode = 'Y' THEN
3009 pa_debug.set_err_stack('pa_fin_plan_pvt.CREATE_FINPLAN_LINES');
3010 END IF;
3011 IF P_PA_DEBUG_MODE = 'Y' THEN
3012 pa_debug.set_process('CREATE_FINPLAN_LINES: ' || 'PLSQL','LOG',l_debug_mode);
3013 END IF;
3014
3015
3016 -- Check for business rules violations
3017
3018 IF P_PA_DEBUG_MODE = 'Y' THEN
3019 pa_debug.g_err_stage:= 'Validating input parameters - CREATE_FINPLAN_LINES';
3020 pa_debug.write('CREATE_FINPLAN_LINES: ' || g_module_name,pa_debug.g_err_stage,3);
3021 END IF;
3022
3023 --Validate plan version id
3024
3025 IF (p_fin_plan_version_id IS NULL)
3026 THEN
3027 IF P_PA_DEBUG_MODE = 'Y' THEN
3028 pa_debug.g_err_stage:= 'fin_plan_version_id = '|| p_fin_plan_version_id;
3029 pa_debug.write('CREATE_FINPLAN_LINES: ' || g_module_name,pa_debug.g_err_stage,5);
3030 END IF;
3031
3032 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
3033 p_msg_name => 'PA_FP_INV_PARAM_PASSED');
3034
3035 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
3036 END IF;
3037
3038 l_calling_context := p_calling_context; /* Bug# 2674353 */
3039
3040 --Populate the individual column tables so that we can do a bulk
3041 --insert into resource assignments table.
3042
3043 IF nvl(p_budget_lines_tab.last,0) > 0 THEN
3044 FOR i in p_budget_lines_tab.first..p_budget_lines_tab.last LOOP
3045
3046 l_task_id_tab(i) := p_budget_lines_tab(i).system_reference1;
3047 l_resource_list_member_id_tab(i) := p_budget_lines_tab(i).system_reference2;
3048 l_description_tab(i) := p_budget_lines_tab(i).description;
3049 l_start_date_tab(i) := p_budget_lines_tab(i).start_date;
3050 l_end_date_tab(i) := p_budget_lines_tab(i).end_date;
3051 l_period_name_tab(i) := p_budget_lines_tab(i).period_name;
3052 l_quantity_tab(i) := p_budget_lines_tab(i).quantity;
3053 l_unit_of_measure_tab(i) := p_budget_lines_tab(i).system_reference4;
3054 l_track_as_labor_flag_tab(i) := p_budget_lines_tab(i).system_reference5;
3055 l_txn_currency_code_tab(i) := p_budget_lines_tab(i).txn_currency_code;
3056 l_raw_cost_tab(i) := p_budget_lines_tab(i).projfunc_raw_cost;
3057 l_burdened_cost_tab(i) := p_budget_lines_tab(i).projfunc_burdened_cost;
3058 l_revenue_tab(i) := p_budget_lines_tab(i).projfunc_revenue;
3059 l_txn_raw_cost_tab(i) := p_budget_lines_tab(i).txn_raw_cost;
3060 l_txn_burdened_cost_tab(i) := p_budget_lines_tab(i).txn_burdened_cost;
3061 l_txn_revenue_tab(i) := p_budget_lines_tab(i).txn_revenue;
3062 l_project_raw_cost_tab(i) := p_budget_lines_tab(i).project_raw_cost;
3063 l_project_burdened_cost_tab(i) := p_budget_lines_tab(i).project_burdened_cost;
3064 l_project_revenue_tab(i) := p_budget_lines_tab(i).project_revenue;
3065 l_change_reason_code_tab(i) := p_budget_lines_tab(i).change_reason_code;
3066 l_attribute_category_tab(i) := p_budget_lines_tab(i).attribute_category;
3067 l_attribute1_tab(i) := p_budget_lines_tab(i).attribute1;
3068 l_attribute2_tab(i) := p_budget_lines_tab(i).attribute2;
3069 l_attribute3_tab(i) := p_budget_lines_tab(i).attribute3;
3070 l_attribute4_tab(i) := p_budget_lines_tab(i).attribute4;
3071 l_attribute5_tab(i) := p_budget_lines_tab(i).attribute5;
3072 l_attribute6_tab(i) := p_budget_lines_tab(i).attribute6;
3073 l_attribute7_tab(i) := p_budget_lines_tab(i).attribute7;
3074 l_attribute8_tab(i) := p_budget_lines_tab(i).attribute8;
3075 l_attribute9_tab(i) := p_budget_lines_tab(i).attribute9;
3076 l_attribute10_tab(i) := p_budget_lines_tab(i).attribute10;
3077 l_attribute11_tab(i) := p_budget_lines_tab(i).attribute11;
3078 l_attribute12_tab(i) := p_budget_lines_tab(i).attribute12;
3079 l_attribute13_tab(i) := p_budget_lines_tab(i).attribute13;
3080 l_attribute14_tab(i) := p_budget_lines_tab(i).attribute14;
3081 l_attribute15_tab(i) := p_budget_lines_tab(i).attribute15;
3082 l_PF_COST_RATE_TYPE_tab(i) := p_budget_lines_tab(i).PROJFUNC_COST_RATE_TYPE;
3083 l_PF_COST_RATE_DATE_TYPE_tab(i) := p_budget_lines_tab(i).PROJFUNC_COST_RATE_DATE_TYPE;
3084 l_PF_COST_RATE_DATE_tab(i) := p_budget_lines_tab(i).PROJFUNC_COST_RATE_DATE;
3085 l_PF_COST_RATE_tab(i) := p_budget_lines_tab(i).PROJFUNC_COST_EXCHANGE_RATE;
3086 l_PF_REV_RATE_TYPE_tab(i) := p_budget_lines_tab(i).PROJFUNC_REV_RATE_TYPE;
3087 l_PF_REV_RATE_DATE_TYPE_tab(i) := p_budget_lines_tab(i).PROJFUNC_REV_RATE_DATE_TYPE;
3088 l_PF_REV_RATE_DATE_tab(i) := p_budget_lines_tab(i).PROJFUNC_REV_RATE_DATE;
3089 l_PF_REV_RATE_tab(i) := p_budget_lines_tab(i).PROJFUNC_REV_EXCHANGE_RATE;
3090 l_PJ_COST_RATE_TYPE_tab(i) := p_budget_lines_tab(i).PROJECT_COST_RATE_TYPE;
3091 l_PJ_COST_RATE_DATE_TYPE_tab(i) := p_budget_lines_tab(i).PROJECT_COST_RATE_DATE_TYPE;
3092 l_PJ_COST_RATE_DATE_tab(i) := p_budget_lines_tab(i).PROJECT_COST_RATE_DATE;
3093 l_PJ_COST_RATE_tab(i) := p_budget_lines_tab(i).PROJECT_COST_EXCHANGE_RATE;
3094 l_PJ_REV_RATE_TYPE_tab(i) := p_budget_lines_tab(i).PROJECT_REV_RATE_TYPE;
3095 l_PJ_REV_RATE_DATE_TYPE_tab(i) := p_budget_lines_tab(i).PROJECT_REV_RATE_DATE_TYPE;
3096 l_PJ_REV_RATE_DATE_tab(i) := p_budget_lines_tab(i).PROJECT_REV_RATE_DATE;
3097 l_PJ_REV_RATE_tab(i) := p_budget_lines_tab(i).PROJECT_REV_EXCHANGE_RATE;
3098 l_pm_product_code_tab(i) := p_budget_lines_tab(i).pm_product_code;
3099 l_pm_budget_line_reference_tab(i) := p_budget_lines_tab(i).pm_budget_line_reference;
3100 l_quantity_source_tab(i) := p_budget_lines_tab(i).quantity_source;
3101 l_raw_cost_source_tab(i) := p_budget_lines_tab(i).raw_cost_source;
3102 l_burdened_cost_source_tab(i) := p_budget_lines_tab(i).burdened_cost_source;
3103 l_revenue_source_tab(i) := p_budget_lines_tab(i).revenue_source;
3104 l_resource_assignment_id_tab(i) := p_budget_lines_tab(i).resource_assignment_id;
3105
3106 END LOOP;
3107 END IF;
3108
3109 IF P_PA_DEBUG_MODE = 'Y' THEN
3110 pa_debug.g_err_stage:= 'populated the plsql tables';
3111 pa_debug.write('CREATE_FINPLAN_LINES: ' || g_module_name,pa_debug.g_err_stage,3);
3112
3113 pa_debug.g_err_stage:= 'Delete records if any from the rollup tmp';
3114 pa_debug.write('CREATE_FINPLAN_LINES: ' || g_module_name,pa_debug.g_err_stage,3);
3115 END IF;
3116
3117 delete from pa_fp_rollup_tmp; /* Included after UT */
3118
3119 /* 2727304: Getting the Proj and Proj Func Currencies of the Fin Plan version's project.
3120 These will be used to populate the Proj and Projfunc currency codes in the
3121 pa_fp_rollup_tmp table in case they are not being passed to this API. */
3122
3123 IF P_PA_DEBUG_MODE = 'Y' THEN
3124 pa_debug.g_err_stage:= 'Getting the proj and projfunc currency codes';
3125 pa_debug.write('CREATE_FINPLAN_LINES: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
3126 END IF;
3127
3128 SELECT project_currency_code
3129 ,projfunc_currency_code
3130 INTO l_proj_currency_code
3131 ,l_projfunc_currency_code
3132 FROM pa_projects_all
3133 WHERE project_id = (SELECT project_id
3134 FROM pa_budget_versions
3135 WHERE budget_version_id = p_fin_plan_version_id);
3136
3137 --Bulk insert into the rollup tmp table.
3138 IF nvl(p_budget_lines_tab.last,0) > 0 THEN
3139 FORALL i in p_budget_lines_tab.first..p_budget_lines_tab.last
3140 Insert into pa_fp_rollup_tmp
3141 (
3142 system_reference1 --task_id
3143 ,system_reference2 --rlmid
3144 ,description
3145 ,start_date
3146 ,end_date
3147 ,period_name
3148 ,quantity
3149 ,system_reference4 --unit_of_measure
3150 ,system_reference5 --track_as_labor_flag
3151 ,txn_currency_code
3152 ,project_currency_code --added for #2727304
3153 ,projfunc_currency_code --added for #2727304
3154 ,projfunc_raw_cost
3155 ,projfunc_burdened_cost
3156 ,projfunc_revenue
3157 ,txn_raw_cost
3158 ,txn_burdened_cost
3159 ,txn_revenue
3160 ,project_raw_cost
3161 ,project_burdened_cost
3162 ,project_revenue
3163 ,change_reason_code
3164 ,attribute_category
3165 ,attribute1
3166 ,attribute2
3167 ,attribute3
3168 ,attribute4
3169 ,attribute5
3170 ,attribute6
3171 ,attribute7
3172 ,attribute8
3173 ,attribute9
3174 ,attribute10
3175 ,attribute11
3176 ,attribute12
3177 ,attribute13
3178 ,attribute14
3179 ,attribute15
3180 ,PROJFUNC_COST_RATE_TYPE
3181 ,PROJFUNC_COST_RATE_DATE_TYPE
3182 ,PROJFUNC_COST_RATE_DATE
3183 ,PROJFUNC_COST_EXCHANGE_RATE
3184 ,PROJFUNC_REV_RATE_TYPE
3185 ,PROJFUNC_REV_RATE_DATE_TYPE
3186 ,PROJFUNC_REV_RATE_DATE
3187 ,PROJFUNC_REV_EXCHANGE_RATE
3188 ,PROJECT_COST_RATE_TYPE
3189 ,PROJECT_COST_RATE_DATE_TYPE
3190 ,PROJECT_COST_RATE_DATE
3191 ,PROJECT_COST_EXCHANGE_RATE
3192 ,PROJECT_REV_RATE_TYPE
3193 ,PROJECT_REV_RATE_DATE_TYPE
3194 ,PROJECT_REV_RATE_DATE
3195 ,PROJECT_REV_EXCHANGE_RATE
3196 ,pm_product_code
3197 ,pm_budget_line_reference
3198 ,quantity_source
3199 ,raw_cost_source
3200 ,burdened_cost_source
3201 ,revenue_source
3202 ,resource_assignment_id
3203 ,budget_version_id
3204 )
3205 Values
3206 (
3207 l_task_id_tab(i)
3208 ,l_resource_list_member_id_tab(i)
3209 ,l_description_tab(i)
3210 ,l_start_date_tab(i)
3211 ,l_end_date_tab(i)
3212 ,l_period_name_tab(i)
3213 ,l_quantity_tab(i)
3214 ,l_unit_of_measure_tab(i)
3215 ,l_track_as_labor_flag_tab(i)
3216 ,l_txn_currency_code_tab(i)
3217 ,l_proj_currency_code --added for #2727304
3218 ,l_projfunc_currency_code --added for #2727304
3219 ,l_raw_cost_tab(i)
3220 ,l_burdened_cost_tab(i)
3221 ,l_revenue_tab(i)
3222 ,l_txn_raw_cost_tab(i)
3223 ,l_txn_burdened_cost_tab(i)
3224 ,l_txn_revenue_tab(i)
3225 ,l_project_raw_cost_tab(i)
3226 ,l_project_burdened_cost_tab(i)
3227 ,l_project_revenue_tab(i)
3228 ,l_change_reason_code_tab(i)
3229 ,l_attribute_category_tab(i)
3230 ,l_attribute1_tab(i)
3231 ,l_attribute2_tab(i)
3232 ,l_attribute3_tab(i)
3233 ,l_attribute4_tab(i)
3234 ,l_attribute5_tab(i)
3235 ,l_attribute6_tab(i)
3236 ,l_attribute7_tab(i)
3237 ,l_attribute8_tab(i)
3238 ,l_attribute9_tab(i)
3239 ,l_attribute10_tab(i)
3240 ,l_attribute11_tab(i)
3241 ,l_attribute12_tab(i)
3242 ,l_attribute13_tab(i)
3243 ,l_attribute14_tab(i)
3244 ,l_attribute15_tab(i)
3245 ,l_PF_COST_RATE_TYPE_tab(i)
3246 ,l_PF_COST_RATE_DATE_TYPE_tab(i)
3247 ,l_PF_COST_RATE_DATE_tab(i)
3248 ,l_PF_COST_RATE_tab(i)
3249 ,l_PF_REV_RATE_TYPE_tab(i)
3250 ,l_PF_REV_RATE_DATE_TYPE_tab(i)
3251 ,l_PF_REV_RATE_DATE_tab(i)
3252 ,l_PF_REV_RATE_tab(i)
3253 ,l_PJ_COST_RATE_TYPE_tab(i)
3254 ,l_PJ_COST_RATE_DATE_TYPE_tab(i)
3255 ,l_PJ_COST_RATE_DATE_tab(i)
3256 ,l_PJ_COST_RATE_tab(i)
3257 ,l_PJ_REV_RATE_TYPE_tab(i)
3258 ,l_PJ_REV_RATE_DATE_TYPE_tab(i)
3259 ,l_PJ_REV_RATE_DATE_tab(i)
3260 ,l_PJ_REV_RATE_tab(i)
3261 ,l_pm_product_code_tab(i)
3262 ,l_pm_budget_line_reference_tab(i)
3263 ,l_quantity_source_tab(i)
3264 ,l_raw_cost_source_tab(i)
3265 ,l_burdened_cost_source_tab(i)
3266 ,l_revenue_source_tab(i)
3267 ,l_resource_assignment_id_tab(i)
3268 ,p_fin_plan_version_id
3269 );
3270 END IF;
3271
3272 IF P_PA_DEBUG_MODE = 'Y' THEN
3273 pa_debug.g_err_stage:= 'number of records inserted -> ' || sql%ROWCOUNT;
3274 pa_debug.write('CREATE_FINPLAN_LINES: ' || g_module_name,pa_debug.g_err_stage,3);
3275 END IF;
3276
3277 --Call procedure pa_fp_elements_pub.CREATE_ASSGMT_FROM_ROLLUPTMP to create
3278 --resource assignments and elements.
3279
3280 IF P_PA_DEBUG_MODE = 'Y' THEN
3281 pa_debug.g_err_stage:= 'calling create_assgmt_from_rolluptmp';
3282 pa_debug.write('CREATE_FINPLAN_LINES: ' || g_module_name,pa_debug.g_err_stage,3);
3283 END IF;
3284
3285 pa_fp_elements_pub.create_assgmt_from_rolluptmp
3286 ( p_fin_plan_version_id => p_fin_plan_version_id
3287 ,x_return_status => x_return_status
3288 ,x_msg_count => x_msg_count
3289 ,x_msg_data => x_msg_data );
3290
3291 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3292 IF P_PA_DEBUG_MODE = 'Y' THEN
3293 pa_debug.g_err_stage:= 'Error Calling create_assgmt_from_rolluptmp';
3294 pa_debug.write('CREATE_FINPLAN_LINES: ' || g_module_name,pa_debug.g_err_stage,5);
3295 END IF;
3296 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
3297 END IF;
3298
3299 --Bug # 3507156-<Patchset M: B and F impact changes : AMG:>
3300 --Commented the api PROCESS_MODIFIED_LINES
3301 --Comment START
3302 /*
3303 IF P_PA_DEBUG_MODE = 'Y' THEN
3304 pa_debug.g_err_stage:= 'calling PROCESS_MODIFIED_LINES';
3305 pa_debug.write('CREATE_FINPLAN_LINES: ' || g_module_name,pa_debug.g_err_stage,3);
3306 END IF;
3307
3308 --Call process_modified_lines in edit line package.
3309 PA_FP_EDIT_LINE_PKG.PROCESS_MODIFIED_LINES
3310 ( p_calling_context => l_calling_context -- Bug# 2674353
3311 ,p_resource_assignment_id => NULL
3312 ,p_fin_plan_version_id => p_fin_plan_version_id
3313 ,x_return_status => x_return_status
3314 ,x_msg_count => x_msg_count
3315 ,x_msg_data => x_msg_data );
3316
3317 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3318
3319 IF P_PA_DEBUG_MODE = 'Y' THEN
3320 pa_debug.g_err_stage:= 'Error Calling PROCESS_MODIFIED_LINES';
3321 pa_debug.write('CREATE_FINPLAN_LINES: ' || g_module_name,pa_debug.g_err_stage,5);
3322 END IF;
3323 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
3324 END IF;
3325
3326 IF P_PA_DEBUG_MODE = 'Y' THEN
3327 pa_debug.g_err_stage:= 'Exiting CREATE_FINPLAN_LINES';
3328 pa_debug.write('CREATE_FINPLAN_LINES: ' || g_module_name,pa_debug.g_err_stage,3);
3329 END IF;
3330
3331 */
3332 --Bug # 3507156-<Patchset M: B and F impact changes : AMG:>
3333 --Replaced PA_FP_EDIT_LINE_PKG.PROCESS_MODIFIED_LINES with an insert to pa_budget_lines
3334
3335 -- Bug 3825873 17-JUL-2004 Do not insert amounts as calculate first checks existing values
3336 -- and the input values are different and then only acts on the record.
3337
3338 --Bug 4133468. Calculate Api will be called after this insert stmt(call is in add_finplan_lines API).In autobaseline
3339 --flow, to make sure that the project currency amounts stamped by the calculate API are same as the project currency
3340 --amounts in the funding lines, exchange rate is stamped as (pc in funding lines)/(pfc in funding lines) and
3341 --rate type is stamped as User for project revenue conversion attrs.
3342
3343 /* -----------------------------------------------------------------------------------------
3344 * Bug 4221590: commenting out the following code to avoid creation of budget lines
3345 * with null quantities, instead population pa_fp_spread_calc_tmp1, so that calculate
3346 * api can use that to insert/spread the budget lines passed from AMG/MSP
3347 *----------------------------------------------------------------------------------------
3348 INSERT INTO pa_budget_lines(
3349 RESOURCE_ASSIGNMENT_ID
3350 ,BUDGET_LINE_ID
3351 ,BUDGET_VERSION_ID
3352 ,START_DATE
3353 ,LAST_UPDATE_DATE
3354 ,LAST_UPDATED_BY
3355 ,CREATION_DATE
3356 ,CREATED_BY
3357 ,LAST_UPDATE_LOGIN
3358 ,END_DATE
3359 ,PERIOD_NAME
3360 ,QUANTITY
3361 ,RAW_COST
3362 ,BURDENED_COST
3363 ,REVENUE
3364 ,CHANGE_REASON_CODE
3365 ,DESCRIPTION
3366 ,ATTRIBUTE_CATEGORY
3367 ,ATTRIBUTE1
3368 ,ATTRIBUTE2
3369 ,ATTRIBUTE3
3370 ,ATTRIBUTE4
3371 ,ATTRIBUTE5
3372 ,ATTRIBUTE6
3373 ,ATTRIBUTE7
3374 ,ATTRIBUTE8
3375 ,ATTRIBUTE9
3376 ,ATTRIBUTE10
3377 ,ATTRIBUTE11
3378 ,ATTRIBUTE12
3379 ,ATTRIBUTE13
3380 ,ATTRIBUTE14
3381 ,ATTRIBUTE15
3382 ,RAW_COST_SOURCE
3383 ,BURDENED_COST_SOURCE
3384 ,QUANTITY_SOURCE
3385 ,REVENUE_SOURCE
3386 ,PROJFUNC_CURRENCY_CODE
3387 ,PROJFUNC_COST_RATE_TYPE
3388 ,PROJFUNC_COST_EXCHANGE_RATE
3389 ,PROJFUNC_COST_RATE_DATE_TYPE
3390 ,PROJFUNC_COST_RATE_DATE
3391 ,PROJECT_CURRENCY_CODE
3392 ,PROJECT_COST_RATE_TYPE
3393 ,PROJECT_COST_EXCHANGE_RATE
3394 ,PROJECT_COST_RATE_DATE_TYPE
3395 ,PROJECT_COST_RATE_DATE
3396 ,PROJECT_RAW_COST
3397 ,PROJECT_BURDENED_COST
3398 ,PROJECT_REVENUE
3399 ,TXN_RAW_COST
3400 ,TXN_BURDENED_COST
3401 ,TXN_REVENUE
3402 ,TXN_CURRENCY_CODE
3403 ,BUCKETING_PERIOD_CODE
3404 ,PROJFUNC_REV_RATE_DATE_TYPE
3405 ,PROJFUNC_REV_RATE_DATE
3406 ,PROJFUNC_REV_RATE_TYPE
3407 ,PROJFUNC_REV_EXCHANGE_RATE
3408 ,PROJECT_REV_RATE_TYPE
3409 ,PROJECT_REV_EXCHANGE_RATE
3410 ,PROJECT_REV_RATE_DATE_TYPE
3411 ,PROJECT_REV_RATE_DATE
3412 ,PM_PRODUCT_CODE
3413 ,PM_BUDGET_LINE_REFERENCE ) -- Added for bug 3833724
3414
3415 (SELECT
3416 RESOURCE_ASSIGNMENT_ID
3417 ,pa_budget_lines_s.nextval
3418 ,p_fin_plan_version_id
3419 ,START_DATE
3420 ,SYSDATE
3421 ,FND_GLOBAL.USER_ID
3422 ,SYSDATE
3423 ,FND_GLOBAL.USER_ID
3424 ,FND_GLOBAL.LOGIN_ID
3425 ,END_DATE
3426 ,PERIOD_NAME
3427 ,null--QUANTITY
3428 ,null--PROJFUNC_RAW_COST
3429 ,null--PROJFUNC_BURDENED_COST
3430 ,NULL--PROJFUNC_REVENUE
3431 ,CHANGE_REASON_CODE
3432 ,DESCRIPTION
3433 ,ATTRIBUTE_CATEGORY
3434 ,ATTRIBUTE1
3435 ,ATTRIBUTE2
3436 ,ATTRIBUTE3
3437 ,ATTRIBUTE4
3438 ,ATTRIBUTE5
3439 ,ATTRIBUTE6
3440 ,ATTRIBUTE7
3441 ,ATTRIBUTE8
3442 ,ATTRIBUTE9
3443 ,ATTRIBUTE10
3444 ,ATTRIBUTE11
3445 ,ATTRIBUTE12
3446 ,ATTRIBUTE13
3447 ,ATTRIBUTE14
3448 ,ATTRIBUTE15
3449 ,nvl(RAW_COST_SOURCE,decode(PROJFUNC_RAW_COST,null,null,PA_FP_CONSTANTS_PKG.G_AMOUNT_SOURCE_MANUAL_M))
3450 ,nvl(BURDENED_COST_SOURCE,decode(PROJFUNC_BURDENED_COST,null,null,PA_FP_CONSTANTS_PKG.G_AMOUNT_SOURCE_MANUAL_M))
3451 ,nvl(QUANTITY_SOURCE,decode(QUANTITY,null,null,PA_FP_CONSTANTS_PKG.G_AMOUNT_SOURCE_MANUAL_M))
3452 ,nvl(REVENUE_SOURCE,decode(PROJFUNC_REVENUE,null,null,PA_FP_CONSTANTS_PKG.G_AMOUNT_SOURCE_MANUAL_M))
3453 ,PROJFUNC_CURRENCY_CODE
3454 ,PROJFUNC_COST_RATE_TYPE
3455 ,PROJFUNC_COST_EXCHANGE_RATE
3456 ,PROJFUNC_COST_RATE_DATE_TYPE
3457 ,PROJFUNC_COST_RATE_DATE
3458 ,PROJECT_CURRENCY_CODE
3459 ,PROJECT_COST_RATE_TYPE
3460 ,PROJECT_COST_EXCHANGE_RATE
3461 ,PROJECT_COST_RATE_DATE_TYPE
3462 ,PROJECT_COST_RATE_DATE
3463 ,null--PROJECT_RAW_COST
3464 ,null--PROJECT_BURDENED_COST
3465 ,null--PROJECT_REVENUE
3466 ,null--TXN_RAW_COST
3467 ,null--TXN_BURDENED_COST
3468 ,null--TXN_REVENUE
3469 ,TXN_CURRENCY_CODE
3470 ,BUCKETING_PERIOD_CODE
3471 ,PROJFUNC_REV_RATE_DATE_TYPE
3472 ,PROJFUNC_REV_RATE_DATE
3473 ,PROJFUNC_REV_RATE_TYPE
3474 ,PROJFUNC_REV_EXCHANGE_RATE
3475 ,DECODE(p_calling_context,
3476 PA_FP_CONSTANTS_PKG.G_AUTOMATIC_BASELINE,'User',
3477 PROJECT_REV_RATE_TYPE)--Bug 4133468. PROJECT_REV_RATE_TYPE
3478 ,DECODE(p_calling_context,
3479 PA_FP_CONSTANTS_PKG.G_AUTOMATIC_BASELINE,DECODE(PROJFUNC_REVENUE,
3480 NULL,NULL,
3481 0,0,
3482 (PROJECT_REVENUE/PROJFUNC_REVENUE)),
3483 PROJECT_REV_EXCHANGE_RATE)--Bug 4133468. PROJECT_REV_EXCHANGE_RATE
3484 ,DECODE(p_calling_context,
3485 PA_FP_CONSTANTS_PKG.G_AUTOMATIC_BASELINE,NULL,
3486 PROJECT_REV_RATE_DATE_TYPE)--Bug 4133468. PROJECT_REV_RATE_DATE_TYPE
3487 ,DECODE(p_calling_context,
3488 PA_FP_CONSTANTS_PKG.G_AUTOMATIC_BASELINE,NULL,
3489 PROJECT_REV_RATE_DATE)--Bug 4133468. PROJECT_REV_RATE_DATE
3490 ,PM_PRODUCT_CODE -- , l_pm_product_code changed to pm_product_code for bug 3833724
3491 ,PM_BUDGET_LINE_REFERENCE -- Added for bug 3833724
3492 FROM pa_fp_rollup_tmp tmp
3493 WHERE tmp.budget_line_id IS NULL
3494 AND (tmp.txn_raw_cost IS NOT NULL
3495 or tmp.txn_burdened_cost IS NOT NULL
3496 or tmp.quantity IS NOT NULL
3497 or tmp.txn_revenue IS NOT NULL));
3498
3499 IF P_PA_DEBUG_MODE = 'Y' THEN
3500 pa_debug.g_err_stage:= 'number of records inserted -> ' || sql%ROWCOUNT;
3501 pa_debug.write('CREATE_FINPLAN_LINES: ' || g_module_name,pa_debug.g_err_stage,3);
3502 END IF;
3503 ---------------------------------------------------------------------------------------------*/
3504
3505 /* Bug 4221590:inserting into PA_FP_SPREAD_CALC_TMP1 */
3506 DELETE FROM PA_FP_SPREAD_CALC_TMP1;
3507
3508 IF P_PA_DEBUG_MODE = 'Y' THEN
3509 pa_debug.g_err_stage:= 'inserting into pa_fp_spread_calc_tmp1 -> ' || sql%ROWCOUNT;
3510 pa_debug.write('CREATE_FINPLAN_LINES: ' || g_module_name,pa_debug.g_err_stage,3);
3511 END IF;
3512
3513 INSERT INTO PA_FP_SPREAD_CALC_TMP1(
3514 RESOURCE_ASSIGNMENT_ID
3515 ,BUDGET_VERSION_ID
3516 ,START_DATE
3517 ,BL_CREATION_DATE
3518 ,BL_CREATED_BY
3519 ,END_DATE
3520 ,PERIOD_NAME
3521 ,CHANGE_REASON_CODE
3522 ,DESCRIPTION
3523 ,ATTRIBUTE_CATEGORY
3524 ,ATTRIBUTE1
3525 ,ATTRIBUTE2
3526 ,ATTRIBUTE3
3527 ,ATTRIBUTE4
3528 ,ATTRIBUTE5
3529 ,ATTRIBUTE6
3530 ,ATTRIBUTE7
3531 ,ATTRIBUTE8
3532 ,ATTRIBUTE9
3533 ,ATTRIBUTE10
3534 ,ATTRIBUTE11
3535 ,ATTRIBUTE12
3536 ,ATTRIBUTE13
3537 ,ATTRIBUTE14
3538 ,ATTRIBUTE15
3539 ,RAW_COST_SOURCE
3540 ,BURDENED_COST_SOURCE
3541 ,QUANTITY_SOURCE
3542 ,REVENUE_SOURCE
3543 ,PROJFUNC_CURRENCY_CODE
3544 ,PROJFUNC_COST_RATE_TYPE
3545 ,PROJFUNC_COST_EXCHANGE_RATE
3546 ,PROJFUNC_COST_RATE_DATE_TYPE
3547 ,PROJFUNC_COST_RATE_DATE
3548 ,PROJECT_CURRENCY_CODE
3549 ,PROJECT_COST_RATE_TYPE
3550 ,PROJECT_COST_EXCHANGE_RATE
3551 ,PROJECT_COST_RATE_DATE_TYPE
3552 ,PROJECT_COST_RATE_DATE
3553 ,TXN_CURRENCY_CODE
3554 ,BUCKETING_PERIOD_CODE
3555 ,PROJFUNC_REV_RATE_DATE_TYPE
3556 ,PROJFUNC_REV_RATE_DATE
3557 ,PROJFUNC_REV_RATE_TYPE
3558 ,PROJFUNC_REV_EXCHANGE_RATE
3559 ,PROJECT_REV_RATE_TYPE
3560 ,PROJECT_REV_EXCHANGE_RATE
3561 ,PROJECT_REV_RATE_DATE_TYPE
3562 ,PROJECT_REV_RATE_DATE
3563 ,PM_PRODUCT_CODE
3564 ,PM_BUDGET_LINE_REFERENCE ) -- Added for bug 3833724
3565 (SELECT
3566 RESOURCE_ASSIGNMENT_ID
3567 ,p_fin_plan_version_id
3568 ,START_DATE
3569 ,SYSDATE
3570 ,FND_GLOBAL.USER_ID
3571 ,END_DATE
3572 ,PERIOD_NAME
3573 ,CHANGE_REASON_CODE
3574 ,DESCRIPTION
3575 ,ATTRIBUTE_CATEGORY
3576 ,ATTRIBUTE1
3577 ,ATTRIBUTE2
3578 ,ATTRIBUTE3
3579 ,ATTRIBUTE4
3580 ,ATTRIBUTE5
3581 ,ATTRIBUTE6
3582 ,ATTRIBUTE7
3583 ,ATTRIBUTE8
3584 ,ATTRIBUTE9
3585 ,ATTRIBUTE10
3586 ,ATTRIBUTE11
3587 ,ATTRIBUTE12
3588 ,ATTRIBUTE13
3589 ,ATTRIBUTE14
3590 ,ATTRIBUTE15
3591 ,nvl(RAW_COST_SOURCE,decode(PROJFUNC_RAW_COST,null,null,PA_FP_CONSTANTS_PKG.G_AMOUNT_SOURCE_MANUAL_M))
3592 ,nvl(BURDENED_COST_SOURCE,decode(PROJFUNC_BURDENED_COST,null,null,PA_FP_CONSTANTS_PKG.G_AMOUNT_SOURCE_MANUAL_M))
3593 ,nvl(QUANTITY_SOURCE,decode(QUANTITY,null,null,PA_FP_CONSTANTS_PKG.G_AMOUNT_SOURCE_MANUAL_M))
3594 ,nvl(REVENUE_SOURCE,decode(PROJFUNC_REVENUE,null,null,PA_FP_CONSTANTS_PKG.G_AMOUNT_SOURCE_MANUAL_M))
3595 ,PROJFUNC_CURRENCY_CODE
3596 ,PROJFUNC_COST_RATE_TYPE
3597 ,PROJFUNC_COST_EXCHANGE_RATE
3598 ,PROJFUNC_COST_RATE_DATE_TYPE
3599 ,PROJFUNC_COST_RATE_DATE
3600 ,PROJECT_CURRENCY_CODE
3601 ,PROJECT_COST_RATE_TYPE
3602 ,PROJECT_COST_EXCHANGE_RATE
3603 ,PROJECT_COST_RATE_DATE_TYPE
3604 ,PROJECT_COST_RATE_DATE
3605 ,TXN_CURRENCY_CODE
3606 ,BUCKETING_PERIOD_CODE
3607 ,PROJFUNC_REV_RATE_DATE_TYPE
3608 ,PROJFUNC_REV_RATE_DATE
3609 ,PROJFUNC_REV_RATE_TYPE
3610 ,PROJFUNC_REV_EXCHANGE_RATE
3611 ,DECODE(p_calling_context,
3612 PA_FP_CONSTANTS_PKG.G_AUTOMATIC_BASELINE,'User',
3613 PROJECT_REV_RATE_TYPE)--Bug 4133468. PROJECT_REV_RATE_TYPE
3614 ,DECODE(p_calling_context,
3615 PA_FP_CONSTANTS_PKG.G_AUTOMATIC_BASELINE,DECODE(PROJFUNC_REVENUE,
3616 NULL,NULL,
3617 0,0,
3618 (PROJECT_REVENUE/PROJFUNC_REVENUE)),
3619 PROJECT_REV_EXCHANGE_RATE)--Bug 4133468. PROJECT_REV_EXCHANGE_RATE
3620 ,DECODE(p_calling_context,
3621 PA_FP_CONSTANTS_PKG.G_AUTOMATIC_BASELINE,NULL,
3622 PROJECT_REV_RATE_DATE_TYPE)--Bug 4133468. PROJECT_REV_RATE_DATE_TYPE
3623 ,DECODE(p_calling_context,
3624 PA_FP_CONSTANTS_PKG.G_AUTOMATIC_BASELINE,NULL,
3625 PROJECT_REV_RATE_DATE)--Bug 4133468. PROJECT_REV_RATE_DATE
3626 ,PM_PRODUCT_CODE -- , l_pm_product_code changed to pm_product_code for bug 3833724
3627 ,PM_BUDGET_LINE_REFERENCE -- Added for bug 3833724
3628 FROM pa_fp_rollup_tmp tmp
3629 WHERE tmp.budget_line_id IS NULL); /*Changed for bug 4224464. When a budget line is passed for which amounts and quantity
3630 were not passed i.e these values were miss_xxx values then these lines wont get selected here
3631 but in this case our intent should be to not update these columns for these lines and update
3632 the rest of the coulmns*/
3633 /* AND (tmp.txn_raw_cost IS NOT NULL
3634 or tmp.txn_burdened_cost IS NOT NULL
3635 or tmp.quantity IS NOT NULL
3636 or tmp.txn_revenue IS NOT NULL));*/
3637
3638 IF P_PA_DEBUG_MODE = 'Y' THEN
3639 pa_debug.g_err_stage:= 'number of records inserted -> ' || sql%ROWCOUNT;
3640 pa_debug.write('CREATE_FINPLAN_LINES: ' || g_module_name,pa_debug.g_err_stage,3);
3641 END IF;
3642
3643 -- Bug 3861261 Update resource assignments planning start and end date
3644 -- as the min(start_date) and max(end_date) of the corresponding budget lines
3645 -- Start changes for Bug 6432606
3646 -- In the AMG API flow for a scenario when task is having actuals the budget line
3647 -- corresponding that is not getting processed and stored in pa_fp_spread_calc_tmp1
3648 -- coz of that start date of that budget line is not taken in consideration.
3649 -- To avoid this planning_start_date is updated with original value if the original
3650 -- planning_start_date is least.
3651 IF(p_calling_context = PA_FP_CONSTANTS_PKG.G_AMG_API) THEN
3652 -- For scenario if actuals exist then planning start date for resource assignment
3653 -- should be least of the value presnt in pa_fp_spread_calc_tmp1 and pa_budget_lines
3654 -- else it should be least of value present in pa_fp_spread_calc_tmp1.
3655 update pa_resource_assignments pra
3656 set (planning_start_date, planning_end_date)
3657 = (select decode(min(pbl.start_date),NULL,
3658 nvl(min(tmp.start_date), planning_start_date),
3659 least(nvl(min(tmp.start_date), planning_start_date),
3660 nvl(min(pbl.start_date), planning_start_date))),
3661 nvl(max(tmp.end_date), planning_end_date)
3662 from pa_fp_spread_calc_tmp1 tmp, pa_budget_lines pbl
3663 where tmp.resource_assignment_id = pra.resource_assignment_id
3664 and pbl.resource_assignment_id (+)= tmp.resource_assignment_id)
3665 where pra.budget_version_id = p_fin_plan_version_id;
3666 ELSE
3667 update pa_resource_assignments pra
3668 set (planning_start_date, planning_end_date)
3669 = (select nvl(min(start_date), planning_start_date),
3670 nvl(max(end_date), planning_end_date)
3671 from pa_fp_spread_calc_tmp1 tmp /* Bug 4221590 */
3672 where tmp.resource_assignment_id = pra.resource_assignment_id)
3673 where pra.budget_version_id = p_fin_plan_version_id;
3674 END IF;
3675 --End changes for Bug 6432606
3676
3677 IF p_pa_debug_mode = 'Y' THEN
3678 pa_debug.reset_err_stack;
3679 END IF;
3680 EXCEPTION
3681 WHEN PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc THEN
3682
3683 x_return_status := FND_API.G_RET_STS_ERROR;
3684 l_msg_count := FND_MSG_PUB.count_msg;
3685 IF l_msg_count = 1 THEN
3686 PA_INTERFACE_UTILS_PUB.get_messages
3687 (p_encoded => FND_API.G_TRUE
3688 ,p_msg_index => 1
3689 ,p_msg_count => l_msg_count
3690 ,p_msg_data => l_msg_data
3691 ,p_data => l_data
3692 ,p_msg_index_out => l_msg_index_out);
3693 x_msg_data := l_data;
3694 x_msg_count := l_msg_count;
3695 ELSE
3696 x_msg_count := l_msg_count;
3697 END IF;
3698
3699 IF P_PA_DEBUG_MODE = 'Y' THEN
3700 pa_debug.g_err_stage:= 'Invalid Arguments Passed';
3701 pa_debug.write('CREATE_FINPLAN_LINES: ' || g_module_name,pa_debug.g_err_stage,5);
3702 pa_debug.reset_err_stack;
3703 END IF;
3704 RAISE;
3705
3706 WHEN others THEN
3707
3708 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3709 x_msg_count := 1;
3710 x_msg_data := SQLERRM;
3711 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'pa_fin_plan_pvt'
3712 ,p_procedure_name => 'CREATE_FINPLAN_LINES');
3713 IF P_PA_DEBUG_MODE = 'Y' THEN
3714 pa_debug.g_err_stage:= 'Unexpected Error'||SQLERRM;
3715 pa_debug.write('CREATE_FINPLAN_LINES: ' || g_module_name,pa_debug.g_err_stage,5);
3716 pa_debug.reset_err_stack;
3717 END IF;
3718 RAISE;
3719
3720 END CREATE_FINPLAN_LINES;
3721
3722 /*Given the name of a plan type this function returns the
3723 Id of that plan type if it exists. Otherwise Null is
3724 returned
3725 */
3726
3727 FUNCTION Fetch_Plan_Type_Id
3728 (p_fin_plan_type_name pa_fin_plan_types_tl.name%TYPE) RETURN NUMBER IS
3729
3730 CURSOR l_get_plan_type_id_csr
3731 (p_fin_plan_type_name pa_fin_plan_types_tl.name%TYPE )
3732 IS
3733 SELECT fin_plan_type_id
3734 FROM pa_fin_plan_types_vl
3735 WHERE name=p_fin_plan_type_name;
3736
3737 x_fin_plan_type_id pa_fin_plan_types_b.fin_plan_type_id%TYPE;
3738
3739 BEGIN
3740
3741 OPEN l_get_plan_type_id_csr(p_fin_plan_type_name);
3742 FETCH l_get_plan_type_id_csr INTO x_fin_plan_type_id;
3743
3744 IF (l_get_plan_type_id_csr%FOUND) THEN
3745
3746 CLOSE l_get_plan_type_id_csr;
3747 RETURN x_fin_plan_type_id;
3748
3749 ELSE
3750
3751 CLOSE l_get_plan_type_id_csr;
3752 RETURN NULL;
3753
3754 END IF;
3755
3756 END Fetch_Plan_Type_Id;
3757
3758 /*This Procudure accepts plan type id and plan type name. If plan type id is not null
3759 its validity is checked and an error message is thrown if the id is invalid.If the name
3760 (and not id) is passed, and if it is valid (case sensitive search is made while trying ot
3761 find the id of the name) tehe Id is passed. Otherwise an error message is thrown
3762 */
3763
3764 PROCEDURE convert_plan_type_name_to_id
3765 ( p_fin_plan_type_id IN pa_fin_plan_types_b.fin_plan_type_id%TYPE
3766 ,p_fin_plan_type_name IN pa_fin_plan_types_tl.name%TYPE
3767 ,x_fin_plan_type_id OUT NOCOPY pa_fin_plan_types_b.fin_plan_type_id%TYPE --File.Sql.39 bug 4440895
3768 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
3769 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
3770 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
3771 ) IS
3772
3773 CURSOR l_fin_plan_type_id_csr
3774 (p_fin_plan_type_id pa_fin_plan_types_b.fin_plan_type_id%TYPE)
3775 IS
3776 SELECT fin_plan_type_id
3777 FROM pa_fin_plan_types_b
3778 WHERE fin_plan_type_id=p_fin_plan_type_id;
3779
3780 l_msg_count NUMBER := 0;
3781 l_data VARCHAR2(2000);
3782 l_msg_data VARCHAR2(2000);
3783 l_msg_index_out NUMBER;
3784 l_debug_mode VARCHAR2(1);
3785
3786 l_debug_level2 CONSTANT NUMBER := 2;
3787 l_debug_level3 CONSTANT NUMBER := 3;
3788 l_debug_level4 CONSTANT NUMBER := 4;
3789 l_debug_level5 CONSTANT NUMBER := 5;
3790
3791 BEGIN
3792
3793 x_msg_count := 0;
3794 x_return_status := FND_API.G_RET_STS_SUCCESS;
3795 l_debug_mode := NVL(FND_PROFILE.value('PA_DEBUG_MODE'),'N');
3796
3797 IF p_pa_debug_mode = 'Y' THEN
3798 pa_debug.set_curr_function( p_function => 'convert_plan_type_name_to_id',
3799 p_debug_mode => l_debug_mode );
3800 END IF;
3801 -- If fin plan type id is passed. validate the fin plan type id. If it is not passed
3802 -- then convert name to fin plan type id
3803 IF (p_fin_plan_type_id IS NOT NULL) THEN
3804
3805 OPEN l_fin_plan_type_id_csr(p_fin_plan_type_id);
3806 FETCH l_fin_plan_type_id_csr INTO x_fin_plan_type_id;
3807
3808 IF(l_fin_plan_type_id_csr%NOTFOUND) THEN
3809
3810 PA_UTILS.ADD_MESSAGE
3811 (p_app_short_name => 'PA',
3812 p_msg_name => 'PA_FP_INVALID_PLAN_TYPE',
3813 p_token1 => 'PLAN_TYPE',
3814 p_value1 => p_fin_plan_type_id);
3815
3816 x_return_status := FND_API.G_RET_STS_ERROR;
3817 x_fin_plan_type_id:=NULL;
3818 CLOSE l_fin_plan_type_id_csr;
3819 IF l_debug_mode='Y' THEN
3820 pa_debug.g_err_stage := 'p_fin_plan_type_id is '||p_fin_plan_type_id ;
3821 pa_debug.write('convert_plan_type_name_to_id: ' || g_module_name
3822 ,pa_debug.g_err_stage,l_debug_level5);
3823 END IF;
3824 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
3825
3826 ELSE
3827
3828 close l_fin_plan_type_id_csr;
3829 x_fin_plan_type_id:=p_fin_plan_type_id;
3830
3831 END IF;
3832
3833 ELSIF ( p_fin_plan_type_name IS NOT NULL) THEN
3834
3835 x_fin_plan_type_id := fetch_plan_type_id(p_fin_plan_type_name);
3836 IF(x_fin_plan_type_id IS NULL) THEN
3837
3838 PA_UTILS.ADD_MESSAGE
3839 (p_app_short_name => 'PA',
3840 p_msg_name => 'PA_FP_INVALID_PLAN_TYPE',
3841 p_token1 => 'PLAN_TYPE',
3842 p_value1 => p_fin_plan_type_name);
3843
3844 x_return_status := FND_API.G_RET_STS_ERROR;
3845 IF l_debug_mode='Y' THEN
3846 pa_debug.g_err_stage := 'p_fin_plan_type_name is '||p_fin_plan_type_name ;
3847 pa_debug.write('convert_plan_type_name_to_id: ' || g_module_name
3848 ,pa_debug.g_err_stage,l_debug_level5);
3849 END IF;
3850 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
3851
3852 END IF;
3853 IF l_debug_mode='Y' THEN
3854 pa_debug.g_err_stage := 'x_fin_plan_type_id derived is '||x_fin_plan_type_id ;
3855 pa_debug.write('convert_plan_type_name_to_id: ' || g_module_name
3856 ,pa_debug.g_err_stage,l_debug_level5);
3857 END IF;
3858
3859
3860 ELSE
3861
3862 PA_UTILS.ADD_MESSAGE
3863 (p_app_short_name => 'PA',
3864 p_msg_name => 'PA_FP_INV_PARAM_PASSED');
3865
3866 x_return_status := FND_API.G_RET_STS_ERROR;
3867 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
3868 END IF;
3869
3870 IF l_debug_mode='Y' THEN
3871 pa_debug.g_err_stage := 'p_fin_plan_type_name is '||p_fin_plan_type_name ;
3872 pa_debug.write('convert_plan_type_name_to_id: ' || g_module_name
3873 ,pa_debug.g_err_stage,l_debug_level5);
3874 pa_debug.reset_curr_function;
3875 END IF;
3876 EXCEPTION
3877 WHEN PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc THEN
3878
3879 IF x_return_status IS NULL OR
3880 x_return_status = FND_API.G_RET_STS_SUCCESS THEN
3881 x_return_status := FND_API.G_RET_STS_ERROR;
3882 END IF;
3883 l_msg_count := FND_MSG_PUB.count_msg;
3884
3885 IF l_msg_count = 1 and x_msg_data IS NULL THEN
3886 PA_INTERFACE_UTILS_PUB.get_messages
3887 (p_encoded => FND_API.G_TRUE
3888 ,p_msg_index => 1
3889 ,p_msg_count => l_msg_count
3890 ,p_msg_data => l_msg_data
3891 ,p_data => l_data
3892 ,p_msg_index_out => l_msg_index_out);
3893 x_msg_data := l_data;
3894 x_msg_count := l_msg_count;
3895 ELSE
3896 x_msg_count := l_msg_count;
3897 END IF;
3898 IF l_debug_mode='Y' THEN
3899 pa_debug.reset_curr_function;
3900 END IF;
3901 RETURN;
3902
3903 WHEN OTHERS THEN
3904
3905 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3906 x_msg_count := 1;
3907 x_msg_data := SQLERRM;
3908
3909
3910 FND_MSG_PUB.add_exc_msg
3911 ( p_pkg_name => 'pa_fin_plan_pvt'
3912 ,p_procedure_name => 'convert_plan_type_name_to_id'
3913 ,p_error_text => x_msg_data);
3914
3915 IF l_debug_mode = 'Y' THEN
3916 pa_debug.g_err_stage:= 'Unexpected Error'||x_msg_data;
3917 pa_debug.write(g_module_name,pa_debug.g_err_stage,
3918 l_debug_level5);
3919 pa_debug.reset_curr_function;
3920 END IF;
3921 RAISE;
3922
3923 END convert_plan_type_name_to_id;
3924
3925 /*=====================================================================
3926 Procedure Name: DELETE_WP_OPTION
3927
3928 This procedure is added as part of FPM Development. Tracking Bug - 3354518.
3929
3930 Purpose: This api Deletes the proj fp options data pertaining
3931 to the workplan type attached to the project for
3932 the passed project id.
3933 Deletes data from the following tables -
3934 1) pa_proj_fp_options
3935 2) pa_fp_txn_currencies
3936 3) pa_proj_period_profiles
3937 4) pa_fp_upgrade_audit
3938
3939 Please note that all validations before calling this API shall be done
3940 in the calling entity.
3941
3942 Parameters:
3943 IN 1) p_project_id - project id.
3944 =======================================================================*/
3945 PROCEDURE Delete_wp_option
3946 (p_project_id IN pa_projects_all.project_id%TYPE
3947 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
3948 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
3949 ,x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
3950 IS
3951
3952 --Start of variables used for debugging
3953 l_msg_count NUMBER :=0;
3954 l_data VARCHAR2(2000);
3955 l_msg_data VARCHAR2(2000);
3956 l_error_msg_code VARCHAR2(30);
3957 l_msg_index_out NUMBER;
3958 l_return_status VARCHAR2(2000);
3959 l_debug_mode VARCHAR2(30);
3960 --End of variables used for debugging
3961
3962 l_proj_fp_options_id pa_proj_fp_options.proj_fp_options_id%TYPE;
3963 l_sv_id_tbl SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
3964 l_sv_id_count NUMBER;
3965
3966 cursor c_struct_ver(c_project_id pa_projects_all.project_id%TYPE) IS
3967 SELECT project_structure_version_id
3968 FROM pa_budget_versions
3969 WHERE project_id = c_project_id
3970 AND nvl(wp_version_flag,'N') = 'Y';
3971
3972 BEGIN
3973
3974 SAVEPOINT DELETE_WP_OPTION_SAVE;
3975
3976 fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
3977 l_debug_mode := NVL(l_debug_mode, 'N');
3978 x_msg_count := 0;
3979 x_return_status := FND_API.G_RET_STS_SUCCESS;
3980 IF p_pa_debug_mode = 'Y' THEN
3981 PA_DEBUG.Set_Curr_Function( p_function => 'PA_FIN_PLAN_PVT.Delete_wp_option',
3982 p_debug_mode => l_debug_mode );
3983 END IF;
3984 ---------------------------------------------------------------
3985 -- validating input parameter p_project_id.
3986 -- p_project_id cannot be passed as null.
3987 ---------------------------------------------------------------
3988 IF l_debug_mode = 'Y' THEN
3989 pa_debug.g_err_stage:='Validating input parameters';
3990 pa_debug.write('Delete_wp_option: ' || g_module_name,pa_debug.g_err_stage,3);
3991 END IF;
3992
3993 IF (p_project_id IS NULL)
3994 THEN
3995
3996 IF l_debug_mode = 'Y' THEN
3997 pa_debug.write('Delete_wp_options Project Id is null: ' || g_module_name,pa_debug.g_err_stage,5);
3998 END IF;
3999
4000 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
4001 p_msg_name => 'PA_FP_INV_PARAM_PASSED');
4002
4003 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
4004
4005 END IF;
4006
4007 ---------------------------------------------------------------
4008 --Fetch proj_fp_options_id
4009 ---------------------------------------------------------------
4010
4011 IF l_debug_mode = 'Y' THEN
4012 pa_debug.g_err_stage:='Fetching proj_fp_options_id ';
4013 pa_debug.write('Delete_wp_option: ' || g_module_name,pa_debug.g_err_stage,3);
4014 END IF;
4015
4016 SELECT pfo.proj_fp_options_id
4017 INTO l_proj_fp_options_id
4018 FROM pa_proj_fp_options pfo
4019 ,pa_fin_plan_types_b pft
4020 WHERE pfo.project_id = p_project_id
4021 AND pfo.fin_plan_type_id = pft.fin_plan_type_id
4022 AND pfo.fin_plan_option_level_code = PA_FP_CONSTANTS_PKG.G_OPTION_LEVEL_PLAN_TYPE
4023 AND nvl(pft.use_for_workplan_flag,'N') = 'Y';
4024
4025 -----------------------------------------------------------------
4026 -- Fetching the workplan structure version ids for the project id
4027 -- passed into the PLSql table l_sv_id_tbl and then calling API
4028 -- Delete_wp_budget_versions
4029 -----------------------------------------------------------------
4030
4031 IF l_debug_mode = 'Y' THEN
4032 pa_debug.g_err_stage:='Fetching the workplan structure ids for the project id';
4033 pa_debug.write('Delete_wp_option: ' || g_module_name,pa_debug.g_err_stage,3);
4034 END IF;
4035
4036 OPEN c_struct_ver(p_project_id);
4037
4038 FETCH c_struct_ver BULK COLLECT INTO l_sv_id_tbl;
4039 IF c_struct_ver%NOTFOUND THEN
4040 IF p_pa_debug_mode = 'Y' THEN
4041 pa_debug.g_err_stage:= 'No Structure versions for the project_id passed - project_id: '||p_project_id;
4042 pa_debug.write(g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL5);
4043 END IF;
4044 END IF;
4045 CLOSE c_struct_ver;
4046
4047 l_sv_id_count := l_sv_id_tbl.count;
4048
4049 IF l_sv_id_count > 0 THEN
4050
4051 IF l_debug_mode = 'Y' THEN
4052 pa_debug.g_err_stage:='Deleting all version data for the wp structure version ids pertaining to project_id:'||p_project_id;
4053 pa_debug.write('Delete_wp_option: ' || g_module_name,pa_debug.g_err_stage,3);
4054 END IF;
4055
4056 Delete_wp_budget_versions
4057 (p_struct_elem_version_id_tbl => l_sv_id_tbl
4058 ,x_return_status => l_return_status
4059 ,x_msg_count => l_msg_count
4060 ,x_msg_data => l_msg_data);
4061
4062 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4063
4064 IF l_debug_mode = 'Y' THEN
4065 pa_debug.g_err_stage:='Call to Delete_wp_budget_versions is returning error status';
4066 pa_debug.write('Delete_wp_option: ' || g_module_name,pa_debug.g_err_stage,3);
4067 END IF;
4068 RAISE Delete_Ver_Exc_PVT;
4069 END IF;
4070
4071 END IF;
4072
4073 -- Bug 5743297: Moved the following DELETE statements which were before the Delete_wp_budget_versions API
4074 -- call to here for avoiding NO_DATA_FOUND.
4075 ---------------------------------------------------------------
4076 --Deleting data from respective tables
4077 ---------------------------------------------------------------
4078
4079 IF l_debug_mode = 'Y' THEN
4080 pa_debug.g_err_stage:='Deleting data from respective tables';
4081 pa_debug.write('Delete_wp_option: ' || g_module_name,pa_debug.g_err_stage,3);
4082 END IF;
4083
4084 -- delete from pa_proj_fp_options table
4085 DELETE FROM pa_proj_fp_options WHERE proj_fp_options_id = l_proj_fp_options_id;
4086
4087 -- delete from pa_fp_txn_currencies
4088 DELETE FROM pa_fp_txn_currencies WHERE proj_fp_options_id = l_proj_fp_options_id;
4089
4090 -- delete from pa_fp_upgrade_audit
4091 DELETE FROM pa_fp_upgrade_audit WHERE proj_fp_options_id = l_proj_fp_options_id;
4092
4093 IF l_debug_mode = 'Y' THEN
4094 pa_debug.reset_curr_function;
4095 END IF;
4096 EXCEPTION
4097 WHEN NO_DATA_FOUND THEN
4098 IF l_debug_mode = 'Y' THEN
4099 pa_debug.reset_curr_function;
4100 END IF;
4101 WHEN Delete_Ver_Exc_PVT THEN
4102 ROLLBACK TO SAVEPOINT PA_FP_PUB_DELETE_VER;
4103 l_msg_count := FND_MSG_PUB.count_msg;
4104 IF l_msg_count = 1 THEN
4105 PA_INTERFACE_UTILS_PUB.get_messages
4106 (p_encoded => FND_API.G_TRUE
4107 ,p_msg_index => 1
4108 ,p_msg_count => l_msg_count
4109 ,p_msg_data => l_msg_data
4110 ,p_data => l_data
4111 ,p_msg_index_out => l_msg_index_out);
4112 x_msg_data := l_data;
4113 x_msg_count := l_msg_count;
4114 ELSE
4115 x_msg_count := l_msg_count;
4116 END IF;
4117
4118 x_return_status := FND_API.G_RET_STS_ERROR;
4119
4120 IF l_debug_mode = 'Y' THEN
4121 pa_debug.g_err_stage:='Delete_wp_budget_versions returned error';
4122 pa_debug.write('Delete_wp_option: ' || g_module_name,pa_debug.g_err_stage,5);
4123 pa_debug.reset_curr_function;
4124 END IF;
4125
4126 WHEN PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc THEN
4127 l_msg_count := FND_MSG_PUB.count_msg;
4128 IF l_msg_count = 1 THEN
4129 PA_INTERFACE_UTILS_PUB.get_messages
4130 (p_encoded => FND_API.G_TRUE
4131 ,p_msg_index => 1
4132 ,p_msg_count => l_msg_count
4133 ,p_msg_data => l_msg_data
4134 ,p_data => l_data
4135 ,p_msg_index_out => l_msg_index_out);
4136 x_msg_data := l_data;
4137 x_msg_count := l_msg_count;
4138 ELSE
4139 x_msg_count := l_msg_count;
4140 END IF;
4141
4142 x_return_status := FND_API.G_RET_STS_ERROR;
4143
4144 IF l_debug_mode = 'Y' THEN
4145 pa_debug.g_err_stage:='Invalid Arguments Passed';
4146 pa_debug.write('Delete_wp_option: ' || g_module_name,pa_debug.g_err_stage,5);
4147 pa_debug.reset_curr_function;
4148 END IF;
4149
4150 WHEN Others THEN
4151 ROLLBACK TO SAVEPOINT DELETE_WP_OPTION_SAVE;
4152 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4153 x_msg_count := 1;
4154 x_msg_data := SQLERRM;
4155 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_FIN_PLAN_PVT'
4156 ,p_procedure_name => 'Delete_wp_option');
4157
4158 IF l_debug_mode = 'Y' THEN
4159 pa_debug.g_err_stage:='Unexpected Error'||SQLERRM;
4160 pa_debug.write('Delete_wp_option: ' || g_module_name,pa_debug.g_err_stage,5);
4161 pa_debug.reset_curr_function;
4162 END IF;
4163 RAISE;
4164
4165 END Delete_wp_option;
4166
4167 /*=====================================================================
4168 * Procedure Name: DELETE_WP_BUDGET_VERSIONS
4169 * This procedure is added as part of FPM Development. Trackinb Bug - 3354518.
4170 * Purpose: This API deletes the budget_versions for all the
4171 * workplan structure version ids passed.
4172 * Parameters: 1) p_struct_elem_version_id_tbl IN SYSTEM.pa_num_tbl_type
4173 *=======================================================================*/
4174 PROCEDURE Delete_wp_budget_versions
4175 (p_struct_elem_version_id_tbl IN SYSTEM.pa_num_tbl_type
4176 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
4177 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
4178 ,x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
4179 IS
4180
4181 --Start of variables used for debugging
4182 l_msg_count NUMBER :=0;
4183 l_data VARCHAR2(2000);
4184 l_msg_data VARCHAR2(2000);
4185 l_error_msg_code VARCHAR2(30);
4186 l_msg_index_out NUMBER;
4187 l_return_status VARCHAR2(2000);
4188 l_debug_mode VARCHAR2(30);
4189 --End of variables used for debugging
4190
4191 l_sv_id_tbl_count NUMBER;
4192
4193 cursor c_budget_ver(c_structure_version_id pa_budget_versions.project_structure_version_id%TYPE) IS
4194 SELECT budget_version_id,record_version_number,project_id
4195 FROM pa_budget_versions
4196 WHERE project_structure_version_id = nvl(c_structure_version_id,-99)
4197 AND nvl(wp_version_flag,'N')='Y';
4198
4199 BEGIN
4200 SAVEPOINT PA_FP_PUB_DELETE_VER;
4201
4202 fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
4203 l_debug_mode := NVL(l_debug_mode, 'N');
4204 x_msg_count := 0;
4205 x_return_status := FND_API.G_RET_STS_SUCCESS;
4206 IF p_pa_debug_mode = 'Y' THEN
4207 PA_DEBUG.Set_Curr_Function( p_function => 'PA_FIN_PLAN_PVT.Delete_wp_bugdet_versions',
4208 p_debug_mode => l_debug_mode );
4209 END IF;
4210
4211 ------------------------------------------------------------------------
4212 -- Check if the PLSql Table p_struct_elem_version_id_tbl has no records.
4213 ------------------------------------------------------------------------
4214
4215 IF l_debug_mode = 'Y' THEN
4216 pa_debug.g_err_stage:='Checking for existence of data in PLsql table p_struct_elem_version_id_tbl';
4217 pa_debug.write('Delete_wp_bugdet_versions: ' || g_module_name,pa_debug.g_err_stage,3);
4218 END IF;
4219
4220 l_sv_id_tbl_count := p_struct_elem_version_id_tbl.COUNT;
4221
4222 IF l_sv_id_tbl_count = 0 THEN
4223 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
4224 END IF;
4225
4226
4227 ------------------------------------------------------------------
4228 -- Derive budget_version_id based on structure_version_id passed
4229 ------------------------------------------------------------------
4230
4231 --------------------------------------------------
4232 -- Loop through all the structure_verion_id passed
4233 --------------------------------------------------
4234 FOR i in p_struct_elem_version_id_tbl.first .. p_struct_elem_version_id_tbl.last LOOP --LoopA
4235
4236 IF l_debug_mode = 'Y' THEN
4237 pa_debug.g_err_stage:='Outer loop : '||i;
4238 pa_debug.write('Delete_wp_bugdet_versions: ' || g_module_name,pa_debug.g_err_stage,3);
4239 END IF;
4240
4241 -----------------------------------------------------------
4242 -- For each stucture_version_id fetch the budget_version_id
4243 -----------------------------------------------------------
4244 FOR c1 IN c_budget_ver(p_struct_elem_version_id_tbl(i)) LOOP --LoopB
4245
4246 ------------------------------------------------------------------
4247 -- If no budget versions exist for the structure_version_id passed
4248 -- iterate through the out loop LoopA
4249 ------------------------------------------------------------------
4250
4251 IF c_budget_ver%NOTFOUND THEN
4252 IF p_pa_debug_mode = 'Y' THEN
4253 pa_debug.g_err_stage:= 'Could not fetch budget_version_id !!!...';
4254 pa_debug.write(g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL5);
4255 END IF;
4256 EXIT; -- Come out of LoopB, Jump to next iteration of LoopA
4257 END IF;
4258
4259 ------------------------------------------------------------------
4260 -- Call Delete_Version for Version_id to delete all version
4261 -- data for the budget_version_id fetched.
4262 ------------------------------------------------------------------
4263 pa_fin_plan_pub.Delete_Version
4264 (p_budget_version_id => c1.budget_version_id,
4265 p_record_version_number => c1.record_version_number,
4266 p_context => PA_FP_CONSTANTS_PKG.G_CALLING_MODULE_WORKPLAN,
4267 p_project_id => c1.project_id,
4268 x_return_Status => l_return_Status,
4269 x_msg_count => l_msg_count,
4270 x_msg_data => l_msg_data);
4271
4272 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4273 RAISE Delete_Ver_Exc_PVT;
4274 END IF;
4275
4276 END LOOP; -- LoopB Closed
4277 END LOOP; -- LoopA Closed
4278 IF p_pa_debug_mode = 'Y' THEN
4279 pa_debug.reset_curr_function;
4280 END IF;
4281 EXCEPTION
4282
4283 WHEN PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc THEN
4284 l_msg_count := FND_MSG_PUB.count_msg;
4285 IF l_msg_count = 1 THEN
4286 PA_INTERFACE_UTILS_PUB.get_messages
4287 (p_encoded => FND_API.G_TRUE
4288 ,p_msg_index => 1
4289 ,p_msg_count => l_msg_count
4290 ,p_msg_data => l_msg_data
4291 ,p_data => l_data
4292 ,p_msg_index_out => l_msg_index_out);
4293 x_msg_data := l_data;
4294 x_msg_count := l_msg_count;
4295 ELSE
4296 x_msg_count := l_msg_count;
4297 END IF;
4298
4299 x_return_status := FND_API.G_RET_STS_ERROR;
4300
4301 IF l_debug_mode = 'Y' THEN
4302 pa_debug.g_err_stage:='Invalid Arguments Passed';
4303 pa_debug.write('Delete_wp_budget_version: ' || g_module_name,pa_debug.g_err_stage,5);
4304 pa_debug.reset_curr_function;
4305 END IF;
4306
4307 WHEN Delete_Ver_Exc_PVT THEN
4308 ROLLBACK TO SAVEPOINT PA_FP_PUB_DELETE_VER;
4309 l_msg_count := FND_MSG_PUB.count_msg;
4310 IF l_msg_count = 1 THEN
4311 PA_INTERFACE_UTILS_PUB.get_messages
4312 (p_encoded => FND_API.G_TRUE
4313 ,p_msg_index => 1
4314 ,p_msg_count => l_msg_count
4315 ,p_msg_data => l_msg_data
4316 ,p_data => l_data
4317 ,p_msg_index_out => l_msg_index_out);
4318 x_msg_data := l_data;
4319 x_msg_count := l_msg_count;
4320 ELSE
4321 x_msg_count := l_msg_count;
4322 END IF;
4323 x_return_status := FND_API.G_RET_STS_ERROR;
4324
4325 IF l_debug_mode = 'Y' THEN
4326 pa_debug.g_err_stage:='Delete_version_helper returned error';
4327 pa_debug.write('Delete_wp_options: ' || g_module_name,pa_debug.g_err_stage,5);
4328 pa_debug.reset_curr_function;
4329 END IF;
4330
4331 WHEN Others THEN
4332 ROLLBACK TO SAVEPOINT DELETE_WP_OPTION_SAVE;
4333 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4334 x_msg_count := 1;
4335 x_msg_data := SQLERRM;
4336 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_FIN_PLAN_PVT'
4337 ,p_procedure_name => 'Delete_wp_budget_versions');
4338
4339 IF l_debug_mode = 'Y' THEN
4340 pa_debug.g_err_stage:='Unexpected Error'||SQLERRM;
4341 pa_debug.write('Delete_wp_budget_versions: ' || g_module_name,pa_debug.g_err_stage,5);
4342 pa_debug.reset_curr_function;
4343 END IF;
4344 RAISE;
4345
4346 END Delete_wp_budget_versions;
4347
4348 PROCEDURE ADD_FIN_PLAN_LINES
4349 ( -- Bug Fix: 4569365. Removed MRC code.
4350 -- p_calling_context IN pa_mrc_finplan.g_calling_module%TYPE
4351 p_calling_context IN VARCHAR2
4352 ,p_fin_plan_version_id IN pa_budget_versions.budget_version_id%TYPE
4353 ,p_finplan_lines_tab IN pa_fin_plan_pvt.budget_lines_tab
4354 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
4355 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
4356 ,x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
4357 IS
4358
4359 l_msg_count NUMBER := 0;
4360 l_data VARCHAR2(2000);
4361 l_msg_data VARCHAR2(2000);
4362 l_error_msg_code VARCHAR2(30);
4363 l_msg_index_out NUMBER;
4364 l_return_status VARCHAR2(1);
4365 l_debug_mode VARCHAR2(30);
4366 l_resource_name VARCHAR2(30);
4367 l_err_code NUMBER:=0;
4368 l_debug_level3 CONSTANT NUMBER := 3;
4369 l_debug_level5 CONSTANT NUMBER := 5;
4370
4371 l_resource_assignment_tab SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
4372 l_delete_budget_lines_tab SYSTEM.pa_varchar2_1_tbl_type := SYSTEM.pa_varchar2_1_tbl_type();
4373 l_spread_amts_flag_tab SYSTEM.pa_varchar2_1_tbl_type := SYSTEM.pa_varchar2_1_tbl_type();
4374 l_line_start_date_tab SYSTEM.pa_date_tbl_type := SYSTEM.pa_date_tbl_type();
4375 l_line_end_date_tab SYSTEM.pa_date_tbl_type := SYSTEM.pa_date_tbl_type();
4376 i NUMBER;
4377 l_lines_count NUMBER; -- Bug 3639983
4378 l_module_name VARCHAR2(30) := 'pa.plsql.PA_FIN_PLAN_PVT';
4379 l_project_id pa_budget_versions.project_id%TYPE;
4380 l_fp_version_ids SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
4381 l_txn_currency_code_tab SYSTEM.pa_varchar2_15_tbl_type := SYSTEM.pa_varchar2_15_tbl_type();
4382 l_txn_currency_override_tab SYSTEM.pa_varchar2_15_tbl_type := SYSTEM.pa_varchar2_15_tbl_type();
4383 l_total_qty_tab SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
4384 l_total_raw_cost_tab SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
4385 l_total_burdened_cost_tab SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
4386 l_total_revenue_tab SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
4387 l_number_null_tab SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type(); --bug 3825873
4388
4389 -- bug 4221650: added the following
4390 l_ver_time_phased_code pa_proj_fp_options.cost_time_phased_code%TYPE;
4391
4392 CURSOR get_proj_id_csr IS
4393 SELECT project_id FROM pa_budget_versions
4394 WHERE budget_version_id = p_fin_plan_version_id ;
4395
4396 BEGIN
4397
4398 x_msg_count := 0;
4399 x_return_status := FND_API.G_RET_STS_SUCCESS;
4400 fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
4401 l_debug_mode := NVL(l_debug_mode, 'Y');
4402
4403 IF p_pa_debug_mode = 'Y' THEN
4404 PA_DEBUG.Set_Curr_Function( p_function => 'PA_FIN_PLAN_PVT.ADD_FIN_PLAN_LINES',
4405 p_debug_mode => l_debug_mode );
4406 END IF;
4407 l_lines_count := p_finplan_lines_tab.COUNT;
4408 IF l_debug_mode = 'Y' THEN
4409 pa_debug.g_err_stage:='Validating input parameter - plan lines count cannot be 0';
4410 pa_debug.write('ADD_FIN_PLAN_LINES: ' || g_module_name,pa_debug.g_err_stage,3);
4411 END IF;
4412
4413 -- Change of Code for Bug 3639983 Starts Here
4414 IF l_lines_count = 0 THEN
4415 IF l_debug_mode = 'Y' THEN
4416 pa_debug.g_err_stage:='No Lines to be added - Returning';
4417 pa_debug.write('ADD_FIN_PLAN_LINES: ' || g_module_name,pa_debug.g_err_stage,3);
4418 pa_debug.reset_curr_function;
4419 END IF;
4420 RETURN;
4421 END IF;
4422 -- Change of Code for Bug 3639983 Ends Here
4423
4424 IF l_debug_mode = 'Y' THEN
4425 pa_debug.g_err_stage:='Calling CREATE_FINPLAN_LINES';
4426 pa_debug.write('ADD_FIN_PLAN_LINES: ' || g_module_name,pa_debug.g_err_stage,3);
4427 END IF;
4428
4429 --Call to api PA_FIN_PLAN_PVT.CREATE_FINPLAN_LINES
4430
4431 IF ( nvl(p_finplan_lines_tab.last,0) > 0 ) THEN
4432
4433 PA_FIN_PLAN_PVT.CREATE_FINPLAN_LINES
4434 ( p_calling_context => p_calling_context
4435 ,p_fin_plan_version_id => p_fin_plan_version_id
4436 ,p_budget_lines_tab => p_finplan_lines_tab
4437 ,x_return_status => x_return_status
4438 ,x_msg_count => x_msg_count
4439 ,x_msg_data => x_msg_data );
4440
4441 IF l_debug_mode = 'Y' THEN
4442 pa_debug.g_err_stage:='Return Status After CREATE_FINPLAN_LINES :'||x_return_status;
4443 pa_debug.write('ADD_FIN_PLAN_LINES: ' || g_module_name,pa_debug.g_err_stage,3);
4444 END IF;
4445
4446 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
4447
4448 IF P_PA_DEBUG_MODE = 'Y' THEN
4449 pa_debug.g_err_stage:= 'Error Calling CREATE_FINPLAN_LINES';
4450 pa_debug.write('CREATE_DRAFT: '||g_module_name,pa_debug.g_err_stage,5);
4451 END IF;
4452
4453 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
4454
4455 END IF;
4456
4457 END IF;
4458
4459 -- Change of Code for Bug 3639983 Starts Here
4460 IF l_debug_mode = 'Y' THEN
4461 pa_debug.g_err_stage:='Extending lenght of all local empty table to l_lines_count';
4462 pa_debug.write('ADD_FIN_PLAN_LINES: ' || g_module_name,pa_debug.g_err_stage,3);
4463 END IF;
4464
4465 l_line_start_date_tab.extend(l_lines_count);
4466 l_line_end_date_tab.extend(l_lines_count);
4467 l_total_qty_tab.extend(l_lines_count);
4468 l_txn_currency_code_tab.extend(l_lines_count);
4469 l_total_raw_cost_tab.extend(l_lines_count);
4470 l_total_burdened_cost_tab.extend(l_lines_count);
4471 l_total_revenue_tab.extend(l_lines_count);
4472 l_resource_assignment_tab.extend(l_lines_count);
4473 l_delete_budget_lines_tab.extend(l_lines_count);
4474 l_spread_amts_flag_tab.extend(l_lines_count);
4475 l_number_null_tab.extend(l_lines_count); -- bug 3825873
4476
4477 -- Change of Code for Bug 3639983 Ends Here
4478 -- Change of Code for Bug 3732414 Starts Here
4479 SELECT start_date,
4480 end_date,
4481 quantity,
4482 txn_currency_code,
4483 txn_raw_cost,
4484 txn_burdened_cost,
4485 txn_revenue,
4486 resource_assignment_id,
4487 'N' delete_budget_lines,
4488 'N' spread_amouts,
4489 NULL
4490 BULK COLLECT INTO
4491 l_line_start_date_tab
4492 ,l_line_end_date_tab
4493 ,l_total_qty_tab
4494 ,l_txn_currency_code_tab
4495 ,l_total_raw_cost_tab
4496 ,l_total_burdened_cost_tab
4497 ,l_total_revenue_tab
4498 ,l_resource_assignment_tab
4499 ,l_delete_budget_lines_tab
4500 ,l_spread_amts_flag_tab
4501 ,l_number_null_tab -- bug 3825873
4502 FROM pa_fp_rollup_tmp;
4503
4504 -- Change of Code for Bug 3732414 Ends Here
4505
4506 IF l_debug_mode = 'Y' THEN
4507 pa_debug.g_err_stage:='Fetching project Id from get_proj_id_csr';
4508 pa_debug.write('ADD_FIN_PLAN_LINES: ' || g_module_name,pa_debug.g_err_stage,3);
4509 END IF;
4510
4511 OPEN get_proj_id_csr ;
4512 FETCH get_proj_id_csr into l_project_id ;
4513 CLOSE get_proj_id_csr;
4514
4515 -- Calling PA_FP_CALC_PLAN_PKG.calculate api
4516 IF l_debug_mode = 'Y' THEN
4517 pa_debug.g_err_stage:='Calling Calculate API';
4518 pa_debug.write('ADD_FIN_PLAN_LINES: ' || g_module_name,pa_debug.g_err_stage,3);
4519
4520 pa_debug.g_err_stage:='Calling Calculate API l_project_id'||l_project_id;
4521 pa_debug.write('ADD_FIN_PLAN_LINES: ' || g_module_name,pa_debug.g_err_stage,3);
4522
4523 pa_debug.g_err_stage:='Calling Calculate API p_fin_plan_version_id'||p_fin_plan_version_id;
4524 pa_debug.write('ADD_FIN_PLAN_LINES: ' || g_module_name,pa_debug.g_err_stage,3);
4525
4526 pa_debug.g_err_stage:='Calling Calculate API l_resource_assignment_tab'||l_resource_assignment_tab.COUNT;
4527 pa_debug.write('ADD_FIN_PLAN_LINES: ' || g_module_name,pa_debug.g_err_stage,3);
4528
4529 pa_debug.g_err_stage:='Calling Calculate API l_delete_budget_lines_tab'||l_delete_budget_lines_tab.COUNT;
4530 pa_debug.write('ADD_FIN_PLAN_LINES: ' || g_module_name,pa_debug.g_err_stage,3);
4531
4532 pa_debug.g_err_stage:='Calling Calculate API l_spread_amts_flag_tab'||l_spread_amts_flag_tab.COUNT;
4533 pa_debug.write('ADD_FIN_PLAN_LINES: ' || g_module_name,pa_debug.g_err_stage,3);
4534
4535 pa_debug.g_err_stage:='Calling Calculate API l_txn_currency_code_tab'||l_txn_currency_code_tab.COUNT;
4536 pa_debug.write('ADD_FIN_PLAN_LINES: ' || g_module_name,pa_debug.g_err_stage,3);
4537
4538 END IF;
4539
4540 -- bug 4221650: checking for the time phased code of the version to call
4541 -- calculate api in either 'RESOURCE_ASSIGNMENT' or in 'BUDGET_LINE' mode
4542
4543 l_ver_time_phased_code := PA_FIN_PLAN_UTILS.get_time_phased_code (p_fin_plan_version_id);
4544
4545 IF NOT l_ver_time_phased_code = 'N' THEN
4546
4547 IF l_debug_mode = 'Y' THEN
4548 pa_debug.g_err_stage:=' Calling Calculate in BUDGET_LINE mode';
4549 pa_debug.write('ADD_FIN_PLAN_LINES: ' || g_module_name,pa_debug.g_err_stage,3);
4550 END IF;
4551
4552 -- bug 3825873 17-JUL-2004 Corrected the input parameters
4553 /*Bug 4224464 Added the if condition to ditinguish the call to calculate API in AMG flow from other flows.
4554 We are passing the parameter p_calling_module as G_AMG_API if its a AMG flow. This parameter would be internally used by
4555 calculate API to skip the call to client extensions for AMG flows.
4556 Also added the parameter so as not to delete the PA_FP_SPREAD_CALC_TMP1 table in calcualte API . This parameter whould be
4557 passed in all the flows so as not to delete the PA_FP_SPREAD_CALC_TMP1 table.*/
4558 IF(p_calling_context = PA_FP_CONSTANTS_PKG.G_AMG_API)
4559 THEN
4560 PA_FP_CALC_PLAN_PKG.calculate
4561 ( p_project_id => l_project_id
4562 ,p_budget_version_id => p_fin_plan_version_id
4563 ,p_source_context => 'BUDGET_LINE'
4564 ,p_refresh_rates_flag => 'N'
4565 ,p_refresh_conv_rates_flag => 'N'
4566 ,p_conv_rates_required_flag => 'Y'
4567 ,p_spread_required_flag => 'Y'
4568 ,p_rollup_required_flag => 'Y'
4569 ,p_mass_adjust_flag => 'N'
4570 ,p_resource_assignment_tab => l_resource_assignment_tab
4571 ,p_delete_budget_lines_tab => l_delete_budget_lines_tab
4572 ,p_spread_amts_flag_tab => l_spread_amts_flag_tab
4573 ,p_txn_currency_code_tab => l_txn_currency_code_tab
4574 ,p_total_qty_tab => l_total_qty_tab
4575 ,p_addl_qty_tab => l_number_null_tab
4576 ,p_total_raw_cost_tab => l_total_raw_cost_tab
4577 ,p_addl_raw_cost_tab => l_number_null_tab
4578 ,p_total_burdened_cost_tab => l_total_burdened_cost_tab
4579 ,p_addl_burdened_cost_tab => l_number_null_tab
4580 ,p_total_revenue_tab => l_total_revenue_tab
4581 ,p_addl_revenue_tab => l_number_null_tab
4582 ,p_line_start_date_tab => l_line_start_date_tab
4583 ,p_line_end_date_tab => l_line_end_date_tab
4584 ,p_raw_cost_rate_tab => l_number_null_tab
4585 ,p_rw_cost_rate_override_tab => l_number_null_tab
4586 ,p_b_cost_rate_tab => l_number_null_tab
4587 ,p_b_cost_rate_override_tab => l_number_null_tab
4588 ,p_bill_rate_tab => l_number_null_tab
4589 ,p_bill_rate_override_tab => l_number_null_tab
4590 ,p_del_spread_calc_tmp1_flg => 'N' /* Bug: 4309290.Added the parameter to identify if
4591 PA_FP_SPREAD_CALC_TMP1 is to be deleted or not. Frm AMG flow
4592 we will pass N and for other calls to calculate api it would
4593 be yes */
4594 ,p_calling_module => PA_FP_CONSTANTS_PKG.G_AMG_API
4595 ,x_return_status => x_return_status
4596 ,x_msg_count => x_msg_count
4597 ,x_msg_data => x_msg_data);
4598 ELSE
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'
4630 ,x_return_status => x_return_status
4631 ,x_msg_count => x_msg_count
4632 ,x_msg_data => x_msg_data);
4633 END IF;
4634 ELSE
4635 -- bug 4221650:
4636 IF l_debug_mode = 'Y' THEN
4637 pa_debug.g_err_stage:=' Calling Calculate in RESOURCE_ASSIGNMENT mode';
4638 pa_debug.write('ADD_FIN_PLAN_LINES: ' || g_module_name,pa_debug.g_err_stage,3);
4639 END IF;
4640
4641 /*Bug 4224464 Added the if condition to ditinguish the call to calculate API in AMG flow from other flows.
4642 We are passing the parameter p_calling_module as G_AMG_API if its a AMG flow. This parameter would be internally used by
4643 calculate API to skip the call to client extensions for AMG flows.
4644 Also added the parameter so as not to delete the PA_FP_SPREAD_CALC_TMP1 table in calcualte API . This parameter whould be
4645 passed in all the flows so as not to delete the PA_FP_SPREAD_CALC_TMP1 table.*/
4646 IF(p_calling_context = PA_FP_CONSTANTS_PKG.G_AMG_API)
4647 THEN
4648 PA_FP_CALC_PLAN_PKG.calculate
4649 ( p_project_id => l_project_id
4650 ,p_budget_version_id => p_fin_plan_version_id
4651 ,p_source_context => 'RESOURCE_ASSIGNMENT'
4652 ,p_resource_assignment_tab => l_resource_assignment_tab
4653 ,p_spread_amts_flag_tab => l_spread_amts_flag_tab
4654 ,p_txn_currency_code_tab => l_txn_currency_code_tab
4655 ,p_total_qty_tab => l_total_qty_tab
4656 ,p_total_raw_cost_tab => l_total_raw_cost_tab
4657 ,p_total_burdened_cost_tab => l_total_burdened_cost_tab
4658 ,p_total_revenue_tab => l_total_revenue_tab
4659 ,p_line_start_date_tab => l_line_start_date_tab
4660 ,p_line_end_date_tab => l_line_end_date_tab
4661 ,p_calling_module => PA_FP_CONSTANTS_PKG.G_AMG_API
4662 ,p_del_spread_calc_tmp1_flg => 'N'
4663 ,x_return_status => x_return_status
4664 ,x_msg_count => x_msg_count
4665 ,x_msg_data => x_msg_data);
4666 ELSE
4667 PA_FP_CALC_PLAN_PKG.calculate
4668 ( p_project_id => l_project_id
4669 ,p_budget_version_id => p_fin_plan_version_id
4670 ,p_source_context => 'RESOURCE_ASSIGNMENT'
4671 ,p_resource_assignment_tab => l_resource_assignment_tab
4672 ,p_spread_amts_flag_tab => l_spread_amts_flag_tab
4673 ,p_txn_currency_code_tab => l_txn_currency_code_tab
4674 ,p_total_qty_tab => l_total_qty_tab
4675 ,p_total_raw_cost_tab => l_total_raw_cost_tab
4676 ,p_total_burdened_cost_tab => l_total_burdened_cost_tab
4677 ,p_total_revenue_tab => l_total_revenue_tab
4678 ,p_line_start_date_tab => l_line_start_date_tab
4679 ,p_line_end_date_tab => l_line_end_date_tab
4680 ,p_del_spread_calc_tmp1_flg => 'N'
4681 ,x_return_status => x_return_status
4682 ,x_msg_count => x_msg_count
4683 ,x_msg_data => x_msg_data);
4684 END IF; -- IF(p_calling_context = PA_FP_CONSTANTS_PKG.G_AMG_API)
4685
4686 END IF;
4687
4688 IF l_debug_mode = 'Y' THEN
4689 pa_debug.g_err_stage:='Return Status After CALCULATE :'||x_return_status;
4690 pa_debug.write('ADD_FIN_PLAN_LINES: ' || g_module_name,pa_debug.g_err_stage,3);
4691 END IF;
4692
4693 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4694
4695 IF l_debug_mode = 'Y' THEN
4696 pa_debug.g_err_stage:='Called API PA_FP_CALC_PLAN_PKG.calculate returned error';
4697 pa_debug.write(l_module_name,pa_debug.g_err_stage, l_debug_level3);
4698 END IF;
4699
4700 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
4701
4702 END IF;
4703 IF l_debug_mode = 'Y' THEN
4704 pa_debug.g_err_stage:='Exiting ADD FIN PLAN LINES x_return_status: '||x_return_status;
4705 pa_debug.write('ADD_FIN_PLAN_LINES: ' || g_module_name,pa_debug.g_err_stage,3);
4706 pa_debug.reset_curr_function;
4707 END IF;
4708 EXCEPTION
4709
4710 WHEN PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc THEN
4711
4712 x_return_status := FND_API.G_RET_STS_ERROR;
4713 l_msg_count := FND_MSG_PUB.count_msg;
4714
4715 IF l_msg_count = 1 THEN
4716 PA_INTERFACE_UTILS_PUB.get_messages
4717 (p_encoded => FND_API.G_TRUE
4718 ,p_msg_index => 1
4719 ,p_msg_count => l_msg_count
4720 ,p_msg_data => l_msg_data
4721 ,p_data => l_data
4722 ,p_msg_index_out => l_msg_index_out);
4723 x_msg_data := l_data;
4724 x_msg_count := l_msg_count;
4725 ELSE
4726 x_msg_count := l_msg_count;
4727 END IF;
4728
4729 pa_debug.g_err_stage:= 'Invalid Arguments Passed';
4730
4731 IF P_PA_DEBUG_MODE = 'Y' THEN
4732 pa_debug.write('ADD_FIN_PLAN_LINES: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL5);
4733 pa_debug.reset_curr_function;
4734 END IF;
4735 RETURN;
4736
4737 WHEN others THEN
4738
4739 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4740 x_msg_count := 1;
4741 x_msg_data := SQLERRM;
4742
4743 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'pa_fin_plan_pvt'
4744 ,p_procedure_name => 'ADD_FIN_PLAN_LINES');
4745 pa_debug.g_err_stage:= 'Unexpected Error'||SQLERRM;
4746
4747 IF P_PA_DEBUG_MODE = 'Y' THEN
4748 pa_debug.write('ADD_FIN_PLAN_LINES: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL5);
4749 pa_debug.reset_curr_function;
4750 END IF;
4751 RAISE;
4752
4753 END ADD_FIN_PLAN_LINES;
4754
4755 END pa_fin_plan_pvt;