1 PACKAGE BODY WIP_FIX_REQ_OPS_PKG AS
2 /* $Header: wiprqfxb.pls 115.7 2002/12/12 15:52:21 rmahidha ship $ */
3
4 PROCEDURE Fix(X_Wip_Entity_Id NUMBER,
5 X_Organization_Id NUMBER,
6 X_Repetitive_Schedule_Id NUMBER,
7 X_Entity_Start_Date DATE) IS
8 CURSOR Cdisc IS
9 SELECT DISTINCT ABS(OPERATION_SEQ_NUM) OPERATION_SEQ_NUM
10 FROM WIP_REQUIREMENT_OPERATIONS WRO
11 WHERE WRO.WIP_ENTITY_ID = X_Wip_Entity_Id
12 AND WRO.ORGANIZATION_ID = X_Organization_Id
13 AND NOT EXISTS
14 (SELECT 'op exists'
15 FROM WIP_OPERATIONS WO
16 WHERE WO.WIP_ENTITY_ID = WRO.WIP_ENTITY_ID
17 AND WO.ORGANIZATION_ID = WRO.ORGANIZATION_ID
18 AND WO.OPERATION_SEQ_NUM = ABS(WRO.OPERATION_SEQ_NUM));
19 /* Bug fix 1304144*/
20 CURSOR Crep IS
21 SELECT DISTINCT OPERATION_SEQ_NUM
22 FROM WIP_REQUIREMENT_OPERATIONS WRO
23 WHERE WRO.WIP_ENTITY_ID = X_Wip_Entity_Id
24 AND WRO.ORGANIZATION_ID = X_Organization_Id
25 AND WRO.REPETITIVE_SCHEDULE_ID = X_Repetitive_Schedule_Id
26 AND NOT EXISTS
27 (SELECT 'op exists'
28 FROM WIP_OPERATIONS WO
29 WHERE WO.WIP_ENTITY_ID = WRO.WIP_ENTITY_ID
30 AND WO.ORGANIZATION_ID = WRO.ORGANIZATION_ID
31 AND WO.REPETITIVE_SCHEDULE_ID = WRO.REPETITIVE_SCHEDULE_ID
32 AND WO.OPERATION_SEQ_NUM = WRO.OPERATION_SEQ_NUM);
33
34 BEGIN
35 IF X_Repetitive_Schedule_Id IS NULL THEN
36 /* Just update the department and date_required if the op exists */
37
38 UPDATE WIP_REQUIREMENT_OPERATIONS WRO
39 SET (WRO.DEPARTMENT_ID, WRO.DATE_REQUIRED) =
40 (SELECT DEPARTMENT_ID, FIRST_UNIT_START_DATE
41 FROM WIP_OPERATIONS WO
42 WHERE WO.WIP_ENTITY_ID = WRO.WIP_ENTITY_ID
43 AND WO.OPERATION_SEQ_NUM = WRO.OPERATION_SEQ_NUM
44 AND WO.ORGANIZATION_ID = WRO.ORGANIZATION_ID)
45 WHERE WRO.WIP_ENTITY_ID = X_Wip_Entity_Id
46 AND WRO.ORGANIZATION_ID = X_Organization_Id
47 AND EXISTS
48 (SELECT 'operation exists'
49 FROM WIP_OPERATIONS WO
50 WHERE WO.WIP_ENTITY_ID = WRO.WIP_ENTITY_ID
51 AND WO.OPERATION_SEQ_NUM = WRO.OPERATION_SEQ_NUM
52 AND WO.ORGANIZATION_ID = WRO.ORGANIZATION_ID);
53
54 /* Otherwise treat the same as a deleted operation */
55
56 FOR C1 IN Cdisc LOOP
57 WIP_OPERATIONS_UTILITIES.Check_Requirements(
58 X_Wip_Entity_Id,
59 X_Organization_Id,
60 C1.operation_seq_num,
61 X_Repetitive_Schedule_Id,
62 X_Entity_Start_Date
63 );
64 END LOOP;
65 ELSE
66 UPDATE WIP_REQUIREMENT_OPERATIONS WRO
67 SET (WRO.DEPARTMENT_ID, WRO.DATE_REQUIRED) =
68 (SELECT DEPARTMENT_ID, FIRST_UNIT_START_DATE
69 FROM WIP_OPERATIONS WO
70 WHERE WO.WIP_ENTITY_ID = WRO.WIP_ENTITY_ID
71 AND WO.OPERATION_SEQ_NUM = WRO.OPERATION_SEQ_NUM
72 AND WO.REPETITIVE_SCHEDULE_ID = WRO.REPETITIVE_SCHEDULE_ID
73 AND WO.ORGANIZATION_ID = WRO.ORGANIZATION_ID)
74 WHERE WRO.WIP_ENTITY_ID = X_Wip_Entity_Id
75 AND WRO.ORGANIZATION_ID = X_Organization_Id
76 AND WRO.REPETITIVE_SCHEDULE_ID = X_Repetitive_Schedule_Id
77 AND EXISTS
78 (SELECT 'operation exists'
79 FROM WIP_OPERATIONS WO
80 WHERE WO.WIP_ENTITY_ID = WRO.WIP_ENTITY_ID
81 AND WO.OPERATION_SEQ_NUM = WRO.OPERATION_SEQ_NUM
82 AND WO.REPETITIVE_SCHEDULE_ID = WRO.REPETITIVE_SCHEDULE_ID
83 AND WO.ORGANIZATION_ID = WRO.ORGANIZATION_ID);
84
85 FOR C1 IN Crep LOOP
86 WIP_OPERATIONS_UTILITIES.Check_Requirements(
87 X_Wip_Entity_Id,
88 X_Organization_Id,
89 C1.operation_seq_num,
90 X_Repetitive_Schedule_Id,
91 X_Entity_Start_Date
92 );
93 END LOOP;
94 END IF;
95 END;
96
97 END WIP_FIX_REQ_OPS_PKG;
[Home] [Help]