[Home] [Help]
PACKAGE BODY: APPS.PA_ASSIGNMENTS_PVT
Source
1 PACKAGE BODY pa_assignments_pvt AS
2 /*$Header: PARAPVTB.pls 120.6.12010000.3 2008/08/22 08:45:24 sugupta ship $*/
3 --
4 P_DEBUG_MODE varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N'); /* Added Debug Profile Option variable initialization for bug#2674619 */
5 li_message_level NUMBER := 1;
6
7 PROCEDURE Create_Assignment
8 ( p_assignment_rec IN PA_ASSIGNMENTS_PUB.Assignment_Rec_Type
9 ,p_asgn_creation_mode IN VARCHAR2 := 'FULL'
10 ,p_unfilled_assignment_status IN pa_project_assignments.status_code%TYPE := FND_API.G_MISS_CHAR
11 ,p_resource_source_id IN NUMBER := FND_API.G_MISS_NUM
12 ,p_project_subteam_id IN pa_project_subteams.project_subteam_id%TYPE := FND_API.G_MISS_NUM
13 ,p_location_city IN pa_locations.city%TYPE := FND_API.G_MISS_CHAR
14 ,p_location_region IN pa_locations.region%TYPE := FND_API.G_MISS_CHAR
15 ,p_location_country_code IN pa_locations.country_code%TYPE := FND_API.G_MISS_CHAR
16 ,p_adv_action_set_id IN NUMBER := FND_API.G_MISS_NUM
17 ,p_start_adv_action_set_flag IN VARCHAR2 := FND_API.G_MISS_CHAR
18 ,p_sum_tasks_flag IN VARCHAR2 := FND_API.G_FALSE -- FP.M Development
19 ,p_budget_version_id IN pa_resource_assignments.budget_version_id%TYPE := FND_API.G_MISS_NUM
20 ,p_number_of_requirements IN NUMBER := 1
21 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
22 ,p_validate_only IN VARCHAR2 := FND_API.G_TRUE
23 ,x_new_assignment_id OUT NOCOPY pa_project_assignments.assignment_id%TYPE --File.Sql.39 bug 4440895
24 ,x_assignment_number OUT NOCOPY pa_project_assignments.assignment_number%TYPE --File.Sql.39 bug 4440895
25 ,x_assignment_row_id OUT NOCOPY ROWID --File.Sql.39 bug 4440895
26 ,x_resource_id OUT NOCOPY pa_resources.resource_id%TYPE --File.Sql.39 bug 4440895
27 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
28 )
29 IS
30
31 l_assignment_rec PA_ASSIGNMENTS_PUB.Assignment_Rec_Type;
32 l_project_subteam_party_id pa_project_subteam_parties.project_subteam_party_id%TYPE;
33 l_return_status VARCHAR2(10);
34 l_row_id ROWID;
35 l_msg_count NUMBER;
36 l_msg_data VARCHAR2(4000);
37 l_assignment_effort pa_project_assignments.assignment_effort%TYPE;
38 l_work_type_id NUMBER;
39 l_expenditure_type pa_project_assignments.expenditure_type%TYPE;
40 l_expenditure_type_class pa_project_assignments.expenditure_type_class%TYPE;
41 l_assignment_effort_calc VARCHAR2(1) := 'N';
42 l_raw_revenue NUMBER;
43 l_rec_version_number NUMBER;
44 -- FP.M Development
45 l_location_city pa_locations.city%TYPE;
46 l_location_region pa_locations.region%TYPE;
47 l_location_country_code pa_locations.country_code%TYPE;
48
49 CURSOR get_subteam_id IS
50 SELECT project_subteam_id,
51 primary_subteam_flag
52 FROM pa_project_subteam_parties
53 WHERE object_type = 'PA_PROJECT_ASSIGNMENTS'
54 AND object_id = l_assignment_rec.source_assignment_id;
55
56 BEGIN
57
58 -- Initialize the Error Stack
59 PA_DEBUG.init_err_stack('PA_ASSIGNMENTS_PVT.Create_Assignment');
60
61 --Log Message
62 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
63 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_ASSIGNMENTS_PVT.Create_Assignment.begin'
64 ,x_msg => 'Beginning of the PVT Create_Assignment'
65 ,x_log_level => 5);
66 END IF;
67 -- Initialize the return status to success
68 x_return_status := FND_API.G_RET_STS_SUCCESS;
69
70 --Assign p_assignment_rec to l_assignment_rec
71 l_assignment_rec := p_assignment_rec;
72
73 -- FP.M Development
74 -- If l_assignment_rec.resource_list_member_id is not miss_num
75 -- and p_asgn_creation_mode is not 'COPY',
76 -- the api must be called by the create team role page.
77 -- In this case, we need to default assignment attributes that
78 -- are not specified on page.
79
80 IF l_assignment_rec.resource_list_member_id <> FND_API.G_MISS_NUM
81 AND p_asgn_creation_mode <> 'COPY' THEN
82
83 -- Commented for Performance fix 4898314 SQL ID 14905834
84 -- SELECT work_type_id
85 -- ,location_id
86 -- ,city
87 -- ,region
88 -- ,country_code
89 -- INTO l_assignment_rec.work_type_id
90 -- ,l_assignment_rec.location_id
91 -- ,l_location_city
92 -- ,l_location_region
93 -- ,l_location_country_code
94 -- FROM PA_PROJECTS_PRM_V
95 -- WHERE project_id = l_assignment_rec.project_id;
96
97 --Included for Performance fix 4898314 SQL ID 14905834
98 SELECT ppa.work_type_id
99 ,pl.location_id
100 ,pl.city
101 ,pl.region
102 ,pl.country_code
103 INTO l_assignment_rec.work_type_id
104 ,l_assignment_rec.location_id
105 ,l_location_city
106 ,l_location_region
107 ,l_location_country_code
108 FROM pa_projects_all ppa,pa_locations pl
109 WHERE project_id = l_assignment_rec.project_id
110 AND PPA.LOCATION_ID = PL.LOCATION_ID(+) ;
111
112 -- Bug 4282413: Job levels are passed in from CrUdTeamRoleVORowImpl.
113 /*
114 IF p_assignment_rec.assignment_type = 'OPEN_ASSIGNMENT' THEN
115 SELECT ppr.default_min_job_level
116 ,ppr.default_max_job_level
117 INTO l_assignment_rec.min_resource_job_level
118 ,l_assignment_rec.max_resource_job_level
119 FROM pa_project_roles_lov_v ppr
120 ,fnd_lookups pl
121 WHERE pl.lookup_type = 'YES_NO'
122 AND ppr.schedulable_flag = pl.lookup_code
123 AND ppr.project_role_id = l_assignment_rec.project_role_id;
124 END IF; */
125 ELSE
126 l_location_city := p_location_city;
127 l_location_region := p_location_region;
128 l_location_country_code := p_location_country_code;
129 END IF;
130
131 -- END FP.M Development
132 IF P_DEBUG_MODE = 'Y' THEN
133 pa_debug.write(x_module => 'pa.plsql.PA_ASSIGNMENTS_PVT.Create_Assignment'
134 ,x_msg => 'after FP.M'
135 ,x_log_level => li_message_level);
136 END IF;
137
138 --dbms_output.put_line('before create assignment');
139 --Create Requirement/Assignment
140 IF p_assignment_rec.assignment_type = 'OPEN_ASSIGNMENT' THEN
141
142 --Log Message
143 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
144 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_ASSIGNMENTS_PVT.Create_Assignment.create_open'
145 ,x_msg => 'Calling Create Requirement'
146 ,x_log_level => 5);
147 END IF;
148
149 PA_OPEN_ASSIGNMENT_PVT.Create_Open_Assignment
150 ( p_assignment_rec => l_assignment_rec
151 ,p_asgn_creation_mode => p_asgn_creation_mode
152 ,p_location_city => l_location_city
153 ,p_location_region => l_location_region
154 ,p_location_country_code => l_location_country_code
155 ,p_adv_action_set_id => p_adv_action_set_id
156 ,p_start_adv_action_set_flag => p_start_adv_action_set_flag
157 ,p_sum_tasks_flag => p_sum_tasks_flag
158 ,p_budget_version_id => p_budget_version_id
159 ,p_number_of_requirements => p_number_of_requirements
160 ,p_commit => p_commit
161 ,p_validate_only => p_validate_only
162 ,x_new_assignment_id => x_new_assignment_id
163 ,x_assignment_number => x_assignment_number
164 ,x_assignment_row_id => x_assignment_row_id
165 ,x_return_status => x_return_status
166 );
167
168 IF P_DEBUG_MODE = 'Y' THEN
169 pa_debug.write(x_module => 'pa.plsql.PA_ASSIGNMENTS_PVT.Create_Assignment'
170 ,x_msg => 'after open_asgn_pvt.create, status = '||x_return_status
171 ,x_log_level => li_message_level);
172 END IF;
173
174 ELSE
175 --Log Message
176 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
177 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_ASSIGNMENTS_PVT.Create_Assignment.create_staff'
178 ,x_msg => 'Calling Create Assignment'
179 ,x_log_level => 5);
180
181 END IF;
182 PA_STAFFED_ASSIGNMENT_PVT.Create_Staffed_Assignment
183 ( p_assignment_rec => l_assignment_rec
184 ,p_unfilled_assignment_status => p_unfilled_assignment_status
185 ,p_resource_source_id => p_resource_source_id
186 ,p_location_city => l_location_city
187 ,p_location_region => l_location_region
188 ,p_location_country_code => l_location_country_code
189 ,p_sum_tasks_flag => p_sum_tasks_flag -- FP.M Development
190 ,p_budget_version_id => p_budget_version_id
191 ,p_commit => p_commit
192 ,p_validate_only => p_validate_only
193 ,x_new_assignment_id => x_new_assignment_id
194 ,x_assignment_row_id => x_assignment_row_id
195 ,x_resource_id => x_resource_id
196 ,x_return_status => x_return_status
197 );
198 END IF;
199
200 --check the calendar is valid from a business rule point of view
201 --need to call this after create_staffed_assignment b/c we may not have
202 --the resource id until create_staffed_assignment is called (create_staffed_assignment calls create_resource).
203 --if calendar_type is PROJECT or OTHER, calendar_id must be not null
204 --if calendar_type is RESOURCE, calendar_id must be not null
205
206 IF ((l_assignment_rec.calendar_type='PROJECT' OR l_assignment_rec.calendar_type='OTHER') AND l_assignment_rec.calendar_id IS NOT NULL AND l_assignment_rec.calendar_id <> FND_API.G_MISS_NUM) OR
207 (l_assignment_rec.calendar_type='RESOURCE' AND x_resource_id IS NOT NULL AND x_resource_id<>FND_API.G_MISS_NUM) THEN
208 PA_SCHEDULE_UTILS.Check_Calendar(p_calendar_type => l_assignment_rec.calendar_type,
209 p_calendar_id => l_assignment_rec.calendar_id,
210 p_resource_id => x_resource_id,
211 p_start_date => l_assignment_rec.start_date,
212 p_end_date => l_assignment_rec.end_date,
213 x_return_status => l_return_status,
214 x_msg_count => l_msg_count,
215 x_msg_data => l_msg_data);
216 END IF;
217
218 --dbms_output.put_line('after create assignment:'||x_return_status);
219
220 IF PA_ASSIGNMENTS_PUB.g_assignment_id_tbl.COUNT >0 THEN
221
222 FOR i IN PA_ASSIGNMENTS_PUB.g_assignment_id_tbl.FIRST .. PA_ASSIGNMENTS_PUB.g_assignment_id_tbl.LAST LOOP
223
224 --If no source assignment id, then call create subteam party once
225 --else loop through the source assignment's subteam party table and
226 --call create subteam party on each one of the source subteam ids
227
228 IF (l_assignment_rec.source_assignment_id IS NULL) OR
229 (l_assignment_rec.source_assignment_id=FND_API.G_MISS_NUM) THEN
230
231 -- Check the necessary input parameters are there
232 IF (p_project_subteam_id IS NOT NULL AND p_project_subteam_id <> FND_API.G_MISS_NUM) THEN
233
234 --Log Message
235 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
236 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_ASSIGNMENTS_PVT.Create_Assignment.create_subteam_party'
237 ,x_msg => 'Calling Create Assignment'
238 ,x_log_level => 5);
239 END IF;
240
241 PA_PROJECT_SUBTEAM_PARTIES_PVT.Create_Subteam_Party
242 ( p_validate_only => p_validate_only
243 ,p_project_subteam_id => p_project_subteam_id
244 ,p_object_type => 'PA_PROJECT_ASSIGNMENTS'
245 ,p_object_id => PA_ASSIGNMENTS_PUB.g_assignment_id_tbl(i).assignment_id
246 ,x_project_subteam_party_row_id => l_row_id
247 ,x_project_subteam_party_id => l_project_subteam_party_id
248 ,x_return_status => l_return_status
249 ,x_msg_count => l_msg_count
250 ,x_msg_data => l_msg_data
251 );
252
253 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
254 -- PA_ASSIGNMENT_UTILS.Add_Message( p_app_short_name => 'PA'
255 -- ,p_msg_name => l_error_message_code);
256 PA_ASSIGNMENTS_PUB.g_error_exists := FND_API.G_TRUE;
257 END IF;
258 END IF;
259
260 -- else source assignment id exist, then loop through subteam party table
261 ELSE
262
263 FOR get_subteam_id_rec IN get_subteam_id LOOP
264 IF get_subteam_id_rec.project_subteam_id IS NOT NULL THEN
265
266 --Log Message
267 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
268 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_ASSIGNMENTS_PVT.Create_Assignment.create_subteam_party'
269 ,x_msg => 'Calling Create Assignment'
270 ,x_log_level => 5);
271 END IF;
272
273 PA_PROJECT_SUBTEAM_PARTIES_PVT.Create_Subteam_Party
274 ( p_validate_only => p_validate_only
275 ,p_project_subteam_id => get_subteam_id_rec.project_subteam_id
276 ,p_object_type => 'PA_PROJECT_ASSIGNMENTS'
277 ,p_object_id => PA_ASSIGNMENTS_PUB.g_assignment_id_tbl(i).assignment_id
278 ,p_primary_subteam_flag => get_subteam_id_rec.primary_subteam_flag
279 ,x_project_subteam_party_row_id=> l_row_id
280 ,x_project_subteam_party_id => l_project_subteam_party_id
281 ,x_return_status => l_return_status
282 ,x_msg_count => l_msg_count
283 ,x_msg_data => l_msg_data
284 );
285
286 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
287 -- PA_ASSIGNMENT_UTILS.Add_Message( p_app_short_name => 'PA'
288 -- ,p_msg_name => l_error_message_code);
289 PA_ASSIGNMENTS_PUB.g_error_exists := FND_API.G_TRUE;
290
291 END IF; --error
292
293 END IF; --project subteam id is not null
294
295 END LOOP; --looping through subteams
296
297 END IF; -- end of checking source assignment id
298
299 END LOOP; -- LOOPING through new assignment ids
300
301 --
302 --Calculate for assignment effort
303
304 --Do not calculate assignment effort if this is a template requirement.
305 --No timeline/schedule is created for template requirements.
306 IF (l_assignment_rec.project_id IS NOT NULL AND l_assignment_rec.project_id <> FND_API.G_MISS_NUM) THEN
307
308 l_assignment_effort := PA_SCHEDULE_UTILS.get_num_hours(p_assignment_rec.project_id, PA_ASSIGNMENTS_PUB.g_assignment_id_tbl(1).assignment_id);
309
310 END IF;
311
312 --Log Message
313 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
314 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_ASSIGNMENTS_PVT.Create_Assignment.add_asgmt_effort'
315 ,x_msg => 'Adding Assignment Effort.'
316 ,x_log_level => 5);
317 END IF;
318
319 IF l_assignment_effort IS NOT NULL THEN
320
321 SELECT record_version_number INTO l_rec_version_number
322 FROM pa_project_assignments
323 WHERE assignment_id = PA_ASSIGNMENTS_PUB.g_assignment_id_tbl(1).assignment_id;
324
325 --This is an bulk update for assignment effort using the global assignment_id array
326 PA_PROJECT_ASSIGNMENTS_PKG.Update_Row
327 ( p_assignment_id => NULL
328 ,p_record_version_number => l_rec_version_number
329 ,p_assignment_effort => l_assignment_effort
330 ,x_return_status => l_return_status
331 );
332
333 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
334 PA_ASSIGNMENTS_PUB.g_error_exists := FND_API.G_TRUE;
335 END IF;
336 END IF;
337
338 END IF; --checking there are any new assignments ids
339
340 -- Reset the error stack when returning to the calling program
341 PA_DEBUG.Reset_Err_Stack;
342
343 -- If g_error_exists is TRUE then set the x_return_status to 'E'
344
345 IF PA_ASSIGNMENTS_PUB.g_error_exists = FND_API.G_TRUE THEN
346
347 x_return_status := FND_API.G_RET_STS_ERROR;
348
349 END IF;
350
351
352 EXCEPTION
353 WHEN OTHERS THEN
354 -- 4537865 : RESET OUT params to Proper Values
355 x_new_assignment_id := NULL ;
356 x_assignment_number := NULL ;
357 x_assignment_row_id := NULL ;
358 x_resource_id := NULL ;
359 -- ENd : 4537865
360
361 -- Set the excetption Message and the stack
362 FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_ASSIGNMENTS_PVT.Create_Assignment'
363 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
364 --
365 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
366 RAISE;
367 END Create_Assignment;
368
369
370
371 PROCEDURE Update_Assignment
372 ( p_assignment_rec IN PA_ASSIGNMENTS_PUB.Assignment_Rec_Type
373 ,p_project_subteam_id IN pa_project_subteams.project_subteam_id%TYPE := FND_API.G_MISS_NUM
374 ,p_project_subteam_party_id IN pa_project_subteam_parties.project_subteam_party_id%TYPE := FND_API.G_MISS_NUM
375 ,p_location_city IN pa_locations.city%TYPE := FND_API.G_MISS_CHAR
376 ,p_location_region IN pa_locations.region%TYPE := FND_API.G_MISS_CHAR
377 ,p_location_country_code IN pa_locations.country_code%TYPE := FND_API.G_MISS_CHAR
378 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
379 ,p_validate_only IN VARCHAR2 := FND_API.G_TRUE
380 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
381 )
382 IS
383
384 l_project_subteam_party_id pa_project_subteam_parties.project_subteam_party_id%TYPE;
385 l_return_status VARCHAR2(10);
386 l_record_version_number NUMBER;
387 l_msg_count NUMBER;
388 l_msg_data VARCHAR2(4000);
389 l_object_id NUMBER;
390 l_work_type_id NUMBER;
391 l_assignment_rec PA_ASSIGNMENTS_PUB.Assignment_Rec_Type;
392
393 -- cursor to get the utilization information
394 CURSOR get_work_type IS
395 SELECT work_type_id
396 FROM pa_project_assignments
397 WHERE assignment_id = p_assignment_rec.assignment_id;
398
399 BEGIN
400
401 --dbms_output.put_line('Beginning PVTB Update_Assignment');
402
403 -- Initialize the Error Stack
404 PA_DEBUG.init_err_stack('PA_ASSIGNMENTS_PVT.Update_Assignment');
405
406 --Log Message
407 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
408 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_ASSIGNMENTS_PVT.Update_Assignment.begin'
409 ,x_msg => 'Beginning of PVT Update_Assignment'
410 ,x_log_level => 5);
411 END IF;
412
413
414 -- Initialize the return status to success
415 x_return_status := FND_API.G_RET_STS_SUCCESS;
416
417 --dbms_output.put_line('Update Subteam party 1');
418 --
419 --Update Subteam Party
420 --
421 --If Subteam Party Id exist or Subteam Id exist, then call Update Subteam Party
422 IF (p_project_subteam_id IS NOT NULL AND p_project_subteam_id <> FND_API.G_MISS_NUM OR
423 p_project_subteam_party_id IS NOT NULL AND p_project_subteam_party_id <> FND_API.G_MISS_NUM) AND
424 (p_assignment_rec.assignment_id IS NOT NULL AND p_assignment_rec.assignment_id <>FND_API.G_MISS_NUM) THEN
425
426 --Log Message
427 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
428 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_ASSIGNMENTS_PVT.Update_Assignment.update_subteam_party'
429 ,x_msg => 'Calling Update_SPT_Assgn for subteam party.'
430 ,x_log_level => 5);
431 END IF;
432 PA_PROJECT_SUBTEAM_PARTIES_PVT.Update_SPT_Assgn
433 ( p_validate_only => p_validate_only
434 ,p_project_subteam_party_id => p_project_subteam_party_id
435 ,p_project_subteam_id => p_project_subteam_id
436 ,p_object_type => 'PA_PROJECT_ASSIGNMENTS'
437 ,p_object_id => p_assignment_rec.assignment_id
438 ,x_project_subteam_party_id => l_project_subteam_party_id
439 ,x_return_status => l_return_status
440 ,x_record_version_number => l_record_version_number
441 ,x_msg_count => l_msg_count
442 ,x_msg_data => l_msg_data
443 );
444
445 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
446 -- PA_UTILS.Add_Message( p_app_short_name => 'PA'
447 -- ,p_msg_name => l_error_message_code);
448 PA_ASSIGNMENTS_PUB.g_error_exists := FND_API.G_TRUE;
449 END IF;
450 END IF;
451
452 --If this is a template requirement then it is possible to update the
453 --start date and the end date through this API - as template requirements
454 --do not have schedules. For requirements which belong to projects, these updates
455 --are not allowed through this API and would have already caused a validation error
456 --in the public API.
457 IF p_assignment_rec.start_date<> FND_API.G_MISS_DATE OR p_assignment_rec.end_date<>FND_API.G_MISS_DATE THEN
458 IF p_assignment_rec.start_date > p_assignment_rec.end_date THEN
459 PA_UTILS.Add_Message( p_app_short_name => 'PA'
460 ,p_msg_name => 'PA_INVALID_START_DATE');
461 PA_ASSIGNMENTS_PUB.g_error_exists := FND_API.G_TRUE;
462 END IF;
463 END IF;
464
465 -- Get the old values of Work Type
466 OPEN get_work_type;
467 FETCH get_work_type INTO l_work_type_id;
468 CLOSE get_work_type;
469
470 --Get the new tp amount type from the work type if work type is updated
471 l_assignment_rec := p_assignment_rec;
472
473 -- FP-J Bug: no data found in Mass Update Team Role
474 -- work_type_id may be passed in as null within Mass Update
475 IF l_assignment_rec.work_type_id IS NOT NULL
476 AND l_assignment_rec.work_type_id <> FND_API.G_MISS_NUM
477 AND l_assignment_rec.work_type_id <> l_work_type_id THEN
478
479 PA_FP_ORG_FCST_UTILS.Get_Tp_Amount_Type(
480 p_project_id => l_assignment_rec.project_id
481 ,p_work_type_id => l_assignment_rec.work_type_id
482 ,x_tp_amount_type => l_assignment_rec.fcst_tp_amount_type
483 ,x_return_status => l_return_status
484 ,x_msg_count => l_msg_count
485 ,x_msg_data => l_msg_data
486 );
487
488 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
489 PA_ASSIGNMENTS_PUB.g_error_exists := FND_API.G_TRUE;
490 END IF;
491
492
493 END IF;
494
495
496 IF l_assignment_rec.assignment_type = 'OPEN_ASSIGNMENT' THEN
497 --Log Message
498 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
499 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_ASSIGNMENTS_PVT.Update_Assignment.update_open'
500 ,x_msg => 'Calling update requirement.'
501 ,x_log_level => 5);
502 END IF;
503
504 PA_OPEN_ASSIGNMENT_PVT.Update_Open_Assignment
505 ( p_assignment_rec => l_assignment_rec
506 ,p_location_city => p_location_city
507 ,p_location_region => p_location_region
508 ,p_location_country_code => p_location_country_code
509 ,p_validate_only => p_validate_only
510 ,p_commit => p_commit
511 ,x_return_status => x_return_status
512 );
513 ELSE
514
515 --Log Message
516 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
517 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_ASSIGNMENTS_PVT.Update_Assignment.update_staff'
518 ,x_msg => 'Calling update assignment.'
519 ,x_log_level => 5);
520 END IF;
521
522 PA_STAFFED_ASSIGNMENT_PVT.Update_Staffed_Assignment
523 ( p_assignment_rec => l_assignment_rec
524 ,p_location_city => p_location_city
525 ,p_location_region => p_location_region
526 ,p_location_country_code => p_location_country_code
527 ,p_validate_only => p_validate_only
528 ,p_commit => p_commit
529 ,x_return_status => x_return_status
530 );
531 END IF;
532
533 -- Reset the error stack when returning to the calling program
534 PA_DEBUG.Reset_Err_Stack;
535
536 -- If g_error_exists is TRUE then set the x_return_status to 'E'
537
538 IF PA_ASSIGNMENTS_PUB.g_error_exists = FND_API.G_TRUE THEN
539
540 x_return_status := FND_API.G_RET_STS_ERROR;
541
542 END IF;
543
544
545 EXCEPTION
546 WHEN OTHERS THEN
547
548 -- Set the excetption Message and the stack
549 FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_ASSIGNMENTS_PVT.Update_Assignment'
550 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
551 --
552 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
553 RAISE;
554 END Update_Assignment;
555
556
557 PROCEDURE Delete_Assignment
558 ( p_assignment_row_id IN ROWID := NULL
559 ,p_assignment_id IN pa_project_assignments.assignment_id%TYPE := FND_API.G_MISS_NUM
560 ,p_assignment_type IN pa_project_assignments.assignment_type%TYPE := FND_API.G_MISS_CHAR
561 ,p_record_version_number IN NUMBER := FND_API.G_MISS_NUM
562 ,p_assignment_number IN pa_project_assignments.assignment_number%TYPE := FND_API.G_MISS_NUM
563 ,p_calling_module IN VARCHAR2 := FND_API.G_MISS_CHAR
564 ,p_project_party_id IN pa_project_parties.project_party_id%TYPE := FND_API.G_MISS_NUM
565 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
566 ,p_validate_only IN VARCHAR2 := FND_API.G_TRUE
567 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
568 )
569 IS
570
571 l_return_status VARCHAR2(10);
572 l_msg_count NUMBER;
573 l_msg_data VARCHAR2(4000);
574 l_task_assignment_id_tbl system.pa_num_tbl_type;
575 l_task_version_id_tbl system.pa_num_tbl_type := system.pa_num_tbl_type();
576 l_budget_version_id_tbl system.pa_num_tbl_type := system.pa_num_tbl_type();
577 l_struct_version_id_tbl system.pa_num_tbl_type := system.pa_num_tbl_type();
578 l_project_assignment_id_tbl system.pa_num_tbl_type := system.pa_num_tbl_type();
579 l_cur_role_flag pa_res_formats_b.role_enabled_flag%TYPE;
580
581
582 CURSOR get_linked_res_asgmts IS
583 SELECT resource_assignment_id, wbs_element_version_id, budget_version_id, project_structure_version_id
584 FROM
585 (
586 (SELECT ra.resource_assignment_id, ra.wbs_element_version_id, bv.budget_version_id, bv.project_structure_version_id
587 FROM PA_RESOURCE_ASSIGNMENTS ra
588 ,PA_BUDGET_VERSIONS bv
589 ,PA_PROJ_ELEM_VER_STRUCTURE evs
590 -- ,PA_PROJECT_ASSIGNMENTS pa -- 5110598 Removed PA_PROJECT_ASSIGNMENTS table usage
591 WHERE ra.project_id = bv.project_id
592 AND bv.project_id = evs.project_id
593 AND ra.budget_version_id = bv.budget_version_id
594 AND bv.project_structure_version_id = evs.element_version_id
595 -- AND ra.project_id = l_assignment_rec.project_id
596 -- AND pa.assignment_id = p_assignment_id -- 5110598 Removed table usage
597 -- AND ra.project_id = pa.project_id -- 5110598 Removed table usage
598 AND ra.project_assignment_id = p_assignment_id
599 AND evs.status_code = 'STRUCTURE_WORKING')
600 UNION ALL
601 (SELECT ra.resource_assignment_id, ra.wbs_element_version_id, bv.budget_version_id, bv.project_structure_version_id
602 FROM PA_RESOURCE_ASSIGNMENTS ra
603 ,PA_BUDGET_VERSIONS bv
604 ,PA_PROJ_ELEM_VER_STRUCTURE evs
605 ,PA_PROJ_WORKPLAN_ATTR pwa
606 -- ,PA_PROJECT_ASSIGNMENTS pa -- 5110598 Removed PA_PROJECT_ASSIGNMENTS table usage
607 WHERE pwa.wp_enable_Version_flag = 'N'
608 AND pwa.project_id = ra.project_id
609 AND pwa.proj_element_id = evs.proj_element_id
610 AND ra.project_id = bv.project_id
611 AND bv.project_id = evs.project_id
612 AND ra.budget_version_id = bv.budget_version_id
613 AND bv.project_structure_version_id = evs.element_version_id
614 -- AND ra.project_id = l_assignment_rec.project_id
615 -- AND pa.assignment_id = p_assignment_id -- 5110598 Removed table usage
616 -- AND ra.project_id = pa.project_id -- 5110598 Removed table usage
617 AND ra.project_assignment_id = p_assignment_id)
618 )
619 ORDER BY budget_version_id, project_structure_version_id;
620
621 CURSOR get_res_mand_attributes IS
622 SELECT rf.ROLE_ENABLED_FLAG
623 FROM pa_res_formats_b rf,
624 pa_resource_list_members rlm,
625 pa_project_assignments pa
626 WHERE pa.assignment_id = p_assignment_id
627 AND pa.resource_list_member_id IS NOT NULL
628 AND rlm.resource_list_member_id = pa.resource_list_member_id
629 AND rlm.res_format_id = rf.res_format_id;
630
631
632 BEGIN
633
634 -- Initialize the Error Stack
635 PA_DEBUG.init_err_stack('PA_ASSIGNMENTS_PVT.Delete_Assignment');
636
637 -- Initialize the return status to success
638 x_return_status := FND_API.G_RET_STS_SUCCESS;
639
640 --Log Message
641 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
642 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_ASSIGNMENTS_PVT.Delete_Assignment.begin'
643 ,x_msg => 'Beginning of Delete_Assignment.'
644 ,x_log_level => 5);
645 END IF;
646
647 -- Delete Subteam Party if assignment id exists
648 IF (p_assignment_id IS NOT NULL AND p_assignment_id <>FND_API.G_MISS_NUM) THEN
649
650 --Log Message
651 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
652 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_ASSIGNMENTS_PVT.Delete_Assignment.delete_subteam_party'
653 ,x_msg => 'Deleting subteam party'
654 ,x_log_level => 5);
655 END IF;
656 PA_PROJECT_SUBTEAM_PARTIES_PVT.Delete_SubteamParty_By_Obj
657 ( p_validate_only => p_validate_only
658 ,p_object_type => 'PA_PROJECT_ASSIGNMENTS'
659 ,p_object_id => p_assignment_id
660 ,p_init_msg_list => FND_API.G_FALSE -- Added for bug 5130421
661 ,x_return_status => l_return_status
662 ,x_msg_count => l_msg_count
663 ,x_msg_data => l_msg_data
664 );
665 --dbms_output.put_line('return status is '||l_return_status);
666 --dbms_output.put_line('message count is '||l_msg_count);
667 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
668 -- PA_UTILS.Add_Message( p_app_short_name => 'PA'
669 -- ,p_msg_name => l_error_message_code);
670 PA_ASSIGNMENTS_PUB.g_error_exists := FND_API.G_TRUE;
671 END IF;
672
673 --Bug 6330317
674 IF P_DEBUG_MODE = 'Y' THEN
675 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_ASSIGNMENTS_PVT.Delete_Assignment.delete_rows'
676 ,x_msg => 'Deleting Conflict History Information'
677 ,x_log_level => 5);
678 END IF;
679
680 PA_ASGN_CONFLICT_HIST_PKG.Delete_rows
681 ( p_assignment_id => p_assignment_id
682 ,x_return_status => l_return_status
683 ,x_msg_count => l_msg_count
684 ,x_msg_data => l_msg_data
685 );
686
687 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
688 -- PA_UTILS.Add_Message( p_app_short_name => 'PA'
689 -- ,p_msg_name => l_error_message_code);
690 PA_ASSIGNMENTS_PUB.g_error_exists := FND_API.G_TRUE;
691 END IF;
692 END IF;
693
694 -- FP.M Development
695 -- run the cursor before the assignment/requirement is deleted
696 OPEN get_linked_res_asgmts;
697 FETCH get_linked_res_asgmts
698 BULK COLLECT INTO l_task_assignment_id_tbl,
699 l_task_version_id_tbl,
700 l_budget_version_id_tbl,
701 l_struct_version_id_tbl;
702 CLOSE get_linked_res_asgmts;
703
704 -- FP.M Development
705 -- Check whether role is part of the deleted assignment's
706 -- planning resource's resource format
707 -- 4117262: Move this cursor fetch to BEFORE deleting the assignment
708 l_cur_role_flag := NULL;
709 OPEN get_res_mand_attributes;
710 FETCH get_res_mand_attributes INTO l_cur_role_flag;
711 CLOSE get_res_mand_attributes;
712
713 IF p_assignment_type = 'OPEN_ASSIGNMENT' THEN
714
715 --Log Message
716 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
717 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_ASSIGNMENTS_PVT.Delete_Assignment.delete_open'
718 ,x_msg => 'Deleting Requirement'
719 ,x_log_level => 5);
720 END IF;
721
722 PA_OPEN_ASSIGNMENT_PVT.Delete_Open_Assignment
723 ( p_assignment_row_id => p_assignment_row_id
724 ,p_assignment_id => p_assignment_id
725 ,p_record_version_number => p_record_version_number
726 ,p_calling_module => p_calling_module
727 ,p_commit => p_commit
728 ,p_validate_only => p_validate_only
729 ,x_return_status => x_return_status
730 );
731 --
732 ELSE
733
734 --Log Message
735 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
736 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_ASSIGNMENTS_PVT.Delete_Assignment.delete_staff'
737 ,x_msg => 'Deleting Assignment'
738 ,x_log_level => 5);
739 END IF;
740
741 PA_STAFFED_ASSIGNMENT_PVT.Delete_Staffed_Assignment
742 ( p_assignment_row_id => p_assignment_row_id
743 ,p_assignment_id => p_assignment_id
744 ,p_record_version_number => p_record_version_number
745 ,p_project_party_id => p_project_party_id
746 ,p_calling_module => p_calling_module
747 ,p_commit => p_commit
748 ,p_validate_only => p_validate_only
749 ,x_return_status => x_return_status
750 );
751 END IF;
752
753 -- FP.M Development
754 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
755 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_ASSIGNMENTS_PVT.Delete_Assignment.delete_staff'
756 ,x_msg => 'Call Update_Task_Assignments'
757 ,x_log_level => 5);
758 END IF;
759
760
761 -- 1. Change project_assignment_id to NULL (-1)
762 -- 2. Don't wipe out project_role_id,
763 -- 3. Wipe out named_role when it is not a mandatory attribute
764 -- of planning resource
765 -- 4117262: Modified to check the flag value only
766 IF l_cur_role_flag = 'Y' THEN
767
768 Update_Task_Assignments(
769 p_task_assignment_id_tbl => l_task_assignment_id_tbl
770 ,p_task_version_id_tbl => l_task_version_id_tbl
771 ,p_budget_version_id_tbl => l_budget_version_id_tbl
772 ,p_struct_version_id_tbl => l_struct_version_id_tbl
773 ,p_project_assignment_id => -1
774 ,x_return_status => l_return_status
775 );
776 ELSE
777 Update_Task_Assignments(
778 p_task_assignment_id_tbl => l_task_assignment_id_tbl
779 ,p_task_version_id_tbl => l_task_version_id_tbl
780 ,p_budget_version_id_tbl => l_budget_version_id_tbl
781 ,p_struct_version_id_tbl => l_struct_version_id_tbl
782 ,p_project_assignment_id => -1
783 ,p_named_role => FND_API.G_MISS_CHAR
784 ,x_return_status => l_return_status
785 );
786 END IF;
787
788 /*
789
790
791 BULK COLLECT INTO l_task_assignment_id_tbl
792
793
794 l_project_assignment_id_tbl.extend(l_task_assignment_id_tbl.count);
795
796 IF l_task_assignment_id_tbl.COUNT <>0 THEN
797 FOR i IN l_task_assignment_id_tbl.FIRST .. l_task_assignment_id_tbl.LAST LOOP
798 l_project_assignment_id_tbl(i) := NULL;
799 END LOOP;
800
801 pa_fp_planning_transaction_pub.update_planning_transactions
802 (
803 p_context => 'TASK_ASSIGNMENT'
804 ,p_resource_assignment_id_tbl => l_task_assignment_id_tbl
805 ,p_project_assignment_id_tbl => l_project_assignment_id_tbl
806 ,X_Return_Status => l_return_status
807 ,X_Msg_Data => l_msg_data
808 ,X_Msg_Count => l_msg_count);
809 END IF;
810 */
811
812 -- Reset the error stack when returning to the calling program
813 PA_DEBUG.Reset_Err_Stack;
814
815 -- If g_error_exists is TRUE then set the x_return_status to 'E'
816
817 IF PA_ASSIGNMENTS_PUB.g_error_exists = FND_API.G_TRUE THEN
818
819 x_return_status := FND_API.G_RET_STS_ERROR;
820
821 END IF;
822
823
824 EXCEPTION
825 WHEN OTHERS THEN
826
827 -- Set the excetption Message and the stack
828 FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_ASSIGNMENTS_PVT.Delete_Assignment'
829 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
830 --
831 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
832 RAISE;
833 END Delete_Assignment;
834
835 /* --------------------------------------------------------------------
836 FUNCTION: Update_Revenue_Bill_Rate
837 PURPOSE: This API updates the revenue_bill_rate for assignments passed
838 in. It should be only called by the Project Forecast Process.
839 -------------------------------------------------------------------- */
840 /* PROCEDURE Update_Revenue_Bill_Rate (p_assignment_id_tbl IN SYSTEM.pa_num_tbl_type,
841 p_revenue_bill_rate_tbl IN SYSTEM.pa_num_tbl_type,
842 x_return_status OUT VARCHAR2) */
843 PROCEDURE Update_Revenue_Bill_Rate
844 ( p_assignment_id_tbl IN PA_PLSQL_DATATYPES.IdTabTyp
845 ,p_revenue_bill_rate_tbl IN PA_PLSQL_DATATYPES.NumTabTyp
846 ,x_return_status OUT NOCOPY VARCHAR2 ) --File.Sql.39 bug 4440895
847 IS
848 BEGIN
849
850 PA_DEBUG.init_err_stack('PA_ASSIGNMENTS_PVT.Update_Revenue_Bill_Rate');
851
852 x_return_status := FND_API.G_RET_STS_SUCCESS;
853
854 IF p_assignment_id_tbl.COUNT > 0 THEN
855 FOR i IN p_assignment_id_tbl.FIRST .. p_assignment_id_tbl.LAST LOOP
856
857 pa_project_assignments_pkg.Update_row (
858 p_assignment_id => p_assignment_id_tbl(i),
859 p_revenue_bill_rate => p_revenue_bill_rate_tbl(i),
860 x_return_status => x_return_status);
861 END LOOP;
862 END IF;
863
864 EXCEPTION
865 WHEN OTHERS THEN
866 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
867
868 fnd_msg_pub.add_exc_msg
869 (p_pkg_name => 'PA_ASSIGNMENTS_PVT',
870 p_procedure_name => 'Update_Revenue_Bill_Rate');
871
872 RAISE;
873 END Update_Revenue_Bill_Rate;
874
875 --
876 --
877
878 /* Added procedure Update_Transfer_Price for bug 3051110
879 This Procedure calls update_row which will update the record in pa_project_assignments table
880 with the transfer price rate and transfer price rate curr passed.
881 */
882
883 PROCEDURE Update_Transfer_Price
884 ( p_assignment_id IN pa_project_assignments.assignment_id%TYPE
885 ,p_transfer_price_rate IN pa_project_assignments.transfer_price_rate%TYPE
886 ,p_transfer_pr_rate_curr IN pa_project_assignments.transfer_pr_rate_curr%TYPE
887 ,p_debug_mode IN VARCHAR2 default 'N'
888 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
889 )
890 IS
891 BEGIN
892
893 x_return_status := FND_API.G_RET_STS_SUCCESS;
894
895 pa_project_assignments_pkg.update_row(
896 p_assignment_id => p_assignment_id,
897 p_transfer_price_rate => p_transfer_price_rate,
898 p_transfer_pr_rate_curr => p_transfer_pr_rate_curr,
899 x_return_status => x_return_status);
900
901 EXCEPTION
902 WHEN OTHERS THEN
903 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
904
905 fnd_msg_pub.add_exc_msg
906 (p_pkg_name => 'PA_ASSIGNMENTS_PVT',
907 p_procedure_name => 'Update_Transfer_Price');
908
909 RAISE;
910 END Update_Transfer_Price;
911
912 /* Added procedure Calc_Init_Transfer_Price for bug 3051110. (TP Enhancement)
913 This Procedure calls the Billing API which calculates the TP Rate and returns.
914 The rate is used for updation in the pa_project_assignments table.
915 */
916
917 PROCEDURE Calc_Init_Transfer_Price
918 ( p_assignment_id IN pa_project_assignments.assignment_id%TYPE
919 ,p_start_date IN pa_project_assignments.start_date%TYPE
920 ,p_debug_mode IN VARCHAR2 DEFAULT 'N'
921 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
922 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
923 ,x_msg_count OUT NOCOPY Number --File.Sql.39 bug 4440895
924 )
925 IS
926
927 lx_transfer_price_rate pa_project_assignments.transfer_price_rate%TYPE;
928 lx_transfer_pr_rate_curr pa_project_assignments.transfer_pr_rate_curr%TYPE;
929 l_start_date pa_project_assignments.start_date%TYPE := Null;
930
931 BEGIN
932
933 if p_debug_mode = 'Y' THEN
934 pa_debug.write('PA_ASSIGNMENT_PVT.Calc_Init_Transfer_Price', 'Calling PA_CC_TRANSFER_PRICE.Get_Initial_Transfer_Price' , 3);
935 end if;
936
937 /* Calling get_initial_transfer_price to get the transfer_price_rate and transfer_price_rate */
938
939 BEGIN
940
941 /*
942 This select is done for getting the min item_date from pa_forecast_items table.
943 There can be case that the assignment start date is a holiday (Exception in the calendar)
944 and hence no fi will be created for the assignment start date. So we need to get the first
945 working date which is done in the select below. If the min(item_date) is null, we dont call the
946 pa_cc_transfer_price.get_initial_transfer_price api
947 */
948
949 select min(item_date) into l_start_date
950 from pa_forecast_items
951 WHERE Assignment_id = p_assignment_id
952 AND Error_Flag = 'N'
953 AND Delete_Flag = 'N';
954
955 EXCEPTION WHEN NO_DATA_FOUND THEN
956 l_start_date := NULL;
957 lx_transfer_price_rate := NULL;
958 lx_transfer_pr_rate_curr := NULL;
959 END;
960
961 if p_debug_mode = 'Y' THEN
962 pa_debug.write('PA_ASSIGNMENT_PVT.Calc_Init_Transfer_Price','The starting date as in forecast items table:'||l_start_date, 3);
963 end if;
964
965 IF l_start_date IS NOT NULL THEN
966
967 IF P_DEBUG_MODE = 'Y' THEN
968 pa_debug.write(x_module => 'pa.plsql.PA_ASSIGNMENTS_PVT.Calc_Init_Transfer_Price'
969 ,x_msg => 'asgmt_id='||p_assignment_id||
970 ' start_date='||l_start_date||
971 ' debug_mod='||p_debug_mode
972 ,x_log_level => li_message_level);
973 END IF;
974
975 PA_CC_TRANSFER_PRICE.Get_Initial_Transfer_Price
976 ( p_assignment_id => p_assignment_id
977 ,p_start_date => l_start_date
978 ,p_debug_mode => p_debug_mode
979 ,x_transfer_price_rate => lx_transfer_price_rate
980 ,x_transfer_pr_rate_curr => lx_transfer_pr_rate_curr
981 ,x_return_status => x_return_status
982 ,x_msg_data => x_msg_data
983 ,x_msg_count => x_msg_count
984 );
985
986 if p_debug_mode = 'Y' THEN
987 pa_debug.write('PA_ASSIGNMENT_PVT.Calc_Init_Transfer_Price',' Out of PA_CC_TRANSFER_PRICE.Get_Initial_Transfer_Price', 3);
988 pa_debug.write('PA_ASSIGNMENT_PVT.Calc_Init_Transfer_Price','status is :'||x_return_status||' : x_msg_count:'||x_msg_count, 3);
989 pa_debug.write('PA_ASSIGNMENT_PVT.Calc_Init_Transfer_Price','Transfer Price Rate:'||lx_transfer_price_rate, 3);
990 pa_debug.write('PA_ASSIGNMENT_PVT.Calc_Init_Transfer_Price','Transfer Price Rate curr:'||lx_transfer_pr_rate_curr, 3);
991 end if;
992
993 /* Call to update_Transfer_Price to udpate the transfer_price_Rate and transfer_pr_rate_curr only if no error */
994
995 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
996
997 IF p_debug_mode = 'Y' THEN
998 pa_debug.write('PA_ASSIGNMENT_PVT.Calc_Init_Transfer_Price',' Calling update_transfer_price with proper Values', 3);
999 END IF;
1000
1001 PA_ASSIGNMENTS_PVT.Update_Transfer_Price
1002 (
1003 p_assignment_id => p_assignment_id
1004 ,p_transfer_price_rate => lx_transfer_price_rate
1005 ,p_transfer_pr_rate_curr => lx_transfer_pr_rate_curr
1006 ,x_return_status => x_return_status
1007 );
1008
1009 END IF;
1010
1011 if p_debug_mode = 'Y' THEN
1012 pa_debug.write('PA_ASSIGNMENT_PVT.Calc_Init_Transfer_Price',' Out of update_transfer_price', 3);
1013 pa_debug.write('PA_ASSIGNMENT_PVT.Calc_Init_Transfer_Price',' x_return_status: '||x_return_status, 3);
1014 end if;
1015
1016 END IF;
1017 -- 4537865 : Included Exception Block
1018 EXCEPTION
1019 WHEN OTHERS THEN
1020 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1021 x_msg_count := 1;
1022 x_msg_data := SUBSTRB(SQLERRM,1,240);
1023
1024 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_ASSIGNMENTS_PVT'
1025 ,p_procedure_name => 'Calc_Init_Transfer_Price'
1026 ,P_error_text => x_msg_data );
1027 RAISE ;
1028 END Calc_Init_Transfer_Price;
1029
1030 PROCEDURE Update_Task_Assignments
1031 ( p_mode IN VARCHAR2 := 'UPDATE'
1032 ,p_task_assignment_id_tbl IN system.pa_num_tbl_type
1033 ,p_task_version_id_tbl IN system.pa_num_tbl_type
1034 ,p_budget_version_id_tbl IN system.pa_num_tbl_type
1035 ,p_struct_version_id_tbl IN system.pa_num_tbl_type
1036 ,p_project_assignment_id IN NUMBER := NULL
1037 ,p_resource_list_member_id IN NUMBER := NULL
1038 -- pass in all resource attributes
1039 ,p_resource_class_flag IN VARCHAR2 := NULL
1040 ,p_resource_class_code IN VARCHAR2 := NULL
1041 ,p_resource_class_id IN NUMBER := NULL
1042 ,p_res_type_code IN VARCHAR2 := NULL
1043 ,p_incur_by_res_type IN VARCHAR2 := NULL
1044 ,p_person_id IN NUMBER := NULL
1045 ,p_job_id IN NUMBER := NULL
1046 ,p_person_type_code IN VARCHAR2 := NULL
1047 ,p_named_role IN VARCHAR2 := NULL -- named_role
1048 ,p_bom_resource_id IN NUMBER := NULL
1049 ,p_non_labor_resource IN VARCHAR2 := NULL
1050 ,p_inventory_item_id IN NUMBER := NULL
1051 ,p_item_category_id IN NUMBER := NULL
1052 ,p_project_role_id IN NUMBER := NULL
1053 ,p_organization_id IN NUMBER := NULL
1054 ,p_fc_res_type_code IN VARCHAR2 := NULL
1055 ,p_expenditure_type IN VARCHAR2 := NULL
1056 ,p_expenditure_category IN VARCHAR2 := NULL
1057 ,p_event_type IN VARCHAR2 := NULL
1058 ,p_revenue_category_code IN VARCHAR2 := NULL
1059 ,p_supplier_id IN NUMBER := NULL
1060 ,p_spread_curve_id IN NUMBER := NULL
1061 ,p_etc_method_code IN VARCHAR2 := NULL
1062 ,p_mfc_cost_type_id IN NUMBER := NULL
1063 ,p_incurred_by_res_flag IN VARCHAR2 := NULL
1064 ,p_incur_by_res_class_code IN VARCHAR2 := NULL
1065 ,p_incur_by_role_id IN NUMBER := NULL
1066 ,p_unit_of_measure IN VARCHAR2 := NULL
1067 ,p_org_id IN NUMBER := NULL
1068 ,p_rate_based_flag IN VARCHAR2 := NULL
1069 ,p_rate_expenditure_type IN VARCHAR2 := NULL
1070 ,p_rate_func_curr_code IN VARCHAR2 := NULL
1071 ,p_rate_incurred_by_org_id IN NUMBER := NULL
1072 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1073 ) IS
1074
1075 l_task_assignment_id_tbl system.pa_num_tbl_type;
1076 l_task_version_id_tbl system.pa_num_tbl_type := system.pa_num_tbl_type();
1077 l_budget_version_id_tbl system.pa_num_tbl_type := system.pa_num_tbl_type();
1078 l_struct_version_id_tbl system.pa_num_tbl_type := system.pa_num_tbl_type();
1079 l_update_task_asgmt_id_tbl system.pa_num_tbl_type := system.pa_num_tbl_type();
1080 l_last_struct_version_id NUMBER;
1081 l_update_task_version_id_tbl system.pa_num_tbl_type := system.pa_num_tbl_type();
1082
1083 l_project_assignment_id_tbl system.pa_num_tbl_type := system.pa_num_tbl_type();
1084 l_resource_list_member_id_tbl SYSTEM.PA_NUM_TBL_TYPE := system.pa_num_tbl_type();
1085 l_proj_req_res_format_id NUMBER;
1086 l_last_bvid NUMBER;
1087 l_update_count NUMBER;
1088
1089 l_return_status VARCHAR2(1);
1090 l_msg_count NUMBER;
1091 l_msg_data VARCHAR2(2000);
1092 l_overall_return_status VARCHAR2(1);
1093 l_project_id NUMBER;
1094 l_edit_task_ok VARCHAR2(1);
1095 l_msg_count1 NUMBER;
1096 l_msg_count2 NUMBER;
1097
1098 l_resource_class_flag_tbl SYSTEM.PA_VARCHAR2_1_TBL_TYPE := system.pa_varchar2_1_tbl_type();
1099 l_resource_class_code_tbl SYSTEM.PA_VARCHAR2_30_TBL_TYPE := system.pa_varchar2_30_tbl_type();
1100 l_resource_class_id_tbl SYSTEM.PA_NUM_TBL_TYPE := system.pa_num_tbl_type();
1101 l_res_type_code_tbl SYSTEM.PA_VARCHAR2_30_TBL_TYPE := system.pa_varchar2_30_tbl_type();
1102 l_incur_by_res_type_tbl SYSTEM.PA_VARCHAR2_30_TBL_TYPE := system.pa_varchar2_30_tbl_type();
1103 l_person_id_tbl SYSTEM.PA_NUM_TBL_TYPE := system.pa_num_tbl_type();
1104 l_job_id_tbl SYSTEM.PA_NUM_TBL_TYPE := system.pa_num_tbl_type();
1105 l_person_type_code_tbl SYSTEM.PA_VARCHAR2_30_TBL_TYPE := system.pa_varchar2_30_tbl_type();
1106 l_named_role_tbl SYSTEM.PA_VARCHAR2_80_TBL_TYPE := system.pa_varchar2_80_tbl_type();
1107 l_bom_resource_id_tbl SYSTEM.PA_NUM_TBL_TYPE := system.pa_num_tbl_type();
1108 l_non_labor_resource_tbl SYSTEM.PA_VARCHAR2_20_TBL_TYPE := system.pa_varchar2_20_tbl_type();
1109 l_inventory_item_id_tbl SYSTEM.PA_NUM_TBL_TYPE := system.pa_num_tbl_type();
1110 l_item_category_id_tbl SYSTEM.PA_NUM_TBL_TYPE := system.pa_num_tbl_type();
1111 l_project_role_id_tbl SYSTEM.PA_NUM_TBL_TYPE := system.pa_num_tbl_type();
1112 l_organization_id_tbl SYSTEM.PA_NUM_TBL_TYPE := system.pa_num_tbl_type();
1113 l_fc_res_type_code_tbl SYSTEM.PA_VARCHAR2_30_TBL_TYPE := system.pa_varchar2_30_tbl_type();
1114 l_expenditure_type_tbl SYSTEM.PA_VARCHAR2_30_TBL_TYPE := system.pa_varchar2_30_tbl_type();
1115 l_expenditure_category_tbl SYSTEM.PA_VARCHAR2_30_TBL_TYPE := system.pa_varchar2_30_tbl_type();
1116 l_event_type_tbl SYSTEM.PA_VARCHAR2_30_TBL_TYPE := system.pa_varchar2_30_tbl_type();
1117 l_revenue_category_code_tbl SYSTEM.PA_VARCHAR2_30_TBL_TYPE := system.pa_varchar2_30_tbl_type();
1118 l_supplier_id_tbl SYSTEM.PA_NUM_TBL_TYPE := system.pa_num_tbl_type();
1119 l_spread_curve_id_tbl SYSTEM.PA_NUM_TBL_TYPE := system.pa_num_tbl_type();
1120 l_etc_method_code_tbl SYSTEM.PA_VARCHAR2_30_TBL_TYPE := system.pa_varchar2_30_tbl_type();
1121 l_mfc_cost_type_id_tbl SYSTEM.PA_NUM_TBL_TYPE := system.pa_num_tbl_type();
1122 l_incurred_by_res_flag_tbl SYSTEM.PA_VARCHAR2_1_TBL_TYPE := system.pa_varchar2_1_tbl_type();
1123 l_incur_by_res_class_code_tbl SYSTEM.PA_VARCHAR2_30_TBL_TYPE := system.pa_varchar2_30_tbl_type();
1124 l_incur_by_role_id_tbl SYSTEM.PA_NUM_TBL_TYPE := system.pa_num_tbl_type();
1125 l_unit_of_measure_tbl SYSTEM.PA_VARCHAR2_30_TBL_TYPE := system.pa_varchar2_30_tbl_type();
1126 l_org_id_tbl SYSTEM.PA_NUM_TBL_TYPE := system.pa_num_tbl_type();
1127 l_rate_based_flag_tbl SYSTEM.PA_VARCHAR2_1_TBL_TYPE := system.pa_varchar2_1_tbl_type();
1128 l_rate_expenditure_type_tbl SYSTEM.PA_VARCHAR2_30_TBL_TYPE := system.pa_varchar2_30_tbl_type();
1129 l_rate_func_curr_code_tbl SYSTEM.PA_VARCHAR2_30_TBL_TYPE := system.pa_varchar2_30_tbl_type();
1130 l_rate_incurred_by_org_id_tbl SYSTEM.PA_NUM_TBL_TYPE := system.pa_num_tbl_type();
1131
1132 -- Added for Bug 6856934
1133 l_structure_ver_name pa_proj_elem_ver_structure.name%type;
1134
1135 BEGIN
1136 l_overall_return_status := FND_API.G_RET_STS_SUCCESS;
1137
1138 l_task_assignment_id_tbl := p_task_assignment_id_tbl;
1139 l_task_version_id_tbl := p_task_version_id_tbl;
1140 l_budget_version_id_tbl := p_budget_version_id_tbl;
1141 l_struct_version_id_tbl := p_struct_version_id_tbl;
1142 l_update_task_asgmt_id_tbl.delete();
1143
1144 IF l_task_assignment_id_tbl.COUNT <> 0 THEN
1145
1146 IF P_DEBUG_MODE = 'Y' THEN
1147 pa_debug.write(x_module => 'pa.plsql.PA_ASSIGNMENTS_PVT.Update_Task_Assignments'
1148 ,x_msg => 'l_task_assignment_id_tbl.COUNT='||l_task_assignment_id_tbl.COUNT
1149 ,x_log_level => li_message_level);
1150 END IF;
1151
1152 l_last_bvid := l_budget_version_id_tbl(1);
1153 l_last_struct_version_id := l_struct_version_id_tbl(1);
1154 l_update_count := 0;
1155
1156 SELECT project_id
1157 INTO l_project_id
1158 FROM pa_resource_assignments
1159 WHERE resource_assignment_id = l_task_assignment_id_tbl(1);
1160
1161 FOR j IN l_task_assignment_id_tbl.FIRST .. l_task_assignment_id_tbl.LAST + 1 LOOP
1162
1163 IF P_DEBUG_MODE = 'Y' THEN
1164 pa_debug.write(x_module => 'pa.plsql.PA_ASSIGNMENTS_PVT.Update_Task_Assignments'
1165 ,x_msg => 'inside loop'
1166 ,x_log_level => li_message_level);
1167 END IF;
1168
1169 IF j = l_task_assignment_id_tbl.LAST + 1
1170 OR l_budget_version_id_tbl(j) <> l_last_bvid THEN
1171
1172 IF P_DEBUG_MODE = 'Y' THEN
1173 pa_debug.write(x_module => 'pa.plsql.PA_ASSIGNMENTS_PVT.Update_Task_Assignments'
1174 ,x_msg => 'prepare to call'
1175 ,x_log_level => li_message_level);
1176 END IF;
1177
1178 l_project_assignment_id_tbl.extend(l_update_task_asgmt_id_tbl.COUNT);
1179 l_resource_list_member_id_tbl.extend(l_update_task_asgmt_id_tbl.COUNT);
1180 l_resource_class_flag_tbl.extend(l_update_task_asgmt_id_tbl.COUNT);
1181 l_resource_class_code_tbl.extend(l_update_task_asgmt_id_tbl.COUNT);
1182 l_resource_class_id_tbl.extend(l_update_task_asgmt_id_tbl.COUNT);
1183 l_res_type_code_tbl.extend(l_update_task_asgmt_id_tbl.COUNT);
1184 l_incur_by_res_type_tbl.extend(l_update_task_asgmt_id_tbl.COUNT);
1185 l_person_id_tbl.extend(l_update_task_asgmt_id_tbl.COUNT);
1186 l_job_id_tbl.extend(l_update_task_asgmt_id_tbl.COUNT);
1187 l_person_type_code_tbl.extend(l_update_task_asgmt_id_tbl.COUNT);
1188 l_named_role_tbl.extend(l_update_task_asgmt_id_tbl.COUNT);
1189 l_bom_resource_id_tbl.extend(l_update_task_asgmt_id_tbl.COUNT);
1190 l_non_labor_resource_tbl.extend(l_update_task_asgmt_id_tbl.COUNT);
1191 l_inventory_item_id_tbl.extend(l_update_task_asgmt_id_tbl.COUNT);
1192 l_item_category_id_tbl.extend(l_update_task_asgmt_id_tbl.COUNT);
1193 l_project_role_id_tbl.extend(l_update_task_asgmt_id_tbl.COUNT);
1194 l_organization_id_tbl.extend(l_update_task_asgmt_id_tbl.COUNT);
1195 l_fc_res_type_code_tbl.extend(l_update_task_asgmt_id_tbl.COUNT);
1196 l_expenditure_type_tbl.extend(l_update_task_asgmt_id_tbl.COUNT);
1197 l_expenditure_category_tbl.extend(l_update_task_asgmt_id_tbl.COUNT);
1198 l_event_type_tbl.extend(l_update_task_asgmt_id_tbl.COUNT);
1199 l_revenue_category_code_tbl.extend(l_update_task_asgmt_id_tbl.COUNT);
1200 l_supplier_id_tbl.extend(l_update_task_asgmt_id_tbl.COUNT);
1201 l_spread_curve_id_tbl.extend(l_update_task_asgmt_id_tbl.COUNT);
1202 l_etc_method_code_tbl.extend(l_update_task_asgmt_id_tbl.COUNT);
1203 l_mfc_cost_type_id_tbl.extend(l_update_task_asgmt_id_tbl.COUNT);
1204 l_incurred_by_res_flag_tbl.extend(l_update_task_asgmt_id_tbl.COUNT);
1205 l_incur_by_res_class_code_tbl.extend(l_update_task_asgmt_id_tbl.COUNT);
1206 l_incur_by_role_id_tbl.extend(l_update_task_asgmt_id_tbl.COUNT);
1207 l_unit_of_measure_tbl.extend(l_update_task_asgmt_id_tbl.COUNT);
1208 l_org_id_tbl.extend(l_update_task_asgmt_id_tbl.COUNT);
1209 l_rate_based_flag_tbl.extend(l_update_task_asgmt_id_tbl.COUNT);
1210 l_rate_expenditure_type_tbl.extend(l_update_task_asgmt_id_tbl.COUNT);
1211 l_rate_func_curr_code_tbl.extend(l_update_task_asgmt_id_tbl.COUNT);
1212 l_rate_incurred_by_org_id_tbl.extend(l_update_task_asgmt_id_tbl.COUNT);
1213
1214 IF P_DEBUG_MODE = 'Y' THEN
1215 pa_debug.write(x_module => 'pa.plsql.PA_ASSIGNMENTS_PVT.Update_Task_Assignments'
1216 ,x_msg => 'struct_version_id='||l_last_struct_version_id||
1217 ' bvid='||l_last_bvid
1218 ,x_log_level => li_message_level);
1219 END IF;
1220
1221 FOR i IN l_update_task_asgmt_id_tbl.FIRST .. l_update_task_asgmt_id_tbl.LAST LOOP
1222
1223 l_project_assignment_id_tbl(i) := p_project_assignment_id;
1224 l_resource_list_member_id_tbl(i) := p_resource_list_member_id;
1225 l_resource_class_flag_tbl(i) := p_resource_class_flag;
1226 l_resource_class_code_tbl(i) := p_resource_class_code;
1227 l_resource_class_id_tbl(i) := p_resource_class_id;
1228 l_res_type_code_tbl(i) := p_res_type_code;
1229 l_incur_by_res_type_tbl(i) := p_incur_by_res_type;
1230 l_person_id_tbl(i) := p_person_id;
1231 l_job_id_tbl(i) := p_job_id;
1232 l_person_type_code_tbl(i) := p_person_type_code;
1233 l_named_role_tbl(i) := p_named_role;
1234 l_bom_resource_id_tbl(i) := p_bom_resource_id;
1235 l_non_labor_resource_tbl(i) := p_non_labor_resource;
1236 l_inventory_item_id_tbl(i) := p_inventory_item_id;
1237 l_item_category_id_tbl(i) := p_item_category_id;
1238 l_project_role_id_tbl(i) := p_project_role_id;
1239 l_organization_id_tbl(i) := p_organization_id;
1240 l_fc_res_type_code_tbl(i) := p_fc_res_type_code;
1241 l_expenditure_type_tbl(i) := p_expenditure_type;
1242 l_expenditure_category_tbl(i) := p_expenditure_category;
1243 l_event_type_tbl(i) := p_event_type;
1244 l_revenue_category_code_tbl(i) := p_revenue_category_code;
1245 l_supplier_id_tbl(i) := p_supplier_id;
1246 l_spread_curve_id_tbl(i) := p_spread_curve_id;
1247 l_etc_method_code_tbl(i) := p_etc_method_code;
1248 l_mfc_cost_type_id_tbl(i) := p_mfc_cost_type_id;
1249 l_incurred_by_res_flag_tbl(i) := p_incurred_by_res_flag;
1250 l_incur_by_res_class_code_tbl(i) := p_incur_by_res_class_code;
1251 l_incur_by_role_id_tbl(i) := p_incur_by_role_id;
1252 l_unit_of_measure_tbl(i) := p_unit_of_measure;
1253 l_org_id_tbl(i) := p_org_id;
1254 l_rate_based_flag_tbl(i) := p_rate_based_flag;
1255 l_rate_expenditure_type_tbl(i) := p_rate_expenditure_type;
1256 l_rate_func_curr_code_tbl(i) := p_rate_func_curr_code;
1257 l_rate_incurred_by_org_id_tbl(i) := p_rate_incurred_by_org_id;
1258
1259 IF P_DEBUG_MODE = 'Y' THEN
1260 pa_debug.write(x_module => 'pa.plsql.PA_ASSIGNMENTS_PVT.Update_Task_Assignments'
1261 ,x_msg => 'i='||i||'version id='||l_update_task_version_id_tbl(i)||
1262 ' task_id='||l_update_task_asgmt_id_tbl(i)||
1263 ' asgmt_id='||l_project_assignment_id_tbl(i)||
1264 ' rlm='||l_resource_list_member_id_tbl(i)
1265 ,x_log_level => li_message_level);
1266
1267 pa_debug.write(x_module => 'pa.plsql.PA_ASSIGNMENTS_PVT.Update_Task_Assignments'
1268 ,x_msg => 'i='||i||'res_class_flag='||l_resource_class_flag_tbl(i)||
1269 ' res_class_code='||l_resource_class_code_tbl(i)||
1270 ' res_class_id='||l_resource_class_id_tbl(i)||
1271 ' res_type_code='||l_res_type_code_tbl(i)||
1272 ' person_id='||l_person_id_tbl(i)
1273 ,x_log_level => li_message_level);
1274
1275 pa_debug.write(x_module => 'pa.plsql.PA_ASSIGNMENTS_PVT.Update_Task_Assignments'
1276 ,x_msg => 'i='||i||'job_id='||l_job_id_tbl(i)||
1277 ' person_type_code='||l_person_type_code_tbl(i)||
1278 ' named_role='||l_named_role_tbl(i)||
1279 ' bom_res_id='||l_bom_resource_id_tbl(i)||
1280 ' non_labor_res='||l_non_labor_resource_tbl(i)
1281 ,x_log_level => li_message_level);
1282
1283 pa_debug.write(x_module => 'pa.plsql.PA_ASSIGNMENTS_PVT.Update_Task_Assignments'
1284 ,x_msg => 'i='||i||'inven_item='||l_inventory_item_id_tbl(i)||
1285 ' item_cat_id='||l_item_category_id_tbl(i)||
1286 ' proj_role_id='||l_project_role_id_tbl(i)||
1287 ' org_id='||l_organization_id_tbl(i)||
1288 ' fc_res_type='||l_fc_res_type_code_tbl(i)||
1289 ' exp_type='||l_expenditure_type_tbl(i)
1290 ,x_log_level => li_message_level);
1291
1292 pa_debug.write(x_module => 'pa.plsql.PA_ASSIGNMENTS_PVT.Update_Task_Assignments'
1293 ,x_msg => 'i='||i||'exp_cat='||l_expenditure_category_tbl(i)||
1294 ' event_type='||l_event_type_tbl(i)||
1295 ' rev_cat_code='||l_revenue_category_code_tbl(i)||
1296 ' supplier_id='||l_supplier_id_tbl(i)||
1297 ' spread_curve='||l_spread_curve_id_tbl(i)
1298 ,x_log_level => li_message_level);
1299
1300 pa_debug.write(x_module => 'pa.plsql.PA_ASSIGNMENTS_PVT.Update_Task_Assignments'
1301 ,x_msg => 'i='||i||'etc_method_code='||l_etc_method_code_tbl(i)||
1302 ' mfc_cost_type='||l_mfc_cost_type_id_tbl(i)||
1303 ' inc_by_res_flag='||l_incurred_by_res_flag_tbl(i)||
1304 ' inc_by_res_class='||l_incur_by_res_class_code_tbl(i)||
1305 ' inc_by_role_id='||l_incur_by_role_id_tbl(i)||
1306 ' unit_of_measure'||l_unit_of_measure_tbl(i)
1307 ,x_log_level => li_message_level);
1308 END IF;
1309 END LOOP;
1310
1311 -- Bug 4059887: Check privilege of update task assignments
1312 /* commented and changed as below for Bug 6856934
1313 l_edit_task_ok := pa_task_assignment_utils.check_edit_task_ok(
1314 P_PROJECT_ID => l_project_id
1315 ,P_STRUCTURE_VERSION_ID => l_last_struct_version_id
1316 ,P_CURR_STRUCT_VERSION_ID => l_last_struct_version_id);
1317 */
1318 l_edit_task_ok := PA_PROJECT_STRUCTURE_UTILS.IS_STRUC_VER_LOCKED_BY_USER(
1319 p_user_id => FND_GLOBAL.USER_ID,
1320 p_structure_version_id => l_last_struct_version_id);
1321
1322 IF P_DEBUG_MODE = 'Y' THEN
1323 pa_debug.write(x_module => 'pa.plsql.PA_ASSIGNMENTS_PVT.Update_Task_Assignments'
1324 ,x_msg => 'l_edit_task_ok='||l_edit_task_ok
1325 ,x_log_level => li_message_level);
1326 END IF;
1327
1328 -- IF 'Y' = l_edit_task_ok THEN Commented and changed as below for Bug 6856934
1329 IF nvl(l_edit_task_ok,'X') <> 'O' THEN
1330
1331 l_msg_count1 := FND_MSG_PUB.Count_Msg;
1332 pa_fp_planning_transaction_pub.update_planning_transactions (
1333 p_context => 'TASK_ASSIGNMENT'
1334 ,p_calling_context => 'PA_PROJECT_ASSIGNMENT' -- Added for Bug 6856934
1335 ,p_struct_elem_version_id => l_last_struct_version_id
1336 ,p_budget_version_id => l_last_bvid
1337 ,p_task_elem_version_id_tbl => l_update_task_version_id_tbl
1338 ,p_resource_assignment_id_tbl => l_update_task_asgmt_id_tbl
1339 ,p_project_assignment_id_tbl => l_project_assignment_id_tbl
1340 -- resource_list_member_id
1341 ,p_resource_list_member_id_tbl => l_resource_list_member_id_tbl
1342 -- pass in all resource attributes
1343 ,p_resource_class_flag_tbl => l_resource_class_flag_tbl
1344 ,p_resource_class_code_tbl => l_resource_class_code_tbl
1345 ,p_resource_class_id_tbl => l_resource_class_id_tbl
1346 ,p_res_type_code_tbl => l_res_type_code_tbl
1347 -- ,p_incur_by_res_type_tbl => l_incur_by_res_type_tbl
1348 ,p_person_id_tbl => l_person_id_tbl
1349 ,p_job_id_tbl => l_job_id_tbl
1350 ,p_person_type_code => l_person_type_code_tbl
1351 ,p_named_role_tbl => l_named_role_tbl -- named_role
1352 ,p_bom_resource_id_tbl => l_bom_resource_id_tbl
1353 ,p_non_labor_resource_tbl => l_non_labor_resource_tbl
1354 ,p_inventory_item_id_tbl => l_inventory_item_id_tbl
1355 ,p_item_category_id_tbl => l_item_category_id_tbl
1356 --Bug 4170933 ,p_project_role_id_tbl => l_project_role_id_tbl
1357 ,p_organization_id_tbl => l_organization_id_tbl
1358 ,p_fc_res_type_code_tbl => l_fc_res_type_code_tbl
1359 ,p_expenditure_type_tbl => l_expenditure_type_tbl
1360 ,p_expenditure_category_tbl => l_expenditure_category_tbl
1361 ,p_event_type_tbl => l_event_type_tbl
1362 ,p_revenue_category_code_tbl => l_revenue_category_code_tbl
1363 ,p_supplier_id_tbl => l_supplier_id_tbl
1364 ,p_spread_curve_id_tbl => l_spread_curve_id_tbl
1365 ,p_etc_method_code_tbl => l_etc_method_code_tbl
1366 ,p_mfc_cost_type_id_tbl => l_mfc_cost_type_id_tbl
1367 ,p_incurred_by_res_flag_tbl => l_incurred_by_res_flag_tbl
1368 ,p_incur_by_res_class_code_tbl => l_incur_by_res_class_code_tbl
1369 ,p_incur_by_role_id_tbl => l_incur_by_role_id_tbl
1370 ,p_unit_of_measure_tbl => l_unit_of_measure_tbl
1371 -- ,p_org_id_tbl => l_org_id_tbl
1372 -- ,p_rate_based_flag_tbl => l_rate_based_flag_tbl
1373 -- ,p_rate_expenditure_type_tbl => l_rate_expenditure_type_tbl
1374 -- ,p_rate_func_curr_code_tbl => l_rate_func_curr_code_tbl
1375 -- ,p_rate_incurred_by_org_id_tbl => l_rate_incurred_by_org_id_tbl
1376 ,x_return_status => l_return_status
1377 ,x_msg_data => l_msg_data
1378 ,x_msg_count => l_msg_count);
1379
1380 IF P_DEBUG_MODE = 'Y' THEN
1381 pa_debug.write(x_module => 'pa.plsql.PA_ASSIGNMENTS_PVT.Update_Task_Assignments'
1382 ,x_msg => 'return status'||l_return_status
1383 ,x_log_level => li_message_level);
1384 END IF;
1385
1386 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1387 l_overall_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1388
1389 -- bug 4117269: Remove the error messages added by the above API
1390 l_msg_count2 := FND_MSG_PUB.Count_Msg;
1391 IF P_DEBUG_MODE = 'Y' THEN
1392 pa_debug.write(x_module => 'pa.plsql.PA_ASSIGNMENTS_PVT.Update_Task_Assignments'
1393 ,x_msg => ' count1='||l_msg_count1||
1394 ' count2='||l_msg_count2
1395 ,x_log_level => li_message_level);
1396 END IF;
1397
1398 FOR k IN (l_msg_count1 + 1)..l_msg_count2 LOOP
1399 IF P_DEBUG_MODE = 'Y' THEN
1400 pa_debug.write(x_module => 'pa.plsql.PA_ASSIGNMENTS_PVT.Update_Task_Assignments'
1401 ,x_msg => 'Deleting message at index: ' || FND_MSG_PUB.Count_Msg
1402 ,x_log_level => li_message_level);
1403 END IF;
1404 FND_MSG_PUB.delete_msg(p_msg_index => FND_MSG_PUB.Count_Msg);
1405 END LOOP;
1406
1407 PA_UTILS.Add_Message( p_app_short_name => 'PA'
1408 ,p_msg_name => 'PA_NO_UPDATE_ASGN_WITH_ACTUALS' );
1409 PA_ASSIGNMENTS_PUB.g_error_exists := FND_API.G_TRUE;
1410
1411 END IF;
1412
1413 ELSE -- If nvl(l_edit_task_ok,'X') <> 'O'
1414
1415 IF p_mode <> 'CREATE' THEN
1416 -- Bug 6856934
1417 Begin
1418 select ppevs.name into l_structure_ver_name
1419 from pa_proj_elem_ver_structure ppevs
1420 where ppevs.project_id = l_project_id
1421 and ppevs.element_version_id = l_last_struct_version_id;
1422 Exception when NO_DATA_FOUND then
1423 null;
1424 end;
1425 PA_UTILS.Add_Message(
1426 p_app_short_name => 'PA'
1427 ,p_msg_name => 'PA_WORKPLAN_LOCKED_NO_UPD'
1428 ,p_token1 => 'TEAM_ROLE'
1429 ,p_value1 => p_named_role
1430 ,p_token2 => 'WP_VERSION_NAME'
1431 ,p_value2 => l_structure_ver_name);
1432 l_overall_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1433 END IF;
1434
1435 END IF; -- IF 'Y' = l_edit_task_ok THEN
1436
1437 l_update_count := 0;
1438 l_update_task_version_id_tbl.delete();
1439 l_update_task_asgmt_id_tbl.delete();
1440 l_project_assignment_id_tbl.delete();
1441 l_resource_list_member_id_tbl.delete();
1442 l_resource_class_flag_tbl.delete();
1443 l_resource_class_code_tbl.delete();
1444 l_resource_class_id_tbl.delete();
1445 l_res_type_code_tbl.delete();
1446 l_incur_by_res_type_tbl.delete();
1447 l_person_id_tbl.delete();
1448 l_job_id_tbl.delete();
1449 l_person_type_code_tbl.delete();
1450 l_named_role_tbl.delete();
1451 l_bom_resource_id_tbl.delete();
1452 l_non_labor_resource_tbl.delete();
1453 l_inventory_item_id_tbl.delete();
1454 l_item_category_id_tbl.delete();
1455 l_project_role_id_tbl.delete();
1456 l_organization_id_tbl.delete();
1457 l_fc_res_type_code_tbl.delete();
1458 l_expenditure_type_tbl.delete();
1459 l_expenditure_category_tbl.delete();
1460 l_event_type_tbl.delete();
1461 l_revenue_category_code_tbl.delete();
1462 l_supplier_id_tbl.delete();
1463 l_spread_curve_id_tbl.delete();
1464 l_etc_method_code_tbl.delete();
1465 l_mfc_cost_type_id_tbl.delete();
1466 l_incurred_by_res_flag_tbl.delete();
1467 l_incur_by_res_class_code_tbl.delete();
1468 l_incur_by_role_id_tbl.delete();
1469 l_unit_of_measure_tbl.delete();
1470 l_org_id_tbl.delete();
1471 l_rate_based_flag_tbl.delete();
1472 l_rate_expenditure_type_tbl.delete();
1473 l_rate_func_curr_code_tbl.delete();
1474 l_rate_incurred_by_org_id_tbl.delete();
1475
1476 END IF; -- budget_version_id different
1477
1478 IF j <> l_task_assignment_id_tbl.LAST + 1 THEN
1479 IF P_DEBUG_MODE = 'Y' THEN
1480 pa_debug.write(x_module => 'pa.plsql.PA_ASSIGNMENTS_PVT.Update_Task__Assignments'
1481 ,x_msg => 'j <> l_task_assignment_id_tbl.LAST + 1'
1482 ,x_log_level => li_message_level);
1483 END IF;
1484
1485 l_update_count := l_update_count + 1;
1486 l_update_task_asgmt_id_tbl.extend(1);
1487 l_update_task_version_id_tbl.extend(1);
1488 l_update_task_asgmt_id_tbl(l_update_count) := l_task_assignment_id_tbl(j);
1489 l_update_task_version_id_tbl(l_update_count) := l_task_version_id_tbl(j);
1490 l_last_bvid := l_budget_version_id_tbl(j);
1491 l_last_struct_version_id := l_struct_version_id_tbl(j);
1492 END IF;
1493
1494 END LOOP;
1495
1496 END IF; -- l_task_assignment_id_tbl.COUNT <> 0 THEN
1497
1498 x_return_status := l_overall_return_status;
1499
1500 -- Reset the error stack when returning to the calling program
1501 PA_DEBUG.Reset_err_stack;
1502
1503 EXCEPTION
1504 WHEN OTHERS THEN
1505
1506 -- Set the excetption Message and the stack
1507 FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_ASSIGNMENTS_PVT.Update_Task_Assignments'
1508 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
1509 --
1510 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1511 RAISE; -- This is optional depending on the needs
1512
1513
1514 END Update_Task_Assignments;
1515
1516
1517
1518 END pa_assignments_pvt;