[Home] [Help]
PACKAGE BODY: APPS.PA_TEAM_TEMPLATES_PVT
Source
1 PACKAGE BODY pa_team_templates_pvt AS
2 /*$Header: PARTPVTB.pls 120.1 2005/08/19 17:01:20 mwasowic noship $*/
3 --
4 P_DEBUG_MODE varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
5 PROCEDURE Start_Apply_Team_Template_WF
6 (p_team_template_id_tbl IN PA_TEAM_TEMPLATES_PUB.team_template_id_tbl
7 ,p_project_id IN pa_projects_all.project_id%TYPE
8 ,p_project_start_date IN pa_projects_all.start_date%TYPE
9 ,p_team_start_date IN pa_team_templates.team_start_date%TYPE := FND_API.G_MISS_DATE
10 ,p_use_project_location IN VARCHAR2 := 'N'
11 ,p_project_location_id IN pa_projects_all.location_id%TYPE := NULL
12 ,p_use_project_calendar IN VARCHAR2 := 'N'
13 ,p_project_calendar_id IN pa_projects_all.calendar_id%TYPE := NULL
14 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
15 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
16 )
17 IS
18
19 l_item_type VARCHAR2(8) := 'PARAPTEM';
20 l_item_key VARCHAR2(2000);
21 l_team_template_id pa_team_templates.team_template_id%TYPE;
22 l_project_name pa_projects_all.name%TYPE;
23 l_project_number pa_projects_all.segment1%TYPE;
24 l_team_template_name pa_team_templates.team_template_name%TYPE;
25 l_save_threshold NUMBER;
26 l_nextval NUMBER;
27
28 CURSOR get_project_name_and_number IS
29 SELECT name, segment1
30 FROM pa_projects_all
31 WHERE project_id = p_project_id;
32
33 CURSOR get_team_template_name(l_team_template_id NUMBER) IS
34 SELECT team_template_name
35 FROM pa_team_templates
36 WHERE team_template_id = l_team_template_id;
37
38 BEGIN
39 --initialize return status to Success
40 x_return_status := FND_API.G_RET_STS_SUCCESS;
41
42 -- Setting thresold value to run the process in background
43 l_save_threshold := wf_engine.threshold;
44
45 IF wf_engine.threshold < 0 THEN
46 wf_engine.threshold := l_save_threshold;
47 END IF;
48 wf_engine.threshold := -1;
49
50 FOR l_index IN p_team_template_id_tbl.FIRST..p_team_template_id_tbl.LAST LOOP
51
52 SELECT pa_apply_team_template_wf_s.nextval
53 INTO l_nextval
54 FROM dual;
55
56 --get the item key --- **USE SEQUENCE**
57 l_item_key := p_team_template_id_tbl(l_index).team_template_id||'-'||l_nextval;
58
59 -- Creating the work flow process
60 WF_ENGINE.CreateProcess( itemtype => l_item_type,
61 itemkey => l_item_key,
62 process => 'PA_APPLY_TEAM_TEMPLATE') ;
63
64 -- Setting the attribute value for team template id
65 WF_ENGINE.SetItemAttrNumber( itemtype => l_item_type,
66 itemkey => l_item_key,
67 aname => 'TEAM_TEMPLATE_ID',
68 avalue => p_team_template_id_tbl(l_index).team_template_id);
69
70 -- Setting the attribute value for project id
71 WF_ENGINE.SetItemAttrNumber( itemtype => l_item_type,
72 itemkey => l_item_key,
73 aname => 'PROJECT_ID',
74 avalue => p_project_id);
75
76 -- Setting the attribute value for project start date
77 WF_ENGINE.SetItemAttrDate( itemtype => l_item_type,
78 itemkey => l_item_key,
79 aname => 'PROJECT_START_DATE',
80 avalue => p_project_start_date);
81
82 -- Setting the attribute value for team start date
83 WF_ENGINE.SetItemAttrDate( itemtype => l_item_type,
84 itemkey => l_item_key,
85 aname => 'TEAM_START_DATE',
86 avalue => p_team_start_date);
87
88 -- Setting the attribute value for use project location
89 WF_ENGINE.SetItemAttrText( itemtype => l_item_type,
90 itemkey => l_item_key,
91 aname => 'USE_PROJECT_LOCATION',
92 avalue => p_use_project_location);
93
94 -- Setting the attribute value for project location id
95 WF_ENGINE.SetItemAttrNumber( itemtype => l_item_type,
96 itemkey => l_item_key,
97 aname => 'PROJECT_LOCATION_ID',
98 avalue => p_project_location_id);
99
100 -- Setting the attribute value for use project calendar
101 WF_ENGINE.SetItemAttrText( itemtype => l_item_type,
102 itemkey => l_item_key,
103 aname => 'USE_PROJECT_CALENDAR',
104 avalue => p_use_project_calendar);
105
106 -- Setting the attribute value for project calendar id
107 WF_ENGINE.SetItemAttrNumber( itemtype => l_item_type,
108 itemkey => l_item_key,
109 aname => 'PROJECT_CALENDAR_ID',
110 avalue => p_project_calendar_id);
111
112 -- Setting the attribute value for project calendar id
113 WF_ENGINE.SetItemAttrText( itemtype => l_item_type,
114 itemkey => l_item_key,
115 aname => 'APPLIER_USER_NAME',
116 avalue => FND_GLOBAL.user_name);
117
118 ---- Code added for bug 3919767 for setting the context values for running WF process
119
120
121 WF_ENGINE.SetItemAttrNumber( itemtype => l_item_type
122 , itemkey => l_item_key
123 , aname => 'USER_ID'
124 , avalue => FND_GLOBAL.USER_ID);
125
126 WF_ENGINE.SetItemAttrNumber( itemtype => l_item_type
127 , itemkey => l_item_key
128 , aname => 'RESPONSIBILITY_ID'
129 , avalue => FND_GLOBAL.RESP_ID);
130
131 WF_ENGINE.SetItemAttrNumber( itemtype => l_item_type
132 , itemkey => l_item_key
133 , aname => 'APPLICATION_ID'
134 , avalue => FND_GLOBAL.RESP_APPL_ID);
135
136 ----- Code addition end for bug 3919767
137
138 --get the project name/number to be used in the notification.
139 OPEN get_project_name_and_number;
140 FETCH get_project_name_and_number INTO l_project_name, l_project_number;
141 CLOSE get_project_name_and_number;
142
143 WF_ENGINE.SetItemAttrText( itemtype => l_item_type,
144 itemkey => l_item_key,
145 aname => 'PROJECT_NAME',
146 avalue => l_project_name);
147
148 WF_ENGINE.SetItemAttrText( itemtype => l_item_type,
149 itemkey => l_item_key,
150 aname => 'PROJECT_NUMBER',
151 avalue => l_project_number);
152
153 --get the team template name to be used in the notification.
154 OPEN get_team_template_name(p_team_template_id_tbl(l_index).team_template_id);
155 FETCH get_team_template_name INTO l_team_template_name;
156 CLOSE get_team_template_name;
157
158 WF_ENGINE.SetItemAttrText( itemtype => l_item_type,
159 itemkey => l_item_key,
160 aname => 'TEAM_TEMPLATE_NAME',
161 avalue => l_team_template_name);
162
163 -- start the workflow process
164 WF_ENGINE.StartProcess( itemtype => l_item_type,
165 itemkey => l_item_key);
166
167
168 UPDATE pa_team_templates
169 SET workflow_in_progress_flag = 'Y'
170 WHERE team_template_id = p_team_template_id_tbl(l_index).team_template_id;
171
172 END LOOP;
173
174 --Setting the original value
175 wf_engine.threshold := l_save_threshold;
176
177 EXCEPTION
178 WHEN OTHERS THEN
179
180 --Setting the original value
181 wf_engine.threshold := l_save_threshold;
182
183 -- Set the excetption Message and the stack
184 FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_TEAM_TEMPLATES_PVT.Apply_Team_Template'
185 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
186 --
187 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
188 RAISE;
189
190 END Start_Apply_Team_Template_WF;
191
192
193 PROCEDURE Apply_Team_Template_WF
194 (p_item_type IN VARCHAR2,
195 p_item_key IN VARCHAR2,
196 p_actid IN NUMBER,
197 p_funcmode IN VARCHAR2,
198 p_result OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
199 IS
200
201 l_team_template_id pa_team_templates.team_template_id%TYPE;
202 l_project_id pa_projects_all.project_id%TYPE;
203 l_project_start_date pa_projects_all.start_date%TYPE;
204 l_team_start_date pa_team_templates.team_start_date%TYPE;
205 l_use_project_location VARCHAR2(1);
206 l_project_location_id pa_projects_all.location_id%TYPE;
207 l_use_project_calendar VARCHAR2(1);
208 l_project_calendar_id pa_projects_all.calendar_id%TYPE;
209 l_return_status VARCHAR2(1);
210 l_msg_count NUMBER;
211 l_count NUMBER;
212 l_msg_data fnd_new_messages.message_text%TYPE;
213 l_msg_index_out NUMBER;
214 l_err_code NUMBER;
215 l_err_stage VARCHAR2(2000);
216 l_err_stack VARCHAR2(2000);
217 l_name VARCHAR2(1);
218
219 PRAGMA AUTONOMOUS_TRANSACTION;
220
221 BEGIN
222
223 --initialize the message stack.
224 FND_MSG_PUB.initialize;
225
226 IF (p_funcmode = 'RUN') THEN
227
228 --set the check ID flag to 'N'
229 PA_STARTUP.G_Check_ID_Flag := 'N';
230
231 l_team_template_id := WF_ENGINE.GetItemAttrNumber (itemtype => p_item_type,
232 itemkey => p_item_key,
233 aname => 'TEAM_TEMPLATE_ID');
234 --set the team template id token global variable so that the template
235 --name will show up in the error message.
236 PA_ASSIGNMENT_UTILS.g_team_template_id := l_team_template_id;
237
238 l_project_id := WF_ENGINE.GetItemAttrNumber (itemtype => p_item_type,
239 itemkey => p_item_key,
240 aname => 'PROJECT_ID');
241
242 l_project_start_date := WF_ENGINE.GetItemAttrDate (itemtype => p_item_type,
243 itemkey => p_item_key,
244 aname => 'PROJECT_START_DATE');
245
246 l_team_start_date := WF_ENGINE.GetItemAttrDate (itemtype => p_item_type,
247 itemkey => p_item_key,
248 aname => 'TEAM_START_DATE');
249
250 l_use_project_location := WF_ENGINE.GetItemAttrText (itemtype => p_item_type,
251 itemkey => p_item_key,
252 aname => 'USE_PROJECT_LOCATION');
253
254 l_project_location_id := WF_ENGINE.GetItemAttrNumber (itemtype => p_item_type,
255 itemkey => p_item_key,
256 aname => 'PROJECT_LOCATION_ID');
257
258 l_use_project_calendar := WF_ENGINE.GetItemAttrText (itemtype => p_item_type,
259 itemkey => p_item_key,
260 aname => 'USE_PROJECT_CALENDAR');
261
262 l_project_calendar_id := WF_ENGINE.GetItemAttrNumber (itemtype => p_item_type,
263 itemkey => p_item_key,
264 aname => 'PROJECT_CALENDAR_ID');
265 /*
266 dbms_output.put_line('l_team_template_id = '||l_team_template_id);
267 dbms_output.put_line('l_project_id = '||l_project_id);
268 dbms_output.put_line('l_project_start_date = '||l_project_start_date);
269 dbms_output.put_line('l_team_start_date = '||l_team_start_date);
270 */
271
272 PA_TEAM_TEMPLATES_PVT.Apply_Team_Template( p_team_template_id => l_team_template_id,
273 p_project_id => l_project_id,
274 p_project_start_date => l_project_start_date,
275 p_team_start_date => l_team_start_date,
276 p_use_project_location => l_use_project_location,
277 p_project_location_id => l_project_location_id,
278 p_use_project_calendar => l_use_project_calendar,
279 p_project_calendar_id => l_project_calendar_id,
280 p_commit => FND_API.G_TRUE,
281 x_return_status => l_return_status);
282
283 l_msg_count := FND_MSG_PUB.Count_Msg;
284
285 IF l_msg_count = 0 THEN
286 p_result := 'COMPLETE:S';
287 ELSE
288 p_result := 'COMPLETE:F';
289 FOR l_count IN 1..l_msg_count LOOP
290 FND_MSG_PUB.get( p_encoded => FND_API.G_FALSE
291 ,p_msg_index => l_count
292 ,p_data => l_msg_data
293 ,p_msg_index_out => l_msg_index_out);
294
295 WF_ENGINE.SetItemAttrText( itemtype => p_item_type
296 , itemkey => p_item_key
297 , aname => 'ERROR_MESSAGE_'||l_count
298 , avalue => l_count||'. '||l_msg_data);
299
300 EXIT WHEN l_msg_count=20;
301
302 END LOOP;
303 END IF; --msg count=0
304
305 --update the workflow in progress flag if no other workflows are active or pending
306 --for this team template.
307
308 /* Bug 3271891 - Added AND condition for item_key <> p_item_key along with
309 activity_status_code <> 'DEFERRED' in the below query */
310
311 UPDATE PA_TEAM_TEMPLATES
312 SET workflow_in_progress_flag = 'N'
313 WHERE team_template_id = l_team_template_id
314 AND NOT EXISTS(
315 SELECT 'Y'
316 FROM wf_item_activity_statuses_v
317 WHERE item_type = p_item_type
318 AND substr(item_key, 1, instr(item_key, '-')) = substr(p_item_key, 1, instr(p_item_key, '-'))
319 AND ((activity_status_code = 'DEFERRED' AND item_key <> p_item_key) OR
320 (activity_name='START_APPLY_TEAM_TEMPLATE_WF' AND activity_status_code = 'ACTIVE' AND item_key <> p_item_key)))
321 ;
322
323 --INSERT INTO NOTIFICATIONS TABLE
324 PA_WORKFLOW_UTILS.Insert_WF_Processes
325 (p_wf_type_code => 'APPLY_TEAM_TEMPLATE'
326 ,p_item_type => p_item_type
327 ,p_item_key => p_item_key
328 ,p_entity_key1 => to_char(l_project_id)
329 ,p_description => NULL
330 ,p_err_code => l_err_code
331 ,p_err_stage => l_err_stage
332 ,p_err_stack => l_err_stack);
333
334
335 END IF; --p_funcmode = RUN
336
337 COMMIT;
338
339 EXCEPTION
340
341 WHEN OTHERS THEN
342 -- Set the excetption Message and the stack
343 FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_TEAM_TEMPLATES_PVT.Apply_Team_Template'
344 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
345 RAISE;
346
347
348 END Apply_Team_Template_WF;
349
350
351 PROCEDURE Apply_Team_Template
352 (p_team_template_id IN pa_team_templates.team_template_id%TYPE
353 ,p_project_id IN pa_projects_all.project_id%TYPE
354 ,p_project_start_date IN pa_projects_all.start_date%TYPE
355 ,p_team_start_date IN pa_team_templates.team_start_date%TYPE := FND_API.G_MISS_DATE
356 ,p_use_project_location IN VARCHAR2 := 'N'
357 ,p_project_location_id IN pa_projects_all.location_id%TYPE := NULL
358 ,p_use_project_calendar IN VARCHAR2 := 'N'
359 ,p_project_calendar_id IN pa_projects_all.calendar_id%TYPE := NULL
360 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
361 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
362 )
363 IS
364
365 l_new_assignment_id pa_project_assignments.assignment_id%TYPE;
366 l_assignment_number pa_project_assignments.assignment_number%TYPE;
367 l_assignment_row_id ROWID;
368 l_resource_id pa_resources.resource_id%TYPE;
369 l_return_status VARCHAR2(1);
370 l_error_message_code fnd_new_messages.message_name%TYPE;
371 l_msg_count NUMBER;
372 l_msg_data fnd_new_messages.message_text%TYPE;
373 l_index NUMBER;
374 l_project_calendar_id pa_projects_all.calendar_id%TYPE;
375 l_project_location_id pa_projects_all.location_id%TYPE;
376 l_number_of_days NUMBER;
377 l_project_subteam_id pa_project_subteams.project_subteam_id%TYPE;
378 l_subteam_name pa_project_subteams.name%TYPE;
379 l_subteam_row_id ROWID;
380 l_team_start_date pa_team_templates.team_start_date%TYPE;
381 l_template_requirement_rec PA_ASSIGNMENTS_PUB.assignment_rec_type;
382 l_person_name PER_PEOPLE_F.full_name%TYPE;
383 l_err_msg_code VARCHAR2(80);
384
385 CURSOR get_project_location_and_cal IS
386 SELECT calendar_id, location_id
387 FROM pa_projects_all
388 WHERE project_id = p_project_id;
389
390 CURSOR get_template_req_attributes(p_team_template_id NUMBER) IS
391 SELECT assignment_name
392 ,'OPEN_ASSIGNMENT'
393 ,assignment_type
394 ,status_code
395 ,staffing_priority_code
396 ,project_role_id
397 ,description
398 ,start_date
399 ,end_date
400 ,extension_possible
401 ,min_resource_job_level
402 ,max_resource_job_level
403 ,additional_information
404 ,location_id
405 ,work_type_id
406 ,expense_owner
407 ,expense_limit
408 ,expense_limit_currency_code
409 ,expenditure_org_id
410 ,expenditure_organization_id
411 ,expenditure_type_class
412 ,expenditure_type
413 ,calendar_type
414 ,calendar_id
415 ,assignment_id --used for source_assignment_id
416 ,assignment_template_id --used to tieback to the team template the requirement was created from
417 ,attribute_category
418 ,attribute1
419 ,attribute2
420 ,attribute3
421 ,attribute4
422 ,attribute5
423 ,attribute6
424 ,attribute7
425 ,attribute8
426 ,attribute9
427 ,attribute10
428 ,attribute11
429 ,attribute12
430 ,attribute13
431 ,attribute14
432 ,attribute15
433 FROM pa_project_assignments
434 WHERE assignment_template_id = p_team_template_id
435 AND template_flag = 'Y';
436
437 CURSOR get_team_template_subteams(p_team_template_id NUMBER) IS
438 SELECT name
439 FROM pa_project_subteams
440 WHERE object_type = 'PA_TEAM_TEMPLATES'
441 AND object_id = p_team_template_id;
442
443 CURSOR get_team_start_date(p_team_template_id NUMBER) IS
444 SELECT team_start_date
445 FROM pa_team_templates
446 WHERE team_template_id = p_team_template_id;
447
448 BEGIN
449
450 -- Initialize the Error Stack
451 PA_DEBUG.init_err_stack('PA_TEAM_TEMPLATES_PVT.Apply_Team_Template');
452
453 -- Initialize the return status to success
454 x_return_status := FND_API.G_RET_STS_SUCCESS;
455
456 -- Issue API savepoint if the transaction is to be committed
457 IF p_commit = FND_API.G_TRUE THEN
458 SAVEPOINT ASG_PVT_APPLY_TEAM_TEMPLATE;
459 END IF;
460
461 --Log Message
462 IF (P_DEBUG_MODE ='Y') THEN
463 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_TEAM_TEMPLATES_PVT.apply_team_template'
464 ,x_msg => 'Beginning of Apply_Team_Template'
465 ,x_log_level => 5);
466 END IF;
467
468 l_project_calendar_id := p_project_calendar_id;
469 l_project_location_id := p_project_location_id;
470
471 --if p_use_project_location = Y or p_use_project_calendar='Y' and the values are not
472 --passed in to the API then get the project calendar/location.
473 IF (p_use_project_location ='Y' AND p_project_location_id IS NULL) OR
474 (p_use_project_calendar='Y' AND p_project_calendar_id IS NULL) THEN
475
476 OPEN get_project_location_and_cal;
477
478 FETCH get_project_location_and_cal INTO l_project_calendar_id, l_project_location_id;
479
480 CLOSE get_project_location_and_cal;
481
482 END IF;
483
484 --validate that a project calendar is defined if p_use_project_calendar='Y'
485 IF p_use_project_calendar ='Y' AND l_project_calendar_id IS NULL THEN
486
487 PA_UTILS.Add_Message( p_app_short_name => 'PA'
488 ,p_msg_name => 'PA_PROJ_CAL_NOT_DEFINED');
489
490 END IF;
491
492 --validate that a project location is defined if p_use_project_location='Y'
493 IF p_use_project_location ='Y' AND l_project_location_id IS NULL THEN
494
495 PA_UTILS.Add_Message( p_app_short_name => 'PA'
496 ,p_msg_name => 'PA_PROJ_LOC_NOT_DEFINED');
497
498 END IF;
499 /*
500 --Loop through the team templates to be applied.
501
502 FOR l_index IN p_team_template_id_tbl.FIRST..p_team_template_id_tbl.LAST LOOP
503 */
504 --set the team template id token global variable so that the template
505 --name will show up in the error message.
506 -- PA_ASSIGNMENT_UTILS.g_team_template_id :=p_team_template_id_tbl(l_index).team_template_id;
507 PA_ASSIGNMENT_UTILS.g_team_template_id :=p_team_template_id;
508
509 --if the team start date was not passed in then get it
510 l_team_start_date := p_team_start_date;
511
512 IF p_team_start_date IS NULL OR p_team_start_date = FND_API.G_MISS_DATE THEN
513
514 OPEN get_team_start_date(p_team_template_id);
515
516 FETCH get_team_start_date INTO l_team_start_date;
517
518 CLOSE get_team_start_date;
519
520 END IF;
521
522 --calculate the difference between the project_start_date and the team
523 --start date. This will be used to determine the requirement start
524 --and end dates.
525 l_number_of_days := p_project_start_date - l_team_start_date;
526
527 --If the template requirement has subteams then check if those subteams already
528 --exist on the project. If they don't, then call API to create it.
529 OPEN get_team_template_subteams(p_team_template_id);
530
531 LOOP
532
533 FETCH get_team_template_subteams INTO l_subteam_name;
534
535 EXIT WHEN get_team_template_subteams%NOTFOUND;
536
537 l_project_subteam_id := NULL;
538
539
540 PA_PROJECT_SUBTEAM_UTILS.Check_Subteam_Name_Or_Id( p_subteam_name => l_subteam_name
541 ,p_object_type => 'PA_PROJECTS'
542 ,p_object_id => p_project_id
543 ,p_check_id_flag => PA_STARTUP.G_Check_ID_Flag
544 ,x_subteam_id => l_project_subteam_id
545 ,x_return_status => l_return_status
546 ,x_error_message_code => l_error_message_code );
547
548 --if the project subteam id returned is null then that subteam does not exist on that
549 --project, so call create subteam API
550 IF l_project_subteam_id IS NULL THEN
551
552 PA_PROJECT_SUBTEAMS_PUB.Create_Subteam(p_subteam_name => l_subteam_name,
553 p_object_type => 'PA_PROJECTS',
554 p_object_id => p_project_id,
555 p_validate_only => FND_API.G_FALSE,
556 p_init_msg_list => FND_API.G_FALSE,
557 x_new_subteam_id => l_project_subteam_id,
558 x_subteam_row_id => l_subteam_row_id,
559 x_return_status => l_return_status,
560 x_msg_count => l_msg_count,
561 x_msg_data => l_msg_data);
562
563 END IF;
564
565 END LOOP; --get subteams
566
567 CLOSE get_team_template_subteams;
568
569 --get the template requirements to be copied if there are no
570 --validation errors.
571 IF FND_MSG_PUB.Count_Msg = 0 THEN
572
573 OPEN get_template_req_attributes(p_team_template_id);
574
575 LOOP
576 --not using PA_ASSIGNMENTS_PUB.Copy_Team_Role because API expects the
577 --assignment id to be copied to be passed in. But it would be an extra db hit to
578 --get the assignment ids in this API and then pass them to copy_team_role. So
579 --I'll just get everything here and call create assignment.
580 FETCH get_template_req_attributes INTO l_template_requirement_rec.assignment_name
581 ,l_template_requirement_rec.assignment_type
582 ,l_template_requirement_rec.source_assignment_type
583 ,l_template_requirement_rec.status_code
584 ,l_template_requirement_rec.staffing_priority_code
585 ,l_template_requirement_rec.project_role_id
586 ,l_template_requirement_rec.description
587 ,l_template_requirement_rec.start_date
588 ,l_template_requirement_rec.end_date
589 ,l_template_requirement_rec.extension_possible
590 ,l_template_requirement_rec.min_resource_job_level
591 ,l_template_requirement_rec.max_resource_job_level
592 ,l_template_requirement_rec.additional_information
593 ,l_template_requirement_rec.location_id
594 ,l_template_requirement_rec.work_type_id
595 ,l_template_requirement_rec.expense_owner
596 ,l_template_requirement_rec.expense_limit
597 ,l_template_requirement_rec.expense_limit_currency_code
598 ,l_template_requirement_rec.expenditure_org_id
599 ,l_template_requirement_rec.expenditure_organization_id
600 ,l_template_requirement_rec.expenditure_type_class
601 ,l_template_requirement_rec.expenditure_type
602 ,l_template_requirement_rec.calendar_type
603 ,l_template_requirement_rec.calendar_id
604 ,l_template_requirement_rec.source_assignment_id
605 ,l_template_requirement_rec.assignment_template_id
606 ,l_template_requirement_rec.attribute_category
607 ,l_template_requirement_rec.attribute1
608 ,l_template_requirement_rec.attribute2
609 ,l_template_requirement_rec.attribute3
610 ,l_template_requirement_rec.attribute4
611 ,l_template_requirement_rec.attribute5
612 ,l_template_requirement_rec.attribute6
613 ,l_template_requirement_rec.attribute7
614 ,l_template_requirement_rec.attribute8
615 ,l_template_requirement_rec.attribute9
616 ,l_template_requirement_rec.attribute10
617 ,l_template_requirement_rec.attribute11
618 ,l_template_requirement_rec.attribute12
619 ,l_template_requirement_rec.attribute13
620 ,l_template_requirement_rec.attribute14
621 ,l_template_requirement_rec.attribute15
622 ;
623
624 EXIT WHEN get_template_req_attributes%NOTFOUND;
625
626 --set the assignment id token global variable so that the template
627 --name will show up in the error message.
628 PA_ASSIGNMENT_UTILS.g_team_role_name_token := l_template_requirement_rec.assignment_name;
629
630 --set the project id into the pl/sql record.
631 l_template_requirement_rec.project_id := p_project_id;
632
633 --determine the requirement's start and end dates by adding l_number_of_days to
634 --the template requirement's start and end dates.
635 l_template_requirement_rec.start_date := l_template_requirement_rec.start_date + l_number_of_days;
636
637 l_template_requirement_rec.end_date := l_template_requirement_rec.end_date + l_number_of_days;
638
639
640 --set the project location id if the project location should be used.
641 IF p_use_project_location ='Y' THEN
642
643 l_template_requirement_rec.location_id := l_project_location_id;
644
645 END IF;
646
647 --set the project calendar id if the project calendar should be used.
648 IF p_use_project_calendar = 'Y' THEN
649
650 l_template_requirement_rec.calendar_id := l_project_calendar_id;
651
652 END IF;
653
654 --set default staffing owner
655 pa_assignment_utils.Get_Default_Staffing_Owner
656 ( p_project_id => p_project_id
657 ,p_exp_org_id => null
658 ,x_person_id => l_template_requirement_rec.staffing_owner_person_id
659 ,x_person_name => l_person_name
660 ,x_return_status => x_return_status
661 ,x_error_message_code => l_err_msg_code);
662
663 -- Do I need role list check? Should only be on client side.
664
665 --call API to create the requirement
666 --using mode 'COPY' because the requirement is being copied from the
667 --template requirement - and then the source assignment id will only be
668 --used to get the subteams to copy, and will not be inserted to the db (no link kept).
669 PA_ASSIGNMENTS_PUB.Create_Assignment(p_assignment_rec => l_template_requirement_rec
670 ,p_asgn_creation_mode => 'COPY'
671 ,p_validate_only => FND_API.G_FALSE
672 ,x_new_assignment_id => l_new_assignment_id
673 ,x_assignment_number => l_assignment_number
674 ,x_assignment_row_id => l_assignment_row_id
675 ,x_resource_id => l_resource_id
676 ,x_return_status => l_return_status
677 ,x_msg_count => l_msg_count
678 ,x_msg_data => l_msg_data
679 );
680
681 IF p_commit = FND_API.G_TRUE THEN
682 IF l_return_status <> 'S' THEN
683 ROLLBACK;
684 ELSE COMMIT;
685 END IF;
686 END IF;
687
688
689 END LOOP; --template requirements in 1 team template
690
691 CLOSE get_template_req_attributes;
692
693 END IF; --no errors
694
695 -- END LOOP; --multiple team templates
696
697
698 EXCEPTION
699 WHEN OTHERS THEN
700 IF p_commit = FND_API.G_TRUE THEN
701 ROLLBACK TO ASG_PVT_APPLY_TEAM_TEMPLATE;
702 END IF;
703
704 -- Set the excetption Message and the stack
705 FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_TEAM_TEMPLATES_PVT.Apply_Team_Template'
706 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
707 --
708 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
709 RAISE;
710
711 END Apply_Team_Template;
712
713
714
715 PROCEDURE Create_Team_Template
716 ( p_team_template_rec IN PA_TEAM_TEMPLATES_PUB.team_template_rec
717 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
718 ,p_validate_only IN VARCHAR2 := FND_API.G_TRUE
719 ,x_team_template_id OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
720 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
721 )
722 IS
723
724 l_msg_count NUMBER;
725 l_team_template_rec PA_TEAM_TEMPLATES_PUB.team_template_rec;
726 l_team_template_name_unique VARCHAR2(1);
727
728 BEGIN
729
730 -- Initialize the Error Stack
731 PA_DEBUG.set_err_stack('PA_TEAM_TEMPLATE_PVT.Create_Team_Template');
732
733 --Log Message
734 IF (P_DEBUG_MODE ='Y') THEN
735 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_TEAM_TEMPLATES_PVT.Create_Team_Template.begin'
736 ,x_msg => 'Beginning of the PVT Create_Team_Template'
737 ,x_log_level => 5);
738 END IF;
739
740 -- Initialize the return status to success
741 x_return_status := FND_API.G_RET_STS_SUCCESS;
742
743 --Issue API savepoint if the transaction is to be committed
744 IF p_commit = FND_API.G_TRUE THEN
745 SAVEPOINT ASGN_PVT_CREATE_TEAM_TEMPLATE;
746 END IF;
747
748 l_team_template_rec := p_team_template_rec;
749
750 --validate that team template name is unique
751 l_team_template_name_unique := PA_TEAM_TEMPLATES_UTILS.Is_Team_Template_Name_Unique
752 (p_team_template_name => l_team_template_rec.team_template_name);
753
754 IF l_team_template_name_unique = 'N' THEN
755
756 PA_UTILS.Add_Message( p_app_short_name => 'PA'
757 ,p_msg_name => 'PA_TEAM_TEMPLATE_NAME_INVALID');
758
759 END IF;
760
761 --validate effective start date is before effective end date
762 IF l_team_template_rec.end_date_active IS NOT NULL and l_team_template_rec.end_date_active <> FND_API.G_MISS_DATE AND l_team_template_rec.start_date_active > l_team_template_rec.end_date_active THEN
763 PA_UTILS.Add_Message( p_app_short_name => 'PA'
764 ,p_msg_name => 'PA_INVALID_START_DATE');
765 END IF;
766
767 --If no validation errors then insert the team template.
768 IF p_validate_only = FND_API.G_FALSE AND FND_MSG_PUB.Count_Msg = 0 THEN
769 IF (P_DEBUG_MODE ='Y') THEN
770 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_TEAM_TEMPLATE_PVT.Create_Team_Template.calling_insert_row'
771 ,x_msg => 'calling pa_team_templates.insert_row.'
772 ,x_log_level => 5);
773 END IF;
774
775 PA_TEAM_TEMPLATES_PKG.Insert_Row(
776 p_team_template_name => l_team_template_rec.team_template_name,
777 p_description => l_team_template_rec.description,
778 p_start_date_active => l_team_template_rec.start_date_active,
779 p_end_date_active => l_team_template_rec.end_date_active,
780 p_calendar_id => l_team_template_rec.calendar_id,
781 p_work_type_id => l_team_template_rec.work_type_id,
782 p_role_list_id => l_team_template_rec.role_list_id,
783 p_team_start_date => l_team_template_rec.team_start_date,
784 x_team_template_id => x_team_template_id,
785 x_return_status => x_return_status);
786
787 END IF;
788
789 l_msg_count := FND_MSG_PUB.Count_Msg;
790
791 -- Commit if the flag is set and there is no error
792 IF p_commit = FND_API.G_TRUE AND l_msg_count =0 THEN
793 COMMIT;
794 END IF;
795
796 -- Reset the error stack when returning to the calling program
797 PA_DEBUG.Reset_Err_Stack;
798
799 -- If any errors exist then set the x_return_status to 'E'
800
801 IF l_msg_count>0 THEN
802
803 x_return_status := FND_API.G_RET_STS_ERROR;
804
805 END IF;
806
807
808 EXCEPTION
809 WHEN OTHERS THEN
810 IF p_commit = FND_API.G_TRUE THEN
811 ROLLBACK TO ASGN_PVT_CREATE_TEAM_TEMPLATE;
812 END IF;
813 --
814 -- Set the excetption Message and the stack
815 FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_TEAM_TEMPLATE_PVT.Create_Team_Template'
816 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
817 --
818 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
819 RAISE;
820
821 END Create_Team_Template;
822
823
824 PROCEDURE Update_Team_Template
825 ( p_team_template_rec IN PA_TEAM_TEMPLATES_PUB.team_template_rec
826 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
827 ,p_validate_only IN VARCHAR2 := FND_API.G_TRUE
828 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
829 )
830 IS
831
832 l_msg_count NUMBER;
833 l_team_template_rec PA_TEAM_TEMPLATES_PUB.team_template_rec;
834
835 BEGIN
836
837 -- Initialize the Error Stack
838 PA_DEBUG.set_err_stack('PA_TEAM_TEMPLATE_PVT.Update_Team_Template');
839
840 --Log Message
841 IF (P_DEBUG_MODE ='Y') THEN
842 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_TEAM_TEMPLATES_PVT.Update_Team_Template.begin'
843 ,x_msg => 'Beginning of the PVT Update_Team_Template'
844 ,x_log_level => 5);
845 END IF;
846
847 -- Initialize the return status to success
848 x_return_status := FND_API.G_RET_STS_SUCCESS;
849
850 --Issue API savepoint if the transaction is to be committed
851 IF p_commit = FND_API.G_TRUE THEN
852 SAVEPOINT ASGN_PVT_Update_TEAM_TEMPLATE;
853 END IF;
854
855 l_team_template_rec := p_team_template_rec;
856
857 --validate effective start date is before effective end date
858 IF l_team_template_rec.end_date_active IS NOT NULL and l_team_template_rec.end_date_active <> FND_API.G_MISS_DATE AND l_team_template_rec.start_date_active > l_team_template_rec.end_date_active THEN
859 PA_UTILS.Add_Message( p_app_short_name => 'PA'
860 ,p_msg_name => 'PA_INVALID_START_DATE');
861 END IF;
862
863 --If no validation errors then insert the team template.
864 IF p_validate_only = FND_API.G_FALSE AND FND_MSG_PUB.Count_Msg = 0 THEN
865 IF (P_DEBUG_MODE ='Y') THEN
866 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_TEAM_TEMPLATE_PVT.Update_Team_Template.calling_update_row'
867 ,x_msg => 'calling pa_team_templates.update_row.'
868 ,x_log_level => 5);
869 END IF;
870
871 PA_TEAM_TEMPLATES_PKG.Update_Row(
872 p_team_template_id => l_team_template_rec.team_template_id,
873 p_team_template_name => l_team_template_rec.team_template_name,
874 p_record_version_number => l_team_template_rec.record_version_number,
875 p_description => l_team_template_rec.description,
876 p_start_date_active => l_team_template_rec.start_date_active,
877 p_end_date_active => l_team_template_rec.end_date_active,
878 p_calendar_id => l_team_template_rec.calendar_id,
879 p_work_type_id => l_team_template_rec.work_type_id,
880 p_role_list_id => l_team_template_rec.role_list_id,
881 p_team_start_date => l_team_template_rec.team_start_date,
882 p_workflow_in_progress_flag => l_team_template_rec.workflow_in_progress_flag,
883 x_return_status => x_return_status);
884
885 END IF;
886
887 l_msg_count := FND_MSG_PUB.Count_Msg;
888
889 -- Commit if the flag is set and there is no error
890 IF p_commit = FND_API.G_TRUE AND l_msg_count =0 THEN
891 COMMIT;
892 END IF;
893
894 -- Reset the error stack when returning to the calling program
895 PA_DEBUG.Reset_Err_Stack;
896
897 -- If any errors exist then set the x_return_status to 'E'
898
899 IF l_msg_count>0 THEN
900
901 x_return_status := FND_API.G_RET_STS_ERROR;
902
903 END IF;
904
905
906 EXCEPTION
907 WHEN OTHERS THEN
908 IF p_commit = FND_API.G_TRUE THEN
909 ROLLBACK TO ASGN_PVT_UPDATE_TEAM_TEMPLATE;
910 END IF;
911 --
912 -- Set the excetption Message and the stack
913 FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_TEAM_TEMPLATE_PVT.Update_Team_Template'
914 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
915 --
916 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
917 RAISE;
918
919 END Update_Team_Template;
920
921 PROCEDURE Delete_Team_Template
922 ( p_team_template_id IN pa_team_templates.team_template_id%TYPE
923 ,p_record_version_number IN NUMBER
924 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
925 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
926 )
927 IS
928
929 l_return_status VARCHAR2(1);
930 l_msg_count NUMBER;
931 l_msg_data fnd_new_messages.message_text%TYPE;
932 l_check_team_template_in_use VARCHAR2(1);
933
934 CURSOR check_team_template_in_use IS
935 SELECT 'X'
936 FROM pa_project_assignments
937 WHERE assignment_template_id = p_team_template_id
938 AND template_flag <> 'Y';
939
940 CURSOR get_template_req_details IS
941 SELECT assignment_id,
942 record_version_number,
943 assignment_type
944 FROM pa_project_assignments
945 WHERE assignment_template_id = p_team_template_id
946 AND template_flag = 'Y';
947
948 BEGIN
949
950 -- Initialize the Error Stack
951 PA_DEBUG.init_err_stack('PA_TEAM_TEMPLATE_PVT.Delete_Assignment');
952
953 -- Initialize the return status to success
954 x_return_status := FND_API.G_RET_STS_SUCCESS;
955
956 -- Issue API savepoint if the transaction is to be committed
957 IF p_commit = FND_API.G_TRUE THEN
958 SAVEPOINT ASGN_PVT_DELETE_TEAM_TEMPLATE;
959 END IF;
960
961 --Log Message
962 IF (P_DEBUG_MODE ='Y') THEN
963 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_TEAM_TEMPLATE_PVT.Delete_Team_Template.begin'
964 ,x_msg => 'Beginning of Delete Team Template PVT.'
965 ,x_log_level => 5);
966 END IF;
967
968 --check if the team template is in use
969 OPEN check_team_template_in_use;
970
971 FETCH check_team_template_in_use INTO l_check_team_template_in_use;
972
973 CLOSE check_team_template_in_use;
974
975 IF l_check_team_template_in_use = 'X' THEN
976
977 PA_UTILS.Add_Message( p_app_short_name => 'PA'
978 ,p_msg_name => 'PA_TEAM_TEMPLATE_IN_USE');
979
980 ELSE
981
982 --delete the subteams belonging to this team template
983 --Log Message
984 IF (P_DEBUG_MODE ='Y') THEN
985 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_TEAM_TEMPLATE_PVT.Delete_Team_Template'
986 ,x_msg => 'calling delete subteam'
987 ,x_log_level => 5);
988 END IF;
989 --don't pass subteam id and this API will delete all subteams belonging to the
990 --team template.
991 PA_PROJECT_SUBTEAMS_PUB.Delete_Subteam_By_Obj
992 (p_init_msg_list => FND_API.G_FALSE,
993 p_validate_only => FND_API.G_FALSE,
994 p_object_type => 'PA_TEAM_TEMPLATES',
995 p_object_id => p_team_template_id,
996 x_return_status => l_return_status,
997 x_msg_count => l_msg_count,
998 x_msg_data => l_msg_data);
999
1000 --delete the template requirements
1001 --Log Message
1002 IF (P_DEBUG_MODE ='Y') THEN
1003 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_TEAM_TEMPLATE_PVT.Delete_Team_Template'
1004 ,x_msg => 'calling delete assignment (template req)'
1005 ,x_log_level => 5);
1006 END IF;
1007 FOR l_template_requirements IN get_template_req_details LOOP
1008
1009 PA_ASSIGNMENTS_PUB.Delete_Assignment(
1010 p_assignment_id => l_template_requirements.assignment_id,
1011 p_record_version_number => l_template_requirements.record_version_number,
1012 p_assignment_type => l_template_requirements.assignment_type,
1013 p_calling_module => 'TEMPLATE_REQUIREMENT',
1014 p_validate_only => FND_API.G_FALSE,
1015 x_return_status => l_return_status,
1016 x_msg_count => l_msg_count,
1017 x_msg_data => l_msg_data);
1018
1019 END LOOP;
1020
1021
1022 --delete the team template header
1023
1024 --Log Message
1025 IF (P_DEBUG_MODE ='Y') THEN
1026 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_TEAM_TEMPLATE_PVT.Delete_Team_Template'
1027 ,x_msg => 'calling delete_row'
1028 ,x_log_level => 5);
1029 END IF;
1030 PA_TEAM_TEMPLATES_PKG.Delete_Row(p_team_template_id => p_team_template_id
1031 ,p_record_version_number => p_record_version_number
1032 ,x_return_status => x_return_status);
1033
1034 END IF; --check team template in use.
1035
1036
1037 -- If errors exist then set the x_return_status to 'E'
1038
1039 IF FND_MSG_PUB.Count_Msg > 0 THEN
1040
1041 x_return_status := FND_API.G_RET_STS_ERROR;
1042
1043 END IF;
1044
1045 EXCEPTION
1046 WHEN OTHERS THEN
1047 IF p_commit = FND_API.G_TRUE THEN
1048 ROLLBACK TO ASGN_PVT_DELETE_ASSIGNMENTT;
1049 END IF;
1050
1051 -- Set the excetption Message and the stack
1052 FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_TEAM_TEMPLATE_PVT.Delete_Team_Template'
1053 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
1054 --
1055 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1056 RAISE;
1057
1058 END Delete_Team_Template;
1059
1060 END pa_team_templates_pvt;