[Home] [Help]
109: begin
110: for i in p_startIdx..x_resTbls.resID.count loop
111: if(x_resTbls.opSeqNum(i) = x_resTbls.opSeqNum(p_startIdx) and
112: x_resTbls.schedSeqNum(i) = x_resTbls.schedSeqNum(p_startIdx)) then
113: if(x_resTbls.schedFlag(i) = wip_constants.sched_prior) then
114: x_resTbls.schedFlag(i) := wip_constants.sched_yes;
115: end if;
116: else
117: return i;
110: for i in p_startIdx..x_resTbls.resID.count loop
111: if(x_resTbls.opSeqNum(i) = x_resTbls.opSeqNum(p_startIdx) and
112: x_resTbls.schedSeqNum(i) = x_resTbls.schedSeqNum(p_startIdx)) then
113: if(x_resTbls.schedFlag(i) = wip_constants.sched_prior) then
114: x_resTbls.schedFlag(i) := wip_constants.sched_yes;
115: end if;
116: else
117: return i;
118: end if;
125: procedure removePriorsFromBatch(x_resTbls IN OUT NOCOPY op_res_rectbl_t) is
126: i number := 2;
127: l_curOp NUMBER := x_resTbls.opSeqNum(1);
128: l_curSch NUMBER := x_resTbls.schedSeqNum(1);
129: l_priorExists boolean := x_resTbls.schedFlag(1) = wip_constants.sched_prior;
130: l_otherExists boolean := x_resTbls.schedFlag(1) in (wip_constants.sched_yes, wip_constants.sched_next);
131: l_startIdx NUMBER := 1;
132: l_logLevel NUMBER := fnd_log.g_current_runtime_level;
133: l_retStatus VARCHAR2(1);
126: i number := 2;
127: l_curOp NUMBER := x_resTbls.opSeqNum(1);
128: l_curSch NUMBER := x_resTbls.schedSeqNum(1);
129: l_priorExists boolean := x_resTbls.schedFlag(1) = wip_constants.sched_prior;
130: l_otherExists boolean := x_resTbls.schedFlag(1) in (wip_constants.sched_yes, wip_constants.sched_next);
131: l_startIdx NUMBER := 1;
132: l_logLevel NUMBER := fnd_log.g_current_runtime_level;
133: l_retStatus VARCHAR2(1);
134: l_params wip_logger.param_tbl_t;
132: l_logLevel NUMBER := fnd_log.g_current_runtime_level;
133: l_retStatus VARCHAR2(1);
134: l_params wip_logger.param_tbl_t;
135: begin
136: if(l_logLevel <= wip_constants.trace_logging) then
137: wip_logger.entryPoint(p_procName => 'wip_infResSched_grp.removePriorsFromBatch',
138: p_params => l_params,
139: x_returnStatus => l_retStatus);
140: end if;
141: while(i <= x_resTbls.resID.count) loop
142: --in same batch as the previous res
143: if(x_resTbls.schedSeqNum(i) = l_curSch and
144: x_resTbls.opSeqNum(i) = l_curOp) then
145: l_priorExists := l_priorExists or x_resTbls.schedFlag(i) = wip_constants.sched_prior;
146: l_otherExists := l_otherExists or x_resTbls.schedFlag(i) in (wip_constants.sched_yes, wip_constants.sched_next);
147: if(l_priorExists and l_otherExists) then
148: i := cleanBatch(p_startIdx => l_startIdx, x_resTbls => x_resTbls);
149: end if;
142: --in same batch as the previous res
143: if(x_resTbls.schedSeqNum(i) = l_curSch and
144: x_resTbls.opSeqNum(i) = l_curOp) then
145: l_priorExists := l_priorExists or x_resTbls.schedFlag(i) = wip_constants.sched_prior;
146: l_otherExists := l_otherExists or x_resTbls.schedFlag(i) in (wip_constants.sched_yes, wip_constants.sched_next);
147: if(l_priorExists and l_otherExists) then
148: i := cleanBatch(p_startIdx => l_startIdx, x_resTbls => x_resTbls);
149: end if;
150:
152: else
153: l_curOp := x_resTbls.opSeqNum(i);
154: l_curSch := x_resTbls.schedSeqNum(i);
155: l_startIdx := i;
156: l_priorExists := x_resTbls.schedFlag(i) = wip_constants.sched_prior;
157: l_otherExists := x_resTbls.schedFlag(i) in (wip_constants.sched_yes, wip_constants.sched_next);
158: end if;
159: i := i + 1;
160: end loop;
153: l_curOp := x_resTbls.opSeqNum(i);
154: l_curSch := x_resTbls.schedSeqNum(i);
155: l_startIdx := i;
156: l_priorExists := x_resTbls.schedFlag(i) = wip_constants.sched_prior;
157: l_otherExists := x_resTbls.schedFlag(i) in (wip_constants.sched_yes, wip_constants.sched_next);
158: end if;
159: i := i + 1;
160: end loop;
161: if (l_logLevel <= wip_constants.trace_logging) then
157: l_otherExists := x_resTbls.schedFlag(i) in (wip_constants.sched_yes, wip_constants.sched_next);
158: end if;
159: i := i + 1;
160: end loop;
161: if (l_logLevel <= wip_constants.trace_logging) then
162: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.removePriorsFromBatch',
163: p_procReturnStatus => null,
164: p_msg => 'success',
165: x_returnStatus => l_retStatus);
172: l_logLevel NUMBER := fnd_log.g_current_runtime_level;
173: l_retStatus VARCHAR2(1);
174: l_params wip_logger.param_tbl_t;
175: begin
176: if(l_logLevel <= wip_constants.trace_logging) then
177: wip_logger.entryPoint(p_procName => 'wip_infResSched_grp.removePriors',
178: p_params => l_params,
179: x_returnStatus => l_retStatus);
180: end if;
179: x_returnStatus => l_retStatus);
180: end if;
181: for i in 1..x_resTbls.resID.count loop
182: if(l_curOp = x_resTbls.opSeqNum(i)) then
183: if(l_nonPriorExists and x_resTbls.schedFlag(i) = wip_constants.sched_prior) then
184: x_resTbls.schedFlag(i) := wip_constants.sched_yes;
185: end if;
186: l_nonPriorExists := l_nonPriorExists or (x_resTbls.schedFlag(i) not in (wip_constants.sched_prior, wip_constants.sched_no));
187: else
180: end if;
181: for i in 1..x_resTbls.resID.count loop
182: if(l_curOp = x_resTbls.opSeqNum(i)) then
183: if(l_nonPriorExists and x_resTbls.schedFlag(i) = wip_constants.sched_prior) then
184: x_resTbls.schedFlag(i) := wip_constants.sched_yes;
185: end if;
186: l_nonPriorExists := l_nonPriorExists or (x_resTbls.schedFlag(i) not in (wip_constants.sched_prior, wip_constants.sched_no));
187: else
188: l_curOp := x_resTbls.opSeqNum(i);
182: if(l_curOp = x_resTbls.opSeqNum(i)) then
183: if(l_nonPriorExists and x_resTbls.schedFlag(i) = wip_constants.sched_prior) then
184: x_resTbls.schedFlag(i) := wip_constants.sched_yes;
185: end if;
186: l_nonPriorExists := l_nonPriorExists or (x_resTbls.schedFlag(i) not in (wip_constants.sched_prior, wip_constants.sched_no));
187: else
188: l_curOp := x_resTbls.opSeqNum(i);
189: l_nonPriorExists := x_resTbls.schedFlag(i) not in (wip_constants.sched_prior, wip_constants.sched_no);
190: end if;
185: end if;
186: l_nonPriorExists := l_nonPriorExists or (x_resTbls.schedFlag(i) not in (wip_constants.sched_prior, wip_constants.sched_no));
187: else
188: l_curOp := x_resTbls.opSeqNum(i);
189: l_nonPriorExists := x_resTbls.schedFlag(i) not in (wip_constants.sched_prior, wip_constants.sched_no);
190: end if;
191: end loop;
192: if (l_logLevel <= wip_constants.trace_logging) then
193: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.removePriors',
188: l_curOp := x_resTbls.opSeqNum(i);
189: l_nonPriorExists := x_resTbls.schedFlag(i) not in (wip_constants.sched_prior, wip_constants.sched_no);
190: end if;
191: end loop;
192: if (l_logLevel <= wip_constants.trace_logging) then
193: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.removePriors',
194: p_procReturnStatus => null,
195: p_msg => 'success',
196: x_returnStatus => l_retStatus);
203: l_logLevel NUMBER := fnd_log.g_current_runtime_level;
204: l_retStatus VARCHAR2(1);
205: l_params wip_logger.param_tbl_t;
206: begin
207: if(l_logLevel <= wip_constants.trace_logging) then
208: wip_logger.entryPoint(p_procName => 'wip_infResSched_grp.removeNexts',
209: p_params => l_params,
210: x_returnStatus => l_retStatus);
211: end if;
210: x_returnStatus => l_retStatus);
211: end if;
212: for i in reverse 1..x_resTbls.resID.count loop
213: if(l_curOp = x_resTbls.opSeqNum(i)) then
214: if(l_nonNextExists and x_resTbls.schedFlag(i) = wip_constants.sched_next) then
215: x_resTbls.schedFlag(i) := wip_constants.sched_yes;
216: end if;
217: l_nonNextExists := l_nonNextExists or
218: (x_resTbls.schedFlag(i) not in (wip_constants.sched_next, wip_constants.sched_no));
211: end if;
212: for i in reverse 1..x_resTbls.resID.count loop
213: if(l_curOp = x_resTbls.opSeqNum(i)) then
214: if(l_nonNextExists and x_resTbls.schedFlag(i) = wip_constants.sched_next) then
215: x_resTbls.schedFlag(i) := wip_constants.sched_yes;
216: end if;
217: l_nonNextExists := l_nonNextExists or
218: (x_resTbls.schedFlag(i) not in (wip_constants.sched_next, wip_constants.sched_no));
219: else
214: if(l_nonNextExists and x_resTbls.schedFlag(i) = wip_constants.sched_next) then
215: x_resTbls.schedFlag(i) := wip_constants.sched_yes;
216: end if;
217: l_nonNextExists := l_nonNextExists or
218: (x_resTbls.schedFlag(i) not in (wip_constants.sched_next, wip_constants.sched_no));
219: else
220: l_curOp := x_resTbls.opSeqNum(i);
221: l_nonNextExists := x_resTbls.schedFlag(i) not in (wip_constants.sched_no, wip_constants.sched_next);
222: end if;
217: l_nonNextExists := l_nonNextExists or
218: (x_resTbls.schedFlag(i) not in (wip_constants.sched_next, wip_constants.sched_no));
219: else
220: l_curOp := x_resTbls.opSeqNum(i);
221: l_nonNextExists := x_resTbls.schedFlag(i) not in (wip_constants.sched_no, wip_constants.sched_next);
222: end if;
223: end loop;
224: if (l_logLevel <= wip_constants.trace_logging) then
225: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.removeNexts',
220: l_curOp := x_resTbls.opSeqNum(i);
221: l_nonNextExists := x_resTbls.schedFlag(i) not in (wip_constants.sched_no, wip_constants.sched_next);
222: end if;
223: end loop;
224: if (l_logLevel <= wip_constants.trace_logging) then
225: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.removeNexts',
226: p_procReturnStatus => null,
227: p_msg => 'success',
228: x_returnStatus => l_retStatus);
234: l_logLevel NUMBER := fnd_log.g_current_runtime_level;
235: l_retStatus VARCHAR2(1);
236: l_params wip_logger.param_tbl_t;
237: begin
238: if(l_logLevel <= wip_constants.trace_logging) then
239: wip_logger.entryPoint(p_procName => 'wip_infResSched_grp.verifyResources',
240: p_params => l_params,
241: x_returnStatus => l_retStatus);
242: end if;
260:
261: --changes schedule method of prior resources to yes if
262: -- + they are in a simultaneous batch with yes or next resources.
263: removePriorsFromBatch(x_resTbls => x_resTbls);
264: if (l_logLevel <= wip_constants.trace_logging) then
265: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.verifyResources',
266: p_procReturnStatus => null,
267: p_msg => 'success',
268: x_returnStatus => l_retStatus);
273: l_logLevel NUMBER := fnd_log.g_current_runtime_level;
274: l_retStatus VARCHAR2(1);
275: l_params wip_logger.param_tbl_t;
276: begin
277: if(l_logLevel <= wip_constants.trace_logging) then
278: wip_logger.entryPoint(p_procName => 'wip_infResSched_grp.dumpOps',
279: p_params => l_params,
280: x_returnStatus => l_retStatus);
281: end if;
278: wip_logger.entryPoint(p_procName => 'wip_infResSched_grp.dumpOps',
279: p_params => l_params,
280: x_returnStatus => l_retStatus);
281: end if;
282: if(l_logLevel <= wip_constants.full_logging) then
283: for i in 1..p_opTbl.count loop
284: wip_logger.log('op:' || i, l_retStatus);
285: wip_logger.log('startDate:' || to_char(p_opTbl(i).startDate, g_logDateFmt), l_retStatus);
286: wip_logger.log('endDate:' || to_char(p_opTbl(i).endDate, g_logDateFmt), l_retStatus);
296: end if;
297: wip_logger.log('resRange:' || p_opTbl(i).resStartIdx || '-' || p_opTbl(i).resEndIdx, l_retStatus);
298: end loop;
299: end if;
300: if (l_logLevel <= wip_constants.trace_logging) then
301: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.dumpOps',
302: p_procReturnStatus => null,
303: p_msg => 'success',
304: x_returnStatus => l_retStatus);
316: l_logLevel NUMBER := fnd_log.g_current_runtime_level;
317: l_retStatus VARCHAR2(1);
318: l_params wip_logger.param_tbl_t;
319: begin
320: if(l_logLevel <= wip_constants.trace_logging) then
321: l_params(1).paramName := 'p_anchorDate';
322: l_params(1).paramValue := to_char(p_anchorDate, g_logDateFmt);
323: wip_logger.entryPoint(p_procName => 'wip_infResSched_grp.buildOpStructure',
324: p_params => l_params,
331:
332: for i in 1..p_resTbls.resID.count loop
333: if(l_opSeqNum <> p_resTbls.opSeqNum(i)) then
334:
335: if(l_logLevel <= wip_constants.full_logging) then
336: wip_logger.log('new op at resource ' || i, l_retStatus);
337: end if;
338:
339: x_opTbl(j).resEndIdx := i - 1;
346: x_opTbl(j).resEndIdx := null;
347:
348: l_opSeqNum := p_resTbls.opSeqNum(i);
349: end if;
350: x_opTbl(j).priorsExist := x_opTbl(j).priorsExist or p_resTbls.schedFlag(i) = wip_constants.sched_prior;
351: x_opTbl(j).nextsExist := x_opTbl(j).nextsExist or p_resTbls.schedFlag(i) = wip_constants.sched_next;
352: if(p_resTbls.schedFlag(i) = wip_constants.sched_yes) then
353: if(l_firstYesOpIdx is null) then
354: l_firstYesOpIdx := j;
347:
348: l_opSeqNum := p_resTbls.opSeqNum(i);
349: end if;
350: x_opTbl(j).priorsExist := x_opTbl(j).priorsExist or p_resTbls.schedFlag(i) = wip_constants.sched_prior;
351: x_opTbl(j).nextsExist := x_opTbl(j).nextsExist or p_resTbls.schedFlag(i) = wip_constants.sched_next;
352: if(p_resTbls.schedFlag(i) = wip_constants.sched_yes) then
353: if(l_firstYesOpIdx is null) then
354: l_firstYesOpIdx := j;
355: end if;
348: l_opSeqNum := p_resTbls.opSeqNum(i);
349: end if;
350: x_opTbl(j).priorsExist := x_opTbl(j).priorsExist or p_resTbls.schedFlag(i) = wip_constants.sched_prior;
351: x_opTbl(j).nextsExist := x_opTbl(j).nextsExist or p_resTbls.schedFlag(i) = wip_constants.sched_next;
352: if(p_resTbls.schedFlag(i) = wip_constants.sched_yes) then
353: if(l_firstYesOpIdx is null) then
354: l_firstYesOpIdx := j;
355: end if;
356: x_opTbl(j).startDate := least(p_resTbls.startDate(i), nvl(x_opTbl(j).startDate, p_resTbls.startDate(i)));
354: l_firstYesOpIdx := j;
355: end if;
356: x_opTbl(j).startDate := least(p_resTbls.startDate(i), nvl(x_opTbl(j).startDate, p_resTbls.startDate(i)));
357: x_opTbl(j).endDate := greatest(p_resTbls.endDate(i), nvl(x_opTbl(j).endDate, p_resTbls.endDate(i)));
358: if(l_logLevel <= wip_constants.full_logging) then
359: wip_logger.log('op ' || j || '''s start date:' || to_char(x_opTbl(j).startDate, g_logDateFmt), l_retStatus);
360: wip_logger.log('op ' || j || '''s end date:' || to_char(x_opTbl(j).endDate, g_logDateFmt), l_retStatus);
361: end if;
362:
381: end if;
382: end if;
383: end loop;
384:
385: if (l_logLevel <= wip_constants.full_logging) then
386: dumpOps(x_opTbl);
387: end if;
388:
389: if (l_logLevel <= wip_constants.trace_logging) then
385: if (l_logLevel <= wip_constants.full_logging) then
386: dumpOps(x_opTbl);
387: end if;
388:
389: if (l_logLevel <= wip_constants.trace_logging) then
390: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.buildOpStructure',
391: p_procReturnStatus => null,
392: p_msg => 'success',
393: x_returnStatus => l_retStatus);
404: l_params wip_logger.param_tbl_t;
405: l_logLevel NUMBER := fnd_log.g_current_runtime_level;
406: l_foundMidPntOp boolean := false;
407: begin
408: if(l_logLevel <= wip_constants.trace_logging) then
409: l_params(1).paramName := 'p_opSeqNum';
410: l_params(1).paramValue := p_opSeqNum;
411: l_params(2).paramName := 'p_resSeqNum';
412: l_params(2).paramValue := p_resSeqNum;
422: --find the midpoint resource
423: for i in 1..p_resTbls.resID.count loop
424: if(p_opSeqNum = p_resTbls.opSeqNum(i)) then --op matches
425: l_foundMidPntOp := true;
426: if(l_logLevel <= wip_constants.full_logging) then
427: wip_logger.log('op seq matches res ' || i, l_retStatus);
428: end if;
429:
430: if(p_resSeqNum is not null) then
429:
430: if(p_resSeqNum is not null) then
431: if(p_resSeqNum = p_resTbls.resSeqNum(i)) then --res seq matches
432:
433: if(l_logLevel <= wip_constants.full_logging) then
434: wip_logger.log('res seq matches res ' || i, l_retStatus);
435: end if;
436:
437: if(p_isMdPntFwd) then
469: exit; --res seq matched, exit loop
470: end if;
471: else --resource seq was not populated. use op start or end res
472:
473: if(l_logLevel <= wip_constants.full_logging) then
474: wip_logger.log('res seq is null', l_retStatus);
475: end if;
476:
477: if(p_isMdPntFwd) then --forward scheduling midpoint op
487: --if backward scheduling the midpoint op and the first op of the next op was found...
488: if(l_foundMidPntOp and
489: not(p_isMdPntFwd) and
490: p_resTbls.opSeqNum(i) <> p_opSeqNum) then
491: if(l_logLevel <= wip_constants.full_logging) then
492: wip_logger.log('first res past midpoint at idx:' || i, l_retStatus);
493: end if;
494: x_midPntBkwdIdx := i - 1;
495: x_midPntFwdIdx := i;
496: exit;
497: end if;
498:
499: if(p_resTbls.resID.count = i) then
500: if(l_logLevel <= wip_constants.full_logging) then
501: wip_logger.log('backward scheduling everything', l_retStatus);
502: end if;
503:
504: x_midPntBkwdIdx := i;
504: x_midPntBkwdIdx := i;
505: exit;
506: end if;
507: end loop;
508: if(l_logLevel <= wip_constants.trace_logging) then
509: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.findMdPntRes',
510: p_procReturnStatus => null,
511: p_msg => 'fwdIdx:' || x_midPntFwdIdx || '; bkwdIdx:' || x_midPntBkwdIdx,
512: x_returnStatus => l_retStatus);
533: l_range num_tbl_t := num_tbl_t(null,null);
534: l_opTbl op_tbl_t;
535: l_errMsg VARCHAR2(2000);
536: begin
537: if(l_logLevel <= wip_constants.trace_logging) then
538: l_params(1).paramName := 'p_orgID';
539: l_params(1).paramValue := p_orgID;
540: l_params(2).paramName := 'p_repLineID';
541: l_params(2).paramValue := p_repLineID;
554: end if;
555: x_returnStatus := fnd_api.g_ret_sts_success;
556:
557: if(x_resTbls.resID is null or x_resTbls.resID.count < 1) then
558: if (l_logLevel <= wip_constants.trace_logging) then
559: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.schedule',
560: p_procReturnStatus => x_returnStatus,
561: p_msg => 'no resources to schedule!',
562: x_returnStatus => l_retStatus);
580: x_resTbls.usgEndIdx.extend(x_resTbls.resID.count);
581:
582: x_resTbls.startDate.extend(x_resTbls.resID.count);
583: x_resTbls.endDate.extend(x_resTbls.resID.count);
584: if(l_logLevel <= wip_constants.trace_logging) then
585: dumpResources(x_resTbls);
586: end if;
587:
588: verifyResources(x_resTbls => x_resTbls);
586: end if;
587:
588: verifyResources(x_resTbls => x_resTbls);
589:
590: if(l_logLevel <= wip_constants.trace_logging) then
591: dumpResources(x_resTbls);
592: end if;
593:
594: --caller wants to either forward or backward schedule.
599: l_bkwdEndIdx := null;--this line isn''t necessary, but is included for clarity
600: l_startDate := p_startDate;
601: l_endDate := null;--this line isn''t necessary, but is included for clarity
602:
603: if (l_logLevel <= wip_constants.trace_logging) then
604: wip_logger.log(p_msg => 'forward scheduling',
605: x_returnStatus => x_returnStatus);
606: end if;
607:
611: l_bkwdEndIdx := x_resTbls.resID.count;
612: l_startDate := null;--this line isn''t necessary, but is included for clarity
613: l_endDate := p_endDate;
614:
615: if (l_logLevel <= wip_constants.trace_logging) then
616: wip_logger.log(p_msg => 'backward scheduling',
617: x_returnStatus => x_returnStatus);
618: end if;
619: end if;
624: p_isMdPntFwd => p_startDate is not null,
625: x_midPntFwdIdx => l_fwdStIdx,
626: x_midPntBkwdIdx => l_bkwdEndIdx);
627:
628: if (l_logLevel <= wip_constants.trace_logging) then
629: wip_logger.log(p_msg => 'midpoint scheduling',
630: x_returnStatus => x_returnStatus);
631: end if;
632:
651: forwardSchedule(p_orgID => p_orgID,
652: p_repLineID => p_repLineID,
653: p_startDate => l_startDate,
654: p_range => l_range,
655: p_schedFlag => wip_constants.sched_yes,
656: x_resTbls => x_resTbls,
657: x_returnStatus => x_returnStatus);
658:
659: if(x_returnStatus <> fnd_api.g_ret_sts_success) then
660: wip_logger.log('fwdSch failed', l_retStatus);
661: raise fnd_api.g_exc_unexpected_error;
662: end if;
663:
664: if(l_logLevel <= wip_constants.full_logging) then
665: dumpResources(x_resTbls);
666: end if;
667: end if;
668:
674: backwardSchedule(p_orgID => p_orgID,
675: p_repLineID => p_repLineID,
676: p_endDate => l_endDate,
677: p_range => l_range,
678: p_schedFlag => wip_constants.sched_yes,
679: x_resTbls => x_resTbls,
680: x_returnStatus => x_returnStatus);
681: if(x_returnStatus <> fnd_api.g_ret_sts_success) then
682: wip_logger.log('bkwdSch failed', l_retStatus);
712: wip_logger.log('schNextRes failed', l_retStatus);
713: raise fnd_api.g_exc_unexpected_error;
714: end if;
715:
716: if(l_logLevel <= wip_constants.full_logging) then
717: dumpResources(x_resTbls);
718: end if;
719:
720: --if forward scheduling...
748: x_returnStatus => x_returnStatus);
749:
750:
751:
752: if(l_logLevel <= wip_constants.trace_logging) then
753: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.schedule',
754: p_procReturnStatus => x_returnStatus,
755: p_msg => 'success',
756: x_returnStatus => l_retStatus);
760: end if;
761: exception
762: when fnd_api.g_exc_unexpected_error then
763: x_returnStatus := fnd_api.g_ret_sts_unexp_error;
764: if(l_logLevel <= wip_constants.trace_logging) then
765: wip_utilities.get_message_stack(p_msg => l_errMsg,
766: p_delete_stack => fnd_api.g_false);
767:
768: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.schedule',
777: x_returnStatus := fnd_api.g_ret_sts_unexp_error;
778: fnd_msg_pub.add_exc_msg(p_pkg_name => 'wip_infResSched_grp',
779: p_procedure_name => 'schedule',
780: p_error_text => SQLERRM);
781: if(l_logLevel <= wip_constants.trace_logging) then
782: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.schedule',
783: p_procReturnStatus => x_returnStatus,
784: p_msg => 'unexpected error: ' || SQLERRM,
785: x_returnStatus => l_retStatus);
832: l_maxEndDate date;
833: l_logLevel NUMBER := fnd_log.g_current_runtime_level;
834: l_bool boolean;
835: begin
836: if (l_logLevel <= wip_constants.trace_logging) then
837: l_params(1).paramName := 'p_range(1)';
838: l_params(1).paramValue := p_range(1);
839: l_params(2).paramName := 'p_range(2)';
840: l_params(2).paramValue := p_range(2);
858: --latest end date to use as the next resource's start date
859: i := p_curIdx;
860:
861: if(p_doneSchedBatch) then
862: if(l_logLevel <= wip_constants.full_logging) then
863: wip_logger.log('done scheduling batch', l_retStatus);
864: end if;
865: while(getNextResIdx(p_range, p_schedFlag, g_backward, p_resTbls, i)) loop
866: if(l_logLevel <= wip_constants.full_logging) then
862: if(l_logLevel <= wip_constants.full_logging) then
863: wip_logger.log('done scheduling batch', l_retStatus);
864: end if;
865: while(getNextResIdx(p_range, p_schedFlag, g_backward, p_resTbls, i)) loop
866: if(l_logLevel <= wip_constants.full_logging) then
867: wip_logger.log('in loop', l_retStatus);
868: wip_logger.log('resID' || p_resTbls.resID(i), l_retStatus);
869: wip_logger.log('opSeq' || p_resTbls.opSeqNum(i), l_retStatus);
870: wip_logger.log('schSeq' || p_resTbls.schedSeqNum(i), l_retStatus);
872: end if;
873: if(p_resTbls.schedSeqNum(i) = p_resTbls.schedSeqNum(p_prevIdx) and
874: p_resTbls.opSeqNum(i) = p_resTbls.opSeqNum(p_prevIdx)) then
875: l_maxEndDate := greatest(nvl(l_maxEndDate, p_resTbls.endDate(i)), p_resTbls.endDate(i));
876: if(l_logLevel <= wip_constants.full_logging) then
877: wip_logger.log('resource in batch. endDate:' || to_char(p_resTbls.endDate(i), g_logDateFmt), l_retStatus);
878: end if;
879: else
880: if(l_logLevel <= wip_constants.full_logging) then
876: if(l_logLevel <= wip_constants.full_logging) then
877: wip_logger.log('resource in batch. endDate:' || to_char(p_resTbls.endDate(i), g_logDateFmt), l_retStatus);
878: end if;
879: else
880: if(l_logLevel <= wip_constants.full_logging) then
881: wip_logger.log('resource not in batch.', l_retStatus);
882: end if;
883: exit;
884: end if;
887: l_bool := (getNextResIdx(p_range, p_schedFlag, g_backward, p_resTbls, i));
888: l_maxEndDate := p_resTbls.endDate(i);
889: end if;
890:
891: if (l_logLevel <= wip_constants.trace_logging) then
892: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.getStartDate',
893: p_procReturnStatus => to_char(l_maxEndDate),
894: p_msg => 'finished scheduling',
895: x_returnStatus => l_retStatus);
988: l_prevProcTime NUMBER;
989: l_isFirstUsg boolean := true;
990: l_dummy NUMBER; /* Bug 5660475 */
991: begin
992: if (l_logLevel <= wip_constants.trace_logging) then
993: l_params(1).paramName := 'p_orgID';
994: l_params(1).paramValue := p_orgID;
995: l_params(2).paramName := 'p_startDate';
996: l_params(2).paramValue := to_char(p_startDate, g_logDateFmt);
1007: x_returnStatus := fnd_api.g_ret_sts_success;
1008:
1009: /* Fix for bug 5660475: If dealing with shift resource, first check if shifts are setup fine. */
1010: if( p_repLineID is null
1011: and x_resTbls.avail24Flag(p_idx) = wip_constants.no
1012: and x_resTbls.schedFlag(p_idx) <> wip_constants.sched_no) then
1013: wip_logger.log('This is a shift resource. Need to validate shift setup', l_retStatus);
1014: begin
1015: select 1
1008:
1009: /* Fix for bug 5660475: If dealing with shift resource, first check if shifts are setup fine. */
1010: if( p_repLineID is null
1011: and x_resTbls.avail24Flag(p_idx) = wip_constants.no
1012: and x_resTbls.schedFlag(p_idx) <> wip_constants.sched_no) then
1013: wip_logger.log('This is a shift resource. Need to validate shift setup', l_retStatus);
1014: begin
1015: select 1
1016: into l_dummy
1046: x_resTbls.usgStartIdx(p_idx) := null;
1047: x_resTbls.usgEndIdx(p_idx) := null;
1048: loop
1049: exit when l_resourceScheduled;
1050: if (l_logLevel <= wip_constants.full_logging) then
1051: wip_logger.log('cursor start date is' || to_char(l_cursorStartDate, g_logDateFmt), l_retStatus);
1052: wip_logger.log('cursor end date is' || to_char((l_cursorStartDate + g_dateCursorLen - 1/86400), g_logDateFmt), l_retStatus);
1053: end if;
1054:
1053: end if;
1054:
1055: --for v_endDate, subtract a second to avoid overlap between cursors.
1056: if(p_repLineID is not null) then
1057: if(l_logLevel <= wip_constants.full_logging) then
1058: wip_logger.log('scheduling repetitive', l_retStatus);
1059: end if;
1060: open c_repTimes(v_repLineID => p_repLineID,
1061: v_orgID => p_orgID,
1065: bulk collect into l_shifts.shiftNum,
1066: l_shifts.startDate,
1067: l_shifts.endDate;
1068: close c_repTimes;
1069: elsif(x_resTbls.avail24Flag(p_idx) = wip_constants.yes) then
1070: if(l_logLevel <= wip_constants.full_logging) then
1071: wip_logger.log('scheduling 24HR resource', l_retStatus);
1072: end if;
1073: open c_24HrTimes(v_orgID => p_orgID,
1066: l_shifts.startDate,
1067: l_shifts.endDate;
1068: close c_repTimes;
1069: elsif(x_resTbls.avail24Flag(p_idx) = wip_constants.yes) then
1070: if(l_logLevel <= wip_constants.full_logging) then
1071: wip_logger.log('scheduling 24HR resource', l_retStatus);
1072: end if;
1073: open c_24HrTimes(v_orgID => p_orgID,
1074: v_startDate => l_cursorStartDate,
1078: l_shifts.startDate,
1079: l_shifts.endDate;
1080: close c_24HrTimes;
1081: else
1082: if(l_logLevel <= wip_constants.full_logging) then
1083: wip_logger.log('scheduling shift resource', l_retStatus);
1084: end if;
1085: open c_shiftTimes(v_resID => x_resTbls.resID(p_idx),
1086: v_deptID => x_resTbls.deptID(p_idx),
1116: if(l_shifts.endDate(i) < l_shifts.startDate(i)) then --overnight shift
1117: l_shifts.endDate(i) := l_shifts.endDate(i) + 1;
1118: end if;
1119:
1120: if (l_logLevel <= wip_constants.full_logging) then
1121: wip_logger.log('**********shiftNum:' || l_shifts.shiftNum(i), l_retStatus);
1122: wip_logger.log('**shift start date:' || to_char(l_shifts.startDate(i), g_logDateFmt), l_retStatus);
1123: wip_logger.log('****shift end date:' || to_char(l_shifts.endDate(i), g_logDateFmt), l_retStatus);
1124: end if;
1125:
1126: --if shift ends before the requested start date, skip it since none of the shift
1127: --can be used. don't do this in the sql query as it degrades performance
1128: if(l_shifts.endDate(i) < p_startDate) then
1129: if (l_logLevel <= wip_constants.full_logging) then
1130: wip_logger.log('skipping shift (ends before start date)', l_retStatus);
1131: end if;
1132: goto NO_FULFILL_USAGE;--end of loop
1133: end if;
1133: end if;
1134:
1135: --if the shift starts before the start time, adjust the shift length
1136: l_fromDate := greatest(l_shifts.startDate(i), p_startDate);
1137: if (l_logLevel <= wip_constants.full_logging) then
1138: wip_logger.log('calculated start date: ' || to_char(l_fromDate, g_logDateFmt), l_retStatus);
1139: end if;
1140:
1141: l_shiftLen := l_shifts.endDate(i) - l_fromDate;
1140:
1141: l_shiftLen := l_shifts.endDate(i) - l_fromDate;
1142: /*Bug 7015594: If shift start time is same as end time then consider it as 24 hours resource.
1143: This should be only done when 24 hours check is unchecked and resource is not used on repetitive line*/
1144: if(x_resTbls.avail24Flag(p_idx) <> wip_constants.yes AND p_repLineID is null AND l_shiftLen= 0) then
1145: l_shiftLen := 86400;
1146: end if;
1147:
1148: if (l_logLevel <= wip_constants.full_logging) then
1144: if(x_resTbls.avail24Flag(p_idx) <> wip_constants.yes AND p_repLineID is null AND l_shiftLen= 0) then
1145: l_shiftLen := 86400;
1146: end if;
1147:
1148: if (l_logLevel <= wip_constants.full_logging) then
1149: wip_logger.log('shiftLen(HRS) is ' || round(l_shiftLen*24, g_precision), l_retStatus);
1150: end if;
1151:
1152: if(round(l_shiftLen, g_precision) = 0) then
1149: wip_logger.log('shiftLen(HRS) is ' || round(l_shiftLen*24, g_precision), l_retStatus);
1150: end if;
1151:
1152: if(round(l_shiftLen, g_precision) = 0) then
1153: if (l_logLevel <= wip_constants.full_logging) then
1154: wip_logger.log('skipping shift (no usage)', l_retStatus);
1155: end if;
1156: goto NO_FULFILL_USAGE;--end of loop
1157: end if;
1158:
1159:
1160: if(l_startDate is null) then
1161: l_startDate := l_fromDate;
1162: if (l_logLevel <= wip_constants.full_logging) then
1163: wip_logger.log('calculated resource start date:' || to_char(l_startDate, g_logDateFmt), l_retStatus);
1164: end if;
1165: end if;
1166:
1166:
1167: if(round(l_remUsage, g_precision) <= round(l_shiftLen, g_precision)) then
1168: --shift fullfilled resource usage (round to approximately seconds)
1169:
1170: if (l_logLevel <= wip_constants.full_logging) then
1171: wip_logger.log('calculated resource start date:' || to_char(l_startDate, g_logDateFmt), l_retStatus);
1172: end if;
1173: x_resTbls.startDate(p_idx) := l_startDate;
1174: x_resTbls.endDate(p_idx) := l_fromDate + l_remUsage;
1177: x_resTbls.usgEndDate.extend(1);
1178: x_resTbls.usgCumMinProcTime.extend(1);
1179:
1180: l_usgIdx := x_resTbls.usgStartDate.count;
1181: if (l_logLevel <= wip_constants.full_logging) then
1182: wip_logger.log('idx is ' || l_usgIdx, l_retStatus);
1183: wip_logger.log('count is ' || x_resTbls.usgStartIdx.count, l_retStatus);
1184: wip_logger.log('val is ' || x_resTbls.usgStartIdx(p_idx), l_retStatus);
1185: end if;
1191:
1192: --shift fulfilled resource => usage end time is resource end time
1193: x_resTbls.usgEndDate(l_usgIdx) := x_resTbls.endDate(p_idx);
1194: if(l_isFirstUsg) then
1195: if (l_logLevel <= wip_constants.full_logging) then
1196: wip_logger.log('first usage', l_retStatus);
1197: end if;
1198: l_isFirstUsg := false;
1199: l_prevProcTime := 0;
1197: end if;
1198: l_isFirstUsg := false;
1199: l_prevProcTime := 0;
1200: else
1201: if (l_logLevel <= wip_constants.full_logging) then
1202: wip_logger.log('not first usage', l_retStatus);
1203: end if;
1204: l_prevProcTime := x_resTbls.usgCumMinProcTime(l_usgIdx - 1);
1205: end if;
1207: x_resTbls.usgCumMinProcTime(l_usgIdx) := l_prevProcTime +
1208: (24*60)*(x_resTbls.usgEndDate(l_usgIdx) -
1209: x_resTbls.usgStartDate(l_usgIdx));
1210:
1211: if (l_logLevel <= wip_constants.full_logging) then
1212: wip_logger.log('start date is ' || to_char(x_resTbls.startDate(p_idx), g_logDateFmt), l_retStatus);
1213: wip_logger.log('end date is ' || to_char(x_resTbls.endDate(p_idx), g_logDateFmt), l_retStatus);
1214: wip_logger.log('usage:' || to_char(x_resTbls.usgStartDate(l_usgIdx), g_logDateFmt) || ' - ' ||
1215: to_char(x_resTbls.usgEndDate(l_usgIdx), g_logDateFmt), l_retStatus);
1243: end if;
1244: x_resTbls.usgCumMinProcTime(l_usgIdx) := l_prevProcTime +
1245: (24*60)*(x_resTbls.usgEndDate(l_usgIdx) -
1246: x_resTbls.usgStartDate(l_usgIdx));
1247: if (l_logLevel <= wip_constants.full_logging) then
1248: wip_logger.log('exhausted shift. remaining usage(HRS) is ' || round(l_remUsage*24, g_precision), l_retStatus);
1249: wip_logger.log('usage:' || to_char(x_resTbls.usgStartDate(l_usgIdx), g_logDateFmt) || ' - ' ||
1250: to_char(x_resTbls.usgEndDate(l_usgIdx), g_logDateFmt), l_retStatus);
1251: wip_logger.log('cum usage time:' || x_resTbls.usgCumMinProcTime(l_usgIdx), l_retStatus);
1260: l_cursorStartDate := l_cursorStartDate + g_dateCursorLen;
1261:
1262: --if the next start date is after the end of the calendar, then we can't schedule anything
1263: if(l_cursorStartDate > p_maxDate) then
1264: if (l_logLevel <= wip_constants.full_logging) then
1265: wip_logger.log('exhausted calendar. remaining usage(HRS) is ' || round(l_remUsage*24, g_precision), l_retStatus);
1266: end if;
1267: fnd_message.set_name('WIP', 'WIP_NO_CALENDAR');
1268: fnd_msg_pub.add;
1269: raise fnd_api.g_exc_unexpected_error;
1270: end if;
1271: end if;
1272: end loop;
1273: if(l_logLevel <= wip_constants.trace_logging) then
1274: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.forwardSchResource',
1275: p_procReturnStatus => x_returnStatus,
1276: p_msg => 'success',
1277: x_returnStatus => l_retStatus);
1278: end if;
1279: exception
1280: when fnd_api.g_exc_unexpected_error then
1281: x_returnStatus := fnd_api.g_ret_sts_unexp_error;
1282: if(l_logLevel <= wip_constants.trace_logging) then
1283: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.forwardSchResource',
1284: p_procReturnStatus => x_returnStatus,
1285: p_msg => 'error',
1286: x_returnStatus => l_retStatus);
1289: x_returnStatus := fnd_api.g_ret_sts_unexp_error;
1290: fnd_msg_pub.add_exc_msg(p_pkg_name => 'wip_infResSched_grp',
1291: p_procedure_name => 'forwardSchResource',
1292: p_error_text => SQLERRM);
1293: if(l_logLevel <= wip_constants.trace_logging) then
1294: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.forwardSchResource',
1295: p_procReturnStatus => x_returnStatus,
1296: p_msg => 'unexp error: ' || SQLERRM,
1297: x_returnStatus => l_retStatus);
1352: flag BOOLEAN;
1353:
1354: BEGIN
1355:
1356: if (l_logLevel <= wip_constants.trace_logging) then
1357: l_params(1).paramName := 'p_resID';
1358: l_params(1).paramValue := p_resID;
1359: l_params(2).paramName := 'p_deptID';
1360: l_params(2).paramValue := p_deptID;
1379: l_lastRow := x_shifts.shiftNum.LAST;
1380: j := l_firstRow;
1381: flag := FALSE;
1382:
1383: if (l_capacity(i).actionType = wip_constants.DELETE_WKDY) THEN -- delete a working day
1384:
1385: WHILE (j <= l_lastRow) LOOP
1386:
1387: IF ( Trunc(l_capacity(i).fromDate) = Trunc(x_shifts.startDate(j)) ) then
1402: END IF;
1403: j := x_shifts.shiftNum.NEXT(j);
1404: END LOOP;
1405:
1406: ELSIF (l_capacity(i).actionType = wip_constants.ADD_WKDY) THEN -- add a non-working day
1407:
1408: WHILE (j <= l_lastRow) LOOP
1409: IF ( Trunc(l_capacity(i).fromDate) < Trunc(x_shifts.startDate(j)) ) THEN -- add the day just before the shift date
1410: flag := TRUE; -- that is greater than the capacity date
1431: END IF;
1432: j := x_shifts.shiftNum.NEXT(j);
1433: END LOOP;
1434:
1435: ELSIF l_capacity(i).actionType = wip_constants.MODIFY_WKDY THEN -- modify capacity - modify or reduce capacity
1436:
1437: WHILE (j <= l_lastRow) LOOP
1438: IF ( Trunc(x_shifts.startDate(j)) >= Trunc(l_capacity(i).fromDate)
1439: AND Trunc(x_shifts.startDate(j)) <= Trunc(l_capacity(i).toDate) ) THEN
1450:
1451: END IF;
1452: END LOOP; -- end outer loop
1453:
1454: IF(l_logLevel <= wip_constants.trace_logging) then
1455: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.capacityExceptions',
1456: p_procReturnStatus => x_returnStatus,
1457: p_msg => 'success',
1458: x_returnStatus => l_retStatus);
1459: END IF;
1460: EXCEPTION
1461: WHEN fnd_api.g_exc_unexpected_error THEN
1462: x_returnStatus := fnd_api.g_ret_sts_unexp_error;
1463: if(l_logLevel <= wip_constants.trace_logging) then
1464: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.capacityExceptions',
1465: p_procReturnStatus => x_returnStatus,
1466: p_msg => 'error',
1467: x_returnStatus => l_retStatus);
1470: x_returnStatus := fnd_api.g_ret_sts_unexp_error;
1471: fnd_msg_pub.add_exc_msg(p_pkg_name => 'wip_infResSched_grp',
1472: p_procedure_name => 'capacityExceptions',
1473: p_error_text => SQLERRM);
1474: if(l_logLevel <= wip_constants.trace_logging) then
1475: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.capacityExceptions',
1476: p_procReturnStatus => x_returnStatus,
1477: p_msg => 'unexp error: ' || SQLERRM,
1478: x_returnStatus => l_retStatus);
1499: i number;
1500: l_schedulingBatch boolean := false;
1501: l_doneSchedBatch boolean;
1502: begin
1503: if (l_logLevel <= wip_constants.trace_logging) then
1504: l_params(1).paramName := 'p_repLineID';
1505: l_params(1).paramValue := p_repLineID;
1506: l_params(2).paramName := 'p_startDate';
1507: l_params(2).paramValue := to_char(p_startDate, g_logDateFmt);
1523: where mp.organization_id = p_orgID
1524: and mp.calendar_code = bc.calendar_code;
1525:
1526: while(getNextResIdx(p_range, p_schedFlag, g_forward, x_resTbls, i)) loop
1527: if (l_logLevel <= wip_constants.full_logging) then
1528: wip_logger.log('begin scheduling resource:' || x_resTbls.resID(i), l_retStatus);
1529: wip_logger.log(' operation:' || x_resTbls.opSeqNum(i), l_retStatus);
1530: wip_logger.log(' res seq num:' || x_resTbls.resSeqNum(i), l_retStatus);
1531: wip_logger.log(' schedule seq num:' || x_resTbls.schedSeqNum(i), l_retStatus);
1543: if(l_prevResIdx is not null and
1544: x_resTbls.schedSeqNum(i) = x_resTbls.schedSeqNum(l_prevResIdx) and
1545: x_resTbls.opSeqNum(i) = x_resTbls.opSeqNum(l_prevResIdx)) then
1546: l_schedulingBatch := true;
1547: if (l_logLevel <= wip_constants.full_logging) then
1548: wip_logger.log('setting sched batch to true', l_retStatus);
1549: end if;
1550: --just finished scheduling batch
1551: elsif(l_schedulingBatch) then
1550: --just finished scheduling batch
1551: elsif(l_schedulingBatch) then
1552: l_schedulingBatch := false;
1553: l_doneSchedBatch := true;
1554: if (l_logLevel <= wip_constants.full_logging) then
1555: wip_logger.log('setting done sched batch to true', l_retStatus);
1556: end if;
1557: end if;
1558:
1572:
1573: l_prevResIdx := i;
1574: end loop;
1575:
1576: if(l_logLevel <= wip_constants.trace_logging) then
1577: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.forwardSchedule',
1578: p_procReturnStatus => x_returnStatus,
1579: p_msg => 'success',
1580: x_returnStatus => l_retStatus);
1581: end if;
1582: exception
1583: when fnd_api.g_exc_unexpected_error then
1584: x_returnStatus := fnd_api.g_ret_sts_unexp_error;
1585: if(l_logLevel <= wip_constants.trace_logging) then
1586: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.forwardSchedule',
1587: p_procReturnStatus => x_returnStatus,
1588: p_msg => 'error',
1589: x_returnStatus => l_retStatus);
1592: x_returnStatus := fnd_api.g_ret_sts_unexp_error;
1593: fnd_msg_pub.add_exc_msg(p_pkg_name => 'wip_infResSched_grp',
1594: p_procedure_name => 'forwardSchedule',
1595: p_error_text => SQLERRM);
1596: if(l_logLevel <= wip_constants.trace_logging) then
1597: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.forwardSchedule',
1598: p_procReturnStatus => x_returnStatus,
1599: p_msg => 'unexp error: ' || SQLERRM,
1600: x_returnStatus => l_retStatus);
1614: l_minStartDate date;
1615: l_logLevel NUMBER := fnd_log.g_current_runtime_level;
1616: l_bool boolean;
1617: begin
1618: if (l_logLevel <= wip_constants.trace_logging) then
1619: l_params(1).paramName := 'p_range(1)';
1620: l_params(1).paramValue := p_range(1);
1621: l_params(2).paramName := 'p_range(2)';
1622: l_params(2).paramValue := p_range(2);
1640: --latest end date to use as the next resource's start date
1641: i := p_curIdx;
1642:
1643: if(p_doneSchedBatch) then
1644: if(l_logLevel <= wip_constants.full_logging) then
1645: wip_logger.log('done scheduling batch', l_retStatus);
1646: end if;
1647: while(getNextResIdx(p_range, p_schedFlag, g_forward, p_resTbls, i)) loop
1648: if(l_logLevel <= wip_constants.full_logging) then
1644: if(l_logLevel <= wip_constants.full_logging) then
1645: wip_logger.log('done scheduling batch', l_retStatus);
1646: end if;
1647: while(getNextResIdx(p_range, p_schedFlag, g_forward, p_resTbls, i)) loop
1648: if(l_logLevel <= wip_constants.full_logging) then
1649: wip_logger.log('in loop', l_retStatus);
1650: wip_logger.log('resID' || p_resTbls.resID(i), l_retStatus);
1651: wip_logger.log('opSeq' || p_resTbls.opSeqNum(i), l_retStatus);
1652: wip_logger.log('schSeq' || p_resTbls.schedSeqNum(i), l_retStatus);
1654: end if;
1655: if(p_resTbls.schedSeqNum(i) = p_resTbls.schedSeqNum(p_prevIdx) and
1656: p_resTbls.opSeqNum(i) = p_resTbls.opSeqNum(p_prevIdx)) then
1657: l_minStartDate := least(nvl(l_minStartDate, p_resTbls.startDate(i)), p_resTbls.startDate(i));
1658: if(l_logLevel <= wip_constants.full_logging) then
1659: wip_logger.log('resource in batch. startDate:' || to_char(p_resTbls.startDate(i), g_logDateFmt), l_retStatus);
1660: end if;
1661: else
1662: if(l_logLevel <= wip_constants.full_logging) then
1658: if(l_logLevel <= wip_constants.full_logging) then
1659: wip_logger.log('resource in batch. startDate:' || to_char(p_resTbls.startDate(i), g_logDateFmt), l_retStatus);
1660: end if;
1661: else
1662: if(l_logLevel <= wip_constants.full_logging) then
1663: wip_logger.log('resource not in batch.', l_retStatus);
1664: end if;
1665: exit;
1666: end if;
1669: l_bool := (getNextResIdx(p_range, p_schedFlag, g_forward, p_resTbls, i));
1670: l_minStartDate := p_resTbls.startDate(i);
1671: end if;
1672:
1673: if (l_logLevel <= wip_constants.trace_logging) then
1674: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.getEndDate',
1675: p_procReturnStatus => to_char(l_minStartDate),
1676: p_msg => 'finished scheduling',
1677: x_returnStatus => l_retStatus);
1774: l_prevProcTime NUMBER := 0;
1775: l_dummy NUMBER; /* Bug 5660475 */
1776: begin
1777:
1778: if (l_logLevel <= wip_constants.trace_logging) then
1779: l_params(1).paramName := 'p_orgID';
1780: l_params(1).paramValue := p_orgID;
1781: l_params(2).paramName := 'p_endDate';
1782: l_params(2).paramValue := to_char(p_endDate, g_logDateFmt);
1791: x_returnStatus => l_retStatus);
1792: end if;
1793: /* Fix for bug 5660475: If dealing with shift resource, first check if shifts are setup fine. */
1794: if( p_repLineID is null
1795: and x_resTbls.avail24Flag(p_idx) = wip_constants.no
1796: and x_resTbls.schedFlag(p_idx) <> wip_constants.sched_no) then
1797: wip_logger.log('This is a shift resource. Need to validate shift setup', l_retStatus);
1798: begin
1799: select 1
1792: end if;
1793: /* Fix for bug 5660475: If dealing with shift resource, first check if shifts are setup fine. */
1794: if( p_repLineID is null
1795: and x_resTbls.avail24Flag(p_idx) = wip_constants.no
1796: and x_resTbls.schedFlag(p_idx) <> wip_constants.sched_no) then
1797: wip_logger.log('This is a shift resource. Need to validate shift setup', l_retStatus);
1798: begin
1799: select 1
1800: into l_dummy
1831: x_resTbls.usgEndIdx(p_idx) := null;
1832:
1833: loop
1834: exit when l_resourceScheduled;
1835: if (l_logLevel <= wip_constants.full_logging) then
1836: wip_logger.log('cursor start date: ' || to_char((l_cursorEndDate - (g_dateCursorLen - 1/86400)), g_logDateFmt), l_retStatus);
1837: wip_logger.log('cursor end date: ' || to_char(l_cursorEndDate, g_logDateFmt), l_retStatus);
1838: end if;
1839:
1847: bulk collect into l_shifts.shiftNum,
1848: l_shifts.startDate,
1849: l_shifts.endDate;
1850: close c_repTimes;
1851: elsif(x_resTbls.avail24Flag(p_idx) = wip_constants.yes) then
1852: open c_24HrTimes(v_orgID => p_orgID,
1853: v_startDate => l_cursorEndDate - (g_dateCursorLen - 1/86400),
1854: v_endDate => l_cursorEndDate);
1855: fetch c_24HrTimes
1883: for i in 1..l_shifts.shiftNum.count loop
1884: if(l_shifts.endDate(i) < l_shifts.startDate(i)) then --overnight shift
1885: l_shifts.endDate(i) := l_shifts.endDate(i) + 1;
1886: end if;
1887: if (l_logLevel <= wip_constants.full_logging) then
1888:
1889: wip_logger.log('**********shiftNum:' || l_shifts.shiftNum(i), l_retStatus);
1890: wip_logger.log('**shift start date:' || to_char(l_shifts.startDate(i), g_logDateFmt), l_retStatus);
1891: wip_logger.log('****shift end date:' || to_char(l_shifts.endDate(i), g_logDateFmt), l_retStatus);
1893:
1894: --if shift starts after the requested end date, skip it since none of the shift
1895: --can be used. don't do this in the sql query as it degrades performance
1896: if(l_shifts.startDate(i) > p_endDate) then
1897: if (l_logLevel <= wip_constants.full_logging) then
1898: wip_logger.log('skipping shift (starts after end date)', l_retStatus);
1899: end if;
1900: goto NO_FULFILL_USAGE;--end of loop
1901: end if;
1901: end if;
1902:
1903: --if the shift ends before the end time, adjust the shift length
1904: l_toDate := least(l_shifts.endDate(i), p_endDate);
1905: if (l_logLevel <= wip_constants.full_logging) then
1906: wip_logger.log('calculated end date: ' || to_char(l_toDate, g_logDateFmt), l_retStatus);
1907: end if;
1908:
1909: l_shiftLen := l_toDate - l_shifts.startDate(i);
1908:
1909: l_shiftLen := l_toDate - l_shifts.startDate(i);
1910: /*Bug 7015594: If shift start time is same as end time then consider it as 24 hours resource.
1911: This should be only done when 24 hours check is unchecked and resource is not used on repetitive line*/
1912: if(x_resTbls.avail24Flag(p_idx) <> wip_constants.yes AND p_repLineID is null AND l_shiftLen= 0) then
1913: l_shiftLen := 86400;
1914: end if;
1915:
1916:
1913: l_shiftLen := 86400;
1914: end if;
1915:
1916:
1917: if (l_logLevel <= wip_constants.full_logging) then
1918: wip_logger.log('shiftLen(HRS) is ' || round(l_shiftLen*24, g_precision), l_retStatus);
1919: end if;
1920:
1921: if(round(l_shiftLen, g_precision) = 0) then
1918: wip_logger.log('shiftLen(HRS) is ' || round(l_shiftLen*24, g_precision), l_retStatus);
1919: end if;
1920:
1921: if(round(l_shiftLen, g_precision) = 0) then
1922: if (l_logLevel <= wip_constants.full_logging) then
1923: wip_logger.log('skipping shift (no usage)', l_retStatus);
1924: end if;
1925: goto NO_FULFILL_USAGE;--end of loop
1926: end if;
1927:
1928:
1929: if(l_endDate is null) then
1930: l_endDate := l_toDate;
1931: if (l_logLevel <= wip_constants.full_logging) then
1932: wip_logger.log('calculated resource end date:' || to_char(l_endDate, g_logDateFmt), l_retStatus);
1933: end if;
1934: end if;
1935:
1944: x_resTbls.usgEndDate.extend(1);
1945: x_resTbls.usgCumMinProcTime.extend(1);
1946:
1947: l_usgIdx := x_resTbls.usgStartDate.count;
1948: if (l_logLevel <= wip_constants.full_logging) then
1949: wip_logger.log('start idx is ' || x_resTbls.usgStartIdx(p_idx), l_retStatus);
1950: wip_logger.log('end idx is ' || x_resTbls.usgEndIdx(p_idx), l_retStatus);
1951: wip_logger.log('usg idx is ' || l_usgIdx, l_retStatus);
1952: end if;
1957: --shift fulfilled resource => usage start time is resource end time
1958: x_resTbls.usgStartDate(l_usgIdx) := x_resTbls.startDate(p_idx);
1959: x_resTbls.usgEndDate(l_usgIdx) := l_toDate;--l_shifts.endDate(i);
1960:
1961: if (l_logLevel <= wip_constants.full_logging) then
1962: wip_logger.log('start date is ' || to_char(x_resTbls.startDate(p_idx), g_logDateFmt), l_retStatus);
1963: wip_logger.log('end date is ' || to_char(x_resTbls.endDate(p_idx), g_logDateFmt), l_retStatus);
1964: wip_logger.log('usgIdx:' || l_usgIdx, l_retStatus);
1965: wip_logger.log('usage:' || to_char(x_resTbls.usgStartDate(l_usgIdx), g_logDateFmt) || ' - ' ||
1984: x_resTbls.usgStartDate(l_usgIdx) := l_shifts.startDate(i);
1985: --resource consumed until end of the shift
1986: x_resTbls.usgEndDate(l_usgIdx) := l_toDate;
1987:
1988: if (l_logLevel <= wip_constants.full_logging) then
1989: wip_logger.log('exhausted shift. remaining usage(HRS) is ' || round(l_remUsage*24, g_precision), l_retStatus);
1990: wip_logger.log('usage:' || to_char(x_resTbls.usgStartDate(l_usgIdx), g_logDateFmt) || ' - ' ||
1991: to_char(x_resTbls.usgEndDate(l_usgIdx), g_logDateFmt), l_retStatus);
1992: wip_logger.log('usgIdx:' || l_usgIdx, l_retStatus);
2001: l_cursorEndDate := l_cursorEndDate - g_dateCursorLen;
2002:
2003: --if the next start date is after the end of the calendar, then we can't schedule anything
2004: if(l_cursorEndDate < p_minDate) then
2005: if (l_logLevel <= wip_constants.full_logging) then
2006: wip_logger.log('exhausted calendar. remaining usage(HRS) is ' || round(l_remUsage*24, g_precision), l_retStatus);
2007: end if;
2008: fnd_message.set_name('WIP', 'WIP_NO_CALENDAR');
2009: fnd_msg_pub.add;
2014:
2015: --resource usages are in reverse chronological order. Flip them so they go start to end.
2016: i := x_resTbls.usgStartIdx(p_idx);
2017: j := x_resTbls.usgEndIdx(p_idx);
2018: if (l_logLevel <= wip_constants.full_logging) then
2019: wip_logger.log('i: ' || i || '; j: ' || j, l_retStatus);
2020: end if;
2021:
2022: while(j > i) loop
2033: x_resTbls.usgCumMinProcTime(i) := l_prevProcTime + (24*60)*(x_resTbls.usgEndDate(i)-x_resTbls.usgStartDate(i));
2034: l_prevProcTime := x_resTbls.usgCumMinProcTime(i);
2035: end loop;
2036:
2037: if(l_logLevel <= wip_constants.trace_logging) then
2038: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.backwardSchResource',
2039: p_procReturnStatus => null,
2040: p_msg => 'success',
2041: x_returnStatus => l_retStatus);
2042: end if;
2043: exception
2044: when fnd_api.g_exc_unexpected_error then
2045: x_returnStatus := fnd_api.g_ret_sts_unexp_error;
2046: if(l_logLevel <= wip_constants.trace_logging) then
2047: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.backwardSchResource',
2048: p_procReturnStatus => x_returnStatus,
2049: p_msg => 'error',
2050: x_returnStatus => l_retStatus);
2054: fnd_msg_pub.add_exc_msg(p_pkg_name => 'wip_infResSched_grp',
2055: p_procedure_name => 'backwardSchResource',
2056: p_error_text => SQLERRM);
2057:
2058: if(l_logLevel <= wip_constants.trace_logging) then
2059: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.backwardSchResource',
2060: p_procReturnStatus => x_returnStatus,
2061: p_msg => 'unexp error:' || SQLERRM,
2062: x_returnStatus => l_retStatus);
2075: l_logLevel number := fnd_log.g_current_runtime_level;
2076: l_params wip_logger.param_tbl_t;
2077: l_retStatus VARCHAR2(1);
2078: begin
2079: if (l_logLevel <= wip_constants.trace_logging) then
2080: l_params(1).paramName := 'p_orgID';
2081: l_params(1).paramValue := p_orgID;
2082: l_params(2).paramName := 'p_repLineID';
2083: l_params(2).paramValue := p_repLineID;
2103: if(x_resTbls.schedSeqNum(j) = x_resTbls.schedSeqNum(p_startIdx) and
2104: x_resTbls.opSeqNum(j) = x_resTbls.opSeqNum(p_startIdx)) then
2105: --calculate min start date
2106: l_minStartDate := least(l_minStartDate, x_resTbls.startDate(j));
2107: if(l_logLevel <= wip_constants.full_logging) then
2108: wip_logger.log('resID:' || x_resTbls.resID(j), l_retStatus);
2109: wip_logger.log('res start date:' || to_char(x_resTbls.startDate(j), g_logDateFmt), l_retStatus);
2110: wip_logger.log('min start date:' || to_char(l_minStartDate, g_logDateFmt), l_retStatus);
2111: end if;
2124: p_schedFlag => p_schedFlag,
2125: x_resTbls => x_resTbls,
2126: x_returnStatus => x_returnStatus);
2127:
2128: if(l_logLevel <= wip_constants.trace_logging) then
2129: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.forwardScheduleBatch',
2130: p_procReturnStatus => x_returnStatus,
2131: p_msg => 'success?',
2132: x_returnStatus => l_retStatus);
2155: l_schedulingBatch boolean := false;
2156: l_doneSchedBatch boolean := false;
2157: l_forSchedRange num_tbl_t := num_tbl_t(null, null);
2158: begin
2159: if (l_logLevel <= wip_constants.trace_logging) then
2160: l_params(1).paramName := 'p_orgID';
2161: l_params(1).paramValue := p_orgID;
2162: l_params(2).paramName := 'p_repLineID';
2163: l_params(2).paramValue := p_repLineID;
2182: where mp.organization_id = p_orgID
2183: and mp.calendar_code = bc.calendar_code;
2184:
2185: while(getNextResIdx(p_range, p_schedFlag, g_backward, x_resTbls, i)) loop
2186: if (l_logLevel <= wip_constants.full_logging) then
2187: wip_logger.log('begin scheduling resource:' || x_resTbls.resID(i), l_retStatus);
2188: wip_logger.log(' operation:' || x_resTbls.opSeqNum(i), l_retStatus);
2189: wip_logger.log(' schedule seq num:' || x_resTbls.schedSeqNum(i), l_retStatus);
2190: wip_logger.log(' sched flag:' || x_resTbls.schedFlag(i), l_retStatus);
2201: if(l_prevResIdx is not null and
2202: x_resTbls.schedSeqNum(i) = x_resTbls.schedSeqNum(l_prevResIdx) and
2203: x_resTbls.opSeqNum(i) = x_resTbls.opSeqNum(l_prevResIdx)) then
2204: l_schedulingBatch := true;
2205: if(l_logLevel <= wip_constants.full_logging) then
2206: wip_logger.log('starting batch', l_retStatus);
2207: end if;
2208:
2209: --just finished scheduling batch
2207: end if;
2208:
2209: --just finished scheduling batch
2210: elsif(l_schedulingBatch) then
2211: if(l_logLevel <= wip_constants.full_logging) then
2212: wip_logger.log('done bkwd scheduling batch, now fwd sched', l_retStatus);
2213: end if;
2214:
2215: l_schedulingBatch := false;
2222: p_startIdx => l_prevResIdx,
2223: x_resTbls => x_resTbls,
2224: x_returnStatus => x_returnStatus);
2225: if(x_returnStatus <> fnd_api.g_ret_sts_success) then
2226: if(l_logLevel <= wip_constants.full_logging) then
2227: wip_logger.log('simult batch scheduling failed', l_retStatus);
2228: end if;
2229: raise fnd_api.g_exc_unexpected_error;
2230: end if;
2239: end if;
2240:
2241: backwardSchResource(p_orgID, l_resEndDate, l_minDate, i, p_repLineID, x_resTbls, x_returnStatus);
2242: if(x_returnStatus <> fnd_api.g_ret_sts_success) then
2243: if(l_logLevel <= wip_constants.full_logging) then
2244: wip_logger.log('backward schedule failed', l_retStatus);
2245: end if;
2246: raise fnd_api.g_exc_unexpected_error;
2247: end if;
2249: l_prevResIdx := i;
2250: end loop;
2251:
2252: if(l_schedulingBatch) then
2253: if(l_logLevel <= wip_constants.full_logging) then
2254: wip_logger.log('done bkwd scheduling last batch, now fwd sched', l_retStatus);
2255: end if;
2256: forwardScheduleBatch(p_orgID => p_orgID,
2257: p_repLineID => p_repLineID,
2260: p_startIdx => l_prevResIdx,
2261: x_resTbls => x_resTbls,
2262: x_returnStatus => x_returnStatus);
2263: if(x_returnStatus <> fnd_api.g_ret_sts_success) then
2264: if(l_logLevel <= wip_constants.full_logging) then
2265: wip_logger.log('final simult batch scheduling failed', l_retStatus);
2266: end if;
2267: raise fnd_api.g_exc_unexpected_error;
2268: end if;
2267: raise fnd_api.g_exc_unexpected_error;
2268: end if;
2269: end if;
2270:
2271: if(l_logLevel <= wip_constants.trace_logging) then
2272: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.backwardSchedule',
2273: p_procReturnStatus => x_returnStatus,
2274: p_msg => 'success',
2275: x_returnStatus => l_retStatus);
2277:
2278: exception
2279: when fnd_api.g_exc_unexpected_error then
2280: x_returnStatus := fnd_api.g_ret_sts_unexp_error;
2281: if(l_logLevel <= wip_constants.trace_logging) then
2282: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.backwardSchedule',
2283: p_procReturnStatus => x_returnStatus,
2284: p_msg => 'error',
2285: x_returnStatus => l_retStatus);
2289: fnd_msg_pub.add_exc_msg(p_pkg_name => 'wip_infResSched_grp',
2290: p_procedure_name => 'backwardSchedule',
2291: p_error_text => SQLERRM);
2292:
2293: if(l_logLevel <= wip_constants.trace_logging) then
2294: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.backwardSchedule',
2295: p_procReturnStatus => x_returnStatus,
2296: p_msg => 'unexp error:' || SQLERRM,
2297: x_returnStatus => l_retStatus);
2307: l_retStatus VARCHAR2(1);
2308: l_params wip_logger.param_tbl_t;
2309: l_logLevel NUMBER := fnd_log.g_current_runtime_level;
2310: begin
2311: if(l_logLevel <= wip_constants.trace_logging) then
2312: l_params(1).paramName := 'p_orgID';
2313: l_params(1).paramValue := p_orgID;
2314: l_params(2).paramName := 'p_repLineID';
2315: l_params(2).paramValue := p_repLineID;
2326: backwardSchedule(p_orgID => p_orgID,
2327: p_repLineID => p_repLineID,
2328: p_EndDate => p_opTbl(i).startDate,
2329: p_range => num_tbl_t(p_opTbl(i).resStartIdx, p_opTbl(i).resEndIdx),
2330: p_schedFlag => wip_constants.sched_prior,
2331: x_resTbls => x_resTbls,
2332: x_returnStatus => x_returnStatus);
2333: if(x_returnStatus <> fnd_api.g_ret_sts_success) then
2334: raise fnd_api.g_exc_unexpected_error;
2335: end if;
2336: end if;
2337: end loop;
2338:
2339: if(l_logLevel <= wip_constants.trace_logging) then
2340: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.schedulePriorResources',
2341: p_procReturnStatus => x_returnStatus,
2342: p_msg => 'success',
2343: x_returnStatus => l_retStatus);
2344: end if;
2345: exception
2346: when fnd_api.g_exc_unexpected_error then
2347: x_returnStatus := fnd_api.g_ret_sts_unexp_error;
2348: if(l_logLevel <= wip_constants.trace_logging) then
2349: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.schedulePriorResources',
2350: p_procReturnStatus => x_returnStatus,
2351: p_msg => 'backward scheduling failed',
2352: x_returnStatus => l_retStatus);
2352: x_returnStatus => l_retStatus);
2353: end if;
2354: when others then
2355: x_returnStatus := fnd_api.g_ret_sts_unexp_error;
2356: if(l_logLevel <= wip_constants.trace_logging) then
2357: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.schedulePriorResources',
2358: p_procReturnStatus => x_returnStatus,
2359: p_msg => 'unexp error:' || SQLERRM,
2360: x_returnStatus => l_retStatus);
2370: l_retStatus VARCHAR2(1);
2371: l_params wip_logger.param_tbl_t;
2372: l_logLevel NUMBER := fnd_log.g_current_runtime_level;
2373: begin
2374: if(l_logLevel <= wip_constants.trace_logging) then
2375: l_params(1).paramName := 'p_orgID';
2376: l_params(1).paramValue := p_orgID;
2377: l_params(2).paramName := 'p_repLineID';
2378: l_params(2).paramValue := p_repLineID;
2389: forwardSchedule(p_orgID => p_orgID,
2390: p_repLineID => p_repLineID,
2391: p_startDate => p_opTbl(i).endDate,
2392: p_range => num_tbl_t(p_opTbl(i).resStartIdx, p_opTbl(i).resEndIdx),
2393: p_schedFlag => wip_constants.sched_next,
2394: x_resTbls => x_resTbls,
2395: x_returnStatus => x_returnStatus);
2396: if(x_returnStatus <> fnd_api.g_ret_sts_success) then
2397: raise fnd_api.g_exc_unexpected_error;
2398: end if;
2399: end if;
2400: end loop;
2401:
2402: if(l_logLevel <= wip_constants.trace_logging) then
2403: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.scheduleNextResources',
2404: p_procReturnStatus => x_returnStatus,
2405: p_msg => 'success',
2406: x_returnStatus => l_retStatus);
2407: end if;
2408: exception
2409: when fnd_api.g_exc_unexpected_error then
2410: x_returnStatus := fnd_api.g_ret_sts_unexp_error;
2411: if(l_logLevel <= wip_constants.trace_logging) then
2412: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.scheduleNextResources',
2413: p_procReturnStatus => x_returnStatus,
2414: p_msg => 'backward scheduling failed',
2415: x_returnStatus => l_retStatus);
2419: fnd_msg_pub.add_exc_msg(p_pkg_name => 'wip_infResSched_grp',
2420: p_procedure_name => 'scheduleNextResources',
2421: p_error_text => SQLERRM);
2422:
2423: if(l_logLevel <= wip_constants.trace_logging) then
2424: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.scheduleNextResources',
2425: p_procReturnStatus => x_returnStatus,
2426: p_msg => 'unexp error:' || SQLERRM,
2427: x_returnStatus => l_retStatus);
2446: l_exceptionExists boolean := false;
2447: l_errMsg VARCHAR2(200);
2448: begin
2449: x_returnStatus := fnd_api.g_ret_sts_success;
2450: if(l_logLevel <= wip_constants.trace_logging) then
2451: l_params(1).paramName := 'p_orgID';
2452: l_params(1).paramValue := p_orgID;
2453: l_params(2).paramName := 'p_repLineID';
2454: l_params(2).paramValue := p_repLineID;
2458: p_params => l_params,
2459: x_returnStatus => x_returnStatus);
2460: end if;
2461: --this loop finds an exception
2462: while(getNextResIdx(l_range, wip_constants.sched_prior, g_forward, x_resTbls, i)) loop
2463: --if we have already found an exception and moved on to the next op, stop
2464: --and reschedule based on the current info
2465: if(l_exceptionExists) then
2466: if(l_opSeqNum <> x_resTbls.opSeqNum(i)) then
2513: forwardSchedule(p_orgID => p_orgID,
2514: p_repLineID => p_repLineID,
2515: p_startDate => p_startDate,
2516: p_range => l_opRange,
2517: p_schedFlag => wip_constants.sched_prior,
2518: x_resTbls => x_resTbls,
2519: x_returnStatus => x_returnStatus);
2520:
2521: if(x_returnStatus <> fnd_api.g_ret_sts_success) then
2524: end if;
2525:
2526: --find latest completion date of the prior resources
2527: i := null;
2528: while(getNextResIdx(l_opRange, wip_constants.sched_prior, g_forward, x_resTbls, i)) loop
2529: l_opStartDate := greatest(nvl(l_opStartDate, x_resTbls.endDate(i)), x_resTbls.endDate(i));
2530: end loop;
2531:
2532: --now midpoint schedule from the new op start date...This invocation of schedule() will not invoke
2558: raise fnd_api.g_exc_unexpected_error;
2559: end if;
2560: end if;
2561:
2562: if(l_logLevel <= wip_constants.trace_logging) then
2563: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.resolvePriorExceptions',
2564: p_procReturnStatus => x_returnStatus,
2565: p_msg => 'success.',
2566: x_returnStatus => l_retStatus);
2567: end if;
2568: exception
2569: when fnd_api.g_exc_unexpected_error then
2570: x_returnStatus := fnd_api.g_ret_sts_unexp_error;
2571: if(l_logLevel <= wip_constants.trace_logging) then
2572: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.resolvePriorExceptions',
2573: p_procReturnStatus => x_returnStatus,
2574: p_msg => 'errmsg: ' || l_errMsg,
2575: x_returnStatus => l_retStatus);
2579: fnd_msg_pub.add_exc_msg(p_pkg_name => 'wip_infResSched_grp',
2580: p_procedure_name => 'resolvePriorExceptions',
2581: p_error_text => SQLERRM);
2582:
2583: if(l_logLevel <= wip_constants.trace_logging) then
2584: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.resolvePriorExceptions',
2585: p_procReturnStatus => x_returnStatus,
2586: p_msg => 'unexp error:' || SQLERRM,
2587: x_returnStatus => l_retStatus);
2605: l_exceptionExists boolean := false;
2606: l_errMsg VARCHAR2(200);
2607: begin
2608: x_returnStatus := fnd_api.g_ret_sts_success;
2609: if(l_logLevel <= wip_constants.trace_logging) then
2610: l_params(1).paramName := 'p_orgID';
2611: l_params(1).paramValue := p_orgID;
2612: l_params(2).paramName := 'p_repLineID';
2613: l_params(2).paramValue := p_repLineID;
2617: p_params => l_params,
2618: x_returnStatus => x_returnStatus);
2619: end if;
2620: --this loop finds an exception
2621: while(getNextResIdx(l_range, wip_constants.sched_next, g_backward, x_resTbls, i)) loop
2622: --if we have already found an exception and moved on to the next op, stop
2623: --and reschedule based on the current info
2624: if(l_exceptionExists) then
2625: if(l_opSeqNum <> x_resTbls.opSeqNum(i)) then
2634: l_opSeqNum := x_resTbls.opSeqNum(i);
2635: end if;
2636: l_opRange(1) := i;
2637: l_exceptionExists := x_resTbls.endDate(i) > p_endDate;
2638: if(l_logLevel <= wip_constants.full_logging) then
2639: wip_logger.log('res end date: ' || to_char(x_resTbls.endDate(i), g_logDateFmt), l_retStatus);
2640: wip_logger.log('job end date: ' || to_char(p_endDate, g_logDateFmt), l_retStatus);
2641: end if;
2642: end if;
2643: end loop;
2644:
2645: --found a prior resource whose start date is earlier than job start...
2646: if(l_exceptionExists) then
2647: if(l_logLevel <= wip_constants.full_logging) then
2648: wip_logger.log('found exception', l_retStatus);
2649: end if;
2650: i := null;
2651: --going to reschedule entire job
2677: backwardSchedule(p_orgID => p_orgID,
2678: p_repLineID => p_repLineID,
2679: p_endDate => p_endDate,
2680: p_range => l_opRange,
2681: p_schedFlag => wip_constants.sched_next,
2682: x_resTbls => x_resTbls,
2683: x_returnStatus => x_returnStatus);
2684:
2685: if(x_returnStatus <> fnd_api.g_ret_sts_success) then
2688: end if;
2689:
2690: --find earliest start date of the next resources
2691: i := null;
2692: while(getNextResIdx(l_opRange, wip_constants.sched_next, g_forward, x_resTbls, i)) loop
2693: l_opEndDate := least(nvl(l_opEndDate, x_resTbls.startDate(i)), x_resTbls.startDate(i));
2694: end loop;
2695:
2696: --now midpoint schedule from the new op start date...This invocation of schedule() will not invoke
2722: raise fnd_api.g_exc_unexpected_error;
2723: end if;
2724: end if;
2725:
2726: if(l_logLevel <= wip_constants.trace_logging) then
2727: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.resolveNextExceptions',
2728: p_procReturnStatus => x_returnStatus,
2729: p_msg => 'success.',
2730: x_returnStatus => l_retStatus);
2731: end if;
2732: exception
2733: when fnd_api.g_exc_unexpected_error then
2734: x_returnStatus := fnd_api.g_ret_sts_unexp_error;
2735: if(l_logLevel <= wip_constants.trace_logging) then
2736: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.resolveNextExceptions',
2737: p_procReturnStatus => x_returnStatus,
2738: p_msg => l_errMsg,
2739: x_returnStatus => l_retStatus);
2743: fnd_msg_pub.add_exc_msg(p_pkg_name => 'wip_infResSched_grp',
2744: p_procedure_name => 'resolveNextExceptions',
2745: p_error_text => SQLERRM);
2746:
2747: if(l_logLevel <= wip_constants.trace_logging) then
2748: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.resolveNextExceptions',
2749: p_procReturnStatus => x_returnStatus,
2750: p_msg => 'unexp error:' || SQLERRM,
2751: x_returnStatus => l_retStatus);
2761: l_retStatus VARCHAR2(1);
2762: l_resCode VARCHAR2(10);
2763: begin
2764:
2765: if (l_logLevel <= wip_constants.trace_logging) then
2766: wip_logger.entryPoint(p_procName => 'wip_infResSched_grp.dumpResources',
2767: p_params => l_params,
2768: x_returnStatus => l_retStatus);
2769: end if;
2768: x_returnStatus => l_retStatus);
2769: end if;
2770:
2771: if(p_resTbls.resID is null or p_resTbls.resID.count < 1 and
2772: l_logLevel <= wip_constants.trace_logging) then
2773: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.dumpResources',
2774: p_procReturnStatus => null,
2775: p_msg => 'no resources in table!',
2776: x_returnStatus => l_retStatus);
2776: x_returnStatus => l_retStatus);
2777: return;
2778: end if;
2779:
2780: if (l_logLevel <= wip_constants.full_logging) then
2781: for i in 1..p_resTbls.resID.count loop
2782: select resource_code
2783: into l_resCode
2784: from bom_resources
2803: end loop;
2804: end if;
2805: end loop;
2806: end if;
2807: if (l_logLevel <= wip_constants.trace_logging) then
2808: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.dumpResources',
2809: p_procReturnStatus => null,
2810: p_msg => 'success',
2811: x_returnStatus => l_retStatus);
2812: end if;
2813:
2814: exception
2815: when others then
2816: if (l_logLevel <= wip_constants.trace_logging) then
2817: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.dumpResources',
2818: p_procReturnStatus => null,
2819: p_msg => 'exception:' || SQLERRM,
2820: x_returnStatus => l_retStatus);
2832: l_logLevel NUMBER := fnd_log.g_current_runtime_level;
2833: l_retStatus VARCHAR2(1);
2834: l_params wip_logger.param_tbl_t;
2835: begin
2836: if(l_logLevel <= wip_constants.trace_logging) then
2837: l_params(1).paramName := 'p_anchorDate';
2838: l_params(1).paramValue := to_char(p_anchorDate, g_logDateFmt);
2839: wip_logger.entryPoint(p_procName => 'wip_infResSched_grp.scheduleNoResources',
2840: p_params => l_params,
2841: x_returnStatus => l_retStatus);
2842: end if;
2843: x_returnStatus := fnd_api.g_ret_sts_success;
2844:
2845: while(getNextResIdx(l_range, wip_constants.sched_no, g_forward, x_resTbls, i)) loop
2846:
2847: if(l_logLevel <= wip_constants.full_logging) then
2848: wip_logger.log('found scheduled no resource at ' || i, l_retStatus);
2849: end if;
2843: x_returnStatus := fnd_api.g_ret_sts_success;
2844:
2845: while(getNextResIdx(l_range, wip_constants.sched_no, g_forward, x_resTbls, i)) loop
2846:
2847: if(l_logLevel <= wip_constants.full_logging) then
2848: wip_logger.log('found scheduled no resource at ' || i, l_retStatus);
2849: end if;
2850:
2851: l_prevRange(2) := i;
2851: l_prevRange(2) := i;
2852: l_nextRange(1) := i;
2853: j := null;
2854: --find previous scheduled yes resource
2855: if(getNextResIdx(l_prevRange, wip_constants.sched_yes, g_backward, x_resTbls, j)) then
2856:
2857: if(l_logLevel <= wip_constants.full_logging) then
2858: wip_logger.log('found previous scheduled yes resource at ' || j, l_retStatus);
2859: end if;
2853: j := null;
2854: --find previous scheduled yes resource
2855: if(getNextResIdx(l_prevRange, wip_constants.sched_yes, g_backward, x_resTbls, j)) then
2856:
2857: if(l_logLevel <= wip_constants.full_logging) then
2858: wip_logger.log('found previous scheduled yes resource at ' || j, l_retStatus);
2859: end if;
2860: x_resTbls.startDate(i) := x_resTbls.endDate(j);
2861:
2860: x_resTbls.startDate(i) := x_resTbls.endDate(j);
2861:
2862: --couldn't find a scheduled yes resource
2863: /* Bug 6954186: Find the next scheduled resource in forward direction*/
2864: elsif(getNextResIdx(l_nextRange, wip_constants.sched_yes, g_forward, x_resTbls, j)) then
2865: if(l_logLevel <= wip_constants.full_logging) then
2866: wip_logger.log('found later scheduled yes resource at ' || j, l_retStatus);
2867: end if;
2868:
2861:
2862: --couldn't find a scheduled yes resource
2863: /* Bug 6954186: Find the next scheduled resource in forward direction*/
2864: elsif(getNextResIdx(l_nextRange, wip_constants.sched_yes, g_forward, x_resTbls, j)) then
2865: if(l_logLevel <= wip_constants.full_logging) then
2866: wip_logger.log('found later scheduled yes resource at ' || j, l_retStatus);
2867: end if;
2868:
2869: x_resTbls.startDate(i) := x_resTbls.startDate(j);
2869: x_resTbls.startDate(i) := x_resTbls.startDate(j);
2870:
2871: else --no scheduled yes resources
2872: x_resTbls.startDate(i) := p_anchorDate;
2873: if(l_logLevel <= wip_constants.full_logging) then
2874: wip_logger.log('no scheduled yes resources found', l_retStatus);
2875: end if;
2876: end if;
2877:
2876: end if;
2877:
2878: x_resTbls.endDate(i) := x_resTbls.startDate(i);
2879: end loop;
2880: if(l_logLevel <= wip_constants.trace_logging) then
2881: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.scheduleNoResources',
2882: p_procReturnStatus => x_returnStatus,
2883: p_msg => 'success',
2884: x_returnStatus => l_retStatus);
2885: end if;
2886: exception
2887: when others then
2888: x_returnStatus := fnd_api.g_ret_sts_unexp_error;
2889: if(l_logLevel <= wip_constants.trace_logging) then
2890: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.scheduleNoResources',
2891: p_procReturnStatus => x_returnStatus,
2892: p_msg => 'error: ' || SQLERRM,
2893: x_returnStatus => l_retStatus);