[Home] [Help]
PACKAGE BODY: APPS.EAM_ASSIGN_EMP_PUB
Source
1 PACKAGE BODY EAM_ASSIGN_EMP_PUB as
2 /* $Header: EAMPESHB.pls 120.15.12010000.2 2008/10/30 09:41:47 vmec ship $ */
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.department_id,
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_available_hours NUMBER:=0;
881 l_resource_code VARCHAR2(10);
882 l_department_code VARCHAR2(10);
883 l_assign_unassign_enable VARCHAR2(30);
884 l_assigned_hours NUMBER:=0;
885 l_unassigned_hours NUMBER:=0;
886 l_assigned_percentage NUMBER:=0;
887 l_empl_start_date DATE;
888 l_emp_st_date DATE;
889 l_empl_completion_date DATE;
890 l_emp_end_date DATE;
891 l_duration NUMBER;
892 l_firm_status VARCHAR2(1);
893 l_employee_number VARCHAR2(30);
894 l_calendar_code VARCHAR2(10);
895 l_uom_code VARCHAR2(3);
896 l_context_dept_id NUMBER ;
897 l_context_res_id NUMBER ;
898 l_dept_assign_flag VARCHAR2(30) ;
899
900 --store at each level the number of records which needs to be added
901 l_end_count BINARY_INTEGER;
902 --store the starting count of the table index from which the records for assigned and available hours needs to be populated
903 l_start_count BINARY_INTEGER;
904
905
906 l_Emp_Search_Result_Rec Eam_Emp_Search_Result_Tbl%ROWTYPE;
907 l_Emp_Search_Result_Tbl EAM_ASSIGN_EMP_PUB.Emp_Search_Result_Tbl_Type;
908
909
910
911 l_api_name CONSTANT VARCHAR2(30) := 'Get_Emp_Search_Results_Pvt';
912 l_api_version CONSTANT NUMBER := 1.0;
913 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
914
915
916 BEGIN
917 --msg('inside search_pvt = ' || HR_GENERAL.GET_BUSINESS_GROUP_ID);
918 --msg(' wip_entity_id ==>' || p_wip_entity_id);
919 --msg( 'p_operation_seq_num ==>' || p_operation_seq_num);
920 --msg(' p_resource_seq_num ==>' || p_resource_seq_num);
921 --msg(' p_organization_id ==>' || p_organization_id);
922 SAVEPOINT EAM_ASSIGN_EMP_PUB;
923 -- Standard call to check for call compatibility.
924 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
925 p_api_version ,
926 l_api_name ,
927 G_PKG_NAME ) THEN
928 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
929 END IF;
930 -- Initialize message list if p_init_msg_list is set to TRUE.
931 IF FND_API.to_Boolean( p_init_msg_list ) THEN
932 FND_MSG_PUB.initialize;
933 END IF;
934 -- Initialize API return status to success
935 x_return_status := FND_API.g_ret_sts_success;
936 --msg('Getting the calndar code.p_organization_id==>' ||p_organization_id);
937
938 SELECT calendar_code INTO l_calendar_code
939 FROM mtl_parameters
940 WHERE organization_id = p_organization_id;
941
942 --msg('Getting the calndar code l_calendar_code==>' || l_calendar_code);
943 -- get the proposed operation/resource dates and duration.
944
945 IF p_operation_seq_num IS NOT NULL THEN
946
947 FOR l_op_date_csr_rec IN l_op_date_csr_type LOOP
948
949 IF l_op_date_csr_type%FOUND THEN
950 l_empl_start_date := l_op_date_csr_rec.start_date;
951 l_empl_completion_date := l_op_date_csr_rec.completion_date;
952 l_duration := l_op_date_csr_rec.duration;
953 l_context_dept_id := l_op_date_csr_rec.context_dept_id ;
954 --msg( ' inside operation ') ;
955 --msg( ' l_empl_start_date ' || to_char( l_empl_start_date,'DD-MON-YYYY HH:MI:SS ' ) ) ;
956 --msg( ' l_empl_completion_date ' || to_char( l_empl_completion_date,'DD-MON-YYYY HH:MI:SS ' ) ) ;
957
958 ELSE
959 EXIT;
960 END IF;
961
962 END LOOP l_op_date_csr_type;
963 END IF ;
964
965 IF p_resource_seq_num IS NOT NULL THEN
966
967 FOR l_res_date_csr_rec IN l_res_date_csr_type LOOP
968
969 IF l_res_date_csr_type%FOUND THEN
970 l_empl_start_date := l_res_date_csr_rec.start_date;
971 l_empl_completion_date := l_res_date_csr_rec.completion_date;
972 l_duration := l_res_date_csr_rec.duration;
973 l_context_res_id := l_res_date_csr_rec.context_res_id ;
974
975 ELSE
976 EXIT;
977 END IF;
978
979 END LOOP l_res_date_csr_type;
980
981 END IF;
982
983 --msg('Going to fetch the firm status of the workorder p_wip_entity_id==>' ||p_wip_entity_id|| ' and organization==>' || p_organization_id);
984
985 IF (p_wip_entity_id IS NOT NULL) THEN
986
987 SELECT wdj.FIRM_PLANNED_FLAG INTO l_firm_status
988 FROM wip_discrete_jobs wdj
989 WHERE wdj.wip_entity_id = p_wip_entity_id
990 AND organization_id = p_organization_id ;
991
992 END IF;
993 -- get all the person ids and loop through them
994
995 l_end_count := 1;
996 l_Emp_Search_Result_Tbl := EAM_ASSIGN_EMP_PUB.Emp_Search_Result_Tbl_Type();
997
998 FOR l_person_rec IN l_person_id_csr_type LOOP
999 EXIT WHEN l_person_id_csr_type%NOTFOUND;
1000
1001 l_assign_unassign_enable := 'AssignEnabled' ;
1002 l_emp_st_date := l_empl_start_date ;
1003 l_emp_end_date := l_empl_completion_date ;
1004 l_person_id := l_person_rec.person_id;
1005 l_employee_name := l_person_rec.full_name;
1006 l_employee_number := l_person_rec.employee_number;
1007 l_assigned_hours := 0;
1008 l_available_hours := 0;
1009
1010 --msg('l_person_id==>'|| l_person_id);
1011 --msg('l_employee_name==>' ||l_employee_name);
1012 --msg('l_employee_number==>' ||l_employee_number);
1013 --msg('workorder instances'||'calling');
1014
1015 FOR l_workorder_instance_rec IN l_workorder_instance_csr_type(l_person_id)
1016 LOOP
1017 EXIT WHEN l_workorder_instance_csr_type%NOTFOUND;
1018 --msg('calculating'||'assigned hours');
1019 l_assigned_hours := l_assigned_hours +
1020 Cal_Assigned_Hours(l_workorder_instance_rec.wo_st_dt,
1021 l_workorder_instance_rec.wo_end_dt,
1022 p_horizon_start_date,
1023 p_horizon_end_date);
1024 --msg('calculating'||l_assigned_hours);
1025 --msg('Calculating at workorder level'||'for flag l_assign_unassign_enable');
1026 --msg('l_assign_unassign_enable ==>'||l_assign_unassign_enable);
1027 --msg('l_workorder_instance_rec.wip_entity_id = p_wip_entity_id'||l_workorder_instance_rec.wip_entity_id);
1028 --msg('l_workorder_instance_rec.operation_seq_num '||l_workorder_instance_rec.operation_seq_num);
1029 --msg('l_workorder_instance_rec.resource_seq_num '||l_workorder_instance_rec.resource_seq_num);
1030
1031 IF (l_assign_unassign_enable = 'AssignEnabled') THEN
1032 /* Added check for p_resource_seq_num NULL . Reqd if user is not coming with a resource context */
1033 IF ((l_workorder_instance_rec.wip_entity_id = p_wip_entity_id)
1034 AND (l_workorder_instance_rec.operation_seq_num = p_operation_seq_num)
1035 AND ( p_resource_seq_num IS NULL OR l_workorder_instance_rec.resource_seq_num = p_resource_seq_num) ) THEN
1036
1037
1038
1039 l_assign_unassign_enable := 'UnassignEnabled';
1040 l_emp_st_date := l_workorder_instance_rec.wo_st_dt;
1041 l_emp_end_date := l_workorder_instance_rec.wo_end_dt;
1042 --msg( ' l_empl_start_date ' || to_char( l_empl_start_date,'DD-MON-YYYY HH:MI:SS ' ) ) ;
1043 --msg( ' l_empl_completion_date ' || to_char( l_empl_completion_date,'DD-MON-YYYY HH:MI:SS ' ) ) ;
1044
1045
1046 END IF;
1047
1048 END IF;
1049
1050 END LOOP l_workorder_instance_csr_type;
1051
1052 l_start_count := l_end_count;
1053
1054
1055 FOR l_inst_id_rec IN l_inst_id_csr_type(l_person_id,p_organization_id)
1056 LOOP
1057 EXIT WHEN l_inst_id_csr_type%NOTFOUND;
1058
1059
1060 l_instance_id := l_inst_id_rec.instance_id ;
1061
1062
1063 FOR l_dept_res_id_rec IN l_dept_res_id_csr_type(l_instance_id,p_department_id)
1064 LOOP
1065 EXIT WHEN l_dept_res_id_csr_type%NOTFOUND;
1066
1067 l_department_id := l_dept_res_id_rec.department_id;
1068 l_resource_id := l_dept_res_id_rec.resource_id;
1069 l_dept_assign_flag := l_assign_unassign_enable ;
1070
1071 l_dept_assign_flag := 'AssignEnabled';
1072
1073 /* Bug 5346714: Only mark those employees that are actually assigned to the work order*/
1074 FOR l_res_code_assigned_rec IN l_res_code_assigned_csr_type(l_instance_id)
1075 LOOP
1076 EXIT WHEN l_res_code_assigned_csr_type%NOTFOUND;
1077
1078 if l_res_code_assigned_rec.instance_id = l_instance_id then
1079 l_dept_assign_flag := 'UnassignEnabled';
1080 end if;
1081
1082 END LOOP l_res_code_assigned_csr_type;
1083
1084
1085 --if the resource id does not match the context the checkbox should be disabled.
1086 IF (l_context_res_id <> l_resource_id) THEN
1087 l_dept_assign_flag := 'AssignUnassignDisable' ;
1088 END IF;
1089 --if the department id does not match the context the checkbox should be disabled.
1090 IF (l_department_id <> l_context_dept_id) THEN
1091 l_dept_assign_flag := 'AssignUnassignDisable' ;
1092 END IF;
1093
1094
1095 --get the resource code and department code and put in l_resource_code and l_department_code
1096 FOR l_dept_code_rec IN l_dept_code_csr_type(l_department_id)
1097 LOOP
1098 EXIT WHEN l_dept_code_csr_type%NOTFOUND;
1099 l_department_code := l_dept_code_rec.department_code;
1100 END LOOP l_dept_code_csr_type;
1101
1102
1103
1104 FOR l_res_code_rec IN l_res_code_csr_type(l_resource_id)
1105 LOOP
1106 EXIT WHEN l_res_code_csr_type%NOTFOUND;
1107 l_resource_code := l_res_code_rec.resource_code;
1108 l_uom_code := l_res_code_rec.uom_code;
1109
1110 END LOOP l_res_code_csr_type;
1111
1112 --msg('Total avaiolabe hours before calling cal_available_hour===>'||l_available_hours);
1113 --msg('calling'||'cal_available_hour');
1114
1115 l_available_hours := cal_available_hour(l_resource_id,
1116 l_department_id,
1117 l_calendar_code,
1118 p_horizon_start_date,
1119 p_horizon_end_date);
1120 --msg('Total avaiolabe hours after calling cal_available_hour===>'||l_available_hours);
1121 --insert the values into the record EmpSearchRslt_Rec
1122 --leave the assigned hour,unassigned hour,available hour and percentage assigned as null.
1123 l_duration := ROUND( (l_emp_end_date - l_emp_st_date )*24,2 ) ;
1124
1125 l_Emp_Search_Result_Rec.person_id := l_person_id;
1126 l_Emp_Search_Result_Rec.employee_name := l_employee_name;
1127 l_Emp_Search_Result_Rec.employee_number := l_employee_number;
1128 l_Emp_Search_Result_Rec.instance_id := l_instance_id;
1129 l_Emp_Search_Result_Rec.resource_id := l_resource_id;
1130 l_Emp_Search_Result_Rec.department_id := l_department_id;
1131 l_Emp_Search_Result_Rec.resource_code := l_resource_code;
1132 l_Emp_Search_Result_Rec.department_code := l_department_code;
1133 l_Emp_Search_Result_Rec.assign_unassign_enable := l_dept_assign_flag;
1134 l_Emp_Search_Result_Rec.available_hours := l_available_hours;
1135 l_Emp_Search_Result_Rec.start_date := l_emp_st_date;
1136 l_Emp_Search_Result_Rec.completion_date := l_emp_end_date;
1137 l_Emp_Search_Result_Rec.duration := l_duration;
1138 l_Emp_Search_Result_Rec.wo_firm_status := l_firm_status;
1139 l_Emp_Search_Result_Rec.uom := l_uom_code;
1140
1141
1142
1143 --insert the record into table after initialization
1144
1145 l_Emp_Search_Result_Tbl.EXTEND;
1146
1147 IF l_Emp_Search_Result_Tbl.EXISTS(l_end_count) THEN
1148 --msg('present in table subscript'||l_end_count);
1149 --msg('max elements present==>'||l_Emp_Search_Result_Tbl.count);
1150 l_Emp_Search_Result_Tbl(l_end_count) := l_Emp_Search_Result_Rec;
1151 l_end_count := l_end_count + 1;
1152 ELSE
1153 NULL;
1154 --msg('l_end_count==>'||l_end_count);
1155 --msg('max elements present==>'||l_Emp_Search_Result_Tbl.count);
1156 --msg('EXCEPTION'||'subscript out of bound exceptio');
1157 END IF;
1158
1159 END LOOP l_dept_res_id_csr_type ;
1160
1161 END LOOP l_inst_id_csr_type;
1162
1163 -- loop through the newly added records to add the remaining computed columns like assigned,available hour etc.
1164
1165 FOR l_internal_table_counter IN l_start_count..(l_end_count-1)
1166 LOOP
1167 --msg('l_internal_table_counter'||l_internal_table_counter);
1168 --msg('fetching'||'l_Emp_Search_Result_Rec');
1169 l_Emp_Search_Result_Rec := l_Emp_Search_Result_Tbl(l_internal_table_counter);
1170 --msg('fetching'||'l_unassigned_hours');
1171 l_unassigned_hours := l_Emp_Search_Result_Rec.available_hours - l_assigned_hours;
1172 --msg('l_unassigned_hours==>'||l_unassigned_hours);
1173 IF (l_Emp_Search_Result_Rec.available_hours <> 0) THEN
1174 l_assigned_percentage := ROUND(((l_assigned_hours/l_Emp_Search_Result_Rec.available_hours) * 100),2);
1175 ELSE
1176 l_assigned_percentage := 0;
1177 END IF;
1178
1179 -- Add to EmpSearchRslt_Rec the following values
1180 -- l_unassigned_hours ,l_assigned_percentage ,l_available_hours l_assigned_hour
1181
1182 l_Emp_Search_Result_Rec.assigned_hours := l_assigned_hours ;
1183 l_Emp_Search_Result_Rec.unassigned_hours := l_unassigned_hours;
1184 l_Emp_Search_Result_Rec.assigned_percentage := l_assigned_percentage;
1185
1186 l_Emp_Search_Result_Tbl(l_internal_table_counter) := l_Emp_Search_Result_Rec;
1187 END LOOP ;
1188
1189
1190 END LOOP l_person_id_csr_type;
1191
1192 -- Copy the contents of the search table into the global temporary table.
1193 --Bulk collect into global temporary table
1194
1195 DELETE FROM Eam_Emp_Search_Result_Tbl;
1196 IF FND_API.TO_BOOLEAN(p_commit) THEN
1197 COMMIT WORK;
1198 end if;
1199 FORALL indx IN l_Emp_Search_Result_Tbl.FIRST..l_Emp_Search_Result_Tbl.LAST
1200 INSERT INTO Eam_Emp_Search_Result_Tbl
1201 VALUES l_Emp_Search_Result_Tbl(indx);
1202
1203
1204 EXCEPTION
1205 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1206 ROLLBACK TO EAM_ASSIGN_EMP_PUB;
1207 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1208 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name, SQLERRM);
1209 FND_MSG_PUB.Count_And_Get(
1210 p_count => x_msg_count,
1211 p_data => x_msg_data);
1212 WHEN NO_DATA_FOUND THEN
1213 ROLLBACK TO EAM_ASSIGN_EMP_PUB;
1214 x_return_status := FND_API.G_RET_STS_ERROR ;
1215 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name, SQLERRM);
1216 FND_MSG_PUB.Count_And_Get(
1217 p_count => x_msg_count,
1218 p_data => x_msg_data);
1219 END Get_Emp_Search_Results_Pvt;
1220
1221
1222
1223 --Procedure to get the assignment details-------
1224
1225 PROCEDURE Get_Emp_Assignment_Details_Pvt
1226 (
1227 p_person_id IN VARCHAR2,
1228 p_horizon_start_date IN DATE,
1229 p_horizon_end_date IN DATE,
1230 p_organization_id IN NUMBER,
1231 p_api_version IN NUMBER :=1.0 ,
1232 p_init_msg_list IN VARCHAR2:= FND_API.G_FALSE,
1233 p_commit IN VARCHAR2:= FND_API.G_FALSE ,
1234 p_validation_level IN NUMBER:= FND_API.G_VALID_LEVEL_FULL,
1235 x_return_status OUT NOCOPY VARCHAR2 ,
1236 x_msg_count OUT NOCOPY NUMBER ,
1237 x_msg_data OUT NOCOPY VARCHAR2
1238 )
1239
1240 AS
1241
1242 l_api_name CONSTANT VARCHAR2(30) := 'Get_Emp_Assignment_Details_Pvt';
1243 l_api_version CONSTANT NUMBER := 1.0;
1244 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
1245
1246 BEGIN
1247 -- Initialize API return status to success
1248
1249 SAVEPOINT EAM_ASSIGN_EMP_PUB;
1250 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
1251 p_api_version ,
1252 l_api_name ,
1253 G_PKG_NAME ) THEN
1254 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1255 END IF;
1256
1257 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1258 FND_MSG_PUB.initialize;
1259 END IF;
1260 x_return_status := FND_API.g_ret_sts_success;
1261 --copy the contents into the global temporary table
1262 DELETE FROM Eam_Emp_Assignment_Details_Tbl ;
1263 /* Bug 4715069 :
1264 Removed code to COMMIT. Issuing a COMMIT will truncate the search results GTT too which is not desired.
1265 Also, TRUNCATE cannot be used since it will do an implicit COMMIT and purge the Eam_Emp_Search_Result_Tbl .
1266 Removed code that loops through the pl/sql table and then bulk binds using FORALL into Eam_Emp_Assignment_Details_Tbl. Instead
1267 using INSERT with SELECT
1268 */
1269 INSERT INTO Eam_Emp_Assignment_Details_Tbl (
1270 wip_entity_id,
1271 wo_end_dt,
1272 wo_st_dt ,
1273 workordername ,
1274 resource_code ,
1275 update_switcher ,
1276 usage ,
1277 operation_seq_num ,
1278 resource_seq_num ,
1279 person_id ,
1280 wo_assign_check ,
1281 assign_switcher ,
1282 instance_id ,
1283 organization_id ,
1284 employee_name ,
1285 firm_status )
1286
1287 SELECT woru.wip_entity_id wip_entity_id,
1288 woru.completion_date wo_end_dt,
1289 woru.start_date wo_st_dt,
1290 (
1291 SELECT wip_entity_name
1292 FROM wip_entities we
1293 WHERE we.wip_entity_id = woru.wip_entity_id
1294 AND we.organization_id = woru.organization_id
1295 ) AS WorkOrderName,
1296 (
1297 SELECT br.resource_code
1298 FROM bom_resource_employees bre,
1299 bom_resources br
1300 WHERE bre.instance_id = woru.instance_id
1301 AND bre.organization_id = woru.organization_id
1302 AND bre.effective_start_date <= sysdate
1303 AND bre.effective_end_date > sysdate
1304 AND br.resource_id = bre.resource_id
1305 AND br.organization_id = woru.organization_id
1306 AND ( br.disable_date IS NULL
1307 OR br.disable_date > sysdate)
1308 ) AS Resource_code,
1309 ( DECODE(woru.organization_id,p_organization_id,
1310 ( CASE WHEN (wdj.status_type IN (5,7,12)) THEN
1311 'DisableWOUpdate'
1312 ELSE
1313 'EnableWOUpdate'
1314 END
1315 ),'DisableWOUpdate')
1316 ) AS Update_Switcher ,
1317 (
1318 SELECT ROUND(SUM(wor.usage_rate_or_amount),2)
1319 FROM wip_operation_resources wor
1320 WHERE wor.wip_entity_id = woru.wip_entity_id
1321 AND wor.organization_id = woru.organization_id
1322 AND wor.operation_seq_num = woru.operation_seq_num
1323 AND wor.resource_seq_num = woru.resource_seq_num
1324 ) AS usage ,
1325 woru.operation_seq_num,
1326 woru.resource_seq_num,
1327 ppf.person_id as person_id,
1328 'Y' as wo_assign_check,
1329 ( DECODE(woru.organization_id,p_organization_id,
1330 ( CASE WHEN (wdj.status_type IN ( 5,7,12,14,15 ) OR ewod.pending_flag = 'Y' ) THEN
1331 'DisableAssign'
1332 ELSE
1333 'EnableAssign'
1334 END
1335 ),'DisableAssign')
1336 ) AS Assign_Switcher,
1337 woru.instance_id,
1338 woru.organization_id,
1339 ppf.full_name as employee_name ,
1340 wdj.firm_planned_flag as firm_status
1341 FROM wip_operation_resource_usage woru ,
1342 wip_discrete_jobs wdj,
1343 eam_work_order_details ewod,
1344 bom_resource_employees bre,
1345 per_people_f ppf
1346 WHERE ppf.person_id = p_person_id
1347 AND ppf.business_group_id = HR_GENERAL.GET_BUSINESS_GROUP_ID
1348 AND ppf.EFFECTIVE_START_DATE <= sysdate
1349 AND ppf.EFFECTIVE_END_DATE > sysdate
1350 AND bre.person_id = ppf.person_id
1351 AND bre.effective_start_date <= sysdate
1352 AND bre.effective_end_date > sysdate
1353 AND woru.instance_id = bre.instance_id
1354 AND wdj.wip_entity_id = woru.wip_entity_id
1355 AND wdj.organization_id = woru.organization_id
1356 AND wdj.wip_entity_id = ewod.wip_entity_id
1357 AND wdj.organization_id = ewod.organization_id
1358 AND woru.start_date <= p_horizon_end_date
1359 AND woru.completion_date >= p_horizon_start_date
1360 AND woru.instance_id is not null ;
1361
1362 EXCEPTION
1363 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1364 ROLLBACK TO EAM_ASSIGN_EMP_PUB;
1365 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1366 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name, SQLERRM);
1367 FND_MSG_PUB.Count_And_Get(
1368 p_count => x_msg_count,
1369 p_data => x_msg_data);
1370 WHEN NO_DATA_FOUND THEN
1371 ROLLBACK TO EAM_ASSIGN_EMP_PUB;
1372 x_return_status := FND_API.G_RET_STS_ERROR ;
1373 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name, SQLERRM);
1374 FND_MSG_PUB.Count_And_Get(
1375 p_count => x_msg_count,
1376 p_data => x_msg_data);
1377 END Get_Emp_Assignment_Details_Pvt;
1378
1379
1380 -------------function to get the assignment status of the workorder--------------
1381
1382 FUNCTION Get_Emp_Assignment_Status
1383 (
1384 p_wip_entity_id IN NUMBER,
1385 p_organization_id IN NUMBER
1386 )
1387 RETURN VARCHAR2
1388 AS
1389
1390 CURSOR l_wo_op_csr_type(p_wip_entity_id IN NUMBER,p_organization_id IN NUMBER) IS
1391 SELECT wo.operation_seq_num
1392 FROM wip_operations wo
1393 WHERE wo.wip_entity_id = p_wip_entity_id
1394 AND wo.organization_id = p_organization_id
1395 AND (wo.disable_date is null OR wo.disable_date > sysdate )
1396 AND wo.repetitive_schedule_id IS NULL;
1397
1398 CURSOR l_res_csr_type(p_wip_entity_id IN NUMBER,p_organization_id IN NUMBER,p_op_seq_num IN NUMBER) IS
1399 SELECT wor.resource_seq_num , wor.usage_rate_or_amount
1400 FROM wip_operation_resources wor, bom_resources br
1401 WHERE wor.wip_entity_id = p_wip_entity_id
1402 AND wor.operation_seq_num = p_op_seq_num
1403 AND wor.ORGANIZATION_ID = p_organization_id
1404 AND wor.repetitive_schedule_id IS NULL
1405 AND br.resource_id = wor.resource_id
1406 AND br.resource_type = 2 ;
1407
1408 --Added union to fetch data from WORI, since data may not exist in WORU during upgrade step when this API is called
1409 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
1410 SELECT ROUND(NVL((woru.completion_date - woru.start_date)*24,0),2) as assigned_hours
1411 FROM wip_operation_resource_usage woru
1412 WHERE woru.serial_number IS NULL
1413 AND woru.instance_id IS NOT NULL
1414 AND woru.wip_entity_id = p_wip_entity_id
1415 AND woru.operation_seq_num = p_op_seq_num
1416 AND woru.ORGANIZATION_ID = p_organization_id
1417 AND woru.repetitive_schedule_id IS NULL
1418 AND woru.resource_seq_num = p_res_seq_num
1419 UNION
1420 SELECT ROUND(NVL((wori.completion_date - wori.start_date)*24,0),2) as assigned_hours
1421 FROM wip_op_resource_instances wori
1422 WHERE wori.serial_number IS NULL
1423 AND wori.instance_id IS NOT NULL
1424 AND wori.wip_entity_id = p_wip_entity_id
1425 AND wori.operation_seq_num = p_op_seq_num
1426 AND wori.ORGANIZATION_ID = p_organization_id
1427 AND wori.resource_seq_num = p_res_seq_num
1428 AND NOT EXISTS (SELECT 1
1429 FROM wip_operation_resource_usage woru1
1430 WHERE woru1.serial_number IS NULL
1431 AND woru1.instance_id = wori.instance_id
1432 AND woru1.wip_entity_id = wori.wip_entity_id
1433 AND woru1.operation_seq_num = wori.operation_seq_num
1434 AND woru1.ORGANIZATION_ID = wori.ORGANIZATION_ID
1435 AND woru1.resource_seq_num = wori.resource_seq_num);
1436
1437 l_ret_status VARCHAR2(100) := 'Assignment Incomplete';
1438 l_required_hours NUMBER := 0;
1439 l_assigned_hours NUMBER := 0;
1440 l_unassigned_hours NUMBER := 0;
1441 BEGIN
1442 --msg('entered'||'get assignment status');
1443 l_ret_status := '1';--'Assignment Complete'
1444 FOR l_wo_op_csr_rec IN l_wo_op_csr_type( p_wip_entity_id ,p_organization_id )
1445 LOOP
1446 IF ((l_wo_op_csr_type%FOUND) AND (l_ret_status <> 'Assignment Incomplete')) THEN
1447
1448 --msg('operation sequence number'||l_wo_op_csr_rec.operation_seq_num );
1449 FOR l_res_csr_rec IN l_res_csr_type(p_wip_entity_id ,
1450 p_organization_id,
1451 l_wo_op_csr_rec.operation_seq_num)
1452 LOOP
1453 IF l_res_csr_type%FOUND THEN
1454
1455 --msg('resource sequence number'||l_res_csr_rec.resource_seq_num);
1456 l_required_hours := l_res_csr_rec.usage_rate_or_amount ;
1457 --msg('l_required_hours'||l_required_hours);
1458 l_assigned_hours := 0 ;
1459
1460 FOR l_assigned_hr_csr_rec IN l_assigned_hr_csr_type(p_wip_entity_id ,
1461 p_organization_id,
1462 l_wo_op_csr_rec.operation_seq_num,
1463 l_res_csr_rec.resource_seq_num)
1464 LOOP
1465
1466 IF l_assigned_hr_csr_type%FOUND THEN
1467 l_assigned_hours := l_assigned_hours + l_assigned_hr_csr_rec.assigned_hours;
1468 --msg('l_assigned_hours==>'||l_assigned_hours );
1469 END IF;
1470 END LOOP instance_csr_type;
1471 l_unassigned_hours := l_required_hours - l_assigned_hours;
1472
1473
1474 --msg('l_unassigned_hours====>'||l_unassigned_hours );
1475 IF (l_unassigned_hours >0) THEN
1476 l_ret_status := '2';--'Assignment Incomplete'
1477
1478 EXIT;
1479 END IF;
1480 END IF;
1481 END LOOP res_csr_type;
1482 END IF;
1483 END LOOP op_csr_type;
1484
1485 RETURN l_ret_status;
1486 EXCEPTION
1487 WHEN OTHERS THEN
1488 l_ret_status := '2'; --'Assignment Incomplete'
1489 RETURN l_ret_status;
1490 END Get_Emp_Assignment_Status;
1491
1492 FUNCTION Date_Exception
1493 (
1494 p_date IN DATE,
1495 p_calendar_code IN VARCHAR2
1496 )
1497 RETURN CHAR
1498 AS
1499 CURSOR l_date_check_csr_type(p_calendar_code IN VARCHAR2,p_date IN DATE) IS
1500 SELECT 1
1501 FROM bom_calendar_dates bcd
1502 WHERE bcd.calendar_code = p_calendar_code
1503 AND calendar_date = p_date
1504 AND seq_num IS NOT NULL ;
1505
1506 l_date_in_exception CHAR(1):= 'Y';
1507 BEGIN
1508 FOR l_date_check_csr_rec IN l_date_check_csr_type(p_calendar_code,p_date)
1509 LOOP
1510 IF l_date_check_csr_type%FOUND THEN
1511 l_date_in_exception := 'N';
1512 END IF;
1513 EXIT;
1514 END LOOP;
1515 --msg('Date in Exception? ' || l_date_in_exception);
1516 RETURN l_date_in_exception;
1517 END Date_Exception;
1518
1519 PROCEDURE Cal_Extra_Hour_Start_Dt
1520 (
1521 l_start_date IN DATE,
1522 l_previous IN BOOLEAN,
1523 l_calendar_code IN VARCHAR2,
1524 l_dept_id IN NUMBER,
1525 l_resource_id IN NUMBER,
1526 x_start_date OUT NOCOPY DATE,
1527 x_extra_hour OUT NOCOPY NUMBER
1528 )
1529 AS
1530 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
1531 SELECT
1532 bst.from_time,bst.to_time
1533 FROM bom_resource_shifts brs,
1534 bom_shift_dates bsd,
1535 bom_shift_times bst
1536 WHERE brs.department_id = p_dept_id
1537 AND brs.resource_id = p_resource_id
1538 AND brs.shift_num = bsd.shift_num
1539 AND bsd.seq_num is not null
1540 AND bsd.calendar_code = p_calendar_code
1541 AND bsd.shift_date = p_date
1542 AND bst.calendar_code = bsd.calendar_code
1543 AND bst.shift_num = bsd.shift_num;
1544
1545
1546 l_starting_time NUMBER := 0;
1547 l_first_shift BOOLEAN := FALSE;
1548 l_shift_start_time NUMBER := 0;
1549 l_shift_end_time NUMBER :=0;
1550 l_extra_hour NUMBER := 0;
1551 l_temp_start_date DATE;
1552 BEGIN
1553 --msg('inside Cal_Extra_Hour_Start_Dt');
1554 --msg('l_start_date===> ' || to_char(l_start_date));
1555 l_temp_start_date := TRUNC(l_start_date); --shift and calndar table have only date component.
1556 x_extra_hour := 0;
1557 x_start_date := l_temp_start_date;
1558 l_starting_time := (l_start_date - TRUNC(l_start_date))*86400; -- IN SECONDS
1559 --msg('l_starting_time===> ' || to_char(l_starting_time));
1560
1561
1562 IF (Date_Exception(l_temp_start_date,l_calendar_code)='N') THEN --no exception
1563 --msg('date is not in exception');
1564 --msg('getting shifts from l_date_shifts_csr_type');
1565 --get valid shifts.
1566 l_first_shift := true;
1567 FOR l_date_shifts_csr_rec IN l_date_shifts_csr_type(l_dept_id,
1568 l_resource_id,
1569 l_calendar_code,
1570 l_temp_start_date
1571 )
1572 LOOP
1573 EXIT WHEN l_date_shifts_csr_type%NOTFOUND;
1574
1575 --msg('Shift dates and times as follows==>');
1576 --msg(' l_shift_start_time==>' || l_date_shifts_csr_rec.from_time);
1577 --msg(' l_shift_end_time==>' ||l_date_shifts_csr_rec.to_time);
1578 l_shift_start_time := l_date_shifts_csr_rec.from_time;
1579 l_shift_end_time := l_date_shifts_csr_rec.to_time;
1580
1581 --l_start_date should not be touched. Modify only the x_start_date
1582 if (l_shift_start_time <= l_shift_end_time) then --(200 to 2000 hours)
1583 --msg(' Shift start time < shift end time');
1584 if (l_starting_time between l_shift_start_time and l_shift_end_time) then
1585 --msg(' horizon start time is between shift start and end time');
1586 x_extra_hour := x_extra_hour + (l_shift_end_time - l_starting_time);
1587 --msg('extra hour==>' || x_extra_hour);
1588 x_start_date := l_temp_start_date +1;
1589 elsif (l_starting_time < l_shift_start_time and l_first_shift = true and l_previous = false) then
1590 --msg('part of the start date lies in the yesterdays shift');
1591 --part of start lies in yesterday's shift
1592 --msg('Calculate the extra hour for the yesterdays shift.Calling Cal_Extra_Hour_Start_Dt for starting date -1');
1593 Cal_Extra_Hour_Start_Dt(l_start_date - 1,
1594 true,
1595 l_calendar_code,
1596 l_dept_id,
1597 l_resource_id,
1598 l_temp_start_date,
1599 l_extra_hour);
1600 --msg('amount of extra hours to be added from the yesterday shift.l_extra_hour==>' || l_extra_hour);
1601 x_extra_hour := x_extra_hour + (l_extra_hour *3600);
1602 --msg('after adding the extra hour the x_extra_hour==>' || x_extra_hour);
1603 x_start_date := l_start_date ;
1604 --msg('start_date ==>' || to_char(x_start_date));
1605 exit;
1606 elsif (l_starting_time <= l_shift_start_time and l_previous = false) then
1607 --msg('starting time is less than shift start time but does not lie in yesterdays shift');
1608 --msg('before adding the extra hour for this shift,x_extra_hour==>' || x_extra_hour);
1609 x_extra_hour := x_extra_hour + (l_shift_end_time - l_shift_start_time);
1610 --msg('after adding the extra hour for this shift,x_extra_hour==>' || x_extra_hour);
1611 x_start_date := l_temp_start_date +1;
1612 end if;
1613 elsif (l_shift_start_time > l_shift_end_time) then
1614 --msg('the shift end time is less than the shift start time');
1615 if (l_first_shift = true and l_previous = false and l_starting_time < l_shift_start_time) then
1616 --msg('part of the start date lies in the yesterdays shift.Calling Cal_Extra_Hour_End_Dt for terminatiung date +1');
1617 --part of start lies in yesterday's shift
1618 Cal_Extra_Hour_Start_Dt(l_start_date - 1,
1619 true,
1620 l_calendar_code,
1621 l_dept_id,
1622 l_resource_id,
1623 l_temp_start_date,
1624 l_extra_hour);
1625 --msg('l_extra_hour calculated from yesterdays shift==>' || l_extra_hour);
1626 --msg('x_extra_hour before adding the extra hour==>' || x_extra_hour);
1627 x_extra_hour := x_extra_hour + (l_extra_hour * 3600);
1628 --msg('x_extra_hour after adding the extra hour==>' || x_extra_hour);
1629 x_start_date := l_start_date;
1630 exit;
1631 --- if this is not the first shift, then the complete shift should be accounted for.
1632 elsif (l_first_shift = true and l_previous = true) then
1633 --msg('first shift and came in recursion for previous date');
1634 if (l_starting_time <= l_shift_end_time) then
1635 --msg('starting time is <= shift end time');
1636 --msg('extra hour before addding the extra hour ==> ' || x_extra_hour);
1637 x_extra_hour := x_extra_hour + (l_shift_end_time - l_starting_time);
1638 --msg('extra hour after addding the extra hour ==> ' || x_extra_hour);
1639 exit;
1640 elsif (l_starting_time >= l_shift_start_time) then
1641 --msg('starting time is <= shift start time');
1642 --msg('before adding extra hour, x_extra_hour==> ' || x_extra_hour);
1643 x_extra_hour := x_extra_hour + (86400-l_starting_time) + l_shift_end_time;
1644 --msg('after adding extra hour, x_extra_hour==> ' || x_extra_hour);
1645 exit;
1646 end if;
1647 elsif (l_first_shift = false and l_previous = false) then
1648 --msg('first shift false and previous = false. Add the entire shift');
1649 --msg('extra hour before addding the extra hour ==> ' || x_extra_hour);
1650 x_extra_hour := x_extra_hour + (l_shift_end_time ) + (86400-l_shift_start_time);
1651 --msg('extra hour after addding the extra hour ==> ' || x_extra_hour);
1652 elsif (l_first_shift = false and l_previous = true) then
1653 --msg('first shift is false. l previous = true');
1654 if (l_starting_time <= l_shift_end_time) then
1655 --msg('starting time is <= shift end time');
1656 --msg('extra hour beforfe addding the extra hour ==> ' || x_extra_hour);
1657 x_extra_hour := x_extra_hour + (l_shift_end_time - l_starting_time);
1658 --msg('extra hour after addding the extra hour ==> ' || x_extra_hour);
1659 exit;
1660 elsif (l_starting_time >= l_shift_start_time) then
1661 --msg('starting time is >= shift start time');
1662 --msg('before adding extra hour, x_extra_hour==> ' || x_extra_hour);
1663 x_extra_hour := x_extra_hour + (86400-l_starting_time) + l_shift_end_time;
1664 --msg('after adding extra hour, x_extra_hour==> ' || x_extra_hour);
1665 exit;
1666 elsif (l_previous = false) then
1667 --msg('before adding extra hour, x_extra_hour==> ' || x_extra_hour);
1668 x_extra_hour := x_extra_hour + (86400-l_shift_start_time) + l_shift_end_time;
1669 --msg('after adding extra hour, x_extra_hour==> ' || x_extra_hour);
1670 x_start_date := l_temp_start_date +1;
1671 end if;
1672 end if;
1673 end if;
1674
1675 l_first_shift := false;
1676
1677 END LOOP l_date_shifts_csr_type;
1678 ELSE
1679 null;--no extra hour. continue with the same date
1680 END IF;
1681 x_extra_hour := round((x_extra_hour/3600),2);
1682 --msg('returning from Cal_Extra_Hour_Start_Dt with extra hour==>' || x_extra_hour);
1683 END Cal_Extra_Hour_Start_Dt;
1684
1685 PROCEDURE Cal_Extra_Hour_End_Dt
1686 (
1687 l_end_date IN DATE,
1688 l_previous IN BOOLEAN,
1689 l_calendar_code IN VARCHAR2,
1690 l_dept_id IN NUMBER,
1691 l_resource_id IN NUMBER,
1692 x_end_date OUT NOCOPY DATE,
1693 x_extra_hour OUT NOCOPY NUMBER
1694 )
1695 AS
1696 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
1697 SELECT
1698 bst.from_time,bst.to_time
1699 FROM bom_resource_shifts brs,
1700 bom_shift_dates bsd,
1701 bom_shift_times bst
1702 WHERE brs.department_id = p_dept_id
1703 AND brs.resource_id = p_resource_id
1704 AND brs.shift_num = bsd.shift_num
1705 AND bsd.seq_num is not null
1706 AND bsd.calendar_code = p_calendar_code
1707 AND bsd.shift_date = p_date
1708 AND bst.calendar_code = bsd.calendar_code
1709 AND bst.shift_num = bsd.shift_num;
1710
1711
1712 l_terminating_time NUMBER := 0;
1713 l_first_shift BOOLEAN := FALSE;
1714 l_shift_start_time NUMBER := 0;
1715 l_shift_end_time NUMBER :=0;
1716 l_extra_hour NUMBER := 0;
1717 l_temp_end_date DATE;
1718 BEGIN
1719 --msg('inside Cal_Extra_Hour_End_Dt');
1720 --msg('l_end_date===> ' || to_char(l_end_date));
1721
1722 l_temp_end_date := TRUNC(l_end_date); --shift and calndar table have only date component.
1723 x_extra_hour := 0;
1724 x_end_date := l_temp_end_date;
1725 l_terminating_time := (l_end_date - TRUNC(l_end_date))*86400; -- IN SECONDS
1726 --msg('l_terminating_time===> ' || to_char(l_terminating_time));
1727
1728 IF (Date_Exception(l_temp_end_date,l_calendar_code)='N') THEN --no exception
1729 --msg('date is not in exception');
1730 --msg('getting shifts from l_date_shifts_csr_type');
1731 --get valid shifts.
1732 l_first_shift := true;
1733 FOR l_date_shifts_csr_rec IN l_date_shifts_csr_type(l_dept_id,
1734 l_resource_id,
1735 l_calendar_code,
1736 l_temp_end_date
1737 )
1738 LOOP
1739 EXIT WHEN l_date_shifts_csr_type%NOTFOUND;
1740
1741
1742 l_shift_start_time := l_date_shifts_csr_rec.from_time;
1743 l_shift_end_time := l_date_shifts_csr_rec.to_time;
1744
1745 --msg('Shift dates and times as follows==>');
1746 --msg(' l_shift_start_time==>' || l_date_shifts_csr_rec.from_time);
1747 --msg(' l_shift_end_time==>' ||l_date_shifts_csr_rec.to_time);
1748
1749 --l_end_date should not be touched. Modify only the x_end_date
1750 if (l_shift_start_time < l_shift_end_time) then --(200 to 2000 hours)
1751 --msg(' Shift start time < shift end time');
1752 if (l_terminating_time between l_shift_start_time and l_shift_end_time) then
1753 --msg(' horizon end time is between shift start and end time');
1754 x_extra_hour := x_extra_hour + ( l_terminating_time - l_shift_start_time);
1755 --msg('extra hour==>' || x_extra_hour);
1756
1757 x_end_date := l_temp_end_date - 1;
1758 --msg('x_end_date==>' || to_char(x_end_date));
1759
1760 exit;
1761 elsif (l_terminating_time >= l_shift_start_time and l_terminating_time >= l_shift_end_time) then
1762 --msg('add the entire shifts time to extra hour ');
1763 --msg('before adding extra hour, x_extra_hour==> ' || x_extra_hour);
1764 x_extra_hour := x_extra_hour + (l_shift_end_time - l_shift_start_time);
1765 --msg('after adding extra hour, x_extra_hour==> ' || x_extra_hour);
1766 x_end_date := l_temp_end_date -1;
1767 --msg('x_end_date==>' || to_char(x_end_date));
1768 elsif (l_terminating_time < l_shift_start_time and l_previous = false and l_first_shift = true) then
1769 --msg('the termintaing time lies in last dates shift.Calling Cal_Extra_Hour_End_Dt for terminatiung date -1');
1770 Cal_Extra_Hour_End_Dt(l_end_date - 1,
1771 true,
1772 l_calendar_code,
1773 l_dept_id,
1774 l_resource_id,
1775 l_temp_end_date,
1776 l_extra_hour);
1777 --msg('before adding extra hour, x_extra_hour==> ' || x_extra_hour);
1778 x_extra_hour := x_extra_hour + (l_extra_hour*3600);
1779 --msg('after adding extra hour, x_extra_hour==> ' || x_extra_hour);
1780
1781 if l_extra_hour >0 then
1782 x_end_date := l_end_date - 2;
1783 else
1784 x_end_date := l_end_date - 1;
1785 end if;
1786 --msg('x_end_date==>' || to_char(x_end_date));
1787 exit;
1788 elsif (l_terminating_time <= l_shift_start_time and l_terminating_time <= l_shift_end_time and l_first_shift = false) then
1789 --msg('add the complete shift');
1790 --msg('before adding extra hour, x_extra_hour==> ' || x_extra_hour);
1791 x_extra_hour := x_extra_hour + (l_shift_end_time - l_shift_start_time);
1792 --msg('after adding extra hour, x_extra_hour==> ' || x_extra_hour);
1793 elsif (l_terminating_time < l_shift_start_time and l_previous = true and l_first_shift = true) then
1794 --msg('calculating the extra hour for the previous date');
1795 --msg('before adding extra hour, x_extra_hour==> ' || x_extra_hour);
1796 x_extra_hour := x_extra_hour + (l_shift_end_time - l_shift_start_time);
1797 --msg('after adding extra hour, x_extra_hour==> ' || x_extra_hour);
1798 end if;
1799 elsif (l_shift_start_time > l_shift_end_time) then
1800 --msg('the shift start time is less than the shift end time.');
1801 if (l_first_shift = true and l_previous = false and l_terminating_time < l_shift_start_time) then
1802 --msg('the termintaing time lies in last dates shift.Calling Cal_Extra_Hour_End_Dt for terminatiung date -1');
1803 --part of start lies in yesterdays shift
1804 Cal_Extra_Hour_End_Dt(l_end_date - 1,
1805 true,
1806 l_calendar_code,
1807 l_dept_id,
1808 l_resource_id,
1809 l_temp_end_date,
1810 l_extra_hour);
1811 --msg('before adding extra hour, x_extra_hour==> ' || x_extra_hour);
1812 x_extra_hour := x_extra_hour + (l_extra_hour *3600);
1813 --msg('after adding extra hour, x_extra_hour==> ' || x_extra_hour);
1814 if l_extra_hour >0 then
1815 x_end_date := l_end_date - 2;
1816 else
1817 x_end_date := l_end_date - 1;
1818 end if;
1819 --msg('x_end_date==>' || to_char(x_end_date));
1820 exit;
1821 --- if this is not the first shift, then the complete shift should be accounted for.
1822 elsif (l_first_shift = true and l_previous = true) then
1823 --msg('first shift and came in recursion for previous date');
1824 if (l_terminating_time <= l_shift_end_time) then
1825 --msg('l_terminating_time is <= shift end time');
1826 --msg('extra hour before addding the extra hour ==> ' || x_extra_hour);
1827 x_extra_hour := x_extra_hour + l_terminating_time + (86400 - l_shift_end_time);
1828 --msg('extra hour after addding the extra hour ==> ' || x_extra_hour);
1829 exit;
1830 elsif (l_terminating_time >= l_shift_start_time) then
1831 --msg('l_terminating_time is >= shift start time');
1832 --msg('before adding extra hour, x_extra_hour==> ' || x_extra_hour);
1833 x_extra_hour := x_extra_hour + (l_terminating_time -l_shift_start_time );
1834 --msg('after adding extra hour, x_extra_hour==> ' || x_extra_hour);
1835 exit;
1836 end if;
1837 elsif (l_first_shift = false and l_previous = true) then
1838 --msg('first shift is false. l previous = true');
1839 if (l_terminating_time <= l_shift_end_time) then
1840 --msg('terminating time is less than the end time of the shift');
1841 --msg('before adding extra hour, x_extra_hour==> ' || x_extra_hour);
1842 x_extra_hour := x_extra_hour + (86400 - l_shift_start_time) + l_terminating_time;
1843 --msg('after adding extra hour, x_extra_hour==> ' || x_extra_hour);
1844 elsif (l_terminating_time >= l_shift_start_time) then
1845 --msg('terminating time is greater than the starting time of the shift');
1846 --msg('before adding extra hour, x_extra_hour==> ' || x_extra_hour);
1847 x_extra_hour := x_extra_hour + (l_terminating_time - l_shift_start_time);
1848 --msg('after adding extra hour, x_extra_hour==> ' || x_extra_hour);
1849 elsif (l_previous = false) then
1850 x_extra_hour := x_extra_hour + (86400-l_shift_start_time) + l_shift_end_time;
1851 x_end_date := l_temp_end_date +1;
1852 end if;
1853 elsif (l_first_shift = false and l_previous = false) then
1854 --msg('first shift false and previous = false. Add the entire shift');
1855 --msg('extra hour before addding the extra hour ==> ' || x_extra_hour);
1856 x_extra_hour := x_extra_hour + (l_shift_end_time ) + (86400-l_shift_start_time);
1857 --msg('extra hour after addding the extra hour ==> ' || x_extra_hour);
1858 end if;
1859 end if;
1860 l_first_shift := false;
1861
1862 END LOOP l_date_shifts_csr_type;
1863 ELSE
1864 null;--no extra hour. continue with the same date
1865 END IF;
1866 x_extra_hour := round((x_extra_hour/3600),2);
1867 --msg('returning from Cal_Extra_Hour_End_Dt with x_extra_hour==>' || x_extra_hour);
1868 END Cal_Extra_Hour_End_Dt;
1869
1870 procedure cal_extra_24_hr_st_dt
1871 (
1872 p_start_date IN DATE,
1873 p_calendar_code IN VARCHAR2,
1874 x_end_date OUT NOCOPY DATE,
1875 x_extra_hour OUT NOCOPY NUMBER
1876 )
1877 AS
1878 l_start_date DATE;
1879 l_start_time NUMBER:=0;
1880 l_extra_hour NUMBER:=0;
1881 BEGIN
1882 l_start_date := TRUNC(p_start_date ) ;
1883 l_start_time := (p_start_date - l_start_date)*86400; -- IN SECONDS
1884 IF (Date_Exception(l_start_date,p_calendar_code)='N') THEN
1885 l_extra_hour := (86400-l_start_time);
1886 end if;
1887 x_extra_hour := round((l_extra_hour/3600),2) ;
1888 --msg('x_extra_hour for 24 hr resource start date==>' || x_extra_hour);
1889 x_end_date := l_start_date + 1;
1890 END;
1891
1892 procedure cal_extra_24_hr_end_dt
1893 (
1894 p_end_date IN DATE,
1895 p_calendar_code IN VARCHAR2,
1896 x_end_date OUT NOCOPY DATE,
1897 x_extra_hour OUT NOCOPY NUMBER
1898 )
1899 AS
1900 l_end_date DATE;
1901 l_end_time NUMBER:=0;
1902 l_extra_hour NUMBER:=0;
1903 BEGIN
1904 l_end_date := TRUNC(p_end_date ) ;
1905 l_end_time := (p_end_date - l_end_date)*86400; -- IN SECONDS
1906
1907 IF (Date_Exception(l_end_date,p_calendar_code)='N') THEN
1908 l_extra_hour := (l_end_time);
1909 end if;
1910 x_extra_hour := round((l_extra_hour/3600),2) ;
1911 --msg('x_extra_hour for 24 hr resource end date==>' || x_extra_hour);
1912 x_end_date := l_end_date - 1;
1913 END;
1914
1915 PROCEDURE Cal_Extra_Hour_Same_Dt
1916 (
1917 l_start_date IN DATE,
1918 l_end_date IN DATE,
1919 l_calendar_code IN VARCHAR2,
1920 l_dept_id IN NUMBER,
1921 l_resource_id IN NUMBER,
1922 x_extra_hour OUT NOCOPY NUMBER
1923 )
1924 AS
1925 CURSOR l_date_shifts_csr_type(p_dept_id IN NUMBER,p_resource_id IN
1926 NUMBER,p_calendar_code IN VARCHAR2,p_date IN DATE) IS
1927 SELECT
1928 bst.from_time,bst.to_time
1929 FROM bom_resource_shifts brs,
1930 bom_shift_dates bsd,
1931 bom_shift_times bst
1932 WHERE brs.department_id = p_dept_id
1933 AND brs.resource_id = p_resource_id
1934 AND brs.shift_num = bsd.shift_num
1935 AND bsd.seq_num is not null
1936 AND bsd.calendar_code = p_calendar_code
1937 AND bsd.shift_date = p_date
1938 AND bst.calendar_code = bsd.calendar_code
1939 AND bst.shift_num = bsd.shift_num;
1940
1941
1942 l_start_time NUMBER := 0;
1943 l_end_time NUMBER := 0;
1944 l_shift_start_time NUMBER := 0;
1945 l_shift_end_time NUMBER :=0;
1946 l_extra_hour NUMBER := 0;
1947 l_temp_end_date date;
1948
1949 BEGIN
1950 l_temp_end_date := TRUNC(l_end_date);
1951 x_extra_hour := 0;
1952 l_start_time := (l_start_date - TRUNC(l_start_date))*86400;
1953 if(TRUNC(l_end_date)-TRUNC(l_start_date)=1 and l_end_date-TRUNC(l_end_date) = 0) then
1954 l_end_time := 86400;
1955 else
1956 l_end_time := (l_end_date - TRUNC(l_end_date))*86400;
1957 end if;
1958
1959
1960 FOR l_date_shifts_csr_rec IN l_date_shifts_csr_type(l_dept_id,
1961 l_resource_id,
1962 l_calendar_code,
1963 l_temp_end_date
1964 )
1965 LOOP
1966 EXIT WHEN l_date_shifts_csr_type%NOTFOUND;
1967
1968
1969 l_shift_start_time := l_date_shifts_csr_rec.from_time;
1970 l_shift_end_time := l_date_shifts_csr_rec.to_time;
1971 if(l_start_time<l_shift_start_time) then
1972 if(l_end_time<l_shift_start_time) then
1973 x_extra_hour := x_extra_hour + 0;
1974 elsif(l_end_time=l_shift_start_time) then
1975 x_extra_hour := x_extra_hour + 0;
1976 elsif(l_end_time>l_shift_start_time and l_end_time<l_shift_end_time)
1977 then
1978 x_extra_hour := x_extra_hour + l_end_time - l_shift_start_time;
1979 elsif(l_end_time=l_shift_end_time) then
1980 x_extra_hour := x_extra_hour + l_end_time - l_shift_start_time;
1981 elsif(l_end_time>l_shift_end_time) then
1982 x_extra_hour := x_extra_hour + l_shift_end_time -
1983 l_shift_start_time;
1984 end if;
1985
1986 elsif(l_start_time=l_shift_start_time) then
1987 if(l_end_time=l_shift_start_time) then
1988 x_extra_hour := x_extra_hour + 0;
1989 elsif(l_end_time>l_shift_start_time and l_end_time<l_shift_end_time)
1990 then
1991 x_extra_hour := x_extra_hour + l_end_time - l_start_time;
1992 elsif(l_end_time=l_shift_end_time) then
1993 x_extra_hour := x_extra_hour + l_end_time - l_start_time;
1994 elsif(l_end_time>l_shift_end_time) then
1995 x_extra_hour := x_extra_hour + l_shift_end_time - l_start_time;
1996 end if;
1997
1998 elsif(l_start_time>l_shift_start_time and l_start_time<l_shift_end_time)
1999 then
2000 if(l_end_time>l_shift_start_time and l_end_time<l_shift_end_time) then
2001 x_extra_hour := x_extra_hour + l_end_time - l_start_time;
2002 elsif(l_end_time=l_shift_end_time) then
2003 x_extra_hour := x_extra_hour + l_end_time - l_start_time;
2004 elsif(l_end_time>l_shift_end_time) then
2005 x_extra_hour := x_extra_hour + l_shift_end_time - l_start_time;
2006 end if;
2007 elsif(l_start_time>=l_shift_end_time) then
2008 x_extra_hour := x_extra_hour + 0;
2009 end if;
2010 END LOOP l_date_shifts_csr_type;
2011 x_extra_hour := round((x_extra_hour/3600),2);
2012 END Cal_Extra_Hour_Same_Dt;
2013
2014
2015 PROCEDURE Cal_Extra_Hour_Generic
2016 (
2017 l_start_date IN DATE,
2018 l_end_date IN DATE,
2019 l_calendar_code IN VARCHAR2,
2020 l_dept_id IN NUMBER,
2021 l_resource_id IN NUMBER,
2022 x_extra_hour OUT NOCOPY NUMBER
2023 )
2024 AS
2025 l_extra_hour NUMBER := 0;
2026 l_temp_date1 DATE;
2027 l_temp_date DATE;
2028 BEGIN
2029 x_extra_hour := 0;
2030
2031 if(l_end_date-l_start_date<=1) then
2032 if(TRUNC(l_end_date)-TRUNC(l_start_date) = 0) then
2033 Cal_Extra_Hour_Same_Dt
2034 (
2035 l_start_date ,
2036 l_end_date ,
2037 l_calendar_code ,
2038 l_dept_id ,
2039 l_resource_id ,
2040 l_extra_hour
2041 );
2042 x_extra_hour := x_extra_hour+l_extra_hour;
2043 else
2044 l_temp_date1 := TRUNC(l_end_date);
2045 Cal_Extra_Hour_Same_Dt
2046 (
2047 l_start_date ,
2048 l_temp_date1,
2049 l_calendar_code ,
2050 l_dept_id ,
2051 l_resource_id ,
2052 l_extra_hour
2053 );
2054 x_extra_hour := x_extra_hour+l_extra_hour;
2055 Cal_Extra_Hour_Same_Dt
2056 (
2057 l_temp_date1,
2058 l_end_date ,
2059 l_calendar_code ,
2060 l_dept_id ,
2061 l_resource_id ,
2062 l_extra_hour
2063 );
2064 x_extra_hour := x_extra_hour+l_extra_hour;
2065 end if;
2066 else
2067 l_temp_date := l_start_date;
2068 while l_temp_date + 1 < l_end_date loop
2069 Cal_Extra_Hour_Generic
2070 (
2071 l_temp_date,
2072 l_temp_date+1 ,
2073 l_calendar_code ,
2074 l_dept_id ,
2075 l_resource_id ,
2076 l_extra_hour
2077 );
2078 x_extra_hour := x_extra_hour+l_extra_hour;
2079 l_temp_date := l_temp_date+1;
2080 end loop;
2081 Cal_Extra_Hour_Generic
2082 (
2083 l_temp_date,
2084 l_end_date,
2085 l_calendar_code ,
2086 l_dept_id ,
2087 l_resource_id ,
2088 l_extra_hour
2089 );
2090 x_extra_hour := x_extra_hour+l_extra_hour;
2091
2092 end if;
2093 end Cal_Extra_Hour_Generic;
2094
2095 ------------------------Helper Functions--------------------------
2096
2097
2098 FUNCTION Fetch_Details
2099 (
2100 p_op_res_end_dt IN DATE
2101 )
2102 RETURN VARCHAR2
2103 AS
2104 l_fetch_employee_results VARCHAR2(1);
2105 BEGIN
2106 IF (p_op_res_end_dt >= sysdate) THEN
2107 l_fetch_employee_results := 'Y';
2108 ELSE
2109 l_fetch_employee_results := 'N';
2110 END IF;
2111 --msg('Work Order completion date in future?'||l_fetch_employee_results);
2112 RETURN l_fetch_employee_results;
2113 END Fetch_Details;
2114
2115 END EAM_ASSIGN_EMP_PUB;