[Home] [Help]
PACKAGE BODY: APPS.EAM_ASSIGN_EMP_PUB
Source
1 PACKAGE BODY EAM_ASSIGN_EMP_PUB as
2 /* $Header: EAMPESHB.pls 120.23 2012/01/27 10:51:00 vchidura noship $ */
3 /***************************************************************************
4 --
5 -- Copyright (c) 2002 Oracle Corporation, Redwood Shores, CA, USA
6 -- All rights reserved.
7 --
8 -- FILENAME
9 --
10 -- EAMPESHB.pls
11 --
12 -- DESCRIPTION
13 -- Package body for returning the various employees eligible for assignment
14 -- to a specific workorder -operation or workorder-operation-resource context.
15 -- NOTES
16 --
17 -- HISTORY
18 --
19 -- 11-Mar-05 Samir Jain Initial Creation
20 ***************************************************************************/
21 g_pkg_name CONSTANT VARCHAR2(30):= 'EAM_ASSIGN_EMP_PUB';
22 g_debug CONSTANT VARCHAR2(1):=NVL(fnd_profile.value('APPS_DEBUG'),'N');
23
24 procedure msg(x varchar2) is
25 pragma autonomous_transaction;
26 begin
27 --insert into debug_same values(x,sysdate);
28 --commit;
29 null;
30 end;
31
32 procedure purge is
33 pragma autonomous_transaction;
34 begin
35 -- delete from debug_same;
36 -- commit;
37 null;
38 end;
39
40
41 ------------------------------------------
42 PROCEDURE Get_Emp_Search_Results_Pub (
43 p_horizon_start_date IN DATE ,
44 p_horizon_end_date IN DATE ,
45 p_organization_id IN NUMBER,
46 p_wip_entity_id IN NUMBER ,
47 p_competence_type IN VARCHAR2 ,
48 p_competence_id IN NUMBER ,
49 p_resource_id IN NUMBER ,
50 p_resource_seq_num IN NUMBER ,
51 p_operation_seq_num IN NUMBER ,
52 p_department_id IN NUMBER ,
53 p_person_id IN NUMBER ,
54 p_api_version IN NUMBER :=1.0 ,
55 p_init_msg_list IN VARCHAR2:= FND_API.G_FALSE,
56 p_commit IN VARCHAR2:= FND_API.G_FALSE ,
57 p_validation_level IN NUMBER:= FND_API.G_VALID_LEVEL_FULL,
58 x_return_status OUT NOCOPY VARCHAR2 ,
59 x_msg_count OUT NOCOPY NUMBER ,
60 x_msg_data OUT NOCOPY VARCHAR2)
61 AS
62
63 l_api_name CONSTANT VARCHAR2(30) := 'Get_Emp_Search_Results_Pub';
64 l_api_version CONSTANT NUMBER := 1.0;
65 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
66 l_msg_data VARCHAR2(1000);
67
68
69 BEGIN
70 --msg('inside Get_Emp_Search_Results_Pub');
71 x_return_status := FND_API.G_RET_STS_SUCCESS;
72 -- Check for call compatibility.
73 --msg('Checkign APi compatibility');
74 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
75 p_api_version ,
76 l_api_name ,
77 G_PKG_NAME )
78 THEN
79 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
80 END IF;
81 --msg('insitializing message list');
82 IF FND_API.to_Boolean( p_init_msg_list ) THEN
83 FND_MSG_PUB.initialize;
84 END IF;
85 --msg('validating parameters');
86 -- Validate the parameters passed.
87 IF (p_organization_id IS NULL) THEN
88 FND_MESSAGE.SET_NAME ( 'EAM' , 'EAM_ASSET_ORG_ID_REQ');
89 FND_MSG_PUB.Add;
90 x_return_status := FND_API.G_RET_STS_ERROR;
91 ELSIF (p_competence_type IS NULL AND p_competence_id IS NULL AND
92 p_person_id IS NULL AND p_resource_id IS NULL AND p_department_id IS NULL) THEN
93
94 FND_MESSAGE.SET_NAME ( 'EAM' , 'EAM_EMPSCH_DEP_RES');
95 FND_MSG_PUB.Add;
96 x_return_status := FND_API.G_RET_STS_ERROR;
97 ELSE
98 IF (p_horizon_start_date IS NULL OR p_horizon_end_date IS NULL) THEN
99 FND_MESSAGE.SET_NAME ( 'EAM' , 'EAM_EMPSCH_DATE_MISS');
100 FND_MSG_PUB.Add;
101 x_return_status := FND_API.G_RET_STS_ERROR;
102 ELSIF (p_horizon_start_date > p_horizon_end_date) THEN
103 FND_MESSAGE.SET_NAME ( 'EAM' , 'EAM_EMPSCHED_DT_ERROR');
104 FND_MSG_PUB.Add;
105 x_return_status := FND_API.G_RET_STS_ERROR;
106 END IF;
107 END IF;
108
109 FND_MSG_PUB.count_and_get(
110 p_encoded => FND_API.G_FALSE,
111 p_count => x_msg_count,
112 p_data => x_msg_data
113 );
114
115 IF x_msg_count > 0
116 THEN
117 FOR indexCount IN 1..x_msg_count
118 LOOP
119 l_msg_data := FND_MSG_PUB.get(indexCount, FND_API.G_FALSE);
120 --msg(indexCount ||'-'||l_msg_data);
121 END LOOP;
122 END IF;
123
124 --msg('p_wip_entity_id==>' || p_wip_entity_id);
125 --msg('return status before calling private API==>' || x_return_status);
126 IF (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
127 --msg('calling private API');
128 EAM_ASSIGN_EMP_PUB.get_emp_search_results_pvt(
129 p_horizon_start_date ,
130 p_horizon_end_date ,
131 p_organization_id ,
132 p_wip_entity_id ,
133 p_competence_type ,
134 p_competence_id ,
135 p_resource_id,
136 p_resource_seq_num,
137 p_operation_seq_num,
138 p_department_id,
139 p_person_id,
140 p_api_version,
141 p_init_msg_list,
142 p_commit,
143 p_validation_level,
144 x_return_status,
145 x_msg_count,
146 x_msg_data);
147 END IF;
148 EXCEPTION
149 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
150 ROLLBACK TO EAM_ASSIGN_EMP_PUB;
151 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
152 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name, SQLERRM);
153 FND_MSG_PUB.Count_And_Get(
154 p_count => x_msg_count,
155 p_data => x_msg_data);
156
157 WHEN NO_DATA_FOUND THEN
158 ROLLBACK TO EAM_ASSIGN_EMP_PUB;
159 x_return_status := FND_API.G_RET_STS_ERROR ;
160 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name, SQLERRM);
161 FND_MSG_PUB.Count_And_Get(
162 p_count => x_msg_count,
163 p_data => x_msg_data);
164
165 END Get_Emp_Search_Results_Pub;
166
167
168 -- Function and procedure signature to return the assignment details of an employee
169 PROCEDURE Get_Emp_Assignment_Details_Pub
170 (
171 p_person_id IN VARCHAR2,
172 p_horizon_start_date IN DATE,
173 p_horizon_end_date IN DATE,
174 p_organization_id IN NUMBER,
175 p_api_version IN NUMBER :=1.0 ,
176 p_init_msg_list IN VARCHAR2:= FND_API.G_FALSE,
177 p_commit IN VARCHAR2:= FND_API.G_FALSE ,
178 p_validation_level IN NUMBER:= FND_API.G_VALID_LEVEL_FULL,
179 x_return_status OUT NOCOPY VARCHAR2 ,
180 x_msg_count OUT NOCOPY NUMBER ,
181 x_msg_data OUT NOCOPY VARCHAR2)
182 AS
183 l_api_name CONSTANT VARCHAR2(30) := 'Get_Emp_Assignment_Details_Pub';
184 l_api_version CONSTANT NUMBER := 1.0;
185 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
186
187 BEGIN
188 x_return_status := FND_API.G_RET_STS_SUCCESS;
189 -- Check for call compatibility.
190 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
191 p_api_version ,
192 l_api_name ,
193 G_PKG_NAME )
194 THEN
195 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
196 END IF;
197 IF FND_API.to_Boolean( p_init_msg_list ) THEN
198 FND_MSG_PUB.initialize;
199 END IF;
200
201 --validate the parameters passed
202 IF (p_organization_id IS NULL) THEN
203 FND_MESSAGE.SET_NAME ( 'EAM' , 'EAM_ASSET_ORG_ID_REQ');
204 FND_MSG_PUB.Add;
205 x_return_status := FND_API.G_RET_STS_ERROR;
206 ELSIF (p_person_id IS NULL) THEN
207 FND_MESSAGE.SET_NAME ( 'EAM' , 'EAM_EMPSCH_PERSON_MISS');
208 FND_MSG_PUB.Add;
209 x_return_status := FND_API.G_RET_STS_ERROR;
210 ELSE
211 IF (p_horizon_start_date IS NULL OR p_horizon_end_date IS NULL) THEN
212 FND_MESSAGE.SET_NAME ( 'EAM' , 'EAM_EMPSCH_DATE_MISS');
213 FND_MSG_PUB.Add;
214 x_return_status := FND_API.G_RET_STS_ERROR;
215 ELSIF (p_horizon_start_date > p_horizon_end_date) THEN
216 FND_MESSAGE.SET_NAME ( 'EAM' , 'EAM_EMPSCHED_DT_ERROR');
217 FND_MSG_PUB.Add;
218 x_return_status := FND_API.G_RET_STS_ERROR;
219 END IF;
220 END IF;
221
222 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
223 Get_Emp_Assignment_Details_Pvt(p_person_id,
224 p_horizon_start_date,
225 p_horizon_end_date,
226 p_organization_id,
227 p_api_version,
228 p_init_msg_list,
229 p_commit,
230 p_validation_level,
231 x_return_status,
232 x_msg_count,
233 x_msg_data);
234 END IF;
235 EXCEPTION
236 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
237 ROLLBACK TO EAM_ASSIGN_EMP_PUB;
238 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
239 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name, SQLERRM);
240 FND_MSG_PUB.Count_And_Get(
241 p_count => x_msg_count,
242 p_data => x_msg_data);
243
244 WHEN NO_DATA_FOUND THEN
245 ROLLBACK TO EAM_ASSIGN_EMP_PUB;
246 x_return_status := FND_API.G_RET_STS_ERROR ;
247 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name, SQLERRM);
248 FND_MSG_PUB.Count_And_Get(
249 p_count => x_msg_count,
250 p_data => x_msg_data);
251
252 END Get_Emp_Assignment_Details_Pub;
253 ------------------------------------------
254
255 FUNCTION Cal_Available_Hour(
256 p_resource_id IN NUMBER,
257 p_dept_id IN NUMBER,
258 p_calendar_code IN VARCHAR2,
259 p_horizon_start_date IN DATE,
260 p_horizon_end_date IN DATE
261 )
262 RETURN NUMBER
263 AS
264
265 --calculate the hours in case the resource is 24 hours exclusing exception dates
266 CURSOR l_res_24_hours_csr_type(l_horizon_start_date IN DATE,l_horizon_end_date IN DATE) IS
267 SELECT nvl(SUM(1*24),0) AS hours_24
268 FROM bom_calendar_dates
269 WHERE calendar_code = p_calendar_code
270 AND calendar_date BETWEEN l_horizon_start_date AND l_horizon_end_date
271 AND seq_num IS NOT NULL ;
272
273 CURSOR l_check_res_24_hour_csr_type(p_dept_id NUMBER,p_resource_id NUMBER) IS
274 SELECT bdr2.available_24_hours_flag AS available_24
275 FROM bom_department_resources bdr2
276 WHERE bdr2.department_id = p_dept_id
277 AND bdr2.resource_id = p_resource_id ;
278
279 l_available_hour NUMBER:=0;
280
281 l_st_dt DATE;
282 l_end_dt DATE;
283 l_next_st_dt DATE;
284 l_next_end_dt DATE;
285 l_extra_hour NUMBER:=0;
286 l_temp_extra_hour NUMBER :=0; -- get the extra hour from the procedure and add to the l_extra_hour
287
288 BEGIN
289 --msg('inside'||'Cal_Available_Hour');
290 --msg('p_dept_id'||p_dept_id);
291 --msg('p_resource_id'||p_resource_id);
292 --msg('p_calendar_code'||p_calendar_code);
293 --msg('p_horizon_start_date'||to_char(p_horizon_start_date,'dd-mm-yyyy hh24:mi:ss'));
294 --msg('p_horizon_end_date'||to_char(p_horizon_end_date,'dd-mm-yyyy hh24:mi:ss'));
295
296
297
298
299 IF SYSDATE <= p_horizon_end_date THEN
300
301 --truncating since the calendar_date stores the value without the time component.
302 IF p_horizon_start_date > SYSDATE THEN
303 l_st_dt := p_horizon_start_date;
304 ELSE
305 l_st_dt := SYSDATE;
306 END IF;
307
308 l_end_dt := p_horizon_end_date;
309
310 --msg('calling'||'l_check_res_24_hour_csr_type');
311
312 FOR l_check_res_24_hour_rec IN l_check_res_24_hour_csr_type(p_dept_id,p_resource_id)
313 LOOP
314 --msg('inside'||'l_check_res_24_hour_csr_type');
315 EXIT WHEN l_check_res_24_hour_csr_type%NOTFOUND;
316 -- if 1 then resource is 24 hour available
317 IF (l_check_res_24_hour_rec.available_24 = 1) THEN
318 --msg('resource available'||'24 hours');
319 --msg('calling'||'l_res_24_hours_csr_type');
320
321 -- Calculate the extra hours for the resource on the horizon start and end date
322 -- and run query for the remaining period
323 -- Bug 5211191. check whether horizon start and end dates fall on same day.
324
325 /*
326 --Bug 5211191 . Commenting out this code which validated bom calendar for 24 hr resource too.
327 --Added new code to simply calculate the times based on start and end time for 24 hr resource.
328 --For future use , when supporting bom calendar for 24 hr res, uncomment this code and comment ot the new code
329
330 IF ( trunc( l_st_dt ) = trunc ( l_end_dt ) && -- Date_Exception(trunc( l_st_dt ),p_calendar_code)='N' ) THEN
331 l_available_hour := ( l_end_dt - l_st_dt )*86400 ;
332 ELSE
333 cal_extra_24_hr_st_dt
334 (
335 l_st_dt ,
336 p_calendar_code ,
337 l_next_st_dt ,
338 l_temp_extra_hour
339 );
340 l_extra_hour := l_extra_hour + (l_temp_extra_hour*3600);
341 --msg(' after calling extra hour for start date, the extra hour==>' || l_extra_hour);
342
343 cal_extra_24_hr_end_dt
344 (
345 l_end_dt,
346 p_calendar_code ,
347 l_next_end_dt ,
348 l_temp_extra_hour
349 );
350 l_extra_hour := l_extra_hour + (l_temp_extra_hour*3600);
351 --msg(' afterhe extra hour==>' || l_extra_hour);
352
353 -- Add extra hour to the available hours for the resource.
354 --msg(' beofe adding extra hour l_available_hour==>' || l_available_hour);
355 l_available_hour := l_available_hour + l_extra_hour;
356 --msg('l_available_hour after adding extra hours ==> '||l_available_hour);
357
358 IF (l_next_st_dt <= l_next_end_dt) THEN
359 FOR l_res_24_hours_rec IN l_res_24_hours_csr_type(l_next_st_dt,l_next_end_dt)
360 LOOP
361 --msg('inside'||'l_res_24_hours_csr_type');
362 EXIT WHEN l_res_24_hours_csr_type%NOTFOUND;
363 --msg('l_res_24_hours_csr_type%rowcount==>' || l_res_24_hours_csr_type%ROWCOUNT);
364 --msg('before addition l_available_hour==>' || l_available_hour);
365 --msg('before addition value added ==>' || l_res_24_hours_rec.hours_24);
366 l_available_hour := l_available_hour + (l_res_24_hours_rec.hours_24*3600);
367 --msg('l_available_hour'||l_available_hour);
368 END LOOP l_res_24_hours_csr_type;
369 --msg('outside'||'l_res_24_hours_csr_type');
370 END IF;
371 END IF ; -- check whether horizon start and end dates fall on same day.
372 --End of commented code for Bug 5211191 .
373 */
374 -- Start of fix for bug 5211191. Following line added
375 l_available_hour := ( l_end_dt - l_st_dt )*86400 ;
376 ELSE
377 --Calculate the extra shift hours for the resource with shifts. Do it
378 -- for Horizon start date and end date.
379 Cal_Extra_Hour_Generic(
380 l_st_dt,
381 l_end_dt,
382 p_calendar_code ,
383 p_dept_id ,
384 p_resource_id ,
385 l_temp_extra_hour
386 );
387 return l_temp_extra_hour;
388
389 /* --msg('Calculate for the shift wise resource');
390 Cal_Extra_Hour_Start_Dt
391 (
392 l_st_dt,
393 false , -- previous = false, since calling for the first time.
394 p_calendar_code ,
395 p_dept_id ,
396 p_resource_id ,
397 l_next_st_dt ,
398 l_temp_extra_hour
399 );
400 l_extra_hour := l_extra_hour + (l_temp_extra_hour*3600);
401 --msg(' after calling extra hour for start date, the extra hour==>' || l_extra_hour);
402
403 Cal_Extra_Hour_End_Dt
404 (
405 l_end_dt ,
406 false , -- previous = false, since calling for the first time.
407 p_calendar_code ,
408 p_dept_id ,
409 p_resource_id ,
410 l_next_end_dt ,
411 l_temp_extra_hour
412 );
413 l_extra_hour := l_extra_hour + (l_temp_extra_hour*3600);
414 --msg(' after calling extra hour for end date, the extra hour==>' || l_extra_hour);
415 --msg(' beofe adding extra hour l_available_hour==>' || l_available_hour);
416
417 l_available_hour := l_available_hour + l_extra_hour;
418
419 --msg(' after adding extra hour l_available_hour==>' || l_available_hour);
420 l_next_st_dt := trunc(l_next_st_dt);
421 l_next_end_dt := trunc(l_next_end_dt);
422 --msg('Fetch the shifts availability for period st_dt ==>' || to_char(l_st_dt,'dd-mon-yyyy hh24:mi:ss') || ' and end_dt==>' || to_char(l_end_dt));
423 --msg('calling l_res_shifts_hours_csr_type');
424 IF (l_st_dt < l_end_dt) THEN
425 FOR l_res_shifts_hours_csr_rec IN l_res_shifts_hours_csr_type(l_next_st_dt, l_next_end_dt)
426 LOOP
427 --msg('inside l_res_shifts_hours_csr_type');
428 EXIT WHEN l_res_shifts_hours_csr_type%NOTFOUND;
429 --msg('l_available_hour before adding the shift hour through l_res_shifts_hours_csr_rec.shift_hours==>' || l_available_hour);
430 --msg('l_res_shifts_hours_csr_rec.shift_hours==>' || l_res_shifts_hours_csr_rec.shift_hours);
431 if (l_res_shifts_hours_csr_rec.shift_hours is not null) then
432 l_available_hour := l_available_hour + (l_res_shifts_hours_csr_rec.shift_hours*3600);
433 end if;
434 --msg('l_available_hour before addding extra hours ==>'||l_available_hour);
435 END LOOP l_res_shifts_hours_csr_type;
436 --msg('outside'||'l_res_shifts_hours_csr_type');*/
437 END IF;
438
439 END LOOP l_check_res_24_hour_csr_type;
440 --msg('outside'||'l_check_res_24_hour_csr_type');
441 --msg('returning from'||'Cal_Available_Hour');
442 --msg('l_available_hour final'||l_available_hour);
443 END IF; -- other wise return the available hour as 0.
444 RETURN round((l_available_hour/3600),2);
445 END Cal_Available_Hour;
446
447
448 FUNCTION Cal_Assigned_Hours
449 (p_wo_st_dt IN DATE,
450 p_wo_end_dt IN DATE,
451 p_horizon_start_date IN DATE,
452 p_horizon_end_date IN DATE
453 )
454 RETURN NUMBER
455 AS
456 l_assigned_hours NUMBER:=0;
457 BEGIN
458 --msg('inside'||'Cal_Assigned_Hours');
459 --msg('p_wo_st_dt'||p_wo_st_dt);
460 --msg('p_wo_end_dt'||p_wo_end_dt);
461 --msg('p_horizon_start_date'||p_horizon_start_date);
462 --msg('p_horizon_end_date'||p_horizon_end_date);
463
464 IF (p_wo_st_dt > p_horizon_end_date) THEN
465 RETURN 0;
466 END IF;
467
468 IF (p_wo_end_dt < p_horizon_start_date) THEN
469 RETURN 0;
470 END IF;
471
472 IF( p_wo_end_dt <= SYSDATE) THEN
473 RETURN 0;
474 END IF;
475
476 IF SYSDATE BETWEEN p_horizon_start_date AND p_horizon_end_date THEN
477 l_assigned_hours := Cal_Hr_Sys_Between_Horizon(p_wo_st_dt,p_wo_end_dt,p_horizon_start_date,p_horizon_end_date);
478 --msg('sysdate between horizonh ');
479 ELSIF SYSDATE < p_horizon_start_date THEN
480 l_assigned_hours := Cal_Hr_Sys_Before_Horizon(p_wo_st_dt,p_wo_end_dt,p_horizon_start_date,p_horizon_end_date);
481 ELSE
482 l_assigned_hours := 0;
483 END IF;
484 --msg('returning from'|| 'Cal_Assigned_Hours');
485 RETURN ROUND((l_assigned_hours*24),2);
486 END Cal_Assigned_Hours;
487
488
489 FUNCTION Cal_Hr_Sys_Between_Horizon
490 (p_wo_st_dt IN DATE,
491 p_wo_end_dt IN DATE,
492 p_horizon_start_date IN DATE,
493 p_horizon_end_date IN DATE
494 )
495 RETURN NUMBER
496 AS
497 l_assigned_hours NUMBER:=0;
498 BEGIN
499
500 IF (p_wo_st_dt > p_horizon_start_date) THEN
501 IF(p_wo_end_dt > p_horizon_end_date) THEN
502 IF(p_wo_st_dt > SYSDATE) THEN
503 l_assigned_hours := p_horizon_end_date - p_wo_st_dt;
504 ELSE
505 l_assigned_hours := p_horizon_end_date - SYSDATE;
506 END IF;
507 ELSE
508 IF(p_wo_st_dt > SYSDATE) THEN
509 l_assigned_hours := p_wo_end_dt - p_wo_st_dt;
510 ELSE
511 l_assigned_hours := p_wo_end_dt - SYSDATE;
512 END IF;
513 END IF;
514 ELSE
515 IF (p_wo_end_dt > p_horizon_end_date) THEN
516 l_assigned_hours := p_horizon_end_date - SYSDATE;
517 ELSE
518 l_assigned_hours := p_wo_end_dt - SYSDATE;
519 END IF;
520 END IF;
521
522 RETURN l_assigned_hours;
523 END Cal_Hr_Sys_Between_Horizon;
524
525
526 FUNCTION Cal_Hr_Sys_Before_Horizon
527 (p_wo_st_dt IN DATE,
528 p_wo_end_dt IN DATE,
529 p_horizon_start_date IN DATE,
530 p_horizon_end_date IN DATE
531 )
532 RETURN NUMBER
533 AS
534 l_assigned_hours NUMBER:=0;
535 BEGIN
536
537 IF (p_wo_st_dt > p_horizon_start_date) THEN
538 IF (p_wo_end_dt > p_horizon_end_date) THEN
539 l_assigned_hours := p_horizon_end_date - p_wo_st_dt;
540 ELSE
541 l_assigned_hours := p_wo_end_dt - p_wo_st_dt;
542 END IF;
543 ELSE
544 IF (p_wo_end_dt > p_horizon_end_date) THEN
545 l_assigned_hours := p_horizon_end_date - p_horizon_start_date;
546 ELSE
547 l_assigned_hours := p_wo_end_dt - p_horizon_start_date;
548 END IF;
549 END IF;
550
551 RETURN l_assigned_hours;
552 END Cal_Hr_Sys_Before_Horizon;
553
554 FUNCTION Competence_Check
555 (
556 p_person_id IN NUMBER,
557 p_competence_id IN NUMBER
558 )
559 RETURN VARCHAR2
560 AS
561 l_count_competence NUMBER;
562 l_competence_match VARCHAR2(1);
563
564 BEGIN
565 --msg('inside competence id check. Person id ==>'||p_person_id);
566 --msg('p_competence_id ==>'||p_competence_id);
567
568 SELECT count(1) INTO l_count_competence
569 FROM PER_COMPETENCE_ELEMENTS pce
570 WHERE pce.person_id = p_person_id
571 AND pce.business_group_id = HR_GENERAL.GET_BUSINESS_GROUP_ID
572 AND pce.type = 'PERSONAL'
573 AND pce.competence_id = p_competence_id
574 AND ( pce.effective_date_from IS NULL
575 OR trunc(sysdate) >= pce.effective_date_from
576 )
577 AND ( pce.effective_date_to IS NULL
578 OR trunc(sysdate) < pce.effective_date_to
579 );
580
581 IF (l_count_competence > 0) THEN
582 l_competence_match := 'Y';
583 ELSE
584 l_competence_match := 'N';
585 END IF;
586 RETURN l_competence_match;
587 END Competence_Check;
588
589
590 FUNCTION Competence_Type_Check
591 (
592 p_person_id IN NUMBER,
593 p_competence_type IN VARCHAR2
594 )
595 RETURN VARCHAR2
596 AS
597 l_competence_type_match VARCHAR2(1);
598 l_count_competence_type NUMBER:=0;
599 BEGIN
600
601 --msg('inside competence type check. Person id ==>'||p_person_id);
602 --msg('Competence type ==>'||p_competence_type);
603 SELECT COUNT(1) INTO l_count_competence_type
604 FROM PER_COMPETENCE_ELEMENTS pce,
605 PER_COMPETENCE_ELEMENTS pce1
606 WHERE pce.person_id = p_person_id
607 AND pce.business_group_id = HR_GENERAL.GET_BUSINESS_GROUP_ID
608 AND pce.type = 'PERSONAL'
609 AND pce.competence_id = pce1.competence_id
610 AND (pce.effective_date_from IS NULL
611 OR trunc(sysdate) >= pce.effective_date_from
612 )
613 AND (pce.effective_date_to IS NULL
614 OR trunc(sysdate) < pce.effective_date_to
615 )
616 AND pce1.business_group_id = pce.business_group_id
617 AND pce1.type = 'COMPETENCE_USAGE'
618 AND pce1.competence_type = p_competence_type
619 AND (pce1.effective_date_from IS NULL
620 OR trunc(sysdate) >= pce1.effective_date_from
621 )
622 AND (pce1.effective_date_to IS NULL
623 OR trunc(sysdate) < pce1.effective_date_to
624 );
625
626 IF (l_count_competence_type > 0) THEN
627 l_competence_type_match := 'Y';
628 ELSE
629 l_competence_type_match := 'N';
630 END IF;
631 RETURN l_competence_type_match;
632 END Competence_Type_Check;
633
634
635
636
637
638
639 --Procedure to get the employee search results -------
640
641 PROCEDURE Get_Emp_Search_Results_Pvt (
642 p_horizon_start_date IN DATE ,
643 p_horizon_end_date IN DATE ,
644 p_organization_id IN NUMBER,
645 p_wip_entity_id IN NUMBER ,
646 p_competence_type IN VARCHAR2 ,
647 p_competence_id IN NUMBER ,
648 p_resource_id IN NUMBER ,
649 p_resource_seq_num IN NUMBER ,
650 p_operation_seq_num IN NUMBER ,
651 p_department_id IN NUMBER ,
652 p_person_id IN NUMBER ,
653 p_api_version IN NUMBER :=1.0 ,
654 p_init_msg_list IN VARCHAR2:= FND_API.G_FALSE,
655 p_commit IN VARCHAR2:= FND_API.G_FALSE ,
656 p_validation_level IN NUMBER:= FND_API.G_VALID_LEVEL_FULL,
657 x_return_status OUT NOCOPY VARCHAR2 ,
658 x_msg_count OUT NOCOPY NUMBER ,
659 x_msg_data OUT NOCOPY VARCHAR2)
660
661 AS
662
663 /* Modified cursor for bug 4957001 . Removed function call and added inline exists clause for performant query */
664
665 CURSOR l_person_id_csr_type IS
666 SELECT DISTINCT
667 ppf.person_id,
668 ppf.full_name,
669 ppf.employee_number
670 FROM bom_departments bd,
671 bom_department_resources bdr,
672 bom_dept_res_instances bdri,
673 bom_resource_employees bre,
674 per_people_f ppf
675 WHERE bd.organization_id = p_organization_id
676 AND ( bd.disable_date IS NULL
677 OR (bd.disable_date > sysdate))
678 AND (p_department_id IS NULL
679 OR bd.department_id = p_department_id)
680 AND bdr.department_id = bd.department_id
681 AND (p_resource_id IS NULL
682 OR bdr.resource_id = p_resource_id)
683 AND bdri.resource_id = bdr.resource_id
684 AND (bdri.department_id = bdr.department_id OR bdri.department_id = bdr.share_from_dept_id )
685 AND bdri.serial_number is null
686 AND bdri.instance_id = bre.instance_id
687 AND bre.organization_id = bd.organization_id
688 AND bre.effective_start_date <= sysdate
689 AND bre.effective_end_date > sysdate
690 AND (p_person_id IS NULL
691 OR bre.person_id = p_person_id)
692 AND bre.person_id = ppf.person_id
693 AND ppf.effective_start_date <= sysdate
694 AND ppf.effective_end_date > sysdate
695 AND ppf.business_group_id = HR_GENERAL.GET_BUSINESS_GROUP_ID
696 AND ( ppf.current_employee_flag is null
697 OR ppf.current_employee_flag = 'Y' )
698 AND ( p_competence_id IS NULL OR p_competence_id IN
699 ( SELECT competence_id
700 FROM PER_COMPETENCE_ELEMENTS pce
701 WHERE pce.person_id = ppf.person_id
702 AND pce.business_group_id = HR_GENERAL.GET_BUSINESS_GROUP_ID
703 AND pce.type = 'PERSONAL'
704 AND pce.competence_id = p_competence_id
705 AND ( pce.effective_date_from IS NULL OR trunc(sysdate) >= pce.effective_date_from )
706 AND ( pce.effective_date_to IS NULL OR trunc(sysdate) < pce.effective_date_to)
707 )
708 )
709 AND ( p_competence_type IS NULL OR p_competence_type IN
710 ( SELECT pce1.competence_type
711 FROM PER_COMPETENCE_ELEMENTS pce, PER_COMPETENCE_ELEMENTS pce1
712 WHERE pce.person_id = ppf.person_id
713 AND pce.business_group_id = HR_GENERAL.GET_BUSINESS_GROUP_ID
714 AND pce.type = 'PERSONAL'
715 AND pce.competence_id = pce1.competence_id
716 AND (pce.effective_date_from IS NULL OR trunc(sysdate) >= pce.effective_date_from )
717 AND (pce.effective_date_to IS NULL OR trunc(sysdate) < pce.effective_date_to )
718 AND pce1.business_group_id = pce.business_group_id
719 AND pce1.type = 'COMPETENCE_USAGE'
720 AND pce1.competence_type = p_competence_type
721 AND (pce1.effective_date_from IS NULL OR trunc(sysdate) >= pce1.effective_date_from )
722 AND (pce1.effective_date_to IS NULL OR trunc(sysdate) < pce1.effective_date_to )
723 )
724 ) ;
725
726 -----------------------------------Define Cursors-------------------------------------------------------
727 --get the person id after filtering records on the basis of the search criteria's entered including
728 --methods for the competence check competence_check(l_person_id) and competence_type_check(l_person_id)
729
730
731
732 --get the instance id for the person id from bom_resource_employees
733 CURSOR l_inst_id_csr_type(p_person_id IN NUMBER,p_organization_id IN NUMBER) IS
734 SELECT bre2.instance_id
735 FROM bom_resource_employees bre2
736 WHERE bre2.person_id = p_person_id
737 AND bre2.organization_id = p_organization_id
738 AND bre2.effective_start_date <= sysdate
739 AND bre2.effective_end_date > sysdate
740 AND (p_resource_id IS NULL
741 OR bre2.resource_id = p_resource_id);
742
743 --get the department id and the resource id for the instance id from bom_department_resorce_instances
744 CURSOR l_dept_res_id_csr_type(p_inst_id IN NUMBER,p_department_id IN NUMBER) IS
745 SELECT bdr.share_from_dept_id, bdr.department_id, /*bug 10213972/11776366 -pass share_from_dept_id, indicates borrowed resources*/
746 bdr.resource_id
747 FROM bom_dept_res_instances bdri2 , bom_department_resources bdr
748 WHERE bdri2.instance_id = p_inst_id
749 AND bdri2.resource_id = bdr.resource_id
750 AND ( p_department_id IS NULL OR bdr.department_id = p_department_id )
751 AND ( p_department_id IS NULL OR bdri2.department_id = p_department_id OR bdri2.department_id = bdr.share_from_dept_id ) ;
752
753 --get the resource code for the resource id from bom_resources
754 CURSOR l_res_code_csr_type(p_resource_id IN NUMBER) IS
755 SELECT br.resource_code,
756 br.unit_of_measure as uom_code
757 FROM bom_resources br
758 WHERE br.resource_id = p_resource_id;
759
760 --get the department code for the department from bom_departments
761 CURSOR l_dept_code_csr_type(p_department_id IN NUMBER) IS
762 SELECT bd.department_code
763 FROM bom_departments bd
764 WHERE bd.department_id = p_department_id;
765
766 --get the workorders linked to the person id.(if not from woru, then from wori)
767 CURSOR l_workorder_instance_csr_type(l_person_id NUMBER) IS
768 SELECT woru.wip_entity_id wip_entity_id,
769 woru.completion_date wo_end_dt,
770 woru.start_date wo_st_dt,
771 (
772 CASE
773 WHEN (p_horizon_end_date> woru.completion_date) THEN
774 woru.completion_date
775 ELSE
776 p_horizon_end_date
777 END
778 ) AS task_bar_completion_date,
779 (
780 CASE
781 WHEN (p_horizon_start_date> woru.start_date) THEN
782 woru.start_date
783 ELSE
784 p_horizon_start_date
785 END
786 ) AS task_bar_start_date,
787 (
788 SELECT wip_entity_name
789 FROM wip_entities we
790 WHERE we.wip_entity_id = woru.wip_entity_id
791 AND we.organization_id = woru.organization_id
792 ) AS WorkOrderName,
793 (
794 SELECT br.resource_code
795 FROM bom_resource_employees bre,
796 bom_resources br
797 WHERE bre.instance_id = woru.instance_id
798 AND bre.organization_id = woru.organization_id
799 AND bre.effective_start_date <= sysdate
800 AND bre.effective_end_date > sysdate
801 AND br.resource_id = bre.resource_id
802 AND br.organization_id = woru.organization_id
803 AND ( br.disable_date IS NULL
804 OR br.disable_date > sysdate)
805 ) AS Resource_code,
806 ( DECODE(woru.organization_id,p_organization_id,
807 ( CASE WHEN (wdj.status_type IN (5,7,12)) THEN
808 'Disable'
809 ELSE
810 'Enable'
811 END
812 ),'Disable')
813 ) AS Enable_Row_Switcher ,
814 (
815 SELECT ROUND(SUM(wor.usage_rate_or_amount),2)
816 FROM wip_operation_resources wor
817 WHERE wor.wip_entity_id = woru.wip_entity_id
818 AND wor.organization_id = woru.organization_id
819 AND wor.operation_seq_num = woru.operation_seq_num
820 AND wor.resource_seq_num = woru.resource_seq_num
821 ) AS usage ,
822 woru.operation_seq_num,
823 woru.resource_seq_num
824 FROM wip_operation_resource_usage woru ,
825 wip_discrete_jobs wdj,
826 bom_resource_employees bre
827 WHERE bre.person_id = l_person_id
828 AND bre.effective_start_date <= sysdate
829 AND bre.effective_end_date > sysdate
830 AND woru.instance_id = bre.instance_id
831 AND wdj.wip_entity_id = woru.wip_entity_id
832 AND wdj.organization_id = woru.organization_id
833 AND woru.instance_id IS NOT NULL;
834
835 -- cursor to return the operation dates
836 CURSOR l_op_date_csr_type IS
837 SELECT
838 wo.first_unit_start_date as start_date,
839 wo.last_unit_completion_date as completion_date,
840 ROUND(((wo.last_unit_completion_date - wo.first_unit_start_date)*24),2) as duration,
841 wo.department_id as context_dept_id
842 FROM wip_operations wo
843 WHERE wo.wip_entity_id = p_wip_entity_id
844 AND wo.organization_id = p_organization_id
845 AND wo.operation_seq_num = p_operation_seq_num
846 AND wo.repetitive_schedule_id is null ;
847
848 --cursor to get resource code actually assigned to this workorder
849 CURSOR l_res_code_assigned_csr_type(l_instance_id number) IS
850 SELECT woru.instance_id
851 FROM wip_operation_resource_usage woru ,
852 bom_departments bd
853 WHERE woru.wip_entity_id = p_wip_entity_id
854 AND woru.organization_id = p_organization_id
855 AND woru.operation_seq_num = p_operation_seq_num
856 AND woru.instance_id = l_instance_id;
857
858 -- cursor to return the resource start date
859 CURSOR l_res_date_csr_type IS
860 SELECT
861 wor.start_date as start_date,
862 wor.completion_date as completion_date,
863 ROUND(((wor.completion_date - wor.start_date)*24),2) as duration,
864 wor.resource_id as context_res_id
865 FROM wip_operation_resources wor
866 WHERE wor.wip_entity_id = p_wip_entity_id
867 AND wor.operation_seq_num = p_operation_seq_num
868 AND wor.resource_seq_num = p_resource_seq_num
869 AND wor.organization_id= p_organization_id
870 AND wor.REPETITIVE_SCHEDULE_ID is null ;
871
872
873 --Define Local Variables--
874
875 l_person_id NUMBER:=0;
876 l_employee_name VARCHAR2(240);
877 l_instance_id NUMBER:=0;
878 l_resource_id NUMBER:=0;
879 l_department_id NUMBER:=0;
880 l_share_from_dept_id NUMBER:=0; -- for fix 11776366
881 l_available_hours NUMBER:=0;
882 l_resource_code VARCHAR2(10);
883 l_department_code VARCHAR2(10);
884 l_assign_unassign_enable VARCHAR2(30);
885 l_assigned_hours NUMBER:=0;
886 l_unassigned_hours NUMBER:=0;
887 l_assigned_percentage NUMBER:=0;
888 l_empl_start_date DATE;
889 l_emp_st_date DATE;
890 l_empl_completion_date DATE;
891 l_emp_end_date DATE;
892 l_duration NUMBER;
893 l_firm_status VARCHAR2(1);
894 l_employee_number VARCHAR2(30);
895 l_calendar_code VARCHAR2(10);
896 l_uom_code VARCHAR2(3);
897 l_context_dept_id NUMBER ;
898 l_context_res_id NUMBER ;
899 l_dept_assign_flag VARCHAR2(30) ;
900
901 --store at each level the number of records which needs to be added
902 l_end_count BINARY_INTEGER;
903 --store the starting count of the table index from which the records for assigned and available hours needs to be populated
904 l_start_count BINARY_INTEGER;
905
906
907 l_Emp_Search_Result_Rec Eam_Emp_Search_Result_Tbl%ROWTYPE;
908 l_Emp_Search_Result_Tbl EAM_ASSIGN_EMP_PUB.Emp_Search_Result_Tbl_Type;
909
910
911
912 l_api_name CONSTANT VARCHAR2(30) := 'Get_Emp_Search_Results_Pvt';
913 l_api_version CONSTANT NUMBER := 1.0;
914 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
915
916
917 BEGIN
918 --msg('inside search_pvt = ' || HR_GENERAL.GET_BUSINESS_GROUP_ID);
919 --msg(' wip_entity_id ==>' || p_wip_entity_id);
920 --msg( 'p_operation_seq_num ==>' || p_operation_seq_num);
921 --msg(' p_resource_seq_num ==>' || p_resource_seq_num);
922 --msg(' p_organization_id ==>' || p_organization_id);
923 SAVEPOINT EAM_ASSIGN_EMP_PUB;
924 -- Standard call to check for call compatibility.
925 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
926 p_api_version ,
927 l_api_name ,
928 G_PKG_NAME ) THEN
929 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
930 END IF;
931 -- Initialize message list if p_init_msg_list is set to TRUE.
932 IF FND_API.to_Boolean( p_init_msg_list ) THEN
933 FND_MSG_PUB.initialize;
934 END IF;
935 -- Initialize API return status to success
936 x_return_status := FND_API.g_ret_sts_success;
937 --msg('Getting the calndar code.p_organization_id==>' ||p_organization_id);
938
939 SELECT calendar_code INTO l_calendar_code
940 FROM mtl_parameters
941 WHERE organization_id = p_organization_id;
942
943 --msg('Getting the calndar code l_calendar_code==>' || l_calendar_code);
944 -- get the proposed operation/resource dates and duration.
945
946 IF p_operation_seq_num IS NOT NULL THEN
947
948 FOR l_op_date_csr_rec IN l_op_date_csr_type LOOP
949
950 IF l_op_date_csr_type%FOUND THEN
951 l_empl_start_date := l_op_date_csr_rec.start_date;
952 l_empl_completion_date := l_op_date_csr_rec.completion_date;
953 l_duration := l_op_date_csr_rec.duration;
954 l_context_dept_id := l_op_date_csr_rec.context_dept_id ;
955 --msg( ' inside operation ') ;
956 --msg( ' l_empl_start_date ' || to_char( l_empl_start_date,'DD-MON-YYYY HH:MI:SS ' ) ) ;
957 --msg( ' l_empl_completion_date ' || to_char( l_empl_completion_date,'DD-MON-YYYY HH:MI:SS ' ) ) ;
958
959 ELSE
960 EXIT;
961 END IF;
962
963 END LOOP l_op_date_csr_type;
964 END IF ;
965
966 IF p_resource_seq_num IS NOT NULL THEN
967
968 FOR l_res_date_csr_rec IN l_res_date_csr_type LOOP
969
970 IF l_res_date_csr_type%FOUND THEN
971 l_empl_start_date := l_res_date_csr_rec.start_date;
972 l_empl_completion_date := l_res_date_csr_rec.completion_date;
973 l_duration := l_res_date_csr_rec.duration;
974 l_context_res_id := l_res_date_csr_rec.context_res_id ;
975
976 ELSE
977 EXIT;
978 END IF;
979
980 END LOOP l_res_date_csr_type;
981
982 END IF;
983
984 --msg('Going to fetch the firm status of the workorder p_wip_entity_id==>' ||p_wip_entity_id|| ' and organization==>' || p_organization_id);
985
986 IF (p_wip_entity_id IS NOT NULL) THEN
987
988 SELECT wdj.FIRM_PLANNED_FLAG INTO l_firm_status
989 FROM wip_discrete_jobs wdj
990 WHERE wdj.wip_entity_id = p_wip_entity_id
991 AND organization_id = p_organization_id ;
992
993 END IF;
994 -- get all the person ids and loop through them
995
996 l_end_count := 1;
997 l_Emp_Search_Result_Tbl := EAM_ASSIGN_EMP_PUB.Emp_Search_Result_Tbl_Type();
998
999 FOR l_person_rec IN l_person_id_csr_type LOOP
1000 EXIT WHEN l_person_id_csr_type%NOTFOUND;
1001
1002 l_assign_unassign_enable := 'AssignEnabled' ;
1003 l_emp_st_date := l_empl_start_date ;
1004 l_emp_end_date := l_empl_completion_date ;
1005 l_person_id := l_person_rec.person_id;
1006 l_employee_name := l_person_rec.full_name;
1007 l_employee_number := l_person_rec.employee_number;
1008 l_assigned_hours := 0;
1009 l_available_hours := 0;
1010
1011 --msg('l_person_id==>'|| l_person_id);
1012 --msg('l_employee_name==>' ||l_employee_name);
1013 --msg('l_employee_number==>' ||l_employee_number);
1014 --msg('workorder instances'||'calling');
1015
1016 FOR l_workorder_instance_rec IN l_workorder_instance_csr_type(l_person_id)
1017 LOOP
1018 EXIT WHEN l_workorder_instance_csr_type%NOTFOUND;
1019 --msg('calculating'||'assigned hours');
1020 l_assigned_hours := l_assigned_hours +
1021 Cal_Assigned_Hours(l_workorder_instance_rec.wo_st_dt,
1022 l_workorder_instance_rec.wo_end_dt,
1023 p_horizon_start_date,
1024 p_horizon_end_date);
1025 --msg('calculating'||l_assigned_hours);
1026 --msg('Calculating at workorder level'||'for flag l_assign_unassign_enable');
1027 --msg('l_assign_unassign_enable ==>'||l_assign_unassign_enable);
1028 --msg('l_workorder_instance_rec.wip_entity_id = p_wip_entity_id'||l_workorder_instance_rec.wip_entity_id);
1029 --msg('l_workorder_instance_rec.operation_seq_num '||l_workorder_instance_rec.operation_seq_num);
1030 --msg('l_workorder_instance_rec.resource_seq_num '||l_workorder_instance_rec.resource_seq_num);
1031
1032 IF (l_assign_unassign_enable = 'AssignEnabled') THEN
1033 /* Added check for p_resource_seq_num NULL . Reqd if user is not coming with a resource context */
1034 IF ((l_workorder_instance_rec.wip_entity_id = p_wip_entity_id)
1035 AND (l_workorder_instance_rec.operation_seq_num = p_operation_seq_num)
1036 AND ( p_resource_seq_num IS NULL OR l_workorder_instance_rec.resource_seq_num = p_resource_seq_num) ) THEN
1037
1038
1039
1040 l_assign_unassign_enable := 'UnassignEnabled';
1041 l_emp_st_date := l_workorder_instance_rec.wo_st_dt;
1042 l_emp_end_date := l_workorder_instance_rec.wo_end_dt;
1043 --msg( ' l_empl_start_date ' || to_char( l_empl_start_date,'DD-MON-YYYY HH:MI:SS ' ) ) ;
1044 --msg( ' l_empl_completion_date ' || to_char( l_empl_completion_date,'DD-MON-YYYY HH:MI:SS ' ) ) ;
1045
1046
1047 END IF;
1048
1049 END IF;
1050
1051 END LOOP l_workorder_instance_csr_type;
1052
1053 l_start_count := l_end_count;
1054
1055
1056 FOR l_inst_id_rec IN l_inst_id_csr_type(l_person_id,p_organization_id)
1057 LOOP
1058 EXIT WHEN l_inst_id_csr_type%NOTFOUND;
1059
1060
1061 l_instance_id := l_inst_id_rec.instance_id ;
1062
1063
1064 FOR l_dept_res_id_rec IN l_dept_res_id_csr_type(l_instance_id,p_department_id)
1065 LOOP
1066 EXIT WHEN l_dept_res_id_csr_type%NOTFOUND;
1067
1068 l_department_id := l_dept_res_id_rec.department_id;
1069 l_share_from_dept_id :=l_dept_res_id_rec.share_from_dept_id; --For bug fix 11776366
1070 l_resource_id := l_dept_res_id_rec.resource_id;
1071 l_dept_assign_flag := l_assign_unassign_enable ;
1072
1073 l_dept_assign_flag := 'AssignEnabled';
1074
1075 /* Bug 5346714: Only mark those employees that are actually assigned to the work order*/
1076 FOR l_res_code_assigned_rec IN l_res_code_assigned_csr_type(l_instance_id)
1077 LOOP
1078 EXIT WHEN l_res_code_assigned_csr_type%NOTFOUND;
1079
1080 if l_res_code_assigned_rec.instance_id = l_instance_id then
1081 l_dept_assign_flag := 'UnassignEnabled';
1082 end if;
1083
1084 END LOOP l_res_code_assigned_csr_type;
1085
1086
1087 --if the resource id does not match the context the checkbox should be disabled.
1088 IF (l_context_res_id <> l_resource_id) THEN
1089 l_dept_assign_flag := 'AssignUnassignDisable' ;
1090 END IF;
1091 --if the department id does not match the context the checkbox should be disabled.
1092 IF (l_department_id <> l_context_dept_id) THEN
1093 l_dept_assign_flag := 'AssignUnassignDisable' ;
1094 END IF;
1095
1096 -- l_department_id := nvl(l_share_from_dept_id,l_department_id); --For bug fix 11776366
1097
1098 --get the resource code and department code and put in l_resource_code and l_department_code
1099 FOR l_dept_code_rec IN l_dept_code_csr_type(l_department_id)
1100 LOOP
1101 EXIT WHEN l_dept_code_csr_type%NOTFOUND;
1102 l_department_code := l_dept_code_rec.department_code;
1103 END LOOP l_dept_code_csr_type;
1104
1105
1106
1107 FOR l_res_code_rec IN l_res_code_csr_type(l_resource_id)
1108 LOOP
1109 EXIT WHEN l_res_code_csr_type%NOTFOUND;
1110 l_resource_code := l_res_code_rec.resource_code;
1111 l_uom_code := l_res_code_rec.uom_code;
1112
1113 END LOOP l_res_code_csr_type;
1114
1115 --msg('Total avaiolabe hours before calling cal_available_hour===>'||l_available_hours);
1116 --msg('calling'||'cal_available_hour');
1117
1118 l_available_hours := cal_available_hour(l_resource_id,
1119 nvl(l_share_from_dept_id,l_department_id), --For bug fix 11776366
1120 l_calendar_code,
1121 p_horizon_start_date,
1122 p_horizon_end_date);
1123 --msg('Total avaiolabe hours after calling cal_available_hour===>'||l_available_hours);
1124 --insert the values into the record EmpSearchRslt_Rec
1125 --leave the assigned hour,unassigned hour,available hour and percentage assigned as null.
1126 l_duration := ROUND( (l_emp_end_date - l_emp_st_date )*24,2 ) ;
1127
1128 l_Emp_Search_Result_Rec.person_id := l_person_id;
1129 l_Emp_Search_Result_Rec.employee_name := l_employee_name;
1130 l_Emp_Search_Result_Rec.employee_number := l_employee_number;
1131 l_Emp_Search_Result_Rec.instance_id := l_instance_id;
1132 l_Emp_Search_Result_Rec.resource_id := l_resource_id;
1133 l_Emp_Search_Result_Rec.department_id := l_department_id;
1134 l_Emp_Search_Result_Rec.resource_code := l_resource_code;
1135 l_Emp_Search_Result_Rec.department_code := l_department_code;
1136 l_Emp_Search_Result_Rec.assign_unassign_enable := l_dept_assign_flag;
1137 l_Emp_Search_Result_Rec.available_hours := l_available_hours;
1138 l_Emp_Search_Result_Rec.start_date := l_emp_st_date;
1139 l_Emp_Search_Result_Rec.completion_date := l_emp_end_date;
1140 l_Emp_Search_Result_Rec.duration := l_duration;
1141 l_Emp_Search_Result_Rec.wo_firm_status := l_firm_status;
1142 l_Emp_Search_Result_Rec.uom := l_uom_code;
1143
1144
1145
1146 --insert the record into table after initialization
1147
1148 l_Emp_Search_Result_Tbl.EXTEND;
1149
1150 IF l_Emp_Search_Result_Tbl.EXISTS(l_end_count) THEN
1151 --msg('present in table subscript'||l_end_count);
1152 --msg('max elements present==>'||l_Emp_Search_Result_Tbl.count);
1153 l_Emp_Search_Result_Tbl(l_end_count) := l_Emp_Search_Result_Rec;
1154 l_end_count := l_end_count + 1;
1155 ELSE
1156 NULL;
1157 --msg('l_end_count==>'||l_end_count);
1158 --msg('max elements present==>'||l_Emp_Search_Result_Tbl.count);
1159 --msg('EXCEPTION'||'subscript out of bound exceptio');
1160 END IF;
1161
1162 END LOOP l_dept_res_id_csr_type ;
1163
1164 END LOOP l_inst_id_csr_type;
1165
1166 -- loop through the newly added records to add the remaining computed columns like assigned,available hour etc.
1167
1168 FOR l_internal_table_counter IN l_start_count..(l_end_count-1)
1169 LOOP
1170 --msg('l_internal_table_counter'||l_internal_table_counter);
1171 --msg('fetching'||'l_Emp_Search_Result_Rec');
1172 l_Emp_Search_Result_Rec := l_Emp_Search_Result_Tbl(l_internal_table_counter);
1173 --msg('fetching'||'l_unassigned_hours');
1174 l_unassigned_hours := l_Emp_Search_Result_Rec.available_hours - l_assigned_hours;
1175 --msg('l_unassigned_hours==>'||l_unassigned_hours);
1176 IF (l_Emp_Search_Result_Rec.available_hours <> 0) THEN
1177 l_assigned_percentage := ROUND(((l_assigned_hours/l_Emp_Search_Result_Rec.available_hours) * 100),2);
1178 ELSE
1179 l_assigned_percentage := 0;
1180 END IF;
1181
1182 -- Add to EmpSearchRslt_Rec the following values
1183 -- l_unassigned_hours ,l_assigned_percentage ,l_available_hours l_assigned_hour
1184
1185 l_Emp_Search_Result_Rec.assigned_hours := l_assigned_hours ;
1186 l_Emp_Search_Result_Rec.unassigned_hours := l_unassigned_hours;
1187 l_Emp_Search_Result_Rec.assigned_percentage := l_assigned_percentage;
1188
1189 l_Emp_Search_Result_Tbl(l_internal_table_counter) := l_Emp_Search_Result_Rec;
1190 END LOOP ;
1191
1192
1193 END LOOP l_person_id_csr_type;
1194
1195 -- Copy the contents of the search table into the global temporary table.
1196 --Bulk collect into global temporary table
1197
1198 DELETE FROM Eam_Emp_Search_Result_Tbl;
1199 IF FND_API.TO_BOOLEAN(p_commit) THEN
1200 COMMIT WORK;
1201 end if;
1202 FORALL indx IN l_Emp_Search_Result_Tbl.FIRST..l_Emp_Search_Result_Tbl.LAST
1203 INSERT INTO Eam_Emp_Search_Result_Tbl
1204 VALUES l_Emp_Search_Result_Tbl(indx);
1205
1206
1207 EXCEPTION
1208 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1209 ROLLBACK TO EAM_ASSIGN_EMP_PUB;
1210 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1211 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name, SQLERRM);
1212 FND_MSG_PUB.Count_And_Get(
1213 p_count => x_msg_count,
1214 p_data => x_msg_data);
1215 WHEN NO_DATA_FOUND THEN
1216 ROLLBACK TO EAM_ASSIGN_EMP_PUB;
1217 x_return_status := FND_API.G_RET_STS_ERROR ;
1218 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name, SQLERRM);
1219 FND_MSG_PUB.Count_And_Get(
1220 p_count => x_msg_count,
1221 p_data => x_msg_data);
1222 END Get_Emp_Search_Results_Pvt;
1223
1224
1225
1226 --Procedure to get the assignment details-------
1227
1228 PROCEDURE Get_Emp_Assignment_Details_Pvt
1229 (
1230 p_person_id IN VARCHAR2,
1231 p_horizon_start_date IN DATE,
1232 p_horizon_end_date IN DATE,
1233 p_organization_id IN NUMBER,
1234 p_api_version IN NUMBER :=1.0 ,
1235 p_init_msg_list IN VARCHAR2:= FND_API.G_FALSE,
1236 p_commit IN VARCHAR2:= FND_API.G_FALSE ,
1237 p_validation_level IN NUMBER:= FND_API.G_VALID_LEVEL_FULL,
1238 x_return_status OUT NOCOPY VARCHAR2 ,
1239 x_msg_count OUT NOCOPY NUMBER ,
1240 x_msg_data OUT NOCOPY VARCHAR2
1241 )
1242
1243 AS
1244
1245 l_api_name CONSTANT VARCHAR2(30) := 'Get_Emp_Assignment_Details_Pvt';
1246 l_api_version CONSTANT NUMBER := 1.0;
1247 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
1248
1249 BEGIN
1250 -- Initialize API return status to success
1251
1252 SAVEPOINT EAM_ASSIGN_EMP_PUB;
1253 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
1254 p_api_version ,
1255 l_api_name ,
1256 G_PKG_NAME ) THEN
1257 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1258 END IF;
1259
1260 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1261 FND_MSG_PUB.initialize;
1262 END IF;
1263 x_return_status := FND_API.g_ret_sts_success;
1264 --copy the contents into the global temporary table
1265 DELETE FROM Eam_Emp_Assignment_Details_Tbl ;
1266 /* Bug 4715069 :
1267 Removed code to COMMIT. Issuing a COMMIT will truncate the search results GTT too which is not desired.
1268 Also, TRUNCATE cannot be used since it will do an implicit COMMIT and purge the Eam_Emp_Search_Result_Tbl .
1269 Removed code that loops through the pl/sql table and then bulk binds using FORALL into Eam_Emp_Assignment_Details_Tbl. Instead
1270 using INSERT with SELECT
1271 */
1272 INSERT INTO Eam_Emp_Assignment_Details_Tbl (
1273 wip_entity_id,
1274 wo_end_dt,
1275 wo_st_dt ,
1276 workordername ,
1277 resource_code ,
1278 update_switcher ,
1279 usage ,
1280 operation_seq_num ,
1281 resource_seq_num ,
1282 person_id ,
1283 wo_assign_check ,
1284 assign_switcher ,
1285 instance_id ,
1286 organization_id ,
1287 employee_name ,
1288 firm_status )
1289
1290 SELECT woru.wip_entity_id wip_entity_id,
1291 woru.completion_date wo_end_dt,
1292 woru.start_date wo_st_dt,
1293 (
1294 SELECT wip_entity_name
1295 FROM wip_entities we
1296 WHERE we.wip_entity_id = woru.wip_entity_id
1297 AND we.organization_id = woru.organization_id
1298 ) AS WorkOrderName,
1299 (
1300 SELECT br.resource_code
1301 FROM bom_resource_employees bre,
1302 bom_resources br
1303 WHERE bre.instance_id = woru.instance_id
1304 AND bre.organization_id = woru.organization_id
1305 AND bre.effective_start_date <= sysdate
1306 AND bre.effective_end_date > sysdate
1307 AND br.resource_id = bre.resource_id
1308 AND br.organization_id = woru.organization_id
1309 AND ( br.disable_date IS NULL
1310 OR br.disable_date > sysdate)
1311 ) AS Resource_code,
1312 ( DECODE(wdj.maintenance_object_source,2,'DisableWOUpdate',DECODE(woru.organization_id,p_organization_id,
1313 ( CASE WHEN (wdj.status_type IN (5,7,12)) THEN
1314 'DisableWOUpdate'
1315 ELSE
1316 'EnableWOUpdate'
1317 END
1318 ),'DisableWOUpdate'))
1319 ) AS Update_Switcher ,
1320 (
1321 SELECT ROUND(SUM(wor.usage_rate_or_amount),2)
1322 FROM wip_operation_resources wor
1323 WHERE wor.wip_entity_id = woru.wip_entity_id
1324 AND wor.organization_id = woru.organization_id
1325 AND wor.operation_seq_num = woru.operation_seq_num
1326 AND wor.resource_seq_num = woru.resource_seq_num
1327 ) AS usage ,
1328 woru.operation_seq_num,
1329 woru.resource_seq_num,
1330 ppf.person_id as person_id,
1331 'Y' as wo_assign_check,
1332 ( DECODE(woru.organization_id,p_organization_id,
1333 ( CASE WHEN (wdj.status_type IN ( 5,7,12,14,15 ) OR ewod.pending_flag = 'Y' ) THEN
1334 'DisableAssign'
1335 ELSE
1336 'EnableAssign'
1337 END
1338 ),'DisableAssign')
1339 ) AS Assign_Switcher,
1340 woru.instance_id,
1341 woru.organization_id,
1342 ppf.full_name as employee_name ,
1343 wdj.firm_planned_flag as firm_status
1344 FROM wip_operation_resource_usage woru ,
1345 wip_discrete_jobs wdj,
1346 eam_work_order_details ewod,
1347 bom_resource_employees bre,
1348 per_people_f ppf
1349 WHERE ppf.person_id = p_person_id
1350 AND ppf.business_group_id = HR_GENERAL.GET_BUSINESS_GROUP_ID
1351 AND ppf.EFFECTIVE_START_DATE <= sysdate
1352 AND ppf.EFFECTIVE_END_DATE > sysdate
1353 AND bre.person_id = ppf.person_id
1354 AND bre.effective_start_date <= sysdate
1355 AND bre.effective_end_date > sysdate
1356 AND woru.instance_id = bre.instance_id
1357 AND wdj.wip_entity_id = woru.wip_entity_id
1358 AND wdj.organization_id = woru.organization_id
1359 AND wdj.wip_entity_id = ewod.wip_entity_id
1360 AND wdj.organization_id = ewod.organization_id
1361 AND woru.start_date <= p_horizon_end_date
1362 AND woru.completion_date >= p_horizon_start_date
1363 AND woru.instance_id is not null ;
1364
1365 EXCEPTION
1366 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1367 ROLLBACK TO EAM_ASSIGN_EMP_PUB;
1368 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1369 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name, SQLERRM);
1370 FND_MSG_PUB.Count_And_Get(
1371 p_count => x_msg_count,
1372 p_data => x_msg_data);
1373 WHEN NO_DATA_FOUND THEN
1374 ROLLBACK TO EAM_ASSIGN_EMP_PUB;
1375 x_return_status := FND_API.G_RET_STS_ERROR ;
1376 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name, SQLERRM);
1377 FND_MSG_PUB.Count_And_Get(
1378 p_count => x_msg_count,
1379 p_data => x_msg_data);
1380 END Get_Emp_Assignment_Details_Pvt;
1381
1382
1383 -------------function to get the assignment status of the workorder--------------
1384
1385 FUNCTION Get_Emp_Assignment_Status
1386 (
1387 p_wip_entity_id IN NUMBER,
1388 p_organization_id IN NUMBER
1389 )
1390 RETURN VARCHAR2
1391 AS
1392
1393 CURSOR l_wo_op_csr_type(p_wip_entity_id IN NUMBER,p_organization_id IN NUMBER) IS
1394 SELECT wo.operation_seq_num
1395 FROM wip_operations wo
1396 WHERE wo.wip_entity_id = p_wip_entity_id
1397 AND wo.organization_id = p_organization_id
1398 AND (wo.disable_date is null OR wo.disable_date > sysdate )
1399 AND wo.repetitive_schedule_id IS NULL;
1400
1401 CURSOR l_res_csr_type(p_wip_entity_id IN NUMBER,p_organization_id IN NUMBER,p_op_seq_num IN NUMBER) IS
1402 SELECT wor.resource_seq_num , wor.usage_rate_or_amount
1403 FROM wip_operation_resources wor, bom_resources br
1404 WHERE wor.wip_entity_id = p_wip_entity_id
1405 AND wor.operation_seq_num = p_op_seq_num
1406 AND wor.ORGANIZATION_ID = p_organization_id
1407 AND wor.repetitive_schedule_id IS NULL
1408 AND br.resource_id = wor.resource_id
1409 AND br.resource_type = 2 ;
1410
1411 --Added union to fetch data from WORI, since data may not exist in WORU during upgrade step when this API is called
1412 CURSOR l_assigned_hr_csr_type(p_wip_entity_id IN NUMBER,p_organization_id IN NUMBER,p_op_seq_num IN NUMBER,p_res_seq_num IN NUMBER) IS
1413 SELECT ROUND(NVL((woru.completion_date - woru.start_date)*24,0),2) as assigned_hours
1414 FROM wip_operation_resource_usage woru
1415 WHERE woru.serial_number IS NULL
1416 AND woru.instance_id IS NOT NULL
1417 AND woru.wip_entity_id = p_wip_entity_id
1418 AND woru.operation_seq_num = p_op_seq_num
1419 AND woru.ORGANIZATION_ID = p_organization_id
1420 AND woru.repetitive_schedule_id IS NULL
1421 AND woru.resource_seq_num = p_res_seq_num
1422 UNION
1423 SELECT ROUND(NVL((wori.completion_date - wori.start_date)*24,0),2) as assigned_hours
1424 FROM wip_op_resource_instances wori
1425 WHERE wori.serial_number IS NULL
1426 AND wori.instance_id IS NOT NULL
1427 AND wori.wip_entity_id = p_wip_entity_id
1428 AND wori.operation_seq_num = p_op_seq_num
1429 AND wori.ORGANIZATION_ID = p_organization_id
1430 AND wori.resource_seq_num = p_res_seq_num
1431 AND NOT EXISTS (SELECT 1
1432 FROM wip_operation_resource_usage woru1
1433 WHERE woru1.serial_number IS NULL
1434 AND woru1.instance_id = wori.instance_id
1435 AND woru1.wip_entity_id = wori.wip_entity_id
1436 AND woru1.operation_seq_num = wori.operation_seq_num
1437 AND woru1.ORGANIZATION_ID = wori.ORGANIZATION_ID
1438 AND woru1.resource_seq_num = wori.resource_seq_num);
1439
1440 l_ret_status VARCHAR2(100) := 'Assignment Incomplete';
1441 l_required_hours NUMBER := 0;
1442 l_assigned_hours NUMBER := 0;
1443 l_unassigned_hours NUMBER := 0;
1444 BEGIN
1445 --msg('entered'||'get assignment status');
1446 l_ret_status := '1';--'Assignment Complete'
1447 FOR l_wo_op_csr_rec IN l_wo_op_csr_type( p_wip_entity_id ,p_organization_id )
1448 LOOP
1449 IF ((l_wo_op_csr_type%FOUND) AND (l_ret_status <> 'Assignment Incomplete')) THEN
1450
1451 --msg('operation sequence number'||l_wo_op_csr_rec.operation_seq_num );
1452 FOR l_res_csr_rec IN l_res_csr_type(p_wip_entity_id ,
1453 p_organization_id,
1454 l_wo_op_csr_rec.operation_seq_num)
1455 LOOP
1456 IF l_res_csr_type%FOUND THEN
1457
1458 --msg('resource sequence number'||l_res_csr_rec.resource_seq_num);
1459 l_required_hours := l_res_csr_rec.usage_rate_or_amount ;
1460 --msg('l_required_hours'||l_required_hours);
1461 l_assigned_hours := 0 ;
1462
1463 FOR l_assigned_hr_csr_rec IN l_assigned_hr_csr_type(p_wip_entity_id ,
1464 p_organization_id,
1465 l_wo_op_csr_rec.operation_seq_num,
1466 l_res_csr_rec.resource_seq_num)
1467 LOOP
1468
1469 IF l_assigned_hr_csr_type%FOUND THEN
1470 l_assigned_hours := l_assigned_hours + l_assigned_hr_csr_rec.assigned_hours;
1471 --msg('l_assigned_hours==>'||l_assigned_hours );
1472 END IF;
1473 END LOOP instance_csr_type;
1474 l_unassigned_hours := l_required_hours - l_assigned_hours;
1475
1476
1477 --msg('l_unassigned_hours====>'||l_unassigned_hours );
1478 IF (l_unassigned_hours >0) THEN
1479 l_ret_status := '2';--'Assignment Incomplete'
1480
1481 EXIT;
1482 END IF;
1483 END IF;
1484 END LOOP res_csr_type;
1485 END IF;
1486 END LOOP op_csr_type;
1487
1488 RETURN l_ret_status;
1489 EXCEPTION
1490 WHEN OTHERS THEN
1491 l_ret_status := '2'; --'Assignment Incomplete'
1492 RETURN l_ret_status;
1493 END Get_Emp_Assignment_Status;
1494
1495 FUNCTION Date_Exception
1496 (
1497 p_date IN DATE,
1498 p_calendar_code IN VARCHAR2
1499 )
1500 RETURN CHAR
1501 AS
1502 CURSOR l_date_check_csr_type(p_calendar_code IN VARCHAR2,p_date IN DATE) IS
1503 SELECT 1
1504 FROM bom_calendar_dates bcd
1505 WHERE bcd.calendar_code = p_calendar_code
1506 AND calendar_date = p_date
1507 AND seq_num IS NOT NULL ;
1508
1509 l_date_in_exception CHAR(1):= 'Y';
1510 BEGIN
1511 FOR l_date_check_csr_rec IN l_date_check_csr_type(p_calendar_code,p_date)
1512 LOOP
1513 IF l_date_check_csr_type%FOUND THEN
1514 l_date_in_exception := 'N';
1515 END IF;
1516 EXIT;
1517 END LOOP;
1518 --msg('Date in Exception? ' || l_date_in_exception);
1519 RETURN l_date_in_exception;
1520 END Date_Exception;
1521
1522 PROCEDURE Cal_Extra_Hour_Start_Dt
1523 (
1524 l_start_date IN DATE,
1525 l_previous IN BOOLEAN,
1526 l_calendar_code IN VARCHAR2,
1527 l_dept_id IN NUMBER,
1528 l_resource_id IN NUMBER,
1529 x_start_date OUT NOCOPY DATE,
1530 x_extra_hour OUT NOCOPY NUMBER
1531 )
1532 AS
1533 CURSOR l_date_shifts_csr_type(p_dept_id IN NUMBER,p_resource_id IN NUMBER,p_calendar_code IN VARCHAR2,p_date IN DATE) IS
1534 SELECT
1535 bst.from_time,bst.to_time
1536 FROM bom_resource_shifts brs,
1537 bom_shift_dates bsd,
1538 bom_shift_times bst
1539 WHERE brs.department_id = p_dept_id
1540 AND brs.resource_id = p_resource_id
1541 AND brs.shift_num = bsd.shift_num
1542 AND bsd.seq_num is not null
1543 AND bsd.calendar_code = p_calendar_code
1544 AND bsd.shift_date = p_date
1545 AND bst.calendar_code = bsd.calendar_code
1546 AND bst.shift_num = bsd.shift_num;
1547
1548
1549 l_starting_time NUMBER := 0;
1550 l_first_shift BOOLEAN := FALSE;
1551 l_shift_start_time NUMBER := 0;
1552 l_shift_end_time NUMBER :=0;
1553 l_extra_hour NUMBER := 0;
1554 l_temp_start_date DATE;
1555 BEGIN
1556 --msg('inside Cal_Extra_Hour_Start_Dt');
1557 --msg('l_start_date===> ' || to_char(l_start_date));
1558 l_temp_start_date := TRUNC(l_start_date); --shift and calndar table have only date component.
1559 x_extra_hour := 0;
1560 x_start_date := l_temp_start_date;
1561 l_starting_time := (l_start_date - TRUNC(l_start_date))*86400; -- IN SECONDS
1562 --msg('l_starting_time===> ' || to_char(l_starting_time));
1563
1564
1565 IF (Date_Exception(l_temp_start_date,l_calendar_code)='N') THEN --no exception
1566 --msg('date is not in exception');
1567 --msg('getting shifts from l_date_shifts_csr_type');
1568 --get valid shifts.
1569 l_first_shift := true;
1570 FOR l_date_shifts_csr_rec IN l_date_shifts_csr_type(l_dept_id,
1571 l_resource_id,
1572 l_calendar_code,
1573 l_temp_start_date
1574 )
1575 LOOP
1576 EXIT WHEN l_date_shifts_csr_type%NOTFOUND;
1577
1578 --msg('Shift dates and times as follows==>');
1579 --msg(' l_shift_start_time==>' || l_date_shifts_csr_rec.from_time);
1580 --msg(' l_shift_end_time==>' ||l_date_shifts_csr_rec.to_time);
1581 l_shift_start_time := l_date_shifts_csr_rec.from_time;
1582 l_shift_end_time := l_date_shifts_csr_rec.to_time;
1583
1584 --l_start_date should not be touched. Modify only the x_start_date
1585 if (l_shift_start_time <= l_shift_end_time) then --(200 to 2000 hours)
1586 --msg(' Shift start time < shift end time');
1587 if (l_starting_time between l_shift_start_time and l_shift_end_time) then
1588 --msg(' horizon start time is between shift start and end time');
1589 x_extra_hour := x_extra_hour + (l_shift_end_time - l_starting_time);
1590 --msg('extra hour==>' || x_extra_hour);
1591 x_start_date := l_temp_start_date +1;
1592 elsif (l_starting_time < l_shift_start_time and l_first_shift = true and l_previous = false) then
1593 --msg('part of the start date lies in the yesterdays shift');
1594 --part of start lies in yesterday's shift
1595 --msg('Calculate the extra hour for the yesterdays shift.Calling Cal_Extra_Hour_Start_Dt for starting date -1');
1596 Cal_Extra_Hour_Start_Dt(l_start_date - 1,
1597 true,
1598 l_calendar_code,
1599 l_dept_id,
1600 l_resource_id,
1601 l_temp_start_date,
1602 l_extra_hour);
1603 --msg('amount of extra hours to be added from the yesterday shift.l_extra_hour==>' || l_extra_hour);
1604 x_extra_hour := x_extra_hour + (l_extra_hour *3600);
1605 --msg('after adding the extra hour the x_extra_hour==>' || x_extra_hour);
1606 x_start_date := l_start_date ;
1607 --msg('start_date ==>' || to_char(x_start_date));
1608 exit;
1609 elsif (l_starting_time <= l_shift_start_time and l_previous = false) then
1610 --msg('starting time is less than shift start time but does not lie in yesterdays shift');
1611 --msg('before adding the extra hour for this shift,x_extra_hour==>' || x_extra_hour);
1612 x_extra_hour := x_extra_hour + (l_shift_end_time - l_shift_start_time);
1613 --msg('after adding the extra hour for this shift,x_extra_hour==>' || x_extra_hour);
1614 x_start_date := l_temp_start_date +1;
1615 end if;
1616 elsif (l_shift_start_time > l_shift_end_time) then
1617 --msg('the shift end time is less than the shift start time');
1618 if (l_first_shift = true and l_previous = false and l_starting_time < l_shift_start_time) then
1619 --msg('part of the start date lies in the yesterdays shift.Calling Cal_Extra_Hour_End_Dt for terminatiung date +1');
1620 --part of start lies in yesterday's shift
1621 Cal_Extra_Hour_Start_Dt(l_start_date - 1,
1622 true,
1623 l_calendar_code,
1624 l_dept_id,
1625 l_resource_id,
1626 l_temp_start_date,
1627 l_extra_hour);
1628 --msg('l_extra_hour calculated from yesterdays shift==>' || l_extra_hour);
1629 --msg('x_extra_hour before adding the extra hour==>' || x_extra_hour);
1630 x_extra_hour := x_extra_hour + (l_extra_hour * 3600);
1631 --msg('x_extra_hour after adding the extra hour==>' || x_extra_hour);
1632 x_start_date := l_start_date;
1633 exit;
1634 --- if this is not the first shift, then the complete shift should be accounted for.
1635 elsif (l_first_shift = true and l_previous = true) then
1636 --msg('first shift and came in recursion for previous date');
1637 if (l_starting_time <= l_shift_end_time) then
1638 --msg('starting time is <= shift end time');
1639 --msg('extra hour before addding the extra hour ==> ' || x_extra_hour);
1640 x_extra_hour := x_extra_hour + (l_shift_end_time - l_starting_time);
1641 --msg('extra hour after addding the extra hour ==> ' || x_extra_hour);
1642 exit;
1643 elsif (l_starting_time >= l_shift_start_time) then
1644 --msg('starting time is <= shift start time');
1645 --msg('before adding extra hour, x_extra_hour==> ' || x_extra_hour);
1646 x_extra_hour := x_extra_hour + (86400-l_starting_time) + l_shift_end_time;
1647 --msg('after adding extra hour, x_extra_hour==> ' || x_extra_hour);
1648 exit;
1649 end if;
1650 elsif (l_first_shift = false and l_previous = false) then
1651 --msg('first shift false and previous = false. Add the entire shift');
1652 --msg('extra hour before addding the extra hour ==> ' || x_extra_hour);
1653 x_extra_hour := x_extra_hour + (l_shift_end_time ) + (86400-l_shift_start_time);
1654 --msg('extra hour after addding the extra hour ==> ' || x_extra_hour);
1655 elsif (l_first_shift = false and l_previous = true) then
1656 --msg('first shift is false. l previous = true');
1657 if (l_starting_time <= l_shift_end_time) then
1658 --msg('starting time is <= shift end time');
1659 --msg('extra hour beforfe addding the extra hour ==> ' || x_extra_hour);
1660 x_extra_hour := x_extra_hour + (l_shift_end_time - l_starting_time);
1661 --msg('extra hour after addding the extra hour ==> ' || x_extra_hour);
1662 exit;
1663 elsif (l_starting_time >= l_shift_start_time) then
1664 --msg('starting time is >= shift start time');
1665 --msg('before adding extra hour, x_extra_hour==> ' || x_extra_hour);
1666 x_extra_hour := x_extra_hour + (86400-l_starting_time) + l_shift_end_time;
1667 --msg('after adding extra hour, x_extra_hour==> ' || x_extra_hour);
1668 exit;
1669 elsif (l_previous = false) then
1670 --msg('before adding extra hour, x_extra_hour==> ' || x_extra_hour);
1671 x_extra_hour := x_extra_hour + (86400-l_shift_start_time) + l_shift_end_time;
1672 --msg('after adding extra hour, x_extra_hour==> ' || x_extra_hour);
1673 x_start_date := l_temp_start_date +1;
1674 end if;
1675 end if;
1676 end if;
1677
1678 l_first_shift := false;
1679
1680 END LOOP l_date_shifts_csr_type;
1681 ELSE
1682 null;--no extra hour. continue with the same date
1683 END IF;
1684 x_extra_hour := round((x_extra_hour/3600),2);
1685 --msg('returning from Cal_Extra_Hour_Start_Dt with extra hour==>' || x_extra_hour);
1686 END Cal_Extra_Hour_Start_Dt;
1687
1688 PROCEDURE Cal_Extra_Hour_End_Dt
1689 (
1690 l_end_date IN DATE,
1691 l_previous IN BOOLEAN,
1692 l_calendar_code IN VARCHAR2,
1693 l_dept_id IN NUMBER,
1694 l_resource_id IN NUMBER,
1695 x_end_date OUT NOCOPY DATE,
1696 x_extra_hour OUT NOCOPY NUMBER
1697 )
1698 AS
1699 CURSOR l_date_shifts_csr_type(p_dept_id IN NUMBER,p_resource_id IN NUMBER,p_calendar_code IN VARCHAR2,p_date IN DATE) IS
1700 SELECT
1701 bst.from_time,bst.to_time
1702 FROM bom_resource_shifts brs,
1703 bom_shift_dates bsd,
1704 bom_shift_times bst
1705 WHERE brs.department_id = p_dept_id
1706 AND brs.resource_id = p_resource_id
1707 AND brs.shift_num = bsd.shift_num
1708 AND bsd.seq_num is not null
1709 AND bsd.calendar_code = p_calendar_code
1710 AND bsd.shift_date = p_date
1711 AND bst.calendar_code = bsd.calendar_code
1712 AND bst.shift_num = bsd.shift_num;
1713
1714
1715 l_terminating_time NUMBER := 0;
1716 l_first_shift BOOLEAN := FALSE;
1717 l_shift_start_time NUMBER := 0;
1718 l_shift_end_time NUMBER :=0;
1719 l_extra_hour NUMBER := 0;
1720 l_temp_end_date DATE;
1721 BEGIN
1722 --msg('inside Cal_Extra_Hour_End_Dt');
1723 --msg('l_end_date===> ' || to_char(l_end_date));
1724
1725 l_temp_end_date := TRUNC(l_end_date); --shift and calndar table have only date component.
1726 x_extra_hour := 0;
1727 x_end_date := l_temp_end_date;
1728 l_terminating_time := (l_end_date - TRUNC(l_end_date))*86400; -- IN SECONDS
1729 --msg('l_terminating_time===> ' || to_char(l_terminating_time));
1730
1731 IF (Date_Exception(l_temp_end_date,l_calendar_code)='N') THEN --no exception
1732 --msg('date is not in exception');
1733 --msg('getting shifts from l_date_shifts_csr_type');
1734 --get valid shifts.
1735 l_first_shift := true;
1736 FOR l_date_shifts_csr_rec IN l_date_shifts_csr_type(l_dept_id,
1737 l_resource_id,
1738 l_calendar_code,
1739 l_temp_end_date
1740 )
1741 LOOP
1742 EXIT WHEN l_date_shifts_csr_type%NOTFOUND;
1743
1744
1745 l_shift_start_time := l_date_shifts_csr_rec.from_time;
1746 l_shift_end_time := l_date_shifts_csr_rec.to_time;
1747
1748 --msg('Shift dates and times as follows==>');
1749 --msg(' l_shift_start_time==>' || l_date_shifts_csr_rec.from_time);
1750 --msg(' l_shift_end_time==>' ||l_date_shifts_csr_rec.to_time);
1751
1752 --l_end_date should not be touched. Modify only the x_end_date
1753 if (l_shift_start_time < l_shift_end_time) then --(200 to 2000 hours)
1754 --msg(' Shift start time < shift end time');
1755 if (l_terminating_time between l_shift_start_time and l_shift_end_time) then
1756 --msg(' horizon end time is between shift start and end time');
1757 x_extra_hour := x_extra_hour + ( l_terminating_time - l_shift_start_time);
1758 --msg('extra hour==>' || x_extra_hour);
1759
1760 x_end_date := l_temp_end_date - 1;
1761 --msg('x_end_date==>' || to_char(x_end_date));
1762
1763 exit;
1764 elsif (l_terminating_time >= l_shift_start_time and l_terminating_time >= l_shift_end_time) then
1765 --msg('add the entire shifts time to extra hour ');
1766 --msg('before adding extra hour, x_extra_hour==> ' || x_extra_hour);
1767 x_extra_hour := x_extra_hour + (l_shift_end_time - l_shift_start_time);
1768 --msg('after adding extra hour, x_extra_hour==> ' || x_extra_hour);
1769 x_end_date := l_temp_end_date -1;
1770 --msg('x_end_date==>' || to_char(x_end_date));
1771 elsif (l_terminating_time < l_shift_start_time and l_previous = false and l_first_shift = true) then
1772 --msg('the termintaing time lies in last dates shift.Calling Cal_Extra_Hour_End_Dt for terminatiung date -1');
1773 Cal_Extra_Hour_End_Dt(l_end_date - 1,
1774 true,
1775 l_calendar_code,
1776 l_dept_id,
1777 l_resource_id,
1778 l_temp_end_date,
1779 l_extra_hour);
1780 --msg('before adding extra hour, x_extra_hour==> ' || x_extra_hour);
1781 x_extra_hour := x_extra_hour + (l_extra_hour*3600);
1782 --msg('after adding extra hour, x_extra_hour==> ' || x_extra_hour);
1783
1784 if l_extra_hour >0 then
1785 x_end_date := l_end_date - 2;
1786 else
1787 x_end_date := l_end_date - 1;
1788 end if;
1789 --msg('x_end_date==>' || to_char(x_end_date));
1790 exit;
1791 elsif (l_terminating_time <= l_shift_start_time and l_terminating_time <= l_shift_end_time and l_first_shift = false) then
1792 --msg('add the complete shift');
1793 --msg('before adding extra hour, x_extra_hour==> ' || x_extra_hour);
1794 x_extra_hour := x_extra_hour + (l_shift_end_time - l_shift_start_time);
1795 --msg('after adding extra hour, x_extra_hour==> ' || x_extra_hour);
1796 elsif (l_terminating_time < l_shift_start_time and l_previous = true and l_first_shift = true) then
1797 --msg('calculating the extra hour for the previous date');
1798 --msg('before adding extra hour, x_extra_hour==> ' || x_extra_hour);
1799 x_extra_hour := x_extra_hour + (l_shift_end_time - l_shift_start_time);
1800 --msg('after adding extra hour, x_extra_hour==> ' || x_extra_hour);
1801 end if;
1802 elsif (l_shift_start_time > l_shift_end_time) then
1803 --msg('the shift start time is less than the shift end time.');
1804 if (l_first_shift = true and l_previous = false and l_terminating_time < l_shift_start_time) then
1805 --msg('the termintaing time lies in last dates shift.Calling Cal_Extra_Hour_End_Dt for terminatiung date -1');
1806 --part of start lies in yesterdays shift
1807 Cal_Extra_Hour_End_Dt(l_end_date - 1,
1808 true,
1809 l_calendar_code,
1810 l_dept_id,
1811 l_resource_id,
1812 l_temp_end_date,
1813 l_extra_hour);
1814 --msg('before adding extra hour, x_extra_hour==> ' || x_extra_hour);
1815 x_extra_hour := x_extra_hour + (l_extra_hour *3600);
1816 --msg('after adding extra hour, x_extra_hour==> ' || x_extra_hour);
1817 if l_extra_hour >0 then
1818 x_end_date := l_end_date - 2;
1819 else
1820 x_end_date := l_end_date - 1;
1821 end if;
1822 --msg('x_end_date==>' || to_char(x_end_date));
1823 exit;
1824 --- if this is not the first shift, then the complete shift should be accounted for.
1825 elsif (l_first_shift = true and l_previous = true) then
1826 --msg('first shift and came in recursion for previous date');
1827 if (l_terminating_time <= l_shift_end_time) then
1828 --msg('l_terminating_time is <= shift end time');
1829 --msg('extra hour before addding the extra hour ==> ' || x_extra_hour);
1830 x_extra_hour := x_extra_hour + l_terminating_time + (86400 - l_shift_end_time);
1831 --msg('extra hour after addding the extra hour ==> ' || x_extra_hour);
1832 exit;
1833 elsif (l_terminating_time >= l_shift_start_time) then
1834 --msg('l_terminating_time is >= shift start time');
1835 --msg('before adding extra hour, x_extra_hour==> ' || x_extra_hour);
1836 x_extra_hour := x_extra_hour + (l_terminating_time -l_shift_start_time );
1837 --msg('after adding extra hour, x_extra_hour==> ' || x_extra_hour);
1838 exit;
1839 end if;
1840 elsif (l_first_shift = false and l_previous = true) then
1841 --msg('first shift is false. l previous = true');
1842 if (l_terminating_time <= l_shift_end_time) then
1843 --msg('terminating time is less than the end time of the shift');
1844 --msg('before adding extra hour, x_extra_hour==> ' || x_extra_hour);
1845 x_extra_hour := x_extra_hour + (86400 - l_shift_start_time) + l_terminating_time;
1846 --msg('after adding extra hour, x_extra_hour==> ' || x_extra_hour);
1847 elsif (l_terminating_time >= l_shift_start_time) then
1848 --msg('terminating time is greater than the starting time of the shift');
1849 --msg('before adding extra hour, x_extra_hour==> ' || x_extra_hour);
1850 x_extra_hour := x_extra_hour + (l_terminating_time - l_shift_start_time);
1851 --msg('after adding extra hour, x_extra_hour==> ' || x_extra_hour);
1852 elsif (l_previous = false) then
1853 x_extra_hour := x_extra_hour + (86400-l_shift_start_time) + l_shift_end_time;
1854 x_end_date := l_temp_end_date +1;
1855 end if;
1856 elsif (l_first_shift = false and l_previous = false) then
1857 --msg('first shift false and previous = false. Add the entire shift');
1858 --msg('extra hour before addding the extra hour ==> ' || x_extra_hour);
1859 x_extra_hour := x_extra_hour + (l_shift_end_time ) + (86400-l_shift_start_time);
1860 --msg('extra hour after addding the extra hour ==> ' || x_extra_hour);
1861 end if;
1862 end if;
1863 l_first_shift := false;
1864
1865 END LOOP l_date_shifts_csr_type;
1866 ELSE
1867 null;--no extra hour. continue with the same date
1868 END IF;
1869 x_extra_hour := round((x_extra_hour/3600),2);
1870 --msg('returning from Cal_Extra_Hour_End_Dt with x_extra_hour==>' || x_extra_hour);
1871 END Cal_Extra_Hour_End_Dt;
1872
1873 procedure cal_extra_24_hr_st_dt
1874 (
1875 p_start_date IN DATE,
1876 p_calendar_code IN VARCHAR2,
1877 x_end_date OUT NOCOPY DATE,
1878 x_extra_hour OUT NOCOPY NUMBER
1879 )
1880 AS
1881 l_start_date DATE;
1882 l_start_time NUMBER:=0;
1883 l_extra_hour NUMBER:=0;
1884 BEGIN
1885 l_start_date := TRUNC(p_start_date ) ;
1886 l_start_time := (p_start_date - l_start_date)*86400; -- IN SECONDS
1887 IF (Date_Exception(l_start_date,p_calendar_code)='N') THEN
1888 l_extra_hour := (86400-l_start_time);
1889 end if;
1890 x_extra_hour := round((l_extra_hour/3600),2) ;
1891 --msg('x_extra_hour for 24 hr resource start date==>' || x_extra_hour);
1892 x_end_date := l_start_date + 1;
1893 END;
1894
1895 procedure cal_extra_24_hr_end_dt
1896 (
1897 p_end_date IN DATE,
1898 p_calendar_code IN VARCHAR2,
1899 x_end_date OUT NOCOPY DATE,
1900 x_extra_hour OUT NOCOPY NUMBER
1901 )
1902 AS
1903 l_end_date DATE;
1904 l_end_time NUMBER:=0;
1905 l_extra_hour NUMBER:=0;
1906 BEGIN
1907 l_end_date := TRUNC(p_end_date ) ;
1908 l_end_time := (p_end_date - l_end_date)*86400; -- IN SECONDS
1909
1910 IF (Date_Exception(l_end_date,p_calendar_code)='N') THEN
1911 l_extra_hour := (l_end_time);
1912 end if;
1913 x_extra_hour := round((l_extra_hour/3600),2) ;
1914 --msg('x_extra_hour for 24 hr resource end date==>' || x_extra_hour);
1915 x_end_date := l_end_date - 1;
1916 END;
1917
1918 PROCEDURE Cal_Extra_Hour_Same_Dt
1919 (
1920 l_start_date IN DATE,
1921 l_end_date IN DATE,
1922 l_calendar_code IN VARCHAR2,
1923 l_dept_id IN NUMBER,
1924 l_resource_id IN NUMBER,
1925 x_extra_hour OUT NOCOPY NUMBER
1926 )
1927 AS
1928 CURSOR l_date_shifts_csr_type(p_dept_id IN NUMBER,p_resource_id IN
1929 NUMBER,p_calendar_code IN VARCHAR2,p_date IN DATE) IS
1930 SELECT
1931 bst.from_time,bst.to_time
1932 FROM bom_resource_shifts brs,
1933 bom_shift_dates bsd,
1934 bom_shift_times bst
1935 WHERE brs.department_id = p_dept_id
1936 AND brs.resource_id = p_resource_id
1937 AND brs.shift_num = bsd.shift_num
1938 AND bsd.seq_num is not null
1939 AND bsd.calendar_code = p_calendar_code
1940 AND bsd.shift_date = p_date
1941 AND bst.calendar_code = bsd.calendar_code
1942 AND bst.shift_num = bsd.shift_num;
1943
1944
1945 l_start_time NUMBER := 0;
1946 l_end_time NUMBER := 0;
1947 l_shift_start_time NUMBER := 0;
1948 l_shift_end_time NUMBER :=0;
1949 l_extra_hour NUMBER := 0;
1950 l_temp_end_date date;
1951
1952 BEGIN
1953 l_temp_end_date := TRUNC(l_end_date);
1954 x_extra_hour := 0;
1955 l_start_time := (l_start_date - TRUNC(l_start_date))*86400;
1956 if(TRUNC(l_end_date)-TRUNC(l_start_date)=1 and l_end_date-TRUNC(l_end_date) = 0) then
1957 l_end_time := 86400;
1958 else
1959 l_end_time := (l_end_date - TRUNC(l_end_date))*86400;
1960 end if;
1961
1962
1963 FOR l_date_shifts_csr_rec IN l_date_shifts_csr_type(l_dept_id,
1964 l_resource_id,
1965 l_calendar_code,
1966 l_temp_end_date
1967 )
1968 LOOP
1969 EXIT WHEN l_date_shifts_csr_type%NOTFOUND;
1970
1971
1972 l_shift_start_time := l_date_shifts_csr_rec.from_time;
1973 l_shift_end_time := l_date_shifts_csr_rec.to_time;
1974 if(l_start_time<l_shift_start_time) then
1975 if(l_end_time<l_shift_start_time) then
1976 x_extra_hour := x_extra_hour + 0;
1977 elsif(l_end_time=l_shift_start_time) then
1978 x_extra_hour := x_extra_hour + 0;
1979 elsif(l_end_time>l_shift_start_time and l_end_time<l_shift_end_time)
1980 then
1981 x_extra_hour := x_extra_hour + l_end_time - l_shift_start_time;
1982 elsif(l_end_time=l_shift_end_time) then
1983 x_extra_hour := x_extra_hour + l_end_time - l_shift_start_time;
1984 elsif(l_end_time>l_shift_end_time) then
1985 x_extra_hour := x_extra_hour + l_shift_end_time -
1986 l_shift_start_time;
1987 end if;
1988
1989 elsif(l_start_time=l_shift_start_time) then
1990 if(l_end_time=l_shift_start_time) then
1991 x_extra_hour := x_extra_hour + 0;
1992 elsif(l_end_time>l_shift_start_time and l_end_time<l_shift_end_time)
1993 then
1994 x_extra_hour := x_extra_hour + l_end_time - l_start_time;
1995 elsif(l_end_time=l_shift_end_time) then
1996 x_extra_hour := x_extra_hour + l_end_time - l_start_time;
1997 elsif(l_end_time>l_shift_end_time) then
1998 x_extra_hour := x_extra_hour + l_shift_end_time - l_start_time;
1999 end if;
2000
2001 elsif(l_start_time>l_shift_start_time and l_start_time<l_shift_end_time)
2002 then
2003 if(l_end_time>l_shift_start_time and l_end_time<l_shift_end_time) then
2004 x_extra_hour := x_extra_hour + l_end_time - l_start_time;
2005 elsif(l_end_time=l_shift_end_time) then
2006 x_extra_hour := x_extra_hour + l_end_time - l_start_time;
2007 elsif(l_end_time>l_shift_end_time) then
2008 x_extra_hour := x_extra_hour + l_shift_end_time - l_start_time;
2009 end if;
2010 elsif(l_start_time>=l_shift_end_time) then
2011 x_extra_hour := x_extra_hour + 0;
2012 end if;
2013 END LOOP l_date_shifts_csr_type;
2014 x_extra_hour := round((x_extra_hour/3600),2);
2015 END Cal_Extra_Hour_Same_Dt;
2016
2017
2018 PROCEDURE Cal_Extra_Hour_Generic
2019 (
2020 l_start_date IN DATE,
2021 l_end_date IN DATE,
2022 l_calendar_code IN VARCHAR2,
2023 l_dept_id IN NUMBER,
2024 l_resource_id IN NUMBER,
2025 x_extra_hour OUT NOCOPY NUMBER
2026 )
2027 AS
2028 l_extra_hour NUMBER := 0;
2029 l_temp_date1 DATE;
2030 l_temp_date DATE;
2031 BEGIN
2032 x_extra_hour := 0;
2033
2034 if(l_end_date-l_start_date<=1) then
2035 if(TRUNC(l_end_date)-TRUNC(l_start_date) = 0) then
2036 Cal_Extra_Hour_Same_Dt
2037 (
2038 l_start_date ,
2039 l_end_date ,
2040 l_calendar_code ,
2041 l_dept_id ,
2042 l_resource_id ,
2043 l_extra_hour
2044 );
2045 x_extra_hour := x_extra_hour+l_extra_hour;
2046 else
2047 l_temp_date1 := TRUNC(l_end_date);
2048 Cal_Extra_Hour_Same_Dt
2049 (
2050 l_start_date ,
2051 l_temp_date1,
2052 l_calendar_code ,
2053 l_dept_id ,
2054 l_resource_id ,
2055 l_extra_hour
2056 );
2057 x_extra_hour := x_extra_hour+l_extra_hour;
2058 Cal_Extra_Hour_Same_Dt
2059 (
2060 l_temp_date1,
2061 l_end_date ,
2062 l_calendar_code ,
2063 l_dept_id ,
2064 l_resource_id ,
2065 l_extra_hour
2066 );
2067 x_extra_hour := x_extra_hour+l_extra_hour;
2068 end if;
2069 else
2070 l_temp_date := l_start_date;
2071 while l_temp_date + 1 < l_end_date loop
2072 Cal_Extra_Hour_Generic
2073 (
2074 l_temp_date,
2075 l_temp_date+1 ,
2076 l_calendar_code ,
2077 l_dept_id ,
2078 l_resource_id ,
2079 l_extra_hour
2080 );
2081 x_extra_hour := x_extra_hour+l_extra_hour;
2082 l_temp_date := l_temp_date+1;
2083 end loop;
2084 Cal_Extra_Hour_Generic
2085 (
2086 l_temp_date,
2087 l_end_date,
2088 l_calendar_code ,
2089 l_dept_id ,
2090 l_resource_id ,
2091 l_extra_hour
2092 );
2093 x_extra_hour := x_extra_hour+l_extra_hour;
2094
2095 end if;
2096 end Cal_Extra_Hour_Generic;
2097
2098 ------------------------Helper Functions--------------------------
2099
2100
2101 FUNCTION Fetch_Details
2102 (
2103 p_op_res_end_dt IN DATE
2104 )
2105 RETURN VARCHAR2
2106 AS
2107 l_fetch_employee_results VARCHAR2(1);
2108 BEGIN
2109 IF (p_op_res_end_dt >= sysdate) THEN
2110 l_fetch_employee_results := 'Y';
2111 ELSE
2112 l_fetch_employee_results := 'N';
2113 END IF;
2114 --msg('Work Order completion date in future?'||l_fetch_employee_results);
2115 RETURN l_fetch_employee_results;
2116 END Fetch_Details;
2117
2118 END EAM_ASSIGN_EMP_PUB;