[Home] [Help]
PACKAGE BODY: APPS.WIP_INFINITE_SCHEDULER_PVT
Source
1 package body wip_infinite_scheduler_pvt as
2 /* $Header: wipiscdb.pls 120.6 2008/01/25 12:16:57 abgangul ship $ */
3
4 --private types
5 type num_tbl_t is table of number;
6 type date_tbl_t is table of date;
7
8
9 --package constants
10 g_logDateFmt CONSTANT VARCHAR2(30) := 'HH24:MI:SS MM/DD/YYYY';
11
12
13 --private procedures
14
15 --reads in job ops and resources (and locks the records)
16 -- Fixed bug 5440007
17 -- Added a new parameter p_quantity
18 procedure readJobSchedule(p_wipEntityID NUMBER,
19 p_repSchedID NUMBER := null,
20 p_orgID NUMBER,
21 p_minOpSeqNum NUMBER,
22 p_minSchedSeqNum NUMBER,
23 p_maxOpSeqNum NUMBER,
24 p_maxSchedSeqNum NUMBER,
25 p_quantity NUMBER := null, --- Fixed bug 5440007
26 x_resTbls out nocopy wip_infResSched_grp.op_res_rectbl_t,
27 x_assignedUnits out nocopy num_tbl_t,
28 x_returnStatus out nocopy varchar2);
29
30 --writes out job op dates and resource dates
31 procedure writeJobSchedule(p_wipEntityID NUMBER,
32 p_repSchedID NUMBER := null,
33 p_orgID NUMBER,
34 p_schedMethod NUMBER,
35 p_minOpSeqNum NUMBER,
36 p_minSchedSeqNum NUMBER,
37 p_maxOpSeqNum NUMBER,
38 p_maxSchedSeqNum NUMBER,
39 p_anchorDate in date,
40 p_assignedUnits in num_tbl_t,
41 x_resTbls in out nocopy wip_infResSched_grp.op_res_rectbl_t,
42 x_returnStatus out nocopy varchar2);
43
44 procedure getMidPointInfo(p_midPntOpSeqNum IN NUMBER,
45 p_midPntResSeqNum IN NUMBER,
46 p_subGrpNum IN NUMBER,
47 p_schedMethod IN NUMBER,
48 p_wipEntityID IN NUMBER,
49 p_orgID IN NUMBER,
50 x_minOpSeqNum OUT NOCOPY NUMBER,
51 x_minSchedSeqNum OUT NOCOPY NUMBER,
52 x_maxOpSeqNum OUT NOCOPY NUMBER,
53 x_maxSchedSeqNum OUT NOCOPY NUMBER);
54
55
56 -- Fixed bug 5440007
57 -- Added a new parameter p_quantity to the schedule api
58 -- As of now, wip massload code path will pass this value
59 procedure schedule(p_initMsgList IN VARCHAR2 := null,
60 p_endDebug IN VARCHAR2 := null,
61 p_orgID IN NUMBER,
62 p_wipEntityID IN NUMBER,
63 p_repSchedID IN NUMBER := null,
64 p_startDate IN DATE := null,
65 p_endDate IN DATE := null,
66 p_midPntMethod IN NUMBER := null,
67 p_opSeqNum IN NUMBER := null,
68 p_resSeqNum IN NUMBER := null,
69 p_subGrpNum IN NUMBER := null,
70 p_quantity IN NUMBER := null, -- Fix bug 5440007
71 x_returnStatus OUT NOCOPY VARCHAR2,
72 x_errorMsg OUT NOCOPY VARCHAR2) is
73 l_logLevel NUMBER := fnd_log.g_current_runtime_level;
74 l_params wip_logger.param_tbl_t;
75 l_retStatus VARCHAR2(1);
76 l_resTbls wip_infResSched_grp.op_res_rectbl_t;
77 l_repLineID NUMBER;
78 l_assignedUnits num_tbl_t;
79 l_minOpSeqNum NUMBER;
80 l_minSchedSeqNum NUMBER;
81 l_maxOpSeqNum NUMBER;
82 l_maxSchedSeqNum NUMBER;
83 l_schedMethod NUMBER;
84 begin
85 savepoint wipiscdb0;
86 if (l_logLevel <= wip_constants.trace_logging) then
87 l_params(1).paramName := 'p_orgID';
88 l_params(1).paramValue := p_orgID;
89 l_params(2).paramName := 'p_wipEntityID';
90 l_params(2).paramValue := p_wipEntityID;
91 l_params(3).paramName := 'p_repSchedID';
92 l_params(3).paramValue := p_repSchedID;
93 l_params(4).paramName := 'p_startDate';
94 l_params(4).paramValue := to_char(p_startDate, g_logDateFmt);
95 l_params(5).paramName := 'p_endDate';
96 l_params(5).paramValue := to_char(p_endDate, g_logDateFmt);
97 l_params(6).paramName := 'p_midPntMethod';
98 l_params(6).paramValue := p_midPntMethod;
99 l_params(7).paramName := 'p_opSeqNum';
100 l_params(7).paramValue := p_opSeqNum;
101 l_params(8).paramName := 'p_resSeqNum';
102 l_params(8).paramValue := p_resSeqNum;
103 l_params(9).paramName := 'p_subGrpNum';
104 l_params(9).paramValue := p_subGrpNum;
105 l_params(10).paramName := 'p_quantity';
106 l_params(10).paramValue := p_quantity;
107
108 wip_logger.entryPoint(p_procName => 'wip_infinite_scheduler_pvt.schedule',
109 p_params => l_params,
110 x_returnStatus => x_returnStatus);
111 if(x_returnStatus <> fnd_api.g_ret_sts_success) then
112 raise fnd_api.g_exc_unexpected_error;
113 end if;
114 end if;
115 x_returnStatus := fnd_api.g_ret_sts_success;
116
117 if(fnd_api.to_boolean(nvl(p_initMsgList, fnd_api.g_true))) then
118 fnd_msg_pub.initialize;
119 end if;
120
121 if (l_logLevel <= wip_constants.full_logging) then
122 wip_logger.log('reading job/schedule...', l_retStatus);
123 end if;
124 if(p_midPntMethod is not null) then
125 l_schedMethod := p_midPntMethod;
126 elsif(p_opSeqNum is not null) then
127 l_schedMethod := wip_constants.midpoint;
128 elsif(p_startDate is not null) then
129 l_schedMethod := wip_constants.forwards;
130 else
131 l_schedMethod := wip_constants.backwards;
132 end if;
133
134 getMidPointInfo(p_midPntOpSeqNum => p_opSeqNum,
135 p_midPntResSeqNum => p_resSeqNum,
136 p_subGrpNum => p_subGrpNum,
137 p_schedMethod => l_schedMethod,
138 p_wipEntityID => p_wipEntityID,
139 p_orgID => p_orgID,
140 x_minOpSeqNum => l_minOpSeqNum,
141 x_minSchedSeqNum => l_minSchedSeqNum,
142 x_maxOpSeqNum => l_maxOpSeqNum,
143 x_maxSchedSeqNum => l_maxSchedSeqNum);
144
145 -- Fixed bug 5440007
146 -- Started passing new parameter p_quantity
147 readJobSchedule(p_wipEntityID => p_wipEntityID,
148 p_repSchedID => p_repSchedID,
149 p_orgID => p_orgID,
150 p_minOpSeqNum => l_minOpSeqNum,
151 p_minSchedSeqNum => l_minSchedSeqNum,
152 p_maxOpSeqNum => l_maxOpSeqNum,
153 p_maxSchedSeqNum => l_maxSchedSeqNum,
154 p_quantity => p_quantity, --- fixed bug 5440007
155 x_resTbls => l_resTbls,
156 x_assignedUnits => l_assignedUnits,
157 x_returnStatus => x_returnStatus);
158
159 if(x_returnStatus <> fnd_api.g_ret_sts_success) then
160 raise fnd_api.g_exc_unexpected_error;
161 end if;
162 /*
163 if(p_repSchedID is not null) then
164 select line_id
165 into l_repLineID
166 from wip_repetitive_schedules
167 where repetitive_schedule_id = p_repSchedID;
168 end if;
169 */
170 if (l_logLevel <= wip_constants.full_logging) then
171 wip_logger.log('scheduling job/schedule...', l_retStatus);
172 end if;
173
174 wip_infResSched_grp.schedule(p_orgID => p_orgID,
175 -- p_repLineID => l_repLineID,
176 p_startDate => p_startDate,
177 p_endDate => p_endDate,
178 p_opSeqNum => p_opSeqNum,
179 p_resSeqNum => p_resSeqNum,
180 p_endDebug => fnd_api.g_false,
181 x_resTbls => l_resTbls,
182 x_returnStatus => x_returnStatus);
183 if(x_returnStatus <> fnd_api.g_ret_sts_success) then
184 raise fnd_api.g_exc_unexpected_error;
185 end if;
186
187 if (l_logLevel <= wip_constants.full_logging) then
188 wip_logger.log('writing job/schedule...', l_retStatus);
189 end if;
190
191 writeJobSchedule(p_wipEntityID => p_wipEntityID,
192 p_repSchedID => p_repSchedID,
193 p_orgID => p_orgID,
194 p_schedMethod => l_schedMethod,
195 p_minOpSeqNum => l_minOpSeqNum,
196 p_minSchedSeqNum => l_minSchedSeqNum,
197 p_maxOpSeqNum => l_maxOpSeqNum,
198 p_maxSchedSeqNum => l_maxSchedSeqNum,
199 p_anchorDate => nvl(p_startDate, p_endDate),
200 p_assignedUnits => l_assignedUnits,
201 x_resTbls => l_resTbls,
202 x_returnStatus => x_returnStatus);
203
204 if(x_returnStatus <> fnd_api.g_ret_sts_success) then
205 raise fnd_api.g_exc_unexpected_error;
206 end if;
207
208 if (l_logLevel <= wip_constants.trace_logging) then
209 wip_logger.exitPoint(p_procName => 'wip_infinite_scheduler_pvt.schedule',
210 p_procReturnStatus => x_returnStatus,
211 p_msg => 'success',
212 x_returnStatus => l_retStatus);
213 if(fnd_api.to_boolean(nvl(p_endDebug, fnd_api.g_true))) then
214 wip_logger.cleanup(l_retStatus);
215 end if;
216 end if;
217 exception
218 when fnd_api.g_exc_unexpected_error then
219 rollback to wipiscdb0;
220 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
221 wip_utilities.get_message_stack(p_msg => x_errorMsg,
222 p_delete_stack => fnd_api.g_false);
223 if (l_logLevel <= wip_constants.trace_logging) then
224 wip_logger.exitPoint(p_procName => 'wip_infinite_scheduler_pvt.schedule',
225 p_procReturnStatus => x_returnStatus,
226 p_msg => 'error: ' || x_errorMsg,
227 x_returnStatus => l_retStatus);
228 if(fnd_api.to_boolean(nvl(p_endDebug, fnd_api.g_true))) then
229 wip_logger.cleanup(l_retStatus);
230 end if;
231 end if;
232
233 when others then
234 rollback to wipiscdb0;
235 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
236 fnd_msg_pub.add_exc_msg(p_pkg_name => 'wip_infinite_scheduler_pvt',
237 p_procedure_name => 'schedule',
238 p_error_text => SQLERRM);
239 wip_utilities.get_message_stack(p_msg => x_errorMsg,
240 p_delete_stack => fnd_api.g_false);
241 if (l_logLevel <= wip_constants.trace_logging) then
242 wip_logger.exitPoint(p_procName => 'wip_infinite_scheduler_pvt.schedule',
243 p_procReturnStatus => x_returnStatus,
244 p_msg => 'unexp error: ' || x_errorMsg,
245 x_returnStatus => l_retStatus);
246 if(fnd_api.to_boolean(nvl(p_endDebug, fnd_api.g_true))) then
247 wip_logger.cleanup(l_retStatus);
248 end if;
249 end if;
250 end schedule;
251
252 procedure getMidPointInfo(p_midPntOpSeqNum IN NUMBER,
253 p_midPntResSeqNum IN NUMBER,
254 p_subGrpNum IN NUMBER,
255 p_schedMethod IN NUMBER,
256 p_wipEntityID IN NUMBER,
257 p_orgID IN NUMBER,
258 x_minOpSeqNum OUT NOCOPY NUMBER,
259 x_minSchedSeqNum OUT NOCOPY NUMBER,
260 x_maxOpSeqNum OUT NOCOPY NUMBER,
261 x_maxSchedSeqNum OUT NOCOPY NUMBER) is
262 l_logLevel NUMBER := fnd_log.g_current_runtime_level;
263 l_params wip_logger.param_tbl_t;
264 l_retStatus VARCHAR2(1);
265 begin
266 if (l_logLevel <= wip_constants.trace_logging) then
267 l_params(1).paramName := 'p_midPntOpSeqNum';
268 l_params(1).paramValue := p_midPntOpSeqNum;
269 l_params(2).paramName := 'p_midPntResSeqNum';
270 l_params(2).paramValue := p_midPntResSeqNum;
271 l_params(3).paramName := 'p_subGrpNum';
272 l_params(3).paramValue := p_subGrpNum;
273 l_params(4).paramName := 'p_schedMethod';
274 l_params(4).paramValue := p_schedMethod;
275 l_params(5).paramName := 'p_wipEntityID';
276 l_params(5).paramValue := p_wipEntityID;
277 l_params(6).paramName := 'p_orgID';
278 l_params(6).paramValue := p_orgID;
279 wip_logger.entryPoint(p_procName => 'wip_infinite_scheduler_pvt.getMidPointInfo',
280 p_params => l_params,
281 x_returnStatus => l_retStatus);
282 end if;
283
284 if(p_schedMethod in (wip_constants.forwards,
285 wip_constants.backwards,
286 wip_constants.midpoint)) then
287
288 if(l_logLevel <= wip_constants.full_logging) then
289 wip_logger.log('entire job reschedule', l_retStatus);
290 end if;
291
292 select min(operation_seq_num),
293 max(operation_seq_num)
294 into x_minOpSeqNum,
295 x_maxOpSeqNum
296 from wip_operations
297 where wip_entity_id = p_wipEntityID
298 and organization_id = p_orgID;
299
300 elsif(p_schedMethod = wip_constants.midpoint_forwards) then
301
302 if(l_logLevel <= wip_constants.full_logging) then
303 wip_logger.log('midpoint forward', l_retStatus);
304 end if;
305
306 x_minOpSeqNum := p_midPntOpSeqNum;
307
308 if (p_midPntOpSeqNum is not null AND
309 p_midPntResSeqNum is not null) then
310 select nvl(schedule_seq_num, resource_seq_num)
311 into x_minSchedSeqNum
312 from wip_operation_resources
313 where wip_entity_id = p_wipEntityID
314 and organization_id = p_orgID
315 and operation_seq_num = p_midPntOpSeqNum
316 and resource_seq_num = p_midPntResSeqNum;
317 else
318 x_minSchedSeqNum := null;
319 end if;
320
321 select max(operation_seq_num)
322 into x_maxOpSeqNum
323 from wip_operations
324 where wip_entity_id = p_wipEntityID
325 and organization_id = p_orgID;
326
327 select max(nvl(schedule_seq_num, resource_seq_num))
328 into x_maxSchedSeqNum
329 from wip_operation_resources
330 where wip_entity_id = p_wipEntityID
331 and organization_id = p_orgID
332 and operation_seq_num = x_maxOpSeqNum;
333
334 elsif(p_schedMethod = wip_constants.midpoint_backwards) then
335 x_maxOpSeqNum := p_midPntOpSeqNum;
336
337 if (p_midPntOpSeqNum is not null AND
338 p_midPntResSeqNum is not null) then
339 select nvl(schedule_seq_num, resource_seq_num)
340 into x_maxSchedSeqNum
341 from wip_operation_resources
342 where wip_entity_id = p_wipEntityID
343 and organization_id = p_orgID
344 and operation_seq_num = p_midPntOpSeqNum
345 and resource_seq_num = p_midPntResSeqNum;
346 else
347 x_maxSchedSeqNum := null;
348 end if;
349
350 if(l_logLevel <= wip_constants.full_logging) then
351 wip_logger.log('midpoint backward', l_retStatus);
352 end if;
353
354 select min(operation_seq_num)
355 into x_minOpSeqNum
356 from wip_operations
357 where wip_entity_id = p_wipEntityID
358 and organization_id = p_orgID;
359
360 select min(nvl(schedule_seq_num, resource_seq_num))
361 into x_minSchedSeqNum
362 from wip_operation_resources
363 where wip_entity_id = p_wipEntityID
364 and organization_id = p_orgID
365 and operation_seq_num = x_minOpSeqNum;
366 elsif(p_schedMethod = wip_constants.current_op) then
367
368 if(l_logLevel <= wip_constants.full_logging) then
369 wip_logger.log('current op', l_retStatus);
370 end if;
371
372 x_minOpSeqNum := p_midPntOpSeqNum;
373 x_maxOpSeqNum := p_midPntOpSeqNum;
374 select min(nvl(schedule_seq_num, resource_seq_num)), max(nvl(schedule_seq_num, resource_seq_num))
375 into x_minSchedSeqNum, x_maxSchedSeqNum
376 from wip_operation_resources
377 where wip_entity_id = p_wipEntityID
378 and organization_id = p_orgID
379 and operation_seq_num = p_midPntOpSeqNum;
380 elsif(p_schedMethod = wip_constants.current_sub_grp) then
381
382 if(l_logLevel <= wip_constants.full_logging) then
383 wip_logger.log('current sub grp', l_retStatus);
384 end if;
385
386 x_minOpSeqNum := p_midPntOpSeqNum;
387 x_maxOpSeqNum := p_midPntOpSeqNum;
388
389 select min(nvl(schedule_seq_num, resource_seq_num)), max(nvl(schedule_seq_num, resource_seq_num))
390 into x_minSchedSeqNum, x_maxSchedSeqNum
391 from wip_operation_resources
392 where wip_entity_id = p_wipEntityID
393 and organization_id = p_orgID
394 and operation_seq_num = p_midPntOpSeqNum
395 and substitute_group_num = p_subGrpNum;
396 else --current op resource
397
398 if(l_logLevel <= wip_constants.full_logging) then
399 wip_logger.log('current op res', l_retStatus);
400 end if;
401
402 x_minOpSeqNum := p_midPntOpSeqNum;
403 x_maxOpSeqNum := p_midPntOpSeqNum;
404
405 if (p_midPntOpSeqNum is not null AND
406 p_midPntResSeqNum is not null) then
407 select nvl(schedule_seq_num, resource_seq_num)
408 into x_minSchedSeqNum
409 from wip_operation_resources
410 where wip_entity_id = p_wipEntityID
411 and organization_id = p_orgID
412 and operation_seq_num = p_midPntOpSeqNum
413 and resource_seq_num = p_midPntResSeqNum;
414 else
415 x_minSchedSeqNum := null;
416 end if;
417
418 x_maxSchedSeqNum := x_minSchedSeqNum;
419 end if;
420
421 if(l_logLevel <= wip_constants.full_logging) then
422 wip_logger.log('min op seq' || x_minOpSeqNum, l_retStatus);
423 wip_logger.log('min sched seq' || x_minSchedSeqNum, l_retStatus);
424 wip_logger.log('max op seq' || x_maxOpSeqNum, l_retStatus);
425 wip_logger.log('max sched seq' || x_maxSchedSeqNum, l_retStatus);
426 end if;
427
428 if(l_logLevel <= wip_constants.trace_logging) then
429 wip_logger.exitPoint(p_procName => 'wip_infinite_scheduler_pvt.schedule',
430 p_procReturnStatus => 'N/A',
431 p_msg => 'success',
432 x_returnStatus => l_retStatus);
433 end if;
434 end getMidPointInfo;
435
436
437 -- Fixed bug 5440007
438 -- Added a new parameter p_quantity
439
440 procedure readJobSchedule(p_wipEntityID in number,
441 p_repSchedID in number := null,
442 p_orgID in number,
443 p_minOpSeqNum in number,
444 p_minSchedSeqNum in number,
445 p_maxOpSeqNum in number,
446 p_maxSchedSeqNum in number,
447 p_quantity IN NUMBER := null, -- Fixed bug 5440007
448 x_resTbls out nocopy wip_infResSched_grp.op_res_rectbl_t,
449 x_assignedUnits out nocopy num_tbl_t,
450 x_returnStatus out nocopy varchar2) is
451 l_loglevel NUMBER := fnd_log.g_current_runtime_level;
452 l_params wip_logger.param_tbl_t;
453 l_retStatus VARCHAR2(1);
454 l_hrUOM VARCHAR2(3);
455 l_hrVal NUMBER;
456 l_uomClass VARCHAR2(10);
457 l_dummy NUMBER;
458
459 --the following cursors simply lock the records writeJobSchedule() will later modify
460 cursor c_job is
461 select 1
462 from wip_discrete_jobs
463 where wip_entity_id = p_wipEntityID
464 and organization_id = p_orgID
465 for update nowait;
466
467 cursor c_ops is
468 select 1
469 from wip_operations
470 where wip_entity_id = p_wipEntityID
471 and organization_id = p_orgID
472 and operation_seq_num between p_minOpSeqNum and p_maxOpSeqNum
473 for update nowait;
474
475 cursor c_mtlReqs is
476 select 1
477 from wip_requirement_operations
478 where wip_entity_id = p_wipEntityID
479 and organization_id = p_orgID
480 and operation_seq_num between p_minOpSeqNum and p_maxOpSeqNum
481 for update nowait;
482
483 cursor c_resUsgs is
484 select 1
485 from wip_operation_resource_usage woru,
486 wip_operation_resources wor
487 where wor.wip_entity_id = p_wipEntityID
488 and wor.organization_id = p_orgID
489 and ( wor.operation_seq_num < p_maxOpSeqNum
490 and wor.operation_seq_num > p_minOpSeqNum
491 or ( p_minOpSeqNum <> p_maxOpSeqNum
492 and wor.operation_seq_num = p_minOpSeqNum
493 and nvl(wor.schedule_seq_num, wor.resource_seq_num) >= nvl(p_minSchedSeqNum, nvl(wor.schedule_seq_num, wor.resource_seq_num))
494 )
495 or ( p_minOpSeqNum <> p_maxOpSeqNum
496 and wor.operation_seq_num = p_maxOpSeqNum
497 and nvl(wor.schedule_seq_num, wor.resource_seq_num) <= nvl(p_maxSchedSeqNum, nvl(wor.schedule_seq_num, wor.resource_seq_num))
498 )
499 or ( p_minOpSeqNum = p_maxOpSeqNum
500 and wor.operation_seq_num = p_maxOpSeqNum
501 and nvl(wor.schedule_seq_num, wor.resource_seq_num) between nvl(p_minSchedSeqNum, nvl(wor.schedule_seq_num, wor.resource_seq_num)) and nvl(p_maxSchedSeqNum, nvl(wor.schedule_seq_num, wor.resource_seq_num))
502 )
503 )
504 and woru.organization_id = wor.organization_id
505 and woru.wip_entity_id = wor.wip_entity_id
506 and woru.operation_seq_num = wor.operation_seq_num
507 and woru.resource_seq_num = wor.resource_seq_num
508 for update nowait;
509
510 cursor c_resInsts is
511 select 1
512 from wip_op_resource_instances wori,
513 wip_operation_resources wor
514 where wor.wip_entity_id = p_wipEntityID
515 and wor.organization_id = p_orgID
516 and ( wor.operation_seq_num < p_maxOpSeqNum
517 and wor.operation_seq_num > p_minOpSeqNum
518 or ( p_minOpSeqNum <> p_maxOpSeqNum
519 and wor.operation_seq_num = p_minOpSeqNum
520 and nvl(wor.schedule_seq_num, wor.resource_seq_num) >= nvl(p_minSchedSeqNum, nvl(wor.schedule_seq_num, wor.resource_seq_num))
521 )
522 or ( p_minOpSeqNum <> p_maxOpSeqNum
523 and wor.operation_seq_num = p_maxOpSeqNum
524 and nvl(wor.schedule_seq_num, wor.resource_seq_num) <= nvl(p_maxSchedSeqNum, nvl(wor.schedule_seq_num, wor.resource_seq_num))
525 )
526 or ( p_minOpSeqNum = p_maxOpSeqNum
527 and wor.operation_seq_num = p_maxOpSeqNum
528 and nvl(wor.schedule_seq_num, wor.resource_seq_num) between nvl(p_minSchedSeqNum, nvl(wor.schedule_seq_num, wor.resource_seq_num)) and nvl(p_maxSchedSeqNum, nvl(wor.schedule_seq_num, wor.resource_seq_num))
529 )
530 )
531 and wori.organization_id = wor.organization_id
532 and wori.wip_entity_id = wor.wip_entity_id
533 and wori.operation_seq_num = wor.operation_seq_num
534 and wori.resource_seq_num = wor.resource_seq_num
535 for update nowait;
536 begin
537
538 if (l_logLevel <= wip_constants.trace_logging) then
539 l_params(1).paramName := 'p_wipEntityID';
540 l_params(1).paramValue := p_wipEntityID;
541 l_params(2).paramName := 'p_repSchedID';
542 l_params(2).paramValue := p_repSchedID;
543 l_params(3).paramName := 'p_orgID';
544 l_params(3).paramValue := p_orgID;
545 l_params(4).paramName := 'p_minOpSeqNum';
546 l_params(4).paramValue := p_minOpSeqNum;
547 l_params(5).paramName := 'p_minSchedSeqNum';
548 l_params(5).paramValue := p_minSchedSeqNum;
549 l_params(6).paramName := 'p_maxOpSeqNum';
550 l_params(6).paramValue := p_maxOpSeqNum;
551 l_params(7).paramName := 'p_maxSchedSeqNum';
552 l_params(7).paramValue := p_maxSchedSeqNum;
553 l_params(8).ParamName := 'P_quantity';
554 l_params(8).ParamValue := p_quantity;
555
556 wip_logger.entryPoint(p_procName => 'wip_infinite_scheduler_pvt.readJobSchedule',
557 p_params => l_params,
558 x_returnStatus => l_retStatus);
559 end if;
560 x_returnStatus := fnd_api.g_ret_sts_success;
561
562 l_hrUOM := fnd_profile.value('BOM:HOUR_UOM_CODE');
563 select conversion_rate, uom_class
564 into l_hrVal, l_uomClass
565 from mtl_uom_conversions
566 where uom_code = l_hrUOM
567 and nvl(disable_date, sysdate + 1) > sysdate
568 and inventory_item_id = 0;
569
570 --Fix for bug#4888567
571 --UOM conversion from Resource UOM to HOUR UOM was not properly done
572 -- Using the standard function inv_convert.inv_um_convert to convert
573 -- between resource UOM and standard HR uom code received from BOM profile value.
574
575
576 -- Fixed bug 5440007
577 -- Added decode clause to derive the quantity from wip_discrete_jobs only if the parameter
578 -- p_quantity is null
579 select wor.operation_seq_num,
580 wor.resource_id,
581 nvl(bdr.share_from_dept_id, bdr.department_id),
582 wor.resource_seq_num,
583 wor.schedule_seq_num,
584 wor.scheduled_flag,
585 bdr.available_24_hours_flag,
586 --l_hrVal * nvl(muc.conversion_rate,0) *
587 --Bug 4614036:Rounding of usage rate to next minute is handled.
588 /*
589 round((decode(wor.basis_type, wip_constants.per_lot, 1, decode(p_quantity,null,wdj.start_quantity,p_quantity)) *
590 inv_convert.inv_um_convert(0,
591 NULL,
592 wor.usage_rate_or_amount,
593 wor.UOM_CODE,
594 l_hrUOM,
595 NULL,
596 NULL )),0)
597 -- NULL )*60)+0.5,0) --Fixed bug #5618787
598 / (
599 -- 60*24 * least(wor.assigned_units, bdr.capacity_units) * --Fixed bug #5618787
600 24 * least(wor.assigned_units, bdr.capacity_units) *
601 decode(wp.include_resource_utilization,
602 wip_constants.yes, nvl(bdr.utilization, 1), 1) *
603 decode(wp.include_resource_efficiency,
604 wip_constants.yes, nvl(bdr.efficiency, 1), 1)
605 ),
606 */
607 -- Start of fix for Bug #5657612: Use ceil function to round up the usage rate to next minute
608 -- bug 6741020: pass a precision = 6 to inv_um_convert as resource usage form field
609 -- supports 6 decimals. INV assumes a default of 5 decimals if null is passed. This causes
610 -- errors in calculation.
611 ceil((decode(wor.basis_type, wip_constants.per_lot, 1, decode(p_quantity,null,wdj.start_quantity,p_quantity)) *
612 inv_convert.inv_um_convert(0,
613 6,
614 wor.usage_rate_or_amount,
615 wor.UOM_CODE,
616 l_hrUOM,
617 NULL,
618 NULL )*60) /
619 ( least(wor.assigned_units, bdr.capacity_units) *
620 decode(wp.include_resource_utilization,
621 wip_constants.yes, nvl(bdr.utilization, 1), 1) *
622 decode(wp.include_resource_efficiency,
623 wip_constants.yes, nvl(bdr.efficiency, 1), 1) )
624 ) / (60 * 24),
625 -- End of fix for Bug #5657612
626 wor.assigned_units
627 bulk collect into x_resTbls.opSeqNum,
628 x_resTbls.resID,
629 x_resTbls.deptID,
630 x_resTbls.resSeqNum,
631 x_resTbls.schedSeqNum,
632 x_resTbls.schedFlag,
633 x_resTbls.avail24Flag,
634 x_resTbls.totalDaysUsg,
635 x_assignedUnits
636 from wip_discrete_jobs wdj,
637 wip_operations wo,
638 wip_operation_resources wor,
639 mtl_uom_conversions muc,
640 bom_department_resources bdr,
641 wip_parameters wp
642 where wp.organization_id = wdj.organization_id
643 and wdj.wip_entity_id = p_wipEntityID
644 and wdj.organization_id = p_orgID
645 and wdj.wip_entity_id = wo.wip_entity_id
646 and wdj.organization_id = wo.organization_id
647 and wo.wip_entity_id = wor.wip_entity_id
648 and wo.organization_id = wor.organization_id
649 and wo.operation_seq_num = wor.operation_seq_num
650 and bdr.resource_id = wor.resource_id
651 and bdr.department_id = nvl(wor.department_id, wo.department_id)
652 and wor.uom_code = muc.uom_code (+)
653 and muc.uom_class (+)= l_uomClass
654 and muc.inventory_item_id (+)= 0
655 and (
656 ( wor.operation_seq_num < p_maxOpSeqNum
657 and wor.operation_seq_num > p_minOpSeqNum
658 )
659 or ( p_minOpSeqNum <> p_maxOpSeqNum
660 and wor.operation_seq_num = p_minOpSeqNum
661 and nvl(wor.schedule_seq_num, wor.resource_seq_num) >= nvl(p_minSchedSeqNum, nvl(wor.schedule_seq_num, wor.resource_seq_num))
662 )
663 or ( p_minOpSeqNum <> p_maxOpSeqNum
664 and wor.operation_seq_num = p_maxOpSeqNum
665 and nvl(wor.schedule_seq_num, wor.resource_seq_num) <= nvl(p_maxSchedSeqNum, nvl(wor.schedule_seq_num, wor.resource_seq_num))
666 )
667 or ( p_minOpSeqNum = p_maxOpSeqNum
668 and wor.operation_seq_num = p_maxOpSeqNum
669 and nvl(wor.schedule_seq_num, wor.resource_seq_num) between nvl(p_minSchedSeqNum, nvl(wor.schedule_seq_num, wor.resource_seq_num)) and nvl(p_maxSchedSeqNum, nvl(wor.schedule_seq_num, wor.resource_seq_num))
670 )
671 )
672 order by wor.operation_seq_num, nvl(wor.schedule_seq_num, wor.resource_seq_num)
673 for update of wor.start_date nowait;
674
675 --lock the job
676 open c_job;
677 close c_job;
678
679 --lock the routing
680 open c_ops;
681 close c_ops;
682 --lock the BOM
683 open c_mtlReqs;
684 close c_mtlReqs;
685
686 --lock the usages (will be deleted later)
687 open c_resUsgs;
688 close c_resUsgs;
689
690 --lock the resource instances
691 open c_resInsts;
692 close c_resInsts;
693
694 if (l_logLevel <= wip_constants.trace_logging) then
695 wip_logger.exitPoint(p_procName => 'wip_infinite_scheduler_pvt.readJobSchedule',
696 p_procReturnStatus => x_returnStatus,
697 p_msg => 'success',
698 x_returnStatus => l_retStatus);
699 end if;
700 exception
701 when others then
702 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
703 fnd_msg_pub.add_exc_msg(p_pkg_name => 'wip_infinite_scheduler_pvt',
704 p_procedure_name => 'readJobSchedule',
705 p_error_text => SQLERRM);
706 if (l_logLevel <= wip_constants.trace_logging) then
707 wip_logger.exitPoint(p_procName => 'wip_infinite_scheduler_pvt.readJobSchedule',
708 p_procReturnStatus => x_returnStatus,
709 p_msg => 'unexp error: ' || SQLERRM,
710 x_returnStatus => l_retStatus);
711 end if;
712 end readJobSchedule;
713
714 procedure writeJobSchedule(p_wipEntityID in number,
715 p_repSchedID in number := null,
716 p_orgID in number,
717 p_schedMethod in number,
718 p_minOpSeqNum in number,
719 p_minSchedSeqNum in number,
720 p_maxOpSeqNum in number,
721 p_maxSchedSeqNum in number,
722 p_anchorDate in date,
723 p_assignedUnits in num_tbl_t,
724 x_resTbls in out nocopy wip_infResSched_grp.op_res_rectbl_t,
725 x_returnStatus out nocopy varchar2) is
726 type op_rectbl_t is record (opSeqNum num_tbl_t,
727 startDate date_tbl_t,
728 endDate date_tbl_t);
729
730
731 l_opTbls op_rectbl_t;
732 l_opSchYesTbls op_rectbl_t;
733 l_startOpIdx NUMBER;
734 l_endOpIdx NUMBER;
735
736 --standard who columns
737 l_sysDate DATE := sysdate;
738 l_userID NUMBER := fnd_global.user_id;
739 l_loginID NUMBER := fnd_global.login_id;
740 l_reqID NUMBER := fnd_global.conc_request_id;
741 l_progApplID NUMBER := fnd_global.prog_appl_id;
742 l_progID NUMBER := fnd_global.conc_program_id;
743 l_minResStartDate DATE;
744 l_maxResEndDate DATE;
745 l_jobStartDate DATE;
746 l_jobCplDate DATE;
747 l_logLevel NUMBER := fnd_log.g_current_runtime_level;
748 l_params wip_logger.param_tbl_t;
749 l_retStatus VARCHAR2(1);
750 begin
751 savepoint wipiscdb100;
752 if (l_logLevel <= wip_constants.trace_logging) then
753 l_params(1).paramName := 'p_wipEntityID';
754 l_params(1).paramValue := p_wipEntityID;
755 l_params(2).paramName := 'p_repSchedID';
756 l_params(2).paramValue := p_repSchedID;
757 l_params(3).paramName := 'p_orgID';
758 l_params(3).paramValue := p_orgID;
759 l_params(4).paramName := 'p_schedMethod';
760 l_params(4).paramValue := p_schedMethod;
761 l_params(5).paramName := 'p_minOpSeqNum';
762 l_params(5).paramValue := p_minOpSeqNum;
763 l_params(6).paramName := 'p_minSchedSeqNum';
764 l_params(6).paramValue := p_minSchedSeqNum;
765 l_params(7).paramName := 'p_maxOpSeqNum';
766 l_params(7).paramValue := p_maxOpSeqNum;
767 l_params(8).paramName := 'p_maxSchedSeqNum';
768 l_params(8).paramValue := p_maxSchedSeqNum;
769 l_params(9).paramName := 'p_anchorDate';
770 l_params(9).paramValue := to_char(p_anchorDate, g_logDateFmt);
771 for i in 1..p_assignedUnits.count loop
772 l_params(9+i).paramName := 'p_assignedUnits(' || i || ')';
773 l_params(9+i).paramValue := p_assignedUnits(i);
774 end loop;
775 wip_logger.entryPoint(p_procName => 'wip_infinite_scheduler_pvt.writeJobSchedule',
776 p_params => l_params,
777 x_returnStatus => l_retStatus);
778 end if;
779 x_returnStatus := fnd_api.g_ret_sts_success;
780
781 --update resources
782 forall i in 1..x_resTbls.resID.count
783 update wip_operation_resources
784 set start_date = x_resTbls.startDate(i),
785 completion_date = x_resTbls.endDate(i),
786 last_update_date = l_sysdate,
787 last_updated_by = l_userID,
788 last_update_login = l_loginID,
789 request_id = l_reqID,
790 program_application_id = l_progApplID,
791 program_id = l_progID,
792 program_update_date = l_sysDate
793 where wip_entity_id = p_wipEntityID
794 and organization_id = p_orgID
795 and operation_seq_num = x_resTbls.opSeqNum(i)
796 and resource_seq_num = x_resTbls.resSeqNum(i);
797
798 if(l_logLevel <= wip_constants.full_logging) then
799 wip_logger.log('wrote resources', l_retStatus);
800 end if;
801
802
803 forall i in 1..x_resTbls.resID.count
804 update wip_op_resource_instances
805 set start_date = x_resTbls.startDate(i),
806 completion_date = x_resTbls.endDate(i),
807 last_update_date = l_sysdate,
808 last_updated_by = l_userID,
809 last_update_login = l_loginID
810 where wip_entity_id = p_wipEntityID
811 and organization_id = p_orgID
812 and operation_seq_num = x_resTbls.opSeqNum(i)
813 and resource_seq_num = x_resTbls.resSeqNum(i);
814
815 if(l_logLevel <= wip_constants.full_logging) then
816 wip_logger.log('wrote resource instances', l_retStatus);
817 end if;
818
819 delete wip_operation_resource_usage
820 where (organization_id, wip_entity_id, operation_seq_num, resource_seq_num) in
821 (select organization_id,
822 wip_entity_id,
823 operation_seq_num,
824 resource_seq_num
825 from wip_operation_resources wor
826 where wor.wip_entity_id = p_wipEntityID
827 and wor.organization_id = p_orgID
828 and ( wor.operation_seq_num < p_maxOpSeqNum
829 and wor.operation_seq_num > p_minOpSeqNum
830 or ( p_minOpSeqNum <> p_maxOpSeqNum
831 and wor.operation_seq_num = p_minOpSeqNum
832 and nvl(wor.schedule_seq_num, wor.resource_seq_num) >= nvl(p_minSchedSeqNum, nvl(wor.schedule_seq_num, wor.resource_seq_num))
833 )
834 or ( p_minOpSeqNum <> p_maxOpSeqNum
835 and wor.operation_seq_num = p_maxOpSeqNum
836 and nvl(wor.schedule_seq_num, wor.resource_seq_num) <= nvl(p_maxSchedSeqNum, nvl(wor.schedule_seq_num, wor.resource_seq_num))
837 )
838 or ( p_minOpSeqNum = p_maxOpSeqNum
839 and wor.operation_seq_num = p_maxOpSeqNum
840 and nvl(wor.schedule_seq_num, wor.resource_seq_num) between nvl(p_minSchedSeqNum, nvl(wor.schedule_seq_num, wor.resource_seq_num)) and nvl(p_maxSchedSeqNum, nvl(wor.schedule_seq_num, wor.resource_seq_num))
841 )
842 )
843 );
844
845 for i in 1..x_resTbls.resID.count loop
846 if(x_resTbls.usgStartIdx(i) is not null) then
847 forall j in x_resTbls.usgStartIdx(i)..x_resTbls.usgEndIdx(i)
848 insert into wip_operation_resource_usage
849 (wip_entity_id,
850 operation_seq_num,
851 resource_seq_num,
852 organization_id,
853 start_date,
854 completion_date,
855 assigned_units,
856 cumulative_processing_time,
857 last_update_date,
858 last_updated_by,
859 creation_date,
860 created_by,
861 last_update_login,
862 request_id,
863 program_application_id,
864 program_id,
865 program_update_date)
866 values
867 (p_wipEntityID,
868 x_resTbls.opSeqNum(i),
869 x_resTbls.resSeqNum(i),
870 p_orgID,
871 x_resTbls.usgStartDate(j),
872 x_resTbls.usgEndDate(j),
873 p_assignedUnits(i),
874 x_resTbls.usgCumMinProcTime(j),
875 l_sysdate,
876 l_userID,
877 l_sysdate,
878 l_userID,
879 l_loginID,
880 l_reqID,
881 l_progApplID,
882 l_progID,
883 l_sysdate);
884 end if;
885 end loop;
886 if(l_logLevel <= wip_constants.full_logging) then
887 wip_logger.log('wrote resource usages', l_retStatus);
888 end if;
889
890 insert into wip_operation_resource_usage
891 (wip_entity_id,
892 operation_seq_num,
893 resource_seq_num,
894 organization_id,
895 start_date,
896 completion_date,
897 assigned_units,
898 instance_id,
899 serial_number,
900 cumulative_processing_time,
901 last_update_date,
902 last_updated_by,
903 creation_date,
904 created_by,
905 last_update_login,
906 request_id,
907 program_application_id,
908 program_id,
909 program_update_date)
910 select p_wipEntityID,
911 woru.operation_seq_num,
912 woru.resource_seq_num,
913 p_orgID,
914 woru.start_date,
915 woru.completion_date,
916 1,
917 wori.instance_id,
918 wori.serial_number,
919 woru.cumulative_processing_time,
920 l_sysdate,
921 l_userID,
922 l_sysdate,
923 l_userID,
924 l_loginID,
925 l_reqID,
926 l_progApplID,
927 l_progID,
928 l_sysdate
929 from wip_operation_resource_usage woru,
930 wip_op_resource_instances wori,
931 wip_operation_resources wor
932 where woru.wip_entity_id = wori.wip_entity_id
933 and woru.operation_seq_num = wori.operation_seq_num
934 and woru.resource_seq_num = wori.resource_seq_num
935 and woru.organization_id = wori.organization_id
936 and wor.wip_entity_id = p_wipEntityID
937 and wor.organization_id = p_orgID
938 and ( wor.operation_seq_num < p_maxOpSeqNum
939 and wor.operation_seq_num > p_minOpSeqNum
940 or ( p_minOpSeqNum <> p_maxOpSeqNum
941 and wor.operation_seq_num = p_minOpSeqNum
942 and nvl(wor.schedule_seq_num, wor.resource_seq_num) >= nvl(p_minSchedSeqNum, nvl(wor.schedule_seq_num, wor.resource_seq_num))
943 )
944 or ( p_minOpSeqNum <> p_maxOpSeqNum
945 and wor.operation_seq_num = p_maxOpSeqNum
946 and nvl(wor.schedule_seq_num, wor.resource_seq_num) <= nvl(p_maxSchedSeqNum, nvl(wor.schedule_seq_num, wor.resource_seq_num))
947 )
948 or ( p_minOpSeqNum = p_maxOpSeqNum
949 and wor.operation_seq_num = p_maxOpSeqNum
950 and nvl(wor.schedule_seq_num, wor.resource_seq_num) between nvl(p_minSchedSeqNum, nvl(wor.schedule_seq_num, wor.resource_seq_num)) and nvl(p_maxSchedSeqNum, nvl(wor.schedule_seq_num, wor.resource_seq_num))
951 )
952 )
953 and woru.organization_id = wor.organization_id
954 and woru.wip_entity_id = wor.wip_entity_id
955 and woru.operation_seq_num = wor.operation_seq_num
956 and woru.resource_seq_num = wor.resource_seq_num;
957
958
959 if(l_logLevel <= wip_constants.full_logging) then
960 wip_logger.log('wrote ' || SQL%ROWCOUNT || ' resource instance usages', l_retStatus);
961 end if;
962
963 if(l_logLevel <= wip_constants.full_logging) then
964 wip_logger.log('about to start op processing', l_retStatus);
965 end if;
966 --base operation dates off of op resource dates
967 select wo.operation_seq_num,
968 min(wor.start_date),
969 max(wor.completion_date)
970 bulk collect into
971 l_opTbls.opSeqNum,
972 l_opTbls.startDate,
973 l_opTbls.endDate
974 from wip_operations wo,
975 wip_operation_resources wor
976 where wo.wip_entity_id = p_wipEntityID
977 and wo.organization_id = p_orgID
978 and wo.wip_entity_id = wor.wip_entity_id (+)
979 and wo.organization_id = wor.organization_id (+)
980 and wo.operation_seq_num = wor.operation_seq_num (+)
981 group by wo.operation_seq_num;
982
983 select wo.operation_seq_num,
984 min(wor.start_date),
985 max(wor.completion_date)
986 bulk collect into
987 l_opSchYesTbls.opSeqNum,
988 l_opSchYesTbls.startDate,
989 l_opSchYesTbls.endDate
990 from wip_operations wo,
991 wip_operation_resources wor
992 where wo.wip_entity_id = p_wipEntityID
993 and wo.organization_id = p_orgID
994 and wo.wip_entity_id = wor.wip_entity_id (+)
995 and wo.organization_id = wor.organization_id (+)
996 and wo.operation_seq_num = wor.operation_seq_num (+)
997 and wip_constants.sched_yes = wor.scheduled_flag (+)
998 group by wo.operation_seq_num;
999
1000 if(l_logLevel <= wip_constants.full_logging) then
1001 wip_logger.log('about to start op processing', l_retStatus);
1002 end if;
1003
1004 for i in 1..l_opTbls.opSeqNum.count loop
1005 if(l_opTbls.startDate(i) is null) then
1006
1007 --look for previous operations with a yes resource
1008 if(i > 1) then
1009 for j in reverse 1..(i-1) loop
1010 if(l_opSchYesTbls.endDate(j) is not null) then
1011 l_opTbls.startDate(i) := l_opSchYesTbls.endDate(j);
1012 l_opTbls.endDate(i) := l_opTbls.startDate(i);
1013 exit;
1014 end if;
1015 end loop;
1016 end if;
1017
1018 --if the date is still null, look for future ops with a yes resource
1019 if(l_opTbls.startDate(i) is null) then
1020 if(i < l_opTbls.opSeqNum.count) then
1021 for j in (i+1)..l_opTbls.opSeqNum.count loop
1022 if(l_opSchYesTbls.endDate(j) is not null) then
1023 l_opTbls.startDate(i) := l_opSchYesTbls.endDate(j);
1024 l_opTbls.endDate(i) := l_opSchYesTbls.endDate(j);
1025 exit;
1026 end if;
1027 end loop;
1028 end if;
1029 end if;
1030
1031 --if the date is still null, this means that there are no scheduled yes
1032 --resources in the job.
1033 if(l_opTbls.startDate(i) is null) then
1034 l_opTbls.startDate(i) := p_anchorDate;
1035 l_opTbls.endDate(i) := p_anchorDate;
1036 for j in 1..x_resTbls.resID.count loop
1037 if(x_resTbls.opSeqNum(j) < l_opTbls.opSeqNum(i) and
1038 x_resTbls.schedFlag(j) = wip_constants.sched_prior) then
1039 l_opTbls.startDate(i) := x_resTbls.endDate(j);
1040 l_opTbls.endDate(i) := x_resTbls.endDate(j);
1041 else
1042 exit;
1043 end if;
1044 end loop;
1045 end if;
1046
1047 end if;
1048 end loop;
1049
1050 --determine which operations to update
1051 for i in 1..l_opTbls.opSeqNum.count loop
1052 if(l_opTbls.opSeqNum(i) = p_minOpSeqNum) then
1053 l_startOpIdx := i;
1054 end if;
1055 if(l_opTbls.opSeqNum(i) = p_maxOpSeqNum) then
1056 l_endOpIdx := i;
1057 exit;
1058 end if;
1059 end loop;
1060
1061 if(l_logLevel <= wip_constants.full_logging) then
1062 wip_logger.log('about to write ops', l_retStatus);
1063 end if;
1064
1065 if(l_startOpIdx is not null) then
1066 forall i in l_startOpIdx..l_endOpIdx
1067 update wip_operations
1068 set first_unit_start_date = l_opTbls.startDate(i),
1069 last_unit_start_date = l_opTbls.startDate(i),
1070 first_unit_completion_date = l_opTbls.endDate(i),
1071 last_unit_completion_date = l_opTbls.endDate(i),
1072 last_update_date = l_sysdate,
1073 last_updated_by = l_userID,
1074 last_update_login = l_loginID,
1075 request_id = l_reqID,
1076 program_application_id = l_progApplID,
1077 program_id = l_progID,
1078 program_update_date = l_sysDate
1079 where wip_entity_id = p_wipEntityID
1080 and organization_id = p_orgID
1081 and operation_seq_num = l_opTbls.opSeqNum(i);
1082 if(l_logLevel <= wip_constants.full_logging) then
1083 wip_logger.log('wrote ops', l_retStatus);
1084 end if;
1085
1086 --update mtl requirement dates if job has routing
1087 forall i in l_startOpIdx..l_endOpIdx
1088 update wip_requirement_operations
1089 set date_required = l_opTbls.startDate(i),
1090 last_update_date = l_sysdate,
1091 last_updated_by = l_userID,
1092 last_update_login = l_loginID,
1093 request_id = l_reqID,
1094 program_application_id = l_progApplID,
1095 program_id = l_progID,
1096 program_update_date = l_sysDate
1097 where wip_entity_id = p_wipEntityID
1098 and organization_id = p_orgID
1099 and operation_seq_num = l_opTbls.opSeqNum(i);
1100 else
1101 --update mtl requirement dates if job doesn't have a routing
1102 update wip_requirement_operations
1103 set date_required = p_anchorDate,
1104 last_update_date = l_sysdate,
1105 last_updated_by = l_userID,
1106 last_update_login = l_loginID,
1107 request_id = l_reqID,
1108 program_application_id = l_progApplID,
1109 program_id = l_progID,
1110 program_update_date = l_sysDate
1111 where wip_entity_id = p_wipEntityID
1112 and organization_id = p_orgID
1113 and operation_seq_num = 1;
1114 end if;
1115
1116 if(l_logLevel <= wip_constants.full_logging) then
1117 wip_logger.log('wrote material requirements', l_retStatus);
1118 end if;
1119
1120 --now update job dates
1121 --must select from table to get resource dates b/c of partial job scheduling
1122 select min(start_date), max(completion_date)
1123 into l_minResStartDate, l_maxResEndDate
1124 from wip_operation_resources
1125 where wip_entity_id = p_wipEntityID
1126 and organization_id = p_orgID;
1127
1128 if(l_logLevel <= wip_constants.full_logging) then
1129 wip_logger.log('min res date:' || to_char(l_minResStartDate, g_logDateFmt), l_retStatus);
1130 wip_logger.log('max res date:' || to_char(l_maxResEndDate, g_logDateFmt), l_retStatus);
1131 end if;
1132 --if there are no resources, set the local variables to p_anchorDate
1133 --so below update is correct.
1134 l_jobStartDate := nvl(l_minResStartDate, p_anchorDate);
1135 l_jobCplDate := nvl(l_maxResEndDate, p_anchorDate);
1136
1137 update wip_discrete_jobs
1138 set scheduled_start_date = l_jobStartDate,
1139 scheduled_completion_date = l_jobCplDate,
1140 last_update_date = l_sysdate,
1141 last_updated_by = l_userID,
1142 last_update_login = l_loginID,
1143 request_id = l_reqID,
1144 program_application_id = l_progApplID,
1145 program_id = l_progID,
1146 program_update_date = l_sysDate
1147 where wip_entity_id = p_wipEntityID
1148 and organization_id = p_orgID;
1149
1150 if (l_logLevel <= wip_constants.trace_logging) then
1151 wip_logger.exitPoint(p_procName => 'wip_infinite_scheduler_pvt.writeJobSchedule',
1152 p_procReturnStatus => x_returnStatus,
1153 p_msg => 'success',
1154 x_returnStatus => l_retStatus);
1155 end if;
1156 exception
1157 when others then
1158 rollback to wipiscdb100;
1159 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
1160 fnd_msg_pub.add_exc_msg(p_pkg_name => 'wip_infinite_scheduler_pvt',
1161 p_procedure_name => 'writeJobSchedule',
1162 p_error_text => SQLERRM);
1163 if (l_logLevel <= wip_constants.trace_logging) then
1164 wip_logger.exitPoint(p_procName => 'wip_infinite_scheduler_pvt.writeJobSchedule',
1165 p_procReturnStatus => x_returnStatus,
1166 p_msg => 'unexp error: ' || SQLERRM,
1167 x_returnStatus => l_retStatus);
1168 end if;
1169 end writeJobSchedule;
1170
1171
1172 end wip_infinite_scheduler_pvt;