[Home] [Help]
PACKAGE BODY: APPS.WIP_OVERCOMPLETION
Source
1 PACKAGE BODY WIP_OVERCOMPLETION AS
2 /* $Header: wipocmpb.pls 120.1 2006/01/17 06:45:18 panagara noship $ */
3
4
5 /*=====================================================================+
6 | PROCEDURE
7 | update_wip_req_operations
8 |
9 | PURPOSE
10 | Updates the required quantity values of all the components.
11 |
12 | ARGUMENTS
13 |
14 | EXCEPTIONS
15 |
16 | NOTES
17 |
18 +=====================================================================*/
19
20 procedure update_wip_req_operations
21 ( P_GROUP_ID IN NUMBER,
22 P_TXN_DATE IN VARCHAR2,
23 P_USER_ID IN NUMBER default -1,
24 P_LOGIN_ID IN NUMBER default -1,
25 P_REQ_ID IN NUMBER default -1,
26 P_APPL_ID IN NUMBER default -1,
27 P_PROG_ID IN NUMBER default -1
28 ) is
29 BEGIN
30
31 update wip_requirement_operations wro
32 set (wro.required_quantity
33 ,last_updated_by,
34 last_update_date,
35 last_update_login, request_id, program_application_id,
36 program_id, program_update_date ) =
37 ( select
38 MIN(required_quantity)
39 + NVL(
40 SUM(
41 NVL(wma1.primary_quantity,wti1.primary_quantity)),0)
42 * MIN(quantity_per_assembly),
43 DECODE(p_user_id,-1,NULL,p_user_id),
44 SYSDATE,
45 DECODE(p_login_id,-1,NULL,p_login_id),
46 DECODE(p_req_id,-1,NULL,p_req_id),
47 DECODE(p_appl_id,-1,NULL,p_appl_id),
48 DECODE(p_prog_id,-1,NULL,p_prog_id),
49 DECODE(p_req_id,-1,NULL,SYSDATE)
50 from wip_requirement_operations wro1,
51 wip_move_txn_interface wti1,
52 WIP_MOVE_TXN_ALLOCATIONS wma1
53 where
54 wro1.rowid = wro.rowid
55 -- The WO rows to be updated are identified by the rowids.
56 -- For each such row, go back and sum the quantities from WMTI
57 and wti1.group_id = p_group_id
58 and wti1.process_phase = WIP_CONSTANTS.MOVE_PROC
59 and wti1.process_status = WIP_CONSTANTS.RUNNING
60 and TRUNC(wti1.transaction_date)
61 = TO_DATE(P_TXN_DATE, WIP_CONSTANTS.DATE_FMT)
62 and wti1.overcompletion_transaction_id is not null
63 and wti1.overcompletion_primary_qty IS NULL
64 and wro1.wip_entity_id = wti1.wip_entity_id
65 and wro1.organization_id = wti1.organization_id
66 AND wti1.organization_id = wma1.organization_id (+)
67 AND wti1.transaction_id = wma1.transaction_id (+)
68 AND nvl(wma1.repetitive_schedule_id,0)
69 = nvl(wro1.repetitive_schedule_id,0)
70 )
71 where wro.rowid in
72 (
73 select wro2.rowid from
74 wip_requirement_operations wro2,
75 wip_move_txn_interface wti2,
76 WIP_MOVE_TXN_ALLOCATIONS wma2
77 where
78 wti2.group_id = p_group_id
79 and wti2.process_phase = WIP_CONSTANTS.MOVE_PROC
80 and wti2.process_status = WIP_CONSTANTS.RUNNING
81 and TRUNC(wti2.transaction_date)
82 = TO_DATE(P_TXN_DATE, WIP_CONSTANTS.DATE_FMT)
83 and wti2.overcompletion_transaction_id is not null
84 and wti2.overcompletion_primary_qty IS NULL
85 -- Picked a Move txn
86 and wro2.wip_entity_id = wti2.wip_entity_id
87 and wro2.organization_id = wti2.organization_id
88 AND wti2.organization_id = wma2.organization_id (+)
89 AND wti2.transaction_id = wma2.transaction_id (+)
90 AND nvl(wma2.repetitive_schedule_id,0)
91 = nvl(wro2.repetitive_schedule_id,0));
92
93 exception
94 when NO_DATA_FOUND then
95 null;
96 /* The whole "group_id" may not have any such transactions */
97 end ;
98
99 /*=====================================================================+
100 | PROCEDURE
101 | update_wip_req_operations_mmtt
102 |
103 | PURPOSE
104 | Updates the required quantity values of all the components.
105 |
106 | ARGUMENTS
107 |
108 | EXCEPTIONS
109 |
110 | NOTES
111 |
112 +=====================================================================*/
113
114 procedure update_wip_req_operations_mmtt
115 (
116 P_CPL_TXN_ID IN NUMBER,
117 P_USER_ID IN NUMBER default -1,
118 P_LOGIN_ID IN NUMBER default -1,
119 P_REQ_ID IN NUMBER default -1,
120 P_APPL_ID IN NUMBER default -1,
121 P_PROG_ID IN NUMBER default -1
122 ) is
123 BEGIN
124
125 UPDATE wip_requirement_operations wro
126 SET (wro.required_quantity
127 ,last_updated_by,
128 last_update_date,
129 last_update_login, request_id, program_application_id,
130 program_id, program_update_date ) =
131 ( SELECT
132 MIN(required_quantity)
133 + Nvl(
134 SUM(
135 Nvl(mmta1.primary_quantity, nvl(mmtt1.overcompletion_primary_qty,0))),0)
136 * MIN(quantity_per_assembly),
137 Decode(p_user_id,-1,NULL,p_user_id),
138 Sysdate,
139 Decode(p_login_id,-1,NULL,p_login_id),
140 Decode(p_req_id,-1,NULL,p_req_id),
141 Decode(p_appl_id,-1,NULL,p_appl_id),
142 Decode(p_prog_id,-1,NULL,p_prog_id),
143 Decode(p_req_id,-1,NULL,Sysdate)
144 FROM wip_requirement_operations wro1,
145 mtl_material_transactions_temp mmtt1,
146 mtl_material_txn_allocations mmta1
147 WHERE
148 wro1.ROWID = wro.ROWID
149 -- The WO rows to be updated are identified by the rowids.
150 -- For each such row, go back and sum the quantities from WMTI
151 and mmtt1.completion_transaction_id = p_cpl_txn_id
152 AND mmtt1.transaction_action_id = wip_constants.cplassy_action
153 AND mmtt1.overcompletion_primary_qty IS NOT NULL
154 AND wro1.wip_entity_id = mmtt1.transaction_source_id
155 AND wro1.organization_id = mmtt1.organization_id
156 AND mmtt1.organization_id = mmta1.organization_id (+)
157 AND mmtt1.transaction_temp_id = mmta1.transaction_id (+)
158 AND Nvl(mmta1.repetitive_schedule_id,0)
159 = Nvl(wro1.repetitive_schedule_id,0)
160 )
161 WHERE wro.ROWID IN
162 (
163 SELECT wro2.ROWID FROM
164 wip_requirement_operations wro2,
165 mtl_material_transactions_temp mmtt2,
166 mtl_material_txn_allocations mmta2
167 WHERE
168 mmtt2.transaction_action_id = wip_constants.cplassy_action
169 AND mmtt2.overcompletion_primary_qty IS NOT NULL
170 and mmtt2.completion_transaction_id = p_cpl_txn_id
171 -- Picked a cpl txn
172 AND wro2.wip_entity_id = mmtt2.transaction_source_id
173 AND wro2.organization_id = mmtt2.organization_id
174 AND mmtt2.organization_id = mmta2.organization_id (+)
175 AND mmtt2.transaction_temp_id = mmta2.transaction_id (+)
176 AND Nvl(mmta2.repetitive_schedule_id,0)
177 = Nvl(wro2.repetitive_schedule_id,0));
178
179 exception
180 when NO_DATA_FOUND then
181 null;
182
183 end ;
184
185 /*=====================================================================+
186 | PROCEDURE
187 | update_wip_operations
188 |
189 | PURPOSE
190 | Updates the quantity in the queue step of the from operation for
191 | the child move transactions
192 |
193 | ARGUMENTS
194 |
195 | EXCEPTIONS
196 |
197 | NOTES
198 |
199 +=====================================================================*/
200 procedure update_wip_operations
201 (
202 p_txn_id IN NUMBER, -- must be of the CHILD
203 P_GROUP_ID IN NUMBER,
204 P_TXN_DATE IN VARCHAR2,
205 P_USER_ID IN NUMBER default -1,
206 P_LOGIN_ID IN NUMBER default -1,
207 P_REQ_ID IN NUMBER default -1,
208 P_APPL_ID IN NUMBER default -1,
209 P_PROG_ID IN NUMBER default -1
210 ) is
211 BEGIN
212 update wip_operations wop
213 set (quantity_in_queue
214 ,last_updated_by
215 , last_update_date,
216 last_update_login, request_id, program_application_id,
217 program_id, program_update_date ) =
218 (select
219 wop.quantity_in_queue
220 + wti1.primary_quantity,
221 DECODE(P_user_id,-1,NULL,P_user_id),
222 SYSDATE,
223 DECODE(P_login_id,-1,NULL,P_login_id),
224 DECODE(P_req_id,-1,NULL,P_req_id),
225 DECODE(P_appl_id,-1,NULL,P_appl_id),
226 DECODE(P_prog_id,-1,NULL,P_prog_id),
227 DECODE(P_req_id,-1,NULL,SYSDATE)
228 from
229 wip_operations wop1,
230 wip_move_txn_interface wti1,
231 wip_move_txn_allocations wma1
232 where
233 wop1.rowid = wop.rowid
234 -- The WO rows to be updated are identified by the rowids.
235 -- For each such row, go back and sum the quantities from WMTI
236 and wti1.group_id = p_group_id
237 and TRUNC(wti1.transaction_date)
238 = TO_DATE(P_TXN_DATE, WIP_CONSTANTS.DATE_FMT)
239 AND wti1.transaction_id = p_txn_id
240 and wop1.wip_entity_id = wti1.wip_entity_id
241 and wop1.organization_id = wti1.organization_id
242 and wop1.operation_seq_num = wti1.fm_operation_seq_num
243 AND wti1.organization_id = wma1.organization_id (+)
244 AND wti1.transaction_id = wma1.transaction_id (+)
245 AND nvl(wma1.repetitive_schedule_id,0) = nvl(wop1.repetitive_schedule_id,0)
246 )
247 -- the select below must return just 1 row. When Online, group_id
248 -- is the same as transaction_id. When in BG, then the transaction_id
249 -- must be passed.
250 where wop.rowid =
251 (
252 select wop2.rowid from
253 wip_operations wop2,
254 wip_move_txn_interface wti2,
255 wip_move_txn_allocations wma2
256 where
257 wti2.group_id = p_group_id
258 and TRUNC(wti2.transaction_date)
259 = TO_DATE(P_TXN_DATE, WIP_CONSTANTS.DATE_FMT)
260 and wti2.transaction_id = p_txn_id
261 -- Picked a Move txn
262 and wop2.wip_entity_id = wti2.wip_entity_id
263 and wop2.organization_id = wti2.organization_id
264 and wop2.operation_seq_num = wti2.fm_operation_seq_num
265 AND wti2.organization_id = wma2.organization_id (+)
266 AND wti2.transaction_id = wma2.transaction_id (+)
267 AND nvl(wma2.repetitive_schedule_id,0) = nvl(wop2.repetitive_schedule_id,0)
268 );
269 -- Picked the row corresponding to the txn. 1 each for such txns
270 -- Rowids can be duplicate because there might be 2 wmti records with
271 -- the same fm_op
272
273 exception
274 when NO_DATA_FOUND then
275 null;
276
277 /* The whole "group_id" may not have any such transactions */
278
279 END;
280
281
282 /*=====================================================================+
283 | PROCEDURE
284 | undo_overcompletion
285 |
286 | PURPOSE
287 | Resets the "Required quantity" field of wip_requirement_operations
288 | during Unrelease of a Job since Overcompletions would have updated it
289 | if there were any overcompletions.
290 |
291 |
292 | ARGUMENTS
293 |
294 | EXCEPTIONS
295 |
296 | NOTES
297 |
298 +=====================================================================*/
299
300 PROCEDURE undo_overcompletion
301 (p_org_id IN NUMBER,
302 p_wip_entity_id IN NUMBER,
303 p_rep_id IN NUMBER DEFAULT NULL
304 ) is
305 BEGIN
306
307 IF( nvl(p_rep_id, -1) = -1 ) THEN
308 update wip_requirement_operations wro
309 set wro.required_quantity =
310 ( select
311 ( wro1.quantity_per_assembly * wdj.start_quantity)
312 from
313 wip_requirement_operations wro1,
314 wip_discrete_jobs wdj
315 WHERE
316 wro1.rowid = wro.rowid
317 and wro1.wip_entity_id = wdj.wip_entity_id
318 AND wro1.organization_id = wdj.organization_id)
319 where wro.wip_entity_id = p_wip_entity_id
320 and wro.organization_id = p_org_id;
321 ELSIF ( p_rep_id > 0 ) then
322 update wip_requirement_operations wro
323 set wro.required_quantity =
324 ( select
325 ( wro1.quantity_per_assembly *
326 round(wrs.processing_work_days * wrs.daily_production_rate, 6))
327 from
328 wip_requirement_operations wro1,
329 wip_repetitive_schedules wrs
330 where
331 wro1.rowid = wro.rowid
332 and wro1.repetitive_schedule_id =wrs.repetitive_schedule_id
333 AND wro1.organization_id = wrs.organization_id
334 AND wro1.wip_entity_id = wrs.wip_entity_id
335 )
336 where
337 wro.organization_id = p_org_id
338 AND wro.wip_entity_id = p_wip_entity_id
339 AND wro.repetitive_schedule_id = p_rep_id;
340 END IF;
341
342 END;
343
344 /*=====================================================================+
345 | PROCEDURE
346 | insert_child_move_txn
347 |
348 | PURPOSE
349 | Inserts the child WIP Move transaction for an Overcomplete transaction.
350 |
351 | ARGUMENTS
352 |
353 |
354 | EXCEPTIONS
355 |
356 | NOTES
357 |
358 +=====================================================================*/
359
360 PROCEDURE insert_child_move_txn
361 (
362 p_primary_quantity IN NUMBER,
363 p_parent_txn_id IN NUMBER,
364 p_move_profile IN NUMBER,
365 p_sched_id IN NUMBER,
366 p_user_id IN NUMBER default -1,
367 p_login_id IN NUMBER default -1,
368 p_req_id IN NUMBER default -1,
369 p_appl_id IN NUMBER default -1,
370 p_prog_id IN NUMBER default -1,
371 p_child_txn_id IN OUT NOCOPY NUMBER,
372 p_oc_txn_id OUT NOCOPY NUMBER,
373 p_first_operation_seq_num OUT NOCOPY NUMBER,
374 p_first_operation_code OUT NOCOPY VARCHAR2,
375 p_first_department_id OUT NOCOPY NUMBER,
376 p_first_department_code OUT NOCOPY VARCHAR2,
377 p_err_mesg OUT NOCOPY VARCHAR2
378 ) is
379
380 x_org_id NUMBER;
381 x_wip_entity_id NUMBER;
382 x_line_id NUMBER;
383 x_first_schedule_id NUMBER;
384 x_result NUMBER := wip_constants.yes;
385 x_sched_id NUMBER;
386
387 cursor get_move_transaction_id is
388 select wip_transactions_s.nextval from dual;
389
390 BEGIN
391
392 if( p_sched_id is null OR p_sched_id < 0) then
393 x_sched_id := NULL;
394 else
395 x_sched_id := p_sched_id;
396 end if;
397
398 p_err_mesg := NULL;
399 p_oc_txn_id := NULL;
400 p_first_operation_seq_num := NULL;
401 p_first_operation_code := NULL;
402 p_first_department_id := NULL;
403 p_first_department_code := NULL;
404 p_err_mesg := NULL;
405
406 /* Pick up the parent transaction and its associated details */
407
408 SELECT
409 organization_id,
410 wip_entity_id,
411 line_id,
412 repetitive_schedule_id /* first_schedule_id */
413 INTO
414 x_org_id,
415 x_wip_entity_id,
416 x_line_id,
417 x_first_schedule_id
418 FROM WIP_MOVE_TXN_INTERFACE
419 WHERE
420 transaction_id = p_parent_txn_id
421 AND overcompletion_primary_qty IS NOT NULL
422 ;
423
424
425 IF( p_move_profile = wip_constants.background) THEN
426 -- when the Move is done Online, the tolerance is validated in the
427 -- form.
428 x_result := wip_constants.no;
429 check_tolerance(
430 x_org_id,
431 x_wip_entity_id,
432 x_sched_id,
433 p_primary_quantity,
434 x_result
435 );
436 END IF;
437
438 IF( x_result = wip_constants.no ) THEN
439 fnd_message.set_name ('WIP', 'WIP_OC_TOLERANCE_FAIL');
440 p_err_mesg := fnd_message.get;
441 --p_err_mesg := 'Transaction violates Tolerance Level';
442 ELSE
443 IF(p_child_txn_id IS NULL OR
444 p_child_txn_id = -1 OR
445 p_child_txn_id = 0) THEN
446 -- generate transaction_id
447 open get_move_transaction_id;
448 fetch get_move_transaction_id into p_child_txn_id;
449 close get_move_transaction_id;
450 END IF;
451
452 open get_move_transaction_id;
453 fetch get_move_transaction_id into p_oc_txn_id;
454 close get_move_transaction_id;
455
456 UPDATE wip_move_txn_interface
457 SET overcompletion_transaction_id = p_oc_txn_id
458 WHERE transaction_id = p_parent_txn_id;
459
460
461 /* For the parent wip_entity_id, find the first operation */
462
463 WIP_OPERATIONS_INFO.first_operation
464 (
465 p_org_id =>to_number(x_org_id),
466 p_wip_entity_id => to_number(x_wip_entity_id),
467 p_line_id => to_number(x_line_id),
468 p_first_schedule_id => to_number(x_first_schedule_id),
469 p_first_op_seq => P_first_operation_seq_num,
470 p_first_op_code => P_first_operation_code,
471 p_first_dept_id => P_first_department_id,
472 p_first_dept_code => P_first_department_code);
473
474
475 insert into wip_move_txn_interface
476 (
477 transaction_id,
478 last_update_date,
479 last_updated_by,
480 creation_date,
481 created_by,
482 last_update_login,
483 request_id,
484 program_application_id,
485 program_id,
486 program_update_date,
487 group_id,
488 source_code,
489 source_line_id,
490 process_phase,
491 process_status,
492 organization_id,
493 organization_code,
494 wip_entity_id,
495 wip_entity_name,
496 entity_type,
497 primary_item_id,
498 line_id,
499 line_code,
500 repetitive_schedule_id,
501 transaction_date,
502 acct_period_id,
503 fm_operation_seq_num,
504 fm_operation_code,
505 fm_department_id,
506 fm_department_code,
507 fm_intraoperation_step_type,
508 to_operation_seq_num,
509 to_operation_code,
510 to_department_id,
511 to_department_code,
512 to_intraoperation_step_type,
513 transaction_quantity,
514 transaction_uom,
515 primary_quantity,
516 primary_uom,
517 scrap_account_id,
518 reason_id,
519 reason_name,
520 reference,
521 attribute_category,
522 attribute1,
523 attribute2,
524 attribute3,
525 attribute4,
526 attribute5,
527 attribute6,
528 attribute7,
529 attribute8,
530 attribute9,
531 attribute10,
532 attribute11,
533 attribute12,
534 attribute13,
535 attribute14,
536 attribute15,
537 transaction_type,
538 qa_collection_id,
539 overcompletion_transaction_id,
540 overcompletion_transaction_qty,
541 overcompletion_primary_qty
542 ) (
543 SELECT
544 p_child_txn_id,
545 Sysdate, /* last_update_date, */
546 p_user_id, /* last_updated_by */
547 SYSDATE, /* creation_date */
548 p_user_id, /* created_by */
549 p_login_id, /* last_update_login */
550 p_req_id, /* request_id */
551 p_appl_id, /* program_application_id */
552 p_prog_id, /* program_id */
553 decode(p_req_id, -1, NULL, SYSDATE), /* program_update_date */
554 decode(p_move_profile, /* group_id */
555 wip_constants.online , p_child_txn_id,
556 wip_constants.background, wti.group_id),
557 /* Use the parent''s group_id if in BG */
558 NULL, /* source_code */
559 NULL, /* source_line_id */
560 WIP_CONSTANTS.MOVE_PROC, /* process phase */
561 WIP_CONSTANTS.RUNNING, /* process status */
562 /* Process status is RUNNING even for BG,
563 since the child record is
564 inserted only when the parent is in Running status */
565 organization_id,
566 organization_code,
567 wip_entity_id,
568 wip_entity_name,
569 entity_type,
570 primary_item_id,
571 line_id,
572 line_code,
573 repetitive_schedule_id,
574 transaction_date,
575 acct_period_id,
576 P_first_operation_seq_num,
577 P_first_operation_code,
578 P_first_department_id,
579 P_first_department_code,
580 WIP_CONSTANTS.QUEUE,
581 fm_operation_seq_num,
582 fm_operation_code,
583 fm_department_id,
584 fm_department_code,
585 fm_intraoperation_step_type,
586 p_primary_quantity, /* transaction_quantity */
587 primary_uom, /* transaction_uom */
588 p_primary_quantity,
589 primary_uom, /* primary_uom */
590 scrap_account_id,
591 NULL, /* reason_id, */
592 NULL, /* reason_name, */
593 NULL, /* reference, */
594 NULL, /* attribute_category, */
595 NULL, /* attribute1, */
596 NULL, /* attribute2, */
597 NULL, /* attribute3, */
598 NULL, /* attribute4, */
599 NULL, /* attribute5, */
600 NULL, /* attribute6, */
601 NULL, /* attribute7, */
602 NULL, /* attribute8, */
603 NULL, /* attribute9, */
604 NULL, /* attribute10, */
605 NULL, /* attribute11, */
606 NULL, /* attribute12, */
607 NULL, /* attribute13, */
608 NULL, /* attribute14, */
609 NULL, /* attribute15, */
610 WIP_CONSTANTS.MOVE_TXN,
611 NULL, /* qa_collection_id, */
612 overcompletion_transaction_id,
613 NULL, /* overcompletion_transaction_qty */
614 NULL /* overcompletion_primary_qty */
615 from wip_move_txn_interface wti
616 where overcompletion_transaction_id = P_oc_txn_id
617 and overcompletion_primary_qty IS NOT NULL
618 );
619
620 END IF; -- if (x_result = no )
621 END;
622
623
624 /*=====================================================================+
625 | PROCEDURE
626 | delete_child_records
627 |
628 | PURPOSE
629 | This call would delete the child rows that have the fm_op &
630 | to_op to be the first operation and the step types to be
631 | 'Queue'.
632 |
633 |
634 | ARGUMENTS
635 |
636 | EXCEPTIONS
637 |
638 | NOTES
639 |
640 +=====================================================================*/
641
642 PROCEDURE delete_child_records
643 ( p_group_id IN NUMBER,
644 p_txn_date IN VARCHAR2,
645 p_outcome OUT NOCOPY NUMBER) IS
646 BEGIN
647
648 DELETE FROM wip_move_txn_interface wti
649 WHERE
650 wti.group_id = p_group_id
651 AND Trunc(wti.transaction_date)
652 = TO_DATE(p_txn_date,WIP_CONSTANTS.date_fmt)
653 AND wti.process_phase = wip_constants.move_proc
654 AND wti.process_status = wip_constants.running
655 AND wti.overcompletion_transaction_id IS NOT NULL
656 AND wti.overcompletion_primary_qty IS NULL
657 AND wti.fm_operation_seq_num = wti.to_operation_seq_num
658 AND wti.fm_intraoperation_step_type = wti.to_intraoperation_step_type
659 AND wti.fm_intraoperation_step_type = wip_constants.queue
660 ;
661 -- This first operation check is not necessary since, no other transaction
662 -- will have their op seq and steps match !!
663 /* AND exists
664 ( SELECT 'x'
665 FROM wip_operations wop
666 WHERE
667 wti.organization_id = wop.organization_id
668 AND wti.wip_entity_id = wop.wip_entity_id
669 AND wti.fm_operation_seq_num = wop.operation_seq_num
670 AND wop.previous_operation_seq_num IS NULL
671 -- First operation
672 ); */
673
674 if (SQL%found) THEN
675 p_outcome := wip_constants.yes;
676 ELSE
677 p_outcome := wip_constants.no;
678 END IF;
679
680
681 EXCEPTION
682 WHEN no_data_found THEN
683 p_outcome := wip_constants.no;
684 /* There may be no such Move records in WMTI */
685
686 END delete_child_records;
687
688 /*=====================================================================+
689 | PROCEDURE
690 | check_tolerance
691 |
692 | PURPOSE
693 | This procedure would check if the transaciton primary quantity +
694 | total quantity already in the job would still be less than the tolerance.
695 |
696 | ARGUMENTS
697 |
698 | EXCEPTIONS
699 |
700 | NOTES
701 |
702 +=====================================================================*/
703
704 PROCEDURE check_tolerance
705 (
706 p_organization_id IN NUMBER,
707 p_wip_entity_id IN NUMBER,
708 p_repetitive_schedule_id IN NUMBER DEFAULT NULL,
709 p_primary_quantity IN NUMBER,
710 p_result OUT NOCOPY NUMBER -- 1 = yes, 2 = No
711 ) IS
712 x_total_qty NUMBER;
713 x_repetitive_schedule_id NUMBER;
714 l_default_tolerance NUMBER := NULL;
715 BEGIN
716 p_result := wip_constants.no;
717
718 if(p_repetitive_schedule_id is null OR p_repetitive_schedule_id < 0) then
719 x_repetitive_schedule_id := NULL;
720 else
721 x_repetitive_schedule_id := p_repetitive_schedule_id;
722 end if;
723
724 -- find default overcompletion tolerance for item without tolerance setting
725 begin
726 select default_overcompl_tolerance
727 into l_default_tolerance
728 from wip_parameters
729 where organization_id = p_organization_id;
730 exception
731 when NO_DATA_FOUND then
732 -- do nothing, and let the default tolerance to be 0
733 NULL;
734 end;
735
736 if (l_default_tolerance IS NULL) then
737 l_default_tolerance := 0;
738 else
739 l_default_tolerance := l_default_tolerance / 100;
740 end if;
741
742
743 wip_common.get_total_quantity
744 (
745 p_organization_id,
746 p_wip_entity_id,
747 x_repetitive_schedule_id,
748 x_total_qty
749 );
750
751 IF( x_repetitive_schedule_id IS NULL) THEN
752
753 SELECT
754 Decode
755 (
756 Sign(
757 x_total_qty + p_primary_quantity - wdj.start_quantity -
758 Decode(wdj.overcompletion_tolerance_type,
759 wip_constants.percent,
760 wdj.start_quantity
761 * wdj.overcompletion_tolerance_value/100,
762 wip_constants.amount,
763 wdj.overcompletion_tolerance_value,
764 NULL,
765 -- if both org and job tolerance is not set, need to offset the
766 -- p_primary_quantity
767 decode(l_default_tolerance,
768 0, 0,
769 l_default_tolerance * wdj.start_quantity ))),
770 1, wip_constants.no,
771 0, wip_constants.yes,
772 -1, wip_constants.yes
773 )
774 INTO p_result
775 FROM wip_discrete_jobs wdj
776 WHERE
777 wdj.organization_id = p_organization_id
778 AND wdj.wip_entity_id = p_wip_entity_id;
779
780 ELSE
781 SELECT
782 Decode
783 (
784 Sign(
785 x_total_qty + p_primary_quantity -
786 (wrs.daily_production_rate * wrs.processing_work_days) -
787 Decode(wri.overcompletion_tolerance_type,
788 wip_constants.percent,
789 (wrs.daily_production_rate * wrs.processing_work_days)
790 * wri.overcompletion_tolerance_value/100,
791 wip_constants.amount,
792 wri.overcompletion_tolerance_value,
793 NULL,
794 decode(l_default_tolerance,
795 0,
796 0,
797 l_default_tolerance * wrs.daily_production_rate
798 * wrs.processing_work_days))),
799 1, wip_constants.no,
800 0, wip_constants.yes,
801 -1, wip_constants.yes
802 )
803 INTO p_result
804 FROM wip_repetitive_items wri,
805 wip_repetitive_schedules wrs
806 WHERE
807 wrs.organization_id = p_organization_id
808 AND wrs.repetitive_schedule_id = x_repetitive_schedule_id
809 AND wrs.organization_id = wri.organization_id
810 AND wrs.wip_entity_id = wri.wip_entity_id
811 AND wrs.line_id = wri.line_id;
812 END IF;
813 END;
814
815 /*=====================================================================+
816 | PROCEDURE
817 | get_tolerance_default
818 |
819 | PURPOSE
820 | This procedure takes as input the assembly item id and returns the
821 | tolerance column values.
822 |
823 | ARGUMENTS
824 |
825 | EXCEPTIONS
826 |
827 | NOTES
828 |
829 +=====================================================================*/
830
831 PROCEDURE get_tolerance_default
832 (
833 p_primary_item_id IN NUMBER,
834 p_org_id IN NUMBER,
835 p_tolerance_type OUT NOCOPY NUMBER,
836 p_tolerance_value OUT NOCOPY NUMBER
837 ) IS
838 BEGIN
839
840 SELECT
841 overcompletion_tolerance_type,
842 overcompletion_tolerance_value
843 INTO p_tolerance_type, p_tolerance_value
844 FROM mtl_system_items msi
845 WHERE
846 msi.inventory_item_id = p_primary_item_id
847 AND msi.organization_id = p_org_id;
848
849 END;
850
851 /*=====================================================================+
852 | PROCEDURE
853 | insert_oc_move_txn
854 |
855 | PURPOSE
856 | Inserts the child WIP Move transaction for an Overcomplete transaction.
857 | This is used for Assembly Completion
858 | ARGUMENTS
859 |
860 |
861 | EXCEPTIONS
862 |
863 | NOTES
864 |
865 +=====================================================================*/
866
867 PROCEDURE insert_oc_move_txn
868 (
869 p_primary_quantity IN NUMBER,
870 p_cpl_profile IN NUMBER,
871 p_oc_txn_id IN NUMBER,
872 p_parent_cpl_txn_id IN NUMBER,
873 p_first_schedule_id IN NUMBER,
874 -- Have to pass this since it is not populated in MMTT !!
875 p_user_id IN NUMBER default -1,
876 p_login_id IN NUMBER default -1,
877 p_req_id IN NUMBER default -1,
878 p_appl_id IN NUMBER default -1,
879 p_prog_id IN NUMBER default -1,
880 p_child_txn_id IN OUT NOCOPY NUMBER,
881 p_first_operation_seq_num OUT NOCOPY NUMBER,
882 p_err_mesg OUT NOCOPY VARCHAR2
883 ) is
884
885 x_result NUMBER;
886 x_first_schedule_id NUMBER; /* dummy */
887 x_org_code VARCHAR2(3);
888 x_line_code VARCHAR2(10);
889 x_first_operation_code VARCHAR2(4);
890 x_first_department_id NUMBER;
891 x_first_department_code VARCHAR2(10);
892 x_last_operation_code VARCHAR2(4);
893 x_last_department_id NUMBER;
894 x_last_department_code VARCHAR2(10);
895
896 x_org_id NUMBER;
897 x_wip_entity_id NUMBER;
898 x_line_id NUMBER;
899 x_last_operation_seq_num NUMBER;
900
901 CURSOR get_move_transaction_id is
902 SELECT wip_transactions_s.nextval
903 FROM dual;
904
905 BEGIN
906
907 p_err_mesg := NULL;
908 p_first_operation_seq_num := NULL;
909
910 if( p_first_schedule_id is null OR p_first_schedule_id < 0) then
911 x_first_schedule_id := NULL;
912 else
913 x_first_schedule_id := p_first_schedule_id;
914 end if;
915
916 /* Pick up the parent transaction and its associated details */
917 SELECT
918 MIN(organization_id),
919 MIN(transaction_source_id),
920 MIN(repetitive_line_id),
921 MIN(operation_seq_num)
922 INTO x_org_id, x_wip_entity_id, x_line_id,
923 x_last_operation_seq_num
924 FROM mtl_material_transactions_temp
925 WHERE completion_transaction_id = p_parent_cpl_txn_id
926 AND transaction_action_id = wip_constants.cplassy_action;
927
928 IF( x_result = wip_constants.no ) THEN
929 fnd_message.set_name ('WIP', 'WIP_OC_TOLERANCE_FAIL');
930 p_err_mesg := fnd_message.get;
931 --p_err_mesg := 'Transaction violates Tolerance Level';
932
933 ELSE
934 if(p_child_txn_id is null or
935 p_child_txn_id <= 0) then
936 -- generate transaction_id
937 open get_move_transaction_id;
938 fetch get_move_transaction_id into p_child_txn_id;
939 close get_move_transaction_id;
940 end if;
941
942 SELECT organization_code
943 INTO x_org_code
944 FROM mtl_parameters
945 WHERE organization_id = x_org_id;
946
947 IF( x_line_id IS NOT NULL ) THEN
948 SELECT line_code
949 INTO x_line_code
950 FROM wip_lines
951 WHERE line_id = x_line_id;
952 END IF;
953
954
955 /* For the parent wip_entity_id, find the first operation */
956
957 WIP_OPERATIONS_INFO.first_operation
958 (
959 p_org_id => to_number(x_org_id),
960 p_wip_entity_id => to_number(x_wip_entity_id),
961 p_line_id => to_number(x_line_id),
962 p_first_schedule_id => to_number(x_first_schedule_id),
963 p_first_op_seq => p_first_operation_seq_num,
964 p_first_op_code => x_first_operation_code,
965 p_first_dept_id => x_first_department_id,
966 p_first_dept_code => x_first_department_code);
967
968 select
969 bso.operation_code,
970 wo.department_id,
971 bd.department_code
972 INTO
973 x_last_operation_code,
974 x_last_department_id,
975 x_last_department_code
976 from bom_standard_operations bso,
977 bom_departments bd,
978 wip_operations wo
979 WHERE wo.operation_seq_num = x_last_operation_seq_num
980 AND wo.department_id = bd.department_id
981 AND wo.standard_operation_id = bso.standard_operation_id (+)
982 AND wo.organization_id = x_org_id
983 AND wo.wip_entity_id = x_wip_entity_id
984 AND (wo.repetitive_schedule_id is NULL
985 OR
986 wo.repetitive_schedule_id = x_first_schedule_id);
987
988
989 insert into wip_move_txn_interface
990 (
991 transaction_id,
992 last_update_date,
993 last_updated_by,
994 creation_date,
995 created_by,
996 last_update_login,
997 request_id,
998 program_application_id,
999 program_id,
1000 program_update_date,
1001 group_id,
1002 source_code,
1003 source_line_id,
1004 process_phase,
1005 process_status,
1006 organization_id,
1007 organization_code,
1008 wip_entity_id,
1009 wip_entity_name,
1010 entity_type,
1011 primary_item_id,
1012 line_id,
1013 line_code,
1014 repetitive_schedule_id,
1015 transaction_date,
1016 acct_period_id,
1017 fm_operation_seq_num,
1018 fm_operation_code,
1019 fm_department_id,
1020 fm_department_code,
1021 fm_intraoperation_step_type,
1022 to_operation_seq_num,
1023 to_operation_code,
1024 to_department_id,
1025 to_department_code,
1026 to_intraoperation_step_type,
1027 transaction_quantity,
1028 transaction_uom,
1029 primary_quantity,
1030 primary_uom,
1031 scrap_account_id,
1032 reason_id,
1033 reason_name,
1034 reference,
1035 attribute_category,
1036 attribute1,
1037 attribute2,
1038 attribute3,
1039 attribute4,
1040 attribute5,
1041 attribute6,
1042 attribute7,
1043 attribute8,
1044 attribute9,
1045 attribute10,
1046 attribute11,
1047 attribute12,
1048 attribute13,
1049 attribute14,
1050 attribute15,
1051 transaction_type,
1052 qa_collection_id,
1053 overcompletion_transaction_id,
1054 overcompletion_transaction_qty,
1055 overcompletion_primary_qty
1056 ) (
1057 SELECT UNIQUE /* Online may have several cpl txns */
1058 p_child_txn_id,
1059 Sysdate, /* last_update_date, */
1060 p_user_id, /* last_updated_by */
1061 SYSDATE, /* creation_date */
1062 p_user_id, /* created_by */
1063 p_login_id, /* last_update_login */
1064 p_req_id, /* request_id */
1065 p_appl_id, /* program_application_id */
1066 p_prog_id, /* program_id */
1067 decode(p_req_id, -1, NULL, SYSDATE), /* program_update_date */
1068 p_child_txn_id,
1069 /* group_id - Always Online, even when cpl is in BG */
1070 NULL, /* source_code */
1071 NULL, /* source_line_id */
1072 WIP_CONSTANTS.MOVE_PROC, /* process phase */
1073 decode(p_cpl_profile, WIP_CONSTANTS.ONLINE,
1074 WIP_CONSTANTS.RUNNING,
1075 WIP_CONSTANTS.BACKGROUND,
1076 WIP_CONSTANTS.PENDING),
1077 /* process status. wiltws requires it to be in Pending */
1078 x_org_id,
1079 x_org_code,
1080 x_wip_entity_id, /* wip_entity_id */
1081 mmtt.transaction_source_name, /* wip_entity_name */
1082 mmtt.wip_entity_type, /* entity_type */
1083 mmtt.inventory_item_id, /* primary_item_id */
1084 x_line_id, /* line_id */
1085 x_line_code,
1086 x_first_schedule_id, /* repetitive_schedule_id */
1087 mmtt.transaction_date,
1088 mmtt.acct_period_id,
1089 p_first_operation_seq_num,
1090 x_first_operation_code,
1091 x_first_department_id,
1092 x_first_department_code,
1093 WIP_CONSTANTS.QUEUE,
1094 x_last_operation_seq_num,
1095 x_last_operation_code,
1096 x_last_department_id,
1097 x_last_department_code,
1098 wip_constants.tomove, /* p_intraoperation_step_type */
1099 p_primary_quantity, /* transaction_quantity */
1100 msi.primary_uom_code, /* transaction_uom */
1101 p_primary_quantity,
1102 msi.primary_uom_code, /* primary_uom */
1103 NULL, /* scrap_account_id */
1104 NULL, /* reason_id, */
1105 NULL, /* reason_name, */
1106 NULL, /* reference, */
1107 NULL, /* attribute_category, */
1108 NULL, /* attribute1, */
1109 NULL, /* attribute2, */
1110 NULL, /* attribute3, */
1111 NULL, /* attribute4, */
1112 NULL, /* attribute5, */
1113 NULL, /* attribute6, */
1114 NULL, /* attribute7, */
1115 NULL, /* attribute8, */
1116 NULL, /* attribute9, */
1117 NULL, /* attribute10, */
1118 NULL, /* attribute11, */
1119 NULL, /* attribute12, */
1120 NULL, /* attribute13, */
1121 NULL, /* attribute14, */
1122 NULL, /* attribute15, */
1123 WIP_CONSTANTS.MOVE_TXN,
1124 NULL, /* qa_collection_id, */
1125 p_oc_txn_id, /* overcompletion_transaction_id */
1126 NULL, /* overcompletion_transaction_qty */
1127 NULL /* overcompletion_primary_qty */
1128 from mtl_material_transactions_temp mmtt,
1129 mtl_system_items msi
1130 where
1131 mmtt.completion_transaction_id = p_parent_cpl_txn_id
1132 and mmtt.transaction_action_id = wip_constants.cplassy_action
1133 and mmtt.overcompletion_primary_qty IS NOT NULL
1134 and mmtt.inventory_item_id = msi.inventory_item_id
1135 and mmtt.organization_id = msi.organization_id
1136 );
1137
1138 END IF; -- if (x_result = no )
1139 END;
1140
1141 END WIP_OVERCOMPLETION;