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