[Home] [Help]
PACKAGE BODY: APPS.PA_TASK_ASSIGNMENTS_PVT
Source
1 PACKAGE BODY PA_TASK_ASSIGNMENTS_PVT AS
2 -- $Header: PATAPVTB.pls 120.13.12020000.5 2013/04/30 18:51:44 sachandr ship $
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'PA_TASK_ASSIGNMENTS_PVT';
5 P_DEBUG_MODE varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
6 li_curr_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
7
8
9
10
11
12
13
14 PROCEDURE Create_Task_Assignment_Periods
15 ( p_api_version_number IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
16 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
17 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
18 ,p_pm_product_code IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
19 ,p_pm_project_reference IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
20 ,p_pa_project_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
21 ,p_pa_structure_version_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
22 ,p_task_assignment_periods_in IN PA_TASK_ASSIGNMENTS_PUB.ASSIGNMENT_PERIODS_TBL_TYPE
23 ,p_task_assignment_periods_out OUT NOCOPY PA_TASK_ASSIGNMENTS_PUB.ASSIGNMENT_OUT_TBL_TYPE
24 ,x_msg_count OUT NOCOPY NUMBER
25 ,x_msg_data OUT NOCOPY VARCHAR2
26 ,x_return_status OUT NOCOPY VARCHAR2
27 ) IS
28 l_calling_context varchar2(200);
29 l_fin_plan_version_id number;
30 l_finplan_lines_tab pa_fin_plan_pvt.budget_lines_tab;
31 --pa_fp_rollup_tmp
32 l_project_id pa_projects.project_id%type;
33 l_d_task_id NUMBER;
34 l_resource_assignment_id_tbl system.pa_num_tbl_type := system.pa_num_tbl_type();
35 l_msg_count NUMBER ;
36 l_msg_data VARCHAR2(2000);
37 l_function_allowed VARCHAR2(1);
38 l_resp_id NUMBER := 0;
39 l_user_id NUMBER := 0;
40 l_module_name VARCHAR2(80);
41 l_return_status VARCHAR2(1);
42 l_api_name CONSTANT VARCHAR2(30) := 'add_task_assignments';
43 i NUMBER;
44 l_count NUMBER;
45
46 l_context varchar2(200);
47 l_calling_module varchar2(200);
48 l_struct_elem_version_id number;
49 l_budget_version_id pa_budget_versions.budget_version_id%TYPE;
50 l_task_elem_version_id_tbl system.pa_num_tbl_type := system.pa_num_tbl_type();
51 l_start_date_tbl system.pa_date_tbl_type := system.pa_date_tbl_type();
52 l_end_date_tbl system.pa_date_tbl_type := system.pa_date_tbl_type();
53 l_resource_list_member_id_tbl system.pa_num_tbl_type := system.pa_num_tbl_type();
54 l_cbs_element_id_tbl system.pa_num_tbl_type := system.pa_num_tbl_type(); --16598322
55 l_quantity_tbl system.pa_num_tbl_type := system.pa_num_tbl_type();
56 l_currency_code_tbl system.pa_varchar2_15_tbl_type := system.pa_varchar2_15_tbl_type();
57 l_raw_cost_tbl system.pa_num_tbl_type := system.pa_num_tbl_type();
58 l_burdened_cost_tbl system.pa_num_tbl_type := system.pa_num_tbl_type();
59
60 l_project_currency_code pa_projects_all.project_currency_code%TYPE;
61
62 l_txn_currency_code pa_budget_lines.txn_currency_code%TYPE;
63
64 l_period_set_name gl_sets_of_books.period_set_name%TYPE;
65 l_accounted_period_type gl_sets_of_books.accounted_period_type%TYPE;
66 l_pa_period_type pa_implementations_all.pa_period_type%TYPE;
67 l_time_phased_code pa_proj_fp_options.all_time_phased_code%TYPE;
68 l_period_name gl_periods.period_name%TYPE;
69 l_period_start_date DATE;
70 l_period_end_date DATE;
71 -- 10/22/04: To handle only lines after the progress as of date
72 l_finplan_line_count NUMBER := 1;
73
74 l_etc_start_date pa_budget_versions.etc_start_date%TYPE;
75 l_project_name pa_projects_all.name%TYPE;
76
77 --bug#13059417.changes starts here.
78 l_spread_curve_id pa_resource_assignments.spread_curve_id%TYPE;
79 l_sp_fixed_date DATE;
80
81 Cursor C_Spread_Curve_Id(p_resource_assignment_id IN NUMBER) IS
82 select spread_curve_id, sp_fixed_date
83 from pa_resource_assignments
84 where resource_assignment_id = p_resource_assignment_id ;
85 -- bug#13059417.changes ends here.
86
87 CURSOR C_Get_txn_Currency(p_resource_assignment_id IN NUMBER) IS
88 select txn_currency_code -- ,min(start_date) bug 6407736 - skkoppul
89 from pa_resource_asgn_curr
90 where resource_assignment_id = p_resource_assignment_id;
91 -- group by txn_currency_code;bug 6407736 - skkoppul
92
93 CURSOR C_Res_Asgmt_Data(p_resource_assignment_id IN NUMBER) IS
94 select task_id, wbs_element_version_id, supplier_id, resource_class_code, resource_assignment_id,
95 project_role_id, organization_id,
96 fc_res_type_code, named_role,res_type_code, planning_start_date, planning_end_date,
97 procure_resource_flag, use_task_schedule_flag, rate_based_flag from pa_resource_assignments where
98 resource_assignment_id = p_resource_assignment_id;
99 C_Res_Asgmt_Data_Rec C_Res_Asgmt_Data%ROWTYPE;
100
101 CURSOR C_Get_res_info(p_resource_assignment_id IN NUMBER) IS
102 select rate_based_flag , resource_list_member_id, planning_start_date, planning_end_date
103 from pa_resource_assignments r
104 where resource_assignment_id = p_resource_assignment_id;
105 C_Get_res_info_rec C_Get_res_info%ROWTYPE;
106
107 CURSOR C_Get_Budget_Version_Id(p_structure_version_id IN NUMBER, p_project_id IN NUMBER) is
108 select a.budget_version_id, b.project_currency_code
109 from pa_budget_versions a, pa_projects_all b
110 where a.project_structure_version_id = p_structure_version_id
111 and a.project_id = b.project_id
112 and a.project_id = p_project_id;
113
114 CURSOR C_Progress_Exists(p_project_id IN NUMBER, p_task_id IN NUMBER, p_resource_assignment_id IN NUMBER) IS
115 SELECT prog.actual_finish_date, pa_progress_utils.Check_object_has_prog(
116 p_project_id,
117 p_task_id,
118 p_resource_assignment_id,
119 'PA_ASSIGNMENTS',
120 'WORKPLAN'
121 ) Progress_Exists
122 FROM pa_assignment_progress_v prog
123 WHERE prog.resource_assignment_id = p_resource_assignment_id;
124
125 l_prog_finish_date DATE;
126 l_progress_exists varchar2(1);
127
128 l_task_elem_version_id NUMBER;
129 l_task_name pa_proj_elements.name%TYPE;
130 l_rlm_alias pa_resource_list_members.alias%TYPE;
131
132 CURSOR C_Task_Elem_Version_Id(p_structure_version_id IN NUMBER,
133 p_task_id in NUMBER,
134 p_project_id IN NUMBER) IS
135 SELECT pe.element_version_id
136 from pa_proj_element_versions pe
137 where parent_structure_version_id = p_structure_version_id
138 and pe.proj_element_id = p_task_id
139 and pe.project_id = p_project_id;
140
141 CURSOR C_task_version(p_task_element_version_id IN NUMBER) IS
142 SELECT pe.element_version_id, pe.proj_element_id
143 from pa_proj_element_versions pe
144 where pe.element_version_id = p_task_element_version_id;
145
146 -- Bug# 6432606 Start
147 /*
148 CURSOR C_Prog_Date(p_resource_assignment_id IN NUMBER) IS
149 SELECT a.as_of_date from pa_assignment_progress_v a
150 WHERE a.resource_assignment_id = p_resource_assignment_id
151 and a.project_id = p_pa_project_id
152 and a.structure_version_id = p_pa_structure_version_id;
153 */
154 -- Commented above and added below for Bug 6432606
155 /*
156 CURSOR C_Prog_Date(p_resource_assignment_id IN NUMBER) IS
157 select ppr.as_of_date
158 from pa_progress_rollup ppr,
159 pa_proj_element_versions ppv,
160 pa_resource_assignments pra
161 where ppv.parent_structure_version_id=p_pa_structure_version_id
162 and ppv.project_id=p_pa_project_id
163 and pra.resource_assignment_id=p_resource_assignment_id
164 and pra.project_id=ppv.project_id
165 and ppv.proj_element_id=pra.task_id
166 and ppr.object_id=pra.resource_list_member_id
167 and ppr.object_type='PA_ASSIGNMENTS'
168 and ppr.object_version_id=ppv.element_version_id
169 and ppr.project_id=ppv.project_id
170 and ppr.structure_version_id=ppv.parent_structure_version_id
171 and ppr.structure_type='WORKPLAN'
172 and ppr.current_flag='Y';
173 -- Bug# 6432606 End
174
175
176 C_Prog_Date_Rec C_Prog_Date%ROWTYPE;
177 */
178
179 L_FuncProc varchar2(2000);
180
181 CURSOR get_resource_alias(c_resource_list_member_id IN NUMBER) IS
182 SELECT alias
183 from pa_resource_list_members
184 where resource_list_member_id = c_resource_list_member_id;
185
186 CURSOR get_task_name(c_task_id NUMBER) IS
187 SELECT NAME
188 FROM pa_proj_elements
189 WHERE proj_element_id = c_task_id;
190
191 --C_Res_List_Mem_Check_Rec C_Res_List_Mem_Check%ROWTYPE;
192
193 CURSOR c_cur_out(p_structure_version_id IN NUMBER,
194 p_project_id IN NUMBER,
195 p_wbs_version_id IN NUMBER,
196 p_resource_list_member_id IN NUMBER ) IS
197 Select a.alias, b.resource_assignment_id
198 from pa_resource_list_members a, pa_resource_assignments b, pa_budget_versions bv
199 where a.resource_list_member_id = b.resource_list_member_id
200 and b.resource_list_member_id = p_resource_list_member_id
201 and b.ta_display_flag = 'Y'
202 and b.budget_version_id = bv.budget_version_id
203 and b.project_id = bv.project_id
204 and bv.project_structure_version_id = p_structure_version_id
205 and b.project_id = p_project_id
206 and b.wbs_element_version_id = p_wbs_version_id;
207 c_rec_out c_cur_out%ROWTYPE;
208
209 --Added for CBS ::CBS::
210 CURSOR c_cur_out_for_cbs(p_structure_version_id IN NUMBER,
211 p_project_id IN NUMBER,
212 p_wbs_version_id IN NUMBER,
213 p_resource_list_member_id IN NUMBER,p_cbs_element_id IN NUMBER ) IS
214 Select a.alias, b.resource_assignment_id
215 from pa_resource_list_members a, pa_resource_assignments b, pa_budget_versions bv
216 where a.resource_list_member_id = b.resource_list_member_id
217 and b.resource_list_member_id = p_resource_list_member_id
218 and b.ta_display_flag = 'Y'
219 and b.budget_version_id = bv.budget_version_id
220 and b.project_id = bv.project_id
221 and bv.project_structure_version_id = p_structure_version_id
222 and b.project_id = p_project_id
223 and b.wbs_element_version_id = p_wbs_version_id
224 and b.cbs_element_id=p_cbs_element_id;
225 c_rec_out_for_cbs c_cur_out_for_cbs%ROWTYPE;
226
227
228 CURSOR C_Workplan_Costs_enabled(p_budget_version_id IN NUMBER) IS
229 select TRACK_WORKPLAN_COSTS_FLAG enabled_flag from pa_proj_fp_options
230 where fin_plan_version_id = p_budget_version_id;
231 C_Workplan_costs_rec C_Workplan_Costs_enabled%ROWTYPE;
232
233 -- MOAC Changes: Bug 4363092: removed nvl with org_id
234 CURSOR get_fp_options_csr(c_budget_version_id NUMBER) IS
235 SELECT gsb.period_set_name
236 ,gsb.accounted_period_type
237 ,pia.pa_period_type
238 ,decode(pbv.version_type,
239 'COST',ppfo.cost_time_phased_code,
240 'REVENUE',ppfo.revenue_time_phased_code,
241 ppfo.all_time_phased_code) time_phase_code
242 ,pbv.etc_start_date
243 FROM gl_sets_of_books gsb
244 ,pa_implementations_all pia
245 ,pa_projects_all ppa
246 ,pa_budget_versions pbv
247 ,pa_proj_fp_options ppfo
248 WHERE ppa.project_id = pbv.project_id
249 AND pbv.budget_version_id = ppfo.fin_plan_version_id
250 AND ppa.org_id = pia.org_id
251 AND gsb.set_of_books_id = pia.set_of_books_id
252 AND pbv.budget_version_id = c_budget_version_id;
253
254
255 l_start_date DATE;
256 k_index NUMBER := 0;
257 -- l_org_id NUMBER; --Bug 4363092: Commenting this
258 l_progress_safe VARCHAR2(1);
259
260 p_task_assignment_tbl pa_task_assignment_utils.l_resource_rec_tbl_type;
261
262 val_index number := 0;
263
264 l_old_resource_assignment_id NUMBER;
265 l_old_txn_currency VARCHAR2(15);
266 l_prog_resource_assignment_id NUMBER;
267
268 l_valid_member_flag varchar2(1);
269 l_resource_list_id number;
270 l_resource_list_member_id number;
271
272 l_cbs_enabled_project varchar2(1) :='N'; -- Added for CBS 16598322
273 BEGIN
274
275 L_FuncProc := 'Create_Task_Asgmts Periods';
276
277 --Bug 4363092: MOAC Changes: Commenting beloew call as l_org_id is not used anywhere
278 --fnd_profile.get('ORG_ID', l_org_id);
279
280 IF P_DEBUG_MODE = 'Y' AND (li_curr_level <= 3) THEN
281 pa_debug.g_err_stage:='Entered ' || L_FuncProc ;
282 pa_debug.write(G_PKG_NAME ,pa_debug.g_err_stage,3);
283 END IF;
284 --dbms_output.put_line('Entered Create Task Asgmts.');
285 -- Standard begin of API savepoint
286
287 SAVEPOINT add_task_asgmt_periods;
288
289 -- Standard call to check for call compatibility.
290
291 IF NOT FND_API.Compatible_API_Call ( 1.0, --pa_project_pub.g_api_version_number ,
292 p_api_version_number ,
293 l_api_name ,
294 G_PKG_NAME )
295 THEN
296
297 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
298
299 END IF;
300
301 --dbms_output.put_line('Fnd Api is compatible:');
302
303 -- Initialize the message table if requested.
304
305 IF FND_API.TO_BOOLEAN( p_init_msg_list )
306 THEN
307
308 FND_MSG_PUB.initialize;
309
310 END IF;
311
312 -- Set API return status to success
313
314 x_return_status := FND_API.G_RET_STS_SUCCESS;
315
316 -- Initialize the message table if requested.
317 -- pm_product_code is mandatory
318
319 --dbms_output.put_line('Initialized message table.');
320
321 IF P_DEBUG_MODE = 'Y' AND (li_curr_level <= 3) THEN
322 pa_debug.g_err_stage:='Checking p_pm_product_code ' || L_FuncProc;
323 pa_debug.write(G_PKG_NAME ,pa_debug.g_err_stage,3);
324 END IF;
325
326 IF p_pm_product_code IS NOT NULL
327 AND p_pm_product_code = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR THEN
328
329 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
330 pa_interface_utils_pub.map_new_amg_msg
331 ( p_old_message_code => 'PA_PRODUCT_CODE_IS_MISSING'
332 ,p_msg_attribute => 'CHANGE'
333 ,p_resize_flag => 'N'
334 ,p_msg_context => 'GENERAL'
335 ,p_attribute1 => ''
336 ,p_attribute2 => ''
337 ,p_attribute3 => ''
338 ,p_attribute4 => ''
339 ,p_attribute5 => '');
340 END IF;
341 RAISE FND_API.G_EXC_ERROR;
342
343 END IF;
344
345 --dbms_output.put_line('Product Code is checked:');
346
347 l_resp_id := FND_GLOBAL.Resp_id;
348 l_user_id := FND_GLOBAL.User_id;
349
350 --dbms_output.put_line('User id :' || l_user_id || 'l_resp_id' || l_resp_id);
351
352 IF P_DEBUG_MODE = 'Y' AND (li_curr_level <= 3) THEN
353 pa_debug.g_err_stage:=' p_pm_product_code check successful.' || L_FuncProc;
354 pa_debug.write(G_PKG_NAME ,pa_debug.g_err_stage,3);
355 END IF;
356 --> Need sep. fn. for periods ? check..
357
358 l_module_name := 'PA_PM_ADD_TASK_ASSIGNMENT';
359
360 --> Project Id check.
361
362 IF p_pa_project_id is NOT NULL AND p_pa_project_id <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM THEN
363
364 l_project_id := p_pa_project_id;
365
366 --dbms_output.put_line('Project_id successfully passed..Check ' || l_project_id);
367
368 ELSE
369 --dbms_output.put_line('Converting Project ref to id:' || p_pm_project_reference);
370 PA_PROJECT_PVT.Convert_pm_projref_to_id
371 ( p_pm_project_reference => p_pm_project_reference
372 , p_pa_project_id => p_pa_project_id
373 , p_out_project_id => l_project_id
374 , p_return_status => l_return_status
375 );
376
377 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
378
379 --dbms_output.put_line('Project_id not successful ');
380 IF P_DEBUG_MODE = 'Y' AND (li_curr_level <= 3) THEN
381 pa_debug.g_err_stage:=' Project ref to id check not successful.' || L_FuncProc;
382 pa_debug.write(G_PKG_NAME ,pa_debug.g_err_stage,3);
383 END IF;
384 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
385
386 ELSIF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
387
388 --dbms_output.put_line('Project_id conv. not successful ');
389 IF P_DEBUG_MODE = 'Y' AND (li_curr_level <= 3) THEN
390 pa_debug.g_err_stage:=' Project ref to id check not successful.' || L_FuncProc;
391 pa_debug.write(G_PKG_NAME ,pa_debug.g_err_stage,3);
392 END IF;
393 RAISE FND_API.G_EXC_ERROR;
394
395 END IF;
396 END IF;
397
398 --dbms_output.put_line('Project ref to id check successful for Project ' || l_Project_id);
399 IF P_DEBUG_MODE = 'Y' AND (li_curr_level <= 3) THEN
400 pa_debug.g_err_stage:=' Project ref to id check successful.' || L_FuncProc;
401 pa_debug.write(G_PKG_NAME ,pa_debug.g_err_stage,3);
402 END IF;
403 -- As part of enforcing project security, which would determine
404 -- whether the user has the necessary privileges to update the project
405 -- need to call the pa_security package
406
407 pa_security.initialize (X_user_id => l_user_id,
408 X_calling_module => l_module_name);
409 IF P_DEBUG_MODE = 'Y' AND (li_curr_level <= 3) THEN
410 pa_debug.g_err_stage:=' After initializing security..' || L_FuncProc;
411 pa_debug.write(G_PKG_NAME ,pa_debug.g_err_stage,3);
412 END IF;
413 -- Actions performed using the APIs would be subject to
414 -- function security. If the responsibility does not allow
415 -- such functions to be executed, the API should not proceed further
416 -- since the user does not have access to such functions
417
418 -- Function security procedure check whether user have the
419 -- privilege to add task or not
420
421 --dbms_output.put_line('Security Initialize successful.');
422 PA_INTERFACE_UTILS_PUB.G_PROJECT_ID := l_project_id; --bug 2471668 ( in the project context )
423
424 --Get the CBS flag 16598322
425 l_cbs_enabled_project := PA_ALTERNATE_TASK_PVT.Is_Cbs_Enabled(p_Project_Id => l_project_id);
426
427 PA_PM_FUNCTION_SECURITY_PUB.check_function_security
428 (p_api_version_number => p_api_version_number,
429 p_responsibility_id => l_resp_id,
430 p_function_name => 'PA_PM_ADD_TASK_ASSIGNMENT',
431 p_msg_count => l_msg_count,
432 p_msg_data => l_msg_data,
433 p_return_status => l_return_status,
434 p_function_allowed => l_function_allowed);
435
436 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
437 THEN
438 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
439
440 ELSIF l_return_status = FND_API.G_RET_STS_ERROR
441 THEN
442 RAISE FND_API.G_EXC_ERROR;
443 END IF;
444
445 IF P_DEBUG_MODE = 'Y' AND (li_curr_level <= 3) THEN
446 pa_debug.g_err_stage:=' PA_PM_ADD_TASK_ASSIGNMENT function check successful.' || L_FuncProc;
447 pa_debug.write(G_PKG_NAME ,pa_debug.g_err_stage,3);
448 END IF;
449 --dbms_output.put_line('PA_PM_ADD_TASK_ASSIGNMENT function check successful.');
450 IF l_function_allowed = 'N' THEN
451 pa_interface_utils_pub.map_new_amg_msg
452 ( p_old_message_code => 'PA_FUNCTION_SECURITY_ENFORCED'
453 ,p_msg_attribute => 'CHANGE'
454 ,p_resize_flag => 'Y'
455 ,p_msg_context => 'GENERAL'
456 ,p_attribute1 => ''
457 ,p_attribute2 => ''
458 ,p_attribute3 => ''
459 ,p_attribute4 => ''
460 ,p_attribute5 => '');
461 RAISE FND_API.G_EXC_ERROR;
462 END IF;
463 --dbms_output.put_line('PA_FUNCTION_SECURITY_ENFORCED function check successful.');
464 IF P_DEBUG_MODE = 'Y' AND (li_curr_level <= 3) THEN
465 pa_debug.g_err_stage:=' PA_FUNCTION_SECURITY_ENFORCED function check successful.' || L_FuncProc;
466 pa_debug.write(G_PKG_NAME ,pa_debug.g_err_stage,3);
467 END IF;
468
469
470 -- Now verify whether project security allows the user to update
471 -- the project
472 -- The user does not have query privileges on this project
473 -- Hence, cannot update the project.Raise error
474 -- If the user has query privileges, then check whether
475 -- update privileges are also available
476
477 IF pa_security.allow_query(x_project_id => l_project_id ) = 'N' OR
478 pa_security.allow_update(x_project_id => l_project_id ) = 'N' THEN
479
480 -- The user does not have update privileges on this project
481 -- Hence , raise error
482 --dbms_output.put_line('pa_security.allow_query or update not allowed..');
483 pa_interface_utils_pub.map_new_amg_msg
484 ( p_old_message_code => 'PA_PROJECT_SECURITY_ENFORCED'
485 ,p_msg_attribute => 'CHANGE'
486 ,p_resize_flag => 'Y'
487 ,p_msg_context => 'GENERAL'
488 ,p_attribute1 => ''
489 ,p_attribute2 => ''
490 ,p_attribute3 => ''
491 ,p_attribute4 => ''
492 ,p_attribute5 => '');
493 RAISE FND_API.G_EXC_ERROR;
494 END IF;
495
496 --dbms_output.put_line('pa_security.allow_query or update successful..');
497 IF P_DEBUG_MODE = 'Y' AND (li_curr_level <= 3) THEN
498 pa_debug.g_err_stage:='PA_PROJECT_SECURITY_ENFORCED function check successful.' || L_FuncProc;
499 pa_debug.write(G_PKG_NAME ,pa_debug.g_err_stage,3);
500 END IF;
501 --dbms_output.put_line('Project Id:' || l_project_id);
502
503 IF NVL(PA_PROJ_TASK_STRUC_PUB.WP_STR_EXISTS( l_project_id ), 'N') = 'N' THEN
504 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
505 THEN
506 --dbms_output.put_line('PA_PROJ_TASK_STRUC_PUB.WP_STR_EXISTS IS N..');
507 pa_interface_utils_pub.map_new_amg_msg
508 ( p_old_message_code => 'PA_PS_WP_NOT_SEP_FN_AMG'
509 ,p_msg_attribute => 'CHANGE'
510 ,p_resize_flag => 'N'
511 ,p_msg_context => 'GENERAL'
512 ,p_attribute1 => ''
513 ,p_attribute2 => ''
514 ,p_attribute3 => ''
515 ,p_attribute4 => ''
516 ,p_attribute5 => '');
517 END IF;
518 RAISE FND_API.G_EXC_ERROR;
519 END IF;
520
521 --dbms_output.put_line('PA_PROJ_TASK_STRUC_PUB.WP_STR_EXISTS IS Fine..');
522 IF P_DEBUG_MODE = 'Y' AND (li_curr_level <= 3) THEN
523 pa_debug.g_err_stage:='PA_PROJ_TASK_STRUC_PUB.WP_STR_EXISTS function check successful.' || L_FuncProc;
524 pa_debug.write(G_PKG_NAME ,pa_debug.g_err_stage,3);
525 END IF;
526
527 IF p_pa_structure_version_id IS NOT NULL AND
528 (p_pa_structure_version_id <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM) THEN
529
530 l_struct_elem_version_id := p_pa_structure_version_id;
531
532 ELSE
533 --dbms_output.put_line('Getting current structure version' );
534 l_struct_elem_version_id := PA_PROJECT_STRUCTURE_UTILS.get_current_working_ver_id(
535 p_project_id => l_project_id);
536
537
538 END IF;
539
540 --dbms_output.put_line(' structure version: ' || l_struct_elem_version_id );
541 --Project Structures Integration
542
543 IF ( l_struct_elem_version_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM OR
544 l_struct_elem_version_id IS NULL )
545 THEN
546 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
547 THEN
548 pa_interface_utils_pub.map_new_amg_msg
549 ( p_old_message_code => 'PA_PS_STRUC_VER_REQ'
550 ,p_msg_attribute => 'CHANGE'
551 ,p_resize_flag => 'N'
552 ,p_msg_context => 'GENERAL'
553 ,p_attribute1 => ''
554 ,p_attribute2 => ''
555 ,p_attribute3 => ''
556 ,p_attribute4 => ''
557 ,p_attribute5 => '');
558 END IF;
559 RAISE FND_API.G_EXC_ERROR;
560 END IF;
561
562 -- DHI ER: allowing multiple user to update task assignment
563 -- Removed logic to lock version.
564 --lock_version( l_project_id, l_struct_elem_version_id);
565
566 IF 'N' = pa_task_assignment_utils.check_edit_task_ok( P_PROJECT_ID => l_project_id,
567 P_STRUCTURE_VERSION_ID => l_struct_elem_version_id,
568 P_CURR_STRUCT_VERSION_ID => l_struct_elem_version_id) THEN
569 -- Bug 4533152
570 --PA_UTILS.ADD_MESSAGE
571 -- (p_app_short_name => 'PA',
572 -- p_msg_name => 'PA_UPDATE_PUB_VER_ERR'
573 -- );
574 x_return_status := FND_API.G_RET_STS_ERROR;
575 RAISE FND_API.G_EXC_ERROR;
576 END IF;
577
578 IF P_DEBUG_MODE = 'Y' AND (li_curr_level <= 3) THEN
579 pa_debug.g_err_stage:='struct_elem version id function check successful.' || L_FuncProc;
580 pa_debug.write(G_PKG_NAME ,pa_debug.g_err_stage,3);
581 END IF;
582
583
584 OPEN C_Get_Budget_Version_Id(l_struct_elem_version_id, l_project_id);
585 FETCH C_Get_Budget_Version_Id INTO l_budget_version_id, l_project_currency_code;
586 CLOSE C_Get_Budget_Version_Id;
587
588 IF ( l_budget_version_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM OR
589 l_budget_version_id IS NULL )
590 THEN
591 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
592 THEN
593 PA_UTILS.ADD_MESSAGE
594 (p_app_short_name => 'PA',
595 p_msg_name => 'PA_FP_PROJ_VERSION_MISMATCH'
596 );
597 END IF;
598 x_return_status := FND_API.G_RET_STS_ERROR;
599 RAISE FND_API.G_EXC_ERROR;
600
601 END IF;
602
603 --dbms_output.put_line(' budget version id: ' || l_budget_version_id );
604
605 l_count := p_task_assignment_periods_in.COUNT;
606
607 IF P_DEBUG_MODE = 'Y' AND (li_curr_level <= 3) THEN
608 pa_debug.g_err_stage:='Count of task assignment periods' || l_count || ':' || L_FuncProc;
609 pa_debug.write(G_PKG_NAME ,pa_debug.g_err_stage,3);
610 END IF;
611
612 --dbms_output.put_line(' Input Count of Global Input Tables..: ' || l_count );
613
614 l_task_elem_version_id_tbl.extend(l_count);
615 l_start_date_tbl.extend(l_count);
616 l_end_date_tbl.extend(l_count);
617 l_resource_list_member_id_tbl.extend(l_count);
618 l_cbs_element_id_tbl.extend(l_count);--16598322
619 l_quantity_tbl.extend(l_count);
620 l_currency_code_tbl.extend(l_count);
621 l_raw_cost_tbl.extend(l_count);
622 l_burdened_cost_tbl.extend(l_count);
623 l_resource_assignment_id_tbl.extend(l_count);
624
625 --dbms_output.put_line('Entering Loop for internal table set..');
626
627 -- Bug 3866222: Get info from pa_proj_fp_options for checking period details
628 OPEN get_fp_options_csr(l_budget_version_id);
629 FETCH get_fp_options_csr INTO
630 l_period_set_name
631 ,l_accounted_period_type
632 ,l_pa_period_type
633 ,l_time_phased_code
634 ,l_etc_start_date;
635 CLOSE get_fp_options_csr;
636
637 select name into l_project_name from pa_projects_all where project_id = l_project_id;
638
639 FOR i in 1..l_count LOOP
640
641 -- Bug 3866222: Initial period details
642 l_period_name := null;
643 l_period_start_date := null;
644 l_period_end_date := null;
645 -- C_Prog_Date_Rec := NULL;
646
647 --dbms_output.put_line('Entering setting in create Task Assignment periods for index:' || i);
648 --dbms_output.put_line('Within load of internal tables..index is:' || i);
649
650
651 l_d_task_id := NULL;
652 l_task_elem_version_id := NULL;
653
654 -- Bug 9544497
655 IF p_task_assignment_periods_in(i).pa_task_assignment_id is not NULL AND
656 p_task_assignment_periods_in(i).pa_task_assignment_id <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM THEN
657 OPEN C_Res_Asgmt_Data(p_task_assignment_periods_in(i).pa_task_assignment_id );
658 FETCH C_Res_Asgmt_Data into C_Res_Asgmt_Data_Rec;
659 CLOSE C_Res_Asgmt_Data;
660
661 l_d_task_id := C_Res_Asgmt_Data_Rec.task_id;
662 l_task_elem_version_id := C_Res_Asgmt_Data_Rec.wbs_element_version_id;
663 END IF;
664
665 --l_d_task_id := C_Res_Asgmt_Data_Rec.task_id;
666 --l_task_elem_version_id := C_Res_Asgmt_Data_Rec.wbs_element_version_id;
667
668
669 IF l_task_elem_version_id IS NULL AND p_task_assignment_periods_in.exists(i) AND p_task_assignment_periods_in(i).pa_task_element_version_id IS NOT NULL AND
670 p_task_assignment_periods_in(i).pa_task_element_version_id <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM THEN
671
672 OPEN C_task_version(p_task_assignment_periods_in(i).pa_task_element_version_id);
673 FETCH C_task_version INTO l_task_elem_version_id, l_d_task_id;
674 CLOSE C_task_version;
675
676
677 ELSIF l_task_elem_version_id IS NULL AND p_task_assignment_periods_in.exists(i) AND p_task_assignment_periods_in(i).pa_task_id IS NOT NULL AND
678 p_task_assignment_periods_in(i).pa_task_id <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM THEN
679
680 l_d_task_id := p_task_assignment_periods_in(i).pa_task_id;
681
682 --dbms_output.put_line('l_d_task_id valid input:'|| l_d_task_id);
683
684 IF P_DEBUG_MODE = 'Y' AND (li_curr_level <= 3) THEN
685 pa_debug.g_err_stage:='task_id ' || l_d_task_id;
686 pa_debug.write(G_PKG_NAME ,pa_debug.g_err_stage,3);
687 END IF;
688
689 --dbms_output.put_line('l_d_task_id'|| l_d_task_id);
690
691 IF ( l_d_task_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM OR
692 l_d_task_id IS NULL )
693 THEN
694 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
695 THEN
696 PA_UTILS.ADD_MESSAGE
697 (p_app_short_name => 'PA',
698 p_msg_name => 'PA_TASK_REQUIRED'
699 );
700 END IF;
701
702 RAISE FND_API.G_EXC_ERROR;
703 END IF;
704
705
706
707 l_task_elem_version_id := PA_PROJ_ELEMENTS_UTILS.GET_TASK_VERSION_ID(p_structure_version_id => l_struct_elem_version_id
708 ,p_task_id => l_d_task_id);
709
710
711 ELSIF l_task_elem_version_id IS NULL AND p_task_assignment_periods_in.exists(i) AND
712 p_task_assignment_periods_in(i).pm_task_reference IS NOT NULL AND
713 p_task_assignment_periods_in(i).pm_task_reference <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR THEN
714
715 --dbms_output.put_line('l_d_task_reference'|| p_task_assignment_periods_in(i).pm_task_reference);
716
717
718
719 PA_PROJECT_PVT.CONVERT_PM_TASKREF_TO_ID_all(p_pa_project_id => l_project_id
720 ,p_pm_task_reference => p_task_assignment_periods_in(i).pm_task_reference
721 ,p_structure_type => 'WORKPLAN'
722 ,p_out_task_id => l_d_task_id
723 ,p_return_status => l_return_status);
724
725 --dbms_output.put_line('l_d_task_id'|| l_d_task_id);
726
727 IF ( l_d_task_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM OR
728 l_d_task_id IS NULL )
729 THEN
730 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
731 THEN
732 PA_UTILS.ADD_MESSAGE
733 (p_app_short_name => 'PA',
734 p_msg_name => 'PA_TASK_REQUIRED'
735 );
736 END IF;
737
738 RAISE FND_API.G_EXC_ERROR;
739 END IF;
740
741
742
743 l_task_elem_version_id := PA_PROJ_ELEMENTS_UTILS.GET_TASK_VERSION_ID(p_structure_version_id => l_struct_elem_version_id
744 ,p_task_id => l_d_task_id);
745
746 END IF;
747
748 IF l_task_elem_version_id is not NULL AND
749 l_task_elem_version_id <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM THEN
750
751 --extended already.
752
753 l_task_elem_version_id_tbl(i):= l_task_elem_version_id;
754
755 ELSE
756 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
757 THEN
758 PA_UTILS.ADD_MESSAGE
759 (p_app_short_name => 'PA',
760 p_msg_name => 'PA_TASK_VERSION_REQUIRED'
761 );
762
763 RAISE FND_API.G_EXC_ERROR;
764 END IF;
765 END IF;
766
767
768
769 --dbms_output.put_line('l_task_elem_version_id' || l_task_elem_version_id);
770
771 IF p_task_assignment_periods_in(i).pa_task_assignment_id is NOT null AND
772 p_task_assignment_periods_in(i).pa_task_assignment_id <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM THEN
773
774 --dbms_output.put_line('Accepting Task Assignment Id given:' || p_task_assignment_periods_in(i).pa_task_assignment_id );
775 l_resource_assignment_id_tbl(i) := p_task_assignment_periods_in(i).pa_task_assignment_id;
776
777 ELSIF p_task_assignment_periods_in(i).pm_task_asgmt_reference is not null AND
778 p_task_assignment_periods_in(i).pm_task_asgmt_reference <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR THEN
779
780 --dbms_output.put_line('Converting Task Asgmt Reference:' || p_task_assignment_periods_in(i).pm_task_asgmt_reference );
781
782 PA_TASK_ASSIGNMENTS_PUB.Convert_PM_TARef_To_ID( p_pm_product_code => p_pm_product_code
783 ,p_pa_project_id => l_project_id
784 ,p_pa_structure_version_id => l_struct_elem_version_id
785 ,p_pa_task_id => l_d_task_id
786 ,p_pa_task_elem_ver_id => l_task_elem_version_id_tbl(i)
787 ,p_pm_task_asgmt_reference => p_task_assignment_periods_in(i).pm_task_asgmt_reference
788 ,p_pa_task_assignment_id => p_task_assignment_periods_in(i).pa_task_assignment_id
789 ,p_resource_alias => p_task_assignment_periods_in(i).resource_alias
790 ,p_resource_list_member_id => p_task_assignment_periods_in(i).resource_list_member_id
791 ,p_cbs_element_id => p_task_assignment_periods_in(i).cbs_element_id
792 ,x_pa_task_assignment_id => l_resource_assignment_id_tbl(i)
793 ,x_return_status => x_return_status
794 );
795
796
797 END IF;
798
799
800 IF l_resource_assignment_id_tbl(i) IS NULL OR
801 l_resource_assignment_id_tbl(i) = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM THEN
802 --new message case bug 3855080
803 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
804 THEN
805 PA_UTILS.ADD_MESSAGE
806 (p_app_short_name => 'PA',
807 p_msg_name => 'PA_PM_TASK_ASGMT_REQ',
808 p_token1 => 'RESOURCE_REF',
809 p_value1 => p_task_assignment_periods_in(i).pm_task_asgmt_reference
810 );
811 END IF;
812 x_return_status := FND_API.G_RET_STS_ERROR;
813 RAISE FND_API.G_EXC_ERROR;
814
815 END IF;
816
817 -- Bug 3866222: validate period data
818 -- Additional check to ensure periodic date information is passed
819 OPEN get_resource_alias(l_resource_list_member_id);
820 FETCH get_resource_alias INTO l_rlm_alias;
821 CLOSE get_resource_alias;
822
823 OPEN get_task_name(l_d_task_id);
824 FETCH get_task_name INTO l_task_name;
825 CLOSE get_task_name;
826 -- End of Bug 3866222: validate period data
827
828 IF (
829 (
830 (p_task_assignment_periods_in(i).start_date IS NULL OR
831 p_task_assignment_periods_in(i).start_date = PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE
832 )
833 OR
834 (p_task_assignment_periods_in(i).end_date IS NULL OR
835 p_task_assignment_periods_in(i).end_date = PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE
836 )
837 )
838 AND
839 (p_task_assignment_periods_in(i).period_name IS NULL OR
840 p_task_assignment_periods_in(i).period_name = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
841 )
842 )THEN
843
844 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
845
846 PA_UTILS.ADD_MESSAGE
847 (p_app_short_name => 'PA',
848 p_msg_name => 'PA_INVALID_PERIOD_ERR',
849 p_token1 => 'TASK_NAME',
850 p_value1 => l_task_name,
851 p_token2 => 'PL_RES_ALIAS',
852 p_value2 => l_rlm_alias,
853 p_token3 => 'PERIOD_NAME',
854 p_value3 => p_task_assignment_periods_in(i).period_name,
855 p_token4 => 'START_DATE',
856 p_value4 => p_task_assignment_periods_in(i).start_date,
857 p_token5 => 'END_DATE',
858 p_value5 => p_task_assignment_periods_in(i).end_date
859 );
860
861 x_return_status := FND_API.G_RET_STS_ERROR;
862 RAISE FND_API.G_EXC_ERROR;
863 END IF;
864
865 END IF;
866
867 -- Bug 3866222: validate period data
868 l_period_name := p_task_assignment_periods_in(i).period_name;
869 l_period_start_date := p_task_assignment_periods_in(i).start_date;
870 l_period_end_date := p_task_assignment_periods_in(i).end_date;
871
872 --dbms_output.put_line('before l_period_name:'||l_period_name);
873 --dbms_output.put_line('before l_period_start_date:'||l_period_start_date);
874 --dbms_output.put_line('before l_period_end_date:'||l_period_end_date);
875
876 Check_Period_Details(
877 P_BUDGET_VERSION_ID => l_budget_version_id,
878 p_period_set_name => l_period_set_name,
879 p_time_phase_code => l_time_phased_code,
880 p_accounted_period_type => l_accounted_period_type,
881 p_pa_period_type => l_pa_period_type,
882 p_task_name => l_task_name,
883 p_rlm_alias => l_rlm_alias,
884 P_PERIOD_NAME => l_period_name,
885 P_PERIOD_START_DATE => l_period_start_date,
886 P_PERIOD_END_DATE => l_period_end_date,
887 x_return_status => l_return_status
888 );
889
890 -- dbms_output.put_line('after l_period_name:'||l_period_name);
891 -- dbms_output.put_line('after l_period_start_date:'||l_period_start_date);
892 -- dbms_output.put_line('after l_period_end_date:'||l_period_end_date);
893
894 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
895 RAISE FND_API.G_EXC_ERROR;
896 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
897 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
898 END IF;
899 -- End of Bug 3866222: validate period data
900
901 -- Bug 8498316 - Raise an error if data is passed for a period which
902 -- is prior to the period on which the etc start date falls.
903 IF P_DEBUG_MODE = 'Y' AND (li_curr_level <= 3) THEN
904 pa_debug.g_err_stage := 'ETC Start Date - ' || l_etc_start_date;
905 pa_debug.write(G_PKG_NAME ,pa_debug.g_err_stage,3);
906 END IF;
907
908 IF p_task_assignment_periods_in(i).resource_list_member_id IS NOT NULL AND
909 p_task_assignment_periods_in(i).resource_list_member_id <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM THEN
910 OPEN get_resource_alias(p_task_assignment_periods_in(i).resource_list_member_id);
911 FETCH get_resource_alias INTO l_rlm_alias;
912 CLOSE get_resource_alias;
913 END IF;
914
915 -- ETC Start Date is greater than period end date, raise error
916 -- This is now consistent with the self service front end behaviour and with
917 -- the code in the Calculate API
918 IF (((l_etc_start_date IS NOT NULL) AND (l_period_end_date IS NOT NULL)) AND
919 (l_etc_start_date > l_period_end_date)) THEN
920 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
921 PA_UTILS.ADD_MESSAGE
922 (
923 p_app_short_name => 'PA',
924 p_msg_name => 'PA_FP_ETC_SPREAD_DATE_ERR',
925 p_token1 => 'G_PROJECT_NAME' ,
926 p_value1 => l_project_name,
927 p_token2 => 'G_TASK_NAME',
928 p_value2 => l_task_name,
929 p_token3 => 'G_RESOURCE_NAME',
930 p_value3 => l_rlm_alias,
931 p_token4 => 'G_SPREAD_FROM_DATE',
932 p_value4 => l_etc_start_date
933 );
934 x_return_status := FND_API.G_RET_STS_ERROR;
935 RAISE FND_API.G_EXC_ERROR;
936 END IF;
937 END IF;
938
939 /* Commenting for bug 8498316
940
941 -- Ignore the period line if progress/actual exists in period after this line
942 l_progress_safe := 'Y';
943 l_start_date := Null;
944
945 OPEN C_Prog_Date(l_resource_assignment_id_tbl(i));
946 FETCH C_Prog_Date INTO C_Prog_Date_Rec;
947
948 IF C_Prog_Date%FOUND AND
949 -- Replaced l_period_start_date with l_period_end_date for Bug# 6432606
950 -- l_period_start_date < C_Prog_Date_Rec.as_of_date THEN
951 l_period_end_date < C_Prog_Date_Rec.as_of_date THEN
952
953 l_progress_safe := 'N';
954 l_prog_resource_assignment_id := l_resource_assignment_id_tbl(i);
955
956 END IF;
957
958 CLOSE C_Prog_Date;
959 -- End of progress/actual check
960 */
961
962 -- IF l_progress_safe <> 'N' THEN
963
964 IF p_task_assignment_periods_in(i).resource_list_member_id IS NOT NULL AND
965 p_task_assignment_periods_in(i).resource_list_member_id <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM THEN
966
967 l_resource_list_id := PA_TASK_ASSIGNMENT_UTILS.Get_WP_Resource_List_Id(l_project_id);
968
969 PA_PLANNING_RESOURCE_UTILS.check_list_member_on_list(
970 p_resource_list_id => l_resource_list_id,
971 p_resource_list_member_id => p_task_assignment_periods_in(i).resource_list_member_id,
972 p_project_id => l_project_id,
973 p_chk_enabled => 'Y',
974 x_resource_list_member_id => l_resource_list_member_id,
975 x_valid_member_flag => l_valid_member_flag,
976 x_return_status => x_return_status,
977 x_msg_count => x_msg_count,
978 x_msg_data => x_msg_data ) ;
979
980 IF l_valid_member_flag <> 'Y' THEN
981 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
982 THEN
983 PA_UTILS.ADD_MESSAGE
984 (p_app_short_name => 'PA',
985 p_msg_name => 'PA_INVALID_RES_LIST_MEM_ID'
986 );
987 x_return_status := FND_API.G_RET_STS_ERROR;
988 RAISE FND_API.G_EXC_ERROR;
989 END IF;
990 END IF;
991
992 ELSIF p_task_assignment_periods_in(i).pa_task_assignment_id IS NULL AND
993 (p_task_assignment_periods_in(i).resource_list_member_id IS NULL OR
994 p_task_assignment_periods_in(i).resource_list_member_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM) THEN
995 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
996 THEN
997 PA_UTILS.ADD_MESSAGE
998 (p_app_short_name => 'PA',
999 p_msg_name => 'PA_INVALID_RES_LIST_MEM_ID'
1000 );
1001 x_return_status := FND_API.G_RET_STS_ERROR;
1002 RAISE FND_API.G_EXC_ERROR;
1003 END IF;
1004 END IF;
1005
1006
1007 -- ADD CBS VALIDATIONS HERE 16598322
1008 IF l_cbs_enabled_project = 'Y' THEN
1009 IF p_task_assignment_periods_in(i).cbs_element_id IS NOT NULL AND
1010 p_task_assignment_periods_in(i).cbs_element_id <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM THEN
1011
1012 PA_ALTERNATE_TASK_PVT.Is_Cost_Element_Valid(
1013 p_Cbs_Element_Id =>p_task_assignment_periods_in(i).cbs_element_id,
1014 p_Task_Id =>l_d_task_id,
1015 p_Project_Id =>l_project_id,
1016 x_return_status => x_return_status,
1017 x_msg_count => x_msg_count,
1018 x_msg_data => x_msg_data
1019 );
1020
1021 IF x_return_status <> 'S' THEN
1022 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA'
1023 ,p_msg_name => x_msg_data
1024 );
1025 x_return_status := FND_API.G_RET_STS_ERROR;
1026 RAISE FND_API.G_EXC_ERROR;
1027 END IF;
1028
1029 ELSE
1030 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA'
1031 ,p_msg_name => 'PA_CBS_WP__COST_CODE_MNDTR'
1032 ,p_token1 => 'RES_NAME'
1033 ,p_value1 => PA_TASK_UTILS.get_resource_name(p_task_assignment_periods_in(i).resource_list_member_id)
1034 );
1035 x_return_status := FND_API.G_RET_STS_ERROR;
1036 RAISE FND_API.G_EXC_ERROR;
1037
1038 END IF;
1039
1040 END IF;
1041 IF l_cbs_enabled_project = 'Y' THEN
1042 /* Bug 8498316 - Handle the variable l_prog_resource_assignment_id
1043 * If progress has been applied for this task assignment, then set
1044 * l_prog_resource_assignment_id to l_resource_assignment_id_tbl(i)
1045 * for later validations to kick in.
1046 */
1047 IF 'Y' = pa_progress_utils.check_prog_exists_and_delete(
1048 p_project_id => l_project_id,
1049 p_task_id => l_d_task_id,
1050 p_object_type => 'PA_ASSIGNMENTS',
1051 p_object_id => p_task_assignment_periods_in(i).resource_list_member_id,
1052 p_cbs_element_id => p_task_assignment_periods_in(i).cbs_element_id,
1053 p_structure_type => 'WORKPLAN',
1054 p_delete_progress_flag => 'N'
1055 ) THEN
1056 l_prog_resource_assignment_id := l_resource_assignment_id_tbl(i);
1057 END IF;
1058 ELSE
1059 /* Bug 8498316 - Handle the variable l_prog_resource_assignment_id
1060 * If progress has been applied for this task assignment, then set
1061 * l_prog_resource_assignment_id to l_resource_assignment_id_tbl(i)
1062 * for later validations to kick in.
1063 */
1064 IF 'Y' = pa_progress_utils.check_prog_exists_and_delete(
1065 p_project_id => l_project_id,
1066 p_task_id => l_d_task_id,
1067 p_object_type => 'PA_ASSIGNMENTS',
1068 p_object_id => p_task_assignment_periods_in(i).resource_list_member_id,
1069 p_structure_type => 'WORKPLAN',
1070 p_delete_progress_flag => 'N'
1071 ) THEN
1072 l_prog_resource_assignment_id := l_resource_assignment_id_tbl(i);
1073 END IF;
1074 END IF;
1075
1076
1077 -- 10/22/04: To handle only lines after the progress as of date
1078 l_FINPLAN_LINES_TAB(l_finplan_line_count).resource_assignment_id := pa_task_assignments_pvt.pfnum(l_resource_assignment_id_tbl(i));
1079
1080 OPEN C_Get_res_info(l_FINPLAN_LINES_TAB(l_finplan_line_count).resource_assignment_id);
1081 FETCH C_Get_res_info INTO C_Get_res_info_rec;
1082 CLOSE C_Get_res_info;
1083
1084
1085
1086 l_txn_currency_code := NULL;
1087
1088 OPEN C_Get_txn_Currency(l_resource_assignment_id_tbl(i));
1089 FETCH C_Get_txn_Currency INTO l_txn_currency_code; --, l_txn_start_date; bug 6407736 - skkoppul
1090 CLOSE C_Get_txn_Currency;
1091
1092 OPEN C_Workplan_Costs_enabled(l_budget_version_id);
1093 FETCH C_Workplan_Costs_enabled into C_Workplan_Costs_rec;
1094 CLOSE C_Workplan_Costs_enabled;
1095
1096 --dbms_output.put_line('l_FINPLAN_LINES_TAB(l_finplan_line_count).resource_assignment_id:' || l_FINPLAN_LINES_TAB(l_finplan_line_count).resource_assignment_id);
1097 l_FINPLAN_LINES_TAB(l_finplan_line_count).system_reference1 := pa_task_assignments_pvt.pfnum(l_d_task_id);
1098 --dbms_output.put_line('l_FINPLAN_LINES_TAB(l_finplan_line_count).system_reference1: ' || l_FINPLAN_LINES_TAB(l_finplan_line_count).system_reference1);
1099 l_FINPLAN_LINES_TAB(l_finplan_line_count).system_reference2 := NVL(pa_task_assignments_pvt.pfnum(p_task_assignment_periods_in(i).resource_list_member_id), C_Get_res_info_rec.resource_list_member_id);
1100 --dbms_output.put_line('l_FINPLAN_LINES_TAB(l_finplan_line_count).system_reference2:' || l_FINPLAN_LINES_TAB(l_finplan_line_count).system_reference2);
1101
1102 -- aDDED FOR cbs ::pUBLIC_a
1103
1104 l_FINPLAN_LINES_TAB(l_finplan_line_count).start_date := pa_task_assignments_pvt.pfdate(l_period_start_date) ;
1105 --dbms_output.put_line('l_FINPLAN_LINES_TAB(l_finplan_line_count).start_date:' || l_FINPLAN_LINES_TAB(l_finplan_line_count).start_date);
1106 l_FINPLAN_LINES_TAB(l_finplan_line_count).end_date := pa_task_assignments_pvt.pfdate(l_period_end_date) ;
1107 --dbms_output.put_line('l_FINPLAN_LINES_TAB(l_finplan_line_count).end_date:' || l_FINPLAN_LINES_TAB(l_finplan_line_count).end_date);
1108 l_FINPLAN_LINES_TAB(l_finplan_line_count).quantity := pa_task_assignments_pvt.pfnum(p_task_assignment_periods_in(i).quantity) ;
1109 --dbms_output.put_line('l_FINPLAN_LINES_TAB(l_finplan_line_count).quantity:' || l_FINPLAN_LINES_TAB(l_finplan_line_count).quantity);
1110 l_FINPLAN_LINES_TAB(l_finplan_line_count).period_name := pa_task_assignments_pvt.pfchar(l_period_name) ;
1111 --dbms_output.put_line('l_FINPLAN_LINES_TAB(l_finplan_line_count).period_name:' || l_FINPLAN_LINES_TAB(l_finplan_line_count).period_name);
1112
1113 IF l_prog_resource_assignment_id = l_resource_assignment_id_tbl(i) THEN
1114 --new message case bug 3855080
1115 IF pa_task_assignments_pvt.pfchar(p_task_assignment_periods_in(i).txn_currency_code) <> l_txn_currency_code THEN
1116 PA_UTILS.ADD_MESSAGE
1117 (p_app_short_name => 'PA',
1118 p_msg_name => 'PA_TA_SAME_CURR_PROG_ERR',
1119 p_token1 => 'RESOURCE_REF',
1120 p_value1 => p_task_assignment_periods_in(i).pm_task_asgmt_reference
1121
1122 );
1123 ELSE
1124 l_FINPLAN_LINES_TAB(l_finplan_line_count).txn_currency_code := l_txn_currency_code ;
1125 END IF;
1126 ELSIF l_prog_resource_assignment_id <> l_resource_assignment_id_tbl(i) AND
1127 l_old_resource_assignment_id <> l_resource_assignment_id_tbl(i) THEN
1128
1129 IF C_Get_res_info_rec.rate_based_flag = 'Y' AND C_Workplan_Costs_rec.enabled_flag = 'N' THEN
1130 l_FINPLAN_LINES_TAB(l_finplan_line_count).txn_currency_code := NVL(l_txn_currency_code, l_project_currency_code) ;
1131 ELSE
1132 l_FINPLAN_LINES_TAB(l_finplan_line_count).txn_currency_code :=
1133 NVL(NVL(pa_task_assignments_pvt.pfchar(p_task_assignment_periods_in(i).txn_currency_code),
1134 l_txn_currency_code), l_project_currency_code) ;
1135 END IF;
1136 ELSIF l_prog_resource_assignment_id <> l_resource_assignment_id_tbl(i) AND
1137 l_old_resource_assignment_id = l_resource_assignment_id_tbl(i) THEN
1138 --new message case bug 3855080
1139 IF pa_task_assignments_pvt.pfchar(p_task_assignment_periods_in(i).txn_currency_code) <> l_old_txn_currency THEN
1140 PA_UTILS.ADD_MESSAGE
1141 (p_app_short_name => 'PA',
1142 p_msg_name => 'PA_TA_SAME_CURR_ERR',
1143 p_token1 => 'RESOURCE_REF',
1144 p_value1 => p_task_assignment_periods_in(i).pm_task_asgmt_reference
1145 );
1146 ELSE
1147 l_FINPLAN_LINES_TAB(l_finplan_line_count).txn_currency_code := l_old_txn_currency;
1148 END IF;
1149 ELSE
1150 l_FINPLAN_LINES_TAB(l_finplan_line_count).txn_currency_code := NVL(l_txn_currency_code, l_project_currency_code) ;
1151 END IF;
1152
1153
1154
1155 --dbms_output.put_line('l_FINPLAN_LINES_TAB(l_finplan_line_count).txn_currency_code:' || l_FINPLAN_LINES_TAB(l_finplan_line_count).txn_currency_code);
1156 l_FINPLAN_LINES_TAB(l_finplan_line_count).txn_raw_cost := pa_task_assignments_pvt.pfnum(p_task_assignment_periods_in(i).txn_raw_cost) ;
1157 --dbms_output.put_line('l_FINPLAN_LINES_TAB(l_finplan_line_count).txn_raw_cost:' || l_FINPLAN_LINES_TAB(l_finplan_line_count).txn_raw_cost);
1158 l_FINPLAN_LINES_TAB(l_finplan_line_count).txn_burdened_cost := pa_task_assignments_pvt.pfnum(p_task_assignment_periods_in(i).txn_burdened_cost) ;
1159 --dbms_output.put_line('l_FINPLAN_LINES_TAB(l_finplan_line_count).txn_burdened_cost:' || l_FINPLAN_LINES_TAB(l_finplan_line_count).txn_burdened_cost);
1160
1161 l_old_txn_currency := l_FINPLAN_LINES_TAB(l_finplan_line_count).txn_currency_code;
1162
1163 --dbms_output.put_line('End of Loop');
1164
1165 IF P_DEBUG_MODE = 'Y' AND (li_curr_level <= 3) THEN
1166 pa_debug.g_err_stage:='l_FINPLAN_LINES_TAB(l_finplan_line_count).resource_assignment_id: ' || l_FINPLAN_LINES_TAB(l_finplan_line_count).resource_assignment_id;
1167 pa_debug.write(G_PKG_NAME ,pa_debug.g_err_stage,3);
1168
1169
1170 pa_debug.g_err_stage:='l_FINPLAN_LINES_TAB(l_finplan_line_count).system_reference1 (task_id): ' || l_FINPLAN_LINES_TAB(l_finplan_line_count).system_reference1;
1171 pa_debug.write(G_PKG_NAME ,pa_debug.g_err_stage,3);
1172
1173
1174 pa_debug.g_err_stage:='l_FINPLAN_LINES_TAB(l_finplan_line_count).system_reference2 (resource_list_member_id): ' || l_FINPLAN_LINES_TAB(l_finplan_line_count).system_reference2;
1175 pa_debug.write(G_PKG_NAME ,pa_debug.g_err_stage,3);
1176
1177
1178 pa_debug.g_err_stage:='l_FINPLAN_LINES_TAB(l_finplan_line_count).start_date: ' || l_FINPLAN_LINES_TAB(l_finplan_line_count).start_date;
1179 pa_debug.write(G_PKG_NAME ,pa_debug.g_err_stage,3);
1180
1181
1182 pa_debug.g_err_stage:='l_FINPLAN_LINES_TAB(l_finplan_line_count).end_date: ' || l_FINPLAN_LINES_TAB(l_finplan_line_count).end_date;
1183 pa_debug.write(G_PKG_NAME ,pa_debug.g_err_stage,3);
1184
1185
1186 pa_debug.g_err_stage:='l_FINPLAN_LINES_TAB(l_finplan_line_count).period_name: ' || l_FINPLAN_LINES_TAB(l_finplan_line_count).period_name;
1187 pa_debug.write(G_PKG_NAME ,pa_debug.g_err_stage,3);
1188
1189
1190 pa_debug.g_err_stage:='l_FINPLAN_LINES_TAB(l_finplan_line_count).txn_currency_code: ' || l_FINPLAN_LINES_TAB(l_finplan_line_count).txn_currency_code;
1191 pa_debug.write(G_PKG_NAME ,pa_debug.g_err_stage,3);
1192
1193 pa_debug.g_err_stage:='l_FINPLAN_LINES_TAB(l_finplan_line_count).txn_raw_cost: ' || l_FINPLAN_LINES_TAB(l_finplan_line_count).txn_raw_cost;
1194 pa_debug.write(G_PKG_NAME ,pa_debug.g_err_stage,3);
1195
1196 pa_debug.g_err_stage:='l_FINPLAN_LINES_TAB(l_finplan_line_count).txn_burdened_cost: ' || l_FINPLAN_LINES_TAB(l_finplan_line_count).txn_burdened_cost;
1197 pa_debug.write(G_PKG_NAME ,pa_debug.g_err_stage,3);
1198 END IF;
1199
1200 val_index := val_index + 1;
1201
1202 p_task_assignment_tbl(val_index).resource_assignment_id := l_FINPLAN_LINES_TAB(l_finplan_line_count).resource_assignment_id;
1203 p_task_assignment_tbl(val_index).override_currency_code := l_FINPLAN_LINES_TAB(l_finplan_line_count).txn_currency_code;
1204
1205 --For purposes of validation of updation override is passed as not null & defaulted below..discussed as per Sheenie 08/25/04
1206 p_task_assignment_tbl(val_index).cost_rate_override := 1;
1207 p_task_assignment_tbl(val_index).burdened_rate_override := 1;
1208 p_task_assignment_tbl(val_index).total_quantity := l_FINPLAN_LINES_TAB(l_finplan_line_count).quantity;
1209
1210
1211 -- Bug Fix 5638541.
1212 -- removed the hard coded apps.
1213 -- APPS.PA_TASK_ASSIGNMENT_UTILS.VALIDATE_UPDATE_ASSIGNMENT ( P_TASK_ASSIGNMENT_TBL, X_RETURN_STATUS );
1214 -- Paramererized arguments for Bug 6856934
1215 PA_TASK_ASSIGNMENT_UTILS.VALIDATE_UPDATE_ASSIGNMENT ( p_task_assignment_tbl => P_TASK_ASSIGNMENT_TBL,
1216 x_return_status => X_RETURN_STATUS );
1217 -- End of Bug Fix 5638541.
1218
1219
1220 IF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1221 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1222 ELSIF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
1223 RAISE FND_API.G_EXC_ERROR;
1224 END IF;
1225
1226 --Prior to calling do resets
1227
1228 /* OPEN C_Prog_Date(l_resource_assignment_id_tbl(i));
1229 FETCH C_Prog_Date INTO C_Prog_Date_Rec;
1230 CLOSE C_Prog_Date;
1231 */
1232
1233 -- Bug 3954155 Should not access a closed cursor
1234 -- IF C_Prog_Date_Rec.as_of_date IS NOT NULL THEN
1235 -- delete from pa_budget_lines
1236 -- where resource_assignment_id = l_resource_assignment_id_tbl(i)
1237 -- and start_date > C_Prog_Date_Rec.as_of_date;
1238 -- ELSE
1239 -- delete from pa_budget_lines where resource_assignment_id = l_resource_assignment_id_tbl(i);
1240 -- END IF;
1241
1242 /* commented for bug#13059417
1243 update pa_resource_assignments set sp_fixed_date=null,spread_curve_id =null, record_version_number=(record_version_number+1)
1244 where resource_assignment_id = l_FINPLAN_LINES_TAB(l_finplan_line_count).resource_assignment_id; */ -- commented for bug#13059417
1245
1246 --bug#13059417. changes starts here.
1247 l_spread_curve_id := NULL;
1248 l_sp_fixed_date := NULL;
1249
1250 OPEN C_Spread_Curve_Id(l_resource_assignment_id_tbl(i));
1251 FETCH C_Spread_Curve_Id INTO l_spread_curve_id, l_sp_fixed_date;
1252 CLOSE C_Spread_Curve_Id;
1253
1254 update pa_resource_assignments set sp_fixed_date=l_sp_fixed_date, spread_curve_id =l_spread_curve_id, record_version_number=(record_version_number+1)
1255 where resource_assignment_id = l_FINPLAN_LINES_TAB(l_finplan_line_count).resource_assignment_id;
1256
1257 --bug#13059417. changes ends here.
1258
1259 -- 10/22/04: Increment the l_finplan_lines table coutn
1260 l_finplan_line_count := l_finplan_line_count+1;
1261
1262 -- END IF; --IF l_progress_safe <> 'N' THEN
1263
1264 l_old_resource_assignment_id := l_resource_assignment_id_tbl(i);
1265
1266 END LOOP;
1267 --dbms_output.put_line('After end of Loop');
1268
1269
1270
1271 IF P_DEBUG_MODE = 'Y' AND (li_curr_level <= 3) THEN
1272 pa_debug.g_err_stage:='Calling context.' ||l_calling_context;
1273 pa_debug.write(G_PKG_NAME ,pa_debug.g_err_stage,3);
1274
1275 pa_debug.g_err_stage:='Return status B4 add fin plan lines:' ||x_return_status;
1276 pa_debug.write(G_PKG_NAME ,pa_debug.g_err_stage,3);
1277 END IF;
1278
1279 --dbms_output.put_line('Calling PA_FIN_PLAN_PVT.ADD_FIN_PLAN_LINES');
1280 PA_FIN_PLAN_PVT.ADD_FIN_PLAN_LINES
1281 ( PA_FP_CONSTANTS_PKG.G_AMG_API,
1282 l_budget_version_id,
1283 l_FINPLAN_LINES_TAB,
1284 X_RETURN_STATUS,
1285 X_MSG_COUNT,
1286 X_MSG_DATA );
1287
1288 --dbms_output.put_line('After returning from returning from add fin plan lines. return status:' ||x_return_status );
1289
1290 IF P_DEBUG_MODE = 'Y' AND (li_curr_level <= 3) THEN
1291 pa_debug.g_err_stage:='Return status after add fin plan lines:' ||x_return_status;
1292 pa_debug.write(G_PKG_NAME ,pa_debug.g_err_stage,3);
1293 END IF;
1294
1295 IF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1296 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1297 ELSIF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
1298 RAISE FND_API.G_EXC_ERROR;
1299 END IF;
1300
1301
1302 --dbms_output.put_line(' Internal Add Tables index' || k_index);
1303
1304
1305 FOR i in 1..l_FINPLAN_LINES_TAB.COUNT LOOP
1306
1307 --dbms_output.put_line('Obtaining Task Assignment Ids index:' || i);
1308 IF l_cbs_enabled_project = 'Y' THEN -- :: Public_Api_Changes
1309 open c_cur_out( l_struct_elem_version_id, l_project_id, l_task_elem_version_id_tbl(i), p_task_assignment_periods_in(i).resource_list_member_id );
1310 fetch c_cur_out into c_rec_out;
1311
1312 IF c_cur_out%FOUND THEN
1313 --dbms_output.put_line('Success on index:' || i);
1314 p_task_assignment_periods_out(i).return_status := 'S';
1315 -- Bug Fix 5638541.
1316 -- removed the hard coded apps.
1317 -- APPS.PA_TASK_ASSIGNMENTS_PUB.g_asgmts_periods_out_tbl(i).return_status:= 'S';
1318 PA_TASK_ASSIGNMENTS_PUB.g_asgmts_periods_out_tbl(i).return_status:= 'S';
1319 -- End of Bug Fix 5638541.
1320
1321 ELSE
1322 --dbms_output.put_line('Errored on index:' || i);
1323 p_task_assignment_periods_out(i).return_status := 'E';
1324 -- Bug Fix 5638541.
1325 -- removed the hard coded apps.
1326 -- APPS.PA_TASK_ASSIGNMENTS_PUB.g_asgmts_periods_out_tbl(i).return_status:= 'E';
1327 PA_TASK_ASSIGNMENTS_PUB.g_asgmts_periods_out_tbl(i).return_status:= 'E';
1328 -- End of Bug Fix 5638541.
1329 END IF;
1330
1331 --dbms_output.put_line('Out resource_assignment_id:' || c_rec_out.resource_assignment_id);
1332 --dbms_output.put_line('Out resource alias:' || c_rec_out.alias);
1333
1334 p_task_assignment_periods_out(i).pa_task_assignment_id := c_rec_out.resource_assignment_id;
1335 p_task_assignment_periods_out(i).resource_alias := c_rec_out.alias;
1336
1337 -- Bug Fix 5638541.
1338 -- removed the hard coded apps.
1339 -- APPS.PA_TASK_ASSIGNMENTS_PUB.g_asgmts_periods_out_tbl(i).pa_task_assignment_id := c_rec_out.resource_assignment_id;
1340 -- APPS.PA_TASK_ASSIGNMENTS_PUB.g_asgmts_periods_out_tbl(i).resource_alias := c_rec_out.alias;
1341 PA_TASK_ASSIGNMENTS_PUB.g_asgmts_periods_out_tbl(i).pa_task_assignment_id := c_rec_out.resource_assignment_id;
1342 PA_TASK_ASSIGNMENTS_PUB.g_asgmts_periods_out_tbl(i).resource_alias := c_rec_out.alias;
1343 -- End of Bug Fix 5638541.
1344
1345
1346 close c_cur_out;
1347
1348
1349 ELSE
1350 open c_cur_out_for_cbs( l_struct_elem_version_id, l_project_id, l_task_elem_version_id_tbl(i), p_task_assignment_periods_in(i).resource_list_member_id, p_task_assignment_periods_in(i).cbs_element_id );
1351 fetch c_cur_out_for_cbs into c_rec_out_for_cbs;
1352
1353 IF c_cur_out_for_cbs%FOUND THEN
1354 --dbms_output.put_line('Success on index:' || i);
1355 p_task_assignment_periods_out(i).return_status := 'S';
1356 PA_TASK_ASSIGNMENTS_PUB.g_asgmts_periods_out_tbl(i).return_status:= 'S';
1357
1358 ELSE
1359 --dbms_output.put_line('Errored on index:' || i);
1360 p_task_assignment_periods_out(i).return_status := 'E';
1361 PA_TASK_ASSIGNMENTS_PUB.g_asgmts_periods_out_tbl(i).return_status:= 'E';
1362
1363 END IF;
1364
1365 p_task_assignment_periods_out(i).pa_task_assignment_id := c_rec_out_for_cbs.resource_assignment_id;
1366 p_task_assignment_periods_out(i).resource_alias := c_rec_out_for_cbs.alias;
1367 p_task_assignment_periods_out(i).cbs_element_id := p_task_assignment_periods_in(i).cbs_element_id;
1368
1369 PA_TASK_ASSIGNMENTS_PUB.g_asgmts_periods_out_tbl(i).pa_task_assignment_id := c_rec_out_for_cbs.resource_assignment_id;
1370 PA_TASK_ASSIGNMENTS_PUB.g_asgmts_periods_out_tbl(i).resource_alias := c_rec_out_for_cbs.alias;
1371 PA_TASK_ASSIGNMENTS_PUB.g_asgmts_periods_out_tbl(i).cbs_element_id := p_task_assignment_periods_in(i).cbs_element_id;
1372 -- End of Bug Fix 5638541.
1373
1374
1375 close c_cur_out_for_cbs;
1376
1377
1378 END IF;
1379
1380
1381 END LOOP;
1382
1383 --dbms_output.put_line('End of Create Task Assignments:');
1384
1385 EXCEPTION
1386
1387 WHEN FND_API.G_EXC_ERROR
1388 THEN
1389 ROLLBACK TO add_task_asgmt_periods;
1390
1391 x_return_status := FND_API.G_RET_STS_ERROR;
1392 IF P_DEBUG_MODE = 'Y' THEN
1393 PA_DEBUG.write_log (x_module => G_PKG_NAME
1394 ,x_msg => 'Expected Error:' || L_FuncProc || SQLERRM
1395 ,x_log_level => 5);
1396 END IF;
1397 FND_MSG_PUB.Count_And_Get
1398 ( p_count => x_msg_count ,
1399 p_data => x_msg_data );
1400
1401 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
1402 THEN
1403 ROLLBACK TO add_task_asgmt_periods;
1404
1405 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1406 IF P_DEBUG_MODE = 'Y' THEN
1407 PA_DEBUG.write_log (x_module => G_PKG_NAME
1408 ,x_msg => 'Unexpected Error:' || L_FuncProc || SQLERRM
1409 ,x_log_level => 5);
1410 END IF;
1411 FND_MSG_PUB.Count_And_Get
1412 ( p_count => x_msg_count ,
1413 p_data => x_msg_data );
1414
1415 WHEN OTHERS THEN
1416 ROLLBACK TO add_task_asgmt_periods;
1417
1418 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1419 IF P_DEBUG_MODE = 'Y' THEN
1420 PA_DEBUG.write_log (x_module => G_PKG_NAME
1421 ,x_msg => 'Unexpected Error:' || L_FuncProc || SQLERRM
1422 ,x_log_level => 5);
1423 END IF;
1424 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1425 THEN
1426 FND_MSG_PUB.add_exc_msg
1427 ( p_pkg_name => G_PKG_NAME
1428 , p_procedure_name => l_api_name );
1429
1430 END IF;
1431
1432 FND_MSG_PUB.Count_And_Get
1433 ( p_count => x_msg_count ,
1434 p_data => x_msg_data );
1435
1436 END CREATE_TASK_ASSIGNMENT_PERIODS;
1437
1438
1439 FUNCTION PFCHAR(P_CHAR IN VARCHAR2 DEFAULT to_char(NULL) ) RETURN VARCHAR2 IS
1440 begin
1441
1442
1443 if p_char IS NOT NULL and p_char = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR THEN
1444
1445 return to_char(NULL);
1446
1447 elsif p_char IS NOT NULL and p_char = FND_API.G_MISS_CHAR THEN
1448
1449 return to_char(NULL);
1450
1451 elsif p_char IS NULL THEN
1452
1453 return fnd_api.g_miss_char;
1454
1455 else
1456
1457 return p_char;
1458
1459 end if;
1460
1461 EXCEPTION WHEN OTHERS THEN
1462 RETURN P_CHAR;
1463
1464 END PFCHAR;
1465
1466 FUNCTION PFNUM(P_NUM IN NUMBER DEFAULT TO_NUMBER(NULL)) RETURN NUMBER IS
1467 begin
1468
1469
1470 --dbms_output.put_line('entered pfnum 1');
1471 if p_num IS NOT NULL and p_num = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM THEN
1472 --dbms_output.put_line('entered pfnum 2');
1473
1474 return to_number(NULL);
1475
1476 elsif p_num IS NOT NULL and p_num = FND_API.G_MISS_NUM THEN
1477 --dbms_output.put_line('entered pfnum 3');
1478
1479 return to_number(NULL);
1480
1481 elsif p_num IS NULL THEN
1482 --dbms_output.put_line('entered pfnum 4');
1483
1484 return fnd_api.g_miss_num;
1485
1486 else
1487 --dbms_output.put_line('entered pfnum 5');
1488
1489 return p_num;
1490
1491 end if;
1492
1493 EXCEPTION WHEN OTHERS THEN
1494 RETURN P_NUM;
1495
1496 END PFNUM;
1497
1498 FUNCTION PFDATE(P_DATE IN DATE DEFAULT TO_DATE(NULL)) RETURN DATE IS
1499 begin
1500
1501
1502 if p_date IS NOT NULL and p_date = PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE THEN
1503
1504 return to_date(NULL);
1505
1506 elsif p_date IS NOT NULL and p_date = FND_API.G_MISS_DATE THEN
1507
1508 return to_date(NULL);
1509
1510 elsif p_date IS NULL THEN
1511
1512 return fnd_api.g_miss_date;
1513
1514 else
1515
1516 return p_date;
1517
1518 end if;
1519
1520 EXCEPTION WHEN OTHERS THEN
1521 RETURN P_DATE;
1522
1523 END PFDATE;
1524
1525 /*
1526 FUNCTION GET_PERIOD_START_DATE(P_PERIOD_NAME IN VARCHAR2, P_BUDGET_VERSION_ID IN NUMBER) RETURN DATE IS
1527
1528 l_period_set_name gl_sets_of_books.period_set_name%type;
1529 l_accounted_period_type gl_sets_of_books.accounted_period_type%type;
1530 l_pa_period_type pa_implementations_all.pa_period_type%type;
1531 l_time_phase_code pa_proj_fp_options.cost_time_phased_code%type;
1532
1533
1534 cursor c_period_data IS
1535 SELECT gsb.period_set_name
1536 ,gsb.accounted_period_type
1537 ,pia.pa_period_type
1538 ,decode(pbv.version_type,
1539 'COST',ppfo.cost_time_phased_code,
1540 'REVENUE',ppfo.revenue_time_phased_code,
1541 ppfo.all_time_phased_code) time_phase_code
1542 ,glp.start_date period_start_date,glp.end_date period_end_date
1543 FROM gl_sets_of_books gsb
1544 ,pa_implementations_all pia
1545 ,pa_projects_all ppa
1546 ,pa_budget_versions pbv
1547 ,pa_proj_fp_options ppfo
1548 ,gl_periods glp
1549 WHERE ppa.project_id = pbv.project_id
1550 AND pbv.budget_version_id = ppfo.fin_plan_version_id
1551 AND nvl(ppa.org_id,-99) = nvl(pia.org_id,-99)
1552 AND gsb.set_of_books_id = pia.set_of_books_id
1553 AND pbv.budget_version_id = p_budget_version_id
1554 AND glp.period_set_name = gsb.period_set_name
1555 -- this condition is not required as
1556 -- period_set_name and period_name are the unique columns on gl_periods
1557 -- and glp.period_type = decode(pbv.version_type
1558 -- ,'COST', decode(ppfo.cost_time_phased_code,'G',gsb.accounted_period_type,'P',pia.pa_period_type)
1559 -- ,'REVENUE',decode(ppfo.revenue_time_phased_code,'G',gsb.accounted_period_type,'P',pia.pa_period_type)
1560 -- ,decode(ppfo.all_time_phased_code,'G',gsb.accounted_period_type,'P',pia.pa_period_type))
1561 --
1562 AND glp.period_name = p_period_name
1563 AND adjustment_period_flag = 'N' ;
1564
1565 c_period_data_rec c_period_data%rowtype;
1566
1567 v_return_status varchar2(3);
1568 v_msg_count number;
1569 v_msg_data varchar2(2000);
1570
1571 begin
1572 null;
1573
1574
1575 open c_period_data;
1576 fetch c_period_data into c_period_data_rec;
1577 close c_period_data;
1578
1579
1580 --dbms_output.put_line('st date' || c_period_data_rec.period_start_date );
1581
1582 --dbms_output.put_line('get_period_start_date end');
1583
1584 return c_period_data_rec.period_start_date;
1585
1586
1587 EXCEPTION WHEN OTHERS THEN
1588 -- --dbms_output.put_line('Exception');
1589 return to_date(null);
1590
1591 END GET_PERIOD_START_DATE;
1592 */
1593
1594 PROCEDURE lock_version( p_project_id IN NUMBER, p_structure_version_id IN NUMBER) IS
1595 cursor version_info IS
1596 select pev_structure_id, record_version_number,name
1597 from PA_proj_elem_ver_structure
1598 where element_version_id = p_structure_version_id
1599 and project_id = p_project_id;
1600
1601 l_api_name CONSTANT VARCHAR2(30) := 'lock_version';
1602
1603 l_record_version_number pa_proj_elem_ver_structure.record_version_number%type;
1604 l_name pa_proj_elem_ver_structure.name%type;
1605 x_return_status VARCHAR2(1);
1606 x_msg_data VARCHAR2(2000);
1607 x_msg_count NUMBER;
1608 str version_info%ROWTYPE;
1609 l_structure_id NUMBER;
1610
1611 L_FuncProc varchar2(200);
1612
1613 BEGIN
1614
1615 L_FuncProc := 'lock_version';
1616
1617 --dbms_output.put_line(' Entered lock version');
1618 IF P_DEBUG_MODE = 'Y' AND (li_curr_level <= 3) THEN
1619 pa_debug.g_err_stage:='Entered ' || L_FuncProc ;
1620 pa_debug.write(G_PKG_NAME ,pa_debug.g_err_stage,3);
1621 END IF;
1622
1623 x_return_status := FND_API.G_RET_STS_SUCCESS;
1624
1625 OPEN version_info;
1626 FETCH version_info INTO str;
1627 IF version_info%NOTFOUND THEN
1628 RAISE NO_DATA_FOUND;
1629 END IF;
1630
1631 l_record_version_number := str.record_version_number;
1632 l_name := str.name;
1633 l_structure_id := str.pev_structure_id;
1634
1635 CLOSE version_info;
1636
1637 --dbms_output.put_line(' lock version name' || l_name);
1638 --dbms_output.put_line(' lock version number' || l_record_version_number);
1639 --dbms_output.put_line(' lock structure id' || l_structure_id);
1640
1641 PA_PROJECT_STRUCTURE_PUB1.Update_Structure_Version_Attr
1642 (
1643 p_pev_structure_id => l_structure_id
1644 -- Commented for bug 4240130
1645 --,p_locked_status_code => 'LOCKED'
1646 ,p_structure_version_name => l_name
1647 ,p_record_version_number => l_record_version_number
1648 ,x_return_status => x_return_status
1649 ,x_msg_count => x_msg_count
1650 ,x_msg_data => x_msg_data
1651 );
1652 --dbms_output.put_line(' After lock version upd str vers attr call return status' || x_return_status);
1653 IF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1654 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1655 ELSIF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
1656 PA_UTILS.ADD_MESSAGE
1657 (p_app_short_name => 'PA',
1658 p_msg_name => 'PA_PS_STRUC_VER_LOCKED'
1659 );
1660 RAISE FND_API.G_EXC_ERROR;
1661 END IF;
1662
1663
1664
1665
1666 --dbms_output.put_line(' Leaving lock version');
1667 EXCEPTION
1668
1669 WHEN FND_API.G_EXC_ERROR
1670 THEN
1671
1672 x_return_status := FND_API.G_RET_STS_ERROR;
1673 IF P_DEBUG_MODE = 'Y' THEN
1674 PA_DEBUG.write_log (x_module => G_PKG_NAME
1675 ,x_msg => 'Expected Error:' || L_FuncProc || SQLERRM
1676 ,x_log_level => 5);
1677 END IF;
1678 FND_MSG_PUB.Count_And_Get
1679 ( p_count => x_msg_count ,
1680 p_data => x_msg_data );
1681
1682 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
1683 THEN
1684
1685 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1686 IF P_DEBUG_MODE = 'Y' THEN
1687 PA_DEBUG.write_log (x_module => G_PKG_NAME
1688 ,x_msg => 'Unexpected Error:' || L_FuncProc || SQLERRM
1689 ,x_log_level => 5);
1690 END IF;
1691 FND_MSG_PUB.Count_And_Get
1692 ( p_count => x_msg_count ,
1693 p_data => x_msg_data );
1694
1695 WHEN OTHERS THEN
1696
1697 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1698 IF P_DEBUG_MODE = 'Y' AND (li_curr_level <= 3) THEN
1699 PA_DEBUG.write_log (x_module => G_PKG_NAME
1700 ,x_msg => 'Unexpected Error:' || L_FuncProc || SQLERRM
1701 ,x_log_level => 5);
1702 END IF;
1703 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1704 THEN
1705 FND_MSG_PUB.add_exc_msg
1706 ( p_pkg_name => G_PKG_NAME
1707 , p_procedure_name => l_api_name );
1708
1709 END IF;
1710
1711 FND_MSG_PUB.Count_And_Get
1712 ( p_count => x_msg_count ,
1713 p_data => x_msg_data );
1714
1715
1716 END lock_version;
1717
1718
1719 /***********************************************************************
1720 * This API is invoked upon the following flows:
1721 * 1. Actual summerization for shared structure
1722 * 2. Submit or Apply progress for split structure
1723 *
1724 * It returns the appropriate resource assignment on which
1725 * actuals should be tracked against for the given rlm and task version
1726 * combination.
1727 * 1. Return the existing planned assignment if exists
1728 * 2. Otherwise, create the unplanned assignment using the given rlm
1729 * 2.1 In shared case, the unplanned assignment will be created
1730 * as the task level assignment with ta_display_flag = 'N' if
1731 * the wp resource list is None and the rlm is of the people class
1732 * 2.2 In split case, the unplanned assignment will be created
1733 * as the task level assignment with ta_display_flag = 'N' if
1734 * rlm is not passed into the API.
1735 **********************************************************************/
1736 PROCEDURE Derive_Task_Assignments
1737 ( p_project_id IN PA_PROJECTS_ALL.project_id%TYPE
1738 ,p_extraction_type IN VARCHAR2 DEFAULT NULL -- Added for bug 13419867
1739 ,p_task_version_id IN PA_PROJ_ELEMENT_VERSIONS.element_version_id%TYPE
1740 ,p_scheduled_start IN DATE
1741 ,p_scheduled_end IN DATE
1742 ,p_resource_class_code IN PA_RESOURCE_LIST_MEMBERS.resource_class_code%TYPE
1743 ,p_resource_list_member_id IN PA_RESOURCE_LIST_MEMBERS.resource_list_member_id%TYPE
1744 --CBS Change in Derive_Task_Assignments
1745 ,p_cbs_element_id IN PA_RESOURCE_ASSIGNMENTS.cbs_element_id%TYPE DEFAULT NULL
1746 -- End CBS Change in Derive_Task_Assignments
1747 ,p_unplanned_flag IN PA_RESOURCE_ASSIGNMENTS.unplanned_flag%TYPE
1748 ,x_resource_assignment_id OUT NOCOPY NUMBER -- 4537865 Added Nocopy hint
1749 ,x_task_version_id OUT NOCOPY NUMBER -- 4537865 Added Nocopy hint
1750 ,x_resource_list_member_id OUT NOCOPY NUMBER -- 4537865 Added Nocopy hint
1751 ,x_currency_code OUT NOCOPY VARCHAR2 -- 4537865 Added Nocopy hint
1752 ,x_rate_based_flag OUT NOCOPY VARCHAR2 -- 4537865 Added Nocopy hint
1753 ,x_rbs_element_id OUT NOCOPY NUMBER -- 4537865 Added Nocopy hint
1754 ,x_msg_count OUT NOCOPY NUMBER
1755 ,x_msg_data OUT NOCOPY VARCHAR2
1756 ,x_return_status OUT NOCOPY VARCHAR2
1757 )
1758 IS
1759
1760 CURSOR get_task_assignment(c_resource_list_member_id NUMBER,
1761 --CBS Change in Derive_Task_Assignments
1762 c_cbs_element_id NUMBER)
1763 -- End CBS Change in Derive_Task_Assignments
1764 IS
1765 SELECT resource_assignment_id,
1766 DECODE(ta_display_flag, 'N', wbs_element_version_id, NULL),
1767 rate_based_flag,
1768 rbs_element_id
1769 FROM pa_resource_assignments
1770 WHERE resource_list_member_id = c_resource_list_member_id
1771 --CBS Change in Derive_Task_Assignments
1772 AND nvl(cbs_element_id,-1) = nvl(c_cbs_element_id, -1) -- Bug 16743324
1773 --End CBS Change in Derive_Task_Assignments
1774 AND wbs_element_version_id = p_task_version_id
1775 AND ta_display_flag IS NOT NULL
1776 AND rownum = 1;
1777
1778 CURSOR get_task_level_rec IS
1779 SELECT resource_assignment_id, resource_list_member_id
1780 FROM pa_resource_assignments
1781 WHERE wbs_element_version_id = p_task_version_id
1782 AND ta_display_flag = 'N'
1783 AND rownum = 1;
1784
1785 CURSOR get_people_class_asgmt IS
1786 SELECT resource_assignment_id, resource_list_member_id
1787 FROM pa_resource_assignments
1788 WHERE wbs_element_version_id = p_task_version_id
1789 AND ta_display_flag = 'Y'
1790 AND resource_class_code = 'PEOPLE'
1791 AND rownum = 1;
1792
1793 CURSOR get_budget_version_id IS
1794 SELECT bv.budget_version_id
1795 FROM pa_budget_versions bv,
1796 pa_proj_element_versions ev,
1797 pa_proj_elem_ver_structure evs
1798 WHERE bv.project_id = p_project_id
1799 AND bv.wp_version_flag = 'Y'
1800 AND bv.project_structure_version_id = evs.element_version_id
1801 AND ev.project_id = p_project_id
1802 AND ev.element_version_id = p_task_version_id
1803 AND evs.project_id = ev.project_id
1804 AND ev.parent_structure_version_id = evs.element_version_id;
1805
1806 CURSOR get_struct_version_id IS
1807 SELECT ev.parent_structure_version_id
1808 FROM pa_proj_element_versions ev
1809 WHERE ev.project_id = p_project_id
1810 AND ev.element_version_id = p_task_version_id;
1811
1812 CURSOR get_txn_cur_code(c_resource_assignment_id NUMBER) IS
1813 SELECT txn_currency_code
1814 FROM pa_budget_lines
1815 where resource_assignment_id = c_resource_assignment_id
1816 and txn_currency_code is not null
1817 and rownum = 1;
1818
1819 CURSOR get_proj_cur_code IS
1820 SELECT project_currency_code
1821 FROM pa_projects_all
1822 where project_id = p_project_id;
1823
1824 CURSOR check_none_resource_list(c_resource_list_id NUMBER) IS
1825 SELECT uncategorized_flag
1826 from pa_resource_lists
1827 where resource_list_id = c_resource_list_id;
1828
1829 l_resource_assignment_id NUMBER := NULL;
1830 l_resource_list_member_id NUMBER := NULL;
1831 l_task_version_id NUMBER := NULL;
1832 l_ta_exists VARCHAR2(1) := 'N';
1833 l_bvid NUMBER(15) := NULL;
1834 l_struct_ver_id NUMBER(15) := NULL;
1835 l_in_resource_list_member_id NUMBER := NULL;
1836 l_in_resource_class_code VARCHAR2(30) := NULL;
1837 l_resource_list_id NUMBER := NULL;
1838 l_rate_based_flag VARCHAR2(1) := NULL;
1839 l_uncategorized_flag VARCHAR2(1) := 'N';
1840 l_toggle_ta_display_flag VARCHAR2(1) := 'N';
1841 l_rbs_element_id NUMBER := NULL;
1842 --CBS Change in Derive_Task_Assignments
1843 l_in_cbs_element_id NUMBER;
1844 --End CBS Change in Derive_Task_Assignments
1845
1846 TYPE l_number_tbl IS TABLE OF NUMBER;
1847 TYPE l_date_tbl IS TABLE OF DATE;
1848 TYPE l_varchar_tbl is TABLE OF VARCHAR2(1);
1849
1850 l_task_elem_version_id_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE(1);
1851 --CBS Change in Derive_Task_Assignments
1852 l_cbs_element_id_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE(1);
1853 --End CBS Change in Derive_Task_Assignments
1854 l_res_list_member_id_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE(1);
1855 l_planned_people_effort_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE(1);
1856 l_quantity_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE(1);
1857 l_start_date_tbl SYSTEM.PA_DATE_TBL_TYPE := SYSTEM.PA_DATE_TBL_TYPE(NULL);
1858 l_end_date_tbl SYSTEM.PA_DATE_TBL_TYPE := SYSTEM.PA_DATE_TBL_TYPE(NULL);
1859 l_unplanned_flag_tbl SYSTEM.PA_VARCHAR2_1_TBL_TYPE := SYSTEM.PA_VARCHAR2_1_TBL_TYPE(NULL);
1860
1861 BEGIN
1862
1863 x_return_status := FND_API.G_RET_STS_SUCCESS;
1864 x_msg_count := 0;
1865 x_msg_data := NULL;
1866 l_in_resource_list_member_id := p_resource_list_member_id;
1867 l_in_resource_class_code := p_resource_class_code;
1868 l_uncategorized_flag := 'N';
1869
1870 --dbms_output.put_line('derive TA, p_resource_list_member_id:'||p_resource_list_member_id);
1871 --dbms_output.put_line('derive TA, p_task_version_id:'||p_task_version_id);
1872 --dbms_output.put_line('derive TA, p_resource_class_code:'||p_resource_class_code);
1873
1874 -- Added to support Apply Progress flow in which when progress was
1875 -- entered against the task, rlm id will not be passed into this API
1876 -- upon Apply Progress. Should get the PEOPLE class rlm in this case.
1877 -- Also, in shared case, we need to check whether resource list
1878 -- is a None resource list if the given rlm is of the people class.
1879 IF p_resource_list_member_id IS NULL OR
1880 p_resource_list_member_id = FND_API.G_MISS_NUM OR
1881 p_resource_class_code = 'PEOPLE' THEN
1882
1883 l_resource_list_id := pa_task_assignment_utils.Get_WP_Resource_List_Id(p_project_id);
1884
1885 OPEN check_none_resource_list(l_resource_list_id);
1886 FETCH check_none_resource_list INTO l_uncategorized_flag;
1887 CLOSE check_none_resource_list;
1888
1889 --dbms_output.put_line('derive TA, l_uncategorized_flag:'||l_uncategorized_flag);
1890
1891 IF p_resource_list_member_id IS NULL OR
1892 p_resource_list_member_id = FND_API.G_MISS_NUM THEN
1893
1894 l_in_resource_list_member_id := PA_PLANNING_RESOURCE_UTILS.get_class_member_id(
1895 p_project_id => p_project_id,
1896 p_resource_list_id => l_resource_list_id,
1897 p_resource_class_code => 'PEOPLE');
1898 l_in_resource_class_code := 'PEOPLE';
1899
1900 END IF;
1901
1902 END IF;
1903
1904 --dbms_output.put_line('l_in_resource_list_member_id:'||l_in_resource_list_member_id);
1905
1906 --CBS Change in Derive_Task_Assignments
1907 l_in_cbs_element_id := p_cbs_element_id;
1908 --End CBS Change in Derive_Task_Assignments
1909 -- CASE 1: return the assignment if it already exists for the task and
1910 -- resource list member combination
1911 -- CBS Change in Derive_Task_Assignments Added l_in_cbs_element_id
1912 OPEN get_task_assignment(l_in_resource_list_member_id, l_in_cbs_element_id);
1913 FETCH get_task_assignment INTO l_resource_assignment_id, l_task_version_id, l_rate_based_flag, l_rbs_element_id;
1914 CLOSE get_task_assignment;
1915
1916 IF l_resource_assignment_id IS NOT NULL THEN
1917
1918 --dbms_output.put_line('CASE 1');
1919
1920 x_resource_assignment_id := l_resource_assignment_id;
1921 x_task_version_id := l_task_version_id;
1922 x_resource_list_member_id := l_in_resource_list_member_id;
1923 x_rate_based_flag := l_rate_based_flag;
1924 x_rbs_element_id := l_rbs_element_id;
1925
1926 -- CASE 2: otherwise, create the task assignment record and
1927 -- take care of the unplanned_flag
1928 ELSE
1929 --dbms_output.put_line('CASE 4');
1930 l_task_elem_version_id_tbl(1) := p_task_version_id;
1931 l_CBS_ELEMENT_ID_TBL(1) := p_cbs_element_id;
1932 l_res_list_member_id_tbl(1) := l_in_resource_list_member_id;
1933 l_planned_people_effort_tbl(1) := 0;
1934 l_quantity_tbl(1) := 0;
1935 l_start_date_tbl(1) := p_scheduled_start;
1936 l_end_date_tbl(1) := p_scheduled_end;
1937 l_unplanned_flag_tbl(1) := p_unplanned_flag;
1938
1939 --dbms_output.put_line('project_id:'||p_project_id);
1940 --dbms_output.put_line('p_task_version_id:'||p_task_version_id);
1941 --dbms_output.put_line('l_in_resource_list_member_id:'||l_in_resource_list_member_id);
1942 --dbms_output.put_line('p_scheduled_start:'||p_scheduled_start);
1943 --dbms_output.put_line('p_scheduled_end:'||p_scheduled_end);
1944 --dbms_output.put_line('p_unplanned_flag:'||p_unplanned_flag);
1945
1946 OPEN get_struct_version_id;
1947 FETCH get_struct_version_id INTO l_struct_ver_id;
1948 CLOSE get_struct_version_id;
1949
1950 OPEN get_budget_version_id;
1951 FETCH get_budget_version_id INTO l_bvid;
1952 CLOSE get_budget_version_id;
1953
1954 -- Bug 3849244
1955 -- Update ta_display_flag = 'Y' to make the created record
1956 -- a task effort record IF:
1957 -- 1. in split case, rlm id is passed in as NULL
1958 -- 2. in shared case, the RL is None and the rlm is of People class
1959 -- AND no assignment exists on the task version
1960 l_toggle_ta_display_flag := 'N';
1961 pa_task_assignment_utils.g_ta_display_flag := 'Y';
1962
1963 IF (p_resource_list_member_id IS NULL OR
1964 p_resource_list_member_id = FND_API.G_MISS_NUM OR
1965 l_uncategorized_flag = 'Y') AND
1966 pa_task_assignment_utils.Check_Asgmt_Exists_In_Task(p_task_version_id) = 'N' THEN
1967
1968 l_toggle_ta_display_flag := 'Y';
1969
1970 END IF;
1971
1972
1973 --dbms_output.put_line('l_bvid:'||l_bvid);
1974 --dbms_output.put_line('l_struct_ver_id:'||l_struct_ver_id);
1975
1976
1977 --dbms_output.put_line('pa_task_assignment_utils.g_ta_display_flag:'||pa_task_assignment_utils.g_ta_display_flag);
1978
1979 --dbms_output.put_line('CASE 4: before add planning transaction');
1980
1981 -- Bug 4286558
1982 -- skip progress update check in apply progress mode
1983 -- because apply progress can be done within process update
1984 PA_TASK_ASSIGNMENT_UTILS.g_apply_progress_flag := 'Y';
1985
1986 PA_FP_PLANNING_TRANSACTION_PUB.Add_Planning_Transactions(
1987 p_context => 'TASK_ASSIGNMENT'
1988 ,p_extraction_type => p_extraction_type /* Added for bug 13419867*/
1989 ,p_project_id => p_project_id
1990 ,p_budget_version_id => l_bvid
1991 , p_struct_elem_version_id => l_struct_ver_id
1992 ,p_task_elem_version_id_tbl => l_task_elem_version_id_tbl
1993 ,P_CBS_ELEMENT_ID_TBL => l_CBS_ELEMENT_ID_TBL --CBS Change in Derive_Task_Assignments
1994 ,p_resource_list_member_id_tbl => l_res_list_member_id_tbl
1995 ,p_planned_people_effort_tbl => l_planned_people_effort_tbl
1996 ,p_start_date_tbl => l_start_date_tbl
1997 ,p_end_date_tbl => l_end_date_tbl
1998 ,p_quantity_tbl => l_quantity_tbl
1999 ,p_unplanned_flag_tbl => l_unplanned_flag_tbl
2000 ,x_return_status => x_return_status
2001 ,x_msg_count => x_msg_count
2002 ,x_msg_data => x_msg_data
2003 );
2004 pa_task_assignment_utils.g_ta_display_flag := 'N';
2005 PA_TASK_ASSIGNMENT_UTILS.g_apply_progress_flag := 'N'; -- Bug 4286558
2006
2007 --dbms_output.put_line('CASE 4: after add planning transaciton'||x_return_status);
2008 -- dbms_output.put_line('pa_task_assignment_utils.g_ta_display_flag:'||pa_task_assignment_utils.g_ta_display_flag);
2009
2010
2011 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
2012
2013 OPEN get_task_assignment(l_in_resource_list_member_id, l_in_cbs_element_id);
2014 FETCH get_task_assignment INTO l_resource_assignment_id, l_task_version_id, l_rate_based_flag, l_rbs_element_id;
2015 CLOSE get_task_assignment;
2016
2017 --dbms_output.put_line('CASE 4: resource assignment id '|| l_resource_assignment_id);
2018 --dbms_output.put_line('CASE 4: l_task_version_id '|| l_task_version_id);
2019 -- Bug 3849244
2020 -- Update ta_display_flag = 'Y' to make the created record
2021 -- a task effort record IF:
2022 -- 1. in split case, rlm id is passed in as NULL
2023 -- 2. in shared case, the RL is None and the rlm is of People class
2024 -- AND no assignment exists on the task version
2025 IF l_toggle_ta_display_flag = 'Y' THEN
2026
2027 UPDATE pa_resource_assignments
2028 SET ta_display_flag = 'N'
2029 WHERE resource_assignment_id = l_resource_assignment_id;
2030
2031 END IF;
2032
2033 x_resource_assignment_id := l_resource_assignment_id;
2034 x_resource_list_member_id := l_in_resource_list_member_id;
2035 x_rate_based_flag := l_rate_based_flag;
2036 x_rbs_element_id := l_rbs_element_id;
2037
2038 END IF;
2039
2040 END IF; -- cases
2041
2042 -- Return the appropriate currency code
2043 OPEN get_txn_cur_code (x_resource_assignment_id);
2044 FETCH get_txn_cur_code INTO x_currency_code;
2045 CLOSE get_txn_cur_code;
2046
2047 IF x_currency_code IS NULL THEN
2048 OPEN get_proj_cur_code;
2049 FETCH get_proj_cur_code INTO x_currency_code;
2050 CLOSE get_proj_cur_code;
2051 END IF;
2052
2053 EXCEPTION
2054 WHEN OTHERS THEN
2055
2056 pa_task_assignment_utils.g_ta_display_flag := 'N';
2057 PA_TASK_ASSIGNMENT_UTILS.g_apply_progress_flag := 'N'; -- Bug 4286558
2058
2059 -- Set the excetption Message and the stack
2060 FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_TASK_ASSIGNMENTS_PVT.Derive_Task_Assignments'
2061 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
2062 --
2063 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2064
2065 -- Start 4537865
2066 x_resource_assignment_id := NULL ;
2067 x_task_version_id := NULL ;
2068 x_resource_list_member_id := NULL ;
2069 x_currency_code := NULL ;
2070 x_rate_based_flag := NULL ;
2071 x_rbs_element_id := NULL ;
2072
2073 -- End : 4537865
2074
2075 RAISE; -- This is optional depending on the needs
2076
2077 END Derive_Task_Assignments;
2078
2079
2080
2081
2082
2083 PROCEDURE Copy_Missing_Unplanned_Asgmts
2084 (
2085 p_project_id IN PA_PROJECTS_ALL.PROJECT_ID%TYPE,
2086 p_old_structure_version_id IN PA_PROJ_ELEM_VER_STRUCTURE.ELEMENT_VERSION_ID%TYPE,
2087 p_new_structure_version_id IN PA_PROJ_ELEM_VER_STRUCTURE.ELEMENT_VERSION_ID%TYPE,
2088 p_new_budget_version_id IN PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE DEFAULT NULL,
2089 x_msg_count OUT NOCOPY NUMBER,
2090 x_msg_data OUT NOCOPY VARCHAR2,
2091 x_return_status OUT NOCOPY VARCHAR2
2092 )
2093
2094 IS
2095
2096 l_old_budget_version_id PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE;
2097 l_new_budget_version_id PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE;
2098
2099 l_parent_struct_ver_id_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
2100 l_element_version_id_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
2101 l_resource_list_member_id_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
2102 l_planned_people_effort_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
2103 l_planning_start_date_tbl SYSTEM.PA_DATE_TBL_TYPE := SYSTEM.PA_DATE_TBL_TYPE();
2104 l_planning_end_date_tbl SYSTEM.PA_DATE_TBL_TYPE := SYSTEM.PA_DATE_TBL_TYPE();
2105 l_task_start_date_tbl SYSTEM.PA_DATE_TBL_TYPE := SYSTEM.PA_DATE_TBL_TYPE();
2106 l_task_end_date_tbl SYSTEM.PA_DATE_TBL_TYPE := SYSTEM.PA_DATE_TBL_TYPE();
2107 l_quantity_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
2108 l_unplanned_flag_tbl SYSTEM.PA_VARCHAR2_1_TBL_TYPE := SYSTEM.PA_VARCHAR2_1_TBL_TYPE();
2109 l_ta_display_flag_tbl SYSTEM.PA_VARCHAR2_1_TBL_TYPE := SYSTEM.PA_VARCHAR2_1_TBL_TYPE();
2110 l_cbs_element_id_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE(); -- Added for 16200605
2111
2112 k NUMBER;
2113
2114 CURSOR c_get_budget_version_id(structure_version_id PA_PROJ_ELEM_VER_STRUCTURE.ELEMENT_VERSION_ID%TYPE) IS
2115 SELECT budget_version_id
2116 FROM pa_budget_versions
2117 WHERE project_structure_version_id = structure_version_id
2118 AND wp_version_flag = 'Y';
2119
2120 CURSOR c_get_missing_asgmts(old_structure_version_id PA_PROJ_ELEM_VER_STRUCTURE.ELEMENT_VERSION_ID%TYPE,
2121 new_structure_version_id PA_PROJ_ELEM_VER_STRUCTURE.ELEMENT_VERSION_ID%TYPE,
2122 old_budget_version_id PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE,
2123 new_budget_version_id PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE) IS
2124 SELECT
2125 pev.element_version_id,
2126 ra_old.resource_list_member_id,
2127 0,
2128 ra_old.planning_start_date,
2129 ra_old.planning_end_date,
2130 pevs.scheduled_start_date,
2131 pevs.scheduled_finish_date,
2132 0,
2133 'Y',
2134 ra_old.ta_display_flag,
2135 ra_old.cbs_element_id -- Added for 16200605
2136 FROM
2137 pa_resource_assignments ra_old,
2138 pa_proj_element_versions pev,
2139 pa_proj_elem_ver_schedule pevs
2140 WHERE
2141 ra_old.budget_version_id = old_budget_version_id AND
2142 PA_PROGRESS_UTILS.Check_Prog_Exists_And_Delete
2143 (
2144 ra_old.project_id,
2145 ra_old.task_id,
2146 'PA_ASSIGNMENTS',
2147 ra_old.resource_list_member_id,
2148 'WORKPLAN',
2149 'N'
2150 ) = 'Y' AND
2151 pev.parent_structure_version_id = new_structure_version_id AND
2152 pev.proj_element_id = ra_old.task_id AND
2153 pevs.element_version_id = pev.element_version_id AND
2154 NOT EXISTS
2155 (
2156 SELECT
2157 ra_new.resource_assignment_id
2158 FROM
2159 pa_resource_assignments ra_new
2160 WHERE
2161 ra_new.resource_list_member_id = ra_old.resource_list_member_id AND
2162 NVL(ra_new.cbs_element_id,-1) = NVL(ra_old.cbs_element_id,-1) AND -- Added for 16200605
2163 ra_new.task_id = ra_old.task_id AND
2164 ra_new.project_id = ra_old.project_id AND
2165 ra_new.budget_version_id = new_budget_version_id
2166 );
2167
2168 BEGIN
2169
2170 x_return_status := FND_API.G_RET_STS_SUCCESS;
2171 x_msg_count := 0;
2172 x_msg_data := NULL;
2173
2174 IF p_old_structure_version_id IS NOT NULL THEN
2175
2176 IF p_new_budget_version_id IS NOT NULL THEN
2177
2178 l_new_budget_version_id := p_new_budget_version_id;
2179
2180 ELSE
2181
2182 OPEN c_get_budget_version_id(p_new_structure_version_id);
2183 FETCH c_get_budget_version_id INTO l_new_budget_version_id;
2184 CLOSE c_get_budget_version_id;
2185
2186 END IF; -- IF p_new_budget_version_id IS NOT NULL
2187
2188 OPEN c_get_budget_version_id(p_old_structure_version_id);
2189 FETCH c_get_budget_version_id INTO l_old_budget_version_id;
2190 CLOSE c_get_budget_version_id;
2191
2192 OPEN c_get_missing_asgmts(p_old_structure_version_id,
2193 p_new_structure_version_id,
2194 l_old_budget_version_id,
2195 l_new_budget_version_id);
2196 FETCH c_get_missing_asgmts BULK COLLECT INTO l_element_version_id_tbl,
2197 l_resource_list_member_id_tbl,
2198 l_planned_people_effort_tbl,
2199 l_planning_start_date_tbl,
2200 l_planning_end_date_tbl,
2201 l_task_start_date_tbl,
2202 l_task_end_date_tbl,
2203 l_quantity_tbl,
2204 l_unplanned_flag_tbl,
2205 l_ta_display_flag_tbl ,
2206 l_cbs_element_id_tbl; -- Added for 16200605
2207 CLOSE c_get_missing_asgmts;
2208
2209 IF l_element_version_id_tbl.COUNT > 0 THEN
2210 PA_FP_PLANNING_TRANSACTION_PUB.Add_Planning_Transactions
2211 (
2212 p_context => 'TASK_ASSIGNMENT',
2213 p_one_to_one_mapping_flag => 'Y',
2214 p_project_id => p_project_id,
2215 p_budget_version_id => l_new_budget_version_id,
2216 p_struct_elem_version_id => p_new_structure_version_id,
2217 p_task_elem_version_id_tbl => l_element_version_id_tbl,
2218 p_resource_list_member_id_tbl => l_resource_list_member_id_tbl,
2219 p_cbs_element_id_tbl => l_cbs_element_id_tbl, -- Added for 16200605
2220 p_planned_people_effort_tbl => l_planned_people_effort_tbl,
2221 p_start_date_tbl => l_task_start_date_tbl,
2222 p_end_date_tbl => l_task_end_date_tbl,
2223 p_planning_start_date_tbl => l_task_start_date_tbl,
2224 p_planning_end_date_tbl => l_task_end_date_tbl,
2225 p_quantity_tbl => l_quantity_tbl,
2226 p_unplanned_flag_tbl => l_unplanned_flag_tbl,
2227 p_skip_duplicates_flag => 'Y',
2228 x_return_status => x_return_status,
2229 x_msg_count => x_msg_count,
2230 x_msg_data => x_msg_data
2231 );
2232
2233 -- Workaround to the fact that Add_Planning_Transactions only accept planning_*_date
2234 -- which are then stemed to schedule_*_date in Validate_Create_Assignment
2235 FORALL i IN 1..l_element_version_id_tbl.COUNT
2236 UPDATE pa_resource_assignments
2237 SET planning_start_date = l_planning_start_date_tbl(i),
2238 planning_end_date = l_planning_end_date_tbl(i),
2239 ta_display_flag = l_ta_display_flag_tbl(i)
2240 WHERE wbs_element_version_id = l_element_version_id_tbl(i)
2241 AND resource_list_member_id = l_resource_list_member_id_tbl(i);
2242 END IF;
2243
2244 END IF; -- IF p_old_structure_version_id IS NOT NULL
2245
2246 EXCEPTION
2247 WHEN OTHERS THEN
2248 -- Set the exception message and the stack
2249 FND_MSG_PUB.Add_Exc_Msg( p_pkg_name => 'PA_TASK_ASSIGNMENTS_PVT.Copy_Missing_Unplanned_Asgmts',
2250 p_procedure_name => PA_DEBUG.G_Err_Stack );
2251 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2252 RAISE; -- This is optional depending on the needs
2253
2254 END Copy_Missing_Unplanned_Asgmts;
2255
2256
2257 PROCEDURE Check_Period_Details(
2258 P_BUDGET_VERSION_ID IN pa_budget_versions.budget_version_id%TYPE,
2259 p_period_set_name IN gl_periods.period_set_name%TYPE,
2260 p_time_phase_code IN pa_proj_fp_options.cost_time_phased_code%TYPE,
2261 p_accounted_period_type IN gl_periods.period_type%TYPE,
2262 p_pa_period_type IN gl_periods.period_type%TYPE,
2263 p_task_name IN pa_proj_elements.name%TYPE,
2264 p_rlm_alias IN pa_resource_list_members.alias%TYPE,
2265 P_PERIOD_NAME IN OUT NOCOPY VARCHAR2, -- 4537865
2266 P_PERIOD_START_DATE IN OUT NOCOPY DATE, -- 4537865
2267 P_PERIOD_END_DATE IN OUT NOCOPY DATE, -- 4537865
2268 x_return_status OUT NOCOPY VARCHAR2
2269 ) IS
2270
2271 l_period_name gl_periods.period_name%type := NULL;
2272 l_period_start_date gl_periods.start_date%type := NULL;
2273 l_period_end_date gl_periods.end_date%type := NULL;
2274
2275
2276 CURSOR get_period_name_from_dates IS
2277 SELECT start_date,
2278 end_date,
2279 period_name
2280 FROM gl_periods
2281 WHERE period_set_name = p_period_set_name
2282 AND period_type =
2283 decode(p_time_phase_code,'G',p_accounted_period_type,
2284 'P',p_pa_period_type)
2285 AND start_date = p_period_start_date
2286 AND end_date = p_period_end_date
2287 AND adjustment_period_flag = 'N';
2288
2289
2290 CURSOR get_period_dates_from_name IS
2291 SELECT start_date,
2292 end_date,
2293 period_name
2294 FROM gl_periods
2295 WHERE period_set_name = p_period_set_name
2296 AND period_type =
2297 decode(p_time_phase_code,'G',p_accounted_period_type,
2298 'P',p_pa_period_type)
2299 AND period_name = p_period_name
2300 AND adjustment_period_flag = 'N';
2301
2302
2303
2304 BEGIN
2305
2306 x_return_status := FND_API.G_RET_STS_SUCCESS;
2307
2308 IF p_time_phase_code <> PA_FP_CONSTANTS_PKG.G_TIME_PHASED_CODE_P
2309 AND p_time_phase_code <> PA_FP_CONSTANTS_PKG.G_TIME_PHASED_CODE_G THEN
2310 PA_UTILS.ADD_MESSAGE
2311 (p_app_short_name => 'PA',
2312 p_msg_name => 'PA_INVALID_TIMEPHASE_CODE'
2313 );
2314 RAISE FND_API.G_EXC_ERROR;
2315 END IF;
2316
2317 --dbms_output.put_line('p_period_name:'||p_period_name);
2318
2319 --1. get dates from period name
2320 IF p_period_name IS NOT NULL
2321 AND p_period_name <> FND_API.G_MISS_CHAR
2322 AND p_period_name <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR THEN
2323
2324 OPEN get_period_dates_from_name;
2325 FETCH get_period_dates_from_name
2326 INTO l_period_start_date, l_period_end_date, l_period_name;
2327 CLOSE get_period_dates_from_name;
2328
2329 --2. get period name from dates
2330 ELSIF p_period_start_date IS NOT NULL
2331 AND p_period_start_date <> FND_API.G_MISS_DATE
2332 AND p_period_start_date <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE THEN
2333
2334 OPEN get_period_name_from_dates;
2335 FETCH get_period_name_from_dates
2336 INTO l_period_start_date, l_period_end_date, l_period_name;
2337 CLOSE get_period_name_from_dates;
2338
2339 END IF;
2340
2341 IF l_period_start_date IS NULL THEN
2342 PA_UTILS.ADD_MESSAGE
2343 (p_app_short_name => 'PA',
2344 p_msg_name => 'PA_INVALID_PERIOD_ERR',
2345 p_token1 => 'TASK_NAME',
2346 p_value1 => p_task_name,
2347 p_token2 => 'PL_RES_ALIAS',
2348 p_value2 => p_rlm_alias,
2349 p_token3 => 'PERIOD_NAME',
2350 p_value3 => p_period_name,
2351 p_token4 => 'START_DATE',
2352 p_value4 => p_period_start_date,
2353 p_token5 => 'END_DATE',
2354 p_value5 => p_period_end_date
2355 );
2356 RAISE FND_API.G_EXC_ERROR;
2357 ELSE
2358
2359 P_PERIOD_NAME := l_period_name;
2360 P_PERIOD_START_DATE := l_period_start_date;
2361 P_PERIOD_END_DATE := l_period_end_date;
2362
2363 END IF;
2364
2365
2366 EXCEPTION
2367
2368 WHEN FND_API.G_EXC_ERROR THEN
2369 x_return_status := FND_API.G_RET_STS_ERROR;
2370
2371 WHEN OTHERS THEN
2372 -- Set the exception message and the stack
2373 FND_MSG_PUB.Add_Exc_Msg( p_pkg_name => 'PA_TASK_ASSIGNMENTS_PVT.Check_Period_Details',
2374 p_procedure_name => PA_DEBUG.G_Err_Stack );
2375 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2376
2377 P_PERIOD_NAME := NULL ; -- 4537865
2378 P_PERIOD_START_DATE := NULL ; -- 4537865
2379 P_PERIOD_END_DATE := NULL ; -- 4537865
2380
2381 RAISE; -- This is optional depending on the needs
2382
2383 END Check_Period_Details;
2384
2385
2386 END PA_TASK_ASSIGNMENTS_PVT;