[Home] [Help]
130: l_otherExists boolean := x_resTbls.schedFlag(1) in (wip_constants.sched_yes, wip_constants.sched_next);
131: l_startIdx NUMBER := 1;
132: l_logLevel NUMBER := fnd_log.g_current_runtime_level;
133: l_retStatus VARCHAR2(1);
134: l_params wip_logger.param_tbl_t;
135: begin
136: if(l_logLevel <= wip_constants.trace_logging) then
137: wip_logger.entryPoint(p_procName => 'wip_infResSched_grp.removePriorsFromBatch',
138: p_params => l_params,
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
158: end if;
159: i := i + 1;
160: end loop;
161: if (l_logLevel <= wip_constants.trace_logging) then
162: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.removePriorsFromBatch',
163: p_procReturnStatus => null,
164: p_msg => 'success',
165: x_returnStatus => l_retStatus);
166: end if;
170: l_curOp NUMBER:= x_resTbls.opSeqNum(1);
171: l_nonPriorExists boolean := false;
172: l_logLevel NUMBER := fnd_log.g_current_runtime_level;
173: l_retStatus VARCHAR2(1);
174: l_params wip_logger.param_tbl_t;
175: begin
176: if(l_logLevel <= wip_constants.trace_logging) then
177: wip_logger.entryPoint(p_procName => 'wip_infResSched_grp.removePriors',
178: p_params => l_params,
173: l_retStatus VARCHAR2(1);
174: l_params wip_logger.param_tbl_t;
175: begin
176: if(l_logLevel <= wip_constants.trace_logging) then
177: wip_logger.entryPoint(p_procName => 'wip_infResSched_grp.removePriors',
178: p_params => l_params,
179: x_returnStatus => l_retStatus);
180: end if;
181: for i in 1..x_resTbls.resID.count loop
189: l_nonPriorExists := x_resTbls.schedFlag(i) not in (wip_constants.sched_prior, wip_constants.sched_no);
190: end if;
191: end loop;
192: if (l_logLevel <= wip_constants.trace_logging) then
193: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.removePriors',
194: p_procReturnStatus => null,
195: p_msg => 'success',
196: x_returnStatus => l_retStatus);
197: end if;
201: l_curOp NUMBER:= x_resTbls.opSeqNum(x_resTbls.resID.count);
202: l_nonNextExists boolean := false;
203: l_logLevel NUMBER := fnd_log.g_current_runtime_level;
204: l_retStatus VARCHAR2(1);
205: l_params wip_logger.param_tbl_t;
206: begin
207: if(l_logLevel <= wip_constants.trace_logging) then
208: wip_logger.entryPoint(p_procName => 'wip_infResSched_grp.removeNexts',
209: p_params => l_params,
204: l_retStatus VARCHAR2(1);
205: l_params wip_logger.param_tbl_t;
206: begin
207: if(l_logLevel <= wip_constants.trace_logging) then
208: wip_logger.entryPoint(p_procName => 'wip_infResSched_grp.removeNexts',
209: p_params => l_params,
210: x_returnStatus => l_retStatus);
211: end if;
212: for i in reverse 1..x_resTbls.resID.count loop
221: l_nonNextExists := x_resTbls.schedFlag(i) not in (wip_constants.sched_no, wip_constants.sched_next);
222: end if;
223: end loop;
224: if (l_logLevel <= wip_constants.trace_logging) then
225: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.removeNexts',
226: p_procReturnStatus => null,
227: p_msg => 'success',
228: x_returnStatus => l_retStatus);
229: end if;
232: procedure verifyResources(x_resTbls IN OUT NOCOPY OP_RES_RECTBL_T) is
233:
234: l_logLevel NUMBER := fnd_log.g_current_runtime_level;
235: l_retStatus VARCHAR2(1);
236: l_params wip_logger.param_tbl_t;
237: begin
238: if(l_logLevel <= wip_constants.trace_logging) then
239: wip_logger.entryPoint(p_procName => 'wip_infResSched_grp.verifyResources',
240: p_params => l_params,
235: l_retStatus VARCHAR2(1);
236: l_params wip_logger.param_tbl_t;
237: begin
238: if(l_logLevel <= wip_constants.trace_logging) then
239: wip_logger.entryPoint(p_procName => 'wip_infResSched_grp.verifyResources',
240: p_params => l_params,
241: x_returnStatus => l_retStatus);
242: end if;
243:
261: --changes schedule method of prior resources to yes if
262: -- + they are in a simultaneous batch with yes or next resources.
263: removePriorsFromBatch(x_resTbls => x_resTbls);
264: if (l_logLevel <= wip_constants.trace_logging) then
265: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.verifyResources',
266: p_procReturnStatus => null,
267: p_msg => 'success',
268: x_returnStatus => l_retStatus);
269: end if;
271:
272: procedure dumpOps(p_opTbl in op_tbl_t) is
273: l_logLevel NUMBER := fnd_log.g_current_runtime_level;
274: l_retStatus VARCHAR2(1);
275: l_params wip_logger.param_tbl_t;
276: begin
277: if(l_logLevel <= wip_constants.trace_logging) then
278: wip_logger.entryPoint(p_procName => 'wip_infResSched_grp.dumpOps',
279: p_params => l_params,
274: l_retStatus VARCHAR2(1);
275: l_params wip_logger.param_tbl_t;
276: begin
277: if(l_logLevel <= wip_constants.trace_logging) then
278: wip_logger.entryPoint(p_procName => 'wip_infResSched_grp.dumpOps',
279: p_params => l_params,
280: x_returnStatus => l_retStatus);
281: end if;
282: if(l_logLevel <= wip_constants.full_logging) then
280: x_returnStatus => l_retStatus);
281: end if;
282: if(l_logLevel <= wip_constants.full_logging) then
283: for i in 1..p_opTbl.count loop
284: wip_logger.log('op:' || i, l_retStatus);
285: wip_logger.log('startDate:' || to_char(p_opTbl(i).startDate, g_logDateFmt), l_retStatus);
286: wip_logger.log('endDate:' || to_char(p_opTbl(i).endDate, g_logDateFmt), l_retStatus);
287: if(p_opTbl(i).priorsExist) then
288: wip_logger.log('priorsExist:true', l_retStatus);
281: end if;
282: if(l_logLevel <= wip_constants.full_logging) then
283: for i in 1..p_opTbl.count loop
284: wip_logger.log('op:' || i, l_retStatus);
285: wip_logger.log('startDate:' || to_char(p_opTbl(i).startDate, g_logDateFmt), l_retStatus);
286: wip_logger.log('endDate:' || to_char(p_opTbl(i).endDate, g_logDateFmt), l_retStatus);
287: if(p_opTbl(i).priorsExist) then
288: wip_logger.log('priorsExist:true', l_retStatus);
289: else
282: if(l_logLevel <= wip_constants.full_logging) then
283: for i in 1..p_opTbl.count loop
284: wip_logger.log('op:' || i, l_retStatus);
285: wip_logger.log('startDate:' || to_char(p_opTbl(i).startDate, g_logDateFmt), l_retStatus);
286: wip_logger.log('endDate:' || to_char(p_opTbl(i).endDate, g_logDateFmt), l_retStatus);
287: if(p_opTbl(i).priorsExist) then
288: wip_logger.log('priorsExist:true', l_retStatus);
289: else
290: wip_logger.log('priorsExist:false', l_retStatus);
284: wip_logger.log('op:' || i, l_retStatus);
285: wip_logger.log('startDate:' || to_char(p_opTbl(i).startDate, g_logDateFmt), l_retStatus);
286: wip_logger.log('endDate:' || to_char(p_opTbl(i).endDate, g_logDateFmt), l_retStatus);
287: if(p_opTbl(i).priorsExist) then
288: wip_logger.log('priorsExist:true', l_retStatus);
289: else
290: wip_logger.log('priorsExist:false', l_retStatus);
291: end if;
292: if(p_opTbl(i).nextsExist) then
286: wip_logger.log('endDate:' || to_char(p_opTbl(i).endDate, g_logDateFmt), l_retStatus);
287: if(p_opTbl(i).priorsExist) then
288: wip_logger.log('priorsExist:true', l_retStatus);
289: else
290: wip_logger.log('priorsExist:false', l_retStatus);
291: end if;
292: if(p_opTbl(i).nextsExist) then
293: wip_logger.log('nextsExist:true', l_retStatus);
294: else
289: else
290: wip_logger.log('priorsExist:false', l_retStatus);
291: end if;
292: if(p_opTbl(i).nextsExist) then
293: wip_logger.log('nextsExist:true', l_retStatus);
294: else
295: wip_logger.log('nextsExist:false', l_retStatus);
296: end if;
297: wip_logger.log('resRange:' || p_opTbl(i).resStartIdx || '-' || p_opTbl(i).resEndIdx, l_retStatus);
291: end if;
292: if(p_opTbl(i).nextsExist) then
293: wip_logger.log('nextsExist:true', l_retStatus);
294: else
295: wip_logger.log('nextsExist:false', l_retStatus);
296: end if;
297: wip_logger.log('resRange:' || p_opTbl(i).resStartIdx || '-' || p_opTbl(i).resEndIdx, l_retStatus);
298: end loop;
299: end if;
293: wip_logger.log('nextsExist:true', l_retStatus);
294: else
295: wip_logger.log('nextsExist:false', l_retStatus);
296: end if;
297: wip_logger.log('resRange:' || p_opTbl(i).resStartIdx || '-' || p_opTbl(i).resEndIdx, l_retStatus);
298: end loop;
299: end if;
300: if (l_logLevel <= wip_constants.trace_logging) then
301: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.dumpOps',
297: wip_logger.log('resRange:' || p_opTbl(i).resStartIdx || '-' || p_opTbl(i).resEndIdx, l_retStatus);
298: end loop;
299: end if;
300: if (l_logLevel <= wip_constants.trace_logging) then
301: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.dumpOps',
302: p_procReturnStatus => null,
303: p_msg => 'success',
304: x_returnStatus => l_retStatus);
305: end if;
314: j number := 1;
315: l_firstYesOpIdx NUMBER;
316: l_logLevel NUMBER := fnd_log.g_current_runtime_level;
317: l_retStatus VARCHAR2(1);
318: l_params wip_logger.param_tbl_t;
319: begin
320: if(l_logLevel <= wip_constants.trace_logging) then
321: l_params(1).paramName := 'p_anchorDate';
322: l_params(1).paramValue := to_char(p_anchorDate, g_logDateFmt);
319: begin
320: if(l_logLevel <= wip_constants.trace_logging) then
321: l_params(1).paramName := 'p_anchorDate';
322: l_params(1).paramValue := to_char(p_anchorDate, g_logDateFmt);
323: wip_logger.entryPoint(p_procName => 'wip_infResSched_grp.buildOpStructure',
324: p_params => l_params,
325: x_returnStatus => l_retStatus);
326: end if;
327:
332: for i in 1..p_resTbls.resID.count loop
333: if(l_opSeqNum <> p_resTbls.opSeqNum(i)) then
334:
335: if(l_logLevel <= wip_constants.full_logging) then
336: wip_logger.log('new op at resource ' || i, l_retStatus);
337: end if;
338:
339: x_opTbl(j).resEndIdx := i - 1;
340: j := j + 1;
355: end if;
356: x_opTbl(j).startDate := least(p_resTbls.startDate(i), nvl(x_opTbl(j).startDate, p_resTbls.startDate(i)));
357: x_opTbl(j).endDate := greatest(p_resTbls.endDate(i), nvl(x_opTbl(j).endDate, p_resTbls.endDate(i)));
358: if(l_logLevel <= wip_constants.full_logging) then
359: wip_logger.log('op ' || j || '''s start date:' || to_char(x_opTbl(j).startDate, g_logDateFmt), l_retStatus);
360: wip_logger.log('op ' || j || '''s end date:' || to_char(x_opTbl(j).endDate, g_logDateFmt), l_retStatus);
361: end if;
362:
363: end if;
356: x_opTbl(j).startDate := least(p_resTbls.startDate(i), nvl(x_opTbl(j).startDate, p_resTbls.startDate(i)));
357: x_opTbl(j).endDate := greatest(p_resTbls.endDate(i), nvl(x_opTbl(j).endDate, p_resTbls.endDate(i)));
358: if(l_logLevel <= wip_constants.full_logging) then
359: wip_logger.log('op ' || j || '''s start date:' || to_char(x_opTbl(j).startDate, g_logDateFmt), l_retStatus);
360: wip_logger.log('op ' || j || '''s end date:' || to_char(x_opTbl(j).endDate, g_logDateFmt), l_retStatus);
361: end if;
362:
363: end if;
364: end loop;
386: dumpOps(x_opTbl);
387: end if;
388:
389: if (l_logLevel <= wip_constants.trace_logging) then
390: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.buildOpStructure',
391: p_procReturnStatus => null,
392: p_msg => 'success',
393: x_returnStatus => l_retStatus);
394: end if;
400: p_isMdPntFwd boolean,
401: x_midPntFwdIdx OUT NOCOPY NUMBER,
402: x_midPntBkwdIdx OUT NOCOPY NUMBER) is
403: l_retStatus VARCHAR2(1);
404: l_params wip_logger.param_tbl_t;
405: l_logLevel NUMBER := fnd_log.g_current_runtime_level;
406: l_foundMidPntOp boolean := false;
407: begin
408: if(l_logLevel <= wip_constants.trace_logging) then
413: l_params(3).paramName := 'p_isMdPntFwd';
414: if(p_isMdPntFwd) then l_params(3).paramValue := 'true';
415: else l_params(3).paramValue := 'false'; end if;
416:
417: wip_logger.entryPoint(p_procName => 'wip_infResSched_grp.findMdPntRes',
418: p_params => l_params,
419: x_returnStatus => l_retStatus);
420: end if;
421:
423: for i in 1..p_resTbls.resID.count loop
424: if(p_opSeqNum = p_resTbls.opSeqNum(i)) then --op matches
425: l_foundMidPntOp := true;
426: if(l_logLevel <= wip_constants.full_logging) then
427: wip_logger.log('op seq matches res ' || i, l_retStatus);
428: end if;
429:
430: if(p_resSeqNum is not null) then
431: if(p_resSeqNum = p_resTbls.resSeqNum(i)) then --res seq matches
430: if(p_resSeqNum is not null) then
431: if(p_resSeqNum = p_resTbls.resSeqNum(i)) then --res seq matches
432:
433: if(l_logLevel <= wip_constants.full_logging) then
434: wip_logger.log('res seq matches res ' || i, l_retStatus);
435: end if;
436:
437: if(p_isMdPntFwd) then
438: -- bug 3423612: If there are simultaneous resources, we have to
470: end if;
471: else --resource seq was not populated. use op start or end res
472:
473: if(l_logLevel <= wip_constants.full_logging) then
474: wip_logger.log('res seq is null', l_retStatus);
475: end if;
476:
477: if(p_isMdPntFwd) then --forward scheduling midpoint op
478: x_midPntFwdIdx := i;
488: if(l_foundMidPntOp and
489: not(p_isMdPntFwd) and
490: p_resTbls.opSeqNum(i) <> p_opSeqNum) then
491: if(l_logLevel <= wip_constants.full_logging) then
492: wip_logger.log('first res past midpoint at idx:' || i, l_retStatus);
493: end if;
494: x_midPntBkwdIdx := i - 1;
495: x_midPntFwdIdx := i;
496: exit;
497: end if;
498:
499: if(p_resTbls.resID.count = i) then
500: if(l_logLevel <= wip_constants.full_logging) then
501: wip_logger.log('backward scheduling everything', l_retStatus);
502: end if;
503:
504: x_midPntBkwdIdx := i;
505: exit;
505: exit;
506: end if;
507: end loop;
508: if(l_logLevel <= wip_constants.trace_logging) then
509: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.findMdPntRes',
510: p_procReturnStatus => null,
511: p_msg => 'fwdIdx:' || x_midPntFwdIdx || '; bkwdIdx:' || x_midPntBkwdIdx,
512: x_returnStatus => l_retStatus);
513: end if;
522: p_endDebug IN VARCHAR2 := null,
523: x_resTbls IN OUT NOCOPY OP_RES_RECTBL_T,
524: x_returnStatus OUT NOCOPY VARCHAR2) is
525: l_logLevel NUMBER := fnd_log.g_current_runtime_level;
526: l_params wip_logger.param_tbl_t;
527: l_retStatus VARCHAR2(1);
528:
529: l_fwdStIdx NUMBER;--resource to start forward scheduling from
530: l_bkwdEndIdx NUMBER;--resource to backward schedule to
544: l_params(4).paramName := 'p_endDate';
545: l_params(4).paramValue := to_char(p_endDate, g_logDateFmt);
546: l_params(5).paramName := 'p_opSeqNum';
547: l_params(5).paramValue := p_opSeqNum;
548: wip_logger.entryPoint(p_procName => 'wip_infResSched_grp.schedule',
549: p_params => l_params,
550: x_returnStatus => x_returnStatus);
551: if(x_returnStatus <> fnd_api.g_ret_sts_success) then
552: raise fnd_api.g_exc_unexpected_error;
555: x_returnStatus := fnd_api.g_ret_sts_success;
556:
557: if(x_resTbls.resID is null or x_resTbls.resID.count < 1) then
558: if (l_logLevel <= wip_constants.trace_logging) then
559: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.schedule',
560: p_procReturnStatus => x_returnStatus,
561: p_msg => 'no resources to schedule!',
562: x_returnStatus => l_retStatus);
563: end if;
600: l_startDate := p_startDate;
601: l_endDate := null;--this line isn''t necessary, but is included for clarity
602:
603: if (l_logLevel <= wip_constants.trace_logging) then
604: wip_logger.log(p_msg => 'forward scheduling',
605: x_returnStatus => x_returnStatus);
606: end if;
607:
608: --backward
612: l_startDate := null;--this line isn''t necessary, but is included for clarity
613: l_endDate := p_endDate;
614:
615: if (l_logLevel <= wip_constants.trace_logging) then
616: wip_logger.log(p_msg => 'backward scheduling',
617: x_returnStatus => x_returnStatus);
618: end if;
619: end if;
620: else --midpoint scheduling
625: x_midPntFwdIdx => l_fwdStIdx,
626: x_midPntBkwdIdx => l_bkwdEndIdx);
627:
628: if (l_logLevel <= wip_constants.trace_logging) then
629: wip_logger.log(p_msg => 'midpoint scheduling',
630: x_returnStatus => x_returnStatus);
631: end if;
632:
633: if(p_startDate is not null) then
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
678: p_schedFlag => wip_constants.sched_yes,
679: x_resTbls => x_resTbls,
680: x_returnStatus => x_returnStatus);
681: if(x_returnStatus <> fnd_api.g_ret_sts_success) then
682: wip_logger.log('bkwdSch failed', l_retStatus);
683: raise fnd_api.g_exc_unexpected_error;
684: end if;
685: end if;
686:
696: x_resTbls => x_resTbls,
697: x_returnStatus => x_returnStatus);
698:
699: if(x_returnStatus <> fnd_api.g_ret_sts_success) then
700: wip_logger.log('schPriorRes failed', l_retStatus);
701: raise fnd_api.g_exc_unexpected_error;
702: end if;
703:
704:
708: x_resTbls => x_resTbls,
709: x_returnStatus => x_returnStatus);
710:
711: if(x_returnStatus <> fnd_api.g_ret_sts_success) then
712: wip_logger.log('schNextRes failed', l_retStatus);
713: raise fnd_api.g_exc_unexpected_error;
714: end if;
715:
716: if(l_logLevel <= wip_constants.full_logging) then
724: p_startDate => p_startDate,
725: x_resTbls => x_resTbls,
726: x_returnStatus => x_returnStatus);
727: if(x_returnStatus <> fnd_api.g_ret_sts_success) then
728: wip_logger.log('resolvePriorRes failed', l_retStatus);
729: raise fnd_api.g_exc_unexpected_error;
730: end if;
731: --if backward scheduling
732: elsif(p_opSeqNum is null and p_endDate is not null) then
735: p_endDate => p_endDate,
736: x_resTbls => x_resTbls,
737: x_returnStatus => x_returnStatus);
738: if(x_returnStatus <> fnd_api.g_ret_sts_success) then
739: wip_logger.log('resolveNextRes failed', l_retStatus);
740: raise fnd_api.g_exc_unexpected_error;
741: end if;
742: end if;
743:
749:
750:
751:
752: if(l_logLevel <= wip_constants.trace_logging) then
753: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.schedule',
754: p_procReturnStatus => x_returnStatus,
755: p_msg => 'success',
756: x_returnStatus => l_retStatus);
757: if(fnd_api.to_boolean(nvl(p_endDebug, fnd_api.g_true))) then
754: p_procReturnStatus => x_returnStatus,
755: p_msg => 'success',
756: x_returnStatus => l_retStatus);
757: if(fnd_api.to_boolean(nvl(p_endDebug, fnd_api.g_true))) then
758: wip_logger.cleanup(l_retStatus);
759: end if;
760: end if;
761: exception
762: when fnd_api.g_exc_unexpected_error then
764: if(l_logLevel <= wip_constants.trace_logging) then
765: wip_utilities.get_message_stack(p_msg => l_errMsg,
766: p_delete_stack => fnd_api.g_false);
767:
768: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.schedule',
769: p_procReturnStatus => x_returnStatus,
770: p_msg => 'failure: ' || l_errMsg,
771: x_returnStatus => l_retStatus);
772: if(fnd_api.to_boolean(nvl(p_endDebug, fnd_api.g_true))) then
769: p_procReturnStatus => x_returnStatus,
770: p_msg => 'failure: ' || l_errMsg,
771: x_returnStatus => l_retStatus);
772: if(fnd_api.to_boolean(nvl(p_endDebug, fnd_api.g_true))) then
773: wip_logger.cleanup(l_retStatus);
774: end if;
775: end if;
776: when others then
777: x_returnStatus := fnd_api.g_ret_sts_unexp_error;
778: fnd_msg_pub.add_exc_msg(p_pkg_name => 'wip_infResSched_grp',
779: p_procedure_name => 'schedule',
780: p_error_text => SQLERRM);
781: if(l_logLevel <= wip_constants.trace_logging) then
782: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.schedule',
783: p_procReturnStatus => x_returnStatus,
784: p_msg => 'unexpected error: ' || SQLERRM,
785: x_returnStatus => l_retStatus);
786: if(fnd_api.to_boolean(nvl(p_endDebug, fnd_api.g_true))) then
783: p_procReturnStatus => x_returnStatus,
784: p_msg => 'unexpected error: ' || SQLERRM,
785: x_returnStatus => l_retStatus);
786: if(fnd_api.to_boolean(nvl(p_endDebug, fnd_api.g_true))) then
787: wip_logger.cleanup(l_retStatus);
788: end if;
789: end if;
790: end schedule;
791:
826: p_curIdx in number,
827: p_doneSchedBatch in boolean,
828: p_prevIdx in number) return date is
829: l_retStatus VARCHAR2(1);
830: l_params wip_logger.param_tbl_t;
831: i number;
832: l_maxEndDate date;
833: l_logLevel NUMBER := fnd_log.g_current_runtime_level;
834: l_bool boolean;
848: end if;
849: l_params(6).paramName := 'p_prevIdx';
850: l_params(6).paramValue := p_prevIdx;
851:
852: wip_logger.entryPoint(p_procName => 'wip_infResSched_grp.getStartDate',
853: p_params => l_params,
854: x_returnStatus => l_retStatus);
855: end if;
856:
859: i := p_curIdx;
860:
861: if(p_doneSchedBatch) then
862: if(l_logLevel <= wip_constants.full_logging) then
863: wip_logger.log('done scheduling batch', l_retStatus);
864: end if;
865: while(getNextResIdx(p_range, p_schedFlag, g_backward, p_resTbls, i)) loop
866: if(l_logLevel <= wip_constants.full_logging) then
867: wip_logger.log('in loop', l_retStatus);
863: wip_logger.log('done scheduling batch', l_retStatus);
864: end if;
865: while(getNextResIdx(p_range, p_schedFlag, g_backward, p_resTbls, i)) loop
866: if(l_logLevel <= wip_constants.full_logging) then
867: wip_logger.log('in loop', l_retStatus);
868: wip_logger.log('resID' || p_resTbls.resID(i), l_retStatus);
869: wip_logger.log('opSeq' || p_resTbls.opSeqNum(i), l_retStatus);
870: wip_logger.log('schSeq' || p_resTbls.schedSeqNum(i), l_retStatus);
871: wip_logger.log('idx' || i, l_retStatus);
864: end if;
865: while(getNextResIdx(p_range, p_schedFlag, g_backward, p_resTbls, i)) loop
866: if(l_logLevel <= wip_constants.full_logging) then
867: wip_logger.log('in loop', l_retStatus);
868: wip_logger.log('resID' || p_resTbls.resID(i), l_retStatus);
869: wip_logger.log('opSeq' || p_resTbls.opSeqNum(i), l_retStatus);
870: wip_logger.log('schSeq' || p_resTbls.schedSeqNum(i), l_retStatus);
871: wip_logger.log('idx' || i, l_retStatus);
872: end if;
865: while(getNextResIdx(p_range, p_schedFlag, g_backward, p_resTbls, i)) loop
866: if(l_logLevel <= wip_constants.full_logging) then
867: wip_logger.log('in loop', l_retStatus);
868: wip_logger.log('resID' || p_resTbls.resID(i), l_retStatus);
869: wip_logger.log('opSeq' || p_resTbls.opSeqNum(i), l_retStatus);
870: wip_logger.log('schSeq' || p_resTbls.schedSeqNum(i), l_retStatus);
871: wip_logger.log('idx' || i, l_retStatus);
872: end if;
873: if(p_resTbls.schedSeqNum(i) = p_resTbls.schedSeqNum(p_prevIdx) and
866: if(l_logLevel <= wip_constants.full_logging) then
867: wip_logger.log('in loop', l_retStatus);
868: wip_logger.log('resID' || p_resTbls.resID(i), l_retStatus);
869: wip_logger.log('opSeq' || p_resTbls.opSeqNum(i), l_retStatus);
870: wip_logger.log('schSeq' || p_resTbls.schedSeqNum(i), l_retStatus);
871: wip_logger.log('idx' || i, l_retStatus);
872: end if;
873: if(p_resTbls.schedSeqNum(i) = p_resTbls.schedSeqNum(p_prevIdx) and
874: p_resTbls.opSeqNum(i) = p_resTbls.opSeqNum(p_prevIdx)) then
867: wip_logger.log('in loop', l_retStatus);
868: wip_logger.log('resID' || p_resTbls.resID(i), l_retStatus);
869: wip_logger.log('opSeq' || p_resTbls.opSeqNum(i), l_retStatus);
870: wip_logger.log('schSeq' || p_resTbls.schedSeqNum(i), l_retStatus);
871: wip_logger.log('idx' || i, l_retStatus);
872: end if;
873: if(p_resTbls.schedSeqNum(i) = p_resTbls.schedSeqNum(p_prevIdx) and
874: p_resTbls.opSeqNum(i) = p_resTbls.opSeqNum(p_prevIdx)) then
875: l_maxEndDate := greatest(nvl(l_maxEndDate, p_resTbls.endDate(i)), p_resTbls.endDate(i));
873: if(p_resTbls.schedSeqNum(i) = p_resTbls.schedSeqNum(p_prevIdx) and
874: p_resTbls.opSeqNum(i) = p_resTbls.opSeqNum(p_prevIdx)) then
875: l_maxEndDate := greatest(nvl(l_maxEndDate, p_resTbls.endDate(i)), p_resTbls.endDate(i));
876: if(l_logLevel <= wip_constants.full_logging) then
877: wip_logger.log('resource in batch. endDate:' || to_char(p_resTbls.endDate(i), g_logDateFmt), l_retStatus);
878: end if;
879: else
880: if(l_logLevel <= wip_constants.full_logging) then
881: wip_logger.log('resource not in batch.', l_retStatus);
877: wip_logger.log('resource in batch. endDate:' || to_char(p_resTbls.endDate(i), g_logDateFmt), l_retStatus);
878: end if;
879: else
880: if(l_logLevel <= wip_constants.full_logging) then
881: wip_logger.log('resource not in batch.', l_retStatus);
882: end if;
883: exit;
884: end if;
885: end loop;
888: l_maxEndDate := p_resTbls.endDate(i);
889: end if;
890:
891: if (l_logLevel <= wip_constants.trace_logging) then
892: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.getStartDate',
893: p_procReturnStatus => to_char(l_maxEndDate),
894: p_msg => 'finished scheduling',
895: x_returnStatus => l_retStatus);
896: end if;
975: /* end of fix bug 7027946 */
976:
977: l_shifts shift_recTbl_t;
978: l_logLevel NUMBER := fnd_log.g_current_runtime_level;
979: l_params wip_logger.param_tbl_t;
980: l_retStatus VARCHAR2(1);
981: l_resourceScheduled boolean := false;
982: l_cursorStartDate date := trunc(p_startDate) - 1;--subtract 1 to make sure to get wraparound shifts (start on prev day)
983: l_fromDate date;
999: l_params(4).paramName := 'p_idx';
1000: l_params(4).paramValue := p_idx;
1001: l_params(5).paramName := 'p_repLineID';
1002: l_params(5).paramValue := p_repLineID;
1003: wip_logger.entryPoint(p_procName => 'wip_infResSched_grp.forwardSchResource',
1004: p_params => l_params,
1005: x_returnStatus => l_retStatus);
1006: end if;
1007: x_returnStatus := fnd_api.g_ret_sts_success;
1009: /* Fix for bug 5660475: If dealing with shift resource, first check if shifts are setup fine. */
1010: if( p_repLineID is null
1011: and x_resTbls.avail24Flag(p_idx) = wip_constants.no
1012: and x_resTbls.schedFlag(p_idx) <> wip_constants.sched_no) then
1013: wip_logger.log('This is a shift resource. Need to validate shift setup', l_retStatus);
1014: begin
1015: select 1
1016: into l_dummy
1017: from dual
1033: and bst.shift_num = bsd.shift_num
1034: and bst.calendar_code = bsd.calendar_code);
1035: exception
1036: when NO_DATA_FOUND then
1037: wip_logger.log('Error: Missing shifts or shift times!', l_retStatus);
1038: fnd_message.set_name('WIP', 'WIP_SHIFT_RESOURCE');
1039: fnd_message.set_token('ENTITY1', x_resTbls.resSeqNum(p_idx));
1040: fnd_message.set_token('ENTITY2', x_resTbls.opSeqNum(p_idx));
1041: fnd_msg_pub.add;
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:
1055: --for v_endDate, subtract a second to avoid overlap between cursors.
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:
1055: --for v_endDate, subtract a second to avoid overlap between cursors.
1056: if(p_repLineID is not null) then
1054:
1055: --for v_endDate, subtract a second to avoid overlap between cursors.
1056: if(p_repLineID is not null) then
1057: if(l_logLevel <= wip_constants.full_logging) then
1058: wip_logger.log('scheduling repetitive', l_retStatus);
1059: end if;
1060: open c_repTimes(v_repLineID => p_repLineID,
1061: v_orgID => p_orgID,
1062: v_startDate => l_cursorStartDate,
1067: l_shifts.endDate;
1068: close c_repTimes;
1069: elsif(x_resTbls.avail24Flag(p_idx) = wip_constants.yes) then
1070: if(l_logLevel <= wip_constants.full_logging) then
1071: wip_logger.log('scheduling 24HR resource', l_retStatus);
1072: end if;
1073: open c_24HrTimes(v_orgID => p_orgID,
1074: v_startDate => l_cursorStartDate,
1075: v_endDate => l_cursorStartDate + g_dateCursorLen - 1/86400);
1079: l_shifts.endDate;
1080: close c_24HrTimes;
1081: else
1082: if(l_logLevel <= wip_constants.full_logging) then
1083: wip_logger.log('scheduling shift resource', l_retStatus);
1084: end if;
1085: open c_shiftTimes(v_resID => x_resTbls.resID(p_idx),
1086: v_deptID => x_resTbls.deptID(p_idx),
1087: v_orgID => p_orgID,
1094:
1095: if (l_shifts.shiftNum.count = 0 ) then
1096: /* Fix for bug 5660475: If shifts are not available in the date range,
1097: we should continue to search in the next date range, instead of erroring out. */
1098: wip_logger.log('No shifts found in this period.', l_retStatus);
1099: l_resourceScheduled := false;
1100: end if;
1101:
1102: close c_shiftTimes;
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:
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
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
1126: --if shift ends before the requested start date, skip it since none of the shift
1127: --can be used. don't do this in the sql query as it degrades performance
1128: if(l_shifts.endDate(i) < p_startDate) then
1129: if (l_logLevel <= wip_constants.full_logging) then
1130: wip_logger.log('skipping shift (ends before start date)', l_retStatus);
1131: end if;
1132: goto NO_FULFILL_USAGE;--end of loop
1133: end if;
1134:
1134:
1135: --if the shift starts before the start time, adjust the shift length
1136: l_fromDate := greatest(l_shifts.startDate(i), p_startDate);
1137: if (l_logLevel <= wip_constants.full_logging) then
1138: wip_logger.log('calculated start date: ' || to_char(l_fromDate, g_logDateFmt), l_retStatus);
1139: end if;
1140:
1141: l_shiftLen := l_shifts.endDate(i) - l_fromDate;
1142: /*Bug 7015594: If shift start time is same as end time then consider it as 24 hours resource.
1145: l_shiftLen := 86400;
1146: end if;
1147:
1148: if (l_logLevel <= wip_constants.full_logging) then
1149: wip_logger.log('shiftLen(HRS) is ' || round(l_shiftLen*24, g_precision), l_retStatus);
1150: end if;
1151:
1152: if(round(l_shiftLen, g_precision) = 0) then
1153: if (l_logLevel <= wip_constants.full_logging) then
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:
1167: if(round(l_remUsage, g_precision) <= round(l_shiftLen, g_precision)) then
1167: if(round(l_remUsage, g_precision) <= round(l_shiftLen, g_precision)) then
1168: --shift fullfilled resource usage (round to approximately seconds)
1169:
1170: if (l_logLevel <= wip_constants.full_logging) then
1171: wip_logger.log('calculated resource start date:' || to_char(l_startDate, g_logDateFmt), l_retStatus);
1172: end if;
1173: x_resTbls.startDate(p_idx) := l_startDate;
1174: x_resTbls.endDate(p_idx) := l_fromDate + l_remUsage;
1175: --record shift usage
1178: x_resTbls.usgCumMinProcTime.extend(1);
1179:
1180: l_usgIdx := x_resTbls.usgStartDate.count;
1181: if (l_logLevel <= wip_constants.full_logging) then
1182: wip_logger.log('idx is ' || l_usgIdx, l_retStatus);
1183: wip_logger.log('count is ' || x_resTbls.usgStartIdx.count, l_retStatus);
1184: wip_logger.log('val is ' || x_resTbls.usgStartIdx(p_idx), l_retStatus);
1185: end if;
1186:
1179:
1180: l_usgIdx := x_resTbls.usgStartDate.count;
1181: if (l_logLevel <= wip_constants.full_logging) then
1182: wip_logger.log('idx is ' || l_usgIdx, l_retStatus);
1183: wip_logger.log('count is ' || x_resTbls.usgStartIdx.count, l_retStatus);
1184: wip_logger.log('val is ' || x_resTbls.usgStartIdx(p_idx), l_retStatus);
1185: end if;
1186:
1187: x_resTbls.usgStartIdx(p_idx) := nvl(x_resTbls.usgStartIdx(p_idx), l_usgIdx);
1180: l_usgIdx := x_resTbls.usgStartDate.count;
1181: if (l_logLevel <= wip_constants.full_logging) then
1182: wip_logger.log('idx is ' || l_usgIdx, l_retStatus);
1183: wip_logger.log('count is ' || x_resTbls.usgStartIdx.count, l_retStatus);
1184: wip_logger.log('val is ' || x_resTbls.usgStartIdx(p_idx), l_retStatus);
1185: end if;
1186:
1187: x_resTbls.usgStartIdx(p_idx) := nvl(x_resTbls.usgStartIdx(p_idx), l_usgIdx);
1188: x_resTbls.usgEndIdx(p_idx) := l_usgIdx;
1192: --shift fulfilled resource => usage end time is resource end time
1193: x_resTbls.usgEndDate(l_usgIdx) := x_resTbls.endDate(p_idx);
1194: if(l_isFirstUsg) then
1195: if (l_logLevel <= wip_constants.full_logging) then
1196: wip_logger.log('first usage', l_retStatus);
1197: end if;
1198: l_isFirstUsg := false;
1199: l_prevProcTime := 0;
1200: else
1198: l_isFirstUsg := false;
1199: l_prevProcTime := 0;
1200: else
1201: if (l_logLevel <= wip_constants.full_logging) then
1202: wip_logger.log('not first usage', l_retStatus);
1203: end if;
1204: l_prevProcTime := x_resTbls.usgCumMinProcTime(l_usgIdx - 1);
1205: end if;
1206:
1208: (24*60)*(x_resTbls.usgEndDate(l_usgIdx) -
1209: x_resTbls.usgStartDate(l_usgIdx));
1210:
1211: if (l_logLevel <= wip_constants.full_logging) then
1212: wip_logger.log('start date is ' || to_char(x_resTbls.startDate(p_idx), g_logDateFmt), l_retStatus);
1213: wip_logger.log('end date is ' || to_char(x_resTbls.endDate(p_idx), g_logDateFmt), l_retStatus);
1214: wip_logger.log('usage:' || to_char(x_resTbls.usgStartDate(l_usgIdx), g_logDateFmt) || ' - ' ||
1215: to_char(x_resTbls.usgEndDate(l_usgIdx), g_logDateFmt), l_retStatus);
1216: wip_logger.log('cum usage time:' || x_resTbls.usgCumMinProcTime(l_usgIdx), l_retStatus);
1209: x_resTbls.usgStartDate(l_usgIdx));
1210:
1211: if (l_logLevel <= wip_constants.full_logging) then
1212: wip_logger.log('start date is ' || to_char(x_resTbls.startDate(p_idx), g_logDateFmt), l_retStatus);
1213: wip_logger.log('end date is ' || to_char(x_resTbls.endDate(p_idx), g_logDateFmt), l_retStatus);
1214: wip_logger.log('usage:' || to_char(x_resTbls.usgStartDate(l_usgIdx), g_logDateFmt) || ' - ' ||
1215: to_char(x_resTbls.usgEndDate(l_usgIdx), g_logDateFmt), l_retStatus);
1216: wip_logger.log('cum usage time:' || x_resTbls.usgCumMinProcTime(l_usgIdx), l_retStatus);
1217: end if;
1210:
1211: if (l_logLevel <= wip_constants.full_logging) then
1212: wip_logger.log('start date is ' || to_char(x_resTbls.startDate(p_idx), g_logDateFmt), l_retStatus);
1213: wip_logger.log('end date is ' || to_char(x_resTbls.endDate(p_idx), g_logDateFmt), l_retStatus);
1214: wip_logger.log('usage:' || to_char(x_resTbls.usgStartDate(l_usgIdx), g_logDateFmt) || ' - ' ||
1215: to_char(x_resTbls.usgEndDate(l_usgIdx), g_logDateFmt), l_retStatus);
1216: wip_logger.log('cum usage time:' || x_resTbls.usgCumMinProcTime(l_usgIdx), l_retStatus);
1217: end if;
1218:
1212: wip_logger.log('start date is ' || to_char(x_resTbls.startDate(p_idx), g_logDateFmt), l_retStatus);
1213: wip_logger.log('end date is ' || to_char(x_resTbls.endDate(p_idx), g_logDateFmt), l_retStatus);
1214: wip_logger.log('usage:' || to_char(x_resTbls.usgStartDate(l_usgIdx), g_logDateFmt) || ' - ' ||
1215: to_char(x_resTbls.usgEndDate(l_usgIdx), g_logDateFmt), l_retStatus);
1216: wip_logger.log('cum usage time:' || x_resTbls.usgCumMinProcTime(l_usgIdx), l_retStatus);
1217: end if;
1218:
1219: l_resourceScheduled := true; --exit outer loop
1220: exit; --exit inner loop
1244: x_resTbls.usgCumMinProcTime(l_usgIdx) := l_prevProcTime +
1245: (24*60)*(x_resTbls.usgEndDate(l_usgIdx) -
1246: x_resTbls.usgStartDate(l_usgIdx));
1247: if (l_logLevel <= wip_constants.full_logging) then
1248: wip_logger.log('exhausted shift. remaining usage(HRS) is ' || round(l_remUsage*24, g_precision), l_retStatus);
1249: wip_logger.log('usage:' || to_char(x_resTbls.usgStartDate(l_usgIdx), g_logDateFmt) || ' - ' ||
1250: to_char(x_resTbls.usgEndDate(l_usgIdx), g_logDateFmt), l_retStatus);
1251: wip_logger.log('cum usage time:' || x_resTbls.usgCumMinProcTime(l_usgIdx), l_retStatus);
1252: end if;
1245: (24*60)*(x_resTbls.usgEndDate(l_usgIdx) -
1246: x_resTbls.usgStartDate(l_usgIdx));
1247: if (l_logLevel <= wip_constants.full_logging) then
1248: wip_logger.log('exhausted shift. remaining usage(HRS) is ' || round(l_remUsage*24, g_precision), l_retStatus);
1249: wip_logger.log('usage:' || to_char(x_resTbls.usgStartDate(l_usgIdx), g_logDateFmt) || ' - ' ||
1250: to_char(x_resTbls.usgEndDate(l_usgIdx), g_logDateFmt), l_retStatus);
1251: wip_logger.log('cum usage time:' || x_resTbls.usgCumMinProcTime(l_usgIdx), l_retStatus);
1252: end if;
1253: end if;
1247: if (l_logLevel <= wip_constants.full_logging) then
1248: wip_logger.log('exhausted shift. remaining usage(HRS) is ' || round(l_remUsage*24, g_precision), l_retStatus);
1249: wip_logger.log('usage:' || to_char(x_resTbls.usgStartDate(l_usgIdx), g_logDateFmt) || ' - ' ||
1250: to_char(x_resTbls.usgEndDate(l_usgIdx), g_logDateFmt), l_retStatus);
1251: wip_logger.log('cum usage time:' || x_resTbls.usgCumMinProcTime(l_usgIdx), l_retStatus);
1252: end if;
1253: end if;
1254: <
1255: null;
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);
1287: end if;
1290: fnd_msg_pub.add_exc_msg(p_pkg_name => 'wip_infResSched_grp',
1291: p_procedure_name => 'forwardSchResource',
1292: p_error_text => SQLERRM);
1293: if(l_logLevel <= wip_constants.trace_logging) then
1294: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.forwardSchResource',
1295: p_procReturnStatus => x_returnStatus,
1296: p_msg => 'unexp error: ' || SQLERRM,
1297: x_returnStatus => l_retStatus);
1298: end if;
1339: type capacity_recTbl_t is table of capacity_rec_t index by binary_integer;
1340:
1341: l_capacity capacity_recTbl_t ;
1342: l_logLevel NUMBER := fnd_log.g_current_runtime_level;
1343: l_params wip_logger.param_tbl_t;
1344: l_retStatus VARCHAR2(1);
1345:
1346: l_firstRow NUMBER;
1347: l_currRow NUMBER;
1360: l_params(2).paramValue := p_deptID;
1361: l_params(3).paramName := 'p_orgID';
1362: l_params(3).paramValue := p_orgID;
1363:
1364: wip_logger.entryPoint(p_procName => 'wip_infResSched_grp.capacityExceptions',
1365: p_params => l_params,
1366: x_returnStatus => x_returnStatus);
1367: end if;
1368:
1451: END IF;
1452: END LOOP; -- end outer loop
1453:
1454: IF(l_logLevel <= wip_constants.trace_logging) then
1455: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.capacityExceptions',
1456: p_procReturnStatus => x_returnStatus,
1457: p_msg => 'success',
1458: x_returnStatus => l_retStatus);
1459: END IF;
1460: EXCEPTION
1461: WHEN fnd_api.g_exc_unexpected_error THEN
1462: x_returnStatus := fnd_api.g_ret_sts_unexp_error;
1463: if(l_logLevel <= wip_constants.trace_logging) then
1464: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.capacityExceptions',
1465: p_procReturnStatus => x_returnStatus,
1466: p_msg => 'error',
1467: x_returnStatus => l_retStatus);
1468: end if;
1471: fnd_msg_pub.add_exc_msg(p_pkg_name => 'wip_infResSched_grp',
1472: p_procedure_name => 'capacityExceptions',
1473: p_error_text => SQLERRM);
1474: if(l_logLevel <= wip_constants.trace_logging) then
1475: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.capacityExceptions',
1476: p_procReturnStatus => x_returnStatus,
1477: p_msg => 'unexp error: ' || SQLERRM,
1478: x_returnStatus => l_retStatus);
1479: end if;
1488: x_resTbls in out NOCOPY OP_RES_RECTBL_T,
1489: x_returnStatus OUT NOCOPY VARCHAR2) is
1490: l_resStartDate DATE; -- := p_startDate;
1491: l_logLevel NUMBER := fnd_log.g_current_runtime_level;
1492: l_params wip_logger.param_tbl_t;
1493: l_retStatus VARCHAR2(1);
1494: l_fromTime NUMBER;
1495: l_maxDate DATE;
1496: l_shiftStartDate DATE;
1511: l_params(4).paramValue := p_range(2);
1512: l_params(5).paramName := 'p_schedFlag';
1513: l_params(5).paramValue := p_schedFlag;
1514:
1515: wip_logger.entryPoint(p_procName => 'wip_infResSched_grp.forwardSchedule',
1516: p_params => l_params,
1517: x_returnStatus => x_returnStatus);
1518: end if;
1519: --get the maximum date
1524: and mp.calendar_code = bc.calendar_code;
1525:
1526: while(getNextResIdx(p_range, p_schedFlag, g_forward, x_resTbls, i)) loop
1527: if (l_logLevel <= wip_constants.full_logging) then
1528: wip_logger.log('begin scheduling resource:' || x_resTbls.resID(i), l_retStatus);
1529: wip_logger.log(' operation:' || x_resTbls.opSeqNum(i), l_retStatus);
1530: wip_logger.log(' res seq num:' || x_resTbls.resSeqNum(i), l_retStatus);
1531: wip_logger.log(' schedule seq num:' || x_resTbls.schedSeqNum(i), l_retStatus);
1532: wip_logger.log(' sched flag:' || x_resTbls.schedFlag(i), l_retStatus);
1525:
1526: while(getNextResIdx(p_range, p_schedFlag, g_forward, x_resTbls, i)) loop
1527: if (l_logLevel <= wip_constants.full_logging) then
1528: wip_logger.log('begin scheduling resource:' || x_resTbls.resID(i), l_retStatus);
1529: wip_logger.log(' operation:' || x_resTbls.opSeqNum(i), l_retStatus);
1530: wip_logger.log(' res seq num:' || x_resTbls.resSeqNum(i), l_retStatus);
1531: wip_logger.log(' schedule seq num:' || x_resTbls.schedSeqNum(i), l_retStatus);
1532: wip_logger.log(' sched flag:' || x_resTbls.schedFlag(i), l_retStatus);
1533: wip_logger.log(' total usage (HRS):' || round(x_resTbls.totalDaysUsg(i)*24, g_precision), l_retStatus);
1526: while(getNextResIdx(p_range, p_schedFlag, g_forward, x_resTbls, i)) loop
1527: if (l_logLevel <= wip_constants.full_logging) then
1528: wip_logger.log('begin scheduling resource:' || x_resTbls.resID(i), l_retStatus);
1529: wip_logger.log(' operation:' || x_resTbls.opSeqNum(i), l_retStatus);
1530: wip_logger.log(' res seq num:' || x_resTbls.resSeqNum(i), l_retStatus);
1531: wip_logger.log(' schedule seq num:' || x_resTbls.schedSeqNum(i), l_retStatus);
1532: wip_logger.log(' sched flag:' || x_resTbls.schedFlag(i), l_retStatus);
1533: wip_logger.log(' total usage (HRS):' || round(x_resTbls.totalDaysUsg(i)*24, g_precision), l_retStatus);
1534:
1527: if (l_logLevel <= wip_constants.full_logging) then
1528: wip_logger.log('begin scheduling resource:' || x_resTbls.resID(i), l_retStatus);
1529: wip_logger.log(' operation:' || x_resTbls.opSeqNum(i), l_retStatus);
1530: wip_logger.log(' res seq num:' || x_resTbls.resSeqNum(i), l_retStatus);
1531: wip_logger.log(' schedule seq num:' || x_resTbls.schedSeqNum(i), l_retStatus);
1532: wip_logger.log(' sched flag:' || x_resTbls.schedFlag(i), l_retStatus);
1533: wip_logger.log(' total usage (HRS):' || round(x_resTbls.totalDaysUsg(i)*24, g_precision), l_retStatus);
1534:
1535: if(l_prevResIdx is not null) then
1528: wip_logger.log('begin scheduling resource:' || x_resTbls.resID(i), l_retStatus);
1529: wip_logger.log(' operation:' || x_resTbls.opSeqNum(i), l_retStatus);
1530: wip_logger.log(' res seq num:' || x_resTbls.resSeqNum(i), l_retStatus);
1531: wip_logger.log(' schedule seq num:' || x_resTbls.schedSeqNum(i), l_retStatus);
1532: wip_logger.log(' sched flag:' || x_resTbls.schedFlag(i), l_retStatus);
1533: wip_logger.log(' total usage (HRS):' || round(x_resTbls.totalDaysUsg(i)*24, g_precision), l_retStatus);
1534:
1535: if(l_prevResIdx is not null) then
1536: wip_logger.log('prev sched seq num is:' || x_resTbls.schedSeqNum(l_prevResIdx), l_retStatus);
1529: wip_logger.log(' operation:' || x_resTbls.opSeqNum(i), l_retStatus);
1530: wip_logger.log(' res seq num:' || x_resTbls.resSeqNum(i), l_retStatus);
1531: wip_logger.log(' schedule seq num:' || x_resTbls.schedSeqNum(i), l_retStatus);
1532: wip_logger.log(' sched flag:' || x_resTbls.schedFlag(i), l_retStatus);
1533: wip_logger.log(' total usage (HRS):' || round(x_resTbls.totalDaysUsg(i)*24, g_precision), l_retStatus);
1534:
1535: if(l_prevResIdx is not null) then
1536: wip_logger.log('prev sched seq num is:' || x_resTbls.schedSeqNum(l_prevResIdx), l_retStatus);
1537: wip_logger.log('prev op seq num is:' || x_resTbls.opSeqNum(l_prevResIdx), l_retStatus);
1532: wip_logger.log(' sched flag:' || x_resTbls.schedFlag(i), l_retStatus);
1533: wip_logger.log(' total usage (HRS):' || round(x_resTbls.totalDaysUsg(i)*24, g_precision), l_retStatus);
1534:
1535: if(l_prevResIdx is not null) then
1536: wip_logger.log('prev sched seq num is:' || x_resTbls.schedSeqNum(l_prevResIdx), l_retStatus);
1537: wip_logger.log('prev op seq num is:' || x_resTbls.opSeqNum(l_prevResIdx), l_retStatus);
1538: end if;
1539: end if;
1540:
1533: wip_logger.log(' total usage (HRS):' || round(x_resTbls.totalDaysUsg(i)*24, g_precision), l_retStatus);
1534:
1535: if(l_prevResIdx is not null) then
1536: wip_logger.log('prev sched seq num is:' || x_resTbls.schedSeqNum(l_prevResIdx), l_retStatus);
1537: wip_logger.log('prev op seq num is:' || x_resTbls.opSeqNum(l_prevResIdx), l_retStatus);
1538: end if;
1539: end if;
1540:
1541: l_doneSchedBatch := false;
1544: x_resTbls.schedSeqNum(i) = x_resTbls.schedSeqNum(l_prevResIdx) and
1545: x_resTbls.opSeqNum(i) = x_resTbls.opSeqNum(l_prevResIdx)) then
1546: l_schedulingBatch := true;
1547: if (l_logLevel <= wip_constants.full_logging) then
1548: wip_logger.log('setting sched batch to true', l_retStatus);
1549: end if;
1550: --just finished scheduling batch
1551: elsif(l_schedulingBatch) then
1552: l_schedulingBatch := false;
1551: elsif(l_schedulingBatch) then
1552: l_schedulingBatch := false;
1553: l_doneSchedBatch := true;
1554: if (l_logLevel <= wip_constants.full_logging) then
1555: wip_logger.log('setting done sched batch to true', l_retStatus);
1556: end if;
1557: end if;
1558:
1559:
1573: l_prevResIdx := i;
1574: end loop;
1575:
1576: if(l_logLevel <= wip_constants.trace_logging) then
1577: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.forwardSchedule',
1578: p_procReturnStatus => x_returnStatus,
1579: p_msg => 'success',
1580: x_returnStatus => l_retStatus);
1581: end if;
1582: exception
1583: when fnd_api.g_exc_unexpected_error then
1584: x_returnStatus := fnd_api.g_ret_sts_unexp_error;
1585: if(l_logLevel <= wip_constants.trace_logging) then
1586: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.forwardSchedule',
1587: p_procReturnStatus => x_returnStatus,
1588: p_msg => 'error',
1589: x_returnStatus => l_retStatus);
1590: end if;
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);
1601: end if;
1608: p_curIdx in number,
1609: p_doneSchedBatch in boolean,
1610: p_prevIdx in number) return date is
1611: l_retStatus VARCHAR2(1);
1612: l_params wip_logger.param_tbl_t;
1613: i number;
1614: l_minStartDate date;
1615: l_logLevel NUMBER := fnd_log.g_current_runtime_level;
1616: l_bool boolean;
1630: end if;
1631: l_params(6).paramName := 'p_prevIdx';
1632: l_params(6).paramValue := p_prevIdx;
1633:
1634: wip_logger.entryPoint(p_procName => 'wip_infResSched_grp.getEndDate',
1635: p_params => l_params,
1636: x_returnStatus => l_retStatus);
1637: end if;
1638:
1641: i := p_curIdx;
1642:
1643: if(p_doneSchedBatch) then
1644: if(l_logLevel <= wip_constants.full_logging) then
1645: wip_logger.log('done scheduling batch', l_retStatus);
1646: end if;
1647: while(getNextResIdx(p_range, p_schedFlag, g_forward, p_resTbls, i)) loop
1648: if(l_logLevel <= wip_constants.full_logging) then
1649: wip_logger.log('in loop', l_retStatus);
1645: wip_logger.log('done scheduling batch', l_retStatus);
1646: end if;
1647: while(getNextResIdx(p_range, p_schedFlag, g_forward, p_resTbls, i)) loop
1648: if(l_logLevel <= wip_constants.full_logging) then
1649: wip_logger.log('in loop', l_retStatus);
1650: wip_logger.log('resID' || p_resTbls.resID(i), l_retStatus);
1651: wip_logger.log('opSeq' || p_resTbls.opSeqNum(i), l_retStatus);
1652: wip_logger.log('schSeq' || p_resTbls.schedSeqNum(i), l_retStatus);
1653: wip_logger.log('idx' || i, l_retStatus);
1646: end if;
1647: while(getNextResIdx(p_range, p_schedFlag, g_forward, p_resTbls, i)) loop
1648: if(l_logLevel <= wip_constants.full_logging) then
1649: wip_logger.log('in loop', l_retStatus);
1650: wip_logger.log('resID' || p_resTbls.resID(i), l_retStatus);
1651: wip_logger.log('opSeq' || p_resTbls.opSeqNum(i), l_retStatus);
1652: wip_logger.log('schSeq' || p_resTbls.schedSeqNum(i), l_retStatus);
1653: wip_logger.log('idx' || i, l_retStatus);
1654: end if;
1647: while(getNextResIdx(p_range, p_schedFlag, g_forward, p_resTbls, i)) loop
1648: if(l_logLevel <= wip_constants.full_logging) then
1649: wip_logger.log('in loop', l_retStatus);
1650: wip_logger.log('resID' || p_resTbls.resID(i), l_retStatus);
1651: wip_logger.log('opSeq' || p_resTbls.opSeqNum(i), l_retStatus);
1652: wip_logger.log('schSeq' || p_resTbls.schedSeqNum(i), l_retStatus);
1653: wip_logger.log('idx' || i, l_retStatus);
1654: end if;
1655: if(p_resTbls.schedSeqNum(i) = p_resTbls.schedSeqNum(p_prevIdx) and
1648: if(l_logLevel <= wip_constants.full_logging) then
1649: wip_logger.log('in loop', l_retStatus);
1650: wip_logger.log('resID' || p_resTbls.resID(i), l_retStatus);
1651: wip_logger.log('opSeq' || p_resTbls.opSeqNum(i), l_retStatus);
1652: wip_logger.log('schSeq' || p_resTbls.schedSeqNum(i), l_retStatus);
1653: wip_logger.log('idx' || i, l_retStatus);
1654: end if;
1655: if(p_resTbls.schedSeqNum(i) = p_resTbls.schedSeqNum(p_prevIdx) and
1656: p_resTbls.opSeqNum(i) = p_resTbls.opSeqNum(p_prevIdx)) then
1649: wip_logger.log('in loop', l_retStatus);
1650: wip_logger.log('resID' || p_resTbls.resID(i), l_retStatus);
1651: wip_logger.log('opSeq' || p_resTbls.opSeqNum(i), l_retStatus);
1652: wip_logger.log('schSeq' || p_resTbls.schedSeqNum(i), l_retStatus);
1653: wip_logger.log('idx' || i, l_retStatus);
1654: end if;
1655: if(p_resTbls.schedSeqNum(i) = p_resTbls.schedSeqNum(p_prevIdx) and
1656: p_resTbls.opSeqNum(i) = p_resTbls.opSeqNum(p_prevIdx)) then
1657: l_minStartDate := least(nvl(l_minStartDate, p_resTbls.startDate(i)), p_resTbls.startDate(i));
1655: if(p_resTbls.schedSeqNum(i) = p_resTbls.schedSeqNum(p_prevIdx) and
1656: p_resTbls.opSeqNum(i) = p_resTbls.opSeqNum(p_prevIdx)) then
1657: l_minStartDate := least(nvl(l_minStartDate, p_resTbls.startDate(i)), p_resTbls.startDate(i));
1658: if(l_logLevel <= wip_constants.full_logging) then
1659: wip_logger.log('resource in batch. startDate:' || to_char(p_resTbls.startDate(i), g_logDateFmt), l_retStatus);
1660: end if;
1661: else
1662: if(l_logLevel <= wip_constants.full_logging) then
1663: wip_logger.log('resource not in batch.', l_retStatus);
1659: wip_logger.log('resource in batch. startDate:' || to_char(p_resTbls.startDate(i), g_logDateFmt), l_retStatus);
1660: end if;
1661: else
1662: if(l_logLevel <= wip_constants.full_logging) then
1663: wip_logger.log('resource not in batch.', l_retStatus);
1664: end if;
1665: exit;
1666: end if;
1667: end loop;
1670: l_minStartDate := p_resTbls.startDate(i);
1671: end if;
1672:
1673: if (l_logLevel <= wip_constants.trace_logging) then
1674: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.getEndDate',
1675: p_procReturnStatus => to_char(l_minStartDate),
1676: p_msg => 'finished scheduling',
1677: x_returnStatus => l_retStatus);
1678: end if;
1756: endDate date_tbl_t);
1757:
1758: l_shifts shift_recTbl_t;
1759: l_logLevel NUMBER := fnd_log.g_current_runtime_level;
1760: l_params wip_logger.param_tbl_t;
1761: l_retStatus VARCHAR2(1);
1762: l_resourceScheduled boolean := false;
1763: l_cursorEndDate date := p_endDate;
1764: l_shiftEndDate date;
1785: l_params(4).paramName := 'p_idx';
1786: l_params(4).paramValue := p_idx;
1787: l_params(5).paramName := 'p_repLineID';
1788: l_params(5).paramValue := p_repLineID;
1789: wip_logger.entryPoint(p_procName => 'wip_infResSched_grp.backwardSchResource',
1790: p_params => l_params,
1791: x_returnStatus => l_retStatus);
1792: end if;
1793: /* Fix for bug 5660475: If dealing with shift resource, first check if shifts are setup fine. */
1793: /* Fix for bug 5660475: If dealing with shift resource, first check if shifts are setup fine. */
1794: if( p_repLineID is null
1795: and x_resTbls.avail24Flag(p_idx) = wip_constants.no
1796: and x_resTbls.schedFlag(p_idx) <> wip_constants.sched_no) then
1797: wip_logger.log('This is a shift resource. Need to validate shift setup', l_retStatus);
1798: begin
1799: select 1
1800: into l_dummy
1801: from dual
1817: and bst.shift_num = bsd.shift_num
1818: and bst.calendar_code = bsd.calendar_code);
1819: exception
1820: when NO_DATA_FOUND then
1821: wip_logger.log('Error: Missing shifts or shift times!', l_retStatus);
1822: fnd_message.set_name('WIP', 'WIP_SHIFT_RESOURCE');
1823: fnd_message.set_token('ENTITY1', x_resTbls.resSeqNum(p_idx));
1824: fnd_message.set_token('ENTITY2', x_resTbls.opSeqNum(p_idx));
1825: fnd_msg_pub.add;
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:
1840: --for v_endDate, subtract a second to avoid overlap between cursors.
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:
1840: --for v_endDate, subtract a second to avoid overlap between cursors.
1841: if(p_repLineID is not null) then
1870:
1871: if (l_shifts.shiftNum.count = 0 ) then
1872: /* Fix for bug 5660475: If shifts are not available in the date range,
1873: we should continue to search in the next date range, instead of erroring out. */
1874: wip_logger.log('No shifts found in this period.', l_retStatus);
1875: l_resourceScheduled := false;
1876: end if;
1877:
1878: close c_shiftTimes;
1885: l_shifts.endDate(i) := l_shifts.endDate(i) + 1;
1886: end if;
1887: if (l_logLevel <= wip_constants.full_logging) then
1888:
1889: wip_logger.log('**********shiftNum:' || l_shifts.shiftNum(i), l_retStatus);
1890: wip_logger.log('**shift start date:' || to_char(l_shifts.startDate(i), g_logDateFmt), l_retStatus);
1891: wip_logger.log('****shift end date:' || to_char(l_shifts.endDate(i), g_logDateFmt), l_retStatus);
1892: end if;
1893:
1886: end if;
1887: if (l_logLevel <= wip_constants.full_logging) then
1888:
1889: wip_logger.log('**********shiftNum:' || l_shifts.shiftNum(i), l_retStatus);
1890: wip_logger.log('**shift start date:' || to_char(l_shifts.startDate(i), g_logDateFmt), l_retStatus);
1891: wip_logger.log('****shift end date:' || to_char(l_shifts.endDate(i), g_logDateFmt), l_retStatus);
1892: end if;
1893:
1894: --if shift starts after the requested end date, skip it since none of the shift
1887: if (l_logLevel <= wip_constants.full_logging) then
1888:
1889: wip_logger.log('**********shiftNum:' || l_shifts.shiftNum(i), l_retStatus);
1890: wip_logger.log('**shift start date:' || to_char(l_shifts.startDate(i), g_logDateFmt), l_retStatus);
1891: wip_logger.log('****shift end date:' || to_char(l_shifts.endDate(i), g_logDateFmt), l_retStatus);
1892: end if;
1893:
1894: --if shift starts after the requested end date, skip it since none of the shift
1895: --can be used. don't do this in the sql query as it degrades performance
1894: --if shift starts after the requested end date, skip it since none of the shift
1895: --can be used. don't do this in the sql query as it degrades performance
1896: if(l_shifts.startDate(i) > p_endDate) then
1897: if (l_logLevel <= wip_constants.full_logging) then
1898: wip_logger.log('skipping shift (starts after end date)', l_retStatus);
1899: end if;
1900: goto NO_FULFILL_USAGE;--end of loop
1901: end if;
1902:
1902:
1903: --if the shift ends before the end time, adjust the shift length
1904: l_toDate := least(l_shifts.endDate(i), p_endDate);
1905: if (l_logLevel <= wip_constants.full_logging) then
1906: wip_logger.log('calculated end date: ' || to_char(l_toDate, g_logDateFmt), l_retStatus);
1907: end if;
1908:
1909: l_shiftLen := l_toDate - l_shifts.startDate(i);
1910: /*Bug 7015594: If shift start time is same as end time then consider it as 24 hours resource.
1914: end if;
1915:
1916:
1917: if (l_logLevel <= wip_constants.full_logging) then
1918: wip_logger.log('shiftLen(HRS) is ' || round(l_shiftLen*24, g_precision), l_retStatus);
1919: end if;
1920:
1921: if(round(l_shiftLen, g_precision) = 0) then
1922: if (l_logLevel <= wip_constants.full_logging) then
1919: end if;
1920:
1921: if(round(l_shiftLen, g_precision) = 0) then
1922: if (l_logLevel <= wip_constants.full_logging) then
1923: wip_logger.log('skipping shift (no usage)', l_retStatus);
1924: end if;
1925: goto NO_FULFILL_USAGE;--end of loop
1926: end if;
1927:
1928:
1929: if(l_endDate is null) then
1930: l_endDate := l_toDate;
1931: if (l_logLevel <= wip_constants.full_logging) then
1932: wip_logger.log('calculated resource end date:' || to_char(l_endDate, g_logDateFmt), l_retStatus);
1933: end if;
1934: end if;
1935:
1936: if(round(l_remUsage, g_precision) <= round(l_shiftLen, g_precision)) then
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;
1953: x_resTbls.usgStartIdx(p_idx) := nvl(x_resTbls.usgStartIdx(p_idx), l_usgIdx);
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;
1953: x_resTbls.usgStartIdx(p_idx) := nvl(x_resTbls.usgStartIdx(p_idx), l_usgIdx);
1954: x_resTbls.usgEndIdx(p_idx) := l_usgIdx;
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;
1953: x_resTbls.usgStartIdx(p_idx) := nvl(x_resTbls.usgStartIdx(p_idx), l_usgIdx);
1954: x_resTbls.usgEndIdx(p_idx) := l_usgIdx;
1955:
1958: x_resTbls.usgStartDate(l_usgIdx) := x_resTbls.startDate(p_idx);
1959: x_resTbls.usgEndDate(l_usgIdx) := l_toDate;--l_shifts.endDate(i);
1960:
1961: if (l_logLevel <= wip_constants.full_logging) then
1962: wip_logger.log('start date is ' || to_char(x_resTbls.startDate(p_idx), g_logDateFmt), l_retStatus);
1963: wip_logger.log('end date is ' || to_char(x_resTbls.endDate(p_idx), g_logDateFmt), l_retStatus);
1964: wip_logger.log('usgIdx:' || l_usgIdx, l_retStatus);
1965: wip_logger.log('usage:' || to_char(x_resTbls.usgStartDate(l_usgIdx), g_logDateFmt) || ' - ' ||
1966: to_char(x_resTbls.usgEndDate(l_usgIdx), g_logDateFmt), l_retStatus);
1959: x_resTbls.usgEndDate(l_usgIdx) := l_toDate;--l_shifts.endDate(i);
1960:
1961: if (l_logLevel <= wip_constants.full_logging) then
1962: wip_logger.log('start date is ' || to_char(x_resTbls.startDate(p_idx), g_logDateFmt), l_retStatus);
1963: wip_logger.log('end date is ' || to_char(x_resTbls.endDate(p_idx), g_logDateFmt), l_retStatus);
1964: wip_logger.log('usgIdx:' || l_usgIdx, l_retStatus);
1965: wip_logger.log('usage:' || to_char(x_resTbls.usgStartDate(l_usgIdx), g_logDateFmt) || ' - ' ||
1966: to_char(x_resTbls.usgEndDate(l_usgIdx), g_logDateFmt), l_retStatus);
1967: end if;
1960:
1961: if (l_logLevel <= wip_constants.full_logging) then
1962: wip_logger.log('start date is ' || to_char(x_resTbls.startDate(p_idx), g_logDateFmt), l_retStatus);
1963: wip_logger.log('end date is ' || to_char(x_resTbls.endDate(p_idx), g_logDateFmt), l_retStatus);
1964: wip_logger.log('usgIdx:' || l_usgIdx, l_retStatus);
1965: wip_logger.log('usage:' || to_char(x_resTbls.usgStartDate(l_usgIdx), g_logDateFmt) || ' - ' ||
1966: to_char(x_resTbls.usgEndDate(l_usgIdx), g_logDateFmt), l_retStatus);
1967: end if;
1968:
1961: if (l_logLevel <= wip_constants.full_logging) then
1962: wip_logger.log('start date is ' || to_char(x_resTbls.startDate(p_idx), g_logDateFmt), l_retStatus);
1963: wip_logger.log('end date is ' || to_char(x_resTbls.endDate(p_idx), g_logDateFmt), l_retStatus);
1964: wip_logger.log('usgIdx:' || l_usgIdx, l_retStatus);
1965: wip_logger.log('usage:' || to_char(x_resTbls.usgStartDate(l_usgIdx), g_logDateFmt) || ' - ' ||
1966: to_char(x_resTbls.usgEndDate(l_usgIdx), g_logDateFmt), l_retStatus);
1967: end if;
1968:
1969: l_resourceScheduled := true; --exit outer loop
1985: --resource consumed until end of the shift
1986: x_resTbls.usgEndDate(l_usgIdx) := l_toDate;
1987:
1988: if (l_logLevel <= wip_constants.full_logging) then
1989: wip_logger.log('exhausted shift. remaining usage(HRS) is ' || round(l_remUsage*24, g_precision), l_retStatus);
1990: wip_logger.log('usage:' || to_char(x_resTbls.usgStartDate(l_usgIdx), g_logDateFmt) || ' - ' ||
1991: to_char(x_resTbls.usgEndDate(l_usgIdx), g_logDateFmt), l_retStatus);
1992: wip_logger.log('usgIdx:' || l_usgIdx, l_retStatus);
1993: end if;
1986: x_resTbls.usgEndDate(l_usgIdx) := l_toDate;
1987:
1988: if (l_logLevel <= wip_constants.full_logging) then
1989: wip_logger.log('exhausted shift. remaining usage(HRS) is ' || round(l_remUsage*24, g_precision), l_retStatus);
1990: wip_logger.log('usage:' || to_char(x_resTbls.usgStartDate(l_usgIdx), g_logDateFmt) || ' - ' ||
1991: to_char(x_resTbls.usgEndDate(l_usgIdx), g_logDateFmt), l_retStatus);
1992: wip_logger.log('usgIdx:' || l_usgIdx, l_retStatus);
1993: end if;
1994: end if;
1988: if (l_logLevel <= wip_constants.full_logging) then
1989: wip_logger.log('exhausted shift. remaining usage(HRS) is ' || round(l_remUsage*24, g_precision), l_retStatus);
1990: wip_logger.log('usage:' || to_char(x_resTbls.usgStartDate(l_usgIdx), g_logDateFmt) || ' - ' ||
1991: to_char(x_resTbls.usgEndDate(l_usgIdx), g_logDateFmt), l_retStatus);
1992: wip_logger.log('usgIdx:' || l_usgIdx, l_retStatus);
1993: end if;
1994: end if;
1995: <
1996: null;
2002:
2003: --if the next start date is after the end of the calendar, then we can't schedule anything
2004: if(l_cursorEndDate < p_minDate) then
2005: if (l_logLevel <= wip_constants.full_logging) then
2006: wip_logger.log('exhausted calendar. remaining usage(HRS) is ' || round(l_remUsage*24, g_precision), l_retStatus);
2007: end if;
2008: fnd_message.set_name('WIP', 'WIP_NO_CALENDAR');
2009: fnd_msg_pub.add;
2010: raise fnd_api.g_exc_unexpected_error;
2015: --resource usages are in reverse chronological order. Flip them so they go start to end.
2016: i := x_resTbls.usgStartIdx(p_idx);
2017: j := x_resTbls.usgEndIdx(p_idx);
2018: if (l_logLevel <= wip_constants.full_logging) then
2019: wip_logger.log('i: ' || i || '; j: ' || j, l_retStatus);
2020: end if;
2021:
2022: while(j > i) loop
2023: l_tmpUsgStartDate := x_resTbls.usgStartDate(j);
2034: l_prevProcTime := x_resTbls.usgCumMinProcTime(i);
2035: end loop;
2036:
2037: if(l_logLevel <= wip_constants.trace_logging) then
2038: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.backwardSchResource',
2039: p_procReturnStatus => null,
2040: p_msg => 'success',
2041: x_returnStatus => l_retStatus);
2042: end if;
2043: exception
2044: when fnd_api.g_exc_unexpected_error then
2045: x_returnStatus := fnd_api.g_ret_sts_unexp_error;
2046: if(l_logLevel <= wip_constants.trace_logging) then
2047: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.backwardSchResource',
2048: p_procReturnStatus => x_returnStatus,
2049: p_msg => 'error',
2050: x_returnStatus => l_retStatus);
2051: end if;
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);
2063: end if;
2072: x_returnStatus out nocopy varchar2) is
2073: j number;
2074: l_minStartDate DATE;
2075: l_logLevel number := fnd_log.g_current_runtime_level;
2076: l_params wip_logger.param_tbl_t;
2077: l_retStatus VARCHAR2(1);
2078: begin
2079: if (l_logLevel <= wip_constants.trace_logging) then
2080: l_params(1).paramName := 'p_orgID';
2089: l_params(5).paramValue := p_schedFlag;
2090: l_params(6).paramName := 'p_startIdx';
2091: l_params(6).paramValue := p_startIdx;
2092:
2093: wip_logger.entryPoint(p_procName => 'wip_infResSched_grp.forwardScheduleBatch',
2094: p_params => l_params,
2095: x_returnStatus => x_returnStatus);
2096: end if;
2097:
2104: x_resTbls.opSeqNum(j) = x_resTbls.opSeqNum(p_startIdx)) then
2105: --calculate min start date
2106: l_minStartDate := least(l_minStartDate, x_resTbls.startDate(j));
2107: if(l_logLevel <= wip_constants.full_logging) then
2108: wip_logger.log('resID:' || x_resTbls.resID(j), l_retStatus);
2109: wip_logger.log('res start date:' || to_char(x_resTbls.startDate(j), g_logDateFmt), l_retStatus);
2110: wip_logger.log('min start date:' || to_char(l_minStartDate, g_logDateFmt), l_retStatus);
2111: end if;
2112: --clear backward scheduled times
2105: --calculate min start date
2106: l_minStartDate := least(l_minStartDate, x_resTbls.startDate(j));
2107: if(l_logLevel <= wip_constants.full_logging) then
2108: wip_logger.log('resID:' || x_resTbls.resID(j), l_retStatus);
2109: wip_logger.log('res start date:' || to_char(x_resTbls.startDate(j), g_logDateFmt), l_retStatus);
2110: wip_logger.log('min start date:' || to_char(l_minStartDate, g_logDateFmt), l_retStatus);
2111: end if;
2112: --clear backward scheduled times
2113: x_resTbls.usgStartIdx(j) := null;
2106: l_minStartDate := least(l_minStartDate, x_resTbls.startDate(j));
2107: if(l_logLevel <= wip_constants.full_logging) then
2108: wip_logger.log('resID:' || x_resTbls.resID(j), l_retStatus);
2109: wip_logger.log('res start date:' || to_char(x_resTbls.startDate(j), g_logDateFmt), l_retStatus);
2110: wip_logger.log('min start date:' || to_char(l_minStartDate, g_logDateFmt), l_retStatus);
2111: end if;
2112: --clear backward scheduled times
2113: x_resTbls.usgStartIdx(j) := null;
2114: x_resTbls.usgEndIdx(j) := null;
2125: x_resTbls => x_resTbls,
2126: x_returnStatus => x_returnStatus);
2127:
2128: if(l_logLevel <= wip_constants.trace_logging) then
2129: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.forwardScheduleBatch',
2130: p_procReturnStatus => x_returnStatus,
2131: p_msg => 'success?',
2132: x_returnStatus => l_retStatus);
2133: end if;
2142: x_resTbls in out NOCOPY OP_RES_RECTBL_T,
2143: x_returnStatus OUT NOCOPY VARCHAR2) is
2144: l_resEndDate DATE;
2145: l_logLevel NUMBER := fnd_log.g_current_runtime_level;
2146: l_params wip_logger.param_tbl_t;
2147: l_retStatus VARCHAR2(1);
2148: l_fromTime NUMBER;
2149: l_minDate DATE;
2150: l_shiftStartDate DATE;
2169: l_params(5).paramValue := p_range(2);
2170: l_params(6).paramName := 'p_schedFlag';
2171: l_params(6).paramValue := p_schedFlag;
2172:
2173: wip_logger.entryPoint(p_procName => 'wip_infResSched_grp.backwardSchedule',
2174: p_params => l_params,
2175: x_returnStatus => x_returnStatus);
2176: end if;
2177: --get the maximum date
2183: and mp.calendar_code = bc.calendar_code;
2184:
2185: while(getNextResIdx(p_range, p_schedFlag, g_backward, x_resTbls, i)) loop
2186: if (l_logLevel <= wip_constants.full_logging) then
2187: wip_logger.log('begin scheduling resource:' || x_resTbls.resID(i), l_retStatus);
2188: wip_logger.log(' operation:' || x_resTbls.opSeqNum(i), l_retStatus);
2189: wip_logger.log(' schedule seq num:' || x_resTbls.schedSeqNum(i), l_retStatus);
2190: wip_logger.log(' sched flag:' || x_resTbls.schedFlag(i), l_retStatus);
2191: wip_logger.log(' total usage (HRS):' || round(x_resTbls.totalDaysUsg(i)*24, g_precision), l_retStatus);
2184:
2185: while(getNextResIdx(p_range, p_schedFlag, g_backward, x_resTbls, i)) loop
2186: if (l_logLevel <= wip_constants.full_logging) then
2187: wip_logger.log('begin scheduling resource:' || x_resTbls.resID(i), l_retStatus);
2188: wip_logger.log(' operation:' || x_resTbls.opSeqNum(i), l_retStatus);
2189: wip_logger.log(' schedule seq num:' || x_resTbls.schedSeqNum(i), l_retStatus);
2190: wip_logger.log(' sched flag:' || x_resTbls.schedFlag(i), l_retStatus);
2191: wip_logger.log(' total usage (HRS):' || round(x_resTbls.totalDaysUsg(i)*24, g_precision), l_retStatus);
2192:
2185: while(getNextResIdx(p_range, p_schedFlag, g_backward, x_resTbls, i)) loop
2186: if (l_logLevel <= wip_constants.full_logging) then
2187: wip_logger.log('begin scheduling resource:' || x_resTbls.resID(i), l_retStatus);
2188: wip_logger.log(' operation:' || x_resTbls.opSeqNum(i), l_retStatus);
2189: wip_logger.log(' schedule seq num:' || x_resTbls.schedSeqNum(i), l_retStatus);
2190: wip_logger.log(' sched flag:' || x_resTbls.schedFlag(i), l_retStatus);
2191: wip_logger.log(' total usage (HRS):' || round(x_resTbls.totalDaysUsg(i)*24, g_precision), l_retStatus);
2192:
2193: if(l_prevResIdx is not null) then
2186: if (l_logLevel <= wip_constants.full_logging) then
2187: wip_logger.log('begin scheduling resource:' || x_resTbls.resID(i), l_retStatus);
2188: wip_logger.log(' operation:' || x_resTbls.opSeqNum(i), l_retStatus);
2189: wip_logger.log(' schedule seq num:' || x_resTbls.schedSeqNum(i), l_retStatus);
2190: wip_logger.log(' sched flag:' || x_resTbls.schedFlag(i), l_retStatus);
2191: wip_logger.log(' total usage (HRS):' || round(x_resTbls.totalDaysUsg(i)*24, g_precision), l_retStatus);
2192:
2193: if(l_prevResIdx is not null) then
2194: wip_logger.log('prev sched seq num is:' || x_resTbls.schedSeqNum(l_prevResIdx), l_retStatus);
2187: wip_logger.log('begin scheduling resource:' || x_resTbls.resID(i), l_retStatus);
2188: wip_logger.log(' operation:' || x_resTbls.opSeqNum(i), l_retStatus);
2189: wip_logger.log(' schedule seq num:' || x_resTbls.schedSeqNum(i), l_retStatus);
2190: wip_logger.log(' sched flag:' || x_resTbls.schedFlag(i), l_retStatus);
2191: wip_logger.log(' total usage (HRS):' || round(x_resTbls.totalDaysUsg(i)*24, g_precision), l_retStatus);
2192:
2193: if(l_prevResIdx is not null) then
2194: wip_logger.log('prev sched seq num is:' || x_resTbls.schedSeqNum(l_prevResIdx), l_retStatus);
2195: wip_logger.log('prev op seq num is:' || x_resTbls.opSeqNum(l_prevResIdx), l_retStatus);
2190: wip_logger.log(' sched flag:' || x_resTbls.schedFlag(i), l_retStatus);
2191: wip_logger.log(' total usage (HRS):' || round(x_resTbls.totalDaysUsg(i)*24, g_precision), l_retStatus);
2192:
2193: if(l_prevResIdx is not null) then
2194: wip_logger.log('prev sched seq num is:' || x_resTbls.schedSeqNum(l_prevResIdx), l_retStatus);
2195: wip_logger.log('prev op seq num is:' || x_resTbls.opSeqNum(l_prevResIdx), l_retStatus);
2196: end if;
2197: end if;
2198:
2191: wip_logger.log(' total usage (HRS):' || round(x_resTbls.totalDaysUsg(i)*24, g_precision), l_retStatus);
2192:
2193: if(l_prevResIdx is not null) then
2194: wip_logger.log('prev sched seq num is:' || x_resTbls.schedSeqNum(l_prevResIdx), l_retStatus);
2195: wip_logger.log('prev op seq num is:' || x_resTbls.opSeqNum(l_prevResIdx), l_retStatus);
2196: end if;
2197: end if;
2198:
2199: l_doneSchedBatch := false;
2202: x_resTbls.schedSeqNum(i) = x_resTbls.schedSeqNum(l_prevResIdx) and
2203: x_resTbls.opSeqNum(i) = x_resTbls.opSeqNum(l_prevResIdx)) then
2204: l_schedulingBatch := true;
2205: if(l_logLevel <= wip_constants.full_logging) then
2206: wip_logger.log('starting batch', l_retStatus);
2207: end if;
2208:
2209: --just finished scheduling batch
2210: elsif(l_schedulingBatch) then
2208:
2209: --just finished scheduling batch
2210: elsif(l_schedulingBatch) then
2211: if(l_logLevel <= wip_constants.full_logging) then
2212: wip_logger.log('done bkwd scheduling batch, now fwd sched', l_retStatus);
2213: end if;
2214:
2215: l_schedulingBatch := false;
2216: l_doneSchedBatch := true;
2223: x_resTbls => x_resTbls,
2224: x_returnStatus => x_returnStatus);
2225: if(x_returnStatus <> fnd_api.g_ret_sts_success) then
2226: if(l_logLevel <= wip_constants.full_logging) then
2227: wip_logger.log('simult batch scheduling failed', l_retStatus);
2228: end if;
2229: raise fnd_api.g_exc_unexpected_error;
2230: end if;
2231: end if;
2240:
2241: backwardSchResource(p_orgID, l_resEndDate, l_minDate, i, p_repLineID, x_resTbls, x_returnStatus);
2242: if(x_returnStatus <> fnd_api.g_ret_sts_success) then
2243: if(l_logLevel <= wip_constants.full_logging) then
2244: wip_logger.log('backward schedule failed', l_retStatus);
2245: end if;
2246: raise fnd_api.g_exc_unexpected_error;
2247: end if;
2248:
2250: end loop;
2251:
2252: if(l_schedulingBatch) then
2253: if(l_logLevel <= wip_constants.full_logging) then
2254: wip_logger.log('done bkwd scheduling last batch, now fwd sched', l_retStatus);
2255: end if;
2256: forwardScheduleBatch(p_orgID => p_orgID,
2257: p_repLineID => p_repLineID,
2258: p_range => p_range,
2261: x_resTbls => x_resTbls,
2262: x_returnStatus => x_returnStatus);
2263: if(x_returnStatus <> fnd_api.g_ret_sts_success) then
2264: if(l_logLevel <= wip_constants.full_logging) then
2265: wip_logger.log('final simult batch scheduling failed', l_retStatus);
2266: end if;
2267: raise fnd_api.g_exc_unexpected_error;
2268: end if;
2269: end if;
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);
2276: end if;
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);
2286: end if;
2290: p_procedure_name => 'backwardSchedule',
2291: p_error_text => SQLERRM);
2292:
2293: if(l_logLevel <= wip_constants.trace_logging) then
2294: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.backwardSchedule',
2295: p_procReturnStatus => x_returnStatus,
2296: p_msg => 'unexp error:' || SQLERRM,
2297: x_returnStatus => l_retStatus);
2298: end if;
2304: p_opTbl in op_tbl_t,
2305: x_resTbls IN OUT NOCOPY OP_RES_RECTBL_T,
2306: x_returnStatus OUT NOCOPY VARCHAR2) is
2307: l_retStatus VARCHAR2(1);
2308: l_params wip_logger.param_tbl_t;
2309: l_logLevel NUMBER := fnd_log.g_current_runtime_level;
2310: begin
2311: if(l_logLevel <= wip_constants.trace_logging) then
2312: l_params(1).paramName := 'p_orgID';
2313: l_params(1).paramValue := p_orgID;
2314: l_params(2).paramName := 'p_repLineID';
2315: l_params(2).paramValue := p_repLineID;
2316:
2317: wip_logger.entryPoint(p_procName => 'wip_infResSched_grp.schedulePriorResources',
2318: p_params => l_params,
2319: x_returnStatus => l_retStatus);
2320: end if;
2321:
2336: end if;
2337: end loop;
2338:
2339: if(l_logLevel <= wip_constants.trace_logging) then
2340: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.schedulePriorResources',
2341: p_procReturnStatus => x_returnStatus,
2342: p_msg => 'success',
2343: x_returnStatus => l_retStatus);
2344: end if;
2345: exception
2346: when fnd_api.g_exc_unexpected_error then
2347: x_returnStatus := fnd_api.g_ret_sts_unexp_error;
2348: if(l_logLevel <= wip_constants.trace_logging) then
2349: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.schedulePriorResources',
2350: p_procReturnStatus => x_returnStatus,
2351: p_msg => 'backward scheduling failed',
2352: x_returnStatus => l_retStatus);
2353: end if;
2353: end if;
2354: when others then
2355: x_returnStatus := fnd_api.g_ret_sts_unexp_error;
2356: if(l_logLevel <= wip_constants.trace_logging) then
2357: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.schedulePriorResources',
2358: p_procReturnStatus => x_returnStatus,
2359: p_msg => 'unexp error:' || SQLERRM,
2360: x_returnStatus => l_retStatus);
2361: end if;
2367: x_resTbls IN OUT NOCOPY OP_RES_RECTBL_T,
2368: x_returnStatus OUT NOCOPY VARCHAR2) is
2369:
2370: l_retStatus VARCHAR2(1);
2371: l_params wip_logger.param_tbl_t;
2372: l_logLevel NUMBER := fnd_log.g_current_runtime_level;
2373: begin
2374: if(l_logLevel <= wip_constants.trace_logging) then
2375: l_params(1).paramName := 'p_orgID';
2376: l_params(1).paramValue := p_orgID;
2377: l_params(2).paramName := 'p_repLineID';
2378: l_params(2).paramValue := p_repLineID;
2379:
2380: wip_logger.entryPoint(p_procName => 'wip_infResSched_grp.scheduleNextResources',
2381: p_params => l_params,
2382: x_returnStatus => l_retStatus);
2383: end if;
2384:
2399: end if;
2400: end loop;
2401:
2402: if(l_logLevel <= wip_constants.trace_logging) then
2403: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.scheduleNextResources',
2404: p_procReturnStatus => x_returnStatus,
2405: p_msg => 'success',
2406: x_returnStatus => l_retStatus);
2407: end if;
2408: exception
2409: when fnd_api.g_exc_unexpected_error then
2410: x_returnStatus := fnd_api.g_ret_sts_unexp_error;
2411: if(l_logLevel <= wip_constants.trace_logging) then
2412: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.scheduleNextResources',
2413: p_procReturnStatus => x_returnStatus,
2414: p_msg => 'backward scheduling failed',
2415: x_returnStatus => l_retStatus);
2416: end if;
2420: p_procedure_name => 'scheduleNextResources',
2421: p_error_text => SQLERRM);
2422:
2423: if(l_logLevel <= wip_constants.trace_logging) then
2424: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.scheduleNextResources',
2425: p_procReturnStatus => x_returnStatus,
2426: p_msg => 'unexp error:' || SQLERRM,
2427: x_returnStatus => l_retStatus);
2428: end if;
2434: p_startDate IN DATE,
2435: x_resTbls IN OUT NOCOPY OP_RES_RECTBL_T,
2436: x_returnStatus OUT NOCOPY VARCHAR2) is
2437: l_logLevel NUMBER := fnd_log.g_current_runtime_level;
2438: l_params wip_logger.param_tbl_t;
2439: l_retStatus VARCHAR2(1);
2440: l_opSeqNum NUMBER := null;
2441: l_opStartDate DATE;
2442: l_count NUMBER;
2453: l_params(2).paramName := 'p_repLineID';
2454: l_params(2).paramValue := p_repLineID;
2455: l_params(3).paramName := 'p_startDate';
2456: l_params(3).paramValue := to_char(p_startDate, g_logDateFmt);
2457: wip_logger.entryPoint(p_procName => 'wip_infResSched_grp.resolvePriorExceptions',
2458: p_params => l_params,
2459: x_returnStatus => x_returnStatus);
2460: end if;
2461: --this loop finds an exception
2559: end if;
2560: end if;
2561:
2562: if(l_logLevel <= wip_constants.trace_logging) then
2563: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.resolvePriorExceptions',
2564: p_procReturnStatus => x_returnStatus,
2565: p_msg => 'success.',
2566: x_returnStatus => l_retStatus);
2567: end if;
2568: exception
2569: when fnd_api.g_exc_unexpected_error then
2570: x_returnStatus := fnd_api.g_ret_sts_unexp_error;
2571: if(l_logLevel <= wip_constants.trace_logging) then
2572: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.resolvePriorExceptions',
2573: p_procReturnStatus => x_returnStatus,
2574: p_msg => 'errmsg: ' || l_errMsg,
2575: x_returnStatus => l_retStatus);
2576: end if;
2580: p_procedure_name => 'resolvePriorExceptions',
2581: p_error_text => SQLERRM);
2582:
2583: if(l_logLevel <= wip_constants.trace_logging) then
2584: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.resolvePriorExceptions',
2585: p_procReturnStatus => x_returnStatus,
2586: p_msg => 'unexp error:' || SQLERRM,
2587: x_returnStatus => l_retStatus);
2588: end if;
2593: p_endDate IN DATE,
2594: x_resTbls IN OUT NOCOPY OP_RES_RECTBL_T,
2595: x_returnStatus OUT NOCOPY VARCHAR2) is
2596: l_logLevel NUMBER := fnd_log.g_current_runtime_level;
2597: l_params wip_logger.param_tbl_t;
2598: l_retStatus VARCHAR2(1);
2599: l_opSeqNum NUMBER := null;
2600: l_opEndDate DATE;
2601: l_count NUMBER;
2612: l_params(2).paramName := 'p_repLineID';
2613: l_params(2).paramValue := p_repLineID;
2614: l_params(3).paramName := 'p_endDate';
2615: l_params(3).paramValue := to_char(p_endDate, g_logDateFmt);
2616: wip_logger.entryPoint(p_procName => 'wip_infResSched_grp.resolveNextExceptions',
2617: p_params => l_params,
2618: x_returnStatus => x_returnStatus);
2619: end if;
2620: --this loop finds an exception
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;
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:
2644:
2645: --found a prior resource whose start date is earlier than job start...
2646: if(l_exceptionExists) then
2647: if(l_logLevel <= wip_constants.full_logging) then
2648: wip_logger.log('found exception', l_retStatus);
2649: end if;
2650: i := null;
2651: --going to reschedule entire job
2652: --delete usages
2723: end if;
2724: end if;
2725:
2726: if(l_logLevel <= wip_constants.trace_logging) then
2727: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.resolveNextExceptions',
2728: p_procReturnStatus => x_returnStatus,
2729: p_msg => 'success.',
2730: x_returnStatus => l_retStatus);
2731: end if;
2732: exception
2733: when fnd_api.g_exc_unexpected_error then
2734: x_returnStatus := fnd_api.g_ret_sts_unexp_error;
2735: if(l_logLevel <= wip_constants.trace_logging) then
2736: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.resolveNextExceptions',
2737: p_procReturnStatus => x_returnStatus,
2738: p_msg => l_errMsg,
2739: x_returnStatus => l_retStatus);
2740: end if;
2744: p_procedure_name => 'resolveNextExceptions',
2745: p_error_text => SQLERRM);
2746:
2747: if(l_logLevel <= wip_constants.trace_logging) then
2748: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.resolveNextExceptions',
2749: p_procReturnStatus => x_returnStatus,
2750: p_msg => 'unexp error:' || SQLERRM,
2751: x_returnStatus => l_retStatus);
2752: end if;
2756:
2757:
2758: procedure dumpResources(p_resTbls IN OP_RES_RECTBL_T) IS
2759: l_logLevel NUMBER := fnd_log.g_current_runtime_level;
2760: l_params wip_logger.param_tbl_t;
2761: l_retStatus VARCHAR2(1);
2762: l_resCode VARCHAR2(10);
2763: begin
2764:
2762: l_resCode VARCHAR2(10);
2763: begin
2764:
2765: if (l_logLevel <= wip_constants.trace_logging) then
2766: wip_logger.entryPoint(p_procName => 'wip_infResSched_grp.dumpResources',
2767: p_params => l_params,
2768: x_returnStatus => l_retStatus);
2769: end if;
2770:
2769: end if;
2770:
2771: if(p_resTbls.resID is null or p_resTbls.resID.count < 1 and
2772: l_logLevel <= wip_constants.trace_logging) then
2773: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.dumpResources',
2774: p_procReturnStatus => null,
2775: p_msg => 'no resources in table!',
2776: x_returnStatus => l_retStatus);
2777: return;
2782: select resource_code
2783: into l_resCode
2784: from bom_resources
2785: where resource_id = p_resTbls.resID(i);
2786: wip_logger.log('res:' || l_resCode || '(' || p_resTbls.resID(i) || ')', l_retStatus);
2787: wip_logger.log('+ usage (HRS):' || round(p_resTbls.totalDaysUsg(i)*24, 6),l_retStatus);
2788: wip_logger.log('+ operation:' || p_resTbls.opSeqNum(i), l_retStatus);
2789: wip_logger.log('+ department:' || p_resTbls.deptID(i), l_retStatus);
2790: wip_logger.log('+ sched seq:' || p_resTbls.schedSeqNum(i), l_retStatus);
2783: into l_resCode
2784: from bom_resources
2785: where resource_id = p_resTbls.resID(i);
2786: wip_logger.log('res:' || l_resCode || '(' || p_resTbls.resID(i) || ')', l_retStatus);
2787: wip_logger.log('+ usage (HRS):' || round(p_resTbls.totalDaysUsg(i)*24, 6),l_retStatus);
2788: wip_logger.log('+ operation:' || p_resTbls.opSeqNum(i), l_retStatus);
2789: wip_logger.log('+ department:' || p_resTbls.deptID(i), l_retStatus);
2790: wip_logger.log('+ sched seq:' || p_resTbls.schedSeqNum(i), l_retStatus);
2791: wip_logger.log('+ res seq:' || p_resTbls.resSeqNum(i), l_retStatus);
2784: from bom_resources
2785: where resource_id = p_resTbls.resID(i);
2786: wip_logger.log('res:' || l_resCode || '(' || p_resTbls.resID(i) || ')', l_retStatus);
2787: wip_logger.log('+ usage (HRS):' || round(p_resTbls.totalDaysUsg(i)*24, 6),l_retStatus);
2788: wip_logger.log('+ operation:' || p_resTbls.opSeqNum(i), l_retStatus);
2789: wip_logger.log('+ department:' || p_resTbls.deptID(i), l_retStatus);
2790: wip_logger.log('+ sched seq:' || p_resTbls.schedSeqNum(i), l_retStatus);
2791: wip_logger.log('+ res seq:' || p_resTbls.resSeqNum(i), l_retStatus);
2792: wip_logger.log('+ sched flag:' || p_resTbls.schedFlag(i), l_retStatus);
2785: where resource_id = p_resTbls.resID(i);
2786: wip_logger.log('res:' || l_resCode || '(' || p_resTbls.resID(i) || ')', l_retStatus);
2787: wip_logger.log('+ usage (HRS):' || round(p_resTbls.totalDaysUsg(i)*24, 6),l_retStatus);
2788: wip_logger.log('+ operation:' || p_resTbls.opSeqNum(i), l_retStatus);
2789: wip_logger.log('+ department:' || p_resTbls.deptID(i), l_retStatus);
2790: wip_logger.log('+ sched seq:' || p_resTbls.schedSeqNum(i), l_retStatus);
2791: wip_logger.log('+ res seq:' || p_resTbls.resSeqNum(i), l_retStatus);
2792: wip_logger.log('+ sched flag:' || p_resTbls.schedFlag(i), l_retStatus);
2793: wip_logger.log('+ 24hrs flag:' || p_resTbls.avail24Flag(i), l_retStatus);
2786: wip_logger.log('res:' || l_resCode || '(' || p_resTbls.resID(i) || ')', l_retStatus);
2787: wip_logger.log('+ usage (HRS):' || round(p_resTbls.totalDaysUsg(i)*24, 6),l_retStatus);
2788: wip_logger.log('+ operation:' || p_resTbls.opSeqNum(i), l_retStatus);
2789: wip_logger.log('+ department:' || p_resTbls.deptID(i), l_retStatus);
2790: wip_logger.log('+ sched seq:' || p_resTbls.schedSeqNum(i), l_retStatus);
2791: wip_logger.log('+ res seq:' || p_resTbls.resSeqNum(i), l_retStatus);
2792: wip_logger.log('+ sched flag:' || p_resTbls.schedFlag(i), l_retStatus);
2793: wip_logger.log('+ 24hrs flag:' || p_resTbls.avail24Flag(i), l_retStatus);
2794: wip_logger.log('+ start date:' || to_char(p_resTbls.startDate(i), g_logDateFmt), l_retStatus);
2787: wip_logger.log('+ usage (HRS):' || round(p_resTbls.totalDaysUsg(i)*24, 6),l_retStatus);
2788: wip_logger.log('+ operation:' || p_resTbls.opSeqNum(i), l_retStatus);
2789: wip_logger.log('+ department:' || p_resTbls.deptID(i), l_retStatus);
2790: wip_logger.log('+ sched seq:' || p_resTbls.schedSeqNum(i), l_retStatus);
2791: wip_logger.log('+ res seq:' || p_resTbls.resSeqNum(i), l_retStatus);
2792: wip_logger.log('+ sched flag:' || p_resTbls.schedFlag(i), l_retStatus);
2793: wip_logger.log('+ 24hrs flag:' || p_resTbls.avail24Flag(i), l_retStatus);
2794: wip_logger.log('+ start date:' || to_char(p_resTbls.startDate(i), g_logDateFmt), l_retStatus);
2795: wip_logger.log('+ end date:' || to_char(p_resTbls.endDate(i), g_logDateFmt), l_retStatus);
2788: wip_logger.log('+ operation:' || p_resTbls.opSeqNum(i), l_retStatus);
2789: wip_logger.log('+ department:' || p_resTbls.deptID(i), l_retStatus);
2790: wip_logger.log('+ sched seq:' || p_resTbls.schedSeqNum(i), l_retStatus);
2791: wip_logger.log('+ res seq:' || p_resTbls.resSeqNum(i), l_retStatus);
2792: wip_logger.log('+ sched flag:' || p_resTbls.schedFlag(i), l_retStatus);
2793: wip_logger.log('+ 24hrs flag:' || p_resTbls.avail24Flag(i), l_retStatus);
2794: wip_logger.log('+ start date:' || to_char(p_resTbls.startDate(i), g_logDateFmt), l_retStatus);
2795: wip_logger.log('+ end date:' || to_char(p_resTbls.endDate(i), g_logDateFmt), l_retStatus);
2796: wip_logger.log('+ usg st idx:' || p_resTbls.usgStartIdx(i), l_retStatus);
2789: wip_logger.log('+ department:' || p_resTbls.deptID(i), l_retStatus);
2790: wip_logger.log('+ sched seq:' || p_resTbls.schedSeqNum(i), l_retStatus);
2791: wip_logger.log('+ res seq:' || p_resTbls.resSeqNum(i), l_retStatus);
2792: wip_logger.log('+ sched flag:' || p_resTbls.schedFlag(i), l_retStatus);
2793: wip_logger.log('+ 24hrs flag:' || p_resTbls.avail24Flag(i), l_retStatus);
2794: wip_logger.log('+ start date:' || to_char(p_resTbls.startDate(i), g_logDateFmt), l_retStatus);
2795: wip_logger.log('+ end date:' || to_char(p_resTbls.endDate(i), g_logDateFmt), l_retStatus);
2796: wip_logger.log('+ usg st idx:' || p_resTbls.usgStartIdx(i), l_retStatus);
2797: wip_logger.log('+ usg end idx:' || p_resTbls.usgEndIdx(i), l_retStatus);
2790: wip_logger.log('+ sched seq:' || p_resTbls.schedSeqNum(i), l_retStatus);
2791: wip_logger.log('+ res seq:' || p_resTbls.resSeqNum(i), l_retStatus);
2792: wip_logger.log('+ sched flag:' || p_resTbls.schedFlag(i), l_retStatus);
2793: wip_logger.log('+ 24hrs flag:' || p_resTbls.avail24Flag(i), l_retStatus);
2794: wip_logger.log('+ start date:' || to_char(p_resTbls.startDate(i), g_logDateFmt), l_retStatus);
2795: wip_logger.log('+ end date:' || to_char(p_resTbls.endDate(i), g_logDateFmt), l_retStatus);
2796: wip_logger.log('+ usg st idx:' || p_resTbls.usgStartIdx(i), l_retStatus);
2797: wip_logger.log('+ usg end idx:' || p_resTbls.usgEndIdx(i), l_retStatus);
2798: if(p_resTbls.usgStartIdx(i) is not null) then
2791: wip_logger.log('+ res seq:' || p_resTbls.resSeqNum(i), l_retStatus);
2792: wip_logger.log('+ sched flag:' || p_resTbls.schedFlag(i), l_retStatus);
2793: wip_logger.log('+ 24hrs flag:' || p_resTbls.avail24Flag(i), l_retStatus);
2794: wip_logger.log('+ start date:' || to_char(p_resTbls.startDate(i), g_logDateFmt), l_retStatus);
2795: wip_logger.log('+ end date:' || to_char(p_resTbls.endDate(i), g_logDateFmt), l_retStatus);
2796: wip_logger.log('+ usg st idx:' || p_resTbls.usgStartIdx(i), l_retStatus);
2797: wip_logger.log('+ usg end idx:' || p_resTbls.usgEndIdx(i), l_retStatus);
2798: if(p_resTbls.usgStartIdx(i) is not null) then
2799: for j in p_resTbls.usgStartIdx(i)..p_resTbls.usgEndIdx(i) loop
2792: wip_logger.log('+ sched flag:' || p_resTbls.schedFlag(i), l_retStatus);
2793: wip_logger.log('+ 24hrs flag:' || p_resTbls.avail24Flag(i), l_retStatus);
2794: wip_logger.log('+ start date:' || to_char(p_resTbls.startDate(i), g_logDateFmt), l_retStatus);
2795: wip_logger.log('+ end date:' || to_char(p_resTbls.endDate(i), g_logDateFmt), l_retStatus);
2796: wip_logger.log('+ usg st idx:' || p_resTbls.usgStartIdx(i), l_retStatus);
2797: wip_logger.log('+ usg end idx:' || p_resTbls.usgEndIdx(i), l_retStatus);
2798: if(p_resTbls.usgStartIdx(i) is not null) then
2799: for j in p_resTbls.usgStartIdx(i)..p_resTbls.usgEndIdx(i) loop
2800: wip_logger.log(' + usage start date:' || to_char(p_resTbls.usgStartDate(j), g_logDateFmt),l_retStatus);
2793: wip_logger.log('+ 24hrs flag:' || p_resTbls.avail24Flag(i), l_retStatus);
2794: wip_logger.log('+ start date:' || to_char(p_resTbls.startDate(i), g_logDateFmt), l_retStatus);
2795: wip_logger.log('+ end date:' || to_char(p_resTbls.endDate(i), g_logDateFmt), l_retStatus);
2796: wip_logger.log('+ usg st idx:' || p_resTbls.usgStartIdx(i), l_retStatus);
2797: wip_logger.log('+ usg end idx:' || p_resTbls.usgEndIdx(i), l_retStatus);
2798: if(p_resTbls.usgStartIdx(i) is not null) then
2799: for j in p_resTbls.usgStartIdx(i)..p_resTbls.usgEndIdx(i) loop
2800: wip_logger.log(' + usage start date:' || to_char(p_resTbls.usgStartDate(j), g_logDateFmt),l_retStatus);
2801: wip_logger.log(' + usage end date:' || to_char(p_resTbls.usgEndDate(j), g_logDateFmt),l_retStatus);
2796: wip_logger.log('+ usg st idx:' || p_resTbls.usgStartIdx(i), l_retStatus);
2797: wip_logger.log('+ usg end idx:' || p_resTbls.usgEndIdx(i), l_retStatus);
2798: if(p_resTbls.usgStartIdx(i) is not null) then
2799: for j in p_resTbls.usgStartIdx(i)..p_resTbls.usgEndIdx(i) loop
2800: wip_logger.log(' + usage start date:' || to_char(p_resTbls.usgStartDate(j), g_logDateFmt),l_retStatus);
2801: wip_logger.log(' + usage end date:' || to_char(p_resTbls.usgEndDate(j), g_logDateFmt),l_retStatus);
2802: wip_logger.log(' + cumulative usage:' || p_resTbls.usgCumMinProcTime(j),l_retStatus);
2803: end loop;
2804: end if;
2797: wip_logger.log('+ usg end idx:' || p_resTbls.usgEndIdx(i), l_retStatus);
2798: if(p_resTbls.usgStartIdx(i) is not null) then
2799: for j in p_resTbls.usgStartIdx(i)..p_resTbls.usgEndIdx(i) loop
2800: wip_logger.log(' + usage start date:' || to_char(p_resTbls.usgStartDate(j), g_logDateFmt),l_retStatus);
2801: wip_logger.log(' + usage end date:' || to_char(p_resTbls.usgEndDate(j), g_logDateFmt),l_retStatus);
2802: wip_logger.log(' + cumulative usage:' || p_resTbls.usgCumMinProcTime(j),l_retStatus);
2803: end loop;
2804: end if;
2805: end loop;
2798: if(p_resTbls.usgStartIdx(i) is not null) then
2799: for j in p_resTbls.usgStartIdx(i)..p_resTbls.usgEndIdx(i) loop
2800: wip_logger.log(' + usage start date:' || to_char(p_resTbls.usgStartDate(j), g_logDateFmt),l_retStatus);
2801: wip_logger.log(' + usage end date:' || to_char(p_resTbls.usgEndDate(j), g_logDateFmt),l_retStatus);
2802: wip_logger.log(' + cumulative usage:' || p_resTbls.usgCumMinProcTime(j),l_retStatus);
2803: end loop;
2804: end if;
2805: end loop;
2806: end if;
2804: end if;
2805: end loop;
2806: end if;
2807: if (l_logLevel <= wip_constants.trace_logging) then
2808: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.dumpResources',
2809: p_procReturnStatus => null,
2810: p_msg => 'success',
2811: x_returnStatus => l_retStatus);
2812: end if;
2813:
2814: exception
2815: when others then
2816: if (l_logLevel <= wip_constants.trace_logging) then
2817: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.dumpResources',
2818: p_procReturnStatus => null,
2819: p_msg => 'exception:' || SQLERRM,
2820: x_returnStatus => l_retStatus);
2821: end if;
2830: i NUMBER;
2831: j NUMBER;
2832: l_logLevel NUMBER := fnd_log.g_current_runtime_level;
2833: l_retStatus VARCHAR2(1);
2834: l_params wip_logger.param_tbl_t;
2835: begin
2836: if(l_logLevel <= wip_constants.trace_logging) then
2837: l_params(1).paramName := 'p_anchorDate';
2838: l_params(1).paramValue := to_char(p_anchorDate, g_logDateFmt);
2835: begin
2836: if(l_logLevel <= wip_constants.trace_logging) then
2837: l_params(1).paramName := 'p_anchorDate';
2838: l_params(1).paramValue := to_char(p_anchorDate, g_logDateFmt);
2839: wip_logger.entryPoint(p_procName => 'wip_infResSched_grp.scheduleNoResources',
2840: p_params => l_params,
2841: x_returnStatus => l_retStatus);
2842: end if;
2843: x_returnStatus := fnd_api.g_ret_sts_success;
2844:
2845: while(getNextResIdx(l_range, wip_constants.sched_no, g_forward, x_resTbls, i)) loop
2846:
2847: if(l_logLevel <= wip_constants.full_logging) then
2848: wip_logger.log('found scheduled no resource at ' || i, l_retStatus);
2849: end if;
2850:
2851: l_prevRange(2) := i;
2852: l_nextRange(1) := i;
2854: --find previous scheduled yes resource
2855: if(getNextResIdx(l_prevRange, wip_constants.sched_yes, g_backward, x_resTbls, j)) then
2856:
2857: if(l_logLevel <= wip_constants.full_logging) then
2858: wip_logger.log('found previous scheduled yes resource at ' || j, l_retStatus);
2859: end if;
2860: x_resTbls.startDate(i) := x_resTbls.endDate(j);
2861:
2862: --couldn't find a scheduled yes resource
2862: --couldn't find a scheduled yes resource
2863: /* Bug 6954186: Find the next scheduled resource in forward direction*/
2864: elsif(getNextResIdx(l_nextRange, wip_constants.sched_yes, g_forward, x_resTbls, j)) then
2865: if(l_logLevel <= wip_constants.full_logging) then
2866: wip_logger.log('found later scheduled yes resource at ' || j, l_retStatus);
2867: end if;
2868:
2869: x_resTbls.startDate(i) := x_resTbls.startDate(j);
2870:
2870:
2871: else --no scheduled yes resources
2872: x_resTbls.startDate(i) := p_anchorDate;
2873: if(l_logLevel <= wip_constants.full_logging) then
2874: wip_logger.log('no scheduled yes resources found', l_retStatus);
2875: end if;
2876: end if;
2877:
2878: x_resTbls.endDate(i) := x_resTbls.startDate(i);
2877:
2878: x_resTbls.endDate(i) := x_resTbls.startDate(i);
2879: end loop;
2880: if(l_logLevel <= wip_constants.trace_logging) then
2881: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.scheduleNoResources',
2882: p_procReturnStatus => x_returnStatus,
2883: p_msg => 'success',
2884: x_returnStatus => l_retStatus);
2885: end if;
2886: exception
2887: when others then
2888: x_returnStatus := fnd_api.g_ret_sts_unexp_error;
2889: if(l_logLevel <= wip_constants.trace_logging) then
2890: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.scheduleNoResources',
2891: p_procReturnStatus => x_returnStatus,
2892: p_msg => 'error: ' || SQLERRM,
2893: x_returnStatus => l_retStatus);
2894: end if;