1 PACKAGE BODY WIP_OPERATIONS_UTILITIES AS
2 /* $Header: wipoputb.pls 120.3.12020000.4 2013/03/01 07:27:35 akuppa ship $ */
3
4 PROCEDURE Check_Unique(X_Wip_Entity_Id NUMBER,
5 X_Organization_Id NUMBER,
6 X_Operation_Seq_Num NUMBER,
7 X_Repetitive_Schedule_Id NUMBER) IS
8 ops_count NUMBER := 0;
9 cursor discrete_check is
10 SELECT count(*)
11 FROM WIP_OPERATIONS
12 WHERE ORGANIZATION_ID = X_Organization_Id
13 AND WIP_ENTITY_ID = X_Wip_Entity_Id
14 AND OPERATION_SEQ_NUM = X_Operation_Seq_Num;
15 cursor repetitive_check is
16 SELECT count(*)
17 FROM WIP_OPERATIONS
18 WHERE ORGANIZATION_ID = X_Organization_Id
19 AND WIP_ENTITY_ID = X_Wip_Entity_Id
20 AND OPERATION_SEQ_NUM = X_Operation_Seq_Num
21 AND REPETITIVE_SCHEDULE_ID = X_Repetitive_Schedule_Id;
22 BEGIN
23 IF X_Repetitive_Schedule_Id IS NULL then
24 open discrete_check;
25 fetch discrete_check into ops_count;
26 close discrete_check;
27 ELSE
28 open repetitive_check;
29 fetch repetitive_check into ops_count;
30 close repetitive_check;
31 END IF;
32 IF ops_count <> 0 THEN
33 FND_MESSAGE.SET_NAME('WIP','WIP_ALREADY_EXISTS');
34 FND_MESSAGE.SET_TOKEN('ENTITY1',
35 'operation sequence number-cap', TRUE);
36 fnd_message.raise_error;
37 app_exception.raise_exception;
38 END IF;
39 END Check_Unique;
40
41 FUNCTION Pending_Op_Txns(X_Wip_Entity_Id NUMBER,
42 X_Organization_Id NUMBER,
43 X_Operation_Seq_Num NUMBER,
44 X_Repetitive_Schedule_Id NUMBER,
45 X_Line_Id NUMBER)
46 return BOOLEAN IS
47 X_count NUMBER := 0;
48 retval BOOLEAN;
49 cursor disc_move_check is
50 SELECT 1
51 FROM WIP_MOVE_TXN_INTERFACE
52 WHERE ORGANIZATION_ID = X_Organization_Id
53 AND WIP_ENTITY_ID = X_Wip_Entity_Id
54 AND (FM_OPERATION_SEQ_NUM = X_Operation_Seq_Num
55 OR TO_OPERATION_SEQ_NUM = X_Operation_Seq_Num);
56 cursor disc_res_check is
57 SELECT 1
58 FROM WIP_OPERATION_RESOURCES
59 WHERE ORGANIZATION_ID = X_Organization_Id
60 AND WIP_ENTITY_ID = X_Wip_Entity_Id
61 AND OPERATION_SEQ_NUM = X_Operation_Seq_Num
62 AND APPLIED_RESOURCE_UNITS <> 0;
63 cursor disc_cost_check is
64 SELECT 1
65 FROM WIP_COST_TXN_INTERFACE
66 WHERE ORGANIZATION_ID = X_Organization_Id
67 AND WIP_ENTITY_ID = X_Wip_Entity_Id
68 AND OPERATION_SEQ_NUM = X_Operation_Seq_Num;
69 cursor rep_move_check is
70 SELECT 1
71 FROM WIP_MOVE_TXN_INTERFACE
72 WHERE ORGANIZATION_ID = X_Organization_Id
73 AND WIP_ENTITY_ID = X_Wip_Entity_Id
74 AND LINE_ID = X_Line_Id
75 AND (FM_OPERATION_SEQ_NUM = X_Operation_Seq_Num
76 OR TO_OPERATION_SEQ_NUM = X_Operation_Seq_Num);
77 cursor rep_res_check is
78 SELECT 1
79 FROM WIP_OPERATION_RESOURCES
80 WHERE ORGANIZATION_ID = X_Organization_Id
81 AND WIP_ENTITY_ID = X_Wip_Entity_Id
82 AND REPETITIVE_SCHEDULE_ID = X_Repetitive_Schedule_Id
83 AND OPERATION_SEQ_NUM = X_Operation_Seq_Num
84 AND APPLIED_RESOURCE_UNITS <> 0;
85 cursor rep_cost_check is
86 SELECT 1
87 FROM WIP_COST_TXN_INTERFACE
88 WHERE ORGANIZATION_ID = X_Organization_Id
89 AND WIP_ENTITY_ID = X_Wip_Entity_Id
90 AND LINE_ID = X_Line_Id
91 AND OPERATION_SEQ_NUM = X_Operation_Seq_Num;
92 BEGIN
93 IF X_Repetitive_Schedule_Id IS NULL THEN
94 open disc_move_check;
95 open disc_res_check;
96 open disc_cost_check;
97 fetch disc_move_check into X_count;
98 fetch disc_res_check into X_count;
99 fetch disc_cost_check into X_count;
100 retval := (NOT (disc_move_check%NOTFOUND AND disc_res_check%NOTFOUND AND disc_cost_check%NOTFOUND));
101 close disc_move_check;
102 close disc_res_check;
103 close disc_cost_check;
104 ELSE
105 open rep_move_check;
106 open rep_res_check;
107 open rep_cost_check;
108 fetch rep_move_check into X_count;
109 fetch rep_res_check into X_count;
110 fetch rep_cost_check into X_count;
111 retval := (NOT (rep_move_check%NOTFOUND AND rep_res_check%NOTFOUND AND rep_cost_check%NOTFOUND));
112 close rep_move_check;
113 close rep_res_check;
114 close rep_cost_check;
115 END IF;
116 return retval;
117 END Pending_Op_Txns;
118
119
120 FUNCTION Get_Previous_Op(X_Wip_Entity_Id NUMBER,
121 X_Organization_Id NUMBER,
122 X_Operation_Seq_Num NUMBER,
123 X_Repetitive_Schedule_Id NUMBER)
124 return NUMBER IS
125 opseq NUMBER;
126 cursor disc_op is
127 select max(operation_seq_num)
128 from wip_operations
129 where wip_entity_id = X_Wip_Entity_Id
130 and organization_id = X_Organization_Id
131 and operation_seq_num < X_Operation_Seq_Num;
132 cursor rep_op is
133 select max(operation_seq_num)
134 from wip_operations
135 where wip_entity_id = X_Wip_Entity_Id
136 and organization_id = X_Organization_Id
137 and operation_seq_num < X_Operation_Seq_Num
138 and repetitive_schedule_id = X_Repetitive_Schedule_Id;
139 BEGIN
140 IF X_Repetitive_Schedule_Id IS NULL THEN
141 open disc_op;
142 fetch disc_op into opseq;
143 close disc_op;
144 ELSE
145 open rep_op;
146 fetch rep_op into opseq;
147 close rep_op;
148 END IF;
149 return opseq;
150 END Get_Previous_Op;
151
152
153 PROCEDURE Get_Prev_Next_Op(X_Wip_Entity_Id NUMBER,
154 X_Organization_Id NUMBER,
155 X_Operation_Seq_Num NUMBER,
156 X_Repetitive_Schedule_Id NUMBER,
157 X_Insert_Flag BOOLEAN,
158 X_Prev_Op_Seq IN OUT NOCOPY NUMBER,
159 X_Next_Op_Seq IN OUT NOCOPY NUMBER) IS
160 BEGIN
161 IF X_Repetitive_Schedule_Id IS NULL THEN
162 select max(operation_seq_num)
163 into X_Prev_Op_Seq
164 from wip_operations
165 where wip_entity_id = X_Wip_Entity_Id
166 and organization_id = X_Organization_Id
167 and operation_seq_num < X_Operation_Seq_Num;
168 select min(operation_seq_num)
169 into X_Next_Op_Seq
170 from wip_operations
171 where wip_entity_id = X_Wip_Entity_Id
172 and organization_id = X_Organization_Id
173 and operation_seq_num > X_Operation_Seq_Num;
174 ELSE
175 select max(operation_seq_num)
176 into X_Prev_Op_Seq
177 from wip_operations
178 where wip_entity_id = X_Wip_Entity_Id
179 and organization_id = X_Organization_Id
180 and operation_seq_num < X_Operation_Seq_Num
181 and repetitive_schedule_id = X_Repetitive_Schedule_Id;
182 select min(operation_seq_num)
183 into X_Next_Op_Seq
184 from wip_operations
185 where wip_entity_id = X_Wip_Entity_Id
186 and organization_id = X_Organization_Id
187 and operation_seq_num > X_Operation_Seq_Num
188 and repetitive_schedule_id = X_Repetitive_Schedule_Id;
189 END IF;
190 IF X_Insert_Flag THEN
191 IF (X_Prev_Op_Seq IS NOT NULL) THEN
192 Set_Next_Op(X_Wip_Entity_Id,
193 X_Organization_Id,
194 X_Prev_Op_Seq,
195 X_Operation_Seq_Num,
196 X_Repetitive_Schedule_Id);
197 END IF;
198 IF (X_Next_Op_Seq IS NOT NULL) THEN
199 Set_Previous_Op(X_Wip_Entity_Id,
200 X_Organization_Id,
201 X_Next_Op_Seq,
202 X_Operation_Seq_Num,
203 X_Repetitive_Schedule_Id);
204 END IF;
205 ELSE -- Called by pre-delete
206 IF (X_Prev_Op_Seq IS NOT NULL) THEN
207 Set_Next_Op(X_Wip_Entity_Id,
208 X_Organization_Id,
209 X_Prev_Op_Seq,
210 X_Next_Op_Seq,
211 X_Repetitive_Schedule_Id);
212 END IF;
213 IF (X_Next_Op_Seq IS NOT NULL) THEN
214 Set_Previous_Op(X_Wip_Entity_Id,
215 X_Organization_Id,
216 X_Next_Op_Seq,
217 X_Prev_Op_Seq,
218 X_Repetitive_Schedule_Id);
219 END IF;
220 END IF;
221
222 END Get_Prev_Next_Op;
223
224 PROCEDURE Set_Previous_Op(X_Wip_Entity_Id NUMBER,
225 X_Organization_Id NUMBER,
226 X_Operation_Seq_Num NUMBER,
227 X_Prev_Op_Seq NUMBER,
228 X_Repetitive_Schedule_Id NUMBER) IS
229 BEGIN
230 IF X_Repetitive_Schedule_Id IS NULL then
231 update wip_operations
232 set previous_operation_seq_num = X_Prev_Op_Seq
233 where wip_entity_id = X_Wip_Entity_Id
234 and organization_id = X_Organization_Id
235 and operation_seq_num = X_Operation_Seq_Num;
236 ELSE
237 update wip_operations
238 set previous_operation_seq_num = X_Prev_Op_Seq
239 where wip_entity_id = X_Wip_Entity_Id
240 and organization_id = X_Organization_Id
241 and repetitive_schedule_id = X_Repetitive_Schedule_Id
242 and operation_seq_num = X_Operation_Seq_Num;
243 END IF;
244 END Set_Previous_Op;
245
246 PROCEDURE Set_Next_Op(X_Wip_Entity_Id NUMBER,
247 X_Organization_Id NUMBER,
248 X_Operation_Seq_Num NUMBER,
249 X_Next_Op_Seq NUMBER,
250 X_Repetitive_Schedule_Id NUMBER) IS
251 BEGIN
252 IF X_Repetitive_Schedule_Id IS NULL then
253 update wip_operations
254 set next_operation_seq_num = X_Next_Op_Seq
255 where wip_entity_id = X_Wip_Entity_Id
256 and organization_id = X_Organization_Id
257 and operation_seq_num = X_Operation_Seq_Num;
258 ELSE
259 update wip_operations
260 set next_operation_seq_num = X_Next_Op_Seq
261 where wip_entity_id = X_Wip_Entity_Id
262 and organization_id = X_Organization_Id
263 and repetitive_schedule_id = X_Repetitive_Schedule_Id
264 and operation_seq_num = X_Operation_Seq_Num;
265 END IF;
266 END Set_Next_Op;
267
268 PROCEDURE Delete_Resources(X_Wip_Entity_Id NUMBER,
269 X_Organization_Id NUMBER,
270 X_Operation_Seq_Num NUMBER,
271 X_Repetitive_Schedule_Id NUMBER,
272 x_return_status OUT NOCOPY VARCHAR2) IS
273
274 -- remove cursors to check po/req exists because these cursors does not
275 -- consider canceled po/req. Morever, we already had an API to do this job
276 -- , so we will call wip_osp.po_req_exists instead.
277 l_propagate_job_change_to_po NUMBER;
278 l_entity_type NUMBER;
279 l_return_status VARCHAR2(1);
280 BEGIN
281 IF(X_Operation_Seq_Num IS NULL) THEN
282 return;
283 END IF;
284 x_return_status := fnd_api.g_ret_sts_success;
285 IF(x_repetitive_schedule_id IS NULL) THEN
289 END IF;
286 l_entity_type := WIP_CONSTANTS.DISCRETE;
287 ELSE
288 l_entity_type := WIP_CONSTANTS.REPETITIVE;
290 IF(wip_osp.po_req_exists(
291 p_wip_entity_id => x_wip_entity_id,
292 p_rep_sched_id => x_repetitive_schedule_id,
293 p_organization_id => x_organization_id,
294 p_op_seq_num => x_operation_seq_num,
295 p_entity_type => l_entity_type)) THEN
296
297 IF(po_code_release_grp.Current_Release >=
298 po_code_release_grp.PRC_11i_Family_Pack_J) THEN
299
300 SELECT propagate_job_change_to_po
301 INTO l_propagate_job_change_to_po
302 FROM wip_parameters
303 WHERE organization_id = x_organization_id;
304
305 IF(l_propagate_job_change_to_po = WIP_CONSTANTS.YES) THEN
306 -- Try to cancel PO/requisitions
307 wip_osp.cancelPOReq(
308 p_job_id => x_wip_entity_id,
309 p_repetitive_id => x_repetitive_schedule_id,
310 p_org_id => x_organization_id,
311 p_op_seq_num => x_operation_seq_num,
312 x_return_status => l_return_status);
313
314 IF(l_return_status <> fnd_api. g_ret_sts_success) THEN
315 -- If we are unable to cancel all PO/requisition associated to
316 -- this job/schedule, we will try to cancel as much as we can,
317 -- then user need to manually cancel the rest.
318 x_return_status := fnd_api.g_ret_sts_error;
319 END IF; -- check return status
320 ELSE
321 -- propagate_job_change_to_po is manual
322 x_return_status := fnd_api.g_ret_sts_error;
323 fnd_message.set_name('WIP','WIP_DELETE_OSP_RESOURCE');
324 fnd_msg_pub.add;
325 END IF;
326 ELSE
327 -- customer does not have PO patchset J onward
328 x_return_status := fnd_api.g_ret_sts_error;
329 fnd_message.set_name('WIP','WIP_DELETE_OSP_RESOURCE');
330 fnd_msg_pub.add;
331 END IF;
332 END IF; -- PO/requisition exists
333 END Delete_Resources;
334
335 PROCEDURE Insert_Resources(X_Wip_Entity_Id NUMBER,
336 X_Organization_Id NUMBER,
337 X_Operation_Seq_Num NUMBER,
338 X_Standard_Operation_Id NUMBER,
339 X_Repetitive_Schedule_Id NUMBER,
340 X_Last_Updated_By NUMBER,
341 X_Created_By NUMBER,
342 X_Last_Update_Login NUMBER,
343 X_Start_Date DATE,
344 X_Completion_Date DATE) IS
345 /* Added : -- bug 7371859 */
346 sub_res_count number;
347 l_wsor_max_res_seq_num number :=0;
348 /* End : -- bug 7371859 */
349
350 BEGIN
351 INSERT INTO WIP_OPERATION_RESOURCES
352 (WIP_ENTITY_ID, OPERATION_SEQ_NUM,
353 RESOURCE_SEQ_NUM, ORGANIZATION_ID,
354 REPETITIVE_SCHEDULE_ID, LAST_UPDATE_DATE,
355 LAST_UPDATED_BY, CREATION_DATE,
356 CREATED_BY, LAST_UPDATE_LOGIN,
357 RESOURCE_ID, UOM_CODE,
358 BASIS_TYPE, USAGE_RATE_OR_AMOUNT,
359 ACTIVITY_ID, SCHEDULED_FLAG,
360 ASSIGNED_UNITS, AUTOCHARGE_TYPE,
361 STANDARD_RATE_FLAG, APPLIED_RESOURCE_UNITS,
362 APPLIED_RESOURCE_VALUE, START_DATE,
363 COMPLETION_DATE, ATTRIBUTE_CATEGORY,
364 ATTRIBUTE1, ATTRIBUTE2, ATTRIBUTE3,
365 ATTRIBUTE4, ATTRIBUTE5, ATTRIBUTE6,
366 ATTRIBUTE7, ATTRIBUTE8, ATTRIBUTE9,
367 ATTRIBUTE10, ATTRIBUTE11, ATTRIBUTE12,
368 ATTRIBUTE13, ATTRIBUTE14, ATTRIBUTE15,
369 SUBSTITUTE_GROUP_NUM, SCHEDULE_SEQ_NUM,PRINCIPLE_FLAG,REPLACEMENT_GROUP_NUM) --/* Added : ---- bug 7371859
370 SELECT X_Wip_Entity_Id, X_Operation_Seq_Num,
371 S.RESOURCE_SEQ_NUM, X_Organization_Id,
372 X_Repetitive_Schedule_Id, SYSDATE,
373 X_Last_Updated_By, SYSDATE,
374 X_Created_By, X_Last_Update_Login,
375 S.RESOURCE_ID, R.UNIT_OF_MEASURE,
376 S.BASIS_TYPE, S.USAGE_RATE_OR_AMOUNT,
377 S.ACTIVITY_ID, S.SCHEDULE_FLAG,
378 S.ASSIGNED_UNITS, S.AUTOCHARGE_TYPE,
379 S.STANDARD_RATE_FLAG, 0,
380 0,
381 DECODE(X_Start_Date, NULL, SYSDATE, X_Start_Date),
382 DECODE(X_Completion_Date, NULL, SYSDATE, X_Completion_Date),
383 S.ATTRIBUTE_CATEGORY,
384 S.ATTRIBUTE1, S.ATTRIBUTE2, S.ATTRIBUTE3,
385 S.ATTRIBUTE4, S.ATTRIBUTE5, S.ATTRIBUTE6,
386 S.ATTRIBUTE7, S.ATTRIBUTE8, S.ATTRIBUTE9,
387 S.ATTRIBUTE10, S.ATTRIBUTE11, S.ATTRIBUTE12,
388 S.ATTRIBUTE13, S.ATTRIBUTE14, S.ATTRIBUTE15,
389 S.SUBSTITUTE_GROUP_NUM, S.SCHEDULE_SEQ_NUM,S.PRINCIPLE_FLAG,0 --/* Added : -- bug 7371859
390 FROM BOM_STD_OP_RESOURCES S,
391 BOM_RESOURCES R
392 WHERE S.STANDARD_OPERATION_ID = X_Standard_Operation_Id
393 AND R.RESOURCE_ID = S.RESOURCE_ID
394 AND NVL(R.DISABLE_DATE, SYSDATE + 1) > SYSDATE;
395
396 /* Added : - bug 7371859 */
397 BEGIN
398 SELECT count(*)
399 INTO sub_res_count
400 FROM BOM_STD_SUB_OP_RESOURCES BSSOR
401 WHERE BSSOR.STANDARD_OPERATION_ID=X_Standard_Operation_Id;
402 EXCEPTION
403 WHEN no_data_found THEN
404 null;
405 END ;
406
407 IF sub_res_count >0 then
408
409 BEGIN
410 SELECT nvl(max(resource_seq_num), 10)
411 INTO l_wsor_max_res_seq_num
412 FROM WIP_SUB_OPERATION_RESOURCES WSOR
413 WHERE wip_entity_id = x_wip_entity_id
414 AND OPERATION_SEQ_NUM = X_Operation_Seq_Num;
415 EXCEPTION
419
416 WHEN no_data_found THEN
417 null;
418 END;
420 INSERT INTO WIP_SUB_OPERATION_RESOURCES
421 (wip_entity_id,
422 operation_seq_num,
423 resource_seq_num,
424 organization_id,
425 repetitive_schedule_id,
426 last_update_date,
427 last_updated_by,
428 creation_date,
429 created_by,
430 last_update_login,
431 resource_id,
432 uom_code,
433 basis_type,
434 usage_rate_or_amount,
435 activity_id,
436 scheduled_flag,
437 assigned_units,
438 maximum_assigned_units,
439 autocharge_type,
440 standard_rate_flag,
441 applied_resource_units,
442 applied_resource_value,
443 attribute_category,
444 attribute1,
445 attribute2,
446 attribute3,
447 attribute4,
448 attribute5,
449 attribute6,
450 attribute7,
451 attribute8,
452 attribute9,
453 attribute10,
454 attribute11,
455 attribute12,
456 attribute13,
457 attribute14,
458 attribute15,
459 completion_date,
460 start_date,
461 schedule_seq_num,
462 substitute_group_num,
463 replacement_group_num,
464 setup_id)
465 SELECT X_Wip_Entity_Id,
466 X_Operation_Seq_Num,
467 (rownum + l_wsor_max_res_seq_num),
468 X_Organization_Id,
469 X_Repetitive_Schedule_Id,
470 SYSDATE ,
471 X_Last_Updated_By,
472 SYSDATE,
473 X_Created_By,
474 X_Last_Update_Login,
475 BSSOR.resource_id,
476 BR.unit_of_measure,
477 BSSOR.basis_type,
478 BSSOR.usage_rate_or_amount,
479 BSSOR.activity_id,
480 BSSOR.schedule_flag,
481 BSSOR.assigned_units,
482 BSSOR.assigned_units,
483 BSSOR.autocharge_type,
484 BSSOR.standard_rate_flag,
485 0, --WCOR.applied_resource_units,
486 0, -- WCOR.applied_resource_value,
487 BSSOR.attribute_category,
488 BSSOR.attribute1,
489 BSSOR.attribute2,
490 BSSOR.attribute3,
491 BSSOR.attribute4,
492 BSSOR.attribute5,
493 BSSOR.attribute6,
494 BSSOR.attribute7,
495 BSSOR.attribute8,
496 BSSOR.attribute9,
497 BSSOR.attribute10,
498 BSSOR.attribute11,
499 BSSOR.attribute12,
500 BSSOR.attribute13,
501 BSSOR.attribute14,
502 BSSOR.attribute15,
503 DECODE(X_Start_Date, NULL, SYSDATE, X_Start_Date),
504 DECODE(X_Completion_Date, NULL, SYSDATE, X_Completion_Date),
505 BSSOR.schedule_seq_num ,
506 BSSOR.substitute_group_num,
507 BSSOR.replacement_group_num,
508 NULL --setup_id
509 FROM BOM_RESOURCES BR,
510 BOM_STD_SUB_OP_RESOURCES BSSOR
511 where bssor.standard_operation_id=X_Standard_Operation_Id
512 and BSSOR.RESOURCE_ID = BR.RESOURCE_ID;
513
514 End IF;
515 /* End : -- bug 7371859 */
516
517
518 END Insert_Resources;
519
520 -- Counts the number of resources per standard operation --
521 FUNCTION Num_Standard_Resources(X_Organization_Id NUMBER,
522 X_Standard_Operation_Id NUMBER)
523 RETURN NUMBER IS
524 P_Num_Resources NUMBER;
525 BEGIN
526 SELECT COUNT(R.RESOURCE_ID)
527 INTO P_Num_Resources
528 FROM BOM_STD_OP_RESOURCES S,
529 BOM_RESOURCES R
530 WHERE S.STANDARD_OPERATION_ID = X_Standard_Operation_Id
531 AND R.ORGANIZATION_ID = X_Organization_Id
532 AND R.RESOURCE_ID = S.RESOURCE_ID
533 AND NVL(R.DISABLE_DATE, SYSDATE + 1) > SYSDATE;
534 RETURN P_Num_Resources;
535 END Num_Standard_Resources;
536
537 /* Note: This routine is called from two places:
538 1) When an Operation is Deleted
539 2) When the routing of a job/schedule is updated
540 */
541
542 PROCEDURE Check_Requirements(X_Wip_Entity_Id NUMBER,
543 X_Organization_Id NUMBER,
544 X_Operation_Seq_Num NUMBER,
545 X_Repetitive_Schedule_Id NUMBER,
546 X_Entity_Start_Date DATE) IS
547 firstop NUMBER;
548 firstdep NUMBER;
549 firstdate DATE;
550 BEGIN
551 firstop := NULL;
552 IF X_Repetitive_Schedule_Id IS NULL then
553 SELECT nvl(min(operation_seq_num),0)
554 INTO firstop
555 FROM wip_operations
559 SELECT nvl(min(operation_seq_num),0)
556 WHERE wip_entity_id = X_Wip_Entity_Id
557 AND organization_id = X_Organization_Id;
558 ELSE
560 INTO firstop
561 FROM wip_operations
562 WHERE wip_entity_id = X_Wip_Entity_Id
563 AND organization_id = X_Organization_Id
564 AND repetitive_schedule_id = X_Repetitive_Schedule_Id;
565 END IF;
566
567 IF firstop = 0 THEN
568 firstop := 1;
569 firstdep := NULL;
570 firstdate := X_Entity_Start_Date;
571 ELSIF X_Repetitive_Schedule_Id IS NULL THEN
572 SELECT department_id, first_unit_start_date
573 INTO firstdep, firstdate
574 FROM wip_operations wo
575 WHERE wip_entity_id = X_Wip_Entity_Id
576 AND organization_id = X_Organization_Id
577 AND operation_seq_num = firstop;
578 ELSE
579 SELECT department_id, first_unit_start_date
580 INTO firstdep, firstdate
581 FROM wip_operations wo
582 WHERE wip_entity_id = X_Wip_Entity_Id
583 AND organization_id = X_Organization_Id
584 AND operation_seq_num = firstop
585 AND repetitive_schedule_id = X_Repetitive_Schedule_Id;
586 END IF;
587
588 IF X_Repetitive_Schedule_Id IS NULL THEN
589
590 /* Update the department and date required of requirements to
591 those of their new operation */
592
593 UPDATE WIP_REQUIREMENT_OPERATIONS WRO
594 SET OPERATION_SEQ_NUM = SIGN(OPERATION_SEQ_NUM) * firstop,
595 DEPARTMENT_ID = firstdep,
596 DATE_REQUIRED = firstdate
597 WHERE ORGANIZATION_ID = X_Organization_Id
598 AND WIP_ENTITY_ID = X_Wip_Entity_Id
599 AND OPERATION_SEQ_NUM = SIGN(OPERATION_SEQ_NUM) * X_Operation_Seq_Num
600 AND NOT EXISTS
601 (SELECT 'checking for duplicate requirements'
602 FROM WIP_REQUIREMENT_OPERATIONS
603 WHERE ORGANIZATION_ID = X_Organization_Id
604 AND WIP_ENTITY_ID = X_Wip_Entity_Id
605 AND OPERATION_SEQ_NUM = SIGN(WRO.OPERATION_SEQ_NUM) * firstop
606 AND INVENTORY_ITEM_ID = WRO.INVENTORY_ITEM_ID);
607
608 /* If the requirement already existed at this operation, we
609 want to increase the quantities instead of adding a new
610 requirement.
611 We don't want to do this IF the deleted op is Op Seq 1 and
612 there are no other ops.
613 */
614
615 IF X_Operation_Seq_Num NOT IN (-1, 1) OR firstdep IS NOT NULL THEN
616 UPDATE WIP_REQUIREMENT_OPERATIONS WRO
617 SET (WIP_SUPPLY_TYPE, REQUIRED_QUANTITY,
618 QUANTITY_ISSUED, QUANTITY_PER_ASSEMBLY) =
619 (SELECT LEAST(WRO.WIP_SUPPLY_TYPE, WIP_SUPPLY_TYPE),
620 WRO.REQUIRED_QUANTITY + REQUIRED_QUANTITY,
621 WRO.QUANTITY_ISSUED + QUANTITY_ISSUED,
622 WRO.QUANTITY_PER_ASSEMBLY + QUANTITY_PER_ASSEMBLY
623 FROM WIP_REQUIREMENT_OPERATIONS
624 WHERE ORGANIZATION_ID = X_Organization_Id
625 AND WIP_ENTITY_ID = X_Wip_Entity_Id
626 AND OPERATION_SEQ_NUM = SIGN(WRO.OPERATION_SEQ_NUM) *
627 X_Operation_Seq_Num
628 AND INVENTORY_ITEM_ID = WRO.INVENTORY_ITEM_ID)
629 WHERE ORGANIZATION_ID = X_Organization_Id
630 AND WIP_ENTITY_ID = X_Wip_Entity_Id
631 AND OPERATION_SEQ_NUM = SIGN(OPERATION_SEQ_NUM) * firstop
632 AND EXISTS
633 (SELECT 'checking for duplicate requirements'
634 FROM WIP_REQUIREMENT_OPERATIONS
635 WHERE ORGANIZATION_ID = X_Organization_Id
636 AND WIP_ENTITY_ID = X_Wip_Entity_Id
637 AND OPERATION_SEQ_NUM = SIGN(WRO.OPERATION_SEQ_NUM) * X_Operation_Seq_Num
638 AND INVENTORY_ITEM_ID = WRO.INVENTORY_ITEM_ID);
639
640 DELETE FROM WIP_REQUIREMENT_OPERATIONS
641 WHERE ORGANIZATION_ID = X_Organization_Id
642 AND WIP_ENTITY_ID = X_Wip_Entity_Id
643 AND (OPERATION_SEQ_NUM = X_Operation_Seq_Num
644 OR OPERATION_SEQ_NUM = X_Operation_Seq_Num * -1);
645
646 /* If you are deleting an Operation with Op Seq 1,
647 the Op Seq will stay 1, but the department and
648 Date Required might need to be reset */
649
650 ELSE
651
652 UPDATE WIP_REQUIREMENT_OPERATIONS WRO
653 SET OPERATION_SEQ_NUM = SIGN(OPERATION_SEQ_NUM) * firstop,
654 DEPARTMENT_ID = firstdep,
655 DATE_REQUIRED = firstdate
656 WHERE ORGANIZATION_ID = X_Organization_Id
657 AND WIP_ENTITY_ID = X_Wip_Entity_Id
658 AND OPERATION_SEQ_NUM = SIGN(OPERATION_SEQ_NUM) * X_Operation_Seq_Num;
659
660 END IF;
661
662
663 /* Repetitive Case */
664
665 ELSE
666
667 /* Update the department and date required of requirements to
668 those of their new operation */
669 UPDATE WIP_REQUIREMENT_OPERATIONS WRO
670 SET OPERATION_SEQ_NUM = SIGN(OPERATION_SEQ_NUM) * firstop,
671 DEPARTMENT_ID = firstdep,
672 DATE_REQUIRED = firstdate
673 WHERE ORGANIZATION_ID = X_Organization_Id
674 AND WIP_ENTITY_ID = X_Wip_Entity_Id
675 AND REPETITIVE_SCHEDULE_ID = X_Repetitive_Schedule_Id
676 AND OPERATION_SEQ_NUM = SIGN(OPERATION_SEQ_NUM) * X_Operation_Seq_Num
677 AND NOT EXISTS
678 (SELECT 'checking for duplicate requirements'
679 FROM WIP_REQUIREMENT_OPERATIONS
683 AND OPERATION_SEQ_NUM = SIGN(WRO.OPERATION_SEQ_NUM) * firstop
680 WHERE ORGANIZATION_ID = X_Organization_Id
681 AND WIP_ENTITY_ID = X_Wip_Entity_Id
682 AND REPETITIVE_SCHEDULE_ID = X_Repetitive_Schedule_Id
684 AND INVENTORY_ITEM_ID = WRO.INVENTORY_ITEM_ID);
685
686 /* If the requirement already existed at this operation, we
687 want to increase the quantities instead of adding a new
688 requirement.
689 We don't want to do this IF the deleted op is Op Seq 1 and
690 there are no other ops.
691 */
692
693 IF X_Operation_Seq_Num NOT IN (-1, 1) OR firstdep IS NOT NULL THEN
694 UPDATE WIP_REQUIREMENT_OPERATIONS WRO
695 SET (WIP_SUPPLY_TYPE, REQUIRED_QUANTITY,
696 QUANTITY_ISSUED, QUANTITY_PER_ASSEMBLY) =
697 (SELECT LEAST(WRO.WIP_SUPPLY_TYPE, WIP_SUPPLY_TYPE),
698 WRO.REQUIRED_QUANTITY + REQUIRED_QUANTITY,
699 WRO.QUANTITY_ISSUED + QUANTITY_ISSUED,
700 WRO.QUANTITY_PER_ASSEMBLY + QUANTITY_PER_ASSEMBLY
701 FROM WIP_REQUIREMENT_OPERATIONS
702 WHERE ORGANIZATION_ID = X_Organization_Id
703 AND WIP_ENTITY_ID = X_Wip_Entity_Id
704 AND REPETITIVE_SCHEDULE_ID = X_Repetitive_Schedule_Id
705 AND OPERATION_SEQ_NUM = SIGN(WRO.OPERATION_SEQ_NUM) *
706 X_Operation_Seq_Num
707 AND INVENTORY_ITEM_ID = WRO.INVENTORY_ITEM_ID)
708 WHERE ORGANIZATION_ID = X_Organization_Id
709 AND WIP_ENTITY_ID = X_Wip_Entity_Id
710 AND REPETITIVE_SCHEDULE_ID = X_Repetitive_Schedule_Id
711 AND OPERATION_SEQ_NUM = SIGN(OPERATION_SEQ_NUM) * firstop
712 AND EXISTS
713 (SELECT 'checking for duplicate requirements'
714 FROM WIP_REQUIREMENT_OPERATIONS
715 WHERE ORGANIZATION_ID = X_Organization_Id
716 AND WIP_ENTITY_ID = X_Wip_Entity_Id
717 AND REPETITIVE_SCHEDULE_ID = X_Repetitive_Schedule_Id
718 AND OPERATION_SEQ_NUM = SIGN(WRO.OPERATION_SEQ_NUM) * X_Operation_Seq_Num
719 AND INVENTORY_ITEM_ID = WRO.INVENTORY_ITEM_ID);
720
721 DELETE FROM WIP_REQUIREMENT_OPERATIONS
722 WHERE ORGANIZATION_ID = X_Organization_Id
723 AND WIP_ENTITY_ID = X_Wip_Entity_Id
724 AND (OPERATION_SEQ_NUM = X_Operation_Seq_Num
725 OR OPERATION_SEQ_NUM = X_Operation_Seq_Num * -1)
726 AND REPETITIVE_SCHEDULE_ID = X_Repetitive_Schedule_Id;
727
728 /* If you are deleting an Operation with Op Seq 1,
729 the Op Seq will stay 1, but the department and
730 Date Required might need to be reset */
731
732 ELSE
733
734 UPDATE WIP_REQUIREMENT_OPERATIONS WRO
735 SET OPERATION_SEQ_NUM = SIGN(OPERATION_SEQ_NUM) * firstop,
736 DEPARTMENT_ID = firstdep,
737 DATE_REQUIRED = firstdate
738 WHERE ORGANIZATION_ID = X_Organization_Id
739 AND WIP_ENTITY_ID = X_Wip_Entity_Id
740 AND OPERATION_SEQ_NUM = SIGN(OPERATION_SEQ_NUM) * X_Operation_Seq_Num
741 AND REPETITIVE_SCHEDULE_ID = X_Repetitive_Schedule_Id;
742
743 END IF;
744
745 END IF;
746
747 END Check_Requirements;
748
749 FUNCTION Num_Assembly_Pull(X_Wip_Entity_Id NUMBER,
750 X_Organization_Id NUMBER,
751 X_Operation_Seq_Num NUMBER,
752 X_Repetitive_Schedule_Id NUMBER)
753 return NUMBER is
754 opseq NUMBER;
755 BEGIN
756 IF X_Repetitive_Schedule_Id IS NULL THEN
757 SELECT count(*)
758 INTO opseq
759 FROM WIP_REQUIREMENT_OPERATIONS
760 WHERE WIP_ENTITY_ID = X_Wip_Entity_Id
761 AND ORGANIZATION_ID = X_Organization_Id
762 AND OPERATION_SEQ_NUM = X_Operation_Seq_Num
763 AND WIP_SUPPLY_TYPE = 2;
764 ELSE
765 SELECT count(*)
766 INTO opseq
767 FROM WIP_REQUIREMENT_OPERATIONS
768 WHERE WIP_ENTITY_ID = X_Wip_Entity_Id
769 AND REPETITIVE_SCHEDULE_ID = X_Repetitive_Schedule_Id
770 AND ORGANIZATION_ID = X_Organization_Id
771 AND OPERATION_SEQ_NUM = X_Operation_Seq_Num
772 AND WIP_SUPPLY_TYPE = 2;
773 END IF;
774 return opseq;
775 END Num_Assembly_Pull;
776
777 FUNCTION Num_Resources(X_Wip_Entity_Id NUMBER,
778 X_Organization_Id NUMBER,
779 X_Operation_Seq_Num NUMBER,
780 X_Repetitive_Schedule_Id NUMBER)
781 return NUMBER is
782 resct NUMBER;
783 BEGIN
784 IF X_Repetitive_Schedule_Id is NULL THEN
785 SELECT count(resource_id)
786 INTO resct
787 FROM wip_operation_resources
788 WHERE wip_entity_id = X_Wip_Entity_Id
789 AND organization_id = X_Organization_Id
790 AND operation_seq_num = X_Operation_Seq_Num;
791 ELSE
792 SELECT count(resource_id)
793 INTO resct
794 FROM wip_operation_resources
795 WHERE wip_entity_id = X_Wip_Entity_Id
796 AND organization_id = X_Organization_Id
797 AND operation_seq_num = X_Operation_Seq_Num
798 AND repetitive_schedule_id = X_Repetitive_Schedule_Id;
799 END IF;
800 return resct;
801 END Num_Resources;
802
806 X_Repetitive_Schedule_Id NUMBER,
803 PROCEDURE Set_Operation_Dates(X_Wip_Entity_Id NUMBER,
804 X_Organization_Id NUMBER,
805 X_Operation_Seq_Num NUMBER,
807 X_First_Unit_Start_Date DATE,
808 X_Last_Unit_Completion_Date DATE,
809 X_Resource_Start_Date DATE,
810 X_Resource_Completion_Date DATE) IS
811 BEGIN
812 IF X_Repetitive_Schedule_Id is NULL THEN
813 UPDATE wip_operations
814 SET first_unit_start_date = DECODE(SIGN(X_First_Unit_Start_Date-
815 X_Resource_Start_Date),
816 -1, X_First_Unit_Start_Date,
817 X_Resource_Start_Date),
818 last_unit_completion_date = DECODE(SIGN(X_Last_Unit_Completion_Date-
819 X_Resource_Completion_Date),
820 -1, X_Resource_Completion_Date,
821 X_Last_Unit_Completion_Date)
822 WHERE
823 wip_entity_id = X_Wip_Entity_Id
824 AND organization_id = X_Organization_Id
825 AND operation_seq_num = X_Operation_Seq_Num;
826 ELSE
827 UPDATE wip_operations
828 SET first_unit_start_date = DECODE(SIGN(X_First_Unit_Start_Date-
829 X_Resource_Start_Date),
830 -1, X_First_Unit_Start_Date,
831 X_Resource_Start_Date),
832 last_unit_completion_date = DECODE(SIGN(X_Last_Unit_Completion_Date-
833 X_Resource_Completion_Date),
834 -1, X_Resource_Completion_Date,
835 X_Last_Unit_Completion_Date)
836 WHERE
837 wip_entity_id = X_Wip_Entity_Id
838 AND organization_id = X_Organization_Id
839 AND operation_seq_num = X_Operation_Seq_Num
840 AND repetitive_schedule_id = X_Repetitive_Schedule_Id;
841 END IF;
842 END Set_Operation_Dates;
843
844 PROCEDURE Set_Entity_Dates(X_Wip_Entity_Id NUMBER,
845 X_Organization_Id NUMBER,
846 X_Repetitive_Schedule_Id NUMBER,
847 X_First_Unit_Start_Date DATE,
848 X_Last_Unit_Completion_Date DATE) IS
849 BEGIN
850 IF X_Repetitive_Schedule_Id is NULL THEN
851 UPDATE wip_discrete_jobs
852 SET scheduled_start_date = X_First_Unit_Start_Date,
853 scheduled_completion_date = X_Last_Unit_Completion_Date
854 WHERE
855 wip_entity_id = X_Wip_Entity_Id
856 AND organization_id = X_Organization_Id;
857 ELSE
858 UPDATE wip_repetitive_schedules
859 SET first_unit_start_date = X_First_Unit_Start_Date,
860 last_unit_completion_date = X_Last_Unit_Completion_Date
861 WHERE wip_entity_id = X_Wip_Entity_Id
862 AND organization_id = X_Organization_Id
863 AND repetitive_schedule_id = X_Repetitive_Schedule_Id;
864 END IF;
865 END Set_Entity_Dates;
866
867 -- Assumes that there were previously no operations. --
868 PROCEDURE Update_Operationless_Reqs(X_Wip_Entity_Id NUMBER,
869 X_Organization_Id NUMBER,
870 X_Operation_Seq_Num NUMBER,
871 X_Repetitive_Schedule_Id NUMBER,
872 X_Department_Id NUMBER,
873 X_First_Unit_Start_Date DATE) IS
874 l_msg_data VARCHAR2(240);
875 l_return_status VARCHAR2(1);
876 l_serialization_start_op NUMBER;
877 BEGIN
878
879 --just ignore the return status.
880 --this is status quo for the form (WIPOPMDF) apparently since only db errors will cause exceptions in these
881 --procedures.
882 wip_picking_pvt.update_allocation_op_seqs(p_wip_entity_id => X_Wip_Entity_Id,
883 p_operation_seq_num => X_Operation_Seq_Num,
884 p_repetitive_schedule_id => X_Repetitive_Schedule_Id,
885 x_msg_data => l_msg_data,
886 x_return_status => l_return_status);
887
888 IF X_Repetitive_Schedule_Id IS NULL THEN
889 update wip_discrete_jobs
890 set serialization_start_op = decode(serialization_start_op, 1, X_Operation_Seq_Num, null)
891 where wip_entity_id = X_Wip_Entity_Id;
892
893 --Bug#14835310 : Added not exists condition so that when pending transactions exist, the operation_seq_num is not updated
894 --Quantity_issued should be equal to 0 so that already transacted components are not updated
895 --Bug#16373586: Updating only when no pending transactions exist in MMTT. MTI is not checked here as customers can directly insert into MTI
896 -- Also, the source_type should not be WIP. In case of backflush transfer, the source type is INV.
897 UPDATE WIP_REQUIREMENT_OPERATIONS WRO
898 SET OPERATION_SEQ_NUM = SIGN(OPERATION_SEQ_NUM) *
899 X_Operation_Seq_Num,
900 DEPARTMENT_ID = X_Department_Id,
901 DATE_REQUIRED = X_First_Unit_Start_Date
902 WHERE ORGANIZATION_ID = X_Organization_Id
903 AND WIP_ENTITY_ID = X_Wip_Entity_Id
904 AND OPERATION_SEQ_NUM in (1,-1)
905 AND QUANTITY_ISSUED=0
906 AND NOT EXISTS (SELECT 1 FROM MTL_MATERIAL_TRANSACTIONS_TEMP MMTT
910 UPDATE WIP_REQUIREMENT_OPERATIONS wro
907 WHERE MMTT.TRANSACTION_SOURCE_ID=X_Wip_Entity_Id and MMTT.organization_id=X_Organization_Id and MMTT.operation_seq_num in (-1,1)
908 and MMTT.inventory_item_id=WRO.INVENTORY_ITEM_ID AND mmtt.transaction_source_type_id=INV_Globals.G_SOURCETYPE_WIP);
909 ELSE
911 SET OPERATION_SEQ_NUM = SIGN(OPERATION_SEQ_NUM) *
912 X_Operation_Seq_Num,
913 DEPARTMENT_ID = X_Department_Id,
914 DATE_REQUIRED = X_First_Unit_Start_Date
915 WHERE ORGANIZATION_ID = X_Organization_Id
916 AND WIP_ENTITY_ID = X_Wip_Entity_Id
917 AND REPETITIVE_SCHEDULE_ID = X_Repetitive_Schedule_Id
918 AND OPERATION_SEQ_NUM IN (1,-1)
919 AND QUANTITY_ISSUED=0
920 AND NOT EXISTS (SELECT 1 FROM MTL_MATERIAL_TRANSACTIONS_TEMP MMTT
921 WHERE TRANSACTION_SOURCE_ID=X_Wip_Entity_Id and MMTT.organization_id=X_Organization_Id AND MMTT.repetitive_line_id=X_Repetitive_Schedule_Id and
922 MMTT.operation_seq_num in (-1,1) and MMTT.inventory_item_id=WRO.INVENTORY_ITEM_ID and mmtt.transaction_source_type_id=INV_Globals.G_SOURCETYPE_WIP);
923
924 END IF;
925 END Update_Operationless_Reqs;
926
927 PROCEDURE Update_Reqs(X_Wip_Entity_Id NUMBER,
928 X_Organization_Id NUMBER,
929 X_Operation_Seq_Num NUMBER,
930 X_Repetitive_Schedule_Id NUMBER,
931 X_Department_Id NUMBER,
932 X_Start_Date DATE) IS
933 BEGIN
934 /* Added nvl(X_department_id) for bug 5979782 (base bug 5657251)*/
935 IF X_Repetitive_Schedule_Id IS NULL THEN
936 UPDATE WIP_REQUIREMENT_OPERATIONS
937 SET DEPARTMENT_ID = nvl(X_Department_Id,department_id),
938 --Start : Fix for bug #5177994/5094448 --
939 DATE_REQUIRED = trunc(NVL(X_Start_Date,DATE_REQUIRED)),
940 MPS_DATE_REQUIRED = trunc(NVL(X_Start_Date,MPS_DATE_REQUIRED))
941 -- DATE_REQUIRED = X_Start_Date,
942 -- MPS_DATE_REQUIRED = X_Start_Date
943 --End : Fix for bug #5177994/5094448 --
944 WHERE ORGANIZATION_ID = X_Organization_Id
945 AND WIP_ENTITY_ID = X_Wip_Entity_Id
946 AND (OPERATION_SEQ_NUM = X_Operation_Seq_Num
947 OR OPERATION_SEQ_NUM = -1 * X_Operation_Seq_Num);
948 ELSE
949 UPDATE WIP_REQUIREMENT_OPERATIONS
950 SET DEPARTMENT_ID = X_Department_Id,
951 --Start : Fix for bug #5177994/5094448 --
952 DATE_REQUIRED = trunc(NVL(X_Start_Date,DATE_REQUIRED)),
953 MPS_DATE_REQUIRED = trunc(NVL(X_Start_Date,MPS_DATE_REQUIRED))
954 -- DATE_REQUIRED = X_Start_Date,
955 -- MPS_DATE_REQUIRED = X_Start_Date
956 --End : Fix for bug #5177994/5094448 --
957 WHERE ORGANIZATION_ID = X_Organization_Id
958 AND WIP_ENTITY_ID = X_Wip_Entity_Id
959 AND REPETITIVE_SCHEDULE_ID = X_Repetitive_Schedule_Id
960 AND (OPERATION_SEQ_NUM = X_Operation_Seq_Num
961 OR OPERATION_SEQ_NUM = -1 * X_Operation_Seq_Num);
962 END IF;
963 END Update_Reqs;
964
965 PROCEDURE Get_Prev_Op_Dates(X_Wip_Entity_Id NUMBER,
966 X_Organization_Id NUMBER,
967 X_Prev_Operation_Seq_Num NUMBER,
968 X_Repetitive_Schedule_Id NUMBER,
969 X_First_Unit_Start_Date OUT NOCOPY DATE,
970 X_Last_Unit_Start_Date OUT NOCOPY DATE,
971 X_First_Unit_Completion_Date OUT NOCOPY DATE,
972 X_Last_Unit_Completion_Date OUT NOCOPY DATE) IS
973 BEGIN
974 IF X_Repetitive_Schedule_Id IS NULL THEN
975 SELECT FIRST_UNIT_COMPLETION_DATE,
976 LAST_UNIT_COMPLETION_DATE,
977 FIRST_UNIT_COMPLETION_DATE,
978 LAST_UNIT_COMPLETION_DATE
979 INTO X_First_Unit_Start_Date,
980 X_Last_Unit_Start_Date,
981 X_First_Unit_Completion_Date,
982 X_Last_Unit_Completion_Date
983 FROM WIP_OPERATIONS
984 WHERE ORGANIZATION_ID = X_Organization_Id
985 AND WIP_ENTITY_ID = X_Wip_Entity_Id
986 AND OPERATION_SEQ_NUM = X_Prev_Operation_Seq_Num;
987 ELSE
988 SELECT FIRST_UNIT_COMPLETION_DATE,
989 LAST_UNIT_COMPLETION_DATE,
990 FIRST_UNIT_COMPLETION_DATE,
991 LAST_UNIT_COMPLETION_DATE
992 INTO X_First_Unit_Start_Date,
993 X_Last_Unit_Start_Date,
994 X_First_Unit_Completion_Date,
995 X_Last_Unit_Completion_Date
996 FROM WIP_OPERATIONS
997 WHERE ORGANIZATION_ID = X_Organization_Id
998 AND WIP_ENTITY_ID = X_Wip_Entity_Id
999 AND REPETITIVE_SCHEDULE_ID = X_Repetitive_Schedule_Id
1000 AND OPERATION_SEQ_NUM = X_Prev_Operation_Seq_Num;
1001 END IF;
1002 END Get_Prev_Op_Dates;
1003
1004 PROCEDURE Update_Res_Op_Seq(X_Wip_Entity_Id NUMBER,
1005 X_Organization_Id NUMBER,
1006 X_Old_Operation_Seq_Num NUMBER,
1007 X_New_Operation_Seq_Num NUMBER,
1008 X_Repetitive_Schedule_Id NUMBER) IS
1009 BEGIN
1010 IF X_Repetitive_Schedule_Id IS NULL THEN
1011 UPDATE WIP_OPERATION_RESOURCES
1012 SET OPERATION_SEQ_NUM = X_New_Operation_Seq_num
1013 WHERE ORGANIZATION_ID = X_Organization_Id
1014 AND WIP_ENTITY_ID = X_Wip_Entity_Id
1015 AND OPERATION_SEQ_NUM = X_Old_Operation_Seq_Num;
1016 ELSE
1017 UPDATE WIP_OPERATION_RESOURCES
1021 AND REPETITIVE_SCHEDULE_ID = X_Repetitive_Schedule_Id
1018 SET OPERATION_SEQ_NUM = X_New_Operation_Seq_num
1019 WHERE ORGANIZATION_ID = X_Organization_Id
1020 AND WIP_ENTITY_ID = X_Wip_Entity_Id
1022 AND OPERATION_SEQ_NUM = X_Old_Operation_Seq_Num;
1023 END IF;
1024 END Update_Res_Op_Seq;
1025
1026 FUNCTION Other_Active_Schedules(X_Wip_Entity_Id NUMBER,
1027 X_Org_Id NUMBER,
1028 X_Line_Id NUMBER) RETURN VARCHAR IS
1029 X_Count NUMBER;
1030 cursor get_schedules is
1031 SELECT COUNT(*)
1032 FROM WIP_REPETITIVE_SCHEDULES
1033 WHERE WIP_ENTITY_ID = X_Wip_Entity_Id
1034 AND ORGANIZATION_ID = X_Org_Id
1035 AND LINE_ID = X_Line_Id
1036 AND STATUS_TYPE in (3,4,6);
1037 BEGIN
1038 open get_schedules;
1039 fetch get_schedules into X_Count;
1040 close get_schedules;
1041 IF (X_Count > 1) THEN
1042 RETURN 'Y';
1043 ELSIF (X_Count = 1) THEN
1044 RETURN 'N';
1045 ELSE
1046 -- This should never happen
1047 APP_EXCEPTION.RAISE_EXCEPTION;
1048 END IF;
1049 END Other_Active_Schedules;
1050
1051 PROCEDURE rollback_database IS
1052 BEGIN
1053 ROLLBACK;
1054 END rollback_database;
1055
1056 END WIP_OPERATIONS_UTILITIES;
[Home] [Help]