[Home] [Help]
106: begin
107: for i in p_startIdx..x_resTbls.resID.count loop
108: if(x_resTbls.opSeqNum(i) = x_resTbls.opSeqNum(p_startIdx) and
109: x_resTbls.schedSeqNum(i) = x_resTbls.schedSeqNum(p_startIdx)) then
110: if(x_resTbls.schedFlag(i) = wip_constants.sched_prior) then
111: x_resTbls.schedFlag(i) := wip_constants.sched_yes;
112: end if;
113: else
114: return i;
107: for i in p_startIdx..x_resTbls.resID.count loop
108: if(x_resTbls.opSeqNum(i) = x_resTbls.opSeqNum(p_startIdx) and
109: x_resTbls.schedSeqNum(i) = x_resTbls.schedSeqNum(p_startIdx)) then
110: if(x_resTbls.schedFlag(i) = wip_constants.sched_prior) then
111: x_resTbls.schedFlag(i) := wip_constants.sched_yes;
112: end if;
113: else
114: return i;
115: end if;
121: procedure removePriorsFromBatch(x_resTbls IN OUT NOCOPY op_res_rectbl_t) is
122: i number := 2;
123: l_curOp NUMBER := x_resTbls.opSeqNum(1);
124: l_curSch NUMBER := x_resTbls.schedSeqNum(1);
125: l_priorExists boolean := x_resTbls.schedFlag(1) = wip_constants.sched_prior;
126: l_otherExists boolean := x_resTbls.schedFlag(1) in (wip_constants.sched_yes, wip_constants.sched_next);
127: l_startIdx NUMBER := 1;
128: l_logLevel NUMBER := fnd_log.g_current_runtime_level;
129: l_retStatus VARCHAR2(1);
122: i number := 2;
123: l_curOp NUMBER := x_resTbls.opSeqNum(1);
124: l_curSch NUMBER := x_resTbls.schedSeqNum(1);
125: l_priorExists boolean := x_resTbls.schedFlag(1) = wip_constants.sched_prior;
126: l_otherExists boolean := x_resTbls.schedFlag(1) in (wip_constants.sched_yes, wip_constants.sched_next);
127: l_startIdx NUMBER := 1;
128: l_logLevel NUMBER := fnd_log.g_current_runtime_level;
129: l_retStatus VARCHAR2(1);
130: l_params wip_logger.param_tbl_t;
128: l_logLevel NUMBER := fnd_log.g_current_runtime_level;
129: l_retStatus VARCHAR2(1);
130: l_params wip_logger.param_tbl_t;
131: begin
132: if(l_logLevel <= wip_constants.trace_logging) then
133: wip_logger.entryPoint(p_procName => 'wip_infResSched_grp.removePriorsFromBatch',
134: p_params => l_params,
135: x_returnStatus => l_retStatus);
136: end if;
137: while(i <= x_resTbls.resID.count) loop
138: --in same batch as the previous res
139: if(x_resTbls.schedSeqNum(i) = l_curSch and
140: x_resTbls.opSeqNum(i) = l_curOp) then
141: l_priorExists := l_priorExists or x_resTbls.schedFlag(i) = wip_constants.sched_prior;
142: l_otherExists := l_otherExists or x_resTbls.schedFlag(i) in (wip_constants.sched_yes, wip_constants.sched_next);
143: if(l_priorExists and l_otherExists) then
144: i := cleanBatch(p_startIdx => l_startIdx, x_resTbls => x_resTbls);
145: end if;
138: --in same batch as the previous res
139: if(x_resTbls.schedSeqNum(i) = l_curSch and
140: x_resTbls.opSeqNum(i) = l_curOp) then
141: l_priorExists := l_priorExists or x_resTbls.schedFlag(i) = wip_constants.sched_prior;
142: l_otherExists := l_otherExists or x_resTbls.schedFlag(i) in (wip_constants.sched_yes, wip_constants.sched_next);
143: if(l_priorExists and l_otherExists) then
144: i := cleanBatch(p_startIdx => l_startIdx, x_resTbls => x_resTbls);
145: end if;
146:
148: else
149: l_curOp := x_resTbls.opSeqNum(i);
150: l_curSch := x_resTbls.schedSeqNum(i);
151: l_startIdx := i;
152: l_priorExists := x_resTbls.schedFlag(i) = wip_constants.sched_prior;
153: l_otherExists := x_resTbls.schedFlag(i) in (wip_constants.sched_yes, wip_constants.sched_next);
154: end if;
155: i := i + 1;
156: end loop;
149: l_curOp := x_resTbls.opSeqNum(i);
150: l_curSch := x_resTbls.schedSeqNum(i);
151: l_startIdx := i;
152: l_priorExists := x_resTbls.schedFlag(i) = wip_constants.sched_prior;
153: l_otherExists := x_resTbls.schedFlag(i) in (wip_constants.sched_yes, wip_constants.sched_next);
154: end if;
155: i := i + 1;
156: end loop;
157: if (l_logLevel <= wip_constants.trace_logging) then
153: l_otherExists := x_resTbls.schedFlag(i) in (wip_constants.sched_yes, wip_constants.sched_next);
154: end if;
155: i := i + 1;
156: end loop;
157: if (l_logLevel <= wip_constants.trace_logging) then
158: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.removePriorsFromBatch',
159: p_procReturnStatus => null,
160: p_msg => 'success',
161: x_returnStatus => l_retStatus);
168: l_logLevel NUMBER := fnd_log.g_current_runtime_level;
169: l_retStatus VARCHAR2(1);
170: l_params wip_logger.param_tbl_t;
171: begin
172: if(l_logLevel <= wip_constants.trace_logging) then
173: wip_logger.entryPoint(p_procName => 'wip_infResSched_grp.removePriors',
174: p_params => l_params,
175: x_returnStatus => l_retStatus);
176: end if;
175: x_returnStatus => l_retStatus);
176: end if;
177: for i in 1..x_resTbls.resID.count loop
178: if(l_curOp = x_resTbls.opSeqNum(i)) then
179: if(l_nonPriorExists and x_resTbls.schedFlag(i) = wip_constants.sched_prior) then
180: x_resTbls.schedFlag(i) := wip_constants.sched_yes;
181: end if;
182: l_nonPriorExists := l_nonPriorExists or (x_resTbls.schedFlag(i) not in (wip_constants.sched_prior, wip_constants.sched_no));
183: else
176: end if;
177: for i in 1..x_resTbls.resID.count loop
178: if(l_curOp = x_resTbls.opSeqNum(i)) then
179: if(l_nonPriorExists and x_resTbls.schedFlag(i) = wip_constants.sched_prior) then
180: x_resTbls.schedFlag(i) := wip_constants.sched_yes;
181: end if;
182: l_nonPriorExists := l_nonPriorExists or (x_resTbls.schedFlag(i) not in (wip_constants.sched_prior, wip_constants.sched_no));
183: else
184: l_curOp := x_resTbls.opSeqNum(i);
178: if(l_curOp = x_resTbls.opSeqNum(i)) then
179: if(l_nonPriorExists and x_resTbls.schedFlag(i) = wip_constants.sched_prior) then
180: x_resTbls.schedFlag(i) := wip_constants.sched_yes;
181: end if;
182: l_nonPriorExists := l_nonPriorExists or (x_resTbls.schedFlag(i) not in (wip_constants.sched_prior, wip_constants.sched_no));
183: else
184: l_curOp := x_resTbls.opSeqNum(i);
185: l_nonPriorExists := x_resTbls.schedFlag(i) not in (wip_constants.sched_prior, wip_constants.sched_no);
186: end if;
181: end if;
182: l_nonPriorExists := l_nonPriorExists or (x_resTbls.schedFlag(i) not in (wip_constants.sched_prior, wip_constants.sched_no));
183: else
184: l_curOp := x_resTbls.opSeqNum(i);
185: l_nonPriorExists := x_resTbls.schedFlag(i) not in (wip_constants.sched_prior, wip_constants.sched_no);
186: end if;
187: end loop;
188: if (l_logLevel <= wip_constants.trace_logging) then
189: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.removePriors',
184: l_curOp := x_resTbls.opSeqNum(i);
185: l_nonPriorExists := x_resTbls.schedFlag(i) not in (wip_constants.sched_prior, wip_constants.sched_no);
186: end if;
187: end loop;
188: if (l_logLevel <= wip_constants.trace_logging) then
189: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.removePriors',
190: p_procReturnStatus => null,
191: p_msg => 'success',
192: x_returnStatus => l_retStatus);
199: l_logLevel NUMBER := fnd_log.g_current_runtime_level;
200: l_retStatus VARCHAR2(1);
201: l_params wip_logger.param_tbl_t;
202: begin
203: if(l_logLevel <= wip_constants.trace_logging) then
204: wip_logger.entryPoint(p_procName => 'wip_infResSched_grp.removeNexts',
205: p_params => l_params,
206: x_returnStatus => l_retStatus);
207: end if;
206: x_returnStatus => l_retStatus);
207: end if;
208: for i in reverse 1..x_resTbls.resID.count loop
209: if(l_curOp = x_resTbls.opSeqNum(i)) then
210: if(l_nonNextExists and x_resTbls.schedFlag(i) = wip_constants.sched_next) then
211: x_resTbls.schedFlag(i) := wip_constants.sched_yes;
212: end if;
213: l_nonNextExists := l_nonNextExists or
214: (x_resTbls.schedFlag(i) not in (wip_constants.sched_next, wip_constants.sched_no));
207: end if;
208: for i in reverse 1..x_resTbls.resID.count loop
209: if(l_curOp = x_resTbls.opSeqNum(i)) then
210: if(l_nonNextExists and x_resTbls.schedFlag(i) = wip_constants.sched_next) then
211: x_resTbls.schedFlag(i) := wip_constants.sched_yes;
212: end if;
213: l_nonNextExists := l_nonNextExists or
214: (x_resTbls.schedFlag(i) not in (wip_constants.sched_next, wip_constants.sched_no));
215: else
210: if(l_nonNextExists and x_resTbls.schedFlag(i) = wip_constants.sched_next) then
211: x_resTbls.schedFlag(i) := wip_constants.sched_yes;
212: end if;
213: l_nonNextExists := l_nonNextExists or
214: (x_resTbls.schedFlag(i) not in (wip_constants.sched_next, wip_constants.sched_no));
215: else
216: l_curOp := x_resTbls.opSeqNum(i);
217: l_nonNextExists := x_resTbls.schedFlag(i) not in (wip_constants.sched_no, wip_constants.sched_next);
218: end if;
213: l_nonNextExists := l_nonNextExists or
214: (x_resTbls.schedFlag(i) not in (wip_constants.sched_next, wip_constants.sched_no));
215: else
216: l_curOp := x_resTbls.opSeqNum(i);
217: l_nonNextExists := x_resTbls.schedFlag(i) not in (wip_constants.sched_no, wip_constants.sched_next);
218: end if;
219: end loop;
220: if (l_logLevel <= wip_constants.trace_logging) then
221: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.removeNexts',
216: l_curOp := x_resTbls.opSeqNum(i);
217: l_nonNextExists := x_resTbls.schedFlag(i) not in (wip_constants.sched_no, wip_constants.sched_next);
218: end if;
219: end loop;
220: if (l_logLevel <= wip_constants.trace_logging) then
221: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.removeNexts',
222: p_procReturnStatus => null,
223: p_msg => 'success',
224: x_returnStatus => l_retStatus);
230: l_logLevel NUMBER := fnd_log.g_current_runtime_level;
231: l_retStatus VARCHAR2(1);
232: l_params wip_logger.param_tbl_t;
233: begin
234: if(l_logLevel <= wip_constants.trace_logging) then
235: wip_logger.entryPoint(p_procName => 'wip_infResSched_grp.verifyResources',
236: p_params => l_params,
237: x_returnStatus => l_retStatus);
238: end if;
256:
257: --changes schedule method of prior resources to yes if
258: -- + they are in a simultaneous batch with yes or next resources.
259: removePriorsFromBatch(x_resTbls => x_resTbls);
260: if (l_logLevel <= wip_constants.trace_logging) then
261: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.verifyResources',
262: p_procReturnStatus => null,
263: p_msg => 'success',
264: x_returnStatus => l_retStatus);
269: l_logLevel NUMBER := fnd_log.g_current_runtime_level;
270: l_retStatus VARCHAR2(1);
271: l_params wip_logger.param_tbl_t;
272: begin
273: if(l_logLevel <= wip_constants.trace_logging) then
274: wip_logger.entryPoint(p_procName => 'wip_infResSched_grp.dumpOps',
275: p_params => l_params,
276: x_returnStatus => l_retStatus);
277: end if;
274: wip_logger.entryPoint(p_procName => 'wip_infResSched_grp.dumpOps',
275: p_params => l_params,
276: x_returnStatus => l_retStatus);
277: end if;
278: if(l_logLevel <= wip_constants.full_logging) then
279: for i in 1..p_opTbl.count loop
280: wip_logger.log('op:' || i, l_retStatus);
281: wip_logger.log('startDate:' || to_char(p_opTbl(i).startDate, g_logDateFmt), l_retStatus);
282: wip_logger.log('endDate:' || to_char(p_opTbl(i).endDate, g_logDateFmt), l_retStatus);
292: end if;
293: wip_logger.log('resRange:' || p_opTbl(i).resStartIdx || '-' || p_opTbl(i).resEndIdx, l_retStatus);
294: end loop;
295: end if;
296: if (l_logLevel <= wip_constants.trace_logging) then
297: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.dumpOps',
298: p_procReturnStatus => null,
299: p_msg => 'success',
300: x_returnStatus => l_retStatus);
312: l_logLevel NUMBER := fnd_log.g_current_runtime_level;
313: l_retStatus VARCHAR2(1);
314: l_params wip_logger.param_tbl_t;
315: begin
316: if(l_logLevel <= wip_constants.trace_logging) then
317: l_params(1).paramName := 'p_anchorDate';
318: l_params(1).paramValue := to_char(p_anchorDate, g_logDateFmt);
319: wip_logger.entryPoint(p_procName => 'wip_infResSched_grp.buildOpStructure',
320: p_params => l_params,
327:
328: for i in 1..p_resTbls.resID.count loop
329: if(l_opSeqNum <> p_resTbls.opSeqNum(i)) then
330:
331: if(l_logLevel <= wip_constants.full_logging) then
332: wip_logger.log('new op at resource ' || i, l_retStatus);
333: end if;
334:
335: x_opTbl(j).resEndIdx := i - 1;
342: x_opTbl(j).resEndIdx := null;
343:
344: l_opSeqNum := p_resTbls.opSeqNum(i);
345: end if;
346: x_opTbl(j).priorsExist := x_opTbl(j).priorsExist or p_resTbls.schedFlag(i) = wip_constants.sched_prior;
347: x_opTbl(j).nextsExist := x_opTbl(j).nextsExist or p_resTbls.schedFlag(i) = wip_constants.sched_next;
348: if(p_resTbls.schedFlag(i) = wip_constants.sched_yes) then
349: if(l_firstYesOpIdx is null) then
350: l_firstYesOpIdx := j;
343:
344: l_opSeqNum := p_resTbls.opSeqNum(i);
345: end if;
346: x_opTbl(j).priorsExist := x_opTbl(j).priorsExist or p_resTbls.schedFlag(i) = wip_constants.sched_prior;
347: x_opTbl(j).nextsExist := x_opTbl(j).nextsExist or p_resTbls.schedFlag(i) = wip_constants.sched_next;
348: if(p_resTbls.schedFlag(i) = wip_constants.sched_yes) then
349: if(l_firstYesOpIdx is null) then
350: l_firstYesOpIdx := j;
351: end if;
344: l_opSeqNum := p_resTbls.opSeqNum(i);
345: end if;
346: x_opTbl(j).priorsExist := x_opTbl(j).priorsExist or p_resTbls.schedFlag(i) = wip_constants.sched_prior;
347: x_opTbl(j).nextsExist := x_opTbl(j).nextsExist or p_resTbls.schedFlag(i) = wip_constants.sched_next;
348: if(p_resTbls.schedFlag(i) = wip_constants.sched_yes) then
349: if(l_firstYesOpIdx is null) then
350: l_firstYesOpIdx := j;
351: end if;
352: x_opTbl(j).startDate := least(p_resTbls.startDate(i), nvl(x_opTbl(j).startDate, p_resTbls.startDate(i)));
350: l_firstYesOpIdx := j;
351: end if;
352: x_opTbl(j).startDate := least(p_resTbls.startDate(i), nvl(x_opTbl(j).startDate, p_resTbls.startDate(i)));
353: x_opTbl(j).endDate := greatest(p_resTbls.endDate(i), nvl(x_opTbl(j).endDate, p_resTbls.endDate(i)));
354: if(l_logLevel <= wip_constants.full_logging) then
355: wip_logger.log('op ' || j || '''s start date:' || to_char(x_opTbl(j).startDate, g_logDateFmt), l_retStatus);
356: wip_logger.log('op ' || j || '''s end date:' || to_char(x_opTbl(j).endDate, g_logDateFmt), l_retStatus);
357: end if;
358:
384: end if;
385: end if;
386: end loop;
387:
388: if (l_logLevel <= wip_constants.full_logging) then
389: dumpOps(x_opTbl);
390: end if;
391:
392: if (l_logLevel <= wip_constants.trace_logging) then
388: if (l_logLevel <= wip_constants.full_logging) then
389: dumpOps(x_opTbl);
390: end if;
391:
392: if (l_logLevel <= wip_constants.trace_logging) then
393: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.buildOpStructure',
394: p_procReturnStatus => null,
395: p_msg => 'success',
396: x_returnStatus => l_retStatus);
407: l_params wip_logger.param_tbl_t;
408: l_logLevel NUMBER := fnd_log.g_current_runtime_level;
409: l_foundMidPntOp boolean := false;
410: begin
411: if(l_logLevel <= wip_constants.trace_logging) then
412: l_params(1).paramName := 'p_opSeqNum';
413: l_params(1).paramValue := p_opSeqNum;
414: l_params(2).paramName := 'p_resSeqNum';
415: l_params(2).paramValue := p_resSeqNum;
425: --find the midpoint resource
426: for i in 1..p_resTbls.resID.count loop
427: if(p_opSeqNum = p_resTbls.opSeqNum(i)) then --op matches
428: l_foundMidPntOp := true;
429: if(l_logLevel <= wip_constants.full_logging) then
430: wip_logger.log('op seq matches res ' || i, l_retStatus);
431: end if;
432:
433: if(p_resSeqNum is not null) then
432:
433: if(p_resSeqNum is not null) then
434: if(p_resSeqNum = p_resTbls.resSeqNum(i)) then --res seq matches
435:
436: if(l_logLevel <= wip_constants.full_logging) then
437: wip_logger.log('res seq matches res ' || i, l_retStatus);
438: end if;
439:
440: if(p_isMdPntFwd) then
472: exit; --res seq matched, exit loop
473: end if;
474: else --resource seq was not populated. use op start or end res
475:
476: if(l_logLevel <= wip_constants.full_logging) then
477: wip_logger.log('res seq is null', l_retStatus);
478: end if;
479:
480: if(p_isMdPntFwd) then --forward scheduling midpoint op
490: --if backward scheduling the midpoint op and the first op of the next op was found...
491: if(l_foundMidPntOp and
492: not(p_isMdPntFwd) and
493: p_resTbls.opSeqNum(i) <> p_opSeqNum) then
494: if(l_logLevel <= wip_constants.full_logging) then
495: wip_logger.log('first res past midpoint at idx:' || i, l_retStatus);
496: end if;
497: x_midPntBkwdIdx := i - 1;
498: x_midPntFwdIdx := i;
499: exit;
500: end if;
501:
502: if(p_resTbls.resID.count = i) then
503: if(l_logLevel <= wip_constants.full_logging) then
504: wip_logger.log('backward scheduling everything', l_retStatus);
505: end if;
506:
507: x_midPntBkwdIdx := i;
507: x_midPntBkwdIdx := i;
508: exit;
509: end if;
510: end loop;
511: if(l_logLevel <= wip_constants.trace_logging) then
512: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.findMdPntRes',
513: p_procReturnStatus => null,
514: p_msg => 'fwdIdx:' || x_midPntFwdIdx || '; bkwdIdx:' || x_midPntBkwdIdx,
515: x_returnStatus => l_retStatus);
536: l_range num_tbl_t := num_tbl_t(null,null);
537: l_opTbl op_tbl_t;
538: l_errMsg VARCHAR2(2000);
539: begin
540: if(l_logLevel <= wip_constants.trace_logging) then
541: l_params(1).paramName := 'p_orgID';
542: l_params(1).paramValue := p_orgID;
543: l_params(2).paramName := 'p_repLineID';
544: l_params(2).paramValue := p_repLineID;
557: end if;
558: x_returnStatus := fnd_api.g_ret_sts_success;
559:
560: if(x_resTbls.resID is null or x_resTbls.resID.count < 1) then
561: if (l_logLevel <= wip_constants.trace_logging) then
562: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.schedule',
563: p_procReturnStatus => x_returnStatus,
564: p_msg => 'no resources to schedule!',
565: x_returnStatus => l_retStatus);
583: x_resTbls.usgEndIdx.extend(x_resTbls.resID.count);
584:
585: x_resTbls.startDate.extend(x_resTbls.resID.count);
586: x_resTbls.endDate.extend(x_resTbls.resID.count);
587: if(l_logLevel <= wip_constants.trace_logging) then
588: dumpResources(x_resTbls);
589: end if;
590:
591: verifyResources(x_resTbls => x_resTbls);
589: end if;
590:
591: verifyResources(x_resTbls => x_resTbls);
592:
593: if(l_logLevel <= wip_constants.trace_logging) then
594: dumpResources(x_resTbls);
595: end if;
596:
597: --caller wants to either forward or backward schedule.
602: l_bkwdEndIdx := null;--this line isn''t necessary, but is included for clarity
603: l_startDate := p_startDate;
604: l_endDate := null;--this line isn''t necessary, but is included for clarity
605:
606: if (l_logLevel <= wip_constants.trace_logging) then
607: wip_logger.log(p_msg => 'forward scheduling',
608: x_returnStatus => x_returnStatus);
609: end if;
610:
614: l_bkwdEndIdx := x_resTbls.resID.count;
615: l_startDate := null;--this line isn''t necessary, but is included for clarity
616: l_endDate := p_endDate;
617:
618: if (l_logLevel <= wip_constants.trace_logging) then
619: wip_logger.log(p_msg => 'backward scheduling',
620: x_returnStatus => x_returnStatus);
621: end if;
622: end if;
627: p_isMdPntFwd => p_startDate is not null,
628: x_midPntFwdIdx => l_fwdStIdx,
629: x_midPntBkwdIdx => l_bkwdEndIdx);
630:
631: if (l_logLevel <= wip_constants.trace_logging) then
632: wip_logger.log(p_msg => 'midpoint scheduling',
633: x_returnStatus => x_returnStatus);
634: end if;
635:
654: forwardSchedule(p_orgID => p_orgID,
655: p_repLineID => p_repLineID,
656: p_startDate => l_startDate,
657: p_range => l_range,
658: p_schedFlag => wip_constants.sched_yes,
659: x_resTbls => x_resTbls,
660: x_returnStatus => x_returnStatus);
661:
662: if(x_returnStatus <> fnd_api.g_ret_sts_success) then
659: x_resTbls => x_resTbls,
660: x_returnStatus => x_returnStatus);
661:
662: if(x_returnStatus <> fnd_api.g_ret_sts_success) then
663: if(l_logLevel <= wip_constants.full_logging) then
664: wip_logger.log('fwdSch failed', l_retStatus);
665: end if;
666: raise fnd_api.g_exc_unexpected_error;
667: end if;
665: end if;
666: raise fnd_api.g_exc_unexpected_error;
667: end if;
668:
669: if(l_logLevel <= wip_constants.full_logging) then
670: dumpResources(x_resTbls);
671: end if;
672: end if;
673:
679: backwardSchedule(p_orgID => p_orgID,
680: p_repLineID => p_repLineID,
681: p_endDate => l_endDate,
682: p_range => l_range,
683: p_schedFlag => wip_constants.sched_yes,
684: x_resTbls => x_resTbls,
685: x_returnStatus => x_returnStatus);
686: if(x_returnStatus <> fnd_api.g_ret_sts_success) then
687: if(l_logLevel <= wip_constants.full_logging) then
683: p_schedFlag => wip_constants.sched_yes,
684: x_resTbls => x_resTbls,
685: x_returnStatus => x_returnStatus);
686: if(x_returnStatus <> fnd_api.g_ret_sts_success) then
687: if(l_logLevel <= wip_constants.full_logging) then
688: wip_logger.log('bkwdSch failed', l_retStatus);
689: end if;
690: raise fnd_api.g_exc_unexpected_error;
691: end if;
703: x_resTbls => x_resTbls,
704: x_returnStatus => x_returnStatus);
705:
706: if(x_returnStatus <> fnd_api.g_ret_sts_success) then
707: if(l_logLevel <= wip_constants.full_logging) then
708: wip_logger.log('schPriorRes failed', l_retStatus);
709: end if;
710: raise fnd_api.g_exc_unexpected_error;
711: end if;
721: x_returnStatus => x_returnStatus);
722:
723:
724: if(x_returnStatus <> fnd_api.g_ret_sts_success) then
725: if(l_logLevel <= wip_constants.full_logging) then
726: wip_logger.log('schNextRes failed', l_retStatus);
727: end if;
728: raise fnd_api.g_exc_unexpected_error;
729: end if;
727: end if;
728: raise fnd_api.g_exc_unexpected_error;
729: end if;
730:
731: if(l_logLevel <= wip_constants.full_logging) then
732: dumpResources(x_resTbls);
733: end if;
734:
735: --if forward scheduling...
739: p_startDate => p_startDate,
740: x_resTbls => x_resTbls,
741: x_returnStatus => x_returnStatus);
742: if(x_returnStatus <> fnd_api.g_ret_sts_success) then
743: if(l_logLevel <= wip_constants.full_logging) then
744: wip_logger.log('resolvePriorRes failed', l_retStatus);
745: end if;
746: raise fnd_api.g_exc_unexpected_error;
747: end if;
754: x_returnStatus => x_returnStatus);
755: -- Bug # 13388579 resetting the flag.
756: is_backWardSch := false;
757: if(x_returnStatus <> fnd_api.g_ret_sts_success) then
758: if(l_logLevel <= wip_constants.full_logging) then
759: wip_logger.log('resolveNextRes failed', l_retStatus);
760: end if;
761: raise fnd_api.g_exc_unexpected_error;
762: end if;
769: x_returnStatus => x_returnStatus);
770:
771:
772:
773: if(l_logLevel <= wip_constants.trace_logging) then
774: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.schedule',
775: p_procReturnStatus => x_returnStatus,
776: p_msg => 'success',
777: x_returnStatus => l_retStatus);
781: end if;
782: exception
783: when fnd_api.g_exc_unexpected_error then
784: x_returnStatus := fnd_api.g_ret_sts_unexp_error;
785: if(l_logLevel <= wip_constants.trace_logging) then
786: wip_utilities.get_message_stack(p_msg => l_errMsg,
787: p_delete_stack => fnd_api.g_false);
788:
789: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.schedule',
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);
853: l_maxEndDate date;
854: l_logLevel NUMBER := fnd_log.g_current_runtime_level;
855: l_bool boolean;
856: begin
857: if (l_logLevel <= wip_constants.trace_logging) then
858: l_params(1).paramName := 'p_range(1)';
859: l_params(1).paramValue := p_range(1);
860: l_params(2).paramName := 'p_range(2)';
861: l_params(2).paramValue := p_range(2);
879: --latest end date to use as the next resource's start date
880: i := p_curIdx;
881:
882: if(p_doneSchedBatch) then
883: if(l_logLevel <= wip_constants.full_logging) then
884: wip_logger.log('done scheduling batch', l_retStatus);
885: end if;
886: while(getNextResIdx(p_range, p_schedFlag, g_backward, p_resTbls, i)) loop
887: if(l_logLevel <= wip_constants.full_logging) then
883: if(l_logLevel <= wip_constants.full_logging) then
884: wip_logger.log('done scheduling batch', l_retStatus);
885: end if;
886: while(getNextResIdx(p_range, p_schedFlag, g_backward, p_resTbls, i)) loop
887: if(l_logLevel <= wip_constants.full_logging) then
888: wip_logger.log('in loop', l_retStatus);
889: wip_logger.log('resID' || p_resTbls.resID(i), l_retStatus);
890: wip_logger.log('opSeq' || p_resTbls.opSeqNum(i), l_retStatus);
891: wip_logger.log('schSeq' || p_resTbls.schedSeqNum(i), l_retStatus);
893: end if;
894: if(p_resTbls.schedSeqNum(i) = p_resTbls.schedSeqNum(p_prevIdx) and
895: p_resTbls.opSeqNum(i) = p_resTbls.opSeqNum(p_prevIdx)) then
896: l_maxEndDate := greatest(nvl(l_maxEndDate, p_resTbls.endDate(i)), p_resTbls.endDate(i));
897: if(l_logLevel <= wip_constants.full_logging) then
898: wip_logger.log('resource in batch. endDate:' || to_char(p_resTbls.endDate(i), g_logDateFmt), l_retStatus);
899: end if;
900: else
901: if(l_logLevel <= wip_constants.full_logging) then
897: if(l_logLevel <= wip_constants.full_logging) then
898: wip_logger.log('resource in batch. endDate:' || to_char(p_resTbls.endDate(i), g_logDateFmt), l_retStatus);
899: end if;
900: else
901: if(l_logLevel <= wip_constants.full_logging) then
902: wip_logger.log('resource not in batch.', l_retStatus);
903: end if;
904: exit;
905: end if;
908: l_bool := (getNextResIdx(p_range, p_schedFlag, g_backward, p_resTbls, i));
909: l_maxEndDate := p_resTbls.endDate(i);
910: end if;
911:
912: if (l_logLevel <= wip_constants.trace_logging) then
913: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.getStartDate',
914: p_procReturnStatus => to_char(l_maxEndDate),
915: p_msg => 'finished scheduling',
916: x_returnStatus => l_retStatus);
926: l_retStatus VARCHAR2(1);
927: l_params wip_logger.param_tbl_t;
928: l_logLevel NUMBER := fnd_log.g_current_runtime_level;
929: begin
930: if (l_logLevel <= wip_constants.trace_logging) then
931: wip_logger.entryPoint(p_procName => 'wip_infResSched_grp.process_shift',
932: p_params => l_params,
933: x_returnStatus => l_retStatus);
934: end if;
935: if(p_fwd_or_bk_flag = 1) then /*backward schedule*/
936: /*For backward scheduling, the shifts are ordered in descreasing order of
937: startDate. Thats how the cursors populating into x_shifts are written.*/
938: if(x_shifts.endDate(i+1) < x_shifts.startDate(i)) then
939: if(l_logLevel <= wip_constants.full_logging) then
940: wip_logger.log('no overlap for shift', l_retStatus);
941: end if;
942: else
943: x_overlap_exists := true;
941: end if;
942: else
943: x_overlap_exists := true;
944: if(x_shifts.endDate(i+1) <= x_shifts.endDate(i)) then
945: if(l_logLevel <= wip_constants.full_logging) then
946: wip_logger.log('partial overlap for shift', l_retStatus);
947: end if;
948: x_shifts.endDate(i+1) := x_shifts.endDate(i);
949: else
946: wip_logger.log('partial overlap for shift', l_retStatus);
947: end if;
948: x_shifts.endDate(i+1) := x_shifts.endDate(i);
949: else
950: if(l_logLevel <= wip_constants.full_logging) then
951: wip_logger.log('complete overlap for shift', l_retStatus);
952: end if;
953: end if;
954: /*instead of deleting the shift here setting it to -1.
960: else /*forward schedule*/
961: /*For forward scheduling, the shifts are ordered in ascending order of
962: startDate. Thats how the cursors populating into x_shifts are written.*/
963: if(x_shifts.startDate(i+1) > x_shifts.endDate(i)) then
964: if(l_logLevel <= wip_constants.full_logging) then
965: wip_logger.log('no overlap for shift', l_retStatus);
966: end if;
967: else
968: x_overlap_exists := true;
966: end if;
967: else
968: x_overlap_exists := true;
969: if(x_shifts.startDate(i+1) > x_shifts.startDate(i)) then
970: if(l_logLevel <= wip_constants.full_logging) then
971: wip_logger.log('partial overlap for shift: adjusted startDate', l_retStatus);
972: end if;
973: x_shifts.startDate(i+1) := x_shifts.startDate(i);
974: end if;
972: end if;
973: x_shifts.startDate(i+1) := x_shifts.startDate(i);
974: end if;
975: if(x_shifts.endDate(i+1) < x_shifts.endDate(i)) then
976: if(l_logLevel <= wip_constants.full_logging) then
977: wip_logger.log('partial overlap for shift: adjusted endDate', l_retStatus);
978: end if;
979: x_shifts.endDate(i+1) := x_shifts.endDate(i);
980: end if;
984: performance impact.*/
985: x_shifts.shiftNum(i) := -1;
986: end if;
987: end if;
988: if(l_logLevel <= wip_constants.trace_logging) then
989: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.process_shift',
990: p_procReturnStatus => l_retStatus,
991: p_msg => 'successful complete',
992: x_returnStatus => l_retStatus);
1002: l_params wip_logger.param_tbl_t;
1003: l_logLevel NUMBER := fnd_log.g_current_runtime_level;
1004: l_shifts shift_recTbl_t;
1005: begin
1006: if (l_logLevel <= wip_constants.trace_logging) then
1007: wip_logger.entryPoint(p_procName => 'wip_infResSched_grp.process_overlap_shifts',
1008: p_params => l_params,
1009: x_returnStatus => l_retStatus);
1010:
1012: wip_logger.log('*********************************', l_retStatus);
1013: end if;
1014:
1015: for i in 1..(x_shifts.shiftNum.count) loop
1016: if (l_logLevel <= wip_constants.trace_logging) then
1017: wip_logger.log('shiftNum: ' || x_shifts.shiftNum(i)
1018: || ' startDate: ' || to_char(x_shifts.startDate(i), g_logDateFmt)
1019: || ' endDate:' || to_char(x_shifts.endDate(i), g_logDateFmt), l_retStatus);
1020: end if;
1022: /*Overnight shifts*/
1023: for i in 1..(x_shifts.shiftNum.count) loop
1024: if(x_shifts.endDate(i) < x_shifts.startDate(i)) then --overnight shift
1025: x_shifts.endDate(i) := x_shifts.endDate(i) + 1;
1026: if (l_logLevel <= wip_constants.trace_logging) then
1027: wip_logger.log('increased endDate by 1 day for count: ' || i, l_retStatus);
1028: end if;
1029: end if;
1030: end loop;
1032: Since shifts are getting merged thus Shift Numbers may be incorrect after this processing.
1033: But in later part of the code, Shift Numbers are not needed and only start and end times
1034: are used.*/
1035: while(l_overlap_exists) loop
1036: if (l_logLevel <= wip_constants.trace_logging) then
1037: wip_logger.log('while loop begins', l_retStatus);
1038: end if;
1039: --at the start of the loop, setting overlap to false. if it exists it will set to true
1040: l_overlap_exists := false;
1046: end if;
1047: end loop;--for loop
1048: /*Deleting shifts with shiftNum = -1*/
1049: l_count := x_shifts.shiftNum.count;
1050: if (l_logLevel <= wip_constants.trace_logging) then
1051: wip_logger.log('num of shifts: ' || l_count, l_retStatus);
1052: end if;
1053: for i in 1..(x_shifts.shiftNum.count) loop
1054: if (l_logLevel <= wip_constants.trace_logging) then
1050: if (l_logLevel <= wip_constants.trace_logging) then
1051: wip_logger.log('num of shifts: ' || l_count, l_retStatus);
1052: end if;
1053: for i in 1..(x_shifts.shiftNum.count) loop
1054: if (l_logLevel <= wip_constants.trace_logging) then
1055: wip_logger.log('counter: ' || i, l_retStatus);
1056: end if;
1057: if(x_shifts.shiftNum(i) = -1) then
1058: l_count2 := i;
1060: if (x_shifts.shiftNum(j) > -1) then
1061: l_count2 := j; exit;
1062: end if;
1063: end loop;
1064: if (l_logLevel <= wip_constants.trace_logging) then
1065: wip_logger.log('moving shift at position: ' || l_count2 || 'to position: ' || i, l_retStatus);
1066: end if;
1067: if(l_count2 > i) then
1068: x_shifts.shiftNum(i) := x_shifts.shiftNum(l_count2);
1069: x_shifts.startDate(i) := x_shifts.startDate(l_count2);
1070: x_shifts.endDate(i) := x_shifts.endDate(l_count2);
1071: x_shifts.shiftNum(l_count2) := -1;
1072: end if;
1073: if (l_logLevel <= wip_constants.trace_logging) then
1074: wip_logger.log('deleted counter: ' || i, l_retStatus);
1075: end if;
1076: end if;
1077: end loop;
1080: for i in 1..(x_shifts.shiftNum.count) loop
1081: if(x_shifts.shiftNum(i) > -1) then l_count2 := l_count2 + 1;
1082: end if;
1083: end loop;
1084: if (l_logLevel <= wip_constants.trace_logging) then
1085: wip_logger.log('num of shifts (after deletion): ' || l_count2, l_retStatus);
1086: end if;
1087: /*Trim the remaining shifts. These shifts are deleted above and have shiftNum as -1*/
1088: x_shifts.shiftNum.trim(l_count-l_count2);
1088: x_shifts.shiftNum.trim(l_count-l_count2);
1089: x_shifts.startDate.trim(l_count-l_count2);
1090: x_shifts.endDate.trim(l_count-l_count2);
1091: end loop;
1092: if (l_logLevel <= wip_constants.trace_logging) then
1093: wip_logger.log('shifts AFTER overlap processing', l_retStatus);
1094: wip_logger.log('*********************************', l_retStatus);
1095: end if;
1096: for i in 1..(x_shifts.shiftNum.count) loop
1093: wip_logger.log('shifts AFTER overlap processing', l_retStatus);
1094: wip_logger.log('*********************************', l_retStatus);
1095: end if;
1096: for i in 1..(x_shifts.shiftNum.count) loop
1097: if (l_logLevel <= wip_constants.trace_logging) then
1098: wip_logger.log('shiftNum: ' || x_shifts.shiftNum(i)
1099: || ' startDate: ' || to_char(x_shifts.startDate(i), g_logDateFmt)
1100: || ' endDate:' || to_char(x_shifts.endDate(i), g_logDateFmt), l_retStatus);
1101: end if;
1099: || ' startDate: ' || to_char(x_shifts.startDate(i), g_logDateFmt)
1100: || ' endDate:' || to_char(x_shifts.endDate(i), g_logDateFmt), l_retStatus);
1101: end if;
1102: end loop;
1103: if(l_logLevel <= wip_constants.trace_logging) then
1104: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.process_overlap_shifts',
1105: p_procReturnStatus => l_retStatus,
1106: p_msg => 'successful complete',
1107: x_returnStatus => l_retStatus);
1207: l_prevProcTime NUMBER;
1208: l_isFirstUsg boolean := true;
1209: l_dummy NUMBER; /* Bug 5660475 */
1210: begin
1211: if (l_logLevel <= wip_constants.trace_logging) then
1212: l_params(1).paramName := 'p_orgID';
1213: l_params(1).paramValue := p_orgID;
1214: l_params(2).paramName := 'p_startDate';
1215: l_params(2).paramValue := to_char(p_startDate, g_logDateFmt);
1227:
1228: /* Fix for bug 5660475: If dealing with shift resource, first check if shifts are setup fine. */
1229: -- Bug 16322124 . Modified for Performance. Validation is simplified since all shifts are assumed to have valid time setups .
1230: if( p_repLineID is null
1231: and x_resTbls.avail24Flag(p_idx) = wip_constants.no
1232: and x_resTbls.schedFlag(p_idx) <> wip_constants.sched_no) then
1233: if (l_logLevel <= wip_constants.trace_logging) then
1234: wip_logger.log('This is a shift resource. Need to validate shift setup', l_retStatus);
1235: end if;
1228: /* Fix for bug 5660475: If dealing with shift resource, first check if shifts are setup fine. */
1229: -- Bug 16322124 . Modified for Performance. Validation is simplified since all shifts are assumed to have valid time setups .
1230: if( p_repLineID is null
1231: and x_resTbls.avail24Flag(p_idx) = wip_constants.no
1232: and x_resTbls.schedFlag(p_idx) <> wip_constants.sched_no) then
1233: if (l_logLevel <= wip_constants.trace_logging) then
1234: wip_logger.log('This is a shift resource. Need to validate shift setup', l_retStatus);
1235: end if;
1236: begin
1229: -- Bug 16322124 . Modified for Performance. Validation is simplified since all shifts are assumed to have valid time setups .
1230: if( p_repLineID is null
1231: and x_resTbls.avail24Flag(p_idx) = wip_constants.no
1232: and x_resTbls.schedFlag(p_idx) <> wip_constants.sched_no) then
1233: if (l_logLevel <= wip_constants.trace_logging) then
1234: wip_logger.log('This is a shift resource. Need to validate shift setup', l_retStatus);
1235: end if;
1236: begin
1237: -- Bug 16322124 . Modified for Performance.
1248: and brs.shift_num is not null
1249: and rownum = 1);
1250: exception
1251: when NO_DATA_FOUND then
1252: if (l_logLevel <= wip_constants.trace_logging) then
1253: wip_logger.log('Error: Missing shifts or shift times!', l_retStatus);
1254: end if;
1255: fnd_message.set_name('WIP', 'WIP_SHIFT_RESOURCE');
1256: fnd_message.set_token('ENTITY1', x_resTbls.resSeqNum(p_idx));
1263: x_resTbls.usgStartIdx(p_idx) := null;
1264: x_resTbls.usgEndIdx(p_idx) := null;
1265: loop
1266: exit when l_resourceScheduled;
1267: if (l_logLevel <= wip_constants.full_logging) then
1268: wip_logger.log('cursor start date is' || to_char(l_cursorStartDate, g_logDateFmt), l_retStatus);
1269: wip_logger.log('cursor end date is' || to_char((l_cursorStartDate + g_dateCursorLen - 1/86400), g_logDateFmt), l_retStatus);
1270: end if;
1271:
1270: end if;
1271:
1272: --for v_endDate, subtract a second to avoid overlap between cursors.
1273: if(p_repLineID is not null) then
1274: if(l_logLevel <= wip_constants.full_logging) then
1275: wip_logger.log('scheduling repetitive', l_retStatus);
1276: end if;
1277: open c_repTimes(v_repLineID => p_repLineID,
1278: v_orgID => p_orgID,
1282: bulk collect into l_shifts.shiftNum,
1283: l_shifts.startDate,
1284: l_shifts.endDate;
1285: close c_repTimes;
1286: elsif(x_resTbls.avail24Flag(p_idx) = wip_constants.yes) then
1287: if(l_logLevel <= wip_constants.full_logging) then
1288: wip_logger.log('scheduling 24HR resource', l_retStatus);
1289: end if;
1290: open c_24HrTimes(v_orgID => p_orgID,
1283: l_shifts.startDate,
1284: l_shifts.endDate;
1285: close c_repTimes;
1286: elsif(x_resTbls.avail24Flag(p_idx) = wip_constants.yes) then
1287: if(l_logLevel <= wip_constants.full_logging) then
1288: wip_logger.log('scheduling 24HR resource', l_retStatus);
1289: end if;
1290: open c_24HrTimes(v_orgID => p_orgID,
1291: v_startDate => l_cursorStartDate,
1295: l_shifts.startDate,
1296: l_shifts.endDate;
1297: close c_24HrTimes;
1298: else
1299: if(l_logLevel <= wip_constants.full_logging) then
1300: wip_logger.log('scheduling shift resource', l_retStatus);
1301: end if;
1302: open c_shiftTimes(v_resID => x_resTbls.resID(p_idx),
1303: v_deptID => x_resTbls.deptID(p_idx),
1311:
1312: if (l_shifts.shiftNum.count = 0 ) then
1313: /* Fix for bug 5660475: If shifts are not available in the date range,
1314: we should continue to search in the next date range, instead of erroring out. */
1315: if (l_logLevel <= wip_constants.trace_logging) then
1316: wip_logger.log('No shifts found in this period.', l_retStatus);
1317: end if;
1318: l_resourceScheduled := false;
1319: end if;
1327: x_shifts => l_shifts,
1328: x_returnStatus => x_returnStatus);
1329: /* end of fix bug 7027946 */
1330:
1331: if (l_logLevel <= wip_constants.full_logging) then
1332: if (l_logLevel <= wip_constants.trace_logging) then
1333: wip_logger.log('*** Shift records after incorporating capacity exceptions ***', l_retStatus);
1334: end if;
1335: for i in 1..l_shifts.shiftNum.count loop
1328: x_returnStatus => x_returnStatus);
1329: /* end of fix bug 7027946 */
1330:
1331: if (l_logLevel <= wip_constants.full_logging) then
1332: if (l_logLevel <= wip_constants.trace_logging) then
1333: wip_logger.log('*** Shift records after incorporating capacity exceptions ***', l_retStatus);
1334: end if;
1335: for i in 1..l_shifts.shiftNum.count loop
1336: if (l_logLevel <= wip_constants.trace_logging) then
1332: if (l_logLevel <= wip_constants.trace_logging) then
1333: wip_logger.log('*** Shift records after incorporating capacity exceptions ***', l_retStatus);
1334: end if;
1335: for i in 1..l_shifts.shiftNum.count loop
1336: if (l_logLevel <= wip_constants.trace_logging) then
1337: wip_logger.log(l_shifts.shiftNum(i)||' - '||to_char(l_shifts.startDate(i),'DD.MM.YYYY HH24:MI:SS')||' - '||to_char(l_shifts.endDate(i),'DD.MM.YYYY HH24:MI:SS'), l_retStatus);
1338: end if;
1339: end loop;
1340: end if;
1347: if(l_shifts.endDate(i) < l_shifts.startDate(i)) then --overnight shift
1348: l_shifts.endDate(i) := l_shifts.endDate(i) + 1;
1349: end if;
1350:
1351: if (l_logLevel <= wip_constants.full_logging) then
1352: wip_logger.log('**********shiftNum:' || l_shifts.shiftNum(i), l_retStatus);
1353: wip_logger.log('**shift start date:' || to_char(l_shifts.startDate(i), g_logDateFmt), l_retStatus);
1354: wip_logger.log('****shift end date:' || to_char(l_shifts.endDate(i), g_logDateFmt), l_retStatus);
1355: end if;
1356:
1357: --if shift ends before the requested start date, skip it since none of the shift
1358: --can be used. don't do this in the sql query as it degrades performance
1359: if(l_shifts.endDate(i) <= p_startDate) then
1360: if (l_logLevel <= wip_constants.full_logging) then
1361: wip_logger.log('skipping shift (ends before start date)', l_retStatus);
1362: end if;
1363: goto NO_FULFILL_USAGE;--end of loop
1364: end if;
1364: end if;
1365:
1366: --if the shift starts before the start time, adjust the shift length
1367: l_fromDate := greatest(l_shifts.startDate(i), p_startDate);
1368: if (l_logLevel <= wip_constants.full_logging) then
1369: wip_logger.log('calculated start date: ' || to_char(l_fromDate, g_logDateFmt), l_retStatus);
1370: end if;
1371:
1372: l_shiftLen := l_shifts.endDate(i) - l_fromDate;
1372: l_shiftLen := l_shifts.endDate(i) - l_fromDate;
1373: /*Bug 7015594: If shift start time is same as end time then consider it as 24 hours resource.
1374: This should be only done when 24 hours check is unchecked and resource is not used on repetitive line*/
1375: /*Bug 9355406: fixed regression caused by 7015594, if resource start day is the end of the shift, it wont be treated as 24 hrs resource*/
1376: if(x_resTbls.avail24Flag(p_idx) <> wip_constants.yes AND p_repLineID is null AND l_shifts.startDate(i)=l_shifts.endDate(i)) then
1377: l_shiftLen := 86400;
1378: end if;
1379:
1380: if (l_logLevel <= wip_constants.full_logging) then
1376: if(x_resTbls.avail24Flag(p_idx) <> wip_constants.yes AND p_repLineID is null AND l_shifts.startDate(i)=l_shifts.endDate(i)) then
1377: l_shiftLen := 86400;
1378: end if;
1379:
1380: if (l_logLevel <= wip_constants.full_logging) then
1381: wip_logger.log('shiftLen(HRS) is ' || round(l_shiftLen*24, g_precision), l_retStatus);
1382: end if;
1383:
1384: if(round(l_shiftLen, g_precision) = 0) then
1381: wip_logger.log('shiftLen(HRS) is ' || round(l_shiftLen*24, g_precision), l_retStatus);
1382: end if;
1383:
1384: if(round(l_shiftLen, g_precision) = 0) then
1385: if (l_logLevel <= wip_constants.full_logging) then
1386: wip_logger.log('skipping shift (no usage)', l_retStatus);
1387: end if;
1388: goto NO_FULFILL_USAGE;--end of loop
1389: end if;
1390:
1391:
1392: if(l_startDate is null) then
1393: l_startDate := l_fromDate;
1394: if (l_logLevel <= wip_constants.full_logging) then
1395: wip_logger.log('calculated resource start date:' || to_char(l_startDate, g_logDateFmt), l_retStatus);
1396: end if;
1397: end if;
1398: /*Bug 9355406: fixed regression caused by 7136375. if remaining usage is one day:
1400: for 24 hours resource, condiser shift cannot fullfilled resource usage and loop to next working day
1401: */
1402: -- Commented below condition for bug #13388579
1403: -- if(round(l_remUsage, g_precision) < round(l_shiftLen, g_precision) or /* Fix for bug 7136375.If time remaining is one day then we need to loop to next working day*/
1404: -- (round(l_remUsage, g_precision) = round(l_shiftLen, g_precision) and x_resTbls.avail24Flag(p_idx) <> wip_constants.yes)) then
1405: --shift fullfilled resource usage (round to approximately seconds)
1406: /* Bug 13388579 : If there exist a resource of type "next" in backward scheduling then no need to move end date to next working day if its end on non working day
1407: because in backward scheduling we wont move end date to next working day if it end on non working day.
1408: */
1406: /* Bug 13388579 : If there exist a resource of type "next" in backward scheduling then no need to move end date to next working day if its end on non working day
1407: because in backward scheduling we wont move end date to next working day if it end on non working day.
1408: */
1409: if ((g_reset_date=0 and ( (round(l_remUsage, g_precision) < round(l_shiftLen, g_precision)) or
1410: (round(l_remUsage, g_precision) = round(l_shiftLen, g_precision) and x_resTbls.avail24Flag(p_idx) <> wip_constants.yes))) or (g_reset_date=1 and (round(l_remUsage, g_precision) <= round(l_shiftLen, g_precision)) ) ) then
1411:
1412: if (l_logLevel <= wip_constants.full_logging) then
1413: wip_logger.log('calculated resource start date:' || to_char(l_startDate, g_logDateFmt), l_retStatus);
1414: end if;
1408: */
1409: if ((g_reset_date=0 and ( (round(l_remUsage, g_precision) < round(l_shiftLen, g_precision)) or
1410: (round(l_remUsage, g_precision) = round(l_shiftLen, g_precision) and x_resTbls.avail24Flag(p_idx) <> wip_constants.yes))) or (g_reset_date=1 and (round(l_remUsage, g_precision) <= round(l_shiftLen, g_precision)) ) ) then
1411:
1412: if (l_logLevel <= wip_constants.full_logging) then
1413: wip_logger.log('calculated resource start date:' || to_char(l_startDate, g_logDateFmt), l_retStatus);
1414: end if;
1415: x_resTbls.startDate(p_idx) := l_startDate;
1416: x_resTbls.endDate(p_idx) := l_fromDate + l_remUsage;
1419: x_resTbls.usgEndDate.extend(1);
1420: x_resTbls.usgCumMinProcTime.extend(1);
1421:
1422: l_usgIdx := x_resTbls.usgStartDate.count;
1423: if (l_logLevel <= wip_constants.full_logging) then
1424: wip_logger.log('idx is ' || l_usgIdx, l_retStatus);
1425: wip_logger.log('count is ' || x_resTbls.usgStartIdx.count, l_retStatus);
1426: wip_logger.log('val is ' || x_resTbls.usgStartIdx(p_idx), l_retStatus);
1427: end if;
1433:
1434: --shift fulfilled resource => usage end time is resource end time
1435: x_resTbls.usgEndDate(l_usgIdx) := x_resTbls.endDate(p_idx);
1436: if(l_isFirstUsg) then
1437: if (l_logLevel <= wip_constants.full_logging) then
1438: wip_logger.log('first usage', l_retStatus);
1439: end if;
1440: l_isFirstUsg := false;
1441: l_prevProcTime := 0;
1439: end if;
1440: l_isFirstUsg := false;
1441: l_prevProcTime := 0;
1442: else
1443: if (l_logLevel <= wip_constants.full_logging) then
1444: wip_logger.log('not first usage', l_retStatus);
1445: end if;
1446: l_prevProcTime := x_resTbls.usgCumMinProcTime(l_usgIdx - 1);
1447: end if;
1449: x_resTbls.usgCumMinProcTime(l_usgIdx) := l_prevProcTime +
1450: (24*60)*(x_resTbls.usgEndDate(l_usgIdx) -
1451: x_resTbls.usgStartDate(l_usgIdx));
1452:
1453: if (l_logLevel <= wip_constants.full_logging) then
1454: wip_logger.log('start date is ' || to_char(x_resTbls.startDate(p_idx), g_logDateFmt), l_retStatus);
1455: wip_logger.log('end date is ' || to_char(x_resTbls.endDate(p_idx), g_logDateFmt), l_retStatus);
1456: wip_logger.log('usage:' || to_char(x_resTbls.usgStartDate(l_usgIdx), g_logDateFmt) || ' - ' ||
1457: to_char(x_resTbls.usgEndDate(l_usgIdx), g_logDateFmt), l_retStatus);
1485: end if;
1486: x_resTbls.usgCumMinProcTime(l_usgIdx) := l_prevProcTime +
1487: (24*60)*(x_resTbls.usgEndDate(l_usgIdx) -
1488: x_resTbls.usgStartDate(l_usgIdx));
1489: if (l_logLevel <= wip_constants.full_logging) then
1490: wip_logger.log('exhausted shift. remaining usage(HRS) is ' || round(l_remUsage*24, g_precision), l_retStatus);
1491: wip_logger.log('usage:' || to_char(x_resTbls.usgStartDate(l_usgIdx), g_logDateFmt) || ' - ' ||
1492: to_char(x_resTbls.usgEndDate(l_usgIdx), g_logDateFmt), l_retStatus);
1493: wip_logger.log('cum usage time:' || x_resTbls.usgCumMinProcTime(l_usgIdx), l_retStatus);
1502: l_cursorStartDate := l_cursorStartDate + g_dateCursorLen;
1503:
1504: --if the next start date is after the end of the calendar, then we can't schedule anything
1505: if(l_cursorStartDate > p_maxDate) then
1506: if (l_logLevel <= wip_constants.full_logging) then
1507: wip_logger.log('exhausted calendar. remaining usage(HRS) is ' || round(l_remUsage*24, g_precision), l_retStatus);
1508: end if;
1509: fnd_message.set_name('WIP', 'WIP_NO_CALENDAR');
1510: fnd_msg_pub.add;
1511: raise fnd_api.g_exc_unexpected_error;
1512: end if;
1513: end if;
1514: end loop;
1515: if(l_logLevel <= wip_constants.trace_logging) then
1516: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.forwardSchResource',
1517: p_procReturnStatus => x_returnStatus,
1518: p_msg => 'success',
1519: x_returnStatus => l_retStatus);
1520: end if;
1521: exception
1522: when fnd_api.g_exc_unexpected_error then
1523: x_returnStatus := fnd_api.g_ret_sts_unexp_error;
1524: if(l_logLevel <= wip_constants.trace_logging) then
1525: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.forwardSchResource',
1526: p_procReturnStatus => x_returnStatus,
1527: p_msg => 'error',
1528: x_returnStatus => l_retStatus);
1531: x_returnStatus := fnd_api.g_ret_sts_unexp_error;
1532: fnd_msg_pub.add_exc_msg(p_pkg_name => 'wip_infResSched_grp',
1533: p_procedure_name => 'forwardSchResource',
1534: p_error_text => SQLERRM);
1535: if(l_logLevel <= wip_constants.trace_logging) then
1536: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.forwardSchResource',
1537: p_procReturnStatus => x_returnStatus,
1538: p_msg => 'unexp error: ' || SQLERRM,
1539: x_returnStatus => l_retStatus);
1612: n Number;
1613:
1614: BEGIN
1615:
1616: if (l_logLevel <= wip_constants.trace_logging) then
1617: l_params(1).paramName := 'p_resID';
1618: l_params(1).paramValue := p_resID;
1619: l_params(2).paramName := 'p_deptID';
1620: l_params(2).paramValue := p_deptID;
1632: fetch c_capacityDtls
1633: bulk collect into l_capacity ;
1634: close c_capacityDtls;
1635:
1636: if(l_logLevel <= wip_constants.trace_logging) then
1637: wip_logger.log('l_capacity.count = ' || l_capacity.count, l_retStatus);
1638: end if;
1639:
1640: /*Bug 16322124: if there is no capacity exception being found, return and do nothing.
1639:
1640: /*Bug 16322124: if there is no capacity exception being found, return and do nothing.
1641: This will avoid shift being deleted if customer define shift start time and endtime the same as 00:00:00*/
1642: if(l_capacity.count = 0) then
1643: if(l_logLevel <= wip_constants.trace_logging) then
1644: wip_logger.log('No Capacity Exception found. exiting capacity exception', l_retStatus);
1645: end if;
1646: return;
1647: end if;
1669: l_capacity.DELETE;
1670: l_capacity := l_capacity_2;
1671:
1672: l_capacity_2.DELETE;
1673: if (l_logLevel <= wip_constants.trace_logging) then
1674: wip_logger.log('l capacity count is :'||l_capacity.Count, l_retStatus);
1675: end if;
1676: -------------------
1677: for i in 1..l_capacity.count loop
1674: wip_logger.log('l capacity count is :'||l_capacity.Count, l_retStatus);
1675: end if;
1676: -------------------
1677: for i in 1..l_capacity.count loop
1678: if (l_logLevel <= wip_constants.trace_logging) then
1679: wip_logger.log('****************************modified capacity exception records**************************************', l_retStatus);
1680: wip_logger.log('shiftNum = ' || l_capacity(i).shiftNum , l_retStatus);
1681: wip_logger.log('fromDate = ' || l_capacity(i).fromDate , l_retStatus);
1682: wip_logger.log('toDate = ' || l_capacity(i).toDate , l_retStatus);
1693: l_firstRow := x_shifts.shiftNum.FIRST;
1694: l_lastRow := x_shifts.shiftNum.LAST;
1695: j := l_firstRow;
1696: flag := FALSE;
1697: if(l_logLevel <= wip_constants.trace_logging) then
1698: wip_logger.log('inside for. i = ' || i || ' n count = ' || l_capacity.count, l_retStatus);
1699: wip_logger.log('l_firstRow = ' || l_firstRow, l_retStatus);
1700: wip_logger.log('l_lastRow = ' || l_lastRow, l_retStatus);
1701: end if;
1698: wip_logger.log('inside for. i = ' || i || ' n count = ' || l_capacity.count, l_retStatus);
1699: wip_logger.log('l_firstRow = ' || l_firstRow, l_retStatus);
1700: wip_logger.log('l_lastRow = ' || l_lastRow, l_retStatus);
1701: end if;
1702: if (l_capacity(i).actionType = wip_constants.DELETE_WKDY) THEN -- delete a working day
1703: /* If more than one shift exists for a day (in case of shift with breakups) then it will
1704: delete all those shifts for that day. */
1705: if(l_logLevel <= wip_constants.trace_logging) then
1706: wip_logger.log('inside delete a working day', l_retStatus);
1701: end if;
1702: if (l_capacity(i).actionType = wip_constants.DELETE_WKDY) THEN -- delete a working day
1703: /* If more than one shift exists for a day (in case of shift with breakups) then it will
1704: delete all those shifts for that day. */
1705: if(l_logLevel <= wip_constants.trace_logging) then
1706: wip_logger.log('inside delete a working day', l_retStatus);
1707: end if;
1708: WHILE (j <= l_lastRow) LOOP
1709: /* Changed IF condition to WHILE loop to delete all the shifts for the given day */
1725: END LOOP;
1726: j := x_shifts.shiftNum.NEXT(j);
1727: END LOOP;
1728:
1729: ELSIF (l_capacity(i).actionType = wip_constants.ADD_WKDY) THEN -- add a non-working day
1730: if(l_logLevel <= wip_constants.trace_logging) then
1731: wip_logger.log('inside adding a non-working day', l_retStatus);
1732: end if;
1733:
1726: j := x_shifts.shiftNum.NEXT(j);
1727: END LOOP;
1728:
1729: ELSIF (l_capacity(i).actionType = wip_constants.ADD_WKDY) THEN -- add a non-working day
1730: if(l_logLevel <= wip_constants.trace_logging) then
1731: wip_logger.log('inside adding a non-working day', l_retStatus);
1732: end if;
1733:
1734: /* Bug 12627423: Added extra if statement to check that the fromDate of the capacity exception is bigger
1763: j := x_shifts.shiftNum.NEXT(j);
1764: END LOOP;
1765: END IF;
1766:
1767: ELSIF l_capacity(i).actionType = wip_constants.MODIFY_WKDY THEN -- modify capacity - modify or reduce capacity
1768: if(l_logLevel <= wip_constants.trace_logging) then
1769: wip_logger.log('inside modifying capacity', l_retStatus);
1770: end if;
1771: WHILE (j <= l_lastRow) LOOP
1764: END LOOP;
1765: END IF;
1766:
1767: ELSIF l_capacity(i).actionType = wip_constants.MODIFY_WKDY THEN -- modify capacity - modify or reduce capacity
1768: if(l_logLevel <= wip_constants.trace_logging) then
1769: wip_logger.log('inside modifying capacity', l_retStatus);
1770: end if;
1771: WHILE (j <= l_lastRow) LOOP
1772: l_shiftDate := x_shifts.startDate(j);
1769: wip_logger.log('inside modifying capacity', l_retStatus);
1770: end if;
1771: WHILE (j <= l_lastRow) LOOP
1772: l_shiftDate := x_shifts.startDate(j);
1773: if(l_logLevel <= wip_constants.trace_logging) then
1774: wip_logger.log('1: j = ' || j || ' l_lastRow = ' || l_lastRow, l_retStatus);
1775: wip_logger.log('inside while: j = ' || j || ' startDate = ' || to_char(x_shifts.startDate(j),g_logDateFmt), l_retStatus);
1776: wip_logger.log('inside while: j = ' || j || ' fromDate = ' || to_char(l_capacity(i).fromDate,g_logDateFmt), l_retStatus);
1777: wip_logger.log('inside while: j = ' || j || ' toDate = ' || to_char(l_capacity(i).toDate,g_logDateFmt), l_retStatus);
1791:
1792: if j is null then
1793: j := l_lastRow;
1794: end if;
1795: if (l_logLevel <= wip_constants.trace_logging) then
1796: wip_logger.log('After j is null code', l_retStatus);
1797: end if;
1798: IF ( l_capacity(i).capacityChange > 0 ) THEN -- add capacity
1799: /*Idea here is that if a new capacity is added and one of it's dates matches with shift dates then we adjust shift start
1797: end if;
1798: IF ( l_capacity(i).capacityChange > 0 ) THEN -- add capacity
1799: /*Idea here is that if a new capacity is added and one of it's dates matches with shift dates then we adjust shift start
1800: or end time. In all other cases we will add a new time in shift table for the exception to add the new capacity. */
1801: if(l_logLevel <= wip_constants.trace_logging) then
1802: wip_logger.log('ADD capacity', l_retStatus);
1803: end if;
1804: if (x_shifts.endDate(k) = (l_capacity(i).fromDate + l_capacity(i).fromTime/86400)) then
1805: if(l_logLevel <= wip_constants.trace_logging) then
1801: if(l_logLevel <= wip_constants.trace_logging) then
1802: wip_logger.log('ADD capacity', l_retStatus);
1803: end if;
1804: if (x_shifts.endDate(k) = (l_capacity(i).fromDate + l_capacity(i).fromTime/86400)) then
1805: if(l_logLevel <= wip_constants.trace_logging) then
1806: wip_logger.log('inside IF.1: endDate = ' || to_char(x_shifts.endDate(k), g_logDateFmt), l_retStatus);
1807: end if;
1808: x_shifts.endDate(k) := x_shifts.endDate(k) + ((l_capacity(i).toTime - l_capacity(i).fromTime)/86400 );
1809: if(l_logLevel <= wip_constants.trace_logging) then
1805: if(l_logLevel <= wip_constants.trace_logging) then
1806: wip_logger.log('inside IF.1: endDate = ' || to_char(x_shifts.endDate(k), g_logDateFmt), l_retStatus);
1807: end if;
1808: x_shifts.endDate(k) := x_shifts.endDate(k) + ((l_capacity(i).toTime - l_capacity(i).fromTime)/86400 );
1809: if(l_logLevel <= wip_constants.trace_logging) then
1810: wip_logger.log('inside IF.1 endDate = ' || to_char(x_shifts.endDate(k),g_logDateFmt), l_retStatus);
1811: wip_logger.log('inside elseIF.1: startDate = ' || to_char(x_shifts.startDate(k),g_logDateFmt), l_retStatus);
1812: end if;
1813: elsif (x_shifts.startDate(k) = (l_capacity(i).toDate + l_capacity(i).toTime/86400)) then
1810: wip_logger.log('inside IF.1 endDate = ' || to_char(x_shifts.endDate(k),g_logDateFmt), l_retStatus);
1811: wip_logger.log('inside elseIF.1: startDate = ' || to_char(x_shifts.startDate(k),g_logDateFmt), l_retStatus);
1812: end if;
1813: elsif (x_shifts.startDate(k) = (l_capacity(i).toDate + l_capacity(i).toTime/86400)) then
1814: if(l_logLevel <= wip_constants.trace_logging) then
1815: wip_logger.log('inside elseIF.1: startDate = ' || to_char(x_shifts.startDate(k),g_logDateFmt), l_retStatus);
1816: end if;
1817: x_shifts.startDate(k) := x_shifts.startDate(k) - ((l_capacity(i).toTime - l_capacity(i).fromTime)/86400) ;
1818: if(l_logLevel <= wip_constants.trace_logging) then
1814: if(l_logLevel <= wip_constants.trace_logging) then
1815: wip_logger.log('inside elseIF.1: startDate = ' || to_char(x_shifts.startDate(k),g_logDateFmt), l_retStatus);
1816: end if;
1817: x_shifts.startDate(k) := x_shifts.startDate(k) - ((l_capacity(i).toTime - l_capacity(i).fromTime)/86400) ;
1818: if(l_logLevel <= wip_constants.trace_logging) then
1819: wip_logger.log('inside elseIF.1 startDate = ' || to_char(x_shifts.startDate(k),g_logDateFmt), l_retStatus);
1820: end if;
1821: elsif (x_shifts.endDate(k) = x_shifts.startDate(k)) THEN
1822: -- this should only come when user reduce the entire shift capacity
1827: --Fix bug 10632963, only add the shift if the capacity start and end date is not the same
1828: --as shift start date and end date
1829: if((x_shifts.startDate(k) <> (l_capacity(i).fromDate + l_capacity(i).fromTime/86400)) and
1830: (x_shifts.endDate(k) <> (l_capacity(i).toDate + l_capacity(i).toTime/86400))) then
1831: if(l_logLevel <= wip_constants.trace_logging) then
1832: wip_logger.log('inside else.1:', l_retStatus);
1833: end if;
1834: if (x_shifts.startDate(k) > (l_capacity(i).fromDate + l_capacity(i).fromTime/86400)) then
1835: /* capacity shd be added before the current shift */
1837:
1838: if (l_counter IS NULL) then
1839: l_counter := j;
1840: end if;
1841: if(l_logLevel <= wip_constants.trace_logging) then
1842: wip_logger.log('inside else.1: j-1: l_counter = ' || l_counter, l_retStatus);
1843: end if;
1844: else
1845: /* capcacity should be added after the current shift */
1843: end if;
1844: else
1845: /* capcacity should be added after the current shift */
1846: l_counter := j;
1847: if(l_logLevel <= wip_constants.trace_logging) then
1848: wip_logger.log('inside else.1: j: l_counter = ' || l_counter, l_retStatus);
1849: end if;
1850: end if;
1851: x_shifts.shiftNum.extend; -- extend the xshifts table by one row and insert the day and then shift remaning days
1867: j := x_shifts.shiftNum.NEXT(j); -- need to move shift pointer as current shift is added and j should point to next shift.
1868: end if;
1869: end if;
1870: ELSE -- reduce capacity
1871: if(l_logLevel <= wip_constants.trace_logging) then
1872: wip_logger.log('inside else.2', l_retStatus);
1873: wip_logger.log('capacity units is ' || l_capacity(i).capacityUnits, l_retStatus);
1874: end if;
1875:
1878: I replaced the if statement with one that will always return true when capacityUnit is null.
1879: */
1880:
1881: if(nvl(l_capacity(i).capacityUnits, 0) <= (abs(l_capacity(i).capacityChange))) THEN
1882: if(l_logLevel <= wip_constants.trace_logging) then
1883: wip_logger.log('inside else.2 capacityChange = ' || l_capacity(i).capacityChange, l_retStatus);
1884: /*Idea here is that if a capacity is reduced and one of it's dates matches with shift dates then we adjust
1885: shift start or end time. In all other cases we will reduce time in shift table for the exception to reduce the new capacity.*/
1886: wip_logger.log('before IF.2: endDate = ' || to_char(x_shifts.endDate(k),g_logDateFmt), l_retStatus);
1887: wip_logger.log('before IF.2: l_cap.toDateTime = ' || to_char(l_capacity(i).toDate + l_capacity(i).toTime/86400,g_logDateFmt), l_retStatus);
1888: wip_logger.log('before IF.2: l_cap.fromDateTime = ' || to_char(l_capacity(i).fromDate + l_capacity(i).fromTime/86400,g_logDateFmt), l_retStatus);
1889: end if;
1890: if x_shifts.endDate(k) = (l_capacity(i).toDate + l_capacity(i).toTime/86400) then
1891: if(l_logLevel <= wip_constants.trace_logging) then
1892: wip_logger.log('inside IF.2: endDate = ' || to_char(x_shifts.endDate(k),g_logDateFmt), l_retStatus);
1893: end if;
1894: x_shifts.endDate(k) := x_shifts.endDate(k) - (l_capacity(i).toTime - l_capacity(i).fromTime)/86400;
1895:
1901: x_shifts.startDate(k) := x_shifts.startDate(m);
1902: x_shifts.endDate(k) := x_shifts.endDate(m);
1903: k := m;
1904: END LOOP;
1905: if (l_logLevel <= wip_constants.trace_logging) then
1906: wip_logger.log('inside IF.2-: after while loop', l_retStatus);
1907: end if;
1908: x_shifts.shiftNum.trim(); -- trim the last row
1909: x_shifts.startDate.trim();
1907: end if;
1908: x_shifts.shiftNum.trim(); -- trim the last row
1909: x_shifts.startDate.trim();
1910: x_shifts.endDate.trim();
1911: if (l_logLevel <= wip_constants.trace_logging) then
1912: wip_logger.log('inside IF.2-: after trim', l_retStatus);
1913: end if;
1914: l_lastRow := x_shifts.shiftNum.LAST; -- updated last row
1915: if (l_logLevel <= wip_constants.trace_logging) then
1911: if (l_logLevel <= wip_constants.trace_logging) then
1912: wip_logger.log('inside IF.2-: after trim', l_retStatus);
1913: end if;
1914: l_lastRow := x_shifts.shiftNum.LAST; -- updated last row
1915: if (l_logLevel <= wip_constants.trace_logging) then
1916: wip_logger.log('inside IF.2-: after l_lastRow = '||l_lastRow, l_retStatus);
1917: end if;
1918: j := x_shifts.shiftNum.PRIOR(j); -- update j as one shift record is deleted
1919: if (l_logLevel <= wip_constants.trace_logging) then
1915: if (l_logLevel <= wip_constants.trace_logging) then
1916: wip_logger.log('inside IF.2-: after l_lastRow = '||l_lastRow, l_retStatus);
1917: end if;
1918: j := x_shifts.shiftNum.PRIOR(j); -- update j as one shift record is deleted
1919: if (l_logLevel <= wip_constants.trace_logging) then
1920: wip_logger.log('inside IF.2-: after updating j = '||j, l_retStatus);
1921: end if;
1922: end if;*/
1923: --
1921: end if;
1922: end if;*/
1923: --
1924:
1925: if(l_logLevel <= wip_constants.trace_logging) then
1926: wip_logger.log('inside IF.2--: endDate = .... = ', l_retStatus);
1927:
1928: end if;
1929: elsif x_shifts.startDate(k) = (l_capacity(i).fromDate + l_capacity(i).fromTime/86400) then
1926: wip_logger.log('inside IF.2--: endDate = .... = ', l_retStatus);
1927:
1928: end if;
1929: elsif x_shifts.startDate(k) = (l_capacity(i).fromDate + l_capacity(i).fromTime/86400) then
1930: if(l_logLevel <= wip_constants.trace_logging) then
1931: wip_logger.log('inside ELSEIF.2: endDate = ' || to_char(x_shifts.startDate(k),g_logDateFmt), l_retStatus);
1932: end if;
1933: x_shifts.startDate(k) := x_shifts.startDate(k) + (l_capacity(i).toTime - l_capacity(i).fromTime)/86400 ;
1934: if(l_logLevel <= wip_constants.trace_logging) then
1930: if(l_logLevel <= wip_constants.trace_logging) then
1931: wip_logger.log('inside ELSEIF.2: endDate = ' || to_char(x_shifts.startDate(k),g_logDateFmt), l_retStatus);
1932: end if;
1933: x_shifts.startDate(k) := x_shifts.startDate(k) + (l_capacity(i).toTime - l_capacity(i).fromTime)/86400 ;
1934: if(l_logLevel <= wip_constants.trace_logging) then
1935: wip_logger.log('inside ELSEIF.2--: endDate = ' || to_char(x_shifts.startDate(k),g_logDateFmt), l_retStatus);
1936: end if;
1937: else
1938: if(l_logLevel <= wip_constants.trace_logging) then
1934: if(l_logLevel <= wip_constants.trace_logging) then
1935: wip_logger.log('inside ELSEIF.2--: endDate = ' || to_char(x_shifts.startDate(k),g_logDateFmt), l_retStatus);
1936: end if;
1937: else
1938: if(l_logLevel <= wip_constants.trace_logging) then
1939: wip_logger.log('inside else.2--: endDate = ' || to_char(x_shifts.endDate(k),g_logDateFmt), l_retStatus);
1940: end if;
1941: -- Split the shift time into two here to reduce capacity.
1942: -- Add a new record in x_shifts table after Kth record.
2002: j := j+1;
2003: end if;
2004: end loop;
2005:
2006: IF(l_logLevel <= wip_constants.trace_logging) then
2007: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.capacityExceptions',
2008: p_procReturnStatus => x_returnStatus,
2009: p_msg => 'success',
2010: x_returnStatus => l_retStatus);
2011: END IF;
2012: EXCEPTION
2013: WHEN fnd_api.g_exc_unexpected_error THEN
2014: x_returnStatus := fnd_api.g_ret_sts_unexp_error;
2015: if(l_logLevel <= wip_constants.trace_logging) then
2016: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.capacityExceptions',
2017: p_procReturnStatus => x_returnStatus,
2018: p_msg => 'error',
2019: x_returnStatus => l_retStatus);
2022: x_returnStatus := fnd_api.g_ret_sts_unexp_error;
2023: fnd_msg_pub.add_exc_msg(p_pkg_name => 'wip_infResSched_grp',
2024: p_procedure_name => 'capacityExceptions',
2025: p_error_text => SQLERRM);
2026: if(l_logLevel <= wip_constants.trace_logging) then
2027: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.capacityExceptions',
2028: p_procReturnStatus => x_returnStatus,
2029: p_msg => 'unexp error: ' || SQLERRM,
2030: x_returnStatus => l_retStatus);
2051: i number;
2052: l_schedulingBatch boolean := false;
2053: l_doneSchedBatch boolean;
2054: begin
2055: if (l_logLevel <= wip_constants.trace_logging) then
2056: l_params(1).paramName := 'p_repLineID';
2057: l_params(1).paramValue := p_repLineID;
2058: l_params(2).paramName := 'p_startDate';
2059: l_params(2).paramValue := to_char(p_startDate, g_logDateFmt);
2075: where mp.organization_id = p_orgID
2076: and mp.calendar_code = bc.calendar_code;
2077:
2078: while(getNextResIdx(p_range, p_schedFlag, g_forward, x_resTbls, i)) loop
2079: if (l_logLevel <= wip_constants.full_logging) then
2080: wip_logger.log('begin scheduling resource:' || x_resTbls.resID(i), l_retStatus);
2081: wip_logger.log(' operation:' || x_resTbls.opSeqNum(i), l_retStatus);
2082: wip_logger.log(' res seq num:' || x_resTbls.resSeqNum(i), l_retStatus);
2083: wip_logger.log(' schedule seq num:' || x_resTbls.schedSeqNum(i), l_retStatus);
2095: if(l_prevResIdx is not null and
2096: x_resTbls.schedSeqNum(i) = x_resTbls.schedSeqNum(l_prevResIdx) and
2097: x_resTbls.opSeqNum(i) = x_resTbls.opSeqNum(l_prevResIdx)) then
2098: l_schedulingBatch := true;
2099: if (l_logLevel <= wip_constants.full_logging) then
2100: wip_logger.log('setting sched batch to true', l_retStatus);
2101: end if;
2102: --just finished scheduling batch
2103: elsif(l_schedulingBatch) then
2102: --just finished scheduling batch
2103: elsif(l_schedulingBatch) then
2104: l_schedulingBatch := false;
2105: l_doneSchedBatch := true;
2106: if (l_logLevel <= wip_constants.full_logging) then
2107: wip_logger.log('setting done sched batch to true', l_retStatus);
2108: end if;
2109: end if;
2110:
2124:
2125: l_prevResIdx := i;
2126: end loop;
2127:
2128: if(l_logLevel <= wip_constants.trace_logging) then
2129: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.forwardSchedule',
2130: p_procReturnStatus => x_returnStatus,
2131: p_msg => 'success',
2132: x_returnStatus => l_retStatus);
2133: end if;
2134: exception
2135: when fnd_api.g_exc_unexpected_error then
2136: x_returnStatus := fnd_api.g_ret_sts_unexp_error;
2137: if(l_logLevel <= wip_constants.trace_logging) then
2138: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.forwardSchedule',
2139: p_procReturnStatus => x_returnStatus,
2140: p_msg => 'error',
2141: x_returnStatus => l_retStatus);
2144: x_returnStatus := fnd_api.g_ret_sts_unexp_error;
2145: fnd_msg_pub.add_exc_msg(p_pkg_name => 'wip_infResSched_grp',
2146: p_procedure_name => 'forwardSchedule',
2147: p_error_text => SQLERRM);
2148: if(l_logLevel <= wip_constants.trace_logging) then
2149: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.forwardSchedule',
2150: p_procReturnStatus => x_returnStatus,
2151: p_msg => 'unexp error: ' || SQLERRM,
2152: x_returnStatus => l_retStatus);
2166: l_minStartDate date;
2167: l_logLevel NUMBER := fnd_log.g_current_runtime_level;
2168: l_bool boolean;
2169: begin
2170: if (l_logLevel <= wip_constants.trace_logging) then
2171: l_params(1).paramName := 'p_range(1)';
2172: l_params(1).paramValue := p_range(1);
2173: l_params(2).paramName := 'p_range(2)';
2174: l_params(2).paramValue := p_range(2);
2192: --latest end date to use as the next resource's start date
2193: i := p_curIdx;
2194:
2195: if(p_doneSchedBatch) then
2196: if(l_logLevel <= wip_constants.full_logging) then
2197: wip_logger.log('done scheduling batch', l_retStatus);
2198: end if;
2199: while(getNextResIdx(p_range, p_schedFlag, g_forward, p_resTbls, i)) loop
2200: if(l_logLevel <= wip_constants.full_logging) then
2196: if(l_logLevel <= wip_constants.full_logging) then
2197: wip_logger.log('done scheduling batch', l_retStatus);
2198: end if;
2199: while(getNextResIdx(p_range, p_schedFlag, g_forward, p_resTbls, i)) loop
2200: if(l_logLevel <= wip_constants.full_logging) then
2201: wip_logger.log('in loop', l_retStatus);
2202: wip_logger.log('resID' || p_resTbls.resID(i), l_retStatus);
2203: wip_logger.log('opSeq' || p_resTbls.opSeqNum(i), l_retStatus);
2204: wip_logger.log('schSeq' || p_resTbls.schedSeqNum(i), l_retStatus);
2206: end if;
2207: if(p_resTbls.schedSeqNum(i) = p_resTbls.schedSeqNum(p_prevIdx) and
2208: p_resTbls.opSeqNum(i) = p_resTbls.opSeqNum(p_prevIdx)) then
2209: l_minStartDate := least(nvl(l_minStartDate, p_resTbls.startDate(i)), p_resTbls.startDate(i));
2210: if(l_logLevel <= wip_constants.full_logging) then
2211: wip_logger.log('resource in batch. startDate:' || to_char(p_resTbls.startDate(i), g_logDateFmt), l_retStatus);
2212: end if;
2213: else
2214: if(l_logLevel <= wip_constants.full_logging) then
2210: if(l_logLevel <= wip_constants.full_logging) then
2211: wip_logger.log('resource in batch. startDate:' || to_char(p_resTbls.startDate(i), g_logDateFmt), l_retStatus);
2212: end if;
2213: else
2214: if(l_logLevel <= wip_constants.full_logging) then
2215: wip_logger.log('resource not in batch.', l_retStatus);
2216: end if;
2217: exit;
2218: end if;
2221: l_bool := (getNextResIdx(p_range, p_schedFlag, g_forward, p_resTbls, i));
2222: l_minStartDate := p_resTbls.startDate(i);
2223: end if;
2224:
2225: if (l_logLevel <= wip_constants.trace_logging) then
2226: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.getEndDate',
2227: p_procReturnStatus => to_char(l_minStartDate),
2228: p_msg => 'finished scheduling',
2229: x_returnStatus => l_retStatus);
2334: l_prevProcTime NUMBER := 0;
2335: l_dummy NUMBER; /* Bug 5660475 */
2336: begin
2337:
2338: if (l_logLevel <= wip_constants.trace_logging) then
2339: l_params(1).paramName := 'p_orgID';
2340: l_params(1).paramValue := p_orgID;
2341: l_params(2).paramName := 'p_endDate';
2342: l_params(2).paramValue := to_char(p_endDate, g_logDateFmt);
2352: end if;
2353: /* Fix for bug 5660475: If dealing with shift resource, first check if shifts are setup fine. */
2354: -- Bug 16322124 . Modified for Performance. Validation is simplified since all shifts are assumed to have valid time setups .
2355: if( p_repLineID is null
2356: and x_resTbls.avail24Flag(p_idx) = wip_constants.no
2357: and x_resTbls.schedFlag(p_idx) <> wip_constants.sched_no) then
2358: if (l_logLevel <= wip_constants.trace_logging) then
2359: wip_logger.log('This is a shift resource. Need to validate shift setup', l_retStatus);
2360: end if;
2353: /* Fix for bug 5660475: If dealing with shift resource, first check if shifts are setup fine. */
2354: -- Bug 16322124 . Modified for Performance. Validation is simplified since all shifts are assumed to have valid time setups .
2355: if( p_repLineID is null
2356: and x_resTbls.avail24Flag(p_idx) = wip_constants.no
2357: and x_resTbls.schedFlag(p_idx) <> wip_constants.sched_no) then
2358: if (l_logLevel <= wip_constants.trace_logging) then
2359: wip_logger.log('This is a shift resource. Need to validate shift setup', l_retStatus);
2360: end if;
2361: begin
2354: -- Bug 16322124 . Modified for Performance. Validation is simplified since all shifts are assumed to have valid time setups .
2355: if( p_repLineID is null
2356: and x_resTbls.avail24Flag(p_idx) = wip_constants.no
2357: and x_resTbls.schedFlag(p_idx) <> wip_constants.sched_no) then
2358: if (l_logLevel <= wip_constants.trace_logging) then
2359: wip_logger.log('This is a shift resource. Need to validate shift setup', l_retStatus);
2360: end if;
2361: begin
2362: -- Bug 16322124 . Modified for Performance.
2373: and brs.shift_num is not null
2374: and rownum = 1);
2375: exception
2376: when NO_DATA_FOUND then
2377: if (l_logLevel <= wip_constants.trace_logging) then
2378: wip_logger.log('Error: Missing shifts or shift times!', l_retStatus);
2379: end if;
2380: fnd_message.set_name('WIP', 'WIP_SHIFT_RESOURCE');
2381: fnd_message.set_token('ENTITY1', x_resTbls.resSeqNum(p_idx));
2389: x_resTbls.usgEndIdx(p_idx) := null;
2390:
2391: loop
2392: exit when l_resourceScheduled;
2393: if (l_logLevel <= wip_constants.full_logging) then
2394: wip_logger.log('cursor start date: ' || to_char((l_cursorEndDate - (g_dateCursorLen - 1/86400)), g_logDateFmt), l_retStatus);
2395: wip_logger.log('cursor end date: ' || to_char(l_cursorEndDate, g_logDateFmt), l_retStatus);
2396: end if;
2397:
2396: end if;
2397:
2398: --for v_endDate, subtract a second to avoid overlap between cursors.
2399: if(p_repLineID is not null) then
2400: if (l_logLevel <= wip_constants.trace_logging) then
2401: wip_logger.log('Using c_repTimes', l_retStatus);
2402: end if;
2403: open c_repTimes(v_repLineID => p_repLineID,
2404: v_orgID => p_orgID,
2408: bulk collect into l_shifts.shiftNum,
2409: l_shifts.startDate,
2410: l_shifts.endDate;
2411: close c_repTimes;
2412: elsif(x_resTbls.avail24Flag(p_idx) = wip_constants.yes) then
2413: if (l_logLevel <= wip_constants.trace_logging) then
2414: wip_logger.log('Using c_24HrTimes', l_retStatus);
2415: end if;
2416: open c_24HrTimes(v_orgID => p_orgID,
2409: l_shifts.startDate,
2410: l_shifts.endDate;
2411: close c_repTimes;
2412: elsif(x_resTbls.avail24Flag(p_idx) = wip_constants.yes) then
2413: if (l_logLevel <= wip_constants.trace_logging) then
2414: wip_logger.log('Using c_24HrTimes', l_retStatus);
2415: end if;
2416: open c_24HrTimes(v_orgID => p_orgID,
2417: v_startDate => l_cursorEndDate - (g_dateCursorLen - 1/86400),
2421: l_shifts.startDate,
2422: l_shifts.endDate;
2423: close c_24HrTimes;
2424: else
2425: if (l_logLevel <= wip_constants.trace_logging) then
2426: wip_logger.log('Using c_shiftTimes', l_retStatus);
2427: end if;
2428: open c_shiftTimes(v_resID => x_resTbls.resID(p_idx),
2429: v_deptID => x_resTbls.deptID(p_idx),
2437:
2438: if (l_shifts.shiftNum.count = 0 ) then
2439: /* Fix for bug 5660475: If shifts are not available in the date range,
2440: we should continue to search in the next date range, instead of erroring out. */
2441: if (l_logLevel <= wip_constants.trace_logging) then
2442: wip_logger.log('No shifts found in this period.', l_retStatus);
2443: end if;
2444: l_resourceScheduled := false;
2445: end if;
2453: for i in 1..l_shifts.shiftNum.count loop
2454: if(l_shifts.endDate(i) < l_shifts.startDate(i)) then --overnight shift
2455: l_shifts.endDate(i) := l_shifts.endDate(i) + 1;
2456: end if;
2457: if (l_logLevel <= wip_constants.full_logging) then
2458:
2459: wip_logger.log('**********shiftNum:' || l_shifts.shiftNum(i), l_retStatus);
2460: wip_logger.log('**shift start date:' || to_char(l_shifts.startDate(i), g_logDateFmt), l_retStatus);
2461: wip_logger.log('****shift end date:' || to_char(l_shifts.endDate(i), g_logDateFmt), l_retStatus);
2463:
2464: --if shift starts after the requested end date, skip it since none of the shift
2465: --can be used. don't do this in the sql query as it degrades performance
2466: if(l_shifts.startDate(i) > p_endDate) then
2467: if (l_logLevel <= wip_constants.full_logging) then
2468: wip_logger.log('skipping shift (starts after end date)', l_retStatus);
2469: end if;
2470: goto NO_FULFILL_USAGE;--end of loop
2471: end if;
2471: end if;
2472:
2473: --if the shift ends before the end time, adjust the shift length
2474: l_toDate := least(l_shifts.endDate(i), p_endDate);
2475: if (l_logLevel <= wip_constants.full_logging) then
2476: wip_logger.log('calculated end date: ' || to_char(l_toDate, g_logDateFmt), l_retStatus);
2477: end if;
2478:
2479: l_shiftLen := l_toDate - l_shifts.startDate(i);
2479: l_shiftLen := l_toDate - l_shifts.startDate(i);
2480: /*Bug 7015594: If shift start time is same as end time then consider it as 24 hours resource.
2481: This should be only done when 24 hours check is unchecked and resource is not used on repetitive line*/
2482: /*Bug 9355406: fixed regression caused by 7015594, if resource completion date is the beginning of the shift, it wont be treated as 24 hrs resource*/
2483: if(x_resTbls.avail24Flag(p_idx) <> wip_constants.yes AND p_repLineID is null AND l_shifts.startDate(i)=l_shifts.endDate(i)) then
2484: l_shiftLen := 86400;
2485: end if;
2486:
2487:
2484: l_shiftLen := 86400;
2485: end if;
2486:
2487:
2488: if (l_logLevel <= wip_constants.full_logging) then
2489: wip_logger.log('shiftLen(HRS) is ' || round(l_shiftLen*24, g_precision), l_retStatus);
2490: end if;
2491:
2492: if(round(l_shiftLen, g_precision) = 0) then
2489: wip_logger.log('shiftLen(HRS) is ' || round(l_shiftLen*24, g_precision), l_retStatus);
2490: end if;
2491:
2492: if(round(l_shiftLen, g_precision) = 0) then
2493: if (l_logLevel <= wip_constants.full_logging) then
2494: wip_logger.log('skipping shift (no usage)', l_retStatus);
2495: end if;
2496: goto NO_FULFILL_USAGE;--end of loop
2497: end if;
2498:
2499:
2500: if(l_endDate is null) then
2501: l_endDate := l_toDate;
2502: if (l_logLevel <= wip_constants.full_logging) then
2503: wip_logger.log('calculated resource end date:' || to_char(l_endDate, g_logDateFmt), l_retStatus);
2504: end if;
2505: end if;
2506:
2515: x_resTbls.usgEndDate.extend(1);
2516: x_resTbls.usgCumMinProcTime.extend(1);
2517:
2518: l_usgIdx := x_resTbls.usgStartDate.count;
2519: if (l_logLevel <= wip_constants.full_logging) then
2520: wip_logger.log('start idx is ' || x_resTbls.usgStartIdx(p_idx), l_retStatus);
2521: wip_logger.log('end idx is ' || x_resTbls.usgEndIdx(p_idx), l_retStatus);
2522: wip_logger.log('usg idx is ' || l_usgIdx, l_retStatus);
2523: end if;
2528: --shift fulfilled resource => usage start time is resource end time
2529: x_resTbls.usgStartDate(l_usgIdx) := x_resTbls.startDate(p_idx);
2530: x_resTbls.usgEndDate(l_usgIdx) := l_toDate;--l_shifts.endDate(i);
2531:
2532: if (l_logLevel <= wip_constants.full_logging) then
2533: wip_logger.log('start date is ' || to_char(x_resTbls.startDate(p_idx), g_logDateFmt), l_retStatus);
2534: wip_logger.log('end date is ' || to_char(x_resTbls.endDate(p_idx), g_logDateFmt), l_retStatus);
2535: wip_logger.log('usgIdx:' || l_usgIdx, l_retStatus);
2536: wip_logger.log('usage:' || to_char(x_resTbls.usgStartDate(l_usgIdx), g_logDateFmt) || ' - ' ||
2555: x_resTbls.usgStartDate(l_usgIdx) := l_shifts.startDate(i);
2556: --resource consumed until end of the shift
2557: x_resTbls.usgEndDate(l_usgIdx) := l_toDate;
2558:
2559: if (l_logLevel <= wip_constants.full_logging) then
2560: wip_logger.log('exhausted shift. remaining usage(HRS) is ' || round(l_remUsage*24, g_precision), l_retStatus);
2561: wip_logger.log('usage:' || to_char(x_resTbls.usgStartDate(l_usgIdx), g_logDateFmt) || ' - ' ||
2562: to_char(x_resTbls.usgEndDate(l_usgIdx), g_logDateFmt), l_retStatus);
2563: wip_logger.log('usgIdx:' || l_usgIdx, l_retStatus);
2572: l_cursorEndDate := l_cursorEndDate - g_dateCursorLen;
2573:
2574: --if the next start date is after the end of the calendar, then we can't schedule anything
2575: if(l_cursorEndDate < p_minDate) then
2576: if (l_logLevel <= wip_constants.full_logging) then
2577: wip_logger.log('exhausted calendar. remaining usage(HRS) is ' || round(l_remUsage*24, g_precision), l_retStatus);
2578: end if;
2579: fnd_message.set_name('WIP', 'WIP_NO_CALENDAR');
2580: fnd_msg_pub.add;
2585:
2586: --resource usages are in reverse chronological order. Flip them so they go start to end.
2587: i := x_resTbls.usgStartIdx(p_idx);
2588: j := x_resTbls.usgEndIdx(p_idx);
2589: if (l_logLevel <= wip_constants.full_logging) then
2590: wip_logger.log('i: ' || i || '; j: ' || j, l_retStatus);
2591: end if;
2592:
2593: while(j > i) loop
2604: x_resTbls.usgCumMinProcTime(i) := l_prevProcTime + (24*60)*(x_resTbls.usgEndDate(i)-x_resTbls.usgStartDate(i));
2605: l_prevProcTime := x_resTbls.usgCumMinProcTime(i);
2606: end loop;
2607:
2608: if(l_logLevel <= wip_constants.trace_logging) then
2609: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.backwardSchResource',
2610: p_procReturnStatus => null,
2611: p_msg => 'success',
2612: x_returnStatus => l_retStatus);
2613: end if;
2614: exception
2615: when fnd_api.g_exc_unexpected_error then
2616: x_returnStatus := fnd_api.g_ret_sts_unexp_error;
2617: if(l_logLevel <= wip_constants.trace_logging) then
2618: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.backwardSchResource',
2619: p_procReturnStatus => x_returnStatus,
2620: p_msg => 'error',
2621: x_returnStatus => l_retStatus);
2625: fnd_msg_pub.add_exc_msg(p_pkg_name => 'wip_infResSched_grp',
2626: p_procedure_name => 'backwardSchResource',
2627: p_error_text => SQLERRM);
2628:
2629: if(l_logLevel <= wip_constants.trace_logging) then
2630: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.backwardSchResource',
2631: p_procReturnStatus => x_returnStatus,
2632: p_msg => 'unexp error:' || SQLERRM,
2633: x_returnStatus => l_retStatus);
2646: l_logLevel number := fnd_log.g_current_runtime_level;
2647: l_params wip_logger.param_tbl_t;
2648: l_retStatus VARCHAR2(1);
2649: begin
2650: if (l_logLevel <= wip_constants.trace_logging) then
2651: l_params(1).paramName := 'p_orgID';
2652: l_params(1).paramValue := p_orgID;
2653: l_params(2).paramName := 'p_repLineID';
2654: l_params(2).paramValue := p_repLineID;
2674: if(x_resTbls.schedSeqNum(j) = x_resTbls.schedSeqNum(p_startIdx) and
2675: x_resTbls.opSeqNum(j) = x_resTbls.opSeqNum(p_startIdx)) then
2676: --calculate min start date
2677: l_minStartDate := least(l_minStartDate, x_resTbls.startDate(j));
2678: if(l_logLevel <= wip_constants.full_logging) then
2679: wip_logger.log('resID:' || x_resTbls.resID(j), l_retStatus);
2680: wip_logger.log('res start date:' || to_char(x_resTbls.startDate(j), g_logDateFmt), l_retStatus);
2681: wip_logger.log('min start date:' || to_char(l_minStartDate, g_logDateFmt), l_retStatus);
2682: end if;
2695: p_schedFlag => p_schedFlag,
2696: x_resTbls => x_resTbls,
2697: x_returnStatus => x_returnStatus);
2698:
2699: if(l_logLevel <= wip_constants.trace_logging) then
2700: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.forwardScheduleBatch',
2701: p_procReturnStatus => x_returnStatus,
2702: p_msg => 'success?',
2703: x_returnStatus => l_retStatus);
2726: l_schedulingBatch boolean := false;
2727: l_doneSchedBatch boolean := false;
2728: l_forSchedRange num_tbl_t := num_tbl_t(null, null);
2729: begin
2730: if (l_logLevel <= wip_constants.trace_logging) then
2731: l_params(1).paramName := 'p_orgID';
2732: l_params(1).paramValue := p_orgID;
2733: l_params(2).paramName := 'p_repLineID';
2734: l_params(2).paramValue := p_repLineID;
2753: where mp.organization_id = p_orgID
2754: and mp.calendar_code = bc.calendar_code;
2755:
2756: while(getNextResIdx(p_range, p_schedFlag, g_backward, x_resTbls, i)) loop
2757: if (l_logLevel <= wip_constants.full_logging) then
2758: wip_logger.log('begin scheduling resource:' || x_resTbls.resID(i), l_retStatus);
2759: wip_logger.log(' operation:' || x_resTbls.opSeqNum(i), l_retStatus);
2760: wip_logger.log(' schedule seq num:' || x_resTbls.schedSeqNum(i), l_retStatus);
2761: wip_logger.log(' sched flag:' || x_resTbls.schedFlag(i), l_retStatus);
2772: if(l_prevResIdx is not null and
2773: x_resTbls.schedSeqNum(i) = x_resTbls.schedSeqNum(l_prevResIdx) and
2774: x_resTbls.opSeqNum(i) = x_resTbls.opSeqNum(l_prevResIdx)) then
2775: l_schedulingBatch := true;
2776: if(l_logLevel <= wip_constants.full_logging) then
2777: wip_logger.log('starting batch', l_retStatus);
2778: end if;
2779:
2780: --just finished scheduling batch
2778: end if;
2779:
2780: --just finished scheduling batch
2781: elsif(l_schedulingBatch) then
2782: if(l_logLevel <= wip_constants.full_logging) then
2783: wip_logger.log('done bkwd scheduling batch, now fwd sched', l_retStatus);
2784: end if;
2785:
2786: l_schedulingBatch := false;
2793: p_startIdx => l_prevResIdx,
2794: x_resTbls => x_resTbls,
2795: x_returnStatus => x_returnStatus);
2796: if(x_returnStatus <> fnd_api.g_ret_sts_success) then
2797: if(l_logLevel <= wip_constants.full_logging) then
2798: wip_logger.log('simult batch scheduling failed', l_retStatus);
2799: end if;
2800: raise fnd_api.g_exc_unexpected_error;
2801: end if;
2810: end if;
2811:
2812: backwardSchResource(p_orgID, l_resEndDate, l_minDate, i, p_repLineID, x_resTbls, x_returnStatus);
2813: if(x_returnStatus <> fnd_api.g_ret_sts_success) then
2814: if(l_logLevel <= wip_constants.full_logging) then
2815: wip_logger.log('backward schedule failed', l_retStatus);
2816: end if;
2817: raise fnd_api.g_exc_unexpected_error;
2818: end if;
2820: l_prevResIdx := i;
2821: end loop;
2822:
2823: if(l_schedulingBatch) then
2824: if(l_logLevel <= wip_constants.full_logging) then
2825: wip_logger.log('done bkwd scheduling last batch, now fwd sched', l_retStatus);
2826: end if;
2827: forwardScheduleBatch(p_orgID => p_orgID,
2828: p_repLineID => p_repLineID,
2831: p_startIdx => l_prevResIdx,
2832: x_resTbls => x_resTbls,
2833: x_returnStatus => x_returnStatus);
2834: if(x_returnStatus <> fnd_api.g_ret_sts_success) then
2835: if(l_logLevel <= wip_constants.full_logging) then
2836: wip_logger.log('final simult batch scheduling failed', l_retStatus);
2837: end if;
2838: raise fnd_api.g_exc_unexpected_error;
2839: end if;
2838: raise fnd_api.g_exc_unexpected_error;
2839: end if;
2840: end if;
2841:
2842: if(l_logLevel <= wip_constants.trace_logging) then
2843: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.backwardSchedule',
2844: p_procReturnStatus => x_returnStatus,
2845: p_msg => 'success',
2846: x_returnStatus => l_retStatus);
2848:
2849: exception
2850: when fnd_api.g_exc_unexpected_error then
2851: x_returnStatus := fnd_api.g_ret_sts_unexp_error;
2852: if(l_logLevel <= wip_constants.trace_logging) then
2853: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.backwardSchedule',
2854: p_procReturnStatus => x_returnStatus,
2855: p_msg => 'error',
2856: x_returnStatus => l_retStatus);
2860: fnd_msg_pub.add_exc_msg(p_pkg_name => 'wip_infResSched_grp',
2861: p_procedure_name => 'backwardSchedule',
2862: p_error_text => SQLERRM);
2863:
2864: if(l_logLevel <= wip_constants.trace_logging) then
2865: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.backwardSchedule',
2866: p_procReturnStatus => x_returnStatus,
2867: p_msg => 'unexp error:' || SQLERRM,
2868: x_returnStatus => l_retStatus);
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;
2897: backwardSchedule(p_orgID => p_orgID,
2898: p_repLineID => p_repLineID,
2899: p_EndDate => p_opTbl(i).startDate,
2900: p_range => num_tbl_t(p_opTbl(i).resStartIdx, p_opTbl(i).resEndIdx),
2901: p_schedFlag => wip_constants.sched_prior,
2902: x_resTbls => x_resTbls,
2903: x_returnStatus => x_returnStatus);
2904: if(x_returnStatus <> fnd_api.g_ret_sts_success) then
2905: raise fnd_api.g_exc_unexpected_error;
2906: end if;
2907: end if;
2908: end loop;
2909:
2910: if(l_logLevel <= wip_constants.trace_logging) then
2911: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.schedulePriorResources',
2912: p_procReturnStatus => x_returnStatus,
2913: p_msg => 'success',
2914: x_returnStatus => l_retStatus);
2915: end if;
2916: exception
2917: when fnd_api.g_exc_unexpected_error then
2918: x_returnStatus := fnd_api.g_ret_sts_unexp_error;
2919: if(l_logLevel <= wip_constants.trace_logging) then
2920: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.schedulePriorResources',
2921: p_procReturnStatus => x_returnStatus,
2922: p_msg => 'backward scheduling failed',
2923: x_returnStatus => l_retStatus);
2923: x_returnStatus => l_retStatus);
2924: end if;
2925: when others then
2926: x_returnStatus := fnd_api.g_ret_sts_unexp_error;
2927: if(l_logLevel <= wip_constants.trace_logging) then
2928: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.schedulePriorResources',
2929: p_procReturnStatus => x_returnStatus,
2930: p_msg => 'unexp error:' || SQLERRM,
2931: x_returnStatus => l_retStatus);
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;
2964: forwardSchedule(p_orgID => p_orgID,
2965: p_repLineID => p_repLineID,
2966: p_startDate => p_opTbl(i).endDate,
2967: p_range => num_tbl_t(p_opTbl(i).resStartIdx, p_opTbl(i).resEndIdx),
2968: p_schedFlag => wip_constants.sched_next,
2969: x_resTbls => x_resTbls,
2970: x_returnStatus => x_returnStatus);
2971: if (is_backWardSch) then
2972: g_reset_date := 0; -- Bug #13388579
2976: end if;
2977: end if;
2978: end loop;
2979:
2980: if(l_logLevel <= wip_constants.trace_logging) then
2981: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.scheduleNextResources',
2982: p_procReturnStatus => x_returnStatus,
2983: p_msg => 'success',
2984: x_returnStatus => l_retStatus);
2985: end if;
2986: exception
2987: when fnd_api.g_exc_unexpected_error then
2988: x_returnStatus := fnd_api.g_ret_sts_unexp_error;
2989: if(l_logLevel <= wip_constants.trace_logging) then
2990: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.scheduleNextResources',
2991: p_procReturnStatus => x_returnStatus,
2992: p_msg => 'backward scheduling failed',
2993: x_returnStatus => l_retStatus);
2997: fnd_msg_pub.add_exc_msg(p_pkg_name => 'wip_infResSched_grp',
2998: p_procedure_name => 'scheduleNextResources',
2999: p_error_text => SQLERRM);
3000:
3001: if(l_logLevel <= wip_constants.trace_logging) then
3002: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.scheduleNextResources',
3003: p_procReturnStatus => x_returnStatus,
3004: p_msg => 'unexp error:' || SQLERRM,
3005: x_returnStatus => l_retStatus);
3024: l_exceptionExists boolean := false;
3025: l_errMsg VARCHAR2(200);
3026: begin
3027: x_returnStatus := fnd_api.g_ret_sts_success;
3028: if(l_logLevel <= wip_constants.trace_logging) then
3029: l_params(1).paramName := 'p_orgID';
3030: l_params(1).paramValue := p_orgID;
3031: l_params(2).paramName := 'p_repLineID';
3032: l_params(2).paramValue := p_repLineID;
3036: p_params => l_params,
3037: x_returnStatus => x_returnStatus);
3038: end if;
3039: --this loop finds an exception
3040: while(getNextResIdx(l_range, wip_constants.sched_prior, g_forward, x_resTbls, i)) loop
3041: --if we have already found an exception and moved on to the next op, stop
3042: --and reschedule based on the current info
3043: if(l_exceptionExists) then
3044: if(l_opSeqNum <> x_resTbls.opSeqNum(i)) then
3091: forwardSchedule(p_orgID => p_orgID,
3092: p_repLineID => p_repLineID,
3093: p_startDate => p_startDate,
3094: p_range => l_opRange,
3095: p_schedFlag => wip_constants.sched_prior,
3096: x_resTbls => x_resTbls,
3097: x_returnStatus => x_returnStatus);
3098:
3099: if(x_returnStatus <> fnd_api.g_ret_sts_success) then
3102: end if;
3103:
3104: --find latest completion date of the prior resources
3105: i := null;
3106: while(getNextResIdx(l_opRange, wip_constants.sched_prior, g_forward, x_resTbls, i)) loop
3107: l_opStartDate := greatest(nvl(l_opStartDate, x_resTbls.endDate(i)), x_resTbls.endDate(i));
3108: end loop;
3109:
3110: --now midpoint schedule from the new op start date...This invocation of schedule() will not invoke
3136: raise fnd_api.g_exc_unexpected_error;
3137: end if;
3138: end if;
3139:
3140: if(l_logLevel <= wip_constants.trace_logging) then
3141: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.resolvePriorExceptions',
3142: p_procReturnStatus => x_returnStatus,
3143: p_msg => 'success.',
3144: x_returnStatus => l_retStatus);
3145: end if;
3146: exception
3147: when fnd_api.g_exc_unexpected_error then
3148: x_returnStatus := fnd_api.g_ret_sts_unexp_error;
3149: if(l_logLevel <= wip_constants.trace_logging) then
3150: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.resolvePriorExceptions',
3151: p_procReturnStatus => x_returnStatus,
3152: p_msg => 'errmsg: ' || l_errMsg,
3153: x_returnStatus => l_retStatus);
3157: fnd_msg_pub.add_exc_msg(p_pkg_name => 'wip_infResSched_grp',
3158: p_procedure_name => 'resolvePriorExceptions',
3159: p_error_text => SQLERRM);
3160:
3161: if(l_logLevel <= wip_constants.trace_logging) then
3162: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.resolvePriorExceptions',
3163: p_procReturnStatus => x_returnStatus,
3164: p_msg => 'unexp error:' || SQLERRM,
3165: x_returnStatus => l_retStatus);
3183: l_exceptionExists boolean := false;
3184: l_errMsg VARCHAR2(200);
3185: begin
3186: x_returnStatus := fnd_api.g_ret_sts_success;
3187: if(l_logLevel <= wip_constants.trace_logging) then
3188: l_params(1).paramName := 'p_orgID';
3189: l_params(1).paramValue := p_orgID;
3190: l_params(2).paramName := 'p_repLineID';
3191: l_params(2).paramValue := p_repLineID;
3195: p_params => l_params,
3196: x_returnStatus => x_returnStatus);
3197: end if;
3198: --this loop finds an exception
3199: while(getNextResIdx(l_range, wip_constants.sched_next, g_backward, x_resTbls, i)) loop
3200: --if we have already found an exception and moved on to the next op, stop
3201: --and reschedule based on the current info
3202: if(l_exceptionExists) then
3203: if(l_opSeqNum <> x_resTbls.opSeqNum(i)) then
3212: l_opSeqNum := x_resTbls.opSeqNum(i);
3213: end if;
3214: l_opRange(1) := i;
3215: l_exceptionExists := x_resTbls.endDate(i) > p_endDate;
3216: if(l_logLevel <= wip_constants.full_logging) then
3217: wip_logger.log('res end date: ' || to_char(x_resTbls.endDate(i), g_logDateFmt), l_retStatus);
3218: wip_logger.log('job end date: ' || to_char(p_endDate, g_logDateFmt), l_retStatus);
3219: end if;
3220: end if;
3221: end loop;
3222:
3223: --found a prior resource whose start date is earlier than job start...
3224: if(l_exceptionExists) then
3225: if(l_logLevel <= wip_constants.full_logging) then
3226: wip_logger.log('found exception', l_retStatus);
3227: end if;
3228: i := null;
3229: --going to reschedule entire job
3255: backwardSchedule(p_orgID => p_orgID,
3256: p_repLineID => p_repLineID,
3257: p_endDate => p_endDate,
3258: p_range => l_opRange,
3259: p_schedFlag => wip_constants.sched_next,
3260: x_resTbls => x_resTbls,
3261: x_returnStatus => x_returnStatus);
3262:
3263: if(x_returnStatus <> fnd_api.g_ret_sts_success) then
3266: end if;
3267:
3268: --find earliest start date of the next resources
3269: i := null;
3270: while(getNextResIdx(l_opRange, wip_constants.sched_next, g_forward, x_resTbls, i)) loop
3271: l_opEndDate := least(nvl(l_opEndDate, x_resTbls.startDate(i)), x_resTbls.startDate(i));
3272: end loop;
3273:
3274: --now midpoint schedule from the new op start date...This invocation of schedule() will not invoke
3300: raise fnd_api.g_exc_unexpected_error;
3301: end if;
3302: end if;
3303:
3304: if(l_logLevel <= wip_constants.trace_logging) then
3305: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.resolveNextExceptions',
3306: p_procReturnStatus => x_returnStatus,
3307: p_msg => 'success.',
3308: x_returnStatus => l_retStatus);
3309: end if;
3310: exception
3311: when fnd_api.g_exc_unexpected_error then
3312: x_returnStatus := fnd_api.g_ret_sts_unexp_error;
3313: if(l_logLevel <= wip_constants.trace_logging) then
3314: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.resolveNextExceptions',
3315: p_procReturnStatus => x_returnStatus,
3316: p_msg => l_errMsg,
3317: x_returnStatus => l_retStatus);
3321: fnd_msg_pub.add_exc_msg(p_pkg_name => 'wip_infResSched_grp',
3322: p_procedure_name => 'resolveNextExceptions',
3323: p_error_text => SQLERRM);
3324:
3325: if(l_logLevel <= wip_constants.trace_logging) then
3326: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.resolveNextExceptions',
3327: p_procReturnStatus => x_returnStatus,
3328: p_msg => 'unexp error:' || SQLERRM,
3329: x_returnStatus => l_retStatus);
3339: l_retStatus VARCHAR2(1);
3340: l_resCode VARCHAR2(10);
3341: begin
3342:
3343: if (l_logLevel <= wip_constants.trace_logging) then
3344: wip_logger.entryPoint(p_procName => 'wip_infResSched_grp.dumpResources',
3345: p_params => l_params,
3346: x_returnStatus => l_retStatus);
3347: end if;
3346: x_returnStatus => l_retStatus);
3347: end if;
3348:
3349: if(p_resTbls.resID is null or p_resTbls.resID.count < 1 and
3350: l_logLevel <= wip_constants.trace_logging) then
3351: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.dumpResources',
3352: p_procReturnStatus => null,
3353: p_msg => 'no resources in table!',
3354: x_returnStatus => l_retStatus);
3354: x_returnStatus => l_retStatus);
3355: return;
3356: end if;
3357:
3358: if (l_logLevel <= wip_constants.full_logging) then
3359: for i in 1..p_resTbls.resID.count loop
3360: select resource_code
3361: into l_resCode
3362: from bom_resources
3381: end loop;
3382: end if;
3383: end loop;
3384: end if;
3385: if (l_logLevel <= wip_constants.trace_logging) then
3386: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.dumpResources',
3387: p_procReturnStatus => null,
3388: p_msg => 'success',
3389: x_returnStatus => l_retStatus);
3390: end if;
3391:
3392: exception
3393: when others then
3394: if (l_logLevel <= wip_constants.trace_logging) then
3395: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.dumpResources',
3396: p_procReturnStatus => null,
3397: p_msg => 'exception:' || SQLERRM,
3398: x_returnStatus => l_retStatus);
3413: l_params wip_logger.param_tbl_t;
3414:
3415: l_endDate DATE; --bug 8614399 (FP 8586766)
3416: begin
3417: if(l_logLevel <= wip_constants.trace_logging) then
3418: l_params(1).paramName := 'p_anchorDate';
3419: l_params(1).paramValue := to_char(p_anchorDate, g_logDateFmt);
3420: wip_logger.entryPoint(p_procName => 'wip_infResSched_grp.scheduleNoResources',
3421: p_params => l_params,
3422: x_returnStatus => l_retStatus);
3423: end if;
3424: x_returnStatus := fnd_api.g_ret_sts_success;
3425:
3426: while(getNextResIdx(l_range, wip_constants.sched_no, g_forward, x_resTbls, i)) loop
3427:
3428: if(l_logLevel <= wip_constants.full_logging) then
3429: wip_logger.log('found scheduled no resource at ' || i, l_retStatus);
3430: end if;
3424: x_returnStatus := fnd_api.g_ret_sts_success;
3425:
3426: while(getNextResIdx(l_range, wip_constants.sched_no, g_forward, x_resTbls, i)) loop
3427:
3428: if(l_logLevel <= wip_constants.full_logging) then
3429: wip_logger.log('found scheduled no resource at ' || i, l_retStatus);
3430: end if;
3431:
3432: l_prevRange(2) := i;
3433: l_nextRange(1) := i;
3434: j := null;
3435: k := null; --bug 8669295 (FP 8651554)
3436: --find previous scheduled yes resource
3437: if(getNextResIdx(l_prevRange, wip_constants.sched_yes, g_backward, x_resTbls, j)) then
3438:
3439: if(l_logLevel <= wip_constants.full_logging) then
3440: wip_logger.log('found previous scheduled yes resource at ' || j, l_retStatus);
3441: end if;
3435: k := null; --bug 8669295 (FP 8651554)
3436: --find previous scheduled yes resource
3437: if(getNextResIdx(l_prevRange, wip_constants.sched_yes, g_backward, x_resTbls, j)) then
3438:
3439: if(l_logLevel <= wip_constants.full_logging) then
3440: wip_logger.log('found previous scheduled yes resource at ' || j, l_retStatus);
3441: end if;
3442:
3443: l_endDate := x_resTbls.endDate(j);
3443: l_endDate := x_resTbls.endDate(j);
3444: --bug 8614399 (FP 8586766): identify the highest endDate in case of simultaneous resources
3445: if(x_resTbls.schedSeqNum(j) is not null) then
3446: for k in reverse 1..(j-1) loop --bug 8669295 (FP 8651554: range in for loop to be low..high
3447: if((x_resTbls.schedFlag(k) = wip_constants.sched_yes)
3448: and (x_resTbls.schedSeqNum(k) = x_resTbls.schedSeqNum(j))
3449: and (x_resTbls.endDate(k) > l_endDate)) then
3450: l_endDate := x_resTbls.endDate(k);
3451: if(l_logLevel <= wip_constants.full_logging) then
3447: if((x_resTbls.schedFlag(k) = wip_constants.sched_yes)
3448: and (x_resTbls.schedSeqNum(k) = x_resTbls.schedSeqNum(j))
3449: and (x_resTbls.endDate(k) > l_endDate)) then
3450: l_endDate := x_resTbls.endDate(k);
3451: if(l_logLevel <= wip_constants.full_logging) then
3452: wip_logger.log('higher endDate found at: '|| k || ' date: ' || l_endDate,l_retStatus);
3453: end if;
3454: end if;
3455: if((k > 1) and (x_resTbls.schedSeqNum(k) <> x_resTbls.schedSeqNum(k-1))) then
3460: x_resTbls.startDate(i) := l_endDate;
3461:
3462: --couldn't find a scheduled yes resource
3463: /* Bug 6954186: Find the next scheduled resource in forward direction*/
3464: elsif(getNextResIdx(l_nextRange, wip_constants.sched_yes, g_forward, x_resTbls, j)) then
3465: if(l_logLevel <= wip_constants.full_logging) then
3466: wip_logger.log('found later scheduled yes resource at ' || j, l_retStatus);
3467: end if;
3468:
3461:
3462: --couldn't find a scheduled yes resource
3463: /* Bug 6954186: Find the next scheduled resource in forward direction*/
3464: elsif(getNextResIdx(l_nextRange, wip_constants.sched_yes, g_forward, x_resTbls, j)) then
3465: if(l_logLevel <= wip_constants.full_logging) then
3466: wip_logger.log('found later scheduled yes resource at ' || j, l_retStatus);
3467: end if;
3468:
3469: x_resTbls.startDate(i) := x_resTbls.startDate(j);
3469: x_resTbls.startDate(i) := x_resTbls.startDate(j);
3470:
3471: else --no scheduled yes resources
3472: x_resTbls.startDate(i) := p_anchorDate;
3473: if(l_logLevel <= wip_constants.full_logging) then
3474: wip_logger.log('no scheduled yes resources found', l_retStatus);
3475: end if;
3476: end if;
3477:
3476: end if;
3477:
3478: x_resTbls.endDate(i) := x_resTbls.startDate(i);
3479: end loop;
3480: if(l_logLevel <= wip_constants.trace_logging) then
3481: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.scheduleNoResources',
3482: p_procReturnStatus => x_returnStatus,
3483: p_msg => 'success',
3484: x_returnStatus => l_retStatus);
3485: end if;
3486: exception
3487: when others then
3488: x_returnStatus := fnd_api.g_ret_sts_unexp_error;
3489: if(l_logLevel <= wip_constants.trace_logging) then
3490: wip_logger.exitPoint(p_procName => 'wip_infResSched_grp.scheduleNoResources',
3491: p_procReturnStatus => x_returnStatus,
3492: p_msg => 'error: ' || SQLERRM,
3493: x_returnStatus => l_retStatus);