[Home] [Help]
PACKAGE BODY: APPS.PA_ASSIGNMENTS_PVT
Source
1 PACKAGE BODY pa_assignments_pvt AS
2 /*$Header: PARAPVTB.pls 120.6.12010000.5 2010/03/30 05:53:13 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 bv.budget_type_code IS NULL -- added for bug#8247628
594 AND ra.budget_version_id = bv.budget_version_id
595 AND bv.project_structure_version_id = evs.element_version_id
596 -- AND ra.project_id = l_assignment_rec.project_id
597 -- AND pa.assignment_id = p_assignment_id -- 5110598 Removed table usage
598 -- AND ra.project_id = pa.project_id -- 5110598 Removed table usage
599 AND ra.project_assignment_id = p_assignment_id
600 AND evs.status_code = 'STRUCTURE_WORKING')
601 UNION ALL
602 (SELECT ra.resource_assignment_id, ra.wbs_element_version_id, bv.budget_version_id, bv.project_structure_version_id
603 FROM PA_RESOURCE_ASSIGNMENTS ra
604 ,PA_BUDGET_VERSIONS bv
605 ,PA_PROJ_ELEM_VER_STRUCTURE evs
606 ,PA_PROJ_WORKPLAN_ATTR pwa
607 -- ,PA_PROJECT_ASSIGNMENTS pa -- 5110598 Removed PA_PROJECT_ASSIGNMENTS table usage
608 WHERE pwa.wp_enable_Version_flag = 'N'
609 AND pwa.project_id = ra.project_id
610 AND pwa.proj_element_id = evs.proj_element_id
611 AND ra.project_id = bv.project_id
612 AND bv.project_id = evs.project_id
613 AND bv.budget_type_code IS NULL -- added for bug#8247628
614 AND ra.budget_version_id = bv.budget_version_id
615 AND bv.project_structure_version_id = evs.element_version_id
616 -- AND ra.project_id = l_assignment_rec.project_id
617 -- AND pa.assignment_id = p_assignment_id -- 5110598 Removed table usage
618 -- AND ra.project_id = pa.project_id -- 5110598 Removed table usage
619 AND ra.project_assignment_id = p_assignment_id)
620 )
621 ORDER BY budget_version_id, project_structure_version_id;
622
623 CURSOR get_res_mand_attributes IS
624 SELECT rf.ROLE_ENABLED_FLAG
625 FROM pa_res_formats_b rf,
626 pa_resource_list_members rlm,
627 pa_project_assignments pa
628 WHERE pa.assignment_id = p_assignment_id
629 AND pa.resource_list_member_id IS NOT NULL
630 AND rlm.resource_list_member_id = pa.resource_list_member_id
631 AND rlm.res_format_id = rf.res_format_id;
632
633
634 BEGIN
635
636 -- Initialize the Error Stack
637 PA_DEBUG.init_err_stack('PA_ASSIGNMENTS_PVT.Delete_Assignment');
638
639 -- Initialize the return status to success
640 x_return_status := FND_API.G_RET_STS_SUCCESS;
641
642 --Log Message
643 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
644 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_ASSIGNMENTS_PVT.Delete_Assignment.begin'
645 ,x_msg => 'Beginning of Delete_Assignment.'
646 ,x_log_level => 5);
647 END IF;
648
649 -- Delete Subteam Party if assignment id exists
650 IF (p_assignment_id IS NOT NULL AND p_assignment_id <>FND_API.G_MISS_NUM) THEN
651
652 --Log Message
653 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
654 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_ASSIGNMENTS_PVT.Delete_Assignment.delete_subteam_party'
655 ,x_msg => 'Deleting subteam party'
656 ,x_log_level => 5);
657 END IF;
658 PA_PROJECT_SUBTEAM_PARTIES_PVT.Delete_SubteamParty_By_Obj
659 ( p_validate_only => p_validate_only
660 ,p_object_type => 'PA_PROJECT_ASSIGNMENTS'
661 ,p_object_id => p_assignment_id
662 ,p_init_msg_list => FND_API.G_FALSE -- Added for bug 5130421
663 ,x_return_status => l_return_status
664 ,x_msg_count => l_msg_count
665 ,x_msg_data => l_msg_data
666 );
667 --dbms_output.put_line('return status is '||l_return_status);
668 --dbms_output.put_line('message count is '||l_msg_count);
669 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
670 -- PA_UTILS.Add_Message( p_app_short_name => 'PA'
671 -- ,p_msg_name => l_error_message_code);
672 PA_ASSIGNMENTS_PUB.g_error_exists := FND_API.G_TRUE;
673 END IF;
674
675 --Bug 6330317
676 IF P_DEBUG_MODE = 'Y' THEN
677 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_ASSIGNMENTS_PVT.Delete_Assignment.delete_rows'
678 ,x_msg => 'Deleting Conflict History Information'
679 ,x_log_level => 5);
680 END IF;
681
682 --Bug#9356483
683 IF p_assignment_type IN ('STAFFED_ASSIGNMENT','STAFFED_ADMIN_ASSIGNMENT') THEN
684
685 PA_ASGN_CONFLICT_HIST_PKG.Delete_rows
686 ( p_assignment_id => p_assignment_id
687 ,x_return_status => l_return_status
688 ,x_msg_count => l_msg_count
689 ,x_msg_data => l_msg_data
690 );
691
692 END IF;
693 --Bug#9356483
694
695 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
696 -- PA_UTILS.Add_Message( p_app_short_name => 'PA'
697 -- ,p_msg_name => l_error_message_code);
698 PA_ASSIGNMENTS_PUB.g_error_exists := FND_API.G_TRUE;
699 END IF;
700 END IF;
701
702 -- FP.M Development
703 -- run the cursor before the assignment/requirement is deleted
704 OPEN get_linked_res_asgmts;
705 FETCH get_linked_res_asgmts
706 BULK COLLECT INTO l_task_assignment_id_tbl,
707 l_task_version_id_tbl,
708 l_budget_version_id_tbl,
709 l_struct_version_id_tbl;
710 CLOSE get_linked_res_asgmts;
711
712 -- FP.M Development
713 -- Check whether role is part of the deleted assignment's
714 -- planning resource's resource format
715 -- 4117262: Move this cursor fetch to BEFORE deleting the assignment
716 l_cur_role_flag := NULL;
717 OPEN get_res_mand_attributes;
718 FETCH get_res_mand_attributes INTO l_cur_role_flag;
719 CLOSE get_res_mand_attributes;
720
721 IF p_assignment_type = 'OPEN_ASSIGNMENT' THEN
722
723 --Log Message
724 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
725 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_ASSIGNMENTS_PVT.Delete_Assignment.delete_open'
726 ,x_msg => 'Deleting Requirement'
727 ,x_log_level => 5);
728 END IF;
729
730 PA_OPEN_ASSIGNMENT_PVT.Delete_Open_Assignment
731 ( p_assignment_row_id => p_assignment_row_id
732 ,p_assignment_id => p_assignment_id
733 ,p_record_version_number => p_record_version_number
734 ,p_calling_module => p_calling_module
735 ,p_commit => p_commit
736 ,p_validate_only => p_validate_only
737 ,x_return_status => x_return_status
738 );
739 --
740 ELSE
741
742 --Log Message
743 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
744 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_ASSIGNMENTS_PVT.Delete_Assignment.delete_staff'
745 ,x_msg => 'Deleting Assignment'
746 ,x_log_level => 5);
747 END IF;
748
749 PA_STAFFED_ASSIGNMENT_PVT.Delete_Staffed_Assignment
750 ( p_assignment_row_id => p_assignment_row_id
751 ,p_assignment_id => p_assignment_id
752 ,p_record_version_number => p_record_version_number
753 ,p_project_party_id => p_project_party_id
754 ,p_calling_module => p_calling_module
755 ,p_commit => p_commit
756 ,p_validate_only => p_validate_only
757 ,x_return_status => x_return_status
758 );
759 END IF;
760
761 -- FP.M Development
762 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
763 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_ASSIGNMENTS_PVT.Delete_Assignment.delete_staff'
764 ,x_msg => 'Call Update_Task_Assignments'
765 ,x_log_level => 5);
766 END IF;
767
768
769 -- 1. Change project_assignment_id to NULL (-1)
770 -- 2. Don't wipe out project_role_id,
771 -- 3. Wipe out named_role when it is not a mandatory attribute
772 -- of planning resource
773 -- 4117262: Modified to check the flag value only
774 IF l_cur_role_flag = 'Y' THEN
775
776 Update_Task_Assignments(
777 p_task_assignment_id_tbl => l_task_assignment_id_tbl
778 ,p_task_version_id_tbl => l_task_version_id_tbl
779 ,p_budget_version_id_tbl => l_budget_version_id_tbl
780 ,p_struct_version_id_tbl => l_struct_version_id_tbl
781 ,p_project_assignment_id => -1
782 ,x_return_status => l_return_status
783 );
784 ELSE
785 Update_Task_Assignments(
786 p_task_assignment_id_tbl => l_task_assignment_id_tbl
787 ,p_task_version_id_tbl => l_task_version_id_tbl
788 ,p_budget_version_id_tbl => l_budget_version_id_tbl
789 ,p_struct_version_id_tbl => l_struct_version_id_tbl
790 ,p_project_assignment_id => -1
791 ,p_named_role => FND_API.G_MISS_CHAR
792 ,x_return_status => l_return_status
793 );
794 END IF;
795
796 /*
797
798
799 BULK COLLECT INTO l_task_assignment_id_tbl
800
801
802 l_project_assignment_id_tbl.extend(l_task_assignment_id_tbl.count);
803
804 IF l_task_assignment_id_tbl.COUNT <>0 THEN
805 FOR i IN l_task_assignment_id_tbl.FIRST .. l_task_assignment_id_tbl.LAST LOOP
806 l_project_assignment_id_tbl(i) := NULL;
807 END LOOP;
808
809 pa_fp_planning_transaction_pub.update_planning_transactions
810 (
811 p_context => 'TASK_ASSIGNMENT'
812 ,p_resource_assignment_id_tbl => l_task_assignment_id_tbl
813 ,p_project_assignment_id_tbl => l_project_assignment_id_tbl
814 ,X_Return_Status => l_return_status
815 ,X_Msg_Data => l_msg_data
816 ,X_Msg_Count => l_msg_count);
817 END IF;
818 */
819
820 -- Reset the error stack when returning to the calling program
821 PA_DEBUG.Reset_Err_Stack;
822
823 -- If g_error_exists is TRUE then set the x_return_status to 'E'
824
825 IF PA_ASSIGNMENTS_PUB.g_error_exists = FND_API.G_TRUE THEN
826
827 x_return_status := FND_API.G_RET_STS_ERROR;
828
829 END IF;
830
831
832 EXCEPTION
833 WHEN OTHERS THEN
834
835 -- Set the excetption Message and the stack
836 FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_ASSIGNMENTS_PVT.Delete_Assignment'
837 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
838 --
839 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
840 RAISE;
841 END Delete_Assignment;
842
843 /* --------------------------------------------------------------------
844 FUNCTION: Update_Revenue_Bill_Rate
845 PURPOSE: This API updates the revenue_bill_rate for assignments passed
846 in. It should be only called by the Project Forecast Process.
847 -------------------------------------------------------------------- */
848 /* PROCEDURE Update_Revenue_Bill_Rate (p_assignment_id_tbl IN SYSTEM.pa_num_tbl_type,
849 p_revenue_bill_rate_tbl IN SYSTEM.pa_num_tbl_type,
850 x_return_status OUT VARCHAR2) */
851 PROCEDURE Update_Revenue_Bill_Rate
852 ( p_assignment_id_tbl IN PA_PLSQL_DATATYPES.IdTabTyp
853 ,p_revenue_bill_rate_tbl IN PA_PLSQL_DATATYPES.NumTabTyp
854 ,x_return_status OUT NOCOPY VARCHAR2 ) --File.Sql.39 bug 4440895
855 IS
856 BEGIN
857
858 PA_DEBUG.init_err_stack('PA_ASSIGNMENTS_PVT.Update_Revenue_Bill_Rate');
859
860 x_return_status := FND_API.G_RET_STS_SUCCESS;
861
862 IF p_assignment_id_tbl.COUNT > 0 THEN
863 FOR i IN p_assignment_id_tbl.FIRST .. p_assignment_id_tbl.LAST LOOP
864
865 pa_project_assignments_pkg.Update_row (
866 p_assignment_id => p_assignment_id_tbl(i),
867 p_revenue_bill_rate => p_revenue_bill_rate_tbl(i),
868 x_return_status => x_return_status);
869 END LOOP;
870 END IF;
871
872 EXCEPTION
873 WHEN OTHERS THEN
874 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
875
876 fnd_msg_pub.add_exc_msg
877 (p_pkg_name => 'PA_ASSIGNMENTS_PVT',
878 p_procedure_name => 'Update_Revenue_Bill_Rate');
879
880 RAISE;
881 END Update_Revenue_Bill_Rate;
882
883 --
884 --
885
886 /* Added procedure Update_Transfer_Price for bug 3051110
887 This Procedure calls update_row which will update the record in pa_project_assignments table
888 with the transfer price rate and transfer price rate curr passed.
889 */
890
891 PROCEDURE Update_Transfer_Price
892 ( p_assignment_id IN pa_project_assignments.assignment_id%TYPE
893 ,p_transfer_price_rate IN pa_project_assignments.transfer_price_rate%TYPE
894 ,p_transfer_pr_rate_curr IN pa_project_assignments.transfer_pr_rate_curr%TYPE
895 ,p_debug_mode IN VARCHAR2 default 'N'
896 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
897 )
898 IS
899 BEGIN
900
901 x_return_status := FND_API.G_RET_STS_SUCCESS;
902
903 pa_project_assignments_pkg.update_row(
904 p_assignment_id => p_assignment_id,
905 p_transfer_price_rate => p_transfer_price_rate,
906 p_transfer_pr_rate_curr => p_transfer_pr_rate_curr,
907 x_return_status => x_return_status);
908
909 EXCEPTION
910 WHEN OTHERS THEN
911 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
912
913 fnd_msg_pub.add_exc_msg
914 (p_pkg_name => 'PA_ASSIGNMENTS_PVT',
915 p_procedure_name => 'Update_Transfer_Price');
916
917 RAISE;
918 END Update_Transfer_Price;
919
920 /* Added procedure Calc_Init_Transfer_Price for bug 3051110. (TP Enhancement)
921 This Procedure calls the Billing API which calculates the TP Rate and returns.
922 The rate is used for updation in the pa_project_assignments table.
923 */
924
925 PROCEDURE Calc_Init_Transfer_Price
926 ( p_assignment_id IN pa_project_assignments.assignment_id%TYPE
927 ,p_start_date IN pa_project_assignments.start_date%TYPE
928 ,p_debug_mode IN VARCHAR2 DEFAULT 'N'
929 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
930 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
931 ,x_msg_count OUT NOCOPY Number --File.Sql.39 bug 4440895
932 )
933 IS
934
935 lx_transfer_price_rate pa_project_assignments.transfer_price_rate%TYPE;
936 lx_transfer_pr_rate_curr pa_project_assignments.transfer_pr_rate_curr%TYPE;
937 l_start_date pa_project_assignments.start_date%TYPE := Null;
938
939 BEGIN
940
941 if p_debug_mode = 'Y' THEN
942 pa_debug.write('PA_ASSIGNMENT_PVT.Calc_Init_Transfer_Price', 'Calling PA_CC_TRANSFER_PRICE.Get_Initial_Transfer_Price' , 3);
943 end if;
944
945 /* Calling get_initial_transfer_price to get the transfer_price_rate and transfer_price_rate */
946
947 BEGIN
948
949 /*
950 This select is done for getting the min item_date from pa_forecast_items table.
951 There can be case that the assignment start date is a holiday (Exception in the calendar)
952 and hence no fi will be created for the assignment start date. So we need to get the first
953 working date which is done in the select below. If the min(item_date) is null, we dont call the
954 pa_cc_transfer_price.get_initial_transfer_price api
955 */
956
957 select min(item_date) into l_start_date
958 from pa_forecast_items
959 WHERE Assignment_id = p_assignment_id
960 AND Error_Flag = 'N'
961 AND Delete_Flag = 'N';
962
963 EXCEPTION WHEN NO_DATA_FOUND THEN
964 l_start_date := NULL;
965 lx_transfer_price_rate := NULL;
966 lx_transfer_pr_rate_curr := NULL;
967 END;
968
969 if p_debug_mode = 'Y' THEN
970 pa_debug.write('PA_ASSIGNMENT_PVT.Calc_Init_Transfer_Price','The starting date as in forecast items table:'||l_start_date, 3);
971 end if;
972
973 IF l_start_date IS NOT NULL THEN
974
975 IF P_DEBUG_MODE = 'Y' THEN
976 pa_debug.write(x_module => 'pa.plsql.PA_ASSIGNMENTS_PVT.Calc_Init_Transfer_Price'
977 ,x_msg => 'asgmt_id='||p_assignment_id||
978 ' start_date='||l_start_date||
979 ' debug_mod='||p_debug_mode
980 ,x_log_level => li_message_level);
981 END IF;
982
983 PA_CC_TRANSFER_PRICE.Get_Initial_Transfer_Price
984 ( p_assignment_id => p_assignment_id
985 ,p_start_date => l_start_date
986 ,p_debug_mode => p_debug_mode
987 ,x_transfer_price_rate => lx_transfer_price_rate
988 ,x_transfer_pr_rate_curr => lx_transfer_pr_rate_curr
989 ,x_return_status => x_return_status
990 ,x_msg_data => x_msg_data
991 ,x_msg_count => x_msg_count
992 );
993
994 if p_debug_mode = 'Y' THEN
995 pa_debug.write('PA_ASSIGNMENT_PVT.Calc_Init_Transfer_Price',' Out of PA_CC_TRANSFER_PRICE.Get_Initial_Transfer_Price', 3);
996 pa_debug.write('PA_ASSIGNMENT_PVT.Calc_Init_Transfer_Price','status is :'||x_return_status||' : x_msg_count:'||x_msg_count, 3);
997 pa_debug.write('PA_ASSIGNMENT_PVT.Calc_Init_Transfer_Price','Transfer Price Rate:'||lx_transfer_price_rate, 3);
998 pa_debug.write('PA_ASSIGNMENT_PVT.Calc_Init_Transfer_Price','Transfer Price Rate curr:'||lx_transfer_pr_rate_curr, 3);
999 end if;
1000
1001 /* Call to update_Transfer_Price to udpate the transfer_price_Rate and transfer_pr_rate_curr only if no error */
1002
1003 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
1004
1005 IF p_debug_mode = 'Y' THEN
1006 pa_debug.write('PA_ASSIGNMENT_PVT.Calc_Init_Transfer_Price',' Calling update_transfer_price with proper Values', 3);
1007 END IF;
1008
1009 PA_ASSIGNMENTS_PVT.Update_Transfer_Price
1010 (
1011 p_assignment_id => p_assignment_id
1012 ,p_transfer_price_rate => lx_transfer_price_rate
1013 ,p_transfer_pr_rate_curr => lx_transfer_pr_rate_curr
1014 ,x_return_status => x_return_status
1015 );
1016
1017 END IF;
1018
1019 if p_debug_mode = 'Y' THEN
1020 pa_debug.write('PA_ASSIGNMENT_PVT.Calc_Init_Transfer_Price',' Out of update_transfer_price', 3);
1021 pa_debug.write('PA_ASSIGNMENT_PVT.Calc_Init_Transfer_Price',' x_return_status: '||x_return_status, 3);
1022 end if;
1023
1024 END IF;
1025 -- 4537865 : Included Exception Block
1026 EXCEPTION
1027 WHEN OTHERS THEN
1028 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1029 x_msg_count := 1;
1030 x_msg_data := SUBSTRB(SQLERRM,1,240);
1031
1032 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_ASSIGNMENTS_PVT'
1033 ,p_procedure_name => 'Calc_Init_Transfer_Price'
1034 ,P_error_text => x_msg_data );
1035 RAISE ;
1036 END Calc_Init_Transfer_Price;
1037
1038 PROCEDURE Update_Task_Assignments
1039 ( p_mode IN VARCHAR2 := 'UPDATE'
1040 ,p_task_assignment_id_tbl IN system.pa_num_tbl_type
1041 ,p_task_version_id_tbl IN system.pa_num_tbl_type
1042 ,p_budget_version_id_tbl IN system.pa_num_tbl_type
1043 ,p_struct_version_id_tbl IN system.pa_num_tbl_type
1044 ,p_project_assignment_id IN NUMBER := NULL
1045 ,p_resource_list_member_id IN NUMBER := NULL
1046 -- pass in all resource attributes
1047 ,p_resource_class_flag IN VARCHAR2 := NULL
1048 ,p_resource_class_code IN VARCHAR2 := NULL
1049 ,p_resource_class_id IN NUMBER := NULL
1050 ,p_res_type_code IN VARCHAR2 := NULL
1051 ,p_incur_by_res_type IN VARCHAR2 := NULL
1052 ,p_person_id IN NUMBER := NULL
1053 ,p_job_id IN NUMBER := NULL
1054 ,p_person_type_code IN VARCHAR2 := NULL
1055 ,p_named_role IN VARCHAR2 := NULL -- named_role
1056 ,p_bom_resource_id IN NUMBER := NULL
1057 ,p_non_labor_resource IN VARCHAR2 := NULL
1058 ,p_inventory_item_id IN NUMBER := NULL
1059 ,p_item_category_id IN NUMBER := NULL
1060 ,p_project_role_id IN NUMBER := NULL
1061 ,p_organization_id IN NUMBER := NULL
1062 ,p_fc_res_type_code IN VARCHAR2 := NULL
1063 ,p_expenditure_type IN VARCHAR2 := NULL
1064 ,p_expenditure_category IN VARCHAR2 := NULL
1065 ,p_event_type IN VARCHAR2 := NULL
1066 ,p_revenue_category_code IN VARCHAR2 := NULL
1067 ,p_supplier_id IN NUMBER := NULL
1068 ,p_spread_curve_id IN NUMBER := NULL
1069 ,p_etc_method_code IN VARCHAR2 := NULL
1070 ,p_mfc_cost_type_id IN NUMBER := NULL
1071 ,p_incurred_by_res_flag IN VARCHAR2 := NULL
1072 ,p_incur_by_res_class_code IN VARCHAR2 := NULL
1073 ,p_incur_by_role_id IN NUMBER := NULL
1074 ,p_unit_of_measure IN VARCHAR2 := NULL
1075 ,p_org_id IN NUMBER := NULL
1076 ,p_rate_based_flag IN VARCHAR2 := NULL
1077 ,p_rate_expenditure_type IN VARCHAR2 := NULL
1078 ,p_rate_func_curr_code IN VARCHAR2 := NULL
1079 ,p_rate_incurred_by_org_id IN NUMBER := NULL
1080 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1081 ) IS
1082
1083 l_task_assignment_id_tbl system.pa_num_tbl_type;
1084 l_task_version_id_tbl system.pa_num_tbl_type := system.pa_num_tbl_type();
1085 l_budget_version_id_tbl system.pa_num_tbl_type := system.pa_num_tbl_type();
1086 l_struct_version_id_tbl system.pa_num_tbl_type := system.pa_num_tbl_type();
1087 l_update_task_asgmt_id_tbl system.pa_num_tbl_type := system.pa_num_tbl_type();
1088 l_last_struct_version_id NUMBER;
1089 l_update_task_version_id_tbl system.pa_num_tbl_type := system.pa_num_tbl_type();
1090
1091 l_project_assignment_id_tbl system.pa_num_tbl_type := system.pa_num_tbl_type();
1092 l_resource_list_member_id_tbl SYSTEM.PA_NUM_TBL_TYPE := system.pa_num_tbl_type();
1093 l_proj_req_res_format_id NUMBER;
1094 l_last_bvid NUMBER;
1095 l_update_count NUMBER;
1096
1097 l_return_status VARCHAR2(1);
1098 l_msg_count NUMBER;
1099 l_msg_data VARCHAR2(2000);
1100 l_overall_return_status VARCHAR2(1);
1101 l_project_id NUMBER;
1102 l_edit_task_ok VARCHAR2(1);
1103 l_msg_count1 NUMBER;
1104 l_msg_count2 NUMBER;
1105
1106 l_resource_class_flag_tbl SYSTEM.PA_VARCHAR2_1_TBL_TYPE := system.pa_varchar2_1_tbl_type();
1107 l_resource_class_code_tbl SYSTEM.PA_VARCHAR2_30_TBL_TYPE := system.pa_varchar2_30_tbl_type();
1108 l_resource_class_id_tbl SYSTEM.PA_NUM_TBL_TYPE := system.pa_num_tbl_type();
1109 l_res_type_code_tbl SYSTEM.PA_VARCHAR2_30_TBL_TYPE := system.pa_varchar2_30_tbl_type();
1110 l_incur_by_res_type_tbl SYSTEM.PA_VARCHAR2_30_TBL_TYPE := system.pa_varchar2_30_tbl_type();
1111 l_person_id_tbl SYSTEM.PA_NUM_TBL_TYPE := system.pa_num_tbl_type();
1112 l_job_id_tbl SYSTEM.PA_NUM_TBL_TYPE := system.pa_num_tbl_type();
1113 l_person_type_code_tbl SYSTEM.PA_VARCHAR2_30_TBL_TYPE := system.pa_varchar2_30_tbl_type();
1114 l_named_role_tbl SYSTEM.PA_VARCHAR2_80_TBL_TYPE := system.pa_varchar2_80_tbl_type();
1115 l_bom_resource_id_tbl SYSTEM.PA_NUM_TBL_TYPE := system.pa_num_tbl_type();
1116 l_non_labor_resource_tbl SYSTEM.PA_VARCHAR2_20_TBL_TYPE := system.pa_varchar2_20_tbl_type();
1117 l_inventory_item_id_tbl SYSTEM.PA_NUM_TBL_TYPE := system.pa_num_tbl_type();
1118 l_item_category_id_tbl SYSTEM.PA_NUM_TBL_TYPE := system.pa_num_tbl_type();
1119 l_project_role_id_tbl SYSTEM.PA_NUM_TBL_TYPE := system.pa_num_tbl_type();
1120 l_organization_id_tbl SYSTEM.PA_NUM_TBL_TYPE := system.pa_num_tbl_type();
1121 l_fc_res_type_code_tbl SYSTEM.PA_VARCHAR2_30_TBL_TYPE := system.pa_varchar2_30_tbl_type();
1122 l_expenditure_type_tbl SYSTEM.PA_VARCHAR2_30_TBL_TYPE := system.pa_varchar2_30_tbl_type();
1123 l_expenditure_category_tbl SYSTEM.PA_VARCHAR2_30_TBL_TYPE := system.pa_varchar2_30_tbl_type();
1124 l_event_type_tbl SYSTEM.PA_VARCHAR2_30_TBL_TYPE := system.pa_varchar2_30_tbl_type();
1125 l_revenue_category_code_tbl SYSTEM.PA_VARCHAR2_30_TBL_TYPE := system.pa_varchar2_30_tbl_type();
1126 l_supplier_id_tbl SYSTEM.PA_NUM_TBL_TYPE := system.pa_num_tbl_type();
1127 l_spread_curve_id_tbl SYSTEM.PA_NUM_TBL_TYPE := system.pa_num_tbl_type();
1128 l_etc_method_code_tbl SYSTEM.PA_VARCHAR2_30_TBL_TYPE := system.pa_varchar2_30_tbl_type();
1129 l_mfc_cost_type_id_tbl SYSTEM.PA_NUM_TBL_TYPE := system.pa_num_tbl_type();
1130 l_incurred_by_res_flag_tbl SYSTEM.PA_VARCHAR2_1_TBL_TYPE := system.pa_varchar2_1_tbl_type();
1131 l_incur_by_res_class_code_tbl SYSTEM.PA_VARCHAR2_30_TBL_TYPE := system.pa_varchar2_30_tbl_type();
1132 l_incur_by_role_id_tbl SYSTEM.PA_NUM_TBL_TYPE := system.pa_num_tbl_type();
1133 l_unit_of_measure_tbl SYSTEM.PA_VARCHAR2_30_TBL_TYPE := system.pa_varchar2_30_tbl_type();
1134 l_org_id_tbl SYSTEM.PA_NUM_TBL_TYPE := system.pa_num_tbl_type();
1135 l_rate_based_flag_tbl SYSTEM.PA_VARCHAR2_1_TBL_TYPE := system.pa_varchar2_1_tbl_type();
1136 l_rate_expenditure_type_tbl SYSTEM.PA_VARCHAR2_30_TBL_TYPE := system.pa_varchar2_30_tbl_type();
1137 l_rate_func_curr_code_tbl SYSTEM.PA_VARCHAR2_30_TBL_TYPE := system.pa_varchar2_30_tbl_type();
1138 l_rate_incurred_by_org_id_tbl SYSTEM.PA_NUM_TBL_TYPE := system.pa_num_tbl_type();
1139
1140 -- Added for Bug 6856934
1141 l_structure_ver_name pa_proj_elem_ver_structure.name%type;
1142
1143 BEGIN
1144 l_overall_return_status := FND_API.G_RET_STS_SUCCESS;
1145
1146 l_task_assignment_id_tbl := p_task_assignment_id_tbl;
1147 l_task_version_id_tbl := p_task_version_id_tbl;
1148 l_budget_version_id_tbl := p_budget_version_id_tbl;
1149 l_struct_version_id_tbl := p_struct_version_id_tbl;
1150 l_update_task_asgmt_id_tbl.delete();
1151
1152 IF l_task_assignment_id_tbl.COUNT <> 0 THEN
1153
1154 IF P_DEBUG_MODE = 'Y' THEN
1155 pa_debug.write(x_module => 'pa.plsql.PA_ASSIGNMENTS_PVT.Update_Task_Assignments'
1156 ,x_msg => 'l_task_assignment_id_tbl.COUNT='||l_task_assignment_id_tbl.COUNT
1157 ,x_log_level => li_message_level);
1158 END IF;
1159
1160 l_last_bvid := l_budget_version_id_tbl(1);
1161 l_last_struct_version_id := l_struct_version_id_tbl(1);
1162 l_update_count := 0;
1163
1164 SELECT project_id
1165 INTO l_project_id
1166 FROM pa_resource_assignments
1167 WHERE resource_assignment_id = l_task_assignment_id_tbl(1);
1168
1169 FOR j IN l_task_assignment_id_tbl.FIRST .. l_task_assignment_id_tbl.LAST + 1 LOOP
1170
1171 IF P_DEBUG_MODE = 'Y' THEN
1172 pa_debug.write(x_module => 'pa.plsql.PA_ASSIGNMENTS_PVT.Update_Task_Assignments'
1173 ,x_msg => 'inside loop'
1174 ,x_log_level => li_message_level);
1175 END IF;
1176
1177 IF j = l_task_assignment_id_tbl.LAST + 1
1178 OR l_budget_version_id_tbl(j) <> l_last_bvid THEN
1179
1180 IF P_DEBUG_MODE = 'Y' THEN
1181 pa_debug.write(x_module => 'pa.plsql.PA_ASSIGNMENTS_PVT.Update_Task_Assignments'
1182 ,x_msg => 'prepare to call'
1183 ,x_log_level => li_message_level);
1184 END IF;
1185
1186 l_project_assignment_id_tbl.extend(l_update_task_asgmt_id_tbl.COUNT);
1187 l_resource_list_member_id_tbl.extend(l_update_task_asgmt_id_tbl.COUNT);
1188 l_resource_class_flag_tbl.extend(l_update_task_asgmt_id_tbl.COUNT);
1189 l_resource_class_code_tbl.extend(l_update_task_asgmt_id_tbl.COUNT);
1190 l_resource_class_id_tbl.extend(l_update_task_asgmt_id_tbl.COUNT);
1191 l_res_type_code_tbl.extend(l_update_task_asgmt_id_tbl.COUNT);
1192 l_incur_by_res_type_tbl.extend(l_update_task_asgmt_id_tbl.COUNT);
1193 l_person_id_tbl.extend(l_update_task_asgmt_id_tbl.COUNT);
1194 l_job_id_tbl.extend(l_update_task_asgmt_id_tbl.COUNT);
1195 l_person_type_code_tbl.extend(l_update_task_asgmt_id_tbl.COUNT);
1196 l_named_role_tbl.extend(l_update_task_asgmt_id_tbl.COUNT);
1197 l_bom_resource_id_tbl.extend(l_update_task_asgmt_id_tbl.COUNT);
1198 l_non_labor_resource_tbl.extend(l_update_task_asgmt_id_tbl.COUNT);
1199 l_inventory_item_id_tbl.extend(l_update_task_asgmt_id_tbl.COUNT);
1200 l_item_category_id_tbl.extend(l_update_task_asgmt_id_tbl.COUNT);
1201 l_project_role_id_tbl.extend(l_update_task_asgmt_id_tbl.COUNT);
1202 l_organization_id_tbl.extend(l_update_task_asgmt_id_tbl.COUNT);
1203 l_fc_res_type_code_tbl.extend(l_update_task_asgmt_id_tbl.COUNT);
1204 l_expenditure_type_tbl.extend(l_update_task_asgmt_id_tbl.COUNT);
1205 l_expenditure_category_tbl.extend(l_update_task_asgmt_id_tbl.COUNT);
1206 l_event_type_tbl.extend(l_update_task_asgmt_id_tbl.COUNT);
1207 l_revenue_category_code_tbl.extend(l_update_task_asgmt_id_tbl.COUNT);
1208 l_supplier_id_tbl.extend(l_update_task_asgmt_id_tbl.COUNT);
1209 l_spread_curve_id_tbl.extend(l_update_task_asgmt_id_tbl.COUNT);
1210 l_etc_method_code_tbl.extend(l_update_task_asgmt_id_tbl.COUNT);
1211 l_mfc_cost_type_id_tbl.extend(l_update_task_asgmt_id_tbl.COUNT);
1212 l_incurred_by_res_flag_tbl.extend(l_update_task_asgmt_id_tbl.COUNT);
1213 l_incur_by_res_class_code_tbl.extend(l_update_task_asgmt_id_tbl.COUNT);
1214 l_incur_by_role_id_tbl.extend(l_update_task_asgmt_id_tbl.COUNT);
1215 l_unit_of_measure_tbl.extend(l_update_task_asgmt_id_tbl.COUNT);
1216 l_org_id_tbl.extend(l_update_task_asgmt_id_tbl.COUNT);
1217 l_rate_based_flag_tbl.extend(l_update_task_asgmt_id_tbl.COUNT);
1218 l_rate_expenditure_type_tbl.extend(l_update_task_asgmt_id_tbl.COUNT);
1219 l_rate_func_curr_code_tbl.extend(l_update_task_asgmt_id_tbl.COUNT);
1220 l_rate_incurred_by_org_id_tbl.extend(l_update_task_asgmt_id_tbl.COUNT);
1221
1222 IF P_DEBUG_MODE = 'Y' THEN
1223 pa_debug.write(x_module => 'pa.plsql.PA_ASSIGNMENTS_PVT.Update_Task_Assignments'
1224 ,x_msg => 'struct_version_id='||l_last_struct_version_id||
1225 ' bvid='||l_last_bvid
1226 ,x_log_level => li_message_level);
1227 END IF;
1228
1229 FOR i IN l_update_task_asgmt_id_tbl.FIRST .. l_update_task_asgmt_id_tbl.LAST LOOP
1230
1231 l_project_assignment_id_tbl(i) := p_project_assignment_id;
1232 l_resource_list_member_id_tbl(i) := p_resource_list_member_id;
1233 l_resource_class_flag_tbl(i) := p_resource_class_flag;
1234 l_resource_class_code_tbl(i) := p_resource_class_code;
1235 l_resource_class_id_tbl(i) := p_resource_class_id;
1236 l_res_type_code_tbl(i) := p_res_type_code;
1237 l_incur_by_res_type_tbl(i) := p_incur_by_res_type;
1238 l_person_id_tbl(i) := p_person_id;
1239 l_job_id_tbl(i) := p_job_id;
1240 l_person_type_code_tbl(i) := p_person_type_code;
1241 l_named_role_tbl(i) := p_named_role;
1242 l_bom_resource_id_tbl(i) := p_bom_resource_id;
1243 l_non_labor_resource_tbl(i) := p_non_labor_resource;
1244 l_inventory_item_id_tbl(i) := p_inventory_item_id;
1245 l_item_category_id_tbl(i) := p_item_category_id;
1246 l_project_role_id_tbl(i) := p_project_role_id;
1247 l_organization_id_tbl(i) := p_organization_id;
1248 l_fc_res_type_code_tbl(i) := p_fc_res_type_code;
1249 l_expenditure_type_tbl(i) := p_expenditure_type;
1250 l_expenditure_category_tbl(i) := p_expenditure_category;
1251 l_event_type_tbl(i) := p_event_type;
1252 l_revenue_category_code_tbl(i) := p_revenue_category_code;
1253 l_supplier_id_tbl(i) := p_supplier_id;
1254 l_spread_curve_id_tbl(i) := p_spread_curve_id;
1255 l_etc_method_code_tbl(i) := p_etc_method_code;
1256 l_mfc_cost_type_id_tbl(i) := p_mfc_cost_type_id;
1257 l_incurred_by_res_flag_tbl(i) := p_incurred_by_res_flag;
1258 l_incur_by_res_class_code_tbl(i) := p_incur_by_res_class_code;
1259 l_incur_by_role_id_tbl(i) := p_incur_by_role_id;
1260 l_unit_of_measure_tbl(i) := p_unit_of_measure;
1261 l_org_id_tbl(i) := p_org_id;
1262 l_rate_based_flag_tbl(i) := p_rate_based_flag;
1263 l_rate_expenditure_type_tbl(i) := p_rate_expenditure_type;
1264 l_rate_func_curr_code_tbl(i) := p_rate_func_curr_code;
1265 l_rate_incurred_by_org_id_tbl(i) := p_rate_incurred_by_org_id;
1266
1267 IF P_DEBUG_MODE = 'Y' THEN
1268 pa_debug.write(x_module => 'pa.plsql.PA_ASSIGNMENTS_PVT.Update_Task_Assignments'
1269 ,x_msg => 'i='||i||'version id='||l_update_task_version_id_tbl(i)||
1270 ' task_id='||l_update_task_asgmt_id_tbl(i)||
1271 ' asgmt_id='||l_project_assignment_id_tbl(i)||
1272 ' rlm='||l_resource_list_member_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||'res_class_flag='||l_resource_class_flag_tbl(i)||
1277 ' res_class_code='||l_resource_class_code_tbl(i)||
1278 ' res_class_id='||l_resource_class_id_tbl(i)||
1279 ' res_type_code='||l_res_type_code_tbl(i)||
1280 ' person_id='||l_person_id_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||'job_id='||l_job_id_tbl(i)||
1285 ' person_type_code='||l_person_type_code_tbl(i)||
1286 ' named_role='||l_named_role_tbl(i)||
1287 ' bom_res_id='||l_bom_resource_id_tbl(i)||
1288 ' non_labor_res='||l_non_labor_resource_tbl(i)
1289 ,x_log_level => li_message_level);
1290
1291 pa_debug.write(x_module => 'pa.plsql.PA_ASSIGNMENTS_PVT.Update_Task_Assignments'
1292 ,x_msg => 'i='||i||'inven_item='||l_inventory_item_id_tbl(i)||
1293 ' item_cat_id='||l_item_category_id_tbl(i)||
1294 ' proj_role_id='||l_project_role_id_tbl(i)||
1295 ' org_id='||l_organization_id_tbl(i)||
1296 ' fc_res_type='||l_fc_res_type_code_tbl(i)||
1297 ' exp_type='||l_expenditure_type_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||'exp_cat='||l_expenditure_category_tbl(i)||
1302 ' event_type='||l_event_type_tbl(i)||
1303 ' rev_cat_code='||l_revenue_category_code_tbl(i)||
1304 ' supplier_id='||l_supplier_id_tbl(i)||
1305 ' spread_curve='||l_spread_curve_id_tbl(i)
1306 ,x_log_level => li_message_level);
1307
1308 pa_debug.write(x_module => 'pa.plsql.PA_ASSIGNMENTS_PVT.Update_Task_Assignments'
1309 ,x_msg => 'i='||i||'etc_method_code='||l_etc_method_code_tbl(i)||
1310 ' mfc_cost_type='||l_mfc_cost_type_id_tbl(i)||
1311 ' inc_by_res_flag='||l_incurred_by_res_flag_tbl(i)||
1312 ' inc_by_res_class='||l_incur_by_res_class_code_tbl(i)||
1313 ' inc_by_role_id='||l_incur_by_role_id_tbl(i)||
1314 ' unit_of_measure'||l_unit_of_measure_tbl(i)
1315 ,x_log_level => li_message_level);
1316 END IF;
1317 END LOOP;
1318
1319 -- Bug 4059887: Check privilege of update task assignments
1320 /* commented and changed as below for Bug 6856934
1321 l_edit_task_ok := pa_task_assignment_utils.check_edit_task_ok(
1322 P_PROJECT_ID => l_project_id
1323 ,P_STRUCTURE_VERSION_ID => l_last_struct_version_id
1324 ,P_CURR_STRUCT_VERSION_ID => l_last_struct_version_id);
1325 */
1326 l_edit_task_ok := PA_PROJECT_STRUCTURE_UTILS.IS_STRUC_VER_LOCKED_BY_USER(
1327 p_user_id => FND_GLOBAL.USER_ID,
1328 p_structure_version_id => l_last_struct_version_id);
1329
1330 IF P_DEBUG_MODE = 'Y' THEN
1331 pa_debug.write(x_module => 'pa.plsql.PA_ASSIGNMENTS_PVT.Update_Task_Assignments'
1332 ,x_msg => 'l_edit_task_ok='||l_edit_task_ok
1333 ,x_log_level => li_message_level);
1334 END IF;
1335
1336 -- IF 'Y' = l_edit_task_ok THEN Commented and changed as below for Bug 6856934
1337 IF nvl(l_edit_task_ok,'X') <> 'O' THEN
1338
1339 l_msg_count1 := FND_MSG_PUB.Count_Msg;
1340 pa_fp_planning_transaction_pub.update_planning_transactions (
1341 p_context => 'TASK_ASSIGNMENT'
1342 ,p_calling_context => 'PA_PROJECT_ASSIGNMENT' -- Added for Bug 6856934
1343 ,p_struct_elem_version_id => l_last_struct_version_id
1344 ,p_budget_version_id => l_last_bvid
1345 ,p_task_elem_version_id_tbl => l_update_task_version_id_tbl
1346 ,p_resource_assignment_id_tbl => l_update_task_asgmt_id_tbl
1347 ,p_project_assignment_id_tbl => l_project_assignment_id_tbl
1348 -- resource_list_member_id
1349 ,p_resource_list_member_id_tbl => l_resource_list_member_id_tbl
1350 -- pass in all resource attributes
1351 ,p_resource_class_flag_tbl => l_resource_class_flag_tbl
1352 ,p_resource_class_code_tbl => l_resource_class_code_tbl
1353 ,p_resource_class_id_tbl => l_resource_class_id_tbl
1354 ,p_res_type_code_tbl => l_res_type_code_tbl
1355 -- ,p_incur_by_res_type_tbl => l_incur_by_res_type_tbl
1356 ,p_person_id_tbl => l_person_id_tbl
1357 ,p_job_id_tbl => l_job_id_tbl
1358 ,p_person_type_code => l_person_type_code_tbl
1359 ,p_named_role_tbl => l_named_role_tbl -- named_role
1360 ,p_bom_resource_id_tbl => l_bom_resource_id_tbl
1361 ,p_non_labor_resource_tbl => l_non_labor_resource_tbl
1362 ,p_inventory_item_id_tbl => l_inventory_item_id_tbl
1363 ,p_item_category_id_tbl => l_item_category_id_tbl
1364 --Bug 4170933 ,p_project_role_id_tbl => l_project_role_id_tbl
1365 ,p_organization_id_tbl => l_organization_id_tbl
1366 ,p_fc_res_type_code_tbl => l_fc_res_type_code_tbl
1367 ,p_expenditure_type_tbl => l_expenditure_type_tbl
1368 ,p_expenditure_category_tbl => l_expenditure_category_tbl
1369 ,p_event_type_tbl => l_event_type_tbl
1370 ,p_revenue_category_code_tbl => l_revenue_category_code_tbl
1371 ,p_supplier_id_tbl => l_supplier_id_tbl
1372 ,p_spread_curve_id_tbl => l_spread_curve_id_tbl
1373 ,p_etc_method_code_tbl => l_etc_method_code_tbl
1374 ,p_mfc_cost_type_id_tbl => l_mfc_cost_type_id_tbl
1375 ,p_incurred_by_res_flag_tbl => l_incurred_by_res_flag_tbl
1376 ,p_incur_by_res_class_code_tbl => l_incur_by_res_class_code_tbl
1377 ,p_incur_by_role_id_tbl => l_incur_by_role_id_tbl
1378 ,p_unit_of_measure_tbl => l_unit_of_measure_tbl
1379 -- ,p_org_id_tbl => l_org_id_tbl
1380 -- ,p_rate_based_flag_tbl => l_rate_based_flag_tbl
1381 -- ,p_rate_expenditure_type_tbl => l_rate_expenditure_type_tbl
1382 -- ,p_rate_func_curr_code_tbl => l_rate_func_curr_code_tbl
1383 -- ,p_rate_incurred_by_org_id_tbl => l_rate_incurred_by_org_id_tbl
1384 ,x_return_status => l_return_status
1385 ,x_msg_data => l_msg_data
1386 ,x_msg_count => l_msg_count);
1387
1388 IF P_DEBUG_MODE = 'Y' THEN
1389 pa_debug.write(x_module => 'pa.plsql.PA_ASSIGNMENTS_PVT.Update_Task_Assignments'
1390 ,x_msg => 'return status'||l_return_status
1391 ,x_log_level => li_message_level);
1392 END IF;
1393
1394 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1395 l_overall_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1396
1397 -- bug 4117269: Remove the error messages added by the above API
1398 l_msg_count2 := FND_MSG_PUB.Count_Msg;
1399 IF P_DEBUG_MODE = 'Y' THEN
1400 pa_debug.write(x_module => 'pa.plsql.PA_ASSIGNMENTS_PVT.Update_Task_Assignments'
1401 ,x_msg => ' count1='||l_msg_count1||
1402 ' count2='||l_msg_count2
1403 ,x_log_level => li_message_level);
1404 END IF;
1405
1406 FOR k IN (l_msg_count1 + 1)..l_msg_count2 LOOP
1407 IF P_DEBUG_MODE = 'Y' THEN
1408 pa_debug.write(x_module => 'pa.plsql.PA_ASSIGNMENTS_PVT.Update_Task_Assignments'
1409 ,x_msg => 'Deleting message at index: ' || FND_MSG_PUB.Count_Msg
1410 ,x_log_level => li_message_level);
1411 END IF;
1412 FND_MSG_PUB.delete_msg(p_msg_index => FND_MSG_PUB.Count_Msg);
1413 END LOOP;
1414
1415 PA_UTILS.Add_Message( p_app_short_name => 'PA'
1416 ,p_msg_name => 'PA_NO_UPDATE_ASGN_WITH_ACTUALS' );
1417 PA_ASSIGNMENTS_PUB.g_error_exists := FND_API.G_TRUE;
1418
1419 END IF;
1420
1421 ELSE -- If nvl(l_edit_task_ok,'X') <> 'O'
1422
1423 IF p_mode <> 'CREATE' THEN
1424 -- Bug 6856934
1425 Begin
1426 select ppevs.name into l_structure_ver_name
1427 from pa_proj_elem_ver_structure ppevs
1428 where ppevs.project_id = l_project_id
1429 and ppevs.element_version_id = l_last_struct_version_id;
1430 Exception when NO_DATA_FOUND then
1431 null;
1432 end;
1433 PA_UTILS.Add_Message(
1434 p_app_short_name => 'PA'
1435 ,p_msg_name => 'PA_WORKPLAN_LOCKED_NO_UPD'
1436 ,p_token1 => 'TEAM_ROLE'
1437 ,p_value1 => p_named_role
1438 ,p_token2 => 'WP_VERSION_NAME'
1439 ,p_value2 => l_structure_ver_name);
1440 l_overall_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1441 END IF;
1442
1443 END IF; -- IF 'Y' = l_edit_task_ok THEN
1444
1445 l_update_count := 0;
1446 l_update_task_version_id_tbl.delete();
1447 l_update_task_asgmt_id_tbl.delete();
1448 l_project_assignment_id_tbl.delete();
1449 l_resource_list_member_id_tbl.delete();
1450 l_resource_class_flag_tbl.delete();
1451 l_resource_class_code_tbl.delete();
1452 l_resource_class_id_tbl.delete();
1453 l_res_type_code_tbl.delete();
1454 l_incur_by_res_type_tbl.delete();
1455 l_person_id_tbl.delete();
1456 l_job_id_tbl.delete();
1457 l_person_type_code_tbl.delete();
1458 l_named_role_tbl.delete();
1459 l_bom_resource_id_tbl.delete();
1460 l_non_labor_resource_tbl.delete();
1461 l_inventory_item_id_tbl.delete();
1462 l_item_category_id_tbl.delete();
1463 l_project_role_id_tbl.delete();
1464 l_organization_id_tbl.delete();
1465 l_fc_res_type_code_tbl.delete();
1466 l_expenditure_type_tbl.delete();
1467 l_expenditure_category_tbl.delete();
1468 l_event_type_tbl.delete();
1469 l_revenue_category_code_tbl.delete();
1470 l_supplier_id_tbl.delete();
1471 l_spread_curve_id_tbl.delete();
1472 l_etc_method_code_tbl.delete();
1473 l_mfc_cost_type_id_tbl.delete();
1474 l_incurred_by_res_flag_tbl.delete();
1475 l_incur_by_res_class_code_tbl.delete();
1476 l_incur_by_role_id_tbl.delete();
1477 l_unit_of_measure_tbl.delete();
1478 l_org_id_tbl.delete();
1479 l_rate_based_flag_tbl.delete();
1480 l_rate_expenditure_type_tbl.delete();
1481 l_rate_func_curr_code_tbl.delete();
1482 l_rate_incurred_by_org_id_tbl.delete();
1483
1484 END IF; -- budget_version_id different
1485
1486 IF j <> l_task_assignment_id_tbl.LAST + 1 THEN
1487 IF P_DEBUG_MODE = 'Y' THEN
1488 pa_debug.write(x_module => 'pa.plsql.PA_ASSIGNMENTS_PVT.Update_Task__Assignments'
1489 ,x_msg => 'j <> l_task_assignment_id_tbl.LAST + 1'
1490 ,x_log_level => li_message_level);
1491 END IF;
1492
1493 l_update_count := l_update_count + 1;
1494 l_update_task_asgmt_id_tbl.extend(1);
1495 l_update_task_version_id_tbl.extend(1);
1496 l_update_task_asgmt_id_tbl(l_update_count) := l_task_assignment_id_tbl(j);
1497 l_update_task_version_id_tbl(l_update_count) := l_task_version_id_tbl(j);
1498 l_last_bvid := l_budget_version_id_tbl(j);
1499 l_last_struct_version_id := l_struct_version_id_tbl(j);
1500 END IF;
1501
1502 END LOOP;
1503
1504 END IF; -- l_task_assignment_id_tbl.COUNT <> 0 THEN
1505
1506 x_return_status := l_overall_return_status;
1507
1508 -- Reset the error stack when returning to the calling program
1509 PA_DEBUG.Reset_err_stack;
1510
1511 EXCEPTION
1512 WHEN OTHERS THEN
1513
1514 -- Set the excetption Message and the stack
1515 FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_ASSIGNMENTS_PVT.Update_Task_Assignments'
1516 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
1517 --
1518 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1519 RAISE; -- This is optional depending on the needs
1520
1521
1522 END Update_Task_Assignments;
1523
1524
1525
1526 END pa_assignments_pvt;