[Home] [Help]
PACKAGE BODY: APPS.WSM_INFINITE_SCHEDULER_PVT
Source
1 package body wsm_infinite_scheduler_pvt as
2 /* $Header: WSMVIFSB.pls 120.7.12020000.2 2013/03/22 09:22:57 kswarna 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 -- added the below and condition on p_scheduleMode for bug 16384136
1550 --This is to ensure that the job completion date is not updated when current op which is non-recommended is
1551 --being scheduled.
1552 if(p_opTbl(p_opTbl.count) = p_endRtgOpSeqNum)
1553 or (p_opTbl(p_opTbl.count)<0 and p_scheduleMode <> 9) then --Current op is last op
1554 l_newJobComplDate := NVL(l_maxDate, l_jobComplDate);
1555 else
1556 l_newJobComplDate := l_jobComplDate;
1557 end if;
1558
1559 if(g_discrete_charges_exist) then
1560 -- since the job has charge, should not allow changing job start date
1561 l_newJobStartDate := l_jobStartDate;
1562 end if;
1563 -- EC: bug 3439417
1564
1565
1566 l_stat_num := 60;
1567 --update job
1568 --if(l_jobStartDate IS NOT NULL or l_jobComplDate IS NOT NULL) then
1569 if(l_jobStartDate <> l_newJobStartDate or l_jobComplDate <> l_newJobComplDate) then
1570
1571 update wip_discrete_jobs
1572 set --scheduled_start_date = NVL(l_jobStartDate, scheduled_start_date),
1573 --scheduled_completion_date = NVL(l_jobComplDate, scheduled_completion_date),
1574 scheduled_start_date = l_newJobStartDate,
1575 scheduled_completion_date = l_newJobComplDate,
1576 ----standard who columns----
1577 last_update_date = l_sysdate,
1578 last_updated_by = l_userID,
1579 last_update_login = l_loginID,
1580 request_id = l_reqID,
1581 program_application_id = l_progApplID,
1582 program_id = l_progID,
1583 program_update_date = l_sysDate
1584 where wip_entity_id = p_wipEntityID
1585 and organization_id = p_orgID;
1586
1587 if(l_logLevel <= wip_constants.full_logging) then
1588 wip_logger.log('wrote job', l_retStatus);
1589 end if;
1590 end if;
1591
1592
1593 l_stat_num := 70;
1594 --update operations WO
1595 forall i in 1..l_cnt_wo
1596 update wip_operations
1597 set first_unit_start_date = NVL(l_OpStartDate(i), first_unit_start_date),
1598 last_unit_start_date = NVL(l_OpStartDate(i), last_unit_start_date),
1599 first_unit_completion_date = NVL(l_OpEndDate(i), first_unit_completion_date),
1600 last_unit_completion_date = NVL(l_OpEndDate(i), last_unit_completion_date),
1601 scheduled_quantity = l_OpQty(i),
1602 ----standard who columns----
1603 last_update_date = l_sysdate,
1604 last_updated_by = l_userID,
1605 last_update_login = l_loginID,
1606 request_id = l_reqID,
1607 program_application_id = l_progApplID,
1608 program_id = l_progID,
1609 program_update_date = l_sysDate
1610 where wip_entity_id = p_wipEntityID
1611 and organization_id = p_orgID
1612 and operation_seq_num = -p_opTbl(i);
1613 if(l_logLevel <= wip_constants.full_logging) then
1614 wip_logger.log('wrote WO', l_retStatus);
1615 end if;
1616
1617
1618 l_stat_num := 80;
1619 --update operations WCO
1620 forall i in l_cnt_wo+1..p_opTbl.count
1621 update wsm_copy_operations
1622 set reco_start_date = l_OpStartDate(i),
1623 reco_completion_date = l_OpEndDate(i),
1624 reco_scheduled_quantity = l_OpQty(i),
1625 ----standard who columns----
1626 last_update_date = l_sysdate,
1627 last_updated_by = l_userID,
1628 last_update_login = l_loginID,
1629 request_id = l_reqID,
1630 program_application_id = l_progApplID,
1631 program_id = l_progID,
1632 program_update_date = l_sysDate
1633 where wip_entity_id = p_wipEntityID
1634 and organization_id = p_orgID
1635 and operation_seq_num = p_opTbl(i);
1636 if(l_logLevel <= wip_constants.full_logging) then
1637 wip_logger.log('wrote WCO', l_retStatus);
1638 end if;
1639
1640 --bug 6345672: begin
1641 l_stat_num := 85;
1642 if g_update_current_op and l_cnt_wo is not NULL and l_cnt_wo > 0 then --bug 6345672
1643 update wsm_copy_operations
1644 set reco_start_date = l_OpStartDate(l_cnt_wo),
1645 reco_completion_date = l_OpEndDate(l_cnt_wo),
1646 reco_scheduled_quantity = l_OpQty(l_cnt_wo),
1647 ----standard who columns----
1648 last_update_date = l_sysdate,
1649 last_updated_by = l_userID,
1650 last_update_login = l_loginID,
1651 request_id = l_reqID,
1652 program_application_id = l_progApplID,
1653 program_id = l_progID,
1654 program_update_date = l_sysDate
1655 where wip_entity_id = p_wipEntityID
1656 and organization_id = p_orgID
1657 and operation_seq_num = -p_opTbl(l_cnt_wo);
1658
1659 if(l_logLevel <= wip_constants.full_logging) then
1660 wip_logger.log('wrote WCO for current op', l_retStatus);
1661 end if;
1662 end if;
1663 --bug 6345672: end
1664
1665 ----------------------------------------------------------------
1666 l_stat_num := 90;
1667 --update mtl requirement dates WRO
1668 forall i in 1..l_cnt_wo
1669 update wip_requirement_operations
1670 set date_required = NVL(l_OpStartDate(i), date_required),
1671 ----standard who columns----
1672 last_update_date = l_sysdate,
1673 last_updated_by = l_userID,
1674 last_update_login = l_loginID,
1675 request_id = l_reqID,
1676 program_application_id = l_progApplID,
1677 program_id = l_progID,
1678 program_update_date = l_sysDate
1679 where wip_entity_id = p_wipEntityID
1680 and organization_id = p_orgID
1681 and operation_seq_num = -p_opTbl(i);
1682 if(l_logLevel <= wip_constants.full_logging) then
1683 wip_logger.log('wrote WRO', l_retStatus);
1684 end if;
1685
1686 l_stat_num := 100;
1687 --update mtl requirement dates WCRO
1688 forall i in l_cnt_wo+1..p_opTbl.count
1689 update wsm_copy_requirement_ops
1690 set reco_date_required = l_OpStartDate(i),
1691 ----standard who columns----
1692 last_update_date = l_sysdate,
1693 last_updated_by = l_userID,
1694 last_update_login = l_loginID,
1695 request_id = l_reqID,
1696 program_application_id = l_progApplID,
1697 program_id = l_progID,
1698 program_update_date = l_sysDate
1699 where wip_entity_id = p_wipEntityID
1700 and organization_id = p_orgID
1701 and operation_seq_num = p_opTbl(i);
1702 if(l_logLevel <= wip_constants.full_logging) then
1703 wip_logger.log('wrote WCRO', l_retStatus);
1704 end if;
1705
1706 --bug 6345672: begin
1707 l_stat_num := 105;
1708 if g_update_current_op and l_cnt_wo is not NULL and l_cnt_wo > 0 then --bug 6345672
1709 update wsm_copy_requirement_ops
1710 set reco_date_required = l_OpStartDate(l_cnt_wo),
1711 ----standard who columns----
1712 last_update_date = l_sysdate,
1713 last_updated_by = l_userID,
1714 last_update_login = l_loginID,
1715 request_id = l_reqID,
1716 program_application_id = l_progApplID,
1717 program_id = l_progID,
1718 program_update_date = l_sysDate
1719 where wip_entity_id = p_wipEntityID
1720 and organization_id = p_orgID
1721 and operation_seq_num = -p_opTbl(l_cnt_wo);
1722
1723 if(l_logLevel <= wip_constants.full_logging) then
1724 wip_logger.log('wrote WCRO for current op', l_retStatus);
1725 end if;
1726 end if;
1727 --bug 6345672: end
1728
1729 ----------------------------------------------------------------
1730 if(x_resTbls.opSeqNum.count <> 0) then
1731
1732 l_cnt_wor := 0;
1733 l_idx := x_resTbls.opSeqNum.first;
1734 while(l_idx IS NOT NULL and x_resTbls.opSeqNum(l_idx) < 0)
1735 loop
1736 l_cnt_wor := l_cnt_wor +1;
1737 l_idx := x_resTbls.opSeqNum.next(l_idx);
1738 end loop;
1739
1740 l_stat_num := 110;
1741 --update resources (WOR)
1742 forall i in 1..l_cnt_wor
1743 update wip_operation_resources
1744 set start_date = NVL(x_resTbls.startDate(i), start_date),
1745 completion_date = NVL(x_resTbls.endDate(i), completion_date),
1746 ----standard who columns----
1747 last_update_date = l_sysdate,
1748 last_updated_by = l_userID,
1749 last_update_login = l_loginID,
1750 request_id = l_reqID,
1751 program_application_id = l_progApplID,
1752 program_id = l_progID,
1753 program_update_date = l_sysDate
1754 where wip_entity_id = p_wipEntityID
1755 and organization_id = p_orgID
1756 and operation_seq_num = -x_resTbls.opSeqNum(i)
1757 and resource_seq_num = x_resTbls.resSeqNum(i);
1758 if(l_logLevel <= wip_constants.full_logging) then
1759 wip_logger.log('wrote WOR', l_retStatus);
1760 end if;
1761
1762 l_stat_num := 120;
1763 --update resources (WCOR)
1764 forall i in l_cnt_wor+1..x_resTbls.resID.count
1765 update wsm_copy_op_resources
1766 set reco_start_date = x_resTbls.startDate(i),
1767 reco_completion_date = x_resTbls.endDate(i),
1768 ----standard who columns----
1769 last_update_date = l_sysdate,
1770 last_updated_by = l_userID,
1771 last_update_login = l_loginID,
1772 request_id = l_reqID,
1773 program_application_id = l_progApplID,
1774 program_id = l_progID,
1775 program_update_date = l_sysDate
1776 where wip_entity_id = p_wipEntityID
1777 and organization_id = p_orgID
1778 and operation_seq_num = x_resTbls.opSeqNum(i)
1779 and resource_seq_num = x_resTbls.resSeqNum(i);
1780 if(l_logLevel <= wip_constants.full_logging) then
1781 wip_logger.log('wrote WCOR', l_retStatus);
1782 end if;
1783
1784 --bug 6345672: begin
1785 l_stat_num := 125;
1786 if g_update_current_op and l_cnt_wor > 0 and l_cnt_wor is not NULL then --bug 6345672/bug 7163667
1787 update wsm_copy_op_resources
1788 set reco_start_date = x_resTbls.startDate(l_cnt_wor),
1789 reco_completion_date = x_resTbls.endDate(l_cnt_wor),
1790 ----standard who columns----
1791 last_update_date = l_sysdate,
1792 last_updated_by = l_userID,
1793 last_update_login = l_loginID,
1794 request_id = l_reqID,
1795 program_application_id = l_progApplID,
1796 program_id = l_progID,
1797 program_update_date = l_sysDate
1798 where wip_entity_id = p_wipEntityID
1799 and organization_id = p_orgID
1800 and operation_seq_num = -x_resTbls.opSeqNum(l_cnt_wor)
1801 and resource_seq_num = x_resTbls.resSeqNum(l_cnt_wor);
1802
1803 if(l_logLevel <= wip_constants.full_logging) then
1804 wip_logger.log('wrote WCOR for current op', l_retStatus);
1805 end if;
1806 end if;
1807 --bug 6345672: end
1808
1809 ----------------------------------------------------------------
1810 l_stat_num := 130;
1811 --update resources instances (WORI)
1812 forall i in 1..l_cnt_wor
1813 update wip_op_resource_instances
1814 set start_date = NVL(x_resTbls.startDate(i), start_date),
1815 completion_date = NVL(x_resTbls.endDate(i), completion_date),
1816 ----standard who columns----
1817 last_update_date = l_sysdate,
1818 last_updated_by = l_userID,
1819 last_update_login = l_loginID
1820 where wip_entity_id = p_wipEntityID
1821 and organization_id = p_orgID
1822 and operation_seq_num = -x_resTbls.opSeqNum(i)
1823 and resource_seq_num = x_resTbls.resSeqNum(i);
1824 if(l_logLevel <= wip_constants.full_logging) then
1825 wip_logger.log('wrote WORI', l_retStatus);
1826 end if;
1827
1828 l_stat_num := 140;
1829 --update resources instances (WCORI)
1830 forall i in l_cnt_wor+1..x_resTbls.resID.count
1831 update wsm_copy_op_resource_instances
1832 set start_date = x_resTbls.startDate(i),
1833 completion_date = x_resTbls.endDate(i),
1834 ----standard who columns----
1835 last_update_date = l_sysdate,
1836 last_updated_by = l_userID,
1837 last_update_login = l_loginID
1838 where wip_entity_id = p_wipEntityID
1839 and organization_id = p_orgID
1840 and operation_seq_num = x_resTbls.opSeqNum(i)
1841 and resource_seq_num = x_resTbls.resSeqNum(i);
1842 if(l_logLevel <= wip_constants.full_logging) then
1843 wip_logger.log('wrote WCORI', l_retStatus);
1844 end if;
1845
1846 --bug 6345672: begin
1847 l_stat_num := 145;
1848 if g_update_current_op and l_cnt_wor > 0 and l_cnt_wor is not NULL then --bug 6345672/bug 7163667
1849 update wsm_copy_op_resource_instances
1850 set start_date = x_resTbls.startDate(l_cnt_wor),
1851 completion_date = x_resTbls.endDate(l_cnt_wor),
1852 ----standard who columns----
1853 last_update_date = l_sysdate,
1854 last_updated_by = l_userID,
1855 last_update_login = l_loginID
1856 where wip_entity_id = p_wipEntityID
1857 and organization_id = p_orgID
1858 and operation_seq_num = -x_resTbls.opSeqNum(l_cnt_wor)
1859 and resource_seq_num = x_resTbls.resSeqNum(l_cnt_wor);
1860 if(l_logLevel <= wip_constants.full_logging) then
1861 wip_logger.log('wrote WCORI for current op', l_retStatus);
1862 end if;
1863 end if;
1864 --bug 6345672: end
1865
1866 ----------------------------------------------------------------
1867
1868 l_stat_num := 150;
1869 --update resources usage (WORU)
1870 forall i in 1..l_cnt_wor
1871 delete wip_operation_resource_usage
1872 where wip_entity_id = p_wipEntityID
1873 and operation_seq_num = -x_resTbls.opSeqNum(i)
1874 and resource_seq_num = x_resTbls.resSeqNum(i);
1875
1876 l_stat_num := 160;
1877 for i in 1..l_cnt_wor loop
1878 if(x_resTbls.usgStartIdx(i) is not null) then
1879 forall j in x_resTbls.usgStartIdx(i)..x_resTbls.usgEndIdx(i)
1880 insert into wip_operation_resource_usage (
1881 wip_entity_id,
1882 operation_seq_num,
1883 resource_seq_num,
1884 organization_id,
1885 start_date,
1886 completion_date,
1887 assigned_units,
1888 cumulative_processing_time,
1889 ----standard who columns----
1890 last_update_date,
1891 last_updated_by,
1892 creation_date,
1893 created_by,
1894 last_update_login,
1895 request_id,
1896 program_application_id,
1897 program_id,
1898 program_update_date
1899 ) values (
1900 p_wipEntityID,
1901 -x_resTbls.opSeqNum(i),
1902 x_resTbls.resSeqNum(i),
1903 p_orgID,
1904 x_resTbls.usgStartDate(j),
1905 x_resTbls.usgEndDate(j),
1906 p_assignedUnits(i),
1907 x_resTbls.usgCumMinProcTime(j),
1908 l_sysdate,
1909 l_userID,
1910 l_sysdate,
1911 l_userID,
1912 l_loginID,
1913 l_reqID,
1914 l_progApplID,
1915 l_progID,
1916 l_sysdate);
1917 end if;
1918 end loop;
1919 if(l_logLevel <= wip_constants.full_logging) then
1920 wip_logger.log('wrote WORU', l_retStatus);
1921 end if;
1922
1923 l_stat_num := 170;
1924 forall i in 1..l_cnt_wor
1925 insert into wip_operation_resource_usage(
1926 wip_entity_id,
1927 operation_seq_num,
1928 resource_seq_num,
1929 organization_id,
1930 start_date,
1931 completion_date,
1932 assigned_units,
1933 instance_id,
1934 serial_number,
1935 cumulative_processing_time,
1936 ----standard who columns----
1937 last_update_date,
1938 last_updated_by,
1939 creation_date,
1940 created_by,
1941 last_update_login,
1942 request_id,
1943 program_application_id,
1944 program_id,
1945 program_update_date)
1946 select p_wipEntityID,
1947 woru.operation_seq_num,
1948 woru.resource_seq_num,
1949 p_orgID,
1950 woru.start_date,
1951 woru.completion_date,
1952 1,
1953 wori.instance_id,
1954 wori.serial_number,
1955 woru.cumulative_processing_time,
1956 l_sysdate,
1957 l_userID,
1958 l_sysdate,
1959 l_userID,
1960 l_loginID,
1961 l_reqID,
1962 l_progApplID,
1963 l_progID,
1964 l_sysdate
1965 from wip_operation_resource_usage woru,
1966 wip_op_resource_instances wori
1967 where woru.wip_entity_id = wori.wip_entity_id
1968 and woru.operation_seq_num = wori.operation_seq_num
1969 and woru.resource_seq_num = wori.resource_seq_num
1970 and woru.organization_id = wori.organization_id
1971 and woru.wip_entity_id = p_wipEntityID
1972 and woru.organization_id = p_orgID
1973 and wori.operation_seq_num = -x_resTbls.opSeqNum(i)
1974 and wori.resource_seq_num = x_resTbls.resSeqNum(i);
1975 if(l_logLevel <= wip_constants.full_logging) then
1976 wip_logger.log('wrote ' || SQL%ROWCOUNT || ' WROU', l_retStatus);
1977 end if;
1978
1979
1980 l_stat_num := 180;
1981 --update resources usage (WCORU)
1982 forall i in l_cnt_wor+1..x_resTbls.resID.count
1983 delete wsm_copy_op_resource_usage
1984 where wip_entity_id = p_wipEntityID
1985 and operation_seq_num = x_resTbls.opSeqNum(i)
1986 and resource_seq_num = x_resTbls.resSeqNum(i);
1987
1988 --bug 6345672: begin
1989 l_stat_num := 185;
1990 if g_update_current_op and l_cnt_wor > 0 and l_cnt_wor is not NULL then --bug 6345672/bug 7163667
1991 delete wsm_copy_op_resource_usage
1992 where wip_entity_id = p_wipEntityID
1993 and operation_seq_num = -x_resTbls.opSeqNum(l_cnt_wor)
1994 and resource_seq_num = x_resTbls.resSeqNum(l_cnt_wor);
1995 end if;
1996 --bug 6345672: end
1997
1998
1999 l_stat_num := 190;
2000 for i in l_cnt_wor+1..x_resTbls.resID.count loop
2001 if(x_resTbls.usgStartIdx(i) is not null) then
2002 forall j in x_resTbls.usgStartIdx(i)..x_resTbls.usgEndIdx(i)
2003 insert into wsm_copy_op_resource_usage(
2004 wip_entity_id,
2005 operation_seq_num,
2006 resource_seq_num,
2007 organization_id,
2008 start_date,
2009 completion_date,
2010 assigned_units,
2011 cumulative_processing_time,
2012 ----standard who columns----
2013 last_update_date,
2014 last_updated_by,
2015 creation_date,
2016 created_by,
2017 last_update_login,
2018 request_id,
2019 program_application_id,
2020 program_id,
2021 program_update_date
2022 ) values (
2023 p_wipEntityID,
2024 x_resTbls.opSeqNum(i),
2025 x_resTbls.resSeqNum(i),
2026 p_orgID,
2027 x_resTbls.usgStartDate(j),
2028 x_resTbls.usgEndDate(j),
2029 p_assignedUnits(i),
2030 x_resTbls.usgCumMinProcTime(j),
2031 l_sysdate,
2032 l_userID,
2033 l_sysdate,
2034 l_userID,
2035 l_loginID,
2036 l_reqID,
2037 l_progApplID,
2038 l_progID,
2039 l_sysdate);
2040 end if;
2041 end loop;
2042 if(l_logLevel <= wip_constants.full_logging) then
2043 wip_logger.log('wrote WCORU', l_retStatus);
2044 end if;
2045
2046 --bug 6345672: begin
2047 l_stat_num := 195;
2048 if g_update_current_op and l_cnt_wor > 0 and l_cnt_wor is not NULL then --bug 6345672/bug 7163667
2049 if(x_resTbls.usgStartIdx(l_cnt_wor) is not null) then
2050 forall j in x_resTbls.usgStartIdx(l_cnt_wor)..x_resTbls.usgEndIdx(l_cnt_wor)
2051 insert into wsm_copy_op_resource_usage(
2052 wip_entity_id,
2053 operation_seq_num,
2054 resource_seq_num,
2055 organization_id,
2056 start_date,
2057 completion_date,
2058 assigned_units,
2059 cumulative_processing_time,
2060 ----standard who columns----
2061 last_update_date,
2062 last_updated_by,
2063 creation_date,
2064 created_by,
2065 last_update_login,
2066 request_id,
2067 program_application_id,
2068 program_id,
2069 program_update_date
2070 ) values (
2071 p_wipEntityID,
2072 -x_resTbls.opSeqNum(l_cnt_wor),
2073 x_resTbls.resSeqNum(l_cnt_wor),
2074 p_orgID,
2075 x_resTbls.usgStartDate(j),
2076 x_resTbls.usgEndDate(j),
2077 p_assignedUnits(l_cnt_wor),
2078 x_resTbls.usgCumMinProcTime(j),
2079 l_sysdate,
2080 l_userID,
2081 l_sysdate,
2082 l_userID,
2083 l_loginID,
2084 l_reqID,
2085 l_progApplID,
2086 l_progID,
2087 l_sysdate);
2088 end if;
2089
2090 if(l_logLevel <= wip_constants.full_logging) then
2091 wip_logger.log('wrote WCORU for current op', l_retStatus);
2092 end if;
2093 end if;
2094 --bug 6345672: end
2095
2096 l_stat_num := 200;
2097 forall i in l_cnt_wor+1..x_resTbls.resID.count
2098 insert into wsm_copy_op_resource_usage(
2099 wip_entity_id,
2100 operation_seq_num,
2101 resource_seq_num,
2102 organization_id,
2103 start_date,
2104 completion_date,
2105 assigned_units,
2106 instance_id,
2107 serial_number,
2108 cumulative_processing_time,
2109 ----standard who columns----
2110 last_update_date,
2111 last_updated_by,
2112 creation_date,
2113 created_by,
2114 last_update_login,
2115 request_id,
2116 program_application_id,
2117 program_id,
2118 program_update_date)
2119 select p_wipEntityID,
2120 wcoru.operation_seq_num,
2121 wcoru.resource_seq_num,
2122 p_orgID,
2123 wcoru.start_date,
2124 wcoru.completion_date,
2125 1,
2126 wcori.instance_id,
2127 wcori.serial_number,
2128 wcoru.cumulative_processing_time,
2129 l_sysdate,
2130 l_userID,
2131 l_sysdate,
2132 l_userID,
2133 l_loginID,
2134 l_reqID,
2135 l_progApplID,
2136 l_progID,
2137 l_sysdate
2138 from wsm_copy_op_resource_usage wcoru,
2139 wsm_copy_op_resource_instances wcori
2140 where wcoru.wip_entity_id = wcori.wip_entity_id
2141 and wcoru.operation_seq_num = wcori.operation_seq_num
2142 and wcoru.resource_seq_num = wcori.resource_seq_num
2143 and wcoru.organization_id = wcori.organization_id
2144 and wcoru.wip_entity_id = p_wipEntityID
2145 and wcoru.organization_id = p_orgID
2146 and wcori.operation_seq_num = x_resTbls.opSeqNum(i)
2147 and wcori.resource_seq_num = x_resTbls.resSeqNum(i);
2148 if(l_logLevel <= wip_constants.full_logging) then
2149 wip_logger.log('wrote ' || SQL%ROWCOUNT || ' WCORU', l_retStatus);
2150 end if;
2151
2152 --bug 6345672: begin
2153 l_stat_num := 205;
2154 if g_update_current_op and l_cnt_wor > 0 and l_cnt_wor is not NULL then --bug 6345672/bug 7163667
2155 insert into wsm_copy_op_resource_usage(
2156 wip_entity_id,
2157 operation_seq_num,
2158 resource_seq_num,
2159 organization_id,
2160 start_date,
2161 completion_date,
2162 assigned_units,
2163 instance_id,
2164 serial_number,
2165 cumulative_processing_time,
2166 ----standard who columns----
2167 last_update_date,
2168 last_updated_by,
2169 creation_date,
2170 created_by,
2171 last_update_login,
2172 request_id,
2173 program_application_id,
2174 program_id,
2175 program_update_date)
2176 select p_wipEntityID,
2177 wcoru.operation_seq_num,
2178 wcoru.resource_seq_num,
2179 p_orgID,
2180 wcoru.start_date,
2181 wcoru.completion_date,
2182 1,
2183 wcori.instance_id,
2184 wcori.serial_number,
2185 wcoru.cumulative_processing_time,
2186 l_sysdate,
2187 l_userID,
2188 l_sysdate,
2189 l_userID,
2190 l_loginID,
2191 l_reqID,
2192 l_progApplID,
2193 l_progID,
2194 l_sysdate
2195 from wsm_copy_op_resource_usage wcoru,
2196 wsm_copy_op_resource_instances wcori
2197 where wcoru.wip_entity_id = wcori.wip_entity_id
2198 and wcoru.operation_seq_num = wcori.operation_seq_num
2199 and wcoru.resource_seq_num = wcori.resource_seq_num
2200 and wcoru.organization_id = wcori.organization_id
2201 and wcoru.wip_entity_id = p_wipEntityID
2202 and wcoru.organization_id = p_orgID
2203 and wcori.operation_seq_num = -x_resTbls.opSeqNum(l_cnt_wor)
2204 and wcori.resource_seq_num = x_resTbls.resSeqNum(l_cnt_wor);
2205 if(l_logLevel <= wip_constants.full_logging) then
2206 wip_logger.log('wrote ' || SQL%ROWCOUNT || ' WCORU for current op', l_retStatus);
2207 end if;
2208
2209 end if;
2210 --bug 6345672: end
2211
2212 end if;
2213
2214 if (l_logLevel <= wip_constants.trace_logging) then
2215 wip_logger.exitPoint(
2216 p_procName => 'wsm_infinite_scheduler_pvt.wsmJobWriter',
2217 p_procReturnStatus => x_returnStatus,
2218 p_msg => 'success',
2219 x_returnStatus => l_retStatus);
2220 end if;
2221
2222 exception
2223
2224 when others then
2225 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
2226 fnd_msg_pub.add_exc_msg(
2227 p_pkg_name => 'wsm_infinite_scheduler_pvt',
2228 p_procedure_name => 'wsmJobWriter',
2229 p_error_text => SQLERRM || ' (writer #' || l_stat_num || ')' );
2230 if (l_logLevel <= wip_constants.trace_logging) then
2231 wip_logger.exitPoint(
2232 p_procName => 'wsm_infinite_scheduler_pvt.wsmJobWriter',
2233 p_procReturnStatus => x_returnStatus,
2234 p_msg => 'unexp error: ' || SQLERRM || ' (writer #' || l_stat_num || ')',
2235 x_returnStatus => l_retStatus);
2236 end if;
2237 end wsmJobWriter;
2238
2239
2240 end wsm_infinite_scheduler_pvt;