[Home] [Help]
PACKAGE BODY: APPS.WIP_SCHEDULER
Source
1 PACKAGE BODY WIP_SCHEDULER AS
2 /* $Header: wipschdb.pls 115.28 2003/09/05 22:10:30 kbavadek ship $ */
3
4 PROCEDURE explode_routing(
5 X_Wip_Entity_Id NUMBER,
6 X_Organization_Id NUMBER,
7 X_Repetitive_Schedule_Id NUMBER,
8 X_Start_Date VARCHAR2,
9 X_Completion_Date VARCHAR2,
10 X_Routing_Seq NUMBER,
11 X_Routing_Rev_Date VARCHAR2,
12 X_Quantity NUMBER,
13 X_Created_By NUMBER,
14 X_Last_Update_Login NUMBER) IS
15
16
17 p_req_id NUMBER := FND_GLOBAL.CONC_REQUEST_ID;
18 p_appl_id NUMBER := FND_GLOBAL.PROG_APPL_ID;
19 p_prog_id NUMBER := FND_GLOBAL.CONC_PROGRAM_ID;
20 p_curdate DATE := SYSDATE;
21 p_user_id NUMBER := FND_GLOBAL.USER_ID;
22 p_login_id NUMBER := FND_GLOBAL.LOGIN_ID;
23 max_seq NUMBER;
24
25
26 CURSOR wip_op_inst IS
27 SELECT ops.operation_seq_num,
28 ops.operation_sequence_id
29 FROM wip_operations ops
30 WHERE NVL(ops.repetitive_schedule_id, -1) =
31 NVL(X_Repetitive_Schedule_Id, -1)
32 AND ops.organization_id = X_Organization_Id
33 AND ops.wip_entity_id = X_Wip_Entity_Id
34 AND EXISTS
35 (SELECT fnd.pk1_value
36 FROM fnd_attached_documents fnd
37 WHERE fnd.pk1_value = to_char(ops.operation_sequence_id)
38 AND fnd.entity_name = 'BOM_OPERATION_SEQUENCES');
39 l_params wip_logger.param_tbl_t;
40 l_returnStatus VARCHAR(1);
41 l_logLevel NUMBER := to_number(fnd_log.g_current_runtime_level);
42 BEGIN
43 if (l_logLevel <= wip_constants.trace_logging) then
44 l_params(1).paramName := 'wip_id';
45 l_params(1).paramValue := X_Wip_Entity_Id;
46 l_params(2).paramName := 'orgID';
47 l_params(2).paramValue := X_Organization_Id ;
48 l_params(3).paramName := 'start_date';
49 l_params(3).paramValue := X_Start_Date;
50 l_params(4).paramName := 'routing_seq';
51 l_params(4).paramValue := X_Routing_Seq ;
52 l_params(5).paramName := 'routing_rev_date';
53 l_params(5).paramValue := X_Routing_Rev_Date;
54 -- write parameter value to log file
55 wip_logger.entryPoint(p_procName =>'wip_scheduler.explode_routing',
56 p_params => l_params,
57 x_returnStatus => l_returnStatus);
58 end if;
59
60 /* This is all extracted froom wiloer.ppc */
61
62 INSERT INTO WIP_OPERATIONS
63 (WIP_ENTITY_ID,
64 OPERATION_SEQ_NUM,
65 ORGANIZATION_ID,
66 REPETITIVE_SCHEDULE_ID,
67 LAST_UPDATE_DATE,
68 LAST_UPDATED_BY,
69 CREATION_DATE,
70 CREATED_BY,
71 LAST_UPDATE_LOGIN,
72 REQUEST_ID,
73 PROGRAM_APPLICATION_ID,
74 PROGRAM_ID,
75 PROGRAM_UPDATE_DATE,
76 OPERATION_SEQUENCE_ID,
77 STANDARD_OPERATION_ID,
78 DEPARTMENT_ID,
79 DESCRIPTION,
80 SCHEDULED_QUANTITY,
81 QUANTITY_IN_QUEUE,
82 QUANTITY_RUNNING,
83 QUANTITY_WAITING_TO_MOVE,
84 QUANTITY_REJECTED,
85 QUANTITY_SCRAPPED,
86 QUANTITY_COMPLETED,
87 CUMULATIVE_SCRAP_QUANTITY, /* For enhancement #2864382*/
88 FIRST_UNIT_START_DATE,
89 FIRST_UNIT_COMPLETION_DATE,
90 LAST_UNIT_START_DATE,
91 LAST_UNIT_COMPLETION_DATE,
92 PREVIOUS_OPERATION_SEQ_NUM,
93 NEXT_OPERATION_SEQ_NUM,
94 COUNT_POINT_TYPE,
95 BACKFLUSH_FLAG,
96 MINIMUM_TRANSFER_QUANTITY,
97 DATE_LAST_MOVED,
98 LONG_DESCRIPTION,
99 ATTRIBUTE_CATEGORY,
100 ATTRIBUTE1,
101 ATTRIBUTE2,
102 ATTRIBUTE3,
103 ATTRIBUTE4,
104 ATTRIBUTE5,
105 ATTRIBUTE6,
106 ATTRIBUTE7,
107 ATTRIBUTE8,
108 ATTRIBUTE9,
109 ATTRIBUTE10,
110 ATTRIBUTE11,
111 ATTRIBUTE12,
112 ATTRIBUTE13,
113 ATTRIBUTE14,
114 ATTRIBUTE15)
115 SELECT X_Wip_Entity_Id,
116 SEQ.OPERATION_SEQ_NUM,
117 X_Organization_Id,
118 X_Repetitive_Schedule_Id,
119 p_curdate,
120 X_Created_By,
121 p_curdate,
122 X_Created_By,
123 X_Last_Update_Login,
124 DECODE(p_req_id, 0, '', p_req_id),
125 DECODE(p_appl_id, 0, '', p_appl_id),
126 DECODE(p_prog_id, 0, '', p_prog_id),
127 DECODE(p_prog_id, 0, '', p_curdate),
128 SEQ.OPERATION_SEQUENCE_ID,
129 SEQ.STANDARD_OPERATION_ID,
130 SEQ.DEPARTMENT_ID,
131 SEQ.OPERATION_DESCRIPTION,
132 ROUND(X_Quantity, WIP_CONSTANTS.MAX_DISPLAYED_PRECISION),
133 0, 0, 0, 0, 0, 0, 0,
134 TO_DATE(X_Start_Date, WIP_CONSTANTS.DT_NOSEC_FMT),
135 TO_DATE(X_Completion_Date, WIP_CONSTANTS.DT_NOSEC_FMT),
136 TO_DATE(X_Start_Date, WIP_CONSTANTS.DT_NOSEC_FMT),
137 TO_DATE(X_Completion_Date, WIP_CONSTANTS.DT_NOSEC_FMT),
138 0, 0,
139 SEQ.COUNT_POINT_TYPE,
140 SEQ.BACKFLUSH_FLAG,
141 NVL(SEQ.MINIMUM_TRANSFER_QUANTITY, 0),
142 '',
143 SEQ.LONG_DESCRIPTION,
144 SEQ.ATTRIBUTE_CATEGORY,
145 SEQ.ATTRIBUTE1,
146 SEQ.ATTRIBUTE2,
147 SEQ.ATTRIBUTE3,
148 SEQ.ATTRIBUTE4,
149 SEQ.ATTRIBUTE5,
150 SEQ.ATTRIBUTE6,
151 SEQ.ATTRIBUTE7,
152 SEQ.ATTRIBUTE8,
153 SEQ.ATTRIBUTE9,
154 SEQ.ATTRIBUTE10,
155 SEQ.ATTRIBUTE11,
156 SEQ.ATTRIBUTE12,
157 SEQ.ATTRIBUTE13,
158 SEQ.ATTRIBUTE14,
159 SEQ.ATTRIBUTE15
160 FROM BOM_OPERATION_SEQUENCES SEQ
161 WHERE SEQ.ROUTING_SEQUENCE_ID = X_Routing_Seq
162 /* %cfm */
163 AND NVL(SEQ.OPERATION_TYPE, 1) = 1
164 /* %/cfm */
165 AND TO_DATE(TO_CHAR(SEQ.EFFECTIVITY_DATE, WIP_CONSTANTS.DATETIME_FMT),
166 WIP_CONSTANTS.DATETIME_FMT) <=
167 TO_DATE(X_Routing_Rev_Date,WIP_CONSTANTS.DATETIME_FMT)
168 AND NVL(TO_DATE(TO_CHAR(SEQ.DISABLE_DATE,
169 WIP_CONSTANTS.DATETIME_FMT),
170 WIP_CONSTANTS.DATETIME_FMT),
171 TO_DATE(X_Routing_Rev_Date,WIP_CONSTANTS.DATETIME_FMT) + 2) >=
172 TO_DATE(X_Routing_Rev_Date,WIP_CONSTANTS.DATETIME_FMT)
173 AND SEQ.IMPLEMENTATION_DATE IS NOT NULL;
174
175 UPDATE WIP_OPERATIONS WO
176 SET WO.PREVIOUS_OPERATION_SEQ_NUM =
177 (SELECT MAX(OPERATION_SEQ_NUM)
178 FROM WIP_OPERATIONS
179 WHERE WIP_ENTITY_ID = X_Wip_Entity_Id
180 AND ORGANIZATION_ID = X_Organization_Id
181 AND NVL(REPETITIVE_SCHEDULE_ID, -1) =
182 NVL(X_Repetitive_Schedule_Id, -1)
183 AND OPERATION_SEQ_NUM < WO.OPERATION_SEQ_NUM),
184 WO.NEXT_OPERATION_SEQ_NUM =
185 (SELECT MIN(OPERATION_SEQ_NUM)
186 FROM WIP_OPERATIONS
187 WHERE WIP_ENTITY_ID = X_Wip_Entity_Id
188 AND ORGANIZATION_ID = X_Organization_Id
189 AND NVL(REPETITIVE_SCHEDULE_ID, -1) =
190 NVL(X_Repetitive_Schedule_Id, -1)
191 AND OPERATION_SEQ_NUM > WO.OPERATION_SEQ_NUM)
192 WHERE WO.WIP_ENTITY_ID = X_Wip_Entity_Id
193 AND WO.ORGANIZATION_ID = X_Organization_Id
194 AND NVL(WO.REPETITIVE_SCHEDULE_ID, -1) =
195 NVL(X_Repetitive_Schedule_Id, -1);
196
197 INSERT INTO WIP_OPERATION_RESOURCES
198 (WIP_ENTITY_ID, OPERATION_SEQ_NUM, RESOURCE_SEQ_NUM,
199 ORGANIZATION_ID, REPETITIVE_SCHEDULE_ID,
200 LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE,
201 CREATED_BY, LAST_UPDATE_LOGIN, REQUEST_ID,
202 PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE,
203 RESOURCE_ID, UOM_CODE,
204 BASIS_TYPE, USAGE_RATE_OR_AMOUNT, ACTIVITY_ID,
205 SCHEDULED_FLAG, ASSIGNED_UNITS, AUTOCHARGE_TYPE,
206 STANDARD_RATE_FLAG, APPLIED_RESOURCE_UNITS, APPLIED_RESOURCE_VALUE,
207 START_DATE, COMPLETION_DATE,
208 ATTRIBUTE_CATEGORY, ATTRIBUTE1, ATTRIBUTE2,
209 ATTRIBUTE3, ATTRIBUTE4, ATTRIBUTE5,
210 ATTRIBUTE6, ATTRIBUTE7, ATTRIBUTE8,
211 ATTRIBUTE9, ATTRIBUTE10, ATTRIBUTE11,
212 ATTRIBUTE12, ATTRIBUTE13, ATTRIBUTE14,
213 ATTRIBUTE15, SCHEDULE_SEQ_NUM, SUBSTITUTE_GROUP_NUM,
214 REPLACEMENT_GROUP_NUM, PRINCIPLE_FLAG, SETUP_ID)
215 SELECT OPS.WIP_ENTITY_ID, OPS.OPERATION_SEQ_NUM, ORS.RESOURCE_SEQ_NUM,
216 OPS.ORGANIZATION_ID, OPS.REPETITIVE_SCHEDULE_ID,
217 OPS.LAST_UPDATE_DATE, OPS.LAST_UPDATED_BY, OPS.CREATION_DATE,
218 OPS.CREATED_BY, OPS.LAST_UPDATE_LOGIN, OPS.REQUEST_ID,
219 OPS.PROGRAM_APPLICATION_ID, OPS.PROGRAM_ID,
220 OPS.PROGRAM_UPDATE_DATE, ORS.RESOURCE_ID, RSC.UNIT_OF_MEASURE,
221 ORS.BASIS_TYPE, ORS.USAGE_RATE_OR_AMOUNT, ORS.ACTIVITY_ID,
222 ORS.SCHEDULE_FLAG, ORS.ASSIGNED_UNITS, ORS.AUTOCHARGE_TYPE,
223 ORS.STANDARD_RATE_FLAG, 0, 0,
224 OPS.FIRST_UNIT_START_DATE, OPS.LAST_UNIT_COMPLETION_DATE,
225 ORS.ATTRIBUTE_CATEGORY, ORS.ATTRIBUTE1, ORS.ATTRIBUTE2,
226 ORS.ATTRIBUTE3, ORS.ATTRIBUTE4, ORS.ATTRIBUTE5,
227 ORS.ATTRIBUTE6, ORS.ATTRIBUTE7, ORS.ATTRIBUTE8,
228 ORS.ATTRIBUTE9, ORS.ATTRIBUTE10, ORS.ATTRIBUTE11,
229 ORS.ATTRIBUTE12, ORS.ATTRIBUTE13, ORS.ATTRIBUTE14,
230 ORS.ATTRIBUTE15, ORS.SCHEDULE_SEQ_NUM,
231 ORS.SUBSTITUTE_GROUP_NUM, 0, ORS.PRINCIPLE_FLAG, ORS.SETUP_ID
232 FROM BOM_RESOURCES RSC,
233 BOM_OPERATION_RESOURCES ORS,
234 WIP_OPERATIONS OPS
235 WHERE OPS.ORGANIZATION_ID = X_Organization_Id
236 AND OPS.WIP_ENTITY_ID = X_Wip_Entity_Id
237 AND NVL(OPS.REPETITIVE_SCHEDULE_ID, -1) =
238 NVL(X_Repetitive_Schedule_Id, -1)
239 AND OPS.OPERATION_SEQUENCE_ID = ORS.OPERATION_SEQUENCE_ID
240 AND ORS.RESOURCE_ID = RSC.RESOURCE_ID
241 AND RSC.ORGANIZATION_ID = OPS.ORGANIZATION_ID
242 AND (ORS.ACD_TYPE IS NULL OR ORS.ACD_TYPE <> 3);
243
244 SELECT MAX(resource_seq_num)
245 INTO max_seq
246 FROM WIP_OPERATION_RESOURCES
247 WHERE organization_id = X_Organization_Id and
248 wip_entity_id = X_Wip_Entity_Id;
249
250 INSERT INTO WIP_SUB_OPERATION_RESOURCES
251 (WIP_ENTITY_ID, OPERATION_SEQ_NUM, RESOURCE_SEQ_NUM,
252 ORGANIZATION_ID, REPETITIVE_SCHEDULE_ID,
253 LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE,
254 CREATED_BY, LAST_UPDATE_LOGIN, REQUEST_ID,
255 PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE,
256 RESOURCE_ID, UOM_CODE,
257 BASIS_TYPE, USAGE_RATE_OR_AMOUNT, ACTIVITY_ID,
258 SCHEDULED_FLAG, ASSIGNED_UNITS, AUTOCHARGE_TYPE,
259 STANDARD_RATE_FLAG, APPLIED_RESOURCE_UNITS, APPLIED_RESOURCE_VALUE,
260 START_DATE, COMPLETION_DATE,
261 ATTRIBUTE_CATEGORY, ATTRIBUTE1, ATTRIBUTE2,
262 ATTRIBUTE3, ATTRIBUTE4, ATTRIBUTE5,
263 ATTRIBUTE6, ATTRIBUTE7, ATTRIBUTE8,
264 ATTRIBUTE9, ATTRIBUTE10, ATTRIBUTE11,
265 ATTRIBUTE12, ATTRIBUTE13, ATTRIBUTE14,
266 ATTRIBUTE15, SCHEDULE_SEQ_NUM, SUBSTITUTE_GROUP_NUM,
267 REPLACEMENT_GROUP_NUM, PRINCIPLE_FLAG)
268 SELECT OPS.WIP_ENTITY_ID, OPS.OPERATION_SEQ_NUM,
269 max_seq + ROWNUM,
270 OPS.ORGANIZATION_ID, OPS.REPETITIVE_SCHEDULE_ID,
271 OPS.LAST_UPDATE_DATE, OPS.LAST_UPDATED_BY, OPS.CREATION_DATE,
272 OPS.CREATED_BY, OPS.LAST_UPDATE_LOGIN, OPS.REQUEST_ID,
273 OPS.PROGRAM_APPLICATION_ID, OPS.PROGRAM_ID,
274 OPS.PROGRAM_UPDATE_DATE, SORS.RESOURCE_ID, RSC.UNIT_OF_MEASURE,
275 SORS.BASIS_TYPE, SORS.USAGE_RATE_OR_AMOUNT, SORS.ACTIVITY_ID,
276 SORS.SCHEDULE_FLAG, SORS.ASSIGNED_UNITS, SORS.AUTOCHARGE_TYPE,
277 SORS.STANDARD_RATE_FLAG, 0, 0,
278 OPS.FIRST_UNIT_START_DATE, OPS.LAST_UNIT_COMPLETION_DATE,
279 SORS.ATTRIBUTE_CATEGORY, SORS.ATTRIBUTE1, SORS.ATTRIBUTE2,
280 SORS.ATTRIBUTE3, SORS.ATTRIBUTE4, SORS.ATTRIBUTE5,
281 SORS.ATTRIBUTE6, SORS.ATTRIBUTE7, SORS.ATTRIBUTE8,
282 SORS.ATTRIBUTE9, SORS.ATTRIBUTE10, SORS.ATTRIBUTE11,
283 SORS.ATTRIBUTE12, SORS.ATTRIBUTE13, SORS.ATTRIBUTE14,
284 SORS.ATTRIBUTE15, SORS.SCHEDULE_SEQ_NUM,
285 SORS.SUBSTITUTE_GROUP_NUM, SORS.REPLACEMENT_GROUP_NUM,
286 SORS.PRINCIPLE_FLAG
287 FROM BOM_RESOURCES RSC,
288 BOM_SUB_OPERATION_RESOURCES SORS,
289 WIP_OPERATIONS OPS
290 WHERE OPS.ORGANIZATION_ID = X_Organization_Id
291 AND OPS.WIP_ENTITY_ID = X_Wip_Entity_Id
292 AND NVL(OPS.REPETITIVE_SCHEDULE_ID, -1) =
293 NVL(X_Repetitive_Schedule_Id, -1)
294 AND OPS.OPERATION_SEQUENCE_ID = SORS.OPERATION_SEQUENCE_ID
295 AND SORS.RESOURCE_ID = RSC.RESOURCE_ID
296 AND RSC.ORGANIZATION_ID = OPS.ORGANIZATION_ID
297 AND (SORS.ACD_TYPE IS NULL OR SORS.ACD_TYPE <> 3);
298
299 FOR cur_rec IN wip_op_inst LOOP
300 IF (X_Repetitive_Schedule_Id IS NULL) THEN
301 FND_ATTACHED_DOCUMENTS2_PKG.copy_attachments(
302 X_FROM_ENTITY_NAME => 'BOM_OPERATION_SEQUENCES',
303 X_FROM_PK1_VALUE => to_char(cur_rec.operation_sequence_id),
304 X_TO_ENTITY_NAME => 'WIP_DISCRETE_OPERATIONS',
305 X_TO_PK1_VALUE => to_char(X_Wip_Entity_Id),
306 X_TO_PK2_VALUE => to_char(cur_rec.operation_seq_num),
307 X_TO_PK3_VALUE => to_char(X_Organization_Id),
308 X_CREATED_BY => p_user_id,
309 X_LAST_UPDATE_LOGIN => p_login_id,
310 X_PROGRAM_APPLICATION_ID => p_appl_id,
311 X_PROGRAM_ID => p_prog_id,
312 X_REQUEST_ID => p_req_id);
313
314 ELSE
315 FND_ATTACHED_DOCUMENTS2_PKG.copy_attachments(
316 X_FROM_ENTITY_NAME => 'BOM_OPERATION_SEQUENCES',
317 X_FROM_PK1_VALUE => to_char(cur_rec.operation_sequence_id),
318 X_TO_ENTITY_NAME => 'WIP_REPETITIVE_OPERATIONS',
319 X_TO_PK1_VALUE => to_char(X_Wip_Entity_Id),
320 X_TO_PK2_VALUE => to_char(cur_rec.operation_seq_num),
321 X_TO_PK3_VALUE => to_char(X_Organization_Id),
322 X_TO_PK4_VALUE => to_char(X_Repetitive_Schedule_Id),
323 X_CREATED_BY => p_user_id,
324 X_LAST_UPDATE_LOGIN => p_login_id,
325 X_PROGRAM_APPLICATION_ID => p_appl_id,
326 X_PROGRAM_ID => p_prog_id,
327 X_REQUEST_ID => p_req_id);
328
329 END IF;
330 END LOOP;
331 if (l_logLevel <= wip_constants.trace_logging) then
332 wip_logger.exitPoint(p_procName => 'wip_scheduler.explode_routing',
333 p_procReturnStatus => 'S',
334 p_msg => 'procedure complete',
335 x_returnStatus => l_returnStatus);
336 end if;
337 END explode_routing;
338
339 PROCEDURE update_routing(
340 X_Wip_Entity_Id NUMBER,
341 X_load_type NUMBER,
342 X_Organization_Id NUMBER,
343 X_Repetitive_Schedule_Id NUMBER,
344 X_Start_Date VARCHAR2,
345 X_Completion_Date VARCHAR2,
346 X_Old_Status_Type NUMBER,
347 X_Status_Type NUMBER,
348 X_Old_Quantity NUMBER,
349 X_Quantity NUMBER,
350 X_Last_Updated_By NUMBER,
351 X_Last_Update_Login NUMBER,
352 X_Success_Flag OUT NOCOPY NUMBER) IS
353 p_req_id NUMBER := FND_GLOBAL.CONC_REQUEST_ID;
354 p_appl_id NUMBER := FND_GLOBAL.PROG_APPL_ID;
355 p_prog_id NUMBER := FND_GLOBAL.CONC_PROGRAM_ID;
356 x_first_op NUMBER;
357 x_qty_comp NUMBER;
358 x_start DATE;
359 x_comp DATE;
360 BEGIN
361
362 X_Success_Flag := 1;
363 x_start := to_date(X_Start_Date, WIP_CONSTANTS.DT_NOSEC_FMT);
364 x_comp := to_date(X_Completion_Date, WIP_CONSTANTS.DT_NOSEC_FMT);
365
366 /* This portion chopped from check_quantity routine in wiloer */
367
368
369 /* Figure out if there are operations */
370
371 IF X_Old_Status_Type <> 1 AND X_Status_Type <> 1
372 AND X_Old_Quantity <> X_Quantity THEN
373 SELECT NVL(MIN(OPERATION_SEQ_NUM), -1)
374 INTO x_first_op
375 FROM WIP_OPERATIONS
376 WHERE ORGANIZATION_ID = X_Organization_Id
377 AND WIP_ENTITY_ID = X_Wip_Entity_Id
378 AND NVL(REPETITIVE_SCHEDULE_ID, -1) =
379 NVL(X_Repetitive_Schedule_Id, -1);
380
381 /* If there are operations */
382
383 IF x_first_op <> -1 THEN
384
385 /* Reset the quantity in queue for the first op */
386
387 UPDATE WIP_OPERATIONS
388 SET QUANTITY_IN_QUEUE = QUANTITY_IN_QUEUE -
389 (SCHEDULED_QUANTITY - X_Quantity)
390 WHERE ORGANIZATION_ID = X_Organization_id
391 AND WIP_ENTITY_ID = X_Wip_Entity_id
392 AND NVL(REPETITIVE_SCHEDULE_ID, -1) =
393 NVL(X_Repetitive_Schedule_Id, -1)
394 AND OPERATION_SEQ_NUM = x_first_op
395 AND SCHEDULED_QUANTITY - X_Quantity <= QUANTITY_IN_QUEUE;
396
397 /* If no rows were updated, that means that the quantity was
398 lowered below what was already past queue of the first op.
399 This is an error. */
400
401 IF SQL%NOTFOUND THEN
402 x_success_flag := 0;
403 return;
404 END IF;
405
406 ELSE
407
408 -- I think this check is duplicated in
409 -- Quantity field handler but since this package is generic...
410
411 IF X_Repetitive_Schedule_Id IS NULL THEN
412 SELECT QUANTITY_COMPLETED
413 INTO x_qty_comp
414 FROM WIP_DISCRETE_JOBS
415 WHERE ORGANIZATION_ID = X_Organization_id
416 AND WIP_ENTITY_ID = X_Wip_Entity_id;
417 ELSE
418 SELECT QUANTITY_COMPLETED
419 INTO x_qty_comp
420 FROM WIP_REPETITIVE_SCHEDULES
421 WHERE ORGANIZATION_ID = X_Organization_id
422 AND REPETITIVE_SCHEDULE_ID = X_Repetitive_Schedule_id;
423 END IF;
424
425 /* For routingless jobs, you cant lower the quantity below
426 what was already completed.
427 */
428
429 IF X_Quantity < x_qty_comp THEN
430 x_success_flag := 0;
431 return;
432 END IF;
433
434 END IF;
435
436 END IF; /* End of Quantity validation */
437
438 -- Update operation quantities and dates
439
440 UPDATE WIP_OPERATIONS
441 SET FIRST_UNIT_START_DATE = x_start,
442 FIRST_UNIT_COMPLETION_DATE = decode(X_load_type, WIP_CONSTANTS.RESCHED_EAM_JOB, x_comp, x_start),
443 LAST_UNIT_START_DATE = x_start,
444 LAST_UNIT_COMPLETION_DATE = decode(X_load_type, WIP_CONSTANTS.RESCHED_EAM_JOB, x_comp, x_start),
445 SCHEDULED_QUANTITY =
446 ROUND(X_Quantity, WIP_CONSTANTS.MAX_DISPLAYED_PRECISION),
447 LAST_UPDATED_BY = X_Last_Updated_By,
448 LAST_UPDATE_DATE = SYSDATE,
449 LAST_UPDATE_LOGIN = X_Last_Update_Login,
450 REQUEST_ID = p_req_id,
451 PROGRAM_UPDATE_DATE = SYSDATE,
452 PROGRAM_ID = p_prog_id,
453 PROGRAM_APPLICATION_ID = p_appl_id
454 WHERE ORGANIZATION_ID = X_Organization_Id
455 AND WIP_ENTITY_ID = X_Wip_Entity_Id
456 AND NVL(REPETITIVE_SCHEDULE_ID, -1) = NVL(X_Repetitive_Schedule_ID, -1);
457
458 /* Note that WRO quantities are NOT updated here */
459
460 UPDATE WIP_REQUIREMENT_OPERATIONS WRO
461 SET WRO.DATE_REQUIRED =
462 (SELECT NVL(MIN(FIRST_UNIT_START_DATE), X_Start)
463 FROM WIP_OPERATIONS
464 WHERE ORGANIZATION_ID = X_Organization_Id
465 AND WIP_ENTITY_ID = X_Wip_Entity_Id
466 AND NVL(REPETITIVE_SCHEDULE_ID, -1) =
467 NVL(X_Repetitive_Schedule_ID, -1)
468 AND OPERATION_SEQ_NUM = ABS(WRO.OPERATION_SEQ_NUM)),
469 LAST_UPDATED_BY = X_Last_Updated_By,
470 LAST_UPDATE_DATE = SYSDATE,
471 LAST_UPDATE_LOGIN = X_Last_Update_Login,
472 REQUEST_ID = p_req_id,
473 PROGRAM_UPDATE_DATE = SYSDATE,
474 PROGRAM_ID = p_prog_id,
475 PROGRAM_APPLICATION_ID = p_appl_id
476 WHERE ORGANIZATION_ID = X_Organization_Id
477 AND WIP_ENTITY_ID = X_Wip_Entity_Id
478 AND NVL(REPETITIVE_SCHEDULE_ID, -1) = NVL(X_Repetitive_Schedule_ID, -1);
479
480 UPDATE WIP_OPERATION_RESOURCES
481 SET START_DATE = X_Start,
482 COMPLETION_DATE = DECODE(X_load_type, WIP_CONSTANTS.RESCHED_EAM_JOB , x_comp, x_start) ,
483 LAST_UPDATED_BY = X_Last_Updated_By,
484 LAST_UPDATE_DATE = SYSDATE,
485 LAST_UPDATE_LOGIN = X_Last_Update_Login,
486 REQUEST_ID = p_req_id,
487 PROGRAM_UPDATE_DATE = SYSDATE,
488 PROGRAM_ID = p_prog_id,
489 PROGRAM_APPLICATION_ID = p_appl_id
490 WHERE ORGANIZATION_ID = X_Organization_Id
491 AND WIP_ENTITY_ID = X_Wip_Entity_Id
492 AND NVL(REPETITIVE_SCHEDULE_ID, -1) = NVL(X_Repetitive_Schedule_ID, -1);
493
494 END update_routing;
495
496 END WIP_SCHEDULER;