[Home] [Help]
PACKAGE BODY: APPS.WIP_INFRESSCHED_GRP
Source
1 package body wip_infResSched_grp as
2 /* $Header: wipinrsb.pls 120.2.12010000.2 2008/09/10 00:33:10 ntangjee ship $ */
3
4 --package constants
5 g_dateCursorLen CONSTANT NUMBER := 10;--must be greater than or equal to 1
6 g_precision CONSTANT NUMBER := 6;
7
8 g_forward CONSTANT NUMBER := 0;
9 g_backward CONSTANT NUMBER := 1;
10
11 g_logDateFmt CONSTANT VARCHAR2(30) := 'HH24:MI:SS MM/DD/YYYY';
12 --private types
13 type op_rec_t is record(startDate date,
14 endDate date,
15 priorsExist boolean,
16 nextsExist boolean,
17 resStartIdx number,
18 resEndIdx number);
19
20 type op_tbl_t is table of op_rec_t index by binary_integer;
21
22 /* fix bug 7027946 */
23 type shift_recTbl_t is record(shiftNum num_tbl_t,
24 startDate date_tbl_t,
25 endDate date_tbl_t);
26 /* end of fix bug 7027946 */
27
28 procedure buildOpStructure(p_resTbls in op_res_rectbl_t,
29 p_anchorDate in DATE,
30 x_opTbl out nocopy op_tbl_t);
31
32 procedure findMdPntRes(p_resTbls IN OP_RES_RECTBL_T,
33 p_opSeqNum NUMBER,
34 p_resSeqNum NUMBER,
35 p_isMdPntFwd boolean,
36 x_midPntFwdIdx OUT NOCOPY NUMBER,
37 x_midPntBkwdIdx OUT NOCOPY NUMBER);
38
39 --schedules prior resources when forward scheduling, or resource is
40 --on or after the midpoint op when midpoint scheduling
41 procedure schedulePriorResources(p_orgID IN NUMBER,
42 p_repLineID NUMBER,
43 p_opTbl in op_tbl_t,
44 x_resTbls IN OUT NOCOPY OP_RES_RECTBL_T,
45 x_returnStatus OUT NOCOPY VARCHAR2);
46
47 --schedules next resources when backward scheduling, or resource is
48 --on or before the midpoint op when midpoint scheduling
49 procedure scheduleNextResources(p_orgID IN NUMBER,
50 p_repLineID NUMBER,
51 p_opTbl in op_tbl_t,
52 x_resTbls IN OUT NOCOPY OP_RES_RECTBL_T,
53 x_returnStatus OUT NOCOPY VARCHAR2);
54
55
56
57 --schedules 'no' resources
58 procedure scheduleNoResources(p_anchorDate IN DATE,
59 x_resTbls IN OUT NOCOPY OP_RES_RECTBL_T,
60 x_returnStatus OUT NOCOPY VARCHAR2);
61
62 --when forward scheduling, this function will reschedule the entire job
63 --if one or more prior resources are initially scheduled to start before
64 --the start date passed in.
65 procedure resolvePriorExceptions(p_orgID IN NUMBER,
66 p_repLineID IN NUMBER,
67 p_startDate IN DATE,
68 x_resTbls IN OUT NOCOPY OP_RES_RECTBL_T,
69 x_returnStatus OUT NOCOPY VARCHAR2);
70
71 /* fix bug 7027946 */
72 procedure capacityExceptions(p_resID IN NUMBER,
73 p_deptID IN NUMBER,
74 p_orgID IN NUMBER,
75 x_shifts IN OUT NOCOPY shift_recTbl_t,
76 x_returnStatus OUT NOCOPY VARCHAR2);
77 /* end of fix bug 7027946 */
78
79 --when backward scheduling, this function will reschedule the entire job
80 --if one or more next resources are initially scheduled to end after the
81 --end date passed in.
82 procedure resolveNextExceptions(p_orgID IN NUMBER,
83 p_repLineID IN NUMBER,
84 p_endDate IN DATE,
85 x_resTbls IN OUT NOCOPY OP_RES_RECTBL_T,
86 x_returnStatus OUT NOCOPY VARCHAR2);
87
88 procedure forwardSchedule(p_orgID in number,
89 p_repLineID in NUMBER := null,
90 p_startDate in DATE,
91 p_range in num_tbl_t,
92 p_schedFlag in number,
93 x_resTbls in out NOCOPY OP_RES_RECTBL_T,
94 x_returnStatus OUT NOCOPY VARCHAR2);
95
96 procedure backwardSchedule(p_orgID in number,
97 p_repLineID in NUMBER := null,
98 p_endDate in DATE,
99 p_range in num_tbl_t,
100 p_schedFlag in number,
101 x_resTbls in out NOCOPY OP_RES_RECTBL_T,
102 x_returnStatus OUT NOCOPY VARCHAR2);
103
104
105 --removes priors from simultaneous batch
106 --returns last index of batch
107 function cleanBatch(p_startIdx NUMBER,
108 x_resTbls IN OUT NOCOPY op_res_rectbl_t) return number is
109 begin
110 for i in p_startIdx..x_resTbls.resID.count loop
111 if(x_resTbls.opSeqNum(i) = x_resTbls.opSeqNum(p_startIdx) and
112 x_resTbls.schedSeqNum(i) = x_resTbls.schedSeqNum(p_startIdx)) then
113 if(x_resTbls.schedFlag(i) = wip_constants.sched_prior) then
114 x_resTbls.schedFlag(i) := wip_constants.sched_yes;
115 end if;
116 else
117 return i;
118 end if;
119 end loop;
120 return x_resTbls.resID.count;
121 end cleanBatch;
122
123 --checks if priors co-exist with other schedule methods in simultaneous batch
124 --if so, it changes the priors to scheduled yes.
125 procedure removePriorsFromBatch(x_resTbls IN OUT NOCOPY op_res_rectbl_t) is
126 i number := 2;
127 l_curOp NUMBER := x_resTbls.opSeqNum(1);
128 l_curSch NUMBER := x_resTbls.schedSeqNum(1);
129 l_priorExists boolean := x_resTbls.schedFlag(1) = wip_constants.sched_prior;
130 l_otherExists boolean := x_resTbls.schedFlag(1) in (wip_constants.sched_yes, wip_constants.sched_next);
131 l_startIdx NUMBER := 1;
132 l_logLevel NUMBER := fnd_log.g_current_runtime_level;
133 l_retStatus VARCHAR2(1);
134 l_params wip_logger.param_tbl_t;
135 begin
136 if(l_logLevel <= wip_constants.trace_logging) then
137 wip_logger.entryPoint(p_procName => 'wip_infResSched_grp.removePriorsFromBatch',
138 p_params => l_params,
139 x_returnStatus => l_retStatus);
140 end if;
141 while(i <= x_resTbls.resID.count) loop
142 --in same batch as the previous res
143 if(x_resTbls.schedSeqNum(i) = l_curSch and
144 x_resTbls.opSeqNum(i) = l_curOp) then
145 l_priorExists := l_priorExists or x_resTbls.schedFlag(i) = wip_constants.sched_prior;
146 l_otherExists := l_otherExists or x_resTbls.schedFlag(i) in (wip_constants.sched_yes, wip_constants.sched_next);
147 if(l_priorExists and l_otherExists) then
148 i := cleanBatch(p_startIdx => l_startIdx, x_resTbls => x_resTbls);
149 end if;
150
151 --new batch
152 else
153 l_curOp := x_resTbls.opSeqNum(i);
154 l_curSch := x_resTbls.schedSeqNum(i);
155 l_startIdx := i;
156 l_priorExists := x_resTbls.schedFlag(i) = wip_constants.sched_prior;
157 l_otherExists := x_resTbls.schedFlag(i) in (wip_constants.sched_yes, wip_constants.sched_next);
158 end if;
159 i := i + 1;
160 end loop;
161 if (l_logLevel <= wip_constants.trace_logging) then
162 wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.removePriorsFromBatch',
163 p_procReturnStatus => null,
164 p_msg => 'success',
165 x_returnStatus => l_retStatus);
166 end if;
167 end removePriorsFromBatch;
168
169 procedure removePriors(x_resTbls IN OUT NOCOPY OP_RES_RECTBL_T) is
170 l_curOp NUMBER:= x_resTbls.opSeqNum(1);
171 l_nonPriorExists boolean := false;
172 l_logLevel NUMBER := fnd_log.g_current_runtime_level;
173 l_retStatus VARCHAR2(1);
174 l_params wip_logger.param_tbl_t;
175 begin
176 if(l_logLevel <= wip_constants.trace_logging) then
177 wip_logger.entryPoint(p_procName => 'wip_infResSched_grp.removePriors',
178 p_params => l_params,
179 x_returnStatus => l_retStatus);
180 end if;
181 for i in 1..x_resTbls.resID.count loop
182 if(l_curOp = x_resTbls.opSeqNum(i)) then
183 if(l_nonPriorExists and x_resTbls.schedFlag(i) = wip_constants.sched_prior) then
184 x_resTbls.schedFlag(i) := wip_constants.sched_yes;
185 end if;
186 l_nonPriorExists := l_nonPriorExists or (x_resTbls.schedFlag(i) not in (wip_constants.sched_prior, wip_constants.sched_no));
187 else
188 l_curOp := x_resTbls.opSeqNum(i);
189 l_nonPriorExists := x_resTbls.schedFlag(i) not in (wip_constants.sched_prior, wip_constants.sched_no);
190 end if;
191 end loop;
192 if (l_logLevel <= wip_constants.trace_logging) then
193 wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.removePriors',
194 p_procReturnStatus => null,
195 p_msg => 'success',
196 x_returnStatus => l_retStatus);
197 end if;
198 end removePriors;
199
200 procedure removeNexts(x_resTbls IN OUT NOCOPY OP_RES_RECTBL_T) is
201 l_curOp NUMBER:= x_resTbls.opSeqNum(x_resTbls.resID.count);
202 l_nonNextExists boolean := false;
203 l_logLevel NUMBER := fnd_log.g_current_runtime_level;
204 l_retStatus VARCHAR2(1);
205 l_params wip_logger.param_tbl_t;
206 begin
207 if(l_logLevel <= wip_constants.trace_logging) then
208 wip_logger.entryPoint(p_procName => 'wip_infResSched_grp.removeNexts',
209 p_params => l_params,
210 x_returnStatus => l_retStatus);
211 end if;
212 for i in reverse 1..x_resTbls.resID.count loop
213 if(l_curOp = x_resTbls.opSeqNum(i)) then
214 if(l_nonNextExists and x_resTbls.schedFlag(i) = wip_constants.sched_next) then
215 x_resTbls.schedFlag(i) := wip_constants.sched_yes;
216 end if;
217 l_nonNextExists := l_nonNextExists or
218 (x_resTbls.schedFlag(i) not in (wip_constants.sched_next, wip_constants.sched_no));
219 else
220 l_curOp := x_resTbls.opSeqNum(i);
221 l_nonNextExists := x_resTbls.schedFlag(i) not in (wip_constants.sched_no, wip_constants.sched_next);
222 end if;
223 end loop;
224 if (l_logLevel <= wip_constants.trace_logging) then
225 wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.removeNexts',
226 p_procReturnStatus => null,
227 p_msg => 'success',
228 x_returnStatus => l_retStatus);
229 end if;
230 end removeNexts;
231
232 procedure verifyResources(x_resTbls IN OUT NOCOPY OP_RES_RECTBL_T) is
233
234 l_logLevel NUMBER := fnd_log.g_current_runtime_level;
235 l_retStatus VARCHAR2(1);
236 l_params wip_logger.param_tbl_t;
237 begin
238 if(l_logLevel <= wip_constants.trace_logging) then
239 wip_logger.entryPoint(p_procName => 'wip_infResSched_grp.verifyResources',
240 p_params => l_params,
241 x_returnStatus => l_retStatus);
242 end if;
243
244 --changes schedule method of prior resources to yes if
245 -- + they are in the first op <= can't do this b/c of midpoint scheduling
246 -- + other resources with a different schedule type precede them in the operation
247 removePriors(x_resTbls => x_resTbls);
248
249 --changes schedule method of next resources to yes if
250 -- + other resources with a different schedule type are after them in the operation
251 removeNexts(x_resTbls => x_resTbls);
252
253 --if a prior resource is in a simultaneous batch that contains other resources
254 --with different scheduling methods simply treat them as
255 --scheduled ("yes") resources as the next/prior goals cannot be met, i.e. no
256 --overlap with the next/previous operation can be achieved.
257 --this shouldn't be done for next resources as they can still complete after
258 --the next operation starts (batched resources must start at the same time but
259 --can complete at different times).
260
261 --changes schedule method of prior resources to yes if
262 -- + they are in a simultaneous batch with yes or next resources.
263 removePriorsFromBatch(x_resTbls => x_resTbls);
264 if (l_logLevel <= wip_constants.trace_logging) then
265 wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.verifyResources',
266 p_procReturnStatus => null,
267 p_msg => 'success',
268 x_returnStatus => l_retStatus);
269 end if;
270 end verifyResources;
271
272 procedure dumpOps(p_opTbl in op_tbl_t) is
273 l_logLevel NUMBER := fnd_log.g_current_runtime_level;
274 l_retStatus VARCHAR2(1);
275 l_params wip_logger.param_tbl_t;
276 begin
277 if(l_logLevel <= wip_constants.trace_logging) then
278 wip_logger.entryPoint(p_procName => 'wip_infResSched_grp.dumpOps',
279 p_params => l_params,
280 x_returnStatus => l_retStatus);
281 end if;
282 if(l_logLevel <= wip_constants.full_logging) then
283 for i in 1..p_opTbl.count loop
284 wip_logger.log('op:' || i, l_retStatus);
285 wip_logger.log('startDate:' || to_char(p_opTbl(i).startDate, g_logDateFmt), l_retStatus);
286 wip_logger.log('endDate:' || to_char(p_opTbl(i).endDate, g_logDateFmt), l_retStatus);
287 if(p_opTbl(i).priorsExist) then
288 wip_logger.log('priorsExist:true', l_retStatus);
289 else
290 wip_logger.log('priorsExist:false', l_retStatus);
291 end if;
292 if(p_opTbl(i).nextsExist) then
293 wip_logger.log('nextsExist:true', l_retStatus);
294 else
295 wip_logger.log('nextsExist:false', l_retStatus);
296 end if;
297 wip_logger.log('resRange:' || p_opTbl(i).resStartIdx || '-' || p_opTbl(i).resEndIdx, l_retStatus);
298 end loop;
299 end if;
300 if (l_logLevel <= wip_constants.trace_logging) then
301 wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.dumpOps',
302 p_procReturnStatus => null,
303 p_msg => 'success',
304 x_returnStatus => l_retStatus);
305 end if;
306 end dumpOps;
307
308 procedure buildOpStructure(p_resTbls in op_res_rectbl_t,
309 p_anchorDate in Date,
310 x_opTbl out nocopy op_tbl_t) is
311 l_opSeqNum NUMBER := p_resTbls.opSeqNum(1);
312 l_startRange NUMBER := 1;
313 l_endRange NUMBER := 1;
314 j number := 1;
315 l_firstYesOpIdx NUMBER;
316 l_logLevel NUMBER := fnd_log.g_current_runtime_level;
317 l_retStatus VARCHAR2(1);
318 l_params wip_logger.param_tbl_t;
319 begin
320 if(l_logLevel <= wip_constants.trace_logging) then
321 l_params(1).paramName := 'p_anchorDate';
322 l_params(1).paramValue := to_char(p_anchorDate, g_logDateFmt);
323 wip_logger.entryPoint(p_procName => 'wip_infResSched_grp.buildOpStructure',
324 p_params => l_params,
325 x_returnStatus => l_retStatus);
326 end if;
327
328 --initialize op structure
329 x_opTbl(1).resStartIdx := 1;
330 x_opTbl(1).resEndIdx := null;
331
332 for i in 1..p_resTbls.resID.count loop
333 if(l_opSeqNum <> p_resTbls.opSeqNum(i)) then
334
335 if(l_logLevel <= wip_constants.full_logging) then
336 wip_logger.log('new op at resource ' || i, l_retStatus);
337 end if;
338
339 x_opTbl(j).resEndIdx := i - 1;
340 j := j + 1;
341 -- if(j > 1) then
342 -- x_opTbl(j).startDate := x_opTbl(j-1).startDate;
343 -- x_opTbl(j).endDate := x_opTbl(j-1).endDate;
344 -- end if;
345 x_opTbl(j).resStartIdx := i;
346 x_opTbl(j).resEndIdx := null;
347
348 l_opSeqNum := p_resTbls.opSeqNum(i);
349 end if;
350 x_opTbl(j).priorsExist := x_opTbl(j).priorsExist or p_resTbls.schedFlag(i) = wip_constants.sched_prior;
351 x_opTbl(j).nextsExist := x_opTbl(j).nextsExist or p_resTbls.schedFlag(i) = wip_constants.sched_next;
352 if(p_resTbls.schedFlag(i) = wip_constants.sched_yes) then
353 if(l_firstYesOpIdx is null) then
354 l_firstYesOpIdx := j;
355 end if;
356 x_opTbl(j).startDate := least(p_resTbls.startDate(i), nvl(x_opTbl(j).startDate, p_resTbls.startDate(i)));
357 x_opTbl(j).endDate := greatest(p_resTbls.endDate(i), nvl(x_opTbl(j).endDate, p_resTbls.endDate(i)));
358 if(l_logLevel <= wip_constants.full_logging) then
359 wip_logger.log('op ' || j || '''s start date:' || to_char(x_opTbl(j).startDate, g_logDateFmt), l_retStatus);
360 wip_logger.log('op ' || j || '''s end date:' || to_char(x_opTbl(j).endDate, g_logDateFmt), l_retStatus);
361 end if;
362
363 end if;
364 end loop;
365 --for the last op, set the end resource to the last one in the structure
366 x_opTbl(x_opTbl.count).resEndIdx := p_resTbls.resID.count;
367
368 for i in 1..x_opTbl.count loop
369 if(x_opTbl(i).startDate is null) then
370 if(i = 1) then
371 if(l_firstYesOpIdx is null) then
372 x_opTbl(i).startDate := p_anchorDate;
373 x_opTbl(i).endDate := p_anchorDate;
374 else
375 x_opTbl(i).startDate := x_opTbl(l_firstYesOpIdx).startDate;
376 x_opTbl(i).endDate := x_opTbl(l_firstYesOpIdx).startDate;
377 end if;
378 else
379 x_opTbl(i).startDate := x_opTbl(i-1).endDate;
380 x_opTbl(i).endDate := x_opTbl(i-1).endDate;
381 end if;
382 end if;
383 end loop;
384
385 if (l_logLevel <= wip_constants.full_logging) then
386 dumpOps(x_opTbl);
387 end if;
388
389 if (l_logLevel <= wip_constants.trace_logging) then
390 wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.buildOpStructure',
391 p_procReturnStatus => null,
392 p_msg => 'success',
393 x_returnStatus => l_retStatus);
394 end if;
395 end buildOpStructure;
396
397 procedure findMdPntRes(p_resTbls IN OP_RES_RECTBL_T,
398 p_opSeqNum NUMBER,
399 p_resSeqNum NUMBER,
400 p_isMdPntFwd boolean,
401 x_midPntFwdIdx OUT NOCOPY NUMBER,
402 x_midPntBkwdIdx OUT NOCOPY NUMBER) is
403 l_retStatus VARCHAR2(1);
404 l_params wip_logger.param_tbl_t;
405 l_logLevel NUMBER := fnd_log.g_current_runtime_level;
406 l_foundMidPntOp boolean := false;
407 begin
408 if(l_logLevel <= wip_constants.trace_logging) then
409 l_params(1).paramName := 'p_opSeqNum';
410 l_params(1).paramValue := p_opSeqNum;
411 l_params(2).paramName := 'p_resSeqNum';
412 l_params(2).paramValue := p_resSeqNum;
413 l_params(3).paramName := 'p_isMdPntFwd';
414 if(p_isMdPntFwd) then l_params(3).paramValue := 'true';
415 else l_params(3).paramValue := 'false'; end if;
416
417 wip_logger.entryPoint(p_procName => 'wip_infResSched_grp.findMdPntRes',
418 p_params => l_params,
419 x_returnStatus => l_retStatus);
420 end if;
421
422 --find the midpoint resource
423 for i in 1..p_resTbls.resID.count loop
424 if(p_opSeqNum = p_resTbls.opSeqNum(i)) then --op matches
425 l_foundMidPntOp := true;
426 if(l_logLevel <= wip_constants.full_logging) then
427 wip_logger.log('op seq matches res ' || i, l_retStatus);
428 end if;
429
430 if(p_resSeqNum is not null) then
431 if(p_resSeqNum = p_resTbls.resSeqNum(i)) then --res seq matches
432
433 if(l_logLevel <= wip_constants.full_logging) then
434 wip_logger.log('res seq matches res ' || i, l_retStatus);
435 end if;
436
437 if(p_isMdPntFwd) then
438 -- bug 3423612: If there are simultaneous resources, we have to
439 -- set the index to the first res in the group (last res for
440 -- backwards scheduling).
441 for j in reverse 1..i loop
442 if (p_resTbls.opSeqNum(j) = p_resTbls.opSeqNum(i) and
443 nvl(p_resTbls.schedSeqNum(j), p_resTbls.resSeqNum(j)) = nvl(p_resTbls.schedSeqNum(i), p_resTbls.resSeqNum(i))) then
444 x_midPntFwdIdx := j;
445 if(j <> 1) then
446 x_midPntBkwdIdx := j - 1;
447 else
448 x_midPntBkwdIdx := null;
449 end if;
450 else
451 exit;
452 end if;
453 end loop;
454 else
455 for j in i..p_resTbls.resID.count loop
456 if (p_resTbls.opSeqNum(j) = p_resTbls.opSeqNum(i) and
457 nvl(p_resTbls.schedSeqNum(j), p_resTbls.resSeqNum(j)) = nvl(p_resTbls.schedSeqNum(i), p_resTbls.resSeqNum(i))) then
458 x_midPntBkwdIdx := j;
459 if(j <> p_resTbls.resID.count) then
460 x_midPntFwdIdx := j + 1;
461 else
462 x_midPntFwdIdx := null;
463 end if;
464 else
465 exit;
466 end if;
467 end loop;
468 end if; --start date...
469 exit; --res seq matched, exit loop
470 end if;
471 else --resource seq was not populated. use op start or end res
472
473 if(l_logLevel <= wip_constants.full_logging) then
474 wip_logger.log('res seq is null', l_retStatus);
475 end if;
476
477 if(p_isMdPntFwd) then --forward scheduling midpoint op
478 x_midPntFwdIdx := i;
479 if(i <> 1) then
480 x_midPntBkwdIdx := i - 1;
481 end if;
482 exit;
483 end if;
484 end if;
485 end if;
486
487 --if backward scheduling the midpoint op and the first op of the next op was found...
488 if(l_foundMidPntOp and
489 not(p_isMdPntFwd) and
490 p_resTbls.opSeqNum(i) <> p_opSeqNum) then
491 if(l_logLevel <= wip_constants.full_logging) then
492 wip_logger.log('first res past midpoint at idx:' || i, l_retStatus);
493 end if;
494 x_midPntBkwdIdx := i - 1;
495 x_midPntFwdIdx := i;
496 exit;
497 end if;
498
499 if(p_resTbls.resID.count = i) then
500 if(l_logLevel <= wip_constants.full_logging) then
501 wip_logger.log('backward scheduling everything', l_retStatus);
502 end if;
503
504 x_midPntBkwdIdx := i;
505 exit;
506 end if;
507 end loop;
508 if(l_logLevel <= wip_constants.trace_logging) then
509 wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.findMdPntRes',
510 p_procReturnStatus => null,
511 p_msg => 'fwdIdx:' || x_midPntFwdIdx || '; bkwdIdx:' || x_midPntBkwdIdx,
512 x_returnStatus => l_retStatus);
513 end if;
514 end findMdPntRes;
515
516 procedure schedule(p_orgID IN NUMBER,
517 p_repLineID NUMBER := null,
518 p_startDate DATE := null,
519 p_endDate DATE := null,
520 p_opSeqNum NUMBER := null,
521 p_resSeqNum NUMBER := null,
522 p_endDebug IN VARCHAR2 := null,
523 x_resTbls IN OUT NOCOPY OP_RES_RECTBL_T,
524 x_returnStatus OUT NOCOPY VARCHAR2) is
525 l_logLevel NUMBER := fnd_log.g_current_runtime_level;
526 l_params wip_logger.param_tbl_t;
527 l_retStatus VARCHAR2(1);
528
529 l_fwdStIdx NUMBER;--resource to start forward scheduling from
530 l_bkwdEndIdx NUMBER;--resource to backward schedule to
531 l_startDate DATE;
532 l_endDate DATE;
533 l_range num_tbl_t := num_tbl_t(null,null);
534 l_opTbl op_tbl_t;
535 l_errMsg VARCHAR2(2000);
536 begin
537 if(l_logLevel <= wip_constants.trace_logging) then
538 l_params(1).paramName := 'p_orgID';
539 l_params(1).paramValue := p_orgID;
540 l_params(2).paramName := 'p_repLineID';
541 l_params(2).paramValue := p_repLineID;
542 l_params(3).paramName := 'p_startDate';
543 l_params(3).paramValue := to_char(p_startDate, g_logDateFmt);
544 l_params(4).paramName := 'p_endDate';
545 l_params(4).paramValue := to_char(p_endDate, g_logDateFmt);
546 l_params(5).paramName := 'p_opSeqNum';
547 l_params(5).paramValue := p_opSeqNum;
548 wip_logger.entryPoint(p_procName => 'wip_infResSched_grp.schedule',
549 p_params => l_params,
550 x_returnStatus => x_returnStatus);
551 if(x_returnStatus <> fnd_api.g_ret_sts_success) then
552 raise fnd_api.g_exc_unexpected_error;
553 end if;
554 end if;
555 x_returnStatus := fnd_api.g_ret_sts_success;
556
557 if(x_resTbls.resID is null or x_resTbls.resID.count < 1) then
558 if (l_logLevel <= wip_constants.trace_logging) then
559 wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.schedule',
560 p_procReturnStatus => x_returnStatus,
561 p_msg => 'no resources to schedule!',
562 x_returnStatus => l_retStatus);
563 end if;
564 return;
565 end if;
566
570
567 --initialize the date tables
568 x_resTbls.startDate := date_tbl_t();
569 x_resTbls.endDate := date_tbl_t();
571 x_resTbls.usgStartIdx := num_tbl_t();
572 x_resTbls.usgEndIdx := num_tbl_t();
573
574 x_resTbls.usgStartDate := date_tbl_t();
575 x_resTbls.usgEndDate := date_tbl_t();
576
577 x_resTbls.usgCumMinProcTime := num_tbl_t();
578
579 x_resTbls.usgStartIdx.extend(x_resTbls.resID.count);
580 x_resTbls.usgEndIdx.extend(x_resTbls.resID.count);
581
582 x_resTbls.startDate.extend(x_resTbls.resID.count);
583 x_resTbls.endDate.extend(x_resTbls.resID.count);
584 if(l_logLevel <= wip_constants.trace_logging) then
585 dumpResources(x_resTbls);
586 end if;
587
588 verifyResources(x_resTbls => x_resTbls);
589
590 if(l_logLevel <= wip_constants.trace_logging) then
591 dumpResources(x_resTbls);
592 end if;
593
594 --caller wants to either forward or backward schedule.
595 if(p_opSeqNum is null) then
596 --forward
597 if(p_startDate is not null) then
598 l_fwdStIdx := 1;
599 l_bkwdEndIdx := null;--this line isn''t necessary, but is included for clarity
600 l_startDate := p_startDate;
601 l_endDate := null;--this line isn''t necessary, but is included for clarity
602
603 if (l_logLevel <= wip_constants.trace_logging) then
604 wip_logger.log(p_msg => 'forward scheduling',
605 x_returnStatus => x_returnStatus);
606 end if;
607
608 --backward
609 else
610 l_fwdStIdx := null;--this line isn''t necessary, but is included for clarity
611 l_bkwdEndIdx := x_resTbls.resID.count;
612 l_startDate := null;--this line isn''t necessary, but is included for clarity
613 l_endDate := p_endDate;
614
615 if (l_logLevel <= wip_constants.trace_logging) then
616 wip_logger.log(p_msg => 'backward scheduling',
617 x_returnStatus => x_returnStatus);
618 end if;
619 end if;
620 else --midpoint scheduling
621 findMdPntRes(p_resTbls => x_resTbls,
622 p_opSeqNum => p_opSeqNum,
623 p_resSeqNum => p_resSeqNum,
624 p_isMdPntFwd => p_startDate is not null,
625 x_midPntFwdIdx => l_fwdStIdx,
626 x_midPntBkwdIdx => l_bkwdEndIdx);
627
628 if (l_logLevel <= wip_constants.trace_logging) then
629 wip_logger.log(p_msg => 'midpoint scheduling',
630 x_returnStatus => x_returnStatus);
631 end if;
632
633 if(p_startDate is not null) then
634 --forward schedule operation provided and those greater.
635 --backward schedule previous operations
636 l_startDate := p_startDate;
637 l_endDate := p_startDate;
638 else
639 --forward schedule operations greater than the one provided
640 --backward schedule op provided and the previous ones
641 l_startDate := p_endDate;
642 l_endDate := p_endDate;
643 end if;
644 end if;
645
646 if(l_fwdStIdx is not null) then
647 l_range(1) := l_fwdStIdx;
648 l_range(2) := x_resTbls.resID.count;
649
650 --forward schedule resources in range.
651 forwardSchedule(p_orgID => p_orgID,
652 p_repLineID => p_repLineID,
653 p_startDate => l_startDate,
654 p_range => l_range,
655 p_schedFlag => wip_constants.sched_yes,
656 x_resTbls => x_resTbls,
657 x_returnStatus => x_returnStatus);
658
659 if(x_returnStatus <> fnd_api.g_ret_sts_success) then
660 wip_logger.log('fwdSch failed', l_retStatus);
661 raise fnd_api.g_exc_unexpected_error;
662 end if;
663
664 if(l_logLevel <= wip_constants.full_logging) then
665 dumpResources(x_resTbls);
666 end if;
667 end if;
668
669 if(l_bkwdEndIdx is not null) then
670 l_range(1) := 1;
671 l_range(2) := l_bkwdEndIdx;
672
673 --backward schedule resources in range.
674 backwardSchedule(p_orgID => p_orgID,
675 p_repLineID => p_repLineID,
676 p_endDate => l_endDate,
677 p_range => l_range,
678 p_schedFlag => wip_constants.sched_yes,
679 x_resTbls => x_resTbls,
680 x_returnStatus => x_returnStatus);
681 if(x_returnStatus <> fnd_api.g_ret_sts_success) then
682 wip_logger.log('bkwdSch failed', l_retStatus);
683 raise fnd_api.g_exc_unexpected_error;
684 end if;
685 end if;
686
687 --build the operation structure
688 buildOpStructure(p_resTbls => x_resTbls,
689 p_anchorDate => nvl(p_startDate, p_endDate),
690 x_opTbl => l_opTbl);
691
692 --now schedule prior and next resources
693 schedulePriorResources(p_orgID => p_orgID,
694 p_repLineID => p_repLineID,
695 p_opTbl => l_opTbl,
696 x_resTbls => x_resTbls,
697 x_returnStatus => x_returnStatus);
701 raise fnd_api.g_exc_unexpected_error;
698
699 if(x_returnStatus <> fnd_api.g_ret_sts_success) then
700 wip_logger.log('schPriorRes failed', l_retStatus);
702 end if;
703
704
705 scheduleNextResources(p_orgID => p_orgID,
706 p_repLineID => p_repLineID,
707 p_opTbl => l_opTbl,
708 x_resTbls => x_resTbls,
709 x_returnStatus => x_returnStatus);
710
711 if(x_returnStatus <> fnd_api.g_ret_sts_success) then
712 wip_logger.log('schNextRes failed', l_retStatus);
713 raise fnd_api.g_exc_unexpected_error;
714 end if;
715
716 if(l_logLevel <= wip_constants.full_logging) then
717 dumpResources(x_resTbls);
718 end if;
719
720 --if forward scheduling...
721 if(p_opSeqNum is null and p_startDate is not null) then
722 resolvePriorExceptions(p_orgID => p_orgID,
723 p_repLineID => p_repLineID,
724 p_startDate => p_startDate,
725 x_resTbls => x_resTbls,
726 x_returnStatus => x_returnStatus);
727 if(x_returnStatus <> fnd_api.g_ret_sts_success) then
728 wip_logger.log('resolvePriorRes failed', l_retStatus);
729 raise fnd_api.g_exc_unexpected_error;
730 end if;
731 --if backward scheduling
732 elsif(p_opSeqNum is null and p_endDate is not null) then
733 resolveNextExceptions(p_orgID => p_orgID,
734 p_repLineID => p_repLineID,
735 p_endDate => p_endDate,
736 x_resTbls => x_resTbls,
737 x_returnStatus => x_returnStatus);
738 if(x_returnStatus <> fnd_api.g_ret_sts_success) then
739 wip_logger.log('resolveNextRes failed', l_retStatus);
740 raise fnd_api.g_exc_unexpected_error;
741 end if;
742 end if;
743
744
745 --assign dates to scheduled no resources
746 scheduleNoResources(p_anchorDate => nvl(p_startDate, p_endDate),
747 x_resTbls => x_resTbls,
748 x_returnStatus => x_returnStatus);
749
750
751
752 if(l_logLevel <= wip_constants.trace_logging) then
753 wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.schedule',
754 p_procReturnStatus => x_returnStatus,
755 p_msg => 'success',
756 x_returnStatus => l_retStatus);
757 if(fnd_api.to_boolean(nvl(p_endDebug, fnd_api.g_true))) then
758 wip_logger.cleanup(l_retStatus);
759 end if;
760 end if;
761 exception
762 when fnd_api.g_exc_unexpected_error then
763 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
764 if(l_logLevel <= wip_constants.trace_logging) then
765 wip_utilities.get_message_stack(p_msg => l_errMsg,
766 p_delete_stack => fnd_api.g_false);
767
768 wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.schedule',
769 p_procReturnStatus => x_returnStatus,
770 p_msg => 'failure: ' || l_errMsg,
771 x_returnStatus => l_retStatus);
772 if(fnd_api.to_boolean(nvl(p_endDebug, fnd_api.g_true))) then
773 wip_logger.cleanup(l_retStatus);
774 end if;
775 end if;
776 when others then
777 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
778 fnd_msg_pub.add_exc_msg(p_pkg_name => 'wip_infResSched_grp',
779 p_procedure_name => 'schedule',
780 p_error_text => SQLERRM);
781 if(l_logLevel <= wip_constants.trace_logging) then
782 wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.schedule',
783 p_procReturnStatus => x_returnStatus,
784 p_msg => 'unexpected error: ' || SQLERRM,
785 x_returnStatus => l_retStatus);
786 if(fnd_api.to_boolean(nvl(p_endDebug, fnd_api.g_true))) then
787 wip_logger.cleanup(l_retStatus);
788 end if;
789 end if;
790 end schedule;
791
792 function getNextResIdx(p_range in num_tbl_t,
793 p_schedFlag in number,
794 p_schedMethod in number,
795 p_resTbls in OP_RES_RECTBL_T,
796 x_idx in out nocopy number) return boolean is
797 l_retStatus VARCHAR2(1);
798 begin
799 if(p_schedMethod = g_forward) then
800 for j in nvl(x_idx+1, p_range(1))..p_range(2) loop
801 if(p_resTbls.schedFlag(j) = p_schedFlag) then
802 x_idx := j;
803 return true;
804 end if;
805 end loop;
806 end if;
807
808 if(p_schedMethod = g_backward) then
809 for j in reverse p_range(1)..nvl(x_idx-1,p_range(2)) loop
810 if(p_resTbls.schedFlag(j) = p_schedFlag) then
811 x_idx := j;
812 return true;
813 end if;
814 end loop;
815 end if;
816
817 return false;
818 end getNextResIdx;
819
823 function getStartDate(p_range in num_tbl_t,
820 --p_prevStartDate: The date from which the previous resource was scheduled from (not necessarily
821 -- the start date of the previous resource...no shift could have been defined on the exact time
822 -- the resource could have been scheduled from)
824 p_schedFlag in number,
825 p_resTbls in op_res_rectbl_t,
826 p_curIdx in number,
827 p_doneSchedBatch in boolean,
828 p_prevIdx in number) return date is
829 l_retStatus VARCHAR2(1);
830 l_params wip_logger.param_tbl_t;
831 i number;
832 l_maxEndDate date;
833 l_logLevel NUMBER := fnd_log.g_current_runtime_level;
834 l_bool boolean;
835 begin
836 if (l_logLevel <= wip_constants.trace_logging) then
837 l_params(1).paramName := 'p_range(1)';
838 l_params(1).paramValue := p_range(1);
839 l_params(2).paramName := 'p_range(2)';
840 l_params(2).paramValue := p_range(2);
841 l_params(3).paramName := 'p_schedFlag';
842 l_params(3).paramValue := p_schedFlag;
843 l_params(4).paramName := 'p_curIdx';
844 l_params(4).paramValue := p_curIdx;
845 l_params(5).paramName := 'p_doneSchedBatch';
846 if(p_doneSchedBatch) then l_params(5).paramValue := 'true';
847 else l_params(5).paramValue := 'false';
848 end if;
849 l_params(6).paramName := 'p_prevIdx';
850 l_params(6).paramValue := p_prevIdx;
851
852 wip_logger.entryPoint(p_procName => 'wip_infResSched_grp.getStartDate',
853 p_params => l_params,
854 x_returnStatus => l_retStatus);
855 end if;
856
857 --in case we just got done scheduling a batch of simultaneous resources, get the
858 --latest end date to use as the next resource's start date
859 i := p_curIdx;
860
861 if(p_doneSchedBatch) then
862 if(l_logLevel <= wip_constants.full_logging) then
863 wip_logger.log('done scheduling batch', l_retStatus);
864 end if;
865 while(getNextResIdx(p_range, p_schedFlag, g_backward, p_resTbls, i)) loop
866 if(l_logLevel <= wip_constants.full_logging) then
867 wip_logger.log('in loop', l_retStatus);
868 wip_logger.log('resID' || p_resTbls.resID(i), l_retStatus);
869 wip_logger.log('opSeq' || p_resTbls.opSeqNum(i), l_retStatus);
870 wip_logger.log('schSeq' || p_resTbls.schedSeqNum(i), l_retStatus);
871 wip_logger.log('idx' || i, l_retStatus);
872 end if;
873 if(p_resTbls.schedSeqNum(i) = p_resTbls.schedSeqNum(p_prevIdx) and
874 p_resTbls.opSeqNum(i) = p_resTbls.opSeqNum(p_prevIdx)) then
875 l_maxEndDate := greatest(nvl(l_maxEndDate, p_resTbls.endDate(i)), p_resTbls.endDate(i));
876 if(l_logLevel <= wip_constants.full_logging) then
877 wip_logger.log('resource in batch. endDate:' || to_char(p_resTbls.endDate(i), g_logDateFmt), l_retStatus);
878 end if;
879 else
880 if(l_logLevel <= wip_constants.full_logging) then
881 wip_logger.log('resource not in batch.', l_retStatus);
882 end if;
883 exit;
884 end if;
885 end loop;
886 else
887 l_bool := (getNextResIdx(p_range, p_schedFlag, g_backward, p_resTbls, i));
888 l_maxEndDate := p_resTbls.endDate(i);
889 end if;
890
891 if (l_logLevel <= wip_constants.trace_logging) then
892 wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.getStartDate',
893 p_procReturnStatus => to_char(l_maxEndDate),
894 p_msg => 'finished scheduling',
895 x_returnStatus => l_retStatus);
896 end if;
897 return l_maxEndDate;
898 end getStartDate;
899
900 procedure forwardSchResource(p_orgID in number,
901 p_startDate in date,
902 p_maxDate in date,
903 p_idx in number,
904 p_repLineID in number := null,
905 x_resTbls in out nocopy op_res_rectbl_t,
906 x_returnStatus out nocopy varchar2) is
907
908 cursor c_shiftTimes(v_resID NUMBER,
909 v_deptID NUMBER,
910 v_orgID NUMBER,
911 v_startDate DATE,
912 v_endDate DATE) is
913 select brs.shift_num,
914 bsd.shift_date + bst.from_time/86400,
915 bsd.shift_date + bst.to_time/86400
916 from bom_resource_shifts brs,
917 mtl_parameters mp,
918 bom_shift_dates bsd,
919 bom_shift_times bst,
920 bom_department_resources bdr
921 where bdr.department_id = v_deptID
922 and bdr.resource_id = v_resID
923 and brs.resource_id = bdr.resource_id
924 and brs.department_id = nvl(bdr.share_from_dept_id,bdr.department_id)
925 and mp.organization_id = v_orgID
926 and mp.calendar_code = bsd.calendar_code
927 and mp.calendar_exception_set_id = bsd.exception_set_id
928 and brs.shift_num = bsd.shift_num
929 and bsd.shift_date between v_startDate and v_endDate --don't incorporate time into this check as it slows the query
930 and bsd.seq_num is not null
934
931 and bst.shift_num = bsd.shift_num
932 and bst.calendar_code = bsd.calendar_code
933 order by bsd.shift_date, bst.from_time;
935 --for repetitive, ignore shifts and use the line's start and stop times. However, do
936 --respect the working days definition
937 cursor c_repTimes(v_repLineID NUMBER,
938 v_orgID NUMBER,
939 v_startDate DATE,
940 v_endDate DATE) is
941 select -1 shiftNum,
942 bcd.calendar_date + wl.start_time/86400,
943 bcd.calendar_date + wl.stop_time/86400
944 from mtl_parameters mp,
945 bom_calendar_dates bcd,
946 wip_lines wl
947 where mp.organization_id = v_orgID
948 and mp.calendar_code = bcd.calendar_code
949 and mp.calendar_exception_set_id = bcd.exception_set_id
950 and wl.line_id = v_repLineID
951 and bcd.seq_num is not null --working day
952 and bcd.calendar_date between v_startDate and v_endDate
953 order by bcd.calendar_date;
954
955 cursor c_24HrTimes(v_orgID NUMBER,
956 v_startDate DATE,
957 v_endDate DATE) is
958 select -1,
959 bcd.calendar_date,
960 bcd.calendar_date + 1
961 from mtl_parameters mp,
962 bom_calendar_dates bcd
963 where mp.organization_id = v_orgID
964 and mp.calendar_code = bcd.calendar_code
965 and mp.calendar_exception_set_id = bcd.exception_set_id
966 and bcd.calendar_date between v_startDate and v_endDate
967 and bcd.seq_num is not null
968 order by bcd.calendar_date;
969
970 --used to collect cursor records...
971 /* fix bug 7027946 */
972 /* type shift_recTbl_t is record(shiftNum num_tbl_t,
973 startDate date_tbl_t,
974 endDate date_tbl_t);
975 /* end of fix bug 7027946 */
976
977 l_shifts shift_recTbl_t;
978 l_logLevel NUMBER := fnd_log.g_current_runtime_level;
979 l_params wip_logger.param_tbl_t;
980 l_retStatus VARCHAR2(1);
981 l_resourceScheduled boolean := false;
982 l_cursorStartDate date := trunc(p_startDate) - 1;--subtract 1 to make sure to get wraparound shifts (start on prev day)
983 l_fromDate date;
984 l_shiftLen NUMBER;
985 l_remUsage NUMBER := x_resTbls.totalDaysUsg(p_idx);
986 l_usgIdx NUMBER;
987 l_startDate DATE;
988 l_prevProcTime NUMBER;
989 l_isFirstUsg boolean := true;
990 l_dummy NUMBER; /* Bug 5660475 */
991 begin
992 if (l_logLevel <= wip_constants.trace_logging) then
993 l_params(1).paramName := 'p_orgID';
994 l_params(1).paramValue := p_orgID;
995 l_params(2).paramName := 'p_startDate';
996 l_params(2).paramValue := to_char(p_startDate, g_logDateFmt);
997 l_params(3).paramName := 'p_maxDate';
998 l_params(3).paramValue := to_char(p_maxDate, g_logDateFmt);
999 l_params(4).paramName := 'p_idx';
1000 l_params(4).paramValue := p_idx;
1001 l_params(5).paramName := 'p_repLineID';
1002 l_params(5).paramValue := p_repLineID;
1003 wip_logger.entryPoint(p_procName => 'wip_infResSched_grp.forwardSchResource',
1004 p_params => l_params,
1005 x_returnStatus => l_retStatus);
1006 end if;
1007 x_returnStatus := fnd_api.g_ret_sts_success;
1008
1009 /* Fix for bug 5660475: If dealing with shift resource, first check if shifts are setup fine. */
1010 if( p_repLineID is null
1011 and x_resTbls.avail24Flag(p_idx) = wip_constants.no
1012 and x_resTbls.schedFlag(p_idx) <> wip_constants.sched_no) then
1013 wip_logger.log('This is a shift resource. Need to validate shift setup', l_retStatus);
1014 begin
1015 select 1
1016 into l_dummy
1017 from dual
1018 where exists (select 1
1019 from bom_resource_shifts brs,
1020 mtl_parameters mp,
1021 bom_shift_dates bsd,
1022 bom_shift_times bst,
1023 bom_department_resources bdr
1024 where bdr.department_id = x_resTbls.deptID(p_idx)
1025 and bdr.resource_id = x_resTbls.resID(p_idx)
1026 and brs.resource_id = bdr.resource_id
1027 and brs.department_id = nvl(bdr.share_from_dept_id,bdr.department_id)
1028 and mp.organization_id = p_orgID
1029 and mp.calendar_code = bsd.calendar_code
1030 and mp.calendar_exception_set_id = bsd.exception_set_id
1031 and brs.shift_num = bsd.shift_num
1032 and bsd.seq_num is not null
1033 and bst.shift_num = bsd.shift_num
1034 and bst.calendar_code = bsd.calendar_code);
1035 exception
1036 when NO_DATA_FOUND then
1037 wip_logger.log('Error: Missing shifts or shift times!', l_retStatus);
1038 fnd_message.set_name('WIP', 'WIP_SHIFT_RESOURCE');
1039 fnd_message.set_token('ENTITY1', x_resTbls.resSeqNum(p_idx));
1040 fnd_message.set_token('ENTITY2', x_resTbls.opSeqNum(p_idx));
1041 fnd_msg_pub.add;
1042 raise fnd_api.g_exc_unexpected_error;
1043 end;
1044 end if;
1045
1046 x_resTbls.usgStartIdx(p_idx) := null;
1050 if (l_logLevel <= wip_constants.full_logging) then
1047 x_resTbls.usgEndIdx(p_idx) := null;
1048 loop
1049 exit when l_resourceScheduled;
1051 wip_logger.log('cursor start date is' || to_char(l_cursorStartDate, g_logDateFmt), l_retStatus);
1052 wip_logger.log('cursor end date is' || to_char((l_cursorStartDate + g_dateCursorLen - 1/86400), g_logDateFmt), l_retStatus);
1053 end if;
1054
1055 --for v_endDate, subtract a second to avoid overlap between cursors.
1056 if(p_repLineID is not null) then
1057 if(l_logLevel <= wip_constants.full_logging) then
1058 wip_logger.log('scheduling repetitive', l_retStatus);
1059 end if;
1060 open c_repTimes(v_repLineID => p_repLineID,
1061 v_orgID => p_orgID,
1062 v_startDate => l_cursorStartDate,
1063 v_endDate => l_cursorStartDate + g_dateCursorLen - 1/86400);
1064 fetch c_repTimes
1065 bulk collect into l_shifts.shiftNum,
1066 l_shifts.startDate,
1067 l_shifts.endDate;
1068 close c_repTimes;
1069 elsif(x_resTbls.avail24Flag(p_idx) = wip_constants.yes) then
1070 if(l_logLevel <= wip_constants.full_logging) then
1071 wip_logger.log('scheduling 24HR resource', l_retStatus);
1072 end if;
1073 open c_24HrTimes(v_orgID => p_orgID,
1074 v_startDate => l_cursorStartDate,
1075 v_endDate => l_cursorStartDate + g_dateCursorLen - 1/86400);
1076 fetch c_24HrTimes
1077 bulk collect into l_shifts.shiftNum,
1078 l_shifts.startDate,
1079 l_shifts.endDate;
1080 close c_24HrTimes;
1081 else
1082 if(l_logLevel <= wip_constants.full_logging) then
1083 wip_logger.log('scheduling shift resource', l_retStatus);
1084 end if;
1085 open c_shiftTimes(v_resID => x_resTbls.resID(p_idx),
1086 v_deptID => x_resTbls.deptID(p_idx),
1087 v_orgID => p_orgID,
1088 v_startDate => l_cursorStartDate,
1089 v_endDate => l_cursorStartDate + g_dateCursorLen - 1/86400);
1090 fetch c_shiftTimes
1091 bulk collect into l_shifts.shiftNum,
1092 l_shifts.startDate,
1093 l_shifts.endDate;
1094
1095 if (l_shifts.shiftNum.count = 0 ) then
1096 /* Fix for bug 5660475: If shifts are not available in the date range,
1097 we should continue to search in the next date range, instead of erroring out. */
1098 wip_logger.log('No shifts found in this period.', l_retStatus);
1099 l_resourceScheduled := false;
1100 end if;
1101
1102 close c_shiftTimes;
1103
1104 /* fix bug 7027946 */
1105 capacityExceptions(p_resID => x_resTbls.resID(p_idx), -- adjust the capacity exception.
1106 p_deptID => x_resTbls.deptID(p_idx),
1107 p_orgID => p_orgID,
1108 x_shifts => l_shifts,
1109 x_returnStatus => x_returnStatus);
1110 /* end of fix bug 7027946 */
1111
1112 end if;
1113
1114
1115 for i in 1..l_shifts.shiftNum.count loop
1116 if(l_shifts.endDate(i) < l_shifts.startDate(i)) then --overnight shift
1117 l_shifts.endDate(i) := l_shifts.endDate(i) + 1;
1118 end if;
1119
1120 if (l_logLevel <= wip_constants.full_logging) then
1121 wip_logger.log('**********shiftNum:' || l_shifts.shiftNum(i), l_retStatus);
1122 wip_logger.log('**shift start date:' || to_char(l_shifts.startDate(i), g_logDateFmt), l_retStatus);
1123 wip_logger.log('****shift end date:' || to_char(l_shifts.endDate(i), g_logDateFmt), l_retStatus);
1124 end if;
1125
1126 --if shift ends before the requested start date, skip it since none of the shift
1127 --can be used. don't do this in the sql query as it degrades performance
1128 if(l_shifts.endDate(i) < p_startDate) then
1129 if (l_logLevel <= wip_constants.full_logging) then
1130 wip_logger.log('skipping shift (ends before start date)', l_retStatus);
1131 end if;
1132 goto NO_FULFILL_USAGE;--end of loop
1133 end if;
1134
1135 --if the shift starts before the start time, adjust the shift length
1136 l_fromDate := greatest(l_shifts.startDate(i), p_startDate);
1137 if (l_logLevel <= wip_constants.full_logging) then
1138 wip_logger.log('calculated start date: ' || to_char(l_fromDate, g_logDateFmt), l_retStatus);
1139 end if;
1140
1141 l_shiftLen := l_shifts.endDate(i) - l_fromDate;
1142 /*Bug 7015594: If shift start time is same as end time then consider it as 24 hours resource.
1143 This should be only done when 24 hours check is unchecked and resource is not used on repetitive line*/
1144 if(x_resTbls.avail24Flag(p_idx) <> wip_constants.yes AND p_repLineID is null AND l_shiftLen= 0) then
1145 l_shiftLen := 86400;
1146 end if;
1147
1148 if (l_logLevel <= wip_constants.full_logging) then
1149 wip_logger.log('shiftLen(HRS) is ' || round(l_shiftLen*24, g_precision), l_retStatus);
1150 end if;
1151
1152 if(round(l_shiftLen, g_precision) = 0) then
1153 if (l_logLevel <= wip_constants.full_logging) then
1157 end if;
1154 wip_logger.log('skipping shift (no usage)', l_retStatus);
1155 end if;
1156 goto NO_FULFILL_USAGE;--end of loop
1158
1159
1160 if(l_startDate is null) then
1161 l_startDate := l_fromDate;
1162 if (l_logLevel <= wip_constants.full_logging) then
1163 wip_logger.log('calculated resource start date:' || to_char(l_startDate, g_logDateFmt), l_retStatus);
1164 end if;
1165 end if;
1166
1167 if(round(l_remUsage, g_precision) <= round(l_shiftLen, g_precision)) then
1168 --shift fullfilled resource usage (round to approximately seconds)
1169
1170 if (l_logLevel <= wip_constants.full_logging) then
1171 wip_logger.log('calculated resource start date:' || to_char(l_startDate, g_logDateFmt), l_retStatus);
1172 end if;
1173 x_resTbls.startDate(p_idx) := l_startDate;
1174 x_resTbls.endDate(p_idx) := l_fromDate + l_remUsage;
1175 --record shift usage
1176 x_resTbls.usgStartDate.extend(1);
1177 x_resTbls.usgEndDate.extend(1);
1178 x_resTbls.usgCumMinProcTime.extend(1);
1179
1180 l_usgIdx := x_resTbls.usgStartDate.count;
1181 if (l_logLevel <= wip_constants.full_logging) then
1182 wip_logger.log('idx is ' || l_usgIdx, l_retStatus);
1183 wip_logger.log('count is ' || x_resTbls.usgStartIdx.count, l_retStatus);
1184 wip_logger.log('val is ' || x_resTbls.usgStartIdx(p_idx), l_retStatus);
1185 end if;
1186
1187 x_resTbls.usgStartIdx(p_idx) := nvl(x_resTbls.usgStartIdx(p_idx), l_usgIdx);
1188 x_resTbls.usgEndIdx(p_idx) := l_usgIdx;
1189
1190 x_resTbls.usgStartDate(l_usgIdx) := l_fromDate;
1191
1192 --shift fulfilled resource => usage end time is resource end time
1193 x_resTbls.usgEndDate(l_usgIdx) := x_resTbls.endDate(p_idx);
1194 if(l_isFirstUsg) then
1195 if (l_logLevel <= wip_constants.full_logging) then
1196 wip_logger.log('first usage', l_retStatus);
1197 end if;
1198 l_isFirstUsg := false;
1199 l_prevProcTime := 0;
1200 else
1201 if (l_logLevel <= wip_constants.full_logging) then
1202 wip_logger.log('not first usage', l_retStatus);
1203 end if;
1204 l_prevProcTime := x_resTbls.usgCumMinProcTime(l_usgIdx - 1);
1205 end if;
1206
1207 x_resTbls.usgCumMinProcTime(l_usgIdx) := l_prevProcTime +
1208 (24*60)*(x_resTbls.usgEndDate(l_usgIdx) -
1209 x_resTbls.usgStartDate(l_usgIdx));
1210
1211 if (l_logLevel <= wip_constants.full_logging) then
1212 wip_logger.log('start date is ' || to_char(x_resTbls.startDate(p_idx), g_logDateFmt), l_retStatus);
1213 wip_logger.log('end date is ' || to_char(x_resTbls.endDate(p_idx), g_logDateFmt), l_retStatus);
1214 wip_logger.log('usage:' || to_char(x_resTbls.usgStartDate(l_usgIdx), g_logDateFmt) || ' - ' ||
1215 to_char(x_resTbls.usgEndDate(l_usgIdx), g_logDateFmt), l_retStatus);
1216 wip_logger.log('cum usage time:' || x_resTbls.usgCumMinProcTime(l_usgIdx), l_retStatus);
1217 end if;
1218
1219 l_resourceScheduled := true; --exit outer loop
1220 exit; --exit inner loop
1221
1222 else --shift did not fulfill resource usage
1223 l_remUsage := l_remUsage - l_shiftLen; --decrement remaining time
1224
1225 --record shift usage
1226 x_resTbls.usgStartDate.extend(1);
1227 x_resTbls.usgEndDate.extend(1);
1228 x_resTbls.usgCumMinProcTime.extend(1);
1229
1230 l_usgIdx := x_resTbls.usgStartDate.count;
1231 x_resTbls.usgStartIdx(p_idx) := nvl(x_resTbls.usgStartIdx(p_idx), l_usgIdx);
1232 x_resTbls.usgEndIdx(p_idx) := l_usgIdx;
1233
1234 x_resTbls.usgStartDate(l_usgIdx) := l_fromDate;
1235 --resource consumed until end of the shift
1236 x_resTbls.usgEndDate(l_usgIdx) := l_shifts.endDate(i);
1237
1238 if(l_isFirstUsg) then
1239 l_prevProcTime := 0;
1240 l_isFirstUsg := false;
1241 else
1242 l_prevProcTime := x_resTbls.usgCumMinProcTime(l_usgIdx - 1);
1243 end if;
1244 x_resTbls.usgCumMinProcTime(l_usgIdx) := l_prevProcTime +
1245 (24*60)*(x_resTbls.usgEndDate(l_usgIdx) -
1246 x_resTbls.usgStartDate(l_usgIdx));
1247 if (l_logLevel <= wip_constants.full_logging) then
1248 wip_logger.log('exhausted shift. remaining usage(HRS) is ' || round(l_remUsage*24, g_precision), l_retStatus);
1249 wip_logger.log('usage:' || to_char(x_resTbls.usgStartDate(l_usgIdx), g_logDateFmt) || ' - ' ||
1250 to_char(x_resTbls.usgEndDate(l_usgIdx), g_logDateFmt), l_retStatus);
1251 wip_logger.log('cum usage time:' || x_resTbls.usgCumMinProcTime(l_usgIdx), l_retStatus);
1252 end if;
1253 end if;
1254 <<NO_FULFILL_USAGE>>
1255 null;
1256 end loop;
1257
1258 --if the resource wasn't scheduled, increment the date and keep going.
1259 if(not l_resourceScheduled) then
1260 l_cursorStartDate := l_cursorStartDate + g_dateCursorLen;
1261
1265 wip_logger.log('exhausted calendar. remaining usage(HRS) is ' || round(l_remUsage*24, g_precision), l_retStatus);
1262 --if the next start date is after the end of the calendar, then we can't schedule anything
1263 if(l_cursorStartDate > p_maxDate) then
1264 if (l_logLevel <= wip_constants.full_logging) then
1266 end if;
1267 fnd_message.set_name('WIP', 'WIP_NO_CALENDAR');
1268 fnd_msg_pub.add;
1269 raise fnd_api.g_exc_unexpected_error;
1270 end if;
1271 end if;
1272 end loop;
1273 if(l_logLevel <= wip_constants.trace_logging) then
1274 wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.forwardSchResource',
1275 p_procReturnStatus => x_returnStatus,
1276 p_msg => 'success',
1277 x_returnStatus => l_retStatus);
1278 end if;
1279 exception
1280 when fnd_api.g_exc_unexpected_error then
1281 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
1282 if(l_logLevel <= wip_constants.trace_logging) then
1283 wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.forwardSchResource',
1284 p_procReturnStatus => x_returnStatus,
1285 p_msg => 'error',
1286 x_returnStatus => l_retStatus);
1287 end if;
1288 when others then
1289 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
1290 fnd_msg_pub.add_exc_msg(p_pkg_name => 'wip_infResSched_grp',
1291 p_procedure_name => 'forwardSchResource',
1292 p_error_text => SQLERRM);
1293 if(l_logLevel <= wip_constants.trace_logging) then
1294 wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.forwardSchResource',
1295 p_procReturnStatus => x_returnStatus,
1296 p_msg => 'unexp error: ' || SQLERRM,
1297 x_returnStatus => l_retStatus);
1298 end if;
1299
1300 end forwardSchResource;
1301
1302 /* fix bug 7027946: Added procedure wip_infResSched_grp.capacityExceptions to handle resource capacity exceptions.
1303 It will change the resource shifts (add day, delete day, modify shift times) as per capacity
1304 exceptions.
1305 */
1306 procedure capacityExceptions(p_resID IN NUMBER,
1307 p_deptID IN NUMBER,
1308 p_orgID IN NUMBER,
1309 x_shifts IN OUT NOCOPY shift_recTbl_t,
1310 x_returnStatus OUT NOCOPY VARCHAR2) is
1311
1312 cursor c_capacityDtls(v_resID NUMBER,
1313 v_deptID NUMBER,
1314 v_orgID NUMBER) is
1315 select shift_num,
1316 from_date,
1317 to_date,
1318 from_time,
1319 to_time,
1320 capacity_change,
1321 action_type
1322 from bom_resource_changes brc,
1323 crp_simulation_sets crp
1324 where department_id = v_deptID
1325 and resource_id = v_resID
1326 and organization_id = v_orgID
1327 and crp.simulation_set = brc.simulation_set
1328 and crp.use_in_wip_flag = 1;
1329
1330 --used to collect cursor records...
1331 type capacity_rec_t is record(shiftNum number,
1332 fromDate date,
1333 toDate date ,
1334 fromTime number,
1335 toTime number,
1336 capacityChange number,
1337 actionType number);
1338
1339 type capacity_recTbl_t is table of capacity_rec_t index by binary_integer;
1340
1341 l_capacity capacity_recTbl_t ;
1342 l_logLevel NUMBER := fnd_log.g_current_runtime_level;
1343 l_params wip_logger.param_tbl_t;
1344 l_retStatus VARCHAR2(1);
1345
1346 l_firstRow NUMBER;
1347 l_currRow NUMBER;
1348 l_lastRow NUMBER;
1349 l_prevRow NUMBER;
1350 j NUMBER;
1351 k NUMBER;
1352 flag BOOLEAN;
1353
1354 BEGIN
1355
1356 if (l_logLevel <= wip_constants.trace_logging) then
1357 l_params(1).paramName := 'p_resID';
1358 l_params(1).paramValue := p_resID;
1359 l_params(2).paramName := 'p_deptID';
1360 l_params(2).paramValue := p_deptID;
1361 l_params(3).paramName := 'p_orgID';
1362 l_params(3).paramValue := p_orgID;
1363
1364 wip_logger.entryPoint(p_procName => 'wip_infResSched_grp.capacityExceptions',
1365 p_params => l_params,
1366 x_returnStatus => x_returnStatus);
1367 end if;
1368
1369 open c_capacityDtls(v_resID => p_resID,
1370 v_deptID => p_deptID,
1371 v_orgID => p_orgID);
1372 fetch c_capacityDtls
1373 bulk collect into l_capacity ;
1374 close c_capacityDtls;
1375
1376 for i in 1..l_capacity.count loop -- outer most loop to loop through all the capacity exception records
1377
1378 l_firstRow := x_shifts.shiftNum.FIRST;
1379 l_lastRow := x_shifts.shiftNum.LAST;
1380 j := l_firstRow;
1381 flag := FALSE;
1382
1383 if (l_capacity(i).actionType = wip_constants.DELETE_WKDY) THEN -- delete a working day
1384
1388 l_currRow := j; -- row that needs to be deleted
1385 WHILE (j <= l_lastRow) LOOP
1386
1387 IF ( Trunc(l_capacity(i).fromDate) = Trunc(x_shifts.startDate(j)) ) then
1389 k := x_shifts.shiftNum.NEXT(l_currRow);
1390 WHILE (k <= l_lastRow) LOOP -- shift all the rows one-up
1391 x_shifts.shiftNum(l_currRow) := x_shifts.shiftNum(k);
1392 x_shifts.startDate(l_currRow) := x_shifts.startDate(k);
1393 x_shifts.endDate(l_currRow) := x_shifts.endDate(k);
1394 l_currRow := k;
1395 k := x_shifts.shiftNum.NEXT(k);
1396 END LOOP;
1397
1398 x_shifts.shiftNum.trim(); -- trim the last row
1399 x_shifts.startDate.trim();
1400 x_shifts.endDate.trim();
1401 l_lastRow := x_shifts.shiftNum.LAST; -- updated last row
1402 END IF;
1403 j := x_shifts.shiftNum.NEXT(j);
1404 END LOOP;
1405
1406 ELSIF (l_capacity(i).actionType = wip_constants.ADD_WKDY) THEN -- add a non-working day
1407
1408 WHILE (j <= l_lastRow) LOOP
1409 IF ( Trunc(l_capacity(i).fromDate) < Trunc(x_shifts.startDate(j)) ) THEN -- add the day just before the shift date
1410 flag := TRUE; -- that is greater than the capacity date
1411 x_shifts.shiftNum.extend; -- extend the xshifts table by one row and insert the day and then shift remaning days
1412 x_shifts.startDate.extend;
1413 x_shifts.endDate.extend;
1414 l_lastRow := x_shifts.shiftNum.LAST;
1415
1416 k := l_lastRow;
1417 l_prevRow := x_shifts.shiftNum.PRIOR(k); -- now shift all the rows one-down
1418 WHILE (k <> j) LOOP
1419 x_shifts.shiftNum(k) := x_shifts.shiftNum(l_prevRow);
1420 x_shifts.startDate(k) := x_shifts.startDate(l_prevRow);
1421 x_shifts.endDate(k) := x_shifts.endDate(l_prevRow);
1422 k := l_prevRow;
1423 l_prevRow := x_shifts.shiftNum.PRIOR(k);
1424 END LOOP;
1425
1426 x_shifts.shiftNum(j) := l_capacity(i).shiftNum;
1427 x_shifts.startDate(j) := l_capacity(i).fromDate + l_capacity(i).fromTime/86400;
1428 x_shifts.endDate(j) := l_capacity(i).fromDate + l_capacity(i).toTime/86400;
1429
1430 EXIT WHEN flag = TRUE;
1431 END IF;
1432 j := x_shifts.shiftNum.NEXT(j);
1433 END LOOP;
1434
1435 ELSIF l_capacity(i).actionType = wip_constants.MODIFY_WKDY THEN -- modify capacity - modify or reduce capacity
1436
1437 WHILE (j <= l_lastRow) LOOP
1438 IF ( Trunc(x_shifts.startDate(j)) >= Trunc(l_capacity(i).fromDate)
1439 AND Trunc(x_shifts.startDate(j)) <= Trunc(l_capacity(i).toDate) ) THEN
1440
1441 IF ( l_capacity(i).capacityChange >0 ) THEN -- add capacity
1442 x_shifts.endDate(j) := x_shifts.endDate(j) + (l_capacity(i).toTime - l_capacity(i).fromTime)/86400 ;
1443 ELSE -- reduce capacity
1444 x_shifts.endDate(j) := x_shifts.endDate(j) - (l_capacity(i).toTime - l_capacity(i).fromTime)/86400 ;
1445 END IF;
1446
1447 END IF;
1448 j := x_shifts.shiftNum.NEXT(j);
1449 END LOOP;
1450
1451 END IF;
1452 END LOOP; -- end outer loop
1453
1454 IF(l_logLevel <= wip_constants.trace_logging) then
1455 wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.capacityExceptions',
1456 p_procReturnStatus => x_returnStatus,
1457 p_msg => 'success',
1458 x_returnStatus => l_retStatus);
1459 END IF;
1460 EXCEPTION
1461 WHEN fnd_api.g_exc_unexpected_error THEN
1462 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
1463 if(l_logLevel <= wip_constants.trace_logging) then
1464 wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.capacityExceptions',
1465 p_procReturnStatus => x_returnStatus,
1466 p_msg => 'error',
1467 x_returnStatus => l_retStatus);
1468 end if;
1469 WHEN others THEN
1470 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
1471 fnd_msg_pub.add_exc_msg(p_pkg_name => 'wip_infResSched_grp',
1472 p_procedure_name => 'capacityExceptions',
1473 p_error_text => SQLERRM);
1474 if(l_logLevel <= wip_constants.trace_logging) then
1475 wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.capacityExceptions',
1476 p_procReturnStatus => x_returnStatus,
1477 p_msg => 'unexp error: ' || SQLERRM,
1478 x_returnStatus => l_retStatus);
1479 end if;
1480 END capacityExceptions;
1481 /* end of fix bug 7027946 */
1482
1483 procedure forwardSchedule(p_orgID IN number,
1484 p_repLineID in NUMBER := null,
1485 p_startDate in DATE,
1486 p_range in num_tbl_t,
1487 p_schedFlag in number,
1491 l_logLevel NUMBER := fnd_log.g_current_runtime_level;
1488 x_resTbls in out NOCOPY OP_RES_RECTBL_T,
1489 x_returnStatus OUT NOCOPY VARCHAR2) is
1490 l_resStartDate DATE; -- := p_startDate;
1492 l_params wip_logger.param_tbl_t;
1493 l_retStatus VARCHAR2(1);
1494 l_fromTime NUMBER;
1495 l_maxDate DATE;
1496 l_shiftStartDate DATE;
1497 l_currSchedSeqNum NUMBER;
1498 l_prevResIdx NUMBER;
1499 i number;
1500 l_schedulingBatch boolean := false;
1501 l_doneSchedBatch boolean;
1502 begin
1503 if (l_logLevel <= wip_constants.trace_logging) then
1504 l_params(1).paramName := 'p_repLineID';
1505 l_params(1).paramValue := p_repLineID;
1506 l_params(2).paramName := 'p_startDate';
1507 l_params(2).paramValue := to_char(p_startDate, g_logDateFmt);
1508 l_params(3).paramName := 'p_range(1)';
1509 l_params(3).paramValue := p_range(1);
1510 l_params(4).paramName := 'p_range(2)';
1511 l_params(4).paramValue := p_range(2);
1512 l_params(5).paramName := 'p_schedFlag';
1513 l_params(5).paramValue := p_schedFlag;
1514
1515 wip_logger.entryPoint(p_procName => 'wip_infResSched_grp.forwardSchedule',
1516 p_params => l_params,
1517 x_returnStatus => x_returnStatus);
1518 end if;
1519 --get the maximum date
1520 select bc.calendar_end_date
1521 into l_maxDate
1522 from bom_calendars bc, mtl_parameters mp
1523 where mp.organization_id = p_orgID
1524 and mp.calendar_code = bc.calendar_code;
1525
1526 while(getNextResIdx(p_range, p_schedFlag, g_forward, x_resTbls, i)) loop
1527 if (l_logLevel <= wip_constants.full_logging) then
1528 wip_logger.log('begin scheduling resource:' || x_resTbls.resID(i), l_retStatus);
1529 wip_logger.log(' operation:' || x_resTbls.opSeqNum(i), l_retStatus);
1530 wip_logger.log(' res seq num:' || x_resTbls.resSeqNum(i), l_retStatus);
1531 wip_logger.log(' schedule seq num:' || x_resTbls.schedSeqNum(i), l_retStatus);
1532 wip_logger.log(' sched flag:' || x_resTbls.schedFlag(i), l_retStatus);
1533 wip_logger.log(' total usage (HRS):' || round(x_resTbls.totalDaysUsg(i)*24, g_precision), l_retStatus);
1534
1535 if(l_prevResIdx is not null) then
1536 wip_logger.log('prev sched seq num is:' || x_resTbls.schedSeqNum(l_prevResIdx), l_retStatus);
1537 wip_logger.log('prev op seq num is:' || x_resTbls.opSeqNum(l_prevResIdx), l_retStatus);
1538 end if;
1539 end if;
1540
1541 l_doneSchedBatch := false;
1542 --scheduling simultaneous
1543 if(l_prevResIdx is not null and
1544 x_resTbls.schedSeqNum(i) = x_resTbls.schedSeqNum(l_prevResIdx) and
1545 x_resTbls.opSeqNum(i) = x_resTbls.opSeqNum(l_prevResIdx)) then
1546 l_schedulingBatch := true;
1547 if (l_logLevel <= wip_constants.full_logging) then
1548 wip_logger.log('setting sched batch to true', l_retStatus);
1549 end if;
1550 --just finished scheduling batch
1551 elsif(l_schedulingBatch) then
1552 l_schedulingBatch := false;
1553 l_doneSchedBatch := true;
1554 if (l_logLevel <= wip_constants.full_logging) then
1555 wip_logger.log('setting done sched batch to true', l_retStatus);
1556 end if;
1557 end if;
1558
1559
1560 if(l_prevResIdx is null) then
1561 l_resStartDate := p_startDate;
1562
1563 --if scheduling simultaneous, no need to get new start date, just use the previous start date
1564 elsif(not l_schedulingBatch) then
1565 l_resStartDate := getStartDate(p_range, p_schedFlag, x_resTbls, i, l_doneSchedBatch, l_prevResIdx);
1566 end if;
1567
1568 forwardSchResource(p_orgID, l_resStartDate, l_maxDate, i, p_repLineID, x_resTbls, x_returnStatus);
1569 if(x_returnStatus <> fnd_api.g_ret_sts_success) then
1570 raise fnd_api.g_exc_unexpected_error;
1571 end if;
1572
1573 l_prevResIdx := i;
1574 end loop;
1575
1576 if(l_logLevel <= wip_constants.trace_logging) then
1577 wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.forwardSchedule',
1578 p_procReturnStatus => x_returnStatus,
1579 p_msg => 'success',
1580 x_returnStatus => l_retStatus);
1581 end if;
1582 exception
1583 when fnd_api.g_exc_unexpected_error then
1584 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
1585 if(l_logLevel <= wip_constants.trace_logging) then
1586 wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.forwardSchedule',
1587 p_procReturnStatus => x_returnStatus,
1588 p_msg => 'error',
1589 x_returnStatus => l_retStatus);
1590 end if;
1591 when others then
1592 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
1593 fnd_msg_pub.add_exc_msg(p_pkg_name => 'wip_infResSched_grp',
1594 p_procedure_name => 'forwardSchedule',
1595 p_error_text => SQLERRM);
1596 if(l_logLevel <= wip_constants.trace_logging) then
1597 wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.forwardSchedule',
1598 p_procReturnStatus => x_returnStatus,
1602 end forwardSchedule;
1599 p_msg => 'unexp error: ' || SQLERRM,
1600 x_returnStatus => l_retStatus);
1601 end if;
1603
1604 -- the resource could have been scheduled from
1605 function getEndDate(p_range in num_tbl_t,
1606 p_schedFlag in number,
1607 p_resTbls in op_res_rectbl_t,
1608 p_curIdx in number,
1609 p_doneSchedBatch in boolean,
1610 p_prevIdx in number) return date is
1611 l_retStatus VARCHAR2(1);
1612 l_params wip_logger.param_tbl_t;
1613 i number;
1614 l_minStartDate date;
1615 l_logLevel NUMBER := fnd_log.g_current_runtime_level;
1616 l_bool boolean;
1617 begin
1618 if (l_logLevel <= wip_constants.trace_logging) then
1619 l_params(1).paramName := 'p_range(1)';
1620 l_params(1).paramValue := p_range(1);
1621 l_params(2).paramName := 'p_range(2)';
1622 l_params(2).paramValue := p_range(2);
1623 l_params(3).paramName := 'p_schedFlag';
1624 l_params(3).paramValue := p_schedFlag;
1625 l_params(4).paramName := 'p_curIdx';
1626 l_params(4).paramValue := p_curIdx;
1627 l_params(5).paramName := 'p_doneSchedBatch';
1628 if(p_doneSchedBatch) then l_params(5).paramValue := 'true';
1629 else l_params(5).paramValue := 'false';
1630 end if;
1631 l_params(6).paramName := 'p_prevIdx';
1632 l_params(6).paramValue := p_prevIdx;
1633
1634 wip_logger.entryPoint(p_procName => 'wip_infResSched_grp.getEndDate',
1635 p_params => l_params,
1636 x_returnStatus => l_retStatus);
1637 end if;
1638
1639 --in case we just got done scheduling a batch of simultaneous resources, get the
1640 --latest end date to use as the next resource's start date
1641 i := p_curIdx;
1642
1643 if(p_doneSchedBatch) then
1644 if(l_logLevel <= wip_constants.full_logging) then
1645 wip_logger.log('done scheduling batch', l_retStatus);
1646 end if;
1647 while(getNextResIdx(p_range, p_schedFlag, g_forward, p_resTbls, i)) loop
1648 if(l_logLevel <= wip_constants.full_logging) then
1649 wip_logger.log('in loop', l_retStatus);
1650 wip_logger.log('resID' || p_resTbls.resID(i), l_retStatus);
1651 wip_logger.log('opSeq' || p_resTbls.opSeqNum(i), l_retStatus);
1652 wip_logger.log('schSeq' || p_resTbls.schedSeqNum(i), l_retStatus);
1653 wip_logger.log('idx' || i, l_retStatus);
1654 end if;
1655 if(p_resTbls.schedSeqNum(i) = p_resTbls.schedSeqNum(p_prevIdx) and
1656 p_resTbls.opSeqNum(i) = p_resTbls.opSeqNum(p_prevIdx)) then
1657 l_minStartDate := least(nvl(l_minStartDate, p_resTbls.startDate(i)), p_resTbls.startDate(i));
1658 if(l_logLevel <= wip_constants.full_logging) then
1659 wip_logger.log('resource in batch. startDate:' || to_char(p_resTbls.startDate(i), g_logDateFmt), l_retStatus);
1660 end if;
1661 else
1662 if(l_logLevel <= wip_constants.full_logging) then
1663 wip_logger.log('resource not in batch.', l_retStatus);
1664 end if;
1665 exit;
1666 end if;
1667 end loop;
1668 else
1669 l_bool := (getNextResIdx(p_range, p_schedFlag, g_forward, p_resTbls, i));
1670 l_minStartDate := p_resTbls.startDate(i);
1671 end if;
1672
1673 if (l_logLevel <= wip_constants.trace_logging) then
1674 wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.getEndDate',
1675 p_procReturnStatus => to_char(l_minStartDate),
1676 p_msg => 'finished scheduling',
1677 x_returnStatus => l_retStatus);
1678 end if;
1679 return l_minStartDate;
1680 end getEndDate;
1681
1682 procedure backwardSchResource(p_orgID in number,
1683 p_endDate in date,
1684 p_minDate in date,
1685 p_idx in number,
1686 p_repLineID in number := null,
1687 x_resTbls in out nocopy op_res_rectbl_t,
1688 x_returnStatus out nocopy varchar2) is
1689
1690 cursor c_shiftTimes(v_resID NUMBER,
1691 v_deptID NUMBER,
1692 v_orgID NUMBER,
1693 v_startDate DATE,
1694 v_endDate DATE) is
1695 select brs.shift_num shiftNum,
1696 bsd.shift_date + bst.from_time/86400,
1697 bsd.shift_date + bst.to_time/86400
1698 from bom_resource_shifts brs,
1699 mtl_parameters mp,
1700 bom_shift_dates bsd,
1701 bom_shift_times bst,
1702 bom_department_resources bdr
1703 where bdr.department_id = v_deptID
1704 and bdr.resource_id = v_resID
1705 and brs.resource_id = bdr.resource_id
1706 and brs.department_id = nvl(bdr.share_from_dept_id,bdr.department_id)
1707 and mp.organization_id = v_orgID
1708 and mp.calendar_code = bsd.calendar_code
1709 and mp.calendar_exception_set_id = bsd.exception_set_id
1710 and brs.shift_num = bsd.shift_num
1711 and bsd.shift_date between v_startDate and v_endDate --don't incorporate time into this check as it slows the query
1712 and bsd.seq_num is not null
1716
1713 and bst.shift_num = bsd.shift_num
1714 and bst.calendar_code = bsd.calendar_code
1715 order by bsd.shift_date desc, bst.from_time desc;
1717 cursor c_24HrTimes(v_orgID NUMBER,
1718 v_startDate DATE,
1719 v_endDate DATE) is
1720 select -1,
1721 bcd.calendar_date,
1722 bcd.calendar_date + 1
1723 from mtl_parameters mp,
1724 bom_calendar_dates bcd
1725 where mp.organization_id = v_orgID
1726 and mp.calendar_code = bcd.calendar_code
1727 and mp.calendar_exception_set_id = bcd.exception_set_id
1728 and bcd.calendar_date between v_startDate and v_endDate
1729 and bcd.seq_num is not null
1730 order by bcd.calendar_date desc;
1731
1732
1733 --for repetitive, ignore shifts and use the line's start and stop times. However, do
1734 --respect the working days definition
1735 cursor c_repTimes(v_repLineID NUMBER,
1736 v_orgID NUMBER,
1737 v_startDate DATE,
1738 v_endDate DATE) is
1739 select -1 shiftNum,
1740 bcd.calendar_date + wl.start_time/86400,
1741 bcd.calendar_date + wl.stop_time/86400
1742 from mtl_parameters mp,
1743 bom_calendar_dates bcd,
1744 wip_lines wl
1745 where mp.organization_id = v_orgID
1746 and mp.calendar_code = bcd.calendar_code
1747 and mp.calendar_exception_set_id = bcd.exception_set_id
1748 and wl.line_id = v_repLineID
1749 and bcd.seq_num is not null --working day
1750 and bcd.calendar_date between v_startDate and v_endDate --use stop_time to comsume tail end of a shift
1751 order by bcd.calendar_date desc;
1752
1753 --used to collect cursor records...
1754 type shift_recTbl_t is record(shiftNum num_tbl_t,
1755 startDate date_tbl_t,
1756 endDate date_tbl_t);
1757
1758 l_shifts shift_recTbl_t;
1759 l_logLevel NUMBER := fnd_log.g_current_runtime_level;
1760 l_params wip_logger.param_tbl_t;
1761 l_retStatus VARCHAR2(1);
1762 l_resourceScheduled boolean := false;
1763 l_cursorEndDate date := p_endDate;
1764 l_shiftEndDate date;
1765 l_toDate DATE;
1766 l_shiftLen NUMBER;
1767 l_remUsage NUMBER := x_resTbls.totalDaysUsg(p_idx);
1768 l_usgIdx NUMBER;
1769 i number;
1770 j number;
1771 l_tmpUsgStartDate date;
1772 l_tmpUsgEndDate date;
1773 l_endDate date;
1774 l_prevProcTime NUMBER := 0;
1775 l_dummy NUMBER; /* Bug 5660475 */
1776 begin
1777
1778 if (l_logLevel <= wip_constants.trace_logging) then
1779 l_params(1).paramName := 'p_orgID';
1780 l_params(1).paramValue := p_orgID;
1781 l_params(2).paramName := 'p_endDate';
1782 l_params(2).paramValue := to_char(p_endDate, g_logDateFmt);
1783 l_params(3).paramName := 'p_minDate';
1784 l_params(3).paramValue := to_char(p_minDate, g_logDateFmt);
1785 l_params(4).paramName := 'p_idx';
1786 l_params(4).paramValue := p_idx;
1787 l_params(5).paramName := 'p_repLineID';
1788 l_params(5).paramValue := p_repLineID;
1789 wip_logger.entryPoint(p_procName => 'wip_infResSched_grp.backwardSchResource',
1790 p_params => l_params,
1791 x_returnStatus => l_retStatus);
1792 end if;
1793 /* Fix for bug 5660475: If dealing with shift resource, first check if shifts are setup fine. */
1794 if( p_repLineID is null
1795 and x_resTbls.avail24Flag(p_idx) = wip_constants.no
1796 and x_resTbls.schedFlag(p_idx) <> wip_constants.sched_no) then
1797 wip_logger.log('This is a shift resource. Need to validate shift setup', l_retStatus);
1798 begin
1799 select 1
1800 into l_dummy
1801 from dual
1802 where exists (select 1
1803 from bom_resource_shifts brs,
1804 mtl_parameters mp,
1805 bom_shift_dates bsd,
1806 bom_shift_times bst,
1807 bom_department_resources bdr
1808 where bdr.department_id = x_resTbls.deptID(p_idx)
1809 and bdr.resource_id = x_resTbls.resID(p_idx)
1810 and brs.resource_id = bdr.resource_id
1811 and brs.department_id = nvl(bdr.share_from_dept_id,bdr.department_id)
1812 and mp.organization_id = p_orgID
1813 and mp.calendar_code = bsd.calendar_code
1814 and mp.calendar_exception_set_id = bsd.exception_set_id
1815 and brs.shift_num = bsd.shift_num
1816 and bsd.seq_num is not null
1817 and bst.shift_num = bsd.shift_num
1818 and bst.calendar_code = bsd.calendar_code);
1819 exception
1820 when NO_DATA_FOUND then
1821 wip_logger.log('Error: Missing shifts or shift times!', l_retStatus);
1822 fnd_message.set_name('WIP', 'WIP_SHIFT_RESOURCE');
1823 fnd_message.set_token('ENTITY1', x_resTbls.resSeqNum(p_idx));
1824 fnd_message.set_token('ENTITY2', x_resTbls.opSeqNum(p_idx));
1825 fnd_msg_pub.add;
1826 raise fnd_api.g_exc_unexpected_error;
1827 end;
1828 end if;
1829
1830 x_resTbls.usgStartIdx(p_idx) := null;
1831 x_resTbls.usgEndIdx(p_idx) := null;
1832
1836 wip_logger.log('cursor start date: ' || to_char((l_cursorEndDate - (g_dateCursorLen - 1/86400)), g_logDateFmt), l_retStatus);
1833 loop
1834 exit when l_resourceScheduled;
1835 if (l_logLevel <= wip_constants.full_logging) then
1837 wip_logger.log('cursor end date: ' || to_char(l_cursorEndDate, g_logDateFmt), l_retStatus);
1838 end if;
1839
1840 --for v_endDate, subtract a second to avoid overlap between cursors.
1841 if(p_repLineID is not null) then
1842 open c_repTimes(v_repLineID => p_repLineID,
1843 v_orgID => p_orgID,
1844 v_startDate => l_cursorEndDate - (g_dateCursorLen - 1/86400),
1845 v_endDate => l_cursorEndDate);
1846 fetch c_repTimes
1847 bulk collect into l_shifts.shiftNum,
1848 l_shifts.startDate,
1849 l_shifts.endDate;
1850 close c_repTimes;
1851 elsif(x_resTbls.avail24Flag(p_idx) = wip_constants.yes) then
1852 open c_24HrTimes(v_orgID => p_orgID,
1853 v_startDate => l_cursorEndDate - (g_dateCursorLen - 1/86400),
1854 v_endDate => l_cursorEndDate);
1855 fetch c_24HrTimes
1856 bulk collect into l_shifts.shiftNum,
1857 l_shifts.startDate,
1858 l_shifts.endDate;
1859 close c_24HrTimes;
1860 else
1861 open c_shiftTimes(v_resID => x_resTbls.resID(p_idx),
1862 v_deptID => x_resTbls.deptID(p_idx),
1863 v_orgID => p_orgID,
1864 v_startDate => l_cursorEndDate - (g_dateCursorLen - 1/86400),
1865 v_endDate => l_cursorEndDate);
1866 fetch c_shiftTimes
1867 bulk collect into l_shifts.shiftNum,
1868 l_shifts.startDate,
1869 l_shifts.endDate;
1870
1871 if (l_shifts.shiftNum.count = 0 ) then
1872 /* Fix for bug 5660475: If shifts are not available in the date range,
1873 we should continue to search in the next date range, instead of erroring out. */
1874 wip_logger.log('No shifts found in this period.', l_retStatus);
1875 l_resourceScheduled := false;
1876 end if;
1877
1878 close c_shiftTimes;
1879 end if;
1880
1881
1882
1883 for i in 1..l_shifts.shiftNum.count loop
1884 if(l_shifts.endDate(i) < l_shifts.startDate(i)) then --overnight shift
1885 l_shifts.endDate(i) := l_shifts.endDate(i) + 1;
1886 end if;
1887 if (l_logLevel <= wip_constants.full_logging) then
1888
1889 wip_logger.log('**********shiftNum:' || l_shifts.shiftNum(i), l_retStatus);
1890 wip_logger.log('**shift start date:' || to_char(l_shifts.startDate(i), g_logDateFmt), l_retStatus);
1891 wip_logger.log('****shift end date:' || to_char(l_shifts.endDate(i), g_logDateFmt), l_retStatus);
1892 end if;
1893
1894 --if shift starts after the requested end date, skip it since none of the shift
1895 --can be used. don't do this in the sql query as it degrades performance
1896 if(l_shifts.startDate(i) > p_endDate) then
1897 if (l_logLevel <= wip_constants.full_logging) then
1898 wip_logger.log('skipping shift (starts after end date)', l_retStatus);
1899 end if;
1900 goto NO_FULFILL_USAGE;--end of loop
1901 end if;
1902
1903 --if the shift ends before the end time, adjust the shift length
1904 l_toDate := least(l_shifts.endDate(i), p_endDate);
1905 if (l_logLevel <= wip_constants.full_logging) then
1906 wip_logger.log('calculated end date: ' || to_char(l_toDate, g_logDateFmt), l_retStatus);
1907 end if;
1908
1909 l_shiftLen := l_toDate - l_shifts.startDate(i);
1910 /*Bug 7015594: If shift start time is same as end time then consider it as 24 hours resource.
1911 This should be only done when 24 hours check is unchecked and resource is not used on repetitive line*/
1912 if(x_resTbls.avail24Flag(p_idx) <> wip_constants.yes AND p_repLineID is null AND l_shiftLen= 0) then
1913 l_shiftLen := 86400;
1914 end if;
1915
1916
1917 if (l_logLevel <= wip_constants.full_logging) then
1918 wip_logger.log('shiftLen(HRS) is ' || round(l_shiftLen*24, g_precision), l_retStatus);
1919 end if;
1920
1921 if(round(l_shiftLen, g_precision) = 0) then
1922 if (l_logLevel <= wip_constants.full_logging) then
1923 wip_logger.log('skipping shift (no usage)', l_retStatus);
1924 end if;
1925 goto NO_FULFILL_USAGE;--end of loop
1926 end if;
1927
1928
1929 if(l_endDate is null) then
1930 l_endDate := l_toDate;
1931 if (l_logLevel <= wip_constants.full_logging) then
1932 wip_logger.log('calculated resource end date:' || to_char(l_endDate, g_logDateFmt), l_retStatus);
1933 end if;
1934 end if;
1935
1936 if(round(l_remUsage, g_precision) <= round(l_shiftLen, g_precision)) then
1937 --shift fullfilled resource usage (round to approximately seconds)
1938
1939 x_resTbls.startDate(p_idx) := l_toDate - l_remUsage;
1940 x_resTbls.endDate(p_idx) := l_endDate;
1941
1942 --record shift usage
1943 x_resTbls.usgStartDate.extend(1);
1944 x_resTbls.usgEndDate.extend(1);
1948 if (l_logLevel <= wip_constants.full_logging) then
1945 x_resTbls.usgCumMinProcTime.extend(1);
1946
1947 l_usgIdx := x_resTbls.usgStartDate.count;
1949 wip_logger.log('start idx is ' || x_resTbls.usgStartIdx(p_idx), l_retStatus);
1950 wip_logger.log('end idx is ' || x_resTbls.usgEndIdx(p_idx), l_retStatus);
1951 wip_logger.log('usg idx is ' || l_usgIdx, l_retStatus);
1952 end if;
1953 x_resTbls.usgStartIdx(p_idx) := nvl(x_resTbls.usgStartIdx(p_idx), l_usgIdx);
1954 x_resTbls.usgEndIdx(p_idx) := l_usgIdx;
1955
1956
1957 --shift fulfilled resource => usage start time is resource end time
1958 x_resTbls.usgStartDate(l_usgIdx) := x_resTbls.startDate(p_idx);
1959 x_resTbls.usgEndDate(l_usgIdx) := l_toDate;--l_shifts.endDate(i);
1960
1961 if (l_logLevel <= wip_constants.full_logging) then
1962 wip_logger.log('start date is ' || to_char(x_resTbls.startDate(p_idx), g_logDateFmt), l_retStatus);
1963 wip_logger.log('end date is ' || to_char(x_resTbls.endDate(p_idx), g_logDateFmt), l_retStatus);
1964 wip_logger.log('usgIdx:' || l_usgIdx, l_retStatus);
1965 wip_logger.log('usage:' || to_char(x_resTbls.usgStartDate(l_usgIdx), g_logDateFmt) || ' - ' ||
1966 to_char(x_resTbls.usgEndDate(l_usgIdx), g_logDateFmt), l_retStatus);
1967 end if;
1968
1969 l_resourceScheduled := true; --exit outer loop
1970 exit; --exit inner loop
1971
1972 else --shift did not fulfill resource usage
1973 l_remUsage := l_remUsage - l_shiftLen; --decrement remaining time
1974
1975 --record shift usage
1976 x_resTbls.usgStartDate.extend(1);
1977 x_resTbls.usgEndDate.extend(1);
1978 x_resTbls.usgCumMinProcTime.extend(1);
1979 l_usgIdx := x_resTbls.usgStartDate.count;
1980
1981 x_resTbls.usgStartIdx(p_idx) := nvl(x_resTbls.usgStartIdx(p_idx), l_usgIdx);
1982 x_resTbls.usgEndIdx(p_idx) := l_usgIdx;
1983
1984 x_resTbls.usgStartDate(l_usgIdx) := l_shifts.startDate(i);
1985 --resource consumed until end of the shift
1986 x_resTbls.usgEndDate(l_usgIdx) := l_toDate;
1987
1988 if (l_logLevel <= wip_constants.full_logging) then
1989 wip_logger.log('exhausted shift. remaining usage(HRS) is ' || round(l_remUsage*24, g_precision), l_retStatus);
1990 wip_logger.log('usage:' || to_char(x_resTbls.usgStartDate(l_usgIdx), g_logDateFmt) || ' - ' ||
1991 to_char(x_resTbls.usgEndDate(l_usgIdx), g_logDateFmt), l_retStatus);
1992 wip_logger.log('usgIdx:' || l_usgIdx, l_retStatus);
1993 end if;
1994 end if;
1995 <<NO_FULFILL_USAGE>>
1996 null;
1997 end loop;
1998
1999 --if the resource wasn't scheduled, increment the date and keep going.
2000 if(not l_resourceScheduled) then
2001 l_cursorEndDate := l_cursorEndDate - g_dateCursorLen;
2002
2003 --if the next start date is after the end of the calendar, then we can't schedule anything
2004 if(l_cursorEndDate < p_minDate) then
2005 if (l_logLevel <= wip_constants.full_logging) then
2006 wip_logger.log('exhausted calendar. remaining usage(HRS) is ' || round(l_remUsage*24, g_precision), l_retStatus);
2007 end if;
2008 fnd_message.set_name('WIP', 'WIP_NO_CALENDAR');
2009 fnd_msg_pub.add;
2010 raise fnd_api.g_exc_unexpected_error;
2011 end if;
2012 end if;
2013 end loop;
2014
2015 --resource usages are in reverse chronological order. Flip them so they go start to end.
2016 i := x_resTbls.usgStartIdx(p_idx);
2017 j := x_resTbls.usgEndIdx(p_idx);
2018 if (l_logLevel <= wip_constants.full_logging) then
2019 wip_logger.log('i: ' || i || '; j: ' || j, l_retStatus);
2020 end if;
2021
2022 while(j > i) loop
2023 l_tmpUsgStartDate := x_resTbls.usgStartDate(j);
2024 l_tmpUsgEndDate := x_resTbls.usgEndDate(j);
2025 x_resTbls.usgStartDate(j) := x_resTbls.usgStartDate(i);
2026 x_resTbls.usgEndDate(j) := x_resTbls.usgEndDate(i);
2027 x_resTbls.usgStartDate(i) := l_tmpUsgStartDate;
2028 x_resTbls.usgEndDate(i) := l_tmpUsgEndDate;
2029 j := j-1;
2030 i := i+1;
2031 end loop;
2032 for i in x_resTbls.usgStartIdx(p_idx)..x_resTbls.usgEndIdx(p_idx) loop
2033 x_resTbls.usgCumMinProcTime(i) := l_prevProcTime + (24*60)*(x_resTbls.usgEndDate(i)-x_resTbls.usgStartDate(i));
2034 l_prevProcTime := x_resTbls.usgCumMinProcTime(i);
2035 end loop;
2036
2037 if(l_logLevel <= wip_constants.trace_logging) then
2038 wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.backwardSchResource',
2039 p_procReturnStatus => null,
2040 p_msg => 'success',
2041 x_returnStatus => l_retStatus);
2042 end if;
2043 exception
2044 when fnd_api.g_exc_unexpected_error then
2045 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
2046 if(l_logLevel <= wip_constants.trace_logging) then
2047 wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.backwardSchResource',
2048 p_procReturnStatus => x_returnStatus,
2049 p_msg => 'error',
2050 x_returnStatus => l_retStatus);
2051 end if;
2052 when others then
2056 p_error_text => SQLERRM);
2053 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
2054 fnd_msg_pub.add_exc_msg(p_pkg_name => 'wip_infResSched_grp',
2055 p_procedure_name => 'backwardSchResource',
2057
2058 if(l_logLevel <= wip_constants.trace_logging) then
2059 wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.backwardSchResource',
2060 p_procReturnStatus => x_returnStatus,
2061 p_msg => 'unexp error:' || SQLERRM,
2062 x_returnStatus => l_retStatus);
2063 end if;
2064 end backwardSchResource;
2065
2066 procedure forwardScheduleBatch(p_orgID in number,
2067 p_repLineID in number,
2068 p_range in num_tbl_t,
2069 p_schedFlag in number,
2070 p_startIdx in number,
2071 x_resTbls in out nocopy op_res_rectbl_t,
2072 x_returnStatus out nocopy varchar2) is
2073 j number;
2074 l_minStartDate DATE;
2075 l_logLevel number := fnd_log.g_current_runtime_level;
2076 l_params wip_logger.param_tbl_t;
2077 l_retStatus VARCHAR2(1);
2078 begin
2079 if (l_logLevel <= wip_constants.trace_logging) then
2080 l_params(1).paramName := 'p_orgID';
2081 l_params(1).paramValue := p_orgID;
2082 l_params(2).paramName := 'p_repLineID';
2083 l_params(2).paramValue := p_repLineID;
2084 l_params(3).paramName := 'p_range(1)';
2085 l_params(3).paramValue := p_range(1);
2086 l_params(4).paramName := 'p_range(2)';
2087 l_params(4).paramValue := p_range(2);
2088 l_params(5).paramName := 'p_schedFlag';
2089 l_params(5).paramValue := p_schedFlag;
2090 l_params(6).paramName := 'p_startIdx';
2091 l_params(6).paramValue := p_startIdx;
2092
2093 wip_logger.entryPoint(p_procName => 'wip_infResSched_grp.forwardScheduleBatch',
2094 p_params => l_params,
2095 x_returnStatus => x_returnStatus);
2096 end if;
2097
2098 j := p_startIdx;
2099 l_minStartDate := x_resTbls.startDate(p_startIdx);
2100 --now find the min start date in the simultaneous batch and forward schedule
2101 --all resources in the batch from that point.
2102 while(getNextResIdx(p_range, p_schedFlag, g_forward, x_resTbls, j)) loop
2103 if(x_resTbls.schedSeqNum(j) = x_resTbls.schedSeqNum(p_startIdx) and
2104 x_resTbls.opSeqNum(j) = x_resTbls.opSeqNum(p_startIdx)) then
2105 --calculate min start date
2106 l_minStartDate := least(l_minStartDate, x_resTbls.startDate(j));
2107 if(l_logLevel <= wip_constants.full_logging) then
2108 wip_logger.log('resID:' || x_resTbls.resID(j), l_retStatus);
2109 wip_logger.log('res start date:' || to_char(x_resTbls.startDate(j), g_logDateFmt), l_retStatus);
2110 wip_logger.log('min start date:' || to_char(l_minStartDate, g_logDateFmt), l_retStatus);
2111 end if;
2112 --clear backward scheduled times
2113 x_resTbls.usgStartIdx(j) := null;
2114 x_resTbls.usgEndIdx(j) := null;
2115 else
2116 j := j - 1;--decrement j to previous resource for forwardSchedule stmt below...
2117 exit;
2118 end if;
2119 end loop;
2120 forwardSchedule(p_orgID => p_orgID,
2121 p_repLineID => p_repLineID,
2122 p_startDate => l_minStartDate,
2123 p_range => num_tbl_t(p_startIdx, j),
2124 p_schedFlag => p_schedFlag,
2125 x_resTbls => x_resTbls,
2126 x_returnStatus => x_returnStatus);
2127
2128 if(l_logLevel <= wip_constants.trace_logging) then
2129 wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.forwardScheduleBatch',
2130 p_procReturnStatus => x_returnStatus,
2131 p_msg => 'success?',
2132 x_returnStatus => l_retStatus);
2133 end if;
2134 end forwardScheduleBatch;
2135
2136
2137 procedure backwardSchedule(p_orgID IN NUMBER,
2138 p_repLineID in NUMBER := null,
2139 p_endDate in DATE,
2140 p_range in num_tbl_t,
2141 p_schedFlag in number,
2142 x_resTbls in out NOCOPY OP_RES_RECTBL_T,
2143 x_returnStatus OUT NOCOPY VARCHAR2) is
2144 l_resEndDate DATE;
2145 l_logLevel NUMBER := fnd_log.g_current_runtime_level;
2146 l_params wip_logger.param_tbl_t;
2147 l_retStatus VARCHAR2(1);
2148 l_fromTime NUMBER;
2149 l_minDate DATE;
2150 l_shiftStartDate DATE;
2151 l_currSchedSeqNum NUMBER;
2152 l_prevResIdx NUMBER;
2153 i number;
2154 j number;
2155 l_schedulingBatch boolean := false;
2156 l_doneSchedBatch boolean := false;
2157 l_forSchedRange num_tbl_t := num_tbl_t(null, null);
2158 begin
2159 if (l_logLevel <= wip_constants.trace_logging) then
2160 l_params(1).paramName := 'p_orgID';
2161 l_params(1).paramValue := p_orgID;
2162 l_params(2).paramName := 'p_repLineID';
2163 l_params(2).paramValue := p_repLineID;
2167 l_params(4).paramValue := p_range(1);
2164 l_params(3).paramName := 'p_endDate';
2165 l_params(3).paramValue := to_char(p_endDate, g_logDateFmt);
2166 l_params(4).paramName := 'p_range(1)';
2168 l_params(5).paramName := 'p_range(2)';
2169 l_params(5).paramValue := p_range(2);
2170 l_params(6).paramName := 'p_schedFlag';
2171 l_params(6).paramValue := p_schedFlag;
2172
2173 wip_logger.entryPoint(p_procName => 'wip_infResSched_grp.backwardSchedule',
2174 p_params => l_params,
2175 x_returnStatus => x_returnStatus);
2176 end if;
2177 --get the maximum date
2178
2179 select bc.calendar_start_date
2180 into l_minDate
2181 from bom_calendars bc, mtl_parameters mp
2182 where mp.organization_id = p_orgID
2183 and mp.calendar_code = bc.calendar_code;
2184
2185 while(getNextResIdx(p_range, p_schedFlag, g_backward, x_resTbls, i)) loop
2186 if (l_logLevel <= wip_constants.full_logging) then
2187 wip_logger.log('begin scheduling resource:' || x_resTbls.resID(i), l_retStatus);
2188 wip_logger.log(' operation:' || x_resTbls.opSeqNum(i), l_retStatus);
2189 wip_logger.log(' schedule seq num:' || x_resTbls.schedSeqNum(i), l_retStatus);
2190 wip_logger.log(' sched flag:' || x_resTbls.schedFlag(i), l_retStatus);
2191 wip_logger.log(' total usage (HRS):' || round(x_resTbls.totalDaysUsg(i)*24, g_precision), l_retStatus);
2192
2193 if(l_prevResIdx is not null) then
2194 wip_logger.log('prev sched seq num is:' || x_resTbls.schedSeqNum(l_prevResIdx), l_retStatus);
2195 wip_logger.log('prev op seq num is:' || x_resTbls.opSeqNum(l_prevResIdx), l_retStatus);
2196 end if;
2197 end if;
2198
2199 l_doneSchedBatch := false;
2200 --scheduling simultaneous
2201 if(l_prevResIdx is not null and
2202 x_resTbls.schedSeqNum(i) = x_resTbls.schedSeqNum(l_prevResIdx) and
2203 x_resTbls.opSeqNum(i) = x_resTbls.opSeqNum(l_prevResIdx)) then
2204 l_schedulingBatch := true;
2205 if(l_logLevel <= wip_constants.full_logging) then
2206 wip_logger.log('starting batch', l_retStatus);
2207 end if;
2208
2209 --just finished scheduling batch
2210 elsif(l_schedulingBatch) then
2211 if(l_logLevel <= wip_constants.full_logging) then
2212 wip_logger.log('done bkwd scheduling batch, now fwd sched', l_retStatus);
2213 end if;
2214
2215 l_schedulingBatch := false;
2216 l_doneSchedBatch := true;
2217
2218 forwardScheduleBatch(p_orgID => p_orgID,
2219 p_repLineID => p_repLineID,
2220 p_range => p_range,
2221 p_schedFlag => p_schedFlag,
2222 p_startIdx => l_prevResIdx,
2223 x_resTbls => x_resTbls,
2224 x_returnStatus => x_returnStatus);
2225 if(x_returnStatus <> fnd_api.g_ret_sts_success) then
2226 if(l_logLevel <= wip_constants.full_logging) then
2227 wip_logger.log('simult batch scheduling failed', l_retStatus);
2228 end if;
2229 raise fnd_api.g_exc_unexpected_error;
2230 end if;
2231 end if;
2232
2233 if(l_prevResIdx is null) then
2234 l_resEndDate := p_EndDate;
2235
2236 --if scheduling simultaneous, no need to get new end date, just use the previous end date
2237 elsif(not l_schedulingBatch) then
2238 l_resEndDate := getEndDate(p_range, p_schedFlag, x_resTbls, i, l_doneSchedBatch, l_prevResIdx);
2239 end if;
2240
2241 backwardSchResource(p_orgID, l_resEndDate, l_minDate, i, p_repLineID, x_resTbls, x_returnStatus);
2242 if(x_returnStatus <> fnd_api.g_ret_sts_success) then
2243 if(l_logLevel <= wip_constants.full_logging) then
2244 wip_logger.log('backward schedule failed', l_retStatus);
2245 end if;
2246 raise fnd_api.g_exc_unexpected_error;
2247 end if;
2248
2249 l_prevResIdx := i;
2250 end loop;
2251
2252 if(l_schedulingBatch) then
2253 if(l_logLevel <= wip_constants.full_logging) then
2254 wip_logger.log('done bkwd scheduling last batch, now fwd sched', l_retStatus);
2255 end if;
2256 forwardScheduleBatch(p_orgID => p_orgID,
2257 p_repLineID => p_repLineID,
2258 p_range => p_range,
2259 p_schedFlag => p_schedFlag,
2260 p_startIdx => l_prevResIdx,
2261 x_resTbls => x_resTbls,
2262 x_returnStatus => x_returnStatus);
2263 if(x_returnStatus <> fnd_api.g_ret_sts_success) then
2264 if(l_logLevel <= wip_constants.full_logging) then
2265 wip_logger.log('final simult batch scheduling failed', l_retStatus);
2266 end if;
2267 raise fnd_api.g_exc_unexpected_error;
2268 end if;
2269 end if;
2270
2271 if(l_logLevel <= wip_constants.trace_logging) then
2272 wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.backwardSchedule',
2273 p_procReturnStatus => x_returnStatus,
2274 p_msg => 'success',
2275 x_returnStatus => l_retStatus);
2279 when fnd_api.g_exc_unexpected_error then
2276 end if;
2277
2278 exception
2280 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
2281 if(l_logLevel <= wip_constants.trace_logging) then
2282 wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.backwardSchedule',
2283 p_procReturnStatus => x_returnStatus,
2284 p_msg => 'error',
2285 x_returnStatus => l_retStatus);
2286 end if;
2287 when others then
2288 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
2289 fnd_msg_pub.add_exc_msg(p_pkg_name => 'wip_infResSched_grp',
2290 p_procedure_name => 'backwardSchedule',
2291 p_error_text => SQLERRM);
2292
2293 if(l_logLevel <= wip_constants.trace_logging) then
2294 wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.backwardSchedule',
2295 p_procReturnStatus => x_returnStatus,
2296 p_msg => 'unexp error:' || SQLERRM,
2297 x_returnStatus => l_retStatus);
2298 end if;
2299 end backwardSchedule;
2300
2301
2302 procedure schedulePriorResources(p_orgID IN NUMBER,
2303 p_repLineID NUMBER,
2304 p_opTbl in op_tbl_t,
2305 x_resTbls IN OUT NOCOPY OP_RES_RECTBL_T,
2306 x_returnStatus OUT NOCOPY VARCHAR2) is
2307 l_retStatus VARCHAR2(1);
2308 l_params wip_logger.param_tbl_t;
2309 l_logLevel NUMBER := fnd_log.g_current_runtime_level;
2310 begin
2311 if(l_logLevel <= wip_constants.trace_logging) then
2312 l_params(1).paramName := 'p_orgID';
2313 l_params(1).paramValue := p_orgID;
2314 l_params(2).paramName := 'p_repLineID';
2315 l_params(2).paramValue := p_repLineID;
2316
2317 wip_logger.entryPoint(p_procName => 'wip_infResSched_grp.schedulePriorResources',
2318 p_params => l_params,
2319 x_returnStatus => l_retStatus);
2320 end if;
2321
2322 x_returnStatus := fnd_api.g_ret_sts_success;
2323
2324 for i in 1..p_opTbl.count loop
2325 if(p_opTbl(i).priorsExist) then
2326 backwardSchedule(p_orgID => p_orgID,
2327 p_repLineID => p_repLineID,
2328 p_EndDate => p_opTbl(i).startDate,
2329 p_range => num_tbl_t(p_opTbl(i).resStartIdx, p_opTbl(i).resEndIdx),
2330 p_schedFlag => wip_constants.sched_prior,
2331 x_resTbls => x_resTbls,
2332 x_returnStatus => x_returnStatus);
2333 if(x_returnStatus <> fnd_api.g_ret_sts_success) then
2334 raise fnd_api.g_exc_unexpected_error;
2335 end if;
2336 end if;
2337 end loop;
2338
2339 if(l_logLevel <= wip_constants.trace_logging) then
2340 wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.schedulePriorResources',
2341 p_procReturnStatus => x_returnStatus,
2342 p_msg => 'success',
2343 x_returnStatus => l_retStatus);
2344 end if;
2345 exception
2346 when fnd_api.g_exc_unexpected_error then
2347 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
2348 if(l_logLevel <= wip_constants.trace_logging) then
2349 wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.schedulePriorResources',
2350 p_procReturnStatus => x_returnStatus,
2351 p_msg => 'backward scheduling failed',
2352 x_returnStatus => l_retStatus);
2353 end if;
2354 when others then
2355 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
2356 if(l_logLevel <= wip_constants.trace_logging) then
2357 wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.schedulePriorResources',
2358 p_procReturnStatus => x_returnStatus,
2359 p_msg => 'unexp error:' || SQLERRM,
2360 x_returnStatus => l_retStatus);
2361 end if;
2362 end schedulePriorResources;
2363
2364 procedure scheduleNextResources(p_orgID IN NUMBER,
2365 p_repLineID NUMBER,
2366 p_opTbl IN OP_TBL_T,
2367 x_resTbls IN OUT NOCOPY OP_RES_RECTBL_T,
2368 x_returnStatus OUT NOCOPY VARCHAR2) is
2369
2370 l_retStatus VARCHAR2(1);
2371 l_params wip_logger.param_tbl_t;
2372 l_logLevel NUMBER := fnd_log.g_current_runtime_level;
2373 begin
2374 if(l_logLevel <= wip_constants.trace_logging) then
2375 l_params(1).paramName := 'p_orgID';
2376 l_params(1).paramValue := p_orgID;
2377 l_params(2).paramName := 'p_repLineID';
2378 l_params(2).paramValue := p_repLineID;
2379
2380 wip_logger.entryPoint(p_procName => 'wip_infResSched_grp.scheduleNextResources',
2381 p_params => l_params,
2382 x_returnStatus => l_retStatus);
2383 end if;
2384
2385 x_returnStatus := fnd_api.g_ret_sts_success;
2386
2387 for i in 1..p_opTbl.count loop
2388 if(p_opTbl(i).nextsExist) then
2392 p_range => num_tbl_t(p_opTbl(i).resStartIdx, p_opTbl(i).resEndIdx),
2389 forwardSchedule(p_orgID => p_orgID,
2390 p_repLineID => p_repLineID,
2391 p_startDate => p_opTbl(i).endDate,
2393 p_schedFlag => wip_constants.sched_next,
2394 x_resTbls => x_resTbls,
2395 x_returnStatus => x_returnStatus);
2396 if(x_returnStatus <> fnd_api.g_ret_sts_success) then
2397 raise fnd_api.g_exc_unexpected_error;
2398 end if;
2399 end if;
2400 end loop;
2401
2402 if(l_logLevel <= wip_constants.trace_logging) then
2403 wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.scheduleNextResources',
2404 p_procReturnStatus => x_returnStatus,
2405 p_msg => 'success',
2406 x_returnStatus => l_retStatus);
2407 end if;
2408 exception
2409 when fnd_api.g_exc_unexpected_error then
2410 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
2411 if(l_logLevel <= wip_constants.trace_logging) then
2412 wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.scheduleNextResources',
2413 p_procReturnStatus => x_returnStatus,
2414 p_msg => 'backward scheduling failed',
2415 x_returnStatus => l_retStatus);
2416 end if;
2417 when others then
2418 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
2419 fnd_msg_pub.add_exc_msg(p_pkg_name => 'wip_infResSched_grp',
2420 p_procedure_name => 'scheduleNextResources',
2421 p_error_text => SQLERRM);
2422
2423 if(l_logLevel <= wip_constants.trace_logging) then
2424 wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.scheduleNextResources',
2425 p_procReturnStatus => x_returnStatus,
2426 p_msg => 'unexp error:' || SQLERRM,
2427 x_returnStatus => l_retStatus);
2428 end if;
2429 end scheduleNextResources;
2430
2431
2432 procedure resolvePriorExceptions(p_orgID IN NUMBER,
2433 p_repLineID IN NUMBER,
2434 p_startDate IN DATE,
2435 x_resTbls IN OUT NOCOPY OP_RES_RECTBL_T,
2436 x_returnStatus OUT NOCOPY VARCHAR2) is
2437 l_logLevel NUMBER := fnd_log.g_current_runtime_level;
2438 l_params wip_logger.param_tbl_t;
2439 l_retStatus VARCHAR2(1);
2440 l_opSeqNum NUMBER := null;
2441 l_opStartDate DATE;
2442 l_count NUMBER;
2443 l_range num_tbl_t := num_tbl_t(1, x_resTbls.resID.count);
2444 l_opRange num_tbl_t := num_tbl_t(null,null);
2445 i number;
2446 l_exceptionExists boolean := false;
2447 l_errMsg VARCHAR2(200);
2448 begin
2449 x_returnStatus := fnd_api.g_ret_sts_success;
2450 if(l_logLevel <= wip_constants.trace_logging) then
2451 l_params(1).paramName := 'p_orgID';
2452 l_params(1).paramValue := p_orgID;
2453 l_params(2).paramName := 'p_repLineID';
2454 l_params(2).paramValue := p_repLineID;
2455 l_params(3).paramName := 'p_startDate';
2456 l_params(3).paramValue := to_char(p_startDate, g_logDateFmt);
2457 wip_logger.entryPoint(p_procName => 'wip_infResSched_grp.resolvePriorExceptions',
2458 p_params => l_params,
2459 x_returnStatus => x_returnStatus);
2460 end if;
2461 --this loop finds an exception
2462 while(getNextResIdx(l_range, wip_constants.sched_prior, g_forward, x_resTbls, i)) loop
2463 --if we have already found an exception and moved on to the next op, stop
2464 --and reschedule based on the current info
2465 if(l_exceptionExists) then
2466 if(l_opSeqNum <> x_resTbls.opSeqNum(i)) then
2467 exit;
2468 else
2469 l_opRange(2) := i; --prior resource is in same op, extend the schedule range
2470 end if;
2471 else --no exception found yet
2472
2473 --assume current op will contain an exception.
2474 if(l_opSeqNum is null or l_opSeqNum <> x_resTbls.opSeqNum(i)) then
2475 l_opRange(1) := i;
2476 l_opSeqNum := x_resTbls.opSeqNum(i);
2477 end if;
2478
2479 l_opRange(2) := i;
2480 l_exceptionExists := x_resTbls.startDate(i) < p_startDate;
2481 end if;
2482 end loop;
2483
2484 --found a prior resource whose start date is earlier than job start...
2485 if(l_exceptionExists) then
2486
2487 i := null;
2488 --going to reschedule entire job
2489 --delete usages
2490 x_resTbls.usgStartDate.delete;
2491 x_resTbls.usgEndDate.delete;
2492 x_resTbls.usgCumMinProcTime.delete;
2493 x_resTbls.usgStartIdx.delete;
2494 x_resTbls.usgEndIdx.delete;
2495
2496 --delete resource times and reinitialize tables
2497 x_resTbls.startDate.delete;
2498 x_resTbls.endDate.delete;
2499 x_resTbls.startDate := date_tbl_t();
2500 x_resTbls.endDate := date_tbl_t();
2501 x_resTbls.startDate.extend(x_resTbls.resID.count);
2502 x_resTbls.endDate.extend(x_resTbls.resID.count);
2503 --reinitialize usage tables
2504 x_resTbls.usgStartIdx := num_tbl_t();
2508 x_resTbls.usgStartDate := date_tbl_t();
2505 x_resTbls.usgEndIdx := num_tbl_t();
2506 x_resTbls.usgStartIdx.extend(x_resTbls.resID.count);
2507 x_resTbls.usgEndIdx.extend(x_resTbls.resID.count);
2509 x_resTbls.usgEndDate := date_tbl_t();
2510 x_resTbls.usgCumMinProcTime := num_tbl_t();
2511
2512 --forward schedule the prior resources in the 'bad' op from job start
2513 forwardSchedule(p_orgID => p_orgID,
2514 p_repLineID => p_repLineID,
2515 p_startDate => p_startDate,
2516 p_range => l_opRange,
2517 p_schedFlag => wip_constants.sched_prior,
2518 x_resTbls => x_resTbls,
2519 x_returnStatus => x_returnStatus);
2520
2521 if(x_returnStatus <> fnd_api.g_ret_sts_success) then
2522 l_errMsg := 'forward schedule failed';
2523 raise fnd_api.g_exc_unexpected_error;
2524 end if;
2525
2526 --find latest completion date of the prior resources
2527 i := null;
2528 while(getNextResIdx(l_opRange, wip_constants.sched_prior, g_forward, x_resTbls, i)) loop
2529 l_opStartDate := greatest(nvl(l_opStartDate, x_resTbls.endDate(i)), x_resTbls.endDate(i));
2530 end loop;
2531
2532 --now midpoint schedule from the new op start date...This invocation of schedule() will not invoke
2533 --resolvePriorExceptions() because it is operating in midpoint mode
2534 schedule(p_orgID => p_orgID,
2535 p_repLineID => p_repLineID,
2536 p_startDate => l_opStartDate,
2537 p_opSeqNum => x_resTbls.opSeqNum(l_opRange(1)),
2538 p_endDebug => fnd_api.g_false,
2539 x_resTbls => x_resTbls,
2540 x_returnStatus => x_returnStatus);
2541
2542 if(x_returnStatus <> fnd_api.g_ret_sts_success) then
2543 l_errMsg := 'schedule() failed';
2544 raise fnd_api.g_exc_unexpected_error;
2545 end if;
2546
2547 --There still might be other exceptions. Call resolvePriorExceptions recursively. Note that this
2548 --terminates (eventually) because if schedule is called again, the resources we just re-scheduled
2549 --will be moved to an even later date guaranteeing the current exception will not cause any more problems...
2550 resolvePriorExceptions(p_orgID => p_orgID,
2551 p_repLineID => p_repLineID,
2552 p_startDate => p_startDate,
2553 x_resTbls => x_resTbls,
2554 x_returnStatus => x_returnStatus);
2555
2556 if(x_returnStatus <> fnd_api.g_ret_sts_success) then
2557 l_errMsg := 'resolvePriorExceptions Failed';
2558 raise fnd_api.g_exc_unexpected_error;
2559 end if;
2560 end if;
2561
2562 if(l_logLevel <= wip_constants.trace_logging) then
2563 wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.resolvePriorExceptions',
2564 p_procReturnStatus => x_returnStatus,
2565 p_msg => 'success.',
2566 x_returnStatus => l_retStatus);
2567 end if;
2568 exception
2569 when fnd_api.g_exc_unexpected_error then
2570 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
2571 if(l_logLevel <= wip_constants.trace_logging) then
2572 wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.resolvePriorExceptions',
2573 p_procReturnStatus => x_returnStatus,
2574 p_msg => 'errmsg: ' || l_errMsg,
2575 x_returnStatus => l_retStatus);
2576 end if;
2577 when others then
2578 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
2579 fnd_msg_pub.add_exc_msg(p_pkg_name => 'wip_infResSched_grp',
2580 p_procedure_name => 'resolvePriorExceptions',
2581 p_error_text => SQLERRM);
2582
2583 if(l_logLevel <= wip_constants.trace_logging) then
2584 wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.resolvePriorExceptions',
2585 p_procReturnStatus => x_returnStatus,
2586 p_msg => 'unexp error:' || SQLERRM,
2587 x_returnStatus => l_retStatus);
2588 end if;
2589 end resolvePriorExceptions;
2590
2591 procedure resolveNextExceptions(p_orgID IN NUMBER,
2592 p_repLineID IN NUMBER,
2593 p_endDate IN DATE,
2594 x_resTbls IN OUT NOCOPY OP_RES_RECTBL_T,
2595 x_returnStatus OUT NOCOPY VARCHAR2) is
2596 l_logLevel NUMBER := fnd_log.g_current_runtime_level;
2597 l_params wip_logger.param_tbl_t;
2598 l_retStatus VARCHAR2(1);
2599 l_opSeqNum NUMBER := null;
2600 l_opEndDate DATE;
2601 l_count NUMBER;
2602 l_range num_tbl_t := num_tbl_t(1,x_resTbls.resID.count);
2603 l_opRange num_tbl_t := num_tbl_t(null,null);
2604 i number;
2605 l_exceptionExists boolean := false;
2606 l_errMsg VARCHAR2(200);
2607 begin
2608 x_returnStatus := fnd_api.g_ret_sts_success;
2609 if(l_logLevel <= wip_constants.trace_logging) then
2610 l_params(1).paramName := 'p_orgID';
2611 l_params(1).paramValue := p_orgID;
2612 l_params(2).paramName := 'p_repLineID';
2613 l_params(2).paramValue := p_repLineID;
2617 p_params => l_params,
2614 l_params(3).paramName := 'p_endDate';
2615 l_params(3).paramValue := to_char(p_endDate, g_logDateFmt);
2616 wip_logger.entryPoint(p_procName => 'wip_infResSched_grp.resolveNextExceptions',
2618 x_returnStatus => x_returnStatus);
2619 end if;
2620 --this loop finds an exception
2621 while(getNextResIdx(l_range, wip_constants.sched_next, g_backward, x_resTbls, i)) loop
2622 --if we have already found an exception and moved on to the next op, stop
2623 --and reschedule based on the current info
2624 if(l_exceptionExists) then
2625 if(l_opSeqNum <> x_resTbls.opSeqNum(i)) then
2626 exit;
2627 else
2628 l_opRange(1) := i; --next resource is in same op, extend the schedule range
2629 end if;
2630 else --no exception found yet
2631 --assume current op will contain an exception.
2632 if(l_opSeqNum is null or l_opSeqNum <> x_resTbls.opSeqNum(i)) then
2633 l_opRange(2) := i;
2634 l_opSeqNum := x_resTbls.opSeqNum(i);
2635 end if;
2636 l_opRange(1) := i;
2637 l_exceptionExists := x_resTbls.endDate(i) > p_endDate;
2638 if(l_logLevel <= wip_constants.full_logging) then
2639 wip_logger.log('res end date: ' || to_char(x_resTbls.endDate(i), g_logDateFmt), l_retStatus);
2640 wip_logger.log('job end date: ' || to_char(p_endDate, g_logDateFmt), l_retStatus);
2641 end if;
2642 end if;
2643 end loop;
2644
2645 --found a prior resource whose start date is earlier than job start...
2646 if(l_exceptionExists) then
2647 if(l_logLevel <= wip_constants.full_logging) then
2648 wip_logger.log('found exception', l_retStatus);
2649 end if;
2650 i := null;
2651 --going to reschedule entire job
2652 --delete usages
2653 x_resTbls.usgStartDate.delete;
2654 x_resTbls.usgEndDate.delete;
2655 x_resTbls.usgCumMinProcTime.delete;
2656 x_resTbls.usgStartIdx.delete;
2657 x_resTbls.usgEndIdx.delete;
2658
2659 --delete resource times and reinitialize tables
2660 x_resTbls.startDate.delete;
2661 x_resTbls.endDate.delete;
2662 x_resTbls.startDate := date_tbl_t();
2663 x_resTbls.endDate := date_tbl_t();
2664 x_resTbls.startDate.extend(x_resTbls.resID.count);
2665 x_resTbls.endDate.extend(x_resTbls.resID.count);
2666
2667 --reinitialize usage tables
2668 x_resTbls.usgStartIdx := num_tbl_t();
2669 x_resTbls.usgEndIdx := num_tbl_t();
2670 x_resTbls.usgStartIdx.extend(x_resTbls.resID.count);
2671 x_resTbls.usgEndIdx.extend(x_resTbls.resID.count);
2672 x_resTbls.usgStartDate := date_tbl_t();
2673 x_resTbls.usgEndDate := date_tbl_t();
2674 x_resTbls.usgCumMinProcTime := num_tbl_t();
2675
2676 --backward schedule the next resources in the 'bad' op from job start
2677 backwardSchedule(p_orgID => p_orgID,
2678 p_repLineID => p_repLineID,
2679 p_endDate => p_endDate,
2680 p_range => l_opRange,
2681 p_schedFlag => wip_constants.sched_next,
2682 x_resTbls => x_resTbls,
2683 x_returnStatus => x_returnStatus);
2684
2685 if(x_returnStatus <> fnd_api.g_ret_sts_success) then
2686 l_errMsg := 'backward schedule failed';
2687 raise fnd_api.g_exc_unexpected_error;
2688 end if;
2689
2690 --find earliest start date of the next resources
2691 i := null;
2692 while(getNextResIdx(l_opRange, wip_constants.sched_next, g_forward, x_resTbls, i)) loop
2693 l_opEndDate := least(nvl(l_opEndDate, x_resTbls.startDate(i)), x_resTbls.startDate(i));
2694 end loop;
2695
2696 --now midpoint schedule from the new op start date...This invocation of schedule() will not invoke
2697 --resolveNextExceptions() because it is operating in midpoint mode
2698 schedule(p_orgID => p_orgID,
2699 p_repLineID => p_repLineID,
2700 p_endDate => l_opEndDate,
2701 p_opSeqNum => x_resTbls.opSeqNum(l_opRange(1)),
2702 p_endDebug => fnd_api.g_false,
2703 x_resTbls => x_resTbls,
2704 x_returnStatus => x_returnStatus);
2705
2706 if(x_returnStatus <> fnd_api.g_ret_sts_success) then
2707 l_errMsg := 'schedule() failed';
2708 raise fnd_api.g_exc_unexpected_error;
2709 end if;
2710
2711 --There still might be other exceptions. Call resolveNextExceptions recursively. Note that this
2712 --terminates (eventually) because if schedule is called again, the resources we just re-scheduled
2713 --will be moved to an even earlier date guaranteeing the current exception will not cause any more problems...
2714 resolveNextExceptions(p_orgID => p_orgID,
2715 p_repLineID => p_repLineID,
2716 p_endDate => p_endDate,
2717 x_resTbls => x_resTbls,
2718 x_returnStatus => x_returnStatus);
2719
2720 if(x_returnStatus <> fnd_api.g_ret_sts_success) then
2721 l_errMsg := 'resolveNextExceptions Failed';
2722 raise fnd_api.g_exc_unexpected_error;
2723 end if;
2724 end if;
2725
2726 if(l_logLevel <= wip_constants.trace_logging) then
2727 wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.resolveNextExceptions',
2728 p_procReturnStatus => x_returnStatus,
2729 p_msg => 'success.',
2730 x_returnStatus => l_retStatus);
2731 end if;
2732 exception
2733 when fnd_api.g_exc_unexpected_error then
2734 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
2735 if(l_logLevel <= wip_constants.trace_logging) then
2736 wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.resolveNextExceptions',
2737 p_procReturnStatus => x_returnStatus,
2738 p_msg => l_errMsg,
2739 x_returnStatus => l_retStatus);
2740 end if;
2741 when others then
2742 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
2743 fnd_msg_pub.add_exc_msg(p_pkg_name => 'wip_infResSched_grp',
2744 p_procedure_name => 'resolveNextExceptions',
2745 p_error_text => SQLERRM);
2746
2747 if(l_logLevel <= wip_constants.trace_logging) then
2748 wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.resolveNextExceptions',
2749 p_procReturnStatus => x_returnStatus,
2750 p_msg => 'unexp error:' || SQLERRM,
2751 x_returnStatus => l_retStatus);
2752 end if;
2753 end resolveNextExceptions;
2754
2755
2756
2757
2758 procedure dumpResources(p_resTbls IN OP_RES_RECTBL_T) IS
2759 l_logLevel NUMBER := fnd_log.g_current_runtime_level;
2760 l_params wip_logger.param_tbl_t;
2761 l_retStatus VARCHAR2(1);
2762 l_resCode VARCHAR2(10);
2763 begin
2764
2765 if (l_logLevel <= wip_constants.trace_logging) then
2766 wip_logger.entryPoint(p_procName => 'wip_infResSched_grp.dumpResources',
2767 p_params => l_params,
2768 x_returnStatus => l_retStatus);
2769 end if;
2770
2771 if(p_resTbls.resID is null or p_resTbls.resID.count < 1 and
2772 l_logLevel <= wip_constants.trace_logging) then
2773 wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.dumpResources',
2774 p_procReturnStatus => null,
2775 p_msg => 'no resources in table!',
2776 x_returnStatus => l_retStatus);
2777 return;
2778 end if;
2779
2780 if (l_logLevel <= wip_constants.full_logging) then
2781 for i in 1..p_resTbls.resID.count loop
2782 select resource_code
2783 into l_resCode
2784 from bom_resources
2785 where resource_id = p_resTbls.resID(i);
2789 wip_logger.log('+ department:' || p_resTbls.deptID(i), l_retStatus);
2786 wip_logger.log('res:' || l_resCode || '(' || p_resTbls.resID(i) || ')', l_retStatus);
2787 wip_logger.log('+ usage (HRS):' || round(p_resTbls.totalDaysUsg(i)*24, 6),l_retStatus);
2788 wip_logger.log('+ operation:' || p_resTbls.opSeqNum(i), l_retStatus);
2790 wip_logger.log('+ sched seq:' || p_resTbls.schedSeqNum(i), l_retStatus);
2791 wip_logger.log('+ res seq:' || p_resTbls.resSeqNum(i), l_retStatus);
2792 wip_logger.log('+ sched flag:' || p_resTbls.schedFlag(i), l_retStatus);
2793 wip_logger.log('+ 24hrs flag:' || p_resTbls.avail24Flag(i), l_retStatus);
2794 wip_logger.log('+ start date:' || to_char(p_resTbls.startDate(i), g_logDateFmt), l_retStatus);
2795 wip_logger.log('+ end date:' || to_char(p_resTbls.endDate(i), g_logDateFmt), l_retStatus);
2796 wip_logger.log('+ usg st idx:' || p_resTbls.usgStartIdx(i), l_retStatus);
2797 wip_logger.log('+ usg end idx:' || p_resTbls.usgEndIdx(i), l_retStatus);
2798 if(p_resTbls.usgStartIdx(i) is not null) then
2799 for j in p_resTbls.usgStartIdx(i)..p_resTbls.usgEndIdx(i) loop
2800 wip_logger.log(' + usage start date:' || to_char(p_resTbls.usgStartDate(j), g_logDateFmt),l_retStatus);
2801 wip_logger.log(' + usage end date:' || to_char(p_resTbls.usgEndDate(j), g_logDateFmt),l_retStatus);
2802 wip_logger.log(' + cumulative usage:' || p_resTbls.usgCumMinProcTime(j),l_retStatus);
2803 end loop;
2804 end if;
2805 end loop;
2806 end if;
2807 if (l_logLevel <= wip_constants.trace_logging) then
2808 wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.dumpResources',
2809 p_procReturnStatus => null,
2810 p_msg => 'success',
2811 x_returnStatus => l_retStatus);
2812 end if;
2813
2814 exception
2815 when others then
2816 if (l_logLevel <= wip_constants.trace_logging) then
2817 wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.dumpResources',
2818 p_procReturnStatus => null,
2819 p_msg => 'exception:' || SQLERRM,
2820 x_returnStatus => l_retStatus);
2821 end if;
2822 end dumpResources;
2823
2824 procedure scheduleNoResources(p_anchorDate IN DATE,
2825 x_resTbls IN OUT NOCOPY OP_RES_RECTBL_T,
2826 x_returnStatus OUT NOCOPY VARCHAR2) is
2827 l_range num_tbl_t := num_tbl_t(1, x_resTbls.resID.count);
2828 l_prevRange num_tbl_t := num_tbl_t(1, null);
2829 l_nextRange num_tbl_t := num_tbl_t(null, x_resTbls.resID.count);
2830 i NUMBER;
2831 j NUMBER;
2832 l_logLevel NUMBER := fnd_log.g_current_runtime_level;
2833 l_retStatus VARCHAR2(1);
2834 l_params wip_logger.param_tbl_t;
2835 begin
2836 if(l_logLevel <= wip_constants.trace_logging) then
2837 l_params(1).paramName := 'p_anchorDate';
2838 l_params(1).paramValue := to_char(p_anchorDate, g_logDateFmt);
2839 wip_logger.entryPoint(p_procName => 'wip_infResSched_grp.scheduleNoResources',
2840 p_params => l_params,
2841 x_returnStatus => l_retStatus);
2842 end if;
2843 x_returnStatus := fnd_api.g_ret_sts_success;
2844
2845 while(getNextResIdx(l_range, wip_constants.sched_no, g_forward, x_resTbls, i)) loop
2846
2847 if(l_logLevel <= wip_constants.full_logging) then
2848 wip_logger.log('found scheduled no resource at ' || i, l_retStatus);
2849 end if;
2850
2851 l_prevRange(2) := i;
2852 l_nextRange(1) := i;
2853 j := null;
2854 --find previous scheduled yes resource
2855 if(getNextResIdx(l_prevRange, wip_constants.sched_yes, g_backward, x_resTbls, j)) then
2856
2857 if(l_logLevel <= wip_constants.full_logging) then
2858 wip_logger.log('found previous scheduled yes resource at ' || j, l_retStatus);
2859 end if;
2860 x_resTbls.startDate(i) := x_resTbls.endDate(j);
2861
2862 --couldn't find a scheduled yes resource
2863 /* Bug 6954186: Find the next scheduled resource in forward direction*/
2864 elsif(getNextResIdx(l_nextRange, wip_constants.sched_yes, g_forward, x_resTbls, j)) then
2865 if(l_logLevel <= wip_constants.full_logging) then
2866 wip_logger.log('found later scheduled yes resource at ' || j, l_retStatus);
2867 end if;
2868
2869 x_resTbls.startDate(i) := x_resTbls.startDate(j);
2870
2871 else --no scheduled yes resources
2872 x_resTbls.startDate(i) := p_anchorDate;
2873 if(l_logLevel <= wip_constants.full_logging) then
2874 wip_logger.log('no scheduled yes resources found', l_retStatus);
2875 end if;
2876 end if;
2877
2878 x_resTbls.endDate(i) := x_resTbls.startDate(i);
2879 end loop;
2880 if(l_logLevel <= wip_constants.trace_logging) then
2881 wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.scheduleNoResources',
2882 p_procReturnStatus => x_returnStatus,
2883 p_msg => 'success',
2884 x_returnStatus => l_retStatus);
2885 end if;
2886 exception
2887 when others then
2888 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
2889 if(l_logLevel <= wip_constants.trace_logging) then
2890 wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.scheduleNoResources',
2891 p_procReturnStatus => x_returnStatus,
2892 p_msg => 'error: ' || SQLERRM,
2893 x_returnStatus => l_retStatus);
2894 end if;
2895 fnd_msg_pub.add_exc_msg(p_pkg_name => 'wip_infResSched_grp',
2896 p_procedure_name => 'scheduleNoResources',
2899 end wip_infResSched_grp;
2897 p_error_text => SQLERRM);
2898 end scheduleNoResources;