[Home] [Help]
PACKAGE BODY: APPS.WIP_INFINITE_SCHEDULER_PVT
Source
1 package body wip_infinite_scheduler_pvt as
2 /* $Header: wipiscdb.pls 120.9.12020000.3 2012/07/31 07:15:56 sisankar 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
327 select max(nvl(schedule_seq_num, resource_seq_num))
324 where wip_entity_id = p_wipEntityID
325 and organization_id = p_orgID;
326
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
464 and organization_id = p_orgID
461 select 1
462 from wip_discrete_jobs
463 where wip_entity_id = p_wipEntityID
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,
583 wor.schedule_seq_num,
580 wor.resource_id,
581 nvl(bdr.share_from_dept_id, bdr.department_id),
582 wor.resource_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 --Bug 9965479 (FP 9818184): Increase precision from 6 to 10 to correct very low usage rates
612 /*Bug#13718464- Checking if any of the units is 0, so that the totalDaysUsg is 0
613 - This handles the case of zero denominator*/
614 decode(( least(wor.assigned_units, bdr.capacity_units) *
615 decode(wp.include_resource_utilization,
616 wip_constants.yes, nvl(bdr.utilization, 1), 1) *
617 decode(wp.include_resource_efficiency,
618 wip_constants.yes, nvl(bdr.efficiency, 1), 1) ),0,0,
619 ceil((decode(wor.basis_type, wip_constants.per_lot, 1, decode(p_quantity,null,wdj.start_quantity,p_quantity)) *
620 inv_convert.inv_um_convert(0,
621 10,
622 wor.usage_rate_or_amount,
623 wor.UOM_CODE,
624 l_hrUOM,
625 NULL,
626 NULL )*60) /
627 ( least(wor.assigned_units, bdr.capacity_units) *
628 decode(wp.include_resource_utilization,
629 wip_constants.yes, nvl(bdr.utilization, 1), 1) *
630 decode(wp.include_resource_efficiency,
631 wip_constants.yes, nvl(bdr.efficiency, 1), 1) )
632 ) / (60 * 24)),
633 -- End of fix for Bug #5657612
634 wor.assigned_units
635 bulk collect into x_resTbls.opSeqNum,
636 x_resTbls.resID,
637 x_resTbls.deptID,
638 x_resTbls.resSeqNum,
639 x_resTbls.schedSeqNum,
640 x_resTbls.schedFlag,
641 x_resTbls.avail24Flag,
642 x_resTbls.totalDaysUsg,
643 x_assignedUnits
644 from wip_discrete_jobs wdj,
645 wip_operations wo,
646 wip_operation_resources wor,
647 mtl_uom_conversions muc,
648 bom_department_resources bdr,
649 wip_parameters wp
650 where wp.organization_id = wdj.organization_id
651 and wdj.wip_entity_id = p_wipEntityID
652 and wdj.organization_id = p_orgID
653 and wdj.wip_entity_id = wo.wip_entity_id
654 and wdj.organization_id = wo.organization_id
655 and wo.wip_entity_id = wor.wip_entity_id
656 and wo.organization_id = wor.organization_id
657 and wo.operation_seq_num = wor.operation_seq_num
658 and bdr.resource_id = wor.resource_id
659 and bdr.department_id = nvl(wor.department_id, wo.department_id)
660 and wor.uom_code = muc.uom_code (+)
661 and muc.uom_class (+)= l_uomClass
662 and muc.inventory_item_id (+)= 0
663 and (
664 ( wor.operation_seq_num < p_maxOpSeqNum
665 and wor.operation_seq_num > p_minOpSeqNum
666 )
667 or ( p_minOpSeqNum <> p_maxOpSeqNum
668 and wor.operation_seq_num = p_minOpSeqNum
669 and nvl(wor.schedule_seq_num, wor.resource_seq_num) >= nvl(p_minSchedSeqNum, nvl(wor.schedule_seq_num, wor.resource_seq_num))
670 )
671 or ( p_minOpSeqNum <> p_maxOpSeqNum
672 and wor.operation_seq_num = p_maxOpSeqNum
673 and nvl(wor.schedule_seq_num, wor.resource_seq_num) <= nvl(p_maxSchedSeqNum, nvl(wor.schedule_seq_num, wor.resource_seq_num))
674 )
675 or ( p_minOpSeqNum = p_maxOpSeqNum
676 and wor.operation_seq_num = p_maxOpSeqNum
677 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))
678 )
679 )
683 --lock the job
680 order by wor.operation_seq_num, nvl(wor.schedule_seq_num, wor.resource_seq_num)
681 for update of wor.start_date nowait;
682
684 open c_job;
685 close c_job;
686
687 --lock the routing
688 open c_ops;
689 close c_ops;
690 --lock the BOM
691 open c_mtlReqs;
692 close c_mtlReqs;
693
694 --lock the usages (will be deleted later)
695 open c_resUsgs;
696 close c_resUsgs;
697
698 --lock the resource instances
699 open c_resInsts;
700 close c_resInsts;
701
702 if (l_logLevel <= wip_constants.trace_logging) then
703 wip_logger.exitPoint(p_procName => 'wip_infinite_scheduler_pvt.readJobSchedule',
704 p_procReturnStatus => x_returnStatus,
705 p_msg => 'success',
706 x_returnStatus => l_retStatus);
707 end if;
708 exception
709 /*Fix bug 8914181 (FP 8894732)*/
710 when wip_constants.records_locked then
711 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
712 if (l_logLevel <= wip_constants.trace_logging) then
713 wip_logger.exitPoint(p_procName => 'wip_infinite_scheduler_pvt.readJobSchedule',
714 p_procReturnStatus => x_returnStatus,
715 p_msg => 'records were locked',
716 x_returnStatus => l_retStatus);
717 end if;
718 fnd_message.set_name('WIP', 'WIP_LOCKED_ROW_ALREADY_LOCKED');
719 fnd_msg_pub.add;
720 when others then
721 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
722 fnd_msg_pub.add_exc_msg(p_pkg_name => 'wip_infinite_scheduler_pvt',
723 p_procedure_name => 'readJobSchedule',
724 p_error_text => SQLERRM);
725 if (l_logLevel <= wip_constants.trace_logging) then
726 wip_logger.exitPoint(p_procName => 'wip_infinite_scheduler_pvt.readJobSchedule',
727 p_procReturnStatus => x_returnStatus,
728 p_msg => 'unexp error: ' || SQLERRM,
729 x_returnStatus => l_retStatus);
730 end if;
731 end readJobSchedule;
732
733 procedure writeJobSchedule(p_wipEntityID in number,
734 p_repSchedID in number := null,
735 p_orgID in number,
736 p_schedMethod in number,
737 p_minOpSeqNum in number,
738 p_minSchedSeqNum in number,
739 p_maxOpSeqNum in number,
740 p_maxSchedSeqNum in number,
741 p_anchorDate in date,
742 p_assignedUnits in num_tbl_t,
743 x_resTbls in out nocopy wip_infResSched_grp.op_res_rectbl_t,
744 x_returnStatus out nocopy varchar2) is
745 type op_rectbl_t is record (opSeqNum num_tbl_t,
746 startDate date_tbl_t,
747 endDate date_tbl_t);
748
749
750 l_opTbls op_rectbl_t;
751 l_opSchYesTbls op_rectbl_t;
752 l_startOpIdx NUMBER;
753 l_endOpIdx NUMBER;
754
755 --standard who columns
756 l_sysDate DATE := sysdate;
757 l_userID NUMBER := fnd_global.user_id;
758 l_loginID NUMBER := fnd_global.login_id;
759 l_reqID NUMBER := fnd_global.conc_request_id;
760 l_progApplID NUMBER := fnd_global.prog_appl_id;
761 l_progID NUMBER := fnd_global.conc_program_id;
762 l_minResStartDate DATE;
763 l_maxResEndDate DATE;
764 l_jobStartDate DATE;
765 l_jobCplDate DATE;
766 l_logLevel NUMBER := fnd_log.g_current_runtime_level;
767 l_params wip_logger.param_tbl_t;
768 l_retStatus VARCHAR2(1);
769 l_jobdate_align NUMBER := null;
770 begin
771 savepoint wipiscdb100;
772 if (l_logLevel <= wip_constants.trace_logging) then
773 l_params(1).paramName := 'p_wipEntityID';
774 l_params(1).paramValue := p_wipEntityID;
775 l_params(2).paramName := 'p_repSchedID';
776 l_params(2).paramValue := p_repSchedID;
777 l_params(3).paramName := 'p_orgID';
778 l_params(3).paramValue := p_orgID;
779 l_params(4).paramName := 'p_schedMethod';
780 l_params(4).paramValue := p_schedMethod;
781 l_params(5).paramName := 'p_minOpSeqNum';
782 l_params(5).paramValue := p_minOpSeqNum;
783 l_params(6).paramName := 'p_minSchedSeqNum';
784 l_params(6).paramValue := p_minSchedSeqNum;
785 l_params(7).paramName := 'p_maxOpSeqNum';
786 l_params(7).paramValue := p_maxOpSeqNum;
787 l_params(8).paramName := 'p_maxSchedSeqNum';
788 l_params(8).paramValue := p_maxSchedSeqNum;
789 l_params(9).paramName := 'p_anchorDate';
790 l_params(9).paramValue := to_char(p_anchorDate, g_logDateFmt);
791 for i in 1..p_assignedUnits.count loop
792 l_params(9+i).paramName := 'p_assignedUnits(' || i || ')';
793 l_params(9+i).paramValue := p_assignedUnits(i);
794 end loop;
795 wip_logger.entryPoint(p_procName => 'wip_infinite_scheduler_pvt.writeJobSchedule',
796 p_params => l_params,
797 x_returnStatus => l_retStatus);
798 end if;
799 x_returnStatus := fnd_api.g_ret_sts_success;
800
801 --update resources
802 forall i in 1..x_resTbls.resID.count
803 update wip_operation_resources
804 set start_date = x_resTbls.startDate(i),
805 completion_date = x_resTbls.endDate(i),
806 last_update_date = l_sysdate,
810 program_application_id = l_progApplID,
807 last_updated_by = l_userID,
808 last_update_login = l_loginID,
809 request_id = l_reqID,
811 program_id = l_progID,
812 program_update_date = l_sysDate
813 where wip_entity_id = p_wipEntityID
814 and organization_id = p_orgID
815 and operation_seq_num = x_resTbls.opSeqNum(i)
816 and resource_seq_num = x_resTbls.resSeqNum(i);
817
818 if(l_logLevel <= wip_constants.full_logging) then
819 wip_logger.log('wrote resources', l_retStatus);
820 end if;
821
822
823 forall i in 1..x_resTbls.resID.count
824 update wip_op_resource_instances
825 set start_date = x_resTbls.startDate(i),
826 completion_date = x_resTbls.endDate(i),
827 last_update_date = l_sysdate,
828 last_updated_by = l_userID,
829 last_update_login = l_loginID
830 where wip_entity_id = p_wipEntityID
831 and organization_id = p_orgID
832 and operation_seq_num = x_resTbls.opSeqNum(i)
833 and resource_seq_num = x_resTbls.resSeqNum(i);
834
835 if(l_logLevel <= wip_constants.full_logging) then
836 wip_logger.log('wrote resource instances', l_retStatus);
837 end if;
838
839 delete wip_operation_resource_usage
840 where (organization_id, wip_entity_id, operation_seq_num, resource_seq_num) in
841 (select organization_id,
842 wip_entity_id,
843 operation_seq_num,
844 resource_seq_num
845 from wip_operation_resources wor
846 where wor.wip_entity_id = p_wipEntityID
847 and wor.organization_id = p_orgID
848 and ( wor.operation_seq_num < p_maxOpSeqNum
849 and wor.operation_seq_num > p_minOpSeqNum
850 or ( p_minOpSeqNum <> p_maxOpSeqNum
851 and wor.operation_seq_num = p_minOpSeqNum
852 and nvl(wor.schedule_seq_num, wor.resource_seq_num) >= nvl(p_minSchedSeqNum, nvl(wor.schedule_seq_num, wor.resource_seq_num))
853 )
854 or ( p_minOpSeqNum <> p_maxOpSeqNum
855 and wor.operation_seq_num = p_maxOpSeqNum
856 and nvl(wor.schedule_seq_num, wor.resource_seq_num) <= nvl(p_maxSchedSeqNum, nvl(wor.schedule_seq_num, wor.resource_seq_num))
857 )
858 or ( p_minOpSeqNum = p_maxOpSeqNum
859 and wor.operation_seq_num = p_maxOpSeqNum
860 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))
861 )
862 )
863 );
864
865 for i in 1..x_resTbls.resID.count loop
866 if(x_resTbls.usgStartIdx(i) is not null) then
867 forall j in x_resTbls.usgStartIdx(i)..x_resTbls.usgEndIdx(i)
868 insert into wip_operation_resource_usage
869 (wip_entity_id,
870 operation_seq_num,
871 resource_seq_num,
872 organization_id,
873 start_date,
874 completion_date,
875 assigned_units,
876 cumulative_processing_time,
877 last_update_date,
878 last_updated_by,
879 creation_date,
880 created_by,
881 last_update_login,
882 request_id,
883 program_application_id,
884 program_id,
885 program_update_date)
886 values
887 (p_wipEntityID,
888 x_resTbls.opSeqNum(i),
889 x_resTbls.resSeqNum(i),
890 p_orgID,
891 x_resTbls.usgStartDate(j),
892 x_resTbls.usgEndDate(j),
893 p_assignedUnits(i),
894 x_resTbls.usgCumMinProcTime(j),
895 l_sysdate,
896 l_userID,
897 l_sysdate,
898 l_userID,
899 l_loginID,
900 l_reqID,
901 l_progApplID,
902 l_progID,
903 l_sysdate);
904 end if;
905 end loop;
906 if(l_logLevel <= wip_constants.full_logging) then
907 wip_logger.log('wrote resource usages', l_retStatus);
908 end if;
909
910 insert into wip_operation_resource_usage
911 (wip_entity_id,
912 operation_seq_num,
913 resource_seq_num,
914 organization_id,
915 start_date,
916 completion_date,
917 assigned_units,
918 instance_id,
919 serial_number,
920 cumulative_processing_time,
921 last_update_date,
922 last_updated_by,
923 creation_date,
924 created_by,
925 last_update_login,
926 request_id,
927 program_application_id,
928 program_id,
929 program_update_date)
930 select p_wipEntityID,
931 woru.operation_seq_num,
932 woru.resource_seq_num,
933 p_orgID,
934 woru.start_date,
935 woru.completion_date,
936 1,
937 wori.instance_id,
938 wori.serial_number,
939 woru.cumulative_processing_time,
940 l_sysdate,
941 l_userID,
942 l_sysdate,
943 l_userID,
944 l_loginID,
945 l_reqID,
946 l_progApplID,
947 l_progID,
948 l_sysdate
952 where woru.wip_entity_id = wori.wip_entity_id
949 from wip_operation_resource_usage woru,
950 wip_op_resource_instances wori,
951 wip_operation_resources wor
953 and woru.operation_seq_num = wori.operation_seq_num
954 and woru.resource_seq_num = wori.resource_seq_num
955 and woru.organization_id = wori.organization_id
956 and wor.wip_entity_id = p_wipEntityID
957 and wor.organization_id = p_orgID
958 and ( wor.operation_seq_num < p_maxOpSeqNum
959 and wor.operation_seq_num > p_minOpSeqNum
960 or ( p_minOpSeqNum <> p_maxOpSeqNum
961 and wor.operation_seq_num = p_minOpSeqNum
962 and nvl(wor.schedule_seq_num, wor.resource_seq_num) >= nvl(p_minSchedSeqNum, nvl(wor.schedule_seq_num, wor.resource_seq_num))
963 )
964 or ( p_minOpSeqNum <> p_maxOpSeqNum
965 and wor.operation_seq_num = p_maxOpSeqNum
966 and nvl(wor.schedule_seq_num, wor.resource_seq_num) <= nvl(p_maxSchedSeqNum, nvl(wor.schedule_seq_num, wor.resource_seq_num))
967 )
968 or ( p_minOpSeqNum = p_maxOpSeqNum
969 and wor.operation_seq_num = p_maxOpSeqNum
970 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))
971 )
972 )
973 and woru.organization_id = wor.organization_id
974 and woru.wip_entity_id = wor.wip_entity_id
975 and woru.operation_seq_num = wor.operation_seq_num
976 and woru.resource_seq_num = wor.resource_seq_num;
977
978
979 if(l_logLevel <= wip_constants.full_logging) then
980 wip_logger.log('wrote ' || SQL%ROWCOUNT || ' resource instance usages', l_retStatus);
981 end if;
982
983 if(l_logLevel <= wip_constants.full_logging) then
984 wip_logger.log('about to start op processing', l_retStatus);
985 end if;
986 --base operation dates off of op resource dates
987 select wo.operation_seq_num,
988 min(wor.start_date),
989 max(wor.completion_date)
990 bulk collect into
991 l_opTbls.opSeqNum,
992 l_opTbls.startDate,
993 l_opTbls.endDate
994 from wip_operations wo,
995 wip_operation_resources wor
996 where wo.wip_entity_id = p_wipEntityID
997 and wo.organization_id = p_orgID
998 and wo.wip_entity_id = wor.wip_entity_id (+)
999 and wo.organization_id = wor.organization_id (+)
1000 and wo.operation_seq_num = wor.operation_seq_num (+)
1001 group by wo.operation_seq_num;
1002
1003 select wo.operation_seq_num,
1004 min(wor.start_date),
1005 max(wor.completion_date)
1006 bulk collect into
1007 l_opSchYesTbls.opSeqNum,
1008 l_opSchYesTbls.startDate,
1009 l_opSchYesTbls.endDate
1010 from wip_operations wo,
1011 wip_operation_resources wor
1012 where wo.wip_entity_id = p_wipEntityID
1013 and wo.organization_id = p_orgID
1014 and wo.wip_entity_id = wor.wip_entity_id (+)
1015 and wo.organization_id = wor.organization_id (+)
1016 and wo.operation_seq_num = wor.operation_seq_num (+)
1017 and wip_constants.sched_yes = wor.scheduled_flag (+)
1018 group by wo.operation_seq_num;
1019
1020 if(l_logLevel <= wip_constants.full_logging) then
1021 wip_logger.log('about to start op processing', l_retStatus);
1022 end if;
1023
1024 for i in 1..l_opTbls.opSeqNum.count loop
1025 if(l_opTbls.startDate(i) is null) then
1026
1027 --look for previous operations with a yes resource
1028 if(i > 1) then
1029 for j in reverse 1..(i-1) loop
1030 if(l_opSchYesTbls.endDate(j) is not null) then
1031 l_opTbls.startDate(i) := l_opSchYesTbls.endDate(j);
1032 l_opTbls.endDate(i) := l_opTbls.startDate(i);
1033 exit;
1034 end if;
1035 end loop;
1036 end if;
1037
1038 --if the date is still null, look for future ops with a yes resource
1039 if(l_opTbls.startDate(i) is null) then
1040 if(i < l_opTbls.opSeqNum.count) then
1041 for j in (i+1)..l_opTbls.opSeqNum.count loop
1042 /*Fix bug 9811851(FP 9694075), when looking for future ops yes resource, we
1043 should assign the start/end date of current non-schedule resource
1044 to l_opSchYesTbls.startDate(j)*/
1045 if(l_opSchYesTbls.startDate(j) is not null) then
1046 l_opTbls.startDate(i) := l_opSchYesTbls.startDate(j);
1047 l_opTbls.endDate(i) := l_opSchYesTbls.startDate(j);
1048 exit;
1049 end if;
1050 end loop;
1051 end if;
1052 end if;
1053
1054 --if the date is still null, this means that there are no scheduled yes
1055 --resources in the job.
1056 if(l_opTbls.startDate(i) is null) then
1057 l_opTbls.startDate(i) := p_anchorDate;
1058 l_opTbls.endDate(i) := p_anchorDate;
1059 for j in 1..x_resTbls.resID.count loop
1060 if(x_resTbls.opSeqNum(j) < l_opTbls.opSeqNum(i) and
1061 x_resTbls.schedFlag(j) = wip_constants.sched_prior) then
1062 l_opTbls.startDate(i) := x_resTbls.endDate(j);
1063 l_opTbls.endDate(i) := x_resTbls.endDate(j);
1064 else
1065 exit;
1066 end if;
1067 end loop;
1068 end if;
1069
1070 end if;
1071 end loop;
1072
1073 --determine which operations to update
1077 end if;
1074 for i in 1..l_opTbls.opSeqNum.count loop
1075 if(l_opTbls.opSeqNum(i) = p_minOpSeqNum) then
1076 l_startOpIdx := i;
1078 if(l_opTbls.opSeqNum(i) = p_maxOpSeqNum) then
1079 l_endOpIdx := i;
1080 exit;
1081 end if;
1082 end loop;
1083
1084 if(l_logLevel <= wip_constants.full_logging) then
1085 wip_logger.log('about to write ops', l_retStatus);
1086 end if;
1087
1088 if(l_startOpIdx is not null) then
1089 forall i in l_startOpIdx..l_endOpIdx
1090 update wip_operations
1091 set first_unit_start_date = l_opTbls.startDate(i),
1092 last_unit_start_date = l_opTbls.startDate(i),
1093 first_unit_completion_date = l_opTbls.endDate(i),
1094 last_unit_completion_date = l_opTbls.endDate(i),
1095 last_update_date = l_sysdate,
1096 last_updated_by = l_userID,
1097 last_update_login = l_loginID,
1098 request_id = l_reqID,
1099 program_application_id = l_progApplID,
1100 program_id = l_progID,
1101 program_update_date = l_sysDate
1102 where wip_entity_id = p_wipEntityID
1103 and organization_id = p_orgID
1104 and operation_seq_num = l_opTbls.opSeqNum(i);
1105 if(l_logLevel <= wip_constants.full_logging) then
1106 wip_logger.log('wrote ops', l_retStatus);
1107 end if;
1108
1109 --update mtl requirement dates if job has routing
1110 forall i in l_startOpIdx..l_endOpIdx
1111 update wip_requirement_operations
1112 set date_required = l_opTbls.startDate(i),
1113 last_update_date = l_sysdate,
1114 last_updated_by = l_userID,
1115 last_update_login = l_loginID,
1116 request_id = l_reqID,
1117 program_application_id = l_progApplID,
1118 program_id = l_progID,
1119 program_update_date = l_sysDate
1120 where wip_entity_id = p_wipEntityID
1121 and organization_id = p_orgID
1122 and operation_seq_num = l_opTbls.opSeqNum(i);
1123 else
1124 --update mtl requirement dates if job doesn't have a routing
1125 update wip_requirement_operations
1126 set date_required = p_anchorDate,
1127 last_update_date = l_sysdate,
1128 last_updated_by = l_userID,
1129 last_update_login = l_loginID,
1130 request_id = l_reqID,
1131 program_application_id = l_progApplID,
1132 program_id = l_progID,
1133 program_update_date = l_sysDate
1134 where wip_entity_id = p_wipEntityID
1135 and organization_id = p_orgID
1136 and operation_seq_num = 1;
1137 end if;
1138
1139 if(l_logLevel <= wip_constants.full_logging) then
1140 wip_logger.log('wrote material requirements', l_retStatus);
1141 end if;
1142
1143 --now update job dates
1144 --must select from table to get resource dates b/c of partial job scheduling
1145 select min(start_date), max(completion_date)
1146 into l_minResStartDate, l_maxResEndDate
1147 from wip_operation_resources
1148 where wip_entity_id = p_wipEntityID
1149 and organization_id = p_orgID;
1150
1151 if(l_logLevel <= wip_constants.full_logging) then
1152 wip_logger.log('min res date:' || to_char(l_minResStartDate, g_logDateFmt), l_retStatus);
1153 wip_logger.log('max res date:' || to_char(l_maxResEndDate, g_logDateFmt), l_retStatus);
1154 end if;
1155 --if there are no resources, set the local variables to p_anchorDate
1156 --so below update is correct.
1157 l_jobdate_align := nvl(fnd_profile.value('WIP:ALIGN_JOB_DATE'),1);
1158 l_jobStartDate := nvl(l_minResStartDate, p_anchorDate);
1159 l_jobCplDate := nvl(l_maxResEndDate, p_anchorDate);
1160 if l_jobdate_align = WIP_CONSTANTS.NO and p_schedMethod = WIP_CONSTANTS.FORWARDS then
1161 l_jobStartDate := p_anchorDate;
1162 end if;
1163 if l_jobdate_align = WIP_CONSTANTS.NO and p_schedMethod = WIP_CONSTANTS.BACKWARDS then
1164 l_jobCplDate := p_anchorDate;
1165 end if;
1166
1167 update wip_discrete_jobs
1168 set scheduled_start_date = l_jobStartDate,
1169 scheduled_completion_date = l_jobCplDate,
1170 last_update_date = l_sysdate,
1171 last_updated_by = l_userID,
1172 last_update_login = l_loginID,
1173 request_id = l_reqID,
1174 program_application_id = l_progApplID,
1175 program_id = l_progID,
1176 program_update_date = l_sysDate
1177 where wip_entity_id = p_wipEntityID
1178 and organization_id = p_orgID;
1179
1180 if (l_logLevel <= wip_constants.trace_logging) then
1181 wip_logger.exitPoint(p_procName => 'wip_infinite_scheduler_pvt.writeJobSchedule',
1182 p_procReturnStatus => x_returnStatus,
1183 p_msg => 'success',
1184 x_returnStatus => l_retStatus);
1185 end if;
1186 exception
1187 when others then
1188 rollback to wipiscdb100;
1189 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
1190 fnd_msg_pub.add_exc_msg(p_pkg_name => 'wip_infinite_scheduler_pvt',
1191 p_procedure_name => 'writeJobSchedule',
1192 p_error_text => SQLERRM);
1193 if (l_logLevel <= wip_constants.trace_logging) then
1194 wip_logger.exitPoint(p_procName => 'wip_infinite_scheduler_pvt.writeJobSchedule',
1195 p_procReturnStatus => x_returnStatus,
1196 p_msg => 'unexp error: ' || SQLERRM,
1197 x_returnStatus => l_retStatus);
1198 end if;
1199 end writeJobSchedule;
1200
1201
1202 end wip_infinite_scheduler_pvt;