1 PACKAGE BODY PA_SCHEDULE_PUB as
2 /* $Header: PARGPUBB.pls 120.11.12010000.2 2008/10/20 10:29:25 vgovvala ship $ */
3
4 l_out_of_range_date EXCEPTION; -- Exception variable for raising the exception when date is out of range
5 l_out_of_range_from_date EXCEPTION; -- Exception variable for raising the exception when date is out of range
6 l_out_of_range_to_date EXCEPTION; -- Exception variable for raising the exception when date is out of range
7 l_empty_tab_record EXCEPTION; -- Variable to raise the exception if the passing table of records is empty
8 l_x_return_status VARCHAR2(50);
9 l_from_to_date_null EXCEPTION; -- This exception is raise when the start date or end date is null
10 l_asgn_stus_not_for_proj_stus EXCEPTION; -- Exception variable for raising the exception when the assignment status is not allowed for the project status
11
12 -- procedure : update_schedule
13 -- Purpose : This procedure will change the schedule records of the assignments passed in.
14 -- It can accept either one assignment ID or an assignment ID array.
15 --
16 -- Input parameters
17 -- Parameters Type Required Description
18 -- --------------------------- ------ -------- --------------------------------------------------------
19 -- P_Project_Id NUMBER YES project id of the associated calendar
20 -- P_Calendar_Id NUMBER NO Id for that calendar which is associated to this assignment
21
22 PROCEDURE update_schedule
23 ( p_project_id IN NUMBER
24 ,p_mass_update_flag IN VARCHAR2 := FND_API.G_FALSE
25 ,p_exception_type_code IN VARCHAR2
26 ,p_record_version_number IN NUMBER := NULL
27 ,p_assignment_id IN NUMBER := NULL
28 ,p_assignment_id_array IN SYSTEM.PA_NUM_TBL_TYPE := NULL
29 ,p_change_start_date IN DATE := NULL
30 ,p_change_end_date IN DATE := NULL
31 ,p_requirement_status_code IN VARCHAR2 := NULL
32 ,p_assignment_status_code IN VARCHAR2 := NULL
33 ,p_monday_hours IN NUMBER := NULL
34 ,p_tuesday_hours IN NUMBER := NULL
35 ,p_wednesday_hours IN NUMBER := NULL
36 ,p_thursday_hours IN NUMBER := NULL
37 ,p_friday_hours IN NUMBER := NULL
38 ,p_saturday_hours IN NUMBER := NULL
39 ,p_sunday_hours IN NUMBER := NULL
40 ,p_non_working_day_flag IN VARCHAR2 := 'N'
41 ,p_change_hours_type_code IN VARCHAR2 := NULL
42 ,p_hrs_per_day IN NUMBER := NULL
43 ,p_calendar_percent IN NUMBER := NULL
44 ,p_change_calendar_type_code IN VARCHAR2 := NULL
45 ,p_change_calendar_name IN VARCHAR2 := NULL
46 ,p_change_calendar_id IN NUMBER := NULL
47 ,p_duration_shift_type_code IN VARCHAR2 := NULL
48 ,p_duration_shift_unit_code IN VARCHAR2 := NULL
49 ,p_number_of_shift IN NUMBER := NULL
50 ,p_last_row_flag IN VARCHAR2 := 'Y'
51 ,p_change_start_date_tbl IN SYSTEM.PA_DATE_TBL_TYPE := NULL
52 ,p_change_end_date_tbl IN SYSTEM.PA_DATE_TBL_TYPE := NULL
53 ,p_monday_hours_tbl IN SYSTEM.PA_NUM_TBL_TYPE := NULL
54 ,p_tuesday_hours_tbl IN SYSTEM.PA_NUM_TBL_TYPE := NULL
55 ,p_wednesday_hours_tbl IN SYSTEM.PA_NUM_TBL_TYPE := NULL
56 ,p_thursday_hours_tbl IN SYSTEM.PA_NUM_TBL_TYPE := NULL
57 ,p_friday_hours_tbl IN SYSTEM.PA_NUM_TBL_TYPE := NULL
58 ,p_saturday_hours_tbl IN SYSTEM.PA_NUM_TBL_TYPE := NULL
59 ,p_sunday_hours_tbl IN SYSTEM.PA_NUM_TBL_TYPE := NULL
60 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
61 ,p_validate_only IN VARCHAR2 := FND_API.G_TRUE
62 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
63 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
64 ,x_msg_data OUT NOCOPY VARCHAR2 ) --File.Sql.39 bug 4440895
65 IS
66
67 l_err_msg_code fnd_new_messages.message_name%TYPE;
68 l_updated_calendar_id NUMBER;
69 l_msg_data VARCHAR2(2000);
70 l_msg_count NUMBER;
71 l_msg_index_out NUMBER;
72 l_change_calendar_id NUMBER;
73 l_valid_assign_start_flag VARCHAR2(1) := 'Y'; -- Bug 6411422
74 l_profile_begin_date DATE; -- Bug 6411422
75
76 BEGIN
77 -- Initialize the return status to success
78 x_return_status := FND_API.G_RET_STS_SUCCESS ;
79
80 --Clear the global PL/SQL message table
81 FND_MSG_PUB.initialize;
82
83 -- Initialize the Error Stack
84 PA_DEBUG.init_err_stack('PA_SCHEDULE_PUB.update_schedule');
85
86 -- Issue API savepoint if the transaction is to be committed
87 IF (p_commit = FND_API.G_TRUE) THEN
88 SAVEPOINT SCH_PUB_UPDATE_SCH;
89 END IF;
90
91 -- Bug 6411422
92 l_valid_assign_start_flag := PA_PROJECT_DATES_UTILS.IS_VALID_ASSIGN_START_DATE( p_project_id => p_project_id,
93 p_assign_start_date => p_change_start_date ) ;
94 IF ( l_valid_assign_start_flag = 'Y' )
95 THEN -- Bug 6411422
96
97 pa_schedule_utils.debug('pa.plsql.pa_schedule_pub', 'Start online validation', 6);
98 ----------------------------------------------------------------------------------------------
99 --
100 -- On Line Validation
101 --
102 ----------------------------------------------------------------------------------------------
103
104 -- If this api has been called from the page which has start_date, end_date input
105 IF (p_exception_type_code = 'CHANGE_DURATION' OR p_exception_type_code = 'CHANGE_HOURS' OR
106 p_exception_type_code = 'CHANGE_WORK_PATTERN' OR p_exception_type_code = 'CHANGE_STATUS') THEN
107
108 -- If p_exception_type_code = 'CHANGE_DURATION', at least one of start_date or end_date
109 -- should not be null. The reason is that if both are null, actually it wouldn't update anything.
110 IF (p_exception_type_code = 'CHANGE_DURATION' AND
111 p_change_start_date IS NULL AND p_change_end_date IS NULL) THEN
112 PA_UTILS.Add_Message ('PA', 'PA_SCH_FROM_OR_TO_DATE_NULL');
113 RAISE FND_API.G_EXC_ERROR;
114 END IF;
115
116 -- If p_exception_type_code = 'CHANGE_HOURS' or 'CHANGE_WORK_PATTERN' or 'CHANGE_STATUS',
117 -- End date or Start date should not be null.
118 IF ( (p_exception_type_code = 'CHANGE_HOURS' OR p_exception_type_code = 'CHANGE_WORK_PATTERN' OR
119 p_exception_type_code = 'CHANGE_STATUS') AND
120 (p_change_start_date IS NULL OR p_change_end_date IS NULL) )THEN
121 PA_UTILS.Add_Message ('PA', 'PA_SCH_FROM_TO_DATE_NULL');
122 RAISE FND_API.G_EXC_ERROR;
123 END IF;
124
125 -- If this api has been called from the page which has start_date, end_date input, call
126 -- the validation date procedure. It will validate the date i.e. start_date should not be greater
127 -- than end_date. If end date date greater than start_date, then it will return l_x_return_status as error.
128 PA_SCHEDULE_UTILS.validate_date_range (p_change_start_date, p_change_end_date, l_x_return_status, l_err_msg_code);
129
130 -- If validate_date_range fails, put error message into error stack and stop to process.
131 IF (l_x_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
132 PA_UTILS.Add_Message ('PA', l_err_msg_code);
133 RAISE FND_API.G_EXC_ERROR;
134 END IF;
135 END IF;
136
137 -- If p_exception_type_code = 'SHIFT_DURATION' then throw exception in following cases.
138 -- single update: if 'status for new days'(p_assignment_status_code) is null. (-> this field
139 -- should be required on front end)
140 -- mass update: if both 'p_requirement_status_code' and 'p_assignment_status_code' are null.
141 IF ( (p_exception_type_code = 'SHIFT_DURATION' OR p_exception_type_code = 'CHANGE_STATUS') AND
142 (p_requirement_status_code is NULL AND p_assignment_status_code is NULL AND
143 p_mass_update_flag = FND_API.G_TRUE) ) THEN
144 PA_UTILS.Add_Message ('PA', 'PA_SCH_ASGN_STATUS_NULL');
145 RAISE FND_API.G_EXC_ERROR;
146 END IF;
147
148 -- validations when p_exception_type_code = 'CHANGE_HOURS'
149 IF (p_exception_type_code = 'CHANGE_HOURS') THEN
150
151 -- Checking that if the we want to change the hours by taking the hours type code as HOURS
152 -- then the hours per day should not be null and should not beyond the 0 to 24 hours
153 -- same with the PERCENTAGE then calendar percentage should not be null and should not beyond the
154 -- the 0 to 100 percent
155 IF (p_change_hours_type_code = 'HOURS') THEN
156
157 IF (p_hrs_per_day IS NULL) THEN
158 PA_UTILS.Add_Message ('PA', 'PA_SCH_HOURS_NULL');
159 RAISE FND_API.G_EXC_ERROR;
160 ELSIF (p_hrs_per_day NOT BETWEEN 0 AND 24 ) THEN
161 PA_UTILS.Add_Message ('PA', 'PA_SCH_HOURS_OUT_OF_RANGE');
162 RAISE FND_API.G_EXC_ERROR;
163 END IF;
164
165 ELSIF (p_change_hours_type_code = 'PERCENTAGE') THEN
166 IF (p_calendar_percent IS NULL) THEN
167 PA_UTILS.Add_Message ('PA', 'PA_SCH_PERCENTAGE_NULL');
168 RAISE FND_API.G_EXC_ERROR;
169 ELSIF (p_calendar_percent NOT BETWEEN 0 AND 100 ) THEN
170 PA_UTILS.Add_Message ('PA', 'PA_SCH_PERCENTAGE_OUT_OF_RANGE');
171 RAISE FND_API.G_EXC_ERROR;
172 END IF;
173
174 -- Value/ID validation for calendar_name/id entered through 'OTHER' text input field on
175 -- the Update Hours Of Days screen.
176 IF (p_change_calendar_type_code = 'OTHER') THEN
177
178 -- IF both calendar_name and calendar_id are null, error out.
179 IF (p_change_calendar_name is NULL AND p_change_calendar_id IS NULL) THEN
180 PA_UTILS.Add_Message ('PA', 'PA_OTHER_CALENDAR_NULL');
181 RAISE FND_API.G_EXC_ERROR;
182 END IF;
183
184 PA_CALENDAR_UTILS.Check_Calendar_Name_Or_Id(
185 p_calendar_id => p_change_calendar_id
186 ,p_calendar_name => p_change_calendar_name
187 ,p_check_id_flag => 'N'
188 ,x_calendar_id => l_change_calendar_id
189 ,x_return_status => l_x_return_status
190 ,x_error_message_code => l_err_msg_code);
191
192 -- If calendar_name/id validation fails, put error message into error stack.
193 IF (l_x_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
194 PA_UTILS.Add_Message ('PA', l_err_msg_code);
195 RAISE FND_API.G_EXC_ERROR;
196 END IF;
197 END IF; -- IF(p_change_calendar_type_code = 'OTHER')
198 END IF; -- ELSIF (p_change_hours_type_code = 'PERCENTAGE')
199
200 END IF; -- p_exception_type_code = 'CHANGE_HOURS'
201
202 -- validations when p_exception_type_code = 'CHANGE_WORK_PATTERN'
203 IF (p_exception_type_code = 'CHANGE_WORK_PATTERN') THEN
204
205 -- If all of working hours are null, error out.
206 IF (p_monday_hours IS NULL AND p_tuesday_hours IS NULL AND
207 p_wednesday_hours IS NULL AND p_thursday_hours IS NULL AND
208 p_friday_hours IS NULL AND p_saturday_hours IS NULL AND
209 p_sunday_hours IS NULL) THEN
210 PA_UTILS.Add_Message ('PA', 'PA_SCH_HOURS_ALL_NULL');
211 RAISE FND_API.G_EXC_ERROR;
212 END IF;
213
214 -- if anyday hours is not null and not valid number(between 0 and 24), throw an errors.
215 IF ( (p_monday_hours IS NOT NULL AND (p_monday_hours NOT BETWEEN 0 AND 24)) OR
216 (p_tuesday_hours IS NOT NULL AND (p_tuesday_hours NOT BETWEEN 0 AND 24)) OR
217 (p_wednesday_hours IS NOT NULL AND (p_wednesday_hours NOT BETWEEN 0 AND 24)) OR
218 (p_thursday_hours IS NOT NULL AND (p_thursday_hours NOT BETWEEN 0 AND 24)) OR
219 (p_friday_hours IS NOT NULL AND (p_friday_hours NOT BETWEEN 0 AND 24)) OR
220 (p_saturday_hours IS NOT NULL AND (p_saturday_hours NOT BETWEEN 0 AND 24)) OR
221 (p_sunday_hours IS NOT NULL AND (p_sunday_hours NOT BETWEEN 0 AND 24)) ) THEN
222 PA_UTILS.Add_Message ('PA', 'PA_SCH_HOURS_OUT_OF_RANGE');
223 RAISE FND_API.G_EXC_ERROR;
224 END IF;
225
226 -- Cross-row validation when p_last_row_flag = 'Y'
227 IF p_change_start_date_tbl IS NOT NULL THEN
228 IF p_last_row_flag = 'Y' and p_change_start_date_tbl.COUNT > 1 THEN
229 FOR i IN p_change_start_date_tbl.FIRST .. p_change_start_date_tbl.LAST LOOP
230 FOR j IN i+1 .. p_change_start_date_tbl.LAST LOOP
231 IF ((p_change_start_date_tbl(j) >= p_change_start_date_tbl(i) AND p_change_start_date_tbl(j) <= p_change_end_date_tbl(i))
232 OR (p_change_end_date_tbl(j) >= p_change_start_date_tbl(i) AND p_change_end_date_tbl(j) <= p_change_end_date_tbl(i))
233 OR (p_change_start_date_tbl(j) <= p_change_start_date_tbl(i) AND p_change_end_date_tbl(j) >= p_change_end_date_tbl(i))
234 OR (p_change_start_date_tbl(j) >= p_change_start_date_tbl(i) AND p_change_end_date_tbl(j) <= p_change_end_date_tbl(i)))
235 THEN
236 PA_UTILS.Add_Message ('PA', 'PA_SCH_OVERLAP_WORK_PATTERN');
237 RAISE FND_API.G_EXC_ERROR;
238 END IF;
239 END LOOP;
240 END LOOP;
241 END IF;
242 END IF;
243
244 END IF; -- p_exception_type_code = 'CHANGE_WORK_PATTERN'
245
246 ----------------------------------------------------------------------------------------------
247 --
248 -- 'Continue and Submit' button? => return
249 --
250 ----------------------------------------------------------------------------------------------
251
252 -- If p_validate_only = 'T', it won't do anything other than on line validation i.e. validate_date_range.
253 -- From the Update Schedule page,
254 -- p_validate_only = 'T' : 'Continue and Submit' button
255 -- p_validate_only = 'F' : 'Apply' button
256 IF (p_validate_only = FND_API.G_TRUE) THEN
257 RETURN;
258 END IF;
259
260 ----------------------------------------------------------------------------------------------
261 --
262 -- Mass Schedule Update
263 --
264 ----------------------------------------------------------------------------------------------
265 -- If this is for Mass schedule update, call Mass Transaction Workflow.
266 IF (p_mass_update_flag = FND_API.G_TRUE AND p_last_row_flag = 'Y') THEN
267 --start the mass WF
268 PA_MASS_ASGMT_TRX.Start_Mass_Asgmt_Trx_Wf(
269 p_mode => PA_MASS_ASGMT_TRX.G_MASS_UPDATE_SCHEDULE
270 ,p_action => PA_MASS_ASGMT_TRX.G_SAVE
271 ,p_project_id => p_project_id
272 ,p_exception_type_code => p_exception_type_code
273 ,p_assignment_id_tbl => p_assignment_id_array
274 ,p_change_start_date => p_change_start_date
275 ,p_change_end_date => p_change_end_date
276 ,p_change_rqmt_status_code => p_requirement_status_code
277 ,p_change_asgmt_status_code => p_assignment_status_code
278 ,p_change_start_date_tbl => p_change_start_date_tbl
279 ,p_change_end_date_tbl => p_change_end_date_tbl
280 ,p_monday_hours_tbl => p_monday_hours_tbl
281 ,p_tuesday_hours_tbl => p_tuesday_hours_tbl
282 ,p_wednesday_hours_tbl => p_wednesday_hours_tbl
283 ,p_thursday_hours_tbl => p_thursday_hours_tbl
284 ,p_friday_hours_tbl => p_friday_hours_tbl
285 ,p_saturday_hours_tbl => p_saturday_hours_tbl
286 ,p_sunday_hours_tbl => p_sunday_hours_tbl
287 ,p_non_working_day_flag => p_non_working_day_flag
288 ,p_change_hours_type_code => p_change_hours_type_code
289 ,p_hrs_per_day => p_hrs_per_day
290 ,p_calendar_percent => p_calendar_percent
291 ,p_change_calendar_type_code => p_change_calendar_type_code
292 ,p_change_calendar_name => p_change_calendar_name
293 ,p_change_calendar_id => p_change_calendar_id
294 ,p_duration_shift_type_code => p_duration_shift_type_code
295 ,p_duration_shift_unit_code => p_duration_shift_unit_code
296 ,p_num_of_shift => p_number_of_shift
297 ,x_return_status => l_x_return_status );
298
299
300 ----------------------------------------------------------------------------------------------
301 --
302 -- Single Schedule Update
303 --
304 ----------------------------------------------------------------------------------------------
305 -- If this is for Single schedule update, call an appropriate procedure depends on
306 -- p_exception_type_code.
307 ELSIF (p_mass_update_flag = FND_API.G_FALSE) THEN
308 -- call execute_update_schedule procedure for single schedule update.
309
310 pa_schedule_utils.debug('pa.plsql.pa_schedule_pub', 'Calling single_update_schedule', 6);
311 single_update_schedule (
312 p_project_id => p_project_id
313 ,p_exception_type_code => p_exception_type_code
314 ,p_record_version_number => p_record_version_number
315 ,p_assignment_id => p_assignment_id
316 ,p_change_start_date => p_change_start_date
317 ,p_change_end_date => p_change_end_date
318 ,p_assignment_status_code => p_assignment_status_code
319 ,p_monday_hours => p_monday_hours
320 ,p_tuesday_hours => p_tuesday_hours
321 ,p_wednesday_hours => p_wednesday_hours
322 ,p_thursday_hours => p_thursday_hours
323 ,p_friday_hours => p_friday_hours
324 ,p_saturday_hours => p_saturday_hours
325 ,p_sunday_hours => p_sunday_hours
326 ,p_non_working_day_flag => p_non_working_day_flag
327 ,p_change_hours_type_code => p_change_hours_type_code
328 ,p_hrs_per_day => p_hrs_per_day
329 ,p_calendar_percent => p_calendar_percent
330 ,p_change_calendar_type_code => p_change_calendar_type_code
331 --,p_change_calendar_name => p_change_calendar_name
332 ,p_change_calendar_id => l_change_calendar_id
333 ,p_duration_shift_type_code => p_duration_shift_type_code
334 ,p_duration_shift_unit_code => p_duration_shift_unit_code
335 ,p_number_of_shift => p_number_of_shift
336 ,p_last_row_flag => p_last_row_flag
337 ,p_init_msg_list => FND_API.G_TRUE
338 ,p_commit => FND_API.G_FALSE
339 ,x_return_status => l_x_return_status
340 ,x_msg_count => l_msg_count
341 ,x_msg_data => l_msg_data);
342 END IF;
343
344 -- If the called API fails, raise an exception.
345 IF (l_x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
346 RAISE FND_API.G_EXC_ERROR;
347 END IF;
348
349 ELSE -- Bug 6411422
350
351 --l_profile_begin_date := to_date(fnd_profile.value('PA_UTL_START_DATE'), 'DD/MM/YYYY'); /* commenting for For Bug 7304151 */
352 l_profile_begin_date := to_date(fnd_profile.value('PA_UTL_START_DATE'), 'DD/MM/YYYY', 'NLS_DATE_LANGUAGE=AMERICAN'); /*Adding For Bug 7304151*/
353 PA_UTILS.Add_Message( p_app_short_name => 'PA'
354 ,p_msg_name => 'PA_INVALID_ASSIGN_START_DATE'
355 ,p_token1 => 'PROFILE_DATE'
356 ,p_value1 => l_profile_begin_date );
357 RAISE FND_API.G_EXC_ERROR;
358
359 END IF; -- Bug 6411422
360
361 ----------------------------------------------------------------------------------------------
362 --
363 -- Exception Handling
364 --
365 ----------------------------------------------------------------------------------------------
366 EXCEPTION
367
368 WHEN FND_API.G_EXC_ERROR THEN
369 x_return_status := FND_API.G_RET_STS_ERROR;
370 x_msg_count := FND_MSG_PUB.Count_Msg;
371
372 IF x_msg_count = 1 THEN
373 pa_interface_utils_pub.get_messages
374 (p_encoded => FND_API.G_TRUE,
375 p_msg_index => 1,
376 p_data => x_msg_data,
377 p_msg_index_out => l_msg_index_out );
378 END IF;
379
380 WHEN OTHERS THEN
381 IF p_commit = FND_API.G_TRUE THEN
382 ROLLBACK TO SCH_PUB_UPDATE_SCH;
383 END IF;
384
385 -- 4537865 : RESET x_msg_data also.
386 x_msg_data := SUBSTRB(SQLERRM,1,240);
387
388 -- Set the excetption Message and the stack
389 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_SCHEDULE_PUB'
390 ,p_procedure_name => 'execute_update_schedule'
391 ,p_error_text => x_msg_data ); -- 4537865
392
393 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
394 x_msg_count := 1;
395
396 RAISE; -- This is optional depending on the needs
397
398 END update_schedule;
399
400
401
402
403 -- procedure : single_update_schedule
404 -- Purpose : This procedure will change the schedule records of a single assignment.
405 --
406 -- Input parameters
407 -- Parameters Type Required Description
408 -- --------------------------- ------ -------- --------------------------------------------------------
409 -- P_Project_Id NUMBER YES project id of the associated calendar
410 -- P_Calendar_Id NUMBER NO Id for that calendar which is associated to this assignment
411
412 PROCEDURE single_update_schedule
413 ( p_project_id IN NUMBER
414 ,p_exception_type_code IN VARCHAR2
415 ,p_record_version_number IN NUMBER := NULL
416 ,p_assignment_id IN NUMBER := NULL
417 ,p_change_start_date IN DATE := NULL
418 ,p_change_end_date IN DATE := NULL
419 ,p_assignment_status_code IN VARCHAR2 := NULL
420 ,p_monday_hours IN NUMBER := NULL
421 ,p_tuesday_hours IN NUMBER := NULL
422 ,p_wednesday_hours IN NUMBER := NULL
423 ,p_thursday_hours IN NUMBER := NULL
424 ,p_friday_hours IN NUMBER := NULL
425 ,p_saturday_hours IN NUMBER := NULL
426 ,p_sunday_hours IN NUMBER := NULL
427 ,p_non_working_day_flag IN VARCHAR2 := 'N'
428 ,p_change_hours_type_code IN VARCHAR2 := NULL
429 ,p_hrs_per_day IN NUMBER := NULL
430 ,p_calendar_percent IN NUMBER := NULL
431 ,p_change_calendar_type_code IN VARCHAR2 := NULL
432 --,p_change_calendar_name IN VARCHAR2 := NULL
433 ,p_change_calendar_id IN NUMBER := NULL
434 ,p_duration_shift_type_code IN VARCHAR2 := NULL
435 ,p_duration_shift_unit_code IN VARCHAR2 := NULL
436 ,p_number_of_shift IN NUMBER := NULL
437 ,p_last_row_flag IN VARCHAR2 := 'Y'
438 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
439 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
440 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
441 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
442 ,x_msg_data OUT NOCOPY VARCHAR2 ) --File.Sql.39 bug 4440895
443 IS
444
445 l_err_msg_code fnd_new_messages.message_name%TYPE;
446 l_updated_calendar_id NUMBER;
447 l_msg_data VARCHAR2(2000);
448 l_msg_count NUMBER;
449 l_msg_index_out NUMBER;
450 l_assignment_type VARCHAR2(30);
451 l_assignment_status_code VARCHAR2(30);
452 l_calendar_id NUMBER;
453 l_cur_asgn_start_date DATE;
454 l_cur_asgn_end_date DATE;
455
456 -- To get information for the given assignment
457 CURSOR get_asgmt_info_csr IS
458 SELECT assignment_type,
459 status_code,
460 calendar_id,
461 start_date,
462 end_date
463 FROM pa_project_assignments
464 WHERE assignment_id = p_assignment_id;
465
466 BEGIN
467 --Clear the global PL/SQL message table
468 IF (p_init_msg_list = FND_API.G_TRUE) THEN
469 FND_MSG_PUB.initialize;
470 END IF;
471
472 -- Initialize the return status to success
473 x_return_status := FND_API.G_RET_STS_SUCCESS ;
474
475 -- Initialize the Error Stack
476 PA_DEBUG.init_err_stack('PA_SCHEDULE_PUB.single_update_schedule');
477
478 -- Issue API savepoint if the transaction is to be committed
479 IF p_commit = FND_API.G_TRUE THEN
480 SAVEPOINT SCH_PUB_SINGLE_UPDATE_SCH;
481 END IF;
482
483 -- Get assignment information to pass to change_XXX apis.
484 OPEN get_asgmt_info_csr;
485 FETCH get_asgmt_info_csr
486 INTO l_assignment_type, l_assignment_status_code,
487 l_calendar_id,
488 l_cur_asgn_start_date, l_cur_asgn_end_date;
489 CLOSE get_asgmt_info_csr;
490
491 -- Call an appropriate procedure depends on p_exception_type_code.
492 IF (p_exception_type_code = 'CHANGE_DURATION' OR p_exception_type_code = 'SHIFT_DURATION') THEN
493 change_duration (
494 p_record_version_number => p_record_version_number
495 ,p_project_id => p_project_id
496 ,p_exception_type_code => p_exception_type_code
497 ,p_calendar_id => l_calendar_id
498 ,p_assignment_id => p_assignment_id
499 ,p_assignment_type => l_assignment_type
500 ,p_start_date => p_change_start_date
501 ,p_end_date => p_change_end_date
502 ,p_assignment_status_code => p_assignment_status_code
503 ,p_asgn_start_date => l_cur_asgn_start_date
504 ,p_asgn_end_date => l_cur_asgn_end_date
505 ,p_duration_shift_type_code => p_duration_shift_type_code
506 ,p_duration_shift_unit_code => p_duration_shift_unit_code
507 ,p_number_of_shift => p_number_of_shift
508 ,x_return_status => l_x_return_status
509 ,x_msg_count => l_msg_count
510 ,x_msg_data => l_msg_data);
511
512 ELSIF (p_exception_type_code = 'CHANGE_STATUS') THEN
513 change_status (
514 p_record_version_number => p_record_version_number
515 ,p_project_id => p_project_id
516 ,p_calendar_id => l_calendar_id
517 ,p_assignment_id => p_assignment_id
518 ,p_assignment_type => l_assignment_type
519 ,p_status_type => NULL
520 ,p_start_date => p_change_start_date
521 ,p_end_date => p_change_end_date
522 ,p_assignment_status_code => p_assignment_status_code
523 ,p_asgn_start_date => l_cur_asgn_start_date
524 ,p_asgn_end_date => l_cur_asgn_end_date
525 ,x_return_status => l_x_return_status
526 ,x_msg_count => l_msg_count
527 ,x_msg_data => l_msg_data);
528
529 ELSIF (p_exception_type_code = 'CHANGE_WORK_PATTERN') THEN
530 pa_schedule_utils.debug('pa.plsql.pa_schedule_pub', 'Calling change_work_pattern', 6);
531 change_work_pattern (
532 p_record_version_number => p_record_version_number
533 ,p_project_id => p_project_id
534 ,p_calendar_id => l_calendar_id
535 ,p_assignment_id => p_assignment_id
536 ,p_assignment_type => l_assignment_type
537 ,p_start_date => p_change_start_date
538 ,p_end_date => p_change_end_date
539 ,p_monday_hours => p_monday_hours
540 ,p_tuesday_hours => p_tuesday_hours
541 ,p_wednesday_hours => p_wednesday_hours
542 ,p_thursday_hours => p_thursday_hours
543 ,p_friday_hours => p_friday_hours
544 ,p_saturday_hours => p_saturday_hours
545 ,p_sunday_hours => p_sunday_hours
546 ,p_asgn_start_date => l_cur_asgn_start_date
547 ,p_asgn_end_date => l_cur_asgn_end_date
548 ,p_last_row_flag => p_last_row_flag
549 ,x_return_status => l_x_return_status
550 ,x_msg_count => l_msg_count
551 ,x_msg_data => l_msg_data);
552
553 ELSIF (p_exception_type_code = 'CHANGE_HOURS') THEN
554 change_hours (
555 p_record_version_number => p_record_version_number
556 ,p_project_id => p_project_id
557 ,p_calendar_id => l_calendar_id
558 ,p_assignment_id => p_assignment_id
559 ,p_assignment_type => l_assignment_type
560 ,p_start_date => p_change_start_date
561 ,p_end_date => p_change_end_date
562 ,p_assignment_status_code => l_assignment_status_code
563 ,p_change_hours_type_code => p_change_hours_type_code
564 ,p_hrs_per_day => p_hrs_per_day
565 ,p_non_working_day_flag => p_non_working_day_flag
566 ,p_calendar_percent => p_calendar_percent
567 ,p_change_calendar_type_code => p_change_calendar_type_code
568 -- ,p_change_calendar_name => p_change_calendar_name
569 ,p_change_calendar_id => p_change_calendar_id
570 ,p_asgn_start_date => l_cur_asgn_start_date
571 ,p_asgn_end_date => l_cur_asgn_end_date
572 ,x_return_status => l_x_return_status
573 ,x_msg_count => l_msg_count
574 ,x_msg_data => l_msg_data);
575
576 END IF;
577
578 -- If the called API fails, raise an exception.
579 IF l_x_return_status = FND_API.G_RET_STS_ERROR THEN
580 RAISE FND_API.G_EXC_ERROR;
581 ELSIF l_x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
582 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
583 END IF;
584
585 ----------------------------------------------------------------------------------------------
586 --
587 -- Exception Handling
588 --
589 ----------------------------------------------------------------------------------------------
590 EXCEPTION
591
592 WHEN FND_API.G_EXC_ERROR THEN
593 x_return_status := FND_API.G_RET_STS_ERROR;
594 x_msg_count := FND_MSG_PUB.Count_Msg;
595
596 IF x_msg_count = 1 THEN
597 pa_interface_utils_pub.get_messages
598 (p_encoded => FND_API.G_TRUE,
599 p_msg_index => 1,
600 p_data => x_msg_data,
601 p_msg_index_out => l_msg_index_out );
602 END IF;
603
604 WHEN OTHERS THEN
605 IF p_commit = FND_API.G_TRUE THEN
606 ROLLBACK TO SCH_PUB_SINGLE_UPDATE_SCH;
607 END IF;
608
609 -- 4537865 : RESET x_msg_data also
610 x_msg_data := SUBSTRB(SQLERRM,1,240);
611
612 -- Set the excetption Message and the stack
613 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_SCHEDULE_PUB'
614 ,p_procedure_name => 'single_update_schedule',
615 p_error_text => x_msg_data ); -- 4537865
616
617 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
618 x_msg_count := 1;
619
620 RAISE; -- This is optional depending on the needs
621
622 END single_update_schedule;
623
624
625
626 -- procedure : mass_update_schedule
627 -- Purpose : This procedure will change the schedule records of the assignments passed in.
628 -- Currently, this procedure will only be called by the Mass Transaction Workflow API.
629 --
630 -- Input parameters
631 -- Parameters Type Required Description
632 -- --------------------------- ------ -------- --------------------------------------------------------
633 -- P_Project_Id NUMBER YES project id of the associated calendar
634 PROCEDURE mass_update_schedule
635 ( p_project_id IN NUMBER
636 ,p_exception_type_code IN VARCHAR2
637 ,p_assignment_id_array IN SYSTEM.PA_NUM_TBL_TYPE
638 ,p_change_start_date IN DATE := NULL
639 ,p_change_end_date IN DATE := NULL
640 ,p_change_rqmt_status_code IN VARCHAR2 := NULL
641 ,p_change_asgmt_status_code IN VARCHAR2 := NULL
642 ,p_change_start_date_tbl IN SYSTEM.PA_DATE_TBL_TYPE := NULL
643 ,p_change_end_date_tbl IN SYSTEM.PA_DATE_TBL_TYPE := NULL
644 ,p_monday_hours_tbl IN SYSTEM.PA_NUM_TBL_TYPE := NULL
645 ,p_tuesday_hours_tbl IN SYSTEM.PA_NUM_TBL_TYPE := NULL
646 ,p_wednesday_hours_tbl IN SYSTEM.PA_NUM_TBL_TYPE := NULL
647 ,p_thursday_hours_tbl IN SYSTEM.PA_NUM_TBL_TYPE := NULL
648 ,p_friday_hours_tbl IN SYSTEM.PA_NUM_TBL_TYPE := NULL
649 ,p_saturday_hours_tbl IN SYSTEM.PA_NUM_TBL_TYPE := NULL
650 ,p_sunday_hours_tbl IN SYSTEM.PA_NUM_TBL_TYPE := NULL
651 ,p_non_working_day_flag IN VARCHAR2 := 'N'
652 ,p_change_hours_type_code IN VARCHAR2 := NULL
653 ,p_hrs_per_day IN NUMBER := NULL
654 ,p_calendar_percent IN NUMBER := NULL
655 ,p_change_calendar_type_code IN VARCHAR2 := NULL
656 ,p_change_calendar_name IN VARCHAR2 := NULL
657 ,p_change_calendar_id IN NUMBER := NULL
658 ,p_duration_shift_type_code IN VARCHAR2 := NULL
659 ,p_duration_shift_unit_code IN VARCHAR2 := NULL
660 ,p_number_of_shift IN NUMBER := NULL
661 ,p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
662 ,p_validate_only IN VARCHAR2 := FND_API.G_TRUE
663 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
664 ,x_success_assignment_id_tbl OUT NOCOPY SYSTEM.PA_NUM_TBL_TYPE /* Added NOCOPY for bug#2674619 */
665 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
666 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
667 ,x_msg_data OUT NOCOPY VARCHAR2 ) --File.Sql.39 bug 4440895
668 IS
669
670 l_err_msg_code fnd_new_messages.message_name%TYPE;
671 l_updated_calendar_id NUMBER;
672 l_msg_data VARCHAR2(2000);
673 l_msg_count NUMBER;
674 l_assignment_id NUMBER;
675 l_assignment_type VARCHAR2(30);
676 l_assignment_status_code VARCHAR2(30);
677 l_asgmt_system_status_code VARCHAR2(30);
678 l_change_asgmt_status_code VARCHAR2(30);
679 l_resource_id NUMBER;
680 l_return_status VARCHAR2(50);
681 l_return_code VARCHAR2(30);
682 l_rownum NUMBER;
683 l_privilege VARCHAR2(30);
684 l_success_assignment_id_tbl system.pa_num_tbl_type := p_assignment_id_array;
685 l_last_row_flag VARCHAR2(1);
686
687 -- To get information for the gieven assignment
688 CURSOR get_asgmt_info_csr(l_assignment_id IN NUMBER) IS
689 SELECT assignment_type,
690 status_code,
691 resource_id
692 FROM pa_project_assignments
693 WHERE assignment_id = l_assignment_id;
694
695
696 BEGIN
697 -- Initialize the return status to success
698 x_return_status := FND_API.G_RET_STS_SUCCESS ;
699
700 --Clear the global PL/SQL message table
701 IF (p_init_msg_list = FND_API.G_TRUE) THEN
702 FND_MSG_PUB.initialize;
703 END IF;
704
705 -- Initialize the Error Stack
706 PA_DEBUG.init_err_stack('PA_SCHEDULE_PUB.mass_update_schedule');
707
708
709 ----------------------------------------------------------------------------------------------
710 --
711 -- Loop through Assignment_Id_Array
712 --
713 ----------------------------------------------------------------------------------------------
714 -- loop through assignmentId_array and process for each single update_schedule
715 FOR i IN p_assignment_id_array.FIRST..p_assignment_id_array.LAST LOOP
716
717 BEGIN
718 -- We need to commit for each schedule update rather than one time after
719 -- completing mass schedule update.
720 IF p_commit = FND_API.G_TRUE THEN
721 SAVEPOINT SCH_PUB_MASS_UPDATE_SCH;
722 END IF;
723
724 -- get the detail information of the assignment to prepare the parameters for change_XXX api
725 l_assignment_id := p_assignment_id_array(i);
726
727 OPEN get_asgmt_info_csr(l_assignment_id);
728 FETCH get_asgmt_info_csr
729 INTO l_assignment_type, l_assignment_status_code, l_resource_id;
730
731 l_rownum := get_asgmt_info_csr%ROWCOUNT;
732 CLOSE get_asgmt_info_csr;
733
734 -- If there is no record for the assignment_id
735 IF (l_rownum = 0) THEN
736 -- set l_success_assignment_id_tbl(i) to null, which will be passed as a out arameter
737 -- so that notification won't be sent for the assignment_id.
738 l_success_assignment_id_tbl(i) := null;
739
740 -- add appropriate error message to the error stack as raise error where the error will be
741 -- copied to the error table.
742 PA_UTILS.Add_Message ('PA', 'PA_NO_ASGMT');
743 RAISE FND_API.G_EXC_ERROR;
744
745 -- If there is only record for the assignment_id, call single_update_schedule after security check
746 -- if necessary.
747 ELSIF (l_rownum = 1) THEN
748
749 -- Actually l_change_asgmt_status_code is used for requirement_status_code for OPEN_ASSIGNMENT.
750 IF (l_assignment_type = 'OPEN_ASSIGNMENT') THEN
751 l_change_asgmt_status_code := p_change_rqmt_status_code;
752 ELSE
753 l_change_asgmt_status_code := p_change_asgmt_status_code;
754 END IF;
755
756 -- call single_update_schedule procedure for single schedule update.
757 -- Need to pass NULL for p_record_version_number so that it doesn't check record_version_number.
758 -- Because we don't need to care about it for mass schedule update.
759
760 -- For CHANGE_WORK_PATTERN
761 IF p_exception_type_code = 'CHANGE_WORK_PATTERN' THEN
762 IF p_change_start_date_tbl.COUNT > 0 THEN
763 FOR j IN p_change_start_date_tbl.FIRST .. p_change_start_date_tbl.LAST LOOP
764 IF j < p_change_start_date_tbl.LAST THEN
765 l_last_row_flag := 'N';
766 ELSE
767 l_last_row_flag := 'Y';
768 END IF;
769
770 single_update_schedule (
771 p_project_id => p_project_id
772 ,p_exception_type_code => p_exception_type_code
773 ,p_record_version_number => NULL
774 ,p_assignment_id => p_assignment_id_array(i)
775 ,p_change_start_date => p_change_start_date_tbl(j)
776 ,p_change_end_date => p_change_end_date_tbl(j)
777 ,p_assignment_status_code => l_change_asgmt_status_code
778 ,p_monday_hours => p_monday_hours_tbl(j)
779 ,p_tuesday_hours => p_tuesday_hours_tbl(j)
780 ,p_wednesday_hours => p_wednesday_hours_tbl(j)
781 ,p_thursday_hours => p_thursday_hours_tbl(j)
782 ,p_friday_hours => p_friday_hours_tbl(j)
783 ,p_saturday_hours => p_saturday_hours_tbl(j)
784 ,p_sunday_hours => p_sunday_hours_tbl(j)
785 ,p_non_working_day_flag => p_non_working_day_flag
786 ,p_change_hours_type_code => p_change_hours_type_code
787 ,p_hrs_per_day => p_hrs_per_day
788 ,p_calendar_percent => p_calendar_percent
789 ,p_change_calendar_type_code => p_change_calendar_type_code
790 --,p_change_calendar_name => p_change_calendar_name
791 ,p_change_calendar_id => p_change_calendar_id
792 ,p_duration_shift_type_code => p_duration_shift_type_code
793 ,p_duration_shift_unit_code => p_duration_shift_unit_code
794 ,p_number_of_shift => p_number_of_shift
795 ,p_last_row_flag => l_last_row_flag
796 ,p_init_msg_list => FND_API.G_TRUE
797 ,p_commit => FND_API.G_FALSE
798 ,x_return_status => l_x_return_status
799 ,x_msg_count => l_msg_count
800 ,x_msg_data => l_msg_data);
801
802 END LOOP;
803 END IF;
804 -- For all other schedule changes
805 ELSE
806 l_last_row_flag := 'Y';
807
808 single_update_schedule (
809 p_project_id => p_project_id
810 ,p_exception_type_code => p_exception_type_code
811 ,p_record_version_number => NULL
812 ,p_assignment_id => p_assignment_id_array(i)
813 ,p_change_start_date => p_change_start_date
814 ,p_change_end_date => p_change_end_date
815 ,p_assignment_status_code => l_change_asgmt_status_code
816 ,p_monday_hours => NULL
817 ,p_tuesday_hours => NULL
818 ,p_wednesday_hours => NULL
819 ,p_thursday_hours => NULL
820 ,p_friday_hours => NULL
821 ,p_saturday_hours => NULL
822 ,p_sunday_hours => NULL
823 ,p_non_working_day_flag => p_non_working_day_flag
824 ,p_change_hours_type_code => p_change_hours_type_code
825 ,p_hrs_per_day => p_hrs_per_day
826 ,p_calendar_percent => p_calendar_percent
827 ,p_change_calendar_type_code => p_change_calendar_type_code
828 --,p_change_calendar_name => p_change_calendar_name
829 ,p_change_calendar_id => p_change_calendar_id
830 ,p_duration_shift_type_code => p_duration_shift_type_code
831 ,p_duration_shift_unit_code => p_duration_shift_unit_code
832 ,p_number_of_shift => p_number_of_shift
833 ,p_last_row_flag => l_last_row_flag
834 ,p_init_msg_list => FND_API.G_TRUE
835 ,p_commit => FND_API.G_FALSE
836 ,x_return_status => l_x_return_status
837 ,x_msg_count => l_msg_count
838 ,x_msg_data => l_msg_data);
839 END IF; -- end if for calling single_update_schedule
840
841 -- If the called API succeeded, put the assingnment_id to the out parameter 'x_assignment_id_array'
842 -- and commit.
843 IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
844 l_success_assignment_id_tbl(i) := p_assignment_id_array(i);
845
846 IF (p_commit = FND_API.G_TRUE) THEN
847 COMMIT;
848 END IF;
849
850 -- If the called API doesn't succeeded, set null to tthe out parameter 'x_assignment_id_array'
851 -- instead of the failed assignment_id so that workflow won't be started for the failed assignments.
852 -- And need to rollback.
853 ELSE
854 l_success_assignment_id_tbl(i) := null;
855
856 IF (p_commit = FND_API.G_TRUE) THEN
857 ROLLBACK TO SCH_PUB_MASS_UPDATE_SCH;
858 RAISE FND_API.G_EXC_ERROR;
859 END IF;
860 END IF;
861
862 END IF; -- if l_rownum = 1
863
864 EXCEPTION
865 -- need to catch error for system_status_code being not found
866 WHEN NO_DATA_FOUND THEN
867 PA_UTILS.Add_Message ('PA', 'PA_STATUS_CODE_NOT_FOUND');
868
869 -- save the error message to the proper table so that we can retrive them later
870 -- from workflow notification page.
871 PA_MESSAGE_UTILS.save_messages
872 (p_user_id => PA_MASS_ASGMT_TRX.G_SUBMITTER_USER_ID,
873 p_source_type1 => PA_MASS_ASGMT_TRX.G_SOURCE_TYPE1,
874 p_source_type2 => PA_MASS_ASGMT_TRX.G_MASS_UPDATE_SCHEDULE,
875 p_source_identifier1 => PA_MASS_ASGMT_TRX.G_WORKFLOW_ITEM_TYPE,
876 p_source_identifier2 => PA_MASS_ASGMT_TRX.G_WORKFLOW_ITEM_KEY,
877 p_context1 => p_project_id,
878 p_context2 => p_assignment_id_array(i),
879 p_context3 => NULL,
880 p_commit => FND_API.G_TRUE,
881 x_return_status => l_return_status);
882
883 WHEN FND_API.G_EXC_ERROR THEN
884 -- save the error message to the proper table so that we can retrive them later
885 -- from workflow notification page.
886 PA_MESSAGE_UTILS.save_messages
887 (p_user_id => PA_MASS_ASGMT_TRX.G_SUBMITTER_USER_ID,
888 p_source_type1 => PA_MASS_ASGMT_TRX.G_SOURCE_TYPE1,
889 p_source_type2 => PA_MASS_ASGMT_TRX.G_MASS_UPDATE_SCHEDULE,
890 p_source_identifier1 => PA_MASS_ASGMT_TRX.G_WORKFLOW_ITEM_TYPE,
891 p_source_identifier2 => PA_MASS_ASGMT_TRX.G_WORKFLOW_ITEM_KEY,
892 p_context1 => p_project_id,
893 p_context2 => p_assignment_id_array(i),
894 p_context3 => NULL,
895 p_commit => FND_API.G_TRUE,
896 x_return_status => l_return_status);
897 END;
898
899 END LOOP;
900
901 -- put the success_assignment_id_table to the out parameter to invoke workflow only for
902 -- those success ones.
903 X_success_assignment_id_tbl := l_success_assignment_id_tbl;
904
905 ----------------------------------------------------------------------------------------------
906 --
907 -- Exception Handling
908 --
909 ----------------------------------------------------------------------------------------------
910 EXCEPTION
911 WHEN OTHERS THEN
912
913 -- 4537865 : RESET x_msg_data also
914 x_msg_data := SUBSTRB(SQLERRM,1,240);
915
916 -- Set the excetption Message and the stack
917 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_SCHEDULE_PUB'
918 ,p_procedure_name => 'mass_update_schedule'
919 ,p_error_text => x_msg_data ); -- 4537865
920
921 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
922 x_msg_count := FND_MSG_PUB.Count_Msg;
923
924 RAISE; -- This is optional depending on the needs
925 END mass_update_schedule;
926
927
928
929 -- procedure : change_duration
930 -- Purpose : This procedure will change the duration of the given assignment
931 -- on the basis of start date and end date it will shift the assignment
932 -- , extend the assignment or contract the assignment
933 -- Input parameters
934 -- Parameters Type Required Description
935 -- P_Project_Id NUMBER YES project id of the associated calendar
936 -- p_record_version_number NUMBER YES
937 -- P_Calendar_Id NUMBER NO Id for that calendar which is associated to this assignment
938 -- P_Assignment_Id NUMBER YES Assignment id of the changed duration assignment
939 -- P_Assignment_Type VARCHAR2 YES It is type of the assignment e.g OPEN /STAFFED ASSIGNMENT
940 -- P_Start_Date DATE YES starting date for the changed duration
941 -- P_End_Date DATE YES ending date for the changed duration
942 -- P_Assignment_Status_Code VARCHAR2 YES Status of the assignment e.g OPEN/CONFIRM/PROVISIONAL
943 -- P_Asgn_Start_Date DATE YES Start date of the assignment for which you want to
944 -- change duration
945 -- P_Asgn_End_Date DATE YES End date of the assignment for which you want to
946 -- change duration
947
948 PROCEDURE change_duration
949 (
950 p_record_version_number IN Number ,
951 p_exception_type_code IN Varchar2 ,
952 p_project_id IN Number ,
953 p_calendar_id IN Number ,
954 p_assignment_id IN Number ,
955 p_assignment_type IN Varchar2 ,
956 p_start_date IN date := NULL,
957 p_end_date IN date := NULL,
958 p_assignment_status_code IN varchar2 := NULL,
959 p_asgn_start_date IN DATE := NULL,
960 p_asgn_end_date IN DATE := NULL,
961 p_duration_shift_type_code IN Varchar2 := NULL,
962 p_duration_shift_unit_code IN VARCHAR2 := NULL,
963 p_number_of_shift IN Varchar2 := NULL,
964 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
965 p_generate_timeline_flag IN VARCHAR2 := 'Y', --Unilog
966 x_return_status OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
967 x_msg_count OUT NOCOPY NUMBER , --File.Sql.39 bug 4440895
968 x_msg_data OUT NOCOPY VARCHAR2 ) --File.Sql.39 bug 4440895
969 IS
970 l_assignment_status_null EXCEPTION;
971 l_stale_asmt_data EXCEPTION;
972 l_status_type VARCHAR2(30);
973 l_error_message_code VARCHAR2(50);
974 l_record_version_number NUMBER;
975 l_person_id NUMBER;
976 l_start_date DATE;
977 l_end_date DATE;
978 l_msg_index_out NUMBER;
979 l_shifted_days NUMBER;
980 l_exception_id NUMBER;
981 l_return_status VARCHAR2(1);
982 l_assignment_status_name pa_project_statuses.project_status_name%TYPE;
983
984 -- For error message tokens
985 l_asgn_req_text VARCHAR2(30);
986 l_a_an_text VARCHAR2(30);
987 l_asgn_req_poss_text VARCHAR2(30);
988
989 l_data VARCHAR2(2000); -- 4537865
990 l_new_resource_id NUMBER; -- 4537865
991 -- For retrieving resource_source_id
992 CURSOR get_resource_source_id IS
993 SELECT a.person_id, b.resource_id
994 FROM pa_resource_txn_attributes a, pa_project_assignments b
995 WHERE a.resource_id = b.resource_id
996 AND b.assignment_id = p_assignment_id;
997
998 l_resource_source_id NUMBER;
999 l_resource_id NUMBER;
1000 l_resource_type_id NUMBER;
1001 l_resource_out_of_range EXCEPTION;
1002 l_resource_cc_error_u EXCEPTION;
1003 l_resource_cc_error_e EXCEPTION;
1004 l_cc_ok VARCHAR2(1);
1005 l_ei_asgn_out_of_range EXCEPTION;
1006
1007 -- For retrieving project_status_name
1008 -- 3054787: Select from tables directly to improve performance.
1009 CURSOR get_project_status_name IS
1010 SELECT pps.project_status_name
1011 FROM pa_projects_all ppa, pa_project_statuses pps
1012 WHERE ppa.project_id = p_project_id
1013 AND ppa.project_status_code = pps.project_status_code;
1014
1015 l_project_status_name pa_project_statuses.project_status_name%TYPE;
1016
1017 BEGIN
1018 l_x_return_status := FND_API.G_RET_STS_SUCCESS;
1019
1020 --Clear the global PL/SQL message table
1021 IF (p_init_msg_list = FND_API.G_TRUE) THEN
1022 FND_MSG_PUB.initialize;
1023 END IF;
1024
1025 IF ( p_assignment_type = 'OPEN_ASSIGNMENT' ) THEN
1026 l_asgn_req_text := FND_MESSAGE.GET_STRING('PA','PA_REQUIREMENT_TEXT');
1027 l_a_an_text := FND_MESSAGE.GET_STRING('PA','PA_A_TEXT');
1028 l_asgn_req_poss_text := FND_MESSAGE.GET_STRING('PA','PA_REQUIREMENT_POSS_TEXT');
1029 ELSE
1030 l_asgn_req_text := FND_MESSAGE.GET_STRING('PA','PA_ASSIGNMENT_TEXT');
1031 l_a_an_text := FND_MESSAGE.GET_STRING('PA','PA_AN_TEXT');
1032 l_asgn_req_poss_text := FND_MESSAGE.GET_STRING('PA','PA_ASSIGNMENT_POSS_TEXT');
1033 END IF;
1034
1035
1036 ----------------------------------------------------------------------------------------------
1037 --
1038 -- Logic for Duration
1039 --
1040 ----------------------------------------------------------------------------------------------
1041 IF (p_exception_type_code = 'CHANGE_DURATION') THEN
1042
1043 -- The dates are valid now checking the passing date should valid for the asignment dates
1044 IF (( p_start_date IS NOT NULL ) AND (p_end_date IS NULL ) AND (p_start_date > p_asgn_end_date )) THEN
1045 RAISE l_out_of_range_from_date;
1046 ELSIF (( p_start_date IS NULL ) AND (p_end_date IS NOT NULL ) AND ( p_end_date < p_asgn_start_date )) THEN
1047 RAISE l_out_of_range_to_date;
1048 END IF;
1049
1050 -- for change duration page, set the input value 'p_start_date' and 'p_end_date' to local
1051 -- variables which are used for checking validation resource for the updated date.
1052 l_start_date := p_start_date;
1053 l_end_date := p_end_date;
1054
1055 END IF; -- IF (p_exception_type_code = 'CHANGE_DURATION')
1056
1057
1058 ----------------------------------------------------------------------------------------------
1059 --
1060 -- Logic For Duration Shift
1061 --
1062 ----------------------------------------------------------------------------------------------
1063 IF (p_exception_type_code = 'SHIFT_DURATION') THEN
1064
1065 -- If p_number_of_shift is not null, calculate the new start_date and end_date by adding
1066 -- or substracting p_number_of_shift from p_asgn_start_date and p_asgn_end_date
1067 IF (p_number_of_shift is NOT NULL) THEN
1068
1069 -- compute shifted_days
1070 IF (p_duration_shift_unit_code = 'DAYS') THEN
1071 l_shifted_days := p_number_of_shift;
1072 ELSIF (p_duration_shift_unit_code = 'WEEKS') THEN
1073 l_shifted_days := p_number_of_shift*7;
1074 END IF;
1075
1076 -- set start_Date, end_date according to shift_type_code and shifed_days
1077 IF (p_duration_shift_type_code = 'FORWARD') THEN
1078 IF (p_duration_shift_unit_code = 'MONTHS') THEN
1079 l_start_date := add_months(p_asgn_start_date, p_number_of_shift) ;
1080 l_end_date := add_months(p_asgn_end_date, p_number_of_shift) ;
1081 ELSE
1082 l_start_date := p_asgn_start_date + l_shifted_days;
1083 l_end_date := p_asgn_end_date + l_shifted_days;
1084 END IF;
1085 ELSIF (p_duration_shift_type_code = 'BACKWARD') THEN
1086 IF (p_duration_shift_unit_code = 'MONTHS') THEN
1087 l_start_date := add_months(p_asgn_start_date, p_number_of_shift * -1) ;
1088 l_end_date := add_months(p_asgn_end_date, p_number_of_shift * -1) ;
1089 ELSE
1090 l_start_date := p_asgn_start_date - l_shifted_days;
1091 l_end_date := p_asgn_end_date - l_shifted_days;
1092 END IF;
1093
1094 END IF;
1095
1096 END IF;
1097
1098 END IF; -- IF (p_exception_type_code = 'SHIFT_DURATION')
1099
1100 ----------------------------------------------------------------------------------------------
1101 --
1102 -- Common Logic for both Duration and Duration Shift
1103 --
1104 ----------------------------------------------------------------------------------------------
1105 -- If extending or contracting the duration the the assignment status should not be null for the new duration
1106 -- If extending the staffed assignment duration with a new status, the status should be allowed for the status
1107 -- of the project this assignment belongs to.
1108 IF( ((l_start_date IS NOT NULL) AND (l_start_date NOT BETWEEN p_asgn_start_date AND p_asgn_end_date)) OR
1109 ((l_end_date IS NOT NULL) AND (l_end_date NOT BETWEEN p_asgn_start_date AND p_asgn_end_date)) ) THEN
1110
1111 IF(p_assignment_status_code IS NULL) THEN
1112 RAISE l_assignment_status_null;
1113
1114 ELSIF (p_assignment_status_code IS NOT NULL) AND (p_assignment_type <> 'OPEN_ASSIGNMENT') THEN
1115
1116 l_return_status := PA_ASSIGNMENT_UTILS.is_asgmt_allow_stus_ctl_check(
1117 p_asgmt_status_code => p_assignment_status_code,
1118 p_project_id => p_project_id,
1119 p_add_message => 'N');
1120
1121 IF l_return_status <> 'Y' THEN
1122 OPEN get_project_status_name;
1123 FETCH get_project_status_name INTO l_project_status_name;
1124 CLOSE get_project_status_name;
1125
1126 SELECT project_status_name
1127 INTO l_assignment_status_name
1128 FROM pa_project_statuses
1129 WHERE project_status_code = p_assignment_status_code;
1130
1131 RAISE l_asgn_stus_not_for_proj_stus;
1132 END IF;
1133 END IF;
1134
1135 END IF; --IF(( (p_start_date IS NOT NULL ..
1136
1137 --
1138 -- Validate that resource is valid for new start date
1139 --
1140 PA_SCHEDULE_UTILS.log_message(1,'Validate that resource is valid for new start date?');
1141 IF p_assignment_type <> 'OPEN_ASSIGNMENT' THEN
1142 PA_SCHEDULE_UTILS.log_message(1,'Validating resource');
1143 -- Get resource source id for assignment
1144 OPEN get_resource_source_id;
1145 FETCH get_resource_source_id INTO l_resource_source_id, l_resource_id;
1146 CLOSE get_resource_source_id;
1147
1148 PA_RESOURCE_UTILS.Check_ResourceName_Or_Id (
1149 p_resource_id => l_resource_source_id
1150 ,p_resource_name => null
1151 ,p_check_id_flag => 'Y' --3235018 Changed from N to Y --'N' /* Bug#2822950-Modified null to 'N' */
1152 ,p_date => NVL(l_start_date, p_asgn_start_date) -- 3235018 : replaced p_start_date to l_start_date
1153 ,p_end_date => NVL(l_end_date, p_asgn_end_date) -- 3235018 : Added this
1154 -- 4537865 : ,x_resource_id => l_resource_source_id
1155 ,x_resource_id => l_new_resource_id -- 4537865 : For NOCOPY related Changes.
1156 ,x_resource_type_id => l_resource_type_id
1157 ,x_return_status => l_x_return_status
1158 ,x_error_message_code => l_error_message_code);
1159
1160 IF (l_x_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
1161 PA_SCHEDULE_UTILS.log_message(1,'Resource dates are not valid');
1162 Raise l_resource_out_of_range;
1163 ELSE -- IF if l_x_return_status is success : 4537865
1164 l_resource_source_id := l_new_resource_id ;
1165 END IF;
1166
1167 PA_SCHEDULE_UTILS.log_message(1,'Resource dates are valid');
1168
1169 -- Check if resource is assigned to a valid organization
1170 PA_RESOURCE_UTILS.check_cc_for_resource(p_resource_id => l_resource_id,
1171 p_project_id => p_project_id,
1172 p_start_date => NVL(l_start_date, p_asgn_start_date),
1173 p_end_date => NVL(l_end_date, p_asgn_end_date),
1174 x_cc_ok => l_cc_ok,
1175 x_return_status => l_x_return_status,
1176 x_error_message_code => l_error_message_code);
1177
1178 IF (l_x_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
1179 PA_SCHEDULE_UTILS.log_message(1,'Resource cc is not valid: u');
1180 Raise l_resource_cc_error_u;
1181 END IF;
1182
1183 IF l_cc_ok <> 'Y' THEN
1184 PA_SCHEDULE_UTILS.log_message(1,'Resource cc is not valid: e');
1185 Raise l_resource_cc_error_e;
1186 END IF;
1187
1188 PA_SCHEDULE_UTILS.log_message(1,'Resource cc is valid');
1189
1190 -- Make sure duration change does not cause existing actuals to
1191 -- be out of range.
1192 -- 2797890: Added parameter p_project_id, p_person_id.
1193 PA_TRANS_UTILS.check_txn_exists (
1194 p_assignment_id => p_assignment_id
1195 ,p_old_start_date => p_asgn_start_date
1196 ,p_old_end_date => p_asgn_end_date
1197 ,p_new_start_date => NVL(l_start_date, p_asgn_start_date)
1198 ,p_new_end_date => NVL(l_end_date, p_asgn_end_date)
1199 ,p_calling_mode => 'UPDATE'
1200 ,p_project_id => p_project_id
1201 ,p_person_id => l_resource_source_id
1202 ,x_error_message_code => l_error_message_code
1203 ,x_return_status => x_return_status);
1204 -- End of 2797890
1205 if (x_return_status <> FND_API.G_RET_STS_SUCCESS) then
1206 raise l_ei_asgn_out_of_range;
1207 end if;
1208 END IF;
1209
1210 --
1211 -- Insert row to PA_SCHEDULE_EXCEPTIONS
1212 --
1213 PA_SCHEDULE_UTILS.log_message(1,'Start of the change_duration API ..... ');
1214 PA_SCH_EXCEPT_PKG.Insert_Rows(
1215 p_calendar_id => p_calendar_id
1216 ,p_assignment_id => p_assignment_id
1217 ,p_project_id => p_project_id
1218 ,p_schedule_type_code => p_assignment_type
1219 ,p_assignment_status_code => p_assignment_status_code
1220 ,p_exception_type_code => p_exception_type_code
1221 ,p_start_date => l_start_date
1222 ,p_end_date => l_end_date
1223 ,p_duration_shift_type_code => p_duration_shift_type_code
1224 ,p_duration_shift_unit_code => p_duration_shift_unit_code
1225 ,p_number_of_shift => p_number_of_shift
1226 ,x_exception_id => l_exception_id
1227 ,x_return_status => l_x_return_status
1228 ,x_msg_count => x_msg_count
1229 ,x_msg_data => x_msg_data );
1230
1231 -- Calling the change assignment schedule procedure which will
1232 -- generate the schedule after applying the duration change
1233 PA_SCHEDULE_PUB.change_asgn_schedule(
1234 p_record_version_number => p_record_version_number,
1235 p_assignment_id => p_assignment_id,
1236 p_project_id => p_project_id,
1237 p_exception_id => l_exception_id,
1238 p_generate_timeline_flag => p_generate_timeline_flag,--Unilog
1239 x_return_status => l_x_return_status,
1240 x_msg_count => x_msg_count,
1241 x_msg_data => x_msg_data);
1242
1243
1244 -- Bug 2255963: Call reevaluate_adv_action_set AFTER the start date of
1245 -- requirement (but not asssignment) is updated and before changes are committed.
1246 IF l_x_return_status = FND_API.G_RET_STS_SUCCESS THEN
1247 IF (p_assignment_type = 'OPEN_ASSIGNMENT' AND l_start_date <> p_asgn_start_date) THEN
1248 PA_ADVERTISEMENTS_PUB.Reevaluate_Adv_Action_Set (
1249 p_object_id => p_assignment_id,
1250 p_object_type => 'OPEN_ASSIGNMENT',
1251 p_new_object_start_date => l_start_date,
1252 p_validate_only => FND_API.G_FALSE,
1253 p_init_msg_list => FND_API.G_FALSE,
1254 p_commit => FND_API.G_FALSE,
1255 x_return_status => l_x_return_status);
1256 END IF;
1257 END IF;
1258
1259 -- Bug 2441437: Update the no of active candidates after duration
1260 -- has been changed.
1261 IF l_x_return_status = FND_API.G_RET_STS_SUCCESS THEN
1262 PA_CANDIDATE_UTILS.Update_No_Of_Active_Candidates (
1263 p_assignment_id => p_assignment_id,
1264 x_return_status => l_x_return_status);
1265 END IF;
1266
1267 ----------------------------------------------------------------------
1268 --
1269 -- Exception Handling
1270 --
1271 ----------------------------------------------------------------------
1272 PA_SCHEDULE_UTILS.log_message(1,'End of the change_duration API ..... ');
1273 x_return_status := l_x_return_status;
1274
1275 x_msg_count := FND_MSG_PUB.Count_Msg;
1276 If x_msg_count = 1 THEN
1277 pa_interface_utils_pub.get_messages
1278 (p_encoded => FND_API.G_TRUE ,
1279 p_msg_index => 1,
1280 p_msg_count => x_msg_count ,
1281 p_msg_data => x_msg_data ,
1282 p_data => l_data, -- 4537865 : Replaced x_msg_data with l_data
1283 p_msg_index_out => l_msg_index_out );
1284 x_msg_data := l_data ; -- 4537865 : NOCOPY related change
1285 End If;
1286
1287 EXCEPTION
1288 WHEN l_ei_asgn_out_of_range THEN
1289 PA_UTILS.Add_Message('PA', l_error_message_code);
1290 x_return_status := FND_API.G_RET_STS_ERROR;
1291 x_msg_data := l_error_message_code;
1292 x_msg_count := FND_MSG_PUB.Count_Msg;
1293 If x_msg_count = 1 THEN
1294 pa_interface_utils_pub.get_messages
1295 (p_encoded => FND_API.G_TRUE,
1296 p_msg_index => 1,
1297 p_msg_count => x_msg_count,
1298 p_msg_data => x_msg_data,
1299 p_data => l_data, -- 4537865 : Replaced x_msg_data with l_data
1300 p_msg_index_out => l_msg_index_out );
1301 x_msg_data := l_data ; -- 4537865 : NOCOPY related change
1302 End If;
1303 WHEN l_resource_cc_error_u THEN
1304 PA_UTILS.Add_Message('PA', l_error_message_code);
1305 x_return_status := FND_API.G_RET_STS_ERROR;
1306 x_msg_data := l_error_message_code;
1307 x_msg_count := FND_MSG_PUB.Count_Msg;
1308 If x_msg_count = 1 THEN
1309 pa_interface_utils_pub.get_messages
1310 (p_encoded => FND_API.G_TRUE,
1311 p_msg_index => 1,
1312 p_msg_count => x_msg_count,
1313 p_msg_data => x_msg_data,
1314 p_data => l_data, -- 4537865 : Replaced x_msg_data with l_data
1315 p_msg_index_out => l_msg_index_out );
1316 x_msg_data := l_data ; -- 4537865 : NOCOPY related change
1317 End If;
1318 WHEN l_resource_cc_error_e THEN
1319 PA_UTILS.Add_Message('PA', 'CROSS_CHARGE_VALIDATION_FAILED');
1320 x_return_status := FND_API.G_RET_STS_ERROR;
1321 x_msg_data := 'CROSS_CHARGE_VALIDATION_FAILED';
1322 x_msg_count := FND_MSG_PUB.Count_Msg;
1323 If x_msg_count = 1 THEN
1324 pa_interface_utils_pub.get_messages
1325 (p_encoded => FND_API.G_TRUE,
1326 p_msg_index => 1,
1327 p_msg_count => x_msg_count,
1328 p_msg_data => x_msg_data,
1329 p_data => l_data, -- 4537865 : Replaced x_msg_data with l_data
1330 p_msg_index_out => l_msg_index_out );
1331 x_msg_data := l_data ; -- 4537865 : NOCOPY related change
1332 End If;
1333 WHEN l_resource_out_of_range THEN
1334 PA_UTILS.Add_Message( 'PA', 'PA_RESOURCE_OUT_OF_RANGE');
1335 x_return_status := FND_API.G_RET_STS_ERROR;
1336 x_msg_data := 'PA_RESOURCE_OUT_OF_RANGE';
1337 x_msg_count := FND_MSG_PUB.Count_Msg;
1338 If x_msg_count = 1 THEN
1339 pa_interface_utils_pub.get_messages
1340 (p_encoded => FND_API.G_TRUE,
1341 p_msg_index => 1,
1342 p_msg_count => x_msg_count,
1343 p_msg_data => x_msg_data,
1344 p_data => l_data, -- 4537865 : Replaced x_msg_data with l_data
1345 p_msg_index_out => l_msg_index_out );
1346 x_msg_data := l_data ; -- 4537865 : NOCOPY related change
1347 End If;
1348 WHEN l_stale_asmt_data THEN
1349 PA_UTILS.add_message('PA','PA_XC_RECORD_CHANGED');
1350 x_return_status := FND_API.G_RET_STS_ERROR;
1351 x_msg_data := 'PA_XC_RECORD_CHANGED';
1352 x_msg_count := FND_MSG_PUB.Count_Msg;
1353 If x_msg_count = 1 THEN
1354 pa_interface_utils_pub.get_messages
1355 (p_encoded => FND_API.G_TRUE,
1356 p_msg_index => 1,
1357 p_msg_count => x_msg_count,
1358 p_msg_data => x_msg_data,
1359 p_data => l_data, -- 4537865 : Replaced x_msg_data with l_data
1360 p_msg_index_out => l_msg_index_out );
1361 x_msg_data := l_data ; -- 4537865 : NOCOPY related change
1362 End If;
1363 WHEN l_assignment_status_null THEN
1364 PA_UTILS.add_message('PA','PA_SCH_ASGN_STATUS_NULL');
1365 x_return_status := FND_API.G_RET_STS_ERROR;
1366 x_msg_data := 'PA_SCH_ASGN_STATUS_NULL';
1367 x_msg_count := FND_MSG_PUB.Count_Msg;
1368 If x_msg_count = 1 THEN
1369 pa_interface_utils_pub.get_messages
1370 (p_encoded => FND_API.G_TRUE,
1371 p_msg_index => 1,
1372 p_msg_count => x_msg_count,
1373 p_msg_data => x_msg_data,
1374 p_data => l_data, -- 4537865 : Replaced x_msg_data with l_data
1375 p_msg_index_out => l_msg_index_out );
1376 x_msg_data := l_data ; -- 4537865 : NOCOPY related change
1377 End If;
1378 WHEN l_out_of_range_from_date THEN
1379 PA_UTILS.add_message('PA','PA_SCH_INVALID_FROM_DATE',
1380 'ASMT_TYPE_POSS', l_asgn_req_poss_text,
1381 'ASMT_TYPE', l_asgn_req_text);
1382 x_msg_data := 'PA_SCH_INVALID_FROM_DATE';
1383 x_return_status := FND_API.G_RET_STS_ERROR;
1384 x_msg_count := FND_MSG_PUB.Count_Msg;
1385 If x_msg_count = 1 THEN
1386 pa_interface_utils_pub.get_messages
1387 (p_encoded => FND_API.G_TRUE,
1388 p_msg_index => 1,
1389 p_msg_count => x_msg_count,
1390 p_msg_data => x_msg_data,
1391 p_data => l_data, -- 4537865 : Replaced x_msg_data with l_data
1392 p_msg_index_out => l_msg_index_out );
1393 x_msg_data := l_data; -- 4537865 : NOCOPY related change
1394 End If;
1395 WHEN l_out_of_range_to_date THEN
1396 PA_UTILS.add_message('PA','PA_SCH_INVALID_TO_DATE',
1397 'ASMT_TYPE_POSS', l_asgn_req_poss_text,
1398 'ASMT_TYPE', l_asgn_req_text);
1399 x_return_status := FND_API.G_RET_STS_ERROR;
1400 x_msg_data := 'PA_SCH_INVALID_TO_DATE';
1401 x_msg_count := FND_MSG_PUB.Count_Msg;
1402 If x_msg_count = 1 THEN
1403 pa_interface_utils_pub.get_messages
1404 (p_encoded => FND_API.G_TRUE,
1405 p_msg_index => 1,
1406 p_msg_count => x_msg_count,
1407 p_msg_data => x_msg_data,
1408 p_data => l_data, -- 4537865 : Replaced x_msg_data with l_data
1409 p_msg_index_out => l_msg_index_out );
1410 x_msg_data := l_data; -- 4537865 : NOCOPY related change
1411 End If;
1412 WHEN l_asgn_stus_not_for_proj_stus THEN
1413 PA_UTILS.Add_Message( p_app_short_name => 'PA',
1414 p_msg_name => 'PA_ASGN_STUS_NOT_FOR_PROJ_STUS',
1415 p_token1 => 'PROJ_STATUS',
1416 p_value1 => l_project_status_name,
1417 p_token2 => 'ASGN_STATUS',
1418 p_value2 => l_assignment_status_name);
1419 x_return_status := FND_API.G_RET_STS_ERROR;
1420 x_msg_data := 'PA_ASGN_STUS_NOT_FOR_PROJ_STUS';
1421 x_msg_count := FND_MSG_PUB.Count_Msg;
1422 If x_msg_count = 1 THEN
1423 pa_interface_utils_pub.get_messages
1424 (p_encoded => FND_API.G_TRUE,
1425 p_msg_index => 1,
1426 p_msg_count => x_msg_count,
1427 p_msg_data => x_msg_data,
1428 p_data => l_data, -- 4537865 : Replaced x_msg_data with l_data
1429 p_msg_index_out => l_msg_index_out );
1430 x_msg_data := l_data; -- 4537865 : NOCOPY related change
1431 End If;
1432 WHEN OTHERS THEN
1433 PA_SCHEDULE_UTILS.log_message(1,'ERROR in change_durarion API ..'||sqlerrm);
1434 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1435 x_msg_count := 1;
1436 x_msg_data := substrb(SQLERRM,1,240); -- 4537865 : Changed substr to substrb
1437 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_SCHEDULE_PUB',
1438 p_procedure_name => 'change_duration');
1439 If x_msg_count = 1 THEN
1440 pa_interface_utils_pub.get_messages
1441 (p_encoded => FND_API.G_TRUE,
1442 p_msg_index => 1,
1443 p_msg_count => x_msg_count,
1444 p_msg_data => x_msg_data,
1445 p_data => l_data, -- 4537865 : Replaced x_msg_data with l_data
1446 p_msg_index_out => l_msg_index_out );
1447 x_msg_data := l_data; -- 4537865 : NOCOPY related change
1448 End If;
1449 RAISE; -- This is optional depending on the needs
1450 END change_duration;
1451
1452
1453 -- Purpose : This procedure will change the hours of the passed assignment
1454 -- From its passed start date till the passed end date .
1455 -- It will change the hours either percentage or hours wise.
1456 -- Input parameters
1457 -- Parameters Type Required Description
1458 -- P_Project_Id NUMBER YES project id of the associated calendar
1459 -- P_Calendar_Id NUMBER NO Id for that calendar which is associated to this assignment
1460 -- P_Assignment_Id NUMBER YES Assignment id of the changed hours assignment
1461 -- P_Assignment_Type VARCHAR2 YES It is type of the assignment e.g OPEN /STAFFED ASSIGNMENT
1462 -- P_Start_Date DATE YES starting date for the changed hours
1463 -- P_End_Date DATE YES ending date for the changed hours
1464 -- P_Assignment_Status_Code VARCHAR2 YES Status of the assignment e.g OPEN/CONFIRM/PROVISIONAL
1465 -- P_Change_Hours_Type_Code VARCHAR2 YES It is the type of code by which you want to change the hours
1466 -- e.g. HOURS/PERCENTAGE
1467 -- P_Hrs_Per_Day NUMBER YES It is the changed hours value
1468 -- P_Non_Working_Day_Flag VARCHAR2 YES It is the flag which indicate that the non working day should
1469 -- include or not e.g Y/N
1470 -- P_Calendar_Percent NUMBER YES if the hours type code is percentage then this is the percent
1471 -- age value for the changed hours
1472 -- P_Asgn_Start_Date DATE YES Start date of the assignment for which you want to
1473 -- change hours
1474 -- P_Asgn_End_Date DATE YES End date of the assignment for which you want to
1475 -- change hours
1476 --
1477
1478 PROCEDURE change_hours
1479 (
1480 p_record_version_number IN Number ,
1481 p_project_id IN Number ,
1482 p_calendar_id IN Number ,
1483 p_assignment_id IN Number ,
1484 p_assignment_type IN Varchar2 ,
1485 p_start_date IN date ,
1486 p_end_date IN date ,
1487 p_non_working_day_flag IN varchar2 := 'N',
1488 p_assignment_status_code IN Varchar2 ,
1489 p_change_hours_type_code IN varchar2 ,
1490 p_hrs_per_day IN Number ,
1491 p_calendar_percent IN Number ,
1492 p_change_calendar_type_code IN VARCHAR2 ,
1493 -- p_change_calendar_name IN VARCHAR2 ,
1494 p_change_calendar_id IN VARCHAR2 ,
1495 p_asgn_start_date IN DATE ,
1496 p_asgn_end_date IN DATE ,
1497 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1498 x_return_status OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
1499 x_msg_count OUT NOCOPY NUMBER , --File.Sql.39 bug 4440895
1500 x_msg_data OUT NOCOPY VARCHAR2 ) --File.Sql.39 bug 4440895
1501 IS
1502 l_t_exception_id NUMBER;
1503 l_msg_index_out NUMBER;
1504 l_data VARCHAR2(2000) ; -- 4537865
1505 -- For error message tokens
1506 l_asgn_req_text VARCHAR2(30);
1507 l_a_an_text VARCHAR2(30);
1508 l_asgn_req_poss_text VARCHAR2(30);
1509 BEGIN
1510 l_x_return_status := FND_API.G_RET_STS_SUCCESS ;
1511
1512 --Clear the global PL/SQL message table
1513 IF (p_init_msg_list = FND_API.G_TRUE) THEN
1514 FND_MSG_PUB.initialize;
1515 END IF;
1516
1517 IF ( p_assignment_type = 'OPEN_ASSIGNMENT' ) THEN
1518 l_asgn_req_text := FND_MESSAGE.GET_STRING('PA','PA_REQUIREMENT_TEXT');
1519 l_a_an_text := FND_MESSAGE.GET_STRING('PA','PA_A_TEXT');
1520 l_asgn_req_poss_text := FND_MESSAGE.GET_STRING('PA','PA_REQUIREMENT_POSS_TEXT');
1521 ELSE
1522 l_asgn_req_text := FND_MESSAGE.GET_STRING('PA','PA_ASSIGNMENT_TEXT');
1523 l_a_an_text := FND_MESSAGE.GET_STRING('PA','PA_AN_TEXT');
1524 l_asgn_req_poss_text := FND_MESSAGE.GET_STRING('PA','PA_ASSIGNMENT_POSS_TEXT');
1525 END IF;
1526
1527 -- The passed dates for changing the hours should be between
1528 -- start and end date of the assignment.
1529 IF ((p_start_date NOT BETWEEN p_asgn_start_date AND p_asgn_end_date ) OR
1530 (p_end_date NOT BETWEEN p_asgn_start_date AND p_asgn_end_date )) THEN
1531 Raise l_out_of_range_date;
1532 END IF;
1533
1534 PA_SCHEDULE_UTILS.log_message(1,'Start of the change_hours API ... ');
1535
1536 PA_SCH_EXCEPT_PKG.Insert_Rows(
1537 p_calendar_id => p_calendar_id ,
1538 p_assignment_id => p_assignment_id ,
1539 p_project_id => p_project_id ,
1540 p_schedule_type_code => p_assignment_type ,
1541 p_assignment_status_code => p_assignment_status_code ,
1542 p_exception_type_code => 'CHANGE_HOURS' ,
1543 p_start_date => p_start_date ,
1544 p_end_date => p_end_date ,
1545 p_resource_calendar_percent=> p_calendar_percent ,
1546 p_non_working_day_flag => p_non_working_day_flag ,
1547 p_change_hours_type_code => p_change_hours_type_code ,
1548 p_change_calendar_type_code => p_change_calendar_type_code ,
1549 -- p_change_calendar_name => p_change_calendar_name ,
1550 p_change_calendar_id => p_change_calendar_id ,
1551 p_monday_hours => p_hrs_per_day ,
1552 p_tuesday_hours => p_hrs_per_day ,
1553 p_wednesday_hours => p_hrs_per_day ,
1554 p_thursday_hours => p_hrs_per_day ,
1555 p_friday_hours => p_hrs_per_day ,
1556 p_saturday_hours => p_hrs_per_day ,
1557 p_sunday_hours => p_hrs_per_day ,
1558 x_exception_id => l_t_exception_id ,
1559 x_return_status => l_x_return_status ,
1560 x_msg_count => x_msg_count ,
1561 x_msg_data => x_msg_data );
1562
1563 IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
1564 -- Calling the change assignment schedule procedure
1565 -- which will generate the schedule after applying the hours change
1566 PA_SCHEDULE_PUB.change_asgn_schedule(
1567 p_record_version_number => p_record_version_number,
1568 p_assignment_id => p_assignment_id,
1569 p_project_id => p_project_id,
1570 p_exception_id => l_t_exception_id,
1571 x_return_status => l_x_return_status,
1572 x_msg_count => x_msg_count,
1573 x_msg_data => x_msg_data);
1574 END IF;
1575
1576 PA_SCHEDULE_UTILS.log_message(1,'End of the change_hours API ... ');
1577 x_return_status := l_x_return_status;
1578 x_msg_count := FND_MSG_PUB.Count_Msg;
1579 If x_msg_count = 1 THEN
1580 pa_interface_utils_pub.get_messages
1581 (p_encoded => FND_API.G_TRUE ,
1582 p_msg_index => 1,
1583 p_msg_count => x_msg_count ,
1584 p_msg_data => x_msg_data ,
1585 p_data => l_data, -- 4537865 : Replaced x_msg_data with l_data
1586 p_msg_index_out => l_msg_index_out );
1587 x_msg_data := l_data ; -- 4537865 : NOCOPY related Change
1588 End If;
1589
1590 EXCEPTION
1591 WHEN l_out_of_range_date THEN
1592 PA_UTILS.add_message('PA','PA_SCH_INVALID_FROM_TO_DATE',
1593 'ASMT_TYPE', l_asgn_req_text);
1594 x_return_status := FND_API.G_RET_STS_ERROR;
1595 x_msg_data := 'PA_SCH_INVALID_FROM_TO_DATE';
1596 x_msg_count := FND_MSG_PUB.Count_Msg;
1597 If x_msg_count = 1 THEN
1598 pa_interface_utils_pub.get_messages
1599 (p_encoded => FND_API.G_TRUE,
1600 p_msg_index => 1,
1601 p_msg_count => x_msg_count,
1602 p_msg_data => x_msg_data,
1603 p_data => l_data, -- 4537865 : Replaced x_msg_data with l_data
1604 p_msg_index_out => l_msg_index_out );
1605 x_msg_data := l_data ; -- 4537865 : NOCOPY related Change
1606 End If;
1607 WHEN OTHERS THEN
1608 PA_SCHEDULE_UTILS.log_message(1,'ERROR in change_hours API ..'||sqlerrm);
1609 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1610 x_msg_count := 1;
1611 x_msg_data := substrb(SQLERRM,1,240); -- 4537865 : Changed substr to substrb
1612 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_SCHEDULE_PUB',
1613 p_procedure_name => 'change_duration');
1614 If x_msg_count = 1 THEN
1615 pa_interface_utils_pub.get_messages
1616 (p_encoded => FND_API.G_TRUE,
1617 p_msg_index => 1,
1618 p_msg_count => x_msg_count,
1619 p_msg_data => x_msg_data,
1620 p_data => l_data, -- 4537865 : Replaced x_msg_data with l_data
1621 p_msg_index_out => l_msg_index_out );
1622 x_msg_data := l_data ; -- 4537865 : NOCOPY related Change
1623 End If;
1624
1625 RAISE; -- This is optional depending on the needs
1626 END change_hours;
1627
1628 -- procedure : change_work_pattern
1629 -- Purpose : This procedure will change the work pattern of the passed assignment on the basis of your
1630 -- passed pattern i.e monady hours,tuesday hours and so on for the passed period only.
1631 -- Input parameters
1632 -- Parameters Type Required Description
1633 -- P_Project_Id NUMBER YES project id of the associated calendar
1634 -- P_Calendar_Id NUMBER YES Id for that calendar which is associated to this assignment
1635 -- P_Assignment_Id NUMBER YES Assignment id of the changed work pattern assignment
1636 -- P_Assignment_Type VARCHAR2 YES It is type of the assignment e.g OPEN /STAFFED ASSIGNMENT
1637 -- P_Start_Date DATE YES starting date for the changed work pattern
1638 -- P_End_Date DATE YES ending date for the changed work pattern
1639 -- P_Monday_Hours NUMBER YES No. of hours of this day
1640 -- P_Tuesday_Hours NUMBER YES No. of hours of this day
1641 -- P_Wednesday_Hours NUMBER YES No. of hours of this day
1642 -- P_Thursday_Hours NUMBER YES No. of hours of this day
1643 -- P_Friday_Hours NUMBER YES No. of hours of this day
1644 -- P_Saturday_Hours NUMBER YES No. of hours of this day
1645 -- P_Sunday_Hours NUMBER YES No. of hours of this day
1646 -- P_Asgn_Start_Date DATE YES Start date of the assignment for which you want to
1647 -- change work pattern
1648 -- P_Asgn_End_Date DATE YES End date of the assignment for which you want to
1649 -- change work pattern
1650 --
1651
1652 PROCEDURE change_work_pattern
1653 (
1654 p_record_version_number IN Number ,
1655 p_project_id IN Number ,
1656 p_calendar_id IN Number ,
1657 p_assignment_id IN Number ,
1658 p_assignment_type IN Varchar2 ,
1659 p_start_date IN date ,
1660 p_end_date IN date ,
1661 p_monday_hours IN Number := NULL,
1662 p_tuesday_hours IN Number := NULL,
1663 p_wednesday_hours IN Number := NULL,
1664 p_thursday_hours IN Number := NULL,
1665 p_friday_hours IN Number := NULL,
1666 p_saturday_hours IN Number := NULL,
1667 p_sunday_hours IN Number := NULL,
1668 p_asgn_start_date IN DATE ,
1669 p_asgn_end_date IN DATE ,
1670 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1671 p_remove_conflict_flag IN VARCHAR2 := 'N',
1672 p_last_row_flag IN VARCHAR2 := 'Y',
1673 p_generate_timeline_flag IN VARCHAR2 := 'Y', --Unilog
1674 x_return_status OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
1675 x_msg_count OUT NOCOPY NUMBER , --File.Sql.39 bug 4440895
1676 x_msg_data OUT NOCOPY VARCHAR2 ) --File.Sql.39 bug 4440895
1677 IS
1678 l_t_exception_id NUMBER; -- Temp variable
1679 l_p_monday_hours NUMBER;
1680 l_p_tuesday_hours NUMBER;
1681 l_p_wednesday_hours NUMBER;
1682 l_p_thursday_hours NUMBER;
1683 l_p_friday_hours NUMBER;
1684 l_p_saturday_hours NUMBER;
1685 l_p_sunday_hours NUMBER;
1686 l_msg_index_out NUMBER;
1687
1688 -- For error message tokens
1689 l_asgn_req_text VARCHAR2(30);
1690 l_a_an_text VARCHAR2(30);
1691 l_asgn_req_poss_text VARCHAR2(30);
1692
1693 l_data VARCHAR2(200); -- 4537865
1694 BEGIN
1695 -- Storing status success to track the error
1696 l_x_return_status := FND_API.G_RET_STS_SUCCESS ;
1697
1698 --Clear the global PL/SQL message table
1699 IF (p_init_msg_list = FND_API.G_TRUE) THEN
1700 FND_MSG_PUB.initialize;
1701 END IF;
1702
1703 IF ( p_assignment_type = 'OPEN_ASSIGNMENT' ) THEN
1704 l_asgn_req_text := FND_MESSAGE.GET_STRING('PA','PA_REQUIREMENT_TEXT');
1705 l_a_an_text := FND_MESSAGE.GET_STRING('PA','PA_A_TEXT');
1706 l_asgn_req_poss_text := FND_MESSAGE.GET_STRING('PA','PA_REQUIREMENT_POSS_TEXT');
1707 ELSE
1708 l_asgn_req_text := FND_MESSAGE.GET_STRING('PA','PA_ASSIGNMENT_TEXT');
1709 l_a_an_text := FND_MESSAGE.GET_STRING('PA','PA_AN_TEXT');
1710 l_asgn_req_poss_text := FND_MESSAGE.GET_STRING('PA','PA_ASSIGNMENT_POSS_TEXT');
1711 END IF;
1712
1713
1714 -- The passed start date and end date should be between the
1715 -- passed start and end date of the assignmente */
1716 IF ((p_start_date NOT BETWEEN p_asgn_start_date AND p_asgn_end_date ) OR
1717 (p_end_date NOT BETWEEN p_asgn_start_date AND p_asgn_end_date )) THEN
1718 RAISE l_out_of_range_date;
1719 END IF;
1720
1721 PA_SCHEDULE_UTILS.log_message(1,'Start of the change_work_pattern API ... ');
1722
1723 l_p_monday_hours := p_monday_hours;
1724 l_p_tuesday_hours := p_tuesday_hours;
1725 l_p_wednesday_hours := p_wednesday_hours;
1726 l_p_thursday_hours := p_thursday_hours;
1727 l_p_friday_hours := p_friday_hours;
1728 l_p_saturday_hours := p_saturday_hours;
1729 l_p_sunday_hours := p_sunday_hours;
1730
1731
1732 /*
1733 -- The passing day hours should not beyond the 24 hours and should not null
1734 IF (p_monday_hours IS NULL ) THEN
1735 l_p_monday_hours := 0;
1736 ELSE
1737 l_p_monday_hours := p_monday_hours;
1738 END IF;
1739
1740 IF (p_tuesday_hours IS NULL ) THEN
1741 l_p_tuesday_hours := 0;
1742 ELSE
1743 l_p_tuesday_hours := p_tuesday_hours;
1744 END IF;
1745
1746 IF (p_wednesday_hours IS NULL ) THEN
1747 l_p_wednesday_hours := 0;
1748 ELSE
1749 l_p_wednesday_hours := p_wednesday_hours;
1750 END IF;
1751
1752 IF (p_thursday_hours IS NULL ) THEN
1753 l_p_thursday_hours := 0;
1754 ELSE
1755 l_p_thursday_hours := p_thursday_hours;
1756 END IF;
1757
1758 IF (p_friday_hours IS NULL ) THEN
1759 l_p_friday_hours := 0;
1760 ELSE
1761 l_p_friday_hours := p_friday_hours;
1762 END IF;
1763
1764 IF (p_saturday_hours IS NULL ) THEN
1765 l_p_saturday_hours := 0;
1766 ELSE
1767 l_p_saturday_hours := p_saturday_hours;
1768 END IF;
1769
1770 IF (p_sunday_hours IS NULL ) THEN
1771 l_p_sunday_hours := 0;
1772 ELSE
1773 l_p_sunday_hours := p_sunday_hours;
1774 END IF;
1775 */
1776
1777 -- When called by mass_update_schedule, null working hours are passed in as -99.
1778 -- Need to convert -99 back to null.
1779 IF p_monday_hours = -99 THEN
1780 l_p_monday_hours := null;
1781 END IF;
1782 IF p_tuesday_hours = -99 THEN
1783 l_p_tuesday_hours := null;
1784 END IF;
1785 IF p_wednesday_hours = -99 THEN
1786 l_p_wednesday_hours := null;
1787 END IF;
1788 IF p_thursday_hours = -99 THEN
1789 l_p_thursday_hours := null;
1790 END IF;
1791 IF p_friday_hours = -99 THEN
1792 l_p_friday_hours := null;
1793 END IF;
1794 IF p_saturday_hours = -99 THEN
1795 l_p_saturday_hours := null;
1796 END IF;
1797 IF p_sunday_hours = -99 THEN
1798 l_p_sunday_hours := null;
1799 END IF;
1800
1801 pa_schedule_utils.debug('pa.plsql.pa_schedule_pub', 'Calling pa_sch_except_pkg.insert_rows', 6);
1802
1803 PA_SCH_EXCEPT_PKG.Insert_Rows(
1804 p_calendar_id => p_calendar_id ,
1805 p_assignment_id => p_assignment_id ,
1806 p_project_id => p_project_id ,
1807 p_schedule_type_code => p_assignment_type ,
1808 p_exception_type_code => 'CHANGE_WORK_PATTERN' ,
1809 p_start_date => p_start_date ,
1810 p_end_date => p_end_date ,
1811 p_monday_hours => l_p_monday_hours ,
1812 p_tuesday_hours => l_p_tuesday_hours ,
1813 p_wednesday_hours => l_p_wednesday_hours ,
1814 p_thursday_hours => l_p_thursday_hours ,
1815 p_friday_hours => l_p_friday_hours ,
1816 p_saturday_hours => l_p_saturday_hours ,
1817 p_sunday_hours => l_p_sunday_hours ,
1818 x_exception_id => l_t_exception_id ,
1819 x_return_status => l_x_return_status ,
1820 x_msg_count => x_msg_count ,
1821 x_msg_data => x_msg_data );
1822
1823 IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS AND p_last_row_flag = 'Y') THEN
1824 -- Calling the change assignment schedule procedure that will generate the changed schedule
1825 -- of the passed assignment for the given exception i.e change work patern
1826 pa_schedule_utils.debug('pa.plsql.pa_schedule_pub', 'Calling change_asgn_schedule', 6);
1827 PA_SCHEDULE_PUB.change_asgn_schedule(
1828 p_record_version_number => p_record_version_number,
1829 p_assignment_id => p_assignment_id,
1830 p_project_id => p_project_id,
1831 p_exception_id => NULL,
1832 p_remove_conflict_flag => p_remove_conflict_flag,
1833 p_generate_timeline_flag => p_generate_timeline_flag,--Unilog
1834 x_return_status => l_x_return_status,
1835 x_msg_count => x_msg_count,
1836 x_msg_data => x_msg_data);
1837 END IF;
1838
1839 PA_SCHEDULE_UTILS.log_message(1,'End of the change_work_pattern API ... ');
1840 x_return_status := l_x_return_status;
1841
1842 x_msg_count := FND_MSG_PUB.Count_Msg;
1843 If x_msg_count = 1 THEN
1844 pa_interface_utils_pub.get_messages
1845 (p_encoded => FND_API.G_TRUE ,
1846 p_msg_index => 1,
1847 p_msg_count => x_msg_count ,
1848 p_msg_data => x_msg_data ,
1849 p_data => l_data, -- 4537865 : Replaced x_msg_data with l_data
1850 p_msg_index_out => l_msg_index_out );
1851 x_msg_data := l_data ; -- 4537865 : NOCOPY related change
1852 End If;
1853
1854 EXCEPTION
1855 WHEN l_out_of_range_date THEN
1856 PA_UTILS.add_message('PA','PA_SCH_INVALID_FROM_TO_DATE',
1857 'ASMT_TYPE', l_asgn_req_text);
1858 x_return_status := FND_API.G_RET_STS_ERROR;
1859 x_msg_data := 'PA_SCH_INVALID_FROM_TO_DATE';
1860 x_msg_count := FND_MSG_PUB.Count_Msg;
1861 If x_msg_count = 1 THEN
1862 pa_interface_utils_pub.get_messages
1863 (p_encoded => FND_API.G_TRUE,
1864 p_msg_index => 1,
1865 p_msg_count => x_msg_count,
1866 p_msg_data => x_msg_data,
1867 p_data => l_data, -- 4537865 : Replaced x_msg_data with l_data
1868 p_msg_index_out => l_msg_index_out );
1869 x_msg_data := l_data ; -- 4537865 : NOCOPY related change
1870 End If;
1871 WHEN OTHERS THEN
1872 PA_SCHEDULE_UTILS.log_message(1,'ERROR in change_work_pattern API ..'||sqlerrm);
1873 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1874 x_msg_count := 1;
1875 x_msg_data := substrb(SQLERRM,1,240); -- 4537865 : Replaced substr with substrb
1876 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_SCHEDULE_PUB',
1877 p_procedure_name => 'change_work_pattern');
1878 If x_msg_count = 1 THEN
1879 pa_interface_utils_pub.get_messages
1880 (p_encoded => FND_API.G_TRUE,
1881 p_msg_index => 1,
1882 p_msg_count => x_msg_count,
1883 p_msg_data => x_msg_data,
1884 p_data => l_data, -- 4537865 : Replaced x_msg_data with l_data
1885 p_msg_index_out => l_msg_index_out );
1886 x_msg_data := l_data ; -- 4537865 : NOCOPY related change
1887 End If;
1888
1889 RAISE; -- This is optional depending on the needs
1890 END change_work_pattern;
1891
1892
1893
1894 -- Procedure : change_status
1895 -- Purpose : This procedure will change tha status of the passed assignment i.e provisional.confirm
1896 -- etc. It will change the status only for the passed period i.e start date and end date.
1897 -- Input parameters
1898 -- Parameters Type Required Description
1899 -- P_Project_Id NUMBER YES project id of the associated calendar
1900 -- P_Calendar_Id NUMBER NO Id for that calendar which is associated to this assignment
1901 -- P_Assignment_Id NUMBER YES Assignment id of the changed status assignment
1902 -- P_Assignment_Type VARCHAR2 YES It is type of the assignment e.g OPEN /STAFFED ASSIGNMENT
1903 -- P_Status_Type VARCHAR2 YES It is status type.
1904 -- P_Start_Date DATE YES starting date for the changed status
1905 -- P_End_Date DATE YES ending date for the changed status
1906 -- P_Assignment_Status_Code VARCHAR2 YES Status of the assignment e.g OPEN/CONFIRM/PROVISIONAL
1907 -- P_Asgn_Start_Date DATE YES Start date of the assignment for which you want to
1908 -- change status
1909 -- P_Asgn_End_Date DATE YES End date of the assignment for which you want to
1910 -- change status
1911 -- p_save_to_hist VARCHAR2 NO If TRUE, then the change_approval_status proc.
1912 -- is called and the change is saved to the
1913 -- exceptions history. This is the case when
1914 -- the procedure is called from the UI.
1915 -- If FALSE, the the change_approval_status proc.
1916 -- is not called and the change is not saved to
1917 -- the exceptions history. FALSE should be used in
1918 -- all cases except when called from UI.
1919 --
1920
1921 PROCEDURE change_status
1922 (
1923 p_record_version_number IN Number ,
1924 p_project_id IN Number ,
1925 p_calendar_id IN Number ,
1926 p_assignment_id IN Number ,
1927 p_assignment_type IN Varchar2 ,
1928 p_status_type IN Varchar2 ,
1929 p_start_date IN date ,
1930 p_end_date IN date ,
1931 p_assignment_status_code IN Varchar2 ,
1932 p_asgn_start_date IN DATE ,
1933 p_asgn_end_date IN DATE ,
1934 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1935 p_save_to_hist IN VARCHAR2 := FND_API.G_TRUE,
1936 x_return_status OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
1937 x_msg_count OUT NOCOPY NUMBER , --File.Sql.39 bug 4440895
1938 x_msg_data OUT NOCOPY VARCHAR2 ) --File.Sql.39 bug 4440895
1939 IS
1940 l_t_check_cancel VARCHAR2(1); --Temp variable
1941 l_invalid_asgn_cancelled_date EXCEPTION;
1942 l_stale_asmt_data EXCEPTION;
1943 l_status_type VARCHAR2(30);
1944 l_error_message_code VARCHAR2(50);
1945 l_record_version_number NUMBER;
1946 l_person_id NUMBER;
1947 l_msg_index_out NUMBER;
1948 l_exception_id NUMBER;
1949 l_return_status VARCHAR2(1);
1950 l_assignment_status_name pa_project_statuses.project_status_name%TYPE;
1951
1952 -- For error message tokens
1953 l_asgn_req_text VARCHAR2(30);
1954 l_a_an_text VARCHAR2(30);
1955 l_asgn_req_poss_text VARCHAR2(30);
1956 l_data VARCHAR2(2000); -- 4537865
1957 -- For retrieving project_status_name
1958 -- 3054787: Select from tables directly to improve performance.
1959 CURSOR get_project_status_name IS
1960 SELECT pps.project_status_name
1961 FROM pa_projects_all ppa, pa_project_statuses pps
1962 WHERE ppa.project_id = p_project_id
1963 AND ppa.project_status_code = pps.project_status_code;
1964
1965 l_project_status_name pa_project_statuses.project_status_name%TYPE;
1966
1967 BEGIN
1968 l_x_return_status := FND_API.G_RET_STS_SUCCESS ;
1969
1970 --Clear the global PL/SQL message table
1971 IF FND_API.TO_BOOLEAN( p_init_msg_list ) THEN
1972 FND_MSG_PUB.initialize;
1973 END IF;
1974
1975 IF ( p_assignment_type = 'OPEN_ASSIGNMENT' ) THEN
1976 l_asgn_req_text := FND_MESSAGE.GET_STRING('PA','PA_REQUIREMENT_TEXT');
1977 l_a_an_text := FND_MESSAGE.GET_STRING('PA','PA_A_TEXT');
1978 l_asgn_req_poss_text := FND_MESSAGE.GET_STRING('PA','PA_REQUIREMENT_POSS_TEXT');
1979 ELSE
1980 l_asgn_req_text := FND_MESSAGE.GET_STRING('PA','PA_ASSIGNMENT_TEXT');
1981 l_a_an_text := FND_MESSAGE.GET_STRING('PA','PA_AN_TEXT');
1982 l_asgn_req_poss_text := FND_MESSAGE.GET_STRING('PA','PA_ASSIGNMENT_POSS_TEXT');
1983 END IF;
1984
1985 PA_SCHEDULE_UTILS.log_message(1,'Start of Change_Status ... ');
1986
1987 -- p_assignment_status_code should not be null
1988 IF (p_assignment_status_code is NULL) THEN
1989 PA_UTILS.Add_Message ('PA', 'PA_SCH_ASGN_STATUS_NULL'); -- is this message okay?
1990 RAISE FND_API.G_EXC_ERROR;
1991 END IF;
1992
1993 -- The passed dates should fall between the assignment start date and assignment end date */
1994 IF ((p_start_date NOT BETWEEN p_asgn_start_date AND p_asgn_end_date ) OR
1995 (p_end_date NOT BETWEEN p_asgn_start_date AND p_asgn_end_date )) THEN
1996 Raise l_out_of_range_date;
1997 END IF;
1998
1999 -- New Project Status Control added for PRM v1.0.2.
2000 -- If extending the staffed assignment duration with a new status, the status should be allowed for the status of the project this assignment belongs to.
2001 IF (p_assignment_status_code IS NOT NULL) AND (p_assignment_type <> 'OPEN_ASSIGNMENT') THEN
2002 l_return_status := PA_ASSIGNMENT_UTILS.is_asgmt_allow_stus_ctl_check(
2003 p_asgmt_status_code => p_assignment_status_code,
2004 p_project_id => p_project_id,
2005 p_add_message => 'N');
2006
2007 IF l_return_status <> 'Y' THEN
2008 OPEN get_project_status_name;
2009 FETCH get_project_status_name INTO l_project_status_name;
2010 CLOSE get_project_status_name;
2011
2012 SELECT project_status_name
2013 INTO l_assignment_status_name
2014 FROM pa_project_statuses
2015 WHERE project_status_code = p_assignment_status_code;
2016
2017 RAISE l_asgn_stus_not_for_proj_stus;
2018 END IF;
2019 END IF;
2020
2021 -- Added this code , since status_type is derived parameter.
2022 IF p_status_type is NULL then
2023 if ( p_assignment_type = 'OPEN_ASSIGNMENT' ) then
2024 l_status_type := 'OPEN_ASGMT';
2025 else
2026 l_status_type := 'STAFFED_ASGMT';
2027 end if;
2028 else
2029 l_status_type := p_status_type;
2030 end if;
2031
2032 PA_SCHEDULE_UTILS.log_message(1,'Calling Assignment API ... ');
2033
2034 -- Partially cancelled assignments are now possible, so we are removing the
2035 -- check.
2036
2037 -- Checking the assignment that if it is cancelled then it will not be partialy cancelled it
2038 -- should be fully cancelled
2039 -- IF (p_assignment_type = 'OPEN_ASSIGNMENT') THEN
2040 -- l_t_check_cancel := PA_ASSIGNMENT_UTILS.is_open_asgmt_cancelled(
2041 -- p_status_code => p_assignment_status_code,
2042 -- p_status_type => l_status_type);
2043 -- IF(UPPER(l_t_check_cancel) = 'Y') THEN
2044 -- IF ((p_start_date <> p_asgn_start_date) OR
2045 -- (p_end_date <> p_asgn_end_date)) THEN
2046 -- RAISE l_invalid_asgn_cancelled_date;
2047 -- END IF;
2048 -- END IF;
2049 -- ELSIF (p_assignment_type = 'STAFFED_ASSIGNMENT') THEN
2050 -- l_t_check_cancel := PA_ASSIGNMENT_UTILS.is_staffed_asgmt_cancelled(
2051 -- p_status_code => p_assignment_status_code,
2052 -- p_status_type => l_status_type);
2053 -- IF(UPPER(l_t_check_cancel) = 'Y') THEN
2054 -- IF ((p_start_date <> p_asgn_start_date) OR
2055 -- (p_end_date <> p_asgn_end_date)) THEN
2056 -- RAISE l_invalid_asgn_cancelled_date;
2057 -- END IF;
2058 -- END IF;
2059 -- END IF;
2060 -- */
2061
2062 PA_SCHEDULE_UTILS.log_message(1,'Start of the change_status API ... ');
2063 PA_SCH_EXCEPT_PKG.Insert_Rows(
2064 p_calendar_id => p_calendar_id ,
2065 p_assignment_id => p_assignment_id ,
2066 p_project_id => p_project_id ,
2067 p_schedule_type_code => p_assignment_type ,
2068 p_assignment_status_code => p_assignment_status_code ,
2069 p_exception_type_code => 'CHANGE_STATUS' ,
2070 p_start_date => p_start_date ,
2071 p_end_date => p_end_date ,
2072 x_exception_id => l_exception_id ,
2073 x_return_status => l_x_return_status ,
2074 x_msg_count => x_msg_count ,
2075 x_msg_data => x_msg_data );
2076
2077 IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
2078 -- Calling the procedure change assignment schedule that will
2079 -- generate the changed schedule for the
2080 -- passed asignment only for the passed period
2081 PA_SCHEDULE_PUB.change_asgn_schedule(
2082 p_record_version_number => p_record_version_number,
2083 p_assignment_id => p_assignment_id,
2084 p_project_id => p_project_id,
2085 p_exception_id => l_exception_id,
2086 p_save_to_hist => p_save_to_hist,
2087 x_return_status => l_x_return_status,
2088 x_msg_count => x_msg_count,
2089 x_msg_data => x_msg_data);
2090 END IF;
2091
2092 PA_SCHEDULE_UTILS.log_message(1,'End of the change_status API ... ');
2093 x_return_status := l_x_return_status;
2094
2095 x_msg_count := FND_MSG_PUB.Count_Msg;
2096 If x_msg_count = 1 THEN
2097 pa_interface_utils_pub.get_messages
2098 (p_encoded => FND_API.G_TRUE ,
2099 p_msg_index => 1,
2100 p_msg_count => x_msg_count ,
2101 p_msg_data => x_msg_data ,
2102 p_data => l_data, -- 4537865 : Replaced x_msg_data with l_data
2103 p_msg_index_out => l_msg_index_out );
2104 x_msg_data := l_data ; -- 4537865 : NOCOPY related change
2105 End If;
2106
2107 EXCEPTION
2108 WHEN FND_API.G_EXC_ERROR THEN
2109 x_return_status := FND_API.G_RET_STS_ERROR;
2110 x_msg_count := FND_MSG_PUB.Count_Msg;
2111
2112 IF x_msg_count = 1 THEN
2113 pa_interface_utils_pub.get_messages
2114 (p_encoded => FND_API.G_TRUE,
2115 p_msg_index => 1,
2116 p_data => x_msg_data,
2117 p_msg_index_out => l_msg_index_out );
2118 END IF;
2119 WHEN l_stale_asmt_data THEN
2120 PA_UTILS.add_message('PA','PA_XC_RECORD_CHANGED');
2121 x_return_status := FND_API.G_RET_STS_ERROR;
2122 x_msg_data := 'PA_XC_RECORD_CHANGED';
2123 x_msg_count := FND_MSG_PUB.Count_Msg;
2124 If x_msg_count = 1 THEN
2125 pa_interface_utils_pub.get_messages
2126 (p_encoded => FND_API.G_TRUE,
2127 p_msg_index => 1,
2128 p_msg_count => x_msg_count,
2129 p_msg_data => x_msg_data,
2130 p_data => l_data, -- 4537865 : Replaced x_msg_data with l_data
2131 p_msg_index_out => l_msg_index_out );
2132 x_msg_data := l_data ; -- 4537865 : NOCOPY related change
2133 End If;
2134 WHEN l_invalid_asgn_cancelled_date THEN
2135 PA_UTILS.add_message('PA','PA_INVALID_ASGN_CANCELLED_DATE',
2136 'ASMT_TYPE_POSS', l_asgn_req_poss_text,
2137 'A_OR_AN', l_a_an_text,
2138 'ASMT_TYPE', l_asgn_req_text);
2139 x_return_status := FND_API.G_RET_STS_ERROR;
2140 x_msg_data := 'PA_INVALID_ASGN_CANCELLED_DATE';
2141 x_msg_count := FND_MSG_PUB.Count_Msg;
2142 If x_msg_count = 1 THEN
2143 pa_interface_utils_pub.get_messages
2144 (p_encoded => FND_API.G_TRUE,
2145 p_msg_index => 1,
2146 p_msg_count => x_msg_count,
2147 p_msg_data => x_msg_data,
2148 p_data => l_data, -- 4537865 : Replaced x_msg_data with l_data
2149 p_msg_index_out => l_msg_index_out );
2150 x_msg_data := l_data ; -- 4537865 : NOCOPY related change
2151 End If;
2152 WHEN l_out_of_range_date THEN
2153 PA_UTILS.add_message('PA','PA_SCH_INVALID_FROM_TO_DATE',
2154 'ASMT_TYPE', l_asgn_req_text);
2155 x_return_status := FND_API.G_RET_STS_ERROR;
2156 x_msg_data := 'PA_SCH_INVALID_FROM_TO_DATE';
2157 x_msg_count := FND_MSG_PUB.Count_Msg;
2158 If x_msg_count = 1 THEN
2159 pa_interface_utils_pub.get_messages
2160 (p_encoded => FND_API.G_TRUE,
2161 p_msg_index => 1,
2162 p_msg_count => x_msg_count,
2163 p_msg_data => x_msg_data,
2164 p_data => l_data, -- 4537865 : Replaced x_msg_data with l_data
2165 p_msg_index_out => l_msg_index_out );
2166 x_msg_data := l_data ; -- 4537865 : NOCOPY related change
2167 End If;
2168 WHEN l_asgn_stus_not_for_proj_stus THEN
2169 PA_UTILS.Add_Message( p_app_short_name => 'PA',
2170 p_msg_name => 'PA_ASGN_STUS_NOT_FOR_PROJ_STUS',
2171 p_token1 => 'PROJ_STATUS',
2172 p_value1 => l_project_status_name,
2173 p_token2 => 'ASGN_STATUS',
2174 p_value2 => l_assignment_status_name);
2175 x_return_status := FND_API.G_RET_STS_ERROR;
2176 x_msg_data := 'PA_ASGN_STUS_NOT_FOR_PROJ_STUS';
2177 x_msg_count := FND_MSG_PUB.Count_Msg;
2178 If x_msg_count = 1 THEN
2179 pa_interface_utils_pub.get_messages
2180 (p_encoded => FND_API.G_TRUE,
2181 p_msg_index => 1,
2182 p_msg_count => x_msg_count,
2183 p_msg_data => x_msg_data,
2184 p_data => l_data, -- 4537865 : Replaced x_msg_data with l_data
2185 p_msg_index_out => l_msg_index_out );
2186 x_msg_data := l_data ; -- 4537865 : NOCOPY related change
2187 End If;
2188 WHEN OTHERS THEN
2189 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2190 x_msg_count := 1;
2191 x_msg_data := substrb(SQLERRM,1,240); -- 4537865 Replaced substr with substrb
2192 PA_SCHEDULE_UTILS.log_message(1,'ERROR in change_status API ..'||sqlerrm);
2193 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_SCHEDULE_PUB',
2194 p_procedure_name => 'change_status');
2195 If x_msg_count = 1 THEN
2196 pa_interface_utils_pub.get_messages
2197 (p_encoded => FND_API.G_TRUE,
2198 p_msg_index => 1,
2199 p_msg_count => x_msg_count,
2200 p_msg_data => x_msg_data,
2201 p_data => l_data, -- 4537865 : Replaced x_msg_data with l_data
2202 p_msg_index_out => l_msg_index_out );
2203 x_msg_data := l_data ; -- 4537865 : NOCOPY related change
2204 End If;
2205 RAISE; -- This is optional depending on the needs
2206 END change_status;
2207
2208 --
2209 -- Procedure : change_calendar
2210 -- Purpose : This procedure will change the calendar for the passed assignment But it will
2211 -- change the calendar only for the passed period i.e start date and end date.
2212 -- Input parameters
2213 -- Parameters Type Required Description
2214 -- P_Project_Id NUMBER YES project id of the associated calendar
2215 -- P_Calendar_Id NUMBER YES Id for that calendar which is associated to this assignment
2216 -- P_Calendar_Name VARCHAR2 YES It is the name of the calendar
2217 -- P_Assignment_Id NUMBER YES Assignment id of the changed calendar assignment
2218 -- P_Assignment_Type VARCHAR2 YES It is type of the assignment e.g OPEN /STAFFED ASSIGNMENT
2219 -- P_Start_Date DATE YES starting date for the changed calendar
2220 -- P_End_Date DATE YES ending date for the changed calendar
2221 -- P_Asgn_Start_Date DATE YES Start date of the assignment for which you want to
2222 -- change calendar
2223 -- P_Asgn_End_Date DATE YES End date of the assignment for which you want to
2224 -- change calendar
2225 --
2226
2227 PROCEDURE change_calendar
2228 (
2229 p_record_version_number IN Number ,
2230 p_project_id IN Number ,
2231 p_calendar_id IN Number ,
2232 p_calendar_name IN varchar2 ,
2233 p_assignment_id IN Number ,
2234 p_assignment_type IN Varchar2 ,
2235 p_start_date IN date ,
2236 p_end_date IN date ,
2237 p_asgn_start_date IN DATE ,
2238 p_asgn_end_date IN DATE ,
2239 x_return_status OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
2240 x_msg_count OUT NOCOPY NUMBER , --File.Sql.39 bug 4440895
2241 x_msg_data OUT NOCOPY VARCHAR2 ) --File.Sql.39 bug 4440895
2242 IS
2243 l_t_exception_id NUMBER; -- temp variable
2244 l_calendar_id NUMBER; -- temp variable
2245 l_invalid_duplicate_cal_name EXCEPTION;
2246 l_msg_index_out NUMBER;
2247 l_data VARCHAR2(2000) ; -- 4537865
2248 -- For error message tokens
2249 l_asgn_req_text VARCHAR2(30);
2250 l_a_an_text VARCHAR2(30);
2251 l_asgn_req_poss_text VARCHAR2(30);
2252 BEGIN
2253 -- storing the status success to track the error
2254 l_x_return_status := FND_API.G_RET_STS_SUCCESS ;
2255
2256 --Clear the global PL/SQL message table
2257 fnd_msg_pub.initialize;
2258
2259 IF ( p_assignment_type = 'OPEN_ASSIGNMENT' ) THEN
2260 l_asgn_req_text := FND_MESSAGE.GET_STRING('PA','PA_REQUIREMENT_TEXT');
2261 l_a_an_text := FND_MESSAGE.GET_STRING('PA','PA_A_TEXT');
2262 l_asgn_req_poss_text := FND_MESSAGE.GET_STRING('PA','PA_REQUIREMENT_POSS_TEXT');
2263 ELSE
2264 l_asgn_req_text := FND_MESSAGE.GET_STRING('PA','PA_ASSIGNMENT_TEXT');
2265 l_a_an_text := FND_MESSAGE.GET_STRING('PA','PA_AN_TEXT');
2266 l_asgn_req_poss_text := FND_MESSAGE.GET_STRING('PA','PA_ASSIGNMENT_POSS_TEXT');
2267 END IF;
2268
2269 -- Current functionality allows user to change the work patern by specifing the calendar
2270 -- for the whole period so we don't need to check the start /end date for null.
2271 -- End date or Start date or both should not be null for the change calendar
2272 -- IF ((p_start_date IS NULL) OR (p_end_date IS NULL)) THEN
2273 -- RAISE l_from_to_date_null;
2274 -- END IF;
2275
2276 -- If the user select calendar name only then taking the calendar id
2277 IF (p_calendar_id IS NULL ) THEN
2278 BEGIN
2279 SELECT calendar_id
2280 INTO l_calendar_id
2281 FROM jtf_calendars_vl
2282 WHERE calendar_name = p_calendar_name;
2283
2284
2285 x_msg_count := FND_MSG_PUB.Count_Msg;
2286 If x_msg_count = 1 THEN
2287 pa_interface_utils_pub.get_messages
2288 (p_encoded => FND_API.G_TRUE ,
2289 p_msg_index => 1,
2290 p_msg_count => x_msg_count ,
2291 p_msg_data => x_msg_data ,
2292 p_data => l_data, -- 4537865 : Replaced x_msg_data with l_data
2293 p_msg_index_out => l_msg_index_out );
2294 x_msg_data := l_data ; -- 4537865 : NOCOPY related change
2295 End If;
2296
2297 EXCEPTION
2298 WHEN NO_DATA_FOUND or TOO_MANY_ROWS THEN
2299 RAISE l_invalid_duplicate_cal_name;
2300 WHEN OTHERS THEN
2301 Raise;
2302 END;
2303 ELSE
2304 l_calendar_id := p_calendar_id;
2305 END IF;
2306
2307 PA_SCHEDULE_UTILS.log_message(1,'Start of the change_calendar API ... ');
2308 PA_SCH_EXCEPT_PKG.Insert_Rows(
2309 p_calendar_id => l_calendar_id ,
2310 p_assignment_id => p_assignment_id ,
2311 p_project_id => p_project_id ,
2312 p_schedule_type_code => p_assignment_type ,
2313 p_exception_type_code => 'CHANGE_CALENDAR' ,
2314 p_start_date => p_start_date ,
2315 p_end_date => p_end_date ,
2316 x_exception_id => l_t_exception_id ,
2317 x_return_status => l_x_return_status ,
2318 x_msg_count => x_msg_count ,
2319 x_msg_data => x_msg_data );
2320
2321 IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
2322 -- Calling the change assignment schedule procedure that will generate the changed schedule
2323 -- according to the passed calendar but only for given period
2324 PA_SCHEDULE_PUB.change_asgn_schedule(
2325 p_record_version_number => p_record_version_number,
2326 p_assignment_id => p_assignment_id,
2327 p_project_id => p_project_id,
2328 p_exception_id => l_t_exception_id,
2329 x_return_status => l_x_return_status,
2330 x_msg_count => x_msg_count,
2331 x_msg_data => x_msg_data);
2332 END IF;
2333
2334 PA_SCHEDULE_UTILS.log_message(1,'End of the change_calendar API ... ');
2335 x_return_status := l_x_return_status;
2336
2337 x_msg_count := FND_MSG_PUB.Count_Msg;
2338 If x_msg_count = 1 THEN
2339 pa_interface_utils_pub.get_messages
2340 (p_encoded => FND_API.G_TRUE ,
2341 p_msg_index => 1,
2342 p_msg_count => x_msg_count ,
2343 p_msg_data => x_msg_data ,
2344 p_data => l_data, -- 4537865 : Replaced x_msg_data with l_data
2345 p_msg_index_out => l_msg_index_out );
2346 x_msg_data := l_data ; -- 4537865 : NOCOPY related change
2347 End If;
2348
2349
2350 EXCEPTION
2351 WHEN l_invalid_duplicate_cal_name THEN
2352 PA_UTILS.add_message('PA','PA_CALENDAR_INVALID_AMBIGOUS');
2353 x_return_status := FND_API.G_RET_STS_ERROR;
2354 x_msg_data := 'PA_CALENDAR_INVALID_AMBIGOUS';
2355 x_msg_count := FND_MSG_PUB.Count_Msg;
2356 If x_msg_count = 1 THEN
2357 pa_interface_utils_pub.get_messages
2358 (p_encoded => FND_API.G_TRUE,
2359 p_msg_index => 1,
2360 p_msg_count => x_msg_count,
2361 p_msg_data => x_msg_data,
2362 p_data => l_data, -- 4537865 :Replaced x_msg_data with l_data
2363 p_msg_index_out => l_msg_index_out );
2364 x_msg_data := l_data ; -- 4537865 : NOCOPY related change
2365 End If;
2366 WHEN l_from_to_date_null THEN
2367 PA_UTILS.add_message('PA','PA_SCH_FROM_TO_DATE_NULL');
2368 x_return_status := FND_API.G_RET_STS_ERROR;
2369 x_msg_data := 'PA_SCH_FROM_TO_DATE_NULL';
2370 x_msg_count := FND_MSG_PUB.Count_Msg;
2371 If x_msg_count = 1 THEN
2372 pa_interface_utils_pub.get_messages
2373 (p_encoded => FND_API.G_TRUE,
2374 p_msg_index => 1,
2375 p_msg_count => x_msg_count,
2376 p_msg_data => x_msg_data,
2377 p_data => l_data, -- 4537865 :Replaced x_msg_data with l_data
2378 p_msg_index_out => l_msg_index_out );
2379 x_msg_data := l_data ; -- 4537865 : NOCOPY related change
2380 End If;
2381 WHEN OTHERS THEN
2382 PA_SCHEDULE_UTILS.log_message(1,'ERROR in change_calendar API ..'||sqlerrm);
2383 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2384 x_msg_count := 1;
2385 x_msg_data := substrb(SQLERRM,1,240); -- Changed substr to substrb : 4537865
2386 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_SCHEDULE_PUB',
2387 p_procedure_name => 'change_calendar');
2388 If x_msg_count = 1 THEN
2389 pa_interface_utils_pub.get_messages
2390 (p_encoded => FND_API.G_TRUE,
2391 p_msg_index => 1,
2392 p_msg_count => x_msg_count,
2393 p_msg_data => x_msg_data,
2394 p_data => l_data, -- 4537865 :Replaced x_msg_data with l_data
2395 p_msg_index_out => l_msg_index_out );
2396 x_msg_data := l_data ; -- 4537865 : NOCOPY related change
2397 End If;
2398 END change_calendar;
2399
2400
2401
2402 -- Procedure : change_schedule
2403 -- Purpose : Once the schedule is created for the assignment it cane be changed by this procedure on the basis of its exception.
2404 -- This procedure does not seem to be used.
2405
2406 PROCEDURE change_schedule
2407 (x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
2408 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
2409 x_msg_data OUT NOCOPY VARCHAR2 ) --File.Sql.39 bug 4440895
2410 IS
2411 l_msg_index_out NUMBER;
2412 l_data varchar2(2000) ; -- 4537865
2413
2414 -- This cursor will select the distinct assignment id
2415 CURSOR csr_sch_excp IS
2416 SELECT distinct a.assignment_id, b.record_version_number, b.project_id
2417 FROM pa_schedule_exceptions a , pa_project_assignments b
2418 WHERE a.assignment_id = b.assignment_id
2419 ORDER by a.assignment_id;
2420 BEGIN
2421 -- store the status success to track the error
2422 l_x_return_status := FND_API.G_RET_STS_SUCCESS ;
2423
2424 PA_SCHEDULE_UTILS.log_message(1,'Start of the change_schedule API ... ');
2425
2426 FOR rec_sch_excp IN csr_sch_excp LOOP
2427 PA_SCHEDULE_UTILS.log_message(1,
2428 'Schedule Exception table - assignment Id :'||
2429 to_char(rec_sch_excp.assignment_id));
2430 -- Calling the procedure change_asgn_schedule that will generate the
2431 -- change schedule for the passed start date and end date
2432 PA_SCHEDULE_PUB.change_asgn_schedule(
2433 p_record_version_number => rec_sch_excp.record_version_number,
2434 p_assignment_id => rec_sch_excp.assignment_id,
2435 p_project_id => rec_sch_excp.project_id,
2436 p_exception_id => null,
2437 x_return_status => l_x_return_status,
2438 x_msg_count => x_msg_count,
2439 x_msg_data => x_msg_data);
2440 END LOOP;
2441 PA_SCHEDULE_UTILS.log_message(1,'End of the change_schedule API ... ');
2442
2443 x_return_status := l_x_return_status;
2444
2445 x_msg_count := FND_MSG_PUB.Count_Msg;
2446 If x_msg_count = 1 THEN
2447 pa_interface_utils_pub.get_messages
2448 (p_encoded => FND_API.G_TRUE ,
2449 p_msg_index => 1,
2450 p_msg_count => x_msg_count ,
2451 p_msg_data => x_msg_data ,
2452 p_data => l_data, -- 4537865 :Replaced x_msg_data with l_data
2453 p_msg_index_out => l_msg_index_out );
2454 x_msg_data := l_data ; -- 4537865 : NOCOPY related change
2455 End If;
2456
2457 EXCEPTION
2458 WHEN OTHERS THEN
2459 PA_SCHEDULE_UTILS.log_message(1,'ERROR in change_schedule API ..'||sqlerrm);
2460 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2461 x_msg_count := 1;
2462 x_msg_data := substrb(SQLERRM,1,240); -- 4537865 : Replaced substr usage with substrb
2463 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_SCHEDULE_PUB',
2464 p_procedure_name => 'change_schedule');
2465 If x_msg_count = 1 THEN
2466 pa_interface_utils_pub.get_messages
2467 (p_encoded => FND_API.G_TRUE ,
2468 p_msg_index => 1,
2469 p_msg_count => x_msg_count ,
2470 p_msg_data => x_msg_data ,
2471 p_data => l_data, -- 4537865 :Replaced x_msg_data with l_data
2472 p_msg_index_out => l_msg_index_out );
2473 x_msg_data := l_data ; -- 4537865 : NOCOPY related change
2474 End If;
2475 RAISE; -- This is optional depending on the needs
2476 END change_schedule;
2477
2478
2479
2480 -- Procedure : change_asgn_schedule
2481 -- Purpose : This procedure will be called from each schedule change page via
2482 -- workflow. This procedure will apply the exceptions for the assignment
2483 -- on the assignment schedules.
2484 -- Input parameters
2485 -- Parameters Type Required Description
2486 -- P_Assignment_Id NUMBER YES Assignment id for the changed assignment
2487 -- P_Exception_Id NUMBER YES Exception id for changing ths chedule of the
2488 -- assiciated assignment
2489 -- p_save_to_hist VARCHAR2 NO If TRUE, then the change_approval_status proc.
2490 -- is called and the change is saved to the
2491 -- exceptions history. This is the case when
2492 -- the procedure is called from the UI.
2493 -- If FALSE, the the change_approval_status proc.
2494 -- is not called and the change is not saved to
2495 -- the exceptions history. FALSE should be used in
2496 -- all cases except when called from UI.
2497
2498 PROCEDURE change_asgn_schedule(
2499 p_record_version_number IN NUMBER,
2500 p_assignment_id IN NUMBER,
2501 p_project_id IN NUMBER,
2502 p_exception_id IN NUMBER,
2503 p_save_to_hist IN VARCHAR2 := FND_API.G_TRUE,
2504 p_remove_conflict_flag IN VARCHAR2 := 'N',
2505 p_generate_timeline_flag IN VARCHAR2 := 'Y', --Unilog
2506 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
2507 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
2508 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2509 )
2510 IS
2511 l_msg_index_out NUMBER;
2512 l_record_version_number NUMBER;
2513
2514 l_data VARCHAR2(2000); -- 4537865
2515 -- jmarques: 1776658: Local variables for storing new duration and
2516 -- old duration.
2517 l_new_start_date DATE := NULL;
2518 l_new_end_date DATE := NULL;
2519 l_old_start_date DATE := NULL;
2520 l_old_end_date DATE := NULL;
2521 l_resource_id NUMBER := NULL;
2522
2523 CURSOR csr_sch_excp IS
2524 SELECT calendar_id,
2525 schedule_exception_id,
2526 assignment_id,
2527 project_id,
2528 status_code,
2529 schedule_type_code,
2530 exception_type_code,
2531 resource_calendar_percent,
2532 non_working_day_flag,
2533 change_hours_type_code,
2534 change_calendar_type_code,
2535 -- change_calendar_name,
2536 change_calendar_id,
2537 duration_shift_type_code,
2538 duration_shift_unit_code,
2539 number_of_shift,
2540 start_date,
2541 end_date,
2542 Monday_hours,
2543 Tuesday_hours,
2544 Wednesday_hours,
2545 Thursday_hours,
2546 Friday_hours,
2547 saturday_hours,
2548 Sunday_hours
2549 FROM pa_schedule_exceptions
2550 WHERE assignment_id = p_assignment_id
2551 AND ((p_exception_id IS NULL) OR
2552 (schedule_exception_id = p_exception_id))
2553 ORDER BY schedule_exception_id;
2554
2555 l_tr_sch_rec_tab PA_SCHEDULE_GLOB.ScheduleTabTyp;
2556 l_sch_except_record_tab PA_SCHEDULE_GLOB.SchExceptTabTyp;
2557 l_sch_except_rec PA_SCHEDULE_GLOB.SchExceptRecord;
2558 l_chg_tr_sch_rec_tab PA_SCHEDULE_GLOB.ScheduleTabTyp;
2559 l_out_tr_sch_rec_tab PA_SCHEDULE_GLOB.ScheduleTabTyp;
2560 l_del_tr_sch_rec_tab PA_SCHEDULE_GLOB.ScheduleTabTyp;
2561 l_I NUMBER;
2562 l_p_start_id NUMBER;
2563 l_p_end_id NUMBER;
2564 l_apply_schedule_changes BOOLEAN;
2565 l_change_id PA_SCHEDULES_HISTORY.change_id%type;
2566 l_temp_status_code PA_PROJECT_ASSIGNMENTS.status_code%type;
2567
2568 l_save_to_hist VARCHAR2(1); -- Unilog
2569 l_record_version_number_wo_chg NUMBER; -- Unilog
2570
2571 BEGIN
2572 l_x_return_status := FND_API.G_RET_STS_SUCCESS ;
2573
2574 PA_SCHEDULE_UTILS.log_message(1,'Start of the change_asgn_schedule API ... ');
2575
2576 -- Unilog Begin
2577 l_save_to_hist := p_save_to_hist;
2578 IF p_generate_timeline_flag = 'N' THEN -- It means it is called from WeeklySchedule
2579 l_save_to_hist := FND_API.G_FALSE;
2580 END IF;
2581 -- Also now we should be using l_save_to_hist instead of p_save_to_hist
2582 -- Unilog End
2583
2584 -- Initializing local variables in case next procedure is not called.
2585 l_record_version_number := p_record_version_number;
2586
2587 -- Updates the assignment's approval status to WORKING which
2588 -- copies the history records into the assignment and schedule
2589 -- history tables.
2590 -- This if statement was added so that the approval status is not
2591 -- updated in all situations. For example, the approval status
2592 -- should not be updated when changing the status after failure
2593 -- or success. This is a work around and will be removed when the
2594 -- approval flow is redesigned.
2595 -- Bug 2135616: when this is called by PA_SCHEDULE_PVT.resolve_conflicts to remove
2596 -- conflicts, the approval status should not change.
2597 IF (FND_API.TO_BOOLEAN(NVL(l_save_to_hist,FND_API.G_TRUE))
2598 AND p_remove_conflict_flag = 'N') THEN
2599
2600 pa_assignment_approval_pvt.update_approval_status(
2601 p_assignment_id => p_assignment_id,
2602 p_action_code => PA_ASSIGNMENT_APPROVAL_PUB.g_update_action,
2603 p_record_version_number => p_record_version_number,
2604 x_record_version_number => l_record_version_number,
2605 x_change_id => l_change_id,
2606 x_apprvl_status_code => l_temp_status_code,
2607 x_return_status => l_x_return_status,
2608 x_msg_count => x_msg_count,
2609 x_msg_data => x_msg_data);
2610 ELSE
2611 l_record_version_number := p_record_version_number;
2612 l_change_id := PA_ASSIGNMENT_APPROVAL_PVT.Get_Change_Id(p_assignment_id);
2613 END IF;
2614
2615
2616 l_apply_schedule_changes := FALSE;
2617
2618 PA_SCHEDULE_UTILS.log_message(1, 'Before Calling the API get_assignment_schedule ....');
2619
2620 -- Calling the PVT API This api will bring the asignment schedule for the passed
2621 -- asignment id and it will store the schedule in tabel of record i.e l_tr_sch_rec_tab.
2622 PA_SCHEDULE_PVT.get_assignment_schedule(p_assignment_id,
2623 l_tr_sch_rec_tab,
2624 l_x_return_status,
2625 x_msg_count,
2626 x_msg_data );
2627
2628 PA_SCHEDULE_UTILS.log_message(1,'START ASSG SCHEDULE ',l_tr_sch_rec_tab );
2629 PA_SCHEDULE_UTILS.log_message(1, 'After Calling the API get_assignment_schedule ....');
2630
2631 l_I := 1;
2632
2633 -- Copying the exceptions of the given assignment
2634 FOR rec_sch_excp IN csr_sch_excp LOOP
2635 l_apply_schedule_changes := TRUE;
2636 l_sch_except_record_tab(l_i).calendar_id := rec_sch_excp.calendar_id;
2637 l_sch_except_record_tab(l_i).schedule_exception_id := rec_sch_excp.schedule_exception_id;
2638 l_sch_except_record_tab(l_i).assignment_id := rec_sch_excp.assignment_id;
2639 l_sch_except_record_tab(l_i).project_id := rec_sch_excp.project_id;
2640 l_sch_except_record_tab(l_i).assignment_status_code := rec_sch_excp.status_code;
2641 l_sch_except_record_tab(l_i).schedule_type_code := rec_sch_excp.schedule_type_code;
2642 l_sch_except_record_tab(l_i).exception_type_code := rec_sch_excp.exception_type_code;
2643 l_sch_except_record_tab(l_i).resource_calendar_percent := rec_sch_excp.resource_calendar_percent;
2644 l_sch_except_record_tab(l_i).non_working_day_flag := rec_sch_excp.non_working_day_flag;
2645 l_sch_except_record_tab(l_i).change_hours_type_code := rec_sch_excp.change_hours_type_code;
2646 l_sch_except_record_tab(l_i).change_calendar_type_code := rec_sch_excp.change_calendar_type_code;
2647 --l_sch_except_record_tab(l_i).change_calendar_name := rec_sch_excp.change_calendar_name;
2648 l_sch_except_record_tab(l_i).change_calendar_id := rec_sch_excp.change_calendar_id;
2649 l_sch_except_record_tab(l_i).duration_shift_type_code := rec_sch_excp.duration_shift_type_code;
2650 l_sch_except_record_tab(l_i).duration_shift_unit_code := rec_sch_excp.duration_shift_unit_code;
2651 l_sch_except_record_tab(l_i).number_of_shift := rec_sch_excp.number_of_shift;
2652 l_sch_except_record_tab(l_i).start_date := rec_sch_excp.start_date;
2653 l_sch_except_record_tab(l_i).end_date := rec_sch_excp.end_date;
2654 l_sch_except_record_tab(l_i).Monday_hours := rec_sch_excp.Monday_hours;
2655 l_sch_except_record_tab(l_i).Tuesday_hours := rec_sch_excp.Tuesday_hours;
2656 l_sch_except_record_tab(l_i).Wednesday_hours := rec_sch_excp.Wednesday_hours;
2657 l_sch_except_record_tab(l_i).Thursday_hours := rec_sch_excp.Thursday_hours;
2658 l_sch_except_record_tab(l_i).Friday_hours := rec_sch_excp.Friday_hours;
2659 l_sch_except_record_tab(l_i).saturday_hours := rec_sch_excp.saturday_hours;
2660 l_sch_except_record_tab(l_i).Sunday_hours := rec_sch_excp.sunday_hours;
2661 l_sch_except_rec.assignment_id := rec_sch_excp.assignment_id;
2662 l_sch_except_rec.calendar_id := rec_sch_excp.calendar_id;
2663 l_sch_except_rec.schedule_exception_id := rec_sch_excp.schedule_exception_id;
2664 l_sch_except_rec.project_id := rec_sch_excp.project_id;
2665 l_sch_except_rec.assignment_status_code := rec_sch_excp.status_code;
2666 l_sch_except_rec.schedule_type_code := rec_sch_excp.schedule_type_code;
2667 l_sch_except_rec.exception_type_code := rec_sch_excp.exception_type_code;
2668 l_sch_except_rec.resource_calendar_percent := rec_sch_excp.resource_calendar_percent;
2669 l_sch_except_rec.non_working_day_flag := rec_sch_excp.non_working_day_flag;
2670 l_sch_except_rec.change_hours_type_code := rec_sch_excp.change_hours_type_code;
2671 l_sch_except_rec.change_calendar_type_code := rec_sch_excp.change_calendar_type_code;
2672 --l_sch_except_rec.change_calendar_name := rec_sch_excp.change_calendar_name;
2673 l_sch_except_rec.change_calendar_id := rec_sch_excp.change_calendar_id;
2674 l_sch_except_rec.duration_shift_type_code := rec_sch_excp.duration_shift_type_code;
2675 l_sch_except_rec.duration_shift_unit_code := rec_sch_excp.duration_shift_unit_code;
2676 l_sch_except_rec.number_of_shift := rec_sch_excp.number_of_shift;
2677 l_sch_except_rec.start_date := rec_sch_excp.start_date;
2678 l_sch_except_rec.end_date := rec_sch_excp.end_date;
2679 l_sch_except_rec.Monday_hours := rec_sch_excp.Monday_hours;
2680 l_sch_except_rec.Tuesday_hours := rec_sch_excp.Tuesday_hours;
2681 l_sch_except_rec.Wednesday_hours := rec_sch_excp.Wednesday_hours;
2682 l_sch_except_rec.Thursday_hours := rec_sch_excp.Thursday_hours;
2683 l_sch_except_rec.Friday_hours := rec_sch_excp.Friday_hours;
2684 l_sch_except_rec.saturday_hours := rec_sch_excp.saturday_hours;
2685 l_sch_except_rec.Sunday_hours := rec_sch_excp.sunday_hours;
2686
2687
2688 IF (l_I > 1) THEN
2689 PA_SCHEDULE_UTILS.log_message(1,'Index Value :' || to_char(l_i));
2690 l_tr_sch_rec_tab.delete;
2691
2692
2693 IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
2694 -- This procedure will copy the schedule record from one table of record to another
2695 PA_SCHEDULE_UTILS.copy_schedule_rec_tab(
2696 l_chg_tr_sch_rec_tab,
2697 l_chg_tr_sch_rec_tab.first,
2698 l_chg_tr_sch_rec_tab.last,
2699 l_tr_sch_rec_tab,
2700 l_x_return_status,
2701 x_msg_count,
2702 x_msg_data
2703 );
2704 END IF;
2705
2706 PA_SCHEDULE_UTILS.log_message(1,'after copy schedule : ',l_tr_sch_rec_tab );
2707 END IF;
2708
2709 IF (rec_sch_excp.exception_type_code = 'CHANGE_DURATION' OR
2710 rec_sch_excp.exception_type_code = 'SHIFT_DURATION') then
2711 IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
2712 -- calling PVT API that will change the duration and generate a new schedule
2713 -- on the basis of passed schedule and exception record
2714 PA_SCHEDULE_PVT.apply_change_duration(l_tr_sch_rec_tab,
2715 l_sch_except_rec,
2716 l_out_tr_sch_rec_tab,
2717 l_x_return_status,
2718 x_msg_count,
2719 x_msg_data);
2720
2721 -- jmarques: 1776658: Store new duration.
2722 l_new_start_date := l_sch_except_rec.start_date;
2723 l_new_end_date := l_sch_except_rec.end_date;
2724 END IF;
2725
2726 PA_SCHEDULE_UTILS.log_message(1,'after change_duration : ',l_out_tr_sch_rec_tab );
2727 ELSE
2728
2729 PA_SCHEDULE_UTILS.log_message(1,'after change_duration : ' );
2730
2731 IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
2732 -- calling PVT API that will apply the changes
2733 PA_SCHEDULE_UTILS.log_message (1,' IN THE ELSE PART AND starting of apply other changes ');
2734 PA_SCHEDULE_UTILS.log_message(1, 'Calling apply_other_changes1 :', l_tr_sch_rec_tab);
2735 --PA_SCHEDULE_UTILS.log_message(1, 'Calling apply_other_changes2 :', l_sch_except_rec);
2736 PA_SCHEDULE_PVT.apply_other_changes(l_tr_sch_rec_tab,
2737 l_sch_except_rec,
2738 l_out_tr_sch_rec_tab,
2739 l_x_return_status,
2740 x_msg_count,
2741 x_msg_data
2742 );
2743 END IF;
2744
2745 PA_SCHEDULE_UTILS.log_message(1,'after change_others : ',l_out_tr_sch_rec_tab );
2746 END IF;
2747
2748 IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
2749 -- seperating the deleted or non deleted record i.e inserted or updated
2750 PA_SCHEDULE_UTILS.sep_del_sch_rec_tab(l_out_tr_sch_rec_tab,
2751 l_del_tr_sch_rec_tab,
2752 l_chg_tr_sch_rec_tab,
2753 l_x_return_status,
2754 x_msg_count,
2755 x_msg_data
2756 );
2757 END IF;
2758
2759 l_I := l_I + 1;
2760
2761 PA_SCHEDULE_UTILS.log_message(1,'after delete seperate (change ) : ',l_chg_tr_sch_rec_tab );
2762 PA_SCHEDULE_UTILS.log_message(1,'after delete seperate (delete ) : ',l_del_tr_sch_rec_tab );
2763
2764 END LOOP;
2765
2766 PA_SCHEDULE_UTILS.log_message(1,'FINAL (change ) : ',l_chg_tr_sch_rec_tab );
2767 PA_SCHEDULE_UTILS.log_message(1,'FINAL (delete ) : ',l_del_tr_sch_rec_tab );
2768
2769 IF ( l_apply_schedule_changes ) THEN
2770
2771 PA_SCHEDULE_UTILS.log_message(1, 'Before Calling the API apply_schedule_change ....');
2772
2773 IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
2774 -- Calling the PVT api that will change the schedule
2775 PA_SCHEDULE_PVT.apply_schedule_change(l_chg_tr_sch_rec_tab,
2776 l_del_tr_sch_rec_tab,
2777 l_x_return_status,
2778 x_msg_count,
2779 x_msg_data);
2780 END IF;
2781
2782 PA_SCHEDULE_UTILS.log_message(1, 'After Calling the API apply_schedule_change ....');
2783
2784 IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
2785 -- Calling the API PA_SCHEDULE_PVT.apply_assignment_change
2786
2787 -- jmarques: 1776658: Get old duration.
2788 select start_date, end_date, resource_id, record_version_number -- Unilog Selected record_version_number too
2789 into l_old_start_date, l_old_end_date, l_resource_id, l_record_version_number_wo_chg
2790 from pa_project_assignments
2791 where assignment_id = p_assignment_id;
2792
2793 -- Unilog Added this IF condition
2794 IF p_generate_timeline_flag = 'N' THEN
2795 l_record_version_number := l_record_version_number_wo_chg;
2796 END IF;
2797
2798 -- jmarques: 1776658: Fix variables so that no null values are present.
2799 l_new_start_date := NVL(l_new_start_date, l_old_start_date);
2800 l_new_end_date := NVL(l_new_end_date, l_old_end_date);
2801
2802 PA_SCHEDULE_PVT.apply_assignment_change(
2803 p_record_version_number => l_record_version_number,
2804 chg_tr_sch_rec_tab => l_chg_tr_sch_rec_tab,
2805 sch_except_record_tab => l_sch_except_record_tab,
2806 x_return_status => l_x_return_status,
2807 x_msg_count => x_msg_count,
2808 x_msg_data => x_msg_data);
2809 END IF;
2810
2811 PA_SCHEDULE_UTILS.log_message(1, 'Before Calling the API delete_rows ....');
2812
2813 IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
2814 -- Deleting the rows from pa_schedule_exception table
2815 PA_SCH_EXCEPT_PKG.delete_rows(l_sch_except_record_tab,
2816 l_x_return_status,
2817 x_msg_count,
2818 x_msg_data
2819 );
2820 END IF;
2821
2822 PA_SCHEDULE_UTILS.log_message(1, 'After Calling the API delete_rows ..');
2823
2824 -- inserting the rows from schedule except history table
2825 IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
2826 IF (FND_API.TO_BOOLEAN(NVL(l_save_to_hist,FND_API.G_TRUE))) THEN
2827 PA_SCH_EXCEPT_HIST_PKG.insert_rows(
2828 l_sch_except_record_tab,
2829 l_change_id,
2830 l_x_return_status,
2831 x_msg_count,
2832 x_msg_data
2833 );
2834 END IF;
2835 END IF;
2836
2837 PA_SCHEDULE_UTILS.log_message(1, 'After Calling the API insert_rows ....');
2838 END IF;
2839
2840 IF p_generate_timeline_flag = 'Y' THEN --Unilog
2841
2842 -- Calling the Timeline api to create the timeline records
2843 -- for the assignment
2844 IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
2845 PA_SCHEDULE_UTILS.log_message(1,'Calling Timeline API ..... ');
2846 PA_SCHEDULE_UTILS.log_message(1, 'Num: ' || FND_MSG_PUB.Count_Msg);
2847 PA_TIMELINE_PVT.create_timeline (
2848 p_assignment_id =>p_assignment_id ,
2849 x_return_status =>l_x_return_status ,
2850 x_msg_count =>x_msg_count ,
2851 x_msg_data =>x_msg_data );
2852
2853
2854 -- jmarques: 1776658: If the duration has changed, then create the
2855 -- resource timeline (recalculates availability) for the parts of
2856 -- the old duration which do not overlap the new duration.
2857
2858 IF (l_old_start_date < l_new_start_date AND l_resource_id is not null) THEN
2859 IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
2860 pa_timeline_pvt.Create_Timeline (
2861 p_start_resource_name => NULL,
2862 p_end_resource_name => NULL,
2863 p_resource_id => l_resource_id,
2864 p_start_date => l_old_start_date,
2865 p_end_date => l_new_start_date,
2866 x_return_status => l_x_return_status,
2867 x_msg_count => x_msg_count,
2868 x_msg_data => x_msg_data);
2869 END IF;
2870 END IF;
2871
2872 IF (l_old_end_date > l_new_end_date AND l_resource_id is not null) THEN
2873 IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
2874 pa_timeline_pvt.Create_Timeline (
2875 p_start_resource_name => NULL,
2876 p_end_resource_name => NULL,
2877 p_resource_id => l_resource_id,
2878 p_start_date => l_new_end_date,
2879 p_end_date => l_old_end_date,
2880 x_return_status => l_x_return_status,
2881 x_msg_count => x_msg_count,
2882 x_msg_data => x_msg_data);
2883 END IF;
2884 END IF;
2885 END IF;
2886 END IF; -- Unilog
2887
2888 PA_SCHEDULE_UTILS.log_message(1,'End of the change_asgn_schedule API ... ');
2889 x_return_status := l_x_return_status;
2890
2891 x_msg_count := FND_MSG_PUB.Count_Msg;
2892
2893 If x_msg_count = 1 THEN
2894 pa_interface_utils_pub.get_messages
2895 (p_encoded => FND_API.G_TRUE ,
2896 p_msg_index => 1,
2897 p_msg_count => x_msg_count ,
2898 p_msg_data => x_msg_data ,
2899 p_data => l_data, -- 4537865
2900 p_msg_index_out => l_msg_index_out );
2901 x_msg_data := l_data ; -- 4537865
2902 End If;
2903
2904 EXCEPTION
2905 WHEN OTHERS THEN
2906 PA_SCHEDULE_UTILS.log_message(1,'ERROR in change_asgn_schedule API ..'
2907 ||sqlerrm);
2908 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2909 x_msg_count := 1;
2910 x_msg_data := substrb(SQLERRM,1,240); -- 4537865 : Changed substr to substrb
2911 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_SCHEDULE_PUB',
2912 p_procedure_name => 'change_asgn_schedule');
2913 If x_msg_count = 1 THEN
2914 pa_interface_utils_pub.get_messages
2915 (p_encoded => FND_API.G_TRUE ,
2916 p_msg_index => 1,
2917 p_msg_count => x_msg_count ,
2918 p_msg_data => x_msg_data ,
2919 p_data => l_data, -- 4537865
2920 p_msg_index_out => l_msg_index_out );
2921 x_msg_data := l_data ; -- 4537865
2922 End If;
2923 RAISE; -- This is optional depending on the needs
2924 END change_asgn_schedule;
2925
2926 -- Procedure : create_calendar_schedule
2927 -- Purpose : This procedure is called from periodic process for creating calendar schedule
2928 -- . It will generate the new schedule on the basi of passed calendar id
2929 -- Input parameters
2930 -- Parameters Type Required Description
2931 -- P_Calendar_Id NUMBER YES Id for that calendar for which you want to create schedule
2932 --
2933
2934 PROCEDURE create_calendar_schedule ( p_calendar_id IN NUMBER,
2935 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
2936 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
2937 x_msg_data OUT NOCOPY VARCHAR2 ) --File.Sql.39 bug 4440895
2938 IS
2939
2940 l_x_cal_record_tab PA_SCHEDULE_GLOB.CalendarTabTyp;
2941 l_schedule_rec_tab PA_SCHEDULE_GLOB.ScheduleTabTyp;
2942 l_x_sch_record_tab PA_SCHEDULE_GLOB.ScheduleTabTyp;
2943 l_x_cal_except_record_tab PA_SCHEDULE_GLOB.calExceptionTabTyp;
2944 l_msg_index_out NUMBER;
2945 l_data varchar2(2000) ;-- 4537865
2946 BEGIN
2947 PA_SCHEDULE_UTILS.log_message(1,'Start of the create_calendar_schedule API ... ');
2948 -- storing the status success to track the error
2949 l_x_return_status := FND_API.G_RET_STS_SUCCESS ;
2950
2951 -- Calling the Calendar API that will give the shift assign to the calendar*/
2952 PA_CALENDAR_UTILS.get_calendar_shifts(p_calendar_id,l_x_cal_record_tab,l_x_return_status,x_msg_count,x_msg_data);
2953
2954 IF ( l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
2955 PA_CALENDAR_UTILS.gen_calendar_sch(p_calendar_id,l_x_cal_record_tab,l_schedule_rec_tab,l_x_return_status,
2956 x_msg_count,x_msg_data);
2957
2958 END IF;
2959
2960 PA_SCHEDULE_UTILS.log_message(1,'GEN SCH ',l_schedule_rec_tab);
2961 IF ( l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
2962 -- Calling the PA_CALENDAR_UTILS API that will take the exception associated with the calendar
2963 PA_CALENDAR_UTILS.get_calendar_except(p_calendar_id,l_x_cal_except_record_tab,l_x_return_status,
2964 x_msg_count,x_msg_data);
2965
2966 END IF;
2967
2968 IF ( l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
2969
2970 -- Calling the PA_CALENDAR_UTILS API that will generate the calendar schedule after applying the exception
2971 PA_CALENDAR_UTILS.apply_calendar_except(p_calendar_id,l_x_cal_except_record_tab,l_schedule_rec_tab,
2972 l_x_sch_record_tab,l_x_return_status,x_msg_count,x_msg_data);
2973
2974 END IF;
2975
2976 IF ( l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
2977 -- Inserting the records in PA_SCHEDULES table
2978 PA_SCHEDULE_PKG.insert_rows(l_x_sch_record_tab,l_x_return_status,x_msg_count,x_msg_data);
2979
2980 END IF;
2981 /** Added call to update_wp_calendar for all projects **/
2982 -- Start Of Bug No :4666318
2983 --Commented for Bug No:4666318
2984 -- This functionality was originally introduced in FP.K .But It was removed in FP.M.
2985 /* PA_PROJECT_STRUCTURE_PVT1.update_all_wp_calendar
2986 ( p_calendar_id => p_calendar_id
2987 ,x_return_status => x_return_status
2988 ,x_msg_count => x_msg_count
2989 ,x_msg_data => x_msg_data
2990 );*/
2991 -- End Of Bug No : 4666318
2992
2993 PA_SCHEDULE_UTILS.log_message(1,'End of the create_calendar_schedule API ... ');
2994
2995 x_return_status := l_x_return_status;
2996 x_msg_count := FND_MSG_PUB.Count_Msg;
2997 If x_msg_count = 1 THEN
2998 pa_interface_utils_pub.get_messages
2999 (p_encoded => FND_API.G_TRUE ,
3000 p_msg_index => 1,
3001 p_msg_count => x_msg_count ,
3002 p_msg_data => x_msg_data ,
3003 p_data => l_data, -- 4537865
3004 p_msg_index_out => l_msg_index_out );
3005 x_msg_Data := l_data ;-- 4537865
3006 End If;
3007 EXCEPTION
3008 WHEN OTHERS THEN
3009 PA_SCHEDULE_UTILS.log_message(1,'ERROR in create_calendar_schedule API ..'||sqlerrm);
3010 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3011 x_msg_count := 1;
3012 x_msg_data := substrb(SQLERRM,1,240); -- 4537865 changed substr to substrb
3013 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_SCHEDULE_PUB',
3014 p_procedure_name => 'create_calendar_schedule');
3015 If x_msg_count = 1 THEN
3016 pa_interface_utils_pub.get_messages
3017 (p_encoded => FND_API.G_TRUE,
3018 p_msg_index => 1,
3019 p_msg_count => x_msg_count,
3020 p_msg_data => x_msg_data,
3021 p_data => l_data, -- 4537865
3022 p_msg_index_out => l_msg_index_out );
3023 x_msg_Data := l_data ;-- 4537865
3024 End If;
3025 RAISE; -- This is optional depending on the needs
3026 END create_calendar_schedule;
3027
3028 -- Procedure : get_proj_calendar_default
3029 -- Purpose : This procedure is called for getting the default calendar for the project.
3030 -- This will be called from projects form
3031 --
3032 -- Input parameters
3033 -- Parameters Type Required Description
3034 -- P_Proj_Organization NUMBER YES project organization
3035 -- P_Project_Id NUMBER YES project id
3036 -- Out parameters
3037 -- X_Calendar_Id NUMBER YES It stores the id for the calendar
3038 -- X_Calendar_Name VARCHAR2 YES It stores name of the calendar
3039 --
3040
3041 PROCEDURE get_proj_calendar_default ( p_proj_organization IN NUMBER,
3042 p_project_id IN NUMBER,
3043 x_calendar_id OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
3044 x_calendar_name OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
3045 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
3046 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
3047 x_msg_data OUT NOCOPY VARCHAR2 ) IS --File.Sql.39 bug 4440895
3048 l_no_calendar_at_org BOOLEAN;
3049 l_temp_calendar_id VARCHAR2(80);
3050 l_null_default_calendar EXCEPTION;
3051 l_invalid_default_calendar EXCEPTION;
3052 l_t_carrying_out_org_id PA_PROJECTS.carrying_out_organization_id%TYPE; -- To store the orgnaization id
3053 L_MSG_INDEX_OUT NUMBER;
3054
3055 l_data varchar2(2000); -- 4537865
3056 BEGIN
3057 PA_SCHEDULE_UTILS.log_message(1,'Start of the get_proj_calendar_default API ... ');
3058
3059 l_x_return_status := FND_API.G_RET_STS_SUCCESS ;
3060 l_no_calendar_at_org := FALSE;
3061
3062 /* Bug2873984
3063 IF (PA_INSTALL.IS_PRM_LICENSED() <> 'Y') THEN
3064 PA_SCHEDULE_UTILS.log_message(1,'PRM is not licensed, so returning null.');
3065 x_calendar_id := null;
3066 x_calendar_name := null;
3067 x_return_status := l_x_return_status;
3068 return;
3069 END IF; */
3070
3071 PA_SCHEDULE_UTILS.log_message(1,'PRM is licensed, so continuing.');
3072
3073 -- Taking out the orgnization id for passing to the get_proj_calendar_default */
3074 IF (p_project_id IS NOT NULL ) THEN
3075 BEGIN
3076 -- Modified to select from PA_PROJECTS_ALL instead of PA_PROJECTS.
3077 SELECT carrying_out_organization_id
3078 INTO l_t_carrying_out_org_id
3079 FROM PA_PROJECTS_ALL
3080 WHERE project_id = p_project_id;
3081
3082 EXCEPTION
3083 WHEN NO_DATA_FOUND THEN
3084 x_return_status := FND_API.G_RET_STS_ERROR;
3085 RAISE;
3086 END;
3087 ELSE
3088 l_t_carrying_out_org_id := p_proj_organization;
3089 END IF;
3090
3091 BEGIN
3092 PA_SCHEDULE_UTILS.log_message(1,'before select on hr_org.... ');
3093
3094 -- Taking the calendar on the basis of organization assigned to the project
3095 -- R12 changes - the calendar is now stored
3096 -- under a new information type - Resource Defaults
3097 -- in a different column
3098
3099 --SELECT TO_NUMBER(hr1.org_information2) ,
3100 SELECT TO_NUMBER(hr1.org_information1) ,
3101 cal1.calendar_name
3102 INTO x_calendar_id ,
3103 x_calendar_name
3104 FROM hr_organization_information hr1,
3105 jtf_calendars_vl cal1
3106 --WHERE cal1.calendar_id = TO_NUMBER(hr1.org_information2) BUG 3530529
3107 -- WHERE TO_CHAR(cal1.calendar_id) = hr1.org_information2
3108 WHERE TO_CHAR(cal1.calendar_id) = hr1.org_information1
3109 AND hr1.organization_id = l_t_carrying_out_org_id
3110 --AND hr1.org_information_context = 'Exp Organization Defaults';
3111 AND hr1.org_information_context = 'Resource Defaults';
3112
3113 PA_SCHEDULE_UTILS.log_message(1,'after select on hr_org.... ');
3114 EXCEPTION
3115 WHEN NO_DATA_FOUND THEN
3116 PA_SCHEDULE_UTILS.log_message(1,'inside no data found for select on hr_org.... ');
3117 l_no_calendar_at_org := TRUE;
3118 WHEN OTHERS THEN
3119 PA_SCHEDULE_UTILS.log_message(1,'ERROR while excuting select on hr_org.... '||sqlerrm);
3120 RAISE;
3121 END;
3122
3123 -- If no calendar is associated with the organization the we will take the calendar which will be
3124 -- assiciated with the PROFILE
3125 IF ( l_no_calendar_at_org ) THEN
3126 FND_PROFILE.GET('PA_PRM_DEFAULT_CALENDAR',l_temp_calendar_id);
3127
3128 IF ( l_temp_calendar_id is NULL ) THEN
3129 /* Commented for bug 2873984
3130 x_msg_data := 'Default Calendar not assigned to profile option PA_PRM_CALENDAR';
3131 RAISE l_null_default_calendar; */
3132 /* Code Addition for bug 2873984 starts */
3133 x_calendar_id := null;
3134 x_calendar_name := null;
3135 /* Code Addition for bug 2873984 ends */
3136 ELSE
3137 PA_SCHEDULE_UTILS.log_message(1,'calendar_id '||l_temp_calendar_id);
3138 BEGIN
3139 SELECT
3140 calendar_name
3141 INTO x_calendar_name
3142 FROM jtf_calendars_vl
3143 WHERE calendar_id = TO_NUMBER(l_temp_calendar_id);
3144
3145 x_calendar_id := TO_NUMBER(l_temp_calendar_id);
3146 EXCEPTION
3147 WHEN NO_DATA_FOUND THEN
3148 x_msg_data := 'Not a valid calendar assigned to profile option ';
3149 PA_SCHEDULE_UTILS.log_message(1,'Not a valid calendar assigned to profile option ');
3150 RAISE l_invalid_default_calendar;
3151 WHEN OTHERS THEN
3152 PA_SCHEDULE_UTILS.log_message(1,'ERROR while excuting select on jtf_calendars_vl.... '||sqlerrm);
3153 RAISE;
3154 END;
3155
3156 END IF;
3157 END IF;
3158
3159 PA_SCHEDULE_UTILS.log_message(1,'End of the get_proj_calendar_default API ... ');
3160 x_return_status := l_x_return_status;
3161
3162 EXCEPTION
3163 WHEN l_invalid_default_calendar THEN
3164 PA_SCHEDULE_UTILS.log_message(1,'ERROR: invalid calendar id at profile option ..');
3165 PA_UTILS.add_message('PA','PA_INVALID_PROFILE_CALENDAR_ID');
3166 x_return_status := FND_API.G_RET_STS_ERROR;
3167 x_msg_data := 'PA_INVALID_PROFILE_CALENDAR_ID';
3168 x_msg_count := FND_MSG_PUB.Count_Msg;
3169
3170 -- RESET other out params also : 4537865
3171 x_calendar_id := NULL ;
3172 x_calendar_name := NULL;
3173
3174 If x_msg_count = 1 THEN
3175 pa_interface_utils_pub.get_messages
3176 (p_encoded => FND_API.G_TRUE,
3177 p_msg_index => 1,
3178 p_msg_count => x_msg_count,
3179 p_msg_data => x_msg_data,
3180 p_data => l_data, -- 4537865
3181 p_msg_index_out => l_msg_index_out );
3182 x_msg_data := l_data ; -- 4537865
3183 End If;
3184 WHEN l_null_default_calendar THEN
3185 PA_SCHEDULE_UTILS.log_message(1,'ERROR: Null calendar id at profile option ..');
3186 PA_UTILS.add_message('PA','PA_INVALID_PROFILE_CALENDAR_ID');
3187 x_return_status := FND_API.G_RET_STS_ERROR;
3188 x_msg_data := 'PA_NULL_PROFILE_CALENDAR_ID';
3189 x_msg_count := FND_MSG_PUB.Count_Msg;
3190
3191 -- RESET other out params also : 4537865
3192 x_calendar_id := NULL ;
3193 x_calendar_name := NULL;
3194
3195 If x_msg_count = 1 THEN
3196 pa_interface_utils_pub.get_messages
3197 (p_encoded => FND_API.G_TRUE,
3198 p_msg_index => 1,
3199 p_msg_count => x_msg_count,
3200 p_msg_data => x_msg_data,
3201 p_data => l_data, -- 4537865
3202 p_msg_index_out => l_msg_index_out );
3203 x_msg_data := l_data ; -- 4537865
3204 End If;
3205 WHEN OTHERS THEN
3206 PA_SCHEDULE_UTILS.log_message(1,'ERROR in get_proj_calendar_default API ..'||sqlerrm);
3207 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3208 x_msg_count := 1;
3209 x_msg_data := substrb(SQLERRM,1,240); -- 4537865 : CHANGd substr to substrb
3210
3211 -- RESET other out params also : 4537865
3212 x_calendar_id := NULL ;
3213 x_calendar_name := NULL;
3214
3215 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_SCHEDULE_PUB',
3216 p_procedure_name => 'get_proj_calendar_default');
3217 If x_msg_count = 1 THEN
3218 pa_interface_utils_pub.get_messages
3219 (p_encoded => FND_API.G_TRUE,
3220 p_msg_index => 1,
3221 p_msg_count => x_msg_count,
3222 p_msg_data => x_msg_data,
3223 p_data => l_data, -- 4537865
3224 p_msg_index_out => l_msg_index_out );
3225 x_msg_data := l_data ; -- 4537865
3226 End If;
3227 RAISE; -- This is optional depending on the needs
3228 END get_proj_calendar_default;
3229
3230 -- Procedure : create_new_cal_schedules
3231 -- Purpose : This procedure is called for creating the new schedule for the given calendars
3232 -- .
3233 -- Input parameters
3234 -- Parameters Type Required Description
3235 -- P_Start_Calendar_Name VARCHAR2 NO Name of the starting calendar
3236 -- P_End_Calendar_Name VARCHAR2 NO Name of the Ending calendar
3237 --
3238
3239 PROCEDURE create_new_cal_schedules ( p_start_calendar_name IN VARCHAR2,
3240 p_end_calendar_name IN VARCHAR2,
3241 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
3242 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
3243 x_msg_data OUT NOCOPY VARCHAR2 ) --File.Sql.39 bug 4440895
3244 IS
3245 l_I NUMBER;
3246 l_code VARCHAR2(50); -- temp variable
3247 l_flag VARCHAR2(1); -- temp variable
3248 l_t_start_calendar_name VARCHAR2(50); -- temp variable to store the calendar start name for creating
3249 -- the new schedule
3250 l_t_end_calendar_name VARCHAR2(50); -- temp variable to store the calendar end name for creating
3251 -- the new schedule
3252
3253 -- This cursor will select only those records which are matching in the start and end calendar name
3254 -- or coming between them
3255 CURSOR C1 IS SELECT calendar_id,calendar_name
3256 FROM JTF_CALENDARS_VL
3257 WHERE calendar_name BETWEEN l_t_start_calendar_name AND l_t_end_calendar_name;
3258 l_msg_index_out NUMBER;
3259 l_exception EXCEPTION;
3260 l_debug_mode VARCHAR2(20) := 'N';
3261 l_counter NUMBER;
3262
3263 l_data varchar2(2000) ; -- 4537865
3264 BEGIN
3265 -- Storing the status for error handling
3266 l_x_return_status := FND_API.G_RET_STS_SUCCESS;
3267
3268 -- 2843435
3269 fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
3270
3271 -- 4370082
3272 IF l_debug_mode = 'Y' THEN
3273 pa_debug.set_process('PLSQL','LOG',l_debug_mode);
3274 END IF;
3275
3276 -- End of 2843435
3277
3278 -- The passing calendar start name is null then take the lowest one
3279 IF (p_start_calendar_name IS NULL ) THEN
3280
3281 SELECT MIN(calendar_name)
3282 INTO l_t_start_calendar_name
3283 FROM JTF_CALENDARS_VL;
3284 ELSE
3285 l_t_start_calendar_name := p_start_calendar_name;
3286 END IF;
3287
3288 -- The passing calendar start name is null then take the hightest one
3289 IF (p_end_calendar_name IS NULL ) THEN
3290
3291 SELECT MAX(calendar_name)
3292 INTO l_t_end_calendar_name
3293 FROM JTF_CALENDARS_VL;
3294 ELSE
3295 l_t_end_calendar_name := p_end_calendar_name;
3296 END IF;
3297
3298 FOR v_c1 IN C1 LOOP
3299 -- Defining save point
3300 SAVEPOINT bfr_strt_del;
3301
3302 --Locking the JTF_CALENDAR_B table becouse of updation of its record
3303 SELECT 1
3304 INTO l_I
3305 FROM JTF_CALENDARS_B
3306 WHERE calendar_id=v_c1.calendar_id
3307 FOR UPDATE ;
3308
3309 -- Deleting the existing schedule of the calendar and creating the new one
3310 DELETE FROM PA_SCHEDULES
3311 WHERE calendar_id = v_c1.calendar_id
3312 AND schedule_type_code = 'CALENDAR';
3313
3314 -- Creating new schedule for the Calendar Id */
3315 PA_SCHEDULE_PUB.create_calendar_schedule(v_c1.calendar_id,l_x_return_status,x_msg_count,x_msg_data);
3316
3317 PA_SCHEDULE_UTILS.log_message(1,'Calling Timeline API ..... ');
3318 IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
3319 PA_TIMELINE_PVT.Create_Timeline (p_calendar_id =>v_c1.calendar_id,
3320 x_return_status =>l_x_return_status,
3321 x_msg_count =>x_msg_count,
3322 x_msg_data =>x_msg_data);
3323 END IF;
3324
3325 IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
3326 l_flag := 'S';
3327 l_code := 'PA_SCH_SUCC_CAL_GEN';
3328 COMMIT;
3329 ELSE
3330 l_flag := 'E';
3331 l_code := 'PA_SCH_FAIL_CAL_GEN';
3332 ROLLBACK TO SAVEPOINT bfr_strt_del;
3333 END IF;
3334 -- Inserting the calendars in session level temp table to pupulate the report
3335 INSERT INTO PA_CAL_GEN_STATUS_TEMP(calendar_id,calendar_name,generate_status_flag,message_code)
3336 VALUES(v_c1.calendar_id,v_c1.calendar_name,l_flag,l_code);
3337 COMMIT;
3338
3339 -- 2843435: Need to raise expected error messages and print them to
3340 -- FND_FILE. Currently we don't have the logic to print error messages
3341 -- to the report file. Therefore, we have to raise expected errors.
3342 IF l_x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3343
3344 IF l_debug_mode = 'Y' THEN
3345 pa_debug.write_file('CREATE_NEW_CAL_SCHEDULES: '||'LOG', 'msg_count = '||FND_MSG_PUB.Count_Msg);
3346 END IF;
3347
3348 FOR l_counter IN 1..FND_MSG_PUB.Count_Msg LOOP
3349 pa_interface_utils_pub.get_messages ( p_encoded => FND_API.G_FALSE
3350 ,p_msg_index => l_counter
3351 ,p_data => x_msg_data
3352 ,p_msg_index_out => l_msg_index_out);
3353 IF l_debug_mode = 'Y' THEN
3354 pa_debug.write_file('CREATE_NEW_CAL_SCHEDULES: '||'LOG', x_msg_data);
3355 END IF;
3356 END LOOP;
3357
3358 RAISE l_exception;
3359
3360 END IF;
3361 -- End of 2843435
3362
3363 END LOOP;
3364
3365 x_return_status := FND_API.G_RET_STS_SUCCESS;
3366 x_msg_count := FND_MSG_PUB.Count_Msg;
3367 If x_msg_count = 1 THEN
3368 pa_interface_utils_pub.get_messages
3369 (p_encoded => FND_API.G_TRUE ,
3370 p_msg_index => 1,
3371 p_msg_count => x_msg_count ,
3372 p_msg_data => x_msg_data ,
3373 p_data => l_data, -- 4537865
3374 p_msg_index_out => l_msg_index_out );
3375 x_msg_data := l_data ; -- 4537865
3376 End If;
3377
3378 EXCEPTION
3379 -- 2843435
3380 WHEN l_exception THEN
3381 RAISE;
3382 -- End of 2843435
3383 WHEN NO_DATA_FOUND THEN
3384 NULL;
3385 WHEN OTHERS THEN
3386 PA_SCHEDULE_UTILS.log_message(1,'ERROR in create_new_cal_schedules API ..'|| sqlerrm);
3387 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3388 x_msg_count := 1;
3389 x_msg_data := substrb(SQLERRM,1,240); -- 4537865 : Changed substrb to substr
3390 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_SCHEDULE_PUB',
3391 p_procedure_name => 'create_new_cal_schedules');
3392 If x_msg_count = 1 THEN
3393 pa_interface_utils_pub.get_messages
3394 (p_encoded => FND_API.G_TRUE,
3395 p_msg_index => 1,
3396 p_msg_count => x_msg_count,
3397 p_msg_data => x_msg_data,
3398 p_data => l_data, -- 4537865
3399 p_msg_index_out => l_msg_index_out );
3400 x_msg_data := l_data ; -- 4537865
3401 End If;
3402 RAISE; -- This is optional depending on the needs
3403 END create_new_cal_schedules;
3404
3405 -- Unilog Enhancement : BEGIN
3406
3407 -- Procedure : change_work_pattern_duration
3408 -- Purpose : This procedure is called from self service for changing duration and work pattern.
3409 -- : It uses existing change_work_pattern and change_duration procedures to do the job.
3410 -- : While calling change_duartion and change_work_pattern, it passes newly introduced
3411 -- : parameter p_generate_timeline_flag as N, so that they do not call timeline API.
3412 -- : Typically this API will get called for a set of assignments of a resource
3413 -- : (in a loop or from VORowImpl). So it takes two parameters p_prev_call_timeline_st_date
3414 -- : and p_prev_call_timeline_end_date. For first assignment in the loop it will be null.
3415 -- : So x_call_timeline_st_date and x_call_timeline_end_date will have the required date ranges
3416 -- : for which timeline has to be regenerated. For the second assignment p_prev_call_timeline_st_date
3417 -- : and p_prev_call_timeline_end_date will have the first assighnmenmt's x_call_timeline_st_date
3418 -- : and x_call_timeline_end_date correspondingly. Then it will again calculate the timeline start date
3419 -- : and timeline end date for the second assignment. Then it will compare it with
3420 -- : p_prev_call_timeline_st_date and p_prev_call_timeline_end_date and will take
3421 -- : min(new timeline start date, p_prev_call_timeline_st_date) and
3422 -- : max(new timeline end date, p_prev_call_timeline_end_date). Similarly for other assignments....
3423 -- : After this API is called for a set of assignments, you need to call PA_FORECASTITEM_PVT.Create_Forecast_Item
3424 -- : with person_id as paremetrer and with the returned dates x_call_timeline_st_date
3425 -- : and x_call_timeline_end_date
3426 -- Parameters :
3427 -- Note : Note that the p_hours_table should have hours quantity starting at p_start_date and
3428 -- : ending at p_end_date.
3429
3430
3431 PROCEDURE change_work_pattern_duration(
3432 p_record_version_number IN Number ,
3433 p_project_id IN Number ,
3434 p_calendar_id IN Number ,
3435 p_assignment_id IN Number ,
3436 p_resource_id IN Number ,
3437 p_assignment_type IN Varchar2 ,
3438 p_asgn_start_date IN DATE := NULL ,
3439 p_asgn_end_date IN DATE := NULL ,
3440 p_start_date IN date := NULL ,
3441 -- p_end_date IN date := NULL ,
3442 p_assignment_status_code IN varchar2 := NULL ,
3443 p_hours_table IN SYSTEM.PA_NUM_TBL_TYPE ,
3444 p_prev_call_timeline_st_date IN DATE ,
3445 p_prev_call_timeline_end_date IN DATE ,
3446 x_call_timeline_st_date OUT NOCOPY Date , --File.Sql.39 bug 4440895
3447 x_call_timeline_end_date OUT NOCOPY Date , --File.Sql.39 bug 4440895
3448 x_return_status OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
3449 x_msg_count OUT NOCOPY NUMBER , --File.Sql.39 bug 4440895
3450 x_msg_data OUT NOCOPY VARCHAR2 ) --File.Sql.39 bug 4440895
3451 IS
3452
3453 l_hours_table PA_PLSQL_DATATYPES.IdTabTyp;
3454 l_hours_db_table PA_PLSQL_DATATYPES.IdTabTyp;
3455 l_monday_hours PA_PLSQL_DATATYPES.IdTabTyp;
3456 l_tuesday_hours PA_PLSQL_DATATYPES.IdTabTyp;
3457 l_wednesday_hours PA_PLSQL_DATATYPES.IdTabTyp;
3458 l_thursday_hours PA_PLSQL_DATATYPES.IdTabTyp;
3459 l_friday_hours PA_PLSQL_DATATYPES.IdTabTyp;
3460 l_saturday_hours PA_PLSQL_DATATYPES.IdTabTyp;
3461 l_sunday_hours PA_PLSQL_DATATYPES.IdTabTyp;
3462 l_sch_record_tab PA_SCHEDULE_GLOB.ScheduleTabTyp;
3463 l_date DATE;
3464 l_counter NUMBER := 1;
3465 l_changes_done BOOLEAN := false;
3466 l_call_change_work_pattern BOOLEAN := false;
3467 l_call_change_duration BOOLEAN := false;
3468 l_call_cng_work_patt_out_range BOOLEAN := false;
3469 l_call_second_time BOOLEAN :=false;
3470 l_call_first_time BOOLEAN :=false; --Added for the bug 3421637
3471 l_new_assgn_start_date DATE;
3472 l_new_assgn_end_date DATE;
3473 l_update_work_zero_start_date DATE;
3474 l_update_work_zero_end_date DATE;
3475 l_count NUMBER;
3476 l_global_week_start_day NUMBER; --Added for bug 4068167
3477 l_days_to_inc NUMBER; --Added for bug 4068167
3478 l_actual_days_to_inc NUMBER; --Added for bug 4068167
3479 l_week_day VARCHAR2(10);
3480 l_ch_work_pattern_st_date1 DATE;
3481 l_ch_work_pattern_end_date1 DATE;
3482 l_ch_work_pattern_st_date2 DATE;
3483 l_ch_work_pattern_end_date2 DATE;
3484 l_actual_start_date DATE;
3485 l_actual_end_date DATE;
3486 -- l_exception EXCEPTION;
3487 API_ERROR EXCEPTION;
3488 l_msg_index_out NUMBER;
3489 p_end_date DATE;
3490 l_last_row_flag VARCHAR2(1);
3491
3492 l_data varchar2(2000); -- 4537865
3493 BEGIN
3494
3495 FND_MSG_PUB.initialize;
3496 l_count := p_hours_table.COUNT;
3497 p_end_date := p_start_date+13;
3498
3499 l_global_week_start_day := fnd_profile.value_specific('PA_GLOBAL_WEEK_START_DAY'); --Added for bug 4068167
3500
3501 PA_SCHEDULE_UTILS.log_message(1,'Start of the change_work_pattern_duration API ... ');
3502 PA_SCHEDULE_UTILS.log_message(1,'Parameters ... ');
3503 PA_SCHEDULE_UTILS.log_message(1,'p_record_version_number='||p_record_version_number);
3504 PA_SCHEDULE_UTILS.log_message(1,'p_project_id='||p_project_id);
3505 PA_SCHEDULE_UTILS.log_message(1,'p_calendar_id='||p_calendar_id);
3506 PA_SCHEDULE_UTILS.log_message(1,'p_assignment_id='||p_assignment_id);
3507 PA_SCHEDULE_UTILS.log_message(1,'p_resource_id='||p_resource_id);
3508 PA_SCHEDULE_UTILS.log_message(1,'p_assignment_type='||p_assignment_type);
3509 PA_SCHEDULE_UTILS.log_message(1,'p_asgn_start_date='||p_asgn_start_date);
3510 PA_SCHEDULE_UTILS.log_message(1,'p_asgn_end_date='||p_asgn_end_date);
3511 PA_SCHEDULE_UTILS.log_message(1,'p_start_date='||p_start_date);
3512 PA_SCHEDULE_UTILS.log_message(1,'p_end_date='||p_end_date);
3513 PA_SCHEDULE_UTILS.log_message(1,'p_assignment_status_code='||p_assignment_status_code);
3514 PA_SCHEDULE_UTILS.log_message(1,'Number of records in p_hours_table='||l_count);
3515 PA_SCHEDULE_UTILS.log_message(1,'Week start day l_global_week_start_day='||l_global_week_start_day); --Added for bug 4068167
3516
3517 -- Initialize the out date variables, so that if changes are not required then no need to call
3518 -- create_forecast_items
3519
3520 x_call_timeline_st_date := null;
3521 x_call_timeline_end_date := null;
3522 x_return_status := FND_API.G_RET_STS_SUCCESS;
3523
3524 IF ((p_end_date - p_start_date <> 13) OR (p_end_date < p_start_date)) THEN
3525 PA_SCHEDULE_UTILS.log_message(1,'p_start_date and p_end_date is wrongly passed');
3526 raise API_ERROR;
3527 END IF;
3528
3529 IF l_count <> 14 THEN
3530 PA_SCHEDULE_UTILS.log_message(1,'Number of records in p_hours_table is not 14');
3531 raise API_ERROR;
3532 END IF;
3533 -- Bug 3235656 : Added the below condition to show error
3534 FOR i IN p_hours_table.FIRST .. p_hours_table.LAST LOOP
3535 IF (p_hours_table(i) < 0 OR p_hours_table(i) > 24) THEN
3536 PA_UTILS.Add_Message ('PA', 'PA_SCH_HOURS_OUT_OF_RANGE');
3537 RAISE FND_API.G_EXC_ERROR;
3538 END IF;
3539 END LOOP;
3540
3541 -- We want to calculate the actual start date and actual end date here instead of Java
3542 -- Because it is difficult and error prone to play with Dates in java. Java will pass
3543 -- alwyas the first shown date in work pattern table and last date as p_start_date and
3544 -- p_end_date. And a table of hours p_hours_table with 14 values filled in this. These
3545 -- values may be null, 0 or any value
3546
3547 PA_SCHEDULE_UTILS.log_message(1,'Parameters Detrmination Phase Begin');
3548
3549 -- Parameters Detrmination Phase Begin : In this it will determine the following parameters
3550
3551 -- l_actual_start_date : From which date actually changes starts in work pattern table
3552 -- l_actual_end_date : From which date actually changes ends in work pattern table
3553 -- l_new_assgn_start_date : New extended assignment start date. It will be original assignment start date if assignment is not extended
3554 -- l_new_assgn_start_date : New extended assignment end date. It will be original assignment end date if assignment is not extended
3555 -- l_changes_done : Some changes are done, but not very sure. So needs to determine further
3556 -- l_call_change_duration : Duration has been extended
3557 -- l_call_change_work_pattern : Work pattern has been changed
3558 -- l_call_cng_work_patt_out_range : There is a gap between assignment, so needs to fill this with 0 hours
3559 -- l_update_work_zero_start_date : Start date for the Gap created in the assignment
3560 -- l_update_work_zero_end_date : End date for the Gap created in the assignment
3561 -- x_call_timeline_st_date : The start date from which timeline should be regenerated.
3562 -- x_call_timeline_end_date : The end date till which timeline should be regenerated.
3563
3564 IF ((p_start_date BETWEEN p_asgn_start_date AND p_asgn_end_date) AND (p_end_date BETWEEN p_asgn_start_date AND p_asgn_end_date)) THEN
3565 -- Changes are Within Assignment Date Range
3566 PA_SCHEDULE_UTILS.log_message(1,'Changes are Within Assignment Date Range');
3567 l_actual_start_date := p_start_date;
3568 l_actual_end_date := p_end_date;
3569 l_new_assgn_start_date := p_asgn_start_date;
3570 l_new_assgn_end_date := p_asgn_end_date;
3571 x_call_timeline_st_date := l_actual_start_date;
3572 x_call_timeline_end_date := l_actual_end_date;
3573 --Changes are done, now further it needs to be determined that whether work pattern has changed or not
3574 l_changes_done := true;
3575 FOR i IN p_hours_table.FIRST .. p_hours_table.LAST LOOP
3576 IF p_hours_table(i) IS NULL THEN
3577 l_hours_table(i) := 0;
3578 ELSE
3579 l_hours_table(i) := p_hours_table(i);
3580 END IF;
3581 END LOOP;
3582 ELSIF p_end_date < p_asgn_start_date THEN
3583 -- Moving Backward Totally outside range
3584
3585 PA_SCHEDULE_UTILS.log_message(1,'Moving Backward Totally outside range');
3586
3587 -- Example 1 : assgn start date is 20-Oct-2003 and assgn end date is 10-Nov-2003
3588 -- p_start_date is 01-Oct-2003 and p_end_date is 14-Oct-2003
3589 -- p_hours_table has 0,null,0,8,8,8,8,8,8,8,8,8,8,8
3590
3591 -- Example 2 : assgn start date is 15-Oct-2003 and assgn end date is 10-Nov-2003
3592 -- p_start_date is 01-Oct-2003 and p_end_date is 14-Oct-2003
3593 -- p_hours_table has 0,null,0,8,8,8,8,8,8,8,8,8,8,8
3594
3595 l_actual_end_date := p_end_date;
3596 FOR i IN p_hours_table.FIRST .. p_hours_table.LAST LOOP
3597 IF p_hours_table(i) IS NOT NULL AND p_hours_table(i) <> 0 THEN
3598 l_actual_start_date := p_start_date + (i-1);
3599 exit; -- As soon as find non zero, non null; come out
3600 END IF;
3601 END LOOP;
3602
3603 -- Example 1 Results : l_actual_start_date is 04-Oct-2003, l_actual_end_date is 14-Oct-2003
3604 -- Example 2 Results : l_actual_start_date is 04-Oct-2003, l_actual_end_date is 14-Oct-2003
3605
3606 IF l_actual_start_date IS NULL THEN
3607 -- This will happen when all 0 or null hours are passed
3608 l_changes_done := false;
3609 ELSE
3610 l_changes_done := true;
3611 l_call_change_duration := true;
3612 l_call_change_work_pattern := true;
3613 -- Start of addition for bug 4183479
3614 FOR i IN (l_actual_start_date-p_start_date+1) .. p_hours_table.LAST LOOP
3615 IF p_hours_table(i) IS NULL THEN
3616 l_hours_table(i-(l_actual_start_date-p_start_date+1)+1) := 0; -- Bug 3234786 : To make sure that it starts from 1
3617 ELSE
3618 l_hours_table(i-(l_actual_start_date-p_start_date+1)+1) := p_hours_table(i);-- Bug 3234786 : To make sure that it starts from 1
3619 END IF;
3620 END LOOP;
3621 -- End of addition for bug 4183479
3622 END IF;
3623
3624 -- Example 1 Results : l_changes_done, l_call_change_duration, l_call_change_work_pattern are true
3625 -- Example 2 Results : l_changes_done, l_call_change_duration, l_call_change_work_pattern are true
3626
3627 l_new_assgn_start_date := l_actual_start_date;
3628 l_new_assgn_end_date := p_asgn_end_date;
3629 l_update_work_zero_start_date := l_actual_end_date+1;
3630 l_update_work_zero_end_date := p_asgn_start_date-1;
3631 x_call_timeline_st_date := l_actual_start_date;
3632 x_call_timeline_end_date := l_actual_end_date;
3633
3634 IF l_update_work_zero_end_date >= l_update_work_zero_start_date THEN
3635 l_call_cng_work_patt_out_range := true;
3636 x_call_timeline_st_date := l_actual_start_date;
3637 x_call_timeline_end_date := l_update_work_zero_end_date;
3638 END IF;
3639
3640 -- Example 1 Results : l_new_assgn_start_date is 04-Oct-2003 and l_new_assgn_end_date is 10-Nov-2003
3641 -- So now new assignment date is 04-Oct-2003 to 10-Nov-2003 for which change_duration should be called.
3642 -- l_update_work_zero_start_date is 15-Oct-2003 and l_update_work_zero_end_date is 19-Oct-2003
3643 -- for which change_work_pattern should be called with all 0 hours in monday to sunday
3644 -- x_call_timeline_st_date, and x_call_timeline_end_date for which forecast should be regenerated
3645
3646 -- Example 2 Results : l_new_assgn_start_date is 04-Oct-2003 and l_new_assgn_end_date is 10-Nov-2003
3647 -- So now new assignment date is 04-Oct-2003 to 10-Nov-2003 for which change_duration should be called.
3648 -- l_update_work_zero_start_date is 15-Oct-2003 and l_update_work_zero_end_date is 14-Oct-2003
3649 -- so change_work_pattern should not be called with all 0 hours in monday to sunday. Hence
3650 -- l_call_cng_work_patt_out_range will remain false
3651 -- x_call_timeline_st_date, and x_call_timeline_end_date for which forecast should be regenerated
3652
3653 /* Commented and moved above for Bug 4183479
3654 FOR i IN (l_actual_start_date-p_start_date+1) .. p_hours_table.LAST LOOP
3655 IF p_hours_table(i) IS NULL THEN
3656 l_hours_table(i-(l_actual_start_date-p_start_date+1)+1) := 0; -- Bug 3234786 : To make sure that it starts from 1
3657 ELSE
3658 l_hours_table(i-(l_actual_start_date-p_start_date+1)+1) := p_hours_table(i);-- Bug 3234786 : To make sure that it starts from 1
3659 END IF;
3660 END LOOP; */
3661
3662 -- Example 1 Results : l_hours_table has 8,8,8,8,8,8,8,8,8,8,8
3663 -- Example 2 Results : l_hours_table has 8,8,8,8,8,8,8,8,8,8,8
3664
3665 ELSIF p_start_date > p_asgn_end_date THEN
3666 -- Moving Forward Totally outside range
3667
3668 PA_SCHEDULE_UTILS.log_message(1,'Moving Forward Totally outside range');
3669
3670 -- Example 1 : assgn start date is 20-Oct-2003 and assgn end date is 10-Nov-2003
3671 -- p_start_date is 15-Nov-2003 and p_end_date is 28-Nov-2003
3672 -- p_hours_table has 8,null,0,8,8,8,8,8,8,8,8,null,null,null
3673
3674 -- Example 2 : assgn start date is 20-Oct-2003 and assgn end date is 14-Nov-2003
3675 -- p_start_date is 15-Nov-2003 and p_end_date is 28-Nov-2003
3676 -- p_hours_table has 8,null,0,8,8,8,8,8,8,8,8,null,null,null
3677
3678 l_actual_start_date := p_start_date;
3679
3680 FOR i IN p_hours_table.FIRST .. p_hours_table.LAST LOOP
3681 IF p_hours_table(i) IS NOT NULL AND p_hours_table(i) <> 0 THEN
3682 l_actual_end_date := p_start_date + (i-1);
3683 END IF;
3684 END LOOP;
3685
3686 -- Example 1 Results : l_actual_start_date is 15-Nov-2003, l_actual_end_date is 25-Nov-2003
3687 -- Example 2 Results : l_actual_start_date is 15-Nov-2003, l_actual_end_date is 25-Nov-2003
3688
3689 IF l_actual_end_date IS NULL THEN
3690 -- This will happen when all 0 or null hours are passed
3691 l_changes_done := false;
3692 ELSE
3693 l_changes_done := true;
3694 l_call_change_duration := true;
3695 l_call_change_work_pattern := true;
3696 -- Start of addition for bug 4183479
3697 FOR i IN p_hours_table.FIRST .. (l_actual_end_date-p_start_date+1) LOOP
3698 IF p_hours_table(i) IS NULL THEN
3699 l_hours_table(i) := 0;
3700 ELSE
3701 l_hours_table(i) := p_hours_table(i);
3702 END IF;
3703 END LOOP;
3704 -- End of addition for bug 4183479
3705 END IF;
3706
3707 -- Example 1 Results : l_changes_done, l_call_change_duration, l_call_change_work_pattern are true
3708 -- Example 2 Results : l_changes_done, l_call_change_duration, l_call_change_work_pattern are true
3709
3710 l_new_assgn_start_date := p_asgn_start_date;
3711 l_new_assgn_end_date := l_actual_end_date;
3712 l_update_work_zero_start_date := p_asgn_end_date+1;
3713 l_update_work_zero_end_date := l_actual_start_date-1;
3714 x_call_timeline_st_date := l_actual_start_date;
3715 x_call_timeline_end_date := l_actual_end_date;
3716
3717 IF l_update_work_zero_end_date >= l_update_work_zero_start_date THEN
3718 l_call_cng_work_patt_out_range := true;
3719 x_call_timeline_st_date := l_update_work_zero_start_date;
3720 x_call_timeline_end_date := l_actual_end_date;
3721 END IF;
3722
3723 -- Example 1 Results : l_new_assgn_start_date is 20-Oct-2003 and l_new_assgn_end_date is 25-Nov-2003
3724 -- So now new assignment date is 20-Oct-2003 to 25-Nov-2003 for which change_duration should be called.
3725 -- l_update_work_zero_start_date is 11-Nov-2003 and l_update_work_zero_end_date is 14-Nov-2003
3726 -- for which change_work_pattern should be called with all 0 hours in monday to sunday
3727
3728 -- Example 2 Results : l_new_assgn_start_date is 20-Oct-2003 and l_new_assgn_end_date is 25-Nov-2003
3729 -- So now new assignment date is 04-Oct-2003 to 10-Nov-2003 for which change_duration should be called.
3730 -- l_update_work_zero_start_date is 15-Nov-2003 and l_update_work_zero_end_date is 14-Nov-2003
3731 -- so change_work_pattern should not be called with all 0 hours in monday to sunday. Hence
3732 -- l_call_cng_work_patt_out_range will remain false
3733
3734 /* Commented and moved above for Bug 4183479
3735 FOR i IN p_hours_table.FIRST .. (l_actual_end_date-p_start_date+1) LOOP
3736 IF p_hours_table(i) IS NULL THEN
3737 l_hours_table(i) := 0;
3738 ELSE
3739 l_hours_table(i) := p_hours_table(i);
3740 END IF;
3741 END LOOP; */
3742
3743 -- Example 1 Results : l_hours_table has 8,0,0,8,8,8,8,8,8,8,8
3744 -- Example 2 Results : l_hours_table has 8,0,0,8,8,8,8,8,8,8,8
3745
3746 ELSIF (p_start_date BETWEEN p_asgn_start_date AND p_asgn_end_date) AND p_end_date > p_asgn_end_date THEN
3747 -- Moving Forward Partially outside range
3748
3749 PA_SCHEDULE_UTILS.log_message(1,'Moving Forward Partially outside range');
3750
3751 --Changes are done, now further it needs to be determined that whether work pattern has changed or not
3752 l_changes_done := true;
3753 l_actual_start_date := p_start_date;
3754 FOR i IN p_hours_table.FIRST .. p_hours_table.LAST LOOP
3755 IF p_hours_table(i) IS NOT NULL AND p_hours_table(i) <> 0 THEN
3756 l_actual_end_date := p_start_date + (i-1);
3757 END IF;
3758 END LOOP;
3759
3760 IF l_actual_end_date IS NULL THEN
3761 l_actual_end_date := p_asgn_end_date;
3762 -- call change duration will remain false in this case
3763 -- But we need to see further that work pattern changed or not
3764 ELSE
3765 IF l_actual_end_date <= p_asgn_end_date THEN -- cut off in assignment is not possible
3766 l_actual_end_date := p_asgn_end_date;
3767 -- call change duration will remain false in this case
3768 -- But we need to see further that work pattern changed or not
3769 ELSE
3770 l_call_change_duration := true;
3771 l_call_change_work_pattern := true;
3772 END IF;
3773 END IF;
3774
3775 -- x_call_timeline_st_date, and x_call_timeline_end_date for which forecast should be regenerated
3776 x_call_timeline_st_date := l_actual_start_date;
3777 x_call_timeline_end_date := l_actual_end_date;
3778 l_new_assgn_start_date := p_asgn_start_date;
3779 l_new_assgn_end_date := l_actual_end_date;
3780 -- l_call_cng_work_patt_out_range will remain false
3781
3782 FOR i IN p_hours_table.FIRST .. (l_actual_end_date-p_start_date+1) LOOP
3783 IF p_hours_table(i) IS NULL THEN
3784 l_hours_table(i) := 0;
3785 ELSE
3786 l_hours_table(i) := p_hours_table(i);
3787 END IF;
3788 END LOOP;
3789 ELSIF (p_end_date BETWEEN p_asgn_start_date AND p_asgn_end_date) AND p_start_date < p_asgn_start_date THEN
3790 -- Moving Backward Partially outside range
3791
3792 PA_SCHEDULE_UTILS.log_message(1,'Moving Backward Partially outside range');
3793
3794 --Changes are done, now further it needs to be determined that whether work pattern has changed or not
3795 l_changes_done := true;
3796 l_actual_end_date := p_end_date;
3797 FOR i IN p_hours_table.FIRST .. p_hours_table.LAST LOOP
3798 IF p_hours_table(i) IS NOT NULL AND p_hours_table(i) <> 0 THEN
3799 l_actual_start_date := p_start_date + (i-1);
3800 exit; -- As soon as find non zero come out
3801 END IF;
3802 END LOOP;
3803
3804 IF l_actual_start_date IS NULL THEN
3805 -- This will happen when all 0 or null hours are passed
3806 l_actual_start_date := p_asgn_start_date;
3807 ELSE
3808 IF l_actual_start_date >= p_asgn_start_date THEN -- cut off in assignment is not possible
3809 l_actual_start_date := p_asgn_start_date;
3810 -- call change duration will remain false in this case
3811 -- But we need to see further that work pattern changed or not
3812 ELSE
3813 l_call_change_duration := true;
3814 l_call_change_work_pattern := true;
3815 END IF;
3816 END IF;
3817
3818 -- x_call_timeline_st_date, and x_call_timeline_end_date for which forecast should be regenerated
3819 l_new_assgn_start_date := l_actual_start_date;
3820 l_new_assgn_end_date := p_asgn_end_date;
3821 x_call_timeline_st_date := l_actual_start_date;
3822 x_call_timeline_end_date := l_actual_end_date;
3823 -- l_call_cng_work_patt_out_range will remain false
3824
3825 FOR i IN (l_actual_start_date-p_start_date+1) .. p_hours_table.LAST LOOP
3826 IF p_hours_table(i) IS NULL THEN
3827 l_hours_table(i-(l_actual_start_date-p_start_date+1)+1) := 0;-- Bug 3234786 : To make sure that it starts from 1
3828 ELSE
3829 l_hours_table(i-(l_actual_start_date-p_start_date+1)+1) := p_hours_table(i);-- Bug 3234786 : To make sure that it starts from 1
3830 END IF;
3831 END LOOP;
3832 ELSIF ((p_asgn_start_date BETWEEN p_start_date AND p_end_date) AND (p_asgn_end_date BETWEEN p_start_date AND p_end_date)) THEN
3833 -- Moving Partially Backward and Forward Both outside range
3834
3835 PA_SCHEDULE_UTILS.log_message(1,'Moving Partially Backward and Forward Both outside range');
3836
3837 --Changes are done, now further it needs to be determined that whether work pattern has changed or not
3838 l_changes_done := true;
3839 l_actual_end_date := p_end_date;
3840 FOR i IN p_hours_table.FIRST .. p_hours_table.LAST LOOP
3841 IF p_hours_table(i) IS NOT NULL AND p_hours_table(i) <> 0 THEN
3842 l_actual_start_date := p_start_date + (i-1);
3843 exit; -- As soon as find non zero come out
3844 END IF;
3845 END LOOP;
3846
3847 FOR i IN p_hours_table.FIRST .. p_hours_table.LAST LOOP
3848 IF p_hours_table(i) IS NOT NULL AND p_hours_table(i) <> 0 THEN
3849 l_actual_end_date := p_start_date + (i-1);
3850 END IF;
3851 END LOOP;
3852
3853 IF l_actual_start_date IS NULL THEN
3854 -- This will happen when all 0 or null hours are passed
3855 l_actual_start_date := p_asgn_start_date;
3856 ELSE
3857 IF l_actual_start_date >= p_asgn_start_date THEN -- cut off in assignment is not possible
3858 l_actual_start_date := p_asgn_start_date;
3859 -- call change duration will remain false in this case
3860 -- But we need to see further that work pattern changed or not
3861 ELSE
3862 l_call_change_duration := true;
3863 l_call_change_work_pattern := true;
3864 END IF;
3865 END IF;
3866
3867 IF l_actual_end_date IS NULL THEN
3868 -- This will happen when all 0 or null hours are passed
3869 l_actual_end_date := p_asgn_end_date;
3870 ELSE
3871 IF l_actual_end_date <= p_asgn_end_date THEN -- cut off in assignment is not possible
3872 l_actual_end_date := p_asgn_end_date;
3873 -- call change duration will remain false in this case
3874 -- But we need to see further that work pattern changed or not
3875 ELSE
3876 l_call_change_duration := true;
3877 l_call_change_work_pattern := true;
3878 END IF;
3879 END IF;
3880
3881 -- x_call_timeline_st_date, and x_call_timeline_end_date for which forecast should be regenerated
3882 l_new_assgn_start_date := l_actual_start_date;
3883 l_new_assgn_end_date := l_actual_end_date;
3884 x_call_timeline_st_date := l_actual_start_date;
3885 x_call_timeline_end_date := l_actual_end_date;
3886 -- l_call_cng_work_patt_out_range will remain false
3887
3888 FOR i IN (l_actual_start_date-p_start_date+1) .. (l_actual_end_date-p_start_date+1) LOOP
3889 IF p_hours_table(i) IS NULL THEN
3890 l_hours_table(i-(l_actual_start_date-p_start_date+1)+1) := 0;-- Bug 3234786 : To make sure that it starts from 1
3891 ELSE
3892 l_hours_table(i-(l_actual_start_date-p_start_date+1)+1) := p_hours_table(i);-- Bug 3234786 : To make sure that it starts from 1
3893 END IF;
3894 END LOOP;
3895
3896
3897 END IF; --(p_start_date BETWEEN p_asgn_start_date AND p_asgn_end_date) AND (p_end_date BETWEEN p_asgn_start_date AND p_asgn_end_date)) THEN
3898
3899 -- Parameters Detrmination Phase Ends
3900 PA_SCHEDULE_UTILS.log_message(1,'Parameters Detrmination Phase Ends');
3901 PA_SCHEDULE_UTILS.log_message(1,'Parameters Are ...');
3902 PA_SCHEDULE_UTILS.log_message(1,'l_actual_start_date='||l_actual_start_date);
3903 PA_SCHEDULE_UTILS.log_message(1,'l_actual_end_date='||l_actual_end_date);
3904 PA_SCHEDULE_UTILS.log_message(1,'l_new_assgn_start_date='||l_new_assgn_start_date);
3905 PA_SCHEDULE_UTILS.log_message(1,'l_new_assgn_end_date='||l_new_assgn_end_date);
3906 PA_SCHEDULE_UTILS.log_message(1,'l_update_work_zero_start_date='||l_update_work_zero_start_date);
3907 PA_SCHEDULE_UTILS.log_message(1,'l_update_work_zero_end_date='||l_update_work_zero_end_date);
3908
3909
3910 IF l_changes_done THEN
3911 PA_SCHEDULE_UTILS.log_message(1,'l_changes_done is true');
3912
3913 -- Initialization of hours table
3914 -- These tables will be used while calling change_work_pattern
3915
3916 FOR i in 1..2 LOOP
3917 l_monday_hours(i):=-99;
3918 l_tuesday_hours(i):=-99;
3919 l_wednesday_hours(i):=-99;
3920 l_thursday_hours(i):=-99;
3921 l_friday_hours(i):=-99;
3922 l_saturday_hours(i):=-99;
3923 l_sunday_hours(i):= -99;
3924 END LOOP;
3925 /*Placed the call here for the bug 3421637*/
3926 IF l_call_change_duration THEN
3927 PA_SCHEDULE_UTILS.log_message(1,'l_call_change_duration is true');
3928
3929 PA_SCHEDULE_UTILS.log_message(1,'Calling change_duration');
3930 PA_SCHEDULE_UTILS.log_message(1,'l_new_assgn_start_date='||l_new_assgn_start_date);
3931 PA_SCHEDULE_UTILS.log_message(1,'l_new_assgn_end_date='||l_new_assgn_end_date);
3932
3933 pa_schedule_pub.change_duration(
3934 p_record_version_number => p_record_version_number,
3935 p_exception_type_code => 'CHANGE_DURATION' ,
3936 p_project_id => p_project_id ,
3937 p_calendar_id => p_calendar_id ,
3938 p_assignment_id => p_assignment_id ,
3939 p_assignment_type => p_assignment_type ,
3940 p_start_date => l_new_assgn_start_date ,
3941 p_end_date => l_new_assgn_end_date ,
3942 p_assignment_status_code => p_assignment_status_code ,
3943 p_asgn_start_date => p_asgn_start_date ,
3944 p_asgn_end_date => p_asgn_end_date ,
3945 p_init_msg_list => FND_API.G_FALSE ,
3946 p_generate_timeline_flag => 'N' ,
3947 x_return_status => x_return_status ,
3948 x_msg_count => x_msg_count ,
3949 x_msg_data => x_msg_data) ;
3950
3951 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3952 raise API_ERROR;
3953 END IF;
3954
3955 -- There is a gap found, so need to fill this with 0 hours
3956 IF l_call_cng_work_patt_out_range THEN
3957 PA_SCHEDULE_UTILS.log_message(1,'l_call_cng_work_patt_out_range is true');
3958
3959 pa_schedule_pub.change_work_pattern(
3960 p_record_version_number => p_record_version_number ,
3961 p_project_id => p_project_id ,
3962 p_calendar_id => p_calendar_id ,
3963 p_assignment_id => p_assignment_id ,
3964 p_assignment_type => p_assignment_type ,
3965 p_start_date => l_update_work_zero_start_date ,
3966 p_end_date => l_update_work_zero_end_date ,
3967 p_monday_hours => 0 ,
3968 p_tuesday_hours => 0 ,
3969 p_wednesday_hours => 0 ,
3970 p_thursday_hours => 0 ,
3971 p_friday_hours => 0 ,
3972 p_saturday_hours => 0 ,
3973 p_sunday_hours => 0 ,
3974 p_asgn_start_date => l_new_assgn_start_date ,
3975 p_asgn_end_date => l_new_assgn_end_date ,
3976 p_init_msg_list => FND_API.G_FALSE ,
3977 p_last_row_flag => 'Y' , --Changed 'N' to 'Y' for Bug 4165970.
3978 p_generate_timeline_flag => 'N' ,
3979 x_return_status => x_return_status ,
3980 x_msg_count => x_msg_count ,
3981 x_msg_data => x_msg_data) ;
3982
3983 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3984 raise API_ERROR;
3985 END IF;
3986 END IF; -- l_call_cng_work_patt_out_range THEN
3987
3988 END IF; -- l_call_change_duration THEN*
3989
3990 PA_SCHEDULE_UTILS.log_message(1,'Calling pa_schedule_pvt.get_assignment_schedule');
3991
3992 pa_schedule_pvt.get_assignment_schedule ( p_assignment_id => p_assignment_id ,
3993 p_start_date => l_actual_start_date ,
3994 p_end_date => l_actual_end_date ,
3995 x_sch_record_tab => l_sch_record_tab ,
3996 x_return_status => x_return_status ,
3997 x_msg_count => x_msg_count ,
3998 x_msg_data => x_msg_data) ;
3999
4000
4001
4002 -- get_assignment_schedule will return 0 records in l_sch_record_tab, if given dates are
4003 -- outside the assignment range. So we need to populate the l_sch_record_tab with one row here
4004
4005 IF l_sch_record_tab.COUNT = 0 THEN
4006 l_sch_record_tab(1).start_date := l_actual_start_date;
4007 l_sch_record_tab(1).end_date := l_actual_end_date;
4008 l_sch_record_tab(1).monday_hours := 0;
4009 l_sch_record_tab(1).tuesday_hours := 0;
4010 l_sch_record_tab(1).wednesday_hours := 0;
4011 l_sch_record_tab(1).thursday_hours := 0;
4012 l_sch_record_tab(1).friday_hours := 0;
4013 l_sch_record_tab(1).saturday_hours := 0;
4014 l_sch_record_tab(1).sunday_hours := 0;
4015 END IF;
4016
4017 PA_SCHEDULE_UTILS.log_message(1,'After calling pa_schedule_pvt.get_assignment_schedule l_sch_record_tab.count='||l_sch_record_tab.count);
4018
4019 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4020 raise API_ERROR;
4021 END IF;
4022
4023
4024 -- Put the monday..sunday hours in the l_hours_db_table table which stores the data base values of the
4025 -- schedule records. Also find out the monday..sunday hours to be passed to change_work_pattern
4026 /* Start of Addition for bug 4068167 */
4027 Begin
4028 select decode(l_global_week_start_day,1,1,2,0,3,6,4,5,5,4,6,3,7,2,0) into l_days_to_inc from dual;
4029 END;
4030 /* End of addition for bug 4068167 */
4031
4032 IF l_sch_record_tab.COUNT > 0 THEN
4033 l_counter := 1;
4034 FOR j IN l_sch_record_tab.FIRST..l_sch_record_tab.LAST LOOP
4035 l_date := l_sch_record_tab(j).start_date;
4036
4037 IF l_sch_record_tab(j).start_date IS NOT NULL AND l_sch_record_tab(j).end_date IS NOT NULL THEN
4038 LOOP
4039 l_week_day := TO_CHAR(l_date, 'DY', 'NLS_DATE_LANGUAGE=AMERICAN');
4040
4041 IF l_week_day = 'MON' THEN
4042 l_hours_db_table(l_counter) := l_sch_record_tab(j).monday_hours;
4043 /*Modified the if condition as below for the bug 3421637*/
4044 --IF l_monday_hours(1) = -99
4045 /* Commented for bug 4068167 IF l_monday_hours(1) = -99 and (l_date = p_start_Date
4046 OR (l_date = p_start_date + 7 and l_actual_start_date > p_start_date+6)) */
4047 -- Start of addition for bug 4068167
4048 l_actual_days_to_inc := l_days_to_inc;
4049 if (l_actual_days_to_inc>6) then
4050 l_actual_days_to_inc := l_actual_days_to_inc - 7 ;
4051 end if ;
4052 IF l_monday_hours(1) = -99 and (l_date = p_start_Date + l_actual_days_to_inc
4053 OR (l_date = p_start_date + l_actual_days_to_inc + 7 and l_actual_start_date > p_start_date+6)) -- End of addition for bug 4068167
4054 THEN
4055 l_monday_hours(1) := l_hours_table(l_counter);
4056 ELSE
4057 l_monday_hours(2) := l_hours_table(l_counter);
4058 END IF;
4059 l_counter := l_counter+1;
4060 ELSIF l_week_day = 'TUE' THEN
4061 l_hours_db_table(l_counter) := l_sch_record_tab(j).tuesday_hours;
4062 /*Modified the if condition as below for the bug 3421637*/
4063 --IF l_tuesday_hours(1) = -99
4064 /* Commented for bug 4068167 IF l_tuesday_hours(1) = -99 AND (l_date = p_start_Date + 1
4065 OR (l_date = p_start_date + 8 and l_actual_start_date > p_start_date+6)) */
4066 -- Start of addition for bug 4068167
4067 l_actual_days_to_inc := l_days_to_inc + 1;
4068 if (l_actual_days_to_inc>6) then
4069 l_actual_days_to_inc := l_actual_days_to_inc - 7 ;
4070 end if ;
4071 IF l_tuesday_hours(1) = -99 AND (l_date = p_start_Date + l_actual_days_to_inc
4072 OR (l_date = p_start_date + l_actual_days_to_inc + 7 and l_actual_start_date > p_start_date+6)) -- End of addition for bug 4068167
4073 THEN
4074 l_tuesday_hours(1) := l_hours_table(l_counter);
4075 ELSE
4076 l_tuesday_hours(2) := l_hours_table(l_counter);
4077 END IF;
4078 l_counter := l_counter+1;
4079 ELSIF l_week_day = 'WED' THEN
4080 l_hours_db_table(l_counter) := l_sch_record_tab(j).wednesday_hours;
4081 /*Modified the if condition as below for the bug 3421637*/
4082 --IF l_wednesday_hours(1) = -99
4083 /*Commented for bug 4068167 IF l_wednesday_hours(1) = -99 AND (l_date = p_start_Date + 2
4084 OR (l_date = p_start_date + 9 and l_actual_start_date > p_start_date+6))*/
4085 -- Start of addition for bug 4068167
4086 l_actual_days_to_inc := l_days_to_inc + 2;
4087 if (l_actual_days_to_inc>6) then
4088 l_actual_days_to_inc := l_actual_days_to_inc - 7 ;
4089 end if ;
4090 IF l_wednesday_hours(1) = -99 AND (l_date = p_start_Date + l_actual_days_to_inc
4091 OR (l_date = p_start_date + l_actual_days_to_inc + 7 and l_actual_start_date > p_start_date+6)) -- End of addition for bug 4068167
4092 THEN
4093 l_wednesday_hours(1) := l_hours_table(l_counter);
4094 ELSE
4095 l_wednesday_hours(2) := l_hours_table(l_counter);
4096 END IF;
4097 l_counter := l_counter+1;
4098 ELSIF l_week_day = 'THU' THEN
4099 l_hours_db_table(l_counter) := l_sch_record_tab(j).thursday_hours;
4100 /* Commented for bug 4068167 Modified the if condition as below for the bug 3421637*/
4101 --IF l_thursday_hours(1) = -99
4102 /*IF l_thursday_hours(1) = -99 AND (l_date = p_start_Date + 3
4103 OR (l_date = p_start_date + 10 and l_actual_start_date > p_start_date+6))*/
4104 -- Start of addition for bug 4068167
4105 l_actual_days_to_inc := l_days_to_inc + 3;
4106 if (l_actual_days_to_inc>6) then
4107 l_actual_days_to_inc := l_actual_days_to_inc - 7 ;
4108 end if ;
4109 IF l_thursday_hours(1) = -99 AND (l_date = p_start_Date + l_actual_days_to_inc
4110 OR (l_date = p_start_date + l_actual_days_to_inc + 7 and l_actual_start_date > p_start_date+6)) -- End of addition for bug 4068167
4111 THEN
4112 l_thursday_hours(1) := l_hours_table(l_counter);
4113 ELSE
4114 l_thursday_hours(2) := l_hours_table(l_counter);
4115 END IF;
4116 l_counter := l_counter+1;
4117 ELSIF l_week_day = 'FRI' THEN
4118 l_hours_db_table(l_counter) := l_sch_record_tab(j).friday_hours;
4119 /*Modified the if condition as below for the bug 3421637*/
4120 --IF l_friday_hours(1) = -99
4121 /* Commented for bug 4068167 IF l_friday_hours(1) = -99 AND (l_date = p_start_Date + 4
4122 OR (l_date = p_start_date + 11 and l_actual_start_date > p_start_date+6)) */
4123 -- Start of addition for bug 4068167
4124 l_actual_days_to_inc := l_days_to_inc + 4;
4125 if (l_actual_days_to_inc>6) then
4126 l_actual_days_to_inc := l_actual_days_to_inc - 7 ;
4127 end if ;
4128 IF l_friday_hours(1) = -99 AND (l_date = p_start_Date + l_actual_days_to_inc
4129 OR (l_date = p_start_date + l_actual_days_to_inc + 7 and l_actual_start_date > p_start_date+6)) -- End of addition for bug 4068167
4130 THEN
4131 l_friday_hours(1) := l_hours_table(l_counter);
4132 ELSE
4133 l_friday_hours(2) := l_hours_table(l_counter);
4134 END IF;
4135 l_counter := l_counter+1;
4136 ELSIF l_week_day = 'SAT' THEN
4137 l_hours_db_table(l_counter) := l_sch_record_tab(j).saturday_hours;
4138 /*Modified the if condition as below for the bug 3421637*/
4139 --IF l_saturday_hours(1) = -99
4140 /* Commented for bug 4068167 IF l_saturday_hours(1) = -99 AND (l_date = p_start_Date + 5
4141 OR (l_date = p_start_date + 12 and l_actual_start_date > p_start_date+6)) */
4142 -- Start of addition for bug 4068167
4143 l_actual_days_to_inc := l_days_to_inc + 5;
4144 if (l_actual_days_to_inc>6) then
4145 l_actual_days_to_inc := l_actual_days_to_inc - 7 ;
4146 end if ;
4147 IF l_saturday_hours(1) = -99 AND (l_date = p_start_Date + l_actual_days_to_inc
4148 OR (l_date = p_start_date + l_actual_days_to_inc + 7 and l_actual_start_date > p_start_date+6)) -- End of addition for bug 4068167
4149 THEN
4150 l_saturday_hours(1) := l_hours_table(l_counter);
4151 ELSE
4152 l_saturday_hours(2) := l_hours_table(l_counter);
4153 END IF;
4154 l_counter := l_counter+1;
4155 ELSIF l_week_day = 'SUN' THEN
4156 l_hours_db_table(l_counter) := l_sch_record_tab(j).sunday_hours;
4157 /*Modified the if condition as below for the bug 3421637*/
4158 --IF l_sunday_hours(1) = -99
4159 /* Commented for bug 4068167 IF l_sunday_hours(1) = -99 AND (l_date = p_start_Date + 6
4160 OR (l_date = p_start_date + 13 and l_actual_start_date > p_start_date+6)) */
4161 -- Start of addition for bug 4068167
4162 l_actual_days_to_inc := l_days_to_inc + 6;
4163 if (l_actual_days_to_inc>6) then
4164 l_actual_days_to_inc := l_actual_days_to_inc - 7 ;
4165 end if ;
4166 IF l_sunday_hours(1) = -99 AND (l_date = p_start_Date + l_actual_days_to_inc
4167 OR (l_date = p_start_date + l_actual_days_to_inc + 7 and l_actual_start_date > p_start_date+6)) -- End of addition for bug 4068167
4168 THEN
4169 l_sunday_hours(1) := l_hours_table(l_counter);
4170 ELSE
4171 l_sunday_hours(2) := l_hours_table(l_counter);
4172 END IF;
4173 l_counter := l_counter+1;
4174 END IF;
4175
4176 l_date := l_date + 1;
4177
4178 EXIT WHEN trunc(l_date) > trunc(l_sch_record_tab(j).end_date);
4179 END LOOP;
4180 END IF; -- l_sch_record_tab(j).start_date IS NOT NULL AND l_sch_record_tab(j).end_date IS NOT NULL THEN
4181 END LOOP; -- j IN l_sch_record_tab.FIRST..l_sch_record_tab.LAST LOOP
4182 END IF; -- l_sch_record_tab.COUNT > 0 THEN
4183
4184 PA_SCHEDULE_UTILS.log_message(1,'After populating the monday..sunday hours tables and l_hours_db_table ');
4185
4186 -- Compare the passed hours with the database values. If no changes then no need to call change_work_pattern
4187 -- if l_call_change_duration then it means work pattern changes will be alwyas there
4188 IF l_call_change_duration = false THEN
4189 FOR i IN l_hours_db_table.FIRST..l_hours_db_table.LAST LOOP
4190 IF l_hours_db_table(i) <> l_hours_table(i) THEN
4191 l_call_change_work_pattern := true;
4192 END IF;
4193 END LOOP;
4194 END IF;
4195
4196 IF l_call_change_duration = false AND l_call_change_work_pattern = false THEN
4197 x_call_timeline_st_date := null;
4198 x_call_timeline_end_date := null;
4199 --x_person_id := null;
4200 --return;
4201 END IF;
4202
4203 -- Now all parameters for calling API's are determined. Now start calling them
4204
4205 -- Returning the person_id back to the calling environment so that it does not have to fetch
4206 --x_person_id := PA_FORECAST_ITEMS_UTILS.get_person_id(p_resource_id);
4207
4208
4209 -- Initialize the monday..sinday hours table with 0 if data is not populated already
4210 -- Note that in First set of hours at least one day will have at least one non -99 value
4211
4212 IF l_monday_hours(1) = -99 THEN
4213 l_monday_hours(1) := 0;
4214 END IF;
4215 IF l_tuesday_hours(1) = -99 THEN
4216 l_tuesday_hours(1) := 0;
4217 END IF;
4218 IF l_wednesday_hours(1) = -99 THEN
4219 l_wednesday_hours(1) := 0;
4220 END IF;
4221 IF l_thursday_hours(1) = -99 THEN
4222 l_thursday_hours(1) := 0;
4223 END IF;
4224 IF l_friday_hours(1) = -99 THEN
4225 l_friday_hours(1) := 0;
4226 END IF;
4227 IF l_saturday_hours(1) = -99 THEN
4228 l_saturday_hours(1) := 0;
4229 END IF;
4230 IF l_sunday_hours(1) = -99 THEN
4231 l_sunday_hours(1) := 0;
4232 END IF;
4233
4234 /*Added for the bug 3421637*/
4235 IF l_monday_hours(2) = -99 THEN
4236 l_monday_hours(2) := 0;
4237 END IF;
4238 IF l_tuesday_hours(2) = -99 THEN
4239 l_tuesday_hours(2) := 0;
4240 END IF;
4241 IF l_wednesday_hours(2) = -99 THEN
4242 l_wednesday_hours(2) := 0;
4243 END IF;
4244 IF l_thursday_hours(2) = -99 THEN
4245 l_thursday_hours(2) := 0;
4246 END IF;
4247 IF l_friday_hours(2) = -99 THEN
4248 l_friday_hours(2) := 0;
4249 END IF;
4250 IF l_saturday_hours(2) = -99 THEN
4251 l_saturday_hours(2) := 0;
4252 END IF;
4253 IF l_sunday_hours(2) = -99 THEN
4254 l_sunday_hours(2) := 0;
4255 END IF;
4256 /*Added till here for the bug 3421637*/
4257 -- If Only 7 days records are passed then no need to call change_work_pattern second time.
4258 -- Otherwise we need to call it two times.
4259
4260 /*Addition for the bug 3421637 starts*/
4261 l_call_second_time := False;
4262
4263 IF (l_actual_start_date - p_start_date) <= 6
4264 AND (l_actual_end_date - p_start_date ) >= 6
4265 THEN
4266 l_call_second_time := True;
4267 END IF;
4268
4269 If l_call_second_time THEN
4270 l_ch_work_pattern_st_date1 := l_actual_start_date;
4271 l_ch_work_pattern_end_date1 := p_start_date + 6; --l_actual_start_date+6;
4272 l_ch_work_pattern_st_date2 := p_start_date + 7; --l_actual_start_date+7;
4273 l_ch_work_pattern_end_date2 := l_actual_end_date;
4274 l_call_first_time := false;
4275 for i in 1 .. (l_ch_work_pattern_end_date1 - l_ch_work_pattern_st_date1) + 1 LOOP
4276 IF l_hours_db_table(i) <> l_hours_table(i) THEN
4277 l_call_first_time := true;
4278 exit;
4279 END IF;
4280 END LOOP;
4281 l_call_second_time := false;
4282 for i in (l_ch_work_pattern_st_date2 - l_ch_work_pattern_st_date1) +1 ..
4283 (l_ch_work_pattern_end_date2 - l_ch_work_pattern_st_date1) + 1 LOOP
4284 IF l_hours_db_table(i) <> l_hours_table(i) THEN
4285 l_call_second_time := true;
4286 exit;
4287 END IF;
4288 END LOOP;
4289
4290 ELSE
4291 l_ch_work_pattern_st_date1 := l_actual_start_date;
4292 l_ch_work_pattern_end_date1 := l_actual_end_date;
4293 l_call_first_time := false;
4294 for i in 1 .. (l_ch_work_pattern_end_date1 - l_ch_work_pattern_st_date1) + 1 LOOP
4295 IF l_hours_db_table(i) <> l_hours_table(i) THEN
4296 l_call_first_time := true;
4297 exit;
4298 END IF;
4299 END LOOP;
4300 END IF;
4301
4302 /*Addition for the bug 3421637 ends*/
4303
4304 /*Commenting the below code for the bug 3421637*/
4305
4306 /* IF (l_actual_end_date - l_actual_start_date) <= 6 THEN
4307 l_ch_work_pattern_st_date1 := l_actual_start_date;
4308 l_ch_work_pattern_end_date1 := l_actual_end_date;
4309 l_call_second_time := false;
4310 ELSE
4311 l_ch_work_pattern_st_date1 := l_actual_start_date;
4312 l_ch_work_pattern_end_date1 := l_actual_start_date+6;
4313 l_ch_work_pattern_st_date2 := l_actual_start_date+7;
4314 l_ch_work_pattern_end_date2 := l_actual_end_date;
4315 l_call_second_time := true;
4316
4317 -- If need to call second time then Initialize the second set of monday..sunday hours
4318 -- table with 0 if data is not populated already
4319 IF l_monday_hours(2) = -99 THEN
4320 l_monday_hours(2) := 0;
4321 END IF;
4322 IF l_tuesday_hours(2) = -99 THEN
4323 l_tuesday_hours(2) := 0;
4324 END IF;
4325 IF l_wednesday_hours(2) = -99 THEN
4326 l_wednesday_hours(2) := 0;
4327 END IF;
4328 IF l_thursday_hours(2) = -99 THEN
4329 l_thursday_hours(2) := 0;
4330 END IF;
4331 IF l_friday_hours(2) = -99 THEN
4332 l_friday_hours(2) := 0;
4333 END IF;
4334 IF l_saturday_hours(2) = -99 THEN
4335 l_saturday_hours(2) := 0;
4336 END IF;
4337 IF l_sunday_hours(2) = -99 THEN
4338 l_sunday_hours(2) := 0;
4339 END IF;
4340 END IF; -- (l_actual_end_date - l_actual_start_date) <= 6 THEN*/
4341 /*Commenting till here for the bug 3421637*/
4342
4343
4344 /*Moved the below call above */
4345 /* IF l_call_change_duration THEN
4346 PA_SCHEDULE_UTILS.log_message(1,'l_call_change_duration is true');
4347
4348 PA_SCHEDULE_UTILS.log_message(1,'Calling change_duration');
4349 PA_SCHEDULE_UTILS.log_message(1,'l_new_assgn_start_date='||l_new_assgn_start_date);
4350 PA_SCHEDULE_UTILS.log_message(1,'l_new_assgn_end_date='||l_new_assgn_end_date);
4351
4352 pa_schedule_pub.change_duration(
4353 p_record_version_number => p_record_version_number,
4354 p_exception_type_code => 'CHANGE_DURATION' ,
4355 p_project_id => p_project_id ,
4356 p_calendar_id => p_calendar_id ,
4357 p_assignment_id => p_assignment_id ,
4358 p_assignment_type => p_assignment_type ,
4359 p_start_date => l_new_assgn_start_date ,
4360 p_end_date => l_new_assgn_end_date ,
4361 p_assignment_status_code => p_assignment_status_code ,
4362 p_asgn_start_date => p_asgn_start_date ,
4363 p_asgn_end_date => p_asgn_end_date ,
4364 p_init_msg_list => FND_API.G_FALSE ,
4365 p_generate_timeline_flag => 'N' ,
4366 x_return_status => x_return_status ,
4367 x_msg_count => x_msg_count ,
4368 x_msg_data => x_msg_data) ;
4369
4370 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4371 raise API_ERROR;
4372 END IF;
4373
4374 -- There is a gap found, so need to fill this with 0 hours
4375 IF l_call_cng_work_patt_out_range THEN
4376 PA_SCHEDULE_UTILS.log_message(1,'l_call_cng_work_patt_out_range is true');
4377
4378 pa_schedule_pub.change_work_pattern(
4379 p_record_version_number => p_record_version_number ,
4380 p_project_id => p_project_id ,
4381 p_calendar_id => p_calendar_id ,
4382 p_assignment_id => p_assignment_id ,
4383 p_assignment_type => p_assignment_type ,
4384 p_start_date => l_update_work_zero_start_date ,
4385 p_end_date => l_update_work_zero_end_date ,
4386 p_monday_hours => 0 ,
4387 p_tuesday_hours => 0 ,
4388 p_wednesday_hours => 0 ,
4389 p_thursday_hours => 0 ,
4390 p_friday_hours => 0 ,
4391 p_saturday_hours => 0 ,
4392 p_sunday_hours => 0 ,
4393 p_asgn_start_date => l_new_assgn_start_date ,
4394 p_asgn_end_date => l_new_assgn_end_date ,
4395 p_init_msg_list => FND_API.G_FALSE ,
4396 p_last_row_flag => 'N' ,
4397 p_generate_timeline_flag => 'N' ,
4398 x_return_status => x_return_status ,
4399 x_msg_count => x_msg_count ,
4400 x_msg_data => x_msg_data) ;
4401
4402 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4403 raise API_ERROR;
4404 END IF;
4405 END IF; -- l_call_cng_work_patt_out_range THEN
4406
4407 END IF; -- l_call_change_duration THEN*/
4408
4409 IF l_call_change_work_pattern THEN
4410
4411 PA_SCHEDULE_UTILS.log_message(1,'l_call_change_work_pattern is true');
4412 PA_SCHEDULE_UTILS.log_message(1,'Calling change_work_pattern first time');
4413 PA_SCHEDULE_UTILS.log_message(1,'l_ch_work_pattern_st_date1='||l_ch_work_pattern_st_date1);
4414 PA_SCHEDULE_UTILS.log_message(1,'l_ch_work_pattern_end_date1='||l_ch_work_pattern_end_date1);
4415
4416 -- Call change_work_pattern for the first 7 days
4417 IF l_call_second_time THEN
4418 l_last_row_flag := 'N';
4419 ELSE
4420 l_last_row_flag := 'Y';
4421 END IF;
4422
4423 If l_call_first_time THEN
4424 pa_schedule_pub.change_work_pattern(
4425 p_record_version_number => p_record_version_number ,
4426 p_project_id => p_project_id ,
4427 p_calendar_id => p_calendar_id ,
4428 p_assignment_id => p_assignment_id ,
4429 p_assignment_type => p_assignment_type ,
4430 p_start_date => l_ch_work_pattern_st_date1 ,
4431 p_end_date => l_ch_work_pattern_end_date1 ,
4432 p_monday_hours => l_monday_hours(1) ,
4433 p_tuesday_hours => l_tuesday_hours(1) ,
4434 p_wednesday_hours => l_wednesday_hours(1) ,
4435 p_thursday_hours => l_thursday_hours(1) ,
4436 p_friday_hours => l_friday_hours(1) ,
4437 p_saturday_hours => l_saturday_hours(1) ,
4438 p_sunday_hours => l_sunday_hours(1) ,
4439 p_asgn_start_date => l_new_assgn_start_date ,
4440 p_asgn_end_date => l_new_assgn_end_date ,
4441 p_init_msg_list => FND_API.G_FALSE ,
4442 p_last_row_flag => l_last_row_flag ,
4443 p_generate_timeline_flag => 'N' ,
4444 x_return_status => x_return_status ,
4445 x_msg_count => x_msg_count ,
4446 x_msg_data => x_msg_data) ;
4447
4448 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4449 raise API_ERROR;
4450 END IF;
4451 END IF;
4452
4453 IF l_call_second_time THEN
4454 PA_SCHEDULE_UTILS.log_message(1,'Calling change_work_pattern second time');
4455 PA_SCHEDULE_UTILS.log_message(1,'l_ch_work_pattern_st_date2='||l_ch_work_pattern_st_date2);
4456 PA_SCHEDULE_UTILS.log_message(1,'l_ch_work_pattern_end_date2='||l_ch_work_pattern_end_date2);
4457
4458 pa_schedule_pub.change_work_pattern(
4459 p_record_version_number => p_record_version_number ,
4460 p_project_id => p_project_id ,
4461 p_calendar_id => p_calendar_id ,
4462 p_assignment_id => p_assignment_id ,
4463 p_assignment_type => p_assignment_type ,
4464 p_start_date => l_ch_work_pattern_st_date2 ,
4465 p_end_date => l_ch_work_pattern_end_date2 ,
4466 p_monday_hours => l_monday_hours(2) ,
4467 p_tuesday_hours => l_tuesday_hours(2) ,
4468 p_wednesday_hours => l_wednesday_hours(2) ,
4469 p_thursday_hours => l_thursday_hours(2) ,
4470 p_friday_hours => l_friday_hours(2) ,
4471 p_saturday_hours => l_saturday_hours(2) ,
4472 p_sunday_hours => l_sunday_hours(2) ,
4473 p_asgn_start_date => l_new_assgn_start_date ,
4474 p_asgn_end_date => l_new_assgn_end_date ,
4475 p_init_msg_list => FND_API.G_FALSE ,
4476 p_last_row_flag => 'Y' ,
4477 p_generate_timeline_flag => 'N' ,
4478 x_return_status => x_return_status ,
4479 x_msg_count => x_msg_count ,
4480 x_msg_data => x_msg_data) ;
4481
4482 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4483 raise API_ERROR;
4484 END IF;
4485 END IF;--l_call_second_time THEN
4486 END IF; --l_call_change_work_pattern THEN
4487
4488 -- Calling create_timeline is important instead of create_forecast_item as
4489 -- assignment_effort also has to be updated.
4490 -- For performance we can call create_forecast_item, but we need to add/subtract
4491 -- the extra effort. This can be done later.
4492
4493 IF ((l_call_change_work_pattern = true) OR(l_call_change_duration = true)) THEN
4494 PA_TIMELINE_PVT.Create_Timeline(
4495 p_assignment_id => p_assignment_id ,
4496 x_return_status => x_return_status ,
4497 x_msg_count => x_msg_count ,
4498 x_msg_data => x_msg_data) ;
4499
4500 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4501 raise API_ERROR;
4502 END IF;
4503 END IF;
4504
4505 END IF; --l_changes_done THEN
4506
4507 -- The following section should be outside beacuse for the previous assignment
4508 -- there may be changes and some values are there in prev variables.
4509 IF p_prev_call_timeline_st_date IS NOT NULL AND p_prev_call_timeline_st_date < NVL(x_call_timeline_st_date, p_prev_call_timeline_st_date+1) THEN
4510 x_call_timeline_st_date := p_prev_call_timeline_st_date;
4511 END IF;
4512
4513 IF p_prev_call_timeline_end_date IS NOT NULL AND p_prev_call_timeline_end_date > NVL(x_call_timeline_end_date, p_prev_call_timeline_end_date-1) THEN
4514 x_call_timeline_end_date := p_prev_call_timeline_end_date;
4515 END IF;
4516
4517 PA_SCHEDULE_UTILS.log_message(1,'End of change_work_pattern_duration');
4518
4519 -- Note : The calling environment should call the following API if x_call_timeline_st_date and x_call_timeline_end_date is not null
4520 -- PA_FORECASTITEM_PVT.Create_Forecast_Item (
4521 -- p_resource_id => p_resource_id,
4522 -- p_start_date => x_call_timeline_st_date,
4523 -- p_end_date => x_call_timeline_end_date,
4524 -- p_process_mode => 'GENERATE',
4525 -- x_return_status => x_return_status,
4526 -- x_msg_count => x_msg_count,
4527 -- x_msg_data => x_msg_data);
4528
4529
4530
4531 EXCEPTION
4532 WHEN FND_API.G_EXC_ERROR THEN -- Added for Bug 3235656
4533 x_return_status := FND_API.G_RET_STS_ERROR;
4534 x_msg_count := FND_MSG_PUB.Count_Msg;
4535
4536 -- 4537865 : RESET other out params also.
4537 x_call_timeline_st_date := NULL ;
4538 x_call_timeline_end_date := NULL ;
4539
4540 IF x_msg_count = 1 THEN
4541 pa_interface_utils_pub.get_messages
4542 (p_encoded => FND_API.G_TRUE,
4543 p_msg_index => 1,
4544 p_data => x_msg_data,
4545 p_msg_index_out => l_msg_index_out );
4546 END IF;
4547 WHEN API_ERROR THEN
4548 PA_SCHEDULE_UTILS.log_message(1,'User Defined Exception in change_work_pattern_duration API ..');
4549 x_return_status := 'E';
4550 IF x_msg_count = 0 THEN
4551 x_msg_count := 1;
4552 x_msg_data := 'User Defined Exception in change_work_pattern_duration API ..';
4553 END IF;
4554
4555 -- 4537865 : RESET other out params also.
4556 x_call_timeline_st_date := NULL ;
4557 x_call_timeline_end_date := NULL ;
4558
4559 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_SCHEDULE_PUB',
4560 p_procedure_name => 'change_work_pattern_duration');
4561 IF x_msg_count = 1 THEN
4562 pa_interface_utils_pub.get_messages
4563 (p_encoded => FND_API.G_TRUE,
4564 p_msg_index => 1,
4565 p_msg_count => x_msg_count,
4566 p_msg_data => x_msg_data,
4567 p_data => l_data, -- 4537865
4568 p_msg_index_out => l_msg_index_out );
4569 x_msg_data := l_data ; -- 4537865
4570 END IF;
4571
4572 WHEN OTHERS THEN
4573 PA_SCHEDULE_UTILS.log_message(1,'ERROR in change_work_pattern_duration API ..'|| sqlerrm);
4574 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4575 x_msg_count := 1;
4576 x_msg_data := substrb(SQLERRM,1,240); -- 4537865 : Chnaged substr to substrb
4577 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_SCHEDULE_PUB',
4578 p_procedure_name => 'change_work_pattern_duration');
4579
4580 -- 4537865 : RESET other out params also.
4581 x_call_timeline_st_date := NULL ;
4582 x_call_timeline_end_date := NULL ;
4583
4584 IF x_msg_count = 1 THEN
4585 pa_interface_utils_pub.get_messages
4586 (p_encoded => FND_API.G_TRUE,
4587 p_msg_index => 1,
4588 p_msg_count => x_msg_count,
4589 p_msg_data => x_msg_data,
4590 p_data => l_data, -- 4537865
4591 p_msg_index_out => l_msg_index_out );
4592 x_msg_data := l_data ; -- 4537865
4593 END IF;
4594 RAISE;
4595 END change_work_pattern_duration;
4596
4597 -- Procedure : populate_work_pattern_table
4598 -- Purpose : This procedure is called from self service for populating the global temp table
4599 -- : pa_work_pattern_temp_table for the given assignment start date and assignment
4600 -- : end date. The data will be populated for 14 days starting with Global week start day
4601 -- : <= p_display_start_date. p_status_code is optional, if it is not given then it will
4602 -- : fetch all the assignments irrespective of the assignment schedule status.
4603 -- : Finally it returns the actual start date depending on the global week start date
4604 -- Parameters :
4605 --
4606
4607 PROCEDURE Populate_work_pattern_table (
4608 p_resource_id_tbl IN SYSTEM.PA_NUM_TBL_TYPE ,
4609 p_assgn_range_start_date IN DATE := NULL ,
4610 p_assgn_range_end_date IN DATE := NULL ,
4611 p_display_start_date IN DATE ,
4612 p_status_code IN VARCHAR2 := NULL ,
4613 p_delete_flag IN VARCHAR2 := 'Y' ,
4614 x_show_start_date OUT NOCOPY DATE , --File.Sql.39 bug 4440895
4615 x_return_status OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
4616 x_msg_count OUT NOCOPY NUMBER , --File.Sql.39 bug 4440895
4617 x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
4618 AS
4619
4620 l_work_pattern_table WORK_PATTERN_TAB_TYPE;
4621 l_resource_id_tbl PA_PLSQL_DATATYPES.IdTabTyp;
4622 l_actual_display_start_date DATE;
4623 l_count NUMBER;
4624 l_where_to_place_counter NUMBER;
4625 l_current_date DATE;
4626 l_qty NUMBER;
4627 l_global_week_start_day NUMBER;
4628 l_global_week_start_day_new NUMBER; /* Added for Bug 5622389 */
4629 l_msg_index_out NUMBER;
4630 l_capacity_label VARCHAR2(80);
4631 l_availability_label VARCHAR2(80);
4632 l_display_start_day NUMBER; --Added for the bug 3648827
4633
4634 l_data varchar2(2000) ; -- 4537865
4635 l_counter_mod NUMBER ; /* Added for Bug 6176678 */
4636 -- This cursor fetches all the assignments for the given filer conditions
4637
4638 CURSOR C_ASSIGNMENTS(l_res_id number) IS
4639 SELECT
4640 project_id,
4641 project_name,
4642 assignment_name,
4643 start_date,
4644 end_date,
4645 status_name,
4646 assignment_id,
4647 resource_id,
4648 status_code,
4649 record_version_number,
4650 assignment_type,
4651 calendar_id,
4652 calendar_type,
4653 project_role_name,
4654 apprvl_status_name,
4655 assignment_effort,
4656 assignment_duration,
4657 project_system_status_code,
4658 --decode(decode(assignment_type, 'STAFFED_ASSIGNMENT', pa_security_pvt.check_user_privilege
4659 --('PA_ASN_SCHEDULE_ED', 'PA_PROJECTS', project_id), 'STAFFED_ADMIN_ASSIGNMENT',
4660 -- pa_security_pvt.check_user_privilege('PA_ADM_ASN_SCHEDULE_ED', 'PA_PROJECTS',project_id)),'Y',1,0) read_only_flag
4661 -- 1 read_only_flag -- Here we are selecting read_only_flag as 0, actual value will be poulated Java side bcoz it does caching
4662 DECODE(mass_wf_in_progress_flag, 'Y', 1,
4663 DECODE(pending_approval_flag, 'Y', 1,
4664 DECODE(apprvl_status_code, 'ASGMT_APPRVL_CANCELED', 1, -- Bug 3235731
4665 DECODE(status_code, null, 0, -- 3235675 This is needed as is_asgmt_allow_stus_ctl_check returns N if status_code is null
4666 DECODE(pa_assignment_utils.is_asgmt_allow_stus_ctl_check(status_code, project_id, 'N'), 'N', 1, 0))))) read_only_flag
4667 FROM pa_project_assignments_v asgn
4668 WHERE asgn.resource_id = l_res_id
4669 AND (
4670 ((p_assgn_range_start_date IS NOT NULL AND p_assgn_range_end_date IS NOT NULL)
4671 AND
4672 (((asgn.start_date between p_assgn_range_start_date AND p_assgn_range_end_date)OR(asgn.end_date between p_assgn_range_start_date AND p_assgn_range_end_date))
4673 OR
4674 ((p_assgn_range_start_date between asgn.start_date AND asgn.end_date)OR(p_assgn_range_end_date between asgn.start_date AND asgn.end_date))
4675 )
4676 )
4677 OR -- Get all assignments excpet those who are end dated before p_assgn_range_start_date
4678 ( p_assgn_range_start_date IS NOT NULL AND p_assgn_range_end_date IS NULL AND asgn.end_date >= p_assgn_range_start_date
4679 )
4680 OR -- Get all assignments excpet those who are started after p_assgn_range_end_date
4681 ( p_assgn_range_start_date IS NULL AND p_assgn_range_end_date IS NOT NULL AND asgn.start_date <= p_assgn_range_end_date
4682 )
4683 )
4684 --AND asgn.status_code=nvl(p_status_code, asgn.status_code) 3235675 This is not needed, Also this is incorrect if status_code is null
4685 AND 'STAFFED_ASGMT_CANCEL' <> nvl(project_system_status_code, 'XYZ') -- Bug 3235731
4686 ORDER BY resource_id, assignment_id; -- This is very important. Logic is woven depending on this order
4687
4688
4689 -- This cursor fetches all the forecast_items for the resource and assignments for the given filer conditions
4690 -- First part before UNION is for resource capacity and next part is for assignment's forecast items
4691
4692 CURSOR c_quantity_cursor(l_res_id number) IS
4693 SELECT
4694 item_date,
4695 capacity_quantity quantity,
4696 --capacity_quantity-(decode(availability_flag,
4697 -- 'Y', decode(sign(capacity_quantity-availability_quantity), 1, 0, availability_quantity),
4698 -- 'N', decode(sign(capacity_quantity-overcommitment_quantity), 1, 0, overcommitment_quantity))) quantity,
4699 resource_id,
4700 forecast_item_type,
4701 -1 assignment_id
4702 FROM pa_forecast_items
4703 WHERE resource_id = l_res_id
4704 AND forecast_item_type = 'U'
4705 AND item_date between l_actual_display_start_date and l_actual_display_start_date+14
4706 AND delete_flag = 'N'
4707 UNION ALL
4708 SELECT
4709 fi.item_date,
4710 fi.item_quantity quantity,
4711 fi.resource_id,
4712 fi.forecast_item_type,
4713 asgn.assignment_id
4714 FROM pa_project_assignments asgn,
4715 pa_forecast_items fi
4716 WHERE asgn.resource_id = l_res_id
4717 AND fi.resource_id = l_res_id
4718 AND fi.delete_flag = 'N'
4719 AND fi.item_date between l_actual_display_start_date and l_actual_display_start_date+13
4720 AND fi.forecast_item_type = 'A'
4721 AND fi.assignment_id = asgn.assignment_id
4722 AND (
4723 ((p_assgn_range_start_date IS NOT NULL AND p_assgn_range_end_date IS NOT NULL)
4724 AND
4725 (((asgn.start_date between p_assgn_range_start_date AND p_assgn_range_end_date)OR(asgn.end_date between p_assgn_range_start_date AND p_assgn_range_end_date))
4726 OR
4727 ((p_assgn_range_start_date between asgn.start_date AND asgn.end_date)OR(p_assgn_range_end_date between asgn.start_date AND asgn.end_date))
4728 )
4729 )
4730 OR -- Get all assignments excpet those who are end dated before p_assgn_range_start_date
4731 ( p_assgn_range_start_date IS NOT NULL AND p_assgn_range_end_date IS NULL AND asgn.end_date >= p_assgn_range_start_date
4732 )
4733 OR -- Get all assignments excpet those who are started after p_assgn_range_end_date
4734 ( p_assgn_range_start_date IS NULL AND p_assgn_range_end_date IS NOT NULL AND asgn.start_date <= p_assgn_range_end_date
4735 )
4736 )
4737 --AND asgn.status_code=nvl(p_status_code, asgn.status_code) 3235675 This is not needed, Also this is incorrect if status_code is null
4738 ORDER BY resource_id, assignment_id, item_date, forecast_item_type desc;
4739
4740 BEGIN
4741 FND_MSG_PUB.initialize;
4742 PA_SCHEDULE_UTILS.log_message(1,'Start of the Populate_work_pattern_table API ... ');
4743 PA_SCHEDULE_UTILS.log_message(1,'Parameters ... ');
4744 PA_SCHEDULE_UTILS.log_message(1,'p_assgn_range_start_date='||p_assgn_range_start_date);
4745 PA_SCHEDULE_UTILS.log_message(1,'p_assgn_range_end_date='||p_assgn_range_end_date);
4746 PA_SCHEDULE_UTILS.log_message(1,'p_display_start_date='||p_display_start_date);
4747 PA_SCHEDULE_UTILS.log_message(1,'p_status_code='||p_status_code);
4748 PA_SCHEDULE_UTILS.log_message(1,'p_delete_flag='||p_delete_flag);
4749 PA_SCHEDULE_UTILS.log_message(1,'p_resource_id_tbl.count='||p_resource_id_tbl.count);
4750
4751
4752 x_return_status := FND_API.G_RET_STS_SUCCESS;
4753 l_global_week_start_day := fnd_profile.value_specific('PA_GLOBAL_WEEK_START_DAY');
4754
4755 /* Code added for Bug 5622389 */
4756 /* To incorporate the difference between PA weekday numbers and */
4757 /* session parameter dependent weekday numbers.*/
4758 Select (to_number(to_char((to_date('01-01-1950','dd-mm-yyyy')+(l_global_week_start_day - 1)),'D')))
4759 into l_global_week_start_day_new
4760 from dual;
4761 /* Code ends for Bug 5622389 */
4762
4763 -- Get the next_day-7 for the given date
4764
4765 BEGIN
4766 /*Commented for the bug 3648827
4767 SELECT next_day(p_display_start_date,decode(l_global_week_start_day,1,'SUNDAY',2,'MONDAY',3,'TUESDAY',4,'WEDNESDAY',5,'THURSDAY',6,'FRIDAY',7,'SATURDAY'))-7
4768 INTO l_actual_display_start_date
4769 FROM dual;*/
4770 /*Added the below code for bug 3648827*/
4771 SELECT to_char(p_display_start_date,'D') INTO l_display_start_day FROM dual;
4772 END;
4773
4774 /*Added the code for the bug 3648827*/
4775 /* Changed l_global_week_start_day to l_global_week_start_day_new for Bug 5622389*/
4776 IF l_global_week_start_day_new > l_display_start_day THEN
4777 l_actual_display_start_date := p_display_start_date - 7 + l_global_week_start_day_new - l_display_start_day;
4778 ELSE
4779 l_actual_display_start_date := p_display_start_date + l_global_week_start_day_new - l_display_start_day ;
4780 END IF;
4781 --IF l_global_week_start_day > l_display_start_day THEN
4782 -- l_actual_display_start_date := p_display_start_date - 7 + l_global_week_start_day - l_display_start_day;
4783 --ELSE
4784 -- l_actual_display_start_date := p_display_start_date + l_global_week_start_day - l_display_start_day ;
4785 --END IF;
4786 /*Commented for the bug 3648827
4787 IF ((p_display_start_date - l_actual_display_start_date)=7) THEN
4788 -- It means already the given date is falling on right global start week day
4789 l_actual_display_start_date := p_display_start_date;
4790 END IF;
4791 */
4792
4793 x_show_start_date := l_actual_display_start_date;
4794
4795 IF p_delete_flag = 'Y' then
4796 DELETE FROM pa_work_pattern_temp_table;
4797 END IF;
4798
4799 --If more than 25 resources than raise error. In phase2 we plan to have this for multiple resources
4800 l_count := p_resource_id_tbl.COUNT;
4801 IF l_count > 25 THEN
4802 null;
4803 END IF;
4804
4805 IF l_count > 0 THEN
4806 FOR i IN p_resource_id_tbl.FIRST .. p_resource_id_tbl.LAST LOOP -- 25 is the limit, later for multiple resources we can keep this in loop
4807 l_resource_id_tbl(i) := p_resource_id_tbl(i);
4808 END LOOP;
4809 END IF;
4810
4811 -- First it makes the plsql table l_work_pattern_table with two rows capacity and availbility/overcommitment
4812 -- These two row's qty fields are initialized with 0 initially in the Initialization part of the code.
4813 -- Then it creates rows for all the assignments for the given filter conditions. It initializes these
4814 -- assignment row's qty fields with 0 or null depending on whether corresponding date is falling in assignment
4815 -- date range or not.
4816 -- After the initialization part, it fethes the forecast items and then loops thru this plsql table
4817 -- and populate the qty fields with respective capacity_quantity(for Capacity row) or item_quantity(for assignment rows).
4818
4819 -- Initialization Part Begin
4820 PA_SCHEDULE_UTILS.log_message(1,'Initialization Begin');
4821
4822 BEGIN
4823 SELECT meaning into l_capacity_label from pa_lookups where lookup_type='PA_CAPC_AVL_LABELS' and lookup_code ='CAPACITY';
4824 SELECT meaning into l_availability_label from pa_lookups where lookup_type='PA_CAPC_AVL_LABELS' and lookup_code ='AVAILABILITY';
4825 END;
4826
4827 l_where_to_place_counter := 0;
4828 l_current_date := l_actual_display_start_date;
4829 FOR i IN l_resource_id_tbl.FIRST .. l_resource_id_tbl.LAST LOOP
4830 -- Initialize the First two rows with 0. These two rows are for Capacity and Availability/Overcommitment
4831 FOR j in 1 .. 2 LOOP
4832 l_current_date := l_actual_display_start_date;
4833 l_where_to_place_counter := l_where_to_place_counter+1;
4834 l_work_pattern_table(l_where_to_place_counter).l_resource_id := l_resource_id_tbl(i);
4835 IF j = 1 THEN
4836 l_work_pattern_table(l_where_to_place_counter).l_assignment_id := -98;
4837 ELSE
4838 l_work_pattern_table(l_where_to_place_counter).l_assignment_id := -99;
4839 END IF;
4840 l_work_pattern_table(l_where_to_place_counter).l_project_role_name := null;
4841 l_work_pattern_table(l_where_to_place_counter).l_project_id := null;
4842 l_work_pattern_table(l_where_to_place_counter).l_status_name := null;
4843 l_work_pattern_table(l_where_to_place_counter).l_read_only_flag := 1;
4844 IF j = 1 THEN
4845 l_work_pattern_table(l_where_to_place_counter).l_assignment_name := l_capacity_label;
4846 ELSE
4847 l_work_pattern_table(l_where_to_place_counter).l_assignment_name := l_availability_label;
4848 END IF;
4849 l_work_pattern_table(l_where_to_place_counter).l_day1 := l_current_date;
4850 l_work_pattern_table(l_where_to_place_counter).l_qty1 := 0;
4851 l_current_date := l_current_date+1;
4852 l_work_pattern_table(l_where_to_place_counter).l_day2 := l_current_date;
4853 l_work_pattern_table(l_where_to_place_counter).l_qty2 := 0;
4854 l_current_date := l_current_date+1;
4855 l_work_pattern_table(l_where_to_place_counter).l_day3 := l_current_date;
4856 l_work_pattern_table(l_where_to_place_counter).l_qty3 := 0;
4857 l_current_date := l_current_date+1;
4858 l_work_pattern_table(l_where_to_place_counter).l_day4 := l_current_date;
4859 l_work_pattern_table(l_where_to_place_counter).l_qty4 := 0;
4860 l_current_date := l_current_date+1;
4861 l_work_pattern_table(l_where_to_place_counter).l_day5 := l_current_date;
4862 l_work_pattern_table(l_where_to_place_counter).l_qty5 := 0;
4863 l_current_date := l_current_date+1;
4864 l_work_pattern_table(l_where_to_place_counter).l_day6 := l_current_date;
4865 l_work_pattern_table(l_where_to_place_counter).l_qty6 := 0;
4866 l_current_date := l_current_date+1;
4867 l_work_pattern_table(l_where_to_place_counter).l_day7 := l_current_date;
4868 l_work_pattern_table(l_where_to_place_counter).l_qty7 := 0;
4869 l_current_date := l_current_date+1;
4870 l_work_pattern_table(l_where_to_place_counter).l_day8 := l_current_date;
4871 l_work_pattern_table(l_where_to_place_counter).l_qty8 := 0;
4872 l_current_date := l_current_date+1;
4873 l_work_pattern_table(l_where_to_place_counter).l_day9 := l_current_date;
4874 l_work_pattern_table(l_where_to_place_counter).l_qty9 := 0;
4875 l_current_date := l_current_date+1;
4876 l_work_pattern_table(l_where_to_place_counter).l_day10 := l_current_date;
4877 l_work_pattern_table(l_where_to_place_counter).l_qty10 := 0;
4878 l_current_date := l_current_date+1;
4879 l_work_pattern_table(l_where_to_place_counter).l_day11 := l_current_date;
4880 l_work_pattern_table(l_where_to_place_counter).l_qty11 := 0;
4881 l_current_date := l_current_date+1;
4882 l_work_pattern_table(l_where_to_place_counter).l_day12 := l_current_date;
4883 l_work_pattern_table(l_where_to_place_counter).l_qty12 := 0;
4884 l_current_date := l_current_date+1;
4885 l_work_pattern_table(l_where_to_place_counter).l_day13 := l_current_date;
4886 l_work_pattern_table(l_where_to_place_counter).l_qty13 := 0;
4887 l_current_date := l_current_date+1;
4888 l_work_pattern_table(l_where_to_place_counter).l_day14 := l_current_date;
4889 l_work_pattern_table(l_where_to_place_counter).l_qty14 := 0;
4890 l_work_pattern_table(l_where_to_place_counter).l_row_type_code := j ; --1:Capacity, 2:Availability/Overcommitment
4891 END LOOP; -- j in 1..2
4892
4893 -- Initialize the next rows with all the assignments with given filter condition
4894
4895 FOR l_asgn IN c_assignments(l_resource_id_tbl(i)) LOOP
4896 /* Added for Bug 6176678 */
4897 /* Now on, if there are any assignments to be displayed, then we will again set both
4898 the (10K+1)th and (10K+2)th rows for Capacity and Availability/Overcommitment
4899 This value 10 is the 'Records Displayed' property of WorkPatternTable item in
4900 WeeklyScheduleRN.xml that we are using to display the records
4901 */
4902
4903 SELECT MOD(l_where_to_place_counter,10)
4904 INTO l_counter_mod
4905 FROM dual;
4906
4907 IF (l_counter_mod = 0 ) THEN
4908
4909 FOR j in 1 .. 2 LOOP
4910 l_current_date := l_actual_display_start_date;
4911 l_where_to_place_counter := l_where_to_place_counter+1;
4912 l_work_pattern_table(l_where_to_place_counter).l_resource_id :=l_resource_id_tbl(i);
4913 IF j = 1 THEN
4914 l_work_pattern_table(l_where_to_place_counter).l_assignment_id := -98;
4915 ELSE
4916 l_work_pattern_table(l_where_to_place_counter).l_assignment_id := -99;
4917 END IF;
4918 l_work_pattern_table(l_where_to_place_counter).l_project_role_name := null;
4919 l_work_pattern_table(l_where_to_place_counter).l_project_id := null;
4920 l_work_pattern_table(l_where_to_place_counter).l_status_name := null;
4921 l_work_pattern_table(l_where_to_place_counter).l_read_only_flag := 1;
4922 IF j = 1 THEN
4923 l_work_pattern_table(l_where_to_place_counter).l_assignment_name := l_capacity_label;
4924 ELSE
4925 l_work_pattern_table(l_where_to_place_counter).l_assignment_name := l_availability_label;
4926 END IF;
4927 l_work_pattern_table(l_where_to_place_counter).l_day1 := l_current_date;
4928 l_work_pattern_table(l_where_to_place_counter).l_qty1 := 0;
4929 l_current_date := l_current_date+1;
4930 l_work_pattern_table(l_where_to_place_counter).l_day2 := l_current_date;
4931 l_work_pattern_table(l_where_to_place_counter).l_qty2 := 0;
4932 l_current_date := l_current_date+1;
4933 l_work_pattern_table(l_where_to_place_counter).l_day3 := l_current_date;
4934 l_work_pattern_table(l_where_to_place_counter).l_qty3 := 0;
4935 l_current_date := l_current_date+1;
4936 l_work_pattern_table(l_where_to_place_counter).l_day4 := l_current_date;
4937 l_work_pattern_table(l_where_to_place_counter).l_qty4 := 0;
4938 l_current_date := l_current_date+1;
4939 l_work_pattern_table(l_where_to_place_counter).l_day5 := l_current_date;
4940 l_work_pattern_table(l_where_to_place_counter).l_qty5 := 0;
4941 l_current_date := l_current_date+1;
4942 l_work_pattern_table(l_where_to_place_counter).l_day6 := l_current_date;
4943 l_work_pattern_table(l_where_to_place_counter).l_qty6 := 0;
4944 l_current_date := l_current_date+1;
4945 l_work_pattern_table(l_where_to_place_counter).l_day7 := l_current_date;
4946 l_work_pattern_table(l_where_to_place_counter).l_qty7 := 0;
4947 l_current_date := l_current_date+1;
4948 l_work_pattern_table(l_where_to_place_counter).l_day8 := l_current_date;
4949 l_work_pattern_table(l_where_to_place_counter).l_qty8 := 0;
4950 l_current_date := l_current_date+1;
4951 l_work_pattern_table(l_where_to_place_counter).l_day9 := l_current_date;
4952 l_work_pattern_table(l_where_to_place_counter).l_qty9 := 0;
4953 l_current_date := l_current_date+1;
4954 l_work_pattern_table(l_where_to_place_counter).l_day10 := l_current_date;
4955 l_work_pattern_table(l_where_to_place_counter).l_qty10 := 0;
4956 l_current_date := l_current_date+1;
4957 l_work_pattern_table(l_where_to_place_counter).l_day11 := l_current_date;
4958 l_work_pattern_table(l_where_to_place_counter).l_qty11 := 0;
4959 l_current_date := l_current_date+1;
4960 l_work_pattern_table(l_where_to_place_counter).l_day12 := l_current_date;
4961 l_work_pattern_table(l_where_to_place_counter).l_qty12 := 0;
4962 l_current_date := l_current_date+1;
4963 l_work_pattern_table(l_where_to_place_counter).l_day13 := l_current_date;
4964 l_work_pattern_table(l_where_to_place_counter).l_qty13 := 0;
4965 l_current_date := l_current_date+1;
4966 l_work_pattern_table(l_where_to_place_counter).l_day14 := l_current_date;
4967 l_work_pattern_table(l_where_to_place_counter).l_qty14 := 0;
4968 l_work_pattern_table(l_where_to_place_counter).l_row_type_code := j ; --1:Capacity, 2:Availability/Overcommitment
4969 END LOOP; -- j in 1..2
4970
4971 END IF ; --IF (l_counter_mod = 0 ) THEN
4972 /* Changes end for Bug 6176678 */
4973
4974 l_current_date := l_actual_display_start_date;
4975 l_where_to_place_counter := l_where_to_place_counter+1;
4976 l_work_pattern_table(l_where_to_place_counter).l_resource_id := l_asgn.resource_id;
4977 l_work_pattern_table(l_where_to_place_counter).l_assignment_id := l_asgn.assignment_id;
4978 l_work_pattern_table(l_where_to_place_counter).l_project_id := l_asgn.project_id;
4979 l_work_pattern_table(l_where_to_place_counter).l_project_name := l_asgn.project_name;
4980 l_work_pattern_table(l_where_to_place_counter).l_assignment_name := l_asgn.assignment_name;
4981 l_work_pattern_table(l_where_to_place_counter).l_start_date := l_asgn.start_date;
4982 l_work_pattern_table(l_where_to_place_counter).l_end_date := l_asgn.end_date;
4983 l_work_pattern_table(l_where_to_place_counter).l_status_name := l_asgn.status_name;
4984 l_work_pattern_table(l_where_to_place_counter).l_status_code := l_asgn.status_code;
4985 l_work_pattern_table(l_where_to_place_counter).l_record_version_number := l_asgn.record_version_number;
4986 l_work_pattern_table(l_where_to_place_counter).l_assignment_type := l_asgn.assignment_type;
4987 l_work_pattern_table(l_where_to_place_counter).l_calendar_id := l_asgn.calendar_id;
4988 l_work_pattern_table(l_where_to_place_counter).l_calendar_type := l_asgn.calendar_type;
4989 l_work_pattern_table(l_where_to_place_counter).l_project_role_name := l_asgn.project_role_name;
4990 l_work_pattern_table(l_where_to_place_counter).l_apprvl_status_name := l_asgn.apprvl_status_name;
4991 l_work_pattern_table(l_where_to_place_counter).l_assignment_effort := l_asgn.assignment_effort;
4992 l_work_pattern_table(l_where_to_place_counter).l_assignment_duration := l_asgn.assignment_duration;
4993 l_work_pattern_table(l_where_to_place_counter).l_project_system_status_code := l_asgn.project_system_status_code;
4994 l_work_pattern_table(l_where_to_place_counter).l_read_only_flag := l_asgn.read_only_flag;
4995 l_work_pattern_table(l_where_to_place_counter).l_day1 := l_current_date;
4996
4997 -- If l_current_date goes outside the assignment date ranges then the qty field should be bull
4998
4999 IF l_current_date > l_asgn.END_DATE OR l_current_date < l_asgn.START_DATE THEN
5000 l_qty:=null;
5001 ELSE
5002 l_qty:=0;
5003 END IF;
5004 l_work_pattern_table(l_where_to_place_counter).l_qty1 := l_qty;
5005 l_current_date := l_current_date+1;
5006 l_work_pattern_table(l_where_to_place_counter).l_day2 := l_current_date;
5007 IF l_current_date > l_asgn.END_DATE OR l_current_date < l_asgn.START_DATE THEN
5008 l_qty:=null;
5009 ELSE
5010 l_qty:=0;
5011 END IF;
5012 l_work_pattern_table(l_where_to_place_counter).l_qty2 := l_qty;
5013 l_current_date := l_current_date+1;
5014 l_work_pattern_table(l_where_to_place_counter).l_day3 := l_current_date;
5015 IF l_current_date > l_asgn.END_DATE OR l_current_date < l_asgn.START_DATE THEN
5016 l_qty:=null;
5017 ELSE
5018 l_qty:=0;
5019 END IF;
5020 l_work_pattern_table(l_where_to_place_counter).l_qty3 := l_qty;
5021 l_current_date := l_current_date+1;
5022 l_work_pattern_table(l_where_to_place_counter).l_day4 := l_current_date;
5023 IF l_current_date > l_asgn.END_DATE OR l_current_date < l_asgn.START_DATE THEN
5024 l_qty:=null;
5025 ELSE
5026 l_qty:=0;
5027 END IF;
5028 l_work_pattern_table(l_where_to_place_counter).l_qty4 := l_qty;
5029 l_current_date := l_current_date+1;
5030 l_work_pattern_table(l_where_to_place_counter).l_day5 := l_current_date;
5031 IF l_current_date > l_asgn.END_DATE OR l_current_date < l_asgn.START_DATE THEN
5032 l_qty:=null;
5033 ELSE
5034 l_qty:=0;
5035 END IF;
5036 l_work_pattern_table(l_where_to_place_counter).l_qty5 := l_qty;
5037 l_current_date := l_current_date+1;
5038 l_work_pattern_table(l_where_to_place_counter).l_day6 := l_current_date;
5039 IF l_current_date > l_asgn.END_DATE OR l_current_date < l_asgn.START_DATE THEN
5040 l_qty:=null;
5041 ELSE
5042 l_qty:=0;
5043 END IF;
5044 l_work_pattern_table(l_where_to_place_counter).l_qty6 := l_qty;
5045 l_current_date := l_current_date+1;
5046 l_work_pattern_table(l_where_to_place_counter).l_day7 := l_current_date;
5047 IF l_current_date > l_asgn.END_DATE OR l_current_date < l_asgn.START_DATE THEN
5048 l_qty:=null;
5049 ELSE
5050 l_qty:=0;
5051 END IF;
5052 l_work_pattern_table(l_where_to_place_counter).l_qty7 := l_qty;
5053 l_current_date := l_current_date+1;
5054 l_work_pattern_table(l_where_to_place_counter).l_day8 := l_current_date;
5055 IF l_current_date > l_asgn.END_DATE OR l_current_date < l_asgn.START_DATE THEN
5056 l_qty:=null;
5057 ELSE
5058 l_qty:=0;
5059 END IF;
5060 l_work_pattern_table(l_where_to_place_counter).l_qty8 := l_qty;
5061 l_current_date := l_current_date+1;
5062 l_work_pattern_table(l_where_to_place_counter).l_day9 := l_current_date;
5063 IF l_current_date > l_asgn.END_DATE OR l_current_date < l_asgn.START_DATE THEN
5064 l_qty:=null;
5065 ELSE
5066 l_qty:=0;
5067 END IF;
5068 l_work_pattern_table(l_where_to_place_counter).l_qty9 := l_qty;
5069 l_current_date := l_current_date+1;
5070 l_work_pattern_table(l_where_to_place_counter).l_day10 := l_current_date;
5071 IF l_current_date > l_asgn.END_DATE OR l_current_date < l_asgn.START_DATE THEN
5072 l_qty:=null;
5073 ELSE
5074 l_qty:=0;
5075 END IF;
5076 l_work_pattern_table(l_where_to_place_counter).l_qty10 := l_qty;
5077 l_current_date := l_current_date+1;
5078 l_work_pattern_table(l_where_to_place_counter).l_day11 := l_current_date;
5079 IF l_current_date > l_asgn.END_DATE OR l_current_date < l_asgn.START_DATE THEN
5080 l_qty:=null;
5081 ELSE
5082 l_qty:=0;
5083 END IF;
5084 l_work_pattern_table(l_where_to_place_counter).l_qty11 := l_qty;
5085 l_current_date := l_current_date+1;
5086 l_work_pattern_table(l_where_to_place_counter).l_day12 := l_current_date;
5087 IF l_current_date > l_asgn.END_DATE OR l_current_date < l_asgn.START_DATE THEN
5088 l_qty:=null;
5089 ELSE
5090 l_qty:=0;
5091 END IF;
5092 l_work_pattern_table(l_where_to_place_counter).l_qty12 := l_qty;
5093 l_current_date := l_current_date+1;
5094 l_work_pattern_table(l_where_to_place_counter).l_day13 := l_current_date;
5095 IF l_current_date > l_asgn.END_DATE OR l_current_date < l_asgn.START_DATE THEN
5096 l_qty:=null;
5097 ELSE
5098 l_qty:=0;
5099 END IF;
5100 l_work_pattern_table(l_where_to_place_counter).l_qty13 := l_qty;
5101 l_current_date := l_current_date+1;
5102 l_work_pattern_table(l_where_to_place_counter).l_day14 := l_current_date;
5103 IF l_current_date > l_asgn.END_DATE OR l_current_date < l_asgn.START_DATE THEN
5104 l_qty:=null;
5105 ELSE
5106 l_qty:=0;
5107 END IF;
5108 l_work_pattern_table(l_where_to_place_counter).l_qty14 := l_qty;
5109 l_work_pattern_table(l_where_to_place_counter).l_row_type_code := 3 ; --Assignments
5110 END LOOP; -- l_asgn IN c_assignments
5111 END LOOP; -- l_resource_id_tbl.FIRST .. l_resource_id_tbl.LAST
5112
5113 -- Initialization Part End
5114 PA_SCHEDULE_UTILS.log_message(1,'Initialization End');
5115
5116 -- Now loop thru the forecast items and populate the corresponding qty fields in
5117 -- l_work_pattern_table
5118
5119 /* Changes for Bug 6176678
5120 Now, for populating the Capacity rows (that is , l_row_type_code = 1),
5121 we will NOT exit after each IF check.
5122 */
5123
5124 FOR i IN l_resource_id_tbl.FIRST .. l_resource_id_tbl.LAST LOOP
5125 FOR l_temp IN c_quantity_cursor(l_resource_id_tbl(i)) LOOP
5126 FOR j IN l_work_pattern_table.FIRST .. l_work_pattern_table.LAST LOOP
5127 IF l_work_pattern_table(j).l_row_type_code = 1 AND l_temp.resource_id = l_work_pattern_table(j).l_resource_id AND l_temp.forecast_item_type='U' THEN
5128 IF l_temp.item_date = l_work_pattern_table(j).l_day1 THEN
5129 l_work_pattern_table(j).l_qty1 := l_temp.quantity;
5130 --exit;
5131 ELSIF l_temp.item_date = l_work_pattern_table(j).l_day2 THEN
5132 l_work_pattern_table(j).l_qty2 := l_temp.quantity;
5133 --exit;
5134 ELSIF l_temp.item_date = l_work_pattern_table(j).l_day3 THEN
5135 l_work_pattern_table(j).l_qty3 := l_temp.quantity;
5136 --exit;
5137 ELSIF l_temp.item_date = l_work_pattern_table(j).l_day4 THEN
5138 l_work_pattern_table(j).l_qty4 := l_temp.quantity;
5139 --exit;
5140 ELSIF l_temp.item_date = l_work_pattern_table(j).l_day5 THEN
5141 l_work_pattern_table(j).l_qty5 := l_temp.quantity;
5142 --exit;
5143 ELSIF l_temp.item_date = l_work_pattern_table(j).l_day6 THEN
5144 l_work_pattern_table(j).l_qty6 := l_temp.quantity;
5145 --exit;
5146 ELSIF l_temp.item_date = l_work_pattern_table(j).l_day7 THEN
5147 l_work_pattern_table(j).l_qty7 := l_temp.quantity;
5148 --exit;
5149 ELSIF l_temp.item_date = l_work_pattern_table(j).l_day8 THEN
5150 l_work_pattern_table(j).l_qty8 := l_temp.quantity;
5151 --exit;
5152 ELSIF l_temp.item_date = l_work_pattern_table(j).l_day9 THEN
5153 l_work_pattern_table(j).l_qty9 := l_temp.quantity;
5154 --exit;
5155 ELSIF l_temp.item_date = l_work_pattern_table(j).l_day10 THEN
5156 l_work_pattern_table(j).l_qty10 := l_temp.quantity;
5157 --exit;
5158 ELSIF l_temp.item_date = l_work_pattern_table(j).l_day11 THEN
5159 l_work_pattern_table(j).l_qty11 := l_temp.quantity;
5160 --exit;
5161 ELSIF l_temp.item_date = l_work_pattern_table(j).l_day12 THEN
5162 l_work_pattern_table(j).l_qty12 := l_temp.quantity;
5163 --exit;
5164 ELSIF l_temp.item_date = l_work_pattern_table(j).l_day13 THEN
5165 l_work_pattern_table(j).l_qty13 := l_temp.quantity;
5166 --exit;
5167 ELSIF l_temp.item_date = l_work_pattern_table(j).l_day14 THEN
5168 l_work_pattern_table(j).l_qty14 := l_temp.quantity;
5169 --exit;
5170 END IF; -- l_temp.item_date = l_work_pattern_table(j).l_day1 THEN
5171 ELSIF l_work_pattern_table(j).l_row_type_code = 3 AND l_temp.resource_id = l_work_pattern_table(j).l_resource_id AND l_temp.assignment_id=l_work_pattern_table(j).l_assignment_id AND l_temp.forecast_item_type='A' THEN
5172 IF l_temp.item_date = l_work_pattern_table(j).l_day1 THEN
5173 l_work_pattern_table(j).l_qty1 := l_temp.quantity;
5174 exit;
5175 ELSIF l_temp.item_date = l_work_pattern_table(j).l_day2 THEN
5176 l_work_pattern_table(j).l_qty2 := l_temp.quantity;
5177 exit;
5178 ELSIF l_temp.item_date = l_work_pattern_table(j).l_day3 THEN
5179 l_work_pattern_table(j).l_qty3 := l_temp.quantity;
5180 exit;
5181 ELSIF l_temp.item_date = l_work_pattern_table(j).l_day4 THEN
5182 l_work_pattern_table(j).l_qty4 := l_temp.quantity;
5183 exit;
5184 ELSIF l_temp.item_date = l_work_pattern_table(j).l_day5 THEN
5185 l_work_pattern_table(j).l_qty5 := l_temp.quantity;
5186 exit;
5187 ELSIF l_temp.item_date = l_work_pattern_table(j).l_day6 THEN
5188 l_work_pattern_table(j).l_qty6 := l_temp.quantity;
5189 exit;
5190 ELSIF l_temp.item_date = l_work_pattern_table(j).l_day7 THEN
5191 l_work_pattern_table(j).l_qty7 := l_temp.quantity;
5192 exit;
5193 ELSIF l_temp.item_date = l_work_pattern_table(j).l_day8 THEN
5194 l_work_pattern_table(j).l_qty8 := l_temp.quantity;
5195 exit;
5196 ELSIF l_temp.item_date = l_work_pattern_table(j).l_day9 THEN
5197 l_work_pattern_table(j).l_qty9 := l_temp.quantity;
5198 exit;
5199 ELSIF l_temp.item_date = l_work_pattern_table(j).l_day10 THEN
5200 l_work_pattern_table(j).l_qty10 := l_temp.quantity;
5201 exit;
5202 ELSIF l_temp.item_date = l_work_pattern_table(j).l_day11 THEN
5203 l_work_pattern_table(j).l_qty11 := l_temp.quantity;
5204 exit;
5205 ELSIF l_temp.item_date = l_work_pattern_table(j).l_day12 THEN
5206 l_work_pattern_table(j).l_qty12 := l_temp.quantity;
5207 exit;
5208 ELSIF l_temp.item_date = l_work_pattern_table(j).l_day13 THEN
5209 l_work_pattern_table(j).l_qty13 := l_temp.quantity;
5210 exit;
5211 ELSIF l_temp.item_date = l_work_pattern_table(j).l_day14 THEN
5212 l_work_pattern_table(j).l_qty14 := l_temp.quantity;
5213 exit;
5214 END IF; -- l_temp.item_date = l_work_pattern_table(j).l_day1 THEN
5215 END IF;--l_work_pattern_table(j).l_row_type_code = 1 AND l_temp.resource_id = l_work_pattern_table(j).l_resource_id AND l_temp.forecast_item_type='U' THEN
5216 END LOOP; -- j IN l_work_pattern_table.FIRST .. l_work_pattern_table.LAST LOOP
5217 END LOOP;-- l_temp IN c_quantity_cursor(l_resource_id_tbl(i)) LOOP
5218 END LOOP;--i IN l_resource_id_tbl.FIRST .. l_resource_id_tbl.LAST LOOP
5219
5220 PA_SCHEDULE_UTILS.log_message(1,'Inserting data in pa_work_pattern_temp_table');
5221
5222 FOR j IN l_work_pattern_table.FIRST .. l_work_pattern_table.LAST LOOP
5223 INSERT INTO pa_work_pattern_temp_table
5224 (
5225 PROJECT_ID ,
5226 PROJECT_NAME,
5227 ASSIGNMENT_NAME,
5228 START_DATE,
5229 END_DATE,
5230 STATUS_NAME,
5231 ASSIGNMENT_ID,
5232 RESOURCE_ID,
5233 STATUS_CODE,
5234 RECORD_VERSION_NUMBER,
5235 ASSIGNMENT_TYPE,
5236 CALENDAR_ID,
5237 CALENDAR_TYPE,
5238 PROJECT_ROLE_NAME,
5239 APPRVL_STATUS_NAME,
5240 ASSIGNMENT_EFFORT,
5241 ASSIGNMENT_DURATION,
5242 PROJECT_SYSTEM_STATUS_CODE,
5243 DAY1,
5244 DAY2,
5245 DAY3,
5246 DAY4,
5247 DAY5,
5248 DAY6,
5249 DAY7,
5250 DAY8,
5251 DAY9,
5252 DAY10,
5253 DAY11,
5254 DAY12,
5255 DAY13,
5256 DAY14,
5257 QTY1,
5258 QTY2,
5259 QTY3,
5260 QTY4,
5261 QTY5,
5262 QTY6,
5263 QTY7,
5264 QTY8,
5265 QTY9,
5266 QTY10,
5267 QTY11,
5268 QTY12,
5269 QTY13,
5270 QTY14,
5271 row_type_code,
5272 read_only_flag)
5273 values
5274 (
5275 l_work_pattern_table(j).l_PROJECT_ID ,
5276 l_work_pattern_table(j).l_PROJECT_NAME,
5277 l_work_pattern_table(j).l_ASSIGNMENT_NAME,
5278 l_work_pattern_table(j).l_START_DATE,
5279 l_work_pattern_table(j).l_END_DATE,
5280 l_work_pattern_table(j).l_STATUS_NAME,
5281 l_work_pattern_table(j).l_ASSIGNMENT_ID,
5282 l_work_pattern_table(j).l_RESOURCE_ID,
5283 l_work_pattern_table(j).l_STATUS_CODE,
5284 l_work_pattern_table(j).l_RECORD_VERSION_NUMBER,
5285 l_work_pattern_table(j).l_ASSIGNMENT_TYPE,
5286 l_work_pattern_table(j).l_CALENDAR_ID,
5287 l_work_pattern_table(j).l_CALENDAR_TYPE,
5288 l_work_pattern_table(j).l_PROJECT_ROLE_NAME,
5289 l_work_pattern_table(j).l_APPRVL_STATUS_NAME,
5290 l_work_pattern_table(j).l_ASSIGNMENT_EFFORT,
5291 l_work_pattern_table(j).l_ASSIGNMENT_DURATION,
5292 l_work_pattern_table(j).l_PROJECT_SYSTEM_STATUS_CODE,
5293 l_work_pattern_table(j).l_DAY1,
5294 l_work_pattern_table(j).l_DAY2,
5295 l_work_pattern_table(j).l_DAY3,
5296 l_work_pattern_table(j).l_DAY4,
5297 l_work_pattern_table(j).l_DAY5,
5298 l_work_pattern_table(j).l_DAY6,
5299 l_work_pattern_table(j).l_DAY7,
5300 l_work_pattern_table(j).l_DAY8,
5301 l_work_pattern_table(j).l_DAY9,
5302 l_work_pattern_table(j).l_DAY10,
5303 l_work_pattern_table(j).l_DAY11,
5304 l_work_pattern_table(j).l_DAY12,
5305 l_work_pattern_table(j).l_DAY13,
5306 l_work_pattern_table(j).l_DAY14,
5307 l_work_pattern_table(j).l_QTY1,
5308 l_work_pattern_table(j).l_QTY2,
5309 l_work_pattern_table(j).l_QTY3,
5310 l_work_pattern_table(j).l_QTY4,
5311 l_work_pattern_table(j).l_QTY5,
5312 l_work_pattern_table(j).l_QTY6,
5313 l_work_pattern_table(j).l_QTY7,
5314 l_work_pattern_table(j).l_QTY8,
5315 l_work_pattern_table(j).l_QTY9,
5316 l_work_pattern_table(j).l_QTY10,
5317 l_work_pattern_table(j).l_QTY11,
5318 l_work_pattern_table(j).l_QTY12,
5319 l_work_pattern_table(j).l_QTY13,
5320 l_work_pattern_table(j).l_QTY14,
5321 l_work_pattern_table(j).l_row_type_code,
5322 l_work_pattern_table(j).l_read_only_flag) ;
5323 END LOOP;
5324
5325 PA_SCHEDULE_UTILS.log_message(1,'End of Populate_work_pattern_table');
5326 EXCEPTION
5327 WHEN OTHERS THEN
5328 PA_SCHEDULE_UTILS.log_message(1,'ERROR in Populate_work_pattern_table API ..'|| sqlerrm);
5329 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5330 x_msg_count := 1;
5331 x_msg_data := substrb(SQLERRM,1,240); -- 4537865
5332 -- RESET x_show_start_date also
5333 x_show_start_date := NULL ;
5334
5335 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_SCHEDULE_PUB',
5336 p_procedure_name => 'Populate_work_pattern_table');
5337 IF x_msg_count = 1 THEN
5338 pa_interface_utils_pub.get_messages
5339 (p_encoded => FND_API.G_TRUE,
5340 p_msg_index => 1,
5341 p_msg_count => x_msg_count,
5342 p_msg_data => x_msg_data,
5343 p_data => l_data, -- 4537865
5344 p_msg_index_out => l_msg_index_out );
5345 x_msg_data := l_data ; -- 4537865
5346 END IF;
5347 RAISE;
5348
5349 END Populate_work_pattern_table;
5350
5351 -- Unilog Enhancement END
5352
5353 END PA_SCHEDULE_PUB;
[Home] [Help]