[Home] [Help]
PACKAGE BODY: APPS.EAM_WO_SCHEDULE_PVT
Source
1 PACKAGE BODY EAM_WO_SCHEDULE_PVT AS
2 /* $Header: EAMVSCDB.pls 120.9 2006/05/22 10:15:04 pkathoti noship $ */
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 l_res_completion_date := trunc(l_curr_date) + l_curr_time/86400 ;
676 END IF; --end of if for shift_index=1
677 l_shift_index := l_shift_index+1;
678
679 IF ( (l_to_time - l_from_time) <= 0
680 AND l_curr_time <= l_to_time) THEN
681 l_sft_avail_time := l_curr_time - 0 ;
682 ELSE
683 l_sft_avail_time := l_curr_time - l_from_time;
684 END IF;/* avail time */
685
686 IF l_sft_avail_time >= l_res_lead_time THEN
687
688 l_res_start_date := trunc(l_curr_date) + (l_curr_time - l_res_lead_time)/86400;
689
690 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)
691 and (op_res_info_tbl(i).op_seq_num = op_res_info_tbl(op_res_info_tbl.NEXT(i)).op_seq_num) then
692
693 if l_res_start_date < l_next_res_completion_date then
694 l_next_res_completion_date := l_res_start_date;
695 end if;
696
697 else
698 l_next_res_completion_date := l_res_start_date;
699 end if;
700
701 l_res_lead_time := 0;
702 t_end_date := trunc(l_curr_date) + (l_curr_time/86400);
703
704 l_eam_res_usage_rec.operation_seq_num:= p_op_seq_num;
705 l_eam_res_usage_rec.resource_seq_num := op_res_info_tbl(i).res_seq_num;
706 l_eam_res_usage_rec.start_date := l_res_start_date;
707 l_eam_res_usage_rec.completion_date := t_end_date;
708 l_eam_res_usage_rec.assigned_units := op_res_info_tbl(i).assigned_units;
709
710 if p_res_usage_tbl.count > 0 then
711 p_res_usage_tbl(p_res_usage_tbl.count+1):=l_eam_res_usage_rec;
712 else
713 p_res_usage_tbl(1):=l_eam_res_usage_rec;
714 end if;
715
716 ELSE
717 l_res_lead_time := l_res_lead_time - l_sft_avail_time;
718 l_curr_time := l_curr_time - l_sft_avail_time;
719 IF l_curr_time <= 0 THEN
720 l_curr_time := 86400 + l_curr_time;
721 l_curr_date := trunc(l_curr_date) - 1;
722 END IF;
723 t_end_date := trunc(l_curr_date) + ((l_curr_time+l_sft_avail_time) /86400);
724 t_start_date := t_end_date - (l_sft_avail_time/86400);
725
726 l_eam_res_usage_rec.operation_seq_num:= p_op_seq_num;
727 l_eam_res_usage_rec.resource_seq_num := op_res_info_tbl(i).res_seq_num;
728 l_eam_res_usage_rec.start_date := t_start_date;
729 l_eam_res_usage_rec.completion_date := t_end_date;
730 l_eam_res_usage_rec.assigned_units := op_res_info_tbl(i).assigned_units;
731
732 if p_res_usage_tbl.count > 0 then
733 p_res_usage_tbl(p_res_usage_tbl.count+1):=l_eam_res_usage_rec;
734 else
735 p_res_usage_tbl(1):=l_eam_res_usage_rec;
736 end if;
737
738 END IF;/* avail time */
739
740 l_curr_index := l_res_sft_tbl.FIRST ;
741
742 ELSE
743 IF ( l_out_index IS NOT NULL AND l_res_sft_tbl.NEXT(l_out_index) IS NULL ) THEN
744 IF l_to_time <= l_from_time THEN
745 l_curr_date := trunc(l_curr_date) - 1;
746 l_curr_time := 86400;
747 ELSE
748 l_curr_time := l_from_time - 1;
749 END IF;
750 l_curr_index := l_res_sft_tbl.FIRST ;
751 END IF;
752 END IF; /*wkdays flag */
753 END LOOP;
754 END IF; /* Schedule direction */
755 -- NULL;
756 END IF; /* end for shift and 24hrs flag based scheduling */
757
758
759 ELSE
760
761 -- for non-scheduled resources
762 IF p_schedule_dir = 1 THEN /* forward schedule */
763 l_res_start_date := l_next_res_start_date;
764 l_res_completion_date := l_res_start_date;
765 l_next_res_start_date := l_res_completion_date;
766 ELSE
767 l_res_completion_date := l_next_res_completion_date;
768 l_res_start_date := l_res_completion_date;
769 l_next_res_completion_date := l_res_start_date;
770 END IF;
771
772 END IF; /* End for scheduled_flag */
773
774
775
776 begin
777 -- bug no 3444091
778 if l_res_start_date > l_res_completion_date then
779 x_return_status := fnd_api.g_ret_sts_error;
780 fnd_message.set_name('EAM','EAM_WO_RES_DT_ERR');
781 fnd_message.set_token('RESNO', op_res_info_tbl(i).res_seq_num);
782 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Negative resource duration') ; END IF ;
783 return;
784 end if;
785
786 UPDATE WIP_OPERATION_RESOURCES
787 SET START_DATE = l_res_start_date,
788 COMPLETION_DATE = l_res_completion_date
789 WHERE WIP_ENTITY_ID = p_wip_entity_id
790 AND OPERATION_SEQ_NUM = p_op_seq_num
791 AND RESOURCE_SEQ_NUM = op_res_info_tbl(i).res_seq_num;
792
793 UPDATE WIP_OP_RESOURCE_INSTANCES
794 set start_date = l_res_start_date
795 , completion_date = l_res_completion_date
796 WHERE WIP_ENTITY_ID = p_wip_entity_id
797 AND OPERATION_SEQ_NUM = p_op_seq_num
798 AND RESOURCE_SEQ_NUM = op_res_info_tbl(i).res_seq_num;
799 exception
800 when others then
801 null;
802 end;
803
804 if (p_schedule_dir = 1) AND ( (l_rsc_index = 1) OR (l_min_rsc_start_date > l_res_start_date ) ) THEN
805 l_min_rsc_start_date := l_res_start_date; --find the minimum resource start date
806 END IF;
807
808 if (p_schedule_dir = 2) AND ( (l_rsc_index = 1) OR (l_max_rsc_end_date < l_res_completion_date ) ) THEN
809 l_max_rsc_end_date := l_res_completion_date; -- find the max resource end date
810 END IF;
811 l_rsc_index := l_rsc_index + 1;
812
813
814 --start of fix for 3574991
815 IF p_schedule_dir = 1 THEN
816 if (l_res_completion_date > p_op_completion_date) then
817 p_op_completion_date := l_res_completion_date;
818 end if;
819
820 ELSE
821 if (l_res_start_date < p_op_start_date) then
822 p_op_start_date := l_res_start_date;
823 end if;
824
825 END IF;
826
827 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 ;
828 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 ;
829 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 ;
830 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 ;
831 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 ;
832 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Op Start Date'|| p_op_start_date) ; END IF ;
833 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Op Completion Date'|| p_op_completion_date) ; END IF ;
834
835
836 END IF; /* End of identification of op_seq_num */
837
838
839 IF p_schedule_dir = 1 THEN
840 i := op_res_info_tbl.NEXT(i);
841 ELSE
842 i := op_res_info_tbl.PRIOR(i);
843 END IF;
844 --end of fix for 3574991
845
846 END LOOP;
847
848 IF p_schedule_dir = 1 THEN --copy the min resource start date to operation's start date
849 p_op_start_date := l_min_rsc_start_date;
850 ELSE --copy max resource end date to operation's end date
851 p_op_completion_date := l_max_rsc_end_date;
852 END IF; --end of check for scheduling direction
853
854
855 EXCEPTION
856 WHEN NO_SFT_EXC THEN
857 x_return_status := fnd_api.g_ret_sts_error;
858 x_error_message := 'NO SHIFT ASSOCIATED TO A RESOURCE';
859 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('No shift found for the resource') ; END IF ;
860
861 END; /*SCHEDULE_RESOURCES */
862
863
864
865
866
867
868
869
870 PROCEDURE SCHEDULE_OPERATIONS
871 ( p_organization_id IN NUMBER,
872 p_wip_entity_id IN NUMBER,
873 p_start_date IN OUT NOCOPY DATE,
874 p_completion_date IN OUT NOCOPY DATE,
875 p_hour_conv IN NUMBER,
876 p_calendar_code IN VARCHAR2,
877 p_excetion_set_id IN NUMBER,
878 p_validation_level IN NUMBER,
879 p_res_usage_tbl IN OUT NOCOPY EAM_PROCESS_WO_PUB.eam_res_usage_tbl_type,
880 p_commit IN VARCHAR2,
881 x_error_message OUT NOCOPY VARCHAR2,
882 x_return_status OUT NOCOPY VARCHAR2) IS
883
884 l_calender_code VARCHAR2(10);
885 l_exception_set_id NUMBER;
886 l_res_rec_count NUMBER;
887 l_op_scd_seq_count NUMBER;
888 l_schedule_dir NUMBER;
889 l_op_index NUMBER;
890 l_res_index NUMBER;
891 l_op_seq_num NUMBER;
892 l_res_count NUMBER;
893 l_op_start_date DATE;
894 l_op_completion_date DATE;
895 l_min_start_date DATE;
896 l_max_completion_date DATE;
897 l_res_sft_rec_count NUMBER;
898 l_stmt_num NUMBER := 100;
899 l_op_last_level NUMBER;
900 l_sched_start_wo_dt DATE;
901 l_sched_completion_wo_dt DATE;
902
903
904
905 /* Cursor definition to store resource information*/
906
907 CURSOR EAM_RSC_CSR IS
908 SELECT WO.OPERATION_SEQ_NUM OP_SEQ_NUM,
909 WO.OPERATION_SEQUENCE_ID OP_SEQ_ID,
910 WO.FIRST_UNIT_START_DATE OP_START_DATE,
911 WO.LAST_UNIT_COMPLETION_DATE OP_COMPLETION_DATE,
912 WO.OPERATION_COMPLETED OP_COMPLETED,
913 WOR.RESOURCE_SEQ_NUM RES_SEQ_NUM,
914 NVL(WOR.SCHEDULE_SEQ_NUM, WOR.RESOURCE_SEQ_NUM) RES_SCH_NUM,
915 WOR.RESOURCE_ID RES_ID,
916 WOR.START_DATE RES_START_DATE,
917 WOR.COMPLETION_DATE RES_COMPLETION_DATE,
918 NVL(WOR.ASSIGNED_UNITS, 0) ASSIGNED_UNITS,
919 DR2.CAPACITY_UNITS CAPACITY_UNITS,
920 ROUND(WOR.USAGE_RATE_OR_AMOUNT * (1/p_hour_conv )* DECODE (CON.CONVERSION_RATE, '', 0, '0', 0, CON.CONVERSION_RATE) *
921 DECODE (WOR.BASIS_TYPE, 1, 1, 2, 1, 1) * 3600) USAGE_RATE,
922 DECODE(WOR.SCHEDULED_FLAG, 1, DECODE(WOR.USAGE_RATE_OR_AMOUNT, 0, 2, 1),
923 WOR.SCHEDULED_FLAG) SCHEDULED_FLAG,
924 DR2.AVAILABLE_24_HOURS_FLAG AVAIL_24_HRS_FLAG
925 FROM WIP_OPERATIONS WO,
926 BOM_DEPARTMENT_RESOURCES DR1,
927 BOM_DEPARTMENT_RESOURCES DR2,
928 WIP_OPERATION_RESOURCES WOR,
929 BOM_RESOURCES RES,
930 MTL_UOM_CONVERSIONS CON
931 WHERE
932 WO.WIP_ENTITY_ID = p_wip_entity_id
933 AND WO.ORGANIZATION_ID = p_organization_id
934 AND WO.WIP_ENTITY_ID = WOR.WIP_ENTITY_ID
935 AND WO.OPERATION_SEQ_NUM = WOR.OPERATION_SEQ_NUM
936 AND WO.DEPARTMENT_ID = DR1.DEPARTMENT_ID
937 AND WOR.RESOURCE_ID = DR1.RESOURCE_ID
938 AND NVL(DR1.SHARE_FROM_DEPT_ID, DR1.DEPARTMENT_ID) = DR2.DEPARTMENT_ID
939 AND WOR.RESOURCE_ID = DR2.RESOURCE_ID
940 AND WOR.RESOURCE_ID = RES.RESOURCE_ID
941 AND CON.UOM_CODE (+) = RES.UNIT_OF_MEASURE
942 AND CON.INVENTORY_ITEM_ID (+) = 0
943 ORDER BY WO.OPERATION_SEQ_NUM,
944 -- WOR.RESOURCE_SEQ_NUM;
945 WOR.SCHEDULE_SEQ_NUM;
946 /* Cursor to identify the operation scheduling sequence considering the dependency */
947
948 CURSOR OP_FWD_SCD_SEQ_CSR IS
949 SELECT -1 "LEVEL", operation_seq_num "OP_SEQ_NUM"
950 FROM wip_operations
951 WHERE wip_entity_id = p_wip_entity_id
952 AND operation_seq_num not in( SELECT prior_operation
953 FROM WIP_OPERATION_NETWORKS
954 WHERE wip_entity_id = p_wip_entity_id
955 UNION
956 SELECT next_operation
957 FROM WIP_OPERATION_NETWORKS
958 WHERE wip_entity_id = p_wip_entity_id)
959 UNION
960
961 SELECT 0 "LEVEL", prior_operation "OP_SEQ_NUM"
962 FROM wip_operation_networks
963 WHERE prior_operation NOT IN
964 (SELECT next_operation
965 FROM wip_operation_networks
966 WHERE wip_entity_id = p_wip_entity_id )
967 AND wip_entity_id=p_wip_entity_id
968 UNION
969 SELECT max(level) "LEVEL", next_operation "OP_SEQ_NUM"
970 FROM wip_operation_networks
971 WHERE wip_entity_id=p_wip_entity_id
972 START WITH prior_operation IN
973 (SELECT prior_operation
974 FROM wip_operation_networks
975 WHERE prior_operation NOT IN
976 (SELECT next_operation
977 FROM wip_operation_networks
978 WHERE wip_entity_id=p_wip_entity_id )
979 AND wip_entity_id=p_wip_entity_id)
980 CONNECT BY PRIOR next_operation = prior_operation
981 AND wip_entity_id = p_wip_entity_id
982 GROUP BY next_operation
983 ORDER BY 1;
984
985
986 CURSOR OP_BWD_SCD_SEQ_CSR IS
987 SELECT -1 "LEVEL", operation_seq_num "OP_SEQ_NUM"
988 FROM wip_operations
989 WHERE wip_entity_id = p_wip_entity_id
990 AND operation_seq_num not in( SELECT prior_operation
991 FROM WIP_OPERATION_NETWORKS
992 WHERE wip_entity_id = p_wip_entity_id
993 UNION
994 SELECT next_operation
995 FROM WIP_OPERATION_NETWORKS
996 WHERE wip_entity_id = p_wip_entity_id)
997 UNION
998 SELECT 0 "LEVEL", next_operation "OP_SEQ_NUM"
999 FROM wip_operation_networks
1000 WHERE next_operation NOT IN
1001 (SELECT prior_operation
1002 FROM wip_operation_networks
1003 WHERE wip_entity_id = p_wip_entity_id )
1004 AND wip_entity_id=p_wip_entity_id
1005 UNION
1006 SELECT max(level) "LEVEL", prior_operation "OP_SEQ_NUM"
1007 FROM wip_operation_networks
1008 WHERE wip_entity_id=p_wip_entity_id
1009 START WITH next_operation IN
1010 (SELECT next_operation
1011 FROM wip_operation_networks
1012 WHERE next_operation NOT IN
1013 (SELECT prior_operation
1014 FROM wip_operation_networks
1015 WHERE wip_entity_id=p_wip_entity_id )
1016 AND wip_entity_id=p_wip_entity_id)
1017 CONNECT BY PRIOR prior_operation = next_operation
1018 AND wip_entity_id = p_wip_entity_id
1019 GROUP BY prior_operation
1020 ORDER BY 1;
1021
1022 /* Bug 5144273. Split cursor OP_RES_SFT_CSR into two cursors OP_RES_SFT_CSR_FWD and OP_RES_SFT_CSR_BWD .
1023 OP_RES_SFT_CSR_FWD is used for Forward scheduling and OP_RES_SFT_CSR_BWD for backward scheduling
1024 */
1025 CURSOR OP_RES_SFT_CSR_FWD IS
1026 SELECT WO.OPERATION_SEQ_NUM OP_SEQ_NUM,
1027 WOR.RESOURCE_SEQ_NUM RES_SEQ_NUM,
1028 SHF.SHIFT_NUM SHIFT_NUM,
1029 SHF.FROM_TIME FROM_TIME,
1030 SHF.TO_TIME TO_TIME
1031 FROM BOM_SHIFT_TIMES SHF,
1032 BOM_RESOURCE_SHIFTS RSH,
1033 BOM_DEPARTMENT_RESOURCES BDR,
1034 WIP_OPERATION_RESOURCES WOR,
1035 WIP_OPERATIONS WO
1036 WHERE WO.WIP_ENTITY_ID = p_wip_entity_id
1037 AND WO.ORGANIZATION_ID = p_organization_id
1038 AND WO.WIP_ENTITY_ID = WOR.WIP_ENTITY_ID
1039 AND WO.OPERATION_SEQ_NUM = WOR.OPERATION_SEQ_NUM
1040 AND WOR.SCHEDULED_FLAG IS NOT NULL
1041 AND WO.DEPARTMENT_ID = BDR.DEPARTMENT_ID
1042 AND WOR.RESOURCE_ID = BDR.RESOURCE_ID
1043 AND NVL(BDR.SHARE_FROM_DEPT_ID, BDR.DEPARTMENT_ID) = RSH.DEPARTMENT_ID
1044 AND RSH.RESOURCE_ID = WOR.RESOURCE_ID
1045 AND RSH.SHIFT_NUM = SHF.SHIFT_NUM
1046 AND SHF.CALENDAR_CODE = p_calendar_code
1047 ORDER BY FROM_TIME, TO_TIME ;
1048
1049
1050 CURSOR OP_RES_SFT_CSR_BWD IS
1051 SELECT WO.OPERATION_SEQ_NUM OP_SEQ_NUM,
1052 WOR.RESOURCE_SEQ_NUM RES_SEQ_NUM,
1053 SHF.SHIFT_NUM SHIFT_NUM,
1054 SHF.FROM_TIME FROM_TIME,
1055 SHF.TO_TIME TO_TIME
1056 FROM BOM_SHIFT_TIMES SHF,
1057 BOM_RESOURCE_SHIFTS RSH,
1058 BOM_DEPARTMENT_RESOURCES BDR,
1059 WIP_OPERATION_RESOURCES WOR,
1060 WIP_OPERATIONS WO
1061 WHERE WO.WIP_ENTITY_ID = p_wip_entity_id
1062 AND WO.ORGANIZATION_ID = p_organization_id
1063 AND WO.WIP_ENTITY_ID = WOR.WIP_ENTITY_ID
1064 AND WO.OPERATION_SEQ_NUM = WOR.OPERATION_SEQ_NUM
1065 AND WOR.SCHEDULED_FLAG IS NOT NULL
1066 AND WO.DEPARTMENT_ID = BDR.DEPARTMENT_ID
1067 AND WOR.RESOURCE_ID = BDR.RESOURCE_ID
1068 AND NVL(BDR.SHARE_FROM_DEPT_ID, BDR.DEPARTMENT_ID) = RSH.DEPARTMENT_ID
1069 AND RSH.RESOURCE_ID = WOR.RESOURCE_ID
1070 AND RSH.SHIFT_NUM = SHF.SHIFT_NUM
1071 AND SHF.CALENDAR_CODE = p_calendar_code
1072 ORDER BY TO_TIME DESC, FROM_TIME DESC;
1073
1074
1075 BEGIN /*SCHEDULE_OPERATIONS*/
1076
1077 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Starting SCHEDULE_OPERATIONS') ; END IF ;
1078 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Scheduling '|| p_wip_entity_id) ; END IF ;
1079
1080 l_res_rec_count:=0;
1081 op_res_info_tbl.delete;
1082
1083 FOR l_op_res_info_rec IN EAM_RSC_CSR LOOP
1084 --fix for bug 3355437.commented the following code so that pl/sql tables will be initialized always
1085 /* l_res_rec_count := op_res_info_tbl.last;
1086
1087 IF l_res_rec_count is NULL THEN
1088 l_res_rec_count:=0;
1089 END IF;*/
1090
1091 l_res_rec_count := l_res_rec_count + 1;
1092
1093 op_res_info_tbl(l_res_rec_count).op_seq_num := l_op_res_info_rec.OP_SEQ_NUM;
1094 op_res_info_tbl(l_res_rec_count).op_seq_id := l_op_res_info_rec.OP_SEQ_id;
1095 op_res_info_tbl(l_res_rec_count).op_start_date := l_op_res_info_rec.op_start_date;
1096 op_res_info_tbl(l_res_rec_count).op_completion_date := l_op_res_info_rec.op_completion_date;
1097 op_res_info_tbl(l_res_rec_count).op_completed := l_op_res_info_rec.op_completed;
1098 op_res_info_tbl(l_res_rec_count).res_seq_num := l_op_res_info_rec.res_seq_num;
1099 op_res_info_tbl(l_res_rec_count).res_sch_num := l_op_res_info_rec.res_sch_num;
1100 op_res_info_tbl(l_res_rec_count).res_id := l_op_res_info_rec.res_id;
1101 op_res_info_tbl(l_res_rec_count).res_start_date := l_op_res_info_rec.res_start_date;
1102 op_res_info_tbl(l_res_rec_count).res_completion_date := l_op_res_info_rec.res_completion_date;
1103 op_res_info_tbl(l_res_rec_count).assigned_units := l_op_res_info_rec.ASSIGNED_UNITS;
1104 op_res_info_tbl(l_res_rec_count).capacity_units := l_op_res_info_rec.CAPACITY_UNITS;
1105 op_res_info_tbl(l_res_rec_count).usage_rate := l_op_res_info_rec.USAGE_RATE;
1106 op_res_info_tbl(l_res_rec_count).scheduled_flag := l_op_res_info_rec.SCHEDULED_FLAG;
1107 op_res_info_tbl(l_res_rec_count).avail_24_hrs_flag := l_op_res_info_rec.AVAIL_24_HRS_FLAG;
1108
1109 END LOOP;
1110
1111 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 ;
1112
1113 l_res_sft_rec_count := 0;
1114 op_res_sft_tbl.delete;
1115
1116 IF p_start_date IS NOT NULL THEN -- fwd scheduling .added for bug 5144273
1117
1118 FOR l_op_rsc_sft_rec IN OP_RES_SFT_CSR_FWD LOOP
1119
1120 l_res_sft_rec_count := l_res_sft_rec_count + 1;
1121
1122 op_res_sft_tbl(l_res_sft_rec_count).op_seq_num := l_op_rsc_sft_rec.op_seq_num;
1123 op_res_sft_tbl(l_res_sft_rec_count).res_seq_num := l_op_rsc_sft_rec.res_seq_num;
1124 op_res_sft_tbl(l_res_sft_rec_count).shift_num := l_op_rsc_sft_rec.shift_num;
1125 op_res_sft_tbl(l_res_sft_rec_count).from_time := l_op_rsc_sft_rec.from_time;
1126 op_res_sft_tbl(l_res_sft_rec_count).to_time := l_op_rsc_sft_rec.to_time;
1127 END LOOP;
1128 ELSE -- bwd scheduling . added for bug 5144273
1129 FOR l_op_rsc_sft_rec IN OP_RES_SFT_CSR_BWD LOOP
1130
1131 l_res_sft_rec_count := l_res_sft_rec_count + 1;
1132
1133 op_res_sft_tbl(l_res_sft_rec_count).op_seq_num := l_op_rsc_sft_rec.op_seq_num;
1134 op_res_sft_tbl(l_res_sft_rec_count).res_seq_num := l_op_rsc_sft_rec.res_seq_num;
1135 op_res_sft_tbl(l_res_sft_rec_count).shift_num := l_op_rsc_sft_rec.shift_num;
1136 op_res_sft_tbl(l_res_sft_rec_count).from_time := l_op_rsc_sft_rec.from_time;
1137 op_res_sft_tbl(l_res_sft_rec_count).to_time := l_op_rsc_sft_rec.to_time;
1138 END LOOP;
1139 END IF ;
1140
1141
1142 IF p_start_date IS NOT NULL THEN
1143
1144 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Forward Scheduling') ; END IF ;
1145
1146 l_schedule_dir := 1;
1147
1148 l_op_scd_seq_count:=0;
1149 op_scd_seq_tbl.delete;
1150
1151 FOR l_op_scd_seq_rec IN OP_FWD_SCD_SEQ_CSR LOOP
1152 --fix for bug 3355437.commented the following code so that pl/sql tables will be initialized always
1153 /*l_op_scd_seq_count := op_scd_seq_tbl.last;
1154
1155 IF l_op_scd_seq_count is NULL THEN
1156 l_op_scd_seq_count:=0;
1157 END IF;*/
1158
1159 l_op_scd_seq_count := l_op_scd_seq_count + 1;
1160
1161 op_scd_seq_tbl(l_op_scd_seq_count).level := l_op_scd_seq_rec.LEVEL;
1162 op_scd_seq_tbl(l_op_scd_seq_count).op_seq_num := l_op_scd_seq_rec.OP_SEQ_NUM;
1163 op_scd_seq_tbl(l_op_scd_seq_count).op_start_date := NULL;
1164 op_scd_seq_tbl(l_op_scd_seq_count).op_completion_date := NULL;
1165
1166 END LOOP;
1167
1168 l_op_index := op_scd_seq_tbl.FIRST;
1169
1170 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 ;
1171
1172 WHILE l_op_index is not NULL LOOP
1173
1174 l_op_seq_num := op_scd_seq_tbl(l_op_index).op_seq_num;
1175 l_res_count := 0;
1176
1177 IF op_scd_seq_tbl(l_op_index).level = 0 OR op_scd_seq_tbl(l_op_index).level = -1 THEN
1178
1179 l_op_start_date := p_start_date;
1180 ELSE
1181
1182 dep_op_seq_num_tbl.delete;
1183
1184 SELECT prior_operation
1185 BULK COLLECT INTO dep_op_seq_num_tbl
1186 FROM wip_operation_networks
1187 WHERE wip_entity_id = p_wip_entity_id
1188 AND next_operation = l_op_seq_num;
1189
1190
1191 DECLARE
1192 l_dep_index NUMBER;
1193 l_op_tab_index NUMBER;
1194 BEGIN
1195
1196 l_op_start_date := p_start_date;
1197 l_dep_index := dep_op_seq_num_tbl.FIRST;
1198
1199 WHILE l_dep_index is not NULL LOOP
1200
1201 l_op_tab_index := op_scd_seq_tbl.FIRST;
1202
1203 WHILE l_op_tab_index is not NULL LOOP
1204
1205 IF dep_op_seq_num_tbl(l_dep_index) = op_scd_seq_tbl(l_op_tab_index).op_seq_num THEN
1206
1207 IF l_op_start_date <= op_scd_seq_tbl(l_op_tab_index).op_completion_date THEN
1208
1209 l_op_start_date := op_scd_seq_tbl(l_op_tab_index).op_completion_date;
1210
1211 ELSE
1212 NULL;
1213 END IF;
1214
1215 EXIT;
1216
1217 END IF;
1218
1219 l_op_tab_index := op_scd_seq_tbl.NEXT(l_op_tab_index);
1220
1221 END LOOP; /* idenfication of completion time of prior op*/
1222
1223 l_dep_index := dep_op_seq_num_tbl.NEXT(l_dep_index);
1224
1225 END LOOP; /* Comparing all prior op completion time */
1226
1227 END;
1228
1229
1230 END IF;/* Identification of start time for intermediate operation ends*/
1231
1232 l_res_index := op_res_info_tbl.FIRST;
1233
1234 WHILE l_res_index is not NULL LOOP
1235
1236 IF op_scd_seq_tbl(l_op_index).op_seq_num = op_res_info_tbl(l_res_index).op_seq_num THEN
1237
1238 l_res_count := l_res_count + 1;
1239
1240 END IF;
1241
1242 l_res_index := op_res_info_tbl.NEXT(l_res_index);
1243
1244 END LOOP;
1245
1246 IF l_res_count = 0 THEN
1247 l_op_completion_date := l_op_start_date;
1248 ELSE
1249
1250 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Calling EAM_CALC_OPERATION_TIME') ; END IF ;
1251 --fix for 3574991
1252 l_op_completion_date :=l_op_start_date;
1253
1254 SCHEDULE_RESOURCES
1255 (p_organization_id,
1256 p_wip_entity_id,
1257 l_op_seq_num,
1258 l_schedule_dir,
1259 p_calendar_code,
1260 op_res_info_tbl,
1261 op_res_sft_tbl,
1262 l_op_start_date,
1263 l_op_completion_date,
1264 p_res_usage_tbl,
1265 p_validation_level,
1266 p_commit,
1267 x_error_message,
1268 x_return_status );
1269
1270 IF(x_return_status <> 'S') THEN
1271 RETURN;
1272 END IF;
1273
1274 END IF;
1275
1276 op_scd_seq_tbl(l_op_index).op_start_date := l_op_start_date;
1277 op_scd_seq_tbl(l_op_index).op_completion_date := l_op_completion_date;
1278
1279 l_op_index := op_scd_seq_tbl.NEXT(l_op_index);
1280
1281 END LOOP;
1282
1283 ELSE
1284
1285 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Backward Scheduling') ; END IF ;
1286
1287
1288 l_schedule_dir := 2;
1289
1290 l_op_scd_seq_count:=0;
1291 op_scd_seq_tbl.delete;
1292
1293 FOR l_op_scd_seq_rec IN OP_BWD_SCD_SEQ_CSR LOOP
1294 --fix for bug 3355437.commented the following code so that pl/sql tables will be initialized always
1295 /* l_op_scd_seq_count := op_scd_seq_tbl.last;
1296
1297 IF l_op_scd_seq_count is NULL THEN
1298
1299 l_op_scd_seq_count:=0;
1300
1301 END IF;*/
1302
1303 l_op_scd_seq_count := l_op_scd_seq_count + 1;
1304
1305 op_scd_seq_tbl(l_op_scd_seq_count).level := l_op_scd_seq_rec.LEVEL;
1306 op_scd_seq_tbl(l_op_scd_seq_count).op_seq_num := l_op_scd_seq_rec.OP_SEQ_NUM;
1307 op_scd_seq_tbl(l_op_scd_seq_count).op_start_date := NULL;
1308 op_scd_seq_tbl(l_op_scd_seq_count).op_completion_date := NULL;
1309
1310 END LOOP;
1311
1312 l_op_index := op_scd_seq_tbl.FIRST;
1313
1314 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 ;
1315
1316 WHILE l_op_index is not NULL LOOP
1317
1318 l_op_seq_num := op_scd_seq_tbl(l_op_index).op_seq_num;
1319 l_res_count := 0;
1320
1321 IF op_scd_seq_tbl(l_op_index).level = 0 OR
1322 op_scd_seq_tbl(l_op_index).level = -1 THEN
1323
1324 l_op_completion_date := p_completion_date;
1325
1326 ELSE
1327
1328 dep_op_seq_num_tbl.delete;
1329
1330 SELECT next_operation
1331 BULK COLLECT INTO dep_op_seq_num_tbl
1332 FROM wip_operation_networks
1333 WHERE wip_entity_id = p_wip_entity_id
1334 AND prior_operation = l_op_seq_num;
1335
1336 DECLARE
1337
1338 l_dep_index NUMBER;
1339 l_op_tab_index NUMBER;
1340
1341 BEGIN
1342
1343 l_op_completion_date := p_completion_date;
1344 l_dep_index := dep_op_seq_num_tbl.FIRST;
1345
1346 WHILE l_dep_index is not NULL LOOP
1347
1348 l_op_tab_index := op_scd_seq_tbl.FIRST;
1349
1350 WHILE l_op_tab_index is not NULL LOOP
1351
1352 IF dep_op_seq_num_tbl(l_dep_index) = op_scd_seq_tbl(l_op_tab_index).op_seq_num THEN
1353
1354 IF l_op_completion_date >= op_scd_seq_tbl(l_op_tab_index).op_start_date THEN
1355
1356 l_op_completion_date := op_scd_seq_tbl(l_op_tab_index).op_start_date;
1357
1358 ELSE
1359
1360 NULL;
1361
1362 END IF;
1363
1364 EXIT;
1365
1366 END IF;
1367
1368 l_op_tab_index := op_scd_seq_tbl.NEXT(l_op_tab_index);
1369
1370 END LOOP; /* idenfication of start time of next op*/
1371
1372 l_dep_index := dep_op_seq_num_tbl.NEXT(l_dep_index);
1373
1374 END LOOP; /* Comparing all NEXT op start time */
1375
1376 END;
1377
1378 END IF;/* Identification of start time for intermediate operation ends*/
1379
1380 l_res_index := op_res_info_tbl.FIRST;
1381
1382 WHILE l_res_index is not NULL LOOP
1383
1384 IF op_scd_seq_tbl(l_op_index).op_seq_num = op_res_info_tbl(l_res_index).op_seq_num THEN
1385
1386 l_res_count := l_res_count + 1;
1387
1388 END IF;
1389
1390 l_res_index := op_res_info_tbl.NEXT(l_res_index);
1391
1392 END LOOP;
1393
1394 IF l_res_count = 0 THEN
1395 l_op_start_date := l_op_completion_date;
1396 ELSE
1397
1398 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Calling EAM_CALC_OPERATION_TIME') ; END IF ;
1399 --fix for 3574991
1400 l_op_start_date := l_op_completion_date;
1401
1402 SCHEDULE_RESOURCES
1403 (p_organization_id,
1404 p_wip_entity_id,
1405 l_op_seq_num,
1406 l_schedule_dir,
1407 p_calendar_code,
1408 op_res_info_tbl,
1409 op_res_sft_tbl,
1410 l_op_start_date,
1411 l_op_completion_date,
1412 p_res_usage_tbl,
1413 p_validation_level,
1414 p_commit,
1415 x_error_message,
1416 x_return_status );
1417
1418 IF(x_return_status <> 'S') THEN
1419 RETURN;
1420 END IF;
1421
1422 END IF;
1423
1424 -- dbms_output.put_line ('Start Date *' || l_op_start_date);
1425 -- dbms_output.put_line ('Completion Date *' || l_op_completion_date);
1426
1427
1428 op_scd_seq_tbl(l_op_index).op_start_date := l_op_start_date;
1429 op_scd_seq_tbl(l_op_index).op_completion_date := l_op_completion_date;
1430
1431 l_op_index := op_scd_seq_tbl.NEXT(l_op_index);
1432
1433 END LOOP;
1434
1435 END IF; /* Decision for Forward or backward scheduling */
1436
1437
1438 l_op_index := op_scd_seq_tbl.FIRST;
1439
1440 l_max_completion_date := NULL;
1441 l_min_start_date := NULL;
1442
1443
1444 WHILE l_op_index is not NULL LOOP
1445
1446 l_op_start_date := op_scd_seq_tbl(l_op_index).op_start_date;
1447 l_op_completion_date := op_scd_seq_tbl(l_op_index).op_completion_date;
1448 l_op_seq_num := op_scd_seq_tbl(l_op_index).op_seq_num;
1449
1450 -- bug no 3444091
1451 if l_op_start_date > l_op_completion_date then
1452 x_return_status := fnd_api.g_ret_sts_error;
1453 fnd_message.set_name('EAM','EAM_WO_OP_DT_TK_ERR');
1454 fnd_message.set_token('OPNO', l_op_seq_num);
1455 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Negative operation duration') ; END IF ;
1456 return;
1457 end if;
1458
1459
1460 UPDATE WIP_OPERATIONS
1461 SET FIRST_UNIT_START_DATE = l_op_start_date,
1462 FIRST_UNIT_COMPLETION_DATE = l_op_completion_date,
1463 LAST_UNIT_START_DATE = l_op_start_date,
1464 LAST_UNIT_COMPLETION_DATE = l_op_completion_date
1465 WHERE WIP_ENTITY_ID = p_wip_entity_id
1466 AND OPERATION_SEQ_NUM = l_op_seq_num;
1467
1468 --irrespective of the scheduling direciton.find the minimum and maximum of operation dates
1469 IF((l_max_completion_date IS NULL) OR (l_max_completion_date < l_op_completion_date)) THEN
1470 l_max_completion_date := l_op_completion_date;
1471 END IF;
1472
1473 IF((l_min_start_date IS NULL) OR (l_min_start_date > l_op_start_date)) THEN
1474 l_min_start_date := l_op_start_date;
1475 END IF;
1476
1477
1478 l_op_index := op_scd_seq_tbl.NEXT(l_op_index);
1479
1480 END LOOP;
1481
1482 --workorder dates will be the minimum start date and maximum end date of operations.
1483 p_start_date := NVL(l_min_start_date,NVL(p_start_date,p_completion_date)); --will be Null when there are no operations
1484 p_completion_date := NVL(l_max_completion_date, p_start_date);
1485
1486 if p_start_date > p_completion_date then
1487 x_return_status := fnd_api.g_ret_sts_error;
1488 fnd_message.set_name('EAM','EAM_WO_DT_TK_ERR');
1489 fnd_message.set_token('WONO', p_wip_entity_id);
1490 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Negative workorder duration') ; END IF ;
1491 return;
1492 end if;
1493
1494 update wip_discrete_jobs
1495 set scheduled_start_date = p_start_date,
1496 scheduled_completion_date = p_completion_date
1497 where wip_entity_id = p_wip_entity_id
1498 and organization_id = p_organization_id;
1499
1500
1501 END;/* SCHEDULE_OPERATIONS */
1502
1503
1504
1505
1506
1507 /* Procedure EAM_RTG_GET_INFO for calling procedures to load activity
1508 routing data to wip tables AND to schedule the operations */
1509
1510 PROCEDURE SCHEDULE_WO
1511 ( p_organization_id IN NUMBER,
1512 p_wip_entity_id IN NUMBER,
1513 p_start_date IN OUT NOCOPY DATE,
1514 p_completion_date IN OUT NOCOPY DATE,
1515 p_validation_level IN NUMBER,
1516 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1517 x_error_message OUT NOCOPY VARCHAR2,
1518 x_return_status OUT NOCOPY VARCHAR2
1519 )
1520 IS
1521 l_calendar_code VARCHAR2(10);
1522 l_exception_set_id NUMBER;
1523 l_uom_conv NUMBER;
1524 l_stmt_num NUMBER := 0;
1525 l_error_msg VARCHAR2(8000);
1526 l_stmt_msg VARCHAR2(200);
1527 l_hour_uom VARCHAR2(10);
1528 l_scd_req NUMBER := 1;
1529 INVALID_PARAM_EXC EXCEPTION;
1530
1531 TYPE l_relationship_records IS REF CURSOR RETURN WIP_SCHED_RELATIONSHIPS%ROWTYPE;
1532 l_constrained_children l_relationship_records;
1533 l_relationship_record WIP_SCHED_RELATIONSHIPS%ROWTYPE;
1534
1535 l_min_date DATE := null;
1536 l_max_date DATE := null;
1537 l_date DATE := null;
1538
1539 l_wo_start_date DATE := null;
1540 l_wo_end_date DATE := null;
1541 l_start_date DATE := null;
1542 l_compl_date DATE := null;
1543 l_status_type NUMBER;
1544 l_date_completed DATE;
1545
1546 l_request_id NUMBER;
1547 l_program_application_id NUMBER;
1548 l_program_id NUMBER;
1549 l_program_update_date DATE;
1550
1551 p_res_usage_tbl EAM_PROCESS_WO_PUB.eam_res_usage_tbl_type;
1552
1553 TYPE wip_operations_tbl_type is TABLE OF number INDEX BY BINARY_INTEGER;
1554 TYPE wip_op_resource_tbl_type is TABLE OF number INDEX BY BINARY_INTEGER;
1555 TYPE wip_op_res_inst_tbl_type is TABLE OF number INDEX BY BINARY_INTEGER;
1556 TYPE wip_op_r_inst_st_tbl_type is TABLE OF DATE INDEX BY BINARY_INTEGER;
1557 TYPE wip_op_r_inst_end_tbl_type is TABLE OF DATE INDEX BY BINARY_INTEGER;
1558
1559 l_WipOperation_tbl wip_operations_tbl_type;
1560 l_WipOperResource_tbl wip_op_resource_tbl_type;
1561 l_WipOperResInst_tbl wip_op_res_inst_tbl_type;
1562 l_WipOperResInstSt_tbl wip_op_r_inst_st_tbl_type;
1563 l_WipOperResInstEnd_tbl wip_op_r_inst_end_tbl_type;
1564
1565 l_mesg_token_tbl EAM_ERROR_MESSAGE_PVT.Mesg_Token_Tbl_Type;
1566 l_out_mesg_token_tbl EAM_ERROR_MESSAGE_PVT.Mesg_Token_Tbl_Type;
1567 l_token_tbl EAM_ERROR_MESSAGE_PVT.Token_Tbl_Type ;
1568
1569 -- Added for bug 5175006
1570 l_count NUMBER ;
1571 CURSOR c_zero_res_req_hours IS
1572 SELECT operation_seq_num,
1573 resource_seq_num,
1574 start_date,
1575 completion_date,
1576 assigned_units
1577 FROM wip_operation_resources
1578 WHERE wip_entity_id = p_wip_entity_id
1579 AND organization_id = p_organization_id
1580 AND usage_rate_or_amount = 0 ;
1581
1582 l_res_rec c_zero_res_req_hours%ROWTYPE ;
1583
1584 BEGIN
1585
1586 SAVEPOINT SCHEDULE_WO;
1587
1588 x_return_status := FND_API.G_RET_STS_SUCCESS;
1589
1590 l_stmt_num := 10;
1591
1592 /* For identifying Hour UOM for resource scheduling */
1593 l_hour_uom := fnd_profile.value('BOM:HOUR_UOM_CODE');
1594
1595 IF l_hour_uom IS NULL THEN
1596 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 ;
1597 x_return_status := FND_API.G_RET_STS_ERROR;
1598 l_token_tbl.DELETE;
1599 l_token_tbl(1).token_name := 'PROFILE';
1600 l_token_tbl(1).token_value := 'BOM: Hour UOM';
1601 l_out_mesg_token_tbl := l_mesg_token_tbl;
1602
1603 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
1604 ( x_Mesg_token_tbl => l_out_Mesg_Token_Tbl
1605 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1606 , p_message_name => 'EAM_NULL_PROFILE'
1607 , p_token_tbl => l_token_tbl
1608 );
1609
1610 l_mesg_token_tbl := l_out_mesg_token_tbl;
1611
1612 EAM_ERROR_MESSAGE_PVT.Translate_And_Insert_Messages
1613 ( p_mesg_token_tbl => l_mesg_token_tbl
1614 , p_error_level => EAM_ERROR_MESSAGE_PVT.G_WO_LEVEL
1615 , p_entity_index => 1
1616 );
1617
1618 RETURN;
1619 END IF;
1620
1621
1622 l_stmt_num := 60;
1623
1624 SELECT CON.CONVERSION_RATE
1625 INTO l_uom_conv
1626 FROM MTL_UOM_CONVERSIONS CON
1627 WHERE CON.UOM_CODE = l_hour_uom
1628 AND NVL(DISABLE_DATE, SYSDATE + 1) > SYSDATE
1629 AND CON.INVENTORY_ITEM_ID = 0;
1630
1631 l_stmt_num := 70;
1632
1633 SELECT CALENDAR_CODE,
1634 CALENDAR_EXCEPTION_SET_ID
1635 INTO l_calendar_code,
1636 l_exception_set_id
1637 FROM MTL_PARAMETERS
1638 WHERE ORGANIZATION_ID = p_organization_id;
1639
1640
1641 l_stmt_num := 80;
1642
1643 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Calling CALC_SCHEDULE from SCHEDULE_WO') ; END IF ;
1644
1645 DELETE FROM wip_operation_resource_usage
1646 WHERE wip_entity_id = p_wip_entity_id
1647 AND organization_id = p_organization_id;
1648
1649
1650 SCHEDULE_OPERATIONS
1651 ( p_organization_id,
1652 p_wip_entity_id,
1653 p_start_date,
1654 p_completion_date,
1655 l_uom_conv,
1656 l_calendar_code,
1657 l_exception_set_id,
1658 p_validation_level,
1659 p_res_usage_tbl,
1660 p_commit,
1661 x_error_message,
1662 x_return_status
1663 );
1664
1665 IF(x_return_status <> 'S' ) THEN
1666 ROLLBACK TO SCHEDULE_WO;
1667 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 ;
1668 RETURN;
1669 END IF;
1670
1671 -- Added for bug 5175006
1672 l_count := p_res_usage_tbl.count ;
1673
1674 OPEN c_zero_res_req_hours ;
1675 LOOP
1676 FETCH c_zero_res_req_hours INTO l_res_rec ;
1677 EXIT WHEN c_zero_res_req_hours%NOTFOUND ;
1678
1679 l_count := l_count +1 ;
1680 p_res_usage_tbl( l_count ).operation_seq_num := l_res_rec.operation_seq_num ;
1681 p_res_usage_tbl( l_count ).resource_seq_num := l_res_rec.resource_seq_num ;
1682 p_res_usage_tbl( l_count ).start_date := l_res_rec.start_date ;
1683 p_res_usage_tbl( l_count ).completion_date := l_res_rec.completion_date ;
1684 p_res_usage_tbl( l_count ).assigned_units := l_res_rec.assigned_units ;
1685
1686 END LOOP;
1687 CLOSE c_zero_res_req_hours ;
1688 -- end of fix for bug 5175006
1689
1690 IF p_res_usage_tbl.count > 0 THEN
1691
1692 SELECT request_id,program_application_id,program_id,program_update_date
1693 INTO l_request_id,l_program_application_id,l_program_id,l_program_update_date
1694 FROM wip_discrete_jobs
1695 WHERE wip_entity_id = p_wip_entity_id
1696 AND organization_id = p_organization_id;
1697
1698 FOR cnt IN p_res_usage_tbl.FIRST..p_res_usage_tbl.LAST LOOP
1699
1700 INSERT INTO WIP_OPERATION_RESOURCE_USAGE
1701 ( wip_entity_id
1702 , operation_seq_num
1703 , resource_seq_num
1704 , organization_id
1705 , start_date
1706 , completion_date
1707 , assigned_units
1708 , last_update_date
1709 , last_updated_by
1710 , creation_date
1711 , created_by
1712 , last_update_login
1713 , request_id
1714 , program_application_id
1715 , program_id
1716 , program_update_date )
1717 VALUES
1718 ( p_wip_entity_id
1719 , p_res_usage_tbl(cnt).operation_seq_num
1720 , p_res_usage_tbl(cnt).resource_seq_num
1721 , p_organization_id
1722 , p_res_usage_tbl(cnt).start_date
1723 , p_res_usage_tbl(cnt).completion_date
1724 , p_res_usage_tbl(cnt).assigned_units
1725 , SYSDATE
1726 , FND_GLOBAL.user_id
1727 , SYSDATE
1728 , FND_GLOBAL.user_id
1729 , FND_GLOBAL.login_id
1730 , l_request_id
1731 , l_program_application_id
1732 , l_program_id
1733 , l_program_update_date );
1734 END LOOP;
1735
1736 END IF;
1737
1738 SELECT operation_seq_num ,
1739 resource_seq_num ,
1740 instance_id ,
1741 start_date ,
1742 completion_date
1743 BULK COLLECT INTO
1744 l_WipOperation_tbl,
1745 l_WipOperResource_tbl,
1746 l_WipOperResInst_tbl,
1747 l_WipOperResInstSt_tbl,
1748 l_WipOperResInstEnd_tbl
1749 FROM WIP_OP_RESOURCE_INSTANCES
1750 WHERE wip_entity_id = p_wip_entity_id
1751 AND organization_id = p_organization_id;
1752
1753 IF l_WipOperResInst_tbl.COUNT > 0 THEN
1754 FOR mm in l_WipOperResInst_tbl.FIRST..l_WipOperResInst_tbl.LAST LOOP
1755
1756 INSERT INTO WIP_OPERATION_RESOURCE_USAGE
1757 ( wip_entity_id
1758 , operation_seq_num
1759 , resource_seq_num
1760 , organization_id
1761 , start_date
1762 , completion_date
1763 , assigned_units
1764 , instance_id
1765 , last_update_date
1766 , last_updated_by
1767 , creation_date
1768 , created_by
1769 , last_update_login
1770 , request_id
1771 , program_application_id
1772 , program_id
1773 , program_update_date )
1774 SELECT
1775 wip_entity_id
1776 , operation_seq_num
1777 , resource_seq_num
1778 , organization_id
1779 , start_date
1780 , completion_date
1781 , assigned_units
1782 , l_WipOperResInst_tbl(mm)
1783 , last_update_date
1784 , last_updated_by
1785 , creation_date
1786 , created_by
1787 , last_update_login
1788 , request_id
1789 , program_application_id
1790 , program_id
1791 , program_update_date
1792 FROM WIP_OPERATION_RESOURCE_USAGE
1793 WHERE wip_entity_id = p_wip_entity_id
1794 AND organization_id = p_organization_id
1795 AND operation_seq_num = l_WipOperation_tbl(mm)
1796 AND resource_seq_num = l_WipOperResource_tbl(mm)
1797 AND instance_id IS NULL;
1798
1799 END LOOP;
1800 END IF;
1801
1802
1803 -- Find the min start date and max end date of all
1804 -- constrained children for this parent
1805
1806 -- find the list of constrained children
1807 IF NOT l_constrained_children%ISOPEN THEN
1808 OPEN l_constrained_children FOR
1809 select * from
1810 wip_sched_relationships
1811 where relationship_type = 1
1812 and parent_object_id = p_wip_entity_id
1813 and parent_object_type_id = 1;
1814 END IF;
1815
1816 LOOP FETCH l_constrained_children into
1817 l_relationship_record;
1818 IF l_relationship_record.parent_object_id is not null then
1819
1820 select scheduled_start_date,scheduled_completion_date,status_type,date_completed
1821 into l_start_date,l_compl_date,l_status_type,l_date_completed
1822 from wip_discrete_jobs
1823 where wip_entity_id = l_relationship_record.child_object_id
1824 and organization_id = p_organization_id;
1825
1826 --do not consider child workorders which are cancelled or [closed and date_completed is null](closed from cancelled status)
1827 IF NOT(
1828 l_status_type = 7
1829 OR ((l_status_type IN (12,14,15)) AND (l_date_completed IS NULL))
1830 ) THEN
1831 IF l_min_date is null OR
1832 l_min_date > l_start_date THEN
1833 l_min_date := l_start_date;
1834 END IF;
1835
1836
1837 IF l_max_date is null OR
1838 l_max_date < l_compl_date THEN
1839 l_max_date := l_compl_date;
1840 END IF;
1841 END IF;
1842
1843 END IF;
1844
1845 EXIT WHEN l_constrained_children%NOTFOUND;
1846 END LOOP;
1847
1848 CLOSE l_constrained_children;
1849
1850
1851 select scheduled_start_date, scheduled_completion_date
1852 into l_wo_start_date, l_wo_end_date
1853 from wip_discrete_jobs
1854 where wip_entity_id = p_wip_entity_id
1855 and organization_id = p_organization_id;
1856
1857
1858 if l_wo_start_date > nvl(l_min_date,l_wo_start_date+1) then
1859 update wip_discrete_jobs set
1860 scheduled_start_date = l_min_date
1861 where wip_entity_id = p_wip_entity_id
1862 and organization_id = p_organization_id;
1863 end if;
1864 if l_wo_end_date < nvl(l_max_date,l_wo_end_date -1) then
1865 update wip_discrete_jobs set
1866 scheduled_completion_date = l_max_date
1867 where wip_entity_id = p_wip_entity_id
1868 and organization_id = p_organization_id;
1869
1870 end if;
1871
1872 -- bug no 3444091
1873 select scheduled_start_date, scheduled_completion_date
1874 into l_wo_start_date, l_wo_end_date
1875 from wip_discrete_jobs
1876 where wip_entity_id = p_wip_entity_id
1877 and organization_id = p_organization_id;
1878
1879 if l_wo_start_date > l_wo_end_date then
1880 x_return_status := fnd_api.g_ret_sts_error;
1881 fnd_message.set_name('EAM','EAM_WO_DT_ERR');
1882 fnd_message.set_token('RESNO', p_wip_entity_id);
1883 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Negative workorder duration') ; END IF ;
1884 return;
1885 end if;
1886
1887 --dbms_output.put_line('Inside Schedule WO, At the end');
1888
1889 --########## Commented out
1890
1891
1892 x_return_status := FND_API.G_RET_STS_SUCCESS;
1893
1894 EXCEPTION
1895 WHEN INVALID_PARAM_EXC THEN
1896 ROLLBACK TO SCHEDULE_WO;
1897 x_return_status := fnd_api.g_ret_sts_error;
1898 x_error_message := ' EAM_RTG_GET_INFO : Statement - '||l_stmt_num||' Invalid parameter - '||l_error_msg;
1899 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Invalid parameter exception') ; END IF ;
1900 WHEN NO_DATA_FOUND THEN
1901 ROLLBACK TO SCHEDULE_WO;
1902 x_return_status := fnd_api.g_ret_sts_error;
1903 x_error_message := ' EAM_RTG_GET_INFO : Statement - '||l_stmt_num||'No Calendar associated in Org parameters';
1904 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('No calendar associated in Org parameters') ; END IF ;
1905 WHEN OTHERS THEN
1906 ROLLBACK TO SCHEDULE_WO;
1907 x_return_status := fnd_api.g_ret_sts_unexp_error;
1908 x_error_message := ' EAM_RTG_GET_INFO : Statement - '||l_stmt_num||' Error Message - '||SQLERRM;
1909 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Enters when others exception') ; END IF ;
1910 END;
1911
1912
1913
1914 END EAM_WO_SCHEDULE_PVT;