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