1 PACKAGE BODY WIP_SF_STATUS AS
2 /* $Header: wipsfstb.pls 120.3.12020000.2 2012/07/18 18:16:34 ankohli ship $ */
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 l_osp_sf_status VARCHAR2(10);
303 l_num_rcv_txn number;
304
305 begin
306
307 select nvl(wp.osp_shop_floor_status,'@@@')
308 into l_osp_sf_status
309 from wip_parameters wp
310 where wp.organization_id = p_org_id;
311
312 Select count(*)
313 into l_num_rcv_txn
314 from WIP_OPERATION_RESOURCES WOR1
315 WHERE WOR1.WIP_ENTITY_ID = p_wip_id
316 AND WOR1.ORGANIZATION_ID = p_org_id
317 AND WOR1.OPERATION_SEQ_NUM = p_fm_op
318 AND (WOR1.REPETITIVE_SCHEDULE_ID = p_sched_id
319 OR (WOR1.REPETITIVE_SCHEDULE_ID IS NULL AND p_sched_id IS NULL))
320 AND WOR1.AUTOCHARGE_TYPE = 4
321 AND p_source_code = 'RCV';
322
323 SELECT COUNT(1)
324 INTO x_no_move_count
325 FROM WIP_OPERATIONS WOP,
326 WIP_OPERATION_RESOURCES WOR,
327 WIP_PARAMETERS WP,
328 WIP_SHOP_FLOOR_STATUS_CODES WSFSC,
329 WIP_SHOP_FLOOR_STATUSES WSFS
330 WHERE WSFS.INTRAOPERATION_STEP_TYPE IN (1,/* Queue */
331 2,/* Run */
332 3 /* To Move */)
333 AND WP.ORGANIZATION_ID = p_org_id
334 AND NVL(WP.MOVES_OVER_NO_MOVE_STATUSES, 1) = 2 /* No */
335 /* Fix for bug 2121222 */
336 AND ((l_num_rcv_txn = 0 AND l_osp_sf_status = WSFS.SHOP_FLOOR_STATUS_CODE)
337 OR l_osp_sf_status <> WSFS.SHOP_FLOOR_STATUS_CODE)
338 AND WSFS.ORGANIZATION_ID = wp.organization_id
339 AND WSFS.WIP_ENTITY_ID = p_wip_id
340 AND (WSFS.LINE_ID = p_line_id
341 OR (WSFS.LINE_ID IS NULL AND p_line_id IS NULL))
342 AND WSFSC.ORGANIZATION_ID = WSFS.ORGANIZATION_ID
343 AND WSFSC.SHOP_FLOOR_STATUS_CODE = WSFS.SHOP_FLOOR_STATUS_CODE
344 AND WSFSC.STATUS_MOVE_FLAG = 2 /* No */
345 AND WOP.ORGANIZATION_ID = WSFS.ORGANIZATION_ID
346 AND WOP.WIP_ENTITY_ID = WSFS.WIP_ENTITY_ID
347 AND (WOP.REPETITIVE_SCHEDULE_ID = p_sched_id
348 OR (WOP.REPETITIVE_SCHEDULE_ID IS NULL AND p_sched_id IS NULL))
349 AND WOP.OPERATION_SEQ_NUM = WSFS.OPERATION_SEQ_NUM
350 AND WOR.ORGANIZATION_ID (+)= WSFS.ORGANIZATION_ID
351 AND WOR.WIP_ENTITY_ID (+)= WSFS.WIP_ENTITY_ID
352 AND WOR.OPERATION_SEQ_NUM (+)= WSFS.OPERATION_SEQ_NUM
353
354 /* Remove 2 statements below because WOR.REPETITIVE_SCHEDULE_ID can be null
355 * even for repetitive because the user do not have to set up resource before
356 * do move transaction while p_schedule_id is always not null because it is
357 * default to first_schedule_id. So we should compare with first_schedule_id
358 * only when this value is not null.
359 */
360 -- AND (WOR.REPETITIVE_SCHEDULE_ID = p_sched_id
361 -- OR (WOR.REPETITIVE_SCHEDULE_ID IS NULL AND p_sched_id IS NULL))
362 AND (WOR.REPETITIVE_SCHEDULE_ID IS NULL OR
363 WOR.REPETITIVE_SCHEDULE_ID = p_sched_id)
364 AND
365 ((/* forward move - different operations */
366 (p_fm_op < p_to_op)
367 AND
368 (/* shop floor status is between from and to operations
369 and at a count point operation */
370 (p_fm_op < WSFS.OPERATION_SEQ_NUM
371 AND WSFS.OPERATION_SEQ_NUM < p_to_op
372 AND WSFS.OPERATION_SEQ_NUM = WOP.OPERATION_SEQ_NUM
373 AND WOP.COUNT_POINT_TYPE IN (1,/* Yes Auto */
374 2 /* No Auto */))
375 OR
376 (/* shop floor status is at the same operation as from operation
377 but after the from intraoperation step */
378 p_fm_op = WSFS.OPERATION_SEQ_NUM
379 AND p_fm_step <= WSFS.INTRAOPERATION_STEP_TYPE)
380 OR
381 (/* shop floor status is at the same operation as to operation
382 but before the to intraoperation step */
383 p_to_op = WSFS.OPERATION_SEQ_NUM
384 AND WSFS.INTRAOPERATION_STEP_TYPE < p_to_step)))
385 OR
386 (/* forward move - same operation */
387 (p_fm_op = p_to_op AND p_fm_step < p_to_step)
388 AND
389 (/* shop floor status is at same operation as from operation but
390 between the from intraoperation step and to intraoperation step */
391 p_fm_op = WSFS.OPERATION_SEQ_NUM
392 AND p_fm_step <= WSFS.INTRAOPERATION_STEP_TYPE
393 AND WSFS.INTRAOPERATION_STEP_TYPE < p_to_step))
394 OR
395 (/* backward move - different operations */
396 (p_fm_op > p_to_op)
397 AND
398 (/* shop floor status is between to and from operations
399 and at a count point operation */
400 (p_fm_op > WSFS.OPERATION_SEQ_NUM
401 AND WSFS.OPERATION_SEQ_NUM > p_to_op
402 AND WSFS.OPERATION_SEQ_NUM = WOP.OPERATION_SEQ_NUM
403 AND WOP.COUNT_POINT_TYPE IN (1,/* Yes Auto */
404 2 /* No Auto */))
405 OR
406 (/* shop floor status is at the same operation as from operation
407 but before the from intraoperation step */
408 p_fm_op = WSFS.OPERATION_SEQ_NUM
409 AND p_fm_step >= WSFS.INTRAOPERATION_STEP_TYPE)
410 OR
411 (/* shop floor status is at the same operation as to operation
412 but after the to intraoperation step */
413 p_to_op = WSFS.OPERATION_SEQ_NUM
414 AND WSFS.INTRAOPERATION_STEP_TYPE > p_to_step)))
415 OR
416 (/* backward move - same operation */
417 (p_fm_op = p_to_op AND p_fm_step > p_to_step)
418 AND
419 (/* shop floor status is at same operation as from operation but
420 between the from intraoperation step and to intraoperation step */
421 p_fm_op = WSFS.OPERATION_SEQ_NUM
422 AND p_fm_step >= WSFS.INTRAOPERATION_STEP_TYPE
423 AND WSFS.INTRAOPERATION_STEP_TYPE > p_to_step)));
424
425 return(x_no_move_count);
426 end count_no_move_statuses;
427
428 FUNCTION count_no_move_last_step(p_org_id IN NUMBER,
429 p_wip_id IN NUMBER) RETURN NUMBER IS
430
431 l_params wip_logger.param_tbl_t;
432 l_returnStatus VARCHAR2(1);
433 l_logLevel NUMBER := fnd_log.g_current_runtime_level;
434 l_no_move_count NUMBER := 0;
435 BEGIN
436 -- write parameter value to log file
437 IF (l_logLevel <= wip_constants.trace_logging) THEN
438 l_params(1).paramName := 'p_org_id';
439 l_params(1).paramValue := p_org_id;
440 l_params(2).paramName := 'p_wip_id';
441 l_params(2).paramValue := p_wip_id;
442 wip_logger.entryPoint(p_procName => 'wip_sf_status.count_no_move_last_step',
443 p_params => l_params,
444 x_returnStatus => l_returnStatus);
445 END IF;
446
447 SELECT count(*)
448 INTO l_no_move_count
449 FROM wip_shop_floor_status_codes wsc,
450 wip_shop_floor_statuses ws,
451 wip_operations wo1
452 WHERE ws.organization_id = p_org_id
453 AND ws.wip_entity_id = p_wip_id
454 AND wsc.organization_id = ws.organization_id
455 AND wo1.operation_seq_num = ws.operation_seq_num
456 AND wo1.organization_id = ws.organization_id
457 AND wo1.wip_entity_id = ws.wip_entity_id
458 AND wo1.operation_seq_num =
459 (SELECT max(wo2.operation_seq_num)
460 FROM wip_operations wo2
461 WHERE wo2.organization_id = wo1.organization_id
462 AND wo2.wip_entity_id = wo1.wip_entity_id)
463 AND ws.intraoperation_step_type = WIP_CONSTANTS.TOMOVE
464 AND ws.shop_floor_status_code = wsc.shop_floor_status_code
465 AND wsc.status_move_flag = WIP_CONSTANTS.NO
466 AND NVL(wsc.disable_date, SYSDATE + 1) > SYSDATE;
467
468 IF (l_logLevel <= wip_constants.trace_logging) THEN
469 wip_logger.exitPoint(p_procName => 'wip_sf_status.count_no_move_last_step',
470 p_procReturnStatus => fnd_api.g_ret_sts_success,
471 p_msg => 'procedure complete',
472 x_returnStatus => l_returnStatus);
473 END IF;
474 RETURN l_no_move_count;
475 EXCEPTION
476 WHEN others THEN
477 IF (l_logLevel <= wip_constants.trace_logging) THEN
478 wip_logger.exitPoint(p_procName => 'wip_sf_status.count_no_move_last_step',
479 p_procReturnStatus => fnd_api.g_ret_sts_unexp_error,
480 p_msg => 'Unexpected Errors: ' || SQLERRM,
481 x_returnStatus => l_returnStatus);
482 END IF;
483 END count_no_move_last_step;
484
485 function get_no_move_status(
486 p_org_id in number,
487 p_wip_id in number,
488 p_line_id in number,
489 p_sched_id in number,
490 p_fm_op in number,
491 p_fm_step in number,
492 p_to_op in number,
493 p_to_step in number,
494 p_source_code in varchar2 default null) return varchar2 is
495
496 x_sf_status varchar2(100);
497
498 begin
499 SELECT WSFS.SHOP_FLOOR_STATUS_CODE
500 INTO x_sf_status
501 FROM WIP_OPERATIONS WOP,
502 WIP_OPERATION_RESOURCES WOR,
503 WIP_PARAMETERS WP,
504 WIP_SHOP_FLOOR_STATUS_CODES WSFSC,
505 WIP_SHOP_FLOOR_STATUSES WSFS
506 WHERE WSFS.INTRAOPERATION_STEP_TYPE IN (1,/* Queue */
507 2,/* Run */
508 3 /* To Move */)
509 AND WP.ORGANIZATION_ID = p_org_id
510 AND NVL(WP.MOVES_OVER_NO_MOVE_STATUSES, 1) = 2 /* No */
511 /* Fix for bug 2121222 */
512 AND NOT EXISTS
513 (Select 'X' from WIP_OPERATION_RESOURCES WOR1
514 WHERE WOR1.WIP_ENTITY_ID = p_wip_id
515 AND WOR1.ORGANIZATION_ID = p_org_id
516 AND WOR1.OPERATION_SEQ_NUM = p_fm_op
517 AND (WOR1.REPETITIVE_SCHEDULE_ID = p_sched_id
518 OR (WOR1.REPETITIVE_SCHEDULE_ID IS NULL AND p_sched_id IS NULL))
519 AND WOR1.AUTOCHARGE_TYPE = 4
520 AND p_source_code = 'RCV' )
521 AND WSFS.ORGANIZATION_ID = wp.organization_id
522 AND WSFS.WIP_ENTITY_ID = p_wip_id
523 AND (WSFS.LINE_ID = p_line_id
524 OR (WSFS.LINE_ID IS NULL AND p_line_id IS NULL))
525 AND WSFSC.ORGANIZATION_ID = WSFS.ORGANIZATION_ID
526 AND WSFSC.SHOP_FLOOR_STATUS_CODE = WSFS.SHOP_FLOOR_STATUS_CODE
527 AND WSFSC.STATUS_MOVE_FLAG = 2 /* No */
528 AND WOP.ORGANIZATION_ID = WSFS.ORGANIZATION_ID
529 AND WOP.WIP_ENTITY_ID = WSFS.WIP_ENTITY_ID
530 AND (WOP.REPETITIVE_SCHEDULE_ID = p_sched_id
531 OR (WOP.REPETITIVE_SCHEDULE_ID IS NULL AND p_sched_id IS NULL))
532 AND WOP.OPERATION_SEQ_NUM = WSFS.OPERATION_SEQ_NUM
533 AND WOR.ORGANIZATION_ID (+)= WSFS.ORGANIZATION_ID
534 AND WOR.WIP_ENTITY_ID (+)= WSFS.WIP_ENTITY_ID
535 AND WOR.OPERATION_SEQ_NUM (+)= WSFS.OPERATION_SEQ_NUM
536
537 /* Remove 2 statements below because WOR.REPETITIVE_SCHEDULE_ID can be null
538 * even for repetitive because the user do not have to set up resource before
539 * do move transaction while p_schedule_id is always not null because it is
540 * default to first_schedule_id. So we should compare with first_schedule_id
541 * only when this value is not null.
542 */
543 -- AND (WOR.REPETITIVE_SCHEDULE_ID = p_sched_id
544 -- OR (WOR.REPETITIVE_SCHEDULE_ID IS NULL AND p_sched_id IS NULL))
545 AND (WOR.REPETITIVE_SCHEDULE_ID IS NULL OR
546 WOR.REPETITIVE_SCHEDULE_ID = p_sched_id)
547 AND
548 ((/* forward move - different operations */
549 (p_fm_op < p_to_op)
550 AND
551 (/* shop floor status is between from and to operations
552 and at a count point operation */
553 (p_fm_op < WSFS.OPERATION_SEQ_NUM
554 AND WSFS.OPERATION_SEQ_NUM < p_to_op
555 AND WSFS.OPERATION_SEQ_NUM = WOP.OPERATION_SEQ_NUM
556 AND WOP.COUNT_POINT_TYPE IN (1,/* Yes Auto */
557 2 /* No Auto */))
558 OR
559 (/* shop floor status is at the same operation as from operation
560 but after the from intraoperation step */
561 p_fm_op = WSFS.OPERATION_SEQ_NUM
562 AND p_fm_step <= WSFS.INTRAOPERATION_STEP_TYPE)
563 OR
564 (/* shop floor status is at the same operation as to operation
565 but before the to intraoperation step */
566 p_to_op = WSFS.OPERATION_SEQ_NUM
567 AND WSFS.INTRAOPERATION_STEP_TYPE < p_to_step)))
568 OR
569 (/* forward move - same operation */
570 (p_fm_op = p_to_op AND p_fm_step < p_to_step)
571 AND
572 (/* shop floor status is at same operation as from operation but
573 between the from intraoperation step and to intraoperation step */
574 p_fm_op = WSFS.OPERATION_SEQ_NUM
575 AND p_fm_step <= WSFS.INTRAOPERATION_STEP_TYPE
576 AND WSFS.INTRAOPERATION_STEP_TYPE < p_to_step))
577 OR
578 (/* backward move - different operations */
579 (p_fm_op > p_to_op)
580 AND
581 (/* shop floor status is between to and from operations
582 and at a count point operation */
583 (p_fm_op > WSFS.OPERATION_SEQ_NUM
584 AND WSFS.OPERATION_SEQ_NUM > p_to_op
585 AND WSFS.OPERATION_SEQ_NUM = WOP.OPERATION_SEQ_NUM
586 AND WOP.COUNT_POINT_TYPE IN (1,/* Yes Auto */
587 2 /* No Auto */))
588 OR
589 (/* shop floor status is at the same operation as from operation
590 but before the from intraoperation step */
591 p_fm_op = WSFS.OPERATION_SEQ_NUM
592 AND p_fm_step >= WSFS.INTRAOPERATION_STEP_TYPE)
593 OR
594 (/* shop floor status is at the same operation as to operation
595 but after the to intraoperation step */
596 p_to_op = WSFS.OPERATION_SEQ_NUM
597 AND WSFS.INTRAOPERATION_STEP_TYPE > p_to_step)))
598 OR
599 (/* backward move - same operation */
600 (p_fm_op = p_to_op AND p_fm_step > p_to_step)
601 AND
602 (/* shop floor status is at same operation as from operation but
603 between the from intraoperation step and to intraoperation step */
604 p_fm_op = WSFS.OPERATION_SEQ_NUM
605 AND p_fm_step >= WSFS.INTRAOPERATION_STEP_TYPE
606 AND WSFS.INTRAOPERATION_STEP_TYPE > p_to_step)))
607 AND ROWNUM = 1;
608
609 return(x_sf_status);
610 end get_no_move_status;
611
612 END WIP_SF_STATUS;
[Home] [Help]