[Home] [Help]
PACKAGE BODY: APPS.WSM_INFINITE_SCHEDULER_PVT
Source
1 package body wsm_infinite_scheduler_pvt as
2 /* $Header: WSMVIFSB.pls 120.3.12010000.4 2008/09/19 09:31:37 tbhande ship $ */
3
4 --private types
5 type num_tbl_t is table of number;
6 type date_tbl_t is table of date;
7 type t_number is table of number index by binary_integer;
8
9 g_opQty t_number;
10 g_discrete_charges_exist boolean;
11 mrp_debug varchar2(1):= fnd_profile.value('mrp_debug');
12
13 g_update_current_op boolean; -- Bug 6345672
14
15 --private procedures
16
17 --reads in job ops and resources (and locks the records)
18 procedure wsmJobReader (
19 p_wipEntityID in number,
20 p_orgID in number,
21 p_scheduleMode in number := null,
22 p_opSeqNum in out nocopy number,
23 p_resSeqNum in out nocopy number,
24 p_scheQuantity in number := null,
25 p_curJobOpSeqNum in number,
26 p_curJobOpSeqId in number,
27 p_strRtgOpSeqNum in number := null,
28 p_endRtgOpSeqNum in number := null,
29 p_strRecoSeqNum in number,
30 p_endRecoSeqNum in number,
31 x_resTbls out nocopy wip_infResSched_grp.op_res_rectbl_t,
32 x_assignedUnits out nocopy num_tbl_t,
33 x_opTbl out nocopy num_tbl_t,
34 x_returnStatus out nocopy varchar2,
35 x_returnCode out nocopy number, -- ADD: BUG3195950
36 p_new_job in number
37 );
38
39 --writes out job op dates and resource dates
40 procedure wsmJobWriter (
41 p_wipEntityID in number,
42 p_orgID in number,
43 p_scheduleMode in number := null,
44 p_opSeqNum in number := null,
45 p_resSeqNum in number := null,
46 p_curJobOpSeqNum in number,
47 p_strRtgOpSeqNum in number,
48 p_endRtgOpSeqNum in number,
49 p_anchorDate in date,
50 p_opTbl in num_tbl_t,
51 p_assignedUnits in num_tbl_t,
52 x_resTbls in out nocopy wip_infressched_grp.op_res_rectbl_t,
53 x_returnStatus out nocopy varchar2,
54 x_returnCode out nocopy number -- ADD: BUG 3439417
55 );
56
57 -------------------------------------------------------------------------
58 -- This private procedure will schedule a lot based job based on
59 -- resources usages, assuming infinite resource availibility. It will
60 -- read job/schedule information out of the relevant database tables,
61 -- schedule them based on input parameters, and then update the schedule
62 -- dates in WO, WOR, WCO, WCOR
63 -- OSFM scheduler will only schedule current and future operations
64 -- All the completed operations will not be re-scheduled
65 --
66 -- Parameters:
67 -- + p_initMsgList: Clear the message stack before processing?
68 -- True (fnd_api.g_true) should be passed
69 -- unless relevant messages are being saved
70 -- on the stack. This value defaults to true.
71 --
72 -- + p_endDebug: Pass true (fnd_api.g_true) unless the
73 -- debug session will be ended at a later
74 -- point by the caller. This value defaults
75 -- to true
76 --
77 -- + p_orgID: The organization of the entity.
78 --
79 -- + p_wipEntityID: The entity to reschedule.
80 --
81 -- + p_scheduleMode: will have the following values
82 --
83 -- FORWARDS CONSTANT NUMBER := 1;
84 -- BACKWARDS CONSTANT NUMBER := 4;
85 -- MIDPOINT CONSTANT NUMBER := 6;
86 -- MIDPOINT_FORWARDS CONSTANT NUMBER := 7;
87 -- MIDPOINT_BACKWARDS CONSTANT NUMBER := 8;
88 -- CURRENT_OP CONSTANT NUMBER := 9;
89 -- CURRENT_SUB_GRP CONSTANT NUMBER := 11;
90 --
91 -- - If p_scheduleMode = WIP_CONSTANTS.CURRENT_OP,
92 -- p_opSeqNum must be given, only operation
93 -- p_opSeqNum will be scheduled
94 -- - If p_scheduleMode = WIP_CONSTANTS.MIDPOINT,
95 -- p_opSeqNum must be given, all the current and
96 -- future operations will be scheduled
97 -- - If p_scheduleMode = WIP_CONSTANTS.MIDPOINT_FORWARDS,
98 -- p_opSeqNum must be given, all operations after and
99 -- include p_opSeqNum will be scheduled
100 -- - If p_scheduleMode = WIP_CONSTANTS.MIDPOINT_BACKWARDS,
101 -- p_opSeqNum must be given, all operations before and
102 -- include p_opSeqNum will be scheduled
103 -- - If p_scheduleMode = WIP_CONSTANTS.CURRENT_SUB_GRP,
104 -- p_opSeqNum and p_resSeqNum must be given, only
105 -- resources with the same (substitute_group_number,
106 -- replacement_group_number) as this resource will be
107 -- scheduled
108 -- - If p_scheduleMode = WIP_CONSTANTS.FORWARDS, all the
109 -- current and future operations will be forward
110 -- scheduled,
111 -- p_startDate must be given
112 -- - If p_scheduleMode = WIP_CONSTANTS.BACKWARDS, all the
113 -- current and future operations will be backward
114 -- scheduled,
115 -- p_endDate must be given
116 --
117 -- + p_startDate: The start anchor date of either the operation
118 -- or resource.
119 --
120 -- + p_endDate: The end anchor date of either the operation
121 -- or resource.
122 --
123 -- + p_opSeqNum: Populate to midpoint schedule.
124 -- Should be negative if passing current job op_seq_num,
125 -- should be possitive if passing routing op_seq_num
126 --
127 -- + p_resSeqNum: Populate to midpoint schedule down to the resource
128 -- level, only used if p_opSeqNum is populated.
129 -- pass -JOB_OP_SEQ_NUM if it is the current op
130 --
131 -- + p_subGrpNum: This parameter is currently ignored
132 --
133 -- + x_returnStatus:fnd_api.g_ret_sts_success if the entity
134 -- was scheduled successfully.
135 --
136 -- + x_errorMsg: The error message. The error message will also
137 -- be left on the stack.
138 -------------------------------------------------------------------------
139
140 procedure schedule(
141 p_initMsgList in varchar2 := null,
142 p_endDebug in varchar2 := null,
143 p_orgID in number,
144 p_wipEntityID in number,
145 p_scheduleMode in number := null,
146 p_startDate in date := null,
147 p_endDate in date := null,
148 p_opSeqNum in number := null,
149 p_resSeqNum in number := null,
150 p_scheQuantity in number := null,
151 x_returnStatus out nocopy varchar2,
152 x_errorMsg out nocopy varchar2,
153 --OPTII-PER:Added the following arguments
154 p_charges_exist in number default NULL,
155 p_new_job in number default NULL) is
156
157 l_logLevel NUMBER := fnd_log.g_current_runtime_level;
158 l_params wip_logger.param_tbl_t;
159 l_retStatus VARCHAR2(1);
160 l_resTbls wip_infResSched_grp.op_res_rectbl_t;
161 l_repLineID NUMBER;
162 l_opTbl num_tbl_t := num_tbl_t();
163 l_assignedUnits num_tbl_t := num_tbl_t();
164
165
166 l_curJobOpSeqid NUMBER := null; -- current job op_seq_id
167 l_curJobOpSeqNum NUMBER := null; -- current job op_seq_num
168 l_strRtgOpSeqNum NUMBER := null; -- rtg_op_seq_num for routing start
169 l_strRtgOpSeqId NUMBER := null; -- rtg_op_seq_num for routing start
170 l_strRecoSeqNum NUMBER := null; -- reco_path_seq_num for routing start
171 l_endRtgOpSeqNum NUMBER := null; -- rtg_op_seq_num for routing end
172 l_endRtgOpSeqId NUMBER := null; -- rtg_op_seq_num for routing end
173 l_endRecoSeqNum NUMBER := null; -- reco_path_seq_num for routing end
174 l_opSeqNum NUMBER;
175 l_resSeqNum NUMBER;
176 l_retCode NUMBER := 0; -- ADD BUG3195950
177
178 l_stmt_num NUMBER;
179 e_wsm_error exception;
180 e_skip_sche exception; -- ADD BUG3195950
181
182 l_job_status NUMBER;
183 l_count NUMBER := 0;
184 l_op_seq_incr NUMBER;
185 l_copy_type NUMBER := 0;
186
187 begin
188
189 l_stmt_num := 5;
190 savepoint SP_WSMIFS_0;
191 if (l_logLevel <= wip_constants.trace_logging) then
192 l_params(1).paramName := 'p_wipEntityID';
193 l_params(1).paramValue := p_wipEntityID;
194 l_params(2).paramName := 'p_orgID';
195 l_params(2).paramValue := p_orgID;
196 l_params(3).paramName := 'p_scheduleMode';
197 l_params(3).paramValue := p_scheduleMode;
198 l_params(4).paramName := 'p_startDate';
199 l_params(4).paramValue := to_char(p_startDate, 'DD-MON-YYYY HH24:MI:SS');
200 l_params(5).paramName := 'p_endDate';
201 l_params(5).paramValue := to_char(p_endDate, 'DD-MON-YYYY HH24:MI:SS');
202 l_params(6).paramName := 'p_opSeqNum';
203 l_params(6).paramValue := p_opSeqNum;
204 l_params(7).paramName := 'p_resSeqNum';
205 l_params(7).paramValue := p_resSeqNum;
206
207 wip_logger.entryPoint(
208 p_procName => 'wsm_infinite_scheduler_pvt.schedule',
209 p_params => l_params,
210 x_returnStatus => x_returnStatus);
211 if(x_returnStatus <> fnd_api.g_ret_sts_success) then
212 raise fnd_api.g_exc_unexpected_error;
213 end if;
214 end if;
215 x_returnStatus := fnd_api.g_ret_sts_success;
216
217 if(fnd_api.to_boolean(nvl(p_initMsgList, fnd_api.g_true))) then
218 fnd_msg_pub.initialize;
219 end if;
220
221 if (l_logLevel <= wip_constants.full_logging) then
222 wip_logger.log('reading lot based job...', l_retStatus);
223 end if;
224
225 l_copy_type := WSMPUTIL.get_internal_copy_type(p_wipEntityID);
226 if(l_copy_type = 3) then
227 fnd_message.set_name('WSM', 'WSM_NO_VALID_COPY');
228 x_errorMsg := fnd_message.get;
229 raise e_wsm_error;
230 end if;
231
232 BEGIN
233 l_stmt_num := 20.1;
234 -- get the rtg op_seq_num of the routing start
235 -- we assume WSM copy tables exist
236 select wco.operation_seq_num,
237 wco.operation_sequence_id,
238 wco.reco_path_seq_num
239 into l_strRtgOpSeqNum,
240 l_strRtgOpSeqId,
241 l_strRecoSeqNum
242 from wsm_copy_operations wco
243 where wco.wip_entity_id = p_wipEntityID
244 and wco.network_start_end = 'S';
245
246 l_stmt_num := 20.2;
247 -- get the rtg op_seq_num of the routing end
248 select wco.operation_seq_num,
249 wco.operation_sequence_id,
250 wco.reco_path_seq_num
251 into l_endRtgOpSeqNum,
252 l_endRtgOpSeqId,
253 l_endRecoSeqNum
254 from wsm_copy_operations wco
255 where wco.wip_entity_id = p_wipEntityID
256 and wco.network_start_end = 'E';
257
258 -- BD: bug 3388636, should remove this check
259 -- Create job copy API will not call infinite schedule if reco_seq_num cannot be set
260 --if(l_strRecoSeqNum IS NULL or l_endRecoSeqNum IS NULL) then
261 -- x_errorMsg := 'Error: could not schedule a job when wco.reco_path_seq_num is not set';
262 -- raise e_wsm_error;
263 --end if;
264 -- ED: bug 3388636
265
266 EXCEPTION
267 when no_data_found then
268 x_errorMsg := 'Error: could not find routing start/end in the job copy.';
269 raise e_wsm_error;
270 END;
271
272 l_stmt_num := 30;
273 --get l_curJobOpSeqNum and l_curJobOpSeqid
274 begin
275 select count(operation_seq_num)
276 into l_count
277 from wip_operations
278 where wip_entity_id = p_wipEntityID;
279
280 if(l_count = 0) then
281 l_curJobOpSeqNum := null;
282 l_curJobOpSeqid := null;
283 else
284 l_stmt_num := 30.1;
285 -- get the job status
286 select status_type
287 into l_job_status
288 from wip_discrete_jobs
289 where wip_entity_id = p_wipEntityID;
290
291 if(l_job_status = WIP_CONSTANTS.UNRELEASED) then
292 l_stmt_num := 30.2;
293 -- get OP_SEQ_NUM_INCREMENT
294 select nvl(OP_SEQ_NUM_INCREMENT, 10)
295 into l_op_seq_incr
296 from wsm_parameters
297 where ORGANIZATION_ID = p_orgID;
298
299 l_curJobOpSeqNum := l_op_seq_incr;
300 l_curJobOpSeqid := l_strRtgOpSeqId;
301
302 else -- l_job_status <> WIP_CONSTANTS.UNRELEASED
303 if( p_opSeqNum < 0 and p_scheduleMode = WIP_CONSTANTS.CURRENT_OP) then
304 -- will trust parameter given by caller
305 l_stmt_num := 30.3;
306 -- get current operation in WO
307 select operation_seq_num,
308 operation_sequence_id
309 into l_curJobOpSeqNum,
310 l_curJobOpSeqid
311 from wip_operations
312 where wip_entity_id = p_wipEntityID
313 and operation_seq_num = -p_opSeqNum;
314 else
315 l_stmt_num := 30.4;
316 -- get current operation in WO
317 select operation_seq_num,
318 operation_sequence_id
319 into l_curJobOpSeqNum,
320 l_curJobOpSeqid
321 from wip_operations
322 where wip_entity_id = p_wipEntityID
323 and (quantity_in_queue <> 0 or
324 quantity_running <> 0 or
325 quantity_waiting_to_move <> 0);
326 end if;
327 end if; -- l_job_status <> WIP_CONSTANTS.UNRELEASED
328
329 --OPTII-PERF: Check if charges exists is already known
330 -- call discrete_charges_exist
331 if p_charges_exist = 1 THEN
332 g_discrete_charges_exist := true;
333 elsif p_charges_exist = 2 THEN
334 g_discrete_charges_exist := false;
335 else
336 --charges exist is NULL..
337 if(l_job_status = WIP_CONSTANTS.UNRELEASED) then
338 g_discrete_charges_exist := false;
339 elsif(l_job_status IN (WIP_CONSTANTS.COMPLETED, WIP_CONSTANTS.CLOSED)) then
340 g_discrete_charges_exist := true;
341 else
342 g_discrete_charges_exist := WSM_LBJ_INTERFACE_PVT.discrete_charges_exist(
343 p_wipEntityID,
344 p_orgID, 0);
345 end if;
346 end if;--End of check on p_charges_exist
347
348 end if; -- l_count = 0 no WO records exist
349 exception
350 when no_data_found then
351 l_curJobOpSeqNum := null;
352 l_curJobOpSeqid := null;
353 end;
354
355 -- bug 6345672: Update copy tables for current operation if the job is on the
356 -- first op queue and no charges exist.
357
358 if (l_count = 1) and (g_discrete_charges_exist = false) then
359 g_update_current_op := true;
360 if l_curJobOpSeqNum is not null then
361 declare
362 l_dummy varchar2(1);
363 begin
364 select '1'
365 into l_dummy
366 from wip_operations
367 where wip_entity_id = p_wipEntityID
368 and operation_seq_num = l_curJobOpSeqNum
369 and nvl(quantity_running, 0) = 0
370 and nvl(quantity_waiting_to_move, 0) = 0;
371
372 g_update_current_op := true;
373 exception
374 when no_data_found then
375 g_update_current_op := false;
376 end;
377 end if;
378 else
379 g_update_current_op := false;
380 end if;
381
382 if (l_logLevel <= wip_constants.full_logging) then
383 if g_update_current_op then
384 wip_logger.log('schedule: g_update_current_op = true', l_retStatus);
385 else
386 wip_logger.log('schedule: g_update_current_op = false', l_retStatus);
387 end if;
388 end if;
389 -- end bug 6345672
390
391 l_opSeqNum := p_opSeqNum;
392 l_resSeqNum := p_resSeqNum;
393
394 l_stmt_num := 40;
395 wsmJobReader (
396 p_wipEntityID => p_wipEntityID,
397 p_orgID => p_orgID,
398 p_scheduleMode => p_scheduleMode,
399 p_opSeqNum => l_opSeqNum,
400 p_resSeqNum => l_resSeqNum,
401 p_scheQuantity => p_scheQuantity,
402 p_curJobOpSeqNum => l_curJobOpSeqNum,
403 p_curJobOpSeqId => l_curJobOpSeqid,
404 p_strRtgOpSeqNum => l_strRtgOpSeqNum,
405 p_endRtgOpSeqNum => l_endRtgOpSeqNum,
406 p_strRecoSeqNum => l_strRecoSeqNum,
407 p_endRecoSeqNum => l_endRecoSeqNum,
408 x_resTbls => l_resTbls,
409 x_assignedUnits => l_assignedUnits,
410 x_opTbl => l_opTbl,
411 x_returnStatus => x_returnStatus,
412 x_returnCode => l_retCode,
413 p_new_job => p_new_job);
414 if(x_returnStatus <> fnd_api.g_ret_sts_success) then
415 raise fnd_api.g_exc_unexpected_error;
416 end if;
417 -- BA: BUG3195950
418 if(l_retCode = 1) then
419 x_errorMsg := 'Skip infinite scheduling for this job, no quantity.';
420 wip_logger.log(x_errorMsg, l_retStatus);
421 raise e_skip_sche;
422 end if;
423 -- EA: BUG3195950
424
425 if (l_logLevel <= wip_constants.full_logging) then
426 wip_logger.log('scheduling lot based job...', l_retStatus);
427 end if;
428
429 if(l_resTbls.opSeqNum.count <> 0) then
430 l_stmt_num := 50;
431 wip_infResSched_grp.schedule(
432 p_orgID => p_orgID,
433 p_repLineID => l_repLineID,
434 p_startDate => p_startDate,
435 p_endDate => p_endDate,
436 p_opSeqNum => l_opSeqNum,
437 p_resSeqNum => l_resSeqNum,
438 p_endDebug => fnd_api.g_false,
439 x_resTbls => l_resTbls,
440 x_returnStatus => x_returnStatus);
441 if(x_returnStatus <> fnd_api.g_ret_sts_success) then
442 raise fnd_api.g_exc_unexpected_error;
443 end if;
444
445 if (l_logLevel <= wip_constants.full_logging) then
446 wip_logger.log('writing lot based job...', l_retStatus);
447 end if;
448 end if;
449
450 l_stmt_num := 60;
451 l_retCode := 0;
452 wsmJobWriter (
453 p_wipEntityID => p_wipEntityID,
454 p_orgID => p_orgID,
455 p_scheduleMode => p_scheduleMode,
456 p_opSeqNum => l_opSeqNum,
457 p_resSeqNum => l_resSeqNum,
458 p_curJobOpSeqNum => l_curJobOpSeqNum,
459 p_strRtgOpSeqNum => l_strRtgOpSeqNum,
460 p_endRtgOpSeqNum => l_endRtgOpSeqNum,
461 p_anchorDate => nvl(p_startDate, p_endDate),
462 p_opTbl => l_opTbl,
463 p_assignedUnits => l_assignedUnits,
464 x_resTbls => l_resTbls,
465 x_returnStatus => x_returnStatus,
466 x_returnCode => l_retCode );
467 if(x_returnStatus <> fnd_api.g_ret_sts_success) then
468 raise fnd_api.g_exc_unexpected_error;
469 end if;
470
471 l_stmt_num := 70;
472 if (l_logLevel <= wip_constants.trace_logging) then
473 wip_logger.exitPoint(
474 p_procName => 'wsm_infinite_scheduler_pvt.schedule',
475 p_procReturnStatus => x_returnStatus,
476 p_msg => 'success',
477 x_returnStatus => l_retStatus);
478 if(fnd_api.to_boolean(nvl(p_endDebug, fnd_api.g_true))) then
479 wip_logger.cleanup(l_retStatus);
480 end if;
481 end if;
482
483 exception
484
485 when e_wsm_error then
486 rollback to SP_WSMIFS_0;
487 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
488 if(mrp_debug = 'Y') then
489 x_errorMsg := x_errorMsg || ' (# ' || l_stmt_num || ')';
490 end if;
491
492 when e_skip_sche then
493 rollback to SP_WSMIFS_0;
494 -- since this is just a warning, do not change return status
495 if(mrp_debug = 'Y') then
496 x_errorMsg := x_errorMsg || ' (# ' || l_stmt_num || ')';
497 end if;
498
499 when fnd_api.g_exc_unexpected_error then
500 rollback to SP_WSMIFS_0;
501 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
502 wip_utilities.get_message_stack(
503 p_msg => x_errorMsg,
504 p_delete_stack => fnd_api.g_false);
505 if (l_logLevel <= wip_constants.trace_logging) then
506 wip_logger.exitPoint(
507 p_procName => 'wsm_infinite_scheduler_pvt.schedule',
508 p_procReturnStatus => x_returnStatus,
509 p_msg => 'error: ' || x_errorMsg,
510 x_returnStatus => l_retStatus);
511 if(fnd_api.to_boolean(nvl(p_endDebug, fnd_api.g_true))) then
512 wip_logger.cleanup(l_retStatus);
513 end if;
514 end if;
515 if(mrp_debug = 'Y') then
516 x_errorMsg := x_errorMsg || ' (# ' || l_stmt_num || ')';
517 end if;
518
519 when others then
520 rollback to SP_WSMIFS_0;
521 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
522 fnd_msg_pub.add_exc_msg(
523 p_pkg_name => 'wsm_infinite_scheduler_pvt',
524 p_procedure_name => 'schedule',
525 p_error_text => SQLERRM);
526 wip_utilities.get_message_stack(
527 p_msg => x_errorMsg,
528 p_delete_stack => fnd_api.g_false);
529 if (l_logLevel <= wip_constants.trace_logging) then
530 wip_logger.exitPoint(
531 p_procName => 'wsm_infinite_scheduler_pvt.schedule',
532 p_procReturnStatus => x_returnStatus,
533 p_msg => 'unexp error: ' || x_errorMsg,
534 x_returnStatus => l_retStatus);
535 if(fnd_api.to_boolean(nvl(p_endDebug, fnd_api.g_true))) then
536 wip_logger.cleanup(l_retStatus);
537 end if;
538 end if;
539 if(mrp_debug = 'Y') then
540 x_errorMsg := x_errorMsg || ' (# ' || l_stmt_num || ')';
541 end if;
542
543 end schedule;
544
545
546 procedure wsmJobReader (
547 p_wipEntityID in number,
548 p_orgID in number,
549 p_scheduleMode in number := null,
550 p_opSeqNum in out nocopy number,
551 p_resSeqNum in out nocopy number,
552 p_scheQuantity in number := null,
553 p_curJobOpSeqNum in number,
554 p_curJobOpSeqId in number,
555 p_strRtgOpSeqNum in number := null,
556 p_endRtgOpSeqNum in number := null,
557 p_strRecoSeqNum in number,
558 p_endRecoSeqNum in number,
559 x_resTbls out nocopy wip_infResSched_grp.op_res_rectbl_t,
560 x_assignedUnits out nocopy num_tbl_t,
561 x_opTbl out nocopy num_tbl_t,
562 x_returnStatus out nocopy varchar2,
563 x_returnCode out nocopy number, -- ADD: BUG3195950
564 p_new_job in number
565 ) is
566
567 l_loglevel NUMBER := fnd_log.g_current_runtime_level;
568 l_params wip_logger.param_tbl_t;
569 l_retStatus VARCHAR2(1);
570 l_hrUOM VARCHAR2(3);
571 l_hrVal NUMBER;
572 l_uomClass VARCHAR2(10);
573 l_dummy NUMBER;
574 l_cnt_wor number;
575 l_cnt_wo number;
576 l_idx number;
577
578 l_cur_job_op_seq NUMBER;
579 l_the_rec_seq_num NUMBER;
580 l_fst_rec_seq_num NUMBER;
581 l_lst_rec_seq_num NUMBER;
582 l_res_seq_num NUMBER;
583 l_sub_grp_num NUMBER;
584 l_rpl_grp_num NUMBER;
585 l_levels num_tbl_t := num_tbl_t();
586
587 l_job_start_qty number;
588 l_job_scrap_qty number;
589 l_job_quantity number;
590 l_cur_op_yield number := 1;
591 l_qty_posi number; -- 1: queue/running 2: to move
592 l_rec_seq_num number;
593 l_opSeqTbl num_tbl_t := num_tbl_t();
594 l_opYieldTbl num_tbl_t := num_tbl_t();
595 l_baseTypes num_tbl_t := num_tbl_t();
596 l_scheFlagOrder num_tbl_t := num_tbl_t();
597
598 l_stat_num number;
599 e_skip_sche exception; -- BUG3195950
600
601 --the following cursors simply lock the records wsmJobWriter() will later modify
602 cursor c_WO is
603 select 1
604 from wip_operations
605 where wip_entity_id = p_wipEntityID
606 and organization_id = p_orgID
607 for update nowait;
608
609 cursor c_WRO is
610 select 1
611 from wip_requirement_operations
612 where wip_entity_id = p_wipEntityID
613 and organization_id = p_orgID
614 for update nowait;
615
616 cursor c_WOR is
617 select 1
618 from wip_operation_resources
619 where wip_entity_id = p_wipEntityID
620 and organization_id = p_orgID
621 for update nowait;
622
623 cursor c_WORU is
624 select 1
625 from wip_operation_resource_usage
626 where wip_entity_id = p_wipEntityID
627 and organization_id = p_orgID
628 for update nowait;
629
630 cursor c_WORI is
631 select 1
632 from wip_op_resource_instances
633 where wip_entity_id = p_wipEntityID
634 and organization_id = p_orgID
635 for update nowait;
636
637 cursor c_WCO is
638 select 1
639 from wsm_copy_operations
640 where wip_entity_id = p_wipEntityID
641 and organization_id = p_orgID
642 for update nowait;
643
644 cursor c_WCOR is
645 select 1
646 from wsm_copy_op_resources
647 where wip_entity_id = p_wipEntityID
648 and organization_id = p_orgID
649 for update nowait;
650
651 cursor c_WCRO is
652 select 1
653 from wsm_copy_requirement_ops
654 where wip_entity_id = p_wipEntityID
655 and organization_id = p_orgID
656 for update nowait;
657
658 cursor c_WCORU is
659 select 1
660 from wsm_copy_op_resource_usage
661 where wip_entity_id = p_wipEntityID
662 and organization_id = p_orgID
663 for update nowait;
664
665 cursor c_WCORI is
666 select 1
667 from wsm_copy_op_resource_instances
668 where wip_entity_id = p_wipEntityID
669 and organization_id = p_orgID
670 for update nowait;
671
672 -- cursor to fetch all the resources
673 cursor c_op_resources (c_cur_job_op_seq number,
674 c_fst_rec_seq_seq number,
675 c_lst_rec_seq_seq number,
676 c_res_seq_num number,
677 c_sub_grp_num number,
678 c_rpl_grp_num number) IS
679 -- current job op --
680 select 0 lvl,
681 -c_cur_job_op_seq,
682 wor.resource_id,
683 NVL(wor.department_id, wo.department_id),
684 wor.resource_seq_num RES_SEQ_NUM,
685 wor.schedule_seq_num RES_SCH_NUM,
686 wor.scheduled_flag,
687 decode(wor.scheduled_flag, 3, 1, 1, 2, 4, 3, null)
688 SCHE_FLAG_ORDER,
689 bdr.available_24_hours_flag,
690 --Bug 4554494
691 --l_hrVal * nvl(muc.conversion_rate,0)
692 nvl(muc.conversion_rate,0)
693 * decode(wor.basis_type, wip_constants.per_lot, 1, wdj.start_quantity)
694 * wor.usage_rate_or_amount
695 / ( 24 * nvl(l_hrVal,1)*least(wor.assigned_units, bdr.capacity_units)
696 * nvl(bdr.utilization, 1) * nvl(bdr.efficiency, 1) ),
697 wor.basis_type,
698 wor.assigned_units
699 from wip_discrete_jobs wdj,
700 wip_operations wo,
701 wip_operation_resources wor,
702 mtl_uom_conversions muc,
703 bom_department_resources bdr
704 where wdj.wip_entity_id = p_wipEntityID
705 and wdj.organization_id = p_orgID
706 and wdj.wip_entity_id = wo.wip_entity_id
707 and wdj.organization_id = wo.organization_id
708 and wo.wip_entity_id = wor.wip_entity_id
709 and wo.organization_id = wor.organization_id
710 and wo.operation_seq_num = wor.operation_seq_num
711 and wor.resource_seq_num
712 = decode(c_res_seq_num,
713 null, wor.resource_seq_num,
714 decode(c_sub_grp_num,
715 null, c_res_seq_num,
716 wor.resource_seq_num))
717 and nvl(wor.substitute_group_num, 0)
718 = decode(c_res_seq_num,
719 null, nvl(wor.substitute_group_num, 0),
720 decode(c_sub_grp_num,
721 null, nvl(wor.substitute_group_num, 0),
722 c_sub_grp_num))
723 and nvl(wor.replacement_group_num, 0)
724 = decode(c_res_seq_num,
725 null, nvl(wor.replacement_group_num, 0),
726 decode(c_sub_grp_num,
727 null, nvl(wor.replacement_group_num, 0),
728 c_rpl_grp_num))
729 and wo.operation_seq_num = c_cur_job_op_seq
730 and bdr.resource_id = wor.resource_id
731 and bdr.department_id = nvl(wor.department_id, wo.department_id)
732 and wor.uom_code = muc.uom_code (+)
733 and muc.uom_class (+)= l_uomClass
734 and muc.inventory_item_id (+)= 0
735 and c_cur_job_op_seq IS NOT NULL
736 union
737 -- future rtg ops --
738 select wco.reco_path_seq_num lvl,
739 wcor.operation_seq_num,
740 wcor.resource_id,
741 NVL(wcor.department_id, wco.department_id),
742 wcor.resource_seq_num RES_SEQ_NUM,
743 wcor.schedule_seq_num RES_SCH_NUM,
744 wcor.schedule_flag,
745 decode(wcor.schedule_flag, 3, 1, 1, 2, 4, 3, null)
746 SCHE_FLAG_ORDER,
747 bdr.available_24_hours_flag,
748 --Bug 4554494
749 --l_hrVal * nvl(muc.conversion_rate,0)
750 nvl(muc.conversion_rate,0)
751 * decode(wcor.basis_type, wip_constants.per_lot, 1, wdj.start_quantity)
752 * wcor.usage_rate_or_amount
753 / ( 24 * nvl(l_hrVal,1)* least(wcor.assigned_units, bdr.capacity_units)
754 * nvl(bdr.utilization, 1) * nvl(bdr.efficiency, 1) ),
755 wcor.basis_type,
756 wcor.assigned_units
757 from wip_discrete_jobs wdj,
758 wsm_copy_operations wco,
759 wsm_copy_op_resources wcor,
760 mtl_uom_conversions muc,
761 bom_department_resources bdr
762 where wdj.wip_entity_id = p_wipEntityID
763 and wdj.organization_id = p_orgID
764 and wdj.wip_entity_id = wco.wip_entity_id
765 and wdj.organization_id = wco.organization_id
766 and wco.wip_entity_id = wcor.wip_entity_id
767 and wco.organization_id = wcor.organization_id
768 and wco.operation_seq_num = wcor.operation_seq_num
769 and (wco.reco_path_seq_num between c_fst_rec_seq_seq and c_lst_rec_seq_seq)
770 and wcor.recommended = 'Y'
771 and wcor.resource_seq_num
772 = decode(c_res_seq_num,
773 null, wcor.resource_seq_num,
774 decode(c_sub_grp_num,
775 null, c_res_seq_num,
776 wcor.resource_seq_num))
777 and nvl(wcor.substitute_group_num, 0)
778 = decode(c_res_seq_num,
779 null, nvl(wcor.substitute_group_num, 0),
780 decode(c_sub_grp_num,
781 null, nvl(wcor.substitute_group_num, 0),
782 c_sub_grp_num))
783 and nvl(wcor.replacement_group_num, 0)
784 = decode(c_res_seq_num,
785 null, nvl(wcor.replacement_group_num, 0),
786 decode(c_sub_grp_num,
787 null, nvl(wcor.replacement_group_num, 0),
788 c_rpl_grp_num))
789 and bdr.resource_id = wcor.resource_id
790 and bdr.department_id = nvl(wcor.department_id, wco.department_id)
791 and wcor.uom_code = muc.uom_code (+)
792 and muc.uom_class (+)= l_uomClass
793 and muc.inventory_item_id (+)= 0
794 order by lvl, SCHE_FLAG_ORDER, RES_SEQ_NUM, RES_SCH_NUM;
795
796 -- cursor to fetch the operations
797 cursor c_operations (c_cur_job_op_seq number,
798 c_fst_rec_seq_seq number,
799 c_lst_rec_seq_seq number) is
800 -- current job op --
801 select 0 lvl,
802 -c_cur_job_op_seq OP_SEQ_NUM
803 from dual
804 where c_cur_job_op_seq IS NOT NULL
805 union
806 -- other rtg ops --
807 select wco.reco_path_seq_num lvl,
808 operation_seq_num OP_SEQ_NUM
809 from wsm_copy_operations wco
810 where wco.wip_entity_id = p_wipEntityID
811 and (wco.reco_path_seq_num between c_fst_rec_seq_seq and c_lst_rec_seq_seq)
812 order by lvl;
813
814 -- cursor to fetch all future operations
815 cursor c_future_op_yield (
816 c_fst_rec_seq_seq number,
817 c_lst_rec_seq_seq number) is
818 select operation_seq_num,
819 NVL(yield, 1.0)
820 from wsm_copy_operations wco
821 where wco.wip_entity_id = p_wipEntityID
822 and (wco.reco_path_seq_num between c_fst_rec_seq_seq and c_lst_rec_seq_seq)
823 order by wco.reco_path_seq_num;
824
825 BEGIN
826
827 l_stat_num := 10;
828 if (l_logLevel <= wip_constants.trace_logging) then
829 l_params(1).paramName := 'p_wipEntityID';
830 l_params(1).paramValue := p_wipEntityID;
831 l_params(2).paramName := 'p_orgID';
832 l_params(2).paramValue := p_orgID;
833 l_params(3).paramName := 'p_scheduleMode';
834 l_params(3).paramValue := p_scheduleMode;
835 l_params(4).paramName := 'p_curJobOpSeqNum';
836 l_params(4).paramValue := p_curJobOpSeqNum;
837 l_params(5).paramName := 'p_curJobOpSeqId';
838 l_params(5).paramValue := p_curJobOpSeqId;
839 l_params(6).paramName := 'p_strRtgOpSeqNum';
840 l_params(6).paramValue := p_strRtgOpSeqNum;
841 l_params(7).paramName := 'p_endRtgOpSeqNum';
842 l_params(7).paramValue := p_endRtgOpSeqNum;
843 l_params(8).paramName := 'p_strRecoSeqNum';
844 l_params(8).paramValue := p_strRecoSeqNum;
845 l_params(9).paramName := 'p_endRecoSeqNum';
846 l_params(9).paramValue := p_endRecoSeqNum;
847
848 wip_logger.entryPoint(
849 p_procName => 'wsm_infinite_scheduler_pvt.wsmJobReader',
850 p_params => l_params,
851 x_returnStatus => l_retStatus);
852 end if;
853 x_returnStatus := fnd_api.g_ret_sts_success;
854 x_returnCode := 0; -- ADD: BUG3195950
855
856 l_stat_num := 20;
857 l_hrUOM := fnd_profile.value('BOM:HOUR_UOM_CODE');
858 select conversion_rate, uom_class
859 into l_hrVal, l_uomClass
860 from mtl_uom_conversions
861 where uom_code = l_hrUOM
862 and nvl(disable_date, sysdate + 1) > sysdate
863 and inventory_item_id = 0;
864
865
866 ----------------------------------------------------------
867 -- set the default value for
868 -- l_cur_job_op_seq, l_fst_rec_seq_num, l_lst_rec_seq_num,
869 -- l_res_seq_num, l_sub_grp_num, l_rpl_grp_num
870 ----------------------------------------------------------
871 l_cur_job_op_seq := p_curJobOpSeqNum;
872 l_lst_rec_seq_num := p_endRecoSeqNum;
873 l_res_seq_num := null;
874 l_sub_grp_num := null;
875 l_rpl_grp_num := null;
876
877 l_stat_num := 30;
878 if(l_cur_job_op_seq IS NULL) then
879 -- get the reco_path_seq_num for routing start, this should happen during
880 -- job creation, usually l_fst_rec_seq_num should not be null
881 l_fst_rec_seq_num := p_strRecoSeqNum;
882 else
883 if(p_curJobOpSeqId IS NULL) then
884 -- currently in a operation outside routing
885 l_fst_rec_seq_num := null;
886 else
887 l_stat_num := 30.1;
888 BEGIN
889 select wco.reco_path_seq_num
890 into l_fst_rec_seq_num
891 from WSM_COPY_OPERATIONS wco
892 where wco.wip_entity_id = p_wipEntityID
893 and wco.operation_sequence_id = p_curJobOpSeqId;
894 EXCEPTION
895 when no_data_found then
896 l_fst_rec_seq_num := null;
897 END;
898 end if;
899
900 if l_fst_rec_seq_num IS NOT NULL then
901 -- pointing to the next op of the current operation
902 l_fst_rec_seq_num := l_fst_rec_seq_num +1;
903 if (l_fst_rec_seq_num > l_lst_rec_seq_num) then
904 -- current op is the rtg end, no future operation exists !!
905 l_fst_rec_seq_num := null;
906 l_lst_rec_seq_num := null;
907 end if;
908 end if;
909 end if;
910
911 -- Note: l_fst_rec_seq_num IS NULL means future op unknown !!
912 -- only current operation will be scheduled
913 if(l_fst_rec_seq_num IS NULL) then
914 l_lst_rec_seq_num := null;
915 end if;
916
917 l_stat_num := 40;
918 -- get reco_path_seq_num for p_opSeqNum
919 if(p_opSeqNum IS NOT NULL and p_opSeqNum > 0) then
920 BEGIN
921 select wco.reco_path_seq_num
922 into l_the_rec_seq_num
923 from WSM_COPY_OPERATIONS wco
924 where wco.wip_entity_id = p_wipEntityID
925 and wco.operation_seq_num = p_opSeqNum;
926 EXCEPTION
927 when no_data_found then
928 l_the_rec_seq_num := null;
929 END;
930 end if;
931
932 l_stat_num := 50;
933 -- get job start quantity
934 select wdj.start_quantity,
935 wdj.quantity_scrapped
936 into l_job_start_qty,
937 l_job_scrap_qty
938 from wip_discrete_jobs wdj
939 where wdj.wip_entity_id = p_wipEntityID;
940 -- BA: BUG3195950
941 if(l_job_start_qty = 0 or
942 l_job_start_qty - l_job_scrap_qty = 0) then
943 raise e_skip_sche;
944 end if;
945 -- EA: BUG3195950
946
947 l_stat_num := 60;
948 g_opQty.delete;
949 -- get scheduled quantity for all the future operations
950 if(l_cur_job_op_seq IS NOT NULL) then
951 -- WO records exist
952 -- get current job quantity
953 select wo.quantity_in_queue + wo.quantity_running
954 + wo.quantity_waiting_to_move,
955 nvl(wo.operation_yield, 1),
956 decode(wo.quantity_waiting_to_move,
957 0, 1, 2)
958 into l_job_quantity,
959 l_cur_op_yield,
960 l_qty_posi
961 from wip_operations wo
962 where wo.wip_entity_id = p_wipEntityID
963 and wo.operation_seq_num = l_cur_job_op_seq;
964
965 if(l_job_quantity = 0) then
966 -- This can happen for an unreleased job or during move to an un-scheduled op
967 if(p_scheQuantity IS NULL) then
968 l_job_quantity := l_job_start_qty - l_job_scrap_qty;
969 else
970 l_job_quantity := p_scheQuantity;
971 end if;
972 l_qty_posi := 1;
973 end if;
974
975 g_opQty(-l_cur_job_op_seq) := l_job_quantity;
976
977 -- get operation yield
978 if(l_fst_rec_seq_num IS NOT NULL) then
979 l_stat_num := 60.1;
980 open c_future_op_yield (
981 l_fst_rec_seq_num,
982 l_lst_rec_seq_num);
983 fetch c_future_op_yield bulk collect into
984 l_opSeqTbl,
985 l_opYieldTbl;
986 close c_future_op_yield;
987
988 -- caculate operation quantity based on yield
989 if(l_opSeqTbl.exists(1)) then
990 if(l_qty_posi = 1) then
991 g_opQty(l_opSeqTbl(1)) := l_job_quantity * l_cur_op_yield;
992 else
993 g_opQty(l_opSeqTbl(1)) := l_job_quantity;
994 end if;
995
996 for i in 2..l_opSeqTbl.last
997 loop
998 g_opQty(l_opSeqTbl(i)) := g_opQty(l_opSeqTbl(i-1)) * l_opYieldTbl(i-1);
999 end loop;
1000 end if;
1001 end if;
1002 else
1003 l_stat_num := 60.2;
1004 -- get current job quantity
1005 l_job_quantity := l_job_start_qty;
1006 l_qty_posi := 1;
1007
1008 -- get operation yield
1009 open c_future_op_yield (
1010 p_strRecoSeqNum,
1011 p_endRecoSeqNum);
1012 fetch c_future_op_yield bulk collect into
1013 l_opSeqTbl,
1014 l_opYieldTbl;
1015 close c_future_op_yield;
1016
1017 -- caculate operation quantity based on yield
1018 g_opQty(l_opSeqTbl(1)) := l_job_quantity;
1019 for i in 2..l_opSeqTbl.last
1020 loop
1021 g_opQty(l_opSeqTbl(i)) := g_opQty(l_opSeqTbl(i-1)) * l_opYieldTbl(i-1);
1022 end loop;
1023 end if;
1024
1025 l_stat_num := 70;
1026 -- set the parameters to open the cursor
1027 if(p_scheduleMode = WIP_CONSTANTS.FORWARDS) then
1028 --if(l_cur_job_op_seq IS NOT NULL) then
1029 -- p_opSeqNum := -l_cur_job_op_seq;
1030 --else
1031 -- p_opSeqNum := p_strRtgOpSeqNum;
1032 --end if;
1033 p_opSeqNum := null;
1034 p_resSeqNum := null;
1035 elsif(p_scheduleMode = WIP_CONSTANTS.BACKWARDS) then
1036 --p_opSeqNum := p_endRtgOpSeqNum;
1037 p_opSeqNum := null;
1038 p_resSeqNum := null;
1039 elsif(p_scheduleMode = WIP_CONSTANTS.MIDPOINT_FORWARDS) then
1040 if( p_opSeqNum > 0) then
1041 -- should always ignore current op
1042 l_cur_job_op_seq := null;
1043 l_fst_rec_seq_num := l_the_rec_seq_num;
1044 end if;
1045 elsif(p_scheduleMode = WIP_CONSTANTS.MIDPOINT_BACKWARDS) then
1046 if(p_opSeqNum > 0) then
1047 -- should fetch up to p_opSeqNum
1048 l_lst_rec_seq_num := l_the_rec_seq_num;
1049 else
1050 l_fst_rec_seq_num := null;
1051 end if;
1052 elsif(p_scheduleMode in (WIP_CONSTANTS.CURRENT_OP,
1053 WIP_CONSTANTS.CURRENT_SUB_GRP)) then
1054 if(p_opSeqNum > 0) then
1055 -- should always ignore current op, fetch p_opSeqNum only
1056 l_cur_job_op_seq := null;
1057 l_fst_rec_seq_num := l_the_rec_seq_num;
1058 l_lst_rec_seq_num := l_the_rec_seq_num;
1059 else
1060 l_fst_rec_seq_num := null;
1061 end if;
1062
1063 if(p_scheduleMode = WIP_CONSTANTS.CURRENT_SUB_GRP) then
1064 l_res_seq_num := p_resSeqNum;
1065 if(p_opSeqNum > 0) then
1066 l_stat_num := 70.1;
1067 select substitute_group_num,
1068 replacement_group_num
1069 into l_sub_grp_num,
1070 l_rpl_grp_num
1071 from WSM_COPY_OP_RESOURCES
1072 where wip_entity_id = p_wipEntityID
1073 and operation_seq_num = p_opSeqNum
1074 and resource_seq_num = p_resSeqNum;
1075 else
1076 l_stat_num := 70.2;
1077 select substitute_group_num,
1078 replacement_group_num
1079 into l_sub_grp_num,
1080 l_rpl_grp_num
1081 from WIP_OPERATION_RESOURCES
1082 where wip_entity_id = p_wipEntityID
1083 and operation_seq_num = -p_opSeqNum
1084 and resource_seq_num = p_resSeqNum;
1085 end if;
1086 end if;
1087 end if;
1088
1089 l_stat_num := 80;
1090 -- bulk fetch all the resources
1091 open c_op_resources (
1092 l_cur_job_op_seq,
1093 l_fst_rec_seq_num,
1094 l_lst_rec_seq_num,
1095 l_res_seq_num,
1096 l_sub_grp_num,
1097 l_rpl_grp_num);
1098 fetch c_op_resources bulk collect into
1099 l_levels,
1100 x_resTbls.opSeqNum,
1101 x_resTbls.resID,
1102 x_resTbls.deptID,
1103 x_resTbls.resSeqNum,
1104 x_resTbls.schedSeqNum,
1105 x_resTbls.schedFlag,
1106 l_scheFlagOrder,
1107 x_resTbls.avail24Flag,
1108 x_resTbls.totalDaysUsg,
1109 l_baseTypes,
1110 x_assignedUnits;
1111 close c_op_resources;
1112
1113 if(x_resTbls.opSeqNum.count <> 0) then
1114 -- update totalDaysUsg based on scheduled quantity
1115 l_idx := x_resTbls.opSeqNum.first;
1116 while (l_idx IS NOT NULL)
1117 loop
1118 if(l_baseTypes(l_idx) <> wip_constants.per_lot) then
1119 x_resTbls.totalDaysUsg(l_idx) :=
1120 x_resTbls.totalDaysUsg(l_idx)
1121 * g_opQty(x_resTbls.opSeqNum(l_idx))/l_job_start_qty;
1122 end if;
1123 l_idx := x_resTbls.opSeqNum.next(l_idx);
1124 end loop;
1125 end if;
1126
1127 l_stat_num := 90;
1128 -- Note: not all operations have resources. Thus we have to select the ops from the db
1129 open c_operations (
1130 l_cur_job_op_seq,
1131 l_fst_rec_seq_num,
1132 l_lst_rec_seq_num);
1133 fetch c_operations bulk collect into
1134 l_levels,
1135 x_opTbl;
1136 close c_operations;
1137
1138 l_stat_num := 100;
1139 --lock the job
1140 IF p_new_job <> 1 THEN
1141 select 1
1142 into l_dummy
1143 from wip_discrete_jobs
1144 where wip_entity_id = p_wipEntityID
1145 and organization_id = p_orgID
1146 for update nowait;
1147 END IF;
1148
1149 l_stat_num := 110;
1150 --lock WO/WCO, WRO/WCRO
1151 l_cnt_wo := 0;
1152 l_idx := x_opTbl.first;
1153 while(l_idx IS NOT NULL and x_opTbl(l_idx) < 0)
1154 loop
1155 l_cnt_wo := l_cnt_wo +1;
1156 l_idx := x_opTbl.next(l_idx);
1157 end loop;
1158
1159 l_stat_num := 120;
1160 --if(l_cnt_wo>=1) then
1161 if(l_cnt_wo>=1) AND (p_new_job <> 1) then
1162 open c_WO;
1163 close c_WO;
1164 open c_WRO;
1165 close c_WRO;
1166 end if;
1167 --if(x_opTbl.count>l_cnt_wo) then
1168 if(x_opTbl.count>l_cnt_wo) AND (p_new_job <> 1) then
1169 open c_WCO;
1170 close c_WCO;
1171 open c_WCRO;
1172 close c_WCRO;
1173 end if;
1174
1175 l_stat_num := 130;
1176 if(x_resTbls.opSeqNum.count <> 0) then
1177 --lock WOR/WCOR, WORU/WCORU, WORI/WCORI
1178 l_cnt_wor := 0;
1179 l_idx := x_resTbls.opSeqNum.first;
1180 while(l_idx IS NOT NULL and x_resTbls.opSeqNum(l_idx) < 0)
1181 loop
1182 l_cnt_wor := l_cnt_wor +1;
1183 l_idx := x_resTbls.opSeqNum.next(l_idx);
1184 end loop;
1185
1186 if(l_cnt_wor>=1) AND (p_new_job <> 1) then
1187 open c_WOR;
1188 close c_WOR;
1189 open c_WORU;
1190 close c_WORU;
1191 open c_WORI;
1192 close c_WORI;
1193 end if;
1194 if(x_resTbls.opSeqNum.count>l_cnt_wor) AND (p_new_job <> 1) then
1195 open c_WCOR;
1196 close c_WCOR;
1197 open c_WCORU;
1198 close c_WCORU;
1199 open c_WCORI;
1200 close c_WCORI;
1201 end if;
1202 end if;
1203
1204 if (l_logLevel <= wip_constants.trace_logging) then
1205 wip_logger.exitPoint(p_procName => 'wsm_infinite_scheduler_pvt.wsmJobReader',
1206 p_procReturnStatus => x_returnStatus,
1207 p_msg => 'success',
1208 x_returnStatus => l_retStatus);
1209 end if;
1210
1211 EXCEPTION
1212 -- BA: BUG3195950
1213 when e_skip_sche then
1214 if (l_logLevel <= wip_constants.trace_logging) then
1215 wip_logger.exitPoint(p_procName => 'wsm_infinite_scheduler_pvt.wsmJobReader',
1216 p_procReturnStatus => x_returnStatus,
1217 p_msg => 'success',
1218 x_returnStatus => l_retStatus);
1219 end if;
1220 x_returnCode := 1;
1221 -- EA: BUG3195950
1222 when others then
1223 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
1224 fnd_msg_pub.add_exc_msg(
1225 p_pkg_name => 'wsm_infinite_scheduler_pvt',
1226 p_procedure_name => 'wsmJobReader',
1227 p_error_text => SQLERRM || ' (reader #' || l_stat_num || ')' );
1228 if (l_logLevel <= wip_constants.trace_logging) then
1229 wip_logger.exitPoint(
1230 p_procName => 'wsm_infinite_scheduler_pvt.wsmJobReader',
1231 p_procReturnStatus => x_returnStatus,
1232 p_msg => 'unexp error: ' || SQLERRM || ' (reader #' || l_stat_num || ')',
1233 x_returnStatus => l_retStatus);
1234 end if;
1235 END wsmJobReader;
1236
1237
1238 procedure wsmJobWriter(
1239 p_wipEntityID in NUMBER,
1240 p_orgID in NUMBER,
1241 p_scheduleMode in number := null,
1242 p_opSeqNum in number := null,
1243 p_resSeqNum in number := null,
1244 p_curJobOpSeqNum in number,
1245 p_strRtgOpSeqNum in number,
1246 p_endRtgOpSeqNum in number,
1247 p_anchorDate in date,
1248 p_opTbl in num_tbl_t,
1249 p_assignedUnits in num_tbl_t,
1250 x_resTbls in out nocopy wip_infResSched_grp.op_res_rectbl_t,
1251 x_returnStatus out nocopy varchar2,
1252 x_returnCode out nocopy number -- ADD: BUG 3439417
1253 ) is
1254
1255 type t_date is table of date index by binary_integer;
1256
1257 l_hashOpSDate t_date;
1258 l_hashOpEDate t_date;
1259 l_hashOpSDateYes t_date;
1260 l_hashOpEDateYes t_date;
1261
1262 l_OpStartDate date_tbl_t := date_tbl_t();
1263 l_OpEndDate date_tbl_t := date_tbl_t();
1264 l_OpQty num_tbl_t := num_tbl_t();
1265
1266 l_jobStartDate date := null;
1267 l_jobComplDate date := null;
1268 l_newJobStartDate date := null; -- ADD: bug 3439417
1269 l_newJobComplDate date := null; -- ADD: bug 3439417
1270 l_minDate date;
1271 l_maxDate date;
1272 l_minDateYes date;
1273 l_currentDate date;
1274
1275 l_op_seq_incr number;
1276 l_curOp number;
1277 l_retStatus VARCHAR2(1);
1278 l_logLevel NUMBER := fnd_log.g_current_runtime_level;
1279 l_params wip_logger.param_tbl_t;
1280
1281 --standard who columns
1282 l_sysDate DATE := sysdate;
1283 l_userID NUMBER := fnd_global.user_id;
1284 l_loginID NUMBER := fnd_global.login_id;
1285 l_reqID NUMBER := fnd_global.conc_request_id;
1286 l_progApplID NUMBER := fnd_global.prog_appl_id;
1287 l_progID NUMBER := fnd_global.conc_program_id;
1288
1289 l_cnt_wor number;
1290 l_cnt_wo number;
1291 l_idx number;
1292 l_stat_num number;
1293
1294 e_bad_date exception;
1295
1296 begin
1297
1298 x_returnCode := 0;
1299
1300 l_stat_num := 10;
1301 if (l_logLevel <= wip_constants.trace_logging) then
1302 l_params(1).paramName := 'p_wipEntityID';
1303 l_params(1).paramValue := p_wipEntityID;
1304 l_params(2).paramName := 'p_orgID';
1305 l_params(2).paramValue := p_orgID;
1306 l_params(3).paramName := 'p_scheduleMode';
1307 l_params(3).paramValue := p_scheduleMode;
1308 l_params(4).paramName := 'p_anchorDate';
1309 l_params(4).paramValue := to_char(p_anchorDate, 'DD-MON-YYYY HH24:MI:SS');
1310 l_params(5).paramName := 'p_curJobOpSeqNum';
1311 l_params(5).paramValue := p_curJobOpSeqNum;
1312 l_params(6).paramName := 'p_strRtgOpSeqNum';
1313 l_params(6).paramValue := p_strRtgOpSeqNum;
1314 l_params(7).paramName := 'p_endRtgOpSeqNum';
1315 l_params(7).paramValue := p_endRtgOpSeqNum;
1316 l_params(8).paramName := 'p_opSeqNum';
1317 l_params(8).paramValue := p_opSeqNum;
1318 l_params(9).paramName := 'p_resSeqNum';
1319 l_params(9).paramValue := p_resSeqNum;
1320
1321
1322 wip_logger.entryPoint(
1323 p_procName => 'wsm_infinite_scheduler_pvt.wsmJobWriter',
1324 p_params => l_params,
1325 x_returnStatus => l_retStatus);
1326 end if;
1327 x_returnStatus := fnd_api.g_ret_sts_success;
1328
1329 l_stat_num := 20;
1330 -- get the current job start/end date
1331 select wdj.scheduled_start_date,
1332 wdj.scheduled_completion_date
1333 into l_jobStartDate,
1334 l_jobComplDate
1335 from wip_discrete_jobs wdj
1336 where wdj.wip_entity_id = p_wipEntityID;
1337
1338
1339 if(x_resTbls.opSeqNum.count <> 0) then
1340 l_stat_num := 30;
1341
1342 if p_scheduleMode = WIP_CONSTANTS.CURRENT_SUB_GRP then
1343
1344 l_OpStartDate.extend(1);
1345 l_OpEndDate.extend(1);
1346 l_OpQty.extend(1); -- bug 3585783 must initialize this
1347
1348 l_curOp := x_resTbls.opSeqNum(1);
1349
1350 if(l_curOp<0) then
1351 select first_unit_start_date,
1352 last_unit_completion_date
1353 into l_OpStartDate(1),
1354 l_OpEndDate(1)
1355 from wip_operations
1356 where wip_entity_id = p_wipEntityID
1357 and organization_id = p_orgID
1358 and operation_seq_num = -l_curOp;
1359 else
1360 select reco_start_date,
1361 reco_completion_date
1362 into l_OpStartDate(1),
1363 l_OpEndDate(1)
1364 from wsm_copy_operations
1365 where wip_entity_id = p_wipEntityID
1366 and organization_id = p_orgID
1367 and operation_seq_num = l_curOp;
1368 end if;
1369
1370 for i in 1..x_resTbls.resID.count loop
1371 l_OpStartDate(1) := least(nvl(l_OpStartDate(1), x_resTbls.startDate(i)),
1372 x_resTbls.startDate(i));
1373 l_OpEndDate(1) := greatest(nvl(l_OpEndDate(1), x_resTbls.endDate(i)),
1374 x_resTbls.endDate(i));
1375 end loop;
1376 l_OpQty(1):= g_opQty(l_curOp); -- bug 3585783
1377 l_minDate := l_OpStartDate(1);
1378 l_maxDate := l_OpEndDate(1);
1379
1380
1381 else -- p_scheduleMode <> WIP_CONSTANTS.CURRENT_SUB_GRP
1382
1383 l_minDate := null;
1384 l_maxDate := null;
1385 l_minDateYes := null;
1386
1387 for i in 1..x_resTbls.resID.count loop
1388 l_curOp := x_resTbls.opSeqNum(i);
1389 if(NOT l_hashOpSDate.exists(l_curOp)) then
1390 l_hashOpSDate(l_curOp) := null;
1391 l_hashOpEDate(l_curOp) := null;
1392 l_hashOpSDateYes(l_curOp) := null;
1393 l_hashOpEDateYes(l_curOp) := null;
1394 end if;
1395
1396 if(x_resTbls.startDate(i) IS NOT NULL) then
1397 l_hashOpSDate(l_curOp)
1398 := least(nvl(l_hashOpSDate(l_curOp), x_resTbls.startDate(i)),
1399 x_resTbls.startDate(i));
1400 l_minDate
1401 := least(nvl(l_minDate, x_resTbls.startDate(i)),
1402 x_resTbls.startDate(i));
1403 if(x_resTbls.schedFlag(i) = 1) then
1404 l_hashOpSDateYes(l_curOp)
1405 := least(nvl(l_hashOpSDateYes(l_curOp), x_resTbls.startDate(i)),
1406 x_resTbls.startDate(i));
1407 l_minDateYes
1408 := least(nvl(l_minDateYes, x_resTbls.startDate(i)),
1409 x_resTbls.startDate(i));
1410 end if;
1411 end if;
1412 if(x_resTbls.endDate(i) IS NOT NULL) then
1413 l_hashOpEDate(l_curOp)
1414 := greatest(nvl(l_hashOpEDate(l_curOp), x_resTbls.endDate(i)),
1415 x_resTbls.endDate(i));
1416 l_maxDate
1417 := greatest(nvl(l_maxDate, x_resTbls.endDate(i)),
1418 x_resTbls.endDate(i));
1419 if(x_resTbls.schedFlag(i) = 1) then
1420 l_hashOpEDateYes(l_curOp)
1421 := greatest(nvl(l_hashOpEDateYes(l_curOp), x_resTbls.endDate(i)),
1422 x_resTbls.endDate(i));
1423 end if;
1424 end if;
1425 end loop;
1426
1427 -- set startDate/endDate for resources with schedFlag = No
1428 l_currentDate := l_minDateYes;
1429 for i in 1..x_resTbls.resID.count loop
1430 l_curOp := x_resTbls.opSeqNum(i);
1431 l_currentDate := NVL(l_hashOpEDateYes(l_curOp), l_currentDate);
1432
1433 if(x_resTbls.startDate(i) IS NULL) then
1434 x_resTbls.startDate(i) := l_currentDate;
1435 x_resTbls.endDate(i) := l_currentDate;
1436 end if;
1437 end loop;
1438
1439
1440 l_currentDate := NVL(l_minDateYes,l_minDate); -- bug fix 6806858
1441 for i in 1..p_opTbl.count loop
1442 l_curOp := p_opTbl(i);
1443
1444 if(NOT l_hashOpSDate.exists(l_curOp)) then
1445 l_hashOpSDate(l_curOp) := l_currentDate;
1446 l_hashOpEDate(l_curOp) := l_currentDate;
1447 l_hashOpSDateYes(l_curOp) := l_currentDate;
1448 l_hashOpEDateYes(l_curOp) := l_currentDate;
1449 else
1450 l_currentDate := NVL(l_hashOpEDateYes(l_curOp), l_currentDate);
1451 end if;
1452 end loop;
1453
1454 l_OpStartDate.extend(p_opTbl.count);
1455 l_OpEndDate.extend(p_opTbl.count);
1456 l_OpQty.extend(p_opTbl.count);
1457 for i in 1..p_opTbl.count loop
1458 l_curOp := p_opTbl(i);
1459 l_OpStartDate(i) := l_hashOpSDate(l_curOp);
1460 l_OpEndDate(i) := l_hashOpEDate(l_curOp);
1461 l_OpQty(i) := g_opQty(l_curOp);
1462 end loop;
1463
1464 end if; -- p_scheduleMode <> WIP_CONSTANTS.CURRENT_SUB_GRP
1465
1466 else -- x_resTbls.opSeqNum.count = 0
1467 l_stat_num := 40;
1468 l_OpStartDate.extend(p_opTbl.count);
1469 l_OpEndDate.extend(p_opTbl.count);
1470 l_OpQty.extend(p_opTbl.count);
1471
1472 for i in 1..p_opTbl.count loop
1473 l_curOp := p_opTbl(i);
1474 l_OpStartDate(i) := p_anchorDate;
1475 l_OpEndDate(i) := p_anchorDate;
1476 l_OpQty(i) := g_opQty(l_curOp);
1477 end loop;
1478 -- BA: bug 3350262
1479 l_minDate := p_anchorDate;
1480 l_maxDate := p_anchorDate;
1481 -- EA: bug 3350262
1482 end if;
1483
1484 l_stat_num := 45;
1485
1486 ----------------------------------------------------------------
1487 l_cnt_wo := 0;
1488 l_idx := p_opTbl.first;
1489 while(l_idx IS NOT NULL and p_opTbl(l_idx) < 0)
1490 loop
1491 l_cnt_wo := l_cnt_wo +1;
1492 l_idx := p_opTbl.next(l_idx);
1493 end loop;
1494
1495
1496 l_stat_num := 50;
1497 -- if current op is the first op update update l_jobStartDate
1498 -- if current op = null, first op is routing start, update l_jobStartDate
1499 -- last op is routing end, update l_jobCompDate
1500 select nvl(OP_SEQ_NUM_INCREMENT, 10)
1501 into l_op_seq_incr
1502 from wsm_parameters
1503 where ORGANIZATION_ID = p_orgID;
1504
1505 -- BC: bug 3439417 the following logic should be changed
1506
1507 --if(l_minDate IS NOT NULL) then
1508 -- if(l_minDate < l_jobStartDate) then
1509 -- l_jobStartDate := l_minDate;
1510 -- else
1511 -- if(l_cnt_wo >= 1) then
1512 -- if(l_op_seq_incr = -p_opTbl(1)) then
1513 -- l_jobStartDate := l_minDate;
1514 -- end if;
1515 -- else
1516 -- if(p_opTbl(1) = p_strRtgOpSeqNum) then
1517 -- l_jobStartDate := l_minDate;
1518 -- end if;
1519 -- end if;
1520 -- end if;
1521 --end if;
1522
1523 --if(l_maxDate IS NOT NULL) then
1524 -- if(l_maxDate > l_jobComplDate) then
1525 -- l_jobComplDate := l_maxDate;
1526 -- else
1527 -- if(p_opTbl(p_opTbl.count) = p_endRtgOpSeqNum) then
1528 -- l_jobComplDate := l_maxDate;
1529 -- end if;
1530 -- end if;
1531 --end if;
1532
1533 if(l_cnt_wo >= 1) then
1534 if(l_op_seq_incr = -p_opTbl(1)) then
1535 l_newJobStartDate := NVL(l_minDate, l_jobStartDate);
1536 else
1537 l_newJobStartDate := l_jobStartDate;
1538 end if;
1539 else
1540 if(p_strRtgOpSeqNum = p_opTbl(1)) then
1541 l_newJobStartDate := NVL(l_minDate, l_jobStartDate);
1542 else
1543 l_newJobStartDate := l_jobStartDate;
1544 end if;
1545 end if;
1546 --Bug 5110917:If the last op is the current operation, the following
1547 --check fails.
1548 --if(p_opTbl(p_opTbl.count) = p_endRtgOpSeqNum) then
1549 if(p_opTbl(p_opTbl.count) = p_endRtgOpSeqNum)
1550 or p_opTbl(p_opTbl.count)<0 then --Current op is last op
1551 l_newJobComplDate := NVL(l_maxDate, l_jobComplDate);
1552 else
1553 l_newJobComplDate := l_jobComplDate;
1554 end if;
1555
1556 if(g_discrete_charges_exist) then
1557 -- since the job has charge, should not allow changing job start date
1558 l_newJobStartDate := l_jobStartDate;
1559 end if;
1560 -- EC: bug 3439417
1561
1562
1563 l_stat_num := 60;
1564 --update job
1565 --if(l_jobStartDate IS NOT NULL or l_jobComplDate IS NOT NULL) then
1566 if(l_jobStartDate <> l_newJobStartDate or l_jobComplDate <> l_newJobComplDate) then
1567
1568 update wip_discrete_jobs
1569 set --scheduled_start_date = NVL(l_jobStartDate, scheduled_start_date),
1570 --scheduled_completion_date = NVL(l_jobComplDate, scheduled_completion_date),
1571 scheduled_start_date = l_newJobStartDate,
1572 scheduled_completion_date = l_newJobComplDate,
1573 ----standard who columns----
1574 last_update_date = l_sysdate,
1575 last_updated_by = l_userID,
1576 last_update_login = l_loginID,
1577 request_id = l_reqID,
1578 program_application_id = l_progApplID,
1579 program_id = l_progID,
1580 program_update_date = l_sysDate
1581 where wip_entity_id = p_wipEntityID
1582 and organization_id = p_orgID;
1583
1584 if(l_logLevel <= wip_constants.full_logging) then
1585 wip_logger.log('wrote job', l_retStatus);
1586 end if;
1587 end if;
1588
1589
1590 l_stat_num := 70;
1591 --update operations WO
1592 forall i in 1..l_cnt_wo
1593 update wip_operations
1594 set first_unit_start_date = NVL(l_OpStartDate(i), first_unit_start_date),
1595 last_unit_start_date = NVL(l_OpStartDate(i), last_unit_start_date),
1596 first_unit_completion_date = NVL(l_OpEndDate(i), first_unit_completion_date),
1597 last_unit_completion_date = NVL(l_OpEndDate(i), last_unit_completion_date),
1598 scheduled_quantity = l_OpQty(i),
1599 ----standard who columns----
1600 last_update_date = l_sysdate,
1601 last_updated_by = l_userID,
1602 last_update_login = l_loginID,
1603 request_id = l_reqID,
1604 program_application_id = l_progApplID,
1605 program_id = l_progID,
1606 program_update_date = l_sysDate
1607 where wip_entity_id = p_wipEntityID
1608 and organization_id = p_orgID
1609 and operation_seq_num = -p_opTbl(i);
1610 if(l_logLevel <= wip_constants.full_logging) then
1611 wip_logger.log('wrote WO', l_retStatus);
1612 end if;
1613
1614
1615 l_stat_num := 80;
1616 --update operations WCO
1617 forall i in l_cnt_wo+1..p_opTbl.count
1618 update wsm_copy_operations
1619 set reco_start_date = l_OpStartDate(i),
1620 reco_completion_date = l_OpEndDate(i),
1621 reco_scheduled_quantity = l_OpQty(i),
1622 ----standard who columns----
1623 last_update_date = l_sysdate,
1624 last_updated_by = l_userID,
1625 last_update_login = l_loginID,
1626 request_id = l_reqID,
1627 program_application_id = l_progApplID,
1628 program_id = l_progID,
1629 program_update_date = l_sysDate
1630 where wip_entity_id = p_wipEntityID
1631 and organization_id = p_orgID
1632 and operation_seq_num = p_opTbl(i);
1633 if(l_logLevel <= wip_constants.full_logging) then
1634 wip_logger.log('wrote WCO', l_retStatus);
1635 end if;
1636
1637 --bug 6345672: begin
1638 l_stat_num := 85;
1639 if g_update_current_op and l_cnt_wo is not NULL and l_cnt_wo > 0 THEN --bug 6345672
1640 update wsm_copy_operations
1641 set reco_start_date = l_OpStartDate(l_cnt_wo),
1642 reco_completion_date = l_OpEndDate(l_cnt_wo),
1643 reco_scheduled_quantity = l_OpQty(l_cnt_wo),
1644 ----standard who columns----
1645 last_update_date = l_sysdate,
1646 last_updated_by = l_userID,
1647 last_update_login = l_loginID,
1648 request_id = l_reqID,
1649 program_application_id = l_progApplID,
1650 program_id = l_progID,
1651 program_update_date = l_sysDate
1652 where wip_entity_id = p_wipEntityID
1653 and organization_id = p_orgID
1654 and operation_seq_num = -p_opTbl(l_cnt_wo);
1655
1656 if(l_logLevel <= wip_constants.full_logging) then
1657 wip_logger.log('wrote WCO for current op', l_retStatus);
1658 end if;
1659 end if;
1660 --bug 6345672: end
1661
1662 ----------------------------------------------------------------
1663 l_stat_num := 90;
1664 --update mtl requirement dates WRO
1665 forall i in 1..l_cnt_wo
1666 update wip_requirement_operations
1667 set date_required = NVL(l_OpStartDate(i), date_required),
1668 ----standard who columns----
1669 last_update_date = l_sysdate,
1670 last_updated_by = l_userID,
1671 last_update_login = l_loginID,
1672 request_id = l_reqID,
1673 program_application_id = l_progApplID,
1674 program_id = l_progID,
1675 program_update_date = l_sysDate
1676 where wip_entity_id = p_wipEntityID
1677 and organization_id = p_orgID
1678 and operation_seq_num = -p_opTbl(i);
1679 if(l_logLevel <= wip_constants.full_logging) then
1680 wip_logger.log('wrote WRO', l_retStatus);
1681 end if;
1682
1683 l_stat_num := 100;
1684 --update mtl requirement dates WCRO
1685 forall i in l_cnt_wo+1..p_opTbl.count
1686 update wsm_copy_requirement_ops
1687 set reco_date_required = l_OpStartDate(i),
1688 ----standard who columns----
1689 last_update_date = l_sysdate,
1690 last_updated_by = l_userID,
1691 last_update_login = l_loginID,
1692 request_id = l_reqID,
1693 program_application_id = l_progApplID,
1694 program_id = l_progID,
1695 program_update_date = l_sysDate
1696 where wip_entity_id = p_wipEntityID
1697 and organization_id = p_orgID
1698 and operation_seq_num = p_opTbl(i);
1699 if(l_logLevel <= wip_constants.full_logging) then
1700 wip_logger.log('wrote WCRO', l_retStatus);
1701 end if;
1702
1703 --bug 6345672: begin
1704 l_stat_num := 105;
1705 if g_update_current_op and l_cnt_wo is not NULL and l_cnt_wo > 0 then --bug 6345672
1706 update wsm_copy_requirement_ops
1707 set reco_date_required = l_OpStartDate(l_cnt_wo),
1708 ----standard who columns----
1709 last_update_date = l_sysdate,
1710 last_updated_by = l_userID,
1711 last_update_login = l_loginID,
1712 request_id = l_reqID,
1713 program_application_id = l_progApplID,
1714 program_id = l_progID,
1715 program_update_date = l_sysDate
1716 where wip_entity_id = p_wipEntityID
1717 and organization_id = p_orgID
1718 and operation_seq_num = -p_opTbl(l_cnt_wo);
1719
1720 if(l_logLevel <= wip_constants.full_logging) then
1721 wip_logger.log('wrote WCRO for current op', l_retStatus);
1722 end if;
1723 end if;
1724 --bug 6345672: end
1725
1726 ----------------------------------------------------------------
1727 if(x_resTbls.opSeqNum.count <> 0) then
1728
1729 l_cnt_wor := 0;
1730 l_idx := x_resTbls.opSeqNum.first;
1731 while(l_idx IS NOT NULL and x_resTbls.opSeqNum(l_idx) < 0)
1732 loop
1733 l_cnt_wor := l_cnt_wor +1;
1734 l_idx := x_resTbls.opSeqNum.next(l_idx);
1735 end loop;
1736
1737 l_stat_num := 110;
1738 --update resources (WOR)
1739 forall i in 1..l_cnt_wor
1740 update wip_operation_resources
1741 set start_date = NVL(x_resTbls.startDate(i), start_date),
1742 completion_date = NVL(x_resTbls.endDate(i), completion_date),
1743 ----standard who columns----
1744 last_update_date = l_sysdate,
1745 last_updated_by = l_userID,
1746 last_update_login = l_loginID,
1747 request_id = l_reqID,
1748 program_application_id = l_progApplID,
1749 program_id = l_progID,
1750 program_update_date = l_sysDate
1751 where wip_entity_id = p_wipEntityID
1752 and organization_id = p_orgID
1753 and operation_seq_num = -x_resTbls.opSeqNum(i)
1754 and resource_seq_num = x_resTbls.resSeqNum(i);
1755 if(l_logLevel <= wip_constants.full_logging) then
1756 wip_logger.log('wrote WOR', l_retStatus);
1757 end if;
1758
1759 l_stat_num := 120;
1760 --update resources (WCOR)
1761 forall i in l_cnt_wor+1..x_resTbls.resID.count
1762 update wsm_copy_op_resources
1763 set reco_start_date = x_resTbls.startDate(i),
1764 reco_completion_date = x_resTbls.endDate(i),
1765 ----standard who columns----
1766 last_update_date = l_sysdate,
1767 last_updated_by = l_userID,
1768 last_update_login = l_loginID,
1769 request_id = l_reqID,
1770 program_application_id = l_progApplID,
1771 program_id = l_progID,
1772 program_update_date = l_sysDate
1773 where wip_entity_id = p_wipEntityID
1774 and organization_id = p_orgID
1775 and operation_seq_num = x_resTbls.opSeqNum(i)
1776 and resource_seq_num = x_resTbls.resSeqNum(i);
1777 if(l_logLevel <= wip_constants.full_logging) then
1778 wip_logger.log('wrote WCOR', l_retStatus);
1779 end if;
1780
1781 --bug 6345672: begin
1782 l_stat_num := 125;
1783 if g_update_current_op AND l_cnt_wor > 0 and l_cnt_wor is not NULL then --bug 6345672/ -- bug 7248484
1784 update wsm_copy_op_resources
1785 set reco_start_date = x_resTbls.startDate(l_cnt_wor),
1786 reco_completion_date = x_resTbls.endDate(l_cnt_wor),
1787 ----standard who columns----
1788 last_update_date = l_sysdate,
1789 last_updated_by = l_userID,
1790 last_update_login = l_loginID,
1791 request_id = l_reqID,
1792 program_application_id = l_progApplID,
1793 program_id = l_progID,
1794 program_update_date = l_sysDate
1795 where wip_entity_id = p_wipEntityID
1796 and organization_id = p_orgID
1797 and operation_seq_num = -x_resTbls.opSeqNum(l_cnt_wor)
1798 and resource_seq_num = x_resTbls.resSeqNum(l_cnt_wor);
1799
1800 if(l_logLevel <= wip_constants.full_logging) then
1801 wip_logger.log('wrote WCOR for current op', l_retStatus);
1802 end if;
1803 end if;
1804 --bug 6345672: end
1805
1806 ----------------------------------------------------------------
1807 l_stat_num := 130;
1808 --update resources instances (WORI)
1809 forall i in 1..l_cnt_wor
1810 update wip_op_resource_instances
1811 set start_date = NVL(x_resTbls.startDate(i), start_date),
1812 completion_date = NVL(x_resTbls.endDate(i), completion_date),
1813 ----standard who columns----
1814 last_update_date = l_sysdate,
1815 last_updated_by = l_userID,
1816 last_update_login = l_loginID
1817 where wip_entity_id = p_wipEntityID
1818 and organization_id = p_orgID
1819 and operation_seq_num = -x_resTbls.opSeqNum(i)
1820 and resource_seq_num = x_resTbls.resSeqNum(i);
1821 if(l_logLevel <= wip_constants.full_logging) then
1822 wip_logger.log('wrote WORI', l_retStatus);
1823 end if;
1824
1825 l_stat_num := 140;
1826 --update resources instances (WCORI)
1827 forall i in l_cnt_wor+1..x_resTbls.resID.count
1828 update wsm_copy_op_resource_instances
1829 set start_date = x_resTbls.startDate(i),
1830 completion_date = x_resTbls.endDate(i),
1831 ----standard who columns----
1832 last_update_date = l_sysdate,
1833 last_updated_by = l_userID,
1834 last_update_login = l_loginID
1835 where wip_entity_id = p_wipEntityID
1836 and organization_id = p_orgID
1837 and operation_seq_num = x_resTbls.opSeqNum(i)
1838 and resource_seq_num = x_resTbls.resSeqNum(i);
1839 if(l_logLevel <= wip_constants.full_logging) then
1840 wip_logger.log('wrote WCORI', l_retStatus);
1841 end if;
1842
1843 --bug 6345672: begin
1844 l_stat_num := 145;
1845 if g_update_current_op and l_cnt_wor > 0 and l_cnt_wor is not NULL then --bug 6345672 -- bug 7248484
1846 update wsm_copy_op_resource_instances
1847 set start_date = x_resTbls.startDate(l_cnt_wor),
1848 completion_date = x_resTbls.endDate(l_cnt_wor),
1849 ----standard who columns----
1850 last_update_date = l_sysdate,
1851 last_updated_by = l_userID,
1852 last_update_login = l_loginID
1853 where wip_entity_id = p_wipEntityID
1854 and organization_id = p_orgID
1855 and operation_seq_num = -x_resTbls.opSeqNum(l_cnt_wor)
1856 and resource_seq_num = x_resTbls.resSeqNum(l_cnt_wor);
1857 if(l_logLevel <= wip_constants.full_logging) then
1858 wip_logger.log('wrote WCORI for current op', l_retStatus);
1859 end if;
1860 end if;
1861 --bug 6345672: end
1862
1863 ----------------------------------------------------------------
1864
1865 l_stat_num := 150;
1866 --update resources usage (WORU)
1867 forall i in 1..l_cnt_wor
1868 delete wip_operation_resource_usage
1869 where wip_entity_id = p_wipEntityID
1870 and operation_seq_num = -x_resTbls.opSeqNum(i)
1871 and resource_seq_num = x_resTbls.resSeqNum(i);
1872
1873 l_stat_num := 160;
1874 for i in 1..l_cnt_wor loop
1875 if(x_resTbls.usgStartIdx(i) is not null) then
1876 forall j in x_resTbls.usgStartIdx(i)..x_resTbls.usgEndIdx(i)
1877 insert into wip_operation_resource_usage (
1878 wip_entity_id,
1879 operation_seq_num,
1880 resource_seq_num,
1881 organization_id,
1882 start_date,
1883 completion_date,
1884 assigned_units,
1885 cumulative_processing_time,
1886 ----standard who columns----
1887 last_update_date,
1888 last_updated_by,
1889 creation_date,
1890 created_by,
1891 last_update_login,
1892 request_id,
1893 program_application_id,
1894 program_id,
1895 program_update_date
1896 ) values (
1897 p_wipEntityID,
1898 -x_resTbls.opSeqNum(i),
1899 x_resTbls.resSeqNum(i),
1900 p_orgID,
1901 x_resTbls.usgStartDate(j),
1902 x_resTbls.usgEndDate(j),
1903 p_assignedUnits(i),
1904 x_resTbls.usgCumMinProcTime(j),
1905 l_sysdate,
1906 l_userID,
1907 l_sysdate,
1908 l_userID,
1909 l_loginID,
1910 l_reqID,
1911 l_progApplID,
1912 l_progID,
1913 l_sysdate);
1914 end if;
1915 end loop;
1916 if(l_logLevel <= wip_constants.full_logging) then
1917 wip_logger.log('wrote WORU', l_retStatus);
1918 end if;
1919
1920 l_stat_num := 170;
1921 forall i in 1..l_cnt_wor
1922 insert into wip_operation_resource_usage(
1923 wip_entity_id,
1924 operation_seq_num,
1925 resource_seq_num,
1926 organization_id,
1927 start_date,
1928 completion_date,
1929 assigned_units,
1930 instance_id,
1931 serial_number,
1932 cumulative_processing_time,
1933 ----standard who columns----
1934 last_update_date,
1935 last_updated_by,
1936 creation_date,
1937 created_by,
1938 last_update_login,
1939 request_id,
1940 program_application_id,
1941 program_id,
1942 program_update_date)
1943 select p_wipEntityID,
1944 woru.operation_seq_num,
1945 woru.resource_seq_num,
1946 p_orgID,
1947 woru.start_date,
1948 woru.completion_date,
1949 1,
1950 wori.instance_id,
1951 wori.serial_number,
1952 woru.cumulative_processing_time,
1953 l_sysdate,
1954 l_userID,
1955 l_sysdate,
1956 l_userID,
1957 l_loginID,
1958 l_reqID,
1959 l_progApplID,
1960 l_progID,
1961 l_sysdate
1962 from wip_operation_resource_usage woru,
1963 wip_op_resource_instances wori
1964 where woru.wip_entity_id = wori.wip_entity_id
1965 and woru.operation_seq_num = wori.operation_seq_num
1966 and woru.resource_seq_num = wori.resource_seq_num
1967 and woru.organization_id = wori.organization_id
1968 and woru.wip_entity_id = p_wipEntityID
1969 and woru.organization_id = p_orgID
1970 and wori.operation_seq_num = -x_resTbls.opSeqNum(i)
1971 and wori.resource_seq_num = x_resTbls.resSeqNum(i);
1972 if(l_logLevel <= wip_constants.full_logging) then
1973 wip_logger.log('wrote ' || SQL%ROWCOUNT || ' WROU', l_retStatus);
1974 end if;
1975
1976
1977 l_stat_num := 180;
1978 --update resources usage (WCORU)
1979 forall i in l_cnt_wor+1..x_resTbls.resID.count
1980 delete wsm_copy_op_resource_usage
1981 where wip_entity_id = p_wipEntityID
1982 and operation_seq_num = x_resTbls.opSeqNum(i)
1983 and resource_seq_num = x_resTbls.resSeqNum(i);
1984
1985 --bug 6345672: begin
1986 l_stat_num := 185;
1987 if g_update_current_op and l_cnt_wor > 0 and l_cnt_wor is not NULL then --bug 6345672 -- bug 7248484
1988 delete wsm_copy_op_resource_usage
1989 where wip_entity_id = p_wipEntityID
1990 and operation_seq_num = -x_resTbls.opSeqNum(l_cnt_wor)
1991 and resource_seq_num = x_resTbls.resSeqNum(l_cnt_wor);
1992 end if;
1993 --bug 6345672: end
1994
1995
1996 l_stat_num := 190;
1997 for i in l_cnt_wor+1..x_resTbls.resID.count loop
1998 if(x_resTbls.usgStartIdx(i) is not null) then
1999 forall j in x_resTbls.usgStartIdx(i)..x_resTbls.usgEndIdx(i)
2000 insert into wsm_copy_op_resource_usage(
2001 wip_entity_id,
2002 operation_seq_num,
2003 resource_seq_num,
2004 organization_id,
2005 start_date,
2006 completion_date,
2007 assigned_units,
2008 cumulative_processing_time,
2009 ----standard who columns----
2010 last_update_date,
2011 last_updated_by,
2012 creation_date,
2013 created_by,
2014 last_update_login,
2015 request_id,
2016 program_application_id,
2017 program_id,
2018 program_update_date
2019 ) values (
2020 p_wipEntityID,
2021 x_resTbls.opSeqNum(i),
2022 x_resTbls.resSeqNum(i),
2023 p_orgID,
2024 x_resTbls.usgStartDate(j),
2025 x_resTbls.usgEndDate(j),
2026 p_assignedUnits(i),
2027 x_resTbls.usgCumMinProcTime(j),
2028 l_sysdate,
2029 l_userID,
2030 l_sysdate,
2031 l_userID,
2032 l_loginID,
2033 l_reqID,
2034 l_progApplID,
2035 l_progID,
2036 l_sysdate);
2037 end if;
2038 end loop;
2039 if(l_logLevel <= wip_constants.full_logging) then
2040 wip_logger.log('wrote WCORU', l_retStatus);
2041 end if;
2042
2043 --bug 6345672: begin
2044 l_stat_num := 195;
2045 if g_update_current_op and l_cnt_wor > 0 and l_cnt_wor is not NULL then --bug 6345672/ -- bug 7248484
2046 if(x_resTbls.usgStartIdx(l_cnt_wor) is not null) then
2047 forall j in x_resTbls.usgStartIdx(l_cnt_wor)..x_resTbls.usgEndIdx(l_cnt_wor)
2048 insert into wsm_copy_op_resource_usage(
2049 wip_entity_id,
2050 operation_seq_num,
2051 resource_seq_num,
2052 organization_id,
2053 start_date,
2054 completion_date,
2055 assigned_units,
2056 cumulative_processing_time,
2057 ----standard who columns----
2058 last_update_date,
2059 last_updated_by,
2060 creation_date,
2061 created_by,
2062 last_update_login,
2063 request_id,
2064 program_application_id,
2065 program_id,
2066 program_update_date
2067 ) values (
2068 p_wipEntityID,
2069 -x_resTbls.opSeqNum(l_cnt_wor),
2070 x_resTbls.resSeqNum(l_cnt_wor),
2071 p_orgID,
2072 x_resTbls.usgStartDate(j),
2073 x_resTbls.usgEndDate(j),
2074 p_assignedUnits(l_cnt_wor),
2075 x_resTbls.usgCumMinProcTime(j),
2076 l_sysdate,
2077 l_userID,
2078 l_sysdate,
2079 l_userID,
2080 l_loginID,
2081 l_reqID,
2082 l_progApplID,
2083 l_progID,
2084 l_sysdate);
2085 end if;
2086
2087 if(l_logLevel <= wip_constants.full_logging) then
2088 wip_logger.log('wrote WCORU for current op', l_retStatus);
2089 end if;
2090 end if;
2091 --bug 6345672: end
2092
2093 l_stat_num := 200;
2094 forall i in l_cnt_wor+1..x_resTbls.resID.count
2095 insert into wsm_copy_op_resource_usage(
2096 wip_entity_id,
2097 operation_seq_num,
2098 resource_seq_num,
2099 organization_id,
2100 start_date,
2101 completion_date,
2102 assigned_units,
2103 instance_id,
2104 serial_number,
2105 cumulative_processing_time,
2106 ----standard who columns----
2107 last_update_date,
2108 last_updated_by,
2109 creation_date,
2110 created_by,
2111 last_update_login,
2112 request_id,
2113 program_application_id,
2114 program_id,
2115 program_update_date)
2116 select p_wipEntityID,
2117 wcoru.operation_seq_num,
2118 wcoru.resource_seq_num,
2119 p_orgID,
2120 wcoru.start_date,
2121 wcoru.completion_date,
2122 1,
2123 wcori.instance_id,
2124 wcori.serial_number,
2125 wcoru.cumulative_processing_time,
2126 l_sysdate,
2127 l_userID,
2128 l_sysdate,
2129 l_userID,
2130 l_loginID,
2131 l_reqID,
2132 l_progApplID,
2133 l_progID,
2134 l_sysdate
2135 from wsm_copy_op_resource_usage wcoru,
2136 wsm_copy_op_resource_instances wcori
2137 where wcoru.wip_entity_id = wcori.wip_entity_id
2138 and wcoru.operation_seq_num = wcori.operation_seq_num
2139 and wcoru.resource_seq_num = wcori.resource_seq_num
2140 and wcoru.organization_id = wcori.organization_id
2141 and wcoru.wip_entity_id = p_wipEntityID
2142 and wcoru.organization_id = p_orgID
2143 and wcori.operation_seq_num = x_resTbls.opSeqNum(i)
2144 and wcori.resource_seq_num = x_resTbls.resSeqNum(i);
2145 if(l_logLevel <= wip_constants.full_logging) then
2146 wip_logger.log('wrote ' || SQL%ROWCOUNT || ' WCORU', l_retStatus);
2147 end if;
2148
2149 --bug 6345672: begin
2150 l_stat_num := 205;
2151 if g_update_current_op and l_cnt_wor > 0 and l_cnt_wor is not NULL then --bug 6345672 -- bug 7248484
2152 insert into wsm_copy_op_resource_usage(
2153 wip_entity_id,
2154 operation_seq_num,
2155 resource_seq_num,
2156 organization_id,
2157 start_date,
2158 completion_date,
2159 assigned_units,
2160 instance_id,
2161 serial_number,
2162 cumulative_processing_time,
2163 ----standard who columns----
2164 last_update_date,
2165 last_updated_by,
2166 creation_date,
2167 created_by,
2168 last_update_login,
2169 request_id,
2170 program_application_id,
2171 program_id,
2172 program_update_date)
2173 select p_wipEntityID,
2174 wcoru.operation_seq_num,
2175 wcoru.resource_seq_num,
2176 p_orgID,
2177 wcoru.start_date,
2178 wcoru.completion_date,
2179 1,
2180 wcori.instance_id,
2181 wcori.serial_number,
2182 wcoru.cumulative_processing_time,
2183 l_sysdate,
2184 l_userID,
2185 l_sysdate,
2186 l_userID,
2187 l_loginID,
2188 l_reqID,
2189 l_progApplID,
2190 l_progID,
2191 l_sysdate
2192 from wsm_copy_op_resource_usage wcoru,
2193 wsm_copy_op_resource_instances wcori
2194 where wcoru.wip_entity_id = wcori.wip_entity_id
2195 and wcoru.operation_seq_num = wcori.operation_seq_num
2196 and wcoru.resource_seq_num = wcori.resource_seq_num
2197 and wcoru.organization_id = wcori.organization_id
2198 and wcoru.wip_entity_id = p_wipEntityID
2199 and wcoru.organization_id = p_orgID
2200 and wcori.operation_seq_num = -x_resTbls.opSeqNum(l_cnt_wor)
2201 and wcori.resource_seq_num = x_resTbls.resSeqNum(l_cnt_wor);
2202 if(l_logLevel <= wip_constants.full_logging) then
2203 wip_logger.log('wrote ' || SQL%ROWCOUNT || ' WCORU for current op', l_retStatus);
2204 end if;
2205
2206 end if;
2207 --bug 6345672: end
2208
2209 end if;
2210
2211 if (l_logLevel <= wip_constants.trace_logging) then
2212 wip_logger.exitPoint(
2213 p_procName => 'wsm_infinite_scheduler_pvt.wsmJobWriter',
2214 p_procReturnStatus => x_returnStatus,
2215 p_msg => 'success',
2216 x_returnStatus => l_retStatus);
2217 end if;
2218
2219 exception
2220
2221 when others then
2222 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
2223 fnd_msg_pub.add_exc_msg(
2224 p_pkg_name => 'wsm_infinite_scheduler_pvt',
2225 p_procedure_name => 'wsmJobWriter',
2226 p_error_text => SQLERRM || ' (writer #' || l_stat_num || ')' );
2227 if (l_logLevel <= wip_constants.trace_logging) then
2228 wip_logger.exitPoint(
2229 p_procName => 'wsm_infinite_scheduler_pvt.wsmJobWriter',
2230 p_procReturnStatus => x_returnStatus,
2231 p_msg => 'unexp error: ' || SQLERRM || ' (writer #' || l_stat_num || ')',
2232 x_returnStatus => l_retStatus);
2233 end if;
2234 end wsmJobWriter;
2235
2236
2237 end wsm_infinite_scheduler_pvt;