[Home] [Help]
PACKAGE BODY: APPS.EAM_WO_SCHEDULE_PVT
Source
1 PACKAGE BODY EAM_WO_SCHEDULE_PVT AS
2 /* $Header: EAMVSCDB.pls 120.10.12020000.3 2013/02/08 11:20:51 antmishr ship $ */
3 /***************************************************************************
4 --
5 -- Copyright (c) 2002 Oracle Corporation, Redwood Shores, CA, USA
6 -- All rights reserved.
7 --
8 -- FILENAME
9 --
10 -- EAMVSCDB.pls
11 --
12 -- DESCRIPTION
13 --
14 -- Body of package EAM_WO_SCHEDULE_PVT
15 --
16 -- NOTES
17 --
18 -- HISTORY
19 --
20 -- 30-JUN-2002 Kenichi Nagumo Initial Creation
21 ***************************************************************************/
22
23 G_Pkg_Name VARCHAR2(30) := 'EAM_WO_SCHEDULE_PVT';
24
25 g_token_tbl EAM_ERROR_MESSAGE_PVT.Token_Tbl_Type;
26 g_dummy NUMBER;
27
28 PROCEDURE EAM_GET_SHIFT_WKDAYS
29 ( p_curr_date IN DATE,
30 p_calendar_code IN VARCHAR2,
31 p_shift_num IN NUMBER,
32 p_schedule_dir IN NUMBER,
33 x_wkday_flag OUT NOCOPY NUMBER,
34 x_error_message OUT NOCOPY VARCHAR2,
35 x_return_status OUT NOCOPY VARCHAR2)
36 IS
37 l_calendar_index NUMBER;
38 l_cal_rec_count NUMBER;
39 l_cal_first_date DATE;
40 l_cal_last_date DATE;
41 l_shift_first_index_date DATE;
42 l_shift_last_index_date DATE;
43 OUT_OF_CAL_EXC EXCEPTION;
44
45 CURSOR EAM_GET_FWD_SHIFT_DATES_CSR IS
46 SELECT SHIFT_NUM,
47 SHIFT_DATE,
48 SEQ_NUM,
49 CALENDAR_CODE
50 FROM BOM_SHIFT_DATES
51 WHERE CALENDAR_CODE = p_calendar_code
52 AND SHIFT_DATE >= trunc(p_curr_date)
53 AND SHIFT_DATE <= trunc(p_curr_date) + 50
54 AND EXCEPTION_SET_ID = -1
55 ORDER BY SHIFT_NUM, SHIFT_DATE;
56
57 CURSOR EAM_GET_BKWD_SHIFT_DATES_CSR IS
58 SELECT SHIFT_NUM,
59 SHIFT_DATE,
60 SEQ_NUM,
61 CALENDAR_CODE
62 FROM BOM_SHIFT_DATES
63 WHERE CALENDAR_CODE = p_calendar_code
64 AND SHIFT_DATE >= trunc(p_curr_date) - 50
65 AND SHIFT_DATE <= trunc(p_curr_date)
66 AND EXCEPTION_SET_ID = -1
67 ORDER BY SHIFT_NUM DESC, SHIFT_DATE DESC;
68
69 BEGIN
70
71 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Enters eam_get_shift_wkdays'||p_shift_num||'in'||p_curr_date||'for'||p_calendar_code) ; END IF ;
72
73 l_cal_rec_count := shift_date_tbl.LAST;
74
75 IF(l_cal_rec_count IS NOT NULL) THEN --if shift table exists in memory
76 l_shift_first_index_date := shift_date_tbl(shift_date_tbl.FIRST).SHIFT_DATE; --the first date in the shift table
77 l_shift_last_index_date := shift_date_tbl(l_cal_rec_count).SHIFT_DATE; --the last date in the shift table
78 END IF;
79
80 IF p_schedule_dir = 1 THEN
81 --fix for bug 4201713.execute the query to find the shifts only if the date passed is not already there in the shift table
82 IF (l_cal_rec_count IS NULL)
83 OR (l_cal_rec_count IS NOT NULL AND
84 ((p_calendar_code <> shift_date_tbl(l_cal_rec_count).calendar_code ) OR --if calendar is different or
85 --if p_curr_date not exists between the shift dates in the table. Need to check 2 conditions as shift table can be organised in ascending/descending order for fwd/backward scheduling.
86 (NOT ((l_shift_first_index_date <= p_curr_date AND l_shift_last_index_date >= p_curr_date) OR (l_shift_first_index_date >= p_curr_date AND l_shift_last_index_date <= p_curr_date))))
87 ) THEN
88
89 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Shift query executed') ; END IF ;
90
91 shift_date_tbl.delete;
92 l_calendar_index := 0;
93
94 FOR l_tab_shift_rec IN EAM_GET_FWD_SHIFT_DATES_CSR LOOP
95 l_calendar_index := l_calendar_index + 1;
96 shift_date_tbl(l_calendar_index).SHIFT_NUM := l_tab_shift_rec.shift_num;
97 shift_date_tbl(l_calendar_index).SHIFT_DATE := l_tab_shift_rec.shift_date;
98 shift_date_tbl(l_calendar_index).SEQ_NUM := l_tab_shift_rec.seq_num;
99 END LOOP;
100
101 IF l_calendar_index = 0 THEN
102 RAISE OUT_OF_CAL_EXC;
103 END IF;
104 END IF; /* for populating shift_date_tbl */
105
106 ELSE
107 IF (l_cal_rec_count IS NULL)
108 OR (l_cal_rec_count IS NOT NULL AND
109 ((p_calendar_code <> shift_date_tbl(l_cal_rec_count).calendar_code ) OR --if calendar is different or
110 --if p_curr_date not exists between the shift dates in the table. Need to check 2 conditions as shift table can be organised in ascending/descending order for fwd/backward scheduling.
111 (NOT ((l_shift_first_index_date <= p_curr_date AND l_shift_last_index_date >= p_curr_date) OR (l_shift_first_index_date >= p_curr_date AND l_shift_last_index_date <= p_curr_date))))
112 ) THEN
113
114 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Shift query executed') ; END IF ;
115
116 shift_date_tbl.delete;
117 l_calendar_index := 0;
118
119 FOR l_tab_shift_rec IN EAM_GET_BKWD_SHIFT_DATES_CSR LOOP
120
121 l_calendar_index := l_calendar_index + 1;
122 shift_date_tbl(l_calendar_index).SHIFT_NUM := l_tab_shift_rec.shift_num;
123 shift_date_tbl(l_calendar_index).SHIFT_DATE := l_tab_shift_rec.shift_date;
124 shift_date_tbl(l_calendar_index).SEQ_NUM := l_tab_shift_rec.seq_num;
125 END LOOP;
126
127 IF l_calendar_index = 0 THEN
128 RAISE OUT_OF_CAL_EXC;
129 END IF;
130 END IF; /* for populating shift_date_tbl for Backward scheduling*/
131 END IF;/* scheduling direction */
132
133 l_calendar_index := shift_date_tbl.FIRST;
134 WHILE l_calendar_index is not NULL LOOP
135 IF (shift_date_tbl(l_calendar_index).SHIFT_NUM = p_shift_num AND
136 shift_date_tbl(l_calendar_index).SHIFT_DATE = trunc(p_curr_date)) THEN
137 IF shift_date_tbl(l_calendar_index).seq_num IS NOT NULL THEN
138 x_wkday_flag := 1;
139 ELSE
140 x_wkday_flag := 2;
141 END IF;
142
143 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Enters x_wkday_flag'||x_wkday_flag) ; END IF ;
144
145 EXIT;
146 END IF;
147 l_calendar_index := shift_date_tbl.NEXT(l_calendar_index);
148 END LOOP;
149
150 EXCEPTION WHEN OUT_OF_CAL_EXC THEN
151 x_return_status := fnd_api.g_ret_sts_error;
152 x_error_message := 'DATE OUT OF CALENDAR';
153 IF EAM_PROCESS_WO_PVT. Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Exception raised.No entry found for this date, calendar and exception_set_id combination'||p_shift_num||'in'||p_curr_date) ; END IF ;
154
155 END;
156
157
158
159
160
161
162 PROCEDURE EAM_GET_SHIFT_NUM
163 ( p_curr_time IN OUT NOCOPY NUMBER,
164 p_schedule_dir IN NUMBER,
165 l_res_sft_tbl IN l_res_sft_tab,
166 p_curr_index IN NUMBER ,
167 x_shift_num OUT NOCOPY NUMBER,
168 x_from_time OUT NOCOPY NUMBER,
169 x_to_time OUT NOCOPY NUMBER,
170 x_error_message OUT NOCOPY VARCHAR2,
171 x_return_status OUT NOCOPY VARCHAR2,
172 x_index_at OUT NOCOPY NUMBER
173 )
174 IS
175 l_next_shift_num NUMBER := -1;
176 l_next_start_time NUMBER;
177 l_res_sft_index NUMBER;
178 l_curr_time1 NUMBER := -1;
179 temp NUMBER;
180
181 BEGIN
182
183 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Starting EAM_GET_SHIFT_NUM') ; END IF ;
184
185 l_next_start_time := p_curr_time;
186 l_res_sft_index := p_curr_index ; /* Bug 4738273 : Changed code to set index to p_curr_index */
187
188 WHILE l_res_sft_index is not NULL LOOP
189
190 IF ((p_curr_time >= l_res_sft_tbl(l_res_sft_index).from_time
191 AND p_curr_time < l_res_sft_tbl(l_res_sft_index).to_time)
192 OR
193 ((p_curr_time >= l_res_sft_tbl(l_res_sft_index).from_time
194 OR p_curr_time < l_res_sft_tbl(l_res_sft_index).to_time)
195 AND
196 (l_res_sft_tbl(l_res_sft_index).to_time -
197 l_res_sft_tbl(l_res_sft_index).from_time) <= 0))
198 AND p_schedule_dir = 1
199 OR
200 ((p_curr_time > l_res_sft_tbl(l_res_sft_index).from_time
201 AND p_curr_time <= l_res_sft_tbl(l_res_sft_index).to_time)
202 OR
203 ((p_curr_time > l_res_sft_tbl(l_res_sft_index).from_time
204 OR p_curr_time <= l_res_sft_tbl(l_res_sft_index).to_time)
205 AND
206 (l_res_sft_tbl(l_res_sft_index).to_time -
207 l_res_sft_tbl(l_res_sft_index).from_time) <= 0))
208 AND p_schedule_dir = 2
209 THEN
210 x_shift_num := l_res_sft_tbl(l_res_sft_index).shift_num;
211 x_from_time := l_res_sft_tbl(l_res_sft_index).from_time ;
212 x_to_time := l_res_sft_tbl(l_res_sft_index).to_time ;
213 l_curr_time1 := p_curr_time ;
214 x_index_at := l_res_sft_index ; /* Bug 4738273 : Added line */
215 EXIT;
216 ELSIF l_res_sft_tbl(l_res_sft_index).from_time > p_curr_time
217 AND p_schedule_dir = 1 THEN
218
219 IF l_next_start_time > l_res_sft_tbl(l_res_sft_index).from_time
220 OR l_next_start_time = p_curr_time THEN
221
222 l_next_shift_num := l_res_sft_tbl(l_res_sft_index).shift_num;
223 x_shift_num := l_next_shift_num;
224 x_from_time := l_res_sft_tbl(l_res_sft_index).from_time;
225 x_to_time := l_res_sft_tbl(l_res_sft_index).to_time;
226 l_next_start_time := l_res_sft_tbl(l_res_sft_index).from_time;
227 l_curr_time1 := l_res_sft_tbl(l_res_sft_index).from_time;
228 x_index_at := l_res_sft_index ; /* Bug 4738273 : Added line */
229 END IF;
230 ELSIF l_res_sft_tbl(l_res_sft_index).to_time < p_curr_time
231 AND p_schedule_dir = 2 THEN
232 IF l_next_start_time < l_res_sft_tbl(l_res_sft_index).to_time
233 OR l_next_start_time = p_curr_time THEN
234
235 l_next_shift_num := l_res_sft_tbl(l_res_sft_index).shift_num;
236 x_shift_num := l_next_shift_num;
237 x_from_time := l_res_sft_tbl(l_res_sft_index).from_time;
238 x_to_time := l_res_sft_tbl(l_res_sft_index).to_time;
239 l_next_start_time := l_res_sft_tbl(l_res_sft_index).to_time;
240 l_curr_time1 := l_res_sft_tbl(l_res_sft_index).to_time;
241 x_index_at := l_res_sft_index ; /* Bug 4738273 : Added line */
242 END IF;
243
244 END IF; -- shift identifier
245 l_res_sft_index := l_res_sft_tbl.NEXT(l_res_sft_index);
246 END LOOP;
247
248 IF l_curr_time1 <> -1 THEN
249 p_curr_time := l_curr_time1;
250 END IF;
251 END; /* EAM_GET_SHIFT_NUM */
252
253
254
255
256
257
258
259 PROCEDURE SCHEDULE_RESOURCES
260 ( p_organization_id IN NUMBER,
261 p_wip_entity_id IN NUMBER,
262 p_op_seq_num IN NUMBER,
263 p_schedule_dir IN NUMBER,
264 p_calendar_code IN VARCHAR2,
265 op_res_info_tbl IN op_res_info_tab,
266 op_res_sft_tbl IN op_res_sft_tab,
267 p_op_start_date IN OUT NOCOPY DATE,
268 p_op_completion_date IN OUT NOCOPY DATE,
269 p_res_usage_tbl IN OUT NOCOPY EAM_PROCESS_WO_PUB.eam_res_usage_tbl_type,
270 p_validation_level IN NUMBER,
271 p_commit IN VARCHAR2,
272 x_error_message OUT NOCOPY VARCHAR2,
273 x_return_status OUT NOCOPY VARCHAR2
274 )
275 IS
276 i NUMBER := 0;
277 l_res_start_date DATE ;
278 l_res_completion_date DATE ;
279 l_next_res_start_date DATE ;
280 l_next_res_completion_date DATE ;
281 l_prior_res_completion_date DATE ;
282 l_res_lead_time NUMBER ;
283 l_res_seq_num NUMBER ;
284 l_curr_date DATE ;
285 l_curr_time NUMBER ;
286 l_shift_num NUMBER ;
287 l_curr_index NUMBER ; /* Bug 4738273 : Added l_curr_index, l_out_index */
288 l_out_index NUMBER ;
289 l_from_time NUMBER ;
290 l_to_time NUMBER ;
291 l_op_res_sft_index NUMBER ;
292 l_res_sft_index NUMBER ;
293 l_res_sft_count NUMBER ;
294 l_wkday_flag NUMBER;
295 l_sft_avail_time NUMBER;
296 l_stmt_num NUMBER := 200;
297 NO_SFT_EXC EXCEPTION;
298
299 l_min_rsc_start_date DATE;
300 l_max_rsc_end_date DATE;
301 l_rsc_index NUMBER;
302 l_shift_index NUMBER;
303
304 t_start_date DATE;
305 t_end_date DATE;
306 l_eam_res_usage_rec EAM_PROCESS_WO_PUB.eam_res_usage_rec_type;
307
308 BEGIN /*SCHEDULE_RESOURCES*/
309
310 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Starting EAM_CALC_OPERATION_TIME') ; END IF ;
311 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Scheduling Direction '|| p_schedule_dir) ; END IF ;
312
313 IF p_schedule_dir = 1 THEN
314 i := op_res_info_tbl.FIRST;
315 ELSE
316 i := op_res_info_tbl.LAST;
317 END IF;
318
319
320
321 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Initializing date parameters') ; END IF ;
322
323
324 l_res_start_date := p_op_start_date;
325 l_res_completion_date := p_op_completion_date;
326 l_next_res_start_date := p_op_start_date ;
327 l_next_res_completion_date := p_op_completion_date;
328
329
330 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Entering Loop') ; END IF ;
331 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Scheduling Operation '|| p_op_seq_num) ; END IF ;
332
333 l_rsc_index := 1; --initialise the resource index
334
335 WHILE i is not NULL LOOP
336
337
338 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Within Loop') ; END IF ;
339
340 l_res_lead_time := 0;
341
342 IF (op_res_info_tbl(i).op_seq_num = p_op_seq_num) /*fix3725352 and (NVL(op_res_info_tbl(i).op_completed, 'N') <> 'Y')*/ THEN
343
344 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Within op_seq_num and op_completed check') ; END IF ;
345
346 IF op_res_info_tbl(i).scheduled_flag = 1 THEN
347
348 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Within scheduled_flag check') ; END IF ;
349
350 IF op_res_info_tbl(i).avail_24_hrs_flag = 1 THEN
351
352 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Resource available 24 hr') ; END IF ;
353
354 /* Calculating the leadtime in seconds */ /* mmaduska */
355 l_res_lead_time := op_res_info_tbl(i).usage_rate/op_res_info_tbl(i).assigned_units;
356
357 IF p_schedule_dir = 1 THEN /* forward schedule */
358
359 if (i > op_res_info_tbl.FIRST ) and (op_res_info_tbl(i).res_sch_num = op_res_info_tbl(op_res_info_tbl.PRIOR(i)).res_sch_num)
360 and (op_res_info_tbl(i).op_seq_num = op_res_info_tbl(op_res_info_tbl.PRIOR(i)).op_seq_num) then
361
362 l_res_completion_date := l_res_start_date + (l_res_lead_time/86400);
363
364 if l_res_completion_date > l_next_res_start_date then
365 l_next_res_start_date := l_res_completion_date;
366 end if;
367
368 else
369
370 l_res_start_date := l_next_res_start_date;
371 l_res_completion_date := l_res_start_date + (l_res_lead_time/86400);
372 l_next_res_start_date := l_res_completion_date;
373
374 end if;
375
376
377
378 ELSE
379
380 -- dbms_output.put_line ('i is ' || i);
381
382 if (i > op_res_info_tbl.LAST ) and (op_res_info_tbl(i).res_sch_num = op_res_info_tbl(op_res_info_tbl.NEXT(i)).res_sch_num)
383 and (op_res_info_tbl(i).op_seq_num = op_res_info_tbl(op_res_info_tbl.NEXT(i)).op_seq_num) then
384
385 l_res_start_date := l_res_completion_date - (l_res_lead_time/86400);
386
387 if l_res_start_date < l_next_res_completion_date then
388 l_next_res_completion_date := l_res_start_date;
389 end if;
390
391 else
392 l_res_completion_date := l_next_res_completion_date;
393 l_res_start_date := l_res_completion_date - (l_res_lead_time/86400);
394 l_next_res_completion_date := l_res_start_date;
395
396 end if;
397
398 -- dbms_output.put_line ('l_res_start_date is ' || l_res_start_date);
399
400 END IF;/* For forward and backward schedule */
401
402 l_eam_res_usage_rec.operation_seq_num:= p_op_seq_num;
403 l_eam_res_usage_rec.resource_seq_num := op_res_info_tbl(i).res_seq_num;
404 l_eam_res_usage_rec.start_date := l_res_start_date;
405 l_eam_res_usage_rec.completion_date := l_res_completion_date;
406 l_eam_res_usage_rec.assigned_units := op_res_info_tbl(i).assigned_units;
407
408 if p_res_usage_tbl.count > 0 then
409 p_res_usage_tbl(p_res_usage_tbl.count+1):=l_eam_res_usage_rec;
410 else
411 p_res_usage_tbl(1):=l_eam_res_usage_rec;
412 end if;
413
414 ELSE
415 -- Logic for shifts
416 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Reresource based on shift') ; END IF ;
417
418
419 l_res_seq_num := op_res_info_tbl(i).res_seq_num;
420 /* mmaduska */
421 l_res_lead_time := op_res_info_tbl(i).usage_rate/op_res_info_tbl(i).assigned_units;
422 l_op_res_sft_index := op_res_sft_tbl.FIRST;
423 l_res_sft_tbl.delete;
424 l_res_sft_count := 0;
425 l_res_sft_index := 0;
426
427 WHILE l_op_res_sft_index is not NULL LOOP
428
429 IF op_res_sft_tbl(l_op_res_sft_index).op_seq_num = p_op_seq_num AND
430 op_res_sft_tbl(l_op_res_sft_index).res_seq_num = l_res_seq_num THEN
431 --fix for bug 3355437.commented the following code so that pl/sql tables will be initialized always
432 /* l_res_sft_index := l_res_sft_tbl.LAST;
433
434 IF l_res_sft_index IS NULL THEN
435 l_res_sft_index := 0;
436 END IF;*/
437
438 l_res_sft_index := l_res_sft_index + 1;
439 l_res_sft_tbl(l_res_sft_index).shift_num := op_res_sft_tbl(l_op_res_sft_index).shift_num;
440 l_res_sft_tbl(l_res_sft_index).from_time := op_res_sft_tbl(l_op_res_sft_index).from_time;
441 l_res_sft_tbl(l_res_sft_index).to_time := op_res_sft_tbl(l_op_res_sft_index).to_time;
442 l_res_sft_count := l_res_sft_count + 1;
443
444 END IF;
445
446 l_op_res_sft_index := op_res_sft_tbl.NEXT(l_op_res_sft_index);
447
448 END LOOP;
449
450 IF l_res_sft_count = 0 THEN
451 RAISE NO_SFT_EXC;
452 END IF;
453
454 IF p_schedule_dir = 1 THEN
455
456 if NOT ((i > op_res_info_tbl.FIRST ) and (op_res_info_tbl(i).res_sch_num = op_res_info_tbl(op_res_info_tbl.PRIOR(i)).res_sch_num)
457 and (op_res_info_tbl(i).op_seq_num = op_res_info_tbl(op_res_info_tbl.PRIOR(i)).op_seq_num)) then
458
459 l_res_start_date := l_next_res_start_date;
460
461 end if;
462
463 l_curr_time := (l_res_start_date - trunc(l_res_start_date)) * 86400 ;
464 l_curr_date := l_res_start_date;
465
466 l_shift_index := 1; --initialise shift index to 1
467 l_curr_index := l_res_sft_tbl.FIRST ;
468
469 WHILE l_res_lead_time > 0 LOOP
470
471 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Enters loop for lead time'||l_res_lead_time) ; END IF ;
472
473 eam_get_shift_num
474 ( l_curr_time,
475 p_schedule_dir,
476 l_res_sft_tbl,
477 l_curr_index,
478 l_shift_num,
479 l_from_time,
480 l_to_time,
481 x_error_message,
482 x_return_status,
483 l_out_index
484 );
485
486 IF l_shift_num IS NULL THEN
487 l_curr_date := trunc(l_curr_date) + 1;
488 l_curr_time := 0;
489
490 eam_get_shift_num
491 ( l_curr_time,
492 p_schedule_dir,
493 l_res_sft_tbl,
494 l_curr_index,
495 l_shift_num,
496 l_from_time,
497 l_to_time,
498 x_error_message,
499 x_return_status,
500 l_out_index
501 );
502 ELSE
503 IF ( l_out_index IS NOT NULL ) THEN
504 l_curr_index := l_res_sft_tbl.NEXT( l_out_index ) ;
505 END IF ;
506 END IF;
507
508 eam_get_shift_wkdays
509 ( l_curr_date,
510 p_calendar_code,
511 l_shift_num,
512 p_schedule_dir,
513 l_wkday_flag,
514 x_error_message,
515 x_return_status
516 );
517
518 IF(x_return_status <>'S') THEN
519 RETURN;
520 END IF;
521
522 IF l_wkday_flag = 1 THEN
523
524 IF (l_shift_index = 1) THEN --store the value of first valid shift
525 l_res_start_date := trunc(l_curr_date) + l_curr_time/86400 ;
526 END IF;
527
528 l_shift_index := l_shift_index + 1;
529
530
531 IF ( (l_to_time - l_from_time) <= 0 AND l_curr_time >= l_from_time) THEN
532 l_sft_avail_time := 86400 - l_curr_time;
533 ELSE
534 l_sft_avail_time := l_to_time - l_curr_time;
535 END IF;/* avail time */
536
537 IF l_sft_avail_time >= l_res_lead_time THEN
538
539 l_res_completion_date := trunc(l_curr_date) + (l_curr_time + l_res_lead_time)/86400;
540
541 t_start_date :=trunc(l_curr_date) + (l_curr_time)/86400;
542
543 l_eam_res_usage_rec.operation_seq_num:= p_op_seq_num;
544 l_eam_res_usage_rec.resource_seq_num := op_res_info_tbl(i).res_seq_num;
545 l_eam_res_usage_rec.start_date := t_start_date;
546 l_eam_res_usage_rec.completion_date := l_res_completion_date;
547 l_eam_res_usage_rec.assigned_units := op_res_info_tbl(i).assigned_units;
548
549 if p_res_usage_tbl.count > 0 then
550 p_res_usage_tbl(p_res_usage_tbl.count+1):=l_eam_res_usage_rec;
551 else
552 p_res_usage_tbl(1):=l_eam_res_usage_rec;
553 end if;
554
555 if (i > op_res_info_tbl.FIRST ) and (op_res_info_tbl(i).res_sch_num = op_res_info_tbl(op_res_info_tbl.PRIOR(i)).res_sch_num)
556 and (op_res_info_tbl(i).op_seq_num = op_res_info_tbl(op_res_info_tbl.PRIOR(i)).op_seq_num) then
557
558 if l_res_completion_date > l_next_res_start_date then
559 l_next_res_start_date := l_res_completion_date;
560 end if;
561
562 else
563 l_next_res_start_date := l_res_completion_date;
564 end if;
565
566 l_res_lead_time := 0;
567 ELSE
568 l_res_lead_time := l_res_lead_time - l_sft_avail_time;
569 l_curr_time := l_curr_time + l_sft_avail_time;
570 IF l_curr_time >= 86400 THEN
571 l_curr_time := l_curr_time - 86400;
572 l_curr_date := trunc(l_curr_date) + 1;
573 END IF;
574
575 t_end_date := trunc(l_curr_date) + (l_curr_time/86400);
576 t_start_date := t_end_date - (l_sft_avail_time/86400);
577
578 l_eam_res_usage_rec.operation_seq_num:= p_op_seq_num;
579 l_eam_res_usage_rec.resource_seq_num := op_res_info_tbl(i).res_seq_num;
580 l_eam_res_usage_rec.start_date := t_start_date;
581 l_eam_res_usage_rec.completion_date := t_end_date;
582 l_eam_res_usage_rec.assigned_units := op_res_info_tbl(i).assigned_units;
583
584 if p_res_usage_tbl.count > 0 then
585 p_res_usage_tbl(p_res_usage_tbl.count+1):=l_eam_res_usage_rec;
586 else
587 p_res_usage_tbl(1):=l_eam_res_usage_rec;
588 end if;
589
590 END IF;/* avail time */
591 l_curr_index := l_res_sft_tbl.FIRST ;
592
593 ELSE
594 IF ( l_out_index IS NOT NULL AND l_res_sft_tbl.NEXT(l_out_index) IS NULL ) THEN
595 IF l_to_time <= l_from_time THEN
596 l_curr_date := trunc(l_curr_date) + 1;
597 l_curr_time := 0 ;
598 ELSE
599 l_curr_time := l_to_time + 1;
600 END IF;
601 l_curr_index := l_res_sft_tbl.FIRST ;
602
603 END IF ;
604 END IF; /*wkdays flag */
605
606 END LOOP;
607
608 ELSE
609
610 if NOT ((i > op_res_info_tbl.LAST ) and (op_res_info_tbl(i).res_sch_num = op_res_info_tbl(op_res_info_tbl.NEXT(i)).res_sch_num)
611 and (op_res_info_tbl(i).op_seq_num = op_res_info_tbl(op_res_info_tbl.NEXT(i)).op_seq_num)) then
612
613 l_res_completion_date := l_next_res_completion_date;
614
615 end if;
616
617 l_curr_time := (l_res_completion_date - trunc(l_res_completion_date)) * 86400 ;
618 l_curr_date := l_res_completion_date;
619
620 l_shift_index := 1; --initialise shift index to 1
621 l_curr_index := l_res_sft_tbl.FIRST ;
622
623 WHILE l_res_lead_time > 0 LOOP
624
625 eam_get_shift_num ( l_curr_time ,
626 p_schedule_dir ,
627 l_res_sft_tbl ,
628 l_curr_index ,
629 l_shift_num ,
630 l_from_time ,
631 l_to_time ,
632 x_error_message ,
633 x_return_status ,
634 l_out_index );
635
636
637 IF l_shift_num IS NULL THEN
638 l_curr_date := trunc(l_curr_date) - 1;
639 l_curr_time := 86400;
640
641 eam_get_shift_num ( l_curr_time ,
642 p_schedule_dir ,
643 l_res_sft_tbl ,
644 l_curr_index ,
645 l_shift_num ,
646 l_from_time ,
647 l_to_time ,
648 x_error_message ,
649 x_return_status,
650 l_out_index
651 );
652
653 ELSE
654 IF ( l_out_index IS NOT NULL ) THEN
655 l_curr_index := l_res_sft_tbl.NEXT( l_out_index ) ;
656 END IF ;
657 END IF;
658
659 eam_get_shift_wkdays
660 (l_curr_date,
661 p_calendar_code,
662 l_shift_num,
663 p_schedule_dir,
664 l_wkday_flag ,
665 x_error_message ,
666 x_return_status );
667
668 IF(x_return_status <>'S') THEN
669 RETURN;
670 END IF;
671
672 IF l_wkday_flag = 1 THEN
673
674 IF (l_shift_index = 1) THEN --copy the last valid shift time to resource completion date
675
676 --start for 7264665
677 if (op_res_info_tbl.LAST >i ) and (op_res_info_tbl(i).res_sch_num = op_res_info_tbl(op_res_info_tbl.NEXT(i)).res_sch_num)
678 and (op_res_info_tbl(i).op_seq_num = op_res_info_tbl(op_res_info_tbl.NEXT(i)).op_seq_num) then
679 l_res_completion_date := trunc(l_curr_date) +(l_curr_time+l_res_lead_time)/86400 ;
680 else
681 l_res_completion_date := trunc(l_curr_date) + l_curr_time/86400 ;
682 end if;
683 --end for 7264665
684
685 END IF; --end of if for shift_index=1
686 l_shift_index := l_shift_index+1;
687
688 IF ( (l_to_time - l_from_time) <= 0
689 AND l_curr_time <= l_to_time) THEN
690 l_sft_avail_time := l_curr_time - 0 ;
691 ELSE
692 l_sft_avail_time := l_curr_time - l_from_time;
693 END IF;/* avail time */
694
695 IF l_sft_avail_time >= l_res_lead_time THEN
696
697 --start for 7264665
698
699 if (op_res_info_tbl.LAST >i ) and (op_res_info_tbl(i).res_sch_num = op_res_info_tbl(op_res_info_tbl.NEXT(i)).res_sch_num)
700 and (op_res_info_tbl(i).op_seq_num = op_res_info_tbl(op_res_info_tbl.NEXT(i)).op_seq_num) then
701 l_res_start_date := trunc(l_curr_date) + (l_curr_time)/86400;
702 else
703 l_res_start_date := trunc(l_curr_date) + (l_curr_time - l_res_lead_time)/86400;
704 end if;
705
706 --end for 7264665
707
708
709 if (i > op_res_info_tbl.LAST ) and (op_res_info_tbl(i).res_sch_num = op_res_info_tbl(op_res_info_tbl.NEXT(i)).res_sch_num)
710 and (op_res_info_tbl(i).op_seq_num = op_res_info_tbl(op_res_info_tbl.NEXT(i)).op_seq_num) then
711
712 if l_res_start_date < l_next_res_completion_date then
713 l_next_res_completion_date := l_res_start_date;
714 end if;
715
716 else
717 l_next_res_completion_date := l_res_start_date;
718 end if;
719
720 l_res_lead_time := 0;
721 t_end_date := trunc(l_curr_date) + (l_curr_time/86400);
722
723 l_eam_res_usage_rec.operation_seq_num:= p_op_seq_num;
724 l_eam_res_usage_rec.resource_seq_num := op_res_info_tbl(i).res_seq_num;
725 l_eam_res_usage_rec.start_date := l_res_start_date;
726 l_eam_res_usage_rec.completion_date := t_end_date;
727 l_eam_res_usage_rec.assigned_units := op_res_info_tbl(i).assigned_units;
728
729 if p_res_usage_tbl.count > 0 then
730 p_res_usage_tbl(p_res_usage_tbl.count+1):=l_eam_res_usage_rec;
731 else
732 p_res_usage_tbl(1):=l_eam_res_usage_rec;
733 end if;
734
735 ELSE
736 l_res_lead_time := l_res_lead_time - l_sft_avail_time;
737 l_curr_time := l_curr_time - l_sft_avail_time;
738 IF l_curr_time <= 0 THEN
739 l_curr_time := 86400 + l_curr_time;
740 l_curr_date := trunc(l_curr_date) - 1;
741 END IF;
742 t_end_date := trunc(l_curr_date) + ((l_curr_time+l_sft_avail_time) /86400);
743 t_start_date := t_end_date - (l_sft_avail_time/86400);
744
745 l_eam_res_usage_rec.operation_seq_num:= p_op_seq_num;
746 l_eam_res_usage_rec.resource_seq_num := op_res_info_tbl(i).res_seq_num;
747 l_eam_res_usage_rec.start_date := t_start_date;
748 l_eam_res_usage_rec.completion_date := t_end_date;
749 l_eam_res_usage_rec.assigned_units := op_res_info_tbl(i).assigned_units;
750
751 if p_res_usage_tbl.count > 0 then
752 p_res_usage_tbl(p_res_usage_tbl.count+1):=l_eam_res_usage_rec;
753 else
754 p_res_usage_tbl(1):=l_eam_res_usage_rec;
755 end if;
756
757 END IF;/* avail time */
758
759 l_curr_index := l_res_sft_tbl.FIRST ;
760
761 ELSE
762 IF ( l_out_index IS NOT NULL AND l_res_sft_tbl.NEXT(l_out_index) IS NULL ) THEN
763 IF l_to_time <= l_from_time THEN
764 l_curr_date := trunc(l_curr_date) - 1;
765 l_curr_time := 86400;
766 ELSE
767 l_curr_time := l_from_time - 1;
768 END IF;
769 l_curr_index := l_res_sft_tbl.FIRST ;
770 END IF;
771 END IF; /*wkdays flag */
772 END LOOP;
773 END IF; /* Schedule direction */
774 -- NULL;
775 END IF; /* end for shift and 24hrs flag based scheduling */
776
777
778 ELSE
779
780 -- for non-scheduled resources
781 IF p_schedule_dir = 1 THEN /* forward schedule */
782 l_res_start_date := l_next_res_start_date;
783 l_res_completion_date := l_res_start_date;
784 l_next_res_start_date := l_res_completion_date;
785 ELSE
786 l_res_completion_date := l_next_res_completion_date;
787 l_res_start_date := l_res_completion_date;
788 l_next_res_completion_date := l_res_start_date;
789 END IF;
790
791 END IF; /* End for scheduled_flag */
792
793
794
795 begin
796 -- bug no 3444091
797 if l_res_start_date > l_res_completion_date then
798 x_return_status := fnd_api.g_ret_sts_error;
799 fnd_message.set_name('EAM','EAM_WO_RES_DT_ERR');
800 fnd_message.set_token('RESNO', op_res_info_tbl(i).res_seq_num);
801 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Negative resource duration') ; END IF ;
802 return;
803 end if;
804
805 UPDATE WIP_OPERATION_RESOURCES
806 SET START_DATE = l_res_start_date,
807 COMPLETION_DATE = l_res_completion_date
808 WHERE WIP_ENTITY_ID = p_wip_entity_id
809 AND OPERATION_SEQ_NUM = p_op_seq_num
810 AND RESOURCE_SEQ_NUM = op_res_info_tbl(i).res_seq_num;
811
812 UPDATE WIP_OP_RESOURCE_INSTANCES
813 set start_date = l_res_start_date
814 , completion_date = l_res_completion_date
815 WHERE WIP_ENTITY_ID = p_wip_entity_id
816 AND OPERATION_SEQ_NUM = p_op_seq_num
817 AND RESOURCE_SEQ_NUM = op_res_info_tbl(i).res_seq_num;
818 exception
819 when others then
820 null;
821 end;
822
823 if (p_schedule_dir = 1) AND ( (l_rsc_index = 1) OR (l_min_rsc_start_date > l_res_start_date ) ) THEN
824 l_min_rsc_start_date := l_res_start_date; --find the minimum resource start date
825 END IF;
826
827 if (p_schedule_dir = 2) AND ( (l_rsc_index = 1) OR (l_max_rsc_end_date < l_res_completion_date ) ) THEN
828 l_max_rsc_end_date := l_res_completion_date; -- find the max resource end date
829 END IF;
830 l_rsc_index := l_rsc_index + 1;
831
832
833 --start of fix for 3574991
834 IF p_schedule_dir = 1 THEN
835 if (l_res_completion_date > p_op_completion_date) then
836 p_op_completion_date := l_res_completion_date;
837 end if;
838
839 ELSE
840 if (l_res_start_date < p_op_start_date) then
841 p_op_start_date := l_res_start_date;
842 end if;
843
844 END IF;
845
846 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('entering if'||p_op_seq_num||'...'||op_res_info_tbl(i).res_seq_num) ; END IF ;
847 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('l_res_start_date= '|| l_res_start_date) ; END IF ;
848 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('l_res_completion_date= '|| l_res_completion_date) ; END IF ;
849 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('l_next_res_start_date= '|| l_next_res_start_date) ; END IF ;
850 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('l_next_res_completion_date= '|| l_next_res_completion_date) ; END IF ;
851 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Op Start Date'|| p_op_start_date) ; END IF ;
852 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Op Completion Date'|| p_op_completion_date) ; END IF ;
853
854
855 END IF; /* End of identification of op_seq_num */
856
857
858 IF p_schedule_dir = 1 THEN
859 i := op_res_info_tbl.NEXT(i);
860 ELSE
861 i := op_res_info_tbl.PRIOR(i);
862 END IF;
863 --end of fix for 3574991
864
865 END LOOP;
866
867 IF p_schedule_dir = 1 THEN --copy the min resource start date to operation's start date
868 p_op_start_date := l_min_rsc_start_date;
869 ELSE --copy max resource end date to operation's end date
870 p_op_completion_date := l_max_rsc_end_date;
871 END IF; --end of check for scheduling direction
872
873
874 EXCEPTION
875 WHEN NO_SFT_EXC THEN
876 x_return_status := fnd_api.g_ret_sts_error;
877 x_error_message := 'NO SHIFT ASSOCIATED TO A RESOURCE';
878 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('No shift found for the resource') ; END IF ;
879
880 END; /*SCHEDULE_RESOURCES */
881
882
883
884
885
886
887
888
889 PROCEDURE SCHEDULE_OPERATIONS
890 ( p_organization_id IN NUMBER,
891 p_wip_entity_id IN NUMBER,
892 p_start_date IN OUT NOCOPY DATE,
893 p_completion_date IN OUT NOCOPY DATE,
894 p_hour_conv IN NUMBER,
895 p_calendar_code IN VARCHAR2,
896 p_excetion_set_id IN NUMBER,
897 p_validation_level IN NUMBER,
898 p_res_usage_tbl IN OUT NOCOPY EAM_PROCESS_WO_PUB.eam_res_usage_tbl_type,
899 p_commit IN VARCHAR2,
900 x_error_message OUT NOCOPY VARCHAR2,
901 x_return_status OUT NOCOPY VARCHAR2) IS
902
903 l_calender_code VARCHAR2(10);
904 l_exception_set_id NUMBER;
905 l_res_rec_count NUMBER;
906 l_op_scd_seq_count NUMBER;
907 l_schedule_dir NUMBER;
908 l_op_index NUMBER;
909 l_res_index NUMBER;
910 l_op_seq_num NUMBER;
911 l_res_count NUMBER;
912 l_op_start_date DATE;
913 l_op_completion_date DATE;
914 l_min_start_date DATE;
915 l_max_completion_date DATE;
916 l_res_sft_rec_count NUMBER;
917 l_stmt_num NUMBER := 100;
918 l_op_last_level NUMBER;
919 l_sched_start_wo_dt DATE;
920 l_sched_completion_wo_dt DATE;
921
922
923
924 /* Cursor definition to store resource information*/
925
926 CURSOR EAM_RSC_CSR IS
927 SELECT WO.OPERATION_SEQ_NUM OP_SEQ_NUM,
928 WO.OPERATION_SEQUENCE_ID OP_SEQ_ID,
929 WO.FIRST_UNIT_START_DATE OP_START_DATE,
930 WO.LAST_UNIT_COMPLETION_DATE OP_COMPLETION_DATE,
931 WO.OPERATION_COMPLETED OP_COMPLETED,
932 WOR.RESOURCE_SEQ_NUM RES_SEQ_NUM,
933 NVL(WOR.SCHEDULE_SEQ_NUM, WOR.RESOURCE_SEQ_NUM) RES_SCH_NUM,
934 WOR.RESOURCE_ID RES_ID,
935 WOR.START_DATE RES_START_DATE,
936 WOR.COMPLETION_DATE RES_COMPLETION_DATE,
937 NVL(WOR.ASSIGNED_UNITS, 0) ASSIGNED_UNITS,
938 DR2.CAPACITY_UNITS CAPACITY_UNITS,
939 ROUND(WOR.USAGE_RATE_OR_AMOUNT * (1/p_hour_conv )* DECODE (CON.CONVERSION_RATE, '', 0, '0', 0, CON.CONVERSION_RATE) *
940 DECODE (WOR.BASIS_TYPE, 1, 1, 2, 1, 1) * 3600) USAGE_RATE,
941 DECODE(WOR.SCHEDULED_FLAG, 1, DECODE(WOR.USAGE_RATE_OR_AMOUNT, 0, 2, 1),
942 WOR.SCHEDULED_FLAG) SCHEDULED_FLAG,
943 DR2.AVAILABLE_24_HOURS_FLAG AVAIL_24_HRS_FLAG
944 FROM WIP_OPERATIONS WO,
945 BOM_DEPARTMENT_RESOURCES DR1,
946 BOM_DEPARTMENT_RESOURCES DR2,
947 WIP_OPERATION_RESOURCES WOR,
948 BOM_RESOURCES RES,
949 MTL_UOM_CONVERSIONS CON
950 WHERE
951 WO.WIP_ENTITY_ID = p_wip_entity_id
952 AND WO.ORGANIZATION_ID = p_organization_id
953 AND WO.WIP_ENTITY_ID = WOR.WIP_ENTITY_ID
954 AND WO.OPERATION_SEQ_NUM = WOR.OPERATION_SEQ_NUM
955 AND WO.DEPARTMENT_ID = DR1.DEPARTMENT_ID
956 AND WOR.RESOURCE_ID = DR1.RESOURCE_ID
957 AND NVL(DR1.SHARE_FROM_DEPT_ID, DR1.DEPARTMENT_ID) = DR2.DEPARTMENT_ID
958 AND WOR.RESOURCE_ID = DR2.RESOURCE_ID
959 AND WOR.RESOURCE_ID = RES.RESOURCE_ID
960 AND CON.UOM_CODE (+) = RES.UNIT_OF_MEASURE
961 AND CON.INVENTORY_ITEM_ID (+) = 0
962 ORDER BY WO.OPERATION_SEQ_NUM,
963 -- WOR.RESOURCE_SEQ_NUM;
964 WOR.SCHEDULE_SEQ_NUM;
965 /* Cursor to identify the operation scheduling sequence considering the dependency */
966
967 CURSOR OP_FWD_SCD_SEQ_CSR IS
968 SELECT -1 "LEVEL", operation_seq_num "OP_SEQ_NUM"
969 FROM wip_operations
970 WHERE wip_entity_id = p_wip_entity_id
971 AND operation_seq_num not in( SELECT prior_operation
972 FROM WIP_OPERATION_NETWORKS
973 WHERE wip_entity_id = p_wip_entity_id
974 UNION
975 SELECT next_operation
976 FROM WIP_OPERATION_NETWORKS
977 WHERE wip_entity_id = p_wip_entity_id)
978 UNION
979
980 SELECT 0 "LEVEL", prior_operation "OP_SEQ_NUM"
981 FROM wip_operation_networks
982 WHERE prior_operation NOT IN
983 (SELECT next_operation
984 FROM wip_operation_networks
985 WHERE wip_entity_id = p_wip_entity_id )
986 AND wip_entity_id=p_wip_entity_id
987 UNION
988 SELECT max(level) "LEVEL", next_operation "OP_SEQ_NUM"
989 FROM (SELECT * FROM wip_operation_networks
990 WHERE wip_entity_id=p_wip_entity_id)
991 START WITH prior_operation IN
992 (SELECT prior_operation
993 FROM wip_operation_networks
994 WHERE prior_operation NOT IN
995 (SELECT next_operation
996 FROM wip_operation_networks
997 WHERE wip_entity_id=p_wip_entity_id )
998 AND wip_entity_id=p_wip_entity_id)
999 CONNECT BY PRIOR next_operation = prior_operation
1000 AND wip_entity_id = p_wip_entity_id
1001 GROUP BY next_operation
1002 ORDER BY 1;
1003
1004
1005 CURSOR OP_BWD_SCD_SEQ_CSR IS
1006 SELECT -1 "LEVEL", operation_seq_num "OP_SEQ_NUM"
1007 FROM wip_operations
1008 WHERE wip_entity_id = p_wip_entity_id
1009 AND operation_seq_num not in( SELECT prior_operation
1010 FROM WIP_OPERATION_NETWORKS
1011 WHERE wip_entity_id = p_wip_entity_id
1012 UNION
1013 SELECT next_operation
1014 FROM WIP_OPERATION_NETWORKS
1015 WHERE wip_entity_id = p_wip_entity_id)
1016 UNION
1017 SELECT 0 "LEVEL", next_operation "OP_SEQ_NUM"
1018 FROM wip_operation_networks
1019 WHERE next_operation NOT IN
1020 (SELECT prior_operation
1021 FROM wip_operation_networks
1022 WHERE wip_entity_id = p_wip_entity_id )
1023 AND wip_entity_id=p_wip_entity_id
1024 UNION
1025 SELECT max(level) "LEVEL", prior_operation "OP_SEQ_NUM"
1026 FROM (SELECT * FROM wip_operation_networks
1027 WHERE wip_entity_id=p_wip_entity_id)
1028 START WITH next_operation IN
1029 (SELECT next_operation
1030 FROM wip_operation_networks
1031 WHERE next_operation NOT IN
1032 (SELECT prior_operation
1033 FROM wip_operation_networks
1034 WHERE wip_entity_id=p_wip_entity_id )
1035 AND wip_entity_id=p_wip_entity_id)
1036 CONNECT BY PRIOR prior_operation = next_operation
1037 AND wip_entity_id = p_wip_entity_id
1038 GROUP BY prior_operation
1039 ORDER BY 1;
1040
1041 /* Bug 5144273. Split cursor OP_RES_SFT_CSR into two cursors OP_RES_SFT_CSR_FWD and OP_RES_SFT_CSR_BWD .
1042 OP_RES_SFT_CSR_FWD is used for Forward scheduling and OP_RES_SFT_CSR_BWD for backward scheduling
1043 */
1044 CURSOR OP_RES_SFT_CSR_FWD IS
1045 SELECT WO.OPERATION_SEQ_NUM OP_SEQ_NUM,
1046 WOR.RESOURCE_SEQ_NUM RES_SEQ_NUM,
1047 SHF.SHIFT_NUM SHIFT_NUM,
1048 SHF.FROM_TIME FROM_TIME,
1049 SHF.TO_TIME TO_TIME
1050 FROM BOM_SHIFT_TIMES SHF,
1051 BOM_RESOURCE_SHIFTS RSH,
1052 BOM_DEPARTMENT_RESOURCES BDR,
1053 WIP_OPERATION_RESOURCES WOR,
1054 WIP_OPERATIONS WO
1055 WHERE WO.WIP_ENTITY_ID = p_wip_entity_id
1056 AND WO.ORGANIZATION_ID = p_organization_id
1057 AND WO.WIP_ENTITY_ID = WOR.WIP_ENTITY_ID
1058 AND WO.OPERATION_SEQ_NUM = WOR.OPERATION_SEQ_NUM
1059 AND WOR.SCHEDULED_FLAG IS NOT NULL
1060 AND WO.DEPARTMENT_ID = BDR.DEPARTMENT_ID
1061 AND WOR.RESOURCE_ID = BDR.RESOURCE_ID
1062 AND NVL(BDR.SHARE_FROM_DEPT_ID, BDR.DEPARTMENT_ID) = RSH.DEPARTMENT_ID
1063 AND RSH.RESOURCE_ID = WOR.RESOURCE_ID
1064 AND RSH.SHIFT_NUM = SHF.SHIFT_NUM
1065 AND SHF.CALENDAR_CODE = p_calendar_code
1066 ORDER BY FROM_TIME, TO_TIME ;
1067
1068
1069 CURSOR OP_RES_SFT_CSR_BWD IS
1070 SELECT WO.OPERATION_SEQ_NUM OP_SEQ_NUM,
1071 WOR.RESOURCE_SEQ_NUM RES_SEQ_NUM,
1072 SHF.SHIFT_NUM SHIFT_NUM,
1073 SHF.FROM_TIME FROM_TIME,
1074 SHF.TO_TIME TO_TIME
1075 FROM BOM_SHIFT_TIMES SHF,
1076 BOM_RESOURCE_SHIFTS RSH,
1077 BOM_DEPARTMENT_RESOURCES BDR,
1078 WIP_OPERATION_RESOURCES WOR,
1079 WIP_OPERATIONS WO
1080 WHERE WO.WIP_ENTITY_ID = p_wip_entity_id
1081 AND WO.ORGANIZATION_ID = p_organization_id
1082 AND WO.WIP_ENTITY_ID = WOR.WIP_ENTITY_ID
1083 AND WO.OPERATION_SEQ_NUM = WOR.OPERATION_SEQ_NUM
1084 AND WOR.SCHEDULED_FLAG IS NOT NULL
1085 AND WO.DEPARTMENT_ID = BDR.DEPARTMENT_ID
1086 AND WOR.RESOURCE_ID = BDR.RESOURCE_ID
1087 AND NVL(BDR.SHARE_FROM_DEPT_ID, BDR.DEPARTMENT_ID) = RSH.DEPARTMENT_ID
1088 AND RSH.RESOURCE_ID = WOR.RESOURCE_ID
1089 AND RSH.SHIFT_NUM = SHF.SHIFT_NUM
1090 AND SHF.CALENDAR_CODE = p_calendar_code
1091 ORDER BY TO_TIME DESC, FROM_TIME DESC;
1092
1093
1094 BEGIN /*SCHEDULE_OPERATIONS*/
1095
1096 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Starting SCHEDULE_OPERATIONS') ; END IF ;
1097 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Scheduling '|| p_wip_entity_id) ; END IF ;
1098
1099 l_res_rec_count:=0;
1100 op_res_info_tbl.delete;
1101
1102 FOR l_op_res_info_rec IN EAM_RSC_CSR LOOP
1103 --fix for bug 3355437.commented the following code so that pl/sql tables will be initialized always
1104 /* l_res_rec_count := op_res_info_tbl.last;
1105
1106 IF l_res_rec_count is NULL THEN
1107 l_res_rec_count:=0;
1108 END IF;*/
1109
1110 l_res_rec_count := l_res_rec_count + 1;
1111
1112 op_res_info_tbl(l_res_rec_count).op_seq_num := l_op_res_info_rec.OP_SEQ_NUM;
1113 op_res_info_tbl(l_res_rec_count).op_seq_id := l_op_res_info_rec.OP_SEQ_id;
1114 op_res_info_tbl(l_res_rec_count).op_start_date := l_op_res_info_rec.op_start_date;
1115 op_res_info_tbl(l_res_rec_count).op_completion_date := l_op_res_info_rec.op_completion_date;
1116 op_res_info_tbl(l_res_rec_count).op_completed := l_op_res_info_rec.op_completed;
1117 op_res_info_tbl(l_res_rec_count).res_seq_num := l_op_res_info_rec.res_seq_num;
1118 op_res_info_tbl(l_res_rec_count).res_sch_num := l_op_res_info_rec.res_sch_num;
1119 op_res_info_tbl(l_res_rec_count).res_id := l_op_res_info_rec.res_id;
1120 op_res_info_tbl(l_res_rec_count).res_start_date := l_op_res_info_rec.res_start_date;
1121 op_res_info_tbl(l_res_rec_count).res_completion_date := l_op_res_info_rec.res_completion_date;
1122 op_res_info_tbl(l_res_rec_count).assigned_units := l_op_res_info_rec.ASSIGNED_UNITS;
1123 op_res_info_tbl(l_res_rec_count).capacity_units := l_op_res_info_rec.CAPACITY_UNITS;
1124 op_res_info_tbl(l_res_rec_count).usage_rate := l_op_res_info_rec.USAGE_RATE;
1125 op_res_info_tbl(l_res_rec_count).scheduled_flag := l_op_res_info_rec.SCHEDULED_FLAG;
1126 op_res_info_tbl(l_res_rec_count).avail_24_hrs_flag := l_op_res_info_rec.AVAIL_24_HRS_FLAG;
1127
1128 END LOOP;
1129
1130 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Resource Count for Scheduling '||op_res_info_tbl.count) ; END IF ;
1131
1132 l_res_sft_rec_count := 0;
1133 op_res_sft_tbl.delete;
1134
1135 IF p_start_date IS NOT NULL THEN -- fwd scheduling .added for bug 5144273
1136
1137 FOR l_op_rsc_sft_rec IN OP_RES_SFT_CSR_FWD LOOP
1138
1139 l_res_sft_rec_count := l_res_sft_rec_count + 1;
1140
1141 op_res_sft_tbl(l_res_sft_rec_count).op_seq_num := l_op_rsc_sft_rec.op_seq_num;
1142 op_res_sft_tbl(l_res_sft_rec_count).res_seq_num := l_op_rsc_sft_rec.res_seq_num;
1143 op_res_sft_tbl(l_res_sft_rec_count).shift_num := l_op_rsc_sft_rec.shift_num;
1144 op_res_sft_tbl(l_res_sft_rec_count).from_time := l_op_rsc_sft_rec.from_time;
1145 op_res_sft_tbl(l_res_sft_rec_count).to_time := l_op_rsc_sft_rec.to_time;
1146 END LOOP;
1147 ELSE -- bwd scheduling . added for bug 5144273
1148 FOR l_op_rsc_sft_rec IN OP_RES_SFT_CSR_BWD LOOP
1149
1150 l_res_sft_rec_count := l_res_sft_rec_count + 1;
1151
1152 op_res_sft_tbl(l_res_sft_rec_count).op_seq_num := l_op_rsc_sft_rec.op_seq_num;
1153 op_res_sft_tbl(l_res_sft_rec_count).res_seq_num := l_op_rsc_sft_rec.res_seq_num;
1154 op_res_sft_tbl(l_res_sft_rec_count).shift_num := l_op_rsc_sft_rec.shift_num;
1155 op_res_sft_tbl(l_res_sft_rec_count).from_time := l_op_rsc_sft_rec.from_time;
1156 op_res_sft_tbl(l_res_sft_rec_count).to_time := l_op_rsc_sft_rec.to_time;
1157 END LOOP;
1158 END IF ;
1159
1160
1161 IF p_start_date IS NOT NULL THEN
1162
1163 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Forward Scheduling') ; END IF ;
1164
1165 l_schedule_dir := 1;
1166
1167 l_op_scd_seq_count:=0;
1168 op_scd_seq_tbl.delete;
1169
1170 FOR l_op_scd_seq_rec IN OP_FWD_SCD_SEQ_CSR LOOP
1171 --fix for bug 3355437.commented the following code so that pl/sql tables will be initialized always
1172 /*l_op_scd_seq_count := op_scd_seq_tbl.last;
1173
1174 IF l_op_scd_seq_count is NULL THEN
1175 l_op_scd_seq_count:=0;
1176 END IF;*/
1177
1178 l_op_scd_seq_count := l_op_scd_seq_count + 1;
1179
1180 op_scd_seq_tbl(l_op_scd_seq_count).level := l_op_scd_seq_rec.LEVEL;
1181 op_scd_seq_tbl(l_op_scd_seq_count).op_seq_num := l_op_scd_seq_rec.OP_SEQ_NUM;
1182 op_scd_seq_tbl(l_op_scd_seq_count).op_start_date := NULL;
1183 op_scd_seq_tbl(l_op_scd_seq_count).op_completion_date := NULL;
1184
1185 END LOOP;
1186
1187 l_op_index := op_scd_seq_tbl.FIRST;
1188
1189 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Operation Count for Scheduling '||op_scd_seq_tbl.count) ; END IF ;
1190
1191 WHILE l_op_index is not NULL LOOP
1192
1193 l_op_seq_num := op_scd_seq_tbl(l_op_index).op_seq_num;
1194 l_res_count := 0;
1195
1196 IF op_scd_seq_tbl(l_op_index).level = 0 OR op_scd_seq_tbl(l_op_index).level = -1 THEN
1197
1198 l_op_start_date := p_start_date;
1199 ELSE
1200
1201 dep_op_seq_num_tbl.delete;
1202
1203 SELECT prior_operation
1204 BULK COLLECT INTO dep_op_seq_num_tbl
1205 FROM wip_operation_networks
1206 WHERE wip_entity_id = p_wip_entity_id
1207 AND next_operation = l_op_seq_num;
1208
1209
1210 DECLARE
1211 l_dep_index NUMBER;
1212 l_op_tab_index NUMBER;
1213 BEGIN
1214
1215 l_op_start_date := p_start_date;
1216 l_dep_index := dep_op_seq_num_tbl.FIRST;
1217
1218 WHILE l_dep_index is not NULL LOOP
1219
1220 l_op_tab_index := op_scd_seq_tbl.FIRST;
1221
1222 WHILE l_op_tab_index is not NULL LOOP
1223
1224 IF dep_op_seq_num_tbl(l_dep_index) = op_scd_seq_tbl(l_op_tab_index).op_seq_num THEN
1225
1226 IF l_op_start_date <= op_scd_seq_tbl(l_op_tab_index).op_completion_date THEN
1227
1228 l_op_start_date := op_scd_seq_tbl(l_op_tab_index).op_completion_date;
1229
1230 ELSE
1231 NULL;
1232 END IF;
1233
1234 EXIT;
1235
1236 END IF;
1237
1238 l_op_tab_index := op_scd_seq_tbl.NEXT(l_op_tab_index);
1239
1240 END LOOP; /* idenfication of completion time of prior op*/
1241
1242 l_dep_index := dep_op_seq_num_tbl.NEXT(l_dep_index);
1243
1244 END LOOP; /* Comparing all prior op completion time */
1245
1246 END;
1247
1248
1249 END IF;/* Identification of start time for intermediate operation ends*/
1250
1251 l_res_index := op_res_info_tbl.FIRST;
1252
1253 WHILE l_res_index is not NULL LOOP
1254
1255 IF op_scd_seq_tbl(l_op_index).op_seq_num = op_res_info_tbl(l_res_index).op_seq_num THEN
1256
1257 l_res_count := l_res_count + 1;
1258
1259 END IF;
1260
1261 l_res_index := op_res_info_tbl.NEXT(l_res_index);
1262
1263 END LOOP;
1264
1265 IF l_res_count = 0 THEN
1266 l_op_completion_date := l_op_start_date;
1267 ELSE
1268
1269 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Calling EAM_CALC_OPERATION_TIME') ; END IF ;
1270 --fix for 3574991
1271 l_op_completion_date :=l_op_start_date;
1272
1273 SCHEDULE_RESOURCES
1274 (p_organization_id,
1275 p_wip_entity_id,
1276 l_op_seq_num,
1277 l_schedule_dir,
1278 p_calendar_code,
1279 op_res_info_tbl,
1280 op_res_sft_tbl,
1281 l_op_start_date,
1282 l_op_completion_date,
1283 p_res_usage_tbl,
1284 p_validation_level,
1285 p_commit,
1286 x_error_message,
1287 x_return_status );
1288
1289 IF(x_return_status <> 'S') THEN
1290 RETURN;
1291 END IF;
1292
1293 END IF;
1294
1295 op_scd_seq_tbl(l_op_index).op_start_date := l_op_start_date;
1296 op_scd_seq_tbl(l_op_index).op_completion_date := l_op_completion_date;
1297
1298 l_op_index := op_scd_seq_tbl.NEXT(l_op_index);
1299
1300 END LOOP;
1301
1302 ELSE
1303
1304 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Backward Scheduling') ; END IF ;
1305
1306
1307 l_schedule_dir := 2;
1308
1309 l_op_scd_seq_count:=0;
1310 op_scd_seq_tbl.delete;
1311
1312 FOR l_op_scd_seq_rec IN OP_BWD_SCD_SEQ_CSR LOOP
1313 --fix for bug 3355437.commented the following code so that pl/sql tables will be initialized always
1314 /* l_op_scd_seq_count := op_scd_seq_tbl.last;
1315
1316 IF l_op_scd_seq_count is NULL THEN
1317
1318 l_op_scd_seq_count:=0;
1319
1320 END IF;*/
1321
1322 l_op_scd_seq_count := l_op_scd_seq_count + 1;
1323
1324 op_scd_seq_tbl(l_op_scd_seq_count).level := l_op_scd_seq_rec.LEVEL;
1325 op_scd_seq_tbl(l_op_scd_seq_count).op_seq_num := l_op_scd_seq_rec.OP_SEQ_NUM;
1326 op_scd_seq_tbl(l_op_scd_seq_count).op_start_date := NULL;
1327 op_scd_seq_tbl(l_op_scd_seq_count).op_completion_date := NULL;
1328
1329 END LOOP;
1330
1331 l_op_index := op_scd_seq_tbl.FIRST;
1332
1333 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Operation Count for Scheduling'||op_scd_seq_tbl.count) ; END IF ;
1334
1335 WHILE l_op_index is not NULL LOOP
1336
1337 l_op_seq_num := op_scd_seq_tbl(l_op_index).op_seq_num;
1338 l_res_count := 0;
1339
1340 IF op_scd_seq_tbl(l_op_index).level = 0 OR
1341 op_scd_seq_tbl(l_op_index).level = -1 THEN
1342
1343 l_op_completion_date := p_completion_date;
1344
1345 ELSE
1346
1347 dep_op_seq_num_tbl.delete;
1348
1349 SELECT next_operation
1350 BULK COLLECT INTO dep_op_seq_num_tbl
1351 FROM wip_operation_networks
1352 WHERE wip_entity_id = p_wip_entity_id
1353 AND prior_operation = l_op_seq_num;
1354
1355 DECLARE
1356
1357 l_dep_index NUMBER;
1358 l_op_tab_index NUMBER;
1359
1360 BEGIN
1361
1362 l_op_completion_date := p_completion_date;
1363 l_dep_index := dep_op_seq_num_tbl.FIRST;
1364
1365 WHILE l_dep_index is not NULL LOOP
1366
1367 l_op_tab_index := op_scd_seq_tbl.FIRST;
1368
1369 WHILE l_op_tab_index is not NULL LOOP
1370
1371 IF dep_op_seq_num_tbl(l_dep_index) = op_scd_seq_tbl(l_op_tab_index).op_seq_num THEN
1372
1373 IF l_op_completion_date >= op_scd_seq_tbl(l_op_tab_index).op_start_date THEN
1374
1375 l_op_completion_date := op_scd_seq_tbl(l_op_tab_index).op_start_date;
1376
1377 ELSE
1378
1379 NULL;
1380
1381 END IF;
1382
1383 EXIT;
1384
1385 END IF;
1386
1387 l_op_tab_index := op_scd_seq_tbl.NEXT(l_op_tab_index);
1388
1389 END LOOP; /* idenfication of start time of next op*/
1390
1391 l_dep_index := dep_op_seq_num_tbl.NEXT(l_dep_index);
1392
1393 END LOOP; /* Comparing all NEXT op start time */
1394
1395 END;
1396
1397 END IF;/* Identification of start time for intermediate operation ends*/
1398
1399 l_res_index := op_res_info_tbl.FIRST;
1400
1401 WHILE l_res_index is not NULL LOOP
1402
1403 IF op_scd_seq_tbl(l_op_index).op_seq_num = op_res_info_tbl(l_res_index).op_seq_num THEN
1404
1405 l_res_count := l_res_count + 1;
1406
1407 END IF;
1408
1409 l_res_index := op_res_info_tbl.NEXT(l_res_index);
1410
1411 END LOOP;
1412
1413 IF l_res_count = 0 THEN
1414 l_op_start_date := l_op_completion_date;
1415 ELSE
1416
1417 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Calling EAM_CALC_OPERATION_TIME') ; END IF ;
1418 --fix for 3574991
1419 l_op_start_date := l_op_completion_date;
1420
1421 SCHEDULE_RESOURCES
1422 (p_organization_id,
1423 p_wip_entity_id,
1424 l_op_seq_num,
1425 l_schedule_dir,
1426 p_calendar_code,
1427 op_res_info_tbl,
1428 op_res_sft_tbl,
1429 l_op_start_date,
1430 l_op_completion_date,
1431 p_res_usage_tbl,
1432 p_validation_level,
1433 p_commit,
1434 x_error_message,
1435 x_return_status );
1436
1437 IF(x_return_status <> 'S') THEN
1438 RETURN;
1439 END IF;
1440
1441 END IF;
1442
1443 -- dbms_output.put_line ('Start Date *' || l_op_start_date);
1444 -- dbms_output.put_line ('Completion Date *' || l_op_completion_date);
1445
1446
1447 op_scd_seq_tbl(l_op_index).op_start_date := l_op_start_date;
1448 op_scd_seq_tbl(l_op_index).op_completion_date := l_op_completion_date;
1449
1450 l_op_index := op_scd_seq_tbl.NEXT(l_op_index);
1451
1452 END LOOP;
1453
1454 END IF; /* Decision for Forward or backward scheduling */
1455
1456
1457 l_op_index := op_scd_seq_tbl.FIRST;
1458
1459 l_max_completion_date := NULL;
1460 l_min_start_date := NULL;
1461
1462
1463 WHILE l_op_index is not NULL LOOP
1464
1465 l_op_start_date := op_scd_seq_tbl(l_op_index).op_start_date;
1466 l_op_completion_date := op_scd_seq_tbl(l_op_index).op_completion_date;
1467 l_op_seq_num := op_scd_seq_tbl(l_op_index).op_seq_num;
1468
1469 -- bug no 3444091
1470 if l_op_start_date > l_op_completion_date then
1471 x_return_status := fnd_api.g_ret_sts_error;
1472 fnd_message.set_name('EAM','EAM_WO_OP_DT_TK_ERR');
1473 fnd_message.set_token('OPNO', l_op_seq_num);
1474 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Negative operation duration') ; END IF ;
1475 return;
1476 end if;
1477
1478
1479 UPDATE WIP_OPERATIONS
1480 SET FIRST_UNIT_START_DATE = l_op_start_date,
1481 FIRST_UNIT_COMPLETION_DATE = l_op_completion_date,
1482 LAST_UNIT_START_DATE = l_op_start_date,
1483 LAST_UNIT_COMPLETION_DATE = l_op_completion_date
1484 WHERE WIP_ENTITY_ID = p_wip_entity_id
1485 AND OPERATION_SEQ_NUM = l_op_seq_num;
1486
1487 --irrespective of the scheduling direciton.find the minimum and maximum of operation dates
1488 IF((l_max_completion_date IS NULL) OR (l_max_completion_date < l_op_completion_date)) THEN
1489 l_max_completion_date := l_op_completion_date;
1490 END IF;
1491
1492 IF((l_min_start_date IS NULL) OR (l_min_start_date > l_op_start_date)) THEN
1493 l_min_start_date := l_op_start_date;
1494 END IF;
1495
1496
1497 l_op_index := op_scd_seq_tbl.NEXT(l_op_index);
1498
1499 END LOOP;
1500
1501 --workorder dates will be the minimum start date and maximum end date of operations.
1502 p_start_date := NVL(l_min_start_date,NVL(p_start_date,p_completion_date)); --will be Null when there are no operations
1503 p_completion_date := NVL(l_max_completion_date, p_start_date);
1504
1505 if p_start_date > p_completion_date then
1506 x_return_status := fnd_api.g_ret_sts_error;
1507 fnd_message.set_name('EAM','EAM_WO_DT_TK_ERR');
1508 fnd_message.set_token('WONO', p_wip_entity_id);
1509 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Negative workorder duration') ; END IF ;
1510 return;
1511 end if;
1512
1513 update wip_discrete_jobs
1514 set scheduled_start_date = p_start_date,
1515 scheduled_completion_date = p_completion_date
1516 where wip_entity_id = p_wip_entity_id
1517 and organization_id = p_organization_id;
1518
1519
1520 END;/* SCHEDULE_OPERATIONS */
1521
1522
1523
1524
1525
1526 /* Procedure EAM_RTG_GET_INFO for calling procedures to load activity
1527 routing data to wip tables AND to schedule the operations */
1528
1529 PROCEDURE SCHEDULE_WO
1530 ( p_organization_id IN NUMBER,
1531 p_wip_entity_id IN NUMBER,
1532 p_start_date IN OUT NOCOPY DATE,
1533 p_completion_date IN OUT NOCOPY DATE,
1534 p_validation_level IN NUMBER,
1535 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1536 x_error_message OUT NOCOPY VARCHAR2,
1537 x_return_status OUT NOCOPY VARCHAR2
1538 )
1539 IS
1540 l_calendar_code VARCHAR2(10);
1541 l_exception_set_id NUMBER;
1542 l_uom_conv NUMBER;
1543 l_stmt_num NUMBER := 0;
1544 l_error_msg VARCHAR2(8000);
1545 l_stmt_msg VARCHAR2(200);
1546 l_hour_uom VARCHAR2(10);
1547 l_scd_req NUMBER := 1;
1548 INVALID_PARAM_EXC EXCEPTION;
1549
1550 TYPE l_relationship_records IS REF CURSOR RETURN WIP_SCHED_RELATIONSHIPS%ROWTYPE;
1551 l_constrained_children l_relationship_records;
1552 l_relationship_record WIP_SCHED_RELATIONSHIPS%ROWTYPE;
1553
1554 l_min_date DATE := null;
1555 l_max_date DATE := null;
1556 l_date DATE := null;
1557
1558 l_wo_start_date DATE := null;
1559 l_wo_end_date DATE := null;
1560 l_start_date DATE := null;
1561 l_compl_date DATE := null;
1562 l_status_type NUMBER;
1563 l_date_completed DATE;
1564
1565 l_request_id NUMBER;
1566 l_program_application_id NUMBER;
1567 l_program_id NUMBER;
1568 l_program_update_date DATE;
1569
1570 p_res_usage_tbl EAM_PROCESS_WO_PUB.eam_res_usage_tbl_type;
1571
1572 TYPE wip_operations_tbl_type is TABLE OF number INDEX BY BINARY_INTEGER;
1573 TYPE wip_op_resource_tbl_type is TABLE OF number INDEX BY BINARY_INTEGER;
1574 TYPE wip_op_res_inst_tbl_type is TABLE OF number INDEX BY BINARY_INTEGER;
1575 TYPE wip_op_r_inst_st_tbl_type is TABLE OF DATE INDEX BY BINARY_INTEGER;
1576 TYPE wip_op_r_inst_end_tbl_type is TABLE OF DATE INDEX BY BINARY_INTEGER;
1577
1578 l_WipOperation_tbl wip_operations_tbl_type;
1579 l_WipOperResource_tbl wip_op_resource_tbl_type;
1580 l_WipOperResInst_tbl wip_op_res_inst_tbl_type;
1581 l_WipOperResInstSt_tbl wip_op_r_inst_st_tbl_type;
1582 l_WipOperResInstEnd_tbl wip_op_r_inst_end_tbl_type;
1583
1584 l_mesg_token_tbl EAM_ERROR_MESSAGE_PVT.Mesg_Token_Tbl_Type;
1585 l_out_mesg_token_tbl EAM_ERROR_MESSAGE_PVT.Mesg_Token_Tbl_Type;
1586 l_token_tbl EAM_ERROR_MESSAGE_PVT.Token_Tbl_Type ;
1587
1588 -- Added for bug 5175006
1589 l_count NUMBER ;
1590 CURSOR c_zero_res_req_hours IS
1591 SELECT operation_seq_num,
1592 resource_seq_num,
1593 start_date,
1594 completion_date,
1595 assigned_units
1596 FROM wip_operation_resources
1597 WHERE wip_entity_id = p_wip_entity_id
1598 AND organization_id = p_organization_id
1599 AND usage_rate_or_amount = 0 ;
1600
1601 l_res_rec c_zero_res_req_hours%ROWTYPE ;
1602
1603 BEGIN
1604
1605 SAVEPOINT SCHEDULE_WO;
1606
1607 x_return_status := FND_API.G_RET_STS_SUCCESS;
1608
1609 l_stmt_num := 10;
1610
1611 /* For identifying Hour UOM for resource scheduling */
1612 l_hour_uom := fnd_profile.value('BOM:HOUR_UOM_CODE');
1613
1614 IF l_hour_uom IS NULL THEN
1615 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Value of Profile BOM: Hour UOM is null') ; END IF ;
1616 x_return_status := FND_API.G_RET_STS_ERROR;
1617 l_token_tbl.DELETE;
1618 l_token_tbl(1).token_name := 'PROFILE';
1619 l_token_tbl(1).token_value := 'BOM: Hour UOM';
1620 l_out_mesg_token_tbl := l_mesg_token_tbl;
1621
1622 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
1623 ( x_Mesg_token_tbl => l_out_Mesg_Token_Tbl
1624 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1625 , p_message_name => 'EAM_NULL_PROFILE'
1626 , p_token_tbl => l_token_tbl
1627 );
1628
1629 l_mesg_token_tbl := l_out_mesg_token_tbl;
1630
1631 EAM_ERROR_MESSAGE_PVT.Translate_And_Insert_Messages
1632 ( p_mesg_token_tbl => l_mesg_token_tbl
1633 , p_error_level => EAM_ERROR_MESSAGE_PVT.G_WO_LEVEL
1634 , p_entity_index => 1
1635 );
1636
1637 RETURN;
1638 END IF;
1639
1640
1641 l_stmt_num := 60;
1642
1643 SELECT CON.CONVERSION_RATE
1644 INTO l_uom_conv
1645 FROM MTL_UOM_CONVERSIONS CON
1646 WHERE CON.UOM_CODE = l_hour_uom
1647 AND NVL(DISABLE_DATE, SYSDATE + 1) > SYSDATE
1648 AND CON.INVENTORY_ITEM_ID = 0;
1649
1650 l_stmt_num := 70;
1651
1652 SELECT CALENDAR_CODE,
1653 CALENDAR_EXCEPTION_SET_ID
1654 INTO l_calendar_code,
1655 l_exception_set_id
1656 FROM MTL_PARAMETERS
1657 WHERE ORGANIZATION_ID = p_organization_id;
1658
1659
1660 l_stmt_num := 80;
1661
1662 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Calling CALC_SCHEDULE from SCHEDULE_WO') ; END IF ;
1663
1664 DELETE FROM wip_operation_resource_usage
1665 WHERE wip_entity_id = p_wip_entity_id
1666 AND organization_id = p_organization_id;
1667
1668
1669 SCHEDULE_OPERATIONS
1670 ( p_organization_id,
1671 p_wip_entity_id,
1672 p_start_date,
1673 p_completion_date,
1674 l_uom_conv,
1675 l_calendar_code,
1676 l_exception_set_id,
1677 p_validation_level,
1678 p_res_usage_tbl,
1679 p_commit,
1680 x_error_message,
1681 x_return_status
1682 );
1683
1684 IF(x_return_status <> 'S' ) THEN
1685 ROLLBACK TO SCHEDULE_WO;
1686 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Return status from Schedule_operations is :'||x_return_status) ; END IF ;
1687 RETURN;
1688 END IF;
1689
1690 -- Added for bug 5175006
1691 l_count := p_res_usage_tbl.count ;
1692
1693 OPEN c_zero_res_req_hours ;
1694 LOOP
1695 FETCH c_zero_res_req_hours INTO l_res_rec ;
1696 EXIT WHEN c_zero_res_req_hours%NOTFOUND ;
1697
1698 l_count := l_count +1 ;
1699 p_res_usage_tbl( l_count ).operation_seq_num := l_res_rec.operation_seq_num ;
1700 p_res_usage_tbl( l_count ).resource_seq_num := l_res_rec.resource_seq_num ;
1701 p_res_usage_tbl( l_count ).start_date := l_res_rec.start_date ;
1702 p_res_usage_tbl( l_count ).completion_date := l_res_rec.completion_date ;
1703 p_res_usage_tbl( l_count ).assigned_units := l_res_rec.assigned_units ;
1704
1705 END LOOP;
1706 CLOSE c_zero_res_req_hours ;
1707 -- end of fix for bug 5175006
1708
1709 IF p_res_usage_tbl.count > 0 THEN
1710
1711 SELECT request_id,program_application_id,program_id,program_update_date
1712 INTO l_request_id,l_program_application_id,l_program_id,l_program_update_date
1713 FROM wip_discrete_jobs
1714 WHERE wip_entity_id = p_wip_entity_id
1715 AND organization_id = p_organization_id;
1716
1717 FOR cnt IN p_res_usage_tbl.FIRST..p_res_usage_tbl.LAST LOOP
1718
1719 INSERT INTO WIP_OPERATION_RESOURCE_USAGE
1720 ( wip_entity_id
1721 , operation_seq_num
1722 , resource_seq_num
1723 , organization_id
1724 , start_date
1725 , completion_date
1726 , assigned_units
1727 , last_update_date
1728 , last_updated_by
1729 , creation_date
1730 , created_by
1731 , last_update_login
1732 , request_id
1733 , program_application_id
1734 , program_id
1735 , program_update_date )
1736 VALUES
1737 ( p_wip_entity_id
1738 , p_res_usage_tbl(cnt).operation_seq_num
1739 , p_res_usage_tbl(cnt).resource_seq_num
1740 , p_organization_id
1741 , p_res_usage_tbl(cnt).start_date
1742 , p_res_usage_tbl(cnt).completion_date
1743 , p_res_usage_tbl(cnt).assigned_units
1744 , SYSDATE
1745 , FND_GLOBAL.user_id
1746 , SYSDATE
1747 , FND_GLOBAL.user_id
1748 , FND_GLOBAL.login_id
1749 , l_request_id
1750 , l_program_application_id
1751 , l_program_id
1752 , l_program_update_date );
1753 END LOOP;
1754
1755 END IF;
1756
1757 SELECT operation_seq_num ,
1758 resource_seq_num ,
1759 instance_id ,
1760 start_date ,
1761 completion_date
1762 BULK COLLECT INTO
1763 l_WipOperation_tbl,
1764 l_WipOperResource_tbl,
1765 l_WipOperResInst_tbl,
1766 l_WipOperResInstSt_tbl,
1767 l_WipOperResInstEnd_tbl
1768 FROM WIP_OP_RESOURCE_INSTANCES
1769 WHERE wip_entity_id = p_wip_entity_id
1770 AND organization_id = p_organization_id;
1771
1772 IF l_WipOperResInst_tbl.COUNT > 0 THEN
1773 FOR mm in l_WipOperResInst_tbl.FIRST..l_WipOperResInst_tbl.LAST LOOP
1774
1775 INSERT INTO WIP_OPERATION_RESOURCE_USAGE
1776 ( wip_entity_id
1777 , operation_seq_num
1778 , resource_seq_num
1779 , organization_id
1780 , start_date
1781 , completion_date
1782 , assigned_units
1783 , instance_id
1784 , last_update_date
1785 , last_updated_by
1786 , creation_date
1787 , created_by
1788 , last_update_login
1789 , request_id
1790 , program_application_id
1791 , program_id
1792 , program_update_date )
1793 SELECT
1794 wip_entity_id
1795 , operation_seq_num
1796 , resource_seq_num
1797 , organization_id
1798 , start_date
1799 , completion_date
1800 , assigned_units
1801 , l_WipOperResInst_tbl(mm)
1802 , last_update_date
1803 , last_updated_by
1804 , creation_date
1805 , created_by
1806 , last_update_login
1807 , request_id
1808 , program_application_id
1809 , program_id
1810 , program_update_date
1811 FROM WIP_OPERATION_RESOURCE_USAGE
1812 WHERE wip_entity_id = p_wip_entity_id
1813 AND organization_id = p_organization_id
1814 AND operation_seq_num = l_WipOperation_tbl(mm)
1815 AND resource_seq_num = l_WipOperResource_tbl(mm)
1816 AND instance_id IS NULL;
1817
1818 END LOOP;
1819 END IF;
1820
1821
1822 -- Find the min start date and max end date of all
1823 -- constrained children for this parent
1824
1825 -- find the list of constrained children
1826 IF NOT l_constrained_children%ISOPEN THEN
1827 OPEN l_constrained_children FOR
1828 select * from
1829 wip_sched_relationships
1830 where relationship_type = 1
1831 and parent_object_id = p_wip_entity_id
1832 and parent_object_type_id = 1;
1833 END IF;
1834
1835 LOOP FETCH l_constrained_children into
1836 l_relationship_record;
1837 IF l_relationship_record.parent_object_id is not null then
1838
1839 select scheduled_start_date,scheduled_completion_date,status_type,date_completed
1840 into l_start_date,l_compl_date,l_status_type,l_date_completed
1841 from wip_discrete_jobs
1842 where wip_entity_id = l_relationship_record.child_object_id
1843 and organization_id = p_organization_id;
1844
1845 --do not consider child workorders which are cancelled or [closed and date_completed is null](closed from cancelled status)
1846 IF NOT(
1847 l_status_type = 7
1848 OR ((l_status_type IN (12,14,15)) AND (l_date_completed IS NULL))
1849 ) THEN
1850 IF l_min_date is null OR
1851 l_min_date > l_start_date THEN
1852 l_min_date := l_start_date;
1853 END IF;
1854
1855
1856 IF l_max_date is null OR
1857 l_max_date < l_compl_date THEN
1858 l_max_date := l_compl_date;
1859 END IF;
1860 END IF;
1861
1862 END IF;
1863
1864 EXIT WHEN l_constrained_children%NOTFOUND;
1865 END LOOP;
1866
1867 CLOSE l_constrained_children;
1868
1869
1870 select scheduled_start_date, scheduled_completion_date
1871 into l_wo_start_date, l_wo_end_date
1872 from wip_discrete_jobs
1873 where wip_entity_id = p_wip_entity_id
1874 and organization_id = p_organization_id;
1875
1876
1877 if l_wo_start_date > nvl(l_min_date,l_wo_start_date+1) then
1878 update wip_discrete_jobs set
1879 scheduled_start_date = l_min_date
1880 where wip_entity_id = p_wip_entity_id
1881 and organization_id = p_organization_id;
1882 end if;
1883 if l_wo_end_date < nvl(l_max_date,l_wo_end_date -1) then
1884 update wip_discrete_jobs set
1885 scheduled_completion_date = l_max_date
1886 where wip_entity_id = p_wip_entity_id
1887 and organization_id = p_organization_id;
1888
1889 end if;
1890
1891 -- bug no 3444091
1892 select scheduled_start_date, scheduled_completion_date
1893 into l_wo_start_date, l_wo_end_date
1894 from wip_discrete_jobs
1895 where wip_entity_id = p_wip_entity_id
1896 and organization_id = p_organization_id;
1897
1898 if l_wo_start_date > l_wo_end_date then
1899 x_return_status := fnd_api.g_ret_sts_error;
1900 fnd_message.set_name('EAM','EAM_WO_DT_ERR');
1901 fnd_message.set_token('RESNO', p_wip_entity_id);
1902 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Negative workorder duration') ; END IF ;
1903 return;
1904 end if;
1905
1906 --dbms_output.put_line('Inside Schedule WO, At the end');
1907
1908 --########## Commented out
1909
1910
1911 x_return_status := FND_API.G_RET_STS_SUCCESS;
1912
1913 EXCEPTION
1914 WHEN INVALID_PARAM_EXC THEN
1915 ROLLBACK TO SCHEDULE_WO;
1916 x_return_status := fnd_api.g_ret_sts_error;
1917 x_error_message := ' EAM_RTG_GET_INFO : Statement - '||l_stmt_num||' Invalid parameter - '||l_error_msg;
1918 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Invalid parameter exception') ; END IF ;
1919 WHEN NO_DATA_FOUND THEN
1920 ROLLBACK TO SCHEDULE_WO;
1921
1922 IF ( l_stmt_num = 60 ) THEN -- change for bug # 16229398 start
1923 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('UOM conversion for base UOM being set for profile BOM: UOM for Hour is end-dated : ' || l_uom_conv) ; END IF ;
1924 x_return_status := FND_API.G_RET_STS_ERROR;
1925 l_token_tbl.DELETE;
1926 l_token_tbl(1).token_name := 'PROFILE';
1927 l_token_tbl(1).token_value := 'BOM: Hour UOM';
1928 l_out_mesg_token_tbl := l_mesg_token_tbl;
1929
1930 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
1931 ( x_Mesg_token_tbl => l_out_Mesg_Token_Tbl
1932 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1933 , p_message_name => 'EAM_BOM_BASE_UOM_END_DATED'
1934 , p_token_tbl => l_token_tbl
1935 );
1936
1937 l_mesg_token_tbl := l_out_mesg_token_tbl;
1938
1939 EAM_ERROR_MESSAGE_PVT.Translate_And_Insert_Messages
1940 ( p_mesg_token_tbl => l_mesg_token_tbl
1941 , p_error_level => EAM_ERROR_MESSAGE_PVT.G_WO_LEVEL
1942 , p_entity_index => 1
1943 );
1944
1945 RETURN;
1946
1947 ELSE -- change for bug # 16229398 end
1948 x_return_status := fnd_api.g_ret_sts_error;
1949 x_error_message := ' EAM_RTG_GET_INFO : Statement - '||l_stmt_num||'No Calendar associated in Org parameters';
1950 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('No calendar associated in Org parameters') ; END IF ;
1951 END IF;
1952 WHEN OTHERS THEN
1953 ROLLBACK TO SCHEDULE_WO;
1954 x_return_status := fnd_api.g_ret_sts_unexp_error;
1955 x_error_message := ' EAM_RTG_GET_INFO : Statement - '||l_stmt_num||' Error Message - '||SQLERRM;
1956 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Enters when others exception') ; END IF ;
1957 END;
1958
1959
1960
1961 END EAM_WO_SCHEDULE_PVT;