[Home] [Help]
PACKAGE BODY: APPS.WIP_OPERATIONS_INFO
Source
1 PACKAGE BODY WIP_OPERATIONS_INFO AS
2 /* $Header: wipopinb.pls 120.2 2006/03/01 16:35:22 hshu noship $ */
3
4 procedure derive_info(
5 p_org_id in number,
6 p_wip_entity_id in number,
7 p_first_schedule_id in number,
8 p_operation_seq_num in number,
9 p_operation_code out nocopy varchar2,
10 p_department_id out nocopy number,
11 p_department_code out nocopy varchar2,
12 p_prev_op_seq_num out nocopy number,
13 p_next_op_seq_num out nocopy number,
14 p_operation_exists out nocopy boolean) is
15 x_operation_exists boolean;
16 begin
17 begin
18 select bso.operation_code,
19 bd.department_code,
20 wo.department_id,
21 wo.previous_operation_seq_num,
22 wo.next_operation_seq_num
23 into p_operation_code,
24 p_department_code,
25 p_department_id,
26 p_prev_op_seq_num,
27 p_next_op_seq_num
28 from bom_standard_operations bso,
29 bom_departments bd,
30 wip_operations wo
31 where wo.organization_id = p_org_id
32 /* %cfm Ignore cfm ops. */
33 and nvl(bso.operation_type, 1) = 1
34 and bso.line_id is null
35 /* %/cfm */
36 and wo.wip_entity_id = p_wip_entity_id
37 and wo.operation_seq_num = p_operation_seq_num
38 and (wo.repetitive_schedule_id is null
39 or
40 wo.repetitive_schedule_id = p_first_schedule_id)
41 and bso.organization_id (+) = wo.organization_id
42 and bso.standard_operation_id (+) = wo.standard_operation_id
43 and bd.organization_id = wo.organization_id
44 and bd.department_id = wo.department_id;
45
46 x_operation_exists := TRUE;
47 exception
48 when NO_DATA_FOUND then
49 p_operation_code := NULL;
50 p_department_id := NULL;
51 p_department_code := NULL;
52 x_operation_exists := FALSE;
53 end;
54
55 -- get previous and next operation for a newly added operation
56 if (not x_operation_exists) then
57 select max(wo1.operation_seq_num),
58 min(wo2.operation_seq_num)
59 into p_prev_op_seq_num,
60 p_next_op_seq_num
61 from dual sd,
62 wip_operations wo1,
63 wip_operations wo2
64 where wo1.organization_id(+) = p_org_id
65 and wo1.wip_entity_id(+) = decode(1, 1, p_wip_entity_id, sd.dummy)
66 and wo1.operation_seq_num(+) < p_operation_seq_num
67 and wo2.organization_id(+) = p_org_id
68 and wo2.wip_entity_id(+) = decode(1, 1, p_wip_entity_id, sd.dummy)
69 and wo2.operation_seq_num(+) > p_operation_seq_num;
70 end if;
71
72 p_operation_exists := x_operation_exists;
73 return;
74
75 exception
76 when others then
77 wip_constants.get_ora_error(
78 application => 'WIP',
79 proc_name => 'WIP_OPERATIONS_PKG.DERIVE_INFO');
80 fnd_message.raise_error;
81 end derive_info;
82
83 procedure last_operation(
84 p_org_id in number,
85 p_wip_entity_id in number,
86 p_line_id in number,
87 p_first_schedule_id in number,
88 p_last_op_seq out nocopy number,
89 p_last_op_code out nocopy varchar2,
90 p_last_dept_id out nocopy number,
91 p_last_dept_code out nocopy varchar2,
92 p_last_op_move_quantity out nocopy number,
93 p_last_op_min_transfer_qty out nocopy number,
94 p_last_move_allowed out nocopy number) is
95
96 -- cursors to get last operation and shop floor status information
97 cursor get_last_operation_yes(
98 c_org_id number,
99 c_wip_entity_id number,
100 c_line_id number,
101 c_first_schedule_id number) is
102 select distinct
103 wo.operation_seq_num,
104 bso.operation_code,
105 wo.department_id,
106 bd.department_code,
107 wo.minimum_transfer_quantity,
108 wo.quantity_waiting_to_move,
109 WIP_CONSTANTS.YES allow_moves
110 from bom_standard_operations bso,
111 bom_departments bd,
112 wip_operations wo
113 where wo.operation_seq_num =
114 (select max(operation_seq_num)
115 from wip_operations wo1
116 where wo1.organization_id = wo.organization_id
117 and wo1.wip_entity_id = wo.wip_entity_id
118 and (wo1.repetitive_schedule_id is NULL
119 or
120 wo1.repetitive_schedule_id = c_first_schedule_id))
121 /* %cfm Ignore cfm ops. */
122 and nvl(bso.operation_type, 1) = 1
123 and bso.line_id is null
124 /* %/cfm */
125 and wo.department_id = bd.department_id
126 and wo.standard_operation_id = bso.standard_operation_id (+)
127 and wo.organization_id = c_org_id
128 and wo.wip_entity_id = c_wip_entity_id
129 and (wo.repetitive_schedule_id is NULL
130 or
131 wo.repetitive_schedule_id = c_first_schedule_id)
132 and not exists
133 (select 'No move status exists'
134 from wip_shop_floor_statuses ws,
135 wip_shop_floor_status_codes wsc
136 where wsc.organization_id = wo.organization_id
137 and ws.organization_id = wo.organization_id
138 and ws.wip_entity_id = wo.wip_entity_id
139 and (ws.line_id is NULL
140 or
141 ws.line_id = c_line_id)
142 and ws.operation_seq_num = wo.operation_seq_num
143 and ws.intraoperation_step_type = WIP_CONSTANTS.TOMOVE
144 and ws.shop_floor_status_code = wsc.shop_floor_status_code
145 and wsc.status_move_flag = WIP_CONSTANTS.NO
146 and nvl(wsc.disable_date, SYSDATE + 1) > SYSDATE);
147
148 cursor get_last_operation_no(
149 c_org_id number,
150 c_wip_entity_id number,
151 c_line_id number,
152 c_first_schedule_id number) is
153 select distinct
154 wo.operation_seq_num,
155 bso.operation_code,
156 wo.department_id,
157 bd.department_code,
158 wo.minimum_transfer_quantity,
159 wo.quantity_waiting_to_move,
160 WIP_CONSTANTS.NO allow_moves
161 from bom_standard_operations bso,
162 bom_departments bd,
163 wip_operations wo
164 where wo.operation_seq_num =
165 (select max(operation_seq_num)
166 from wip_operations wo1
167 where wo1.organization_id = wo.organization_id
168 and wo1.wip_entity_id = wo.wip_entity_id
169 and (wo1.repetitive_schedule_id is NULL
170 or
171 wo1.repetitive_schedule_id = c_first_schedule_id))
172 /* %cfm Ignore cfm ops. */
173 and nvl(bso.operation_type, 1) = 1
174 and bso.line_id is null
175 /* %/cfm */
176 and wo.department_id = bd.department_id
177 and wo.standard_operation_id = bso.standard_operation_id (+)
178 and wo.organization_id = c_org_id
179 and wo.wip_entity_id = c_wip_entity_id
180 and (wo.repetitive_schedule_id is NULL
181 or
182 wo.repetitive_schedule_id = c_first_schedule_id)
183 and exists
184 (select 'Move status exists'
185 from wip_shop_floor_statuses ws,
186 wip_shop_floor_status_codes wsc
187 where wsc.organization_id = wo.organization_id
188 and ws.organization_id = wo.organization_id
189 and ws.wip_entity_id = wo.wip_entity_id
190 and (ws.line_id is NULL
191 or
192 ws.line_id = c_line_id)
193 and ws.operation_seq_num = wo.operation_seq_num
194 and ws.intraoperation_step_type = WIP_CONSTANTS.TOMOVE
195 and ws.shop_floor_status_code = wsc.shop_floor_status_code
196 and wsc.status_move_flag = WIP_CONSTANTS.NO
197 and nvl(wsc.disable_date, SYSDATE + 1) > SYSDATE);
198
199 yes_vals get_last_operation_yes%ROWTYPE;
200 no_vals get_last_operation_no%ROWTYPE;
201 begin
202 open get_last_operation_yes(
203 c_org_id => p_org_id,
204 c_wip_entity_id => p_wip_entity_id,
205 c_line_id => p_line_id,
206 c_first_schedule_id => p_first_schedule_id);
207 open get_last_operation_no(
208 c_org_id => p_org_id,
209 c_wip_entity_id => p_wip_entity_id,
210 c_line_id => p_line_id,
211 c_first_schedule_id => p_first_schedule_id);
212
213 fetch get_last_operation_yes into yes_vals;
214 fetch get_last_operation_no into no_vals;
215 if (get_last_operation_yes%FOUND) then
216 p_last_op_seq := yes_vals.operation_seq_num;
217 p_last_op_code := yes_vals.operation_code;
218 p_last_dept_id := yes_vals.department_id;
219 p_last_dept_code := yes_vals.department_code;
220 p_last_op_min_transfer_qty := yes_vals.minimum_transfer_quantity;
221 p_last_op_move_quantity := yes_vals.quantity_waiting_to_move;
222 p_last_move_allowed := WIP_CONSTANTS.YES;
223 elsif (get_last_operation_no%FOUND) then
224 p_last_op_seq := no_vals.operation_seq_num;
225 p_last_op_code := no_vals.operation_code;
226 p_last_dept_id := no_vals.department_id;
227 p_last_dept_code := no_vals.department_code;
228 p_last_op_min_transfer_qty := no_vals.minimum_transfer_quantity;
229 p_last_op_move_quantity := no_vals.quantity_waiting_to_move;
230 p_last_move_allowed := WIP_CONSTANTS.NO;
231 else
232 p_last_op_seq := -1;
233 p_last_op_code := NULL;
234 p_last_dept_id := NULL;
235 p_last_dept_code := NULL;
236 p_last_op_min_transfer_qty := NULL;
237 p_last_op_move_quantity := NULL;
238 p_last_move_allowed := WIP_CONSTANTS.YES;
239 end if;
240
241 close get_last_operation_yes;
242 close get_last_operation_no;
243
244 return;
245 end last_operation;
246
247 /*=====================================================================+
248 | PROCEDURE
249 | FIRST_OPERATION
250 */
251
252 procedure first_operation(
253 p_org_id in number,
254 p_wip_entity_id in number,
255 p_line_id in number,
256 p_first_schedule_id in number,
257 p_first_op_seq out nocopy number,
258 p_first_op_code out nocopy varchar2,
259 p_first_dept_id out nocopy number,
260 p_first_dept_code out nocopy varchar2) is
261
262 -- cursor to get first operation and shop floor status information
263 cursor get_first_operation(
264 c_org_id number,
265 c_wip_entity_id number,
266 c_line_id number,
267 c_first_schedule_id number) is
268 select distinct
269 wo.operation_seq_num,
270 bso.operation_code,
271 wo.department_id,
272 bd.department_code
273 from bom_standard_operations bso,
274 bom_departments bd,
275 wip_operations wo
276 where wo.operation_seq_num =
277 (select min(operation_seq_num)
278 from wip_operations wo1
279 where wo1.organization_id = wo.organization_id
280 and wo1.wip_entity_id = wo.wip_entity_id
281 and (wo1.repetitive_schedule_id is NULL
282 or
283 wo1.repetitive_schedule_id = c_first_schedule_id))
284 /* %cfm Ignore cfm ops. */
285 and nvl(bso.operation_type, 1) = 1
286 and bso.line_id is null
287 /* %/cfm */
288 and wo.department_id = bd.department_id
289 and wo.standard_operation_id = bso.standard_operation_id (+)
290 and wo.organization_id = c_org_id
291 and wo.wip_entity_id = c_wip_entity_id
292 and (wo.repetitive_schedule_id is NULL
293 or
294 wo.repetitive_schedule_id = c_first_schedule_id);
295 begin
296 open get_first_operation
297 (
298 c_org_id => p_org_id,
299 c_wip_entity_id => p_wip_entity_id,
300 c_line_id => p_line_id,
301 c_first_schedule_id => p_first_schedule_id);
302
303 fetch get_first_operation into
304 p_first_op_seq,
305 p_first_op_code,
306 p_first_dept_id,
307 p_first_dept_code;
308
309 if (get_first_operation%NOTFOUND) then
310 p_first_op_seq := -1;
311 p_first_op_code := NULL;
312 p_first_dept_id := NULL;
313 p_first_dept_code := NULL;
314 end if;
315
316 close get_first_operation;
317
318 return;
319
320 end first_operation;
321
322
323 END WIP_OPERATIONS_INFO;