[Home] [Help]
PACKAGE BODY: APPS.PA_STAFFED_ASSIGNMENT_PVT
Source
1 PACKAGE BODY pa_staffed_assignment_pvt AS
2 /*$Header: PARDPVTB.pls 120.8.12010000.3 2008/11/28 06:18:30 kjai 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_Staffed_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_location_city IN pa_locations.city%TYPE := FND_API.G_MISS_CHAR
13 ,p_location_region IN pa_locations.region%TYPE := FND_API.G_MISS_CHAR
14 ,p_location_country_code IN pa_locations.country_code%TYPE := FND_API.G_MISS_CHAR
15 ,p_sum_tasks_flag IN VARCHAR2 := FND_API.G_FALSE -- FP.M Development
16 ,p_budget_version_id IN pa_resource_assignments.budget_version_id%TYPE := FND_API.G_MISS_NUM
17 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
18 ,p_validate_only IN VARCHAR2 := FND_API.G_TRUE
19 ,x_new_assignment_id OUT NOCOPY pa_project_assignments.assignment_id%TYPE --File.Sql.39 bug 4440895
20 ,x_assignment_row_id OUT NOCOPY ROWID --File.Sql.39 bug 4440895
21 ,x_resource_id OUT NOCOPY pa_resources.resource_id%TYPE --File.Sql.39 bug 4440895
22 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
23 )
24 IS
25
26 l_assignment_rec PA_ASSIGNMENTS_PUB.Assignment_Rec_Type;
27 l_source_assignment_rec PA_ASSIGNMENTS_PUB.Assignment_Rec_Type;
28 l_schedule_basis_flag VARCHAR2(1);
29 l_msg_count NUMBER;
30 l_msg_data VARCHAR2(2000);
31 l_new_open_asgn_count NUMBER := 0;
32 l_new_open_asgn_id pa_project_assignments.assignment_id%TYPE;
33 l_new_open_asgn_number pa_project_assignments.assignment_number%TYPE;
34 l_new_open_asgn_row_id ROWID;
35 l_return_status VARCHAR2(1);
36 l_ret_code VARCHAR2(1);
37 l_system_status_code pa_project_statuses.project_system_status_code%TYPE;
38 l_cc_ok VARCHAR2(1);
39 l_error_message_code VARCHAR2(2000);
40 l_wf_type VARCHAR2(80);
41 l_wf_item_type VARCHAR2(2000);
42 l_wf_process VARCHAR2(2000);
43 l_pp_assignment_id NUMBER;
44 l_resource_source_id NUMBER;
45 l_project_manager_person_id NUMBER ;
46 l_project_manager_name VARCHAR2(200);
47 l_project_party_id NUMBER ;
48 l_project_role_id NUMBER ;
49 l_project_role_name VARCHAR2(80);
50 l_project_status_code pa_project_statuses.project_status_code%TYPE;
51 l_project_status_name pa_project_statuses.project_status_name%TYPE;
52 l_assignment_status_name pa_project_statuses.project_status_name%TYPE;
53 l_asgn_text VARCHAR2(30);
54 l_allow_asgmt VARCHAR2(1);
55 l_work_type_id NUMBER;
56 l_raw_revenue NUMBER;
57 l_project_parties_error_exists VARCHAR2(1) := FND_API.G_FALSE;
58 l_check_resource VARCHAR2(1);
59 l_proj_asgmt_res_format_id NUMBER;
60 l_person_id NUMBER;
61 l_task_assignment_id_tbl system.pa_num_tbl_type := system.pa_num_tbl_type();
62 l_project_assignment_id_tbl system.pa_num_tbl_type := system.pa_num_tbl_type();
63 l_named_role_tbl system.PA_VARCHAR2_80_TBL_TYPE := system.PA_VARCHAR2_80_TBL_TYPE();
64 l_proj_req_res_format_id NUMBER;
65 l_budget_version_id_tbl system.pa_num_tbl_type := system.pa_num_tbl_type();
66 l_struct_version_id_tbl system.pa_num_tbl_type := system.pa_num_tbl_type();
67 l_last_bvid NUMBER;
68 l_update_task_asgmt_id_tbl system.pa_num_tbl_type := system.pa_num_tbl_type();
69 l_update_count NUMBER;
70 l_last_struct_version_id NUMBER;
71 l_task_version_id_tbl system.pa_num_tbl_type := system.pa_num_tbl_type();
72 l_update_task_version_id_tbl system.pa_num_tbl_type := system.pa_num_tbl_type();
73 l_resource_organization_id NUMBER;
74
75 l_person_id_tmp NUMBER;
76 l_fcst_job_id_tmp pa_project_assignments.fcst_job_id%TYPE;
77 l_expenditure_type_tmp pa_project_assignments.expenditure_type%TYPE;
78 l_project_role_id_tmp pa_project_assignments.project_role_id%TYPE;
79 l_assignment_name_tmp pa_project_assignments.assignment_name%TYPE;
80 l_person_type_code Varchar2(30);
81 l_future_dated_emp Varchar2(1);
82 l_future_term_wf_flag pa_resources.future_term_wf_flag%TYPE := NULL; -- Added for Bug 6056112
83
84 CURSOR get_resource_source_id IS
85 SELECT person_id
86 FROM pa_resource_txn_attributes
87 WHERE resource_id = l_assignment_rec.resource_id;
88
89 /* Created for bug 2381199 */
90 CURSOR get_system_status IS
91 SELECT project_system_status_code
92 FROM PA_PROJECT_STATUSES
93 WHERE project_status_code = l_assignment_rec.status_code;
94 /* Created for bug 2381199 */
95
96 -- Bottom Up Flow
97 CURSOR get_bu_resource_assignments IS
98 SELECT ra.resource_assignment_id, ra.wbs_element_version_id, bv.budget_version_id, bv.project_structure_version_id
99 FROM PA_RESOURCE_ASSIGNMENTS ra
100 ,PA_BUDGET_VERSIONS bv
101 ,PA_PROJ_ELEM_VER_STRUCTURE evs
102 WHERE ra.project_id = bv.project_id
103 AND bv.project_id = evs.project_id
104 AND bv.budget_type_code IS NULL -- added for bug 7492618
105 AND ra.budget_version_id = bv.budget_version_id
106 AND bv.project_structure_version_id = evs.element_version_id
107 AND ra.project_id = l_assignment_rec.project_id
108 AND ra.resource_list_member_id = l_assignment_rec.resource_list_member_id
109 AND ra.project_assignment_id = -1
110 -- AND evs.latest_eff_published_flag = 'N'
111 AND ra.budget_version_id = p_budget_version_id;
112 --ORDER BY bv.budget_version_id, bv.project_structure_version_id;
113
114 -- Top-Down Flow
115 CURSOR get_td_resource_assignments IS
116 SELECT resource_assignment_id, wbs_element_version_id, budget_version_id, project_structure_version_id
117 FROM
118 (
119 (SELECT ra.resource_assignment_id, ra.wbs_element_version_id, bv.budget_version_id, bv.project_structure_version_id
120 FROM PA_RESOURCE_ASSIGNMENTS ra
121 ,PA_BUDGET_VERSIONS bv
122 ,PA_PROJ_ELEM_VER_STRUCTURE evs
123 WHERE ra.project_id = bv.project_id
124 AND bv.project_id = evs.project_id
125 AND bv.budget_type_code IS NULL -- added for bug 7492618
126 AND ra.budget_version_id = bv.budget_version_id
127 AND bv.project_structure_version_id = evs.element_version_id
128 AND ra.project_id = l_assignment_rec.project_id
129 AND ra.resource_list_member_id = l_assignment_rec.resource_list_member_id
130 AND ra.project_assignment_id = -1
131 AND evs.status_code = 'STRUCTURE_WORKING')
132 UNION ALL
133 (SELECT ra.resource_assignment_id, ra.wbs_element_version_id, bv.budget_version_id, bv.project_structure_version_id
134 FROM PA_RESOURCE_ASSIGNMENTS ra
135 ,PA_BUDGET_VERSIONS bv
136 ,PA_PROJ_ELEM_VER_STRUCTURE evs
137 ,PA_PROJ_WORKPLAN_ATTR pwa
138 WHERE pwa.wp_enable_Version_flag = 'N'
139 AND pwa.project_id = ra.project_id
140 AND pwa.proj_element_id = evs.proj_element_id
141 AND ra.project_id = bv.project_id
142 AND bv.project_id = evs.project_id
143 AND bv.budget_type_code IS NULL -- added for bug 7492618
144 AND ra.budget_version_id = bv.budget_version_id
145 AND bv.project_structure_version_id = evs.element_version_id
146 AND ra.resource_list_member_id = l_assignment_rec.resource_list_member_id
147 AND ra.project_id = l_assignment_rec.project_id
148 AND ra.project_assignment_id = -1)
149 )
150 ORDER BY budget_version_id, project_structure_version_id;
151
152 -- get the resource's organization in HR
153 CURSOR get_resource_organization IS
154 SELECT resource_organization_id
155 FROM pa_resources_denorm
156 WHERE l_assignment_rec.start_date BETWEEN resource_effective_start_date AND resource_effective_end_date
157 AND resource_id = l_assignment_rec.resource_id;
158
159 CURSOR check_future_dated_employee IS
160 SELECT 'X'
161 FROM per_all_people_f
162 WHERE person_id = l_person_id_tmp
163 AND effective_start_date <= sysdate
164 AND rownum = 1;
165
166 -- 5130421 : Added the following variables
167 l_expenditure_type_tmp1 pa_project_assignments.expenditure_type%TYPE;
168 l_expenditure_type_class_tmp pa_project_assignments.expenditure_type_class%TYPE;
169 l_fcst_tp_amount_type_tmp pa_project_assignments.fcst_tp_amount_type%TYPE;
170 l_apprvl_status_code PA_PROJECT_ASSIGNMENTS.APPRVL_STATUS_CODE%TYPE;
171 -- 5130421 : End
172
173 BEGIN
174
175 -- 4537865 : Initialize the return_status
176 x_return_status := FND_API.G_RET_STS_SUCCESS ;
177
178 -- Initialize the Error Stack
179 PA_DEBUG.set_err_stack('PA_STAFFED_ASSIGNMENT_PVT.Create_Staffed_Assignment');
180 --dbms_output.put_line('create staffed assignment');
181
182 --Log Message
183 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
184 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_STAFFED_ASSIGNMENT_PVT.Create_Staffed_Assignment.begin'
185 ,x_msg => 'Beginning of Create_Staff_Assignment'
186 ,x_log_level => 5);
187 END IF;
188 --
189 -- Assign the record to the local variable
190 --
191 l_assignment_rec := p_assignment_rec;
192
193
194 --
195 --Get assignment text from message to be used as values for token
196 --
197 l_asgn_text := FND_MESSAGE.GET_STRING('PA','PA_ASSIGNMENT_TEXT');
198
199
200 --
201 -- Check that mandatory inputs for Staffed Assignment record are not null
202 --
203 --
204 -- Check that mandatory project id exists
205 --
206 IF (l_assignment_rec.project_id IS NULL OR l_assignment_rec.project_id = FND_API.G_MISS_NUM) THEN
207 PA_UTILS.Add_Message( p_app_short_name => 'PA'
208 ,p_msg_name => 'PA_PROJ_ID_REQUIRED_FOR_ASGN'
209 ,p_token1 => 'ASGNTYPE'
210 ,p_value1 => l_asgn_text);
211 PA_ASSIGNMENTS_PUB.g_error_exists := FND_API.G_TRUE;
212 RETURN;
213 END IF;
214
215 --
216 -- Check that mandatory assignment name exists
217 --
218 IF l_assignment_rec.assignment_name IS NULL OR
219 l_assignment_rec.assignment_name = FND_API.G_MISS_CHAR THEN
220 PA_UTILS.Add_Message( p_app_short_name => 'PA'
221 ,p_msg_name => 'PA_NAME_REQUIRED_FOR_ASGN');
222 PA_ASSIGNMENTS_PUB.g_error_exists := FND_API.G_TRUE;
223 END IF;
224
225 --
226 -- Check that mandatory project role exists
227 --
228 IF l_assignment_rec.project_role_id IS NULL
229 OR l_assignment_rec.project_role_id = FND_API.G_MISS_NUM THEN
230 PA_UTILS.Add_Message( p_app_short_name => 'PA'
231 ,p_msg_name => 'PA_PROJ_ROLE_REQUIRED_FOR_ASGN'
232 ,p_token1 => 'ASGNTYPE'
233 ,p_value1 => l_asgn_text);
234 PA_ASSIGNMENTS_PUB.g_error_exists := FND_API.G_TRUE;
235 END IF;
236
237 --
238 --Check p_work_type_id IS NOT NULL
239 --
240 IF l_assignment_rec.work_type_id IS NULL
241 OR l_assignment_rec.work_type_id = FND_API.G_MISS_NUM
242 OR l_assignment_rec.work_type_id = 0 THEN
243
244 PA_UTILS.Add_Message( p_app_short_name => 'PA'
245 ,p_msg_name => 'PA_WORK_TYPE_REQUIRED_FOR_ASGN' );
246 PA_ASSIGNMENTS_PUB.g_error_exists := FND_API.G_TRUE;
247
248 ELSE
249 --
250 --check for indirect project, only non-billable work types
251 --
252 PA_WORK_TYPE_UTILS.CHECK_WORK_TYPE (
253 P_WORK_TYPE_ID => l_assignment_rec.work_type_id
254 ,P_PROJECT_ID => l_assignment_rec.project_id
255 ,P_TASK_ID => NULL
256 ,X_RETURN_STATUS => l_return_status
257 ,X_ERROR_MESSAGE_CODE => l_error_message_code);
258 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
259 PA_UTILS.Add_Message( p_app_short_name => 'PA'
260 ,p_msg_name => l_error_message_code );
261 PA_ASSIGNMENTS_PUB.g_error_exists := FND_API.G_TRUE;
262 END IF;
263
264 END IF;
265
266 --
267 -- Check valid starting status
268 --
269 IF (PA_PROJECT_STUS_UTILS.Is_Starting_Status( x_project_status_code => p_assignment_rec.status_code)) = 'N' THEN
270 PA_UTILS.Add_Message( p_app_short_name => 'PA'
271 ,p_msg_name => 'PA_INVALID_ASGN_STARTING_STUS'
272 ,p_token1 => 'ASGNTYPE'
273 ,p_value1 => l_asgn_text);
274 PA_ASSIGNMENTS_PUB.g_error_exists := FND_API.G_TRUE;
275 END IF;
276
277 -- Check Action allowed status (check that it is allowed to have a staffed assignment for a project or open assignment)
278
279 -- IF an staffed assignment for a project
280 IF p_assignment_rec.project_id is NOT NULL THEN
281
282 l_allow_asgmt := PA_ASSIGNMENT_UTILS.is_asgmt_allow_stus_ctl_check
283 (p_asgmt_status_code => l_assignment_rec.status_code,
284 p_project_id => l_assignment_rec.project_id,
285 p_add_message => 'Y');
286
287 IF l_allow_asgmt = 'N' THEN
288 PA_ASSIGNMENTS_PUB.g_error_exists := FND_API.G_TRUE;
289 END IF; --l_allow_asgmt := 'N'
290
291 END IF; --p_assignment_rec.project_id is NOT NULL
292
293 --LOCATION
294
295 -- Rules:
296 -- If all the location input parameters are null then default location from project
297 -- If location details are passed then get the location id for the for the given location parameters
298 -- If the location does not already exist then create it
299 --
300 IF (l_assignment_rec.location_id IS NULL OR l_assignment_rec.location_id = FND_API.G_MISS_NUM)
301 AND (p_location_city IS NULL OR p_location_city = FND_API.G_MISS_CHAR)
302 AND (p_location_region IS NULL OR p_location_region = FND_API.G_MISS_CHAR)
303 AND (p_location_country_code IS NULL OR p_location_country_code = FND_API.G_MISS_CHAR)
304 THEN
305
306 --bug 1795160: no need to get location from project, location not required.
307 /*
308 SELECT location_id
309 INTO l_assignment_rec.location_id
310 FROM pa_projects_all
311 WHERE project_id = l_assignment_rec.project_id;
312 */
313 --Log Message
314 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
315 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_STAFFED_ASSIGNMENT_PVT.Create_Staffed_Assignment.location'
316 ,x_msg => 'No need to get location from project'
317 ,x_log_level => 5);
318 END IF;
319
320
321 --only call get_location if location_id IS NULL
322 ELSIF l_assignment_rec.location_id IS NULL OR l_assignment_rec.location_id = FND_API.G_MISS_NUM THEN
323
324 PA_LOCATION_UTILS.Get_Location( p_city => p_location_city
325 ,p_region => p_location_region
326 ,p_country_code => p_location_country_code
327 ,x_location_id => l_assignment_rec.location_id
328 ,x_error_message_code => l_error_message_code
329 ,x_return_status => l_return_status );
330
331 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
332 PA_UTILS.Add_Message ( p_app_short_name => 'PA'
333 ,p_msg_name => l_error_message_code );
334 PA_ASSIGNMENTS_PUB.g_error_exists := FND_API.G_TRUE;
335 END IF;
336
337 END IF;
338
339 --
340 -- Resource name to id is already done in the public API
341 --
342 -- Get the resource_id and project_parties_id for a given resource_source_id
343 -- Schedule_flag default value is 'N'. The value will be'Y' when
344 -- an assignment exists for that person and role combination.
345 --
346
347 --dbms_output.put_line(l_assignment_rec.project_id);
348 --dbms_output.put_line(l_assignment_rec.project_role_id);
349 --dbms_output.put_line('resource source id' ||p_resource_source_id);
350 --dbms_output.put_line(l_assignment_rec.start_date);
351 --dbms_output.put_line(l_assignment_rec.end_date);
352 --dbms_output.put_line('project_party_id' || l_assignment_rec.project_party_id);
353
354 --dbms_output.put_line('resource source id is '||p_resource_source_id);
355
356
357
358 l_resource_source_id := p_resource_source_id;
359
360 --Additional Check: If Project Id is not present, no need to run the following program that depends on it.
361 IF l_assignment_rec.project_id IS NOT NULL AND
362 l_assignment_rec.project_id <> FND_API.G_MISS_NUM THEN
363
364 --If resource id exist, get the resource_source_id, and use it to create project party
365 IF (l_assignment_rec.resource_id IS NOT NULL AND l_assignment_rec.resource_id <> FND_API.G_MISS_NUM) AND
366 (l_resource_source_id IS NULL OR l_resource_source_id =FND_API.G_MISS_NUM) THEN
367
368 --Log Message
369 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
370 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_STAFFED_ASSIGNMENT_PVT.Create_Staffed_Assignment.res_source_id'
371 ,x_msg => 'Getting Resource Source Id'
372 ,x_log_level => 5);
373 END IF;
374 OPEN get_resource_source_id;
375 FETCH get_resource_source_id INTO l_resource_source_id;
376 CLOSE get_resource_source_id;
377 END IF;
378
379
380 IF (l_assignment_rec.project_party_id = FND_API.G_MISS_NUM OR l_assignment_rec.project_party_id IS NULL) AND
381 (l_assignment_rec.project_role_id IS NOT NULL AND l_assignment_rec.project_role_id <> FND_API.G_MISS_NUM) AND
382 (l_resource_source_id IS NOT NULL AND l_resource_source_id <>FND_API.G_MISS_NUM) THEN
383
384 --dbms_output.put_line('*****calling create_project_party');
385 --dbms_output.put_line('*****resource_source_id:'||l_resource_source_id);
386 --dbms_output.put_line('*****resource_id:'||l_assignment_rec.resource_id);
387
388 --Log Message
389 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
390 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_STAFFED_ASSIGNMENT_PVT.Create_Staffed_Assignment.project_party'
391 ,x_msg => 'Creating Project Party'
392 ,x_log_level => 5);
393 END IF;
394
395 PA_PROJECT_PARTIES_PVT.Create_Project_Party
396 ( p_object_id => l_assignment_rec.project_id
397 ,p_object_type => 'PA_PROJECTS'
398 ,p_project_id => l_assignment_rec.project_id
399 ,p_resource_type_id => 101
400 ,p_project_role_id => l_assignment_rec.project_role_id
401 ,p_resource_source_id => l_resource_source_id
402 ,p_start_date_active => l_assignment_rec.start_date
403 ,p_scheduled_flag => 'Y'
404 ,p_calling_module => 'ASSIGNMENT'
405 ,p_project_end_date => NULL
406 ,p_end_date_active => l_assignment_rec.end_date
407 ,p_validate_only => p_validate_only
408 ,p_commit => FND_API.G_FALSE
409 ,x_project_party_id => l_assignment_rec.project_party_id
410 ,x_resource_id => l_assignment_rec.resource_id
411 ,x_assignment_id => l_pp_assignment_id
412 ,x_wf_type => l_wf_type
413 ,x_wf_item_type => l_wf_item_type
414 ,x_wf_process => l_wf_process
415 ,x_return_status => x_return_status
416 ,x_msg_count => l_msg_count
417 ,x_msg_data => l_msg_data
418 );
419 IF P_DEBUG_MODE = 'Y' THEN
420 pa_debug.write(x_module => 'pa.plsql.PA_STAFFED_ASSIGNMENT_PVT.Create_Staffed_Assignment'
421 ,x_msg => 'create project party,status='||x_return_status
422 ,x_log_level => li_message_level);
423 END IF;
424
425
426 x_resource_id := l_assignment_rec.resource_id;
427 --dbms_output.put_line('proj party return status is '||x_return_status);
428 --dbms_output.put_line('proj party resource id is '||l_assignment_rec.resource_id);
429 --dbms_output.put_line('proj party id is '||l_assignment_rec.project_party_id);
430 --dbms_output.put_line('number of error '||l_msg_count);
431
432 IF (x_return_status <>FND_API.G_RET_STS_SUCCESS) THEN
433 PA_ASSIGNMENTS_PUB.g_error_exists := FND_API.G_TRUE;
434 l_project_parties_error_exists := FND_API.G_TRUE;
435 END IF;
436
437 END IF; --end of create project party check
438
439 --Assign out parameter: resource id
440 IF (l_assignment_rec.resource_id <> FND_API.G_MISS_NUM) THEN
441 x_resource_id := l_assignment_rec.resource_id;
442 END IF;
443
444 --Bug 2229861: Check if the resource records are pulled into
445 -- pa_resources_denorm table on the assignment start date
446 l_check_resource := FND_API.G_RET_STS_SUCCESS;
447
448 PA_RESOURCE_UTILS.Validate_Person (
449 p_person_id => l_resource_source_id
450 ,p_start_date => l_assignment_rec.start_date
451 ,x_return_status => l_check_resource
452 );
453 IF P_DEBUG_MODE = 'Y' THEN
454 pa_debug.write(x_module => 'pa.plsql.PA_STAFFED_ASSIGNMENT_PVT.Create_Staffed_Assignment'
455 ,x_msg => 'validate person, status='||l_check_resource
456 ,x_log_level => li_message_level);
457 END IF;
458
459 IF l_check_resource <> FND_API.G_RET_STS_SUCCESS THEN
460 PA_ASSIGNMENTS_PUB.g_error_exists := FND_API.G_TRUE;
461 -- bug 4091194 : This person is invalid for the assignment.
462 -- Set it to null to avoid further process.
463 l_assignment_rec.resource_id := NULL;
464 END IF;
465
466 --Get utilization defaults before creating requirement/assignment
467 --IF it has not been defaulted already OR
468 --IF it is copying from an assignment into a requirement AND
469 --IF IT IS NOT a template requirement.
470 IF (((l_assignment_rec.fcst_tp_amount_type IS NULL) OR
471 (l_assignment_rec.fcst_tp_amount_type = FND_API.G_MISS_CHAR) OR
472 (p_asgn_creation_mode = 'COPY' AND l_assignment_rec.source_assignment_type <> 'OPEN_ASSIGNMENT')) AND
473 (l_assignment_rec.project_id IS NOT NULL AND l_assignment_rec.project_id <> FND_API.G_MISS_NUM)) THEN
474
475 --dbms_output.put_line('calling assignment default');
476 --Log Message
477 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
478 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_STAFFED_ASSIGNMENTS_PVT.Create_Assignment.utilization_defaults'
479 ,x_msg => 'Getting Utilization Defaults.'
480 ,x_log_level => 5);
481 END IF;
482
483 PA_FORECAST_ITEMS_UTILS.Get_Assignment_Default(
484 p_assignment_type => l_assignment_rec.assignment_type,
485 p_project_id => l_assignment_rec.project_id,
486 p_project_role_id => l_assignment_rec.project_role_id,
487 p_work_type_id => l_assignment_rec.work_type_id,
488 x_work_type_id => l_work_type_id,
489 x_default_tp_amount_type => l_fcst_tp_amount_type_tmp, -- 5130421
490 x_default_job_group_id => l_assignment_rec.fcst_job_group_id,
491 x_default_job_id => l_assignment_rec.fcst_job_id,
492 x_org_id => l_assignment_rec.expenditure_org_id,
493 x_carrying_out_organization_id=> l_assignment_rec.expenditure_organization_id,
494 x_default_assign_exp_type => l_expenditure_type_tmp1, -- 5130421
495 x_default_assign_exp_type_cls => l_expenditure_type_class_tmp, -- 5130421
496 x_return_status => l_return_status,
497 x_msg_count => l_msg_count,
498 x_msg_data => l_msg_data
499 );
500
501 -- Bug 5130421 : Expenditure type and class can be passed in, so don't default if passed from PLSQL
502 IF PA_STARTUP.G_Calling_Application = 'PLSQL' AND l_assignment_rec.expenditure_type IS NOT NULL AND l_assignment_rec.expenditure_type <> FND_API.G_MISS_CHAR
503 AND l_assignment_rec.expenditure_type_class IS NOT NULL AND l_assignment_rec.expenditure_type_class IS NOT NULL
504 THEN
505 null;
506 ELSE
507 l_assignment_rec.expenditure_type:=l_expenditure_type_tmp1;
508 l_assignment_rec.expenditure_type_class:=l_expenditure_type_class_tmp;
509 END IF;
510
511 -- fcst tp amount type shd get default from work type
512 -- it was getting default from the default work type
513 IF l_assignment_rec.work_type_id IS NOT NULL AND l_assignment_rec.work_type_id <> FND_API.G_MISS_NUM THEN
514 Pa_Fp_Org_Fcst_Utils.Get_Tp_Amount_Type(
515 p_project_id => l_assignment_rec.project_id,
516 p_work_type_id => l_assignment_rec.work_type_id,
517 x_tp_amount_type => l_fcst_tp_amount_type_tmp,
518 x_return_status => l_return_status,
519 x_msg_count => l_msg_count,
520 x_msg_data => l_msg_data);
521 END IF;
522 l_assignment_rec.fcst_tp_amount_type := l_fcst_tp_amount_type_tmp;
523 -- Bug 5130421 : End
524
525 IF P_DEBUG_MODE = 'Y' THEN
526 pa_debug.write(x_module => 'pa.plsql.PA_STAFFED_ASSIGNMENT_PVT.Create_Staffed_Assignment'
527 ,x_msg => 'work_type_id='||l_work_type_id||
528 ' tp_amount_type='||l_assignment_rec.fcst_tp_amount_type||
529 ' job_group_id='||l_assignment_rec.fcst_job_group_id
530 ,x_log_level => li_message_level);
531
532 pa_debug.write(x_module => 'pa.plsql.PA_STAFFED_ASSIGNMENT_PVT.Create_Staffed_Assignment'
533 ,x_msg => 'job_id='||l_assignment_rec.fcst_job_id||
534 ' org_id='||l_assignment_rec.expenditure_org_id||
535 ' carry_out_org_id='||l_assignment_rec.expenditure_organization_id
536 ,x_log_level => li_message_level);
537
538 pa_debug.write(x_module => 'pa.plsql.PA_STAFFED_ASSIGNMENT_PVT.Create_Staffed_Assignment'
539 ,x_msg => 'exp_type='||l_assignment_rec.expenditure_type||
540 ' exp_type_cls='||l_assignment_rec.expenditure_type_class||
541 ' return_status='||l_return_status
542 ,x_log_level => li_message_level);
543 END IF;
544
545 --dbms_output.put_line('after assignment default:'|| l_return_status);
546
547 END IF;
548
549 --
550 -- Get bill rate and bill rate currency code, and markup percent
551 -- if this is not an admin assignment
552 --
553 IF (l_assignment_rec.project_id IS NOT NULL AND l_assignment_rec.project_id <> FND_API.G_MISS_NUM) AND
554 l_project_parties_error_exists <> FND_API.G_TRUE AND
555 l_check_resource = FND_API.G_RET_STS_SUCCESS AND
556 l_assignment_rec.assignment_type <> 'STAFFED_ADMIN_ASSIGNMENT' AND
557 l_resource_source_id IS NOT NULL THEN
558
559 --Log Message
560 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
561 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_ASSIGNMENT_PVT.Create_Assignment.bill_rate'
562 ,x_msg => 'Getting Revenue Bill Rate'
563 ,x_log_level => 5);
564 END IF;
565 --dbms_output.put_line('before get rev amt');
566
567 PA_FORECAST_REVENUE.Get_Rev_Amt(
568 p_project_id => l_assignment_rec.project_id
569 ,p_quantity => 0
570 ,p_person_id => l_resource_source_id
571 ,p_item_date => l_assignment_rec.start_date
572 ,p_forecast_job_id => l_assignment_rec.fcst_job_id
573 ,p_forecast_job_group_id => l_assignment_rec.fcst_job_group_id
574 ,p_expenditure_org_id => NULL
575 ,p_expenditure_organization_id => NULL
576 ,p_check_error_flag => 'N'
577 ,x_bill_rate => l_assignment_rec.revenue_bill_rate
578 ,x_raw_revenue => l_raw_revenue
579 ,x_rev_currency_code => l_assignment_rec.revenue_currency_code
580 ,x_markup_percentage => l_assignment_rec.markup_percent
581 ,x_return_status => l_return_status
582 ,x_msg_count => l_msg_count
583 ,x_msg_data => l_msg_data);
584 --dbms_output.put_line('after get rev amt');
585
586 END IF;
587 IF P_DEBUG_MODE = 'Y' THEN
588 pa_debug.write(x_module => 'pa.plsql.PA_STAFFED_ASSIGNMENT_PVT.Create_Staffed_Assignment'
589 ,x_msg => 'get_rev_amt done'
590 ,x_log_level => li_message_level);
591 END IF;
592
593 -- FP.M Development
594 IF (p_assignment_rec.resource_list_member_id = FND_API.G_MISS_NUM OR
595 p_assignment_rec.resource_list_member_id IS NULL) AND
596 p_assignment_rec.project_id <> FND_API.G_MISS_NUM AND
597 l_assignment_rec.resource_id IS NOT NULL THEN
598 begin
599
600 SELECT proj_asgmt_res_format_id
601 INTO l_proj_asgmt_res_format_id
602 FROM PA_PROJECTS_ALL
603 WHERE project_id = p_assignment_rec.project_id;
604
605 IF P_DEBUG_MODE = 'Y' THEN
606 pa_debug.write(x_module => 'pa.plsql.PA_STAFFED_ASSIGNMENT_PVT.Create_Staffed_Assignment'
607 ,x_msg => 'resource_id='||l_assignment_rec.resource_id
608 ,x_log_level => li_message_level);
609 END IF;
610
611 SELECT person_id
612 INTO l_person_id
613 FROM pa_resources_denorm
614 WHERE resource_id = l_assignment_rec.resource_id
615 AND rownum = 1;
616
617 exception
618 when others then
619 null;
620 end;
621
622 IF l_proj_asgmt_res_format_id = FND_API.G_MISS_NUM THEN
623 l_proj_asgmt_res_format_id := NULL;
624 END IF;
625
626 l_person_id_tmp := l_person_id;
627 IF l_person_id_tmp = FND_API.G_MISS_NUM THEN
628 l_person_id_tmp := NULL;
629 END IF;
630
631 l_fcst_job_id_tmp := l_assignment_rec.fcst_job_id;
632 IF l_fcst_job_id_tmp = FND_API.G_MISS_NUM THEN
633 l_fcst_job_id_tmp := NULL;
634 END IF;
635
636 l_expenditure_type_tmp := l_assignment_rec.expenditure_type;
637 IF l_expenditure_type_tmp = FND_API.G_MISS_CHAR THEN
638 l_expenditure_type_tmp := NULL;
639 END IF;
640
641 l_project_role_id_tmp := l_assignment_rec.project_role_id;
642 IF l_project_role_id_tmp = FND_API.G_MISS_NUM THEN
643 l_project_role_id_tmp := NULL;
644 END IF;
645
646 l_assignment_name_tmp := l_assignment_rec.assignment_name;
647 IF l_assignment_name_tmp = FND_API.G_MISS_CHAR THEN
648 l_assignment_name_tmp := NULL;
649 END IF;
650
651 -- For Staffed Assignment only, get organization from resource before
652 -- deriving planning resources
653 OPEN get_resource_organization;
654 FETCH get_resource_organization INTO l_resource_organization_id;
655 CLOSE get_resource_organization;
656
657 -- Whenever we call Derive_resource_list_member for staffed assignment
658 -- by passing in person_id, the job_id and person_type are required to pass in.
659
660 /* Start of Changes for Bug 6056112 */
661 SELECT nvl(future_term_wf_flag,'N')
662 INTO l_future_term_wf_flag
663 FROM pa_resources
664 WHERE resource_id = l_assignment_rec.resource_id;
665
666 IF (nvl(l_future_term_wf_flag,'N') = 'Y') THEN
667
668 SELECT job_id
669 INTO l_fcst_job_id_tmp
670 FROM pa_resources_denorm prd
671 WHERE prd.person_id = l_person_id_tmp
672 AND l_assignment_rec.start_date BETWEEN prd.resource_effective_start_date
673 AND prd.resource_effective_end_date ;
674
675 SELECT decode(nvl(peo.employee_number,0), 0,'CWK','EMP')
676 INTO l_person_type_code
677 FROM per_all_people_f peo
678 WHERE peo.person_id = l_person_id_tmp
679 AND l_assignment_rec.start_date BETWEEN peo.effective_start_date
680 AND peo.effective_end_date ;
681
682 ELSE --IF (nvl(l_future_term_wf_flag,'N') = 'Y')
683
684 SELECT job_id
685 INTO l_fcst_job_id_tmp
686 FROM per_all_assignments_f assn
687 WHERE assn.person_id = l_person_id_tmp
688 AND l_assignment_rec.start_date BETWEEN assn.effective_start_date
689 AND assn.effective_end_date
690 AND assn.assignment_type in ('C','E')
691 AND assn.primary_flag = 'Y'
692 AND ROWNUM = 1;
693
694 SELECT decode(peo.current_employee_flag, 'Y', 'EMP', 'CWK')
695 INTO l_person_type_code
696 FROM per_all_people_f peo
697 WHERE peo.person_id = l_person_id_tmp
698 AND l_assignment_rec.start_date BETWEEN peo.effective_start_date
699 AND peo.effective_end_date
700 AND ROWNUM = 1;
701
702 END IF ; --IF (nvl(l_future_term_wf_flag,'N') = 'Y')
703 /* End of Changes for Bug 6056112 */
704
705 IF P_DEBUG_MODE = 'Y' THEN
706 pa_debug.write(x_module => 'pa.plsql.PA_STAFFED_ASSIGNMENT_PVT.Create_Staffed_Assignment'
707 ,x_msg => 'proj_id='||p_assignment_rec.project_id||
708 ' res_format='||l_proj_asgmt_res_format_id||
709 ' person_id='||l_person_id_tmp||
710 ' job_id='||l_fcst_job_id_tmp
711 ,x_log_level => li_message_level);
712 pa_debug.write(x_module => 'pa.plsql.PA_STAFFED_ASSIGNMENT_PVT.Create_Staffed_Assignment'
713 ,x_msg => 'org_id='||l_resource_organization_id||
714 ' exp_type='||l_expenditure_type_tmp||
715 ' role_id='||l_project_role_id_tmp||
716 ' named_role='||l_assignment_name_tmp
717 ,x_log_level => li_message_level);
718 END IF;
719
720 -- check if this is a future dated employee. If it is, do
721 -- NOT call derive_resource_list_member API.
722 OPEN check_future_dated_employee;
723 FETCH check_future_dated_employee INTO l_future_dated_emp;
724
725 IF P_DEBUG_MODE = 'Y' THEN
726 pa_debug.write(x_module => 'pa.plsql.PA_STAFFED_ASSIGNMENT_PVT.Create_Staffed_Assignment'
727 ,x_msg => 'future_dated_employee ='||l_future_dated_emp
728 ,x_log_level => li_message_level);
729 END IF;
730
731 IF check_future_dated_employee%NOTFOUND THEN
732 l_assignment_rec.resource_list_member_id := NULL;
733 ELSE
734 l_assignment_rec.resource_list_member_id :=
735 PA_PLANNING_RESOURCE_UTILS.DERIVE_RESOURCE_LIST_MEMBER (
736 p_project_id => p_assignment_rec.project_id
737 ,p_res_format_id => l_proj_asgmt_res_format_id
738 ,p_person_id => l_person_id_tmp
739 ,p_job_id => l_fcst_job_id_tmp
740 ,p_organization_id => l_resource_organization_id
741 ,p_expenditure_type => l_expenditure_type_tmp
742 ,p_project_role_id => l_project_role_id_tmp
743 ,p_person_type_code => l_person_type_code
744 ,p_named_role => l_assignment_name_tmp);
745 END IF;
746 CLOSE check_future_dated_employee;
747
748 IF P_DEBUG_MODE = 'Y' THEN
749 pa_debug.write(x_module => 'pa.plsql.PA_STAFFED_ASSIGNMENT_PVT.Create_Staffed_Assignment'
750 ,x_msg => 'resource_list_member_id='||l_assignment_rec.resource_list_member_id
751 ,x_log_level => li_message_level);
752 END IF;
753
754 END IF;
755
756 --Log Message
757 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
758 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_STAFFED_ASSIGNMENT_PVT.Create_Staffed_Assignment.function_security'
759 ,x_msg => 'Check if user can confirm an assignment.'
760 ,x_log_level => 5);
761 END IF;
762 --function security call:
763
764 -- Perform security check to see if user has privilege to create
765 -- administrative assignment on the resource if this is mass transaction
766
767
768 IF p_asgn_creation_mode = 'MASS' AND
769 l_assignment_rec.resource_id IS NOT NULL THEN
770
771 IF l_assignment_rec.assignment_type='STAFFED_ADMIN_ASSIGNMENT' THEN
772
773 pa_security_pvt.check_confirm_asmt(p_project_id => l_assignment_rec.project_id,
774 p_resource_id => l_assignment_rec.resource_id,
775 p_resource_name => null,
776 p_privilege => 'PA_ADM_ASN_CR_AND_DL',
777 p_start_date => l_assignment_rec.start_date,
778 x_ret_code => l_ret_code,
779 x_return_status => l_return_status,
780 x_msg_count => l_msg_count,
781 x_msg_data => l_msg_data);
782
783 IF l_ret_code = FND_API.G_FALSE THEN
784 PA_UTILS.Add_Message ( p_app_short_name => 'PA'
785 ,p_msg_name => 'PA_ADD_ADMIN_ASMT_SECURITY' );
786 PA_ASSIGNMENTS_PUB.g_error_exists := FND_API.G_TRUE;
787 END IF;
788
789 END IF;
790 END IF;
791
792 --If the logged in user is trying to confirm an assignment then
793 --see if that user has the permission to confirm an assignment for the
794 --resource.
795 /* Created for bug 2381199 */
796 OPEN get_system_status;
797 FETCH get_system_status INTO l_system_status_code;
798 CLOSE get_system_status;
799 /* Created for bug 2381199 */
800 IF l_assignment_rec.resource_id IS NOT NULL THEN
801
802 IF l_system_status_code = 'STAFFED_ASGMT_CONF' THEN
803
804 IF l_assignment_rec.assignment_type='STAFFED_ASSIGNMENT' THEN
805
806 pa_security_pvt.check_confirm_asmt(p_project_id => l_assignment_rec.project_id,
807 p_resource_id => l_assignment_rec.resource_id,
808 p_resource_name => null,
809 p_privilege => 'PA_ASN_CONFIRM',
810 p_start_date => l_assignment_rec.start_date,
811 x_ret_code => l_ret_code,
812 x_return_status => l_return_status,
813 x_msg_count => l_msg_count,
814 x_msg_data => l_msg_data);
815 --dbms_output.put_line('function security check: ret_code is: '||l_ret_code);
816 ELSIF l_assignment_rec.assignment_type='STAFFED_ADMIN_ASSIGNMENT' THEN
817
818 pa_security_pvt.check_confirm_asmt(p_project_id => l_assignment_rec.project_id,
819 p_resource_id => l_assignment_rec.resource_id,
820 p_resource_name => null,
821 p_privilege => 'PA_ADM_ASN_CONFIRM',
822 p_start_date => l_assignment_rec.start_date,
823 x_ret_code => l_ret_code,
824 x_return_status => l_return_status,
825 x_msg_count => l_msg_count,
826 x_msg_data => l_msg_data);
827 END IF;
828
829 IF l_ret_code = FND_API.G_FALSE THEN
830 PA_UTILS.Add_Message ( p_app_short_name => 'PA'
831 ,p_msg_name => 'PA_ASGN_CONFIRM_NOT_ALLOWED' );
832 PA_ASSIGNMENTS_PUB.g_error_exists := FND_API.G_TRUE;
833 END IF;
834
835 END IF;
836
837 END IF; -- end of check resource id not null
838
839 --DBMS_OUTPUT.Put_Line('Debug stage 1');
840
841 --cross charge validation.
842
843 IF l_assignment_rec.resource_id IS NOT NULL AND l_assignment_rec.resource_id <> FND_API.G_MISS_NUM THEN
844
845 --dbms_output.put_line('cc resource id: '||l_assignment_rec.resource_id);
846 --dbms_output.put_line('cc start date: '||l_assignment_rec.start_date);
847 --dbms_output.put_line('cc end date: '||l_assignment_rec.end_date);
848
849 --Log Message
850 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
851 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_STAFFED_ASSIGNMENT_PVT.Create_Staffed_Assignment.cc_resource'
852 ,x_msg => 'Check if resource can be cross-charged.'
853 ,x_log_level => 5);
854 END IF;
855 PA_RESOURCE_UTILS.check_cc_for_resource(p_resource_id => l_assignment_rec.resource_id,
856 p_project_id => l_assignment_rec.project_id,
857 p_start_date => l_assignment_rec.start_date,
858 p_end_date => l_assignment_rec.end_date,
859 x_cc_ok => l_cc_ok,
860 x_return_status => l_return_status,
861 x_error_message_code => l_error_message_code);
862
863 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
864 PA_UTILS.Add_Message( p_app_short_name => 'PA'
865 ,p_msg_name => l_error_message_code);
866 PA_ASSIGNMENTS_PUB.g_error_exists := FND_API.G_TRUE;
867 END IF;
868 --dbms_output.put_line('l_cc_ok: '||l_cc_ok);
869 IF l_cc_ok <> 'Y' THEN
870 PA_UTILS.Add_Message( p_app_short_name => 'PA'
871 ,p_msg_name => 'CROSS_CHARGE_VALIDATION_FAILED');
872 PA_ASSIGNMENTS_PUB.g_error_exists := FND_API.G_TRUE;
873 END IF;
874
875 END IF;
876
877 END IF; --end of bypassing procedure that use Project Id
878
879 -- v1.1 change: Admin assignment can use project, resource, or other calendar
880 /*
881 --Admin assignments should use the resource calendar at 100%
882
883 IF l_assignment_rec.assignment_type='STAFFED_ADMIN_ASSIGNMENT' THEN
884
885 l_assignment_rec.calendar_type := 'RESOURCE';
886 l_assignment_rec.resource_calendar_percent := 100;
887
888 END IF;
889 */
890
891 --dbms_output.put_line('before calling insert row');
892
893 /*Adding changes for Bug 5918412 to get calendar id when the calendar_type = 'RESOURCE'*/
894 IF (l_assignment_rec.calendar_type = 'RESOURCE')
895 AND l_project_parties_error_exists <> FND_API.G_TRUE -- 6210780
896 AND l_assignment_rec.resource_id IS NOT NULL -- 6210780
897 THEN
898
899 l_assignment_rec.calendar_id := pa_schedule_utils.get_res_calendar(p_resource_id => l_assignment_rec.resource_id ,
900 p_start_date => l_assignment_rec.start_date,
901 p_end_date => l_assignment_rec.end_date);
902 END IF ;
903 /*Changes end for Bug 5918412 */
904
905 IF (p_validate_only <> FND_API.G_TRUE AND FND_MSG_PUB.Count_Msg = 0) THEN
906
907
908 --Log Message
909 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
910 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_STAFFED_ASSIGNMENT_PVT.Create_Staffed_Assignment.insert_row'
911 ,x_msg => 'Insert new assignment into Table.'
912 ,x_log_level => 5);
913 END IF;
914
915 -- Start 5130421
916 -- nvl in below clause is needed for flow other than AMG
917 IF nvl(PA_STAFFED_ASSIGNMENT_PVT.G_AUTO_APPROVE,'N') = 'Y' THEN
918 l_apprvl_status_code := PA_ASSIGNMENT_APPROVAL_PUB.g_approved;
919 ELSE
920 l_apprvl_status_code := PA_ASSIGNMENT_APPROVAL_PUB.g_working ;
921 END IF;
922
923 --RESET the global variable after usage
924 PA_STAFFED_ASSIGNMENT_PVT.G_AUTO_APPROVE := NULL;
925 --End 5130421
926
927 PA_PROJECT_ASSIGNMENTS_PKG.Insert_Row
928 (p_assignment_name => l_assignment_rec.assignment_name
929 ,p_assignment_type => l_assignment_rec.assignment_type
930 ,p_multiple_status_flag => l_assignment_rec.multiple_status_flag
931 ,p_status_code => l_assignment_rec.status_code
932 -- Commented for PJR Enhancement 5130421 ,p_apprvl_status_code => PA_ASSIGNMENT_APPROVAL_PUB.g_working
933 ,p_apprvl_status_code => l_apprvl_status_code -- Included for 5130421
934 ,p_staffing_priority_code => l_assignment_rec.staffing_priority_code
935 ,p_project_id => l_assignment_rec.project_id
936 ,p_project_role_id => l_assignment_rec.project_role_id
937 ,p_resource_id => l_assignment_rec.resource_id
938 ,p_project_party_id => l_assignment_rec.project_party_id
939 ,p_description => l_assignment_rec.description
940 ,p_start_date => l_assignment_rec.start_date
941 ,p_end_date => l_assignment_rec.end_date
942 ,p_assignment_effort => l_assignment_rec.assignment_effort
943 ,p_extension_possible => l_assignment_rec.extension_possible
944 ,p_source_assignment_id => l_assignment_rec.source_assignment_id
945 ,p_additional_information => l_assignment_rec.additional_information
946 ,p_work_type_id => l_assignment_rec.work_type_id
947 ,p_revenue_currency_code => l_assignment_rec.revenue_currency_code
948 ,p_revenue_bill_rate => l_assignment_rec.revenue_bill_rate
949 ,p_markup_percent => l_assignment_rec.markup_percent
950 ,p_fcst_tp_amount_type => l_assignment_rec.fcst_tp_amount_type
951 ,p_fcst_job_id => l_assignment_rec.fcst_job_id
952 ,p_fcst_job_group_id => l_assignment_rec.fcst_job_group_id
953 ,p_expenditure_org_id => l_assignment_rec.expenditure_org_id
954 ,p_expenditure_organization_id => l_assignment_rec.expenditure_organization_id
955 ,p_expenditure_type_class => l_assignment_rec.expenditure_type_class
956 ,p_expenditure_type => l_assignment_rec.expenditure_type
957 ,p_expense_owner => l_assignment_rec.expense_owner
958 ,p_expense_limit => l_assignment_rec.expense_limit
959 ,p_expense_limit_currency_code => l_assignment_rec.expense_limit_currency_code
960 ,p_location_id => l_assignment_rec.location_id
961 ,p_calendar_type => l_assignment_rec.calendar_type
962 ,p_calendar_id => l_assignment_rec.calendar_id
963 ,p_resource_calendar_percent => l_assignment_rec.resource_calendar_percent
964 ,p_bill_rate_override => l_assignment_rec.bill_rate_override
965 ,p_bill_rate_curr_override => l_assignment_rec.bill_rate_curr_override
966 ,p_markup_percent_override => l_assignment_rec.markup_percent_override
967 ,p_discount_percentage => l_assignment_rec.discount_percentage -- FP.L Development
968 ,p_rate_disc_reason_code => l_assignment_rec.rate_disc_reason_code -- FP.L Development
969 ,p_tp_rate_override => l_assignment_rec.tp_rate_override
970 ,p_tp_currency_override => l_assignment_rec.tp_currency_override
971 ,p_tp_calc_base_code_override => l_assignment_rec.tp_calc_base_code_override
972 ,p_tp_percent_applied_override => l_assignment_rec.tp_percent_applied_override
973 ,p_staffing_owner_person_id => l_assignment_rec.staffing_owner_person_id -- FP.L Development
974 ,p_attribute_category => l_assignment_rec.attribute_category
975 ,p_attribute1 => l_assignment_rec.attribute1
976 ,p_attribute2 => l_assignment_rec.attribute2
977 ,p_attribute3 => l_assignment_rec.attribute3
978 ,p_attribute4 => l_assignment_rec.attribute4
979 ,p_attribute5 => l_assignment_rec.attribute5
980 ,p_attribute6 => l_assignment_rec.attribute6
981 ,p_attribute7 => l_assignment_rec.attribute7
982 ,p_attribute8 => l_assignment_rec.attribute8
983 ,p_attribute9 => l_assignment_rec.attribute9
984 ,p_attribute10 => l_assignment_rec.attribute10
985 ,p_attribute11 => l_assignment_rec.attribute11
986 ,p_attribute12 => l_assignment_rec.attribute12
987 ,p_attribute13 => l_assignment_rec.attribute13
988 ,p_attribute14 => l_assignment_rec.attribute14
989 ,p_attribute15 => l_assignment_rec.attribute15
990 ,p_resource_list_member_id => l_assignment_rec.resource_list_member_id
991 ,x_assignment_row_id => x_assignment_row_id
992 ,x_new_assignment_id => x_new_assignment_id
993 ,x_assignment_number => l_assignment_rec.assignment_number
994 ,x_return_status => x_return_status
995 );
996 IF P_DEBUG_MODE = 'Y' THEN
997 pa_debug.write(x_module => 'pa.plsql.PA_STAFFED_ASSIGNMENT_PVT.Create_Staffed_Assignment'
998 ,x_msg => 'after insert_row, status='||x_return_status
999 ,x_log_level => li_message_level);
1000 END IF;
1001
1002 l_assignment_rec.assignment_id := x_new_assignment_id;
1003
1004 -- p_sch_basis_flag :
1005 -- This field is required to know the following :
1006 -- 'R' -> Resource :--> Schedule creation for Staffed team role is
1007 -- based on resource calendars work pattern.
1008 -- 'A' --> Open Assignment :--> Schedule creation for Staffed team
1009 -- role is based on Opem assignment work pattern.
1010 -- Work pattern for 'Staffed Assignment' may be different from the project itself, since
1011 -- user can change the work pattern after 'Open Assignment' is created.
1012 -- 'P' & 'O' --> for these two parameters i will use the calendar_id passed to the API.
1013 --
1014 -- ( 'R' -> based on resource , 'A' -> based on open assignment, 'P' -> project , 'O' -> others
1015
1016
1017 IF l_assignment_rec.source_assignment_id IS NOT NULL AND l_assignment_rec.source_assignment_id <>
1018 FND_API.G_MISS_NUM THEN
1019
1020 l_schedule_basis_flag := 'A';
1021
1022 ELSIF l_assignment_rec.calendar_type = 'PROJECT' THEN
1023
1024 l_schedule_basis_flag := 'P';
1025
1026 ELSIF l_assignment_rec.calendar_type = 'RESOURCE' THEN
1027
1028 l_schedule_basis_flag := 'R';
1029
1030 ELSIF l_assignment_rec.calendar_type = 'OTHER' THEN
1031
1032 l_schedule_basis_flag := 'O';
1033
1034 -- ELSIF l_assignment_rec.calendar_type = 'TASK_ASSIGNMENT' THEN
1035 -- l_sum_tasks_flag := 'Y';
1036
1037 END IF;
1038
1039 --dbms_output.put_line('resource_id ='||l_assignment_rec.resource_id);
1040 --dbms_output.put_line('project_id ='||l_assignment_rec.project_id);
1041 --dbms_output.put_line('sched basis ='||l_schedule_basis_flag);
1042 --dbms_output.put_line('proj party id ='||l_assignment_rec.project_party_id);
1043 --dbms_output.put_line('cal_id ='||l_assignment_rec.calendar_id);
1044 --dbms_output.put_line('assignment_id ='||l_assignment_rec.assignment_id);
1045 --dbms_output.put_line('source_ass_id ='||l_assignment_rec.source_assignment_id);
1046 --dbms_output.put_line('res_cal_per_ ='||l_assignment_rec.resource_calendar_percent);
1047 --dbms_output.put_line('start_date ='||l_assignment_rec.start_date);
1048 --dbms_output.put_line('end_date ='||l_assignment_rec.end_date);
1049 --dbms_output.put_line('status code ='||l_assignment_rec.status_code);
1050 --dbms_output.put_line('work type id ='||l_assignment_rec.work_type_id);
1051
1052 --pa_schedule_utils.l_print_log := TRUE;
1053
1054 --FP.M Development
1055 IF P_DEBUG_MODE = 'Y' THEN
1056 pa_debug.write(x_module => 'pa.plsql.PA_STAFFED_ASSIGNMENT_PVT.Create_Staffed_Assignment'
1057 ,x_msg => 'FP.M Development'
1058 ,x_log_level => li_message_level);
1059
1060 pa_debug.write(x_module => 'pa.plsql.PA_STAFFED_ASSIGNMENT_PVT.Create_Staffed_Assignment'
1061 ,x_msg => 'resource_list_member_id'||l_assignment_rec.resource_list_member_id
1062 ,x_log_level => li_message_level);
1063
1064 pa_debug.write(x_module => 'pa.plsql.PA_STAFFED_ASSIGNMENT_PVT.Create_Staffed_Assignment'
1065 ,x_msg => 'budget_version_id'||p_budget_version_id
1066 ,x_log_level => li_message_level);
1067 END IF;
1068
1069 IF p_budget_version_id IS NOT NULL AND p_budget_version_id <> FND_API.G_MISS_NUM AND
1070 l_assignment_rec.resource_list_member_id IS NOT NULL AND
1071 l_assignment_rec.resource_list_member_id <> FND_API.G_MISS_NUM THEN
1072
1073 OPEN get_bu_resource_assignments;
1074 FETCH get_bu_resource_assignments
1075 BULK COLLECT INTO l_task_assignment_id_tbl,
1076 l_task_version_id_tbl,
1077 l_budget_version_id_tbl,
1078 l_struct_version_id_tbl;
1079 CLOSE get_bu_resource_assignments;
1080
1081 ELSE
1082
1083 /*Added this IF for bug 7492618*/
1084 IF l_assignment_rec.resource_list_member_id IS NOT NULL AND
1085 l_assignment_rec.resource_list_member_id <> FND_API.G_MISS_NUM THEN
1086
1087 OPEN get_td_resource_assignments;
1088 FETCH get_td_resource_assignments
1089 BULK COLLECT INTO l_task_assignment_id_tbl,
1090 l_task_version_id_tbl,
1091 l_budget_version_id_tbl,
1092 l_struct_version_id_tbl;
1093 CLOSE get_td_resource_assignments;
1094 END IF ;
1095 /*Added this IF for bug 7492618*/
1096
1097 END IF;
1098
1099 -- If multiple requirements are created, only the first requirement will be
1100 -- linked to the task assignments.
1101 -- Call planning_transaction_utils api to update project_assignment_id in
1102 -- pa_resource_assignments table.
1103 pa_assignments_pvt.Update_Task_Assignments(
1104 p_mode => 'CREATE'
1105 ,p_task_assignment_id_tbl => l_task_assignment_id_tbl
1106 ,p_task_version_id_tbl => l_task_version_id_tbl
1107 ,p_budget_version_id_tbl => l_budget_version_id_tbl
1108 ,p_struct_version_id_tbl => l_struct_version_id_tbl
1109 -- change project_assignment_id to this assignment_id
1110 ,p_project_assignment_id => PA_ASSIGNMENTS_PUB.g_assignment_id_tbl(1).assignment_id
1111 -- ,p_resource_list_member_id => l_assignment_rec.resource_list_member_id
1112 -- change the named role to this assignment name
1113 ,p_named_role => p_assignment_rec.assignment_name
1114 ,p_project_role_id => p_assignment_rec.project_role_id
1115 ,x_return_status => l_return_status
1116 );
1117 IF P_DEBUG_MODE = 'Y' THEN
1118 pa_debug.write(x_module => 'pa.plsql.PA_STAFFED_ASSIGNMENT_PVT.Create_Staffed_Assignment'
1119 ,x_msg => 'Update_Task_Assignments status '||l_return_status
1120 ,x_log_level => li_message_level);
1121
1122 pa_debug.write(x_module => 'pa.plsql.PA_STAFFED_ASSIGNMENT_PVT.Create_Staffed_Assignment'
1123 ,x_msg => 'project_id='||l_assignment_rec.project_id||
1124 ' sch_flag='||l_schedule_basis_flag||
1125 ' party_id='||l_assignment_rec.project_party_id
1126 ,x_log_level => li_message_level);
1127
1128 pa_debug.write(x_module => 'pa.plsql.PA_STAFFED_ASSIGNMENT_PVT.Create_Staffed_Assignment'
1129 ,x_msg => 'cal_id='||l_assignment_rec.calendar_id||
1130 ' asgmt_id='||l_assignment_rec.assignment_id||
1131 ' src_asgmt_id='||l_assignment_rec.source_assignment_id
1132 ,x_log_level => li_message_level);
1133
1134 pa_debug.write(x_module => 'pa.plsql.PA_STAFFED_ASSIGNMENT_PVT.Create_Staffed_Assignment'
1135 ,x_msg => 's_date='||l_assignment_rec.start_date||
1136 ' cal_perct='||l_assignment_rec.resource_calendar_percent||
1137 ' e_date='||l_assignment_rec.end_date||
1138 ' status='||l_assignment_rec.status_code
1139 ,x_log_level => li_message_level);
1140 END IF;
1141
1142 IF l_task_assignment_id_tbl.COUNT <> 0 THEN
1143 FOR j IN l_task_assignment_id_tbl.FIRST .. l_task_assignment_id_tbl.LAST LOOP
1144 IF P_DEBUG_MODE = 'Y' THEN
1145 pa_debug.write(x_module => 'pa.plsql.PA_STAFFED_ASSIGNMENT_PVT.Create_Staffed_Assignment'
1146 ,x_msg => 'tait('||j||')='||l_task_assignment_id_tbl(j)
1147 ,x_log_level => li_message_level);
1148 END IF;
1149 END LOOP;
1150 END IF;
1151
1152 IF P_DEBUG_MODE = 'Y' THEN
1153 pa_debug.write(x_module => 'pa.plsql.PA_STAFFED_ASSIGNMENT_PVT.Create_Staffed_Assignment'
1154 ,x_msg => 'task_flag='||p_sum_tasks_flag||
1155 ' work_type='||l_assignment_rec.work_type_id
1156 ,x_log_level => li_message_level);
1157 END IF;
1158
1159 PA_SCHEDULE_PVT.Create_STF_ASG_Schedule
1160 ( p_project_id => l_assignment_rec.project_id
1161 ,p_schedule_basis_flag => l_schedule_basis_flag
1162 ,p_project_party_id => l_assignment_rec.project_party_id
1163 ,p_calendar_id => l_assignment_rec.calendar_id
1164 ,p_assignment_id => l_assignment_rec.assignment_id
1165 ,p_open_assignment_id => l_assignment_rec.source_assignment_id
1166 ,p_resource_calendar_percent => l_assignment_rec.resource_calendar_percent
1167 ,p_start_date => l_assignment_rec.start_date
1168 ,p_end_date => l_assignment_rec.end_date
1169 ,p_assignment_status_code => l_assignment_rec.status_code
1170 ,p_task_assignment_id_tbl => l_task_assignment_id_tbl
1171 ,p_sum_tasks_flag => p_sum_tasks_flag
1172 ,p_work_type_id => l_assignment_rec.work_type_id
1173 ,p_task_id => Null
1174 ,p_task_percentage => Null
1175 ,p_budget_version_id => p_budget_version_id
1176 ,x_return_status => x_return_status
1177 ,x_msg_count =>l_msg_count
1178 ,x_msg_data =>l_msg_data
1179 );
1180
1181 /* Added calc_init_transfer_price for bug 3051110 */
1182
1183 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
1184 PA_ASSIGNMENTS_PVT.Calc_Init_Transfer_Price
1185 (p_assignment_id =>l_assignment_rec.assignment_id,
1186 p_start_date => l_assignment_rec.start_date,
1187 p_debug_mode => P_DEBUG_MODE,
1188 x_return_status => l_return_status,
1189 x_msg_data => l_msg_data,
1190 x_msg_count => l_msg_count );
1191
1192 x_return_status := l_return_status;
1193 END IF;
1194
1195 END IF; -- IF (p_validate_only <> FND_API.G_TRUE AND FND_MSG_PUB.Count_Msg = 0)
1196
1197 -- Reset the error stack when returning to the calling program
1198 PA_DEBUG.Reset_err_stack;
1199
1200
1201 EXCEPTION
1202 WHEN OTHERS THEN
1203
1204 -- 4537865 : RESET OUT Params : Start
1205
1206 x_new_assignment_id := NULL ;
1207 x_assignment_row_id := NULL ;
1208 x_resource_id := NULL ;
1209 -- 4537865 : End
1210
1211 -- Set the excetption Message and the stack
1212 FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_STAFFED_ASSIGNMENT_PVT.Create_Staffed_Assignment'
1213 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
1214 --
1215 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1216 RAISE; -- This is optional depending on the needs
1217
1218 END Create_Staffed_Assignment;
1219
1220
1221
1222 PROCEDURE Update_Staffed_Assignment
1223 ( p_assignment_rec IN PA_ASSIGNMENTS_PUB.Assignment_Rec_Type
1224 ,p_location_city IN pa_locations.city%TYPE := FND_API.G_MISS_CHAR
1225 ,p_location_region IN pa_locations.region%TYPE := FND_API.G_MISS_CHAR
1226 ,p_location_country_code IN pa_locations.country_code%TYPE := FND_API.G_MISS_CHAR
1227 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
1228 ,p_validate_only IN VARCHAR2 := FND_API.G_TRUE
1229 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1230 ) IS
1231
1232 l_assignment_rec PA_ASSIGNMENTS_PUB.Assignment_Rec_Type;
1233 -- added for Bug: 4537865
1234 l_new_status_code pa_project_assignments.status_code%TYPE ;
1235
1236 -- added for Bug: 4537865
1237 l_old_status_code pa_project_assignments.status_code%TYPE;
1238 l_old_start_date pa_project_assignments.start_date%TYPE;
1239 l_old_end_date pa_project_assignments.end_date%TYPE;
1240 l_return_status VARCHAR2(1);
1241 l_msg_count NUMBER;
1242 l_msg_data VARCHAR2(2000);
1243 l_change_id NUMBER;
1244 l_error_message_code VARCHAR2(2000);
1245 l_asgn_text VARCHAR2(30);
1246 l_proj_asgmt_res_format_id NUMBER;
1247 l_person_id NUMBER;
1248 l_task_assignment_id_tbl system.pa_num_tbl_type;
1249 l_resource_list_member_id_tbl system.pa_num_tbl_type := system.pa_num_tbl_type();
1250 l_project_assignment_id_tbl system.pa_num_tbl_type := system.pa_num_tbl_type();
1251 l_proj_req_res_format_id NUMBER;
1252 l_budget_version_id_tbl system.pa_num_tbl_type := system.pa_num_tbl_type();
1253 l_struct_version_id_tbl system.pa_num_tbl_type := system.pa_num_tbl_type();
1254 l_last_bvid NUMBER;
1255 l_update_task_asgmt_id_tbl system.pa_num_tbl_type := system.pa_num_tbl_type();
1256 l_update_count NUMBER;
1257 l_last_struct_version_id NUMBER;
1258 l_task_version_id_tbl system.pa_num_tbl_type := system.pa_num_tbl_type();
1259 l_update_task_version_id_tbl system.pa_num_tbl_type := system.pa_num_tbl_type();
1260
1261 l_cur_resource_id pa_project_assignments.resource_id%TYPE;
1262 l_cur_fcst_job_id pa_project_assignments.fcst_job_id%TYPE;
1263 l_cur_exp_org_id pa_project_assignments.expenditure_organization_id%TYPE;
1264 l_cur_expenditure_type pa_project_assignments.expenditure_type%TYPE;
1265 l_cur_project_role_id pa_project_assignments.project_role_id%TYPE;
1266 l_cur_assignment_name pa_project_assignments.assignment_name%TYPE;
1267 l_cur_resource_list_member_id pa_project_assignments.resource_list_member_id%TYPE;
1268 l_new_person_id pa_resource_txn_attributes.person_id%TYPE;
1269 l_named_role pa_project_assignments.ASSIGNMENT_NAME%TYPE;
1270
1271 l_cur_res_format_id pa_res_formats_b.res_format_id%TYPE;
1272 l_cur_res_type_flag pa_res_formats_b.res_type_enabled_flag%TYPE;
1273 l_cur_orgn_flag pa_res_formats_b.orgn_enabled_flag%TYPE;
1274 l_cur_fin_cat_flag pa_res_formats_b.fin_cat_enabled_flag%TYPE;
1275 l_cur_role_flag pa_res_formats_b.role_enabled_flag%TYPE;
1276
1277 -- l_unlink_flag VARCHAR2(1) := 'N';
1278
1279 l_resource_list_members_tbl SYSTEM.PA_NUM_TBL_TYPE := system.pa_num_tbl_type();
1280 l_resource_class_flag_tbl SYSTEM.PA_VARCHAR2_1_TBL_TYPE := system.pa_varchar2_1_tbl_type();
1281 l_resource_class_code_tbl SYSTEM.PA_VARCHAR2_30_TBL_TYPE := system.pa_varchar2_30_tbl_type();
1282 l_resource_class_id_tbl SYSTEM.PA_NUM_TBL_TYPE := system.pa_num_tbl_type();
1283 l_res_type_code_tbl SYSTEM.PA_VARCHAR2_30_TBL_TYPE := system.pa_varchar2_30_tbl_type();
1284 l_incur_by_res_type_tbl SYSTEM.PA_VARCHAR2_30_TBL_TYPE := system.pa_varchar2_30_tbl_type();
1285 l_person_id_tbl SYSTEM.PA_NUM_TBL_TYPE := system.pa_num_tbl_type();
1286 l_job_id_tbl SYSTEM.PA_NUM_TBL_TYPE := system.pa_num_tbl_type();
1287 l_person_type_code_tbl SYSTEM.PA_VARCHAR2_30_TBL_TYPE := system.pa_varchar2_30_tbl_type();
1288 l_named_role_tbl SYSTEM.PA_VARCHAR2_80_TBL_TYPE := system.pa_varchar2_80_tbl_type();
1289 l_bom_resource_id_tbl SYSTEM.PA_NUM_TBL_TYPE := system.pa_num_tbl_type();
1290 l_non_labor_resource_tbl SYSTEM.PA_VARCHAR2_20_TBL_TYPE := system.pa_varchar2_20_tbl_type();
1291 l_inventory_item_id_tbl SYSTEM.PA_NUM_TBL_TYPE := system.pa_num_tbl_type();
1292 l_item_category_id_tbl SYSTEM.PA_NUM_TBL_TYPE := system.pa_num_tbl_type();
1293 l_project_role_id_tbl SYSTEM.PA_NUM_TBL_TYPE := system.pa_num_tbl_type();
1294 l_organization_id_tbl SYSTEM.PA_NUM_TBL_TYPE := system.pa_num_tbl_type();
1295 l_fc_res_type_code_tbl SYSTEM.PA_VARCHAR2_30_TBL_TYPE := system.pa_varchar2_30_tbl_type();
1296 l_expenditure_type_tbl SYSTEM.PA_VARCHAR2_30_TBL_TYPE := system.pa_varchar2_30_tbl_type();
1297 l_expenditure_category_tbl SYSTEM.PA_VARCHAR2_30_TBL_TYPE := system.pa_varchar2_30_tbl_type();
1298 l_event_type_tbl SYSTEM.PA_VARCHAR2_30_TBL_TYPE := system.pa_varchar2_30_tbl_type();
1299 l_revenue_category_code_tbl SYSTEM.PA_VARCHAR2_30_TBL_TYPE := system.pa_varchar2_30_tbl_type();
1300 l_supplier_id_tbl SYSTEM.PA_NUM_TBL_TYPE := system.pa_num_tbl_type();
1301 l_spread_curve_id_tbl SYSTEM.PA_NUM_TBL_TYPE := system.pa_num_tbl_type();
1302 l_etc_method_code_tbl SYSTEM.PA_VARCHAR2_30_TBL_TYPE := system.pa_varchar2_30_tbl_type();
1303 l_mfc_cost_type_id_tbl SYSTEM.PA_NUM_TBL_TYPE := system.pa_num_tbl_type();
1304 l_incurred_by_res_flag_tbl SYSTEM.PA_VARCHAR2_1_TBL_TYPE := system.pa_varchar2_1_tbl_type();
1305 l_incur_by_res_class_code_tbl SYSTEM.PA_VARCHAR2_30_TBL_TYPE := system.pa_varchar2_30_tbl_type();
1306 l_incur_by_role_id_tbl SYSTEM.PA_NUM_TBL_TYPE := system.pa_num_tbl_type();
1307 l_unit_of_measure_tbl SYSTEM.PA_VARCHAR2_30_TBL_TYPE := system.pa_varchar2_30_tbl_type();
1308 l_org_id_tbl SYSTEM.PA_NUM_TBL_TYPE := system.pa_num_tbl_type();
1309 l_rate_based_flag_tbl SYSTEM.PA_VARCHAR2_1_TBL_TYPE := system.pa_varchar2_1_tbl_type();
1310 l_rate_expenditure_type_tbl SYSTEM.PA_VARCHAR2_30_TBL_TYPE := system.pa_varchar2_30_tbl_type();
1311 l_rate_func_curr_code_tbl SYSTEM.PA_VARCHAR2_30_TBL_TYPE := system.pa_varchar2_30_tbl_type();
1312 l_rate_incurred_by_org_id_tbl SYSTEM.PA_NUM_TBL_TYPE := system.pa_num_tbl_type();
1313
1314 l_resource_organization_id NUMBER;
1315
1316 l_person_id_tmp NUMBER;
1317 l_fcst_job_id_tmp pa_project_assignments.fcst_job_id%TYPE;
1318 l_expenditure_type_tmp pa_project_assignments.expenditure_type%TYPE;
1319 l_project_role_id_tmp pa_project_assignments.project_role_id%TYPE;
1320 l_assignment_name_tmp pa_project_assignments.assignment_name%TYPE;
1321 l_person_type_code Varchar2(30);
1322 l_future_term_wf_flag pa_resources.future_term_wf_flag%TYPE := NULL ; --Added for Bug 6056112
1323
1324 CURSOR assignment_status_code_csr
1325 IS
1326 SELECT status_code, start_date, end_date
1327 FROM pa_project_assignments
1328 WHERE assignment_id = p_assignment_rec.assignment_id;
1329
1330 CURSOR get_unlinked_res_asgmts IS
1331 SELECT resource_assignment_id, wbs_element_version_id, budget_version_id, project_structure_version_id
1332 FROM
1333 (
1334 (SELECT ra.resource_assignment_id, ra.wbs_element_version_id, bv.budget_version_id, bv.project_structure_version_id
1335 FROM PA_RESOURCE_ASSIGNMENTS ra
1336 ,PA_BUDGET_VERSIONS bv
1337 ,PA_PROJ_ELEM_VER_STRUCTURE evs
1338 WHERE ra.project_id = bv.project_id
1339 AND bv.project_id = evs.project_id
1340 AND ra.budget_version_id = bv.budget_version_id
1341 AND bv.project_structure_version_id = evs.element_version_id
1342 AND ra.project_id = l_assignment_rec.project_id
1343 AND ra.resource_list_member_id = l_assignment_rec.resource_list_member_id
1344 AND ra.project_assignment_id = -1
1345 AND evs.status_code = 'STRUCTURE_WORKING')
1346 UNION ALL
1347 (SELECT ra.resource_assignment_id, ra.wbs_element_version_id, bv.budget_version_id, bv.project_structure_version_id
1348 FROM PA_RESOURCE_ASSIGNMENTS ra
1349 ,PA_BUDGET_VERSIONS bv
1350 ,PA_PROJ_ELEM_VER_STRUCTURE evs
1351 ,PA_PROJ_WORKPLAN_ATTR pwa
1352 WHERE pwa.wp_enable_Version_flag = 'N'
1353 AND pwa.project_id = ra.project_id
1354 AND pwa.proj_element_id = evs.proj_element_id
1355 AND ra.project_id = bv.project_id
1356 AND bv.project_id = evs.project_id
1357 AND ra.budget_version_id = bv.budget_version_id
1358 AND bv.project_structure_version_id = evs.element_version_id
1359 AND ra.resource_list_member_id = l_assignment_rec.resource_list_member_id
1360 AND ra.project_id = l_assignment_rec.project_id
1361 AND ra.project_assignment_id = -1)
1362 )
1363 ORDER BY budget_version_id, project_structure_version_id;
1364
1365 CURSOR get_linked_res_asgmts IS
1366 SELECT resource_assignment_id, wbs_element_version_id, budget_version_id, project_structure_version_id
1367 FROM
1368 (
1369 (SELECT ra.resource_assignment_id, ra.wbs_element_version_id, bv.budget_version_id, bv.project_structure_version_id
1370 FROM PA_RESOURCE_ASSIGNMENTS ra
1371 ,PA_BUDGET_VERSIONS bv
1372 ,PA_PROJ_ELEM_VER_STRUCTURE evs
1373 WHERE ra.project_id = bv.project_id
1374 AND bv.project_id = evs.project_id
1375 AND ra.budget_version_id = bv.budget_version_id
1376 AND bv.project_structure_version_id = evs.element_version_id
1377 AND ra.project_id = l_assignment_rec.project_id
1378 AND ra.project_assignment_id = l_assignment_rec.assignment_id
1379 AND evs.status_code = 'STRUCTURE_WORKING')
1380 UNION ALL
1381 (SELECT ra.resource_assignment_id, ra.wbs_element_version_id, bv.budget_version_id, bv.project_structure_version_id
1382 FROM PA_RESOURCE_ASSIGNMENTS ra
1383 ,PA_BUDGET_VERSIONS bv
1384 ,PA_PROJ_ELEM_VER_STRUCTURE evs
1385 ,PA_PROJ_WORKPLAN_ATTR pwa
1386 WHERE pwa.wp_enable_Version_flag = 'N'
1387 AND pwa.project_id = ra.project_id
1388 AND pwa.proj_element_id = evs.proj_element_id
1389 AND ra.project_id = bv.project_id
1390 AND bv.project_id = evs.project_id
1391 AND ra.budget_version_id = bv.budget_version_id
1392 AND bv.project_structure_version_id = evs.element_version_id
1393 AND ra.project_id = l_assignment_rec.project_id
1394 AND ra.project_assignment_id = l_assignment_rec.assignment_id)
1395 )
1396 ORDER BY budget_version_id, project_structure_version_id;
1397
1398 CURSOR get_res_mand_attributes IS
1399 SELECT rf.res_format_id, rf.RES_TYPE_ENABLED_FLAG,
1400 rf.ORGN_ENABLED_FLAG, rf.FIN_CAT_ENABLED_FLAG,
1401 rf.ROLE_ENABLED_FLAG
1402 FROM pa_res_formats_b rf,
1403 pa_resource_list_members rlm
1404 WHERE rlm.res_format_id = rf.res_format_id
1405 AND rlm.resource_list_member_id = l_assignment_rec.resource_list_member_id;
1406
1407 CURSOR get_cur_asgmt_attributes IS
1408 SELECT resource_id, fcst_job_id, expenditure_organization_id,
1409 expenditure_type,
1410 project_role_id, assignment_name,
1411 resource_list_member_id,
1412 project_role_id
1413 FROM pa_project_assignments
1414 WHERE assignment_id = l_assignment_rec.assignment_id;
1415
1416 -- get the resource's organization in HR
1417 CURSOR get_resource_organization IS
1418 SELECT resource_organization_id
1419 FROM pa_resources_denorm
1420 WHERE l_assignment_rec.start_date BETWEEN resource_effective_start_date AND resource_effective_end_date
1421 AND resource_id = l_assignment_rec.resource_id;
1422
1423
1424 BEGIN
1425
1426 -- 4537865 : Initialize the return_status
1427 x_return_status := FND_API.G_RET_STS_SUCCESS ;
1428
1429 -- Initialize the Error Stack
1430 PA_DEBUG.set_err_stack('PA_STAFFED_ASSIGNMENT_PVT.Update_Staffed_Assignment');
1431
1432 --Log Message
1433 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
1434 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_STAFFED_ASSIGNMENT_PVT.Update_Staffed_Assignment.begin'
1435 ,x_msg => 'Beginning of Update_Staff_Assignment'
1436 ,x_log_level => 5);
1437 END IF;
1438 --
1439 -- Assign the record to the local variable
1440 --
1441 l_assignment_rec := p_assignment_rec;
1442
1443 -- get the current attributes of the project team role
1444 OPEN get_cur_asgmt_attributes;
1445 FETCH get_cur_asgmt_attributes INTO
1446 l_cur_resource_id,
1447 l_cur_fcst_job_id,
1448 l_cur_exp_org_id,
1449 l_cur_expenditure_type,
1450 l_cur_project_role_id,
1451 l_cur_assignment_name,
1452 l_cur_resource_list_member_id,
1453 l_cur_project_role_id;
1454 CLOSE get_cur_asgmt_attributes;
1455
1456 IF p_assignment_rec.resource_id = FND_API.G_MISS_NUM THEN
1457 l_assignment_rec.resource_id := l_cur_resource_id;
1458 END IF;
1459
1460 IF P_DEBUG_MODE = 'Y' THEN
1461 pa_debug.write(x_module => 'pa.plsql.PA_STAFFED_ASSIGNMENT_PVT.Update_Staffed_Assignment'
1462 ,x_msg => 'l_asgmt_rec.resource_id='||l_assignment_rec.resource_id
1463 ,x_log_level => li_message_level);
1464 END IF;
1465
1466 IF p_assignment_rec.project_role_id = FND_API.G_MISS_NUM THEN
1467 l_assignment_rec.project_role_id := l_cur_project_role_id;
1468 END IF;
1469
1470 --Log Message
1471 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
1472 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_STAFFED_ASSIGNMENT_PVT.Update_Staffed_Assignment.begin'
1473 ,x_msg => 'Old resource list member id='||l_cur_resource_list_member_id
1474 ,x_log_level => 5);
1475 END IF;
1476
1477 IF l_cur_resource_list_member_id IS NOT NULL AND
1478 p_assignment_rec.project_id <> FND_API.G_MISS_NUM THEN
1479
1480 -- get the mandatory attributes of planning resource
1481 OPEN get_res_mand_attributes;
1482 FETCH get_res_mand_attributes INTO
1483 l_cur_res_format_id,
1484 l_cur_res_type_flag,
1485 l_cur_orgn_flag,
1486 l_cur_fin_cat_flag,
1487 l_cur_role_flag;
1488 CLOSE get_res_mand_attributes;
1489
1490 -- check if mandatory attributes are changed
1491 IF l_assignment_rec.resource_id <> FND_API.G_MISS_NUM THEN
1492 select person_id
1493 into l_person_id
1494 from pa_resource_txn_attributes
1495 WHERE resource_id = l_assignment_rec.resource_id;
1496 END IF;
1497 IF P_DEBUG_MODE = 'Y' THEN
1498 pa_debug.write(x_module => 'pa.plsql.PA_STAFFED_ASSIGNMENT_PVT.Update_Staffed_Assignment'
1499 ,x_msg => 'l_person_id='||l_person_id
1500 ,x_log_level => li_message_level);
1501 END IF;
1502
1503 IF (l_cur_res_type_flag = 'Y' AND
1504 l_assignment_rec.resource_id <> FND_API.G_MISS_NUM AND
1505 l_assignment_rec.resource_id <> l_cur_resource_id) OR
1506 (l_cur_res_type_flag = 'Y' AND
1507 p_assignment_rec.fcst_job_id <> FND_API.G_MISS_NUM AND
1508 p_assignment_rec.fcst_job_id <> l_cur_fcst_job_id) OR
1509 (l_cur_orgn_flag = 'Y' AND
1510 p_assignment_rec.expenditure_organization_id <> FND_API.G_MISS_NUM AND
1511 p_assignment_rec.expenditure_organization_id <> l_cur_exp_org_id) OR
1512 (l_cur_fin_cat_flag = 'Y' AND
1513 p_assignment_rec.expenditure_type <> FND_API.G_MISS_CHAR AND
1514 p_assignment_rec.expenditure_type <> l_cur_expenditure_type) OR
1515 (l_cur_role_flag = 'Y' AND
1516 p_assignment_rec.project_role_id <> FND_API.G_MISS_NUM AND
1517 p_assignment_rec.project_role_id <> l_cur_project_role_id) OR
1518 (l_cur_role_flag = 'Y' AND
1519 p_assignment_rec.assignment_name <> FND_API.G_MISS_CHAR AND
1520 p_assignment_rec.assignment_name <> l_cur_assignment_name) THEN
1521 --Log Message
1522 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
1523 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_STAFFED_ASSIGNMENT_PVT.Update_Staffed_Assignment.begin'
1524 ,x_msg => 'Mandatory attributes changed'
1525 ,x_log_level => 5);
1526 END IF;
1527 /*
1528 BEGIN
1529
1530 -- Don't need to get the default format
1531 -- because we should use the format on the planning resource
1532 -- on the team role
1533 SELECT proj_asgmt_res_format_id
1534 INTO l_proj_asgmt_res_format_id
1535 FROM PA_PROJECTS_ALL
1536 WHERE project_id = p_assignment_rec.project_id;
1537
1538
1539 SELECT person_id
1540 INTO l_person_id
1541 FROM pa_resources_denorm
1542 WHERE resource_id = p_assignment_rec.resource_id
1543 AND rownum = 1;
1544
1545 exception
1546 when others then
1547 null;
1548 end;
1549
1550 IF l_proj_asgmt_res_format_id = FND_API.G_MISS_NUM THEN
1551 l_proj_asgmt_res_format_id := NULL;
1552 END IF;
1553 */
1554 l_person_id_tmp := l_person_id;
1555 IF l_person_id_tmp = FND_API.G_MISS_NUM THEN
1556 l_person_id_tmp := NULL;
1557 END IF;
1558
1559 l_fcst_job_id_tmp := l_assignment_rec.fcst_job_id;
1560 IF l_fcst_job_id_tmp = FND_API.G_MISS_NUM THEN
1561 l_fcst_job_id_tmp := NULL;
1562 END IF;
1563
1564 l_expenditure_type_tmp := l_assignment_rec.expenditure_type;
1565 IF l_expenditure_type_tmp = FND_API.G_MISS_CHAR THEN
1566 l_expenditure_type_tmp := NULL;
1567 END IF;
1568
1569 l_project_role_id_tmp := l_assignment_rec.project_role_id;
1570 IF l_project_role_id_tmp = FND_API.G_MISS_NUM THEN
1571 l_project_role_id_tmp := NULL;
1572 END IF;
1573
1574 l_assignment_name_tmp := l_assignment_rec.assignment_name;
1575 IF l_assignment_name_tmp = FND_API.G_MISS_CHAR THEN
1576 l_assignment_name_tmp := NULL;
1577 END IF;
1578
1579 -- For Staffed Assignment only, get organization from resource before
1580 -- deriving planning resources
1581 OPEN get_resource_organization;
1582 FETCH get_resource_organization INTO l_resource_organization_id;
1583 CLOSE get_resource_organization;
1584 IF P_DEBUG_MODE = 'Y' THEN
1585 pa_debug.write(x_module => 'pa.plsql.PA_STAFFED_ASSIGNMENT_PVT.Update_Staffed_Assignment'
1586 ,x_msg => 'l_resource_organization_id='||l_resource_organization_id
1587 ,x_log_level => li_message_level);
1588 END IF;
1589
1590 -- Whenever we call Derive_resource_list_member for staffed assignment
1591 -- by passing in person_id, the job_id and person_type are required to pass in.
1592
1593 /* Start of Changes for Bug 6056112 */
1594 SELECT nvl(future_term_wf_flag,'N')
1595 INTO l_future_term_wf_flag
1596 FROM pa_resources
1597 WHERE resource_id = l_assignment_rec.resource_id;
1598
1599
1600 IF (nvl(l_future_term_wf_flag,'N') = 'Y') THEN
1601
1602 SELECT job_id
1603 INTO l_fcst_job_id_tmp
1604 FROM pa_resources_denorm prd
1605 WHERE prd.person_id = l_person_id_tmp
1606 AND l_assignment_rec.start_date BETWEEN prd.resource_effective_start_date
1607 AND prd.resource_effective_end_date ;
1608
1609 IF P_DEBUG_MODE = 'Y' THEN
1610 pa_debug.write(x_module => 'pa.plsql.PA_STAFFED_ASSIGNMENT_PVT.Update_Staffed_Assignment'
1611 ,x_msg => 'l_fcst_job_id_tmp='||l_fcst_job_id_tmp
1612 ,x_log_level => li_message_level);
1613 END IF;
1614
1615 SELECT decode(nvl(peo.employee_number,0), 0,'CWK','EMP')
1616 INTO l_person_type_code
1617 FROM per_all_people_f peo
1618 WHERE peo.person_id = l_person_id_tmp
1619 AND l_assignment_rec.start_date BETWEEN peo.effective_start_date
1620 AND peo.effective_end_date ;
1621
1622 ELSE --IF (nvl(l_future_term_wf_flag,'N') = 'Y')
1623
1624 SELECT job_id
1625 INTO l_fcst_job_id_tmp
1626 FROM per_all_assignments_f assn, pa_project_assignments pa
1627 WHERE assn.person_id = l_person_id_tmp
1628 AND pa.assignment_id = l_assignment_rec.assignment_id
1629 AND pa.start_date BETWEEN assn.effective_start_date
1630 AND assn.effective_end_date
1631 AND assn.assignment_type in ('C','E')
1632 AND assn.primary_flag = 'Y'
1633 AND ROWNUM = 1;
1634 IF P_DEBUG_MODE = 'Y' THEN
1635 pa_debug.write(x_module => 'pa.plsql.PA_STAFFED_ASSIGNMENT_PVT.Update_Staffed_Assignment'
1636 ,x_msg => 'l_fcst_job_id_tmp='||l_fcst_job_id_tmp
1637 ,x_log_level => li_message_level);
1638 END IF;
1639
1640 SELECT decode(peo.current_employee_flag, 'Y', 'EMP', 'CWK')
1641 INTO l_person_type_code
1642 FROM per_all_people_f peo, pa_project_assignments pa
1643 WHERE peo.person_id = l_person_id_tmp
1644 AND pa.assignment_id = l_assignment_rec.assignment_id
1645 AND pa.start_date BETWEEN peo.effective_start_date
1646 AND peo.effective_end_date
1647 AND ROWNUM = 1;
1648
1649 END IF ; --IF (nvl(l_future_term_wf_flag,'N') = 'Y')
1650 /* End of Changes for Bug 6056112 */
1651
1652 IF P_DEBUG_MODE = 'Y' THEN
1653 pa_debug.write(x_module => 'pa.plsql.PA_STAFFED_ASSIGNMENT_PVT.Update_Staffed_Assignment'
1654 ,x_msg => 'l_person_type_code='||l_person_type_code
1655 ,x_log_level => li_message_level);
1656
1657 pa_debug.write(x_module => 'pa.plsql.PA_STAFFED_ASSIGNMENT_PVT.Update_Staffed_Assignment'
1658 ,x_msg => 'proj_id='||p_assignment_rec.project_id||
1659 ' res_format='||l_cur_res_format_id||
1660 ' person_id='||l_person_id_tmp||
1661 ' job_id='||l_fcst_job_id_tmp
1662 ,x_log_level => li_message_level);
1663 pa_debug.write(x_module => 'pa.plsql.PA_STAFFED_ASSIGNMENT_PVT.Update_Staffed_Assignment'
1664 ,x_msg => 'org_id='||l_resource_organization_id||
1665 ' exp_type='||l_expenditure_type_tmp||
1666 ' role_id='||l_project_role_id_tmp||
1667 ' named_role='||l_assignment_name_tmp
1668 ,x_log_level => li_message_level);
1669 END IF;
1670
1671 l_assignment_rec.resource_list_member_id :=
1672 PA_PLANNING_RESOURCE_UTILS.DERIVE_RESOURCE_LIST_MEMBER (
1673 p_project_id => p_assignment_rec.project_id
1674 ,p_res_format_id => l_cur_res_format_id
1675 ,p_person_id => l_person_id_tmp
1676 ,p_job_id => l_fcst_job_id_tmp
1677 ,p_organization_id => l_resource_organization_id
1678 ,p_expenditure_type => l_expenditure_type_tmp
1679 ,p_project_role_id => l_project_role_id_tmp
1680 ,p_person_type_code => l_person_type_code
1681 ,p_named_role => l_assignment_name_tmp);
1682 --Log Message
1683 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
1684 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_STAFFED_ASSIGNMENT_PVT.Update_Staffed_Assignment.begin'
1685 ,x_msg => 'new resource list member id='||l_assignment_rec.resource_list_member_id
1686 ,x_log_level => 5);
1687 END IF;
1688
1689 IF l_assignment_rec.resource_list_member_id IS NOT NULL THEN
1690
1691 -- 1. change the resource list member in pa_resource_assignments
1692 -- on the linked task assignments in all working versions.
1693
1694 -- if original resource list member on the team role <>
1695 -- the rlm returned from the derive API above
1696 IF l_assignment_rec.resource_list_member_id <> l_cur_resource_list_member_id THEN
1697
1698 OPEN get_linked_res_asgmts;
1699 FETCH get_linked_res_asgmts
1700 BULK COLLECT INTO l_task_assignment_id_tbl,
1701 l_task_version_id_tbl,
1702 l_budget_version_id_tbl,
1703 l_struct_version_id_tbl;
1704 CLOSE get_linked_res_asgmts;
1705
1706 -- Invoke Update_Planning_Transaction API
1707 pa_assignments_pvt.Update_Task_Assignments(
1708 p_task_assignment_id_tbl => l_task_assignment_id_tbl
1709 ,p_task_version_id_tbl => l_task_version_id_tbl
1710 ,p_budget_version_id_tbl => l_budget_version_id_tbl
1711 ,p_struct_version_id_tbl => l_struct_version_id_tbl
1712 ,p_project_assignment_id => l_assignment_rec.assignment_id
1713 -- change resource list member
1714 ,p_resource_list_member_id => l_assignment_rec.resource_list_member_id
1715 /* -- pass in resource attributes
1716 ,p_resource_class_flag => l_resource_class_flag_tbl(1)
1717 ,p_resource_class_code => l_resource_class_code_tbl(1)
1718 ,p_resource_class_id => l_resource_class_id_tbl(1)
1719 ,p_res_type_code => l_res_type_code_tbl(1)
1720 ,p_incur_by_res_type => l_incur_by_res_type_tbl(1)
1721 ,p_person_id => l_person_id_tbl(1)
1722 ,p_job_id => l_job_id_tbl(1)
1723 ,p_person_type_code => l_person_type_code_tbl(1) */
1724 ,p_named_role => l_assignment_rec.assignment_name
1725 /* ,p_bom_resource_id => l_bom_resource_id_tbl(1)
1726 ,p_non_labor_resource => l_non_labor_resource_tbl(1)
1727 ,p_inventory_item_id => l_inventory_item_id_tbl(1)
1728 ,p_item_category_id => l_item_category_id_tbl(1)
1729 */ ,p_project_role_id => l_assignment_rec.project_role_id
1730 /* ,p_organization_id => l_organization_id_tbl(1)
1731 ,p_fc_res_type_code => l_fc_res_type_code_tbl(1)
1732 ,p_expenditure_type => l_expenditure_type_tbl(1)
1733 ,p_expenditure_category => l_expenditure_category_tbl(1)
1734 ,p_event_type => l_event_type_tbl(1)
1735 ,p_revenue_category_code => l_revenue_category_code_tbl(1)
1736 ,p_supplier_id => l_supplier_id_tbl(1)
1737 ,p_spread_curve_id => l_spread_curve_id_tbl(1)
1738 ,p_etc_method_code => l_etc_method_code_tbl(1)
1739 ,p_mfc_cost_type_id => l_mfc_cost_type_id_tbl(1)
1740 ,p_incurred_by_res_flag => l_incurred_by_res_flag_tbl(1)
1741 ,p_incur_by_res_class_code => l_incur_by_res_class_code_tbl(1)
1742 ,p_incur_by_role_id => l_incur_by_role_id_tbl(1)
1743 ,p_unit_of_measure => l_unit_of_measure_tbl(1)
1744 ,p_org_id => l_org_id_tbl(1)
1745 ,p_rate_based_flag => l_rate_based_flag_tbl(1)
1746 ,p_rate_expenditure_type => l_rate_expenditure_type_tbl(1)
1747 ,p_rate_func_curr_code => l_rate_func_curr_code_tbl(1)
1748 -- ,p_rate_incurred_by_org_id => l_rate_incurred_by_org_id_tbl(1) */
1749 ,x_return_status => l_return_status
1750 );
1751 --Log Message
1752 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
1753 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_STAFFED_ASSIGNMENT_PVT.Update_Staffed_Assignment'
1754 ,x_msg => 'Update_task_assignments, status='||l_return_status
1755 ,x_log_level => 5);
1756 END IF;
1757
1758 END IF;--IF l_assignment_rec.resource_list_member_id <> l_cur_resource_list_member_id THEN
1759
1760
1761 -- 2. get all unlinked task assignment using the same planning
1762 -- resource in the working version and link them to
1763 -- this team role. Also, stamp the assignment name on the
1764 -- task assignment's named_role field
1765 OPEN get_unlinked_res_asgmts;
1766 FETCH get_unlinked_res_asgmts
1767 BULK COLLECT INTO l_task_assignment_id_tbl,
1768 l_task_version_id_tbl,
1769 l_budget_version_id_tbl,
1770 l_struct_version_id_tbl;
1771 CLOSE get_unlinked_res_asgmts;
1772
1773 pa_assignments_pvt.Update_Task_Assignments(
1774 p_task_assignment_id_tbl => l_task_assignment_id_tbl
1775 ,p_task_version_id_tbl => l_task_version_id_tbl
1776 ,p_budget_version_id_tbl => l_budget_version_id_tbl
1777 ,p_struct_version_id_tbl => l_struct_version_id_tbl
1778 -- change project_assignment_id to this assignment_id
1779 ,p_project_assignment_id => l_assignment_rec.assignment_id
1780 ,p_resource_list_member_id => l_assignment_rec.resource_list_member_id
1781 -- change the named role to this assignment name
1782 ,p_named_role => l_assignment_rec.assignment_name
1783 ,p_project_role_id => l_assignment_rec.project_role_id
1784 ,x_return_status => l_return_status
1785 );
1786 --Log Message
1787 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
1788 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_STAFFED_ASSIGNMENT_PVT.Update_Staffed_Assignment'
1789 ,x_msg => 'Update_task_assignments, status='||l_return_status
1790 ,x_log_level => 5);
1791 END IF;
1792
1793 ELSE --IF l_assignment_rec.resource_list_member_id IS NOT NULL THEN
1794
1795 -- break the link between this team role and associated task assignments
1796 OPEN get_linked_res_asgmts;
1797 FETCH get_linked_res_asgmts
1798 BULK COLLECT INTO l_task_assignment_id_tbl,
1799 l_task_version_id_tbl,
1800 l_budget_version_id_tbl,
1801 l_struct_version_id_tbl;
1802 CLOSE get_linked_res_asgmts;
1803
1804 -- 1. change project_assignment_id to NULL
1805 -- 2. Don't wipe out project_role_id,
1806 -- 3. Wipe out named_role when it is not a mandatory attribute
1807 -- of planning resource
1808 IF l_cur_role_flag = 'Y' THEN
1809 pa_assignments_pvt.Update_Task_Assignments(
1810 p_task_assignment_id_tbl => l_task_assignment_id_tbl
1811 ,p_task_version_id_tbl => l_task_version_id_tbl
1812 ,p_budget_version_id_tbl => l_budget_version_id_tbl
1813 ,p_struct_version_id_tbl => l_struct_version_id_tbl
1814 ,p_project_assignment_id => -1
1815 ,x_return_status => l_return_status
1816 );
1817 ELSE
1818 pa_assignments_pvt.Update_Task_Assignments(
1819 p_task_assignment_id_tbl => l_task_assignment_id_tbl
1820 ,p_task_version_id_tbl => l_task_version_id_tbl
1821 ,p_budget_version_id_tbl => l_budget_version_id_tbl
1822 ,p_struct_version_id_tbl => l_struct_version_id_tbl
1823 ,p_project_assignment_id => -1
1824 ,p_named_role => FND_API.G_MISS_CHAR
1825 ,x_return_status => l_return_status
1826 );
1827 END IF;
1828
1829
1830 --Log Message
1831 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
1832 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_STAFFED_ASSIGNMENT_PVT.Update_Staffed_Assignment.begin'
1833 ,x_msg => 'Update_task_assignments, status='||l_return_status
1834 ,x_log_level => 5);
1835 END IF;
1836
1837 END IF; --IF l_assignment_rec.resource_list_member_id IS NOT NULL THEN
1838
1839 -- IF mandatory attributes are NOT changed
1840 ELSIF p_assignment_rec.assignment_name <> FND_API.G_MISS_CHAR AND
1841 p_assignment_rec.assignment_name <> l_cur_assignment_name THEN
1842
1843 --Log Message
1844 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
1845 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_STAFFED_ASSIGNMENT_PVT.Update_Staffed_Assignment.begin'
1846 ,x_msg => 'Mandatory attributes not changed'
1847 ,x_log_level => 5);
1848 END IF;
1849
1850 OPEN get_linked_res_asgmts;
1851 FETCH get_linked_res_asgmts
1852 BULK COLLECT INTO l_task_assignment_id_tbl,
1853 l_task_version_id_tbl,
1854 l_budget_version_id_tbl,
1855 l_struct_version_id_tbl;
1856 CLOSE get_linked_res_asgmts;
1857
1858 -- change named_role to p_assignment_rec.assignment_name
1859 pa_assignments_pvt.Update_Task_Assignments(
1860 p_task_assignment_id_tbl => l_task_assignment_id_tbl
1861 ,p_task_version_id_tbl => l_task_version_id_tbl
1862 ,p_budget_version_id_tbl => l_budget_version_id_tbl
1863 ,p_struct_version_id_tbl => l_struct_version_id_tbl
1864 ,p_named_role => l_assignment_rec.assignment_name
1865 ,p_project_role_id => l_assignment_rec.project_role_id
1866 ,x_return_status => l_return_status
1867 );
1868 --Log Message
1869 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
1870 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_STAFFED_ASSIGNMENT_PVT.Update_Staffed_Assignment.begin'
1871 ,x_msg => 'Update_task_assignments, status='||l_return_status
1872 ,x_log_level => 5);
1873 END IF;
1874
1875 END IF; -- IF mandatory attributes are changed
1876
1877 END IF; -- IF l_cur_resource_list_member_id IS NOT NULL ...
1878
1879
1880 --dbms_output.put_line('In PVT update staffed assignment');
1881
1882 -- Check that mandatory inputs for Staffed Assignment record are not null:
1883
1884 -- Check p_assignment_id IS NOT NULL
1885 IF l_assignment_rec.assignment_id IS NULL THEN
1886 PA_UTILS.Add_Message( p_app_short_name => 'PA'
1887 ,p_msg_name => 'PA_ASGN_ID_REQUIRED_FOR_ASG'
1888 ,p_token1 => 'ASGNTYPE'
1889 ,p_value1 => l_asgn_text);
1890 PA_ASSIGNMENTS_PUB.g_error_exists := FND_API.G_TRUE;
1891 END IF;
1892
1893 --
1894 --Get assignment text from message to be used as values for token
1895 --
1896 l_asgn_text := FND_MESSAGE.GET_STRING('PA','PA_ASSIGNMENT_TEXT');
1897
1898 --
1899 -- Check that mandatory project id exists
1900 --
1901 IF l_assignment_rec.project_id IS NULL THEN
1902 PA_UTILS.Add_Message( p_app_short_name => 'PA'
1903 ,p_msg_name => 'PA_PROJ_ID_REQUIRED_FOR_ASGN'
1904 ,p_token1 => 'ASGNTYPE'
1905 ,p_value1 => l_asgn_text);
1906 PA_ASSIGNMENTS_PUB.g_error_exists := FND_API.G_TRUE;
1907 END IF;
1908
1909
1910 --
1911 -- Check that mandatory assignment name exists
1912 --
1913 IF l_assignment_rec.assignment_name IS NULL THEN
1914 PA_UTILS.Add_Message( p_app_short_name => 'PA'
1915 ,p_msg_name => 'PA_ASGN_NAME_REQUIRED_FOR_ASG'
1916 ,p_token1 => 'ASGNTYPE'
1917 ,p_value1 => l_asgn_text);
1918 PA_ASSIGNMENTS_PUB.g_error_exists := FND_API.G_TRUE;
1919 END IF;
1920
1921
1922 --
1923 -- Check that mandatory project role exists
1924 --
1925 IF l_assignment_rec.project_role_id IS NULL THEN
1926 PA_UTILS.Add_Message( p_app_short_name => 'PA'
1927 ,p_msg_name => 'PA_PROJ_ROLE_REQUIRED_FOR_ASGN'
1928 ,p_token1 => 'ASGNTYPE'
1929 ,p_value1 => l_asgn_text);
1930 PA_ASSIGNMENTS_PUB.g_error_exists := FND_API.G_TRUE;
1931 END IF;
1932
1933 --
1934 --Check p_work_type_id IS NOT NULL
1935 --
1936 IF l_assignment_rec.work_type_id IS NULL THEN
1937 --dbms_output.put_line('WORK TYPE INVALID');
1938 PA_UTILS.Add_Message( p_app_short_name => 'PA'
1939 ,p_msg_name => 'PA_WORK_TYPE_REQUIRED_FOR_ASGN' );
1940 PA_ASSIGNMENTS_PUB.g_error_exists := FND_API.G_TRUE;
1941
1942 END IF;
1943
1944 -- Get the location id for the p_location_id for the given location parameters
1945 -- If the location does not already exsists, then create it
1946 IF (p_location_country_code IS NOT NULL AND p_location_country_code <> FND_API.G_MISS_CHAR) THEN
1947 PA_LOCATION_UTILS.Get_Location( p_city => p_location_city
1948 ,p_region => p_location_region
1949 ,p_country_code => p_location_country_code
1950 ,x_location_id => l_assignment_rec.location_id
1951 ,x_error_message_code => l_msg_data
1952 ,x_return_status => l_return_status );
1953
1954 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1955 PA_UTILS.Add_Message ( p_app_short_name => 'PA'
1956 ,p_msg_name => l_msg_data );
1957 PA_ASSIGNMENTS_PUB.g_error_exists := FND_API.G_TRUE;
1958 END IF;
1959 END IF;
1960
1961 /*
1962 -- commenting this out for mass update
1963 -- should not null out location id if
1964 -- l_assignment_rec.location_id = FND_API.G_MISS_NUM
1965
1966 -- for single update, if location_country_name is passed in as null
1967 -- then null out location id (in pa_assignment_pub)
1968
1969 --Bug 1795160: when user empty the location fields, the location id need to be nulled out.
1970 --If in self-service mode, and still no location id by now, then set it to NULL
1971 IF l_assignment_rec.location_id = FND_API.G_MISS_NUM AND PA_STARTUP.G_Calling_Application = 'SELF_SERVICE' THEN
1972 l_assignment_rec.location_id := NULL;
1973 END IF;
1974 */
1975
1976 --Insert into history table if changing from 'APPROVED' to 'WORKING'
1977 IF l_assignment_rec.apprvl_status_code= PA_ASSIGNMENT_APPROVAL_PUB.g_approved THEN
1978
1979 --Log Message
1980 IF P_DEBUG_MODE = 'Y' THEN
1981 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_STAFFED_ASSIGNMENT_PVT.Update_Staffed_Assignment.insert_history'
1982 ,x_msg => 'Inserting into Assignment History.'
1983 ,x_log_level => 5);
1984 END IF;
1985
1986 PA_ASSIGNMENT_APPROVAL_PVT.Insert_Into_Assignment_History ( p_assignment_id => l_assignment_rec.assignment_id
1987 ,x_change_id => l_change_id
1988 ,x_return_status => l_return_status);
1989 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1990 PA_ASSIGNMENTS_PUB.g_error_exists := FND_API.G_TRUE;
1991 END IF;
1992 l_return_status := FND_API.G_MISS_CHAR;
1993 END IF;
1994
1995
1996 --Log Message
1997 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
1998 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_STAFFED_ASSIGNMENT_PVT.Update_Staffed_Assignment.get_next_stus'
1999 ,x_msg => 'Getting next assignment approval status.'
2000 ,x_log_level => 5);
2001
2002 END IF;
2003 -- Get the status code after action performed.
2004 PA_ASSIGNMENT_APPROVAL_PVT.Get_Next_Status_After_Action ( p_action_code => PA_ASSIGNMENT_APPROVAL_PUB.g_update_action
2005 ,p_status_code => l_assignment_rec.apprvl_status_code
2006 -- ,x_status_code => l_assignment_rec.apprvl_status_code * commented for Bug: 4537865
2007 ,x_status_code => l_new_status_code -- added for Bug: 4537865
2008 ,x_return_status => l_return_status);
2009 -- added for bug: 4537865
2010
2011 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
2012 l_assignment_rec.apprvl_status_code := l_new_status_code;
2013 END IF;
2014
2015 -- added for Bug: 4537865
2016
2017 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2018 PA_ASSIGNMENTS_PUB.g_error_exists := FND_API.G_TRUE;
2019 END IF;
2020 l_return_status := FND_API.G_MISS_CHAR;
2021
2022
2023 IF (p_validate_only = FND_API.G_FALSE AND PA_ASSIGNMENTS_PUB.g_error_exists <> FND_API.G_TRUE) THEN
2024
2025 --Log Message
2026 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
2027 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_STAFFED_ASSIGNMENT_PVT.Update_Staffed_Assignment.update_row'
2028 ,x_msg => 'Update Assignment Record.'
2029 ,x_log_level => 5);
2030 END IF;
2031
2032 PA_PROJECT_ASSIGNMENTS_PKG.Update_Row
2033 (p_assignment_row_id => l_assignment_rec.assignment_row_id
2034 ,p_assignment_id => l_assignment_rec.assignment_id
2035 ,p_record_version_number => l_assignment_rec.record_version_number
2036 ,p_assignment_name => l_assignment_rec.assignment_name
2037 ,p_assignment_type => l_assignment_rec.assignment_type
2038 ,p_multiple_status_flag => l_assignment_rec.multiple_status_flag
2039 ,p_apprvl_status_code => l_assignment_rec.apprvl_status_code
2040 ,p_status_code => l_assignment_rec.status_code
2041 ,p_staffing_priority_code => l_assignment_rec.staffing_priority_code
2042 ,p_project_id => l_assignment_rec.project_id
2043 ,p_project_role_id => l_assignment_rec.project_role_id
2044 ,p_project_party_id => l_assignment_rec.project_party_id
2045 ,p_description => l_assignment_rec.description
2046 ,p_start_date => l_assignment_rec.start_date
2047 ,p_end_date => l_assignment_rec.end_date
2048 ,p_assignment_effort => l_assignment_rec.assignment_effort
2049 ,p_extension_possible => l_assignment_rec.extension_possible
2050 ,p_source_assignment_id => l_assignment_rec.source_assignment_id
2051 ,p_additional_information => l_assignment_rec.additional_information
2052 ,p_work_type_id => l_assignment_rec.work_type_id
2053 ,p_revenue_currency_code => l_assignment_rec.revenue_currency_code
2054 ,p_revenue_bill_rate => l_assignment_rec.revenue_bill_rate
2055 ,p_markup_percent => l_assignment_rec.markup_percent
2056 ,p_expense_owner => l_assignment_rec.expense_owner
2057 ,p_expense_limit => l_assignment_rec.expense_limit
2058 ,p_expense_limit_currency_code => l_assignment_rec.expense_limit_currency_code
2059 ,p_fcst_tp_amount_type => l_assignment_rec.fcst_tp_amount_type
2060 ,p_fcst_job_id => l_assignment_rec.fcst_job_id
2061 ,p_fcst_job_group_id => l_assignment_rec.fcst_job_group_id
2062 ,p_expenditure_org_id => l_assignment_rec.expenditure_org_id
2063 ,p_expenditure_organization_id => l_assignment_rec.expenditure_organization_id
2064 ,p_expenditure_type_class => l_assignment_rec.expenditure_type_class
2065 ,p_expenditure_type => l_assignment_rec.expenditure_type
2066 ,p_location_id => l_assignment_rec.location_id
2067 ,p_calendar_type => l_assignment_rec.calendar_type
2068 ,p_calendar_id => l_assignment_rec.calendar_id
2069 ,p_resource_calendar_percent => l_assignment_rec.resource_calendar_percent
2070 ,p_bill_rate_override => l_assignment_rec.bill_rate_override
2071 ,p_bill_rate_curr_override => l_assignment_rec.bill_rate_curr_override
2072 ,p_markup_percent_override => l_assignment_rec.markup_percent_override
2073 ,p_discount_percentage => l_assignment_rec.discount_percentage -- Bug 2590938
2074 ,p_rate_disc_reason_code => l_assignment_rec.rate_disc_reason_code -- Bug 2590938
2075 ,p_tp_rate_override => l_assignment_rec.tp_rate_override
2076 ,p_tp_currency_override => l_assignment_rec.tp_currency_override
2077 ,p_tp_calc_base_code_override => l_assignment_rec.tp_calc_base_code_override
2078 ,p_tp_percent_applied_override => l_assignment_rec.tp_percent_applied_override
2079 ,p_staffing_owner_person_id => l_assignment_rec.staffing_owner_person_id
2080 ,p_attribute_category => l_assignment_rec.attribute_category
2081 ,p_attribute1 => l_assignment_rec.attribute1
2082 ,p_attribute2 => l_assignment_rec.attribute2
2083 ,p_attribute3 => l_assignment_rec.attribute3
2084 ,p_attribute4 => l_assignment_rec.attribute4
2085 ,p_attribute5 => l_assignment_rec.attribute5
2086 ,p_attribute6 => l_assignment_rec.attribute6
2087 ,p_attribute7 => l_assignment_rec.attribute7
2088 ,p_attribute8 => l_assignment_rec.attribute8
2089 ,p_attribute9 => l_assignment_rec.attribute9
2090 ,p_attribute10 => l_assignment_rec.attribute10
2091 ,p_attribute11 => l_assignment_rec.attribute11
2092 ,p_attribute12 => l_assignment_rec.attribute12
2093 ,p_attribute13 => l_assignment_rec.attribute13
2094 ,p_attribute14 => l_assignment_rec.attribute14
2095 ,p_attribute15 => l_assignment_rec.attribute15
2096 ,p_resource_list_member_id => l_assignment_rec.resource_list_member_id
2097 ,x_return_status => x_return_status
2098 );
2099
2100
2101 END IF;
2102
2103
2104 -- Reset the error stack when returning to the calling program
2105 PA_DEBUG.Reset_err_stack;
2106
2107
2108 EXCEPTION
2109 WHEN OTHERS THEN
2110
2111 -- Set the excetption Message and the stack
2112 FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_STAFFED_ASSIGNMENT_PVT.Update_Staffed_Assignment'
2113 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
2114 --
2115 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2116 RAISE; -- This is optional depending on the needs
2117
2118 END Update_Staffed_Assignment;
2119
2120
2121 PROCEDURE Delete_Staffed_Assignment
2122 ( p_assignment_row_id IN ROWID --(Bug-1851096)
2123 ,p_assignment_id IN pa_project_assignments.assignment_id%TYPE := FND_API.G_MISS_NUM
2124 ,p_record_version_number IN NUMBER := FND_API.G_MISS_NUM
2125 ,p_project_party_id IN pa_project_parties.project_party_id%TYPE := FND_API.G_MISS_NUM
2126 ,p_calling_module IN VARCHAR2 := FND_API.G_MISS_CHAR
2127 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
2128 ,p_validate_only IN VARCHAR2 := FND_API.G_TRUE
2129 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2130 ) IS
2131
2132 l_return_status VARCHAR2(1);
2133 l_msg_count NUMBER;
2134 l_msg_data VARCHAR2(2000);
2135 l_start_date DATE;
2136 l_end_date DATE;
2137 l_error_message_code VARCHAR2(2000);
2138 l_project_id NUMBER;
2139 l_person_id NUMBER;
2140
2141 CURSOR get_start_end_date IS
2142 SELECT asgn.start_date, asgn.end_date, asgn.project_id, res.person_id
2143 FROM pa_project_assignments asgn,
2144 pa_resources_denorm res
2145 WHERE assignment_id = p_assignment_id
2146 AND res.resource_id = asgn.resource_id
2147 AND rownum=1;
2148
2149 BEGIN
2150
2151 -- 4537865 : Initialize the return_status
2152 x_return_status := FND_API.G_RET_STS_SUCCESS;
2153 -- Initialize the Error Stack
2154 PA_DEBUG.set_err_stack('PA_STAFFED_ASSIGNMENT_PVT.Delete_Staffed_Assignment');
2155
2156 --Log Message
2157 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
2158 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_STAFFED_ASSIGNMENT_PVT.Delete_Staffed_Assignment.begin'
2159 ,x_msg => 'Beginning of Delete_Staff_Assignment'
2160 ,x_log_level => 5);
2161 END IF;
2162 -- Assignment cannot be deleted if project transactions are associated with it
2163 OPEN get_start_end_date;
2164 FETCH get_start_end_date INTO l_start_date, l_end_date, l_project_id, l_person_id;
2165 CLOSE get_start_end_date;
2166
2167 -- Bug 2797890: Added p_project_id, p_person_id parameters
2168 PA_TRANS_UTILS.Check_Txn_Exists( p_assignment_id => p_assignment_id
2169 ,p_calling_mode => 'DELETE'
2170 ,p_project_id => l_project_id
2171 ,p_person_id => l_person_id
2172 ,p_old_start_date => null
2173 ,p_old_end_date => null
2174 ,p_new_start_date => l_start_date
2175 ,p_new_end_date => l_end_date
2176 ,x_error_message_code => l_error_message_code
2177 ,x_return_status => l_return_status);
2178
2179 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2180 PA_UTILS.Add_Message( p_app_short_name => 'PA'
2181 ,p_msg_name => l_error_message_code);
2182 PA_ASSIGNMENTS_PUB.g_error_exists := FND_API.G_TRUE;
2183
2184 END IF;
2185 l_return_status := NULL;
2186
2187 IF (p_validate_only = FND_API.G_FALSE AND PA_ASSIGNMENTS_PUB.g_error_exists <> FND_API.G_TRUE) THEN
2188
2189 --Log Message
2190 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
2191 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_STAFFED_ASSIGNMENT_PVT.Delete_Staffed_Assignment.del_schedules'
2192 ,x_msg => 'Deleting Assignment Schedules'
2193 ,x_log_level => 5);
2194 END IF;
2195
2196 --
2197 -- Delete all the child shedule records before deleting the parent staff assignment record
2198 --
2199
2200 PA_SCHEDULE_PVT.Delete_Asgn_Schedules
2201 ( p_assignment_id => p_assignment_id
2202 ,p_perm_delete => FND_API.G_TRUE --Added for bug 4389372
2203 ,x_return_status => l_return_status
2204 ,x_msg_count => l_msg_count
2205 ,x_msg_data => l_msg_data
2206 );
2207
2208 --Log Message
2209 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
2210 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_STAFFED_ASSIGNMENT_PVT.Delete_Staffed_Assignment.del_asgmt'
2211 ,x_msg => 'Deleting Assignment record.'
2212 ,x_log_level => 5);
2213 END IF;
2214
2215 -- Delete the master record
2216 PA_PROJECT_ASSIGNMENTS_PKG.Delete_Row
2217 ( p_assignment_row_id => p_assignment_row_id
2218 ,p_assignment_id => p_assignment_id
2219 ,p_record_version_number => p_record_version_number
2220 ,x_return_status => x_return_status
2221 );
2222
2223 --Delete Project Party
2224 IF x_return_status = 'S'AND p_project_party_id IS NOT NULL THEN
2225
2226 --Log Message
2227 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
2228 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_STAFFED_ASSIGNMENT_PVT.Delete_Staffed_Assignment.proj_party.'
2229 ,x_msg => 'Deleting Project Party'
2230 ,x_log_level => 5);
2231 END IF;
2232
2233 PA_PROJECT_PARTIES_PVT.Delete_Project_Party(
2234 p_commit => 'F',
2235 p_validate_only => 'F',
2236 p_project_party_id => p_project_party_id,
2237 p_calling_module => 'ASSIGNMENT',
2238 p_record_version_number => null,
2239 x_return_status => x_return_status,
2240 x_msg_count => l_msg_count,
2241 x_msg_data => l_msg_data);
2242 END IF;
2243
2244
2245 --Log Message
2246 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
2247 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_STAFFED_ASSIGNMENT_PVT.Delete_Staffed_Assignment.del_history'
2248 ,x_msg => 'Deleting Assignment History.'
2249 ,x_log_level => 5);
2250 END IF;
2251
2252 --
2253 --Delete any related record in history table and any related record in wf table
2254 --
2255 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
2256 PA_ASSIGNMENTS_HISTORY_PKG.Delete_By_Assignment ( p_assignment_id =>p_assignment_id
2257 ,x_return_status => x_return_status);
2258
2259 PA_ASGMT_WFSTD.Delete_Assignment_WF_Records (p_assignment_id => p_assignment_id,
2260 p_project_id => l_project_id);
2261 END IF;
2262
2263
2264 -- Reset the error stack when returning to the calling program
2265 PA_DEBUG.Reset_err_stack;
2266
2267 END IF;
2268
2269 EXCEPTION
2270 WHEN OTHERS THEN
2271
2272 -- Set the excetption Message and the stack
2273 FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_STAFFED_ASSIGNMENT_PVT.Delete_Staffed_Assignment'
2274 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
2275 --
2276 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2277 RAISE; -- This is optional depending on the needs
2278
2279 END Delete_Staffed_Assignment;
2280
2281 --
2282 --
2283 END pa_staffed_assignment_pvt;