[Home] [Help]
126: l_otherExists boolean := x_resTbls.schedFlag(1) in (wip_constants.sched_yes, wip_constants.sched_next);
127: l_startIdx NUMBER := 1;
128: l_logLevel NUMBER := fnd_log.g_current_runtime_level;
129: l_retStatus VARCHAR2(1);
130: l_params wip_logger.param_tbl_t;
131: begin
132: if(l_logLevel <= wip_constants.trace_logging) then
133: wip_logger.entryPoint(p_procName => 'wip_infResSched_grp.removePriorsFromBatch',
134: p_params => l_params,
129: l_retStatus VARCHAR2(1);
130: l_params wip_logger.param_tbl_t;
131: begin
132: if(l_logLevel <= wip_constants.trace_logging) then
133: wip_logger.entryPoint(p_procName => 'wip_infResSched_grp.removePriorsFromBatch',
134: p_params => l_params,
135: x_returnStatus => l_retStatus);
136: end if;
137: while(i <= x_resTbls.resID.count) loop
154: end if;
155: i := i + 1;
156: end loop;
157: if (l_logLevel <= wip_constants.trace_logging) then
158: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.removePriorsFromBatch',
159: p_procReturnStatus => null,
160: p_msg => 'success',
161: x_returnStatus => l_retStatus);
162: end if;
166: l_curOp NUMBER:= x_resTbls.opSeqNum(1);
167: l_nonPriorExists boolean := false;
168: l_logLevel NUMBER := fnd_log.g_current_runtime_level;
169: l_retStatus VARCHAR2(1);
170: l_params wip_logger.param_tbl_t;
171: begin
172: if(l_logLevel <= wip_constants.trace_logging) then
173: wip_logger.entryPoint(p_procName => 'wip_infResSched_grp.removePriors',
174: p_params => l_params,
169: l_retStatus VARCHAR2(1);
170: l_params wip_logger.param_tbl_t;
171: begin
172: if(l_logLevel <= wip_constants.trace_logging) then
173: wip_logger.entryPoint(p_procName => 'wip_infResSched_grp.removePriors',
174: p_params => l_params,
175: x_returnStatus => l_retStatus);
176: end if;
177: for i in 1..x_resTbls.resID.count loop
185: l_nonPriorExists := x_resTbls.schedFlag(i) not in (wip_constants.sched_prior, wip_constants.sched_no);
186: end if;
187: end loop;
188: if (l_logLevel <= wip_constants.trace_logging) then
189: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.removePriors',
190: p_procReturnStatus => null,
191: p_msg => 'success',
192: x_returnStatus => l_retStatus);
193: end if;
197: l_curOp NUMBER:= x_resTbls.opSeqNum(x_resTbls.resID.count);
198: l_nonNextExists boolean := false;
199: l_logLevel NUMBER := fnd_log.g_current_runtime_level;
200: l_retStatus VARCHAR2(1);
201: l_params wip_logger.param_tbl_t;
202: begin
203: if(l_logLevel <= wip_constants.trace_logging) then
204: wip_logger.entryPoint(p_procName => 'wip_infResSched_grp.removeNexts',
205: p_params => l_params,
200: l_retStatus VARCHAR2(1);
201: l_params wip_logger.param_tbl_t;
202: begin
203: if(l_logLevel <= wip_constants.trace_logging) then
204: wip_logger.entryPoint(p_procName => 'wip_infResSched_grp.removeNexts',
205: p_params => l_params,
206: x_returnStatus => l_retStatus);
207: end if;
208: for i in reverse 1..x_resTbls.resID.count loop
217: l_nonNextExists := x_resTbls.schedFlag(i) not in (wip_constants.sched_no, wip_constants.sched_next);
218: end if;
219: end loop;
220: if (l_logLevel <= wip_constants.trace_logging) then
221: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.removeNexts',
222: p_procReturnStatus => null,
223: p_msg => 'success',
224: x_returnStatus => l_retStatus);
225: end if;
228: procedure verifyResources(x_resTbls IN OUT NOCOPY OP_RES_RECTBL_T) is
229:
230: l_logLevel NUMBER := fnd_log.g_current_runtime_level;
231: l_retStatus VARCHAR2(1);
232: l_params wip_logger.param_tbl_t;
233: begin
234: if(l_logLevel <= wip_constants.trace_logging) then
235: wip_logger.entryPoint(p_procName => 'wip_infResSched_grp.verifyResources',
236: p_params => l_params,
231: l_retStatus VARCHAR2(1);
232: l_params wip_logger.param_tbl_t;
233: begin
234: if(l_logLevel <= wip_constants.trace_logging) then
235: wip_logger.entryPoint(p_procName => 'wip_infResSched_grp.verifyResources',
236: p_params => l_params,
237: x_returnStatus => l_retStatus);
238: end if;
239:
257: --changes schedule method of prior resources to yes if
258: -- + they are in a simultaneous batch with yes or next resources.
259: removePriorsFromBatch(x_resTbls => x_resTbls);
260: if (l_logLevel <= wip_constants.trace_logging) then
261: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.verifyResources',
262: p_procReturnStatus => null,
263: p_msg => 'success',
264: x_returnStatus => l_retStatus);
265: end if;
267:
268: procedure dumpOps(p_opTbl in op_tbl_t) is
269: l_logLevel NUMBER := fnd_log.g_current_runtime_level;
270: l_retStatus VARCHAR2(1);
271: l_params wip_logger.param_tbl_t;
272: begin
273: if(l_logLevel <= wip_constants.trace_logging) then
274: wip_logger.entryPoint(p_procName => 'wip_infResSched_grp.dumpOps',
275: p_params => l_params,
270: l_retStatus VARCHAR2(1);
271: l_params wip_logger.param_tbl_t;
272: begin
273: if(l_logLevel <= wip_constants.trace_logging) then
274: wip_logger.entryPoint(p_procName => 'wip_infResSched_grp.dumpOps',
275: p_params => l_params,
276: x_returnStatus => l_retStatus);
277: end if;
278: if(l_logLevel <= wip_constants.full_logging) then
276: x_returnStatus => l_retStatus);
277: end if;
278: if(l_logLevel <= wip_constants.full_logging) then
279: for i in 1..p_opTbl.count loop
280: wip_logger.log('op:' || i, l_retStatus);
281: wip_logger.log('startDate:' || to_char(p_opTbl(i).startDate, g_logDateFmt), l_retStatus);
282: wip_logger.log('endDate:' || to_char(p_opTbl(i).endDate, g_logDateFmt), l_retStatus);
283: if(p_opTbl(i).priorsExist) then
284: wip_logger.log('priorsExist:true', l_retStatus);
277: end if;
278: if(l_logLevel <= wip_constants.full_logging) then
279: for i in 1..p_opTbl.count loop
280: wip_logger.log('op:' || i, l_retStatus);
281: wip_logger.log('startDate:' || to_char(p_opTbl(i).startDate, g_logDateFmt), l_retStatus);
282: wip_logger.log('endDate:' || to_char(p_opTbl(i).endDate, g_logDateFmt), l_retStatus);
283: if(p_opTbl(i).priorsExist) then
284: wip_logger.log('priorsExist:true', l_retStatus);
285: else
278: if(l_logLevel <= wip_constants.full_logging) then
279: for i in 1..p_opTbl.count loop
280: wip_logger.log('op:' || i, l_retStatus);
281: wip_logger.log('startDate:' || to_char(p_opTbl(i).startDate, g_logDateFmt), l_retStatus);
282: wip_logger.log('endDate:' || to_char(p_opTbl(i).endDate, g_logDateFmt), l_retStatus);
283: if(p_opTbl(i).priorsExist) then
284: wip_logger.log('priorsExist:true', l_retStatus);
285: else
286: wip_logger.log('priorsExist:false', l_retStatus);
280: wip_logger.log('op:' || i, l_retStatus);
281: wip_logger.log('startDate:' || to_char(p_opTbl(i).startDate, g_logDateFmt), l_retStatus);
282: wip_logger.log('endDate:' || to_char(p_opTbl(i).endDate, g_logDateFmt), l_retStatus);
283: if(p_opTbl(i).priorsExist) then
284: wip_logger.log('priorsExist:true', l_retStatus);
285: else
286: wip_logger.log('priorsExist:false', l_retStatus);
287: end if;
288: if(p_opTbl(i).nextsExist) then
282: wip_logger.log('endDate:' || to_char(p_opTbl(i).endDate, g_logDateFmt), l_retStatus);
283: if(p_opTbl(i).priorsExist) then
284: wip_logger.log('priorsExist:true', l_retStatus);
285: else
286: wip_logger.log('priorsExist:false', l_retStatus);
287: end if;
288: if(p_opTbl(i).nextsExist) then
289: wip_logger.log('nextsExist:true', l_retStatus);
290: else
285: else
286: wip_logger.log('priorsExist:false', l_retStatus);
287: end if;
288: if(p_opTbl(i).nextsExist) then
289: wip_logger.log('nextsExist:true', l_retStatus);
290: else
291: wip_logger.log('nextsExist:false', l_retStatus);
292: end if;
293: wip_logger.log('resRange:' || p_opTbl(i).resStartIdx || '-' || p_opTbl(i).resEndIdx, l_retStatus);
287: end if;
288: if(p_opTbl(i).nextsExist) then
289: wip_logger.log('nextsExist:true', l_retStatus);
290: else
291: wip_logger.log('nextsExist:false', l_retStatus);
292: end if;
293: wip_logger.log('resRange:' || p_opTbl(i).resStartIdx || '-' || p_opTbl(i).resEndIdx, l_retStatus);
294: end loop;
295: end if;
289: wip_logger.log('nextsExist:true', l_retStatus);
290: else
291: wip_logger.log('nextsExist:false', l_retStatus);
292: end if;
293: wip_logger.log('resRange:' || p_opTbl(i).resStartIdx || '-' || p_opTbl(i).resEndIdx, l_retStatus);
294: end loop;
295: end if;
296: if (l_logLevel <= wip_constants.trace_logging) then
297: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.dumpOps',
293: wip_logger.log('resRange:' || p_opTbl(i).resStartIdx || '-' || p_opTbl(i).resEndIdx, l_retStatus);
294: end loop;
295: end if;
296: if (l_logLevel <= wip_constants.trace_logging) then
297: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.dumpOps',
298: p_procReturnStatus => null,
299: p_msg => 'success',
300: x_returnStatus => l_retStatus);
301: end if;
310: j number := 1;
311: l_firstYesOpIdx NUMBER;
312: l_logLevel NUMBER := fnd_log.g_current_runtime_level;
313: l_retStatus VARCHAR2(1);
314: l_params wip_logger.param_tbl_t;
315: begin
316: if(l_logLevel <= wip_constants.trace_logging) then
317: l_params(1).paramName := 'p_anchorDate';
318: l_params(1).paramValue := to_char(p_anchorDate, g_logDateFmt);
315: begin
316: if(l_logLevel <= wip_constants.trace_logging) then
317: l_params(1).paramName := 'p_anchorDate';
318: l_params(1).paramValue := to_char(p_anchorDate, g_logDateFmt);
319: wip_logger.entryPoint(p_procName => 'wip_infResSched_grp.buildOpStructure',
320: p_params => l_params,
321: x_returnStatus => l_retStatus);
322: end if;
323:
328: for i in 1..p_resTbls.resID.count loop
329: if(l_opSeqNum <> p_resTbls.opSeqNum(i)) then
330:
331: if(l_logLevel <= wip_constants.full_logging) then
332: wip_logger.log('new op at resource ' || i, l_retStatus);
333: end if;
334:
335: x_opTbl(j).resEndIdx := i - 1;
336: j := j + 1;
351: end if;
352: x_opTbl(j).startDate := least(p_resTbls.startDate(i), nvl(x_opTbl(j).startDate, p_resTbls.startDate(i)));
353: x_opTbl(j).endDate := greatest(p_resTbls.endDate(i), nvl(x_opTbl(j).endDate, p_resTbls.endDate(i)));
354: if(l_logLevel <= wip_constants.full_logging) then
355: wip_logger.log('op ' || j || '''s start date:' || to_char(x_opTbl(j).startDate, g_logDateFmt), l_retStatus);
356: wip_logger.log('op ' || j || '''s end date:' || to_char(x_opTbl(j).endDate, g_logDateFmt), l_retStatus);
357: end if;
358:
359: end if;
352: x_opTbl(j).startDate := least(p_resTbls.startDate(i), nvl(x_opTbl(j).startDate, p_resTbls.startDate(i)));
353: x_opTbl(j).endDate := greatest(p_resTbls.endDate(i), nvl(x_opTbl(j).endDate, p_resTbls.endDate(i)));
354: if(l_logLevel <= wip_constants.full_logging) then
355: wip_logger.log('op ' || j || '''s start date:' || to_char(x_opTbl(j).startDate, g_logDateFmt), l_retStatus);
356: wip_logger.log('op ' || j || '''s end date:' || to_char(x_opTbl(j).endDate, g_logDateFmt), l_retStatus);
357: end if;
358:
359: end if;
360: end loop;
389: dumpOps(x_opTbl);
390: end if;
391:
392: if (l_logLevel <= wip_constants.trace_logging) then
393: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.buildOpStructure',
394: p_procReturnStatus => null,
395: p_msg => 'success',
396: x_returnStatus => l_retStatus);
397: end if;
403: p_isMdPntFwd boolean,
404: x_midPntFwdIdx OUT NOCOPY NUMBER,
405: x_midPntBkwdIdx OUT NOCOPY NUMBER) is
406: l_retStatus VARCHAR2(1);
407: l_params wip_logger.param_tbl_t;
408: l_logLevel NUMBER := fnd_log.g_current_runtime_level;
409: l_foundMidPntOp boolean := false;
410: begin
411: if(l_logLevel <= wip_constants.trace_logging) then
416: l_params(3).paramName := 'p_isMdPntFwd';
417: if(p_isMdPntFwd) then l_params(3).paramValue := 'true';
418: else l_params(3).paramValue := 'false'; end if;
419:
420: wip_logger.entryPoint(p_procName => 'wip_infResSched_grp.findMdPntRes',
421: p_params => l_params,
422: x_returnStatus => l_retStatus);
423: end if;
424:
426: for i in 1..p_resTbls.resID.count loop
427: if(p_opSeqNum = p_resTbls.opSeqNum(i)) then --op matches
428: l_foundMidPntOp := true;
429: if(l_logLevel <= wip_constants.full_logging) then
430: wip_logger.log('op seq matches res ' || i, l_retStatus);
431: end if;
432:
433: if(p_resSeqNum is not null) then
434: if(p_resSeqNum = p_resTbls.resSeqNum(i)) then --res seq matches
433: if(p_resSeqNum is not null) then
434: if(p_resSeqNum = p_resTbls.resSeqNum(i)) then --res seq matches
435:
436: if(l_logLevel <= wip_constants.full_logging) then
437: wip_logger.log('res seq matches res ' || i, l_retStatus);
438: end if;
439:
440: if(p_isMdPntFwd) then
441: -- bug 3423612: If there are simultaneous resources, we have to
473: end if;
474: else --resource seq was not populated. use op start or end res
475:
476: if(l_logLevel <= wip_constants.full_logging) then
477: wip_logger.log('res seq is null', l_retStatus);
478: end if;
479:
480: if(p_isMdPntFwd) then --forward scheduling midpoint op
481: x_midPntFwdIdx := i;
491: if(l_foundMidPntOp and
492: not(p_isMdPntFwd) and
493: p_resTbls.opSeqNum(i) <> p_opSeqNum) then
494: if(l_logLevel <= wip_constants.full_logging) then
495: wip_logger.log('first res past midpoint at idx:' || i, l_retStatus);
496: end if;
497: x_midPntBkwdIdx := i - 1;
498: x_midPntFwdIdx := i;
499: exit;
500: end if;
501:
502: if(p_resTbls.resID.count = i) then
503: if(l_logLevel <= wip_constants.full_logging) then
504: wip_logger.log('backward scheduling everything', l_retStatus);
505: end if;
506:
507: x_midPntBkwdIdx := i;
508: exit;
508: exit;
509: end if;
510: end loop;
511: if(l_logLevel <= wip_constants.trace_logging) then
512: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.findMdPntRes',
513: p_procReturnStatus => null,
514: p_msg => 'fwdIdx:' || x_midPntFwdIdx || '; bkwdIdx:' || x_midPntBkwdIdx,
515: x_returnStatus => l_retStatus);
516: end if;
525: p_endDebug IN VARCHAR2 := null,
526: x_resTbls IN OUT NOCOPY OP_RES_RECTBL_T,
527: x_returnStatus OUT NOCOPY VARCHAR2) is
528: l_logLevel NUMBER := fnd_log.g_current_runtime_level;
529: l_params wip_logger.param_tbl_t;
530: l_retStatus VARCHAR2(1);
531:
532: l_fwdStIdx NUMBER;--resource to start forward scheduling from
533: l_bkwdEndIdx NUMBER;--resource to backward schedule to
547: l_params(4).paramName := 'p_endDate';
548: l_params(4).paramValue := to_char(p_endDate, g_logDateFmt);
549: l_params(5).paramName := 'p_opSeqNum';
550: l_params(5).paramValue := p_opSeqNum;
551: wip_logger.entryPoint(p_procName => 'wip_infResSched_grp.schedule',
552: p_params => l_params,
553: x_returnStatus => x_returnStatus);
554: if(x_returnStatus <> fnd_api.g_ret_sts_success) then
555: raise fnd_api.g_exc_unexpected_error;
558: x_returnStatus := fnd_api.g_ret_sts_success;
559:
560: if(x_resTbls.resID is null or x_resTbls.resID.count < 1) then
561: if (l_logLevel <= wip_constants.trace_logging) then
562: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.schedule',
563: p_procReturnStatus => x_returnStatus,
564: p_msg => 'no resources to schedule!',
565: x_returnStatus => l_retStatus);
566: end if;
603: l_startDate := p_startDate;
604: l_endDate := null;--this line isn''t necessary, but is included for clarity
605:
606: if (l_logLevel <= wip_constants.trace_logging) then
607: wip_logger.log(p_msg => 'forward scheduling',
608: x_returnStatus => x_returnStatus);
609: end if;
610:
611: --backward
615: l_startDate := null;--this line isn''t necessary, but is included for clarity
616: l_endDate := p_endDate;
617:
618: if (l_logLevel <= wip_constants.trace_logging) then
619: wip_logger.log(p_msg => 'backward scheduling',
620: x_returnStatus => x_returnStatus);
621: end if;
622: end if;
623: else --midpoint scheduling
628: x_midPntFwdIdx => l_fwdStIdx,
629: x_midPntBkwdIdx => l_bkwdEndIdx);
630:
631: if (l_logLevel <= wip_constants.trace_logging) then
632: wip_logger.log(p_msg => 'midpoint scheduling',
633: x_returnStatus => x_returnStatus);
634: end if;
635:
636: if(p_startDate is not null) then
660: x_returnStatus => x_returnStatus);
661:
662: if(x_returnStatus <> fnd_api.g_ret_sts_success) then
663: if(l_logLevel <= wip_constants.full_logging) then
664: wip_logger.log('fwdSch failed', l_retStatus);
665: end if;
666: raise fnd_api.g_exc_unexpected_error;
667: end if;
668:
684: x_resTbls => x_resTbls,
685: x_returnStatus => x_returnStatus);
686: if(x_returnStatus <> fnd_api.g_ret_sts_success) then
687: if(l_logLevel <= wip_constants.full_logging) then
688: wip_logger.log('bkwdSch failed', l_retStatus);
689: end if;
690: raise fnd_api.g_exc_unexpected_error;
691: end if;
692: end if;
704: x_returnStatus => x_returnStatus);
705:
706: if(x_returnStatus <> fnd_api.g_ret_sts_success) then
707: if(l_logLevel <= wip_constants.full_logging) then
708: wip_logger.log('schPriorRes failed', l_retStatus);
709: end if;
710: raise fnd_api.g_exc_unexpected_error;
711: end if;
712: -- Bug # 13388579 logic to identify back ward scheduling .
722:
723:
724: if(x_returnStatus <> fnd_api.g_ret_sts_success) then
725: if(l_logLevel <= wip_constants.full_logging) then
726: wip_logger.log('schNextRes failed', l_retStatus);
727: end if;
728: raise fnd_api.g_exc_unexpected_error;
729: end if;
730:
740: x_resTbls => x_resTbls,
741: x_returnStatus => x_returnStatus);
742: if(x_returnStatus <> fnd_api.g_ret_sts_success) then
743: if(l_logLevel <= wip_constants.full_logging) then
744: wip_logger.log('resolvePriorRes failed', l_retStatus);
745: end if;
746: raise fnd_api.g_exc_unexpected_error;
747: end if;
748: --if backward scheduling
755: -- Bug # 13388579 resetting the flag.
756: is_backWardSch := false;
757: if(x_returnStatus <> fnd_api.g_ret_sts_success) then
758: if(l_logLevel <= wip_constants.full_logging) then
759: wip_logger.log('resolveNextRes failed', l_retStatus);
760: end if;
761: raise fnd_api.g_exc_unexpected_error;
762: end if;
763: end if;
770:
771:
772:
773: if(l_logLevel <= wip_constants.trace_logging) then
774: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.schedule',
775: p_procReturnStatus => x_returnStatus,
776: p_msg => 'success',
777: x_returnStatus => l_retStatus);
778: if(fnd_api.to_boolean(nvl(p_endDebug, fnd_api.g_true))) then
775: p_procReturnStatus => x_returnStatus,
776: p_msg => 'success',
777: x_returnStatus => l_retStatus);
778: if(fnd_api.to_boolean(nvl(p_endDebug, fnd_api.g_true))) then
779: wip_logger.cleanup(l_retStatus);
780: end if;
781: end if;
782: exception
783: when fnd_api.g_exc_unexpected_error then
785: if(l_logLevel <= wip_constants.trace_logging) then
786: wip_utilities.get_message_stack(p_msg => l_errMsg,
787: p_delete_stack => fnd_api.g_false);
788:
789: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.schedule',
790: p_procReturnStatus => x_returnStatus,
791: p_msg => 'failure: ' || l_errMsg,
792: x_returnStatus => l_retStatus);
793: if(fnd_api.to_boolean(nvl(p_endDebug, fnd_api.g_true))) then
790: p_procReturnStatus => x_returnStatus,
791: p_msg => 'failure: ' || l_errMsg,
792: x_returnStatus => l_retStatus);
793: if(fnd_api.to_boolean(nvl(p_endDebug, fnd_api.g_true))) then
794: wip_logger.cleanup(l_retStatus);
795: end if;
796: end if;
797: when others then
798: x_returnStatus := fnd_api.g_ret_sts_unexp_error;
799: fnd_msg_pub.add_exc_msg(p_pkg_name => 'wip_infResSched_grp',
800: p_procedure_name => 'schedule',
801: p_error_text => SQLERRM);
802: if(l_logLevel <= wip_constants.trace_logging) then
803: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.schedule',
804: p_procReturnStatus => x_returnStatus,
805: p_msg => 'unexpected error: ' || SQLERRM,
806: x_returnStatus => l_retStatus);
807: if(fnd_api.to_boolean(nvl(p_endDebug, fnd_api.g_true))) then
804: p_procReturnStatus => x_returnStatus,
805: p_msg => 'unexpected error: ' || SQLERRM,
806: x_returnStatus => l_retStatus);
807: if(fnd_api.to_boolean(nvl(p_endDebug, fnd_api.g_true))) then
808: wip_logger.cleanup(l_retStatus);
809: end if;
810: end if;
811: end schedule;
812:
847: p_curIdx in number,
848: p_doneSchedBatch in boolean,
849: p_prevIdx in number) return date is
850: l_retStatus VARCHAR2(1);
851: l_params wip_logger.param_tbl_t;
852: i number;
853: l_maxEndDate date;
854: l_logLevel NUMBER := fnd_log.g_current_runtime_level;
855: l_bool boolean;
869: end if;
870: l_params(6).paramName := 'p_prevIdx';
871: l_params(6).paramValue := p_prevIdx;
872:
873: wip_logger.entryPoint(p_procName => 'wip_infResSched_grp.getStartDate',
874: p_params => l_params,
875: x_returnStatus => l_retStatus);
876: end if;
877:
880: i := p_curIdx;
881:
882: if(p_doneSchedBatch) then
883: if(l_logLevel <= wip_constants.full_logging) then
884: wip_logger.log('done scheduling batch', l_retStatus);
885: end if;
886: while(getNextResIdx(p_range, p_schedFlag, g_backward, p_resTbls, i)) loop
887: if(l_logLevel <= wip_constants.full_logging) then
888: wip_logger.log('in loop', l_retStatus);
884: wip_logger.log('done scheduling batch', l_retStatus);
885: end if;
886: while(getNextResIdx(p_range, p_schedFlag, g_backward, p_resTbls, i)) loop
887: if(l_logLevel <= wip_constants.full_logging) then
888: wip_logger.log('in loop', l_retStatus);
889: wip_logger.log('resID' || p_resTbls.resID(i), l_retStatus);
890: wip_logger.log('opSeq' || p_resTbls.opSeqNum(i), l_retStatus);
891: wip_logger.log('schSeq' || p_resTbls.schedSeqNum(i), l_retStatus);
892: wip_logger.log('idx' || i, l_retStatus);
885: end if;
886: while(getNextResIdx(p_range, p_schedFlag, g_backward, p_resTbls, i)) loop
887: if(l_logLevel <= wip_constants.full_logging) then
888: wip_logger.log('in loop', l_retStatus);
889: wip_logger.log('resID' || p_resTbls.resID(i), l_retStatus);
890: wip_logger.log('opSeq' || p_resTbls.opSeqNum(i), l_retStatus);
891: wip_logger.log('schSeq' || p_resTbls.schedSeqNum(i), l_retStatus);
892: wip_logger.log('idx' || i, l_retStatus);
893: end if;
886: while(getNextResIdx(p_range, p_schedFlag, g_backward, p_resTbls, i)) loop
887: if(l_logLevel <= wip_constants.full_logging) then
888: wip_logger.log('in loop', l_retStatus);
889: wip_logger.log('resID' || p_resTbls.resID(i), l_retStatus);
890: wip_logger.log('opSeq' || p_resTbls.opSeqNum(i), l_retStatus);
891: wip_logger.log('schSeq' || p_resTbls.schedSeqNum(i), l_retStatus);
892: wip_logger.log('idx' || i, l_retStatus);
893: end if;
894: if(p_resTbls.schedSeqNum(i) = p_resTbls.schedSeqNum(p_prevIdx) and
887: if(l_logLevel <= wip_constants.full_logging) then
888: wip_logger.log('in loop', l_retStatus);
889: wip_logger.log('resID' || p_resTbls.resID(i), l_retStatus);
890: wip_logger.log('opSeq' || p_resTbls.opSeqNum(i), l_retStatus);
891: wip_logger.log('schSeq' || p_resTbls.schedSeqNum(i), l_retStatus);
892: wip_logger.log('idx' || i, l_retStatus);
893: end if;
894: if(p_resTbls.schedSeqNum(i) = p_resTbls.schedSeqNum(p_prevIdx) and
895: p_resTbls.opSeqNum(i) = p_resTbls.opSeqNum(p_prevIdx)) then
888: wip_logger.log('in loop', l_retStatus);
889: wip_logger.log('resID' || p_resTbls.resID(i), l_retStatus);
890: wip_logger.log('opSeq' || p_resTbls.opSeqNum(i), l_retStatus);
891: wip_logger.log('schSeq' || p_resTbls.schedSeqNum(i), l_retStatus);
892: wip_logger.log('idx' || i, l_retStatus);
893: end if;
894: if(p_resTbls.schedSeqNum(i) = p_resTbls.schedSeqNum(p_prevIdx) and
895: p_resTbls.opSeqNum(i) = p_resTbls.opSeqNum(p_prevIdx)) then
896: l_maxEndDate := greatest(nvl(l_maxEndDate, p_resTbls.endDate(i)), p_resTbls.endDate(i));
894: if(p_resTbls.schedSeqNum(i) = p_resTbls.schedSeqNum(p_prevIdx) and
895: p_resTbls.opSeqNum(i) = p_resTbls.opSeqNum(p_prevIdx)) then
896: l_maxEndDate := greatest(nvl(l_maxEndDate, p_resTbls.endDate(i)), p_resTbls.endDate(i));
897: if(l_logLevel <= wip_constants.full_logging) then
898: wip_logger.log('resource in batch. endDate:' || to_char(p_resTbls.endDate(i), g_logDateFmt), l_retStatus);
899: end if;
900: else
901: if(l_logLevel <= wip_constants.full_logging) then
902: wip_logger.log('resource not in batch.', l_retStatus);
898: wip_logger.log('resource in batch. endDate:' || to_char(p_resTbls.endDate(i), g_logDateFmt), l_retStatus);
899: end if;
900: else
901: if(l_logLevel <= wip_constants.full_logging) then
902: wip_logger.log('resource not in batch.', l_retStatus);
903: end if;
904: exit;
905: end if;
906: end loop;
909: l_maxEndDate := p_resTbls.endDate(i);
910: end if;
911:
912: if (l_logLevel <= wip_constants.trace_logging) then
913: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.getStartDate',
914: p_procReturnStatus => to_char(l_maxEndDate),
915: p_msg => 'finished scheduling',
916: x_returnStatus => l_retStatus);
917: end if;
923: i in number,
924: x_shifts in out nocopy shift_recTbl_t,
925: x_overlap_exists in out nocopy boolean) is
926: l_retStatus VARCHAR2(1);
927: l_params wip_logger.param_tbl_t;
928: l_logLevel NUMBER := fnd_log.g_current_runtime_level;
929: begin
930: if (l_logLevel <= wip_constants.trace_logging) then
931: wip_logger.entryPoint(p_procName => 'wip_infResSched_grp.process_shift',
927: l_params wip_logger.param_tbl_t;
928: l_logLevel NUMBER := fnd_log.g_current_runtime_level;
929: begin
930: if (l_logLevel <= wip_constants.trace_logging) then
931: wip_logger.entryPoint(p_procName => 'wip_infResSched_grp.process_shift',
932: p_params => l_params,
933: x_returnStatus => l_retStatus);
934: end if;
935: if(p_fwd_or_bk_flag = 1) then /*backward schedule*/
936: /*For backward scheduling, the shifts are ordered in descreasing order of
937: startDate. Thats how the cursors populating into x_shifts are written.*/
938: if(x_shifts.endDate(i+1) < x_shifts.startDate(i)) then
939: if(l_logLevel <= wip_constants.full_logging) then
940: wip_logger.log('no overlap for shift', l_retStatus);
941: end if;
942: else
943: x_overlap_exists := true;
944: if(x_shifts.endDate(i+1) <= x_shifts.endDate(i)) then
942: else
943: x_overlap_exists := true;
944: if(x_shifts.endDate(i+1) <= x_shifts.endDate(i)) then
945: if(l_logLevel <= wip_constants.full_logging) then
946: wip_logger.log('partial overlap for shift', l_retStatus);
947: end if;
948: x_shifts.endDate(i+1) := x_shifts.endDate(i);
949: else
950: if(l_logLevel <= wip_constants.full_logging) then
947: end if;
948: x_shifts.endDate(i+1) := x_shifts.endDate(i);
949: else
950: if(l_logLevel <= wip_constants.full_logging) then
951: wip_logger.log('complete overlap for shift', l_retStatus);
952: end if;
953: end if;
954: /*instead of deleting the shift here setting it to -1.
955: Will delete the -1 shifts together later, otherwise with everyloop
961: /*For forward scheduling, the shifts are ordered in ascending order of
962: startDate. Thats how the cursors populating into x_shifts are written.*/
963: if(x_shifts.startDate(i+1) > x_shifts.endDate(i)) then
964: if(l_logLevel <= wip_constants.full_logging) then
965: wip_logger.log('no overlap for shift', l_retStatus);
966: end if;
967: else
968: x_overlap_exists := true;
969: if(x_shifts.startDate(i+1) > x_shifts.startDate(i)) then
967: else
968: x_overlap_exists := true;
969: if(x_shifts.startDate(i+1) > x_shifts.startDate(i)) then
970: if(l_logLevel <= wip_constants.full_logging) then
971: wip_logger.log('partial overlap for shift: adjusted startDate', l_retStatus);
972: end if;
973: x_shifts.startDate(i+1) := x_shifts.startDate(i);
974: end if;
975: if(x_shifts.endDate(i+1) < x_shifts.endDate(i)) then
973: x_shifts.startDate(i+1) := x_shifts.startDate(i);
974: end if;
975: if(x_shifts.endDate(i+1) < x_shifts.endDate(i)) then
976: if(l_logLevel <= wip_constants.full_logging) then
977: wip_logger.log('partial overlap for shift: adjusted endDate', l_retStatus);
978: end if;
979: x_shifts.endDate(i+1) := x_shifts.endDate(i);
980: end if;
981: /*instead of deleting the shift here setting it to -1.
985: x_shifts.shiftNum(i) := -1;
986: end if;
987: end if;
988: if(l_logLevel <= wip_constants.trace_logging) then
989: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.process_shift',
990: p_procReturnStatus => l_retStatus,
991: p_msg => 'successful complete',
992: x_returnStatus => l_retStatus);
993: end if;
998: l_retStatus VARCHAR2(1);
999: l_overlap_exists boolean := true;
1000: l_count number;
1001: l_count2 number;
1002: l_params wip_logger.param_tbl_t;
1003: l_logLevel NUMBER := fnd_log.g_current_runtime_level;
1004: l_shifts shift_recTbl_t;
1005: begin
1006: if (l_logLevel <= wip_constants.trace_logging) then
1003: l_logLevel NUMBER := fnd_log.g_current_runtime_level;
1004: l_shifts shift_recTbl_t;
1005: begin
1006: if (l_logLevel <= wip_constants.trace_logging) then
1007: wip_logger.entryPoint(p_procName => 'wip_infResSched_grp.process_overlap_shifts',
1008: p_params => l_params,
1009: x_returnStatus => l_retStatus);
1010:
1011: wip_logger.log('shifts BEFORE overlap processing', l_retStatus);
1007: wip_logger.entryPoint(p_procName => 'wip_infResSched_grp.process_overlap_shifts',
1008: p_params => l_params,
1009: x_returnStatus => l_retStatus);
1010:
1011: wip_logger.log('shifts BEFORE overlap processing', l_retStatus);
1012: wip_logger.log('*********************************', l_retStatus);
1013: end if;
1014:
1015: for i in 1..(x_shifts.shiftNum.count) loop
1008: p_params => l_params,
1009: x_returnStatus => l_retStatus);
1010:
1011: wip_logger.log('shifts BEFORE overlap processing', l_retStatus);
1012: wip_logger.log('*********************************', l_retStatus);
1013: end if;
1014:
1015: for i in 1..(x_shifts.shiftNum.count) loop
1016: if (l_logLevel <= wip_constants.trace_logging) then
1013: end if;
1014:
1015: for i in 1..(x_shifts.shiftNum.count) loop
1016: if (l_logLevel <= wip_constants.trace_logging) then
1017: wip_logger.log('shiftNum: ' || x_shifts.shiftNum(i)
1018: || ' startDate: ' || to_char(x_shifts.startDate(i), g_logDateFmt)
1019: || ' endDate:' || to_char(x_shifts.endDate(i), g_logDateFmt), l_retStatus);
1020: end if;
1021: end loop;
1023: for i in 1..(x_shifts.shiftNum.count) loop
1024: if(x_shifts.endDate(i) < x_shifts.startDate(i)) then --overnight shift
1025: x_shifts.endDate(i) := x_shifts.endDate(i) + 1;
1026: if (l_logLevel <= wip_constants.trace_logging) then
1027: wip_logger.log('increased endDate by 1 day for count: ' || i, l_retStatus);
1028: end if;
1029: end if;
1030: end loop;
1031: /*Looping in x_shifts to delete overlapping shifts and to create one most inclusive shifts.
1033: But in later part of the code, Shift Numbers are not needed and only start and end times
1034: are used.*/
1035: while(l_overlap_exists) loop
1036: if (l_logLevel <= wip_constants.trace_logging) then
1037: wip_logger.log('while loop begins', l_retStatus);
1038: end if;
1039: --at the start of the loop, setting overlap to false. if it exists it will set to true
1040: l_overlap_exists := false;
1041: for i in 1..(x_shifts.shiftNum.count-1) loop --i shift is compared to i+1
1047: end loop;--for loop
1048: /*Deleting shifts with shiftNum = -1*/
1049: l_count := x_shifts.shiftNum.count;
1050: if (l_logLevel <= wip_constants.trace_logging) then
1051: wip_logger.log('num of shifts: ' || l_count, l_retStatus);
1052: end if;
1053: for i in 1..(x_shifts.shiftNum.count) loop
1054: if (l_logLevel <= wip_constants.trace_logging) then
1055: wip_logger.log('counter: ' || i, l_retStatus);
1051: wip_logger.log('num of shifts: ' || l_count, l_retStatus);
1052: end if;
1053: for i in 1..(x_shifts.shiftNum.count) loop
1054: if (l_logLevel <= wip_constants.trace_logging) then
1055: wip_logger.log('counter: ' || i, l_retStatus);
1056: end if;
1057: if(x_shifts.shiftNum(i) = -1) then
1058: l_count2 := i;
1059: for j in (i)..(x_shifts.shiftNum.count) loop
1061: l_count2 := j; exit;
1062: end if;
1063: end loop;
1064: if (l_logLevel <= wip_constants.trace_logging) then
1065: wip_logger.log('moving shift at position: ' || l_count2 || 'to position: ' || i, l_retStatus);
1066: end if;
1067: if(l_count2 > i) then
1068: x_shifts.shiftNum(i) := x_shifts.shiftNum(l_count2);
1069: x_shifts.startDate(i) := x_shifts.startDate(l_count2);
1070: x_shifts.endDate(i) := x_shifts.endDate(l_count2);
1071: x_shifts.shiftNum(l_count2) := -1;
1072: end if;
1073: if (l_logLevel <= wip_constants.trace_logging) then
1074: wip_logger.log('deleted counter: ' || i, l_retStatus);
1075: end if;
1076: end if;
1077: end loop;
1078: /*Identifying how many shifts left after deletion*/
1081: if(x_shifts.shiftNum(i) > -1) then l_count2 := l_count2 + 1;
1082: end if;
1083: end loop;
1084: if (l_logLevel <= wip_constants.trace_logging) then
1085: wip_logger.log('num of shifts (after deletion): ' || l_count2, l_retStatus);
1086: end if;
1087: /*Trim the remaining shifts. These shifts are deleted above and have shiftNum as -1*/
1088: x_shifts.shiftNum.trim(l_count-l_count2);
1089: x_shifts.startDate.trim(l_count-l_count2);
1089: x_shifts.startDate.trim(l_count-l_count2);
1090: x_shifts.endDate.trim(l_count-l_count2);
1091: end loop;
1092: if (l_logLevel <= wip_constants.trace_logging) then
1093: wip_logger.log('shifts AFTER overlap processing', l_retStatus);
1094: wip_logger.log('*********************************', l_retStatus);
1095: end if;
1096: for i in 1..(x_shifts.shiftNum.count) loop
1097: if (l_logLevel <= wip_constants.trace_logging) then
1090: x_shifts.endDate.trim(l_count-l_count2);
1091: end loop;
1092: if (l_logLevel <= wip_constants.trace_logging) then
1093: wip_logger.log('shifts AFTER overlap processing', l_retStatus);
1094: wip_logger.log('*********************************', l_retStatus);
1095: end if;
1096: for i in 1..(x_shifts.shiftNum.count) loop
1097: if (l_logLevel <= wip_constants.trace_logging) then
1098: wip_logger.log('shiftNum: ' || x_shifts.shiftNum(i)
1094: wip_logger.log('*********************************', l_retStatus);
1095: end if;
1096: for i in 1..(x_shifts.shiftNum.count) loop
1097: if (l_logLevel <= wip_constants.trace_logging) then
1098: wip_logger.log('shiftNum: ' || x_shifts.shiftNum(i)
1099: || ' startDate: ' || to_char(x_shifts.startDate(i), g_logDateFmt)
1100: || ' endDate:' || to_char(x_shifts.endDate(i), g_logDateFmt), l_retStatus);
1101: end if;
1102: end loop;
1100: || ' endDate:' || to_char(x_shifts.endDate(i), g_logDateFmt), l_retStatus);
1101: end if;
1102: end loop;
1103: if(l_logLevel <= wip_constants.trace_logging) then
1104: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.process_overlap_shifts',
1105: p_procReturnStatus => l_retStatus,
1106: p_msg => 'successful complete',
1107: x_returnStatus => l_retStatus);
1108: end if;
1194: end of fix bug 7027946 */
1195:
1196: l_shifts shift_recTbl_t;
1197: l_logLevel NUMBER := fnd_log.g_current_runtime_level;
1198: l_params wip_logger.param_tbl_t;
1199: l_retStatus VARCHAR2(1);
1200: l_resourceScheduled boolean := false;
1201: l_cursorStartDate date := trunc(p_startDate) - 1;--subtract 1 to make sure to get wraparound shifts (start on prev day)
1202: l_fromDate date;
1218: l_params(4).paramName := 'p_idx';
1219: l_params(4).paramValue := p_idx;
1220: l_params(5).paramName := 'p_repLineID';
1221: l_params(5).paramValue := p_repLineID;
1222: wip_logger.entryPoint(p_procName => 'wip_infResSched_grp.forwardSchResource',
1223: p_params => l_params,
1224: x_returnStatus => l_retStatus);
1225: end if;
1226: x_returnStatus := fnd_api.g_ret_sts_success;
1230: if( p_repLineID is null
1231: and x_resTbls.avail24Flag(p_idx) = wip_constants.no
1232: and x_resTbls.schedFlag(p_idx) <> wip_constants.sched_no) then
1233: if (l_logLevel <= wip_constants.trace_logging) then
1234: wip_logger.log('This is a shift resource. Need to validate shift setup', l_retStatus);
1235: end if;
1236: begin
1237: -- Bug 16322124 . Modified for Performance.
1238: select 1
1249: and rownum = 1);
1250: exception
1251: when NO_DATA_FOUND then
1252: if (l_logLevel <= wip_constants.trace_logging) then
1253: wip_logger.log('Error: Missing shifts or shift times!', l_retStatus);
1254: end if;
1255: fnd_message.set_name('WIP', 'WIP_SHIFT_RESOURCE');
1256: fnd_message.set_token('ENTITY1', x_resTbls.resSeqNum(p_idx));
1257: fnd_message.set_token('ENTITY2', x_resTbls.opSeqNum(p_idx));
1264: x_resTbls.usgEndIdx(p_idx) := null;
1265: loop
1266: exit when l_resourceScheduled;
1267: if (l_logLevel <= wip_constants.full_logging) then
1268: wip_logger.log('cursor start date is' || to_char(l_cursorStartDate, g_logDateFmt), l_retStatus);
1269: wip_logger.log('cursor end date is' || to_char((l_cursorStartDate + g_dateCursorLen - 1/86400), g_logDateFmt), l_retStatus);
1270: end if;
1271:
1272: --for v_endDate, subtract a second to avoid overlap between cursors.
1265: loop
1266: exit when l_resourceScheduled;
1267: if (l_logLevel <= wip_constants.full_logging) then
1268: wip_logger.log('cursor start date is' || to_char(l_cursorStartDate, g_logDateFmt), l_retStatus);
1269: wip_logger.log('cursor end date is' || to_char((l_cursorStartDate + g_dateCursorLen - 1/86400), g_logDateFmt), l_retStatus);
1270: end if;
1271:
1272: --for v_endDate, subtract a second to avoid overlap between cursors.
1273: if(p_repLineID is not null) then
1271:
1272: --for v_endDate, subtract a second to avoid overlap between cursors.
1273: if(p_repLineID is not null) then
1274: if(l_logLevel <= wip_constants.full_logging) then
1275: wip_logger.log('scheduling repetitive', l_retStatus);
1276: end if;
1277: open c_repTimes(v_repLineID => p_repLineID,
1278: v_orgID => p_orgID,
1279: v_startDate => l_cursorStartDate,
1284: l_shifts.endDate;
1285: close c_repTimes;
1286: elsif(x_resTbls.avail24Flag(p_idx) = wip_constants.yes) then
1287: if(l_logLevel <= wip_constants.full_logging) then
1288: wip_logger.log('scheduling 24HR resource', l_retStatus);
1289: end if;
1290: open c_24HrTimes(v_orgID => p_orgID,
1291: v_startDate => l_cursorStartDate,
1292: v_endDate => l_cursorStartDate + g_dateCursorLen - 1/86400);
1296: l_shifts.endDate;
1297: close c_24HrTimes;
1298: else
1299: if(l_logLevel <= wip_constants.full_logging) then
1300: wip_logger.log('scheduling shift resource', l_retStatus);
1301: end if;
1302: open c_shiftTimes(v_resID => x_resTbls.resID(p_idx),
1303: v_deptID => x_resTbls.deptID(p_idx),
1304: v_orgID => p_orgID,
1312: if (l_shifts.shiftNum.count = 0 ) then
1313: /* Fix for bug 5660475: If shifts are not available in the date range,
1314: we should continue to search in the next date range, instead of erroring out. */
1315: if (l_logLevel <= wip_constants.trace_logging) then
1316: wip_logger.log('No shifts found in this period.', l_retStatus);
1317: end if;
1318: l_resourceScheduled := false;
1319: end if;
1320:
1329: /* end of fix bug 7027946 */
1330:
1331: if (l_logLevel <= wip_constants.full_logging) then
1332: if (l_logLevel <= wip_constants.trace_logging) then
1333: wip_logger.log('*** Shift records after incorporating capacity exceptions ***', l_retStatus);
1334: end if;
1335: for i in 1..l_shifts.shiftNum.count loop
1336: if (l_logLevel <= wip_constants.trace_logging) then
1337: wip_logger.log(l_shifts.shiftNum(i)||' - '||to_char(l_shifts.startDate(i),'DD.MM.YYYY HH24:MI:SS')||' - '||to_char(l_shifts.endDate(i),'DD.MM.YYYY HH24:MI:SS'), l_retStatus);
1333: wip_logger.log('*** Shift records after incorporating capacity exceptions ***', l_retStatus);
1334: end if;
1335: for i in 1..l_shifts.shiftNum.count loop
1336: if (l_logLevel <= wip_constants.trace_logging) then
1337: wip_logger.log(l_shifts.shiftNum(i)||' - '||to_char(l_shifts.startDate(i),'DD.MM.YYYY HH24:MI:SS')||' - '||to_char(l_shifts.endDate(i),'DD.MM.YYYY HH24:MI:SS'), l_retStatus);
1338: end if;
1339: end loop;
1340: end if;
1341:
1348: l_shifts.endDate(i) := l_shifts.endDate(i) + 1;
1349: end if;
1350:
1351: if (l_logLevel <= wip_constants.full_logging) then
1352: wip_logger.log('**********shiftNum:' || l_shifts.shiftNum(i), l_retStatus);
1353: wip_logger.log('**shift start date:' || to_char(l_shifts.startDate(i), g_logDateFmt), l_retStatus);
1354: wip_logger.log('****shift end date:' || to_char(l_shifts.endDate(i), g_logDateFmt), l_retStatus);
1355: end if;
1356:
1349: end if;
1350:
1351: if (l_logLevel <= wip_constants.full_logging) then
1352: wip_logger.log('**********shiftNum:' || l_shifts.shiftNum(i), l_retStatus);
1353: wip_logger.log('**shift start date:' || to_char(l_shifts.startDate(i), g_logDateFmt), l_retStatus);
1354: wip_logger.log('****shift end date:' || to_char(l_shifts.endDate(i), g_logDateFmt), l_retStatus);
1355: end if;
1356:
1357: --if shift ends before the requested start date, skip it since none of the shift
1350:
1351: if (l_logLevel <= wip_constants.full_logging) then
1352: wip_logger.log('**********shiftNum:' || l_shifts.shiftNum(i), l_retStatus);
1353: wip_logger.log('**shift start date:' || to_char(l_shifts.startDate(i), g_logDateFmt), l_retStatus);
1354: wip_logger.log('****shift end date:' || to_char(l_shifts.endDate(i), g_logDateFmt), l_retStatus);
1355: end if;
1356:
1357: --if shift ends before the requested start date, skip it since none of the shift
1358: --can be used. don't do this in the sql query as it degrades performance
1357: --if shift ends before the requested start date, skip it since none of the shift
1358: --can be used. don't do this in the sql query as it degrades performance
1359: if(l_shifts.endDate(i) <= p_startDate) then
1360: if (l_logLevel <= wip_constants.full_logging) then
1361: wip_logger.log('skipping shift (ends before start date)', l_retStatus);
1362: end if;
1363: goto NO_FULFILL_USAGE;--end of loop
1364: end if;
1365:
1365:
1366: --if the shift starts before the start time, adjust the shift length
1367: l_fromDate := greatest(l_shifts.startDate(i), p_startDate);
1368: if (l_logLevel <= wip_constants.full_logging) then
1369: wip_logger.log('calculated start date: ' || to_char(l_fromDate, g_logDateFmt), l_retStatus);
1370: end if;
1371:
1372: l_shiftLen := l_shifts.endDate(i) - l_fromDate;
1373: /*Bug 7015594: If shift start time is same as end time then consider it as 24 hours resource.
1377: l_shiftLen := 86400;
1378: end if;
1379:
1380: if (l_logLevel <= wip_constants.full_logging) then
1381: wip_logger.log('shiftLen(HRS) is ' || round(l_shiftLen*24, g_precision), l_retStatus);
1382: end if;
1383:
1384: if(round(l_shiftLen, g_precision) = 0) then
1385: if (l_logLevel <= wip_constants.full_logging) then
1382: end if;
1383:
1384: if(round(l_shiftLen, g_precision) = 0) then
1385: if (l_logLevel <= wip_constants.full_logging) then
1386: wip_logger.log('skipping shift (no usage)', l_retStatus);
1387: end if;
1388: goto NO_FULFILL_USAGE;--end of loop
1389: end if;
1390:
1391:
1392: if(l_startDate is null) then
1393: l_startDate := l_fromDate;
1394: if (l_logLevel <= wip_constants.full_logging) then
1395: wip_logger.log('calculated resource start date:' || to_char(l_startDate, g_logDateFmt), l_retStatus);
1396: end if;
1397: end if;
1398: /*Bug 9355406: fixed regression caused by 7136375. if remaining usage is one day:
1399: for regular resource, consider shift fullfilled resource usage and exit the loop
1409: if ((g_reset_date=0 and ( (round(l_remUsage, g_precision) < round(l_shiftLen, g_precision)) or
1410: (round(l_remUsage, g_precision) = round(l_shiftLen, g_precision) and x_resTbls.avail24Flag(p_idx) <> wip_constants.yes))) or (g_reset_date=1 and (round(l_remUsage, g_precision) <= round(l_shiftLen, g_precision)) ) ) then
1411:
1412: if (l_logLevel <= wip_constants.full_logging) then
1413: wip_logger.log('calculated resource start date:' || to_char(l_startDate, g_logDateFmt), l_retStatus);
1414: end if;
1415: x_resTbls.startDate(p_idx) := l_startDate;
1416: x_resTbls.endDate(p_idx) := l_fromDate + l_remUsage;
1417: --record shift usage
1420: x_resTbls.usgCumMinProcTime.extend(1);
1421:
1422: l_usgIdx := x_resTbls.usgStartDate.count;
1423: if (l_logLevel <= wip_constants.full_logging) then
1424: wip_logger.log('idx is ' || l_usgIdx, l_retStatus);
1425: wip_logger.log('count is ' || x_resTbls.usgStartIdx.count, l_retStatus);
1426: wip_logger.log('val is ' || x_resTbls.usgStartIdx(p_idx), l_retStatus);
1427: end if;
1428:
1421:
1422: l_usgIdx := x_resTbls.usgStartDate.count;
1423: if (l_logLevel <= wip_constants.full_logging) then
1424: wip_logger.log('idx is ' || l_usgIdx, l_retStatus);
1425: wip_logger.log('count is ' || x_resTbls.usgStartIdx.count, l_retStatus);
1426: wip_logger.log('val is ' || x_resTbls.usgStartIdx(p_idx), l_retStatus);
1427: end if;
1428:
1429: x_resTbls.usgStartIdx(p_idx) := nvl(x_resTbls.usgStartIdx(p_idx), l_usgIdx);
1422: l_usgIdx := x_resTbls.usgStartDate.count;
1423: if (l_logLevel <= wip_constants.full_logging) then
1424: wip_logger.log('idx is ' || l_usgIdx, l_retStatus);
1425: wip_logger.log('count is ' || x_resTbls.usgStartIdx.count, l_retStatus);
1426: wip_logger.log('val is ' || x_resTbls.usgStartIdx(p_idx), l_retStatus);
1427: end if;
1428:
1429: x_resTbls.usgStartIdx(p_idx) := nvl(x_resTbls.usgStartIdx(p_idx), l_usgIdx);
1430: x_resTbls.usgEndIdx(p_idx) := l_usgIdx;
1434: --shift fulfilled resource => usage end time is resource end time
1435: x_resTbls.usgEndDate(l_usgIdx) := x_resTbls.endDate(p_idx);
1436: if(l_isFirstUsg) then
1437: if (l_logLevel <= wip_constants.full_logging) then
1438: wip_logger.log('first usage', l_retStatus);
1439: end if;
1440: l_isFirstUsg := false;
1441: l_prevProcTime := 0;
1442: else
1440: l_isFirstUsg := false;
1441: l_prevProcTime := 0;
1442: else
1443: if (l_logLevel <= wip_constants.full_logging) then
1444: wip_logger.log('not first usage', l_retStatus);
1445: end if;
1446: l_prevProcTime := x_resTbls.usgCumMinProcTime(l_usgIdx - 1);
1447: end if;
1448:
1450: (24*60)*(x_resTbls.usgEndDate(l_usgIdx) -
1451: x_resTbls.usgStartDate(l_usgIdx));
1452:
1453: if (l_logLevel <= wip_constants.full_logging) then
1454: wip_logger.log('start date is ' || to_char(x_resTbls.startDate(p_idx), g_logDateFmt), l_retStatus);
1455: wip_logger.log('end date is ' || to_char(x_resTbls.endDate(p_idx), g_logDateFmt), l_retStatus);
1456: wip_logger.log('usage:' || to_char(x_resTbls.usgStartDate(l_usgIdx), g_logDateFmt) || ' - ' ||
1457: to_char(x_resTbls.usgEndDate(l_usgIdx), g_logDateFmt), l_retStatus);
1458: wip_logger.log('cum usage time:' || x_resTbls.usgCumMinProcTime(l_usgIdx), l_retStatus);
1451: x_resTbls.usgStartDate(l_usgIdx));
1452:
1453: if (l_logLevel <= wip_constants.full_logging) then
1454: wip_logger.log('start date is ' || to_char(x_resTbls.startDate(p_idx), g_logDateFmt), l_retStatus);
1455: wip_logger.log('end date is ' || to_char(x_resTbls.endDate(p_idx), g_logDateFmt), l_retStatus);
1456: wip_logger.log('usage:' || to_char(x_resTbls.usgStartDate(l_usgIdx), g_logDateFmt) || ' - ' ||
1457: to_char(x_resTbls.usgEndDate(l_usgIdx), g_logDateFmt), l_retStatus);
1458: wip_logger.log('cum usage time:' || x_resTbls.usgCumMinProcTime(l_usgIdx), l_retStatus);
1459: end if;
1452:
1453: if (l_logLevel <= wip_constants.full_logging) then
1454: wip_logger.log('start date is ' || to_char(x_resTbls.startDate(p_idx), g_logDateFmt), l_retStatus);
1455: wip_logger.log('end date is ' || to_char(x_resTbls.endDate(p_idx), g_logDateFmt), l_retStatus);
1456: wip_logger.log('usage:' || to_char(x_resTbls.usgStartDate(l_usgIdx), g_logDateFmt) || ' - ' ||
1457: to_char(x_resTbls.usgEndDate(l_usgIdx), g_logDateFmt), l_retStatus);
1458: wip_logger.log('cum usage time:' || x_resTbls.usgCumMinProcTime(l_usgIdx), l_retStatus);
1459: end if;
1460:
1454: wip_logger.log('start date is ' || to_char(x_resTbls.startDate(p_idx), g_logDateFmt), l_retStatus);
1455: wip_logger.log('end date is ' || to_char(x_resTbls.endDate(p_idx), g_logDateFmt), l_retStatus);
1456: wip_logger.log('usage:' || to_char(x_resTbls.usgStartDate(l_usgIdx), g_logDateFmt) || ' - ' ||
1457: to_char(x_resTbls.usgEndDate(l_usgIdx), g_logDateFmt), l_retStatus);
1458: wip_logger.log('cum usage time:' || x_resTbls.usgCumMinProcTime(l_usgIdx), l_retStatus);
1459: end if;
1460:
1461: l_resourceScheduled := true; --exit outer loop
1462: exit; --exit inner loop
1486: x_resTbls.usgCumMinProcTime(l_usgIdx) := l_prevProcTime +
1487: (24*60)*(x_resTbls.usgEndDate(l_usgIdx) -
1488: x_resTbls.usgStartDate(l_usgIdx));
1489: if (l_logLevel <= wip_constants.full_logging) then
1490: wip_logger.log('exhausted shift. remaining usage(HRS) is ' || round(l_remUsage*24, g_precision), l_retStatus);
1491: wip_logger.log('usage:' || to_char(x_resTbls.usgStartDate(l_usgIdx), g_logDateFmt) || ' - ' ||
1492: to_char(x_resTbls.usgEndDate(l_usgIdx), g_logDateFmt), l_retStatus);
1493: wip_logger.log('cum usage time:' || x_resTbls.usgCumMinProcTime(l_usgIdx), l_retStatus);
1494: end if;
1487: (24*60)*(x_resTbls.usgEndDate(l_usgIdx) -
1488: x_resTbls.usgStartDate(l_usgIdx));
1489: if (l_logLevel <= wip_constants.full_logging) then
1490: wip_logger.log('exhausted shift. remaining usage(HRS) is ' || round(l_remUsage*24, g_precision), l_retStatus);
1491: wip_logger.log('usage:' || to_char(x_resTbls.usgStartDate(l_usgIdx), g_logDateFmt) || ' - ' ||
1492: to_char(x_resTbls.usgEndDate(l_usgIdx), g_logDateFmt), l_retStatus);
1493: wip_logger.log('cum usage time:' || x_resTbls.usgCumMinProcTime(l_usgIdx), l_retStatus);
1494: end if;
1495: end if;
1489: if (l_logLevel <= wip_constants.full_logging) then
1490: wip_logger.log('exhausted shift. remaining usage(HRS) is ' || round(l_remUsage*24, g_precision), l_retStatus);
1491: wip_logger.log('usage:' || to_char(x_resTbls.usgStartDate(l_usgIdx), g_logDateFmt) || ' - ' ||
1492: to_char(x_resTbls.usgEndDate(l_usgIdx), g_logDateFmt), l_retStatus);
1493: wip_logger.log('cum usage time:' || x_resTbls.usgCumMinProcTime(l_usgIdx), l_retStatus);
1494: end if;
1495: end if;
1496: <
1497: null;
1503:
1504: --if the next start date is after the end of the calendar, then we can't schedule anything
1505: if(l_cursorStartDate > p_maxDate) then
1506: if (l_logLevel <= wip_constants.full_logging) then
1507: wip_logger.log('exhausted calendar. remaining usage(HRS) is ' || round(l_remUsage*24, g_precision), l_retStatus);
1508: end if;
1509: fnd_message.set_name('WIP', 'WIP_NO_CALENDAR');
1510: fnd_msg_pub.add;
1511: raise fnd_api.g_exc_unexpected_error;
1512: end if;
1513: end if;
1514: end loop;
1515: if(l_logLevel <= wip_constants.trace_logging) then
1516: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.forwardSchResource',
1517: p_procReturnStatus => x_returnStatus,
1518: p_msg => 'success',
1519: x_returnStatus => l_retStatus);
1520: end if;
1521: exception
1522: when fnd_api.g_exc_unexpected_error then
1523: x_returnStatus := fnd_api.g_ret_sts_unexp_error;
1524: if(l_logLevel <= wip_constants.trace_logging) then
1525: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.forwardSchResource',
1526: p_procReturnStatus => x_returnStatus,
1527: p_msg => 'error',
1528: x_returnStatus => l_retStatus);
1529: end if;
1532: fnd_msg_pub.add_exc_msg(p_pkg_name => 'wip_infResSched_grp',
1533: p_procedure_name => 'forwardSchResource',
1534: p_error_text => SQLERRM);
1535: if(l_logLevel <= wip_constants.trace_logging) then
1536: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.forwardSchResource',
1537: p_procReturnStatus => x_returnStatus,
1538: p_msg => 'unexp error: ' || SQLERRM,
1539: x_returnStatus => l_retStatus);
1540: end if;
1589:
1590: l_capacity capacity_recTbl_t ;
1591: l_capacity_2 capacity_recTbl_t ;
1592: l_logLevel NUMBER := fnd_log.g_current_runtime_level;
1593: l_params wip_logger.param_tbl_t;
1594: l_retStatus VARCHAR2(1);
1595:
1596: l_firstRow NUMBER;
1597: l_currRow NUMBER;
1620: l_params(2).paramValue := p_deptID;
1621: l_params(3).paramName := 'p_orgID';
1622: l_params(3).paramValue := p_orgID;
1623:
1624: wip_logger.entryPoint(p_procName => 'wip_infResSched_grp.capacityExceptions',
1625: p_params => l_params,
1626: x_returnStatus => x_returnStatus);
1627: end if;
1628:
1633: bulk collect into l_capacity ;
1634: close c_capacityDtls;
1635:
1636: if(l_logLevel <= wip_constants.trace_logging) then
1637: wip_logger.log('l_capacity.count = ' || l_capacity.count, l_retStatus);
1638: end if;
1639:
1640: /*Bug 16322124: if there is no capacity exception being found, return and do nothing.
1641: This will avoid shift being deleted if customer define shift start time and endtime the same as 00:00:00*/
1640: /*Bug 16322124: if there is no capacity exception being found, return and do nothing.
1641: This will avoid shift being deleted if customer define shift start time and endtime the same as 00:00:00*/
1642: if(l_capacity.count = 0) then
1643: if(l_logLevel <= wip_constants.trace_logging) then
1644: wip_logger.log('No Capacity Exception found. exiting capacity exception', l_retStatus);
1645: end if;
1646: return;
1647: end if;
1648: --------------------
1670: l_capacity := l_capacity_2;
1671:
1672: l_capacity_2.DELETE;
1673: if (l_logLevel <= wip_constants.trace_logging) then
1674: wip_logger.log('l capacity count is :'||l_capacity.Count, l_retStatus);
1675: end if;
1676: -------------------
1677: for i in 1..l_capacity.count loop
1678: if (l_logLevel <= wip_constants.trace_logging) then
1675: end if;
1676: -------------------
1677: for i in 1..l_capacity.count loop
1678: if (l_logLevel <= wip_constants.trace_logging) then
1679: wip_logger.log('****************************modified capacity exception records**************************************', l_retStatus);
1680: wip_logger.log('shiftNum = ' || l_capacity(i).shiftNum , l_retStatus);
1681: wip_logger.log('fromDate = ' || l_capacity(i).fromDate , l_retStatus);
1682: wip_logger.log('toDate = ' || l_capacity(i).toDate , l_retStatus);
1683: wip_logger.log('fromTime = ' || l_capacity(i).fromTime , l_retStatus);
1676: -------------------
1677: for i in 1..l_capacity.count loop
1678: if (l_logLevel <= wip_constants.trace_logging) then
1679: wip_logger.log('****************************modified capacity exception records**************************************', l_retStatus);
1680: wip_logger.log('shiftNum = ' || l_capacity(i).shiftNum , l_retStatus);
1681: wip_logger.log('fromDate = ' || l_capacity(i).fromDate , l_retStatus);
1682: wip_logger.log('toDate = ' || l_capacity(i).toDate , l_retStatus);
1683: wip_logger.log('fromTime = ' || l_capacity(i).fromTime , l_retStatus);
1684: wip_logger.log('toTime = ' || l_capacity(i).toTime , l_retStatus);
1677: for i in 1..l_capacity.count loop
1678: if (l_logLevel <= wip_constants.trace_logging) then
1679: wip_logger.log('****************************modified capacity exception records**************************************', l_retStatus);
1680: wip_logger.log('shiftNum = ' || l_capacity(i).shiftNum , l_retStatus);
1681: wip_logger.log('fromDate = ' || l_capacity(i).fromDate , l_retStatus);
1682: wip_logger.log('toDate = ' || l_capacity(i).toDate , l_retStatus);
1683: wip_logger.log('fromTime = ' || l_capacity(i).fromTime , l_retStatus);
1684: wip_logger.log('toTime = ' || l_capacity(i).toTime , l_retStatus);
1685: wip_logger.log('capacityChange = ' || l_capacity(i).capacityChange , l_retStatus);
1678: if (l_logLevel <= wip_constants.trace_logging) then
1679: wip_logger.log('****************************modified capacity exception records**************************************', l_retStatus);
1680: wip_logger.log('shiftNum = ' || l_capacity(i).shiftNum , l_retStatus);
1681: wip_logger.log('fromDate = ' || l_capacity(i).fromDate , l_retStatus);
1682: wip_logger.log('toDate = ' || l_capacity(i).toDate , l_retStatus);
1683: wip_logger.log('fromTime = ' || l_capacity(i).fromTime , l_retStatus);
1684: wip_logger.log('toTime = ' || l_capacity(i).toTime , l_retStatus);
1685: wip_logger.log('capacityChange = ' || l_capacity(i).capacityChange , l_retStatus);
1686: wip_logger.log('actionType = ' || l_capacity(i).actionType , l_retStatus);
1679: wip_logger.log('****************************modified capacity exception records**************************************', l_retStatus);
1680: wip_logger.log('shiftNum = ' || l_capacity(i).shiftNum , l_retStatus);
1681: wip_logger.log('fromDate = ' || l_capacity(i).fromDate , l_retStatus);
1682: wip_logger.log('toDate = ' || l_capacity(i).toDate , l_retStatus);
1683: wip_logger.log('fromTime = ' || l_capacity(i).fromTime , l_retStatus);
1684: wip_logger.log('toTime = ' || l_capacity(i).toTime , l_retStatus);
1685: wip_logger.log('capacityChange = ' || l_capacity(i).capacityChange , l_retStatus);
1686: wip_logger.log('actionType = ' || l_capacity(i).actionType , l_retStatus);
1687: wip_logger.log('capacityUnits = ' || l_capacity(i).capacityUnits , l_retStatus);
1680: wip_logger.log('shiftNum = ' || l_capacity(i).shiftNum , l_retStatus);
1681: wip_logger.log('fromDate = ' || l_capacity(i).fromDate , l_retStatus);
1682: wip_logger.log('toDate = ' || l_capacity(i).toDate , l_retStatus);
1683: wip_logger.log('fromTime = ' || l_capacity(i).fromTime , l_retStatus);
1684: wip_logger.log('toTime = ' || l_capacity(i).toTime , l_retStatus);
1685: wip_logger.log('capacityChange = ' || l_capacity(i).capacityChange , l_retStatus);
1686: wip_logger.log('actionType = ' || l_capacity(i).actionType , l_retStatus);
1687: wip_logger.log('capacityUnits = ' || l_capacity(i).capacityUnits , l_retStatus);
1688: end if;
1681: wip_logger.log('fromDate = ' || l_capacity(i).fromDate , l_retStatus);
1682: wip_logger.log('toDate = ' || l_capacity(i).toDate , l_retStatus);
1683: wip_logger.log('fromTime = ' || l_capacity(i).fromTime , l_retStatus);
1684: wip_logger.log('toTime = ' || l_capacity(i).toTime , l_retStatus);
1685: wip_logger.log('capacityChange = ' || l_capacity(i).capacityChange , l_retStatus);
1686: wip_logger.log('actionType = ' || l_capacity(i).actionType , l_retStatus);
1687: wip_logger.log('capacityUnits = ' || l_capacity(i).capacityUnits , l_retStatus);
1688: end if;
1689: end loop;
1682: wip_logger.log('toDate = ' || l_capacity(i).toDate , l_retStatus);
1683: wip_logger.log('fromTime = ' || l_capacity(i).fromTime , l_retStatus);
1684: wip_logger.log('toTime = ' || l_capacity(i).toTime , l_retStatus);
1685: wip_logger.log('capacityChange = ' || l_capacity(i).capacityChange , l_retStatus);
1686: wip_logger.log('actionType = ' || l_capacity(i).actionType , l_retStatus);
1687: wip_logger.log('capacityUnits = ' || l_capacity(i).capacityUnits , l_retStatus);
1688: end if;
1689: end loop;
1690: -------------------
1683: wip_logger.log('fromTime = ' || l_capacity(i).fromTime , l_retStatus);
1684: wip_logger.log('toTime = ' || l_capacity(i).toTime , l_retStatus);
1685: wip_logger.log('capacityChange = ' || l_capacity(i).capacityChange , l_retStatus);
1686: wip_logger.log('actionType = ' || l_capacity(i).actionType , l_retStatus);
1687: wip_logger.log('capacityUnits = ' || l_capacity(i).capacityUnits , l_retStatus);
1688: end if;
1689: end loop;
1690: -------------------
1691: for i in 1..l_capacity.count loop -- outer most loop to loop through all the capacity exception records
1694: l_lastRow := x_shifts.shiftNum.LAST;
1695: j := l_firstRow;
1696: flag := FALSE;
1697: if(l_logLevel <= wip_constants.trace_logging) then
1698: wip_logger.log('inside for. i = ' || i || ' n count = ' || l_capacity.count, l_retStatus);
1699: wip_logger.log('l_firstRow = ' || l_firstRow, l_retStatus);
1700: wip_logger.log('l_lastRow = ' || l_lastRow, l_retStatus);
1701: end if;
1702: if (l_capacity(i).actionType = wip_constants.DELETE_WKDY) THEN -- delete a working day
1695: j := l_firstRow;
1696: flag := FALSE;
1697: if(l_logLevel <= wip_constants.trace_logging) then
1698: wip_logger.log('inside for. i = ' || i || ' n count = ' || l_capacity.count, l_retStatus);
1699: wip_logger.log('l_firstRow = ' || l_firstRow, l_retStatus);
1700: wip_logger.log('l_lastRow = ' || l_lastRow, l_retStatus);
1701: end if;
1702: if (l_capacity(i).actionType = wip_constants.DELETE_WKDY) THEN -- delete a working day
1703: /* If more than one shift exists for a day (in case of shift with breakups) then it will
1696: flag := FALSE;
1697: if(l_logLevel <= wip_constants.trace_logging) then
1698: wip_logger.log('inside for. i = ' || i || ' n count = ' || l_capacity.count, l_retStatus);
1699: wip_logger.log('l_firstRow = ' || l_firstRow, l_retStatus);
1700: wip_logger.log('l_lastRow = ' || l_lastRow, l_retStatus);
1701: end if;
1702: if (l_capacity(i).actionType = wip_constants.DELETE_WKDY) THEN -- delete a working day
1703: /* If more than one shift exists for a day (in case of shift with breakups) then it will
1704: delete all those shifts for that day. */
1702: if (l_capacity(i).actionType = wip_constants.DELETE_WKDY) THEN -- delete a working day
1703: /* If more than one shift exists for a day (in case of shift with breakups) then it will
1704: delete all those shifts for that day. */
1705: if(l_logLevel <= wip_constants.trace_logging) then
1706: wip_logger.log('inside delete a working day', l_retStatus);
1707: end if;
1708: WHILE (j <= l_lastRow) LOOP
1709: /* Changed IF condition to WHILE loop to delete all the shifts for the given day */
1710: WHILE ( j <= l_lastRow AND Trunc(l_capacity(i).fromDate) = Trunc(x_shifts.startDate(j)) ) LOOP
1727: END LOOP;
1728:
1729: ELSIF (l_capacity(i).actionType = wip_constants.ADD_WKDY) THEN -- add a non-working day
1730: if(l_logLevel <= wip_constants.trace_logging) then
1731: wip_logger.log('inside adding a non-working day', l_retStatus);
1732: end if;
1733:
1734: /* Bug 12627423: Added extra if statement to check that the fromDate of the capacity exception is bigger
1735: than the first day of xshifts. */
1765: END IF;
1766:
1767: ELSIF l_capacity(i).actionType = wip_constants.MODIFY_WKDY THEN -- modify capacity - modify or reduce capacity
1768: if(l_logLevel <= wip_constants.trace_logging) then
1769: wip_logger.log('inside modifying capacity', l_retStatus);
1770: end if;
1771: WHILE (j <= l_lastRow) LOOP
1772: l_shiftDate := x_shifts.startDate(j);
1773: if(l_logLevel <= wip_constants.trace_logging) then
1770: end if;
1771: WHILE (j <= l_lastRow) LOOP
1772: l_shiftDate := x_shifts.startDate(j);
1773: if(l_logLevel <= wip_constants.trace_logging) then
1774: wip_logger.log('1: j = ' || j || ' l_lastRow = ' || l_lastRow, l_retStatus);
1775: wip_logger.log('inside while: j = ' || j || ' startDate = ' || to_char(x_shifts.startDate(j),g_logDateFmt), l_retStatus);
1776: wip_logger.log('inside while: j = ' || j || ' fromDate = ' || to_char(l_capacity(i).fromDate,g_logDateFmt), l_retStatus);
1777: wip_logger.log('inside while: j = ' || j || ' toDate = ' || to_char(l_capacity(i).toDate,g_logDateFmt), l_retStatus);
1778: end if;
1771: WHILE (j <= l_lastRow) LOOP
1772: l_shiftDate := x_shifts.startDate(j);
1773: if(l_logLevel <= wip_constants.trace_logging) then
1774: wip_logger.log('1: j = ' || j || ' l_lastRow = ' || l_lastRow, l_retStatus);
1775: wip_logger.log('inside while: j = ' || j || ' startDate = ' || to_char(x_shifts.startDate(j),g_logDateFmt), l_retStatus);
1776: wip_logger.log('inside while: j = ' || j || ' fromDate = ' || to_char(l_capacity(i).fromDate,g_logDateFmt), l_retStatus);
1777: wip_logger.log('inside while: j = ' || j || ' toDate = ' || to_char(l_capacity(i).toDate,g_logDateFmt), l_retStatus);
1778: end if;
1779: flag := FALSE;
1772: l_shiftDate := x_shifts.startDate(j);
1773: if(l_logLevel <= wip_constants.trace_logging) then
1774: wip_logger.log('1: j = ' || j || ' l_lastRow = ' || l_lastRow, l_retStatus);
1775: wip_logger.log('inside while: j = ' || j || ' startDate = ' || to_char(x_shifts.startDate(j),g_logDateFmt), l_retStatus);
1776: wip_logger.log('inside while: j = ' || j || ' fromDate = ' || to_char(l_capacity(i).fromDate,g_logDateFmt), l_retStatus);
1777: wip_logger.log('inside while: j = ' || j || ' toDate = ' || to_char(l_capacity(i).toDate,g_logDateFmt), l_retStatus);
1778: end if;
1779: flag := FALSE;
1780: IF ( Trunc(x_shifts.startDate(j)) >= Trunc(l_capacity(i).fromDate)
1773: if(l_logLevel <= wip_constants.trace_logging) then
1774: wip_logger.log('1: j = ' || j || ' l_lastRow = ' || l_lastRow, l_retStatus);
1775: wip_logger.log('inside while: j = ' || j || ' startDate = ' || to_char(x_shifts.startDate(j),g_logDateFmt), l_retStatus);
1776: wip_logger.log('inside while: j = ' || j || ' fromDate = ' || to_char(l_capacity(i).fromDate,g_logDateFmt), l_retStatus);
1777: wip_logger.log('inside while: j = ' || j || ' toDate = ' || to_char(l_capacity(i).toDate,g_logDateFmt), l_retStatus);
1778: end if;
1779: flag := FALSE;
1780: IF ( Trunc(x_shifts.startDate(j)) >= Trunc(l_capacity(i).fromDate)
1781: AND Trunc(x_shifts.startDate(j)) <= Trunc(l_capacity(i).toDate) ) THEN
1792: if j is null then
1793: j := l_lastRow;
1794: end if;
1795: if (l_logLevel <= wip_constants.trace_logging) then
1796: wip_logger.log('After j is null code', l_retStatus);
1797: end if;
1798: IF ( l_capacity(i).capacityChange > 0 ) THEN -- add capacity
1799: /*Idea here is that if a new capacity is added and one of it's dates matches with shift dates then we adjust shift start
1800: or end time. In all other cases we will add a new time in shift table for the exception to add the new capacity. */
1798: IF ( l_capacity(i).capacityChange > 0 ) THEN -- add capacity
1799: /*Idea here is that if a new capacity is added and one of it's dates matches with shift dates then we adjust shift start
1800: or end time. In all other cases we will add a new time in shift table for the exception to add the new capacity. */
1801: if(l_logLevel <= wip_constants.trace_logging) then
1802: wip_logger.log('ADD capacity', l_retStatus);
1803: end if;
1804: if (x_shifts.endDate(k) = (l_capacity(i).fromDate + l_capacity(i).fromTime/86400)) then
1805: if(l_logLevel <= wip_constants.trace_logging) then
1806: wip_logger.log('inside IF.1: endDate = ' || to_char(x_shifts.endDate(k), g_logDateFmt), l_retStatus);
1802: wip_logger.log('ADD capacity', l_retStatus);
1803: end if;
1804: if (x_shifts.endDate(k) = (l_capacity(i).fromDate + l_capacity(i).fromTime/86400)) then
1805: if(l_logLevel <= wip_constants.trace_logging) then
1806: wip_logger.log('inside IF.1: endDate = ' || to_char(x_shifts.endDate(k), g_logDateFmt), l_retStatus);
1807: end if;
1808: x_shifts.endDate(k) := x_shifts.endDate(k) + ((l_capacity(i).toTime - l_capacity(i).fromTime)/86400 );
1809: if(l_logLevel <= wip_constants.trace_logging) then
1810: wip_logger.log('inside IF.1 endDate = ' || to_char(x_shifts.endDate(k),g_logDateFmt), l_retStatus);
1806: wip_logger.log('inside IF.1: endDate = ' || to_char(x_shifts.endDate(k), g_logDateFmt), l_retStatus);
1807: end if;
1808: x_shifts.endDate(k) := x_shifts.endDate(k) + ((l_capacity(i).toTime - l_capacity(i).fromTime)/86400 );
1809: if(l_logLevel <= wip_constants.trace_logging) then
1810: wip_logger.log('inside IF.1 endDate = ' || to_char(x_shifts.endDate(k),g_logDateFmt), l_retStatus);
1811: wip_logger.log('inside elseIF.1: startDate = ' || to_char(x_shifts.startDate(k),g_logDateFmt), l_retStatus);
1812: end if;
1813: elsif (x_shifts.startDate(k) = (l_capacity(i).toDate + l_capacity(i).toTime/86400)) then
1814: if(l_logLevel <= wip_constants.trace_logging) then
1807: end if;
1808: x_shifts.endDate(k) := x_shifts.endDate(k) + ((l_capacity(i).toTime - l_capacity(i).fromTime)/86400 );
1809: if(l_logLevel <= wip_constants.trace_logging) then
1810: wip_logger.log('inside IF.1 endDate = ' || to_char(x_shifts.endDate(k),g_logDateFmt), l_retStatus);
1811: wip_logger.log('inside elseIF.1: startDate = ' || to_char(x_shifts.startDate(k),g_logDateFmt), l_retStatus);
1812: end if;
1813: elsif (x_shifts.startDate(k) = (l_capacity(i).toDate + l_capacity(i).toTime/86400)) then
1814: if(l_logLevel <= wip_constants.trace_logging) then
1815: wip_logger.log('inside elseIF.1: startDate = ' || to_char(x_shifts.startDate(k),g_logDateFmt), l_retStatus);
1811: wip_logger.log('inside elseIF.1: startDate = ' || to_char(x_shifts.startDate(k),g_logDateFmt), l_retStatus);
1812: end if;
1813: elsif (x_shifts.startDate(k) = (l_capacity(i).toDate + l_capacity(i).toTime/86400)) then
1814: if(l_logLevel <= wip_constants.trace_logging) then
1815: wip_logger.log('inside elseIF.1: startDate = ' || to_char(x_shifts.startDate(k),g_logDateFmt), l_retStatus);
1816: end if;
1817: x_shifts.startDate(k) := x_shifts.startDate(k) - ((l_capacity(i).toTime - l_capacity(i).fromTime)/86400) ;
1818: if(l_logLevel <= wip_constants.trace_logging) then
1819: wip_logger.log('inside elseIF.1 startDate = ' || to_char(x_shifts.startDate(k),g_logDateFmt), l_retStatus);
1815: wip_logger.log('inside elseIF.1: startDate = ' || to_char(x_shifts.startDate(k),g_logDateFmt), l_retStatus);
1816: end if;
1817: x_shifts.startDate(k) := x_shifts.startDate(k) - ((l_capacity(i).toTime - l_capacity(i).fromTime)/86400) ;
1818: if(l_logLevel <= wip_constants.trace_logging) then
1819: wip_logger.log('inside elseIF.1 startDate = ' || to_char(x_shifts.startDate(k),g_logDateFmt), l_retStatus);
1820: end if;
1821: elsif (x_shifts.endDate(k) = x_shifts.startDate(k)) THEN
1822: -- this should only come when user reduce the entire shift capacity
1823: -- after reducing the entire shift capacity, shift start and end date will be same
1828: --as shift start date and end date
1829: if((x_shifts.startDate(k) <> (l_capacity(i).fromDate + l_capacity(i).fromTime/86400)) and
1830: (x_shifts.endDate(k) <> (l_capacity(i).toDate + l_capacity(i).toTime/86400))) then
1831: if(l_logLevel <= wip_constants.trace_logging) then
1832: wip_logger.log('inside else.1:', l_retStatus);
1833: end if;
1834: if (x_shifts.startDate(k) > (l_capacity(i).fromDate + l_capacity(i).fromTime/86400)) then
1835: /* capacity shd be added before the current shift */
1836: l_counter := x_shifts.shiftNum.PRIOR(j);
1838: if (l_counter IS NULL) then
1839: l_counter := j;
1840: end if;
1841: if(l_logLevel <= wip_constants.trace_logging) then
1842: wip_logger.log('inside else.1: j-1: l_counter = ' || l_counter, l_retStatus);
1843: end if;
1844: else
1845: /* capcacity should be added after the current shift */
1846: l_counter := j;
1844: else
1845: /* capcacity should be added after the current shift */
1846: l_counter := j;
1847: if(l_logLevel <= wip_constants.trace_logging) then
1848: wip_logger.log('inside else.1: j: l_counter = ' || l_counter, l_retStatus);
1849: end if;
1850: end if;
1851: x_shifts.shiftNum.extend; -- extend the xshifts table by one row and insert the day and then shift remaning days
1852: x_shifts.startDate.extend;
1868: end if;
1869: end if;
1870: ELSE -- reduce capacity
1871: if(l_logLevel <= wip_constants.trace_logging) then
1872: wip_logger.log('inside else.2', l_retStatus);
1873: wip_logger.log('capacity units is ' || l_capacity(i).capacityUnits, l_retStatus);
1874: end if;
1875:
1876: /* Bug 14293561: The below if statement used to compare l_capacity(i).capacityUnits with the abs of l_capacity(i).capacityChange.
1869: end if;
1870: ELSE -- reduce capacity
1871: if(l_logLevel <= wip_constants.trace_logging) then
1872: wip_logger.log('inside else.2', l_retStatus);
1873: wip_logger.log('capacity units is ' || l_capacity(i).capacityUnits, l_retStatus);
1874: end if;
1875:
1876: /* Bug 14293561: The below if statement used to compare l_capacity(i).capacityUnits with the abs of l_capacity(i).capacityChange.
1877: capacityUnits seems to be a deprecated column as it was always returning null so flow would always avoid this if-statement
1879: */
1880:
1881: if(nvl(l_capacity(i).capacityUnits, 0) <= (abs(l_capacity(i).capacityChange))) THEN
1882: if(l_logLevel <= wip_constants.trace_logging) then
1883: wip_logger.log('inside else.2 capacityChange = ' || l_capacity(i).capacityChange, l_retStatus);
1884: /*Idea here is that if a capacity is reduced and one of it's dates matches with shift dates then we adjust
1885: shift start or end time. In all other cases we will reduce time in shift table for the exception to reduce the new capacity.*/
1886: wip_logger.log('before IF.2: endDate = ' || to_char(x_shifts.endDate(k),g_logDateFmt), l_retStatus);
1887: wip_logger.log('before IF.2: l_cap.toDateTime = ' || to_char(l_capacity(i).toDate + l_capacity(i).toTime/86400,g_logDateFmt), l_retStatus);
1882: if(l_logLevel <= wip_constants.trace_logging) then
1883: wip_logger.log('inside else.2 capacityChange = ' || l_capacity(i).capacityChange, l_retStatus);
1884: /*Idea here is that if a capacity is reduced and one of it's dates matches with shift dates then we adjust
1885: shift start or end time. In all other cases we will reduce time in shift table for the exception to reduce the new capacity.*/
1886: wip_logger.log('before IF.2: endDate = ' || to_char(x_shifts.endDate(k),g_logDateFmt), l_retStatus);
1887: wip_logger.log('before IF.2: l_cap.toDateTime = ' || to_char(l_capacity(i).toDate + l_capacity(i).toTime/86400,g_logDateFmt), l_retStatus);
1888: wip_logger.log('before IF.2: l_cap.fromDateTime = ' || to_char(l_capacity(i).fromDate + l_capacity(i).fromTime/86400,g_logDateFmt), l_retStatus);
1889: end if;
1890: if x_shifts.endDate(k) = (l_capacity(i).toDate + l_capacity(i).toTime/86400) then
1883: wip_logger.log('inside else.2 capacityChange = ' || l_capacity(i).capacityChange, l_retStatus);
1884: /*Idea here is that if a capacity is reduced and one of it's dates matches with shift dates then we adjust
1885: shift start or end time. In all other cases we will reduce time in shift table for the exception to reduce the new capacity.*/
1886: wip_logger.log('before IF.2: endDate = ' || to_char(x_shifts.endDate(k),g_logDateFmt), l_retStatus);
1887: wip_logger.log('before IF.2: l_cap.toDateTime = ' || to_char(l_capacity(i).toDate + l_capacity(i).toTime/86400,g_logDateFmt), l_retStatus);
1888: wip_logger.log('before IF.2: l_cap.fromDateTime = ' || to_char(l_capacity(i).fromDate + l_capacity(i).fromTime/86400,g_logDateFmt), l_retStatus);
1889: end if;
1890: if x_shifts.endDate(k) = (l_capacity(i).toDate + l_capacity(i).toTime/86400) then
1891: if(l_logLevel <= wip_constants.trace_logging) then
1884: /*Idea here is that if a capacity is reduced and one of it's dates matches with shift dates then we adjust
1885: shift start or end time. In all other cases we will reduce time in shift table for the exception to reduce the new capacity.*/
1886: wip_logger.log('before IF.2: endDate = ' || to_char(x_shifts.endDate(k),g_logDateFmt), l_retStatus);
1887: wip_logger.log('before IF.2: l_cap.toDateTime = ' || to_char(l_capacity(i).toDate + l_capacity(i).toTime/86400,g_logDateFmt), l_retStatus);
1888: wip_logger.log('before IF.2: l_cap.fromDateTime = ' || to_char(l_capacity(i).fromDate + l_capacity(i).fromTime/86400,g_logDateFmt), l_retStatus);
1889: end if;
1890: if x_shifts.endDate(k) = (l_capacity(i).toDate + l_capacity(i).toTime/86400) then
1891: if(l_logLevel <= wip_constants.trace_logging) then
1892: wip_logger.log('inside IF.2: endDate = ' || to_char(x_shifts.endDate(k),g_logDateFmt), l_retStatus);
1888: wip_logger.log('before IF.2: l_cap.fromDateTime = ' || to_char(l_capacity(i).fromDate + l_capacity(i).fromTime/86400,g_logDateFmt), l_retStatus);
1889: end if;
1890: if x_shifts.endDate(k) = (l_capacity(i).toDate + l_capacity(i).toTime/86400) then
1891: if(l_logLevel <= wip_constants.trace_logging) then
1892: wip_logger.log('inside IF.2: endDate = ' || to_char(x_shifts.endDate(k),g_logDateFmt), l_retStatus);
1893: end if;
1894: x_shifts.endDate(k) := x_shifts.endDate(k) - (l_capacity(i).toTime - l_capacity(i).fromTime)/86400;
1895:
1896: -- after reducing the shift if end time and start time become same then we need to delete this k th shift
1902: x_shifts.endDate(k) := x_shifts.endDate(m);
1903: k := m;
1904: END LOOP;
1905: if (l_logLevel <= wip_constants.trace_logging) then
1906: wip_logger.log('inside IF.2-: after while loop', l_retStatus);
1907: end if;
1908: x_shifts.shiftNum.trim(); -- trim the last row
1909: x_shifts.startDate.trim();
1910: x_shifts.endDate.trim();
1908: x_shifts.shiftNum.trim(); -- trim the last row
1909: x_shifts.startDate.trim();
1910: x_shifts.endDate.trim();
1911: if (l_logLevel <= wip_constants.trace_logging) then
1912: wip_logger.log('inside IF.2-: after trim', l_retStatus);
1913: end if;
1914: l_lastRow := x_shifts.shiftNum.LAST; -- updated last row
1915: if (l_logLevel <= wip_constants.trace_logging) then
1916: wip_logger.log('inside IF.2-: after l_lastRow = '||l_lastRow, l_retStatus);
1912: wip_logger.log('inside IF.2-: after trim', l_retStatus);
1913: end if;
1914: l_lastRow := x_shifts.shiftNum.LAST; -- updated last row
1915: if (l_logLevel <= wip_constants.trace_logging) then
1916: wip_logger.log('inside IF.2-: after l_lastRow = '||l_lastRow, l_retStatus);
1917: end if;
1918: j := x_shifts.shiftNum.PRIOR(j); -- update j as one shift record is deleted
1919: if (l_logLevel <= wip_constants.trace_logging) then
1920: wip_logger.log('inside IF.2-: after updating j = '||j, l_retStatus);
1916: wip_logger.log('inside IF.2-: after l_lastRow = '||l_lastRow, l_retStatus);
1917: end if;
1918: j := x_shifts.shiftNum.PRIOR(j); -- update j as one shift record is deleted
1919: if (l_logLevel <= wip_constants.trace_logging) then
1920: wip_logger.log('inside IF.2-: after updating j = '||j, l_retStatus);
1921: end if;
1922: end if;*/
1923: --
1924:
1922: end if;*/
1923: --
1924:
1925: if(l_logLevel <= wip_constants.trace_logging) then
1926: wip_logger.log('inside IF.2--: endDate = .... = ', l_retStatus);
1927:
1928: end if;
1929: elsif x_shifts.startDate(k) = (l_capacity(i).fromDate + l_capacity(i).fromTime/86400) then
1930: if(l_logLevel <= wip_constants.trace_logging) then
1927:
1928: end if;
1929: elsif x_shifts.startDate(k) = (l_capacity(i).fromDate + l_capacity(i).fromTime/86400) then
1930: if(l_logLevel <= wip_constants.trace_logging) then
1931: wip_logger.log('inside ELSEIF.2: endDate = ' || to_char(x_shifts.startDate(k),g_logDateFmt), l_retStatus);
1932: end if;
1933: x_shifts.startDate(k) := x_shifts.startDate(k) + (l_capacity(i).toTime - l_capacity(i).fromTime)/86400 ;
1934: if(l_logLevel <= wip_constants.trace_logging) then
1935: wip_logger.log('inside ELSEIF.2--: endDate = ' || to_char(x_shifts.startDate(k),g_logDateFmt), l_retStatus);
1931: wip_logger.log('inside ELSEIF.2: endDate = ' || to_char(x_shifts.startDate(k),g_logDateFmt), l_retStatus);
1932: end if;
1933: x_shifts.startDate(k) := x_shifts.startDate(k) + (l_capacity(i).toTime - l_capacity(i).fromTime)/86400 ;
1934: if(l_logLevel <= wip_constants.trace_logging) then
1935: wip_logger.log('inside ELSEIF.2--: endDate = ' || to_char(x_shifts.startDate(k),g_logDateFmt), l_retStatus);
1936: end if;
1937: else
1938: if(l_logLevel <= wip_constants.trace_logging) then
1939: wip_logger.log('inside else.2--: endDate = ' || to_char(x_shifts.endDate(k),g_logDateFmt), l_retStatus);
1935: wip_logger.log('inside ELSEIF.2--: endDate = ' || to_char(x_shifts.startDate(k),g_logDateFmt), l_retStatus);
1936: end if;
1937: else
1938: if(l_logLevel <= wip_constants.trace_logging) then
1939: wip_logger.log('inside else.2--: endDate = ' || to_char(x_shifts.endDate(k),g_logDateFmt), l_retStatus);
1940: end if;
1941: -- Split the shift time into two here to reduce capacity.
1942: -- Add a new record in x_shifts table after Kth record.
1943: -- for new record start date is (l_capacity(i).toDate + l_capacity(i).toTime/86400)
2003: end if;
2004: end loop;
2005:
2006: IF(l_logLevel <= wip_constants.trace_logging) then
2007: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.capacityExceptions',
2008: p_procReturnStatus => x_returnStatus,
2009: p_msg => 'success',
2010: x_returnStatus => l_retStatus);
2011: END IF;
2012: EXCEPTION
2013: WHEN fnd_api.g_exc_unexpected_error THEN
2014: x_returnStatus := fnd_api.g_ret_sts_unexp_error;
2015: if(l_logLevel <= wip_constants.trace_logging) then
2016: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.capacityExceptions',
2017: p_procReturnStatus => x_returnStatus,
2018: p_msg => 'error',
2019: x_returnStatus => l_retStatus);
2020: end if;
2023: fnd_msg_pub.add_exc_msg(p_pkg_name => 'wip_infResSched_grp',
2024: p_procedure_name => 'capacityExceptions',
2025: p_error_text => SQLERRM);
2026: if(l_logLevel <= wip_constants.trace_logging) then
2027: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.capacityExceptions',
2028: p_procReturnStatus => x_returnStatus,
2029: p_msg => 'unexp error: ' || SQLERRM,
2030: x_returnStatus => l_retStatus);
2031: end if;
2040: x_resTbls in out NOCOPY OP_RES_RECTBL_T,
2041: x_returnStatus OUT NOCOPY VARCHAR2) is
2042: l_resStartDate DATE; -- := p_startDate;
2043: l_logLevel NUMBER := fnd_log.g_current_runtime_level;
2044: l_params wip_logger.param_tbl_t;
2045: l_retStatus VARCHAR2(1);
2046: l_fromTime NUMBER;
2047: l_maxDate DATE;
2048: l_shiftStartDate DATE;
2063: l_params(4).paramValue := p_range(2);
2064: l_params(5).paramName := 'p_schedFlag';
2065: l_params(5).paramValue := p_schedFlag;
2066:
2067: wip_logger.entryPoint(p_procName => 'wip_infResSched_grp.forwardSchedule',
2068: p_params => l_params,
2069: x_returnStatus => x_returnStatus);
2070: end if;
2071: --get the maximum date
2076: and mp.calendar_code = bc.calendar_code;
2077:
2078: while(getNextResIdx(p_range, p_schedFlag, g_forward, x_resTbls, i)) loop
2079: if (l_logLevel <= wip_constants.full_logging) then
2080: wip_logger.log('begin scheduling resource:' || x_resTbls.resID(i), l_retStatus);
2081: wip_logger.log(' operation:' || x_resTbls.opSeqNum(i), l_retStatus);
2082: wip_logger.log(' res seq num:' || x_resTbls.resSeqNum(i), l_retStatus);
2083: wip_logger.log(' schedule seq num:' || x_resTbls.schedSeqNum(i), l_retStatus);
2084: wip_logger.log(' sched flag:' || x_resTbls.schedFlag(i), l_retStatus);
2077:
2078: while(getNextResIdx(p_range, p_schedFlag, g_forward, x_resTbls, i)) loop
2079: if (l_logLevel <= wip_constants.full_logging) then
2080: wip_logger.log('begin scheduling resource:' || x_resTbls.resID(i), l_retStatus);
2081: wip_logger.log(' operation:' || x_resTbls.opSeqNum(i), l_retStatus);
2082: wip_logger.log(' res seq num:' || x_resTbls.resSeqNum(i), l_retStatus);
2083: wip_logger.log(' schedule seq num:' || x_resTbls.schedSeqNum(i), l_retStatus);
2084: wip_logger.log(' sched flag:' || x_resTbls.schedFlag(i), l_retStatus);
2085: wip_logger.log(' total usage (HRS):' || round(x_resTbls.totalDaysUsg(i)*24, g_precision), l_retStatus);
2078: while(getNextResIdx(p_range, p_schedFlag, g_forward, x_resTbls, i)) loop
2079: if (l_logLevel <= wip_constants.full_logging) then
2080: wip_logger.log('begin scheduling resource:' || x_resTbls.resID(i), l_retStatus);
2081: wip_logger.log(' operation:' || x_resTbls.opSeqNum(i), l_retStatus);
2082: wip_logger.log(' res seq num:' || x_resTbls.resSeqNum(i), l_retStatus);
2083: wip_logger.log(' schedule seq num:' || x_resTbls.schedSeqNum(i), l_retStatus);
2084: wip_logger.log(' sched flag:' || x_resTbls.schedFlag(i), l_retStatus);
2085: wip_logger.log(' total usage (HRS):' || round(x_resTbls.totalDaysUsg(i)*24, g_precision), l_retStatus);
2086:
2079: if (l_logLevel <= wip_constants.full_logging) then
2080: wip_logger.log('begin scheduling resource:' || x_resTbls.resID(i), l_retStatus);
2081: wip_logger.log(' operation:' || x_resTbls.opSeqNum(i), l_retStatus);
2082: wip_logger.log(' res seq num:' || x_resTbls.resSeqNum(i), l_retStatus);
2083: wip_logger.log(' schedule seq num:' || x_resTbls.schedSeqNum(i), l_retStatus);
2084: wip_logger.log(' sched flag:' || x_resTbls.schedFlag(i), l_retStatus);
2085: wip_logger.log(' total usage (HRS):' || round(x_resTbls.totalDaysUsg(i)*24, g_precision), l_retStatus);
2086:
2087: if(l_prevResIdx is not null) then
2080: wip_logger.log('begin scheduling resource:' || x_resTbls.resID(i), l_retStatus);
2081: wip_logger.log(' operation:' || x_resTbls.opSeqNum(i), l_retStatus);
2082: wip_logger.log(' res seq num:' || x_resTbls.resSeqNum(i), l_retStatus);
2083: wip_logger.log(' schedule seq num:' || x_resTbls.schedSeqNum(i), l_retStatus);
2084: wip_logger.log(' sched flag:' || x_resTbls.schedFlag(i), l_retStatus);
2085: wip_logger.log(' total usage (HRS):' || round(x_resTbls.totalDaysUsg(i)*24, g_precision), l_retStatus);
2086:
2087: if(l_prevResIdx is not null) then
2088: wip_logger.log('prev sched seq num is:' || x_resTbls.schedSeqNum(l_prevResIdx), l_retStatus);
2081: wip_logger.log(' operation:' || x_resTbls.opSeqNum(i), l_retStatus);
2082: wip_logger.log(' res seq num:' || x_resTbls.resSeqNum(i), l_retStatus);
2083: wip_logger.log(' schedule seq num:' || x_resTbls.schedSeqNum(i), l_retStatus);
2084: wip_logger.log(' sched flag:' || x_resTbls.schedFlag(i), l_retStatus);
2085: wip_logger.log(' total usage (HRS):' || round(x_resTbls.totalDaysUsg(i)*24, g_precision), l_retStatus);
2086:
2087: if(l_prevResIdx is not null) then
2088: wip_logger.log('prev sched seq num is:' || x_resTbls.schedSeqNum(l_prevResIdx), l_retStatus);
2089: wip_logger.log('prev op seq num is:' || x_resTbls.opSeqNum(l_prevResIdx), l_retStatus);
2084: wip_logger.log(' sched flag:' || x_resTbls.schedFlag(i), l_retStatus);
2085: wip_logger.log(' total usage (HRS):' || round(x_resTbls.totalDaysUsg(i)*24, g_precision), l_retStatus);
2086:
2087: if(l_prevResIdx is not null) then
2088: wip_logger.log('prev sched seq num is:' || x_resTbls.schedSeqNum(l_prevResIdx), l_retStatus);
2089: wip_logger.log('prev op seq num is:' || x_resTbls.opSeqNum(l_prevResIdx), l_retStatus);
2090: end if;
2091: end if;
2092:
2085: wip_logger.log(' total usage (HRS):' || round(x_resTbls.totalDaysUsg(i)*24, g_precision), l_retStatus);
2086:
2087: if(l_prevResIdx is not null) then
2088: wip_logger.log('prev sched seq num is:' || x_resTbls.schedSeqNum(l_prevResIdx), l_retStatus);
2089: wip_logger.log('prev op seq num is:' || x_resTbls.opSeqNum(l_prevResIdx), l_retStatus);
2090: end if;
2091: end if;
2092:
2093: l_doneSchedBatch := false;
2096: x_resTbls.schedSeqNum(i) = x_resTbls.schedSeqNum(l_prevResIdx) and
2097: x_resTbls.opSeqNum(i) = x_resTbls.opSeqNum(l_prevResIdx)) then
2098: l_schedulingBatch := true;
2099: if (l_logLevel <= wip_constants.full_logging) then
2100: wip_logger.log('setting sched batch to true', l_retStatus);
2101: end if;
2102: --just finished scheduling batch
2103: elsif(l_schedulingBatch) then
2104: l_schedulingBatch := false;
2103: elsif(l_schedulingBatch) then
2104: l_schedulingBatch := false;
2105: l_doneSchedBatch := true;
2106: if (l_logLevel <= wip_constants.full_logging) then
2107: wip_logger.log('setting done sched batch to true', l_retStatus);
2108: end if;
2109: end if;
2110:
2111:
2125: l_prevResIdx := i;
2126: end loop;
2127:
2128: if(l_logLevel <= wip_constants.trace_logging) then
2129: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.forwardSchedule',
2130: p_procReturnStatus => x_returnStatus,
2131: p_msg => 'success',
2132: x_returnStatus => l_retStatus);
2133: end if;
2134: exception
2135: when fnd_api.g_exc_unexpected_error then
2136: x_returnStatus := fnd_api.g_ret_sts_unexp_error;
2137: if(l_logLevel <= wip_constants.trace_logging) then
2138: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.forwardSchedule',
2139: p_procReturnStatus => x_returnStatus,
2140: p_msg => 'error',
2141: x_returnStatus => l_retStatus);
2142: end if;
2145: fnd_msg_pub.add_exc_msg(p_pkg_name => 'wip_infResSched_grp',
2146: p_procedure_name => 'forwardSchedule',
2147: p_error_text => SQLERRM);
2148: if(l_logLevel <= wip_constants.trace_logging) then
2149: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.forwardSchedule',
2150: p_procReturnStatus => x_returnStatus,
2151: p_msg => 'unexp error: ' || SQLERRM,
2152: x_returnStatus => l_retStatus);
2153: end if;
2160: p_curIdx in number,
2161: p_doneSchedBatch in boolean,
2162: p_prevIdx in number) return date is
2163: l_retStatus VARCHAR2(1);
2164: l_params wip_logger.param_tbl_t;
2165: i number;
2166: l_minStartDate date;
2167: l_logLevel NUMBER := fnd_log.g_current_runtime_level;
2168: l_bool boolean;
2182: end if;
2183: l_params(6).paramName := 'p_prevIdx';
2184: l_params(6).paramValue := p_prevIdx;
2185:
2186: wip_logger.entryPoint(p_procName => 'wip_infResSched_grp.getEndDate',
2187: p_params => l_params,
2188: x_returnStatus => l_retStatus);
2189: end if;
2190:
2193: i := p_curIdx;
2194:
2195: if(p_doneSchedBatch) then
2196: if(l_logLevel <= wip_constants.full_logging) then
2197: wip_logger.log('done scheduling batch', l_retStatus);
2198: end if;
2199: while(getNextResIdx(p_range, p_schedFlag, g_forward, p_resTbls, i)) loop
2200: if(l_logLevel <= wip_constants.full_logging) then
2201: wip_logger.log('in loop', l_retStatus);
2197: wip_logger.log('done scheduling batch', l_retStatus);
2198: end if;
2199: while(getNextResIdx(p_range, p_schedFlag, g_forward, p_resTbls, i)) loop
2200: if(l_logLevel <= wip_constants.full_logging) then
2201: wip_logger.log('in loop', l_retStatus);
2202: wip_logger.log('resID' || p_resTbls.resID(i), l_retStatus);
2203: wip_logger.log('opSeq' || p_resTbls.opSeqNum(i), l_retStatus);
2204: wip_logger.log('schSeq' || p_resTbls.schedSeqNum(i), l_retStatus);
2205: wip_logger.log('idx' || i, l_retStatus);
2198: end if;
2199: while(getNextResIdx(p_range, p_schedFlag, g_forward, p_resTbls, i)) loop
2200: if(l_logLevel <= wip_constants.full_logging) then
2201: wip_logger.log('in loop', l_retStatus);
2202: wip_logger.log('resID' || p_resTbls.resID(i), l_retStatus);
2203: wip_logger.log('opSeq' || p_resTbls.opSeqNum(i), l_retStatus);
2204: wip_logger.log('schSeq' || p_resTbls.schedSeqNum(i), l_retStatus);
2205: wip_logger.log('idx' || i, l_retStatus);
2206: end if;
2199: while(getNextResIdx(p_range, p_schedFlag, g_forward, p_resTbls, i)) loop
2200: if(l_logLevel <= wip_constants.full_logging) then
2201: wip_logger.log('in loop', l_retStatus);
2202: wip_logger.log('resID' || p_resTbls.resID(i), l_retStatus);
2203: wip_logger.log('opSeq' || p_resTbls.opSeqNum(i), l_retStatus);
2204: wip_logger.log('schSeq' || p_resTbls.schedSeqNum(i), l_retStatus);
2205: wip_logger.log('idx' || i, l_retStatus);
2206: end if;
2207: if(p_resTbls.schedSeqNum(i) = p_resTbls.schedSeqNum(p_prevIdx) and
2200: if(l_logLevel <= wip_constants.full_logging) then
2201: wip_logger.log('in loop', l_retStatus);
2202: wip_logger.log('resID' || p_resTbls.resID(i), l_retStatus);
2203: wip_logger.log('opSeq' || p_resTbls.opSeqNum(i), l_retStatus);
2204: wip_logger.log('schSeq' || p_resTbls.schedSeqNum(i), l_retStatus);
2205: wip_logger.log('idx' || i, l_retStatus);
2206: end if;
2207: if(p_resTbls.schedSeqNum(i) = p_resTbls.schedSeqNum(p_prevIdx) and
2208: p_resTbls.opSeqNum(i) = p_resTbls.opSeqNum(p_prevIdx)) then
2201: wip_logger.log('in loop', l_retStatus);
2202: wip_logger.log('resID' || p_resTbls.resID(i), l_retStatus);
2203: wip_logger.log('opSeq' || p_resTbls.opSeqNum(i), l_retStatus);
2204: wip_logger.log('schSeq' || p_resTbls.schedSeqNum(i), l_retStatus);
2205: wip_logger.log('idx' || i, l_retStatus);
2206: end if;
2207: if(p_resTbls.schedSeqNum(i) = p_resTbls.schedSeqNum(p_prevIdx) and
2208: p_resTbls.opSeqNum(i) = p_resTbls.opSeqNum(p_prevIdx)) then
2209: l_minStartDate := least(nvl(l_minStartDate, p_resTbls.startDate(i)), p_resTbls.startDate(i));
2207: if(p_resTbls.schedSeqNum(i) = p_resTbls.schedSeqNum(p_prevIdx) and
2208: p_resTbls.opSeqNum(i) = p_resTbls.opSeqNum(p_prevIdx)) then
2209: l_minStartDate := least(nvl(l_minStartDate, p_resTbls.startDate(i)), p_resTbls.startDate(i));
2210: if(l_logLevel <= wip_constants.full_logging) then
2211: wip_logger.log('resource in batch. startDate:' || to_char(p_resTbls.startDate(i), g_logDateFmt), l_retStatus);
2212: end if;
2213: else
2214: if(l_logLevel <= wip_constants.full_logging) then
2215: wip_logger.log('resource not in batch.', l_retStatus);
2211: wip_logger.log('resource in batch. startDate:' || to_char(p_resTbls.startDate(i), g_logDateFmt), l_retStatus);
2212: end if;
2213: else
2214: if(l_logLevel <= wip_constants.full_logging) then
2215: wip_logger.log('resource not in batch.', l_retStatus);
2216: end if;
2217: exit;
2218: end if;
2219: end loop;
2222: l_minStartDate := p_resTbls.startDate(i);
2223: end if;
2224:
2225: if (l_logLevel <= wip_constants.trace_logging) then
2226: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.getEndDate',
2227: p_procReturnStatus => to_char(l_minStartDate),
2228: p_msg => 'finished scheduling',
2229: x_returnStatus => l_retStatus);
2230: end if;
2316: endDate date_tbl_t);*/
2317:
2318: l_shifts shift_recTbl_t;
2319: l_logLevel NUMBER := fnd_log.g_current_runtime_level;
2320: l_params wip_logger.param_tbl_t;
2321: l_retStatus VARCHAR2(1);
2322: l_resourceScheduled boolean := false;
2323: l_cursorEndDate date := p_endDate;
2324: l_shiftEndDate date;
2345: l_params(4).paramName := 'p_idx';
2346: l_params(4).paramValue := p_idx;
2347: l_params(5).paramName := 'p_repLineID';
2348: l_params(5).paramValue := p_repLineID;
2349: wip_logger.entryPoint(p_procName => 'wip_infResSched_grp.backwardSchResource',
2350: p_params => l_params,
2351: x_returnStatus => l_retStatus);
2352: end if;
2353: /* Fix for bug 5660475: If dealing with shift resource, first check if shifts are setup fine. */
2355: if( p_repLineID is null
2356: and x_resTbls.avail24Flag(p_idx) = wip_constants.no
2357: and x_resTbls.schedFlag(p_idx) <> wip_constants.sched_no) then
2358: if (l_logLevel <= wip_constants.trace_logging) then
2359: wip_logger.log('This is a shift resource. Need to validate shift setup', l_retStatus);
2360: end if;
2361: begin
2362: -- Bug 16322124 . Modified for Performance.
2363: select 1
2374: and rownum = 1);
2375: exception
2376: when NO_DATA_FOUND then
2377: if (l_logLevel <= wip_constants.trace_logging) then
2378: wip_logger.log('Error: Missing shifts or shift times!', l_retStatus);
2379: end if;
2380: fnd_message.set_name('WIP', 'WIP_SHIFT_RESOURCE');
2381: fnd_message.set_token('ENTITY1', x_resTbls.resSeqNum(p_idx));
2382: fnd_message.set_token('ENTITY2', x_resTbls.opSeqNum(p_idx));
2390:
2391: loop
2392: exit when l_resourceScheduled;
2393: if (l_logLevel <= wip_constants.full_logging) then
2394: wip_logger.log('cursor start date: ' || to_char((l_cursorEndDate - (g_dateCursorLen - 1/86400)), g_logDateFmt), l_retStatus);
2395: wip_logger.log('cursor end date: ' || to_char(l_cursorEndDate, g_logDateFmt), l_retStatus);
2396: end if;
2397:
2398: --for v_endDate, subtract a second to avoid overlap between cursors.
2391: loop
2392: exit when l_resourceScheduled;
2393: if (l_logLevel <= wip_constants.full_logging) then
2394: wip_logger.log('cursor start date: ' || to_char((l_cursorEndDate - (g_dateCursorLen - 1/86400)), g_logDateFmt), l_retStatus);
2395: wip_logger.log('cursor end date: ' || to_char(l_cursorEndDate, g_logDateFmt), l_retStatus);
2396: end if;
2397:
2398: --for v_endDate, subtract a second to avoid overlap between cursors.
2399: if(p_repLineID is not null) then
2397:
2398: --for v_endDate, subtract a second to avoid overlap between cursors.
2399: if(p_repLineID is not null) then
2400: if (l_logLevel <= wip_constants.trace_logging) then
2401: wip_logger.log('Using c_repTimes', l_retStatus);
2402: end if;
2403: open c_repTimes(v_repLineID => p_repLineID,
2404: v_orgID => p_orgID,
2405: v_startDate => l_cursorEndDate - (g_dateCursorLen - 1/86400),
2410: l_shifts.endDate;
2411: close c_repTimes;
2412: elsif(x_resTbls.avail24Flag(p_idx) = wip_constants.yes) then
2413: if (l_logLevel <= wip_constants.trace_logging) then
2414: wip_logger.log('Using c_24HrTimes', l_retStatus);
2415: end if;
2416: open c_24HrTimes(v_orgID => p_orgID,
2417: v_startDate => l_cursorEndDate - (g_dateCursorLen - 1/86400),
2418: v_endDate => l_cursorEndDate);
2422: l_shifts.endDate;
2423: close c_24HrTimes;
2424: else
2425: if (l_logLevel <= wip_constants.trace_logging) then
2426: wip_logger.log('Using c_shiftTimes', l_retStatus);
2427: end if;
2428: open c_shiftTimes(v_resID => x_resTbls.resID(p_idx),
2429: v_deptID => x_resTbls.deptID(p_idx),
2430: v_orgID => p_orgID,
2438: if (l_shifts.shiftNum.count = 0 ) then
2439: /* Fix for bug 5660475: If shifts are not available in the date range,
2440: we should continue to search in the next date range, instead of erroring out. */
2441: if (l_logLevel <= wip_constants.trace_logging) then
2442: wip_logger.log('No shifts found in this period.', l_retStatus);
2443: end if;
2444: l_resourceScheduled := false;
2445: end if;
2446:
2455: l_shifts.endDate(i) := l_shifts.endDate(i) + 1;
2456: end if;
2457: if (l_logLevel <= wip_constants.full_logging) then
2458:
2459: wip_logger.log('**********shiftNum:' || l_shifts.shiftNum(i), l_retStatus);
2460: wip_logger.log('**shift start date:' || to_char(l_shifts.startDate(i), g_logDateFmt), l_retStatus);
2461: wip_logger.log('****shift end date:' || to_char(l_shifts.endDate(i), g_logDateFmt), l_retStatus);
2462: end if;
2463:
2456: end if;
2457: if (l_logLevel <= wip_constants.full_logging) then
2458:
2459: wip_logger.log('**********shiftNum:' || l_shifts.shiftNum(i), l_retStatus);
2460: wip_logger.log('**shift start date:' || to_char(l_shifts.startDate(i), g_logDateFmt), l_retStatus);
2461: wip_logger.log('****shift end date:' || to_char(l_shifts.endDate(i), g_logDateFmt), l_retStatus);
2462: end if;
2463:
2464: --if shift starts after the requested end date, skip it since none of the shift
2457: if (l_logLevel <= wip_constants.full_logging) then
2458:
2459: wip_logger.log('**********shiftNum:' || l_shifts.shiftNum(i), l_retStatus);
2460: wip_logger.log('**shift start date:' || to_char(l_shifts.startDate(i), g_logDateFmt), l_retStatus);
2461: wip_logger.log('****shift end date:' || to_char(l_shifts.endDate(i), g_logDateFmt), l_retStatus);
2462: end if;
2463:
2464: --if shift starts after the requested end date, skip it since none of the shift
2465: --can be used. don't do this in the sql query as it degrades performance
2464: --if shift starts after the requested end date, skip it since none of the shift
2465: --can be used. don't do this in the sql query as it degrades performance
2466: if(l_shifts.startDate(i) > p_endDate) then
2467: if (l_logLevel <= wip_constants.full_logging) then
2468: wip_logger.log('skipping shift (starts after end date)', l_retStatus);
2469: end if;
2470: goto NO_FULFILL_USAGE;--end of loop
2471: end if;
2472:
2472:
2473: --if the shift ends before the end time, adjust the shift length
2474: l_toDate := least(l_shifts.endDate(i), p_endDate);
2475: if (l_logLevel <= wip_constants.full_logging) then
2476: wip_logger.log('calculated end date: ' || to_char(l_toDate, g_logDateFmt), l_retStatus);
2477: end if;
2478:
2479: l_shiftLen := l_toDate - l_shifts.startDate(i);
2480: /*Bug 7015594: If shift start time is same as end time then consider it as 24 hours resource.
2485: end if;
2486:
2487:
2488: if (l_logLevel <= wip_constants.full_logging) then
2489: wip_logger.log('shiftLen(HRS) is ' || round(l_shiftLen*24, g_precision), l_retStatus);
2490: end if;
2491:
2492: if(round(l_shiftLen, g_precision) = 0) then
2493: if (l_logLevel <= wip_constants.full_logging) then
2490: end if;
2491:
2492: if(round(l_shiftLen, g_precision) = 0) then
2493: if (l_logLevel <= wip_constants.full_logging) then
2494: wip_logger.log('skipping shift (no usage)', l_retStatus);
2495: end if;
2496: goto NO_FULFILL_USAGE;--end of loop
2497: end if;
2498:
2499:
2500: if(l_endDate is null) then
2501: l_endDate := l_toDate;
2502: if (l_logLevel <= wip_constants.full_logging) then
2503: wip_logger.log('calculated resource end date:' || to_char(l_endDate, g_logDateFmt), l_retStatus);
2504: end if;
2505: end if;
2506:
2507: if(round(l_remUsage, g_precision) <= round(l_shiftLen, g_precision)) then
2516: x_resTbls.usgCumMinProcTime.extend(1);
2517:
2518: l_usgIdx := x_resTbls.usgStartDate.count;
2519: if (l_logLevel <= wip_constants.full_logging) then
2520: wip_logger.log('start idx is ' || x_resTbls.usgStartIdx(p_idx), l_retStatus);
2521: wip_logger.log('end idx is ' || x_resTbls.usgEndIdx(p_idx), l_retStatus);
2522: wip_logger.log('usg idx is ' || l_usgIdx, l_retStatus);
2523: end if;
2524: x_resTbls.usgStartIdx(p_idx) := nvl(x_resTbls.usgStartIdx(p_idx), l_usgIdx);
2517:
2518: l_usgIdx := x_resTbls.usgStartDate.count;
2519: if (l_logLevel <= wip_constants.full_logging) then
2520: wip_logger.log('start idx is ' || x_resTbls.usgStartIdx(p_idx), l_retStatus);
2521: wip_logger.log('end idx is ' || x_resTbls.usgEndIdx(p_idx), l_retStatus);
2522: wip_logger.log('usg idx is ' || l_usgIdx, l_retStatus);
2523: end if;
2524: x_resTbls.usgStartIdx(p_idx) := nvl(x_resTbls.usgStartIdx(p_idx), l_usgIdx);
2525: x_resTbls.usgEndIdx(p_idx) := l_usgIdx;
2518: l_usgIdx := x_resTbls.usgStartDate.count;
2519: if (l_logLevel <= wip_constants.full_logging) then
2520: wip_logger.log('start idx is ' || x_resTbls.usgStartIdx(p_idx), l_retStatus);
2521: wip_logger.log('end idx is ' || x_resTbls.usgEndIdx(p_idx), l_retStatus);
2522: wip_logger.log('usg idx is ' || l_usgIdx, l_retStatus);
2523: end if;
2524: x_resTbls.usgStartIdx(p_idx) := nvl(x_resTbls.usgStartIdx(p_idx), l_usgIdx);
2525: x_resTbls.usgEndIdx(p_idx) := l_usgIdx;
2526:
2529: x_resTbls.usgStartDate(l_usgIdx) := x_resTbls.startDate(p_idx);
2530: x_resTbls.usgEndDate(l_usgIdx) := l_toDate;--l_shifts.endDate(i);
2531:
2532: if (l_logLevel <= wip_constants.full_logging) then
2533: wip_logger.log('start date is ' || to_char(x_resTbls.startDate(p_idx), g_logDateFmt), l_retStatus);
2534: wip_logger.log('end date is ' || to_char(x_resTbls.endDate(p_idx), g_logDateFmt), l_retStatus);
2535: wip_logger.log('usgIdx:' || l_usgIdx, l_retStatus);
2536: wip_logger.log('usage:' || to_char(x_resTbls.usgStartDate(l_usgIdx), g_logDateFmt) || ' - ' ||
2537: to_char(x_resTbls.usgEndDate(l_usgIdx), g_logDateFmt), l_retStatus);
2530: x_resTbls.usgEndDate(l_usgIdx) := l_toDate;--l_shifts.endDate(i);
2531:
2532: if (l_logLevel <= wip_constants.full_logging) then
2533: wip_logger.log('start date is ' || to_char(x_resTbls.startDate(p_idx), g_logDateFmt), l_retStatus);
2534: wip_logger.log('end date is ' || to_char(x_resTbls.endDate(p_idx), g_logDateFmt), l_retStatus);
2535: wip_logger.log('usgIdx:' || l_usgIdx, l_retStatus);
2536: wip_logger.log('usage:' || to_char(x_resTbls.usgStartDate(l_usgIdx), g_logDateFmt) || ' - ' ||
2537: to_char(x_resTbls.usgEndDate(l_usgIdx), g_logDateFmt), l_retStatus);
2538: end if;
2531:
2532: if (l_logLevel <= wip_constants.full_logging) then
2533: wip_logger.log('start date is ' || to_char(x_resTbls.startDate(p_idx), g_logDateFmt), l_retStatus);
2534: wip_logger.log('end date is ' || to_char(x_resTbls.endDate(p_idx), g_logDateFmt), l_retStatus);
2535: wip_logger.log('usgIdx:' || l_usgIdx, l_retStatus);
2536: wip_logger.log('usage:' || to_char(x_resTbls.usgStartDate(l_usgIdx), g_logDateFmt) || ' - ' ||
2537: to_char(x_resTbls.usgEndDate(l_usgIdx), g_logDateFmt), l_retStatus);
2538: end if;
2539:
2532: if (l_logLevel <= wip_constants.full_logging) then
2533: wip_logger.log('start date is ' || to_char(x_resTbls.startDate(p_idx), g_logDateFmt), l_retStatus);
2534: wip_logger.log('end date is ' || to_char(x_resTbls.endDate(p_idx), g_logDateFmt), l_retStatus);
2535: wip_logger.log('usgIdx:' || l_usgIdx, l_retStatus);
2536: wip_logger.log('usage:' || to_char(x_resTbls.usgStartDate(l_usgIdx), g_logDateFmt) || ' - ' ||
2537: to_char(x_resTbls.usgEndDate(l_usgIdx), g_logDateFmt), l_retStatus);
2538: end if;
2539:
2540: l_resourceScheduled := true; --exit outer loop
2556: --resource consumed until end of the shift
2557: x_resTbls.usgEndDate(l_usgIdx) := l_toDate;
2558:
2559: if (l_logLevel <= wip_constants.full_logging) then
2560: wip_logger.log('exhausted shift. remaining usage(HRS) is ' || round(l_remUsage*24, g_precision), l_retStatus);
2561: wip_logger.log('usage:' || to_char(x_resTbls.usgStartDate(l_usgIdx), g_logDateFmt) || ' - ' ||
2562: to_char(x_resTbls.usgEndDate(l_usgIdx), g_logDateFmt), l_retStatus);
2563: wip_logger.log('usgIdx:' || l_usgIdx, l_retStatus);
2564: end if;
2557: x_resTbls.usgEndDate(l_usgIdx) := l_toDate;
2558:
2559: if (l_logLevel <= wip_constants.full_logging) then
2560: wip_logger.log('exhausted shift. remaining usage(HRS) is ' || round(l_remUsage*24, g_precision), l_retStatus);
2561: wip_logger.log('usage:' || to_char(x_resTbls.usgStartDate(l_usgIdx), g_logDateFmt) || ' - ' ||
2562: to_char(x_resTbls.usgEndDate(l_usgIdx), g_logDateFmt), l_retStatus);
2563: wip_logger.log('usgIdx:' || l_usgIdx, l_retStatus);
2564: end if;
2565: end if;
2559: if (l_logLevel <= wip_constants.full_logging) then
2560: wip_logger.log('exhausted shift. remaining usage(HRS) is ' || round(l_remUsage*24, g_precision), l_retStatus);
2561: wip_logger.log('usage:' || to_char(x_resTbls.usgStartDate(l_usgIdx), g_logDateFmt) || ' - ' ||
2562: to_char(x_resTbls.usgEndDate(l_usgIdx), g_logDateFmt), l_retStatus);
2563: wip_logger.log('usgIdx:' || l_usgIdx, l_retStatus);
2564: end if;
2565: end if;
2566: <
2567: null;
2573:
2574: --if the next start date is after the end of the calendar, then we can't schedule anything
2575: if(l_cursorEndDate < p_minDate) then
2576: if (l_logLevel <= wip_constants.full_logging) then
2577: wip_logger.log('exhausted calendar. remaining usage(HRS) is ' || round(l_remUsage*24, g_precision), l_retStatus);
2578: end if;
2579: fnd_message.set_name('WIP', 'WIP_NO_CALENDAR');
2580: fnd_msg_pub.add;
2581: raise fnd_api.g_exc_unexpected_error;
2586: --resource usages are in reverse chronological order. Flip them so they go start to end.
2587: i := x_resTbls.usgStartIdx(p_idx);
2588: j := x_resTbls.usgEndIdx(p_idx);
2589: if (l_logLevel <= wip_constants.full_logging) then
2590: wip_logger.log('i: ' || i || '; j: ' || j, l_retStatus);
2591: end if;
2592:
2593: while(j > i) loop
2594: l_tmpUsgStartDate := x_resTbls.usgStartDate(j);
2605: l_prevProcTime := x_resTbls.usgCumMinProcTime(i);
2606: end loop;
2607:
2608: if(l_logLevel <= wip_constants.trace_logging) then
2609: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.backwardSchResource',
2610: p_procReturnStatus => null,
2611: p_msg => 'success',
2612: x_returnStatus => l_retStatus);
2613: end if;
2614: exception
2615: when fnd_api.g_exc_unexpected_error then
2616: x_returnStatus := fnd_api.g_ret_sts_unexp_error;
2617: if(l_logLevel <= wip_constants.trace_logging) then
2618: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.backwardSchResource',
2619: p_procReturnStatus => x_returnStatus,
2620: p_msg => 'error',
2621: x_returnStatus => l_retStatus);
2622: end if;
2626: p_procedure_name => 'backwardSchResource',
2627: p_error_text => SQLERRM);
2628:
2629: if(l_logLevel <= wip_constants.trace_logging) then
2630: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.backwardSchResource',
2631: p_procReturnStatus => x_returnStatus,
2632: p_msg => 'unexp error:' || SQLERRM,
2633: x_returnStatus => l_retStatus);
2634: end if;
2643: x_returnStatus out nocopy varchar2) is
2644: j number;
2645: l_minStartDate DATE;
2646: l_logLevel number := fnd_log.g_current_runtime_level;
2647: l_params wip_logger.param_tbl_t;
2648: l_retStatus VARCHAR2(1);
2649: begin
2650: if (l_logLevel <= wip_constants.trace_logging) then
2651: l_params(1).paramName := 'p_orgID';
2660: l_params(5).paramValue := p_schedFlag;
2661: l_params(6).paramName := 'p_startIdx';
2662: l_params(6).paramValue := p_startIdx;
2663:
2664: wip_logger.entryPoint(p_procName => 'wip_infResSched_grp.forwardScheduleBatch',
2665: p_params => l_params,
2666: x_returnStatus => x_returnStatus);
2667: end if;
2668:
2675: x_resTbls.opSeqNum(j) = x_resTbls.opSeqNum(p_startIdx)) then
2676: --calculate min start date
2677: l_minStartDate := least(l_minStartDate, x_resTbls.startDate(j));
2678: if(l_logLevel <= wip_constants.full_logging) then
2679: wip_logger.log('resID:' || x_resTbls.resID(j), l_retStatus);
2680: wip_logger.log('res start date:' || to_char(x_resTbls.startDate(j), g_logDateFmt), l_retStatus);
2681: wip_logger.log('min start date:' || to_char(l_minStartDate, g_logDateFmt), l_retStatus);
2682: end if;
2683: --clear backward scheduled times
2676: --calculate min start date
2677: l_minStartDate := least(l_minStartDate, x_resTbls.startDate(j));
2678: if(l_logLevel <= wip_constants.full_logging) then
2679: wip_logger.log('resID:' || x_resTbls.resID(j), l_retStatus);
2680: wip_logger.log('res start date:' || to_char(x_resTbls.startDate(j), g_logDateFmt), l_retStatus);
2681: wip_logger.log('min start date:' || to_char(l_minStartDate, g_logDateFmt), l_retStatus);
2682: end if;
2683: --clear backward scheduled times
2684: x_resTbls.usgStartIdx(j) := null;
2677: l_minStartDate := least(l_minStartDate, x_resTbls.startDate(j));
2678: if(l_logLevel <= wip_constants.full_logging) then
2679: wip_logger.log('resID:' || x_resTbls.resID(j), l_retStatus);
2680: wip_logger.log('res start date:' || to_char(x_resTbls.startDate(j), g_logDateFmt), l_retStatus);
2681: wip_logger.log('min start date:' || to_char(l_minStartDate, g_logDateFmt), l_retStatus);
2682: end if;
2683: --clear backward scheduled times
2684: x_resTbls.usgStartIdx(j) := null;
2685: x_resTbls.usgEndIdx(j) := null;
2696: x_resTbls => x_resTbls,
2697: x_returnStatus => x_returnStatus);
2698:
2699: if(l_logLevel <= wip_constants.trace_logging) then
2700: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.forwardScheduleBatch',
2701: p_procReturnStatus => x_returnStatus,
2702: p_msg => 'success?',
2703: x_returnStatus => l_retStatus);
2704: end if;
2713: x_resTbls in out NOCOPY OP_RES_RECTBL_T,
2714: x_returnStatus OUT NOCOPY VARCHAR2) is
2715: l_resEndDate DATE;
2716: l_logLevel NUMBER := fnd_log.g_current_runtime_level;
2717: l_params wip_logger.param_tbl_t;
2718: l_retStatus VARCHAR2(1);
2719: l_fromTime NUMBER;
2720: l_minDate DATE;
2721: l_shiftStartDate DATE;
2740: l_params(5).paramValue := p_range(2);
2741: l_params(6).paramName := 'p_schedFlag';
2742: l_params(6).paramValue := p_schedFlag;
2743:
2744: wip_logger.entryPoint(p_procName => 'wip_infResSched_grp.backwardSchedule',
2745: p_params => l_params,
2746: x_returnStatus => x_returnStatus);
2747: end if;
2748: --get the maximum date
2754: and mp.calendar_code = bc.calendar_code;
2755:
2756: while(getNextResIdx(p_range, p_schedFlag, g_backward, x_resTbls, i)) loop
2757: if (l_logLevel <= wip_constants.full_logging) then
2758: wip_logger.log('begin scheduling resource:' || x_resTbls.resID(i), l_retStatus);
2759: wip_logger.log(' operation:' || x_resTbls.opSeqNum(i), l_retStatus);
2760: wip_logger.log(' schedule seq num:' || x_resTbls.schedSeqNum(i), l_retStatus);
2761: wip_logger.log(' sched flag:' || x_resTbls.schedFlag(i), l_retStatus);
2762: wip_logger.log(' total usage (HRS):' || round(x_resTbls.totalDaysUsg(i)*24, g_precision), l_retStatus);
2755:
2756: while(getNextResIdx(p_range, p_schedFlag, g_backward, x_resTbls, i)) loop
2757: if (l_logLevel <= wip_constants.full_logging) then
2758: wip_logger.log('begin scheduling resource:' || x_resTbls.resID(i), l_retStatus);
2759: wip_logger.log(' operation:' || x_resTbls.opSeqNum(i), l_retStatus);
2760: wip_logger.log(' schedule seq num:' || x_resTbls.schedSeqNum(i), l_retStatus);
2761: wip_logger.log(' sched flag:' || x_resTbls.schedFlag(i), l_retStatus);
2762: wip_logger.log(' total usage (HRS):' || round(x_resTbls.totalDaysUsg(i)*24, g_precision), l_retStatus);
2763:
2756: while(getNextResIdx(p_range, p_schedFlag, g_backward, x_resTbls, i)) loop
2757: if (l_logLevel <= wip_constants.full_logging) then
2758: wip_logger.log('begin scheduling resource:' || x_resTbls.resID(i), l_retStatus);
2759: wip_logger.log(' operation:' || x_resTbls.opSeqNum(i), l_retStatus);
2760: wip_logger.log(' schedule seq num:' || x_resTbls.schedSeqNum(i), l_retStatus);
2761: wip_logger.log(' sched flag:' || x_resTbls.schedFlag(i), l_retStatus);
2762: wip_logger.log(' total usage (HRS):' || round(x_resTbls.totalDaysUsg(i)*24, g_precision), l_retStatus);
2763:
2764: if(l_prevResIdx is not null) then
2757: if (l_logLevel <= wip_constants.full_logging) then
2758: wip_logger.log('begin scheduling resource:' || x_resTbls.resID(i), l_retStatus);
2759: wip_logger.log(' operation:' || x_resTbls.opSeqNum(i), l_retStatus);
2760: wip_logger.log(' schedule seq num:' || x_resTbls.schedSeqNum(i), l_retStatus);
2761: wip_logger.log(' sched flag:' || x_resTbls.schedFlag(i), l_retStatus);
2762: wip_logger.log(' total usage (HRS):' || round(x_resTbls.totalDaysUsg(i)*24, g_precision), l_retStatus);
2763:
2764: if(l_prevResIdx is not null) then
2765: wip_logger.log('prev sched seq num is:' || x_resTbls.schedSeqNum(l_prevResIdx), l_retStatus);
2758: wip_logger.log('begin scheduling resource:' || x_resTbls.resID(i), l_retStatus);
2759: wip_logger.log(' operation:' || x_resTbls.opSeqNum(i), l_retStatus);
2760: wip_logger.log(' schedule seq num:' || x_resTbls.schedSeqNum(i), l_retStatus);
2761: wip_logger.log(' sched flag:' || x_resTbls.schedFlag(i), l_retStatus);
2762: wip_logger.log(' total usage (HRS):' || round(x_resTbls.totalDaysUsg(i)*24, g_precision), l_retStatus);
2763:
2764: if(l_prevResIdx is not null) then
2765: wip_logger.log('prev sched seq num is:' || x_resTbls.schedSeqNum(l_prevResIdx), l_retStatus);
2766: wip_logger.log('prev op seq num is:' || x_resTbls.opSeqNum(l_prevResIdx), l_retStatus);
2761: wip_logger.log(' sched flag:' || x_resTbls.schedFlag(i), l_retStatus);
2762: wip_logger.log(' total usage (HRS):' || round(x_resTbls.totalDaysUsg(i)*24, g_precision), l_retStatus);
2763:
2764: if(l_prevResIdx is not null) then
2765: wip_logger.log('prev sched seq num is:' || x_resTbls.schedSeqNum(l_prevResIdx), l_retStatus);
2766: wip_logger.log('prev op seq num is:' || x_resTbls.opSeqNum(l_prevResIdx), l_retStatus);
2767: end if;
2768: end if;
2769:
2762: wip_logger.log(' total usage (HRS):' || round(x_resTbls.totalDaysUsg(i)*24, g_precision), l_retStatus);
2763:
2764: if(l_prevResIdx is not null) then
2765: wip_logger.log('prev sched seq num is:' || x_resTbls.schedSeqNum(l_prevResIdx), l_retStatus);
2766: wip_logger.log('prev op seq num is:' || x_resTbls.opSeqNum(l_prevResIdx), l_retStatus);
2767: end if;
2768: end if;
2769:
2770: l_doneSchedBatch := false;
2773: x_resTbls.schedSeqNum(i) = x_resTbls.schedSeqNum(l_prevResIdx) and
2774: x_resTbls.opSeqNum(i) = x_resTbls.opSeqNum(l_prevResIdx)) then
2775: l_schedulingBatch := true;
2776: if(l_logLevel <= wip_constants.full_logging) then
2777: wip_logger.log('starting batch', l_retStatus);
2778: end if;
2779:
2780: --just finished scheduling batch
2781: elsif(l_schedulingBatch) then
2779:
2780: --just finished scheduling batch
2781: elsif(l_schedulingBatch) then
2782: if(l_logLevel <= wip_constants.full_logging) then
2783: wip_logger.log('done bkwd scheduling batch, now fwd sched', l_retStatus);
2784: end if;
2785:
2786: l_schedulingBatch := false;
2787: l_doneSchedBatch := true;
2794: x_resTbls => x_resTbls,
2795: x_returnStatus => x_returnStatus);
2796: if(x_returnStatus <> fnd_api.g_ret_sts_success) then
2797: if(l_logLevel <= wip_constants.full_logging) then
2798: wip_logger.log('simult batch scheduling failed', l_retStatus);
2799: end if;
2800: raise fnd_api.g_exc_unexpected_error;
2801: end if;
2802: end if;
2811:
2812: backwardSchResource(p_orgID, l_resEndDate, l_minDate, i, p_repLineID, x_resTbls, x_returnStatus);
2813: if(x_returnStatus <> fnd_api.g_ret_sts_success) then
2814: if(l_logLevel <= wip_constants.full_logging) then
2815: wip_logger.log('backward schedule failed', l_retStatus);
2816: end if;
2817: raise fnd_api.g_exc_unexpected_error;
2818: end if;
2819:
2821: end loop;
2822:
2823: if(l_schedulingBatch) then
2824: if(l_logLevel <= wip_constants.full_logging) then
2825: wip_logger.log('done bkwd scheduling last batch, now fwd sched', l_retStatus);
2826: end if;
2827: forwardScheduleBatch(p_orgID => p_orgID,
2828: p_repLineID => p_repLineID,
2829: p_range => p_range,
2832: x_resTbls => x_resTbls,
2833: x_returnStatus => x_returnStatus);
2834: if(x_returnStatus <> fnd_api.g_ret_sts_success) then
2835: if(l_logLevel <= wip_constants.full_logging) then
2836: wip_logger.log('final simult batch scheduling failed', l_retStatus);
2837: end if;
2838: raise fnd_api.g_exc_unexpected_error;
2839: end if;
2840: end if;
2839: end if;
2840: end if;
2841:
2842: if(l_logLevel <= wip_constants.trace_logging) then
2843: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.backwardSchedule',
2844: p_procReturnStatus => x_returnStatus,
2845: p_msg => 'success',
2846: x_returnStatus => l_retStatus);
2847: end if;
2849: exception
2850: when fnd_api.g_exc_unexpected_error then
2851: x_returnStatus := fnd_api.g_ret_sts_unexp_error;
2852: if(l_logLevel <= wip_constants.trace_logging) then
2853: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.backwardSchedule',
2854: p_procReturnStatus => x_returnStatus,
2855: p_msg => 'error',
2856: x_returnStatus => l_retStatus);
2857: end if;
2861: p_procedure_name => 'backwardSchedule',
2862: p_error_text => SQLERRM);
2863:
2864: if(l_logLevel <= wip_constants.trace_logging) then
2865: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.backwardSchedule',
2866: p_procReturnStatus => x_returnStatus,
2867: p_msg => 'unexp error:' || SQLERRM,
2868: x_returnStatus => l_retStatus);
2869: end if;
2875: p_opTbl in op_tbl_t,
2876: x_resTbls IN OUT NOCOPY OP_RES_RECTBL_T,
2877: x_returnStatus OUT NOCOPY VARCHAR2) is
2878: l_retStatus VARCHAR2(1);
2879: l_params wip_logger.param_tbl_t;
2880: l_logLevel NUMBER := fnd_log.g_current_runtime_level;
2881: begin
2882: if(l_logLevel <= wip_constants.trace_logging) then
2883: l_params(1).paramName := 'p_orgID';
2884: l_params(1).paramValue := p_orgID;
2885: l_params(2).paramName := 'p_repLineID';
2886: l_params(2).paramValue := p_repLineID;
2887:
2888: wip_logger.entryPoint(p_procName => 'wip_infResSched_grp.schedulePriorResources',
2889: p_params => l_params,
2890: x_returnStatus => l_retStatus);
2891: end if;
2892:
2907: end if;
2908: end loop;
2909:
2910: if(l_logLevel <= wip_constants.trace_logging) then
2911: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.schedulePriorResources',
2912: p_procReturnStatus => x_returnStatus,
2913: p_msg => 'success',
2914: x_returnStatus => l_retStatus);
2915: end if;
2916: exception
2917: when fnd_api.g_exc_unexpected_error then
2918: x_returnStatus := fnd_api.g_ret_sts_unexp_error;
2919: if(l_logLevel <= wip_constants.trace_logging) then
2920: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.schedulePriorResources',
2921: p_procReturnStatus => x_returnStatus,
2922: p_msg => 'backward scheduling failed',
2923: x_returnStatus => l_retStatus);
2924: end if;
2924: end if;
2925: when others then
2926: x_returnStatus := fnd_api.g_ret_sts_unexp_error;
2927: if(l_logLevel <= wip_constants.trace_logging) then
2928: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.schedulePriorResources',
2929: p_procReturnStatus => x_returnStatus,
2930: p_msg => 'unexp error:' || SQLERRM,
2931: x_returnStatus => l_retStatus);
2932: end if;
2938: x_resTbls IN OUT NOCOPY OP_RES_RECTBL_T,
2939: x_returnStatus OUT NOCOPY VARCHAR2) is
2940:
2941: l_retStatus VARCHAR2(1);
2942: l_params wip_logger.param_tbl_t;
2943: l_logLevel NUMBER := fnd_log.g_current_runtime_level;
2944: begin
2945: if(l_logLevel <= wip_constants.trace_logging) then
2946: l_params(1).paramName := 'p_orgID';
2947: l_params(1).paramValue := p_orgID;
2948: l_params(2).paramName := 'p_repLineID';
2949: l_params(2).paramValue := p_repLineID;
2950:
2951: wip_logger.entryPoint(p_procName => 'wip_infResSched_grp.scheduleNextResources',
2952: p_params => l_params,
2953: x_returnStatus => l_retStatus);
2954: end if;
2955:
2977: end if;
2978: end loop;
2979:
2980: if(l_logLevel <= wip_constants.trace_logging) then
2981: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.scheduleNextResources',
2982: p_procReturnStatus => x_returnStatus,
2983: p_msg => 'success',
2984: x_returnStatus => l_retStatus);
2985: end if;
2986: exception
2987: when fnd_api.g_exc_unexpected_error then
2988: x_returnStatus := fnd_api.g_ret_sts_unexp_error;
2989: if(l_logLevel <= wip_constants.trace_logging) then
2990: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.scheduleNextResources',
2991: p_procReturnStatus => x_returnStatus,
2992: p_msg => 'backward scheduling failed',
2993: x_returnStatus => l_retStatus);
2994: end if;
2998: p_procedure_name => 'scheduleNextResources',
2999: p_error_text => SQLERRM);
3000:
3001: if(l_logLevel <= wip_constants.trace_logging) then
3002: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.scheduleNextResources',
3003: p_procReturnStatus => x_returnStatus,
3004: p_msg => 'unexp error:' || SQLERRM,
3005: x_returnStatus => l_retStatus);
3006: end if;
3012: p_startDate IN DATE,
3013: x_resTbls IN OUT NOCOPY OP_RES_RECTBL_T,
3014: x_returnStatus OUT NOCOPY VARCHAR2) is
3015: l_logLevel NUMBER := fnd_log.g_current_runtime_level;
3016: l_params wip_logger.param_tbl_t;
3017: l_retStatus VARCHAR2(1);
3018: l_opSeqNum NUMBER := null;
3019: l_opStartDate DATE;
3020: l_count NUMBER;
3031: l_params(2).paramName := 'p_repLineID';
3032: l_params(2).paramValue := p_repLineID;
3033: l_params(3).paramName := 'p_startDate';
3034: l_params(3).paramValue := to_char(p_startDate, g_logDateFmt);
3035: wip_logger.entryPoint(p_procName => 'wip_infResSched_grp.resolvePriorExceptions',
3036: p_params => l_params,
3037: x_returnStatus => x_returnStatus);
3038: end if;
3039: --this loop finds an exception
3137: end if;
3138: end if;
3139:
3140: if(l_logLevel <= wip_constants.trace_logging) then
3141: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.resolvePriorExceptions',
3142: p_procReturnStatus => x_returnStatus,
3143: p_msg => 'success.',
3144: x_returnStatus => l_retStatus);
3145: end if;
3146: exception
3147: when fnd_api.g_exc_unexpected_error then
3148: x_returnStatus := fnd_api.g_ret_sts_unexp_error;
3149: if(l_logLevel <= wip_constants.trace_logging) then
3150: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.resolvePriorExceptions',
3151: p_procReturnStatus => x_returnStatus,
3152: p_msg => 'errmsg: ' || l_errMsg,
3153: x_returnStatus => l_retStatus);
3154: end if;
3158: p_procedure_name => 'resolvePriorExceptions',
3159: p_error_text => SQLERRM);
3160:
3161: if(l_logLevel <= wip_constants.trace_logging) then
3162: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.resolvePriorExceptions',
3163: p_procReturnStatus => x_returnStatus,
3164: p_msg => 'unexp error:' || SQLERRM,
3165: x_returnStatus => l_retStatus);
3166: end if;
3171: p_endDate IN DATE,
3172: x_resTbls IN OUT NOCOPY OP_RES_RECTBL_T,
3173: x_returnStatus OUT NOCOPY VARCHAR2) is
3174: l_logLevel NUMBER := fnd_log.g_current_runtime_level;
3175: l_params wip_logger.param_tbl_t;
3176: l_retStatus VARCHAR2(1);
3177: l_opSeqNum NUMBER := null;
3178: l_opEndDate DATE;
3179: l_count NUMBER;
3190: l_params(2).paramName := 'p_repLineID';
3191: l_params(2).paramValue := p_repLineID;
3192: l_params(3).paramName := 'p_endDate';
3193: l_params(3).paramValue := to_char(p_endDate, g_logDateFmt);
3194: wip_logger.entryPoint(p_procName => 'wip_infResSched_grp.resolveNextExceptions',
3195: p_params => l_params,
3196: x_returnStatus => x_returnStatus);
3197: end if;
3198: --this loop finds an exception
3213: end if;
3214: l_opRange(1) := i;
3215: l_exceptionExists := x_resTbls.endDate(i) > p_endDate;
3216: if(l_logLevel <= wip_constants.full_logging) then
3217: wip_logger.log('res end date: ' || to_char(x_resTbls.endDate(i), g_logDateFmt), l_retStatus);
3218: wip_logger.log('job end date: ' || to_char(p_endDate, g_logDateFmt), l_retStatus);
3219: end if;
3220: end if;
3221: end loop;
3214: l_opRange(1) := i;
3215: l_exceptionExists := x_resTbls.endDate(i) > p_endDate;
3216: if(l_logLevel <= wip_constants.full_logging) then
3217: wip_logger.log('res end date: ' || to_char(x_resTbls.endDate(i), g_logDateFmt), l_retStatus);
3218: wip_logger.log('job end date: ' || to_char(p_endDate, g_logDateFmt), l_retStatus);
3219: end if;
3220: end if;
3221: end loop;
3222:
3222:
3223: --found a prior resource whose start date is earlier than job start...
3224: if(l_exceptionExists) then
3225: if(l_logLevel <= wip_constants.full_logging) then
3226: wip_logger.log('found exception', l_retStatus);
3227: end if;
3228: i := null;
3229: --going to reschedule entire job
3230: --delete usages
3301: end if;
3302: end if;
3303:
3304: if(l_logLevel <= wip_constants.trace_logging) then
3305: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.resolveNextExceptions',
3306: p_procReturnStatus => x_returnStatus,
3307: p_msg => 'success.',
3308: x_returnStatus => l_retStatus);
3309: end if;
3310: exception
3311: when fnd_api.g_exc_unexpected_error then
3312: x_returnStatus := fnd_api.g_ret_sts_unexp_error;
3313: if(l_logLevel <= wip_constants.trace_logging) then
3314: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.resolveNextExceptions',
3315: p_procReturnStatus => x_returnStatus,
3316: p_msg => l_errMsg,
3317: x_returnStatus => l_retStatus);
3318: end if;
3322: p_procedure_name => 'resolveNextExceptions',
3323: p_error_text => SQLERRM);
3324:
3325: if(l_logLevel <= wip_constants.trace_logging) then
3326: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.resolveNextExceptions',
3327: p_procReturnStatus => x_returnStatus,
3328: p_msg => 'unexp error:' || SQLERRM,
3329: x_returnStatus => l_retStatus);
3330: end if;
3334:
3335:
3336: procedure dumpResources(p_resTbls IN OP_RES_RECTBL_T) IS
3337: l_logLevel NUMBER := fnd_log.g_current_runtime_level;
3338: l_params wip_logger.param_tbl_t;
3339: l_retStatus VARCHAR2(1);
3340: l_resCode VARCHAR2(10);
3341: begin
3342:
3340: l_resCode VARCHAR2(10);
3341: begin
3342:
3343: if (l_logLevel <= wip_constants.trace_logging) then
3344: wip_logger.entryPoint(p_procName => 'wip_infResSched_grp.dumpResources',
3345: p_params => l_params,
3346: x_returnStatus => l_retStatus);
3347: end if;
3348:
3347: end if;
3348:
3349: if(p_resTbls.resID is null or p_resTbls.resID.count < 1 and
3350: l_logLevel <= wip_constants.trace_logging) then
3351: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.dumpResources',
3352: p_procReturnStatus => null,
3353: p_msg => 'no resources in table!',
3354: x_returnStatus => l_retStatus);
3355: return;
3360: select resource_code
3361: into l_resCode
3362: from bom_resources
3363: where resource_id = p_resTbls.resID(i);
3364: wip_logger.log('res:' || l_resCode || '(' || p_resTbls.resID(i) || ')', l_retStatus);
3365: wip_logger.log('+ usage (HRS):' || round(p_resTbls.totalDaysUsg(i)*24, 6),l_retStatus);
3366: wip_logger.log('+ operation:' || p_resTbls.opSeqNum(i), l_retStatus);
3367: wip_logger.log('+ department:' || p_resTbls.deptID(i), l_retStatus);
3368: wip_logger.log('+ sched seq:' || p_resTbls.schedSeqNum(i), l_retStatus);
3361: into l_resCode
3362: from bom_resources
3363: where resource_id = p_resTbls.resID(i);
3364: wip_logger.log('res:' || l_resCode || '(' || p_resTbls.resID(i) || ')', l_retStatus);
3365: wip_logger.log('+ usage (HRS):' || round(p_resTbls.totalDaysUsg(i)*24, 6),l_retStatus);
3366: wip_logger.log('+ operation:' || p_resTbls.opSeqNum(i), l_retStatus);
3367: wip_logger.log('+ department:' || p_resTbls.deptID(i), l_retStatus);
3368: wip_logger.log('+ sched seq:' || p_resTbls.schedSeqNum(i), l_retStatus);
3369: wip_logger.log('+ res seq:' || p_resTbls.resSeqNum(i), l_retStatus);
3362: from bom_resources
3363: where resource_id = p_resTbls.resID(i);
3364: wip_logger.log('res:' || l_resCode || '(' || p_resTbls.resID(i) || ')', l_retStatus);
3365: wip_logger.log('+ usage (HRS):' || round(p_resTbls.totalDaysUsg(i)*24, 6),l_retStatus);
3366: wip_logger.log('+ operation:' || p_resTbls.opSeqNum(i), l_retStatus);
3367: wip_logger.log('+ department:' || p_resTbls.deptID(i), l_retStatus);
3368: wip_logger.log('+ sched seq:' || p_resTbls.schedSeqNum(i), l_retStatus);
3369: wip_logger.log('+ res seq:' || p_resTbls.resSeqNum(i), l_retStatus);
3370: wip_logger.log('+ sched flag:' || p_resTbls.schedFlag(i), l_retStatus);
3363: where resource_id = p_resTbls.resID(i);
3364: wip_logger.log('res:' || l_resCode || '(' || p_resTbls.resID(i) || ')', l_retStatus);
3365: wip_logger.log('+ usage (HRS):' || round(p_resTbls.totalDaysUsg(i)*24, 6),l_retStatus);
3366: wip_logger.log('+ operation:' || p_resTbls.opSeqNum(i), l_retStatus);
3367: wip_logger.log('+ department:' || p_resTbls.deptID(i), l_retStatus);
3368: wip_logger.log('+ sched seq:' || p_resTbls.schedSeqNum(i), l_retStatus);
3369: wip_logger.log('+ res seq:' || p_resTbls.resSeqNum(i), l_retStatus);
3370: wip_logger.log('+ sched flag:' || p_resTbls.schedFlag(i), l_retStatus);
3371: wip_logger.log('+ 24hrs flag:' || p_resTbls.avail24Flag(i), l_retStatus);
3364: wip_logger.log('res:' || l_resCode || '(' || p_resTbls.resID(i) || ')', l_retStatus);
3365: wip_logger.log('+ usage (HRS):' || round(p_resTbls.totalDaysUsg(i)*24, 6),l_retStatus);
3366: wip_logger.log('+ operation:' || p_resTbls.opSeqNum(i), l_retStatus);
3367: wip_logger.log('+ department:' || p_resTbls.deptID(i), l_retStatus);
3368: wip_logger.log('+ sched seq:' || p_resTbls.schedSeqNum(i), l_retStatus);
3369: wip_logger.log('+ res seq:' || p_resTbls.resSeqNum(i), l_retStatus);
3370: wip_logger.log('+ sched flag:' || p_resTbls.schedFlag(i), l_retStatus);
3371: wip_logger.log('+ 24hrs flag:' || p_resTbls.avail24Flag(i), l_retStatus);
3372: wip_logger.log('+ start date:' || to_char(p_resTbls.startDate(i), g_logDateFmt), l_retStatus);
3365: wip_logger.log('+ usage (HRS):' || round(p_resTbls.totalDaysUsg(i)*24, 6),l_retStatus);
3366: wip_logger.log('+ operation:' || p_resTbls.opSeqNum(i), l_retStatus);
3367: wip_logger.log('+ department:' || p_resTbls.deptID(i), l_retStatus);
3368: wip_logger.log('+ sched seq:' || p_resTbls.schedSeqNum(i), l_retStatus);
3369: wip_logger.log('+ res seq:' || p_resTbls.resSeqNum(i), l_retStatus);
3370: wip_logger.log('+ sched flag:' || p_resTbls.schedFlag(i), l_retStatus);
3371: wip_logger.log('+ 24hrs flag:' || p_resTbls.avail24Flag(i), l_retStatus);
3372: wip_logger.log('+ start date:' || to_char(p_resTbls.startDate(i), g_logDateFmt), l_retStatus);
3373: wip_logger.log('+ end date:' || to_char(p_resTbls.endDate(i), g_logDateFmt), l_retStatus);
3366: wip_logger.log('+ operation:' || p_resTbls.opSeqNum(i), l_retStatus);
3367: wip_logger.log('+ department:' || p_resTbls.deptID(i), l_retStatus);
3368: wip_logger.log('+ sched seq:' || p_resTbls.schedSeqNum(i), l_retStatus);
3369: wip_logger.log('+ res seq:' || p_resTbls.resSeqNum(i), l_retStatus);
3370: wip_logger.log('+ sched flag:' || p_resTbls.schedFlag(i), l_retStatus);
3371: wip_logger.log('+ 24hrs flag:' || p_resTbls.avail24Flag(i), l_retStatus);
3372: wip_logger.log('+ start date:' || to_char(p_resTbls.startDate(i), g_logDateFmt), l_retStatus);
3373: wip_logger.log('+ end date:' || to_char(p_resTbls.endDate(i), g_logDateFmt), l_retStatus);
3374: wip_logger.log('+ usg st idx:' || p_resTbls.usgStartIdx(i), l_retStatus);
3367: wip_logger.log('+ department:' || p_resTbls.deptID(i), l_retStatus);
3368: wip_logger.log('+ sched seq:' || p_resTbls.schedSeqNum(i), l_retStatus);
3369: wip_logger.log('+ res seq:' || p_resTbls.resSeqNum(i), l_retStatus);
3370: wip_logger.log('+ sched flag:' || p_resTbls.schedFlag(i), l_retStatus);
3371: wip_logger.log('+ 24hrs flag:' || p_resTbls.avail24Flag(i), l_retStatus);
3372: wip_logger.log('+ start date:' || to_char(p_resTbls.startDate(i), g_logDateFmt), l_retStatus);
3373: wip_logger.log('+ end date:' || to_char(p_resTbls.endDate(i), g_logDateFmt), l_retStatus);
3374: wip_logger.log('+ usg st idx:' || p_resTbls.usgStartIdx(i), l_retStatus);
3375: wip_logger.log('+ usg end idx:' || p_resTbls.usgEndIdx(i), l_retStatus);
3368: wip_logger.log('+ sched seq:' || p_resTbls.schedSeqNum(i), l_retStatus);
3369: wip_logger.log('+ res seq:' || p_resTbls.resSeqNum(i), l_retStatus);
3370: wip_logger.log('+ sched flag:' || p_resTbls.schedFlag(i), l_retStatus);
3371: wip_logger.log('+ 24hrs flag:' || p_resTbls.avail24Flag(i), l_retStatus);
3372: wip_logger.log('+ start date:' || to_char(p_resTbls.startDate(i), g_logDateFmt), l_retStatus);
3373: wip_logger.log('+ end date:' || to_char(p_resTbls.endDate(i), g_logDateFmt), l_retStatus);
3374: wip_logger.log('+ usg st idx:' || p_resTbls.usgStartIdx(i), l_retStatus);
3375: wip_logger.log('+ usg end idx:' || p_resTbls.usgEndIdx(i), l_retStatus);
3376: if(p_resTbls.usgStartIdx(i) is not null) then
3369: wip_logger.log('+ res seq:' || p_resTbls.resSeqNum(i), l_retStatus);
3370: wip_logger.log('+ sched flag:' || p_resTbls.schedFlag(i), l_retStatus);
3371: wip_logger.log('+ 24hrs flag:' || p_resTbls.avail24Flag(i), l_retStatus);
3372: wip_logger.log('+ start date:' || to_char(p_resTbls.startDate(i), g_logDateFmt), l_retStatus);
3373: wip_logger.log('+ end date:' || to_char(p_resTbls.endDate(i), g_logDateFmt), l_retStatus);
3374: wip_logger.log('+ usg st idx:' || p_resTbls.usgStartIdx(i), l_retStatus);
3375: wip_logger.log('+ usg end idx:' || p_resTbls.usgEndIdx(i), l_retStatus);
3376: if(p_resTbls.usgStartIdx(i) is not null) then
3377: for j in p_resTbls.usgStartIdx(i)..p_resTbls.usgEndIdx(i) loop
3370: wip_logger.log('+ sched flag:' || p_resTbls.schedFlag(i), l_retStatus);
3371: wip_logger.log('+ 24hrs flag:' || p_resTbls.avail24Flag(i), l_retStatus);
3372: wip_logger.log('+ start date:' || to_char(p_resTbls.startDate(i), g_logDateFmt), l_retStatus);
3373: wip_logger.log('+ end date:' || to_char(p_resTbls.endDate(i), g_logDateFmt), l_retStatus);
3374: wip_logger.log('+ usg st idx:' || p_resTbls.usgStartIdx(i), l_retStatus);
3375: wip_logger.log('+ usg end idx:' || p_resTbls.usgEndIdx(i), l_retStatus);
3376: if(p_resTbls.usgStartIdx(i) is not null) then
3377: for j in p_resTbls.usgStartIdx(i)..p_resTbls.usgEndIdx(i) loop
3378: wip_logger.log(' + usage start date:' || to_char(p_resTbls.usgStartDate(j), g_logDateFmt),l_retStatus);
3371: wip_logger.log('+ 24hrs flag:' || p_resTbls.avail24Flag(i), l_retStatus);
3372: wip_logger.log('+ start date:' || to_char(p_resTbls.startDate(i), g_logDateFmt), l_retStatus);
3373: wip_logger.log('+ end date:' || to_char(p_resTbls.endDate(i), g_logDateFmt), l_retStatus);
3374: wip_logger.log('+ usg st idx:' || p_resTbls.usgStartIdx(i), l_retStatus);
3375: wip_logger.log('+ usg end idx:' || p_resTbls.usgEndIdx(i), l_retStatus);
3376: if(p_resTbls.usgStartIdx(i) is not null) then
3377: for j in p_resTbls.usgStartIdx(i)..p_resTbls.usgEndIdx(i) loop
3378: wip_logger.log(' + usage start date:' || to_char(p_resTbls.usgStartDate(j), g_logDateFmt),l_retStatus);
3379: wip_logger.log(' + usage end date:' || to_char(p_resTbls.usgEndDate(j), g_logDateFmt),l_retStatus);
3374: wip_logger.log('+ usg st idx:' || p_resTbls.usgStartIdx(i), l_retStatus);
3375: wip_logger.log('+ usg end idx:' || p_resTbls.usgEndIdx(i), l_retStatus);
3376: if(p_resTbls.usgStartIdx(i) is not null) then
3377: for j in p_resTbls.usgStartIdx(i)..p_resTbls.usgEndIdx(i) loop
3378: wip_logger.log(' + usage start date:' || to_char(p_resTbls.usgStartDate(j), g_logDateFmt),l_retStatus);
3379: wip_logger.log(' + usage end date:' || to_char(p_resTbls.usgEndDate(j), g_logDateFmt),l_retStatus);
3380: wip_logger.log(' + cumulative usage:' || p_resTbls.usgCumMinProcTime(j),l_retStatus);
3381: end loop;
3382: end if;
3375: wip_logger.log('+ usg end idx:' || p_resTbls.usgEndIdx(i), l_retStatus);
3376: if(p_resTbls.usgStartIdx(i) is not null) then
3377: for j in p_resTbls.usgStartIdx(i)..p_resTbls.usgEndIdx(i) loop
3378: wip_logger.log(' + usage start date:' || to_char(p_resTbls.usgStartDate(j), g_logDateFmt),l_retStatus);
3379: wip_logger.log(' + usage end date:' || to_char(p_resTbls.usgEndDate(j), g_logDateFmt),l_retStatus);
3380: wip_logger.log(' + cumulative usage:' || p_resTbls.usgCumMinProcTime(j),l_retStatus);
3381: end loop;
3382: end if;
3383: end loop;
3376: if(p_resTbls.usgStartIdx(i) is not null) then
3377: for j in p_resTbls.usgStartIdx(i)..p_resTbls.usgEndIdx(i) loop
3378: wip_logger.log(' + usage start date:' || to_char(p_resTbls.usgStartDate(j), g_logDateFmt),l_retStatus);
3379: wip_logger.log(' + usage end date:' || to_char(p_resTbls.usgEndDate(j), g_logDateFmt),l_retStatus);
3380: wip_logger.log(' + cumulative usage:' || p_resTbls.usgCumMinProcTime(j),l_retStatus);
3381: end loop;
3382: end if;
3383: end loop;
3384: end if;
3382: end if;
3383: end loop;
3384: end if;
3385: if (l_logLevel <= wip_constants.trace_logging) then
3386: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.dumpResources',
3387: p_procReturnStatus => null,
3388: p_msg => 'success',
3389: x_returnStatus => l_retStatus);
3390: end if;
3391:
3392: exception
3393: when others then
3394: if (l_logLevel <= wip_constants.trace_logging) then
3395: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.dumpResources',
3396: p_procReturnStatus => null,
3397: p_msg => 'exception:' || SQLERRM,
3398: x_returnStatus => l_retStatus);
3399: end if;
3409: j NUMBER;
3410: k NUMBER; -- bug 8669295 (FP 8651554)
3411: l_logLevel NUMBER := fnd_log.g_current_runtime_level;
3412: l_retStatus VARCHAR2(1);
3413: l_params wip_logger.param_tbl_t;
3414:
3415: l_endDate DATE; --bug 8614399 (FP 8586766)
3416: begin
3417: if(l_logLevel <= wip_constants.trace_logging) then
3416: begin
3417: if(l_logLevel <= wip_constants.trace_logging) then
3418: l_params(1).paramName := 'p_anchorDate';
3419: l_params(1).paramValue := to_char(p_anchorDate, g_logDateFmt);
3420: wip_logger.entryPoint(p_procName => 'wip_infResSched_grp.scheduleNoResources',
3421: p_params => l_params,
3422: x_returnStatus => l_retStatus);
3423: end if;
3424: x_returnStatus := fnd_api.g_ret_sts_success;
3425:
3426: while(getNextResIdx(l_range, wip_constants.sched_no, g_forward, x_resTbls, i)) loop
3427:
3428: if(l_logLevel <= wip_constants.full_logging) then
3429: wip_logger.log('found scheduled no resource at ' || i, l_retStatus);
3430: end if;
3431:
3432: l_prevRange(2) := i;
3433: l_nextRange(1) := i;
3436: --find previous scheduled yes resource
3437: if(getNextResIdx(l_prevRange, wip_constants.sched_yes, g_backward, x_resTbls, j)) then
3438:
3439: if(l_logLevel <= wip_constants.full_logging) then
3440: wip_logger.log('found previous scheduled yes resource at ' || j, l_retStatus);
3441: end if;
3442:
3443: l_endDate := x_resTbls.endDate(j);
3444: --bug 8614399 (FP 8586766): identify the highest endDate in case of simultaneous resources
3448: and (x_resTbls.schedSeqNum(k) = x_resTbls.schedSeqNum(j))
3449: and (x_resTbls.endDate(k) > l_endDate)) then
3450: l_endDate := x_resTbls.endDate(k);
3451: if(l_logLevel <= wip_constants.full_logging) then
3452: wip_logger.log('higher endDate found at: '|| k || ' date: ' || l_endDate,l_retStatus);
3453: end if;
3454: end if;
3455: if((k > 1) and (x_resTbls.schedSeqNum(k) <> x_resTbls.schedSeqNum(k-1))) then
3456: exit;
3462: --couldn't find a scheduled yes resource
3463: /* Bug 6954186: Find the next scheduled resource in forward direction*/
3464: elsif(getNextResIdx(l_nextRange, wip_constants.sched_yes, g_forward, x_resTbls, j)) then
3465: if(l_logLevel <= wip_constants.full_logging) then
3466: wip_logger.log('found later scheduled yes resource at ' || j, l_retStatus);
3467: end if;
3468:
3469: x_resTbls.startDate(i) := x_resTbls.startDate(j);
3470:
3470:
3471: else --no scheduled yes resources
3472: x_resTbls.startDate(i) := p_anchorDate;
3473: if(l_logLevel <= wip_constants.full_logging) then
3474: wip_logger.log('no scheduled yes resources found', l_retStatus);
3475: end if;
3476: end if;
3477:
3478: x_resTbls.endDate(i) := x_resTbls.startDate(i);
3477:
3478: x_resTbls.endDate(i) := x_resTbls.startDate(i);
3479: end loop;
3480: if(l_logLevel <= wip_constants.trace_logging) then
3481: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.scheduleNoResources',
3482: p_procReturnStatus => x_returnStatus,
3483: p_msg => 'success',
3484: x_returnStatus => l_retStatus);
3485: end if;
3486: exception
3487: when others then
3488: x_returnStatus := fnd_api.g_ret_sts_unexp_error;
3489: if(l_logLevel <= wip_constants.trace_logging) then
3490: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.scheduleNoResources',
3491: p_procReturnStatus => x_returnStatus,
3492: p_msg => 'error: ' || SQLERRM,
3493: x_returnStatus => l_retStatus);
3494: end if;