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