[Home] [Help]
PACKAGE BODY: APPS.WIP_WS_EXCEPTIONS
Source
1 package body WIP_WS_EXCEPTIONS as
2 /* $Header: wipvexcb.pls 120.2 2005/11/21 03:43:47 amgarg noship $ */
3
4
5 /*
6 * Close all exceptions for a Job
7 */
8 FUNCTION CLOSE_EXCEPTION_JOB
9 (
10 P_WIP_ENTITY_ID NUMBER,
11 P_ORGANIZATION_ID NUMBER
12 ) RETURN BOOLEAN
13 IS
14 RETURN_STATUS BOOLEAN := TRUE;
15 BEGIN
16
17 SAVEPOINT WIPVEXC;
18
19 UPDATE
20 WIP_EXCEPTIONS
21 SET
22 STATUS_TYPE = 2,
23 LAST_UPDATE_DATE = SYSDATE,
24 LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID,
25 LAST_UPDATED_BY = FND_GLOBAL.USER_ID
26 WHERE
27 ORGANIZATION_ID = P_ORGANIZATION_ID AND
28 WIP_ENTITY_ID = P_WIP_ENTITY_ID AND
29 STATUS_TYPE = 1;
30
31 RETURN RETURN_STATUS;
32
33 EXCEPTION
34 WHEN OTHERS THEN
35 ROLLBACK TO WIPVEXC;
36 RETURN_STATUS := FALSE;
37 RETURN RETURN_STATUS;
38
39 END CLOSE_EXCEPTION_JOB;
40
41
42 /*
43 * Close exception for a Job Op combination
44 */
45 FUNCTION close_exception_jobop
46 (
47 p_wip_entity_id number,
48 P_OPERATION_SEQ_NUM NUMBER,
49 P_ORGANIZATION_ID NUMBER
50 ) RETURN BOOLEAN
51 IS
52 RETURN_STATUS BOOLEAN := TRUE;
53 BEGIN
54
55 SAVEPOINT WIPVEXC;
56
57 UPDATE
58 WIP_EXCEPTIONS
59 SET
60 STATUS_TYPE = 2,
61 LAST_UPDATE_DATE = SYSDATE,
62 LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID,
63 LAST_UPDATED_BY = FND_GLOBAL.USER_ID
64 WHERE
65 ORGANIZATION_ID = P_ORGANIZATION_ID AND
66 WIP_ENTITY_ID = P_WIP_ENTITY_ID AND
67 OPERATION_SEQ_NUM = P_OPERATION_SEQ_NUM AND
68 STATUS_TYPE = 1;
69
70 RETURN RETURN_STATUS;
71
72 EXCEPTION
73 WHEN OTHERS THEN
74 ROLLBACK TO WIPVEXC;
75 RETURN_STATUS := FALSE;
76 RETURN RETURN_STATUS;
77
78 END close_exception_jobop;
79
80 /*
81 * Close exception for a Job Op combination
82 * check if department changed, close exception.
83 */
84 FUNCTION CLOSE_EXCEPTION_JOBOP
85 (
86 P_WIP_ENTITY_ID NUMBER,
87 P_OPERATION_SEQ_NUM NUMBER,
88 P_DEPARTMENT_ID NUMBER,
89 P_ORGANIZATION_ID NUMBER
90 ) RETURN BOOLEAN
91 IS
92 RETURN_STATUS BOOLEAN := TRUE;
93 L_ROW_COUNT NUMBER := 0;
94 BEGIN
95
96 SAVEPOINT WIPVEXC;
97
98 --Select row, if deptt id has changed.
99 SELECT COUNT(*) INTO L_ROW_COUNT FROM
100 WIP_OPERATIONS
101 WHERE
102 WIP_ENTITY_ID = P_WIP_ENTITY_ID AND
103 OPERATION_SEQ_NUM = P_OPERATION_SEQ_NUM AND
104 ORGANIZATION_ID = P_ORGANIZATION_ID AND
105 NVL(DEPARTMENT_ID, '-9999') <> NVL(P_DEPARTMENT_ID, '-9999');
106
107 /* CLOSE EXCEPTION ONLY IF DEPTT ID CHANGED */
108 IF (L_ROW_COUNT > 0) THEN
109 UPDATE
110 WIP_EXCEPTIONS
111 SET
112 STATUS_TYPE = 2,
113 LAST_UPDATE_DATE = SYSDATE,
114 LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID,
115 LAST_UPDATED_BY = FND_GLOBAL.USER_ID
116 WHERE
117 ORGANIZATION_ID = P_ORGANIZATION_ID AND
118 WIP_ENTITY_ID = P_WIP_ENTITY_ID AND
119 OPERATION_SEQ_NUM = P_OPERATION_SEQ_NUM AND
120 STATUS_TYPE = 1;
121 END IF;
122
123 RETURN RETURN_STATUS;
124
125 EXCEPTION
126 WHEN OTHERS THEN
127 ROLLBACK TO WIPVEXC;
128 RETURN_STATUS := FALSE;
129 RETURN RETURN_STATUS;
130
131 END CLOSE_EXCEPTION_JOBOP;
132
133
134 /*
135 * Close exception for a Job,Op,Res combination
136 */
137 function close_exception_jobop_res
138 (
139 p_wip_entity_id number,
140 p_operation_seq_num number,
141 p_resource_seq_num number,
142 p_organization_id number
143 ) return boolean
144 IS
145 RETURN_STATUS BOOLEAN := TRUE;
146 BEGIN
147
148 SAVEPOINT WIPVEXC;
149
150 UPDATE
151 WIP_EXCEPTIONS
152 SET
153 STATUS_TYPE = 2,
154 LAST_UPDATE_DATE = SYSDATE,
155 LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID,
156 LAST_UPDATED_BY = FND_GLOBAL.USER_ID
157 WHERE
158 ORGANIZATION_ID = P_ORGANIZATION_ID AND
159 WIP_ENTITY_ID = P_WIP_ENTITY_ID AND
160 OPERATION_SEQ_NUM = P_OPERATION_SEQ_NUM AND
161 RESOURCE_SEQ_NUM = P_RESOURCE_SEQ_NUM AND
162 STATUS_TYPE = 1;
163
164 RETURN RETURN_STATUS;
165
166 EXCEPTION
167 WHEN OTHERS THEN
168 ROLLBACK TO WIPVEXC;
169 RETURN_STATUS := FALSE;
170 RETURN RETURN_STATUS;
171
172 END close_exception_jobop_res;
173
174 /*
175 * Close exception for a Job,Op,Res combination.
176 * Check if Resource Id changed, only then close exceptions.
177 */
178 function close_exception_jobop_res
179 (
180 p_wip_entity_id number,
181 p_operation_seq_num number,
182 p_resource_seq_num number,
183 p_resource_id number,
184 p_organization_id number
185 ) return boolean
186 IS
187 RETURN_STATUS BOOLEAN := TRUE;
188 L_ROW_COUNT NUMBER := 0;
189 BEGIN
190
191 SAVEPOINT WIPVEXC;
192
193 --Select row, if RESOURCE id has changed.
194 SELECT COUNT(*) INTO L_ROW_COUNT FROM
195 WIP_OPERATION_RESOURCES
196 WHERE
197 WIP_ENTITY_ID = P_WIP_ENTITY_ID AND
198 OPERATION_SEQ_NUM = P_OPERATION_SEQ_NUM AND
199 RESOURCE_SEQ_NUM = P_RESOURCE_SEQ_NUM AND
200 ORGANIZATION_ID = P_ORGANIZATION_ID AND
201 RESOURCE_ID <> P_RESOURCE_ID;
202
203 /* CLOSE EXCEPTION ONLY IF RESOURCE ID CHANGED */
204 IF (L_ROW_COUNT > 0) THEN
205 UPDATE
206 WIP_EXCEPTIONS
207 SET
208 STATUS_TYPE = 2,
209 LAST_UPDATE_DATE = SYSDATE,
210 LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID,
211 LAST_UPDATED_BY = FND_GLOBAL.USER_ID
212 WHERE
213 ORGANIZATION_ID = P_ORGANIZATION_ID AND
214 WIP_ENTITY_ID = P_WIP_ENTITY_ID AND
215 OPERATION_SEQ_NUM = P_OPERATION_SEQ_NUM AND
216 RESOURCE_SEQ_NUM = P_RESOURCE_SEQ_NUM AND
217 STATUS_TYPE = 1;
218 END IF;
219
220 RETURN RETURN_STATUS;
221
222 EXCEPTION
223 WHEN OTHERS THEN
224 ROLLBACK TO WIPVEXC;
225 RETURN_STATUS := FALSE;
226 RETURN RETURN_STATUS;
227
228 END close_exception_jobop_res;
229
230 /*
231 * Close exception for a Job,Op,Res combination
232 * Check if either resource_id changed or department_id changed, close exceptions
233 */
234 function close_exception_jobop_res
235 (
236 p_wip_entity_id number,
237 p_operation_seq_num number,
238 p_resource_seq_num number,
239 p_resource_id number,
240 p_department_code varchar2,
241 p_organization_id number
242 ) return boolean
243 IS
244 RETURN_STATUS BOOLEAN := TRUE;
245 L_ROW_COUNT NUMBER := 0;
246 BEGIN
247
248 SAVEPOINT WIPVEXC;
249
250 --Select row, if RESOURCE ID OR DEPTT ID has changed.
251 --SINCE WE HAVE DEPTT CODE, WE NEED DEPTT ID FROM BOM_DEPTT
252 SELECT COUNT(*) INTO L_ROW_COUNT FROM
253 WIP_OPERATION_RESOURCES
254 WHERE
255 WIP_ENTITY_ID = P_WIP_ENTITY_ID AND
256 OPERATION_SEQ_NUM = P_OPERATION_SEQ_NUM AND
257 RESOURCE_SEQ_NUM = P_RESOURCE_SEQ_NUM AND
258 ORGANIZATION_ID = P_ORGANIZATION_ID AND
259 (RESOURCE_ID <> P_RESOURCE_ID
260 OR DEPARTMENT_ID <>
261 (SELECT DEPARTMENT_ID FROM BOM_DEPARTMENTS
262 WHERE ORGANIZATION_ID = P_ORGANIZATION_ID AND
263 DEPARTMENT_CODE = P_DEPARTMENT_CODE)
264 );
265
266 /* CLOSE EXCEPTION ONLY IF RESOURCE ID CHANGED */
267 IF (L_ROW_COUNT > 0) THEN
268 UPDATE
269 WIP_EXCEPTIONS
270 SET
271 STATUS_TYPE = 2,
272 LAST_UPDATE_DATE = SYSDATE,
273 LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID,
274 LAST_UPDATED_BY = FND_GLOBAL.USER_ID
275 WHERE
276 ORGANIZATION_ID = P_ORGANIZATION_ID AND
277 WIP_ENTITY_ID = P_WIP_ENTITY_ID AND
278 OPERATION_SEQ_NUM = P_OPERATION_SEQ_NUM AND
279 RESOURCE_SEQ_NUM = P_RESOURCE_SEQ_NUM AND
280 STATUS_TYPE = 1;
281 END IF;
282
283 RETURN RETURN_STATUS;
284
285 EXCEPTION
286 WHEN OTHERS THEN
287 ROLLBACK TO WIPVEXC;
288 RETURN_STATUS := FALSE;
289 RETURN RETURN_STATUS;
290
291 END close_exception_jobop_res;
292
293
294
295 /*
296 * Close exception for a Job,Op,Replacement Group Num combination
297 * Resolves exception when altenates are assigned.
298 */
299 function close_exception_alt_res
300 (
301 p_wip_entity_id number,
302 p_operation_seq_num number,
303 p_substitute_group_num number,
304 p_organization_id number
305 ) return boolean
306 IS
307 RETURN_STATUS BOOLEAN := TRUE;
308 BEGIN
309
310 SAVEPOINT WIPVEXC;
311
312 UPDATE
313 WIP_EXCEPTIONS
314 SET
315 STATUS_TYPE = 2,
316 LAST_UPDATE_DATE = SYSDATE,
317 LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID,
318 LAST_UPDATED_BY = FND_GLOBAL.USER_ID
319 WHERE
320 ORGANIZATION_ID = P_ORGANIZATION_ID AND
321 WIP_ENTITY_ID = P_WIP_ENTITY_ID AND
322 OPERATION_SEQ_NUM = P_OPERATION_SEQ_NUM AND
323 STATUS_TYPE = 1 AND
324 RESOURCE_SEQ_NUM IN
325 (
326 SELECT
327 RESOURCE_SEQ_NUM
328 FROM
329 WIP_OPERATION_RESOURCES WOR
330 WHERE
331 WOR.ORGANIZATION_ID = P_ORGANIZATION_ID AND
332 WOR.WIP_ENTITY_ID = P_WIP_ENTITY_ID AND
333 WOR.OPERATION_SEQ_NUM = P_OPERATION_SEQ_NUM AND
334 WOR.SUBSTITUTE_GROUP_NUM = P_SUBSTITUTE_GROUP_NUM
335 UNION
336 SELECT
337 RESOURCE_SEQ_NUM
338 FROM
339 WIP_SUB_OPERATION_RESOURCES WSOR
340 WHERE
341 WSOR.ORGANIZATION_ID = P_ORGANIZATION_ID AND
342 WSOR.WIP_ENTITY_ID = P_WIP_ENTITY_ID AND
343 WSOR.OPERATION_SEQ_NUM = P_OPERATION_SEQ_NUM AND
344 WSOR.SUBSTITUTE_GROUP_NUM = P_SUBSTITUTE_GROUP_NUM
345 );
346
347 RETURN RETURN_STATUS;
348
349 EXCEPTION
350 WHEN OTHERS THEN
351 ROLLBACK TO WIPVEXC;
352 RETURN_STATUS := FALSE;
353 RETURN RETURN_STATUS;
354
355 END close_exception_alt_res;
356
357
358 /*
359 * Close exception for a Job,Op,Res Instance combination
360 * when doing a res Instance delete.
361 * Serial Number field is ALSO used.
362 * for any Machine Instance.
363 */
364 FUNCTION close_exception_res_instance
365 (
366 p_wip_entity_id number,
367 P_OPERATION_SEQ_NUM NUMBER,
368 P_RESOURCE_SEQ_NUM NUMBER,
369 P_INSTANCE_ID NUMBER,
370 P_SERIAL_NUMBER VARCHAR2,
371 P_ORGANIZATION_ID NUMBER
372 ) RETURN BOOLEAN
373 IS
374 RETURN_STATUS BOOLEAN := TRUE;
375 L_EQUIP_ITEM_ID NUMBER;
376 BEGIN
377
378 SAVEPOINT WIPVEXC;
379
380 SELECT INVENTORY_ITEM_ID
381 INTO L_EQUIP_ITEM_ID
382 FROM BOM_RESOURCE_EQUIPMENTS
383 WHERE INSTANCE_ID = P_INSTANCE_ID;
384
385 /*Remember although, for Equipment type exceptions,
386 * Serial number cannot be null, we still keep a check for it here.
387 */
388 UPDATE
389 WIP_EXCEPTIONS
390 SET
391 STATUS_TYPE = 2,
392 LAST_UPDATE_DATE = SYSDATE,
393 LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID,
394 LAST_UPDATED_BY = FND_GLOBAL.USER_ID
395 WHERE
396 ORGANIZATION_ID = P_ORGANIZATION_ID AND
397 WIP_ENTITY_ID = P_WIP_ENTITY_ID AND
398 OPERATION_SEQ_NUM = P_OPERATION_SEQ_NUM AND
399 RESOURCE_SEQ_NUM = P_RESOURCE_SEQ_NUM AND
400 EQUIPMENT_ITEM_ID = L_EQUIP_ITEM_ID AND
401 NVL(SERIAL_NUMBER, '-9999') = NVL(P_SERIAL_NUMBER, '-9999') AND
402 STATUS_TYPE = 1;
403
404 RETURN RETURN_STATUS;
405
406 EXCEPTION
407 WHEN OTHERS THEN
408 ROLLBACK TO WIPVEXC;
409 RETURN_STATUS := FALSE;
410 RETURN RETURN_STATUS;
411
412 END close_exception_res_instance;
413
414 /*
415 * Close exception for a Job,Op,Res Instance combination
416 * Closes exception when a Res Instance is Updated.
417 * Check if Serial_Number is changed, close exception.
418 * Otherwise don't need to close.
419 */
420 function close_exp_res_instance_update
421 (
422 p_wip_entity_id number,
423 p_operation_seq_num number,
424 p_resource_seq_num number,
425 p_instance_id number,
426 p_serial_number varchar2,
427 p_organization_id number
428 ) return boolean
429 IS
430 RETURN_STATUS BOOLEAN := TRUE;
431 L_EQUIP_ITEM_ID NUMBER;
432 L_ROW_COUNT NUMBER := 0;
433 L_SERIAL_NUMBER VARCHAR2(30);
434 BEGIN
435
436 SAVEPOINT WIPVEXC;
437
438 SELECT COUNT(*) INTO L_ROW_COUNT
439 FROM WIP_OP_RESOURCE_INSTANCES
440 WHERE ORGANIZATION_ID = P_ORGANIZATION_ID AND
441 WIP_ENTITY_ID = P_WIP_ENTITY_ID AND
442 OPERATION_SEQ_NUM = P_OPERATION_SEQ_NUM AND
443 RESOURCE_SEQ_NUM = P_RESOURCE_SEQ_NUM AND
444 INSTANCE_ID = P_INSTANCE_ID AND
445 SERIAL_NUMBER <> P_SERIAL_NUMBER;
446
447 /*IF SERIAL NUMBER IS NOT SAME AS ORIGINAL SERIAL NUMBER,
448 * WE CLOSE THE EXCEPTION, OTHERWISE NO ACTION.
449 */
450 IF (L_ROW_COUNT > 0) THEN
451
452 /* Get inventory_item_id, since we don't have instance_id in wip_exceptions*/
453 SELECT INVENTORY_ITEM_ID
454 INTO L_EQUIP_ITEM_ID
455 FROM BOM_RESOURCE_EQUIPMENTS BRE
456 WHERE INSTANCE_ID = P_INSTANCE_ID;
457
458 SELECT SERIAL_NUMBER INTO L_SERIAL_NUMBER
459 FROM WIP_OP_RESOURCE_INSTANCES
460 WHERE ORGANIZATION_ID = P_ORGANIZATION_ID AND
461 WIP_ENTITY_ID = P_WIP_ENTITY_ID AND
462 OPERATION_SEQ_NUM = P_OPERATION_SEQ_NUM AND
463 RESOURCE_SEQ_NUM = P_RESOURCE_SEQ_NUM AND
464 INSTANCE_ID = P_INSTANCE_ID;
465
466 UPDATE
467 WIP_EXCEPTIONS
468 SET
469 STATUS_TYPE = 2,
470 LAST_UPDATE_DATE = SYSDATE,
471 LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID,
472 LAST_UPDATED_BY = FND_GLOBAL.USER_ID
473 WHERE
474 ORGANIZATION_ID = P_ORGANIZATION_ID AND
475 WIP_ENTITY_ID = P_WIP_ENTITY_ID AND
476 OPERATION_SEQ_NUM = P_OPERATION_SEQ_NUM AND
477 RESOURCE_SEQ_NUM = P_RESOURCE_SEQ_NUM AND
478 EQUIPMENT_ITEM_ID = L_EQUIP_ITEM_ID AND
479 NVL(SERIAL_NUMBER, '@@@@@') = NVL(L_SERIAL_NUMBER, '@@@@@') AND
480 STATUS_TYPE = 1;
481 END IF;
482
483 RETURN RETURN_STATUS;
484
485 EXCEPTION
486 WHEN OTHERS THEN
487 ROLLBACK TO WIPVEXC;
488 RETURN_STATUS := FALSE;
489 RETURN RETURN_STATUS;
490
491 END close_exp_res_instance_update;
492
493
494 /*
495 * Close exception for a Job:Op component.
496 * component_item_id is inventory_item_id from WRO.
497 */
498 function close_exception_component
499 (
500 p_wip_entity_id number,
501 p_operation_seq_num number,
502 p_component_item_id number,
503 p_organization_id number
504 ) return boolean
505 IS
506 RETURN_STATUS BOOLEAN := TRUE;
510
507 BEGIN
508
509 SAVEPOINT WIPVEXC;
511 UPDATE
512 WIP_EXCEPTIONS
513 SET
514 STATUS_TYPE = 2,
515 LAST_UPDATE_DATE = SYSDATE,
516 LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID,
517 LAST_UPDATED_BY = FND_GLOBAL.USER_ID
518 WHERE
519 ORGANIZATION_ID = P_ORGANIZATION_ID AND
520 WIP_ENTITY_ID = P_WIP_ENTITY_ID AND
521 OPERATION_SEQ_NUM = P_OPERATION_SEQ_NUM AND
522 COMPONENT_ITEM_ID = P_COMPONENT_ITEM_ID AND
523 STATUS_TYPE = 1;
524
525 RETURN RETURN_STATUS;
526
527 EXCEPTION
528 WHEN OTHERS THEN
529 ROLLBACK TO WIPVEXC;
530 RETURN_STATUS := FALSE;
531 RETURN RETURN_STATUS;
532
533 END close_exception_component;
534
535
536 /*
537 * Close exception for this exception_id.
538 */
539 function close_exception
540 (
541 P_exception_id number
542 ) return boolean
543 IS
544 RETURN_STATUS BOOLEAN := TRUE;
545 BEGIN
546
547 SAVEPOINT WIPVEXC;
548
549 UPDATE
550 WIP_EXCEPTIONS
551 SET
552 STATUS_TYPE = 2,
553 LAST_UPDATE_DATE = SYSDATE,
554 LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID,
555 LAST_UPDATED_BY = FND_GLOBAL.USER_ID
556 WHERE
557 EXCEPTION_ID = P_EXCEPTION_ID AND
558 STATUS_TYPE = 1;
559
560 RETURN RETURN_STATUS;
561
562 EXCEPTION
563 WHEN OTHERS THEN
564 ROLLBACK TO WIPVEXC;
565 RETURN_STATUS := FALSE;
566 RETURN RETURN_STATUS;
567
568 END CLOSE_EXCEPTION;
569
570
571 /*
572 * Delete exception for a Job:Op combination.
573 */
574 function delete_exception_jobop
575 (
576 p_wip_entity_id number,
577 p_operation_seq_num number,
578 p_organization_id number
579 ) return boolean
580 IS
581 RETURN_STATUS BOOLEAN := TRUE;
582 BEGIN
583
584 SAVEPOINT WIPVEXC;
585
586 DELETE FROM
587 WIP_EXCEPTIONS
588 WHERE
589 ORGANIZATION_ID = P_ORGANIZATION_ID AND
590 WIP_ENTITY_ID = P_WIP_ENTITY_ID AND
591 OPERATION_SEQ_NUM = P_OPERATION_SEQ_NUM;
592
593 RETURN RETURN_STATUS;
594
595 EXCEPTION
596 WHEN OTHERS THEN
597 ROLLBACK TO WIPVEXC;
598 RETURN_STATUS := FALSE;
599 RETURN RETURN_STATUS;
600
601 END delete_exception_jobop;
602
603
604 /*
605 * Delete all exceptions for a Job.
606 */
607 function delete_exception_job
608 (
609 p_wip_entity_id number,
610 p_organization_id number
611 ) return boolean
612 IS
613 RETURN_STATUS BOOLEAN := TRUE;
614 BEGIN
615
616 SAVEPOINT WIPVEXC;
617
618 DELETE FROM
619 WIP_EXCEPTIONS
620 WHERE
621 ORGANIZATION_ID = P_ORGANIZATION_ID AND
622 WIP_ENTITY_ID = P_WIP_ENTITY_ID;
623
624 RETURN RETURN_STATUS;
625
626 EXCEPTION
627 WHEN OTHERS THEN
628 ROLLBACK TO WIPVEXC;
629 RETURN_STATUS := FALSE;
630 RETURN RETURN_STATUS;
631
632 END delete_exception_job;
633
634
635 END WIP_WS_EXCEPTIONS;
636