[Home] [Help]
PACKAGE BODY: APPS.WIP_SCHEDULING
Source
1 PACKAGE BODY WIP_SCHEDULING AS
2 /* $Header: wipsildb.pls 115.8 2002/11/29 18:53:37 simishra ship $ */
3
4 PROCEDURE LOAD_INTERFACE (
5 P_WIP_ENTITY_ID NUMBER,
6 P_ORGANIZATION_ID NUMBER,
7 P_JOB_INTERFACE_GROUP_ID NUMBER,
8 P_GROUP_ID NUMBER,
9 P_SCHEDULING_LEVEL NUMBER) IS
10
11 BEGIN
12
13 IF P_JOB_INTERFACE_GROUP_ID IS NULL THEN
14
15 IF P_SCHEDULING_LEVEL = 1 THEN
16 INSERT INTO WIP_SCHEDULING_INTERFACE
17 (
18 interface_id,
19 group_id,
20 wip_entity_id,
21 organization_id,
22 operation_seq_num,
23 resource_seq_num,
24 scheduling_level,
25 operation_start_date,
26 operation_completion_date,
27 resource_start_date,
28 resource_completion_date,
29 process_phase,
30 process_status,
31 last_update_date,
32 last_updated_by,
33 last_update_login,
34 creation_date,
35 created_by,
36 request_id,
37 program_application_id,
38 program_id,
39 program_update_date
40 )
41 (SELECT WIP_INTERFACE_S.NEXTVAL,
42 P_GROUP_ID,
43 P_WIP_ENTITY_ID,
44 P_ORGANIZATION_ID,
45 OPERATION_SEQ_NUM,
46 NULL,
47 P_SCHEDULING_LEVEL,
48 FIRST_UNIT_START_DATE,
49 LAST_UNIT_COMPLETION_DATE,
50 NULL,
51 NULL,
52 1,
53 1,
54 SYSDATE,
55 FND_GLOBAL.USER_ID,
56 FND_GLOBAL.LOGIN_ID,
57 SYSDATE,
58 FND_GLOBAL.USER_ID,
59 FND_GLOBAL.CONC_REQUEST_ID,
60 FND_GLOBAL.PROG_APPL_ID,
61 FND_GLOBAL.CONC_PROGRAM_ID,
62 SYSDATE
63 FROM WIP_OPERATIONS
64 WHERE WIP_ENTITY_ID = P_WIP_ENTITY_ID
65 AND ORGANIZATION_ID = P_ORGANIZATION_ID);
66
67 ELSIF P_SCHEDULING_LEVEL = 2 THEN
68 INSERT INTO WIP_SCHEDULING_INTERFACE
69 (
70 interface_id,
71 group_id,
72 wip_entity_id,
73 organization_id,
74 operation_seq_num,
75 resource_seq_num,
76 scheduling_level,
77 operation_start_date,
78 operation_completion_date,
79 resource_start_date,
80 resource_completion_date,
81 usage_rate_or_amount,
82 process_phase,
83 process_status,
84 last_update_date,
85 last_updated_by,
86 last_update_login,
87 creation_date,
88 created_by,
89 request_id,
90 program_application_id,
91 program_id,
92 program_update_date
93 )
94 (SELECT WIP_INTERFACE_S.NEXTVAL,
95 P_GROUP_ID,
96 P_WIP_ENTITY_ID,
97 P_ORGANIZATION_ID,
98 OPERATION_SEQ_NUM,
99 RESOURCE_SEQ_NUM,
100 P_SCHEDULING_LEVEL,
101 NULL,
102 NULL,
103 START_DATE,
104 COMPLETION_DATE,
105 usage_rate_or_amount,
106 1,
107 1,
108 SYSDATE,
109 FND_GLOBAL.USER_ID,
110 FND_GLOBAL.LOGIN_ID,
111 SYSDATE,
112 FND_GLOBAL.USER_ID,
113 FND_GLOBAL.CONC_REQUEST_ID,
114 FND_GLOBAL.PROG_APPL_ID,
115 FND_GLOBAL.CONC_PROGRAM_ID,
116 SYSDATE
117 FROM WIP_OPERATION_RESOURCES
118 WHERE WIP_ENTITY_ID = P_WIP_ENTITY_ID
119 AND ORGANIZATION_ID = P_ORGANIZATION_ID);
120 END IF;
121
122 ELSE
123
124 IF P_SCHEDULING_LEVEL = 1 THEN
125 INSERT INTO WIP_SCHEDULING_INTERFACE
126 (
127 interface_id,
128 group_id,
129 wip_entity_id,
130 organization_id,
131 operation_seq_num,
132 resource_seq_num,
133 scheduling_level,
134 operation_start_date,
135 operation_completion_date,
136 resource_start_date,
137 resource_completion_date,
138 process_phase,
139 process_status,
140 last_update_date,
141 last_updated_by,
142 last_update_login,
143 creation_date,
144 created_by,
145 request_id,
146 program_application_id,
147 program_id,
148 program_update_date
149 )
150 (SELECT WIP_INTERFACE_S.NEXTVAL,
151 P_GROUP_ID,
152 WIP_ENTITY_ID,
153 ORGANIZATION_ID,
154 OPERATION_SEQ_NUM,
155 NULL,
156 P_SCHEDULING_LEVEL,
157 FIRST_UNIT_START_DATE,
158 LAST_UNIT_COMPLETION_DATE,
159 NULL,
160 NULL,
161 1,
162 1,
163 SYSDATE,
164 FND_GLOBAL.USER_ID,
165 FND_GLOBAL.LOGIN_ID,
166 SYSDATE,
167 FND_GLOBAL.USER_ID,
168 FND_GLOBAL.CONC_REQUEST_ID,
169 FND_GLOBAL.PROG_APPL_ID,
170 FND_GLOBAL.CONC_PROGRAM_ID,
171 SYSDATE
172 FROM WIP_OPERATIONS
173 WHERE (WIP_ENTITY_ID, ORGANIZATION_ID) IN
174 (SELECT WIP_ENTITY_ID,
175 ORGANIZATION_ID
176 FROM WIP_JOB_SCHEDULE_INTERFACE WJSI
177 WHERE WJSI.GROUP_ID = P_JOB_INTERFACE_GROUP_ID
178 AND WJSI.PROCESS_PHASE = WIP_CONSTANTS.ML_COMPLETE
179 AND WJSI.PROCESS_STATUS = WIP_CONSTANTS.ML_COMPLETE));
180
181 ELSIF P_SCHEDULING_LEVEL = 2 THEN
182 INSERT INTO WIP_SCHEDULING_INTERFACE
183 (
184 interface_id,
185 group_id,
186 wip_entity_id,
187 organization_id,
188 operation_seq_num,
189 resource_seq_num,
190 scheduling_level,
191 operation_start_date,
192 operation_completion_date,
193 resource_start_date,
194 resource_completion_date,
195 usage_rate_or_amount,
196 process_phase,
197 process_status,
198 last_update_date,
199 last_updated_by,
200 last_update_login,
201 creation_date,
202 created_by,
203 request_id,
204 program_application_id,
205 program_id,
206 program_update_date
207 )
208 (SELECT WIP_INTERFACE_S.NEXTVAL,
209 P_GROUP_ID,
210 WIP_ENTITY_ID,
211 ORGANIZATION_ID,
212 OPERATION_SEQ_NUM,
213 RESOURCE_SEQ_NUM,
214 P_SCHEDULING_LEVEL,
215 NULL,
216 NULL,
217 START_DATE,
218 COMPLETION_DATE,
219 USAGE_RATE_OR_AMOUNT,
220 1,
221 1,
222 SYSDATE,
223 FND_GLOBAL.USER_ID,
224 FND_GLOBAL.LOGIN_ID,
225 SYSDATE,
226 FND_GLOBAL.USER_ID,
227 FND_GLOBAL.CONC_REQUEST_ID,
228 FND_GLOBAL.PROG_APPL_ID,
229 FND_GLOBAL.CONC_PROGRAM_ID,
230 SYSDATE
231 FROM WIP_OPERATION_RESOURCES
232 WHERE (WIP_ENTITY_ID, ORGANIZATION_ID) IN
233 (SELECT WIP_ENTITY_ID,
234 ORGANIZATION_ID
235 FROM WIP_JOB_SCHEDULE_INTERFACE WJSI
236 WHERE WJSI.GROUP_ID = P_JOB_INTERFACE_GROUP_ID
237 AND WJSI.PROCESS_PHASE = WIP_CONSTANTS.ML_COMPLETE
238 AND WJSI.PROCESS_STATUS = WIP_CONSTANTS.ML_COMPLETE));
239 END IF;
240 END IF;
241
242 END LOAD_INTERFACE;
243
244 PROCEDURE LOAD_WIP(P_GROUP_ID NUMBER) IS
245
246 x_min_date DATE;
247 x_max_date DATE;
248
249 CURSOR CJOB IS
250 SELECT DISTINCT WIP_ENTITY_ID, ORGANIZATION_ID, SCHEDULING_LEVEL
251 FROM WIP_SCHEDULING_INTERFACE
252 WHERE GROUP_ID = P_GROUP_ID
253 AND PROCESS_PHASE = WIP_CONSTANTS.ML_VALIDATION
254 AND PROCESS_STATUS = WIP_CONSTANTS.RUNNING;
255
256 CURSOR COP(we_id number, org_id number) IS
257 SELECT OPERATION_SEQ_NUM
258 FROM WIP_OPERATIONS
259 WHERE WIP_ENTITY_ID = we_id
260 AND ORGANIZATION_ID = org_id;
261
262 BEGIN
263
264 UPDATE WIP_SCHEDULING_INTERFACE
265 SET PROCESS_STATUS = WIP_CONSTANTS.RUNNING
266 WHERE GROUP_ID = P_GROUP_ID
267 AND PROCESS_PHASE = WIP_CONSTANTS.ML_VALIDATION
268 AND PROCESS_STATUS = WIP_CONSTANTS.PENDING;
269
270 VALIDATE_JOBS(P_GROUP_ID);
271 VALIDATE_SCHEDULING_LEVEL(P_GROUP_ID);
272 VALIDATE_DATES(P_GROUP_ID);
273 VALIDATE_USAGE_RATE(P_GROUP_ID);
274 VALIDATE_OPS_RES_MATCH(P_GROUP_ID);
275
276 -- This must come after all validation
277 ERROR_ALL_IF_ANY(P_GROUP_ID);
278
279 FOR C IN CJOB LOOP
280
281 IF C.SCHEDULING_LEVEL = 1 THEN
282
283 UPDATE WIP_OPERATIONS WO
284 SET (WO.FIRST_UNIT_START_DATE,
285 WO.FIRST_UNIT_COMPLETION_DATE,
286 WO.LAST_UNIT_START_DATE,
287 WO.LAST_UNIT_COMPLETION_DATE,
288 WO.LAST_UPDATED_BY) =
289 (SELECT OPERATION_START_DATE,
290 OPERATION_COMPLETION_DATE,
291 OPERATION_START_DATE,
292 OPERATION_COMPLETION_DATE,
293 LAST_UPDATED_BY
294 FROM WIP_SCHEDULING_INTERFACE WSI
295 WHERE WSI.WIP_ENTITY_ID = C.WIP_ENTITY_ID
296 AND WSI.OPERATION_SEQ_NUM = WO.OPERATION_SEQ_NUM
297 AND WSI.GROUP_ID = P_GROUP_ID),
298 LAST_UPDATE_DATE = SYSDATE
299 WHERE WO.WIP_ENTITY_ID = C.WIP_ENTITY_ID
300 AND WO.ORGANIZATION_ID = C.ORGANIZATION_ID;
301
302 UPDATE WIP_OPERATION_RESOURCES WOR
303 SET (START_DATE,
304 COMPLETION_DATE,
305 LAST_UPDATE_DATE,
306 LAST_UPDATED_BY) =
307 (SELECT FIRST_UNIT_START_DATE,
308 LAST_UNIT_COMPLETION_DATE,
309 LAST_UPDATE_DATE,
310 LAST_UPDATED_BY
311 FROM WIP_OPERATIONS WO
312 WHERE WO.WIP_ENTITY_ID = WOR.WIP_ENTITY_ID
313 AND WO.ORGANIZATION_ID = WOR.ORGANIZATION_ID
314 AND WO.OPERATION_SEQ_NUM = WOR.OPERATION_SEQ_NUM)
315 WHERE WOR.WIP_ENTITY_ID = C.WIP_ENTITY_ID
316 AND WOR.ORGANIZATION_ID = C.ORGANIZATION_ID;
317
318 ELSIF C.SCHEDULING_LEVEL = 2 THEN
319
320 UPDATE WIP_OPERATION_RESOURCES WOR
321 SET (WOR.START_DATE,
322 WOR.COMPLETION_DATE,
323 WOR.USAGE_RATE_OR_AMOUNT,
324 WOR.LAST_UPDATED_BY) =
325 (SELECT RESOURCE_START_DATE,
326 RESOURCE_COMPLETION_DATE,
327 USAGE_RATE_OR_AMOUNT,
328 LAST_UPDATED_BY
329 FROM WIP_SCHEDULING_INTERFACE WSI
330 WHERE WSI.WIP_ENTITY_ID = C.WIP_ENTITY_ID
331 AND WSI.OPERATION_SEQ_NUM = WOR.OPERATION_SEQ_NUM
332 AND WSI.RESOURCE_SEQ_NUM = WOR.RESOURCE_SEQ_NUM
333 AND WSI.GROUP_ID = P_GROUP_ID),
334 LAST_UPDATE_DATE = SYSDATE
335 WHERE WOR.WIP_ENTITY_ID = C.WIP_ENTITY_ID
336 AND WOR.ORGANIZATION_ID = C.ORGANIZATION_ID;
337
338 FOR D IN COP(C.WIP_ENTITY_ID, C.ORGANIZATION_ID) LOOP
339
340 SELECT MIN(START_DATE), MAX(COMPLETION_DATE)
341 INTO x_min_date, x_max_date
342 FROM WIP_OPERATION_RESOURCES WOR
343 WHERE WOR.WIP_ENTITY_ID = C.WIP_ENTITY_ID
344 AND WOR.OPERATION_SEQ_NUM = D.OPERATION_SEQ_NUM
345 AND WOR.ORGANIZATION_ID = C.ORGANIZATION_ID;
346
347 UPDATE WIP_OPERATIONS WO
348 SET FIRST_UNIT_START_DATE = x_min_date,
349 LAST_UNIT_START_DATE = x_min_date,
350 FIRST_UNIT_COMPLETION_DATE = x_max_date,
351 LAST_UNIT_COMPLETION_DATE = x_max_date,
352 LAST_UPDATE_DATE = SYSDATE,
353 LAST_UPDATED_BY = 1
354 WHERE WO.WIP_ENTITY_ID = C.WIP_ENTITY_ID
355 AND WO.ORGANIZATION_ID = C.ORGANIZATION_ID
356 AND WO.OPERATION_SEQ_NUM = D.OPERATION_SEQ_NUM;
357
358 END LOOP;
359
360 END IF;
361
362 UPDATE_REQ_DATES(C.WIP_ENTITY_ID,
363 C.ORGANIZATION_ID);
364
365 UPDATE_JOB_DATES(C.WIP_ENTITY_ID,
366 C.ORGANIZATION_ID);
367
368 END LOOP;
369
370 UPDATE WIP_SCHEDULING_INTERFACE
371 SET PROCESS_STATUS = WIP_CONSTANTS.ML_COMPLETE,
372 PROCESS_PHASE = WIP_CONSTANTS.ML_COMPLETE
373 WHERE GROUP_ID = P_GROUP_ID
374 AND PROCESS_PHASE = WIP_CONSTANTS.ML_VALIDATION
375 AND PROCESS_STATUS = WIP_CONSTANTS.RUNNING;
376
377 DELETE FROM WIP_SCHEDULING_INTERFACE
378 WHERE GROUP_ID = P_GROUP_ID
379 AND PROCESS_STATUS = WIP_CONSTANTS.ML_COMPLETE
380 AND PROCESS_PHASE = WIP_CONSTANTS.ML_COMPLETE;
381
382 COMMIT;
383
384 END LOAD_WIP;
385
386
387 PROCEDURE LOAD_WIP_CONCURRENT( errbuf OUT NOCOPY VARCHAR2,
388 retcode OUT NOCOPY NUMBER,
389 p_group_id NUMBER) IS
390 recCount NUMBER;
391 BEGIN
392 -- checks if records are available in the interface table
393 SELECT count(*)
394 INTO recCount
395 FROM wip_scheduling_interface
396 WHERE group_id = p_group_id
397 and process_phase = WIP_CONSTANTS.ML_VALIDATION
398 and process_status = WIP_CONSTANTS.PENDING;
399
400 if (recCount = 0) then
401 retcode := 1;
402 errbuf := 'LOAD_WIP: No schedules available in group '
403 || p_group_id || '.
404 Please make sure data are available in WIP_SCHEDULING_INTERFACE with PROCESS_PHASE = 2 and PROCESS_STATUS = 1';
405
406 RETURN;
407 end if;
408
409
410 -- starts load_wip
411 WIP_SCHEDULING.LOAD_WIP(p_group_id);
412
413
414 -- checks if there are errors
415 SELECT count(*)
416 INTO recCount
417 FROM wip_scheduling_interface
418 WHERE group_id = p_group_id
419 and process_status = WIP_CONSTANTS.ERROR;
420
421 if (recCount > 0) then
422 retcode := 2;
423 errbuf := 'LOAD_WIP: Error occurred in loading interface. Please refer to WIP_INTERFACE_ERRORS for more information.';
424
425 end if;
426
427
428 EXCEPTION
429 WHEN others then
430 errbuf := 'LOAD_WIP: Encountered Oracle errors. '
431 || substr(sqlerrm, 1, 100);
432 retcode := 2;
433
434 END LOAD_WIP_CONCURRENT;
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454 PROCEDURE VALIDATE_JOBS(P_GROUP_ID NUMBER) IS
455 BEGIN
456
457 -- Jobs must exist and be status 1,3,4,6
458
459 WIP_MASS_LOAD_UTILITIES.Error
460 (P_Group_Id,
461 'WIP_SCHEDULING_INTERFACE WSI',
462 WIP_CONSTANTS.ERROR,
463 ' NOT EXISTS
464 (SELECT 1
465 FROM WIP_DISCRETE_JOBS WDJ
466 WHERE WDJ.WIP_ENTITY_ID = WSI.WIP_ENTITY_ID
467 AND WDJ.ORGANIZATION_ID = WSI.ORGANIZATION_ID
468 AND WDJ.STATUS_TYPE IN (1,3,4,6)) ',
469 WIP_MASS_LOAD_UTILITIES.MSG_ERROR,
470 'WIP_SI_INVALID_JOB');
471
472 END VALIDATE_JOBS;
473
474 PROCEDURE VALIDATE_SCHEDULING_LEVEL(P_GROUP_ID NUMBER) IS
475 BEGIN
476
477 -- Scheduling level must be 1 (operation) or 2 (resource)
478
479 WIP_MASS_LOAD_UTILITIES.Error
480 (P_Group_Id,
481 'WIP_SCHEDULING_INTERFACE WSI',
482 WIP_CONSTANTS.ERROR,
483 ' SCHEDULING_LEVEL NOT IN (1,2) ',
484 WIP_MASS_LOAD_UTILITIES.MSG_ERROR,
485 'WIP_SI_INVALID_SCHED_LEVEL');
486
487 END VALIDATE_SCHEDULING_LEVEL;
488
489 PROCEDURE VALIDATE_USAGE_RATE(P_GROUP_ID NUMBER) IS
490 BEGIN
491
492 -- Give Error if dates are null or if start date > comp date
493
494 WIP_MASS_LOAD_UTILITIES.Error
495 (P_Group_Id,
496 'WIP_SCHEDULING_INTERFACE WSI',
497 WIP_CONSTANTS.ERROR,
498 ' ((WSI.SCHEDULING_LEVEL = 2
499 AND WSI.USAGE_RATE_OR_AMOUNT < 0)) ',
500 WIP_MASS_LOAD_UTILITIES.MSG_ERROR,
501 'WIP_SI_INVALID_RATE');
502
503 END VALIDATE_USAGE_RATE;
504
505 PROCEDURE VALIDATE_DATES(P_GROUP_ID NUMBER) IS
506 BEGIN
507
508 -- Give Error if dates are null or if start date > comp date
509
510 WIP_MASS_LOAD_UTILITIES.Error
511 (P_Group_Id,
512 'WIP_SCHEDULING_INTERFACE WSI',
513 WIP_CONSTANTS.ERROR,
514 ' ((WSI.SCHEDULING_LEVEL = 1
515 AND( (WSI.OPERATION_COMPLETION_DATE IS NULL
516 OR WSI.OPERATION_START_DATE IS NULL)
517 OR (WSI.OPERATION_START_DATE >
518 WSI.OPERATION_COMPLETION_DATE)))
519 OR (WSI.SCHEDULING_LEVEL = 2
520 AND( (WSI.RESOURCE_COMPLETION_DATE IS NULL
521 OR WSI.RESOURCE_START_DATE IS NULL)
522 OR (WSI.RESOURCE_START_DATE >
523 WSI.RESOURCE_COMPLETION_DATE)))) ',
524 WIP_MASS_LOAD_UTILITIES.MSG_ERROR,
525 'WIP_SI_INVALID_DATES');
526
527 END VALIDATE_DATES;
528
529 PROCEDURE VALIDATE_OPS_RES_MATCH
530 (P_GROUP_ID NUMBER) IS
531 BEGIN
532 -- Need to worry about erroring out 1 ==> erroring out all
533 WIP_MASS_LOAD_UTILITIES.Error
534 (P_Group_Id,
535 'WIP_SCHEDULING_INTERFACE WSI',
536 WIP_CONSTANTS.ERROR,
537 ' WSI.SCHEDULING_LEVEL = 1
538 AND EXISTS (
539 SELECT OPERATION_SEQ_NUM
540 FROM WIP_SCHEDULING_INTERFACE SI2
541 WHERE SI2.GROUP_ID = WSI.GROUP_ID
542 AND SI2.WIP_ENTITY_ID = WSI.WIP_ENTITY_ID
543 AND SI2.ORGANIZATION_ID = WSI.ORGANIZATION_ID
544 AND SI2.PROCESS_PHASE = 2
545 AND SI2.PROCESS_STATUS = 2
546 MINUS
547 SELECT OPERATION_SEQ_NUM
548 FROM WIP_OPERATIONS WO
549 WHERE WO.WIP_ENTITY_ID = WSI.WIP_ENTITY_ID
550 AND WO.ORGANIZATION_ID = WSI.ORGANIZATION_ID) ',
551 WIP_MASS_LOAD_UTILITIES.MSG_ERROR,
552 'WIP_SI_INVALID_OPS');
553
554 WIP_MASS_LOAD_UTILITIES.Error
555 (P_Group_Id,
556 'WIP_SCHEDULING_INTERFACE WSI',
557 WIP_CONSTANTS.ERROR,
558 ' WSI.SCHEDULING_LEVEL = 1
559 AND EXISTS (
560 SELECT OPERATION_SEQ_NUM
561 FROM WIP_OPERATIONS WO
562 WHERE WO.WIP_ENTITY_ID = WSI.WIP_ENTITY_ID
563 AND WO.ORGANIZATION_ID = WSI.ORGANIZATION_ID
564 MINUS
565 SELECT OPERATION_SEQ_NUM
566 FROM WIP_SCHEDULING_INTERFACE SI2
567 WHERE GROUP_ID = WSI.GROUP_ID
568 AND SI2.WIP_ENTITY_ID = WSI.WIP_ENTITY_ID
569 AND SI2.ORGANIZATION_ID = WSI.ORGANIZATION_ID
570 AND SI2.PROCESS_PHASE = 2
571 AND SI2.PROCESS_STATUS = 2) ',
572 WIP_MASS_LOAD_UTILITIES.MSG_ERROR,
573 'WIP_SI_INVALID_OPS');
574
575 WIP_MASS_LOAD_UTILITIES.Error
576 (P_Group_Id,
577 'WIP_SCHEDULING_INTERFACE WSI',
578 WIP_CONSTANTS.ERROR,
579 ' WSI.SCHEDULING_LEVEL = 2
580 AND EXISTS (
581 SELECT OPERATION_SEQ_NUM, RESOURCE_SEQ_NUM
582 FROM WIP_SCHEDULING_INTERFACE SI2
583 WHERE GROUP_ID = WSI.GROUP_ID
584 AND SI2.WIP_ENTITY_ID = WSI.WIP_ENTITY_ID
585 AND SI2.ORGANIZATION_ID = WSI.ORGANIZATION_ID
586 AND SI2.PROCESS_PHASE = 2
587 AND SI2.PROCESS_STATUS = 2
588 MINUS
589 SELECT OPERATION_SEQ_NUM, RESOURCE_SEQ_NUM
590 FROM WIP_OPERATION_RESOURCES WOR
591 WHERE WOR.WIP_ENTITY_ID = WSI.WIP_ENTITY_ID
592 AND WOR.ORGANIZATION_ID = WSI.ORGANIZATION_ID
593 ) ',
594 WIP_MASS_LOAD_UTILITIES.MSG_ERROR,
595 'WIP_SI_INVALID_RES');
596
597 WIP_MASS_LOAD_UTILITIES.Error
598 (P_Group_Id,
599 'WIP_SCHEDULING_INTERFACE WSI',
600 WIP_CONSTANTS.ERROR,
601 ' WSI.SCHEDULING_LEVEL = 2
602 AND EXISTS (
603 SELECT OPERATION_SEQ_NUM, RESOURCE_SEQ_NUM
604 FROM WIP_OPERATION_RESOURCES WOR
605 WHERE WOR.WIP_ENTITY_ID = WSI.WIP_ENTITY_ID
606 AND WOR.ORGANIZATION_ID = WSI.ORGANIZATION_ID
607 MINUS
608 SELECT OPERATION_SEQ_NUM, RESOURCE_SEQ_NUM
609 FROM WIP_SCHEDULING_INTERFACE SI2
610 WHERE GROUP_ID = WSI.GROUP_ID
611 AND SI2.WIP_ENTITY_ID = WSI.WIP_ENTITY_ID
612 AND SI2.ORGANIZATION_ID = WSI.ORGANIZATION_ID
613 AND SI2.PROCESS_PHASE = 2
614 AND SI2.PROCESS_STATUS = 2
615 )',
616 WIP_MASS_LOAD_UTILITIES.MSG_ERROR,
617 'WIP_SI_INVALID_RES');
618
619 END VALIDATE_OPS_RES_MATCH;
620
621 /* If any record for a job is invalid, we must error out ALL records for that job */
622 PROCEDURE ERROR_ALL_IF_ANY
623 (P_GROUP_ID NUMBER) IS
624 BEGIN
625 WIP_MASS_LOAD_UTILITIES.Error
626 (P_Group_Id,
627 'WIP_SCHEDULING_INTERFACE WSI',
628 WIP_CONSTANTS.ERROR,
629 ' EXISTS (SELECT 1
630 FROM WIP_SCHEDULING_INTERFACE WSI2
631 WHERE WSI2.GROUP_ID = WSI.GROUP_ID
632 AND WSI2.WIP_ENTITY_ID = WSI.WIP_ENTITY_ID
633 AND WSI2.PROCESS_STATUS = 3) ',
634 WIP_MASS_LOAD_UTILITIES.MSG_ERROR,
635 'WIP_SI_OTHERS_FAILED');
636 END ERROR_ALL_IF_ANY;
637
638 PROCEDURE UPDATE_REQ_DATES(P_WIP_ENTITY_ID NUMBER,
639 P_ORGANIZATION_ID NUMBER) IS
640 BEGIN
641
642 UPDATE WIP_REQUIREMENT_OPERATIONS WRO
643 SET (DATE_REQUIRED,
644 LAST_UPDATE_DATE,
645 LAST_UPDATED_BY) =
646 (SELECT FIRST_UNIT_START_DATE,
647 LAST_UPDATE_DATE,
648 LAST_UPDATED_BY
649 FROM WIP_OPERATIONS WO
650 WHERE WO.WIP_ENTITY_ID = WRO.WIP_ENTITY_ID
651 AND WO.ORGANIZATION_ID = WRO.ORGANIZATION_ID
652 AND WO.OPERATION_SEQ_NUM = ABS(WRO.OPERATION_SEQ_NUM))
653 WHERE WRO.WIP_ENTITY_ID = P_WIP_ENTITY_ID
654 AND WRO.ORGANIZATION_ID = P_ORGANIZATION_ID;
655
656 END UPDATE_REQ_DATES;
657
658 PROCEDURE UPDATE_JOB_DATES(P_WIP_ENTITY_ID NUMBER,
659 P_ORGANIZATION_ID NUMBER) IS
660 BEGIN
661 UPDATE WIP_DISCRETE_JOBS WDJ
662 SET SCHEDULED_START_DATE =
663 (SELECT MIN(FIRST_UNIT_START_DATE)
664 FROM WIP_OPERATIONS WO
665 WHERE WO.WIP_ENTITY_ID = WDJ.WIP_ENTITY_ID
666 AND WO.ORGANIZATION_ID = WDJ.ORGANIZATION_ID),
667 SCHEDULED_COMPLETION_DATE =
668 (SELECT MAX(LAST_UNIT_COMPLETION_DATE)
669 FROM WIP_OPERATIONS WO
670 WHERE WO.WIP_ENTITY_ID = WDJ.WIP_ENTITY_ID
671 AND WO.ORGANIZATION_ID = WDJ.ORGANIZATION_ID),
672 (LAST_UPDATE_DATE,
673 LAST_UPDATED_BY) =
674 (SELECT LAST_UPDATE_DATE,
675 LAST_UPDATED_BY
676 FROM WIP_OPERATIONS WO
677 WHERE WO.WIP_ENTITY_ID = WDJ.WIP_ENTITY_ID
678 AND WO.ORGANIZATION_ID = WDJ.ORGANIZATION_ID
679 AND WO.PREVIOUS_OPERATION_SEQ_NUM IS NULL)
680 WHERE WDJ.WIP_ENTITY_ID = P_WIP_ENTITY_ID
681 AND WDJ.ORGANIZATION_ID = P_ORGANIZATION_ID;
682
683 END UPDATE_JOB_DATES;
684
685 END WIP_SCHEDULING;