1 PACKAGE BODY WIP_SF_STATUS AS
2 /* $Header: wipsfstb.pls 120.2 2006/01/10 18:04:37 kboonyap noship $ */
3
4 PROCEDURE INSERT_STATUS
5 (P_wip_entity_id IN NUMBER,
6 P_organization_id IN NUMBER,
7 P_line_id IN NUMBER,
8 P_operation_seq_num IN NUMBER,
9 P_intraoperation_step_type IN NUMBER,
10 P_shop_floor_status IN VARCHAR2) IS
11
12 X_user_id NUMBER := FND_GLOBAL.USER_ID;
13 X_login_id NUMBER := FND_GLOBAL.LOGIN_ID;
14
15 BEGIN
16
17 INSERT INTO WIP_SHOP_FLOOR_STATUSES
18 (WIP_ENTITY_ID, ORGANIZATION_ID,
19 OPERATION_SEQ_NUM, SHOP_FLOOR_STATUS_CODE,
20 LINE_ID, INTRAOPERATION_STEP_TYPE,
21 LAST_UPDATE_DATE, LAST_UPDATED_BY,
22 CREATION_DATE, CREATED_BY,
23 LAST_UPDATE_LOGIN, ATTRIBUTE_CATEGORY,
24 ATTRIBUTE1, ATTRIBUTE2, ATTRIBUTE3, ATTRIBUTE4,
25 ATTRIBUTE5, ATTRIBUTE6, ATTRIBUTE7, ATTRIBUTE8,
26 ATTRIBUTE9, ATTRIBUTE10, ATTRIBUTE11, ATTRIBUTE12,
27 ATTRIBUTE13, ATTRIBUTE14, ATTRIBUTE15)
28 SELECT
29 P_wip_entity_id, P_organization_id,
30 P_operation_seq_num, P_shop_floor_status,
31 P_line_id, P_intraoperation_step_type,
32 SYSDATE, X_user_id,
33 SYSDATE, X_user_id,
34 X_login_id, ATTRIBUTE_CATEGORY,
35 ATTRIBUTE1, ATTRIBUTE2, ATTRIBUTE3, ATTRIBUTE4,
36 ATTRIBUTE5, ATTRIBUTE6, ATTRIBUTE7, ATTRIBUTE8,
37 ATTRIBUTE9, ATTRIBUTE10, ATTRIBUTE11, ATTRIBUTE12,
38 ATTRIBUTE13, ATTRIBUTE14, ATTRIBUTE15
39 FROM WIP_SHOP_FLOOR_STATUS_CODES
40 WHERE ORGANIZATION_ID = P_organization_id
41 AND SHOP_FLOOR_STATUS_CODE = P_shop_floor_status
42 AND NOT EXISTS
43 (SELECT 'testing for duplicates'
44 FROM WIP_SHOP_FLOOR_STATUSES
45 WHERE WIP_ENTITY_ID = P_wip_entity_id
46 AND OPERATION_SEQ_NUM = P_operation_seq_num
47 AND SHOP_FLOOR_STATUS_CODE = P_shop_floor_status
48 AND ORGANIZATION_ID = P_organization_id
49 AND NVL(LINE_ID, -1) = NVL(P_line_id, -1)
50 AND INTRAOPERATION_STEP_TYPE = P_intraoperation_step_type)
51 AND (EXISTS(
52 SELECT 'job_exists'
53 FROM WIP_DISCRETE_JOBS wdj
54 WHERE wdj.wip_entity_id = P_wip_entity_id
55 AND wdj.organization_id = P_organization_id)
56 OR EXISTS(
57 SELECT 'schedule exists'
58 FROM wip_repetitive_schedules wrs
59 WHERE wrs.line_id = P_line_id
60 AND wrs.organization_id = P_organization_id
61 AND wrs.wip_entity_id = P_wip_entity_id));
62 END INSERT_STATUS;
63
64 PROCEDURE DELETE_STATUS(
65 P_wip_entity_id IN NUMBER,
66 P_organization_id IN NUMBER,
67 P_line_id IN NUMBER,
68 P_operation_seq_num IN NUMBER,
69 P_intraoperation_step_type IN NUMBER,
70 P_shop_floor_status IN VARCHAR2) IS
71
72 BEGIN
73
74 DELETE FROM WIP_SHOP_FLOOR_STATUSES WSFS
75 WHERE
76 WSFS.wip_entity_id = P_wip_entity_id
77 AND WSFS.organization_id = P_organization_id
78 AND WSFS.operation_seq_num = P_operation_seq_num
79 AND WSFS.intraoperation_step_type = P_intraoperation_step_type
80 AND NVL(WSFS.line_id, -1) = NVL(P_line_id, -1)
81 AND WSFS.shop_floor_status_code = P_shop_floor_status;
82
83 END DELETE_STATUS;
84
85 PROCEDURE ATTACH
86 (P_wip_entity_id NUMBER,
87 P_organization_id NUMBER,
88 P_line_id NUMBER,
89 P_operation_seq_num NUMBER,
90 P_intraoperation_step_type NUMBER,
91 P_shop_floor_status VARCHAR2) IS
92
93 X_test_cursor VARCHAR2(30) := '';
94 X_return_value BOOLEAN := TRUE;
95
96 CURSOR disc_info IS
97 SELECT 'is there a valid job'
98 FROM WIP_DISCRETE_JOBS
99 WHERE WIP_ENTITY_ID = P_wip_entity_id
100 AND ORGANIZATION_ID = P_organization_id
101 AND STATUS_TYPE IN (WIP_CONSTANTS.UNRELEASED, WIP_CONSTANTS.RELEASED,
102 WIP_CONSTANTS.COMP_CHRG, WIP_CONSTANTS.HOLD);
103
104 CURSOR rep_info IS
105 SELECT 'is there a valid schedule'
106 FROM WIP_FIRST_OPEN_SCHEDULE_V
107 WHERE ORGANIZATION_ID = P_organization_id
108 AND WIP_ENTITY_ID = P_wip_entity_id
109 AND LINE_ID = P_line_id;
110
111 CURSOR step_info IS
112 SELECT 'is the step enabled'
113 FROM wip_valid_intraoperation_steps
114 WHERE organization_id = P_organization_id
115 AND step_lookup_type = P_intraoperation_step_type
116 AND ((P_intraoperation_step_type = WIP_CONSTANTS.TOMOVE AND
117 record_creator = 'USER')
118 OR
119 (P_intraoperation_step_type <> WIP_CONSTANTS.TOMOVE));
120
121 CURSOR status_info IS
122 SELECT 'is the status valid'
123 FROM WIP_SHOP_FLOOR_STATUS_CODES
124 WHERE SHOP_FLOOR_STATUS_CODE = P_shop_floor_status
125 AND ORGANIZATION_ID = P_organization_id
126 AND NVL(DISABLE_DATE, SYSDATE + 1) > SYSDATE;
127
128
129 BEGIN
130 IF (P_line_id IS NULL) THEN
131 OPEN disc_info;
132 FETCH disc_info INTO X_test_cursor;
133 IF disc_info%NOTFOUND THEN
134 FND_MESSAGE.SET_NAME('WIP', 'WIP_QA_ACTION_NO_ASSIGN_JOB');
135 X_return_value := FALSE;
136 END IF;
137 CLOSE disc_info;
138 ELSE
139 OPEN rep_info;
140 FETCH rep_info INTO X_test_cursor;
141 IF rep_info%NOTFOUND THEN
142 X_return_value := FALSE;
143 END IF;
144 CLOSE rep_info;
145 END IF;
146 IF X_return_value THEN
147 OPEN step_info;
148 FETCH STEP_INFO INTO X_test_cursor;
149 IF step_info%NOTFOUND THEN
150 FND_MESSAGE.SET_NAME('WIP', 'WIP_QA_ACTION_NO_ASSIGN_STEP');
151 X_return_value := FALSE;
152 END IF;
153 CLOSE step_info;
154 END IF;
155 IF X_return_value THEN
156 OPEN status_info;
157 FETCH status_info INTO X_test_cursor;
158 IF status_info%NOTFOUND THEN
159 FND_MESSAGE.SET_NAME('WIP', 'WIP_QA_ACTION_NO_ASSIGN_SFS');
160 X_return_value := FALSE;
161 END IF;
162 CLOSE status_info;
163 END IF;
164 IF (X_return_value) THEN
165 INSERT_STATUS (
166 P_wip_entity_id,
167 P_organization_id,
168 P_line_id,
169 P_operation_seq_num,
170 P_intraoperation_step_type,
171 P_shop_floor_status);
172 END IF;
173 IF NOT X_return_value THEN
174 APP_EXCEPTION.RAISE_EXCEPTION;
175 END IF;
176 END ATTACH;
177
178 FUNCTION GetOSPStatus (p_org_id NUMBER) return VARCHAR2 is
179 l_osp_shop_floor_status VARCHAR2(10);
180
181 cursor get_status (c_org_id number) is
182 select osp_shop_floor_status
183 from wip_parameters wp
184 where wp.organization_id = c_org_id;
185 BEGIN
186 OPEN get_status (p_org_id);
187 FETCH get_status into l_osp_shop_floor_status;
188
189 if (get_status%NOTFOUND) then
190 l_osp_shop_floor_status := NULL;
191 end if;
192
193 CLOSE get_status;
194 return l_osp_shop_floor_status;
195
196 END GetOSPStatus;
197
198 PROCEDURE CREATE_OSP_STATUS(
199 p_org_id in number,
200 p_wip_entity_id in number,
201 p_repetitive_sched_id in number DEFAULT NULL,
202 p_operation_seq_num in number DEFAULT NULL
203 ) IS
204 l_osp_shop_floor_status VARCHAR2(10);
205 l_line_id NUMBER := NULL;
206
207 CURSOR cwop IS
208 SELECT
209 wo.operation_seq_num
210 FROM wip_operation_resources wo
211 WHERE wo.wip_entity_id = p_wip_entity_id
212 AND wo.organization_id = p_org_id
213 AND nvl (wo.repetitive_schedule_id, -1) = nvl(p_repetitive_sched_id,-1)
214 AND wo.autocharge_type = WIP_CONSTANTS.PO_MOVE;
215
216 BEGIN
217 l_osp_shop_floor_status := GetOSPStatus (p_org_id);
218 l_line_id := wip_repetitive_utilities.get_line_id
219 (p_repetitive_sched_id, p_org_id);
220
221 IF p_operation_seq_num is null then
222 FOR cwop_rec in cwop LOOP
223 INSERT_STATUS (
224 P_wip_entity_id => p_wip_entity_id,
225 P_organization_id => p_org_id,
226 P_line_id => l_line_id,
227 P_operation_seq_num => cwop_rec.operation_seq_num,
228 P_intraoperation_step_type => WIP_CONSTANTS.QUEUE,
229 P_shop_floor_status => l_osp_shop_floor_status);
230 END LOOP;
231 else
232 INSERT_STATUS (
233 P_wip_entity_id => p_wip_entity_id,
234 P_organization_id => p_org_id,
235 P_line_id => l_line_id,
236 P_operation_seq_num => p_operation_seq_num,
237 P_intraoperation_step_type => WIP_CONSTANTS.QUEUE,
238 P_shop_floor_status => l_osp_shop_floor_status);
239
240 end if;
241 END CREATE_OSP_STATUS;
242
243 PROCEDURE REMOVE_OSP_STATUS(
244 p_org_id in number,
245 p_wip_entity_id in number,
246 p_repetitive_sched_id in number DEFAULT NULL,
247 p_operation_seq_num in number DEFAULT NULL
248 ) IS
249 l_osp_shop_floor_status VARCHAR2(10);
250 l_line_id NUMBER;
251
252 CURSOR cwop IS
253 SELECT
254 wo.operation_seq_num
255 FROM wip_operation_resources wo
256 WHERE wo.wip_entity_id = p_wip_entity_id
257 AND wo.organization_id = p_org_id
258 AND nvl (wo.repetitive_schedule_id, -1) = nvl(p_repetitive_sched_id,-1)
259 AND wo.autocharge_type = WIP_CONSTANTS.PO_MOVE;
260
261 BEGIN
262
263 l_osp_shop_floor_status := GetOSPStatus (p_org_id);
264 l_line_id := wip_repetitive_utilities.get_line_id
265 (p_repetitive_sched_id, p_org_id);
266
267 if (p_operation_seq_num is NULL) then
268 FOR cwop_rec in cwop LOOP
269 DELETE_STATUS (
270 p_wip_entity_id => p_wip_entity_id,
271 p_organization_id => p_org_id,
272 p_line_id => l_line_id,
273 p_operation_seq_num => cwop_rec.operation_seq_num,
274 p_intraoperation_step_type => WIP_CONSTANTS.QUEUE,
275 p_shop_floor_status => l_osp_shop_floor_status);
276 END LOOP;
277
278 else
279 DELETE_STATUS(
280 p_wip_entity_id => p_wip_entity_id,
281 p_organization_id => p_org_id,
282 p_line_id => l_line_id,
283 p_operation_seq_num => p_operation_seq_num,
284 p_intraoperation_step_type => WIP_CONSTANTS.QUEUE,
285 p_shop_floor_status => l_osp_shop_floor_status);
286 end if;
287
288 END REMOVE_OSP_STATUS;
289
290 function count_no_move_statuses(
291 p_org_id in number,
292 p_wip_id in number,
293 p_line_id in number,
294 p_sched_id in number,
295 p_fm_op in number,
296 p_fm_step in number,
297 p_to_op in number,
298 p_to_step in number,
299 p_source_code in varchar2 default null) return number is
300
301 x_no_move_count number := 0;
302
303 begin
304 SELECT COUNT(1)
305 INTO x_no_move_count
306 FROM WIP_OPERATIONS WOP,
307 WIP_OPERATION_RESOURCES WOR,
308 WIP_PARAMETERS WP,
309 WIP_SHOP_FLOOR_STATUS_CODES WSFSC,
310 WIP_SHOP_FLOOR_STATUSES WSFS
311 WHERE WSFS.INTRAOPERATION_STEP_TYPE IN (1,/* Queue */
312 2,/* Run */
313 3 /* To Move */)
314 AND WP.ORGANIZATION_ID = p_org_id
315 AND NVL(WP.MOVES_OVER_NO_MOVE_STATUSES, 1) = 2 /* No */
316 /* Fix for bug 2121222 */
317 AND NOT EXISTS
318 (Select 'X' from WIP_OPERATION_RESOURCES WOR1
319 WHERE WOR1.WIP_ENTITY_ID = p_wip_id
320 AND WOR1.ORGANIZATION_ID = p_org_id
321 AND WOR1.OPERATION_SEQ_NUM = p_fm_op
322 AND (WOR1.REPETITIVE_SCHEDULE_ID = p_sched_id
323 OR (WOR1.REPETITIVE_SCHEDULE_ID IS NULL AND p_sched_id IS NULL))
324 AND WOR1.AUTOCHARGE_TYPE = 4
325 AND p_source_code = 'RCV' )
326 AND WSFS.ORGANIZATION_ID = wp.organization_id
327 AND WSFS.WIP_ENTITY_ID = p_wip_id
328 AND (WSFS.LINE_ID = p_line_id
329 OR (WSFS.LINE_ID IS NULL AND p_line_id IS NULL))
330 AND WSFSC.ORGANIZATION_ID = WSFS.ORGANIZATION_ID
331 AND WSFSC.SHOP_FLOOR_STATUS_CODE = WSFS.SHOP_FLOOR_STATUS_CODE
332 AND WSFSC.STATUS_MOVE_FLAG = 2 /* No */
333 AND WOP.ORGANIZATION_ID = WSFS.ORGANIZATION_ID
334 AND WOP.WIP_ENTITY_ID = WSFS.WIP_ENTITY_ID
335 AND (WOP.REPETITIVE_SCHEDULE_ID = p_sched_id
336 OR (WOP.REPETITIVE_SCHEDULE_ID IS NULL AND p_sched_id IS NULL))
337 AND WOP.OPERATION_SEQ_NUM = WSFS.OPERATION_SEQ_NUM
338 AND WOR.ORGANIZATION_ID (+)= WSFS.ORGANIZATION_ID
339 AND WOR.WIP_ENTITY_ID (+)= WSFS.WIP_ENTITY_ID
340 AND WOR.OPERATION_SEQ_NUM (+)= WSFS.OPERATION_SEQ_NUM
341
342 /* Remove 2 statements below because WOR.REPETITIVE_SCHEDULE_ID can be null
343 * even for repetitive because the user do not have to set up resource before
344 * do move transaction while p_schedule_id is always not null because it is
345 * default to first_schedule_id. So we should compare with first_schedule_id
346 * only when this value is not null.
347 */
348 -- AND (WOR.REPETITIVE_SCHEDULE_ID = p_sched_id
349 -- OR (WOR.REPETITIVE_SCHEDULE_ID IS NULL AND p_sched_id IS NULL))
350 AND (WOR.REPETITIVE_SCHEDULE_ID IS NULL OR
351 WOR.REPETITIVE_SCHEDULE_ID = p_sched_id)
352 AND
353 ((/* forward move - different operations */
354 (p_fm_op < p_to_op)
355 AND
356 (/* shop floor status is between from and to operations
357 and at a count point operation */
358 (p_fm_op < WSFS.OPERATION_SEQ_NUM
359 AND WSFS.OPERATION_SEQ_NUM < p_to_op
360 AND WSFS.OPERATION_SEQ_NUM = WOP.OPERATION_SEQ_NUM
361 AND WOP.COUNT_POINT_TYPE IN (1,/* Yes Auto */
362 2 /* No Auto */))
363 OR
364 (/* shop floor status is at the same operation as from operation
365 but after the from intraoperation step */
366 p_fm_op = WSFS.OPERATION_SEQ_NUM
367 AND p_fm_step <= WSFS.INTRAOPERATION_STEP_TYPE)
368 OR
369 (/* shop floor status is at the same operation as to operation
370 but before the to intraoperation step */
371 p_to_op = WSFS.OPERATION_SEQ_NUM
372 AND WSFS.INTRAOPERATION_STEP_TYPE < p_to_step)))
373 OR
374 (/* forward move - same operation */
375 (p_fm_op = p_to_op AND p_fm_step < p_to_step)
376 AND
377 (/* shop floor status is at same operation as from operation but
378 between the from intraoperation step and to intraoperation step */
379 p_fm_op = WSFS.OPERATION_SEQ_NUM
380 AND p_fm_step <= WSFS.INTRAOPERATION_STEP_TYPE
381 AND WSFS.INTRAOPERATION_STEP_TYPE < p_to_step))
382 OR
383 (/* backward move - different operations */
384 (p_fm_op > p_to_op)
385 AND
386 (/* shop floor status is between to and from operations
387 and at a count point operation */
388 (p_fm_op > WSFS.OPERATION_SEQ_NUM
389 AND WSFS.OPERATION_SEQ_NUM > p_to_op
390 AND WSFS.OPERATION_SEQ_NUM = WOP.OPERATION_SEQ_NUM
391 AND WOP.COUNT_POINT_TYPE IN (1,/* Yes Auto */
392 2 /* No Auto */))
393 OR
394 (/* shop floor status is at the same operation as from operation
395 but before the from intraoperation step */
396 p_fm_op = WSFS.OPERATION_SEQ_NUM
397 AND p_fm_step >= WSFS.INTRAOPERATION_STEP_TYPE)
398 OR
399 (/* shop floor status is at the same operation as to operation
400 but after the to intraoperation step */
401 p_to_op = WSFS.OPERATION_SEQ_NUM
402 AND WSFS.INTRAOPERATION_STEP_TYPE > p_to_step)))
403 OR
404 (/* backward move - same operation */
405 (p_fm_op = p_to_op AND p_fm_step > p_to_step)
406 AND
407 (/* shop floor status is at same operation as from operation but
408 between the from intraoperation step and to intraoperation step */
409 p_fm_op = WSFS.OPERATION_SEQ_NUM
410 AND p_fm_step >= WSFS.INTRAOPERATION_STEP_TYPE
411 AND WSFS.INTRAOPERATION_STEP_TYPE > p_to_step)));
412
413 return(x_no_move_count);
414 end count_no_move_statuses;
415
416 FUNCTION count_no_move_last_step(p_org_id IN NUMBER,
417 p_wip_id IN NUMBER) RETURN NUMBER IS
418
419 l_params wip_logger.param_tbl_t;
420 l_returnStatus VARCHAR2(1);
421 l_logLevel NUMBER := fnd_log.g_current_runtime_level;
422 l_no_move_count NUMBER := 0;
423 BEGIN
424 -- write parameter value to log file
425 IF (l_logLevel <= wip_constants.trace_logging) THEN
426 l_params(1).paramName := 'p_org_id';
427 l_params(1).paramValue := p_org_id;
428 l_params(2).paramName := 'p_wip_id';
429 l_params(2).paramValue := p_wip_id;
430 wip_logger.entryPoint(p_procName => 'wip_sf_status.count_no_move_last_step',
431 p_params => l_params,
432 x_returnStatus => l_returnStatus);
433 END IF;
434
435 SELECT count(*)
436 INTO l_no_move_count
437 FROM wip_shop_floor_status_codes wsc,
438 wip_shop_floor_statuses ws,
439 wip_operations wo1
440 WHERE ws.organization_id = p_org_id
441 AND ws.wip_entity_id = p_wip_id
442 AND wsc.organization_id = ws.organization_id
443 AND wo1.operation_seq_num = ws.operation_seq_num
444 AND wo1.organization_id = ws.organization_id
445 AND wo1.wip_entity_id = ws.wip_entity_id
446 AND wo1.operation_seq_num =
447 (SELECT max(wo2.operation_seq_num)
448 FROM wip_operations wo2
449 WHERE wo2.organization_id = wo1.organization_id
450 AND wo2.wip_entity_id = wo1.wip_entity_id)
451 AND ws.intraoperation_step_type = WIP_CONSTANTS.TOMOVE
452 AND ws.shop_floor_status_code = wsc.shop_floor_status_code
453 AND wsc.status_move_flag = WIP_CONSTANTS.NO
454 AND NVL(wsc.disable_date, SYSDATE + 1) > SYSDATE;
455
456 IF (l_logLevel <= wip_constants.trace_logging) THEN
457 wip_logger.exitPoint(p_procName => 'wip_sf_status.count_no_move_last_step',
458 p_procReturnStatus => fnd_api.g_ret_sts_success,
459 p_msg => 'procedure complete',
460 x_returnStatus => l_returnStatus);
461 END IF;
462 RETURN l_no_move_count;
463 EXCEPTION
464 WHEN others THEN
465 IF (l_logLevel <= wip_constants.trace_logging) THEN
466 wip_logger.exitPoint(p_procName => 'wip_sf_status.count_no_move_last_step',
467 p_procReturnStatus => fnd_api.g_ret_sts_unexp_error,
468 p_msg => 'Unexpected Errors: ' || SQLERRM,
469 x_returnStatus => l_returnStatus);
470 END IF;
471 END count_no_move_last_step;
472
473 END WIP_SF_STATUS;
[Home] [Help]