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