[Home] [Help]
PACKAGE BODY: APPS.WIP_OVERCOMPLETION
Source
1 PACKAGE BODY WIP_OVERCOMPLETION AS
2 /* $Header: wipocmpb.pls 120.1.12010000.3 2010/02/23 04:38:53 pfauzdar ship $ */
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 /*Bugfix:8838245.Changed below update to divide with component yield factor
309 as yield is separated from quantity per assembly as part of component yield factor project during R12 */
310 update wip_requirement_operations wro
311 set wro.required_quantity =
312 ( select
313 ( wro1.quantity_per_assembly * wdj.start_quantity)/wro1.component_yield_factor
314 from
315 wip_requirement_operations wro1,
316 wip_discrete_jobs wdj
317 WHERE
318 wro1.rowid = wro.rowid
319 and wro1.wip_entity_id = wdj.wip_entity_id
320 AND wro1.organization_id = wdj.organization_id)
321 where wro.wip_entity_id = p_wip_entity_id
322 and wro.organization_id = p_org_id
323 and wro.basis_type = wip_constants.ITEM_BASED_MTL; /* Bug fix 9337675 */
324 ELSIF ( p_rep_id > 0 ) then
325 /*Bugfix:8838245.Changed below update to divide with component yield factor
326 as yield is separated from quantity per assembly as part of component yield factor project during R12 */
327 update wip_requirement_operations wro
328 set wro.required_quantity =
329 ( select
330 ( wro1.quantity_per_assembly *
331 round(wrs.processing_work_days * wrs.daily_production_rate, 6))/wro1.component_yield_factor
332 from
333 wip_requirement_operations wro1,
334 wip_repetitive_schedules wrs
335 where
336 wro1.rowid = wro.rowid
337 and wro1.repetitive_schedule_id =wrs.repetitive_schedule_id
338 AND wro1.organization_id = wrs.organization_id
339 AND wro1.wip_entity_id = wrs.wip_entity_id
340 )
341 where
342 wro.organization_id = p_org_id
343 AND wro.wip_entity_id = p_wip_entity_id
344 AND wro.repetitive_schedule_id = p_rep_id
345 and wro.basis_type = wip_constants.ITEM_BASED_MTL; /* Bug fix 9337675 */
346 END IF;
347
348 END;
349
350 /*=====================================================================+
351 | PROCEDURE
352 | insert_child_move_txn
353 |
354 | PURPOSE
355 | Inserts the child WIP Move transaction for an Overcomplete transaction.
356 |
357 | ARGUMENTS
358 |
359 |
360 | EXCEPTIONS
361 |
362 | NOTES
363 |
364 +=====================================================================*/
365
366 PROCEDURE insert_child_move_txn
367 (
368 p_primary_quantity IN NUMBER,
369 p_parent_txn_id IN NUMBER,
370 p_move_profile IN NUMBER,
371 p_sched_id IN NUMBER,
372 p_user_id IN NUMBER default -1,
373 p_login_id IN NUMBER default -1,
374 p_req_id IN NUMBER default -1,
375 p_appl_id IN NUMBER default -1,
376 p_prog_id IN NUMBER default -1,
377 p_child_txn_id IN OUT NOCOPY NUMBER,
378 p_oc_txn_id OUT NOCOPY NUMBER,
379 p_first_operation_seq_num OUT NOCOPY NUMBER,
380 p_first_operation_code OUT NOCOPY VARCHAR2,
381 p_first_department_id OUT NOCOPY NUMBER,
382 p_first_department_code OUT NOCOPY VARCHAR2,
383 p_err_mesg OUT NOCOPY VARCHAR2
384 ) is
385
386 x_org_id NUMBER;
387 x_wip_entity_id NUMBER;
388 x_line_id NUMBER;
389 x_first_schedule_id NUMBER;
390 x_result NUMBER := wip_constants.yes;
391 x_sched_id NUMBER;
392
393 cursor get_move_transaction_id is
394 select wip_transactions_s.nextval from dual;
395
396 BEGIN
397
398 if( p_sched_id is null OR p_sched_id < 0) then
399 x_sched_id := NULL;
400 else
401 x_sched_id := p_sched_id;
402 end if;
403
404 p_err_mesg := NULL;
408 p_first_department_id := NULL;
405 p_oc_txn_id := NULL;
406 p_first_operation_seq_num := NULL;
407 p_first_operation_code := NULL;
409 p_first_department_code := NULL;
410 p_err_mesg := NULL;
411
412 /* Pick up the parent transaction and its associated details */
413
414 SELECT
415 organization_id,
416 wip_entity_id,
417 line_id,
418 repetitive_schedule_id /* first_schedule_id */
419 INTO
420 x_org_id,
421 x_wip_entity_id,
422 x_line_id,
423 x_first_schedule_id
424 FROM WIP_MOVE_TXN_INTERFACE
425 WHERE
426 transaction_id = p_parent_txn_id
427 AND overcompletion_primary_qty IS NOT NULL
428 ;
429
430
431 IF( p_move_profile = wip_constants.background) THEN
432 -- when the Move is done Online, the tolerance is validated in the
433 -- form.
434 x_result := wip_constants.no;
435 check_tolerance(
436 x_org_id,
437 x_wip_entity_id,
438 x_sched_id,
439 p_primary_quantity,
440 x_result
441 );
442 END IF;
443
444 IF( x_result = wip_constants.no ) THEN
445 fnd_message.set_name ('WIP', 'WIP_OC_TOLERANCE_FAIL');
446 p_err_mesg := fnd_message.get;
447 --p_err_mesg := 'Transaction violates Tolerance Level';
448 ELSE
449 IF(p_child_txn_id IS NULL OR
450 p_child_txn_id = -1 OR
451 p_child_txn_id = 0) THEN
452 -- generate transaction_id
453 open get_move_transaction_id;
454 fetch get_move_transaction_id into p_child_txn_id;
455 close get_move_transaction_id;
456 END IF;
457
458 open get_move_transaction_id;
459 fetch get_move_transaction_id into p_oc_txn_id;
460 close get_move_transaction_id;
461
462 UPDATE wip_move_txn_interface
463 SET overcompletion_transaction_id = p_oc_txn_id
464 WHERE transaction_id = p_parent_txn_id;
465
466
467 /* For the parent wip_entity_id, find the first operation */
468
469 WIP_OPERATIONS_INFO.first_operation
470 (
471 p_org_id =>to_number(x_org_id),
472 p_wip_entity_id => to_number(x_wip_entity_id),
473 p_line_id => to_number(x_line_id),
474 p_first_schedule_id => to_number(x_first_schedule_id),
475 p_first_op_seq => P_first_operation_seq_num,
476 p_first_op_code => P_first_operation_code,
477 p_first_dept_id => P_first_department_id,
478 p_first_dept_code => P_first_department_code);
479
480
481 insert into wip_move_txn_interface
482 (
483 transaction_id,
484 last_update_date,
485 last_updated_by,
486 creation_date,
487 created_by,
488 last_update_login,
489 request_id,
490 program_application_id,
491 program_id,
492 program_update_date,
493 group_id,
494 source_code,
495 source_line_id,
496 process_phase,
497 process_status,
498 organization_id,
499 organization_code,
500 wip_entity_id,
501 wip_entity_name,
502 entity_type,
503 primary_item_id,
504 line_id,
505 line_code,
506 repetitive_schedule_id,
507 transaction_date,
508 acct_period_id,
509 fm_operation_seq_num,
510 fm_operation_code,
511 fm_department_id,
512 fm_department_code,
513 fm_intraoperation_step_type,
514 to_operation_seq_num,
515 to_operation_code,
516 to_department_id,
517 to_department_code,
518 to_intraoperation_step_type,
519 transaction_quantity,
520 transaction_uom,
521 primary_quantity,
522 primary_uom,
523 scrap_account_id,
524 reason_id,
525 reason_name,
526 reference,
527 attribute_category,
528 attribute1,
529 attribute2,
530 attribute3,
531 attribute4,
532 attribute5,
533 attribute6,
534 attribute7,
535 attribute8,
536 attribute9,
537 attribute10,
538 attribute11,
539 attribute12,
540 attribute13,
541 attribute14,
542 attribute15,
543 transaction_type,
544 qa_collection_id,
545 overcompletion_transaction_id,
546 overcompletion_transaction_qty,
547 overcompletion_primary_qty
548 ) (
549 SELECT
550 p_child_txn_id,
551 Sysdate, /* last_update_date, */
552 p_user_id, /* last_updated_by */
553 SYSDATE, /* creation_date */
554 p_user_id, /* created_by */
555 p_login_id, /* last_update_login */
559 decode(p_req_id, -1, NULL, SYSDATE), /* program_update_date */
556 p_req_id, /* request_id */
557 p_appl_id, /* program_application_id */
558 p_prog_id, /* program_id */
560 decode(p_move_profile, /* group_id */
561 wip_constants.online , p_child_txn_id,
562 wip_constants.background, wti.group_id),
563 /* Use the parent''s group_id if in BG */
564 NULL, /* source_code */
565 NULL, /* source_line_id */
566 WIP_CONSTANTS.MOVE_PROC, /* process phase */
567 WIP_CONSTANTS.RUNNING, /* process status */
568 /* Process status is RUNNING even for BG,
569 since the child record is
570 inserted only when the parent is in Running status */
571 organization_id,
572 organization_code,
573 wip_entity_id,
574 wip_entity_name,
575 entity_type,
576 primary_item_id,
577 line_id,
578 line_code,
579 repetitive_schedule_id,
580 transaction_date,
581 acct_period_id,
582 P_first_operation_seq_num,
583 P_first_operation_code,
584 P_first_department_id,
585 P_first_department_code,
586 WIP_CONSTANTS.QUEUE,
587 fm_operation_seq_num,
588 fm_operation_code,
589 fm_department_id,
590 fm_department_code,
591 fm_intraoperation_step_type,
592 p_primary_quantity, /* transaction_quantity */
593 primary_uom, /* transaction_uom */
594 p_primary_quantity,
595 primary_uom, /* primary_uom */
596 scrap_account_id,
597 NULL, /* reason_id, */
598 NULL, /* reason_name, */
599 NULL, /* reference, */
600 NULL, /* attribute_category, */
601 NULL, /* attribute1, */
602 NULL, /* attribute2, */
603 NULL, /* attribute3, */
604 NULL, /* attribute4, */
605 NULL, /* attribute5, */
606 NULL, /* attribute6, */
607 NULL, /* attribute7, */
608 NULL, /* attribute8, */
609 NULL, /* attribute9, */
610 NULL, /* attribute10, */
611 NULL, /* attribute11, */
612 NULL, /* attribute12, */
613 NULL, /* attribute13, */
614 NULL, /* attribute14, */
615 NULL, /* attribute15, */
616 WIP_CONSTANTS.MOVE_TXN,
617 NULL, /* qa_collection_id, */
618 overcompletion_transaction_id,
619 NULL, /* overcompletion_transaction_qty */
620 NULL /* overcompletion_primary_qty */
621 from wip_move_txn_interface wti
622 where overcompletion_transaction_id = P_oc_txn_id
623 and overcompletion_primary_qty IS NOT NULL
624 );
625
626 END IF; -- if (x_result = no )
627 END;
628
629
630 /*=====================================================================+
631 | PROCEDURE
632 | delete_child_records
633 |
634 | PURPOSE
635 | This call would delete the child rows that have the fm_op &
636 | to_op to be the first operation and the step types to be
637 | 'Queue'.
638 |
639 |
640 | ARGUMENTS
641 |
642 | EXCEPTIONS
643 |
644 | NOTES
645 |
646 +=====================================================================*/
647
648 PROCEDURE delete_child_records
649 ( p_group_id IN NUMBER,
650 p_txn_date IN VARCHAR2,
651 p_outcome OUT NOCOPY NUMBER) IS
652 BEGIN
653
654 DELETE FROM wip_move_txn_interface wti
655 WHERE
656 wti.group_id = p_group_id
657 AND Trunc(wti.transaction_date)
658 = TO_DATE(p_txn_date,WIP_CONSTANTS.date_fmt)
659 AND wti.process_phase = wip_constants.move_proc
660 AND wti.process_status = wip_constants.running
661 AND wti.overcompletion_transaction_id IS NOT NULL
662 AND wti.overcompletion_primary_qty IS NULL
663 AND wti.fm_operation_seq_num = wti.to_operation_seq_num
664 AND wti.fm_intraoperation_step_type = wti.to_intraoperation_step_type
665 AND wti.fm_intraoperation_step_type = wip_constants.queue
666 ;
667 -- This first operation check is not necessary since, no other transaction
668 -- will have their op seq and steps match !!
669 /* AND exists
670 ( SELECT 'x'
671 FROM wip_operations wop
672 WHERE
673 wti.organization_id = wop.organization_id
674 AND wti.wip_entity_id = wop.wip_entity_id
675 AND wti.fm_operation_seq_num = wop.operation_seq_num
676 AND wop.previous_operation_seq_num IS NULL
677 -- First operation
678 ); */
679
680 if (SQL%found) THEN
681 p_outcome := wip_constants.yes;
682 ELSE
683 p_outcome := wip_constants.no;
684 END IF;
685
686
687 EXCEPTION
688 WHEN no_data_found THEN
689 p_outcome := wip_constants.no;
690 /* There may be no such Move records in WMTI */
691
692 END delete_child_records;
693
694 /*=====================================================================+
695 | PROCEDURE
696 | check_tolerance
697 |
698 | PURPOSE
702 | ARGUMENTS
699 | This procedure would check if the transaciton primary quantity +
700 | total quantity already in the job would still be less than the tolerance.
701 |
703 |
704 | EXCEPTIONS
705 |
706 | NOTES
707 |
708 +=====================================================================*/
709
710 PROCEDURE check_tolerance
711 (
712 p_organization_id IN NUMBER,
713 p_wip_entity_id IN NUMBER,
714 p_repetitive_schedule_id IN NUMBER DEFAULT NULL,
715 p_primary_quantity IN NUMBER,
716 p_result OUT NOCOPY NUMBER -- 1 = yes, 2 = No
717 ) IS
718 x_total_qty NUMBER;
719 x_repetitive_schedule_id NUMBER;
720 l_default_tolerance NUMBER := NULL;
721 BEGIN
722 p_result := wip_constants.no;
723
724 if(p_repetitive_schedule_id is null OR p_repetitive_schedule_id < 0) then
725 x_repetitive_schedule_id := NULL;
726 else
727 x_repetitive_schedule_id := p_repetitive_schedule_id;
728 end if;
729
730 -- find default overcompletion tolerance for item without tolerance setting
731 begin
732 select default_overcompl_tolerance
733 into l_default_tolerance
734 from wip_parameters
735 where organization_id = p_organization_id;
736 exception
737 when NO_DATA_FOUND then
738 -- do nothing, and let the default tolerance to be 0
739 NULL;
740 end;
741
742 if (l_default_tolerance IS NULL) then
743 l_default_tolerance := 0;
744 else
745 l_default_tolerance := l_default_tolerance / 100;
746 end if;
747
748
749 wip_common.get_total_quantity
750 (
751 p_organization_id,
752 p_wip_entity_id,
753 x_repetitive_schedule_id,
754 x_total_qty
755 );
756
757 IF( x_repetitive_schedule_id IS NULL) THEN
758
759 SELECT
760 Decode
761 (
762 Sign(
763 x_total_qty + p_primary_quantity - wdj.start_quantity -
764 Decode(wdj.overcompletion_tolerance_type,
765 wip_constants.percent,
766 wdj.start_quantity
767 * wdj.overcompletion_tolerance_value/100,
768 wip_constants.amount,
769 wdj.overcompletion_tolerance_value,
770 NULL,
771 -- if both org and job tolerance is not set, need to offset the
772 -- p_primary_quantity
773 decode(l_default_tolerance,
774 0, 0,
775 l_default_tolerance * wdj.start_quantity ))),
776 1, wip_constants.no,
777 0, wip_constants.yes,
778 -1, wip_constants.yes
779 )
780 INTO p_result
781 FROM wip_discrete_jobs wdj
782 WHERE
783 wdj.organization_id = p_organization_id
784 AND wdj.wip_entity_id = p_wip_entity_id;
785
786 ELSE
787 SELECT
788 Decode
789 (
790 Sign(
791 x_total_qty + p_primary_quantity -
792 (wrs.daily_production_rate * wrs.processing_work_days) -
793 Decode(wri.overcompletion_tolerance_type,
794 wip_constants.percent,
795 (wrs.daily_production_rate * wrs.processing_work_days)
796 * wri.overcompletion_tolerance_value/100,
797 wip_constants.amount,
798 wri.overcompletion_tolerance_value,
799 NULL,
800 decode(l_default_tolerance,
801 0,
802 0,
803 l_default_tolerance * wrs.daily_production_rate
804 * wrs.processing_work_days))),
805 1, wip_constants.no,
806 0, wip_constants.yes,
807 -1, wip_constants.yes
808 )
809 INTO p_result
810 FROM wip_repetitive_items wri,
811 wip_repetitive_schedules wrs
812 WHERE
813 wrs.organization_id = p_organization_id
814 AND wrs.repetitive_schedule_id = x_repetitive_schedule_id
815 AND wrs.organization_id = wri.organization_id
816 AND wrs.wip_entity_id = wri.wip_entity_id
817 AND wrs.line_id = wri.line_id;
818 END IF;
819 END;
820
821 /*=====================================================================+
822 | PROCEDURE
823 | get_tolerance_default
824 |
825 | PURPOSE
826 | This procedure takes as input the assembly item id and returns the
827 | tolerance column values.
828 |
829 | ARGUMENTS
830 |
831 | EXCEPTIONS
832 |
833 | NOTES
834 |
835 +=====================================================================*/
836
837 PROCEDURE get_tolerance_default
838 (
839 p_primary_item_id IN NUMBER,
840 p_org_id IN NUMBER,
841 p_tolerance_type OUT NOCOPY NUMBER,
842 p_tolerance_value OUT NOCOPY NUMBER
843 ) IS
844 BEGIN
845
846 SELECT
847 overcompletion_tolerance_type,
848 overcompletion_tolerance_value
849 INTO p_tolerance_type, p_tolerance_value
850 FROM mtl_system_items msi
854
851 WHERE
852 msi.inventory_item_id = p_primary_item_id
853 AND msi.organization_id = p_org_id;
855 END;
856
857 /*=====================================================================+
858 | PROCEDURE
859 | insert_oc_move_txn
860 |
861 | PURPOSE
862 | Inserts the child WIP Move transaction for an Overcomplete transaction.
863 | This is used for Assembly Completion
864 | ARGUMENTS
865 |
866 |
867 | EXCEPTIONS
868 |
869 | NOTES
870 |
871 +=====================================================================*/
872
873 PROCEDURE insert_oc_move_txn
874 (
875 p_primary_quantity IN NUMBER,
876 p_cpl_profile IN NUMBER,
877 p_oc_txn_id IN NUMBER,
878 p_parent_cpl_txn_id IN NUMBER,
879 p_first_schedule_id IN NUMBER,
880 -- Have to pass this since it is not populated in MMTT !!
881 p_user_id IN NUMBER default -1,
882 p_login_id IN NUMBER default -1,
883 p_req_id IN NUMBER default -1,
884 p_appl_id IN NUMBER default -1,
885 p_prog_id IN NUMBER default -1,
886 p_child_txn_id IN OUT NOCOPY NUMBER,
887 p_first_operation_seq_num OUT NOCOPY NUMBER,
888 p_err_mesg OUT NOCOPY VARCHAR2
889 ) is
890
891 x_result NUMBER;
892 x_first_schedule_id NUMBER; /* dummy */
893 x_org_code VARCHAR2(3);
894 x_line_code VARCHAR2(10);
895 x_first_operation_code VARCHAR2(4);
896 x_first_department_id NUMBER;
897 x_first_department_code VARCHAR2(10);
898 x_last_operation_code VARCHAR2(4);
899 x_last_department_id NUMBER;
900 x_last_department_code VARCHAR2(10);
901
902 x_org_id NUMBER;
903 x_wip_entity_id NUMBER;
904 x_line_id NUMBER;
905 x_last_operation_seq_num NUMBER;
906
907 CURSOR get_move_transaction_id is
908 SELECT wip_transactions_s.nextval
909 FROM dual;
910
911 BEGIN
912
913 p_err_mesg := NULL;
914 p_first_operation_seq_num := NULL;
915
916 if( p_first_schedule_id is null OR p_first_schedule_id < 0) then
917 x_first_schedule_id := NULL;
918 else
919 x_first_schedule_id := p_first_schedule_id;
920 end if;
921
922 /* Pick up the parent transaction and its associated details */
923 SELECT
924 MIN(organization_id),
925 MIN(transaction_source_id),
926 MIN(repetitive_line_id),
927 MIN(operation_seq_num)
928 INTO x_org_id, x_wip_entity_id, x_line_id,
929 x_last_operation_seq_num
930 FROM mtl_material_transactions_temp
931 WHERE completion_transaction_id = p_parent_cpl_txn_id
932 AND transaction_action_id = wip_constants.cplassy_action;
933
934 IF( x_result = wip_constants.no ) THEN
935 fnd_message.set_name ('WIP', 'WIP_OC_TOLERANCE_FAIL');
936 p_err_mesg := fnd_message.get;
937 --p_err_mesg := 'Transaction violates Tolerance Level';
938
939 ELSE
940 if(p_child_txn_id is null or
941 p_child_txn_id <= 0) then
942 -- generate transaction_id
943 open get_move_transaction_id;
944 fetch get_move_transaction_id into p_child_txn_id;
945 close get_move_transaction_id;
946 end if;
947
948 SELECT organization_code
949 INTO x_org_code
950 FROM mtl_parameters
951 WHERE organization_id = x_org_id;
952
953 IF( x_line_id IS NOT NULL ) THEN
954 SELECT line_code
955 INTO x_line_code
956 FROM wip_lines
957 WHERE line_id = x_line_id;
958 END IF;
959
960
961 /* For the parent wip_entity_id, find the first operation */
962
963 WIP_OPERATIONS_INFO.first_operation
964 (
965 p_org_id => to_number(x_org_id),
966 p_wip_entity_id => to_number(x_wip_entity_id),
967 p_line_id => to_number(x_line_id),
968 p_first_schedule_id => to_number(x_first_schedule_id),
969 p_first_op_seq => p_first_operation_seq_num,
970 p_first_op_code => x_first_operation_code,
971 p_first_dept_id => x_first_department_id,
972 p_first_dept_code => x_first_department_code);
973
974 select
975 bso.operation_code,
976 wo.department_id,
977 bd.department_code
978 INTO
979 x_last_operation_code,
980 x_last_department_id,
981 x_last_department_code
982 from bom_standard_operations bso,
983 bom_departments bd,
984 wip_operations wo
985 WHERE wo.operation_seq_num = x_last_operation_seq_num
986 AND wo.department_id = bd.department_id
987 AND wo.standard_operation_id = bso.standard_operation_id (+)
988 AND wo.organization_id = x_org_id
989 AND wo.wip_entity_id = x_wip_entity_id
990 AND (wo.repetitive_schedule_id is NULL
991 OR
992 wo.repetitive_schedule_id = x_first_schedule_id);
993
997 transaction_id,
994
995 insert into wip_move_txn_interface
996 (
998 last_update_date,
999 last_updated_by,
1000 creation_date,
1001 created_by,
1002 last_update_login,
1003 request_id,
1004 program_application_id,
1005 program_id,
1006 program_update_date,
1007 group_id,
1008 source_code,
1009 source_line_id,
1010 process_phase,
1011 process_status,
1012 organization_id,
1013 organization_code,
1014 wip_entity_id,
1015 wip_entity_name,
1016 entity_type,
1017 primary_item_id,
1018 line_id,
1019 line_code,
1020 repetitive_schedule_id,
1021 transaction_date,
1022 acct_period_id,
1023 fm_operation_seq_num,
1024 fm_operation_code,
1025 fm_department_id,
1026 fm_department_code,
1027 fm_intraoperation_step_type,
1028 to_operation_seq_num,
1029 to_operation_code,
1030 to_department_id,
1031 to_department_code,
1032 to_intraoperation_step_type,
1033 transaction_quantity,
1034 transaction_uom,
1035 primary_quantity,
1036 primary_uom,
1037 scrap_account_id,
1038 reason_id,
1039 reason_name,
1040 reference,
1041 attribute_category,
1042 attribute1,
1043 attribute2,
1044 attribute3,
1045 attribute4,
1046 attribute5,
1047 attribute6,
1048 attribute7,
1049 attribute8,
1050 attribute9,
1051 attribute10,
1052 attribute11,
1053 attribute12,
1054 attribute13,
1055 attribute14,
1056 attribute15,
1057 transaction_type,
1058 qa_collection_id,
1059 overcompletion_transaction_id,
1060 overcompletion_transaction_qty,
1061 overcompletion_primary_qty
1062 ) (
1063 SELECT UNIQUE /* Online may have several cpl txns */
1064 p_child_txn_id,
1065 Sysdate, /* last_update_date, */
1066 p_user_id, /* last_updated_by */
1067 SYSDATE, /* creation_date */
1068 p_user_id, /* created_by */
1069 p_login_id, /* last_update_login */
1070 p_req_id, /* request_id */
1071 p_appl_id, /* program_application_id */
1072 p_prog_id, /* program_id */
1073 decode(p_req_id, -1, NULL, SYSDATE), /* program_update_date */
1074 p_child_txn_id,
1075 /* group_id - Always Online, even when cpl is in BG */
1076 NULL, /* source_code */
1077 NULL, /* source_line_id */
1078 WIP_CONSTANTS.MOVE_PROC, /* process phase */
1079 decode(p_cpl_profile, WIP_CONSTANTS.ONLINE,
1080 WIP_CONSTANTS.RUNNING,
1081 WIP_CONSTANTS.BACKGROUND,
1082 WIP_CONSTANTS.PENDING),
1083 /* process status. wiltws requires it to be in Pending */
1084 x_org_id,
1085 x_org_code,
1086 x_wip_entity_id, /* wip_entity_id */
1087 mmtt.transaction_source_name, /* wip_entity_name */
1088 mmtt.wip_entity_type, /* entity_type */
1089 mmtt.inventory_item_id, /* primary_item_id */
1090 x_line_id, /* line_id */
1091 x_line_code,
1092 x_first_schedule_id, /* repetitive_schedule_id */
1093 mmtt.transaction_date,
1094 mmtt.acct_period_id,
1095 p_first_operation_seq_num,
1096 x_first_operation_code,
1097 x_first_department_id,
1098 x_first_department_code,
1099 WIP_CONSTANTS.QUEUE,
1100 x_last_operation_seq_num,
1101 x_last_operation_code,
1102 x_last_department_id,
1103 x_last_department_code,
1104 wip_constants.tomove, /* p_intraoperation_step_type */
1105 p_primary_quantity, /* transaction_quantity */
1106 msi.primary_uom_code, /* transaction_uom */
1107 p_primary_quantity,
1108 msi.primary_uom_code, /* primary_uom */
1109 NULL, /* scrap_account_id */
1110 NULL, /* reason_id, */
1111 NULL, /* reason_name, */
1112 NULL, /* reference, */
1113 NULL, /* attribute_category, */
1114 NULL, /* attribute1, */
1115 NULL, /* attribute2, */
1116 NULL, /* attribute3, */
1117 NULL, /* attribute4, */
1118 NULL, /* attribute5, */
1119 NULL, /* attribute6, */
1120 NULL, /* attribute7, */
1121 NULL, /* attribute8, */
1125 NULL, /* attribute12, */
1122 NULL, /* attribute9, */
1123 NULL, /* attribute10, */
1124 NULL, /* attribute11, */
1126 NULL, /* attribute13, */
1127 NULL, /* attribute14, */
1128 NULL, /* attribute15, */
1129 WIP_CONSTANTS.MOVE_TXN,
1130 NULL, /* qa_collection_id, */
1131 p_oc_txn_id, /* overcompletion_transaction_id */
1132 NULL, /* overcompletion_transaction_qty */
1133 NULL /* overcompletion_primary_qty */
1134 from mtl_material_transactions_temp mmtt,
1135 mtl_system_items msi
1136 where
1137 mmtt.completion_transaction_id = p_parent_cpl_txn_id
1138 and mmtt.transaction_action_id = wip_constants.cplassy_action
1139 and mmtt.overcompletion_primary_qty IS NOT NULL
1140 and mmtt.inventory_item_id = msi.inventory_item_id
1141 and mmtt.organization_id = msi.organization_id
1142 );
1143
1144 END IF; -- if (x_result = no )
1145 END;
1146
1147 END WIP_OVERCOMPLETION;