[Home] [Help]
PACKAGE BODY: APPS.PA_RESOURCE_PVT
Source
1 PACKAGE BODY PA_RESOURCE_PVT AS
2 -- $Header: PARRESVB.pls 120.8.12010000.2 2008/08/18 09:58:32 amehrotr ship $
3 g_profile_id NUMBER := 0;
4 g_person_id NUMBER := 0;
5
6 l_profile varchar2(30) := NVL(FND_PROFILE.value('PA_AVAILABILITY_CAL_PERIOD'), 'DAILY'); /* Added the global profile call for bug 4930256 */
7
8 Procedure Set_No_of_Days
9 (p_no_of_days IN NUMBER)
10 IS
11 BEGIN
12 PA_RESOURCE_PVT.no_of_days := p_no_of_days;
13 END;
14
15 /*-----------------------------------------------------------------+
16 FUNCTION: Get_Start_Date
17 DESCRIPTION: This Function finds the earliest available date for
18 the resource passed in (p_resource_id).
19 Instead of using forecast items table or pa_project_
20 assignments, for PA.K, it uses pa_res_availability
21 table to get available from and available to dates
22 ------------------------------------------------------------------*/
23 FUNCTION Get_Start_Date
24 (p_resource_id IN NUMBER,
25 p_no_of_days IN NUMBER)
26 RETURN DATE
27 IS
28 l_start_date DATE := '';
29 l_end_date DATE := '';
30 l_available_date DATE := '';
31 l_avl_profile VARCHAR2(240) := FND_NUMBER.CANONICAL_TO_NUMBER(fnd_profile.value('PA_RES_CAPACITY_PERCENTAGE'));
32
33 -- new cursor to get available from date using pa_res_availability
34 -- table (PA.K enhancements)
35 CURSOR get_available_from IS
36 SELECT (max(a1.end_date)+1) available_from
37 FROM pa_res_availability a1,
38 pa_res_availability a2
39 WHERE a1.resource_id = p_resource_id
40 and a1.percent < l_avl_profile
41 and a1.record_type = 'C'
42 and a1.start_date < sysdate
43 and a1.resource_id = a2.resource_id
44 and a2.record_type ='C'
45 and a2.percent >= l_avl_profile
46 and sysdate between a2.start_date and a2.end_date
47 UNION
48 SELECT min(a1.start_date) available_from
49 FROM pa_res_availability a1,
50 pa_res_availability a2
51 WHERE a1.resource_id = p_resource_id
52 and a1.percent >= l_avl_profile
53 and a1.record_type = 'C'
54 and a1.start_date >= sysdate
55 and a1.resource_id = a2.resource_id
56 and a2.record_type ='C'
57 and a2.percent < l_avl_profile
58 and sysdate between a2.start_date and a2.end_date
59 UNION
60 SELECT min(a1.start_date) available_from
61 FROM pa_res_availability a1
62 WHERE a1.record_type = 'C'
63 and a1.resource_id = p_resource_id
64 and a1.percent >= l_avl_profile
65 and sysdate < (select min(resource_effective_start_date)
66 from pa_resources_denorm res
67 where a1.resource_id = res.resource_id);
68
69
70 BEGIN
71
72 PA_RESOURCE_PVT.end_date := null;
73
74 /* ----------------------------------------------------------
75 * comment out the following code because no longer using
76 * forecast item and project assignments to get the available
77 * from and to
78
79 SELECT min(item_date)
80 INTO l_available_date
81 FROM pa_forecast_items
82 WHERE resource_id = p_resource_id
83 AND forecast_item_type = 'U'
84 AND delete_flag = 'N'
85 AND availability_flag = 'Y'
86 AND overcommitment_flag = 'N'
87 AND item_date >= trunc(sysdate);
88
89 l_start_date := l_available_date;
90
91 /* To get the available end date, we are checking the earliest
92 * assigment of the resource, and the start date of this
93 * assigment is what we treat as the end date of the resource's
94 * availability */
95
96 BEGIN
97 IF l_start_date is not null THEN
98 select min(start_date)
99 into l_end_date
100 from pa_project_assignments
101 where resource_id = p_resource_id
102 and start_date > l_start_date
103 and status_code in (select project_status_code
104 from pa_project_statuses
105 where project_system_status_code =
106 'STAFFED_ASGMT_CONF');
107
108 IF l_end_date is null THEN
109 -- There is no confirmed assignmented. Current logic is to
110 -- keep the end date null
111 l_end_date := null;
112 END IF;
113
114 IF (l_start_date < trunc(sysdate)) AND (l_end_date < trunc(sysdate)) THEN
115 l_start_date := null;
116 l_end_date := null;
117 END IF;
118 ELSE
119 -- l_start_date is null.
120 l_end_date := null;
121 END IF;
122
123 EXCEPTION
124 WHEN OTHERS THEN
125 PA_RESOURCE_PVT.end_date := null;
126 END;
127
128 ------------------------------------------------- */
129
130 --------------------------------------------------
131 -- new code using pa_res_availability begins here
132 --------------------------------------------------
133 OPEN get_available_from;
134 FETCH get_available_from INTO l_start_date;
135 CLOSE get_available_from;
136
137 IF l_start_date IS NOT NULL THEN
138 l_end_date := PA_RESOURCE_PVT.get_resource_avl_to_date(p_resource_id, l_start_date);
139 ELSE
140 l_end_date := null;
141 END IF;
142
143 PA_RESOURCE_PVT.end_date := l_end_date;
144
145 RETURN l_start_date;
146
147 EXCEPTION
148 WHEN OTHERS THEN
149 PA_RESOURCE_PVT.end_date := null;
150 return null;
151 END;
152
153 FUNCTION Get_End_Date
154 (p_resource_id IN NUMBER)
155 RETURN DATE
156 IS
157 BEGIN
158 RETURN PA_RESOURCE_PVT.end_date;
159 END;
160
161 FUNCTION Get_Start_Date1
162 (p_row_label_id IN NUMBER)
163 RETURN DATE
164 IS
165 l_start_date1 DATE;
166 l_end_date1 DATE;
167 l_scale_type VARCHAR2(30);
168 BEGIN
169 /* Commneted out for 4890553
170 SELECT
171 RSCH.START_DATE
172 , RSCH.END_DATE
173 , RSCH.SCALE_TYPE
174 INTO
175 l_start_date1,
176 l_end_date1,
177 l_scale_type
178 FROM PA_RES_SCH_TIME_CHART RSCH
179 WHERE RSCH.ROW_LABEL_ID = p_ROW_LABEL_ID
180 AND END_DATE >= to_date('2000-10-23', 'YYYY-MM-DD')
181 AND START_DATE <= to_date('2000-11-26', 'YYYY-MM-DD')
182 AND SCALE_TYPE = 'MONTH';*/
183
184 return l_start_date1;
185
186 EXCEPTION
187 WHEN OTHERS THEN
188 PA_RESOURCE_PVT.end_date1 := null;
189 PA_RESOURCE_PVT.scale_type := null;
190 return null;
191 END Get_Start_Date1;
192
193
194 FUNCTION Get_End_Date1
195 RETURN DATE
196 IS
197 BEGIN
198 RETURN PA_RESOURCE_PVT.end_date1;
199 END;
200
201 FUNCTION get_scale_type
202 RETURN VARCHAR2
203 IS
204 BEGIN
205 RETURN PA_RESOURCE_PVT.scale_type;
206 END;
207
208
209 -------------------------------------------------------------------------------+
210 --
211 -- PROCEDURE : Insert_resource_denorm
212 -- DESCRIPTION : This Procedure inserts the record 'p_resource_denorm_rec'
213 -- to the table 'PA_RESOURCES_DENORM' after some appropriate
214 -- validations.
215 --
216 --------------------------------------------------------------------------------+
217 PROCEDURE Insert_resource_denorm
218 ( p_resource_denorm_rec IN Resource_denorm_Rec_type,
219 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
220 x_msg_data OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
221 x_msg_count OUT NOCOPY NUMBER) --File.Sql.39 bug 4440895
222 IS
223 l_resource_denorm_rec PA_RESOURCE_PVT.Resource_denorm_Rec_type;
224 l_resource_id pa_resources_denorm.resource_id%TYPE := NULL;
225 l_return_status VARCHAR2(1);
226 l_err_msg_code fnd_new_messages.message_name%TYPE;
227 l_msg_index_out NUMBER;
228 l_rows_exists VARCHAR2(1);
229 BEGIN
230 -- Initialize the Error Stack
231 PA_DEBUG.init_err_stack('PA_RESOURCE_PVT.Insert_resource_denorm');
232
233 -- Initialize the return status to success
234 x_return_status := FND_API.G_RET_STS_SUCCESS;
235
236 -- Save point in case of Roll back
237 savepoint RES_PVT_INSERT_RES_DENORM;
238
239 -- the input record to the local variable
240 l_resource_denorm_rec := p_resource_denorm_rec;
241
242 --
243 -- check if all the required field are not null. If so, display an error message.
244 --
245 check_required_fields
246 ( p_resource_denorm_rec => l_resource_denorm_rec,
247 x_return_status => l_return_status,
248 x_err_msg_code => l_err_msg_code);
249
250 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
251 pa_utils.add_message
252 ( p_app_short_name => 'PA',
253 p_msg_name => l_err_msg_code);
254
255 RAISE FND_API.G_EXC_ERROR;
256 END IF;
257
258
259 --
260 -- Check if the combination of person_id,job_id are already exist. If it does,
261 -- do not insert and display an error message instead.
262 --
263 -- Bug 4350723 - change to where exists to improve performance.
264
265 SELECT 'Y'
266 INTO l_rows_exists
267 FROM dual
268 WHERE EXISTS (SELECT 'Y'
269 FROM pa_resources_denorm
270 WHERE l_resource_denorm_rec.person_id = person_id
271 AND l_resource_denorm_rec.job_id = job_id);
272
273 IF l_rows_exists = 'Y' THEN
274 pa_utils.add_message
275 ( p_app_short_name => 'PA',
276 p_msg_name => 'PA_PERSON_ID_JOB_ID_EXIST');
277 RAISE FND_API.G_EXC_ERROR;
278 END IF;
279
280 --
281 -- Get resource_id for the person_id
282 --
283 l_resource_id := pa_resource_utils.get_resource_id(l_resource_denorm_rec.person_id);
284
285 IF l_resource_id= -999 OR l_resource_id IS NULL THEN
286 pa_utils.add_message
287 (p_app_short_name => 'PA',
288 p_msg_name => 'PA_NO_RESOURCE_ID_FOUND');
289 RAISE FND_API.G_EXC_ERROR;
290 END IF;
291
292 -- Get the resource's person type from HR
293 --select decode(current_employee_flag, 'Y', 'EMP', 'CWK')
294 --into
295 --from per_all_people_f
296 --where person_id = l_resource_denorm_rec.person_id
297 --and sysdate between effective_start_date and effective_end_date;
298 --
299 -- insert the record to the table 'PA_RESOURCE_DENORM'
300 --
301 INSERT into pa_resources_denorm
302 (
303 PERSON_ID,
304 RESOURCE_ID,
305 RESOURCE_NAME,
306 RESOURCE_TYPE,
307 RESOURCE_ORGANIZATION_ID,
308 RESOURCE_COUNTRY_CODE,
309 RESOURCE_COUNTRY,
310 RESOURCE_REGION,
311 RESOURCE_CITY,
312 JOB_ID,
313 RESOURCE_JOB_LEVEL,
314 RESOURCE_EFFECTIVE_START_DATE,
315 RESOURCE_EFFECTIVE_END_DATE,
316 EMPLOYEE_FLAG,
317 MANAGER_ID,
318 MANAGER_NAME,
319 BILLABLE_FLAG,
320 UTILIZATION_FLAG,
321 SCHEDULABLE_FLAG,
322 RESOURCE_ORG_ID,
323 REQUEST_ID,
324 PROGRAM_APPLICATION_ID,
325 PROGRAM_ID,
326 PROGRAM_UPDATE_DATE,
327 CREATION_DATE,
328 CREATED_BY,
329 LAST_UPDATE_DATE,
330 LAST_UPDATED_BY,
331 LAST_UPDATE_LOGIN
332 -- ,RESOURCE_PERSON_TYPE
333 )
334 VALUES
335 (
336 l_resource_denorm_rec.person_id,
337 l_resource_id,
338 l_resource_denorm_rec.resource_name,
339 l_resource_denorm_rec.resource_type,
340 l_resource_denorm_rec.resource_organization_id,
341 DECODE(l_resource_denorm_rec.resource_country_code, FND_API.G_MISS_CHAR,
342 NULL, l_resource_denorm_rec.resource_country_code),
343 DECODE(l_resource_denorm_rec.resource_country, FND_API.G_MISS_CHAR,
344 NULL, l_resource_denorm_rec.RESOURCE_COUNTRY),
345 DECODE(l_resource_denorm_rec.resource_region, FND_API.G_MISS_CHAR,
346 NULL, l_resource_denorm_rec.resource_region),
347 DECODE(l_resource_denorm_rec.resource_city, FND_API.G_MISS_CHAR,
348 NULL, l_resource_denorm_rec.resource_city),
349 DECODE(l_resource_denorm_rec.job_id, FND_API.G_MISS_NUM,
350 NULL, l_resource_denorm_rec.job_id),
351 DECODE(l_resource_denorm_rec.resource_job_level, FND_API.G_MISS_NUM,
352 NULL, l_resource_denorm_rec.resource_job_level),
353 l_resource_denorm_rec.resource_effective_start_date,
354 DECODE(l_resource_denorm_rec.resource_effective_end_date, FND_API.G_MISS_DATE,
355 NULL, l_resource_denorm_rec.resource_effective_end_date),
356 l_resource_denorm_rec.employee_flag,
357 DECODE(l_resource_denorm_rec.manager_id, FND_API.G_MISS_NUM,
358 NULL, l_resource_denorm_rec.manager_id),
359 DECODE(l_resource_denorm_rec.manager_name, FND_API.G_MISS_CHAR,
360 NULL, l_resource_denorm_rec.manager_name),
361 DECODE(l_resource_denorm_rec.billable_flag, FND_API.G_MISS_CHAR,
362 NULL, l_resource_denorm_rec.billable_flag),
363 DECODE(l_resource_denorm_rec.utilization_flag, FND_API.G_MISS_CHAR,
364 NULL, l_resource_denorm_rec.utilization_flag),
365 DECODE(l_resource_denorm_rec.schedulable_flag, FND_API.G_MISS_CHAR,
366 NULL, l_resource_denorm_rec.schedulable_flag),
367 DECODE(l_resource_denorm_rec.resource_org_id, FND_API.G_MISS_NUM,
368 NULL,l_resource_denorm_rec.resource_org_id),
369 null, -- REQUEST_ID
370 null, -- PROGRAM_APPLICATION_ID
371 null, -- PROGRAM_ID
372 null, -- PROGRAM_UPDATE_DATE
373 sysdate, -- CREATION_DATE
374 fnd_global.user_id, -- CREATED_BY
375 sysdate, -- LAST_UPDATE_DATE
376 fnd_global.user_id, -- LAST_UPDATED_BY
377 fnd_global.login_id -- LAST_UPDATE_LOGIN
378 -- ,l_resource_person_type
379 );
380
381
382 EXCEPTION
383 WHEN FND_API.G_EXC_ERROR THEN
384 ROLLBACK TO RES_PVT_INSERT_RES_DENORM;
385
386 x_return_status := FND_API.G_RET_STS_ERROR;
387
388 -- Reset the error stack when returning to the calling program
389 PA_DEBUG.Reset_Err_Stack;
390
391 -- get the number of messages
392 x_msg_count := FND_MSG_PUB.Count_Msg;
393
394 -- Put a message text from message stack into the x_msg_data if there is only
395 -- single error.
396 IF x_msg_count = 1 THEN
397 pa_interface_utils_pub.get_messages
398 (p_encoded => FND_API.G_TRUE
399 ,p_msg_index => 1
400 ,p_data => x_msg_data
401 ,p_msg_index_out => l_msg_index_out);
402 END IF;
403
404 WHEN OTHERS THEN
405 ROLLBACK TO RES_PVT_INSERT_RES_DENORM;
406
407 -- Set the exception Message and the stack
408 FND_MSG_PUB.add_exc_msg
409 (p_pkg_name => 'PA_RESOURCE_PVT.Insert_resource_denorm'
410 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
411
412 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
413
414 END Insert_resource_denorm;
415
416
417 -----------------------------------------------------------------------------------+
418 --
419 -- PROCEDURE : check_required_fields
420 -- DESCRIPTION: This procedure checks if the all the required fields in
421 -- p_resource_denorm_rec are not null
422 --
423 -----------------------------------------------------------------------------------+
424 PROCEDURE check_required_fields
425 ( p_resource_denorm_rec IN Resource_denorm_Rec_type,
426 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
427 x_err_msg_code OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
428 IS
429 BEGIN
430 -- Initialize the return status to success
431 x_return_status := FND_API.G_RET_STS_SUCCESS;
432
433 IF p_resource_denorm_rec.person_id = FND_API.G_MISS_NUM
434 OR p_resource_denorm_rec.resource_name = FND_API.G_MISS_CHAR
435 OR p_resource_denorm_rec.resource_type = FND_API.G_MISS_CHAR
436 OR p_resource_denorm_rec.resource_organization_id = FND_API.G_MISS_NUM
437 OR p_resource_denorm_rec.resource_effective_start_date = FND_API.G_MISS_DATE
438 OR p_resource_denorm_rec.employee_flag = FND_API.G_MISS_char THEN
439
440 x_err_msg_code := 'REQUIRED_FIELD_NULL';
441 x_return_status := FND_API.G_RET_STS_ERROR;
442
443 END IF;
444 END check_required_fields;
445
446
447 -----------------------------------------------------------------------------------+
448 --
449 -- PROCEDURE : Update_resource_denorm
450 -- DESCRIPTION: This Function updates record(s) in the table
451 -- 'PA_RESOURCES_DENORM' after some appropriate validations.
452 --
453 -----------------------------------------------------------------------------------+
454 PROCEDURE Update_resource_denorm (
455 p_resource_denorm_old_rec IN Resource_denorm_Rec_type,
456 p_resource_denorm_new_rec IN Resource_denorm_Rec_type,
457 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
458 x_msg_data OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
459 x_msg_count OUT NOCOPY NUMBER ) --File.Sql.39 bug 4440895
460 IS
461 l_resource_denorm_new_rec PA_RESOURCE_PVT.Resource_denorm_Rec_type;
462 l_resource_id NUMBER;
463 l_exists VARCHAR2(1);
464 l_return_status VARCHAR2(1);
465 l_err_msg_code fnd_new_messages.message_name%TYPE;
466 l_msg_index_out NUMBER;
467
468 BEGIN
469 -- Initialize the Error Stack
470 PA_DEBUG.init_err_stack('PA_RESOURCE_PVT.Update_resource_denorm');
471
472 -- Initialize the return status to success
473 x_return_status := FND_API.G_RET_STS_SUCCESS;
474
475 -- Save point in case of Roll back
476 savepoint RES_PVT_UPDATE_RES_DENORM;
477
478 -- put input record to the local variable
479 l_resource_denorm_new_rec := p_resource_denorm_new_rec;
480
481 -------------------------------------------------------------------------
482 -- Check if either person_id or job_id has been passed.
483 -- Both person_id and job_id should not be null at the same time.
484 -------------------------------------------------------------------------
485 IF l_resource_denorm_new_rec.person_id = FND_API.G_MISS_NUM AND
486 l_resource_denorm_new_rec.job_id = FND_API.G_MISS_num THEN
487 pa_utils.add_message (
488 p_app_short_name => 'PA',
489 p_msg_name => 'PA_PERSON_ID_JOB_ID_NULL');
490
491 RAISE FND_API.G_EXC_ERROR;
492 END IF;
493
494 -------------------------------------------------------------------------
495 -- If resource_effective_start_date has been passed, update an individual
496 -- record after some appropriate validations.
497 -- Since the combination of person_id and resource_effective_start_date is
498 -- the primary key in the table 'PA_RESOURCES_DENORM', resource_effective
499 -- _start_date should not be null for individual record update.
500 -------------------------------------------------------------------------
501 IF l_resource_denorm_new_rec.resource_effective_start_date <> FND_API.G_MISS_DATE AND
502 l_resource_denorm_new_rec.resource_effective_start_date is not null THEN
503
504 -- Update individual record.
505 update_single_res_denorm_rec (
506 p_resource_denorm_rec => l_resource_denorm_new_rec,
507 x_return_status => l_return_status,
508 x_err_msg_code => l_err_msg_code );
509
510
511 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
512 pa_utils.add_message
513 ( p_app_short_name => 'PA',
514 p_msg_name => l_err_msg_code);
515
516 RAISE FND_API.G_EXC_ERROR;
517 END IF;
518
519 -------------------------------------------------------------------------
520 -- If resource_effective_start_date has not been passed, update multiple
521 -- records after some appropriate validations.
522 -------------------------------------------------------------------------
523 ELSIF l_resource_denorm_new_rec.resource_effective_start_date = FND_API.G_MISS_DATE THEN
524
525 -- If person_id has been passed but not job_id
526 IF l_resource_denorm_new_rec.person_id <> FND_API.G_MISS_NUM AND
527 l_resource_denorm_new_rec.person_id is not null AND
528 l_resource_denorm_new_rec.job_id = FND_API.G_MISS_NUM THEN
529
530 -- Update person related multiple records
531 update_person_res_denorm_recs (
532 p_resource_denorm_rec => l_resource_denorm_new_rec,
533 x_return_status => l_return_status,
534 x_err_msg_code => l_err_msg_code );
535
536 IF l_return_status = FND_API.G_RET_STS_error THEN
537 pa_utils.add_message
538 (p_app_short_name => 'PA',
539 p_msg_name => l_err_msg_code);
540
541 RAISE FND_API.G_EXC_ERROR;
542 END IF;
543
544 -- If job_id has been passed but not person_id, usually update attribute
545 -- job_level or billable_flag on multiple records.
546 ELSIF l_resource_denorm_new_rec.person_id = FND_API.G_MISS_NUM
547 AND l_resource_denorm_new_rec.job_id <> FND_API.G_MISS_NUM
548 AND l_resource_denorm_new_rec.job_id is not null THEN
549
550 -- Update job related multiple records.
551 update_job_res_denorm_recs (
552 p_resource_denorm_rec => l_resource_denorm_new_rec,
553 x_return_status => l_return_status,
554 x_err_msg_code => l_err_msg_code );
555
556 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
557 pa_utils.add_message (
558 p_app_short_name => 'PA',
559 p_msg_name => l_err_msg_code);
560
561 RAISE FND_API.G_EXC_ERROR;
562 END IF;
563
564 END IF;
565
566 END IF;
567
568 EXCEPTION
569 WHEN FND_API.G_EXC_ERROR THEN
570 ROLLBACK TO RES_PVT_UPDATE_RES_DENORM;
571
572 x_return_status := FND_API.G_RET_STS_ERROR;
573
574 -- Reset the error stack when returning to the calling program
575 PA_DEBUG.Reset_Err_Stack;
576
577 -- get the number of messages
578 x_msg_count := FND_MSG_PUB.Count_Msg;
579
580 -- Put a message text from message stack into the x_msg_data if there is only
581 -- single error.
582 IF x_msg_count = 1 THEN
583 pa_interface_utils_pub.get_messages
584 (p_encoded => FND_API.G_TRUE
585 ,p_msg_index => 1
586 ,p_data => x_msg_data
587 ,p_msg_index_out => l_msg_index_out);
588 END IF;
589
590 WHEN OTHERS THEN
591 ROLLBACK TO RES_PVT_UPDATE_RES_DENORM;
592
593 -- Set the exception Message and the stack
594 FND_MSG_PUB.add_exc_msg
595 (p_pkg_name => 'PA_RESOURCE_PVT.Update_resource_denorm'
596 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
597
598 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
599
600 END Update_resource_denorm;
601
602
603
604 ---------------------------------------------------------------------------------------
605 --
606 -- PROCEDURE : update_single_res_denorm_rec
607 -- DESCRIPTION: This procedure updates single record in the table 'PA_RESOURCES_DENORM'.
608 --
609 ---------------------------------------------------------------------------------------
610 PROCEDURE update_single_res_denorm_rec(
611 p_resource_denorm_rec IN resource_denorm_rec_type,
612 x_return_status OUT NOCOPY VARCHAR, --File.Sql.39 bug 4440895
613 x_err_msg_code OUT NOCOPY VARCHAR) --File.Sql.39 bug 4440895
614 IS
615 num_of_rec NUMBER;
616 l_return_status VARCHAR2(1);
617 l_err_msg_code fnd_new_messages.message_name%TYPE;
618
619 BEGIN
620 -- Initialize the return status to success
621 x_return_status := FND_API.G_RET_STS_SUCCESS;
622
623 -- check if the person_id or resource_effective_start_date attribute is not null
624 IF p_resource_denorm_rec.person_id = FND_API.G_MISS_NUM OR
625 p_resource_denorm_rec.resource_effective_start_date = FND_API.G_MISS_date
626 THEN
627 x_err_msg_code := 'PA_PERSON_ID_OR_START_DATE_NULL';
628 x_return_status := FND_API.G_RET_STS_ERROR;
629 RETURN;
630 END IF;
631
632
633 UPDATE pa_resources_denorm
634 SET
635 resource_type = DECODE(p_resource_denorm_rec.resource_type, FND_API.G_MISS_CHAR,
636 resource_type,p_resource_denorm_rec.resource_type),
637 resource_organization_id = DECODE(p_resource_denorm_rec.resource_organization_ID,
638 FND_API.G_MISS_NUM, resource_organization_id,
639 p_resource_denorm_rec.resource_organization_id),
640 resource_country_code = DECODE(p_resource_denorm_rec.resource_country_code, FND_API.G_MISS_CHAR,
641 resource_country_code,p_resource_denorm_rec.resource_country_code),
642 resource_country = DECODE(p_resource_denorm_rec.resource_country, FND_API.G_MISS_CHAR,
643 resource_country,p_resource_denorm_rec.resource_country),
644 resource_region = DECODE(p_resource_denorm_rec.resource_region, FND_API.G_MISS_CHAR,
645 resource_region,p_resource_denorm_rec.resource_region),
646 resource_city = DECODE(p_resource_denorm_rec.resource_city, FND_API.G_MISS_CHAR,
647 resource_city,p_resource_denorm_rec.resource_city),
648 job_id = DECODE(p_resource_denorm_rec.job_id, FND_API.G_MISS_NUM,
649 job_id,p_resource_denorm_rec.job_id),
650 resource_job_level = DECODE(p_resource_denorm_rec.resource_job_level, FND_API.G_MISS_NUM,
651 resource_job_level,p_resource_denorm_rec.resource_job_level),
652 resource_effective_end_date = DECODE(p_resource_denorm_rec.resource_effective_end_date,
653 FND_API.G_MISS_DATE, resource_effective_end_date,
654 p_resource_denorm_rec. resource_effective_end_date),
655 employee_flag = DECODE(p_resource_denorm_rec.employee_flag, FND_API.G_MISS_CHAR,
656 employee_flag,p_resource_denorm_rec.employee_flag),
657 manager_id = DECODE(p_resource_denorm_rec.manager_id, FND_API.G_MISS_NUM,
658 manager_id,p_resource_denorm_rec.manager_id),
659 manager_name = DECODE(p_resource_denorm_rec.manager_name, FND_API.G_MISS_CHAR,
660 manager_name,p_resource_denorm_rec.manager_name),
661 billable_flag = DECODE(p_resource_denorm_rec.billable_flag, FND_API.G_MISS_CHAR,
662 billable_flag,p_resource_denorm_rec.billable_flag),
663 utilization_flag = DECODE(p_resource_denorm_rec.utilization_flag, FND_API.G_MISS_CHAR,
664 utilization_flag,p_resource_denorm_rec.utilization_flag),
665 schedulable_flag = DECODE(p_resource_denorm_rec.schedulable_flag, FND_API.G_MISS_CHAR,
666 schedulable_flag,p_resource_denorm_rec.schedulable_flag),
667 resource_org_id = DECODE(p_resource_denorm_rec.resource_org_id, FND_API.G_MISS_NUM,
668 resource_org_id,p_resource_denorm_rec.resource_org_id),
669 LAST_UPDATE_date = sysdate,
670 LAST_UPDATED_by = fnd_global.user_id,
671 LAST_UPDATE_login = fnd_global.login_id
672 WHERE person_id = p_resource_denorm_rec.person_id
673 AND resource_effective_start_date = p_resource_denorm_rec.resource_effective_start_date;
674 --AND resource_effective_end_date > sysdate;
675
676 EXCEPTION
677 WHEN OTHERS THEN
678 -- Set the exception Message and the stack
679 FND_MSG_PUB.add_exc_msg
680 (p_pkg_name => 'PA_RESOURCE_PVT.update_person_res_denorm_recs',
681 p_procedure_name => PA_DEBUG.G_Err_Stack );
682
683 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
684 -- RAISE;
685 END update_single_res_denorm_rec;
686
687
688
689 ------------------------------------------------------------------------------------
690 --
691 -- PROCEDURE : update_person_res_denorm_recs
692 -- DESCRIPTION: This procedure updates multiple records which are related to a
693 -- person in the table 'PA_RESOURCES_DENORM'. This procedure usually
694 -- is used to update resource_name.
695 --
696 ------------------------------------------------------------------------------------
697 PROCEDURE update_person_res_denorm_recs
698 ( p_resource_denorm_rec IN resource_denorm_rec_type,
699 x_return_status OUT NOCOPY VARCHAR, --File.Sql.39 bug 4440895
700 x_err_msg_code OUT NOCOPY VARCHAR) --File.Sql.39 bug 4440895
701 IS
702 num_of_rec NUMBER;
703 l_return_status VARCHAR2(1);
704 BEGIN
705 -- Initialize the return status to success
706 x_return_status := FND_API.G_RET_STS_SUCCESS;
707
708 -- check if the person_id or resource_effective_start_date attribute is not null
709 IF p_resource_denorm_rec.person_id = FND_API.G_MISS_NUM OR
710 p_resource_denorm_rec.person_id is null
711 THEN
712 x_err_msg_code := 'PA_PERSON_ID_NULL';
713 x_return_status := FND_API.G_RET_STS_ERROR;
714 RETURN;
715 END IF;
716
717
718 -- Update manager's name if there was an update on manager_name attribute in
719 -- this record.
720 syncronize_manager_name (
721 p_new_resource_denorm_rec => p_resource_denorm_rec,
722 x_return_status => l_return_status);
723
724
725 UPDATE pa_resources_denorm
726 SET
727 resource_name = DECODE(p_resource_denorm_rec.resource_name, FND_API.G_MISS_CHAR,
728 resource_name,p_resource_denorm_rec.resource_name),
729 resource_country_code = DECODE(p_resource_denorm_rec.resource_country_code, FND_API.G_MISS_CHAR,
730 resource_country_code,p_resource_denorm_rec.resource_country_code),
731 resource_country = DECODE(p_resource_denorm_rec.resource_country, fnd_API.G_MISS_CHAR,
732 resource_country,p_resource_denorm_rec.resource_country),
733 resource_region = DECODE(p_resource_denorm_rec.resource_region, FND_API.G_MISS_CHAR,
734 resource_region,p_resource_denorm_rec.resource_region),
735 resource_city = DECODE(p_resource_denorm_rec.resource_city, FND_API.G_MISS_CHAR,
736 resource_city,p_resource_denorm_rec.resource_city),
737 last_update_date = sysdate,
738 last_updated_by = fnd_global.user_id,
739 last_update_login = fnd_global.login_id
740 WHERE p_resource_denorm_rec.person_id = person_id;
741
742 -- The following code has been commented out because resource name changes
743 -- must be done for all resource records and not based on the system date.
744 -- AND resource_effective_end_date > sysdate;
745
746 EXCEPTION
747 WHEN OTHERS THEN
748
749 -- Set the exception Message and the stack
750 FND_MSG_PUB.add_exc_msg
751 (p_pkg_name => 'PA_RESOURCE_PVT.update_person_res_denorm_recs'
752 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
753
754 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
755
756 END update_person_res_denorm_recs;
757
758 ------------------------------------------------------------------------------------
759 --
760 -- PROCEDURE : syncronize_manager_name
761 -- DESCRIPTION: If the resource_name has been updated, we need to syncronize
762 -- manager_name if he is manager of someone.
763 --
764 ------------------------------------------------------------------------------------
765 PROCEDURE syncronize_manager_name
766 (p_new_resource_denorm_rec IN resource_denorm_rec_type,
767 x_return_status OUT NOCOPY VARCHAR) --File.Sql.39 bug 4440895
768 IS
769 l_old_resource_name pa_resources_denorm.resource_name%TYPE;
770 l_new_resource_name pa_resources_denorm.resource_name%TYPE := p_new_resource_denorm_rec.resource_name;
771 l_name_changed VARCHAR2(1):='N'; -- 5336386
772 BEGIN
773 -- Initialize the return status to success
774 x_return_status := FND_API.G_RET_STS_SUCCESS;
775
776 IF l_new_resource_name = FND_API.G_MISS_CHAR THEN
777 RETURN;
778 END IF;
779
780
781 -- 5336386 : Commented below select and added new
782 --SELECT DISTINCT resource_name
783 --INTO l_old_resource_name
784 --FROM pa_resources_denorm
785 --WHERE person_id=p_new_resource_denorm_rec.person_id;
786
787 SELECT 'Y' INTO l_name_changed
788 FROM dual
789 WHERE exists
790 (SELECT 'Y'
791 FROM pa_resources_denorm
792 WHERE person_id = p_new_resource_denorm_rec.person_id
793 AND resource_name <> l_new_resource_name) ;
794
795
796 --IF l_new_resource_name <> l_old_resource_name THEN 5336386
797 IF nvl(l_name_changed, 'N') = 'Y' THEN
798
799 -- if the resource_name has been updated, we need to syncronize manager_name if he
800 -- is manager of someone.
801
802 UPDATE pa_resources_denorm
803 SET manager_name = l_new_resource_name
804 WHERE manager_id = p_new_resource_denorm_rec.person_id;
805 --AND resource_effective_end_date > sysdate;
806 END IF;
807
808
809 EXCEPTION
810 WHEN OTHERS THEN
811
812 -- Set the exception Message and the stack
813 FND_MSG_PUB.add_exc_msg
814 (p_pkg_name => 'PA_RESOURCE_PVT.update_person_res_denorm_recs',
815 p_procedure_name => 'PA_RESOURCE_PVT.syncronize_manager_name'); --PA_DEBUG.G_Err_Stack ); 5336386
816
817 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
818
819 END syncronize_manager_name;
820
821
822 ------------------------------------------------------------------------------------
823 -- PROCEDURE : update_job_res_denorm_recs
824 -- DESCRIPTION: This procedure update multiple records which are related to a
825 -- job in the table 'PA_RESOURCES_DENORM'.
826 ------------------------------------------------------------------------------------
827 PROCEDURE update_job_res_denorm_recs
828 (p_resource_denorm_rec IN resource_denorm_rec_type,
829 p_start_rowid IN rowid default NULL,
830 p_end_rowid IN rowid default NULL,
831 x_return_status OUT NOCOPY VARCHAR, --File.Sql.39 bug 4440895
832 x_err_msg_code OUT NOCOPY VARCHAR ) --File.Sql.39 bug 4440895
833 IS
834 BEGIN
835 -- Initialize the return status to success
836 x_return_status := FND_API.G_RET_STS_SUCCESS;
837
838 -- fixed the bug 1559045 here
839 UPDATE pa_resources_denorm
840 SET
841 resource_job_level = DECODE(p_resource_denorm_rec.resource_job_level, FND_API.G_MISS_NUM,
842 resource_job_level,p_resource_denorm_rec.resource_job_level),
843 employee_flag = DECODE(p_resource_denorm_rec.employee_flag, FND_API.G_MISS_CHAR,
844 employee_flag, p_resource_denorm_rec.employee_flag),
845 billable_flag = DECODE(p_resource_denorm_rec.billable_flag, FND_API.G_MISS_CHAR,
846 billable_flag, p_resource_denorm_rec.billable_flag),
847 utilization_flag = DECODE(p_resource_denorm_rec.utilization_flag, FND_API.G_MISS_CHAR,
848 utilization_flag, p_resource_denorm_rec.utilization_flag),
849 schedulable_flag = DECODE(p_resource_denorm_rec.schedulable_flag, FND_API.G_MISS_CHAR,
850 schedulable_flag, p_resource_denorm_rec.schedulable_flag),
851 last_update_date = sysdate,
852 last_updated_by = fnd_global.user_id,
853 last_update_login = fnd_global.login_id
854 WHERE p_resource_denorm_rec.job_id = job_id
855 AND rowid between nvl(p_start_rowid, rowid)
856 and nvl(p_end_rowid, rowid);
857
858 EXCEPTION
859 WHEN OTHERS THEN
860 -- Set the exception Message and the stack
861 FND_MSG_PUB.add_exc_msg
862 (p_pkg_name => 'PA_RESOURCE_PVT.update_person_res_denorm_recs'
863 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
864
865 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
866
867 RAISE;
868 END update_job_res_denorm_recs;
869
870
871 ------------------------------------------------------------------------------------
872 --
873 -- PROCEDURE : delete_resource_denorm
874 -- DESCRIPTION: This procedure deletes a record in the table 'PA_RESOURCES_DENORM'.
875 --
876 ------------------------------------------------------------------------------------
877 PROCEDURE delete_resource_denorm
878 ( p_person_id IN pa_resources_denorm.person_id%type,
879 p_res_effective_start_date IN pa_resources_denorm.resource_effective_start_date%type,
880 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
881 x_msg_data OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
882 x_msg_count OUT NOCOPY NUMBER) --File.Sql.39 bug 4440895
883 IS
884 -- l_manager_id pa_resources_denorm.manager_id%TYPE;
885 -- l_msg_index_out NUMBER;
886 -- l_return_status VARCHAR2(1);
887 -- l_err_msg_code fnd_new_messages.message_name%TYPE;
888
889 BEGIN
890 -- Initialize the return status to success
891 x_return_status := FND_API.G_RET_STS_SUCCESS;
892
893
894 DELETE FROM PA_RESOURCES_DENORM
895 WHERE person_id = p_person_id
896 AND resource_effective_start_date = p_res_effective_start_date
897 AND resource_effective_end_date > trunc(sysdate);
898
899
900 EXCEPTION
901 WHEN OTHERS THEN
902 -- Set the exception Message and the stack
903 FND_MSG_PUB.add_exc_msg
904 ( p_pkg_name => 'PA_RESOURCE_PVT.delete_resource_denorm'
905 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
906
907 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
908 END delete_resource_denorm;
909
910
911
912 PROCEDURE Populate_Resources_Denorm ( p_resource_source_id IN NUMBER
913 , p_resource_id IN NUMBER
914 , p_resource_name IN VARCHAR2
915 , p_resource_type IN VARCHAR2
916 , p_person_type IN VARCHAR2
917 , p_resource_job_id IN NUMBER
918 , p_resource_job_group_id IN NUMBER
919 , p_resource_org_id IN NUMBER
920 , p_resource_organization_id IN NUMBER
921 , p_assignment_start_date IN DATE
922 , p_assignment_end_date IN DATE
923 , p_manager_id IN NUMBER
924 , p_manager_name IN VARCHAR2
925 , p_request_id IN NUMBER
926 , p_program_application_id IN NUMBER
927 , p_program_id IN NUMBER
928 , p_commit IN VARCHAR2
929 , p_validate_only IN VARCHAR2
930 , x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
931 , x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
932 , x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
933 ) IS
934
935 l_resource_country_code pa_resources_denorm.resource_country_code%TYPE;
936 l_resource_country pa_resources_denorm.resource_country%TYPE;
937 l_resource_region pa_resources_denorm.resource_region%TYPE;
938 l_resource_city pa_resources_denorm.resource_city%TYPE;
939 l_resource_job_level pa_resources_denorm.resource_job_level%TYPE;
940 l_employee_flag pa_resources_denorm.employee_flag%TYPE;
941 l_billable_flag pa_resources_denorm.billable_flag%TYPE;
942 l_utilization_flag pa_resources_denorm.utilization_flag%TYPE;
943 l_schedulable_flag pa_resources_denorm.schedulable_flag%TYPE;
944 l_return_status VARCHAR2(1);
945 l_error_msg_code VARCHAR2(2000);
946 l_msg_count NUMBER;
947 l_first_time VARCHAR2(1) := 'Y';
948 l_null_location VARCHAR2(1) := 'N';
949 l_rec_start_date pa_resources_denorm.resource_effective_start_date%TYPE;
950 l_rec_end_date pa_resources_denorm.resource_effective_end_date%TYPE;
951
952 CURSOR denormalized_locations (l_person_id IN NUMBER, l_asgn_start_date IN DATE, l_asgn_end_date IN DATE ) IS
953 SELECT date_from,
954 date_to
955 FROM per_addresses
956 WHERE person_id = l_person_id
957 AND date_from <= l_asgn_end_date
958 AND nvl(date_to, l_asgn_end_date) >= l_asgn_start_date
959 AND primary_flag = 'Y'
960 ORDER BY date_from
961 ;
962
963 location_rec denormalized_locations%ROWTYPE;
964 l_msg_index_out NUMBER; -- bug 5689674
965
966 BEGIN
967
968
969 -- Initialize the return status to success
970 x_return_status := FND_API.G_RET_STS_SUCCESS;
971
972
973 -- The delete logic deletes all overlapping records in pa_resources_denorm
974 -- for the incoming assignment record
975 -- This logic also works with location denormalization changes.
976 DELETE FROM pa_resources_denorm
977 WHERE person_id = p_resource_source_id
978 AND resource_effective_start_date <= p_assignment_end_date
979 AND resource_effective_end_date >= p_assignment_start_date
980 ;
981
982 -- Get common attributes for this HR assignment
983
984
985 -- get the resource's billable flag
986 l_billable_flag := PA_HR_UPDATE_API.check_job_billability(
987 p_job_id => p_resource_job_id,
988 p_person_id => p_resource_source_id,
989 p_date => p_assignment_start_date);
990
991 -- get thre resource's job level
992 l_resource_job_level := PA_HR_UPDATE_API.Get_Job_Level(
993 p_job_id => p_resource_job_id,
994 p_job_group_id => p_resource_job_group_id);
995
996 -- get the resource's utilization flag
997 l_utilization_flag := PA_HR_UPDATE_API.check_job_utilization(
998 p_job_id => p_resource_job_id,
999 p_person_id => p_resource_source_id,
1000 p_date => p_assignment_start_date);
1001
1002 -- get the resource's job schedulable flag
1003 l_schedulable_flag := PA_HR_UPDATE_API.check_job_schedulable
1004 (p_job_id => p_resource_job_id);
1005
1006 /* Bug 2898766 - Raise an exception if the information type attached to this job
1007 contains multiple rows */
1008
1009 IF (l_billable_flag = 'X') OR (l_resource_job_level = -99) OR
1010 (l_utilization_flag = 'X') OR (l_schedulable_flag='X') THEN
1011 /* Bug 5689674: Added code to pass the error message to make the
1012 FND_API.G_EXC_ERROR being raised a generic call for any excpetion. */
1013
1014 PA_UTILS.Add_Message(
1015 p_app_short_name => 'PA'
1016 ,p_msg_name => 'PA_MULT_INF_TYPES_FOR_JOB');
1017 /* End of changes Bug for 5689674 */
1018 RAISE FND_API.G_EXC_ERROR;
1019 END IF;
1020
1021 /* Bug 2898766 - End */
1022
1023 -- Set the employee flag according to the resource type
1024 IF p_resource_type = 'EMPLOYEE' THEN
1025 l_employee_flag:= 'Y';
1026 ELSE
1027 l_employee_flag:= 'N';
1028 END IF;
1029
1030 -- Location denormalization changes involve the following
1031 -- Code loops through all the different location records of a HR assignment
1032 -- and creates 1 record in pa_resources_denorm for every different location
1033 -- of a HR assignment
1034 OPEN denormalized_locations(p_resource_source_id, p_assignment_start_date, p_assignment_end_date);
1035
1036 LOOP
1037 FETCH denormalized_locations INTO location_rec;
1038
1039
1040 IF l_first_time = 'Y' THEN
1041 --dbms_output.put_line('First time in loop');
1042 --dbms_output.put_line('p_assignment_start_date:' || p_assignment_start_date);
1043 --dbms_output.put_line('p_assignment_end_date:' || p_assignment_end_date);
1044
1045 -- Null location record must be populated under the following conditions
1046 -- 1. If no location record is found during this assignment
1047 -- 2. If no location record in the beginning of this assignment
1048
1049 IF denormalized_locations%NOTFOUND THEN
1050 --dbms_output.put_line('No record for location');
1051 l_null_location := 'Y';
1052 l_rec_end_date := p_assignment_end_date;
1053 ELSIF location_rec.date_from > p_assignment_start_date THEN
1054 --dbms_output.put_line('First null location record');
1055 l_null_location := 'Y';
1056 l_rec_end_date := location_rec.date_from - 1;
1057 END IF;
1058
1059 IF l_null_location = 'Y' THEN
1060
1061 l_rec_start_date := p_assignment_start_date;
1062
1063 INSERT INTO PA_RESOURCES_DENORM ( person_id
1064 , resource_id
1065 , resource_name
1066 , resource_type
1067 , resource_org_id
1068 , resource_organization_id
1069 , resource_country_code
1070 , resource_country
1071 , resource_region
1072 , resource_city
1073 , resource_job_level
1074 , resource_effective_start_date
1075 , resource_effective_end_date
1076 , employee_flag
1077 , manager_id
1078 , manager_name
1079 , billable_flag
1080 , job_id
1081 , utilization_flag
1082 , schedulable_flag
1083 , request_id
1084 , program_application_id
1085 , program_id
1086 , program_update_date
1087 , creation_date
1088 , created_by
1089 , last_update_date
1090 , last_updated_by
1091 , last_update_login
1092 , resource_person_type
1093 )
1094 SELECT p_resource_source_id
1095 , p_resource_id
1096 , p_resource_name
1097 , p_resource_type
1098 , p_resource_org_id
1099 , p_resource_organization_id
1100 , l_resource_country_code
1101 , l_resource_country
1102 , l_resource_region
1103 , l_resource_city
1104 , l_resource_job_level
1105 , l_rec_start_date
1106 , l_rec_end_date
1107 , l_employee_flag
1108 , p_manager_id
1109 , p_manager_name
1110 , l_billable_flag
1111 , p_resource_job_id
1112 , l_utilization_flag
1113 , l_schedulable_flag
1114 , p_request_id
1115 , p_program_application_id
1116 , p_program_id
1117 , sysdate
1118 , sysdate
1119 /*----- Bug 1992257 commented
1120 , TO_NUMBER(FND_PROFILE.VALUE('USER_ID'))
1121 , sysdate
1122 , TO_NUMBER(FND_PROFILE.VALUE('USER_ID'))
1123 , TO_NUMBER(FND_PROFILE.VALUE('LOGIN_ID'))
1124 End of Comment, added next 4 lines */
1125 , TO_NUMBER(FND_GLOBAL.USER_ID)
1126 , sysdate
1127 , TO_NUMBER(FND_GLOBAL.USER_ID)
1128 , TO_NUMBER(FND_GLOBAL.LOGIN_ID)
1129 , p_person_type
1130 FROM dual;
1131 --end null location
1132 END IF;
1133 -- end first time
1134 END IF;
1135
1136 l_first_time := 'N';
1137
1138 EXIT WHEN denormalized_locations%NOTFOUND;
1139
1140
1141 -- Initialize the start and end dates and location of the location defined resource records
1142
1143 IF location_rec.date_from <= p_assignment_start_date THEN
1144 l_rec_start_date := p_assignment_start_date;
1145 ELSE
1146 l_rec_start_date := location_rec.date_from;
1147 END IF;
1148
1149 IF location_rec.date_to IS NULL THEN
1150 l_rec_end_date := p_assignment_end_date;
1151 ELSIF location_rec.date_to >= p_assignment_end_date THEN
1152 l_rec_end_date := p_assignment_end_date;
1153 ELSE
1154 l_rec_end_date := location_rec.date_to;
1155 END IF;
1156
1157 --dbms_output.put_line('p_start_date:' || l_rec_start_date);
1158 --dbms_output.put_line('p_end_date:' || l_rec_end_date);
1159
1160
1161 --get the resources locations details
1162 PA_LOCATION_UTILS.Get_EMP_Location_Details(p_person_id => p_resource_source_id,
1163 p_assign_date => l_rec_start_date,
1164 x_country_name => l_resource_country,
1165 x_city => l_resource_city,
1166 x_region => l_resource_region,
1167 x_country_code => l_resource_country_code,
1168 x_return_status => l_return_status,
1169 x_error_message_code => l_error_msg_code);
1170
1171 /* Bug 5689674: Added the below exception handling to throw an error without
1172 proceeding on inserting into pa_resources_denorm. This will show the
1173 error as an exception in the report output. */
1174 IF l_return_status <> FND_API.G_RET_STS_SUCCESS then
1175 IF l_error_msg_code IS NOT NULL THEN
1176 pa_utils.add_message
1177 ( p_app_short_name => 'PA',
1178 p_msg_name => l_error_msg_code);
1179 END IF;
1180 RAISE FND_API.G_EXC_ERROR;
1181 END IF;
1182 /* End of changes for Bug 5689674 */
1183
1184 INSERT INTO PA_RESOURCES_DENORM ( person_id
1185 , resource_id
1186 , resource_name
1187 , resource_type
1188 , resource_org_id
1189 , resource_organization_id
1190 , resource_country_code
1191 , resource_country
1192 , resource_region
1193 , resource_city
1194 , resource_job_level
1195 , resource_effective_start_date
1196 , resource_effective_end_date
1197 , employee_flag
1198 , manager_id
1199 , manager_name
1200 , billable_flag
1201 , job_id
1202 , utilization_flag
1203 , schedulable_flag
1204 , request_id
1205 , program_application_id
1206 , program_id
1207 , program_update_date
1208 , creation_date
1209 , created_by
1210 , last_update_date
1211 , last_updated_by
1212 , last_update_login
1213 , resource_person_type
1214 )
1215 SELECT p_resource_source_id
1216 , p_resource_id
1217 , p_resource_name
1218 , p_resource_type
1219 , p_resource_org_id
1220 , p_resource_organization_id
1221 , l_resource_country_code
1222 , l_resource_country
1223 , l_resource_region
1224 , l_resource_city
1225 , l_resource_job_level
1226 , l_rec_start_date
1227 , l_rec_end_date
1228 , l_employee_flag
1229 , p_manager_id
1230 , p_manager_name
1231 , l_billable_flag
1232 , p_resource_job_id
1233 , l_utilization_flag
1234 , l_schedulable_flag
1235 , p_request_id
1236 , p_program_application_id
1237 , p_program_id
1238 , sysdate
1239 , sysdate
1240 /*----- Bug 1992257 commented
1241 , TO_NUMBER(FND_PROFILE.VALUE('USER_ID'))
1242 , sysdate
1243 , TO_NUMBER(FND_PROFILE.VALUE('USER_ID'))
1244 , TO_NUMBER(FND_PROFILE.VALUE('LOGIN_ID'))
1245 End of Comment, added next 4 lines */
1246 , TO_NUMBER(FND_GLOBAL.USER_ID)
1247 , sysdate
1248 , TO_NUMBER(FND_GLOBAL.USER_ID)
1249 , TO_NUMBER(FND_GLOBAL.LOGIN_ID)
1250 , p_person_type
1251 FROM dual;
1252 END LOOP;
1253
1254 CLOSE denormalized_locations;
1255
1256 x_msg_count := FND_MSG_PUB.Count_Msg;
1257
1258 IF x_msg_count > 0 THEN
1259
1260 x_return_status := FND_API.G_RET_STS_ERROR;
1261
1262 END IF;
1263
1264 IF p_commit = FND_API.G_TRUE AND l_msg_count=0 THEN
1265
1266 COMMIT;
1267
1268 END IF;
1269
1270 EXCEPTION
1271 /* Bug 2898766 - save the error message in the pa_reporting_exceptions table */
1272
1273 WHEN FND_API.G_EXC_ERROR THEN
1274 /* Bug 5689674: Modified the code to make the exception handling generic. */
1275
1276 x_return_status := FND_API.G_RET_STS_ERROR;
1277
1278 -- Reset the error stack when returning to the calling program
1279 PA_DEBUG.Reset_Err_Stack;
1280
1281 -- get the number of messages
1282 x_msg_count := FND_MSG_PUB.Count_Msg;
1283
1284 -- Put a message text from message stack into the x_msg_data if there is only
1285 -- single error.
1286 IF x_msg_count = 1 THEN
1287 pa_interface_utils_pub.get_messages
1288 (p_encoded => FND_API.G_TRUE
1289 ,p_msg_index => 1
1290 ,p_data => x_msg_data
1291 ,p_msg_index_out => l_msg_index_out);
1292 END IF;
1293 /* End of Changes for Bug 5689674*/
1294
1295 /* p_context10 => 'REJECTED' Added for bug 4172140*/
1296 PA_MESSAGE_UTILS.save_messages(
1297 p_request_id => p_request_id
1298 ,p_source_Type1 => 'RESOURCE_PULL'
1299 ,p_source_Type2 => 'PARCPRJR'
1300 ,p_context1 => p_resource_source_id
1301 ,p_context2 => substrb(p_resource_name, 1, 30) --Changed substr to substrb for bug4584297
1302 ,p_context3 => p_resource_organization_id
1303 ,p_context10 => 'REJECTED'
1304 ,p_date_context1 => p_assignment_start_date
1305 ,p_date_context2 => p_assignment_end_date
1306 ,p_commit => p_commit
1307 ,x_return_status => l_return_status);
1308 /* Bug 2898766 - End */
1309 WHEN OTHERS THEN
1310 FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_RESOURCE_PVT.Populate_Resources_Denorm'
1311 , p_procedure_name => PA_DEBUG.G_Err_Stack);
1312 x_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
1313 RAISE;
1314
1315 END Populate_Resources_Denorm;
1316
1317 ------------------------------------------------------------------------------------
1318 --
1319 -- FUNCTION : Get_Manager_Id
1320 -- DESCRIPTION: This function gets the manager_id for a given project. It returns
1321 -- null if there is no manager. Also sets the following global variables
1322 -- g_manager_name - manager name
1323 -- g_manager_resource_id - manager's resource_id from pa_resources_denorm
1324 -- This API is used specifically in views pa_project_open_assns_v
1325 -- and pa_project_open_assns_staff_v
1326 --
1327 ------------------------------------------------------------------------------------
1328 FUNCTION Get_Manager_Id(
1329 p_project_id IN NUMBER)
1330 RETURN NUMBER
1331 IS
1332 l_party_id NUMBER;
1333 BEGIN
1334 SELECT parties.resource_source_id
1335 INTO l_party_id
1336 from pa_project_parties parties
1337 where parties.project_role_id = 1
1338 AND parties.project_id = p_project_id
1339 AND trunc(sysdate) between parties.start_date_active AND NVL(parties.end_date_active,trunc(sysdate) + 1);
1340
1341 select resource_name
1342 into g_manager_name
1343 from pa_resources_denorm
1344 where person_id = l_party_id
1345 and trunc(sysdate) between resource_effective_start_date and resource_effective_end_date;
1346
1347 select resource_id
1348 into g_manager_resource_id
1349 from pa_resources_denorm
1350 where person_id = l_party_id
1351 and trunc(sysdate) between resource_effective_start_date and resource_effective_end_date;
1352
1353 g_project_id := p_project_id; /* Bug#2604495 Setting the Global variable's value */
1354
1355 RETURN l_party_id;
1356 EXCEPTION
1357 WHEN NO_DATA_FOUND THEN
1358 g_manager_name := null;
1359 g_manager_resource_id := null;
1360 g_project_id := null; /* Bug#2604495 Setting the global variable as null if no data found occurs*/
1361 RETURN NULL;
1362 END;
1363
1364 ------------------------------------------------------------------------------------
1365 --
1366 -- FUNCTION : Get_Manager_Name
1367 -- DESCRIPTION: This function gets the manager_name from the global variable
1368 -- g_manager_name - manager name
1369 -- This API is used specifically in views pa_project_open_assns_v
1370 -- and pa_project_open_assns_staff_v
1371 --
1372 ------------------------------------------------------------------------------------
1373 FUNCTION Get_Manager_Name(p_project_id in number DEFAULT null) /*Bug#2604495-Added the parameter p_project_id to the function */
1374 RETURN VARCHAR2
1375 is
1376 l_party_id number(15); /* 2604495-Added local variable */
1377 BEGIN
1378 /* code for bug#2604495 starts */
1379
1380 if p_project_id = g_project_id then
1381 null;
1382 else
1383 l_party_id := get_manager_id(p_project_id);
1384 end if;
1385
1386 /* Code for bug#2604495 ends */
1387 RETURN g_manager_name;
1388 END;
1389
1390 ------------------------------------------------------------------------------------
1391 --
1392 -- FUNCTION : Get_Manager_Resource_Id
1393 -- DESCRIPTION: This function gets the manager's resource_id from the global variable
1394 -- g_manager_resource_id - manager's resource_id
1395 -- This API is used specifically in views pa_project_open_assns_v
1396 -- and pa_project_open_assns_staff_v
1397 --
1398 ------------------------------------------------------------------------------------
1399 FUNCTION Get_Manager_Resource_Id
1400 RETURN NUMBER
1401 IS
1402 BEGIN
1403 RETURN g_manager_resource_id;
1404 END;
1405
1406 ------------------------------------------------------------------------------------
1407 --
1408 -- FUNCTION : Get_Resource_Avl_to_date
1409 -- DESCRIPTION: This function gets the resource available to date for staffing
1410 -- home view objects. This function is modified for PA.K using a new
1411 -- data model (PA_RES_AVAILABILITY) to get availability information.
1412 --
1413 ------------------------------------------------------------------------------------
1414 FUNCTION Get_Resource_Avl_To_Date (p_resource_id IN NUMBER,
1415 p_avl_from_date IN DATE)
1416 RETURN DATE
1417 IS
1418 l_avl_to_date DATE;
1419 l_avl_profile NUMBER := FND_NUMBER.CANONICAL_TO_NUMBER(fnd_profile.value('PA_RES_CAPACITY_PERCENTAGE')); -- 4725606
1420 BEGIN
1421
1422 IF p_avl_from_date is not null THEN
1423
1424 select min(start_date)-1
1425 into l_avl_to_date
1426 from pa_res_availability
1427 where resource_id = p_resource_id
1428 and record_type = 'C'
1429 and start_date > p_avl_from_date
1430 -- Commented for 4725606 and percent < FND_NUMBER.CANONICAL_TO_NUMBER(fnd_profile.value('PA_RES_CAPACITY_PERCENTAGE'));
1431 and percent < l_avl_profile ; -- 4725606
1432
1433 ELSE
1434 l_avl_to_date := null;
1435 END IF;
1436
1437 -- we do not want to show 31-Dec-4712 as the available to date
1438 -- so perform the following logic to show it as null instead
1439 IF l_avl_to_date IS NOT NULL THEN
1440 IF to_char(l_avl_to_date, 'DD-MM-YYYY') = '31-12-4712' THEN
1441 l_avl_to_date := null;
1442 END IF;
1443 END IF;
1444
1445
1446 RETURN l_avl_to_date;
1447
1448 EXCEPTION
1449 WHEN OTHERS THEN
1450 RETURN null;
1451 END;
1452
1453 ------------------------------------------------------------------------------------
1454 --
1455 -- FUNCTION : Get_Resource_ovc_to_date
1456 -- DESCRIPTION: This function gets the resource overcommited to date for staffing
1457 -- home view objects. This function is modified for PA.K using a new
1458 -- data model (PA_RES_AVAILABILITY) to get overcommitment information.
1459 --
1460 ------------------------------------------------------------------------------------
1461 FUNCTION Get_Resource_Ovc_To_Date (p_resource_id IN NUMBER,
1462 p_ovc_from_date IN DATE)
1463 RETURN DATE
1464 IS
1465 l_ovc_to_date DATE;
1466 l_ovc_profile NUMBER := FND_NUMBER.CANONICAL_TO_NUMBER(fnd_profile.value('PA_OVERCOMMITMENT_PERCENTAGE')); -- 4725606
1467 BEGIN
1468
1469 IF p_ovc_from_date is not null THEN
1470 select min(start_date)-1
1471 into l_ovc_to_date
1472 from pa_res_availability
1473 where resource_id = p_resource_id
1474 and record_type = 'C'
1475 and start_date > p_ovc_from_date
1476 -- Commented for 4725606 and -1 * percent < FND_NUMBER.CANONICAL_TO_NUMBER(fnd_profile.value('PA_OVERCOMMITMENT_PERCENTAGE'));
1477 and -1 * percent < l_ovc_profile ; -- 4725606
1478 ELSE
1479 l_ovc_to_date := null;
1480 END IF;
1481
1482 -- we do not want to show 31-Dec-4712 as the available to date
1483 -- so perform the following logic to show it as null instead
1484 IF l_ovc_to_date IS NOT NULL THEN
1485 IF to_char(l_ovc_to_date, 'DD-MM-YYYY') = '31-12-4712' THEN
1486 l_ovc_to_date := null;
1487 END IF;
1488 END IF;
1489
1490
1491 RETURN l_ovc_to_date;
1492
1493 EXCEPTION
1494 WHEN OTHERS THEN
1495 RETURN null;
1496 END;
1497
1498 ------------------------------------------------------------------------------------
1499 --
1500 -- FUNCTION : Get_Resource_ovc_hours
1501 -- DESCRIPTION: This function gets the resource overcommited hours for the given
1502 -- from and to dates using pa_res_availability table.
1503 --
1504 ------------------------------------------------------------------------------------
1505 FUNCTION Get_Resource_Ovc_hours(p_resource_id IN NUMBER,
1506 p_ovc_from_date IN DATE,
1507 p_ovc_to_date IN DATE)
1508 RETURN NUMBER
1509 IS
1510 l_ovc_hours NUMBER;
1511 BEGIN
1512
1513 IF p_ovc_from_date is null OR p_ovc_to_date is null THEN
1514 l_ovc_hours := null;
1515 ELSE
1516 -- have to multiply by -1 since we store overcommitment as
1517 -- negative availability
1518 select sum(hours) * -1
1519 into l_ovc_hours
1520 from pa_res_availability
1521 where resource_id = p_resource_id
1522 and record_type = 'C'
1523 and start_date >= p_ovc_from_date
1524 and end_date <= p_ovc_to_date;
1525 END IF;
1526
1527 RETURN l_ovc_hours;
1528
1529 EXCEPTION
1530 WHEN OTHERS THEN
1531 RETURN null;
1532 END;
1533
1534
1535 -- PROCEDURE
1536 -- Validate_Staff_Filter_Values
1537 -- PURPOSE
1538 -- Specifically for staffing pages use.
1539 -- Currrently used by StaffingHomeAMImpl and ResourceListAMImpl.
1540 -- This procedure validates the organization or/and manager
1541 -- parameters used in the staffing filters. It requires p_responsibility=RM
1542 -- if the user has resource manager responsibility and p_check=Y
1543 -- if the manager_name contains % character (from My Resources page).
1544 -- HISTORY
1545 -- 20-AUG-2002 Created adabdull
1546 --+
1547 PROCEDURE Validate_Staff_Filter_Values(
1548 p_manager_name IN VARCHAR2
1549 ,p_manager_id IN NUMBER
1550 ,p_org_name IN VARCHAR2
1551 ,p_org_id IN NUMBER
1552 ,p_responsibility IN VARCHAR2
1553 ,p_check IN VARCHAR2
1554 ,x_manager_id OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
1555 ,x_org_id OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
1556 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1557 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
1558 ,x_msg_data OUT NOCOPY VARCHAR2 ) --File.Sql.39 bug 4440895
1559 IS
1560 l_return_status VARCHAR2(1);
1561 l_msg_data VARCHAR2(2000);
1562 l_error_message_code fnd_new_messages.message_name%TYPE;
1563 l_resource_type_id NUMBER;
1564 l_msg_index_out NUMBER;
1565 l_msg_count NUMBER;
1566 BEGIN
1567
1568 -- initialize error stack
1569 fnd_msg_pub.initialize;
1570
1571 -- set return status to Success
1572 x_return_status := FND_API.G_RET_STS_SUCCESS;
1573
1574 IF(p_org_name IS NOT NULL) THEN
1575 -- check whether organization name is valid
1576 PA_HR_ORG_UTILS.Check_OrgName_Or_Id (
1577 p_organization_id => p_org_id,
1578 p_organization_name => p_org_name,
1579 p_check_id_flag => PA_STARTUP.G_Check_ID_Flag,
1580 x_organization_id => x_org_id,
1581 x_return_status => l_return_status,
1582 x_error_msg_code => l_error_message_code);
1583
1584 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1585 PA_UTILS.Add_Message (
1586 p_app_short_name => 'PA'
1587 ,p_msg_name => l_error_message_code );
1588 END IF;
1589 ELSE
1590 x_org_id := NULL;
1591 END IF;
1592
1593 IF(p_manager_name IS NOT NULL) THEN
1594
1595 -- validate whether the manager name entered or selected in the LOV
1596 -- exists in the corresponding view depending on user's responsibility
1597 PA_RESOURCE_UTILS.Check_ManagerName_Or_Id(
1598 p_manager_name => p_manager_name
1599 ,p_manager_id => p_manager_id
1600 ,p_responsibility => p_responsibility
1601 ,p_check => p_check
1602 ,x_manager_id => x_manager_id
1603 ,x_msg_count => l_msg_count
1604 ,x_return_status => l_return_status
1605 ,x_error_message_code => l_error_message_code);
1606
1607 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1608 PA_UTILS.Add_Message(
1609 p_app_short_name => 'PA'
1610 ,p_msg_name => l_error_message_code);
1611 END IF;
1612 ELSE
1613 x_manager_id := NULL;
1614 END IF;
1615
1616 x_msg_count := FND_MSG_PUB.Count_Msg;
1617
1618 IF x_msg_count = 1 THEN
1619 pa_interface_utils_pub.get_messages ( p_encoded => FND_API.G_TRUE
1620 ,p_msg_index => 1
1621 ,p_data => x_msg_data
1622 ,p_msg_index_out => l_msg_index_out);
1623 END IF;
1624
1625 IF x_msg_count <> 0 THEN
1626 x_return_status := FND_API.G_RET_STS_ERROR;
1627 END IF;
1628
1629 EXCEPTION
1630 WHEN OTHERS THEN
1631 x_manager_id := NULL;
1632 x_org_id := NULL;
1633 x_return_status := FND_API.G_RET_STS_ERROR;
1634 END Validate_Staff_Filter_Values;
1635
1636 FUNCTION get_res_conf_availability( p_resource_id IN NUMBER,
1637 p_start_date IN DATE,
1638 p_end_date IN DATE)
1639 RETURN NUMBER
1640 IS
1641 l_conf_availability NUMBER;
1642
1643 BEGIN
1644
1645
1646 BEGIN
1647
1648 SELECT sum(nvl(capacity_quantity,0) - nvl(confirmed_qty,0))
1649 INTO l_conf_availability
1650 FROM pa_forecast_items
1651 WHERE resource_id = p_resource_id
1652 AND delete_flag = 'N'
1653 AND forecast_item_type = 'U'
1654 AND item_date between p_start_date
1655 and p_end_date;
1656 EXCEPTION
1657 WHEN NO_DATA_FOUND THEN
1658 l_conf_availability := 0;
1659 END;
1660
1661
1662 RETURN l_conf_availability;
1663 EXCEPTION
1664 WHEN OTHERS THEN
1665 RETURN 0;
1666 END get_res_conf_availability;
1667
1668 FUNCTION get_res_prov_conf_availability( p_resource_id IN NUMBER,
1669 p_start_date IN DATE,
1670 p_end_date IN DATE)
1671 RETURN NUMBER
1672 IS
1673 l_prov_conf_availability NUMBER;
1674
1675 BEGIN
1676
1677
1678 BEGIN
1679
1680 SELECT sum(nvl(capacity_quantity,0) - nvl(confirmed_qty,0) - nvl(provisional_qty,0))
1681 INTO l_prov_conf_availability
1682 FROM pa_forecast_items
1683 WHERE resource_id = p_resource_id
1684 AND delete_flag = 'N'
1685 AND forecast_item_type = 'U'
1686 AND item_date between p_start_date
1687 and p_end_date;
1688 EXCEPTION
1689 WHEN NO_DATA_FOUND THEN
1690 l_prov_conf_availability := 0;
1691 END;
1692
1693
1694 RETURN l_prov_conf_availability;
1695 EXCEPTION
1696 WHEN OTHERS THEN
1697 RETURN 0;
1698 END get_res_prov_conf_availability;
1699
1700 -------------------------------------------------------------------------------+
1701 --
1702 -- PROCEDURE : Populate_Res_Availability
1703 -- DESCRIPTION : This Procedure populates PA_RES_AVAILABILITY for the resource
1704 -- for the given dates
1705 -- It populates the following data slices
1706 -- - (Confirmed) Availability/Overcommittment
1707 -- - (Confirmed + Provisional) Availability/Overcommittment
1708 -- This procedure is also called from the upgrade script
1709 -- used to populate PA_RES_AVAILABILITY
1710 --
1711 --------------------------------------------------------------------------------+
1712 PROCEDURE populate_res_availability (
1713 p_resource_id IN NUMBER,
1714 p_cstart_date IN DATE,
1715 p_cend_date IN DATE,
1716 p_bstart_date IN DATE,
1717 p_bend_date IN DATE,
1718 x_return_status OUT NOCOPY VARCHAR2 ) --File.Sql.39 bug 4440895
1719 IS
1720
1721 --l_profile varchar2(30) := 'DAILY';
1722 l_created_by number;
1723 l_last_updated_by number;
1724 l_last_update_login number;
1725 l_start_date date;
1726 l_end_date date;
1727 l_percent number := -1;
1728 l_earliest_start_date date;
1729 l_rec_count number:= 0;
1730
1731 BEGIN
1732
1733 -- Initialize the return status to success
1734 x_return_status := FND_API.G_RET_STS_SUCCESS;
1735
1736 l_created_by := fnd_global.user_id;
1737 l_last_updated_by := fnd_global.user_id;
1738 l_last_update_login := fnd_global.login_id;
1739
1740 -- commented out for perf bug 4930256
1741 -- l_profile := FND_PROFILE.VALUE_SPECIFIC('PA_AVAILABILITY_CAL_PERIOD');
1742
1743 --Dynamic SQL is being used as Oracle 8i PL/SQL does not support
1744 --Analytic functions
1745
1746 IF l_profile = 'DAILY' THEN
1747
1748 --For Confirmed Availability/Overcommittment
1749 /* Commented dynamix SQL and replaced with bind variables for Bug #3964268
1750 execute immediate
1751 'insert into PA_RES_AVAILABILITY
1752 ( start_date,
1753 end_date,
1754 percent,
1755 record_type,
1756 resource_id,
1757 hours,
1758 creation_date,
1759 last_update_date,
1760 created_by,
1761 last_updated_by,
1762 last_update_login
1763 )
1764 select item_date,
1765 (lead(item_date) over(order by item_date)) - 1,
1766 c * 100 ,
1767 ' || '''C''' ||
1768 ' , resource_id,
1769 pa_resource_pvt.get_res_conf_availability(resource_id, nvl(item_date,' || '''' || p_cstart_date || ''''|| '),
1770 nvl((lead(item_date) over(order by item_date)),' || '''' || p_cend_date || ''''|| ') - 1),
1771 sysdate,
1772 sysdate,
1773 ' || l_created_by || ',
1774 ' || l_last_updated_by || ',
1775 ' || l_last_update_login || '
1776 from ( select (nvl(capacity_quantity,0) - nvl(confirmed_qty,0))/nvl(capacity_quantity,0) a,
1777 lag((nvl(capacity_quantity,0) - nvl(confirmed_qty,0))/nvl(capacity_quantity,0),1,1000) -- bug 3229929
1778 over ( order by item_date) as b,
1779 (nvl(capacity_quantity,0) - nvl(confirmed_qty,0))/capacity_quantity c,
1780 item_date,
1781 resource_id
1782 from pa_forecast_items
1783 where forecast_item_type = ' || '''U''' ||
1784 ' and resource_id = ' || p_resource_id || '
1785 and capacity_quantity > 0
1786 and delete_flag = ' || '''N''' || '
1787 and item_date between ' || '''' || p_cstart_date || '''' || ' and ' ||
1788 '''' || p_cend_date || ''''|| ')
1789 where nvl(a,0) - nvl(b,0) <> 0';
1790 */
1791 /* Modified the above commented SQL as below for Bug #3964268 with bind variables */
1792 execute immediate
1793 'insert into PA_RES_AVAILABILITY
1794 ( start_date,
1795 end_date,
1796 percent,
1797 record_type,
1798 resource_id,
1799 hours,
1800 creation_date,
1801 last_update_date,
1802 created_by,
1803 last_updated_by,
1804 last_update_login
1805 )
1806 select item_date,
1807 (lead(item_date) over(order by item_date)) - 1,
1808 c * 100 ,
1809 :c,
1810 resource_id,
1811 pa_resource_pvt.get_res_conf_availability(resource_id, nvl(item_date, :cstart_date ),
1812 nvl((lead(item_date) over(order by item_date)), :cend_date ) - 1),
1813 sysdate,
1814 sysdate,
1815 :created_by,
1816 :last_updated_by,
1817 :last_update_login
1818 from ( select (nvl(capacity_quantity,0) - nvl(confirmed_qty,0))/nvl(capacity_quantity,0) a,
1819 lag((nvl(capacity_quantity,0) - nvl(confirmed_qty,0))/nvl(capacity_quantity,0),1,1000) -- bug 3229929
1820 over ( order by item_date) as b,
1821 (nvl(capacity_quantity,0) - nvl(confirmed_qty,0))/capacity_quantity c,
1822 item_date,
1823 resource_id
1824 from pa_forecast_items
1825 where forecast_item_type = :u
1826 and resource_id = :resource_id
1827 and capacity_quantity > 0
1828 and delete_flag = :n
1829 and item_date between :cstart_date and :cend_date )
1830 where nvl(a,0) - nvl(b,0) <> 0' using 'C', p_cstart_date, p_cend_date, l_created_by,
1831 l_last_updated_by, l_last_update_login, 'U', p_resource_id, 'N', p_cstart_date, p_cend_date;
1832
1833 --For Confirmed+Provisional Availability/Overcommittment
1834 /* Commented dynamix SQL and replaced with bind variables for Bug #3964268
1835 execute immediate
1836 'insert into PA_RES_AVAILABILITY
1837 ( start_date,
1838 end_date,
1839 percent,
1840 record_type,
1841 resource_id,
1842 hours,
1843 creation_date,
1844 last_update_date,
1845 created_by,
1846 last_updated_by,
1847 last_update_login
1848 )
1849 select item_date,
1850 (lead(item_date) over(order by item_date)) - 1,
1851 c * 100 ,
1852 ' || '''B''' ||
1853 ' , resource_id,
1854 pa_resource_pvt.get_res_prov_conf_availability(resource_id, nvl(item_date,' || '''' || p_bstart_date || '''' || '), nvl((lead(item_date) over(order by item_date)),' || '''' || p_bend_date || ''''|| ') - 1),
1855 sysdate,
1856 sysdate,
1857 ' || l_created_by || ' ,
1858 ' || l_last_updated_by || ' ,
1859 ' || l_last_update_login || '
1860 from ( select (nvl(capacity_quantity,0) - nvl(confirmed_qty,0) - nvl(provisional_qty,0))/nvl(capacity_quantity,0) a,
1861 lag((nvl(capacity_quantity,0) - nvl(confirmed_qty,0) - nvl(provisional_qty,0))/nvl(capacity_quantity,0))
1862 over ( order by item_date) as b,
1863 (nvl(capacity_quantity,0) - nvl(confirmed_qty,0) - nvl(provisional_qty,0))/capacity_quantity c,
1864 item_date,
1865 resource_id
1866 from pa_forecast_items
1867 where forecast_item_type = ' || '''U''' ||
1868 ' and resource_id = ' || p_resource_id || '
1869 and capacity_quantity > 0
1870 and delete_flag = ' || '''N''' || '
1871 and item_date between ' || '''' || p_bstart_date || '''' || ' and ' ||
1872 '''' || p_bend_date || ''''|| ')
1873 where nvl(a,0) - nvl(b,0) <> 0';
1874 */
1875 /* Modified the above commented SQL as below for Bug #3964268 with bind variables */
1876 execute immediate
1877 'insert into PA_RES_AVAILABILITY
1878 ( start_date,
1879 end_date,
1880 percent,
1881 record_type,
1882 resource_id,
1883 hours,
1884 creation_date,
1885 last_update_date,
1886 created_by,
1887 last_updated_by,
1888 last_update_login
1889 )
1890 select item_date,
1891 (lead(item_date) over(order by item_date)) - 1,
1892 c * 100 ,
1893 :b,
1894 resource_id,
1895 pa_resource_pvt.get_res_prov_conf_availability(resource_id, nvl(item_date, :bstart_date ), nvl((lead(item_date) over(order by item_date)), :bend_date ) - 1),
1896 sysdate,
1897 sysdate,
1898 :created_by,
1899 :last_updated_by,
1900 :last_update_login
1901 from ( select (nvl(capacity_quantity,0) - nvl(confirmed_qty,0) - nvl(provisional_qty,0))/nvl(capacity_quantity,0) a,
1902 lag((nvl(capacity_quantity,0) - nvl(confirmed_qty,0) - nvl(provisional_qty,0))/nvl(capacity_quantity,0))
1903 over ( order by item_date) as b,
1904 (nvl(capacity_quantity,0) - nvl(confirmed_qty,0) - nvl(provisional_qty,0))/capacity_quantity c,
1905 item_date,
1906 resource_id
1907 from pa_forecast_items
1908 where forecast_item_type = :u
1909 and resource_id = :resource_id
1910 and capacity_quantity > 0
1911 and delete_flag = :n
1912 and item_date between :bstart_date and :bend_date )
1913 where nvl(a,0) - nvl(b,0) <> 0' using 'B', p_bstart_date, p_bend_date, l_created_by,
1914 l_last_updated_by, l_last_update_login, 'U', p_resource_id, 'N', p_bstart_date, p_bend_date;
1915
1916 --Logic to update the null dates due to usage of lead/lag
1917 update pa_res_availability avl
1918 set start_date = p_cstart_date
1919 where resource_id = p_resource_id
1920 and record_type = 'C'
1921 and start_date is null;
1922
1923 update pa_res_availability avl
1924 set end_date = p_cend_date
1925 where resource_id = p_resource_id
1926 and record_type = 'C'
1927 and end_date is null;
1928
1929 --Logic to insert 0 percent records in the beginning and end
1930 select min(start_date)
1931 into l_start_date
1932 from pa_res_availability
1933 where resource_id = p_resource_id
1934 and record_type = 'C';
1935
1936 IF l_start_date is not null THEN
1937
1938 select percent
1939 into l_percent
1940 from pa_res_availability
1941 where resource_id = p_resource_id
1942 and record_type = 'C'
1943 and start_date = l_start_date;
1944
1945 END IF;
1946
1947 IF l_percent <> 0 THEN
1948
1949 insert into PA_RES_AVAILABILITY
1950 ( start_date,
1951 end_date,
1952 percent,
1953 record_type,
1954 resource_id,
1955 creation_date,
1956 last_update_date,
1957 created_by,
1958 last_updated_by,
1959 last_update_login)
1960 select min(start_date) - 1,
1961 min(start_date) - 1,
1962 0,
1963 record_type,
1964 resource_id,
1965 sysdate,
1966 sysdate,
1967 l_created_by,
1968 l_last_updated_by,
1969 l_last_update_login
1970 from pa_res_availability
1971 where resource_id = p_resource_id
1972 and record_type = 'C'
1973 group by 0,
1974 record_type,
1975 resource_id,
1976 sysdate,
1977 sysdate,
1978 l_created_by,
1979 l_last_updated_by,
1980 l_last_update_login;
1981 END IF;
1982
1983
1984 select max(end_date)
1985 into l_end_date
1986 from pa_res_availability
1987 where resource_id = p_resource_id
1988 and record_type = 'C';
1989
1990 IF l_end_date is not null THEN
1991
1992 select percent
1993 into l_percent
1994 from pa_res_availability
1995 where resource_id = p_resource_id
1996 and record_type = 'C'
1997 and end_date = l_end_date;
1998 END IF;
1999
2000 IF l_percent <> 0 THEN
2001
2002 insert into PA_RES_AVAILABILITY
2003 ( start_date,
2004 end_date,
2005 percent,
2006 record_type,
2007 resource_id,
2008 creation_date,
2009 last_update_date,
2010 created_by,
2011 last_updated_by,
2012 last_update_login)
2013 select max(end_date) + 1,
2014 max(end_date) + 1,
2015 0,
2016 record_type,
2017 resource_id,
2018 sysdate,
2019 sysdate,
2020 l_created_by,
2021 l_last_updated_by,
2022 l_last_update_login
2023 from pa_res_availability
2024 where resource_id = p_resource_id
2025 and record_type = 'C'
2026 group by 0,
2027 record_type,
2028 resource_id,
2029 sysdate,
2030 sysdate,
2031 l_created_by,
2032 l_last_updated_by,
2033 l_last_update_login;
2034 END IF;
2035
2036 --Logic to update the null dates due to usage of lead/lag
2037 update pa_res_availability avl
2038 set start_date = p_bstart_date
2039 where resource_id = p_resource_id
2040 and record_type = 'B'
2041 and start_date is null;
2042
2043 update pa_res_availability avl
2044 set end_date = p_bend_date
2045 where resource_id = p_resource_id
2046 and record_type = 'B'
2047 and end_date is null;
2048
2049 --Logic to insert 0 percent records in the beginning and end
2050 select min(start_date)
2051 into l_start_date
2052 from pa_res_availability
2053 where resource_id = p_resource_id
2054 and record_type = 'B';
2055
2056 IF l_start_date is not null THEN
2057
2058 select percent
2059 into l_percent
2060 from pa_res_availability
2061 where resource_id = p_resource_id
2062 and record_type = 'B'
2063 and start_date = l_start_date;
2064
2065 END IF;
2066
2067 IF l_percent <> 0 THEN
2068
2069
2070 insert into PA_RES_AVAILABILITY
2071 ( start_date,
2072 end_date,
2073 percent,
2074 record_type,
2075 resource_id,
2076 creation_date,
2077 last_update_date,
2078 created_by,
2079 last_updated_by,
2080 last_update_login)
2081 select min(start_date) - 1,
2082 min(start_date) - 1,
2083 0,
2084 record_type,
2085 resource_id,
2086 sysdate,
2087 sysdate,
2088 l_created_by,
2089 l_last_updated_by,
2090 l_last_update_login
2091 from pa_res_availability
2092 where resource_id = p_resource_id
2093 and record_type = 'B'
2094 group by 0,
2095 record_type,
2096 resource_id,
2097 sysdate,
2098 sysdate,
2099 l_created_by,
2100 l_last_updated_by,
2101 l_last_update_login;
2102 END IF;
2103
2104 select max(end_date)
2105 into l_end_date
2106 from pa_res_availability
2107 where resource_id = p_resource_id
2108 and record_type = 'B';
2109
2110 IF l_end_date is not null THEN
2111
2112 select percent
2113 into l_percent
2114 from pa_res_availability
2115 where resource_id = p_resource_id
2116 and record_type = 'B'
2117 and end_date = l_end_date;
2118
2119 END IF;
2120
2121 IF l_percent <> 0 THEN
2122
2123 insert into PA_RES_AVAILABILITY
2124 ( start_date,
2125 end_date,
2126 percent,
2127 record_type,
2128 resource_id,
2129 creation_date,
2130 last_update_date,
2131 created_by,
2132 last_updated_by,
2133 last_update_login)
2134 select max(end_date) + 1,
2135 max(end_date) + 1,
2136 0,
2137 record_type,
2138 resource_id,
2139 sysdate,
2140 sysdate,
2141 l_created_by,
2142 l_last_updated_by,
2143 l_last_update_login
2144 from pa_res_availability
2145 where resource_id = p_resource_id
2146 and record_type = 'B'
2147 group by 0,
2148 record_type,
2149 resource_id,
2150 sysdate,
2151 sysdate,
2152 l_created_by,
2153 l_last_updated_by,
2154 l_last_update_login;
2155 END IF;
2156
2157 ELSIF l_profile = 'WEEKLY' THEN
2158
2159 --dbms_output.put_line('Profile is weekly');
2160
2161 --For Confirmed Availability/Overcommittment
2162 /* Commented dynamix SQL and replaced with bind variables for Bug #3964268
2163 execute immediate
2164 'insert into PA_RES_AVAILABILITY
2165 ( start_date,
2166 end_date,
2167 percent,
2168 record_type,
2169 resource_id,
2170 hours,
2171 creation_date,
2172 last_update_date,
2173 created_by,
2174 last_updated_by,
2175 last_update_login
2176 )
2177 select item_date,
2178 (lead(item_date) over(order by item_date)) - 1,
2179 c * 100 ,
2180 ' || '''C''' ||
2181 ' , resource_id,
2182 pa_resource_pvt.get_res_conf_availability(resource_id, nvl(item_date,' || '''' || p_cstart_date || '''' || '), nvl((lead(item_date) over(order by item_date)),' || '''' || p_cend_date || ''''|| ') - 1),
2183 sysdate,
2184 sysdate,
2185 ' || l_created_by || ',
2186 ' || l_last_updated_by || ',
2187 ' || l_last_update_login || '
2188 from (
2189 select (nvl(a1,0) - nvl(a2,0))/nvl(a1,1) a,
2190 lag((nvl(a1,0) - nvl(a2,0))/nvl(a1,1))
2191 over ( order by d) as b,
2192 (nvl(a1,0) - nvl(a2,0))/nvl(a1,1) c,
2193 d item_date,
2194 resource_id
2195 from (
2196 select (global_exp_period_end_date - 6) d,
2197 resource_id,
2198 sum(capacity_quantity) a1,
2199 sum(confirmed_qty) a2
2200 from pa_forecast_items
2201 where forecast_item_type = ' || '''U''' ||
2202 'and resource_id = ' || p_resource_id || '
2203 and capacity_quantity > 0
2204 and delete_flag = ' || '''N''' || '
2205 and item_date between ' || '''' || p_cstart_date || '''' || ' and ' ||
2206 '''' || p_cend_date || ''''||
2207 'group by global_exp_period_end_date - 6,
2208 resource_id))
2209 where nvl(a,0) - nvl(b,0) <> 0 ';
2210 */
2211 /* Modified the above commented SQL as below for Bug #3964268 with bind variables */
2212 execute immediate
2213 'insert into PA_RES_AVAILABILITY
2214 ( start_date,
2215 end_date,
2216 percent,
2217 record_type,
2218 resource_id,
2219 hours,
2220 creation_date,
2221 last_update_date,
2222 created_by,
2223 last_updated_by,
2224 last_update_login
2225 )
2226 select item_date,
2227 (lead(item_date) over(order by item_date)) - 1,
2228 c * 100 ,
2229 :c,
2230 resource_id,
2231 pa_resource_pvt.get_res_conf_availability(resource_id, nvl(item_date, :cstart_date), nvl((lead(item_date) over(order by item_date)), :cend_date ) - 1),
2232 sysdate,
2233 sysdate,
2234 :created_by,
2235 :last_updated_by,
2236 :last_update_login
2237 from (
2238 select (nvl(a1,0) - nvl(a2,0))/nvl(a1,1) a,
2239 lag((nvl(a1,0) - nvl(a2,0))/nvl(a1,1))
2240 over ( order by d) as b,
2241 (nvl(a1,0) - nvl(a2,0))/nvl(a1,1) c,
2242 d item_date,
2243 resource_id
2244 from (
2245 select (global_exp_period_end_date - 6) d,
2246 resource_id,
2247 sum(capacity_quantity) a1,
2248 sum(confirmed_qty) a2
2249 from pa_forecast_items
2250 where forecast_item_type = :u
2251 and resource_id = :resource_id
2252 and capacity_quantity > 0
2253 and delete_flag = :n
2254 and item_date between :cstart_date and :cend_date
2255 group by global_exp_period_end_date - 6,
2256 resource_id))
2257 where nvl(a,0) - nvl(b,0) <> 0 ' using 'C', p_cstart_date, p_cend_date, l_created_by,
2258 l_last_updated_by, l_last_update_login, 'U', p_resource_id, 'N', p_cstart_date, p_cend_date;
2259
2260 --For Confirmed+Provisional Availability/Overcommittment
2261 /* Commented dynamix SQL and replaced with bind variables for Bug #3964268
2262 execute immediate
2263 'insert into PA_RES_AVAILABILITY
2264 ( start_date,
2265 end_date,
2266 percent,
2267 record_type,
2268 resource_id,
2269 hours,
2270 creation_date,
2271 last_update_date,
2272 created_by,
2273 last_updated_by,
2274 last_update_login
2275 )
2276 select item_date,
2277 (lead(item_date) over(order by item_date)) - 1,
2278 c * 100 ,
2279 ' || '''B''' ||
2280 ' , resource_id,
2281 pa_resource_pvt.get_res_prov_conf_availability(resource_id, nvl(item_date,' || '''' || p_bstart_date || '''' || '), nvl((lead(item_date) over(order by item_date)),' || '''' || p_bend_date || ''''|| ') - 1),
2282 sysdate,
2283 sysdate,
2284 ' || l_created_by || ',
2285 ' || l_last_updated_by ||',
2286 ' || l_last_update_login || '
2287 from (
2288 select (nvl(a1,0) - nvl(a2,0) - nvl(a3,0))/nvl(a1,1) a,
2289 lag((nvl(a1,0) - nvl(a2,0) - nvl(a3,0))/nvl(a1,1))
2290 over ( order by d) as b,
2291 (nvl(a1,0) - nvl(a2,0) - nvl(a3,0))/nvl(a1,1) c,
2292 d item_date,
2293 resource_id
2294 from (
2295 select (global_exp_period_end_date - 6) d,
2296 resource_id,
2297 sum(capacity_quantity) a1,
2298 sum(confirmed_qty) a2,
2299 sum(provisional_qty) a3
2300 from pa_forecast_items
2301 where forecast_item_type = ' || '''U''' ||
2302 'and resource_id = ' || p_resource_id || '
2303 and capacity_quantity > 0
2304 and delete_flag = ' || '''N''' || '
2305 and item_date between ' || '''' || p_bstart_date || '''' || ' and ' ||
2306 '''' || p_bend_date || ''''||
2307 'group by global_exp_period_end_date - 6,
2308 resource_id))
2309 where nvl(a,0) - nvl(b,0) <> 0 ';
2310 */
2311 /* Modified the above commented SQL as below for Bug #3964268 with bind variables */
2312 execute immediate
2313 'insert into PA_RES_AVAILABILITY
2314 ( start_date,
2315 end_date,
2316 percent,
2317 record_type,
2318 resource_id,
2319 hours,
2320 creation_date,
2321 last_update_date,
2322 created_by,
2323 last_updated_by,
2324 last_update_login
2325 )
2326 select item_date,
2327 (lead(item_date) over(order by item_date)) - 1,
2328 c * 100 ,
2329 :b,
2330 resource_id,
2331 pa_resource_pvt.get_res_prov_conf_availability(resource_id, nvl(item_date, :bstart_date ), nvl((lead(item_date) over(order by item_date)), :bend_date ) - 1),
2332 sysdate,
2333 sysdate,
2334 :created_by,
2335 :last_updated_by,
2336 :last_update_login
2337 from (
2338 select (nvl(a1,0) - nvl(a2,0) - nvl(a3,0))/nvl(a1,1) a,
2339 lag((nvl(a1,0) - nvl(a2,0) - nvl(a3,0))/nvl(a1,1))
2340 over ( order by d) as b,
2341 (nvl(a1,0) - nvl(a2,0) - nvl(a3,0))/nvl(a1,1) c,
2342 d item_date,
2343 resource_id
2344 from (
2345 select (global_exp_period_end_date - 6) d,
2346 resource_id,
2347 sum(capacity_quantity) a1,
2348 sum(confirmed_qty) a2,
2349 sum(provisional_qty) a3
2350 from pa_forecast_items
2351 where forecast_item_type = :u
2352 and resource_id = :resource_id
2353 and capacity_quantity > 0
2354 and delete_flag = :n
2355 and item_date between :bstart_date and :bend_date
2356 group by global_exp_period_end_date - 6,
2357 resource_id))
2358 where nvl(a,0) - nvl(b,0) <> 0 ' using 'B', p_bstart_date, p_bend_date, l_created_by,
2359 l_last_updated_by, l_last_update_login, 'U', p_resource_id, 'N', p_bstart_date, p_bend_date;
2360
2361 --dbms_output.put_line('After Insert');
2362
2363 --Logic to update the null dates due to usage of lead/lag
2364 update pa_res_availability avl
2365 set start_date = p_cstart_date
2366 where resource_id = p_resource_id
2367 and record_type = 'C'
2368 and start_date is null;
2369
2370 --dbms_output.put_line('After C Update 1');
2371
2372 update pa_res_availability avl
2373 set end_date = p_cend_date
2374 where resource_id = p_resource_id
2375 and record_type = 'C'
2376 and end_date is null;
2377
2378 --dbms_output.put_line('After C Update 2');
2379
2380 --Logic to insert 0 percent records in the beginning and end
2381 select min(start_date)
2382 into l_start_date
2383 from pa_res_availability
2384 where resource_id = p_resource_id
2385 and record_type = 'C';
2386
2387 IF l_start_date is not null THEN
2388
2389 BEGIN
2390 select percent
2391 into l_percent
2392 from pa_res_availability
2393 where resource_id = p_resource_id
2394 and record_type = 'C'
2395 and start_date = l_start_date
2396 and percent = 0;
2397 EXCEPTION
2398 WHEN NO_DATA_FOUND THEN
2399 l_percent := -1;
2400 END;
2401 --dbms_output.put_line('After C l_percent');
2402
2403 END IF;
2404
2405 IF l_percent <> 0 THEN
2406
2407 insert into PA_RES_AVAILABILITY
2408 ( start_date,
2409 end_date,
2410 percent,
2411 record_type,
2412 resource_id,
2413 creation_date,
2414 last_update_date,
2415 created_by,
2416 last_updated_by,
2417 last_update_login)
2418 select min(start_date) - 1,
2419 min(start_date) - 1,
2420 0,
2421 record_type,
2422 resource_id,
2423 sysdate,
2424 sysdate,
2425 l_created_by,
2426 l_last_updated_by,
2427 l_last_update_login
2428 from pa_res_availability
2429 where resource_id = p_resource_id
2430 and record_type = 'C'
2431 group by 0,
2432 record_type,
2433 resource_id,
2434 sysdate,
2435 sysdate,
2436 l_created_by,
2437 l_last_updated_by,
2438 l_last_update_login;
2439 END IF;
2440
2441 select max(end_date)
2442 into l_end_date
2443 from pa_res_availability
2444 where resource_id = p_resource_id
2445 and record_type = 'C';
2446
2447 IF l_end_date is not null THEN
2448
2449 BEGIN
2450 select percent
2451 into l_percent
2452 from pa_res_availability
2453 where resource_id = p_resource_id
2454 and record_type = 'C'
2455 and end_date = l_end_date
2456 and percent = 0;
2457 EXCEPTION
2458 WHEN NO_DATA_FOUND THEN
2459 l_percent := -1;
2460 END;
2461 --dbms_output.put_line('After B l_percent');
2462
2463 END IF;
2464
2465 IF l_percent <> 0 THEN
2466
2467 insert into PA_RES_AVAILABILITY
2468 ( start_date,
2469 end_date,
2470 percent,
2471 record_type,
2472 resource_id,
2473 creation_date,
2474 last_update_date,
2475 created_by,
2476 last_updated_by,
2477 last_update_login)
2478 select max(end_date) + 1,
2479 max(end_date) + 1,
2480 0,
2481 record_type,
2482 resource_id,
2483 sysdate,
2484 sysdate,
2485 l_created_by,
2486 l_last_updated_by,
2487 l_last_update_login
2488 from pa_res_availability
2489 where resource_id = p_resource_id
2490 and record_type = 'C'
2491 group by 0,
2492 record_type,
2493 resource_id,
2494 sysdate,
2495 sysdate,
2496 l_created_by,
2497 l_last_updated_by,
2498 l_last_update_login;
2499 END IF;
2500
2501 --Logic to update the null dates due to usage of lead/lag
2502 update pa_res_availability avl
2503 set start_date = p_bstart_date
2504 where resource_id = p_resource_id
2505 and record_type = 'B'
2506 and start_date is null;
2507
2508 update pa_res_availability avl
2509 set end_date = p_bend_date
2510 where resource_id = p_resource_id
2511 and record_type = 'B'
2512 and end_date is null;
2513
2514 --Logic to insert 0 percent records in the beginning and end
2515 select min(start_date)
2516 into l_start_date
2517 from pa_res_availability
2518 where resource_id = p_resource_id
2519 and record_type = 'B';
2520
2521 IF l_start_date is not null THEN
2522
2523 BEGIN
2524 select percent
2525 into l_percent
2526 from pa_res_availability
2527 where resource_id = p_resource_id
2528 and record_type = 'B'
2529 and start_date = l_start_date
2530 and percent = 0;
2531 EXCEPTION
2532 WHEN NO_DATA_FOUND THEN
2533 l_percent := -1;
2534 END;
2535
2536 END IF;
2537
2538 IF l_percent <> 0 THEN
2539
2540 insert into PA_RES_AVAILABILITY
2541 ( start_date,
2542 end_date,
2543 percent,
2544 record_type,
2545 resource_id,
2546 creation_date,
2547 last_update_date,
2548 created_by,
2549 last_updated_by,
2550 last_update_login)
2551 select min(start_date) - 1,
2552 min(start_date) - 1,
2553 0,
2554 record_type,
2555 resource_id,
2556 sysdate,
2557 sysdate,
2558 l_created_by,
2559 l_last_updated_by,
2560 l_last_update_login
2561 from pa_res_availability
2562 where resource_id = p_resource_id
2563 and record_type = 'B'
2564 group by 0,
2565 record_type,
2566 resource_id,
2567 sysdate,
2568 sysdate,
2569 l_created_by,
2570 l_last_updated_by,
2571 l_last_update_login;
2572 END IF;
2573
2574 select max(end_date)
2575 into l_end_date
2576 from pa_res_availability
2577 where resource_id = p_resource_id
2578 and record_type = 'B';
2579
2580 IF l_end_date is not null THEN
2581
2582 BEGIN
2583 select percent
2584 into l_percent
2585 from pa_res_availability
2586 where resource_id = p_resource_id
2587 and record_type = 'B'
2588 and end_date = l_end_date
2589 and percent = 0;
2590 EXCEPTION
2591 WHEN NO_DATA_FOUND THEN
2592 l_percent := -1;
2593 END;
2594 END IF;
2595
2596 IF l_percent <> 0 THEN
2597
2598 insert into PA_RES_AVAILABILITY
2599 ( start_date,
2600 end_date,
2601 percent,
2602 record_type,
2603 resource_id,
2604 creation_date,
2605 last_update_date,
2606 created_by,
2607 last_updated_by,
2608 last_update_login)
2609 select max(end_date) + 1,
2610 max(end_date) + 1,
2611 0,
2612 record_type,
2613 resource_id,
2614 sysdate,
2615 sysdate,
2616 l_created_by,
2617 l_last_updated_by,
2618 l_last_update_login
2619 from pa_res_availability
2620 where resource_id = p_resource_id
2621 and record_type = 'B'
2622 group by 0,
2623 record_type,
2624 resource_id,
2625 sysdate,
2626 sysdate,
2627 l_created_by,
2628 l_last_updated_by,
2629 l_last_update_login;
2630 END IF;
2631
2632 ELSE
2633
2634 --TODO: Add error message for missing profile value
2635 PA_UTILS.Add_Message(
2636 p_app_short_name => 'PA'
2637 ,p_msg_name => 'PA_NO_AVL_CALC_PROF');
2638
2639 x_return_status := FND_API.G_RET_STS_ERROR;
2640
2641 RETURN;
2642 END IF;
2643
2644 --dbms_output.put_line('Before bug fix');
2645
2646 --------------------------------------------------
2647 --BUG 2634959: Availability start date must not be
2648 --earlier than min. resource start date
2649 --------------------------------------------------
2650 select min(RESOURCE_EFFECTIVE_START_DATE)
2651 into l_earliest_start_date
2652 from pa_resources_denorm
2653 where RESOURCE_ID = p_resource_id;
2654
2655 update pa_res_availability
2656 set start_date = l_earliest_start_date
2657 where resource_id = p_resource_id
2658 and start_date < l_earliest_start_date
2659 and percent > 0;
2660
2661 select count(*)
2662 into l_rec_count
2663 from pa_res_availability
2664 where resource_id = p_resource_id
2665 and start_date < l_earliest_start_date
2666 and record_type = 'B'
2667 and percent = 0;
2668
2669 IF l_rec_count > 1 THEN
2670
2671 delete
2672 from pa_res_availability
2673 where resource_id = p_resource_id
2674 and start_date < l_earliest_start_date - 1
2675 and record_type = 'B'
2676 and percent = 0;
2677
2678 END IF;
2679
2680 select count(*)
2681 into l_rec_count
2682 from pa_res_availability
2683 where resource_id = p_resource_id
2684 and start_date < l_earliest_start_date
2685 and record_type = 'C'
2686 and percent = 0;
2687
2688 IF l_rec_count > 1 THEN
2689
2690 delete
2691 from pa_res_availability
2692 where resource_id = p_resource_id
2693 and start_date < l_earliest_start_date - 1
2694 and record_type = 'C'
2695 and percent = 0;
2696
2697 END IF;
2698
2699 update pa_res_availability
2700 set start_date = l_earliest_start_date -1,
2701 end_date = l_earliest_start_date -1
2702 where resource_id = p_resource_id
2703 and start_date < l_earliest_start_date
2704 and percent = 0;
2705 --------------------------------------------------
2706
2707 --dbms_output.put_line('After bug fix');
2708
2709 EXCEPTION
2710 WHEN OTHERS THEN
2711
2712 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2713 RAISE;
2714
2715 END populate_res_availability;
2716
2717 -------------------------------------------------------------------------------+
2718 --
2719 -- PROCEDURE : Update_Res_Availability
2720 -- DESCRIPTION : This Procedure is called after FIs are generated
2721 -- for any PJR assignment
2722 -- This API updates PA_RES_AVAILABILITY based
2723 -- on the new assignment created
2724 --------------------------------------------------------------------------------+
2725 PROCEDURE update_res_availability (
2726 p_resource_id IN NUMBER,
2727 p_start_date IN DATE,
2728 p_end_date IN DATE,
2729 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
2730 x_msg_data OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
2731 x_msg_count OUT NOCOPY NUMBER ) --File.Sql.39 bug 4440895
2732 IS
2733
2734 l_msg_index_out number;
2735 l_cstart_date date;
2736 l_cend_date date;
2737 l_bstart_date date;
2738 l_bend_date date;
2739 l_start_date date;
2740 l_end_date date;
2741
2742 BEGIN
2743
2744 -- Initialize the Error Stack
2745 PA_DEBUG.init_err_stack('PA_RESOURCE_PVT.Update_Res_Availability');
2746
2747 -- Initialize the return status to success
2748 x_return_status := FND_API.G_RET_STS_SUCCESS;
2749
2750 BEGIN
2751
2752 select min(resource_effective_start_date)
2753 into l_start_date
2754 from pa_resources_denorm
2755 where resource_id = p_resource_id;
2756
2757 /*bug 3229929 modified min(resource_effective_end_date) to max(resource_effective_end_date) */
2758 select max(resource_effective_end_date)
2759 into l_end_date
2760 from pa_resources_denorm
2761 where resource_id = p_resource_id;
2762
2763 EXCEPTION
2764 WHEN NO_DATA_FOUND THEN
2765 l_start_date := trunc(p_start_date);
2766 l_end_date := trunc(p_end_date);
2767 END;
2768
2769 --Delete all records in pa_res_availability for this resource
2770 delete
2771 from pa_res_availability
2772 where resource_id = p_resource_id;
2773
2774
2775 populate_res_availability (
2776 p_resource_id => p_resource_id,
2777 p_cstart_date => trunc(l_start_date),
2778 p_cend_date => trunc(l_end_date),
2779 p_bstart_date => trunc(l_start_date),
2780 p_bend_date => trunc(l_end_date),
2781 x_return_status => x_return_status );
2782
2783 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2784 RAISE FND_API.G_EXC_ERROR;
2785 END IF;
2786
2787 EXCEPTION
2788 WHEN FND_API.G_EXC_ERROR THEN
2789 x_return_status := FND_API.G_RET_STS_ERROR;
2790
2791 -- Reset the error stack when returning to the calling program
2792 PA_DEBUG.Reset_Err_Stack;
2793
2794 -- get the number of messages
2795 x_msg_count := FND_MSG_PUB.Count_Msg;
2796
2797 -- Put a message text from message stack into the x_msg_data if there is only
2798 -- single error.
2799 IF x_msg_count = 1 THEN
2800 pa_interface_utils_pub.get_messages
2801 (p_encoded => FND_API.G_TRUE
2802 ,p_msg_index => 1
2803 ,p_data => x_msg_data
2804 ,p_msg_index_out => l_msg_index_out);
2805 END IF;
2806
2807 RAISE;
2808
2809 WHEN OTHERS THEN
2810 -- Set the exception Message and the stack
2811 FND_MSG_PUB.add_exc_msg
2812 (p_pkg_name => 'PA_RESOURCE_PVT.Update_Res_Availability'
2813 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
2814
2815 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2816
2817 RAISE;
2818
2819 END Update_Res_Availability;
2820
2821 -------------------------------------------------------------------------------+
2822 --
2823 -- PROCEDURE : Refresh_Res_Availability
2824 -- DESCRIPTION : This Procedure is called by the concurrent program
2825 -- to refresh PA_RES_AVAILABILITY
2826 --------------------------------------------------------------------------------+
2827 PROCEDURE refresh_res_availability (
2828 errbuf OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
2829 retcode OUT NOCOPY VARCHAR2 ) --File.Sql.39 bug 4440895
2830 IS
2831 l_return_status varchar2(1);
2832 l_msg_index_out number;
2833 -- l_profile varchar2(30) := 'DAILY';
2834 CURSOR resources IS
2835 select resource_id,
2836 min(resource_effective_start_date) start_date,
2837 max(resource_effective_end_date) end_date
2838 from pa_resources_denorm
2839 group by resource_id;
2840
2841 CURSOR redundant_resources IS -- Added for bug 7316435
2842 select resource_id
2843 from pa_res_availability
2844 where resource_id not in (Select resource_id from pa_resources_denorm)
2845 and resource_id <> -1;
2846
2847 l_res_count Number; --Added for bug 4928773
2848
2849
2850 BEGIN
2851
2852 -- Initialize the return status to success
2853 retcode := 0;
2854
2855 FOR rec IN redundant_resources LOOP --Added for bug 7316435 (Cleaning Redundant records)
2856
2857 delete
2858 from PA_RES_AVAILABILITY where RESOURCE_ID = rec.resource_id;
2859
2860 END LOOP;
2861
2862
2863
2864 --SAVEPOINT s_res_avl_begin; commented for bug 4928773
2865
2866 --Cleanup existing records in PA_RES_AVAILABILITY
2867 --delete
2868 --from PA_RES_AVAILABILITY;commented for bug 4928773
2869 l_res_count := 0;
2870 FOR rec IN resources LOOP
2871
2872 If l_res_count = 100 or l_res_count = 0 then --Added If block for bug 4928773
2873 l_res_count := 0;
2874 commit;
2875 SAVEPOINT s_res_avl_begin;
2876 end if;
2877
2878 delete
2879 from PA_RES_AVAILABILITY where RESOURCE_ID = rec.resource_id; --Added for bug 4928773
2880
2881 pa_resource_pvt.populate_res_availability(
2882 p_resource_id => rec.resource_id,
2883 p_cstart_date => rec.start_date,
2884 p_cend_date => rec.end_date,
2885 p_bstart_date => rec.start_date,
2886 p_bend_date => rec.end_date,
2887 x_return_status => l_return_status );
2888
2889 l_res_count := l_res_count + 1; --Added for bug 4928773
2890
2891 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2892
2893 ROLLBACK TO s_res_avl_begin;
2894 retcode := 2;
2895 EXIT;
2896
2897 END IF;
2898
2899 END LOOP;
2900
2901 -- commented out for perf bug 4930256
2902 -- l_profile := FND_PROFILE.VALUE_SPECIFIC('PA_AVAILABILITY_CAL_PERIOD');
2903
2904 delete
2905 from PA_RES_AVAILABILITY where RESOURCE_ID = -1; --Added for bug 4928773
2906
2907 INSERT
2908 INTO PA_RES_AVAILABILITY
2909 (
2910 RESOURCE_ID,
2911 START_DATE,
2912 RECORD_TYPE,
2913 CREATION_DATE,
2914 CREATED_BY,
2915 LAST_UPDATE_DATE,
2916 LAST_UPDATED_BY
2917 )
2918 VALUES
2919 (
2920 -1,
2921 sysdate,
2922 decode(l_profile, 'DAILY', 'D', 'W'),
2923 sysdate,
2924 -1,
2925 sysdate,
2926 -1
2927 );
2928
2929
2930 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2931 retcode := 2;
2932 RAISE FND_API.G_EXC_ERROR;
2933 END IF;
2934
2935 COMMIT;
2936
2937 retcode := 0;
2938
2939 EXCEPTION
2940 WHEN FND_API.G_EXC_ERROR THEN
2941
2942 ROLLBACK TO s_res_avl_begin;
2943
2944 retcode := 2;
2945 errbuf := SQLERRM;
2946
2947 WHEN OTHERS THEN
2948
2949 ROLLBACK TO s_res_avl_begin;
2950
2951 errbuf := SQLERRM;
2952 retcode := 2;
2953
2954 END Refresh_Res_Availability;
2955
2956
2957 -- FUNCTION
2958 -- Get_Staff_Mgr_Org_Id
2959 -- PURPOSE
2960 -- Specifically for staffing pages use (Avl/Ovc CO objects)
2961 -- It gets the Staffing Manager Organization (either SM organization
2962 -- or from the profile option) to be used in the VO. It returns the
2963 -- organization id.
2964 --+
2965 FUNCTION Get_Staff_Mgr_Org_Id (p_user_id IN NUMBER
2966 ,p_person_id IN NUMBER)
2967 RETURN VARCHAR2
2968 IS
2969 l_org_id NUMBER;
2970 l_return_status VARCHAR2(1);
2971 l_error_message_code fnd_new_messages.message_name%TYPE;
2972 BEGIN
2973
2974 l_org_id := fnd_profile.value_specific(
2975 name => 'PA_STAFF_HOME_DEF_ORG',
2976 user_id => p_user_id);
2977
2978 IF l_org_id IS NULL THEN
2979 PA_RESOURCE_UTILS.get_org_id(
2980 p_personid => p_person_id,
2981 p_start_date => sysdate,
2982 x_orgid => l_org_id,
2983 x_error_message_code => l_error_message_code,
2984 x_return_status => l_return_status);
2985 END IF;
2986
2987 RETURN l_org_id;
2988
2989 END Get_Staff_Mgr_Org_Id;
2990
2991
2992 END PA_RESOURCE_PVT;