[Home] [Help]
PACKAGE BODY: APPS.PA_RESOURCE_PVT
Source
1 PACKAGE BODY PA_RESOURCE_PVT AS
2 -- $Header: PARRESVB.pls 120.8.12010000.5 2009/07/03 11:14:08 nisinha 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 -- Bug#6875286
912 -- Procedure : GET_ALL_JOB_INFO
913 -- DESCRIPTION: This procedure gets the value of three flags:
914 -- l_billable_flag, l_utilization_flag and l_schedulable_flag
915 --
916 --
917 ------------------------------------------------------------------------------------
918 PROCEDURE Get_All_Job_Info (p_job_id IN NUMBER
919 ,x_billable_flag OUT NOCOPY VARCHAR2
920 ,x_utilization_flag OUT NOCOPY VARCHAR2
921 ,x_schedulable_flag OUT NOCOPY VARCHAR2
922 ) IS
923
924 l_job_info_type VARCHAR2(20) := 'Job Category';
925 l_billable_flag pa_resources_denorm.billable_flag%TYPE;
926 l_utilization_flag pa_resources_denorm.utilization_flag%TYPE;
927 l_schedulable_flag pa_resources_denorm.schedulable_flag%TYPE;
928
929
930 BEGIN
931
932 l_billable_flag := 'N';
933 l_utilization_flag := 'N';
934 l_schedulable_flag := 'N';
935
936
937 SELECT jei_information2, jei_information3,jei_information6
938 INTO l_billable_flag, l_utilization_flag, l_schedulable_flag
939 FROM per_job_extra_info
940 WHERE job_id = p_job_id
941 AND information_type = l_job_info_type;
942
943 IF l_billable_flag IS NULL THEN
944 l_billable_flag := 'N';
945 END IF ;
946
947 IF l_utilization_flag IS NULL THEN
948 l_utilization_flag := 'N';
949 END IF ;
950
951 IF l_schedulable_flag IS NULL THEN
952 l_schedulable_flag := 'N';
953 END IF ;
954
955 x_billable_flag := l_billable_flag;
956 x_utilization_flag := l_utilization_flag;
957 x_schedulable_flag := l_schedulable_flag;
958
959
960 EXCEPTION
961 WHEN TOO_MANY_ROWS THEN
962 l_utilization_flag := 'X';
963 l_billable_flag := 'X';
964 l_schedulable_flag := 'X';
965 x_billable_flag := l_billable_flag;
966 x_utilization_flag := l_utilization_flag;
967 x_schedulable_flag := l_schedulable_flag;
968
969 WHEN NO_DATA_FOUND THEN
970 l_utilization_flag :='N';
971 l_billable_flag := 'N';
972 l_schedulable_flag := 'N';
973 x_billable_flag := l_billable_flag;
974 x_utilization_flag := l_utilization_flag;
975 x_schedulable_flag := l_schedulable_flag;
976
977 WHEN OTHERS THEN
978 NULL ;
979
980 END Get_All_Job_Info;
981
982 -- end bug#6875286
983
984
985 PROCEDURE Populate_Resources_Denorm ( p_resource_source_id IN NUMBER
986 , p_resource_id IN NUMBER
987 , p_resource_name IN VARCHAR2
988 , p_resource_type IN VARCHAR2
989 , p_person_type IN VARCHAR2
990 , p_resource_job_id IN NUMBER
991 , p_resource_job_group_id IN NUMBER
992 , p_resource_org_id IN NUMBER
993 , p_resource_organization_id IN NUMBER
994 , p_assignment_start_date IN DATE
995 , p_assignment_end_date IN DATE
996 , p_manager_id IN NUMBER
997 , p_manager_name IN VARCHAR2
998 , p_request_id IN NUMBER
999 , p_program_application_id IN NUMBER
1000 , p_program_id IN NUMBER
1001 , p_commit IN VARCHAR2
1002 , p_validate_only IN VARCHAR2
1003 , x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1004 , x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
1005 , x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1006 ) IS
1007
1008 l_resource_country_code pa_resources_denorm.resource_country_code%TYPE;
1009 l_resource_country pa_resources_denorm.resource_country%TYPE;
1010 l_resource_region pa_resources_denorm.resource_region%TYPE;
1011 l_resource_city pa_resources_denorm.resource_city%TYPE;
1012 l_resource_job_level pa_resources_denorm.resource_job_level%TYPE;
1013 l_employee_flag pa_resources_denorm.employee_flag%TYPE;
1014 l_billable_flag pa_resources_denorm.billable_flag%TYPE;
1015 l_utilization_flag pa_resources_denorm.utilization_flag%TYPE;
1016 l_schedulable_flag pa_resources_denorm.schedulable_flag%TYPE;
1017 l_return_status VARCHAR2(1);
1018 l_error_msg_code VARCHAR2(2000);
1019 l_msg_count NUMBER;
1020 l_first_time VARCHAR2(1) := 'Y';
1021 l_null_location VARCHAR2(1) := 'N';
1022 l_rec_start_date pa_resources_denorm.resource_effective_start_date%TYPE;
1023 l_rec_end_date pa_resources_denorm.resource_effective_end_date%TYPE;
1024
1025
1026 CURSOR denormalized_locations (l_person_id IN NUMBER, l_asgn_start_date IN DATE, l_asgn_end_date IN DATE ) IS
1027 SELECT date_from,
1028 date_to
1029 FROM per_addresses
1030 WHERE person_id = l_person_id
1031 AND date_from <= l_asgn_end_date
1032 AND nvl(date_to, l_asgn_end_date) >= l_asgn_start_date
1033 AND primary_flag = 'Y'
1034 ORDER BY date_from
1035 ;
1036
1037 location_rec denormalized_locations%ROWTYPE;
1038 l_msg_index_out NUMBER; -- bug 5689674
1039
1040 BEGIN
1041
1042
1043 -- Initialize the return status to success
1044 x_return_status := FND_API.G_RET_STS_SUCCESS;
1045
1046
1047 -- The delete logic deletes all overlapping records in pa_resources_denorm
1048 -- for the incoming assignment record
1049 -- This logic also works with location denormalization changes.
1050 DELETE FROM pa_resources_denorm
1051 WHERE person_id = p_resource_source_id
1052 AND resource_effective_start_date <= p_assignment_end_date
1053 AND resource_effective_end_date >= p_assignment_start_date
1054 ;
1055
1056 -- Get common attributes for this HR assignment
1057
1058 -- get thre resource's job level
1059 l_resource_job_level := PA_HR_UPDATE_API.Get_Job_Level(
1060 p_job_id => p_resource_job_id,
1061 p_job_group_id => p_resource_job_group_id);
1062
1063
1064 /* Start of commented code for bug#6875286
1065 Reason to do so:
1066 =================
1067 Instead of deriving billable_flag, utilization_flag and schedulable_flag
1068 from table per_job_extra_info in three different hits, wrote a procedure to directly query
1069 these 3 flags. This would reduce the hits to the table from 3 to 1 and thus improve the performance.
1070
1071 -- get the resource's billable flag
1072 l_billable_flag := PA_HR_UPDATE_API.check_job_billability(
1073 p_job_id => p_resource_job_id,
1074 p_person_id => p_resource_source_id,
1075 p_date => p_assignment_start_date);
1076
1077
1078 -- get the resource's utilization flag
1079 l_utilization_flag := PA_HR_UPDATE_API.check_job_utilization(
1080 p_job_id => p_resource_job_id,
1081 p_person_id => p_resource_source_id,
1082 p_date => p_assignment_start_date);
1083
1084 -- get the resource's job schedulable flag
1085 l_schedulable_flag := PA_HR_UPDATE_API.check_job_schedulable
1086 (p_job_id => p_resource_job_id);
1087
1088 End of commented code for bug#6875286 */
1089
1090 -- Start of code change for bug#6875286
1091
1092 get_all_job_info(p_job_id => p_resource_job_id
1093 ,x_billable_flag => l_billable_flag
1094 ,x_utilization_flag => l_utilization_flag
1095 ,x_schedulable_flag => l_schedulable_flag );
1096
1097 -- End of code change for bug#6875286
1098
1099 /* Bug 2898766 - Raise an exception if the information type attached to this job
1100 contains multiple rows */
1101
1102 IF (l_billable_flag = 'X') OR (l_resource_job_level = -99) OR
1103 (l_utilization_flag = 'X') OR (l_schedulable_flag='X') THEN
1104 /* Bug 5689674: Added code to pass the error message to make the
1105 FND_API.G_EXC_ERROR being raised a generic call for any excpetion. */
1106
1107 PA_UTILS.Add_Message(
1108 p_app_short_name => 'PA'
1109 ,p_msg_name => 'PA_MULT_INF_TYPES_FOR_JOB');
1110 /* End of changes Bug for 5689674 */
1111 RAISE FND_API.G_EXC_ERROR;
1112 END IF;
1113
1114 /* Bug 2898766 - End */
1115
1116 -- Set the employee flag according to the resource type
1117 IF p_resource_type = 'EMPLOYEE' THEN
1118 l_employee_flag:= 'Y';
1119 ELSE
1120 l_employee_flag:= 'N';
1121 END IF;
1122
1123 -- Location denormalization changes involve the following
1124 -- Code loops through all the different location records of a HR assignment
1125 -- and creates 1 record in pa_resources_denorm for every different location
1126 -- of a HR assignment
1127 OPEN denormalized_locations(p_resource_source_id, p_assignment_start_date, p_assignment_end_date);
1128
1129 LOOP
1130 FETCH denormalized_locations INTO location_rec;
1131
1132
1133 IF l_first_time = 'Y' THEN
1134 --dbms_output.put_line('First time in loop');
1135 --dbms_output.put_line('p_assignment_start_date:' || p_assignment_start_date);
1136 --dbms_output.put_line('p_assignment_end_date:' || p_assignment_end_date);
1137
1138 -- Null location record must be populated under the following conditions
1139 -- 1. If no location record is found during this assignment
1140 -- 2. If no location record in the beginning of this assignment
1141
1142 IF denormalized_locations%NOTFOUND THEN
1143 --dbms_output.put_line('No record for location');
1144 l_null_location := 'Y';
1145 l_rec_end_date := p_assignment_end_date;
1146 ELSIF location_rec.date_from > p_assignment_start_date THEN
1147 --dbms_output.put_line('First null location record');
1148 l_null_location := 'Y';
1149 l_rec_end_date := location_rec.date_from - 1;
1150 END IF;
1151
1152 IF l_null_location = 'Y' THEN
1153
1154 l_rec_start_date := p_assignment_start_date;
1155
1156 INSERT INTO PA_RESOURCES_DENORM ( person_id
1157 , resource_id
1158 , resource_name
1159 , resource_type
1160 , resource_org_id
1161 , resource_organization_id
1162 , resource_country_code
1163 , resource_country
1164 , resource_region
1165 , resource_city
1166 , resource_job_level
1167 , resource_effective_start_date
1168 , resource_effective_end_date
1169 , employee_flag
1170 , manager_id
1171 , manager_name
1172 , billable_flag
1173 , job_id
1174 , utilization_flag
1175 , schedulable_flag
1176 , request_id
1177 , program_application_id
1178 , program_id
1179 , program_update_date
1180 , creation_date
1181 , created_by
1182 , last_update_date
1183 , last_updated_by
1184 , last_update_login
1185 , resource_person_type
1186 )
1187 SELECT p_resource_source_id
1188 , p_resource_id
1189 , p_resource_name
1190 , p_resource_type
1191 , p_resource_org_id
1192 , p_resource_organization_id
1193 , l_resource_country_code
1194 , l_resource_country
1195 , l_resource_region
1196 , l_resource_city
1197 , l_resource_job_level
1198 , l_rec_start_date
1199 , l_rec_end_date
1200 , l_employee_flag
1201 , p_manager_id
1202 , p_manager_name
1203 , l_billable_flag
1204 , p_resource_job_id
1205 , l_utilization_flag
1206 , l_schedulable_flag
1207 , p_request_id
1208 , p_program_application_id
1209 , p_program_id
1210 , sysdate
1211 , sysdate
1212 /*----- Bug 1992257 commented
1213 , TO_NUMBER(FND_PROFILE.VALUE('USER_ID'))
1214 , sysdate
1215 , TO_NUMBER(FND_PROFILE.VALUE('USER_ID'))
1216 , TO_NUMBER(FND_PROFILE.VALUE('LOGIN_ID'))
1217 End of Comment, added next 4 lines */
1218 , TO_NUMBER(FND_GLOBAL.USER_ID)
1219 , sysdate
1220 , TO_NUMBER(FND_GLOBAL.USER_ID)
1221 , TO_NUMBER(FND_GLOBAL.LOGIN_ID)
1222 , p_person_type
1223 FROM dual;
1224 --end null location
1225 END IF;
1226 -- end first time
1227 END IF;
1228
1229 l_first_time := 'N';
1230
1231 EXIT WHEN denormalized_locations%NOTFOUND;
1232
1233
1234 -- Initialize the start and end dates and location of the location defined resource records
1235
1236 IF location_rec.date_from <= p_assignment_start_date THEN
1237 l_rec_start_date := p_assignment_start_date;
1238 ELSE
1239 l_rec_start_date := location_rec.date_from;
1240 END IF;
1241
1242 IF location_rec.date_to IS NULL THEN
1243 l_rec_end_date := p_assignment_end_date;
1244 ELSIF location_rec.date_to >= p_assignment_end_date THEN
1245 l_rec_end_date := p_assignment_end_date;
1246 ELSE
1247 l_rec_end_date := location_rec.date_to;
1248 END IF;
1249
1250 --dbms_output.put_line('p_start_date:' || l_rec_start_date);
1251 --dbms_output.put_line('p_end_date:' || l_rec_end_date);
1252
1253
1254 --get the resources locations details
1255 PA_LOCATION_UTILS.Get_EMP_Location_Details(p_person_id => p_resource_source_id,
1256 p_assign_date => l_rec_start_date,
1257 x_country_name => l_resource_country,
1258 x_city => l_resource_city,
1259 x_region => l_resource_region,
1260 x_country_code => l_resource_country_code,
1261 x_return_status => l_return_status,
1262 x_error_message_code => l_error_msg_code);
1263
1264 /* Bug 5689674: Added the below exception handling to throw an error without
1265 proceeding on inserting into pa_resources_denorm. This will show the
1266 error as an exception in the report output. */
1267 IF l_return_status <> FND_API.G_RET_STS_SUCCESS then
1268 IF l_error_msg_code IS NOT NULL THEN
1269 pa_utils.add_message
1270 ( p_app_short_name => 'PA',
1271 p_msg_name => l_error_msg_code);
1272 END IF;
1273 RAISE FND_API.G_EXC_ERROR;
1274 END IF;
1275 /* End of changes for Bug 5689674 */
1276
1277 INSERT INTO PA_RESOURCES_DENORM ( person_id
1278 , resource_id
1279 , resource_name
1280 , resource_type
1281 , resource_org_id
1282 , resource_organization_id
1283 , resource_country_code
1284 , resource_country
1285 , resource_region
1286 , resource_city
1287 , resource_job_level
1288 , resource_effective_start_date
1289 , resource_effective_end_date
1290 , employee_flag
1291 , manager_id
1292 , manager_name
1293 , billable_flag
1294 , job_id
1295 , utilization_flag
1296 , schedulable_flag
1297 , request_id
1298 , program_application_id
1299 , program_id
1300 , program_update_date
1301 , creation_date
1302 , created_by
1303 , last_update_date
1304 , last_updated_by
1305 , last_update_login
1306 , resource_person_type
1307 )
1308 SELECT p_resource_source_id
1309 , p_resource_id
1310 , p_resource_name
1311 , p_resource_type
1312 , p_resource_org_id
1313 , p_resource_organization_id
1314 , l_resource_country_code
1315 , l_resource_country
1316 , l_resource_region
1317 , l_resource_city
1318 , l_resource_job_level
1319 , l_rec_start_date
1320 , l_rec_end_date
1321 , l_employee_flag
1322 , p_manager_id
1323 , p_manager_name
1324 , l_billable_flag
1325 , p_resource_job_id
1326 , l_utilization_flag
1327 , l_schedulable_flag
1328 , p_request_id
1329 , p_program_application_id
1330 , p_program_id
1331 , sysdate
1332 , sysdate
1333 /*----- Bug 1992257 commented
1334 , TO_NUMBER(FND_PROFILE.VALUE('USER_ID'))
1335 , sysdate
1336 , TO_NUMBER(FND_PROFILE.VALUE('USER_ID'))
1337 , TO_NUMBER(FND_PROFILE.VALUE('LOGIN_ID'))
1338 End of Comment, added next 4 lines */
1339 , TO_NUMBER(FND_GLOBAL.USER_ID)
1340 , sysdate
1341 , TO_NUMBER(FND_GLOBAL.USER_ID)
1342 , TO_NUMBER(FND_GLOBAL.LOGIN_ID)
1343 , p_person_type
1344 FROM dual;
1345 END LOOP;
1346
1347 CLOSE denormalized_locations;
1348
1349 x_msg_count := FND_MSG_PUB.Count_Msg;
1350
1351 IF x_msg_count > 0 THEN
1352
1353 x_return_status := FND_API.G_RET_STS_ERROR;
1354
1355 END IF;
1356
1357 IF p_commit = FND_API.G_TRUE AND l_msg_count=0 THEN
1358
1359 COMMIT;
1360
1361 END IF;
1362
1363 EXCEPTION
1364 /* Bug 2898766 - save the error message in the pa_reporting_exceptions table */
1365
1366 WHEN FND_API.G_EXC_ERROR THEN
1367 /* Bug 5689674: Modified the code to make the exception handling generic. */
1368
1369 x_return_status := FND_API.G_RET_STS_ERROR;
1370
1371 -- Reset the error stack when returning to the calling program
1372 PA_DEBUG.Reset_Err_Stack;
1373
1374 -- get the number of messages
1375 x_msg_count := FND_MSG_PUB.Count_Msg;
1376
1377 -- Put a message text from message stack into the x_msg_data if there is only
1378 -- single error.
1379 IF x_msg_count = 1 THEN
1380 pa_interface_utils_pub.get_messages
1381 (p_encoded => FND_API.G_TRUE
1382 ,p_msg_index => 1
1383 ,p_data => x_msg_data
1384 ,p_msg_index_out => l_msg_index_out);
1385 END IF;
1386 /* End of Changes for Bug 5689674*/
1387
1388 /* p_context10 => 'REJECTED' Added for bug 4172140*/
1389 PA_MESSAGE_UTILS.save_messages(
1390 p_request_id => p_request_id
1391 ,p_source_Type1 => 'RESOURCE_PULL'
1392 ,p_source_Type2 => 'PARCPRJR'
1393 ,p_context1 => p_resource_source_id
1394 ,p_context2 => substrb(p_resource_name, 1, 30) --Changed substr to substrb for bug4584297
1395 ,p_context3 => p_resource_organization_id
1396 ,p_context10 => 'REJECTED'
1397 ,p_date_context1 => p_assignment_start_date
1398 ,p_date_context2 => p_assignment_end_date
1399 ,p_commit => p_commit
1400 ,x_return_status => l_return_status);
1401 /* Bug 2898766 - End */
1402 WHEN OTHERS THEN
1403 FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_RESOURCE_PVT.Populate_Resources_Denorm'
1404 , p_procedure_name => PA_DEBUG.G_Err_Stack);
1405 x_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
1406 RAISE;
1407
1408 END Populate_Resources_Denorm;
1409
1410
1411 ------------------------------------------------------------------------------------
1412 --
1413 -- FUNCTION : Get_Manager_Id
1414 -- DESCRIPTION: This function gets the manager_id for a given project. It returns
1415 -- null if there is no manager. Also sets the following global variables
1416 -- g_manager_name - manager name
1417 -- g_manager_resource_id - manager's resource_id from pa_resources_denorm
1418 -- This API is used specifically in views pa_project_open_assns_v
1419 -- and pa_project_open_assns_staff_v
1420 --
1421 ------------------------------------------------------------------------------------
1422 FUNCTION Get_Manager_Id(
1423 p_project_id IN NUMBER)
1424 RETURN NUMBER
1425 IS
1426 l_party_id NUMBER;
1427 BEGIN
1428 SELECT parties.resource_source_id
1429 INTO l_party_id
1430 from pa_project_parties parties
1431 where parties.project_role_id = 1
1432 AND parties.project_id = p_project_id
1433 AND trunc(sysdate) between parties.start_date_active AND NVL(parties.end_date_active,trunc(sysdate) + 1)--;
1434 -- start -- 18-AUG-2008 cklee Fixed bug: 6708404
1435 and object_id = p_project_id
1436 and object_type = 'PA_PROJECTS'
1437 and resource_type_id = 101;
1438 -- end -- 18-AUG-2008 cklee Fixed bug: 6708404
1439
1440 select resource_name
1441 into g_manager_name
1442 from pa_resources_denorm
1443 where person_id = l_party_id
1444 and trunc(sysdate) between resource_effective_start_date and resource_effective_end_date;
1445
1446 select resource_id
1447 into g_manager_resource_id
1448 from pa_resources_denorm
1449 where person_id = l_party_id
1450 and trunc(sysdate) between resource_effective_start_date and resource_effective_end_date;
1451
1452 g_project_id := p_project_id; /* Bug#2604495 Setting the Global variable's value */
1453
1454 RETURN l_party_id;
1455 EXCEPTION
1456 WHEN NO_DATA_FOUND THEN
1457 g_manager_name := null;
1458 g_manager_resource_id := null;
1459 g_project_id := null; /* Bug#2604495 Setting the global variable as null if no data found occurs*/
1460 RETURN NULL;
1461 END;
1462
1463 ------------------------------------------------------------------------------------
1464 --
1465 -- FUNCTION : Get_Manager_Name
1466 -- DESCRIPTION: This function gets the manager_name from the global variable
1467 -- g_manager_name - manager name
1468 -- This API is used specifically in views pa_project_open_assns_v
1469 -- and pa_project_open_assns_staff_v
1470 --
1471 ------------------------------------------------------------------------------------
1472 FUNCTION Get_Manager_Name(p_project_id in number DEFAULT null) /*Bug#2604495-Added the parameter p_project_id to the function */
1473 RETURN VARCHAR2
1474 is
1475 l_party_id number(15); /* 2604495-Added local variable */
1476 BEGIN
1477 /* code for bug#2604495 starts */
1478
1479 if p_project_id = g_project_id then
1480 null;
1481 else
1482 l_party_id := get_manager_id(p_project_id);
1483 end if;
1484
1485 /* Code for bug#2604495 ends */
1486 RETURN g_manager_name;
1487 END;
1488
1489 ------------------------------------------------------------------------------------
1490 --
1491 -- FUNCTION : Get_Manager_Resource_Id
1492 -- DESCRIPTION: This function gets the manager's resource_id from the global variable
1493 -- g_manager_resource_id - manager's resource_id
1494 -- This API is used specifically in views pa_project_open_assns_v
1495 -- and pa_project_open_assns_staff_v
1496 --
1497 ------------------------------------------------------------------------------------
1498 FUNCTION Get_Manager_Resource_Id
1499 RETURN NUMBER
1500 IS
1501 BEGIN
1502 RETURN g_manager_resource_id;
1503 END;
1504
1505 ------------------------------------------------------------------------------------
1506 --
1507 -- FUNCTION : Get_Resource_Avl_to_date
1508 -- DESCRIPTION: This function gets the resource available to date for staffing
1509 -- home view objects. This function is modified for PA.K using a new
1510 -- data model (PA_RES_AVAILABILITY) to get availability information.
1511 --
1512 ------------------------------------------------------------------------------------
1513 FUNCTION Get_Resource_Avl_To_Date (p_resource_id IN NUMBER,
1514 p_avl_from_date IN DATE)
1515 RETURN DATE
1516 IS
1517 l_avl_to_date DATE;
1518 l_avl_profile NUMBER := FND_NUMBER.CANONICAL_TO_NUMBER(fnd_profile.value('PA_RES_CAPACITY_PERCENTAGE')); -- 4725606
1519 BEGIN
1520
1521 IF p_avl_from_date is not null THEN
1522
1523 select min(start_date)-1
1524 into l_avl_to_date
1525 from pa_res_availability
1526 where resource_id = p_resource_id
1527 and record_type = 'C'
1528 and start_date > p_avl_from_date
1529 -- Commented for 4725606 and percent < FND_NUMBER.CANONICAL_TO_NUMBER(fnd_profile.value('PA_RES_CAPACITY_PERCENTAGE'));
1530 and percent < l_avl_profile ; -- 4725606
1531
1532 ELSE
1533 l_avl_to_date := null;
1534 END IF;
1535
1536 -- we do not want to show 31-Dec-4712 as the available to date
1537 -- so perform the following logic to show it as null instead
1538 IF l_avl_to_date IS NOT NULL THEN
1539 IF to_char(l_avl_to_date, 'DD-MM-YYYY') = '31-12-4712' THEN
1540 l_avl_to_date := null;
1541 END IF;
1542 END IF;
1543
1544
1545 RETURN l_avl_to_date;
1546
1547 EXCEPTION
1548 WHEN OTHERS THEN
1549 RETURN null;
1550 END;
1551
1552 ------------------------------------------------------------------------------------
1553 --
1554 -- FUNCTION : Get_Resource_ovc_to_date
1555 -- DESCRIPTION: This function gets the resource overcommited to date for staffing
1556 -- home view objects. This function is modified for PA.K using a new
1557 -- data model (PA_RES_AVAILABILITY) to get overcommitment information.
1558 --
1559 ------------------------------------------------------------------------------------
1560 FUNCTION Get_Resource_Ovc_To_Date (p_resource_id IN NUMBER,
1561 p_ovc_from_date IN DATE)
1562 RETURN DATE
1563 IS
1564 l_ovc_to_date DATE;
1565 l_ovc_profile NUMBER := FND_NUMBER.CANONICAL_TO_NUMBER(fnd_profile.value('PA_OVERCOMMITMENT_PERCENTAGE')); -- 4725606
1566 BEGIN
1567
1568 IF p_ovc_from_date is not null THEN
1569 select min(start_date)-1
1570 into l_ovc_to_date
1571 from pa_res_availability
1572 where resource_id = p_resource_id
1573 and record_type = 'C'
1574 and start_date > p_ovc_from_date
1575 -- Commented for 4725606 and -1 * percent < FND_NUMBER.CANONICAL_TO_NUMBER(fnd_profile.value('PA_OVERCOMMITMENT_PERCENTAGE'));
1576 and -1 * percent < l_ovc_profile ; -- 4725606
1577 ELSE
1578 l_ovc_to_date := null;
1579 END IF;
1580
1581 -- we do not want to show 31-Dec-4712 as the available to date
1582 -- so perform the following logic to show it as null instead
1583 IF l_ovc_to_date IS NOT NULL THEN
1584 IF to_char(l_ovc_to_date, 'DD-MM-YYYY') = '31-12-4712' THEN
1585 l_ovc_to_date := null;
1586 END IF;
1587 END IF;
1588
1589
1590 RETURN l_ovc_to_date;
1591
1592 EXCEPTION
1593 WHEN OTHERS THEN
1594 RETURN null;
1595 END;
1596
1597 ------------------------------------------------------------------------------------
1598 --
1599 -- FUNCTION : Get_Resource_ovc_hours
1600 -- DESCRIPTION: This function gets the resource overcommited hours for the given
1601 -- from and to dates using pa_res_availability table.
1602 --
1603 ------------------------------------------------------------------------------------
1604 FUNCTION Get_Resource_Ovc_hours(p_resource_id IN NUMBER,
1605 p_ovc_from_date IN DATE,
1606 p_ovc_to_date IN DATE)
1607 RETURN NUMBER
1608 IS
1609 l_ovc_hours NUMBER;
1610 BEGIN
1611
1612 IF p_ovc_from_date is null OR p_ovc_to_date is null THEN
1613 l_ovc_hours := null;
1614 ELSE
1615 -- have to multiply by -1 since we store overcommitment as
1616 -- negative availability
1617 select sum(hours) * -1
1618 into l_ovc_hours
1619 from pa_res_availability
1620 where resource_id = p_resource_id
1621 and record_type = 'C'
1622 and start_date >= p_ovc_from_date
1623 and end_date <= p_ovc_to_date;
1624 END IF;
1625
1626 RETURN l_ovc_hours;
1627
1628 EXCEPTION
1629 WHEN OTHERS THEN
1630 RETURN null;
1631 END;
1632
1633
1634 -- PROCEDURE
1635 -- Validate_Staff_Filter_Values
1636 -- PURPOSE
1637 -- Specifically for staffing pages use.
1638 -- Currrently used by StaffingHomeAMImpl and ResourceListAMImpl.
1639 -- This procedure validates the organization or/and manager
1640 -- parameters used in the staffing filters. It requires p_responsibility=RM
1641 -- if the user has resource manager responsibility and p_check=Y
1642 -- if the manager_name contains % character (from My Resources page).
1643 -- HISTORY
1644 -- 20-AUG-2002 Created adabdull
1645 --+
1646 PROCEDURE Validate_Staff_Filter_Values(
1647 p_manager_name IN VARCHAR2
1648 ,p_manager_id IN NUMBER
1649 ,p_org_name IN VARCHAR2
1650 ,p_org_id IN NUMBER
1651 ,p_responsibility IN VARCHAR2
1652 ,p_check IN VARCHAR2
1653 ,x_manager_id OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
1654 ,x_org_id OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
1655 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1656 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
1657 ,x_msg_data OUT NOCOPY VARCHAR2 ) --File.Sql.39 bug 4440895
1658 IS
1659 l_return_status VARCHAR2(1);
1660 l_msg_data VARCHAR2(2000);
1661 l_error_message_code fnd_new_messages.message_name%TYPE;
1662 l_resource_type_id NUMBER;
1663 l_msg_index_out NUMBER;
1664 l_msg_count NUMBER;
1665 BEGIN
1666
1667 -- initialize error stack
1668 fnd_msg_pub.initialize;
1669
1670 -- set return status to Success
1671 x_return_status := FND_API.G_RET_STS_SUCCESS;
1672
1673 IF(p_org_name IS NOT NULL) THEN
1674 -- check whether organization name is valid
1675 PA_HR_ORG_UTILS.Check_OrgName_Or_Id (
1676 p_organization_id => p_org_id,
1677 p_organization_name => p_org_name,
1678 p_check_id_flag => PA_STARTUP.G_Check_ID_Flag,
1679 x_organization_id => x_org_id,
1680 x_return_status => l_return_status,
1681 x_error_msg_code => l_error_message_code);
1682
1683 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1684 PA_UTILS.Add_Message (
1685 p_app_short_name => 'PA'
1686 ,p_msg_name => l_error_message_code );
1687 END IF;
1688 ELSE
1689 x_org_id := NULL;
1690 END IF;
1691
1692 IF(p_manager_name IS NOT NULL) THEN
1693
1694 -- validate whether the manager name entered or selected in the LOV
1695 -- exists in the corresponding view depending on user's responsibility
1696 PA_RESOURCE_UTILS.Check_ManagerName_Or_Id(
1697 p_manager_name => p_manager_name
1698 ,p_manager_id => p_manager_id
1699 ,p_responsibility => p_responsibility
1700 ,p_check => p_check
1701 ,x_manager_id => x_manager_id
1702 ,x_msg_count => l_msg_count
1703 ,x_return_status => l_return_status
1704 ,x_error_message_code => l_error_message_code);
1705
1706 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1707 PA_UTILS.Add_Message(
1708 p_app_short_name => 'PA'
1709 ,p_msg_name => l_error_message_code);
1710 END IF;
1711 ELSE
1712 x_manager_id := NULL;
1713 END IF;
1714
1715 x_msg_count := FND_MSG_PUB.Count_Msg;
1716
1717 IF x_msg_count = 1 THEN
1718 pa_interface_utils_pub.get_messages ( p_encoded => FND_API.G_TRUE
1719 ,p_msg_index => 1
1720 ,p_data => x_msg_data
1721 ,p_msg_index_out => l_msg_index_out);
1722 END IF;
1723
1724 IF x_msg_count <> 0 THEN
1725 x_return_status := FND_API.G_RET_STS_ERROR;
1726 END IF;
1727
1728 EXCEPTION
1729 WHEN OTHERS THEN
1730 x_manager_id := NULL;
1731 x_org_id := NULL;
1732 x_return_status := FND_API.G_RET_STS_ERROR;
1733 END Validate_Staff_Filter_Values;
1734
1735 FUNCTION get_res_conf_availability( p_resource_id IN NUMBER,
1736 p_start_date IN DATE,
1737 p_end_date IN DATE)
1738 RETURN NUMBER
1739 IS
1740 l_conf_availability NUMBER;
1741
1742 BEGIN
1743
1744
1745 BEGIN
1746
1747 SELECT sum(nvl(capacity_quantity,0) - nvl(confirmed_qty,0))
1748 INTO l_conf_availability
1749 FROM pa_forecast_items
1750 WHERE resource_id = p_resource_id
1751 AND delete_flag = 'N'
1752 AND forecast_item_type = 'U'
1753 AND item_date between p_start_date
1754 and p_end_date;
1755 EXCEPTION
1756 WHEN NO_DATA_FOUND THEN
1757 l_conf_availability := 0;
1758 END;
1759
1760
1761 RETURN l_conf_availability;
1762 EXCEPTION
1763 WHEN OTHERS THEN
1764 RETURN 0;
1765 END get_res_conf_availability;
1766
1767 FUNCTION get_res_prov_conf_availability( p_resource_id IN NUMBER,
1768 p_start_date IN DATE,
1769 p_end_date IN DATE)
1770 RETURN NUMBER
1771 IS
1772 l_prov_conf_availability NUMBER;
1773
1774 BEGIN
1775
1776
1777 BEGIN
1778
1779 SELECT sum(nvl(capacity_quantity,0) - nvl(confirmed_qty,0) - nvl(provisional_qty,0))
1780 INTO l_prov_conf_availability
1781 FROM pa_forecast_items
1782 WHERE resource_id = p_resource_id
1783 AND delete_flag = 'N'
1784 AND forecast_item_type = 'U'
1785 AND item_date between p_start_date
1786 and p_end_date;
1787 EXCEPTION
1788 WHEN NO_DATA_FOUND THEN
1789 l_prov_conf_availability := 0;
1790 END;
1791
1792
1793 RETURN l_prov_conf_availability;
1794 EXCEPTION
1795 WHEN OTHERS THEN
1796 RETURN 0;
1797 END get_res_prov_conf_availability;
1798
1799 -------------------------------------------------------------------------------+
1800 --
1801 -- PROCEDURE : Populate_Res_Availability
1802 -- DESCRIPTION : This Procedure populates PA_RES_AVAILABILITY for the resource
1803 -- for the given dates
1804 -- It populates the following data slices
1805 -- - (Confirmed) Availability/Overcommittment
1806 -- - (Confirmed + Provisional) Availability/Overcommittment
1807 -- This procedure is also called from the upgrade script
1808 -- used to populate PA_RES_AVAILABILITY
1809 --
1810 --------------------------------------------------------------------------------+
1811 PROCEDURE populate_res_availability (
1812 p_resource_id IN NUMBER,
1813 p_cstart_date IN DATE,
1814 p_cend_date IN DATE,
1815 p_bstart_date IN DATE,
1816 p_bend_date IN DATE,
1817 x_return_status OUT NOCOPY VARCHAR2 ) --File.Sql.39 bug 4440895
1818 IS
1819
1820 --l_profile varchar2(30) := 'DAILY';
1821 l_created_by number;
1822 l_last_updated_by number;
1823 l_last_update_login number;
1824 l_start_date date;
1825 l_end_date date;
1826 l_percent number := -1;
1827 l_earliest_start_date date;
1828 l_rec_count number:= 0;
1829
1830 BEGIN
1831
1832 -- Initialize the return status to success
1833 x_return_status := FND_API.G_RET_STS_SUCCESS;
1834
1835 l_created_by := fnd_global.user_id;
1836 l_last_updated_by := fnd_global.user_id;
1837 l_last_update_login := fnd_global.login_id;
1838
1839 -- commented out for perf bug 4930256
1840 -- l_profile := FND_PROFILE.VALUE_SPECIFIC('PA_AVAILABILITY_CAL_PERIOD');
1841
1842 --Dynamic SQL is being used as Oracle 8i PL/SQL does not support
1843 --Analytic functions
1844
1845 IF l_profile = 'DAILY' THEN
1846
1847 --For Confirmed Availability/Overcommittment
1848 /* Commented dynamix SQL and replaced with bind variables for Bug #3964268
1849 execute immediate
1850 'insert into PA_RES_AVAILABILITY
1851 ( start_date,
1852 end_date,
1853 percent,
1854 record_type,
1855 resource_id,
1856 hours,
1857 creation_date,
1858 last_update_date,
1859 created_by,
1860 last_updated_by,
1861 last_update_login
1862 )
1863 select item_date,
1864 (lead(item_date) over(order by item_date)) - 1,
1865 c * 100 ,
1866 ' || '''C''' ||
1867 ' , resource_id,
1868 pa_resource_pvt.get_res_conf_availability(resource_id, nvl(item_date,' || '''' || p_cstart_date || ''''|| '),
1869 nvl((lead(item_date) over(order by item_date)),' || '''' || p_cend_date || ''''|| ') - 1),
1870 sysdate,
1871 sysdate,
1872 ' || l_created_by || ',
1873 ' || l_last_updated_by || ',
1874 ' || l_last_update_login || '
1875 from ( select (nvl(capacity_quantity,0) - nvl(confirmed_qty,0))/nvl(capacity_quantity,0) a,
1876 lag((nvl(capacity_quantity,0) - nvl(confirmed_qty,0))/nvl(capacity_quantity,0),1,1000) -- bug 3229929
1877 over ( order by item_date) as b,
1878 (nvl(capacity_quantity,0) - nvl(confirmed_qty,0))/capacity_quantity c,
1879 item_date,
1880 resource_id
1881 from pa_forecast_items
1882 where forecast_item_type = ' || '''U''' ||
1883 ' and resource_id = ' || p_resource_id || '
1884 and capacity_quantity > 0
1885 and delete_flag = ' || '''N''' || '
1886 and item_date between ' || '''' || p_cstart_date || '''' || ' and ' ||
1887 '''' || p_cend_date || ''''|| ')
1888 where nvl(a,0) - nvl(b,0) <> 0';
1889 */
1890 /* Modified the above commented SQL as below for Bug #3964268 with bind variables */
1891 execute immediate
1892 'insert into PA_RES_AVAILABILITY
1893 ( start_date,
1894 end_date,
1895 percent,
1896 record_type,
1897 resource_id,
1898 hours,
1899 creation_date,
1900 last_update_date,
1901 created_by,
1902 last_updated_by,
1903 last_update_login
1904 )
1905 select item_date,
1906 (lead(item_date) over(order by item_date)) - 1,
1907 c * 100 ,
1908 :c,
1909 resource_id,
1910 pa_resource_pvt.get_res_conf_availability(resource_id, nvl(item_date, :cstart_date ),
1911 nvl((lead(item_date) over(order by item_date)), :cend_date ) - 1),
1912 sysdate,
1913 sysdate,
1914 :created_by,
1915 :last_updated_by,
1916 :last_update_login
1917 from ( select (nvl(capacity_quantity,0) - nvl(confirmed_qty,0))/nvl(capacity_quantity,0) a,
1918 lag((nvl(capacity_quantity,0) - nvl(confirmed_qty,0))/nvl(capacity_quantity,0),1,1000) -- bug 3229929
1919 over ( order by item_date) as b,
1920 (nvl(capacity_quantity,0) - nvl(confirmed_qty,0))/capacity_quantity c,
1921 item_date,
1922 resource_id
1923 from pa_forecast_items
1924 where forecast_item_type = :u
1925 and resource_id = :resource_id
1926 and capacity_quantity > 0
1927 and delete_flag = :n
1928 and item_date between :cstart_date and :cend_date )
1929 where nvl(a,0) - nvl(b,0) <> 0' using 'C', p_cstart_date, p_cend_date, l_created_by,
1930 l_last_updated_by, l_last_update_login, 'U', p_resource_id, 'N', p_cstart_date, p_cend_date;
1931
1932 --For Confirmed+Provisional Availability/Overcommittment
1933 /* Commented dynamix SQL and replaced with bind variables for Bug #3964268
1934 execute immediate
1935 'insert into PA_RES_AVAILABILITY
1936 ( start_date,
1937 end_date,
1938 percent,
1939 record_type,
1940 resource_id,
1941 hours,
1942 creation_date,
1943 last_update_date,
1944 created_by,
1945 last_updated_by,
1946 last_update_login
1947 )
1948 select item_date,
1949 (lead(item_date) over(order by item_date)) - 1,
1950 c * 100 ,
1951 ' || '''B''' ||
1952 ' , resource_id,
1953 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),
1954 sysdate,
1955 sysdate,
1956 ' || l_created_by || ' ,
1957 ' || l_last_updated_by || ' ,
1958 ' || l_last_update_login || '
1959 from ( select (nvl(capacity_quantity,0) - nvl(confirmed_qty,0) - nvl(provisional_qty,0))/nvl(capacity_quantity,0) a,
1960 lag((nvl(capacity_quantity,0) - nvl(confirmed_qty,0) - nvl(provisional_qty,0))/nvl(capacity_quantity,0))
1961 over ( order by item_date) as b,
1962 (nvl(capacity_quantity,0) - nvl(confirmed_qty,0) - nvl(provisional_qty,0))/capacity_quantity c,
1963 item_date,
1964 resource_id
1965 from pa_forecast_items
1966 where forecast_item_type = ' || '''U''' ||
1967 ' and resource_id = ' || p_resource_id || '
1968 and capacity_quantity > 0
1969 and delete_flag = ' || '''N''' || '
1970 and item_date between ' || '''' || p_bstart_date || '''' || ' and ' ||
1971 '''' || p_bend_date || ''''|| ')
1972 where nvl(a,0) - nvl(b,0) <> 0';
1973 */
1974 /* Modified the above commented SQL as below for Bug #3964268 with bind variables */
1975 execute immediate
1976 'insert into PA_RES_AVAILABILITY
1977 ( start_date,
1978 end_date,
1979 percent,
1980 record_type,
1981 resource_id,
1982 hours,
1983 creation_date,
1984 last_update_date,
1985 created_by,
1986 last_updated_by,
1987 last_update_login
1988 )
1989 select item_date,
1990 (lead(item_date) over(order by item_date)) - 1,
1991 c * 100 ,
1992 :b,
1993 resource_id,
1994 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),
1995 sysdate,
1996 sysdate,
1997 :created_by,
1998 :last_updated_by,
1999 :last_update_login
2000 from ( select (nvl(capacity_quantity,0) - nvl(confirmed_qty,0) - nvl(provisional_qty,0))/nvl(capacity_quantity,0) a,
2001 lag((nvl(capacity_quantity,0) - nvl(confirmed_qty,0) - nvl(provisional_qty,0))/nvl(capacity_quantity,0))
2002 over ( order by item_date) as b,
2003 (nvl(capacity_quantity,0) - nvl(confirmed_qty,0) - nvl(provisional_qty,0))/capacity_quantity c,
2004 item_date,
2005 resource_id
2006 from pa_forecast_items
2007 where forecast_item_type = :u
2008 and resource_id = :resource_id
2009 and capacity_quantity > 0
2010 and delete_flag = :n
2011 and item_date between :bstart_date and :bend_date )
2012 where nvl(a,0) - nvl(b,0) <> 0' using 'B', p_bstart_date, p_bend_date, l_created_by,
2013 l_last_updated_by, l_last_update_login, 'U', p_resource_id, 'N', p_bstart_date, p_bend_date;
2014
2015 --Logic to update the null dates due to usage of lead/lag
2016 update pa_res_availability avl
2017 set start_date = p_cstart_date
2018 where resource_id = p_resource_id
2019 and record_type = 'C'
2020 and start_date is null;
2021
2022 update pa_res_availability avl
2023 set end_date = p_cend_date
2024 where resource_id = p_resource_id
2025 and record_type = 'C'
2026 and end_date is null;
2027
2028 --Logic to insert 0 percent records in the beginning and end
2029 select min(start_date)
2030 into l_start_date
2031 from pa_res_availability
2032 where resource_id = p_resource_id
2033 and record_type = 'C';
2034
2035 IF l_start_date is not null THEN
2036
2037 select percent
2038 into l_percent
2039 from pa_res_availability
2040 where resource_id = p_resource_id
2041 and record_type = 'C'
2042 and start_date = l_start_date;
2043
2044 END IF;
2045
2046 IF l_percent <> 0 THEN
2047
2048 insert into PA_RES_AVAILABILITY
2049 ( start_date,
2050 end_date,
2051 percent,
2052 record_type,
2053 resource_id,
2054 creation_date,
2055 last_update_date,
2056 created_by,
2057 last_updated_by,
2058 last_update_login)
2059 select min(start_date) - 1,
2060 min(start_date) - 1,
2061 0,
2062 record_type,
2063 resource_id,
2064 sysdate,
2065 sysdate,
2066 l_created_by,
2067 l_last_updated_by,
2068 l_last_update_login
2069 from pa_res_availability
2070 where resource_id = p_resource_id
2071 and record_type = 'C'
2072 group by 0,
2073 record_type,
2074 resource_id,
2075 sysdate,
2076 sysdate,
2077 l_created_by,
2078 l_last_updated_by,
2079 l_last_update_login;
2080 END IF;
2081
2082
2083 select max(end_date)
2084 into l_end_date
2085 from pa_res_availability
2086 where resource_id = p_resource_id
2087 and record_type = 'C';
2088
2089 IF l_end_date is not null THEN
2090
2091 select percent
2092 into l_percent
2093 from pa_res_availability
2094 where resource_id = p_resource_id
2095 and record_type = 'C'
2096 and end_date = l_end_date;
2097 END IF;
2098
2099 IF l_percent <> 0 THEN
2100
2101 insert into PA_RES_AVAILABILITY
2102 ( start_date,
2103 end_date,
2104 percent,
2105 record_type,
2106 resource_id,
2107 creation_date,
2108 last_update_date,
2109 created_by,
2110 last_updated_by,
2111 last_update_login)
2112 select max(end_date) + 1,
2113 max(end_date) + 1,
2114 0,
2115 record_type,
2116 resource_id,
2117 sysdate,
2118 sysdate,
2119 l_created_by,
2120 l_last_updated_by,
2121 l_last_update_login
2122 from pa_res_availability
2123 where resource_id = p_resource_id
2124 and record_type = 'C'
2125 group by 0,
2126 record_type,
2127 resource_id,
2128 sysdate,
2129 sysdate,
2130 l_created_by,
2131 l_last_updated_by,
2132 l_last_update_login;
2133 END IF;
2134
2135 --Logic to update the null dates due to usage of lead/lag
2136 update pa_res_availability avl
2137 set start_date = p_bstart_date
2138 where resource_id = p_resource_id
2139 and record_type = 'B'
2140 and start_date is null;
2141
2142 update pa_res_availability avl
2143 set end_date = p_bend_date
2144 where resource_id = p_resource_id
2145 and record_type = 'B'
2146 and end_date is null;
2147
2148 --Logic to insert 0 percent records in the beginning and end
2149 select min(start_date)
2150 into l_start_date
2151 from pa_res_availability
2152 where resource_id = p_resource_id
2153 and record_type = 'B';
2154
2155 IF l_start_date is not null THEN
2156
2157 select percent
2158 into l_percent
2159 from pa_res_availability
2160 where resource_id = p_resource_id
2161 and record_type = 'B'
2162 and start_date = l_start_date;
2163
2164 END IF;
2165
2166 IF l_percent <> 0 THEN
2167
2168
2169 insert into PA_RES_AVAILABILITY
2170 ( start_date,
2171 end_date,
2172 percent,
2173 record_type,
2174 resource_id,
2175 creation_date,
2176 last_update_date,
2177 created_by,
2178 last_updated_by,
2179 last_update_login)
2180 select min(start_date) - 1,
2181 min(start_date) - 1,
2182 0,
2183 record_type,
2184 resource_id,
2185 sysdate,
2186 sysdate,
2187 l_created_by,
2188 l_last_updated_by,
2189 l_last_update_login
2190 from pa_res_availability
2191 where resource_id = p_resource_id
2192 and record_type = 'B'
2193 group by 0,
2194 record_type,
2195 resource_id,
2196 sysdate,
2197 sysdate,
2198 l_created_by,
2199 l_last_updated_by,
2200 l_last_update_login;
2201 END IF;
2202
2203 select max(end_date)
2204 into l_end_date
2205 from pa_res_availability
2206 where resource_id = p_resource_id
2207 and record_type = 'B';
2208
2209 IF l_end_date is not null THEN
2210
2211 select percent
2212 into l_percent
2213 from pa_res_availability
2214 where resource_id = p_resource_id
2215 and record_type = 'B'
2216 and end_date = l_end_date;
2217
2218 END IF;
2219
2220 IF l_percent <> 0 THEN
2221
2222 insert into PA_RES_AVAILABILITY
2223 ( start_date,
2224 end_date,
2225 percent,
2226 record_type,
2227 resource_id,
2228 creation_date,
2229 last_update_date,
2230 created_by,
2231 last_updated_by,
2232 last_update_login)
2233 select max(end_date) + 1,
2234 max(end_date) + 1,
2235 0,
2236 record_type,
2237 resource_id,
2238 sysdate,
2239 sysdate,
2240 l_created_by,
2241 l_last_updated_by,
2242 l_last_update_login
2243 from pa_res_availability
2244 where resource_id = p_resource_id
2245 and record_type = 'B'
2246 group by 0,
2247 record_type,
2248 resource_id,
2249 sysdate,
2250 sysdate,
2251 l_created_by,
2252 l_last_updated_by,
2253 l_last_update_login;
2254 END IF;
2255
2256 ELSIF l_profile = 'WEEKLY' THEN
2257
2258 --dbms_output.put_line('Profile is weekly');
2259
2260 --For Confirmed 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 ' || '''C''' ||
2280 ' , resource_id,
2281 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),
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(a1,1) a,
2289 lag((nvl(a1,0) - nvl(a2,0))/nvl(a1,1))
2290 over ( order by d) as b,
2291 (nvl(a1,0) - nvl(a2,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 from pa_forecast_items
2300 where forecast_item_type = ' || '''U''' ||
2301 'and resource_id = ' || p_resource_id || '
2302 and capacity_quantity > 0
2303 and delete_flag = ' || '''N''' || '
2304 and item_date between ' || '''' || p_cstart_date || '''' || ' and ' ||
2305 '''' || p_cend_date || ''''||
2306 'group by global_exp_period_end_date - 6,
2307 resource_id))
2308 where nvl(a,0) - nvl(b,0) <> 0 ';
2309 */
2310 /* Modified the above commented SQL as below for Bug #3964268 with bind variables */
2311 execute immediate
2312 'insert into PA_RES_AVAILABILITY
2313 ( start_date,
2314 end_date,
2315 percent,
2316 record_type,
2317 resource_id,
2318 hours,
2319 creation_date,
2320 last_update_date,
2321 created_by,
2322 last_updated_by,
2323 last_update_login
2324 )
2325 select item_date,
2326 (lead(item_date) over(order by item_date)) - 1,
2327 c * 100 ,
2328 :c,
2329 resource_id,
2330 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),
2331 sysdate,
2332 sysdate,
2333 :created_by,
2334 :last_updated_by,
2335 :last_update_login
2336 from (
2337 select (nvl(a1,0) - nvl(a2,0))/nvl(a1,1) a,
2338 lag((nvl(a1,0) - nvl(a2,0))/nvl(a1,1))
2339 over ( order by d) as b,
2340 (nvl(a1,0) - nvl(a2,0))/nvl(a1,1) c,
2341 d item_date,
2342 resource_id
2343 from (
2344 select (global_exp_period_end_date - 6) d,
2345 resource_id,
2346 sum(capacity_quantity) a1,
2347 sum(confirmed_qty) a2
2348 from pa_forecast_items
2349 where forecast_item_type = :u
2350 and resource_id = :resource_id
2351 and capacity_quantity > 0
2352 and delete_flag = :n
2353 and item_date between :cstart_date and :cend_date
2354 group by global_exp_period_end_date - 6,
2355 resource_id))
2356 where nvl(a,0) - nvl(b,0) <> 0 ' using 'C', p_cstart_date, p_cend_date, l_created_by,
2357 l_last_updated_by, l_last_update_login, 'U', p_resource_id, 'N', p_cstart_date, p_cend_date;
2358
2359 --For Confirmed+Provisional Availability/Overcommittment
2360 /* Commented dynamix SQL and replaced with bind variables for Bug #3964268
2361 execute immediate
2362 'insert into PA_RES_AVAILABILITY
2363 ( start_date,
2364 end_date,
2365 percent,
2366 record_type,
2367 resource_id,
2368 hours,
2369 creation_date,
2370 last_update_date,
2371 created_by,
2372 last_updated_by,
2373 last_update_login
2374 )
2375 select item_date,
2376 (lead(item_date) over(order by item_date)) - 1,
2377 c * 100 ,
2378 ' || '''B''' ||
2379 ' , resource_id,
2380 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),
2381 sysdate,
2382 sysdate,
2383 ' || l_created_by || ',
2384 ' || l_last_updated_by ||',
2385 ' || l_last_update_login || '
2386 from (
2387 select (nvl(a1,0) - nvl(a2,0) - nvl(a3,0))/nvl(a1,1) a,
2388 lag((nvl(a1,0) - nvl(a2,0) - nvl(a3,0))/nvl(a1,1))
2389 over ( order by d) as b,
2390 (nvl(a1,0) - nvl(a2,0) - nvl(a3,0))/nvl(a1,1) c,
2391 d item_date,
2392 resource_id
2393 from (
2394 select (global_exp_period_end_date - 6) d,
2395 resource_id,
2396 sum(capacity_quantity) a1,
2397 sum(confirmed_qty) a2,
2398 sum(provisional_qty) a3
2399 from pa_forecast_items
2400 where forecast_item_type = ' || '''U''' ||
2401 'and resource_id = ' || p_resource_id || '
2402 and capacity_quantity > 0
2403 and delete_flag = ' || '''N''' || '
2404 and item_date between ' || '''' || p_bstart_date || '''' || ' and ' ||
2405 '''' || p_bend_date || ''''||
2406 'group by global_exp_period_end_date - 6,
2407 resource_id))
2408 where nvl(a,0) - nvl(b,0) <> 0 ';
2409 */
2410 /* Modified the above commented SQL as below for Bug #3964268 with bind variables */
2411 execute immediate
2412 'insert into PA_RES_AVAILABILITY
2413 ( start_date,
2414 end_date,
2415 percent,
2416 record_type,
2417 resource_id,
2418 hours,
2419 creation_date,
2420 last_update_date,
2421 created_by,
2422 last_updated_by,
2423 last_update_login
2424 )
2425 select item_date,
2426 (lead(item_date) over(order by item_date)) - 1,
2427 c * 100 ,
2428 :b,
2429 resource_id,
2430 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),
2431 sysdate,
2432 sysdate,
2433 :created_by,
2434 :last_updated_by,
2435 :last_update_login
2436 from (
2437 select (nvl(a1,0) - nvl(a2,0) - nvl(a3,0))/nvl(a1,1) a,
2438 lag((nvl(a1,0) - nvl(a2,0) - nvl(a3,0))/nvl(a1,1))
2439 over ( order by d) as b,
2440 (nvl(a1,0) - nvl(a2,0) - nvl(a3,0))/nvl(a1,1) c,
2441 d item_date,
2442 resource_id
2443 from (
2444 select (global_exp_period_end_date - 6) d,
2445 resource_id,
2446 sum(capacity_quantity) a1,
2447 sum(confirmed_qty) a2,
2448 sum(provisional_qty) a3
2449 from pa_forecast_items
2450 where forecast_item_type = :u
2451 and resource_id = :resource_id
2452 and capacity_quantity > 0
2453 and delete_flag = :n
2454 and item_date between :bstart_date and :bend_date
2455 group by global_exp_period_end_date - 6,
2456 resource_id))
2457 where nvl(a,0) - nvl(b,0) <> 0 ' using 'B', p_bstart_date, p_bend_date, l_created_by,
2458 l_last_updated_by, l_last_update_login, 'U', p_resource_id, 'N', p_bstart_date, p_bend_date;
2459
2460 --dbms_output.put_line('After Insert');
2461
2462 --Logic to update the null dates due to usage of lead/lag
2463 update pa_res_availability avl
2464 set start_date = p_cstart_date
2465 where resource_id = p_resource_id
2466 and record_type = 'C'
2467 and start_date is null;
2468
2469 --dbms_output.put_line('After C Update 1');
2470
2471 update pa_res_availability avl
2472 set end_date = p_cend_date
2473 where resource_id = p_resource_id
2474 and record_type = 'C'
2475 and end_date is null;
2476
2477 --dbms_output.put_line('After C Update 2');
2478
2479 --Logic to insert 0 percent records in the beginning and end
2480 select min(start_date)
2481 into l_start_date
2482 from pa_res_availability
2483 where resource_id = p_resource_id
2484 and record_type = 'C';
2485
2486 IF l_start_date is not null THEN
2487
2488 BEGIN
2489 select percent
2490 into l_percent
2491 from pa_res_availability
2492 where resource_id = p_resource_id
2493 and record_type = 'C'
2494 and start_date = l_start_date
2495 and percent = 0;
2496 EXCEPTION
2497 WHEN NO_DATA_FOUND THEN
2498 l_percent := -1;
2499 END;
2500 --dbms_output.put_line('After C l_percent');
2501
2502 END IF;
2503
2504 IF l_percent <> 0 THEN
2505
2506 insert into PA_RES_AVAILABILITY
2507 ( start_date,
2508 end_date,
2509 percent,
2510 record_type,
2511 resource_id,
2512 creation_date,
2513 last_update_date,
2514 created_by,
2515 last_updated_by,
2516 last_update_login)
2517 select min(start_date) - 1,
2518 min(start_date) - 1,
2519 0,
2520 record_type,
2521 resource_id,
2522 sysdate,
2523 sysdate,
2524 l_created_by,
2525 l_last_updated_by,
2526 l_last_update_login
2527 from pa_res_availability
2528 where resource_id = p_resource_id
2529 and record_type = 'C'
2530 group by 0,
2531 record_type,
2532 resource_id,
2533 sysdate,
2534 sysdate,
2535 l_created_by,
2536 l_last_updated_by,
2537 l_last_update_login;
2538 END IF;
2539
2540 select max(end_date)
2541 into l_end_date
2542 from pa_res_availability
2543 where resource_id = p_resource_id
2544 and record_type = 'C';
2545
2546 IF l_end_date is not null THEN
2547
2548 BEGIN
2549 select percent
2550 into l_percent
2551 from pa_res_availability
2552 where resource_id = p_resource_id
2553 and record_type = 'C'
2554 and end_date = l_end_date
2555 and percent = 0;
2556 EXCEPTION
2557 WHEN NO_DATA_FOUND THEN
2558 l_percent := -1;
2559 END;
2560 --dbms_output.put_line('After B l_percent');
2561
2562 END IF;
2563
2564 IF l_percent <> 0 THEN
2565
2566 insert into PA_RES_AVAILABILITY
2567 ( start_date,
2568 end_date,
2569 percent,
2570 record_type,
2571 resource_id,
2572 creation_date,
2573 last_update_date,
2574 created_by,
2575 last_updated_by,
2576 last_update_login)
2577 select max(end_date) + 1,
2578 max(end_date) + 1,
2579 0,
2580 record_type,
2581 resource_id,
2582 sysdate,
2583 sysdate,
2584 l_created_by,
2585 l_last_updated_by,
2586 l_last_update_login
2587 from pa_res_availability
2588 where resource_id = p_resource_id
2589 and record_type = 'C'
2590 group by 0,
2591 record_type,
2592 resource_id,
2593 sysdate,
2594 sysdate,
2595 l_created_by,
2596 l_last_updated_by,
2597 l_last_update_login;
2598 END IF;
2599
2600 --Logic to update the null dates due to usage of lead/lag
2601 update pa_res_availability avl
2602 set start_date = p_bstart_date
2603 where resource_id = p_resource_id
2604 and record_type = 'B'
2605 and start_date is null;
2606
2607 update pa_res_availability avl
2608 set end_date = p_bend_date
2609 where resource_id = p_resource_id
2610 and record_type = 'B'
2611 and end_date is null;
2612
2613 --Logic to insert 0 percent records in the beginning and end
2614 select min(start_date)
2615 into l_start_date
2616 from pa_res_availability
2617 where resource_id = p_resource_id
2618 and record_type = 'B';
2619
2620 IF l_start_date is not null THEN
2621
2622 BEGIN
2623 select percent
2624 into l_percent
2625 from pa_res_availability
2626 where resource_id = p_resource_id
2627 and record_type = 'B'
2628 and start_date = l_start_date
2629 and percent = 0;
2630 EXCEPTION
2631 WHEN NO_DATA_FOUND THEN
2632 l_percent := -1;
2633 END;
2634
2635 END IF;
2636
2637 IF l_percent <> 0 THEN
2638
2639 insert into PA_RES_AVAILABILITY
2640 ( start_date,
2641 end_date,
2642 percent,
2643 record_type,
2644 resource_id,
2645 creation_date,
2646 last_update_date,
2647 created_by,
2648 last_updated_by,
2649 last_update_login)
2650 select min(start_date) - 1,
2651 min(start_date) - 1,
2652 0,
2653 record_type,
2654 resource_id,
2655 sysdate,
2656 sysdate,
2657 l_created_by,
2658 l_last_updated_by,
2659 l_last_update_login
2660 from pa_res_availability
2661 where resource_id = p_resource_id
2662 and record_type = 'B'
2663 group by 0,
2664 record_type,
2665 resource_id,
2666 sysdate,
2667 sysdate,
2668 l_created_by,
2669 l_last_updated_by,
2670 l_last_update_login;
2671 END IF;
2672
2673 select max(end_date)
2674 into l_end_date
2675 from pa_res_availability
2676 where resource_id = p_resource_id
2677 and record_type = 'B';
2678
2679 IF l_end_date is not null THEN
2680
2681 BEGIN
2682 select percent
2683 into l_percent
2684 from pa_res_availability
2685 where resource_id = p_resource_id
2686 and record_type = 'B'
2687 and end_date = l_end_date
2688 and percent = 0;
2689 EXCEPTION
2690 WHEN NO_DATA_FOUND THEN
2691 l_percent := -1;
2692 END;
2693 END IF;
2694
2695 IF l_percent <> 0 THEN
2696
2697 insert into PA_RES_AVAILABILITY
2698 ( start_date,
2699 end_date,
2700 percent,
2701 record_type,
2702 resource_id,
2703 creation_date,
2704 last_update_date,
2705 created_by,
2706 last_updated_by,
2707 last_update_login)
2708 select max(end_date) + 1,
2709 max(end_date) + 1,
2710 0,
2711 record_type,
2712 resource_id,
2713 sysdate,
2714 sysdate,
2715 l_created_by,
2716 l_last_updated_by,
2717 l_last_update_login
2718 from pa_res_availability
2719 where resource_id = p_resource_id
2720 and record_type = 'B'
2721 group by 0,
2722 record_type,
2723 resource_id,
2724 sysdate,
2725 sysdate,
2726 l_created_by,
2727 l_last_updated_by,
2728 l_last_update_login;
2729 END IF;
2730
2731 ELSE
2732
2733 --TODO: Add error message for missing profile value
2734 PA_UTILS.Add_Message(
2735 p_app_short_name => 'PA'
2736 ,p_msg_name => 'PA_NO_AVL_CALC_PROF');
2737
2738 x_return_status := FND_API.G_RET_STS_ERROR;
2739
2740 RETURN;
2741 END IF;
2742
2743 --dbms_output.put_line('Before bug fix');
2744
2745 --------------------------------------------------
2746 --BUG 2634959: Availability start date must not be
2747 --earlier than min. resource start date
2748 --------------------------------------------------
2749 select min(RESOURCE_EFFECTIVE_START_DATE)
2750 into l_earliest_start_date
2751 from pa_resources_denorm
2752 where RESOURCE_ID = p_resource_id;
2753
2754 update pa_res_availability
2755 set start_date = l_earliest_start_date
2756 where resource_id = p_resource_id
2757 and start_date < l_earliest_start_date
2758 and percent > 0;
2759
2760 select count(*)
2761 into l_rec_count
2762 from pa_res_availability
2763 where resource_id = p_resource_id
2764 and start_date < l_earliest_start_date
2765 and record_type = 'B'
2766 and percent = 0;
2767
2768 IF l_rec_count > 1 THEN
2769
2770 delete
2771 from pa_res_availability
2772 where resource_id = p_resource_id
2773 and start_date < l_earliest_start_date - 1
2774 and record_type = 'B'
2775 and percent = 0;
2776
2777 END IF;
2778
2779 select count(*)
2780 into l_rec_count
2781 from pa_res_availability
2782 where resource_id = p_resource_id
2783 and start_date < l_earliest_start_date
2784 and record_type = 'C'
2785 and percent = 0;
2786
2787 IF l_rec_count > 1 THEN
2788
2789 delete
2790 from pa_res_availability
2791 where resource_id = p_resource_id
2792 and start_date < l_earliest_start_date - 1
2793 and record_type = 'C'
2794 and percent = 0;
2795
2796 END IF;
2797
2798 update pa_res_availability
2799 set start_date = l_earliest_start_date -1,
2800 end_date = l_earliest_start_date -1
2801 where resource_id = p_resource_id
2802 and start_date < l_earliest_start_date
2803 and percent = 0;
2804 --------------------------------------------------
2805
2806 --dbms_output.put_line('After bug fix');
2807
2808 EXCEPTION
2809 WHEN OTHERS THEN
2810
2811 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2812 RAISE;
2813
2814 END populate_res_availability;
2815
2816 -------------------------------------------------------------------------------+
2817 --
2818 -- PROCEDURE : Update_Res_Availability
2819 -- DESCRIPTION : This Procedure is called after FIs are generated
2820 -- for any PJR assignment
2821 -- This API updates PA_RES_AVAILABILITY based
2822 -- on the new assignment created
2823 --------------------------------------------------------------------------------+
2824 PROCEDURE update_res_availability (
2825 p_resource_id IN NUMBER,
2826 p_start_date IN DATE,
2827 p_end_date IN DATE,
2828 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
2829 x_msg_data OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
2830 x_msg_count OUT NOCOPY NUMBER ) --File.Sql.39 bug 4440895
2831 IS
2832
2833 l_msg_index_out number;
2834 l_cstart_date date;
2835 l_cend_date date;
2836 l_bstart_date date;
2837 l_bend_date date;
2838 l_start_date date;
2839 l_end_date date;
2840
2841 BEGIN
2842
2843 -- Initialize the Error Stack
2844 PA_DEBUG.init_err_stack('PA_RESOURCE_PVT.Update_Res_Availability');
2845
2846 -- Initialize the return status to success
2847 x_return_status := FND_API.G_RET_STS_SUCCESS;
2848
2849 BEGIN
2850
2851 select min(resource_effective_start_date)
2852 into l_start_date
2853 from pa_resources_denorm
2854 where resource_id = p_resource_id;
2855
2856 /*bug 3229929 modified min(resource_effective_end_date) to max(resource_effective_end_date) */
2857 select max(resource_effective_end_date)
2858 into l_end_date
2859 from pa_resources_denorm
2860 where resource_id = p_resource_id;
2861
2862 EXCEPTION
2863 WHEN NO_DATA_FOUND THEN
2864 l_start_date := trunc(p_start_date);
2865 l_end_date := trunc(p_end_date);
2866 END;
2867
2868 --Delete all records in pa_res_availability for this resource
2869 delete
2870 from pa_res_availability
2871 where resource_id = p_resource_id;
2872
2873
2874 populate_res_availability (
2875 p_resource_id => p_resource_id,
2876 p_cstart_date => trunc(l_start_date),
2877 p_cend_date => trunc(l_end_date),
2878 p_bstart_date => trunc(l_start_date),
2879 p_bend_date => trunc(l_end_date),
2880 x_return_status => x_return_status );
2881
2882 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2883 RAISE FND_API.G_EXC_ERROR;
2884 END IF;
2885
2886 EXCEPTION
2887 WHEN FND_API.G_EXC_ERROR THEN
2888 x_return_status := FND_API.G_RET_STS_ERROR;
2889
2890 -- Reset the error stack when returning to the calling program
2891 PA_DEBUG.Reset_Err_Stack;
2892
2893 -- get the number of messages
2894 x_msg_count := FND_MSG_PUB.Count_Msg;
2895
2896 -- Put a message text from message stack into the x_msg_data if there is only
2897 -- single error.
2898 IF x_msg_count = 1 THEN
2899 pa_interface_utils_pub.get_messages
2900 (p_encoded => FND_API.G_TRUE
2901 ,p_msg_index => 1
2902 ,p_data => x_msg_data
2903 ,p_msg_index_out => l_msg_index_out);
2904 END IF;
2905
2906 RAISE;
2907
2908 WHEN OTHERS THEN
2909 -- Set the exception Message and the stack
2910 FND_MSG_PUB.add_exc_msg
2911 (p_pkg_name => 'PA_RESOURCE_PVT.Update_Res_Availability'
2912 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
2913
2914 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2915
2916 RAISE;
2917
2918 END Update_Res_Availability;
2919
2920 -------------------------------------------------------------------------------+
2921 --
2922 -- PROCEDURE : Refresh_Res_Availability
2923 -- DESCRIPTION : This Procedure is called by the concurrent program
2924 -- to refresh PA_RES_AVAILABILITY
2925 --------------------------------------------------------------------------------+
2926 PROCEDURE refresh_res_availability (
2927 errbuf OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
2928 retcode OUT NOCOPY VARCHAR2 ) --File.Sql.39 bug 4440895
2929 IS
2930 l_return_status varchar2(1);
2931 l_msg_index_out number;
2932 -- l_profile varchar2(30) := 'DAILY';
2933 CURSOR resources IS
2934 select resource_id,
2935 min(resource_effective_start_date) start_date,
2936 max(resource_effective_end_date) end_date
2937 from pa_resources_denorm
2938 group by resource_id;
2939
2940 CURSOR redundant_resources IS -- Added for bug 7316435
2941 select resource_id
2942 from pa_res_availability
2943 where resource_id not in (Select resource_id from pa_resources_denorm)
2944 and resource_id <> -1;
2945
2946 l_res_count Number; --Added for bug 4928773
2947
2948
2949 BEGIN
2950
2951 -- Initialize the return status to success
2952 retcode := 0;
2953
2954 FOR rec IN redundant_resources LOOP --Added for bug 7316435 (Cleaning Redundant records)
2955
2956 delete
2957 from PA_RES_AVAILABILITY where RESOURCE_ID = rec.resource_id;
2958
2959 END LOOP;
2960
2961
2962
2963 --SAVEPOINT s_res_avl_begin; commented for bug 4928773
2964
2965 --Cleanup existing records in PA_RES_AVAILABILITY
2966 --delete
2967 --from PA_RES_AVAILABILITY;commented for bug 4928773
2968 l_res_count := 0;
2969 FOR rec IN resources LOOP
2970
2971 If l_res_count = 100 or l_res_count = 0 then --Added If block for bug 4928773
2972 l_res_count := 0;
2973 commit;
2974 SAVEPOINT s_res_avl_begin;
2975 end if;
2976
2977 delete
2978 from PA_RES_AVAILABILITY where RESOURCE_ID = rec.resource_id; --Added for bug 4928773
2979
2980 pa_resource_pvt.populate_res_availability(
2981 p_resource_id => rec.resource_id,
2982 p_cstart_date => rec.start_date,
2983 p_cend_date => rec.end_date,
2984 p_bstart_date => rec.start_date,
2985 p_bend_date => rec.end_date,
2986 x_return_status => l_return_status );
2987
2988 l_res_count := l_res_count + 1; --Added for bug 4928773
2989
2990 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2991
2992 ROLLBACK TO s_res_avl_begin;
2993 retcode := 2;
2994 EXIT;
2995
2996 END IF;
2997
2998 END LOOP;
2999
3000 -- commented out for perf bug 4930256
3001 -- l_profile := FND_PROFILE.VALUE_SPECIFIC('PA_AVAILABILITY_CAL_PERIOD');
3002
3003 delete
3004 from PA_RES_AVAILABILITY where RESOURCE_ID = -1; --Added for bug 4928773
3005
3006 INSERT
3007 INTO PA_RES_AVAILABILITY
3008 (
3009 RESOURCE_ID,
3010 START_DATE,
3011 RECORD_TYPE,
3012 CREATION_DATE,
3013 CREATED_BY,
3014 LAST_UPDATE_DATE,
3015 LAST_UPDATED_BY
3016 )
3017 VALUES
3018 (
3019 -1,
3020 sysdate,
3021 decode(l_profile, 'DAILY', 'D', 'W'),
3022 sysdate,
3023 -1,
3024 sysdate,
3025 -1
3026 );
3027
3028
3029 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3030 retcode := 2;
3031 RAISE FND_API.G_EXC_ERROR;
3032 END IF;
3033
3034 COMMIT;
3035
3036 retcode := 0;
3037
3038 EXCEPTION
3039 WHEN FND_API.G_EXC_ERROR THEN
3040
3041 ROLLBACK TO s_res_avl_begin;
3042
3043 retcode := 2;
3044 errbuf := SQLERRM;
3045
3046 WHEN OTHERS THEN
3047
3048 ROLLBACK TO s_res_avl_begin;
3049
3050 errbuf := SQLERRM;
3051 retcode := 2;
3052
3053 END Refresh_Res_Availability;
3054
3055
3056 -- FUNCTION
3057 -- Get_Staff_Mgr_Org_Id
3058 -- PURPOSE
3059 -- Specifically for staffing pages use (Avl/Ovc CO objects)
3060 -- It gets the Staffing Manager Organization (either SM organization
3061 -- or from the profile option) to be used in the VO. It returns the
3062 -- organization id.
3063 --+
3064 FUNCTION Get_Staff_Mgr_Org_Id (p_user_id IN NUMBER
3065 ,p_person_id IN NUMBER)
3066 RETURN VARCHAR2
3067 IS
3068 l_org_id NUMBER;
3069 l_return_status VARCHAR2(1);
3070 l_error_message_code fnd_new_messages.message_name%TYPE;
3071 BEGIN
3072
3073 l_org_id := fnd_profile.value_specific(
3074 name => 'PA_STAFF_HOME_DEF_ORG',
3075 user_id => p_user_id);
3076
3077 IF l_org_id IS NULL THEN
3078 PA_RESOURCE_UTILS.get_org_id(
3079 p_personid => p_person_id,
3080 p_start_date => sysdate,
3081 x_orgid => l_org_id,
3082 x_error_message_code => l_error_message_code,
3083 x_return_status => l_return_status);
3084 END IF;
3085
3086 RETURN l_org_id;
3087
3088 END Get_Staff_Mgr_Org_Id;
3089
3090
3091 END PA_RESOURCE_PVT;