[Home] [Help]
PACKAGE BODY: APPS.PA_OPEN_ASSIGNMENT_PVT
Source
1 PACKAGE BODY pa_open_assignment_pvt AS
2 /*$Header: PAROPVTB.pls 120.3 2007/02/06 09:53:16 dthakker ship $*/
3 --
4 P_DEBUG_MODE varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
5 li_message_level NUMBER := 1;
6 PROCEDURE Create_Open_Assignment
7 ( p_assignment_rec IN PA_ASSIGNMENTS_PUB.Assignment_Rec_Type
8 ,p_asgn_creation_mode IN VARCHAR2
9 ,p_location_city IN pa_locations.city%TYPE := FND_API.G_MISS_CHAR
10 ,p_location_region IN pa_locations.region%TYPE := FND_API.G_MISS_CHAR
11 ,p_location_country_code IN pa_locations.country_code%TYPE := FND_API.G_MISS_CHAR
12 ,p_adv_action_set_id IN NUMBER := FND_API.G_MISS_NUM
13 ,p_start_adv_action_set_flag IN VARCHAR2 := FND_API.G_MISS_CHAR
14 ,p_sum_tasks_flag IN VARCHAR2 := FND_API.G_FALSE -- FP.M Development
15 ,p_budget_version_id IN pa_resource_assignments.budget_version_id%TYPE := FND_API.G_MISS_NUM
16 ,p_number_of_requirements IN NUMBER := 1
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_number OUT NOCOPY pa_project_assignments.assignment_number%TYPE --File.Sql.39 bug 4440895
21 ,x_assignment_row_id OUT NOCOPY ROWID --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_assignment_id pa_project_assignments.assignment_id%TYPE;
28 l_def_assignment_name pa_project_assignments.assignment_name%TYPE;
29 l_def_min_resource_job_level pa_project_assignments.min_resource_job_level%TYPE;
30 l_def_max_resource_job_level pa_project_assignments.max_resource_job_level%TYPE;
31 l_source_assignment_id pa_project_assignments.source_assignment_id%TYPE;
32 l_source_calendar_type pa_project_assignments.calendar_type%TYPE;
33 l_source_status_code pa_project_assignments.status_code%TYPE;
34 l_menu_id NUMBER;
35 l_return_status VARCHAR2(1);
36 l_competencies_tbl PA_HR_COMPETENCE_UTILS.Competency_Tbl_Typ;
37 l_job_id NUMBER;
38 l_error_message_code fnd_new_messages.message_name%TYPE;
39 l_schedulable_flag VARCHAR2(1);
40 l_location_row_id ROWID;
41 l_no_of_competencies NUMBER;
42 l_task_id NUMBER;
43 l_task_percentage NUMBER;
44 l_msg_count NUMBER;
45 l_msg_data VARCHAR2(2000);
46
47 l_element_rowid ROWID;
48 l_element_id NUMBER;
49 l_element_return_status VARCHAR2(1);
50 l_req_text FND_NEW_MESSAGES.message_text%TYPE;
51 l_calendar_id NUMBER;
52 l_work_type_id NUMBER;
53 l_raw_revenue NUMBER;
54
55 l_comp_match_weighting pa_project_assignments.competence_match_weighting%TYPE;
56 l_avail_match_weighting pa_project_assignments.availability_match_weighting%TYPE;
57 l_job_level_match_weighting pa_project_assignments.job_level_match_weighting%TYPE;
58 l_search_min_availability pa_project_assignments.search_min_availability%TYPE;
59 l_search_exp_org_struct_ver_id pa_project_assignments.search_exp_org_struct_ver_id%TYPE;
60 l_search_exp_start_org_id pa_project_assignments.search_exp_start_org_id%TYPE;
61 l_search_country_code pa_project_assignments.search_country_code%TYPE;
62 l_search_min_candidate_score pa_project_assignments.search_min_candidate_score%TYPE;
63 l_starting_status_code pa_action_sets.status_code%TYPE;
64 l_new_action_set_id NUMBER;
65 l_adv_action_set_id NUMBER;
66 l_start_adv_action_set_flag VARCHAR2(1);
67 l_task_assignment_id_tbl system.pa_num_tbl_type;
68 l_proj_req_res_format_id NUMBER;
69 l_project_assignment_id_tbl system.pa_num_tbl_type := system.pa_num_tbl_type();
70 l_budget_version_id_tbl system.pa_num_tbl_type := system.pa_num_tbl_type();
71 l_struct_version_id_tbl system.pa_num_tbl_type := system.pa_num_tbl_type();
72 l_last_bvid NUMBER;
73 l_update_task_asgmt_id_tbl system.pa_num_tbl_type := system.pa_num_tbl_type();
74 l_update_count NUMBER;
75 l_last_struct_version_id NUMBER;
76 l_task_version_id_tbl system.pa_num_tbl_type := system.pa_num_tbl_type();
77 l_update_task_version_id_tbl system.pa_num_tbl_type := system.pa_num_tbl_type();
78
79 l_fcst_job_id_tmp pa_project_assignments.fcst_job_id%TYPE;
80 l_expenditure_org_id_tmp pa_project_assignments.expenditure_organization_id%TYPE;
81 l_expenditure_type_tmp pa_project_assignments.expenditure_type%TYPE;
82 l_project_role_id_tmp pa_project_assignments.project_role_id%TYPE;
83 l_assignment_name_tmp pa_project_assignments.assignment_name%TYPE;
84
85 l_fcst_tp_amount_type_tmp pa_project_assignments.fcst_tp_amount_type%TYPE;
86 l_fcst_job_group_id_tmp pa_project_assignments.fcst_job_group_id%TYPE;
87 l_exp_org_id_tmp pa_project_assignments.expenditure_org_id%TYPE;
88 l_expenditure_type_class_tmp pa_project_assignments.expenditure_type_class%TYPE;
89 l_enable_auto_cand_nom_flag pa_project_assignments.enable_auto_cand_nom_flag%TYPE;
90
91 CURSOR get_project_info IS
92 SELECT calendar_id, competence_match_wt, availability_match_wt, job_level_match_wt, search_min_availability, search_org_hier_id, search_starting_org_id, search_country_code, min_cand_score_reqd_for_nom, adv_action_set_id, start_adv_action_set_flag,
93 enable_automated_search -- Added for bug 4306049
94 FROM pa_projects_all
95 WHERE project_id = l_assignment_rec.project_id;
96
97 -- Bottom Up Flow
98 CURSOR get_bu_resource_assignments IS
99 SELECT ra.resource_assignment_id, ra.wbs_element_version_id, bv.budget_version_id, bv.project_structure_version_id
100 FROM PA_RESOURCE_ASSIGNMENTS ra
101 ,PA_BUDGET_VERSIONS bv
102 ,PA_PROJ_ELEM_VER_STRUCTURE evs
103 WHERE ra.project_id = bv.project_id
104 AND bv.project_id = evs.project_id
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 ra.budget_version_id = bv.budget_version_id
126 AND bv.project_structure_version_id = evs.element_version_id
127 AND ra.project_id = l_assignment_rec.project_id
128 AND ra.resource_list_member_id = l_assignment_rec.resource_list_member_id
129 AND ra.project_assignment_id = -1
130 AND evs.status_code = 'STRUCTURE_WORKING')
131 UNION ALL
132 (SELECT ra.resource_assignment_id, ra.wbs_element_version_id, bv.budget_version_id, bv.project_structure_version_id
133 FROM PA_RESOURCE_ASSIGNMENTS ra
134 ,PA_BUDGET_VERSIONS bv
135 ,PA_PROJ_ELEM_VER_STRUCTURE evs
136 ,PA_PROJ_WORKPLAN_ATTR pwa
137 WHERE pwa.wp_enable_Version_flag = 'N'
138 AND pwa.project_id = ra.project_id
139 AND pwa.proj_element_id = evs.proj_element_id
140 AND ra.project_id = bv.project_id
141 AND bv.project_id = evs.project_id
142 AND ra.budget_version_id = bv.budget_version_id
143 AND bv.project_structure_version_id = evs.element_version_id
144 AND ra.resource_list_member_id = l_assignment_rec.resource_list_member_id
145 AND ra.project_id = l_assignment_rec.project_id
146 AND ra.project_assignment_id = -1)
147 )
148 ORDER BY budget_version_id, project_structure_version_id;
149
150 BEGIN
151
152 --dbms_output.put_line('PA_OPEN_ASSIGNMENT_PVT.Create_Open_Assignment');
153 -- Initialize the Error Stack
154 PA_DEBUG.set_err_stack('PA_OPEN_ASSIGNMENT_PVT.Create_Open_Assignment');
155
156 --Log Message
157 IF (P_DEBUG_MODE = 'Y') THEN
158 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_OPEN_ASSIGNMENT_PVT.Create_Open_Assignment.begin'
159 ,x_msg => 'Beginning of Create_Open_Assignment'
160 ,x_log_level => li_message_level);
161
162 END IF;
163
164 -- Initialize the return status to success
165 x_return_status := FND_API.G_RET_STS_SUCCESS;
166
167 -- Assign the input record to the local variable
168 l_assignment_rec := p_assignment_rec;
169
170 --The following is done when a team role is being copied to create a new
171 --requirement:
172 --Store the source assignment id in a local variable.
173 --If we are COPYING a team role then null out the source assignment id in the
174 --l_assignment_rec so that it won't be inserted to the db
175 --as part of the new assignment record.
176 --In the case of copying a team role, we need the source assignment id to create the subteam parties,
177 --but we do not want to keep the link between the new requirement and the
178 --team role it was copied from b/c then we shouldn't allow the team role it
179 --was copied from to be deleted.
180 --Confirmed with anchen that not keeping the link is OK.
181 --Also get the default starting requirement status which will be
182 --used as the status of the new requirement - NOT WHEN CREATING A REQUIREMENT FROM A TEMPLATE REQUIREMENT
183 --in that case use the status from the template requirement.
184
185 l_source_assignment_id := l_assignment_rec.source_assignment_id;
186
187 IF p_asgn_creation_mode = 'COPY' AND (l_assignment_rec.assignment_template_id IS NULL OR l_assignment_rec.assignment_template_id = FND_API.G_MISS_NUM) THEN
188
189 l_assignment_rec.source_assignment_id := NULL;
190
191 --get the default starting requirement status.
192
193 l_source_status_code := l_assignment_rec.status_code;
194
195 FND_PROFILE.Get('PA_START_OPEN_ASGMT_STATUS',l_assignment_rec.status_code);
196
197 IF l_assignment_rec.status_code IS NULL THEN
198
199 PA_ASSIGNMENT_UTILS.Add_Message ( p_app_short_name => 'PA'
200 ,p_msg_name => 'PA_START_STATUS_NOT_DEFINED');
201 PA_ASSIGNMENTS_PUB.g_error_exists := FND_API.G_TRUE;
202
203 END IF;
204
205 END IF; --asgn creation mode is copy.
206
207 --
208 --Get assignment text from message to be used as values for token
209 --
210 l_req_text := FND_MESSAGE.GET_STRING('PA','PA_REQUIREMENT_TEXT');
211
212 --
213 -- Check that mandatory project id exists if this is not a template requirement.
214 --
215 IF (p_assignment_rec.project_id IS NULL OR p_assignment_rec.project_id = FND_API.G_MISS_NUM) AND
216 (p_assignment_rec.assignment_template_id IS NULL OR p_assignment_rec.assignment_template_id = FND_API.G_MISS_NUM) THEN
217 PA_ASSIGNMENT_UTILS.Add_Message( p_app_short_name => 'PA'
218 ,p_msg_name => 'PA_PROJ_ID_REQUIRED_FOR_ASGN'
219 ,p_token1 => 'ASGNTYPE'
220 ,p_value1 => l_req_text);
221 PA_ASSIGNMENTS_PUB.g_error_exists := FND_API.G_TRUE;
222 END IF;
223
224 --
225 -- Check that mandatory assignment name exists
226 --
227 IF p_assignment_rec.assignment_name IS NULL OR
228 p_assignment_rec.assignment_name = FND_API.G_MISS_CHAR THEN
229 PA_ASSIGNMENT_UTILS.Add_Message( p_app_short_name => 'PA'
230 ,p_msg_name => 'PA_NAME_REQUIRED_FOR_ASGN');
231 PA_ASSIGNMENTS_PUB.g_error_exists := FND_API.G_TRUE;
232 END IF;
233
234 --
235 -- Check valid starting status
236 --
237 IF (PA_PROJECT_STUS_UTILS.Is_Starting_Status( x_project_status_code => l_assignment_rec.status_code)) = 'N' THEN
238 PA_ASSIGNMENT_UTILS.Add_Message( p_app_short_name => 'PA'
239 ,p_msg_name => 'PA_INVALID_ASGN_STARTING_STUS'
240 ,p_token1 => 'ASGNTYPE'
241 ,p_value1 => l_req_text);
242 PA_ASSIGNMENTS_PUB.g_error_exists := FND_API.G_TRUE;
243 END IF;
244
245 --
246 -- Check that mandatory project role exists
247 --
248 IF p_assignment_rec.project_role_id IS NULL
249 OR p_assignment_rec.project_role_id = FND_API.G_MISS_NUM THEN
250 PA_ASSIGNMENT_UTILS.Add_Message( p_app_short_name => 'PA'
251 ,p_msg_name => 'PA_PROJ_ROLE_REQUIRED_FOR_ASGN'
252 ,p_token1 => 'ASGNTYPE'
253 ,p_value1 => l_req_text);
254 PA_ASSIGNMENTS_PUB.g_error_exists := FND_API.G_TRUE;
255 END IF;
256
257 --if the new requirement is being copied from a requirement or a template
258 --requirement then get the competencies from the source requirement
259 --otherwise get the competencies from the role.
260 -- Bug 2401916: ADDED COMPETENCE IN THE BASE REQ. IS NOT GETTING COPIED
261 IF l_source_assignment_id IS NOT NULL
262 AND l_source_assignment_id <> FND_API.G_MISS_NUM
263 AND l_assignment_rec.source_assignment_type = 'OPEN_ASSIGNMENT' THEN
264
265 PA_HR_COMPETENCE_UTILS.get_competencies(p_object_name => 'OPEN_ASSIGNMENT',
266 p_object_id => l_source_assignment_id,
267 x_competency_tbl => l_competencies_tbl,
268 x_no_of_competencies => l_no_of_competencies,
269 x_error_message_code => l_error_message_code,
270 x_return_status => l_return_status);
271
272 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
273 PA_ASSIGNMENT_UTILS.Add_Message ( p_app_short_name => 'PA'
274 ,p_msg_name => l_error_message_code );
275 PA_ASSIGNMENTS_PUB.g_error_exists := FND_API.G_TRUE;
276 END IF;
277
278 ELSE
279
280 --
281 -- Get role default values
282 --
283 PA_ROLE_UTILS.Get_Role_Defaults( p_role_id => p_assignment_rec.project_role_id
284 ,x_meaning => l_def_assignment_name
285 ,x_default_min_job_level => l_def_min_resource_job_level
286 ,x_default_max_job_level => l_def_max_resource_job_level
287 ,x_menu_id => l_menu_id
288 ,x_schedulable_flag => l_schedulable_flag
289 ,x_default_job_id => l_fcst_job_id_tmp
290 ,x_def_competencies => l_competencies_tbl
291 ,x_return_status => l_return_status
292 ,x_error_message_code => l_error_message_code );
293
294 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
295
296 PA_ASSIGNMENT_UTILS.Add_Message ( p_app_short_name => 'PA'
297 ,p_msg_name => l_error_message_code );
298 PA_ASSIGNMENTS_PUB.g_error_exists := FND_API.G_TRUE;
299 END IF;
300
301 IF l_schedulable_flag <> 'Y' THEN
302
303 PA_ASSIGNMENT_UTILS.Add_Message ( p_app_short_name => 'PA'
304 ,p_msg_name => 'PA_ROLE_NOT_SCHEDULABLE' );
305 PA_ASSIGNMENTS_PUB.g_error_exists := FND_API.G_TRUE;
306 return;
307 END IF;
308
309 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
310 IF l_assignment_rec.min_resource_job_level IS NULL
311 OR l_assignment_rec.min_resource_job_level = FND_API.G_MISS_NUM THEN
312 l_assignment_rec.min_resource_job_level := l_def_min_resource_job_level;
313 END IF;
314 --
315 IF l_assignment_rec.max_resource_job_level IS NULL
316 OR l_assignment_rec.max_resource_job_level = FND_API.G_MISS_NUM THEN
317 l_assignment_rec.max_resource_job_level := l_def_max_resource_job_level;
318 END IF;
319
320 -- 5130421 : It is possible to null out the job so remove the null check
321 IF --l_assignment_rec.fcst_job_id IS NULL
322 l_assignment_rec.fcst_job_id = FND_API.G_MISS_NUM THEN
323 l_assignment_rec.fcst_job_id := l_fcst_job_id_tmp;
324 END IF;
325 END IF;
326 END IF;
327
328 --Get utilization defaults before creating requirement/assignment
329 --IF it has not been defaulted already OR
330 --IF it is copying from an assignment into a requirement AND
331 --IF IT IS NOT a template requirement.
332
333 IF (((l_assignment_rec.expenditure_type IS NULL) OR
334 (l_assignment_rec.expenditure_type = FND_API.G_MISS_CHAR) OR
335 (p_asgn_creation_mode <> 'COPY') OR
336 (p_asgn_creation_mode = 'COPY' AND l_assignment_rec.source_assignment_type <> 'OPEN_ASSIGNMENT')) AND
337 (l_assignment_rec.project_id IS NOT NULL AND l_assignment_rec.project_id <> FND_API.G_MISS_NUM))THEN
338
339 --dbms_output.put_line('calling assignment default');
340 --Log Message
341 IF (P_DEBUG_MODE = 'Y') THEN
342 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_OPEN_ASSIGNMENT_PVT.Create_Open_Assignment'
343 ,x_msg => 'Getting Utilization Defaults.'
344 ,x_log_level => li_message_level);
345 END IF;
346
347 PA_FORECAST_ITEMS_UTILS.Get_Assignment_Default(
348 p_assignment_type => l_assignment_rec.assignment_type,
349 p_project_id => l_assignment_rec.project_id,
350 p_project_role_id => l_assignment_rec.project_role_id,
351 p_work_type_id => l_assignment_rec.work_type_id,
352 x_work_type_id => l_work_type_id,
353 x_default_tp_amount_type => l_fcst_tp_amount_type_tmp,
354 x_default_job_group_id => l_fcst_job_group_id_tmp,
355 x_default_job_id => l_fcst_job_id_tmp,
356 x_org_id => l_exp_org_id_tmp,
357 x_carrying_out_organization_id=> l_expenditure_org_id_tmp,
358 x_default_assign_exp_type => l_expenditure_type_tmp,
359 x_default_assign_exp_type_cls => l_expenditure_type_class_tmp,
360 x_return_status => l_return_status,
361 x_msg_count => l_msg_count,
362 x_msg_data => l_msg_data
363 );
364 -- Bug 5130421
365 -- fcst tp amount type shd get default from work type
366 -- it was getting default from the default work type
367 IF l_assignment_rec.work_type_id IS NOT NULL AND l_assignment_rec.work_type_id <> FND_API.G_MISS_NUM THEN
368 Pa_Fp_Org_Fcst_Utils.Get_Tp_Amount_Type(
369 p_project_id => l_assignment_rec.project_id,
370 p_work_type_id => l_assignment_rec.work_type_id,
371 x_tp_amount_type => l_fcst_tp_amount_type_tmp,
372 x_return_status => l_return_status,
373 x_msg_count => l_msg_count,
374 x_msg_data => l_msg_data);
375 IF (l_assignment_rec.fcst_tp_amount_type IS NULL OR
376 l_assignment_rec.fcst_tp_amount_type = FND_API.G_MISS_CHAR) THEN
377 l_assignment_rec.fcst_tp_amount_type := l_fcst_tp_amount_type_tmp;
378 END IF;
379 ELSE
380 IF (l_assignment_rec.fcst_tp_amount_type IS NULL OR
381 l_assignment_rec.fcst_tp_amount_type = FND_API.G_MISS_CHAR) THEN
382 l_assignment_rec.fcst_tp_amount_type := l_fcst_tp_amount_type_tmp;
383 END IF;
384 END IF;
385 -- 5130421 : It is possible to null out the job and job group so remove the null check
386 IF (--l_assignment_rec.fcst_job_group_id IS NULL OR
387 l_assignment_rec.fcst_job_group_id = FND_API.G_MISS_NUM) THEN
388 l_assignment_rec.fcst_job_group_id := l_fcst_job_group_id_tmp;
389 END IF;
390 IF (--l_assignment_rec.fcst_job_id IS NULL OR
391 l_assignment_rec.fcst_job_id = FND_API.G_MISS_NUM) THEN
392 l_assignment_rec.fcst_job_id := l_fcst_job_id_tmp;
393 END IF;
394 IF (l_assignment_rec.expenditure_org_id IS NULL OR
395 l_assignment_rec.expenditure_org_id = FND_API.G_MISS_NUM) THEN
396 l_assignment_rec.expenditure_org_id := l_exp_org_id_tmp;
397 END IF;
398 IF (l_assignment_rec.expenditure_organization_id IS NULL OR
399 l_assignment_rec.expenditure_organization_id = FND_API.G_MISS_NUM) THEN
400 l_assignment_rec.expenditure_organization_id := l_expenditure_org_id_tmp;
401 END IF;
402 IF (l_assignment_rec.expenditure_type IS NULL OR
403 l_assignment_rec.expenditure_type = FND_API.G_MISS_CHAR) THEN
404 l_assignment_rec.expenditure_type := l_expenditure_type_tmp;
405 END IF;
406 IF (l_assignment_rec.expenditure_type_class IS NULL OR
407 l_assignment_rec.expenditure_type_class = FND_API.G_MISS_CHAR) THEN
408 l_assignment_rec.expenditure_type_class := l_expenditure_type_class_tmp;
409 END IF;
410
411 --dbms_output.put_line('after assignment default:'|| l_return_status);
412 --IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
413 -- PA_ASSIGNMENTS_PUB.g_error_exists := FND_API.G_TRUE;
414 --ELSE
415
416 END IF;
417
418 --
419 -- Get bill rate and bill rate currency code, and markup percent
420 -- Call this API only if the required parameters are present.
421 -- Note: expenditure_org_id(OU) can be NULL in the case of single org
422 IF l_assignment_rec.fcst_job_id IS NOT NULL AND l_assignment_rec.fcst_job_id <> FND_API.G_MISS_NUM AND
423 l_assignment_rec.fcst_job_group_id IS NOT NULL AND l_assignment_rec.fcst_job_group_id <> FND_API.G_MISS_NUM AND
424 l_assignment_rec.expenditure_organization_id IS NOT NULL AND l_assignment_rec.expenditure_organization_id <> FND_API.G_MISS_NUM THEN
425
426 --Log Message
427 IF (P_DEBUG_MODE = 'Y') THEN
428 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_OPEN_ASSIGNMENT_PVT.Create_Open_Assignment'
429 ,x_msg => 'Getting Revenue Bill Rate'
430 ,x_log_level => li_message_level);
431
432 END IF;
433
434 PA_FORECAST_REVENUE.Get_Rev_Amt(
435 p_project_id => l_assignment_rec.project_id
436 ,p_quantity => 0
437 ,p_person_id => NULL
438 ,p_item_date => l_assignment_rec.start_date
439 ,p_forecast_job_id => l_assignment_rec.fcst_job_id
440 ,p_forecast_job_group_id => l_assignment_rec.fcst_job_group_id
441 ,p_expenditure_org_id => l_assignment_rec.expenditure_org_id
442 ,p_expenditure_organization_id => l_assignment_rec.expenditure_organization_id
443 ,p_check_error_flag => 'N'
444 ,x_bill_rate => l_assignment_rec.revenue_bill_rate
445 ,x_raw_revenue => l_raw_revenue
446 ,x_rev_currency_code => l_assignment_rec.revenue_currency_code
447 ,x_markup_percentage => l_assignment_rec.markup_percent
448 ,x_return_status => l_return_status
449 ,x_msg_count => l_msg_count
450 ,x_msg_data => l_msg_data);
451
452 END IF; -- if required parameters are present
453 --END IF; -- if get_assignment_default returns success
454 --END IF;
455
456 -- FP.M Development
457 IF P_DEBUG_MODE = 'Y' THEN
458 pa_debug.write(x_module => 'pa.plsql.PA_OPEN_ASSIGNMENT_PVT.Create_Open_Assignment'
459 ,x_msg => 'FP.M Development'
460 ,x_log_level => li_message_level);
461 END IF;
462
463 IF (p_assignment_rec.resource_list_member_id = FND_API.G_MISS_NUM OR
464 p_assignment_rec.resource_list_member_id IS NULL) AND
465 p_assignment_rec.project_id <> FND_API.G_MISS_NUM THEN
466 SELECT proj_req_res_format_id
467 INTO l_proj_req_res_format_id
468 FROM PA_PROJECTS_ALL
469 WHERE project_id = p_assignment_rec.project_id;
470
471 l_fcst_job_id_tmp := l_assignment_rec.fcst_job_id;
472 IF l_fcst_job_id_tmp = FND_API.G_MISS_NUM THEN
473 l_fcst_job_id_tmp := NULL;
474 END IF;
475
476 l_expenditure_org_id_tmp := l_assignment_rec.expenditure_organization_id;
477 IF l_expenditure_org_id_tmp = FND_API.G_MISS_NUM THEN
478 l_expenditure_org_id_tmp := NULL;
479 END IF;
480
481 l_expenditure_type_tmp := l_assignment_rec.expenditure_type;
482 IF l_expenditure_type_tmp = FND_API.G_MISS_CHAR THEN
483 l_expenditure_type_tmp := NULL;
484 END IF;
485
486 l_project_role_id_tmp := l_assignment_rec.project_role_id;
487 IF l_project_role_id_tmp = FND_API.G_MISS_NUM THEN
488 l_project_role_id_tmp := NULL;
489 END IF;
490
491 l_assignment_name_tmp := l_assignment_rec.assignment_name;
492 IF l_assignment_name_tmp = FND_API.G_MISS_CHAR THEN
493 l_assignment_name_tmp := NULL;
494 END IF;
495
496 IF P_DEBUG_MODE = 'Y' THEN
497 pa_debug.write(x_module => 'pa.plsql.PA_OPEN_ASSIGNMENT_PVT.Create_Open_Assignment'
498 ,x_msg => 'proj_id='||p_assignment_rec.project_id||
499 ' res_format='||l_proj_req_res_format_id||
500 ' job_id='||l_fcst_job_id_tmp
501 ,x_log_level => li_message_level);
502 pa_debug.write(x_module => 'pa.plsql.PA_OPEN_ASSIGNMENT_PVT.Create_Open_Assignment'
503 ,x_msg => 'org_id='||l_expenditure_org_id_tmp||
504 ' exp_type='||l_expenditure_type_tmp||
505 ' role_id='||l_project_role_id_tmp||
506 ' named_role='||l_assignment_name_tmp
507 ,x_log_level => li_message_level);
508 END IF;
509
510 l_assignment_rec.resource_list_member_id :=
511 PA_PLANNING_RESOURCE_UTILS.DERIVE_RESOURCE_LIST_MEMBER (
512 p_project_id => p_assignment_rec.project_id
513 ,p_res_format_id => l_proj_req_res_format_id
514 ,p_job_id => l_fcst_job_id_tmp
515 ,p_organization_id => l_expenditure_org_id_tmp
516 ,p_expenditure_type => l_expenditure_type_tmp
517 ,p_project_role_id => l_project_role_id_tmp
518 ,p_named_role => l_assignment_name_tmp);
519 IF P_DEBUG_MODE = 'Y' THEN
520 pa_debug.write(x_module => 'pa.plsql.PA_OPEN_ASSIGNMENT_PVT.Create_Open_Assignment'
521 ,x_msg => 'resource_list_member_id='||l_assignment_rec.resource_list_member_id
522 ,x_log_level => li_message_level);
523 END IF;
524
525 END IF;
526
527 --
528 --Check p_work_type_id IS NOT NULL
529 --
530 IF l_assignment_rec.work_type_id IS NULL
531 OR l_assignment_rec.work_type_id = FND_API.G_MISS_NUM
532 OR l_assignment_rec.work_type_id = 0 THEN
533 --dbms_output.put_line('WORK TYPE INVALID');
534 PA_ASSIGNMENT_UTILS.Add_Message( p_app_short_name => 'PA'
535 ,p_msg_name => 'PA_WORK_TYPE_REQUIRED_FOR_ASGN' );
536 PA_ASSIGNMENTS_PUB.g_error_exists := FND_API.G_TRUE;
537
538 ELSIF l_assignment_rec.project_id IS NOT NULL AND l_assignment_rec.project_id <> FND_API.G_MISS_NUM THEN
539 --
540 --check for indirect project, only non-billable work types if this is NOT
541 --a template requirement
542 PA_WORK_TYPE_UTILS.CHECK_WORK_TYPE (
543 P_WORK_TYPE_ID => l_assignment_rec.work_type_id
544 ,P_PROJECT_ID => l_assignment_rec.project_id
545 ,P_TASK_ID => NULL
546 ,X_RETURN_STATUS => l_return_status
547 ,X_ERROR_MESSAGE_CODE => l_error_message_code);
548 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
549 PA_ASSIGNMENT_UTILS.Add_Message( p_app_short_name => 'PA'
550 ,p_msg_name => l_error_message_code );
551 PA_ASSIGNMENTS_PUB.g_error_exists := FND_API.G_TRUE;
552 END IF;
553
554 END IF;
555
556 --
557 -- For Open Assignment multiple status flag should be set to 'N'
558 --
559 l_assignment_rec.multiple_status_flag := 'N';
560
561 -- Rules:
562 -- If this is NOT a template requirement AND
563 -- If all the location input parameters are null then default location from project
564 -- If location details are passed then get the location id for the for the given location parameters
565 -- If the location does not already exist then create it
566 --
567 IF (l_assignment_rec.location_id IS NULL OR l_assignment_rec.location_id = FND_API.G_MISS_NUM)
568 AND (p_location_city IS NULL OR p_location_city = FND_API.G_MISS_CHAR)
569 AND (p_location_region IS NULL OR p_location_region = FND_API.G_MISS_CHAR)
570 AND (p_location_country_code IS NULL OR p_location_country_code = FND_API.G_MISS_CHAR)
571 AND (l_assignment_rec.project_id IS NOT NULL and l_assignment_rec.project_id <> FND_API.G_MISS_NUM)
572 THEN
573
574 --Bug 1795160: no need to get location from project, location not required
575 /*
576 SELECT location_id
577 INTO l_assignment_rec.location_id
578 FROM pa_projects_all
579 WHERE project_id = l_assignment_rec.project_id;
580 */
581 --Log Message
582 IF (P_DEBUG_MODE = 'Y') THEN
583 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_OPEN_ASSIGNMENT_PVT.Create_Open_Assignment.location'
584 ,x_msg => 'No need to get location from project'
585 ,x_log_level => li_message_level);
586 END IF;
587
588
589
590 --only call get_location if location_id IS NULL
591 ELSIF l_assignment_rec.location_id IS NULL OR l_assignment_rec.location_id = FND_API.G_MISS_NUM THEN
592
593 PA_LOCATION_UTILS.Get_Location( p_city => p_location_city
594 ,p_region => p_location_region
595 ,p_country_code => p_location_country_code
596 ,x_location_id => l_assignment_rec.location_id
597 ,x_error_message_code => l_error_message_code
598 ,x_return_status => l_return_status );
599
600 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
601 PA_ASSIGNMENT_UTILS.Add_Message ( p_app_short_name => 'PA'
602 ,p_msg_name => l_error_message_code );
603 PA_ASSIGNMENTS_PUB.g_error_exists := FND_API.G_TRUE;
604 END IF;
605
606 END IF;
607
608 --get defaults from project when a requirement (not template
609 --requirement) is being either newly created
610 --or created(copied) from an assignment
611 IF l_assignment_rec.project_id IS NOT NULL AND l_assignment_rec.project_id <> FND_API.G_MISS_NUM THEN
612
613 OPEN get_project_info;
614 FETCH get_project_info INTO l_calendar_id,
615 l_comp_match_weighting,
616 l_avail_match_weighting,
617 l_job_level_match_weighting,
618 l_search_min_availability,
619 l_search_exp_org_struct_ver_id,
620 l_search_exp_start_org_id,
621 l_search_country_code,
622 l_search_min_candidate_score,
623 l_adv_action_set_id,
624 l_start_adv_action_set_flag,
625 l_enable_auto_cand_nom_flag; --Added for bug 4306049
626 CLOSE get_project_info;
627 END IF;
628
629 -- if no candidate or search settings parameters is passed in
630 -- and this is not to create template requirement
631 -- use candidate and search settings defaulted from the project
632
633 -- FP.M Development: Default the following attributes when
634 -- l_assignment_rec.resource_list_member_id IS NOT NULL
635 IF l_assignment_rec.project_id IS NOT NULL AND l_assignment_rec.project_id <> FND_API.G_MISS_NUM
636 AND (l_assignment_rec.comp_match_weighting IS NULL OR l_assignment_rec.comp_match_weighting = FND_API.G_MISS_NUM OR l_assignment_rec.resource_list_member_id <> FND_API.G_MISS_NUM)
637 AND PA_STARTUP.G_Calling_Application <> 'PLSQL' -- Bug 5130421 : added PLSQL check
638 THEN
639 l_assignment_rec.comp_match_weighting := l_comp_match_weighting;
640 l_assignment_rec.avail_match_weighting := l_avail_match_weighting;
641 l_assignment_rec.job_level_match_weighting := l_job_level_match_weighting;
642 l_assignment_rec.search_min_availability := l_search_min_availability;
643 l_assignment_rec.search_exp_org_struct_ver_id := l_search_exp_org_struct_ver_id;
644 l_assignment_rec.search_exp_start_org_id := l_search_exp_start_org_id;
645 l_assignment_rec.search_country_code := l_search_country_code;
646 l_assignment_rec.search_min_candidate_score := l_search_min_candidate_score;
647 l_assignment_rec.enable_auto_cand_nom_flag := l_enable_auto_cand_nom_flag; -- Changed 'Y' to l_enable_auto_cand_nom_flag for bug 4306049;
648 -- Bug 5130421 Added ELSIF
649 ELSIF PA_STARTUP.G_Calling_Application = 'PLSQL' AND l_assignment_rec.project_id IS NOT NULL AND l_assignment_rec.project_id <> FND_API.G_MISS_NUM
650 THEN
651 IF l_assignment_rec.comp_match_weighting IS NULL OR l_assignment_rec.comp_match_weighting = FND_API.G_MISS_NUM THEN
652 l_assignment_rec.comp_match_weighting := l_comp_match_weighting;
653 END IF;
654 IF l_assignment_rec.avail_match_weighting IS NULL OR l_assignment_rec.avail_match_weighting = FND_API.G_MISS_NUM THEN
655 l_assignment_rec.avail_match_weighting := l_avail_match_weighting;
656 END IF;
657 IF l_assignment_rec.job_level_match_weighting IS NULL OR l_assignment_rec.job_level_match_weighting = FND_API.G_MISS_NUM THEN
658 l_assignment_rec.job_level_match_weighting := l_job_level_match_weighting;
659 END IF;
660 IF l_assignment_rec.search_min_availability IS NULL OR l_assignment_rec.search_min_availability = FND_API.G_MISS_NUM THEN
661 l_assignment_rec.search_min_availability := l_search_min_availability;
662 END IF;
663 IF l_assignment_rec.search_exp_org_struct_ver_id IS NULL OR l_assignment_rec.search_exp_org_struct_ver_id = FND_API.G_MISS_NUM THEN
664 l_assignment_rec.search_exp_org_struct_ver_id := l_search_exp_org_struct_ver_id;
665 END IF;
666 IF l_assignment_rec.search_exp_start_org_id IS NULL OR l_assignment_rec.search_exp_start_org_id = FND_API.G_MISS_NUM THEN
667 l_assignment_rec.search_exp_start_org_id := l_search_exp_start_org_id;
668 END IF;
669 IF l_assignment_rec.search_country_code = FND_API.G_MISS_CHAR THEN
670 -- Search country code can be null
671 l_assignment_rec.search_country_code := l_search_country_code;
672 END IF;
673 IF l_assignment_rec.search_min_candidate_score IS NULL OR l_assignment_rec.search_min_candidate_score = FND_API.G_MISS_NUM THEN
674 l_assignment_rec.search_min_candidate_score := l_search_min_candidate_score;
675 END IF;
676 IF l_assignment_rec.enable_auto_cand_nom_flag IS NULL OR l_assignment_rec.enable_auto_cand_nom_flag = FND_API.G_MISS_CHAR THEN
677 l_assignment_rec.enable_auto_cand_nom_flag := 'Y';
678 END IF;
679 END IF;
680
681 --if a new requirement is being created(copied) from an assignment with a
682 --RESOURCE calendar then use the PROJECT calendar for the new requirement.
683 --It doesn't make sense to create a requirement with a resource calendar, as
684 --there is no resource for a requirement.
685 l_source_calendar_type := l_assignment_rec.calendar_type;
686
687 -- FP.M Development
688 IF l_assignment_rec.calendar_type = 'RESOURCE' THEN
689 l_assignment_rec.calendar_type := 'PROJECT';
690 l_assignment_rec.calendar_id := l_calendar_id;
691 -- ELSIF l_assignment_rec.calendar_type = 'TASK_ASSIGNMENT' THEN
692 -- l_sum_tasks_flag := 'Y';
693 --l_assignment_rec.calendar_type := 'PROJECT';
694 END IF;
695
696 --
697 -- Create the Open Assignment Record
698 --
699
700 IF p_validate_only = FND_API.G_FALSE AND FND_MSG_PUB.Count_Msg = 0 THEN
701
702 --Log Message
703 IF (P_DEBUG_MODE = 'Y') THEN
704 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_OPEN_ASSIGNMENT_PVT.Create_Open_Assignment.insert_row'
705 ,x_msg => 'Inserting record into pa_project_assignments.'
706 ,x_log_level => li_message_level);
707 END IF;
708
709 --dbms_output.put_line('calling insert_row');
710 PA_PROJECT_ASSIGNMENTS_PKG.Insert_Row
711 ( p_assignment_name => l_assignment_rec.assignment_name
712 ,p_assignment_type => l_assignment_rec.assignment_type
713 ,p_multiple_status_flag => l_assignment_rec.multiple_status_flag
714 ,p_status_code => l_assignment_rec.status_code
715 ,p_staffing_priority_code => l_assignment_rec.staffing_priority_code
716 ,p_project_id => l_assignment_rec.project_id
717 ,p_assignment_template_id => l_assignment_rec.assignment_template_id
718 ,p_project_role_id => l_assignment_rec.project_role_id
719 ,p_description => l_assignment_rec.description
720 ,p_start_date => l_assignment_rec.start_date
721 ,p_end_date => l_assignment_rec.end_date
722 ,p_assignment_effort => l_assignment_rec.assignment_effort
723 ,p_extension_possible => l_assignment_rec.extension_possible
724 ,p_source_assignment_id => l_assignment_rec.source_assignment_id
725 ,p_min_resource_job_level => l_assignment_rec.min_resource_job_level
726 ,p_max_resource_job_level => l_assignment_rec.max_resource_job_level
727 ,p_additional_information => l_assignment_rec.additional_information
728 ,p_work_type_id => l_assignment_rec.work_type_id
729 ,p_revenue_currency_code => l_assignment_rec.revenue_currency_code
730 ,p_revenue_bill_rate => l_assignment_rec.revenue_bill_rate
731 ,p_markup_percent => l_assignment_rec.markup_percent
732 ,p_expense_owner => l_assignment_rec.expense_owner
733 ,p_expense_limit => l_assignment_rec.expense_limit
734 ,p_expense_limit_currency_code => l_assignment_rec.expense_limit_currency_code
735 ,p_fcst_tp_amount_type => l_assignment_rec.fcst_tp_amount_type
736 ,p_fcst_job_id => l_assignment_rec.fcst_job_id
737 ,p_fcst_job_group_id => l_assignment_rec.fcst_job_group_id
738 ,p_expenditure_org_id => l_assignment_rec.expenditure_org_id
739 ,p_expenditure_organization_id => l_assignment_rec.expenditure_organization_id
740 ,p_expenditure_type_class => l_assignment_rec.expenditure_type_class
741 ,p_expenditure_type => l_assignment_rec.expenditure_type
742 ,p_location_id => l_assignment_rec.location_id
743 ,p_calendar_type => l_assignment_rec.calendar_type
744 ,p_calendar_id => l_assignment_rec.calendar_id
745 ,p_comp_match_weighting => l_assignment_rec.comp_match_weighting
746 ,p_avail_match_weighting => l_assignment_rec.avail_match_weighting
747 ,p_job_level_match_weighting => l_assignment_rec.job_level_match_weighting
748 ,p_search_min_availability => l_assignment_rec.search_min_availability
749 ,p_search_country_code => l_assignment_rec.search_country_code
750 ,p_search_exp_org_struct_ver_id => l_assignment_rec.search_exp_org_struct_ver_id
751 ,p_search_exp_start_org_id => l_assignment_rec.search_exp_start_org_id
752 ,p_search_min_candidate_score => l_assignment_rec.search_min_candidate_score
753 ,p_enable_auto_cand_nom_flag => l_assignment_rec.enable_auto_cand_nom_flag
754 ,p_bill_rate_override => l_assignment_rec.bill_rate_override
755 ,p_bill_rate_curr_override => l_assignment_rec.bill_rate_curr_override
756 ,p_markup_percent_override => l_assignment_rec.markup_percent_override
757 ,p_discount_percentage => l_assignment_rec.discount_percentage -- FP.L Development
758 ,p_rate_disc_reason_code => l_assignment_rec.rate_disc_reason_code -- FP.L Development
759 ,p_tp_rate_override => l_assignment_rec.tp_rate_override
760 ,p_tp_currency_override => l_assignment_rec.tp_currency_override
761 ,p_tp_calc_base_code_override => l_assignment_rec.tp_calc_base_code_override
762 ,p_tp_percent_applied_override => l_assignment_rec.tp_percent_applied_override
763 ,p_staffing_owner_person_id => l_assignment_rec.staffing_owner_person_id -- FP.L Development
764 ,p_resource_list_member_id => l_assignment_rec.resource_list_member_id -- FP.M Development
765 ,p_attribute_category => l_assignment_rec.attribute_category
766 ,p_attribute1 => l_assignment_rec.attribute1
767 ,p_attribute2 => l_assignment_rec.attribute2
768 ,p_attribute3 => l_assignment_rec.attribute3
769 ,p_attribute4 => l_assignment_rec.attribute4
770 ,p_attribute5 => l_assignment_rec.attribute5
771 ,p_attribute6 => l_assignment_rec.attribute6
772 ,p_attribute7 => l_assignment_rec.attribute7
773 ,p_attribute8 => l_assignment_rec.attribute8
774 ,p_attribute9 => l_assignment_rec.attribute9
775 ,p_attribute10 => l_assignment_rec.attribute10
776 ,p_attribute11 => l_assignment_rec.attribute11
777 ,p_attribute12 => l_assignment_rec.attribute12
778 ,p_attribute13 => l_assignment_rec.attribute13
779 ,p_attribute14 => l_assignment_rec.attribute14
780 ,p_attribute15 => l_assignment_rec.attribute15
781 ,p_number_of_requirements => p_number_of_requirements
782 ,x_assignment_row_id => x_assignment_row_id
783 ,x_new_assignment_id => l_assignment_id
784 ,x_assignment_number => x_assignment_number
785 ,x_return_status => x_return_status
786 );
787
788 x_new_assignment_id := l_assignment_id;
789
790 --Log Message
791 IF (P_DEBUG_MODE = 'Y') THEN
792 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_OPEN_ASSIGNMENT_PVT.Create_Open_Assignment.insert_schedule'
793 ,x_msg => 'Insert the schedule days for the open assignment.'
794 ,x_log_level => li_message_level);
795 END IF;
796
797 --
798 -- Insert the schedule days for the open assignment
799 -- Do not create the schedule if this is a template requirement.
800 IF l_assignment_rec.project_id IS NOT NULL AND l_assignment_rec.project_id <> FND_API.G_MISS_NUM THEN
801
802 IF l_source_assignment_id = FND_API.G_MISS_NUM THEN
803 l_source_assignment_id := NULL;
804 END IF;
805
806 --if this requirement is being copied from a team role then
807 --a)pass a NULL calendar id to the create schedule API if the source team role did not
808 -- have a RESOURCE calendar type. This is because the
809 -- schedule API will just copy the schedule from the source team role and the
810 -- NULL calendar id is a flag to the create schedule API.
811 --b)do pass the calendar id to the create schedule API if the source team role
812 -- has a resource calendar because the new requirement cannot use a
813 -- resource calendar (it would use the project calendar as described above)
814 -- so the create schedule API will actually create a new schedule.
815 --If this requirement is being created from a template requirement (assignment template id
816 --is populated then we must pass the calendar and status because a template requirement
817 --does not have any schedule so it must be created.
818 IF p_asgn_creation_mode = 'COPY' AND (l_assignment_rec.assignment_template_id IS NULL OR
819 l_assignment_rec.assignment_template_id = FND_API.G_MISS_NUM) THEN
820
821 IF l_source_calendar_type <> 'RESOURCE' THEN
822 l_assignment_rec.calendar_id := NULL;
823
824 --If the source status code (status code of the team role being copied) is
825 --the same as the default requirement starting status then don't pass the
826 --status code to the create schedule API. That is a flag to the create schedule
827 --API that the status is the same.
828 --if calendar id is going to be passed then status code also MUST be passed.
829 --that is why this IF condition is inside the outer.
830 IF l_source_status_code = l_assignment_rec.status_code THEN
831 l_assignment_rec.status_code := NULL;
832 END IF;
833 END IF;
834
835 END IF;
836 /*
837 dbms_output.put_line('l_assignment_rec.project_id='||l_assignment_rec.project_id);
838 dbms_output.put_line('l_assignment_rec.calendar_id='||l_assignment_rec.calendar_id);
839 dbms_output.put_line('l_source_assignment_id='||l_source_assignment_id);
840 dbms_output.put_line('l_assignment_rec.start_date='||l_assignment_rec.start_date);
841 dbms_output.put_line('l_assignment_rec.end_date='||l_assignment_rec.end_date);
842 dbms_output.put_line('l_assignment_rec.status_code='||l_assignment_rec.status_code);
843 */
844
845 --FP.M Development
846 IF p_assignment_rec.project_id <> FND_API.G_MISS_NUM AND
847 p_asgn_creation_mode <> 'COPY' THEN
848
849 IF P_DEBUG_MODE = 'Y' THEN
850 pa_debug.write(x_module => 'pa.plsql.PA_OPEN_ASSIGNMENT_PVT.Create_Open_Assignment'
851 ,x_msg => 'FP.M Development'
852 ,x_log_level => li_message_level);
853
854 pa_debug.write(x_module => 'pa.plsql.PA_OPEN_ASSIGNMENT_PVT.Create_Open_Assignment'
855 ,x_msg => 'resource_list_member_id'||l_assignment_rec.resource_list_member_id
856 ,x_log_level => li_message_level);
857
858 pa_debug.write(x_module => 'pa.plsql.PA_OPEN_ASSIGNMENT_PVT.Create_Open_Assignment'
859 ,x_msg => 'budget_version_id'||p_budget_version_id
860 ,x_log_level => li_message_level);
861 END IF;
862
863 IF p_budget_version_id IS NOT NULL AND p_budget_version_id <> FND_API.G_MISS_NUM AND
864 l_assignment_rec.resource_list_member_id IS NOT NULL AND
865 l_assignment_rec.resource_list_member_id <> FND_API.G_MISS_NUM THEN
866
867 OPEN get_bu_resource_assignments;
868 FETCH get_bu_resource_assignments
869 BULK COLLECT INTO l_task_assignment_id_tbl,
870 l_task_version_id_tbl,
871 l_budget_version_id_tbl,
872 l_struct_version_id_tbl;
873 CLOSE get_bu_resource_assignments;
874
875 ELSE
876
877 OPEN get_td_resource_assignments;
878 FETCH get_td_resource_assignments
879 BULK COLLECT INTO l_task_assignment_id_tbl,
880 l_task_version_id_tbl,
881 l_budget_version_id_tbl,
882 l_struct_version_id_tbl;
883 CLOSE get_td_resource_assignments;
884
885 END IF;
886
887 -- If multiple requirements are created, only the first requirement will be
888 -- linked to the task assignments.
889 -- Call planning_transaction_utils api to update project_assignment_id in
890 -- pa_resource_assignments table.
891 pa_assignments_pvt.Update_Task_Assignments(
892 p_mode => 'CREATE'
893 ,p_task_assignment_id_tbl => l_task_assignment_id_tbl
894 ,p_task_version_id_tbl => l_task_version_id_tbl
895 ,p_budget_version_id_tbl => l_budget_version_id_tbl
896 ,p_struct_version_id_tbl => l_struct_version_id_tbl
897 -- change project_assignment_id to this assignment_id
898 ,p_project_assignment_id => PA_ASSIGNMENTS_PUB.g_assignment_id_tbl(1).assignment_id
899 -- ,p_resource_list_member_id => l_assignment_rec.resource_list_member_id
900 -- change the named role to this assignment name
901 ,p_named_role => p_assignment_rec.assignment_name
902 ,p_project_role_id => p_assignment_rec.project_role_id
903 ,x_return_status => l_return_status
904 );
905
906 END IF; -- IF p_assignment_rec.project_id <> FND_API.G_MISS_NUM AND
907 -- p_asgn_creation_mode <> 'COPY' THEN
908
909 PA_SCHEDULE_PVT.Create_OPN_ASG_Schedule
910 ( p_project_id => l_assignment_rec.project_id
911 ,p_calendar_id => l_assignment_rec.calendar_id
912 ,p_assignment_id_tbl => PA_ASSIGNMENTS_PUB.g_assignment_id_tbl
913 ,p_assignment_source_id => l_source_assignment_id
914 ,p_start_date => l_assignment_rec.start_date
915 ,p_end_date => l_assignment_rec.end_date
916 ,p_assignment_status_code => l_assignment_rec.status_code
917 ,p_task_assignment_id_tbl => l_task_assignment_id_tbl
918 ,p_sum_tasks_flag => p_sum_tasks_flag
919 ,p_budget_version_id => p_budget_version_id
920 ,x_return_status => l_return_status
921 ,x_msg_count => l_msg_count
922 ,x_msg_data => l_msg_data
923 );
924
925 IF P_DEBUG_MODE = 'Y' THEN
926 pa_debug.write(x_module => 'pa.plsql.PA_OPEN_ASSIGNMENT_PVT.Create_Open_Assignment'
927 ,x_msg => 'create_opn_asg_schedule '||l_return_status
928 ,x_log_level => li_message_level);
929 END IF;
930
931 /* Bug 3051110 - Added code to call PA_ASSIGNMENTS_PVT.Calc_Init_Transfer_Price if the l_return status is success,
932 this is to populate the TP columns in pa_project_assignments table */
933
934 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
935
936 IF p_debug_mode = 'Y' THEN
937 PA_DEBUG.WRITE('PA_OPEN_ASSIGNMENT_PVT.Create_Open_Assignment', 'About to call .Calc_Init_Transfer_Price', 3);
938 PA_DEBUG.WRITE('PA_OPEN_ASSIGNMENT_PVT.Create_Open_Assignment', 'Assignment_id is :'||l_assignment_id||' and start date:'||l_assignment_rec.start_date, 3);
939 END IF;
940
941 FOR i IN PA_ASSIGNMENTS_PUB.g_assignment_id_tbl.FIRST .. PA_ASSIGNMENTS_PUB.g_assignment_id_tbl.LAST LOOP
942
943 PA_ASSIGNMENTS_PVT.Calc_Init_Transfer_Price
944 (p_assignment_id => PA_ASSIGNMENTS_PUB.g_assignment_id_tbl(i).assignment_id,
945 p_start_date => l_assignment_rec.start_date,
946 p_debug_mode => p_debug_mode,
947 x_return_status => l_return_status,
948 x_msg_data => l_msg_data,
949 x_msg_count => l_msg_count );
950 IF P_DEBUG_MODE = 'Y' THEN
951 pa_debug.write(x_module => 'pa.plsql.PA_OPEN_ASSIGNMENT_PVT.Create_Open_Assignment'
952 ,x_msg => 'calc_init_transfer_price '||l_return_status
953 ,x_log_level => li_message_level);
954 END IF;
955 END LOOP;
956
957 END IF;
958
959 --
960 -- Bug 2388060 - Apply Action Set after schedule has been created
961 -- Apply the Advertisement Action Set on the non-template requirement
962 --
963 IF p_adv_action_set_id IS NOT NULL AND p_adv_action_set_id <> FND_API.G_MISS_NUM THEN
964 l_adv_action_set_id := p_adv_action_set_id;
965 END IF;
966
967 IF p_start_adv_action_set_flag IS NOT NULL AND p_start_adv_action_set_flag <> FND_API.G_MISS_CHAR THEN
968 l_start_adv_action_set_flag := p_start_adv_action_set_flag;
969 END IF;
970
971 -- set the global variable for PA_ADVERTISEMENTS_PUB
972 -- Is_Action_Set_Started_On_Apply to return to overriding flag
973 /*Commented for bug 2636577*/
974 --PA_ADVERTISEMENTS_PUB.g_start_adv_action_set_flag := l_start_adv_action_set_flag;
975
976 --dbms_output.put_line('before calling PA_ACTION_SETS_PUB.Apply_Action_Set');
977 --dbms_output.put_line('action set id= '||l_adv_action_set_id);
978 --dbms_output.put_line('start action set ? '||l_start_adv_action_set_flag);
979
980 --Log Message
981 IF (P_DEBUG_MODE = 'Y') THEN
982 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_OPEN_ASSIGNMENT_PVT.Create_Open_Assignment'
983 ,x_msg => 'calling PA_ACTION_SETS_PUB.Apply_Action_Set'
984 ,x_log_level => li_message_level);
985 END IF;
986
987
988 IF FND_MSG_PUB.Count_Msg = 0 THEN
989
990 FOR i IN PA_ASSIGNMENTS_PUB.g_assignment_id_tbl.FIRST .. PA_ASSIGNMENTS_PUB.g_assignment_id_tbl.LAST LOOP
991 /*Added for bug 2636577*/
992
993 PA_ADVERTISEMENTS_PUB.g_start_adv_action_set_flag := l_start_adv_action_set_flag;
994
995 /*code change end for 2636577*/
996
997 IF P_DEBUG_MODE = 'Y' THEN
998 pa_debug.write(x_module => 'pa.plsql.PA_OPEN_ASSIGNMENT_PVT.Create_Open_Assignment'
999 ,x_msg => 'before Apply_Action_Set, action_set_id='||l_adv_action_set_id||
1000 ' obj_id='||PA_ASSIGNMENTS_PUB.g_assignment_id_tbl(i).assignment_id||
1001 ' commit='||p_commit||
1002 ' val='||p_validate_only
1003 ,x_log_level => li_message_level);
1004 END IF;
1005
1006 PA_ACTION_SETS_PUB.Apply_Action_Set(
1007 p_action_set_id => l_adv_action_set_id
1008 ,p_object_type => 'OPEN_ASSIGNMENT'
1009 ,p_object_id => PA_ASSIGNMENTS_PUB.g_assignment_id_tbl(i).assignment_id
1010 ,p_perform_action_set_flag => 'Y'
1011 ,p_commit => p_commit
1012 ,p_validate_only => p_validate_only
1013 ,p_init_msg_list => FND_API.G_FALSE
1014 ,x_new_action_set_id => l_new_action_set_id
1015 ,x_return_status => l_return_status
1016 ,x_msg_count => l_msg_count
1017 ,x_msg_data => l_msg_data);
1018
1019 IF P_DEBUG_MODE = 'Y' THEN
1020 pa_debug.write(x_module => 'pa.plsql.PA_OPEN_ASSIGNMENT_PVT.Create_Open_Assignment'
1021 ,x_msg => 'Apply_Action_Set, status='||l_return_status
1022 ,x_log_level => li_message_level);
1023 END IF;
1024
1025 END LOOP;
1026 END IF;
1027
1028 --dbms_output.put_line('after calling PA_ACTION_SETS_PUB.Apply_Action_Set');
1029
1030 END IF;
1031
1032
1033 --Log Message
1034 IF (P_DEBUG_MODE = 'Y') THEN
1035 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_OPEN_ASSIGNMENT_PVT.Create_Open_Assignment.create_competency'
1036 ,x_msg => 'Creating competencies.'
1037 ,x_log_level => li_message_level);
1038 END IF;
1039
1040
1041 --
1042 -- Create Competencies for Requirement and Template Requiremento
1043 --
1044 --dbms_output.put_line('competency table count: ' || l_competencies_tbl.COUNT);
1045 --
1046 -- FP.L Development
1047 -- User can now specify competences in the page, so there is no need to insert
1048 -- competences defaulting from the role.
1049 -- In this case, we only need to insert competencies when the requirement is
1050 -- created from template
1051 --
1052 -- FP.M Development
1053 -- If creating requirement from Create Team Roles page, insert the competencies.
1054
1055 -- 5130421 : Added G_Calling_Application check so that competencies are copied
1056 -- while creation of new requirments
1057
1058 IF (l_source_assignment_id IS NOT NULL AND
1059 l_source_assignment_id <> FND_API.G_MISS_NUM AND
1060 l_assignment_rec.source_assignment_type = 'OPEN_ASSIGNMENT')
1061 OR (p_assignment_rec.resource_list_member_id <> FND_API.G_MISS_NUM AND
1062 l_assignment_rec.source_assignment_type = 'OPEN_ASSIGNMENT')
1063 OR (p_budget_version_id IS NOT NULL AND
1064 p_budget_version_id <> FND_API.G_MISS_NUM AND
1065 l_assignment_rec.resource_list_member_id IS NOT NULL AND
1066 l_assignment_rec.resource_list_member_id <> FND_API.G_MISS_NUM)
1067 OR (PA_STARTUP.G_Calling_Application = 'PLSQL' AND PA_STARTUP.G_Calling_module = 'AMG')
1068 THEN
1069
1070 FOR i IN 1..l_competencies_tbl.COUNT LOOP
1071 /*
1072 dbms_output.put_line('PA_ASSIGNMENTS_PUB.g_assignment_id_tbl.COUNT='||PA_ASSIGNMENTS_PUB.g_assignment_id_tbl.COUNT);
1073 dbms_output.put_line('l_assignment_rec.project_id='||l_assignment_rec.project_id);
1074 dbms_output.put_line('l_competencies_tbl(1).competence_id='||l_competencies_tbl(1).competence_id);
1075 dbms_output.put_line('l_competencies_tbl(1).rating_level_id='||l_competencies_tbl(1).rating_level_id);
1076 dbms_output.put_line('l_competencies_tbl(1).mandatory='||l_competencies_tbl(1).mandatory);
1077 */
1078 PA_COMPETENCE_PVT.Add_Competence_Element
1079 ( p_object_name => 'OPEN_ASSIGNMENT'
1080 ,p_object_id => PA_ASSIGNMENTS_PUB.g_assignment_id_tbl
1081 ,p_project_id => l_assignment_rec.project_id
1082 ,p_competence_id => l_competencies_tbl(i).competence_id
1083 ,p_rating_level_id => l_competencies_tbl(i).rating_level_id
1084 ,p_mandatory_flag => l_competencies_tbl(i).mandatory
1085 ,p_commit => p_commit
1086 ,p_validate_only => p_validate_only
1087 ,x_element_rowid => l_element_rowid
1088 ,x_element_id => l_element_id
1089 ,x_return_status => l_element_return_status
1090 );
1091 END LOOP;
1092 END IF;
1093
1094 END IF;
1095
1096
1097
1098 -- Reset the error stack when returning to the calling program
1099 PA_DEBUG.Reset_Err_Stack;
1100
1101 -- If g_error_exists is TRUE then set the x_return_status to 'E'
1102
1103 IF PA_ASSIGNMENTS_PUB.g_error_exists = FND_API.G_TRUE THEN
1104
1105 x_return_status := FND_API.G_RET_STS_ERROR;
1106
1107 END IF;
1108
1109
1110
1111 EXCEPTION
1112 WHEN OTHERS THEN
1113
1114 -- 4537865 : RESET other OUT params also.
1115 x_new_assignment_id := NULL ;
1116 x_assignment_number := NULL ;
1117 x_assignment_row_id := NULL ;
1118
1119 -- Set the excetption Message and the stack
1120 FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_OPEN_ASSIGNMENT_PVT.Create_Open_Assignment'
1121 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
1122 --
1123 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1124 RAISE; -- This is optional depending on the needs
1125 END Create_Open_Assignment;
1126
1127
1128 PROCEDURE Update_Open_Assignment
1129 ( p_assignment_rec IN PA_ASSIGNMENTS_PUB.Assignment_Rec_Type
1130 ,p_location_city IN pa_locations.city%TYPE := FND_API.G_MISS_CHAR
1131 ,p_location_region IN pa_locations.region%TYPE := FND_API.G_MISS_CHAR
1132 ,p_location_country_code IN pa_locations.country_code%TYPE := FND_API.G_MISS_CHAR
1133 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
1134 ,p_validate_only IN VARCHAR2 := FND_API.G_TRUE
1135 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1136 )
1137 IS
1138
1139 l_assignment_rec PA_ASSIGNMENTS_PUB.Assignment_Rec_Type;
1140 l_old_status_code pa_project_assignments.status_code%TYPE;
1141 l_old_start_date pa_project_assignments.start_date%TYPE;
1142 l_old_end_date pa_project_assignments.end_date%TYPE;
1143 l_return_status VARCHAR2(1);
1144 l_msg_count NUMBER;
1145 l_error_message_code fnd_new_messages.message_name%TYPE;
1146 l_msg_data FND_NEW_MESSAGES.message_text%TYPE;
1147 l_req_text FND_NEW_MESSAGES.message_text%TYPE;
1148 l_proj_req_res_format_id NUMBER;
1149 l_task_assignment_id_tbl system.pa_num_tbl_type;
1150 l_resource_list_member_id_tbl system.pa_num_tbl_type := system.pa_num_tbl_type();
1151 l_project_assignment_id_tbl system.pa_num_tbl_type := system.pa_num_tbl_type();
1152 l_budget_version_id_tbl system.pa_num_tbl_type := system.pa_num_tbl_type();
1153 l_struct_version_id_tbl system.pa_num_tbl_type := system.pa_num_tbl_type();
1154 l_last_bvid NUMBER;
1155 l_update_task_asgmt_id_tbl system.pa_num_tbl_type := system.pa_num_tbl_type();
1156 l_update_count NUMBER;
1157 l_last_struct_version_id NUMBER;
1158 l_task_version_id_tbl system.pa_num_tbl_type := system.pa_num_tbl_type();
1159 l_update_task_version_id_tbl system.pa_num_tbl_type := system.pa_num_tbl_type();
1160
1161 l_cur_resource_id pa_project_assignments.resource_id%TYPE;
1162 l_cur_fcst_job_id pa_project_assignments.fcst_job_id%TYPE;
1163 l_cur_exp_org_id pa_project_assignments.expenditure_organization_id%TYPE;
1164 l_cur_expenditure_type pa_project_assignments.expenditure_type%TYPE;
1165 l_cur_project_role_id pa_project_assignments.project_role_id%TYPE;
1166 l_cur_assignment_name pa_project_assignments.assignment_name%TYPE;
1167 l_cur_resource_list_member_id pa_project_assignments.resource_list_member_id%TYPE;
1168 l_new_person_id pa_resource_txn_attributes.person_id%TYPE;
1169 l_named_role pa_project_assignments.ASSIGNMENT_NAME%TYPE;
1170
1171 l_cur_res_format_id pa_res_formats_b.res_format_id%TYPE;
1172 l_cur_res_type_flag pa_res_formats_b.res_type_enabled_flag%TYPE;
1173 l_cur_orgn_flag pa_res_formats_b.orgn_enabled_flag%TYPE;
1174 l_cur_fin_cat_flag pa_res_formats_b.fin_cat_enabled_flag%TYPE;
1175 l_cur_role_flag pa_res_formats_b.role_enabled_flag%TYPE;
1176
1177 -- l_unlink_flag VARCHAR2(1) := 'N';
1178
1179 l_resource_list_members_tbl SYSTEM.PA_NUM_TBL_TYPE := system.pa_num_tbl_type();
1180 l_resource_class_flag_tbl SYSTEM.PA_VARCHAR2_1_TBL_TYPE := system.pa_varchar2_1_tbl_type();
1181 l_resource_class_code_tbl SYSTEM.PA_VARCHAR2_30_TBL_TYPE := system.pa_varchar2_30_tbl_type();
1182 l_resource_class_id_tbl SYSTEM.PA_NUM_TBL_TYPE := system.pa_num_tbl_type();
1183 l_res_type_code_tbl SYSTEM.PA_VARCHAR2_30_TBL_TYPE := system.pa_varchar2_30_tbl_type();
1184 l_incur_by_res_type_tbl SYSTEM.PA_VARCHAR2_30_TBL_TYPE := system.pa_varchar2_30_tbl_type();
1185 l_person_id_tbl SYSTEM.PA_NUM_TBL_TYPE := system.pa_num_tbl_type();
1186 l_job_id_tbl SYSTEM.PA_NUM_TBL_TYPE := system.pa_num_tbl_type();
1187 l_person_type_code_tbl SYSTEM.PA_VARCHAR2_30_TBL_TYPE := system.pa_varchar2_30_tbl_type();
1188 l_named_role_tbl SYSTEM.PA_VARCHAR2_80_TBL_TYPE := system.pa_varchar2_80_tbl_type();
1189 l_bom_resource_id_tbl SYSTEM.PA_NUM_TBL_TYPE := system.pa_num_tbl_type();
1190 l_non_labor_resource_tbl SYSTEM.PA_VARCHAR2_20_TBL_TYPE := system.pa_varchar2_20_tbl_type();
1191 l_inventory_item_id_tbl SYSTEM.PA_NUM_TBL_TYPE := system.pa_num_tbl_type();
1192 l_item_category_id_tbl SYSTEM.PA_NUM_TBL_TYPE := system.pa_num_tbl_type();
1193 l_project_role_id_tbl SYSTEM.PA_NUM_TBL_TYPE := system.pa_num_tbl_type();
1194 l_organization_id_tbl SYSTEM.PA_NUM_TBL_TYPE := system.pa_num_tbl_type();
1195 l_fc_res_type_code_tbl SYSTEM.PA_VARCHAR2_30_TBL_TYPE := system.pa_varchar2_30_tbl_type();
1196 l_expenditure_type_tbl SYSTEM.PA_VARCHAR2_30_TBL_TYPE := system.pa_varchar2_30_tbl_type();
1197 l_expenditure_category_tbl SYSTEM.PA_VARCHAR2_30_TBL_TYPE := system.pa_varchar2_30_tbl_type();
1198 l_event_type_tbl SYSTEM.PA_VARCHAR2_30_TBL_TYPE := system.pa_varchar2_30_tbl_type();
1199 l_revenue_category_code_tbl SYSTEM.PA_VARCHAR2_30_TBL_TYPE := system.pa_varchar2_30_tbl_type();
1200 l_supplier_id_tbl SYSTEM.PA_NUM_TBL_TYPE := system.pa_num_tbl_type();
1201 l_spread_curve_id_tbl SYSTEM.PA_NUM_TBL_TYPE := system.pa_num_tbl_type();
1202 l_etc_method_code_tbl SYSTEM.PA_VARCHAR2_30_TBL_TYPE := system.pa_varchar2_30_tbl_type();
1203 l_mfc_cost_type_id_tbl SYSTEM.PA_NUM_TBL_TYPE := system.pa_num_tbl_type();
1204 l_incurred_by_res_flag_tbl SYSTEM.PA_VARCHAR2_1_TBL_TYPE := system.pa_varchar2_1_tbl_type();
1205 l_incur_by_res_class_code_tbl SYSTEM.PA_VARCHAR2_30_TBL_TYPE := system.pa_varchar2_30_tbl_type();
1206 l_incur_by_role_id_tbl SYSTEM.PA_NUM_TBL_TYPE := system.pa_num_tbl_type();
1207 l_unit_of_measure_tbl SYSTEM.PA_VARCHAR2_30_TBL_TYPE := system.pa_varchar2_30_tbl_type();
1208 l_org_id_tbl SYSTEM.PA_NUM_TBL_TYPE := system.pa_num_tbl_type();
1209 l_rate_based_flag_tbl SYSTEM.PA_VARCHAR2_1_TBL_TYPE := system.pa_varchar2_1_tbl_type();
1210 l_rate_expenditure_type_tbl SYSTEM.PA_VARCHAR2_30_TBL_TYPE := system.pa_varchar2_30_tbl_type();
1211 l_rate_func_curr_code_tbl SYSTEM.PA_VARCHAR2_30_TBL_TYPE := system.pa_varchar2_30_tbl_type();
1212 l_rate_incurred_by_org_id_tbl SYSTEM.PA_NUM_TBL_TYPE := system.pa_num_tbl_type();
1213
1214 l_fcst_job_id_tmp pa_project_assignments.fcst_job_id%TYPE;
1215 l_expenditure_org_id_tmp pa_project_assignments.expenditure_organization_id%TYPE;
1216 l_expenditure_type_tmp pa_project_assignments.expenditure_type%TYPE;
1217 l_project_role_id_tmp pa_project_assignments.project_role_id%TYPE;
1218 l_assignment_name_tmp pa_project_assignments.assignment_name%TYPE;
1219
1220 CURSOR assignment_status_code_csr IS
1221 SELECT status_code, start_date, end_date
1222 FROM pa_project_assignments
1223 WHERE assignment_id = p_assignment_rec.assignment_id;
1224
1225 CURSOR get_unlinked_res_asgmts IS
1226 SELECT resource_assignment_id, wbs_element_version_id, budget_version_id, project_structure_version_id
1227 FROM
1228 (
1229 (SELECT ra.resource_assignment_id, ra.wbs_element_version_id, bv.budget_version_id, bv.project_structure_version_id
1230 FROM PA_RESOURCE_ASSIGNMENTS ra
1231 ,PA_BUDGET_VERSIONS bv
1232 ,PA_PROJ_ELEM_VER_STRUCTURE evs
1233 WHERE ra.project_id = bv.project_id
1234 AND bv.project_id = evs.project_id
1235 AND ra.budget_version_id = bv.budget_version_id
1236 AND bv.project_structure_version_id = evs.element_version_id
1237 AND ra.project_id = l_assignment_rec.project_id
1238 AND ra.resource_list_member_id = l_assignment_rec.resource_list_member_id
1239 AND ra.project_assignment_id = -1
1240 AND evs.status_code = 'STRUCTURE_WORKING')
1241 UNION ALL
1242 (SELECT ra.resource_assignment_id, ra.wbs_element_version_id, bv.budget_version_id, bv.project_structure_version_id
1243 FROM PA_RESOURCE_ASSIGNMENTS ra
1244 ,PA_BUDGET_VERSIONS bv
1245 ,PA_PROJ_ELEM_VER_STRUCTURE evs
1246 ,PA_PROJ_WORKPLAN_ATTR pwa
1247 WHERE pwa.wp_enable_Version_flag = 'N'
1248 AND pwa.project_id = ra.project_id
1249 AND pwa.proj_element_id = evs.proj_element_id
1250 AND ra.project_id = bv.project_id
1251 AND bv.project_id = evs.project_id
1252 AND ra.budget_version_id = bv.budget_version_id
1253 AND bv.project_structure_version_id = evs.element_version_id
1254 AND ra.resource_list_member_id = l_assignment_rec.resource_list_member_id
1255 AND ra.project_id = l_assignment_rec.project_id
1256 AND ra.project_assignment_id = -1)
1257 )
1258 ORDER BY budget_version_id, project_structure_version_id;
1259
1260 CURSOR get_linked_res_asgmts IS
1261 SELECT resource_assignment_id, wbs_element_version_id, budget_version_id, project_structure_version_id
1262 FROM
1263 (
1264 (SELECT ra.resource_assignment_id, ra.wbs_element_version_id, bv.budget_version_id, bv.project_structure_version_id
1265 FROM PA_RESOURCE_ASSIGNMENTS ra
1266 ,PA_BUDGET_VERSIONS bv
1267 ,PA_PROJ_ELEM_VER_STRUCTURE evs
1268 WHERE ra.project_id = bv.project_id
1269 AND bv.project_id = evs.project_id
1270 AND ra.budget_version_id = bv.budget_version_id
1271 AND bv.project_structure_version_id = evs.element_version_id
1272 AND ra.project_id = l_assignment_rec.project_id
1273 AND ra.project_assignment_id = l_assignment_rec.assignment_id
1274 AND evs.status_code = 'STRUCTURE_WORKING')
1275 UNION ALL
1276 (SELECT ra.resource_assignment_id, ra.wbs_element_version_id, bv.budget_version_id, bv.project_structure_version_id
1277 FROM PA_RESOURCE_ASSIGNMENTS ra
1278 ,PA_BUDGET_VERSIONS bv
1279 ,PA_PROJ_ELEM_VER_STRUCTURE evs
1280 ,PA_PROJ_WORKPLAN_ATTR pwa
1281 WHERE pwa.wp_enable_Version_flag = 'N'
1282 AND pwa.project_id = ra.project_id
1283 AND pwa.proj_element_id = evs.proj_element_id
1284 AND ra.project_id = bv.project_id
1285 AND bv.project_id = evs.project_id
1286 AND ra.budget_version_id = bv.budget_version_id
1287 AND bv.project_structure_version_id = evs.element_version_id
1288 AND ra.project_id = l_assignment_rec.project_id
1289 AND ra.project_assignment_id = l_assignment_rec.assignment_id)
1290 )
1291 ORDER BY budget_version_id, project_structure_version_id;
1292
1293 CURSOR get_res_mand_attributes IS
1294 SELECT rf.res_format_id, rf.RES_TYPE_ENABLED_FLAG,
1295 rf.ORGN_ENABLED_FLAG, rf.FIN_CAT_ENABLED_FLAG,
1296 rf.ROLE_ENABLED_FLAG
1297 FROM pa_res_formats_b rf,
1298 pa_resource_list_members rlm
1299 WHERE rlm.res_format_id = rf.res_format_id
1300 AND rlm.resource_list_member_id = l_assignment_rec.resource_list_member_id;
1301
1302 CURSOR get_cur_asgmt_attributes IS
1303 SELECT resource_id, fcst_job_id, expenditure_organization_id,
1304 expenditure_type,
1305 project_role_id, assignment_name,
1306 resource_list_member_id,
1307 project_role_id
1308 FROM pa_project_assignments
1309 WHERE assignment_id = l_assignment_rec.assignment_id;
1310
1311 BEGIN
1312 -- Initialize the Error Stack
1313 PA_DEBUG.set_err_stack('PA_OPEN_ASSIGNMENT_PVT.Update_Open_Assignment');
1314
1315 --Log Message
1316 IF (P_DEBUG_MODE = 'Y') THEN
1317 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_OPEN_ASSIGNMENT_PVT.Update_Open_Assignment.begin'
1318 ,x_msg => 'Beginning of Update_Open_Assignment'
1319 ,x_log_level => li_message_level);
1320 END IF;
1321
1322
1323 -- Initialize the return status to success
1324 x_return_status := FND_API.G_RET_STS_SUCCESS;
1325
1326 -- Assign the input record to the local variable
1327 l_assignment_rec := p_assignment_rec;
1328
1329 --dbms_output.put_line('IN PVT open assignment');
1330
1331 -- get the current attributes of the project team role
1332 OPEN get_cur_asgmt_attributes;
1333 FETCH get_cur_asgmt_attributes INTO
1334 l_cur_resource_id,
1335 l_cur_fcst_job_id,
1336 l_cur_exp_org_id,
1337 l_cur_expenditure_type,
1338 l_cur_project_role_id,
1339 l_cur_assignment_name,
1340 l_cur_resource_list_member_id,
1341 l_cur_project_role_id;
1342 CLOSE get_cur_asgmt_attributes;
1343
1344 IF p_assignment_rec.project_role_id = FND_API.G_MISS_NUM THEN
1345 l_assignment_rec.project_role_id := l_cur_project_role_id;
1346 END IF;
1347
1348 --Log Message
1349 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
1350 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_OPEN_ASSIGNMENT_PVT.Update_Open_Assignment.begin'
1351 ,x_msg => 'Old resource list member id='||l_cur_resource_list_member_id
1352 ,x_log_level => li_message_level);
1353 END IF;
1354
1355 IF l_cur_resource_list_member_id IS NOT NULL AND
1356 p_assignment_rec.project_id <> FND_API.G_MISS_NUM THEN
1357
1358 -- get the mandatory attributes of planning resource
1359 OPEN get_res_mand_attributes;
1360 FETCH get_res_mand_attributes INTO
1361 l_cur_res_format_id,
1362 l_cur_res_type_flag,
1363 l_cur_orgn_flag,
1364 l_cur_fin_cat_flag,
1365 l_cur_role_flag;
1366 CLOSE get_res_mand_attributes;
1367
1368 -- check if mandatory attributes are changed
1369 IF (l_cur_res_type_flag = 'Y' AND
1370 p_assignment_rec.resource_id <> FND_API.G_MISS_NUM AND
1371 p_assignment_rec.resource_id <> l_cur_resource_id) OR
1372 (l_cur_res_type_flag = 'Y' AND
1373 p_assignment_rec.fcst_job_id <> FND_API.G_MISS_NUM AND
1374 p_assignment_rec.fcst_job_id <> l_cur_fcst_job_id) OR
1375 (l_cur_orgn_flag = 'Y' AND
1376 p_assignment_rec.expenditure_organization_id <> FND_API.G_MISS_NUM AND
1377 p_assignment_rec.expenditure_organization_id <> l_cur_exp_org_id) OR
1378 (l_cur_fin_cat_flag = 'Y' AND
1379 p_assignment_rec.expenditure_type <> FND_API.G_MISS_CHAR AND
1380 p_assignment_rec.expenditure_type <> l_cur_expenditure_type) OR
1381 (l_cur_role_flag = 'Y' AND
1382 p_assignment_rec.project_role_id <> FND_API.G_MISS_NUM AND
1383 p_assignment_rec.project_role_id <> l_cur_project_role_id) OR
1384 (l_cur_role_flag = 'Y' AND
1385 p_assignment_rec.assignment_name <> FND_API.G_MISS_CHAR AND
1386 p_assignment_rec.assignment_name <> l_cur_assignment_name) THEN
1387 --Log Message
1388 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
1389 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_OPEN_ASSIGNMENT_PVT.Update_Open_Assignment.begin'
1390 ,x_msg => 'Mandatory attributes changed'
1391 ,x_log_level => li_message_level);
1392 END IF;
1393
1394 l_fcst_job_id_tmp := l_assignment_rec.fcst_job_id;
1395 IF l_fcst_job_id_tmp = FND_API.G_MISS_NUM THEN
1396 l_fcst_job_id_tmp := NULL;
1397 END IF;
1398
1399 l_expenditure_org_id_tmp := l_assignment_rec.expenditure_organization_id;
1400 IF l_expenditure_org_id_tmp = FND_API.G_MISS_NUM THEN
1401 l_expenditure_org_id_tmp := NULL;
1402 END IF;
1403
1404 l_expenditure_type_tmp := l_assignment_rec.expenditure_type;
1405 IF l_expenditure_type_tmp = FND_API.G_MISS_CHAR THEN
1406 l_expenditure_type_tmp := NULL;
1407 END IF;
1408
1409 l_project_role_id_tmp := l_assignment_rec.project_role_id;
1410 IF l_project_role_id_tmp = FND_API.G_MISS_NUM THEN
1411 l_project_role_id_tmp := NULL;
1412 END IF;
1413
1414 l_assignment_name_tmp := l_assignment_rec.assignment_name;
1415 IF l_assignment_name_tmp = FND_API.G_MISS_CHAR THEN
1416 l_assignment_name_tmp := NULL;
1417 END IF;
1418
1419 IF P_DEBUG_MODE = 'Y' THEN
1420 pa_debug.write(x_module => 'pa.plsql.PA_OPEN_ASSIGNMENT_PVT.Create_Open_Assignment'
1421 ,x_msg => 'proj_id='||p_assignment_rec.project_id||
1422 ' res_format='||l_proj_req_res_format_id||
1423 ' job_id='||l_fcst_job_id_tmp
1424 ,x_log_level => li_message_level);
1425 pa_debug.write(x_module => 'pa.plsql.PA_OPEN_ASSIGNMENT_PVT.Create_Open_Assignment'
1426 ,x_msg => 'org_id='||l_expenditure_org_id_tmp||
1427 ' exp_type='||l_expenditure_type_tmp||
1428 ' role_id='||l_project_role_id_tmp||
1429 ' named_role='||l_assignment_name_tmp
1430 ,x_log_level => li_message_level);
1431 END IF;
1432
1433 l_assignment_rec.resource_list_member_id :=
1434 PA_PLANNING_RESOURCE_UTILS.DERIVE_RESOURCE_LIST_MEMBER (
1435 p_project_id => p_assignment_rec.project_id
1436 ,p_res_format_id => l_cur_res_format_id
1437 ,p_job_id => l_fcst_job_id_tmp
1438 ,p_organization_id => l_expenditure_org_id_tmp
1439 ,p_expenditure_type => l_expenditure_type_tmp
1440 ,p_project_role_id => l_project_role_id_tmp
1441 ,p_named_role => l_assignment_name_tmp);
1442 --Log Message
1443 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
1444 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_OPEN_ASSIGNMENT_PVT.Update_Open_Assignment.begin'
1445 ,x_msg => 'new resource list member id='||l_assignment_rec.resource_list_member_id
1446 ,x_log_level => li_message_level);
1447 END IF;
1448
1449 IF l_assignment_rec.resource_list_member_id IS NOT NULL THEN
1450
1451 -- 1. change the resource list member in pa_resource_assignments
1452 -- on the linked task assignments in all working versions.
1453
1454 -- if original resource list member on the team role <>
1455 -- the rlm returned from the derive API above
1456 IF l_assignment_rec.resource_list_member_id <> l_cur_resource_list_member_id THEN
1457
1458 OPEN get_linked_res_asgmts;
1459 FETCH get_linked_res_asgmts
1460 BULK COLLECT INTO l_task_assignment_id_tbl,
1461 l_task_version_id_tbl,
1462 l_budget_version_id_tbl,
1463 l_struct_version_id_tbl;
1464 CLOSE get_linked_res_asgmts;
1465
1466 /* bug 3730480 - remove call to get_resource_defaults
1467
1468 -- get default resource attributes of the new rlm
1469 l_resource_list_members_tbl.extend(1);
1470 l_resource_list_members_tbl(1) := l_assignment_rec.resource_list_member_id;
1471 pa_planning_resource_utils.get_resource_defaults (
1472 P_resource_list_members => l_resource_list_members_tbl
1473 ,P_project_id => l_assignment_rec.project_id
1474 ,X_resource_class_flag => l_resource_class_flag_tbl
1475 ,X_resource_class_code => l_resource_class_code_tbl
1476 ,X_resource_class_id => l_resource_class_id_tbl
1477 ,X_res_type_code => l_res_type_code_tbl
1478 ,X_incur_by_res_type => l_incur_by_res_type_tbl
1479 ,X_person_id => l_person_id_tbl
1480 ,X_job_id => l_job_id_tbl
1481 ,X_person_type_code => l_person_type_code_tbl
1482 ,X_named_role => l_named_role_tbl
1483 ,X_bom_resource_id => l_bom_resource_id_tbl
1484 ,X_non_labor_resource => l_non_labor_resource_tbl
1485 ,X_inventory_item_id => l_inventory_item_id_tbl
1486 ,X_item_category_id => l_item_category_id_tbl
1487 ,X_project_role_id => l_project_role_id_tbl
1488 ,X_organization_id => l_organization_id_tbl
1489 ,X_fc_res_type_code => l_fc_res_type_code_tbl
1490 ,X_expenditure_type => l_expenditure_type_tbl
1491 ,X_expenditure_category => l_expenditure_category_tbl
1492 ,X_event_type => l_event_type_tbl
1493 ,X_revenue_category_code => l_revenue_category_code_tbl
1494 ,X_supplier_id => l_supplier_id_tbl
1495 ,X_spread_curve_id => l_spread_curve_id_tbl
1496 ,X_etc_method_code => l_etc_method_code_tbl
1497 ,X_mfc_cost_type_id => l_mfc_cost_type_id_tbl
1498 ,X_incurred_by_res_flag => l_incurred_by_res_flag_tbl
1499 ,X_incur_by_res_class_code => l_incur_by_res_class_code_tbl
1500 ,X_incur_by_role_id => l_incur_by_role_id_tbl
1501 ,X_unit_of_measure => l_unit_of_measure_tbl
1502 ,X_org_id => l_org_id_tbl
1503 ,X_rate_based_flag => l_rate_based_flag_tbl
1504 ,X_rate_expenditure_type => l_rate_expenditure_type_tbl
1505 ,X_rate_func_curr_code => l_rate_func_curr_code_tbl
1506 -- ,X_rate_incurred_by_org_id => l_rate_incurred_by_org_id_tbl
1507 ,X_msg_data => l_msg_data
1508 ,X_msg_count => l_msg_count
1509 ,X_return_status => l_return_status
1510 );
1511 --Log Message
1512 IF P_DEBUG_MODE = 'Y' THEN -- Added Debug Profile Option Check for bug#2674619
1513 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_OPEN_ASSIGNMENT_PVT.Update_Open_Assignment.begin'
1514 ,x_msg => 'Get resource defaults, status='||l_return_status
1515 ,x_log_level => li_message_level);
1516 END IF;
1517
1518 l_named_role := l_named_role_tbl(1);
1519 IF l_named_role IS NULL THEN
1520 l_named_role := l_assignment_rec.assignment_name;
1521 END IF;
1522
1523 bug 3730480 */
1524
1525 -- Invoke Update_Planning_Transaction API
1526 pa_assignments_pvt.Update_Task_Assignments(
1527 p_task_assignment_id_tbl => l_task_assignment_id_tbl
1528 ,p_task_version_id_tbl => l_task_version_id_tbl
1529 ,p_budget_version_id_tbl => l_budget_version_id_tbl
1530 ,p_struct_version_id_tbl => l_struct_version_id_tbl
1531 ,p_project_assignment_id => l_assignment_rec.assignment_id
1532 -- change resource list member
1533 ,p_resource_list_member_id => l_assignment_rec.resource_list_member_id
1534 ,p_named_role => l_assignment_rec.assignment_name
1535 ,p_project_role_id => l_assignment_rec.project_role_id
1536 ,x_return_status => l_return_status
1537 );
1538 --Log Message
1539 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
1540 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_OPEN_ASSIGNMENT_PVT.Update_Open_Assignment.begin'
1541 ,x_msg => 'Update_task_assignments, status='||l_return_status
1542 ,x_log_level => li_message_level);
1543 END IF;
1544
1545 END IF;--IF l_assignment_rec.resource_list_member_id <> l_cur_resource_list_member_id THEN
1546
1547
1548 -- 2. get all unlinked task assignment using the same planning
1549 -- resource in the working version and link them to
1550 -- this team role. Also, stamp the assignment name on the
1551 -- task assignment's named_role field
1552 OPEN get_unlinked_res_asgmts;
1553 FETCH get_unlinked_res_asgmts
1554 BULK COLLECT INTO l_task_assignment_id_tbl,
1555 l_task_version_id_tbl,
1556 l_budget_version_id_tbl,
1557 l_struct_version_id_tbl;
1558 CLOSE get_unlinked_res_asgmts;
1559
1560 pa_assignments_pvt.Update_Task_Assignments(
1561 p_task_assignment_id_tbl => l_task_assignment_id_tbl
1562 ,p_task_version_id_tbl => l_task_version_id_tbl
1563 ,p_budget_version_id_tbl => l_budget_version_id_tbl
1564 ,p_struct_version_id_tbl => l_struct_version_id_tbl
1565 -- change project_assignment_id to this assignment_id
1566 ,p_project_assignment_id => l_assignment_rec.assignment_id
1567 ,p_resource_list_member_id => l_assignment_rec.resource_list_member_id
1568 -- change the named role to this assignment name
1569 ,p_named_role => l_assignment_rec.assignment_name
1570 ,p_project_role_id => l_assignment_rec.project_role_id
1571 ,x_return_status => l_return_status
1572 );
1573 --Log Message
1574 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
1575 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_OPEN_ASSIGNMENT_PVT.Update_Open_Assignment.begin'
1576 ,x_msg => 'Update_task_assignments, status='||l_return_status
1577 ,x_log_level => li_message_level);
1578 END IF;
1579
1580 ELSE --IF l_assignment_rec.resource_list_member_id IS NOT NULL THEN
1581
1582 -- break the link between this team role and associated task assignments
1583 OPEN get_linked_res_asgmts;
1584 FETCH get_linked_res_asgmts
1585 BULK COLLECT INTO l_task_assignment_id_tbl,
1586 l_task_version_id_tbl,
1587 l_budget_version_id_tbl,
1588 l_struct_version_id_tbl;
1589 CLOSE get_linked_res_asgmts;
1590
1591 -- change project_assignment_id to NULL
1592 IF l_cur_role_flag = 'Y' THEN
1593 pa_assignments_pvt.Update_Task_Assignments(
1594 p_task_assignment_id_tbl => l_task_assignment_id_tbl
1595 ,p_task_version_id_tbl => l_task_version_id_tbl
1596 ,p_budget_version_id_tbl => l_budget_version_id_tbl
1597 ,p_struct_version_id_tbl => l_struct_version_id_tbl
1598 ,p_project_assignment_id => -1
1599 ,x_return_status => l_return_status
1600 );
1601 ELSE
1602 pa_assignments_pvt.Update_Task_Assignments(
1603 p_task_assignment_id_tbl => l_task_assignment_id_tbl
1604 ,p_task_version_id_tbl => l_task_version_id_tbl
1605 ,p_budget_version_id_tbl => l_budget_version_id_tbl
1606 ,p_struct_version_id_tbl => l_struct_version_id_tbl
1607 ,p_project_assignment_id => -1
1608 ,p_named_role => FND_API.G_MISS_CHAR
1609 ,x_return_status => l_return_status
1610 );
1611 END IF;
1612
1613 --Log Message
1614 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
1615 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_OPEN_ASSIGNMENT_PVT.Update_Open_Assignment.begin'
1616 ,x_msg => 'Update_task_assignments, status='||l_return_status
1617 ,x_log_level => li_message_level);
1618 END IF;
1619
1620 END IF; --IF l_assignment_rec.resource_list_member_id IS NOT NULL THEN
1621
1622 -- IF mandatory attributes are NOT changed
1623 ELSIF p_assignment_rec.assignment_name <> FND_API.G_MISS_CHAR AND
1624 p_assignment_rec.assignment_name <> l_cur_assignment_name THEN
1625
1626 --Log Message
1627 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
1628 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_OPEN_ASSIGNMENT_PVT.Update_Open_Assignment.begin'
1629 ,x_msg => 'Mandatory attributes not changed'
1630 ,x_log_level => li_message_level);
1631 END IF;
1632
1633 OPEN get_linked_res_asgmts;
1634 FETCH get_linked_res_asgmts
1635 BULK COLLECT INTO l_task_assignment_id_tbl,
1636 l_task_version_id_tbl,
1637 l_budget_version_id_tbl,
1638 l_struct_version_id_tbl;
1639 CLOSE get_linked_res_asgmts;
1640
1641 -- change named_role to p_assignment_rec.assignment_name
1642 pa_assignments_pvt.Update_Task_Assignments(
1643 p_task_assignment_id_tbl => l_task_assignment_id_tbl
1644 ,p_task_version_id_tbl => l_task_version_id_tbl
1645 ,p_budget_version_id_tbl => l_budget_version_id_tbl
1646 ,p_struct_version_id_tbl => l_struct_version_id_tbl
1647 ,p_named_role => l_assignment_rec.assignment_name
1648 ,p_project_role_id => l_assignment_rec.project_role_id
1649 ,x_return_status => l_return_status
1650 );
1651 --Log Message
1652 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
1653 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_OPEN_ASSIGNMENT_PVT.Update_Open_Assignment.begin'
1654 ,x_msg => 'Update_task_assignments, status='||l_return_status
1655 ,x_log_level => li_message_level);
1656 END IF;
1657
1658 END IF; -- IF mandatory attributes are changed
1659
1660 END IF; -- IF l_cur_resource_list_member_id IS NOT NULL ...
1661
1662 --
1663 -- Check that mandatory inputs for Open Assignment record are not null:
1664 --
1665
1666 -- Check p_assignment_id IS NOT NULL
1667 IF p_assignment_rec.assignment_id IS NULL THEN
1668 PA_UTILS.Add_Message( p_app_short_name => 'PA'
1669 ,p_msg_name => 'PA_ASGN_ID_REQUIRED_FOR_ASG'
1670 ,p_token1 => 'ASGNTYPE'
1671 ,p_value1 => l_req_text);
1672 PA_ASSIGNMENTS_PUB.g_error_exists := FND_API.G_TRUE;
1673 END IF;
1674
1675
1676 --the following validation not required for updating an assignment.
1677
1678
1679 -- Check p_assignment_name is not null
1680 IF p_assignment_rec.assignment_name IS NULL THEN
1681 PA_UTILS.Add_Message( p_app_short_name => 'PA'
1682 ,p_msg_name => 'PA_ASGN_NAME_REQUIRED_FOR_ASG'
1683 ,p_token1 => 'ASGNTYPE'
1684 ,p_value1 => l_req_text);
1685 PA_ASSIGNMENTS_PUB.g_error_exists := FND_API.G_TRUE;
1686 END IF;
1687
1688
1689 --Status code updates and start/end date updates for TEMPLATE REQUIREMENTS ONLY are allowed through this
1690 --API - status updates and start/end date updates to PROJECT REQUIREMENTS must go through the schedule
1691 --page. If this is a template requirement then validate the next allowable status and that
1692 --start date <= end date
1693
1694 IF l_assignment_rec.project_id IS NULL or l_assignment_rec.project_id = FND_API.G_MISS_NUM THEN
1695
1696 OPEN assignment_status_code_csr;
1697 FETCH assignment_status_code_csr INTO l_old_status_code
1698 ,l_old_start_date
1699 ,l_old_end_date;
1700 CLOSE assignment_status_code_csr;
1701
1702 IF l_old_status_code <> l_assignment_rec.status_code THEN
1703 --
1704 -- Check if the new status code is a valid next status code
1705 --
1706 IF ('Y' <> PA_PROJECT_STUS_UTILS.Allow_Status_Change( o_status_code => l_old_status_code
1707 ,n_status_code => l_assignment_rec.status_code))THEN
1708 PA_UTILS.Add_Message( p_app_short_name => 'PA'
1709 ,p_msg_name => 'PA_ASGN_INV_NEXT_STATUS_CODE');
1710 PA_ASSIGNMENTS_PUB.g_error_exists := FND_API.G_TRUE;
1711 END IF;
1712
1713 END IF;
1714
1715 --start date <= end_date validation
1716 IF l_assignment_rec.start_date > l_assignment_rec.end_date THEN
1717 PA_UTILS.Add_Message( p_app_short_name => 'PA'
1718 ,p_msg_name => 'PA_INVALID_START_DATE');
1719 PA_ASSIGNMENTS_PUB.g_error_exists := FND_API.G_TRUE;
1720 END IF;
1721
1722 END IF; --project id is null
1723 --
1724 --Check p_work_type_id IS NOT NULL
1725 --
1726 IF l_assignment_rec.work_type_id IS NULL THEN
1727 --dbms_output.put_line('WORK TYPE INVALID');
1728 PA_UTILS.Add_Message( p_app_short_name => 'PA'
1729 ,p_msg_name => 'PA_WORK_TYPE_REQUIRED_FOR_ASGN' );
1730 PA_ASSIGNMENTS_PUB.g_error_exists := FND_API.G_TRUE;
1731
1732 END IF;
1733
1734
1735 --No updates to assignment dates / status allowed through the
1736 --Update Assignment API. Must use Schedule APIs.
1737
1738 -- Get the location id for the p_location_id for the given location parameters
1739 -- If the location does not already exsists, then create it
1740 --However, if country code is not available then no need to get_location at all.
1741
1742 IF (p_location_country_code IS NOT NULL AND p_location_country_code <> FND_API.G_MISS_CHAR) THEN
1743
1744 --dbms_output.put_line('location code is '||p_location_country_code);
1745 PA_LOCATION_UTILS.Get_Location( p_city => p_location_city
1746 ,p_region => p_location_region
1747 ,p_country_code => p_location_country_code
1748 ,x_location_id => l_assignment_rec.location_id
1749 ,x_error_message_code => l_error_message_code
1750 ,x_return_status => l_return_status );
1751
1752 --dbms_output.put_line('location id is '||l_assignment_rec.location_id);
1753 --dbms_output.put_line('return status is '||l_return_status);
1754
1755 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1756 PA_UTILS.Add_Message ( p_app_short_name => 'PA'
1757 ,p_msg_name => l_error_message_code );
1758 PA_ASSIGNMENTS_PUB.g_error_exists := FND_API.G_TRUE;
1759 END IF;
1760 END IF;
1761
1762 /*
1763 -- commenting this out for mass update
1764 -- should not null out location id if
1765 -- l_assignment_rec.location_id = FND_API.G_MISS_NUM
1766
1767 -- for single update, if location_country_name is passed in as null
1768 -- then null out location id (in pa_assignment_pub)
1769
1770 --Bug 1795160: when user empty the location fields, the location id need to be nulled out.
1771 --If in self-service mode, and still no location id by now, then set it to NULL
1772 IF l_assignment_rec.location_id = FND_API.G_MISS_NUM AND PA_STARTUP.G_Calling_Application = 'SELF_SERVICE' THEN
1773 l_assignment_rec.location_id := NULL;
1774 END IF;
1775 */
1776
1777 --dbms_output.put_line('validate only = '||p_validate_only);
1778 --dbms_output.put_line('error exists = '||PA_ASSIGNMENTS_PUB.g_error_exists);
1779
1780 IF p_validate_only = FND_API.G_FALSE AND PA_ASSIGNMENTS_PUB.g_error_exists = FND_API.G_FALSE
1781 THEN
1782
1783 --Log Message
1784 IF (P_DEBUG_MODE = 'Y') THEN
1785 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_OPEN_ASSIGNMENT_PVT.Update_Open_Assignment.update_row'
1786 ,x_msg => 'Update Assignment Record in the table'
1787 ,x_log_level => li_message_level);
1788 END IF;
1789
1790 --dbms_output.put_line('calling PA_PROJECT_ASSIGNMENTS_PKG.Update_Row');
1791
1792
1793 PA_PROJECT_ASSIGNMENTS_PKG.Update_Row
1794 ( p_assignment_row_id => l_assignment_rec.assignment_row_id
1795 ,p_assignment_id => l_assignment_rec.assignment_id
1796 ,p_record_version_number => l_assignment_rec.record_version_number
1797 ,p_assignment_name => l_assignment_rec.assignment_name
1798 ,p_assignment_type => l_assignment_rec.assignment_type
1799 ,p_multiple_status_flag => l_assignment_rec.multiple_status_flag
1800 ,p_status_code => l_assignment_rec.status_code
1801 ,p_staffing_priority_code => l_assignment_rec.staffing_priority_code
1802 ,p_project_role_id => l_assignment_rec.project_role_id
1803 ,p_description => l_assignment_rec.description
1804 ,p_start_date => l_assignment_rec.start_date
1805 ,p_end_date => l_assignment_rec.end_date
1806 ,p_assignment_effort => l_assignment_rec.assignment_effort
1807 ,p_source_assignment_id => l_assignment_rec.source_assignment_id
1808 ,p_min_resource_job_level => l_assignment_rec.min_resource_job_level
1809 ,p_max_resource_job_level => l_assignment_rec.max_resource_job_level
1810 ,p_additional_information => l_assignment_rec.additional_information
1811 ,p_work_type_id => l_assignment_rec.work_type_id
1812 ,p_revenue_currency_code => l_assignment_rec.revenue_currency_code
1813 ,p_revenue_bill_rate => l_assignment_rec.revenue_bill_rate
1814 ,p_markup_percent => l_assignment_rec.markup_percent
1815 ,p_extension_possible => l_assignment_rec.extension_possible
1816 ,p_expense_owner => l_assignment_rec.expense_owner
1817 ,p_expense_limit => l_assignment_rec.expense_limit
1818 ,p_expense_limit_currency_code => l_assignment_rec.expense_limit_currency_code
1819 ,p_fcst_tp_amount_type => l_assignment_rec.fcst_tp_amount_type
1820 ,p_fcst_job_id => l_assignment_rec.fcst_job_id
1821 ,p_fcst_job_group_id => l_assignment_rec.fcst_job_group_id
1822 ,p_expenditure_org_id => l_assignment_rec.expenditure_org_id
1823 ,p_expenditure_organization_id => l_assignment_rec.expenditure_organization_id
1824 ,p_expenditure_type_class => l_assignment_rec.expenditure_type_class
1825 ,p_expenditure_type => l_assignment_rec.expenditure_type
1826 ,p_location_id => l_assignment_rec.location_id
1827 ,p_calendar_type => l_assignment_rec.calendar_type
1828 ,p_calendar_id => l_assignment_rec.calendar_id
1829 ,p_comp_match_weighting => l_assignment_rec.comp_match_weighting
1830 ,p_avail_match_weighting => l_assignment_rec.avail_match_weighting
1831 ,p_job_level_match_weighting => l_assignment_rec.job_level_match_weighting
1832 ,p_search_min_availability => l_assignment_rec.search_min_availability
1833 ,p_search_country_code => l_assignment_rec.search_country_code
1834 ,p_search_exp_org_struct_ver_id => l_assignment_rec.search_exp_org_struct_ver_id
1835 ,p_search_exp_start_org_id => l_assignment_rec.search_exp_start_org_id
1836 ,p_search_min_candidate_score => l_assignment_rec.search_min_candidate_score
1837 ,p_enable_auto_cand_nom_flag => l_assignment_rec.enable_auto_cand_nom_flag
1838 ,p_bill_rate_override => l_assignment_rec.bill_rate_override
1839 ,p_bill_rate_curr_override => l_assignment_rec.bill_rate_curr_override
1840 ,p_markup_percent_override => l_assignment_rec.markup_percent_override
1841 ,p_discount_percentage => l_assignment_rec.discount_percentage -- Bug 2590938
1842 ,p_rate_disc_reason_code => l_assignment_rec.rate_disc_reason_code -- Bug 2590938
1843 ,p_tp_rate_override => l_assignment_rec.tp_rate_override
1844 ,p_tp_currency_override => l_assignment_rec.tp_currency_override
1845 ,p_tp_calc_base_code_override => l_assignment_rec.tp_calc_base_code_override
1846 ,p_tp_percent_applied_override => l_assignment_rec.tp_percent_applied_override
1847 ,p_staffing_owner_person_id => l_assignment_rec.staffing_owner_person_id
1848 ,p_resource_list_member_id => l_assignment_rec.resource_list_member_id -- FP-M Development -- FP.M Development
1849 ,p_attribute_category => l_assignment_rec.attribute_category
1850 ,p_attribute1 => l_assignment_rec.attribute1
1851 ,p_attribute2 => l_assignment_rec.attribute2
1852 ,p_attribute3 => l_assignment_rec.attribute3
1853 ,p_attribute4 => l_assignment_rec.attribute4
1854 ,p_attribute5 => l_assignment_rec.attribute5
1855 ,p_attribute6 => l_assignment_rec.attribute6
1856 ,p_attribute7 => l_assignment_rec.attribute7
1857 ,p_attribute8 => l_assignment_rec.attribute8
1858 ,p_attribute9 => l_assignment_rec.attribute9
1859 ,p_attribute10 => l_assignment_rec.attribute10
1860 ,p_attribute11 => l_assignment_rec.attribute11
1861 ,p_attribute12 => l_assignment_rec.attribute12
1862 ,p_attribute13 => l_assignment_rec.attribute13
1863 ,p_attribute14 => l_assignment_rec.attribute14
1864 ,p_attribute15 => l_assignment_rec.attribute15
1865 ,x_return_status => x_return_status
1866 );
1867
1868 END IF;
1869
1870
1871 -- Reset the error stack when returning to the calling program
1872 PA_DEBUG.Reset_Err_Stack;
1873
1874 -- If g_error_exists is TRUE then set the x_return_status to 'E'
1875
1876 IF PA_ASSIGNMENTS_PUB.g_error_exists = FND_API.G_TRUE THEN
1877
1878 x_return_status := FND_API.G_RET_STS_ERROR;
1879
1880 END IF;
1881
1882 EXCEPTION
1883 WHEN OTHERS THEN
1884
1885 -- Set the excetption Message and the stack
1886 FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_OPEN_ASSIGNMENT_PVT.Update_Open_Assignment'
1887 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
1888 --
1889 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1890 RAISE; -- This is optional depending on the needs
1891 END Update_Open_Assignment;
1892
1893
1894
1895 PROCEDURE Delete_Open_Assignment
1896 ( p_assignment_row_id IN ROWID
1897 ,p_assignment_id IN pa_project_assignments.assignment_id%TYPE := FND_API.G_MISS_NUM
1898 ,p_record_version_number IN NUMBER := FND_API.G_MISS_NUM
1899 ,p_calling_module IN VARCHAR2 := FND_API.G_MISS_NUM
1900 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
1901 ,p_validate_only IN VARCHAR2 := FND_API.G_TRUE
1902 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1903 )
1904 IS
1905
1906 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
1907 l_msg_count NUMBER;
1908 l_msg_count_sum NUMBER;
1909 l_msg_data VARCHAR2(2000);
1910 l_competency_tbl PA_HR_COMPETENCE_UTILS.competency_tbl_typ;
1911 l_no_of_competencies NUMBER;
1912 l_error_message_code fnd_new_messages.message_name%TYPE;
1913 l_check_id_flag VARCHAR2(1);
1914 l_action_set_id NUMBER;
1915 l_record_version_number NUMBER;
1916
1917 -- get advertisement action set details
1918 CURSOR get_action_set IS
1919 SELECT action_set_id, record_version_number
1920 FROM pa_action_sets
1921 WHERE object_id = p_assignment_id
1922 AND object_type = 'OPEN_ASSIGNMENT'
1923 AND action_set_type_code = 'ADVERTISEMENT'
1924 AND status_code <> 'DELETED';
1925
1926 BEGIN
1927 -- Initialize the Error Stack
1928 PA_DEBUG.set_err_stack('PA_OPEN_ASSIGNMENT_PVT.Delete_Open_Assignment');
1929
1930 --Log Message
1931 IF (P_DEBUG_MODE = 'Y') THEN
1932 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_OPEN_ASSIGNMENT_PVT.Delete_Open_Assignment.begin'
1933 ,x_msg => 'Beginning of Delete_Open_Assignment'
1934 ,x_log_level => li_message_level);
1935 END IF;
1936
1937 -- Initialize the return status to success
1938 x_return_status := FND_API.G_RET_STS_SUCCESS;
1939
1940 IF p_validate_only = FND_API.G_FALSE AND PA_ASSIGNMENTS_PUB.g_error_exists <> FND_API.G_TRUE THEN
1941
1942 IF p_calling_module <> 'TEMPLATE_REQUIREMENT' THEN
1943
1944 --Log Message
1945 IF (P_DEBUG_MODE = 'Y') THEN
1946 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_OPEN_ASSIGNMENT_PVT.Delete_Open_Assignment.delete_schedule'
1947 ,x_msg => 'Deleting Open Assignment schedules'
1948 ,x_log_level => li_message_level);
1949 END IF;
1950
1951 -- Delete all the child shedule records before deleting the parent open assignment record
1952 -- unless this is a template requirement. No schedules exists for template requirements.
1953
1954 PA_SCHEDULE_PVT.Delete_Asgn_Schedules
1955 ( p_assignment_id => p_assignment_id
1956 ,p_perm_delete => FND_API.G_TRUE --Added for bug 4389372
1957 ,x_return_status => l_return_status
1958 ,x_msg_count => l_msg_count
1959 ,x_msg_data => l_msg_data
1960 );
1961
1962 END IF; --calling module <> template requirement
1963
1964 --Delete the advertisement action set
1965 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
1966
1967 -- Bug 2497298: PJ.J:B3:GEN: DELETE TEAM TEMPLATE GIVES SQLEXCEPTION
1968 -- Do not delete action set if requirement does not have an
1969 -- associated action set
1970 OPEN get_action_set;
1971 FETCH get_action_set INTO l_action_set_id, l_record_version_number;
1972 IF get_action_set%NOTFOUND THEN
1973 CLOSE get_action_set;
1974 ELSE
1975
1976 PA_ACTION_SETS_PUB.Delete_Action_Set (
1977 p_init_msg_list => FND_API.G_FALSE -- 5130421
1978 ,p_action_set_id => l_action_set_id
1979 ,p_action_set_type_code => 'ADVERTISEMENT'
1980 ,p_object_id => p_assignment_id
1981 ,p_object_type => 'OPEN_ASSIGNMENT'
1982 ,p_record_version_number => l_record_version_number
1983 ,p_commit => p_commit
1984 ,p_validate_only => p_validate_only
1985 ,x_return_status => l_return_status
1986 ,x_msg_count => l_msg_count
1987 ,x_msg_data => l_msg_data );
1988
1989 CLOSE get_action_set;
1990 END IF;
1991
1992 END IF;
1993
1994
1995 --Delete related candidate records
1996 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
1997 PA_CANDIDATE_PUB.Delete_Candidates (p_assignment_id => p_assignment_id
1998 ,x_return_status => l_return_status
1999 ,x_msg_count => l_msg_count
2000 ,x_msg_data => l_msg_data );
2001 END IF;
2002
2003 --l_return_status is initialized to success.
2004 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
2005
2006 --dbms_output.put_line('Getting Competency Table');
2007 --Delete the Competencies associated with the assignment
2008 --Get the Competencies table first
2009
2010 PA_HR_COMPETENCE_UTILS.Get_Competencies
2011 ( p_object_name => 'OPEN_ASSIGNMENT'
2012 ,p_object_id => p_assignment_id
2013 ,x_competency_tbl => l_competency_tbl
2014 ,x_no_of_competencies => l_no_of_competencies
2015 ,x_error_message_code => l_error_message_code
2016 ,x_return_status => l_return_status);
2017
2018 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
2019
2020 --Delete the Competencies Elements
2021
2022 l_msg_count_sum := 0;
2023
2024 --dbms_output.put_line('Start Deleting Competencies');
2025 /* A temporary fix:
2026 Need to avoid the LOV ID clearing check implemented in most validation packages.
2027 Since only the ids are passed in and not the names.
2028 */
2029
2030 l_check_id_flag := PA_STARTUP.G_Check_ID_Flag;
2031 IF PA_STARTUP.G_Calling_Application = 'SELF_SERVICE' THEN
2032 PA_STARTUP.G_Check_ID_Flag := 'N';
2033 END IF;
2034
2035 --If the competency table is not empty for this assignment then delete
2036 IF (l_competency_tbl.FIRST IS NOT NULL) THEN
2037
2038 FOR i IN l_competency_tbl.FIRST .. l_competency_tbl.LAST LOOP
2039
2040 --Log Message
2041 IF (P_DEBUG_MODE = 'Y') THEN
2042 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_OPEN_ASSIGNMENT_PVT.Delete_Open_Assignment.del_competency'
2043 ,x_msg => 'Deleting Requirement Competencies.'
2044 ,x_log_level => li_message_level);
2045 END IF;
2046
2047 PA_COMPETENCE_PUB.Delete_Competence_Element
2048 ( p_object_name => 'OPEN_ASSIGNMENT'
2049 ,p_object_id => p_assignment_id
2050 ,p_competence_id => l_competency_tbl(i).competence_id
2051 ,p_element_id => l_competency_tbl(i).competence_element_id
2052 ,p_object_version_number => l_competency_tbl(i).object_version_number
2053 ,x_return_status => l_return_status
2054 ,x_msg_count => l_msg_count
2055 ,x_msg_data => l_msg_data);
2056
2057 l_msg_count_sum := l_msg_count_sum + l_msg_count;
2058
2059 END LOOP; --loop through competence table
2060
2061 END IF; --competency tbl is not null
2062
2063 IF (l_msg_count_sum > 0 ) THEN
2064 PA_ASSIGNMENTS_PUB.g_error_exists := FND_API.G_TRUE;
2065 ELSE
2066
2067 --Log Message
2068 IF (P_DEBUG_MODE = 'Y') THEN
2069 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_OPEN_ASSIGNMENT_PVT.Delete_Open_Assignment.del_asgmt'
2070 ,x_msg => 'Deleting Requirement Record'
2071 ,x_log_level => li_message_level);
2072 END IF;
2073
2074
2075 -- Delete the master record
2076 PA_PROJECT_ASSIGNMENTS_PKG.Delete_Row
2077 ( p_assignment_row_id => p_assignment_row_id
2078 ,p_assignment_id => p_assignment_id
2079 ,p_record_version_number => p_record_version_number
2080 ,x_return_status => x_return_status);
2081
2082 END IF;--end of l_msg_count_sum > 0
2083
2084 --set the global check_id_flag back to the orignal
2085 PA_STARTUP.G_Check_ID_Flag := l_check_id_flag;
2086
2087 ELSE
2088 PA_UTILS.Add_Message( p_app_short_name => 'PA'
2089 ,p_msg_name => l_error_message_code);
2090 PA_ASSIGNMENTS_PUB.g_error_exists := FND_API.G_TRUE;
2091 END IF; --success getting the competencies
2092
2093 ELSE
2094
2095 PA_UTILS.Add_Message( p_app_short_name => 'PA'
2096 ,p_msg_name => 'PA_FAILED_TO_DEL_ASGN_SCHEDULE');
2097 PA_ASSIGNMENTS_PUB.g_error_exists := FND_API.G_TRUE;
2098
2099 END IF; --success deleting the schedule
2100
2101 END IF; --validate only is false and no errors exist
2102
2103
2104 -- If g_error_exists is TRUE then set the x_return_status to 'E'
2105
2106 IF PA_ASSIGNMENTS_PUB.g_error_exists = FND_API.G_TRUE THEN
2107
2108 x_return_status := FND_API.G_RET_STS_ERROR;
2109
2110 END IF;
2111
2112 EXCEPTION
2113 WHEN OTHERS THEN
2114
2115 -- Set the excetption Message and the stack
2116 FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_OPEN_ASSIGNMENT_PVT.Delete_Open_Assignment'
2117 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
2118 --
2119 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2120 RAISE;
2121 END Delete_Open_Assignment;
2122 --
2123 --
2124 END pa_open_assignment_pvt;